If you want to create a TFS query that would identify work items that have changed, but were not changed by the person working it, there is a nifty way to do this.The filtering field can be set to <> another field that is available, but the syntax/setup in Visual Studio is not intuitive. It’s in the dropdown list, but I’d never noticed it before!

the problem

An issue with SQL Server 2012 SP3 was identified that impacted EF4/5 due to additional datatypes in the dll.

System.EntryPointNotFoundException: Unable to find an entry point named 'SetClrFeatureSwitchMap' in DLL 'SqlServerSpatial110.dll'

diagnosing

To easily identify the available dll versions of sql server, I ran a quick adhoc bat file.

The output returns a simple text file like this:

Ran into an issue with the “Preview New Reporting Portal” link on a fresh install of 2016 giving me a not found error.

Changing the virtual directory in the Report URL tab for SSRS configuration fixed this invalid link. In my case, I …

Quick snippet I threw together to help with failing over synchronized databases to the other server in bulk. No way I want to click that darn Fail-over button repeatedly. This scripts the statements to print (i commented out the exec portion) so that you can preview the results and run manually.Note that it’s also useful to have a way to do this as leaving databases running on the mirror server for an indefinite period can violate licensing terms on the secondary server when it’s a …

This turned out to be quite a challenge. I couldn’t find anything that made this very clean and straight forward to calculate, and in my case I was trying to gauge how many mirroring databases I could run on a server.In my scenario, I wasn’t running Expensive Edition (@BrentO coined this wonderful phrase), so was looking for the best way to assess numbers by doing mirroring on a large number of databases, in my case > 300 eventually. The documentation was… well…. a …

Have been working through the fantastic training resources from Brent Ozar’s Everything Bundle and on the recommended resources they mention after all the training on log shipping and availability groups that you can use a lab environment from TechNet to actually get going on familiarizing yourself with the technology more.

This is great! Of course, it’s not the full deal, but this gives a tangible way to get moving on familiarizing yourself with this complex technology. TechNet …

Diff all files

Continuation of some notes regarding the excellent content by Paul Randal in Pluralsight: SQL Server: Logging, Recovery, and the Transaction Log. Please consider supporting his excellent material by using Pluralsight and subscribing to his blog. He’s contributed a vast amount to the SQL server community through SQLSkills This is my absorbing of key elements that I never had worked through

Jackalopes Are Real….so are Virtual Log Files

Ever seen a picture of a jackalope? Image by …

Just recently discovered this artist after being exposed to an article from Worship Leader magazine. Fantastic dynamics. The dynamics and beautiful harmonies are pretty darn close to what I’d love experimenting with if I had a group of folks playing those instruments. Interestingly, the vocal harmonies are very simple, but I found very beautiful. It’s compromised of a tenor and a baritone range, and is a fresh change stuff like Shane and Shane which both have incredibly high ranges. …

Ran into a few issues verifying instant file initialization. I was trying to ensure that file initialization was enabled, but found out that running the xp_cmd to execute whoami /priv could be inaccurate when I’m not running it from the account that has the privileges. This means that if my sql service account has different permissions than I do, I could get the incorrect reading on if it is enabled.

Paul Randal covers a second approach using the sysinternals tool Accesschk, which seems …

Continuation of some notes regarding the excellent content by Paul Randal in Pluralsight: SQL Server: Logging, Recovery, and the Transaction Log. Please consider supporting his excellent material by using Pluralsight and subscribing to his blog. He’s contributed a vast amount to the SQL server community through SQLSkills

Transaction Log File

• The initial size of the log file is the larger of 0.58 MB or 25% of the total data files specified in the create database statement. For example, …

Logging & Recovery Notes from SQL Server Logging, Recovery, and the Transaction Log (pluralsight) Paul Randal Going to share some key points I’ve found helpful from taking the course by Paul Randal at pluralsight. I’ve found his knowledge and in detail presentation extremely helpful, and a great way to expand my expertise. Highly recommend pluralsight.com as well as anything by Paul Randal (just be prepared to have your brain turned to mush… he’s definitely not …

Ran into an issue where a developer was trying to create a database and was denied due to no ability to obtain exclusive lock on model. After verifying with other blogs, I found that creating a database required exclusive lock to use model as a template for the new database creation.

In my case I had connected with SSMS directly to model for some queries instead of master. In this case, SQL Complete (Devarts’s excellent alternative to SQL Prompt) was querying the schema had this open …

Don’t get too excited. I know you love documentation, and just can’t wait to spend some time digging in to document your database thoroughly. I imagine you probably want to build visio charts manually, or draw whiteboard diagrams by hand and take pictures.

For the rest of us that are lazy, a tool to help document your database is a great idea. I’m a big fan of SQL Doc by Red Gate, and ApexSQL Doc . I ended up using ApexSQL doc to document the database at my work, though Red …

Not quite fineprint, but sure feels like it!

Quotename can be a pretty cool function to simplify your dynamic sql, as it can ease some of the escaping of strings. However, I normally use it for table/column names, and so hadn’t ran into a “gotcha” of this function until today. It’s limited to 128 characters, and if you pass in greater than 128 characters will yield a null. Yep… you could be trying to track down that error for a null string somewhere in your …

XML handling with aliases

I find it interesting the difference in behavior with querying XML between column assignment, and quoted alias naming. It’s a generally understood best practice to not use the deprecated syntax of column aliasing using a quoted name. For example:

1
2
3
4
5
6

select
    [escapedWithBracketsIsGood] = case when raining then cats else dogs end
    ,NoEscapeNeededPerGoodNamingPractices = case when rains then pours else friday end
    ,case when writtenThisWay …

My preview post showed a demonstration of the multi-cursor editing power of Sublime Text 3 when speeding up your coding with SQL server.There is a pretty straight forward way to setup sublime (or one of your preferred text editors) to open the same file you are editing in SQL Management Studio without much hassle. I find this helpful when the type of editing might benefit from some of the fantastic functionality in Sublime.

External Tool Menu

Go to Tools > External Tools

• Yes… multicursor editing is epic
• No… SSMS doesn’t support multi-cursor editing the way it should.
• Yes… you can do some basic editing with multiple lines using alt-shift
• No… it doesn’t come close to what you can do with Sublime. Cool thing is you can open text in Sublime synced w/SSMS cursor position and switch between the two with a shortcut. That will be a post for another day, I’m just telling you now to get your appetite going. If you can’t …

It was entertaining to listen to a technical wizard fighting the obsession with waiting to eat chocolate with 3k viewers watching. Kendra wore about 4-5 cowboy hats in an effort to help those of us who wear many hats feel welcome…. Now that’s the kinda of training I enjoy! No pretense, just honest real, and insightful training with enjoyable humor included for free

Highly recommend attending the webex presentations occurring today and tomorrow with Brent Ozar and his amazing team. …

Cheap ain’t easy

There’s a lot of tools out there, and very few that are polished, have a good UI, and some reasonable functionality to help monitoring, that don’t cost an arm and a leg. One such tool I’ve recently begun to appreciate is MiniDBA . I was generously provided with a license to evaluate this and continue testing, and have recently had an actual chance to start using it more in my environment. The cost for MiniDBA is one of the most affordable I’ve …

I ran into an issue with errors with an database upgrade running into a violation of a foreign key constraint. Don’t know how it happened. Figured that while I’m at it, I’d go ahead and evaluate every single check constraint in the database to see if I could identify any other violations, because they shouldn’t be happening.

improve the execution plan by checking the data

In my reading, I found out that checking the constraints can enable the constraint to be marked …

Looks like the rapid release channel now has a great feature for locking database objects that you are working on. Having worked in a shared environment before, this could have been a major help. It’s like the poor man’s version of checking an object out in visual studio except on database objects! With multiple developers working in a shared environment, this might help reduce conflicting multiple changes on the same object.

Note that this doesn’t look to evaluate dependency …

Qure Analyzer Benchmark Testing

Have never really played around with the option: SET NOEXEC ON Turns out this can be a helpful way to validate larger batch scripts before actually making changes, to ensure compilation happens. If you choose, you can verify syntax by “parsing” in SSMS. However, this doesn’t compile. Compilation checks more than your syntax. It actually validates the objects referenced exist.

The execution of statements in SQL Server has two phases: compilation and execution. This setting is …

out of memory, argggh!

Am I the only one that has experienced the various out of memory issues with SSMS? Not according to google!

lovingly crafted in the forges of.. well … dbforge

I’ve a huge fan of Devarts products. I’ve done a review in the past on their SQL Complete addin, which is the single most used tool in my SQL arsenal. It vanquishes nasty unformatted code into a standard lined up format I can easily read. The 100’s of options to customize the …

Working in a development environment, I botched up a restore. After this restore attempt to overwrite my database with the previous version, I had it stuck in single-user mode.

SSMS provided me with helpful messages such as this: Changes to the state or options of database 'PoorDb' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it. Additionally, I was told I was the deadlock victim when attempting to set the user mode back to multi-user. …

Was testing a batch file to add a user to a localdb instance. Assumed that my user as admin on the machine wouldn’t have an issue inserting myself back…. didn’t think that one through too carefully. Executing any type of SQLCMD against it denied me. SSMS denied me. No SA had been setup on it, so I couldn’t login as SA either. Looked for various solutions, and ended up uninstalling and reinstalling (localdb)v11.0 so that I’d stop having myself denied permissions. …

XML is a beast if you’ve never tackled it. Here are some simple examples of what I discovered as I experimented and walked through obtaining values out of a XML column.

Found a couple good walkthroughs on enabling instant file initialization. However, I’m becoming more familar with the nuances of various setups and found it confusing in trying to map the correct user/group to enable this option. In my case, I had the SQL Service running under NT SERVICE/MSSSQLSERVER and as such this logic wasn’t showing up when trying to find groups/users to add to the necessary permissions. Lo and behold…

I typed it in manually and it worked. If time permits …

Couldn’t find documentation showing that upgrade from SQL 2014 evaluation version was possible to developer edition. I just successfully converted an evaluation version to developer edition.

1. Obtain the key for the developer edition (in my case I had to download the .ISO from MSDN downloads, and go through the installation wizard to get it)
2. Run the installation center app for SQL Server 2014
3. Select edition upgrade
4. Plug in your new serial from the developer edition. Pretty simply, but …

I was dealing with a challenging dynamic sql procedure that allowed a .NET app to pass in a list of columns and a view name, and it would generate a select statement from this view. Due to requirements at the time, I needed the flexibility of the “MAIN” proc which generated a dynamic select statement, while overriding certain requested views by executing a stored proc instead of the dynamic sql.

During this, I started looking into the string parsing being completed for a comma …