texts: Search within same ID across several rows

texts:
My table has the following structure:

+----+---------+------------+-------------+
| id | word_id | meaning_id | sentence_id |
+----+---------+------------+-------------+
| 1  | 1       | 15333      | 1           |
| 2  | 1       | 12355      | 1           |
| 3  | 1       | 768        | 1           |
| 4  | 2       | 12455      | 1           |
| 5  | 2       | 9581       | 1           |
| 6  | 3       | 41         | 1           |
| 7  | 4       | 125        | 1           |
| 8  | 1       | 17854      | 2           |
| 9  | 2       | 35214      | 2           |
| 10 | 3       | 12455      | 2           |
| 11 | 3       | 988        | 2           |
+----+---------+------------+-------------+

I have a list of meaning_ids and I want to get all the sentence_ids that contain all of the meaning_ids. I do not care what the sentence it is, as long as the sentence contains all meaning_ids (= sentence_id is the same).

Lets say I have the following sentence_ids: 12455 and 15333. The query should return sentence_id 1 because both only occur there.

How can I achieve this with MySQL?


texts: Options for setting NOLOCK hint in dataset queries

texts:
Some context:
At first we wrote reports just "straight up", without any locking hints in the queries. With the larger reports this would sometimes cause locking problems. At first we remedied this by using the WITH (NOLOCK) hint for tables in the query.

Because (a) it's quite obtrusive, and (b) it's easy to forget the hint for one of the tables, we moved to a second approach setting TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED (which is fine) at the top of each dataset's query.

As you may guess, it's still easy to forget the hint for one of the datasets. So this leads to the question:

Question: What are the options for sending NOLOCK hints along with report queries?

PS. I realize this is to some extent an XY-problem (with a lot of my other options for X, such as optimizing the query, not doing reporting on the operational database, etc), but tried to make this a valid question on itself nonetheless.

Options:
Here are the options mentioned above, with added options about which I'm curious if they would work:

Set WITH (NOLOCK) hint for each table. (obtrusive, very easy to forget)
Set isolation level to READ UNCOMMITTED for the entire query. (still easy to forget)
Is it possible to specify this at the report level? E.g. make sure all dataset queries for one report will be run without locking.
Is it possible to specify this at some other SSRS level? E.g. perhaps set this for a certain Report Folder, or by utilizing an extension?
Is it possible to specify this at the data source / connection string level? E.g. have all relevant reports use a certain "No-lock-data-source"?
Related to the previous option: perhaps it's possible to specify a default locking hint for a specific "no-lock-sql-user" (the one that's used in the connection)?
???

Which options are viable? Are there options I've missed?


texts:
Take the following code:

Declare @a integer
set @a = 0
set @a = @a | cast (1 as BINARy)
set @a = @a | cast (100 as BINARy)
select cast (@a as integer)

Now, shouldn't the output of this code be 5 ?

I have an integer, set it to 0, so in binary its all 0's

then I OR it with 1 , so it becomes 0..001

then I OR it with 100 in binary, so it should become 00..0101 which, in decimal is 5

The output I actually get is 101

EDIT: for more clarity, I'm trying to accomplish with TSQL what the following code does in C++

#include<stdio.h>
#include<iostream>
using namespace std;
int main()
{
int a;
a = 0;
a = a | 1;
cout<< a;
a = a | 0b100;
cout<<endl<<a;
}


texts: How to trace security_error_ring_buffer_recorded errors in SQL Server 2014 Express

texts:
Just so everyone doesn't have a high expectation of my level of knowledge on this topic, I'm not a DBA; I'm a .NET developer who has to try and figure this out by virtue of being "the one most likely to be able to figure it out".

We're running SQL Server 2014 Express in Mixed Mode for an e-commerce website, and I've noticed several thousand security_error_ring_buffer_recorded errors in the space of 7 days.

There aren't any corresponding errors that I can see in any SQL
Server or windows logs.
There are no logs which correlate the session_id and SPIDs.
There's no information on any particular entry    other than the
details listed below (and other than the timestamp,    session_id,
and call stack, they're all identical).
There are no 139F or 5023 error codes in any logs as well. No
State 11 errors, either.
I don't see either an SQL Server user or a Windows user associated with
these entries.
The SQL Server itself is behind a Windows firewall and only a
small number of IPs have access to it (approx. 20, and I can account for all of them). The RDP to the server is restricted to the same IPs.

So my questions are these:

Is this something I should be concerned with (I tend to be just given the volume of entries)?
How else can I debug these errors to figure out where the underlying problem is?

Thanks.


texts: Oracle 11.1.0 Autobackup going to wrong location

texts:
I am running Oracle 11.1.0 on Win Server 2008. My autobackups used to go to flash_recovery_area\myDB\autobackup\*yyyy_mm_dd*. Then I added a prefix to the file name in front of %F and they started going to C:\ORACLE\PRODUCT\11.1.0\DB_1\DATABASE\ so I removed the prefix but they still don't go to the flash recovery area. I realize I could specify the directory but I would prefer if it went back to putting each backup in its own folder with the date. I didn't make any other changes, any ideas what I am doing wrong? My RMAN config output is:

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name SPATIAL are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 31 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\PRODUCT\11.1.0\DB_1\DATABASE\SNCFSPATIAL.ORA'; # default


texts: Do I have to retype information every time I make a mistake in SQLPlus

texts: If I make a mistake in SQLPlus while inputting an 'insert into' command, is there a way I can bring this information back up in order to edit it?


texts: Should I store user_id in all tables to ease Row Level Policy checks

texts:
In a Postgres database serving classical multiuser web application, I’ve got multiple tables, including the user table.

user_id integer is what row level security policy is based on in the application.

Is it a good idea to denormalize user_id and put it to all tables, add foreign keys, and let policies use the column?

Is it practical in terms of getting policy checks work faster at the cost of increased memory consumed by indexes and te data itself?

I’m expecting tens of millions rows in every table (well, except the users table). I’m concerned about policy checks execution speed.

Kind of data stored, if that helps:

10k-500k records per user in the tables.
Data both added (90%) and deleted (10%).
Data added regularly on a daily basis.
Plenty of complex aggregated, windowed and hierarchical queries.
Data represents graphs, networks, and plenty of metadata attached to entities.
approximately 30 tables


texts:
I have a relation:

R4 = {{T,U,V}, {T → U, U → T, T → V}}

I know from looking at the answer key that this relation is in BCNF.

I'm going through the process of rigorously determining what normal form the relationship adheres to.  It's clear to me why the relationship is in 1NF and 2NF, and if I assume it's in 3NF, BCNF follows easily.

However, the definition of 3NF states:

  Every non-prime attribute is non-transitively dependent on every candidate key in the table.

But, as far as I can tell, both {T} and {U} are candidate keys of the table, and {V} is thus transitively dependent on {U}.

There is an alternate definition of 3NF available on wikipedia:

  A 3NF definition that is equivalent to Codd's, but expressed differently, was given by Carlo Zaniolo in 1982. This definition states that a table is in 3NF if and only if, for each of its functional dependencies X → A, at least one of the following conditions holds:

  X contains A (that is, X → A is trivial functional dependency)
  X is a superkey
  Every element of A-X, the set difference between A and X, is a prime attribute (i.e., each column in A-X is contained in some candidate key)

And the relation is clearly in 3NF by this definition (all of the functional dependencies are covered by "X is a superkey").

So why the discrepancy?  How am I misapplying the definition?  Please don't give me shortcuts that give me the answer in a way I don't want, unless you also help me understand why my application of 3NF (as described) is inaccurate.


texts: how to search in language and get results for another language

texts:
I have two tables

product

|----|---------------------|
| id |        code         |
|----|---------------------|
|  1 |          A          |
|  2 |          B          |

and

product_tarnslation
|---|------|---------------------|
|pid| name |        locale       |
|---|------|---------------------|
| 1 |prod A|          en         |
| 2 |prod B|          en         |
| 1 | منتج |          ar         |
| 2 |prod B|          ar         |

I need a query to search in any locale but get result for an only desired locale.
eg:
when I search by prod and I want Arabic translation

expected result
|---|------|---------------------|
|pid| name |        locale       |
|---|------|---------------------|
| 1 | منتج |          ar         |
| 2 |prod B|          ar         |

UPDATE
I solved it by self join, but it resulted in a slow query.

SELECT t1.name as t1name, t2.name as t2name, t1.locale as t1locale, t2.locale as t2locale
FROM product_translation t1
INNER JOIN product_translation t2
on t1.translatable_id = t2.translatable_id
WHERE t1.name LIKE '%prod%'
AND t2.locale = 'ar'


texts:
When someone tries to connect to a SQL Server instance, the error shows up:

  It's not possible do generate SSPI context.

Yesterday we had a blackout (don't know how to say this expression in English) and I had to shut down our servers.

Looking for answers, I found this:

SQL Server 2008 connectivity issue : cannot generate SSPI context

But it doesn't help me, because they're working fine until yesterday. I don't want to change anything. But if a have to, I will change it.

Obs: I can't restart the server now.

Edit: Since I my answer, we haven't had any errors.


texts: MongoDB - Don't know what to do with file

texts:
Windows 10 and MongoDB 3.2

I have 2 files. One with .ns extension and one with. They are both in c:\data\dump

I am trying to run

mongorestore -d gnome c:\data\dump

However all I get is

2015-12-11T23:14:12.860+0000    building a list of collections to restore from c:\data\dump dir
2015-12-11T23:14:12.862+0000    don't know what to do with file "c:\data\dump\gnome.0", skipping...
2015-12-11T23:14:12.862+0000    don't know what to do with file "c:\data\dump\gnome.ns", skipping...
2015-12-11T23:14:12.863+0000    done

Any advice?


texts: SSIS Validation slow. OLTP OLAP running on same server

texts:
I have four identical servers 2 are used in production and the other two are demo machines/emergency fail over boxes. The more powerful machine a 16 core 127 gigs of Ram hosts both our Datawarehouse and our OLTP database. The less powerful box is an 8 core 98 gigs of ram box that we use for ETL and our datastore. The problem I am having is on the production servers integration services takes a large amount of time to perform package validation. For a query that returns in 6 seconds it takes 40 seconds to complete package validation. As you can see it's a fairly simple package.

Keep in mind this is just the package I'm using as an example all of my packages have the same problem although some to a lesser degree. When I run this the validation portion takes 40 seconds on average.

In order to better test this I ran the exact same package on the sister box which runs all of the same SQL jobs as this box. Only to have the pre-execution validation take 0 seconds and the query take between 4 and 6 seconds.

To further test this I ran the query against both instances of SQL server.

SELECT '{' + CAST(Store_Id AS CHAR(36)) + '}' AS Store_Id
      ,'{' + CAST(ItemGroupDetail_Id AS CHAR(36)) + '}' AS ItemGroupDetail_Id
      ,ItemGroupType_Id
      ,'{' + CAST(Concept_Id AS CHAR(36)) + '}' AS Concept_Id
      ,'{' + CAST(Company_Id AS CHAR(36)) + '}' AS Company_Id
      ,'{' + CAST(ItemGroup_Id AS CHAR(36)) + '}' AS ItemGroup_Id
      ,GroupName
      ,Sort
      ,NameOverride
      ,'{' + CAST(ParentGroup_Id AS CHAR(36)) + '}' AS ParentGroup_Id
      ,Active
      ,'{' + CAST(UserCreated AS CHAR(36)) + '}'  AS UserCreated
      ,'{' + CAST(UserModified AS CHAR(36)) + '}'  AS UserModified
  FROM bos_vAllItemGroups

Execution on the main server with live up to date date returns 63326 rows in 5 seconds on average and the 50527 rows in 3 seconds. Which is to be expected this server has much less activity with the same specs I would expect it to be slightly faster. What I wouldn't expect is for package validation time to increase so much more than actual query run time.

After much digging yesterday I think I may have discovered the problem but am not sure how to prove it. The production OLTP database manages content for digital menu boards across the country that are calling home constantly to check for new content. Most of the time there is nothing to change and the queries to find out finish in milliseconds. However they do send about this process ends up running on average 4500 queries per second. Could this be the cause of my problem? The large volume of transactions coming from our web-server. If so how do I prove it? I have already been trying to get these databases running on separate machines as this OLTP database is growing very quickly as is our datawarehouse and from all my reading keeping your OLAP and OLTP databases on the same server is considered bad practice.


texts: DVWA SQLi: How to get column names from table “users” only?

texts:
You can find the original question here. However it has been closed almost immediately by schroeder with the following justification.

  This is a pure SQL question, not a security question. You are
  literally asking for all column names in the database ...

I hope this is the right channel then.

This is DVWA database. There are only 2 tables in it.

mysql> show tables;
+----------------+
| Tables_in_dvwa |
+----------------+
| guestbook      |
| users          |
+----------------+
2 rows in set (0.00 sec)

mysql>

I've no issue getting these 2 tables via SQL injection bug

http://127.0.0.1/dvwa/vulnerabilities/sqli/?id=' UNION SELECT GROUP_CONCAT(table_name),2 FROM information_schema.tables WHERE table_schema=DATABASE() -- -&Submit=Submit#

Output in web (TABLE_NAME in "dvwa" DATABASE)

First name: guestbook,users

Table "users" looks interesting and would like to know all columns in it.

There are 6 columns as shown in MySQL query below.

mysql> SELECT * FROM users;
+---------+------------+-----------+---------+----------------------------------+-------------------------------------------------------+
| user_id | first_name | last_name | user    | password                         | avatar                                                |
+---------+------------+-----------+---------+----------------------------------+-------------------------------------------------------+
|       1 | admin      | admin     | admin   | 5f4dcc3b5aa765d61d8327deb882cf99 | http://172.16.123.129/dvwa/hackable/users/admin.jpg   |
|       2 | Gordon     | Brown     | gordonb | e99a18c428cb38d5f260853678922e03 | http://172.16.123.129/dvwa/hackable/users/gordonb.jpg |
|       3 | Hack       | Me        | 1337    | 8d3533d75ae2c3966d7e0d4fcc69216b | http://172.16.123.129/dvwa/hackable/users/1337.jpg    |
|       4 | Pablo      | Picasso   | pablo   | 0d107d09f5bbe40cade3de5c71e9e9b7 | http://172.16.123.129/dvwa/hackable/users/pablo.jpg   |
|       5 | Bob        | Smith     | smithy  | 5f4dcc3b5aa765d61d8327deb882cf99 | http://172.16.123.129/dvwa/hackable/users/smithy.jpg  |
+---------+------------+-----------+---------+----------------------------------+-------------------------------------------------------+
5 rows in set (0.00 sec)

mysql>

However, my next attempt to get only columns from table users didn't work well.

http://127.0.0.1/dvwa/vulnerabilities/sqli/?id=' UNION SELECT GROUP_CONCAT(column_name),2 FROM information_schema.columns WHERE table_schema=DATABASE() -- -&Submit=Submit#

Output in web

First name: comment_id,comment,name,user_id,first_name,last_name,user,password,avatar

The problem is columns comment_id,comment,name are not part of users table.

What's wrong in this SQLi syntax and how do I get only column names from table users only.

DESIRED OUTPUT

First name: user_id,first_name,last_name,user,password,avatar


texts: How do I limit a SQL stored procedure to be run by one person at a time?

texts:
I have a stored procedure that basically selects values from one table and inserts them to another, a sort of archiving. I want to avoid multiple people from doing that at the same time.

While this procedure is running, I don't want anyone else to be able to start it, however I don't want serialization, the other person to run the procedure after I am done with it.

What I want is for the other person trying to start it to get an error, while I am running the procedure.

I've tried with using sp_getapplock, however I can't manage to completely stop the person from running the procedure.

I also tried finding the procedure with sys.dm_exec_requests and blocking the procedure, while this does work, i think it's not optimal because on some servers I don't have the permissions to run sys.dm_exec_sql_text(sql_handle).

What is the best way for me to do this?


texts: Under load, the following job-queue design does not work reliably, why?

texts:
I have a job queue table which is meant to mark-and-return one job at a time to the caller. The stored procedure works like this:

update job_queue
set status = "WORKING", job_id = <arg_unique_job_id>
where status = "NEW"
limit 1
;

if row_count() > 0 then

    select
        -- get job details for work
    from job_queue
    where job_id = <arg_unique_job_id>
    ;

else

    -- nothing to do
    select 1 from dual where false;

end if;

The client generates a unique number (arg_unique_job_id) and passes it to the stored procedure. If there is any work to be done, that work will be marked as "WORKING" and get assigned the caller's job ID.

On occasion, under heavy DB load, I would find a job marked as WORKING but the client did not appear to get any row back, as if row_count "lied". Above statements are executed in one transaction, against InnoDB table (MySQL 5.6.39). Am I wrong in my assumption how above should work, or the error is likely elsewhere?


texts:
I have a SQL Server 2008 R2 cluster on Win2008 R2.

I am unable to add a login for the service account.

Any other login I add to this instance works fine and I have been able to add the same service account login to numerous other SQL instances.

Moving the instance to other node does not resolve the issue.

when I run

 CREATE LOGIN [DOMAIN\ServiceAccount] FROM windows;

I get the error

Msg 15401, Level 16, State 1, Line 1
Windows NT user or group 'DOMAIN\ServiceAccount' not found.
    Check the name again.

When I run

exec xp_logininfo 'DOMAIN\ServiceAccount', 'all';

I get the error

Msg 15404, Level 16, State 11, Procedure xp_logininfo, Line 62
Could not obtain information about Windows NT group/user
    'DOMAIN\ServiceAccount', error code 0xffff0002.

Any suggestions would be appreciated.


texts: Is there a way to make a null check on a variable in a WHERE clause only occur once?

texts:
I have a query on a large table that looks like this:

declare @myIdParam int = 1

select *
from myTable
where (@myIdParam is null or myTable.Id = @myIdParam)

There are several similar conditionals like this in the where clause, and there are also a lot of joins, but this is a summary.

Effectively, if @myIdParam is null, we do not want to restrict the results using this parameter.

I am not a DB pro, but from my tests it seems like this NULL check is done for every record and not optimized out in any way.

If I remove the null check and assume the parameter is not null, the query returns instantly.  Otherwise, it takes up to ten seconds.

Is there a way to optimize this so the check is done only once at runtime?


texts: How can I represent a relationship of cardinality many to many?

texts:
I'm trying to design a database for the information shown in the table
below:

In this regard, let's make the following assumptions:

Each Student may take many Courses
Each Course may have many Students
Student Numbers, Course Numbers and Advisor Names are unique
Student Names and Course Descriptions are not unique
Each Student has one Advisor
Each Advisor may advise many Students
Each Advisor has one Office
A Student can visit with his Advisor many times

My current approach

So far, I have come up with the following diagramatic representation:

The question

Being that this contains a many-to-many (M:N) relationship, I am confused as to what the association class (or associative entity type) should be.

What would the correct way to represent this relationship be?


texts:
I am using Idera SQL Diagnostic Manager to monitor a Windows Server Failover Cluster with two SQL Server 2008 R2 clusters configured to run with one SQL Server cluster on each node. It just started reporting that one of the instances does not have DTC or SQL Server Agent installed.

I've RDP'd to the cluster, and both nodes. In the cluster manager I see the DTC node running and is owned by the "A" node. On the "A" node, there are two services called "Distributed Transaction Coordinator." On the "B" node (which is the node on which the instance throwing the error on SQL Diagnostic Manager) there is only one service (in services.msc) called "Distributed Transaction Coordinator."  There is an error in the event log that says:

  Failed trying to get the state of the cluster node: SERVER-DTC.The
  error code returned: 0x80070005

This cluster node is on the "A" node along with a SQL Server instance. The "B" node has this error. I can list and enumerate jobs in SSMS in the instance that is on the "B" node, but the SQL Server Agent does not have the little green triangle in it (just the empty circle).

So, I don't know exactly what to do. It seems like things are working OK, but this message from Idera SQL Diagnostic Manager has me perplexed since it is new (the error).

Running:

EXEC master..xp_servicecontrol N'QUERYSTATE', 'MSDTC'

on both nodes results in "Running".


texts:
After creating a CouchBase dump and copying it to a Google Storage bucket I tried importing the data to BigQuery.

I failed miserably, apparently the design.json or the files that appear in the same directory as data-0000.cbb are not working when I'm trying the json import.

Do I need to convert the dump somehow?
Is there any other way to migrate the data from CB to BQ?

Thank you all in advance.


texts: Simulate effective LEAD(1) or LEAD(2) in MySQL

texts:
Assume i have a table with the dutch or english alfabet.

Alfabet

digit
--------
a
b
c
d
e
f
g
h
i
j
k
l
m
n
o
p
q
r
s
t
x
y
z

Now i want to display a list like this.

digit   next_digit  next_two_digit
------  ----------  ----------------
a       b           c
b       c           d
c       d           e
d       e           f
e       f           g
f       g           h
g       h           i
h       i           j
i       j           k
j       k           l
k       l           m
l       m           n
m       n           o
n       o           p
o       p           q
p       q           r
q       r           s
r       s           t
s       t           x
t       x           y
x       y           z
y       z           (NULL)
z       (NULL)      (NULL)

In Oracle (and more databases that support window functions) you can get this list with

SELECT
   "digit"
 , LEAD("digit", 1) OVER (ORDER BY "digit" ASC) as next_digit
 , LEAD("digit", 2) OVER (ORDER BY "digit" ASC) as next_two_digits
FROM
 alfabet
ORDER BY
 "digit" asc

demo http://www.sqlfiddle.com/#!4/a11f1/4

Now i want to simulate the window function lead(1) and lead(2) in MySQL.

Now in MySQL i know i can get the same with co-related subqueries like so.

SELECT
 *
 , (SELECT
     *
    FROM
     alfabet alfabetInner
    WHERE
      alfabetInner.digit > alfabet.digit
    ORDER BY
      digit ASC
    LIMIT 0, 1
   ) AS next_digit

 , (SELECT
     *
    FROM
     alfabet alfabetInner
    WHERE
      alfabetInner.digit > alfabet.digit
    ORDER BY
      digit ASC
    LIMIT 1, 1
   ) AS next_two_digit
FROM
 alfabet
ORDER BY
 alfabet.digit ASC

Editted because off comment from Evan Carroll

  why doesn't the table Alfabet just store the id/row number on it?

Because i wanted to simulate a pure LEAD(1) or LEAD(2) solution without having to need a auto increment sequence without gaps as PRIMARY ID... the window function off Oracle doesnt do annything with a auto increment PRIMARY key also.

demo http://www.sqlfiddle.com/#!9/6a8701/2

  Now the question. Do you know even more (effective) LEAD simulation
  methodes?


texts: Issues installing MySQL server on Ubuntu 13.04

texts:
I'm currently trying to install a MySQL server on my Ubuntu 13.04 machine. The problem is, when I try to install it, I get error messages indicating that not all packages could be downloaded. When I run sudo apt-get install mysql-server, and after hanging for a while at reading headers, the console reads as follows:

Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
  libaio1 libdbd-mysql-perl libdbi-perl libhtml-template-perl libmysqlclient18 libnet-daemon-perl libplrpc-perl mysql-client-5.5 mysql-common mysql-server-5.5 mysql-server-core-5.5
Suggested packages:
  libipc-sharedcache-perl tinyca mailx
The following NEW packages will be installed:
  libaio1 libdbd-mysql-perl libdbi-perl libhtml-template-perl libmysqlclient18 libnet-daemon-perl libplrpc-perl mysql-client-5.5 mysql-common mysql-server mysql-server-5.5 mysql-server-core-5.5
0 upgraded, 12 newly installed, 0 to remove and 3 not upgraded.
Need to get 8,077 kB/24.5 MB of archives.
After this operation, 84.9 MB of additional disk space will be used.
Do you want to continue [Y/n]? y
Err http://us.archive.ubuntu.com/ubuntu/ raring-updates/main mysql-client-5.5 i386 5.5.32-0ubuntu0.13.04.1
  Connection failed [IP: 91.189.91.13 80]
Err http://security.ubuntu.com/ubuntu/ raring-security/main mysql-client-5.5 i386 5.5.32-0ubuntu0.13.04.1
  Connection failed [IP: 91.189.92.190 80]
Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/m/mysql-5.5/mysql-client-5.5_5.5.32-0ubuntu0.13.04.1_i386.deb  Connection failed [IP: 91.189.92.190 80]
E: Unable to fetch some archives, maybe run apt-get update or try with --fix-missing?

I've tried the suggested fix or running apt-get update and such, I've made sure I have no connections blocked, I've restarted, uninstalled, reinstalled, etc, all to no avail. I've been searching the web all day for solutions I haven't yet tried, but most people with this issue are running an older version of Ubuntu. Suggestions?


texts: What is the right way to increment like/comment counters on CosmosDB?

texts:
I'm currently working on a new app which will use a NOSQL DB (particularly Cosmos DB) for data scalability. I need to implement a way to update counters every time a post receives a like or comment. As of now, I could think of two approaches to this problem:

Write a JS stored procedure to check document ETAG and increment counters only if the ETAG is current. If not, run a loop and fetch the most recent version of the document before updating it. The document holding counters as well as like and comment records would belong to the same partition key.
The counters should be eventually consistent. A WebJob or Function is triggered to update them after the like or comment record is written to the database.

In each scenario, Cosmos DB would run on Session consistency level.

Assume that a document is so popular that it begins to receive thousands of likes immediately. How would the first approach scale in this case? Since it's running in a CAS loop, it is likely to time out given time constraints. Second, is ETAG comparison the right approach here? Does Cosmos DB write to only one replica so that we could rely on ETAG changes to make sure we're updating the most recent version of a document?

As for the second approach, perhaps eventual consistency is fine, but I hate seeing mismatched information on websites such as (3 comments on a post, but you can read 5 total comments). If the lag was tiny, this could be tolerated, and I'm not sure how much of a lag a typical, say, social media app should expect.


texts: How to configure MySQL master master replication but use only one server for writing? Is it done at LB end and how? If two users work on same db and same table and they both enter a data into it which users data will replicate first and will there be any loss of data or data will overwrite?


texts: Insert max numeric value in Postgres column

texts:
Suppose I have a table like

CREATE TABLE public.products
(
    id       INTEGER NOT NULL
    article  CHARACTER VARYING(255)
    name     CHARACTER VARYING(255)
    quantity NUMERIC(10, 3)
)

As you can see, max value that can be inserted into quantity field is 9999999. If I try to insert value like 9999999+1, of course I receive

  Numeric value out of range: 7 ERROR:  numeric field overflow

Is there any way to track on INSERT/UPDATE that value of quantity exceeds 9999999 and instead of this value insert/update column with 9999999?

Of course NUMERIC(10, 3) can be NUMERIC(10, 2) or NUMERIC(5, 2) and whatever, so the maximum value should not be hardcoded in query text.

In pseudocode it is something like

UPDATE table SET quantity = IF value > 9999999 THEN 9999999 ELSE value;

Sure, I can perform some data checks before running pgsql-query, but currently this is the last solution that I can use.


texts: ORACLE DB Connects only when I do not specify SID

texts:
I have an Oracle 19c database in a Unix server.
SQLPLUS command connects only when I do not specify the SID.
sqlplus system/manager
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 20 12:37:30 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Mon Jul 20 2020 11:45:41 +05:30

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

However, when I try with the SID, it does not connect.
sqlplus system/manager@S19110

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 20 12:40:01 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

ERROR:
ORA-12537: TNS:connection closed

Enter user-name:

I see the following in my listener.log :
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 32: Broken pipe

I googled the error and tried the following troubleshooting methods but no help.

6751 permission for $ORACLE_HOME/bin/oracle
Increase max number of processes in the database.

Please help.


texts: How should I represent a midnight end time?

texts:
I have a table which represents time ranges.

   Column    |            Type             |                                Modifiers
-------------+-----------------------------+--------------------------------------------------------------------------
 id          | bigint                      | not null default nextval('exploded_recurring_sessions_id_seq'::regclass)
 schedule_id | bigint                      |
 start_time  | time without time zone      |
 end_time    | time without time zone      |

I'll then do a query such as this:

select from my_table where localtime >= start_time and localtime < end_time;

The business logic here makes sense in the case where end_time is midnight, because I consider a range to be inclusive of start_time and not inclusive of end_time.

Of course, this query doesn't actually work, because localtime will never been < end_time when end_time is 00:00:00.

There are two solutions to this I can think of.

instead of 00:00:00, use '00:00:00'::time without time zone - interval '1 microsecond' for midnight. The likelihood of this every causing an incorrect calculation is probably low enough to never happen. And even if it does happen a handful of times per year, the consequences are essentially nonexistent, at least as my system now stands. However, it's an ugly solution, and using wrong data like this might have other consequences down the line.
select from my_table where localtime >= start_time and (localtime < end_time or end_time == '00:00:00'). This seems pretty straightforward, but it makes it necessary to make one or more special indexes. Not a problem if it's the only solution, but feels like a drawback worth avoiding if possible.

Is there any other way to go about this? Some sort of special datetime function which accommodates this calculation? A better way to represent the data?


texts: How do i merge multiple records into a single record


texts: SQL ssis delete imported rows from source table

texts:
I have setup a simple SSIS package that copies data from a source table to a destination table which works.
I would like to get the package to also delete the data in the source table that has been copied to the destination, there seems to be a great deal on deleting from the destination but not the source, how can I achieve deleting the copied data from the source?
Thanks

SQL 2008 R2


texts: Register the time a machine running in a timerange using sql

texts:
machinename startdatetime             enddatetime               timerunning(mins)   timerange
vxx:2019    2020-02-07 13:45:18.790    2020-02-07 16:00:38.920  135            12:00

the above machine runs from 13:45 to 16:00. I want to  register the first 15 mins in 12:00 timerange and next 120 mins in 2:00.

Currently my case statement is based on startdatetime and registers the hours in one timerange even though it overlaps two timeranges or can run across multiple timeranges. Also, the 24 hour is supposed to be broken down into 2 hour intervals. So total 12 intervals. And a process can run across multiple intervals.Also fyi i dont have create access on this database. Any help is appreciated. Thanks!

I am looking to present the data as

machinename startdatetime             enddatetime               timerunning(mins)   timerange
vxx:2019    2020-02-07 13:45:18.790    2020-02-07 16:00:38.920  15             12:00
vxx:2019    2020-02-07 13:45:18.790    2020-02-07 16:00:38.920  120            2:00

This is what i tried and it seems to be working.

--split duration on a 2 hr basis

select case when st < startdatetime then startdatetime else st end [startdatetime],    case when et > [enddatetime] then [enddatetime] else et end [enddatetime] from (select *,
            xstart = dateadd(mi, 120*(datediff(mi, 0, d.startdatetime)/120), 0),
            blocks = datediff(mi, d.[startdatetime], d.[enddatetime])/120+2
     from table d) d cross apply (    select
          st = dateadd(mi,v.number*120,xstart),
          et = dateadd(mi,v.number*120+120,xstart)    from master..spt_values v    where v.type='P' and v.number <= d.blocks
     and d.[enddatetime] > dateadd(mi,v.number*120,xstart)) v


texts: I did some research about distributed databases, but I still have some questions. I understand there is something called "fragmentation database" but I can't find any help full document for understanding and implementing that.


texts: SQL Server Agent logging actions and changes done to jobs

texts:
Is there a way to track changes done to SQL Server Agent jobs?

If you had a reason to believe somebody is playing around with your job settings. How would you proceed to track who did it and from where the changes were made to the individual job?

I'm thinking along the lines of extended events.


texts: Restoring a SQL Server 2012 database in high availability

texts:
I have a database which is in always-on high availability mode synchronized with another database on a different instance. How can I restore from a .bak file into the primary database using T-SQL?

I'm new to high availability and I've been advised that I need to take the database out of high availability before I can do a restore and then put it back in high availability again but I'm not sure.

I'm hoping I can just restore straight into the primary while the AlwaysOn is still enabled and it will auto sync with the secondary.


texts: Login failed for user when trying to connect to Azure Postgre database from MSSMS

texts:
I created a database on Azure but can't log to it with MSSMS. I always have the Error 18456 even if I followed this tutorial.

TITLE: Connect to Server
------------------------------

Cannot connect to coventgarden.postgres.database.azure.com.

------------------------------
ADDITIONAL INFORMATION:

Login failed for user '******'. (Microsoft SQL Server, Error: 18456)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------


texts: Backup Error with Msg 3201, Level 16, State 1, Line 1 & Operating system error 3

texts:
When i am taking the backup in network share drive with mention below TSQL.

Use DatabaseName
Go
Backup Database DatabaseName
To Disk=N'H:\DatabaseBackup\DatabaseName.bak';
Go

I am getting the following below error in SSMS.

 Msg 3201, Level 16, State 1, Line 1

Cannot open backup device 'H:\DatabaseBackup\DatabaseName.bak'. Operating system error 3(The system cannot find the path specified.).

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

As I have checked to 'xp_cmdshell' through this TSQL . The run_value & config_value  as follows:

name       minimum  maximum config_value    run_value
xp_cmdshell 0           1     1               1

Note : Here H is my Network share.

Any suggestion will be appreciated.


texts: Table Partitioning in SP1 for SQL Server 2016

texts:
Can anyone validate\conclude for sure if SP1 for SQL Server 2016 now makes Table Partitioning available in the Standard Edition version? It has always been an Enterprise Edition feature in the past. I have seen this on a few blogs, but is this true?

Thank You,
Max


texts: Copying table subset from one database to another with same schema

texts:
I am trying to subset of a table from one table to another

  users(id, name, referrer_id, country_id)
  this table have self referential association with refferer_id as foreign key to itself.
  I tried following query

psql source_table -c 'COPY (SELECT * FROM users where country_id = 2) TO stdout' | psql target_table -c 'COPY users FROM stdin'

Issue with above approach is reffer may not be in the same subset, so it will show the error

ERROR:  insert or update on table "users" violates foreign key constraint "users_referrer_id_fk"
DETAIL:  Key (referrer_id)=(123) is not present in table "users".

I tried to use ON CONFLICT but it seems that ON CONFLICT syntax is not available in COPY TABLE syntax. Is there a better way to transfer subset of tables between databases


texts:
We have the following scenario:

We have several SQL serves instances in distributed geographic areas (distributed buildings) (in a city or maybe globally)

All the SQL servers run one snapshot of a database

The databases should be synchronized (maybe with peer to peer replication or other alternatives)

All computers in a building connected to the SQL server that is located in the building

Connection between buildings maybe lost (for a day for example) and after servers reconnected data should be synced and all the SQL servers should have same data (and we should taking care of conflicts absolutely)

How we can handle this scenario?

(I am not a SQL server expert so it would be nice of you to explain it in more details)


texts: Synchronise data from one table to another & vice-versa using triggers

texts:
We are migrating from one database structure to another - very slowly. The system is MySQL. There are two databases. For example's sake, we will call the databases old_db and new_db. Both databases have tables that will need to be synchronised for around a year whilst the business's application is migrated from the old db to the new db.

Again to slim down the example, we have one table in the old database, called: old_db . myoldertable, new_db . mynewtable.

Structure:

old_db.myoldertable
primary_id, Date, amount, unneedded_column1, unneedded_column2 ... and so-on

new_db.mynewtable
primary_id, date, amount, old_table_primary_id

They look similar in structure, but this is simplified.

Problem:

If I put an insert/update/delete trigger on both tables, I will end up in a recursion loop. This is because the tables need to be synchronised, so the insert / update / delete triggers need to be on both tables.

Requirements

Must be 100% MySQL. Cannot use a programming language for this.
The new and old database tables must be synchronised immediately if either have rows that are inserted/updated/deleted.
Daily / hourly updates are NOT an option. Again, must be synchronised immediately.

Solutions?

Ideas to break possibility of recursion for all three insert / update / delete scenarios.
Any other ideas.

EDIT :

So far I have looked at

One solution I have explored is replacing the old table with a view. However, our new database has two tables (or more) where the old database had only one table. Views, in MySQL cannot be writeable to more than one base table. Also views cannot have triggers in MySQL.

I also looked at using a UUID that represents the last update. I would create a column in both tables to contain a Unique ID. Making sure that both tables contain the same UUID as part of the triggers. However, this does not stop recursion.


texts: How do I back up On Premise Microsoft SQL Server 2008 to Azure cloud

texts:
I have a 50 GB hosted SQL Server database at SoftLayer.

I want to do backups to cloud storage, as the SoftLayer managed SQL Server backups are ridiculously priced. Eventually we will totally move away from their hosting model to pure cloud, but that is a topic for another question.

For the time being, I want to automate backup of "on premise" SQL Server databases to a cloud storage provider. Ideally, I would stay with Microsoft Azure, but I am open to S3 or other options if they are available.


texts: In what sort of way "v$transaction" view will help DBA's to improve the performance of the query?

texts:
I've just started learning some DBA tips and techniques in Oracle and I came across v$transaction view. I've read some articles regarding this view but some things are still ambiguous ! I want to know:

On which circumstances do DBA's normally use this view?
What does USED_UREC column shows us? the description "the number of undo records held by an active transaction" is a bit confusing .
In what sort of way this view will help DBA's to improve the performance of the query?

I was wondering if you could help me understand the question above.
Thanks in advance


texts:
after trying out this script: Script out all alerts By Carolyn Richardson

or even the traditional way of ssms -> right click -> script alert as

none of these methods are fully comprehensive because they miss out information that is inside the alert, like the job it calls, etc.

for example I have been Setting Up Failover Alerts

and I get many actions set up as you can see on the script below:

-- 1480 - AG Role Change (failover)
EXEC msdb.dbo.sp_add_alert
        @name = N'AG Role Change',
        @message_id = 1480,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1;
GO
EXEC msdb.dbo.sp_add_notification
        @alert_name = N'AG Role Change',
        @operator_name = N'<OperatorName,sysname,Alerts>',
        @notification_method = <Notification,INT,1>;
GO

is there a way to script all these info along with the alert?


texts: Does SQL Server really require fully qualified object references to match a statement to a cached execution plan

texts:
From SQL Server Docs, section Execution Plan Caching and Reuse:

The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. For example, the first of these SELECT statements is not matched with an existing plan, and the second is matched:
SELECT * FROM Contact
SELECT * FROM Person.Contact

Is the above statement also true for object references that use the default dbo? The reason I ask is because I personally never fully qualify object references (and I assume most people don't) even when using a driver like JDBC. Does this mean that all of my SQL statements are being re-compiled every time they're executed?


texts:
I get following error when executing the query

  Invalid token in '[count(../[name(../)=name(.)])=count(../) and count(../*)>1'

However when installing the Itstar_xml_util package, it asks for two initial variables one quot and another call gt, which form the initial structure of the XML, I believe that is causing the error, someone knows how to configure correctly?

SQL> @C:\instantclient_11_2\itstar_xml_util.pxk
     Packge created.
     Enter value for quot: ?
     Enter value for gt: ?

Also follow the query...

SQL > declare
      l_sql_string varchar2(32000);
      l_xml xmltype;
      l_json xmltype;
      begin
      l_sql_string := 'select DESCRICAO from VEI_TIPO_VEICULO';
      l_xml := 'itstar_xml_util.sql2xml(l_sql_string);
      dbms_output.put_line(l_xml.getclobval());
      l_json := itstar_xml_util.xml2json(l_xml);
      dbms_output.put_line(l_json.getclobval());
      end;
      /


texts: "Login failed for user..." with cross-instance access on same server in SSIS package

texts:
I have 2 SQL Server instances running on the same server.
Lets call the instances Instance_A and Instance_B and the server Svr0045.
Both server use the same service accounts (a service account is bound to the server, not the instance, so they all share the same service accounts). For simplicity sake, this service account is named Svr0045_SQLSvc.

I have an SSIS package that is running on Instance_B under the service account which tries to access Instance_A using an OLEDB Connection Manager set to Windows Authentication.
I say "tries" because i get the "Login failed for user 'Svr0045_SQLSvc'" error when i run the package.
Since the service account is the same for both instances i assumed that it should work.
The service account has sufficiant rights (since it's a member of the sysadmin role), is granted connection to the instance and is enabled.

Questions:
Is this problem known / is there a reason to this?
How can i get it to work (if there even is a way)?


texts: Dropping rows based on the previous selection statmnet

texts:
I have a database with following tables: temp_1, temp_2.... and cached_tbl. Inside cached_tbl I have column table_name and in corresponding rows I store all the temp table names I mentioned above. SO what I wanted to do is to create event scheduler that would drop temp tables that were created more than a day ago...For that purpose I am writing something like

SELECT (DROP)
    *
FROM
    information_schema.TABLES
WHERE
    table_schema = 'db' and CREATE_TIME < (NOW()-INTERVAL 24 HOUR)

But I also wanted to delete the rows containing just removed table names inside cached_tbl table... Please advise how I can do it...
In the regular programming I would just through all the selected table_names from previous script into the array and go through the loop comparing this table names to the table names in cached_tbls... But I have no idea how implement it in mysql


texts: Converting show-plan XML to graphical view plan

texts:
I am using the performance dashboard to set up baseline monitoring for my inventory of SQL servers. As per the performance dashboard available within SSMS, when i click on any query to view  the plan from ssms or via SSRS reporting it always give XML format.

Later i will have to copy that XML and save as .SQLplan to open it in SSMS.

How can i convert the same within SSRS to display as graphical plan, rather than going on that specific SQL server and copy paste the XML plan and then use SSMS to view the graphical plan?

Is above possible or one needs to know program this in JAVA.net or any other language to display?


texts: SQL Server Replication - Can destination db have stored procedures that source db lacks?

texts:
I'm a developer with a very basic question, but I can't see anything conclusive on MSDN. I'm developing a web application that uses a SQL server 2008 R2 back end. According to this MSDN page, SQL replication can be set up to replicate only certain database objects.

If stored procedures, functions, etc. are created on the destination database (the subscriber in replication parlance) will they survive future replications from the source database? I only need the data from the source system, so the replication need not publish anything but tables.


texts: Mixed SQL Server instances with static and dynamic ports

texts:
Let's assume someone has 5 different SQL Server instances running on a server. Initially all are using dynamic ports.

Is it safe to restrict one of those instances to use the static port 1433 while all others still use dynamic ports?

Normally, depending on startup order it could happen that one dynamic instance claims port 1433, so that the fixed port instance can't grab it later on. Would this possibly prevent the fixed port instance to run?

Or is there any internal mechanism in SQL Server that starts fixed port instances first, and dynamic port ones afterwards?


texts: Is using a JSON data type here appropriate?

texts:
I am using MySQL 8.0 and I have an ACL table, which has two columns int UserID and tinyint GroupID. They both form a composite primary key.
A user can be in many groups, however there are only a few users who have such groups (administrators, moderators, etc.).
I have to get the user's ACL groups when they log in, but because of that I have to perform two SELECT queries, one of User table and another of ACL table.

Would it be a good choice to change the data type of GroupID to JSON and store all group IDs of a user in one field? I could then use INNER JOIN and get all necessary data with a single query and UserID would become the primary key of ACL table. Are there any drawbacks to such a structure or perhaps better solutions?
I intend to mostly just use the SELECT operation on the GroupID field, UPDATE and INSERT would be rarely used.


texts: How to get MySQL database to stop repeatedly turning itself off and on?

texts:
I am running a local MySQL database on my Macbook, and when it works it works. But every once in awhile, it will have turned itself off. Then when I start it again, it will repeatedly stop and start itself every few seconds or so. If I stop it manually and start it again, it repeats the cycle.

How can I get it to stay on? What could be causing this? I have connected to it through Python and also use my terminal to access it, could that be causing the issue?


texts: Good database structure for scenario with orders that have a state and the state has a date

texts:
so we have a table orders

with say:

id, product_id, order_state_id, comment

1, 5, 2, 'thisisatest'

we have a table order_state
with

id, name

1, pending processing

2, payment accepted

for each of the order states of an order, i must save a date as of when the state was performed. The order can only be in one state at a time.

say we have an order that gets put into payment accepted state. Now I have to save a date/time along with it.

what is the cleanest / best way of doing that?


texts: Starting up sql server after hard-drive recovery

texts:
So I'm in a bit of a pickle. My primary work computer has recently crashed and some of the windows files were corrupted I tried repairing but did not work. This computer was running a program that stores all my customer information and invoice data(so pretty critical). This application had a Microsoft SQL server back end. I managed to recover the data and put it on a new computer, I can launch the application but it wont work since SQL server is not running.

When ever I try to start up SQL I get the following error "SQL Server could not fine the default instance(MSSQLSERVER)" which makes sense since its not actuality installed on this new computer. Is there anyway I would be able to transfer the data over to a new instance of SQL server if I cant start up the original SQL server ?

Asking the company that made the application is not possible since they went out of business a while back.


texts: SSRS 2014 - query running fine on management studio - report timing out - how to troubleshoot?

texts:
I had a problem in reporting services 2014 - all the queries in a report were running fine from management studio but timing out when running the report.

here is the message from the user:

what to do to troubleshoot a SSRS 2014 report that gets stuck on loading?

where are the SSRS logs?

the report has 3 datasets and all of them run fine when I take the query off and run it through SSMSS.

this reporting services server, has only the service, the databases (ReportServer, etc) are on a different server but that is not the problem, it has been running for months.

this is the query of the report:

USE Product
declare @SeasonId int;set @SeasonId = 16

SELECT DISTINCT
        EDP [EDP],
        '-' [PO],
        NULL [PO Type Initial/ Repeat],
        NULL [PO Status Open/ Close],
        s.StyleCode + c.ColourCode + sz.SizeCode [SKU] ,
        StyleDesc [Style Description] ,
        s.StyleCode [Style] ,
        c.ColourCode [Option] ,
        SizeCode [Size] ,
        NULL [MPLAN Segment Code],
        SeasonDesc [Season],
        DepartmentDesc [Department],
        CategoryDesc [Category],
        NULL [ALL Season, SPR Only, SUM Only, INTERNET Only],
        NULL [Fabric Type],
        NULL [Garment Composition],
        NULL [Country Of Origin],
        NULL [MID Code],
        NULL [HTS Code],
        0.00 [Cost to use],
        NULL [Exception Flag],
        NULL [Duty Rate]
FROM    [Product].[dbo].[Style] s
        JOIN Product.dbo.StyleDetail sd ON s.StyleID = sd.StyleID
        JOIN [Product].[dbo].[Category] ct ON s.CategoryID = ct.CategoryID
        JOIN [Product].[dbo].[Department] d ON ct.DepartmentID = d.DepartmentID
        JOIN [Product].[dbo].[Segment] sg ON d.SegmentID = sg.SegmentID --
        JOIN [Product].[dbo].[Item] i ON s.styleid = i.styleid
        JOIN [Product].[dbo].[ItemColour] ic ON i.itemid = ic.itemid
        JOIN [Product].[dbo].[Colour] c ON ic.colourid = c.colourid
        JOIN [Product].[dbo].[ItemSize] isz ON i.itemid = isz.itemid
        JOIN [Product].[dbo].[Size] sz ON isz.sizeid = sz.sizeid
        JOIN [Product].[dbo].[SeasonItem] si ON i.itemid = si.itemid
        JOIN [Product].[dbo].[Season] se ON se.seasonid = si.seasonid
        JOIN [product].[dbo].[ItemMPLANReference] pn ON pn.itemid = i.itemid
        JOIN [Product].[dbo].[Product_Staging_CategoryImportMap] ci ON ci.CategoryID = ct.CategoryID
        JOIN [SAPurchaseOrder].[dbo].[tblPO1Detail] pod ON pn.EDP = pod.strItemNo

WHERE se.seasonid IN (@SeasonId)
and EDP not in (SELECT skuEDP from USCarrier.dbo.WHDespatchSku)
ORDER BY 1

this problem has been solved.
how I did it?

I had a look at the query plan and noticed a missed index.

USE [SAPurchaseOrder]
GO
CREATE NONCLUSTERED INDEX [idx_strItemNo]
ON [dbo].[tblPO1Detail] ([strItemNo])

GO

After I added the index the report started to work fine again.

What I think that really happened is:
SSRS had cached the query execution plan, and there was a case of parameter sniffing.
Once I created the index the plan was removed from cache and had to be recompiled.

this is very similar to this question here:
SQL runs slow in SSRS, but fast in SSMS

One good thing I think useful from the question above is how to eliminate the parameter sniffing possibility:

  As discussed in the comments, let's get rid of the parameters to see
  if your query is getting affected by parameter sniffing.

  To do this we build the SQL statement from scratch. Most things in
  SSRS can be expressions including the SQL query so you can build it as
  a string. With the parameters, we'll convert them to a comma delimited
  list using JOIN.

questions remain:

what to do to troubleshoot a SSRS 2014 report that gets stuck on loading?

where are the SSRS 2014 logs?


texts: Changing all character varying to text across all tables and columns

texts:
I'm trying to find a way to iterate over all of my tables and changing each charvar[n] to text and charvar[][n] to text[].
However, only found ways to change it for a specific table and column or specific column name. Using postgres 10.
Any advice?

Thanks


texts:
My MySQL log directory contains the following files:

7.9K   mysqld.err-old
4.0G   mysqld-slow.log
94     mysql.log
93M    mysqld.err

mysqld-slow.log contains a long list of queries with some metadata.

Questions

Which queries are logged into that file?
Which tools do I have to analyze the runtime of slow queries?


texts: MongoDB moveChunk (chunk migration) chooses secondary with larger ping time for writeconcern majority

texts:
We have a sharded cluster using replica sets with local secondaries (pingMs = 0), and remote secondaries (pingMs = 40).  So, each replica set has a local secondary and a remote secondary.  Chunk migration used to choose the local secondary when moving a chunk to satisfy its "majority" writeconcern used by Mongo's chunk migration.  And our transfer speed was reasonably fast.

Today we added another shard using a replica set configured identically to the other shards.  And, now when it does chunk migration to that new shard, it's using the remote secondary instead of the local secondary to satisfy the "majority" writeconcern and transfers are very slow due the network latency??  Am I correct in thinking that Mongo should be choosing the local secondary to satisfy the "majority" writeconcern because it has a much lower ping time?

I've compared rs.config() between the new shard and one of the older shards and they are identically configured.  We're using MongoDB 3.2.6.


texts: Cannot find record in table after upgrading mysql from 5.6 to 5.7

texts: We recently upgraded our database from mysql 5.6 to 5.7 and started seeing a new error that happens to occur intermittently. The error happens while trying to access results of a query or while getting the total count. The error message that comes back looks like Can't find record in tablename. The same query works perfectly fine when tried again almost immediately. Also, we are only seeing this issue in production which makes me think if we are missing some config while upgrading to Mysql 5.7. Please let me know if you need other specific information since I'm not sure what all would help at this point.


texts: Incorrect result when using 'case when count()=0' & 'where'

texts:
I would very appreciate if someone could help me out with this query.
I can't seem to get the correct result when using case when count()=0 & where

I work with SQL Server, with a DB of a system that is alike "Facebook".

I'm trying to write a query that shows the email address, tagging status & the number of posts posted in the past month.
The tagging status should be as follows:
If tagging 1-2 posts - display "few"
If tagging 3-5 posts - display "medium"
If tagging 5+ posts - display "many"
If not tagging at all - display "none".

Below is the query I wrote. The problem with the query I wrote is that because of the condition in WHERE, I will never receive 'none'.

How can I change the query in order to display the correct result?

select U.Mail, count(P.ID) as PostCount,
  case
    when count(P.ID) = 0 then 'none'
        when count(P.ID) <= 2 then 'few'
        when count(P.ID) <= 5 then 'medium'
    else 'many'
  end PostCountCategory
from Users U
    left join Tagging T on U.Mail = T.Mail
    left join Post P on T.IDPost = P.ID
where datediff(day,P.DatePosted,getdate()) <= 30 --Because of this condition I would never get 'none'
group by U.Mail, U.Gender

Data for example:

The required input should be: Kelly-'none', Lilly-'none', Nelly-'few', Owen-'none'.

create table Users
(
Mail nvarchar (20) primary key check(Mail like '_%@_%._%' and (Mail like '%[0-9]%' Or Mail like '%[a-z]%'Or Mail like '%[A-Z]%')),
Password nvarchar (8) check (Password like '%[0-9]%' and Password like '%[az]%' and len(password) <= 8) not null,
FirstName nvarchar (20) not null,
LastName nvarchar (20) not null,
BirthDate date check (datediff(year,BirthDate,getdate())>=18) not null,
JoinDate date check (JoinDate<=getdate()) not null,
Gender nchar(1) check(Gender = 'F' or Gender = 'M' or Gender = 'O'),
NickName nvarchar(20),
Photo nvarchar(20),
Phone bigint check (Phone like '%[0-9]%' and len(Phone) <= 10) not null
)

INSERT INTO Users
VALUES
('Kelly@gmail.com','k1000000','Kelly','Ka','1992-05-15','2016-09-04','F','Kelly','Kelly.jpg','546296100'),
('Lilly@gmail.com','l1101111','Lilly','La','1999-04-03','2012-04-04','F','Lilly','Lilly.jpg','542448300'),
('Nelly@gmail.com','n130131','Nelly','Na','1994-03-07','2020-04-13','F','Nelly','NellyNa.jpg','541234567');
('Owen@gmail.com','o140141','Owen','Oa','1992-02-02','2020-05-13','M','Owen','OwenOa.jpg','541234567');

create table Post
(
ID int identity(1,1) primary key,
Photo nvarchar(20),
Text nvarchar(200),
Location nvarchar(50),
Video int,
DatePosted date check (datediff(month,DatePosted,getdate())<=3) not null,
UserMail nvarchar (20) references Users(Mail) on delete cascade on update
cascade not null
)

INSERT INTO Post
VALUES
('','my name is nellu','','','2020-05-08','Nelly@gmail.com'),
('','hii','','','2020-02-19','Lilly@gmail.com');

create table Tagging
(
Mail nvarchar (20) references Users(Mail) not null,
IDPost int references Post(ID) not null,
TagMail nvarchar(20) references Users (Mail) not null,
primary key (Mail,IDPost);
)

INSERT INTO Tagging
VALUES
('Nelly@gmail.com','1','Kelly@gmail.com'),
('Nelly@gmail.com','1','Owen@gmail.com');


texts: PostgreSQL: Creating view of multiple tables in function

texts:
I want to create view of multiple table in function using PostgreSQL 9.3 version.

Example:

I have following tables in the table name "AllTables":

table1, table2,table3

And I have following tables in the database:

table4,table5

Now I need to combine these tables into one and create view within a function.

create or replace function fun1()
returns void as
$body$
Declare
       que varchar;
       b varchar;
Begin
      Select 'select * from ROM ' ||  quote_ident("TableName") ||' UNION'
    FROM "AllTables" a
    INNER JOIN INFORMATION_SCHEMA.Tables so on a."TableName" = so.Table_name into b;

    que := 'Create view view1 as '|| LEFT(b,length(b)-length('UNION'));
    raise notice '%',que;
    execute que;
end;
$body$
language plpgsql;

Note: Able to get only one table. Not getting all tables view.


texts: If I create a table that has a SecureFile column, does it mean that all the LOBs put into the column are automatically saved as files? In other words, DBFS and SecureFiles go hand in hand mandatorily or SecureFiles can exist without DBFS too?


texts: how to set up sql server alerts based on a schedule basis?

texts: We would like to stop getting sql server alerts from dev servers during the weekend. We have alerts for severity 016 to 025.


texts: innodb_flush_method=O_DIRECT vs O_DSYNC performance impact on ext3 with LVM disk partition

texts:
In one of my production environments, we have two instances running on a RedHat cluster, with  one production instance associated with the cluster.
We have 125G main memory with 24G InnoDB buffer pool occupied by instance1 & 12G occupied by instance2, which is not associated with the RedHat cluster. Both the data and transaction logs are located on the LVM disk partition with an ext3 file system.
For a performance boost and better I/O throughput I have decided to change innodb_flush_method to O_DIRECT.
With reference to the MySQL documentation:

Where InnoDB data and log files are located on a SAN, it has been found that setting innodb_flush_method to O_DIRECT can degrade performance of simple SELECT statements by a factor of three.

Referring to High performance MySQL Ver 2 and 3, it stated that InnoDB developers found bugs with using innodb_flush_method=O_DSYNC. O_SYNC and O_DSYNC are similar to fsync() and fdatasync(): O_SYNC syncs both data and metadata, whereas O_DSYNC syncs data only.
If that all seemed like a lot of explanation with no advice, here’s the advice:

if you use a Unix-like operating system and your RAID controller has a battery-backed writecache, we recommend that you use O_DIRECT. If not, either the default or O_DIRECT will probably be the best choice, depending on your application.

By googling, I got this benchmark report: on O_DSYNC vs O_DIRECT

Bench Mark Report :
===================
1B Row Complex Transactional Test, 64 threads

 *   SAN O_DIRECT: read/write requests: 31560140 (8766.61 per sec.)
 *   SAN O_DSYNC: read/write requests: 5179457 (1438.52 per sec.)
 *   SAN fdatasync: read/write requests: 9445774 (2623.66 per sec.)
 *   Local-disk O_DIRECT: read/write requests: 3258595 (905.06 per sec.)
 *   Local-disk O_DSYNC: read/write requests: 3494632 (970.65 per sec.)
 *   Local-disk fdatasync: read/write requests: 4223757 (1173.04 per sec.

However, O_DIRECT disables the OS level cache, where double caching can be disabled which show some better I/O throughput.
Is it good to go with O_DIRECT rather than O_DSYNC? These two options are a bit confusing. Which option can show some better I/O throughput and enhancement in the performance without any impact on the data, reads/writes especially in production? Any better suggestions based on your personal experience?
I could see Rolando Update in the post:

Still there is slight confusion on both of these parameters. Where i could see most of the production config templates using O_DIRECT, i haven't seen any where recommending O_DSYNC.

System

MySQL 5.1.51-enterprise-gpl-pro-log
Red Hat Enterprise Linux Server release 5.5
DELL DRAC with Raid Controller having a battery write back cache 512MB
Dell PERC controllers H700 with a battery back-up unit (BBU).

Additional information

mysql> show variables like 'innodb_thread_concurrency';

+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_thread_concurrency | 96    |
+---------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_read_io_threads';
Empty set (0.00 sec)

mysql> show variables like 'innodb_write_io_threads';
Empty set (0.00 sec)

We are using the default plugin, so I have posted the info from InnoDB status:

mysql> SELECT * FROM Plugins WHERE PLUGIN_NAME LIKE '%innodb%' AND PLUGIN_TYPE LIKE 'STORAGE ENGINE'\G
*************************** 1. row ***************************
           PLUGIN_NAME: InnoDB
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: STORAGE ENGINE
   PLUGIN_TYPE_VERSION: 50151.0
        PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
         PLUGIN_AUTHOR: Innobase OY
    PLUGIN_DESCRIPTION: Supports transactions, row-level locking, and foreign keys
        PLUGIN_LICENSE: GPL
1 row in set (0.00 sec)


texts:
I want to use SQL Server for a big project and was wanting to know what licence is needed, Enterprise or Standard?

I have 2 servers and I'm looking to have the first as a primary and the second as a mirror (as a reporting server and DR server), from what I can see on Microsoft's comparisons I should be able to do standard but am I missing anything?

I understand I would need to manually promote the mirror in the case of DR, but would enterprise make it easier/faster?


texts: Tempdb tables, table in database or even cte for usp for reporting

texts:
I am about to start a project for SSRs report base on a USP. The procedure will have to use two cursors to compare orders v stock position (similar to a mrp run ): I am thinking of storing the data into a table that can be used In the ssrs report.

So the question is for a result set of no more than a few hundred lines what sort of table should I use. One in the production dB. A temp table in tempdb or can you use cte ?

The table will be dropped each run of the USP/ report

What comments are there

Thanks


texts:
Does anybody know when the mysql slow log file delete slow query records?

based on my daily observation. The log file starts at 7am. I have searched for this but nothing answers me clearly. My server version if 5.6.14. Thank you.


texts: Need for backing up MySQL databases information_schema, performance_schema, mysql and phpmyadmin?

texts:
I know it's not necessary to backup the information_schema database, but what about the following databases (besides the user-defined databases)?

performance_schema
mysql
phpmyadmin

(So, in case of problems, and we've to start from scratch (fresh MySQL-install), which databases do I've to place back)?)


texts: How can I remove identical (but negative) rows that are also in some days of each other from a SELECT on a table

texts:
I have a MySQL table with two fields: date and value.  date is of type date, and value is decimal(10,2).
There are "pairs" of rows (non-consecutive) whose values are of equal magnitude but opposite sign, within 7 days of each other.
date        value
...
2020-06-20  123.45
...
2020-06-25  -123.45
...

The above is a "pair" because they are negatives of each other and their dates are within 7 days of each other.  There may be any number of intervening rows.  There may be any number of pairs in the table.  Pairs of differing values may overlap 7 day periods.  For simplicity assume there are no other equal-value rows also with 7 days (ie, no triples).
I'm trying to find a SELECT statement that gives me all the table's rows EXCEPT "pairs".  Could someone please help?


texts: Is there an elegant way to send query results directly to a physical printer?

texts:
I've got this weird request to have a report be scheduled and printed to a printer.

The report itself is pretty simple, and will fit on a single page. I can print it to a txt and that will be just fine (although I'm open to suggestions on a better product).

Here's the code. It works. But I don't like it. Specifically, Id like to do away with the nested @bcp and @SQL.

Question: Is there a more elegant way to do this?

declare @filepath varchar(255),
@filename varchar(255),
@filetype varchar(255),
@sql nvarchar(max),
@coverpage_text nvarchar(max)

set @filepath = 'C:\users\jmay\documents\'
SET @filename = 'TestFile'
set @filetype = '.txt'

--output to txt
set @sql = N'declare @bcp varchar(4000)
set @bcp = ''bcp " select * from test_data " queryout '
+ @filepath +  @filename + @filetype + ' -t " - " -c -T -d DBA''
print @bcp

EXECUTE master.dbo.xp_cmdshell @BCP'

print @sql
 exec sp_executesql @sql

--print data
 set @sql = N'declare @bcp varchar(4000)
set @bcp = ''START /MIN NOTEPAD /P ' + @filepath +  @filename +  @filetype + '''
print @bcp

EXECUTE master.dbo.xp_cmdshell @BCP'

print @sql
exec sp_executesql @sql


texts:
One of our INNODB tables contained about 350 million entries. All of a sudden, we started getting the error 'The table 'table_name' is full'. The table contains logs-like entries(click logs, basically). Since we didn't need data that was a year old, as a temporary fix I deleted about 30 million entries. However, since we are getting about 5-6 million entries everyday now, I fear that this error would crop up again. Any thoughts on how to overcome it ?

The specs of the machine are : 48 GB ram, 757 gb SSD. Currently, the used space on disk is 500 gb. 30 % free space.


texts: Is there any other way that we can take auto backup in oracle 11g apart from RMAN?

texts: I have tried with RMAN. But is there any other possible way that we can backup database and tables. backup using queries or stored procedures is possible?


texts: Post mortem on tempdb transaction log after instance restart?

texts: TempDB transaction log filled up and I had to restart SQL Server as a result. After the restart, is there any way to see what caused the transaction log to fill up in the first place?


texts: Index rebuild - Not reducing fragmentation

texts:
I have a table with nearly 18 million records and it has nearly 14 indexes including a clustered index.

One of the non-clustered index has a fragmentation level of 97%, so when I tried to rebuild the index - fragmentation level goes down to 96% or 94%. But, it is not reducing any further. Few details about the index,

Key columns: 2 (Includes clustered index column)

Col 1 - nvarchar(255) – Clustered index column
Col 2 - nchar(16)

Included columns: 2

Col 1 - smallint
Col 2 - smallint

Pages: 143539
Index size: 1.3 GB
Fill factor: 90
Row locks: True
Page locks: False
Pad index: False

Any ideas why fragmentation level is not reducing?


texts: How can I recover data from the LDF file?

texts:
We are using SQL 2005 Express Edition. We want to read the data from the LDF file in order to restore some deleted records.

We tried using a trial version of ApexSQL which helped us a lot. Instead of going for third party software, we are trying to figure out how to read the log file ourselves.

How can we read and recover data from the LDF file?


texts: Soft-deleting change-tracked user content

texts:
To begin with, I distinguish between tables containing user-generated content and tables containing information about the user. The former are associated with the user profile, whereas the latter are directly associated with the user. In particular, foreign keys referencing the user profile are set to cascade deletions. Thus, user-generated content is deleted along with the profile, whereas information about the user is retained.

Furthermore, any modification to the content tables is kept track of in separate "change tables". This is accomplished using triggers. After inserting user content, the newly created entry is copied to the corresponding change table. When the entry is updated, only the modified fields are stored in the change table while the rest is left empty. The entry in the change table also references the content entry whose change it tracks as well as the user who authored the change (see figure 1). When a content entry is deleted, the foreign keys referencing that entry in the change table are set to null (for example the user_content_id field in the user_content_change table).

Figure 1: Example Database Schema.

Now this is what I am not happy with at all. Consider the following scenario: a user creates a ticket to report an inappropriate comment. The ticket references the comment in question. However, the comment author then decides to delete his or her profile and hence the comment. Although the comment text is still stored in the change table, the reference to the comment is lost, making it hard to review the ticket. (It should be noted that this is a rather typical use case for the change tables. Most of them are queried very occasionally.)

So the alternative I came up with are traditional soft-deletes. By this I mean that the change tracking remains the same but the content tables are each equipped with a deleted_at column. When querying user content, the results where this column is null are filtered out. Since no entry is ever physically deleted, the change tables don't lose their references to the content tables. Moreover, I can merge the user_profile table back into the user table, as it only served as an anchor point for cascading deletions. Regarding performance, I think that I can partition the table on the deleted_at column, so that the table indices aren't cluttered with deleted entries.

Nevertheless, I am also unhappy with this approach. First, it makes querying much more cumbersome and prone to error because one always has to exclude the already deleted entries. Secondly, the convenience of cascading deletions is lost. This applies above all to content that has a lot of metadata and derived content attached to it. An adequate delete trigger may have to update the deleted_at column of 10 to 15 tables -- not to speak of a user profile being deleted!

Could you provide some guidance on how to improve this design, or maybe suggest a different approach? Please note that this is the first time I am confronted with designing a schema at a large scale.


texts: Errors using expdp with `flashback_time=systimestamp`

texts:
Oracle 11.2.0.1.0 on Windows 2012 Std:

expdp '/ as sysdba' full=y directory=exp dumpfile=... logfile=... flashback_time=systimestamp

Fails with:

ORA-39001: invalid argument value
ORA-39150: bad flashback time
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
ORA-01877: string is too long for internal buffer

Replacing flashback_time=systimestamp with consistent=y which AFAIK is a backwards compatible way to say the same exact thing also fails with the same errors. Removing those options completely, with no other changes, makes it work.

Still, I would like to take a consistent backup in addition to RMAN, which yes I do have configured and working. There are various workarounds suggested on the web. For example using to_timestamp but none of them seem to be working.

My questions are:

Is this a known bug?
Are there any settings that could cause this behavior like system locale?
I am using a slightly modified version of this script and I swear just yesterday it worked fine. I don't recall changing anything, today it doesn't work. Any ideas?
Is there a reliable workaround? I am thinking of using flashback_scn instead but that will require a separate query beforehand.

Edit:

Doubling down on the weirdness here. Each day:

00:00-11:59 - errors as above
12:00-23:59 - everything works fine

I ran the script repeatedly yesterday evening using flashback_time=systimestamp - no errors. Today 7:45am getting errors again from the same script, without modifications.


texts: Can I use an index to optimize select clause, versus where clause?

texts:
I'm not sure if this question has been answered, because I'm not sure of the proper wording to describe what I need. So here it is:

I am trying to optimize a database that is running very slowly, hinged on one central table, with 128k records (set to accept about 10x that many in the next couple months), and 24 columns. 90+% of the time a query only needs to return a particular subset of 4 of those columns, and that query needs to be as fast as possible, because it happens constantly.

My understanding is that given enough RAM (our db server has plenty of extra right now), indexes all will be kept in RAM, and any query that only requires fields present in the index will be able to complete without performing a disk read at all, thereby speeding the query significantly.

Is this assumption accurate? And if so, must they all be in the same multi-column index in order for that to work, or would separate per-column indices work just as well? Is there a different method I should be using, since I really want to optimize the selecting and not the filtering part of the query?

The 4 crucial columns are id, person_type, person_id (a foreign key, used in conjunction with person_type), and full_name. The typical use cases are:

The where clause only specifies one or more values for id.
The where clause specifies person_id then person_type.
The where clause specifies person_type then a LIKE condition for full_name.

In each of the above cases, all 4 columns are specified in the SELECT clause.
A few sample queries:

SELECT contacts.full_name, contacts.person_type, contacts.person_id
FROM `contacts`
WHERE `contacts`.`person_type` = 'Usage::User'
AND `contacts`.`person_id` IN (388,389)

SELECT `contacts`.* FROM `contacts`
WHERE (person_type="Usage::Profile" AND full_name LIKE "%Foo%")

SELECT `crm_connections`.*, `contacts`.`full_name`, `contacts`.`person_type`, `contacts`.`person_id`
FROM `crm_connections`
INNER JOIN `contacts` ON `contacts`.`person_id` = `crm_connections`.`id`
AND `contacts`.`person_type` = 'Crm::Connection'
WHERE `crm_connections`.`active` = 1
AND (contacts.full_name LIKE '%Foo%')
LIMIT 1


texts: In SQL Server sp_cursorprepare/sp_cursorexecute run extremely slowly

texts:
I'm running an application (Pentaho user console with the Saiku Ad-hoc plugin) which creates a query and runs it using sp_cursorprepare & sp_cursorexecute standard stored procedures.

My model is mostly made up of tables joined utilising the traditional foreign to primary key method.  However in the middle I have a linking table that contains foreign keys of the tables connected to it (with some other data) but its primary key is never referenced.  I have a clustered index on this table which references each of these foreign keys in order of the quantity of records.  When run the procedure call will the non-clustered primary key index and takes 6 - 7 minutes to run.  If I run the query on it's own it again uses the non-clustered primary key index although it runs in 7 seconds!

Is there anything I could do to combat this behaviour?


texts:
I've a process that is open since 2 days ago and is executing a select as last command, but this session got stuck in runnable status.

I checked inputbuffer several times and everytime it shows the same query
I will share you the result of sp_who2 and please note that last batch is for 2 days ago:

I checked query plan and didn't find any unusual things and it takes 1 second to show result but it's still in my active expensive query in activity monitor

other point is I ran profiler for this SPID since yesterday and here is the result :

this process lock some objects on my db and it blocks and rebuilding index on this tables

now what should I do ? should I kill it without finding root cause?

sp_WhoIsActive result

the result of EXEC sp_WhoIsActive @find_block_leaders = 1, @sort_order = '[blocked_session_count] DESC', @get_locks =1


texts: How to insert fields to different tables at once?

texts:
5 Main Tables: TEMP, PAYMENT, CUSTOMER, PURCHASE, and PRODUCT.

TEMP:     TempID, Email, Name, Product1, Product2, Date, Amount
PAYMENT:  PaymentID, Status, Amount, Date
CUSTOMER: CustomerID, Email, Name
PURCHASE: CustomerID, PaymentID, ProductID, Quantity
PRODUCT:  ProductID, ProductName, Price

How to insert each field from TEMP where TEMP.Email = '$Email' to PAYMENT, CUSTOMER, PURCHASE, and PRODUCT:

TEMP.Email    >> CUSTOMER.Email
TEMP.Name     >> CUSTOMER.Name
TEMP.Product1 >> PURCHASE.ProductID
TEMP.Product2 >> PURCHASE.ProductID
TEMP.Date     >> PAYMENT.Date
TEMP.Amount   >> PAYMENT.Amount


texts:
We have a new Windows 2016 server and I have to install SQL Server 2016 standard edition. Once I install and configure the SQL server I will move my existing production database to the new db server.

I have remote desktop access to the server to install the SQL server, but I am not sure if I need to have domain service accounts? I was of the impression that the default services SQL server assigns during the installation is enough.

Question 1:

The DB Admin team will remote desktop to that server and connect to SQL server using SSMS,not from their local SSMS. In that case do I have to have domain service accounts?

Question 2:

The application which DB support will have few hundred users connecting over network? In that case the default service account will be enough or do I have to have domain service accounts?

I am new to the database administration world, so I am still struggling with few concepts.


texts: How to capture 'Deleted' and 'Updated' record after Merge in PL/SQL.TSQL 'Output' equivalent in PL/SQL

texts:
hope you're doing well.
imagine I have a source and destination table like:

Source(Column1 int , column2 varchar2(50))
Destination (Column1 int , column2 varchar2(50))

I want to merge these two tables and I want to capture those records that have been updated and deleted after the merge statement .
I did that in T/SQL with output like this:

Merge target AS Target
Using source AS Source
  On Target. column1 = Source.Column1
   when Matched and
       Target.Column2 <> Source.Column2
   Then UPDATE
       Set Target.Column2 = Source.Column2
   When Not Matched Then
    Insert (Column1 , Column2)
    Values (Source.Column1 , Source.Column2)

        Output Deleted.column1 , Deleted . column 2
        Into <Some variable>
           Where Actionx <>'Insert'

I know how to write this code in PL/SQL but I do not know how to write the output part.
I was wondering if you could help me out with this. Thanks in advande.


texts: Making a column unmodifiable/non-updatable after its row's initial creation?

texts:
What is the most idiomatic way to make a column unmodifiable/non-updatable after its initial creation via INSERT in Oracle?

CREATE TABLE my_table (
  id           NUMBER       NOT NULL CONSTRAINT my_table_pk PRIMARY KEY,
  -- lots of other columns that are updatable
  crt_ts       TIMESTAMP(6) NOT NULL default CURRENT_TIMESTAMP, -- immutable?
  upd_ts       TIMESTAMP(6) NOT NULL
)

Is there a constraint or technique that can easily be applied to the crt_ts column that does not allow it to be modified on an UPDATE for a single user account.

I know I could hack the permissions to restrict writes to that table and that column and/or do some hacky triggers, updatable views, etc. Those solutions would not be maintainable because I have dozens of tables with columns that have this business logic requirement to be immutable and the database is already in production. I am not sure I might not be missing some low impact solution that is named something strange, because you know, Oracle.

Ideally I would like the crt_ts not to be INSERTable either and just force the default but that might be a stretch.


texts: pg_basebackup failing from another server

texts:
I want to take backup of my PostgreSQL database cluster from another server. I'm using below command ,from another server:

[root@shiwangini2 pgsql]# sudo -u postgres  pg_basebackup -h 192.168.XX.XX  -p 5432  -Ft -D /var/lib/pgsql/db_file_backup

However, I'm getting below message after running this:

[root@shiwangini2 pgsql]# sudo -u postgres  pg_basebackup -h
192.168.XX.XX  -p 5432  -Ft -D /var/lib/pgsql/db_file_backup
Password:

  pg_basebackup: could not get write-ahead log end position from server:
  ERROR:  could not open file "./pg_hba.conf": Permission denied
  pg_basebackup: removing contents of data directory
  "/var/lib/pgsql/db_file_backup"

In order to fix this,after googling I updated my server's pg_hba.conf file like below,to allow connections from outside:

host    all             all              0.0.0.0/0
md5
host    all             all              ::/0                            md5

After that I restarted PotgreSQL services. And again, when I'm trying to backup it from another server, I'm still getting the same error. Please let me know if anything wrong I'm making here. Or any other way to take backup from another server.


texts: Different types of result for coulmnName = nulll and coumnName IS NULL in PostgreSQL

texts:
I have table Party with columns party_Id, party_type

When I use the below queries, I get two different types of result.

SELECT * FROM party WHERE party_type = null

Returns nothing

SELECT * FROM party WHERE party_type IS NULL

Returns all possible rows

Need a query which should accept both null and values.


texts:
My standalone install of SQL Server 2017 is stuck on the Feature Selection page. Below is what I see in the install logs, does anyone have any ideas how to get past this?

  (08) 2017-10-24 11:15:20 Slp: Running Action:
  SetMultiInstanceTransformAction (08) 2017-10-24 11:15:20 Slp: Error:
  Action
  "Microsoft.SqlServer.Configuration.SetupExtension.SetMultiInstanceTransformAction"
  threw an exception during execution. (08) 2017-10-24 11:15:20 Slp:
  Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException:
  Value cannot be null. (08) 2017-10-24 11:15:20 Slp: Parameter name:
  Cannot find SqlDiscoveryData in Datastore --->
  System.ArgumentNullException: Value cannot be null. (08) 2017-10-24
  11:15:20 Slp: Parameter name: Cannot find SqlDiscoveryData in
  Datastore (08) 2017-10-24 11:15:20 Slp:    at
  Microsoft.SqlServer.Configuration.SetupExtension.QueryPackageTransforms.set_DatastoreDiscoveryData


texts: Why no rows in sys.dm_os_cluster_* on Availability Group?

texts:
Running into some issues with failovers on an Availability Group with two nodes and a file share witness.  I read a post  and then I tried to get the HealthCheckTimeout value from sys.dm_os_cluster_properties and no rows are returned.  My google skills are either not working well this morning or no one else has this problem.  I was able to get the values out of the Failover Cluster Manager.  I want to know why I get no rows out of the cluster DMVs when I do have a cluster.  I am assuming I have something set up wrong.

SQL SERVER 2016 SP1 CU2
WINDOWS SERVER 2016

I also don't get any rows on 2012 SQL and Server versions.

Thanks in advance.


texts:
Issue : I was moving a non clustered index on a 3TB table from one
file-group to another (userfilegroup to index) and it took 11 hours and never completed and i had to kill the process as it was blocking other processes.

Rollback is taking forever. It has been 10 hours since i killed the process and no clue what is happening. Locks are still held and are impacting other processes.

I see the percent_complete as 0% and kill with statusonly also shows 0% after so many hours.

Can you please suggest me if there is a way that i can check if rollback is still doing anything or just got stuck . I am afraid to restart the server as it has to go through the rollback process anyways.

Only positive thing that see is under sp_who2  diskIO moving for this SPID.

****Any help is much appreciated....****


texts: why do SSRS reports run longer for a particular user

texts:
We have been receiving complaints from a particular SSRS user that her reports run slow.  I investigated in the ExecutionLog table in the ReportServer database and I observed something strange.

Fairly consistently, the TimeDataRetrieval is much longer than other users.  Both TimeProcessing and TimeRendering are near the averages of other users.

I am puzzled.  The reports all use the same shared data source that runs as a service account.  I would think if it was a crappy user computer issue, I would see differences in the rendering time.  Same if there was a network issue.

I don't know where else to look - any ideas?


texts: encoding format of multilingual text while saving to database

texts:
I have website that save content in various languages. just to display that i have enter to database.

Suppose i copy a French text in sql server table column it will display correct on my website, but when i try to save it through my website to table then it does not save text in same encoding as before insert method.

My question is regarding sql server text encoding format that it use to save such type of multilingual text??

i want convert my text encoding from website to sql server encoded text.

Is there issue of Collation. Database behind a Multilingual User Interface


texts:
I have a table now containing over 43 million records. To execute SELECT, I usually select records with the same field, say A. Will it be more efficient to divide the table into several tables by different A and save in the database? How much can I gain?
I have one table named entry: {entryid (PK), B}, containing 6 thousand records, and several other tables with the similar structure T1: {id(PK), entryid, C, ...}, containing over millions of records. Do the following two processes have the same efficiency?

SELECT id FROM T1, entry WHERE T1.entryid = entry.entryid AND entry.B = XXX

and

SELECT entryid FROM entry WHERE B = XXX
//format a string S as (entryid1, entryid2, ... )
//then run
SELECT id FROM T1 WHERE entryid IN S


texts: What is the cause of high index fragmentation in SQL server?

texts:
We have an application which is using a SQL Server database. As the application is in use for many years, its database is of huge size, resulting in slowness of application. It was noticed that the data has high fragmentation. So we defragment and build indexes twice a week. After the defragmentation, the following is the fragmentation report of our db.

Results still shows high fragmentation, even the application is only used for 2 to 3 days after defragmentation. Can any expert tell if the results are normal and satisfactory? Can some one suggest some reason of this results?


texts: DateTime conversion fails when the datetime has seconds

texts:
This seems to be most discussed, but I could not arrive at a solution yet. I have a date format and have to add a month to it (DATEADD should not be used as my date is string initially) and finally should convert it as date. 103 format converts but it does not give seconds.
Below is the code I tried
    Declare @time_info nvarchar(max)
Declare @format_time_info nvarchar(max)
Declare @time_info_final nvarchar(max)
Declare @time_detail datetime
SET @time_info='30/1/2020 20:34:16 6 300'

Select @format_time_info =  (Select REPLACE(@time_info,('/'+SUBSTRING( @time_info,
CHARINDEX('/', @time_info) + 1,
LEN(@time_info) - CHARINDEX('/', @time_info) - CHARINDEX('/', REVERSE(@time_info)))+'/'),
(CONCAT('/',(convert(varchar,(convert(int,(SUBSTRING( @time_info,
CHARINDEX('/', @time_info) + 1,
LEN(@time_info) - CHARINDEX('/', @time_info) - CHARINDEX('/', REVERSE(@time_info)))+1))))),'/'))))

Select @time_info_final =(Select left(@format_time_info, charindex(' ', @format_time_info, charindex(' ', @format_time_info)+1)-1))
 Select @time_detail =  CONVERT(datetime, @time_info_final,103)-- Cast(@time_info_final as datetime)-- Convert(datetime,Cast(@time_info_final as datetime2),210) --
Print @time_info_final
 Print @time_detail

This throws the common exception
In this query I used 103
Select time_info_final =(Select convert(datetime,left(@format_time_info, charindex(' ', @format_time_info, charindex(' ', @format_time_info)+1)-1),103))

This is the dateformat I get from source '30/3/2020 20:34:16 6 300' or sometimes '30/03/2020 20:34:16 6 300'. So I used substring to find '/' and added date. The thing that troubles me is the seconds is always 00 ... looks 103 format changes it. Using other formats throws error while converting


texts:
I am trying to normalize the following table but having some difficulties

Mem_id mem_name phone email Book_num Book_titl Author_nam publication edition
100     smith    12443 eml1    200     physics   john         MACGROW   1ST
100     smith    12443 eml1    201     math      martin,tina  MACGROW   2ND
101     hena     1020  eml2    200     physics   john         MACGROW   2ND
102     moon     2020  eml3    204     new java  korth,chira  ALPHA     4TH

1NF:

Member:

Mem_id mem_name phone email

100     smith    12443 eml1

101     hena     1020  eml2

102     moon     2020  eml3

Book:

Mem_id Book_num  Book_titl  Author_name  publication edition

100     200       physics      john        MACGROW      1ST

100     201       math       martin,tina   MACGROW      2ND

101     200       physics      john        MACGROW      2ND

102     204       new java   korth,chira   ALPHA        4TH

2NF:

Member:

Mem_id mem_name phone email

100     smith    12443 eml1

101     hena     1020  eml2

102     moon     2020  eml3

Issue:

Mem_id Book_num

100     200

100     201

101     200

102     204

Book:

Book_num  Book_titl  Author_name  publication edition

200       physics      john        MACGROW      1ST

201       math       martin,tina   MACGROW      2ND

204       new java   korth,chira   ALPHA        4TH

Now i am having difficulty here.Am i on the right way to the normalization ?Is it possible to normalize the book table to 3NF OR it is already in 3NF ?


texts: How to export my query output to CSV in SSMS

texts: I have some outputs in SQL Server Management Studio, but want to export them to CSV for easy transmission to another db. I have seen command to export to xml but not CSV. Thanks Abdulmojeed


texts: Why does Oracle store time in DATE column?

texts:
I have a table with a date column. I used JDBC to save a java.util.Date object into the table.

When I run following query I get date aswell as a time value:

select To_CHAR(departure_date,'DD-MM-YYYY HH:MI') from service

This tells me that date is column is also storing time.

This is unexpected. Shouldn't date type discard time value like an integer type discards fractional digits?


texts: Postgres Compare with Date in Where Clause

texts:
I have a table with ~200 million rows that has a date column. I want to perform the following query.

select my_date from my_table
where my_date > to_date('2020-10-01', 'YYYY-MM-DD');

As the query is executing, on each my_date in each row, the date value in the database will be compared with my string date cast into a date object. Is postgres smart enough to realize that this transformation from string to date object needs only to be performed once or will it execute with each row?