About PRINT and other SQLCMD questions

#1

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/

#2

.....DotNet4Ever.... (hate.spam (AT) nowhere (DOT) com) writes:

Quote:

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.

Quote:

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#3

.....DotNet4Ever.... (hate.spam (AT) nowhere (DOT) com) writes:

Quote:

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.

Quote:

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#4

.....DotNet4Ever.... (hate.spam (AT) nowhere (DOT) com) writes:

Quote:

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.

Quote:

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#5

.....DotNet4Ever.... (hate.spam (AT) nowhere (DOT) com) writes:

Quote:

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.

Quote:

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#6

.....DotNet4Ever.... (hate.spam (AT) nowhere (DOT) com) writes:

Quote:

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.

Quote:

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#7

.....DotNet4Ever.... (hate.spam (AT) nowhere (DOT) com) writes:

Quote:

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.

Quote:

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#8

.....DotNet4Ever.... (hate.spam (AT) nowhere (DOT) com) writes:

Quote:

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.

Quote:

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#9

.....DotNet4Ever.... (hate.spam (AT) nowhere (DOT) com) writes:

Quote:

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.

Quote:

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#10

.....DotNet4Ever.... (hate.spam (AT) nowhere (DOT) com) writes:

Quote:

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.

Quote:

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx