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
Jennifer BarmanAsked:
Who is Participating?
 
David ToddSenior DBACommented:
Hi,

From SSMS just run a script and save the results and you have your text file.

The only extra thing is to now add commas and delimters to the output.

so
select someInt, someString
from dbo.SomeTable

becomes
select convert( varchar( 20 ), someInt ) + ',"' + someString + '"'
from dbo.SomeTable

HTH
  David
0
 
lcohanDatabase AnalystCommented:
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
0
 
mlmccCommented:
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
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Jennifer BarmanAuthor Commented:
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.
0
 
Jennifer BarmanAuthor Commented:
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.
0
 
Jennifer BarmanAuthor Commented:
Let me give this a try...dtodd...
0
 
CodeCruiserCommented:
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?
0
 
vastoCommented:
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.
0
 
lcohanDatabase AnalystCommented:
--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

0
 
Jennifer BarmanAuthor Commented:
it only partically answered my q
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.