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. Remember, the code is a interpretive language, its designed to be gateway between the machines and humans, if you write something complex your more then likely need to be include more comments explain what it does or look at refactoring so other developers can continue your work.

The few options are:

Double dash (–)

— Single line quote

Single Hash (#)

# Single line quote

Double forward-slash (//)

// string domain = “matt40k.uk”;

All of these are single line quotes, as in each line needs the require comment and the comment continues till the end of the line \ carriage break.

You also got:

Forward-slash and asterisk (/*)

/* Multi-Line
quote */

Forward-slash and asterisk marks (/*) the start of the comment, the comment continues until the asterisk marks and Backward-slash (*/). The code must close any open Forward-slash and asterisk marks (/*) comments in order to compile.

With Forward-slash and asterisk, you can comment in-line, for example

Select * from /* Change the code to the right */ sys.tables

This is valid T-SQL that will return the all the columns for meta data about tables on the selected database

You can also use this across multiple lines, for example

select

TableSchema = schema_name(schema_id)

,TableName = name

/*
You could also add

other tables

 

*/
from

sys.tables

 

Power Query Formula (M) Language

Double dash (–) No
Single hash (#) Yes
Double forward-slash (//) No
Forward-slash and asterisk (/*) Yes

Transact-SQL (TSQL)

Double dash (–) Yes
Single hash (#) No
Double forward-slash (//) No
Forward-slash and asterisk (/*) Yes

C sharp (C#)

Double dash (–) No
Single hash (#) No
Double forward-slash (//) Yes
Forward-slash and asterisk (/*) Yes

 

Fun fact, my most commonly used comment?

To-Do

SQLPrompt

So one of the really nice sponsors at SQL Saturday had a vending machine that was rigged up to release a treat when you sent a tweet. Like so

and give you…

In mine had a SQL Prompt license. At first I was a bit disappointed, I hoping for SQL Test license or a full blown SQL toolbelt but the more I’ve been using it, the more I’m loving it.

Like the IntelliSense… or the fact press table next to the * expands it out into the list of columns

sqlprompt_1

The fact it re-opens the tables you had open the last time you were in SQL Management Studio, that it has a history. I’d like to say I never close the wrong window, especially when I haven’t saved them, but I do. Luckily with SQL Prompt it allows me reopen it.

sqlprompt_2

It even stops DBAs going to jail :p

 

 

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.

Doing so its pretty easy, I already have a my database project setup in Visual Studio, all I then did was right-click then Schema Compare…

The app will then open and you select, from the right hand menu, the target, the database you want to compare your project with. Then you just click the compare button.

This appeared to work but didn’t display any results. When you look at the bottom status bar it reads

Comparison complete. No differences detected. Restricted comparison. See Error List for details.

I knew there we at least some differences. I then click on the Error List table below it which revealed

The reverse engineering operation cannot continue because you do not have View Definition permission on the ‘Warehouse’

Googling the problem lead me to a number of recommendations, some didn’t work (like restricting it to only tables), others I didn’t want to do – I don’t want to give myself db_owner on production. The final solution came as a simple one liner

GRANT VIEW Definition TO [DOMAIN\user]

Just added it to my Database Project as Post-Deployment script and its now comparing ok within Visual Studio (SSDT).

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 Helper has 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 GitHub is that it supports GeoJSON and automatically renders it

With the mapping data already imported into Geography in SQL Server it was easy enough to convert to GeoJSON (Stackoverflow to the rescue again!)

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. This is especially a problem when you have a form that has drop-down options (luckily stored in a different table more efficiently) which generates an amount which the end user can overtype in order to moderate it up or down, which has zero validation.

The data is “stored” as numeric in the database so, for example, 1200.34, but is formatted as currency – so £1,200.34. The problem occurs when the user overtypes the amount, when they do, they overtype it, say as, 1201.34, but they don’t enter 1201.34. They enter £1201.34. Now this is a problem as when I load the data into the Data Mart, I store the data as a numeric(18,2), which means I need to cast it. This will of course fail if the user has overtyped it as it isn’t a numeric – which has historically happened. The way I resolved it was to strip out the £ sign using a replace then to add a ISNUMERIC statement as a fail safe.

However despite my failsafe it failed today – the problem was with ISNUMERIC – if you read the man, it says “ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($)”. What it doesn’t tell you is it also covers commas – so:
select cast(replace('£1,220', '£', '') as int)
will fail with
Conversion failed when converting the varchar value '1,220' to data type int.
this is despite ISNUMERIC returning 1 (ie valid)
select ISNUMERIC('1,220')

The fix is to replace (well remove) commas as well as the pound sign (£). Going forward, in SQL2012 Microsoft has introduced Try_Cast which might be another option.

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.

After limiting it, the database still didn’t reduce the database size. A quick Google found someone saying that it is because it can’t handle over 1 million rows of old reports, which makes sense, running a SQL command that would delete over 30gb of data would have insane log file grow as well as massive performance problems.

The steps to resolve this, on MS-SQL, was to:

  1. Stop the Business Objects services on the app tier
  2. Ensure you have a backup of the database
  3. Set the recovery model to simple if it isn’t already
  4. Copy out all the required rows
  5. Truncate the table
  6. Re-insert the required rows back into the table
  7. Check everything looks ok, deal with the fact the database is now tiny
  8. Create another backup
  9. Start the app tier backup

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. A quick Google revealed:

http://www.sqlhammer.com/blog/creating-logins-and-users-why-i-cant-connect/

Bingo. Appears I need to add the following to the end of the script:

 GRANT CONNECT TO [newuser]