Avatar of Star79
Star79
Flag for United States of America asked on

Create CSV file from store Proc

I have the following query;
in a proc
select Pharmacy,PharmacyNPI,Chain,Facility,FacilityNPI,MONTH,DISPENSEDT,RXNO ,PATIENTSSN,PATIENTPHARMACYID,PATIENTLASTNAME,PATIENTFIRSTNAME,GENDER,
 DATEOFBIRTH,NDC,CCID,MEDICATION,STRENGTH,QTY,DAYSSUPPLY,COST,PHYSICIAN,PHYSICIANNPI, PAYTYPE ,ROUTEOFADMIN,INVENTORYCATEGORY,COPAY,BRAND from RXs

Open in new window


How to output this to a CSV file to a specific folder say C/FTPupload

Please let me know.
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Star79

8/22/2022 - Mon
Ganapathi

In SQL Management Studio:

Right click on the result set grid, select 'Save Result As...' and save in.

On a tool bar toggle 'Result to Text' button. This will prompt for file name, provide it and save wherever you want.
Star79

ASKER
hello Ganapathi,
Iam looking to automate this proc so that the proc itself will create the file and put it to the location.
Star79

ASKER
Ok so I did the below in my proc:
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.4.0','Text;Database=C:\scripts\accuscriptFTP;HDR=YES;FMT=Delimited','SELECT * FROM [TESTFILE.txt]')
select Pharmacy,PharmacyNPI,Chain,Facility,FacilityNPI,MONTH,DISPENSEDT,RXNO ,PATIENTSSN,PATIENTPHARMACYID,PATIENTLASTNAME,PATIENTFIRSTNAME,GENDER,
 DATEOFBIRTH,NDC,CCID,MEDICATION,STRENGTH,QTY,DAYSSUPPLY,COST,PHYSICIAN,PHYSICIANNPI, PAYTYPE ,r.routename ROUTEOFADMIN ,INVENTORYCATEGORY,COPAY,BRAND,PERDIEMPRICE,rxbatch from @T t
left join drug..routenames r on t.ROUTEOFADMIN = r.routecd
 WHERE RxNo<>0 and RxNo<>-1 and FacilityNPI ='106' and   DISPENSEDT =CONVERT(char(10),getdate()-1,101) 
 order by PATIENTLASTNAME,RXNO

Open in new window

But when I try to compile the proc it says:

The OLE DB provider "Microsoft.ACE.OLEDB.4.0" has not been registered.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Star79

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Star79

ASKER
Creating the batch process was a better way of doing it