Question Trying to export data into csv file using stored procedure

mp3909

Well-known member
Joined
Apr 22, 2018
Messages
61
Location
UK
Programming Experience
3-5
Can someone please help me with the error I am getting in the attached file.
 

Attachments

  • Capture.JPG
    Capture.JPG
    196.2 KB · Views: 187
What error? We can't even see it in that image. Please don't post images of text in the first place. Code is text and so are error messages so you can post them as text, formatted appropriately. That way, we can copy and paste them in order to test or search. Screenshots can help in certain instances but should never be the primary source of information. In this case, it doesn't even help.
 
Actually there is an error in the image, but I agree that an image for this isn't very effective.
Also, and I know you're asking about using a stored procedure, but have you considered using SSIS for this?

Reason I ask is even if it's possible to write data out to a file using a stored procedure that's not really what it's meant for whereas SSIS is & is free.
 
I try executing the following

SQL:
declare @sql varchar(8000)

select @sql = 'bcp [sample].[dbo].[fams] out C:\Users\mp88_\OneDrive\Desktop\samplefile.txt -c -t, -T -S' + @@servername

exec master..xp_cmdshell @sql

NULL

but I get the following error

SQLState = S1000, NativeError = 0

Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file

Obviously I know this means it can not access the file but I tried enabling this but still did not work. Maybe I am not taking the correct steps in giving it access to the file?

The reason why I am not using SSIS is because I don’t have any experience with SSIS. I am trying to automate this task where I have 2 excel files with many many data columns and I need to consolidate them into one excel file and then from there, give the user the flexibility to create an output file in excel based on their preference of which columns they want from this consolidated file. So I was thinking if I can load these 2 output files into SQL, then write a query to join them together either using a union (provided that they have the same number of columns and data type of each column in both files are the same) else using a full join. Then the user can write a select query specifying the columns they want to select from this one big table. And then the final part is to export this result set into excel which is why I am asking this question on the thread; expecting data into excel using a stored procedure.
 
Last edited by a moderator:
Back
Top Bottom