I have database called 'test1' and when i use the code below im getting this error
Server: Msg 170, Level 15, State 1, Line 9
Line 11: Incorrect syntax near '@.dbName'.
plz help, here is the code
DECLARE @.dbName varchar(50)
DECLARE @.index int
SET @.index = 1
SET @.dbName ='test'
set @.dbName = @.dbName + CAST(@.index as varchar)
use @.dbNameu cannot do that. if u want to select rows from table1 of test1 database
select * from test1..table1
or u can use dynamic sql like
DECLARE @.dbName varchar(50)
DECLARE @.index int
SET @.index = 1
SET @.dbName ='test'
set @.dbName = @.dbName + CAST(@.index as varchar)
exec ('select * from ' + @.dbName + '..table1')
remember dynamic SQLs r having security issues if not managed carefully|||thanks for replying. but i want to use 'use' syntax not 'exec'
this works fine
use test1
select * from table1
but when i use this i get the error
use 'test' + '1'
select * from table1|||Everything Upsalen has said is correct. His code is equivalent to what you requrest.
If you really must use USE then:
DECLARE @.dbName varchar(50)
DECLARE @.index int
SET @.index = 1
SET @.dbName ='test'
set @.dbName = @.dbName + CAST(@.index as varchar)
exec ('USE ' + @.dbname + ' GO select * from table1')|||The reason i want to use 'USE' is i have around 20 database (test1,test2,test3,...test20) with same schema, everytime when i make update or fix scripts i have to run it 20 times. now i want to use loop and run it once for all the databases.
here is what the code looks like...
DECLARE @.dbName varchar(50)
DECLARE @.index int
DECLARE @.NUM_OF_DB int
SET @.NUM_OF_DB = 20
SET @.dbName = 'test'
set @.index = 1
while @.index <= @.NUM_OF_DB
begin
set @.dbName = dbName + CAST(@.index as varchar)
USE @.dbName
-- paste here the scripts (script is thousands of lines long)
set @.index = @.index + 1
SET @.dbName = 'test'
end
or if you know easy way to do this plz ...|||yes, i know easy way to do this
if you have 20 databases which are essentially identical in structure (they would have to be if you can run the same thousand-line script on their tables), then just combine them into one database and vwalah, your USE problem goes away
:)|||Rudy's suggestion. My Code. Upsalen's code. Your code does not, and cannot, work. Unless perhaps you write something in .NET or similar and execute it from there. You cannot do what you want to do from QA\ SSMS.|||yes, i know easy way to do this
if you have 20 databases which are essentially identical in structure (they would have to be if you can run the same thousand-line script on their tables), then just combine them into one database and vwalah, your USE problem goes away
:)
c'mon......i have good reason not to do that|||okay, you have good reason
how about this: write 20 scripts, each with a different USE, each calling the same common script
:cool:|||You may be able to write some code using ADO\ ADO.NET - execute your script, looping through the various databases, changing the database at each pass.
I suspect that Rudy's point is that if you have 20 databases of all identical schemas then perhaps your design is flawed... Perhaps it isn't. But perhaps it is.|||You may be able to write some code using ADO\ ADO.NET - execute your script, looping through the various databases, changing the database at each pass.
I suspect that Rudy's point is that if you have 20 databases of all identical schemas then perhaps your design is flawed... Perhaps it isn't. But perhaps it is.
about design, 20 of them are same database. i just use them for training, testing,etc which means at the end it is one database.
where can i find code for ADO\ ADO.NET.|||were can i find code for ADO\ ADO.NET.You'll have to write it I am afraid. I don't have time to come up with any right now.|||I suspect that Rudy's point is that if you have 20 databases of all identical schemas then perhaps your design is flawed... Perhaps it isn't. But perhaps it is.well, yes, that was my point in post #6
but then, after having been assured that there is good reason for 20 databases, i put forth in post #9 a modest programming suggestion (not requiring ADD or whatever that was)
this suggestion, had it been undertaken, would have solved the problem elegantly
and about half an hour ago
:)|||well, yes, that was my point in post #6
but then, after having been assured that there is good reason for 20 databases, i put forth in post #9 a modest programming suggestion (not requiring ADD or whatever that was)
this suggestion, had it been undertaken, would have solved the problem elegantly
and about half an hour ago
:)
okey im just looking for easy way. how can i call common script?
to explain about db design i have one database copied 20 times for different purpose.|||ADO - ActiveX Data Objects
not to be confused with Data Access Objects which are totally different ;)
Yes - I missed the calling thingy - probably easier than ADO.|||another simple way to do this is to write a .bat file that calls osql.exe or sqlcmd.exe 20 times with the same script, each time connecting to a different database.|||another simple way to do this is to write a .bat file that calls osql.exe or sqlcmd.exe 20 times with the same script, each time connecting to a different database.
nice one i will give it a try|||Just wondering, does
DECLARE @.dbName varchar(50)
SET @.dbName = 'test'
USE @.dbName
Work?
If so, I might have a suggestion :p|||Just wondering, does
DECLARE @.dbName varchar(50)
SET @.dbName = 'test'
USE @.dbName
Work?
If so, I might have a suggestion :pNope :)|||another simple way to do this is to write a .bat file that calls osql.exe or sqlcmd.exe 20 times with the same script, each time connecting to a different database.
as your suggestion i write simple batch file and its working great just the way i want it. thank you men you saved me lot of time.
here is what the batch file looks like
SET YOGI_HOME=%cd%
SET /P IN_PUT_FILE=Script file name:
SET OUT_PUT_FILE=out.txt
SET NUM_DB=20
SET i=1
.
.
.
:Loop
Echo Executing script file %IN_PUT_FILE% on test%i%. . .
cd C:\Program Files\Microsoft SQL Server\80\Tools\Binn\
isqlw -s [Local] -d [test%i%] -E -u [sa] -p [password] -i [%YOGI_HOME%\%IN_PUT_FILE%] -o [%YOGI_HOME%\%OUT_PUT_FILE%]
.
.
.
