variable for DB name in sql script

Question

variable for DB name in sql script

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

gljjr SSCrazy Eights Points: 9970 More actions · Answer 1

Better yet just store the scripts as a file WITHOUT the DB Name. Then use a batch file to run the scripts via osql. Since the scripts will be running under the database context you have used to log into osql with you should be ok.

The user would then simply open a dos box and type something like

BuildReports.cmd MyServer MyNewDB

The batch file would look something like the following...(note this is untested!)



@echo off



if '%1' == '' goto usage

if '%2' == '' goto usage

echo ---------------------------------------------------------------

ECHO creating database %2

echo ---------------------------------------------------------------

osql -S %1 -d master -E -b -n -Q "CREATE DATABASE %2"



echo ---------------------------------------------------------------

ECHO Calling RUN_SCRIPT1.sql

echo ---------------------------------------------------------------

echo.

osql -S %1 -d %2 -E -b -n -i "RUN_SCRIPT1.sql"

if %ERRORLEVEL% NEQ 0 goto errors

goto finish



REM: How to use screen

:usage

echo.

echo ---------------------------------------------------------------

echo Usage: MyScript Server Database

echo Server: the name of the target SQL Server

echo Database: the name of the target database

echo.

echo Example: MyScript.cmd MainServer MainDatabase

echo ---------------------------------------------------------------

echo.

echo.

goto done



REM: error handler

:errors

echo.

echo WARNING! Error(s) were detected!

echo ---------------------------------------------------------------

echo Please evaluate the situation and, if needed,

echo restart this command file. You may need to

echo supply command parameters when executing

echo this command file.

echo ---------------------------------------------------------------

echo.

pause

goto done



REM: finished execution

:finish

echo.

echo ---------------------------------------------------------------

echo Execution of all scripts are complete!

echo ---------------------------------------------------------------

:done

@echo on

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

quote:
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

I feel like a fool!

I knew it was simply, but couldn't figure it out yesterday.

But having the choice between dynamic sql and Gary's solution, I would use the latter.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Timothy S Neumark Valued Member Points: 57 More actions · Answer 3

I have written a batch file that asks the user for a username and password, the database name and path... it addresses your situation exactly. I can email it to you, that's probably easiest. Let me know.