User profile for Andras B

Hi, I have not looked at the issue from this perspective. We will consider this in for future releases. Many thanks, Andras / comments

Hi, I have not looked at the issue from this perspective. We will consider this in for future releases. Many thanks, Andras

0 votes

thies wrote: Brian Donahue wrote: You may want to have a look at this blog: http://blogs.red-gate.com/blogs/andras/ This contains some information about how we have adjusted SQL Compare to cope with SQL Server 2005's requirement for doubling-up the partentesis. I read through this, but am not quite sure if it makes things clearer to me. I need a way to compare an SQL 2000 database with an SQL 2005 database, and after syncronize them the two, having SQL Compare tell me there are no differences between the two. With the way things are now between 2000 and 2005, it compares SQL 2000s: DEFAULT (0) with SQL 2005s: DEFAULT ((0)) and says they are different. Fine, lets sync these differences and recompare. SQL Compare suggests chaning the SQL 2005s DEFAULT ((0)) with DEFAULT (0) - but when this is executed on the SQL 2005 side, it changes again to DEFAULT ((0)), as expected. If I could get the DEFAULTs used in 2000 and 2005 to be viewed as the same, I would be happy, as that would be a once off - I am happy doing changes either in the SQL 2000 database or the SQL 2005 database. Problem: In SQL 2005 I cannot make DEFAULT (0) work. In SQL 2000 I cannot make DEFAULT ((0)) work. My main issue is SQL Compare saying these are different, is it makes me have to add a manual (read: error prone) process of checking the defaults in a seperate task (disabling DEFAULT checking in SQL Compare). I understand that this is not SQL Compare that has changed, but some other product. I would however love if SQL Compare where to handle this case. I love SQL Compare/SQL Data Compare - my job would not be as fun without such tools. Hi, Are you using a user defined type? The problem with UDTs is that they have changed a bit between 2000 and 2005. Under 2005 the UDTs live in a schema, under 2000 they have an owner that does not form part of the fully qualified name of the UDT. I have improved the support for such case in our internal build. The next dot release should have this fixed. So, just to summarise, the textual difference in SQL compare shows the default text to be different, internally we consider the semantics, so we consider them the same, but the base type of the column we mark in the current version as different because of the 2000 - 2005 changes. Note that if you migrate a 2000 db to 2005 by restoring it. changing the compatibility level, the 2000 behaviour will exist in 2005 [image] Regards, Andras[/code] / comments

thies wrote: Brian Donahue wrote: You may want to have a look at this blog:http://blogs.red-gate.com/blogs/andras/ This contains some information about how we have adjusted SQL Compare to cop...

0 votes

digitalage wrote: I am comparing 2 SQL Server 2005 Databases. One DB is on my local machine while another is on a remote Windows Server 2003. Both DBs are running under SQL Server 2005 Standard. Anyway, for some reason, when I compare the 2 databases, SQL Compare seems to think that many tables have differences even when they don't. I narrowed down the problem and it seems like there is some weird thing going on with the DEFAULTS on columns, specifically the ones on bit columns. In my local DB, SQL Compare shows the defaults as being ((0)) while in the remote DB it shows it as (0) with only 1 set of parentheses. Example: Local DB: [Absent] [bit] NULL CONSTRAINT [DF_Employees_Base_Absent] DEFAULT ((0)) Remote DB: [Absent] [bit] NULL CONSTRAINT [DF_Employees_Base_Absent] DEFAULT (0) This, I guess, is enough of a difference to make the 2 tables look different to SQL Compare. When I look at the tables through the management console, I do indeed see that there are 2 parentheses in the remote DB's Default's for all bit columns, but the local DB has only one set of parentheses. I was wondering if there was a setting that I need to change, in either SQL Server or Red-Gate that can help me avoid this issue...because now, almost all my tables come up as different which makes it a real pain to see whet the real differences are. (By the way, when I try to remove the parentheses or add the parentheses to make the 2 databases match, the parenthese revert back as soon as I save...so I can't even fix it that way) Thanks in advance!!! Hi, there is no way of fixing this. SQL Server 2005 parses the definition of defaults, and it modifies it. So it is no longer possible to put exactly the text we wish into a default. SQL Server even changes function names in addition to introducing parantheses all over the place. For this reason we now look at the semantics of the defaults, and compare them at semantic level. So while textual differences will exist, we still can detect correctly whether the content is different or is the same. For more information please see my blog http://blogs.red-gate.com/blogs/andras Regards, Andras / comments

digitalage wrote: I am comparing 2 SQL Server 2005 Databases. One DB is on my local machine while another is on a remote Windows Server 2003. Both DBs are running under SQL Server 2005 Standard....

0 votes

virtking wrote: SQL Compare 1. I ran across a table which was marked as "=", but there was a difference in one of the constraints. I don't have a screen shot of it. 2. We keep objects under custom owners, so any dbo owned objects just clutters the view. In general it would be nice to filter objects by owner (i.e. exclude/include all 'dbo', 'abc' and 'xyz' owned objects). And make it a project level setting. 3. Template projects - projects only for settings without database selection capability. Regular projects should just inherit from a template project. If you need to change some/all settings - just change to a different template project, if exists, or change inherited properties right in the project. 4. When comparing snapshots of the same database, it would be nice to see file name of each snapshot file (possibly in a tooltip). 5. 'Map Owners' functionality runs only once per project - if I change either source or destination databases, clicking on the button will just display results from 1st mapping. 6. Ability to prefix *all* object names with owners - currently only tables are prefixed with owners (this is how it works with v.3 and 4 of the tool.). This will allow to run the script using just one id, instead of switching between many (4 in our case, since we don't use dbo as owner.) when running scripts. It also is a problem from the security point of view, because a dba may not want to give out more than one id to perform database conversion if he/she is not the person who executes the scripts. Also, (this is not unique to v5) if an object is prefixed in one database, but not the other, and this is the only difference, treat it as equal. SQL Data Compare 1. See #2 above. 2. See #3 above Thanks for a great product. Hi, some answers to a few of your points above. For #1: Certain objects cannot be recreated exatly the same way in SQL Server. One of these is a default. Defaults are processed, and in SQL Server 2005 are modified in a very radical way. As an example, if you create a DRI default on 2005 as having the value "1", SQL Server will store (without asking you) "((1))". For this reason we must consider the semantics of defaults, and certain other objects. For more information please have a look at my blog post: <a>http://blogs.red-gate.com/blogs/andras/archive/2005/11/04/1.aspx</a> So while the objects are the same the textual representations are different. Some of these you can influence by our options. For example if you have many with statements in a stored procedure, e.g. "with encryption, recompile", I try to ignore the order difference. But this can be influenced by the ignore with element order option. However, in the bottom panel we display textual differences in the creation SQL. Unlike the engine the panel with the textual difference does not consider the semantics, and hence is the difference you see. For point 6, there is partial support for this via mappings, but it is not complete, thus it is reserved for the future. Regards, Andras / comments

virtking wrote: SQL Compare 1. I ran across a table which was marked as "=", but there was a difference in one of the constraints. I don't have a screen shot of it. 2. We keep objects under cu...

0 votes

fpw23 wrote: I just used the beta for the first time and I love the UI and clear labels on everything but i did notice one thing. I compared two tables where I inserted a new column at position three. When i created the sync script, it just added the new column at the last position. I am pretty sure that the old version would preserve the column order. am I missing an option somewhere? Thanks, Frank Hi Frank, please use the "force column order" option. This is off by default both in the old and the new version. Regards, Andras / comments

fpw23 wrote: I just used the beta for the first time and I love the UI and clear labels on everything but i did notice one thing. I compared two tables where I inserted a new column at position...

0 votes

mikeATCompuware wrote: There appears to be an issue when syncing objects where the underlying definition does not match the object name. For instance, you have a view object named v_users and its definition is: create view dbo.v_users AS select user_name, user_id from users Now if you rename the view object to v_users2, the underlying view definition remains 'create view dbo.v_users AS...' In addition to views, I think this issue applies to stored procedures and maybe other objects. Also, if the owner is changed, a similar issue will occur. You are absolutely right. And unfortunately this is a sideeffect of sp_rename and of operations that change the owner of a stored procedure, view, function, etc. For this reason it is advised to avoid sp_rename (or to use it with extreme care). Basically this means that the database will be in a sort of inconsistent state (its stored procedure definition is not reflecting the statement that shoud be used to create it again). In the current version we do not support such inconsistent databases. The better way of renaming stored procedures is to drop them, and create them again. Regards, Andras / comments

mikeATCompuware wrote: There appears to be an issue when syncing objects where the underlying definition does not match the object name. For instance, you have a view object named v_users and it...

0 votes

ESG wrote: Hi, I have just installed the trial version of SQL Compare - very impressive. Anyway, I copied a database so that I know that I'm dealing with two identical DBs to begin with. When I view the Summary Report where it lists the user is lists the following code: == -- User == IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'username') == exec sp_addlogin N'username', 'password' == GO == sp_grantdbaccess N'username', N'username' == GO == Is the password shown a generic password allocated by SQL Compare? With the intention of the DBA then going in and resetting passwords. Or are the passwords listed the actual passwords (I am new to this orgnasation, and the possibility that all the users have the same password is very possible - this server is not in production yet). I will be sharing these reports with others in the project, as we will be using it to document updates etc provided by the developers (long story, don't ask!). Anyway, I am not happy about the user passwords being listed in plain text in the report. I have had a look at the SQL Compare Help, but there is nothing at all that I can see about users & passwords. Can anyone shed any light on this for me? Thanks ESG [image] Hi, The password in question is one that is set by SQL Compare. If a user does not exist in the target database, we will generate a script that will create this user, with the password we specify ("p@ssw0rd"). We do give a warning about this, so it is worth to have a look at them. The warning also serves as a reminder, that wile we do create a user to ensure that the migration succeeds, idealy users should be migrated manually in a way that complies with the organization's security policy (if there is one [image] ). This is especially important in case of SQL Server 2005 which has a few extra aurthorization options for users (such as certificate or key based users). Regards, Andras / comments

ESG wrote: Hi, I have just installed the trial version of SQL Compare - very impressive. Anyway, I copied a database so that I know that I'm dealing with two identical DBs to begin with. When I ...

0 votes

colinbo wrote: I'm working on incorporating the command line version into our build process. I'd like to see a future release where we can exclude objects when making a snapshot. The reason behind it is that our development environment always contains a set of users which do not exist in our production environment (e.g. developers have db_owner access in development, but not in production). As a result when comparing changes to a database in production we never want to have users factor. We save all schema information in our snapshots, regardless the options that are set (e.g. if one sets ignore triggers, we still save triggers into the snapshot, since in the future one may change the option). Among the reasons for saving everything is the complex dependencies that a database may contain. One can of course use these options to control the synchronization later, but if you would like to have full flexibility and control over what objects are synchronized, I would recommend our toolkit. It allows you to filter objects/differences at a very fine grain from a .Net based application. Regards, Andras / comments

colinbo wrote: I'm working on incorporating the command line version into our build process. I'd like to see a future release where we can exclude objects when making a snapshot. The reason be...

0 votes

iposner wrote: I think the answer given is very shoddy. If there is a problem with sp_rename not changing the syscomments definition, then it should be escalated to Microsoft to provide a fix. I'm sure they will listen far more attentively to a major toolset vendor than to individuals. Having said that, it is not beyond the capability of red gate to provide a cross-checking routine and modify scripts appropriately Hi, Microsoft is aware of the problem, but since the stored procedures work, Microsoft, AFAIK, is not planning any changes to sp_rename. Concerning us modifying the scripts to ensure their consistency is something I now added to my to do list, but I'm afraid it will not be in our next release. Many thanks for the suggestion, Regards, Andras / comments

iposner wrote: I think the answer given is very shoddy. If there is a problem with sp_rename not changing the syscomments definition, then it should be escalated to Microsoft to provide a fix. I...

0 votes

debeera wrote: We renamed a stored proc called "a" to "b" on database 1. Compare detects that stored proc "b" is not in the database 2. The script of stored proc "b" however still shows the stored proc name as being "a" and when you sync, stored proc "a" gets created in database 2. Does anyone know why this is happening, is there a setting that we are missing? Thanks. Hi, In SQL Server stored procedures are stored as text. If you just use sp_rename, the definition of the stored procedure will still contain the original name. This basically means that the database is in an inconsistent state. You can get the stored procedure definition using the syscomments system table. Unfortunately this is a limitation of SQL Server's sp_rename and it does end the database in an inconsistent state. Regards, Andras / comments

debeera wrote: We renamed a stored proc called "a" to "b" on database 1. Compare detects that stored proc "b" is not in the database 2. The script of stored proc "b" however still shows the stor...

0 votes

How can we help you today?

Andras B

Comments