calling server and db name from a metadata table

I would like to be able to have the Server name and
Database name query from a metadata table so I can use
that as the parameter for my stored procedure. So I don't
have to changes these parameters each time I move my
script to a different server server and/or databases. Is
that possible? Thanks in advance for your help. Daizy
exec
master.dbo.xp_cmdshell 'dtsrun /Smy_server /Umy_login /Pmyp
assword /Nmy_dts_pkg /Mdtspassword'Daizy,
For one possible meaning of your question, the only way to do this is to use
dynamic SQL, which pretty much abandons the security and performance reasons
for using store procedures. (Rule: There is no indirection in elements of
a SQL Server (database, table, column, etc) but they must be explicitly
coded.) In this case you would be trying to do something like:
Select * from @.MyServer.@.MyDatabase.dbo.Tablename
On the other hand, since you are showing an xp_cmdshell line, are you trying
to fill out that line with the name of the server and database that you are
currently running in? In that case, examine:
SERVERPROPERTY('servername')
DB_NAME()
These will give you the name of your current server and database.
Russell Fields
"Daizy" <anonymous@.discussions.microsoft.com> wrote in message
news:2cc7201c39404$db9ff860$a601280a@.phx.gbl...
> I would like to be able to have the Server name and
> Database name query from a metadata table so I can use
> that as the parameter for my stored procedure. So I don't
> have to changes these parameters each time I move my
> script to a different server server and/or databases. Is
> that possible? Thanks in advance for your help. Daizy
> exec
> master.dbo.xp_cmdshell 'dtsrun /Smy_server /Umy_login /Pmyp
> assword /Nmy_dts_pkg /Mdtspassword'