SQL statement along with its active SPIDS

Find out the actual SQL statement along with its active SPIDS that are currently running and not just the last statement sent to SQL server (as DBCC INPUTBUFFER displays). Below scripts will display the current activity of ALL spids that are currently active along with its SQL statements.

--SQL 2000 Syntax
SET NOCOUNT ON
GO
DECLARE
@SPID INT,
@last_Batch datetime,
@hostname varchar(32),
@loginame varchar(32),
@bHandle BINARY(20),
@stmt_start INT,
@stmt_end INT,
@waittime int

--Get spids in loop, only where there is some statement in the buffer.
SET @SPID=(SELECT MIN(SPID) FROM Master.dbo.SYSPROCESSES WHERE (stmt_start<>0 or stmt_end<>0) and SPID<>@@SPID)

WHILE @SPID IS NOT NULL
BEGIN
SELECT
@last_Batch=last_batch,
@hostname=hostname,
@loginame=loginame,
@bHandle=sql_handle,
@stmt_start = stmt_start/2,
@stmt_end = stmt_end/2,
@waittime = waittime
FROM
MASTER.DBO.SYSPROCESSES
WHERE SPID = @SPID AND ecid = 0

IF @stmt_end = 0
SELECT @SPID,@last_batch,
@hostname,
@loginame,
SUBSTRING(text,@stmt_start,8000)
FROM ::fn_get_sql(@bHandle)
ELSE
SELECT @SPID,@last_batch,
@hostname,
@loginame,
SUBSTRING(text,@stmt_start,@stmt_end - @stmt_start)
FROM ::fn_get_sql(@bHandle)

--GET NEXT SPID
SET @SPID=(SELECT MIN(SPID)
FROM Master.dbo.SYSPROCESSES
WHERE (stmt_start<>0 or stmt_end<>0)
AND SPID<>@@SPID
AND SPID>@SPID)
END
GO

--SQL 2005 Syntax
SELECT
s2.session_id,
s2.start_time,
s1.host_name,
s1.login_name,
s2.command,
s2.open_transaction_count,
(SELECT TOP 1 SUBSTRING(s3.text, statement_start_offset / 2,
((CASE WHEN statement_end_offset = -1 THEN
(LEN(CONVERT(nvarchar(max),s3.text)) * 2)
ELSE statement_end_offset
END) - statement_start_offset) / 2)) AS sql_statement
FROM Master.sys.dm_exec_sessions s1
INNER JOIN Master.sys.dm_exec_requests s2 on s1.session_id=s2.session_id
CROSS APPLY Master.sys.dm_exec_sql_text(s2.sql_handle) AS s3
WHERE s2.sql_handle is NOT NULL
AND s2.session_id<>@@SPID

SQL Server backup across more than one file

You can stripe a SQL Server’s database backup across more than one file. Striping a SQL Server backup across more than one backup file can provide performance advantages as well as make it possible to backup a VLDB where there is no single drive available with enough free space. Striped backups are supported in SQL Server 2000 & SQL Server 2005.

-- take a striped backup of the DB. Can be many more files than just two.
backup database northwind
to disk='\\Server1\t$\northwind1.bak',
disk='\\Server2\h$\northwind2.bak'

-- take a look at the logical files in the DB so we can move them on restore
restore filelistonly
from disk='\\Server1\t$\northwind1.bak',
disk='\\Server2\h$\northwind2.bak'

-- restore a DB from a striped backup
restore database northwind
from disk='\\Server1\t$\northwind1.bak',
disk='\\Server2\h$\northwind2.bak'
with move 'northwind_Data' to 't:\northwind2.mdf',
move 'northwind_Log' to 'h:\northwind2.ldf',
replace

GotDotNet Code Gallery

Are you looking for a place where you can share, find, download, evaluate and discuss evolving .NET applications, uncompiled code, ideas, and technical documents?
You have a greate place to go now, CodeGallery at http://www.gotdotnet.com/codegallery/

SQL Server 2005 - Default Trace

SQL Server 2005 out of the box comes with a default trace that's always running which tracks configurational changes and process level information. You may obtain this information from SQL Server Management Studio by higlighting the registered server and selecting "Reports" from the summary page:

You will find:
Configuration change history
Schema Changes History
Memory Consumption
All Blocking Transactions
Top Sessions
Top Connections
Top Transactions by Age
Top Queries by Average CPU time
Top Queries by Average IO & lot of other information

You may also query the default trace file using the below query:
SELECT * FROM fn_trace_gettable ('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc', default)
GO

SQL 2005 COPY_ONLY Backup

In SQL Server 2000 or it's earlier versions, if you perform a backup of a database out of sequence/chain (Full & Diffrential or Full & Log, etc) it used to break the sequence of the backups. Meaning you had to reset the backup sequence everytime you had to take a seperate out of sequence backups of the database. Taking a backup normally changes the database, in turn affecting other backups and how they are restored. Sometimes, however, a backup must be taken for a special purpose that should not affect the overall backup and restore sequence or procedures for the database.

With SQL Server 2005, you can now perform an out of sequence backups using "COPY_ONLY" option with the backup statement and this option is available for all types of backups.

Note:
A Full backup taken with the COPY_ONLY option cannot be used as a base backup and does not affect any existing differential backups.
A Diffrential backup taken with the COPY_ONLY option is identical to a regular diffrential backup.
A Log backup taken with COPY_ONLY option causes the backup to retain the current log archive point and also the Transaction log is not truncated by a log backup.
Important: SQL Server Management Studio does not support COPY_ONLY backup/restore functionality, but you can use BACKUP & RESTORE commands using T-SQL for COPY_ONLY backup of a database.
Example:
Backup database AdventureWorks to Disk='D:\AdventureWorks.bak' with COPY_ONLY

Can a Web Application with no SSL use ADFS Web Agent NT Token to authenticate users?
It would not work because it require SSL and to mark cookies as secure only. Cookies are not encrypted and without SSL they could be easily attacked. The return URL must be an https-based URL.

Is there a sample of single sign-on using ADFS?
Here is a step by step guide to setup ADFS:
http://www.microsoft.com/downloads/details.aspx?familyid=062F7382-A82F-4428-9BBD-A103B9F27654&displaylang=en

How to use ADSchemaAnalyzer tool?
For the ADSchemaAnalyzer tool, the target schema is the AD instance and the base schema is the ADAM instance.
To use the AdamSyncer tool you need to do the following:
Import the LDIF files MS-AdamSchemaW2K3.LDF and MS-AdamSyncMetadata.LDF into the Adam instance.
Using the AdSchemaAnalyzer tool check if the schema of the AD instance and ADAM instance are the same.
Open the AdSchemaAnalyzer tool from c:\windows\adam
Choose File / Target, set server: localhost:389
Choose File / Base, set server: localhost:50000
Select Schema / Mark all non-present elements as included
Select File / Create Ldif file.
Save to r2-diff.ldf

In C#, I take an entity and serialize it to XML and pass it to a stored procedure which takes the data and inserts it into a table. I have several datetime properties within my entity and when I serialize it to xml, the datetime looks like this:
2005-11-14T13:37:38.1516436-06:00.
Here is the C# code that serializes the entity:
XmlSerializer xmls = new XmlSerializer(typeof(SaleItem));
using (MemoryStream ms = new System.IO.MemoryStream())
{
xmls.Serialize(ms, si);
ms.Seek(0, SeekOrigin.Begin);
StreamReader tr = new StreamReader(ms);

return tr.ReadToEnd();
}
Anyway, sp_xml_preparedocument does not like the datetime value generated by the XmlSerializer. The only way I know of to get around this problem is to:
1) Hand walk the entity and create my own XML (don’t use XmlSerializer)
2) Parse the string and get rid of the milliseconds (after 3 characters) and the -06:00.
Either way seems kind of like a pain and does not flow very well. Does anybody have a better way to do this?
The problem are indeed the beyond millisecond resolution and the timezone indicator. Can you generate your value with milliseconds only and the timezone Z or no timezone instead? Use casting rules for that in SQL Server 2005. In SQL Server 2005, you can write:

declare @x xml
set @x = N'2005-11-14T13:37:38.1516436-06:00'
select @x.value('xs:dateTime(/EndDateTime[1])', 'datetime')

Note that this however performs two casts, so if you can change the value generation, that would be better.
Alternatively, if you can provide a schema that types the element as xs:dateTime, you will not have to explicitly cast it to xs:dateTime.

Is there another way I can create the SSL certificates for ADFS than using the SelfSSL tool?
If you are building proof-of-concept software and not ship bits, you can use makecert.exe to generate one for you.
Here is an MSDN link with the necessary information:
http://msdn2.microsoft.com/en-us/library/ms186362.aspx

Calculate the Last Logon Time in Windows 2003

There is a nice article in technet website about calculating the Last Logon Time in Windows 2003 Active Directory.
Here the script:
Set objUser = GetObject("LDAP://cn=Ken Myer, ou=Finance, dc=fabrikam, dc=com")
Set objLastLogon = objUser.Get("lastLogonTimestamp")
intLastLogonTime = objLastLogon.HighPart * (2^32) + objLastLogon.LowPart
intLastLogonTime = intLastLogonTime / (60 * 10000000)
intLastLogonTime = intLastLogonTime / 1440
Wscript.Echo "Last logon time: " & intLastLogonTime + #1/1/1601#

We must know few catches around it:
- lastLogonTimestamp attribute in Win 2003 keeps track of the last time a user logged on to the domain, and also replicated from one domain controller to another.
- lastLogonTimestamp is replicated only once every 14 days. This helps limit replication traffic, although it also means that the lastLogonTimestamp for any given user could be off by as much as 14 days.

More information at http://www.microsoft.com/technet/scriptcenter/topics/win2003/lastlogon.mspx

Service Pack 1 (SP1) for ADAM

Service Pack 1 (SP1) for Active Directory Application Mode (ADAM) for the English language is now available.
http://www.microsoft.com/windowsserver2003/adam/default.mspx

ADAM SP1 new features are:
- Active Directory to ADAM Synchronizer tool.
- ADAM users can bind to an ADAM instance by using Digest authentication.
- Active Directory Schema Analyzer tool.
- Newer version of LDP tool with ACL editor.
- Password chaining to AD users through ADAM proxy objects
- Users can be created in the configuration partition so that ADAM users can be ADAM administrators.

How to express time in ISO 8601 format?
DateTime myTimestamp = DateTime.Now;
string t = String.Format("{0:u}",myTimestamp);
t = t.Remove(19,1);
string s = String.Format("{0}.{1:fff}Z",t,myTimestamp);

Is there a way to get DataTable from DataView? I am creating a DataView from a DataTable and applying some filters and sorting to Dataview. But I want to have DataTable out of this new Dataview (filterd/sorted).
In .NetFramework v1.0/v1.1 you can do:
Dim dt2 As DataTable = dv.Table.Clone() ‘ copies the structure
For I = 0 To dv.Count – 1
dt2.ImportRow(dv.Item(I).Row) ‘ copies a row
Next I

My ASP.NET form is many screens long. After user presses the button and the form is posted back to itself, I'd like to scroll it to the location where the button was pressed. What is the easiest way to achieve that?
You could use javascript, something like .scrollIntoView() for this.
string startupScript = @"";
Page.RegisterStartupScript("ReturnToAnchorAfterPostback", startupScript);

When ADSI binding fails, can the program know that is the userid that is not valid rather than the password is not match? Will doing a lookup to the lists of user take too much time?
It depends on how many objects are in your forest/domain. Searches are efficient if you use indexed attributes.

When changing password using ADSI, will the program know that the password actually not meeting the complexity requirement?
you'll get 800708c5 error.

Try C:\>net helpmsg 2245

Native XML Web Services for SQL Server 2005

Get an overview of how to set up and use XML Web Services using SOAP/HTTP inside SQL Server 2005 (formerly known as "Yukon"). Illustrative examples are included. To get the most from this paper, you should have a basic understanding of Web service technologies including HTTP, SOAP, and WSDL.

http://msdn.microsoft.com/data/default.aspx?pull=/library/en-us/dnsql90/html/sql2005websvc.asp

URL Rewriting in ASP.NET

Examines how to perform dynamic URL rewriting with Microsoft ASP.NET. URL rewriting is the process of intercepting an incoming Web request and automatically redirecting it to a different URL. Discusses the various techniques for implementing URL rewriting, and examines real-world scenarios of URL rewriting.

http://msdn.microsoft.com/asp.net/using/building/web/default.aspx?pull=/library/en-us/dnaspp/html/URLRewriting.asp

Does anyone get sample of code doing transformation and manipulating character strings (string tokenizer) ? You can find an example [Handling Different Row Types In The Same File] on http://www.sqlis.com/default.aspx?54

Interpreting Execution Plans of Partitioned Objects

The Web refresh of SQL Server 2005 Books Online contains a new topic, "Interpreting Execution Plans of Partitioned Objects" which should answer your question. You can download the latest version of Books Online at the following URL:
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx