February 11, 2013 at 3:56 am
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
February 11, 2013 at 5:32 am
arthi.anan (2/11/2013)
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
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.
February 11, 2013 at 5:47 am
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.
February 13, 2013 at 12:12 am
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
February 13, 2013 at 4:50 am
Have a look at Use sqlcmd with Scripting Variables in BOL. This may well be what you are looking for.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply