RPAD and LPAD Functions in Sql Server

Edit: If you are using SQL Server 2012 or 2014 you may like to see my new post on this topic: LPAD and RPAD Functions in SQL Server

In Sql Server we don’t have RPAD and LPAD functions as they are in Oracle. But it is not that difficult to implement the same thing in Sql Server. With the help of RIGHT and LEFT functions we can achieve that easily. Let’s see how…

First create this table:
CREATE TABLE Temp(Id INT)
GO

Now insert the table Temp with some sample data:
INSERT INTO Temp SELECT
1 UNION SELECT
2 UNION SELECT
12 UNION SELECT
123 UNION SELECT
1234 UNION SELECT
12345
GO

And here is our RPAD and LPAD query

SELECT ID
,RIGHT( '00000' + CONVERT(VARCHAR(5), Id), 5) as LPAD_Example
,LEFT( CONVERT (VARCHAR(5), Id)+'00000' , 5) as RAPD_Example
FROM Temp

--END--

- Mangal Pardeshi

Linked Server Error : Msg 7399, Level 16, State 1

In this post I'll show you a simple solution to a very common error you get while running a query against a linked Server.
You create a Linked Server with Flat Files, Excel or DBF in SQL Server using Microsoft.Jet.OLEDB.4.0 and when you try run a query against it you get the error
--Error--
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server " " returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "".

Well whatever study I have done on this particular error, I have observed that you will get this error only when try to query from a remote machine. And this is something to do with the Security Context of the Linked Server.

Solution to this problem is very simple. Simply follow steps -
1.Open the Management Studio and navigate to Server Objects and then to Linked Server.
2.Right click on your Linked Server Name, and click on Properties.
3.Go to Security Page. Now for solving above problem you have 2 option, you can try any of the below 2 option.

A] Select the option “Be made without using a security context” by clicking on radio button.

OR

B] Select the last option “Be made using this security context ”.
As soon as click on the radio button before “Be made using this security context ”, the“Remote login” and “With password” text boxes become active to be filled in.
Now in the “Remote login”, enter Admin as the login user. Leave the password text box as blank.
Now just click on OK, and now can run the query against your linked server without any error.

- Mangal Pardeshi.

How to delete the Duplicate Rows from SQL Server table

Lets see how to delete the duplicate Rows from SQL Server table.

Sample Table:

ID	NAMES	CODE
1	Mangal	101
1	Mangal	101
1	Mangal	101
2	Ricky	102
3	Brian	103
4	shiv	104
4	Shiv	104
5	Kunal	105
5	Kunal	105
6	Kate	106

As you can see in above table, record with Id 1 has come thrice, and ID 4 and 7 has come twice.

Desired Output: after removing duplicates -

ID	NAMES	CODE
1	Mangal	101
2	Ricky	102
3	Brian	103
4	Shiv	104
5	Kunal	105
6	Kate	106

Create And Load Sample Data -

-- Create Sample table CREATE TABLE Temp ( Id INT, Names VARCHAR(100), Code INT ) GO -- Load sample data INSERT INTO Temp SELECT 1, 'Mangal', 123 UNION ALL SELECT 1, 'Mangal', 123 UNION ALL SELECT 1, 'Mangal', 123 UNION ALL SELECT 2, 'Ricky', 134  UNION ALL SELECT 3, 'Brian', 435 UNION ALL SELECT 4, 'Shiv', 223   UNION ALL SELECT 4, 'Shiv', 223   UNION ALL SELECT 5, 'Kunal', 654  UNION ALL SELECT 5, 'Kunal', 654  UNION ALL SELECT 6, 'Kate', 611 GO

Now first lets see how to find duplicate rows. With SQL Server 2005/2008 and Row_Number function, it has become very easy.


To Find Duplicate rows :

WITH Cte AS ( SELECT Id, Names, Code, ROW_NUMBER() Over (PARTITION BY Id ORDER BY ID) as Seq FROM TEMP ) SELECT DISTINCT ID, Names, Code FROM Cte WHERE Seq > 1

To Delete the Duplicate Rows :

WITH Cte AS ( SELECT Id, Names, Code, ROW_NUMBER() Over (PARTITION BY Id ORDER BY ID) as Seq FROM TEMP ) DELETE FROM cte WHERE Seq > 1

- Mangal Pardeshi

Removing Time from DateTime column in Sql Server 2005

Well this is most common question I answered many times on Sql Server MSDN forums.

How to remove Time from Datetime column in Sql Server 2005 and 2000?

Actually this is one of the biggest drawbacks in Sql server till the Sql Server 2008 happened; that we don’t have only Date datatype. In Sql server 2000 and 2005 we have Datetime datatype, which always come with time part attached with Date. Thankfully in Sql Server 2008 we have simple Date datatype.

Let it be, we’ll see how to remove Time part while querying the Datetime column. Actually it is quite easy with simple CONVERT function. The Sql syntax for it is as follows –

SELECT CONVERT ( varchar(12), DateColumn[, Style] ) as Date

FROM TableName.

Ahh, actually I wanted to create a chart out of it, but with Blogger you can’t create one, so I ended up writing this long list. Here I used getdate function; you can put the Datetime Column name in place of getdate() of your table in following examples.

• Format: mm/dd/yy
  SELECT CONVERT( Varchar(12), GetDate(),1)
  Output: 12/27/08

• Format: mm/dd/yyyy
  SELECT CONVERT(Varchar(12),GetDate(),101)
  Output: 12/27/2008

