Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2006
    Posts
    53

    Unanswered: Problem copying table

    We upgraded to SQL Server 2005 & I'm having trouble with the Import and Export Wizard in the Management Studio. We periodically need to export some data to a different database to save it while we update the 'real' database which basically starts it over with empty tables. Under SQL Server 2000 that wasn't a problem. Under 2005 it is.

    The database comes from a vendor and nearly every table in it has a timestamp column & when I try to import/export the rows I get a Validation Error: Error 0xc0202048: Data Flow Task: Attempting insertion into the row version column "timestamp". Cannot insert into a row version column.

    Now I can write a query that omits the timestamp column & the import/export works perfectly-but a couple of the tables have over a hundred fields! (Oh, what I'd give for an 'except' analog to the * selection.)

    Any suggestion how to get around this? Thanks.
    Now back on Oracle. The more things change, the more they stay the same.

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    You cannot insert specific values to a column when the datatype is timestamp. Depending on the source of your data, you most likely want to change the datatype of the destination column to either binary(8), varbinary(8) or datetime. More information about timestamp columns in SQL Server is available in BOL.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Mar 2006
    Posts
    53
    You know, I thought my question was clear. I know what the problem is, what I don't know is how to solve it-and since the app comes from a vendor changing it myself isn't an option. (Is it really an option for you, roac, or do you just not work with applications you don't develop yourself?)

    FWIW I submitted a request to the vendor to remove/change the timestamp column-no response, yet, which is why I'm looking for a solution that I can implement.

    So far it looks like I'm stuck-either wait for the vendor to make the change or start writing queries. I figure at least two solid days' work to write the queries, and then there's testing & keeping them updated.
    Now back on Oracle. The more things change, the more they stay the same.

  4. #4
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Ah, I see now, I obviously overlooked a few details

    Hopefully you have a test-environment where you can try the following procedure:

    1. Backup the database
    2. Restore on alternate location
    3. Use ApexSQL Diff to script changes to structure
    4. Restore database where backup was taken
    5. Run script to apply changes in structure.

    As far as I can see this approach should work. If the database is not deleted and recreated during upgrade, and you are using Enterprise Edition, you could use a Database Snapshot instead of backups.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Wow, Calvin. That was a pretty rude response to someone who honestly was trying to help you.
    I have a script that might assist you in writing queries for these lengthy tables, but I hesitate to post it because it may not be exactly what you want.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Mar 2006
    Posts
    53
    roac, I'll take a look at ApexSQL. Right off I don't see how it will help as your description sounds like a way to change the timestamp column and what I need is a way to backup/restore all the data except the timestamp column. But maybe ApexSQL will let me do that-I'll take a look & thanks.

    Blindman, I'd be happy to take a look at your script. Thanks.
    Now back on Oracle. The more things change, the more they stay the same.

  7. #7
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    ApexSQL diff is a tool for scripting the code neccessary to change one version of a data structure to another. So, you can create a script that change the data structure of the data that you already have to the version created by the application's upgrade.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Attached is a script for creating formatted lists of column names for easy cut-n-paste into code. Make sure when you run it that you have QA set to output results in text mode.
    Attached Files Attached Files
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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