Stack Overflow
Stack Overflow

Questions tagged [invoke-sqlcmd]

PowerShell cmdlet that runs a script containing statements from the languages (Transact-SQL and XQuery) and commands supported by the SQL Server sqlcmd utility.

For MicroSoft's official documentation on this cmdlet see: https://msdn.microsoft.com/en-GB/library/cc281720.aspx

Alternative options to this command exist. For a discussion of these see: https://dba.stackexchange.com/questions/17435/which-powershell-technique-should-i-use-to-talk-to-sql-server

148 questions
0
votes
0 answers

Concatenating "string + variable + string" results in "string + System.Data.DataRow + string"

I'm trying to query a database, get a value, and then use that value with a string with Export-Csv. However, while I can see that my variable is being populated when I'm trying to concat with strings it changes to System.Data.DataRow Example…
pix1985
  • 208
  • 1
  • 8
0
votes
3 answers

How to suppress sql query output and show only the number of rows

I have the following powershell command which displays the number of rows in a sql table. This works but it also includes the header: Invoke-Sqlcmd -ServerInstance "01-SQL1\INSTANCE22" -Database master -Query "select Count(*) from dbo.mytable" The…
yorkman
  • 119
  • 2
  • 16
0
votes
2 answers

Powershell Invoke-Sqlcmd doesn't capture all verbose output

I created following powershell (v2) script to execute query on target database and build another query/queries to be run: $sql = @" DECLARE @Tables CURSOR; DECLARE @TableName NVARCHAR(200); BEGIN SET @Tables = CURSOR FOR SELECT TABLE_NAME …
bontade
  • 3,194
  • 6
  • 48
  • 75
0
votes
1 answer

Unable to insert PowerShell variables from array into Invoke-Sqlcmd call

I have an array that loops through a string assigning values to elements. If I then assign those elements to a variable and pass that variable into the invoke-sqlcmd I get an error. However, if I assign the value directly to the variable and pass it…
hoakey
  • 998
  • 3
  • 18
  • 34
0
votes
1 answer

Dropping Database in Powershell with Invoke-Sqlcmd tells me DB is in use even though it's not

I am trying to drop a database like this Invoke-Sqlcmd -Database Dbname-ServerInstance ".\SQLEXPRESS" -Query "drop database DbName;" in a powershell script and I get this message: Invoke-Sqlcmd : Cannot drop database "DbName" because it is…
hot33331
  • 805
  • 11
  • 25
0
votes
1 answer

Running sqlcmd or invoke-sqlcmd in powershell script in c#

Been using: private string RunScript(string storeNumber) { //string importSQL = "Import-Module sqlserver -DisableNameChecking"; //string executionPolicy = "Set-ExecutionPolicy -ExecutionPolicy Unrestricted"; …
James
  • 13
  • 1
  • 5
0
votes
0 answers

Assigning vars using sqlcmd

I'm trying to make some changes to backup jobs on a server, I am wondering it's possible to create a variable and then subsequently call that in the script. sqlcmd -E -d master -Q "DECLARE @CurrentDate VARCHAR(10); SET @CurrentDate =…
a_m_2016
  • 215
  • 1
  • 3
  • 9
0
votes
2 answers

Missing values passing array variables from csv file to invoke-sqlcmd insert statement?

I'm just learning PowerShell, I've built a script trying to import a .CSV file into my SQL Server database, and found that the following query was just appending the table. $database = 'DATABASE' $server = 'SERVER' $table = 'TABLE' Import-CSV…
Lee
  • 95
  • 1
  • 13
0
votes
0 answers

Compile sqlcmd SQL template with variables

I have a SQL file with variable in it. I want to get the SQL file where variable replaced by value I provide to sqlcmd. Can this be done? I have: USE AdventureWorks2012; SELECT x.$(ColumnName) FROM Person.Person x WHERE x.BusinessEntityID < 5; I…
skfd
  • 2,528
  • 1
  • 19
  • 29
0
votes
0 answers

If statement not evaluating correctly - invoke-sqlcmd

So i have the following loop: foreach ( $item in $mergeJobs.JobID ) { $mainExists = Invoke-SQLCmd -Query "SELECT COUNT(*) FROM JobCandidate WHERE JobID = $item AND CandidateID = $($mainCandidate.itemArray[0])" -ServerInstance $ServerAddress…
Owain Esau
  • 1,876
  • 2
  • 21
  • 34
0
votes
1 answer

"Invoke-Sqlcmd | Out-File" not in matrix format?

I am using powershell to query a SQL table and then save the results in a file, this is working fine, however, the results are not stored in a matrix format but rather chunks. How can I save the output like normal rows with separators…
David Garcia
  • 3,056
  • 18
  • 55
  • 90
0
votes
1 answer

Can't remotely login to a SQL Server database using sqlcmd

I am trying to connect to a remote database using sqlcmd. I have tried something like this sqlcmd -U \Administrator -P adminpassword -S *.*.*.* -d mydatabase But this is causing an error: Failed to Login failed for user…
MrClamps
  • 31
  • 9
0
votes
1 answer

Store Invoke-Sqlcmd Query as String

I have a value that I want to find and pull out of a database. I have everything I need in order to run this script except how to store the result in a variable and then write those results to a txt file $CNumber = Invoke-Sqlcmd -query ("Select Cust…
Ochuse
  • 219
  • 1
  • 5
  • 12
0
votes
0 answers

Powershell invoke_sqlcmd is not closing connection

I am using Powershell to a script to dynamically restore a database (sql server 2012). set-location "SQLSERVER:\sql\$host_name\$inst\databases\.$db_target" $db = Invoke-Sqlcmd -Query "select type_desc,name,physical_name from sys.master_files where…
Judi
  • 710
  • 3
  • 10
  • 25
0
votes
0 answers

Powershell, Using the results of a Invoke-SQLcmd as a variable in a file path

I am trying to use Powershell to run a stored procedure in a database and set those results to a variable. That part of my script works just fine, however when i try to use said variable in a file path I receive the below error: "Cannot find path…
paradox430
  • 11
1 2 3
…
9
10