OPENQUERY and string

Hi

Does anyone know how to include a string in the statement of an open query?

I want to execute the following query:

select * from TEST where A like 'A'

But if use this it in an openquery like it follows

SELECT *

FROM OPENQUERY (MD_AS400, 'select * from TEST where A like 'A'')

The 'A' is not recognize like a string.

This is due to the single quote around 'A'

try this

''A'''

rule is if u need a quoted string put TWO quotes.

Gurpreet S. Gill

|||

You should escape quote by putting another quote.

So your query would be

' select * from TEST where A like ''A'' '

|||

Lot of lanugaues accepted the escape sequence char starts with \.

But in SQL Server (i remember in VB & MDX also) the same character will be repeated.

Code Snippet

SELECT *

FROM OPENQUERY (MD_AS400, 'select * from TEST where A like ''A''')

sql

OpenQuery & Quotes Syntax

I'm trying to select records within a date range from a linked server. If I
don't include the WHERE part and it's variables, my sql retrieves all
records. I also can hard code the WHERE criteria like "loadID=500".
SQL returns an error on my below code saying"
Incorrect syntax near '+'
What am I doing wrong with my commas and plus syntax? It looks correct, but
doesn't like my plus syntax for some reason.
CODE ***********
declare @.dtStartDate datetime, @.dtEndDate datetime
set @.dtStartDate = GetDate()
set @.dtEndDate = '20060701'
SELECT * FROM OPENQUERY(myLinkServer, 'SELECT loadID, loadDate
FROM load
WHERE loadDate >= ''' + convert(char(8), @.dtStartDate, 112) +
'''' + ' AND '
+ '' + 'loadDate <= ''' + convert(char(8), @.dtEndDate, 112) +
'''')On my system that string works fine. Using the current date for the
variables I get:
SELECT loadID, loadDate FROM load WHERE loadDate >= '20060515' AND
loadDate <= '20060515'
Are you certain that this is the line with the error?
Try to avoid building strings as parameters like this. Instead, assign the
string to a variable, and pass the variable as the parameter. You can
output the variable in order to make sure it contains the correct string.
You can then execute this string seperately in Query Analyzer to simplify
troubleshooting.
"Scott" <sbailey@.mileslumber.com> wrote in message
news:%23jm8YzCeGHA.4576@.TK2MSFTNGP05.phx.gbl...
> I'm trying to select records within a date range from a linked server. If
I
> don't include the WHERE part and it's variables, my sql retrieves all
> records. I also can hard code the WHERE criteria like "loadID=500".
> SQL returns an error on my below code saying"
> Incorrect syntax near '+'
> What am I doing wrong with my commas and plus syntax? It looks correct,
but
> doesn't like my plus syntax for some reason.
>
> CODE ***********
> declare @.dtStartDate datetime, @.dtEndDate datetime
> set @.dtStartDate = GetDate()
> set @.dtEndDate = '20060701'
> SELECT * FROM OPENQUERY(myLinkServer, 'SELECT loadID, loadDate
> FROM load
> WHERE loadDate >= ''' + convert(char(8), @.dtStartDate, 112)
+
> '''' + ' AND '
> + '' + 'loadDate <= ''' + convert(char(8), @.dtEndDate, 112)
+
> '''')
>|||SELECT * FROM OPENQUERY(myLinkServer, 'SELECT loadID, loadDate
FROM
load
WHERE
loadDate >= convert(char(8), getdate(), 112) +
AND loadDate <= convert(char(8), ''20060701'', 112)')
Could u please try with this?

OnVariableChangedEvent Log Message

I have posted a very simple request to have the message in the SSIS log from an OnVariableValueChanged Event to include the vale the variable has been set to.

If you like the idea of this enhancement please go and vote and validate it here

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=238803

Phil,

What does it have in there currently?

-Jamie

|||Just the name of the variable that changed