Linked Server

Question

Post reply

Linked Server

anil -167628

Newbie

Points: 5

April 22, 2005 at 8:40 am

#61548

I have a problem with linked server slowness.

I am trying to insert data from SQL server to Oracle in a Sql server stored procedure. It is working ok, with out any problem except slowness. Some times it may insert upto 5000 rows.

Following is my code in SQL Server SP. Please let me know if there is any alternative method.

CREATE PROCEDURE [dbo].[proc_res_data]

@pid NUMERIC(8), @ppid VARCHAR(1000) AS

DECLARE

@local_sqlstmt NVARCHAR(4000);

SET @local_sqlstmt = 'INSERT INTO bo..USER1.ORA_TABLE'

SET @local_sqlstmt = @local_sqlstmt + '(Oracle table field list)'

SET @local_sqlstmt = @local_sqlstmt + 'SELECT sql table field list'

SET @local_sqlstmt = @local_sqlstmt + ' FROM SQL_TABLE'

SET @local_sqlstmt = @local_sqlstmt + ' WHERE id = ' + CAST(@pid AS VARCHAR)

SET @local_sqlstmt = @local_sqlstmt + ' AND pid IN (' +@ppid + ')'

EXECUTE sp_executesql @local_sqlstmt;

GO

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 728159

April 22, 2005 at 11:07 am

#554395

It's probably a link issue between the servers where one is too busy to respond quickly if it's not a consistent problem.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Nilssond Hall of Fame Points: 3285 More actions · Answer 2

Nilssond

Hall of Fame

Points: 3285

April 22, 2005 at 3:12 pm

#554441

Collation compatibility may be an issue. Make sure you have the compatibility you want by checking the appropriate box in the linked server --> Properties -->Server Options applet tab.