Odd problem (conversion or openquery?) only appearing in a stored procedure but not when running it adhoc

alex3_14

Here's the code I can run with no problems:

declare @AnneeSelectionnee numeric
select @AnneeSelectionnee = 2009
declare @DateDernierImport datetime
select @DateDernierImport = 'Nov 14 2008 12:00AM'

SELECT
LTRIM(RTRIM(T_ASFSTAG0.STANS1)),
LTRIM(RTRIM(T_ASFSTAG0.STHFO1)), LTRIM(RTRIM(T_ASFSTAG0.STRSP1))
FROM T_ASFSTAG0
WHERE (T_ASFSTAG0.STANS1=@AnneeSelectionnee) AND
((isdate(T_ASFSTAG0.STCRT1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STCRT1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)) or
(isdate(T_ASFSTAG0.STMAJ1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STMAJ1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)))

Please note that T_ASFSTAG0 is a view that is partly defined as follows:

FROM OPENQUERY(ISERIES_SQLDBF, 'SELECT * FROM tstadvdbf.ASFSTAG0') AS DerivedTable1

And the following is the relevant code from the stored procedure that fails:

print @AnneeSelectionnee--2009
print @DateDernierImport--Nov 14 2008 12:00AM

print '------before the select-----------'
print convert(datetime,convert(varchar,@DateDernierImport,103),103)
print '------right before the select-----------'

SELECT --line 60--
LTRIM(RTRIM(T_ASFSTAG0.STANS1)),
LTRIM(RTRIM(T_ASFSTAG0.STHFO1)), LTRIM(RTRIM(T_ASFSTAG0.STRSP1))
FROM T_ASFSTAG0
WHERE (T_ASFSTAG0.STANS1=@AnneeSelectionnee) AND
((isdate(T_ASFSTAG0.STCRT1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STCRT1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)) or
(isdate(T_ASFSTAG0.STMAJ1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STMAJ1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)))

print '------after the select and before the insert-----------'

That select statement (on "line 60") reports an error. We never see the "after the select and before the insert" print message. I thought it might have been a conversion problem but as long as an AND statement works like && does in C# (and, AFAIK, it does) then we should have no problems with the (isdate(x) AND convert(datetime(x)). This is the error I receive:

Server: Msg 7399, Level 16, State 1, Procedure P_IMPORT_STAGES_PREPA, Line 60
OLE DB provider 'MSDASQL' reported an error. One or more arguments were reported invalid by the provider.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x800700

ElectricLlama · print @AnneeSelectionnee--2009 print @DateDernierImport--Nov 14 2008 12:00AM

IMHO the only way to debug this stuff is hack it down to its simplest level and start adding columns until the error occurs. You will either find a mysterious undocumented bug or an obvious headslapping one. So maybe progressively alter your SP as follows: SELECT 'A' As C1, 'B' As C2, 'C' As C3 FROM T_ASFSTAG0 WHERE (T_ASFSTAG0.STANS1=@AnneeSelectionnee) /* AND ((isdate(T_ASFSTAG0.STCRT1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STCRT1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)) or (isdate(T_ASFSTAG0.STMAJ1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STMAJ1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103))) */ Does that work? keep adding bits in till it breaks. Also having a select * anywhere in code makes me nervous so maybe it could help things if you alter your view to select specific columns from the table.

alex3_14

I went one higher and did the following:

print '------right before the select-----------'
--2009
--Nov 14 2008 12:00AM
SELECT --line 67--
LTRIM(RTRIM(T_ASFSTAG0.STANS1)),
LTRIM(RTRIM(T_ASFSTAG0.STHFO1)), LTRIM(RTRIM(T_ASFSTAG0.STRSP1))
FROM T_ASFSTAG0
/* WHERE (T_ASFSTAG0.STANS1=@AnneeSelectionnee) AND
((isdate(T_ASFSTAG0.STCRT1)=1 and (convert(datetime,convert(varchar,T_ASFSTAG0.STCRT1))>=convert(datetime,convert(varchar,@DateDernierImport,103),103))) or
(isdate(T_ASFSTAG0.STMAJ1)=1 and (convert(datetime,convert(varchar,T_ASFSTAG0.STMAJ1))>=convert(datetime,convert(varchar,@DateDernierImport,103),103))))
*/print '------after the select and before the insert-----------'

Essentially, it's just the referencing of the view that makes it fail. By the way, the view is defined with specific fields...

SELECT STANS1, STSTA1--,... snip many others
FROM OPENQUERY(ISERIES_SQLDBF, 'SELECT * FROM tstadvdbf.ASFSTAG0') AS DerivedTable1

The error I got was:

Server: Msg 7399, Level 16, State 1, Procedure P_IMPORT_STAGES_PREPA, Line 67
OLE DB provider 'MSDASQL' reported an error. One or more arguments were reported invalid by the provider.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x80070057: One or more arguments were reported invalid by the provider.].

Alex

ElectricLlama

... I meant this star: 'SELECT * FROM tstadvdbf.ASFSTAG0' maybe if you reduce the fields it will cut out the field causing trouble. Some other ideas: 1. Compare the query plan for the working and failing case and see if there is any difference (for the linked server part of it) 2. try select * into table from yourview (I know know its a star ;P ) and see what data types you get in the new table. Maybe this will shed some light on it.