Query to get the SQL text of a particular process (SPID)

July 18, 2012

Query to get the SQL text of a particular process (SPID)

To know the process id run sp_who2. You will get the process id.

After getting the process id run the below batch to get the SQL text:

DECLARE @sqlhandle binary(20)
SELECT @sqlhandle=sql_handle FROM sysprocesses WHERE spid=90 — here I use the process id as 90
SELECT text FROM ::fn_get_sql(@sqlhandle)


How to Add/Remove the Server (Computer)?

July 18, 2012

How to Add/Remove the Server (Computer)?

There are some situations at which you need to Add/Remove the Server (Computer) to your SQL Server instance.

Here is a scenario. Your database server name is changed from Old_Server to New_Server. Even after changing your database server name, your SQL Server Instance will be pointing to the Old_Server name. This may affect the SQL Agent jobs.You may be facing problems in Add/Edit the scheduled jobs some times.

To avoid this you need to remove the Old_Server and add the New_Server name.

To know the current Server name run the below command:

SP_HELPSERVER

To remove the Old_Server run the below command:

SP_DROPSERVER ‘Old_Server’

To add the New_Server run the below command:

SP_ADDSERVER ‘New_Server’

After completing this process you will see that your SQL Agent Jobs will be start working fine.

For more information see the Microsoft links:

 


How to Enable/Disable the Identity column?

July 17, 2012

To insert the values manually in to the identity column you have to enable by setting IDENTITY_INSERT to ON. To stop inserting the manual values into identity column you have to disable by setting IDENTITY_INSERT to OFF. Here is the script:

SET IDENTITY_INSERT <Table Name> ON; — This will enable the Identity column inserting values
<Insert Query with identity column values>
SET IDENTITY_INSERT <Table Name> OFF; — This will disable the Identity column inserting values


How to reseed the Identity column in a table?

July 17, 2012

Here is the syntax to reseed the Identity column in a table:

DBCC CHECKIDENT (‘<table Name>’,RESEED,<value>)

Eg.
DBCC CHECKIDENT (‘table1’,RESEED,10) — If you want to insert the value from 11


Displaying the Database details

July 14, 2012

The below syntax will display the details of a particular database or all databases:

SP_HELPDB ‘<Database Name>’

The above query will display the details of a particular database. The below query will display the details of all the databases:

SP_HELPDB


How to bring the database to Multi-User mode?

July 13, 2012

The below set of queries would bring the database to Multi-User mode:

USE master;
ALTER DATABASE <Database Name> SET MULTI_USER;
GO


How to bring the database to a Single User mode?

July 13, 2012

The below set of queries would bring the database to a single user mode:

USE master;
ALTER DATABASE <Database Name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

ROLLBACK IMMEDIATE will bring the database to the single user mode immediately after rolling back the transactions.

 


%d bloggers like this: