Stored Procedures dont come across in DTS

Question

Post reply

Stored Procedures dont come across in DTS

Frank Rueff

SSC Enthusiast

Points: 155
More actions
August 16, 2004 at 12:15 pm

#360682

I am moving the database from a 7.0 to 2000 sql server. Everything comes across except SPs. I cannot restore it because of diskspace issues, according to other posts DTS is my only option. I did go into the package to just bring the SPs across but they do not even show up. I have tried it from both machines. Any ideas?

Thanks

Frank

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

~nano Right there with Babe Points: 794 More actions · Answer 1

Hi Frank

If you are using Enterprise Manager, then you can generate the Creation Scripts for the Stored Procedures. This script can then be carried out on the new server and if the structure is the same, the sp's should then be there.

This is how we do it. There might be a better way ... Anyone???

greetings from Germany

~nano

Lowell SSC Guru Points: 323482 More actions · Answer 2

If you are using the IMPORT or EXPORT functionality from Enterprise Manager,it might be because you are using the default options:

If you used Right click>>All Tasks>>Import Data (or Export data)

Then you chose your Data source and Destination...the next selection is three option buttons:

the first says "Copy table and Views"(selected by default)

the second says "Use a query to specify data to transfer"

the last says copy objects and data between SQL Server databases.

As I remember, if you selected the first choice, only tables and views get copied...I don't believe foreign keys, indexes, procedures functions or constraints (other than primary key on a table) gets copied...you might be missing more than just procedures.

I always use the third option to copy data between servers:

i recommend unchecking the last checkbox on the next screen to uncheck "use default options", and then click the options button and unselect the top three checkboxes that refer to specific users;

Hope that helps.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Scott Coleman One Orange Chip Points: 27451 More actions · Answer 3

Scott Coleman

One Orange Chip

Points: 27451

August 17, 2004 at 7:30 am

#519332

You say they don't show up, but you don't say you're getting any errors. Are you sure you aren't copying them to the wrong database?

The previous two posts are excellent suggestions if you can't get DTS to do what you want.

Frank Rueff SSC Enthusiast Points: 155 More actions · Answer 4

Thanks for the replys. I have done the export from the sql 2000 and import from the sql 7 server with the same results. Everything else does come across into the correct database. That is the strange part, it does not throw any errors. I have tried to just export the SPs but they don't even show up as an option to export. This is a vendor database(they are no help.) Is there a way to script what the SPs are in the current database? The problem is that it is going from a 7 to 2000 DB.

Thanks

Scott Coleman One Orange Chip Points: 27451 More actions · Answer 5

The easiest way was in the first reply. In Enterprise Manager, right-click on a stored procedure and select All Tasks - Generate SQL Script. Click the Show All button and the All Stored Procedures checkbox.

The other tabs have various options, but pay close attention to the Security options on the last tab. You probably want to uncheck all of the security options, with the possible exception of "Script object-level permissions".

When you click OK it will script all the stored procedures to a file. Connect to the new server in Query Analyzer and load the file. Check it over to make sure it will do what you want before executing it.

SQL Draggon Right there with Babe Points: 716 More actions · Answer 6

Are the stored procedures encrypted ?? they won't come across and you can't script them >> you can only restore from a backup. I have had ~50% success in using DTS to copy stored procedures. the best answer with 100% success is the first answer >> script the stored procedure then run on the destination.

good luck..

Don't count what you do, do what counts.

SQL Draggon