To get the session ID, simply use the intrinsic variable @@SPID: SELECT @@SPID The acronym for SPID means Server Process ID. It is synonymous with session. Popular search terms:sql session idsql server session idsql server get session idtsql session idhow to get session id in sql server
Archive of entries posted on May 2010
How to Copy a Table
There are few methods of copying a table to another database, depending on your situation. Same SQL Server Instance If trying to copy a table to a database that is on the same instance of SQL Server, The easiest solution is to use a SELECT INTO while using the fully qualifed database names. SELECT * [...]
SELECT TOP 1
There are many good uses of the SELECT TOP 1 method of querying. Essentially, the select top 1 method is used to find the min or max record for a particular column’s value. There is some debate as to whether this is the ‘correct’ method of querying, however it should be known that this method [...]
Check if Database Exists
In creating a database you also need to check whether or not the database already exists. In order to do so, simply use the ‘if exists’ method and select the name of the database from sysdatabases. IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N’SQLServerPlanet’) CREATE DATABASE [SQLServerPlanet] The code below will drop [...]
String or binary data would be truncated.
This error message is relatively straight forward. The way it normally happens is when you are trying to insert data from a table that contains values that have larger data lengths than the table you are trying to insert into. An example of this would be trying to insert data from a permanent table, into [...]
How to Shrink Log File
Sometimes after one or more large transactions, the t-log (transaction log) will become full. In these particular cases you may receive an error message indicating the transaction log is full. In order to alleviate this issue, you need to find the names of the transaction logs on your system and then shrink them. To find [...]
Simulate ROW_NUMBER in SQL 2000
While the row_number feature in sql 2005+ has proven to be a very powerful feature, there are still ways to implement that same functionality in SQL Server 2000. Let’s first look at the SQL 2005+ implementation of ROW_NUMBER, then compare it to the SQL 2000: — SQL 2005+ SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY [...]
SQL Server Max SmallInt Value
The maximum value for a smallint in SQL Server is: -32768 through 32767 And the byte size is: 2 bytes other maximum values: BigInt: -9223372036854775808 through 9223372036854775807 (8 bytes) Int: -2147483648 through 2147483647 (4 bytes) TinyInt: 0 through 255 (1 byte) Popular search terms:sql server smallintsmallint sqlsql smallint maxsql server smallint max valuesql smallint
SQL Server Max TinyInt Value
The maximum value for a tinyint in SQL Server is: 0 through 255 And the byte size is: 1 byte other maximum values: BigInt: -9223372036854775808 through 9223372036854775807 (8 bytes) Int: -2147483648 through 2147483647 (4 bytes) SmallInt: -32768 through 32767 (2 bytes) Popular search terms:tinyint sql serverTINYINT sqlsql server tinyint max valuesql tinyintsql server tinyint
Gracefully Handling Deadlocks
In some situations, deadlocks may need to be dealt with not by changing the source of the deadlock, but by changing handling the deadlock gracefully. An example of this may be an external subscription that runs on a schedule deadlocking with another process. If the subscription deadlocks then it would be ok to just kill [...]
