Prev: Collation settings
Next: Sql Server Indexing With Two or More Columns
From: morphius on 5 Dec 2009 08:33
I have a .sql file with about at least 70-80 different sql
statements in any order (insert, update, delete) . I ran it in ssms
successfully, but it
didnt run some of the statements. The statements that didnt run will only run
successfully if run by itself. How do I ensure every statement successfull
fires?
I have read an article http://support.microsoft.com/kb/827575 about the
output buffer.
1. How is the output buffer increase?
2. What is the size limit of the output buffer so that I can divide my big
..sql file into several small ones?
From: Erland Sommarskog on 5 Dec 2009 10:09
morphius (morphius(a)discussions.microsoft.com) writes:
> I have a .sql file with about at least 70-80 different sql statements in
> any order (insert, update, delete) . I ran it in ssms successfully, but
> it didnt run some of the statements. The statements that didnt run will
> only run successfully if run by itself. How do I ensure every statement
> successfull fires?
> I have read an article http://support.microsoft.com/kb/827575 about the
> output buffer.
> 1. How is the output buffer increase?
> 2. What is the size limit of the output buffer so that I can divide my big
> .sql file into several small ones?

There is too little information to say what is wrong, but whatever, the
output buffer has nothing to do with it.

I can think of two explanations to your problem:
1) There are flaws in the script, for instance comments, that prevents
the statement to be run.
2) Your diagnosis that the statements did not run is in correct.

If you post the file, and point out which statements that did not
run, we might be able to give more useful help.


--
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: Dan Guzman on 5 Dec 2009 10:55
> I have read an article http://support.microsoft.com/kb/827575 about the
> output buffer.
> 1. How is the output buffer increase?
> 2. What is the size limit of the output buffer so that I can divide my big
> .sql file into several small ones?

I think the KB article is applicable only to client applications that don't
consume all of the results generated by the script. SSMS should consume
all of the results of the script.

As I posted in your "Transactional Processing" thread, you might running
Profiler of SQL statement starting and completed events. That will show
what is actually being happening on the server.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/



From: Jay on 5 Dec 2009 13:00
Are you logically separating the statements with ;'s and placing GO at the
end of logical blocks?

Beyond that, you would have to build everything into a procedure and add
error checking (see @@ERROR, TRY & CATCH).


"morphius" <morphius(a)discussions.microsoft.com> wrote in message
news:8490B9FC-4C70-4A24-95D4-EED5979B8727(a)microsoft.com...
>I have a .sql file with about at least 70-80 different sql
> statements in any order (insert, update, delete) . I ran it in ssms
> successfully, but it
> didnt run some of the statements. The statements that didnt run will only
> run
> successfully if run by itself. How do I ensure every statement successfull
> fires?
> I have read an article http://support.microsoft.com/kb/827575 about the
> output buffer.
> 1. How is the output buffer increase?
> 2. What is the size limit of the output buffer so that I can divide my big
> .sql file into several small ones?


From: morphius on 6 Dec 2009 13:04
Jay,
You are right. It was the ;'s and GO's. Thanks.

"Jay" wrote:

> Are you logically separating the statements with ;'s and placing GO at the
> end of logical blocks?
>
> Beyond that, you would have to build everything into a procedure and add
> error checking (see @@ERROR, TRY & CATCH).
>
>
> "morphius" <morphius(a)discussions.microsoft.com> wrote in message
> news:8490B9FC-4C70-4A24-95D4-EED5979B8727(a)microsoft.com...
> >I have a .sql file with about at least 70-80 different sql
> > statements in any order (insert, update, delete) . I ran it in ssms
> > successfully, but it
> > didnt run some of the statements. The statements that didnt run will only
> > run
> > successfully if run by itself. How do I ensure every statement successfull
> > fires?
> > I have read an article http://support.microsoft.com/kb/827575 about the
> > output buffer.
> > 1. How is the output buffer increase?
> > 2. What is the size limit of the output buffer so that I can divide my big
> > .sql file into several small ones?
>
>
> .
>
 |  Next  |  Last
Pages: 1 2 3 4
Prev: Collation settings
Next: Sql Server Indexing With Two or More Columns