Solved

SQL Query Analyzer

Posted on 2006-11-15
8
312 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

776 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