Use database name dynamically - Use @dbname

Question

Use database name dynamically - Use @dbname

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
August 20, 2010 at 3:34 pm

#237072

I am trying to make use of databasename dynamicall so that the sql script can loop through different databases.
declare @dbname varchar(10)
select @dbname = name from sys.databases
where name like'test'
Use @dbname
select top 10 * from dbo.all_names
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '@dbname'.
Please advice...

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Dynamic SQL's the only way to do that and once the USE is in dynamic, everything to be done in the DB needs to be as well.

What are you trying to do? There may be a better way.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

so Gail does it mean that i cant use Use @dynamicvariable?. I have a control server and i want to push all the ddl/dml changes to other servers from this server instead of physically logging into each one. I do have linked servers set up from my control server to all other servers. Any thoughts?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

If you have management studio 2008 (regardless of the database engine version), you can do multi-server queries. Other than that, Redgate has a multi-server query tool iirc.

You can use the SQLCMD mode to connect to servers and run code. I've before now written stored procs to generate SQLCMD mode scripts for running code on multiple servers

But, yes, as I said, dynamic SQL's the only way to do a USE with a variable and once the USE is in dynamic, everything to be done in the DB needs to be as well.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 4

Thanks Gail. I still have few questions,

i) How can i use "Use @dynamicvariable" please provide the script if you have.

ii) could you give some more detail on how can i run query from 2008 client across multiple servers.

Thanks again for your help.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 5

Gail from my previous post i got the second part by using 2008 but does only server level. Still need an explanation for the first part..Thanks again...

Derrick Smith SSChampion Points: 10833 More actions · Answer 6

You would need to do something like

declare @dbname nvarchar(255)

declare @sql nvarchar(500)

select @dbname = 'msdb' --Set up your cursor here to insert into this variable

select @sql = 'use ['+@dbname+']

select top 1000 * from sysjobhistory

'

exec(@sql)

Context is reverted back to the original DB once the query is executed.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 7

curious_sqldba

SSC-Dedicated

Points: 36494

August 24, 2010 at 9:16 am

#1211094

awesome, this is wonderfull. Thanks

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 8

all right another pbm now. Create table works fine but create/alter procedure doesn't work. Can you please take a look at the script which i have attached. Basically i have grouped the servers in 2008 client and want to use this script to loop across different dbs and run the scripts. Create table works, but doesnt work for create/alter sp's, views. Please take a look..thanks

jvanderberg SSCertifiable Points: 6762 More actions · Answer 9

You have a BEGIN in your stored procedure code, but no END. That is probably your error.

One of the problems with dynammic SQL is that it can sometimes be difficult to debug...

jvanderberg SSCertifiable Points: 6762 More actions · Answer 10

Have you thought about using replication? It seems like that's what you're trying to do...

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 11

jvanderberg (8/24/2010)
You have a BEGIN in your stored procedure code, but no END. That is probably your error.
One of the problems with dynammic SQL is that it can sometimes be difficult to debug...

I did test with end also same error "'CREATE/ALTER PROCEDURE' must be the first statement in a query batch."

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 12

jvanderberg (8/24/2010)
Have you thought about using replication? It seems like that's what you're trying to do...

no replication cannot be an option, firstly setup is complicated as i am talking about 200 gb dbs with around 20-30 secondly maintenance would be an overhead. I did find one sure shot solution but is little lengthy , trying to find a better one. Thanks

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 13

As the error states, create/alter procedure must be the first statement in the batch. In your dynamic query, you need to separate the USE from the CREATE/ALTER with a batch separator.

You need to add a GO after the USE statement and before the CREATE/ALTER.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 14

Jeffrey Williams-493691 (8/24/2010)
As the error states, create/alter procedure must be the first statement in the batch. In your dynamic query, you need to separate the USE from the CREATE/ALTER with a batch separator.
You need to add a GO after the USE statement and before the CREATE/ALTER.

Thanks . Tried doesn't work, same error. :unsure: