Post reply

Importing Data

  • May 15, 2007 at 10:41 am

    #118754

    In SQL 2000 I used to update tables by doing this in Query Analyzer: TRUNCATE TABLE . Then Execute. Then I would use utilities/sqldev/import to import the table into the destination DB. How is this done in SQL 2005? Basically, I want to update the table. Do I really need to TRUNCATE in SQL 2005? Online Books is a bit foggy as for method using the Wizard. Thanks in advance.

  • May 15, 2007 at 2:46 pm

    #706612

    It's pretty much the same.  The Import/Export Wizard still gives you the option to delete rows in the destination table before importing.  Just like in SQL 2000, you decide if you want to do a separate TRUNCATE of the destination table or if you want let the Wizard delete from the table.  The difference is, if you decide to TRUNCATE, you do it in a query window in SQL Server Management Studio rather than in Query Analyzer.

    Is that what you want to know?

    Greg

    Greg

  • May 15, 2007 at 3:36 pm

    #706617

    Sounds like it should work. I do as you say but I get this: Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21.

    An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

    (SQL Server Import and Export Wizard)

  • May 15, 2007 at 4:26 pm

    #706623

    Is the source on a remote SQL Server?  If it is, then you have to use the SQL OLE DB Provider rather than SQL Native Client.

    Greg

    Greg

  • May 15, 2007 at 4:28 pm

    #706624

    The source is an Access file on the SQL server. I should be importing the data into the SQL Native, yes?

  • May 15, 2007 at 4:47 pm

    #706627

    Check this thread from last October.  It concerned an import from Excel, but the same datatype mismatch could happen with Access.

    http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=148&messageid=315637

    Greg

    Greg

  • May 16, 2007 at 5:48 pm

    #706967

    Thanks, but that one was not it. I found a web page describing the exact problem: "Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

    Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify that the column metadata is valid.

    Error 0xc004701a: Data Flow Task: component "Destination - Query" (40) failed the pre-execute phase and returned error code 0xC0202025.

    Well, as usual, an error message that is not very helpful. However, after poking around in Google, I came upon some entries that talked about using the new max keyword for array lengths. I decided to try it out; I looked at the autogenerated SQL for the import column mapping, and everywhere I saw nvarchar(1) I put in nvarchar(max), and sure enough it worked.

    So, if you ever run into this obscure error when importing into SQL Server 2005, see if any of the column mappings use nvarchar(1) in the SQL. If so, try using nvarchar(max) instead."

    How does one change the nvarchar to max?

  • May 17, 2007 at 3:56 pm

    #707267

    Use ALTER TABLE ALTER COLUMN to modify the column's datatype.

    Greg

    Greg

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

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