Stored procedure with openquery

[psychodad71]

Hi,

do anyone find the fault ?

------------------------------

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



ALTER PROCEDURE [Fallauskunft]
(@nr int)

AS
begin
declare @alles varchar(8000)
select @alles ='
select * from openquery (connection, ''select *
from connection..view_dale2.VP where original_vers_id ='''+ @nr+''')'
exec (@alles)
END
GO

------------------------------------

Uli

 

[ClaireHCM]

ALTER PROCEDURE [Fallauskunft](@nr int)
AS
declare @alles varchar(8000)
select @alles ='select * from openquery (connection,'+
'"select * from connection..view_dale2.VP where original_vers_id ='+"'"+convert(varchar,@nr)+"'"+'&quot'
exec(@alles)

 

[psychodad71]

Sorry it does not work. There must by something wrong with the select statement. Any ideas ?

ULi

 

[zhavic]

May be two dots in 'connection..view_dale2.VP'

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[ClaireHCM]

May I know what is the error msg you are getting?

 

[nat35]

Try that
select @alles ='
select * from openquery (connection, ''select *
from connection..view_dale2.VP where
(original_vers_id = '''''+ @nr+ ''''')'')'

 

[nat35]

I did not see so your @nr must be a varchar
convert first
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



ALTER PROCEDURE [Fallauskunft]
(@nr int)

AS
begin
declare @alles varchar(8000),@nr1 varchar(10)
set @nr1 = convert(varchar,@nr)
select @alles ='
select @alles ='
select * from openquery (connection, ''select *
from connection..view_dale2.VP where
(original_vers_id = '''''+ @nr1+ ''''')'')'
exec (@alles)
END
GO