Question Trying to export data into csv file using stored procedure
- Thread starter mp3909
- Start date
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
Staff member
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.
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
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.
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
NULLbut 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:
Similar threads
- Question
