Odd problem (conversion or openquery?) only appearing in a stored procedure but not when running it adhoc
Home > IT Answers > SQL Server > Odd problem (conversion or openquery?) only a...
LeftForDead
20 pts.
0
Q:
Odd problem (conversion or openquery?) only appearing in a stored procedure but not when running it adhoc
SQL 2000, Linked Server, OPNQRY
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 0x80070057:  One or more arguments were reported invalid by the provider.].


Any help or hints is greatly appreciated.
ASKED: Sep 18 2009  8:48 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
LeftForDead
20 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
Last Answered: Sep 18 2009  8:48 AM GMT by LeftForDead   20 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

LeftForDead   20 pts.  |   Sep 18 2009  11:28AM GMT

I modified the stored procedure further, to try and isolate the problem, and I began with the simplest possibility - expecting it to succeed - but it failed at line 67…

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———–’

So it seems the view (in production for X number of years) or, most likely, the openquery is the culprit.

The view’s code:

SELECT     STANS1, STSTA1–,… snip many others
FROM         OPENQUERY(ISERIES_SQLDBF, ‘SELECT * FROM tstadvdbf.ASFSTAG0′) AS DerivedTable1

Maybe it’s a question of resources - the only real difference between my VM test environment and the production server. Could it be that my VM can’t handle the resources this SELECT demands since it’s connecting, real time, to a linked server (AS400)?

 

Meandyou   1840 pts.  |   Sep 18 2009  3:25PM GMT

When I first your the question, my first thought was a missing terminator following the failing SQL statement, something between the SELECT and the PRINT statements. That is my only thought on this subject.

 
0