| Author |
Message |
philcart
Joined: 14 Aug 2006
Posts: 30
|
 Posted: Thu Mar 29, 2012 3:07 am Post subject: 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,
| Code: |
&"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,
| Code: |
&"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 |
|
| Back to top |
|
 |
philcart
Joined: 14 Aug 2006
Posts: 30
|
| Posted: Thu Mar 29, 2012 4:01 am Post subject: |
|
|
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. |
|
| Back to top |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6346
Location: Red Gate Software
|
| Posted: Mon Apr 02, 2012 11:15 am Post subject: |
|
|
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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
philcart
Joined: 14 Aug 2006
Posts: 30
|
| Posted: Mon Apr 02, 2012 11:20 pm Post subject: |
|
|
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,
| Code: |
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 |
|
| Back to top |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6346
Location: Red Gate Software
|
| Posted: Wed Apr 04, 2012 3:29 pm Post subject: |
|
|
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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
philcart
Joined: 14 Aug 2006
Posts: 30
|
| Posted: Thu Apr 05, 2012 4:02 am Post subject: |
|
|
Brian
I've replaced the "default" option so my command line now looks like,
| Code: |
&"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] |
|
| Back to top |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6346
Location: Red Gate Software
|
| Posted: Thu Apr 12, 2012 3:17 pm Post subject: |
|
|
Hi Phil,
I've been told this is a known issue and will be fixed in version 10.2.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
philcart
Joined: 14 Aug 2006
Posts: 30
|
| Posted: Fri Apr 13, 2012 12:34 am Post subject: |
|
|
Thanks Brian, eagerly awaiting that update  |
|
| Back to top |
|
|
|
