31 August 2008

ROWCOUNT in action Especially when working with big tables, the default behaviour of Query Analyzer is to not show the output until the last record has been fetched. This can be time and resource consuming and therefore I’ve appreciated the fact that TOAD and SQL Developer are fetching only a certain number of records. Now I can see that same can be done starting with SQL Server 2005 onward by modifying ROWCOUNT server property using Query/Query Options menu functionality. Query Options under SQL Server 2008 Query Options under SQL Server 2008 or by running the command: SET ROWCOUNT <number of records>; Of course somebody may limit the number of records returned by a query using TOP function when working with SQL Server and ROWNUM in Oracle, though I find it not always handy – it depends from case to case. There are also technical implications between the two types of usage, according SQL Server Books online it is recommended to TOP with SELECT over ROWCOUNT with regard to scope and query optimization, however in this context only the later makes sense:
"As a part a SELECT statement, the query optimizer can use the value of expression in the TOP clause as part of generating an execution plan for a query. Because SET ROWCOUNT is used outside a statement that executes a query, its value cannot be used to generate a query plan for a query."
Notes: 1. Do not mix the ROWNUM with @@ROWNUM function which returns the number of rows affected by the last statement. 2. Some of us list all the records in order to see the number of records returned by a query, though that’s totally not advisable!

Document everything!

    One of the lessons I learned on the hard way is that I have to document everything: meeting minutes, ideas, location of artefacts, code, steps done, and even error messages received when installing or using different pieces of software. Of course “everything” has the connotation of “everything important”, though you never know what’s important or not in the near future. Further, if I can put together the ideas in a list of best practices, then I can bring more value to my notes and maybe could help other people too.

    It’s also important how you document everything, though I believed that a question of habitude and preference. I’m still exploring this using flat documents and mind maps or cognitive maps when possible.

    Talking about best practices, it is interesting to see which are other people or companies’ best practices and, in the end, they reflect their level of knowledge reached. I actually target to check first the best practices of a technology or language when learning something new. It can be helpful and a fast way to learn the most appropriate techniques and avoid possible mistakes.
AdventureWorks requires FILESTREAM enabled Surprises, surprises, surprises, programmers’ world is full of them! When you say that everything is ok, you just discover that something went wrong. I was expecting to have Adventure Works database installed though I haven’t checked that. I realized today that it’s missing, so I tried to reinstall it enabling this time the “Restore AdventureWorks DBs” feature, though I got another nice error: Setup failed for MSSQLSERVER. The following features are missing: FILESTREAM Fix the problems and re-run setup. Guy Burstein, in his blog, wrote that the STREAM support can be enabled using the following SQL command: exec [dbo.sp_filestream_configure] @enable_level = 3; I tried that and another error came in: Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'sp_filestream_configure' Checking my local installation of SQL Server Books Online, I found no track of sp_filestream_configure stored procedure, but I found that I can enable the STREAM support using sp_configure stored procedure as below: EXEC sp_configure filestream_access_level, 2 RECONFIGURE GO Once I executed the 3 lines together, I got the following confirmation message which, amusingly, still recommands me to run the RECONFIGURE statement even if I did that. Anyway better more redundant information than nothing… Configuration option 'filestream access level' changed from 2 to 2. Run the RECONFIGURE statement to install.

30 August 2008

No records returned by queries No records returned by a query even if there should be results? Usually I’m using the following checklist: 1. check if the tables contain data. Silly but effective, especially in Oracle APPS in which some tables got deprecated and were replaced by tables with similar names (PA_PROJECTS_ALL vs. PA_PROJECTS), though that could happen in other environments too; 2. check if the JOIN syntax is correct; 3. check if one of the columns use in JOIN has only NULL values; 4. check if the constraints used in WHERE clause causes makes sense (e.g. wrong values or syntax); 5. for Oracle flavored queries, check if in WHERE clause there is a column not referenced with the table name or alias, and the column is available in more than one table used in the query. This Oracle bug is really dangerous when doing fast query checks! 6. for Oracle (APPS), check whether the query or view uses USERENV function with LANG or LANGUAGE text parameter, normally a constraint like: TABLE1.LANGUAGE = USERENV(‘LANG’). The problem with such queries comes when user’s system language is other than the one expected, and thus query’s output might not be as expected. Usually it is preferable to hardcode the value, when possible: TABLE1.LANGUAGE = ‘US’ Note: Actually, also the tools you are using to run a query could create issues, for example a query run under Oracle’s SQL Developer was not returning records even if in TOAD did that. The problem was solved with the installation of a newer SQL Developer version.
Oracle ANSI 92 JOIN syntax error Lately I’m working a lot with Oracle APPS, doing mainly ad-hoc reporting. One of my nightmares is an Oracle bug related to ANSI 92 syntax: “ORA-01445: cannot select ROWID from, or sample, a join without a key-preserved table” Unfortunately, even if the bug was solved by Oracle, it seems the update was missed on some servers and the bug haunts my queries almost on a daily basis. Having an SQL Server background and, for code clearness, I prefer ANSI 92 JOIN syntax: SELECT A.column1, B.column2 FROM table1 A JOIN table2 B ON A.column1 = B.column2 instead of using the old fashioned writing: SELECT A.column1, B.column2 FROM table1 A , table2 B WHERE A.column1 = B.column2 In theory the two queries should provide the same output and have, hopefully, similar performance, but that’s not in scope of this post. The problem with ANSI 92 syntax is that, on some Oracle installations, when the number of joins exceeds a certain limit, usually greater than 7, I get the above error. I can avoid that by using a subquery or a view which would allow me to split the logic and limit the number of direct joins to a table. That can be helpful sometimes, but most of the times it’s cumbersome because it requires more development time and awkward design solutions.
Adventure Works installation error on Vista I tried to install the Adventure Works OLTP & DW on SQL Server 2008 RTM from CodePlex though I got an error: “The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2738.” Initially I thought that the problem was caused by existing Adventure Works installations made on a previous CTP version of SQL Server 2008, forgetting to uninstall them when I uninstalled the CTP version. Totally wrong! Doing a little research, I found first a post on CodePlex Discussion forum mentioning that the problem could be caused by VBScript runtime because, as Toms’Tricks blog highlights, VBScript and Jscript are not registered on Windows Vista. Wonderful! I just run regsvr32 vbscript.dll command and it worked! Another situation in which regsvr32 saved the day! I wonder why I haven’t got the same problem when I previously installed Adventure Works database on CTP version! Could it be because of Windows Vista SP1 changes (I installed Windows Vista SP1 after SQL Server CTP)?
Microsoft SQL Server 2008 installation error This week I tried to install SQL Server 2008 however I got the following error: “A previous release of Microsoft Visual Studio 2008 is installed on this computer. Upgrade Microsoft Visual Studio 2008 to the SP1 before installing SQL Server 2008.” It’s true that I have previously installed Visual Studio 2008, though once I did that I checked if there are any updates and thus I installed SP1 too. I did a quick search on Google and the first results pointed me to an article o Microsoft Help and Support website: Visual Studio 2008 SP1 may be required for SQL Server 2008 installations. It didn’t make sense; in the end I’ve installed the server but enabled the installation of the following components: • Management Tools (Basic or Complete) • Integration Services • Business Intelligence Development Studio The server was installed without problems, so I tried to install the remaining components getting the same error as above. I had to stop at that point and today, giving more thought to the problem, I realized that the error could be caused by Microsoft Visual Studio 2008 Express edition, which I managed to install a few months back. Instead of uninstalling Microsoft Visual Studio 2008 it looked easier to uninstall the Express version, and once I did that, I managed to install the remaining components. Actually I checked before if there is a SP1 for Microsoft Visual Studio 2008 Express, I arrived at Microsoft Visual Studio 2008 Express Editions with SP1 page, though I remembered that I have to install the Web Developer, Visual Basic and C# 2008 separately and in the end I presumed that maybe it would be easier to uninstall the existing versions and try then to install SQL Server remaining components. I haven’t tried to install the Express editions with SP1 as now I have the Professional edition.