Post reply

Impossible - Query produces error on different server with same data and database

  • June 27, 2011 at 9:51 pm

    #148245

    Hi guys,

    This is a tough one...I'm going on 14 hours straight trying to solve it.

    With SQL Server 2005 Standard on a test server an Qpenquery insert using a Linked Server (OLEDB Access 4.0 driver) works just fine.

    I COPY the SQL database to the other server and attach it.

    I COPY the source data (CSV Files) to the other server.

    All Server and Database options match.

    I run the query on the other server and get "String or Binary Data Would be Truncated".

    So...

    Same Edition of SQL Server.

    Same Database (exactly).

    Same source data (exactly).

    Same Linked Server Options.

    I don't see how this could possibly be happening.

    Can anyone imagine what might be the problem?

    OK---1 difference. The test server is running on Windows 7.

    The production server is running on Windows Server 2003.

    Both 32-bit. I plan on testing on a Windows 2003 test server tomorrow.

    Help!

  • June 27, 2011 at 11:48 pm

    #1344958

    Hi John,

    Is the ANSI_WARNINGS option OFF at the DB where you are not getting any error.

    Can you try using Danny's Method at both the servers, to check if it is the data which is causing the error?

    http://sqlblogcasts.com/blogs/danny/archive/2008/01/12/scuffling-with-string-or-binary-data-would-be-truncated.aspx

    Regards - Yasub

  • June 28, 2011 at 6:35 am

    #1345125

    Thanks for your reply yasub,

    Yes, ANSI_WARNINGS are set to False on both SQL Server databases.

    I am running the SQL import from the Access linked server via VB.NET CLR. Therefore Danny's method won't work for me. However, I did try sending the command "SET ANSI_WARNINGS OFF" prior to running the import to see if that would help. I was then greeted by another warning "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection".

    Apparently when querying from a different data source format, for example using the OLEDB Access Driver or from Oracle, etc., SQL Server requires those settings. In fact it automatically sets ANSI_WARNINGS on when those types of queries are run and won't let you over-ride the settings.

    I have spent 3 weeks re-writing a very large data import program that was running like clockwork in testing on a couple different servers...but now chokes on this particular server. Part of the problem comes from the fact that the newer versions of DTS fail to properly handle a very valid CSV format that is handled properly by MS Access and Excel and SQL Server 2000 DTS.

    Anyway, I have downloaded a copy of the Database from the offending production server and will now attach it to SQL Server 2005 running on Windows Server 2003 at "the shop" to more closely approximate the exact situation. We shall see if I get the same error.

  • June 29, 2011 at 6:41 am

    #1345869

    If you open the CSV file with Excel, does it look normal? If it was saved with Unicode encoding instead of ASCII, all the data will appear in the first column. That might explain the different behavior.

  • June 29, 2011 at 8:13 am

    #1345950

    Thanks for the input William.

    The data in the CSV files looks Ok in Excel. However that was a great idea I had not considered. All the data being stuffed into the first column would surely cause that error.

    So here is the latest update.

    I tried a variation of Danny's method, but slugging through it manually by testing each of the 60 columns in the offending input CSV looking for Max(len([column-name])). There are 10 input CSV's but this largest one seemed to be causing the trouble.

    I modified the Insert query that I use to load the staging table...

    INSERT dbo.Stage_Main

    SELECT * FROM OPENQUERY(PC_IMPORT_NM, 'SELECT * FROM [Main#csv]')

    And just used part of it...

    SELECT MAX(LEN(column-name)) FROM OPENQUERY(PC_IMPORT_NM, 'SELECT * FROM [Main#csv]')

    ... to inspect the fields one at a time.

    Because SQL Server 2005 does not allow "SET ANSI_WARNINGS OFF" for a Heterogeneous query, I could not use Danny's method.

    I indeed found 2 columns where the Length exceeded the declared size of the destination table's corresponding field. I upped the size of those columns in the staging table and just this morning successfully ran the imports on the production server.

    It still puzzles me as to why SQL Server 2005 allowed these columns to be truncated in testing but not on the production server. I know what the data represents in those columns and also know that truncation would have been acceptable...as I also wrote the ASP.NET application that uses the data.

    However, Microsoft has decided to protect us from ourselves and taken those decisions out of our hands. I am now wondering if in SQL Server 2000 I would be allowed the truncation if I so chose. That is essentially the reason I could not use a DTSX data flow solution in SQL server 2005...because Microsoft is intentionally protecting us from mistakes. If I only had to support SQL Server 2000 I would have used DTS...which handles the CSV's with no problem. Isn't that a hoot?

    Sometimes in the "real" world we are forced to use the data that is handed to us...like it or not. It can't always be pre-cleansed by a Database Administrator.

    I hope this post will help someone else.

    Thanks for the responses guys.

Viewing 5 posts - 1 through 4 (of 4 total)

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