Friday, April 23, 2010

Splitting CSV string using CTE

DECLARE @CSVString varchar(2000)
DECLARE @Delimiter varchar(1)
SELECT 
      @CSVString = 'This,is,a,test,for,splitting,using,a,CTE,to,break,a,varchar,into,records,based,on,a,delimiter',
      @Delimiter = ','
SET @CSVString = @CSVString+@Delimiter  --- append the delimiter 
;WITH Res(s, r)
AS
(
SELECT
SUBSTRING(@CSVString,1, CHARINDEX(@Delimiter, @CSVString)-1) s,
SUBSTRING(@CSVString,CHARINDEX(@Delimiter, @CSVString)+1, len(@CSVString)) r
UNION ALL
SELECT
SUBSTRING(r,1, CHARINDEX(@Delimiter, r)-1) s,
SUBSTRING(r,CHARINDEX(@Delimiter, r)+1, len(r)) r
FROM Res
WHERE
CHARINDEX(@Delimiter, r) > 0
)
SELECT s FROM Res

Monday, April 19, 2010

Some Licensing FAQ

What is a CAL?

A CAL is a Client Access License. This type of license grants one network user or device permission to access a network service (such as a SQL or Exchange Server). There are also different types of CALs for each service.

What is the difference between per seat, per server, and per processor?

The Per seat licensing model refers to the total number of computers allowed to connect to the service on that machine. Each connection is called a Client Access License. In per seat mode a single CAL for each workstation can be used to access multiple servers in per Seat mode. This is usually the most economical option

In Per Server licensing, each CAL is dedicated to that specific server (think of this as concurrent connections).

In per Processor mode, no CALs are needed to connect to the server. The right to access the server is covered by the (more expensive) Software License itself.

Refer to http://www.microsoft.com/licensing for more information.

What is the difference between a Device CAL and a User CAL?

A Device CAL is specific to a workstation whereas a User CAL is specific to a user. For example, if you have one user connecting to a SQL server from 2 different machines, you would need 2 SQL Server Device CALs or 1 SQL Server User CAL.

Thursday, April 15, 2010

Using SET XACT_ABORT

SET XACT_ABORT specifies what action SQL Server should take following run-time errors. The default session setting is SET XACT_ABORT OFF, which indicates that only the Transact-SQL statement that raised the error is rolled back and the transaction continues. Depending on the severity of the error, the entire transaction and/or batch may be rolled back even with SET XACT_ABORT is OFF.

A side effect of SET XACT_ABORT OFF is that a cancel/timeout error can leave an open transaction so it’s the client’s responsibility to cleanup following cancel/timeout. To safeguard against leaving an open transaction, applications that execute transactions with SET XACT_ABORT OFF need to roll back transactions and perhaps close the connection following SQL exceptions.

Note that with connection pooling, simply closing the connection without a rollback will only return the connection to the pool and the transaction will remain open until later reused or removed from the pool. This can result in locks begin held unnecessary and cause other timeouts and rolling blocks.

SET XACT_ABORT ON instructs SQL Server to rollback the entire transaction and abort the batch when a run-time error occurs. Compile errors (e.g. syntax errors) are not affected by SET XACT_ABORT.

In my experience, SET XACT_ABORT ON provides the desired behavior in most cases. I’ve never run into a situation where I wouldn’t want to rollback a transaction following a cancel or timeout. I nearly always specify SET XACT_ABORT ON in stored procedures that contain explicit transactions to ensure that transactions are rolled back even if the application code doesn’t clean up properly. The only time I don’t use XACT_ABORT is in rare cases where I need to trap and handle specific errors in Transact-SQL and continue.

I strongly recommend that SET XACT_ABORT ON be included in all stored procedures with explicit transactions unless you have a specific reason to do otherwise. The consequences of an application unwittingly performing work on a connection with an open transaction are disastrous.

SET XACT_ABORT ON is still needed with structured error handling. A client attention event (timeout or query cancel) will stop the executing code and the CATCH block will not be invoked. This could leave an open transaction unless the application code performs a rollback or closes the connection.
When you use XACT_ABORT with structured error handling, you should check XACT_STATE() in the CATCH block to determine whether or not COMMIT/ROLLBACK is possible

XACT_ABORT changes error handling behavior but doesn't change locking when no explicit transaction is specified. Without an explicit transaction, each statement is in an individual transaction that will be committed
or rolled back when the statement completes (successsfully or not) regardless of the XACT_ABORT setting. I think the only advantage of XACT_ABORT without an explicit transaction is that it can prevent the remainder of proc from continuing after an error even when the proc contains no error handling code:

CREATE PROC dbo.foo
AS
SET XACT_ABORT ON;
EXEC sp_executesql N'select * FROM MissingTable';
EXEC sp_executesql N'select * FROM AnotherMissingTable'; --never reached
GO
EXEC dbo.foo
GO

Wednesday, April 14, 2010

Formatting Money datatypes

Quite often people ask how to format numeric/money formats in more readable forms separated by commas. The same CONVERT function we use to format the date’s can be used for the same purpose.

Only money datatypes can be formatted like this, if you want to format a decimal/numeric fields, you need to cast them using the CAST function.

 

DECLARE @Amount MONEY
SELECT @Amount = 123456.7899
--No formatting
SELECT CONVERT(VARCHAR,@Amount,2)    -- 123456.7899
--Rounded but no formatting
SELECT CONVERT(VARCHAR,@Amount,0)    -- 123456.79    
--Formatted with commas
SELECT CONVERT(VARCHAR,@Amount,1)    -- 123,456.79 

List Object level permissions

 
SELECT c.action, o.name, b.name 
FROM sys.objects o
INNER JOIN sysprotects c
      ON o.object_id = c.id
INNER JOIN sysusers b
      ON c.uid = b.uid
WHERE o.type IN ('U','P','V')
AND b.name = 'LoginName'

Thursday, April 1, 2010

System views – login/permissions

---logins:

SELECT
*
FROM
sys.server_principals


 

--database users (run in the db)

SELECT
*
FROM
sys.database_principals


 

--db roles


 

SELECT dp.type_desc, dp.name, p.name

FROM
sys.data base_role_members r

JOIN
sys.database_principals dp ON r.role_principal_id = dp.principal_id

JOIN
sys.database_principals p ON r.member_principal_id = p.principal_id


 

--server roles:

SELECT dp.type_desc, dp.name, p.name

FROM
sys.server_role_members r

JOIN
sys.server_principals dp ON r.role_principal_id = dp.principal_id

JOIN
sys.server_principals p ON r.member_principal_id = p.principal_id


 

--db perms:

SELECT class_desc, permission_name, p.name


FROM
sys.database_permissions dp

JOIN
sys.database_principals p ON dp.grantee_principal_id = p.principal_id


 

--server perms:

SELECT class_desc, permission_name, p.name


FROM
sys.server_permissions dp

JOIN
sys.server_principals p ON dp.grantee_principal_id = p.principal_id