Database specific tags

Single-click script execution on multiple SQL Servers.

Moderators: JonathanWatts, Anu Deshpande, dlkj, Colin Millerchip, Michelle Taylor

Database specific tags

Postby Roust_m » Tue Apr 28, 2009 10:00 am

Hi,

I think it would be very useful to have a feature to automatically replace some text inside the TSQL script depending on the database it will run against. Suppose I have three databases called MyDB_US, MyDB_UK and MyDB_FR. The generic script for all of them would contain:

SET @JOBNAME = N'{country_code}_MyNewJob'
EXEC msdb.dbo.sp_add_job @job_name=@JOBNAME, ...

At the time of execution {country_code} tag would be replaced to "US" for MyDB_US, to "UK" for MyDB_UK, etc.

Are there any plans to implement such feature?

Thanks.
Roust_m
 
Posts: 4
Joined: Tue Apr 28, 2009 9:46 am

Postby Colin Millerchip » Fri May 01, 2009 9:33 am

Hi Roust_m,

Thanks for the feedback, which we'll consider for the next release.

Best regards,


Colin.
Colin Millerchip
 
Posts: 66
Joined: Wed Oct 31, 2007 5:31 pm
Location: Cambridge, UK

Postby Linda Hawksworth » Tue May 05, 2009 10:20 am

In the meantime, you can do what you want using T-SQL:

DECLARE @CURRENT_DB NVARCHAR(128) ;
DECLARE @COUNTRY_CODE NVARCHAR(2) ;

SET @CURRENT_DB = DB_NAME() ;
SET @COUNTRY_CODE = SUBSTRING(@CURRENT_DB, LEN(@CURRENT_DB) - 1, 2) ;
SELECT @CURRENT_DB AS 'Current DB',
@COUNTRY_CODE AS 'Country Code' ;

Let me know if this suits your requirements.
Linda Hawksworth
 
Posts: 192
Joined: Mon Jun 25, 2007 11:32 am

Postby Roust_m » Wed May 06, 2009 5:29 am

Yep, similar to what I am doing now, but this does not cover the whole thing, as there may be a job step parameter which is country specific.

E.g. for UK it would be:
exec spMySP 'Products','UK'
for France:
exec spMySP 'Produits','FR'
for China:
exec spMySP '產品','CN'

and the generic script would look like:
exec spMySP '{Product translation}','{Country code}'

therefore it should also handle unicode.

I understand that I can build a replacement table in the begining of the script. E.g.: if the last two characters of the database name equal to 'FR' then replace {Product translation} with 'Produits' and '{Country code}' with 'FR', but it would be easier to build separate scripts.

It would be ideal if the app could search for the tags and prompt me to set the replacements for each database I am running the script against.
Roust_m
 
Posts: 4
Joined: Tue Apr 28, 2009 9:46 am

Postby Brian Donahue » Wed May 06, 2009 11:22 am

Hello,

I have sent a link to your private messages to a build of Multi Script that can handle Unicode.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Multi Script 1

Who is online

Users browsing this forum: No registered users and 0 guests