Posts Tagged ‘T-SQL’

h1

SQL SERVER: Reset Setup Values, when SQL SERVER is started/restarted

07/15/2009
We have a requirements to clear all setup values when SQL SERVER is started/restarted and we need to setup default values to setup table.

I found one Stored Procedure provided by MS SQL SERVER. Let me share it with all of you.

SQL SERVER provides this SP: “sp_procoption”, which is auto executed every time when SQL SERVER service has been started. I found this SP and it helps me to figure it out the solution for the request as following way. Let me show you how to use it

Syntax use this SP:

EXEC SP_PROCOPTION     
@ProcName = 'SPNAME',    
@OptionName = 'startup',    
@OptionValue = 'true/false OR on/off'

@ProcName, should be Stored procedure name which should be executed when SQL SERVER is started. This stored procedure must be in “master” database.

@OptionName, should be “startup” always.
@OptionValue, this should be set up to execute this given sp or not. If it is “true/on”, given sp will be execute every time when SQL SERVER is started. If it is “false/off”, it will not.
That’s it, lets take an example.
I have one Database called Test, I have created setup table:
CREATE TABLE SetupTable(
Seq INT IDENTITY,
Code VARCHAR(100)
)

Lets insert some default values to this table:

INSERT INTO SetupTable VALUES('A')
INSERT INTO SetupTable VALUES('B')
INSERT INTO SetupTable VALUES('C')
INSERT INTO SetupTable VALUES('D')

What I need to do is, I need to wipe out this values when SQL SERVER is started and fill it with the same default values, because these values might be updated by application.So, I created one stored procedure in master database, named,

CREATE PROC ClearAllData AS

DELETE
FROM    Test.dbo.SetupTable

INSERT INTO SetupTable VALUES('X')
INSERT INTO SetupTable VALUES('Y')
INSERT INTO SetupTable VALUES('Z')

and set up this stored procedure as auto executed every time when SQL SERVER is started as:

EXEC SP_PROCOPTION     
              @ProcName = 'ClearAllData',   
              @OptionName = 'startup',   
              @OptionValue = 'true'
Now, restart SQL SERVICES, and you find that old values will be deleted and new values with ‘X’, ‘Y’, and ‘Z’ will be inserted automatically.
If now you want to stop it to execute automatically, we just need to execute this with “false” as:
EXEC SP_PROCOPTION     
               @ProcName = 'ClearAllData',    
               @OptionName = 'startup',   
               @OptionValue = 'false'
I hope this is very clear to use this feature.
Reference : Tejas Shah (http://www.SQLYoga.com)
h1

SQL SEVER: How to make an Entry in Event Viewer using SQL SERVER

04/02/2009
Today I came across the situation where I need to following scenario.

I need to make entry in Event Viewer when there is an error in Stored Procedure.

I wondered to do this, but finally I come up with the solution. SQL is much powerful.

SQL provides us to make an entry in Event Viewer by two ways:
1. using XP_LogEvent

2. By Raiserror WITH LOG

Lets first see the way using XP_LogEvent:

Here I have created one SP which will raise an error “Divide by zero error encountered.” as I tried to do “10 / 0”.

CREATE PROCEDURE TestEventViewer
AS
BEGIN TRY
    SELECT  10/0
END TRY
BEGIN CATCH
    PRINT ERROR_Message()
    DECLARE @msg VARCHAR(100)
    SELECT @msg = ERROR_Message()

    EXEC xp_logevent 60000, @msg, informational

END CATCH

Lets Execute this SP:

EXEC TestEventViewer

This will write entry in Event Viewer. Now open Event Viewer. You can find Event Viewer at Control Panel –> Administrative Tools –> Event Viewer.

You will get en entry of Error there.

171

So we can do this by Extended Stored Procedure: “xp_logevent”.

Let see the parameters of this Procedure.

First Parameter: “60000” is the Error Number

Second Parameter: “@msg” is the message to be displayed in Event Viewer.

Third Parameter: “informational” is the Error Level. It could be “informational”, “Error”, “Warning”.

Now, Lets see by another way By Raiserror WITH LOG:

It is the same way as we used Raiserror to Raise an Error.

CREATE PROCEDURE TestEventViewer
AS
BEGIN TRY
    SELECT  10/0
END TRY
BEGIN CATCH
    PRINT ERROR_Message()
    DECLARE @msg VARCHAR(100)
    SELECT @msg = ERROR_Message()

    RAISERROR(@msg, 11, 1) WITH LOG

END CATCH

Lets Execute this SP:

EXEC TestEventViewer.

So By these ways we can make an entry to Event Viewer.

Let me know if it helps you in any way

h1

SQL SERVER: How much space occupied by Each Table with sp_MSforeachtable procedure

03/26/2009

Today, I came across requirement where I need to perform an action on all of the tables within a database.

For example, How much space occupied by each table.

I found undocumented Procedure: sp_MSforEachTable in the master database.

The following script reports the space used and allocated for every table in the database.

USE AdventureWorks;
EXECUTE sp_MSforeachtable ‘sp_spaceused [?]’

So, We can use sp_MSforeachtable procedure when we need to loop through each table.

Let me know if it helps you in any way.

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.