How to write query result in an output file in T-SQL (MSDE)

Question

Post reply

How to write query result in an output file in T-SQL (MSDE)

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

Ramesh Saive

SSC-Insane

Points: 24275

November 26, 2007 at 6:05 am

#753988

Hey Jeff,

Just to correct you that we don't need SA privileges to use sp_oa* procedures, we just need execute permissions on these.

--Ramesh

littlesquall SSC Eights! Points: 979 More actions · Answer 2

When you write the result of a "select * from..." in an output file with xp_cmdShell command, the result is saved like in the exemple.

It's an exemple of what I would like to write in the file.

However, I don't want the lines with all the fields (about 150 for my table).

In addition, I want the values in only one line; with all values separated by a "|" or a ";" separator.

The exemple below show that the returned result written in the outpu file is unexploitable.

See by yourself :

==================

adresse_2 code_post_2 loc_2 code_dep niv date_enc origine depart concur date_prev_act date_next_act num1 num2 num3 num4 num5

var1 var2 var3 var4 var5 cli fou num6 num7 var6 var7 var8 var9

var10 susp pro dat1 dat2 dat3 rid rmod livr paymt devise esc livraison facturation type_next_act type_prev_act rid_next_act

rid_prev_act copies nrid fact_so0_nrid livr_so0_nrid complement bpostal type ca credit exmail titulaire societe tel1 fax

tel2 adresse pays loc registre no_tva banque titre cd

code_post code_sec interet reg_code prefixe_int societe_2 depart_2 bpostal_2 pays_2 societe_3 depart_3

adresse_3 bpostal_3 code_post_3 loc_3 pays_3 complement_2 complement_3 langue removed date_susp date_pro date_cli date_concur date_removed

template template_name e_mail complement2 complement2_2 complement2_3 reg_code_2

reg_code_3 dmod street_nb street_nb2 street_nb3 web_link

scheduleid

adr_line_1

adr_line_2

adr_line_3

adr_line_4

adr_line_5

adr_line_6

adr_line_7

adr_line_8 and_check1 and_check2 and_check3 recordsend notesid lastexport nrid_next_act nrid_prev_act var11 var12 var13

var14 var15 var16 var17 var18 var19 var20 var21 var22 var23 var24 var25 var26 var27 num8

num9 dat4 dat5 var28 var29 var30 var31 var32 var33 var34 var35 var36 var37 var38 var39 num10

num11 num12 num13 num14 num15 num16 num17 num18 dat6 num19 num20 var40 var41 var42 var43 var44 var45

-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------- ----------- ----------------------- ------------------------------ -------------------------------------------------- ----------- ----------------------- ----------------------- --------------------------- --------------------------- --------------------------- --------------------------- ---------------------------

------------------------------ -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------- ----------- --------------------------- --------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------ ------------------------------

------------------------------ ----------- ----------- ----------------------- ----------------------- ----------------------- -------------------------------- -------------------------------- -------- ------ ------ --------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------

-------------------------------- --------------------------- ----------------- ----------------- ----------------- -------------------------------------------------- -------------------- ------------------------------ --------------------------- --------------------------- ----------- ------------------------------ -------------------------------------------------- -------------------------------------------------- --------------------------------------------------

-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------

-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------ ----------- -------------------------------------------------- -------------------------------------------------- -------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------

-------------------------------------------------- -------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------ ----------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------

----------- ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------

------------------------------ ----------------------- ------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ----------- ------------------------------ ----------------------- ----------------- ----------------- ------------------------------ ------------------------------ ------------------------------

------------------------------ ------------------------------ ------------------------------------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------

--------------------------- ----------------------- ----------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------

--------------------------- --------------------------- ----------------- ----------------- ----------------- --------------------------- --------------------------- --------------------------- ----------------------- --------------------------- --------------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------- ---------------

NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 38.000000000 38.000000000 .000000000 .000000000 NULL

NULL NULL GRANDES ENSEIGNES GSS GITEM 0 NULL NULL NULL GITEM ORSAY LOGITEC P 0001012294

N. Caspar NULL NULL 2000-05-03 00:00:00.000 2004-09-01 00:00:00.000 NULL so0UPG2007-01-31-15.14.00.007000 so0UPG2007-10-03-19.42.00.730000 NULL NULL NULL NULL NULL NULL NULL NULL NULL

NULL NULL 24001092241410 NULL NULL NULL NULL G NULL 2.000000000 NULL NULL ETS COCARDON/GITEM 0181 03 25 39 83 05 03 25 24 98 06

NULL 17-18 RUE DES FOSSES NULL NOGENT SUR SEINE NULL NULL NULL NULL NULL

10400 NULL NULL Aube NULL NULL NULL NULL NULL NULL NULL

NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

NULL NULL NULL NULL NULL NULL NULL

NULL 2007-10-03 19:42:00.730 NULL NULL NULL NULL

NULL

NULL NULL NULL NULL NULL NULL NULL NULL NULL T. Gentien Nord-Est Fermé

NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

NULL NULL NULL Business to Business G20 Z04 AG05 NULL CD01 IC02 29 0001017592 0001017513 NULL NULL NULL

NULL NULL 24904292481411 24135792351410 NULL 24175692271410.000000000 24246792271410.000000000 24135792351410.000000000 NULL 24001692591410.000000000 24441792591410.000000000 NULL NULL NULL NULL NULL NULL

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

However, I don't want the lines with all the fields (about 150 for my table).

Not a problem... it'll depend on the actual query you write.

In addition, I want the values in only one line; with all values separated by a "|" or a ";" separator.

Not a problem...

The exemple below show that the returned result written in the outpu file is unexploitable.

Heh... now I get it... would have been easier if you just said, it's not in the format you want. 😀

The easiest (and highest performance) way to do this is to use whatever query you want to form a temp table... then, using xp_CmdShell, BCP the table table out.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Colin Bickford Say Hey Kid Points: 663 More actions · Answer 4

Hi

I thought I'd put my pennysworth in.

No-one has suggested writing a VBS file using ADO to loop through the recordset writing to a file at each iteration. This could be scheduled using Windows Scheduler.

Or using XSL on an XML output to produce the required file.

One thing does puzzle me: if the format of the file is causing such a problem, then why not look at changing the process at the other end so that it accepts a file in a format that is possible?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

No-one has suggested writing a VBS file using ADO to loop through the recordset writing to a file at each iteration

The reason I've not suggested such a thing is that it'll be slower than BCP... Lot's slower in most cases...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Colin Bickford Say Hey Kid Points: 663 More actions · Answer 6

Fair point Jeff!

So really it would depend on how many rows are returned?

I'm guessing the threshold would be around 200?

Mind you, if it's 150 columns and 200 rows that's one hell of a continuous line in the text file!

There has to be a better way.

Maybe Littlesquall can enlighten us as to why the file has to be in that format?

littlesquall SSC Eights! Points: 979 More actions · Answer 7

Colin,

Thanks for your propose.

To answer you I've no explanation of why the result is returned in that format in the output file.

I don't want add a process at the end to re-format the file as I want.

I want directly the result values in one line in the output file.

This is for a production use so I wanna a easy and fast process to manage less than 200 lines of 150 fields each...

Do you have some example of the 2 ways proposed :

- OSQL + BCP

- Writing a VBS file using ADO to loop through the recordset writing to a file at each iteration.

Thanks

Colin Bickford Say Hey Kid Points: 663 More actions · Answer 8

Hi LittleSquall

Sorry for the delay.

[font="Courier New"]dim objConn, strConn, objRst, strSQL, strText

strConn="PROVIDER=SQLOLEDB;DATA SOURCE=SQL_SERVER_NAME;UID=USER_ID;PWD=PASSWORD;DATABASE=Northwind "

strSQL="SELECT CategoryID, CategoryName FROM Categories"

strText=""

If Not IsObject(objConn) Then

set objConn = createobject("ADODB.Connection")

objConn.open strConn

End If

If Not IsObject(objRst) then

set objRst = createobject("ADODB.Recordset")

objRst.Open strSQL, objConn

End If

'The following could be made more general by finding the number of columns that the SQL is returning (n) and

'then looping with "for i = 0 to n-1"

'But for brevity, I have chosen the simpler option:

strText = strText & objRst.Fields(0).Name

strText = strText & objRst.Fields(1).Name

Do Until objRst.EOF

'Same applies here as mentioned in lines 18-19

strText = strText & objRst.Fields(0)

strText = strText & objRst.Fields(1)

objRst.MoveNext

Loop

'Here I have just put the text into a message box

'but it could be written to a file using a FileSystemObject :

MsgBox strText

'Close the recordset

If IsObject(objRst) Then

objRst.Close

Set objRst = Nothing

End If

'Close the database

If IsObject(objConn) Then

objConn.Close

Set objConn = Nothing

End If[/font]

You'll have to edit the connection string to fit with your set-up.

SQL_SERVER_NAME is the name of your SQL Server.

You'll need to use a user with rights to the database.

You could, instead, use Integrated Security - that is your decision!

To test it, just save the file with a vbs extension and execute using Start|Run.

You can schedule it's execution in Windows Scheduler.

littlesquall SSC Eights! Points: 979 More actions · Answer 9

Hey Colin,

thanks for your code

I stay on my position and I will implemente the SP_OAmethod solution to write my query result in an output file.

However, to make this possible, I have made my client needs evoluate.

Consequently, I have selected some fields per table (not 150 fields as anounced)

So it will be easy to manage them with SP_OAmethod.

Tanks all for your help.

I will post my code later.

littlesquall

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

So really it would depend on how many rows are returned?

Why do it two different ways?... and have one crawl if the scalability increases?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions