Using Decimal value in Dynamic Open Query

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

General SQL Server Forums

New to SQL Server Programming

Using Decimal value in Dynamic Open Query

Author

Topic

kgundava
Yak Posting Veteran

66 Posts

Posted - 2011-08-01 : 15:12:18

Hi All,
I have a Dynamic sql query which needs to have a numeric value as a parameter i.e @samplepercentile..please see below example
declare @samplepercentile decimal(10,7)
select @samplepercentile = 0.0001

Select @RandomData = 'INSERT INTO RandomData SELECT * FROM OPENQUERY(QAORACLE,''select * from TEDR3."'+@TABLE_NAME+'"'' sample('+@samplepercentile+'))'
Print(@RandomData)

For some reason i am getting the below error when i am running this query...
Msg 8115, Level 16, State 6, Line 12
Arithmetic overflow error converting varchar to data type numeric.

Please let me know if there is any syntax error in @samplepercentile.
Thanks.....

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-01 : 21:04:30

You may need to cast the decimal to varchar to concatenate and make it into a string:


Select @RandomData = 'INSERT INTO RandomData SELECT * FROM
 OPENQUERY(QAORACLE,''select * from TEDR3."'
+@TABLE_NAME+'"'' sample('+cast(@samplepercentile as varchar(32))+'))'

kgundava
Yak Posting Veteran

66 Posts

Posted - 2011-08-02 : 10:55:41

Thanks a lot...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources