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


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

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 MemorySQL 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) thatcan be run on different databases seperatelyHow we do this -We have a SP - that creates a temporary table and then calls anotherSP that actually populates the temporary table created by the first SP*Samples of both SPs are below -PROBLEMThe result is directed to a file -However when the query is run it runs through the entire script but'Jumbles' the outputRunning the same scripts on a copy of the database on other machineswork fine and the size of the outfiles is exactly the sameI 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 gotjumbled as before after a few more users logged in and activity on theserver increased.Another interesting point is that the output is jumbled exactly thesame way each time. It seems the sql executes correctly and writesthe output in chunks only writting the chunks out of sequence - butin the same sequence each time.e.g. of expected resultInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table3Values ................Update RefGenSet Last = 1234Where RefGenRef = 1JUMBLED OUTPUTInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table2Values ...Values ...Update RefGenSet Last = 1234Where RefGenRef = 1Insert into Table3Values ................Insert into Table1Values c, d, e, 21, 12....Insert into Table2----------------------------------------Sample of First Script - STATDATA_RSLT**************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOSET NOCOUNT ONGOCREATE PROCEDURE StatData_rsltASCREATE TABLE #tbl_Script(ScriptText varchar(4000))EXEC TestStatData_intSELECT t.ScriptTextFROM #tbl_Script tGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************************Sample of CALLED SP - TestStatData_int*******************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE PROCEDURE TestStatData_intASDECLARE @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_ScriptVALUES('')-- Create tablesINSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_Test (AttrRef int, TestID int , PrtTestRefint, AttrType tinyint, EdtblSw tinyint, NmValRef int, SrtTypeRefint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES('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_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_AttrName(AttrRef int, LanRef int, Codevarchar(20), [Desc] varchar(255), MultiCode varchar(20), MultiDescvarchar(255), InhSw tinyint, InhFrom int)')INSERT INTO #tbl_ScriptVALUES ('')-- insert Test valuesDECLARE Test_cursor CURSOR FORSELECT l.AttrRef, l.TestID, l.PrtTestRef, l.AttrType, l.EdtblSw,l.NmValRef, l.SrtTypeRefFROM Test lOPEN Test_cursorFETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefWHILE @@FETCH_STATUS = 0BEGINSELECT @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_ScriptVALUES ('INSERT INTO #tbl_Test(AttrRef, TestID, PrtTestRef,AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('VALUES ( ' + @AttrStr + ', ' + @TestStr + ', ' +@PrtTestStr+ ', ' + @AttrTypeStr + ', ' + @EdtblStr + ', ' + @NmValStr + ', ' +@SrtTypeStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefENDCLOSE Test_cursorDEALLOCATE Test_cursorDECLARE TestAttr_cursor CURSOR FORSELECT le.AttrRef, le.TestRef, le.Seq, le.PrtRef, le.AttrType,le.Value,le.TermDate, le.AttrID, le.DefSw, le.WantSw, le.ActSwFROM TestAttr leWHERE le.WantSw = 1AND le.ActSw = 1OPEN TestAttr_cursorFETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwWHILE @@FETCH_STATUS = 0BEGINSELECT @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_ScriptVALUES ('INSERT INTO #tbl_TestAttr(AttrRef, TestRef, Seq, PrtRef,AttrType, Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @TestRefStr + ', ' +@SeqStr+ ', ' + @PrtStr + ', ' + @AttrTypeStr + ', ' + @Value + ', ' +@TermDateStr + ', ' + @AttrIDStr + ', ' + @DefStr + ', ' + @WantStr+', '+ @ActSwStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwENDCLOSE TestAttr_cursorDEALLOCATE TestAttr_cursorDECLARE AttrName_cursor CURSOR FORSELECT e.AttrRef, e.LanRef, e.Code, e.[Desc], e.MultiCode,e.MultiDesc, e.InhSw, e.InhFromFROM AttrName e, TestAttr leWHERE e.LanRef = 0AND e.AttrRef = le.AttrRefAND le.WantSw = 1AND le.ActSw = 1OPEN AttrName_cursorFETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromWHILE @@FETCH_STATUS = 0BEGINSELECT @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_ScriptVALUES ('INSERT INTO #tbl_AttrName(AttrRef, LanRef, Code, [Desc],MultiCode, MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @LanStr + ', ''' + @Code +''', ''' + @Desc + ''', ''' + @MultiCode + ''', ''' + @MultiDesc +''',' + @InhStr + ', ' + @InhFromStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromENDCLOSE AttrName_cursorDEALLOCATE AttrName_cursor-- Do update TestAttr dataINSERT INTO #tbl_ScriptVALUES ('UPDATE le')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' le.TestRef = t.TestRef,')INSERT INTO #tbl_ScriptVALUES (' le.PrtRef = t.PrtRef,')INSERT INTO #tbl_ScriptVALUES (' le.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' le.Value = t.Value,')INSERT INTO #tbl_ScriptVALUES (' le.TermDate = t.TermDate,')INSERT INTO #tbl_ScriptVALUES (' le.AttrID = t.AttrID,')INSERT INTO #tbl_ScriptVALUES (' le.DefSw = t.DefSw,')INSERT INTO #tbl_ScriptVALUES (' le.WantSw = t.WantSw,')INSERT INTO #tbl_ScriptVALUES (' le.ActSw = t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM TestAttr le, #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('WHERE le.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Update AttrNameINSERT INTO #tbl_ScriptVALUES ('UPDATE en')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' en.Code = te.Code,')INSERT INTO #tbl_ScriptVALUES (' en.[Desc] = te.[Desc],')INSERT INTO #tbl_ScriptVALUES (' en.MultiCode = te.MultiCode,')INSERT INTO #tbl_ScriptVALUES (' en.MultiDesc = te.MultiDesc,')INSERT INTO #tbl_ScriptVALUES (' en.InhSw = te.InhSw,')INSERT INTO #tbl_ScriptVALUES (' en.InhFrom = te.InhFrom')INSERT INTO #tbl_ScriptVALUES ('FROM AttrName en, #tbl_AttrName te')INSERT INTO #tbl_ScriptVALUES ('WHERE en.AttrRef = te.AttrRef')INSERT INTO #tbl_ScriptVALUES (' AND en.LanRef = te.LanRef')INSERT INTO #tbl_ScriptVALUES (' AND te.LanRef = 0')-- Do update Test the dataINSERT INTO #tbl_ScriptVALUES ('UPDATE l')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' l.TestID = t.TestID,')INSERT INTO #tbl_ScriptVALUES (' l.PrtTestRef = t.PrtTestRef,')INSERT INTO #tbl_ScriptVALUES (' l.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' l.EdtblSw = t.EdtblSw,')INSERT INTO #tbl_ScriptVALUES (' l.NmValRef = t.NmValRef')INSERT INTO #tbl_ScriptVALUES ('FROM Test l, #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('WHERE l.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')--DELETE where just updatedINSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t, Test l')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = l.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM te')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE te.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Insert TestAttrINSERT INTO #tbl_ScriptVALUES ('INSERT INTO TestAttr (AttrRef, TestRef, Seq, PrtRef,AttrType,Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('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_ScriptVALUES ('FROM #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('')-- AttrNameINSERT INTO #tbl_ScriptVALUES ('INSERT INTO AttrName(AttrRef, LanRef, Code, [Desc],MultiCode,MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('SELECT te.AttrRef, le.AttrRef, te.Code, te.[Desc],te.MultiCode, te.MultiDesc, ')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN 0')INSERT INTO #tbl_ScriptVALUES (' ELSE 1 END,')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN NULL')INSERT INTO #tbl_ScriptVALUES (' ELSE 0 END')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE le.TestRef = ' + CAST(@Lan_TestRef as varchar))INSERT INTO #tbl_ScriptVALUES ('')-- Insert new rowsINSERT INTO #tbl_ScriptVALUES ('INSERT INTO Test(AttrRef, TestID, PrtTestRef, AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestID, t.PrtTestRef, t.AttrType,t.EdtblSw, t.NmValRef, t.SrtTypeRef')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_Test')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_TestAttr')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_AttrName')-- Update RefGenDECLARE @RefGenReflast int,@RefGenRefStr varchar(10)SELECT @RefGenReflast = lastFROM RefGenWHERE RefGenRef = 1SELECT @RefGenRefStr = ISNULL(CAST(@RefGenReflast as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES('')INSERT INTO #tbl_ScriptVALUES('UPDATE RefGen')INSERT INTO #tbl_ScriptVALUES ('SET Last = ' + @RefGenRefStr)INSERT INTO #tbl_ScriptVALUES ('WHERE RefGenRef = 1')INSERT INTO #tbl_ScriptVALUES ('')GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************RegardsGlenn

View 5 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

Output An SQL Query To A Text File

Jul 23, 2005

Hi,How would I go about writing the results of an SQL query to a textfile?I cannot find any info in the Online help files.For example, I would like the results of:SELECT * FROM TableATo be written to the file result.txtAlex

View 10 Replies View Related

Query Analyzer - Not Printing Output

Jul 20, 2005

Is there something that can be set in SQL to not print output to thescreen? Something like SET NOCOUNT ON, only in this case, I don'twant it to print any output except what my script specifies in PRINTstatements. Any ideas? Thank you.

View 3 Replies View Related

Can A Query Using OUTPUT Statement Be SELECTED From?

Oct 19, 2007

I tried wrapping a DELETE FROM ...OUTPUT DELETED.* WHERE... inside of a select, crossed my fingers and got a syntax error. I thought maybe since the OUTPUT statement produces a rowset that it and it's DELETE could be selected from. Is such a thing possible in the same query or should I give up?

View 5 Replies View Related

Sp_send_dbmail: Need Output To A Query Without The Messages.

Feb 27, 2008

I am writing a stored procedure which finally has to send mail to the customer with a list of phones that have been added to his account. I am getting these phones from a SQL Table in the form of a query, and including the output of the query(list of phones) , in the body of the mail.

There are two issues that I am unable to resolve,

1. I just want the output with no messages.



eg. 8887775567

not


8887777767 ( 1 row(s) effected)



2. One phone number per row.



8887775567
8009978776
6679800077

NOT


8887775567 8009978776 6679800077


Thanks in advance.




View 8 Replies View Related







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