How to Setup Linked Server within Same SQL Server

Been wandering around the searches and cannot find a specific answer so hopefully someone can help me. Basic situation is I'm setting up an automatic transfering of data to a historical database that resides on the same SQL Server.

exec sp_dropserver 'linked1', 'droplogins'
exec sp_addlinkedserver 'linked1', 'SQL Server'
exec sp_setnetname 'linked1', <Databasename>
exec sp_addlinkedsrvlogin 'linked1', 'false', null, <user>, <password>

SET ANSI_NULLS ON
go
SET ANSI_WARNINGS ON
go
select * from openquery (linked1, 'select * from dbo.table')

I run this and it says it cannot find the instance...You cannot specify local server as a linked server.|||If you check the documentation for sp_addlinkedserver (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp), it looks like example 2 is almost exactly what you need. Just provide the machine name instead of machine\instance and you'll be in business!

-PatP|||Originally posted by rdjabarov
You cannot specify local server as a linked server. That's strange, it let me do it on my test machine.

-PatP|||What's the SP level on your test? I even decided to try it on one of my Prod servers (SP3a) and it failed just like on my Personal Edition (SP3a).|||My test machine runs at sp2, because many of the third-party applications that I support won't run properly under sp3a. That may well be the difference.

My home machines run at sp3a. I'll have to test it there when I get the chance.

-PatP|||Thank you for all of the replies. I got everything worked out without having to use the linkserver but the help will definitely help me at a later time. Thanks again

Originally posted by Pat Phelan
My test machine runs at sp2, because many of the third-party applications that I support won't run properly under sp3a. That may well be the difference.

My home machines run at sp3a. I'll have to test it there when I get the chance.

-PatP|||I was actually about to suggest, that if it's the same server then you don't need OPENQUERY, or a linked server for that matter. Just a cross-database query would do.