Query Produces Jumbled Output / Output Not In Sequence
Jul 23, 2005Hi!
Server info -
Win2K3 Server +SP1 with 1 GB Memory and 1.5 GB Virtual Memory
SQL Server 2000 Enterprise Edition + SP3 running on this.
Required result -
Create a SQL Script that will contain a set of create, update, insert
& delete statements (about 17500 lines including blank lines) that
can be run on different databases seperately
How we do this -
We have a SP - that creates a temporary table and then calls another
SP that actually populates the temporary table created by the first SP
*Samples of both SPs are below -
PROBLEM
The result is directed to a file -
However when the query is run it runs through the entire script but
'Jumbles' the output
Running the same scripts on a copy of the database on other machines
work fine and the size of the outfiles is exactly the same
I have increased the page size to 2.5 GB and restarted the server.
Running the sp now generated the correct output a few times but got
jumbled as before after a few more users logged in and activity on the
server increased.
Another interesting point is that the output is jumbled exactly the
same way each time. It seems the sql executes correctly and writes
the output in chunks only writting the chunks out of sequence - but
in the same sequence each time.
e.g. of expected result
Insert into Table1
Values x, y, z, 1, 2
Insert into Table1
Values q, s, g, 3, 4
Insert into Table1
Values c, d, e, 21, 12
....
Insert into Table2
Values ...
Insert into Table3
Values ....
...
...
...
...
Update RefGen
Set Last = 1234
Where RefGenRef = 1
JUMBLED OUTPUT
Insert into Table1
Values x, y, z, 1, 2
Insert into Table1
Values q, s, g, 3, 4
Insert into Table1
Values c, d, e, 21, 12
....
Insert into Table2
Values ...
Insert into Table2
Values ...
Values ...
Update RefGen
Set Last = 1234
Where RefGenRef = 1
Insert into Table3
Values ....
...
...
...
...
Insert into Table1
Values c, d, e, 21, 12
....
Insert into Table2
----------------------------------------
Sample of First Script - STATDATA_RSLT
**************************************
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT ON
GO
CREATE PROCEDURE StatData_rslt
AS
CREATE TABLE #tbl_Script(
ScriptText varchar(4000)
)
EXEC TestStatData_int
SELECT t.ScriptText
FROM #tbl_Script t
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
*******************************************
Sample of CALLED SP - TestStatData_int
*******************************************
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE TestStatData_int
AS
DECLARE @AttrRef int,
@TestID int,
@PrtTestRef int,
@AttrType tinyint,
@EdtblSw tinyint,
@NmValRef int,
@SrtTypeRef int,
@AttrStr varchar(20),
@TestStr varchar(20),
@PrtTestStr varchar(20),
@AttrTypeStr varchar(20),
@EdtblStr varchar(20),
@NmValStr varchar(20),
@SrtTypeStr varchar(20),
@TestRef int,
@Seq int,
@PrtRef int,
@Value varchar(255),
@TermDate datetime,
@AttrID int,
@DefSw tinyint,
@WantSw tinyint,
@TestRefStr varchar(20),
@SeqStr varchar(20),
@PrtStr varchar(20),
@TermDateStr varchar(255),
@AttrIDStr varchar(20),
@DefStr varchar(20),
@WantStr varchar(20),
@LanRef int,
@LanStr varchar(20),
@Code varchar(20),
@Desc varchar(255),
@MultiCode varchar(20),
@MultiDesc varchar(255),
@InhSw tinyint,
@InhStr varchar(20),
@InhFrom int,
@InhFromStr varchar(20),
@Lan_TestRef int,
@ActSw tinyint,
@ActSwStr varchar(20)
SELECT @Lan_TestRef = dbo.fn_GetTestRef('Lan')
INSERT INTO #tbl_Script
VALUES('')
-- Create tables
INSERT INTO #tbl_Script
VALUES ('CREATE TABLE #tbl_Test (AttrRef int, TestID int , PrtTestRef
int, AttrType tinyint, EdtblSw tinyint, NmValRef int, SrtTypeRef
int)')
INSERT INTO #tbl_Script
VALUES ('')
INSERT INTO #tbl_Script
VALUES('CREATE TABLE #tbl_TestAttr(AttrRef int, TestRef int, Seq int,
PrtRef int, AttrType tinyint, Value varchar(255), TermDate datetime,
AttrID int, DefSw tinyint, WantSw tinyint, ActSw tinyint)')
INSERT INTO #tbl_Script
VALUES ('')
INSERT INTO #tbl_Script
VALUES ('CREATE TABLE #tbl_AttrName(AttrRef int, LanRef int, Code
varchar(20), [Desc] varchar(255), MultiCode varchar(20), MultiDesc
varchar(255), InhSw tinyint, InhFrom int)')
INSERT INTO #tbl_Script
VALUES ('')
-- insert Test values
DECLARE Test_cursor CURSOR FOR
SELECT l.AttrRef, l.TestID, l.PrtTestRef, l.AttrType, l.EdtblSw,
l.NmValRef, l.SrtTypeRef
FROM Test l
OPEN Test_cursor
FETCH NEXT FROM Test_cursor
INTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,
@SrtTypeRef
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),
@TestStr = ISNULL(CAST(@TestID as varchar), 'NULL'),
@PrtTestStr = ISNULL(CAST(@PrtTestRef as varchar), 'NULL'),
@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),
@EdtblStr = ISNULL(CAST(@EdtblSw as varchar), 'NULL'),
@NmValStr = ISNULL(CAST(@NmValRef as varchar), 'NULL'),
@SrtTypeStr = ISNULL(CAST(@SrtTypeRef as varchar), 'NULL')
INSERT INTO #tbl_Script
VALUES ('INSERT INTO #tbl_Test(AttrRef, TestID, PrtTestRef,
AttrType,
EdtblSw, NmValRef, SrtTypeRef)')
INSERT INTO #tbl_Script
VALUES ('VALUES ( ' + @AttrStr + ', ' + @TestStr + ', ' +
@PrtTestStr
+ ', ' + @AttrTypeStr + ', ' + @EdtblStr + ', ' + @NmValStr + ', ' +
@SrtTypeStr + ')')
INSERT INTO #tbl_Script
VALUES ('')
FETCH NEXT FROM Test_cursor
INTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,
@SrtTypeRef
END
CLOSE Test_cursor
DEALLOCATE Test_cursor
DECLARE TestAttr_cursor CURSOR FOR
SELECT le.AttrRef, le.TestRef, le.Seq, le.PrtRef, le.AttrType,
le.Value,
le.TermDate, le.AttrID, le.DefSw, le.WantSw, le.ActSw
FROM TestAttr le
WHERE le.WantSw = 1
AND le.ActSw = 1
OPEN TestAttr_cursor
FETCH NEXT FROM TestAttr_cursor
INTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,
@TermDate, @AttrID, @DefSw, @WantSw, @ActSw
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),
@TestRefStr = ISNULL(CAST(@TestRef as varchar), 'NULL'),
@SeqStr = ISNULL(CAST(@Seq as varchar), 'NULL'),
@PrtStr = ISNULL(CAST(@PrtRef as varchar), 'NULL'),
@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),
@Value = ISNULL(@Value, 'NULL'),
@TermDateStr = ISNULL(CAST(@TermDate as varchar), 'NULL'),
@AttrIDStr = ISNULL(CAST(@AttrID as varchar), 'NULL'),
@DefStr = ISNULL(CAST(@DefSw as varchar), 'NULL'),
@WantStr = ISNULL(CAST(@WantSw as varchar), 'NULL'),
@ActSwStr = ISNULL(CAST(@ActSw as varchar), '1')
SELECT @Value = '''' + @Value + ''''
WHERE @Value <> 'NULL'
INSERT INTO #tbl_Script
VALUES ('INSERT INTO #tbl_TestAttr(AttrRef, TestRef, Seq, PrtRef,
AttrType, Value, TermDate, AttrID, DefSw, WantSw, ActSw)')
INSERT INTO #tbl_Script
VALUES ('VALUES (' + @AttrStr + ', ' + @TestRefStr + ', ' +
@SeqStr
+ ', ' + @PrtStr + ', ' + @AttrTypeStr + ', ' + @Value + ', ' +
@TermDateStr + ', ' + @AttrIDStr + ', ' + @DefStr + ', ' + @WantStr
+
', '+ @ActSwStr + ')')
INSERT INTO #tbl_Script
VALUES ('')
FETCH NEXT FROM TestAttr_cursor
INTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,
@TermDate, @AttrID, @DefSw, @WantSw, @ActSw
END
CLOSE TestAttr_cursor
DEALLOCATE TestAttr_cursor
DECLARE AttrName_cursor CURSOR FOR
SELECT e.AttrRef, e.LanRef, e.Code, e.[Desc], e.MultiCode,
e.MultiDesc, e.InhSw, e.InhFrom
FROM AttrName e, TestAttr le
WHERE e.LanRef = 0
AND e.AttrRef = le.AttrRef
AND le.WantSw = 1
AND le.ActSw = 1
OPEN AttrName_cursor
FETCH NEXT FROM AttrName_cursor
INTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,
@MultiDesc, @InhSw, @InhFrom
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),
@LanStr = ISNULL(CAST(@LanRef as varchar), 'NULL'),
@Code = ISNULL(@Code, 'NULL'),
@Desc = ISNULL(@Desc, 'NULL'),
@MultiCode = ISNULL(@MultiCode, 'NULL'),
@MultiDesc = ISNULL(@MultiDesc, 'NULL'),
@InhStr = ISNULL(CAST(@InhSw as varchar), 'NULL'),
@InhFromStr = ISNULL(CAST(@InhFrom as varchar), 'NULL')
SELECT @Code = REPLACE(@Code, '''',''''''),
@Desc = REPLACE(@Desc, '''','''''') ,
@MultiCode = REPLACE(@MultiCode, '''','''''') ,
@MultiDesc = REPLACE(@MultiDesc, '''','''''')
INSERT INTO #tbl_Script
VALUES ('INSERT INTO #tbl_AttrName(AttrRef, LanRef, Code, [Desc],
MultiCode, MultiDesc, InhSw, InhFrom)')
INSERT INTO #tbl_Script
VALUES ('VALUES (' + @AttrStr + ', ' + @LanStr + ', ''' + @Code +
''', ''' + @Desc + ''', ''' + @MultiCode + ''', ''' + @MultiDesc +
''',
' + @InhStr + ', ' + @InhFromStr + ')')
INSERT INTO #tbl_Script
VALUES ('')
FETCH NEXT FROM AttrName_cursor
INTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,
@MultiDesc, @InhSw, @InhFrom
END
CLOSE AttrName_cursor
DEALLOCATE AttrName_cursor
-- Do update TestAttr data
INSERT INTO #tbl_Script
VALUES ('UPDATE le')
INSERT INTO #tbl_Script
VALUES ('SET')
INSERT INTO #tbl_Script
VALUES (' le.TestRef = t.TestRef,')
INSERT INTO #tbl_Script
VALUES (' le.PrtRef = t.PrtRef,')
INSERT INTO #tbl_Script
VALUES (' le.AttrType = t.AttrType,')
INSERT INTO #tbl_Script
VALUES (' le.Value = t.Value,')
INSERT INTO #tbl_Script
VALUES (' le.TermDate = t.TermDate,')
INSERT INTO #tbl_Script
VALUES (' le.AttrID = t.AttrID,')
INSERT INTO #tbl_Script
VALUES (' le.DefSw = t.DefSw,')
INSERT INTO #tbl_Script
VALUES (' le.WantSw = t.WantSw,')
INSERT INTO #tbl_Script
VALUES (' le.ActSw = t.ActSw')
INSERT INTO #tbl_Script
VALUES ('FROM TestAttr le, #tbl_TestAttr t')
INSERT INTO #tbl_Script
VALUES ('WHERE le.AttrRef = t.AttrRef')
INSERT INTO #tbl_Script
VALUES ('')
-- Update AttrName
INSERT INTO #tbl_Script
VALUES ('UPDATE en')
INSERT INTO #tbl_Script
VALUES ('SET')
INSERT INTO #tbl_Script
VALUES (' en.Code = te.Code,')
INSERT INTO #tbl_Script
VALUES (' en.[Desc] = te.[Desc],')
INSERT INTO #tbl_Script
VALUES (' en.MultiCode = te.MultiCode,')
INSERT INTO #tbl_Script
VALUES (' en.MultiDesc = te.MultiDesc,')
INSERT INTO #tbl_Script
VALUES (' en.InhSw = te.InhSw,')
INSERT INTO #tbl_Script
VALUES (' en.InhFrom = te.InhFrom')
INSERT INTO #tbl_Script
VALUES ('FROM AttrName en, #tbl_AttrName te')
INSERT INTO #tbl_Script
VALUES ('WHERE en.AttrRef = te.AttrRef')
INSERT INTO #tbl_Script
VALUES (' AND en.LanRef = te.LanRef')
INSERT INTO #tbl_Script
VALUES (' AND te.LanRef = 0')
-- Do update Test the data
INSERT INTO #tbl_Script
VALUES ('UPDATE l')
INSERT INTO #tbl_Script
VALUES ('SET')
INSERT INTO #tbl_Script
VALUES (' l.TestID = t.TestID,')
INSERT INTO #tbl_Script
VALUES (' l.PrtTestRef = t.PrtTestRef,')
INSERT INTO #tbl_Script
VALUES (' l.AttrType = t.AttrType,')
INSERT INTO #tbl_Script
VALUES (' l.EdtblSw = t.EdtblSw,')
INSERT INTO #tbl_Script
VALUES (' l.NmValRef = t.NmValRef')
INSERT INTO #tbl_Script
VALUES ('FROM Test l, #tbl_Test t')
INSERT INTO #tbl_Script
VALUES ('WHERE l.AttrRef = t.AttrRef')
INSERT INTO #tbl_Script
VALUES ('')
--DELETE where just updated
INSERT INTO #tbl_Script
VALUES ('DELETE FROM t')
INSERT INTO #tbl_Script
VALUES ('FROM #tbl_Test t, Test l')
INSERT INTO #tbl_Script
VALUES ('WHERE t.AttrRef = l.AttrRef')
INSERT INTO #tbl_Script
VALUES ('')
INSERT INTO #tbl_Script
VALUES ('DELETE FROM t')
INSERT INTO #tbl_Script
VALUES ('FROM #tbl_TestAttr t, TestAttr le')
INSERT INTO #tbl_Script
VALUES ('WHERE t.AttrRef = le.AttrRef')
INSERT INTO #tbl_Script
VALUES ('')
INSERT INTO #tbl_Script
VALUES ('DELETE FROM te')
INSERT INTO #tbl_Script
VALUES ('FROM #tbl_AttrName te, TestAttr le')
INSERT INTO #tbl_Script
VALUES ('WHERE te.AttrRef = le.AttrRef')
INSERT INTO #tbl_Script
VALUES ('')
-- Insert TestAttr
INSERT INTO #tbl_Script
VALUES ('INSERT INTO TestAttr (AttrRef, TestRef, Seq, PrtRef,
AttrType,
Value, TermDate, AttrID, DefSw, WantSw, ActSw)')
INSERT INTO #tbl_Script
VALUES ('SELECT t.AttrRef, t.TestRef, t.Seq, t.PrtRef, t.AttrType,
t.Value, t.TermDate, t.AttrID, t.DefSw, t.WantSw, t.ActSw')
INSERT INTO #tbl_Script
VALUES ('FROM #tbl_TestAttr t')
INSERT INTO #tbl_Script
VALUES ('')
-- AttrName
INSERT INTO #tbl_Script
VALUES ('INSERT INTO AttrName(AttrRef, LanRef, Code, [Desc],
MultiCode,
MultiDesc, InhSw, InhFrom)')
INSERT INTO #tbl_Script
VALUES ('SELECT te.AttrRef, le.AttrRef, te.Code, te.[Desc],
te.MultiCode, te.MultiDesc, ')
INSERT INTO #tbl_Script
VALUES (' CASE le.AttrRef ')
INSERT INTO #tbl_Script
VALUES (' WHEN 0 THEN 0')
INSERT INTO #tbl_Script
VALUES (' ELSE 1 END,')
INSERT INTO #tbl_Script
VALUES (' CASE le.AttrRef ')
INSERT INTO #tbl_Script
VALUES (' WHEN 0 THEN NULL')
INSERT INTO #tbl_Script
VALUES (' ELSE 0 END')
INSERT INTO #tbl_Script
VALUES ('FROM #tbl_AttrName te, TestAttr le')
INSERT INTO #tbl_Script
VALUES ('WHERE le.TestRef = ' + CAST(@Lan_TestRef as varchar))
INSERT INTO #tbl_Script
VALUES ('')
-- Insert new rows
INSERT INTO #tbl_Script
VALUES ('INSERT INTO Test(AttrRef, TestID, PrtTestRef, AttrType,
EdtblSw, NmValRef, SrtTypeRef)')
INSERT INTO #tbl_Script
VALUES ('SELECT t.AttrRef, t.TestID, t.PrtTestRef, t.AttrType,
t.EdtblSw, t.NmValRef, t.SrtTypeRef')
INSERT INTO #tbl_Script
VALUES ('FROM #tbl_Test t')
INSERT INTO #tbl_Script
VALUES ('')
INSERT INTO #tbl_Script
VALUES ('DROP TABLE #tbl_Test')
INSERT INTO #tbl_Script
VALUES ('DROP TABLE #tbl_TestAttr')
INSERT INTO #tbl_Script
VALUES ('DROP TABLE #tbl_AttrName')
-- Update RefGen
DECLARE @RefGenReflast int,
@RefGenRefStr varchar(10)
SELECT @RefGenReflast = last
FROM RefGen
WHERE RefGenRef = 1
SELECT @RefGenRefStr = ISNULL(CAST(@RefGenReflast as varchar), 'NULL')
INSERT INTO #tbl_Script
VALUES('')
INSERT INTO #tbl_Script
VALUES('UPDATE RefGen')
INSERT INTO #tbl_Script
VALUES ('SET Last = ' + @RefGenRefStr)
INSERT INTO #tbl_Script
VALUES ('WHERE RefGenRef = 1')
INSERT INTO #tbl_Script
VALUES ('')
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
*******************************
Regards
Glenn