SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 EXEC(@SQL) and OPENQUERY with MySQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stonebreaker
Yak Posting Veteran

USA
85 Posts

Posted - 01/16/2013 :  10:54:04  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/16/2013 :  22:40:58  Show Profile  Reply with Quote
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

India
22772 Posts

Posted - 01/17/2013 :  05:15:33  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
EXEC @TSQL;

Should be

EXEC (@TSQL);

Madhivanan

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

ElHolios
Starting Member

South Africa
2 Posts

Posted - 01/17/2013 :  05:23:08  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/17/2013 :  05:34:43  Show Profile  Reply with Quote
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

India
22772 Posts

Posted - 01/17/2013 :  05:36:32  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

South Africa
2 Posts

Posted - 01/17/2013 :  05:41:09  Show Profile  Reply with Quote
Thsnk you... MOST APPRECIATED!!! I am working on quite a complicate decision net in SQL - will be back...!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000