Sql Server 2005: Query Completed with Errors (nothing in Results tab)
in [Server Configuration]
|
From: Surya Halim on 24 Nov 2009 13:51 I am running a batch INSERT statements off a file on Sql Server 2005 and the queries ran for a while then stopped with Query completed with errors next to an exclamation mark in the Results tab. The Results tab had nothing in it so at this point, I am clueless as to how to even begin to figure out which statement causes the whole batch to fail. Tried running Sql Server Profiler with trace running, but the profiler did not show any statement that caused the error either. The file containing the Sql INSERT statements itself is about 21 MB in size. Does anyone have any idea about how to even begin to figure out what went wrong? All I know at this point is, something causes error in the Sql batch. The most obvious way to narrow down the problem would be to cut the file in half, run, test, cut in half, run, test, and so on until the offending block of statements can be identified but I would prefer to do that as absolute last resort. Hong
From: Erland Sommarskog on 24 Nov 2009 17:56 Surya Halim (ljfong(a)gmail.com) writes: > I am running a batch INSERT statements off a file on Sql Server 2005 and > the queries ran for a while then stopped with Query completed with > errors next to an exclamation mark in the Results tab. The Results tab > had nothing in it so at this point, I am clueless as to how to even > begin to figure out which statement causes the whole batch to fail. > Tried running Sql Server Profiler with trace running, but the profiler > did not show any statement that caused the error either. The file > containing the Sql INSERT statements itself is about 21 MB in size. Does > anyone have any idea about how to even begin to figure out what went > wrong? All I know at this point is, something causes error in the Sql > batch. Do you have results to grid or to text. If you have results to grid, the error message is in the Message tab. Once you have found the error message, double-click on it, and Management Studio should find the right line for you. Tip: run with SET NOCOUNT ON, to be saved all those "rows affected" messages. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Surya Halim on 1 Dec 2009 17:32 Hi Erland, There was nothing in the Message tab either. I only had a little exclamation mark with the message Query completed with errors located at the lower left corner of the Message tab. Same thing happened when I did only check for syntax for the Result tab. So something went wrong but no indication of what actually went wrong. The results was set to grid. Surya > -----Original Message----- > From: Erland Sommarskog [mailto:esquel(a)sommarskog.se] > Posted At: Tuesday, November 24, 2009 3:57 PM > Posted To: microsoft.public.sqlserver.server > Conversation: Sql Server 2005: Query Completed with Errors (nothing in > Results tab) > Subject: Re: Sql Server 2005: Query Completed with Errors (nothing in > Results tab) > > Surya Halim (ljfong(a)gmail.com) writes: > > I am running a batch INSERT statements off a file on Sql Server 2005 > and > > the queries ran for a while then stopped with Query completed with > > errors next to an exclamation mark in the Results tab. The Results > tab > > had nothing in it so at this point, I am clueless as to how to even > > begin to figure out which statement causes the whole batch to fail. > > Tried running Sql Server Profiler with trace running, but the > profiler > > did not show any statement that caused the error either. The file > > containing the Sql INSERT statements itself is about 21 MB in size. > Does > > anyone have any idea about how to even begin to figure out what went > > wrong? All I know at this point is, something causes error in the Sql > > batch. > > Do you have results to grid or to text. If you have results to grid, > the error message is in the Message tab. Once you have found the error > message, double-click on it, and Management Studio should find the > right line for you. > > Tip: run with SET NOCOUNT ON, to be saved all those "rows affected" > messages. > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Erland Sommarskog on 1 Dec 2009 17:46 Surya Halim (ljfong(a)gmail.com) writes: > There was nothing in the Message tab either. I only had a little > exclamation mark with the message Query completed with errors located at > the lower left corner of the Message tab. Same thing happened when I did > only check for syntax for the Result tab. So something went wrong but no > indication of what actually went wrong. The results was set to grid. Strange. Did you check whether the rows were actually inserted. Your file is very large. Maybe SSMS simply chokes? Run the file through SQLCMD; this command-line tool can handler large files better than SSMS. The only other advice I can give is to split up the file in smaller files, and run them one by one. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Surya Halim on 1 Dec 2009 18:58
> Strange. Did you check whether the rows were actually inserted. None of the rows were inserted. > Your file is very large. Maybe SSMS simply chokes? Run the file through > SQLCMD; this command-line tool can handler large files better than > SSMS. SSMS could be choking. However, I find it strange to choke on a file that is only 21 MB (SSMS feels kinda "weak"). If it were 2.1 GB, then yeah it makes sense. I'll try running the batch using SQLCMD later. Thanks, Surya > -----Original Message----- > From: Erland Sommarskog [mailto:esquel(a)sommarskog.se] > Posted At: Tuesday, December 01, 2009 3:46 PM > Posted To: microsoft.public.sqlserver.server > Conversation: Sql Server 2005: Query Completed with Errors (nothing in > Results tab) > Subject: Re: Sql Server 2005: Query Completed with Errors (nothing in > Results tab) > > Surya Halim (ljfong(a)gmail.com) writes: > > There was nothing in the Message tab either. I only had a little > > exclamation mark with the message Query completed with errors located > at > > the lower left corner of the Message tab. Same thing happened when I > did > > only check for syntax for the Result tab. So something went wrong but > no > > indication of what actually went wrong. The results was set to grid. > > Strange. Did you check whether the rows were actually inserted. > > Your file is very large. Maybe SSMS simply chokes? Run the file through > SQLCMD; this command-line tool can handler large files better than > SSMS. > > The only other advice I can give is to split up the file in smaller > files, and run them one by one. > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |