Query Produces Jumbled Output / Output Not In Sequence

Jul 23, 2005

Hi!

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

View 5 Replies


ADVERTISEMENT

Number Of ROWS Of Output Of Aggregate Transformation Sometimes Doesn't Match The Output From T-SQL Query

Dec 25, 2006

While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.

For example,the output of the Aggregate Transformation may be 960216 ,but the

'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.

I'm sure the Group By of the Aggregate Transformation is right!



But ,when I set the "keyscale" property of the transformation,the results match!

In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.

Thanks for your advice.

View 2 Replies View Related

Transact SQL :: Generic Store Procedure Call Without Output Parameters But Catching Output

Sep 21, 2015

Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])

EX1 : @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 : @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 : @SPToCall : [sp_GetSomeResult]

I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working

DECLARE @myFinalVarFilledWithCachedOutput 
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'

[code]...

View 3 Replies View Related

Output And Error Output Write The Same Table At The Same Time, Stall The Process.

Aug 30, 2006

Hi

I have Lookup task to determine if source data should be updated to or insert to the customer table. After Lookup task, the Error Output pipeline will redirect to insert new data to the table and the Output pipeline will update customer table. But these two tasks will be processing at the same time which causes stall on the process. Never end.....

The job is similiart to what Slow Changing Dimention does but it won't update the table at the same time.

What can I do to avoid such situation?

Thanks in advance,

JD

View 4 Replies View Related

Using Output From A Stored Procedure As An Output Column In The OLE DB Command Transformation

Dec 8, 2006

I am working on an OLAP modeled database.

I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).

I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.

The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.

What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?

I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.

Thanks in advance for any assistance you can provide.

View 9 Replies View Related

PrimeOutput : Difference Between 'Output' And 'output Buffer'

Aug 12, 2005

When overriding the PrimeOutput method in a custom component, you get as parameters the outputIDs and the output buffers (of type PipelineBuffer). using the outputIDs you can get IDTSOutput90 outputs.

View 5 Replies View Related

SQL Query Output To XML Using BCP HELP!!

Aug 24, 2007

I am having trouble in writing code to save the SQL query output to an XML file.

A simple example like below is working fine and it creates an output a.xml.

------Begin Code-----
Declare @fNameTemp varchar(50)
Declare @SQL varchar(3000)
Set @fNameTemp = 'c:XMLDumpa.xml'

Set @SQL = 'bcp "Select top 10 * From test..month For XML Auto, Elements" QueryOut "' + @fNameTemp + '" -c -t, -T -S'
Exec master..xp_cmdshell @SQL
------End Code ------

But I am having trouble in writing when I change the Select query to a more complex one as in the example below. The code is part of SP.

------Begin Code-----
USE [test]
GO
Declare @TblName1 varchar(10)
Declare @TblName2 varchar(10)
Declare @District varchar(6)
Declare @Month varchar(3)
Declare @TblType varchar(10)
Declare @Thisyear varchar(10)
Declare @Lastyear varchar(10)

SET @TblName1 = '2006exp'
SET @TblName2 = '2005exp'
SET @District = 1
SET @Month = 2
SET @TblType = 'exp1'
set @Thisyear = '2006'
set @Lastyear = '2005'

set @TblName1 = '[' + @TblName1 + ']'
set @TblName2 = '[' + @TblName2 + ']'
set @TblType = '[' + @TblType + ']'

Declare @fNameTemp varchar(50)
SET @fNameTemp = 'C:XMLDump2.xml'
Declare @SQuery nvarchar(3000)

