Red Gate forums :: View topic - SQL Compare from powershell
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

SQL Compare from powershell

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
philcart



Joined: 14 Aug 2006
Posts: 32

PostPosted: Thu Mar 29, 2012 3:07 am    Post subject: SQL Compare from powershell Reply with quote

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
View user's profile Send private message
philcart



Joined: 14 Aug 2006
Posts: 32

PostPosted: Thu Mar 29, 2012 4:01 am    Post subject: Reply with quote

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
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6577

PostPosted: Mon Apr 02, 2012 11:15 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
philcart



Joined: 14 Aug 2006
Posts: 32

PostPosted: Mon Apr 02, 2012 11:20 pm    Post subject: Reply with quote

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
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6577

PostPosted: Wed Apr 04, 2012 3:29 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
philcart



Joined: 14 Aug 2006
Posts: 32

PostPosted: Thu Apr 05, 2012 4:02 am    Post subject: Reply with quote

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
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6577

PostPosted: Thu Apr 12, 2012 3:17 pm    Post subject: Reply with quote

Hi Phil,

I've been told this is a known issue and will be fixed in version 10.2.
Back to top
View user's profile Send private message
philcart



Joined: 14 Aug 2006
Posts: 32

PostPosted: Fri Apr 13, 2012 12:34 am    Post subject: Reply with quote

Thanks Brian, eagerly awaiting that update Smile
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group