Me & My Little Techie.......

Extract Just Time from DATETIME data type in SQL Server 2000, SQL Server 2005 and SQL Server 2008

noreply@blogger.com (Vijaya Kadiyala) — Thu, 11 Feb 2010 03:28:00 +0000

In this post i would like to list out the various methods to extract the time from DATETIME data type in SQL Server. Depends on the version of the SQL Server you need to use different methods. In SQL Server 2008 you can use any of these methods but in SQL Server 2005 and SQL Server 2000 only method 1,2 and 3 will work.

DECLARE @DT DATETIME

SET @DT = GETDATE()
-- Method 1:
SELECT CONVERT(VARCHAR(30),@DT,114)
Output:------------------------------

22:31:43:047
(1 row(s) affected)

-- Method 2:
SELECT CAST(@DT AS TIME)
Output:----------------

22:31:43.0470000
(1 row(s) affected)

-- Method 3:
SELECT DATEADD(DAY, DATEDIFF(DAY, @dt, 0), @dt)
Output:-----------------------

1900-01-01 22:31:43.047
(1 row(s) affected)

-- Method 4:
SELECT @DT - CAST(FLOOR(CAST(@DT AS FLOAT)) AS DATETIME)
Output:
-----------------------

1900-01-01 22:31:43.047
(1 row(s) affected)

Reference : Vijaya Kadiyala (www.DotNetVJ.com)

sargable vs non-sargable queries

noreply@blogger.com (Vijaya Kadiyala) — Thu, 11 Feb 2010 01:59:00 +0000

A condition in a query is said to be sargable if the SQL Server can take the advantage of an index to speed up the execution of the query (using index seeks, not covering indexes). sargable stands for Search ARGument Able.

Non-sargable search arguments in the WHERE clause are:
IS NULL
"<>"
NOT
NOT EXISTS
NOT IN
NOT LIKE
LIKE '%something'

In a query if you have mix of Non-sargable and Sargable conditions then SQL Server might use covering index based on the columns used in SELECT Clause, WHERE Clause and JOIN clause. These covering indexes sometimes may not be appropriate to the query and can increase disk I/O. In adidtion to this, try to avoid using any functions on Indexed columns.

Non-sargable Query:
SELECT FirstName,LastName FROM Person.Person WHERE LEFT(LastName,2) = 'Ma'

Sargable Query:
SELECT FirstName,LastName FROM Person.Person WHERE LastName LIKE 'Ma%'

Both of these queries produce the same result but the first one is non-sargable and will run little slow compared to the second one which is sargable.

Reference : Vijaya Kadiyala (www.DotNetVJ.com)

DATEDIFF Function in SQL Server

noreply@blogger.com (Vijaya Kadiyala) — Wed, 10 Feb 2010 02:18:00 +0000

In this post i would like to show you to find the difference between two dates using DATEDIFF in SQL Server.

Syntax: DateDiff(DatePart, StartDate, EndDate)

DatePart is the parameter on which SQL Server calculates the difference between two input dates i.e. startdate and enddate.

Please refer to the below table:
Datepart Abbreviations

DatePart	Abbreviations
Year	yy, yyyy
quarter	qq, q
Month	mm, m
dayofyear	dy, y
Day	dd, d
Week	wk, ww
Hour	hh
minute	mi, n
second	ss, s
millisecond	ms

Ex: To calculate the difference between two dates (No Of Days)
SELECT DATEDIFF(DAY,'2001-01-01','2010-01-01') AS No_Of_Days_Difference
Output:
No_Of_Days_Difference

---------------------
3287

Ex: To calculate the difference between two dates (No Of Years)
SELECT DATEDIFF(YEAR,'2001-01-01','2010-01-01') AS No_Of_Years_Difference
Output:
No_Of_Years_Difference

---------------------
9

Ex: To calculate the difference between two dates (No Of Months)
SELECT DATEDIFF(MONTH,'2001-01-01','2010-01-01') AS No_Of_Months_Difference
Output:
No_Of_Months_Difference
---------------------
108

Reference : Vijaya Kadiyala(www.DotNetVJ.com)

DotNetVJ News - 500th Article

noreply@blogger.com (Vijaya Kadiyala) — Tue, 09 Feb 2010 03:13:00 +0000

This is my 500th article and I very happy and excited about it. I started this blogging on 18-NOV-2007 to share my knowledge and build my own repository of what I am working on. I started this blog on .Net and then slowly started writing articles on SQL Server, PowerShell and MS Office Tips and Tricks. During this course of blogging i was awarded Microsoft Most Valuable Professional in ASP.NET (2008-2009) and in SQL Server (2009-2010). I am very happy and will continue to share my knowledge.

Find the Database creation date (DATABASE AGE) in SQL Server

noreply@blogger.com (Vijaya Kadiyala) — Thu, 04 Feb 2010 13:59:00 +0000

In this post i would like to show you simple tip to find when the database created.

SELECT name,crdate
FROM master..sysdatabases

Database->Date Of Creation

-------------------------------
master-> Apr 8 2003 9:13AM
tempdb-> Jan 29 2010 9:22PM
model-> Apr 8 2003 9:13AM
msdb-> Jul 9 2008 4:46PM
AdventureWorks-> Aug 31 2009 9:49AM
AdventureWorksDW-> Aug 31 2009 9:50AM
AdventureWorksLT-> Aug 31 2009 9:50AM
AdventureWorks2008-> Aug 31 2009 9:50AM
AdventureWorksDW2008-> Aug 31 2009 9:51AM
AdventureWorksLT2008-> Aug 31 2009 9:51AM

(13 row(s) affected)

Please let me know if you have any other way to find the same information...

Table Scan Vs Index Scan in SQL Server

noreply@blogger.com (Vijaya Kadiyala) — Thu, 04 Feb 2010 01:36:00 +0000

Today i would like to clarify the difference between the Index Scan and Table Scan.

A table scan, table is processed row by row from start to end. In the index scan, index is processed row by row from start to end.

If the index is a clustered index then an index scan is really a table scan.

Reference : Vijaya Kadiyala (www.DotNetVJ.com)

Convert list of values seperated by comma stored in a column into Multiple Rows in SQL Server

noreply@blogger.com (Vijaya Kadiyala) — Wed, 03 Feb 2010 02:28:00 +0000

Today, I would like to give you simple tip to convert list of values seperated by comma stored in a column into Multiple Rows in SQL Server.
declare @String varchar(MAX)

set @String = '100,200,250,300,350,450,500'
set @String =','+ @String + ','
SELECT REPLACE(Val_Column,',','')
FROM
(
select
substring(@String,number,CHARINDEX(',',@String,number+1)-number) As Val_Column
from
master..spt_values
where number < LEN(@String)
and type = 'P'
) C
where Val_Column like ',%'

Output

--------------------------------------
100
200
250
300
350
450
500

(7 row(s) affected)

Split Full Name into First Name, Middle Name, Last Name in SQL Server using PARSENAME function

noreply@blogger.com (Vijaya Kadiyala) — Mon, 01 Feb 2010 18:02:00 +0000

In this post i would like to show you a simple technique to split the full name. There are various techniques available to do the same using LEFT, RIGHT,SUBSTRING, CHARINDEX functions.

DECLARE @SQLVariable VARCHAR(100);

SET @SQLVariable = 'Vijaya.Krishna.Kadiyala.SQL Server';

select PARSENAME(@SQLVariable,1) AS Technical_Skill;

select PARSENAME(@SQLVariable,2) AS LAST_NAME;

select PARSENAME(@SQLVariable,3) AS MIDDLE_NAME;

select PARSENAME(@SQLVariable,4) AS FIRST_NAME;

Output:
Technical_Skill

-----------------
SQL Server
(1 row(s) affected)

LAST_NAME
-------------------
Kadiyala
(1 row(s) affected)

MIDDLE_NAME
-------------------
Krishna

(1 row(s) affected)

FIRST_NAME
-------------------
Vijaya

(1 row(s) affected)

Reference : Vijaya Kadiyala (www.DotNetVJ.com)

Convert Number to Varchar in SQL Server using CAST or CONVERT or STR or Any other String Functions

noreply@blogger.com (Vijaya Kadiyala) — Fri, 29 Jan 2010 00:34:00 +0000

Recently i started to explpore "ways" in SQL Server.
Ways in SQL Server:
===============
Implementation of NOT IN operator with out using NOT IN....
TOP 5 ways to delete Duplicate Records in a Table

In the same series today i would like to show you different ways to convert number to varchar.

Using CAST Function:
=================
SELECT CAST(YEAR(GETDATE()) AS VARCHAR)
Output:
----
2010
(1 row(s) affected)

Using CONVERT Function:
======================
SELECT CONVERT(VARCHAR(4),YEAR(GETDATE()))
Output:
----
2010
(1 row(s) affected)

Using STR String Function:
=====================
SELECT STR(YEAR(GETDATE()))
Output:
----
2010
(1 row(s) affected)

Using RTRIM or LTRIM Functions:
=============================
SELECT RTRIM(YEAR(GETDATE()))
Output:
----
2010
(1 row(s) affected)

When you apply any string functions on the numbers then by default SQL Server converts them to string data type.

Please let me know if you have any other ways.....

Related Articles:
============
String Functions in SQL Server Part 1
String Functions in SQL Server -- Final Part

Search For Columns in SQL Server

noreply@blogger.com (Vijaya Kadiyala) — Wed, 27 Jan 2010 15:32:00 +0000

In this post i would like to show you various methods to search for columns in SQL Server metadata tables.

Method 1:
========

SELECT TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE
'%emp%';

Method 2:
========

SELECT OBJECT_SCHEMA_NAME(object_id) AS SCHEMANAME,
OBJECT_NAME(object_id) OBJECTNAME,
name as column_name
FROM
sys.columns
WHERE name LIKE '%emp%';

I normally use Method 1, to find the information. Please let me know if you have any other approach.....

Is Your MONEY Safe in SQL Server

noreply@blogger.com (Vijaya Kadiyala) — Tue, 26 Jan 2010 16:02:00 +0000

In this post i would like to share my experience with you on MONEY data type in SQL Server .

Let’s looks at one example where MONEY data type will round numbers.

Usage of MONEY data type in storing amounts

DECLARE @Money_Amt1 MONEY, @Money_Amt2 MONEY

SET @Money_Amt1 = 100100.1234567;
SET @Money_Amt2 = 100100.1234;

SELECT @Money_Amt1 AS Money_Amt1,
@Money_Amt2 AS Money_Amt2;

As you can see Money data type can hold only up to 4 digits after the decimal. If you are trying to assign a number whose scale is more than 4 digits, then money data type rounds the number, in which case over the course of the time you will accumulate lot of in correct amounts. So you need to make right decision in choosing the right data type for your data.

FYI: This is not a bug neither this is the limitation.

Let’s look at one example where you want to multiply two MONEY data type variables .

Usage of MONEY data type in Multiplication

DECLARE @Your_Money MONEY, @Currency_Conversion_Rate MONEY;

SET @Your_Money = 12.2345;
SET @Currency_Conversion_Rate = 18.7686;

SELECT (@Your_Money * @Currency_Conversion_Rate) AS Col1,
(@Your_Money * 1.0 * @Currency_Conversion_Rate) AS Col2;

As you can see in the above query, I declared two variables. The result of the multiplication or division of two money data type is always money. As discussed previously money data type can hold only 4 digits after the decimal and if it is anything more than that it tries to round to the nearest number. As you can see in the Col1, you don’t see the accurate results of the multiplication operation and whereas in the Col2 by multiplying with 1.0, basically SQL Server is converting into numeric.

Order By Clause is not accepting alias Name in SQL Server

noreply@blogger.com (Vijaya Kadiyala) — Wed, 20 Jan 2010 20:55:00 +0000

Today, i came across a strange behaviour of ORDER BY clause in SQL Server. In one of my post i mentioned that ORDER BY clause is final step in the SQL Query if you don't have TOP keyword in the query.

Lets look at the below example:

SELECT
BusinessEntityID,
LoginID,
HireDate,
GETDATE() AS Current_Dt
FROM HumanResources.Employee
ORDER BY Current_Dt

In this query i have an alias name in the ORDER BY Clause and SQL Server executes the query with out any errors as shown below:

Now If i use any functions on the alias column in the ORDER BY clause, it comes out with errors:

SELECT BusinessEntityID,
LoginID,
HireDate,
GETDATE() AS Current_Dt
FROM HumanResources.Employee
ORDER BY ABS(DATEDIFF(DAY,Current_Dt,HireDate));

Do let me know if you have any answer to this question....

Reference : Vijaya Kadiyala (www.DotNetVJ.com)

Intellisense update/refresh on schema changes - SQL Server

noreply@blogger.com (Vijaya Kadiyala) — Wed, 20 Jan 2010 20:13:00 +0000

The intellisense in SQL Server 2008 is a great feature but sometimes you might get into trouble with it. As an example, If you make any change like creating a new table or column name change etc, while SSMS is open those changes are not reflected in current or even new query editor window in that instance of SSMS.

If you close the instance and launch SSMS again then you can see the changes. So for every change you don't want to close and re-open. So here is the tip, Just refresh your local cache then it will work.

You can refresh you local cache by going to Edit->IntelliSense->Refresh Local Cache or simple using short cut (CTRL+Shift+R)

Reference : Vijaya Kadiyala (www.DotNetVJ.com)

Get the records nearest to GETDATE() in SQL Server

noreply@blogger.com (Vijaya Kadiyala) — Tue, 19 Jan 2010 02:28:00 +0000

In this post i would like to show you different ways to find the rows nearest to the GetDate() in SQL Server.

Method 1, is based on calculating the difference interims of days between GetDate() and HireDate and then take the absolute number using ABS. Order these results in ascending (ASC)order by using ORDER BY Clauase. Finally, filter the results using TOP 1 WITH TIES. WITH TIES is used to select all the records when there is a tie.

Method 1:
=======

SELECT TOP 1 WITH TIES
BusinessEntityID,
LoginID,
HireDate,
GETDATE() AS [Current_Date]
FROM HumanResources.Employee
ORDER BY ABS(DATEDIFF(DAY,GETDATE(),HireDate));

Method 2, is based on using Group Functions. Where MAX function is used to take the recent date and based on this date extracted all the other information.

Method 2:
=======

SELECT
BusinessEntityID,
LoginID,
HireDate
FROM HumanResources.Employee
WHERE HireDate = (SELECT MAX(HireDate) FROM HumanResources.Employee)

Method 3, is based on Analytical Functions in SQL Server. Where RANK function is used to
along with the OVER BY to calculate the difference between GetDate and HireDate. This logic is same as in Method 1.

Method 3:
=======

SELECT V.* FROM
(SELECT
BusinessEntityID,
LoginID,
HireDate,
RANK() OVER (ORDER BY ABS(DATEDIFF(DAY,GETDATE(),HireDate))) AS RecID
FROM HumanResources.Employee ) V
WHERE V.RecID = 1

Please let me know if you have any other ways to wirte these queries.....

Reference : Vijaya Kadiyala (http://www.dotnetvj.com/)

DROP INDEX - Must specify the table name and index name for the DROP INDEX statement

noreply@blogger.com (Vijaya Kadiyala) — Sat, 16 Jan 2010 20:52:00 +0000

In this post i would like to show you, how to drop an index in SQL Server.

If you came from different database backgrounds like Oracle or DB2, then you will issue the following statement and expect to execute without errors.

DROP INDEX INDEX_NAME;

This will not work in SQL Server. You need to specify the on which table this index is defined.
When you execute the above statement you will get below error:

Msg 159, Level 15, State 1, Line 1
Must specify the table name and index name for the DROP INDEX statement.

The right way to drop an index is:

DROP INDEX INDEX_NAME ON TABLE_NAME;

Related Articles:
Clustered Index in SQL Server
Non Clustered Index in SQL Server
Covering Index in SQL Server
Filtered indexes in SQL Server 2008

Reference: Vijaya Kadiyala (www.DotNetVJ.com)

When to use fully qualified names in SQL Server

noreply@blogger.com (Vijaya Kadiyala) — Fri, 15 Jan 2010 19:16:00 +0000

In Sql Server each and every object name has four parts.
1. Server Name
2. Database Name
3. Owner Name
4. Object Name

If you quality Object Name with Server Name, Database Name and Owner then it is referred as Fully Qualified Name.

Some people believes that if you fully qualify the object name it will improve the performance. But its not true. Depends on the need you need to make a right choice. As an example if your application is using one/single database always then you can write queries as shown below:

SELECT * FROM HumanResources.Shift

Sometimes owner name is also referred as Schema. If you take AdventureWorks2008 database, then there are various schemas like HumanResources, Sales, Production, Person etc.

If you have multiple databases and your queries always joins two or more tables from two or more different databases then it will be good if you qualify with database name as shown below:

SELECT * FROM
AdventureWorks2008.HumanResources.Shift
SELECT * FROM
VJ_LCL.dbo.emp
VJ_LCL is my local database and the schema owner of emp table is dbo. Similarly AdventureWorks2008 is database and HumanResources is the schema name and Shift is the table name. Rememeber, object name has to be unique within each schema.

On the other hand, if you have queries that has joins between two or more tables from two or more different servers then you need to fully qualify the object name as show below:

SELECT * FROM
VJMSSQLServer.AdventureWorks2008.HumanResources.Shift

If you fully qualify each and every object even when you have single database then you might get into following problems:

1) If you are planning to change the database, your code will break
2) If you are planning to change the server or deploying it on another server then your code will break.

