February 11, 2003 at 11:44 am
I have a database that when I save information a text file is created using xp_cmdshell. The problem I am having is that an error is coming up and it looks like other processes are running at the same time caused by the data entered that it is causing a lockup. Is there anyway to make the stored procedure using cmdshell wait till the other processes are done? I am new to SQL Server.
February 11, 2003 at 2:24 pm
To be sure that your sp is blocked, while you are running the stored procedure, execute on Query Analizar the t-sql command exec sp_who2. Si sp, tells you all the spid executing commands and by who they are been blocked.
Run it and check if the problem is that you are being blocked.
February 12, 2003 at 5:26 am
If you do not care whether the information you are reading into the text file could be changed during the read operation, you could issue the following command at the start of the script:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
or, you could issue the WITH (NOLOCK) hint in the SELECT statement. This way, other processes executing at the same time should not have an effect on your stored procedure. Look in Books On Line for information regarding how SQL Server issues locks on different objects...
Hope this helps,
Jay
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply