Database - Joel Lipman .Com

SQL Calendar in Business Intelligence Development Studio

: Joel Lipman; Transact-SQL; 14 January 2011; Hits: 14102

So I find myself searching the web for a way of producing an outlook-style calendar within Business Intelligence Development Studio (BIDS). Weekdays along the top and then dates inside. Additionally we would like this linked to events in a database.

I'm calling this project "Reinventing the Wheel" because the request was a report that was similar to an MS Outlook calendar which will display any room bookings for that month. This is "Reinventing" because we used to use MS Outlook to store these kind of things until the company implemented a software to deal with room bookings and said we shouldn't use Outlook for this...

: Joel Lipman; Transact-SQL; 11 January 2011; Hits: 22784

Thought I'd add a note as I was getting confused with the built-in function "FormatDateTime()". The example is shown as:

copy raw

=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)

=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)

The other formats are:

copy raw

=FormatDateTime(Fields!BirthDate.Value, DateFormat.GeneralDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.LongDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.LongTime)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortTime)

=FormatDateTime(Fields!BirthDate.Value, DateFormat.GeneralDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.LongDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.LongTime)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortTime)

Unfortunately if you are using US dates and want the report to use a specific European date format and you spend as long as I did searching the web for a solution, then ignore all the above.

Another built-in function is the text-formatter

Category: Transact-SQL :: Article: 317

: Joel Lipman; MySQL; 11 January 2011; Hits: 14431

In standard SQL we can combine the contents of two tables with a CROSS JOIN, (BTW these are not instructions to create some table I'll never use again unlike the rest of the solutions I found on the web). In MySQL, I only know how to do this with a UNION ALL clause.

Scenario:
I have a Joomla module which should take the keywords from a specified number of different but similar tables. I'm looking to query the Title and the Introductions of any valid articles from BOTH tables and return one table with everything I want.

: Joel Lipman; Transact-SQL; 06 January 2011; Hits: 15120

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.

copy raw

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.

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.

Category: Transact-SQL :: Article: 313

: Joel Lipman; MySQL; 05 January 2011; Hits: 17646

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.

: Joel Lipman; MySQL; 13 December 2010; Hits: 6222

Basically, I've started using so much more SQL in our new Business Intelligence projects that I've been revising all my scripts to see what SQL I can optimize.

One of my systems is a MediaWiki CMS which is used for the official Bournemouth University Knowledge Base. The skin itself is the index page loaded for any page in the Wiki system. It logs the IP address (VisitorIP), the URL (VisitorURL) entered via the browser useragent (VisitorUAgent), the User ID (VisitorID, 0 if not logged in) and of course the Timestamp (DateTimeStamp).

: Joel Lipman; Transact-SQL; 19 November 2010; Hits: 10065

Source: http://www.kodyaz.com/articles/sql-format-date-format-datetime-t-sql-convert-function.aspx

Create Read-Only Database User in SQL Server

: Joel Lipman; Databases; 09 November 2010; Hits: 69086

The following describes how to setup a database user with read-only access to the AdventureWorks database.

Using SQL Server Management Studio 2008:

Connect to your database server.
Expand Security > Logins.
Right-click on the user who will be set as having read-only access (in this example "adventureworksro").
Select Properties.
Select User Mapping.
Map the login to the database they will have access to.
Tick the boxes for role membership next to public and db_datareader.
Confirm by clicking OK.

You should get something like the following:

: Joel Lipman; MySQL; 28 October 2010; Hits: 6173

The scenario is that I wanted a PHP/MySQL extension created which needs to launch a query to find all columns across the tables of the local database which had valid content to extract keywords from.

The following is a MYSQL query that displays the structure of all the columns in all the databases of the localhost:

copy raw

SELECT * FROM information_schema.COLUMNS ORDER BY TABLE_NAME, COLUMN_NAME

SELECT * FROM information_schema.COLUMNS ORDER BY TABLE_NAME, COLUMN_NAME

The following is a MYSQL query that finds all columns (displayed as "tablename.columnname") that had the data_type TEXT across all databases:

copy raw

SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) AS value FROM information_schema.COLUMNS WHERE DATA_TYPE='text' ORDER BY TABLE_NAME, COLUMN_NAME

SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) AS value FROM information_schema.COLUMNS WHERE DATA_TYPE='text' ORDER BY TABLE_NAME, COLUMN_NAME

The following is a MYSQL query that finds all columns that had the data_type TEXT across a specified database (eg. sample_db):

copy raw

SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) AS value FROM information_schema.COLUMNS WHERE DATA_TYPE='text' AND TABLE_SCHEMA='sample_db' ORDER BY TABLE_NAME, COLUMN_NAME

SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) AS value FROM information_schema.COLUMNS WHERE DATA_TYPE='text' AND TABLE_SCHEMA='sample_db' ORDER BY TABLE_NAME, COLUMN_NAME

Note that the user launching this SQL query would need the SELECT privilege as a minimum on "information_schema" (arguable).

Category: MySQL :: Article: 305

MySQL Oracle equivalents

: Joel Lipman; Databases; 27 September 2010; Hits: 6826

The title of this article implies something rather odd and upcoming considering that Sun Microsystems bought MySQL and Oracle bought Sun. But in fact, this is just a quick list of some regular commands in MySQL that I need in Oracle:

: Joel Lipman; Transact-SQL; 13 July 2010; Hits: 13186

Well I find myself again the dummie of the Internet. I basically came across this error and STFW'd for ages following complex T-SQL Timestamp conversions but to no avail. Not saying that you shouldn't try their solutions but just check you haven't done this silly mistake.

I'm only guessing the same error would happen in Report Builder 2.0 which I've stopped using as my day job wants us to use Business Intelligence Development Studio for all our SQL Server Analysis Service (SSAS) and Reporting Service (SSRS) projects/solutions. The features and interface are very similar when developing reports though.

The error I'd get was

An error occurred during local report processing.
An error has occurred during report processing.
Cannot read the next data row for the dataset DataSet1.
Conversion failed when converting date and/or time from character string.

: Joel Lipman; MySQL; 17 March 2010; Hits: 5755

Ok is it just me who does everything slowly and badly until someone comes along and says why are you doing it like that? My justification is that the job has to be done no matter what.

If you've ever sat there with phpMyAdmin or a MySQL Administration Tool (like Navicat or SQLYog), and still there at night editing each field so that it displays correctly... well that's usually me.

Found it a bit boring but here's a short bit of code to speed it up which I now use:

copy raw

update table_name set field_name=REPLACE(field_name,'string_to_find','string_to_replace');

update table_name set field_name=REPLACE(field_name,'string_to_find','string_to_replace');

Source: http://www.mediacollege.com/computer/database/mysql/find-replace.html

Category: MySQL :: Article: 248

SQL Calendar in Business Intelligence Development Studio

Formatting a date in an MDX query

Merge the content of two similar tables in MySQL

Returned a data type that is not valid

Accessing a MySQL Database with Business Intelligence Development Studio

SQL Queries for Statistics

Transact-SQL Date Format Conversions

Create Read-Only Database User in SQL Server

MySQL Commands to display all columns

MySQL Oracle equivalents

Error during processing - Converting Date/Time

Search and Replace in a MySQL Database

MySQL

Oracle PL/SQL

Transact-SQL

Credit where Credit is Due:

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support