I am an INETA Country Leader

Today I have officially received the role of the INETA Country Leader for Cyprus.
It was an honor for me to receive this role as it will allow me to further promote the .NET community activity in Cyprus.

The main responsibility of this role is to supervise INETA User Groups in Cyprus and to promote technical communities registered to INETA.

At this point I would like to share my thoughts on what a technical community really means.
Well, technical communities are usually groups of professionals (in this case Microsoft .NET developers) who share knowledge in a non-profit environment.

Within the scope of a technical community, discussions can be made having to do with various technical issues/topics. Also conferences and relevant events can be organized for presenting interesting technical topics.

In Cyprus I am also the leader of CDNUG. CDNUG is committed to the .NET and SQL Server community and to facilitating its growth and development. Within this context we organize regular community events presenting and discussing various interesting .NET and SQL Server development topics.
Read more on this article...

Manipulating EXCEL 97-2003 Worksheets with the OPENROWSET Command

SQL Server provides among other the neat feature of the OPENROWSET command.
OPENROWSET allows establishing ad hoc connections using OLE DB. It actually allows one-time access to remote data from an OLE DB data source. If you just need to access this data in an ad hoc manner, you can use the OPENROWSET command instead of setting up a linked server.

An example of the OPENROWSET command, is accessing EXCEL files and treating them like SQL Server tables thus being able to perform SELECT, UPDATE, INSERT and DELETE operations on them.

There is one consideration though; because establishing ad hoc remote connections may increase security risk, in newer versions of SQL Server (SQL Server 2005, SQL Server 2008) this feature is turned off by default.

Though, you can easily enable it either by configuring the Surface Area of SQL Server from within Management Studio or by using sp_configure.

As I am a little bit of a code junkie I prefer using sp_configure :)

Here's how you can enable ad hoc queries in SQL Server (run each statement separately):

sp_configure 'show advanced options',1
reconfigure
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

And here's how you can disable it (again, run each statement separately):

sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
sp_configure 'show advanced options',0
reconfigure


Let's see some code examples on how we can perform these operations on an EXCEL 97-2003 worksheet.

Assumption 1 : I have an EXCEL 97-2003 file with two columns. The sheet is called "Sheet1" and the two columns,"code" and "description" (I used the first excel row for defining the column names).

Assumption 2 : My EXCEL file's full path is "C:\blog_samples\excel_file_2003.xls"

Tip: It is preferable not to use spaces in file paths for such cases.

SELECT Statement
----------------------
select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\blog_samples\excel_file_2003.xls;','SELECT * FROM [Sheet1$]')


UPDATE Statement
-----------------------
update OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\blog_samples\excel_file_2003.xls;','SELECT * FROM [Sheet1$]')
set description='New Description - Product 3'
where code='30'


INSERT Statement
----------------------
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\blog_samples\excel_file_2003.xls;','SELECT * FROM [Sheet1$]')
select '40','Product 4'
UNION ALL
select '50','Product 5'


INSERT Statement + Row Constructors (for SQL Server 2008!) - Yep, it is a cool feature :)
---------------------------------------------------------------------------------------------
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\blog_samples\excel_file_2003.xls;','SELECT * FROM [Sheet1$]')
values ('60','Product 6'),('70','Product 7')

DELETE Statement
--------------------
Well, the DELETE statement is a little bit tricky with EXCEL data and OPENROWSET. As the Microsoft KB Article on the following link says, in an EXCEL worksheet you can only delete values in fields (cells) while you cannot delete an entire record if you receive the following error message:

Deleting data in a linked table is not supported by this ISAM.

Alternatively, you can "delete" EXCEL records with OPENROWSET by blanking out the contents of each individual field.

Also, the above KB Article says that you cannot delete the value in a cell containing an Excel formula or if you get the following error message:

Operation is not allowed in this context.

So, back to our example, let's run the following:

update OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\blog_samples\excel_file_2003.xls;','SELECT * FROM [Sheet1$]')
set code=NULL, description=NULL
where code='70'

In this way the last record of the EXCEL worksheet has been "deleted".

Remarks
-----------
With the above examples we saw how we can manipulate EXCEL 97-2003 files using SQL Server and the OPENROWSET commnand.

A future post will introduce the topic of manipulating EXCEL 2007 files using the OPENROWSET command.

By the way, on this link, you can obtain the latest service pack for the Microsoft Jet 4.0 Database Engine.

Until next time!
Read more on this article...

A transport-level error has occurred when sending the request to the server

This post's purpose is to explain the above connectivity error and ways of resolving it.

In some cases (upon "wrong" actions or network problems :) we might get the following error in SQL Server:

A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

As the error message indicates, there is a connectivity problem with a previously opened session in SQL Server.

As an example, consider that you have a query window opened. This query window is connected to a database (the default database is "master"). To this end, there is an established connection to that database, that is a session. At a lower level in SQL Server, there is an spid representing this session.

If this spid is forcibly terminated (i.e. by using the SQL Server KILL function, or after an unexpected network problem) and though you are still trying to use this session (i.e. execute a query) you will most probably get the above connectivity error.

A solution in this case, is to close the query window and open a new one. To this end a new session will be opened and will be assigned a new spid. After that, you will be able to use the database properly.

The above connectivity error might arise not only when using the query window, but other SQL Server services that might access SQL Server objects as well. It is a general connectivity error and the best solution for resolving it is to start a new session in order for the connectivity to the specific SQL Server object(s) to be restored/re-initiated.

Read more on this article...

Leadership Award

Today we had our company presentation regarding the financial results of the year 2008.

One of the sessions of this meeting were the Employee Awards for 2008.

To my pleasant surprise I have received the Leadership Award for the year 2008 "in recognition of accomplishments far exceeding company expectations" and for gaining and applying technical expertise on the technologies involved with my professional duties.

It was a great moment!

I believe that one of the most important factors for professional success, is to work with technologies that really inspire you and that you enjoy researching on them all the time. Well, one of these technologies is definitely SQL Server!!! :)

Read more on this article...

Divide by zero error encountered

Well, as the error message says, whenever we have a division within a SQL query and the denominator has the value of zero (0) we get this error.

The exact error we get in SQL Server is:

Msg 8134, Level 16, State 1, Line [Here goes the line of the denominator in your query]
Divide by zero error encountered.

So, which is the best way of addressing this issue? As it is data-related we should be precautious anyway when having a division within a query and effectively control the denominator values for handling the case where a zero might be produced.

Just for reproducing the divide by zero error, consider the following example query:

declare @denominator int
set @denominator=0

select 1/@denominator

There are a few approaches of handling such problem. Here I present three.

Approach 1 - Using the "SET ANSI_WARNINGS OFF" Command
By using the "SET ANSI_WARNINGS OFF" command just right before the rest of the queries, it will allow your query that produces the error not to stop the execution of the rest of the queries.

Example:

SET ANSI_WARNINGS OFF

declare @denominator int
set @denominator=0

select 1/@denominator

.... Other queries go here

Approach 2 - Using the CASE Statement
By using the CASE statement it is possible to check the denominator value for a zero and if it is so you can use 1 in order for the division not to fail.

Example Query:

declare @denominator int
set @denominator=0

select 1/(case @denominator when 0 then 1 else @denominator end)

Alternatively, you can create a custom scalar-valued function that given an input parameter, it can check for a zero and if it is encountered it can return 1 else it should return the input:

CREATE FUNCTION check_denominator
(
-- Function parameter
@input int
)
RETURNS int
AS
BEGIN

-- Declare local variable
DECLARE @result int

-- Check for 0, if so then return 1 else return the input
SET @result =(SELECT (CASE @input when 0 then 1 else @input end))

-- Return the result
RETURN @result

END
GO

Then you can use the above function as follows:

declare @denominator int
set @denominator=0

select 1/dbo.check_denominator(@denominator)


Approach 3 - Using the NULLIF Function
Yep, by using the NULLIF function it is possible to handle the issue of a zero denominator.
But how? :)

