May 20, 2003 at 7:57 am
Hello, all. I am relatively new to writing stored procedures, so forgive me if this is a basic question.
I have an sp that basically uses a series of SQL statements to insert data into a table. If I simply run the script from a SQL window it runs great, but when I compile the SP and try to execute, it hangs.
Any ideas?
Thanks,
Ali
May 20, 2003 at 8:40 am
I am sure you have done this - so I apologize for suggesting some of the obvious...
If you copy the code directly into query analyzer from the stored procedure, it works? Was it copied from a working script or retyped? I make plenty of typing mistakes. -- Watch out for "GO" or ";" that complete each thought.
You may want to put a bunch of print statements in the procedure to narrow down to a point of failure.
Probably not much help - but a few ideas.
Guarddata-
May 20, 2003 at 8:42 am
quote:
I have an sp that basically uses a series of SQL statements to insert data into a table. If I simply run the script from a SQL window it runs great, but when I compile the SP and try to execute, it hangs.
It might be helpful if you can post the code or snippets of it
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 20, 2003 at 9:03 am
Thanks for your responses!
This is the first part of the sp, with one example of the 'insert into' statements (there are 6 of them and I just didn't show them all):
===========================================
===========================================
CREATE PROCEDURE mnet_riders_load
AS
--*********************************************************************************************************
--Pull active dataset
DECLARE @dataset varchar(10)
set @dataset = (SELECT mnet_dataset from mnet_active_dataset)
*********************************************************************************************************
print 'The START TIME is: '+ CONVERT(char(30), CURRENT_TIMESTAMP)
*********************************************************************************************************
--clear copy/ copy mnet_riders before changing
DELETE mnet_copy_riders
INSERT INTO mnet_copy_riders
SELECT * FROM mnet_riders
DELETE mnet_riders
*********************************************************************************************************
--Update PreK Language riders assigned as Pickup s/b Delivery
Update mnet_riderlist
set RouteType = 'DELIVERY'
--SELECT StudentID, RouteType, RouteName, Activity
FROM mnet_riderlist
WHERE UPPER(Activity) like '%PRE-K%AM%' AND
Left(RouteName,1) IN('P','S') AND
RouteType = 'PICKUP'
*********************************************************************************************************
--STEP 1: Pull all students with 2 N/A records into mnet_riders
INSERT INTO mnet_riders
SELECT DISTINCT @dataset, a.StudentID, a.LastName, a.FirstName, a.SchoolNo, a.Grade, a.PC1, a.PC2,
a.CensusGroup, a.ResidenceAddress, a.Eligibility, a.SchoolName, a.Activity, a.StopName,
'','', a.RouteName, a.RouteType, a.VehicleID, a.BusType, a.DriverName, a.Assistant
FROM mnet_riderlist a INNER JOIN
(SELECT b.StudentID
FROM mnet_riderlist b
GROUP BY b.StudentID, b.LastName, b.FirstName, b.SchoolNo, b.Grade, b.PC1, b.PC2, b.CensusGroup,
b.ResidenceAddress, b.Eligibility, b.SchoolName, b.Activity, b.StopName, b.RouteName,
b.RouteType, b.VehicleID, b.BusType, b.DriverName, b.Assistant
HAVING b.StopName='N/A' AND Count(b.StudentID)=2) T1
ON a.StudentID = T1.StudentID
print 'N/A records added...'
===========================================
===========================================
If I run the entire script (minus the 'Create Procedure As' of course) in the query analyzer window, it runs beautifully, in a little over 1 minute. The 'print' statements all show up when it is finished, however, as shown below:
===========================================
===========================================
The START TIME is: May 20 2003 10:51AM
(0 row(s) affected)
(61649 row(s) affected)
(61649 row(s) affected)
(0 row(s) affected)
(18517 row(s) affected)
N/A records added...
(145 row(s) affected)
Votran records added...
(4459 row(s) affected)
Eligible Not Requesting records added...
(38528 row(s) affected)
(38530 row(s) affected)
P&D Same Stop...
(589 row(s) affected)
(666 row(s) affected)
P&D Different Stops...
(96 row(s) affected)
Delivery Only records added...
(27 row(s) affected)
Pickup Only records added...
The END TIME is: May 20 2003 10:52AM
===========================================
===========================================
If I open the query window and type 'Exec mnet_riders_load' it hangs up.
Does that help?
Thanks so much for your assistance...
Ali
May 21, 2003 at 2:05 am
Well, at first sight I thought SELECT DISTINCT @dataset might cause the problem, but at quick test has proven me wrong.
Have you tried putting in some @@error check in your proc to see where it hangs like
DECLARE @ErrorSave INT
SET @ErrorSave = 0
do some action..
-- Save any non-zero @@ERROR value.
IF (@@ERROR <> 0)
SET @ErrorSave = @@ERROR
RETURN @ErrorSave
GO
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 23, 2003 at 1:05 pm
Have you try to debug sp on the server.
May 23, 2003 at 1:53 pm
Well, it appears that my SQL syntax was just taxing the server too much.
I changed from
SELECT DISTINCT
FROM
GROUP BY
HAVING
WHERE
to
SELECT DISTINCT
FROM
WHERE
GROUP BY
HAVING
...and it runs fine. I still don't know why it will run the old way in the explorer window but not with an execute command.
Thanks everyone, for your assistance.
May 27, 2003 at 7:20 am
Ali,
If you are doing a GROUP BY you can drop the DISTINCT. This may speed it up a bit as well.
Jeff
May 29, 2003 at 7:18 pm
Ali,
Be careful of your insert statements as well. If the order of the columns changes from either your select * or your insert into your code will fail.
If you explicitly state your columns in your INSERT INTO TableA (Field1, Field2)
SELECT FieldC, FieldD from TableZ
your code should never fail (unless you delete those columns)
AJ Ahrens
SQL DBA
Revenue Assurance Management - AT&T
Good Hunting!
AJ Ahrens
webmaster@kritter.net
May 30, 2003 at 6:25 am
Thanks for your tips! I need them!
Ali
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply