KGJ-Dev (7/10/2014)
Hi Micky,Appreciate your time on this and you sample works fine. but i need to call the procedure instead of this logic because before inserting i will have some logic. so calling SPC will be the best idea.
but by seeing these many constraints, i will propose my client to change this methodology to lined server. is possible to block other developers to use this linked server? because not of the developers should have execute access to the linked server. is it possible to do? any samples please how to achieve this impersonation.
I have just tried using OPENROWSET to call a remote stored procedure and it worked just fine - inserted the row and returned the results.
Here is what I created on the remote server:
Created SQL login called test, default database = ssc with public role on ssc.
create table Emp(IdEmployee int,Name sysname);
go
Create Procedure TestInsert(@IdEmployee int, @Name varchar(20))
as
Insert into Emp(IdEmployee,Name)
output inserted.IdEmployee, inserted.Name
values(@IdEmployee,@Name);
go
grant exec on TestInsert to test;
And on the local server I ran this:
SELECT *
FROM OPENROWSET('SQLNCLI','Server=FOO\BAR;Uid=test;Pwd=test;Database=ssc;',
'set nocount on set fmtonly off exec ssc.dbo.TestInsert 11,''Peter'''
) AS a;
It worked.
I also ran these variants that all worked.
SELECT *
FROM OPENROWSET('SQLNCLI','Server=FOO\BAR;Uid=test;Pwd=test;Database=ssc;',
'exec ssc.dbo.TestInsert 11,''Peter'''
) AS a;
SELECT *
FROM OPENROWSET('SQLOLEDB','Server=FOO\BAR;Uid=test;Pwd=test;Database=ssc;',
'exec ssc.dbo.TestInsert 11,''Peter'''
) AS a;
SELECT *
FROM OPENROWSET('MSDASQL','Driver={SQL Server};Server={FOO\BAR};Trusted_Connection={Yes};Database={ssc};',
'exec ssc.dbo.TestInsert 11,''Peter'''
) AS a;
Are you sure your sql login has permission to execute the stored proc?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);