Dynamic SQL with OPENQUERY doesn't work

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)

Dynamic SQL with OPENQUERY doesn't work

Author

Topic

Narinder
Starting Member

7 Posts

Posted - 2011-06-28 : 13:09:12

We are planning to upgrade to SQL server 2008 and during testing phase we found the SQL code that worked fine on 2000 and 2005 doesn't work on 2008.

It's complaning on subquery in Select list of the OPENQUERY. We are taking about huge data sets. On 2000 and 2005 the query finishes in 3 minutes. If I remove the subquery it works just fine? FYI, moving subquery to FROM clause impacts the performance from 3 minutes to atleast an hour.

Here is partial code.

DECLARE @runDateTime varchar(23)
DECLARE @fromDateTime varchar(23)
DECLARE @toDateTime varchar(23)
DECLARE @labTstCde varchar(8)

SET @runDateTime = CONVERT(varchar(23), GETDATE(), 121)
SET @labTstCde = '9000'

Setting fromDateTime variable to mid night AM time
SET @fromDateTime = CONVERT(varchar(23), DATEADD(dd, DATEDIFF(dd,0, DATEADD(DD, -(13 + DATEPART(DW, @runDateTime)), @runDateTime)), 0), 121)

Setting toDateTime variable to mid night PM time
SET @toDateTime = CONVERT(varchar(23), DATEADD(ms, -3, DATEADD(dd, 7, @fromDateTime)), 121)

TRUNCATE TABLE dbo.SAM_CH1_PRE_LAB

