Help with structure of openquery statement 2

[ISPrincess]

Following is my exact statement in stored proc.
Syntax checks ok but get errors on execute in query analyser:

It must be my use of quotation marks etc. Please help

STORED PROC:


CREATE PROCEDURE dbo.rpt_Link_Test

@sScheduledDate varchar(8),
@sPrestageShift varchar(2),
@sLineName varchar(2),
@sToFac varchar(1)

AS

DECLARE @sSQL nVarchar(4000)
DECLARE @sFac Varchar(3)

Set @sFac = 'NAP'

Set @sSQL = 'Select * from openquery(Server1, ''SELECT DISTINCT '' + @sFac + '' as Facility,
UCM.MoveNo,
UCM.WMSMoveType,
LPH.LicensePlateNo,
UCM.FormattedFromLocation,
UCM.FormattedToLocation,
UCM.FromToFacility,
UCM.ScheduledDate,
UCM.PrestageShift,
UCM.LineName,
UCM.MoveQuantity,
UCM.Code + ' + '-' + ' + UCM.Choice + ' + '-' + ' + UCM.Alternate as SKU
From Server1.FacilityA.dbo.LPHeaderHist LPH, Server1.FacilityA.dbo.LPDetailHist LPD, Server1.FacilityA.dbo.UCMHist UCM
Where LPH.LicensePlateNo = LPD.LicensePlateNo
and LPD.MoveNo = UCM.MoveNo and LPD.WMSMoveType = UCM.WMSMoveType
and UCM.MoveType = ' + '06' +
'and UCM.Scheduleddate = ' + @sScheduledDate +
'and UCM.Prestageshift = ' + @sPrestageShift +
'and UCM.LineName = ' + @sLineName +
'and UCM.FromToFacility = ' + 'N' +@sToFac +
'and LPH.LicensePlateNo Not In
(select LPH2.LicensePlateNo
from Server1.FacilityA.dbo.LPHeaderHist LPH2
Where LPH2.LicensePlateNo = LPH.LicensePlateNo
and (Right(RTrim(LPH2.CurrentLocation),2) = '+ '99' + '
or LPH2.TransStatus = ' + '5105' + ')'''')'


EXEC sp_executesql @sSQL



ERROR:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '+'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ' as Facility,
UCM.MoveNo,
UCM.WMSMoveType,
LPH.LicensePlateNo,
UCM.FormattedFromLocation,
UCM.FormattedToLocation,
UCM.FromToFacility,
UCM.ScheduledDate,
UCM.PrestageShift,
UCM.LineName,
UCM.MoveQuantity,
UCM.Code + - + UCM.Choice + - + UCM.Alternate as SKU
From Server1.FacilityA.dbo.LPHeaderHist LPH, Server1.FacilityA.d...

 

[ISPrincess]

Sorry -

Possibly that was too much for anyone to look at.

Here is a simpler query example that would benefit myself greatly if someone could take a look at it and figure out my quote (single / double whatever) problem.

Thank you ahead of time

CREATE PROCEDURE dbo.rpt_Link_Test

@sDate varchar(8),

AS

Set @sSQL = 'Select * from openquery(Server1, ''SELECT TOP 1 FieldA 
FROM Table 
Where FieldB = 'XX''
AND FieldDate = @sDate '')'

 

[tlbroadbent]

I have a few uncertainties because I don't know all the
data types. However, the following should help you get closer to what you need.

1) The remote query in the OpenQuery call must be enclosed in 2 single quotes.

2) All strings in the remote query must be enclosed in 4 single quotes. However, you also set quoted_identifier off and use 1 double quote. This is much simpler and easier to read.

Here is an example using the double quotes.

Set quoted_identifier off

Select @sSQL =
'Select * from openquery(Server1, ''Set quoted_identifier off;' +
' SELECT DISTINCT "' + @sFac + '"' +
' as Facility, UCM.MoveNo, UCM.WMSMoveType, LPH.LicensePlateNo,' +
' UCM.FormattedFromLocation, UCM.FormattedToLocation, UCM.FromToFacility,' +
' UCM.ScheduledDate, UCM.PrestageShift, UCM.LineName,' +
' UCM.MoveQuantity, UCM.Code + "-" + UCM.Choice + "-" + UCM.Alternate as SKU ' +
' From Server1.FacilityA.dbo.LPHeaderHist LPH,' +
' Server1.FacilityA.dbo.LPDetailHist LPD,' +
' Server1.FacilityA.dbo.UCMHist UCM' +
' Where LPH.LicensePlateNo = LPD.LicensePlateNo' +
' and LPD.MoveNo = UCM.MoveNo and LPD.WMSMoveType = UCM.WMSMoveType' +
' and UCM.MoveType = "06"' +
' and UCM.Scheduleddate = "' + @sScheduledDate + '"' +
' and UCM.Prestageshift = ' + @sPrestageShift +
' and UCM.LineName = ' + @sLineName +
' and UCM.FromToFacility = "N' + @sToFac + '"' +
' and LPH.LicensePlateNo Not In
(select LPH2.LicensePlateNo
from Server1.FacilityA.dbo.LPHeaderHist LPH2
Where LPH2.LicensePlateNo = LPH.LicensePlateNo
and (Right(RTrim(LPH2.CurrentLocation),2) = "99"
or LPH2.TransStatus = "5105&quot'')'

To see the query using 4 single quotes just change all double quotes to 4 single quotes. These are not the only ways to perform this type of query. You may discover something that works better for you. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[SQLSister]

It's so easy to get lost in quotation mark hell on one of these complicated statements. You should use the debug feature of query analyzer when working on a complex sql statement like this when it is populated in a variable.
Set a breakpoint on the exec statement and then check the value of the variable and you will usually see where the quotes are not correct.

 

[ISPrincess]

Terry - again thanks for all your input - these are things I did not know.

SQLSister - Uhhh - query analyser has a debug feature? Woah. Thank you very much - I feel pretty dumb but I will try and find more about this.

 

[ISPrincess]

Terry -
I copied and pasted your example into my test s/p and changed server name etc but still got the following error.

This is really plaqueing me. BTW - This is SQL 7.0

Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 170, Level 15, State 1, Line 1
Line 6: Incorrect syntax near ')'.

 

[tlbroadbent]

I can only suggest that you check the syntax. I don't know of any reason this won't work in SQL 7.

Is @sLineName character or numeric data? If character, you'll need to add quotes around it. The same is true of any other variables that are character and are not enclosed in quotes. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[ISPrincess]

All of the vars are character data.

 

[ISPrincess]

Please - I have tried everything - I do not know what to do now.

 

[tlbroadbent]

Please post the query segment that is giving you trouble. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[ISPrincess]

I tried posting earlier (but site was experiencing problems) that you were correct as usual. When I broke down the SQL string I found that I had an extra parenthesis before the word right. Very sorry about the last couple of posts.



But, more importantly, since I have that part working I need to move on and add the following to the end of this query:

  NAP
  WHERE Nap.MoveNo Not In
  (SELECT MoveNo From OPENQUERY(Server2, "Select MoveNo
  FROM Server2.Fac2.dbo.UCMHist AUR WHERE 
  NAP.MoveNo = AUR.MoveNo
  AND NAP.MoveType = AUR.MoveType
  AND NAP.WMSMoveType = AUR.WMSMoveType"))

Where NAP is the alias for the data from the first query.

Basically - I got everything I needed from the query we discussed but now I need to bump it up against another table and if the data is already there - I do not need it from the first table.

Feel like helping with this 'nested' openquery syntax??

 

[tlbroadbent]

The problem is that NAP doesn't exist on server2 or in the context of the remote query. In this case, you should avoid openquery.
[tt]
WHERE NOT EXISTS
(SELECT *
FROM Server2.Fac2.dbo.UCMHist
WHERE MoveNo = NAP.MoveNo
AND MoveType = NAP.MoveType
AND WMSMoveType = NAP.WMSMoveType)[/tt] Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[ISPrincess]

Ohhhh. Are there rules for this type of stuff posted somewhere? How do you know all this? Trial and error? Intense SQL training?

 

[tlbroadbent]

Mostly, OTJ training. I've been doing SQL server for nearly 8 years and SQL for longer than that. I like to experiment and try things so trial and error is a major component of my training. I also read a lot. Participation in this forum has helped me learn a lot.

There are so many places to look for info like this. I don't believe any single resource can adequately address all of the issues one may encounter. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[ISPrincess]

This is terrible - and I really feel bad about prolonging this agony, but everytime I think I've got it - there is another road-block.

Here is what is happening now, This is a union query between 3 sites. Anyway - in the end the entire query is longer than the 4000 characters allowed in the statement:

DECLARE @sSQL nVarchar(4000)

So now my question is:

1. Can I get a string type to be longer than 4000 or is that the limit?

2. If that is the limit, then I would like to get the query results from each facility into a #temptable. When I tried that earlier in combo with building the dynamic openquery stmts - I could not get that to work. Is there a way to do that?

3. Any other ideas?

 

[tlbroadbent]

You can use varchar instead of nvarchar. The varchar size limit is 8000 characters.

Inserting into a temp teable may be a better option. Simply create a stored procedure that creates the temp table and then insert each result set into the temp table. The final step in SP would be to select from the temp table. Make sure you include the statement "SET NOCOUNT ON" at the beginning before the inserts. This technique usually performs better than large UNION queries. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[ISPrincess]

Thanks-
I am still struggling (alot!) with the quote thing in this openquery statement. But when I added the words 'Into #TempTable ' in the query - although it still ran - I could never see the data in #TempTable.

Is this something to do with the exec statement?

 

[tlbroadbent]

Create the temp table before you execute the SQL statement. Then you can insert the result of the execute statement into the temp table.

Create #temp (col1, ... etc)

Insert #temp
Exec(@sql) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[ISPrincess]

Unbelievable!

I have never run into so many road-blocks ...

There must be a limitation on how long of a sql statement can be executed with exec because although I broke up each string and then tried to execute them like:

exec (@sSQL1 + @sSQL2)

The @ssQL5 string ended up being truncated for no apparent reason.

Anyway then I tried the temptable approach and got the following errors:

Server: Msg 8501, Level 16, State 1, Line 1
MSDTC on server 'SERVER1' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' does not support distributed transactions.

 

[tlbroadbent]

SQL BOL indicates that when concatenating multiple strings, the strings should each be limited to 4000 characters. If 800 characters, truncation will occur.

There are restrictions on distributed transactions. See SQL BOL for details on distributed queirs and transactions. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.