CODEWITHSUNDEEP
sqlsql-serveropenquery
Perry
Perry

Reputation: 1337

Why wont this query work

I have the following SQL openquery

SELECT      @TSQL = 'SELECT * FROM OPENQUERY(RMSPROD2,''SELECT COUNT(DISTINCT W.Bond) AS NoBID_Count
                                                FROM TIB.WRMAST w
                                                WHERE (w.BID In (''No Bid'', ''No Cost'', ''None'') AND w.CtlNumber = ''''' + @WarControlID + ''''''')'

When I run this query I get the following error:

SELECT * FROM OPENQUERY(RMSPROD2,'SELECT COUNT(DISTINCT W.Bond) AS NoBond_Count
                                                FROM TIBURON.WRMAST w
                                                WHERE (w.Bond In ('No Bond', 'No Bail', 'None') AND w.CtlNumber = ''575403''')
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'No'.

What is the error that is being thrown I have no indications in the design window of any issues

Upvotes: 0

Views: 143

Answers (3)

Perry
Perry

Reputation: 1337

I could not find an answer to this problem So I went with a different approach. I used the OpenQuery with this code

SELECT  @TSQL = 'SELECT * FROM OPENQUERY(RMSPROD2,''SELECT TIB.WRMAST.WR_INVL, TIB.WRMAST.WR_WARR_CTL,TIBURON.WRMAST.WR_BAIL,TIB.WRWCHG.WC_BAIL
                       FROM TIB.WRMAST 
                       LEFT JOIN TIBURON.WRWCHG
                       ON WRWCHG.WC_WR_CHAIN = WRMAST.WRMAST_ROW
                       WHERE TIBURON.WRMAST.WR_WARR_CTL = ''''' + @WarControlID + ''''''')'
Insert Into @WarrantBail
   EXEC (@TSQL)

And placed the results into a Temp Table. I was then able to use regular T-SQL to write the rest of the needed syntax.

Upvotes: 0

Fernando Gutierrez
Fernando Gutierrez

Reputation: 439

I think you are missing a couple of '' characters, try the next:

SELECT      @TSQL = 'SELECT * FROM OPENQUERY(RMSPROD2,''SELECT COUNT(DISTINCT   W.Bond) AS NoBID_Count
                                            FROM TIB.WRMAST w
                                            WHERE (w.BID In (''''No Bid'''', ''''No Cost'''', ''''None'''') AND w.CtlNumber = ''''' + @WarControlID + ''''''')'

Upvotes: 1

I_am_Batman
I_am_Batman

Reputation: 915

The root cause of your problem is that you are inadvertently closing the opening quote here :

SELECT * FROM OPENQUERY(RMSPROD2,'SELECT COUNT(DISTINCT W.Bond) AS NoBond_Count
                                                FROM TIBURON.WRMAST w
                                                WHERE (w.Bond In ('

Hence you are getting the error near No. In java for instance, \ is the escape character, and so to ensure continuity, we would have to write :

"SELECT COUNT(DISTINCT W.Bond) AS NoBond_Count
                                                    FROM TIBURON.WRMAST w
                                                    WHERE (w.Bond In (\'     "  //till the end

Search for appropriate escape character in the language of your choice and apply it.

Upvotes: 2

Related Questions