The ReportServer Database
View: ExecutionLog
| InstanceName | nvarchar(38) | NOT NULL | Name of the report server instance that handled the request. Usually YOURSERVERNAME\MSSQLSERVER |
| ReportID | uniqueidentifier | NULL | The ID of the report (looks like a hexadecimal SSID). It's the unique ID of the report but not unique in the table (can be referenced many times). |
| UserName | nvarchar(260) | NULL | Windows authenticated username and domain of the person running the report (eg. MYDOMAIN\myusername) |
| RequestType | bit | NOT NULL | User or System. Can be 1 or 0. This was zero "0" when I would run a report as a user. |
| Format | nvarchar(26) | NULL | This is the rendering format. Mostly RPL if viewed in MS Internet Explorer. |
| Parameters | ntext | NULL | Parameters and the values they were submitted with. |
| TimeStart | datetime | NOT NULL | Time report started to run. |
| TimeEnd | datetime | NOT NULL | Time report finished running? Need to check what finished? |
| TimeDataRetrieval | int | NOT NULL | Milliseconds spent retrieving the data. |
| TimeProcessing | int | NOT NULL | Milliseconds spent processing the report. |
| TimeRendering | int | NOT NULL | Milliseconds spent rendering the report. |
| Source | int | NOT NULL | Source of the report exection (1=Live, 2=Cache, 3=Snapshot, 4=History) |
| Status | nvarchar(32) | NOT NULL | either rsSuccess or an error code; if multiple errors occur, only the first error is recorded |
| ByteCount | bigint | NOT NULL | Size of rendered reports in bytes. |
| RowCount | bigint | NOT NULL | Number of rows returned from queries. |
Conversion failed when converting varchar to int
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
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.
T-SQL DateTimestamps
- Windows XP SP3
- MS SQL Server Management Studio 2008
- SQL Server 2008 R2
- Regional Setting: London BST (British Summer Time)
Returned a data type that is not valid
- 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.
Accessing a MySQL Database with Business Intelligence Development Studio
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.
- 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.