Asp.Net (C#)

Null and ToString (Asp.Net)

Quotes

MS SQL Server (Administration)

Shrink DB Size

SSRS

SQL Server Reports (Coming Soon)

SSIS

Error while importing from Flat File

SSIS: Create SMTP (Mail Server) Connection

SSIS: Creating Data Source

SSIS: Create File Connection

MS SQL Server (T-SQL)

SQL NOT IN Constraint and NULL values
Line Break In Dynamic SQL

Learn Spnish

Its Friday Finally -- su el viernes por ultimo.

Null and ToString (Asp.Net)

Sometimes while using ToString method for a object we end up in a situation where object is null and we get Null Exception. eg

Object 0 = null;
o.ToString();

In this case we can use if condition to check whether the object is null and then use ToString method.

if (o != null)
{
o.ToString();
}

SQL NOT IN Constraint and NULL values

Few days back i was working on a query which involved "IN". To verify the result i am getting i used "NOT IN" and guess what... i get 0 result.After spending couple of hours on the issue i found that if in the subquery there is any NULL value then query will return a count of o records.
According to SQL Online Book:
"Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. Using null values in together with IN or NOT IN can produce unexpected results."
Click http://msdn.microsoft.com/en-us/library/ms177682(SQL.90).aspx to read more
On another website i got better example to explain:
To state it simply, why does query A return a result but B doesn't?
A: select 'true' where 3 in (1, 2, 3, null)B: select 'true' where 3 not in (1, 2, null)
This was on SQL Server 2005. I also found that calling set ansi_nulls off causes B to return a result.
The most accepted reason for this behaviour is:
Query A is the same as:select 'true' where 3 = 1 or 3 = 2 or 3 = 3 or 3 = nullSince 3 = 3 is true, you get a result.
Query B is the same as:select 'true' where 3 <> 1 and 3 <> 2 and 3 <> nullWhen ansi_nulls is on, 3 <> null is UNKNOWN, so the predicate evaluates to UNKNOWN, and you don't get any rows.
When ansi_nulls is off, 3 <> null is true, so the predicate evaluates to true, and you get a row
And the another reason i found which explains it well is as follows:
In A, 3 is tested for equality against each member of the set, yielding (FALSE, FALSE, TRUE, UNKNOWN). Since one of the elements is TRUE, the condition is TRUE. (It's also possible that some short-circuiting takes place here, so it actually stops as soon as it hits the first TRUE and never evaluates 3=NULL.)
In B, I think it is evaluating the condition as NOT (3 in (1,2,null)). Testing 3 for equality against the set yields (FALSE, FALSE, UNKNOWN), which is aggregated to UNKNOWN. NOT ( UNKNOWN ) yields UNKNOWN. So overall the truth of the condition is unknown, which at the end is essentially treated as FALSE

Line Break In Dynamic SQL

While writting dynamic sql you might get into situation where you want to separate the code in next line. For that purpose use

Char(13) + char(10) and this is will give a line break in the code you are writing in dynamic sql.

Error while importing from Flat File

Sometime while importing the data from Text file to SQL Server 2005 table we keep getting the below error and the same data can be imported to SQL Server 2000 without any issue.
"Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "FRDNAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".(SQL Server Import and Export Wizard)"
Initially i was also convinced that this is definitly a bug in SQL Server 2005. But i was wrong. The reason why SQL gives this error is because Flat File defaults initially all the columns to Characters with length of 50.And if your data length in column is more than 50 you get this error.
The easy fix for this issue is.
While you select the text file as data source go to "Advanced" tab and Set the size of the column length to be according to what you want.
And you are all set.

Shrink DB Size

Many times we delete/truncate the data from database but database does not release the space to system after that. In that case DBCC ShrinkDatabase and DBCC ShrinkFile can be usefull.

This is something important information about DBCC ShrinkDatabase.

"The NOTRUNCATE option, with or without specifying target_percent, performs the actual data movement operations of DBCC SHRINKDATABASE including the movement of allocated pages from the end of a file to unallocated pages in the front of the file. However, the free space at the end of the file is not returned to the operating system and the physical size of the file does not change. Therefore, data files appear not to shrink when the NOTRUNCATE option is specified.The TRUNCATEONLY option reclaims all free space at the end of the file to the operating system. However, TRUNCATEONLY does not perform any page movement inside the file or files. The specified file is shrunk only to the last allocated extent. target_percent is ignored if specified with the TRUNCATEONLY option."

Personally i would prefer DBCC ShrinkFile and Shrink each file (mdf/ldf) separatly.

If you have very big database then do it off peak times.

SSIS: Create File Connection

· Right click anywhere in the Connection Managers pane.
· In the Types list select File and click ADD.
· In Usage type section select the appropriate option from the dropdown.
** If Create File is selected then new file will be created to the specified location with the given name.
· Click browse and select the location for the file.
· Click OK.

SSIS: Create SMTP (Mail Server) Connection

· Right click anywhere in the Connection Managers pane.
· In the Type select SMTP and then click ADD.
· Provide suitable name to the connection.
· Enter the name of the SMTP server.
· Select suitable option for Windows Authentication and Enable SSL.
· Click OK.

SSIS: Creating Data Source

· Right click data sources and select New Data Source.
· Click on New.
· In provider dropdown select Microsoft OLEDB Provider for SQL Server or suitable connection provider according to your data source.
· Enter the server credentials.
· Click on Test Connection to verify the connection.
· Click OK.
· Hit Next.
· In Data Source Name give proper name to your data source connection and verify the connection string in preview box.
· Click Finish.

Quotes

When i read about the evils of drinking, i gave up reading.

To achieve the impossible dream, try going to sleep.

SQL Server Reports (Coming Soon)

SQL Server Reports (Coming Soon)