SQL Yoga : Certificate Expired, Mirroring Stopped

Database Administrators might face this issue while certificate gets expired and database mirroring (Non domain database mirroring) gets disconnected as two servers, Primary and Secondary cannot communicate with each other. The error message can be found in Log as follows: Message Database Mirroring login attempt failed with error: 'Connection handshake failed. The certificate used by this endpoint was not found: Certificate expired. Use DBCC CHECKDB in master database to verify the metadata integrity of the endpoints. State 85.'. [CLIENT: xxx.xxx.xxx.xxx] In this post, We are going to review step by step process to resolve this issue by providing renew parameters with certificate configurations. 1. Create a new certificate with longer endpoint (on Principal):

  1: -- Create a new certificate for the endpoint
  2: USE master;
  3: CREATE CERTIFICATE [Principal_Certificate_New]
  4:    WITH SUBJECT = 'Principal Certificate',
  5:     START_DATE='01/01/2014', -- Provide date prior to current date
  6:     EXPIRY_DATE='12/31/2020'; -- Provide this as future date
  7: GO

2. Take backup of the newly created certificate (on Principal):

  1: USE master;
  2: BACKUP CERTIFICATE [Principal_Certificate_New] TO FILE = N'F:\Backup\Principal_Certificate_New.cer';
  3: GO

3. Set mirroring to use the newly created certificate (On Principal):

  1: ALTER ENDPOINT DBMirrorEndPoint
  2: FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [Principal_Certificate_New])
  3: GO

4. Delete the old certificate for endpoint (on Principal):

  1: USE master;
  2: DROP CERTIFICATE [Principal_Certificate_Old]
  3: GO

5. Drop the Old Certificate for Principal Login (on Mirror):

  1: USE master;
  2: DROP CERTIFICATE [Principal_Certificate_Old]
  3: GO

6. Create a new certificate from the principal backup file (on Mirror):

  1: USE master;
  2: CREATE CERTIFICATE [Principal_Certificate_New] AUTHORIZATION PrincipalServerUser
  3: FROM FILE = N'F:\Backup\Principal_Certificate_New.cer';
  4: GO

7. Create a new certificate with longer endpoint (on Mirror):

  1: use master;
  2: CREATE CERTIFICATE Mirror_Certificate_New
  3: WITH SUBJECT = 'Mirror Certificate New'
  4:  ,EXPIRY_DATE = '12/31/2020' -- Provide this as future date
  5: GO

8. Take backup of newly created certificate (on Mirror):

  1: USE master;
  2: BACKUP CERTIFICATE [mirror_new_cert] TO FILE = 'F:\Backup\Mirror_Certificate_New.cer';
  3: GO

9. Set mirroring for newly created certificate (on Mirror):

  1: USE master;
  2: ALTER ENDPOINT DBMirrorEndPoint
  3: FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [Mirror_Certificate_New])
  4: GO

10. Drop the old certificate for endpoint (On Mirror):

  1: USE master;
  2: DROP CERTIFICATE [Mirror_Certificate_Old]
  3: GO

11. Drop the old certificate for Mirror login (On Principal):

  1: USE master;
  2: DROP CERTIFICATE [Mirror_Certificate_Old]
  3: GO

12. Create a new certificate from the mirror backup file (On Principal):

  1: USE master;
  2: CREATE CERTIFICATE [Mirror_Certificate_New] AUTHORIZATION MirrorServerUser
  3: FROM FILE = 'F:\Backup\Mirror_Certificate_New.cer'
  4: GO

13. Resume the mirroring session for each database(On Principal and Mirror):

  1: USE master;
  2: ALTER DATABASE [Mirrored_Database_Name] SET PARTNER RESUME
  3: GO

By following the mentioned steps, we can resolve the certificate issues for Mirroring database and Mirroring will be resumed.
Note : 1. Always prefer to provide the endpoint date as far date, so that issue doesn’t occur very soon. 2. During this process, Primary database is available without any interruption.
Reference: Tejas Shah (www.SQLYoga.com)

Read More

SQL SERVER: Find executing queries

Recently my company owner reported that SQL SERVER is running slow. I searched for it and get following T-SQL query. SQL SERVER provides dynamic views, which contains current information about all details.

 

SELECT OBJECT_NAME(ObjectID) as ObjectName,
  st.Text, 
  DB_NAME(database_ID) as dbname,
  Blocking_session_ID as BlockingSessionID,  
  *
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st

 

It will give us details like about running SQL Objects, with Database name:

 

 

Reference : Tejas Shah http://www.SQLYoga.com

Read More

SQL SERVER: Execute Stored Procedure when SQL SERVER is started

We have a requirements to execute Stored Procedure when SQL SERVER is started/restarted and we need to start some processes. I found that SQL SERVER provides a way to call Stored Procedure when SQL services are restarted.

SQL SERVER provides this SP: "sp_procoption", which is auto executed every time when SQL SERVER service has been started. I found this SP and it helps me to figure it out the solution for the request as following way.

Let me show you how to use it. Syntax to use SP:

EXEC SP_PROCOPTION     
@ProcName = 'SPNAME',    
@OptionName = 'startup',    
@OptionValue = 'true/false OR on/off'

• @ProcName, should be Stored procedure name which should be executed when SQL SERVER is started. This stored procedure must be in "master" database.
  
• @OptionName, should be "startup" always.
  
• @OptionValue, this should be set up to execute this given sp or not. If it is "true/on", given sp will be execute every time when SQL SERVER is started. If it is "false/off", it will not.

That's it, I hope this is very clear to use this feature.

Reference : Tejas Shah (http://www.SQLYoga.com)

Read More

SQL SERVER: SSIS - Foreach Loop Container

Today, I am going to explain SSIS For Each Loop Task. I am sure that it will be easy to configure SSIS Package with Foreach Loop task, after you read this article.

The Foreach Loop container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure in programming languages. In a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator.

Many times, user asks that I want to process all files from my folder to database. This is my answer to all of those users. SSIS comes up with For Each Loop which did the same.

Now I'm assuming that you're familiar with using the SQL Dev Studio tools and building basic packages. If this isn't the case, I recommend working your way through the Integration Services using my earlier posts.

1. SSIS Foreach Loop Container - General : Here we need to assign unique name to this container and also we can specify brief description, so we will get idea why we need to design this container.

2. SSIS Foreach Loop Container - Collection : Use the Collection page of the Foreach Loop Editor dialog box to specify the enumerator type and configure the enumerator.

Select the enumerator type from the list. This property has the options listed as follows:

1. Foreach File Enumerator: Enumerate files
   
2. Foreach Item Enumerator: Enumerate values in an item
   
3. Foreach ADO Enumerator: Enumerate tables or rows in tables
   
4. Foreach ADO.NET Schema Rowset Enumerator: Enumerate a schema
   
5. Foreach From Variable Enumerator: Enumerate the value in a variable
   
6. Foreach Nodelist Enumerator: Enumerate nodes in an XML document
   
7. Foreach SMO Enumerator: Enumerate a SMO object

Let me explain with Foreach File Enumerator

We can use this Foreach File enumerator to enumerate files in a folder.

The content of the folders and subfolders that the Foreach File enumerator enumerates might change while the loop is executing because external processes or tasks in the loop add, rename, or delete files while the loop is executing. This means that a number of unexpected situations may occur:

In Folder option, provide the path of the root folder to enumerate. In Files option, specify the files to enumerate.

If wildcard characters are specified in the Files option, then the fully-qualified paths that are returned match the filter.

1. Fully qualified: Select to retrieve the fully qualified path of file names.
   
2. Name and extension: Select to retrieve the file names and their file name extensions.
   
3. Name only: Select to retrieve only the file names.

3. SSIS Foreach Loop Container - Variable Mappings : Use the Variables Mappings page of the Foreach Loop Editor dialog box to map variables to the collection value. The value of the variable is updated with the collection values on each iteration of the loop.

Variable: Select an existing variable, or click <New variable...> to create a new variable.

Index: If using the Foreach Item enumerator, specify the index of the column in the collection value to map to the variable. For other enumerator types, the index is read-only.

Once we configured, For Each Loop container, Lets add the process that we want. Here what I am going to do is:

1. Data Flow Task.

NOTE: We can add any other tasks to this container. I took Data flow process, as its already explained by this BLOG.

Here lets see the logical scenario of For Each Container Task.

First, From the selected folder, fetch all the files and assign file path to the variable.

Second, For Data Flow Process, process the file from the folder and execute data flow.

NOTE: For Data Flow Process, we need to assign Connection from variable. ( as we assigned variable after each loop, we need to process new file from the folder). So How can we configure File connection to use new file?

Please refer to my next article for How to assign Connection from variable.

That's it. This is what we need to design for "For Each Loop".

Let me know if there is any difficulties you have to design this package.

Read More

SQL SERVER SSIS: BULK INSERT Task

As we have seen SSIS Data Flow TASK in previous article, today I am going to explain SSIS BULK INSERT TASK. The Bulk Insert task provides an efficient way to copy large amounts of data into a SQL Server table or view. Let's say company is using Online Product Marketing and every day they have to update their databases with the latest information provided by their suppliers in Tab Separated/Comma Separated file. Here BULK INSERT provides efficient way. We can move data to SQL SERVER Table or View by BULK INSERT Task. Let see How to configure SSIS BULK INSERT Task: 1. Drag Control: Drag and drop, BULK INSERT TASK from Toolbox to Design surface: 2. Configure connection: Right click on Task and click on Edit Specify Destination Connection: Specify the OLE DB connection manager to connect to the destination SQL Server database and the table or view into which data is inserted. NOTE: The Bulk Insert task supports only OLE DB connections for the destination database. Define Format: We need to Define the format that is used by the Bulk Insert task, either by using a format file or by defining the column and row delimiters of the source data. If using a format file, specify the File connection manager to access the format file. We need to specify Row Delimiter and Column Delimiter, as per our requirement. Specify Source Connection: Finally, we need to specify Flat or File Connection Manager to access the source file. BULK INSERT Task is process this file as per defined format on previous step. 3. Configure Options: SSIS BULK INSERT Task also provides to specify some options which needs to perform when BULK TASK Insert some data to destination Table/View, like: Check Constraint, Keep Nulls, Enable Identity Insert, Table Lock, Fire Triggers. That's it. Now execute the package, you find that your Table/view is populated with data from provided text file. Why we have both BULK INSERT AND DATA FLOW TASK ? We can say that Data Flow Task is advanced version of BULK INSERT TASK, which provides more features and more scalable. The Bulk Insert task can transfer data only from a text file into a SQL Server table or view. To use the Bulk Insert task to transfer data from other database management systems (DBMSs), you must export the data from the source to a text file and then import the data from the text file into a SQL Server table or view. With Data Flow Task, we can import/export data from any database, we don't need to convert it to text and then import. So, we can use either of these as per our requirements. Let me know if you have  any questions. Reference: SQL SERVER Books Online, Tejas Shah (http://www.SQLYoga.com)

Read More

SQL SERVER: What is SSIS?

SSIS (SQL Server Integration Services) is an upgrade of DTS (Data Transformation Services), which is a feature of the previous version of SQL Server SSIS (SQL Server Integration Services) is introduced in SQL SERVER 2005. It is totally new transformation of data. Previously we have DTS package in SQL SERVER 2000. Now they introduced SSIS with many features. SSIS is an extensive data integration platform which is used to transfer and merge the information from various sources and load to multiple systems. It contains wide range of capabilities. Business Intelligence is used for decision making, future trends based on collected data. I can find very good article written by Microsoft SQL SERVER MVP, Pinalkumar Dave. He write about BI on his one of the best article: http://blog.sqlauthority.com/2009/10/21/sql-server-introduction-to-business-intelligence-important-terms-definitions/. SSIS packages can be created in BIDS (Business Intelligence Development Studio). I am going to explain all features of SSIS for easy and better understanding by following articles. Reference: Tejas Shah (http://www.SQLYoga.com )

Read More

Community TechDays at Ahmedabad - Great Event

There was a great event held by Microsoft and PASS (Professional Association of SQL Server) in Ahmedabad on 3rd October 2009. This was very big event where 250+ attendees attend the excellent session of Vinod Kumar, Pinal Dave, Jacob Sebastian and Prabhjot Singh Bakshi. There were Four technical sessions by these excellent speakers. First session is of Vinod Kumar. He is Microsoft Evangelist. There was excellent session of Three hours where he give features of Windows 7 and Office 2010. Second session is of Pinal Dave, SQL MVP. He presented session on the subject - "SQL Server - The Other Side of Index". This was also excellent way to know more about SQL Index. and How to use index. That is really nice session for SQL Developers to know, How to optimize query. It gives proper understanding of indexes. Third session is of Jacob Sebastian, SQL MVP. He presented session on '"The Best Practices for Exception Handling and Defensive Programming in Microsoft SQL Server". This was also very great session where developer can get more idea about When exception occur and how to handle that exception. It was presented in a way so all attendees get clear idea about it and understand it properly. Forth and last session is of Prabhjot Singh Bakshi, MCT(Microsoft Certified Trainer). He presented session on "NET Framework 4.0". He also presented very nice session about featured of .Net Framework 4.0. He developed .Net code to give clear understanding of new features. Click with Vinod kumar and Jacob Sebastian: Overall, that was excellent day for Ahmedabad. I hope this type of TechDays scheduled in near future. Reference: Tejas Shah ( http://www.SQLYoga.com)

Read More

SQL SERVER: Check if Node exists in XML or not

Today, I have one requirement to check dynamically if a node exists in my xml or NOT. I have a stored procedure that receives XML and I need to check if the message information xml contains one Node or NOT. If that node exists then I need to execute that Stored Procedure by different logic and if not it should run with different logic. I figure it out by using EXISTS. This is my XML, that I got as parameter. DECLARE @ExportData XML SELECT @ExportData = '<Data Number="A123"> <BulkData> <EachData Parts="Test1" /> <EachData Parts="Test2" /> <EachData Parts="Test3" /> </BulkData> </Data>' Now I need to check if "BulkData" node exists in XML, then I need to write different logic to get the result. So, I used this SELECT @ExportData.exist('(//BulkData)') This will return "1" if node is exists else return "0". That's it. I can write based on the return result by this statement. Let me know if it helps you. Reference : Tejas Shah(http://www.SQLYoga.com)

Read More

SQL SERVER: Use CONTEXT_INFO

Recently I have following situation:

I don't need to execute trigger's code on some condition, like if it is called from particular stored procedure. If data is being updated from any other places, like application, or any other stored procedures, trigger code should be executed, but when one particular Stored Procedure is updating data to that table, it should not allow to do so.

I search and I got the option CONTEXT_INFO with SQL SERVER. Let me share this with all of you.

NOTE: We can use CONTEXT_INFO value in that session only. If there is new session we cannot use that value stored in CONTEXT_INFO.

We can set CONTEXT_INFO up to 128 bytes of binary information with the current session or connection. Reference: http://msdn.microsoft.com/en-us/library/ms180125.aspx

Let see an example, for better understanding:

CREATE TABLE tblA(
 ID INT IDENTITY,
 ColVal VARCHAR(100)
)

Let's create two SPs, which will insert a record into tblA, which is just created

CREATE PROC TestA
AS
BEGIN 
    INSERT INTO tblA(ColVal)
    SELECT 'Allow To insert'
END

CREATE PROC TestB
AS
BEGIN 

    DECLARE @UID VARBINARY(128)
    SELECT @UID = CAST('TestB' AS VARBINARY(128))
    SET CONTEXT_INFO 0x5465737442
    
    
    INSERT INTO tblA(ColVal)
    SELECT 'Not Allow To insert'

END

In this example, when we execute SP: TestA, it should allow to insert record in the table, while we execute SP: TestB, it should restrict. Here I set CONTEXT_INFO with SPName. I can use the same whatever is stored in CONTEXT_INFO during the current session.

To make validation as defined above, lets create one trigger to restrict/allow user to proceed.

CREATE TRIGGER trg_TblA
   ON  tblA
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @Message varbinary(128)
    SELECT @Message = cast('TestB' as varbinary(128))
    
    IF    @Message = CONTEXT_INFO() BEGIN
        RAISERROR('Not Allowed to Insert/Update/Delete from SP: TestB',15,1)
        ROLLBACK TRAN
    END
        
END

In Trigger, as you can see, I again converted the SP name and compare it with CONTEXT_INFO.

When Stored Procedure TestA is being executed, CONTEXT_INFO is not set, so trigger will not find comparison and allows user to insert record.

While we execute TestB, as we have set CONTEXT_INFO, system will find comparison in trigger and restrict us to complete transaction. So system will give error message:

So, by this way we can validate value in trigger, to make such decision

Reference : Tejas Shah http://www.SQLYoga.com

Read More

SQL SERVER: Reset Setup Values, when SQL SERVER is started/restarted

We have a requirements to clear all setup values when SQL SERVER is started/restarted and we need to setup default values to setup table. I found one Stored Procedure provided by MS SQL SERVER. Let me share it with all of you. SQL SERVER provides this SP: “sp_procoption”, which is auto executed every time when SQL SERVER service has been started. I found this SP and it helps me to figure it out the solution for the request as following way. Let me show you how to use it Syntax use this SP: EXEC SP_PROCOPTION @ProcName = 'SPNAME', @OptionName = 'startup', @OptionValue = 'true/false OR on/off' @ProcName, should be Stored procedure name which should be executed when SQL SERVER is started. This stored procedure must be in “master” database. @OptionName, should be “startup” always. @OptionValue, this should be set up to execute this given sp or not. If it is “true/on”, given sp will be execute every time when SQL SERVER is started. If it is “false/off”, it will not. That’s it, lets take an example. I have one Database called Test, I have created setup table: CREATE TABLE SetupTable( Seq INT IDENTITY, Code VARCHAR(100) ) Lets insert some default values to this table: INSERT INTO SetupTable VALUES('A') INSERT INTO SetupTable VALUES('B') INSERT INTO SetupTable VALUES('C') INSERT INTO SetupTable VALUES('D') What I need to do is, I need to wipe out this values when SQL SERVER is started and fill it with the same default values, because these values might be updated by application.So, I created one stored procedure in master database, named, CREATE PROC ClearAllData AS DELETE FROM Test.dbo.SetupTable INSERT INTO SetupTable VALUES('X') INSERT INTO SetupTable VALUES('Y') INSERT INTO SetupTable VALUES('Z') and set up this stored procedure as auto executed every time when SQL SERVER is started as: EXEC SP_PROCOPTION @ProcName = 'ClearAllData', @OptionName = 'startup', @OptionValue = 'true' Now, restart SQL SERVICES, and you find that old values will be deleted and new values with ‘X’, ‘Y’, and ‘Z’ will be inserted automatically. If now you want to stop it to execute automatically, we just need to execute this with “false” as: EXEC SP_PROCOPTION @ProcName = 'ClearAllData', @OptionName = 'startup', @OptionValue = 'false' I hope this is very clear to use this feature.   Reference : Tejas Shah (http://www.SQLYoga.com)

Read More

SQL SERVER: Configure Database Mail with SQL SERVER 2005

We used Database mail to send mail to client on each updates.

This is a very simple process to configure. Let me share how to configure Database mail with sql server 2005 with all of you.

After setting up Profile and Account properly, you just need to write following code to send a mail to client:

Step 1:

Step 2:

Step 3:

Step 4: You might get this message:

Step 5: Create Profile

Step 6 : Create Account

That's it.

exec msdb.dbo.sp_send_dbmail
@profile_name = 'ProfileName', IN our CASE, 'Tejas'
@recipients = 'Client Email Address' ,
@blind_copy_recipients = 'BCC Address',
@subject = 'Subject',
@BODY = 'Message Body',
@body_format = 'Message Type', it could be text OR html

Let me know if you have any complexity or comments in setting up Database mail.

Read More

SQL SERVER: Read values from Comma Separated variable

As we have seen, How to generate Comma separated List in SQL. Today we know, how to get values from Comma separated column. Many times developers asked, How can I read comma separated values from variable? There are many ways to get solution for this. Lets discuss about the best way, I think so. We can use XML to read values from comma separated values. XML made our life easy.

 

Example:

I have created one procedure which has one parameter VARCHAR(100). This procedure contains the parameter having value like '1,5,6,20'. The requirement is: Update the records having ID = 1,5,6,20 (Any of them). This is the one way that we can prepare the SQL query.

 

Proposed Solution:

Convert VARCHAR variable to XML as follows:

SET @xmlIDs =    '
                    ' + REPLACE(@str, ',', '') + '' +
                ''

So this statement will generate XML from VARCHAR value as follows:

  1
  6
  7
  8
  20

So, Now this is the XML, which can be easily read in SQL SERVER (2005 and above) as:

SELECT x.v.value('.','INT')
FROM @xmlIDs.nodes('/IDs/ID') x(v)

This will give me result set as: ( as separate table)

So that’s it, Now I can easily use this result set in my query, to update the rows accordingly.

So my Procedure looks like:

CREATE PROC Test_ReadValuesFromCommaSeparatedVariable
    @str    VARCHAR(100)
AS

DECLARE @XmlIDs XML

SET @xmlIDs =    '
                    ' + REPLACE(@str, ',', '') + '' +
                ''

UPDATE    TableName
SET        Flag = 1
WHERE    ID IN(
                SELECT x.v.value('.','INT')
                FROM @xmlIDs.nodes('/IDs/ID') x(v)
            )
            

 

So, it is very easy to read values from Comma separated value.

 

Let me know if it helps you.

Read More

SQL SERVER - Query to compare number of Rows between different Databases

We have two databases in the same SQL server instance. Both of the databases are copy of the production database, so both contains same tables. We added some records in Test Database’s some tables to update some features. We have added data in many tables of test database. Now we need to also update Production DB with the updated data. To update Production Database, we need to make sure in which tables we have updated data and then we will check and update production database accordingly. We have many numbers of tables, and we have updated many tables, so its not possible for us to check it manually. So, we need to make query to compare rows of each table with the another database tables, to find out which tables has different rows then the original database. Solution: As I need to solve this problem, I write a query which will give me Rows of each table in one Database. As I need to compare it with another database I write the following query to come out with the solution.  Example: I have two databases. Database A and Database B. I need to make a report, which will give me details of each table and rows. I made this Stored Procedure in Database A. CREATE PROC CompareRowsBetweenDatabas AS WITH A AS( SELECT sch.name AS SchemaName, st.Name AS TableName, SUM( CASE WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows ELSE 0 END ) AS Rows FROM sys.partitions p INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id INNER JOIN sys.tables st ON st.object_id = p.Object_ID INNER JOIN sys.schemas sch ON sch.schema_id = st.schema_id GROUP BY st.name, sch.name ), b as( SELECT sch.name AS SchemaName, st.Name AS TableName, SUM( CASE WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows ELSE 0 END ) AS Rows FROM B.sys.partitions p INNER JOIN B.sys.allocation_units a ON p.partition_id = a.container_id INNER JOIN B.sys.tables st ON st.object_id = p.Object_ID INNER JOIN B.sys.schemas sch ON sch.schema_id = st.schema_id GROUP BY st.name, sch.name ) SELECT a.SchemaName, a.TableName, a.Rows, b.SchemaName As BSchemaName, b.TableName AS BTableName, B.Rows AS BRows FROM a INNER JOIN b ON a.TableName = b.TableName AND a.SchemaName = b.SchemaName EXEC CompareRowsBetweenDatabase This will give me results as I need. Let me know if it helps you.

Read More

SQL SERVER: Difference between OpenQuery and OpenRowSet

Today, one developer asked me what is the difference between OpenQuery and OpenRowSet.

Let me share this thing with all of you.

Syntax for both the command:

OPENQUERY ( linked_server ,'query' )

OPENROWSET
( 'provider_name' , 'datasource' ; 'user_id' ; 'password'
, { [ catalog. ] [ schema. ] object | 'query' }
)

Difference is:
OpenQuery uses a predefined linked server,
While OpenRowSet has to specify all the connection options. So with OpenRowSet you can query to your remote SQL server from local.

Else it's the same.

Let me know if it helps you in any way.

Read More

SQL SERVER: SQL Query To Find Most used Tables

We have very large database and today we want to search the tables which are used mostly. Means tables which are used in Procedures, Constraints, Views, Triggers etc. I know this is very strange requirement, but we need to do this. So, I tried to make an query which will help me to find out the top most tables used in other objects as I mentioned Let me share that sp with all of you: SELECT TableName, COUNT(*) FROM ( Select Distinct o.Name 'TableName', op.Name 'DependentObject' From SysObjects o INNER Join SysDepends d ON d.DepId = o.Id INNER Join SysObjects op on op.Id = d.Id Where o.XType = 'U' Group by o.Name, o.Id, op.Name ) x GROUP BY TableName ORDER BY 2 desc So, I made my life easy, by using this. I can get the list if Tables which are used most. Let me know if it helps you in any way.

Read More

SQL SEVER: How to make an Entry in Event Viewer using SQL SERVER

Today I came across the situation where I need to following scenario. I need to make entry in Event Viewer when there is an error in Stored Procedure. I wondered to do this, but finally I come up with the solution. SQL is much powerful. SQL provides us to make an entry in Event Viewer by two ways: 1. using XP_LogEvent 2. By Raiserror WITH LOG Lets first see the way using XP_LogEvent: Here I have created one SP which will raise an error “Divide by zero error encountered.” as I tried to do “10 / 0”. CREATE PROCEDURE TestEventViewer AS BEGIN TRY     SELECT  10/0 END TRY BEGIN CATCH     PRINT ERROR_Message()     DECLARE @msg VARCHAR(100)     SELECT @msg = ERROR_Message()     EXEC xp_logevent 60000, @msg, informational END CATCH Lets Execute this SP: EXEC TestEventViewer This will write entry in Event Viewer. Now open Event Viewer. You can find Event Viewer at Control Panel –> Administrative Tools –> Event Viewer. You will get en entry of Error there. So we can do this by Extended Stored Procedure: “xp_logevent”. Let see the parameters of this Procedure. First Parameter: “60000” is the Error Number Second Parameter: “@msg” is the message to be displayed in Event Viewer. Third Parameter: “informational” is the Error Level. It could be “informational”, “Error”, “Warning”. Now, Lets see by another way By Raiserror WITH LOG: It is the same way as we used Raiserror to Raise an Error. CREATE PROCEDURE TestEventViewer AS BEGIN TRY     SELECT  10/0 END TRY BEGIN CATCH     PRINT ERROR_Message()     DECLARE @msg VARCHAR(100)     SELECT @msg = ERROR_Message()     RAISERROR(@msg, 11, 1) WITH LOG END CATCH Lets Execute this SP: EXEC TestEventViewer. So By these ways we can make an entry to Event Viewer. Let me know if it helps you in any way

Read More