Remote Query help - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Remote Query help

Author

Topic

chih
Posting Yak Master

154 Posts

Posted - 2012-04-13 : 01:29:38

Hi Everyone,

I tried to get SampleID from a linked server.

This is an original script. But it costs for a remote query.

Select @SampleID=SampleID From [linkedServer].[Mem].[dbo].[Member] Where UserID=@UserID

I rewrote it to like

EXEC ('Select SampleID From [linkedServer].[Mem].[dbo].[Member] Where UserID=?',@UserID ) at [linkedServer]

It is faster but how can I assign the output to @SampleID?
I have tried to create a table variable and insert the output to it. But it costs even more than the original query.

Can anyone help if there is any way I can assign the output to @SampleID?

Thanks

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-13 : 02:55:36

I am not sure about what you said about the EXEC query being faster. May be it was faster for you. But on my system, the first query was faster than the second in most of the traces.

Regarding your question, you sure can store the result of a dynamic SQL statement in a local variable using sp_executesql which allows input parameters and returns results as output parameters(temp variables).

Check the Following link:

[url]http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/516ab7eb-f087-44c0-a1b1-374e8c493c4e[/url]

Vinu Vijayan

N 28° 33' 11.93148"
E 77° 14' 33.66384"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 11:51:54

quote:
Originally posted by chih

Hi Everyone,

I tried to get SampleID from a linked server.

This is an original script. But it costs for a remote query.

Select @SampleID=SampleID From [linkedServer].[Mem].[dbo].[Member] Where UserID=@UserID

I rewrote it to like

EXEC ('Select SampleID From [linkedServer].[Mem].[dbo].[Member] Where UserID=?',@UserID ) at [linkedServer]

It is faster but how can I assign the output to @SampleID?
I have tried to create a table variable and insert the output to it. But it costs even more than the original query.

Can anyone help if there is any way I can assign the output to @SampleID?

Thanks

i dont think you need dynamic sql here.
Did you try creating it as procedure with input and output parameter and returning value through it?

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

chih
Posting Yak Master

154 Posts

Posted - 2012-04-16 : 00:34:32

Yes, I did try but it will still need to assign to @output variable in the SP, isn't it?

quote:
Originally posted by visakh16

quote:
Originally posted by chih

Hi Everyone,

I tried to get SampleID from a linked server.

This is an original script. But it costs for a remote query.

Select @SampleID=SampleID From [linkedServer].[Mem].[dbo].[Member] Where UserID=@UserID

I rewrote it to like

EXEC ('Select SampleID From [linkedServer].[Mem].[dbo].[Member] Where UserID=?',@UserID ) at [linkedServer]

It is faster but how can I assign the output to @SampleID?
I have tried to create a table variable and insert the output to it. But it costs even more than the original query.

Can anyone help if there is any way I can assign the output to @SampleID?

Thanks

i dont think you need dynamic sql here.
Did you try creating it as procedure with input and output parameter and returning value through it?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-16 : 11:39:47

quote:
Originally posted by chih

Yes, I did try but it will still need to assign to @output variable in the SP, isn't it?

quote:
Originally posted by visakh16

quote:
Originally posted by chih

Hi Everyone,

I tried to get SampleID from a linked server.

This is an original script. But it costs for a remote query.

Select @SampleID=SampleID From [linkedServer].[Mem].[dbo].[Member] Where UserID=@UserID

I rewrote it to like

EXEC ('Select SampleID From [linkedServer].[Mem].[dbo].[Member] Where UserID=?',@UserID ) at [linkedServer]

It is faster but how can I assign the output to @SampleID?
I have tried to create a table variable and insert the output to it. But it costs even more than the original query.

Can anyone help if there is any way I can assign the output to @SampleID?

Thanks

i dont think you need dynamic sql here.
Did you try creating it as procedure with input and output parameter and returning value through it?

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

why you need to assign it inside sp?
whats the value you're trying to return? why not return it using RETURN clause if its of type int?

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

chih
Posting Yak Master

154 Posts

Posted - 2012-04-16 : 19:04:16

Originally I want to do is
Select @SampleID=SampleID From [linkedServer].[Mem].[dbo].[Member] Where UserID=@UserID

So I can use @SampleID in other input and update.

quote:
Originally posted by visakh16

quote:
Originally posted by chih

Yes, I did try but it will still need to assign to @output variable in the SP, isn't it?

quote:
Originally posted by visakh16

quote:
Originally posted by chih

Hi Everyone,

I tried to get SampleID from a linked server.

This is an original script. But it costs for a remote query.

Select @SampleID=SampleID From [linkedServer].[Mem].[dbo].[Member] Where UserID=@UserID

I rewrote it to like

EXEC ('Select SampleID From [linkedServer].[Mem].[dbo].[Member] Where UserID=?',@UserID ) at [linkedServer]

It is faster but how can I assign the output to @SampleID?
I have tried to create a table variable and insert the output to it. But it costs even more than the original query.

Can anyone help if there is any way I can assign the output to @SampleID?

Thanks

i dont think you need dynamic sql here.
Did you try creating it as procedure with input and output parameter and returning value through it?

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

why you need to assign it inside sp?
whats the value you're trying to return? why not return it using RETURN clause if its of type int?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-17 : 13:59:15

wrap it in a procedure like below


CREATE PROC GetSampleID
@UserID int,
@SampleID int OUTPUT 
AS

Select @SampleID=SampleID From [linkedServer].[Mem].[dbo].[Member] Where UserID=@UserID

GO

Then execute it like

DECLARE @SampID int

EXEC GetSampleID <your user id value>,@SampID OUT

SELECT @SampID

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources