About PRINT and other SQLCMD questions

About PRINT and other SQLCMD questions

I am using SQLCMD.EXE of SQL Server 2005 for the automation of my 
installation scripts. I wanted to make use of transactions when inserting 
data into the database during the installation but since there are several 
GO in between it was not possible without having to multiplicate the 
BEGIN/END TRANSACTION.

My remaining questions are the following (regarding SQLCMD.EXE scripts):

1) Is there (or will there ever) be a way to check in such script if a 
variable is set? If I use $(variable) and it has not been set then I get an 
error which kind of gets in the way. Or another alternative would have been 
to have some sort of :SetVar that would set a default value in case it is 
not set in any other way but get overriden if it is

2) It is terrible that using the :r <file> command always displays "1 record 
changed" instead of NOT doing that and simply displaying such record 
messages for the CONTENTS of the included file. Unfortunately that cannot be 
changed (a forgotten feature but more useful than the current 
implementation).

3) Does the PRINT 'any message' gets automatically flushed to the output 
stream or do I always have to use GO after it to make sure it is flushed 
before continuing? I need to print these messages (and thus have the user 
read them) BEFORE the action is performed, if it remains in the cache then 
the user feedback is lost. I wanted to avoid having GO after a plain old 
PRINT.

Any ideas?

Emilio
http://www.virtual-aviation.info
http://www.panamasights.com/

date: Fri, 30 May 2008 09:07:11 +0200 author: ....DotNet4Ever....

Re: About PRINT and other SQLCMD questions

....DotNet4Ever.... (hate.spam@nowhere.com) writes:
> I am using SQLCMD.EXE of SQL Server 2005 for the automation of my 
> installation scripts. I wanted to make use of transactions when inserting 
> data into the database during the installation but since there are several 
> GO in between it was not possible without having to multiplicate the 
> BEGIN/END TRANSACTION.

Transactions can span batches. What you need to consider is that each
batch that is intended to be part of the transaction is formed like
this:

   IF @@trancount > 0
   BEGIN 
      .....
   END

This is because an error in a previous batch may abort the batch and
roll back any open transactions.

 
> 3) Does the PRINT 'any message' gets automatically flushed to the output 
> stream or do I always have to use GO after it to make sure it is flushed 
> before continuing? I need to print these messages (and thus have the user 
> read them) BEFORE the action is performed, if it remains in the cache then 
> the user feedback is lost. I wanted to avoid having GO after a plain old 
> PRINT.
 
Use RAISERROR('My message', 0, 1) WITH NOWAIT instead of PRINT to
address this problem.

Sorry, I don't have the answer for the other two questions.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

date: Fri, 30 May 2008 15:15:09 -0700 author: Erland Sommarskog

Re: About PRINT and other SQLCMD questions

Thanks a million Erland, these answers are just what I needed.

Emilio

"Erland Sommarskog"  wrote in message 
news:Xns9AAF2B03DF54Yazorman@127.0.0.1...
> ....DotNet4Ever.... (hate.spam@nowhere.com) writes:
>> I am using SQLCMD.EXE of SQL Server 2005 for the automation of my
>> installation scripts. I wanted to make use of transactions when inserting
>> data into the database during the installation but since there are 
>> several
>> GO in between it was not possible without having to multiplicate the
>> BEGIN/END TRANSACTION.
>
> Transactions can span batches. What you need to consider is that each
> batch that is intended to be part of the transaction is formed like
> this:
>
>   IF @@trancount > 0
>   BEGIN
>      .....
>   END
>
> This is because an error in a previous batch may abort the batch and
> roll back any open transactions.
>
>
>> 3) Does the PRINT 'any message' gets automatically flushed to the output
>> stream or do I always have to use GO after it to make sure it is flushed
>> before continuing? I need to print these messages (and thus have the user
>> read them) BEFORE the action is performed, if it remains in the cache 
>> then
>> the user feedback is lost. I wanted to avoid having GO after a plain old
>> PRINT.
>
> Use RAISERROR('My message', 0, 1) WITH NOWAIT instead of PRINT to
> address this problem.
>
> Sorry, I don't have the answer for the other two questions.
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

date: Tue, 3 Jun 2008 10:05:58 +0200 author: ....DotNet4Ever....

Re: About PRINT and other SQLCMD questions

Just another question on this topic...

Assuming my SQL script is used with SQLCMD and I use it to "source" other 
scripts such as this:

BEGIN TRY
    PRINT 'Installing Common'
    BEGIN TRANSACTION T1
