Enterprise Manager error when using WITH (NOLOCKS)

I have added WITH (NOLOCK) to a couple of views that were causing unecessary
blocking for MS Access end user reporting.
I can update edit and save the views fine with Query Analyzer, however when
I try and run the query from Enterprise Manager Design View I get this
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect Syntax
The views work fine in everywhere else. When in EM if I remove the WITH
(NOLOCK) I do not get the error.
It looks as if the alias is causing the issue.
select * from MyTable WITH (NOLOCK) works
however Select * from MyTable mt WITH (NOLOCK) causes the error. But only in
EM not in query analyzer.
Is anyone else experiencing this?
"lurdan" wrote:

> I have added WITH (NOLOCK) to a couple of views that were causing unecessary
> blocking for MS Access end user reporting.
> I can update edit and save the views fine with Query Analyzer, however when
> I try and run the query from Enterprise Manager Design View I get this
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect Syntax
> The views work fine in everywhere else. When in EM if I remove the WITH
> (NOLOCK) I do not get the error.
>
|||I got the same result. When I tried to run the query in EM, the query tool
inverted the alias to after the table hint, which is bad syntax.
Russel Loski, MCSD.Net
"lurdan" wrote:
[vbcol=seagreen]
> It looks as if the alias is causing the issue.
> select * from MyTable WITH (NOLOCK) works
> however Select * from MyTable mt WITH (NOLOCK) causes the error. But only in
> EM not in query analyzer.
> Is anyone else experiencing this?
> "lurdan" wrote:

Enterprise Manager error when using WITH (NOLOCKS)

I have added WITH (NOLOCK) to a couple of views that were causing unecessary
blocking for MS Access end user reporting.
I can update edit and save the views fine with Query Analyzer, however when
I try and run the query from Enterprise Manager Design View I get this
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
Syntax
The views work fine in everywhere else. When in EM if I remove the WITH
(NOLOCK) I do not get the error.It looks as if the alias is causing the issue.
select * from MyTable WITH (NOLOCK) works
however Select * from MyTable mt WITH (NOLOCK) causes the error. But only in
EM not in query analyzer.
Is anyone else experiencing this?
"lurdan" wrote:

> I have added WITH (NOLOCK) to a couple of views that were causing unecessa
ry
> blocking for MS Access end user reporting.
> I can update edit and save the views fine with Query Analyzer, however whe
n
> I try and run the query from Enterprise Manager Design View I get this
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorre
ct Syntax
> The views work fine in everywhere else. When in EM if I remove the WITH
> (NOLOCK) I do not get the error.
>|||I got the same result. When I tried to run the query in EM, the query tool
inverted the alias to after the table hint, which is bad syntax.
--
Russel Loski, MCSD.Net
"lurdan" wrote:
[vbcol=seagreen]
> It looks as if the alias is causing the issue.
> select * from MyTable WITH (NOLOCK) works
> however Select * from MyTable mt WITH (NOLOCK) causes the error. But only
in
> EM not in query analyzer.
> Is anyone else experiencing this?
> "lurdan" wrote:
>

Enterprise Manager causing lockout of user account on server

When I connect to the SQL Server remotely via enterprise manager as SA my
user account on the server ends up being locked out. Currently my work around
is to log on to the server via terminal service then run enterprise manager
from my terminal service session. I have no problems with my account
accessing enterprise manager through that method. I must point out that I
successfully connect as SA when I run enterprise manager on my desktop. Any
suggest would be most helpful.
I'm not sure how you are logging in on your desktop but it
could be related to the polling of the servers from
Enterprise Manager so you can try turning that off. In
Enterprise Manager, go to the menu to Tools, select Options.
On the General Tab, remove the check for Server State
Polling.
-Sue
On Wed, 2 Mar 2005 05:47:02 -0800, "James"
<James@.discussions.microsoft.com> wrote:

>When I connect to the SQL Server remotely via enterprise manager as SA my
>user account on the server ends up being locked out. Currently my work around
>is to log on to the server via terminal service then run enterprise manager
>from my terminal service session. I have no problems with my account
>accessing enterprise manager through that method. I must point out that I
>successfully connect as SA when I run enterprise manager on my desktop. Any
>suggest would be most helpful.

Enterprise Manager causing lockout of user account on server

When I connect to the SQL Server remotely via enterprise manager as SA my
user account on the server ends up being locked out. Currently my work aroun
d
is to log on to the server via terminal service then run enterprise manager
from my terminal service session. I have no problems with my account
accessing enterprise manager through that method. I must point out that I
successfully connect as SA when I run enterprise manager on my desktop. Any
suggest would be most helpful.I'm not sure how you are logging in on your desktop but it
could be related to the polling of the servers from
Enterprise Manager so you can try turning that off. In
Enterprise Manager, go to the menu to Tools, select Options.
On the General Tab, remove the check for Server State
Polling.
-Sue
On Wed, 2 Mar 2005 05:47:02 -0800, "James"
<James@.discussions.microsoft.com> wrote:

>When I connect to the SQL Server remotely via enterprise manager as SA my
>user account on the server ends up being locked out. Currently my work arou
nd
>is to log on to the server via terminal service then run enterprise manager
>from my terminal service session. I have no problems with my account
>accessing enterprise manager through that method. I must point out that I
>successfully connect as SA when I run enterprise manager on my desktop. Any
>suggest would be most helpful.

Enterprise Manager causing lockout of user account on server

When I connect to the SQL Server remotely via enterprise manager as SA my
user account on the server ends up being locked out. Currently my work around
is to log on to the server via terminal service then run enterprise manager
from my terminal service session. I have no problems with my account
accessing enterprise manager through that method. I must point out that I
successfully connect as SA when I run enterprise manager on my desktop. Any
suggest would be most helpful.I'm not sure how you are logging in on your desktop but it
could be related to the polling of the servers from
Enterprise Manager so you can try turning that off. In
Enterprise Manager, go to the menu to Tools, select Options.
On the General Tab, remove the check for Server State
Polling.
-Sue
On Wed, 2 Mar 2005 05:47:02 -0800, "James"
<James@.discussions.microsoft.com> wrote:
>When I connect to the SQL Server remotely via enterprise manager as SA my
>user account on the server ends up being locked out. Currently my work around
>is to log on to the server via terminal service then run enterprise manager
>from my terminal service session. I have no problems with my account
>accessing enterprise manager through that method. I must point out that I
>successfully connect as SA when I run enterprise manager on my desktop. Any
>suggest would be most helpful.

Enterprise Manager causing extensive network traffic

Does anybody have any idea what is happening here. On 3 out of five workstations when entering Enterprise Manager the network traffic jumps drastically to the point that the server needs to be rebooted. Any information would be greatly appreciated.
Thanks
NewbieTry Using the SQL Profiler that comes with SQL Server

This underused programme will list every command that hits the SQL Server in Real time.

Just run the default trace & see whats happening when a client logs on

GW|||I used profiler this morning, using the default trace and I also set it to log everything. It didn't log anything out of the ordinary, and there was nothing that coincided with the amount of traffic being generated.

Originally posted by GWilliy
Try Using the SQL Profiler that comes with SQL Server

This underused programme will list every command that hits the SQL Server in Real time.

Just run the default trace & see whats happening when a client logs on

GW|||OK

My next step would be to download a trial version of Commview 3 - install it onto the client.

This App is an excellent packet sniffer and will show you exactly any traffic that is happening & to which IP Address it is going to

You could try and decipher the packets with this tool if you are brave - at very least it will show how much traffic is generated on which port to where.

GW

Enterprise Manager causing blocking

Hi,
We recently had Problems with blockings on some tables.
Now i found that everytime I work in Enterprise Manager and
open up large tables where i need to scroll, EM seems to
lock this table as every update the cusomers do are blocked by
myself.
This really shouldn't happen.
What kind of Tool is this causing Problems while doing queries.
Has anyone similar experiences or some workarounds?
(exept using QueryAnalyzer)
Thanks
Mikepeppi911@.hotmail.com wrote:

> Hi,
> We recently had Problems with blockings on some tables.
> Now i found that everytime I work in Enterprise Manager and
> open up large tables where i need to scroll, EM seems to
> lock this table as every update the cusomers do are blocked by
> myself.
> This really shouldn't happen.
> What kind of Tool is this causing Problems while doing queries.
> Has anyone similar experiences or some workarounds?
> (exept using QueryAnalyzer)
> Thanks
> Mike
Enterprise Manager isn't a good way to edit and enter data. It is a
management tool, not an end-user application. Why would you want to
open and scroll through large tables? Never ever use EM like that in a
multi-user production environment.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi, thanks for answering.
Well users want Results the whole day, so i do special queries
and copy everything in Excel.
QA doesn't even copy the heads so i don't know which way other
that using EM to get the result in a proper way.
Arn't there other Tools?
Does the new Version 2005 do the same?
Greetings,
Mike|||xpeppi911@.hotmail.com wrote:
> Hi, thanks for answering.
> Well users want Results the whole day, so i do special queries
> and copy everything in Excel.
> QA doesn't even copy the heads so i don't know which way other
> that using EM to get the result in a proper way.
> Arn't there other Tools?
> Does the new Version 2005 do the same?
> Greetings,
> Mike
Some options:
In Excel select Data / Import External Data / New Database Query...
Use any one of many query / reporting or data integration tools.
Crystal Reports, Business Objects for example. Some of those will
surely support Excel formats.
Use DTS or SQL Server Integration Services to export in Excel format.
Write your own code or Excel macro to export in Excel format.
Share data by other means without using Excel. You could publish the
data to users through Reporting Services for example (they can still
paste data into Excel if they really want to). This would certainly be
my favoured option. Copy-and-paste is an inherently limited, costly and
inefficient way to share data.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||QA has secrets (that you can use to your advantage):
http://www.sqlservercentral.com/col...
yanalyzer.asp
ML
http://milambda.blogspot.com/

Enterprise Manager 2/ Me 0

Oi! Not that anyone will ever see this post again - but it was McAfee's
Entercept Agent causing the problem - the version we had apparently didn't
like comments (!) in stored procedures, and when it came across one, would
destabilize the server and cause it to crash (v 42.58)..... the latest
version (42.59) fixed the problem.
"B@.DJJ" wrote:

> Ahoy All!
> Say I come in, bright-eyed-and-bushy-tailed in the AM and say "Hey! I thi
nk
> I'll add a field to a table this Bright and Glorious morning!" so I fire u
p
> Enterprise Manager (v 2.0) with the SQL Enterprise Manager (v 8.0) (runnin
g
> on XP SP2 hitting a SQL Server 2000 SP4 DB running on W2k3 Server) and go
to
> design view, add my tiny little field (this time an int, default 0), click
> Apply, click Yes Please (actually OK), and then, lo and behold, I get this
> little gem:
> 17310 :
> SqlDumpExceptionHandler: Process 848 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> No further access via EM or QA at that point.
> So I stop the SQLService on the server, count to 10 and sprinkle a little
> pixie dust about, restart the SQLService, and voila! sometimes my field i
s
> in the table, sometimes it isn't, and if it isn't, I add it in, click Appl
y,
> click Yes Please, and double-viola! It works like a charm - then, for the
> rest of the day, all is well (can add fields, update sprocs, modify trigge
rs,
> you-name-it).
> Any thoughts and suggestions would be appreciated. Haven't tried it via Q
A,
> though, next time I think I'll give 'er a whirl.
> Thanks
>Simply ridiculous.
ML
http://milambda.blogspot.com/