h1

SQL SERVER: Difference between OpenQuery and OpenRowSet

03/25/2009

Today, one developer asked me what is the difference between OpenQuery and OpenRowSet.

Let me share this thing with all of you.

Syntax for both the command:

OPENQUERY ( linked_server ,’query’ )

OPENROWSET
( ‘provider_name’ , ‘datasource’ ; ‘user_id’ ; ‘password’
, { [ catalog. ] [ schema. ] object | ‘query’ }
)

Difference is:
OpenQuery uses a predefined linked server,
While OpenRowSet has to specify all the connection options. So with OpenRowSet you can query to your remote SQL server from local.

Else it’s the same.

Let me know if it helps you in any way.

h1

SQL SERVER: Get Result ORDER BY Time regardless Date on SQL DateTime column

03/24/2009


Usually we save Time with Dates in DATETIME column.
Today, I came across situation, where I need to sort my result set by Time, regardless the Date.


Let’s see Example

I have some sample data like this:

DECLARE @Data TABLE(dt DATETIME)

INSERT INTO @Data(dt)
SELECT ‘2008-12-05 04:00:00.000’
UNION ALL
SELECT ‘2008-12-10 10:00:00.000’
UNION ALL
SELECT ‘2009-03-01 08:00:00.000’
UNION ALL
SELECT ‘2009-03-02 07:15:00.000’
UNION ALL
SELECT ‘2009-03-10 08:50:00.000’
UNION ALL
SELECT ‘2008-12-31 23:00:00.000’
UNION ALL
SELECT ‘2009-05-01 21:10:00.000’

SELECT *
FROM @Data
Now we need output like this:

Get Result Order BY Time regardless Date on DateTime column

Get Result Order BY Time regardless Date on DateTime column


I found very quick solution for this. You can create query as follows:

SELECT *
FROM @Data
ORDER BY Convert(VARCHAR, dt,108)


Let me know if it helps you in any way.

h1

SQL SERVER: Display Minutes in Hour Format like HH:MM

03/23/2009


Usually, we are saving Hours in Minute Format. So if user has entered
Hour: 2 Minutes: 15. We will save 135 Minutes in Database, so we can manipulate it easily.


Now we need a report in which we need to display this minutes in HH:MM format as user has entered.

This is very simple, lets see:

DECLARE @t TABLE(Minutes INT)
INSERT INTO @t
SELECT 120
UNION ALL
SELECT 135
UNION ALL
SELECT 135
UNION ALL
SELECT 1440
UNION ALL
SELECT 640
UNION ALL
SELECT 720

SELECT Minutes / 60 as Hours,
Minutes % 60 AS Minutes
FROM @t

Let me know if it helps you in any way.

h1

SQL SERVER: Unable to Launch Activity Monitor. How to give VIEW SERVER STATE Permissions to launch Activity Monitor

03/22/2009
Activity Monitor, is used view to current executing queries, Query status, Start Time, Host, Blocking or not, Database etc.

Last week, one of my developer has one issue. When he try to open Activity Monitor, System gives the following Error:

152

I found that this user need to get VIEW STATE Permissions, Lets see how to give this permission to the user.

We can give permission to user by this two ways:

1. T-SQL: GRANT VIEW SERVER STATE TO YourLogin

2. GUI:

Step 1: Right mouse click the SQL Server and go to Properties. You need to give GRANT to “View server State’ for that user.

151

Let me know it it helps you.

h1

SQL SERVER: How to enable ‘Ad Hoc Distributed Queries’ SQL SERVER 2005

03/19/2009
Frequently, we need to use OPENROWSET queries to connect to remote database servers. To enable this feature on SQL Server 2005, you should first configure the database to enable Ad Hoc Distributed Queries.

We can Enable this feature by two ways:

1. SQL Server Surface Area Configuration.

2. by sp_configure option.

Lets check with first way, by SQL Server Surface Area Configuration.

Open surface Area configuration, you will get this screen:

131

Click on second option, Surface Areas Configuration for Features. you will get this screen, where you need to check to Enable OPENROWSET and OPENDATASOURCE support.

132

Lets see second option to enable this feature with sp_configure option:

sp_configure

If you run this command, you will lists of SQL configuration settings. There are 14 items in the list in which ‘Ad Hoc Distributed Queries’ is not exist. To see this, we need to enable the ‘show advanced options’ configuration parameter.

You can enable advance options by:

sp_configure ‘show advanced options’,1

When we run this command we will get this message:

“Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.”

so we need to execute reconfigure command as:

reconfigure

so now if we run sp_configure again, we will get result set as follows:

133

Here, we can find that config_value for “Ad Hoc Distributed Queries” is “0”. We need to set it to 1 to enable this feature. so to do that we need to use following:

sp_configure ‘Ad Hoc Distributed Queries’,1
reconfigure

so, now if we run sp_configure, we will get result as follows:

134

Here, we can find that now config_value for “Ad Hoc Distributed Queries” is “1”.

That’s it, now you can use OPENROWSET and OPENDATASOURCE to connect with remote database without Linked server.

Let me know if it helps you in any way.

h1

SQL SERVER: Get column values as comma seperated list using XML PATH() instead of UDF’s using SQL COALESCE

02/28/2009

Today I have the following situation, where I need to display all related data in comma seprated list.

Till today we are using scalar function to display Comma separated list with select statement. That scalar function use the COALESCE() to make comma separated list. Today I found wonderful solution to display comma separated list without scalar function. Let see that.

Scenario:

I have Table like:

CREATE TABLE #test(
field1 VARCHAR(5), field2 VARCHAR(5)
)

Lets insert some data in this table:

INSERT INTO #test
SELECT '001','AAA'
UNION ALL
SELECT '001','BBB'
UNION ALL
SELECT '002','CCC'
UNION ALL
SELECT '003','DDD'
UNION ALL
SELECT '004','EEE'
UNION ALL
SELECT '004','FFF'
UNION ALL
SELECT '004','GGG'

So now my table has Data like:

Get Comma separated List

Get Comma separated List

I want output like:

Get Comma separated List

Get Comma separated List

I come up with very good solution. Let me share with all of you:

Get Comma separated List

Get Comma separated List

My Output will be:

Get Comma separated List

Get Comma separated List

Please make comments, if this helps you in any way

h1

SQL SERVER: REPLICATE function

02/26/2009

Today i read SQLAuthority.com, and I found that one developer
has this issue. Lets see that problem as well as the solution
for the same.

There is one numeric column. User needs to make
sure that all data should be of same size.

We need to display as:

17.00 TO 17.00
2.00 TO 02.00
8.17 TO 08.17

So I found REPLICATE function of SQL SERVER, to fix this.

How to use REPLICATE function?

REPLICATE (“string that you want TO append” ,”INTEGER
VALUE” )

FIRST parameter, i need TO
SET the CHARACTER, which
will append it BEFORE the NUMBER.

SECOND parameter, how many
times this CHARACTER should be ADD TO the NUMBER.

Example:
DECLARE @t AS NUMERIC(8,2)
SELECT @t = 08.2
SELECT Cast(Replicate(0,6-Len(@t)) AS VARCHAR(5)) + Cast(@t AS VARCHAR(5))

Here I specify that there should be 5 length. In this case 8.23 has four digit, so this will add one “0” to this number.

We can append any character by just changing the First Parameter, String value.

Let me know your suggestions