SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Stored Procedure to write to a file?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 04/21/2008 :  05:56:36  Show Profile  Reply with Quote

I have a query running in QA. I really need to get output directly to a file. Is it possible?

Right now I am copying the output string using ctrl-C and pasting to Notepad! When this script becomes a stored procedure I'll need a proper way to get output to a file. How do I do it?

Thanks

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 04/21/2008 :  06:51:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You have three options in QA.

1) Show the results as text
2) Show the results as grid
3) Export results as file

They are all accesible with a toolbar button in QA.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 04/21/2008 :  07:03:40  Show Profile  Reply with Quote

Sure. Doing all this for years. But this is a point-and-click routine and I need a file popping out from a stored procedure in a directory I need it, as the script runs

I now have the script in a stored procedure. Hoping to get the script generating the file before that, but I couldn't wait.

The stored procedure is part of a job. The parameter'd HTML generated is PRINT'd and this created an error in the job, making the step and the whole job fail. The error message is actually the HTML output I wanted! But in a file!

Now I'm SELECT'ing that to see what happens.

Thanks...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 04/21/2008 :  07:39:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Use BCP to create files.
Or even DTS/SSIS.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 04/21/2008 :  08:06:31  Show Profile  Reply with Quote
When I looked at it last time, BCP seemed to be an OS program. DTS / SSIS parts of SQL Server, but still requiring human activity, with hand on mouse.

I'm after automation in SQL script to create 100s of text files to be generated daily from the database, in the IN tray directory of a PDF converter for secure, readable files for an archive and to be sp_sendmail'd. Doesn't this kind of task get done every day?

What good is a car if it can't go offroad sometimes? My database needs to make files without help. I miss UNIX. What has Microsoft done?!?!?
Go to Top of Page

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 04/21/2008 :  10:09:11  Show Profile  Reply with Quote
The answer to my question

is

xp_cmdshell
Go to Top of Page

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 04/23/2008 :  11:15:02  Show Profile  Reply with Quote

A huge problem with using this was that Windoze command prompt (worst software borne of this Earth) will not accept < as a starting character, as it's thinking of file redirection... so goodbye HTML. No chance.

I found a procedure I could recreate called sp_appendToFile. No matter its name, no need for how it was implemented, it takes parameters @filename and @text and I was astonished that none of you gurus had a clue how to create and write to a WinOS file from SQL Server. Unbelievable.

Using sp_OACreate, sp_OAMethod
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/23/2008 :  12:00:32  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Yeah, we're a bunch of idiots here. I wouldn't bother asking anyone here for any more help, it would be a complete waste of your very valuable time.

And you're absolutely right: all the problems you are experiencing are because of Micro$oft and Windoze. They are stupid.

- Jeff
http://weblogs.sqlteam.com/JeffS

Edited by - jsmith8858 on 04/23/2008 12:04:12
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/23/2008 :  12:25:28  Show Profile  Reply with Quote
quote:
Originally posted by mikebird

DTS / SSIS parts of SQL Server, but still requiring human activity, with hand on mouse.
So schedule your DTS/SSIS package through SQL Job agent. Duh.
"Hand on mouse"...I can only imagine where the other hand is...

e4 d5 xd5 Nf6
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 04/23/2008 :  13:10:43  Show Profile  Visit tkizer's Homepage  Reply with Quote
I find it very funny that his profile says SQL Server 2005 DBA and he couldn't initially figure out that you can call executables such as bcp from xp_cmdshell. That's SQL Server 2005 DBA 101.

It's also very funny that he blames the products when it's his lack of knowledge that is the problem. The products provide everything that he needs. But they must be stupid since he isn't aware of them.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Edited by - tkizer on 04/23/2008 13:11:05
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 04/23/2008 :  13:45:46  Show Profile  Reply with Quote
Really mercy for the company he is working
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/23/2008 :  14:13:54  Show Profile  Reply with Quote
quote:
Originally posted by mikebird
...I was astonished that none of you gurus had a clue how to create and write to a WinOS file from SQL Server. Unbelievable...



We're just a bunch of dummies here, and none of us have ever had to do anything so unusual and technically challenging as creating a file from the results of a query, because things like that can only be done in UNIX or LINUX.

Certainly no one here has ever thought of using SQL Server Books Online or Google to find about one million references and examples of how to use BCP, DTS, Integration Services, or sp_makewebtask to do this. Or using SQL Server Reporting Services to create PDF files directly.








CODO ERGO SUM

Edited by - Michael Valentine Jones on 04/23/2008 16:12:38
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 04/23/2008 :  14:21:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Stop now! Behave!

Mikebird has gone through great pains from yesterday when working on jobs to this exhaustive task.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

tosscrosby
Aged Yak Warrior

USA
676 Posts

Posted - 04/23/2008 :  14:25:51  Show Profile  Reply with Quote
quote:
Originally posted by blindman

quote:
Originally posted by mikebird

DTS / SSIS parts of SQL Server, but still requiring human activity, with hand on mouse.
So schedule your DTS/SSIS package through SQL Job agent. Duh.
"Hand on mouse"...I can only imagine where the other hand is...

e4 d5 xd5 Nf6



Do we even want to know??

Terry
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000