SQL Server's Fascinating Stories

Rebuild Index|Create Index|DML on a Table with Filtered Index fails with the error: The following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934).  The step failed. The Operation above might succeed when its through SSMS. But, would fail when the operation is from a SQL Agent Job. This is because, SQL Agent by default, do not have the SET Options enabled (ON). They are OFF. Reason: The following are the SET Options that are needed while operation on a table with a filtered index. Required SET Options for Filtered Indexes The SET options in the Required Value column are required whenever any of the following conditions occur: Create a filtered index. INSERT, UPDATE, DELETE, or MERGE operation modifies the data in a...

Sharing a folder through CMD script: This might possibly be a need while automating a share on a specific condition. Here in, our requirement was to start a share of the folder  for multiple users based on SQL Agent Start. This can be done by the following steps. The approach is to have a .bat file with the command that shares the folder. Later, the bat file can be called from SQL Agent Job Step. The command is: NET SHARE XXX$=E:\MyFolder /GRANT:SQLServer2005SQLAgentUser,FULL /GRANT:SQLServerSQLUser,FULL  /GRANT:"Database Administrators",FULL /GRANT:EVERYONE,READ For Help in Command Prompt: NET SHARE /help Note: Share resource names that end in a $ character do not appear when you browse the local computer from a remote computer. The .bat file can be created by pasting the command above in any text editor and the file can be saved by the extension .bat. The .bat file can be called from the agent job step as Choose the Job Step Type as OperatingSystem (Cmd...

Rule "Reporting Services Catalog Database File Existence" failed error: ------------ Solution: Check for the existence of Reporting Services database files in the default location or the location where the database files are set to go. Delete the database files. Then the retry would solve the problem.

This is quite a prevalant scenario for any DBA that would use begin tran to update a column as per requirement. If there isn't an instruction to the Storage Engine to either commit the transaction or roll it back, it would lead to a situation that would swamp the transaction log file which would slow down the system that would access the database. If that is the case, one can check for open transactions through the below command dbcc opentran This would show the process id if any transaction is open. Then the details related to open transaction can be fetched from one of the below system objects. exec sp_who or select * from sys.sysprocesses A column called sqlhandle can be useful to know the query being executed by the process. If the results produced through any updates are favourable, its better commit as soon as possible and if not roll it back.

·          I have SQL Server 2005 log shipping setup with primary/secondary configuration.  I can confirm from the logs that log shipping is working without issue, however, reports generated from the monitor server show this message: Violation of PRIMARY KEY constraint 'PK__#log_shipping_mo__3ABBDC91'. Cannot insert duplicate key in object 'dbo.#log_shipping_monitor'.  The statement has been terminated. There is nothing special about the configuration.  Any ideas? ·          I think the problem you are seeing is related to some old information being present in the tables used to store log shipping configuration.  There are some scenarios where this can happen and it causes the problem you reported in your first post.  We are working on correcting this in a future release.  As you can tell from the error, the problem is caused by an insert to a temp table causing a PK constr...

Knowing the SPID of the current session we are using. select @@SPID To know the database name from the dbid select DB_Name (DBID) and viceversa, select db_ID (DBname) We can get the query being executed on any session using the below query. DECLARE @Handle varbinary(64); SELECT @Handle = sql_handle FROM sys.sysprocesses WHERE spid = 62; SELECT text FROM ::fn_get_sql(@Handle)

I recently had a quaint thinking, as to how to know the hostname of a machine given its IP. Its easy. Just give the following in command prompt. C:\> nbtstat -A xxx.xxx.xxx.xxx where xxx.xxx.xxx.xxx represents an IP address.