Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6053
  • Last Modified:

Insert into linked server using OPENQUERY

Im trying to create a stored procedure that inserts into table (on a linked server) using OpenQuery and Im getting following error.

OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface returned 0x80004002].
Msg 7390, Level 16, State 1, Line 1
The requested operation could not be performed because the OLE DB provider 'MSDASQL' does not support the required transaction interface.

Tried Select from same table using OpenQuery and it works fine. Linked server that Im trying to access is Informix on Linux.
Any idea what might be the problem?

DECLARE @sql1   VARCHAR(750), @sql2   VARCHAR(750), @sql3   VARCHAR(1000), @sql4   VARCHAR(1200)
declare @appl as varchar(5), @scf as varchar(3),@day_no varchar(1), @w_day varchar(2), @time_slot varchar(5), @tim_ampm varchar(2)
 
set @appl = 'COAT'
set @scf = '935'
set @day_no = '1'
set @w_day = 'MO'
set @time_slot = '10:00'
set @tim_ampm  ='AM' 
	
		set @sql1 = 'insert into OPENQUERY(NOBLE, '''
		set @sql2 = 'select appl, office_code, day_no, w_day, time_slot, time_ampm, slot_count from appt_setup'')'
		set @sql3 = ' values ('''
						+ @appl+''','''
						+ convert(varchar(3),@scf) + ''','''
						+ convert(varchar(2),@day_no) + ''','''
						+ @w_day + ''','''
						+ @time_slot + ''','''
						+ @tim_ampm + ''', 0)'
		set @sql4 = @sql1+ @sql2+@sql3
		exec (@sql4)

Open in new window

0
Jesus Rodriguez
Asked:
Jesus Rodriguez
  • 2
1 Solution
 
ralmadaCommented:
Have you added the linked server using sp_addlinkedserver sp?
More reference on this here:
http://msdn.microsoft.com/en-us/library/ms190479(SQL.90).aspx 
0
 
ralmadaCommented:
Also in Management Studio, go to Server Objects -> Linked Servers->Providers and right click on MSDASQL. Make sure that the Provider Option "Non transacted updates" is ticked.
0
 
Jesus RodriguezIT ManagerAuthor Commented:
Thanks ralmada, you are prompt and right to the point.
I checked the option Non transacted updates now my query works fine and I was able to insert records.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now