OPENQUERY

  • I am new to coding in the TSQL environemnt at least in a complex manner. I have more of main frame, midrange background and would appreciate any insight you can shed on my problem described below.

    I have a very complex and large SQL program that has one OPENQUERY select statement using a linked server. The problem I am expericing is that I have appearently hit a limit on the number of characters that one can have in a single Select statement or I hit a limit of the number of files you can loop against to get MAX date in that single select or a combo of both.

    I have 3 more fields and 3 more tables that require 2 select coding loops to pull the most current data by max date. When I try to add them within the OPENQUERY statement I get a syntax error when validating the code and it is bogus as there is not any syntax error.

    Can one use multiple OPENQUERY statements in a single TSQL program and if not what is a good work arournd?

    Thanks in advance for your time and input!

  • Hi

    What kind of remote server is connected?

    rjern (5/1/2009)


    I have a very complex and large SQL program that has one OPENQUERY select statement using a linked server. The problem I am expericing is that I have appearently hit a limit on the number of characters that one can have in a single Select statement

    Could you explain more detailed what you are doing in this statement? Example? DDL?

    or I hit a limit of the number of files you can loop against

    What do you mean with "files"?

    I have 3 more fields and 3 more tables that require 2 select coding loops to pull the most current data by max date. When I try to add them within the OPENQUERY statement I get a syntax error when validating the code and it is bogus as there is not any syntax error.

    A syntax error usually specifies an error in your statement, not a to huge statement.

    Can one use multiple OPENQUERY statements in a single TSQL program and if not what is a good work arournd?

    What do you mean with "TSQL program"? Sure, you can use more than one statements using OPENQUERY in an application. You can also use more than one OPENQUERY in one SQL statement.

    Maybe have a look to the link in my signature to get better help.

    Greets

    Flo

  • For the most part, if you have defined a linked server, there is usually no need at all for OPENQUERY or anything like it. Just use a 4 part naming convention when referring to the tables in the linked server.

    The other thing is that you used the word "loop" a lot... generally speaking that's a form of death by SQL and should be avoided.

    Without seeing the code, there's not much else I can offer.... but if you've made a query so big that SQL Server hit the character limit on it, then you've made a query with hundreds of thousand of characters... don't post it here, please. I suspect it's something else wrong, though.

    Last, but not least, it would be handy if you posted the actual error messages. We're just guessing here.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hello, Sorry this has taken a while I debated on posting again as this is long, but here goes.

    On mainframe and midrange platforms that I have previously worked on Files is what you call Tables.

    T_SQL is the form of SQL I am using via Microsoft SQL Server Management Studio.

    The Server this will run is a SQL SERVER

    Below is a much more detail description of the SQL program and the issue. Thanks!

    /* Obtain data and populate the initial file (#INITIAL_FASLO) */

    IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE NAME LIKE '#INITIAL_FASLO%')

    DROP TABLE #INITIAL_FASLO

    SELECT*

    INTO#INITIAL_FASLO

    FROMOPENQUERY(AURFBD, '

    SELECT

    L.LOAN_NUMBER AS PURCHASE_ORDER, L.LOAN_NUMBER, L.INVESTOR_ID,

    CAST(NULL AS VARCHAR(25)) AS WHOLE_LOAN_STATUS_HOLDER,

    CAST(NULL AS VARCHAR(20)) AS FinDelinquencyReason,

    L.ZONE, LM.LM_TEMPLATE_ID, LM.LOSS_MIT_SET_UP_DATE, LM.LOSS_MIT_STATUS_CODE,

    /* 45+ more lines of various fields continue unitll the FROM statement */

    FROMBDE.LOAN_V L

    INNER JOIN BDE.LOSS_MITIGATION_V LM ON (L.LOAN_NUMBER = LM.LOAN_NUMBER)

    INNER JOIN BDE.BORROWER_V B ON (L.LOAN_NUMBER = B.LOAN_NUMBER)

    INNER JOIN BDE.YEAR_END_V YE ON (L.LOAN_NUMBER = YE.LOAN_NUMBER)

    /* Continue 12 more inner joins related to the FROM statement, then begin left outer joins with select statements to pull max dates*/

    LEFT OUTER JOIN (

    SELECTA.*

    FROM BDE.ARM_PAYMENT_V A

    INNER JOIN(SELECTLOAN_NUMBER, MAX(ARM_PR_EFFECTIVE_DATE) AS MAX_ARMPED

    FROMBDE.ARM_PAYMENT_V

    WHEREARM_PR_EFFECTIVE_DATE < CURRENT DATE

    GROUP BY LOAN_NUMBER) AP_MAXDT

    ON (A.LOAN_NUMBER = AP_MAXDT.LOAN_NUMBER AND A.ARM_PR_EFFECTIVE_DATE = AP_MAXDT.MAX_ARMPED)

    ) AP ON (L.LOAN_NUMBER = AP.LOAN_NUMBER)

    /* The WHERE clause of the MAIN Select statement all of the above is within */

    WHEREL.LOAN_NUMBER ''0999999998''

    AND LM.LM_TEMPLATE_ID = ''FAMOD''

    AND L.ZONE NOT IN (''DD'', ''DU'', ''DH'', ''DM'', ''DL'')

    AND P.PROPERTY_TYPE_FNMA_CODE IN (''1'',''2'',''3'',''5'',''6'',''7'')

    AND P.OCCUPANCY_CODE = ''1''

    AND P.OCCUPANCY_CURRENT_STATUS_CODE IN (''0'', ''1'', ''2'', ''7'', ''9'')

    AND L.LO_TYPE IN (''3'',''6'')

    AND (U.USER_03_POSITION_FIELD_4B IS NULL OR U.USER_03_POSITION_FIELD_4B ''Y'')

    FOR FETCH ONLY WITH UR ')

    /* Now updates are made to the initial data file #INITIAL_FASLO */

    UPDATE#INITIAL_FASLO

    SETWHOLE_LOAN_STATUS_HOLDER = UPPER(cPortfolioCode)

    FROM#INITIAL_FASLO I

    INNER JOIN LTS.dbo.LoanMaster LM ON (I.LOAN_NUMBER = LM.cLoanNo)

    LEFT OUTER JOIN LTS.dbo.Investor_Groupings IG ON (LM.cInvestorID = IG.cInvestorID AND LM.cCatCode = IG.cCatCode)

    UPDATE#INITIAL_FASLO

    SETFinDelinquencyReason = ISNULL(FinancialDelinquencyReason,' ')

    FROM#INITIAL_FASLO I

    INNER JOIN servicing.dbo.tblSSFinancial SN ON (I.LOAN_NUMBER = SN.LoanNumber)

    */ Now edit the final data ouput and in

    IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE NAME LIKE '#FASLO_FINAL%')

    DROP TABLE #FASLO_FINAL

    SELECT ISNULL(PURCHASE_ORDER, ' ') AS Purchase_Order,

    ISNULL(LOAN_NUMBER, ' ') AS cLoanNo,

    ISNULL( INVESTOR_ID, ' ') AS cInvestorID,

    CASEWHEN WHOLE_LOAN_STATUS_HOLDER = 'WL' THEN '''WHOLE'''

    ELSE ' '

    END AS [Whole Loan Status],

    /* continue editing data to be written to the Final table # FASL_FINAL another 50 lines */

    ISNULL(MORTGAGOR_FIRST_NAME, ' ') as cMortgFirstName

    INTO#FASLO_FINAL

    FROM#INITIAL_FASLO I

    INNER JOIN LTS.dbo.LoanMaster LM ON (I.LOAN_NUMBER = LM.cLoanNo)

    LEFT OUTER JOIN SBAURSQL001.DokTrakSQL.dbo.dRecData DT ON (I.LOAN_NUMBER = DT.loanno)

    /* ------------------------------------------------------------------------------------------------------------------------------------*/

    /* Update product line to equal NULL if not INT, OPT OR LOC */

    /* ------------------------------------------------------------------------------------------------------------------------------------*/

    UPDATE #FASLO_FINAL

    SET cProductLineCode = ' '

    WHERE cProductLineCode NOT IN ('INT', 'OPT', 'LOC')

    /* ------------------------------------------------------------------------------------------------------------------------------------*/

    /* Check for NULLS in the files DATE fields and repllace with appropriate output (space or 0) */

    /* ------------------------------------------------------------------------------------------------------------------------------------*/

    UPDATE #FASLO_FINAL

    SET LossMitSetUpDate = ' ' Where LossMitSetUpDate IS NULL;

    UPDATE #FASLO_FINAL

    SET dtBankruptcyDate = ' ' Where dtBankruptcyDate IS NULL;

    /* Continue another 40 or so lines of Updates to set varous fields to blank from NULL as required by custome */

    /* At end of the script, select all rows to write to grid */

    SELECT * FROM#FASLO_FINAL

    I have abbreviated as much as possible without lossing the gist of the SQL coding. When I try to add enve 1 more fields on the main select statement I get this error:

    Msg 170, Level 15, State 1, Line 43

    Line 43: Incorrect syntax near '

    SELECT

    L.LOAN_NUMBER AS PURCHASE_ORDER, L.LOAN_NUMBER, L.INVESTOR_ID,

    CAST(NULL AS VARCHAR(25)) AS WHOLE_LOAN_'.

    I cannot understand what is causing this error. It matters not where I try to add a field in the select statement I alwaays get this error. The field I am trying to add is CC.COMMENT_ADDED_DATE. I cannot add the the Join for the table this field comes from to the INNER JOINS statemetns as I have tried to add this innter join by itself and get the same error of ‘Incorrect Syntax ner ‘ …

    I hope I have not posted to much information but I was not sure how to skinny things down much further. Thank you in advace for your time and trouble.

  • Hi

    I did not analyze the complete statement since now. BOL says the query is restricted to 8k.

    Maybe stupid question: What if you just pot the code into a procedure on your remote server and call this procedure instead of this huge dynamic SQL statement?

    Greets

    Flo

  • Hi Flo, I am not able to directly acces the remote server and have been instructed to write the program in a large select statement intially by my boss, it is the fastest way to process on this box from what I understand.

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

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