SQL 2000 to 2005 sproc endless execute

[Tarwn]

I have been asked to look into a problem one of our partner groups is having with a set of SQL procedures. I have run out of ideas and was hoping someone here might think of something, as I am not a DBA and don't have the depth of knowledge many of you do.

Situation:
A set of procedures exists that imports data from our ERP database to our WMS database (Parts, PO's, etc). Both of the databases were running on 2000, but they have decided that as part of an ERP upgrade that the new database will be running on 2005. During testing last week they determined that 3 critical jobs were failing. On executing the stored procedures manually they found them to run endlessly, with no output to the messages queue and no failure.

For the sake of this post I will call the 2000 WMS DB "W2000" and the link for the 2005 ERP DB "E2005". You don't want to see what it actually got named...

Proc:
This is a small portion of one procedure that sits on "W2000". We have pinpointed it as the portion that causes the failure for this procedure.

create table #tmpParts (
Part_Number varchar(20) not null,
Part_Description varchar(30) not null,
Part_Type varchar(20) null,
UOM varchar(10)null ,
StorageClass int null,
Conversion_Factor_To_EA decimal(10,5) null
)

/*GET Part Numbers*/

insert into #tmpParts
exec E2005.ERP_DB.dbo.SP_Part_Export

Troubleshooting Steps:
- Executing any individual portion of this procedure works.
- Highlighting the 'exec' and running it manually works in a second or less. (see below for running exec and insert into)
- Temp table fields are the exact same as the fields returned by the stored procedure
- Permissions for the link apparently are all mapped to use the remote "sa" account (which pleases me so much) so I do not believe permissions to be an issue

I moved this portion of the procedure (and added a drop) to a separate window. I then added print outputs before and after each piece. When executing there are no messages in the output, just the same behavior of endlessly grinding (same in 2000 Enterprise Mangler + 2005 Studio). Any time the INSERT INTO...EXEC... section is included the system exhibits this behavior.

Canceling in Mangler or Studio does not work.

The remote stored procedure this example calls on the linked server is a very simple SELECT statement with no logic or additional calls in it.
The other two failing procedures are more complex and have a great deal of logic built into them (which is why I chose this one to use in the post).

I have tried searching both this forum and Google, but have not found anything that has helped. Please let me know if I can provide any more information or if anyone has ideas on additional troubleshooting steps. I cannot recall all of the things I have tried, but I am perfectly willing to retry anything if it will provide more information.

Thanks,
-T

 

[Catadmin]

You're sure that no one changed the proc so that it is endlessly looping?

Also, were the stored procs (and everything else in the db) run through the Upgrade Advisor before you upgraded to SQL 2005? That could be your problem. There were a lot of little code changes, things that no longer work and things that were depreciated, with the release of 2k5.

Lastly, have you run Profiler while running the job so that you can see at which point in the stored proc run it gets stuck at?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[bikerboy718]

Catadmin is right. I would run profiler to see what is going on with these stored procedures.

Is the external stored procedure E2005.ERP_DB.dbo.SP_Part_Export on a 2000 or 2005 version of SQL Server?

This may be worth a try, try removing the insert into and just use the insert.

With Great Power Comes Great Responsibility!!!

Michael

 

[gmmastros]

I would suggest that you check the collations on each server. If the collations are different, you may have problems. I would expect for you to get an error message instead of 'endlessly running', but who knows. Ya know.

In case you don't know how to check the collations...

In 2000, open enterprise manager, drill down to your database, right click, properties. On the general tab, at the bottom, it will show the collation.

In 2005, the process is the same. Open Management Studio, drill down to your database, right click, properties. On the general page at the bottom, you will see the collation.

If the collations are the same for both databases on the different servers, then I wouldn't expect there to be any problem.

If they are different, you may want to try specifying the collation for the temp table, like this...

create table #tmpParts (
Part_Number varchar(20) not null [!]COLLATE SQL_Latin1_General_CP1_CI_AS[/!],
Part_Description varchar(30) not null [!]COLLATE SQL_Latin1_General_CP1_CI_AS[/!],
Part_Type varchar(20) null [!]COLLATE SQL_Latin1_General_CP1_CI_AS[/!],
UOM varchar(10) null [!]COLLATE SQL_Latin1_General_CP1_CI_AS[/!],
StorageClass int null,
Conversion_Factor_To_EA decimal(10,5) null
)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

