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

20 pts.
Tags:
Linked Server
OPNQRY
SQL 2000
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • LeftForDead
    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)?
    20 pointsBadges:
    report
  • Meandyou
    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.
    5,220 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following