Question about procedures to create procedures in a different database

[John A Fotheringham]

I'm trying to write a procedure that having created a new database,
will then create within that new database all the tables and
procedures that go with it.

In doing this I'm hitting the problem that you can't issue a USE
command within a procedure.

So my question is either

- how do I get around this?
- if I can't, how can I create procedures etc in a *different*
(i.e. the newly created) database

or

- is there a better way to do all this (*)

I have SQL files that do this currently, but I need to edit in the
name of the database each time before execution, so I thought a
procedure would be better. Also I'd like eventually to expose some
of this functionality via a web interface.

Although I'm a newbie, I feel I'm diving in the deep end. Any good
pointers to all the issues involved in this aspect of database
management would be appreciated.

(*) One thought that occurs to me is to have a "template" database,
and to then somehow copy all procedures, tables, view etc from that.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/

[Simon Hayes]

"John A Fotheringham" <ja*@jafsoft.com> wrote in message
news:0b********************************@4ax.com...

I'm trying to write a procedure that having created a new database,
will then create within that new database all the tables and
procedures that go with it.

In doing this I'm hitting the problem that you can't issue a USE
command within a procedure.

So my question is either

- how do I get around this?
- if I can't, how can I create procedures etc in a *different*
(i.e. the newly created) database

or

- is there a better way to do all this (*)

I have SQL files that do this currently, but I need to edit in the
name of the database each time before execution, so I thought a
procedure would be better. Also I'd like eventually to expose some
of this functionality via a web interface.

Although I'm a newbie, I feel I'm diving in the deep end. Any good
pointers to all the issues involved in this aspect of database
management would be appreciated.

(*) One thought that occurs to me is to have a "template" database,
and to then somehow copy all procedures, tables, view etc from that.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/

A 'template' database is a relatively easy solution - backup a source
database, restore it on your target system (you can change the database name
and file locations during restore if you want) and it's done. But you don't
have any flexibility, and there's no way to 'upgrade' to a new version of
your tables, procs, functions etc.

The best way is usually to treat every CREATE script as source code, and use
a source control system (VSS or whatever). Then you can get the files to
create each object, run them against your target database with a custom
script or osql.exe, and build the database that way. You choose the server
and database name when you run the script (eg. the -S and -d switches for
osql.exe), so it's also easier to run against multiple databases. This is
much more flexible and a better long-term solution, but of course you do
have to invest more time up front.

Erland has a solution described here, which should give you some good ideas:

http://www.abaris.se/abaperls/index.html

Simon

[John A Fotheringham]

"Simon Hayes" <sq*@hayes.ch> wrote:

(*) One thought that occurs to me is to have a "template" database,
and to then somehow copy all procedures, tables, view etc from that.
--
A 'template' database is a relatively easy solution - backup a source
database, restore it on your target system (you can change the database name
and file locations during restore if you want) and it's done. But you don't
have any flexibility, and there's no way to 'upgrade' to a new version of
your tables, procs, functions etc.

All the databases would be on the one server. Also there are other
parameters involved. For example when I create a new database I also
add a new user login to allow access to this database, and have to
grant privs on the new database and also on some shared databases.

Would I be able to pass such parameters to osql.exe? I think that's
a minor issue, as adding users and granting privs could easily be
separated out into a different procedure once the database and tables
have been created.

I'd prefer the procedure route for two reasons

- easier to change the procedures and have them loaded
into the new (or existing) databases

- better prospects of invoking such a script from a web
interface (though there may not be much in it).
Then you can get the files to
create each object, run them against your target database with a custom
script or osql.exe, and build the database that way. You choose the server
and database name when you run the script (eg. the -S and -d switches for
osql.exe), so it's also easier to run against multiple databases.

Thanks for that. That would do what I wanted, but I hoped there was
some equivalent way of running a procedure within a named database
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/

[Simon Hayes]

"John A Fotheringham" <ja*@jafsoft.com> wrote in message
news:ba********************************@4ax.com...

"Simon Hayes" <sq*@hayes.ch> wrote:

