April 8, 2008 at 7:15 am
I first posted this on the SSIS forum because my package had broken and I couldn't fix it.
I've now run the following statement in SSMS, against the synonym, which I had first deleted and recreated:
'select max(len(short_description)) from dbo.peregrine_problem'
'OLE DB provider 'SQLNCLI' for linked server 'gsc-rpt.svr.bankone.net' returned data that does not match expected data length for column '[gsc-rpt.svr.bankone.net].[Peregrine].[dbo].[scProblem].short_description'. The (maximum) expected data length is 100, while the returned data length is 102.'
I ran exactly the same SQL from vba in Excel and it works. The maximum size is 1024.
Has anyone any ideas, please?
April 8, 2008 at 8:50 am
Moving down the list of queries and beginning to panic as no answer.
If this isn't the right forum for this question, or if more info is required can someone please tell me.
I am teaching myself SQL Server as I go, with no-one else in my company for reference, so I really would appreciate some pointers
TIA
April 8, 2008 at 8:54 am
Is the column defined as 100? I'm slightly worried about corruption here. Or has the table definition changed?
I'm not sure what you mean when you ran it against Excel. Is the linked server an Excel sheet?
April 8, 2008 at 9:11 am
The width of column short_description on the linked server has been changed from 100 to 1024. I have a package which extracts data from the linked server and stores it in my database. The package failed when the column size was changed, and I originally thought it would be enough to change the size of my output table.
Whern this didn't work I tried running the above SQL direct against the linked server, which gave me the same error as the package. I've tried delinking and relinking the server to no effect.
Using an ADO connection in Excel VBA, I pass the same query to the same server, and get a valid result. The maximum column width is returned as 1024. We also have an Access database that links to it, and once we had amended the size of tables in our Access database, all queries ran successfully against the server, with longer descriptions being returned.
The linked server is also a SQL Server database, but I have no control over the set-up.
Hope this helps
April 8, 2008 at 9:50 am
What I don't understand about this is why there is an expected size of 100.
I have deleted all reference to the linked server, so when I reinstate the linkage, surely it must get the new size information from the linked server? I can prove that the table on the linked server has a size of 1024, and Access and Excel can both deal with the larger column size when I extract data using these tools.
This makes me think the problem is with SQLNCLI. Is there some limitation on sizes of certain data types perhaps?
Does anyone know of an expert contact with SQLNCLI?
April 8, 2008 at 10:45 am
born2 -
double check that SSIS isn't making any "stupid" guesses on the datasource properties (which also has column data widths set up). I find that it makes a lot of errors there, and that the truncation-type error message more often than not come from it, and not the destination object.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 8, 2008 at 11:38 am
This isn't an SSIS problem, which is why I moved off the SSIS board.
I am working in SQL Server Management Services and writing an ad-hoc query. It's just a piece of test sql to target the problem column. SELECT max(len(short_description)) from dbo.peregrine_Problem.
the source is a view on a linked server and I have already tried deleting the linked server from within SQL Server, and then reinstating it, in the hope it will pick up the new column width, but that hasn't helped.
Embed the same query into Excel or Access vba and it returns 1024, without any error messages. As a result, I am convinced it is something specific to the way SQLNCLI works.
I'm a SQL Server newbie and everything I do is self taught, but I'm really beginning to dislike it. Everything is too high-level and I can't see what is happening behind the scenes. If I could do this in Access I would, but unfortunately the scale of the data is too great.
April 9, 2008 at 7:23 am
Well, the first place I would start is with checking my work. You said that you changed the max length from 100, to 1024. Just to ensure that it did in fact store the change, in SSMS I would browse to the table, and then to the column you are referencing, right click and go to properties. Verify that the length is correct.
Also, the last post you said you are calling on this information from a view. Can you try to retrieve this directly from the source column? I do a lot of work with MS CRM and I know that when I retrieve data through CRM's filtered views the data does sometimes seem to act different. Hope this helps a little.
April 9, 2008 at 8:04 am
I found the probable cause of the problem on the Administering SQL Server forum.
It seems I must get the owners of the remote server to recompile their view, at the least, or possibly install SQL Server 2000 SP3/SP4.
I followed the guidance on this link:
http://www.sqlservercentral.com/Forums/Topic473552-146-1.aspx
Using OPENQUERY allowed me to write something that worked, but returned the truncated column, so it's not the total answer. When I incorporate this theory into my SSIS package, and use the more complex filtering my business requirement demands, it slows the package down. It normally runs in 8 minutes, but I am running it now and it has been going for 90 minutes and is 10% through.
I am hoping I can exert pressure on the remote database owners to resolve this for me, but failing that I am thinking of returning to Access or Excel to extract a flat file that I can read into my database, as they can handle the changes and return the full length of the field
April 9, 2008 at 8:58 am
Thanks for the update. I'd forgotten about views not showing column changes .
April 9, 2008 at 9:50 am
Well that answers some questions I have had also. I was wondering why the CRM FilteredViews seemed so unresponsive. Is there anything else that is not reflected in a view??
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply