Linked server - self reference

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Linked server - self reference

Author

Topic

anand_d
Starting Member

9 Posts

Posted - 2006-07-04 : 05:36:30

how do i use the openquery to reference the current server as a linked server. I need to do without registering a linked server for the current one.

Is it possible to do this or is there any workaround for this.

~Anand

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-04 : 06:06:29

OpenQuery, OpenRowset or OpenDataSource can do this for you. Read about them in Book Online.

But why would you?

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-07-04 : 06:14:29

[code]
if exists (select * from master..sysservers where srvname = 'loopback')
exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
@srvproduct = N'',
@provider = N'SQLOLEDB',
@datasrc = @@servername
go

select * into #t from openquery(loopback, 'exec sp_who')
select * from #t
drop table #t
go

[/code]

Go with the flow & have fun! Else fight the flow

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-04 : 06:15:04

If you want to use a linked server to access then you have to create the linked server.

You can use openrowset which allows to to specify the connection details in the statement.
Openquery requires a linked server.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources