Entries by Neeraj Jindal

Stored Procedure QUOTED IDENTIFIER, ANSI NULLS and NOCOUNT

While working on stored procedures in SQL Server I came across a few keywords which are not commonly used while writing queries: QUOTED IDENTIFIER ON/OFF, ANSI NULLS ON/OFF and NOCOUNT ON/OFF. I had created a new stored procedure (by right clicking on Stored Procedures folder in left pane of SSMS and then “New Stored Procedure”). […]

Recursive Queries using Common Table Expression

Problem Statement Part 1: What is the best approach to find out Employee-Manager hierarchy using a given sample table of employees. On the first thought, best is to use self join. Problem Statement Part 2: But in case the level of hierarchy is not available and it has to be identified Solution: “Recursive Query”. Here […]

Aggregating Correlated Sub-Queries in SQL Server 2008

While working on a query I was trying to calculate an aggregate function such as SUM() on a correlated subquery and received the following error: Msg 130, Level 15, State 1, Line 24 Cannot perform an aggregate function on an expression containing an aggregate or a subquery. Here are a couple of solutions you can […]

GROUP BY vs DISTINCT in SQL Server

Yesterday, I was travelling in the metro where I overheard two college students puzzling over the difference between GROUP BY and DISTINCT. GROUP BY and DISTINCT cause a lot of confusion and I couldn’t help myself so I jumped into their conversation and explained the differences. Their problem was understanding where to use GROUP BY and where to use DISTINCT when both return same results in most scenarios when no aggregates are used, and which one had better performance. GROUP BY and DISTINCT both generate the same query execution plan when no aggregates are used, so there is no appreciable difference in performance in this situation. GROUP BY becomes important when aggregates are used. DISTINCT is just for filtering out duplicate records from query result sets. Ideally, when aggregates are not used in a query, then one should use DISTINCT to enhance readability. But when aggregates are used GROUP BY performs much better. Here are some examples: DISTINCT GROUP BY GROUP BY with Aggregate Function

The post GROUP BY vs DISTINCT in SQL Server appeared first on OptimusBI.

GROUP BY vs DISTINCT in SQL Server

Yesterday, I was travelling in the metro where I overheard two college students puzzling over the difference between GROUP BY and DISTINCT. GROUP BY and DISTINCT cause a lot of confusion and I couldn’t help myself so I jumped into their conversation and explained the differences. Their problem was understanding where to use GROUP BY […]

MySQL to Microsoft SQL Server Migration

Need to migrate data from MySQL into Microsoft SQL Server 2005 and later? Here’s how to do it with ODBC. Setup ODBC Connection to MySQL Database I’m going to assume that you already have MySQL and Microsoft SQL Server set up which makes the first step getting the MySQL ODBC Connector. For this article, I downloaded the MySQL ODBC Connector 5.1. The setup of this connector is pretty simple: Open your ODBC Data Source Administrator from the Control Panel -> Administrative Tools. Under the tab labeled as System DSN, press the Add button. On the Create New Data Source dialog that appears, choose MySQL ODBC 5.1 Driver and then press the Finish button. After that, a MySQL connection configuration dialog will appear. Add your MySQL database account information in it, preferably the “root” account which has full access to your databases in MySQL. In this case, my database is called “tigerdb”. Do not change the port to anything other than 3306, unless during your MySQL server installation, you have defined something else. Press the Test button to ensure your connection settings are set properly and then the OK button when you’re done. Create a Microsoft SQL Link to your MySQL Database In this state, you are ready to establish a link towards MySQL database from your Microsoft SQL Server Management Studio. Open a query window and run the following SQL statement: This script will produce a link to your MySQL database through the ODBC connection you just created in the previous stage. The link will appear in the Microsoft SQL Server Management Studio like this: If it doesn’t show up in the treeview, press the refresh button. Import Data between the Databases Create a new database in Microsoft SQL Server. I called mine “testMySQL”. In the query window, run the following SQL statement to import table “users” from the MySQL database tigerdb, into the newly created database in Microsoft SQL called testMySQL. That’s it!

The post MySQL to Microsoft SQL Server Migration appeared first on OptimusBI.

MySQL to Microsoft SQL Server Migration

Need to migrate data from MySQL into Microsoft SQL Server 2005 and later? Here’s how to do it with ODBC. Setup ODBC Connection to MySQL Database I’m going to assume that you already have MySQL and Microsoft SQL Server set up which makes the first step getting the MySQL ODBC Connector. For this article, I downloaded […]

Setting up SMS notification for BI reports

