Sql-server – Create Text File From Stored Procedure

sql serversql-server-2016stored-procedures

I have an ETL process (in SSIS) that extracts data from the database and writes the data to a predefined text file. Currently, I create the directories and files manually to satisfy this process.

To add a new variable to this process, I run a usp that adds a new row to a table, which is read in to the ETL process and determines which packages to run.

To remove the manual creation of the directories and files mentioned above, and to remove the possibility of human error, I want to create the new directory and file when a new row is added via the usp.

In short; I check to see if the subdirectory exists using xp_dirtree, and then use xp_create_subdir to create the new subdirectory. What I want to do now is create an empty text file, in the new subdirectory, that will have data written to it during the ETL process.

The options I think I have are the following:

Use xp_cmdshell; I recognise that there is a stigma attached to this, but I am not adverse to using it if it is the most optimal way. This is disabled by default, but I can reconfigure on and off within the usp.
Use fso; I'ved used fso within .NET scripts, but have no experience of using it within SQL Server. I can't seem to find any resources that dictate how to create a text file, only read and write.
Use SSIS; within the ETL project I have a flat file connection manager, where the connection string is dynamically set, based on the name of the variable. I'm unsure if the text file will automatically be created if it does not exists? I have manually created them to avoid any chance of failure. If the text file is automatically created, this could be an option.

I've purposely left BCP out of the options above, as I don't have a result set to copy, and therefore presume this is not a viable option? Unless I can "copy" an empty dataset to create the text file?

Can anybody provide any useful resource for how to accomplish this? Or can anybody provide any pointers on how best to achieve this? Is xp_cmdshell the best way? I want to create an empty text file when I create the new subdirectory.

Thanks in advance for any help offered.

Best Answer

You could use SQL CLR. This allows (more-or-less) any C# code to be executed from within SQL Server as if it were a stored procedure. This includes OS and file system manipulation. The code can accept values from and return values to T-SQL. Permissions can be controlled just like any other stored procedure, sidestepping many of the concerns with xp_cmdshell.

Related Solutions

Kill CLR stored procedure process

You can use handle.exe from SysInternals to see which process has a handle open to your file. But I wouldn't be too surprised if it indeed is the SQL Server process itself (as others have noted in the comments).

First make sure, that your procedure actually closes the file correctly, when being canceled.

You didn't show the code for the procedure, but it is not too far fetched to assume, that you don't close your file (stream) properly in the face of an exception - which is what your procedure will get from SQL Server when being interrupted by a user (more specifically a ThreadAbortException).

Note though, that even if you do handle this correctly, there are still possibilities (e.g. error handle takes "too long") for this cleanup code to fail or not execute to completion, hence leaving the file open.

You may want to check this blog entry for further information.

Sql-server – Stored Procedure as Create Table proxy

Yes, it should be working how you think. Below is an example proving this:

-- create the dummy login
create login TestLogin1
with password = 'p@$$w0rd';
go

use TestDB;
go

-- create the test user
create user TestUser1
for login TestLogin1;
go

-- create the proc to create a table
create procedure dbo.CreateTableProc
as

    create table dbo.SomeTestTable(id int);

go

-- give TestUser1 perms to execute CreateTableProc
grant execute
on dbo.CreateTableProc
to TestUser1;
go


-- execute CreateTableProc under the security context of TestUser1
execute as user = 'TestUser1';
go

exec dbo.CreateTableProc;
go

revert;
go
-- unsuccessful, permission denied

Msg 262, Level 14, State 1, Procedure CreateTableProc, Line 4
CREATE TABLE permission denied in database 'TestDB'.

-- alter the proc to execute under my security context (with CREATE TABLE perms)
alter procedure dbo.CreateTableProc
    with execute as owner
as

    create table dbo.SomeTestTable(id int);

go

-- execute CreateTableProc under the security context of TestUser1
execute as user = 'TestUser1';
go

exec dbo.CreateTableProc;
go

revert;
go
-- successful, table created

Command(s) completed successfully.

In this example, TestUser1 does not have the permissions to create the table. We see that when the original version of CreateTableProc is called, as the CREATE TABLE DDL executes under the security context of TestUser1. But then by modifying the CreateTableProc stored procedure definition and including the WITH EXECUTE AS OWNER clause, now TestUser1 can successfully call the proc and create a table because now the CREATE TABLE DDL executes under the security context of the owner (my database user, in the db_owner role).

Best Answer

Related Solutions

Kill CLR stored procedure process

Sql-server – Stored Procedure as Create Table proxy

Related Question