:r Create\Common.SQL        -- Does multiple INSERT
    PRINT 'Installing Add-on'
    IF @@TranCount > 0
    BEGIN
:r Create\AddOn.SQL            -- Does multiple INSERT
        COMMIT TRANSACTION T1
    END
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION T1
END CATCH
GO

Then my question is, would the approach you mentioned with @@transcount work 
here as well? or does it get "broken" by the fact that two child scripts 
(:r) are sourced?

http://www.panamasights.com
http://www.virtual-aviation.info

----- Original Message ----- 
From: "Erland Sommarskog" 
Newsgroups: microsoft.public.sqlserver.tools
Sent: Saturday, May 31, 2008 12:15 AM
Subject: Re: About PRINT and other SQLCMD questions


> ....DotNet4Ever.... (hate.spam@nowhere.com) writes:
>> I am using SQLCMD.EXE of SQL Server 2005 for the automation of my
>> installation scripts. I wanted to make use of transactions when inserting
>> data into the database during the installation but since there are 
>> several
>> GO in between it was not possible without having to multiplicate the
>> BEGIN/END TRANSACTION.
>
> Transactions can span batches. What you need to consider is that each
> batch that is intended to be part of the transaction is formed like
> this:
>
>   IF @@trancount > 0
>   BEGIN
>      .....
>   END
>
> This is because an error in a previous batch may abort the batch and
> roll back any open transactions.
>
>
>> 3) Does the PRINT 'any message' gets automatically flushed to the output
>> stream or do I always have to use GO after it to make sure it is flushed
>> before continuing? I need to print these messages (and thus have the user
>> read them) BEFORE the action is performed, if it remains in the cache 
>> then
>> the user feedback is lost. I wanted to avoid having GO after a plain old
>> PRINT.
>
> Use RAISERROR('My message', 0, 1) WITH NOWAIT instead of PRINT to
> address this problem.
>
> Sorry, I don't have the answer for the other two questions.
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
"Erland Sommarskog"  wrote in message 
news:Xns9AAF2B03DF54Yazorman@127.0.0.1...
> ....DotNet4Ever.... (hate.spam@nowhere.com) writes:
>> I am using SQLCMD.EXE of SQL Server 2005 for the automation of my
>> installation scripts. I wanted to make use of transactions when inserting
>> data into the database during the installation but since there are 
>> several
>> GO in between it was not possible without having to multiplicate the
>> BEGIN/END TRANSACTION.
>
> Transactions can span batches. What you need to consider is that each
> batch that is intended to be part of the transaction is formed like
> this:
>
>   IF @@trancount > 0
>   BEGIN
>      .....
>   END
>
> This is because an error in a previous batch may abort the batch and
> roll back any open transactions.
>
>
>> 3) Does the PRINT 'any message' gets automatically flushed to the output
>> stream or do I always have to use GO after it to make sure it is flushed
>> before continuing? I need to print these messages (and thus have the user
>> read them) BEFORE the action is performed, if it remains in the cache 
>> then
>> the user feedback is lost. I wanted to avoid having GO after a plain old
>> PRINT.
>
> Use RAISERROR('My message', 0, 1) WITH NOWAIT instead of PRINT to
> address this problem.
>
> Sorry, I don't have the answer for the other two questions.
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

date: Tue, 3 Jun 2008 13:16:15 +0200 author: ....DotNet4Ever....

Re: About PRINT and other SQLCMD questions

....DotNet4Ever.... (hate.spam@nowhere.com) writes:
> Just another question on this topic...
> 
> Assuming my SQL script is used with SQLCMD and I use it to "source" other 
> scripts such as this:
> 
> BEGIN TRY
>     PRINT 'Installing Common'
>     BEGIN TRANSACTION T1
>:r Create\Common.SQL        -- Does multiple INSERT
>     PRINT 'Installing Add-on'
>     IF @@TranCount > 0
>     BEGIN
>:r Create\AddOn.SQL            -- Does multiple INSERT
>         COMMIT TRANSACTION T1
>     END
> END TRY
> BEGIN CATCH
>     ROLLBACK TRANSACTION T1
> END CATCH
> GO
> 
> Then my question is, would the approach you mentioned with @@transcount
> work here as well? or does it get "broken" by the fact that two child
> scripts (:r) are sourced? 
 
Well, that depends on what it is those files. If there are any GO
in them, you will get a syntax error for the BEGIN TRY without 
a matching END TRY. Unless there is one in the file you include.

But if there are no GO in the file, this should work fine.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

date: Tue, 03 Jun 2008 14:59:48 -0700 author: Erland Sommarskog