Question:
I wanted to run a sql script using powershell but getting an error “The term ‘Invoke-sqlcmd’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check t
he spelling of the name, or if a path was included, verify that the path is correct and try again.”
I have found the below snippet from some website.but this is only for one sql command..But i wanted to run a sql script.
Could someone please help in modifying the below for sql script or any better suggestion ?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQLServer = "ABCD\ABC" $SQLDBName = "ABC_1223" $SqlQuery = "select * from table" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server = $SQLServer; Database =$SQLDBName;uid=$SQLDBName;pwd= $pwd; Integrated Security = True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $SqlQuery $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) $SqlConnection.Close() clear $DataSet.Tables[0] |
Answer:
You can use your code as it is, just put your SQL script in a Here-String (string literal). In addition I would wrap the call to the SQL database in a Try/Catch/Finally construct to ensure your connections are closed and disposed. Finally, consider using Application Name in your connection string to give your DBA’s a clue as to what your connection is related to. Putting that together:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
Try{ $SQLServer = "ABCD\ABC" $SQLDBName = "ABC_1223" #This is the here-string $SqlQuery = @" select * from table where we can select stuff and filter it and join etc "@ $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server = $SQLServer; Database =$SQLDBName;Application Name = 'user2075017_db_call';uid=$SQLDBName;pwd= $pwd; Integrated Security = True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $SqlQuery $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) $DataSet.Tables[0] } Catch{ } Finally{ $SqlConnection.Close() $SqlConnection.Dispose() } |