Export to text files based on a value

Umm, why not use all SQL Server ?

Why the VB.Net or R for that matter ?

Would you be interested in a T-SQL solution ?

the next to t-sql best way is SSIS
check this tep-0by-step example
SSIS: Exporting data to a text file using a package
https://decipherinfosys.wordpress.com/2008/07/23/ssis-exporting-data-to-a-text-file-using-a-package/

Apologies, was interrupted by some business, and then New Year.

Happy New Year to you :)

Will get back into this later tonight my time.

Are there any control breaks ?
If driven by employee - is it possible to have other employers / start dates ?
If driven by employer can we have multiple employees and is there any control break logic/ grouping ?
Can we have multi-pages ?
What is the idea behind dbo.ConstantVariable - seems arbitrary ?

Probably a few more questions, but will await your response.

OK, we will do this in a couple of parts. Still a little unsure of a couple of things - like the datatypes/sizes of dbo.Employee

First part is to create a user defined function to do the formatting. We have 1 parameter @filename which we use to pass the Filename we want formatted.

Good thing about user defined functions is we can select from them.

Second part (which I will post after the user defined function has been verified) will be a stored procedure to actually export

So, the first part.... In SSMS paste the following code

-- to change after wards, you can ALTER FUNCTION.... rather than CREATE although, I prefer to DROP and then CREATE
--DROP FUNCTION [dbo].[udf_export_employee];

CREATE FUNCTION [dbo].[udf_export_employee](@filename varchar(50))
RETURNS @textfile table 
	(
	linectr int identity, 
	rundate datetime, 
	[Filename] varchar(50),
	exportrecord varchar (500)
	)
AS
BEGIN
	
	DECLARE @rundate datetime = getdate()
	DECLARE @pagecounter int = 0
	DECLARE @Employee_UniqueID varchar(50)
	DECLARE @Employee_Filename varchar(50)
	DECLARE filename_cursor CURSOR FOR SELECT UniqueID,[Filename] from Employee where [Filename] = @filename

	OPEN filename_cursor
	FETCH NEXT FROM filename_cursor INTO @Employee_UniqueID,@Employee_Filename
	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET @pagecounter = @pagecounter + 1

		IF @pagecounter = 1
		BEGIN
			INSERT @textfile SELECT @rundate,@filename,'------------------------ '+ConstantVariable+' ------------------------' 
                              FROM ConstantVariable where UniqueID = @pagecounter
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
		END
		ELSE
		BEGIN
			INSERT @textfile SELECT @rundate,@filename,'------------------------ Page '+convert(varchar(3),@pagecounter)+' ------------------------' 
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
   			INSERT @textfile SELECT @rundate,@filename,'                                                       '
		END


		INSERT @textfile SELECT @rundate,@filename,EmployeeName from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployeeAddress from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployeeCity from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployeeZip from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
    	INSERT @textfile SELECT @rundate,@filename,'                                                       '
		INSERT @textfile SELECT @rundate,@filename,EmploymentDate from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
    	INSERT @textfile SELECT @rundate,@filename,'                                                       '
		INSERT @textfile SELECT @rundate,@filename,EmployerName from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployerAddress from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployerCity from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
		INSERT @textfile SELECT @rundate,@filename,EmployerZip from Employee where UniqueID = @Employee_UniqueID and [Filename] = @Employee_Filename
    	INSERT @textfile SELECT @rundate,@filename,'                                                       '


		FETCH NEXT FROM filename_cursor INTO @Employee_UniqueID,@Employee_Filename

	END

	CLOSE filename_cursor
	DEALLOCATE filename_cursor

	IF @pagecounter = 0
    	INSERT @textfile SELECT @rundate,@filename,'No Employee Details Exist For '+@filename
	   

	RETURN
END
GO

Select all Open in new window

Now to test the required format, use the UDF with the Filename as the parameter

select * from udf_export_employee ('File1')

select * from udf_export_employee ('File2')

select * from udf_export_employee ('File3')

select * from udf_export_employee ('NoSuchFile')

Select all Open in new window

If you could run and test the above, nd get back to me, we will fix/finesse and then move onto the Stored Procedure

The Stored Procedure will use a utility called BCP to export. It is a command line utility that comes with SQL Server.
It looks like : 'bcp "select exportrecord from YourDB.dbo.udf_export_employee ('File3')" queryout "' + @ExportName + '"  -T -c -CACP'
To execute, we will need to enable xp_cmdshell but more on that later.

OK, so now the Stored Procedure....

But first we have to turn on (allow) a system procedure. We need to first "see" the advanced options of SQL Server, then enable XP_CMDSHELL. This can be considered as a once-off process

--step 1 turn on xp_cmdshell

