OPENROWSET Query - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

OPENROWSET Query

Author

Topic

Ads
Starting Member

23 Posts

Posted - 2012-05-03 : 06:26:08

Hi,

I have an OPENROWSET query which isnt working as it did on server 2000.

I'm now seing an error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'WHERE'.

The code I am using:

<%
Dim rsSelectOrder
Dim rsSelectOrder_numRows

Set rsSelectOrder = Server.CreateObject("ADODB.Recordset")
rsSelectOrder.ActiveConnection = MM_conn_to_mhwebdb_STRING
rsSelectOrder.Source = "SELECT * from " &ActinicPersonsNew&" "&ActinicOrdersNew&" WHERE [Contact ID] = [Invoice Contact ID] AND [Order Number] NOT IN ( SELECT OrderNumber from dbo.tbl_QuasarOrdersEntered)"
rsSelectOrder.CursorType = 0
rsSelectOrder.CursorLocation = 2
rsSelectOrder.LockType = 1
rsSelectOrder.Open()

rsSelectOrder_numRows = 0
%>

Thanks

Ads

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-03 : 07:22:46

What I would suggest is to print out the query and see if you can execute it from an SSMS window. It seems to me that your query is something like:

SELECT * from ActinicPersonsNew ActinicOrdersNew WHERE [Contact ID] ....

May be you need a comma somewhere in there?

rsSelectOrder.Source = "SELECT * from " &ActinicPersonsNew&" ,"&ActinicOrdersNew&" WHERE [Contact ID] = [Invoice Contact ID] AND [Order Number] NOT IN ( SELECT OrderNumber from dbo.tbl_QuasarOrdersEntered)"