Mismatch between number of columns produced by 'SHOWGRANTS' and the number of aliases specified

[stephenchen408]

I get this error when trying to upload a table to snowflake.

I've been using excelerator for over a year and this is the first time this has happened and I don't think there have been any changes in what I've been doing.

ERROR: Error: SQL Compilation error: Mismatch between number of columns produced by 'SHOWGRANTS' and the number of aliases specified

[mherman-amd]

I have the same issue today - from my investigation it looks like Snowflake might have added another column to the returning result set from the show grants command. The query generated by Excelerator specifies the expected columns, but the result of the command produces an additional column so it doesn't know what to do with it and errors out, causing Excelerator to roll back the upload. I'm going to try to find where that query is generated in the VBA. It would be great if the developer would share a fix that could be "pasted in" as I have a couple minor modifications I would prefer not to re-create.

[mherman-amd]

Screenshot of what I added to my version of the VBA code attached. It worked for me, but please note I am not a developer for this add-in, so I don't know of any possible side-effects (I don't expect any). Just a guy who has benefited from many people posting useful stuff on the internet.

The function requiring modification is under the "Load" Module. The addition is ",granted_by_role_type"

[stephenchen408]

I actually realized later that the upload was actually successful - still getting the error message but when I check the updated table is indeed in snowflake.

On Fri, Apr 21, 2023 at 2:07 PM mherman-amd @.***> wrote:

I have the same issue today - from my investigation it looks like Snowflake might have added another column to the returning result set from the show grants command. The query generated by Excelerator specifies the expected columns, but the result of the command produces an additional column so it doesn't know what to do with it and errors out, causing Excelerator to roll back the upload. I'm going to try to find where that query is generated in the VBA. It would be great if the developer would share a fix that could be "pasted in" as I have a couple minor modifications I would prefer not to re-create.

— Reply to this email directly, view it on GitHub https://github.com/Snowflake-Labs/Excelerator/issues/38#issuecomment-1518173214, or unsubscribe https://github.com/notifications/unsubscribe-auth/ALZ5INEPJ3UOEZL7ZHDXS5LXCLEGPANCNFSM6AAAAAAXG464FM . You are receiving this because you authored the thread.Message ID: @.***>

[mherman-amd]

I actually realized later that the upload was actually successful - still getting the error message but when I check the updated table is indeed in snowflake. … On Fri, Apr 21, 2023 at 2:07 PM mherman-amd @.> wrote: I have the same issue today - from my investigation it looks like Snowflake might have added another column to the returning result set from the show grants command. The query generated by Excelerator specifies the expected columns, but the result of the command produces an additional column so it doesn't know what to do with it and errors out, causing Excelerator to roll back the upload. I'm going to try to find where that query is generated in the VBA. It would be great if the developer would share a fix that could be "pasted in" as I have a couple minor modifications I would prefer not to re-create. — Reply to this email directly, view it on GitHub <#38 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/ALZ5INEPJ3UOEZL7ZHDXS5LXCLEGPANCNFSM6AAAAAAXG464FM . You are receiving this because you authored the thread.Message ID: @.>

Ok, for me the table was there but it didn't actually contain the updates. I assume that's because the default behavior or Excelerator is to try to swap back the original data when it encounters an error.

[ssegal100]

@mherman-amd & @stephenchen408 Thank you guys for hunting this down. You are right, there was a new column added to the GRANTS_TO_ROLES view, which this leverages. https://docs.snowflake.com/en/release-notes/2023-03-bcr

Your fix looks good, but I would actually prefer the change to specify the columns explicitly instead of using the 'SELECT *'. I should never have relied on that in the first place. Poor design. So the change to the SQL would look like this: (select 'created_on','privilege','granted_on','name','granted_to','grantee_name','grant_option','granted_by','granted_by_role_type' from table(result_scan(last_query_id()))) I'm actually not sure if the new column 'granted_by_role_type ' is needed. I'm not able to test it currently. Would you try with and without this last column? This columns is part of a new Snowflake feature that some accounts have turned on and some do not. If it works without the new column then that is preferable since it will work in accounts with this turned on or off. Please let me know how it goes. Thanks. Steve

[stephenchen408]

So this error seemed to have resolved itself - I didn’t change anything and the next day everything was back to working normally.

On Sun, Apr 23, 2023 at 9:02 PM Steven Segal @.***> wrote:

@mherman-amd https://github.com/mherman-amd & @stephenchen408 https://github.com/stephenchen408 Thank you guys for hunting this down. You are right, there was a new column added to the GRANTS_TO_ROLES view, which this leverages. https://docs.snowflake.com/en/release-notes/2023-03-bcr

Your fix looks good, but I would actually prefer the change to specify the columns explicitly instead of using the 'SELECT *'. I should never have relied on that in the first place. Poor design. So the change to the SQL would look like this: (select 'created_on','privilege','granted_on','name','granted_to','grantee_name','grant_option','granted_by','granted_by_role_type' from table(result_scan(last_query_id()))) I'm actually not sure if the new column 'granted_by_role_type ' is needed. I'm not able to test it currently. Would you try with and without this last column? This columns is part of a new Snowflake feature that some accounts have turned on and some do not. If it works without the new column then that is preferable since it will work in accounts with this turned on or off. Please let me know how it goes. Thanks. Steve

— Reply to this email directly, view it on GitHub https://github.com/Snowflake-Labs/Excelerator/issues/38#issuecomment-1519231303, or unsubscribe https://github.com/notifications/unsubscribe-auth/ALZ5INCPKD4J4DIJTPOPXE3XCXGKPANCNFSM6AAAAAAXG464FM . You are receiving this because you were mentioned.Message ID: @.***>

[ssegal100]

Maybe that new feature was disabled for now. It might come back, so let me know.

[sniidu]

We had to manually alter showgrants code as well. For future changes sake we decided to hardcode only necessary columns:

sql = "with ShowGrants (privilege,granted_to,grantee_name,grant_option,created_on) as " & _
    "(select " & Chr(34) & "privilege" & Chr(34) & ", " & Chr(34) & "granted_to" & Chr(34) & ", " & Chr(34) & "grantee_name" & Chr(34) & ", " & Chr(34) & "grant_option" & Chr(34) & ", " & Chr(34) & "created_on" & Chr(34) & " from  table(result_scan(last_query_id()))) " & _

[mherman-amd]

@ssegal100, it looks like the granted_by_role_type came and went in Snowflake somehow (maybe someone was changing our account settings? and then I got the same error again when I was looking for it in the VBA query), so I updated my add-in version to remove the reference (it couldn't have been required previously) and changed the "select *" from the next line to explicit column references, now working again.