From: Surya Halim on
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
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
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
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
> 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