March 31, 2014

SQL Server - Convert Seconds into Hours:Minutes:Seconds format

This query can help to change seconds into Hours:Minutes:Seconds format.

DECLARE @TimeinSecond INT
SET @TimeinSecond = 8000 -- Change the seconds
SELECT RIGHT('0' + CAST(@TimeinSecond / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((@TimeinSecond / 60) % 60 AS VARCHAR),2)  + ':' +
RIGHT('0' + CAST(@TimeinSecond % 60 AS VARCHAR),2)

Output:
02:13:20

March 15, 2014

SQL Server - Split the string

To split the string in SQL Server, you can use CHARINDEX and SUBSTRING function.

CHARINDEX function helps to find the position of the character in the string. This function accepts two arguments. First one is an expression to find and second is an expression to search, based on these argument it will return the first index position of the character of the first argument.

Syntax:

CHARINDEX( expressiontofind, expressiontosearch, start_location)

start_location is optional, if provided then search should start from there otherwise from start.

For example:

SELECT CHARINDEX(' ','Avesh Dhakal') --Result: 6

SELECT CHARINDEX(' ','Avesh Dhakal, 4') -- Result: 6

SELECT CHARINDEX(' ','Avesh Dhakal, 7') -- Result: 0

SUBSTRING function returns the part of the character of the given expression. This function accepts three arguments.

Syntax:

SUBSTRING(expression, start, length)

expression: Its a given string.

start: Its a start position. If the position is less than one, the returned character will begin from first position of the string.

length: It specifies how many character of the string will be returned.

For Example:

SELECT SUBSTRING('Avesh Dhakal',3,6) -- Result: esh Dh


Now, using CHARINDEX and  SUBSTRING function, lets do an example to split the string:

For Example:

DECLARE @String VarChar(255) = 'Hello! How are you?'

SELECT SUBSTRING(@String , CHARINDEX('!', @String ) + 2, 50)

Result: How are you?

March 2, 2014

Linked Server CRUD Operation

Using OPENQUERY


OPENQUERY function can be used to execute the pass-through queries on the linked server.

For example: Let postgres be the linked server name.

Select Statement

select * from openquery('postgres', 'select * from dbo.table')

Update Statement

update openquery(postgres, 'select column_name from table where id = 1')
set column_name = 'abc'

Insert Statement

insert openquery(postgres, 'select column_name from table')
values ('abc')

Delete Statement

delect openquery(postgres, ''select column_name from table where column_name = ''abc''')

Without Using OPENQUERY


There is also another method to run the pass-through queries on the linked server without using OPENQUERY.

LINKEDSERVERNAME.DATABASENAME.SCHEMANAME.TABLENAME

Instead of above select statement, it can be written as:

select * from postgres.databasename.dbo.table

In order to execute the query without using OPENQUERY, you need to query through Remote Procedures Calls (RPC ). This RPC can be found while creating the linked server on the server option.

RPC is to allow remote procedures calls from linked server.
RPC Out is to allow remote procedures calls to linked server.

RPC and RPC Out both should be true.

Even when RPC and RPC Out is false. You can execute the query without using OPENQUERY.

Example:

select * from postgres...dbo.table

It will run successfully.

March 1, 2014

Linked Server for a Remote Database Server

Linked server helps SQL Server Database Engine to execute the query against OLE data sources outside of the instance (Remote Server) of SQL Server.

To add a Linked Server, it can be done either using the GUI interface or the so_addlinkedserver command.

Suppose, we want to create Linked Server for Postgres Database Instance. For this:

1) Firstly, we need to create ODBC Data Source Administrator aster installing the Postgres ODBC Driver.

Go to Run. Type "odbcad32" - System DSN - Add-  Then configure



2) Now, to create Linked Server, you need to go to SQL Server Management Studio - expand the "Server Objects" folder, and then right click on the "Linked Server".

In the below screen shot, datasoure name should be PostgresSQL64W, as given above in the odbc connector. Here, give the linked server name, choose provider and give the datasource name. The provider for remote database will be Microsoft OLE DB Provider SQL Server. Provider string should be blank. Catalog is optional. (Default database can be used).


3) In detail, security and server option: http://msdn.microsoft.com/en-us/library/ff772782.aspx