Sqlcmd powershell command issue

Octopus Server
,
1

Hi,

I tried to execute a SQL file from my db server using powershell sqlcmd using one step in octopus server. I could see this error. Am i missing something here?
whereas this works in windows powershell, but via octopus step using powershell.
Any thoughts would be more helpful.

Command used at one step in octopus deploy via powershell option:
sqlcmd -S $sqlServerName -d $database -b -i “F:\CreateDB\TESTCreateDatabase\CreateDatabase.sql”

also tried this one as well
sqlcmd -s $sqlServerName -i “F:\CreateDB\TESTCreateDatabase\CreateDatabase.sql” -d $database -U “xxxxx” -P “xxxx”

Error Details:
Sqlcmd: Error: Error occurred while opening or operating on file F:\CreateDB\TESTCreateDatabase\CreateDatabase.sql (Reason: The system cannot find the path specified). The remote script failed with exit code 1 on db server xxx

But i could see the sql files available in the same path at my db server xxx.

3

Hi @duraiselvaraaj,

Thanks for getting in touch! I suspect the cause of this error might come down to either permissions, or the user the Tentacle is running as doesn’t have that drive set as the same location. A search online pointed me to the following FAQ which does a good job of explaining the cause of this error and how to resolve it.

https://www.se.com/au/en/faqs/FA233914/

Could you confirm if that’s the cause of this in your case and if the resolution provided above fixes it?

Let me know how you go or if you have any further questions going forward!

Best regards,

Kenny

4

Thanks Kenny for checking on my issue.

I could get it fixed. The issue is, in my step, i did not deployment targets for executing sql script. Instead i selected octopus server as target machine. That is why it is not able to find the path and it is trying find that path in octopus server instead of my db server.

Thanks! Have a great day!!

5

Hi @duraiselvaraaj,

You’re very welcome! That’s great to hear you’ve spotted what caused this, and I appreciate you letting me know. Don’t hesitate to reach out if you have any questions or concerns in the future. :slight_smile:

Best regards,

Kenny