Menu
  • HOME
  • TAGS

SQL Server connection string with “$” in password

Tag: powershell,sql-server-2012,connection-string

I have a connection string to a SQL Server 2012 DB:

$CONN_STR = "Server=SERVER;Database=mydb;User ID=myuser;Password=abc1$4def;"

I'm creating a connection in PowerShell, but the login fails due to invalid password. I know that the password is correct and validated that in SSMS. I've tested with another account and from what I can tell, the special character in the password is causing the failure.

How can I escape this in the connection string that I use in PowerShell?

Best How To :

try this:

$CONN_STR = 'Server=SERVER;Database=mydb;User ID=myuser;Password=abc1$4def;'

Use single quotes instead that way it wont think you are trying to reference a variable

How to use OFFSET and Fetch without Order by in SQL Server

sql-server,sql-server-2012,sql-order-by,fetch,offset

By adding an identity column to the temp table variable declare @TempTable table([some columns], rownr int identity(1,1) ) INSERT INTO @TempTable [some columns] select [some columns] from table1 order by col1 INSERT INTO @TempTable [same columns] select [some columns] from table2 order by col2 An automatic incrementing number is added...

Why does piping Get-PSSession to Exit-PSSession not work?

powershell

You've not looked closely enough. Don't forget, it's easy to get all cmdlets related to a certain subject by doing something like this: Get-Help PSSession This gets a list of all cmdlets with "PSSession" it its name. If you carefully review the output, there's Exit-PSSession and Disconnect-PSSession, but there's one...

How do I write a loop to read text file and insert it to the database

sql-server,loops,powershell

