?
Solved

Create a Data Extract using T-SQL

Posted on 2011-10-27
10
Medium Priority
?
219 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:Jennifer Barman
10 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 37039708
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 37039900
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
 

Author Comment

by:Jennifer Barman
ID: 37039962
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:Jennifer Barman
ID: 37039976
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
 
LVL 35

Accepted Solution

by:
David Todd earned 1500 total points
ID: 37041942
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
 

Author Comment

by:Jennifer Barman
ID: 37044761
Let me give this a try...dtodd...
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37046068
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
 
LVL 18

Expert Comment

by:vasto
ID: 37046438
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
 
LVL 40

Expert Comment

by:lcohan
ID: 37046772
--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
 

Author Closing Comment

by:Jennifer Barman
ID: 37065786
it only partically answered my q
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Integration Management Part 2
Loops Section Overview
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question