Im trying to create a DTS Package to copy my sql Server objects to a test Server. The server Im copying from is UMTS1 and the server Im copying to is UMTSDEV.
The database name is ProgramSpecs and exists on bother servers. My login is assigned to all server roles on both servers. I have created databases on both servers manually so Im pretty sure I have all the necessary permissions. Im using the DTS task Copy Sql Server Objects to copy sql server objects and have selected Drop Destination objects first.
When I try to execute the package I get the following error:
Error source: MS SQL DMO
Error Description: Invalid OLEVERB Structure [SQL DMO] create file error or UMTS1.ProgramSpecs.LOG
Can anyone tell me what Im doing wrong?
Thanks
GEMDTS writes the necessary script files at C:\Program Files\Microsoft SQL Server\80\Tools (for sql2k provided u havent changed installation folders). check the content of the file UMTS1.ProgramSpecs.LOG at that folder. u may get the clue.|||I looked in C:\Program Files\Microsoft SQL Server\80\Tools and was unable to find the file you mentioned. I looked in the directory on my local drive and on the server and was unable to find the file. The installation folders for SQL Server haven't been changed from the default during the installation.|||Are the specs exactly the same on both servers? Including collation?
You can find this out by right-clicking on the database in Enterprise Manager and select Properties. They should match.|||I looked in C:\Program Files\Microsoft SQL Server\80\Tools and was unable to find the file you mentioned. I looked in the directory on my local drive and on the server and was unable to find the file. The installation folders for SQL Server haven't been changed from the default during the installation.
ok, if not in the folder i mentioned, check the "Script file directory" textbox in the "copy" tab of the DTS step. you must find the file there. at times missing dependent objects causes the copy to fail.
alternatively u can use backup/restore to make a copy of a database. its easy.|||I was going to say - why are we DTS'ing this when we could backup and restore to the test environment?
That's what we do over here and it's never caused us any problems :)|||I want to use the DTS package so I can select which objects to copy. I have already converted my initial data from an MS Access database which was a real chore. I m now in the process of creating a website and stored procedures. I think if I just do a restore database, I would loose all of the stored procedures Im in the process of creating. So unless I duplicated any new store procedures I developed in the other database I would loose them when I restored. If I copied the objects using DTS I could only copy the tables and data.
Another option I have considered is to create a second database that only had views and stored procedures that referenced the original database.
Example for northwind:
Northwind (original Database)
Northwind_Web (Web Database)
Nortwind has a table called tblEmployees(I think). The database Northwind_web would have a VIEW called tblEmployees and the select statement would be Select * from Northwind.dbo.tblEmployees. All of my updates, deletions and appends would be done through the views. Then when I got ready to go live all I would have to do is script the procs from Northwind_web to Northwind and it should work because instead of updating views, the tables would automatically be updated because the names are the same.
Another option I have thought about is to simply use Update Northwind.dbo.TblEmployees instead of just update tblEmployees in my procs . Either way I would have a separate database; one for just the data and one for the views and stored procedures that updated the original database. Then I could just restore the Northwind database when I wanted to refresh the data.
My questions are: How stupid is this? What would the affect on performance be having 2 databases? If I decide to go with a restore only, can you restore only data without the stored procedures and views?
