The cursor does not include the table being modified or the table is not updatable through the cursor

Hello Friends,

Recently I came through this error “The cursor does not include the table being modified or the table is not updatable through the cursor“. It turned my mind to solve. Here is my experience with it.

Problem:

Server A is inserting data into Server B using linked server but while Inserting following error were occurring.

Error msg: 

The cursor does not include the table being modified or the table is not updatable through the cursor

Finding & Solution:

Most internet finding were suggesting to recreate the linked server but in that was not my case. Actually, Server A was inserting data into Server B using Linked server  into a View and that view had SELECT statement including “WITH (NOLOCK)” option which was causing that error. I just removed the “WITH (NOLOCK)” option from the view definition and problem got solved.

Hope! this will help someone else too. 🙂

Please share your experience with this error.

How to find SQL Server services running under which account using T-SQL query

Every SQL DBA, sometime wants to know about SQL Server services running under which account.  One day, my good friend and colleague asked me, how to get information about all SQL server services an…

Source: How to find SQL Server services running under which account using T-SQL query

How to find SQL Server services running under which account using T-SQL query

Every SQL DBA, sometime wants to know about SQL Server services running under which account.  One day, my good friend and colleague asked me, how to get information about all SQL server services and their account details using TSQL query?

I was wondering because I used to check using configuration manager but he wants to get information about more than 30 servers, so manual checking could not work for him.

Eventually, I came to know that I can get the info from registry using master.dbo.xp_instance_regreadn extended stored procedure and build SQL Server Services Info V_0.1 (Click the query link to download the query) query to get information about SQL Server services and its account details.

A dynamic management View (i.e. sys.dm_Server_Services) also provides information about SQL Server, Full-Text, and SQL Server Agent services in the current instance of SQL Server but not for other services.

Hope this will help some one else too 🙂

Note: After download, you need to change its extension from .key to .sql

  Column Name Description
Service Code SQL Server Service Code
Service Name Service display name
Account Name Account name, which run the service
Description Full description
Path File path

 

SQL Server Agent Job Monitoring

It happens that sometimes we need to monitor the SQL Server agent jobs and its schedule timings. This query monitors SQL Server Agent jobs and its schedule frequencies.

This will helpful for situations like to view basic info, any downtime, evaluation of no activity time for up-gradation process or backup process etc.

Query output columns descriptions are shown in below table.

Download query Mon_Sql_Server_Agent_Job  here

(you need to change the extension from .key to .sql or you can open to text as well)

Version: 1.1

Date: 25-Sept-2015

Description:

Sometimes we need to amend schedules as per our requirement, some days before I had to put multi scheduling ranges for single job process, which runs every 5 minutes, due to some very heavy bulk batch activity. This forced me to change my query to display, the start and end date & time of every job schedules so we can monitor gaps in between.

Download new query: Mon_Sql_Server_Agent_Job_V_1.1

  Column Name Description Remarks
name SQL Server agent job name
Owner Name Owen’s Name Some Windows Service group accounts are not appearing
description Description of Agent Job
isEnable Enabled/ Disabled
Frequency Type · One Time Only
· Daily
· Weekly
· Monthly
· Monthly, Relative to freq_Interval
· Runs when the SQL Server Agent service starts
· Runs when the computer is idle
Frequency Interval Unused Every n days Weekdays name
Frequency Sub Type If the frequency occurs within same day then the further details will display here e.g. Every 15 Minutes.
Next Run Date  Next job running day
Next Run Time  Next job running time
active_start_time Schedule start time
active_end_time Schedule end time
active_start_date Schedule start date
active_end_date Schedule end date
Sch Version Change version of schedule

References:

MSDN:

https://msdn.microsoft.com/en-us/library/ms178644.aspx

https://msdn.microsoft.com/en-us/library/ms189817.aspx

https://msdn.microsoft.com/en-us/library/ms178593.aspx

Datetime conversion:
http://stackoverflow.com/questions/12568408/how-to-convert-an-integer-time-to-hhmmss00-in-sql-server-2008

Welcome to SQLDig.com

Hello friends! Welcome to SQLDig.com 🙂

Nowadays, Microsoft SQL Server has proven his strength as stable RDBMS product; many industries (different sizes) are using it for their requirement fulfillment. In this competitive corporate world many of us either migrated or started his/her career in SQL Server database development/administration etc. As I had started my career as dot net developer but converted into Sql Server side and felt a pleasure.

As the usage of databases has been increasing; the size of databases has also been increasing too.  After holding TBs of important and expensive data, it’s very critical and important job to maintain your database very efficiently with security.

In Order to explore the some points of SQL Server database in terms of performance, I am building this site to gather information and put some live experience to troubleshoot or monitor a SQL Server environment.

Hope you will enjoy it. 🙂