SQL, VB, MicroStrategy & Other Topics

Being a newbie to AS400/RPG, it took me a few hours to figure out how to call a stored procedure on AS400 from MS SQL. Here is the straight forward syntax: declare @sql varchar(100) = 'CALL LIBRARY_NAME.STORED_PROC_NAME (''PARAM1'', ''PARAM2'', ''PARAM3'')' exec (@sql) AT NAME_OF_LINKEDSERVER   Make sure that the LIBRARY_NAME is a part of user's library. Make sure to use IBMDASQL OLE DB Provider.    

I have a linked server from Microsoft SQL 2008 to a DB2 server. Today when I tried to run a SELECT statement based on the linked server, I hit this error, "cannot create a column accessor for OLE DB provider "ibmdasql" for linked server". Earlier in the day, we had restarted the SQL Server Service. Running the following script on the 'affected' sql server should fix the issue. USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[usp_enum_oledb_providers] AS exec sp_enum_oledb_providers GO sp_procoption 'usp_enum_oledb_providers', 'startup', 1 Restart the sql server service after running above script.

I am back after a while! I have accepted a new position which means more responsibilities. I now lead a Business Intelligence initiative. This opportunity is exciting and interesting. I get to do what I love to do! So first task at hand was to be able to access data from the AS400 system for ETL to MS SQL 2008. Simple, right? Simple? I thought so, but got stumped for the first few minutes and thought many others might have the same issue! If you do not see IBMDA400, IBMDARLA and IBMDASQL as in the above screenshot, you are mssing some drivers. Issue at hand - cannot find proper drivers on MS SQL installation to connect to the AS400 system. What you need to do is install IBM Access iSeries for Windows software on your SQL Server box. http://www-03.ibm.com/systems/i/software/access/windows/index.html . After installing this software, logout and log back in. Once that is done follow these steps. 1. Navigate to SQL Server Installation > Server Objects > Linked Servers ...

Somebody at work asked me today, "Ankeet it is hard to understand/remember how to use PIVOT tables in sql. Can you give me an example?". So here it is. I have used table name prefixes so as to make the code easier to understand and be able to identify where does each field come from. I rarely use pivot tables in sql. use dbName go SELECT myPivotTable.CustomerID, isnull(myPivotTable.[2003],0) as [2003 ActualPaid], isnull(myPivotTable.[2004],0) as [2004 ActualPaid] FROM ( select phf.CustomerID, phf.SystemYear, phf.ActualPaid from Sales as phf where phf.CustomerID between 33131 and 33250 ) AS mySourceTable PIVOT ( avg(mySourceTable.ActualPaid) for mySourceTable.SystemYear in ([2003],[2004]) ) AS myPivotTable ORDER BY myPivotTable.CustomerID

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go CREATE PROCEDURE [dbo].[sp_fixusers] AS BEGIN DECLARE @username varchar(25) DECLARE fixusers CURSOR FOR SELECT UserName = name FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null ORDER BY name OPEN fixusers FETCH NEXT FROM fixusers INTO @username WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_change_users_login 'update_one', @username, @username FETCH NEXT FROM fixusers INTO @username END CLOSE fixusers DEALLOCATE fixusers END Please consider the environment before printing this blog! Go Green!

Few years ago, a friend of mine working for Ramco Systems 'taught' me a logic of incrementally and conditionally updating a table with help of a few variables. I had to use that logic this AM and it required some brush-up, so now I am going to post the logic and sample code right here for all other SQL programmers... create table #temp(num int , runningSum int ) INSERT INTO #TEMP VALUES (1, NULL) INSERT INTO #TEMP VALUES (2, NULL) INSERT INTO #TEMP VALUES (3, NULL) INSERT INTO #TEMP VALUES (4, NULL) INSERT INTO #TEMP VALUES (5, NULL) INSERT INTO #TEMP VALUES (6, NULL) declare @RunningSum int SET @RunningSum = 0 UPDATE #TEMP SET RunningSum = @RunningSum, @RunningSum = @RunningSum + num SELECT * FROM #TEMP DROP TABLE #Temp Results: num         runningSum ----------- ----------- 1           1 2           3 3 ...

A very simple way to select top X random rows from a table/query or select rows in a truly random order. The key is using a GUID. SELECT TOP X * FROM SomeTable ORDER B Y NewID () That Simple!