Access database (.accdb), open/read/write - see MS Access database (.accdb), open/read/write
connect to SQL Server database twice
Useful if we want a progress bar. You can't just read in the records and get a count of how many records you have like you can when reading in from a CSV file. Instead, you must first execute a command to get the count, kill the connection, and then re-establish connection with the actual select statement.
# conntect to DB
$ConnectionString
=
"Server=Skynet;Database=SysAdmin;Integrated
Security=True;MultipleActiveResultSets=True"
$SqlConnection
=
New-Object
System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString
=
$ConnectionString
$SqlConnection.Open()
##################################################################
# open
database reader for the 1st time to get count of records
##################################################################
#
break SQL into two parts so we can use the 2nd "from where" part again
# sometimes I forget to change the "from where" in both places
otherwise
$SQLSelectCount
=
"select count(*)"
$SQLFromWhere
=
"from servers
where (Role like '%sql%' or ServerName like '%sql%')"
$SQLReadcount
=
"$SQLSelectCount
$SQLFromWhere"
$reader
=
New-Object
System.Data.SqlClient.sqlCommand
$reader.connection
=
$SqlConnection
$reader.CommandText
=
$SQLReadcount
$dr
=
$reader.ExecuteReader()
$dr.Read()
$countCharacter
=
$dr.GetValue(0)
[int]$count
=
[convert]::ToInt32($countCharacter,
10)
Write-Host
"$count records to go through"
-ForegroundColor DarkCyan
$dr.close
|
Out-Null
$reader.close
|
Out-Null
##################################################################
# open
database reader again to read actual records
##################################################################
$reader
=
New-ObjectSystem.Data.SqlClient.sqlCommand
$reader.connection
=
$SqlConnection
$SQLSelectCount
=
"select
ServerName, ServerID, SQLEdition, SQLVersion"
$SQLFromWhere
=
"from servers
where (Role like '%sql%' or ServerName like '%sql%')"
$SQLReadRecords
=
"$SQLSelectCount
$SQLFromWhere"
$reader.CommandText
=
$SQLReadRecords
$dr
=
$reader.ExecuteReader()
##################################################################
# open
2nd database connection to update records - not used here
##################################################################
$cmd
=
New-Object
System.Data.SqlClient.sqlCommand
$cmd.connection
=
$SqlConnection
$i=0
[int]$count
=
[convert]::ToInt32($countCharacter,
10)
while
($dr.Read()
-and
$i
-le
5)
{
$i++;
$percentTxt
=
($i/$count).ToString("P")
$serverName
=
$dr["ServerName"]
$ServerID
=
$dr["ServerID"]
$SQLEditionStored
=
$dr["SQLEdition"]
# get existing
value for this & next variable
$SQLVersionStored
=
$dr["SQLVersion"]
Write-Host
"$i
of
$($count)
($percentTxt): server=$($serverName), ID=$($ServerID): SQLEditionStored
= $SQLEditionStored, SQLVersionStored
= $SQLVersionStored"
-ForegroundColor Green
}
$dr.close
|
Out-Null
$reader.close
|
Out-Null
$SqlConnection.Dispose()
I suppose we could just create two separate readers and not mess with all this. But I think this is cleaner and more clear that we're getting the count ahead of time and we'll never need that data reader again in the program once we capture that little bit of info into a variable ahead of time. We shouldn't need a whole new reader just to get the count of records satisfying the same “from” and “where”; it's really essentially the same query. Also, I often end up changing the “where” clause and this way I only have to remember to change it once.
how many records before reading records returned, know - see connect to SQL Server database twice
insert values into SQL Server database
Start by opening a database connection
$server
=
"yourServer\mssql2019"
$database
=
"yourDatabase"
$table
=
"yourTable"
$ConnectionString
=
"Server=$server;Database=$database;Integrated Security=True;MultipleActiveResultSets=True"
try
{
$SqlConnection
=
New-Object
System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString
=
$ConnectionString
$cmd
=
New-Object
System.Data.SqlClient.sqlCommand
$cmd.connection
=
$SqlConnection
$cmd.Connection.Open()
}
catch
[System.Exception]
{
Write-Host
$_.Exception.Message -ForegroundColor Yellow
}
Normally the following might now be done inside a loop (not shown here)
If we don't clear the parameters now using .Parameters.Clear()
immediately below
from when we set them during an earlier iteration of the loop further below,
the system will complain because it thinks we're trying to add the same field again from that earlier iteration
(after the 1st iteration, anyway.) Trying to clear using $null
won't work
$cmd.Parameters.Clear()
Now start to compose the insert command. We're inserting 4 fields.
$cmd.CommandText
=
"insert into
$table
(fileName, location,system,URL) values (@fileName,@location,@system,@URL)"
The "old" way we might have done this was just to put the values we wanted inserted
directly into "values" part of the statement above. But using the "@" above and then filling in those
parameters below helps us avoid problems with embedded single quotes
and are more secure with regard to SQL injection attackes
The | Out-Null
statements at the end of each .Parameters.Add()
commands below prevents a
whole bunch of annoying verbiage from going to your console
$cmd.Parameters.Add("@fileName",$attachment) |
Out-Null
$cmd.Parameters.Add("@location",$location) |
Out-Null
$cmd.Parameters.Add("@system",$system) |
Out-Null
$cmd.Parameters.Add("@URL",$link) |
Out-Null
Now that we've properly cued up our insert statement, try to run it:
try
{
$cmd.ExecuteNonQuery() |
Out-Null
}
catch
[System.Data.SqlClient.SqlException]
{
Write-Host
"Problem: $($_.Exception.Message)"
-ForegroundColor Red
}
catch [System.Exception]
{
# A generic error occurred somewhere in the try area.
Write-Host
"Some other error occurred: $($_.Exception.Message)" -ForegroundColor Red
}
Once we're done with our loop, close the database connection
$cmd.Connection.Close()
Install-Module sqlserver
This requires you to know the instance name to begin with. Usually, it's just the computer name. It returns some basic info like version.
Get-SqlInstance -ServerInstance "MagicKindom"
Invoke-Sqlcmd not recognized - see Install-Module sqlserver
log into SQL Server database, can't using non-integrated security - make sure your connection string includes, ";Trusted_Connection = yes"
MS Access database (.accdb), open/read/write
$mytemp
= [environment]::getfolderpath("mydocuments")
$filename
=
"$mytemp\companies.accdb"
$conn = New-Object System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.16.0;Data
Source=$filename;Persist Security
Info=False")
$conn.Open()
# read
$cmd=$conn.CreateCommand()
$cmd.CommandText="Select company.* from company;"
"connection is: $($conn.State)"
$rdr
=
$cmd.ExecuteReader()
$dt
=
New-Object
System.Data.Datatable
$dt.Load($rdr)
$dt
# write
$cmd2
=
$conn.CreateCommand()
$cmd2.CommandText
="INSERT into Company (CompanyName)
values('Jumbotron')"
$cmd2.ExecuteNonQuery()
$conn.Close()
"connection is: $($conn.State)"
ODBC entry, create
Add-OdbcDsn -Name "SysAdmin" -DriverName "SQL Server" -DsnType "System" -SetPropertyValue @("Server=DEV1\MSSQL2012", "Trusted_Connection=Yes", "Database=SysAdmin")
or first make sure the driver has been installed
If
($OdbcDriver.Name
-eq
"ODBC Driver
17 for SQL Server")
# Only continue if this specific SQL ODBC driver is installed
{Add-OdbcDsn
-Name
"NetSuiteODBC17d"
-DriverNameriver 17 for SQL Server"
-Platform
64-bit -DsnType System -SetPropertyValue
@("Server=flamboozl.database.windows.net",
"Authentication=ActiveDirectoryPassword",
"Database=flambuuzl",
"Description=flambuuzl using ODBC version 17",
"Encrypt=Yes",
"TrustServerCertificate=No")}
password for SQL Server database doesn't work using non-integrated security - make sure your connection string includes, ";Trusted_Connection = yes"
progress bar - see connect to SQL Server database twice
read from Azure DB
$database
=
"SourceOfAll"
$ServerInstance
=
"sourceOfAll.database.windows.net"
$Username
=
"readerGuy"
$password
=
"yuulNeverGess!"
$query
=
"select FirstName,
LastName, DisplayName, UserPrincipalName
from
users
where domain = jupiter.com'"
$results
=
Invoke-Sqlcmd
-ServerInstance
$ServerInstance
-Database
$database
-Username
$Username
-Password
$Password
-Query
$query
foreach
($result
in
$results) {
"$($result.DisplayName)"
}
almost the same for insert, update; just different SQL
record count before reading records returned - see connect to SQL Server database twice
SQL command, execute from PowerShell
Invoke-Sqlcmd -ServerInstance "One-Ring" -Database SysAdmin - HostName"One-Ring" -ApplicationName "ReadWrite" -ApplicationIntent ReadWrite -Query "select HOST_NAME() AS HostName, APP_NAME() AS ApplicationIntent, @@SERVERNAME AS ServerName"
SQL command, execute from command line
sqlcmd -S One-Ring -U sauron
The command line will ask you the password. You can optionally specify the password (not recommended, but sometimes is the only way to work):
sqlcmd -S One-Ring -U sauron -P Mypwd$%KillElves
This is great if you already have database credentials. But what if you want to run using Windows Authentication? supposedly, you can invoke a remote session and run the SQL command all in one command:
runas /user: mordor\sauron sqlcmd.exe -S One-Ring
But I've never got it to run all as one command. Instead, I first run as:
runas.exe /user:mordor\sauron cmd.exe
Now at local command prompt, connect to remote server (“One-Ring” in this case):
C:\WINDOWS\system32>sqlcmd -S One-Ring
Which opens up a separate DOS-like command prompt where I can begin issuing commands:
1>
PRINT SUSER_NAME();
2>
GO
returns:
mordor\sauron
I can change database from default master:
1>
use SysAdmin
2> go
returns:
Changed database context to 'SysAdmin'.
now I can finally run something useful:
1> select count(*) as howMany from servers where (Role like '%orc%' or ServerName like '%orc%')
2> go
returns:
howMany
-----------
55
when done, exit:
1> exit
SQL Server database, can't connect to using non-integrated security - make sure your connection string includes, ";Trusted_Connection = yes"
SQL, execute - see also here and here for arrays
$SqlConnection
=
New-Object
System.Data.SqlClient.SqlConnection
$ConnectionString
=
"Server=someserver;Database=somedb;User
Id=someuser;Password=yourpwd"
$SqlConnection.ConnectionString
=
$ConnectionString
try
{
$sqlConnection.Open()
}
catch
[System.Data.SqlClient.SqlException]
{
Write-Host
"Problem connecting to database"
write-host
$_.Exception.ToString()
#write-host $_.Exception.state
exit
}
$sqlCommand
=
$sqlConnection.CreateCommand()
$SQLStr
=
"select * from table"
$sqlCommand.CommandText
=
$SQLStr
$dr
=
$sqlCommand.ExecuteReader()
$SqlConnection.Close()
$DataSet.Tables[0]
if you then want to loop through the recordset returned:
while
($dr.Read())
{
$firstFieldValue
=
$dr.GetValue(0)
$secondFieldValue
=
$dr.GetValue(1)
}
This is all well and good for select statements. But for update and inserts, need something else:
$updateSQL = "update table set somefield = 3"
$sqlCommand.CommandText
=
$updateSQL
$sqlCommand.ExecuteNonQuery()
Startup Programs, Generate a List
Get-CimInstance Win32_StartupCommand | Select-Object Name, command, Location, User | ogv
total record count before reading records returned - see connect to SQL Server database twice