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 (первый текстбокс). Возможно через хранимую процедуру или по-другому.




<!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?

MSSQL database still hits the 10GB limit after enabling RBS

So i am new to the whole sharepoint/mssql thing, but i have managed to install sharepoint 2013 foundation, all is on one server (default installation) and i have RBS setup. Everything goes to the fileshare. For every document that i upload i see new files beeing created as it’s supposed to do.

However, after migrating a bunch of sites i noticed the database is also reaching 9.44GB??

I have tried to find an answer as to why this happens, but documents on the technet site are not ones i seem to grasp.

Is there anyone that can explain to me how this works, and possibly why the database keeps folling up?? I have followed a few RBS tutorials, they are all the same so i am pretty sure i set it up correctly 🙂


ms-sql on Ubuntu 18.04 configuration setup

I am trying to install ms-sql server on Ubuntu 18.04. Everything went fine until I ran

sudo /opt/mssql/bin/mssql-conf setup 

First I got

usermod: no changes Choose an edition of SQL Server:   1) Evaluation (free, no production use rights, 180-day limit)   2) Developer (free, no production use rights)   3) Express (free)   4) Web (PAID)   5) Standard (PAID)   6) Enterprise (PAID)   7) Enterprise Core (PAID)   8) I bought a license through a retail sales channel and have a product key to enter.  Details about editions can be found at https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409  Use of PAID editions of this software requires separate licensing through a Microsoft Volume Licensing program. By choosing a PAID edition, you are verifying that you have the appropriate number of licenses in place to install and run this software.  Enter your edition(1-8): 2 The license terms for this product can be found in /usr/share/doc/mssql-server or downloaded from: https://go.microsoft.com/fwlink/?LinkId=855862&clcid=0x409  The privacy statement can be viewed at: https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409  Enter the SQL Server system administrator password: 

I chose 2) Developer, set-up SA password (and confirmed it) after what I got following messages:

Configuring SQL Server...  This is an evaluation version.  There are [155] days left in the evaluation period. This program has encountered a fatal error and cannot continue running at  Sun Mar 24 18:42:36 2019 The following diagnostic information is available:         Reason: 0x00000007       Message: Cannot open or read the persistent registry: \SystemRoot\security.hiv.       Process: 5172 - sqlservr        Thread: 5176 (application thread 0x4)   Instance Id: c5829295-47bb-4e81-9889-5a20fd444087      Crash Id: c2863467-bd75-467c-b954-3ae7f2474296   Build stamp: c086a7e8c128fab0a270cb245183bad3f01b6afc6e38b5bc449a12e8fedc1cd1  Distribution: Ubuntu 18.04.2 LTS    Processors: 4  Total Memory: 3296792576 bytes     Timestamp: Sun Mar 24 18:42:36 2019  Ubuntu 18.04.2 LTS Capturing core dump and information to /var/opt/mssql/log... Hint: You are currently not seeing messages from other users and the system.       Users in groups 'adm', 'systemd-journal' can see all messages.       Pass -q to turn off this notice. No journal files were opened due to insufficient permissions. Hint: You are currently not seeing messages from other users and the system.       Users in groups 'adm', 'systemd-journal' can see all messages.       Pass -q to turn off this notice. No journal files were opened due to insufficient permissions. /usr/bin/tail: cannot open '/var/log/syslog' for reading: Permission denied Attempting to capture a dump with paldumper Captured a dump with paldumper Core dump and information are being compressed in the background. When complete, they can be found in the following location:   /var/opt/mssql/log/core.sqlservr.03_24_2019_18_42_37.5172.tbz2 Initial setup of Microsoft SQL Server failed. Please consult the ERRORLOG in /var/opt/mssql/log for more information. 

I can’t figure out where it went wrong.

Laravel 5.5 + MSSQL 2008. Ошибка с записью timestamps

добавляю данные в бд так:

$  log = new Carriers_log;   $  log->create([       'user_id' => $  user->id,       'log_title' => '1',       'log_body' => $  request->url()   ]); 


class Carriers_log extends Model {     protected $  table = 'carriers_log';//таблица модели        public $  primaryKey = 'id_log';         public $  timestamps = true;         protected $  dateFormat = "Y-m-d H:i:s.000";      protected $  fillable = ['user_id', 'log_title', 'log_body'];      public function User()     {         return $  this->hasOne('App\User', 'id', 'user_id');     } } 

Проблема в том, что при таком формате данных: Y-m-d H:i:s.000 показывает ошибку:

SQLSTATE[22007]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server] Преобразование типа данных nvarchar в тип данных  datetime привело к выходу значения за пределы диапазона. 

Но как только я меняю местами день и месяц в формате т.е. на Y-d-m H:i:s.000, то всё работает и в базе корректно отображается. Что может быть не так?

laravel 5.4 Пытаюсь получить create_at из бд mssql через eloquent, но получаю неправильную дату

пытаюсь получить таким образов данные по модели из базы данных:

$  logs = Carriers_log::on($  bd)->orderBy('id_log', 'desc')->get();  foreach ($  logs as $  log){ echo $  log->created_at; } 

Но поле “created_at” выводится не правильное:

В базе – 2019-03-14 15:41:33.000
Выводится – 2020-02-03 15:09:34

Не могу понять в чём дело, раньше выводилось правильно, в базу записывается всё правильно и если проверить массив…

dd($  log); 

То там будет правильное значение:

"created_at" => "2019-03-14 15:46:00.000" "updated_at" => "2019-03-14 15:46:00.000" 

Но как только пытаюсь уточнить поле даты, оно сразу меняется.

Ещё пробовал получить так значение:

$  log->value('created_at') 

Получаю такой ответ, год правильный, остальное – нет.

date: 2019-04-03 11:21:50.0 Asia/Yakutsk (+09:00) 

Есть идеи что могло случиться? На сервере время правильное.

Fix OGR2OGR “failed when converting the varchar value to data type int” issue in the MSSQL table –

I am a newbie to MSSQL and am trying to convert an MSSQL table to Mapinfo Tab file using OGR2OGR but it keeps failing with the above message on a few tables.

I have tried to get a reply in the GIS SE about switches to apply in OGR2OGR but have not got any options. https://gis.stackexchange.com/questions/314669/ogr2ogr-conversion-failed-when-converting-the-varchar-value-to-data-type-int

How can I change this in the table?enter image description here

What does NCPR mean -this text doesn’t exist in the table -it it code for something?

Can’t Install any softwares after failure of msSql Installation, Showing error

When i tried to install Mysql in my Ubuntu, Unfortuanley, its failed and a permenent error indicates in the top notification with a miniz symbhol, The error notification is

“An error occured, Please run Package Manager from the right-click menu or apt-get in a terminal to see what is wrong. The error message was:’Error: Opening the cache(E:Malformed line 1 in the source list/etc/apt/sources/list.d/mssql-server.list(type), E:The list of sources could not be read.)’. This usually means that your installed packages have unmet dependencies”

And after onwards, its unable to install any apps, Terminal showing the error below ;

“E: Malformed line 1 in source list /etc/apt/sources.list.d/mssql-server.list (type) E: The list of sources could not be read. E: Malformed line 1 in source list /etc/apt/sources.list.d/mssql-server.list (type) E: The list of sources could not be read.”

Kindly help, thanks in-advance