Linked server query with Quotation Marks

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Linked server query with Quotation Marks

Author

Topic

sw007
Starting Member

3 Posts

Posted - 2007-07-06 : 09:05:56

Hi,
My first post. I am trying to run a select query to a linked server (Cache database on VMS OS) from Microsoft SQL 2000 Query Analyzer.
Here is my problem query:
SELECT *
FROM OPENROWSET('MSDASQL',
'DSN=SHADOW',
'SELECT * FROM Member_Acct WHERE close_dt > {d'2007-07-01'}'

I am getting this error:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '2007'.

I have tried using double quotes and everythingelse I could. Database expect me to send date in this format {d'YYYY-MM-DD'}.

Please help me.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-07-06 : 10:03:29

Try declaring a varchar variable to hold the statement.
Load the statement into the variable and then pass it to the OPENROWSET command.
This should at least help with debugging, as you'll be able to see exactly what is being passed.

e4 d5 xd5 Nf6

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-06 : 10:04:06

replace 1 sigle quote with 2 single quotes

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-06 : 10:06:41

openrowset accepcts variables??

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-07-06 : 11:07:31

OPENROWSET doesn't accept variables?

e4 d5 xd5 Nf6

sw007
Starting Member

3 Posts

Posted - 2007-07-06 : 11:48:48

Thanks. I tried double quotes and that didn't resolve this issue.
I know OPENQUERY can pass Variable but how do I pass a variable with OPENROWSET?

Please help me.

quote:
Originally posted by spirit1

replace 1 sigle quote with 2 single quotes

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-06 : 11:52:25

so this doesn't work for you?


SELECT *
FROM OPENROWSET('MSDASQL',
		'DSN=SHADOW',
		'SELECT * FROM Member_Acct WHERE close_dt > {d''2007-07-01''}')

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-07-06 : 12:20:31

quote:
Originally posted by sw007

Thanks. I tried double quotes and that didn't resolve this issue.

Not double-quotes. Two single quotes. There is a difference.

e4 d5 xd5 Nf6

sw007
Starting Member

3 Posts

Posted - 2007-07-06 : 12:31:05

Thank you. Its working now. I really appriciate it.

quote:
Originally posted by blindman

quote:
Originally posted by sw007

Thanks. I tried double quotes and that didn't resolve this issue.
Not double-quotes. Two single quotes. There is a difference.

e4 d5 xd5 Nf6

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-06 : 12:42:48

> OPENROWSET doesn't accept variables?

if you show me how i'd be glad

becaue this doesn't work


DECLARE @sql NVARCHAR(4000)

SELECT @sql = N'SELECT GroupName, Name, DepartmentID
      FROM AdventureWorks.HumanResources.Department
      ORDER BY GroupName, Name'
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=MyServer;Trusted_Connection=yes;', @sql) AS a;

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-07-06 : 13:22:45

Well, that would answer my question, then.

Obviously a flaw in SQL Server, as it is not behaving the way that I expect it to behave. I shall have a serious talk with Bill next time I see him.

e4 d5 xd5 Nf6

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-06 : 13:28:55

i expect nothing less of you

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-07-06 : 13:39:55

Ummm.... Not really familiar with OPENROWSET myself so I could be wrong, but isn't the problem the {d'2007-07-01'}? That's not valid SQL Server SQL for a date, is it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources