Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

OPENROWSET to return an integer with parameters Expand / Collapse
Author
Message
Posted Sunday, April 4, 2010 9:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 21, 2012 3:17 PM
Points: 2, Visits: 25
OPENROWSET to return an integer with parameters


Inside a function, I need to return an Integer. But if I use parameters I have to wrap the whole Openrowset in Dynamic SQL and It becomes a string and I get the following error when I call the function:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '
SELECT MAID
FROM OPENROWSET('SQLOLEDB','Server=(local);Trusted_Connection=Yes;Database=CDR;',
'Select Top 1 MAID From MastersAgentsRangeList Where ParentID In ( Select MAID From MastersAgentsRangeList
Where RangeFrom = ''1000'' AND RangeTo = ''5000'') ORDER BY NEWID()' )' to data type int.



Statement used to call function:
Select [dbo].[fn_GetRandomT2Value](1000 ,5000 ) as MAID

This is the function:

ALTER FUNCTION [dbo].[fn_GetRandomT2Value](@RangeFrom Int, @RangeTo Int)
RETURNS Int

AS
BEGIN
DECLARE @SQLString NVARCHAR(500)
DECLARE @MAID Int

Set @MAID =(
N'
SELECT MAID
FROM OPENROWSET(''SQLOLEDB'',''Server=(local);Trusted_Connection=Yes;Database=CDR;'',
''Select Top 1 MAID From MastersAgentsRangeList Where ParentID In ( Select MAID From MastersAgentsRangeList
Where RangeFrom = ''''' + Cast(@RangeFrom as nvarchar) + ''''' AND RangeTo = ''''' + Cast(@RangeTo as nvarchar) + ''''') ORDER BY NEWID()'' )'
)

RETURN @MAID

END
Post #896518
Posted Tuesday, April 6, 2010 10:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 1:49 PM
Points: 1,104, Visits: 1,174
My first question ... why are you using OPENROWSET to query a database on the local server? Even if you're hitting a remote server, the function is really designed for ad hoc queries. Otherwise it is better to use linked servers.

└> bt


Forum Etiquette: How to post data/code on a forum to get the best help
Post #897683
Posted Tuesday, April 6, 2010 11:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 21, 2012 3:17 PM
Points: 2, Visits: 25
Seems you cant use Order By new() in a function unless it is in Dynamic sql. And the only way I could find to use it was in openrowset...Any other idea?
Post #897744
Posted Tuesday, April 6, 2010 7:15 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 1:49 PM
Points: 1,104, Visits: 1,174
NEWID() is to create a new unique identifier ... ordering by that function makes no sense to me. I would be curious to understand exactly what you're trying to accomplish in terms of the data you're working with and the results you're after.

└> bt


Forum Etiquette: How to post data/code on a forum to get the best help
Post #898054
Posted Wednesday, April 7, 2010 6:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 37,075, Visits: 31,633
bteraberry (4/6/2010)
NEWID() is to create a new unique identifier ... ordering by that function makes no sense to me. I would be curious to understand exactly what you're trying to accomplish in terms of the data you're working with and the results you're after.


It produces a result set in random order.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #899162
Posted Wednesday, April 7, 2010 6:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 37,075, Visits: 31,633
robert-695083 (4/6/2010)
Seems you cant use Order By new() in a function unless it is in Dynamic sql. And the only way I could find to use it was in openrowset...Any other idea?


You can trick it, though. Create a view with nothing in it but SELECT NEWID() AS SomeNewID and you can call the view from the function.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #899165
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse