reg linked server

We are using linked servers to access another server, we are using windows authentication. but when we use

EXEC master.dbo.sp_addlinkedserver @.server = 'SERVER', @.srvproduct=N'SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER', @.locallogin = NULL, @.useself = N'False', @.rmtuser = N'sa', @.rmtpassword = N'sa'

When we connect using a SQL login as specified in the above line we are able to connect to the linked server.

But when we want to connect using a Windows user it is connecting.(we want to connect using a specific windows user i.e only one windows user will be there in the server and we shall access the server using that windows user)

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER', @.locallogin = NULL, @.useself = N'False', @.rmtuser = N'Domain\userid', @.rmtpassword = 'password'

Is there any other way to connect to the linked serrver.. we dont want to use the SQL server login to connect to the linked server.. how to use windows authentication to connect to the linked server. Anyone have tried it out.. Thanks in advance

It should work try the following example...

EXEC sp_addlinkedserver @.server = 'SERVER', @.provider = 'SQLOLEDB.1', @.srvproduct = '', @.provstr = 'Privider=SQLOLEDB.1;Data Source=TargetServer;Initial Catalog=Database'

go

Exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER', @.useself = true, @.locallogin = null, @.rmtuser = 'Domain\Userid', @.rmtpassword = 'Password'

go

Select * From OpenQuery(MyRemoteServer, 'Select * From Sysobjects')

--OR

Select * From MyRemoteServer.DatabaseName.dbo.Sysobjects

go

Exec sp_droplinkedsrvlogin 'SERVER', null

Exec sp_dropserver 'SERVER'

Referencing a variable set in an EXEC statement

I have code like this:
DECLARE @.CurrentValue nvarchar(1000)
EXEC('SELECT @.CurrentValue = min(Column) FROM Table')
PRINT @.CurrentValue
When I execute this code, I get an error message that I have to declare
@.CurrentValue. Apparently the code in the EXEC statement runs in it's own
scope and it doesn't see variables outside that scope.
But if I try to declare the variable inside the EXEC string, I can't access
it outside the EXEC statement.
I tried creating a global variable (@.@.CurrentValue), but that doesn't seem
to work either."MatthewR" <MatthewR@.discussions.microsoft.com> schrieb im Newsbeitrag
news:D5D08C86-E390-45C0-8D92-A341D457E50B@.microsoft.com...
>I have code like this:
> DECLARE @.CurrentValue nvarchar(1000)
> EXEC('SELECT @.CurrentValue = min(Column) FROM Table')
> PRINT @.CurrentValue
> When I execute this code, I get an error message that I have to declare
> @.CurrentValue. Apparently the code in the EXEC statement runs in it's own
> scope and it doesn't see variables outside that scope.
> But if I try to declare the variable inside the EXEC string, I can't
> access
> it outside the EXEC statement.
> I tried creating a global variable (@.@.CurrentValue), but that doesn't seem
> to work either.
>|||Hi,
try the following
DECLARE @.CurrentValue nvarchar(1000)
set @.CurrentValue = (Select min(Column) FROM Table)
Hope it helps
Regards
Alex|||Have a look here:
http://www.support.microsoft.com/?id=262499 Using OutPut Params &
sp_executeSql
Andrew J. Kelly SQL MVP
"MatthewR" <MatthewR@.discussions.microsoft.com> wrote in message
news:D5D08C86-E390-45C0-8D92-A341D457E50B@.microsoft.com...
>I have code like this:
> DECLARE @.CurrentValue nvarchar(1000)
> EXEC('SELECT @.CurrentValue = min(Column) FROM Table')
> PRINT @.CurrentValue
> When I execute this code, I get an error message that I have to declare
> @.CurrentValue. Apparently the code in the EXEC statement runs in it's own
> scope and it doesn't see variables outside that scope.
> But if I try to declare the variable inside the EXEC string, I can't
> access
> it outside the EXEC statement.
> I tried creating a global variable (@.@.CurrentValue), but that doesn't seem
> to work either.
>|||http://www.aspfaq.com/2492
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"MatthewR" <MatthewR@.discussions.microsoft.com> wrote in message
news:D5D08C86-E390-45C0-8D92-A341D457E50B@.microsoft.com...
>I have code like this:
> DECLARE @.CurrentValue nvarchar(1000)
> EXEC('SELECT @.CurrentValue = min(Column) FROM Table')
> PRINT @.CurrentValue
> When I execute this code, I get an error message that I have to declare
> @.CurrentValue. Apparently the code in the EXEC statement runs in it's own
> scope and it doesn't see variables outside that scope.
> But if I try to declare the variable inside the EXEC string, I can't
> access
> it outside the EXEC statement.
> I tried creating a global variable (@.@.CurrentValue), but that doesn't seem
> to work either.
>|||Look in the posting from ED and today (15.04.2005 20:47) (for Google
searcher the article
http://support.microsoft.com/defaul...kb;en-us;262499)
It describes using the Return value from an Execute. The problem is that in
the context of executing a new session is established and closed when the
statement was executed. So in the next line the value doesnt exists anymore
(in your query)
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"MatthewR" <MatthewR@.discussions.microsoft.com> schrieb im Newsbeitrag
news:D5D08C86-E390-45C0-8D92-A341D457E50B@.microsoft.com...
>I have code like this:
> DECLARE @.CurrentValue nvarchar(1000)
> EXEC('SELECT @.CurrentValue = min(Column) FROM Table')
> PRINT @.CurrentValue
> When I execute this code, I get an error message that I have to declare
> @.CurrentValue. Apparently the code in the EXEC statement runs in it's own
> scope and it doesn't see variables outside that scope.
> But if I try to declare the variable inside the EXEC string, I can't
> access
> it outside the EXEC statement.
> I tried creating a global variable (@.@.CurrentValue), but that doesn't seem
> to work either.
>|||> I tried creating a global variable (@.@.CurrentValue),
Where did you get the idea of a "global variable"? This is merely a
variable called @.CurrentValue, with a @. prefix to indicate it is a variable.
There is absolutely no difference between @.@.CurrentValue and @.CurrentValue.
A|||Thanks for the link. sp_executeSQL with OUTPUT parameters worked great!
"Andrew J. Kelly" wrote:

> Have a look here:
> http://www.support.microsoft.com/?id=262499 Using OutPut Params &
> sp_executeSql
>
> --
> Andrew J. Kelly SQL MVP
>
> "MatthewR" <MatthewR@.discussions.microsoft.com> wrote in message
> news:D5D08C86-E390-45C0-8D92-A341D457E50B@.microsoft.com...
>
>