Solved

SQL Query Analyzer

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now