Using OPENROWSET for moving data from SQL Server t

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

Using OPENROWSET for moving data from SQL Server t

Author

Topic

mgmisola
Starting Member

1 Post

Posted - 2010-10-07 : 22:03:19

Hi Everyone,
I'm using Openrowset function to access data from other server. My problem is, Using the first statement below,it works perfectly,

SELECT a.*
FROM OPENROWSET('SQLOLEDB', 'ATRKES-BLADE1'; 'sa'; 'sa',
'SELECT No_,Name,[Account Type] from NAVISIONLIVE..[ATR KIMENG FINANCIAL LIVE$G_L Account]') AS a

But on the other hand, the second select statement,see below

SELECT x.* FROM OPENROWSET
('SQLOLEDB','ATRKES-BLADE1';'sa';'sa',
'SELECT [CompanyCode]=''ATRKEFC'',AccountCode=a.No_,AccountName=a.Name,
sum(case when [posting date] >= ''Sep 1 2010 4:14PM'' then amount else 0 end) as Amount,
sum(case when [posting date] >= ''Sep 1 2010 4:14PM'' then [debit amount] else 0 end) as debit,
sum(case when [posting date] >= ''Sep 1 2010 4:14PM'' then [credit amount] else 0 end) as credit
FROM NAVISONLIVE..[ATR KIMENG FINANCIAL LIVE$G_L Account] a
JOIN NAVISONLIVE..[ATR KIMENG FINANCIAL LIVE$G_L ENTRY] b
ON b.[G_L Account No_]=a.No_
WHERE [posting date] <= ''Sep 30 2010 4:14PM''
GROUP BY No_, Name ORDER BY No_') x

it returns a message..

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Deferred prepare could not be completed."

I don't understand why it returns a message like that. Please help!!
Appreciate your help on this. Thanks in advance!!!

mgmisola

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-10-08 : 06:25:18

Try this:


SELECT x.* FROM OPENROWSET 
('SQLOLEDB','ATRKES-BLADE1';'sa';'sa',
'SELECT ''ATRKEFC'' as [CompanyCode],a.No_ as AccountCode,a.Name as AccountName, 
sum(case when [posting date] >= ''Sep 1 2010 4:14PM'' then amount else 0 end) as Amount,
sum(case when [posting date] >= ''Sep 1 2010 4:14PM'' then [debit amount] else 0 end) as debit,
sum(case when [posting date] >= ''Sep 1 2010 4:14PM'' then [credit amount] else 0 end) as credit 
FROM NAVISONLIVE..[ATR KIMENG FINANCIAL LIVE$G_L Account] a 
JOIN NAVISONLIVE..[ATR KIMENG FINANCIAL LIVE$G_L ENTRY] b 
ON b.[G_L Account No_]=a.No_ 
WHERE [posting date] <= ''Sep 30 2010 4:14PM'' 
GROUP BY No_, Name ORDER BY No_') x

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources