January 21, 2021 at 8:18 pm
I have a SqlAgentJob calling a stored proc. that loops through a list of locations and passes the location value and other parameters to a stored procedure. I would like to save each of the results to a CSV on the server. Because i have 50+ locations my current method of a separate sqlAgenjob using a sqlcmd to save is not possible a new locations are added or removed. Is there a way to save a sql query result to a csv. I know that you can save the results to a csv from the SSMS window, but i need to run at a scheduled time. and the 50 + reports takes hours to run.
January 21, 2021 at 8:39 pm
January 22, 2021 at 4:19 pm
Since you are using a SQL Agent Job for this, in the settings for the SQL Agent Job STEP, you can tell it to output to a file. You should be able to set that to append after each job step or not (depending on the requirements). then any data set output from the command would be written out to a text file.
I would do some testing with this to make sure it gives things in the format you want. If it doesn't you may need to get fancy and do things in SSIS instead.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 22, 2021 at 5:20 pm
You might be able to setup a Powershell script to do this - and setup the agent job to execute that script. A simple driver table to identify the parameters for each loop should work.
In the script - use Invoke-SqlCmd to get the data from your driver table, loop over the results and call your stored procedure with the appropriate parameters and output to a variable - and then use Export-Csv to export the data from the procedure to a file.
You can dynamically create the file based on the parameters or configuration in your driver table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 27, 2021 at 2:34 pm
A bunch of years ago, I created a terrible stored procedure to allow me to export data from the SQL Server. This is for my own internal use, so it's not very polished, but it's essentially what you're trying to do.
This requires xp_cmdshell to run. The SQL Server Service account needs to have permissions to write to the directory you're trying to export to.
The setup is that you insert data into a global temp table, and then call the stored procedure. The procedure alters the table to VARCHAR datatypes so that it can add the column headers to the output file.
If you want the data to come out sorted, it tries to cheat by putting a clustered index on your sort columns.
The delimiter field is optional, defaulting to comma.
Sample would be something like
select * into ##Blah from sys.databases
exec ExportToDatafileDelim '##Blah', 'E:\OutputDirectory\Databases.csv'
CREATE PROCEDURE [dbo].[ExportToDatafileDelim]
-- Add the parameters for the stored procedure here
@NameOfTableToExport VARCHAR(200),
@ExportPath VARCHAR(1000),
@OrderByCols VARCHAR(1000) = NULL,
@Delimiter char(1) = ','
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @AlterStmt VARCHAR(MAX) = ''
SELECT @AlterStmt = @AlterStmt + 'ALTER TABLE ' + @NameOfTableToExport + ' ALTER COLUMN [' + b.column_name + '] VARCHAR(500);'
FROM TEMPDB.INFORMATION_SCHEMA.TABLES a
INNER JOIN TEMPDB.INFORMATION_SCHEMA.Columns b ON a.table_name = b.table_name
WHERE a.table_name = @NameOfTableToExport
and b.DATA_TYPE <> 'varchar'
if @AlterStmt <> ''
EXEC(@AlterStmt)
SET @AlterStmt = ''
SELECT @AlterStmt = @AlterStmt + 'UPDATE ' + @NameOfTableToExport + ' SET [' + b.column_name + '] = REPLACE([' + b.column_name + '],''' + @Delimiter + ''','''');'
FROM TEMPDB.INFORMATION_SCHEMA.TABLES a
INNER JOIN TEMPDB.INFORMATION_SCHEMA.Columns b ON a.table_name = b.table_name
WHERE a.table_name = @NameOfTableToExport
EXEC(@AlterStmt)
IF @OrderByCols IS NOT NULL BEGIN
SET @AlterStmt = 'CREATE CLUSTERED INDEX IX_PK ON ' + @NameOfTableToExport + ' (' + @OrderByCols + ')'
EXEC(@AlterStmt)
END
DECLARE @SQL VARCHAR(8000) = ''
DECLARE @ColumnHeaders VARCHAR(max) = ''
SELECT @ColumnHeaders = @ColumnHeaders + '''''' + b.column_name + ''''' [' + b.column_name + '],'
FROM TEMPDB.INFORMATION_SCHEMA.TABLES a
INNER JOIN TEMPDB.INFORMATION_SCHEMA.Columns b ON a.table_name = b.table_name
WHERE a.table_name = @NameOfTableToExport
declare @path VARCHAR(1000)
SET @path=SUBSTRING(@ExportPath,1,len(@ExportPath) - CHARINDEX('\',REVERSE(@ExportPath))+1)
set @sql = 'DIR "' + @path + '"'
print @sql
CREATE TABLE #DirInfo(line varchar(255))
INSERT INTO #DirInfo(line)
EXEC master..xp_cmdshell @SQL
if EXISTS(select 1 from #DirInfo where line = 'The system cannot find the file specified.') BEGIN
print 'Creating dir ' + @path
exec master..xp_create_subdir @path
END
drop table #DirInfo
SET @SQL =
'DECLARE @SQLOUT VARCHAR(8000) ' +
'SET @SQLOUT = ''bcp "SELECT ' + LEFT(@ColumnHeaders,LEN(@ColumnHeaders)-1) +
' UNION ALL SELECT * FROM ' + @NameOfTableToExport +
'" queryout "' + @exportpath + '" -c -t"' + @Delimiter +'" -T -S' + @@SERVERNAME + '''' +
' PRINT @SQLOUT; EXEC master..xp_cmdshell @SQLOUT'
EXEC(@SQL)
END
GO
February 1, 2021 at 9:05 pm
This might work, Tks
February 2, 2021 at 2:32 am
@stevenb,
I like it... a LOT! Nicely done!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply