User profile for JackAce

You're right, we are using SQL Data Compare to deploy. We are using SQL Data Compare version 9.0.0.117. We definitely unlinked the tables via Red Gate SQL Source Control. There is no longer a MyTable_Data.sql file in the Data folder, but the information still exists (and keeps getting repopulated) in the RedGateDatabaseInfo.xml file. The schema of this table hasn't been touched for a while. / comments

You're right, we are using SQL Data Compare to deploy. We are using SQL Data Compare version 9.0.0.117. We definitely unlinked the tables via Red Gate SQL Source Control. There is no longer a MyT...

0 votes

We are using SQLCompare.exe version 9.0.0.79. I verified this locally and on our build machine. / comments

We are using SQLCompare.exe version 9.0.0.79. I verified this locally and on our build machine.

0 votes

Thanks, this is exactly what I was looking for. / comments

Thanks, this is exactly what I was looking for.

0 votes

The article is a good start, but I was looking for more detailed information under the hood. The article mostly says, "If you are working in X type of environment, you should do Y." I am looking for something that says, "If you are working in X type of environment, you should be aware of Y." For example, some things that I noticed when working in a mixed environment (some developers with 'shared' and some developers with 'dedicated'): Sometimes you can right click a change in the "Commit Changes" tab and select "Undo Changes". Sometimes you can not. I haven't figured out the exact pattern yet. Working in a dedicated (local) environment is much faster especially because our database and source control servers are remote and we have to connect to them via VPN. This is frustrating when working in a shared environment because the Commit Changes tab usually times out when trying to analyze changes. One major thing that is a problem for us (working in the mixed environment) is that we automate our deployments to database environments. So our continuous integration server can deploy the versioned database to our development environment and our QA environment. If I check in a change from my dedicated (local) machine and deploy it to a shared database server (where other developers are working), their uncommitted changes will be wiped out. In order to avoid clobbering other developers' changes, I am currently using SQL Compare and SQL Data Compare to deploy my committed changes. This is not ideal (especially because only a few developers have licenses for these two products -- most only have licenses for SQL Source Control), and will go away once all developers can work locally. I think an article that outlines "gotchas" like the ones above would be very beneficial. / comments

The article is a good start, but I was looking for more detailed information under the hood. The article mostly says, "If you are working in X type of environment, you should do Y." I am looking ...

0 votes

james.billings wrote: Thanks for your post. I've had a look and couldn't see this already requested so I've added an enhancement request for it to be investigated. That's great. It's nice to know how long each step takes. We usually manually execute the scripts in a mirrored production environment beforehand and it's critical to identify which steps take a long time. / comments

james.billings wrote: Thanks for your post. I've had a look and couldn't see this already requested so I've added an enhancement request for it to be investigated. That's great. It's nice to k...

0 votes

So I figured out what was causing the problem. We have a UserDefinedDataType called dbo.KeyType which is just a wrapper for a BIGINT datatype. If I manually edit the datatype and change it to BIGINT in the table generation script, the error goes away and there doesn't seem to be any problems. We will eventually change all the KeyType fields to be BIGINT, so this issue will eventually go away for us. In any case, here are the answers to your questions: Chris Auckland wrote: 1) Which version of SQL Data compare 9 are you using? (help> About) 2) Does this happen when you use the GUI, or is it just commandline or API? 3) Were the data scripts created by SQL Data Compare? 4) Are you checking out just the data scripts from SVN and comparing them, or are you also checking out the schema statements in the /tables folder too? 5) Could you post the DDL for the dbo.MyTable? 1) I am using Red Gate SQL Data Compare v9.0.0.117 2) I get the Unsupported column type - empty error when using the GUI. 3) The scripts were generated using SQL Source Control, not SQL Data Compare 4) The script repository has both a Data and a Tables folder, both generated by SQL Source Control 5) Here's the table generation script (with some names obfuscated): CREATE TABLE [dbo].[ActivityFeedSource] ( [ID] [dbo].[KeyType] NOT NULL IDENTITY(1, 1), [CodeFile] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MethodName] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DisplayToCustomer] [bit] NOT NULL CONSTRAINT [DF_1] DEFAULT ((1)), [OriginatingCustomerText] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [OriginatingCustomerPublicText] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [SharedCustomerPublicText] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [IsActive] [bit] NOT NULL CONSTRAINT [DF_ActivityFeedSource_IsActive] DEFAULT ((1)), [Display] [bit] NOT NULL CONSTRAINT [DF_ActivityFeedSource_Display] DEFAULT ((1)) ) ON [PRIMARY] GO ALTER TABLE [dbo].[ActivityFeedSource] ADD CONSTRAINT [PK_ActivityFeedSource] PRIMARY KEY CLUSTERED ([ID]) WITH (STATISTICS_NORECOMPUTE=ON) ON [PRIMARY] GO / comments

