SQLTeam.com | Weblogs | Forums

Problem with scripting view as create to: extended property cannot be more than 7,500 bytes


#1

Hi all,

I have been asked to create copies of a view and it's underlying tables for testing purposes.

I scripted the view and changed the table names where required, but when I go to create the new view I get the following error.

Msg 15097, Level 16, State 1, Procedure
sp_addextendedproperty, Line 16

The size associated with an extended property
cannot be more than 7,500 bytes.

The part of the script in question is as follows:-

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]

The thing is, this is generated by SQL Server, so I don't know what the value should be.

Any ideas what I'm doing wrong?

Regards

Farren


#2

What did you use to do that?

Presumably it was actually much longer than the snippet you have posted? (the error suggests that the string much be more than 7,500 bytes, so if that individual call to sys.sp_addextendedproperty was not that big then the problem is probably elsewhere).

It looks like that is storing a Diagram (of the view or somesuch). Do you need that on your copy? it is not instrumental to your copy-view working, per se.


#3

Hi Kristen, thanks for your response

I am not familiar with this database/application at all so I'm not sure what part of the view is essential.

I will post the entire script below in case that helps at all.

I just find it strange that SQL creates the script of the object, but then can't actually run it.

Regards

Farren

/****** Object: View [dbo].[v_GPS_DataFeedFuzzy_Test] Script Date: 9/15/2015 3:50:32 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/WHERE (Country <> 'DE')/
CREATE VIEW [dbo].[v_GPS_DataFeedFuzzy_Test]
AS
SELECT dbo.GPS_EmployeeInfo_Test.GITPersonID, dbo.GPS_EmployeeInfo_Test.FirstName, dbo.GPS_EmployeeInfo_Test.LastName, dbo.GPS_EmployeeInfo_Test.PrefFirstName,
dbo.GPS_EmployeeInfo_Test.PrefLastName, ISNULL(dbo.GPS_CostCenterChange_Test.NewCostCenter, dbo.GPS_EmployeeInfo_Test.CostCenter) AS CostCenter,
dbo.GPS_EmployeeInfo_Test.CostCenterDesc, dbo.GPS_EmployeeInfo_Test.GroupCode, dbo.GPS_EmployeeInfo_Test.GroupDesc, dbo.GPS_EmployeeInfo_Test.DivisionCode,
dbo.GPS_EmployeeInfo_Test.DivisionDesc, dbo.GPS_EmployeeInfo_Test.FunctionCode, dbo.GPS_EmployeeInfo_Test.FunctionDesc, dbo.GPS_EmployeeInfo_Test.EmpStatusDate,
dbo.GPS_EmployeeInfo_Test.EmpStatusCode, dbo.GPS_EmployeeInfo_Test.EmpStatusDesc, dbo.GPS_EmployeeInfo_Test.CategoryDate, dbo.GPS_EmployeeInfo_Test.CategoryCode,
dbo.GPS_EmployeeInfo_Test.CategoryDesc, dbo.GPS_EmployeeInfo_Test.CategoryTypeCode, dbo.GPS_EmployeeInfo_Test.CategoryTypeDesc, dbo.GPS_EmployeeInfo_Test.Hours,
dbo.GPS_EmployeeInfo_Test.FTELevel, '' AS Grade, dbo.GPS_EmployeeInfo_Test.HireDate, dbo.GPS_EmployeeInfo_Test.Location, dbo.GPS_EmployeeInfo_Test.RateDate,
dbo.GPS_EmployeeInfo_Test.CurrencyCode, dbo.GPS_EmployeeInfo_Test.IncentivePct, dbo.GPS_EmployeeInfo_Test.JobDate, dbo.GPS_EmployeeInfo_Test.JobTitle,
dbo.GPS_EmployeeInfo_Test.JobReason, dbo.GPS_EmployeeInfo_Test.CurrentIncentivePlan, ROUND(LTRIM(STR(ISNULL(dbo.GPS_EmployeeInfo_Test.Salary, 0) * RAND() * 0.65, 18,
2)), 0) AS Salary, ROUND(CAST(ISNULL(dbo.GPS_EmployeeInfo_Test.IncentiveAmt, 10000) AS DECIMAL(10, 2)) * RAND() * 0.65, 2) AS IncentiveAmt,
dbo.GPS_EmployeeInfo_Test.Country, dbo.GPS_EmployeeInfo_Test.CurrencyName, dbo.GPS_EmployeeInfo_Test.EmployeeID, dbo.GPS_EmployeeInfo_Test.EmployerContrPct,dbo.GPS_EmployeeInfo_Test.EmailAddress
FROM dbo.GPS_EmployeeInfo_Test LEFT OUTER JOIN
dbo.GPS_CostCenterChange_Test ON dbo.GPS_EmployeeInfo_Test.GITPersonID = dbo.GPS_CostCenterChange_Test.GITPersonID

GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[41] 4[31] 2[4] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "GPS_EmployeeInfo_Test"
Begin Extent =
Top = 6
Left = 38
Bottom = 275
Right = 247
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "GPS_CostCenterChange_Test"
Begin Extent =
Top = 6
Left = 285
Bottom = 114
Right = 441
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 43
Width = 284
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 5340
Alias' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'v_GPS_DataFeedFuzzy_Test'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N' = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'v_GPS_DataFeedFuzzy_Test'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'v_GPS_DataFeedFuzzy_Test'
GO


#4

You can remove everything starting with the "EXEC sys.sp_addextendedproperty ..." and use the rest to create the view. The extendeproperty is to persist the look and feel of what the window would look like in the design view. Almost always, you don't really care about that.


#5

Thanks James,

Now it makes sense.

Regards

Farren