how to use variable to reference database names

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how to use variable to reference database names

Author

Topic

foru123
Starting Member

6 Posts

Posted - 2006-01-07 : 23:03:05

I need to have a query like below to run to select all the fields from different databases that each database have a table named 'table1'

select * from test...table1

select * from test1..table1

I have the following code sample:

declare @dbname varchar(50)
set @dbname='test'
select @dbname
select * from @dbname..table1

However I received the error messge when I ran this code. Can anyone help to resolve this?

Thank you!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-07 : 23:20:25

you can't use variable to reference database name or even table name. But you can do this using dynamic sql.

declare @dbname varchar(50)
set @dbname='test'
select @dbname
exec ('select * from ' + @dbname + '..table1')

-----------------
[KH]

foru123
Starting Member

6 Posts

Posted - 2006-01-08 : 00:52:09

Thanks for the reply,Khtan!
If I modify the following code and how to assign the exe result to @count variable?
declare @dbname varchar(50), @count int
set @dbname='test'
select @dbname
exec ('select count(*) from ' + @dbname + '..table1')

I want to
set @count=exec('select count(*) from ' + @dbname + '..table1')
Tried couple of ways, did not work.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-08 : 03:33:53

you will need to use sp_executesql. It will allow passing of variable as parameters into the query. Check out the syntax from SQLServer Books OnLine

-----------------
[KH]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-08 : 03:53:23

also refer to
http://www.sqlteam.com/item.asp?ItemID=4599 and http://www.sqlteam.com/item.asp?ItemID=4619

-----------------
'KH'
2006

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-09 : 01:49:21

Also refer this
http://www.nigelrivett.net/SQLTsql/sp_executesql.html

Madhivanan

Failing to plan is Planning to fail

foru123
Starting Member

6 Posts

Posted - 2006-01-09 : 16:21:34

Madhivanan & Khtan:

Thank you very much! You guys pointed me to the right direction and helped me resolved the problem in a big time.

I appreciate it.

quote:
Originally posted by madhivanan

Also refer this
http://www.nigelrivett.net/SQLTsql/sp_executesql.html

Madhivanan

Failing to plan is Planning to fail

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-09 : 16:24:33

Why do you need to do this dynamically in T-SQL? You will receive a performance hit and also weaker security. Why not just change databases from within your application such as via the connection string?

Tara Kizer
aka tduggan

foru123
Starting Member

6 Posts

Posted - 2006-01-09 : 19:36:20

This is due to hundreds of databases involved. How can I change a connection string to do that?

I like your favorite quote:I hate people. However I do love those people like you,madhivanan, Khtan who provided invaluable info to help me from not getting fired.

quote:
Originally posted by tkizer

Why do you need to do this dynamically in T-SQL? You will receive a performance hit and also weaker security. Why not just change databases from within your application such as via the connection string?

Tara Kizer
aka tduggan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-09 : 19:44:53

I'll use an application that I have written that does similar to what I referred to. I have a central database that has all of the other servers that I need to connect to. I load this information into a DataTable, then loop through it. During the loop, I connect to each server and run various queries. I do this by modifying my connection string each time. Here is some sample code, note that this is to connect to different servers and not different databases (the code can be adapted for different databases):


        For Each dr As SomeDataSet.SomeRow In dt.Rows
            ' Build the connection string
            Dim connStr As String
            connStr = "Data Source=" & dr.ServerName & ";" & _
                      "Application Name=AppName;Connection Timeout=30;" & _
                      "Initial Catalog=SomeDB;Persist Security Info=False;"
            If dr.AuthType = "SQL" Then
                Dim pwd As Encryption64 = New Encryption64
                connStr = connStr & "User Id=" & drConnectionString.LoginName & ";" & _
                          "Password=" & pwd.DecryptFromBase64String(dr.LoginPassword, DataAccess.EncryptionKey) & ";"
                pwd = Nothing
            Else
                connStr = connStr & "Integrated Security=True;"
            End If
            'Call a sub-routine here, pass in connStr
        Next

Tara Kizer
aka tduggan

foru123
Starting Member

6 Posts

Posted - 2006-01-09 : 20:45:53

Great suggestions, I will need to have something like this soon.
Thanks a lot.

quote:

Originally posted by tkizer

I'll use an application that I have written that does similar to what I referred to. I have a central database that has all of the other servers that I need to connect to. I load this information into a DataTable, then loop through it. During the loop, I connect to each server and run various queries. I do this by modifying my connection string each time. Here is some sample code, note that this is to connect to different servers and not different databases (the code can be adapted for different databases):


        For Each dr As SomeDataSet.SomeRow In dt.Rows
            ' Build the connection string
            Dim connStr As String
            connStr = "Data Source=" & dr.ServerName & ";" & _
                      "Application Name=AppName;Connection Timeout=30;" & _
                      "Initial Catalog=SomeDB;Persist Security Info=False;"
            If dr.AuthType = "SQL" Then
                Dim pwd As Encryption64 = New Encryption64
                connStr = connStr & "User Id=" & drConnectionString.LoginName & ";" & _
                          "Password=" & pwd.DecryptFromBase64String(dr.LoginPassword, DataAccess.EncryptionKey) & ";"
                pwd = Nothing
            Else
                connStr = connStr & "Integrated Security=True;"
            End If
            'Call a sub-routine here, pass in connStr
        Next

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources