|
You need to associate a certain user to the database you want to access.
Using Object Explorer, Go to Security -> Login, Right click Login then select New Login, create your user account. Then on the left side, select User Mapping then put a check on the database you want to access with this user account, check everything below except the denyreader and denywriter (It's up to your preference).
|
|
|
|
|
I had detached my MSSQL 2005 DB and attached the DB without log file because it is getting too big. After attaching the log, the log seem to be NOT growing anymore. What has happen to my log file? Is there anyway or software that can view the log file?
|
|
|
|
|
If you attached the database without the log file, SQL Server may have created a new log file, depending on the options you selected. It may well not be in the location you expect. In SQL Server Management Studio, right-click the database and click Properties. Under Files, scroll to see the Path and File Name columns. This will show where the log file is and the name of the file.
The transaction log is used to record all operations that are performed in the database. This is done using slow, reliable I/O in such a way that the log can be used to undo the operations should a power outage or other failure occur, or if a transaction is aborted. In practice the log is also used to defer writing back changes to the database itself - after the log data is written to disk, SQL Server only updates the affected areas in memory, lazy-writing these. It does ensure that the writes to the database file are done in a fairly timely fashion - it tries to ensure that recovery after a failure will take a bounded time, which is configurable (the 'recovery interval' option, which is set per-server, and defaults to 'automatic' which currently means about one minute). When performing a recovery operation, SQL Server may need to actually reperform actions that made it into the log but weren't yet written to the data files.
What happens to log records that represent changes that have been committed, and securely written to disk, depends on whether SQL Server thinks you want to retain the log records. If not, these log records then become available for reuse. When the end of the log file is reached, if the area at the beginning is available for reuse SQL Server will wrap around, otherwise it has to grow.
Whether SQL Server retains the log records depends on the recovery model and whether you've backed up. If the recovery model is set to Simple, SQL Server does not retain the logs. If, however, it is set to Full or Bulk Logged, and you have made at least one full database backup, it assumes that you do want to keep a backup chain and does retain the logs.
Why would you want to do this? Because if you have the log file and log backups available, you can restore not just the last full backup but also roll forward to the point of failure, or to a given point in time. If you are using Full recovery model, you should ensure that you're regularly backing up the transaction logs as well as the database - a full backup does not clear the transaction log chain (unlike Exchange, for example).
If you are using Full or Bulk Logged recovery model but want to reset your log chain, you can issue the command
BACKUP LOG database WITH TRUNCATE_ONLY which marks all committed transaction log records as reusable. It will then not keep a log chain until you next do a full backup. To actually shrink the log file you will need to then use the Shrink Database feature.
There are tools available that can view the log file but they're not cheap.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
|
Is there any chance to retrieve value from sub report to main report?
Let's say i have a main report (call it report A) and inside of that report A, I have create a subReport (call it report B) in report B, I have an aggregation field that contain expresion like :
=Count(Fields!Project.Value)
Now, in report A, I would like to add a textbox that consist a value that taken from previous Expression. How to do that?
Thanx 1st...
|
|
|
|
|
|
I am developing an application and i have used the Dataset technology to retrieve data from the Database(SQL server 2000).
The problem is that on loading some Forms that retrieve the data from the Database Via the dataset it takes too much time what could be the problem
briogene
|
|
|
|
|
Actually it is depend on the your page server controls.
if you use Gridview than it is take time for loading.
umesh
|
|
|
|
|
soni uma wrote: Actually it is depend on the your page server controls
It can depend on a lot more than that.
|
|
|
|
|
Yes It is Depend on the Many things like time taken by sql query,No of Records and Ect
|
|
|
|
|
briogene wrote: The problem is that on loading some Forms that retrieve the data from the Database Via the dataset it takes too much time what could be the problem
There are a million and one things that could be the problem. But without a better description from you it makes it hard to us to diagnose.
Some things to look at:
How long does the query take to run in SQL Server itself?
How much data is being transferred? What are you doing to it before the user gets to see it?
Do you need a DataSet? Would a forward-only read-only view be more efficient?
|
|
|
|
|
You can use Threads so the form will not lock up. 
|
|
|
|
|
Ian Uy wrote: You can use Threads so the form will not lock up
That doesn't really solve the root problem.
|
|
|
|
|
create proc sp_temp @columnname varchar(25),@value varchar(25) as
declare @sql nvarchar(400)
set @sql='select *from tblname where '+@columnname+' = '+@value
exec(@sql)
This is the code. This procedures works for integer value when I insert in @value but for a string value; it gives invalid column name. Can anyone help me out
-- modified at 2:50 Monday 29th October, 2007
Regards,
LEE
|
|
|
|
|
Just check your table detail. It will be because of you have defined the column name as int while creating the table.
|
|
|
|
|
ok, Now i got your problem and your solution is given here.
set @sql='select *from tblname where '+@columnname+' = '+''''+@value+''''
I have tried it and its working.
|
|
|
|
|
Print your SQL query before executing, you will get the problem.
|
|
|
|
|
Try This it's help u
set @sql='select * from jc_student_master where '+@columnname+' = '''+@value+''' '
|
|
|
|
|
If you are using EXEC command to execute a sql String then the parameter has to converted into Character and made as a part of the query before execution.
try this...
set @sql = 'select * from tblname where ' +
@columnname + ' = ' + CAST(@value AS NVARCHAR(50))
EXEC(@sql)
Regards
- J O H N -
|
|
|
|
|
Let me have table name tblname which has empid and empname. In the paramater @columnname empid and @value(int) this query gets the perfect output. But If I use @columnname empname and @value as string; then it gives invalid column name
Regards,
LEE
|
|
|
|
|
ok I suggest you a better way of doing this. In sql Server we have one method of executing Dynamic Sql using sp_executesql which does a parameter substution based on the parameter definition.Let us do something like this to solve your problem...
create proc sp_temp
@columnname varchar(25),
@value varchar(25)
as
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
set @SQLQuery = 'select * from tblname where (1=1) AND '
IF @columnname = 'EmpID'
BEGIN
set @SQLQuery = @SQLQuery + @columnname + ' = @value'
SET @ParameterDefinition = '@value INT'
END
IF @columnname = 'EmpName'
BEGIN
set @SQLQuery = @SQLQuery + @columnname + ' = @value'
SET @ParameterDefinition = '@value NVARCHAR(25)'
END
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @value
GO
Here in the above procedure I build the ParameterDefinition list with the appropriate Datatype based on the column name. And the sp_executesql would automatically do a parameter substitution based on the DataType. Jus check it out whether it works...
Regards
- J O H N -
|
|
|
|
|
hi all,
In our code,SQL DMO objects(Sql 2000)are used in the VB components.
Now we are doing SQl 2005 upgradation.when i ran the same component in SQl 2005, i got the following error.
To connect to this server you must use SQL Server Management Studio or SQL Server Management Objects (SMO) .I read in an article that we have to reregister SQL-DMO 2005 version.but i have to change that DMO object to SMO in code.
Plz give suggestions on how to convert DMO to SMO.
Thanks in advance,
cheers
sangeet
|
|
|
|
|
You need the SQL Server 2005 Backward Compatibility Components package. You can find this at the latest 'Feature Pack' download. The last update was in February 2007 which can be found here[^].
SMO - Server Management Objects - is a set of .NET components that perform much the same function as DMO.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
|
The sp_who2 internal procedure provides the following information:
Spid—The system process ID.
status—The status of the process (e.g., RUNNABLE, SLEEPING).
loginame—Login name of the user.
hostname—Machine name of the user.
blk—If the process is getting blocked, this value is the SPID of the blocking process.
dbname—Name of database the process is using.
Cmd—The command currently being executed (e.g., SELECT, INSERT)
CPUTime—Total CPU time the process has taken.
DiskIO—Total amount of disk reads for the process.
LastBatch—Last time a client called a procedure or executed a query.
ProgramName—Application that has initiated the connection (e.g., Visual Basic, MS SQL Query Analyzer)
You can use them.
Arun Singh
Noida.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