SET @SQuery = 'bcp "Select d.descrip_1 as [Commodity Description], ty.HS4, ty.Amount1 as ['+ @ThisYear +' Value ($)], (ty.Amount1/ty.Total1)*100 as ['+ @ThisYear +' Share (%)] ,
py.Amount2 as ['+ @LastYear +' Value ($)], (py.Amount2/py.Total2)*100 as ['+ @LastYear +' Share (%)]
FROM
(Select top 10 a.commodity1 as HS4, Sum(a.all_val_mo) as Amount1,
(select Sum(a1.all_val_mo) FROM test..' + @TblName1 + ' a1 where a1.stat_month <=' + @Month + ' and a1.district=' + @District +' ) as Total1
FROM test..' + @TblName1 + ' a where a.stat_month <=' + @Month + ' and a.district=' + @District +'
Group by a.commodity1 Order by Amount1 DESC) ty
JOIN
(Select b.commodity1 as HS4, Sum(b.all_val_mo) as Amount2,
(select Sum(b1.all_val_mo) FROM test..' + @TblName2 + ' b1 where b1.stat_month <=' + @Month + ' and b1.district=' + @District +' ) as Total2
FROM test..' + @TblName2 + ' b where b.stat_month <=' + @Month + ' and b.district=' + @District +'
Group by b.commodity1) py on ty.HS4=py.HS4 LEFT OUTER JOIN '+ @TblType +' d on ty.HS4=d.commodity1
Order by Amount1 DESC FOR XML AUTO, Elements" QueryOut "' + @fNameTemp + '" -c -t, -T -S'

Exec master..xp_cmdshell @SQuery
------End Code ------

Output for above code is shown below:
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL


I don't know what is the mistake I am doing. I would appreciate if anyone can help me figure the error.
http://www.fiu.edu/~atmakurk/ustrade/SQL_XML1.jpg


The above query works perfectly if I remove BCP & For XML Auto part. Here is a screen shot of Successful query results.
http://fiu.edu/~atmakurk/ustrade/working_example.jpg

View 1 Replies View Related

Issues With Sql Query Output

Jun 7, 2008

Iam using the below XML request with OpenXml function in sqlserver to get field values.
But iam not able to get values for  "DeviceType,DeviceID,FeatureLink" also i want to format the value of the devicetype field
<Device Type="KWS,KL,2007,6"> it should return "KWS" alone how can i do that please advice.
DECLARE @idoc intDECLARE @doc varchar(1000)SET @doc ='<Response ><DetaisInfo>   <Tag/>   <InstrService>DEVICE_INFO</InstrService></DetaisInfo><Devices>   <Device Type="KWS,KL,2007,6">     <Field name="DeviceType">0.00</Field>     <Field name="DeviceID">12.00</Field>     <Field name="FeatureLink">25700</Field>   </Device>   <Device Type="SNY,KL,2007,6">     <Field name="DeviceType">1.11</Field>     <Field name="DeviceID">0.10</Field>     <Field name="FeatureLink">21667</Field>   </Device></Devices></Response>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @docSELECT *FROM   OPENXML (@idoc, '/Response/Devices/Device',2)         WITH (Type       varchar(10)         './@Type',               DeviceType  varchar(10) '/@name',               DeviceID   varchar(10)    '/@name',               FeatureLink     varchar(10)         '/@name')          
OutPut
Type                   DeviceType      DeviceID      FeatureLink ----------                ----------             ----------          ----------- KWS,KL,200        NULL                NULL          NULLSNY,KL,200         NULL                NULL          NULL
(2 row(s) affected)
Please advice

View 6 Replies View Related

Output Query To A File

Jul 25, 2001

I have a query something like this:
select "bcp EISAT_08_18.."+name +" OUT C:"+ name+".TXT -c -t -SCJACOBI"
from sysobjects
where type = 'U'
ORDER BY NAME
When I run the above query I want to output the result of the query to a file.
Can someone help me on that?

View 8 Replies View Related

Output The Query Into A File

Jul 31, 2001

Hi all,
When I run a query in the sql query analyzer I need to write the output of that query in to another file. In Oracle its spool. Can someone help me on this please. Thank you!!!

View 1 Replies View Related

Reformatting Output From A Query

Jul 26, 2004

Hey everyone,

as of now i have a sql chunk


Code:


SELECT VIEW.DWG_NAME
FROM VIEW, qry_TURNOVER
WHERE (((VIEW.ID)=[qry_TURNOVER].[CURRENT_ID]));



which works but when i get the results it orders them according to number ( or what seems to be in the order of ID), and i need it in the order that it appears in the column CURRENT_ID

If that is not clear This is what is happening

For example, we have:

qry_TURNOVER
CURRENT_ID
4
5
1
3

VIEW
ID DWG_NAME
1 23g
2 24H
3 25J
4 26K
5 27L

As it stands,The results auto format to:

23G
25H
26K
27L

I need the results to be:

26k
27L
23G
25J


Hope that was clear, any ideas? Im not sure if this is even possible. Any hints or tips to a solution would be much appreciated. The reason why current ID is unordered is because its has other data in its table that is ordered via that way. So current ID cannot be orderd to match out out, it must be the other way around.

Thanks
Doc

View 3 Replies View Related

Query Output Into A File

Feb 27, 2006

Hi,

In a stored procedure how do I output the result of a query to a text file?

Regards,

Bharathram G

View 3 Replies View Related

OSQL Query Output

Dec 10, 2007

Hi All,

The command showed below provides the output shown:

set @cmd = 'osql -S bcgsql1cgsqldev -U checkdb -P checkdb -q "set nocount on; select errorlogdesc from ##errors where datediff(dd,errorlogdatetime,getdate()) = 1 and errorlogdesc like ''%Error:%''" -h-1 -w 900 -o J:ScriptsTestStuffErrorLogMsg.txt'


Here is the output:

Error: 17883, Severity: 1, State: 0




Error: 17883, Severity: 1, State: 0


I need to remove the space between the two lines so I set the -w parameter to 900 but that didn't fix it. Any idea how I can accomplish this?

Thanks

View 4 Replies View Related

Query Output In A Desired Way

Aug 11, 2006

Gopinath writes "Let us consider we have the following data in a table,

Column1 Column2
John orange
John apple
John Grape
Steve orange
Steve watermelon
Steve pineapple

in the above table, i want to query and get output in the below format

Column1 Column2
John orange
apple
Grape
Steve orange
watermelon
pineapple

is it possible? if yes, kindly let me know the query.

Thanks in advance.

Regards,
Gopi."

View 2 Replies View Related

Output Query To Xml File

Apr 13, 2006

I have an sp that runs a query in xp_cmdshell/BCP combo that puts the output into an XML file (using for xml auto hint on the end of the query).

I personally feel that I should now (under SQL 2K5) be able to do this within a query, not going any where near xp_cmdshell or bcp, and then use SSIS in the final step to ftp it up to the clients site.

It builds a command string in the sp, then runs that command string via xp_cmdshell.

I guess what I'm asking is, is it possible to run a query within an SP with the output going to an XML file on the server automatically? AND that file to be a well formed XML file?

Any suggestions?

View 4 Replies View Related

Redirecting Query Output

Feb 27, 2008

Hi

Is there any way to redirect results to file directly while executing query? Like in management studio we can redirect result to Text or File.(Option Result to File)

Regards
Arun.M

View 13 Replies View Related

Output Query Result To A CSV File

Aug 29, 2007

Hi,
I want to export/output result of a query to a CSV file using SQL Server 2005. How can i do it ? I just want to do it all using SQL Server 2005 query window without having to use some 3rd Party control or software. Is it possible and how? Is there some SP which can convert the result to a CSV File ?
Thanking you...

View 4 Replies View Related

Output Of A Query In Sql Server 2000

Oct 4, 2007

Hi,
 I am using SQL SERVER 2000.
I need a procedure/ method where i need to display the output value of a particular query.
As I wrote the following procedure  
CREATE proc proc1as beginDeclare @str nvarchar(100)set @str =  'select top 5 * from CT_TM_EmployeeMaster'print @strend
the output is giving "select top 5 * from CT_TM_EmployeeMaster"
I need the output of the above query. What should i do?
 
Thanks & Regards,
JaiShankar

View 4 Replies View Related

Simple Query Or Resultset Output

Feb 7, 2008

I'm learning ASP.net, transitioning from ColdFusion. So far so go... but there is 1 thing that I can't seem to find a simple answer to. How can I do a simple output of a query resultset  to the screen or to a label?
 In CF it would be simple like this:
<cfquery name="getSomeData" datasource="somedatasource">SELECT somestuffFROM sometableWHERE something = something</cfquery>
<cfoutput>#getsomedata.somestuff#</cfoutput>
FYI: using the queryname.column returns only the first row in the CF query result
 I have seen examples with:
label.text = somevarname
which doesn't work for the query... but what about the query result?
thanks
 

View 2 Replies View Related

Trying To Get Output Parameter From TSQL Query

Apr 10, 2008

I am trying to return an ouput parameter from my query. I have tested the stored proceedure extensivly within the SQL Management Studio, and I know it works fine, so that means the error is somewhere within my code, but for the life of my I cant figure where.
Here is my stored proc:
  set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:Name
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[tblSiteVisits_FindOfficerName]
@VisitID int,
@OfficerName varchar(100) output
AS
BEGIN
-- Variables
Declare @OfficerID int
--Declare @OfficerName varchar(100)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Select @OfficerID = (Select top 1
OfficerID from tblSiteVisitOfficers Where VisitID = @VisitID)

IF (@OfficerID Is Null)
BEGIN -- Get the None Registered Officer
Select @OfficerName = (Select top 1 OfficerOther from dbo.tblSiteVisitOfficers Where VisitID = @VisitID)
print 'Got unregistered Officer ' + @OfficerName
END
ELSE
BEGIN -- Get the Registered Officer
Select @OfficerName = (Select OfficerFName + ' ' + OfficerLname from dbo.tblOfficers Where OfficerID = @OfficerID)
print 'Got Registered Officer ' + @OfficerName
END
END
 
And here is the code I am using to access this proceedure:1 Dim blah As String
2 Dim conn2 As New SqlConnection()
3 Dim cmd2 As New SqlCommand()
4 conn2.ConnectionString = _ConnString
5 cmd2.Connection = conn2
6 cmd2.CommandType = CommandType.StoredProcedure
7 cmd2.CommandText = "dbo.tblSiteVisits_FindOfficerName"
8
9 cmd.Parameters.AddWithValue("@VisitID", Convert.ToInt32(row("VisitID")))
10 cmd.Parameters.Add("@OfficerName", SqlDbType.VarChar, 100)
11 cmd.Parameters("@OfficerName").Direction = ParameterDirection.Output
12 Try
13 conn.Open()
14 cmd.ExecuteNonQuery()
15 blah = cmd.Parameters("@OfficerName").Value.ToString()
16
17 Catch ex As Exception
18 Throw ex
19 Finally
20 conn.Close()
21 End Try
22

 
However there I never recieve the output value, and because of the way my database is structures, there is no possible way, that there is no output value.If anyone can help, that would be great, kind regards.

View 2 Replies View Related

Output Results Of Query To A Txt File

Apr 3, 2001

I have come across manual queries being run daily and the results saved to a txt file on the network.

I basically want to set run these up a stored procedure and set up as a scheduled task to run daily.

Does anyone know if you can automatically save the results of a query/stored procedure to a text file on a network??

Many Thanks..

View 1 Replies View Related

Sending Query Output To A File

Aug 22, 2001

HI,
I have a small problem with SQL server 7.0 where I have to write the output of a query directly to a file without the user going to the menu and clicking on save. Scenario is:
A file is having a query the results of which has to be outputted to a file like in oracle where we have spooling function. Is there any functionality that mimic the oracle functionality where by I can get the output directly to a file?
thanks for your help in advance.
thanks,
Sravan.

View 2 Replies View Related

Query Result To Output File

Jan 24, 2001

Hi All

I am using cursor in my SP, according to my condition I might be getting around 600 records all this records to send to text file, I have tried few option some how I am able to create file and I am geeting only the last record in my output file, I want to know how can I append records into output file(text file), If some one can give me some suggestion that will be great.

Thanks in advance

Regards
Ram

View 1 Replies View Related

Query Output To Flatfile Wraps

Feb 23, 2001

I am running an ISQL script every day and automatically emailing the output of a query as the body of an email, to a group of people. ISQL executes a SQL file which selects some data and the output of the ISQL is sent to a *.txt file.

The Issue is that the output of the query wraps in the flatfile. It seems that the *.txt file wraps at character 76.

Does anyone have an Idea how to prevent query output data from wrapping in the flatfile?

-thanks
-tom

View 1 Replies View Related

