June 25, 2018 at 6:46 am
I have used this original post back then to write a script to scan a specified folder for files with .sql extension, execute them across all test DB's we have, attach to an email and to remove from that folder.
Perhaps it will save you some time:
DECLARE @sql_files_path VARCHAR(100) = '\\YourServer\c$\Temp\SQLScripts\',
@file_name VARCHAR(100),
@db_name VARCHAR(50),
@CommandString VARCHAR(500),
@return_value INT,
@error_message NVARCHAR(1000) = '',
@email_address VARCHAR(1000) = 'yourEmail@Organisation.com',
@email_subject NVARCHAR(1000),
@email_body NVARCHAR(MAX),
@number_of_records INT,
@delete_file VARCHAR(1000);
SET @email_subject = 'Automated Processing Results of SQL Files on Server '+@@SERVERNAME + ' Scanned and Loaded From Folder '+@sql_files_path;
--==== START Create temp table to hold error messages =====--
IF OBJECT_ID('tempdb..#error_messages') IS NOT NULL
DROP TABLE #error_messages;
CREATE TABLE #error_messages (err_message NVARCHAR(500));
--******* END *********---
--==== START Create temp table to hold final messages =====--
IF OBJECT_ID('tempdb..#final_messages') IS NOT NULL
DROP TABLE #final_messages;
CREATE TABLE #final_messages (final_message NVARCHAR(MAX));
--******* END *********---
--==== START Get list of SQL files within the above folder =====---
IF OBJECT_ID('tempdb..#directoryTree') IS NOT NULL
DROP TABLE #directoryTree;
CREATE TABLE #directoryTree (
id INT IDENTITY(1,1),
SQL_file_name NVARCHAR(500),
depth INT,
is_file BIT);
INSERT INTO #directoryTree
EXEC xp_dirtree @sql_files_path, 1, 1;
DELETE FROM #directoryTree
WHERE is_file = 0
OR SQL_file_name NOT LIKE '%.sql'
--******** END **********--
--=== Use cursor to process files one by one. ====---
DECLARE OSQL_cursor CURSOR
READ_ONLY FOR
SELECT SQL_file_name
FROM #directoryTree
ORDER BY SQL_file_name
OPEN OSQL_cursor
FETCH NEXT FROM OSQL_cursor INTO @file_name
WHILE (@@FETCH_STATUS <> -1)
BEGIN
--=== Here comes a new cursor to change DB's
DECLARE db_cursor CURSOR
READ_ONLY FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE dbid > 4
AND name NOT LIKE '%MCF%'
AND name NOT LIKE '%TFS%'
AND name NOT LIKE '%Notes%'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db_name
WHILE (@@FETCH_STATUS <> -1)
BEGIN -- start of db_cursor
SET @CommandString = 'osql -S ' + @@ServerName + ' -E -n -b -d ' + @db_name + ' -i "' + @sql_files_path + @file_name + '"';
DELETE FROM #error_messages;
INSERT INTO #error_messages
EXEC @return_value = master.dbo.xp_cmdshell @CommandString;
IF (@return_value = 0)
BEGIN
INSERT INTO #final_messages
VALUES( 'Successfully applied ' + @sql_files_path+@file_name + ' on ' + @db_name);
END
ELSE
BEGIN
--********* 'Error Handling from here: http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/how-to-capture-error-output-from-xp_cmds/'
SELECT @error_message = @error_message + err_message
FROM #error_messages
WHERE err_message IS NOT NULL;
INSERT INTO #final_messages
VALUES(' Database '+@db_name+'. File name '+@sql_files_path+@file_name+' '+@error_message);
DELETE FROM #error_messages;
SET @error_message = '';
END
FETCH NEXT FROM db_cursor INTO @db_name
END; --- end db_cursor
CLOSE db_cursor;
DEALLOCATE db_cursor;
--just before moving to the next sql file, delete current one from the folder
SET @delete_file = 'del '+@sql_files_path+@file_name;
EXEC @return_value = master.dbo.xp_cmdshell @delete_file;
FETCH NEXT FROM OSQL_cursor INTO @file_name
END
CLOSE OSQL_cursor;
DEALLOCATE OSQL_cursor;
--- Anything was processed?
SELECT @number_of_records = COUNT(*) FROM #final_messages
IF @number_of_records > 0
BEGIN
SET @email_body = N'<style type="text/css">
#box-table
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
text-align: center;
border-collapse: collapse;
border-top: 7px solid #039;
border-bottom: 7px solid #039;
}
#box-table th
{
font-size: 13px;
font-weight: normal;
background: #fff;
border-right: 2px solid #039;
border-left: 2px solid #039;
border-bottom: 2px solid #039;
color: #039;
}
#box-table td
{
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #669;
text-align: left
}
tr:nth-child(odd) { background-color:#eee; }
tr:nth-child(even) { background-color:#fff; }
</style>'+
N'<H3><font color="Red"> Below is a result of processed files.' +
N'<table id="box-table" cellpadding="10" >' +
N'<tr><font color="Green"><th>Result</th>
</tr>' +
CAST ( (
SELECT td = [final_message]
FROM #final_messages
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@recipients = @email_address,
@subject = @email_subject,
@body = @email_body,
@body_format = 'HTML' ;
END;
June 25, 2018 at 7:20 am
I know this was a followup to an old SQL2008 question, but, if you're running SQL 2017, check out the new DMV `sys.dm_os_enumerate_filesystem`
eg:
SELECT * FROM sys.dm_os_enumerate_filesystem('C:\Office','*')
Thomas Rushton
blog: https://thelonedba.wordpress.com
June 25, 2018 at 8:08 am
ThomasRushton - Monday, June 25, 2018 7:20 AMI know this was a followup to an old SQL2008 question, but, if you're running SQL 2017, check out the new DMVsys.dm_os_enumerate_filesystemeg:
SELECT * FROM sys.dm_os_enumerate_filesystem('C:\Office','*')
It's an awesome tool. I wish they'd back port some of this stuff to 2016. I also wish they'd add a BULK EXPORT, support for true CSV and TSV files, and some file handling.
EDIT - BE AWARE THAT ALL OF THE DATE/TIMES ARE UTC!
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2018 at 6:35 pm
Jeff Moden - Monday, June 25, 2018 8:08 AMThomasRushton - Monday, June 25, 2018 7:20 AMI know this was a followup to an old SQL2008 question, but, if you're running SQL 2017, check out the new DMV `sys.dm_os_enumerate_filesystem`eg:
SELECT * FROM sys.dm_os_enumerate_filesystem('C:\Office','*')It's an awesome tool. I wish they'd back port some of this stuff to 2016. I also wish they'd add a BULK EXPORT, support for true CSV and TSV files, and some file handling.
May be awesome, but still adding no value to the product.
It's may appear useful only to those who believe in false advertising of security issues around xp_cmdshell.
Sadly, the array of those believers includes MS developers themselves.
They'd better spend their effort on introducing "BIG FLOAT" data type.
It's a shame they allow 17 bytes to imprecise DECIMAL data types, and limit FLOAT to 8 bytes only.
But maybe it's due to limitations of floating-point calculations in contemporary processors.
_____________
Code for TallyGenerator
June 25, 2018 at 10:12 pm
Sergiy - Monday, June 25, 2018 6:35 PMMay be awesome, but still adding no value to the product.
It's may appear useful only to those who believe in false advertising of security issues around xp_cmdshell.
Sadly, the array of those believers includes MS developers themselves.They'd better spend their effort on introducing "BIG FLOAT" data type.
It's a shame they allow 17 bytes to imprecise DECIMAL data types, and limit FLOAT to 8 bytes only.
But maybe it's due to limitations of floating-point calculations in contemporary processors.
You'll get no argument from me on the merits of xp_CmdShell and the misinformation about it being a security risk.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 28, 2018 at 11:34 am
Sergiy - Monday, June 25, 2018 6:35 PMJeff Moden - Monday, June 25, 2018 8:08 AMThomasRushton - Monday, June 25, 2018 7:20 AMI know this was a followup to an old SQL2008 question, but, if you're running SQL 2017, check out the new DMV `sys.dm_os_enumerate_filesystem`eg:
SELECT * FROM sys.dm_os_enumerate_filesystem('C:\Office','*')It's an awesome tool. I wish they'd back port some of this stuff to 2016. I also wish they'd add a BULK EXPORT, support for true CSV and TSV files, and some file handling.
May be awesome, but still adding no value to the product.
It's may appear useful only to those who believe in false advertising of security issues around xp_cmdshell.
Sadly, the array of those believers includes MS developers themselves.They'd better spend their effort on introducing "BIG FLOAT" data type.
It's a shame they allow 17 bytes to imprecise DECIMAL data types, and limit FLOAT to 8 bytes only.
But maybe it's due to limitations of floating-point calculations in contemporary processors.
Floating point math is non-trivial for sizes beyond 64-bits in a 64-bit processor. Decimal math on the other hand, not so much.
EDIT: I do think we'll eventually get 128 bit processors, but the amount of design time to double up from 64-bit to 128-bit is going to be large because the design complexity is orders of magnitude larger.
2ND EDIT: see here: https://en.wikipedia.org/wiki/128-bit
- specifically, the following text: "While there are currently no mainstream general-purpose processors built to operate on 128-bit integers or addresses, a number of processors do have specialized ways to operate on 128-bit chunks of data. The IBM System/370 could be considered the first simple 128-bit computer, as it used 128-bit floating-point registers. Most modern CPUs feature single-instruction multiple-data (SIMD) instruction sets (Streaming SIMD Extensions, AltiVec etc.) where 128-bit vector registers are used to store several smaller numbers, such as four 32-bit floating-point numbers. A single instruction can then operate on all these values in parallel. However, these processors do not operate on individual numbers that are 128 binary digits in length; only their registers have the size of 128 bits."
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 11, 2019 at 3:52 pm
Jeff Moden - Thursday, January 21, 2010 10:33 PMWithout xp_CmdShell... EXEC Master.dbo.xp_DirTree 'yourpathhere',1,1You can use INSERT/EXEC to capture the info in a table if you prebuild the table.
I am able to successfully retrieve the folder entries using xp_DirTree as long as it is for a locally assigned share but when I reference a location that isn't local to the server it returns no values. I then used BULK INSERT to read one of the files in the non-local folder I am able to read the file in. What could be preventing me from getting the directory listing? I can locate the desired folder using Explorer from the server.
Example:
EXEC Master.dbo.xp_DirTree '\\fs01\it dept$\SQL\Files\',1,1 - Returns no data
BULK INSERT #TempLoad FROM '\\fs01\it dept$\SQL\Files\TestLoad.txt' ... - Successfully loads contents of text file to temp table
August 23, 2019 at 4:20 am
These approaches work for me...
First, xp_cmdshell will launch a cmd.exe process, so if we want to use Powershell, we have to launch Powershell as a sub-process within cmd.exe, and pass in the Powershell code we want to run. The console output will be captured and written to our table.
Second, as far as I know, xp_cmdshell will not parse our output in any way, so we need a way to reliably parse the results. CSV or XML spring to mind.
Here is the Powershell code I used:
-- table variable to hold our results
DECLARE @t TABLE (
line VARCHAR(1000)
)
-- I found it easier to create this long command string in pieces
-- This also makes it easier to comment out sections while debugging
DECLARE @cmd VARCHAR(1000) = ''
SET @cmd += 'powershell -NoProfile -NonInteractive -Command '
SET @cmd += '"'
SET @cmd += 'Get-ChildItem C:\Windows\System32'
SET @cmd += ' | Where-Object {-not $_.PSIsContainer}'
SET @cmd += ' | Select-Object -Property Fullname, Length, @{Name="""LastWriteTime"""; Expression={$_.LastWriteTime.ToString("""yyyy-MM-dd HH:mm:ss""")}}'
SET @cmd += ' | ConvertTo-Csv -NoTypeInformation'
SET @cmd += ' | Select-Object -skip 1'
SET @cmd += ' | Foreach-Object {$_ -replace ''""'', ''''}'
SET @cmd += '"'
-- For debugging. To test this, paste this into a CMD window (NOT Powershell).
PRINT @cmd
INSERT INTO @t
(
line
) EXECUTE xp_cmdshell @cmd
SELECT * FROM @t
Explanation of the Powershell code:
If there is a simpler way to do this please post a reply.
That's all well and good, but we want to parse the results.
I don't take credit for the below approach. See http://jahaines.blogspot.com/2009/06/converting-delimited-string-of-values.html.
;
WITH split_char (line,items)
AS (
SELECT line
,CONVERT(XML,
'<Line><Item>'
+ REPLACE(line,',','</Item><Item>')
+ '</Item></Line>') AS items
FROM @t
WHERE line IS NOT NULL
)
SELECT
line
,items.value('/Line[1]/Item[1]','VARCHAR(256)') AS Fullname
,items.value('/Line[1]/Item[2]','BIGINT') AS Filesize
,items.value('/Line[1]/Item[3]','DATETIME2(0)') AS LastWriteTime
FROM split_char
I personally like Powershell, even with the verbosity of the above command. However, if you want to use cmd.exe processing, here is an alternative approach:
INSERT INTO @t
(
line
) EXECUTE xp_cmdshell 'for %I in (C:\Windows\System32\*.*) do @echo %~fI,%~zI,%~tI'
;
WITH split_char (line,items)
AS (
SELECT line
,CONVERT(XML,
'<Line><Item>'
+ REPLACE(line,',','</Item><Item>')
+ '</Item></Line>') AS items
FROM @t
WHERE line IS NOT NULL
)
SELECT
line
,items.value('/Line[1]/Item[1]','VARCHAR(256)') AS Fullname
,items.value('/Line[1]/Item[2]','BIGINT') AS Filesize
--,items.value('/Line[1]/Item[3]','DATETIME2(0)') AS LastWriteTime
FROM split_char
However, I'm not aware of an easy way to cast '22/08/2013 09:21 PM' into a DATETIME2 column. And even if I did, I would be subject to the Regional Settings of the end user's machine.
Returning to the Powershell example, it also can return XML output:
DECLARE @t TABLE (
line VARCHAR(1000)
)
DECLARE @cmd VARCHAR(1000) = ''
SET @cmd += 'powershell -NoProfile -NonInteractive -Command '
SET @cmd += '"'
SET @cmd += 'Get-ChildItem C:\Windows'
SET @cmd += ' | Where-Object {-not $_.PSIsContainer}'
SET @cmd += ' | Select-Object -Property Fullname, Length, @{Name="""LastWriteTime"""; Expression={$_.LastWriteTime.ToString("""yyyy-MM-dd HH:mm:ss""")}}'
SET @cmd += ' | ConvertTo-Xml -NoTypeInformation -As string'
SET @cmd += '"'
-- For debugging. Paste this into a CMD window (NOT Powershell) to test.
PRINT @cmd
INSERT INTO @t
(
line
) EXECUTE xp_cmdshell @cmd
SELECT *
FROM @t
WHERE line IS NOT NULL
This is where my skill set runs out. I tried this:
DECLARE @s XML
SELECT @s=STUFF((SELECT N',' + LTRIM(line) FROM @t FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,1,N'')
SELECT @s
Which converts the separate lines from xp_cmdshell into an XML string, but I don't know how to convert that to a table of Fullname, Length, LastWriteTime.
I think the XML approach might(?) be the way to go once someone posts how to do the conversion.
I'm on SQL Server 2012, so JSON isn't an option (just replace ConvertTo-Xml with ConvertTo-Json).
August 26, 2019 at 9:29 pm
Does anyone know how to read the Powershell XML output into a table of columns?
August 27, 2020 at 5:38 pm
Hi,
My command is : EXEC Master.dbo.xp_DirTree 'D:\Backups\',1,1
And I have one file in the directory, but the out is : (0 rows affected)
I am running this on SQL Server 2017 version.
Any thoughts please?
August 27, 2020 at 6:46 pm
Hi,
My command is : EXEC Master.dbo.xp_DirTree 'D:\Backups\',1,1
And I have one file in the directory, but the out is : (0 rows affected)
I am running this on SQL Server 2017 version.
Any thoughts please?
Yes... Which machine is the "D:" drive you're trying to interrogate located and from which machine are you running the command?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 27, 2020 at 7:16 pm
Hi,My command is : EXEC Master.dbo.xp_DirTree 'D:\Backups\',1,1And I have one file in the directory, but the out is : (0 rows affected)I am running this on SQL Server 2017 version.Any thoughts please?
1. providing you haven't mapped the drive, you could do something like EXEC XP_CMDSHELL 'net use Y: \\YourRemoteServer\YourSharedFolder'
2. If I am not mistaken, a year ago or so someone also has shown how to call to a network folder without mapping it. Perhaps anyone can remind here how, please?
August 28, 2020 at 11:38 am
Finally it turns out that permissions to the folder were required, permissions to the SQL server service account. Once this was granted
EXEC Master.dbo.xp_DirTree 'D:\Backups\',1,1 ==> Command worked !
Thanks all for the inputs
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply