Replace DB name runtime?? - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Replace DB name runtime??

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-05-30 : 01:25:40

Hi.

I posted similar stuff in one of my previous topic. what i need to do is parse database name runtime
means sample example is

USE <%1>
GO
SELECT * FROM Table_Name

here instead of <%1> i need to pass Test DB name

USE Test
GO
SELECT * FROM Table_Name

my .sql file contains <%1> so i need to replace it

i tried like

declare @t varchar(50)
declare @chg22 varchar(500)

set @t = 'Test'
set @chg22 = 'USE <%1> GO'
set @chg22 = REPLACE(@chg22,'<%1>',@t)

exec(@chg22)
SELECT * FROM Table_Name

but i am not getting it..in above example i am just took one line select * from table_name but actually in .sql file more that 300 lines are there so i m not able to concatenate it..

T.I.A

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-30 : 02:16:45

Don't put the USE statement in your file. Call the sql file from sqlcmd and specify the database name using the d switch.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-05-30 : 02:21:06

thanks for your kind reply

but i am calling this .sql from SSIS (ExecuteSQL Task)

T.I.A

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-30 : 02:23:35

SSIS can call batch files with sqlcmd commands just fine. I haven't used SSIS much so I can't comment on it much further though. For future posts, it would be best to mention your limitation up front first.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-05-30 : 02:34:39

it is possible to pass DB name runtime.. as my application get name from front end...

T.I.A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources