To get the db name as parameters

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » To get the db name as parameters

To get the db name as parameters

Rate Topic

Display Mode

Topic Options

Author

Message

arthi.anan

Posted Monday, February 11, 2013 3:56 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 6:23 AM
Points: 20, Visits: 55

Hi all,

I have a scenario like i have to create the same database for our different clients. The clients name is the db name. So i need to generate a .bat script to call the parameters and write a stored procedure to update the db name as clients name in the table_schema and stored procedure.
So when i run the script and manually enter the client id alone, the db should be created with the given name and as client id is the unique id it should get updated in all the tables.

I have the location of the db files like table_schema, stored_procedure etc in D:\db
Please help with sample script or template

thanks in advance

Post #1418296

OTF

Posted Monday, February 11, 2013 5:32 AM

SSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 10:37 AM
Points: 257, Visits: 3,722

arthi.anan (2/11/2013)

I think you might need to be a little clearer than that in describing your problem.
What tables is the client id to get "updated" in?
if you are creating a separate database for each client, why are you naming the stored procedures with the client id? How will you deal with places in your code where the stored procedures are referenced? Will you have to change that too?
As to creating a database with a differing names each time, I would think you would need to used dynamic sql.
You could store variable values in a table if you choose.

Post #1418342

Ed Wagner

Posted Monday, February 11, 2013 5:47 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:55 PM
Points: 1,884, Visits: 562

I'm not entire sure what you're asking, but it looks like you want to semi-automate some SQL creation scripts. If this is the case, then you might want to look at SQL templates instead of batch files. On a side note, I've used batch files for a great many things, but I'm always in favor of picking the right tool for the job. If the work needs to be done in the database, you should at least start by picking SQL and move to another tool if necessary.

Anyway, SQL templates are a really cool piece of functionality that allows you to write SQL scripts with defined parameters, replace them once and then run your script. They're found in SSMS under Template Explorer. See the examples or MSDN for syntax. When you're done, save the template to your hard drive. You can then open it any time and simply press Ctrl-Shift-M to bring up a template parameter replacement dialog. When you fill it in and click OK, SSMS will replace all the named parameters with the values you specify and you can then make further adjustments if you need to and run it.

If this isn't applicable to what you're asking, please post a clearer description of what you're trying to do. If this is what you're trying to do, I hope it helps.

Post #1418357

arthi.anan

Posted Wednesday, February 13, 2013 12:12 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 6:23 AM
Points: 20, Visits: 55

Actually we need to set up databases for our clients in which, the db name should be the clients name.
So instead of creating a new db everytime and changing the db name in each script, it can be automated as passing the db name as parameters in a batch script.
The script table_schema, procedure,uddtables,uddtypes holds
USE [dbname]

Is there any ways like when i run the same scripts for all clients, the db names in the scripts should automatically change to the clients name and create a new db with the clients name.

here i have a sample script... 'deploy.bat'

set /p s1=Server Name :
set /p s2=User Name :
set /p s3=Password :
set /p s4=Database Name :

sqlcmd -S%s1% -U%s2% -P%s3% -i C:\inetpub\wwroot\db\create_db.sql
pause
sqlcmd -S%s1% -U%s2% -P%s3% -i C:\inetpub\wwroot\db\test_login.sql
pause
sqlcmd -S%s1% -U%s2% -P%s3% -i C:\inetpub\wwroot\db\change_dbowner.sql
pause
sqlcmd -S%s1% -U%s2% -P%s3% -i C:\inetpub\wwroot\db\user.sql
pause
sqlcmd -S%s1% -U%s2% -P%s3% -i C:\inetpub\wwroot\db\uddtypes.sql
pause
sqlcmd -S%s1% -U%s2% -P%s3% -i C:\inetpub\wwroot\db\uddtables.sql
pause
sqlcmd -S%s1% -U%s2% -P%s3% -i C:\inetpub\wwroot\db\table_schema.sql
pause
sqlcmd -S%s1% -U%s2% -P%s3% -i C:\inetpub\wwroot\db\stored_procedure.sql
pause
sqlcmd -S%s1% -U%s2% -P%s3% -i C:\inetpub\wwroot\db\grant_permissions.sql
pause
sqlcmd -S%s1% -U%s2% -P%s3% -i C:\inetpub\wwroot\db\gant_permissions_output.sql
pause
sqlcmd -S%s1% -U%s2% -P%s3% -i C:\inetpub\wwroot\db\sql_injection.sql
pause

Post #1419312

Jan Van der Eecken

Posted Wednesday, February 13, 2013 4:50 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:12 AM
Points: 2,269, Visits: 5,960

Have a look at Use sqlcmd with Scripting Variables in BOL. This may well be what you are looking for.

--------------------------------------------------------------------------

The function of good software is to make the complex appear to be simple. (Grady Booch)

Post #1419401

« Prev Topic | Next Topic »

Permissions