SQL Server's Fascinating Stories

Following query can be used to search through all the job steps for any Keyword. USE [msdb] GO SELECT j.job_id, j.name, js.step_id, js.command, j.enabled FROM dbo.sysjobs j JOIN dbo.sysjobsteps js ON js.job_id = j.job_id WHERE js.command LIKE '%Keyword_Search%' GO

The below script can be used to search a keyword in any of the existing Stored Procedures or Functions on any database. SELECT SPECIFIC_NAME, ROUTINE_NAME, ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES where ROUTINE_DEFINITION like '%KEYWORD_SEARCH%'

You might see the following error while a package was newly imported and is set to be called from an SQL Agent through a job. The job would fail reporting the below error. Source:        Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error This is mainly because, the package protection level, which is by default set to  "EncryptWithSensitiveKey" Resolution: Set the package protection level to "DontSaveSensitive". But, if in case you know the password for the key, you could use it. How to change the SSIS Package Protection: In Business Intelligence Development Studio, open the Integration Services project that contains the package. Open the package in the SSIS designer. If the Properties window d...

Please check the link for a list of Error States explained in detail for the Logon Errors 18456 on SQL Server. http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx These are for now, and will add to this if there are any more useful found.

The following script was made as a requirement to check if there are databases that are subscribing in an instance.  This can be executed on any database.  Create table #TempTable ( DB varchar(100), Is_Subscriber varchar(10)); EXECUTE sp_MSforeachdb 'USE [?]; if exists (SELECT name FROM sys.sysobjects WHERE name = ''MSreplication_subscriptions' ')         begin insert into #TempTable values (db_name(), ''YES''); END' select * from #temptable drop table  #TempTable

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...