Openquery - Error

[cfcProgrammer]

Hi,

I am trying to run a query using open query and a linked server.

Here is my query... the error I am getting is that the "FROM keyword not found where expected".

select * from openquery (STARSRO,'select NMS.SERIAL_NUM,
m.SerialNum as ''Serial Number'',
c.ContactCompanyName as ''Company'',
c.ParentCustomerId as ''Parent Cust ID'',
m.CustomerId as ''Customer ID'',
c.ARAccountCode as ''Account Number'',
c.ContactCompanyName as ''Comapny Name'',
CDR.ACCT_PERIOD,
sum(CDR.MINUTES)as Minutes
from
NMS_MOBILE_ID NMS,
CDR_CUSTOMER CDR,
iridium.dbo.Mobile m,
iridium.dbo.Customer c
where
c.Id = m.CustomerId
and
NMS.SERIAL_NUM = m.serialNum
and
CDR.MOBILE_ID = NMS.BILLABLE_MOBILE_ID
and
CDR.NETWORK = ''IRID''
and
(c.Id = 22151 or c.ParentCustomerId = 22151)
and
m.IsActive =''1''
and
m.SerialNum = ''8988169514000903117''
and CDR.ACCT_PERIOD = ''201211''
group by
NMS.SERIAL_NUM,
m.SerialNum,
m.CustomerId,
c.ParentCustomerId,
c.ARAccountCode,
c.ContactCompanyName,
CDR.ACCT_PERIOD')

Please help as this is driving me nuts... I'm sure it is a simple mistake that my eyes can't see anymore!!

Thanks in advance
CfcProgrammer

cfcProgrammer

 

[gk53]

as i remember it should be
select * from openquery (STARSRO,[highlight #CC0000]"[/highlight]select NMS.SERIAL_NUM,
m.SerialNum as ''Serial Number'',
c.ContactCompanyName as ''Company'',
c.ParentCustomerId as ''Parent Cust ID'',
m.CustomerId as ''Customer ID'',
c.ARAccountCode as ''Account Number'',
c.ContactCompanyName as ''Comapny Name'',
CDR.ACCT_PERIOD,
sum(CDR.MINUTES)as Minutes
from
NMS_MOBILE_ID NMS,
CDR_CUSTOMER CDR,
iridium.dbo.Mobile m,
iridium.dbo.Customer c
where
c.Id = m.CustomerId
and
NMS.SERIAL_NUM = m.serialNum
and
CDR.MOBILE_ID = NMS.BILLABLE_MOBILE_ID
and
CDR.NETWORK = ''IRID''
and
(c.Id = 22151 or c.ParentCustomerId = 22151)
and
m.IsActive =''1''
and
m.SerialNum = ''8988169514000903117''
and CDR.ACCT_PERIOD = ''201211''
group by
NMS.SERIAL_NUM,
m.SerialNum,
m.CustomerId,
c.ParentCustomerId,
c.ARAccountCode,
c.ContactCompanyName,
CDR.ACCT_PERIOD")

 

[gmmastros]

The error message you are getting appears to be an Oracle message, so I guess STARSRO is a linked server to an oracle database.

It looks like you are doubling up your single-quotes in the column aliases. I think this is the problem. I would recommend changing the 2 single-quotes to a single double-quote to see if this resolves the problem.

I would also suggest that you attempt to run the query (just the part inside openquery) directly on your Oracle database without going through the linked server. This may help you to track down the problem easier.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cs2009]

Another set of eyes here.

It appears you are missing a space after "sum(CDR.MINUTES)" and before "as".
See line sum(CDR.MINUTES)as Minutes just before the "from" clause.

 

[philhege]

It appears you are missing a space after "sum(CDR.MINUTES)" and before "as"

In SQL Server (anyway), you actually don't need the space. SQL understands the closing paren as the end of the function, and parses the statement directly after.

-----------
With business clients like mine, you'd be better off herding cats.

 

[cfcProgrammer]

I designed the query initially as below and it executes without errors however, because using this syntax the query takes off the where clause and goes out to the oracle database retrieves all the records first then when it brings back to sql server it then applies the where clause to the query... this is a huge table so this is not feasible which is why I am trying to do it using the openquery.

select NMS.SERIAL_NUM,
m.SerialNum as 'Serial Number',
c.ContactCompanyName as 'Company',
c.ParentCustomerId as 'Parent Cust ID',
m.CustomerId as 'Customer ID',
c.ARAccountCode as 'Account Number',
c.ContactCompanyName as 'Comapny Name',
CDR.ACCT_PERIOD,
sum(CDR.MINUTES)as Minutes
from
STARSRO..BILL_PROD.NMS_MOBILE_ID NMS,
STARSRO..BILL_PROD.CDR_CUSTOMER CDR,
iridium.dbo.Mobile m,
iridium.dbo.Customer c
where
c.Id = m.CustomerId
and
NMS.SERIAL_NUM = m.serialNum
and
CDR.MOBILE_ID = NMS.BILLABLE_MOBILE_ID
and
CDR.NETWORK = 'IRID'
and
(c.Id = 22151 or c.ParentCustomerId = 22151)
and
m.IsActive ='1'
and
m.SerialNum = '8988169514000903117'
and CDR.ACCT_PERIOD = '201211'
group by
NMS.SERIAL_NUM,
m.SerialNum,
m.CustomerId,
c.ParentCustomerId,
c.ARAccountCode,
c.ContactCompanyName,
CDR.ACCT_PERIOD

As suggested above in the response to my initial post, I tried modifying the quotes... the openquery did not like the double quotes around all of the query however, I did change the two single quotes to double quotes. I am receiving a different error.. it is now stating "OLE DB provider "OraOLEDB.Oracle" for linked server "STARSRO" returned message "ORA-00933: SQL command not properly ended"."

Thanks so much for the help you provided so far. I am running very close to missing a deadline so the frustration level is rising every time I try to run this thing!

Thanks
C

cfcProgrammer

 

[philhege]

I take it you've applied the corrections mentioned in the other thread about this query's having a Cartesian join condition?

-----------
With business clients like mine, you'd be better off herding cats.

 

[fredericofonseca]

are all the following tables on the oracle server?
NMS_MOBILE_ID
CDR_CUSTOMER
iridium.dbo.Mobile
iridium.dbo.Customer

doesnt look like it.

on the open query you can only query tables that are on the oracle server (or available to the oracle instance you are connecting to through a dblink with all correct permissions set)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[cfcProgrammer]

I am very confused now... I am trying to gather data from two different servers and I thought I could access the oracle database from a SQL Server by the linked server STARSRO. Using openquery was allowing me to run a query using all fields from both servers.

If this is not possible using OpenQuery and I am unable to use the STARSRO.. syntax then how am I to accomplish this?

Oracle tables -- NMS_MOBILE_ID NMS, CDR_CUSTOMER CDR

SQL Server tables -- iridium.dbo.Mobile m, iridium.dbo.Customer c

Information needed
Serial Number (m.SerialNum)
Company (c.ContactCompanyName)
Parent Cust ID (c.ParentCustomerId)
Customer ID (m.CustomerId)
Account Number (c.ARAccountCode)
Company Name (c.ContactCompanyName)
Accounting Period (CDR.ACCT_PERIOD)
Minutes (sum(CDR.MINUTES))

The linked server is STARSRO

Can someone please help me determine the correct syntax to use. I know this can be done, however, I am very confused to how!!

Thanks
C



cfcProgrammer

 

[fredericofonseca]

you can do it - but solution is never the same for all queries. Lots of factors influence the use of openquery and/or linked servers.

This

http://msdn.microsoft.com/en-us/library/ms178532.aspx

is something that always should be looked at.

As for your own query - this one has hardcoded values - will it always be the case or will you need to vary the values? - if they vary you will need to use dynamic sql to build the openquery select statement.

this particular query could also be improved to allow for part of the processing to be done on the server.

select select T.SERIAL_NUM
      ,m.SerialNum as 'Serial Number'
      ,c.ContactCompanyName as 'Company' 
      ,c.ParentCustomerId as 'Parent Cust ID'
      ,m.CustomerId as 'Customer ID'
      ,c.ARAccountCode as 'Account Number'
      ,c.ContactCompanyName as 'Comapny Name'
      ,T.ACCT_PERIOD
      ,sum(T.MINUTES)as Minutes
 from openquery (STARSRO,[highlight #8AE234]'select NMS.SERIAL_NUM
                                ,CDR.ACCT_PERIOD
                                ,sum(CDR.MINUTES) as Minutes
                          from BILL_PROD.NMS_MOBILE_ID NMS

                          inner join BILL_PROD.CDR_CUSTOMER CDR
                          on CDR.MOBILE_ID = NMS.BILLABLE_MOBILE_ID
                          and CDR.NETWORK = ''IRID''
                          and CDR.ACCT_PERIOD = ''201211''
                          group by NMS.SERIAL_NUM
                                  ,CDR.ACCT_PERIOD'[/highlight]
                          ) t

inner join iridium.dbo.Mobile m
on t.SERIAL_NUM = m.serialNum
and m.IsActive ='1'
and m.SerialNum = '8988169514000903117'

inner join iridium.dbo.Customer c
on c.Id = m.CustomerId
and (c.Id = 22151 or c.ParentCustomerId = 22151)

group by t.SERIAL_NUM 
        ,m.SerialNum 
        ,m.CustomerId 
        ,c.ParentCustomerId
        ,c.ARAccountCode
        ,c.ContactCompanyName
        ,t.ACCT_PERIOD

Now.. as i don't know that data you have I have duplicated the group by - this is done both on the linked server and on the local server - it may be that your joins with the iridium tables do not give duplicates and the outside group by may not be required. up to you to figure it out.
the openquery bit is basically moving a possibly significant part of the processing to the remote server and returning a smaller row count than the original query. Again this needs to be verified by you with something like

select count(*)
from (
select NMS.SERIAL_NUM
      ,CDR.ACCT_PERIOD
      ,sum(CDR.MINUTES) as Minutes
from BILL_PROD.NMS_MOBILE_ID NMS

inner join BILL_PROD.CDR_CUSTOMER CDR
on CDR.MOBILE_ID = NMS.BILLABLE_MOBILE_ID
and CDR.NETWORK = 'IRID'
and CDR.ACCT_PERIOD = '201211'
group by NMS.SERIAL_NUM
        ,CDR.ACCT_PERIOD
) t

select count(*)
from BILL_PROD.NMS_MOBILE_ID NMS

inner join BILL_PROD.CDR_CUSTOMER CDR
on CDR.MOBILE_ID = NMS.BILLABLE_MOBILE_ID
and CDR.NETWORK = 'IRID'
and CDR.ACCT_PERIOD = '201211'

the first query returns the expected number of rows after the group by and the second query returns the number should you retrieve them all to SQL server (which is what your previous query was doing even if you didn't know it)
the higher the difference the better the new query will behave.

you will also need to considere that in any situation where you have local and remote tables it may be better to either retrieve the remote tables to a local temp table and then do the required joins, or send some of the local tables to the remote server, join there to minimize the records to be transferred across for the remaining joins.

With regards to how openquery works with remote and local tables I hope the example above is clear to you on how it is done. - note that in some cases it is impossible to do it without some kind of send/receive of temp tables


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[cfcProgrammer]

Thank you so much for your help. The query is working and more importantly I understand this concept much better. I appreciate your help, thanks so much again!!
C

cfcProgrammer