Join tables from two source

Generally it is not possible to join databases using SQL. However, MSSQL provides a functionality called OPENROWSET that can actually get data from another source.

Some experts even do a inner join with it. However, when I tried using inner join, our sql server keeps giving me timeout error. Creating a view using the functionality is a success though.

I guess we can then join it to other tables or views to get the final result.

The following is how to get it working.

First we must configure the sql server to allow "AD Hoc Distributed Queries". This need to be done only once per server.

Go to sql management studio and navigate to the database in where you have your original data. Go to programmability, stored procedures. Create a new stored procedure but delete everything from the template. Copy and past the following in it.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

Click on Execute from the window\'s menu. You should see some message appearing. Unless it is an error message, you have just finished doing the configuration. You don\'t have to save the stored procedure as you do not need to run it more than once.

Next go to views in the database and create a new view. Type your "select" statement as normal but change the table name after the "From" to something like below

OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=sgsvcit01;UID=sa;PWD=MyPass', wfm.dbo.wfm_case) AS wfm

Save the query and it will be done.wfm.dbo.wfm_case is the sample database. It means we are using wfm database and gets the wfm_case table.

You have to provide an alias otherwise sql server will make noise then provide one for you instead.For obvious reasons the UID and PWD is a dummy value.

You should use the actual UID and PWD.If you are geting data from msAccess table then the openrowset setting will be different. See below example.

OPENROWSET('Microsoft.Jet.OLEDB.4.0', c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders) AS o

All these information were gathered from internet.

XML parsing issue

I tried to run a web page that gets xml data as source. It failed with 0 row. I checked the xml file and it is well formed. I tried to load another known-to-be-working xml file and it parsed ok. Seems like it just cannot work.

I then tried a very crude way. Copy the contents from another xml file and paste it into the xml file I used. The result is that there is again a 0 row. That xml is a known working file.

Finally I get it. The file name of the xml is the same as the web directory name. You will never guess it that IE rejects such xml from being loaded.

Failed to open or save XLS from outlook

If you happened to get this error "Can't open this item. Can't create file: Right-click the folder you want to create the file in, and then click Properties on the shortcut menu to check your permissions to that folder" while trying to open or save XLS, that means you have some issue with how Outlook stores the temporary file.

Use regedit to search for "OutlookSecureTempFolder". Go to that file directory and delete all the files inside. After that, the file can be opened.

The reason for doing is that outlook saves the attachment to the temporary directory first before opening it. Each time you open the attachment the filename stored will be "filename" +"(X)". X will be incremented each time the same file name was saved. The problem begins if you have previously opened the same file name 99 times. The auto filename cannot go beyond the number 99. By deleting all files in the TempFolder, you will reset the number to 1 again. This will allow you to open the attachment without problem.

MSSQL delete and truncate table

It is interesting to note that delete all from table is different to truncate the table. You will notice that the table size (view table properties) does not change and it takes longer to do the process. Truncate table is very much quicker and always reduce the size of the table to 0MB.

SQLITE

It is quite interesting to look at the SQLITE database. There is no server/client architechure. It strikes me that it was something like MSAccess that we use on some of our web services. However, it even gets better than that, it does not require ODBC setting.

For those data that we do not require security, for example bft results, it will be good idea to store the data in such database. The syntext of accessing is very simple. The example is below.


$db = new SQLiteDatabase("db.sqlite");

$result = $db->query("SELECT * FROM foo");

while ($result->valid()) {

$row = $result->current();

......

$result->next();

}

unset($db);

Compared with those that use ODBC, it is a breeze. There is no authentication whatsoever.

The next problem is of course obvious. How do I get the data? There is no import facility built in. Will think of some way to do it.

Remove duplicate rows

Many a times when we update database, there are chances that we may insert duplicates. It is a headache to maintain such database if the source is not within our control.

Below is a way to solve the headache.

with Uniqrow as (

select *,ROW_NUMBER() over (partition by Case_ID order by case_id,x_Cust_Track_No DESC) as rownumb FROM [wfm_ods].[dbo].[TABLE_CASE]

)

delete from Uniqrow where rownumb > 1

The above is based on table_case in WFMODS database. It somehow retrieved duplicate records and there is no way to find out why. Its function is to simply based on the grouping of case_id and rank it. Since we can't just do a delete directly, we use a CTE "with clause" to encase the ranking statement.

The result is very fast. removing 2700 case in less than 1 sec from a total of 11000 cases.

PIVOT in SQL Server

Prior to SQL 2005 I have to explicitly define a query to do a pivot like below

select work_group, sum(case when days <= 10 then 1 else 0 end) as LT3,sum(case when days > 10 and days <15> 15 then 1 else 0 end) as [GT 15] from view_1 group by work_group

In SQL 2005 there is another way to do it.

select work_group,LT3,[3 to 15],[GT 15] from (

SELECT case_id,work_group, CASE WHEN days <= 10 THEN 'LT3' WHEN days >10 and days < 15 THEN '3 to 15' when days >15 then 'GT 15' END AS days

FROM view_1 ) as rawdata

pivot (

count(case_id) for days in (LT3,[3 to 15],[GT 15])) as pvt

The latter looked more complicated but both worked exactly the same. However, the latter is sorted on work_group automatically.

The example is more interesting when "days" is already parsed according to its grouping. You just need to define "select case_id, work_group, days from view_1". Coupled with the fact that "select work_group,LT3,[3 to 15],[GT 15]" can be shortened to just "select *". You get the following

select * from ( SELECT case_id,work_group, days FROM view_1 ) as rawdata pivot ( count(case_id) for days in (LT3,[3 to 15],[GT 15])) as pvt

Don't you think that the above code is much simpler to write than prior SQL 2005 codes? You just can't make it simpler with the prior SQL2005 series.