I've done CU updates before in the past with no issues but I have learned that CU updates check the data locations towards the end and if they do not exist that update is going to fail. Is it safe to add these directories to the file system or not? SQL seems to be functioning just fine, this predates me so I have no idea why the directory structure change so I am curious

Got an issue I have not run into before. We have a bunch of ssrs reports with schedules that are not processing. Their making it into the Events table, but after that, nothing. What normally happens after they get to the events table to actually send out a report via email? Is there a trigger? some other scheduled job? We're currently checking logs and have not seen anything that stands out, but we're also not sure what this process is supposed to look like as it has always "just worked" Any help would be appreciated

I am a SQL developer so I know the basics of good query writing (ex try table variables or CTEs BEFORE using temp tables, avoid table hints when possible and only use them for specific debugging and/or troubleshooting events, use CASE statements instead of IF when possible., etc).

I am working on designing a new database and I want to make the rules for the new database clear for developers so they dont write bad queries. Any good tips or rules?

Are there really just the same license key being used for every SQL Server version?

For instance, my company, among other things, publishes some high end software and we are heavy SQL users. We buy from MS and then sell that and our software as a package to our customers.

I was on a site this week and doing some heavy SQL playing, updating old systems to new in a step-wise manner. This involved several incremental upgrades to SQL for very short periods of time.

I happened to notice that the one product key that I was given to use with my customer on 2022 std. was EXACTLY the same as a "free" ("non-commercial") product key found on the internet.

Is someone in my company screwing the pooch? Why would MS give out a key that is identical to all others of its ilk?

I am at a loss here. Is everything kosher but just looks strange or are there shenanigans at work?

In SSMS, is it possible to make images appear in each row in the results grid? I have a bunch of images saved to disk that are associated with rows in my table. I want to somehow save these images to my database and also be able to see them when I query the table. Is there maybe an addon to SSMS that can accomplish this?

Hi Everybody,

We are busy with a old Windows Server 2012 server for a customer. Their financial software package is stil needed because of dutch policies.
The software uses a named SQL instance on the server that can be connected from PC where the software is installed on.

We want to put the server in another network, so we can isolate is, and with policies on their firewall (Fortigate) we want to make SQL accesible.

I made the policies and tested working. From my laptop, which is in the primary network, i can connect to the secondary network (i can ping is and RDP into the server, which we made policies for and configured into the network).

I can also connect to the SQL server with SSMS on the IP given (192.168.30.x) and the credentials that are in the SQL server.

But when i want to make connection to the instance (with 192.168.30.x\INSTANCENAME) i cant connect to it.

And when i connect with it and use the default port (192.168.30.x/INSTANCENAM,1433) i can connect to it.

But the software cant handle a connection like this, it just wants to connect by IP and Instance Name.

Does anybody else have experience with this? I hope to hear from you!

Hello, im trying to export the Microsoft adventureworks database in a different format like .csv or .db. I have a Sql server running with the database on it, but when going to tasks > export and then into the import and export wizard, it just gives the following error whenever i try to change the Server name or Database (even though it already autoselected the correct ones):
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. (Microsoft OLE DB Provider for SQL Server)
Clicking next also causes this error. The server is running fine and i've given every single permission to myself, and i cant find too much about it online, only that firewall might have something to do with it? but even if turned off the error is still there. As data source i have the Microsoft OLE DB Provider for SQL Server selected.

Hi,

Hope someone can help. I have set transactional replication up on our ERP server. And a new reporting server to pull from the publication. I have also set two AD domain accounts and 2 SQL accounts up. All of these acounts are sysadmins. However I am getting these errors in the log reader.

https://preview.redd.it/191c8bkhcs0d1.png?width=944&format=png&auto=webp&s=4f8b626e7e13c5d2da48213b0649d53cd474d139

Hello friends, I have written a post about common SQL Server interview questions where I have shared both questions asked to me and the ones I found online, let me know how do you find it

https://www.java67.com/2019/08/microsoft-sql-server-phone-interview-questions-answers.html

Have you also seen these questions? Which ones are asked to you?

Cheers

Hey all

I really need some ideas to overcome a dilemma with a query optimization

I have a report using a stored procedure This stored procedure is calling some tables on the sql server with a join to some external tables that reads some mongo collections

