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? Или необходимо менять конструкцию запроса?

Extract data by day MsSQL server

I need to get all the values from the MsSQL server database by day (24 hours). I have timestamps column in TestAllData table and I want to select the data which only corresponds to a specific day. For instance, there are timestamps like '2019-03-19 12:26:03.002', '2019-03-19 17:31:09.024' and '2019-04-10 14:45:12.015' so I want to load the data for the day 2019-03-19 and separately for the day 2019-04-10

Is this possible to use some functions like DatePart or DateDiff for that? And how can I solve such problem overall?

As in this case, I do not know the exact difference in hours between a timestamp and the end of the day and I need to extract the day itself from the timestamp.

Using MSSQL ROW_NUMBER() in D7 dynamic query with addExpression()

I have a query with a bunch of joins, conditions, etc that uses ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) to group the results so that I can filter them further on the resulting row number. This works perfectly in T-SQL, but when I implement it with a Drupal 7 dynamic query, it’s no good. When I do the following, expecting the below expression to be included in my SELECT clause along with other columns and expressions:

$  query->addExpression('ROW_NUMBER() OVER(PARTITION BY X.id ORDER BY Y.whatever)', 'rowOrder'); 

Drupal changes it to THIS in the actual query, which I print to the screen using dpq():

CROSS APPLY (SELECT ROW_NUMBER() OVER(PARTITION BY X.id ORDER BY Y.whatever) cross_sqlsrv) cross_rowOrder 

which of course is totally useless to me, because SELECT ROW_NUMBER() by itself, without the FROM clause, always just returns 1!

Is there a way I can force Drupal to do what I want, or should I forget about using a dynamic query and just hardcode the entire thing with db_query()?!

Редактирование записей в MSSQL через веб-страницу

Очень нужна помощь, скоро уже сдавать, а я никак не могу разобраться. Есть веб-страница (код приведен ниже). Необходимо, чтобы при введении всех полей (текстбоксы) , они обновлялись по введенному ID (первый текстбокс). Возможно через хранимую процедуру или по-другому.

Процедура

USE [PIS] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[REDAKTIR] @ID [nvarchar](50), @PROBLEM [nvarchar](MAX), @STATUS [nvarchar](50), @RESHENIE[nvarchar](MAX), @COMMENT [nvarchar](MAX) AS BEGIN     SET NOCOUNT ON; UPDATE [dbo].[ZAYAVKA]  SET [PROBLEM]=@PROBLEM WHERE [ID]=@ID AND @PROBLEM IS NOT NULL  UPDATE [dbo].[ZAYAVKA]  SET [RESHENIE]=@RESHENIE WHERE [ID]=@ID AND @RESHENIE IS NOT NULL UPDATE [dbo].[ZAYAVKA]  SET [STATUS]=@STATUS WHERE [ID]=@ID AND @STATUS IS NOT NULL UPDATE [dbo].[ZAYAVKA]  SET [COMMENT]=@COMMENT WHERE [ID]=@ID AND @COMMENT IS NOT NULL END 

redakt.aspx

