关于在不同数据库中创建过程的过程的问题 [英] Question about procedures to create procedures in a different database
问题描述
我正在尝试编写一个创建了新数据库的程序，然后
将在该新数据库中创建所有表格和
程序它。
在这样做的过程中，我遇到的问题是你不能在程序中发出USE
命令。
所以我的问题是
- 我该怎么解决这个问题？
- 如果我不能，怎么样我可以在*不同的*
（即新创建的）数据库中创建程序等
或
- 有没有更好的方法来做所有这些（*）
我目前有SQL文件，但我需要编辑
名称每次执行前都有数据库，所以我认为
程序会更好。我也希望最终通过网络界面公开一些
的这个功能。
虽然我是新手，但我觉得我''在深水中跳水。任何好的
指向数据库这方面所涉及的所有问题
管理将不胜感激。
（*）一认为发生在我身上的是一个模板。数据库，
然后以某种方式复制所有程序，表格，视图等。
-
HTML-to-text和markup使用Detagger删除
http://www.jafsoft.com/detagger/
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/
推荐答案
" John A Fotheringham" < ja*@jafsoft.com>在消息中写道
news：0b ******************************** @ 4ax.com ...
"John A Fotheringham" <ja*@jafsoft.com> wrote in message
news:0b********************************@4ax.com...
我正在尝试编写一个创建了新数据库的程序，然后将在该新数据库中创建所有表格和
程序。在这样做的过程中，我遇到的问题是你不能在程序中发出USE
命令。
所以我的问题是
- 我如何解决这个问题？
- 如果我不能，我怎么能在*不同的*
（即新创建的）中创建程序等数据库
- 有没有更好的方法来做这一切（*）
我有SQL文件，目前这样做，但我每次执行前都需要编辑数据库的名称，所以我认为
程序会更好。我也希望最终通过网络界面公开一些这方面的功能。
虽然我是新手，但我觉得自己正在深入潜水。任何有关数据库管理这方面所涉及的所有问题的指示都将不胜感激。
（*）我想到的一个想法就是拥有一个模板 ;数据库，然后以某种方式复制所有程序，表格，视图等。
-
用Detagger删除HTML到文本和标记
http://www.jafsoft.com/detagger/
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/
''模板''数据库是一个相对简单的解决方案 - 备份源
数据库，在目标系统上恢复它（你可以更改数据库名称
和恢复期间的文件位置（如果你愿意）并且已经完成。但是你没有
有任何灵活性，并且没有办法''升级'到新版本的
你的表，过程，函数
最好的方法通常是将每个CREATE脚本视为源代码，并使用
a源控制系统（VSS或其他）。然后你可以获取文件来创建每个对象，使用自定义的
脚本或osql.exe在目标数据库上运行它们，并以这种方式构建数据库。你在运行脚本时选择服务器
和数据库名称（例如，
osql.exe的-S和-d开关），所以它也是更容易针对多个数据库运行。这是
更灵活，更好的长期解决方案，但当然你要做到这一点
必须预先投入更多时间。
Erland有一个这里描述的解决方案，它应该给你一些好主意：
http://www.abaris.se/abaperls/index.html
Simon
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
< br>
" Simon Hayes" < sq*@hayes.ch>写道：
"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.
所有数据库都在一台服务器上。还有其他
参数。例如，当我创建一个新的数据库时，我还要添加一个新的用户登录以允许访问这个数据库，并且必须在新数据库上同时授予
grant privs。一些共享数据库。
我能将这些参数传递给osql.exe吗？我认为这是'b $ ba小问题，因为添加用户并授予权限很容易就可以将数据库和表格分成不同的程序
$ /
已创建。
我更喜欢程序路线有两个原因
- 更容易更改程序并且有他们将
加载到新的（或现有的）数据库中
- 从网上调用这样一个脚本的更好前景
界面（虽然它可能没有多少）。
然后你可以获取文件来创建每个对象，使用自定义的
脚本或osql对目标数据库运行它们.exe，并以这种方式构建数据库。您在运行脚本时选择服务器
和数据库名称（例如，用于
osql.exe的-S和-d开关），因此对多个数据库运行也更容易。
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.
谢谢你。这会做我想要的，但我希望有一些在命名数据库中运行程序的等价方式
-
HTML使用Detagger删除文本和标记
http：//www.jafsoft。 com / detagger /
" John A Fotheringham" < ja*@jafsoft.com>在消息中写道
新闻：ba ******************************** @ 4ax.com ...
"John A Fotheringham" <ja*@jafsoft.com> wrote in message
news:ba********************************@4ax.com...
" Simon Hayes" < sq*@hayes.ch>写道：
"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.
所有数据库都在一台服务器上。还有其他涉及的参数。例如，当我创建一个新数据库时，我还添加了一个新的用户登录名以允许访问该数据库，并且必须在新数据库和某些共享数据库上授予权限。
我能将这些参数传递给osql.exe吗？我认为这是一个小问题，因为一旦创建了数据库和表格，就可以轻松地将添加用户和授予权限分成不同的程序。
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.
这取决于参数是什么 - osql.exe在
方面有点受到尊重，但如果它类似于数据库名称或当前登录，
然后您可以在
脚本中使用db_name（）或suser_sname（）等函数。否则，存储过程是一个合理的选择 - 你可以在它之后从你的主/包装器脚本执行它
并且所有其他对象都是
创建的，以及任何参数你需要。
我更喜欢程序路线有两个原因
- 更容易更改程序并将它们装入新的（或现有的） ）数据库
- 从web界面调用这样一个脚本的更好前景（虽然可能没有多少）。
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).
如果您需要从ASP或其他任何东西运行，您可能需要查看
ADO，和/或考虑使用Perl / Python / C＃等进行部署
脚本。
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.
然后你可以获取文件来创建每个对象，用你的目标数据库运行它们自定义
脚本或osql.exe，并以这种方式构建数据库。您在运行脚本时选择服务器
和数据库名称（例如，用于
osql.exe的-S和-d开关），因此对多个数据库运行也更容易。
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
这是你的事吗想要吗？
exec db1.dbo.myproc
exec db2.dbo.myproc
但程序有当然首先存在，所以你仍然需要某种方式
在那里创建它。
Simon
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
这篇关于关于在不同数据库中创建过程的过程的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