My apologies. I should have tested this first. The NULL/NOT NULL needs to come after the collate part, like this...

create table #tmpParts (
Part_Number varchar(20) [blue]COLLATE SQL_Latin1_General_CP1_CI_AS[/blue][green] not null [/green],
Part_Description varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS not null,
Part_Type varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS null ,
UOM varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS null ,
StorageClass int null,
Conversion_Factor_To_EA decimal(10,5) null
)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Tarwn]

I'll run profiler.

I do not know if the procs were run through Update Advisor, my guess would be not. The remote proc for the above example is a simple select statement that runs fine on it's own:

select i.No_ Part_Number, i.Description Part_Description,
Part_Type = Case 
	When ISNull(i.[Inventory Type] , 0) = 1 then 'WIP'
	When ISNull(i.[Inventory Type] , 0) = 2 then 'CORCS FG'
	When ISNull(i.[Inventory Type] , 0) = 3 then 'RAW MAT'
	Else 'RAW MAT'
	End,
i.[Base Unit of Measure] UOM, i.StorageClass, cast (Round(iuom.[Qty_ per Unit of Measure], 3, 1) as decimal(10,5)) Conversion_Factor_To_EA
from [Item Table] i left outer join [Item Unit of Measure] iuom on (i.No_ = iuom.[Item No_] and iuom.[Code]='EA')
Where IsNull(i.[Inventory Type], 0) <> 0
order by No_

(Note: I tried to cleanup the formatting on this statement to get rid of the random tabs and such)

I have compared these procs to the ones running on the production system and the only changes are to the procedure on the initial system. The change being to use the new link name to the 2005 system instead of to the existing 2000 production system. I don't see anything that should have changed in 2005, and it does run fine on it's own, just not when used as part of an INSERT INTO.


---

Ok, I tried Profiler against the 2005 system. If I execute the proc on it's own I get the expected SP:Starting, SP: Stmt Starting, etc through to SP:Completed. Executing the following piece gives me nothing:

print 'pre temp'

create table #tmpParts (
Part_Number varchar(20) not null,
Part_Description varchar(30) not null,
Part_Type varchar(20) null,
UOM varchar(10)null ,
StorageClass int null,
Conversion_Factor_To_EA decimal(10,5) null
)

print 'post temp'

print 'pre insert'

insert into #tmpParts
exec E2005.ERP_DB.dbo.SP_Part_Export

print 'post insert'

print 'pre drop'

Drop Table #tmpParts

print 'post drop'

I was monitoring everything in the following categories:
Stored Procedures, TSQL, Transactions

Suggestions on additional categories or items? Remember, I'm not a DBA and while I have used Profiler before, I'm not an expert. I'm assuming I could watch for a login, but not sure if this is necessary.

-T

 

[Tarwn]

The collations are differant and I had actually known about this already, but hadn't considered the impact. I'll give that a shot and see what happened. Luckily they are in GMT +100 so our point-to-point isn't quite as flooded as it was when I was trying to run profiler

 

[gmmastros]

I suggest you read this...

http://www.sql-server-performance.com/linked_server.asp

Pay particular attention to the part about sp_serveroption.

Good luck. I don't envy you for having to figure out this problem. I just hope that my suggestions are helpful.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

More suggestions. These are from the "I got no idea, but I'm gonna guess" department, so you'll want to check George's suggestions first. @=)

Does the 2k5 server allow remote connections? Check the Surface Area Configuration to verify this is on.

Try commenting out everything at & below the EXEC statement on your query and running it against the SQL 2k5 server. Does that part work at least?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLDenis]

when upgrading your db to 2005 from 2000 make SURE (!!!!) you rebuild your indexes or update your statistics

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Tarwn]

Indexes? Statistics? Please, I'm dealing with DB's created by people that thought Excel was complicated on the one hand, and DB's recreated from scratch by application installs on the other. I'm lucky when I find tables that have indexes. We had one table that got copied to an archive and truncated once a month because they thought that tables were unable to hold more than 50,000 records due to performance issues.


Collation on 2000 (initial) Server is SQL_Latin1_General_CP850_BIN
Collation on 2005 (linked) Server is French_CS_AS

I tried specifying the COLLATE in the temp table declaration/creation but still get the endless grind. Is it possible this needs to be set on the 2005 server before it attempts to return the data?
Is it possible to change the collation after a database has been created? I have no problem dumping their silly collation change choice in a heartbeat if I can do it without reinstalling the ERP.

The DB on the 2005 server is a fresh creation of the DB that was created by the ERP as part of the reinstallation process. The procs that I am trying to interface with were scripted out of the old system and put into the new one (I assume).

George - thanks for the link, nothing leapt out at me as being helpful, but I will add it to my list and learned a few things from reading through it. Unfortunately the presence of separate collations means I can't use the sp_serveroption

I can execute the remote stored procedure if I highlight just the exec portion, so I would assume this means that remote connections are on.

 

[gmmastros]

>> Is it possible to change the collation after a database has been created?

I've never done this, but you could try...

Open Management Studio, drill down to your database, right click, properties. Click the Options tab (on the left). Collation is the first item on the right.

I should mention a couple points:

1. I don't know if this will fix your problem. I've never run in to this problem, so I am just spit balling here.
2. I've never changed the collation for a database (the default was always fine for me).

When changing the collation on the database, you should at least spot check the columns. Each char/varchar column can have a different collation. Right click a table, and then click Modify. Click on a varchar column. In the column properties, you should see the collation, which is usually database default, but doesn't have to be.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Tarwn]

How does the fact that the data is returning from a proc on the second database affect collation? Is it possible that the 2000 side is dying in the prepare stage or doesn't understand the French_CS_AS collation?

We have rewritten one of these procedures to do the queries itself off of the remote table (rather than calling a remote proc that does the query) and it works fine without explicitly changing the collation, so now I'm more stumped.

 

[gmmastros]

I don't know.

Originally, you said... I can run the remote procedure, but it doesn't work in the stored procedure. So I thought, what's different? In the stored procedure, you are inserting the data in to a temp table.

At this point, I'm almost out of suggestions. As a last ditch effort, you may want to make sure that both instances are fully patched.

Sorry I couldn't be more help.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Tarwn]

No problem. I'm kind of used to these guys coming up with issues no one else has

 

[mrdenny]

Try running profiler against both machines. But when you do turn on the option to view the execution plans on both machines.

You may need to clear the cashed execution plans on both machines.

How many records are comming accross?

I forgot (and I'm to lazy to scroll up) if you do the create table and the execute outside of the procedure do you have the same problem?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Tarwn]

If I execute the create table and "insert into exec SP" inside or outside of the proc it dies a horrible death of no returns. No records appeared to be coming across and I believe it must be failing before it truly executes the statement because the print messages are not being output either (or I need to turn on or off some buffering mechanism that I don't know about).

I will try profiler against both machines in the morning.

On a sidenote, we found out that the simplest proc should have been failing because several of the fields were not fricking created on the server. I'm glad they caugfht that and these failing critical jobs in their full end-to-end test last week...sorry, a little annoyed.

I still believe some sort of error message should have been posted if the fields don't exist, and it worries me that executing the stored procedure by itself works. I only have someone elses word that those fields don't exist in the test system's tables, so i will check that in the morning as well. It' s my belief that I should be getting an error message in either eventuality rather then results in one and slow-grinding-death in another.

 

[SAS75]

We're having the same problem with an INSERT..EXEC statement running off into lalaland when using remote servers. As with your case, the data retrieval itself works fine - only a problem when I try to insert the output into a temp table. Did you ever determine a solution? Thanks! -Scott

 

[mrdenny]

See if either one of you are having MS DTC issues.

I know that when doing an INSERT EXEC to a remote server via a linked server SQL promotes it to an explicit distributed transaction. If DTC is having problems this could effect your procs.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com