Link to home
Create AccountLog in
Avatar of Star79
Star79Flag 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.
Avatar of Ganapathi
Ganapathi
Flag of India image

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.
Avatar of 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.
Avatar of 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.
ASKER CERTIFIED SOLUTION
Avatar of Star79
Star79
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Star79

ASKER

Creating the batch process was a better way of doing it