SqlServer restore database error

[Origin]: https://stackoverflow.com/questions/18699463/sqlserver-restore-database-error

Use this code to destory all existing connections before restore:

USE master;
GO
ALTER DATABASE YourDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE YourDB
SET MULTI_USER;
GO

Select the backup file to restore and go to options to select “Close existing connections to destination database” and click ok

Screenshot

Use activity monitor and filter on the DB you are wanting to restore. Then check with the user that is using it and make sure it is ok to resotre the DB. Then run the following query.

    USE Master

    KILL <session_id from activity monitor>
Advertisements

SQL Server transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements

[Origin]: https://www.mssqltips.com/sqlservertip/4018/sql-server-transaction-count-after-execute-indicates-a-mismatching-number-of-begin-and-commit-statements/

Problem

There are situations where you might receive this error message “Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.”. Even when using a TRY CATCH block in the stored procedure and a ROLLBACK in the catch block, this error still occurs. One reason this is not captured is because fatal errors are not caught and the transaction remains open. In this tip we will look at how you can handle this issue.

Solution

We will look at an example to create this issue and how this can be resolved. This example is probably something you wouldn’t normally do, but this allowed me to show the issue with a real working example.

We create two different global temporary tables in different query windows:

--On Query Window 1
CREATE TABLE ##tmpStagingTest1
(
    ID INT IDENTITY(1,1),
    Spid SMALLINT,
    Value INT 
)

--On Query Window 2
CREATE TABLE ##tmpStagingTest2
(
    ID INT IDENTITY(1,1),
    Spid SMALLINT,
    Value INT UNIQUE
)

We then create the following stored procedure which works with these tables:

CREATE PROCEDURE uspInsertStageingDataTest
@pValue INT
AS
BEGIN

    SET NOCOUNT ON

    BEGIN TRY

        BEGIN TRANSACTION

        INSERT INTO ##tmpStagingTest1(Spid, Value)
        VALUES (@@SPID, @pValue)

        INSERT INTO ##tmpStagingTest2(Spid, Value)
        VALUES (@@SPID, @pValue)

        COMMIT

    END TRY
    BEGIN CATCH

        SELECT ERROR_MESSAGE()

        IF @@TRANCOUNT > 0
            ROLLBACK

    END CATCH

END

Without closing “Query Window 1” and “Query Window 2” (to make sure the global temporary tables exist) we open a new “Query Window 3” and execute the procedure:

EXEC uspInsertStageingDataTest @pValue=1

There are no errors and the SP runs successfully.

We can then check the data in the tables and the transaction count as follows running the below in “Query Window 3”:

SELECT * FROM ##tmpStagingTest1
SELECT * FROM ##tmpStagingTest2

SELECT @@TRANCOUNT AS Trancount

The result is the following: one row was inserted into each table and the transaction has committed:

SET_XACT_ABORT_ON

Now let’s close “Query Window 2”, so temporary table ##tmpStagingTest2 is deleted and run the uspInsertStageingDataTest stored procedure again:

EXEC uspInsertStageingDataTest @pValue=2

We then get the following error message:

uspInsertStageingDataTest

If we execute these statements again the results are as follows:

SELECT * FROM ##tmpStagingTest1
SELECT * FROM ##tmpStagingTest2

SELECT @@TRANCOUNT AS Trancount

The first statement returns two rows.

result_of_these_queries

The second statement returns an error.

transaction_count

And the third statement returns a Trancount of 1:

SELECT_@@TRANCOUNT

As we can see, one row was inserted in the first table, then the procedure failed and the transaction has not been committed, because an error has not been caught. This is a problem, because we want to implement the logic which inserts data into both tables or insert nothing. We can manually execute the rollback command in “Query Window 3”, after which the transaction will be rolled back and only the first row will remain in the ##tmpStagingTest1 table. If we try to close “Query Window 3” without doing a ROLLBACK we will receive the following message:

Query_Window

Solving The Issue

Let’s assume that we manually rolled back the transaction and there is only one row in ##tmpStagingTest1 table. The reason why the transaction remains open is that ##tmpStagingTest2 table is missing and it causes the fatal error. The catch block can’t catch fatal errors, so the transaction remains open.

To avoid similar situations and implement our logic without problems we can set XACT_ABORT ON in our procedure. When SET XACT_ABORT is ON and T-SQL statement raises a run-time error, SQL Server automatically rolls back the current transaction. By default XACT_ABORT is OFF. Let’s use this in our stored procedure as follows:

ALTER PROCEDURE uspInsertStageingDataTest
@pValue INT
AS
BEGIN

    SET NOCOUNT ON
    SET XACT_ABORT ON

    BEGIN TRY
        BEGIN TRANSACTION

        INSERT INTO ##tmpStagingTest1(Spid, Value)
        VALUES (@@SPID, @pValue)

        INSERT INTO ##tmpStagingTest2(Spid, Value)
        VALUES (@@SPID, @pValue)

        COMMIT
    END TRY
    BEGIN CATCH

        SELECT ERROR_MESSAGE()

        IF @@TRANCOUNT&gt;0
            ROLLBACK

    END CATCH

END

Then we run it again:

EXEC uspInsertStageingDataTest @pValue=3

We will receive the following message:

SELECT_ERROR_MESSAGE()

But in this case transaction is rolled back and the trancount is 0:

SELECT @@TRANCOUNT AS Trancount
SELECT_@@TRANCOUN

Now if we create the ##tmpStagingTest2 table again and run this command twice:

EXEC uspInsertStageingDataTest @pValue=3

The first time it completes successfully, because there is unique constraint on “Value” column in ##tmpStagingTest2 table, the second execution of the stored procedure returns the following message:

##tmpStagingTest2

This means that this error isn’t a fatal error and is handled in the catch block. We return ERROR_MESSAGE() which is selected in the catch block and the transaction is rolled back in the catch block. If we check the trancount we can see this value is 0.

SELECT @@TRANCOUNT AS Trancount
TRY_CATCH_blocks

When SET XACT_ABORT ON is included in a stored procedure without using TRY/CATCH blocks, transactions will roll back in case of errors. However, usually it’s necessary to handle errors in a specific way, for example returning the corresponding message or assign a corresponding response code to output variable when errors occur (not fatal errors). This kind of error handling can be developed in the CATCH block, so using SET XACT_ABORT ON with TRY/CATCH gives us a more flexible error handling solution.

Conclusion

Using SET XACT_ABORT ON in stored procedures can be very helpful in situations when there are transactions and the possibility of fatal errors. It helps developers avoid uncommitted transactions. Using SET XACT_ABORT ON in procedures with TRY/CATCH blocks, we should take into account the fact that all errors that are caught in the catch block are handled there. Only in cases when the CATCH block is unable to handle the error, thanks to the fact that XACT_ ABORT is ON, SQL Server rolls back the transaction.

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0

[Origin]: https://stackoverflow.com/questions/21930156/transaction-count-after-execute-indicates-a-mismatching-number-of-begin-and-comm

I have an Insert Stored Procedure which will feed data to Table1 and get the Column1 value from Table1 and Call the Second Stored Procedure which will feed the Table2.

But when I call The Second Stored Procedure as:

Exec USPStoredProcName

It gives me error as follows:

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

I have read the answers in other such questions and am unable to find where exactly the commit count is getting messed up.

If you have a TRY/CATCH block then the likely cause is that you are catching a transaction abort exception and continue. In the CATCH block you must always check the XACT_STATE() and handle appropriate aborted and uncommitable (doomed) transactions. If your caller starts a transaction and the calee hits, say, a deadlock (which aborted the transaction), how is the callee going to communicate to the caller that the transaction was aborted and it should not continue with ‘business as usual’? The only feasible way is to re-raise an exception, forcing the caller to handle the situation. If you silently swallow an aborted transaction and the caller continues assuming is still in the original transaction, only mayhem can ensure (and the error you get is the way the engine tries to protect itself).

I recommend you go over Exception handling and nested transactions which shows a pattern that can be used with nested transactions and exceptions:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount &gt; 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch
end
go

Current executing procedure name

[Origin]: https://stackoverflow.com/questions/6034488/current-executing-procedure-name

Is it possible to get the name of current Stored Procedure in MS SQL Server? May be there is any system variable or function like GETDATE() ?

You may try this:

SELECT OBJECT_NAME(@@PROCID)

Update: This command is still valid on SQL Server 2016.

Different techniques to identify blocking in SQL Server

[Origin]: https://www.mssqltips.com/sqlservertip/2732/different-techniques-to-identify-blocking-in-sql-server/

Problem

SQL Server is able to service requests from a large number of concurrent users. When SQL Server is servicing requests from many clients, there is a strong possibility that conflicts will occur because different processes request access to the same resources at the same time. A conflict in which one process is waiting for another to release a resource is called a block. Although in SQL Server a blocked process usually resolves itself when the first process releases the resource but there are times when a process holds a transaction lock and doesn’t release it. In this tip, we will learn different techniques to troubleshoot and resolve blocks in SQL Server.

Solution

In order to resolve a blocked process, we first need to determine which process is the blocking process and then if possible kill the blocking process. There are many different ways in SQL Server to identify a blocks and blocking process that are listed as follow:

  • Activity Monitor
  • SQLServer:Locks Performance Object
  • DMVs
    • sys.dm_exec_requests
    • sys.dm_tran_locks
    • sys.dm_os_waiting_tasks
  • SQL Server Profiler Locks Event Category

Each of these tools reports different information which is helpful in resolving blocks quickly. Let’s have a look at these tools in details:

1) Activity Monitor

Activity Monitor is a tool in SQL Server Management Studio that gives you a view of current connections on SQL Server. You can use Activity Monitor to view information about the current processes and locks held on SQL Server resources. To open Activity Monitor in SQL Server Management Studio, right-click the SQL Server instance name in Object Explorer and then select Activity Monitor:

Activity Monitor

Launch Activity Monitor

To find blocked process with Activity Monitor, first click on Processes in Activity Monitor to open the Process Info page:

ProcessInfo Page

Process Info Page and Locating Blocking Process

Then locate the process that is waiting, and then scroll over to the Blocked By column and note the Process ID in that column. Find that Process ID in Process Info page. and

Locate Blocked Process

If you want to terminate the blocking process, right-click it and choose Kill Process:

Kill Blocked Process

2) The SQLServer:Locks performance object

You use SQLServer:Locks object counter in Performance Monitor to view current statistics or create a log or alert to monitor locks. For example, you can monitor Average Wait TimeNumber of deadlocks/sec and Lock Timeouts/sec statistics to determine whether there is a problem with resource contention on SQL Server. However, you will need additional information to determine the exact cause of the problem. Follow the steps below to monitor the SQLServer: Locks performance counter:

On the Start menu, point to Run, type perfmon in the Run dialog box, and then click OK to launch Performance Monitor.

Launch Performance Monitor

Launching Performance Monitor

 

  • Right-click anywhere on the screen and then choose Add Counters.
    Launch Performance Monitor

    Add counters

  • Scroll down to locate SQL Server lock counters and add these three counters and then click OK.
    • Average Wait Time
    • Number of deadlocks/sec
    • Lock Timeouts/sec
    Launch Performance Monitor

    3) DMVs (Dynamic Management Views)

    sys.dm_exec_requests

    You can use the sys.dm_exec_requests dynamic management view to obtain detailed information about the requests currently executing on SQL Server. The dynamic management view includes detailed information about the query and query plan, status of request and information about the amount of time it has been executing. The columns you are most likely to use when troubleshooting a block or deadlock are as follow:

    1. blocking_session_id – The SPID of the blocking session.
    2. wait_type – Type of wait.
    3. wait_time – Length of time request has been waiting (in milliseconds).
    4. last_wait_type – If a wait has ended, its type is listed here.
    5. wait_resource – Name of resource the request is waiting for.
    6. transaction_isolation_level – Isolation level for the transaction.
    7. lock_timeout – Length of time a lock can exist before timing out

    To view blocked process execute the following query:

    USE [master]
    GO
    SELECT  session_id
     ,blocking_session_id
     ,wait_time
     ,wait_type
     ,last_wait_type
     ,wait_resource
     ,transaction_isolation_level
     ,lock_timeout
    FROM sys.dm_exec_requests
    WHERE blocking_session_id &lt;&gt; 0
    GO
    

    sys.dm_tran_locks

    You can view information about current locks and the processes blocking them using the sys.dm_tran_locks dynamic management view. This column has one of three values: GRANT, WAIT or CONVERT. The value of CONVERT means that the requestor has been granted a request but is waiting to upgrade to the initial request to be granted. To locate information about all locks with a request status of CONVERT, you execute the following:

    USE [master]
    GO
    SELECT * from sys.dm_tran_locks
    WHERE request_status = 'CONVERT'
    GO
    

    The request_session_id column contains the Process ID for the process. To view locking in the particular database, execute the following query that joins sys.dm_tran_locks with sys.partitions:

    USE [master]
    GO
    SELECT   tl.resource_type
     ,tl.resource_associated_entity_id
     ,OBJECT_NAME(p.object_id) AS object_name
     ,tl.request_status
     ,tl.request_mode
     ,tl.request_session_id
     ,tl.resource_description
    FROM sys.dm_tran_locks tl
    LEFT JOIN sys.partitions p 
    ON p.hobt_id = tl.resource_associated_entity_id
    WHERE tl.resource_database_id = DB_ID()
    GO
    

    sys.dm_os_waiting_tasks

    The sys.dm_os_waiting_tasks dynamic management view reports information about the blocked and blocking processes. The blocked process is listed in the session_id column. The blocking is listed in the blocking_session_id column.

    Execute the following to view wait stats for all block processes on SQL Server:

    USE [master]
    GO
    SELECT   w.session_id
     ,w.wait_duration_ms
     ,w.wait_type
     ,w.blocking_session_id
     ,w.resource_description
     ,s.program_name
     ,t.text
     ,t.dbid
     ,s.cpu_time
     ,s.memory_usage
    FROM sys.dm_os_waiting_tasks w
    INNER JOIN sys.dm_exec_sessions s
    ON w.session_id = s.session_id
    INNER JOIN sys.dm_exec_requests r
    ON s.session_id = r.session_id
    OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
    WHERE s.is_user_process = 1
    GO
    

    This detail is good for a big picture, or to get a quick idea of the types of waits occurring, but most of the real diagnostics and tuning will occur at a statement level.

    4) SQL Server Profiler

    You use the SQL Server Profiler Locks event category to create a trace of events related to locks and deadlocks. You can choose one or more of these event classes:

    1. Deadlock_Graph_Event_Class — Creates an XML description of deadlocks.
    2. Lock:Acquired — Use in conjunction with Lock:Released to determine the types of locks being requested and the length of time they are retained.
    3. Lock:Cancel — Use to determine which locks are cancelled.
    4. Lock:Deadlock Chain — Use to determine the objects involved in a deadlock.
    5. Lock:Deadlock — Use to determine the objects and applications involved in a deadlock.
    6. Lock:Escalation — Reports information about locks that have been escalated to cover a larger resource. For example, when a row lock becomes a table lock.T-SQL
    7. Lock:Released — Use in conjunction with Lock:Acquired.
    8. Lock:Timeout(timeout>0) — Provides information about locks that have timed out due to blocking issues.
    9. Lock:Timeout — Provides the same information as Lock:Timeout (timeout>0), but includes timeouts where the duration was 0.

    5) sp_who/sp_who2

    Both sp_who/sp_who2 return information about all the sessions that are currently established in the database and these are denoted as spid’s. Both these store procedures accepts parameters. The blk column of sp_who and blkby column of sp_who2 contains the spid for blocking process. Running sp_who and sp_who2 is easy, for example following call of these procedures returns all process that are currently active on SQL Server:

    USE master;
    GO
    EXEC sp_who 'active';
    GO
    EXEC sp_who2 'active';
    GO
    

    6) Use KILL statement to terminate blocked process

    You use the KILL statement to view the status of a process or kill the process. The KILL statement has the syntax: KILL spid | UOW [WITH STATUSONLY]

    USE master;
    GO
    KILL spid | UOW [WITH STATUSONLY]
    GO
    

    You must pass either a spid or, if the process belongs to a Distributed Transaction Coordination (DTC) transaction, you must provide a Unit of Work (UOW). You must be a member of sysadmin or processadmin to kill a process. You can obtain the spid for the current session by running @@spid. You can obtain the spid for the sessions associated with a login by running sp_who2. If you don’t specify a login, sp_who2 returns information for all current connections. If you can’t kill a blocking process, you might have to restart the SQL Server service. Doing so will cause all current connections to close, so you should avoid restarting the service if possible.

    When to use each tool?

    The dynamic management views and Activity Monitor are good tools for learning the current state of a process and for resolving a current blocked process. System Monitor counters are ideal for identifying trends that indicate a stored procedure, application, or database configuration setting is causes a large number of blocked processes or timeouts due to blocking. Running a SQL Server Profiler trace is a good way to analyse detailed information and identify the cause of a large number of blocks.

 

 

Cannot find the object because it does not exist or you do not have permissions , error in SQL Server

[Origin]: https://stackoverflow.com/questions/1136628/cannot-find-the-object-because-it-does-not-exist-or-you-do-not-have-permissions

I have a database and have a sql script to add some fields in a table called “Products” of the database.

But when i am executing this script , I am getting the following error:

Cannot find the object "Products" because it does not exist or you do not have permissions

Why it’s error occured and what should I do to resolve it?

shareedit

Are you sure that you are executing the script against the correct database? In SQL Server Management studio you can change the database you are running the query against in a drop-down box on one of the toolbars, or you can start your query with this:

USE SomeDatabase

shareedit

I found a reason why this would happen. The user had the appropriate permissions, but the stored procedure included a TRUNCATE statement:

TRUNCATE TableName

Since TRUNCATE deletes items without logging, you (apparently) need elevated permissions to execute a stored procedure that contains it. We changed the statement to:

DELETE FROM TableName

…and the error went away!

shareedit

SQL Server – Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column

[Origin]: https://stackoverflow.com/questions/20948819/error-0xc0202049-data-flow-task-1-failure-inserting-into-the-read-only-column/20953512

I am using SQL Server 2008 import and export wizard. I need to import a database. I opened the SQL server import/export wizard and I went through the following actions:-

  1. for the destination I chose “SQL server native client 10”.
  2. then I selected copy data from one or more tables or view.
  3. SSIS run immediately

but i got the following errors,

Operation stopped…

  • Initializing Data Flow Task (Success)
  • Initializing Connections (Success)
  • Setting SQL Command (Success)
  • Setting Source Connection (Success)
  • Setting Destination Connection (Success)
  • Validating (Error)

Messages Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column “ActionID”. (SQL Server Import and Export Wizard)

Error 0xc0202045: Data Flow Task 1: Column metadata validation failed. (SQL Server Import and Export Wizard)

Error 0xc004706b: Data Flow Task 1: “component “Destination – AuditActions” (22)” failed validation and returned validation status “VS_ISBROKEN”. (SQL Server Import and Export Wizard)

It seems that I can not import identity columns and timestamps columns, so how I can force these values to be imported?

shareedit

 

Before Importing date execute the below query to set identity insert on

SET IDENTITY_INSERT TableName ON

// Do the Import Operations

after Importing date execute the below query to set identity insert off

SET IDENTITY_INSERT TableName OFF
shareedit

 

With all due respect to Kishore’s answer, that approach is valid if you’re modifying the packages. Since you’re using the import/export wizard, the more straight forward approach would be to check the Enable Identity Insert box on the Column Mappings tab.

In the Import Export Wizard, after selecting the table for copy, click the Edit Mappings... button

enter image description here

In the resulting screen, click the Enable identity insert property and your identities will be replicated over.

enter image description here

Same net result as issuing SET IDENTITY_INSERT TableName ON & OFF but this is done automagically at build time.

shareedit