Forum Discussion
Data transferring from SQL server table to text file without using bcp
Hi friends,
I have data in SQL server table and I need to transfer from it to the text file with specific name format and with column names and specific data size of the data types. I couldnt do with BCP as it showing no permission for xp_cmdshell from the virtual machine I use.
Please anyone suggest what wil be the solution to achieve the target. Please it's Request
Only SQL servwr query is to be used. No import, no ssis, BCP failed,
Ease any solutions?
Hi Sreej320
You got great solutions so far and I would like to add another one to the party.
All the solutions till this point based on something that is installed but if you cannot install anything then there is a simple solution as well (which fit to any common operating system)
Use Azure Data Studio to execute a simple query which return whatever you need and export it directly to a text file. Azure Data Studio is a management studio application which is free and open source! It has version for any common operating system and there is no need to install anything or have any special permission to run it in the client side (obviously you connect the server so you will need the authentication to the SQL Server but this not related to execute the app but to the connection to the server/database. You can have the app files stored in external disk or disk-on-key and execute it directly from there
With all this said and all solutions, my prefer option will be bcp for most cases
If you want to use something like PowerShell, dbatools has a few commands that can help with that as well.
For instance Export-DbaDbTableData can export the data for you: https://docs.dbatools.io/Export-DbaDbTableData- If you can use SQLCLR objects in your SQL Server environment, that's another option. Quick and pretty easy.
- Sreej320
you can use sqlcmd tool like olafhelper mentioned above,
or if you're familiar with powershell you can select table datas through powershell and convert datatable to csv.
well, you may need to install sql module, but it's fancy and simple though.
https://docs.microsoft.com/en-us/powershell/module/sqlserver/read-sqltabledata?view=sqlserver-ps Sreej320 if you don't want/can use BCP then use SqlCmd; but that don't change anything on the required permissions. See
olafhelper thanks for the help. Do we have any method to transfer columns names of the table to text file as well. When I use BCP I'm getting only data not the table column headers. Pls help if u know any solution
Sreej320 , See the link for SqlCmd I posted => parameter -h headers
