Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

PowerShell Module to Read Directory Contents and Store in a SQL Server Table


By:   |   Last Updated: 2019-01-28   |   Comments (21)   |   Related Tips: More > PowerShell

Problem

In this tip we will walk through how to create a PowerShell module to get a list of files and subfolders of a Windows folder and store this information in a SQL Server table.

Solution

In this tip, I am going to explain the code of a PowerShell module which gets a list of files that reside in a folder and stores this information in a SQL Server table.

I am going to:

  1. Create a SQL table that has a column to store FQN (Fully qualified name) of any file.
  2. Show how to write the PowerShell code.
  3. Execute PowerShell function using T-SQL.

In this tip, I have used the following PowerShell cmdlets.

PowerShell cmdlet Description
Invoke-SQLCmd It is used to execute a SQL command. Reference: Invoke-SQLcmd
Get-ChildItem It gets a list of files in a specified location. Reference: Get-ChildItem.
Foreach-Object It iterates through the collection of items and performs operations against each item. Reference: Foreach-Object

Create the SQL Server Table

To store the directory contents in a table, first I will create a table named "tblFileLocations" in database "DemoDatabase". The following code creates the SQL table.

USE DemoDatabase
GO

CREATE TABLE [dbo].[tblFileLocations](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[File_name] [varchar](max) NULL,
       [FileLocation] [varchar](max) NULL,
	[CreateDate] varchar(50) NULL,
	[LastChangeDate] varchar(50) NULL,
	[FileSize] [numeric](10,5) NULL,
PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Create a PowerShell Module

As I mentioned, I want to get the list of files within a specific directory. To do that, we will create a PowerShell cmdlet. It accepts the Drive Letter as a parameter, iterates through the entire drive and populates the following details:

  1. File Name
  2. Fully qualified file name
  3. File attribute
  4. Last access time
  5. Last modified time
  6. File Size

To execute this function using a SQL Stored procedure, we must register it as a PowerShell module. Once the PowerShell module is registered, we add this module in the PowerShell profile.

First, let’s understand the PowerShell script.

To understand the PowerShell code, I have split it into multiple sections:

Script Part-1

Create a PowerShell function. This function accepts the Drive Letter as a parameter. The following code block creates the function.

function global:getFileLocations
{
param(
    [Parameter(Position=0,mandatory=$true)]
    [string[]] $FileLocation
	)
}   

Script Part-2

Construct a string variable named "$sqlstatment" that has an "Insert" query statement. The following code creates a string variable.

[email protected]'
INSERT INTO tblFileLocations
(
	File_name,	
FileLocation, 
CreateDate,
LastChangeDate,
FileSize    
) 
VALUES (
	'{0}',
	'{1}',
	'{2}',
	'{3}',
	'{4}'    
)
'@

Script Part-3

Populate list of files command and formats the output according to the insert query stored in the "$sqlstatement" variable. The following is the code block.

Get-ChildItem -Recurse $FileLocation  | 
select Name,FullName,CreationTime,	LastWriteTime,
@{Label="Length";Expression={$_.Length / 1KB -as [int] }}  |
ForEach-Object 
{
$SQL = $sqlstatement -f  $_.name,$_.FullName, $_.CreationTime,$_.LastWriteTime,$_.Length		
Invoke-sqlcmd -Query $SQL -ServerInstance “TTI412-VM\SQL2017” -database “DemoDatabase”
}

The above code block performs the following tasks:

  1. It passes "$FilePath" parameter to "Get-ChildItem -Recurse" cmdlet and populates the list of files. "$FilePath" parameter is drive letter/ directory location (C:\Temp\...).
  2. Format the output, generated by "Get-ChildItem -Recurse" in multiple columns. Column sequence of output and the insert statement which is stored in "$sqlstatement" parameter must be the same.
The output of "Get-ChildItem -Recurse" Column sequence in the insert query
FullName {0}
Name {1}
 CreationTime {2}
LastWriteTime {3}
FileSize {4}
  1. It creates a new string variable named "$SQLQuery" by concatenating "$sqlstatement" and the values generated by "Get-ChildItem -Recurse" command. The format of "$SQLQuery" parameter is like a T-SQL insert statement. Using "ForEach-Object" cmdlet, it generates multiple insert statements and stores it in a $SQLQuery variable. See the following image:
powershell output
  1. By using "Invoke-SQLCmd" command, it inserts the value of the $SQLQuery variable into the tblFileLocations table.

Complete PowerShell Script

function global:getFileLocations
{
param(
    [Parameter(Position=0,mandatory=$true)]
    [string[]] $FileLocation
)
Write-Output "Inserting File Locations in table"
[email protected]'
INSERT INTO tblFileLocations(
	File_name,FileLocation,CreateDate,LastChangeDate,FileSize    
) 
VALUES (
	'{0}',
	'{1}',
	'{2}',
	'{3}',
	'{4}'    
)
'@
Get-ChildItem -Recurse $FileLocation  | 
select Name,FullName,CreationTime,	LastWriteTime,
@{Label="Length";Expression={$_.Length / 1KB -as [int] }}  |
	ForEach-Object {
		$SQL = $sqlstatement -f  $_.name,$_.FullName, $_.CreationTime,$_.LastWriteTime,$_.Length		
        Invoke-sqlcmd -Query $SQL -ServerInstance “TTI412-VM\SQL2017” -database “DemoDatabase”
	}
Write-Output "File locations are saved in table"
}

Save Script as a PowerShell Module

To execute the PowerShell function using T-SQL, register this script as PowerShell module. To register any PowerShell script, first a directory must be created in "C:\Windows\System32\WindowsPowerShell\v1.0\Modules". The name of the PowerShell script and directory must be the same. To register the above script, perform the following tasks:

  1. Create a new directory named "getFileLocations" in "C:\Windows\System32\WindowsPowerShell\v1.0\Modules" directory.
  2. Save the PowerShell script as getFileLocations.psm1 in "getFileLocations" directory.
powershell GetFileLocations

Execute the PowerShell Function in T-SQL Script

To execute a PowerShell function in a stored procedure, enable xp_cmdshell. To do that, execute the following command:

use master
go

exec sp_configure 'show advanced option',1
reconfigure with override

exec sp_configure 'xp_cmdshell',1
reconfigure with override

To execute the PowerShell script using a T-SQL query, use the "powershell.exe" command. As I mentioned, our script inserts the fully qualified path of files located in a specific drive or specific directory, hence we must create a parameterized T-SQL script. In the script, we can pass the drive letter OR directory path as a parameter.

For this demonstration, I created a folder named "UPADHYAY ALBUM"" in my E drive. I copied a few SQL scripts to the folder. I am going to insert the file details of all directories and files located in the E:\UPADHYAY ALBUM\ folder. To do that, execute the following code:

declare @PSScript varchar(2500)
declare @FileLocation varchar(2500)
set @FileLocation='D:\UPADHYAY ALBUM'
set @PSScript= 'powershell.exe getFileLocations ''' + @FileLocation +'''' 
exec xp_cmdshell @PSScript

Let’s verify that whether file details are stored in a table using the following code:

SELECT * FROM tblFileLocations

As you can see in the following output, the fully qualified names and details from the "E:\UPADHYAY ALBUM\" directory is stored in the "Fully_Qualified_FileName" " column.

query results

Summary

In this article, I explained:

  1. How to create a PowerShell module and how the PowerShell script works.
  2. Execute the PowerShell function using T-SQL code.
Next Steps


Last Updated: 2019-01-28


next webcast button


next tip button



About the author
MSSQLTips author Nisarg Upadhyay Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional with more than 5 years of experience.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, January 30, 2019 - 3:47:52 AM - Nisarg Upadhyay Back To Top

Hello All,

We have identified couple of bugs in the script. I have changed the code and Tip is updated.

Hope it helps.

Nisarg


Thursday, January 03, 2019 - 12:21:23 AM - Robert C Back To Top

Alister,

I found my problem was the pipeline symbol at end of line 24. Also took your suggetion and made Length a bigint in the table. Don't understand why the pipeline was there in any case. Here's working code for the module.

Thanks, everyone, for your help. Learned a few things and can now read my tutorials with some practical experience and interest.

function get-FileSummary
{
param(
    [Parameter(Position=0,mandatory=$true)]
    [string[]] $FileLocation
)
[email protected]'
INSERT INTO tblFileSummary(
   File_Name, Fully_Qualified_FileName, attributes, CreationTime, LastAccessTime, LastWriteTime,
    Length    
) 
VALUES (
   '{0}',
   '{1}',
   '{2}',
   '{3}',
   '{4}',
   '{5}',
   '{6}'
)
'@
Invoke-Sqlcmd -Query "Truncate Table tblFileSummary" -ServerInstance localhost\SQLEXPRESS -database DemoDatabase
Get-ChildItem -Recurse $FileLocation  | 
select-object FullName, Name,attributes, CreationTime, LastAccessTime, LastWriteTime, Length 
   ForEach-Object {
      $SQL = $sqlstatement -f $_.FullName, $_.name,$_.attributes, $_.CreationTime, $_.LastAccessTime, $_.LastWriteTime, $_.Length
      Invoke-Sqlcmd -Query $SQL -ServerInstance localhost\SQLEXPRESS -database DemoDatabase
}
}
   Export-ModuleMember -Function 'Get-FileSummary'

Wednesday, January 02, 2019 - 5:08:06 PM - Alistair Back To Top

Looks to me like the error came from sql server. Have any of the filenames located under the folder you specified to the command got a single quote on them?

If that's the case, this code doesn't cater for that.

$_.FullName.Replace("'", "''")

And

$_.Name.Replace("'", "''")

Should escape any single quotes for the generated sql


Wednesday, January 02, 2019 - 10:11:50 AM - Robert C Back To Top

@Alistair,

I've learned a bit about profiles and modules but have one remaining problem.   I suspect the last issue is related to my basic lack of knowledge of Powershell, but I'm stuck.

Thanks in advance!

Robert

Here's the output:

Invoke-sqlcmd : Incorrect syntax near 's'.

Unclosed quotation mark after the character string '

)

'.

At C:\Program Files\WindowsPowerShell\Modules\getFileSummary\getFileSummary.psm1:27 char:9

+         Invoke-sqlcmd -Query $SQL -ServerInstance SURFSUP\SQLEXPRESS  ...

+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException

    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

 

Invoke-sqlcmd : Incorrect syntax near 's'.

Unclosed quotation mark after the character string '

)

'.

At C:\Program Files\WindowsPowerShell\Modules\getFileSummary\getFileSummary.psm1:27 char:9

+         Invoke-sqlcmd -Query $SQL -ServerInstance SURFSUP\SQLEXPRESS  ...

+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException

    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

 

NULL

 

The code is below; line endings from Notepad++ are shown for clarity with line 27 in bold:

function get-FileSummary

{

param(

    [Parameter(Position=0,mandatory=$true)]

    [string[]] $FileLocation

)

[email protected]'

INSERT INTO tblFileSummary(

   File_Name, Fully_Qualified_FileName, attributes, CreationTime, LastAccessTime, LastWriteTime,

    Length    

VALUES (

   '{0}',

   '{1}',

   '{2}',

   '{3}',

   '{4}',

   '{5}',

   '{6}'

)

'@

Invoke-Sqlcmd -Query "Truncate Table tblFileSummary;" -ServerInstance localhost\SQLEXPRESS -database DemoDatabase

Get-ChildItem -Recurse $FileLocation  | 

select-object FullName, Name,attributes, CreationTime, LastAccessTime, LastWriteTime,@{Label="Length";Expression={$_.Length / 1MB -as [int] }}|

   ForEach-Object {

      $SQL = $sqlstatement -f $_.FullName, $_.name,$_.attributes, $_.CreationTime, $_.LastAccessTime, $_.LastWriteTime, $_.Length

      Invoke-Sqlcmd -Query $SQL -ServerInstance localhost\SQLEXPRESS -database DemoDatabase

   }

Write-Output "File Inserted successfully... Below Is list of files"

Invoke-Sqlcmd -Query "Select * from tblFileSummary;" -ServerInstance localhost\SQLEXPRESS -database DemoDatabase

}

   Export-ModuleMember -Function 'Get-FileSummary'

 

 


Wednesday, December 26, 2018 - 3:07:57 PM - Alistair Back To Top

It may be formatting in these messages, but ensure there are not blank lines following lines that end with |  (pipeline operator).

You are getting no data at all, or just no filenames?

Test at a PowerShell command prompt that files are actually being selected, by assigning the $FileLocation variable with a path that exists on your system, e.g.

$FileLocation = 'C:\Temp'
Get-ChildItem -Recurse $FileLocation  | 
select-object Fullname,Name,attributes, CreationTime, LastAccessTime, LastWriteTime,@{Label="Length";Expression={$_.Length / 1MB -as [int] }}

This should return a list of the file properties as specificed by the select-object command.


Monday, December 24, 2018 - 6:27:53 PM - Robert C Back To Top

 Alastair,

Kind of you to dig into this with me but making the changes suggested to the select statement still doesn't fix the missing data problem.

 

My code looks like this now:

...

Invoke-Sqlcmd -Query "Truncate Table tblFileSummary" -ServerInstance SURFSUP\SQLEXPRESS -database DemoDatabase

Get-ChildItem -Recurse $FileLocation  | 

select Fullname,Name,attributes, CreationTime, LastAccessTime, LastWriteTime,@{Label="Length";Expression={$_.Length / 1MB -as [int] }}|

   ForEach-Object {

      $SQL = $sqlstatement -f $_.FullName, $_.name,$_.attributes, $_.CreationTime, $_.LastAccessTime, $_.LastWriteTime,$_.Length                            

        Invoke-sqlcmd -Query $SQL -ServerInstance SURFSUP\SQLEXPRESS -database DemoDatabase

 

    }

 

Did I misunderstand?

 

Thanks,

Rob


Monday, December 24, 2018 - 9:29:52 AM - Alistair Back To Top

It would appear that there is a bug in Script Part 3. It should be

 

Invoke-Sqlcmd -Query "Truncate Table tblFileSummary" -ServerInstance TTI412-VM\SQL2017 -database DemoDatabase
Get-ChildItem -Recurse $FileLocation | 
select Fullname,Name,attributes, CreationTime, LastAccessTime, LastWriteTime,@{Label="Length";Expression={$_.Length / 1MB -as [int] }}|
   ForEach-Object {
      $SQL = $sqlstatement -f $_.FullName, $_.name,$_.attributes, $_.CreationTime, $_.LastAccessTime, $_.LastWriteTime,$_.Length                            
        Invoke-sqlcmd -Query $SQL -ServerInstance TTI412-VM\SQL2017 -database DemoDatabase

   }

Difference is the first two columns of the PowerShell select. I would be inclined to use the full syntax Select-Object there so as not to confuse it with SQL select.

Note also that this is rounding all file sizes to the nearest 1MB, thus all small files will be recorded as having a size of zero. If you want the file size in bytes then replace

@{Label="Length";Expression={$_.Length / 1MB -as [int] }}

with simply

Length

and ensure your SQL column is of a sufficiently wide type (e.g. BIGINT)





Sunday, December 23, 2018 - 7:28:49 PM - Robert C Back To Top

 No longer having any issues with server name etc. since I uninstalled the SQL Server 2017 Developer Edition and installed Express.  Query seems to run normally except two columns in the table are not being populated. File_name and Fully_Qualified_Filename are blank.

If I just run Powershell and look at output for get-childitem, the columns returned are Mode, LastWriteTime, Length, and Name.  I don't see where Name is somehow being transfromed to File_name and included in Fully_Qualified_Filename?  If these are automatic where are they documented?  It's been years since my Powershell class and I've not used it since.  Could a kind sould please explain or get me pointed to a tutorial that explains the magic?

Thanks,

Robert


Monday, December 17, 2018 - 9:24:17 AM - Alistair Back To Top

 If you have everything running on the same Windows box (SQL server, the PowerShell etc.) then you should be able to try

  1. In SQL Server Management Studio, connect to the server and run SELECT @@SERVERNAME. Get the result of this - that's your server name
  2. Open a powershell prompt and test the connection:
    Import-Module SQLPS ; Invoke-Sqlcmd -ServerInstance output_from_above_select -Query "SELECT @@VERSION"

If that doesn't work, there is probably something wrong with your sql server installation.


Monday, December 17, 2018 - 9:15:37 AM - Nisarg Upadhyay Back To Top

Hello Robert,

Sorry for delayed response. Answering your question: If you have installed multiple instances on your machine that you should use  "SurfsUp\MSSQLSERVER" If you're running this code on default instance than use "SurfsUp". One more thing, also check the network properties in SQL Server configuration manager. TCP/IP and Named Pipes should be enabled.

Thanks to all readers who answered Robert's question.


Friday, December 14, 2018 - 10:14:04 PM - Robert C Back To Top

 I've checked in Object Explorer / Server Properties / Connections and "Allow remote connections to this server" is ticked.  Also have checked in Services and SQL Server has (MSSQLSERVER) following the name so I believe the -ServerInstance parameters also to be correct:

-ServerInstance SurfsUp\MSSQLSERVER

 SURFSUP is the name of the computer.  Isn't that what is supposed to be used for the Server part of -ServerInstance?

The error message is below.

Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. 

The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is 

configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance 

Specified)

At C:\Program Files\WindowsPowerShell\Modules\getFileSummary\getFileSummary.psm1:24 char:1

+ Invoke-Sqlcmd -Query "Truncate Table tblFileSummary" -ServerInstance  ...

Would much appreciate someone steering me right as this looks like it's probably a simple error but I don't know SQL Server well enough to fix it myself.

Thanks!


Thursday, December 13, 2018 - 2:13:57 PM - Robert C Back To Top

Made the changes suggested but still getting an error that appears related to the instance name.  Using -ServerInstance SURFSUP\MSSQLSERVER as seen in Properties/Advanced for the server.  Actually, it shows Instance ID: MSSQL14.MSSQLSERVER but the Host Name is Surfsup.  What am I missing here?

output

Inserting files

Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. 

The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is 

configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)

At C:\Program Files\WindowsPowerShell\Modules\getFileSummary\getFileSummary.psm1:24 char:1

+ Invoke-Sqlcmd -Query "Truncate Table tblFileSummary" -ServerInstance  ...

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException

    + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

 

Invoke-Sqlcmd : 

At C:\Program Files\WindowsPowerShell\Modules\getFileSummary\getFileSummary.psm1:24 char:1

+ Invoke-Sqlcmd -Query "Truncate Table tblFileSummary" -ServerInstance  ...

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : ParserError: (:) [Invoke-Sqlcmd], ParserException

    + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand 

 ...


Wednesday, December 12, 2018 - 10:59:41 AM - Alistair Back To Top

Hi

Let's look first at the PowerShell Modules path, using the example pasted by Robert C

  • C:\Users\rob\Documents\WindowsPowerShell\Modules - This is the path for user scoped modules. Modules here would only be seen by processes running as rob
  • C:\Program Files\WindowsPowerShell\Modules - This is the shared (all users) module path and would be a better location for these scripts
  • C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules - This is the operating system modules path. You shouldn't add modules here!

Ok, now the scripts themselves. In the original text posted by Nisarg, unfortunately there are some inconsistencies in the spellings of the scripts and files, e.g. 'getFileSummery', 'getFileSummey.psm1', and the table tblFileSummary which could be leading to some confusion. The correct spelling is 'summary' :-)

Also, it's not good practice to create function global:name and it is good practice to follow PowerShell naming conventions, so function Get-FileSummary is a much better choice.

To get the function to be visible outside the module, remove the global: and add as the last line (after the last close brace of the function)

Export-ModuleMember -Function 'Get-FileSummary'

It is necessary when adding the module to the module path that the directory you create exactly matches the name of the PSM1 file you place in there.

Now, if we get all this right then we can test that the module is loadable from a regular PowerShell command prompt with

Import-Module getFileSummary

which shoud not error, and you should now be able to run Get-FileSummary from the powershell prompt, though it may well blow up here due to not being in SQL server context - not an issue.

Furthermore, since a module has been created, it needs to be imported to expose the function within. This will be either directly with script, or by use of a PowerShell profile (mentioned by Nisarg) that the SQL server and agent services can see - a machine wide profile

A better way to run PowerShell from within SQL server and without having to enable xp_cmdshell is to create a job in SQL agent with a PowerShell job step. Then you can simply run pretty much any powershell, and Import-Module your hand-rolled modules as the furst commands in the job step.


Wednesday, December 12, 2018 - 10:44:16 AM - Boris Shimonov Back To Top

 I'm getting following result

getFileSummary : The term 'getFileSummary' is not recognized as the name of a
cmdlet, function, script file, or operable program. Check the spelling of the
name, or if a path was included, verify that the path is correct and try again.
At line:1 char:1
+ getFileSummary 'd:\Scripts'
+ ~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (getFileSummary:String) [], Comm
   andNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException
 
NULL

 


Wednesday, December 12, 2018 - 10:39:06 AM - KM Back To Top

It looks like the problems most users are having stems from the spelling of the table "tblFileSummary" -ary vs. the function "getFileSummery" -ery. 

If the readers are copying code but then using the wrong (actually right) spelling to call it, it won't match.


Wednesday, December 12, 2018 - 10:08:23 AM - Robert C Back To Top

 Nisarg,

Thanks for responding.  I executed the command you suggested in Powershell but the end result of the SQL query is still the same. I notice the new profile created is empty.  Am I intended to add some code to the profile?

PS C:\WINDOWS\system32> New-Item -Type File -Path $PROFILE.AllUsersAllHosts -Force

    Directory: C:\Windows\System32\WindowsPowerShell\v1.0

Mode                LastWriteTime         Length Name

----                -------------         ------ ----

-a----       12/12/2018   7:00 AM              0 profile.ps1

Thanks,

Robert


Tuesday, December 11, 2018 - 11:58:37 PM - Nisarg Upadhyay Back To Top

Did you created a PowerShell profile? If not, than execute following command to do it.

New-Item -Type File -Path $PROFILE.AllUsersAllHosts -Force


Monday, December 10, 2018 - 8:27:00 PM - Robert C Back To Top

This looks so useful. I hope the author can help with the errors. 


Sunday, December 09, 2018 - 2:11:40 PM - Ioannis M. Back To Top

Unfortunately I get the same error

getFileSummery : The term 'getFileSummery' is not recognized as the name of a 

cmdlet, function, script file, or operable program. Check the spelling of the 

name, or if a path was included, verify that the path is correct and try again.

At line:1 char:1

+ getFileSummery 'H:\30032011'

+ ~~~~~~~~~~~~~~

    + CategoryInfo          : ObjectNotFound: (getFileSummery:String) [], Comm 

   andNotFoundException

    + FullyQualifiedErrorId : CommandNotFoundException

NULL


Friday, December 07, 2018 - 2:56:59 PM - Robert C Back To Top

 FWIW, the path for modules is below

 PS C:\WINDOWS\system32> $env:psmodulePath

C:\Users\rob\Documents\WindowsPowerShell\Modules;C:\Program Files\WindowsPowerShell\Modules;C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules;C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules\

PS C:\WINDOWS\system32>


Thursday, December 06, 2018 - 9:36:49 PM - Robert C Back To Top

 I have saved the script as a Powershell module in C:\Windows\System32\WindowsPowerShell\v1.0\Modules\getFileSummary\getFileSummary.psm1

I don't understand why getFileSummary is not recognized.  I'm a complete noob at MS SQL so hopefully it's something completely obvious.  Thanks in advance for any advice.

Here is the script:

function global:getFileSummary

{

param(

    [Parameter(Position=0,mandatory=$true)]

    [string[]] $FileLocation

)

Write-Output "Inserting files"

[email protected]'

INSERT INTO tblFileSummary(

   Fully_Qualified_FileName, File_name, attributes, CreationTime, LastAccessTime, LastWriteTime,

    Length    

VALUES (

   '{0}',

   '{1}',

   '{2}',

   '{3}',

   '{4}',

   '{5}',

    '{6}'

)

'@

Invoke-Sqlcmd -Query "Truncate Table tblFileSummary" -ServerInstance SURFSUP\MSSQLSERVER -database DemoDatabase

Get-ChildItem -Recurse $FileLocation  | 

select File_name,Fully_Qualified_FileName,attributes, CreationTime, LastAccessTime, LastWriteTime,@{Label="Length";Expression={$_.Length / 1MB -as [int] }}|

   ForEach-Object {

      $SQL = $sqlstatement -f $_.FullName, $_.name,$_.attributes, $_.CreationTime, $_.LastAccessTime, $_.LastWriteTime,$_.Length                            

        Invoke-sqlcmd -Query $SQL -ServerInstance SURFSUP\MSSQLSERVER -database DemoDatabase

 

   }

Write-Output "File Inserted successfully... Below Is list of files"

}

and the output:

output

getFileSummary : The term 'getFileSummary' is not recognized as the name of a cmdlet, function, script file, or 

operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try 

again.

At line:1 char:1

+ getFileSummary 'D:\p'

+ ~~~~~~~~~~~~~~

    + CategoryInfo          : ObjectNotFound: (getFileSummary:String) [], CommandNotFoundException

    + FullyQualifiedErrorId : CommandNotFoundException

NULL


Learn more about SQL Server tools