Skip to main content

Posts

Showing posts with the label ms sql

Linked Server to IBM AS400 from MS SQL Server

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 ...
Post a Comment
Read more

MS SQL Pivot Tables

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
1 comment
Read more