Create stored procs in new DB from within a stored proc?

In our application, we dynamically create new databases using a stored
procedure. Each new database must have a few required stored procedures
created in it. Since SQL Server does not allow the specification of a
different database context for creation of procedures or functions, the
current workaround is to define a system stored procedure in Master that
creates the stored procs. We call this in the context of the new DB after it
is created. This works fine, but an approach that does not require the use o
f
any system databases would be preferred.
Is there a better way to accomplish this without Master or Model (in
pseudocode):
create procedure usp_NewDB
@.DBName
as
begin
create database @.DBName
create procedure @.DBName.dbo.SP1 as ...
create procedure @.DBName.dbo.SP2 as ...
endYou cannot do this with a variable in that way...
http://www.sommarskog.se/dynamic_sql.html
"ScottL" <ScottL@.community.nospam> wrote in message
news:C1C4D5F3-F2C8-4970-9368-742D1D55FEBE@.microsoft.com...
> In our application, we dynamically create new databases using a stored
> procedure. Each new database must have a few required stored procedures
> created in it. Since SQL Server does not allow the specification of a
> different database context for creation of procedures or functions, the
> current workaround is to define a system stored procedure in Master that
> creates the stored procs. We call this in the context of the new DB after
> it
> is created. This works fine, but an approach that does not require the use
> of
> any system databases would be preferred.
> Is there a better way to accomplish this without Master or Model (in
> pseudocode):
> create procedure usp_NewDB
> @.DBName
> as
> begin
> create database @.DBName
> create procedure @.DBName.dbo.SP1 as ...
> create procedure @.DBName.dbo.SP2 as ...
> end
>|||Yes, I know. That's why I said it was pseudocode. The issue is not one of
dynamic SQL, it is of creating a stored procedure in a different database
context. Regardless of dynamic SQL, the syntax CREATE PROCEDURE
<DBName>.dbo.<SPName> is not valid, since you cannot specify the database
name with CREATE PROCEDURE. Let me rephrase it for you more simply:
How can I create a stored procedure in Database_B from a stored procedure
running in Database_A?
"Aaron Bertrand [SQL Server MVP]" wrote:

> You cannot do this with a variable in that way...
> http://www.sommarskog.se/dynamic_sql.html
>
> "ScottL" <ScottL@.community.nospam> wrote in message
> news:C1C4D5F3-F2C8-4970-9368-742D1D55FEBE@.microsoft.com...
>
>|||> Let me rephrase it for you more simply:
> How can I create a stored procedure in Database_B from a stored procedure
> running in Database_A?
Let me answer for you "more simply":
EXEC('USE '+@.DBName+'; CREATE PROCEDURE ... ');|||Aaron Bertrand [SQL Server MVP] wrote:
> Let me answer for you "more simply":
>
Aaron, you're not getting annoyed are you :)
Remember people should EXPECT to get their questions answered promptly
and in a way they feel is appropriate, we have to make an effort
keeping them happy ;)
/impslayer, aka Birger Johansson|||Strange response, but thanks anyway. This syntax will not work and actually
executing it would result in:
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Let me answer for you "more simply":
> EXEC('USE '+@.DBName+'; CREATE PROCEDURE ... ');
>
>|||> Aaron, you're not getting annoyed are you :)
The implication I got was, here idiot, since the original question was too
complex for you, let me dumb it down.|||Yes, if you can dynamic sql.
Context switch not change in stored procedure.
You can refer below my example
-- S2K SP3
DECLARE @.I_DB_NAME NVARCHAR(200)
SET @.I_DB_NAME='Demo'
DECLARE @.proc NVARCHAR(4000)
SELECT @.proc =QUOTENAME(@.I_DB_NAME) + '.dbo.sp_execresultset'
EXEC @.proc 'CREATE VIEW t2 AS SELECT GETDATE() D'
"ScottL"?? ??? ??:

> In our application, we dynamically create new databases using a stored
> procedure. Each new database must have a few required stored procedures
> created in it. Since SQL Server does not allow the specification of a
> different database context for creation of procedures or functions, the
> current workaround is to define a system stored procedure in Master that
> creates the stored procs. We call this in the context of the new DB after
it
> is created. This works fine, but an approach that does not require the use
of
> any system databases would be preferred.
> Is there a better way to accomplish this without Master or Model (in
> pseudocode):
> create procedure usp_NewDB
> @.DBName
> as
> begin
> create database @.DBName
> create procedure @.DBName.dbo.SP1 as ...
> create procedure @.DBName.dbo.SP2 as ...
> end
>|||Here's a way to bypass the parser.
DECLARE @.sql VARCHAR(255);
SET @.sql = 'USE tempdb; EXEC(''CREATE PROCEDURE dbo.foo AS SELECT bar =
1'');';
EXEC(@.sql);
GO
EXEC tempdb.dbo.foo;
GO
USE tempdb;
GO
DROP PROCEDURE dbo.foo;
GO|||Aaron Bertrand [SQL Server MVP] skrev:

> The implication I got was, here idiot, since the original question was too
> complex for you, let me dumb it down.
Yeah, I interpreted it the same was as you, and my reply was intended
to support you, in a somewhat humorously way. Not sure I succeeded
though :)
/impslayer, aka Birger Johanssonsql

Create related tables from XML schema

