Solved

SQL Query Analyzer

Posted on 2006-11-15
8
320 Views
Last Modified: 2008-01-16
Hello EE,

I am looking for a script I can run that will export a certain table I need into a CSV (comma seperated file).

for exaple given the DB to pull from and the Technical name i need to  be able to export that table into a CSV

export TABLENAME DESTINATION NAME

Thanks

Chris
0
Comment
Question by:Clownzer
8 Comments
 
LVL 28

Expert Comment

by:imran_fast
ID: 17947004
it will be something like
insert into openquery(execellinkedserver,'select * from sheet1$ where 1=1')
select * from sourcetable
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17947036
this is how you can add excel file as a linked server

EXEC sp_addlinkedserver EXCEL,
     'Jet 4.0',
     'Microsoft.Jet.OLEDB.4.0',
     'c:\data\MySheet.xls',
      NULL,
     'Excel 5.0;'

GO
0
 
LVL 3

Expert Comment

by:ia2189
ID: 17947147
Does it have to be in query analyzer?  You could do it fairly easily using the Data Transformation Services (DTS Local Package) that's found in Enterprise Manager.
0
 

Author Comment

by:Clownzer
ID: 17947605
Whatever is the easiest way to get the task accomplished. Our SQL DBA is out so it got dropped into my lap. Here is the email:

Hi XXXX,             
Here are the files I need for the W-2 work.             

Display Name      Technical Name                      Physical Name
Payroll Year End Header      UPR_Year_End_WORK_HDR      UPR10100
Payroll Year End Wage      UPR_Year_End_WORK_Wage      UPR10101
Payroll Year End Pension      UPR_Year_End_WORK_Pension      UPR10103
Payroll Year End Special      UPR_Year_End_WORK_Special      UPR10104
Payroll Year End State      UPR_Year_End_WORK_State      UPR10105
Payroll Year End Local      UPR_Year_End_WORK_Local      UPR10106
Payroll Year End Other      UPR_Year_End_WORK_Other      UPR10107

There will be a set of these files for all companies.             
            
The easiest way to send these files would be for one of your IT guys to go into the SQL Query tool and export the files as tab or comma delimited files.  If they would also identify all of the files by company that would help too.             
If you need me to visit with one of your IT guys just let me know.             
Thanks, XXX XXXXX      
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 250 total points
ID: 17954689
This email is suggesting that you:

1. Start Query Analyzer
2. Run a SELECT statement (from the Physical name, ie. SELECT * FROM UPR10100) that retrieves the data (Payroll stuff in Great Plains perhaps?)
3. Right click in the results grid, press Save As and save the results to a file.


If this is not automated then your DBA is clearly protecting his job. This kind of thing should not need manual intervention.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

856 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