Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 EXEC(@SQL) and OPENQUERY with MySQL

Author  Topic 

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2013-01-16 : 10:54:04
Having a strange problem. I am trying to pull data from a legacy MySQL system into a SQL Server 2012 database. The issue is that when I try to execute the query as dynamic sql, I get an error; but when I print out the variable value and then run it, it works fine.

DECLARE             
@BeginPullDATE VARCHAR(20)
,@TSQL VARCHAR(8000);

set @BeginPullDATE = '2012-11-01';
SELECT @TSQL =
'SELECT RECNUM, VENDNO, PARTNO, CUSTNO, EVENTNO, [DATE], EXTADJ, VARMOV, VARSAL, VARPUR, INFLATTAG, INFLATSTK, EXTRECON, TS, BRANCH, PARENTCLASS, CLASS, CATEGORY, SKEY, DOCKEY, IKEY, [EVENT]
FROM OPENQUERY(mysql, ''select * from sys.msevents
WHERE DATE > ''''' + @BeginPullDATE + ''''' limit 100'')'

PRINT @TSQL;
EXEC @TSQL;


The PRINT @TSQL line gives me the result
SELECT RECNUM, VENDNO, PARTNO, CUSTNO, EVENTNO, [DATE], EXTADJ, VARMOV, VARSAL, VARPUR, INFLATTAG, INFLATSTK, EXTRECON, TS, BRANCH, PARENTCLASS, CLASS, CATEGORY, SKEY, DOCKEY, IKEY, [EVENT]
FROM OPENQUERY(mysql, 'select * from sys.msevents
WHERE DATE > ''2012-11-01'' limit 100')


When I copy the print output into a query window in SMSS and run it, it works. But it doesn't work when I use EXEC (@TSQL).

Anyone have any idea why the query will work when run as a query but not as dynamic sql?

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-16 : 22:40:58
can you post the error message you're getting?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-01-17 : 05:15:33
EXEC @TSQL;

Should be

EXEC (@TSQL);

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ElHolios
Starting Member

2 Posts

Posted - 2013-01-17 : 05:23:08
I have a similar problem.... I wish to assign a query string result to a variable.... in the first example, this works!


declare @sql nvarchar(max), @a int
set @sql = (select count(distinct age) as c from TADAA)
set @a = (select @sql)
select @a

it is fine.

In the second example, my string is concatenated from a generated column name, but the execution process will not assign the result to a variable (the same answer as the topic above with the same @sql string as the one above....


declare @ColumnName varchar(50),@sql nvarchar(max), @a int
set @ColumnName = (
SELECT Top(1)COLUMN_NAME
FROM information_schema.columns
WHERE table_name = 'TADAA' order by COLUMN_NAME asc)

set @sql = '(select count(distinct ' + @ColumnName + ') as c from TADAA)'
set @a = (select @sql)
select @a

I receive the folliwing error:

Conversion failed when converting the nvarchar value '(select count(distinct AGE) as c from TADAA)' to data type int.

I know a string cannot be assigned to int, but I wish to execute the string and result the answer to int.

PLEASE HELP me..!!

Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-17 : 05:34:43
for returning a value through dynamic query, you need sp_executesql and not EXEC

see example under category C

http://msdn.microsoft.com/en-us/library/ms188001.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-01-17 : 05:36:32
One way is

declare @ColumnName varchar(50),@sql nvarchar(max), @a int
set @ColumnName = (
SELECT Top(1)COLUMN_NAME
FROM information_schema.columns
WHERE table_name = 'TADAA' order by COLUMN_NAME asc)

set @sql = '(select count(distinct ' + @ColumnName + ') as c from TADAA)'

declare @output table(counting int)
insert into @output
exec(@sql)
select @a=(select counting from @output)
select @a

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ElHolios
Starting Member

2 Posts

Posted - 2013-01-17 : 05:41:09
Thsnk you... MOST APPRECIATED!!! I am working on quite a complicate decision net in SQL - will be back...!!
Go to Top of Page
   

- Advertisement -