How does Optimistic concurrency interact with pessimistic concurrency in MSSQL

In MSSQL / Azure SQL, with Snapshots allowed and Read Committed Snapshot Isolation enabled, i have an application that does not use snapshot transactions explicitly (using the default RCSI everywhere). I have a delete statement that takes long time to run due to an suboptimal query plan that is selecting millions of rows to update about 3k rows in an indexed view.

When i try to run this delete with snapshot isolation in SSMS, queries start to pile up immediately as if i’ve placed table locks on all tables touched by the indexed view. From what i’ve read, snapshot transactions should not block other transactions but i couldn’t find any indication what happens if you mix DML snapshot and RCSI transactions.

Problem configuring MSSQL client and Kerberos authentication

I have a problem configuring an Ubuntu 18.04. client to connect to MSSQL Server using Windows Authentication. First of all, Kerberos/AD Login works fine using PAM and Winbind, connection using SQL Authentication works fine too with sqlcmd and php sqlsrv_connect.

But when I use sqlcmd -E -S vm-server\prod -d Database it stops with result:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SSPI Provider: Server not found in Kerberos database. 

I’ve added a DSN configuration to my /etc/odbc.ini file like:

[MSSQL] Driver = ODBC Driver 17 for SQL Server Server = vm-server\prod ServerSPN = MSSQLSvc/vm-client ServerSPN = MSSQLSvc/vm-client@DOMAIN.INTERNAL (as an alternative, with uppercase domain) User = Password = 

And start sqlcmd again with sqlcmd -D -E -S MSSQL -d Database. Now I get another error:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SSPI Provider: Message stream modified. 

Last try, using domain name:

[MSSQL] Driver = ODBC Driver 17 for SQL Server Server = vm-server\prod ServerSPN = MSSQLSvc/vm-client@domain.internal (Lowercase!) User = Password = 
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SSPI Provider: KDC reply did not match expectations. 

Any ideas how to fix this issue? It’s documented everywhere to use UPPERCASE domain name, but both types are not working.

As mentioned above, AD Auth using winbind works well. Apache2 Mod auth kerb works fine too.

Thx in advance!

Best regards

Is there any user friendly debugger for MSSQL Query/Stored Procedures?

I have some extremely complex stored procedures which are larger than 5000 of lines. These are handed over by the previous developer team and its very hard for me to debug in some cases as I do not have much more business logic/logical flow if the procedure in my hand. I wonder if there any tool/software exists that can generate the logical flow of my existing stored procedures or can help me understand the flow while debugging?

Restore or extract MSSQL bak files

During a penetration test I found multiple .bak-files for an MSSQL database. I want to restore them all so I can browse the server and extract hashes, etc. In a Windows VM I installed SQL Server 2017. For restoring the master database, I put the server in single-user mode and tried restoring the database as follows (also tried the same via SSMS):

RESTORE DATABASE master FROM DISK = 'C:\Foo\<path>\master.bak' WITH MOVE 'master' TO 'C:\Foo\master.mdf', MOVE 'master_log' TO 'C:\Foo\master_log.ldf', REPLACE GO 

This gave me an error like:

.. can’t be restored because it was created by a different version of the server (13.00.4435) than this server (14…).

I then downloaded MSSQL 2016 with service pack 1 by subscribing to “Visual Studio Dev Essentials” on my.visualstudio.com. Now I got the error:

.. can’t be restored because it was created by a different version of the server (13.00.4435) than this server (13.00.4001).

So now I needed to update. I downloaded MSSQL 2016 service pack 2, which gave me the error:

.. can’t be restored because it was created by a different version of the server (13.00.4435) than this server (13.00.5026).

So I really seem to require the exact patch. However, I can’t find a list of patches with corresponding server versions. Does anyone have an idea on how to find the right patch?

If I could just extract the .bak file to its .mdf- and .ldf files that would be enough. I found a bunch of tools for restoring MSSQL .bak files, but want they all want to connect to a database (to do a simple restore). I’d be very happy if I could either get the right SQL Server version or extract the .bak file.

¿Algún plugin o api para programar tareas y recibir notificación? PHP + MSSQL

