Search code examples
powershellleading-zeroinvoke-sqlcmd

How To Prevent Invoke-SQLCMD from trimming Leading Zero's

In powershell I have a script and every time it runs it needs to increment a number in a DB by one. Once it reaches 9999 it resets back to 0001. I have this functionality worked out in powershell my issue is that Invoke-SQLCMD keeps stripping out any leading 0's

so If I want to update the DB value to 0001 it only updates it to 1

Is there any way to have SQLCMD keep leading 0's?

Invoke-Sqlcmd -ServerInstance $dataSource -Database $database -Username $user -password $pass -Query "UPDATE DBO.TABLE_NAME SET sequence_no = $newFileNum"
Solution

  • invoke SQLCMD does this by default. One way is when you run your query against SQL to get the value in there you can use .ToString("0000") to put the 0's back in or your can use this as your SQL query.