Linked Server Properties - Security - SQL Server 2000

I would like to specify a locallogin that is a domain(not local) group. It
allows me to specify it, but does not recognize when memebrs of the group ar
e
connected. I have been forced to specify each group memebr individually. I
s
there a way to make the domain groupt option work?I do not think this is possible based on looking at the help file for
sp_addlinkedserverlogin.
Jason Massie
http://statisticsio.com
"Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>I would like to specify a locallogin that is a domain(not local) group. It
> allows me to specify it, but does not recognize when memebrs of the group
> are
> connected. I have been forced to specify each group memebr individually.
> Is
> there a way to make the domain groupt option work?|||Jason,
I agree. If I understand correctly, the logins must be either SQL Server or
Windows User logins, not Windows Groups. That is the only way the server
has a solid security context from which to work. (Much the same thing can
be seen in owners of SQL Agent jobs and several other security features.)
If the login is set up as @.useself=N'True' then anyone can use the link
under their own credentials. That may be too wide open for you Steve, but
if it is not, then you can control group membership on the other side of the
link when granting rights to the linked server's database(s).
RLF
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
>I do not think this is possible based on looking at the help file for
>sp_addlinkedserverlogin.
> --
> Jason Massie
> http://statisticsio.com
> "Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
> message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>|||The login connecting to my sqlserver has no credentials on the remote server
.
My server is an integration point for several databases. My application has
credentials on the remote servers, but If I need to get to my server with sa
y
a firecall id, I want to be able to access the remove servers with the same
permissions that my app has. Our firecall ids are all in a network group.
There are about 60 ids. Fortunatley, they do not change very often, so I
incorporated showmbrs.exe into my script, and add each id individually.
However, if the group changes, I will need to re-run the script. Was lookin
g
for a better option.
"Russell Fields" wrote:

> Jason,
> I agree. If I understand correctly, the logins must be either SQL Server o
r
> Windows User logins, not Windows Groups. That is the only way the server
> has a solid security context from which to work. (Much the same thing can
> be seen in owners of SQL Agent jobs and several other security features.)
> If the login is set up as @.useself=N'True' then anyone can use the link
> under their own credentials. That may be too wide open for you Steve, but
> if it is not, then you can control group membership on the other side of t
he
> link when granting rights to the linked server's database(s).
> RLF
> "jason" <jason-r3move@.statisticsio.com> wrote in message
> news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
>
>

Linked Server Properties - Security - SQL Server 2000

I would like to specify a locallogin that is a domain(not local) group. It
allows me to specify it, but does not recognize when memebrs of the group are
connected. I have been forced to specify each group memebr individually. Is
there a way to make the domain groupt option work?
I do not think this is possible based on looking at the help file for
sp_addlinkedserverlogin.
Jason Massie
http://statisticsio.com
"Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>I would like to specify a locallogin that is a domain(not local) group. It
> allows me to specify it, but does not recognize when memebrs of the group
> are
> connected. I have been forced to specify each group memebr individually.
> Is
> there a way to make the domain groupt option work?
|||Jason,
I agree. If I understand correctly, the logins must be either SQL Server or
Windows User logins, not Windows Groups. That is the only way the server
has a solid security context from which to work. (Much the same thing can
be seen in owners of SQL Agent jobs and several other security features.)
If the login is set up as @.useself=N'True' then anyone can use the link
under their own credentials. That may be too wide open for you Steve, but
if it is not, then you can control group membership on the other side of the
link when granting rights to the linked server's database(s).
RLF
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
>I do not think this is possible based on looking at the help file for
>sp_addlinkedserverlogin.
> --
> Jason Massie
> http://statisticsio.com
> "Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
> message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>
|||The login connecting to my sqlserver has no credentials on the remote server.
My server is an integration point for several databases. My application has
credentials on the remote servers, but If I need to get to my server with say
a firecall id, I want to be able to access the remove servers with the same
permissions that my app has. Our firecall ids are all in a network group.
There are about 60 ids. Fortunatley, they do not change very often, so I
incorporated showmbrs.exe into my script, and add each id individually.
However, if the group changes, I will need to re-run the script. Was looking
for a better option.
"Russell Fields" wrote:

> Jason,
> I agree. If I understand correctly, the logins must be either SQL Server or
> Windows User logins, not Windows Groups. That is the only way the server
> has a solid security context from which to work. (Much the same thing can
> be seen in owners of SQL Agent jobs and several other security features.)
> If the login is set up as @.useself=N'True' then anyone can use the link
> under their own credentials. That may be too wide open for you Steve, but
> if it is not, then you can control group membership on the other side of the
> link when granting rights to the linked server's database(s).
> RLF
> "jason" <jason-r3move@.statisticsio.com> wrote in message
> news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
>
>

Linked Server Properties - Security - SQL Server 2000

I would like to specify a locallogin that is a domain(not local) group. It
allows me to specify it, but does not recognize when memebrs of the group are
connected. I have been forced to specify each group memebr individually. Is
there a way to make the domain groupt option work?I do not think this is possible based on looking at the help file for
sp_addlinkedserverlogin.
--
Jason Massie
http://statisticsio.com
"Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>I would like to specify a locallogin that is a domain(not local) group. It
> allows me to specify it, but does not recognize when memebrs of the group
> are
> connected. I have been forced to specify each group memebr individually.
> Is
> there a way to make the domain groupt option work?|||Jason,
I agree. If I understand correctly, the logins must be either SQL Server or
Windows User logins, not Windows Groups. That is the only way the server
has a solid security context from which to work. (Much the same thing can
be seen in owners of SQL Agent jobs and several other security features.)
If the login is set up as @.useself=N'True' then anyone can use the link
under their own credentials. That may be too wide open for you Steve, but
if it is not, then you can control group membership on the other side of the
link when granting rights to the linked server's database(s).
RLF
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
>I do not think this is possible based on looking at the help file for
>sp_addlinkedserverlogin.
> --
> Jason Massie
> http://statisticsio.com
> "Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
> message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
>>I would like to specify a locallogin that is a domain(not local) group.
>>It
>> allows me to specify it, but does not recognize when memebrs of the group
>> are
>> connected. I have been forced to specify each group memebr individually.
>> Is
>> there a way to make the domain groupt option work?
>|||The login connecting to my sqlserver has no credentials on the remote server.
My server is an integration point for several databases. My application has
credentials on the remote servers, but If I need to get to my server with say
a firecall id, I want to be able to access the remove servers with the same
permissions that my app has. Our firecall ids are all in a network group.
There are about 60 ids. Fortunatley, they do not change very often, so I
incorporated showmbrs.exe into my script, and add each id individually.
However, if the group changes, I will need to re-run the script. Was looking
for a better option.
"Russell Fields" wrote:
> Jason,
> I agree. If I understand correctly, the logins must be either SQL Server or
> Windows User logins, not Windows Groups. That is the only way the server
> has a solid security context from which to work. (Much the same thing can
> be seen in owners of SQL Agent jobs and several other security features.)
> If the login is set up as @.useself=N'True' then anyone can use the link
> under their own credentials. That may be too wide open for you Steve, but
> if it is not, then you can control group membership on the other side of the
> link when granting rights to the linked server's database(s).
> RLF
> "jason" <jason-r3move@.statisticsio.com> wrote in message
> news:449C967C-FB40-47F8-9492-4C27A605E1DC@.microsoft.com...
> >I do not think this is possible based on looking at the help file for
> >sp_addlinkedserverlogin.
> >
> > --
> > Jason Massie
> > http://statisticsio.com
> >
> > "Steve Wilkinson" <SteveWilkinson@.discussions.microsoft.com> wrote in
> > message news:D6C69E85-C31A-4919-BFCE-343078675B68@.microsoft.com...
> >>I would like to specify a locallogin that is a domain(not local) group.
> >>It
> >> allows me to specify it, but does not recognize when memebrs of the group
> >> are
> >> connected. I have been forced to specify each group memebr individually.
> >> Is
> >> there a way to make the domain groupt option work?
> >
>
>

Linked Server Not Displaying Table Details

I have created a linked server using my local SQL2005. The linked server can be seen as a linked server, but the database can not been expanded to see the tables, stored procedures, views, and other details regarding the linked server. The only method that I have found that will allow me to see all the details is to use XP's Remote Desktop Connection to access the database. Has anyone else experienced this problem, if so, how did you resolve it?

Hi,

you need SQL Server 2k5 SP2 to browse through databases, tables etc from UI.

Code Snippet

EXEC sp_addlinkedserver
@.server = N'LONDON2',
@.srvproduct = N'',
@.provider = N'SQLNCLI',
@.provstr = N'SERVER=192.168.100.150;Integrated Security=True'

EXEC sp_tables_ex @.table_server = 'LONDON2',
@.table_catalog = 'AdventureWorks',
@.table_schema = 'HumanResources',
@.table_type = 'TABLE'

linked server for a named instance

sql2k sp3
Im doing some testing on my local pc. The name of my pc happens to have "-"
's in it. I have two instances of sql on it. So lets say I have the names of
my SQL instances are:
1. aaa-aa-aaa
2.aaa-aa-aaa\Destination
While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
"select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because of
the dashes. So I go into Client Network Utility and create an Alias of
"Destination". Then when I try the revised query "select * from
destination.pubs.dbo.authors" I get:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid authorization specification]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
So whats the dealio?
TIA, ChrisR
"select * from aaa-aa-aaa\Destination.pubs.dbo.authors"
You will have to configure the linked server to the names instance, type a
name that is without dashes. I you dont wanna do it, try to set up your
query to use brackets around parts of the name which could be understood as
special charcters or reserved words.
If the linked server is installed properly the authentification will work
like a charme (even the evil dashes are included, because you wont see them
anymore)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
news:%23LsOj6cUFHA.2172@.tk2msftngp13.phx.gbl...
> sql2k sp3
> Im doing some testing on my local pc. The name of my pc happens to have
> "-" 's in it. I have two instances of sql on it. So lets say I have the
> names of my SQL instances are:
> 1. aaa-aa-aaa
> 2.aaa-aa-aaa\Destination
> While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
> "select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because
> of the dashes. So I go into Client Network Utility and create an Alias of
> "Destination". Then when I try the revised query "select * from
> destination.pubs.dbo.authors" I get:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
> returned 0x80004005: ].
> So whats the dealio?
> TIA, ChrisR
>
|||Create a stored procedure with servername, loginuser, login_password and an
alias to call the server(which can be null). Then write a small portion to
drop link server if it exist:
IF EXISTS (SELECT srvname
FROM master.dbo.sysservers (NOLOCK)
WHERE srvname = @.p_alias)
BEGIN
EXEC master.dbo.sp_droplinkedsrvlogin @.p_alias, null
EXEC master.dbo.sp_dropserver @.p_alias
Then make sure you have declare your variable to excute dynamic sql statement;
DECLARE @.v_sql VARCHAR(500)
And then use this peace to actually link the server.
/* Add linked server */
SELECT @.v_sql = 'EXEC master.dbo.sp_addlinkedserver ' +
'@.server = ''' + @.p_alias + ''', ' +
'@.srvproduct = ''SQLServer OLEDB Provider'', ' +
'@.provider = ''SQLOLEDB'', ' +
'@.datasrc = ''' + @.p_servername + ''' ' +
'EXEC master.dbo.sp_serveroption ''' + @.p_alias + ''', ''rpc
out'', ''TRUE'' '
EXEC(@.v_sql)
/* Add login for linked server */
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname = @.p_alias,
@.useself = 'false',
@.rmtuser = @.p_user,
@.rmtpassword = @.p_pw
GO
This is the easy way to do it using TSQL and all credit for this one has to
go to my mentor.
Jared
Now you can link a server in a different way but this script just does all
the work for you.
Hope this helps.
"ChrisR" wrote:

> sql2k sp3
> Im doing some testing on my local pc. The name of my pc happens to have "-"
> 's in it. I have two instances of sql on it. So lets say I have the names of
> my SQL instances are:
> 1. aaa-aa-aaa
> 2.aaa-aa-aaa\Destination
> While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
> "select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because of
> the dashes. So I go into Client Network Utility and create an Alias of
> "Destination". Then when I try the revised query "select * from
> destination.pubs.dbo.authors" I get:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
> returned 0x80004005: ].
> So whats the dealio?
> TIA, ChrisR
>
>
|||First, I create the Alias in the Client Network Utility. Then I:
declare @.p_alias varchar(128)
DECLARE @.v_sql VARCHAR(500)
set @.p_alias = 'LSTestDest'
SeT @.v_sql = 'EXEC master.dbo.sp_addlinkedserver ' +
'@.server = ''' + @.p_alias + ''', ' +
'@.srvproduct = ''SQLServer OLEDB Provider'', ' +
'@.provider = ''SQLOLEDB'' ' +
'EXEC master.dbo.sp_serveroption ''' + @.p_alias + ''', ''rpc
out'', ''TRUE'' '
EXEC(@.v_sql)
Thanks Joseph this fixed it, but I don't understand why? I don't see how
this would be adding a linked server any differently than I did in EM?
"JosephPruiett" <JosephPruiett@.discussions.microsoft.com> wrote in message
news:81DF5536-7CD4-473C-9A71-A0732BDAE2E6@.microsoft.com...[vbcol=seagreen]
> Create a stored procedure with servername, loginuser, login_password and
> an
> alias to call the server(which can be null). Then write a small portion
> to
> drop link server if it exist:
> IF EXISTS (SELECT srvname
> FROM master.dbo.sysservers (NOLOCK)
> WHERE srvname = @.p_alias)
> BEGIN
> EXEC master.dbo.sp_droplinkedsrvlogin @.p_alias, null
> EXEC master.dbo.sp_dropserver @.p_alias
> Then make sure you have declare your variable to excute dynamic sql
> statement;
> DECLARE @.v_sql VARCHAR(500)
> And then use this peace to actually link the server.
> /* Add linked server */
> SELECT @.v_sql = 'EXEC master.dbo.sp_addlinkedserver ' +
> '@.server = ''' + @.p_alias + ''', ' +
> '@.srvproduct = ''SQLServer OLEDB Provider'', ' +
> '@.provider = ''SQLOLEDB'', ' +
> '@.datasrc = ''' + @.p_servername + ''' ' +
> 'EXEC master.dbo.sp_serveroption ''' + @.p_alias + ''',
> ''rpc
> out'', ''TRUE'' '
> EXEC(@.v_sql)
> /* Add login for linked server */
> EXEC master.dbo.sp_addlinkedsrvlogin
> @.rmtsrvname = @.p_alias,
> @.useself = 'false',
> @.rmtuser = @.p_user,
> @.rmtpassword = @.p_pw
> GO
> This is the easy way to do it using TSQL and all credit for this one has
> to
> go to my mentor.
> Jared
> Now you can link a server in a different way but this script just does all
> the work for you.
> Hope this helps.
>
> "ChrisR" wrote:

linked server for a named instance

sql2k sp3
Im doing some testing on my local pc. The name of my pc happens to have "-"
's in it. I have two instances of sql on it. So lets say I have the names of
my SQL instances are:
1. aaa-aa-aaa
2.aaa-aa-aaa\Destination
While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
"select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because of
the dashes. So I go into Client Network Utility and create an Alias of
"Destination". Then when I try the revised query "select * from
destination.pubs.dbo.authors" I get:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid authorization specification]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
So whats the dealio?
TIA, ChrisR"select * from aaa-aa-aaa\Destination.pubs.dbo.authors"
You will have to configure the linked server to the names instance, type a
name that is without dashes. I you don´t wanna do it, try to set up your
query to use brackets around parts of the name which could be understood as
special charcters or reserved words.
If the linked server is installed properly the authentification will work
like a charme (even the evil dashes are included, because you wont see them
anymore)
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
news:%23LsOj6cUFHA.2172@.tk2msftngp13.phx.gbl...
> sql2k sp3
> Im doing some testing on my local pc. The name of my pc happens to have
> "-" 's in it. I have two instances of sql on it. So lets say I have the
> names of my SQL instances are:
> 1. aaa-aa-aaa
> 2.aaa-aa-aaa\Destination
> While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
> "select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because
> of the dashes. So I go into Client Network Utility and create an Alias of
> "Destination". Then when I try the revised query "select * from
> destination.pubs.dbo.authors" I get:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
> returned 0x80004005: ].
> So whats the dealio?
> TIA, ChrisR
>|||Create a stored procedure with servername, loginuser, login_password and an
alias to call the server(which can be null). Then write a small portion to
drop link server if it exist:
IF EXISTS (SELECT srvname
FROM master.dbo.sysservers (NOLOCK)
WHERE srvname = @.p_alias)
BEGIN
EXEC master.dbo.sp_droplinkedsrvlogin @.p_alias, null
EXEC master.dbo.sp_dropserver @.p_alias
Then make sure you have declare your variable to excute dynamic sql statement;
DECLARE @.v_sql VARCHAR(500)
And then use this peace to actually link the server.
/* Add linked server */
SELECT @.v_sql = 'EXEC master.dbo.sp_addlinkedserver ' +
'@.server = ''' + @.p_alias + ''', ' +
'@.srvproduct = ''SQLServer OLEDB Provider'', ' +
'@.provider = ''SQLOLEDB'', ' +
'@.datasrc = ''' + @.p_servername + ''' ' +
'EXEC master.dbo.sp_serveroption ''' + @.p_alias + ''', ''rpc
out'', ''TRUE'' '
EXEC(@.v_sql)
/* Add login for linked server */
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname = @.p_alias,
@.useself = 'false',
@.rmtuser = @.p_user,
@.rmtpassword = @.p_pw
GO
This is the easy way to do it using TSQL and all credit for this one has to
go to my mentor.
Jared
Now you can link a server in a different way but this script just does all
the work for you.
Hope this helps.
"ChrisR" wrote:
> sql2k sp3
> Im doing some testing on my local pc. The name of my pc happens to have "-"
> 's in it. I have two instances of sql on it. So lets say I have the names of
> my SQL instances are:
> 1. aaa-aa-aaa
> 2.aaa-aa-aaa\Destination
> While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
> "select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because of
> the dashes. So I go into Client Network Utility and create an Alias of
> "Destination". Then when I try the revised query "select * from
> destination.pubs.dbo.authors" I get:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
> returned 0x80004005: ].
> So whats the dealio?
> TIA, ChrisR
>
>

linked server for a named instance

sql2k sp3
Im doing some testing on my local pc. The name of my pc happens to have "-"
's in it. I have two instances of sql on it. So lets say I have the names of
my SQL instances are:
1. aaa-aa-aaa
2.aaa-aa-aaa\Destination
While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
"select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because of
the dashes. So I go into Client Network Utility and create an Alias of
"Destination". Then when I try the revised query "select * from
destination.pubs.dbo.authors" I get:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid authorization specification]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
So whats the dealio?
TIA, ChrisR"select * from aaa-aa-aaa\Destination.pubs.dbo.authors"
You will have to configure the linked server to the names instance, type a
name that is without dashes. I you dont wanna do it, try to set up your
query to use brackets around parts of the name which could be understood as
special charcters or reserved words.
If the linked server is installed properly the authentification will work
like a charme (even the evil dashes are included, because you wont see them
anymore)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
news:%23LsOj6cUFHA.2172@.tk2msftngp13.phx.gbl...
> sql2k sp3
> Im doing some testing on my local pc. The name of my pc happens to have
> "-" 's in it. I have two instances of sql on it. So lets say I have the
> names of my SQL instances are:
> 1. aaa-aa-aaa
> 2.aaa-aa-aaa\Destination
> While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
> "select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because
> of the dashes. So I go into Client Network Utility and create an Alias of
> "Destination". Then when I try the revised query "select * from
> destination.pubs.dbo.authors" I get:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification
]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initiali
ze
> returned 0x80004005: ].
> So whats the dealio?
> TIA, ChrisR
>|||Create a stored procedure with servername, loginuser, login_password and an
alias to call the server(which can be null). Then write a small portion t
o
drop link server if it exist:
IF EXISTS (SELECT srvname
FROM master.dbo.sysservers (NOLOCK)
WHERE srvname = @.p_alias)
BEGIN
EXEC master.dbo.sp_droplinkedsrvlogin @.p_alias, null
EXEC master.dbo.sp_dropserver @.p_alias
Then make sure you have declare your variable to excute dynamic sql statemen
t;
DECLARE @.v_sql VARCHAR(500)
And then use this peace to actually link the server.
/* Add linked server */
SELECT @.v_sql = 'EXEC master.dbo.sp_addlinkedserver ' +
'@.server = ''' + @.p_alias + ''', ' +
'@.srvproduct = ''SQLServer OLEDB Provider'', ' +
'@.provider = ''SQLOLEDB'', ' +
'@.datasrc = ''' + @.p_servername + ''' ' +
'EXEC master.dbo.sp_serveroption ''' + @.p_alias + ''', ''rpc
out'', ''TRUE'' '
EXEC(@.v_sql)
/* Add login for linked server */
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname = @.p_alias,
@.useself = 'false',
@.rmtuser = @.p_user,
@.rmtpassword = @.p_pw
GO
This is the easy way to do it using TSQL and all credit for this one has to
go to my mentor.
Jared
Now you can link a server in a different way but this script just does all
the work for you.
Hope this helps.
"ChrisR" wrote:

> sql2k sp3
> Im doing some testing on my local pc. The name of my pc happens to have "-
"
> 's in it. I have two instances of sql on it. So lets say I have the names
of
> my SQL instances are:
> 1. aaa-aa-aaa
> 2.aaa-aa-aaa\Destination
> While on aaa-aa-aaa, I want to query aaa-aa-aaa\Destination. Well to do a
> "select * from aaa-aa-aaa\Destination.pubs.dbo.authors" wont work because
of
> the dashes. So I go into Client Network Utility and create an Alias of
> "Destination". Then when I try the revised query "select * from
> destination.pubs.dbo.authors" I get:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification
]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initiali
ze
> returned 0x80004005: ].
> So whats the dealio?
> TIA, ChrisR
>
>|||First, I create the Alias in the Client Network Utility. Then I:
declare @.p_alias varchar(128)
DECLARE @.v_sql VARCHAR(500)
set @.p_alias = 'LSTestDest'
SeT @.v_sql = 'EXEC master.dbo.sp_addlinkedserver ' +
'@.server = ''' + @.p_alias + ''', ' +
'@.srvproduct = ''SQLServer OLEDB Provider'', ' +
'@.provider = ''SQLOLEDB'' ' +
'EXEC master.dbo.sp_serveroption ''' + @.p_alias + ''', ''rpc
out'', ''TRUE'' '
EXEC(@.v_sql)
Thanks Joseph this fixed it, but I don't understand why? I don't see how
this would be adding a linked server any differently than I did in EM?
"JosephPruiett" <JosephPruiett@.discussions.microsoft.com> wrote in message
news:81DF5536-7CD4-473C-9A71-A0732BDAE2E6@.microsoft.com...[vbcol=seagreen]
> Create a stored procedure with servername, loginuser, login_password and
> an
> alias to call the server(which can be null). Then write a small portion
> to
> drop link server if it exist:
> IF EXISTS (SELECT srvname
> FROM master.dbo.sysservers (NOLOCK)
> WHERE srvname = @.p_alias)
> BEGIN
> EXEC master.dbo.sp_droplinkedsrvlogin @.p_alias, null
> EXEC master.dbo.sp_dropserver @.p_alias
> Then make sure you have declare your variable to excute dynamic sql
> statement;
> DECLARE @.v_sql VARCHAR(500)
> And then use this peace to actually link the server.
> /* Add linked server */
> SELECT @.v_sql = 'EXEC master.dbo.sp_addlinkedserver ' +
> '@.server = ''' + @.p_alias + ''', ' +
> '@.srvproduct = ''SQLServer OLEDB Provider'', ' +
> '@.provider = ''SQLOLEDB'', ' +
> '@.datasrc = ''' + @.p_servername + ''' ' +
> 'EXEC master.dbo.sp_serveroption ''' + @.p_alias + ''',
> ''rpc
> out'', ''TRUE'' '
> EXEC(@.v_sql)
> /* Add login for linked server */
> EXEC master.dbo.sp_addlinkedsrvlogin
> @.rmtsrvname = @.p_alias,
> @.useself = 'false',
> @.rmtuser = @.p_user,
> @.rmtpassword = @.p_pw
> GO
> This is the easy way to do it using TSQL and all credit for this one has
> to
> go to my mentor.
> Jared
> Now you can link a server in a different way but this script just does all
> the work for you.
> Hope this helps.
>
> "ChrisR" wrote:
>

Linked Server error

I have created a linked server (LAB) to an Access 2K DB on my local drive.
From within a DB on the SQL server I started to create a view, and using the
syntax:
SELECT * FROM LAB...tblName tblName_1
It displays all the required data.
When I attempt to save the view though, then I get problems. I receive an
error message:
ODBC error:[Microsoft][ODBC SQL Server Driver][SQL Server]Could not start a
transaction OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB returned message:
Neither the isolation level nor a strengthening of it is supported.]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace[OLE/DB
Provider 'Microsoft.Jet.4.0' ITransactionLoca StartTransaction returned
0x8004d008: ISOLEVEL=1048576].
I then placed the SELECT statement in a SProc, and received the following
error:
Error 7392: Could Not start a transaction for OLE DB provider
'Microsoft.Jet.OLEDB.4.0'.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.4.0'
ITransactionLocal::StartTransaction returned 0x8004d008: ISOLEVEL=1048576].
Setting the Isolation level as the first statement in the SProc made no
difference.
Anyone have any idea where I have gone wrong? Either I didn't set the Linked
Server up correctly, or I have missed something very fundamental.
Thanks in advanced for any help with this one,
TonyWhy don't you create Access from inside Access and create SQL Proc from SQL
side?
GX
"Logicalman" <tony9scott45us@.com7cast.net34> wrote in message
news:16C30F75-3662-49DD-BA57-28A07A39DC19@.microsoft.com...
>I have created a linked server (LAB) to an Access 2K DB on my local drive.
> From within a DB on the SQL server I started to create a view, and using
> the
> syntax:
> SELECT * FROM LAB...tblName tblName_1
> It displays all the required data.
> When I attempt to save the view though, then I get problems. I receive an
> error message:
> ODBC error:[Microsoft][ODBC SQL Server Driver][SQL Server]Could not start
> a
> transaction OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB returned message:
> Neither the isolation level nor a strengthening of it is supported.]
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace[OLE/DB
> Provider 'Microsoft.Jet.4.0' ITransactionLoca StartTransaction returned
> 0x8004d008: ISOLEVEL=1048576].
> I then placed the SELECT statement in a SProc, and received the following
> error:
> Error 7392: Could Not start a transaction for OLE DB provider
> 'Microsoft.Jet.OLEDB.4.0'.
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.4.0'
> ITransactionLocal::StartTransaction returned 0x8004d008:
> ISOLEVEL=1048576].
> Setting the Isolation level as the first statement in the SProc made no
> difference.
> Anyone have any idea where I have gone wrong? Either I didn't set the
> Linked
> Server up correctly, or I have missed something very fundamental.
> Thanks in advanced for any help with this one,
> Tony|||The access DB is a replicant, and, unfortunately, has some data errors in it
.
I originally started importing the data into SQL to complete some web work,
but due to the errors, felt thaat 'linked' tables would suffice for my needs
,
thus, a linked server method raised up and so I tried this. Once the errors
started it then intrigued me to find out why, and thus, I wrote the question
.
".Net Guy" wrote:

> Why don't you create Access from inside Access and create SQL Proc from SQ
L
> side?
> GX
>
> "Logicalman" <tony9scott45us@.com7cast.net34> wrote in message
> news:16C30F75-3662-49DD-BA57-28A07A39DC19@.microsoft.com...
>
>

Linked server DTS issue

Hi need help, I am using Linked server to connect to a oracle server and transferring the data to the local tables, it is working fine if the join transaction is not selected, if we select it it fails with the following error: "MSDASQL was unable to run distributed transaction", the join transaction unit is required as I need to rollback the deletion of data from the tables in the first step of package. Any help will be appreciated. Transaction coordinator is running.My Oracle knowledge is pretty thin but I am betting the MSDTC does not work with Oracle. I would google it.

linked server can not connect using Named Pipes

Push Transactional Replication

Replication was running fine, we switched one of the servers in the local lan to named pipes.

We can still connect to the database with Query Analyzer (QA) and Enterprise Manager (EM) but replication failed.

We realized that the linked server on the distributer for that subscriber could not connect.

We switched back to TCP and all is well again.

We can issue a Net Use \\server\ipc$ with success so it seems the pipe is ok, and we can still connect with QA and EM.

Why would the protocol make a difference. Why can't we connect a linked server with Named Pipes when it connects fine with TCP, and why even though the linked server can not connect can swe till connect with QA or EM.

More info please:

What agent is failing, distribution agent? Is logreader/snapshot agent functioning properly?

Which machine was configured to use named pipes, distributor or subscriber?

Did you change this at the client level or server level?

When you use QA/EM, are you connecting locally or remotely?

What error are you getting?

Linked server and MDAC driver.

Hi,
I have developed a database on my local Win Xp machine, using Express
version of SQL server.
The database is using a linked server to an Oracle database. On the XP
machin I have used the "Microsoft OLE DB Provider for Oracle" as the driver
for the linked server.
Now I'm trying to port this version to full SQL server 2005 on a Windows
2003 Server, but it seems that the driver is not installed(only the Oracle
driver). On MSDN I read that MDAC is installed with the OS on windows 2003
server.
Anyone knows how to enable the Microsoft OLE DB Provider for Oracle on
Windows Server 2003?
Thanks!
D.
Hi
"Dahab" wrote:

> Hi,
> I have developed a database on my local Win Xp machine, using Express
> version of SQL server.
> The database is using a linked server to an Oracle database. On the XP
> machin I have used the "Microsoft OLE DB Provider for Oracle" as the driver
> for the linked server.
> Now I'm trying to port this version to full SQL server 2005 on a Windows
> 2003 Server, but it seems that the driver is not installed(only the Oracle
> driver). On MSDN I read that MDAC is installed with the OS on windows 2003
> server.
> Anyone knows how to enable the Microsoft OLE DB Provider for Oracle on
> Windows Server 2003?
> Thanks!
> D.
>
I would expect these to be part of the Connectivity Components which you can
add from the command prompt using the ADDLOCAL parameter.
John
|||Dahab wrote:
> Hi,
> I have developed a database on my local Win Xp machine, using Express
> version of SQL server.
> The database is using a linked server to an Oracle database. On the XP
> machin I have used the "Microsoft OLE DB Provider for Oracle" as the driver
> for the linked server.
> Now I'm trying to port this version to full SQL server 2005 on a Windows
> 2003 Server, but it seems that the driver is not installed(only the Oracle
> driver). On MSDN I read that MDAC is installed with the OS on windows 2003
> server.
> Anyone knows how to enable the Microsoft OLE DB Provider for Oracle on
> Windows Server 2003?
> Thanks!
> D.
>
Is it by any change a 64-bit installation you are using? The MSDAORA
provider doesn't exists on the 64-bit version. Instead you should use
the Oracle ODAC which is the OraOledb.Oracle provider. You should also
be aware that the MSDAORA hasn't been enhanced since Oracle 8 client and
there are a number of things that isn't supported in this provider.
You can take a look at the link below which is a document that gives a
pretty good overview over the various connectivity options.
http://msdn2.microsoft.com/en-us/library/bb332055.aspx
Regards
Steen Schlter Persson
Database Administrator / System Administrator
|||Hi Steen,
Yes it's a 64bit server.
So I have to figure out how to use the Oracle driver then. Gave it a try a
few days ago with no luck ...
Thanks very much Steen.
D.
""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:uKBKiD8iHHA.2272@.TK2MSFTNGP02.phx.gbl...
> Dahab wrote:
> Is it by any change a 64-bit installation you are using? The MSDAORA
> provider doesn't exists on the 64-bit version. Instead you should use the
> Oracle ODAC which is the OraOledb.Oracle provider. You should also be
> aware that the MSDAORA hasn't been enhanced since Oracle 8 client and
> there are a number of things that isn't supported in this provider.
> You can take a look at the link below which is a document that gives a
> pretty good overview over the various connectivity options.
> http://msdn2.microsoft.com/en-us/library/bb332055.aspx
> --
> Regards
> Steen Schlter Persson
> Database Administrator / System Administrator
|||Dahab wrote:
> Hi Steen,
> Yes it's a 64bit server.
> So I have to figure out how to use the Oracle driver then. Gave it a try a
> few days ago with no luck ...
> Thanks very much Steen.
> D.
It's not that big a problem to use the Oracle driver. You'll have to
download the ODAC (
http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html)
and then make the entries in the tnsnames.ora file. Then you can use the
OraOledb.Oracle provider for setting up your Linked Server.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

Linked server and MDAC driver.

Hi,
I have developed a database on my local Win Xp machine, using Express
version of SQL server.
The database is using a linked server to an Oracle database. On the XP
machin I have used the "Microsoft OLE DB Provider for Oracle" as the driver
for the linked server.
Now I'm trying to port this version to full SQL server 2005 on a Windows
2003 Server, but it seems that the driver is not installed(only the Oracle
driver). On MSDN I read that MDAC is installed with the OS on windows 2003
server.
Anyone knows how to enable the Microsoft OLE DB Provider for Oracle on
Windows Server 2003?
Thanks!
D.Hi
"Dahab" wrote:
> Hi,
> I have developed a database on my local Win Xp machine, using Express
> version of SQL server.
> The database is using a linked server to an Oracle database. On the XP
> machin I have used the "Microsoft OLE DB Provider for Oracle" as the driver
> for the linked server.
> Now I'm trying to port this version to full SQL server 2005 on a Windows
> 2003 Server, but it seems that the driver is not installed(only the Oracle
> driver). On MSDN I read that MDAC is installed with the OS on windows 2003
> server.
> Anyone knows how to enable the Microsoft OLE DB Provider for Oracle on
> Windows Server 2003?
> Thanks!
> D.
>
I would expect these to be part of the Connectivity Components which you can
add from the command prompt using the ADDLOCAL parameter.
John|||Dahab wrote:
> Hi,
> I have developed a database on my local Win Xp machine, using Express
> version of SQL server.
> The database is using a linked server to an Oracle database. On the XP
> machin I have used the "Microsoft OLE DB Provider for Oracle" as the driver
> for the linked server.
> Now I'm trying to port this version to full SQL server 2005 on a Windows
> 2003 Server, but it seems that the driver is not installed(only the Oracle
> driver). On MSDN I read that MDAC is installed with the OS on windows 2003
> server.
> Anyone knows how to enable the Microsoft OLE DB Provider for Oracle on
> Windows Server 2003?
> Thanks!
> D.
>
Is it by any change a 64-bit installation you are using? The MSDAORA
provider doesn't exists on the 64-bit version. Instead you should use
the Oracle ODAC which is the OraOledb.Oracle provider. You should also
be aware that the MSDAORA hasn't been enhanced since Oracle 8 client and
there are a number of things that isn't supported in this provider.
You can take a look at the link below which is a document that gives a
pretty good overview over the various connectivity options.
http://msdn2.microsoft.com/en-us/library/bb332055.aspx
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Hi Steen,
Yes it's a 64bit server.
So I have to figure out how to use the Oracle driver then. Gave it a try a
few days ago with no luck :(...
Thanks very much Steen.
D.
""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:uKBKiD8iHHA.2272@.TK2MSFTNGP02.phx.gbl...
> Dahab wrote:
>> Hi,
>> I have developed a database on my local Win Xp machine, using Express
>> version of SQL server.
>> The database is using a linked server to an Oracle database. On the XP
>> machin I have used the "Microsoft OLE DB Provider for Oracle" as the
>> driver for the linked server.
>> Now I'm trying to port this version to full SQL server 2005 on a Windows
>> 2003 Server, but it seems that the driver is not installed(only the
>> Oracle driver). On MSDN I read that MDAC is installed with the OS on
>> windows 2003 server.
>> Anyone knows how to enable the Microsoft OLE DB Provider for Oracle on
>> Windows Server 2003?
>> Thanks!
>> D.
> Is it by any change a 64-bit installation you are using? The MSDAORA
> provider doesn't exists on the 64-bit version. Instead you should use the
> Oracle ODAC which is the OraOledb.Oracle provider. You should also be
> aware that the MSDAORA hasn't been enhanced since Oracle 8 client and
> there are a number of things that isn't supported in this provider.
> You can take a look at the link below which is a document that gives a
> pretty good overview over the various connectivity options.
> http://msdn2.microsoft.com/en-us/library/bb332055.aspx
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator|||Dahab wrote:
> Hi Steen,
> Yes it's a 64bit server.
> So I have to figure out how to use the Oracle driver then. Gave it a try a
> few days ago with no luck :(...
> Thanks very much Steen.
> D.
It's not that big a problem to use the Oracle driver. You'll have to
download the ODAC (
http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html)
and then make the entries in the tnsnames.ora file. Then you can use the
OraOledb.Oracle provider for setting up your Linked Server.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Linked server and MDAC driver.

Hi,
I have developed a database on my local Win Xp machine, using Express
version of SQL server.
The database is using a linked server to an Oracle database. On the XP
machin I have used the "Microsoft OLE DB Provider for Oracle" as the driver
for the linked server.
Now I'm trying to port this version to full SQL server 2005 on a Windows
2003 Server, but it seems that the driver is not installed(only the Oracle
driver). On MSDN I read that MDAC is installed with the OS on windows 2003
server.
Anyone knows how to enable the Microsoft OLE DB Provider for Oracle on
Windows Server 2003?
Thanks!
D.Hi
"Dahab" wrote:

> Hi,
> I have developed a database on my local Win Xp machine, using Express
> version of SQL server.
> The database is using a linked server to an Oracle database. On the XP
> machin I have used the "Microsoft OLE DB Provider for Oracle" as the drive
r
> for the linked server.
> Now I'm trying to port this version to full SQL server 2005 on a Windows
> 2003 Server, but it seems that the driver is not installed(only the Oracle
> driver). On MSDN I read that MDAC is installed with the OS on windows 2003
> server.
> Anyone knows how to enable the Microsoft OLE DB Provider for Oracle on
> Windows Server 2003?
> Thanks!
> D.
>
I would expect these to be part of the Connectivity Components which you can
add from the command prompt using the ADDLOCAL parameter.
John|||Dahab wrote:
> Hi,
> I have developed a database on my local Win Xp machine, using Express
> version of SQL server.
> The database is using a linked server to an Oracle database. On the XP
> machin I have used the "Microsoft OLE DB Provider for Oracle" as the drive
r
> for the linked server.
> Now I'm trying to port this version to full SQL server 2005 on a Windows
> 2003 Server, but it seems that the driver is not installed(only the Oracle
> driver). On MSDN I read that MDAC is installed with the OS on windows 2003
> server.
> Anyone knows how to enable the Microsoft OLE DB Provider for Oracle on
> Windows Server 2003?
> Thanks!
> D.
>
Is it by any change a 64-bit installation you are using? The MSDAORA
provider doesn't exists on the 64-bit version. Instead you should use
the Oracle ODAC which is the OraOledb.Oracle provider. You should also
be aware that the MSDAORA hasn't been enhanced since Oracle 8 client and
there are a number of things that isn't supported in this provider.
You can take a look at the link below which is a document that gives a
pretty good overview over the various connectivity options.
http://msdn2.microsoft.com/en-us/library/bb332055.aspx
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||Hi Steen,
Yes it's a 64bit server.
So I have to figure out how to use the Oracle driver then. Gave it a try a
few days ago with no luck ...
Thanks very much Steen.
D.
""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:uKBKiD8iHHA.2272@.TK2MSFTNGP02.phx.gbl...
> Dahab wrote:
> Is it by any change a 64-bit installation you are using? The MSDAORA
> provider doesn't exists on the 64-bit version. Instead you should use the
> Oracle ODAC which is the OraOledb.Oracle provider. You should also be
> aware that the MSDAORA hasn't been enhanced since Oracle 8 client and
> there are a number of things that isn't supported in this provider.
> You can take a look at the link below which is a document that gives a
> pretty good overview over the various connectivity options.
> http://msdn2.microsoft.com/en-us/library/bb332055.aspx
> --
> Regards
> Steen Schlter Persson
> Database Administrator / System Administrator|||Dahab wrote:
> Hi Steen,
> Yes it's a 64bit server.
> So I have to figure out how to use the Oracle driver then. Gave it a try a
> few days ago with no luck ...
> Thanks very much Steen.
> D.
It's not that big a problem to use the Oracle driver. You'll have to
download the ODAC (
[url]http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html[/u
rl])
and then make the entries in the tnsnames.ora file. Then you can use the
OraOledb.Oracle provider for setting up your Linked Server.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

Linked Server : SQL Server 2000

Hi,
When I try to update a table on my local server by
referring to another table in the the linked server I get
a message "Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]."
Any ideas would be appreciated as I am running short of
time.
Regards,
VenugopalVenugopal,
Is the Distributed Transaction Coordinator running? On both servers? And
is it configured according to:
http://support.microsoft.com/defaul...kb;en-us;329332
A discussion of linked servers can be found in the article "Linked Servers
and Distributed Transactions"
http://tinyurl.com/2xnsv
Russell Fields
"Venugopal" <vemuriv@.hotmail.com> wrote in message
news:9e1c01c3eac1$61eb1590$a601280a@.phx.gbl...

quote:

---

> Hi,
> When I try to update a table on my local server by
> referring to another table in the the linked server I get
> a message "Server: Msg 7391, Level 16, State 1, Line 1
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]."
> Any ideas would be appreciated as I am running short of
> time.
> Regards,
> Venugopal

---

Linked server & transaction connection

Hi all,
I've a local server and a linked server, which I need to update both
servers' tables.
But I encountered the error "Can't start more transaction in a session'.
Following is the structure of the code in the program:
----
Begin Transaction
Insert local_server_A.table
Update linked_server_B.table
Commit Transaction
If Error
Rollback Transaction
I know the problem is the transaction control can only exist in one
connection.
The linked server is treated as another connection.
Is there any way to workaround for multi connection in a transaction control
?
Thanks in advance,
KristeHi
Read about distribution transactions in the BOL.
"whiteegg" <whiteegg@.discussions.microsoft.com> wrote in message
news:AA6E6C4B-5C3F-476E-AACE-D8AE2EC86388@.microsoft.com...
> Hi all,
> I've a local server and a linked server, which I need to update both
> servers' tables.
> But I encountered the error "Can't start more transaction in a session'.
> Following is the structure of the code in the program:
> ----
> Begin Transaction
> Insert local_server_A.table
> Update linked_server_B.table
> Commit Transaction
> If Error
> Rollback Transaction
> I know the problem is the transaction control can only exist in one
> connection.
> The linked server is treated as another connection.
> Is there any way to workaround for multi connection in a transaction
control?
> Thanks in advance,
> Kriste|||Sorry, I read thr' BOL on MS DTC and replication etc. but I still can't find
solution how can this be applied just between linked servers. Can you provid
e
more info. on this?
"Uri Dimant" wrote:

> Hi
> Read about distribution transactions in the BOL.
>
> "whiteegg" <whiteegg@.discussions.microsoft.com> wrote in message
> news:AA6E6C4B-5C3F-476E-AACE-D8AE2EC86388@.microsoft.com...
> control?
>
>

Linked Server - Excel via UNC

Is it possible to create a linked server to an Excel spreadsheet using a UNC?
I have no problems doing it with a file on the local server, but am having
trouble when using a UNC, which is what I really need to do. If I use no
security context, I get an upspecified error. If I specify a remote login, I
get an authentication error. I have tried domain\username and password and
without the domain name. All the articles I find on the internet only refer
to local paths.
Thanks!
Buddy
Hi,
I tested a Linked Server to Excel file on a Network share with UNC
(\\testmachine\testshare\testfile.xls) successfully. On the Security tab,
I did not enter any Logins in the Login Mapping Grid, at the bottom
selected the 'Be made without using a security context' option. It worked
fine when I had permissions on theNetwork share to Read the XLS file. when
the permissions on the network share were removed, I got an error.
I suggest that you check the following:
1. SQL Server Startup Account - Does that have Network permissions on the
share where Excel file resides
2. Current User Windows Account - Does that have Network permissions on
the share where Excel file resides
After checking these two points if you still get an error, Please send me
the exact error message that you are receiving.
Meanwhile I will compile some documentation on this issue (Security Context
used by the Linked Server to Excel Files) and keep you posted on this.
Thanks
Soma Sekhara Reddy