显示错误....如何将存储过程复制到另一个数据库同一服务器 [英] Show error.... How to copy a store procedure one to another database same server

问题描述

如何将商店程序复制到同一服务器中的另一个数据库



我在一个项目上工作。我希望从旧财政年度 db_17_18 复制全部或单个商店程序到新财政年度 db_18_19 我想使用sql server命令执行此操作。我尝试但显示错误请帮助我....

i二手：

SQL_Server 2008 r2

服务器名称：shop04



我尝试过：



试一下

How to copy a store procedure one to another database in same server

I work on a project. I want copy all or single store procedure from old financial year db_17_18 to new financial year db_18_19 I want to do it using sql server command. i try it but showing error Please help me....
i used :
SQL_Server 2008 r2
server name : shop04

What I have tried:

try 1

select * into DB_18_19.dbo.P_Add_FY_Data.procedures from DB_17_18.P_Add_FY_Data.procedures WHERE  type_desc='SQL_STORED_PROCEDURE'

错误1：

消息117，级别15，状态1，行1

对象名称'DB_18_19。 dbo.P_Add_FY_Data.procedures'包含的前缀数量超过最大数量。最高为2.



尝试2

Error1 :
Msg 117, Level 15, State 1, Line 1
The object name 'DB_18_19.dbo.P_Add_FY_Data.procedures' contains more than the maximum number of prefixes. The maximum is 2.

try 2

select * into DB_18_19.dbo.P_Add_FY_Data.procedures from DB_2017_2018.P_Add_FY_Data.procedures

错误2：

消息117，级别15，状态1，行1

对象名称DB_18_19.dbo.P_Add_FY_Data.procedures包含的内容超过最大前缀数。最高为2.



尝试3

Error2 :
Msg 117, Level 15, State 1, Line 1
The object name 'DB_18_19.dbo.P_Add_FY_Data.procedures' contains more than the maximum number of prefixes. The maximum is 2.

try 3

select * into DB_18_2019.dbo.P_Add_FY_Data from DB_17_18.P_Add_FY_Data

错误3：

消息208，等级16，状态1，第1行

无效的对象名称'DB_2017_2018.P_Add_FY_Data'。





尝试4

Error3 :
Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB_2017_2018.P_Add_FY_Data'.


try 4

select * into DB_18_19.dbo.P_Add_FY_Data from DB_17_18.P_Add_FY_Data WHERE  type_desc='SQL_STORED_PROCEDURE'

错误4：



消息208，级别16，状态1，行1

无效的对象名称'DB_17_2018.P_Add_FY_Data' 。

Error4 :

Msg 208, Level 16, State 1, Line 1
Invalid object name 'DB_17_2018.P_Add_FY_Data'.

推荐答案

如何复制存储过程。



1.打开MSSQL Server Management Studio



2.在对象浏览器中查找存储过程所在的数据库。它将在可编程性下再次位于数据库树视图下的数据库名称下，然后再次位于存储过程下



3.看看你的东西想要复制并注意它也有一个SCHEMA，它也可能必须以同样的方式复制，并且该模式位于SECURITY下并再次位于SCHEMAS下。



4.右键单击要复制的存储过程，然后选择修改。这将导致.sql文件形式的某些TSQL在编辑器中可见。请注意，USE子句声明这整个代码块仅仅附加到此数据库的内容，从该数据库复制此存储过程。另请注意，SCHEMA参考同样是硬编码。当你选择修改一个站立的STORED PROCEDURE时，最后看到与你用鼠标做了什么相关的词......ALTER PROCEDURE......那里你必须做一个叉子，可以这么说，代码。



5.保存这个.sqlalter procedure代码块。在将其保存为原始数据库存储过程的新.sql副本之前，请将其命名为SQLQuery1.sql。将其另存为Huzzah_StoredProcedureOne_ALTER_Copy.sql。关闭SQLQuery1.sql而不保存。打开Huzzah_StoredProcedureOne_ALTER_Copy.sql。



6.现在你必须将ALTER关键字更改为CREATE，显然是因为你要运行这个脚本来创建对某些其他数据库的过程，以便将代码放入该数据库的PROGRAMMABILITY中。尝试更改不存在的过程将不起作用。



7.将ALTER-replacement-with-CREATE脚本保存为Huzzah_StoredProcedureOne_CREATE.sql。



8.现在在SECURITY / SCHEMAS下检查目标/新数据库中是否存在SCHEMA。如果没有，你需要在某处添加几行以便创建SCHEMA，这样当你运行新的STORED PROCEDURE时它就不会出错（这就是我的意思是复制SCHEMA（它不是真正的副本是它？））。当然，我假设你所引用的所有内容，以及所有其他你应该成功复制到这个新数据库的内容。



9.一旦你设法将它全部排序就按F5，你的STORED PROCEDURE现在应该是显而易见的......而且......再次也是ALTERable。



如果这些步骤仍然悬而未决，请务必在发布问题时附上错误消息，就像之前一样。它缩小了FAIL似乎更容易停止的地方。

How to copy a stored procedure.

1. Open MSSQL Server Management Studio

2. Look in Object Browser to find database in which the stored procedure resides. It'll be located in the Databases treeview under your database's name again under "Programmability" and then again under "Stored Procedures"

3. Look and see the thing you want to copy and note that it also has a SCHEMA which might also have to be copied in this same manner and that schema is located under "SECURITY" and again under "SCHEMAS".

4. Right-click on the stored procedure you want to copy and select "MODIFY". This will cause some TSQL in the form of a .sql file to become visible in your editor. Note that the "USE" clause is stating that this whole block of code is sorta attached to only things realted to this database from which this stored procedure is being copied. Also note that the SCHEMA reference is likewise "hard-coded". And finally see the words related to what you you just did with your mouse when you chose to MODIFY a standing STORED PROCEDURE ... "ALTER PROCEDURE" ... there's where you'll have to do a "fork", so to speak, of the code.

5. Save this .sql "alter procedure" code block. Before you save it as your new .sql copy of the origin database stored procedure, sitting in the editor, it's named "SQLQuery1.sql". Save it as "Huzzah_StoredProcedureOne_ALTER_Copy.sql". Close SQLQuery1.sql without save. Open "Huzzah_StoredProcedureOne_ALTER_Copy.sql".

6. Now you've got to change the ALTER keyword to CREATE, obviously because you're going to run this script to CREATE PROCEDURE on some other database in order to get the code into the PROGRAMMABILITY withers of that database. Trying to ALTER a procedure which doesn't exist will not work.

7. Save the ALTER-replaced-with-CREATE script as "Huzzah_StoredProcedureOne_CREATE.sql".

8. Now check in target/new database that the SCHEMA exists under SECURITY/SCHEMAS. If it doesn't you'll need to add a few lines somewhere in order to CREATE SCHEMA so that when you run the new STORED PROCEDURE it will not err (this is what I mean by copy the SCHEMA (it's not really a copy is it?)). And of course I'm assuming everything you reference as far as tables and all the other stuff you've supposedly succeeded at "copying" to this new database is present as well.

9. Hit F5 once you managed to get it all sorted, and your STORED PROCEDURE should now be apparent ... and ... again also ALTERable.

If these steps are still hanging up be sure to include the error messages which accompany them when you post questions, as you did before. It makes narrowing down the place where FAIL seems to be hanging out easier.

这篇关于显示错误....如何将存储过程复制到另一个数据库同一服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！