none
Script View As RRS feed

  • Question

  • When right clicking a View and selecting "Script View As" when you select Create To it creates a script that if quotes are in the design it creates the script with double quotes.  When selecting Alter To it creates the script with quotes with single quotes.  Anyone know how to stop this because it throws errors when you try to run it because of the single quotes.
    • Moved by Tom Phillips Friday, September 6, 2019 5:03 PM SSMS question
    Friday, August 2, 2019 7:52 PM

All replies

  • Post an example to explain your question.

    A Fan of SSIS, SSRS and SSAS

    Friday, August 2, 2019 8:15 PM
  • Hi mrbill65,

     

    The description of your question is very vague, please show your example and provide the error message.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, August 5, 2019 6:58 AM
  • Example:  There are numerous cases of this within the script but to just compare one:

    (dbo.Addresses.AddressNumber + '' '', '''') Create

    (dbo.Addresses.AddressNumber + ' ', '') Alter

    IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[v_Permits]'))
    EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[v_Permits]
    AS
    SELECT        dbo.Permits.PermitId, dbo.Permits.PermitTypeId, dbo.Permits.PermitNumber, dbo.Permits.BeginDate, dbo.Permits.EndDate, dbo.Permits.Comment, dbo.Permits.AddressId, dbo.Permits.RecordId, dbo.PermitTypes.PermitType,
                             dbo.Permits.IssuedToPartyId, dbo.Party.PartyName, dbo.Addresses.AddressCode, ISNULL(dbo.Addresses.AddressNumber + '' '', '''') + ISNULL(dbo.Addresses.Direction + '' '', '''') + ISNULL(dbo.Addresses.Address + '' '', '''')
                             + ISNULL(dbo.Addresses.Suffix + '' '', '''') + ISNULL(dbo.Addresses.SubAddress + '' '', '''') AS FullAddress, dbo.Addresses.City, dbo.Addresses.ZipId, dbo.States.StateAbbr AS State, dbo.PermitTypes.LegalDesc, dbo.Zips.Zip,
                             dbo.Permits.Complete, dbo.Permits.ApprovalStep, dbo.PermitTypes.AgencyId, dbo.Permits.ContactId, dbo.Permits.PropConst, dbo.Permits.OwnerId, dbo.Permits.ContractorId, dbo.PermitTypeActList.PropConst AS TypePropConst,
                                 (SELECT        TOP (1) ISNULL(Phone + '' '', '''') + ISNULL(Extension, '''') AS Phone
                                   FROM            dbo.v_PhoneList
                                   WHERE        (PhoneType = ''Phone'') AND (PartyId = dbo.Permits.IssuedToPartyId)
                                   ORDER BY Sequence) AS Phone, dbo.Permits.FromWeb, dbo.Permits.PermitStatusId, dbo.Permits.ParentPermitId, dbo.Permits.SubmittalDate, dbo.PermitStatus.PermitStatus, dbo.Permits.IssuedToRoleTypeId,
                             dbo.Permits.ContactRoleTypeId, dbo.PermitTypeActList.ActListText, dbo.Permits.IssuingOfficerId, dbo.Permits.ReportId, dbo.Permits.CertReportId, dbo.Permits.LandCertReportId, dbo.Permits.ALReportId,
                             dbo.PermitTypes.EditDefFreq, dbo.Permits.OccupancyTypeId, dbo.Permits.PropertyUseTypeId,
                                 (SELECT        CASE WHEN COUNT(CAST(FeeId AS varchar(36))) = 0 OR
                                                                 (SELECT        SUM(BalanceDue)
                                                                   FROM            v_Fees
                                                                   WHERE        RecordId = dbo.Permits.PermitId
                                                                   GROUP BY RecordId) = 0 THEN CAST(0 AS bit) ELSE CAST(1 AS bit) END AS Expr1
                                   FROM            dbo.Fees
                                   WHERE        (RecordId = dbo.Permits.PermitId)) AS HasFees, dbo.Permits.StopAlerts, dbo.Permits.ExternalId, dbo.Permits.ItemId, dbo.v_Items.Description AS ItemDescription, dbo.v_Items.ItemType,
                             dbo.v_Items.Barcode AS ItemBarcode, dbo.v_Items.Location AS ItemLocation, dbo.v_Items.Status AS ItemStatus, dbo.v_Items.ItemNumber, dbo.v_Items.LocationBase AS ItemLocationBase,
                             dbo.v_Items.LocationType AS ItemLocationType, dbo.Permits.SignOffComplete
    FROM            dbo.Permits LEFT OUTER JOIN
                             dbo.v_Items ON dbo.Permits.ItemId = dbo.v_Items.ItemId LEFT OUTER JOIN
                             dbo.PermitTypes ON dbo.PermitTypes.PermitTypeId = dbo.Permits.PermitTypeId LEFT OUTER JOIN
                             dbo.PermitTypeActList ON dbo.Permits.PermitTypeId = dbo.PermitTypeActList.PermitTypeId LEFT OUTER JOIN
                             dbo.PermitStatus ON dbo.Permits.PermitStatusId = dbo.PermitStatus.PermitStatusId LEFT OUTER JOIN
                             dbo.Party ON dbo.Permits.IssuedToPartyId = dbo.Party.PartyID LEFT OUTER JOIN
                             dbo.Addresses ON dbo.Permits.AddressId = dbo.Addresses.AddressId LEFT OUTER JOIN
                             dbo.Zips ON dbo.Zips.ZipId = dbo.Addresses.ZipId LEFT OUTER JOIN
                             dbo.States ON dbo.Addresses.StateId = dbo.States.StateId
    '
    GO

    ALTER VIEW [dbo].[v_Permits]
    AS
    SELECT        dbo.Permits.PermitId, dbo.Permits.PermitTypeId, dbo.Permits.PermitNumber, dbo.Permits.BeginDate, dbo.Permits.EndDate, dbo.Permits.Comment, dbo.Permits.AddressId, dbo.Permits.RecordId, dbo.PermitTypes.PermitType,
                             dbo.Permits.IssuedToPartyId, dbo.Party.PartyName, dbo.Addresses.AddressCode, ISNULL(dbo.Addresses.AddressNumber + ' ', '') + ISNULL(dbo.Addresses.Direction + ' ', '') + ISNULL(dbo.Addresses.Address + ' ', '')
                             + ISNULL(dbo.Addresses.Suffix + ' ', '') + ISNULL(dbo.Addresses.SubAddress + ' ', '') AS FullAddress, dbo.Addresses.City, dbo.Addresses.ZipId, dbo.States.StateAbbr AS State, dbo.PermitTypes.LegalDesc, dbo.Zips.Zip,
                             dbo.Permits.Complete, dbo.Permits.ApprovalStep, dbo.PermitTypes.AgencyId, dbo.Permits.ContactId, dbo.Permits.PropConst, dbo.Permits.OwnerId, dbo.Permits.ContractorId, dbo.PermitTypeActList.PropConst AS TypePropConst,
                                 (SELECT        TOP (1) ISNULL(Phone + ' ', '') + ISNULL(Extension, '') AS Phone
                                   FROM            dbo.v_PhoneList
                                   WHERE        (PhoneType = 'Phone') AND (PartyId = dbo.Permits.IssuedToPartyId)
                                   ORDER BY Sequence) AS Phone, dbo.Permits.FromWeb, dbo.Permits.PermitStatusId, dbo.Permits.ParentPermitId, dbo.Permits.SubmittalDate, dbo.PermitStatus.PermitStatus, dbo.Permits.IssuedToRoleTypeId,
                             dbo.Permits.ContactRoleTypeId, dbo.PermitTypeActList.ActListText, dbo.Permits.IssuingOfficerId, dbo.Permits.ReportId, dbo.Permits.CertReportId, dbo.Permits.LandCertReportId, dbo.Permits.ALReportId,
                             dbo.PermitTypes.EditDefFreq, dbo.Permits.OccupancyTypeId, dbo.Permits.PropertyUseTypeId,
                                 (SELECT        CASE WHEN COUNT(CAST(FeeId AS varchar(36))) = 0 OR
                                                                 (SELECT        SUM(BalanceDue)
                                                                   FROM            v_Fees
                                                                   WHERE        RecordId = dbo.Permits.PermitId
                                                                   GROUP BY RecordId) = 0 THEN CAST(0 AS bit) ELSE CAST(1 AS bit) END AS Expr1
                                   FROM            dbo.Fees
                                   WHERE        (RecordId = dbo.Permits.PermitId)) AS HasFees, dbo.Permits.StopAlerts, dbo.Permits.ExternalId, dbo.Permits.ItemId, dbo.v_Items.Description AS ItemDescription, dbo.v_Items.ItemType,
                             dbo.v_Items.Barcode AS ItemBarcode, dbo.v_Items.Location AS ItemLocation, dbo.v_Items.Status AS ItemStatus, dbo.v_Items.ItemNumber, dbo.v_Items.LocationBase AS ItemLocationBase,
                             dbo.v_Items.LocationType AS ItemLocationType, dbo.Permits.SignOffComplete
    FROM            dbo.Permits LEFT OUTER JOIN
                             dbo.v_Items ON dbo.Permits.ItemId = dbo.v_Items.ItemId LEFT OUTER JOIN
                             dbo.PermitTypes ON dbo.PermitTypes.PermitTypeId = dbo.Permits.PermitTypeId LEFT OUTER JOIN
                             dbo.PermitTypeActList ON dbo.Permits.PermitTypeId = dbo.PermitTypeActList.PermitTypeId LEFT OUTER JOIN
                             dbo.PermitStatus ON dbo.Permits.PermitStatusId = dbo.PermitStatus.PermitStatusId LEFT OUTER JOIN
                             dbo.Party ON dbo.Permits.IssuedToPartyId = dbo.Party.PartyID LEFT OUTER JOIN
                             dbo.Addresses ON dbo.Permits.AddressId = dbo.Addresses.AddressId LEFT OUTER JOIN
                             dbo.Zips ON dbo.Zips.ZipId = dbo.Addresses.ZipId LEFT OUTER JOIN
                             dbo.States ON dbo.Addresses.StateId = dbo.States.StateId
    GO

    Friday, September 6, 2019 4:13 PM
  • Any idea why you used a dynamic statement to create the view?

    A Fan of SSIS, SSRS and SSAS

    Friday, September 6, 2019 5:59 PM
  • Go Tools->Options in the menu. Find SQL Server Object Explorer. Select the Scripting section. In the table to the right, just below the heading "Object scripting options" you find "Check for object existence". Set this set setting to False, to avoid the nesting with dynamic SQL.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, September 6, 2019 9:44 PM
  • This is what has always been done is what I was told.  I have no clue why.  What would be a better option?  Not include an Alter Statement in with an else on a create view if not exists?  These type of script we do all the time using them to initially install a client app or update a client app to the next version of software.
    Friday, September 6, 2019 9:56 PM