Viewing Query In EM Is Blocking

Aug 9, 2007

EDIT: Oops in the title. I meant "EM" not "IE".

I'm on a Dev database (full copy). In EM (that is running on the Dev server) I run a view that I used to build the list of rows to be updated.

SQL 2000 Std SP3 on Win 2000 Server.

When I cut-n-Pasted the clause to my "Update" statement in Query Analyser and try to run it, to my surprised, it got hung up.

I looked in EM to see Locks, and to my surprise my EM View Display is blocking the Update! So; I press "Cancel" on my EM View display and, whammo, the update goes.

I tested and it's consistant and does not matter what column I'm updating.

Is this normal behaviour for EM?

EDIT: I tried the exact same scenerio, in the exact same EM and QA screens, but this time remoting over to the Production box (since it's midnight). The blocking did not occur. Weird!

View 3 Replies


ADVERTISEMENT

Viewing Database Via Query Analyzer And/or Enterprise Manager

Sep 20, 2004

We are running SQL 2000 SP3 on a Win2K server with multiple user databases. Our current problem is the ability of users to view the database objects via the native tools.

Example: User Bob
Bob has the following rights:
DB A – datareader/datawriter
DB B – datareader/datawriter
DB C – datareader
DB D – datareader
DB E – datareader/datawriter

The user has no builder rights or server roles.

In Enterprise Manager, the user can not see any databases. In Query Analyzer, the user can only see DB A, B, and C. Anyone seen this problem before and/or have suggestions?

Thanks in advance!

View 3 Replies View Related

Viewing Stored Produre Code From Query Analyser

Mar 17, 2004

Hi

Can anyone tell me the way to view stored produre code from query analyser. With sp_help I can only view input paramters
Cheers

View 4 Replies View Related

SQL Server 2008 :: How To Find History Of Blocking Batch / Query

Mar 4, 2015

I see some time email say blocking took place and but it get resolved itself. how to find the history of batch/query which blocked another SPID?

View 3 Replies View Related

SQL Server 2014 :: Query Causing Blocking / Locks Table For 1000 Seconds?

Feb 11, 2015

I have a sql snippet from a 3rd party application that will not complete its transaction. The SELECT statement executes but does not finish. Instead the statement just sits in AWAITING COMMAND for 1000 seconds then dies, thus killing the UPDATE statement that is supposed to follow.

The CROSS JOIN and CROSS APPLY seem suspect.

(
@p0 DATETIME,
@p1 INT,
@p2 INT,
@p3 NVARCHAR(4000),
@p4 INT,

[code]....

View 9 Replies View Related

Viewing SQL Currently Being Ran

Apr 2, 2008

Hi all,

I'm very new to SQL Server having previously worked with Oracle 10g for the last couple of years.

Anyway, in Oracle there is a view called V$SQL that provides information about SQL that is or has recently been executed. I'm trying to find a similar sort of functionality in SQL Server.

Basically, what I'd like to do is list all of the SQL statements that are running at a given moment. Or even better would be a way of listing all statements that have run in the last 10 minutes.

Appreciate any help for a newbie.

We're on SQL Server 2000 8.00.194

Cheers
Kloid

View 8 Replies View Related

Blocking

Aug 15, 2000

One sql transaction is in rollback state and optimization job is trying to restructure index on the same table that this transaction is using. As a result,
transaction is in 'blocking' state and optimization job is in 'blocked' state. 'Kill process' doesn't do anything. How can this situation be resolved?

View 1 Replies View Related

Blocking

Nov 26, 2001

Is it possible that blocking can occur between a select and update statements running at the same time on the same object??thanks for the reply!
Sheila.

View 1 Replies View Related

Self Blocking SP4

Nov 1, 2007

Anyone out there having "self blocking" issues since upgrading to SP4 on SQL 2000? Enabing trace flag 9059 has not helped, is this a parallelism issue ?

thanks,
Paul

View 7 Replies View Related

Blocking When Doing Bcp

Jan 6, 2004

Any ideas?

I execute a sproc from QA.
It interogates a folder:


Select @Command_String = 'Dir ' + @FilePath + '*.txt'
Insert Into XLAT_Folder exec master..xp_cmdshell @Command_String
[/code[


Which I then Parse and get the details...

Set up a CURSOR (Booo hiss....)

[code]

DECLARE XLAT_Folder CURSOR FOR
SELECT Create_Time
, File_Size
, [File_Name]
FROM XLAT_Folder_Parsed
ORDER BY [File_Name]





WHILE FETCH STATUS = 0
BEGIN
BEGIN TRAN


Then, based on the methodology that the file name must match the table and format file (I check to make sure everything is out there)

I then bcp the data in using my connection pooling id (I'm logged on as sa in qa)



SET @cmd = 'bcp ' + @db_name + '..' + SUBSTRING(@File_Name,1,CHARINDEX('.',@File_Name)-1) + ' in '
+ @FilePathAndName
+ ' -f' + 'd:DataTax' + SUBSTRING(@File_Name,1,CHARINDEX('.',@File_Name)-1) + '.fmt'
+ ' -S' + @@servername + ' -U -P'

SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + '''' + ', NO_OUTPUT'

INSERT INTO #XLAT_BCP_Results(Col1) Exec(@Command_String)




MOVE DATA FILE TO ARCHIVE
COMMIT TRAN
ANOTHER FETCH



A spid is launched to do the bcp...I have 4 files...on the last load The connection Pooling lauched spid gets blocked by the sa spid that launched the sproc...it doesn't happen all the time, just most of the time....

I've put a trace on in Profiler, but I don't see anything...I've picked the event class of deadlocks...but I never see it...

When I do sp_who, it shows the blocking...

I'm so confused....

View 8 Replies View Related

Blocking

Aug 31, 2006

Hi just read an article on the internet that states

"A quick way to resolve a blocking problem is to disconnect the connection blocking the rest of the users. All you have to do is ask your database administrator to execute the KILL statement. Obviously, this is not a permanent solution, and won't really help anything except take care of an immediate need."

Now when i run sp_who ... i see that the spid that is blocking is the same as the spid itself....

Now the above article states that killing the one that is blocking the rest of the other spids is ok ...

But what if its the same SPID that is blocking ...what does it signify & whats the solution

Thanks

View 2 Replies View Related

Blocking

Nov 13, 2007

Hi Gurus,

I am running a simple SELECT statement on my TempDB

SELECT * FROM TempDB..SysObjects

When I run this , it's getting blocked by some other Process which is not at all relevant to the above Qry. First it gets blocked by one Stored Proc say Proc1 ( The Proc1 is just a SELECT statement on a table which just has four rows ). So I just kill this PROC1 and again its gets blocked by some other stored Proc PROC2 which again is a simple stored Proc and it runs in not time.

Any Suggestions as to where to start.

Thx in Advance

Venu

View 13 Replies View Related

Blocking

Jul 23, 2005

hi,I am using SQL SERVER 2000.Problem that i am facing is when ever I check locks in EnterpriseManager I findfollowing blocking - :1) sp_cursoropen;12) sp_cursorclose;13) sp_cursorfetch;1We dont have any control over application(source code).Whateverwe have to do, has to done from database side.can anyone guide me to solve this problem.Thanks

View 4 Replies View Related

Viewing Last SQL Statement

Aug 17, 2006

Is there any way, in enterprise manager or otherwise, for me to view the last few SQL statements executed against my sql server?Thanks

View 1 Replies View Related

Viewing Data

Dec 9, 2003

I just installed SQL Server on my 2003 box. Up till now I've been using MS Access for all my database needs. When I wanted to work on fields in a table, all I had to do was open Access and there you go. How do I view and modify Fields in SQL?

Chill

View 2 Replies View Related

Viewing The Transaction Log

Dec 4, 2000

Is it possible to view the transaction log to identify events run against a database.
I know that I could create a trace file to log events.
But what I really want to know is whether there is any way to actually view the live .ldf transaction log file and make sense of it.

It is possible to do something similar in Informix.

View 2 Replies View Related

Viewing The Transaction Log

Aug 17, 1999

How can I view the transaction log for a particular database. We are trying to track down when a delete occurred and wanted to look there to find it, unless there is a better place to look.

Thanks

View 4 Replies View Related

Viewing Transaction Log

Jun 28, 1999

Can any one tell me how you view the transaction log records since we don't have a syslogs anymore?

View 1 Replies View Related

Viewing Transaction Log

Apr 30, 1999

How can I view the contents of a transaction log? Is there a better way to determine who deleted a record and when?

View 1 Replies View Related

Viewing A Table From Another Db

Dec 13, 2004

Holy MOLY I've been banging my head up against the wall on this matter for months now. I have two databases and I need to be able to see the tables from different different databases. Usually I create a view like this

LicensingActions.dbo.License_Suspensions

But it wasnt working kept telling me that the License_Suspensions table didnt exsist and at the time the name was in all caps. So I decided to change the name, and low and BEHOLD IT WORKS. The funny was that I wasnt having that problem any of the other tables just that one. Well live and learn. Just thought I'd share that with you guys

View 4 Replies View Related

Viewing Indexes

Aug 22, 2005

I just got some great code from this site for viewing constraints, but I can't find the equivalent for indexes (and I couldn't modify the constraint sql), any suggestions?

View 2 Replies View Related

Viewing Sql Server

Jun 17, 2007

why can't i see SQL server 2005 the same way i see Visual Basic 2005 Express edition. i'm able to see VB by going under the all Programs menu and select it, when i try to find SQL 2005, i see configuration tools and some submenu of configuration tools. is that the way it is? if not, how do i get SQL 2005 to open up like VB so i can play with it. Also, at home i do not have a server, do i have to have a home server to play with SQL



hens

View 5 Replies View Related

Viewing A View...

Feb 17, 2006

I am trying to view a "View" that is in one of my databases, and I am having a problem seeing the scripting that is inside of it. In sql2000 all I needed to do to view a "View" is double click on it. If I double click on a "View" in sql2005 it will open more folders showing the layout, but I want to see the scripting. If I right click on it and then click open view, sql2005 returns the values that the view would bring across. Does anybody know how to view the scripting of a "View" for sql2005?

Also if I select Modify, sql2005 returns scripting but it does not look right.

Thanks for the help!

View 7 Replies View Related

SSIS Log Viewing

Mar 28, 2006

I would like to know the best way to view Package Logs that are being stored in SQL Server? Right now I select out of sysdtslog90 to see what the log file has. I would like to know if there is a way like SQL 2000 where there is a drop down for the date and you could view the entire package data.

View 2 Replies View Related

Sql Blocking Problem

Dec 13, 2007

I have one batch jobs running 4 time a day and web application.
We have hosted the site long time already but this month we are facing the store procedure blocked problem.
The problem is that when user submit the form when during the execution of batch job, two storeprocedure are blocked.
 One store procedure that is using from batch job read the table "A" and another store procedure that is using from web app update the table "A".
Then deadlock is occure. but i don't understand it is hosted long time already. Please let me know if you know how to solve this issue.
 Thanks,
Aung

View 6 Replies View Related

Blocking URGENT!!

Jan 14, 2002

Hi ALL!
Can a sleeping process block another process sleeping/runnable?It's happening in our SQL server.Can someone expalin??
thanks.
Di.

View 1 Replies View Related

SQL Server 7 Blocking

Apr 24, 2002

I have a powerbuilder application that is connecting to SQL Server. We get very frequent DeadLock situations. Is there a way that I can check to see as what SQL is causing the Lock?

Thanks in advance for any suggestions...
-Raj

View 1 Replies View Related

Blocking Processes

May 29, 2001

I have upgraded a MS SQL database from 6.5 to 7.0. The database functioned fine in 6.5, now I have a table that is locking due to a blocking process. If I kill the process all is fine, but am trying to determine what is causing the process to hang. Has anyone experience any similar situations.

View 2 Replies View Related

Blocking (Urgent)

Jul 10, 2001

Hello,

In the morning at 9:32 one of the spid was blocked and got time out msg for select statement. the statement was sp_execute wht is sp_execute?
can anyone guide me?

Thanks

View 2 Replies View Related

Blocking (Urgent)

Jul 13, 2001

Hello,

I've a problems with blocking
simple select * from table1
is blocked
how to know which statement is blocking
select * from table1 is spid 30 blocked by spid 26
and in current activity spid 26 has no select or any statement
how could i know the spid 26 statement ?
And can just select statement be blocked?

wht should we do to monitor locks?

Thanks

Binoy

View 2 Replies View Related

Spid - Blocking

Nov 15, 2000

I know that spid 22 is blocking 2 other users. How do I tell who that user is? Is this just matching the same spid# in the process info in EM and if it doesn't give me an NT name it's probably because they're coming through the application and I can't figure it out?

Thanks

View 2 Replies View Related

Blocking Problem.

Feb 4, 2001

Hi there,

I've a encountering a problem with "Blocking" and would highly appreciatd, if anyone out there please advice how to get to the bottom of this. the problem in as follows:

Every now and then, my SQL Server Freezes and when i look at the process info, i see some user connections in blocked mode and some in blocking. i dont know, where to start looking for. my first question would be, what should I do here? Should i kill the blocked user or blocking user? Also, Whats the difference between this two modes (blocked and blocking) and why does this happening? can i trace the user id which is causing this problem?

Experts please advice, what to do in the near future to aviod this.

I search at microsoft support site and came up nothing good about this problem.

Appreciated, If anyone out there advice on this.

Many thanks in advance.

Sindi

View 1 Replies View Related

Blocking Problem

Apr 19, 2000

When an SA attempts to change permission on any object they are shown as blocked as expected. However as new users access the object the SA's process is continuely blocked until the object is free. Can anyone explain this??

Thanks
David

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved