Wednesday, March 28, 2012

How odd, strange behaviour

Dear folks,
I've got the following issue and I can't work out:
This statement fails:
SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
FROM srvdesa2.NOM.DBO.G61DIET WITH (NOLOCK)
Servidor: mensaje 7377, nivel 16, estado 1, l_nea 2
Cannot specify an index or locking hint for a remote data source.
But this one, ends successful and pull up the data:
SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
FROM srvdesa2.NOM.DBO.G61DIET NOLOCK
Does anyone ever used or experienced any problem with that? are we talking
about the same or different goal with the different way? I mean, nolock, wit
h
nolock...
Thanks for any input and regards,You are defining just an alias here:
SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
FROM srvdesa2.NOM.DBO.G61DIET NOLOCK
Something like SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
FROM srvdesa2.NOM.DBO.G61DIET SomeAlias
HTH, Jens Suessmeyer.|||sorry Jens, I don't understand you, what do you mean with that?
"Jens" wrote:

> You are defining just an alias here:
> SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
> FROM srvdesa2.NOM.DBO.G61DIET NOLOCK
>
> Something like SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
> FROM srvdesa2.NOM.DBO.G61DIET SomeAlias
>
> HTH, Jens Suessmeyer.
>|||Defining aliases for the tables often helps to clear queries due to
long names or names where you don=B4t know what the data is all about,
something like:
SELECT Address.*
FROM T3455NBGA Address --Normally this is an Adress table but you
don=B4t know from the name, so we give it an alias in the query
INNER JOIN SomeOtherTable
ON SOmeotherTable.ID =3D Address.ID
HTH, Jens Suessmeyer.|||Thanks in advance, anyway it doesn't works.
"Jens" wrote:

> Defining aliases for the tables often helps to clear queries due to
> long names or names where you don′t know what the data is all about,
> something like:
> SELECT Address.*
> FROM T3455NBGA Address --Normally this is an Adress table but you
> don′t know from the name, so we give it an alias in the query
> INNER JOIN SomeOtherTable
> ON SOmeotherTable.ID = Address.ID
> HTH, Jens Suessmeyer.
>|||> But this one, ends successful and pull up the data:
> SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
> FROM srvdesa2.NOM.DBO.G61DIET NOLOCK
So will this:

> SELECT NOLOCK.G61Num AS Curso,NOLOCK.G61FORM AS Dni,'F' AS Tipo
> FROM srvdesa2.NOM.DBO.G61DIET NOLOCK
And this:

> SELECT bozo.G61Num AS Curso,bozo.G61FORM AS Dni,'F' AS Tipo
> FROM srvdesa2.NOM.DBO.G61DIET bozo
You would think that SQL Server would treat 'NOLOCK' as a reserved word, but
it doesn't.
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:95D4CF5F-8E45-44E4-9F35-95F38A5286DA@.microsoft.com...
> Dear folks,
> I've got the following issue and I can't work out:
> This statement fails:
> SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
> FROM srvdesa2.NOM.DBO.G61DIET WITH (NOLOCK)
>
> Servidor: mensaje 7377, nivel 16, estado 1, lnea 2
> Cannot specify an index or locking hint for a remote data source.
> But this one, ends successful and pull up the data:
> SELECT G61Num AS Curso,G61FORM AS Dni,'F' AS Tipo
> FROM srvdesa2.NOM.DBO.G61DIET NOLOCK
> Does anyone ever used or experienced any problem with that? are we talking
> about the same or different goal with the different way? I mean, nolock,
> with
> nolock...
> Thanks for any input and regards,|||Of course not. Because of how you placed NOLOCK in your code, it is simply
being used as an alternative table name, instead of the NOLOCK action that
you are trying to produce. The query that is working is not using NOLOCK as
you intend, but rather is using the term as a reference to your table. The
query that is failing is using NOLOCK as you intended, but SQL Server does
not permit you to use it in that context.
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:5A50B17C-2705-4027-A5F6-24E025F0CD4F@.microsoft.com...
> Thanks in advance, anyway it doesn't works.
> "Jens" wrote:
>|||I should add that this is not your fault (IMO) since it is more than
reasonable to expect NOLOCK to be a reserved word. It is a simple syntax
error, and an honest mistake.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23PJDYbnJGHA.1728@.TK2MSFTNGP09.phx.gbl...
> Of course not. Because of how you placed NOLOCK in your code, it is
simply
> being used as an alternative table name, instead of the NOLOCK action that
> you are trying to produce. The query that is working is not using NOLOCK
as
> you intend, but rather is using the term as a reference to your table.
The
> query that is failing is using NOLOCK as you intended, but SQL Server does
> not permit you to use it in that context.

No comments:

Post a Comment