stored procedures

[Nancy]

I am having some problems with my stored procedures called from a Visual Basic program that Insert into tables. Does anyone have any tips?

 

[MikeLacey]

Hi Nancy,<br>
<br>
We should be able to get you going - can you give a few more detail?<br>
<br>
Database<br>
<br>
ODBC - or whatever (Oracle Objects for OLE for instance)<br>
<br>
Sample bit of code that's not working<br>
<br>
The error you're getting<br>
<br>
Regards<br>
<br>
Mike<br>

 

[Nancy]

I am inserting records into a table on the SQL server and calling a stored procedure in SQL from Visual Basic using an RDO connection.<br>
<br>
This is an example of the code:<br>
<br>
begin transaction<br>
<br>
insert spcl (agency, Client, Time_Stamp, Record_id_Number)<br>
<br>
values (@agency_code, @Client_Number,@Time_Stamp, @record_id_Number)<br>
<br>
commit<br>
<br>
Occasionally when this is called, the SQL server locks. And, I can not understand why. It is a simple stored procedure. I would also like the record to be updated instead of inserted if it is already out there, but I want to keep it as simple as possible until I am sure it is working properly.<br>
<br>
I appreciate your help.

 

[MikeLacey]

Hi Nancy,<br>
<br>
Ok. You probably know all this stuff but bear with me anyway.<br>
<br>
You say "Occasionally" so I guess this works mostly? Whenever I get an update that mostly or sometimes works but sometimes locks the DB that's exactly what I think of .. <br>
<br>
LOCKS! &lt;smile&gt;<br>
<br>
As you're not getting an error message the DB might just be waiting for a resource that's currently busy - that would be my first check. Is that table locked because there's another insert going on? (from a previous session of yours?)<br>
<br>
Next thing would be the patch level of the SQL Server DB. Are you running with the correct service packs etc?<br>
<br>
Does SQL Server have error logs? (it must do I guess) Have a look through those.<br>
<br>
And the other obvious thing: Is the data you're trying to insert valid? Does that statement work in an interactive SQL session?<br>
<br>
<br>
Regards<br>
<br>
Mike<br>
<br>

 

[Nancy]

As far as I can tell by looking at the error log on the SQL Server, there is only one user accessing the table; however, that user gets an error message and all of the other users accessing the SQL Server (not that table) are kicked off.<br>
<br>
The data I am trying to insert is valid. And we are running with the correct service pack. <br>
<br>
The only thing I can think of is that ... when we run one of the programs, it creates a lot of page locks. And after a certain number of page locks, a table lock occurs. I don't want to increase the number of page locks that can occur before a table lock occurs because then I have problems with my temp db.<br>
<br>
I still don't understand why the entire server would freeze. And, I thought the SQL Server was a little more adept at managing locks so that no error would occur.

 

[MikeLacey]

Sounds reasonable - that your page locks would eventually be promoted to a table lock; but this is a normal event and shouldn't cause it to freeze - let alone give an error message. What's the error message that user (who's doing the insert) gets?<br>
<br>
-ml<br>

 

[Nancy]

Your server command (process id 19) was deadlocked with another process and has been chosen as deadlock victim."<br>
<br>
This is the message, but I am positive that no one else is accessing that table.

 

[MikeLacey]

And then it kicks *everybody* off? God bless Microsoft - they really know how to program in Seattle don't they?<br>
<br>
1) It might be a bug. SQL Server might think there's a deadlock when there isn't (something is certainly handling the condition inelegantly)<br>
<br>
2) There *might* be something else after that table. I know - I know - you're certain there's not.<br>
<br>
A deadlock is where (I'm sure you know this but bear with me) your process gets a lock on A and attempts to lock B. B is already locked by somebody else (so you can't lock it) who then tries to lock A (which you've locked so that fails). This then goes 'round in a nice little loop because neither process thinks to unlock everything and try the whole thing again.<br>
<br>
Two things to try:<br>
<br>
a) Try this again when no-one else is using the database. If it fails then, hmmmm.<br>
<br>
b) Have a look at what else your process is doing - as far as I'm aware deadlocks only occur when you're trying to lock more than one object (that might be more than one row in the same table - not just more than one table)<br>
<br>
Go for it Nancy.<br>
<br>
Mike<br>

 

[babeo]

Hi MikeLacey<br><br>Just have sometimes fulling around this forum to find some tips, and I found this one.<br><br>I just have a question about the message:&nbsp;&nbsp;<br><br>&quot;Your server command (process id 19) was deadlocked with another process and has been chosen as deadlock victim.&quot;<br><br>Yes, longtime ago, I get this message, but because I don't know Sybase much and I don't work on the db often, I told my coworker what happens, after that she informs me she rerounds the process.&nbsp;&nbsp;I ask her how she does it, but she refuses to show me.&nbsp;&nbsp;Now, I involve with the db and Sybase (but still don't know much about it &lt;g&gt;!!) and I sometimes see the similar msg again, I would like to know what my coworker said &quot;reround/redirect the process&quot;, and how does she do it?&nbsp;&nbsp;so that I can do it by myself rather calling her help.<br><br>Thanks.<br>

 

[calahans]

Hi Nancy,<br><br>When you get a deadlock SS arbitrarily select a process to kick off.<br><br>If you are sure that no other process is locking that table, I can only think of one other reason. In SS theres a db_option (in v6.5) called &quot;insert row level locking&quot; - this gives you row level locking on INSERTs only (SS uses page level locking normally). <br><br>If your process inserts a row(s) and in the same sp trys then to update those rows, you can deadlock yourself as ther insert is waiting to lock the page but it already has a row level lock.... I have seen this once b4 (we spent ages trying to find the cause)<br><br>I no its convoluted but worth a try<br><br> <p>Cal<br><a href=mailto: > </a><br><a href= > </a><br>

 

[Invicta]

Hi Nancy, you've got us all curious with this one!<br><br>Another thought, are there any triggers on this table that may be running off and doing all kinds of things every time you insert a record, perhaps coming back to the spcl table?<br>