It would be nice to have a report that shows what each user has checked out, grouped by project.

Also some use reports would be nice. Like what objects were changed in the last week and how they were changed.

We are really getting into using you software. The more I use it the more I like it.

Hi,

at the moment the only available report is a Difference Report that shows you which objects are different between source control and the SQL Server.  This can be ran against a server or a database.

You could use the difference report to view which objects are checked out but, because it's looking at the differences, it will take a bit longer than a report specifically designed for that.  And of course, you'd only be able to see which objects where checked out on a given server rather than across a project.

We'll have a look at this and see if we can include some reports for the next release.

Regards,

---

SQLDBcontrol Support

support@sqldbcontrol.com

I am a developer after all. Perhaps you could create a few views that won't change across version that end users could generate their own reports from?

Absolutely.  In fact, you more than welcome to query the DatabaseObject and ObjectHistory table yourself, which is where you'll find most of the information you need.

 

---

SQLDBcontrol Support

support@sqldbcontrol.com

Can you tell me how I would get the project name into the following query? SELECT o.CheckedOutBy, o.LastUpdated , o.UpdatedBy ,o.ServerName, o.DatabaseName, o.ObjOwner, o.ObjName FROM dbo.DatabaseObject o WHERE (CheckedOut = 1 OR o.CheckedOutBy IS NOT NULL) AND o.IsPrivate <> 1 ORDER BY o.CheckedOutBy, o.LastUpdated

Sure,

that would be:

SELECT o.CheckedOutBy, o.LastUpdated , o.UpdatedBy ,o.ServerName, o.DatabaseName, o.ObjOwner, o.ObjName, ps.ProjectName

FROM dbo.DatabaseObject o

join dbo.ProjectServerDBObject psdo on psdo.ObjectId = o.ObjectId

join dbo.ProjectServerDatabase psd on psd.ServerDbId = psdo.ServerDbId

join dbo.ProjectServer ps on ps.ProjectServerId = psd.ProjectServerId

WHERE (CheckedOut = 1 OR o.CheckedOutBy IS NOT NULL) AND o.IsPrivate <> 1 ORDER BY o.CheckedOutBy, o.LastUpdated

---

SQLDBcontrol Support

support@sqldbcontrol.com

Exactly what I needed. Thanks!

We took our queries and turned them into SSRS reports. It worked out very well for our needs. There is one other thing though. I haven't looked too far into this because I don't know if the data is persisted. Can I write a query that shows items that exist on the server or are different on the server so I can see if action needs to be taken without having to open each project? Thanks!

Unfortunately this isn't possible.  The only way to identify objects that are different on the server is by retrieving the definition for each object from the corresponding server and performing a comparison, which is written into the code of the application.

We could potentially extend the Comparison Report functionality such that it performs the comparison at the project level rather than the server level.  The only problem I forsee with this is the amount of time it would take for the report to generate, which would depend on how many servers and databases needed to be checked.

Theoretically, if you could set up linked servers from your source control server to the other servers, it may be possible to create a query that pulls data from the sys.sql_modules table on each database into a temporary table in the source control database.  You would then potentially be able to join this table to the DatabaseObject table and figure out which objects are different and/or don't exist in source control.

That's just an idea though and would involve a bit more thought I imagine.

I'll see if we can come up with something that works from within the application.

---

SQLDBcontrol Support

support@sqldbcontrol.com

That does sound like more work than just reviewing the projects weekly, which is what we have scheduled to do now. Thanks for getting back with me though!