-2

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"
Cleadus Fetus
  • 113
  • 8
  • Please edit the question and add how increment is done. Could be a integer/string converison issue, but that's just a guess. – vonPryz Aug 17 '18 at 13:00
  • Presumably, it is being stored in the DB as an integer, where it is standard practice to ignore leading zeros (because there'd be a lot of them). If you want it to have the leading zeros for display later, you can format it yourself when you read it back. For example: `"{0:0000}" -f 1`, which gives output: `0001` – boxdog Aug 17 '18 at 13:29
  • 1
    `Invoke-SqlCmd` is entirely innocent. If `$newFileNum` contains a correct string (`0001`), you need to pass it as a string (`sequence_no = '$newFileNum'`). Otherwise, you'll send `sequence_no = 0001` to SQL Server, where SQL Server will just parse `0001` back into an `INT`. Or you can avoid both `Invoke-Sqlcmd` and string replacement altogether and use [proper parameters](https://stackoverflow.com/a/50582924/41379160). – Jeroen Mostert Aug 17 '18 at 13:32

1 Answers1

0

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.

Cleadus Fetus
  • 113
  • 8