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

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'.

Recommended Answers

All 2 Replies

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 but the user can not

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.

This is a really weird use-case, I wouldn't recommend it. A better approach is to use a single database and make your dynamic objects (views, functions, procedures etc) aware of the financial year.

Then you'd reap multiple benefits. Firstly, one version of everything, you know which is the truth. Secondly, you can do more useful things with your data. For example, if you want to compare this year's profits to last year's, it's easy.

In PostgreSQL, you can specify a template when you create a DB, you could use that strategy to make your original approach work. Or you could clone your 2018 database and rename it to 2019, then truncate all the tables.

But like I said, you're best sticking to one database and keeping it simple and obvious.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.