Now the question here is how can I optimize it? Because right now this query is very heavy

So I have a SQL server that has about 100 databases on it of varying sizes.

And sometimes we need to restore a database to a testing server to try something out with the data.

To do this, we just run a copy-only backup with overwrite, and push the BAK file to an SMB share on another server.

The actual parameters being:

WITH FORMAT, COPY_ONLY, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, ENCRYPTION (ALGORITHM = <enc algorithm>, SERVER CERTIFICATE = <certificate>

9 times out of 10, this works just fine.

But once in a while, something happens and the BAK file can't be restored. It just throws an error terminated abnormally.

What's weird to me is that if I run another backup to that same file, it will give that same error when restoring. I can do it 15 times, and the backup will always succeed, but the restore will always fail, until I go and manually delete that bak file through windows.

Once I delete the file, it's just fine after that. It's like SQL isn't actually overwriting the file.

Need some advice. I have an old SQL server than has 1tb on the main drive and a few smaller drives for log files and such. That 1 TB is almost full and IT has recommended that the new server I deploy have 3 drives, each 1 TB. They then recommend copying the file directly over causing all of the existing data to remain on the first 1 TB drive. The issue is that it will cost 3 times as much as my current set up (because it's 3 times the space) and I assume the performance will still be bad because all of the reads will be coming off the single drive with the single large file.

I'd like to "spread the data" so to speak across the three new drives. My idea is to (over the weekend) turn off all of the jobs that run on the server so no data is changing. Then copy each table, one by one, into the new server using the tools provided by SSMS. As the data is copied from the old server to the new, the server should automatically balance the imported data across each of the three disks and their respective files.

Is it more complex than this or will my idea work? Is there a great risk of data corruption? Thanks for any suggestions.

I have 2 tables with a time column being stored as an integer, but they are being held in slightly different formats:

Table 1

Table 2

I have verified on the UI that the result I'm looking for is 19:45. I'm able to get this from table 2 by the following:

cast(CONVERT(TIME(0), DATEADD(SS,systime,0),114) as varchar)

But I have no idea what is being suffixed on the end of the time on table 1, and looking at historical data, the length of t1.SysCreateTime varies from 5 to 8 digits, and in some rare cases 3-4 digits, so it's not like I can always take the first 5 digits of that column to convert.

Does anyone recognise what it's doing, or how to get the correct time without joining on table 2?

A few more examples (both tables consolidated):

It just dont install, tried the express version, the 2019 version and nothing. I saw a post at microsoft about this exactly problem but there isnt any solution. I am considering install w10.

Log error:

Final result: Falha: consulte os detalhes abaixo

Exit code (Decimal): -2061893606

Start time: 2024-05-12 22:41:45

End time: 2024-05-12 22:47:39

Requested action: Install

Setup completed with required actions for features.

Troubleshooting information for those features:

Next step for SQLEngine: Use as seguintes informações para corrigir o erro, desinstale o recurso e execute o processo de instalação novamente.

Machine Properties:

Machine name: WIN-3IOPITF253H

Machine processor count: 8

OS version: Microsoft Windows 11 Home Single Language (10.0.22631)

OS service pack:

OS region: Brasil

OS language: português (Brasil)

OS architecture: x64

Process architecture: 64 Bits

OS clustered: Não

Product features discovered:

Product Instance Instance ID Feature Language Edition Version Clustered Configured

Package properties:

Description: Microsoft SQL Server 2022

ProductName: SQL Server 2022

Type: RTM

Version: 16

SPLevel: 0

Installation location: C:\SQL2022\Developer_PTB\x64\setup\

Installation edition: Developer

Product Update Status:

O usuário optou por não incluir atualizações do produto.

Aviso: Leia os Termos de Licença do Software Microsoft SQL Server em aka.ms/useterms.

Configurações de Entrada do Usuário:

ACTION: Install

ADDCURRENTUSERASSQLADMIN: false

AGTSVCACCOUNT: NT Service\SQLSERVERAGENT

AGTSVCPASSWORD: *****

AGTSVCSTARTUPTYPE: Manual

ASBACKUPDIR: Backup

ASCOLLATION: Latin1_General_CI_AS

ASCONFIGDIR: Config

ASDATADIR: Data

ASLOGDIR: Log

ASPROVIDERMSOLAP: 1

ASSERVERMODE: TABULAR

ASSVCACCOUNT: <vazio>

ASSVCPASSWORD: <vazio>

ASSVCSTARTUPTYPE: Automatic

ASSYSADMINACCOUNTS: <vazio>

ASTELSVCACCT: <vazio>

ASTELSVCPASSWORD: <vazio>

ASTELSVCSTARTUPTYPE: 0

ASTEMPDIR: Temp

AZUREARCPROXYSERVER: <vazio>

AZUREBILLEDEDITION:

AZUREREGION: <vazio>

AZURERESOURCEGROUP: <vazio>

AZURESERVICEPRINCIPAL: <vazio>

AZURESERVICEPRINCIPALSECRET: <vazio>

AZURESUBSCRIPTIONID: <vazio>

AZURETENANTID: <vazio>

BROWSERSVCSTARTUPTYPE: Disabled

CONFIGURATIONFILE: C:\Program Files\Microsoft SQL Server\160\Setup Bootstrap\Log\20240512_224145\ConfigurationFile.ini

ENABLERANU: false

ENU: false

EXTSVCACCOUNT: <vazio>

EXTSVCPASSWORD: <vazio>

FEATURES: SQLENGINE

FILESTREAMLEVEL: 0

FILESTREAMSHARENAME: <vazio>

FTSVCACCOUNT: <vazio>

FTSVCPASSWORD: <vazio>

HELP: false

IACCEPTSQLSERVERLICENSETERMS: true

IACKNOWLEDGEENTCALLIMITS: false

INDICATEPROGRESS: true

INSTALLSHAREDDIR: C:\Program Files\Microsoft SQL Server\

INSTALLSHAREDWOWDIR: C:\Program Files (x86)\Microsoft SQL Server\

INSTALLSQLDATADIR: <vazio>

INSTANCEDIR: C:\Program Files\Microsoft SQL Server

INSTANCEID: MSSQLSERVER

INSTANCENAME: MSSQLSERVER

ISMASTERSVCACCOUNT: NT AUTHORITY\Network Service

ISMASTERSVCPASSWORD: <vazio>

ISMASTERSVCPORT: 8391

ISMASTERSVCSSLCERTCN: <vazio>

ISMASTERSVCSTARTUPTYPE: Automatic

ISMASTERSVCTHUMBPRINT: <vazio>

ISSVCACCOUNT: NT AUTHORITY\Network Service

ISSVCPASSWORD: <vazio>

ISSVCSTARTUPTYPE: Automatic

ISTELSVCACCT: <vazio>

ISTELSVCPASSWORD: <vazio>

ISTELSVCSTARTUPTYPE: 0

ISWORKERSVCACCOUNT: NT AUTHORITY\Network Service

ISWORKERSVCCERT: <vazio>

ISWORKERSVCMASTER: <vazio>

ISWORKERSVCPASSWORD: <vazio>

ISWORKERSVCSTARTUPTYPE: Automatic

NPENABLED: 0

PBDMSSVCACCOUNT: <vazio>

PBDMSSVCPASSWORD: <vazio>

PBDMSSVCSTARTUPTYPE: 0

PBENGSVCACCOUNT: <vazio>

PBENGSVCPASSWORD: <vazio>

PBENGSVCSTARTUPTYPE: 0

PBPORTRANGE: <vazio>

PID: *****

PRODUCTCOVEREDBYSA: false

QUIET: true

QUIETSIMPLE: false

ROLE:

SAPWD: <vazio>

SECURITYMODE: <vazio>

SQLBACKUPDIR: <vazio>

SQLCOLLATION: Latin1_General_CI_AS

SQLMAXDOP: 8

SQLMAXMEMORY: 2147483647

SQLMINMEMORY: 0

SQLSVCACCOUNT: NT Service\MSSQLSERVER

SQLSVCINSTANTFILEINIT: true

SQLSVCPASSWORD: <vazio>

SQLSVCSTARTUPTYPE: Automatic

SQLSYSADMINACCOUNTS: WIN-3IOPITF253H\pedro

SQLTELSVCACCT: NT Service\SQLTELEMETRY

SQLTELSVCPASSWORD: <vazio>

SQLTELSVCSTARTUPTYPE: Automatic

SQLTEMPDBDIR: <vazio>

SQLTEMPDBFILECOUNT: 8

SQLTEMPDBFILEGROWTH: 64

SQLTEMPDBFILESIZE: 8

SQLTEMPDBLOGDIR: <vazio>

SQLTEMPDBLOGFILEGROWTH: 64

SQLTEMPDBLOGFILESIZE: 8

SQLUSERDBDIR: <vazio>

SQLUSERDBLOGDIR: <vazio>

SUPPRESSPAIDEDITIONNOTICE: false

SUPPRESSPRIVACYSTATEMENTNOTICE: false

TCPENABLED: 0

UIMODE: Normal

UpdateEnabled: false

UpdateSource: MU

USEMICROSOFTUPDATE: false

USESQLRECOMMENDEDMEMORYLIMITS: false

Configuration file: C:\Program Files\Microsoft SQL Server\160\Setup Bootstrap\Log\20240512_224145\ConfigurationFile.ini

Detailed results:

Feature: Serviços de Mecanismo de Banco de Dados

Status: Falha

Reason for failure: Erro durante o processo de instalação do recurso.

Next Step: Use as seguintes informações para corrigir o erro, desinstale o recurso e execute o processo de instalação novamente.

Component name: Recursos de Instância dos Serviços do Mecanismo de Banco de Dados do SQL Server

Component error code: 0x851A001A

Error description: Falha na espera do identificador de recuperação do Mecanismo de Banco de Dados. Verifique as causas prováveis no log de erros do SQL Server.

Error help link: https://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=16.0.1000.6&EvtType=0xD15B4EB2%400x4BDAF9BA%401306%4026&EvtType=0xD15B4EB2%400x4BDAF9BA%401306%4026

Feature: Browser do SQL

Status: Aprovado

Feature: Gravador do SQL

Status: Aprovado

Feature: Arquivos de Suporte à Instalação

Status: Aprovado

Rules with failures or warnings:

Global rules:

Aviso IsFirewallEnabled O Firewall do Windows está habilitado. Certifique-se de que as portas apropriadas estejam abertas para permitir o acesso remoto. Consulte a documentação sobre regras em https://go.microsoft.com/fwlink/?linkid=2162127 para obter informações sobre as portas que devem ser abertas para cada recurso.

Rules report file: C:\Program Files\Microsoft SQL Server\160\Setup Bootstrap\Log\20240512_224145\SystemConfigurationCheck_Report.htm

Hey guys, do anyone have documentation on connecting sentryone to grafana Or any idea how it can be done, no data source available for sentryone, can we use sql as a data source?

Microsoft SQL Server 2017 (RTM-GDR) (KB5029375) - 14.0.2052.1 (X64)
Web Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

I have a table I need to add a couple of columns to, but I think I may have already butted up against the row size limit. I run this query:

SELECT 
    SUM(max_length) AS TotalRowSizeBytes
FROM 
(
    SELECT 
        CASE 
            WHEN DATA_TYPE IN ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'smalldatetime', 'time', 'tinyint') THEN 8
            WHEN DATA_TYPE IN ('char', 'nchar') THEN CHARACTER_MAXIMUM_LENGTH * 2
            WHEN DATA_TYPE IN ('varchar', 'nvarchar') THEN (CHARACTER_MAXIMUM_LENGTH * 2) + 2
            WHEN DATA_TYPE IN ('decimal', 'numeric') THEN (NUMERIC_PRECISION / 2) + 2
            WHEN DATA_TYPE IN ('float') THEN 8
            WHEN DATA_TYPE IN ('real') THEN 4
            WHEN DATA_TYPE IN ('int') THEN 4
            WHEN DATA_TYPE IN ('smallint') THEN 2
            WHEN DATA_TYPE IN ('uniqueidentifier') THEN 16
            WHEN DATA_TYPE IN ('text', 'ntext') THEN 16
            ELSE 0 
        END AS max_length
    FROM 
		-- SELECT TOP 10 * FROM 
		INFORMATION_SCHEMA.COLUMNS
    WHERE 
        TABLE_NAME = 'tblfinance_dealworksheet'
) AS Sizes;

I get 29006. I'm not sure how the table works now, since I thought the limit was 8k.

In any event, is there an explanation for what I'm seeing and why I'm not getting a ton of errors? Aaaannnd if I can add another couple of numeric(10,2) columns to it?

EDIT: I didn't design the database initially. I'd have broken every block of related stuff into its own table and used views. *sigh* The system is fifteen years old and actually normalizing this stuff is nearly impossible.

Hi!

I am working on trying to resolve an issue with a replicated database. The database clones a police CAD server, utilizing filters, to send to a third party service.

The issue were having issues that it’s only sending a snapshot, even though it is set up as a transactional service. Essentially when the dispatcher generates a 911 call it appears in the replicated database, but if they update call notes or make any other changes it doesn’t seem to show up.

Furthermore, I am not at all a database person, and there is not a database person available. The system is running in Microsoft SQL.

Any help is greatly appreciated.

Basically title.

At the end of the import wizard it asks where you want it saved to, and when I've done it as a file, I can't seem to open it (DTSX file). When I save is in the server, I get an error when trying to save it,

TITLE: SQL Server Import and Export Wizard

The ExistsOnSQLServer method has encountered OLE DB error code 0x80004005 (Login failed for user 'USER'.). The SQL statement issued has failed.

So it seems like the file system is the way to go, but I'm stuck trying to execute the file.

Any help is appreciated!

Hi,

Solved, there is the solution in the answers.

I've read the document in the link. I've done a lot of things, but I still can't install. Turning off the firewall, allowing .exe files. Running commands, etc., to name a few. I may not understand exactly what I need to do.

• IsFirewallEnabled
• Checks whether the Windows Firewall is enabled.
• The Windows Firewall is enabled. Make sure the appropriate ports are open to enable remote access. See the rules documentation at https://go.microsoft.com/fwlink/?linkid=2162127 for information about ports to open for each feature.

Note: For now I'm just learning SQL, I'm setting it up to experiment, I don't think I need external access or anything.

https://preview.redd.it/uhsgj5mknmzc1.png?width=795&format=png&auto=webp&s=85b01c8bf9f0fce7fe71475e700d552832e16f10

https://preview.redd.it/hyoqagejnmzc1.png?width=844&format=png&auto=webp&s=4d44bd64853b20f9fc187df39b7317d60d665bbf

I can not get past an error when the SQL Server Update starts. Fails to connect to WMI. And if i try to repair the 2019 installation same thing happens. WmiServiceStateCheck - Failed.

There are no errors in error-log of event viewer and i for the heck of it can not find out what the problem is. Repaired .mof in the shared folder of my SQL Server a dozen times.

Checked if i can get into WMI with wbemtest can connect to ComputerManagement15 and do some querys. WMI is working fine and everything is correct.

But i can not get past pre installation checks of SQL Updates or Repair. Is there something i've missed?

Hi all,

I have three SQL Sever 2016 in an AG. Node 1 (Secondary) is Async Commit. Node 2 (Primary) and Node 3 (Secondary) is Sync Commit.

Configuration as follows:

https://preview.redd.it/vw21kik8pfzc1.png?width=751&format=png&auto=webp&s=6b0560de2fd64de01031f69d55aa2f9aa4b3332d

Read Only routing is configured so all nodes are poiting to Node 1. Use case is for reporting. We want the report servers or users to connect only to Node 1. If Node 1 is offline, connection must not be made, but we want the flexibility in case Node 1 has an extended outage.

In our test environment I tested this:

• When Node 1 is online, using applicationIntent=ReadOnly in the linked server or a direct connection from SSMS to the works correctly. All read-only is getting routed to Node 1
• When Node 1 is offline, doing a SELECT to the linked server or a direct connection using SSMS (with read-only routing configured) results in "Node only accepts Read/Write Connections" but if I insist a second time, it actually get data from Node 2, which does not make sense.

Is this the correct behavior?

Hi, I tried using the generate script function for my "Main" database and tried sending to a friend of mine to start linking it to our ui, but when we tried it it had lots of errors like Cannot drop the procedure 'dbo.DeleteAdminAccount', because it does not exist or you do not have permission. or when we try to use the usercreate procedure it doesn't allow it because the consumer role doesn't really have any permission but I can use it on mine, is there like a way to script it and when I send it to my friend it just automatically sets all those settings up and doesn't' give a huge block of error?

Is there a free tool that can change the color of the query's tabs based on the connected server?

I've already seen ApexSQL,, dbForge, Redgate sql Promps and SSMS Tools but none of them is free. They all look very very cool and add a lot of features other than tab coloring, but I'm just looking for that.

Edit: My colleague managed to join the NAS to the domain and it sorted everything. Thank you for the reply

Hi, I'm trying to back up to a folder on a NAS. I have given 'Everyone' full rights to the target folder but the SQL backup still fails with access denied.

I am not able to add the SQLService account specifically to this folders' permissions but I can't because the folder only allows me to add local NAS users.

So this may expand into more of a general windows question but I'd appreciate anyones thoughts please? Thanks

Looking at having 2 servers in different networks and locations, connected via a vpn, to use the SQL server replication.

Neither server are part of an Active Directory Domain, so my question is would this still work?

Edit: Solved! It was two small things I had to change. First, I had to add the domain to the username. Second, I had to turn off impersonation. Doing both allowed me to establish my connection again. I got the tip from DBA and I wanted to pass it along in case anyone runs into another issue like this.

Hello!

I am BID for a hospital using Epic and in desperate need of some help. Recently Epic upgraded one of its servers to SQL Server 2022. I have a server running Windows Server 2019 and SSRS 2019 (build: 15.0.1102.896). SSRS has been unable to connnect to Epic's DB ever since their upgrade and I am trying to get to the bottom of it. I already have a support ticket open, but it is still not resolved.

My build:

• SSRS build: 15.0.1102.896
• OS: Windows Server 2019
• I am connecting via OLE DB (version 18.7.2.0)
• .NET 4.8
• SQL Server 2019
• Here is the connection string I am using
  • Provider=SQLOLEDB;Data Source=<server>;Initial Catalog=<db>;trustServerCertificate=true;encrypt=true;column Encryption Setting=Enabled

What I have tried:

• Allowed RSWindowsExtendedProtectionLevel on SSRS config
• Added regkeys for SuppressExtendedProtection and LmCompatibilityLevel 
• upgraded endpoint protection to latest version

Context:

• IT verified no firewall blocks.
• It is only this application on the server that cannot make the connection. SSMS can connect perfectly fine.
• the error I am receiving is "Specified method is not supported."
• The error Epic sees when I attempt to connect is:
  • SSPI handshake failed with error code 0x80090346, state 46 while establishing a connection with integrated security; the connection has been closed. Reason: The Channel Bindings from this client are missing or do not match the established Transport Layer Security (TLS) Channel. The service might be under attack, or the data provider or client operating system might need to be upgraded to support Extended Protection*. Closing the connection. Client's supplied SSPI channel bindings were incorrect.*

Any ideas would be helpful. I am not very familiar with SSRS, but it has been handed to me to manage, so here we are.

Hello,

I've been asked to help set up a database failover solution. I haven't done any significant work in SQL Server for about 7 years, but prior to that, I had extensive experience with replication and clustering.

Microsoft seems to be recommending Always On for this, but that requires a Windows Failover Cluster. That requires at least one drive on shared storage, accessible by both SQL instances.

My initial thought was that database mirroring would be a good solution but mirroring has been deprecated. I could still go with mirroring but they will then not be able to upgrade to the next version of SQL Server in the future.

Does anyone have a recommendation for a good solution?

So, summary, there are two SQL Server instances each on their own physical server with local storage only. What would be the best alternative to database mirroring in SQL 2022?

Thanks.

I need help solving a problem that I don't understand in my T-SQL stored procedure.I have a stored procedure that has a try catch and transactions. But inside this SP I do an insert and have the execution of 3 other SPs which inside of each I have a try catch inside but no transactions. Example:

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

        INSERT INTO ...
        SELECT 1,2,...
         
        EXEC dbo.SP1
        EXEC dbo.SP2
        EXEC dbo.SP3 

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 > 0
            rollback transaction usp_my_procedure_name;

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

This main SP is called through Java and is called several times at the same time (kafka).

Problem: I'm getting the error: "The current transaction cannot be committed and cannot support operations that write to the log file"The objective is to have a streaming insertion into a staging table and after that call the SPs to normalize the data, in this case, data vault (in SQL Server).

I've tried everything and I can't overcome this error, if I remove the call of the 3 SPs from within the main SP it runs without errors, if I leave the call to just 1 SP it also runs but if I add another call to the SP 2 it no longer runs and it gives this error.