What T-SQL commands exist which can be used in Query Analyzer to export a select statement within a stored procedure?

Question

Post reply

What T-SQL commands exist which can be used in Query Analyzer to export a select statement within a stored procedure?

davepevans

Valued Member

Points: 73
More actions
January 21, 2008 at 6:27 am

#76876

I eagerly await you SQL guru's out there and appreciate your help and statement examples if possible.
Thanks!
David

Viewing 13 posts - 1 through 12 (of 12 total)

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

toniupstny Hall of Fame Points: 3986 More actions · Answer 1

I know that getting old my eyesight is not as good but I REALLY missed the question or the code you need help with???

Toi

GSquared SSC Guru Points: 260824 More actions · Answer 2

Are you seriously looking for someone to type up a full list of all T-SQL commands and examples of their use?

If so, I highly recommend opening up Books Online and starting there. Or buy a good book on T-SQL and start with that. Then, when you have more specific questions, give those to us.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

GSquared SSC Guru Points: 260824 More actions · Answer 3

Never mind. The web page was truncating part of the subject.

On the question: "What T-SQL commands exist which can be used in Query Analyzer to export a select statement within a stored procedure?"

I'm not sure what you mean by exporting a select statement.

Do you mean you want to create a file that has the query text in it? Or a file with the results of the query? Or something else?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

davepevans Valued Member Points: 73 More actions · Answer 4

Hi again folks, thanks for the responses and apologies for not being coherent.

For heavens sake don't ask me why I'm looking to do this or suggest other methods like QA's 'Results to file' or DTS etc, as I'm actually just looking to see if there is a command similar to Oracle's Spool or FOpen commands which will allow me to simply execute a stored procedure. The stored procedure won't be run in Query Analyzer which is why SQL Server's tools won't help. However, I want the stored procedure to create a text file. The text file should contain the results from the select statement embedded in the stored procedure.

If anyone knows of a command in SQL Server that does this, I'd be grateful as I can't find it on books online, google etc.

In terms of examples, I'm just looking for one example of the code as oppose to just a description, but this is normal and most likely assumed anyway.

Thanks again and please let me know if you've any questions, I hope this is clearer.

Cheers all,

David

Sergiy SSC Guru Points: 110206 More actions · Answer 5

Probably what you need is bcp utility.

Read the topic about bcp in BOL, it's quite good, if any questions remain don't hesitate to ask.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

The stored procedure won't be run in Query Analyzer which is why SQL Server's tools won't help.

Well, where will it be run from... it'll make a difference.

--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)

Ron McCullough SSC Guru Points: 63877 More actions · Answer 7

Referring to BOL OPENROWSET

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Text;Database=D:\MSSQL\','SELECT * FROM Test.txt')

SELECT column01,column02, FROM yourtablename -- this is your select statement

Note: The file you want to write to must be located on the server.

Test.txt - is the output file name

Results in a CSV format.

OR

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Text;Database=D:\MSSQL\;HDR=Yes;', 'SELECT * FROM Test.txt')

SELECT * FROM yourtablename - -this is your select statement

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

You're not helping your cause by being so short on details....

That being said - depending on what you want to do, you could also use OSQL with an output file in place. It will "capture" any queries you run and spit the results out to your output file.

That being said - you also COULD use DTS (which, until you tell us why it's not a good candidate, might still be your best bet, or at least a viable candidate).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Simply stated, there is no equivalent for Oracle's spool command. You can use OSQL with a DOS redirect to a file or BCP or DTS or...

--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)

GSquared SSC Guru Points: 260824 More actions · Answer 10

I'm not familiar with Oracle, so not sure what "spool" does. From what you are describing, the BCP command-prompt utility is what you're looking for.

From a command prompt, you can give SQL commands through BCP, to save query results to files. (You can also reverse the process and import files into SQL through BCP.) Don't have to open Query Analyzer/Management Studio, etc.

Books Online has details and examples.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

davepevans Valued Member Points: 73 More actions · Answer 11

With exception of Matt Miller, thanks much for your help!

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

That's not nice... Matt was spot-on by saying you weren't providing enough information. I simply took a guess and didn't say much more than he did.

--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)