November 16, 2004 at 2:38 pm
Ran into an interesting issue, SQL log is reporting: The SQL Server cannot obtain a LOCK resource at this time, when a stored procedure is executed via an ODBC connection only when the logic in the stored procedures results in an update, the inserts work with no problem... increasing memory allocated to the SQL Server seem to correct the issue...
Why ODBC only and why the update?
November 17, 2004 at 2:23 am
I had a similar problem - seems to be a memory leak type issue (could find no useful information) in the end it required a reboot when eventually we could no longer even read from views via ODBC. It has not reoccurred but presumably it will at some point in the future.
Best regards,
Andrew
November 17, 2004 at 5:53 am
This is a shot in the dark, but this sounds similar to an issue I have run into with ADO. SQL server can't figure out the specific row that needs updating because there is not enough key information to uniquely identify the row, so you get an error. The procedure completes, no error is returned to the calling proc, but the row never gets updated.
If you don't have it in the procedure already, try to include the primary key in the update.
Good luck.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 18, 2004 at 2:59 pm
Thanks for the feedback..The stored procedure is using an index and is updating a clustered index. Further research showed that the updates work but begin to fail after multiple executives over time.. It is almost like resources used to exec the update via ODBC connection are not releeased and at some point run out...
Changing the SQL configuration and applying the change without re-starting the server seem to fix the problem...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply