Echo sql which has been run

I am relatively new to SQL Server.

I have a command file with the following contents :
osql -E -i%1.sql -d%2 -oq:\%1.log

The sql script file has a number of insert/update statements.
The log file produced looks something like this :

1> 2> (1 row affected)
1> 2> (1 row affected)
1> 2> (0 rows affected)

Is there any setting which can be turned on such that the log file
produced from this command file will echo the statement and then
the number of rows which are affected.

TIA."Michael McGarrigle" <mjm@.barwonwater.vic.gov.au> wrote in message
news:9d0cafdc.0309111717.4dc8efaa@.posting.google.c om...
> I am relatively new to SQL Server.
> I have a command file with the following contents :
> osql -E -i%1.sql -d%2 -oq:\%1.log
> The sql script file has a number of insert/update statements.
> The log file produced looks something like this :
> 1> 2> (1 row affected)
> 1> 2> (1 row affected)
> 1> 2> (0 rows affected)
> Is there any setting which can be turned on such that the log file
> produced from this command file will echo the statement and then
> the number of rows which are affected.
> TIA.

You can try adding -e -n to your command line. It works best if each
statement is in its own batch:

update...
go
insert...
go

Like that, you get each statement with the rowcount immediately after it. If
all statements are in one batch, you'll get all the statements together then
all the rowcounts together. That might be OK for you anyway, of course.

Simonsql

Dynamic USE

I want to run a .sql script that updates data from specific tables using
OSQL. The problem i am having is that i need to run the same script on 300
servers. The database naming scheme are as follows:
server 1 : server1_testdb
server 2 : server2_testdb
server 3: server3_testdb
Question:
How can I write one script that can be used for all 300 servers. All
databases have the same structure and are identical except that it resides in
300 servers that is where the prefix (server1_, server2_, etc.) come from.
Does it mean I have to write 300 scripts to specify the database where the
script will be run.
Ex.
script 1: USE server1_testdb
UPDATE tbl_sample ...
script 2: USE server2_testdb
UPDATE tbl_sample ...
script 3: USE server3_testdb
UPDATE tbl_sample ...
Is there a way to employ the 'USE' statement dynamically? Is there a way to
pass a variable to the USE statement such as:
DECLARE @.sampledb sysname
SELECT @.sampledb = master.sysdatabases.name.........
USE @.sampledb
Help highly appreciated.Looks to me like you should do this with SMO (SQL 2005) or DMO (earlier).
In both cases, your connect strings will determine which server/DB to use.
At that point, it's all SQL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:24723016-1F59-4B31-A779-988ECA6EA444@.microsoft.com...
I want to run a .sql script that updates data from specific tables using
OSQL. The problem i am having is that i need to run the same script on 300
servers. The database naming scheme are as follows:
server 1 : server1_testdb
server 2 : server2_testdb
server 3: server3_testdb
Question:
How can I write one script that can be used for all 300 servers. All
databases have the same structure and are identical except that it resides
in
300 servers that is where the prefix (server1_, server2_, etc.) come from.
Does it mean I have to write 300 scripts to specify the database where the
script will be run.
Ex.
script 1: USE server1_testdb
UPDATE tbl_sample ...
script 2: USE server2_testdb
UPDATE tbl_sample ...
script 3: USE server3_testdb
UPDATE tbl_sample ...
Is there a way to employ the 'USE' statement dynamically? Is there a way to
pass a variable to the USE statement such as:
DECLARE @.sampledb sysname
SELECT @.sampledb = master.sysdatabases.name.........
USE @.sampledb
Help highly appreciated.|||If you have SQL Server 2005 among your servers and all your servers are
visible from the server with SQL Server 2005 installed use SQLCMD
command-line utility and its features combined with some simple Windows
shell commands.
Create a sql script (save it as script.sql) like:
USE $(dbname)
GO
UPDATE ... -- put your statement here
Create simple Windows batch (save it as batch.bat in the same location as
script.sql) like:
for /L %%n IN (1,1,300) DO sqlcmd -S server%%n -E -v
dbname=server%%n_testdb -i script.sql
Then just run the batch and you should get what you want. I assume that you
have the same Windows login on all SQL Server instances.
Let me know if it works.
--
Regards
Pawel Potasinski
[http://www.potasinski.pl]
U¿ytkownik "morphius" <morphius@.discussions.microsoft.com> napisa³ w
wiadomo¶ci news:24723016-1F59-4B31-A779-988ECA6EA444@.microsoft.com...
>I want to run a .sql script that updates data from specific tables using
> OSQL. The problem i am having is that i need to run the same script on
> 300
> servers. The database naming scheme are as follows:
> server 1 : server1_testdb
> server 2 : server2_testdb
> server 3: server3_testdb
> Question:
> How can I write one script that can be used for all 300 servers. All
> databases have the same structure and are identical except that it resides
> in
> 300 servers that is where the prefix (server1_, server2_, etc.) come from.
> Does it mean I have to write 300 scripts to specify the database where the
> script will be run.
> Ex.
> script 1: USE server1_testdb
> UPDATE tbl_sample ...
> script 2: USE server2_testdb
> UPDATE tbl_sample ...
> script 3: USE server3_testdb
> UPDATE tbl_sample ...
> Is there a way to employ the 'USE' statement dynamically? Is there a way
> to
> pass a variable to the USE statement such as:
> DECLARE @.sampledb sysname
> SELECT @.sampledb = master.sysdatabases.name.........
> USE @.sampledb
> Help highly appreciated.
>

Dynamic SQL vs OSQL

Question for the experts here:

Is there any advantage of running an SQL statement through osql with the database information over using dynamic sql?

Example:

DECLARE @.DB Varchar(50)
DECLARE @.SQL Varchar(4000)
SET @.DB = '<nvr_changing_server>.<my_dynamic_db_name>'
SET @.SQL = 'SELECT admissiontype_id AS atype, admissiontype AS atype_desc, start_date, end_date
INTO tlkAdmitType
FROM <nvr_changing_server>.<nvr_changing_DB>.dbo.tlkAdmissionTypes'

exec master..xp_cmdShell 'osql -U sa -P sapwd -S nvr_changing_server -d my_dynamic_db_name -Q @.SQL...'

vs. something like:

DECLARE @.DB Varchar(50)
DECLARE @.SQL Varchar(4000)
SET @.DB = '<nvr_changing_server>.<my_dynamic_db_name>'
SET @.SQL = 'SELECT admissiontype_id AS atype, admissiontype AS atype_desc, start_date, end_date
INTO ' + @.DB +'.dbo.tlkAdmitType
FROM <nvr_changing_server>.<nvr_changing_DB>.dbo.tlkAdmissionTypes'

EXEC(@.SQL)

The purpose of all this is...I need to pass a parameter for the DB that I will be inserting into...here we create a new db with a specific name based on quarterly data. We collect, crunch, validate data and ship it. Then when it's old we archive it then eventually delete it.

I have written a script that makes this quarterly build less painful. In fact I won't have to do it! :)...our Sr. Data Analysts will do it now. In order for this beautiful thing (*in my mind anyway*) to work they need to set parameters for which data to pull and where to put it. The DB is scripted into existance and the data is moved into it. So therefore they need to enter the Qtr,Yr and dbname. I have done DSQL before on smaller scripts and I am just curious if the expert pool here can shed some light on this approach. The script will most likely be run in a DTS SQL Task.

Thanks in advance...RI would go for the second option. It will not require you to open up xp_cmdshell to all users (bad security hole). Also, I think the error handling is better with the second option. I am not sure how to get an error back from option 1.