Reference: Vijaya Kadiyala (http://DotNetVJ.com)

Getting the Server Name in SQL Server using @@SERVERNAME

noreply@blogger.com (Vijaya Kadiyala) — Fri, 15 Jan 2010 18:58:00 +0000

In this post i would like to give you a simple tip on how to get the Server Name of the SQL server.

In order to get the SQL Server - Server name you need to use Global Variable as shown below.

SELECT @@SERVERNAME AS Server_Name

Get Current date and time in SQL Server using CURRENT_TIMESTAMP, GETDATE(), SYSDATETIME()

noreply@blogger.com (Vijaya Kadiyala) — Thu, 14 Jan 2010 18:57:00 +0000

In this post i would like to discuss with you about displaying current date and time in SQL Server.

CURRENT_TIMESTAMP is ANSI compliant. This value is based on the operating system where the SQL Server instance is running. This function is nondeterministic function.

GETDATE() is inherited from sybase.

SYSDATETIME(), this value is based on the operating system where the SQL Server instance is running. This function is nondeterministic function. The return type of this function is datetime2. So this is more accurate than any other time based function.

In addition to this there is another way to display the current date and time.

The {fn Now()} is an ODBC canonical function which can be used in T-SQL. There is no performance difference between any of these functions.

SELECT SYSDATETIME() AS "SYSDATETIME",
CURRENT_TIMESTAMP AS "CURRENT_TIMESTAMP",
GETDATE() AS "GETDATE",
{fn NOW()} AS "fn_now()"

When you co-relate Disk Drives with Girls - Funny Joke

noreply@blogger.com (Vijaya Kadiyala) — Thu, 24 Dec 2009 17:56:00 +0000

I got the below joke from my friend. Its very funny about co-relating Disk Drives with Girls.

HARD-DISK woman: She remembers everything, FOREVER.

RAM woman: She forgets about you, the moment you turn her off.

INTERNET woman: Difficult to access.

SERVER woman: Always busy when you need her.

CD-ROM woman: She is always faster and faster.

EMAIL woman: Every ten things she says, eight are nonsense.

VIRUS woman: Also called “wife”; when you are not expecting her, she comes, installs herself and uses all your resources. If you try to uninstall her you will lose something, if you don’t you will lose everything!!

njoy..

Case Sensitive Search on a Case Insensitive SQL Server

noreply@blogger.com (Vijaya Kadiyala) — Thu, 24 Dec 2009 17:24:00 +0000

In this post i would like to show you, how to implement case sensitive search on
a case insensitive SQL Server column.

First let me create a temp table:

--------------------------------
create table #EMP
(ID int, Name VARCHAR(30));

Insert into #EMP
Select 1, 'vijaya kadiyala' UNION ALL
Select 2, 'Vijaya Kadiyala' UNION ALL
Select 3, 'VIJAYA KADIYALA';

Now run the below query:

SELECT * FROM #EMP WHERE Name = 'VIJAYA KADIYALA'

This query retrieves all the records as shown in below figure:

The string literal what i passed to the query is in Upper case and i am expecting only the row which satisfies this condition. But by default SQL Server Collation attribute is Case insensitive. So SQL Server ignores the case of the string literal.

You can find the collation information by invoking the following procedure.

SELECT DATABASEPROPERTYEX('AdventureWorks2008', 'Collation')

The default collation property value is SQL_Latin1_General_CP1_CI_AS. The text which is Highlighted in green color indicates Case Insensitive.

Now to convert the query into case sensitive query, just add collation property as shown below.

SELECT * FROM #EMP WHERE Name = 'VIJAYA KADIYALA' COLLATE Latin1_General_CS_AS

Now the query is retruning only one row.

Get Database Traffic Report in SQL Server

noreply@blogger.com (Vijaya Kadiyala) — Wed, 23 Dec 2009 17:59:00 +0000

My Database team is working on, to find the database traffic on the server. The database is heavily used by the Online appllication and it is 24/7. We would like to see when the database is heavily loaded and see if there are any processes blocking each other which results in slower performance. One option which came to my mind is setting up a server side trace and capture rpc complete and sql batch complete events along with other events. So we decided to run the trace for 1 week during the first week of the month and 1 week during middle of the month and last week of the month because we are very sure that this is the time where the system is heavily loaded.

During this preriod of time i happen to read one article from technet. With the help of this article you can find the follwwing information:

Determine who's connected to your SQL Server
Dealing with blocked processes
Performance killers
Clues to SQL Server performance problems

Get A Traffic Report: Analyze Your Database Usage With System Tables

Why Upgrade to SQL Server 2008?

noreply@blogger.com (Vijaya Kadiyala) — Wed, 23 Dec 2009 17:40:00 +0000

With the release of the most recent version of SQL Server, Microsoft has delivered a Trusted, Productive and Intelligent Enterprise Data Platform. There are enhancements and new capabilities engineered into SQL Server 2008 to enable your applications to run better and reduce the amount of time you need to spend managing them. If you have been running existing applications on SQL Server 2000 or 2005, you will find a set of exciting new capabilities to improve your applications and reduce support needs within a familiar management interface. Many of these new features can provide immediate benefits without the need to make application changes.

Key reasons to upgrade to SQL Server 2008

Experience the benefits right away - Significant enhancements ranging from Data and Backup compression to query performance and enhanced database mirroring are available without the need to modify your existing applications.

Enhanced security and auditing – Get data encryption and database auditing capabilities within existing applications.

Improved system management capabilities - Features like policy based server management and new tools such as Performance Data Collection help you effectively manage the growth of your data.

Performance Enhancements – There have been many performance enhancements made throughout the technology stack, including enhancements within Analysis Services, Reporting Services and Integration Services. For example Unisys and Microsoft set a new ETL performance record by loading one terabyte of data in less than 30 minutes using SQL Server 2008 Integration Services. Read about additional performance records.

Predictable System Response – New Features such as query governor and data compression along with general scalability enhancements provide scalable solutions that are more reliable for very large enterprise systems.

Developer productivity - Tools like Entity Framework and LINQ, and new date/time, FILESTREAM and spatial datatypes provide powerful and easy to use application development enhancements.

Enhanced Business Intelligence capabilities – additional reporting capabilities integrated with Microsoft Office applications and a new report designer application allows the creation of enterprise reports without the need for Developer Studio.

Application Compatibility & ease of Migration - There are upgrade tools available from Microsoft to help manage your upgrade from prior versions. Compatibility has been maintained with the majority of functionality which should enable most applications to upgrade seamlessly. Learn more about all the system changes from the prior version, and get started with infrastructure assessment and planning for upgrade to SQL Server 2008.

Mainstream support – With the latest version of SQL Server you can benefit from a long term and current support path. As of April 2008 SQL Server 2000 has moved off Mainstream support to an extended support path.

Consistent pricing and support – Microsoft continues its pricing policies of SQL Server 2005 with some additional improvements. In addition, by participating in Microsoft’s Software Assurance program you are eligible for product upgrades, support and other benefits.

Referece : http://www.microsoft.com/sqlserver/2008/en/us/why-upgrade.aspx

Print from the DataGrid view

noreply@blogger.com (Vijaya Kadiyala) — Tue, 22 Dec 2009 21:06:00 +0000

I see one question in almost every technical forum. which is how to print the grid view data.

The DataGridView control in .NET is an amazing data representation control, and contains many advanced features that we could benefit from.

Please check out the below link which will give you very detailed information:
http://www.codeproject.com/KB/printing/datagridviewprinter.aspx

Convert PDF to Image Using Ghostscript API

noreply@blogger.com (Vijaya Kadiyala) — Tue, 22 Dec 2009 20:56:00 +0000

In this post i would like to reference one link to generate image from PDF using external APIs.

You will need at least GhostScript 8.64 (other versions have a problem with Vista). After you have installed the program, just copy the gs32dll.dll from the installation directory (the bin subdirectory) to the directory where you have the EXE of my program.

Check out the below link for complete information:
http://www.codeproject.com/KB/cs/GhostScriptUseWithCSharp.aspx

Convert Rows to Columns with aggregate functions

noreply@blogger.com (Vijaya Kadiyala) — Tue, 22 Dec 2009 20:34:00 +0000

In todays post i would like to show you a simple way to convert rows into columns with aggregate functions. You can also use PIVOT feature to do the same.

create table #Marks
(ID int, Sub int, Value Int)

Insert into #Marks
Select 1, 1, 10 Union all
Select 1, 2, 20 Union all
Select 1, 3, 25 Union all
Select 1, 4, 50 Union all
Select 2, 1, 15 Union all
Select 2, 2, 25 Union all
Select 2, 3, 30 Union all
Select 2, 4, 49

Select * from #Marks;

SELECT Id,
MAX(CASE WHEN Sub = 1 THEN Value ELSE '' END) AS Subject1,
MAX(CASE WHEN Sub = 2 THEN Value ELSE '' END) AS Subject2,
MAX(CASE WHEN Sub = 3 THEN Value ELSE '' END) AS Subject3,
MAX(CASE WHEN Sub = 4 THEN Value ELSE '' END) AS Subject4
FROM #Marks
GROUP BY Id

Thats it :)

Referece: www.DotNetVJ.com