Procs Scripted With ALTER When Scripting A Database

• 
  It turns out that my first description wasn't quite right. What happened was I compared a script and a live database. I told SQLCompare to synchronize from the live database to the script. I then went through SQLCompare's synchronization steps. When I compared the scripts to the live database again, the scripts had ALTER, which no longer matches the CREATE scripted from the live database.

  commcare June 13, 2007 13:33

  - 0 +
• 
  I can't reproduce your problem here - whenever Compare creates or alters a stored procedure in a script database for me, I always end up with a CREATE PROC or CREATE PROCEDURE statement, never an ALTER PROC or ALTER PROCEDURE statement. The only way I could get ALTER PROC statements out of it was to compare to a snapshot or live database and save the script from there.
  
  Do you mean that the scripts were coming out with ALTER PROC statements (no CREATE PROC statements), or do you have stored procedures with CREATE statements in which are being changed, or do you mean that Compare scripts out CREATE PROC statements as if they were ALTER PROC statements which occasionally introduces differences?
  
  Also, note that SQL Compare can't reliably reproduce what SQL Server does to whitespace in stored procedures, so if you have turned the option Ignore Whitespace off, you might notice spurious differences in your stored procedures.
  
  If you're still having the problem with Ignore Whitespace turned on, we'd appreciate a 'before' and 'after' of the affected stored procedures (or a sample case that demonstrates the problem if you can't share the originals with us). You can email them to michelle.taylor@red-gate.com if you're willing.
  
  Regarding the defaults, SQL Compare does not consider the parentheses to amount to a difference which is show in the top pane (if your table is showing different then there is most likely some other difference in it - if there isn't, we'd like to know). The SQL Differences pane only does a textual comparison (for speed reasons) and hence shows up the parentheses as a difference even though the main comparison does not consider them as such. (SQL Compare can't reliably reproduce the bracketing of defaults and similar because their appearence in the live database depends on a range of factors we don't have access to, like whether the database was originally a 2000 database.)

  Michelle T June 14, 2007 06:04

  - 0 +
• 
  I have since discovered that the file scripted out by SQLCompare does not have the double parentheses. Rather, they show up when SQLCompare reads the file in to compare it to a live database. While this happens consistently for individual databases, it does not happen with all databases. Also, I am still having the problem that single and double parentheses do not compare as the same, even when these are the only errors listed by the Compare Project. Currently this forces me to check quite a few files for mismatches if I want to be sure the script and the database actually compare as equal.

  commcare June 19, 2007 14:43

  - 0 +
• 
  I now also realize that my problem with comparing scripts having CREATE vs ALTER has to do with how the scripts were generated. If I export the database structure I get CREATE. If I tell SQLCompare to synchronize a live database to a script and then copy the file generated, I get ALTER. Sorry, if this was obvious.

  commcare June 19, 2007 14:56

  - 0 +

Add comment

Please sign in to leave a comment.