Azure PowerShell Invoke-Sqlcmd : Error converting data type nvarchar to uniqueidentifier

Question

I have a SQL Server stored procedure which accepts first two parameters as uniqueidentifier and a third as nvarchar. When I execute against Azure SQL database in SSMS, it works fine and no problem but when I use Azure PowerShell, then I get the following exception

Invoke-Sqlcmd : Error converting data type nvarchar to uniqueidentifier.

PowerShell script

 $parameter1 = New-Guid
 $parameter2 = New-Guid
 $parameter3 = "Test"

 $DatabaseName = "xxxx"
 $ServerName = "xxxx"

 $sqlQuery = "
            EXEC [dbo].[spxxxxxx]
            @parameter1 = [Guid]${parameter1},
            @parameter2 = [Guid]${parameter2},
            @parameter3 = '${parameter3}'
          "
 $params = @{
          'Database' = $DatabaseName
          'ServerInstance' = $ServerName
          'Username' = 'xxxx'
          'Password' = 'xxxx'
          'OutputSqlErrors' = $true
          'Query' =  $sqlQuery
    }

Invoke-Sqlcmd @params

Can anyone suggest how do I pass Guid parameters for stored procedure in PowerShell using Invoke-Sqlcmd to fix this problem?

David Browne - Microsoft · Accepted Answer · 2019-07-03 15:30:30Z

2

As always when constructing SQL queries, print them out and examine them. You're generating somethign like:

    EXEC [dbo].[spxxxxxx]
    @parameter1 = [Guid]2e244a72-1e11-496d-8a7f-37844ae8ce6b,
    @parameter2 = [Guid]5fe0e88f-9bea-4a9f-b67e-fe55cdcadfbc,
    @parameter3 = 'Test'

but the batch should look like

    EXEC [dbo].[spxxxxxx]
    @parameter1 =  '2e244a72-1e11-496d-8a7f-37844ae8ce6b',
    @parameter2 =  '5fe0e88f-9bea-4a9f-b67e-fe55cdcadfbc',
    @parameter3 = 'Test'

when passing a string literal that will be implicitly converted to a UNQUEIDENTIFIER.

answered Jul 3, 2019 at 15:30

David Browne - Microsoft

90.8k7 gold badges53 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

3355307 Over a year ago

Thank you very much for the print tip and solution. it worked!

Collectives™ on Stack Overflow

Azure PowerShell Invoke-Sqlcmd : Error converting data type nvarchar to uniqueidentifier

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related