Using Bcp To Save Query Output In Excel

Jul 20, 2004

I am using bcp to get the query output in excel file. But I am not able to see the column headings. Is there a way to get the column heading also in the output file.

the command I am using is this.

declare @x varchar(300)

set @x = 'bcp "select * from Northwind..orders" queryout c: est.csv -S local -U sa -P passwd -c -C RAW -t "," -r '

exec master..xp_cmdshell @x

go

View 7 Replies View Related

Using OSQL For Query Output (csv Formatted)

Jun 12, 2006

I'm running a query with osql, and I'm trying to get some clean output that is comma delimited. So far my line looks like this:osql -E -n -d mydb -i custom.qry -o "c:output
esults.csv" -h-1 -s ","This works off a table with only two columns. I'm still left with a lot of extra spaces between the first column output and second column output, and at the bottom the text "(50 rows affected)".So instead of this:data1a (lots of spaces here) ,data1bdata2a (lots of spaces here) ,data2bdata3a (lots of spaces here) ,data3b(50 rows affected)I want to see this:data1a,data1bdata2a,data2bdata3a,data3bThanks very much for any help. :)

View 2 Replies View Related

6.5 Vs 8.0 Query Output. Please Shine Some Light.

Mar 19, 2007

Hi guys,
Quick question (I hope).
Why is there a difference in the output of this query in 6.5 campatibility vs 8.0 campatibility?

DECLARE@FooterLinevarchar(30)

select@FooterLine = rtrim(@FooterLine) + 'Run by Region'

select @footerline

6.5 returns Run by Region
8.0 returns NULL

View 2 Replies View Related

How To Get Quantity From Output Of Query Command

Jun 7, 2012

how to get the quantity from output of sql query command for example, I could get the container name by below command

select container_name from sysibmadm.snapcontainer
get the container number by
select TBSP_NUM_CONTAINERS from SYSIBMADM.SNAPTBSP_PART

now I want to get the container number by below command output result select container_name from sysibmadm.snapcontainer..so what more command should I add on above command, I mean I want to get the container number by container name from the output of above command, not by 'join'.

View 1 Replies View Related

T-SQL (SS2K8) :: Job Details In Query Output

May 1, 2015

I have a T-SQL query that outputs exactly as I wish when run via Management Studio. I now want to automate this query to run monthly via a SQL Agent Job.

I have got this to run but the resulting file is left with a header row which shows "Job 'MyJobName' : Step 1, 'My QUERY' : Began Executing 2015-05-01 10:52:01" and an extra blank row before my data (and column headers).

Is there any way of stopping these extra rows from being created using T-SQL in my query?

View 2 Replies View Related

Create Table From Query Output

May 5, 2008

I have a query that is spliting up FullName into FistName and LastName columns. Need help writing a query that dump that output into a new table.

Thanks
Kevin

View 6 Replies View Related

Create Table From Query Output

Oct 25, 2013

I have the following sql query in vb.net and ms access , how do I create a table from the query result ?

SELECT 'Table1' AS [Table], SUM(a) - SUM(b) AS Result FROM table1

I have tried but it does not work

create table tble10 as SELECT 'Table1' AS [Table], SUM(a) - SUM(b) AS Result FROM table1

View 14 Replies View Related

How To Output Text File In Query

Aug 28, 2014

how to output txt file in query by simple way ? This query have error.

Select * from invoice
into 'c:abc.txt'

View 8 Replies View Related

XML Output Serialized For Remote Query

Jul 23, 2005

Hi,Im running into a strange problem here!I have created a linked server on one of my SQL Servers (local server).Lets call the linked server as LS1.I have an sproc which resides on the remote server(LS1) and uses a FORXML query.On running this sproc from my local server using linked server LS1, Iget a strange serialized data as output instead of the usual XMLoutput.I run the proc asexec [LS1].[DBName].[UserName].[SprocName] '12/1/2004','12/3/2004'the proc accepts 2 parameters as dates.The output generated is smth like:0x44046E0061006D0065004404440061.......(long string of hex codes)Looks like the ouput is getting serialized. How can I avoidserialization OR How to de-serialize this data?Please HelpThanks,Geo

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved