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

Echo for SQL scripts

Hi all,

I want to see input SQL statements in the log file when I run the script in SQLPlus. I have used this set command "SET ECHO ON" for this. However, the log file looks like this -

drop table table_A
*
ERROR at line 1:
ORA-00942: table or view does not exist

7444 rows deleted.

Commit complete.

Thus, the SQL statement is not visible if it is error free. Is there a way to get around this?

ThanksDid you try to SPOOL the results?
This will do it:
SET ECHO ON
SPOOL logfile.log
@.MyScript
SPOOL OFF
;)|||I added these commands in my script -

set echo on
spool log_file.log
@.script_name.sql
spool off

the log file only had this error message -

SP2-0309: SQL*Plus command procedures may only be nested to a depth of 20.|||I added these commands in my script -

set echo on
spool log_file.log
@.script_name.sql
spool off

the log file only had this error message -

SP2-0309: SQL*Plus command procedures may only be nested to a depth of 20.

This error means what it means: your script executes a script that executes a script ...etc upto more that 20 levels deep.
:rolleyes:

Easy User Question

I have a user set up on a SQL 200 server, which I am using to connect to a
database from an ASP.NET application I am making.
When I run the application from the same system as the database is on, the
connection works correctly ( my connection string is:
user id=BlueMaster;data source=BLUE;initial
catalog=BlueSky;password=bluerocks
)
but when I try to connect when running the application off another system,
the connection fails. What do I have to do to set up the user so the
connection will work from remote systems'
ThanksCheck you are using SQL server and Windows authentication in SQL Server (the
defualt is Windows only)
Do this from Enterprise manager - right click the server name, go to
properties and the Security tab. If you do have to change the mode you will
be prompted to restart SQL Server - this is necessary for the security
setting to take effect. If this is not the problem, can you please post the
error message coming back
"Ryan" <web@.balancestudios.com> wrote in message
news:e5321K71DHA.1100@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I have a user set up on a SQL 200 server, which I am using to connect to a
> database from an ASP.NET application I am making.
> When I run the application from the same system as the database is on, the
> connection works correctly ( my connection string is:
> user id=BlueMaster;data source=BLUE;initial
> catalog=BlueSky;password=bluerocks
> )
> but when I try to connect when running the application off another system,
> the connection fails. What do I have to do to set up the user so the
> connection will work from remote systems'
> Thanks
>

---

|||What error message do you get when it fails?
Rand
This posting is provided "as is" with no warranties and confers no rights.

Easy Questions - get me started

I have 3 retail sites running MSDE and I want to aggregate sales data at a
separate SQL Server. I run a VPN and I guess I need to use merge replication
to a full SQL Server Database. My questions are
1. Is the central server the publisher or subscriber
2. The three sites tables are identical and I would like a siteID added at
the table in the central server to distinguish which the data came from
3. If the central server is the publisher will unwanted data from one site
flow across to another.
4. Is what I am asking clear enough and is merge replication appropriate
For merge, the central SQL Server should be the publisher. If you filter
your articles by site name data will not flow to the other subscribers. For
your version of sql server, yes merge is the most appropriate.
However, does data only flow centrally? If so, transactional replication
would be faster, not require a guid column on each table (assuming you have
pks on each table). This would require at least sql server standard edition
everywhere.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Steve Hall" <SteveHall@.discussions.microsoft.com> wrote in message
news:A3AF25D9-8D4B-4C0E-80B6-E95ED675E82E@.microsoft.com...
>I have 3 retail sites running MSDE and I want to aggregate sales data at a
> separate SQL Server. I run a VPN and I guess I need to use merge
> replication
> to a full SQL Server Database. My questions are
> 1. Is the central server the publisher or subscriber
> 2. The three sites tables are identical and I would like a siteID added
> at
> the table in the central server to distinguish which the data came from
> 3. If the central server is the publisher will unwanted data from one site
> flow across to another.
> 4. Is what I am asking clear enough and is merge replication appropriate
|||Steve pls look at this:
http://www.replicationanswers.com/CentralSubscriberArticle.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||You guys rock!
That is two great replies within 30 minutes of posting.
Steve
"Paul Ibison" wrote:

> Steve pls look at this:
> http://www.replicationanswers.com/CentralSubscriberArticle.asp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>

Easy Date Problem

I am somewhat new to SQL and was trying to subtract 11 days from today
but I want it to go to 00:00:00 rather than 11 days whatever time it is
I run it.
Example: It is 8:39 AM right now and when I run
where completiondate BETWEEN getdate()-11 and getdate()-5
I want the result to be xx/xx/xx 00:00:00 rather than xx/xx/xx 8:39.
Thanks!
BVDECLARE @.dt SMALLDATETIME
SET @.dt = DATEDIFF(DAY,0,GETDATE())
SELECT @.dt
SELECT ...
WHERE CompletionDate >= (@.dt - 11)
AND CompletionDate < (@.dt - 5)
(Do not use between here, unless CompletionDate *only* stores the date with
time always set to midnight, and if you want to include rows from 5 days ago
in the result.)
<brentkelli@.gmail.com> wrote in message
news:1130420498.749859.291940@.g49g2000cwa.googlegroups.com...
>I am somewhat new to SQL and was trying to subtract 11 days from today
> but I want it to go to 00:00:00 rather than 11 days whatever time it is
> I run it.
> Example: It is 8:39 AM right now and when I run
> where completiondate BETWEEN getdate()-11 and getdate()-5
> I want the result to be xx/xx/xx 00:00:00 rather than xx/xx/xx 8:39.
> Thanks!
> BV
>|||How is about?
SELECT <columns> FROM Table WHERE completiondate
>=CAST(FLOOR(CAST(DATEADD(day,-11,GETDATE())AS FLOAT))AS DATETIME)AND
<=CAST(FLOOR(CAST(DATEADD(day,-11,GETDATE())AS FLOAT))AS DATETIME)
<brentkelli@.gmail.com> wrote in message
news:1130420498.749859.291940@.g49g2000cwa.googlegroups.com...
>I am somewhat new to SQL and was trying to subtract 11 days from today
> but I want it to go to 00:00:00 rather than 11 days whatever time it is
> I run it.
> Example: It is 8:39 AM right now and when I run
> where completiondate BETWEEN getdate()-11 and getdate()-5
> I want the result to be xx/xx/xx 00:00:00 rather than xx/xx/xx 8:39.
> Thanks!
> BV
>

Dynamically switching report data connection at run time

We have been presented with the problem of using Reporting Services to run
the same report against one of two databases.
We are currently using SQL Server 2000, and while 2005 has support for
dynamically building connection strings with parameters, 2000 apparently does
not.
Thus far, we have found the following solutions:
- A custom data processing extension, which wraps up a SqlConnection, and
switches database context at run-time based on an expected query parameter
- Reporting against a front-end query on the database, which in turn calls
the query from the desired database
- Installing two versions of the same report on the report server, and
having the application choose which to execute at run-time.
Each of these options has various drawbacks, the first brings with it a mess
of support and deployment issues, the second leans on the database harder
than it needs to, and the third is basically redundant.
Although the solution we need now is to switch between one of two databases,
the ideal solution would be able to manage 1-n databases.
We would appreciate your input as to which solution is the best, or if there
is functionality which would better suit our needs that we havenâ't discovered
yet.On Nov 16, 4:07 pm, breedReed <br...@.community.nospam> wrote:
> We have been presented with the problem of using Reporting Services to run
> the same report against one of two databases.
> We are currently using SQL Server 2000, and while 2005 has support for
> dynamically building connection strings with parameters, 2000 apparently does
> not.
> Thus far, we have found the following solutions:
> - A custom data processing extension, which wraps up a SqlConnection, and
> switches database context at run-time based on an expected query parameter
> - Reporting against a front-end query on the database, which in turn calls
> the query from the desired database
> - Installing two versions of the same report on the report server, and
> having the application choose which to execute at run-time.
> Each of these options has various drawbacks, the first brings with it a mess
> of support and deployment issues, the second leans on the database harder
> than it needs to, and the third is basically redundant.
> Although the solution we need now is to switch between one of two databases,
> the ideal solution would be able to manage 1-n databases.
> We would appreciate your input as to which solution is the best, or if there
> is functionality which would better suit our needs that we haven't discovered
> yet.
I would personally create a SQL Server instance that has Linked
Servers to your two other databases, then in reporting services pass
the SELECT * FROM OPENQUERY( @.ServerName, 'SELECT real SQL here' )
-- Scott

Dynamically naming a report

Is there a way to dynamically rename a report? For example if I schedule a report to run every day and I want the report names to be like Report_Dec01_2006, Report_Dec02_2006, etc. This way the users can see which days data is in each of the report.

Any way to handle this?

Thx

Does anyone else have a need for this? Anyone from Microsoft , plz respond!

Dynamically naming a report

Is there a way to dynamically rename a report? For example if I schedule a report to run every day and I want the report names to be like Report_Dec01_2006, Report_Dec02_2006, etc. This way the users can see which days data is in each of the report.

Any way to handle this?

Thx

Does anyone else have a need for this? Anyone from Microsoft , plz respond!

Dynamically Create Connection Managers @ Run time

Is there a way to dynamically create a connection manager @. run time? I would like to do this from a data set of connection strings so I can link them into a union all component.

No. You cannot change package structure at run-time. You can dynamically read information in at run-time, so you can change your connection string for example. The best method is the build in Configuration support. You can also drive most properties through Expressions and supply a variable to set the property. Variables can be set in several ways, including other expressions and script tasks.

To load data from multiple sources, try using the For Each Loop, and drive this off your list of "connections". This can contain a data flow task, the connection of which can be updated on each loop iteration.

Try this article to give you an idea of looping with recordsets.

Shredding a Recordset
(http://www.sqlis.com/default.aspx?59)

Dynamically collapsing textboxes (and adjusting layout)

Just curious if there's a way I can remove a field from my report at run time and shift all the fields underneath it up.

I basically want to end up with the following:

Design time=============================Field 1: Fields!Field1.ValueField 2: Fields!Field2.Value// This one will be blankField 3: Fields!Field3.ValueField 4: Fields!Field4.ValueRun Time - eliminate any blank fields=============================Field 1:"Data 1"Field 3:"Data 3"Field 4:"Data 4"

Problem is, if I set the visibility of the field to false, it still takes up space on the form (as it should). Any suggestions as to how to shift all the fields up without using a table?

Thanks!

I guess I should clairify a bit

This is what is happening now:

Run Time - eliminate any blank fields=============================Field 1:"Data 1"Field 3:"Data 3"Field 4:"Data 4"I want to collapse Field 2 up so that thereis no whitespace between 1 and 3

Not sure if this is even possible...

Dynamically changing SQL ConnectionString for live/test environments

Hello. What I'm trying to do should be fairly simple, but after an hour of searching and trials, I can't seem to get it working.

When I run my website on my test machine, I would like it to use a certain value for my connectionString. But when it's run on the live server, it should use a different connection string. This way, when it's on the test server it'll use the test database.

In my Web.Config file, I have this:

<connectionStrings>
<add name="myConnectionString" connectionString="Data Source=TEST_DB;Initial Catalog=tester;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>

I would like to be able to change the connectionString in something like the Global.asax file, when the application is loaded. I'd check for a certain environment variable, or some other condition, and change the value of the connectionString.

I tried this in my Global.asax, but it told me the value was read_only:

ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString = "Data Source=LIVE_DB;Initial Catalog=live;Integrated Security=True"

Does anyone have a good way of doing this, so I don't need to juggle Web.Config files when I publish my site?

Thanks.

You can have a separate app.config file for production or have a separate configuration section so that when you build the application (deploy), Visual Studio will replace the connection strings

Take a look at this post (http://msdn2.microsoft.com/en-us/library/bb164473(VS.80).aspx)

|||

After a more varied search, I came across the Web Deployment Projects tools.

http://msdn2.microsoft.com/en-us/asp.net/Aa336619.aspx

Now I've got it simply including a different piece of config code when it builds the website in either Debug or Release mode.

Thanks for the reply, though. I'm all set now.

Dynamically Add Data to Crystal Report from Different Tables

Hi All
I am sending query to the Crystal report which i have designed.
but at design time i am using table1.
At run time i am sending query which contains table2 .
Table1 and Table2 are same it differs only in data, all fields are same.
Can i send the query to crystal report at run time if my crystal report(at design time) is using group by field at design time.
It's giving error of HResult exception.
Can we provide Multiple Tables Data at dynamically???
I am using crystal report 8.5 n SQL Server 2000

Plz Help me if anybody knows abt it.

Thanks in Advance

Regards
Henry JonesHi,

Yes u can, tell me whats u r Client Application is made of
Claasic VB, VS 2003/2005 Languages ???

Favaz|||See if you find code samples here
http://support.businessobjects.com/

Dynamic Variables in DTS packages.

Hi,
I have built a DTS package to load a file into the database. I want the
package to be such that it can be run on any database. So, I tried
defining a Global Variable by creating a "Global Variable Task" and
creating a global variable for the "Initial Catalog" Property. But when
I try to run this package from the command line using the dtsrun
utility, the global variable is discarded and data is loaded into the
Database specified when I create the DTS. Is there a way to resolve
this? I also tried assigning it an Environmental Variable instead of
the Global Variable but it didn't work.
Thanks,
KPHi
There is no Global Variables task! Do you have a dynamic properties task to
assign the global variable to the property? Do you have workflow in place to
force the dynamic properties task to execute before the task for which the
properties are changed?
John
"kpraoasp@.yahoo.com" wrote:

> Hi,
> I have built a DTS package to load a file into the database. I want the
> package to be such that it can be run on any database. So, I tried
> defining a Global Variable by creating a "Global Variable Task" and
> creating a global variable for the "Initial Catalog" Property. But when
> I try to run this package from the command line using the dtsrun
> utility, the global variable is discarded and data is loaded into the
> Database specified when I create the DTS. Is there a way to resolve
> this? I also tried assigning it an Environmental Variable instead of
> the Global Variable but it didn't work.
> Thanks,
> KP
>

Dynamic Variables in DTS packages.

Hi,
I have built a DTS package to load a file into the database. I want the
package to be such that it can be run on any database. So, I tried
defining a Global Variable by creating a "Global Variable Task" and
creating a global variable for the "Initial Catalog" Property. But when
I try to run this package from the command line using the dtsrun
utility, the global variable is discarded and data is loaded into the
Database specified when I create the DTS. Is there a way to resolve
this? I also tried assigning it an Environmental Variable instead of
the Global Variable but it didn't work.
Thanks,
KPHi
There is no Global Variables task! Do you have a dynamic properties task to
assign the global variable to the property? Do you have workflow in place to
force the dynamic properties task to execute before the task for which the
properties are changed?
John
"kpraoasp@.yahoo.com" wrote:
> Hi,
> I have built a DTS package to load a file into the database. I want the
> package to be such that it can be run on any database. So, I tried
> defining a Global Variable by creating a "Global Variable Task" and
> creating a global variable for the "Initial Catalog" Property. But when
> I try to run this package from the command line using the dtsrun
> utility, the global variable is discarded and data is loaded into the
> Database specified when I create the DTS. Is there a way to resolve
> this? I also tried assigning it an Environmental Variable instead of
> the Global Variable but it didn't work.
> Thanks,
> KP
>

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]
Uytkownik "morphius" <morphius@.discussions.microsoft.com> napisa w
wiadomoci 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 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 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 i
n
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]
Uytkownik "morphius" <morphius@.discussions.microsoft.com> napisa w
wiadomoci 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 security context

Does a query thats executed as a dynamic SQL run under a different security
context other than the caller ?No, that will be possible in sql 2005.
HTH, Jens Suessmeyer.
"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:OhZ6a11SFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Does a query thats executed as a dynamic SQL run under a different
> security
> context other than the caller ?
>|||Hassan wrote:
> Does a query thats executed as a dynamic SQL run under a different
> security context other than the caller ?
No. Same security context. But the caller must have rights to the
underlying objects. If, for example, you execute a dynamic SQL command
from a stored procedure that updates the Customer table, the caller must
have update rights on the Customer table. Simply granting execute rights
to the proc is not enough.
David Gugick
Imceda Software
www.imceda.com|||Depend. When you execute a stored procedure, the sp is executed under the
security context of the sp owner (first sql server check if the caller has
right to execute the sp), but if this sp use dynamic sql, then the batch
being executed using EXEC() or sp_executesql is executed under the security
context of the caller.
AMB
"Hassan" wrote:

> Does a query thats executed as a dynamic SQL run under a different securit
y
> context other than the caller ?
>
>

dynamic SQL security context

Does a query thats executed as a dynamic SQL run under a different security
context other than the caller ?
No, that will be possible in sql 2005.
HTH, Jens Suessmeyer.
"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:OhZ6a11SFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Does a query thats executed as a dynamic SQL run under a different
> security
> context other than the caller ?
>
|||Hassan wrote:
> Does a query thats executed as a dynamic SQL run under a different
> security context other than the caller ?
No. Same security context. But the caller must have rights to the
underlying objects. If, for example, you execute a dynamic SQL command
from a stored procedure that updates the Customer table, the caller must
have update rights on the Customer table. Simply granting execute rights
to the proc is not enough.
David Gugick
Imceda Software
www.imceda.com
|||Depend. When you execute a stored procedure, the sp is executed under the
security context of the sp owner (first sql server check if the caller has
right to execute the sp), but if this sp use dynamic sql, then the batch
being executed using EXEC() or sp_executesql is executed under the security
context of the caller.
AMB
"Hassan" wrote:

> Does a query thats executed as a dynamic SQL run under a different security
> context other than the caller ?
>
>

dynamic SQL security context

Does a query thats executed as a dynamic SQL run under a different security
context other than the caller ?No, that will be possible in sql 2005.
HTH, Jens Suessmeyer.
"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:OhZ6a11SFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Does a query thats executed as a dynamic SQL run under a different
> security
> context other than the caller ?
>|||Hassan wrote:
> Does a query thats executed as a dynamic SQL run under a different
> security context other than the caller ?
No. Same security context. But the caller must have rights to the
underlying objects. If, for example, you execute a dynamic SQL command
from a stored procedure that updates the Customer table, the caller must
have update rights on the Customer table. Simply granting execute rights
to the proc is not enough.
--
David Gugick
Imceda Software
www.imceda.com|||Depend. When you execute a stored procedure, the sp is executed under the
security context of the sp owner (first sql server check if the caller has
right to execute the sp), but if this sp use dynamic sql, then the batch
being executed using EXEC() or sp_executesql is executed under the security
context of the caller.
AMB
"Hassan" wrote:
> Does a query thats executed as a dynamic SQL run under a different security
> context other than the caller ?
>
>