Heterogeneous queries and sp_sqlexec

[asmith555]

I have a sp that builds a querystring involving a linked server then trys to execute it. I keep getting the following error when I try to use the sp_sqlexec procedure "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query."

It seems to me that it should be working as both the ANSI_NULLS and ANSI_WARNINGS options are set inside and outside the procedure.

Here is the proc code.
Someone please help.

set ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
set QUOTED_IDENTIFIER OFF
go

CREATE PROCEDURE [dbo].[udpGetPickslipItems]
(
@PickslipItemId int = 0,
@PickslipId int = 0,
@SDPSN varchar(50) = ''
)
AS

BEGIN

DECLARE @ERRORMSG varchar(255)
DECLARE @ERRORID int

BEGIN TRAN

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
set QUOTED_IDENTIFIER OFF
SET NOCOUNT ON

DECLARE @SQL1 varchar(2000)
DECLARE @SQL2 varchar(4000)
DECLARE @Where varchar(200)
SET @Where = CASE WHEN @SDPSN = '' THEN ' WHERE 0 = 0' ELSE ' WHERE SDPSN = ''''' + @SDPSN+ '''''' END
SET @SQL1 = 'OPENQUERY(BEAPRDDTA, ''SELECT
SDPSN,
SDITM,
SDLITM,
SDLNID,
SDDSC1,
SDSOQS,
SDAN8,
IVITM,
IVCITM,
IVAN8
FROM F4211
LEFT OUTER JOIN(
SELECT IVITM,
IVCITM,
IVAN8
FROM F4104
WHERE IVXRT = ''''C''''
GROUP BY IVITM,
IVCITM,
IVAN8) F4104
ON F4211.SDAN8 = CASE WHEN F4104.IVAN8 = 0 THEN F4211.SDAN8 ELSE F4104.IVAN8 END
AND F4211.SDITM = F4104.IVITM ' +
@Where +
' AND SDLITM NOT LIKE ''''EXPEDITE FEE*''''
AND SDDSC1 NOT LIKE ''''EXPEDITE FEE*'''''')) AS F4211'
SET @SQL2 = 'SELECT PickSlipItemId,
PSI.PickslipId,
CASE WHEN PSI.SDITM IS NOT NULL THEN PSI.SDITM ELSE F4211.SDITM END AS SDITM,
CASE WHEN PSI.SDLITM IS NOT NULL THEN PSI.SDLITM ELSE F4211.SDLITM END AS SDLITM,
CASE WHEN PSI.SDLNID IS NOT NULL THEN PSI.SDLNID ELSE F4211.SDLNID END AS SDLNID,
CASE WHEN PSI.SDDSC1 IS NOT NULL THEN PSI.SDDSC1 ELSE F4211.SDDSC1 END AS SDDSC1,
CASE WHEN PSI.SDSOQS IS NOT NULL THEN PSI.SDSOQS/10000 ELSE F4211.SDSOQS END AS SDSOQS,
CASE WHEN PSI.IVCITM IS NOT NULL THEN PSI.IVCITM ELSE F4211.IVCITM END AS IVCITM,
CASE WHEN PSI.SDAN8 IS NOT NULL THEN PSI.SDAN8 ELSE F4211.SDAN8 END AS SDAN8,
PSI.Sat,
PSI.Unsat,
PSI.RevisionCode,
PSI.RevisionDate,
PSI.TFText,
PSI.BegSerialNo,
PSI.EndSerialNo,
PSI.SerialRange,
PSI.Comments
FROM PickslipItems PSI
LEFT OUTER JOIN Pickslips PS
ON PSI.PickslipId = PS.PickslipId
RIGHT OUTER JOIN(
SELECT *
FROM ' + @SQL1 + '
ON PS.SDPSN = F4211.SDPSN
WHERE PickSlipItemId = ' + CONVERT(varchar, @PickSlipItemId) + '
OR (PSI.PickSlipId = ' + CONVERT(varchar, @PickSlipId) + ' AND ' + CONVERT(varchar, @PickSlipItemId) + ' = 0)
OR F4211.SDPSN = ''' + @SDPSN + ''''

EXEC sp_sqlexec @SQL2

IF (@@ERROR <> 0)
BEGIN
SET @ERRORID = @@ERROR
SET @ERRORMSG = 'Get Pickslip Items Failed.'
GOTO ERRORHANDLER
END

COMMIT TRAN
RETURN

ERRORHANDLER:
RAISERROR (@ERRORMSG,10,1)
ROLLBACK TRAN
RETURN @ERRORID


END

 

[SQLSister]

I'm not sure but I believe that your setting is out of scope when you use exec sp_sqlexec. I think the setting of these items will need to be part of the text of the @sql variable.

Questions about posting. See faq183-874

 

[asmith555]

I tried adding the switches in the @SQL2 string shortly after my initial post and it still did not work

 

[asmith555]

I have come to a solution.

Instead of using the sp_sqlexec proc I just used the EXEC function and it worked fine.