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)
 [Resolved] Server never completes query

Author  Topic 

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2008-11-24 : 09:38:16
From the query analyzer I am running set of code and having big time issue with the server not completing the request.

So if I run following code:

create table	#Equential_JobQtyDetailTable
		(
			eq_job_date datetime,
			eq_item_number char(15),
			eq_item_qty decimal(8,2),
						                  		)
declare @JobNumber int 
declare @ItemNumber char(20)
declare @ItemNumber1 char(8)
declare @DateFrom datetime
declare @DateTo datetime

set @JobNumber = 1100281
set @ItemNumber = '0080     ' 

exec [VGIWPW03-SQL3\EQUENTIALPROD].goLabor30.dbo.sp_Vecellio_ProductionDetailQtyCompare
@JobNumber, @ItemNumber, @DateFrom, @DateTo


The response is very good and 3 records are returned

Now if I run witn an "insert into..." the query never finishes. The temp table is being created Ok

create table	#Equential_JobQtyDetailTable
		(
			eq_job_date datetime,
			eq_item_number char(15),
			eq_item_qty decimal(8,2),
						                  		)
declare @JobNumber int 
declare @ItemNumber char(20)
declare @ItemNumber1 char(8)
declare @DateFrom datetime
declare @DateTo datetime

set @JobNumber = 1100281
set @ItemNumber = '0080     ' 

insert into   #Equential_JobQtyDetailTable 
exec [VGIWPW03-SQL3\EQUENTIALPROD].goLabor30.dbo.sp_Vecellio_ProductionDetailQtyCompare
@JobNumber, @ItemNumber, @DateFrom, @DateTo


I have been working on this for several days. Does anyonse see any obvious problem with my code ao have any ideas what is going on? Thank you.

PS When I run the same code (except for the linked server connection) on a windows server 2003 I do not have a problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-24 : 09:52:30
whats the sp doing?
Also are you running this just as a batch or inside another sp?
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2008-11-24 : 10:20:29
Actually this problem happens with any other sp's where I create a temp table and do the "insert into...". I also have sp's connection to db2/400, they run fine as long as I do not do the insert. Here is the sp I am calling on windows server 2003:


CREATE PROCEDURE sp_Vecellio_ProductionDetailQtyCompare  

@JobNumber int,
@ItemNumber char(20),
@DateFrom datetime,
@DateTo datetime 

as


CREATE TABLE	#ProductionInquiry

		(
			batch_guid uniqueidentifier,
			event_guid uniqueidentifier,
                                        production_event uniqueidentifier,
			job_guid uniqueidentifier,
			item_guid uniqueidentifier,
			job_date datetime,
			job_number char(15),
			item_number char(15),
			item_status char(2),
			item_qty decimal(8,2),
			item_uom char(40),
                                       event_name char(13)
			                  		)

INSERT INTO   #ProductionInquiry (batch_guid, event_guid, job_guid, item_guid, production_event, 
                          job_date, job_number, item_number, item_status, item_qty, item_uom, event_name)
SELECT            dbo.Event.Batchguid as batch_guid, 
                          dbo.Event.EventGuid as event_guid, 
	            dbo.Job.JobGuid as job_guid,
	            dbo.Item.ItemGuid as item_guid,
	            dbo.ProductionEvent.ProductionEventGuid as production_event, 
                         dbo.Event.EventDate as job_date, 
                         dbo.Job.CompanyJobId as job_number, 
                         dbo.Item.CompanyItemId as item_number,
                         dbo.Item.Active as status,
                         dbo.Productionevent.Quantity as item_qty,
	            dbo.Item.UnitOfMeasure as item_uom,
                          substring(dbo.EventStatusType.Name, 1, 13) as event_name  	
FROM               dbo.Batch
		inner join dbo.Event on dbo.Event.BatchGuid = dbo.Batch.BatchGuid
                           inner join dbo.EventStatusType on dbo.EventStatusType.EventStatusTypeGuid = dbo.Event.EventStatus
		inner join dbo.Job on dbo.Job.JobGuid = dbo.Event.JobGuid
		inner join dbo.Item on dbo.Item.ItemGuid = dbo.Event.ItemGuid
		left join dbo.ProductionEvent on dbo.ProductionEvent.EventGuid = dbo.Event.EventGuid
WHERE          dbo.Job.CompanyJobId = @JobNumber and dbo.Item.CompanyItemId = @ItemNumber and dbo.ProductionEvent.quantity > 0


IF @DateFrom is NULL
	BEGIN 
SELECT 	 job_date,
		 item_number,
	              item_qty
                          
FROM     	#ProductionInquiry
         		left join dbo.Event  on #ProductionInquiry.Batch_Guid = event.batchguid and #ProductionInquiry.event_guid = event.relatedeventguid
         		left join dbo.JobNoteevent on dbo.JobNoteEvent.EventGuid = Event.EventGuid
WHERE 	job_number = @JobNumber and item_number = @ItemNumber
 
ORDER BY 	job_date

END

ELSE
	BEGIN
SELECT 	 job_date,
		 item_number,
	             	 item_qty
                          
FROM     	#ProductionInquiry
         		left join dbo.Event  on #ProductionInquiry.Batch_Guid = event.batchguid and #ProductionInquiry.event_guid = event.relatedeventguid
         		left join dbo.JobNoteevent on dbo.JobNoteEvent.EventGuid = event.eventguid
WHERE 	job_number = @JobNumber and item_number = @ItemNumber  and job_date >= @DateFrom and job_date <= @DateTo
ORDER BY        job_date


END

Drop Table 	#ProductionInquiry
GO
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-24 : 10:23:19
1) Try a table variable
2) Try a non-temp table

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2008-11-24 : 10:54:36
Peso, where should I try

1) Try a table variable
2) Try a non-temp table

on Sequel Server 2005 or 2003?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-24 : 10:57:20
quote:
Originally posted by snufse

exec [VGIWPW03-SQL3\EQUENTIALPROD].goLabor30.dbo.sp_Vecellio_ProductionDetailQtyCompare
@JobNumber, @ItemNumber, @DateFrom, @DateTo[/code]

The response is very good and 3 records are returned

Now if I run witn an "insert into..." the query never finishes.

You created a local temp table in your original post.
Try with a table variable instead. If that fails, try with a non temp-table.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2008-11-24 : 11:05:47
Tried this:

declare @Equential_JobQtyDetailTable table
		(
			eq_job_date datetime,
			eq_item_number char(15),
			eq_item_qty decimal(8,2)
						                  		)
declare @JobNumber int 
declare @ItemNumber char(20)
declare @ItemNumber1 char(8)
declare @DateFrom datetime
declare @DateTo datetime

set @JobNumber = 1100281
set @ItemNumber = '0080     ' 

insert into   @Equential_JobQtyDetailTable 
exec [VGIWPW03-SQL3\EQUENTIALPROD].goLabor30.dbo.sp_Vecellio_ProductionDetailQtyCompare
@JobNumber, @ItemNumber, @DateFrom, @DateTo



and tried this:

create table Equential_JobQtyDetailTable 
		(
			eq_job_date datetime,
			eq_item_number char(15),
			eq_item_qty decimal(8,2)
						                  		)
declare @JobNumber int 
declare @ItemNumber char(20)
declare @ItemNumber1 char(8)
declare @DateFrom datetime
declare @DateTo datetime

set @JobNumber = 1100281
set @ItemNumber = '0080     ' 

insert into   Equential_JobQtyDetailTable 
exec [VGIWPW03-SQL3\EQUENTIALPROD].goLabor30.dbo.sp_Vecellio_ProductionDetailQtyCompare
@JobNumber, @ItemNumber, @DateFrom, @DateTo



Both queries never finishes.......
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-24 : 11:09:13
But this alone work?
EXEC	[VGIWPW03-SQL3\EQUENTIALPROD].goLabor30.dbo.sp_Vecellio_ProductionDetailQtyCompare	@JobNumber,
												@ItemNumber,
												@DateFrom,
												@DateTo


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-24 : 11:09:48
Have you checked datatypes?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2008-11-24 : 11:17:20
We just rebooted the Sequel Server 2005. This is what happened:

The queries then worked (inserting into temp tables for both the Windows Server 2003 and the db2/400 connections) for about 3-4 times and after that back to the same scenario where the queries never finishes. I assume then that the problem must be somewhere else and not in the code or? Any ideas?

Now, also when I try to:

drop table Equential_JobQtyDetailTable



that was created in my test, it never finishes as well.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-24 : 11:19:19
quote:
Originally posted by snufse

We just rebooted the Sequel Server 2005. This is what happened:

The queries then worked (inserting into temp tables for both the Windows Server 2003 and the db2/400 connections) for about 3-4 times and after that back to the same scenario where the queries never finishes. I assume then that the problem must be somewhere else and not in the code or? Any ideas?

I guess parameter sniffing can be a cause. if its always executing this as a SP that this problem occurs.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-24 : 11:22:21
Can you also try to analyse if there's some blocking occuring while executing this by running sp_who2
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2008-11-24 : 12:00:22
Running sp_who2 this is what I see:

RUNNABLE VGIWPW03-SQL5 as400_jde INSERT 0 0 11/24 08:42:29 Microsoft SQL Server Management Studio - Query 90 0

I have 4 jobs showing like this. These are all my test calls and then trying to cancel the queries.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-24 : 12:02:56
quote:
Originally posted by snufse

Running sp_who2 this is what I see:

RUNNABLE VGIWPW03-SQL5 as400_jde INSERT 0 0 11/24 08:42:29 Microsoft SQL Server Management Studio - Query 90 0

I have 4 jobs showing like this. These are all my test calls and then trying to cancel the queries.

did you had a look at blkby column while query was running and spotted any values there? it shows blocking by another process.
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2008-11-24 : 12:07:20
All the blkby column values are blank.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-24 : 12:12:43
quote:
Originally posted by snufse

All the blkby column values are blank.

that means blocking is not happening.
Next can you try running the sp as query batch. just copy body and declare variables for parameters and initialise values and use them in query.
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2008-11-24 : 12:20:23
In batch the job is running and never finishes as well. Any other ideas? We rebooted the server again and I am able to run my procs 5 times (create temp table, declare variables, insert into and drop table). Then we are back to where jobs never finishes again. It is when I do "insert into..." when I have problems.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-11-25 : 00:15:57
whats the recovery model used? Also have a look at log space after execution and see if its getting full
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2008-11-26 : 15:14:18
Think fixed:

Changed the Distributed Transaction Coordinator service security from Local System to Network Service and then made changes to MSDTC security to allow it to send and receive connections without security
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site