I'm new to XML.
I received a schema from one of our vendors (please see below)
I would like to create SQLserver tables required for exporting XML file
later using this schema.
Is there a way to do this in SQLserver?
Thanks
Bill
<CDData> XML Schema
<?xml version="1.0"?>
<!-- CD Schema Basic version 3.2.1 3/2/2004 -->
<!-- SubElements and Attributes for each Element must appear in order
shown -->
<Schema name="CD_Routedata_XML_Schema"
xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name='PRODUCT' content='eltOnly' model='closed'>
<AttributeType name='PRODNAME' dt:type='string' required='no'/>
<AttributeType name='PRODID' dt:type='string' required='no'/>
<AttributeType name='INVOICE' dt:type='string' required='no'/>
<AttributeType name='QTY' dt:type='number' required='no'/>
<Attribute type='PRODNAME'/>
<Attribute type='PRODID'/>
<Attribute type='INVOICE'/>
<Attribute type='QTY'/>
</ElementType>
<ElementType name='PRODUCTS' content='eltOnly' model='closed'>
<Element type='PRODUCT' minOccurs='0' maxOccurs='*'/>
</ElementType>
<ElementType name='ACCTNAME' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='ACCTID' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='DRIVEDISTTO' content='textOnly' dt:type='number'
model='closed'/>
<ElementType name='DRIVETIMETO' content='textOnly' dt:type='time'
model='closed'/>
<ElementType name='ARRIVE' content='textOnly' dt:type='datetime'
model='closed'/>
<ElementType name='DURATION' content='textOnly' dt:type='time'
model='closed'/>
<ElementType name='DEPART' content='textOnly' dt:type='datetime'
model='closed'/>
<ElementType name='CODE' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='STOP' content='eltOnly' model='closed'>
<AttributeType name='NUM' dt:type='ui2' required='yes'/>
<Attribute type='NUM'/>
<Element type='ACCTNAME' minOccurs='0' maxOccurs='1'/>
<Element type='ACCTID' minOccurs='1' maxOccurs='1'/>
<Element type='DRIVEDISTTO' minOccurs='0' maxOccurs='1'/>
<Element type='DRIVETIMETO' minOccurs='0' maxOccurs='1'/>
<Element type='ARRIVE' minOccurs='0' maxOccurs='1'/>
<Element type='DURATION' minOccurs='0' maxOccurs='1'/>
<Element type='DEPART' minOccurs='0' maxOccurs='1'/>
<Element type='CODE' minOccurs='0' maxOccurs='1'/>
<Element type='PRODUCTS' minOccurs='0' maxOccurs='1'/>
</ElementType>
<ElementType name='DRIVERID' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='CODRIVERID' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='TRAILER1ID' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='TRAILER2ID' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='TRAILER3ID' content='textOnly' dt:type='string'
model='closed'/>
<ElementType name='ROUTE' content='eltOnly' model='closed'>
<AttributeType name='DATE' dt:type='datetime' required='yes'/>
<AttributeType name='SITEID' dt:type='string' required='yes'/>
<AttributeType name='ROUTEID' dt:type='string' required='yes'/>
<Attribute type='DATE'/>
<Attribute type='ROUTEID'/>
<Attribute type='SITEID'/>
<Element type='DRIVERID' minOccurs='0' maxOccurs='1'/>
<Element type='CODRIVERID' minOccurs='0' maxOccurs='1'/>
<Element type='TRAILER1ID' minOccurs='0' maxOccurs='1'/>
<Element type='TRAILER2ID' minOccurs='0' maxOccurs='1'/>
<Element type='TRAILER3ID' minOccurs='0' maxOccurs='1'/>
<Element type='STOP' minOccurs='1' maxOccurs='*'/>
</ElementType>
<ElementType name='MOBIUSROUTES' content='eltOnly' model='closed'>
<AttributeType name='ADD' dt:type='string' required='no'/>
<Attribute type='ADD'/>
<Element type='ROUTE' minOccurs='1' maxOccurs='*'/>
</ElementType>
<ElementType name='CADECDATA' content='eltOnly' model='closed'>
<Element type='MOBIUSROUTES' minOccurs='0' maxOccurs='1'/>
</ElementType>
<ElementType name='BASE' content='eltOnly' model='open'>
<Element type='CADECDATA' minOccurs='0' maxOccurs='1'/>
</ElementType>
</Schema>
Look at the SchemaGen option of the SQLXML XML Bulkload object.
Best regards
Michael
"Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:eCA47z88GHA.3280@.TK2MSFTNGP02.phx.gbl...
> I'm new to XML.
> I received a schema from one of our vendors (please see below)
> I would like to create SQLserver tables required for exporting XML file
> later using this schema.
> Is there a way to do this in SQLserver?
> Thanks
> Bill
> --
> <CDData> XML Schema
> <?xml version="1.0"?>
> <!-- CD Schema Basic version 3.2.1 3/2/2004 -->
> <!-- SubElements and Attributes for each Element must appear in order
> shown -->
> <Schema name="CD_Routedata_XML_Schema"
> xmlns="urn:schemas-microsoft-com:xml-data"
> xmlns:dt="urn:schemas-microsoft-com:datatypes">
> <ElementType name='PRODUCT' content='eltOnly' model='closed'>
> <AttributeType name='PRODNAME' dt:type='string' required='no'/>
> <AttributeType name='PRODID' dt:type='string' required='no'/>
> <AttributeType name='INVOICE' dt:type='string' required='no'/>
> <AttributeType name='QTY' dt:type='number' required='no'/>
> <Attribute type='PRODNAME'/>
> <Attribute type='PRODID'/>
> <Attribute type='INVOICE'/>
> <Attribute type='QTY'/>
> </ElementType>
> <ElementType name='PRODUCTS' content='eltOnly' model='closed'>
> <Element type='PRODUCT' minOccurs='0' maxOccurs='*'/>
> </ElementType>
> <ElementType name='ACCTNAME' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='ACCTID' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='DRIVEDISTTO' content='textOnly' dt:type='number'
> model='closed'/>
> <ElementType name='DRIVETIMETO' content='textOnly' dt:type='time'
> model='closed'/>
> <ElementType name='ARRIVE' content='textOnly' dt:type='datetime'
> model='closed'/>
> <ElementType name='DURATION' content='textOnly' dt:type='time'
> model='closed'/>
> <ElementType name='DEPART' content='textOnly' dt:type='datetime'
> model='closed'/>
> <ElementType name='CODE' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='STOP' content='eltOnly' model='closed'>
> <AttributeType name='NUM' dt:type='ui2' required='yes'/>
> <Attribute type='NUM'/>
> <Element type='ACCTNAME' minOccurs='0' maxOccurs='1'/>
> <Element type='ACCTID' minOccurs='1' maxOccurs='1'/>
> <Element type='DRIVEDISTTO' minOccurs='0' maxOccurs='1'/>
> <Element type='DRIVETIMETO' minOccurs='0' maxOccurs='1'/>
> <Element type='ARRIVE' minOccurs='0' maxOccurs='1'/>
> <Element type='DURATION' minOccurs='0' maxOccurs='1'/>
> <Element type='DEPART' minOccurs='0' maxOccurs='1'/>
> <Element type='CODE' minOccurs='0' maxOccurs='1'/>
> <Element type='PRODUCTS' minOccurs='0' maxOccurs='1'/>
> </ElementType>
> <ElementType name='DRIVERID' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='CODRIVERID' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='TRAILER1ID' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='TRAILER2ID' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='TRAILER3ID' content='textOnly' dt:type='string'
> model='closed'/>
> <ElementType name='ROUTE' content='eltOnly' model='closed'>
> <AttributeType name='DATE' dt:type='datetime' required='yes'/>
> <AttributeType name='SITEID' dt:type='string' required='yes'/>
> <AttributeType name='ROUTEID' dt:type='string' required='yes'/>
> <Attribute type='DATE'/>
> <Attribute type='ROUTEID'/>
> <Attribute type='SITEID'/>
> <Element type='DRIVERID' minOccurs='0' maxOccurs='1'/>
> <Element type='CODRIVERID' minOccurs='0' maxOccurs='1'/>
> <Element type='TRAILER1ID' minOccurs='0' maxOccurs='1'/>
> <Element type='TRAILER2ID' minOccurs='0' maxOccurs='1'/>
> <Element type='TRAILER3ID' minOccurs='0' maxOccurs='1'/>
> <Element type='STOP' minOccurs='1' maxOccurs='*'/>
> </ElementType>
> <ElementType name='MOBIUSROUTES' content='eltOnly' model='closed'>
> <AttributeType name='ADD' dt:type='string' required='no'/>
> <Attribute type='ADD'/>
> <Element type='ROUTE' minOccurs='1' maxOccurs='*'/>
> </ElementType>
> <ElementType name='CADECDATA' content='eltOnly' model='closed'>
> <Element type='MOBIUSROUTES' minOccurs='0' maxOccurs='1'/>
> </ElementType>
> <ElementType name='BASE' content='eltOnly' model='open'>
> <Element type='CADECDATA' minOccurs='0' maxOccurs='1'/>
> </ElementType>
> </Schema>
>

Create Database Script - Help Required

Hi,

I have a Database.sql script file that creates my application database (Malibu). My question is how do I get around the problem of the hard coded FILENAME as shown below...

CREATE DATABASE [Malibu] ON (NAME = N'Malibu_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Malibu_Data.MDF' , SIZE = 4, FILEGROWTH = 10%) LOG ON (NAME = N'Malibu_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Malibu_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)

If this path does not exist on the clients PC - the script will fail.

Any suggestions appreciated.

Thanks.

Steve.Build the script as a string and then execute the string.

Declare @.strScript as varchar(5000)

SET @.strScript = 'CREATE DATABASE [Malibu] FILENAME = ' + @.thecorrectpathhereasavariable + '\Malibu_Data.MDF' '

Exec(@.strScript)

How you actually determine the path I do not know, but you can variableize the script as the above example.|||Thanks Wes,

I currently run the script from a batch file using OSQL, however, the way things are heading with .Net, I guess I should look at doing it in VB code.

Steve.|||The code I posted was T-SQL code.