Post reply

Exporting From 2005, importing into 2000

  • December 1, 2006 at 11:36 am

    #92706

    I'm having a serious problem with a requirement I have, and need some guidence ASAP:

    I have an application that as part of the install, creates and loads a bunch of tables.  When creating the installer, this data is exported using BCP, creating both *.dat files and *.fmt format files.  This is all done on our 2005 development server.

    The problem comes when I try to run the installer on a SQL2000 instance.  I get the following error:

    SQLState = S1000, NativeError = 0

    Error = [Microsoft][ODBC SQL Server Driver]Attempt to read unknown version of BCP format file

    I've tried changing our installer generation scripts to specify the "-V 80" parameter to generate 2000-compatable format files, but it doesn't seem to have any effect at all.

    Is there ANY way I can export data using BCP from SQL Server 2005 and have it readable on SQL Server 2000??  Our application is compatible with both, and all our other scripts are compatible with both, it's just the BCP issue for bulk loading data that is seeming to be a serious problem.

    I would hate to have to convert our entire development server back to 2000 just to be able to generate an installer that works.  Surely someone has come across this issue before?

     

     

  • December 4, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • December 4, 2006 at 12:39 pm

    #675579

    Can you post BCP format file?

     

    MohammedU
    Microsoft SQL Server MVP

  • December 4, 2006 at 12:53 pm

    #675589

    I think I found the source of my confusion, and it does seem to be working now.  I was expecting the actual format of the bcp format file to change, not just the version number.  I used the "V -80" switch on all my BCP commands (generating the format files and importing and exporting the *.dat files), and everything now seems to work on both SQL2000 and SQL2005, even though all the format and data files are generated from a SQL2005 server.  The key was to include the -V 80 option on every single invokation of BCP.

    Thanks for your patience with my question.

     

  • December 4, 2006 at 1:19 pm

    #675604

    Good to know it is working

     

    MohammedU
    Microsoft SQL Server MVP

  • December 4, 2006 at 4:55 pm

    #675670

    Well, I THOUGHT it was working.  Now it's not working again.  I swear I had it working briefly.

     

    The problem is that when I do the:

    BCP database.dbo.tablename format nul  -f "tablename.fmt" -N -V 80 -S serverName -u user -p password

    command, it's outputting a file with a 9.0 at the top, instead of the 8.0, and so SQL Server 2000's BCP utility throws up its hands and stops when it sees it.  This is very frustrating.  I SWEAR I had it working this weekend...

    I absolutely need to do this.  Can ANYONE tell me what is going wrong?  I *have* to produce format files and data files from a SQL Server 2005 that is readable on SQL Server 2000.

     

     

  • December 4, 2006 at 5:15 pm

    #675682

    Open your format file and change it to 80 and try it.

     

    MohammedU
    Microsoft SQL Server MVP

  • December 4, 2006 at 5:18 pm

    #675685

    I can and will try that... but there are 760 format files, and one upgrade package is supposed to run against either SQL Server 2000 or 2005.

    It would be nice if BCP on 2005 would simply honor the -V 80 switch I'm sending it, and create a version 8.0 compatable format file! 

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

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