Question

naveej on Mon, 22 Feb 2016 15:18:31


function fn_script_database_objects ( [string]$server ) { # Load the SQL Server Management Objects $assemblylist = "Microsoft.Sqlserver.Smo", "Microsoft.SqlServer.Dmf ", "Microsoft.SqlServer.SqlWmiManagement ", "Microsoft.SqlServer.ConnectionInfo ", "Microsoft.SqlServer.SmoExtended ", "Microsoft.SqlServer.Management.RegisteredServers ", "Microsoft.SqlServer.Management.Sdk.Sfc ", "Microsoft.SqlServer.RegSvrEnum ", "Microsoft.SqlServer.WmiEnum ", "Microsoft.SqlServer.ServiceBrokerEnum ", "Microsoft.SqlServer.ConnectionInfoExtended " [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null $database = "db_name" $srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server $Var = Get-Content D:\Script_Out_Details.txt

#$Var has the drive location where I need to go look for .sql files foreach ($f in Get-ChildItem -path "$Var") { $File = @($var[0]+$f[0])

#$File -- gets each file name one by one $Query = @(Get-Content $File)

#@Query -- reads contents of the file invoke-sqlcmd –ServerInstance $server -Database $database -Query $Query } } fn_script_database_objects -server localhost

I am trying to read a file into a variable and run it in SSMS. I am not sure what's wrong with the above code. Could someone figure it out????



Sponsored



Replies

Olaf Helper on Mon, 22 Feb 2016 15:35:17


Hello,

And you can not automate SSMS using a PowerShell script.

Not clear for me what you are trying to achieve here?  In your script you read the content of some file and execute them against SQL Server using SMO.

naveej on Mon, 22 Feb 2016 15:40:30


I am trying to read .sql files and execute the query in SSMS.

Olaf Helper on Tue, 23 Feb 2016 08:45:34


Then start SSMS, go to menu "File" => "Open", open the SQL file(s) and click on "Execute" in the symbol bar or hit the "F5" key to run the SQL Batch.

Sam Zha on Tue, 23 Feb 2016 11:39:41


Hi naveej,

Based on my test, you could run T-SQL script by the following command, and remember that only one recordset will be displayed.

foreach ($f in Get-ChildItem -path "$Var")
{
	$File = $var+$f
	invoke-sqlcmd -ServerInstance $server -Database $database -InputFile $File
}

naveej on Wed, 24 Feb 2016 16:39:27


@Sam Zha: I ran the above posted script through a sql job but getting an error. Did it work for you?????

naveej on Wed, 24 Feb 2016 20:08:38


function fn_script_database_objects
(
[string]$server
)
{

# Load the SQL Server  Management Objects
$assemblylist = "Microsoft.Sqlserver.Smo",
"Microsoft.SqlServer.Dmf ",
"Microsoft.SqlServer.SqlWmiManagement ",
"Microsoft.SqlServer.ConnectionInfo ",
"Microsoft.SqlServer.SmoExtended ",
"Microsoft.SqlServer.Management.RegisteredServers ",
"Microsoft.SqlServer.Management.Sdk.Sfc ",
"Microsoft.SqlServer.RegSvrEnum ",
"Microsoft.SqlServer.WmiEnum ",
"Microsoft.SqlServer.ServiceBrokerEnum ",
"Microsoft.SqlServer.ConnectionInfoExtended "

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$database = "FDB_BUILD"

$srv 		= New-Object "Microsoft.SqlServer.Management.SMO.Server" $server

$Var = Get-Content D:\UPSDATA\FDB\Script_Out\Script_Out_Details.txt

$db = $srv.Databases[$database]

$connString = "Server=SVRP00027025;Database=FDB_BUILD;Integrated Security=True"

    foreach ($f in Get-ChildItem -path "$Var")

    {

       $File = [String]($var+$f)
       # Write-Host "$File"
 
        invoke-sqlcmd -inputfile $File –ServerInstance $server -Database $database
    }



}


fn_script_database_objects -server localhost

Finally I figured what I was doing wrong. The above script is working me and here it is if it helps anyone.