SQL Server Forums - OPENROWSET Query

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

SQL Server Forums

Register Now and get your question answered!

Username:	Password:
Save Password
Forgot your Password?

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

OPENROWSET Query

New Topic

Reply to Topic

Printer Friendly

Author

Topic

Ads
Starting Member

23 Posts

Posted - 05/03/2012 : 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
Flowing Fount of Yak Knowledge

5152 Posts

Posted - 05/03/2012 : 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)"