Call a insert procedure with OPENROWSET

• Teutales01

  Mr or Mrs. 500

  Points: 522

  More actions

  January 15, 2013 at 5:12 am

  #272769

  Hi Folks,

  There's a simple procedure which inserts data into a table:

  ALTER PROCEDURE [tmp].[test_insert] ( @val INT ) AS

  INSERT INTO robot5.tmp.test VALUES( @val )

  SELECT * FROM robot5.tmp.test

  RETURN

  I want to call this Procedure with an OPENROWSET:

  SELECT * FROM OPENROWSET('SQLNCLI', 'Server=(local);UID=<uid>;PWD=<pwd>', 'SET NOCOUNT ON;EXEC robot5.tmp.test_insert ''22''')

  When I call the procedure I get the correct ResultSet within the new value. When I call now

  SELECT * FROM robot5.tmp.test

  the inserted Value is missing. Looks like a roleback after the openrowset call. When I do the Insert without the OPENROWSET the ResultSet is correct.

• Teutales01

  Mr or Mrs. 500

  Points: 522

  More actions

  January 15, 2013 at 5:19 am

  #1577273

  Here's the solution - add a COMMIT in the OPENROWSET:

  SELECT * FROM OPENROWSET('SQLNCLI', 'Server=(local);UID=<uid>;PWD=<pwd>', 'SET NOCOUNT ON;EXEC robot5.tmp.test_insert ''22'';COMMIT;')

  But why?