CREATE via Dynamic SQL into new database?

From a stored procedure running in the context of one database, I would like
to create a set of objects (stored procedures, functions, views, users) into
a newly-created second database, where the name is dynamically determined.
Creating the new database and retrieving its name is no problem, the problem
is executing CREATE FUNCTION, CREATE PROCEDURE, etc. in the context of the
new database.
As you know, executing dynamic SQL 'use database' won't change the context
of an executing procedure. And 'use database; create function ...' doesn't
work, because the create statements need to be in their own batch. I cannot
store the objects in Master, so I can't have them automatically created with
the new database.
Is there a way to copy the objects from an existing (i.e. template) database
to the new one using dynamic SQL? Any way to attach a copy of a template
database file to a new database dynamically?
Or any out-of-the-box ideas?declare @.sql nvarchar(1000)
set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
exec sp_executesql @.sql
> From a stored procedure running in the context of one database, I would
> like to create a set of objects (stored procedures, functions, views,
> users) into a newly-created second database, where the name is dynamically
> determined. Creating the new database and retrieving its name is no
> problem, the problem is executing CREATE FUNCTION, CREATE PROCEDURE, etc.
> in the context of the new database.
> As you know, executing dynamic SQL 'use database' won't change the context
> of an executing procedure. And 'use database; create function ...' doesn't
> work, because the create statements need to be in their own batch. I
> cannot store the objects in Master, so I can't have them automatically
> created with the new database.
> Is there a way to copy the objects from an existing (i.e. template)
> database to the new one using dynamic SQL? Any way to attach a copy of a
> template database file to a new database dynamically?
> Or any out-of-the-box ideas?
--
new|||here's a real hum-dinger: (this is all on one line)
exec opendatasource('sqloledb', 'data
source=YourServer;uid=UserId;pwd=Password').YourDatabase.dbo.sp_executesql
N'create table mydatabase.dbo.newtable (myfield1 int)'
You'll want to change the following areas:
YourServer
UserId
Password
YourDatabase
... and the statement of course
> declare @.sql nvarchar(1000)
> set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
> exec sp_executesql @.sql
>
>> From a stored procedure running in the context of one database, I would
>> like to create a set of objects (stored procedures, functions, views,
>> users) into a newly-created second database, where the name is
>> dynamically determined. Creating the new database and retrieving its name
>> is no problem, the problem is executing CREATE FUNCTION, CREATE
>> PROCEDURE, etc. in the context of the new database.
>> As you know, executing dynamic SQL 'use database' won't change the
>> context of an executing procedure. And 'use database; create function
>> ...' doesn't work, because the create statements need to be in their own
>> batch. I cannot store the objects in Master, so I can't have them
>> automatically created with the new database.
>> Is there a way to copy the objects from an existing (i.e. template)
>> database to the new one using dynamic SQL? Any way to attach a copy of a
>> template database file to a new database dynamically?
>> Or any out-of-the-box ideas?
>
--
new|||Thanks, but this isn't the issue. Issue is that from a stored procedure (or
batch, for that matter) running in the context of database A, do:
declare @.DBName varchar(20)
set @.DBName = 'dynamic'
declare @.SQL varchar(200)
set @.SQL = 'use ' + @.DBName + '; create function foo ...'
exec (@.SQL)
Doesn't work because 'create function' must be at the beginning of a batch.
set @.SQL = 'create function ' + @.DBName + '.dbo.foo ...' doesn't work by
design.
Need to create functions, stored procs etc. in a different,
dynamically-determined database.
"beginthreadex" wrote:
> declare @.sql nvarchar(1000)
> set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
> exec sp_executesql @.sql
>
> > From a stored procedure running in the context of one database, I would
> > like to create a set of objects (stored procedures, functions, views,
> > users) into a newly-created second database, where the name is dynamically
> > determined. Creating the new database and retrieving its name is no
> > problem, the problem is executing CREATE FUNCTION, CREATE PROCEDURE, etc.
> > in the context of the new database.
> >
> > As you know, executing dynamic SQL 'use database' won't change the context
> > of an executing procedure. And 'use database; create function ...' doesn't
> > work, because the create statements need to be in their own batch. I
> > cannot store the objects in Master, so I can't have them automatically
> > created with the new database.
> >
> > Is there a way to copy the objects from an existing (i.e. template)
> > database to the new one using dynamic SQL? Any way to attach a copy of a
> > template database file to a new database dynamically?
> >
> > Or any out-of-the-box ideas?
> --
> new
>|||LOL ... next it will be sp_cmdshell(osql ... ). :-)
"beginthreadex" wrote:
> here's a real hum-dinger: (this is all on one line)
> exec opendatasource('sqloledb', 'data
> source=YourServer;uid=UserId;pwd=Password').YourDatabase.dbo.sp_executesql
> N'create table mydatabase.dbo.newtable (myfield1 int)'
> You'll want to change the following areas:
> YourServer
> UserId
> Password
> YourDatabase
> ... and the statement of course
>
> > declare @.sql nvarchar(1000)
> > set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
> > exec sp_executesql @.sql
> >
> >
> >> From a stored procedure running in the context of one database, I would
> >> like to create a set of objects (stored procedures, functions, views,
> >> users) into a newly-created second database, where the name is
> >> dynamically determined. Creating the new database and retrieving its name
> >> is no problem, the problem is executing CREATE FUNCTION, CREATE
> >> PROCEDURE, etc. in the context of the new database.
> >>
> >> As you know, executing dynamic SQL 'use database' won't change the
> >> context of an executing procedure. And 'use database; create function
> >> ...' doesn't work, because the create statements need to be in their own
> >> batch. I cannot store the objects in Master, so I can't have them
> >> automatically created with the new database.
> >>
> >> Is there a way to copy the objects from an existing (i.e. template)
> >> database to the new one using dynamic SQL? Any way to attach a copy of a
> >> template database file to a new database dynamically?
> >>
> >> Or any out-of-the-box ideas?
> >
> --
> new
>|||The code I provided does execute the code in the other database. Hence, the
"mydatabase" reference. So, here's your code mixed with mine:
declare @.DBName varchar(20)
set @.DBName = 'dynamic'
declare @.SQL varchar(200)
set @.SQL = 'create function [' + @.DBName + '].dbo.foo ...'
exec sp_executesql @.sql
If there is something else that is confusing please let me know. Because I'm
referencing the database name this will run for the context of the other
database.
;)
> Thanks, but this isn't the issue. Issue is that from a stored procedure
> (or batch, for that matter) running in the context of database A, do:
> declare @.DBName varchar(20)
> set @.DBName = 'dynamic'
> declare @.SQL varchar(200)
> set @.SQL = 'use ' + @.DBName + '; create function foo ...'
> exec (@.SQL)
> Doesn't work because 'create function' must be at the beginning of a
> batch.
> set @.SQL = 'create function ' + @.DBName + '.dbo.foo ...' doesn't work by
> design.
> Need to create functions, stored procs etc. in a different,
> dynamically-determined database.
>
>> declare @.sql nvarchar(1000)
>> set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
>> exec sp_executesql @.sql|||If you tried it (in s2k), you would realize that you cannot use 3 part
naming for creating procedures or functions. These statements are limited
to accepting an owner name (optional) and an object name.
Try the following statement:
create procedure pubs.dbo.junk as select getdate()|||Thanks for the response, I really do appreciate it. But CREATE no longer
accepts a DB name reference for functions/procedures - at least in SQL Server
2000.
"beginthreadex" wrote:
> The code I provided does execute the code in the other database. Hence, the
> "mydatabase" reference. So, here's your code mixed with mine:
> declare @.DBName varchar(20)
> set @.DBName = 'dynamic'
> declare @.SQL varchar(200)
> set @.SQL = 'create function [' + @.DBName + '].dbo.foo ...'
> exec sp_executesql @.sql
> If there is something else that is confusing please let me know. Because I'm
> referencing the database name this will run for the context of the other
> database.
> ;)
> > Thanks, but this isn't the issue. Issue is that from a stored procedure
> > (or batch, for that matter) running in the context of database A, do:
> >
> > declare @.DBName varchar(20)
> > set @.DBName = 'dynamic'
> > declare @.SQL varchar(200)
> > set @.SQL = 'use ' + @.DBName + '; create function foo ...'
> > exec (@.SQL)
> >
> > Doesn't work because 'create function' must be at the beginning of a
> > batch.
> >
> > set @.SQL = 'create function ' + @.DBName + '.dbo.foo ...' doesn't work by
> > design.
> >
> > Need to create functions, stored procs etc. in a different,
> > dynamically-determined database.
> >
> >
> >> declare @.sql nvarchar(1000)
> >> set @.sql = 'create table mydatabase.dbo.newtable (myfield1 int)'
> >> exec sp_executesql @.sql
>|||I deeply apologize! The "Create Table" code does allow for this.
However this DOES work as I have just tested:
exec opendatasource('sqloledb', 'data
source=MySource;uid=MyUID;pwd=MyPWD').pubs.dbo.sp_execsql N'create
procedure dbo.junk as select getdate()'
I know it's not the prettiest, but it DOES work.
> If you tried it (in s2k), you would realize that you cannot use 3 part
> naming for creating procedures or functions. These statements are limited
> to accepting an owner name (optional) and an object name.
> Try the following statement:
> create procedure pubs.dbo.junk as select getdate()
--
new|||Hello,
I suggest that you refer to the following web site:
http://www.databasejournal.com/features/mssql/article.php/3441031
You may try to use sp_MSforeachdb. I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.