EXEC ('INSERT INTO dbo.SAM_CH1_PRE_LAB( csr_nbr, spm_col_dtm, acs_eny_dtm, lab_acs_nbr, svc_ctr_cde, Run_Dte_Tme )
SELECT DISTINCT csr_nbr
, spm_col_dtm
, acs_eny_dtm
, lab_acs_nbr
, svc_ctr_cde
, CAST(''' + @runDateTime + ''' as datetime) Run_Dte_Tme
FROM OPENQUERY(dw_sybase, ''(SELECT tst.csr_nbr
, CONVERT(VARCHAR(10),tst.spm_col_dtm, 101) as spm_col_dtm
, CONVERT(VARCHAR(10),tst.acs_eny_dtm, 101) as acs_eny_dtm
, tst.lab_acs_nbr
, (SELECT DISTINCT pfm_lab_svc_ctr_cde
FROM data_warehouse.dbo.lrf_lab_rsl_evt_lvl_1
WHERE lab_acs_nbr = tst.lab_acs_nbr
AND pvd_ghc_lab_tst_cde = tst.pvd_ghc_lab_tst_cde
AND lab_acs_yr = tst.lab_acs_yr) as svc_ctr_cde
FROM data_warehouse.dbo.LRF_LAB_TST_EVT_lvl_3 tst
WHERE tst.pvd_ghc_lab_tst_cde = ''''' + @labTstCde + '''''
AND tst.spm_col_dtm BETWEEN ''''' + @fromDateTime + ''''' AND ''''' + @toDateTime + '''''
AND EXISTS (SELECT 1
FROM data_warehouse.dbo.LRF_LAB_ACS_lvl_3
WHERE lab_acs_nbr = tst.lab_acs_nbr
AND pat_cls_cde = ''''O''''))'')')

Select * From dbo.SAM_CH1_PRE_LAB

NS

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-28 : 13:18:16

Have you tried using
insert #a
exec ('....') at linkedserver
to get the data into a temp table then reformat locally for the final insert.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Narinder
Starting Member

7 Posts

Posted - 2011-06-28 : 15:07:46

Because I am pulling data from Sybase to SQL server 2008, where you want to pull data into temp table. Also, could you give me an example to your hint (exec ('....') at linkedserver).

NS

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-28 : 15:33:06

hmm guess my post failed
>> I am pulling data from Sybase to SQL server 2008, where you want to pull data into temp table
where do you think the temp tavlbe is?

Put the query you want to send to sybase in a string @sql
create a temp table #a with the correct structure

insert #a
exec (@sql) at dw_sybase

Then you can use the result as a local table.

What is the error message you get and does the query execute when not in dynamic sql?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Narinder
Starting Member

7 Posts

Posted - 2011-06-29 : 17:57:36

The query did returned data with your approach. But insert into temp table doesn't work. I get following message.

OLE DB provider "MSDASQL" for linked server "dw_sybase" returned message "[Sybase][ODBC Driver]Failed to create an XA helper object.".
Msg 7391, Level 16, State 2, Line 111
The operation could not be performed because OLE DB provider "MSDASQL" for linked server "dw_sybase" was unable to begin a distributed transaction.

Here is the new code.

create table #tt(csr_nbr char(8),spm_col_dtm varchar(10),acy_eny_dtm varchar(10),lab_acs_nbr char(8),svc_ctr_cde char(3));

declare @sql varchar(2000)
set @sql = ('SELECT tst.csr_nbr
, CONVERT(VARCHAR(10),tst.spm_col_dtm, 101) as spm_col_dtm
, CONVERT(VARCHAR(10),tst.acs_eny_dtm, 101) as acs_eny_dtm
, tst.lab_acs_nbr
, (SELECT DISTINCT pfm_lab_svc_ctr_cde
FROM data_warehouse.dbo.lrf_lab_rsl_evt_lvl_1
WHERE lab_acs_nbr = tst.lab_acs_nbr
AND pvd_ghc_lab_tst_cde = tst.pvd_ghc_lab_tst_cde
AND lab_acs_yr = tst.lab_acs_yr) as svc_ctr_cde
FROM data_warehouse.dbo.LRF_LAB_TST_EVT_lvl_3 tst
WHERE tst.pvd_ghc_lab_tst_cde = ''' + @labTstCde + '''
AND tst.spm_col_dtm BETWEEN ''' + @fromDateTime + ''' AND ''' + @toDateTime + '''
')

INSERT #tt
EXEC(@sql) at dw_sybase

NS

Narinder
Starting Member

7 Posts

Posted - 2011-06-29 : 18:19:30

I think I resolved the issue by setting linked server property "Enable Promotion of Distributed Transactions" to false. But if there is another way to resolve this issue please share.

Thanks for your help!!

NS

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-30 : 05:42:32

As it's an insert it has to run in a transaction. The driver doesn't support distributed transactions (doesn't support phased commits across the servers) so I don't think there'sa a way around it.
I usually play around with properties until I find something that works and am grateful for that. Another option is to use ssis - that's sometimes needed if the linked server doesn't support the code page conversion anyway. I prefer the source system to deliver files which I can import as that isolates the processes but it all depends on the environment and requirements.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Narinder
Starting Member

7 Posts

Posted - 2011-09-28 : 13:40:33

Hi Nigel,

After fixing the query, now I start getting following messages. We increased the server memory also but no luck.

Here is the error message. FYI, there are no rows marked for deletion.

OLE DB provider "MSDASQL" for linked server "dw_sybase" returned message "Row handle referred to a deleted row or a row marked for deletion.".
Msg 7346, Level 16, State 2, Procedure edm_load_csr_dem, Line 4
Cannot get the data of the row from the OLE DB provider "MSDASQL" for linked server "dw_sybase".

Here is the list of different error messages:

OLE DB provider "MSDASQL" for linked server "dw_sybase" returned message "[Sybase][ODBC Driver]String data, right truncated".
OLE DB provider "MSDASQL" for linked server "dw_sybase" returned message "[Sybase][ODBC Driver]Internal Error".
OLE DB provider "MSDASQL" for linked server "dw_sybase" returned message "[Sybase][ODBC Driver]Data overflow. Increase specified column size or buffer size".
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "dw_sybase".

OLE DB provider "MSDASQL" for linked server "dw_sybase" returned message "[Sybase][ODBC Driver]Internal Error".
OLE DB provider "MSDASQL" for linked server "dw_sybase" returned message "[Sybase][ODBC Driver]Internal Error".
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "dw_sybase".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources