MS SQL Server Solutions

Playing with NULL

2011-08-07T18:10:00.001+05:00

A NULL values is an unknown value, different from a zero or an empty value.There are few points that must be remembered when you are dealing with NULL values:1. No two NULL values are equal.2. Comparison between two NULL values or between one NULL and any other values results in an UNKNOWN value.3. Logical (e.g. AND, OR etc.) and Comparison (e.g. =, >, < etc.) Operators can

Microsoft® SQL Server® code-named 'Denali' - Community Technology Preview 1 (CTP1)

2010-12-15T19:13:00.001+05:00

OverviewSQL Server code-named 'Denali' helps empowers organizations to be more agile in today’s competitive market. Customers will more efficiently deliver mission-critical solutions through a highly scalable and available platform. Industry-leading tools help developers quickly build innovative applications while data integration and management tools help deliver credible data reliably to

Monitoring Transactional Replication Status - SQL Server 2005, 2008, 2008 R2

2010-12-14T23:09:00.001+05:00

USE DISTRIBUTIONGOSELECT s.agent_id ,a.id ,s.article_id ,a.subscriber_id ,ar.Source_owner ,ar.Source_object ,ar.destination_owner ,ar.destination_object ,s.undelivcmdsindistdb ,a.publisher_db ,a.subscriber_db ,a.publicationFROM distribution.dbo.msdistribution_status s with (nolock) INNER JOIN (SELECT * FROM msdistribution_agents with (

DDL EVENTS List - SQL Server 2008/R2

2010-11-01T21:54:00.003+05:00

Below are the all events with their hierarchy and scope that you can use to implement DDL triggers at both Server or Database Level. You can create trigger to fire on all events defined under a group, then you can create trigger for that particular group like "DDL_TABLE_EVENTS", this trigger will fire on all three sub-events defined under this group; these are CREATE_TABLE, ALTER_TABLE and

Script: view information of currently running SQL Server Jobs

2010-10-29T18:48:00.000+05:00

Below script returns five (5) columns result set as output. You can further enhance this query to accommodate your requirement. Job_Name: This is job’s registered name on the server’s instance. Last_Executed_Step_ID: This is the ID of the last executed step in the job. E.g. if a job has 4 steps, and currently it is running 3rd step, so last executed step will be 2 and it will be populated with 2

Compound Operators - SQL SERVER 2008/R2

2010-10-25T22:49:00.000+05:00

You are aware about arithmetic, bitwise and assignment operators, don’t worry if you are not, see below you will get an idea about them. Arithmetic Operators: There are 5 arithmetic operators that are Plus (+), Minus (-), Divide (/), Multiply (*) and Modulo (%) Bitwise Operators: There are three bitwise operators that are Bitwise AND (&), Bitwise OR (|) and Bitwise Exclusive OR (^). Assignment

Enhanced VALUES Clause (ROW Constructor) – SQL SERVER 2008 / R2

2010-10-20T00:52:00.000+05:00

Old Style:If you see at previous versions of SQL Server (Older than 2008), you were able to insert only a single row by using values clause. INSERT INTO dbo.mytable(id, val) VALUES(1, 'First Value'); Or If you were inserting through SELECT statement, you were able to specify a single row values or by combining multiple rows by using UNION/UION ALL operator. INSERT INTO dbo.mytable(id, val)SELECT

SQL Server 2008 SP2 Release (download link)

2010-10-15T18:44:00.001+05:00

What's New: 15K partitioning Improvement. Introduced support for a maximum of 15,000 partitions in tables and indexes in Microsoft SQL Server 2008 Service Pack 2 in the Enterprise, Developer and Evaluation Editions. Reporting Services in SharePoint Integrated Mode. SQL Server 2008 SP2 provides updates for Reporting Services integration with SharePoint products. SQL Server 2008 SP2 report servers

Moving User Database Files in SQL Server 2000

2010-10-11T18:44:00.000+05:00

0. Take Full backup before moving files 1. Set database in single user modeuse mastergo exec sp_dboption testdb2000,'single user','true'go 2. Get path of database files Exec sp_helpdb testdb2000go 3. Save location of all files C:\Program Files\Microsoft SQL Server\MSSQL$INS2000\data\TestDB2000_Data.MDFC:\Program Files\Microsoft SQL Server\MSSQL$INS2000\data\TestDB2000_Log.LDF

Utility to see Logins and users on an instance

2009-04-25T02:03:00.000+06:00

Use below link to download it:http://cid-2512e6af14d229c4.skydrive.live.com/self.aspx/SQL%20Server%20Utilities/Instance%20Users%20and%20Logins%20Details.zipDescription: You can see available logins on an instance and users list in a databases, their fixed roles at server and database level, categorized as server login and database user, Object level permission details.Pre-requistes:.Net Framwork

List of Running Sessions and their Requests on an Instance

2009-04-15T01:13:00.001+06:00

Download this free utility by clicking below link:http://cid-2512e6af14d229c4.skydrive.live.com/self.aspx/SQL%20Server%20Utilities/Running%20Requests%20on%20an%20Instance.zipPre-requists:.Net Framwork 2.0 or +Only for SQL Server 2005+Developed using VB .NetDescription:This is an executable file that is available in zip formate at above link.You can view all logins sessions and their running

Moving database files

2009-02-18T16:56:00.000+05:00

We have three kinds of database files: log files (*.ldf), primary data files (*.mdf) and secondary data files (*.ndf).Primary data file is the starting point for a database and points to all other files in it. Each database has one primary data file. There can be 0 or >0 secondary data files in a database to make up data files other than primary data file. Log files hold all log information that

Preliminary checks for MS DTC

2009-02-10T13:43:00.000+05:00

To dig further to evaluate MS DTC error, check below things is in place.You are able to ping both server from each other. If not then define them in their host files and try to ping them again, till it resolves.All MS DTC configurations should be same on both servers.a) NETWORK DTC Accessb) Client and Administration (Allow remote clients, Allow remote administration)c) Transaction Communication

Database & Disk space analysis

2009-01-14T02:52:00.000+05:00

BEGIN CREATE TABLE #LOGSPACE(DBNAME VARCHAR(100),LOGSIZE DECIMAL(30,10),LOGSPACE DECIMAL(30,10),STATUS INT)CREATE TABLE #DRIVESPACE(DRIVE VARCHAR(2),DRIVESPACE BIGINT)INSERT INTO #LOGSPACEEXEC ('DBCC SQLPERF(LOGSPACE)')INSERT INTO #DRIVESPACEEXEC ('MASTER.SYS.XP_FIXEDDRIVES') SELECT F.TYPE_DESC DBFILES,LEFT(F.PHYSICAL_NAME,1) DRIVE,F.STATE_DESC DBSTATE,((F.[SIZE]*8)/1024.00)/1024.00 DBCURRENTSIZE

Space used in tempdb by each Session

2009-01-14T02:37:00.000+05:00

SELECT DB_NAME(DATABASE_ID) DBNAME,SP.SESSION_ID,S.LOGIN_NAME,S.HOST_NAME,(SUM(USER_OBJECTS_ALLOC_PAGE_COUNT)*8)/1024.00 ALLOCATED_SPACE_MB,(SUM(USER_OBJECTS_DEALLOC_PAGE_COUNT)*8)/1024.00 DEALLOCATED_SPACE_MBFROM TEMPDB.SYS.DM_DB_SESSION_SPACE_USAGE SPINNER JOIN TEMPDB.SYS.DM_EXEC_SESSIONS S ON S.SESSION_ID = SP.SESSION_ID AND S.HOST_NAME IS NOT NULLGROUP BY

Reading Sql Server and Sql Agent log files using T-SQL

2008-12-16T15:46:00.000+05:00

xp_readerrrorlog It is an undocumented extended stored procedure with at least 7 parameters (default null), that calls a dll to show results. Parameters details as: Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc... Log file type: 1 or NULL = error log, 2 = SQL Agent log Search string 1: String one you want to search for Search

Script to get Login's Server and Database level permissions

2008-12-04T00:47:00.000+05:00

We have two levels for login Server LevelDatabase Level We can further divide them into Fixed roles and Permissions for a single login as: Server Level PermissionsServer Level Fixed RolesDatabase Level PermissionsDatabase Level Fixed Roles I developed a script to get such details in current environment or on current instance for all databases. Let me know if you find any issue in it.below

Encryption by using Pass Phrase Keys (independent from database or database keys)

2008-11-02T15:40:00.000+05:00

It is not necessary to create and use a key persisted in the database for encryption. If you are willing to manage the key yourself—and take responsibility for keeping the secret secret—use the EncryptByPassPhrase and DecryptByPassPhrase functions. These functions require only a pass phrase as the first parameter and either clear text or ciphertext as the second, and they do not let you select

INSERTION and DELETION Using Partition

2008-10-29T05:49:00.000+06:00

/*Steps to follow1. Add filegroup2. Create Partition Functions3. Create Partition Scheme4. Create Staging, Main and Dummy table with same structure and indexes on same parition scheme5. Create Procedures to add, emptry, move and merge parition6. Create Main Procedure to execute the above implemenations7. Test Case execution*/ --- * Add Filegroup in the current DatabaseALTER DATABASE TEST ADD

Create Partition Function and Partition Scheme

2008-10-29T03:29:00.000+06:00

Below partition functions and scheme created to use data as argument or base. First of all you need to add a file group in your current database and then you have to create this partition function and scheme on the base of partition function. --- * Add Filegroup in the current DatabaseALTER DATABASE TEST ADD FILEGROUP FG1 -- * Create Partition FunctionCREATE PARTITION FUNCTION

Current Identity value, Identity usage and other identity information

2008-10-19T04:06:00.000+06:00

SELECTQUOTENAME(SCHEMA_NAME(T.SCHEMA_ID)) + '.' + QUOTENAME(T.NAME) AS TABLENAME,C.NAME AS COLUMNNAME,CASE C.SYSTEM_TYPE_ID WHEN 127 THEN 'BIGINT' WHEN 56 THEN 'INT'WHEN 52 THEN 'SMALLINT' WHEN 48 THEN 'TINYINT' END AS 'DATATYPE', IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS CURRENTIDENTITYVALUE,IDENT_INCR(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS IDENTITYINCREMENT,

How to use Output Clause?

2008-08-30T20:38:00.000+06:00

It returns information from each row affected by any DML operation [INSERT/UPDATE/DELETE], it means you can use it with only DML statements. These results can be retuned to the processing application as confirmation message or archiving purpose, and they can be inserted into a physical or temporary or memory table. You can get all columns [*] or you can specify the columns to be returned by

Programming Replication Distribution Agent Executable

2008-08-29T05:44:00.000+06:00

Distribution replication agent can be controlled by programmatically described below,You can copy and paste below code in a batch file and then execute that batch file after replacing the required information with your actual parameters. It will execute the Distribution agent. Required Parameters:Distributor: Distributor instance nameDistributorSecurityMode: 0 for SQL Server

Programming Replication Snapshot Agent Executable

2008-08-29T04:56:00.000+06:00

Snapshot replication agent can be controlled by programmatically described below,You can copy and paste below code in a batch file and then execute that batch file after replacing the required information with your actual parameters. It will execute the snapshot agent. Required Parameters:Publisher: instance namePublicationDB: publisher database namePublication: publicaion

Ranking rows of a result set...

2008-08-19T03:54:00.000+06:00

Rows can be ranked in a result set [Selection from one table or multiple tables by using joins] by using Rank() function. If two or more rows tie for a rank, tied rows will get the same rank.Example:Two temporary tables created, and populated with test data, two queries created one for single table selection and second query to show the results from two tables by using joins. CREATE

comparing current row with previos and next row in a table

2008-08-16T20:11:00.000+06:00

you must define identity column or there should be an integer column to keep the sequence of the rows, or you can import the data into a temporary table with an additional identity column.here is the script: Create Table #Temp(I_Col int identity(1,1) ,val1 int) Insert into #Temp values(10)Insert into #Temp values(25)Insert into #Temp values(67)Insert into #Temp values(98)Insert

how to view association among File Groups, System Logical and Physical Files and Tables

2008-08-12T02:58:00.000+06:00

You can use this code to view file group, logical filename and physical filename associated to a table. It can be beneficial if you have large number of database files and want to see objects (tables) in a file or to view the file associated to a table. To see whole list of tables in each data fileA table can have only one value at a time in index table (0 or 1), 1 for clustered index and

Getting All Disks Free Space information on an instance

2008-08-09T17:12:00.000+06:00

you must define linked servers on the instance executing this code if it has to calculate the free disk space information from remote servers.BEGINSET NOCOUNT ONDECLARE @TOTALCOUNT INT ,@I INT ,@SERVER_NAME NVARCHAR(100) ,@DESCR NVARCHAR(100) ,@STMT NVARCHAR(200) /* Temporary Table holding Server names */CREATE TABLE #SERVERS(PID INT IDENTITY

How to get list of all stored procedures?

2008-08-07T22:33:00.000+06:00

You can use following stored procedure with four optional input parameters to get a list of all stored procedures in the current environment. EXEC SP_STORED_PROCEDURES @sp_name = 'procedure name' , @sp_owner = 'schema name' , @sp_qualifier = 'database name' , @fUsePattern = 'fUsePattern' Note: All parameters

Moving database files to another new location

2008-08-07T13:21:00.000+06:00

You can move database data and log files to any other location by following the below steps, these come under Planned Relocation. Check if there is any user connected to DB by executing the below query. SELECT SPID,LOGINAME,HOSTNAME,PROGRAM_NAME FROM SYS.SYSPROCESSES WHERE DBID=DB_ID('database_name') If there is any replication agent [Log Reader Agent] is running, stop

Table's rows count without using COUNT() function

2008-08-06T23:29:00.000+06:00

You can get table's total number of rows as: SELECT SUM(ROWS) AS Total_Rows FROM SYS.SYSINDEXESWHERE ID=OBJECT_ID('Table1')AND INDID IN (0,1)OBJECT_ID('Table1'):This function will return the object id for table "Table1" or any other specified table.INDID:It can be 0 for heap and 1 for clustered index, and it will have only one value at a time 0 or 1, greater than 1 values are for non-clustered

Determining Index fragmentation / when to rebuild / when to reorganize

2008-07-26T09:54:00.000+06:00

Index fragmentation:When data is inserted, updated or deleted in a table, if clustered or non-clustered indexes exist on that table, they must be maintained to reflect the changes. The maintenance of these indexes eventually will cause the indexes less efficient. This inefficiency leads to index fragmentation.We have two types of index fragmentation:Internal

Finding total / available / used space by transactional log file

2008-07-25T23:47:00.000+06:00

DBCC SQLPERF(LOGSPACE) It provides statistics that how much transactional log space was used by each database in the current server. So, you can monitor the amount of space used and can decide when to take backup or to truncate the transactional log file. Its result set consists of: Database Name – Name of the databaseLog Size (MB) – Actual amount of log file size, it is less than

Determining indexes utilization Or unused indexes

2008-07-25T05:42:00.000+06:00

You can use following DMV to check index utilization or to see unused indexes. SYS.DM_DB_INDEX_USAGE_STATS By determining the stats, you can see what indexes are mostly used by the queries and what indexes are unused and overhead for the system from maintenance point of view. These stats/counters will be initialized to empty whenever SQL Server restarts, database is de-attached, or database

Change the name of a Database, Table or Column

2008-07-24T06:56:00.000+06:00

You can use the following procedure to change database, table, index or column name.SP_RENAME 'Object_Old_Name','Object_New_Name','Object_Type'Object Type can be DATABASE, OBJECT, INDEX, COLUMN and USERDATATYPE.To Change Database name:It will change the database name from DB1 to DB1_New.EXEC SP_RENAME 'DB1','DB1_New','DATABASE'To change Table name:It will update the table name from Table1 to

Scalar Configuration Functions

2008-07-23T22:45:00.000+06:00

All configuration functions are nondeterministic. This means these functions do not always return the same results every time they are called, even with the same set of input values.The following scalar functions return information about current configuration option settings,@@DATEFIRSTReturns the current value, for the session, of SET DATEFIRST.e.g. SELECT @@DATEFIRSTyou can change first day

Searching a Table in all databases on Current Server

2008-07-23T21:04:00.000+06:00

You can use Information_Schema view Tables to see the table list in the current DB as:Select * From DB1.Information_Schema.Tableswe will use the above Sql query with the following stored procedure to search a table.Procedure: dbo.sp_MSforeachdbDescription: This procedure will execute the attached string as a command against every database on the server. Any question mark(?) , within the string

Calcualting estimated time for any running SQL command

2008-07-22T20:07:00.000+06:00

you can use the following SQL statement to calculate estimated time for any sql command. This script is applicable for Sql Server 2005 and above.1) you can get the command label by executing below procedures or sql statement, that you will use in estimated time calculation query.Exec sp_WhoOrExec sp_Who2OrSelect * From sys.sysprocessesOrSelect * From sys.dm_exec_requests2) script for estimated

How to get Sql text without using dbcc inputbuffer

2008-07-22T19:48:00.000+06:00

You can use a dynamic management view for this purpose, details given below:Dynamic Management View:sys.dm_exec_sql_text(sql_handle)sql_handle SQL handle of the text to be looked up.It retruns:dbid - Database ID, you can get database name by using DB_Name(dbid) function.Objectid - Object ID, you can get Object name by using Object_Name(objectid) funciton.number - store procedures number if

List of Permission granted to caller [Calling User]

2008-07-22T19:14:00.000+06:00

Following statements returns the permission details for the user who is executing the statement.-- List of Permission at Server LevelSELECT * FROM fn_my_permissions(NULL, 'SERVER')-- List of Permission at Database LevelSELECT * FROM fn_my_permissions('DB1', 'DATABASE')-- List of Permission at Object LevelSELECT * FROM fn_my_permissions('Object1', 'OBJECT')ORDER BY subentity_name,

Test the connection to a Linked Server

2008-07-22T18:18:00.000+06:00

Procedure: SP_TESTLINKEDSERVERSyntax:sp_testlinkedserver @servername = servername@Servername: Linked servernameNote: Caller must have appropriate login mapping.e.g.Exec sp_testlinkedserver SQL1

Get List of Linked Servers on Curent/Local Server

2008-07-22T17:58:00.000+06:00

Procedure: SP_LINKEDSERVERSDescription: Returns the list of linked servers defined in the local server.It returns:Column name - Description SRV_NAME - Name of the linked server.SRV_PROVIDERNAME - Friendly name of the OLE DB provider managing access to the specified linked server.SRV_PRODUCT - Product name of the linked server.SRV_DATASOURCE - OLE DB data source property corresponding to the

Database Level Principals List : System Stored Procedure

2008-07-22T16:10:00.000+06:00

Procedure Name: sp_helpuserDefinition: Reports information about database-level principals in the current database.Syntax: sp_helpuser [ [ @name_in_db = ] 'security_account' ][ @name_in_db = ] 'security_account' Is the name of database user or database role in the current database. security_account must exist in the current database. security_account is sysname, with a default of NULL. If

case sensitive selection by using Collation Clause

2008-07-22T15:51:00.000+06:00

1) Create temporary table to insert values to check the implemenationCreate Table #temp( ID int identity(1,1) , val1 varchar(50))2) Values insertionInsert into #temp values('abc')Insert into #temp values('ABC')Insert into #temp values('aBc')3) Selection by using collation functionselect * from #temp where val1 like 'abc%'collate SQL_Latin1_General_CP1_CS_AS4) above collation is only for general