• Format: yy.mm.dd
  SELECT CONVERT(Varchar(12),GetDate(),2)
  Output: 08.12.27

• Format: yyyy.mm.dd
  SELECT CONVERT(Varchar(12),GetDate(),102)
  Output: 2008.12.27

• Format: dd/mm/yy
  SELECT CONVERT(Varchar(12),GetDate(), 3)
  Output: 27/12/08

• Format: dd/mm/yyyy
  SELECT CONVERT(Varchar(12),GetDate(), 103)
  Output: 27/12/2008

• Format: dd.mm.yy
  SELECT CONVERT(Varchar(12),GetDate(), 4)
  Output: 27.12.08

• Format: dd.mm.yyyy
  SELECT CONVERT(Varchar(12),GetDate(),104)
  Output: 27.12.2008

• Format: dd-mm-yy
  SELECT CONVERT(Varchar(12),GetDate(), 5)
  Output: 27-12-08

• Format: dd-mm-yyyy
  SELECT CONVERT(Varchar(12),GetDate(),105)
  Output: 27-12-2008

• Format: dd mon yy
  SELECT CONVERT(Varchar(12),GetDate(), 6)
  Output: 27 Dec 08

• Format: dd mon yyyy
  SELECT CONVERT(Varchar(12),GetDate(),106)
  Output: 27 Dec 2008

• Format: mon dd, yy
  SELECT CONVERT(Varchar(12),GetDate(),7)
  Output: Dec 27, 08

• Format: mon dd, yyyy
  SELECT CONVERT(Varchar(12),GetDate(),107)
  Output: Dec 27, 2008

• Format: mm-dd-yy
  SELECT CONVERT(Varchar(12),GetDate(),10)
  Output: 12-27-08

• Format: mm-dd-yyyy
  SELECT CONVERT(Varchar(12),GetDate(),110)
  Output: 12-27-2008

• Format: yy/mm/dd
  SELECT CONVERT(Varchar(12),GetDate(),11)
  Output: 08/12/27

• Format: yyyy/mm/dd
  SELECT CONVERT(Varchar(12),GetDate(),111)
  Output: 2008/12/27

• Format: yymmdd
  SELECT CONVERT(Varchar(12),GetDate(),12)
  Output: 081227

• Format: yyyymmdd
  SELECT CONVERT(Varchar(12),GetDate(),112)
  Output: 20081227
  
  Well I already wrote one blog entry on how to convert date in required format.
  Date Formats In SQL SERVER.
  
  For more on CONVERT see
  CAST and CONVERT (Transact-SQL) Books Online
  
  - Mangal Pardeshi.

Linked Server With DBF Files

In my previous post I wrote about Linked Server with Excel. Now we will see how to create a linked server for DBF files. That is accessing and querying DBF files from SQL Server Management Studio.

First I’ll show you how to set up the Linked Server from Management Studio.

Open the Management Studio and navigate to Server Objects and then to Linked Server. Right click on the Linked Servers node and select “Create New Linked Server”.
Enter in the name of your linked server in the text box that appears next to the “Linked Server” label. This can be any name that you would recognize to describe the object.

Then under Server Type, choose the “Other Data Source” Radio button. And select the provider “Microsoft Jet 4.0 OLE DB Provider” from the list.
For “Product Name” enter “Microsoft Jet”
In the “Data Source” text box, enter the full folder path to your dbf files. For mine I entered this: “D:\DBF Project\Data”.
For the “Provider String” enter “dBASE 5.0”

Then go to the security page. Here you have 2 options.
Either select “Be made without using a security context”
OR
Select “Be made using this security context” radio button found near the bottom of the window. The “Remote login” and “With password” text boxes become active to be filled in.
In the “Remote login”, enter “Admin” as the login user. Leave the password text box blank. Well I don’t know the exact answer why this needs; I tried searching on Net but couldn’t find the answer, but this how it works.

And say “ok”, now your Linked Server DBF files is ready. Just expand the Linked Server and then now created Linked server, after expanding the Default you will see Tables and Views. Expand the Tables node to see a list of table objects for the folder.

Now you can query the DBF files just like any other table from management studio. Now open the new query window to write a query on DBF files. Just remember you have to include the linked server and table name in a four part address. Namely, [LinkedServername]…[TableName].

So your select query will be like
SELECT * FROM [Linked_Server_Name]…[TableName]

e.g.
SELECT * FROM DbfLinked…Items.

Here DbfLinked is the linked server name I gave and “Items” is the table name. Observe that there are 3 dots between linked server name and table name.

We can use almost any select statement to retrieve our dbf data. For example JOINS with other tables in Sql Server or with other DBF tables, WHERE conditions, GROUP BY, HAVING, ORDER BY, even the UPDATE, DELETE or for that matter any valid Sql statement you normally do with tables.


Same Linked Server you can create using Sql Statement.
-- To create Linked Server With DBF Files
EXEC master.dbo.sp_addlinkedserver
@server = 'DbfLinked',
@srvproduct = 'Microsoft Jet',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = 'D:\DBF Project\Data',
@provstr = 'dBASE 5.0'


/* Now use either of two secuirty Context*/
-- To select security Context “Be made without using a security context”

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = 'DB',
@useself = 'False',
@locallogin = NULL,
@rmtuser = NULL,
@rmtpassword = NULL

-- Or using “Be made using this security context”
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = 'DB',
@useself = 'False',
@locallogin = NULL,
@rmtuser = 'Admin',
@rmtpassword = ''


- Mangal Pardeshi.