What SQL job in SQL Server

My x colleague ask me how to query SQL job in SQL Server
Normally I use the below script

use msdb
select name from sysjobs
where enabled=1

Media - For DBA

From Microsoft TechNet
example of Title :
LINQ and Entity Framework, for the DBA
SQL Services - What does it mean for the DBA

and lot of Real World DBA Episode ...

Find all media here from Microsoft Technet

SQL Server 2005 ranking functions - RANK(), DENSE_RANK(), NTILE()

http://msdn.microsoft.com/en-us/library/ms189798.aspx
Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.


Good explanation of ranking function

Good example of ranking function

SQL Free monitor tools

SQL HeartBeat, Monitor your sql server from SQLSolutions
http://www.sqlsolutions.com/products/Heartbeat/index.html

SQL Check, Monitor your sql server from Idera
http://www.idera.com/Products/Free-Tools/SQL-check/

Login is locked out when enforce password policy is on

MY SQL 2005 login is set with “Enforce password policy” turn on

Check what mean by Enforce password policy:
In Start->Administrative tools->Local Security Settings
Security Settings-> Account Policies -> Account Lockout Policy

Account Lockout threshold - 5 invalid login attempts
If user keying 5 invalid login attempts, account will be lock.

Account lockout duration - 30 minutes
account will be lock for 30 min before automatically becoming unlocked

Reset account lockout counter after 30 minutes
This security setting determines the number of minutes that must elapse after a failed logon attempt before the failed logon attempt counter is reset to 0 bad logon attempts

I use test as login id and keying 5 invalid password.
I try next login with correct password, I can’t login into this SQL 2005 server.





I check from Microsoft SQL Server Management Studio Server name->Security-> test
Double click on test (login name), select Status->Under
Status -> SQL Server authentication ->
"Login is locked" out is checked.

It mean my login account been locked.

I unchecked "account been locked" and press "OK".
I login with the correct password, the login still fail.
I double click the SQL login again, "account been locked" is still "checked"

I unchecked Enforce password policy, unchecked Login is locked out" and press OK.
I login with correct password, but login still fail.

Solution:
1. Change password with TSQL
-ALTER LOGIN test WITH PASSWORD = 'complexpassword',CHECK_POLICY = ON;
-I login with complexpassword, login successful

2. Change password from Microsoft SQL Server Management Studio
-Microsoft SQL Server Management Studio -> Server name->Security-> test
-Double click on test (login name)->General -> Keying new password in Password and Confirm Password and press "OK"
-Double click on test (login name)->Status ->SQL Server authentication: -> Login is locked out
-I login with complexpassword, login successful


Reference
Account lockout duration


Reset account lockout counter after

MSOLAP Provider used in connection string

We move asp page from live server to new setup server.
When asp page connect to our olap server, it show the below message:

Microsoft® OLE DB Provider for OLAP Services
error '80004005' OLAP server error: The operation requested failed due to security problems (the user could not be authenticated).

Our connection string as below and same with live server
provider=msolap;data source=servername;initial catalog=customer;user id=user;password=xxx;sspi=anonymous

After some Google and found this article by my colleague
How to check the MSOLAP Provider used in connection string

Some extraction of article as below:
MSOLAP ---Default Provider
MSOLAP.1 ---OLAP 7.0 Provider (msolap.dll)
MSOLAP.1 ---Analysis Service 2000 Provider (msolap80.dll)
MSOLAP.1 ---SSAS provider (msolap90.dll)

HKCR\MSOLAP ---is the default and used when MSOLAP is selected in the connection string, the file msolap.dll, msolap80.dll or msolap90.dll that is latest registered will be the default.
HKCR\MSOLAP.1 ---is the registry key for OLAP version 7, file msolap.dll
HKCR\MSOLAP.2 ---is the registry key for AS2000, file msolap80.dll
HKCR\MSOLAP.3 ---is the registry key for AS2005, file msolap90.dl


We change the connection string to
provider=msolap.3;data source=servername;initial catalog=customer;user id=user;password=xxx;sspi=anonymous
and it work

SQL process, percentage of complete

I restore one database and want know how many percent the SQL process will be complete?

Step
1. Restore database test from disk='c:\test.mdf'
2. We can check which session belong to this process with running the below command
sp_who2 'active'
3. Check on command column, and look for "Restore database"
My SPID for this "Restore database" = 57
4. Run the below command and look for session_id=57

Select session_id,percent_complete,estimated_completion_time,total_elapsed_time
From sys.dm_exec_requests
Where session_id=57

session_id percent_complete estimated_completion_time total_elapsed_time
---------- ---------------- ------------------------- ------------------
57 26.47547 5800366 2090266

The above result show the SQL process already complete 26.47%, estimated_completion_time=5800366 and total_elapsed_time=2090266

This above example is use to show how to use sys.dm_exec_requests to show how many percent_complete of SQL process.
We can also run the below command to show percentage complete of restore process.

Restore database test from disk='c:\test.mdf' with stats
10 percent processed.
20 percent processed.
30 percent processed.
....

Disable SQL Guest user

For SQL 2005
1.
Microsoft SQL Server Management Studio -> SQLServer name-> database -> database name-> right click -> property - >Permission

User/roles -> select guest -> un-check "connect"

2.
USE
GO
REVOKE CONNECT FROM GUEST
GO

Reference
SQL Server Database Guest User Account

SQL connection string

All type of sql connection string for ODBC, OLE DB , SQLConnection.net

refer to this site

SQL Data Type - Bit

We have SQL 2000 and SQL 2005 server
we run this in SQL 2000 server and SQL 2005 Server

DECLARE @bit bit
SET @bit = 'TRUE'
IF @bit = 1
PRINT 'Yes, this is true!'
ELSE
PRINT 'No, this is FALSE!'


SQL 2000 result :
Server: Msg 245, Level 16, State 1, Line 3
Syntax error converting the varchar value 'TRUE' to a column of data type bit.

SQL 2005 result :
Yes, this is true!


SQL 2000 bit data type won't accept 'True'!

thanks for my colleague for this finding.


SQL 2000 - Bit
Integer data type 1, 0, or NULL.

SQL 2005 - Bit
An integer data type that can take a value of 1, 0, or NULL.
The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.

Loop through all database and do something

I code this :

declare @database varchar(50)
declare @strSwitchDB nvarchar(100)
declare @count int
declare @maxRow int
SET @count = 1
SELECT @maxRow=max(database_id) FROM sys.databases
WHILE (@count <= @maxRow)
BEGIN
SELECT @database = name FROM sys.databases WHERE database_id = @count
set @strSwitchDB='use '+@database
print 'do something here, processing'
EXEC sp_executesql @strSwitchDB
set @count=@count+1
END

I expected the process will run in all databases in SQL server
example :
use master
do something
use tempdb
do something
use msdb
do something


but it actually no return expected result. The process will run in same database instead run in all database.

example
use master
do something
do something
do something

I actually find another way to do this
if we want to loop through all database and do something,
we actually can write in one line

EXECUTE sp_msforeachdb 'SELECT ''[?]'', count(1) from [?].dbo.sysobjects'

Result
It actually loop through all the database and count number of row in sysobjects table
-------- -----------
[master] 3594
-------- -----------
[tempdb] 67
------- -----------
[model] 47
------ -----------
[msdb] 754

JFYI,The above is for example and not the the actually action I want to perform

Trigger - After, Instead of

Trigger - After
An UPDATE trigger is used to perform an action after an update is made on a table

Trigger - INSTEAD OF
An Instead of trigger is used to replace the incoming statement with statemement declare in trigger.

Reference
Introduce to Trigger

Making use of INSTEAD-OF triggers in SQL Server 2005
This article explain why we use Instead-of triggers, and give example of Instead-of Triggers.

Using INSTEAD OF In Place of Triggers In SQL Server 2000
This article gives good example of "Instead of trigger". In the example, instead of trigger is used when we want replace insert statement with customize message.

Notes:
- We cannot create an AFTER trigger on views.
- AFTER triggers gets executed automatically after the PK and FK constraints.
- INSTEAD OF triggers can be created on views
- you cannot create two INSTEAD OF triggers on the view/ table for the same event.
- INSTEAD OF DELETE and INSTEAD OF UPDATE triggers cannot be defined on tables that have corresponding ON DELETE or ON UPDATE cascading referential integrity defined.

reference : Implementing Triggers in SQL 2000

Order by "Alias Name"

We have this query:

create table testme11
(id int identity(1,1),
source_name varchar(20),
source_description varchar(20))

insert into testme11 values ('A',NULL)
insert into testme11 values (NULL,'Q')
insert into testme11 values ('E',NULL)
insert into testme11 values (NULL,'C')

select * from testme11
id source_name source_description
----------- -------------------- --------------------
1 A NULL
2 NULL Q
3 E NULL
4 NULL C

(4 row(s) affected)


Are this three queries will return the same result?
A.
SELECT id,ISNULL(source_name,source_description) AS source_name
FROM testme11 src
order by src.source_name

B.
SELECT id,ISNULL(source_name,source_description) AS source_name
FROM testme11 src
order by source_name

C.
SELECT id,ISNULL(source_name,source_description) AS source_name
FROM testme11 src
order by ISNULL(source_name,source_description)

result A:
id source_name
----------- --------------------
2 Q
4 C
1 A
3 E
(4 row(s) affected)

result B:
id source_name
----------- --------------------
1 A
4 C
3 E
2 Q

(4 row(s) affected)


Result C:
id source_name
----------- --------------------
1 A
4 C
3 E
2 Q

(4 row(s) affected)

Query B & C will return same result, while Query A won't return expected result