Solved

Copy the stored procedures between two MSSQL 2000 servers

Posted on 2003-10-23
12
963 Views
Last Modified: 2008-02-26
I want to copy specified database to another one online Mssql 2000 server.

- Two MSSQL server are not located on same AD, so I could not use "Copy Database Wizard". (no permisson on network)

- I only use "Export data". e.g. database "ABC" export from server A to server B,  but it only be copied all tables, not include my created procedures in stored procedures


1. How to copy all by "Export data" ?
2. On the other hand, also how can I do if  stored procedure copy only ?

Thanks help.



0
Comment
Question by:rhinoceros
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 9605293
You might use the Enterprise Manager to generate the "script" to build the stored procedures, and run that script on the other server.
Hope this helps
0
 
LVL 13

Author Comment

by:rhinoceros
ID: 9605317
Thanks reply.


But I don't know how to generate the "sciprt", can tell me more, or even give me the example if possible please ?


Thanks

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 9605326
Using the enterprise manager:
-> right-click the database
 -> All Tasks -> Generate SQL Scripts
Then, a form is opened which should be "self-explaining" ...
Cheers
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 13

Author Comment

by:rhinoceros
ID: 9605349
Sorry friend,

I know it, but I mean I don't know how to use "Generate SQL Script" to do my job ?


:(
0
 
LVL 23

Expert Comment

by:adathelad
ID: 9605423
Hi,

The easiest way for you to do it is click on the Stored Procedures section in your source database and select all the stored procedures you want to copy so they are highlighted. Then just do a right click -> All Tasks -> Generate Sql Scripts. This will automatically set everything up ready, so all you need to do is either:

a) click OK in the window that pops up and save the script to a file
or
b) click "Preview..." and then copy and paste the script it displays

HTH
0
 
LVL 23

Expert Comment

by:adathelad
ID: 9605435
In fact an ever quicker way would be to select the stored procedures as before, do Ctrl-C (Copy) and then open Query Analyser on the target database and do Ctrl-V (Paste). This will paste the scripts for the selected stored procedures you copied.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9606839
adathelad,

One word of caution:  This last approach does not include the permissions, as in:

Grant Execute On ...

Anthony
0
 
LVL 13

Author Comment

by:rhinoceros
ID: 9612000
Hi  adathelad,

>> a) click OK in the window that pops up and save the script to a file

I had done to follow your instruction, but how to restore the script file into new database.

>> do Ctrl-C (Copy) and then open Query Analyser on the target database and do Ctrl-V (Paste)

This step is only for each stored procedures copied (one by none) as I understood, it's impossible for large amount stored procedures files (over 100 files for my database).


This is my first time to do it, can help again please ? thanks !

0
 
LVL 23

Accepted Solution

by:
adathelad earned 50 total points
ID: 9612607
a) Open the .SQL file in Query Analyser on the target database on the target server and then execute the script by pressing F5 (or click green "Play" arrow in top toolbar)

b) No, you can copy them all at once by selecting multiple procedures (e.g. hold Ctrl and click each one or hold down shift and select a range.
Once copied just run in Query Analyser as described in a)
HTH
0
 
LVL 13

Author Comment

by:rhinoceros
ID: 9612699
Thanks, it can be generated the stored procedure in new databae.

But the steps a & b, some error came out

a) * only one
Cannot not add rows to sysdepends for the current stored procedure because it depends on on the missing object 'Getabc'. The stored procedure will still be created

'Getabc' is one of stored procedure


b)
Cannot not add rows to sysdepends for the current stored procedure because it depends on on the missing object 'Getabc'. The stored procedure will still be created

Cannot not add rows to sysdepends for the current stored procedure because it depends on on the missing object 'dbo.dt_adduserobject_vsc'. The stored procedure will still be created

Cannot not add rows to sysdepends for the current stored procedure because it depends on on the missing object 'dbo.dt_setpropertyid'. The stored procedure will still be created

......more



For these error messages, may have something will affect the stored procedure in new database ?

0
 
LVL 23

Expert Comment

by:adathelad
ID: 9613056
The stored procedures were created OK. Those warnings you got are because not all the objects used by a stored procedure are/were in the database when the CREATE script ran.

The warnings are just to let you know that the procedures have been created, but some of the objects they use (e.g. tables, other stored procedures) do not exist (or at least didn't exist when you ran the script)
0
 
LVL 13

Author Comment

by:rhinoceros
ID: 9618576
Ok ic

Thank you very much

0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
locating an SSIS Package on SQL 2008R2 5 56
MSSQL join different row from other table 14 66
Need time in SQL 8 30
SQL Throw Error 7 33
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question