Only this one stored procedure failed, the rest of the system worked fine. The code I used in SQL 2005 was as follows: Code language: SQL (Structured Query Language) (sql) Create first Stored procedures to insert data from Linked server to actual DB Add ('AvailableDate', GetType (Date)) This code creates a row from the DataTable, populates the two columns in the row and then adds the row to the table: Both of these. I tested the exact same stored procedure using SQL management studio, and the data was returned in about 4 seconds (much less than the configured time out). #SQL SERVER RECOMPILE STORED PROCEDURE CODE#Īfter many hours of trying different things, I recompiled this particular stored procedure (re ran the ALTER without changing the code), and the client code started working again exactly as it always had done, no code in either the client or database was changed. This morning I had a similar problem on another stored procedure, the client executed the stored procedure and timed out before the dataset was returned.Īs I had done many other things, I wasn't 100% sure what had fixed it, so I didn't worry about it again. Script Alternatives - Use DBCC FREEPROCCACHE as an alternative to recompile all objects. T-SQL Script to get the execution plan of a stored procedure Septem§ Leave a comment Think if you may have faced a situation, sometimes back, where some stored procedure(s) were executing with a badly performance specially in Production environment and now you want to see the execution plan of it for further analysis To build a. The same stored procedure worked fine using management studio. If a script is needed, an alternative to the loop is the spmsforeachtable system stored procedure. EXEC spMSforeachtable command1'EXEC sprecompile ''' GO. Just keep in mind that spmsforeachtable is an undocumented system stored procedure, so check out the web for examples. Note: See my post over here for other uses of spMSforeachtable 8 Common Uses of the undocumented Stored Procedure spMSforeachtable. #SQL SERVER RECOMPILE STORED PROCEDURE CODE#Īfter a few hours playing with timeouts etc, I remembered the previous problem, and recompiled the stored procedure.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |