SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


OPENQUERY


OPENQUERY

Author
Message
rjern
rjern
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 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!
Florian Reischl
Florian Reischl
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9193 Visits: 3934
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222488 Visits: 42003
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
rjern
rjern
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 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.
Florian Reischl
Florian Reischl
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9193 Visits: 3934
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
rjern
rjern
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search