<!DOCTYPE html> <script runat="server">     Protected Sub Page_Load(sender As Object, e As EventArgs)      End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server">     <title>Редактирование</title>     <meta charset="UTF-8">     <meta name="viewport" content="width=device-width, initial-scale=1"> <!--===============================================================================================-->       <link rel="icon" type="image/png" href="images/icons/favicon.ico"/> <!--===============================================================================================-->     <link rel="stylesheet" type="text/css" href="vendor/bootstrap/css/bootstrap.min.css"> <!--===============================================================================================-->     <link rel="stylesheet" type="text/css" href="fonts/font-awesome-4.7.0/css/font-awesome.min.css"> <!--===============================================================================================-->     <link rel="stylesheet" type="text/css" href="fonts/iconic/css/material-design-iconic-font.min.css"> <!--===============================================================================================-->     <link rel="stylesheet" type="text/css" href="vendor/animate/animate.css"> <!--===============================================================================================-->       <link rel="stylesheet" type="text/css" href="vendor/css-hamburgers/hamburgers.min.css"> <!--===============================================================================================-->     <link rel="stylesheet" type="text/css" href="vendor/animsition/css/animsition.min.css"> <!--===============================================================================================-->     <link rel="stylesheet" type="text/css" href="vendor/select2/select2.min.css"> <!--===============================================================================================-->       <link rel="stylesheet" type="text/css" href="vendor/daterangepicker/daterangepicker.css"> <!--===============================================================================================-->     <link rel="stylesheet" type="text/css" href="css/util.css">     <link rel="stylesheet" type="text/css" href="css/main.css"> <!--===============================================================================================--> </head> <body>      <div class="limiter">         <div class="container-login100" style="background-image: url('images/bg-01.jpg');">                 <form id="form1" runat="server">           <asp:GridView ID="GridView1" runat="server" CellPadding ="4" EnableModelValidation="True" ForeColor="Black" GridLines="Vertical" BackColor="White" BorderColor="SkyBlue" BorderStyle="None" BorderWidth="1px" AutoGenerateColumns="False" DataKeyNames="номер заявки" DataSourceID="SqlDataSource1">             <AlternatingRowStyle BackColor="Pink" />             <Columns>                 <asp:BoundField DataField="номер заявки" HeaderText="номер заявки" ReadOnly="True" SortExpression="номер заявки" />                 <asp:BoundField DataField="фамилия" HeaderText="фамилия" SortExpression="фамилия" />                 <asp:BoundField DataField="имя" HeaderText="имя" SortExpression="имя" />                 <asp:BoundField DataField="отчество" HeaderText="отчество" SortExpression="отчество" />                 <asp:BoundField DataField="проблема" HeaderText="проблема" SortExpression="проблема" />                 <asp:BoundField DataField="статус" HeaderText="статус" SortExpression="статус" />                 <asp:BoundField DataField="решение" HeaderText="решение" SortExpression="решение" />                 <asp:BoundField DataField="комментарий" HeaderText="комментарий" SortExpression="комментарий" />             </Columns>             <FooterStyle BackColor="SkyBlue" />             <HeaderStyle BackColor="WhiteSmoke" Font-Bold="true" ForeColor="SteelBlue" />             <PagerStyle BackColor="SkyBlue" ForeColor="Black" HorizontalAlign="Right" />             <RowStyle BackColor="SkyBlue" />             <SelectedRowStyle BackColor="SkyBlue" Font-Bold="true" ForeColor="SkyBlue" />         </asp:GridView>                       <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$   ConnectionStrings:PISConnectionString1 %>" SelectCommand="ZAYAVK" SelectCommandType="StoredProcedure"></asp:SqlDataSource>                     <br />  <br />                        <asp:TextBox ID="TextBox1" runat="server" Width="95px" OnTextChanged="TextBox1_TextChanged"></asp:TextBox>                     <asp:TextBox ID="TextBox2" runat="server" Width="95px"></asp:TextBox>                     <asp:TextBox ID="TextBox3" runat="server" Width="95px"></asp:TextBox>                     <asp:TextBox ID="TextBox4" runat="server" Width="95px"></asp:TextBox>                     <asp:TextBox ID="TextBox5" runat="server" Width="95px"></asp:TextBox>                       <asp:Button ID="Button1" BackColor="SkyBlue" runat="server" Text="Обновить"  />                      <br />     </form>                          <form class="login100-form validate-form" action="operpage.aspx" method="POST">                         <div class="wrap-login100-form-btn">                             <div class="login100-form-bgbtn"></div>                             <button class="login100-form-btn">                                 Назад</button>                         </div>      </form>                                               </div>         </div>       <div id="dropDownSelect1">     </div>  <!--===============================================================================================-->     <script src="vendor/jquery/jquery-3.2.1.min.js"></script> <!--===============================================================================================-->     <script src="vendor/animsition/js/animsition.min.js"></script> <!--===============================================================================================-->     <script src="vendor/bootstrap/js/popper.js"></script>     <script src="vendor/bootstrap/js/bootstrap.min.js"></script> <!--===============================================================================================-->     <script src="vendor/select2/select2.min.js"></script> <!--===============================================================================================-->     <script src="vendor/daterangepicker/moment.min.js"></script>     <script src="vendor/daterangepicker/daterangepicker.js"></script> <!--===============================================================================================-->     <script src="vendor/countdowntime/countdowntime.js"></script> <!--===============================================================================================-->     <script src="js/main.js"></script>  </body> </html> 

MSSQL – Linked server returns message: “Query timeout expired.”

We have two separate sql servers. On one server we have a datawarehouse DWH, on the other we have sales information.

Now on the DWH server there is an ETL job that collects the information from the sales server. The job runs daily after midnight. The DWH collects the information via linked server from the sales database.

Now, the most time the ETL job runs without any problems. But sometimes it failes because of query timeout. We have found out, that there is a specific pattern: The failure happens every 11th day. So on the 11th day the ETL job fails to collect information.

We have searched everthing and could not found out what this issue causes. Also we know that the amout of data is everytime, almost the same. The next approach is to turn off the anti virus programm on the sales server.

Does anyone have a clue or any idea, where i can search for such a problem?