July 26, 2012 at 5:07 am
HI, have a developer with an issue he would like a quick answer to. Openquery called from a stored proc fails with error below
OLE DB provider "SQLNCLI10" for linked server "LGRDCDSQL06\PROTEA" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@PARAM_FILENAME".
the relevant part of the stored proc is below, anyone throw any light on what its unhappy about
ALTER procedure [dbo].[sp_Job_FderScanDataProc] (@PARAM_FILENAME varchar(255)) as
-- Local variable declaration
-- File handling variables
declare @temp varchar(255)
declare @data varchar(255)
declare @data1 varchar(255)
declare @delimiter char(1)
declare @nHandle int
declare @result int
declare @return int
declare @bEOF int
-- Feeder file entities
declare @qstntype int
declare @docno int
declare @qstnno int
declare @qno int
declare @qfieldno int
declare @qdata varchar(255)
declare @hrhdocno int
-- Default return value
select @result = 0
-- Set delimiter character
select @delimiter = char(150)
-- Open the input file for reading
SELECT * FROM OPENQUERY([LGRDCDSQL06\PROTEA],'exec master..xp_FileReadStart @PARAM_FILENAME, @nHandle output')
select 'Active File Handle : ' = @nHandle
---------------------------------------------------------------------
July 26, 2012 at 5:50 am
The reason is query 'exec master..xp_FileReadStart @PARAM_FILENAME, @nHandle output' would be executed under a new session.
The variable @PARAM_FILENAME is not visible in this new session.
July 26, 2012 at 6:12 am
thanks. so do it as dynamic sql then?
---------------------------------------------------------------------
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply