metadata attributes 'EXEC sys.sp_addextendedproperty' not understood

[mfriedemann]

Hey,

MSSQL dumps with metadata attributes cause an error:

$> perl sqlserver2pgsql/sqlserver2pgsql.pl -conf dbtx.conf 
Cannot understand this comment: EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'-redacted-' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'-redacted-', @level2type=N'CONSTRAINT',@level2name=N'-redacted-'
 at sqlserver2pgsql/sqlserver2pgsql.pl line 1968, <$file> line 4979.
	main::parse_dump() called at sqlserver2pgsql/sqlserver2pgsql.pl line 2748

According to docs [1] and comments [2][3] on the net, these are used to store metadata for e.g. GUI designers and maybe other tooling.

Maybe just skip them with a warning or something instead of bailing out?

[1] https://msdn.microsoft.com/en-gb/library/ms180047.aspx
[2] https://stackoverflow.com/questions/3912761
[3] https://stackoverflow.com/questions/3856077

[marco44]

Can you either give me a full dump, or at least the full section of the dump ? (from this exec to the next "go") ? Just to be sure the correction I'll do works ?

You can send this to cousinmarc at gmail dot com

[mfriedemann]

I can't really do that, as this happened with a DB that I am not at leisure to share.
I can try and create a very simple test db with one table or something that shows both the issue for #58 und #59, but there is currently very little time, so it might not be next week.

[marco44]

Yes, please. That will make it possible for me to add this to regression testing

[mark-jay]

Hey!

I've run into the same problem:

Don't know what to do with this extendedproperty: EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'ONEBANK.ACCOUNT.ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ACCOUNT', @level2type=N'COLUMN',@level2name=N'ID'
 at /path/to/sqlserver2pgsql.pl line 2128, <$file> line 9883.
	main::parse_dump() called at /path/to/sqlserver2pgsql.pl line 2917

so I'm attaching a relevant part of my dump:

CREATE TABLE [dbo].[ACCOUNT](
	[ID] [char](36) NOT NULL,
	[VERSION] [numeric](10, 0) NOT NULL,
	[APPLICATION_ID] [char](36) NOT NULL,
	[ACCOUNT_NUMBER] [nvarchar](35) NOT NULL,
	[DELETED] [numeric](1, 0) NOT NULL,
	[DELETED_BY] [char](36) NULL,
	[DELETED_ON] [datetime2](0) NULL,
	[CREATED_ON] [datetime2](0) NULL,
	[CREATED_BY] [char](36) NULL,
	[DAILY_LIMIT] [numeric](21, 7) NULL,
	[BIC] [varchar](35) NULL,
	[IBAN] [varchar](35) NULL,
	[BACK_OFFICE_ACCOUNT_NUMBER] [varchar](35) NULL,
	[BANK_ACCOUNT_NUMBER] [varchar](35) NULL,
	[OWNER_ID] [char](36) NULL,
	[ALLOW_PENDING] [numeric](1, 0) NOT NULL DEFAULT ((0)),
 CONSTRAINT [SYS_C0010802] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'ONEBANK.ACCOUNT.ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ACCOUNT', @level2type=N'COLUMN',@level2name=N'ID'
GO

[madtibo]

Hello mark-jay,

It seems like this key is a comment.
I created a new branch b074c5b.
Could you check if it solves your issue?

Cordialement,

[mark-jay]

Hello madtibo,
Yes, thank you!
It helped but now I have the same problem with the following line, error:

Cannot understand this comment: EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'ONEBANK.ACCOUNT_CATEGORY.UQ_INDEX' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ACCOUNT_CATEGORY', @level2type=N'INDEX',@level2name=N'UQ_INDEX'

Table itself:

CREATE TABLE [dbo].[ACCOUNT_CATEGORY](
	[ID] [char](36) NOT NULL,
	[VERSION] [numeric](10, 0) NOT NULL,
    ...
 CONSTRAINT [SYS_C0010844] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

[madtibo]

Hello,
Is seems that it was a comment on an index created with the constraint SYS_C0010844.
I have added a way to add index comment.
But it is quite difficult to find the constraint linked to an index name.
Thus, it should now work but you would not have the comment.

Can you test with 2f44917?

Cordialement,

[mark-jay]

Hello,
I tested it on my dump and can verify it is working now,
Thank you!

[madtibo]

Great news, I will merge it then.