SQL Server

Yes well obvious to some but then you google the above and you get half a million results. That's a lot of reading so here's the simple fix for my issue:

Returning an error:

LTRIM(RTRIM(rm.Name + ', ' + rm.[Zone] + ', ' + rm.[Capacity] + ', ' + rm.[Type] + ', ' + rm.[DeptId])) AS RoomDetails

// yields Conversion failed when converting the varchar value ', ' to data type int

My issue was silly T-SQL concatenation which requires plus signs and thinks it's super clever when it tries to add a number and a string together; then remembers it's a Microsoft by-product and realises it can't.

Solved by checking the datatypes for each field in the concatenation string:

LTRIM(RTRIM(rm.Name + ', ' + rm.[Zone] + ', ' + CONVERT(VARCHAR(4), rm.[Capacity]) + ', ' + rm.[Type] + ', ' + rm.[DeptId])) AS RoomDetails

// success! added CONVERT(VARCHAR(4) to the room capacity which was of datatype int. 

My setup

• Windows XP SP3
• MS SQL Server Management Studio 2008
• SQL Server 2008 R2
• Regional Setting: London BST (British Summer Time)

Tuesday the 11th of October 2011 at 06:00am:

CONVERT(CHAR(19), CURRENT_TIMESTAMP, 0)		Oct 11 2011 6:00AM
CONVERT(CHAR(8), CURRENT_TIMESTAMP, 1)		10/11/11
CONVERT(CHAR(8), CURRENT_TIMESTAMP, 2)		11.10.11
CONVERT(CHAR(8), CURRENT_TIMESTAMP, 3)		11/10/11
CONVERT(CHAR(8), CURRENT_TIMESTAMP, 4)		11.10.11
CONVERT(CHAR(8), CURRENT_TIMESTAMP, 5)		11-10-11
CONVERT(CHAR(9), CURRENT_TIMESTAMP, 6)		11 Oct 11
CONVERT(CHAR(10), CURRENT_TIMESTAMP, 7)		Oct 11, 11
CONVERT(CHAR(8), CURRENT_TIMESTAMP, 8)		06:00:00
CONVERT(CHAR(26), CURRENT_TIMESTAMP, 9)		Oct 11 2011 6:00:00.000AM
CONVERT(CHAR(8), CURRENT_TIMESTAMP, 10)		10-11-11
CONVERT(CHAR(8), CURRENT_TIMESTAMP, 11)		11/10/11
CONVERT(CHAR(6), CURRENT_TIMESTAMP, 12)		111011
CONVERT(CHAR(24), CURRENT_TIMESTAMP, 13)	11 Oct 2011 06:00:00.000
CONVERT(CHAR(12), CURRENT_TIMESTAMP, 14)	06:00:00:000
CONVERT(CHAR(19), CURRENT_TIMESTAMP, 20)	2011-10-11 06:00:00
CONVERT(CHAR(23), CURRENT_TIMESTAMP, 21)	2011-10-11 06:00:00.000
CONVERT(CHAR(20), CURRENT_TIMESTAMP, 22)	10/11/11 6:00:00 AM
CONVERT(CHAR(10), CURRENT_TIMESTAMP, 23)	2011-10-11
CONVERT(CHAR(8), CURRENT_TIMESTAMP, 24)		06:00:00
CONVERT(CHAR(23), CURRENT_TIMESTAMP, 25)	2011-10-11 06:00:00.000
CONVERT(CHAR(19), CURRENT_TIMESTAMP, 100)	Oct 11 2011 6:00AM
CONVERT(CHAR(10), CURRENT_TIMESTAMP, 101)	10/11/2011
CONVERT(CHAR(10), CURRENT_TIMESTAMP, 103)	11/10/2011
CONVERT(CHAR(10), CURRENT_TIMESTAMP, 104)	11.10.2011
CONVERT(CHAR(10), CURRENT_TIMESTAMP, 105)	11-10-2011
CONVERT(CHAR(11), CURRENT_TIMESTAMP, 106)	11 Oct 2011
CONVERT(CHAR(12), CURRENT_TIMESTAMP, 107)	Oct 11, 2011
CONVERT(CHAR(8), CURRENT_TIMESTAMP, 108)	06:00:00
CONVERT(CHAR(26), CURRENT_TIMESTAMP, 109)	Oct 11 2011 6:00:00:000AM
CONVERT(CHAR(10), CURRENT_TIMESTAMP, 110)	10-11-2011
CONVERT(CHAR(10), CURRENT_TIMESTAMP, 111)	2011/10/11
CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112)	20111011
CONVERT(CHAR(24), CURRENT_TIMESTAMP, 113)	11 Oct 2011 06:00:00:000
CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114)	06:00:00:000
CONVERT(CHAR(19), CURRENT_TIMESTAMP, 120)	2011-10-11 06:00:00:000
CONVERT(CHAR(23), CURRENT_TIMESTAMP, 121)	2011-10-11 06:00:00.000
CONVERT(CHAR(19), CURRENT_TIMESTAMP, 126)	2011-10-11T06:00:00
CONVERT(CHAR(19), CURRENT_TIMESTAMP, 127)	2011-10-11T06:00:00
CONVERT(CHAR(32), CURRENT_TIMESTAMP, 130)	14 ?? ?????? 1432 06:00:00:000AM
CONVERT(CHAR(25), CURRENT_TIMESTAMP, 131)	14/11/1432 06:00:00:000AM

I originally got a list from databases.aspfaq.com but the convert numbers didn't correspond to the output formats. This is my list created within my environment and setup (see above).

Thought I'd put a note on this error. Bearing in mind that this is a general data type error and not just because I tried to convert a date in SQL format to a Month name.

An error occurred during local report processing.
An error has occurred during report processing.
The Group expression used in grouping 'table1_month' returned a data type that is not valid.

Well I tried the SQL Server Management Studio solution to connect to a MySQL database then lost my way wondering what was I trying to achieve?

I've written this article because this is how I connected to a MySQL database from within the Business Intelligence Development Studio (BIDS) IDE from Microsoft.

Required:

• Windows XP (ODBC Data Source Administrator)
• MS Business Intelligence Development Studio 2008
• Test/Sample MySQL Database to connect to (server and username + password).
• Admin Access to the reporting server if you plan on deploying the report to it.

Scenario:

• We want to report on a MySQL database
• MySQL login information is a user who exists as a MySQL user and a server user.
• Reporting server is remote as well as the MySQL database.
• BIDS is on the client machine.