Error with WHERE in OPEN QUERY Statement

Question

Error with WHERE in OPEN QUERY Statement

Welsh Corgi

SSC Guru

Points: 116520
More actions
March 12, 2012 at 10:57 am

#252427

The following works:
SELECT *
FROM OPENQUERY(LSCTLRDAT1, 'SELECT * FROM Company')
This does not work and I can't remember the systax to indude the WHERE CLause.
SELECT *
FROM OPENQUERY(LSCTLRDAT1, 'SELECT * FROM Company WHERE EFF0YR = '112' AND EFF0MO = '09' AND EFF0DA BETWEEN '01' AND '09'')
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 15 posts - 1 through 15 (of 32 total)

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

Jared SSC Guru Points: 61793 More actions · Answer 1

Welsh Corgi (3/12/2012)
The following works:
SELECT *
FROM OPENQUERY(LSCTLRDAT1, 'SELECT * FROM Company')
This does not work and I can't remember the systax to indude the WHERE CLause.
SELECT *
FROM OPENQUERY(LSCTLRDAT1, 'SELECT * FROM Company WHERE EFF0YR = '112' AND EFF0MO = '09' AND EFF0DA BETWEEN '01' AND '09'')

Add single quotes to your string...

SELECT *

FROM OPENQUERY(LSCTLRDAT1, 'SELECT * FROM Company WHERE EFF0YR = ''112'' AND EFF0MO = ''09'' AND EFF0DA BETWEEN ''01'' AND ''09''')

Jared
CE - Microsoft

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

I tried the additional quotes but it did not return any data?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

Are you sure that there are some data which satisfies all WHERE clauses?

Try to run exactly the same SELECT on your source server...

Without double quotes your statement is invalid.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jared SSC Guru Points: 61793 More actions · Answer 4

What happens when you run this query on the server directly?

SELECT * FROM Company WHERE EFF0YR = '112' AND EFF0MO = '09' AND EFF0DA BETWEEN '01' AND '09'

Jared
CE - Microsoft

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

This looks like Year, Month, Day? You have 112 as your year.

But more importantly WHY not use datetime datatypes. And if you MUST separate out the portions WHY varchar and not int?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

I did not create this Database CSC Did and it is not fun to work with

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

I ran the query on the Server using a toll called SQuirrel without error.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

I ran the query on the Server using a toll called SQuirrel without error.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Pam Brisjar SSChampion Points: 12094 More actions · Answer 9

What's the error?

Do you have any date time columns in the source table? Those can wreak all kinds of havoc when you bring them back to SQL Server.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

Pam Brisjar (3/12/2012)
What's the error?
Do you have any date time columns in the source table? Those can wreak all kinds of havoc when you bring them back to SQL Server.

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near '112'.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Pam Brisjar SSChampion Points: 12094 More actions · Answer 11

You sure that particular column is a character type not a numeric?

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

Pam Brisjar (3/12/2012)
You sure that particular column is a character type not a numeric?

An implicit conversion of '112' to any numeric datatype would not be a problem.

--edit: spelling.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 13

Welsh Corgi (3/12/2012)
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '112'.

What is the EXACT query you are running?

It would be a LOT easier to help you if you read and followed the ideas outlined in the first link of your signature.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Pam Brisjar SSChampion Points: 12094 More actions · Answer 14

An implicit conversion of '112' to any numeric datatype would not be a problem.

Not necessarily. I've seen DB2 do some strange things with data type mismatches.