Solved

Exporting a table to CSV format... job? stored prod?

Posted on 2007-03-24
7
679 Views
Last Modified: 2013-11-30
We have clients that will need their position in csv format a handful of times over the next week or two.  So, we need to figure out how to export positions from SQL to CSV for the following starting with AA(like statement will grab everything starting with AA, etc), AE and CF. I created a query to show the following data that i need for the next 2 weeks.

SELECT     *
FROM         mmPSYMBOL
WHERE     (root LIKE 'AA%') OR
                     (root LIKE 'AE%') OR
                     (root LIKE 'CF')  

I do not want to do this manually everyday. Soo i want to schedule a job to run  export the data maybe a DTS package and save it as a CSV? Do i need to create a Stored procedure or something? If so how do i do that?

Sorry I am new to SQL and ideas?????? Thank you again!

0
Comment
Question by:jcollazo81
7 Comments
 
LVL 11

Expert Comment

by:dready
ID: 18787017
HI,
DTS is very well suited for this task. You can then schedule the package to run every day, or whenever you want to. Following the example in this link explains you exactly how to do it. You can also enter your query in the dts package.

http://www.sqljunkies.com/Article/A8CB0AFE-D143-4B49-B865-4FBBFEDFCCD7.scuk

good luck,

dready
0
 

Author Comment

by:jcollazo81
ID: 18787491
I'm sorry but i don't think that article helped at all... Can you be a little more clear with details :). Sorry again i am new to SQL
0
 
LVL 11

Expert Comment

by:Goodangel Matope
ID: 18788108
You can use a cursor to do this, and write to the file using xp_cmdshell. Maybe if you look at this PAQ it might help?

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_22404100.html

If you need more help just ask.
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 1

Expert Comment

by:Kaviyarasanr
ID: 18788275
Hi,
Creating a DTS package and Scheduling it daily could be a solution.
You can create a DTS Package that has
A source as OLEDB connection for SQL Server.
A Destination as Test File ( you can configure it to separate the results with comma...)
A Execute SQL Task on source connection ( Here you can run the Query)
And a Transform Data Task

create the work flow and run it.

You can refer
DTS Designer: http://msdn2.microsoft.com/en-us/library/aa176285(SQL.80).aspx
DTS Tasks: http://msdn2.microsoft.com/en-us/library/aa933506(SQL.80).aspx
Workflow: http://msdn2.microsoft.com/en-us/library/aa933506(SQL.80).aspx

This may work... i am also new to SQL

Kavi


0
 

Author Comment

by:jcollazo81
ID: 18788459
Hi guys, for some reason this is not working. And DTS Package as well. I created a SP...  These are the columns i only need.'root              Underlying                  exchange'.... Any idea what i am doing wrong? Table is called mmPSYMBOL

CREATE PROCEDURE spMMPSYMBOLTEST
@File VARCHAR(255)
AS

DECLARE @CommandString SYSNAME;
DECLARE @HeaderRecord  SYSNAME;
DECLARE @RecordData    SYSNAME;
DECLARE @FileName      SYSNAME;

SET @FileName = CAST(@File as SYSNAME);

SET @HeaderRecord = 'root              Underlying                  exchange';
SET @CommandString = 'echo ' + @HeaderRecord + ' > ' + @FileName;
exec master..xp_cmdshell @CommandString, NO_OUTPUT

DECLARE mmPSYMBOL CURSOR FOR
SELECT     *
FROM         mmPSYMBOL
WHERE     (root LIKE 'AA%') OR
                    (root LIKE 'AE%') OR
                    (root LIKE 'CF')

OPEN CustomersList
FETCH NEXT FROM CustomerList INTO @RecordData

WHILE @@FETCH_STATUS = 0
BEGIN

   SET @CommandString = 'echo ' + @RecordData + ' >> ' + @FileName;
   EXEC master..xp_cmdshell @CommandString, NO_OUTPUT

   FETCH NEXT FROM CustomerList INTO @RecordData
END

CLOSE CustomerList
DEALLOCATE CustomerList

GO

0
 

Author Comment

by:jcollazo81
ID: 18789944
Soo i created a SP and it got the data that i was looking for when i called on the SP on Query Analyzer.

CREATE PROCEDURE spMMPSYMBOLTEST
AS
SELECT     *
FROM         mmPSYMBOL
WHERE     (root LIKE 'AA%') OR
                   (root LIKE 'AE%') OR
                   (root LIKE 'CF')
GO  

Now how do i get to become a job schedule. I tried to create a DTS Package that has
A source as OLEDB connection for SQL Server.
A Destination as Test File ( you can configure it to separate the results with comma...)
A Execute SQL Task on source connection ( Here you can run the Query)
And a Transform Data Task.

This does not work for me any suggestions?
0
 
LVL 1

Accepted Solution

by:
Kaviyarasanr earned 500 total points
ID: 18790966
Hi
Could  you please specify where the DTS Package has failed.
In the connection the Microsoft OLE DB Provider for SQL Server please speify the server name and the database name in the respective drop down lists.
Create a Transform Data Task and in properties in source tab select the SQL query option and paste your query. In Destination properties choose define from source and execute and press ok
In Test File (Destination) connection specify the file name and click on properties and choose colum delimiter as comma.
Then save and execute the package. I am stressing on this because I tried it and it worked for me. Please query if u face any problems.

Thanks,
Kavi
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql server concatenate fields 10 35
SYbase 4 31
TSQL query to generate xml 4 34
Add sequential columns to an existing query based on rows of another table 3 14
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

773 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