How-To pass variable to TSQL Script with "Create Database" 
Author Message
 How-To pass variable to TSQL Script with "Create Database"

Greetings,

What I am trying to do is allow the user to pass a variable to a TSQL Script
that I run through OSQL.  I have  batch file that calls OSQL with the name
of the script file.

My dilemma is that I cannot hard code the name of the database into the
Create Database statement.  I want to let the user provide the name of the
database used with Create Database.

I have tried to create a stored procedure with a variable for the database
name but when I assign pass Create Database the variable name it doesn't
work. Matter of fact  I cannot even save the stored procedure it produces an
error.  The error is basically telling me that my variable at the end of
create database is invalid.

Any assistance you can provide with this problem would be greatly
appreciated.

Thanks
Bruce Wallwin
Senior Software Developer
Sequencia Corporation



Fri, 12 Apr 2002 03:00:00 GMT
 How-To pass variable to TSQL Script with "Create Database"

As far as the Stored Procedure itself goes, there are several commands such
as CREATE DATABASE, that will not allow the use of variables, it has to be a
constant. However, you can get round this by doing the following:




You can then save the SP.

Mike MacGregor
Database Developer
EMJ Data Systems


Quote:
> Greetings,

> What I am trying to do is allow the user to pass a variable to a TSQL
Script
> that I run through OSQL.  I have  batch file that calls OSQL with the name
> of the script file.

> My dilemma is that I cannot hard code the name of the database into the
> Create Database statement.  I want to let the user provide the name of the
> database used with Create Database.

> I have tried to create a stored procedure with a variable for the database
> name but when I assign pass Create Database the variable name it doesn't
> work. Matter of fact  I cannot even save the stored procedure it produces
an
> error.  The error is basically telling me that my variable at the end of
> create database is invalid.

> Any assistance you can provide with this problem would be greatly
> appreciated.

> Thanks
> Bruce Wallwin
> Senior Software Developer
> Sequencia Corporation



Fri, 12 Apr 2002 03:00:00 GMT
 How-To pass variable to TSQL Script with "Create Database"

Or create a batch file that takes the database name as a parameter, dynamically constructs the sql script file (the one with the create database statement in it) and then call OSQL with the dynamic script file name.

--

Note: Do not try to reply by email.  You're message will not be delivered.  (Anti spam)

-criatura


  As far as the Stored Procedure itself goes, there are several commands such
  as CREATE DATABASE, that will not allow the use of variables, it has to be a
  constant. However, you can get round this by doing the following:




  You can then save the SP.

  Mike MacGregor
  Database Developer
  EMJ Data Systems



  > Greetings,
  >
  > What I am trying to do is allow the user to pass a variable to a TSQL
  Script
  > that I run through OSQL.  I have  batch file that calls OSQL with the name
  > of the script file.
  >
  > My dilemma is that I cannot hard code the name of the database into the
  > Create Database statement.  I want to let the user provide the name of the
  > database used with Create Database.
  >
  > I have tried to create a stored procedure with a variable for the database
  > name but when I assign pass Create Database the variable name it doesn't
  > work. Matter of fact  I cannot even save the stored procedure it produces
  an
  > error.  The error is basically telling me that my variable at the end of
  > create database is invalid.
  >
  > Any assistance you can provide with this problem would be greatly
  > appreciated.
  >
  > Thanks
  > Bruce Wallwin
  > Senior Software Developer
  > Sequencia Corporation
  >
  >



Fri, 12 Apr 2002 03:00:00 GMT
 How-To pass variable to TSQL Script with "Create Database"
Bruce,

Check out OSQL in the SQL Server 7.0 Books Online. The following is from
there:

SET table = sysobjects
osql /q "Select * from %table%"


Quote:
> Greetings,

> What I am trying to do is allow the user to pass a variable to a TSQL
Script
> that I run through OSQL.  I have  batch file that calls OSQL with the name
> of the script file.

> My dilemma is that I cannot hard code the name of the database into the
> Create Database statement.  I want to let the user provide the name of the
> database used with Create Database.

> I have tried to create a stored procedure with a variable for the database
> name but when I assign pass Create Database the variable name it doesn't
> work. Matter of fact  I cannot even save the stored procedure it produces
an
> error.  The error is basically telling me that my variable at the end of
> create database is invalid.

> Any assistance you can provide with this problem would be greatly
> appreciated.

> Thanks
> Bruce Wallwin
> Senior Software Developer
> Sequencia Corporation



Fri, 12 Apr 2002 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. asp passing variable to sql "from "clause

2. CREATE DATABASE creates "memory leak"

3. variable used for "CREATE TABLE"

4. Generate a "create" script (reverse engineer)

5. Seeking "create" scripts

6. SCRIPT HANGS UP ON "CREATE" COMMAND

7. Creating "Find" Scripts

8. help creating "previous layout" script

9. Seeking "create" scripts

10. max of ("...","...","..")

11. Schedule The "Generate SQL Database Script"

12. TSQL - Dynamic "Where" Clause Help Please


 
Powered by phpBB® Forum Software