Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2007
    Location
    Weatherford TX (Between Fort Worth and Abilene)
    Posts
    57

    Unanswered: DTS - Copy Sql Server Objects help

    I’m trying to create a DTS Package to copy my sql Server objects to a test Server. The server I’m copying from is UMTS1 and the server I’m 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 I’m pretty sure I have all the necessary permissions. I’m 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 I’m doing wrong?

    Thanks
    GEM

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    DTS 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.

  3. #3
    Join Date
    Apr 2007
    Location
    Weatherford TX (Between Fort Worth and Abilene)
    Posts
    57
    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.

  4. #4
    Join Date
    Mar 2004
    Posts
    190
    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.
    "An adventure is only an inconvenience rightly understood; an inconvenience is only an adventure wrongly considered." ~G.K. Chesterton

  5. #5
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    Quote Originally Posted by gem1204
    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.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2007
    Location
    Weatherford TX (Between Fort Worth and Abilene)
    Posts
    57
    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 I’m 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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •