Inserting data with Linked Servers

[Dimitrie1]

I have an asp.net app and am calling stored procedures. My app worked great until I had to use linked servers. I did some research and learned that yes performance is impacted and specially on inserts. i read about openquery but can't figure out how to use openquery with my current sql statement - please help.

 select @current_licence = f.folderrsn 
from amandaosg.amanda_dev.dbo.folderpeople fp join amandaosg.amanda_dev.dbo.folder f 
	on fp.folderrsn = f.folderrsn
	where fp.peoplersn = @ArgRegistrationPeopleRSN
	and f.foldertype =    @FolderType and f.subcode = @subcode and statuscode = 2 and folderyear = convert(numeric,substring( convert (char(4),datepart(yy,@now)),3,4) )

 

[SQLSister]

Since it is only referring to one server why not create a stored proc onthat server and execute it?

Also get rid of that convert(numeric,substring( convert (char(4),datepart(yy,@now)),3,4) ) in your statment, it has to repeat for evey single row in the select. Since they all refer to the same varaible, create another variable to store the converted value and use that instead.

"NOTHING is more important in a database than integrity." ESquared

 

[Dimitrie1]

my connection is only to server1 and this database is on server2.

it's an internet app and the security guys won't let server2 thru the firewall

good point on the convert.... statement - I stole this proc from somebody else.

 

[mrdenny]

What SQL Sister was saying was create a stored procedure on Server2, and have Server1 execute the procedure on Server2 via the linked server.

You'll need to enable RPC and RPC Out on the linked server before you can do this.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Dimitrie1]

oh ok missed that - sorry sqlsister.

I tried that and it worked great. thanks mrdenny/sqlsister

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog