Creating Dynamic Database using Script

I am looking for a way to dynamically create a database in SQL Server
2000 using a stored procedure that will create a dynamically named new
database using the script from another database.
More specifically, I would like to call a stored procedure that will
pass in a new DB name and will take the script that I have created from
another database and simply create a new database with the new name
using the old scripts table structure, etc. It might take reading in
the script and passing in a dynamic variable, but I am having trouble
with it, as everytime I try to create a stored procedure with the
database script in it and substitute the variable @.NEWDBNAME in the
place of each instance of the database name in the script, I get errors
when trying to save the SP.
Any help with this would be hugely appreciated.Hi
Posting your code would have been useful!
You will need to make the whole statement "dynamic" and then execute the
statement.
CREATE PROCEDURE spr_create_new_database
AS
BEGIN
DECLARE @.dbname sysname
DECLARE @.stmt varchar(4000)
/* Create New ISD Backup database dynamically */
SET @.dbname = 'BackupDB_' +
REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),GETDATE(),121),':',''),'-',''),'
','')
SET @.stmt = 'CREATE DATABASE ' + @.dbname
EXEC ( @.stmt )
RETURN
END
If you are going to create this database regularly then you may want to
create a template database and copy the file and use sp_attach_db to create a
nwe database, alternatively you could use backup/restore. If you want to
create a copy of your live database using RESTORE may be a quicker option
that creating the tables and pulling the data across.
You could use a DTS package to populate the database with a dynamic
properties task and a Copy SQL Server Objects task. This would allow you to
set the destination database without having to change the package.
John
"SQL Server Group 1" wrote:
> I am looking for a way to dynamically create a database in SQL Server
> 2000 using a stored procedure that will create a dynamically named new
> database using the script from another database.
> More specifically, I would like to call a stored procedure that will
> pass in a new DB name and will take the script that I have created from
> another database and simply create a new database with the new name
> using the old scripts table structure, etc. It might take reading in
> the script and passing in a dynamic variable, but I am having trouble
> with it, as everytime I try to create a stored procedure with the
> database script in it and substitute the variable @.NEWDBNAME in the
> place of each instance of the database name in the script, I get errors
> when trying to save the SP.
> Any help with this would be hugely appreciated.
>|||Thanks John for the reply. I am now in the middle of this and need
just a little clarification.
What I did to this point is alter the script to create a database named
"Connect_Template". Then I created the database from the query
analyzer from within the master database and saved the DTS package that
did that. Now I have a database called "Connect_Templage" and a DTS
package called "Create New DB From Termplate".
What I need to do from here is to create a SP that will create a new
database from that DTS package with a new name being passed in. Is
there a way to do that?
In my case, every time a new customer gets created, a new database
based on that template structure needs to be created and I can then
dynamically add a DSN to it from my end. I just need to figure out
what methodis best and will work.
The script to create my database is really really long, but I wonder if
the sp_detach_db and sp_attach_db would work passing in dynamic names?
Thanks again for your help...you have given me a lot of options. I just
need one that would easily work.
I am trying your dynamic statements now but am wondering if the DTP
method would work passing in a dynamic variable.
John Bell wrote:
> Hi
> Posting your code would have been useful!
> You will need to make the whole statement "dynamic" and then execute the
> statement.
> CREATE PROCEDURE spr_create_new_database
> AS
> BEGIN
> DECLARE @.dbname sysname
> DECLARE @.stmt varchar(4000)
> /* Create New ISD Backup database dynamically */
> SET @.dbname = 'BackupDB_' +
> REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),GETDATE(),121),':',''),'-',''),'
> ','')
> SET @.stmt = 'CREATE DATABASE ' + @.dbname
> EXEC ( @.stmt )
> RETURN
> END
> If you are going to create this database regularly then you may want to
> create a template database and copy the file and use sp_attach_db to create a
> nwe database, alternatively you could use backup/restore. If you want to
> create a copy of your live database using RESTORE may be a quicker option
> that creating the tables and pulling the data across.
> You could use a DTS package to populate the database with a dynamic
> properties task and a Copy SQL Server Objects task. This would allow you to
> set the destination database without having to change the package.
> John
> "SQL Server Group 1" wrote:
> > I am looking for a way to dynamically create a database in SQL Server
> > 2000 using a stored procedure that will create a dynamically named new
> > database using the script from another database.
> >
> > More specifically, I would like to call a stored procedure that will
> > pass in a new DB name and will take the script that I have created from
> > another database and simply create a new database with the new name
> > using the old scripts table structure, etc. It might take reading in
> > the script and passing in a dynamic variable, but I am having trouble
> > with it, as everytime I try to create a stored procedure with the
> > database script in it and substitute the variable @.NEWDBNAME in the
> > place of each instance of the database name in the script, I get errors
> > when trying to save the SP.
> >
> > Any help with this would be hugely appreciated.
> >
> >|||Just for a try, I ran the code below from the Master Database, and it
compiled and worked.
I think that might be the best way to run that. The only thing is now,
how would be the best way to run the create table structures, SPs, etc.
once the new database gets created? How would I create the DTS package
with a dynamic properties task? I have used DTS to import and export
from specific places but not anything dynamically.
Thanks again...this is really helping!
John Bell wrote:
> Hi
> Posting your code would have been useful!
> You will need to make the whole statement "dynamic" and then execute the
> statement.
> CREATE PROCEDURE spr_create_new_database
> AS
> BEGIN
> DECLARE @.dbname sysname
> DECLARE @.stmt varchar(4000)
> /* Create New ISD Backup database dynamically */
> SET @.dbname = 'BackupDB_' +
> REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),GETDATE(),121),':',''),'-',''),'
> ','')
> SET @.stmt = 'CREATE DATABASE ' + @.dbname
> EXEC ( @.stmt )
> RETURN
> END
> If you are going to create this database regularly then you may want to
> create a template database and copy the file and use sp_attach_db to create a
> nwe database, alternatively you could use backup/restore. If you want to
> create a copy of your live database using RESTORE may be a quicker option
> that creating the tables and pulling the data across.
> You could use a DTS package to populate the database with a dynamic
> properties task and a Copy SQL Server Objects task. This would allow you to
> set the destination database without having to change the package.
> John
> "SQL Server Group 1" wrote:
> > I am looking for a way to dynamically create a database in SQL Server
> > 2000 using a stored procedure that will create a dynamically named new
> > database using the script from another database.
> >
> > More specifically, I would like to call a stored procedure that will
> > pass in a new DB name and will take the script that I have created from
> > another database and simply create a new database with the new name
> > using the old scripts table structure, etc. It might take reading in
> > the script and passing in a dynamic variable, but I am having trouble
> > with it, as everytime I try to create a stored procedure with the
> > database script in it and substitute the variable @.NEWDBNAME in the
> > place of each instance of the database name in the script, I get errors
> > when trying to save the SP.
> >
> > Any help with this would be hugely appreciated.
> >
> >|||Hi
The procedure is fine if you want to create a database with a given name,
but to use that else where you should really create the name and pass it as a
parameter to the stored procedure. You can then call a DTS package and pass
the database name as a global variable using xp_cmdshell to call DTSRUN see
http://www.sqldts.com/default.aspx?301 about getting help on the command
syntax.
Using this global variable in the DTS package, it can be assigned using the
dynamic properties task can assign it to the destination of a Copy SQL Server
objects task.
e.g.
CREATE PROCEDURE spr_create_new_database ( @.dbname sysname )
AS
BEGIN
DECLARE @.stmt varchar(4000)
/* Create New Backup database dynamically */
SET @.stmt = 'CREATE DATABASE ' + @.dbname
EXEC ( @.stmt )
RETURN
END
Within the job step you can then do something like:
DECLARE @.cmd varchar(4000)
DECLARE @.dbname sysname
SET @.dbname = 'BackupDB_' +
REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),GETDATE(),121),':',''),'-',''),'','')
EXEC spr_create_new_database @.dbname
SET @.cmd = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "DTS Package" '
SET @.cmd = @.cmd + ' /A " Backup Database Name":"8"="' + @.dbname + '"'
EXEC master..xp_cmdshell @.cmd
This will run the dts package called "DTS Package" on the current server
using trusted authentication, and pass the global variable @.dbname which has
just been used to create the backup database. The global variable is called
"Backup Database Name" You could even pass the source database name as a
global variable and also assign that in the dynamic properties task to make
it totally flexible.
Before you create the DTS package run the stored procedure first so that a
destination database exists for the Copy SQL Objects task. When you create
the DTS Package, create Copy SQL Objects task first and test it, before
creating the global variables and then finally create the Dynamic Properties
task and the workflow to make sure that the Dynamic Properties task runs
before the Copy SQL Objects task.
HTH
John
"SQL Server Group 1" wrote:
> Just for a try, I ran the code below from the Master Database, and it
> compiled and worked.
> I think that might be the best way to run that. The only thing is now,
> how would be the best way to run the create table structures, SPs, etc.
> once the new database gets created? How would I create the DTS package
> with a dynamic properties task? I have used DTS to import and export
> from specific places but not anything dynamically.
> Thanks again...this is really helping!
>
> John Bell wrote:
> > Hi
> >
> > Posting your code would have been useful!
> >
> > You will need to make the whole statement "dynamic" and then execute the
> > statement.
> >
> > CREATE PROCEDURE spr_create_new_database
> > AS
> > BEGIN
> > DECLARE @.dbname sysname
> > DECLARE @.stmt varchar(4000)
> >
> > /* Create New ISD Backup database dynamically */
> > SET @.dbname = 'BackupDB_' +
> > REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),GETDATE(),121),':',''),'-',''),'
> > ','')
> >
> > SET @.stmt = 'CREATE DATABASE ' + @.dbname
> >
> > EXEC ( @.stmt )
> >
> > RETURN
> > END
> >
> > If you are going to create this database regularly then you may want to
> > create a template database and copy the file and use sp_attach_db to create a
> > nwe database, alternatively you could use backup/restore. If you want to
> > create a copy of your live database using RESTORE may be a quicker option
> > that creating the tables and pulling the data across.
> >
> > You could use a DTS package to populate the database with a dynamic
> > properties task and a Copy SQL Server Objects task. This would allow you to
> > set the destination database without having to change the package.
> >
> > John
> >
> > "SQL Server Group 1" wrote:
> >
> > > I am looking for a way to dynamically create a database in SQL Server
> > > 2000 using a stored procedure that will create a dynamically named new
> > > database using the script from another database.
> > >
> > > More specifically, I would like to call a stored procedure that will
> > > pass in a new DB name and will take the script that I have created from
> > > another database and simply create a new database with the new name
> > > using the old scripts table structure, etc. It might take reading in
> > > the script and passing in a dynamic variable, but I am having trouble
> > > with it, as everytime I try to create a stored procedure with the
> > > database script in it and substitute the variable @.NEWDBNAME in the
> > > place of each instance of the database name in the script, I get errors
> > > when trying to save the SP.
> > >
> > > Any help with this would be hugely appreciated.
> > >
> > >
>|||I really appreciate the help...I am sort of new to the advanced DTS
stuff but have used DTS for import, export. So I will give this a try
and see if I can get it.
Thanks for the link. In creating the Advanced DTS run object, what
would be the dynamic variable name for the database name that is passed
to the copy object? Would it be in the Properties List in the Dynamic
Properties task component under Tasks > Copy SQL Server Objects? For
example: DestinationDatabase, DestinationLogin, DestinationPassword?
If that's right, I think I can do this...
John Bell wrote:
> Hi
> The procedure is fine if you want to create a database with a given name,
> but to use that else where you should really create the name and pass it as a
> parameter to the stored procedure. You can then call a DTS package and pass
> the database name as a global variable using xp_cmdshell to call DTSRUN see
> http://www.sqldts.com/default.aspx?301 about getting help on the command
> syntax.
> Using this global variable in the DTS package, it can be assigned using the
> dynamic properties task can assign it to the destination of a Copy SQL Server
> objects task.
> e.g.
> CREATE PROCEDURE spr_create_new_database ( @.dbname sysname )
> AS
> BEGIN
> DECLARE @.stmt varchar(4000)
> /* Create New Backup database dynamically */
> SET @.stmt = 'CREATE DATABASE ' + @.dbname
> EXEC ( @.stmt )
> RETURN
> END
> Within the job step you can then do something like:
> DECLARE @.cmd varchar(4000)
> DECLARE @.dbname sysname
> SET @.dbname = 'BackupDB_' +
> REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),GETDATE(),121),':',''),'-',''),'','')
> EXEC spr_create_new_database @.dbname
> SET @.cmd = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "DTS Package" '
> SET @.cmd = @.cmd + ' /A " Backup Database Name":"8"="' + @.dbname + '"'
> EXEC master..xp_cmdshell @.cmd
> This will run the dts package called "DTS Package" on the current server
> using trusted authentication, and pass the global variable @.dbname which has
> just been used to create the backup database. The global variable is called
> "Backup Database Name" You could even pass the source database name as a
> global variable and also assign that in the dynamic properties task to make
> it totally flexible.
> Before you create the DTS package run the stored procedure first so that a
> destination database exists for the Copy SQL Objects task. When you create
> the DTS Package, create Copy SQL Objects task first and test it, before
> creating the global variables and then finally create the Dynamic Properties
> task and the workflow to make sure that the Dynamic Properties task runs
> before the Copy SQL Objects task.
> HTH
> John
> "SQL Server Group 1" wrote:
> > Just for a try, I ran the code below from the Master Database, and it
> > compiled and worked.
> >
> > I think that might be the best way to run that. The only thing is now,
> > how would be the best way to run the create table structures, SPs, etc.
> > once the new database gets created? How would I create the DTS package
> > with a dynamic properties task? I have used DTS to import and export
> > from specific places but not anything dynamically.
> >
> > Thanks again...this is really helping!
> >
> >
> >
> > John Bell wrote:
> > > Hi
> > >
> > > Posting your code would have been useful!
> > >
> > > You will need to make the whole statement "dynamic" and then execute the
> > > statement.
> > >
> > > CREATE PROCEDURE spr_create_new_database
> > > AS
> > > BEGIN
> > > DECLARE @.dbname sysname
> > > DECLARE @.stmt varchar(4000)
> > >
> > > /* Create New ISD Backup database dynamically */
> > > SET @.dbname = 'BackupDB_' +
> > > REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),GETDATE(),121),':',''),'-',''),'
> > > ','')
> > >
> > > SET @.stmt = 'CREATE DATABASE ' + @.dbname
> > >
> > > EXEC ( @.stmt )
> > >
> > > RETURN
> > > END
> > >
> > > If you are going to create this database regularly then you may want to
> > > create a template database and copy the file and use sp_attach_db to create a
> > > nwe database, alternatively you could use backup/restore. If you want to
> > > create a copy of your live database using RESTORE may be a quicker option
> > > that creating the tables and pulling the data across.
> > >
> > > You could use a DTS package to populate the database with a dynamic
> > > properties task and a Copy SQL Server Objects task. This would allow you to
> > > set the destination database without having to change the package.
> > >
> > > John
> > >
> > > "SQL Server Group 1" wrote:
> > >
> > > > I am looking for a way to dynamically create a database in SQL Server
> > > > 2000 using a stored procedure that will create a dynamically named new
> > > > database using the script from another database.
> > > >
> > > > More specifically, I would like to call a stored procedure that will
> > > > pass in a new DB name and will take the script that I have created from
> > > > another database and simply create a new database with the new name
> > > > using the old scripts table structure, etc. It might take reading in
> > > > the script and passing in a dynamic variable, but I am having trouble
> > > > with it, as everytime I try to create a stored procedure with the
> > > > database script in it and substitute the variable @.NEWDBNAME in the
> > > > place of each instance of the database name in the script, I get errors
> > > > when trying to save the SP.
> > > >
> > > > Any help with this would be hugely appreciated.
> > > >
> > > >
> >
> >|||Hi
If you only have the two tasks then the will be called something like
DTSTask_DTSTransferObjectsTask_1
and DTSTask_DTSDynamicPropertiesTask_1
These will be listed under Tasks in the left hand pane of the "Dynamic
Properties Task: Package Properties" dialogue. Choose
DTSTask_DTSTransferObjectsTask_1.
On the right hand side a table of Property Names and Default Values will
appear, double click on DestinationDatabase in the Property Name column and
you will get the Add/Edit Assignment dialogue. Choose Global Variables as the
source and your Global variable will be in the name of the drop down.
HTH
John
"SQL Server Group 1" wrote:
> I really appreciate the help...I am sort of new to the advanced DTS
> stuff but have used DTS for import, export. So I will give this a try
> and see if I can get it.
> Thanks for the link. In creating the Advanced DTS run object, what
> would be the dynamic variable name for the database name that is passed
> to the copy object? Would it be in the Properties List in the Dynamic
> Properties task component under Tasks > Copy SQL Server Objects? For
> example: DestinationDatabase, DestinationLogin, DestinationPassword?
> If that's right, I think I can do this...
>
> John Bell wrote:
> > Hi
> >
> > The procedure is fine if you want to create a database with a given name,
> > but to use that else where you should really create the name and pass it as a
> > parameter to the stored procedure. You can then call a DTS package and pass
> > the database name as a global variable using xp_cmdshell to call DTSRUN see
> > http://www.sqldts.com/default.aspx?301 about getting help on the command
> > syntax.
> > Using this global variable in the DTS package, it can be assigned using the
> > dynamic properties task can assign it to the destination of a Copy SQL Server
> > objects task.
> >
> > e.g.
> >
> > CREATE PROCEDURE spr_create_new_database ( @.dbname sysname )
> > AS
> > BEGIN
> > DECLARE @.stmt varchar(4000)
> >
> > /* Create New Backup database dynamically */
> > SET @.stmt = 'CREATE DATABASE ' + @.dbname
> > EXEC ( @.stmt )
> > RETURN
> > END
> >
> > Within the job step you can then do something like:
> >
> > DECLARE @.cmd varchar(4000)
> > DECLARE @.dbname sysname
> >
> > SET @.dbname = 'BackupDB_' +
> > REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),GETDATE(),121),':',''),'-',''),'','')
> >
> > EXEC spr_create_new_database @.dbname
> >
> > SET @.cmd = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N "DTS Package" '
> > SET @.cmd = @.cmd + ' /A " Backup Database Name":"8"="' + @.dbname + '"'
> >
> > EXEC master..xp_cmdshell @.cmd
> >
> > This will run the dts package called "DTS Package" on the current server
> > using trusted authentication, and pass the global variable @.dbname which has
> > just been used to create the backup database. The global variable is called
> > "Backup Database Name" You could even pass the source database name as a
> > global variable and also assign that in the dynamic properties task to make
> > it totally flexible.
> >
> > Before you create the DTS package run the stored procedure first so that a
> > destination database exists for the Copy SQL Objects task. When you create
> > the DTS Package, create Copy SQL Objects task first and test it, before
> > creating the global variables and then finally create the Dynamic Properties
> > task and the workflow to make sure that the Dynamic Properties task runs
> > before the Copy SQL Objects task.
> >
> > HTH
> >
> > John
> > "SQL Server Group 1" wrote:
> >
> > > Just for a try, I ran the code below from the Master Database, and it
> > > compiled and worked.
> > >
> > > I think that might be the best way to run that. The only thing is now,
> > > how would be the best way to run the create table structures, SPs, etc.
> > > once the new database gets created? How would I create the DTS package
> > > with a dynamic properties task? I have used DTS to import and export
> > > from specific places but not anything dynamically.
> > >
> > > Thanks again...this is really helping!
> > >
> > >
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > Posting your code would have been useful!
> > > >
> > > > You will need to make the whole statement "dynamic" and then execute the
> > > > statement.
> > > >
> > > > CREATE PROCEDURE spr_create_new_database
> > > > AS
> > > > BEGIN
> > > > DECLARE @.dbname sysname
> > > > DECLARE @.stmt varchar(4000)
> > > >
> > > > /* Create New ISD Backup database dynamically */
> > > > SET @.dbname = 'BackupDB_' +
> > > > REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),GETDATE(),121),':',''),'-',''),'
> > > > ','')
> > > >
> > > > SET @.stmt = 'CREATE DATABASE ' + @.dbname
> > > >
> > > > EXEC ( @.stmt )
> > > >
> > > > RETURN
> > > > END
> > > >
> > > > If you are going to create this database regularly then you may want to
> > > > create a template database and copy the file and use sp_attach_db to create a
> > > > nwe database, alternatively you could use backup/restore. If you want to
> > > > create a copy of your live database using RESTORE may be a quicker option
> > > > that creating the tables and pulling the data across.
> > > >
> > > > You could use a DTS package to populate the database with a dynamic
> > > > properties task and a Copy SQL Server Objects task. This would allow you to
> > > > set the destination database without having to change the package.
> > > >
> > > > John
> > > >
> > > > "SQL Server Group 1" wrote:
> > > >
> > > > > I am looking for a way to dynamically create a database in SQL Server
> > > > > 2000 using a stored procedure that will create a dynamically named new
> > > > > database using the script from another database.
> > > > >
> > > > > More specifically, I would like to call a stored procedure that will
> > > > > pass in a new DB name and will take the script that I have created from
> > > > > another database and simply create a new database with the new name
> > > > > using the old scripts table structure, etc. It might take reading in
> > > > > the script and passing in a dynamic variable, but I am having trouble
> > > > > with it, as everytime I try to create a stored procedure with the
> > > > > database script in it and substitute the variable @.NEWDBNAME in the
> > > > > place of each instance of the database name in the script, I get errors
> > > > > when trying to save the SP.
> > > > >
> > > > > Any help with this would be hugely appreciated.
> > > > >
> > > > >
> > >
> > >
>|||John,
I almost have it, I think. Here is the stored procedure code I am
running. It created the new database just fine, but it doesn't copy
the data from the template database.
CREATE PROCEDURE sprk_create_new_database ( @.dbname sysname )
AS
BEGIN
DECLARE @.stmt varchar(4000)
/* Create New Backup database dynamically */
SET @.stmt = 'CREATE DATABASE ' + @.dbname
EXEC ( @.stmt )
RETURN
END
DECLARE @.cmd varchar(4000)
EXEC sprk_create_new_database @.dbname
SET @.cmd = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N
"Create_New_Connect_DB" '
SET @.cmd = @.cmd + ' /A "dbname":"8"="' + @.dbname + '"'
EXEC master..xp_cmdshell @.cmd
GO
----.
The exec code is:
exec sprk_create_new_database @.dbname = 'TestCopy'
The resulting text is:
The CREATE DATABASE process is allocating 0.63 MB on disk 'TestCopy'.
The CREATE DATABASE process is allocating 0.49 MB on disk
'TestCopy_log'.
However, when it gets to the copy DTS package, "Create_New_Connect_DB",
it does not pass in the variable 'dbname' to the package that has a
Dynamic Properties Variable that passes that name into the
'DestinationDatabase' variable of the Copy SQL Server Objects Task.
Can you see anything here that looks wrong? Again, I really appreciate
the help. I think I'm really close to solving this one!
Brandon|||Hi
You are passing the name to the stored procedure rather than setting the
local variable with the name. The code will not work at all without the local
variable being declared!. This may be easier if you create a procedure to
both call your database creation procedure and the DTS Package (This may wrap)
CREATE PROCEDURE sprk_create_new_database_and_populate_it ( @.dbname sysname )
AS
BEGIN
DECLARE @.cmd varchar(4000)
EXEC sprk_create_new_database @.dbname
SET @.cmd = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N
"Create_New_Connect_DB" '
SET @.cmd = @.cmd + ' /A "dbname":"8"="' + @.dbname + '"'
EXEC master..xp_cmdshell @.cmd
END
GO
Then run sprk_create_new_database_and_populate_it passing the name of the
database you wish to populate:
EXEC sprk_create_new_database_and_populate_it @.dbname = 'TestCopy'
HTH
John
"SQL Server Group 1" wrote:
> John,
> I almost have it, I think. Here is the stored procedure code I am
> running. It created the new database just fine, but it doesn't copy
> the data from the template database.
> CREATE PROCEDURE sprk_create_new_database ( @.dbname sysname )
> AS
> BEGIN
> DECLARE @.stmt varchar(4000)
>
> /* Create New Backup database dynamically */
> SET @.stmt = 'CREATE DATABASE ' + @.dbname
> EXEC ( @.stmt )
> RETURN
> END
>
> DECLARE @.cmd varchar(4000)
> EXEC sprk_create_new_database @.dbname
>
> SET @.cmd = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N
> "Create_New_Connect_DB" '
> SET @.cmd = @.cmd + ' /A "dbname":"8"="' + @.dbname + '"'
>
> EXEC master..xp_cmdshell @.cmd
> GO
> ----.
> The exec code is:
> exec sprk_create_new_database @.dbname = 'TestCopy'
> The resulting text is:
> The CREATE DATABASE process is allocating 0.63 MB on disk 'TestCopy'.
> The CREATE DATABASE process is allocating 0.49 MB on disk
> 'TestCopy_log'.
> However, when it gets to the copy DTS package, "Create_New_Connect_DB",
> it does not pass in the variable 'dbname' to the package that has a
> Dynamic Properties Variable that passes that name into the
> 'DestinationDatabase' variable of the Copy SQL Server Objects Task.
> Can you see anything here that looks wrong? Again, I really appreciate
> the help. I think I'm really close to solving this one!
> Brandon
>|||OK, here are the two SPs
1) sprk_create_new_database_and_populate_it
CREATE PROCEDURE sprk_create_new_database_and_populate_it ( @.dbname
sysname )
AS
BEGIN
DECLARE @.cmd varchar(4000)
EXEC sprk_create_new_database @.dbname
SET @.cmd = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N
"Create_New_Connect_DB" '
SET @.cmd = @.cmd + ' /A "dbname":"8"="' + @.dbname + '"'
EXEC master..xp_cmdshell @.cmd
END
GO
2) sprk_create_new_database
CREATE PROCEDURE sprk_create_new_database ( @.dbname sysname )
AS
BEGIN
DECLARE @.stmt varchar(4000)
/* Create New Backup database dynamically */
SET @.stmt = 'CREATE DATABASE ' + @.dbname
EXEC ( @.stmt )
RETURN
END
GO
When I run:
EXEC sprk_create_new_database_and_populate_it @.dbname = 'TestCopy'
I get the message:
DTSRun: Must specify a value for switch: /N The parameter is
incorrect.
DTSRun: Invalid command options
Usage: dtsrun /option [value] [/option [value]] ...
Options ('/?' shows this screen; '-' May be substituted for '/'):
Package retrieval:
/~S Server Name
/~U User Name
/~P Password
/E <Use trusted connection instead of /U /P>
/~N Package Name
/~M Package Password
/~G Package Guid String
/~V Package Version Guid String
/~F Structured Storage UNC filename (overwritten if /S also
specified)
/~R Repository Database Name <uses default if blank; loads package
from repository database>
Package operation (overrides stored Package settings):
/~A Global Variable Name:typeid=Value <may quote entire string
(including name:typeid)>
/~L Log file name
/~W Write Completion Status to Windows Event Log <True or False>
DTSRun action (default is to execute Package):
/!X <Do not execute; retrieves Package to /F filename>
/!D <Do not execute; drop package from SQL Server (cannot drop from
Storage File)>
/!Y <Do not execute; output encrypted command line>
/!C <Copies command line to Windows clipboard (may be used with /!Y
and /!X)>
Notes:
~ is optional; if present, the parameter is hex text of encrypted
value (0x313233...)
Whitespace between command switch and value is optional
Embedded whitespace in values must be embedded in double-quotes
If an option is specified multiple times, the last one wins (except
multiple /A)
Error: -2147024809 (80070057); Provider Error: 0 (0)
Error string: DTSRun: Must specify a value for switch: /N The
parameter is incorrect.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 0
NULL
Any thoughts...I does created the Database first, which is hopeful.
It just doesn't get through the population part.
Thanks!!|||Hi
Instead of "EXEC master..xp_cmdshell @.cmd" use SELECT @.cmd to print it out.
You could then see the whole command, and if necessary run it from a command
prompt. Check Create_New_Connect_DB is the package name (check no trailing
spaces) and you have saved it to the server.
You may want to compare what you are running against the output from
http://www.sqldts.com/default.aspx?301
John
"SQL Server Group 1" wrote:
> OK, here are the two SPs
> 1) sprk_create_new_database_and_populate_it
>
> CREATE PROCEDURE sprk_create_new_database_and_populate_it ( @.dbname
> sysname )
> AS
> BEGIN
> DECLARE @.cmd varchar(4000)
>
> EXEC sprk_create_new_database @.dbname
>
> SET @.cmd = 'DTSRun /S "' + @.@.SERVERNAME + '" /W "0" /E /N
> "Create_New_Connect_DB" '
> SET @.cmd = @.cmd + ' /A "dbname":"8"="' + @.dbname + '"'
> EXEC master..xp_cmdshell @.cmd
> END
> GO
> 2) sprk_create_new_database
> CREATE PROCEDURE sprk_create_new_database ( @.dbname sysname )
> AS
> BEGIN
> DECLARE @.stmt varchar(4000)
>
> /* Create New Backup database dynamically */
> SET @.stmt = 'CREATE DATABASE ' + @.dbname
> EXEC ( @.stmt )
> RETURN
> END
> GO
>
> When I run:
> EXEC sprk_create_new_database_and_populate_it @.dbname = 'TestCopy'
> I get the message:
> DTSRun: Must specify a value for switch: /N The parameter is
> incorrect.
> DTSRun: Invalid command options
> Usage: dtsrun /option [value] [/option [value]] ...
> Options ('/?' shows this screen; '-' May be substituted for '/'):
> Package retrieval:
> /~S Server Name
> /~U User Name
> /~P Password
> /E <Use trusted connection instead of /U /P>
> /~N Package Name
> /~M Package Password
> /~G Package Guid String
> /~V Package Version Guid String
> /~F Structured Storage UNC filename (overwritten if /S also
> specified)
> /~R Repository Database Name <uses default if blank; loads package
> from repository database>
> Package operation (overrides stored Package settings):
> /~A Global Variable Name:typeid=Value <may quote entire string
> (including name:typeid)>
> /~L Log file name
> /~W Write Completion Status to Windows Event Log <True or False>
> DTSRun action (default is to execute Package):
> /!X <Do not execute; retrieves Package to /F filename>
> /!D <Do not execute; drop package from SQL Server (cannot drop from
> Storage File)>
> /!Y <Do not execute; output encrypted command line>
> /!C <Copies command line to Windows clipboard (may be used with /!Y
> and /!X)>
> Notes:
> ~ is optional; if present, the parameter is hex text of encrypted
> value (0x313233...)
> Whitespace between command switch and value is optional
> Embedded whitespace in values must be embedded in double-quotes
> If an option is specified multiple times, the last one wins (except
> multiple /A)
>
> Error: -2147024809 (80070057); Provider Error: 0 (0)
> Error string: DTSRun: Must specify a value for switch: /N The
> parameter is incorrect.
> Error source: Microsoft Data Transformation Services (DTS) Package
> Help file: sqldts80.hlp
> Help context: 0
> NULL
> Any thoughts...I does created the Database first, which is hopeful.
> It just doesn't get through the population part.
> Thanks!!
>|||OK..Last try.
I now have everything working if I pass in a preset variable, but I
can't seem to get the formatting right when I try to pass in the the
dynamic variable name "@.dbname"
Here is my query analyzer statement:
EXEC sprk_create_new_database_and_populate_it @.dbname = 'NewConnectDB'
Here is what works:
CREATE PROCEDURE sprk_create_new_database_and_populate_it ( @.dbname
sysname )
AS
BEGIN
DECLARE @.cmd varchar(4000)
EXEC sprk_create_new_database @.dbname
SET
@.cmd = 'DTSRun /S "(local)" /N "3Click_Connect_Copy_DB" /G
"{AB3BD356-2FE4-49FB-8233-51C4F82C0B25}" /A
"DestDatabase":"8"="NewConnectDB" /W "0" /E'
EXEC master..xp_cmdshell @.cmd
END
Notice above that I made the DestDatabase a constant and not passing in
@.dbname. That works using the exec statement at the top.
Here is what does not work:
CREATE PROCEDURE sprk_create_new_database_and_populate_it ( @.dbname
sysname )
AS
BEGIN
DECLARE @.cmd varchar(4000)
EXEC sprk_create_new_database @.dbname
SET
@.cmd = 'DTSRun /S "(local)" /N "3Click_Connect_Copy_DB" /G
"{AB3BD356-2FE4-49FB-8233-51C4F82C0B25}" /A "DestDatabase":"8"="'+@.dbname+'" /W "0" /E'
EXEC master..xp_cmdshell @.cmd
END
GO
This gives me an error:
Error string: [SQL-DMO]The name ' "NewConnectDB"' was not found in
the Databases collection. If the name is a qualified name, use [] to
separate various parts of the name, and try again.
I can't seem to format the @.dbname in the cmd statement that will pass
in the value NewConnectDB that is formatted properly. Can you see what
the problem might be?
Thanks again, as I have been working on this for quite a while and
think I am almost done.
Brandon
GO|||AWESOME!!!!
I figured it out finally.
Here is the DTS run string that works just for future reference:
CREATE PROCEDURE sprk_create_new_database_and_populate_it ( @.dbname
sysname )
AS
BEGIN
DECLARE @.cmd varchar(4000)
EXEC sprk_create_new_database @.dbname
SET
@.cmd = 'DTSRun /S "(local)" /N "3Click_Connect_Copy_DB" /G
"{AB3BD356-2FE4-49FB-8233-51C4F82C0B25}" /A "DestDatabase":"8"=' +
@.dbname + ' /W "0" /E'
EXEC master..xp_cmdshell @.cmd
END
GO
I really, really apprciate your help and thank you for your patience.
I should have a good foundation on which to base my future dynamic DTS
packages!
Brandon