So I figured out what was causing the problem. We have a UserDefinedDataType called dbo.KeyType which is just a wrapper for a BIGINT datatype. If I manually edit the datatype and change it to BIGI...

0 votes

james.billings wrote: As aolcot said, I'd expect you to simply source control the table data that the view references. Is there a specific reason you'd like to source control the view interpretation of this? Well, perhaps I should have explained a little more. We have a table that has system-defined lookup data as well as user-defined data. Not the greatest database design, I agree, but it is a design that I have inherited. So the underlying table looks like this MyID BIGINT IDENTITY(1, 1), -- Primary Key MyCode VARCHAR(50), -- Unique values when UserID is NULL UserID BIGINT NULL, MyDescription VARCHAR(200) -- Data that we are concerned with -- other columns go here The view does not contain the Primary Key and is schema bound. It also filters out records where UserID is not NULL. In the view, there is a unique constraint/index defined for the MyCode field. We rely on the MyCode field to identify records that we need. The underlying Primary Key values may be different in Development, QA, Staging and Production. So if we want to version control the content that we care about (values in the table where the UserID is NULL), then I figured that we could do it by versioning the content in the views. If I pull up the SQL Data Compare GUI and allow views to be shown, it gives me the option of synchronizing the views. This works fine. If I create a record in the table that has MyID of 10 and MyCode of 'happy birthday', then a record will be created in the other table with MyID of 101 and MyCode 'happy birthday', assuming that 101 is the next IDENTITY record in the table. If you update MyDescription in the first table to 'You live in a zoo' for the 'happy birthday' record, then the corresponding record in the second table will also update, even if the underlying MyID value is different. The problem is that there is no way to detect/commit changes to source control when changes are made to the data. I wouldn't be surprised to hear that I am going about this in a very backwards way. If there is a more elegant solution (besides putting the data in a separate table), I'd love to hear it. / comments

james.billings wrote: As aolcot said, I'd expect you to simply source control the table data that the view references. Is there a specific reason you'd like to source control the view interpreta...

0 votes

james.billings wrote: Thanks for the clarification. I think the scenario you have here is one that we'd not usually expect - we'd normally assume that static data you want to source control lives in its own table, and isn't "mixed" in with other records- so the short answer is you'd need to split it out. We can certainly look at implementing source-controlling views, but I'm not sure as to the complexity it would involve. I'd suggest you add it as a request over on our Uservoice so that we can gauge the interest from other users to see how in-demand this would be. Thanks! Yes, I understand that this is probably an unusual scenario that is spurned by a questionable design pattern, so I'll probably be a lone voice. In any case, I'll submit the request shortly. Moving forward, we'll probably refactor the single table into two tables. / comments

james.billings wrote: Thanks for the clarification. I think the scenario you have here is one that we'd not usually expect - we'd normally assume that static data you want to source control live...

0 votes

JackAce wrote: Is there something I can change to extend the timeout period of SQL Source Control? Is there something else going on that needs to change on my end? Obviously, using the command line tool to update source control manually is not the optimal solution. So I unlinked and relinked the database to SSC and it seems to have solved the issue. / comments

JackAce wrote: Is there something I can change to extend the timeout period of SQL Source Control? Is there something else going on that needs to change on my end? Obviously, using the command...

0 votes

ccollins wrote: From our experience, up to 25 tables is not so bad, between 25 and 50 it is a long inconvience, after 50 unbearable. We have one database with 304 static data linked tables. The Calculating changes spinner has been going for two hours now... The get latest might take all day for our other programmers. So have you been doing anything besides just waiting the X hours for the change list to show up? I was hoping that others have figured out a workaround. I'm even getting the "timeout expired" with another database that has very few linked tables. It's a small database and it's brand new, so there are just a few dozen tables and stored procedures. I'm still trying to figure out why. / comments

ccollins wrote: From our experience, up to 25 tables is not so bad, between 25 and 50 it is a long inconvience, after 50 unbearable. We have one database with 304 static data linked tables. The...

0 votes

How can we help you today?

JackAce

Comments