How to write and read files from client machine SQL Server 2019

Question

I need to prepare a stored procedure which export as many files as many tables I have in schema, but files need to be written on local machine on specific path e.g. C:\Temp\ not on SQL Server destination. When file exists it should be overwritten, if not simply created. I need also read from local files: with parameters and template, which will be used in procedure. I know how to do everything except the method of read/write files.

I found some ideas for export to files:

1. xp_cmdshell executing bcp command I could only write files on Sql Server 2019 machine.
2. I found also sp_configure 'Ole Automation Procedures', 1;

    DECLARE @OLE INT
    DECLARE @FileID INT
    EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT
    EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, 'D:\data\sqltotext.text', 8, 1
    EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, 'Today is wonderful day'
    EXECUTE sp_OADestroy @FileID
    EXECUTE sp_OADestroy @OLE
    GO

Do you know easier way to achieve that?

Answer 1

I'd like to emphasize point 3 in J.D's post. This is something you definitely want to do outside of SQL Server, if at all humanly possible.

That "outside of SQL Server" could be things like SSIS, Powershell, something you write in your favorite programming language (C#, python), some client app, etc.

Answer 2

Probably the easiest way will be to use the dbatools powershell library and a few lines of powershell.

e.g.

$filename = "C:\Users\way0u\OneDrive\Documents\SQL\Customer" Invoke-Sqlcmd -Query "SELECT * FROM [Sandbox].[dbo].[Customer]" -ServerInstance ".\SQL2019" | Export-Csv -Path "$filename.csv" -NoTypeInformation

library here https://dbatools.io/ examples https://www.sqlservercentral.com/articles/exporting-data-with-powershell

Answer 3

A SQL Server instance has no concept of another machine other than it's own. When you connect to it with SSMS (or other means) you're specifically initiating the connection to that server, not the other way around. So out of the box I don't believe there is a way with pure SQL to accomplish this.

These are a couple ideas of what you can do:

1. xp_cmdshell essentially is a way to execute commands on the server that the SQL Server instance is running on. If you can setup a file share to the local machine from the server that the SQL Server instance is running on first, then you can probably leverage xp_cmdshell to write the files to that share.

2. CLR might be another solution you can use to create a procedure that pipes the data from your server to a web service that has access to the local machine. Then that web service can easily write files to the local machine using C# code (or really any code of your choice that supports web services).

3. Last but certainly not least (probably the most standard way to go about this) is SSIS. You can create an SSIS package that exports your data and then writes actual files to your local machine (though you probably need to add your local machine as a connection in the package).

Answer 4

I agree with the previous posts. I'd like to thrown in MS-Access as an option. Or even SQL Express.

It all depends on what you are trying to achieve. Why are you doing this? Because unless you have a very good reason for doing this, this goes against the common principles and advantages of having a database.

Exporting to text, xml or csv always seems like the easiest option as an intermediate step, but it seldom is.

• If you are migrating, There are better ways.
• If you are reporting, There are better ways.
• If you are building an App, There are better ways.
• If you want version control, There are better ways.

What is your end goal? I will bet money there is a better alternative that exporting to text or .csv.