T-SQL

Comments

One of the annoying things about working with multiple languages is they each have their differences – obviously, otherwise there would only be 1 language! Although there is good reason to have these differences they still have elements that are a pain. One of the most annoying things, other then “do you require a semicolon at the end of the statement?” is comments. Comments aren’t executed or complied but they help developers read code.

SQLPrompt

So oneof the really nice sponsorsat SQL Saturdayhad a vending machine that was rigged up to release a treat when you sent a tweet. Like so I'm at #sqlsatcambridge home of @redgate #redgatetreat #red668 — Matt Smith (@matt40k) September 10, 2016 and give you… In mine had a SQL Promptlicense. At first I was a bit disappointed, I hoping for SQL Testlicense or a full blown SQL toolbeltbut the more I’ve been using it, the more I’m loving it.

SQL Compare failed

Another day, another problem. Todays problem was incorrect column length which was causing a SSIS package to fail. Historically I use a freeware tool to compare the different environments schema, but lately I just don’t bother, I have continuous deployment so its easier just to hit the deploy button then it is to try and figure it out. Well today I decided I wanted to it old school – although I’ve been using the SSDT SQL compare in my deployments, I’ve not actually used it in terms of viewing the differences.

Auditing – SSAS

There are various types of Auditing in the Microsoft BI stack. There is auditing in SSRS, SharePoint, SSAS and not forgetting SQL has its own auditing. Today I am looking at the SSAS auditing – you can find out more about it on TechNet. Olaf Helperhas published some TSQL code for querying the audit data -on the Script Center. But first, we need a table to store the data, here is a TSQL script for creating the table:

GeoJSON

So I’ve been playing wit mapping data, one of the nice things about GitHubis that it supports GeoJSON and automatically renders it With the mapping data already imported into Geographyin SQL Server it was easy enough to convert to GeoJSON (Stackoverflowto the rescue again!)

Getting collation descriptions

Just an updateas I noticed I forgot to say how to get the names and descriptions of the collations your SQL Server supports

Problem with ISNUMERIC

Todays problem was brought to you by a highly customizable system with poor user validation and a user who doesn’t know the difference between data and formatting. First the system. The supplier has created a highly user customizable system, they can for example create a web form with zero coding – 100% user config. The problem is, technically, it creates a mess. So your nice new form with a number of questions stores all its answers in a single table with the data going into a nvarchar(max) column – which is fine for free-text columns, but not so good for integer fields.

Swollen Business Objects database

Our new HR system uses SAP Business Objects for transactional reporting, as this was setup by our HR supplier we haven’t really got involved with it beyond writing a few custom reports, turns out we’ve written quite a few, so much so that the Business Objects CMS database has swollen to over 30 GBs. This, as you might have guessed, is causing a few performance problems. After a discussion with the supplier, we found out it was storing all the reports, even the ad-hoc one-off reports and we should have set a parameter to limit it.

Created new user is disabled

I’ve created a new SQL user along with a new role in our warehouse db project using Visual Studio 2012. CREATE LOGIN [newuser] WITHPASSWORD=’{{ RANDOM PASSWORD }}’,DEFAULT_DATABASE=[Database] GO CREATE USER [newuser] FORLOGIN [newuser] WITHDEFAULT_SCHEMA=[dbo] GO ALTER ROLE [newrole] ADD MEMBER [newuser] GO  Problem is, the user is disabled. When you do it manually via SQL Management Studio, it’s enabled and working. Manually enabling the account doesn’t allow it connect either.