asked on

Create a Data Extract using T-SQL

I need to create an extract that runs for a customer that they can upload. It would be nice to be able to create this and then let the customer just run the report and have the report automatically create a file somewhere that she can grab and upload to her site.

There is a header1 row
Header2 row
Header 3 row
then data.. some of the rows are null.

I have done this type of thing before but it has been years.. and could use some help.

Here is a example of what I need..
Oh and it can be tab or comma delimited

file-example.xlsx

lcohan

You could use SQL bcp like in example below or SQL SSRS to create a nice report that can be sent via email fo a mailinglist when snapshot updated:

exec master..xp_cmdshell N'sqlcmd -E -Q"select * from server_name.dba_name.schema_name.table_name" -h-1 -s"," -W -o"c:\output.txt"', no_output

http://msdn.microsoft.com/en-us/library/ms162773.aspx

--for TEXT fields use BCP!!!

http://msdn.microsoft.com/en-us/library/aa174646(SQL.80).aspx

Mike McCracken

Are you trying to privide data for user to run reports or do you want t runthe report against the data and upload say a PDF copy of the result?

mlmcc

Jennifer Barman

ASKER

what I "ultimatly" need is a .csv file. Now I thought because the user needs the file on no certain schedule, that it might be easy for her to run the report and have it export into a file for her.. BUT.. really all I need is to write a SQL script.

Jennifer Barman

ASKER

I am not sure I have access to SSRS.. :(

I do on "MY" test server.. maybe if I create it.. I can then have my other department upload it. Our system is that they have granted me access to the Database that I use.. there are 40 databases.. I know I cant look at the analyzer for log issues.. cause the root of the sql tree (if I'm saying that right) is locked.

ASKER CERTIFIED SOLUTION

David Todd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jennifer Barman

ASKER

Let me give this a try...dtodd...

Nasir Razzaq

You said you want the customer to have the ability to run something(like a report) which generates the csv file. Could this not be a webpage?

vasto

There is a free tool: Report Genie (http://download.cnet.com/Report-Genie/3000-2064_4-10555178.html). It is pretty old and I am not sure that it will server your needs but it may worth to check it. What it can do s to retrieve data from SQL Server and export it to a file .

Another one which is free for one task is :http://r-tag.com/ReportManager.aspx
It supports SQL queries and the result can be exported to excel. pdf, csv ... saved to local network, document management or SharePoint and send by e-mail. This tool supports also Crystal Reports and SSRS.

The first tool is completely free ( double check this ) while the second one is commercial but you can use it to run one task free.

If you have SSIS you can create a package that will do the same without using external tools.

lcohan

--export to EXCEL from T-sql code

USE [AdventureWorks];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\contact.xls;',
'SELECT * FROM [Sheet1$]')
SELECT TOP 5 FirstName, LastName
FROM Person.Contact
GO

--using BCP with column names:
bcp "select 'ProductID', 'Name', 'ProductNumber' union all select convert(varchar(10), ProductID), Name, ProductNumber from AdventureWorks2008R2.Production.Product" queryout "c:\temp\product.xls" -c -T

Jennifer Barman

ASKER

it only partically answered my q