SMS notification isn’t always supported out of the box by BI reporting tools. Luckily, there is a quick hack that we can use with Google Docs to automatically send email notifications from a particular email ID notifying the end user when a new report is ready. This little hack can also be used in other similar situations. You will need to configure the Label, Filter, Google Docs and Google Calendar to set up SMS notification. Lets Start. Creating a Label Login in to your to Gmail account. Click on the gears icon on the top right and select Settings. Go to Labels tab, scroll down a bit and then click Create new label. Make the label name notifyMe and click Create. Now label creation is done. Creating a Filter Click on the Filter tab and then on the Create a new filter link. If you want SMS alerts only for selected emails such as your manager or boss or the automailer that sends your BI reports. Set the filter condition using Fromfield.(Replace email id in below picture with the desired email id)  For SMS alerts for all incoming emails. Set the Has the words field with is:inbox Click on Create filter with this search. Click on Apply the label dropdown and choose label notifyMe. Now click on Create Label and you are done with creating a filter. Creating a Script in Google Drive Login to Google Drive (formerly Google Docs). Click on Create and then on Spreadsheet. Now from the menu bar select Tools-> Script Editor. A popup comes up, just close it. Now erase default code and replace it with the code given below: 5. Now click on Resources-> Current script’s triggers. It will ask for Project name. Type “Notify” and click “OK” 6. After this click on “No trigger set up. Click here to add one now” link as shown below. 7. Now select all 4 things in dropdowns as shown below in image. 8. Click Authorize and then Grant Access. Now click close and save the trigger again and with this Google doc’s script configuration is complete. Google Calendar Configuration Go to Google Calendar. Click on the gears icon on the top right and select Settings. Select the Mobile Setup tab Select Country, enter your phone number and now click Send Verification Code and wait for a text message to be sent to your phone. Once you receive this message, enter the code in the Verification code field and click Finishsetup. Now got to the Calendar tab and click on the Reminders and notifications link. Now under Notifications select SMS as your default notification channel and save it. Google Calendar configuration is now done. Now whenever you receive an email from that particular email id you’ll get SMS notification. You can also modify this script to configure your machine such that whenever somebody open it up you will get SMS notification that somebody has opened your laptop or desktop. For help on this or further queries/help please comment below. Thanks.

The post Setting up SMS notification for BI reports appeared first on OptimusBI.

Setting up SMS notification for BI reports

SMS notification isn’t always supported out of the box by BI reporting tools. Luckily, there is a quick hack that we can use with Google Docs to automatically send email notifications from a particular email ID notifying the end user when a new report is ready. This little hack can also be used in other […]

How to Migrate from MySQL to Microsoft SQL Server

How to migrate from MySQL to Microsoft SQL Server 2005 and later using the MySQL ODBC Connector 5.1. After following these steps you should have a MySQL database fully migrated in to your Microsoft SQL Server environment. ODBC Connection Setup to MySQL Database This article will assume that user have already downloaded and installed the MySQL ODBC Connector. These instructions use MySQL ODBC Connector 5.1. The setup of the MySQL ODBC Connector is quite simple: Go to Control Panel -> Administrative Tools-> Data Sources (ODBC). Under the tab labeled as System DSN, press the Add button. From the dialog box named Create New Data Source, select MySQL ODBC 5.1 Driver and then click on the Finish button. In the MySQL connector/ODBC Data Source Configuration dialog box, add your connection parameters for the MySQL database account that you are migrating including a user name which has full access to your databases in MySQL preferably the “root” account. Do not change the port to anything other than 3306, unless during your MySQL server installation, you have defined something else. This is default port number for MySQL database. In the server option you can also type remote server host name in case your MySQL server is remotely located. You can check that you have the right connection settings by pressing the Test button and then the OK button. Query to link Microsoft SQL Server to MySQL Database (Linked Server) After completing above steps open a query window in MS SQL and run the following SQL statement: EXEC master.dbo.sp_addlinkedserver @server = N’MYSQL’, @srvproduct=N’MySQL’, @provider=N’MSDASQL’, @provstr=N’DRIVER= {MySQL ODBC 5.1 Driver}; SERVER=mysql-server-name; DATABASE=db-name; USER=username; PASSWORD=password; OPTION=3′ Be sure to replace mysql-server-name, db-name, username and password in the above query. This script will produce a link to your MySQL database through the ODBC connection. You can check this linking in the Microsoft SQL Server Management Studio Object Explorer under Server Objects -> Linked Servers -> Providers.   Data Migration between the Databases Create a new database in Microsoft SQL Server, for instance “testMySQL”. Now run the following SQL statement to migrate/import table “users” from the MySQL database db-name, into the newly created database in Microsoft SQL “testMySQL”. SELECT * INTO testMySQL.dbo.users FROM openquery(MYSQL, ‘SELECT * FROM db-name.users’) As with the previous query, be sure to replace db-name and testMySQL as appropriate. That’s it. Your data is now in your Microsoft SQL Server. Please post any queries or questions in the comments section below.

The post How to Migrate from MySQL to Microsoft SQL Server appeared first on OptimusBI.