0

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?

0

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).

0

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.