• Hello and welcome to our new forums. We upgraded our forum sites to a more robust and modern system which we hope you will enjoy. Be sure to check out your profile by clicking the button on the top right and configure your preferences, signature, time zone, avatar, etc. as you wish. If you need help with using this new forum'ware try the help link on the bottom right.

    Click here to review your account now.

Question Trying to export data into csv file using stored procedure

mp3909

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

Attachments

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
2,172
Location
Sydney, Australia
Programming Experience
10+
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.
 

JuggaloBrotha

Moderator
Staff member
Joined
Apr 23, 2011
Messages
146
Location
Lansing, MI; USA
Programming Experience
10+
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.
 

mp3909

Well-known member
Joined
Apr 22, 2018
Messages
45
Location
UK
Programming Experience
3-5
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:
Top Bottom