Cant execute sql script twice
-
Hi
I have a large sql script running on a SQL Server 2k server, however i can not execute the query twice without stopping all the sql server services and restarting them.
The script im having problems with is as follows:
PRINT '##PREPARING TEMP TABLES##'
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[tbl_exporttemp]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT '-Deleting temporary export table'
DROP TABLE tbl_exporttemp
END
DELETE FROM tbl_export
GO
PRINT '##DONE##'
PRINT '##COLLECTING DATA FOR ASSIGNMENT##'
DECLARE @DataID BIGINT
DECLARE @AssignmentID BIGINT
SELECT TOP 30000
tbl_data.id,
"Title",
"Surname",
"Forename",
"Blank",
"HouseName",
"HouseNumber",
"Street",
"City",
"Postcode",
"TelNo",
"ProfileID"
INTO tbl_exporttemp from tbl_data
left outer join tbl_assignment on tbl_data.id = tbl_assignment.DataID
where tbl_data.TPSStatus is null
AND tbl_data.TPSDate > '02/22/2005'
AND tbl_assignment.resultcode = 'DNS'
AND tbl_assignment.id in( select MAX(tbl_assignment.id) from tbl_assignment where tbl_data.id = tbl_assignment.DataID)
order by tbl_data.id
PRINT '##DONE##'
PRINT '##ABOUT TO ASSIGN DATA##'
INSERT INTO tbl_export ("ID", "Title", "Surname", "Forename", "Blank", "HouseName", "HouseNumber", "Street", "City", "Postcode", "TelNo", "ProfileID") SELECT "ID", "Title", "Surname", "Forename", "Blank", "HouseName", "HouseNumber", "Street", "City", "Postcode", "TelNo", "ProfileID" FROM tbl_exporttemp
SET rowcount 1
SELECT @DataID = ID FROM tbl_exporttemp
WHILE @@rowcount <> 0
BEGIN
--Insert our record into the assignment table
set rowcount 0
INSERT INTO tbl_assignment("DataID", "CallCentreID", "IssueDate") VALUES (@DataID, 'PB001', GETDATE())
--Now that we have inserted arecord into the assignment table, update the id of the record in the
--export table to be that of the assignment (and hence make it unique for updating the record later
--SET rowcount 1
--SELECT @AssignmentID = max(ID) FROM tbl_assignment
--UPDATE tbl_export SET ID=@AssignmentID WHERE ID=@DataID
UPDATE tbl_export SET ID=@@IDENTITY WHERE ID=@DataID
DELETE FROM tbl_exporttemp WHERE ID=@DataID
--Get our next data id
SET rowcount 1
SELECT @DataID = ID FROM tbl_exporttemp
END
--clear up our temp tables
DROP TABLE tbl_exporttemp
PRINT '##DONE##'
SELECT COUNT(*) AS 'Records Exported' FROM tbl_export
Eveything runs fine first time round with the following trace being outputted:
##PREPARING TEMP TABLES##
-Deleting temporary export table
(29999 row(s) affected)
##DONE##
##COLLECTING DATA FOR ASSIGNMENT##
(30000 row(s) affected)
##DONE##
##ABOUT TO ASSIGN DATA##
(30000 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
till end of script
.......
but it just hangs forever when i try to run it a second time with the following trace:
##PREPARING TEMP TABLES##
(1 row(s) affected)
hangs forever
......
I think that its something to do with my server setup.
Ive read a few things about tempdb / logfile sizes hindering query execution, so I have set these to a suitably large size (several gb) to try and eliminate that but with no luck.
Im completely stuck and help on the matter would be greatly appreciated.
Thanks
Gareth.
-
Firstly, I would replace the DELETE FROM statements with TRUNCTATE TABLE statements. These are faster and won't generate the same amount of log file activity.
Secondly, replace your SELECT INTO with a straight INSERT INTO / SELECT. This is also much leaner on resources. It will also mean that rather than dropping and recreating tbl_exporttemp you will simply have to TRUNCATE TABLE dbo.tbl_exporttemp at the start of your procedure.
Lastly, I would replace @@IDENTITY with SCOPE_IDENTITY() for sheer robustness reasons.
-
Another point to add:
You are *not* creating temporary tables in tempdb. Those tables begin with a #. You are creating permanent tables in the current database.
You might also want to choose a safe date format like '20050222'. Might save a lot of trouble.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Is there anything in the SQL error log that may indicate resources being used up.
How much memory do you have in the server and how much is going to SQL.
Also how large is your tempdb?
-
Your query to get data uses a left join but:
>>AND tbl_assignment.resultcode = 'DNS'
You reference the left-joined table in the where clause thereby implicitly converting it to an inner join.
I'm not even sure where to start with the Set RowCount = 1 and the WHILE loop. You're essentially cursoring over 30K records ?
-
SELECT TOP 30000
Have you tried SELECT TOP 10? Do you have same problem with 10 records?
If you do try change WHILE loop to something like(insted of SET ROWCOUNT)
WHILE @id IS NOT NULL
BEGIN
--<Your inserts, updates, deletes.>
SELECT @id = MIN(UID)
FROM --<Your tempTable>
WHERE UID > @id
END
-
Hi all
Many thanks for all the replies, and just to tie up a few of the questions.
I now managed to get teh script working so i dont have to restart sql server evey time, it seemed a combination of the insert into change and the truncate table statement did the trick - so many thanks to everyone for pointing this out.
The reason i have such a wierd looking script is probably beacuse im not really a t-sql developer, prefering to use j2ee do all my app logic / db access normally, but this kinda got dropped on my from a great height and i needed to get a solution (if thats what ya wanna call it
) in really quickly.I guess the looping thing is just how i would have done it in java, but it was the first working solution i came up with, but this was what i needed to do anyway.
Basically there is a BIG table of data (tbl_data) that needs to be assigned out to users (with a specific id) hence this is recorded in tbl_assignment.
Assigned data must match certain requirements (first sql statement selecing into tbl_exporttemp).
I then needed to record this assignment in tbl_assignemnt, then store the newly created id, plus the rest of the data originally extracted from tbl_data in another table (tbl_export) for export to a file later on.
The loop seemed to be the only way to merge the extracted data with the assignment id (although im most probably wrong
)But anyway - it works for now at least. Gonna have a play with the while statement to try and get rid of the messy row counts n stuff later, but im just pleased i can run it more than once now so thatll do for the time being :>
Thanks Again
Gareth.
P.S - Just in case any of you are interested the script now looks like this:
PRINT '##PREPARING TABLES##'
TRUNCATE TABLE tbl_exporttemp
TRUNCATE TABLE tbl_export
GO
PRINT '##DONE##'
PRINT '##COLLECTING DATA FOR ASSIGNMENT##'
DECLARE @DataID BIGINT
DECLARE @AssignmentID BIGINT
DECLARE @CallCentreID VARCHAR(50)
SET @CallCentreID='PB016'
INSERT INTO tbl_exporttemp ( "ID", "Title", "Surname", "Forename", "Blank", "HouseName", "HouseNumber", "Street", "City", "Postcode","TelNo", "ProfileID")
SELECT TOP 20000
tbl_data.id,
"Title",
"Surname",
"Forename",
"Blank",
"HouseName",
"HouseNumber",
"Street",
"City",
"Postcode",
"TelNo",
"ProfileID"
FROM tbl_data left outer join tbl_assignment on tbl_data.id = tbl_assignment.DataID
WHERE tbl_data.TPSStatus is null
AND tbl_data.TPSDate > '02/22/2005'
AND tbl_assignment.resultcode = 'DNS'
AND tbl_assignment.id in( SELECT MAX(tbl_assignment.id)
FROM tbl_assignment
WHERE tbl_data.id = tbl_assignment.DataID)
ORDER BY tbl_data.id
PRINT '##DONE##'
PRINT '##ABOUT TO ASSIGN DATA##'
INSERT INTO tbl_export ("ID", "Title", "Surname", "Forename", "Blank", "HouseName", "HouseNumber", "Street", "City", "Postcode", "TelNo", "ProfileID")
SELECT "ID", "Title", "Surname", "Forename", "Blank", "HouseName", "HouseNumber", "Street", "City", "Postcode", "TelNo", "ProfileID"
FROM tbl_exporttemp
SET rowcount 1
SELECT @DataID = ID FROM tbl_exporttemp
WHILE @@ROWCOUNT 0
BEGIN
--Insert our record into the assignment table
SET rowcount 0
INSERT INTO tbl_assignment("DataID", "CallCentreID", "IssueDate") VALUES (@DataID, @CallCentreID, GETDATE())
--Now that we have inserted arecord into the assignment table, update the id of the record in the
--export table to be that of the assignment (and hence make it unique for updating the record later
UPDATE tbl_export SET ID=@@IDENTITY WHERE ID=@DataID
DELETE FROM tbl_exporttemp WHERE ID=@DataID
--Get our next data id
SET rowcount 1
SELECT @DataID = ID FROM tbl_exporttemp
END
--set rowcount 0
PRINT '##DONE##'
SELECT COUNT(*) AS 'Records Exported' FROM tbl_export
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply