SQL Server Developer

XML Error when Opening SSIS Packages

When opening a SSIS package you may receive the following error: Unable to instantiate XML DOM document, please verify that MSXML binaries are properly installed and registered. Solution This error is caused by an issue with the MSXML installation.  Reinstalling or repairing of MSXML6 will solve the above problem.

Reporting Services 2000: Database changes are not updated when exported to Excel?

In SQL Server 2000 Reporting Services the reports can be exported to other data formats such as Excel. This feature is available with a simple configuration as HTML link call on Reporting Services to export the data to Excel for other users purpose. Sometimes the user might find it difficult to achieve consistency between a […]

How do you create trigger on system tables?

First of all, you should refrain from messing around with SQL Server’s system tables, unless you are told to and are guided by Microsoft. You are running an unsupported system if you modify the system tables! But if you don’t want to listen to the above advice, why would one want to create a trigger […]

How do you mimic Access’ Ignore Null index behavior in SQL Server?

What is meant here is the Microsoft Access feature to create unique indexes on columns that can contain two or more NULLs. SQL Server does not allow a duplicate entry in an index that is created as UNIQUE. Consider this: CREATE TABLE Table1 (cID INT PRIMARY KEY  , c1 INT NULL) CREATE UNIQUE NONCLUSTERED INDEX […]

How do you display NULL values at the end of a resultset?

Actually, this question can be extended to: How can you create a custom ORDER BY. But let’s first have a look at the following query:USE PUBSGOSELECT title_id, type, price, royaltyFROMtitlesORDER BYroyaltytitle_id type         price                 royalty ——– ———— ——————— ———– MC3026   UNDECIDED    NULL                  NULLPC9999   popular_comp NULL                  NULLPS1372   psychology   21.5900               10BU1032   business     19.9900               10BU1111   business     11.9500               10BU7832   business     […]

Why doesn’t ISNUMERIC() always produce the results you expect?

According to SQL Server’s Books Online (BOL), the ISNUMERIC() function an easy and convenient way to determine whether an given expression can be evaluate as one of SQL Server’s valid numeric data types. These are INTEGER, FLOAT (REAL), DECIMAL (NUMERIC) and MONEY.Now, unfortunately, this function is buggy and can easily be fooled. Consider this:SELECTISNUMERIC(‘€+,.’), ISNUMERIC(’1d2′), […]

How do you determine the Nth row in a SQL Server database?

Consider the Pubs sample database. Our task is to determine the third, but last date when one employee joined the company. Several approaches are possible here. Let’s first have a look at the different methods available to us, before getting into a basic performance analysis.   1) Using TOP. This is probably the most intuitive […]

How can you use IIf in Transact-SQL?

This is a quite common question. It is usually asked by people arriving at SQL Server with a background in Microsoft Access. They either want to use SQL Server as a backend for their Access project, or they are otherwise upsizing from Access to SQL Server. The answer, however, is usually not much appreciated at […]

What happens when my integer IDENTITY runs out of scope?

Before we actually look at the answer, let’s recall some basics of the IDENTITY property and SQL Server’s numerical data types. You can define the IDENTITY property on columns of the INT data type and on DECIMAL with scale 0. This gives you a range of: TINYINT 0 – 255 SMALLINT -32.768 – 32.767 INT […]

Is it a good practice to use SQL Server stored procedures for INSERT, UPDATE, and DELETE statements?

Yes, anytime you pass a data modification request from a front-end application to SQL Server, it is a best practice to encapsulate the code in a stored procedure. Here are some of the reasons why: It is faster than using ADO methods to accomplish the same task. Network traffic can often be reduced because less […]
Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |