variable for DB name in sql script
-
I am writing a script for one of my users to run. I want to setup a variable at the beginning to represent the db name that is referenced several times through the script. Each month when he runs this script the db name will change, (a new db is created). When I declare the variable as varchar or char, the script balks at the variable name and says the database does not exist. If I change the db name through the script manually, it works just fine, is there a way to do this?
-
You have to wirte a dynamic query, like this:
declare @dbname varchar(25)
set @dbname='DatabaseName'
exec('use ' + @dbname +
' select .... all the instructions here .... ')
You have to put the script into the string that is executed by the exec.
-
You probably need to use dynamic sql with either the Exec() function or sp_executesql.
e.g.
Exec('Select * from ' + @dbname)
It may be easier to turn your script into a stored procedure and allow your user to pass in the name of the database as a parameter.
-
Are you creating every month the same db with another name?
If so, any reason for this?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
I am writing a script for one of my users to run.How is your user going to execute the script? With OSQL.exe command, you can specific database name as parameter and you don't have to code database name in your script.
-
This db is used by a program that the user runs to generate reports. I am held to certain requirements because of the program. I am basically massaging the data so that the user does not have to enter the same data each month manually that does not get updated each month. I loaded the sql query tool on his machine so he could run my scripts from there. he is running the msde on his machine and I just attach the new db each month, run the scripts that update the data that does not come from the db from the last month and then he runs his program that uses the db to get the reports. I have been running the scripts for him and it has been suggested that it be setup so he can run it.
So I am trying to make it as painless as possible. All the scripts work great, but it is not very user friendly for the "normal" user. I really run a total of 5 scripts to do what I need. I am in the process of automating the scripts for him and did not want to make him go through and change the db name all through the 5 scripts. In 2 of the scripts, the db name is referenced 3 or 4 times. If he corrupts anything by not getting the name in or using the wrong name, we will lose all of the data for that month and I will probably not be posting from here very much longer! I had posted here before with these same scripts and everytime you guys have come through with the answers I needed. I am new to sql scripting, but have been doing vb and vbs for several years now. So I know enough to be dangerous in sql. But it's nice to come to a forum where your experience is not an issue but the problem itself. Thanks for all the help.
-
Ah, those powerusers with their whole bunch of specialities...Well then, I don't know if you can reference a db with osql that doesn't already exist.
I would use dynamic sql and I guess you the pitfalls, right?
A very basic script for creating the db could look like
declare @stmt nvarchar(1000)
declare @dbname nvarchar(10)
set @dbname = 'FRANK_1'
set @stmt = 'CREATE DATABASE ' + @dbname
EXEC sp_executesql @stmtresults in
Der CREATE DATABASE-Prozess reserviert 0.75 MB auf dem Datenträger 'FRANK_1'.
Der CREATE DATABASE-Prozess reserviert 0.49 MB auf dem Datenträger 'FRANK_1_log'.Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
You should be able to place the whole stuff into a sproc with the dbname as input parameter.
Forgot that to mention in the previous post.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
How would I reference the passed name in the sproc, do I set it up like a normal query, declare the variable and then do the exec(query steps), or is it different in a sproc?
-
Does this help?
CREATE PROCEDURE usp_blahbla @dbname nvarchar(10)
as
declare @stmt nvarchar(1000)
set @stmt = 'CREATE DATABASE ' + @dbname
EXEC sp_executesql @stmt
set @stmt = 'USE '+ @dbname
EXEC sp_executesql @stmt
set @stmt = 'CREATE TABLE First( a int, b float)'
EXEC sp_executesql @stmt
GOExecute it this way
exec usp_blahbla 'FRANK_2'Frank
Sorry, there is an error in the above. I'll keep digging
Edited by - Frank Kalis on 12/18/2003 2:14:36 PM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] - Thanks for the help!
-
Actually it compiles, but it doesn't change to the new database. Please don't use it right now!
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Sorry!!!
Brain dead at 10:30 PM. Need to go home to get some sleep.
It shouldn't be difficult, but I can't figure out not what's wrong. Hope someone else jumps right in.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
How about
Declare @otherDB VarChar(50), @SQL varchar(8000)
set @otherDB = 'Pubs'
Set @SQL = 'Print db_name(db_id())'
Set @SQL = @otherDB + '.dbo.sp_ExecuteSQL N''' + Replace(@SQL, '''', '''''') + ''''
Print @SQL
Exec (@SQL)
Once you understand the BITs, all the pieces come together
-
The USE instruction has to be in the same statement as the CREATE TABLE.
set @stmt = 'USE '+ @dbname + ' CREATE TABLE First( a int, b float)'
EXEC sp_executesql @stmt
Ah, those powerusers with their whole bunch of specialities...
Thanks for the help!
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply