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: