Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

OPENQUERY Expand / Collapse
Author
Message
Posted Friday, May 1, 2009 4:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 10, 2009 4:12 PM
Points: 3, Visits: 6
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!
Post #708819
Posted Saturday, May 2, 2009 4:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #708893
Posted Saturday, May 2, 2009 1:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 36,714, Visits: 31,164
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #708963
Posted Friday, May 8, 2009 2:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 10, 2009 4:12 PM
Points: 3, Visits: 6
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
FROM OPENQUERY(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 */
FROM BDE.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 (

SELECT A.*
FROM BDE.ARM_PAYMENT_V A
INNER JOIN (SELECT LOAN_NUMBER, MAX(ARM_PR_EFFECTIVE_DATE) AS MAX_ARMPED
FROM BDE.ARM_PAYMENT_V
WHERE ARM_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 */
WHERE L.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
SET WHOLE_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
SET FinDelinquencyReason = 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,
CASE WHEN 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.
Post #713364
Posted Friday, May 8, 2009 3:24 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #713388
Posted Sunday, May 10, 2009 4:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 10, 2009 4:12 PM
Points: 3, Visits: 6
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.
Post #713793
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse