Show error.... How to copy a store procedure one to another database same server

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

, +

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'

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

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

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

try 4

SQL

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

Error4 :

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

Posted 19-Oct-18 6:55am

Jayanta Modak

Updated 22-Oct-18 8:08am

v3

Add a Solution

Comments

RedDk 19-Oct-18 13:05pm

I think the best idea, or what I usually do when I copy a stoproc from one database to another database, is right-click the target procedure and choose "modify". The procedure will then open in the editor and I can change the word "ALTER" to "CREATE" ... then save it as .sql script. Run it on the target database (the one without it).

Typically there are few other things which might have to be added to the target database as well, possibly a SCHEMA, and any reference which was on the origin database.

Jayanta Modak 19-Oct-18 13:24pm

Thanks sir for replying
Actually i used this sql command in vb.net 2010. when user click on create new db by click a button after that automatically copy all table and stock ledger balance and store procedure and function. when i copy store procedure then i facing the problem.
i can do it "I think the best idea, or what I usually do when I copy a stoproc from one database to another database, is right-click the target procedure and choose "modify". The procedure will then open in the editor and I can change the word "ALTER" to "CREATE" ... then save it as .sql script. Run it on the target database (the one without it)." but the user can not
please help me sir.

RedDk 19-Oct-18 13:45pm

Ok. So what I think you're saying is that the user doesn't have permission to ALTER a stored procedure? My question then is => "why does the user need to ALTER a stored procedure?" If you're saying that a user can't run a stoproc I'd say there's no point in copying all the stoprocs to the target database.

[EDIT]
Maybe I read that wrong. Try ... SELECT * FROM sys.procedures ... and see if the procedure name you require shows up in the list of results. As it stands that INTO clause seems suspect.
[END EDIT]

Jayanta Modak 19-Oct-18 14:19pm

I want to change the database every financial year.
If I change the database and copy all table only then how to run store procedure? Because store procedure stored in database. User have no permission to change any part of program of the project and do not access the the database.

Stored procedure name is correct and it is stored in database "db_17_18

RedDk 19-Oct-18 14:50pm

Ok. I just told you how to copy a stored procedure from one database to another. Your question is answered. Sorry, can't help you with this new stuff.

ZurdoDev 19-Oct-18 15:20pm

Do you understand this error? " contains more than the maximum number of prefixes. The maximum is 2."

Jayanta Modak 19-Oct-18 21:46pm

yes sir then i used this
select * into DB_18_19.dbo.P_Add_FY_Data from DB_17_18.dbo.P_Add_FY_Data

ZurdoDev 19-Oct-18 21:51pm

Then no, you don't understand it. What do you think DB_18_19.dbo.P_Add_FY_Data from DB_17_18.dbo.P_Add_FY_Data means?

It should be databasename.schema.tableorview. You have way too many periods.

Jayanta Modak 19-Oct-18 22:10pm

oh! sorry sir . please forgive me I don't know good english. sorry.
I've copied all my tables in this process. So, i think stored procedure copy process is same.
Please help me sir.... how to copy a stored procedure

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RedDk · Answer 1 · 2018-10-22T08:08:00

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.

Show error.... How to copy a store procedure one to another database same server

1 solution

Solution 1

Add your solution here

Preview 0

Show error.... How to copy a store procedure one to another database same server

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us