SQL Compare from powershell

philcart

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

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.

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?

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

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.

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]

Brian Donahue

Hi Phil,

I've been told this is a known issue and will be fixed in version 10.2.

philcart

Thanks Brian, eagerly awaiting that update