(*) One thought that occurs to me is to have a "template" database,
and to then somehow copy all procedures, tables, view etc from that.
--
A 'template' database is a relatively easy solution - backup a source
database, restore it on your target system (you can change the database
name
and file locations during restore if you want) and it's done. But you
don't
have any flexibility, and there's no way to 'upgrade' to a new version of
your tables, procs, functions etc.

All the databases would be on the one server. Also there are other
parameters involved. For example when I create a new database I also
add a new user login to allow access to this database, and have to
grant privs on the new database and also on some shared databases.

Would I be able to pass such parameters to osql.exe? I think that's
a minor issue, as adding users and granting privs could easily be
separated out into a different procedure once the database and tables
have been created.

It depends on what the parameters would be - osql.exe is a bit limited in
that respect, but if it's something like database name or current login,
then you can just use functions like db_name() or suser_sname() inside your
script. Otherwise, a stored proc is a reasonable option - you can execute it
from your main/wrapper script after it and all the other objects are
created, and with whatever parameters you need.

I'd prefer the procedure route for two reasons

- easier to change the procedures and have them loaded
into the new (or existing) databases

- better prospects of invoking such a script from a web
interface (though there may not be much in it).

If you need to run things from ASP or whatever, you might want to look into
ADO, and/or consider using it from Perl/Python/C# etc. for your deployment
scripting.

Then you can get the files to
create each object, run them against your target database with a custom
script or osql.exe, and build the database that way. You choose the server
and database name when you run the script (eg. the -S and -d switches for
osql.exe), so it's also easier to run against multiple databases.

Thanks for that. That would do what I wanted, but I hoped there was
some equivalent way of running a procedure within a named database

Is this the sort of thing you want?

exec db1.dbo.myproc
exec db2.dbo.myproc

But the procedure has to exist first, of course, so you still need some way
of creating it there.

Simon

[Erland Sommarskog]

John A Fotheringham (ja*@jafsoft.com) writes:

I'm trying to write a procedure that having created a new database,
will then create within that new database all the tables and
procedures that go with it.

In doing this I'm hitting the problem that you can't issue a USE
command within a procedure.

So my question is either

- how do I get around this?
- if I can't, how can I create procedures etc in a *different*
(i.e. the newly created) database

or

- is there a better way to do all this (*)
Indeed.

Only try to do this in SQL only, if you really like to hurt yourself.

It's much better to do this from a client program, written in Perl,
VBscript, C++ or whatever your favourite. The nice thing is that
the program does not need to contain any SQL code - it could just
read of list of files to run. So if you need to create a new procedure,
you just add the file to the list. And if a stored procedure changes,
you simply edit the file for that procedure.

If you were to put this in a stored procedure, you would be in for an
massive amount of dynamic SQL, and maintenance would be a nightmare.
I have SQL files that do this currently, but I need to edit in the
name of the database each time before execution, so I thought a
procedure would be better. Also I'd like eventually to expose some
of this functionality via a web interface.

Web programming is not my game, but I am quite confident that there
is something called CGI that permits you to run scripts on the
web server. Surely web servers are not restricted to running stored
procedures only, are they?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[John A Fotheringham]

>Is this the sort of thing you want?

exec db1.dbo.myproc
exec db2.dbo.myproc

But the procedure has to exist first, of course, so you still need some way
of creating it there.

That's the catch-22 I'm banging my head against.

I'd like a procedure that creates procedures in a named database.
What I want is the equivalent of

create procedure db1.dbo.newproc

or

use db1
create procedure newproc

but neither option seems to work (I could be wrong though - I am a
newbie after all :-). If the first one worked I could embed it in an
EXEC statement with the db passed in as follows

EXEC ('
create procedure ' + @dbname + '.dbo.newproc....
')

I have procedures that use this approach for other things already.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/

[Erland Sommarskog]

John A Fotheringham (ja*@jafsoft.com) writes:

I'd like a procedure that creates procedures in a named database.
What I want is the equivalent of

create procedure db1.dbo.newproc

or

use db1
create procedure newproc

but neither option seems to work (I could be wrong though - I am a
newbie after all :-). If the first one worked I could embed it in an
EXEC statement with the db passed in as follows

EXEC ('
create procedure ' + @dbname + '.dbo.newproc....
')

I have procedures that use this approach for other things already.

You could do:

EXEC ('USE ' + @dbname + '
EXEC (''CREATE PROCEDURE '' + @dbname + ''.dbo.newproc AS
...
'')')

The double level of nested quotes are likely to drive you mad, but you
could do:

SELECT @myproc = 'CREATE PROCEDURE ' + @dbname + 'dbo.newproc AS
...
'
EXEC ('USE ' + @dbnmame + ' ' + @sql)
But if the procedure is more than 8000 characters you can't fit it into
a varchar(8000), but would have to split it up on more variables.

As I said, only go this road if you really enjoy hurting yourself.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[John A Fotheringham]

>You could do:

EXEC ('USE ' + @dbname + '
EXEC (''CREATE PROCEDURE '' + @dbname + ''.dbo.newproc AS
...
'')')

I didn't think that

CREATE PROCEDURE otherDatabase.dbo.test

worked. On my system I get

'CREATE PROCEDURE' does not allow specifying the database name
as a prefix to the object name.

but if the nested EXEC works with the USE, then I shouldn't need the
database name on the CRATE PROCEDURE command. As for being hell as
regards quotes, I'm used to that from other contexts.
[goes away and experiments]
Thanks! The following works

USE db1
GO

CREATE PROCEDURE test_1 AS
BEGIN

EXEC ('USE db2

EXEC (''CREATE PROCEDURE test_2 (@var varchar(10))
AS
BEGIN
PRINT @var
END
'')
')
END
GO

EXEC test_1
GO

USE db2
EXEC test_2 'it works!!'

I think I can live with that, and do everything I want with no more
that two levels of nested EXECs :-)
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/

[John A Fotheringham]

>> - is there a better way to do all this (*)

Indeed.

Only try to do this in SQL only, if you really like to hurt yourself.

It's much better to do this from a client program, written in Perl,
VBscript, C++ or whatever your favourite. The nice thing is that
the program does not need to contain any SQL code - it could just
read of list of files to run. So if you need to create a new procedure,
you just add the file to the list. And if a stored procedure changes,
you simply edit the file for that procedure.

If you were to put this in a stored procedure, you would be in for an
massive amount of dynamic SQL,
I don't think the performance is an issue in this context, as adding
new databases will be a rare event, and in any case the databases and
procedures concerned are not that extensive.
and maintenance would be a nightmare.

That may well be true :-)

Thanks for the advice.

I have SQL files that do this currently, but I need to edit in the
name of the database each time before execution, so I thought a
procedure would be better. Also I'd like eventually to expose some
of this functionality via a web interface.

Web programming is not my game, but I am quite confident that there
is something called CGI that permits you to run scripts on the
web server. Surely web servers are not restricted to running stored
procedures only, are they?

No, but I'm currently learning SQL, C# and ASP.NET simultaneously, and
so am looking for the path of least resistance for now, and C#/ASP.NET
make running a stored procedure fairly simple (in the sense that this
is something I have already got working :-)

--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/

[Erland Sommarskog]

John A Fotheringham (ja*@jafsoft.com) writes:

If you were to put this in a stored procedure, you would be in for an
massive amount of dynamic SQL,

I don't think the performance is an issue in this context, as adding
new databases will be a rare event, and in any case the databases and
procedures concerned are not that extensive.

I didn't mean to say that it wold be a performance issue. It's clear
that this is not a performance-critical thing. And in any case, the
overhead for dynamic SQL for the server is minimal.

The overhead for a human brain to cope with string embedded in string
literals that are embedded in string literals, on the other hand, is
considerable.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[John A Fotheringham]

>I didn't mean to say that it wold be a performance issue. It's clear

that this is not a performance-critical thing. And in any case, the
overhead for dynamic SQL for the server is minimal.

The overhead for a human brain to cope with string embedded in string
literals that are embedded in string literals, on the other hand, is
considerable.

Ah! I see what you mean :-)

--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/