How can we help you today? How can we help you today?

SQL Compare from powershell

I'm putting together a powershell script to help us deploy a changeset from TFS to SQL Server.

As the changeset is only a very small subset of the database objects, in some cases only a single procedure, I'm using the /include switch to only compare the objects in the changeset.

eg: the command line that is built up in Powershell looks like the following,
&"C:\Program Files (x86)\Red Gate\SQL Compare 10\SQLCompare.exe" /Scripts1:"C:\Temp\2583\SQL" /server2:SQL01 /database2:DB1 /scriptfile:"C:\Temp\2583\2583.sql" /options:default,IgnorePermissions,IgnoreUsersPermissionsAndR
oleMemberships /include:Different  /include:StoredProcedure:[eft_ListBrokenEfts]^|[app_CheckRptServer]^|[SendEmailGeneric]

When I run this from the command prompt, it works as expected and the script file is generated with the correct information.

However, when I use Invoke-Expression within Powershell I get the following error,
"Invoke-Expression : Expressions are only allowed as the first element of a pipeline."

This is because of the pipe characters used in the /include switch.

So I re-jigged my script to do the includes individually as per,
&"C:\Program Files (x86)\Red Gate\SQL Compare 10\SQLCompare.exe" /Scripts1:"C:\Temp\2583\SQL" /server2:UATMRISQL01 /database2:KFProd /scriptfile:"C:\Temp\2583\2583.sql" /options:default,IgnorePermissions,IgnoreUsersPermissionsAndR
oleMemberships /include:Different  /include:StoredProcedure:[eft_ListBrokenEfts] /include:StoredProcedure:[app_CheckRptServer] /include:StoredProcedure:[SendEmailGeneric]

This seemed to work, but the script generated included revoking permissions on the included procedures. I can't have the script revoking permissions as they are set specifically in each environment.

Anyone got any ideas on how I progress?


Cheers
Phil
philcart
0

Comments

7 comments

Sort by
  • philcart
    I've solved my "Expressions are only allowed as the first element of a pipeline." error by escaping the pipe character with a backtick.

    But that still leaves me with a deployment script messing around with permissions.
    philcart
    0
  • Brian Donahue
    The IgnorePermissions option should, as the name implies, cause the program to stop scripting permissions. What kinds of permissions are still being scripted in the migration script?
    Brian Donahue
    0
  • philcart
    Brian

    In our TEST/UAT environments there are additional roles/users that have permissions on the procedures being deployed. As such we don't want these permissions reverting to what is set in the DEV environment.

    The script that is generated contains the following, 
    PRINT N'Altering permissions on [dbo].[SendEmailGeneric]'
GO
REVOKE EXECUTE ON  [dbo].[SendEmailGeneric] TO [ApplicationSupport]
GO

    I've tried various switches/options, but it seems explicitly including items overrides the ignore options.

    I would expect the included items to be compared as per the specified switches and options.


    Cheers
    Phil
    philcart
    0
  • Brian Donahue
    Can you try removing the "default" from the options and explicitly stating the options that you want on the command line? I'm told there may a bug in that area.
    Brian Donahue
    0
  • philcart
    Brian

    I've replaced the "default" option so my command line now looks like, 
    &"C:\Program Files (x86)\Red Gate\SQL Compare 10\SQLCompare.exe" /Scripts1:"C:\Temp\2583\SQL" /server2:UATMRISQL01 /database2:KFProd /scriptfile:"C:\Temp\2583\2583.sql" /options:DecryptPost2KEncryptedObjects,IgnoreFillFactor,IgnoreWhiteSpace,IncludeDependencies,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,IgnorePermissions,IgnoreUsersPermissionsAndRoleMemberships /include:Different  /include:StoredProcedure:[eft_ListBrokenEfts]^|[app_CheckRptServer]^|[SendEmailGeneric]


    But I still get the "REVOKE" statements included in the script that is generated.


    Thanks
    Phil
    [/code]
    philcart
    0
  • Brian Donahue
    Hi Phil,

    I've been told this is a known issue and will be fixed in version 10.2.
    Brian Donahue
    0
  • philcart
    Thanks Brian, eagerly awaiting that update :)
    philcart
    0

Add comment

Please sign in to leave a comment.