texts:
I am using MySQL 5.7 and need to count results after filtering out the data.
My table structure:

+----+---------+---------+---------+
| id | metakey | metaval | tour_id |
+----+---------+---------+---------+
|  1 |       4 |      15 |       1 |
|  2 |       4 |      18 |       1 |
|  3 |       4 |      23 |       1 |
|  4 |       4 |      19 |       2 |
|  5 |       4 |      23 |       2 |
|  6 |       4 |      25 |       2 |
|  7 |       4 |      27 |       3 |
|  8 |       4 |      29 |       3 |
|  9 |       3 |       5 |       1 |
| 10 |       2 |       6 |       1 |
| 11 |       3 |       5 |       2 |
| 12 |       2 |       6 |       2 |
+----+---------+---------+---------+

metakey -> is something predefined, like 4 for countries,
metaval -> is real data id, for example country id

Let's say I selected rows by the following query:

SELECT tour_id
from metas
WHERE (metakey, metaval) IN ((3, 5), (2, 6))
group by tour_id
having COUNT(tour_id) = 2;

+---------+
| tour_id |
+---------+
|       1 |
|       2 |
+---------+

Now I need to extract a number of tours for each country (country meta key is 4) of that resultset.

Expected result:

|-----------|---------|
|country_id |  count  |
|-----------|---------|
|    15     |   1     |
|-----------|---------|
|    18     |   1     |
|-----------|---------|
|    19     |   1     |
|-----------|---------|
|    23     |   2     |
|-----------|---------|
|    25     |   1     |
|-----------|---------|

I wrote a query which returns a correct resultset, but it took 0.0231 seconds. Number of rows in table: 35036

SELECT metaval, count(tour_id) as numberOfTours
from metas2
where tour_id in ( SELECT tour_id
                   from metas2
                   WHERE (metakey, metaval) IN ((3, 5), (2, 6))
                   group by tour_id
                   having COUNT(tour_id) = 2 )
      AND metakey = 4
GROUP BY metaval

+---------+---------------+
| metaval | numberOfTours |
+---------+---------------+
|      15 |             1 |
|      18 |             1 |
|      19 |             1 |
|      23 |             2 |
|      25 |             1 |
+---------+---------------+
5 rows in set (0.00 sec)

Does anyone have tips to improve performance?


texts:
Is there a way to get to know how many external tasks (console applications) is reading queue?

I am using Service Broker external activator.

What i want is something similar to information i get from sys.dm_broker_activated_tasks, just for external queue readers.


texts: How to validate and upgrade my knowledge of SQL Server with MCSA

texts:
My story is that I am working now almost 10 years as an administrator, I deal with SQL Server and other IT stuff, most of my knowledge is self taught by reading books, dealings with problems and asking and reading sites like this. Now I want to circularize and certificate that by going to, learning and try to pass Microsoft official exams and programs. Where I should start and what kind of curses and exams I need to look for. I am pretty confused whit so many choices the way I can go.

At one local Microsoft Certified Partner they offered me 4 courses for label of Microsoft Certified IT Professional Server Administrator (equivalent MCSA) Also they are offering "MSCSE" as Enterprise Administrator.

Do I need both of this one so I can continue learning for SQL Server Administrator?
Do I need this ones at all ?
What is the correct name in MS terminology for SQL Server Administrator?
Does there exist a SQL Server developer calling in MS world or is this only extension for already developers of common MS languages such as C#, C++ ?


texts: SQL query for finding a set of columns that create a unique key for a table

texts: I have a table with a dozen or so columns and no primary key defined for it. There are several million rows stored in it. Is there a SQL query I could use that would return the combination of columns that will give a unique key for the table? It is specifically an Oracle database.


texts: Foreign Key Constraint Set Back to Trusted

texts:
We have some production tables with numerous constraints that are not trusted at the moment.  We're having them checked/trusted so they can be used again.  We know that the optimizer has not been using them in its plans (because they weren't trusted).  I have two questions before setting them back to trusted.

Will the optimizer recognize the change and consider them before using cached query plans for re-run transactions? Put another way, do I need to consider removing any query plans?
Is it safe to say this is a situation where SQL Server has been rebuilding the index (we do have this turned on), and maintaining the index throughout inserts, updates, and deletes, but simply not using the foreign key?

Thanks


texts:
In order to secure my server, I'd like to set:

secure_file_priv = NULL

As per MySQL's docs - http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_secure_file_priv

  If set to NULL, the server disables import and export operations. This value is permitted as of MySQL 5.7.6.

However, that value doesn't seem to be in MariaDB's docs - https://mariadb.com/kb/en/mariadb/server-system-variables/#secure_file_priv

If I try NULL in MariaDB 10, I get this error:

  [Warning] Failed to normalize the argument for --secure-file-priv.

What do you recommend to prevent import/export operations?

If I set to a given folder, will things like OUTFILE ../../../etc/my.cnf work? (i.e., relative paths, going level ups)


texts: Migrate heroku database to Amazon RDS with minimum downtime

texts:
I have a heroku postgres database and want to migrate it to Amazon RDS to save cost. What's a way to do so with minimum downtime? Usually this involves replicating database in real time and then promoting the replicated DB as the main DB.

I know I can use a follower database to migrate DB within heroku, and I can use read replica database to migrate DB within Amazon RDS. Is there a similar method to create database replication of heroku DB that lives in my own Amazon RDS?


texts:
I am have difficulty wrapping my head around the following issue.  I currently have a set of data in one table as shown below:

As you can see, there is an invoice ref number along with a date, and two value fields.  Each row has either a Y or N for the desired group the record belongs to.

Unfortunately I don't have access to re-format the data (coming out of an ERP system), and ideally I want to pivot the date, Value1, Value2 fields into columns per group.  At the moment I have a rather long winded approach of creating 6 small temp table vars with the InvoiceRef and Date/Value1/Value2 for each of the 3 groups.

I then read the main table, joining (left join) on InvoiceRef to each of the InvoiceRef fields within each of the temp table vars to get the following output:

So as you can see, I get the Date/Value1/Value2 fields for each InvoiceRef in the desired group across the columns..

My question is I feel there is a more efficient way to do this?  although just trying to grapple with pivot/crosstab is proving a challenge - I feel I need some pointers or even confirmation the temp table solution is generally an acceptable solution in this case?

Many thanks in advance.

P.S: For reference I am using MS-SQL 2008 R2


texts: How to select multiple columns between columns in Oracle SQL?

texts:
A question that was asked among a few developers, now deferred to DBA experts:

Is there a way to select multiple columns within a table instead of SELECT'ing columns piecemeal?  For example, let's say example table contains 26 columns respectively titled by letters (e.g. A, B, C, etc.).  Suppose I would like to retrieve all rows from columns H, I, J, K, L, M, N, O, P- is there a more efficient way to begin the SELECT statement of the following query?

SELECT H, I, J, K, L, M, N, O, P FROM table;

Perhaps something like so?

SELECT H-P FROM table;

Thank you!


texts:
Below is my query. i get an error msg. it works when i remove the fields without the sum. I have added a screen shot of the data. Thanks!

SELECT
    S.PRODUCT_ID,
    SUM(DISTINCT S.AWARD_AMT),
    SUM(DISTINCT S.EST_AMT),
    SUM(PMT.PMT_AMT),
    S.CREATE_DT,
    S.APPROVE_BY,
    S.EVALUATOR
FROM SALES S
    INNER JOIN INVOICE INV
        ON  S.PRODUCT_ID = INVS.PRODUCT_ID
    INNER JOIN PAYMENTS PMT
        ON INV.S.PRODUCT_ID = PMT.S.PRODUCT_ID
            AND INV.SEQ_NO = PMT.SEQ_NO
WHERE CAST(CREATE_DT AS DATE)>='01-JAN-2014'
    AND INV.STATUS_CD = 'CREATE'
GROUP BY S.PRODUCT_ID
order by S.PRODUCT_ID

This was my final query. It worked. I had to remove the dups in excel

SELECT
    S.PRODUCT_ID,
    S.CREATE_DT,
    S.APPROVE_BY,
    S.EVALUATOR ,
    SUM(DISTINCT S.AWARD_AMT) OVER (PARTITION BY S.PRODUCT_ID) AS S.AWARD_AMT,
    SUM(DISTINCT S.EST_AMT) OVER (PARTITION BY S.PRODUCT_ID) AS EST_COST_TOT,
     SUM(PMT.LINE_AM PMT.PMT_AMT T) OVER (PARTITION BY OM.O_OMO_NO) AS TOTAL_PMT
FROM SALES S
    INNER JOIN INVOICE INV
        ON  S.PRODUCT_ID = INVS.PRODUCT_ID
    INNER JOIN PAYMENTS PMT
        ON INV.S.PRODUCT_ID = PMT.S.PRODUCT_ID
            AND INV.SEQ_NO = PMT.SEQ_NO
WHERE CAST(CREATE_DT AS DATE)>='01-JAN-2014'
    AND INV.STATUS_CD = 'CREATE' and SUBSTR(APPROVE_BY,1,3) IN ('PEP','SMI', 'MOP','3/4')
GROUP BY S.PRODUCT_ID
order by S.PRODUCT_ID

ERROR msg:ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:
*Action:
Error at Line: 1 Column: 91


texts: Transaction log dump - backup log from all database to one Dump file

texts:
I am working with some old jobs and I found this code snippet.
I am just wondering why would someone dump transaction log files from ALL databases in to one file TxnLogDump.bak?

Is this simply to dump(ignore) the transaction log and shrink?

IF  EXISTS (SELECT name FROM master.dbo.sysdevices WHERE name = N'TransactionLogDump')
EXEC master.dbo.sp_dropdevice @logicalname = N'TransactionLogDump'
GO

    EXEC master.dbo.sp_addumpdevice  @devtype = N'disk',
@logicalname = N'TransactionLogDump',
@physicalname = N'\\BackupServer\Backup_Drive\Temp\TxnLogDump.bak'
    GO

USE DB1
BACKUP LOG DB1 TO TransactionLogDump
dbcc shrinkfile ('DB1', 2)

USE DB2
BACKUP LOG DB2 TO TransactionLogDump
dbcc shrinkfile ('DB2', 2)

USE DB3
BACKUP LOG DB3TO TransactionLogDump
dbcc shrinkfile ('DB3', 2)

USE DB4
BACKUP LOG DB4TO TransactionLogDump
dbcc shrinkfile ('DB4', 2)

USE DB5
BACKUP LOG DB5TO TransactionLogDump
dbcc shrinkfile ('DB5', 2)


texts: Random Assignment of Employees to Locations Oracle SQL

texts:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -
Production
PL/SQL Release 11.2.0.1.0 - Production "CORE 11.2.0.1.0
Production"
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

I will base this question of the HR Schema for simplicity.
I have two tables Employees and Locations. For this example, I have 37 locations and 173 employees.

My locations have a specific number assigned indicating the number of employees that need to be assigned to the location.
What I am attempting to do is randomly assign the number of required employees for each location from the employees table.

As such an example of desired results would be:

EMPLOYEE_ID FIRST_NAME LAST_NAME STREET_ADDRESS CITY STATE
1              A        Smith     737 Any Road Anywhere USA
2              B         Jones     50 Some Road Nowhere USA
3              C         Butler   737 Any Road Anywhere USA
4              D         Thomas    50 Some Road Nowhere USA
5              E         Marsh    737 Any Road Anywhere USA
6              F         Ruppert   50 Some Road Nowhere USA
7              G         Glenn     50 Some Road Nowhere USA


texts: How to make a trigger that will compare input with a value of other table?

texts:
I had made two tables with the following fields:

TABLE 1: "Bookings"
  FIELD: "fk_flight_number"
  FIELD: "date_of_reservation"

TABLE 2: "Flights"
  FIELD: "flight_number"
  FIELD: "date_of_departure"

I want to add a condition, just for the consistency of my data. I want to check ON INSERT:

1) in Bookings - fk_flight_number must exists in Flights

2) date_of_reservation must be smaller (<) from date_of_departure.

How to do that? I have read about something similar and the keyword was TRIGGER. I think in my case it must be the same. Can you show me how to refer the second table? The logical conditions are not important - I can handle it. I need some snippet, because I don't know how to start writing the TRIGGER I need for. Or If you know some article describing similar problem - I will appreciate it too. Thank a lot. Of cource I'm not 100% sure that the best approach is TRIGGER. Any other advice will be appreciated too. One more time - thank you!


texts: Blue icon with question mark - what does it mean?

texts:
The SQL Server instance is accessible and seems to be fine.

  Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64)
  Mar  6 2017 14:18:16   Copyright (c) Microsoft Corporation  Enterprise
  Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build
  9600: ) (Hypervisor)

But what does the white question mark mean?

these icons dont go away when I refresh.
I am sysadmin inside sql server and outside I am administrator on that box.

Another thing I noticed, you can see on the picture below.
These are 2 different Management Studio sessions.

On the top one, I am logged in as myself, DBA and sysadmin,  on the second one
I use Management Studio with run as a different user and I use a domain account that I use for the replication, which is not sysadmin.

The second one has the blue icon in this and other servers as well, whilst mine is the normal green one.


texts: Concatenate text and tochar(): Why is there a space between the two?

texts:
I want to construct an ID from a textual prefix and a number (more info here).

For example, I want to concatenate ABC with 1, while padding the 1 to 6 digits: ABC000001

However, when I try this, it puts a space between the two:

SELECT
    'ABC' || to_char('1','000000') AS ID
FROM
    DUAL;

Result: ABC 000001

Why does it put a space in there?


texts:
I have 6 tables (t1, t2, t3, ...,t6 in the same SQL Server 2008 R2 database. I wanted to group them into two. Group 1 (TG1) includes tables 1, 2 and 3. Group 1 (TG1) includes tables 4, 5 and six.
I have created two queries each with union

SELECT TG1.*
  FROM (SELECT t1.PID AS ID, t1.FVALUE AS FVALUE, t1.TVALUE AS TVALUE
         FROM mydb.myuser.t1
         UNION
         SELECT t2.PID AS ID, t2.FVALUE AS FVALUE, t2.TVALUE AS TVALUE
         FROM mydb.myuser.t2
         UNION
         SELECT t3.PID AS ID, t3.FVALUE AS FVALUE, t3.TVALUE AS TVALUE
         FROM mydb.myuser.t3) AS TG1
SELECT TG2.*
  FROM (SELECT t4.PID AS PID, t4.VALUE AS VALUE, t4.VALUEID AS VALUEID, t4.VALUEDESC AS VALUEDESC
        FROM mydb.myuser.T4
        SELECT t4.PID AS PID, t4.VALUE AS VALUE, t4.VALUEID AS VALUEID, t4.VALUEDESC AS VALUEDESC
        FROM mydb.myuser.T4
        SELECT t4.PID AS PID, t4.VALUE AS VALUE, t4.VALUEID AS VALUEID, t4.VALUEDESC AS VALUEDESC
        FROM mydb.myuser.T4) AS TG2

I am getting the following two tables

The final query that I want to create is as shown in the screenshot below by taking all the values of TG1 and populate additional fields from TG2 that match the values of TG1.

The values in above screenshot were populated in Excel using vlookup and I need help to do the same in SQL query. I appreciate your help.


texts: How to get all tables in informix database and their sizes in GB?

texts:
I can get a crude estimate of table sizes with

SELECT tabname, (rowsize*nrows)/1024/1024/1024 AS tabsize
FROM informix.systables
ORDER BY tabsize DESC

Is there a way to get a more accurate measurement of the size of the tables?


texts: Attached database to SQL Server 2016 and now won't work in SQL Server 2014

texts:
I have a SQL Server 2014 database (on a portable hard drive) which I had to attach to a SQL Server 2016 machine using the normal attach method.

However, since attaching it to that computer, it no longer works on the SQL Server 2014 computer even though I didn't change the compatibility level or anything else for that matter.

I'm now stuck because SQL Server 2016 will not work on Windows 7 machines which is what all the other computers run on. I just happened to attach it on a laptop running Windows 10 and SQL 2016 thinking it wouldn't be a problem.

Is there any way to reverse this situation? I'm not able to get any good information from the error screen in management studio.


texts:
Consider this query which consists of N self-joins:

select
    t1.*
from [Table] as t1
join [Table] as t2 on
    t1.Id = t2.Id
-- ...
join [Table] as tN on
    t1.Id = tN.Id

It produces an execution plan with N clustered index scans and N-1 merge joins.

Honestly, I don't see any reasons to not optimize away all joins and do just one clustered index scan, i.e. optimize original query to this:

select
    t1.*
from [Table] as t1

Questions

Why joins aren't optimized away?
Is it mathematically incorrect to say that every join doesn't change result set?

Tested on:

Source Server Version : SQL Server 2014 (12.0.4213)
Source Database Engine Edition : Microsoft SQL Server Standard Edition
Source Database Engine Type : Standalone SQL Server
Compatibility level : SQL Server 2008 (100)

The query isn't meaningful; it just came to my mind and I'm curious about it now.

Here's the fiddle with table creation and 3 queries: with inner join's, with left join's and mixed. You can also look at execution plan there, too.

It seems that left joins are eliminated in the result execution plan while inner joins are not. Still don't get why, though.


texts: SQL Server Master Data Services database history by snapshots

texts:
I'm involved in a project where there is SQL Server 2012 Master Data Services used. There's a requirement to save a version of the database contents in certain point of time, say, every month or a quarter or so and possibly replacing previous versions on a yearly rhythm. That is, if a monthly history of database versions is used, there will be at most twelve different snapshots at any given time.

I'm curious and hence two questions:

Is this a preferred way of doing this?
Would there be a better way?

For what I've seen, from a developer perspective, snapshots are utilised for example for reporting purposes, but dropped soon after they are done. It seems they impact performance and perhaps make backups and restores more difficult.

<edit:

As an answer to the database size question: some hundred megabytes. But that's a very rough guess. Certainly at least on hundred.

I've now learned the snapshots are in fact a plan to store MDS history and possibly retained indefinitely -- or  until someone explicitly deletes the oldest ones. I don't know the frequency they are taken. At least four times a year, possibly more often and seemingly retained until someone decides to delete the oldest ones. The rationale is to store MDS history as "untampered" read-only snapshots, of which at least a few previous quarterly snapshots are actively queried (but not altered). There is a plan to publish the MDS periodically -- every ten minutes or so -- the MDS to a special purpose "publish database", which may be the place the snapshots are taken (I'm not sure, but certainly there ought to be awfully lot of snapshots then to cover all the published changes).

This doesn't feel the right way to work with MDS. This looks like the slowly changing dimension problem and it looks like the (great) majority of data does not change. What I know, or believe is that there are nightly bulk loads, but mostly the data loaded should be the same as it has been, and changes are mostly new items plus some small modifications and some additional attributes maintained purely by the data stewards. Then looking Technet regarding Database snapshots it looks me it's difficult to run the system efficiently (e.g. I/O) and reliably (backups and restores) in presence of multiple snapshots, which do carry an operational and contractual purpose in this case.

Maybe I shouldn't fret or this. This doesn't even look like I have any real questions here anymore than

Would it be normal to version database data in snapshots? Be it an operational database or a MDS one.


texts:
How is the comparison between dates (with times) actually implemented?

Consider the following query for example:

SELECT *
FROM dbo.Table1
WHERE DateTimeValue >= '2016-01-01 00:00:000'
  AND DateTimeValue < '2016-01-03 00:00:000'

The above internal contains exact two days, no millisecond more or less.

So, how does SQL Server compare the values in the column with the values in the interval?

String comparison?

This seems not so bad, but if you see the execution plan, you'll notice that the SQL Server is using CONVERT_IMPLICIT to cast the string value to datetime.

Integer comparison?

This sounds ridiculous if you think how many milliseconds there are in one day.

Does anyone know how the comparison is implemented internally in SQL server ?


texts: How to efficiently extract data from a csv file and save it in mysql

texts:
I have a csv file, maybe 1000 or more rows, and echo row’s data is related to two table in mysql. Example,

table `book`(id, title, …)
table `scene`(id, book_id, short_content, position…)

And the csv file look like:

title,short_content1,position1,short_content2,position2,…
...

I want to extract data from it and save it to mysql. Also in the mysql maybe already have the same record, so I don’t want save the same data to mysql.
 My solution is:

First, fetch all the data related to the file, a big data set labeled BS.
Second, for each row in file compare to the BS and create the book, and store the book_id for his scene.
Finally, batch create the scene.

This is my poor solution, and if the csv file has 1000 rows, then the solution will take a large memory space and 1000 + 1 times mysql calls.

I see Bulk database update/insert from CSV file and Update Oracle database from CSV post. But I still confuse how to deal with csv file which related to multiple tables.

Can anyone give me a better solution.
 Thanks.


texts: SQLServer dependency graph and performance

texts:
I have a large stored procedure running on SQLServer2008 to produce a report on the previous day's data. It rehashes data from 12 tables, including a lot of ORDER by date and CASTS. Among others, it SELECTs a few columns from table S, using a LEFT OUTER JOIN S.

The time for execution changed from 3 hours to 12 hours when I (accidentally) dropped an index on an apparently unrelated table SA.

I checked the dependencies and SSMS shows something like this:

spReport

TABLE A

TABLE S

spSA1
spSA2

SSMS tells me that there is dependency on table S, that, in turn, depends on two stored procedures, that (I know) happen to use table SA. The dependency graph stops at the stored procedures, and does not show table SA.

Table S has a TRIGGER AFTER UPDATE that calls these stored procedures on table SA.

But the top level stored procedure only SELECTS columns from table S, it does not UPDATE anything. The trigger should never run during its execution.

Is the dependecy graph correct or is it just ignoring the AFTER UPDATE?

Should I be looking elsewhere to understand the link between performance drop and index on SA?

Could the TRIGGERS without the index on SA be running slowly in the background, locking up tables and disrupting everything else?


texts: Should I convert timezones when moving data to a report server/data warehouse

texts: I'm writing an SSIS Package to take data from a production DB to a reports server/data warehouse. The other databases on my reports server are all in Mountain Time but this DB is in UTC should I convert the data as I'm importing it with SSIS, or should I leave the data as is and just keep track of what time zone the server data is in and deal with it in my queries? Is there a reason not to convert the data as I copy it over?


texts: Insert PBKDF2 hash into PostgreSQL container

texts:
I am using the PostgreSQL docker image with the following Dockerfile:

FROM postgres:10.1

COPY init-db.sh /docker-entrypoint-initdb.d/init-db.sh

and an init-db.sh script with the following content:

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
    CREATE DATABASE foodb;

    \c foodb;
    DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id SERIAL,
    username VARCHAR(25) NOT NULL,
    pw VARCHAR(128) NOT NULL,
    super INT NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
  );

CREATE UNIQUE INDEX users_username ON users (username);

INSERT INTO users (username, pw)
VALUES ('jjolie',
        'PBKDF2$sha256$901$x8mf3JIFTUFU9C23$ic9J6mFDSz0rETKux/Co9TzCPOX50j+V8Vky5GkY5v0=');

After building the container (with the init-db script included) with docker build . --tag postgres, when the container is started with docker run postgres, and the users table is viewed:

docker exec -it postgres bash
psql -U postgres
\c foodb
select * from users;

We see the following output:

 id | username |                   pw                    | super
----+----------+-----------------------------------------+-------
  1 | jjolie   | PBKDF201/Co9TzCPOX50j+V8Vky5GkY5v0=     |     0

PBKDF201/Co9TzCPOX50j+V8Vky5GkY5v0= differs from PBKDF2$sha256$901$x8mf3JIFTUFU9C23$ic9J6mFDSz0rETKux/Co9TzCPOX50j+V8Vky5GkY5v0='.

Why did the password change from PBKDF2$... to PBKDF201..? How can it be prevented?


texts: Can't "Script Database As -> CREATE TO" with SSMS 2008R2 and 2016

texts:
I created a SQL Login (Login01), mapped to DB user (User01), member of db_owner database role.
When connected locally with this user in SSMS, i try to script out the database (Script Database As -> CREATE TO) and it works as expected.
When connected from a remote machine (same domain, same LAN), it fails (Could not read metadata, possibly due to insuficient access rights). Only way i found to permit it is to grant "View any definition" to the Login.

I dont understand why it only fails when connecting remotely, is this an expected behaviour?

Edit: As said in comments, the issue here has nothing to do with local/remote connection but is related to the SSMS version. I edited title also so the question/answer is easier to find.


texts: how to revoke backup and restore permission from database owner

texts:
I have SQL server 2008R2.

I  grant every user who wants to work on the database the database owner privileges, but recently found out that there is a database backup spam on the server, so I thought of revoking the backup and restore permission from the database owner, please advise.


texts: Merge replication sync fails after ALTER TABLE... NOT NULL

texts:
We have a SQL Server 2005 database that uses merge replication to sync to SQL Compact Edition subscriptions. We added a column to a table using:

ALTER TABLE Shifts ADD COLUMN StateOrProvince NOT NULL DEFAULT '  '

The SQL CE subscriptions will not sync, giving this error:

Alter table only allows columns to be added which can contain null values.

We figured out that the MSDN docs are in error, and that we should have used sp_repladdcolumn. But now we are trying to fix the sync problem. We have reinitialized all subscriptions and generated a new snapshot, but we still get the same sync error.

What are we missing?


texts:
I am reading a book about database systems and in the chapter covering database design using ER, the author aims to explain when to use aggregation versus ternary relationship. In short, each Project entity is sponsored by one or more Department entities and each Department can sponsor zero, one or more Projects. Each Sponsorship relationship has a Monitors relationship, which connects Employees with Sponsorship.

This can be expressed in 2 ER diagrams:

Now, we want to express an additional constraint that each Sponsorship relationship is monitored by at most one Employee.The author states that this cannot be done with ternary relationship. I can't wrap my head around why. To express this in diagrams, I would just point an arrow from Sponsors2 to Emplyees in ternary relation. Does this not satisfy the additional constraint?


texts:
We have an ongoing issue with our production sql server (physical) where we randomly receive this error in the log which puts the database into a recovery state

 SQLServerLogMgr::LogWriter: Operating system error 1117(The request could not be performed because of an I/O device error.) encountered.

The issue always occurs on our drive storing the transaction logs. The database normally recovers on its own, but few instances it does not and we need to restart the instance to recover. No errors returned from any database for dbcc checkdb.

Our storage team has been investigating it for weeks with our vendor, but with no luck. The investigation is on-going.

That being said, how should the sql server dba handle this error aside from reporting it to the storage team and checking for database corruption? I am wondering if there is any more information I may be able to gather from the sql server side which may help in their investigation?

Running SQL Server 2012 SP3, storage is a SAN.

First Update

Our infrastructure team made the following changes last night

Updated the firmware on all NICs on the database server
Updated the firmware on the network switches
Enabled Jumbo frames for ICSCI

We haven't received the error yet, I'll update again in a week or so.

Second Update

The changes made in previous update did not resolve the issue. Last night we moved tempdb from the SAN to local drive on the physical server and we disabled iSCSI optimization connection tracking. We haven't received the error yet, and we also see much faster disk read/writes to our data and log drives (still on the san) and of course tempdb being local. Additionally, we were receiving many iSCSI errors in the windows event logs during the time of error and also throughout the day. Since these changes last night those iCSI errors are mostly gone, there are still some coming in but not nearly as much.

Thanks,
Kevin


texts: Upgrade with DistributedAG vs adding a node with SQL Server 2019

texts:
We would like to migrate from SQL Server 2016 to SQL Server 2019.   The databases are part of an Availability Group.
I added a new replica using SQL Server 2019 on a new node and made that replica part of the AG and then failed over successfully to it.
Would a Distributed AG also provide me the same functionality?  What is the advantage of using a Distributed AG versus using the above method to migrate to SQL Server 2019?.
I'm trying to understand if the procedure I followed is correct and supported?  In what way is a Distributed AG useful?


texts: How can I reduce OLEDB wait type in a SSAS linked server connection?

texts:
I have recently created a linked server to one of my SSAS servers.

when I go ahead and open the catalogs, to see which ssas databases I have there,
I use the following procedure:

create procedure sys.sp_catalogs
(
    @server_name        sysname
)
as
    select
        CATALOG_NAME    = f_rc.CATALOG_NAME,
        DESCRIPTION     = convert (nvarchar(255), f_rc.DESCRIPTION)
    from
        sys.fn_remote_catalogs (@server_name, NULL) f_rc
    order by CATALOG_NAME

this is how I call it:

sys.sp_catalogs 'sasbidev01'

When I see it takes too long, I check what it is running:

I see the OLEDB wait type.

Is this on its own, indication that I could do something to improve the speed of this connection?

The Linked server script creation:

USE [master]
GO

IF NOT EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'SASBIDEV01')
BEGIN

