I have stored procedure in a database in an instance of Sql Server 2008.
I have a stored procedure that inside of it I want to connect to a Sql Server 2005 database with UserName and Password and Insert some data to a table.
How I can Do this?
thanks
Create a linked server. You cannot do this in the context of a stored procedure. You can however setup the login credentials in a stored procedure. In the example below it assumes that the linked server was named OTHERSERVER. This would allow you to pass a user name and password for the new connection and call a stored procedure.
create procedure NewTestProc (
@I int,
@userName sysname,
@password sysname
)
as
begin
declare @locallogin sysname
set @locallogin = SUSER_NAME()
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'OTHERSERVER', @useself=N'False',
@locallogin=@locallogin,@rmtuser=@userName,
@rmtpassword=@password
EXEC OTHERSERVER.DestinationDatabase.dbo.StoredProcInOtherDatabase
@OtherParameter = @i
EXEC master.dbo.sp_droplinkedsrvlogin
@rmtsrvname=N'OTHERSERVER',@locallogin=@locallogin
end
To call a stored procedure you would also have to enable RPC for the linked server
EXEC master.dbo.sp_serveroption @server=N'OTHERSERVER',
@optname=N'rpc', @optvalue=N'true'
Create linked server. In SSMS go to "Server Objects"->"Linked Servers" and create New Linked Server for Sql2005 instance providing security context you want to use. Then in the SP refer to the Sql2005 table as: [linked server name].[database name].[schema name].[table name]