To add a simple loop, you can use your existing AutoImportFlatFiles function like this: $Folder= $(read-host "Folder Location ('C:\Test\' okay)") foreach ($file in (get-childitem $Folder)) { $location = split-path $file.FullName -Parent $filename = (split-path $file.FullName -Leaf).split(".")[0] $extension = (split-path $file.FullName -Leaf).split(".")[1] AutoImportFlatFiles -location $location -file $filename -extension $extension -server "WIN123"...

PowerShell - Convert CSV to XLSX

powershell

What is the whole "gps" part of the script for? The two (gps excel -ErrorAction SilentlyContinue).count lines at the start and end of the script count the number of Excel executables running. gps is the shorthand alias for get-process. You can find out more by doing help gps which...

Join SQL query Results and Get-ChildItem Results

sql-server,sql-server-2008,powershell

OK so if the SQL query does not have results then NULL is returned and, in essence, nothing is added to the $dbResults array. Instead lets append the results to a custom object. I don't know what PowerShell version you have so I needed to do something that I know...

Verify data integrity for varbinary column

sql,sql-server,image,sql-server-2012

This worked for me: How to export image field to file? The short version without the cursor looks like this: DECLARE @ImageData VARBINARY(max) DECLARE @FullPathToOutputFile NVARCHAR(2048); SELECT @ImageData = pic FROM Employees WHERE id=5 SET @FullPathToOutputFile = 'C:\51.jpg' DECLARE @ObjectToken INT EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT; EXEC sp_OASetProperty @ObjectToken, 'Type',...

Define an array with prefixes using the range operator

arrays,powershell

Would that do? @(4..9) | % {"usr" + $_} ...

PowerShell Where-Object $_.name -like -in $list

powershell

The Where-Object FilterScript block is just a scriptblock that returns $true, $false or nothing - you can do all kinds of crazy things inside it, including looping over an array to see if there is a wildcard match in one of the entries: Where-Object { $ProductName = $_.Name $_.pscomputername -like...

Extract e-mail from grouped objects

powershell

Your second bit of code hurts my brain, but I think what you want is to output where multiple accounts use the same email address, grouped by email address. So, let's start with getting duplicates. Your first bit of code is kind of functional, but it really collects way more...

Photos as Varbinary(MAX) in SQL Server 2012 causes error 502753 in Access 2010

sql,ms-access,sql-server-2012,ms-access-2010,varbinarymax

It looks like you bulk-inserted the raw binary image data directly from the BMP file into the VARBINARY column using T-SQL. Therefore, those images don't have the "OLE wrapper" that is added when an image is inserted via a Bound Object Frame on an Access form. See the my answer...

Get IP address of the Network Adapter of a computer having No gateway

powershell,ip-address,gateway

$configs=gwmi win32_networkadapterconfiguration | where {$_.ipaddress -ne $null -and $_.defaultipgateway -eq $null} if ($configs -ne $null) { $yourtargetIP= $configs[0].IPAddress[0] } # $yourtargetIP will have the IP address to make the gateway from In fact, should you have more than one IPv4 address on your network card, $configs[0].IPAddress will have them all,...

Using --check on a md5sum command generated checksum file is failing

powershell,cygwin,md5sum

Using the redirection operator to write the checksums to an output file causes the file to be created with the default encoding (Unicode). md5sum expects an ASCII file. Use Set-Content (or Out-File) to save the file with ASCII encoding: md5sum jira_defect.txt | Set-Content result.md5 -Encoding ASCII You can also work...

How to create a powershell script that triggers a NuGet Update-Package –reinstall?

powershell,nuget-package

You should run the update command from nuget.exe. One of the parameters of the update command is FileConflictAction, which tells what action to take when asked to overwrite or ignore existing files referenced by the project: overwrite, Ignore, None. You might have to wrap everything in a powershell script, possibly...

How to pass a switch variable?

powershell,parameter-passing

You can use splatting: $xtraOptions = @{} if ($NoPromptForPushPackageToNuGetGallery) { $xtraOptions.Add("NPFPPTNG",$true) } & "$THIS_SCRIPTS_DIRECTORY_PATH\New-NuGetPackage.ps1" -PushOptions "$pushOptions" -Verbose -ProjectFilePath $project -PO "$packOptions" @xtraOptions If $xtraOptions is just an empty hashtable, @xtraOptions will simply have no effect on the parameters passed. You could also push all the parameters into the splatting table...

Loop Issue - Remote Server

powershell

You never output $DRIVE anywhere, and the expression for $DRIVE shouldn't be in a scriptblock in the first place. The computer name is repeated several times, because you get the SystemName property for each logical disk object. Also, $OS gets the OS name for the local computer, not the remote...

Format a command in powershell including a comma, can't find the right way to escape

powershell,batch-file,escaping,powershell-v2.0,comma

".\pacli DELETEUSER DESTUSER='"[email protected]`,com"' sessionid=333" You have double quotes in single quotes in double quotes, so the inner double quotes will terminate the string, so this will be parsed as three values: ".\pacli DELETEUSER DESTUSER='" [email protected]`,com "' sessionid=333" The answer is to escape, with a back tick (`), the inner...

Programmatically accessing TFS history [closed]

c#,.net,powershell,tfs

Shai Raiten's Blog is great for learning the TFS API. For getting file history - read this post: http://blogs.microsoft.co.il/shair/2014/09/10/tfs-api-part-55-source-control-get-history/...

Powershell Reading text file

powershell,text,text-files

To read the text after the # characters you must read the file content up to the # characters first. Also, in PowerShell you normally read files either line by line (via Get-Content) or completely (via Get-Content -Raw). You can discard thos parts of the read content that don't interest...

Logging actual error when script fails

powershell,automation,error-logging

Change this: catch { $status = "FAILED" Write-Verbose "`tFailed to Change the administrator password. Error: $_" } to this: catch { $status = "FAILED" Write-Verbose "`tFailed to Change the administrator password. Error: $_" $errmsg = $_.Exception.Message } to preserve the error message(s). And change this: if($Status -eq "FAILED" -or $Isonline...

Fastest way to add a grouping column which divides the result per 4 rows

sql,sql-server,tsql,sql-server-2012

Try this: SELECT col, (ROW_NUMBER() OVER (ORDER BY col) - 1) / 4 + 1 AS grp FROM mytable grp is equal to 1 for the first four rows, equal to 2 for the next four, equal to 3 for the next four, etc. Demo here Alternatively, the following can...

Group Count in T/SQL

sql,sql-server,sql-server-2012

You can try qith cross apply: SELECT ..., ca.NoUniqueGRPed FROM #TempTab t1 CROSS APPLY(SELECT COUNT(DISTINCT GRP) AS NoUniqueGRPed FROM #TempTab t2 WHERE t1.Value = t2.Value)ca ...

Filter and delete Registry values with Where-Object

powershell,registry

I think this issue is a matter of stepping back and taking a look at the bigger picture. You're focused on the value or a property, and how to get that property name that you aren't taking into consideration that the property is just a part of a larger object,...

Issue filtering out certain event logs from output

html,powershell

I think I see a couple of potential issues. If we have a look at a few event from my local computer. EventID InstanceId Message ------- ---------- ------- 1202 2147484850 Security policies were propagated with warning.... 0 0 The description for Event ID '0' in Source 'gupdate' cannot be found....

Powershell comparison of text file

powershell,readfile

Something to get you started: # $file1 will be an array with each element containing the line contents $file1 = get-content .\text1.txt # $file2 will be an array with each element containing the line contents just like $file1 $file2 = get-content .\text2.txt # This splits each line of $file2 on...

SQL Query using FOR XML PATH that works right

sql,tsql,sql-server-2012

Similar to @MartianCodeHound I'd use STUFF SELECT t1.eventId, STUFF((SELECT ',' + t3.stringValue FROM TABLE2 t2 JOIN TABLE3 t3 ON t2.valueId = t3.valueId WHERE t2.eventId = t1.eventId ORDER BY t3.stringValue FOR XML PATH('')), 1, 1, '') AS stringValue FROM TABLE1 t1 Here's a test for you DECLARE @TABLE1 TABLE (eventId INT)...

How to retrieve the name and path of VM's through powercli

powershell

If the text infront and including "Resources" is redundant then using a simple regex we can replacing it before it is output from your function. From $path to $path -replace "^.*?Resources/" So that would replace the similar line inside your function ( Where you return the property). We take everything...

SQL Server 2012 & Polybase - 'Hadoop Connectivity' configuration option missing

sql-server,hadoop,sql-server-2012

Are you sure Polybase is installed and enabled? You should have installed it during the SQL Server installation process and enable the according services....

Turn environment variable into an array

powershell

Split the value of the environment variable at whatever delimiter is used. Example: PS C:\> $env:Path C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\System32\WindowsPowerShell\v1.0\ PS C:\> $a = $env:Path -split ';' PS C:\> $a C:\WINDOWS\system32 C:\WINDOWS C:\WINDOWS\System32\Wbem C:\WINDOWS\System32\WindowsPowerShell\v1.0\ PS C:\> $a.GetType().FullName System.String[] Edit: The PowerShell equivalent to bash code like this for a in ${MYARR[@]} ; do...

PowerShell XML formatting issue

xml,powershell

You're missing a set of parentheses (()) at the end of $XmlWriter.WriteEndElement: $xmlWriter.WriteStartElement("Disk$count") # Add tag for each drive $xmlWriter.WriteElementString("DriveLetter","$DriveLetter") # Write Drive Letter to XML $xmlWriter.WriteElementString("DriveSize","$DriveSize") # Write Drive Size to XML $xmlWriter.WriteElementString("DriveFreeSpace","$DriveFreeSpace") # Write Drive Free Space to XML $xmlWriter.WriteEndElement() # <-- Closing Drive Tag - don't forget...

How do I select a string from a string and replace it in powershell?

powershell

Not the best regex but this would be a good start. You aren't specific about what the line looks like so I will assume that it is on its own line with variable whitespace and or text. Get-ChildItem C:\temp\*.asp | ForEach-Object{ $file = $_.FullName (Get-Content $file) -replace '(.*UserRights\s*)"(.*?)"(.*)','$1("$2")$3' | Set-Content...

Why doesn't “go get gopkg.in/…” work while “go get github.com/…” OK?

windows,git,powershell,github,go

The root cause has been found: Because my computer use a web proxy, so I need to set proxy in environment variable: C:\Users\xiaona>set https_proxy=https://web-proxy.corp.hp.com:8080/ C:\Users\xiaona>set http_proxy=https://web-proxy.corp.hp.com:8080/ C:\Users\xiaona>go get -v gopkg.in/fatih/pool.v2 Fetching https://gopkg.in/fatih/pool.v2?go-get=1 Parsing meta tags from https://gopkg.in/fatih/pool.v2?go-get=1 (status code 200) get "gopkg.in/fatih/pool.v2": found meta tag main.metaImport{Prefix:"gopkg.in/fa tih/pool.v2", VCS:"git",...

Remove all folders .old

powershell

Get-ChildItem produces a list of objects. Use a pipeline for processing that list: Get-ChildItem '\\kiewitplaza\vdi\Appsense_profiles' | Where-Object { $_.Name -like '*.old' } | Remove-Item ...

PS pipe WorkingSet as variable

variables,powershell

Use Select-Object -ExpandProperty to grab just a single property from the process: $WorkingSet = Get-Process spiceworks |Select-Object -First 1 -ExpandProperty WorkingSet if($WorkingSet -gt 120MB) { # Send email } ...

Get list of files whose creation date is greater than some date time

powershell,windows-server-2012

Get-ChildItem "C:\Users\gerhardl\Documents\My Received Files" | Where-Object { $_.CreationTime -gt [datetime]"2014/05/28" } | Sort-Object CreationTime | Format-Table Name, CreationTime String is cast to datetime if you specify [datetime] before it. You can read about comparison operators by typing help about_Comparison_Operators in PowerShell console....

SQL varchar variable inserts question mark

sql,sql-server-2012

there is un recognizable character in your string that is giving that ?. Delete the value and retype. see my above screen shot...

Search for certain UPN suffix

powershell,active-directory

You use Get-ADUser and filter on user principal names that end with @sec213.com: $domain = ([adsi]'').distinguishedName $ou = "OU=users,OU=SEC213,OU=Uofguelph,$domain" $suffix = '@sec213.com' Get-ADUser -Filter "userPrincipalName -like '*$suffix'" -SearchBase $ou ...

Finding records in main table that match records in another table in SQL Server

sql,sql-server,sql-server-2012

Ow, my brain hurts... Replace all of the question marks (3 of them) with the trip ID of the trip where you want to check for similar trips. select distinct s.tripIDFK as tripId from Legs l left join Slices s on l.sliceIDFK = s.sliceId where s.tripIDFK != ? and not...

Post messages from async threads to main thread in F#

.net,powershell,f#,system.reactive,f#-async

I ended up creating an EventSink that has a queue of callbacks that are executed on the main PowerShell thread via Drain(). I put the main computation on another thread. The pull request has the full code and more details. ...

Color a cell on the basis of another cell value

html,powershell,powershell-v2.0

You should format your HTML result with a different style if there's a condition. To do that, you declare a variable for data style that should be equal to $normalDataStyle if your condition is false, and a special style if it's true. $redDataStyle='style = "border: 1px solid black; background: #c00000;...

Send email with body consisting of objects

email,powershell,foreach

Also if you want it to look more nice and readable you can do something like this that will spit it out in a table: $body += "<body><table width=""560"" border=""1""><tr>" $bodyArray[0] | ForEach-Object { foreach ($property in $_.PSObject.Properties){$body += "<td>$($property.name)</td>"} } $body += "</tr><tr>" $bodyArray | ForEach-Object { foreach ($property...

Powershell workflow - Get-Service not filtering

powershell,workflow

I have no idea why -Name [wildcard] works and -DisplayName [wildcard] doesn't (inside a workflow), but you can use Where-Object to accomplish the filtering if you like: workflow Restart-Services{ $services = Get-Service |Where-Object -FilterScript {$_.DisplayName -like "S*"} Foreach -Parallel ($svc in $services){ $name = $svc.Name Restart-Service -Name $name } }...

Not getting the correct SUM values in SQL Server 2012 when using a PIVOT

sql-server,sql-server-2012,pivot,distinct,aggregate-functions

The following query should give you what you want: SELECT Store, TotalSold, [John] AS WastedByJohn, [Jim] AS WastedByJim, [Alex] AS WastedByAlex FROM (SELECT Store, Employee, Waste, SUM(Sold) OVER (PARTITION BY Store) AS TotalSold FROM #Foo) src PIVOT (SUM(Waste) FOR Employee IN ([John], [Jim], [Alex]) ) PVT To understand why you...

CPU usage missing from log for some processes

powershell

You can't get CPU for some processes because of insufficient rights. You get null value then. To output "Nothing" you have to compare the cpu value with $null, something like this: [email protected]{Expression={$_.ProcessName};Label="ProcessName";Width=40},@{Expression={$cpu=$_.CPU;if($cpu -eq $null){"Nothing";} else {$cpu;}};Label="CPU";Width=20} $ServiceTable = @{Expression={$_.Name};Label="Name";Width=40},@{Expression={$_.Status};Label="Status";Width=10} Get-Process | Sort-Object CPU -Descending | Select-Object ProcessName, CPU | format-table...

Increment Serial Number using EXIF

windows,powershell,command-line,exif,exiftool

You'll probably have to go to the command line rather than rely upon drag and drop as this command relies upon ExifTool's advance formatting. Exiftool "-SerialNumber<001-001-0001-${filesequence;$_=sprintf('%04d', $_+1 )}" <FILE/DIR> If you want to be more general purpose and to use the original serial number in the file, you could use...

Enhancing the pipeline's content?

powershell,powershell-v3.0

Updating archive .\files.zip ... Compressing files.zip ? Check if your function has a case when adding Archive.zip to Archive.zip, this should throw a warning like copying over itself. About pipeline - I think you should employ -passthru switch, if the switch is present, return the archive as a Get-Item result...

Retrieve the Return Result with Chronological Order Based on Parameter

tsql,stored-procedures,sql-server-2012

Without an explicit ORDER BY Statement, SQL Server will determine the order using a variety of means e.g. collation\indexes\order of insert etc. This is arbitrary and will change over time! No Seatbelt - Expecting Order without ORDER BY If you want to guarantee the order of your output, you need...

SQL Transaction Stuck open

sql,sql-server-2012

You can just execute ROLLBACK TRAN from the same query window. The error didn't abort the transaction because you started the transaction in a separate batch before the GO. If you don't have the same connection available you have to find and kill the transaction. See close/kill transaction....

One Table - Two Fields - Same value - T/SQL

sql-server,tsql,sql-server-2012

The problem is that you're comparing both of the lines to themselves. I assume you would like to find just the first row, but also the second line (t1) will match to the first line (t2) and that's the second result you get. Not sure what you're doing, but you'll...

What is the `.` shorthand for in a PowerShell pipeline?

powershell

. is the dot sourcing operator, which runs a script in the current scope rather than a new scope like call operator (i.e. &). That second segment invokes a script block and in that script block defines an advanced function. The advanced function iterates each item in the pipeline and...

PowerShell logic to remove objects from Array

arrays,powershell

How about not performing a remove, but just sort on tastecode descending and taking just one first result? $DuplicateMembers = $Fruits | Group-Object Name $DuplicateMembers | ForEach-Object { $Outcome = $_.Group | Sort-Object TasteCode -descending | Select -First 1 $Outcome } This way you should not bother to remove anything...