EXEC master.dbo.sp_addlinkedserver
  @server = N'SASBIDEV01'
, @srvproduct=N''
, @provider=N'MSOLAP'
, @datasrc=N'SASBIDEV01'

EXEC master.dbo.sp_addlinkedsrvlogin
 @rmtsrvname=N'SASBIDEV01'
,@useself=N'False'
,@locallogin=NULL
,@rmtuser=N'mycompany.CO.UK\SASBIDEV01_SSAS'
,@rmtpassword='B4l4r4m4__sbidev01'
END
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'rpc', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO


texts: Is this Dedicated Server good enough to Run MySQL on Linux Ubuntu?

texts:
I have a web application that runs simple logic. Most of the SQL procedures that I have
only do simple selects, some simple join table and some simple inserts. I want to get
a dedicated server for My Database, I found one but I didn't get it yet. I want to get
some of your opinion on this machine before paying for it. I know I can benchmark it
but only after I own it. So any idea if this one is enough for 5000 users per day doing
simple reads and simple insertion into MySQL with InnoDB Engine ?

Core i5 Quad Core i5-2400 3.10 GHz
16 GB DDR3-1333 UDIMM
250 GB SATA HDD (7200 RPM)
100 Mbps Uplink Port Speed
Ubuntu 10.04 - 64 Bit


texts: Find "n" consecutive free numbers from table

texts:
I have some table with numbers like this (status is either FREE or ASSIGNED)

id_set  number  status
-----------------------
1       000001  ASSIGNED
1       000002  FREE
1       000003  ASSIGNED
1       000004  FREE
1       000005  FREE
1       000006  ASSIGNED
1       000007  ASSIGNED
1       000008  FREE
1       000009  FREE
1       000010  FREE
1       000011  ASSIGNED
1       000012  ASSIGNED
1       000013  ASSIGNED
1       000014  FREE
1       000015  ASSIGNED

and I need to find "n" consecutive numbers, so for n = 3, query would return

1       000008  FREE
1       000009  FREE
1       000010  FREE

It should return only first possible group of each id_set (in fact, it would be executed only for id_set per query)

I was checking WINDOW functions, tried some queries like COUNT(id_number) OVER (PARTITION BY id_set ROWS UNBOUNDED PRECEDING), but that's all I got :) I couldn't think of logic, how to do that in Postgres.

I was thinking about creating virtual column using WINDOW functions counting preceding rows for every number where status = 'FREE', then select first number, where count is equal to my "n" number.

Or maybe group numbers by status, but only from one ASSIGNED to another ASSIGNED and select only groups containing at least "n" numbers

EDIT

I found this query (and changed it a little bit)

WITH q AS
(
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY id_set, status ORDER BY number) AS rnd,
         ROW_NUMBER() OVER (PARTITION BY id_set ORDER BY number) AS rn
  FROM numbers
)
SELECT id_set,
       MIN(number) AS first_number,
       MAX(number) AS last_number,
       status,
       COUNT(number) AS numbers_count
FROM q
GROUP BY id_set,
         rnd - rn,
         status
ORDER BY
     first_number

which produces groups of FREE/ASSIGNED numbers, but I would like to have all numbers from only first group which meets the condition

SQL Fiddle


texts: Incremental (not differential) backup of SQL Server 2008 R2

texts:
Is it possible (out of the box or free software) to make an INCREMENTAL backup of SQL Server 2008.

My problem is, the full backup and differential backup, backs up my 30gig database to 1 file. So when I run my incremental backup to the cloud, if the 30gig database file has a change, it will upload the entire 30gig.

Ideally, I'd like to be able to make an initial full backup to 1 file, then every night make differential backups to A NEW FILE (or a new file once a month) so that the backup doesnt re-upload the entire file every night.


texts: Is it possible to change the color of relationship lines in MySQL Workbench diagrams?

texts:
I have MySQL Workbench Community Edition 8.0.15 installed on my Mac OS 10.14.3. I'm currently working on making an ERD with Workbench's EER Diagram.

My problem is that whenever I export my diagram, the lines showing the relationships between tables are not visible because the background is white, and the lines are white.

It is visible on the editor because the background is black in workbench, but not when exported.

Is there any way to change the color of these lines? Or at least the background color of the diagram when it is exported so that the lines would at least be visible?


texts: How we can enable `index_condition_pushdown`?


texts:
I'm an application developer (full stack, I'm not shy of SQL, actually prefer it to some ORM solutions).

The database architect (that has no coding role) for the project I'm working on is adamant that I should re-use an existing structure that was not designed in mind with the set of requirements that I have. I've looked at the impacts of the said design and I could see impact on existing code base because of assumptions that were made in the code based on the initial design. I would like to use a separate structure (similar but not identical) that would support my code.

What reasons do you think my database architect has? (Because I've only got the "there is a table there - use it" statement which doesn't make for me).

I've tried to approach the support of my design based on:

It's not inferior to his option from a technical point of view.
It's safer from a code base perspective.
It's less coupled for future enhancements.
It's faster when the requirement conditions are met (millions of rows).

If it's any help:

The existing structure is called Queue. You can see where this could be an issue: a lot of things can be modeled as queues.
When it should matter my structure would hold millions of rows which will skew any indexes and potentially slow down the actual queues recorded there.
Multiple types of queues exist in the system now with different setups (but when tried to setup mine I've hit issues in the existing code base hence my concern).

You could say that is a case of "normalization" but the data itself is not repeated, there is no report that will run across the entire set and if used it will require to use some discriminators to differentiate my stuff from the normal queues.

I guess, at the end of the day, the questions I don't have an answer for are:

What are the compelling reasons to re-use a set of tables when the information you want to retain in them is not overlapping? (The database architect was not able to respond to this).
What's the (importance/cost/risk) of having a similar cloned structure?


texts: How to install pgcrypto extension in postgres 10 on CentOS 7

texts:
I'm in the process of creating a postgres database for production in CentOS 7. So I already installed (yum install postgresql10-server postgresql10 after adding the repos of course) and configured postgres 10. However, in my scripts I need to install pgcrypto extension and I haven't successfully install it. This is what I've done so far:

the first error I got was saying that the /usr/pgsql-10/share/extension/pgcrypto.control files does not exist. Googling I realized that I have to install postgres-contrib package, which I did and then restarted postgres service, but the error continues due to the fact that the extensions were installed into /usr/share/pgsql/extension, so I copied the extension files from they were installed to they were expected and then
appears this message

  "ERROR:  could not access file "$libdir/pgcrypto": No such file or directory"

Googling again I found that maybe I need to give another option, so I ran CREATE EXTENSION pgcrypto FROM unpackaged; then
the error message now is

  ERROR:  function digest(text, text) does not exist

And I'm stuck and without any idea what to do next. Is anybody else using this extension in postgres 10 on Linux?, if so, how did you create the extension?

Version info: PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit


texts: Having a backup of the master key, how can I restore a database that was encrypted by master key?

texts:
while setting up AlwaysOn - Availability Group on a test system,
I generated all the restores from live and restored them to this new server.

then I backup all these databases to a network share to make them ready for being added to the availability group.

when then adding then to the availability group I get this message below, for a couple of them. (2 or 3 databases out of 50)

Bad news:

I don't have the master key for that live server - the server where the databases come from.

Good news.

I have a backup of the master key in the original live server.

I also have backups of certificates:

Not so good news:

I haven't got the decryption password for that master key backup file

This question seems to be related:

What do I need to restore an encrypted MSSQL database?

Question:

How can I restore those databases only having a backup of the master key in the original server?

P.S.
I don't have the decryption password for that master key backup file - above pic

Maybe I will have to regenerate the master key.


texts: Encounter deadlock even when there is no relation between tables

texts:
I've been studying about deadlock and it's different causes .
I found an example regarding this issue but I could not understand the cause of the deadlock exactly because two DIFFERENT tables are involved and what I have read so far was when only one table is involved !!

First I run This Query in Connection 1 :

Begin Tran

 Update  Purchasing.PurchaseOrderHeader
 Set Freight = Freight * 0.9
 Where PurchaseOrderID = 1255;

Again I run this query in connection 2 :

update Purchasing.PurchaseOrderDetail
Set OrderQty = 4
Where ProductID = 448 and
      PurchaseOrderID = 1255

and back in connection 1 I run this query  (which leads to the deadlock):

Begin Transaction

             Update Purchasing.PurchaseOrderDetail
             Set OrderQty = 2
             where ProductID = 448 and
                   PurchaseOrderID = 1255

As you can see , there are two tables involved in the transaction , So what exactly causes the deadlock??
Thanks in advance


texts: Storing timeseries data with dynamic number of columns and rows to a suitable database

texts:
I have a timeseries pandas dataframe which dynamically increases the columns every minute as well as adds a new row:
Initial:
timestamp                100     200     300
2020-11-01 12:00:00       4       3       5

Next minute:
timestamp                100     200     300   500
2020-11-01 12:00:00       4       3       5     0
2020-11-01 12:01:00      14       3       5     4

The dataframe has these updated values and so on every minute.
so ideally, I want to design a database solution that supports such a dynamic column structure. The number of columns could grow to over 20-30k+ and since it's one minute timeseries, it will have 500k+ rows per year.
I've read that relational db's have a limit on the number of columns so that might not work here, but also, since I am setting the data for new columns and assigning a default value(0) to previous timestamps, I lose out on the DEFAULT param that's there on MySQL.
Eventually, I will be querying data for 1 day, 1 month to get the data for the columns and their values.
Please suggest a suitable database solution for this type of dynamic row and column data.


texts: How to convert innodb engine tables to tokudb engine

texts: Hi I want to migarate my server newly installed tokudb engine. i want to convert each table of database from innodb to tokudb. How can I do that using sed command ?


texts:
I'm trying to upload my database to the heroku mongodbLab addon. My MongoDB version is 3.2.4 and my servers is 3.0.9. Mongoose is 4.4.6. However, when I run the command $ mongorestore -h myhostname -d mydatabase -u username -p password ~/tmp/mongodump/Loc8r to push the db to heroku I get the error:

building a list of collections to restore from /Users/user/tmp/mongodump/Loc8r dir
reading metadata for database.locations from /Users/user/tmp/mongodump/Loc8r/locations.metadata.json
restoring database.locations from /Users/user/tmp/mongodump/Loc8r/locations.bson
restoring indexes for collection database.locations from metadata
Failed: database.locations: error creating indexes for database.locations: createIndex error: exception: unsupported geo index version { 2dsphereIndexVersion : 2dsphereIndexVersion: 3 }, only support versions: [1,2]

How can I change the 2dsphereIndexVersion in my schema?

Here's my schema file for my db:

var mongoose = require('mongoose');

var openingTimesSchema = new mongoose.Schema({
  days: {type: String, required: true},
  opening: String,
  closing: String,
  closed: {type: Boolean, required: true}
});

var reviewSchema = new mongoose.Schema({
  author: String,
  rating: {type: Number, required: true, min: 0, max: 5},
  reviewText: String,
  createdOn: {type: Date, "default": Date.now}
});

var locationSchema = new mongoose.Schema({
  name: {type:String, required: true},
  address: String,
  rating: {type: Number, "default": 0, min: 0, max: 5},
  facilities: [String],
  coords: {type: [Number], index: '2dsphere', "2dsphereIndexVersion": 2},
  openingTimes: [openingTimesSchema],
  reviews: [reviewSchema]
});

mongoose.model('Location', locationSchema);

As you can see I tried to change it by adding that attribute "2dsphereIndexVersion": 2. However, that didn't work. Anyone know how I can change this?

-EDIT-
I'm using mongoose to create the database with the schema above. I am trying to set the "2dsphereIndexVersion" to 2 because it's required by my servers version of MongoDB.


texts: Why does mysqldump require drop command?

texts:
I am trying to backup a large table and after reading various other answers on this site have tried

mysqldump --user=user --password=password dbname1 tablename | mysql --user=user --password=password dbname2

I get the error

DROP command was denied to user 'user'@'localhost' for table tablename

which scares me. This is the first time I've worked with such a large DB and thus needed to use mysqldump, so I don't know what's going on. Why would it need the DROP command? I want to basically copy the table into another database, not modify (or delete) the existing one in any way.


texts: How I can find out which views use other views in their sql code that generates them?

texts:
I am mapping the view usage on an uknown database scheme. I was provided with a list with some views:
view_a
view_b
view_c

And I want to search my database which views depend on another views for example if view_c is generated via the following sql query:
CREATE VIEW view_c AS SELECT * from view_a JOIN view_b WHERE view_a.column=344

I want somehow to list that view_c uses view_a and view_b upon excell sheet. For now is done manually via looking the views dll sql on Dbeaver and figuring out from it. But it is very laborious job and may miss some view dependencies as well, so do you have any idea how If I can generate the excell sheet via a query from information_schema.* tables?


texts:
here at my company, we use an RD Gateway server.
that means when we want to access a server we have to set up the gateway in the connection...something like rdsgw.mycompany.com

But, how could I do an ODBC connection with my SQL Server if I have to use the gateway?


texts:
Checking my SQL Server log I see several entries like this:

Date: 08-11-2011 11:40:42
Source: Logon
Message: Login failed for user 'sa'. Reason: Password did not match for the login provided. [CLIENT: 56.60.156.50]
Date: 08-11-2011 11:40:42
Source: Logon
Message: Error: 18456. Severity: 14. State: 8.

Date: 08-11-2011 11:40:41
Source: Logon
Message: Login failed for user 'sa'. Reason: Password did not match for the login provided. [CLIENT: 56.60.156.50]
Date: 08-11-2011 11:40:41
Source: Logon
Message: Error: 18456. Severity: 14. State: 8.

And so on..
Is this a possible attack on my SQL Server from the Chinese???!
I looked up the IP address at ip-lookup.net which stated it was Chinese.

And what to do?

Block the IP adress in the firewall?
Delete the user sa?

And how do I protect my web server the best?!

Thanks in advance!


texts: Is manually partitioning as one table per document evil?

texts:
I wish to use postgresql to store 10k+ independent "documents" each with 10k+ rows and growing. They have the same schema. For active documents I am expecting:

frequent inserts and updates
regular fetch of a complete document
rare deletion
no querying across documents (assume full-text search is handled elsewhere).

The standard way would be to create a single table with a doc_id but I assume so many rows would be a performance nightmare i.e. both #1 and #2 will slow down with size of the table yet I'm not getting any benefit out of it being a single table.

I believe I would get better performance and admin flexibility (e.g. perform piecemeal schema upgrades, perform pg_reorg, future sharding etc.) by creating one table per document. I can't see any benefit from using a master table and setting up triggers.

Is this acceptable, a terrible anti-pattern or is there another way I should be tackling this?


texts: Transfer from 3rd party IAAS cloud provider to Azure SQL

texts:
I'm being asked to define how we're going to migrate a database from a 3rd party cloud provider, which has a 12Gb DB running on a VM, with SQL 2008 R2, to Azure SQL.

And down time needs to be under an hour. We have a 1Mbit line only, so moving 12Gb, is around 30 hours. I have a few months to go live.

The plan I'm looking at is:

Create a VM in Azure, with SQL Server 2016 on it.
Create a replication setup, with the current live DB as a publisher, and my new DB as a subscriber.
(Not sure how I'll go with ports and permissions - hopefully it's possible. We have full control over both the source and destination).
Leave the system replicating for a few days. I think the initial snapshot would take a couple of days to sync up, and then, subsequent changes would be quite quick. After a few days, the destination DB will be in sync.

Once the DBs ae in sync... propose a go-live data.

On go-live:

backup the subscriber DB
restore it to an Azure SQL instance. (Not sure how that's done, but hopefully possible)
Setup roles/permissions.

Change the application connection to use the new database.

Would this work?
Are there any things to know about replication? Would the snapshot generation lock the current live production DB?
Would mirroring be a better option? Not sure the option is available. Source is SQL Server 2008R2.


texts: SQLCMDMODE from SSMS. Can I set -b mode when using :r to include files?

texts:
As far as I read msdn there is no variable that corresponds to the -b flag of the command (on error batch abort).
I found no to enable this mode inside a script when running in sqlcmd mode.
Is there any to force
:r filename
to run in this mode, when called from SSMS?


texts: WHERE Clause Compare VarChar(n) Column = VarChar(MAX) Variable Results in Inequality Comparison and Poor Query Performance

texts:
Earlier today I was working on a Reporting Stored Procedure which had poor performance.  I was able to fix the issue by changing a static variable which held a string of ten characters or less from VARCHAR(MAX) to VARCHAR(10).  I am looking to understand why that would have made a difference.
Additional Details If Needed
The Original query is much bigger than what needs to be looked at but I saw the below things happening, when joining the foundational table to a Key-Value Pair table we were returning a ton more rows than was actually needed (in some instances over 14 billion rows for a final output of ~60,000 rows) and we had a few extra operations (particularly the Lazy Table Spool).  Image of that part of the Query Plan is below:

I could see a weird part of the execution plan was how the @ProgramID variable was being handled.  This variable is used as [Column] = @ProgramID and the Column is part of the tables Non-Clustered Index and is a VarChar(10).  But we are still doing an Inequality Search instead of an Equality Search.

After modifying this parameter/variable from VarChar(Max) to VarChar(10).  The execution time dropped from several hours to about 30 seconds and the plan had a very different structure.  Looking at a similar snippit from the execution plan, we were not returning nearly as many rows, we don't have the extra operations and we were doing an equality search on [Column] = @ProgramID (again Column is part of the tables Non-Clustered Index and is a VarChar(10)).

Below is a simplified query which shows some of this behavior (we don't get all the extra steps, but the Seek Predicate difference is still there): (Paste The Plan)
DECLARE @CompanyID VarChar(10) = 'RxCRoads'
        ,@ClientID VarCHar(10) = 'Amgen'
        ,@ProgramID VarChar(MAX) = 'Foundation'
        ,@StartDate DATETIME = '2020-01-01'
        ,@EndDate DATETIME = '2020-12-07'
        ,@RxOnly INT = 9

SELECT PC.CompanyID,
PC.ClientID,
PC.ProgramID,
PC.PatientID,
PC.CaseID,
RxOnly.[Value] AS RxOnly
FROM PATIENTCASES PC
    LEFT OUTER JOIN PatientCaseDetail RxOnly
        ON RxOnly.CompanyId = PC.CompanyID
        AND RxOnly.ClientId = PC.ClientID
        AND RxOnly.ProgramId = PC.ProgramID
        AND RxOnly.PatientID = PC.PatientID
        AND RxOnly.CaseID = PC.CaseID
        AND RxOnly.PatientCaseAdditionalElementId = @RxOnly
WHERE PC.CompanyID = @CompanyID
AND PC.ClientID = @ClientID
AND PC.ProgramID = @ProgramID
AND PC.CaseCreateDateTime >= @StartDate
AND PC.CaseCreateDateTime < @EndDate

Just changing the @ProgramID VarChar(MAX) = 'Foundation') to @ProgramID VarChar(10) = 'Foundation' returns a more streamline plan and better performance (even though not really noticeable in this particular query). (Paste The Plan)
Is there any particular reason why this change in the size of the data type would result in the change in the execution plan?  Running the same query with @ProgarmId VarChar(8000) instead of @Program VarChar(10) has no noticeable difference from @Program VarChar(10). (Paste The Plan)
My guess is that VarChar(MAX) has different comparison rules due to its size, and this is just a state of what VarChar(MAX) is.  But I didn't know if anyone had a better/more technical answer than I did so I can better apply this lesson in the future.


texts: Spotting foreign key relations in a database that doesn't define all relationships as constraints?

texts:
At work I use a rather old MS Access database which defines some relations between tables, but does not outright define all of them.  If the database application used an ORM, I would think that this might have something to do with reading the source code, which I do not have access to for the app.

The relationships not being defined may be a normal thing in a database design or simple FUD to keep people from being able to use the database themselves.

The problem I'm trying to overcome right now is merging two duplicated employee records together, and ensuring that the two records bring everything with them to the correct record.

My first instinct was to look at the predefined One-to-Many Relations between the employees table (1) and the other tables (M), and this gave me a nice list of relationships, but not all of them...

So I turned to VBA's ADO to search just the tables (no views or linked tables) for field names with the same data type as the primary key of the employees table, and certain keywords in the fieldname compared using lowercase letters to come up with a list of possible relations between tables. (I used the words emp, care, and socialsecuritynumber (no, it's the employee number not the actual ssn))

