473,573 Members | 3,123 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Question about procedures to create procedures in a different database

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/
Jul 23 '05 #1
10 2145

"John A Fotheringham" <ja*@jafsoft.co m> wrote in message
news:0b******** *************** *********@4ax.c om...
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
Jul 23 '05 #2
"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/
Jul 23 '05 #3

"John A Fotheringham" <ja*@jafsoft.co m> wrote in message
news:ba******** *************** *********@4ax.c om...
"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
Jul 23 '05 #4
John A Fotheringham (ja*@jafsoft.co m) 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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5
>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/
Jul 23 '05 #6
John A Fotheringham (ja*@jafsoft.co m) 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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #7
>You could do:

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


I didn't think that

CREATE PROCEDURE otherDatabase.d bo.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/
Jul 23 '05 #8
>> - 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/
Jul 23 '05 #9
John A Fotheringham (ja*@jafsoft.co m) 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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
1661
by: Martin Feuersteiner | last post by:
Dear Group I have found that table A had SELECT permissions for 'Public' but not table B. Giving 'Public' SELECT permissions on table B did the trick. HOWEVER, I don't want anyone to be able to do a direct SELECT on table A or B but only give them access to the data by using the stored procedures. Is there any way this can be set up?
2
4293
by: J.Beaulieu | last post by:
Hi I'll have probably to use sql server soon but prior to that I have a question concerning priviledges and security. Is it possible for someone to do like in access, ie creating a db/table that is locked with a password? My guess is that it will be yes but in cas of... Now is it possible for someone to make a db/table read only rather...
11
10736
by: jrefactors | last post by:
I want to know the differences between SQL Server 2000 stored procedures and oracle stored procedures? Do they have different syntax? The concept should be the same that the stored procedures execute in the database server with better performance? Please advise good references for Oracle stored procedures also. thanks!!
8
7913
by: Thomasb | last post by:
With a background in MS SQL Server programming I'm used to temporary tables. Have just started to work with DB2 ver 7 on z/OS and stumbled into the concept of GLOBAL TEMPORARY TABLE. I have created a temporary database with a tables space. Verified that DECLARE GLOBAL TEMPORARY TABLE TEMP (A INTEGER); INSERT INTO SESSION.TEMP VALUES(10);...
5
1822
by: serge | last post by:
What is the best way to run one command and have a database be created and sql scripts run on it to create the tables, indexes, triggers, procedures, etc.? Is there an existing tool free or commercial to automate this? Thank you
8
1220
by: Mark | last post by:
Hi, I am building an application and I have always built applications like so; Presentation layer (Html, Webforms etc). Logic layer (Database access routines etc). Data layer (SQL database tables, stored procedures etc). My question is about the Data layer. I have always written MSSQL stored procedures to do everything (Even deleting a...
0
2634
by: Amber | last post by:
Stored procedures are faster and more efficient than in-line SQL statements. In this article we will look at two SQL Server stored procedures; one using an input parameter and one not, and see how to call them from an ASP.Net page Every modern database system has a stored procedure language. SQL Server is no different and has a relatively...
9
1483
by: Mike Hofer | last post by:
In a large application I'm working on (ASP.NET 1.1, VS2003), we have a base class that wraps stored procedures. Essentially, this base class (StoredProcedureBase) encapsulates the code to set up the connection, transaction, command and parameters required to invoke a stored procedure on our SQL Server database. It provides helper methods that...
3
1379
by: Mike Husler | last post by:
We have a SQL Server database that can accept data from several different projects throughout our lab. Is it better to (1) let each developer create their own prepared SQL statements for inserts or to (2) hide the schema from them and have them call stored procedures or views to get the data into the database? So for example, in the 1st...
0
7778
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8017
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8201
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7777
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
1
5585
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3731
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2203
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1300
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1039
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.