Tengo un proyecto en php 5.6.40 y Microsoft SQL donde muestro todas las entradas que un usuario va creando, de estas entradas ya guardo la fecha y todo en orden, pero me gustaría añadirle la funcionalidad descrita en el título.

Básicamente me gustaría saber si hay algún plugin o api completo que me permita programar una notificación, es decir mediante un input fecha, tú puedas elegir una fecha y poner en otro input un comentario sobre qué hacer en esa fecha, entonces una vez haya llegado la fecha indicada, llegaría como mínimo esa notificación por pantalla en el panel de usuario. (Estos serían los requisitos mínimos del plugin/api, si es más completo, mejor)

He buscado en varias páginas y no he encontrado ninguna útil, las que he creído útiles son de pago y otras tantas ofrecen el uso de crontab, pero yo no necesito automatizar ningún script.

También he visto que la api de google calendar es bastante completa, pero necesitas logearte para poder programar esas tareas.

Node.js mssql return query result to ajax

I’m new to learning Node.js, so I’m still getting used to asynchronous programming and callbacks. I’m trying to insert a record into a MS SQL Server database and return the new row’s ID to my view.

The mssql query is working correctly when printed to console.log. My problem is not knowing how to properly return the data.

Here is my mssql query – in addJob.js:

var config = require('../../db/config');  async function addJob(title) {      var sql = require('mssql');     const pool = new sql.ConnectionPool(config);     var conn = pool;      let sqlResult = '';     let jobID = '';     conn.connect().then(function () {         var req = new sql.Request(conn);          req.query(`INSERT INTO Jobs (Title, ActiveJD) VALUES ('$  Node.js mssql return query result to ajax', 0) ; SELECT @@IDENTITY AS JobID`).then(function (result) {              jobID = result['recordset'][0]['JobID'];              conn.close();              //This prints the correct value             console.log('jobID: ' + jobID);          }).catch(function (err) {             console.log('Unable to add job: ' + err);             conn.close();         });      }).catch(function (err) {         console.log('Unable to connect to SQL: ' + err);     });      // This prints a blank    console.log('jobID second test: ' + jobID)    return jobID; }  module.exports = addJob; 

This is my front end where a modal box is taking in a string and passing it to the above query. I want it to then receive the query’s returned value and redirect to another page.

// ADD NEW JOB             $  ("#navButton_new").on(ace.click_event, function() {                 bootbox.prompt("New Job Title", function(result) {                     if (result != null) {                          var job = {};                         job.title = result;                          $  .ajax({                             type: 'POST',                             data: JSON.stringify(job),                             contentType: 'application/json',                             url: 'jds/addJob',                                                   success: function(data) {                                  // this just prints that data is an object. Is that because I'm returning a promise? How would I unpack that here?                                 console.log('in success:' + data);                                  // I want to use the returned value here for a page redirect                                 //window.location.href = "jds/edit/?jobID=" + data;                                 return false;                             },                             error: function(err){                                 console.log('Unable to add job: ' + err);                             }                         });                     } else {                      }                 });             }); 

And finally here is the express router code calling the function:

const express = require('express'); //.... const app = express(); //....  app.post('/jds/addJob', function(req, res){      let dataJSON = JSON.stringify(req.body)     let parsedData = JSON.parse(dataJSON);      const addJob = require("../models/jds/addJob");     let statusResult = addJob(parsedData.title);      statusResult.then(result => {         res.send(req.body);     });   }); 

I’ve been reading up on promises and trying to figure out what needs to change here, but I’m having no luck. Can anyone provide any tips?

MSSQL запрос в Django ORM

Есть запрос:

declare @cnt_day int = 2  select top (1) with ties [t1].[rid]                        , [t1].[name_obj]                        , [t1].[date_time] from [table1] as [t1] where isnull([t1].[tyev], 0) = 0       and convert(date, [t1].[date_time]) >= (convert(date, getdate() - @cnt_day)) order by row_number() over(partition by [t1].[name_obj]          order by [t1].[date_time] desc) 

Как его можно записать для ORM Django? Или необходимо менять конструкцию запроса?