The NULLIF function takes two arguments and if they have equal values it then returns a NULL.

The idea here is to compare the denominator value with a zero via NULLIF and if it returns a NULL then to handle it with the ISNULL function (by placing the number 1)!

Example:

declare @denominator int
set @denominator=0

select 1/ISNULL(NULLIF(@denominator,0),1)

Concluding Remarks
Which of the above three aproaches is the best one? Well, this is up to you :)

Personally I do not prefer Approach 1 as it does not solve the problem but rather "says" to SQL Server to ignore it.

So we have Approach 2 and 3 left. Approach 2 looks appealing but still I would only use it with a function.

My personal opinion is that Approach 3 is the best one; it just uses two built-in SQL Server functions and you do not need to write much additional code for handling a zero denominator!

Tip: Also, whenever you have a division in your query keep in mind that if you use only integer variables (like in this example :) and the calculated denominator value is below zero it will return a zero so be careful with that as well (you can use float or decimal instead)!

Hope this was useful.

Until next time!
Read more on this article...

Introduction to ASP .NET AJAX

Well, another successful event took place yesterday in another one of our regular community events of the Cyprus .NET User Group.

This time the topic was ASP .NET AJAX. Our special guest speaker was Karl Davies-Barrett, Developer Platform Evangelist (Microsoft Malta/Cyprus).

AJAX stands for Asynchronous JavaScript and XML. It allows the web developer to make calls from client-side to server-side without a complete Postback.

With AJAX .NET the web developer can easily create interfaces with reusable AJAX components and enhance existing web pages with the use of powerful AJAX controls, thus providing a richer experience for the user.

The event was a success and along with some very interesting discussions it lasted for two hours!

A big thank you to Karl for delivering this great presentation to our User Group!

For more information on our community events/activities check out our User Group's website!

Read more on this article...

Handling NULL and Empty Values in SQL Server

There are many times were we need to handle NULL and "empty" values in SQL Server.
Though there is a difference between a NULL and an "empty" value.

In this example we will examine the above issue.

Consider the following table:

CREATE TABLE [dbo].[table_A](
[id] [int] NOT NULL,
[name] [varchar](50) NULL,
CONSTRAINT [PK_table_A] PRIMARY KEY CLUSTERED
(
[id] ASC
)
) ON [PRIMARY]

Let's populate the table with some data:

insert into table_A(id,name)
values (1,'A'), (2,'B'), (3,''), (4,NULL)

Note that the third record (id=3) contains an empty string for the column "name".
Also note that the fourth (id=4) record contains a NULL value for the same column.

Let's see how can we handle the two above "special" cases.

First of all if we select all the records from table_A we will get:

select id, name from table_A

Result:

ID|Name
-----------
1|A
2|B
3|
4|NULL

Then let's try to handle the record having the NULL value and set as a new value the string "NewValue" for the result set of our select statement.

SQL Server provides 2 functions for doing this; (i) the ISNULL; and (ii) the COALESCE.
Even though the two functions are quite similar, still they have some differences:

(1) ISNULL takes only two parameters as input; (a) the expression to be checked and (b) the replacement value

(2) COALESCE takes N parameters as input (N>=2). By having N expressions as input parameters it returns the first expression that IS NOT NULL. If only all expressions are NULL it then returns a NULL value. It is like a more enhanced version of ISNULL.

Let's try to handle the above scenario with these two functions:

--USE of ISNULL--
select id,ISNULL(name,'NewValue') from table_A

Result:

ID|Name
---------
1|A
2|B
3|
4|NewValue


--USE of COALESCE--
select id,COALESCE(name,'NewValue','NewValue2') from table_A

Result:

ID|Name
-------
1|A
2|B
3|
4|NewValue

Hmmm, we can see that even though for the record with id=4 we got the "NewValue" string instead of NULL, the record with id=3 still returned an empty string value.

In this case it seems that the ISNULL and COALESCE functions had no effect on the third record's result. This was expected because that record does not contain a NULL value but an empty value instead. An empty value cannot be considered as a NULL of course.

So, how do we deal with this? How can we handle empty string values?

Unfortunately there is not a "magic" formula for that. The only suggestion is not to use empty strings but use NULL values instead which can be handled by the ISNULL, NULLIF and COALESCE functions.

Additionally you can always create your own user-defined scalar-valued function that can deal with the issue of the empty string values. In my example, I created the following function and called it ISEMPTY:

CREATE FUNCTION ISEMPTY
(
-- Input Parameters
@input as varchar(250),
@newValue varchar(250)
)

-- Output parameter
RETURNS varchar (250)

AS
BEGIN

-- First handle the case where the input value is a NULL
Declare @inputFiltered as varchar(250)

set @inputFiltered=ISNULL(@input,'')

-- The main logic goes here
RETURN (case rtrim(ltrim(@inputFiltered)) when '' then rtrim(ltrim(@newValue)) else rtrim(ltrim(@inputFiltered)) end)

END
GO

My function takes as input two parameters; (a) the input string and (b) the replacement string.

Then by using a combination of the ISNULL function and the CASE statement it handles both NULL and EMPTY string values. Though, the above user-defined function just handles strings and not any other expressions.

Now if we try running the ISEMPTY function for the same example we get the following:

select id, dbo.ISEMPTY(name,'NewValue') from table_a

Result:

ID|Name
-------
1|A
2|B
3|NewValue
4|NewValue

Well, I guess that's it! :)

You can find more information regarding the abovementioned built-in SQL Server functions that deal with NULLs on the following MSDN Library links: ISNULL, NULLIF, COALESCE.

Enjoy!!
Read more on this article...

String or binary data would be truncated

There are many times where a query may result to the following error:

String or binary data would be truncated.
The statement has been terminated.

We will go through a simple example for explaining this behavior.

Consider the following table:

CREATE TABLE [dbo].[Products](
[id] [int] NOT NULL,
[code] [varchar](10) NULL,
[description] [varchar](100) NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[id] ASC
)) ON [PRIMARY]

In our scenario, we will use the Products table which as the name implies, it stores information about products.

The table has three columns:

• id of type int which is also the primary key
• code of type varchar with size 10
• description of type varchar with size 100

Let's try to insert some records:

insert into Products(id,code,description)
values (1,'00000-1234','Product A')

We can see that the above insert statement was executed successfully:

select * from Products

Result:

id | code | description
---------------------------
1 |00000-1234 | Product A


Now let's try to insert another record:

insert into Products(id,code,description)
values (2,'00000-34567','Product B')

In this case, the above insert statement returns an error:

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

The above error occurs because the size of the code value (11) in the insert statement exceeds the allowed size (10).

If we take a look at the table's definition, we can see that the size of the code column is 10.
Though the size of the value (00000-34567) in the insert statement is 11 characters. In such cases SQL Server returns the abovementioned error.

If we also try to insert multiple records (i.e. by using row constructors) and there is even one record which contains a value that exceeds the allowed size defined for the specific field in the table, then the whole statement fails:

insert into Products(id,code,description)
values (1,'00000-1234','Product A'),(2,'00000-34567','Product B'),(3,'00000-3456','Product C')

Error Message:

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

Now let's try something else:

insert into Products(id,code,description)
values (2,cast('00000-34567' as varchar(10)),'Product B')

Within the above statement the 11-characters code value was converted to varchar(10) and the insert statement was executed successfully.

Though if we see the records in the Products table we can see that the last character (7) was not included.

select * from Products

Result:

id | code | description
---------------------------
1 | 00000-1234 | Product A
2 | 00000-3456 | Product B

As we can see, there are two approaches handling this issue: either filter your data correctly (with respect to the size of characters for the columns in the table's definition) before trying to insert them into the table, or use a SQL Server function (i.e. cast, convert, substring, etc.) in order to automatically remove the redundant characters. The choice is yours!

Of course, you can always use larger column sizes I guess! :)
Read more on this article...