Random Data Sampling Queries in SQL Server 2005

The following query will return a random sampling of data:

SELECT * FROM PHTTEMPORDERSHEET TABLESAMPLE SYSTEM (1 PERCENT);
SELECT * FROM PHTTEMPORDERSHEET TABLESAMPLE SYSTEM (100 PERCENT);


SELECT * FROM PHTTEMPORDERSHEET TABLESAMPLE SYSTEM (20 ROWS)
SELECT * FROM PHTTEMPORDERSHEET TABLESAMPLE SYSTEM (200 ROWS)


Not only will the records returned be random, but so will the number of rows.

Understanding "login failed" (Error 18456) error messages in SQL Server 2005

In continuing with the theme of understanding error messages I'll discuss the "login failed" messages that are surfaced by the client and written to the server's error log (if the auditlevel is set to log failures on login which is the default) in the event of an error during the login process.

If the server encounters an error that prevents a login from succeeding, the client will display the following error mesage.

Msg 18456, Level 14, State 1, Server , Line 1
Login failed for user ''

Note that the message is kept fairly nondescript to prevent information disclosure to unauthenticated clients. In particular, the 'State' will always be shown to be '1' regardless of the nature of the problem. To determine the true reason for the failure, the administrator can look in the server's error log where a corresponding entry will be written. An example of an entry is:

2006-02-27 00:02:00.34 Logon Error: 18456, Severity: 14, State: 8.

2006-02-27 00:02:00.34 Logon Login failed for user ''. [CLIENT: ]

n

The key to the message is the 'State' which the server will accurately set to reflect the source of the problem. In the example above, State 8 indicates that the authentication failed because the user provided an incorrect password. The common error states and their descriptions are provided in the following table:

ERROR STATE	ERROR DESCRIPTION
2 and 5	Invalid userid
6	Attempt to use a Windows login name with SQL Authentication
7	Login disabled and password mismatch
8	Password mismatch
9	Invalid password
11 and 12	Valid login but server access failure
13	SQL Server service paused
18	Change password required

Other error states indicate an internal error and may require assistance from CSS.

Ref Link :http://blogs.msdn.com/

MS SQL Stored procedure with XML

I find that it's easiest to visualize if you start with a very simple XML document. Here is an example XML snippet:

Here is how that stored procedure would be written:

alter procedure usp_InsertManyRows

@XMLDOC varchar(8000)

AS

declare @xml_hndl int

--prepare the XML Document by executing a system stored procedure

exec sp_xml_preparedocument @xml_hndl OUTPUT, @XMLDOC

--insert into table

Insert Into testtable

(

InsertedID

)

Select

IDToInsert

From

OPENXML(@xml_hndl, '/items/item', 1)

With

(

IDToInsert int '@id'

)

Now follows a comparison of how each performs. The following tests were run with simple ASP.NET pages using a local SQL Server database. The results are quite dramatic, and would only be more dramatic if the hits had to go over the network.