“WHERE” Condition with LIKE in ACCESS:

“WHERE” Condition with LIKE in ACCESS:

Generally we used to work with SQL-Server or ORACLE database so we are very well aware with its SQL syntaxes but when sometime we have to deal with ACCESS database, we don’t care to learn it and use our SQL knowledge in ACCESS database. This might lead to logical or syntax error sometime. Once, I have been asked by one of my colleague that simple LIKE is not working in ACCESS database. I had a quick look of it and found one syntax problem in his SQL statement.

Unlike SQL-Server ACCESS database won’t accept”‘%” wildcard. Rather it will accept “*” (asterisk) sign. So if you want to select data from EMPLOYEE table whose first name starts with “R” then you should go for:

SELECT * FROM Employee WHERE FirstName like ‘R*’

Rather than SELECT * FROM Employee WHERE FirstName like ‘R%’

Second SQL statement is wrong in ACCESS database. It’s a logical error.

Reference: Ritesh Shah

Difference between Microsoft SQL-Server 2000 and Microsoft SQL-Server2005:

Difference between Microsoft SQL-Server 2000 and Microsoft SQL-Server2005:

This question is one of the mostly asked question in an interview but let me tell you that in depth answer of this question could generate one complete book but let us see the difference here in brief.

Almost features of Microsoft SQL-Server 2000 are available in Microsoft SQL-Server 2005 with some improvement and enhancement along with some new feature. Let us see those in brief.

— GUI improvement (SSMS –SQL Server Management Studio)
— T-SQL enhancement
–CLR integration
–Table Partitioning
–Database Mirroring
–DDL trigger support

Along with “Database Mirroring” as a high availability in Microsoft SQL Server 2005 there are old feature of SQL Server 2000 are also available like replication, clustering and log shipping but it was working for only SQL-Server 2000’s enterprise edition but in Microsoft SQL Server 2005, it is available for standard edition also.

Scalability was limited to 2GB and 4CPU in standard edition of Microsoft SQL Server 2000 but in Microsoft SQL Server 2005, it supports 4 CPU and no RAM limitation in standard edition.

As long as encryption concern there was no built in option available in Microsoft SQL Server 2000, you have to have third party tool and expertise to use that tool while in Microsoft SQL Server 2005 it comes up with inbuilt encryption support.

Apart from that, MS SQL Server 2005 comes up with BI reporting tool as known as “YUKON”.

Reference: Microsoft Site

WHERE and HAVING clause in Microsoft SQL-Server

 WHERE and HAVING clause in Microsoft SQL-Server

People may get confused many time about usage of WHERE and HAVING clause. I am just trying to throw little bit light on WHERE and HAVING clause to make its concept clear. In my many interview session, I asked this simple question to lot of candidate and many of them are aware with “WHERE” condition but not or partial aware with “HAVING” so I felt to write this simple article for them.

Note: This article is moved to following link on 6th NOVE 2012. Please click on following link to read full article with TSQL Script.

http://blog.extreme-advice.com/2009/03/05/where-and-having-clause-in-microsoft-sql-server/

Use of RANK() and Partition by clause in SQL-Server 2005

Use of RANK()  and Partition by clause in SQL-Server 2005

We will start by creating one table for demonstration and enter some records in it.

–Create one table

CREATE TABLE BlogCount

(

BloggerName VARCHAR(10),

Topic VARCHAR(15),

[Year] INT,

Total INT

)

 This Article is move to here in “Extreme-Advice.com

Sorry for the inconvenience.

 

TOP clause takes INT variable – SQL-Server 2005

 TOP clause takes INT variable – SQL-Server 2005

This is TIPS about new feature of SQL-Server 2005. In SQL-Server 2005 you can use variable in TOP clause which was not possible in any of the older version of Microsoft SQL Server.

You can supply any integer value beside TOP clause in Microsoft SQL-Server 2005 and in 2008. BTW, you can now use TOP clause in INSERT, UPDATE and DELETE also.

USE AdventureWorks

GO

 

DECLARE @ReturnRow INT

SET @ReturnRow=5

SELECT TOP (@ReturnRow) * from Purchasing.Vendor

Reference: Ritesh Shah

Startup Stored Procedure -SQL-Server 2005

 Startup Stored Procedure -SQL-Server 2005

All of you may aware with Stored Procedure but you may not aware with startup stored procedures which suppose to run every time SQL-Server’s services restart.

This task may be useful for some administrative task, i.e.: you may know that TempDB will be recreated from scratch whenever server restarts. You may want some user to grant permission of TempDB every time it is being created.

Before you start generating startup stored procedure, have a look at some basic ground regarding that.

You have to make sure that ‘Scan for Startup Proc’ option is set to 1. You can see that option and make it disable and enable with following queries.

 

–this will show you all advance option

EXEC sp_configure ‘show advanced option’, ‘1’;

RECONFIGURE

 

–this will enable startup procedure

EXEC sp_configure ‘scan for startup procs’, ‘1’;

RECONFIGURE

Now, you can create stored procedure and it will be set as startup proc, before you create it, you have to follow some strict guideline.

–Procedure should be reside in MASTER database
–Its owner should be DBO
–Mark your stored procedure as startup stored procedure with sp_procoption
–Only sysAdmin can set sp_procoption
–Your procedure shouldn’t have any input parameter or return any output parameter

Now, we will create one stored procedure and will mark it as startup procedure.

–Creating strored procedure

CREATE PROC spCreateDatabaseAtStartup

AS

      EXEC(‘CREATE database StartupDatabase’)

GO

 

–Marking SP as startup

exec sp_procoption N‘spCreateDatabaseAtStartup’, ‘startup’, ‘on’

In sp_procoption first parameter is our stored procedure name. Second parameter is “OptionName”  but fortunately or unfortunately there is only one option name “startup” J and third parameter is “OptionValue” that should be “on” or “off”.

Reference: Ritesh Shah

Read XLS or MDB file with OpenDataSource – SQL Server 2005

Read XLS or MDB file with OpenDataSource – SQL Server 2005

Recently, I had a task to transfer data from MDB file to SQL-Server 2005 so I googling little bit and found very good way of doing so. I liked the way and felt it cool so I am sharing with you guys. Before you start this article, Please see my previous article to enable “Ad hoc distributed queries” with sp_configure. Once you enable it. Please create one XLS file, named Emp.XLS, with three columns in “sheet1”.

ID
Name
Dept

This article is moved at following location. sorry for the incovenience.

http://blog.extreme-advice.com/2009/03/02/read-xls-or-mdb-file-with-opendatasource-sql-server-2005/