Reporting

Question

Reporting

imran ashraf

Grasshopper

Points: 13
More actions
January 8, 2004 at 1:51 am

#106973

I am new to SQL Server and I come from Oracle PL\SQL background. What I am trying to do is process records from 2 tables and output it to a flat file in a certain format.
The format is similar to this example,
Department Name
List of Employees in Department.
I know BCP is not what I need is there a way to create an output file to produce the report?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Robert Davis One Orange Chip Points: 28027 More actions · Answer 1

There are a couple of ways you can do it.

1. Use a DTS package to export the data to a text file. To get the report output correct, you may have to use a cursor to cycle through the Department Employees for each Department.

2. Create the output of the report and save it on the client as a query file. Execute the query file using isql.exe from the command line (.bat file) using the -o switch to output the results to a text file. Use either xp_cmdshell/scheduled jobs or Windows Task Scheduler to schedule the .bat file for execution if you want it to run automatically.

I use option #2 for a series of reports that get mailed every day to my clients. I have a second query file that is executed in isql.exe that emails the reports using xp_sendmail.

As an example, here is the content of my .bat file (slightly modified for security):

REM Runs specified SQL query file and outputs to specified text file which is then

REM emailed as an attachment.

REM Parameters Used:

REM -S server name

REM -E trusted connection

REM -i input file (SQL query file)

REM -o output file

REM -w line width

REM -n remove numbering (without this, there would be a line of numbers on the first row of the output)

Pushd "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\"

start /wait isql.exe -S {ServerName} -E -i"D:\reports\ClientReport.sql" -o"D:\reports\NationalReport.txt" -w400 -n

isql.exe -S {ServerName} -E -i"D:\reports\SendClientReport.sql"

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

David Burrows SSC Guru Points: 65142 More actions · Answer 2

Why not bcp?

Create a proc to output the data in the format you want then use either bcp, dts or isql to output to a text file.

Far away is close at hand in the images of elsewhere.
Anon.

gljjr SSChampion Points: 10080 More actions · Answer 3

You could use the QUERYOUT part of BCP to do this also. If you have a SP that puts the data into a temp table and then use BCP with the QUERYOUT you should be able to accomplish exactly what you need.

Otherwise you might think about shelling out to OSQL and use an output file with print statements being your only output from the script. In this way you can format the data the way you might want. Just make sure you suppress the line numbers

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.