How to Set a variable using OPENQUERY in SQL Server

Question

I am trying to read data from a table. This table have a list of table name.

for each row of the data set I want to run a couple of queries to pull data and insert it into a temporary table.

Here is What I have done

DECLARE @campName varchar(255);
DECLARE @sqlCommand varchar(1000);
DECLARE @sqlCommandMySQL varchar(1000);
DECLARE @LastRun varchar(60);
DECLARE @OPENQUERY varchar(1000);


DECLARE MY_CURSOR CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
SELECT LTRIM(RTRIM(CallListName)) AS CallListName
FROM [SMSQL1].[RDI_System].[dbo].[Campaigns]
WHERE dialer_campaign = 1 AND i3Server ='I3New' AND ClientID = 111 AND (EndDate IS NULL OR EndDate >= getdate() - 7)

OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @campName
WHILE @@FETCH_STATUS = 0
BEGIN 
    --SET @LinkedServer = 'GUARDIAN';
    SET @OPENQUERY = 'SELECT @LastRun = lr FROM OPENQUERY(GUARDIAN,''';
    SET @sqlCommandMySQL = 'SELECT IFNULL(MAX(lastRun), DATE_SUB(NOW(), INTERVAL 7 DAY) ) AS lr
                            FROM guardian_dynamo.runtimes_i3
                            WHERE CampaignListName = "'+@campName+'" '')';

    print @OPENQUERY + @sqlCommandMySQL;

    EXEC(@OPENQUERY + @sqlCommandMySQL);


    SET @sqlCommand = ' INSERT INTO #finalList(Attemtps, CAMPAIGNNAME, FINISHCODE, CALLDATE, AGENTID, RDINotes, PHONE, MERCHANTAccount)
                        SELECT ATTEMPTS, CAMPAIGNNAME, FINISHCODE, CALLDATE, AGENTID, RDINotes, PHONE, MERCHANTAccount 
                        FROM [I3_IC4].[dbo].['+ @campName +']
                        WHERE CALLDATE > '''+@LastRun+''' AND ISNULL(status, ''C'') IN (''U'', ''E'', ''A'', ''F'')   ';
    EXEC (@sqlCommand);


    FETCH NEXT FROM MY_CURSOR INTO @campName
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR;

every time I run this query I get this error

Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@LastRun".

I am not sure why since I am declaring this variable on the top as you can see in my code above.

the took the output of print @OPENQUERY + @sqlCommandMySQL; and executed that manually. It worked with no issue and the variable @LastRun will have a datetime value as it should.

Jaylen · Accepted Answer · 2015-02-04 18:11:34Z

7

You need to use sp_executesql to execute the dynamic query which helps you output the variable(@LastRun)

Declare @OPENQUERY Nvarchar(max), @sqlCommandMySQL Nvarchar(max), @OPENQUERYFINAL Nvarchar(max)
....
SET @OPENQUERY = 'SELECT @LastRun = lr FROM OPENQUERY(GUARDIAN,''';
SET @sqlCommandMySQL = 'SELECT IFNULL(MAX(lastRun), DATE_SUB(NOW(), INTERVAL 7 DAY) ) AS lr
                            FROM guardian_dynamo.runtimes_i3
                            WHERE CampaignListName = "'+@campName+'" '')';

    --print @OPENQUERY + @sqlCommandMySQL;
    SET @OPENQUERYFINAL = @OPENQUERY + @sqlCommandMySQL;

    EXEC sp_executesql @OPENQUERYFINAL, 
                       N'@LastRun varchar(10) OUTPUT',
                         @LastRun output

Demo

DECLARE @str VARCHAR(10),
        @sql NVARCHAR(max)

SET @sql= 'select @str=1 '

EXEC Sp_executesql
  @sql,
  N'@str varchar(10) OUTPUT',
  @str output

PRINT @str

edited Feb 4, 2015 at 18:11

Jaylen

40.5k43 gold badges140 silver badges228 bronze badges

answered Feb 4, 2015 at 17:38

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jaylen Over a year ago

thanks you. Yet I got another error Msg 195, Level 15, State 10, Line 1 'NOW' is not a recognized built-in function name. NOW() is a MySQL function and it should be executed on the MySQL server. I am not sure why I am getting this error now? it seems that the dynamic query is being executed on SQL Server insted on MySQL. One thing I changed to your syntax is EXEC sp_executesql @OPENQUERY + @sqlCommandMySQL, to EXEC sp_executesql @OPENQUERY , @sqlCommandMySQL,

Pரதீப் Over a year ago

instead use getdate() and isnull instead of ifnull in @sqlcommandmysql query

Jaylen Over a year ago

Now() is there after DATE_SUB. I can't use getdate() since that is not MySQL function. I think I corrected the issue but I am now getting a conversion issue Conversion failed when converting date and/or time from character string. I delatred @lastRun as datetime insted of varchar and that is still not working

Collectives™ on Stack Overflow

How to Set a variable using OPENQUERY in SQL Server

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related