December 29, 2011 at 8:38 am
I am trying to run the user define function on remote server but it's not working.
Please help SQL GURU.
CREATE TABLE #test
(
par1 INT ,
par2 INT
)
INSERT INTO #test
SELECT 1,2
UNION ALL
SELECT 3,4
UNION ALL
SELECT 5,6
SELECT * FROM #test t
SELECT * FROM #test t
CROSS APPLY server1.DB1.dbo.function1('2011-01-01',t.par1,t.par2) f
But Remote table-valued function calls are not allowed.
I can write something like
select * FROM #test t
join OPENQUERY(server1,'select * from DB1.dbo.function1(''2011-01-01'',t.par1,t.par2)') f
on t.something=f.something
But it not working and I want to pass temp table value to function.
December 29, 2011 at 8:50 am
You can't directly run a UDF on another server like that.
Build a stored procedure on the remote server that does what you want. Run that instead.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 29, 2011 at 9:07 am
Thanks for your response.
But How Can I pass the values of temp table to the store procedure?
December 29, 2011 at 9:16 am
Create a stored procedure on your local server. Create the temp table you want inside that stored procedure. Call the remote stored procedure from within the local stored procedure. I think you will be able to access the temp table from within the called stored procedure.
That will work for local stored procedures. I'm not sure about remote stored procedures. Give it a try and let us know if it works! 🙂
December 29, 2011 at 12:29 pm
krishusavalia (12/29/2011)
Thanks for your response.But How Can I pass the values of temp table to the store procedure?
How many values are you looking at and what SQL Server version? SQL 2008?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 29, 2011 at 1:13 pm
krishusavalia (12/29/2011)
Thanks for your response.But How Can I pass the values of temp table to the store procedure?
Either via a delimited list that you'd separate on the foreign server via something like the delimited8ksplitter, or via a table parameter.
Check out the following link for more information on table parameters:
http://msdn.microsoft.com/en-us/library/bb510489.aspx
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 12, 2012 at 8:01 am
I am using sql server 2008 R2. And I may have more than 1000 values in m y temp table and value pass this all to UDF.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply