specify [server\instance] in select statement

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 2005 Forums

Transact-SQL (2005)

specify [server\instance] in select statement

Author

Topic

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-03-11 : 20:36:40

Hi, what is the different when I execute

select count(*) from [servername\instance].database1.dbo.tablename(nolock)

and

select count(*) from database1.dbo.tablename (nolock)

for the first code, I will get error
Msg 4122, Level 16, State 1, Line 1
Remote table-valued function calls are not allowed.

How can I use nolock with sever name and instance specified in the select statement??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-11 : 21:10:34

According to what I saw when I googled your error message, you need to provide "WITH" with your locking hint in order to avoid the error.

... with (nolock)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-03-11 : 23:28:21

I tried to put

select count(*) from [servername\instance].database1.dbo.tablename with (nolock)

but still get the error...
Server: Msg 7377, Level 16, State 1, Line 1
Cannot specify an index or locking hint for a remote data source.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 02:37:24

Still?
This is a new error. If you haven't noticed, the error messages are different.

Have you tried creating a linked server?

E 12°55'05.25"
N 56°04'39.16"

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-03-12 : 05:10:37

quote:
Originally posted by Peso

Still?
This is a new error. If you haven't noticed, the error messages are different.

Have you tried creating a linked server?

E 12°55'05.25"
N 56°04'39.16"

YES, linked server is already there...but i still not able to execute it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 05:16:58

Use the linked server name, not the physical name, use the logical name.

If you named the linked server for MsSQL2005\Prod instance as MyServer, write

select count(*) from MyServer.database1.dbo.tablename

E 12°55'05.25"
N 56°04'39.16"

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-03-12 : 05:22:43

quote:
Originally posted by Peso

Use the linked server name, not the physical name, use the logical name.

If you named the linked server for MsSQL2005\Prod instance as MyServer, write

select count(*) from MyServer.database1.dbo.tablename

E 12°55'05.25"
N 56°04'39.16"

actually when I get the error, I tried to execute that locally through query analyzer, meaning that no involve any linked server. The error is still the same.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 05:34:14

Well, all your error descriptions posted yet indicates that you ARE running the query over a linked server.

Wether or not if you execute the query "locally" or "on the server" doesn't matter.
A linked server is a relationship between two physical servers.

I think you should read a little about what LINKED SERVER is. It seems to me you miss a piece of knowledge here.

E 12°55'05.25"
N 56°04'39.16"

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-03-12 : 09:03:04

Your error states exactly what the issue is - "Cannot specify an index or locking hint for a remote data source.". Using a query that runs on the source server against a table on the target server can not enforce the hint you specified (or any hint for that matter). That's because they are two different servers and have no way of controlling one another. If you want to do this, try looking into the EXECUTE and OPENQUERY statements. Both of these will allow you to pass a query through to be executed on the target server like so:

EXECUTE ('select count(*) from database1.dbo.tablename(nolock)') AT <linked server>

or

SELECT * FROM OPENQUERY(<linked server>, 'select count(*) from database1.dbo.tablename(nolock)')

jeroen.bakker
Starting Member

2 Posts

Posted - 2008-08-14 : 09:14:23

Or make a view at the remote server that only does a simple:
select * from tablename with (nolock)

And then do the remote call:

select count(*) from [servername\instance].database1.dbo.viewname

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources