Posts Tagged ‘T-SQL Syntax’

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)
Advertisements
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: 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

h1

How to find all places where object (Table/view/Text etc..) is used

02/16/2009

Recently I have one change in a table schema. To make this change I need to be sure that it should not raise an error. To make suare that I need to get all the places where I used this table in whole DB and my DB is too big.

So I triued for that and come with the solution, that I need to share with all of you.

I wrote one query which will give me all the objects in which i have used the TEXT that i want to search.

Query for SQL 2005:

SELECT DISTINCT o.name
FROM sysobjects o
INNER JOIN syscomments c ON c.Id = o.Id
WHERE category = 0 AND c.text like ‘%’ + ‘<TableName>’ + ‘%’
ORDER BY o.name

So it save my life. I found all the objects where I used that table and fix if needed. Let me know if this can help you !

h1

SQL SERVER – Get Time in AM/PM Format

12/22/2008

Today i come with one situation where I need to get Time in AM/PM format.

I tried with different way and come up with the simple solution, that i am going to share with all of you.


SELECT
GETDATE() AS CurrentDate,
RIGHT(CONVERT(VARCHAR, GETDATE(), 100),7) AS CurrentTime,
CONVERT(VARCHAR(10), GETDATE(), 101) + + RIGHT(CONVERT(VARCHAR, GETDATE(), 100),7) AS CurrentDateTime

SQL DateTime

Syntax to Get SQL DateTime With Am/Pm Format

SQL Date time in AM/PM Format

Get SQL Date time in AM/PM Format

I have read very interesting article on SQL Authority.Com . You can find the many ways to get date in different formats.