SQL Server Linked Server INSERT INTO

Question

I have problem with linked server at store procedure, I want to get data from linked server then insert into table.

This is my stored procedure:

CREATE PROCEDURE [dbo].[SP_GETPRODRECORD]
    @PR_NO varchar(10)=''
AS 
BEGIN
    DECLARE @OPENQUERY nvarchar(4000), 
            @TSQL nvarchar(4000), 
            @LinkedServer nvarchar(4000)  

    CREATE TABLE AAA (PR varchar(10))

    SET @LinkedServer = 'LS'

    SET @OPENQUERY = 'INSERT INTO AAA SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
    SET @TSQL = 'SELECT PSHN9G FROM F9G00 WHERE PSHN9G='''''+@PR_NO+''''')'

    EXEC (@OPENQUERY+@TSQL)
END

My problem is that the EXEC is not running, when I try to insert manually with code below is working

INSERT INTO AAA(PR) 
    SELECT PSHN9G 
    FROM OPENQUERY(WAVEDLIB,'SELECT PSHN9G FROM F9G00 WHERE PSHN9G=''XXXXXXX'')

Am I missing something?

Thanks

Answer 1

There is no need to use OPENQUERY and EXECUTE, you can simply reference LinkedServer if you know which DB is your table into:

CREATE PROCEDURE [dbo].[SP_GETPRODRECORD]
    @PR_NO varchar(10)=''
AS 
BEGIN
--No need to CREATE TABLE every time you exec SP. SELECT INTO #temp table instead.
SELECT PSHN9G INTO #temp FROM WAVEDLIB.DBName.dbo.F9G00 WHERE PSHN9G=@PR_NO
SELECT * FROM #temp
END