Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

How to get call stack info in SQL Server's catch block?

T
tahishae last edited by
When I have multiple errors in my query, and I use try catch and error_message() function in my catch block, I only get the last error message.

For example, if my errors are:
```
Msg 2714, Level 16, State 5, Line 14
There is already an object named 'IX_EntityTypeConfigs' in the database.
Msg 1750, Level 16, State 1, Line 14
Could not create constraint or index. See previous errors.
```
in the catch block I just get:
```
Could not create constraint or index. See previous errors.
```
How can I get the whole message?

I have searched and saw that this is a limitation of SQL Server, but articles belonged to 2014 and older than that.

I wonder if things has changed since.
Reply Quote 0
1 Reply Last reply
E
emerson last edited by

I believe it's still correct. The recommended way is to rethrow the errors to the application via the THROW command and let the app handle it.

The best resource is still this article by https://www.sommarskog.se/error_handling/Part1.html

You can also capture the errors with Extended Events (XE) error_reported (on SQL Server side). That would show you all the errors that were fired.

I blog about it here: https://straightforwardsql.com/posts/investigating-errors-with-extended-events/ .
Reply Quote 0
1 Reply Last reply

2
Posts

0
Views

Suggested Topics

T

Setting Up SQL Alerts
SQL, Database Testing • sql server ssms sql server agent alerts • • terrea

2

0
Votes

2
Posts

0
Views

T

One option would be to handle some of this logic inside your code running in the job... this works if you are doing things in batches, or if you are using a cursor or something similar. Procedure to be Executed by Job --create some_stored_procedure as declare @procStartDate datetime = getdate() declare @threshold int = 2 --doing something in batches, like batch delete, etc. --could also uses a cursor here for reindexing, or what ever... waitfor delay '00:00:05' --after each batch, check the time that's expired --if your threshold is met, put an informational message in the error log--or a fatal error if you want it to stop if datediff(second,@procStartDate,getdate()) > @threshold begin exec xp_logevent 50001,'Stored procedure: some_stored_procedure has been running past threshold', informational end select ProcStart = @procStartDate, CurrentDT = getdate() Set Up Alert General Name it and select Type = SQL Server event alert Set Severity = 10 Check "Raise Alert when message contains" and enter "some_stored_procedure" or something unique to the message in xp_logevent Response Check Notify operator and set the operator That is, test it out! You can just create the alert and run the code above without creating a proc and it should email you. Another Option If this ins't feasible, and your threshold is high enough where you wouldn't mind running a job every X minutes, you could create a job to query the sys.jobactivity for jobs running that don't have a stop time and then send an email with sp_send_dbmail. Or, if your job has a lot of small steps, add this to the end of each step. What ever works for your environment. declare @minuteThreshold int = 20 if exists( SELECT ja.job_id, j.name AS job_name, ja.start_execution_date, ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id FROM msdb.dbo.sysjobactivity ja JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id WHERE start_execution_date is not null AND stop_execution_date is null AND j.name = 'yourJobName' AND datediff(minute,ja.start_execution_date,getdate()) > @minuteThreshold) exec sp_send_dbmail
G

Why would a SQL Server 2008 R2 database disable a table index when upgraded to SQL Server 2012
SQL, Database Testing • sql server sql server 2012 backup upgrade • • gionna

2

0
Votes

2
Posts

0
Views

J

This has to do with any table which contains a geospatial field. I'm not sure why yet (is it a bug or a feature lol), but that's the cause. Update: the problem only appears when there is a geospatial field and a PERSISTED column that uses it.
Installing SQL Server 2017, I get E: Unable to locate package mssql-server
SQL, Database Testing • sql server linux ubuntu sql server 2017 • • Analeea

2

0
Votes

2
Posts

0
Views

Normally, a distro packages things in a specific format, so for instance adding a repository like deb [arch=amd64] https://packages.microsoft.com/ubuntu/17.10/prod zesty main Will tell apt to download a package list and look for the packages here, https://packages.microsoft.com/ubuntu/17.10/prod/pool/ However, with Microsoft that's not the case. They have a repository for 17.10 (as they should) which has all their packages for Ubuntu, but they have another repository specifically for SQL Server. To make matters more confusing, that SQL Server repository is in the tree of 16.04. So they have both (pay attention to the /pool/s) https://packages.microsoft.com/ubuntu/16.04/mssql-server/pool/main/m/ https://packages.microsoft.com/ubuntu/16.04/prod/pool/main/m/ You need the 16.04/mssql-server repository. You can add that with sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)" You may think because you have the 17.10/prod and you have SQL Server installed that you don't need the 16.04/prod repository. That's incorrect, you'll need that too if you want the SQL Client sqlcmd provided by mssql-tools sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list)" I did file a doc bug upstream with a description of the confusion so maybe they'll fix it someday If you find your system in an inconsistent state for any reason, you can always remove SQL Server and start over.
C

Need to migrate a SQL Server 6.5 Prod to SQL Server 2012 in VM
SQL, Database Testing • sql server migration • • Cleofas

2

0
Votes

2
Posts

0
Views

A

You can update 6.5 to 2000, from 2000 to 2008 and from there go to 2012 but its much better to script out the schema and bcp the data as Kin suggests. As he seems to be to humble to link to his own post I'll do it for him: Restore SQL Server 2012 backup to a SQL Server 2008 database?
T

What can data cache contain besides pages from physical disk?
SQL, Database Testing • sql server cache • • Trenton

2

0
Votes

2
Posts

0
Views

B

There are couple of things here so let me explain Logical reads: A logical read occurs every time the Database Engine requests a page from the buffer cache. If the page is not currently in the buffer cache, a physical read first copies the page from disk into the cache. Read aheads: I would suggest you to read Reading Pages in SQL Server The Database Engine supports a performance optimization mechanism called read-ahead. Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query. This allows computation and I/O to overlap, taking full advantage of both the CPU and the disk. The read-ahead mechanism allows the Database Engine to read up to 64 contiguous pages (512KB) from one file. The read is performed as a single scatter-gather read to the appropriate number of (probably non-contiguous) buffers in the buffer cache. If any of the pages in the range are already present in the buffer cache, the corresponding page from the read will be discarded when the read completes. The range of pages may also be "trimmed" from either end if the corresponding pages are already present in the cache. Now to your question physical_reads are 0 and read-ahead reads are 276 which means that 276 pages of that table which are on the disk drive were in data cache, No that does not means just the 276 pages it can be much more than 276 actually it is always more than that. Read aheads read *more than required pages *and bring that pages into memory this is done for performance optimization to make sure all the pages are in cache and physical I/O can be avoided. A single read may bring upto 64 pages and this would be seen as one read.From Old BOL document Read-ahead requests are generally 128 pages for each group but can be as many as 1,024 pages when running Microsoft SQL Server Enterprise Edition. From this Old Technet Article, for enterprise edition Read Ahead can be up to 1024 KB. Logical reads are counted for single pages while read aheads are counted as one for chunk of pages.
Z

Upgrading from SQL Server 2012 SP1 to SQL Server 2019
SQL, Database Testing • sql server 2012 sql server 2019 • • zymir

2

0
Votes

2
Posts

0
Views

A

This handy page from Microsoft should be very useful for you as it explains in detail how to carry out a migration and the tools and methods available: Supported Version and Edition Upgrades for SQL Server 2019 Use the Data Migration Assistant (DMA) to scan your databases to make sure they can be migrated to the 2019 server. It will highlight any potential blocking issues such as deprecated features that you may need to resolve before you can migrate. It looks like you will still need to upgrade your SQL Server 2012 to SP4 to be fully supported though.
A

How to find how many number of transactions are happening in SQL Server/SQL Server database in a day?
SQL, Database Testing • sql server sql server 2012 sql server 2008 r2 sql server 2016 windows server • • authera

2

0
Votes

2
Posts

0
Views

R

The Perfmon counters for Transactions/Sec and Batch Requests/Sec are cumulative counters, meaning they keep going up after the SQL Server starts up. To get a count per day since startup, divide the counter output by the number of days that the server has been up. An easy way to do that is to check the creation date of TempDB - here's an example query, but keep in mind that rounding will be a problem if the server's been up for less than a day or two: SELECT DATEDIFF(dd, create_date, GETDATE()) FROM sys.databases WHERE database_id = 2; To get daily numbers going forward, consider saving the contents of that counter to a table. Then you can take a day's metric, subtract the day before, and you'll get the counter delta for that one day. (I use sp_BlitzFirst and the Power BI Dashboard for DBAs to do this with 15-minute granularity. (Disclaimer: those are open source tools that I wrote.)) Unfortunately, if you try to do daily sampling, it can be inaccurate due to: The server restarting or clustered SQL Server service failing over The Availability Group or database mirror transitioning roles (failing over) does not reset those counters, but you'll need to add up the transactions or batches from both replicas If you're combining metrics from multiple replicas, keep in mind that you've also probably got monitoring software, and the monitoring software's queries are also going to be counted
E

Can I restore a SQL Server .bak files without SQL Server?
SQL, Database Testing • sql server backup restore • • elysha

2

0
Votes

2
Posts

0
Views

R

Here is what I suggest: build a virtual machine running Windows, with enough disk space to hold the backup. Copy the backup file there. If you don't already have the ability to build virtual machines, you could do so with free products like Oracle VirtualBox. download and install the evaluation edition of SQL Server. Make sure you include both the database engine and Management Tools - Complete. if the VM has enough space to hold the backup but not enough space to also restore it, you can perform a "virtual restore" using the trial version of a product from Red-Gate with the same name (which allows you to interact with the backup file as if it had been restored). Otherwise, restore the database the normal way. Once the database is available (either through a normal restore or virtual restore), you can generate scripts for the schema and data in the following way: Open Management Studio and connect to your instance. Open Object Explorer. Right-click your newly restored database, choose Tasks > Generate Scripts... Click Next, Click Next On the "Choose Script Options" page, scroll down and set "Script Data" to True Click Next Check all of the relevant objects and click Next Check the tables you want and click Next Choose to script to a file. Now you'll have a file that contains all your objects and data using SQL Server insert syntax, you will have to play with the output to get it in a format that works for Postgres (I am not privy to any minor syntax differences). Alternatively you can try and play with the bcp utility to extract data to CSV files or similar, but you'll have to do this table-by-table or use some clever scripting (PowerShell, T-SQL, C#/SMO, etc.) to generate all of the bcp commands for you. Once in CSV files, it should be trivial to bulk load the data into Postgres (but you will still have some work to generate the tables). As a final suggestion, if the .bak file is not ginormous, and the data is not confidential, I am more than willing to try and generate files for you in the format you need. I have plenty of Windows VMs with space, the challenge would be getting the .BAK file to a place where I can retrieve it - especially if it's larger than most of the file-sharing services support.
Approvals to a temporary table
Software Programming • sql server • • Bogopo

2

0
Votes

2
Posts

0
Views

Depends on what you want to get (Select) or have (Update).Create table Employees (FirstName varchar(100)); Go Insert into Employees(FirstName) values ('Nancy'), ('Andrew'), ('Janet'), ('Steven'); go In the scenario you pose, given that you do not see the reason for having a temporary table, you can also not know whether to change the data of it.SELECT FirstName INTO #tmpEmpleado FROM Employees DECLARE @Equivalencias TABLE(Nombre VARCHAR(100), NombreEquivalente VARCHAR(100)) INSERT into @equivalencias(Nombre, NombreEquivalente) VALUES('Andrew', 'Batman') ,('Steven', 'Superman') Select * from #tmpEmpleado temp left join @Equivalencias equival on temp.FirstName = equival.Nombre; If what you need is to get the first name, and in case it's equivalent, that one.SELECT FirstName INTO #tmpEmpleado FROM Employees DECLARE @Equivalencias TABLE(Nombre VARCHAR(100), NombreEquivalente VARCHAR(100)) INSERT into @equivalencias(Nombre, NombreEquivalente) VALUES('Andrew', 'Batman') ,('Steven', 'Superman') Select coalesce(equiVal.NombreEquivalente, temp.FirstName) as FirstName from #tmpEmpleado temp left join @Equivalencias equival on temp.FirstName = equival.Nombre; For example it is not necessary to have a temporary table. If it were the case.SELECT FirstName INTO #tmpEmpleado FROM Employees DECLARE @Equivalencias TABLE(Nombre VARCHAR(100), NombreEquivalente VARCHAR(100)) INSERT into @equivalencias(Nombre, NombreEquivalente) VALUES('Andrew', 'Batman') ,('Steven', 'Superman') Update temp set FirstName = coalesce(equiVal.NombreEquivalente, temp.FirstName) From #tmpEmpleado temp left join @Equivalencias equival on temp.FirstName = equival.Nombre; Select * from #tmpEmpleado;
O

Backing up to Nul on an SQL Mirror
SQL, Database Testing • sql server backup transac languagetion log mirroring • • obi

2

0
Votes

2
Posts

0
Views

J

If you are complying with all the RPO and RTO requierments by having database mirroring in place, with daily full backups then there are no data consistency issues with making backups to NUL. So the answer to your question is No. But (there is always a but) you are not having a secondary restore path if a full backup is corrupted and both the servers go down and your restore options are always the point in time when the daily backup is made. By making normal log backups and storing them for 48 hours you can restore past a single corrupted full backup which is not possible to do with differential backups.
SQL Server After Insert trigger does not perform actions
SQL, Database Testing • sql server sql server 2012 trigger dml • • carriann

2

0
Votes

2
Posts

0
Views

An INSTEAD OF INSERT trigger performs actions instead of what the original INSERT would have done. In your code, if either @appr_username or @appr_id is NULL, some sort of change is made to the base table (an insert or an update). Otherwise, nothing is done by the trigger (no rows affected), so the AFTER triggers are skipped. After all, SQL Server thinks, if the INSTEAD OF trigger did nothing, there can't be anything for an AFTER trigger to do either, right? Note you can also get some very confusing behaviours if the server configuration option nested triggers (shown as Allow Triggers to Fire Others in the SSMS Advanced Server Properties Tab) is set to 1 (the default). From that link: The nested triggers option controls whether an AFTER trigger can cascade. That is, perform an action that initiates another trigger, which initiates another trigger, and so on. When nested triggers is set to 0, AFTER triggers cannot cascade. When nested triggers is set to 1 (the default), AFTER triggers can cascade to as many as 32 levels. INSTEAD OF triggers can be nested regardless of the setting of this option.
F

Choosing the right storage block size for sql server
SQL, Database Testing • sql server storage size • • fbio

2

0
Votes

2
Posts

0
Views

G

The method is the one used in the article that you linked in your question: test it. You can capture a representative workload for your application/database and replay that workload on servers that have different cluster sizes. What you will find out is that SQL Sever works best with 64KB clusters, because of the way it reads data from disk (read-ahead). However, you don't have to do that: setting a 64KB cluster size is an established best practice, as clearly stated in the article that you refer to. From the article: The file allocation unit size (cluster size) recommended for SQL Server is 64 KB; this is reflected in Figure 4. If you decided to test the performance under different cluster sizes, you would probably discover that the difference is negligible, especially on high-end storage systems.
SQL Server TRY..CATCH resume on terminating error
SQL, Database Testing • sql server stored procedure error handling • • emmalee

2

0
Votes

2
Posts

0
Views

C

I've found a solution, not as elegant as I'd hoped, but it works. If someone has something better I'd like to hear it. The problem with the masterFailProc is it's wrapping a TRY..CATCH block around the entire script, instead of around individual EXEC calls. If the definition is changed as below, then it will happily log errors on a per-exec basis rather than terminating the entire script: CREATE OR ALTER PROCEDURE masterFailProc AS BEGIN SET XACT_ABORT OFF; BEGIN TRY INSERT INTO my_error_log SELECT CURRENT_TIMESTAMP, 'start'; BEGIN TRY EXEC failProc; END TRY BEGIN CATCH /* log error */ END CATCH; BEGIN TRY EXEC failProc; END TRY BEGIN CATCH /* log error */ END CATCH; BEGIN TRY EXEC failProc; END TRY BEGIN CATCH /* log error */ END CATCH; INSERT INTO my_error_log SELECT CURRENT_TIMESTAMP, 'end'; END TRY BEGIN CATCH INSERT INTO my_error_log SELECT CURRENT_TIMESTAMP, 'fatal error'; END CATCH END; GO
Inplace upgrade from SQL Server 2008 Standard to SQL Server 2014 evaluation
SQL, Database Testing • sql server upgrade • • Analeea

2

0
Votes

2
Posts

0
Views

You cannot upgrade from SQL Server 2008 SP4 to SQL Server 2014 evaluation edition. Its not supported and wont allow you please see Supported Version and edition upgarde You should not worry much about upgrading from 2008 Sp4 to 2014. The upgrade would not be a much of problem but how would application behave is something on which you should focus. You can download SQL Server 2014 evaluation edition , which is equivalent to enterprise edition for 180 days. Restore database from 2008 to 2014 and start testing. Don't use any enterprise features. When you are satisfied with application performance do an inplace upgrade to SQL Server 2014 standard Before doing in place upgrade make sure you run upgrade advisor and mitigate all issues(if any) pointed by UG advisor.
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.BestPractices_dbo.WorkOrder1_Id."
Software Programming • c sharp sql server entity framework • • briley

2

0
Votes

2
Posts

0
Views

C

If there is a table BEST_PRACTICE with:ID NAME COMMENT 1 RULE1 Правило А 2 RULE2 Правило Б 3 RULE3 Правило С There is a table Work_Order, where the field RULE_NAME is described as Foreign_KEY in Table BEST_PRACTICE and points to the field NAMEWork_OrderID CODE RULE_NAME 1 code1.cpp RULE2 2 code2.cpp RULE2 3 code3.cpp RULE1 That's when the work_Order type is added (field ID as IDENT)insert into work_order (code, rule_name) values ('code4.cpp', 'RULE5') There will be such a mistake because RULE5 as the key (external) in Table BEST_PRACTICE is not specified. ♪ a if, for example:insert into work_order (code, rule_name) values ('code4.cpp', 'RULE2') That's not gonna be a mistake. ♪

How to get call stack info in SQL Server's catch block?

Suggested Topics

Setting Up SQL Alerts SQL, Database Testing • sql server ssms sql server agent alerts • • terrea

Why would a SQL Server 2008 R2 database disable a table index when upgraded to SQL Server 2012 SQL, Database Testing • sql server sql server 2012 backup upgrade • • gionna

Installing SQL Server 2017, I get E: Unable to locate package mssql-server SQL, Database Testing • sql server linux ubuntu sql server 2017 • • Analeea

Need to migrate a SQL Server 6.5 Prod to SQL Server 2012 in VM SQL, Database Testing • sql server migration • • Cleofas

What can data cache contain besides pages from physical disk? SQL, Database Testing • sql server cache • • Trenton

Upgrading from SQL Server 2012 SP1 to SQL Server 2019 SQL, Database Testing • sql server 2012 sql server 2019 • • zymir

How to find how many number of transactions are happening in SQL Server/SQL Server database in a day? SQL, Database Testing • sql server sql server 2012 sql server 2008 r2 sql server 2016 windows server • • authera

Can I restore a SQL Server .bak files without SQL Server? SQL, Database Testing • sql server backup restore • • elysha

Approvals to a temporary table Software Programming • sql server • • Bogopo

Backing up to Nul on an SQL Mirror SQL, Database Testing • sql server backup transac languagetion log mirroring • • obi

SQL Server After Insert trigger does not perform actions SQL, Database Testing • sql server sql server 2012 trigger dml • • carriann

Choosing the right storage block size for sql server SQL, Database Testing • sql server storage size • • fbio

SQL Server TRY..CATCH resume on terminating error SQL, Database Testing • sql server stored procedure error handling • • emmalee

Inplace upgrade from SQL Server 2008 Standard to SQL Server 2014 evaluation SQL, Database Testing • sql server upgrade • • Analeea

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.BestPractices_dbo.WorkOrder1_Id." Software Programming • c sharp sql server entity framework • • briley

Setting Up SQL Alerts
SQL, Database Testing • sql server ssms sql server agent alerts • • terrea

Why would a SQL Server 2008 R2 database disable a table index when upgraded to SQL Server 2012
SQL, Database Testing • sql server sql server 2012 backup upgrade • • gionna

Installing SQL Server 2017, I get E: Unable to locate package mssql-server
SQL, Database Testing • sql server linux ubuntu sql server 2017 • • Analeea

Need to migrate a SQL Server 6.5 Prod to SQL Server 2012 in VM
SQL, Database Testing • sql server migration • • Cleofas

What can data cache contain besides pages from physical disk?
SQL, Database Testing • sql server cache • • Trenton

Upgrading from SQL Server 2012 SP1 to SQL Server 2019
SQL, Database Testing • sql server 2012 sql server 2019 • • zymir

How to find how many number of transactions are happening in SQL Server/SQL Server database in a day?
SQL, Database Testing • sql server sql server 2012 sql server 2008 r2 sql server 2016 windows server • • authera

Can I restore a SQL Server .bak files without SQL Server?
SQL, Database Testing • sql server backup restore • • elysha

Approvals to a temporary table
Software Programming • sql server • • Bogopo

Backing up to Nul on an SQL Mirror
SQL, Database Testing • sql server backup transac languagetion log mirroring • • obi

SQL Server After Insert trigger does not perform actions
SQL, Database Testing • sql server sql server 2012 trigger dml • • carriann

Choosing the right storage block size for sql server
SQL, Database Testing • sql server storage size • • fbio

SQL Server TRY..CATCH resume on terminating error
SQL, Database Testing • sql server stored procedure error handling • • emmalee

Inplace upgrade from SQL Server 2008 Standard to SQL Server 2014 evaluation
SQL, Database Testing • sql server upgrade • • Analeea

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.BestPractices_dbo.WorkOrder1_Id."
Software Programming • c sharp sql server entity framework • • briley