SQL Black Belt Advance Articles

Recently just involved in SQL Server 2000 Reporting Services Business Intelligence Development, found out a must read articles for any one who would like to master in SQL Server, the web site is at http://databasejournal.com/article.php/1459531 by William E. Pearson, III

MSDAORA Oracle upgrade connectivity Problem

Oracle client always have connectivity problem to the Oracle database server due to the improper configuration in C:\Oracle\Ora81\network\ADMIN\TNSNAMES.ORA (Assume you did not change the default install folder)

Sometimes you may experience all the configuration are fine, you also can use TNSPING and SQLPLUS to access the ORACLE server without problem, but your Application using MSDAORA provider is facing problem to access the Oracle Server, you may get the error like:-
Error while trying to retrieve text for error ORA-03121
This is usually happen after you upgraded your Oracle client, you have restarted your machine, all the TNSNAMES.ORA entries are correct, everything is fine except your application, either ASP, ASP.NET, VB.NET or any application that use MSDAORA provider.
Why the problem happen is, after you upgraded your Oracle client, your MSDAORA still pointing to the old Oracle version, that's why the problem occurs.
To resolve this problem, goto your machine MSDAORA home C:\Program Files\Common Files\System\Ole DB folder, you will see there are some .reg files:-
mtxoci81_winnt.reg
mtxoci81_win2k.reg
mtxoci7x_winnt.reg
mtxoci7x_win2k.reg
If your Oracle client is 8.1 with Windows 2000 and above, double click the mtxoci81_win2k.reg file to register the new registry for the newer client. Then restart your machine, the problem will be solved automatically.
The content of the mtxoci81_win2k.reg is
REGEDIT4
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]
"OracleXaLib"="oraclient8.dll"
"OracleSqlLib"="orasql8.dll"
"OracleOciLib"="oci.dll"
The nice thing about Oracle is, once the Oracle problem is resolved, you will enjoy with Oracle.

ADO filter with NULL value

NULL value in database means no storage in the cell, if you use ADO to query the data and you would like to filter the NULL value, it will be a challenge for a lot of programmers.

Let's see the following code:-

dim rs, strSQL
strSQL = "SELECT EmployeeId, ReportsTo FROM Employees"
Set rs = CreateObject("ADODB.Recordset")
rs.cursorlocation = 3 ' -- adUseClient
rs.Open strSQL, connobj ' -- Assume the connobj already has open connection
do while not rs.eof
rs.movenext
loop
rs.close

The code above is retreiving data from MS SQL Server Northwind database, it tries to get the employeeId and superior employee Id (reportsTo column).

Let's say the requirement is those employee without superior display in one panel and those employee with superior display in another panel.

I believe no one want to query the database two times by using SQL query "SELECT EmployeeId, ReportsTo From Employees WHERE ReportsTo IS NULL" and "SELECT EmployeeId, ReportsTo From Employees WHERE ReportsTo IS NOT NULL", so we will query one time from the database by re-using the above code.

You will add another code before the while loop:-
rs.filter = "ReportsTo IS NULL"

Immediately you will get this error:-
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another

The reason being is because ADO Filter does not support "IS" and "NULL" statement, even you tried to use the filter as ISNULL(ReportsTo, 'N/A') = 'N/A' also get the same problem.

The only solution to this is back to the SQL query, change the query as
SELECT EmployeeId, ISNULL(ReportsTo, 'N/A') FROM Employees

Then the rs.filter use ReportsTo = 'N/A' will solve the problem, the whole code is shown below:-

dim rs, strSQL
strSQL = "SELECT EmployeeId, ISNULL(ReportsTo, 'N/A') FROM Employees"
Set rs = CreateObject("ADODB.Recordset")
rs.cursorlocation = 3 ' -- adUseClient
rs.Open strSQL, connobj ' -- Assume the connobj already has open connection
rs.Filter = "ReportsTo = 'N/A'"
do while not rs.eof
' -- Display Data in Superior Panel
rs.movenext
loop
rs.Filter = "ReportsTo <> 'N/A'"
do while not rs.eof
' -- Display Data in Subordinate Panel
rs.movenext
loop
rs.close



Cheers!

Dynamic SQL FAQ 1: Convert conditional dynamic SQL string to stored proc

A lot of programmers coded the SQL query in dynamic SQL either in presentation layer or inside stored procedure, it has created a lot of problems in performance, security, maintenanbility, network and memory. More in The Curse and Blessings of Dynamic SQL.

From today onwards, I will compose a FAQ how to convert dynamic SQL to proper stored proc.

Dynamic SQL FAQ 1: Convert conditional dynamic SQL string to stored proc

The first FAQ I always heard is "I have a query based on a parameter, if the parameter is ALL, there will be no filter, if the parameter is a comma delimeter string (CSV), the query result will be filtered by the multiple values string in CSV format.

It has 2 challenges here:-
1) How to split the comma delimeter string
2) How to filter the result when it is not 'All'

It can be achieved easily by using dynamic SQL, the sample below is using SQL Server Northwind database with querying from SQL Query Analyser:-

declare @strSQL varchar(1000), @param varchar(100)
set @param = 'All'
set @param = '1,2,3'
set @strSQL = 'select * from employees'
if @param <> 'All'
set @strSQL = @strSQL + ' where employeeid in (' + @param + ')'
exec(@strSQL)

Let's see the 1st challenge, to split the CSV string, we can use the split function provided from Microsoft SQL Server Magazine Treat Yourself to Fn_Split():
This UDF lets you emulate VB's Split() function and parameter arrays

Then you can split the string by using the query easily:-

SELECT value FROM dbo.fn_split(@s, ',')

To filter either 'All' or not 'All', we have to use the power of "OR" operator, then the above dynamic SQL can be converted to proper SQL query as below:-

declare @s varchar(100)

set @s = '1,2,3'
--set @s = 'All'

select * from employees
where (@s <> 'All' AND employeeid in (SELECT value FROM dbo.fn_split(@s, ',')))
OR (@s = 'All')

Let's give me FIVE!

Microsoft JET Database Engine error '80004005' Unspecified error

When you try to access JET data source like MS Access or MS Excel from your Active Server Pages (ASP), you may get this problem

Microsoft JET Database Engine error '80004005'

Unspecified error

You have set the permission of your IUSR_ComputerName user to full control, may be you already give full control permission to everyone, but the problem still occur.

You also reinstalled your JET with latest service pack, rebooted the server, but the problem still exists.

The problem actually is you have set the environment variables TMP and TEMP to invalid temporary folder.

Please access the Microsoft Knowledge base for more details:-
PRB: "Disk or Network Error" or "Unspecified Error" Returned when Using Jet
http://support.microsoft.com/kb/251254

After set the environment variables correctly, please remember to restart your computer. If it still can not work, you may need to change the temporary folder or set the temporary folder have EVERYONE full control.

Good luck!

IIS Log Analysis with Log Parser 2.2

How to analyze your website usage? How many users access your web site? Where is the top 10 URL in your web application? There are many questions you may ask after you deployed your web application into production web server.

You may think to write a small program to read from your web server log files, it will not be simple, you have to spend sometime to study web server log file, programming it open the log file by date, aggregate the data, filter the data, order the data, accept input from users and etc..

Wait a minute, there is a better way to do this: -

Thanks to Microsoft, they have provided a very powerful log analysis tool - Log Parser, now it is version 2.2, you can download it at http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en. There is also an unofficial Log Parser support website at http://www.logparser.com/

The beauty part of the Log Parser is it combines the power of SQL query to query the data like RDBMS. It can be output to chart, grid, XML, SQL and other formats.

I have execute the Log Parser in my IIS web server to trace my web pages usage on March 2005 by using the following log parser command plus SQL query:-

logparser -i:IISW3C -o:Datagrid
"SELECT
date,
cs-uri-stem,
count(*) AS ACCESS_PER_DAY
FROM
c:\winnt\system32\logfiles\w3svc1\ex0503*.log
WHERE
cs-uri-stem LIKE '/myreport/%.asp'
GROUP BY
date,
cs-uri-stem
ORDER BY 
DATE"

It gives me the output in Grid:-

date       cs-uri-stem     ACCESS_PER_DAY
2005-03-08 /myreport/A.asp              1
2005-03-08 /myreport/B.asp              2
2005-03-08 /myreport/A.asp              6

Cool!

Disable Control+C in javascript

How to disable control+C in your web page to prevent user simply copy your text? Here is the code:-


<html>
<head>
<script language="javascript">
<!--
function Disable_Control_C() {
var keystroke = String.fromCharCode(event.keyCode).toLowerCase();

if (event.ctrlKey && keystroke == 'c') {
event.returnValue = false; // disable Ctrl+C
}
}
//--!>
</script>
</head>
<body onkeydown="javascript:Disable_Control_C()">
Hello World!
</body>
</html>

NOTE: You may need to disable the right click and menu bar to prevent user to copy from your text.

Convert dynamic SQL to stored procedure by using CASE

I always access other programmer code, I saw the code interesting and challenging today - the code is in ASP VBScript + dynamic SQL, the purpose of the code is to generate the SQL query string based on web page querystring, the code is as below:-

StrSQL = "select * from fact_drive_failures2 where day_id = " & request("day_id")
If request("floor") <> "" Then
StrSQL = strSQL & " and floor_id = " & request(“floor_id”)
End If
I believe this is a very common practice for many programmers. With the dynamic SQL string with the ASP code, it has many pitfalls in security, performance, permission, network traffic and many more disadvantages...

I convert this code to MS SQL Server stored procedure:-

CREATE PROCEDURE dbo.GET_DATA
(
@pn_DayId int,
@pv_FloorId varchar(1000)
)
AS
Select * from fact_data
where day_id = @pn_dayid
and floor_id =
case @pv_Floor = ‘’ then floor_id
else @pv_floor end

Now the stored procedure can be called by front-end application without worrying the string concatenation, easier to maintain, better security and the performance is much better.

My blog is alive!

Finally, my blog is alive!

I have been in software devlopment in Microsoft and Unix with more than 10 years experience, I have trained many software developers either during my work, speaking in technical conference and IT training center, it is glad to share my tips and tricks online here.

Let's start!