-- First, we need to allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- Then update the currently configured value for advanced options.
RECONFIGURE
GO
-- Now we can enable the XP_CMDSHELL feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Select all Open in new window

So, once that has been done, we can now focus on the Stored Procedure. You will need to change the PATH (see comments in code) and change YOURDB to your database name (again see Comments).  

Now I have used some functions which needs SQL2012 or more recent.
 If you dont have 2012, or more recent, you need to let me know so I can adjust. The two newer functions are the FORMAT() and IIF().

-- ok, now the Stored Procedure

-- I always prefer to drop and re-create functions or stored procedures (+other objects)
-- so let's check if our object exists first

IF OBJECT_ID('dbo.usp_Output_Employee') IS NOT NULL
    DROP PROCEDURE dbo.usp_Output_Employee;
GO

-- now we can create our stored procedure
-- it has an optional parameter for specifying a filename

CREATE PROCEDURE dbo.usp_Output_Employee (@filename varchar(50) = NULL)    -- @filname parameter is optional
AS
DECLARE @Path       varchar(50) = 'C:\MRWTEST\'          -- you need to change this
DECLARE @rundate    varchar(30) = FORMAT(getdate(),'MMddyyyy')
DECLARE @sqlcommand varchar(500)
DECLARE @QryoutName varchar(500)
DECLARE @bcpCommand varchar(500)

IF OBJECT_ID('dbo.udf_Export_Employee') IS NOT NULL   -- let's double check the UDF function exists before we start tying to use it
BEGIN

	DECLARE @Employee_Filename varchar(50)
	DECLARE export_cursor CURSOR FOR SELECT distinct[Filename] from Employee where [Filename] = IIF(isnull(@filename,'') > '',@filename,[Filename])

	OPEN export_cursor 
	FETCH NEXT FROM export_cursor INTO @Employee_Filename
	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET @QryoutName = @Path+@Employee_Filename+'_'+@rundate+'.txt'
		SET @sqlcommand = 'select exportrecord from YOURDB.dbo.udf_export_employee ('+''''+@Employee_Filename+'''' +')'      --  change YOURDB to your database name
		SET @bcpCommand = 'bcp "'+@sqlcommand+'" queryout "' + @QryoutName + '" -T -c -CACP'
		--print @bcpcommand
		EXEC master..xp_cmdshell @bcpCommand

		FETCH NEXT FROM export_cursor INTO @Employee_Filename

	END

	CLOSE export_cursor
	DEALLOCATE export_cursor

END
GO

Select all Open in new window

To test, it is pretty straight forward, just execute the Stored Procedure

exec dbo.usp_Output_Employee 
-- or for a specific filename
exec dbo.usp_Output_Employee @filename='File3'
-- then go check your @path to see the files

Select all Open in new window

BCP stands for Bulk Copy Program and is a command line utility that comes with SQL Server. Thats why we need XP_CMDSHELL so we can run it (launch it) from with SQL Server. You could also run it from the command prompt. See : https://docs.microsoft.com/en-us/sql/tools/bcp-utility

Now the BCP command is being built up using Dynamic SQL and uses a TRUSTED Connection via parameter -T you can change it if needed to use a username and password. The parameters are in the BCP link.

Could be a couple of things...

Most likely with SQL Express, is that SQL Express installs as an "instance" on your computer.

Sounds like we need to add the parameter-S server_name [\instance_name]  for BCP.  Using SQLExpress this will be something like :
 -S YourComputerName\SQLExpress  or -S .\SQLExpress
normally put in front of the -T

Edit your Stored procedure, and change the @BCPCOMMAND line to include the -S , and while editting, also, comment out the XP_CMDSHELL and remove the '--' on the print @bcp  IE

		SET @bcpCommand = 'bcp "'+@sqlcommand+'" queryout "' + @QryoutName + '" -S .\SQLExpress -T -c -CACP'     -- might need to replace the -S .\SQLExpress with -S YourComputerName\SQLExpress 
		print @bcpcommand
		--EXEC master..xp_cmdshell @bcpCommand

Select all Open in new window

then recreate the procedure (or use ALTER instead of CREATE)
Then execute the procedure. You should get a few of the BCP lines being printed.

Select and Copy one of those lines, open a command prompt (or a DOS prompt in old terms) and paste the line, press enter.

If you get errors you can modify the line in the command prompt to try the other -S setting.

Dont close anything, we will continue. Tip, in SSMS at the top of the tree on the left hand side. Your Computer Name (as far as SQL is concerned) will be visible. We might need to go into Properties by right clicking on your Computer name to check some properties.

First, try using the -S parameter for BCP

A pleasure, happy to have been able to help and very happy you have found a solution.

Cheers,
Mark Wills