Need the output in excel

Question

Need the output in excel

espanolanthony

SSCarpal Tunnel

Points: 4204
More actions
October 16, 2012 at 9:30 am

#263302

Hi,
I have this below script and need the output in excel in D:\Reports folder. Anyone can help me with this. I don't know much with queries and all the queries will have to be run as a job every night so please advice accordingly.
select (u.last_name + ', ' + u.first_name) as doctor, h.*
from hss_systems h
join user_names u on h.user_id=u.user_id
order by last_name

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sqlrd22 SSCrazy Points: 2255 More actions · Answer 1

espanolanthony (10/16/2012)
Hi,
I have this below script and need the output in excel in D:\Reports folder. Anyone can help me with this. I don't know much with queries and all the queries will have to be run as a job every night so please advice accordingly.
select (u.last_name + ', ' + u.first_name) as doctor, h.*
from hss_systems h
join user_names u on h.user_id=u.user_id
order by last_name

Use SQL Server Agent job to run the query every night.

And try BCP (xp_cmdshell BCP if using SSMS) to output to an Excel file.

espanolanthony SSCarpal Tunnel Points: 4204 More actions · Answer 2

Thanks you but Since I have no knowledge I need the exact statements with bcp if you can please..

Lowell SSC Guru Points: 323518 More actions · Answer 3

sqlrd22 (10/16/2012)[hr
Use SQL Server Agent job to run the query every night.
And try BCP (xp_cmdshell BCP if using SSMS) to output to an Excel file.

BCP only does raw text files, so can send create a comma delimited file which Excel opens by default on most machines, but just making the extension .xls or .xlsx doesn't make it an excel document.

What i've done in the past is create an existing excel document with the proper headers and no rows.

then I've copied the file, opened the copy, and inserted data via openrowset.

If you are not going to use SSIS or a CLR, that's how i would do it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlrd22 SSCrazy Points: 2255 More actions · Answer 4

espanolanthony (10/16/2012)
Thanks you but Since I have no knowledge I need the exact statements with bcp if you can please..

If you're okay with it being a CSV file (which opens in Excel as default) you could try this...

exec xp_cmdshell 'bcp "select (u.last_name + '', '' + u.first_name) as doctor, h.*

from hss_systems h

join user_names u on h.user_id=u.user_id

order by last_name" queryout D:\Reports\yourfilename.csv -c -t -S yourservername -T';

go

Obviously using SSIS would be a better option though.

Lowell SSC Guru Points: 323518 More actions · Answer 5

here's a tested example;

I've created a spreadsheet that has the contents of sys.servers.

you can grab a copy of it here:

SysServers_Example.xlsx

and the code, assuming 64 bit SSQL 2008 with the 64 bit ACE drivers installed:

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

GO

--what's in there now?

SELECT * FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0',

'Excel 8.0;DATABASE=c:\Data\SysServers_Example.xlsx',

'Select * from [Sheet1$]')

--populate the sheet! running this multiple times inserts rows over and over.

INSERT INTO OPENROWSET('Microsoft.Ace.OLEDB.12.0',

'Excel 8.0;DATABASE=c:\Data\SysServers_Example.xlsx',

'Select * from [Sheet1$]')

Select * from sys.servers

--what's in there after i fiddled with it?

--what's in there now?

SELECT * FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0',

'Excel 8.0;DATABASE=c:\Data\SysServers_Example.xlsx',

'Select * from [Sheet1$]')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

espanolanthony SSCarpal Tunnel Points: 4204 More actions · Answer 6

Thanks SSChampoin but where should my scripts fit in here? Please let me know. Any idea how exactly will it look with my scripts. I need that if possible.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

One rule you should follow is not to use code you don't understand. If you need to support it and you don't know what it's doing, you'll be in trouble.

Read the comments, analyze each statement, search for the terms that you're not familiar with and finally test it with your data.

I could recommend you to try to explore a solution with SSIS (Integration Services).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

kl25 Hall of Fame Points: 3752 More actions · Answer 8

If you want to begin to explore an SSIS solution, use the Import/Export Wizard (SSMS-->Select Database-->Right click, Select 'Tasks' and from there Select 'Export Data'). Walk through the wizard. Use a 'Microsoft Excel' destination. On the 'Specify Table Copy or Query' screen, select 'Write a query to specify the data to transfer' and then use your query. On the 'Select Source Tables and Views' click the 'Edit Mappings' button to be sure the mappings are what you need. Continue walking though the wizard. On the 'Save and Run Package' screen, check 'Save SSIS Package' and save it to the file system. Uncheck 'Run Immediately' unless you want to generate an excel file to examine. Finish walking through the wizard.

Once you've used the wizard to create and save the package, open up BIDS, create a new solution and then import the SSIS package you just saved. Examine it carefully and be sure you understand what it's doing. You'll probably want to change some of the task names from the defaults in the package so you reflect what the task is actually doing.

I echo what's been said before. Be sure you understand the query and the SSIS package.

If you pursue this option, there are different possibilities for scheduling your package. See this MSDN article for more details.