From there I am narrowing it down by running INNER JOINs to see if there are any matching records.

Is there anything else I could be doing to find pesky fields that might be hiding foreign keys that may be linked to the primary key of the employees table before I merge the records?


texts: Should a SQL Server TempDB space be allowed to be reclaimed?

texts:
If I don't have space concerns at the moment, should I size my TempDB as a large space initially and not let it's space be reclaimed?

What's the best way determine what the optimal initial size for my TempDB should be?

My TempDB is currently 12gb and it's set to 10% autogrowth


texts: How to do a ranked search based on number of multiple columns matched

texts:
Context
I am trying to create a ranked-customer-search that will order results based on "most likely correct". We have several factors we search by, but to keep it simple I will stick with just name, phone number, & email. The goal is that if the customer has an existing account, we use that instead of creating a new account.
It is also worth noting that for this system, a customer account is US state-specific. So it is technically possible for a single person to have 49 existing accounts and still need to have a "new account" created, so there are often many duplicate accounts we can copy information from.
My Attempt
The query below uses binary values to determine the rank of a result. So a match based on a phone number (0100) scores higher than a match based on name (0001) or email (0010). This works pretty well but isn't quite ideal. For example, an account that matches both email & name will rank lower than an account that matches only a phone number. Unfortunately, this is pretty much my limit when it comes to creating queries. (see SQL Fiddle)
(The UNIONs below are required because they allow better indexes to be used)
SELECT
    results.id,
    SUM(results.score) AS total_score
FROM (
    (
        SELECT
            c1.id,
            4 AS score
        FROM customers c1
        WHERE
            c1.phone = :phone
    ) UNION ALL (
        SELECT
            c2.id,
            2 AS score
        FROM customers c2
        WHERE
            c2.email = :email
    ) UNION ALL (
        SELECT
            c3.id,
            1 AS score
        FROM customers c3
        WHERE
            c3.first_name = :first_name
            AND c3.last_name = :last_name
    )
) results
GROUP BY
    results.id
ORDER BY
    total_score DESC
LIMIT 10;

My Question
I am not sure how to change it so that matching multiple less-important factors ranks higher than a single important factor?
Also, since this is my first time creating a search query like this, there is likely a better and/or more standard way of doing this; so any resources you can share related to this would also be greatly appreciated!

Example of the current result set:

first_name
last_name
phone
email

john
doe
5556667777
johnd@example.com

john
doe
5556667777

5556667777

john
doe

johnd@example.com

Example of the desired result set: (last 2 rows are switched because the last 1 had more matching data)

first_name
last_name
phone
email

john
doe
5556667777
johnd@example.com

john
doe
5556667777

john
doe

johnd@example.com

5556667777


texts: Can I retrieve all database objects owned by a particular user?

texts: We have a user who is leaving and I need to know every database object that he owns. Is there a query that will provide this information?


texts: How to pass a column name using string_split - ms sql server?

texts:
I'm trying to pass column names using a string split because I need the values from that table to join with another table.

I've already achieved this in Postgresql where I get the values from these columns:

   SELECT unnest(array["column_name1","column_name2","column_name3" AS "amount"
   FROM table1

The unnest in combination with the quotes around the column names returns an array to a set of row for every value.

Amount
307579.00
25579.00
212871.00

Now I want to do the same in MS Server:

   DECLARE @amount NVARCHAR(50) = 'column_name1/column_name2/column_name3'
   SELECT "value" from string_split(@amount,'/')

But this only gives me the literal string:

Amount
column_name1
column_name2
column_name3

I've been trying and searching how to pass an elemant that will read as a column name and shows the values in one column like the first example, maybe I shouldn't use the string splitter etc. Kinda stuck on this..


texts: Fact table design - Level or contribution measures

texts:
I am trying to come up with a dimensional model for the following:

The source data comes from game machines as meter messages. Basically, the game machine accepts payments and sends a snapshot of the current values of several measures. The message contains over 15 meter types such as the coin-in level (in cents), games played level, games won, games lost and so on. This message is captured on a daily basis. Because of the fact that the snapshots of numeric levels are taken on a daily basis, I thought that we should use a periodic snapshot fact table.

Would it be better to store the levels of the numeric values as measures (ex. the total coin-in value and total games played of the machine as of the snapshot) or should we store the contribution amount for the snapshot period (ex $100 was deposited and 10 games were played in the one day ). Or maybe both levels and contributions (levels would not be additive across the date dimension but contributions would be)??
What is the difference in terms of analysis.

We have not started the requirements gathering yet but it seems that there are a lot requests on trending the coin-in data for example by month per machine.

Thanks,


texts:
Using sp_blitzindex from Brent Ozar, I found this particular problem.

The table(s) - (there are many of them, about 80, per business object) - are simple:

User id
Primary key of business object (if PK is composite, there are several columns)

During a particular task, all records of a given user are DELETED and new records are inserted.

delete from OrderTrackingTable where USR_ID = @UsrID

The delete command is created as dynamic SQL inside a stored procedure and executed.

This leads to masive lock wait times on those tables and timeouts.

Has anyone encountered similar problems and anyone has idea for solutions?

sp_blitzindex reports:

  dba.OrderTrackingTable.PK__OrderTrackingTable__BBFBFF17660BA3C2 (1): Page lock waits: 43; total duration: 42 minutes; avg duration: 58 seconds; Lock escalation attempts: 252,312; Actual Escalations: 6.
  Reads: 2,000 (1,960 seek 40 scan) Writes:1,960

DML:

 create table OrderTrackingTable (
    USR_ID      smallint,
    ORDER_ID    int,
    ORDER_ID2   int,
    PRIMARY KEY ( [USR_ID], [ORDER_ID], [ORDER_ID2] )
    )

EDIT

about 2 000 000 records are deleted and inserted


texts: several idle connections SET bytea_output

texts:
i have a situation that i don't know if it is normal, i think not.

I have several idle connections with the query SET bytea_output='escape', so i arrive to max_connections and have issues with my applications, why are there so many connections idle with that query?


texts: What does it means when you have "sql_exit_invoked" in System Health?

texts:
I'm having problems with one of my SQL Server 2016 Standard servers. I have 8 production servers and this one is the only one who crashes randomly without having any trace in the log.

I have system_health enabled on it. I noticed that I have a row in system health witch is "sql_exit_invoked".

I'm trying to find more informations on that row. What does it means?
The only info I found over the internet is that it happens when SQLExit() is called and that it's logged only since SQL 2012. (link available on msdn website )

So my question is : Should I worry to see this in my log? I find this only on my problematic server and not on any of the other 7 servers. (All of them are SQL Server 2016 Standard edition)

Can anybody give me more information on this?


texts: Migrate database from SQL Server 2008 Enterprise to SQL Server 2014 Standard

texts:
I need move database with "data compression" from SQL Server 2008 Enterprise to SQL Server 2014 Standard.

But Standard edition does not support "data compression".

How can I disable "data compression" in database?

I tried this:

ALTER INDEX [_Acc13_ByCode_SR] ON [dbo].[_Acc13]
   REBUILD PARTITION = ALL
   WITH (DATA_COMPRESSION = NONE)

but still see that compression is enabled:


texts: Install SP 3 on SQL Server 2008, where merge replication is running

texts:
I have a SQL Server 2008 and I should install Service Pack 3 for it.

On my server there are several databases replicated, and mobile devices sync data via merge replication.

Can problems occur, if I install SP3, especially with my mobile devices (where SQL CE 3.5 is running).

I can't update all of the mobile devices.


texts: Why is installing ORACLE ODBC client a complex task?

texts:
I'm following the ODBC driver instructions in the link below. I think it would help me deal with issues if I understood why this has to be so complex. Cannot be that ORACLE would not make it easier if they could. Why installing an odbc is not just downloading one or two installers and hit "next, next, next"?

http://ppm.activestate.com/libs/oracle-instant-client/10.1.0.4/win32/ODBC_IC_Readme_Win.html


texts: SQL Server Permissions for Intellisense for an Active Directory User?

texts:
I have created an Active Directory Group and created it as a user, we'll call it "AD SQL", in my DB. I can successfully login in using Active Directory Authentication and read/write.

However, Intellisense is not working for any Active Directory users.

If I log in using my Active Directory Admin, "AD ADMIN", Intellisense works fine.

Can anyone tell me what permissions/roles/view/etc are needed for a user to have access to Intellisense?

My "AD SQL" user the following roles/permissions:

db_owner
db_datareader
db_datawriter
GRANT CONNECT (DATABASE)
GRANT VIEW DEFINITION (DATABASE)

And I have already tried Refreshing Cache/Restarting SSMS.


texts: Import lots of mysqldumps with missing CREATE DATABASE

texts:
I have a bunch of mysqldumps of my old server with one database each.

Unfortunately we forgot to add

CREATE DATABASE ...

in all those dumps.

How can I import those files in one batch?


texts: Query sharded collection returns all the results from the primary shard AND from the other shards

texts:
I tried to set up a shard to test this functionality and maybe migrate some of our data into it.

I followed the documentation guide https://docs.mongodb.com/v3.4/tutorial/convert-replica-set-to-replicated-shard-cluster/

So I have a first replica rs0 (containing 2 servers), a replica rs1 (containing 2 servers) and a config replica (containing 2 servers for my tests, I understand I should use at least 3 in production)

Now, when I'm connecting on mongos instance, I see that rs0 is my primary shard. And the collection I sharded contains 1,000,000 documents. rs1 contains 1 chunk which contains approximately 500,000 documents. If I execute db.partitioned_collection.count() in mongos it returns 1,500,000. Whereas I would expect it to return 1,000,000.

I get that the primary shard contains all the unsharded documents, but mongos should only query data inside rs0 chunks no ?

What am I doing wrong ?

My sh.status() result is :

--- Sharding Status ---
sharding version: {
    "_id" : 1,
    "minCompatibleVersion" : 5,
    "currentVersion" : 6,
    "clusterId" : ObjectId("5b992a952e6c702f9dd5b675")
}
shards:
    {  "_id" : "rs0",  "host" : "rs0/mongo-test:27017,mongo-test:27018",  "state" : 1 }
    {  "_id" : "rs1",  "host" : "rs1/mongo-test:27019,mongo-test:27020",  "state" : 1 }
active mongoses:
    "3.6.7" : 1
autosplit:
    Currently enabled: yes
balancer:
    Currently enabled:  yes
    Currently running:  no
    Failed balancer rounds in last 5 attempts:  0
    Migration Results for the last 24 hours:
            2 : Success
databases:
    {  "_id" : "config",  "primary" : "config",  "partitioned" : true }
            config.system.sessions
                    shard key: { "_id" : 1 }
                    unique: false
                    balancing: true
                    chunks:
                            rs0     1
                    { "_id" : { "$minKey" : 1 } } -->> { "_id" : { "$maxKey" : 1 } } on : rs0 Timestamp(1, 0)
    {  "_id" : "test",  "primary" : "rs0",  "partitioned" : true }
            test.partitioned_collection
                    shard key: { "number" : 1 }
                    unique: false
                    balancing: true
                    chunks:
                            rs0     2
                            rs1     1
                    { "number" : { "$minKey" : 1 } } -->> { "number" : 4794 } on : rs1 Timestamp(2, 0)
                    { "number" : 4794 } -->> { "number" : 9586 } on : rs0 Timestamp(2, 1)
                    { "number" : 9586 } -->> { "number" : { "$maxKey" : 1 } } on : rs0 Timestamp(1, 2)

UPDATE #1 :

It seems I completely misread the following documentation https://docs.mongodb.com/v3.6/core/sharded-cluster-shards/#primary-shard . Indeed the primary shard does not hold the unsharded documents but the unsharded collections ...

So the question now is why my primary shard (rs0) which should only contains 2 chunks, is still containing the whole collection data ?


texts: Data Factory keeps getting "untrusted connection" error when logging into on prem SQL Server via IR on VM

texts:
We have:

A local SQL Server database
A VM with the Integration Runtime installed on it
A Data Factory sharing this IR, working (according to the ADF interface)
Logins stored in Key Vault for SQL Server auth from data factory to log into the SQL Server via the IR

The connection string properties of the connection appear to be what I want, note encryption is set to off and trustservercertificate=true:

The IR reports as connected and working fine:

I have tested the SQL Server auth on the VM with the IR installed via the IR's Diagnostics tab. Works OK:

From Data Factory, I test using the same login parameters on the linked service connected to the IR, which works:

Note we use a lot of parameters to define the connections, these work fine as well.
I set up a dataset, using the same properties, test connection works:

Now I set up an easy copy activity using the same parameters, using the same Dataset and the same linked service:

I also preview the data I want, which works, bringing data back from the server as expected.
This makes no sense to me, I have tested using exactly the same parameters in each step, but a simple copy activity fails when all the other connection tests have succeeded. Anyone have any idea?
NOTE: I have tried setting the Linked Service connection parameters: Encryption = True which sometimes means the connection goes through OK. Again, this makes little sense.


texts: Help with adding a time filter to Postgres query

texts:
I've asked this question over in SE GIS, but told it would be better suited to this SE, so here goes...

I know Postgres is brilliant for grouping points based on their spatial values - that I can do - but is there a way I can group points by their 'closeness' in time. By this I mean, how would I go about identifying points that were created within x minutes of the first point.

To put it into some context, the first point could be the first Facebook status update immediately after Arsenal scored against Aston Villa. There would be a sharp rise in status updates immediately after the goal before gradually trailing off.

So..If I had a dataset of 50,000 records representing Facebook status updates each with a random time and then I edited 1000 of these to be within 10 minutes of the first post, how would I best approach this and what would the SQL look like?

The time format is arranged as YY:MM:DD HH:MM:SS.

So far I have been able to return those records which are close spatially and have a similar message, but adding in the time element so I have results that are close spatially, temporally and share the same topic has proved a little more difficult for me.

I've looked at the interval function but I am unsure of how exactly to deploy this in my SQL. Would it be that first I filter records by their spatial dispersal, then filter which are close in time and finally the similarity of their status?

My SQL looks something like this for now..

Creating the table...

CREATE TABLE FB_status (
id character varying (254),
status character varying (254),
longitude numeric,
latitude numeric,
posted_at character varying (254),
geom geometry(POINT));

My SQL thus far...

SELECT (a.status, b.status)
FROM FB_status AS a
JOIN FB_status AS b
ON ST_Dwithin (a.geom, b.geom, 0.01)
WHERE similarity (a.status, b.status) > 0.5;


texts: Limit the number of records depends on the part of primary key

texts:
I created database for football matches.

I have 2 tables with a many-to-many relationship, so I also created a junction table:

Referee

CREATE TABLE referee (
  id_referee INT IDENTITY PRIMARY KEY,
  id_type_referee INT NOT NULL,
  first_name varchar(20) NOT NULL,
  last_name varchar(30) NOT NULL,
  date_of_birth DATE NOT NULL,
  FOREIGN KEY(id_type_referee) REFERENCES type_referee(id_type_referee)
);

Match

CREATE TABLE match (
  id_match INT IDENTITY PRIMARY KEY,
  id_stadium INT NOT NULL REFERENCES stadium(id_stadium),
  id_season INT NOT NULL REFERENCES season(id_season),
  date_of_match DATE NOT NULL,
  audience INT CHECK (audience>=0)
);

Junction table

CREATE TABLE match_referee(
  id_referee INT NOT NULL,
  id_match INT NOT NULL,
  PRIMARY KEY(id_referee,id_match),
  FOREIGN KEY (id_referee) REFERENCES referee(id_referee),
  FOREIGN KEY (id_match) REFERENCES match(id_match)
);

I want to limit the number of referees at a single match to three. How can I do that?


texts: Run out of virtual memory after added to triple physical RAM and change SQL Server memory option?

texts:
Recently, we added some physical RAM to our server which running SQL Server 2008 R2 instance, the total memory was changed from 64GB to 192GB.

And we tuned the memory option of SQL Server by changing the 'min server memory' from 16GB to 64GB, and the 'max server memory' from 48GB to 128GB.

And the pagefile of Windows Server 2008 R2 is left unchanged with 64GB.

But after two days later, Windows Server reported run out of virtual memory, some 'Windows successfully diagnosed a low virtual memory condition' events can be found in EventLogs. We cannot connected via remote desktop client, and cannot launch mmc or notepad application even though operating from console, only Explorer and task manager can be opened. at that time, about 50% physical memory was used.

And now, we change 'min server memory' back to 16GB, the server runs without any resource exhaustion detector events, but the physical RAM usage was always lower than 64GB.

SQL Server Version:

  Microsoft SQL Server 2008 R2 (SP1) - 10.50.2769.0 (X64) Jun 24 2011
  13:09:43 Copyright (c) Microsoft Corporation Enterprise Edition
  (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

Please help us to solve it.

Thanks.


texts: What should be the relationship between an adminstrators table and users table

texts:
If admin account can create user accounts, does this mean that the relationship between the admin table and the users table is:

ADMIN OneToMany Users

For example, in my project:

A person with the privilege adminstrateur account, can create accounts with the privilege technicien_sav. Obviously, each kind of privilege grants the one who has it access to different parts of the app.
However, I do not see the SQL relationship necessary for the overall functioning of my app. I.e: The administrateur can create technicien_sav accounts even without the existence of the relationship between the two.
What do you think?
PS: It's been years since I've worked with SQL databases, I hope the question is not too naive.


texts: Is it OK to use WITH INIT for my Transaction Log Backup to avoid having it grow too large?

texts:
I'm still struggling to understand SQL's backup jobs.

We currently have a SQL Server 2005 instance using the FULL recovery model.

We do a full database backup every week.

BACKUP DATABASE [MyDatabase]
TO DISK = N'E:\Database Backups\MyDatabase.Bak'
WITH INIT, NAME = N'MyDatabase.Bak'

And a differential backup once a day

BACKUP DATABASE [MyDatabase]
TO DISK = N'E:\Database Backups\MyDatabase_Diff.Bak'
WITH INIT, DIFFERENTIAL, NAME = N'MyDatabase_Diff.Bak'

Now I am trying to figure out how to schedule Transaction Log Backups every hour, so at most we would lose an hours worth of work.

BACKUP LOG [MyDatabase]
TO DISK = N'E:\Database Backups\MyDatabase_Log.Bak'

My problem is, our storage space is limited and I don't want the log file to grow too large. Can I use WITH INIT on the transaction log backup to force it to create a new file every hour? Or do I need all the Transaction Log backups since the last Differential backup to restore to a specific point in time during the day?

And if I do need to keep all copies since the last differential, is there a way to tell it to reset anytime I do a backup?


texts: MySQL: how to use audit_log plugin to audit user modification

texts:
I am trying to audit any user modification action (create user/alter user/drop user/rename user) on a MySQL Enterprise server 5.7.24.
I have installed the audit_log plugin following the
 document,
and tried to configure the filter with a few approaches.
First, I tried to use
general
SELECT audit_log_filter_set_filter('user_modification',
'{
  "filter": {
    "class": {
    "name": "general",
      "event": {
        "name": "status",
        "log": {
          "field": { "name": "general_sql_command.str", "value": "create_user" }
        }
      }
    }
  }
}') as RESULT;

and assigned the filter to everyone but no luck, it doesn't generate any audit log when I tried to create user
SELECT audit_log_filter_set_user('%', 'user_modification');

I also tried to swap a few things in the same filter, no luck

swap 'create user' with full instrument name 'statement/sql/create_user'
swap 'general_sql_command.str' with 'general_command.str'

Next, I tried to use 'table_access' instead of 'general', hopeful it will capture any movement on mysql.user table, but still not luck
SELECT audit_log_filter_set_filter('user_modification',
'{
  "filter": {
    "class": [
      {
        "name": "table_access",
        "event": {
          "name": [ "read", "insert", "delete", "update" ],
          "log": {
            "field": { "name": "table_database.str", "value": "mysql"},
            "field": { "name": "table_name.str", "value": "user"}
          }
        }
      }
    ]
  }
}') as RESULT;

Just in case I missed out on any step during installation, I created another filter for test and the result is positive.
SELECT audit_log_filter_set_filter('connection',
'{
  "filter": {
    "class": { "name": "connection" }
  }
}');

SELECT audit_log_filter_set_user('%', 'connection');

Now I am totally puzzled, can anyone help me? thanks


texts: Mysql 5.5.25 must be restarted to connect

texts:
We have MySQL v5.5.25 running on Windows Server 2008 R2. Occasionally people are unable to connect, and the only way we have found to fix this is to restart the mysql service. This happens every 2-3 days.

Anyone have a clue on this? We have spent hours trying to fix it changing various values in the configuration, but nothing has worked.


texts: SQL Server Setup: Report Server and RAM Considerations

texts:
DISCLAIMER

I am a software developer by trade, but sometimes have to put on my 'dba' hat in the absence of a true dba.

SETUP

I am working on a project where:

SQL Version is SQL Server 2008 R2 (no SP installed).
They have 32GB of RAM, but 28,672MB is the max server memory allowed.
The primary usage of the database is hosting the db for an account / business management system called 'Aderant'.
I have added SQL Reporting Services
I built a few Reports with Report Builder to report on AR and open invoices,
I am experiencing performance issues for larger data sets including CPU spikes to 100%, some reports do not return at all.

QUESTION

Do I need to request more RAM on this server for the additional overhead of running Reporting Services?

Couldn't find anything specific to this on the MS Docs site, searching for "SQL Server Handware and Software Requirements".

Thanks!


texts: How do I differentiate between user-defined public synonyms and built-in public synonyms?

texts: I am trying to query the Oracle ALL_SYNONYMS view, but filtering by OWNER=PUBLIC returns both synonyms I have created and Oracle created ones. Is there a way to distinguish between the two?


texts: Do you know an easy way to generate one record for each hour of the past 12 hours?

texts:
I have a report that shows the count of events for the past 12 hours, grouped by the hour.  Sounds easy enough, but what I am struggling with is how to include records that cover the gaps.

Here is an example table:

Event
(
  EventTime datetime,
  EventType int
)

Data looks like this:

  '2012-03-08 08:00:04', 1
  '2012-03-08 09:10:00', 2
  '2012-03-08 09:11:04', 2
  '2012-03-08 09:10:09', 1
  '2012-03-08 10:00:17', 4
  '2012-03-08 11:00:04', 1

I need to create a result set that has one record for every hour of the past 12 hours, regardless of there being events during that hour or not.

Assuming the current time is '2012-03-08 11:00:00', the report would show (roughly):

Hour  EventCount
----  ----------
23    0
0     0
1     0
2     0
3     0
4     0
5     0
6     0
7     0
8     1
9     3
10    1

I came up with a solution that uses a table that has one record for every hour of the day.  I managed to get the results I was looking for using a UNION and some convoluted case logic in the where clause, but I was hoping somebody had a more elegant solution.


texts: Should PostgreSQL be Installed on a Server Machine for Multi-User Access?

texts:
I am hoping to set up a database using PostgreSQL which multiple internal users should be able to access.
I am relatively new to administrating databases so I am unsure where the best place is for me to install the software.
Would it be best for me to check with my IT department to see if we have a server machine that I could install the database on?


texts: How can one improve the performance of a query that selects on a low cardinality column in MySQL?

texts:
I have a "State" column that has a low cardinality (three possible value), on which most of my queries perform an equality selection (WHERE col = 'blah')  AFAIK you need something like a bitmap index to speed this up, which is not available in MySQL. The column value changes from state1 -> state2 -> state -> 3 etc., so I couldn't use partitioning.
How could one speed things up in this situation?


texts: Can I Parse and/or include "And" in my SQL Statement for a full text index?

texts:
So I have a query that looks at a full text index like this:

SELECT *
FROM Table1 q
    INNER JOIN CONTAINSTABLE(Table1,TableField, @Keywords) search
    ON q.Key = search.[KEY]

Now @Keywords can be either a Word (For example: Moo) a Phrase (Chocolate Milk) or a Search String (Chocolate AND Milk).  However I get an error when it's a phrase (Chocolate Milk) evidently it's expecting a single word or search string.  What's the easiest way to build the intelligence in to understand a phrase vs. a Search string?


texts: Objective, SQL Server 2005 and exporting data using **bcp**

texts:
I was tasked w/ getting data out of an obsolete document management system running on top of an instance of MS SQL Server.

Trying to export > 30000 documents through the app is not an option, as it shoots itself in the foot if I pick more than 10 items.

Trying to export the database (or individual tables) from SQL Server Management Studio renders a variety of errors, namely Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Destination - documents" (that happened when trying to export to either Excel or CSV).

Given the fickle state of the machine and my utter lack of knowledge in all things Microsoft I don't want to try and mess with the SQL Server installation by adding components.

The only way I managed to extract ANY data from tables is via bcp.
That, however, produced an other interesting complication.  Having explicitly chosen TAB as a delimiter I get e.g. for a table w/ 61 (or 65  respectively) columns output that has a variety of column numbers for each row:

awk -F"\t" '{print NF}' folders.txt | sort | uniq -c | sort -n
    3 0
    5 1
    9 17
    9 45
15277 61

awk -F"\t" '{print NF}' documents.txt | sort | uniq -c | sort -n
    1 4
    1 62
   14 0
   24 1
   41 29
   41 37
31291 65

Does anyone have advice on how to get sensible results out of bcp?

Here's a sample query:

bcp "select * from objProd.dbo.documents" queryout z:\test.txt -c -T

Starting copy...
1000 rows successfully bulk-copied to host-file. Total received: 1000
1000 rows successfully bulk-copied to host-file. Total received: 2000
1000 rows successfully bulk-copied to host-file. Total received: 3000
1000 rows successfully bulk-copied to host-file. Total received: 4000
1000 rows successfully bulk-copied to host-file. Total received: 5000
1000 rows successfully bulk-copied to host-file. Total received: 6000
1000 rows successfully bulk-copied to host-file. Total received: 7000
1000 rows successfully bulk-copied to host-file. Total received: 8000
1000 rows successfully bulk-copied to host-file. Total received: 9000
1000 rows successfully bulk-copied to host-file. Total received: 10000
1000 rows successfully bulk-copied to host-file. Total received: 11000
1000 rows successfully bulk-copied to host-file. Total received: 12000
1000 rows successfully bulk-copied to host-file. Total received: 13000
1000 rows successfully bulk-copied to host-file. Total received: 14000
1000 rows successfully bulk-copied to host-file. Total received: 15000
1000 rows successfully bulk-copied to host-file. Total received: 16000
1000 rows successfully bulk-copied to host-file. Total received: 17000
1000 rows successfully bulk-copied to host-file. Total received: 18000
1000 rows successfully bulk-copied to host-file. Total received: 19000
1000 rows successfully bulk-copied to host-file. Total received: 20000
1000 rows successfully bulk-copied to host-file. Total received: 21000
1000 rows successfully bulk-copied to host-file. Total received: 22000
1000 rows successfully bulk-copied to host-file. Total received: 23000
1000 rows successfully bulk-copied to host-file. Total received: 24000
1000 rows successfully bulk-copied to host-file. Total received: 25000
1000 rows successfully bulk-copied to host-file. Total received: 26000
1000 rows successfully bulk-copied to host-file. Total received: 27000
1000 rows successfully bulk-copied to host-file. Total received: 28000
1000 rows successfully bulk-copied to host-file. Total received: 29000
1000 rows successfully bulk-copied to host-file. Total received: 30000
1000 rows successfully bulk-copied to host-file. Total received: 31000

31333 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 3094   Average : (10127.02 rows per sec.)

Note: This is my first time ever using SQL Server, I'm a Linux admin, and had good exposure to Postgres and some to MySQL.  The state of the windows instance and the DB have not changed since long before I joined my current employer.


texts: Allocation Size for Backup disk on SQL Server

texts: What would be the optimal allocation size for a backup disk for SQL Server? We have a 2014 instance which was using Veeam backups till recently, but we decided to switch to local backups again for some reasons. Does the 64k size hold good for backups as well? Please advice


texts: Clustered Primary Key that is never used vs. Non-Clustered Primary Key on Multiple Columns

texts:
I am working on a table design for Customer Totals and trying to make a decision about the primary key. I was going to go with a surrogate identity column with a clustered index, but this column would NEVER be used. The candidate primary key columns are CustomerNumber + AccountNumber, because these are the unique identifiers for each row, but these will NOT be sequentially inserted.

Basically, on a daily basis a report will be run which will update each CustomerNumber + AccountNumber record with the most recent purchase total and total date.

Does it make sense to remove CustomerTotalID completely and have CustomerNumber + AccountNumber be a PK with a NON-clustered index?

    CREATE TABLE CustomerTotals (
    CustomerTotalID INT IDENTITY(1,1),
        CustomerNumber INT,
        AccountNumber INT,
        PurchaseTotal DECIMAL(10,2)
        TotalDate DATE,
      CONSTRAINT [PK_CustomerTotals] PRIMARY CLUSTERED (
    CustomerTotalID ASC
)
    )


texts: Synchronisation of multiple databases with GUID type

texts:
I have two databases. One is on mobile and uses SQLite and another one is on Microsoft azure.

I want to sync data in both directions. Sometimes some data is saved on azure db and should be synchronised with SQLite database.

In each table I am using guid field that identifies unique row.

At the moment I have integer as primary key with clustered index and guid as non clustered index which then child table is linked to.

So if I have tables parent and child, I would use guid as parent key and then in child key I would use guidparentid as foreign key. I link relation between parent and child with guid. It is little messy but I make sure I keep clustered index on integer rather guid.

To simplify this I could just use guid as primary key and link all normally, but even if I use newsequentialid() on guid, clustered index on guid is very bad idea.

What would be your suggestion? Should I stick to integer primary key with clustered index and link parent child tables with guids, it should I just upgrade int to guid as primary clustered key?


texts: MySQL said "InnoDB: Initializing buffer pool, size = 128.0M", does it mean it is using 128MB RAM?

texts:
I am just using a very few InnoDB tables (e.g. less than 1MB), but during MySQL startup, it said

InnoDB: Initializing buffer pool, size = 128.0M

Does it mean even I am using in such a small size, the server still use 128M RAM?


texts:
I have two database designs thought of for storing messages(instant messages) between users. It will be a group chat system.

I have two table designs in mind.

One is this:

CREATE TABLE `mssg_grp_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `grp_id` int(10) unsigned NOT NULL,
  `body` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`created_at`,`grp_id`,`user_id`,`id`),
  KEY `mssg_grp_user_user_id_foreign` (`user_id`),
  KEY `mssg_grp_user_grp_id_foreign` (`grp_id`),
  KEY `mssg_id_is` (`id`),
  CONSTRAINT `mssg_grp_user_grp_id_foreign` FOREIGN KEY (`grp_id`) REFERENCES `groups` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `mssg_grp_user_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=350201 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

And another is this:

in the second one:
the Messages table has the following keys

alter table messages
drop primary key ,
add primary key (created_at, id),
add INDEX mssg_id_ix (id)    ;

And there is a primary key of (grp_id,mssg_id) in the mssg_grps table.

My main queries are to retrieve the newest 50 messages that are in a list of group IDs AND are newer than a certain time. For the 2nd one, I ran this query :

SELECT * FROM test.mssg_grp a LEFT JOIN test.messages b ON a.mssg_id = b.id
Where b.created_at >"1970-01-03 19:08:21"
AND a.grp_id IN(8,2,1,2,5,6,3)
order by created_at limit 50;

This took a long time. It took 0.1 seconds for one query (there are 200k messages) and around 2 minutes for 100 concurrent users.

Then I decided to merge the tables(i.e. change my db schema) and switched to the first one.

To get the same data, i ran this query:

Select sql_no_cache * from test.mssg_grp_user where  grp_id IN (8,1,5,6,7,8,9) and created_at>888880
order by  created_at desc limit 50;

This took 0.1 seconds for 100 concurrent users. This is obviously much faster. My questions are:

Why is the join+order taking so much time?
Should I switch to the first design.(the body field may have up to 500 characters)


texts:
I want to achieve federation using following commands through JDBC.

connect to SIGCAP_M user db2sigcp using db2sigcp ;

drop wrapper drda;
create wrapper drda ;

UNCATALOG NODE XYZ;
CATALOG TCPIP NODE XYZ REMOTE 10.XXX.XX.XX SERVER 446 ostype OS400;

UNCATALOG DB as4xyz;
CATALOG DCS DATABASE as4xyz as as4xyz;
CATALOG DB as4xyz as as4xyz AT NODE xyz authentication DCS ;

attach to xyz user "kasparov" using "kasparov123";

drop server xyz;
create server xyz type DB2/400 version 7 wrapper DRDA authorization db2ngicp PASSWORD db2ngicp options( add dbname 'S845F85V', DB2_TWO_PHASE_COMMIT  'Y'  ) ;

CREATE USER MAPPING FOR db2sigcp SERVER EQUATION OPTIONS ( ADD REMOTE_AUTHID 'db2ngicp',ADD REMOTE_PASSWORD 'db2ngicp') ;

CREATE nickname E_NEPF FOR as4xyz.KFILLV.NEPF;

As per IBM documentation - https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0012547.html

SYSPROC.ADMIN_CMD doesn't support any of the above commands. How can I make this work?


texts:
I am trying to work out whether it's possible to create a Domain Independent Workgroup Cluster in AWS (on Windows Server 2016 for use with SQL 2017 Availability Groups). This environment also has no DNS server, so I would need to manually create entries in the host file of each node for each resource. However, I believe for AWS clusters the nodes need to reside in different subnets, and each node needs its own dedicated IP address for the WSFC resource?
Does anyone know if this actually possible in AWS without a DNS server? If so, what would the host file entries need to be for the WSFC resource on each node? In a regular single subnet cluster I believe this would be the same IP for both nodes, but when each node has its own IP for the WSFC resource I'm not sure if this will work?
Example config is as follows:

Cluster Name - TestCluster1

Node1

Subnet - 192.168.0.0/24
Primary IP - 192.168.0.1
WSFC IP - 192.168.0.2

Host File Entries:

192.168.1.1 Node2
??????????? TestCluster1

Node2

Subnet - 192.168.1.0/24
Primary IP - 192.168.1.1
WSFC IP - 192.168.1.2

Host File Entries:

192.168.0.1 Node1
??????????? TestCluster1

Any thoughts welcome!
Thanks

EDIT: As a follow-up to this, I have tried adding host file entries for both WSFC IPs on each node as follows:

192.168.0.2 TestCluster1
192.168.1.2 TestCluster1

After a quick test it appears this does seem to work - I presume it simulates a round-robin DNS approach, although I don't know how advisable it is?!


texts: How I can find whitch reports use views that are generated from specific tables?

texts:
I have the following table named reports:
report_code PK VARCHAR 5
report_sql TEXT

The report_sql stores the sql code that generates a report. The sql code is in postgresql flavor. So from that table I want to search all the records that on the sql code use views and materialized views that are generated from specific tables.
The views, the materialized views the tables that generated from and the table reports is in the same database server.


texts:
I am trying to install APEX 19.1 on ORACLE XE 18c but the problem is that APEX_LISTENER and APEX_REST_PUBLIC_USER are not being created. While I execute apex_rest_config.sql script via sqlplus, an error message appears saying: GetConsoleMode failed, Last Error=|6|

Then I check the list of users and I see that these two users are not created. Is this a bug of APEX installation? Please can anybody help?


texts: MongoDB: why does a DB with 0 connections still get "commands" and "getMores"?

texts:
I'm in the process of migrating an old mongoDB database.
Before shutting down the old one, I want to make sure nothing is still writing to it or reading from it. Looking at Compose's MongoDB classic monitoring tool, the graph indicates 0 inserts, 0 queries, 0 updates and 0 deletes BUT, also a steady stream of "getMores" and "commands".

I assume the commands must be internal processes of MongoDB, but which ones? What's going on? Also, from the bit of search I've made, it seems that getMores have something to do with cursors, what internal process would be using cursors?


texts: SQL Server 2016 -number of SQL agent replication jobs(transaction log repl)

texts: So I have a SQL server 2016 on a Windows Server 2012 instance. We have 140 databases(small to med). So the SQL instance was setup for self replication and in the process we have 140 SQL agent replication jobs. When the server reboots it can handle ~120 replication jobs, then when one is stopped it can not be re-started - it fails - but with no message at all - then says "between retries". In replication monitor - the agent has the red x and or is showing not running. In the event viewer I see logread.exe application crash. I was trying to research to see if there is a cap on the number of agent jobs running. Our memory is good, the disks and CPU usage is low. I have not been part of something of this size before and I am currently out of ideas. My current work around is to have a PowerShell script read the current failures and manually kick off the -Publisher [xxx] -PublisherDB [DbName] -Distributor [xxx] -DistributorSecurityMode 1 -Continuous  . That is what makes me think there is a cap on the number of agents because I can run it with out the SQL-Agent. I was thinking maybe I need to change that to more of a scheduler but we have reports run that need timely info. What am I missing? Or am I right in looking to breaking up the job runs?


texts:
I wanted to delete duplicate records in my database, but it's a little more in depth than just one field.

Status  ID
P   150013511822029
P   150022555544009
P   150022551512029
X   150014520920007
R   150022994940022
P   150023488655015

If status = R, the record needs to be removed.
If the status = X (this is a reversal), both the X record and the corresponding P record (with the same ID) need to be removed.

Can someone please help out a newbie? Thank you!