texts: Can't connect to SQL Server DB from SSMS network error

texts:
Here some background on the setup. The app server and the db server are on a workgroup no domain. Both are AWS EC2 instances. A partner went and install an evaluation copy of SQL Server 2014. I apply the licence key (SQL Server 2014 Standard) on the instance and run the upgrade wizard since then nothing is working as it should. Before the expired date you could connect to the SQL server from the app server using the SSMS. Now getting an network error 18456.

Firewalls are disabled
Under the SQL Configuration manager TCP/IP have been enable.
Can telnet form the APP server to the SQL server on port 1433 and 1434
Can login with my sql user using SSMS on the SQL server it self

I had a look at these websites as will but still no luck.
https://logicalread.com/errors-sql-server-login-failures-pd01/#.WsNZsIhuZEY
http://sqltechtips.blogspot.co.za/2018/03/sql-server-login-failed-error-18456.html

Sorry I am not an DB admin. Any help will be greatly appreciated
Gerrit


texts: Service Broker User Does Not Have Permission to Send to the Service

texts:
I am using .NET SqlDependency with a named queue (not dynamically created).

My Service Broker Configuration Is

CREATE QUEUE dbo.SqlDependencyQueue;
GO
CREATE SERVICE SqlDependencyService AUTHORIZATION [dbo]
ON QUEUE SqlDependencyQueue
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);
GO
CREATE ROUTE SqlDependencyRoute WITH SERVICE_NAME = 'SqlDependencyService', ADDRESS = 'LOCAL';
GO
GRANT SELECT to [ApplicationRole]
GO
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [ApplicationRole]
GO
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [ApplicationRole]
GO
GRANT REFERENCES on
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
  to [ApplicationRole]
GO

GRANT RECEIVE ON SqlDependencyQueue TO [ApplicationRole]
GO

When I update a table related to the SqlDependency SqlCommand, I see in Sql Server Profiler the events

Broker:Conversation
Broker:Message Classify
Broker:Message Undeliverable
Broker:Remote Message Ackknowledgement

The error message in the Message Undeliverable event is:

This message could not be delivered because the user with ID 6 in database ID 54 does not have permission to send to the service. Service name: 'SqlDependencyService'.

User Id number 6 is ##MS_AgentSigningCertificate##

I have not seen any documentation that mentions this user id in the context of SqlDependency or Service Broker so I am not quite sure why the error is occurring.

I have tried granting this user id access to the service but the id is not recognized.

GRANT SEND ON SERVICE::SqlDependencyService TO ##MS_AgentSigningCertificate##

Msg 15151, Level 16, State 1, Line 1
Cannot find the user '##MS_AgentSigningCertificate##', because it does not exist or you do not have permission.

I thought maybe I needed to create a login for that user but the user is not browseable when creating a login.


texts: How to update ID=null values in table to incremental counter values?

texts:
On SQL Server 2012, i've got an intermediate/staging table for merging existing with new data, where I want to insert numeric IDs for newly created rows:

ID   NaturalID               Comment

1    franknfurther03071972   blahblah
2    chrisrock12081980       nonsense
null clairecampbell24121990  merry christmas
3    walterhermes22032001    young guy
4    tanjaolsen16051996      nice
null timharris20041999       came late

The rows with "null" IDs are new, the numbered IDs are those already existing in the main, target table. The NaturalID can uniquely identify an entry (in fact, it's multiple columns). I want to set the "null" IDs to incremental values, following the current max ID, here: 5 and 6, increasing when more null IDs are found.

Currently, I use a cursor to iterate over the rows with ID null and update each ID with a value, but sincce it's really a very big table, it would take days.

I tried to do an update with row_number(), but it gives me an error "Windowed functions can only appear in the SELECT or ORDER BY clauses.":

update StagingTable set ID=ROW_NUMBER() over (order by NaturalId)
from StagingTable where id is null  -- fails

How can I do it?


texts: SQL Server is running slow after a certain amount of time

texts:
We've been experiencing some issues with our SQL server recently.  I have noticed that some of our SQL jobs are running longer than normal.  One job normally runs in under 3 minutes will take over a day.  A job that runs in about 15 seconds will take over 2 minutes.  These jobs calls a simple stored procedure.

I have looked at the CPU usage and memory and everything looks fine.  I am not sure what else I can look at.  It seems odd that everything will run fine for a bit but then after a week or so, the jobs start taking long to run again.  The only thing I could do it make it better again is to restart the SQL service.

We are running SQL Server 2012 SP2 Enterprise Edition on Windows 2012 R2.  We are using AAG on this particular server as well.

Anyone have any ideas what to look at to see why the performance tanks after a few days?  I had some performance counters captured but I didn't see anything jump out.  I don't see any memory errors either.


texts: Using ODBC Connection to Access Firebird Database

texts: I have an ODBC connection to a Firebird database that tests successfully.  Is there a way to use this ODBC connection within SQL  Server 2008 R2 to access the tables?  I haven't used Linked Servers before and wasn't sure if that was an option...and if so, what would be the provider needed/desired?


texts:
I don't understand what Craig Ringer meant when he commented:

  This solution is subject to lost updates if the inserting transaction rolls back; there's no check to enforce that the UPDATE affected any rows.

on https://stackoverflow.com/a/8702291/14731. Please provide a sample sequence of events (e.g. Thread 1 does X, Thread 2 does Y) that demonstrates how lost updates could occur.


texts: How to define destination for an append query Microsoft Access

texts:
I'm trying to append two tables in MS Access at the moment. This is my SQL View of my Query at the moment:

INSERT INTO MainTable
SELECT
FROM Table1 INNER JOIN Table2 ON Table1.University = Table2.University;

Where university is the only field name that would have similarities between the two tables. When I try and run the query, I get this error:

Query must have at least one destination field.

I assumed that the INSERT INTO MainTable portion of my SQL was defining the destination, but apparently I am wrong. I am not a very frequent Access user, or database administrator at all, really - I was just tasked with this at work and do not want to disappoint, therefore, any help is greatly appreciated.


texts:
At the moment I'm bit stuck and need advice, I have looked up the 'ifnull' but nothing I can find 'ifnull' along with join tables.

SELECT Ifnull((SELECT tbl_sku_units_order.sku         AS 'sold_sku',
                  tbl_list_sku.sku                AS 'list_sku',
                  tbl_sku_units_order.units_ordered,
                  COALESCE(Sum(units_ordered), 0) AS 'Today_Sold'
           FROM   tbl_list_sku
                  INNER JOIN tbl_sku_units_order
                          ON tbl_sku_units_order.sku = tbl_list_sku.sku
           GROUP  BY tbl_list_sku.sku
           HAVING units_ordered IS NOT NULL
                  AND Length(units_ordered) > 0
           ORDER  BY units_ordered DESC), '0');

so I have tried different function, at the moment I am using this

SELECT
    tbl_sku_units_order.sku AS 'sold_sku',
    tbl_list_sku.sku AS 'list_sku',
    tbl_sku_units_order.units_ordered,
    COALESCE(SUM(units_ordered), 0) AS 'Today_Sold'
FROM
    tbl_list_sku
        INNER JOIN
    tbl_sku_units_order ON tbl_sku_units_order.sku = tbl_list_sku.sku
GROUP BY tbl_list_sku.sku
HAVING units_ordered IS NOT NULL
    AND LENGTH(units_ordered) > 0
ORDER BY units_ordered DESC

in the end, I got the result what they sold

but we have 100 SKU are unsold, but they are not displayed at all, as I would like to see the 100 unsold SKU displayed as '0'  as two different table on it.
Maybe I am missing something?


texts: Why does a query run slower in a Stored Procedure than in the Query window?

texts:
I have a complex query which runs in 2 seconds in the query window, but about 5 minutes as a Stored Procedure. Why is it taking so much longer to run as a stored procedure?

Here's what my query looks like.

It takes a specific set of records (identified by @id and @createdDate), and a specific time frame (1 year starting from @startDate) and returns a summarized list of letters sent and estimated payments received as a result of those letters.

CREATE PROCEDURE MyStoredProcedure
    @id int,
    @createdDate varchar(20),
    @startDate varchar(20)

 AS
SET NOCOUNT ON

    -- Get the number of records * .7
    -- Only want to return records containing letters that were sent on 70% or more of the records
    DECLARE @limit int
    SET @limit = IsNull((SELECT Count(*) FROM RecordsTable WITH (NOLOCK) WHERE ForeignKeyId = @id AND Created = @createdDate), 0) * .07

    SELECT DateSent as [Date]
        , LetterCode as [Letter Code]
        , Count(*) as [Letters Sent]
        , SUM(CASE WHEN IsNull(P.DatePaid, '1/1/1753') BETWEEN DateSent AND DateAdd(day, 30, DateSent) THEN IsNull(P.TotalPaid, 0) ELSE 0 END) as [Amount Paid]
    INTO #tmpTable
    FROM (

        -- Letters Table. Filter for specific letters
        SELECT DateAdd(day, datediff(day, 0, LR.DateProcessed), 0) as [DateSent] -- Drop time from datetime
            , LR.LetterCode -- Letter Id
            , M.RecordId -- Record Id
        FROM LetterRequest as LR WITH (NOLOCK)
        INNER JOIN RecordsTable as M WITH (NOLOCK) ON LR.RecordId = M.RecordId
        WHERE ForeignKeyId = @id AND Received = @createdDate
            AND LR.Deleted = 0 AND IsNull(LR.ErrorDescription, '') = ''
            AND LR.DateProcessed BETWEEN @startDate AND DateAdd(year, 1, @startDate)
            AND LR.LetterCode IN ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o')
    ) as T
    LEFT OUTER JOIN (

        -- Payment Table. Payments that bounce are entered as a negative payment and are accounted for
        SELECT PH.RecordId, PH.DatePaid, PH.TotalPaid
        FROM PaymentHistory as PH WITH (NOLOCK)
            INNER JOIN RecordsTable as M WITH (NOLOCK) ON PH.RecordId = M.RecordId
            LEFT OUTER JOIN PaymentHistory as PR WITH (NOLOCK) ON PR.ReverseOfUId = PH.UID
        WHERE PH.SomeString LIKE 'P_'
            AND PR.UID is NULL
            AND PH.DatePaid BETWEEN @startDate AND DateAdd(day, 30, DateAdd(year, 1, @startDate))
            AND M.ForeignKeyId = @id AND M.Created = @createdDate
    ) as P ON T.RecordId = P.RecordId

    GROUP BY DateSent, LetterCode
    --HAVING Count(*) > @limit
    ORDER BY DateSent, LetterCode

    SELECT *
    FROM #tmpTable
    WHERE [Letters Sent] > @limit

    DROP TABLE #tmpTable

The end result looks like this:

Date        Letter Code     Letters Sent    Amount Paid
1/1/2012    a               1245            12345.67
1/1/2012    b               2301            1234.56
1/1/2012    c               1312            7894.45
1/1/2012    a               1455            2345.65
1/1/2012    c               3611            3213.21

I'm having problems figuring out where the slowdown is, because everything runs extremely fast in the query editor. It's only when I move the query to a stored procedure that it starts taking so long to run.

I'm sure it has something to do with the query execution plan getting generated, but I don't know enough about SQL to identify what could be causing the problem.

It should probably be noted that all the tables used in the query have millions of records.

Can someone explain to me why this is taking so much longer to run as a stored procedure than in the query editor, and help me identify what part of my query could be causing performance issues when run as a stored procedure?


texts: Asked to Not Use Transactions and to Use A Workaround to Simulate One

texts:
I've been developing T-SQL for several years and am always digging in further, continuing to learn all I can about all aspects of the language. I recently started working at a new company and have received what I think is an odd suggestion regarding transactions. Don't ever use them. Instead, use a workaround that simulates a transaction. This is coming from our DBA who works in one database with a lot of transactions and subsequently, a lot of blocking. The database I primarily work in does not suffer from this issue and I see transactions have been used in the past.

I understand that blocking is expected with transactions as it's in their nature to do so and if you can get away without using one, by all means do it. But I have many occasions where each statement MUST execute successfully. If one fails they all must fail to commit.

I’ve always kept the scope of my transactions as narrow as possible, always used in conjunction with SET XACT_ABORT ON and always within a TRY/CATCH.

Example:

CREATE SCHEMA someschema;
GO

CREATE TABLE someschema.tableA
(id   INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
 ColA VARCHAR(10) NOT NULL
);
GO

CREATE TABLE someschema.tableB
(id   INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
 ColB VARCHAR(10) NOT NULL
);
GO

CREATE PROCEDURE someschema.ProcedureName @ColA VARCHAR(10),
                                          @ColB VARCHAR(10)
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN
BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO someschema.tableA(ColA)
    VALUES(@ColA);

    INSERT INTO someschema.tableB(ColB)
    VALUES(@ColB);

--Implement error
    SELECT 1/0

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@trancount > 0
    BEGIN
        ROLLBACK TRANSACTION;
    END;
    THROW;
    RETURN;
END CATCH;
END;
GO

Here is what they suggested that I do.

GO

CREATE PROCEDURE someschema.ProcedureNameNoTransaction @ColA VARCHAR(10),
                                                       @ColB VARCHAR(10)
AS
SET NOCOUNT ON;
BEGIN
BEGIN TRY
    DECLARE @tableAid INT;
    DECLARE @tableBid INT;

    INSERT INTO someschema.tableA(ColA)
    VALUES(@ColA);
    SET @tableAid = SCOPE_IDENTITY();

    INSERT INTO someschema.tableB(ColB)
    VALUES(@ColB);
    SET @tableBid = SCOPE_IDENTITY();

--Implement error
    SELECT 1/0

END TRY
BEGIN CATCH
    DELETE FROM someschema.tableA
    WHERE id = @tableAid;

    DELETE FROM someschema.tableB
    WHERE id = @tableBid;

    THROW;

    RETURN;
END CATCH;
END;
GO

My question to the community is as follows. Does this make sense as a viable workaround for transactions?

My opinion from what I know about transactions and what the solution is proposing is that no, this isn’t a viable solution and introduces many points of failure.

In the suggested workaround, I see four implicit transactions occurring. The two inserts in the try and then two more transactions for the deletes in the catch. It does “undo” the inserts but without rolling back anything so nothing is actually rolled back.

This is a very basic example to demonstrate the concept they are suggesting. Some of the actual stored procedures I have been doing this in make them exhaustively long and difficult to manage because “rolling back” multiple result sets vs two parameter values in this example becomes quite complicated as you could imagine. Since "rolling back" is being done manually now, the opportunity to miss something because real.

Another issue that I think exists is for timeouts or severed connections. Does this still get rolled back? This is my understanding of why SET XACT_ABORT ON should be used so that in these cases, the transaction will roll back.

Thanks for your feedback in advance!


texts: How to query a postgresql database to determine it's cluster and version (and port)?

texts: I can quickly query the database name with SELECT current_database(); but I've been searching for way to also query the cluster name and version with no luck - hoping someone can suggest a solution.


texts: Passing parameters in MySql procedure with prepare execute

texts:
I am trying to write a procedure that has a location parameter, this parameter is later used in the queries inside the procedure. Also, I want to be able to limit the query results dynamically.
I have never done anything similar, so I read the appropriate documentation and studied some examples. My code is the following:
DROP PROCEDURE IF EXISTS lower10_avg_price ;
DELIMITER $$
CREATE procedure lower10_avg_price (loc varchar(45))
BEGIN
SET @a = (SELECT ceil(COUNT(*)/10) FROM UNION_SALES WHERE location = loc and current_Price >5000);

SET @quer = concat(
'SELECT ROUND(avg(prices.price),2)
FROM
(SELECT current_Price as price FROM UNION_SALES WHERE location = ', loc,' and current_Price >5000
ORDER BY current_Price ASC LIMIT ?) as prices;');

PREPARE STMT FROM @quer;
EXECUTE STMT USING @a;

END $$
DELIMITER ;

The error I get when I call the procedure is:
CALL lower10_avg_price('Αρχαία Αγορά            ');

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Αγορά             and current_Price >5000 ORDER BY current_Price ASC LIMIT ' at line 3
Can someone please help me understand what is causing this error?
Thank you for any suggestions!


texts: Populate monthly table from quaterly data

texts:
I have below excel file that I am loading to ssis. The problem is that the granualrity for the date dimension is by month. Is there a way to load the data so that for Jan, Feb & Mar the value is -0.10409. April May June is 0.662643 and so on

Cheers

Aidan


texts: Multiple columns VS one big-capacity-column?

texts:
I have a table like this:

// table_A
+--------+---------------+------------+
|   id   |  short-text   | long-text  |
+--------+---------------+------------+
 int(11)    varchar(200)      text         // <= type

Note1: Always one of sort-text or long-text is NULL. Never both of them aren't containing data. Like this:

+--------+---------------+------------+
|   id   |  short-text   | long-text  |
+--------+---------------+------------+
| 1      |some character |  NULL      |
| 2      |some character |  NULL      |
| 3      |some character |  NULL      |
| 4      |some character |  NULL      |
| 5      |some character |  NULL      |
| 6      |NULL           |  some text |
| 7      |some character |  NULL      |
| 8      |some character |  NULL      |
| 9      |some character |  NULL      |
| 10     |some character |  NULL      |
| 11     |some character |  NULL      |
| 12     |NULL           |  some text |
+--------+---------------+------------+

Note2: As you see in the above table example, always there is 5 rows which long-text is NULL and then 1 row which short-text is NULL.

Now my question is, that above structure is better or creating just one big capacity column (type = text) instead of both those columns? something like this:

// table_B
+--------+---------------------+
|   id   | short-and-long-text |
+--------+---------------------+
 int(11)         text                // <= type

And here is table containing data:

+--------+---------------------+
|   id   | short-and-long-text |
+--------+---------------------+
| 1      | some character      |
| 2      | some character      |
| 3      | some character      |
| 4      | some character      |
| 5      | some character      |
| 6      | some text           |
| 7      | some character      |
| 8      | some character      |
| 9      | some character      |
| 10     | some character      |
| 11     | some character      |
| 12     | some text           |
+--------+---------------------+

Well, which one is more efficient and optimize? table_A or table_B ?

Note3: I always looking up by ID.


texts: How to copy migrate data to new tables with identity column, while preserving FK relationship?

texts:
I want to migrate data from one database to another. The table schemas are exactly the same:

CREATE TABLE Customers(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY,
    (some other columns ......)
);

CREATE TABLE Orders(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY,
    [CustomerId] INT NOT NULL,
    (some other columns ......),
    CONSTRAINT [FK_Customers_Orders] FOREIGN KEY ([CustomerId]) REFERENCES [Customers]([Id])
)

The two databases have different data, so the new identity key for the same table would be different at the two databases. That is not a problem; my goal is to append new data to the existing one, not complete replacing all data of the entire table. However I would like to maintain all parent-child relationship of the inserted data.

If I use the "Generate Script" feature of SSMS, the script would attempt to insert using the same ID, which would conflict existing data in the destination database. How can I copy data using database scripts only?

I want the identity column at the destination to continue normally from its last value.

Customers does not have any other UNIQUE NOT NULL constraint. It is ok to have duplicate data in other columns (I'm using Customers and Orders just as an example here, so I don't have to explain the whole story). The question is about any one-to-N relationship.


texts:
I have a string column that I want to be part of a multi-column index in a large table in MySQL. There can be only a few different values in this column. If I knew these values in advance, I could make this column integer and a foreign key to another small table that lists all possible values. But I don't know them in advance.

Does it make sense to index on a string column: i.e. does MySQL somehow avoid repetition of the index column value for each row when storing internally?


texts:
We have a reporting SQL (2014 R2 Enterprise) system with a Reporting and Staging DB on it. The reporting DB is pre-provisioned at 4 TB across 8 data files. Currently it contains just shy of 1 TB of data and growth is marginally slow.

We need to reclaim 2 TB of that pre-allocated space on the SAN...

Since the data files are pre-grown, I assume I need to just change the file initial size and then perform a shrinkdb operation. I know the concerns and issues involved in a shrinkdb, and I just reviewed the article written by @BrentOzar (https://www.brentozar.com/archive/2017/12/whats-bad-shrinking-databases-dbcc-shrinkdatabase/) about it.

Given the pre-allocated file size versus the actual usage, should I be less worried about the fragmentation consequences?

On a side note: I love the tag descriptor when entering 'Shrink' as a tag...


texts: Consequences of tnsnames.ora and sqlnet.ora being unavailable

texts:
Our database servers are configured with the tnsnames.ora and sqlnet.ora files centrally located on a shared network drive. In every ORACLE_HOME/network/admin directory we have a symbolic link pointing to the centrally-located tnsnames.ora and sqlnet.ora files.  I want to understand the consequences of these files becoming unavailable in the case of a loss of connectivity to the network share.

Are these files used by any database processes, or only by client programs like SQL*Plus?
If these files became unavailable, would existing client connections be affected, or only new connections?
Would database links using the TNS aliases be broken while the tnsnames.ora file is unavailable?


texts: Removing duplicate entries for each hour

texts:
I recently asked the same question about MySQL, which has since been migrated to Postgres.
Old question.

To sum it up:

I have a table with an id (type UUID), a timestamp, and some text (row3). How do I retain only one entry per hour for each distinct value of row3?

What I tried is this:

INSERT INTO log_table
SELECT * FROM table1
WHERE id IN (
    SELECT DISTINCT ON(id) id
    FROM table1
    GROUP BY row3, EXTRACT(HOUR FROM "time"), id
);
TRUNCATE table1;

But this, unfortunately, inserts every row from table1.


texts: Extract mdf & ldf files from backup file?

texts:
Is there a way to extract SQL Server files (.mdf & .ldf) from a backup file (.bak) to a physical location, without restore database?

I need a restore query or script to restore files from bak file only, no attaching to server.
Please don't speak about detaching query after restore.


texts:
Following the MongoDB Sharding Documentation, I start a mongos using a configuration file with the specifics of my config server.

It seems rather straightforward, yet I am running into an issue. I start mongos on one machine (lets say its ip address is "some_ip"):

$ sudo mongos --config /etc/some_config.conf

And I try connecting to its shell from another machine, but I get connection refused error.

$ mongos some_ip:xxxxx

Here's part of my some_config.conf file:

    net:
      port: xxxxx
      bindIp: 0.0.0.0

   sharding:
      configDB: <configReplSet>/<ip_of_config>:27019

I'm using 0.0.0.0 for testing purposes. I made sure that the ip_addr and port I'm connecting to matches the ip of the host for the mongos and the port I specified in the net.port param. I also opened up this port in my firewall for ingress/ egress connections.

Did I miss anything ? (NOTE: ** I edited post for further clarification)

UPDATE

(note: i replaced the actual ip addr with "ip_of_config")
This is what my log file for my mongos says when I start mongos.

2018-05-02T05:57:48.533+0000 W NETWORK  [monitoring keys for HMAC] Failed to
connect to <ip_of_config>:27019, in(checking socket for error after poll), reason:
Connection refused
2018-05-02T05:57:48.533+0000 W NETWORK  [monitoring keys for HMAC] Unable to
reach primary for set crs0

Now my config server is up and running fine, but its log file shows that it accepts multiple connections from my mongos server, but these connections close almost immediately. Also there are a few warning logs:

W SHARDING [Balancer] Balancer settings could not be loaded and will be
retried in 10 seconds :: caused by :: ReadConcernMajorityNotAvailableYet:
Failed to refresh the balancer settings due to
ReadConcernMajorityNotAvailableYet: Read concern majority reads are currently
not possible.

W SHARDING [shard registry reload] cant reload ShardRegistry  :: caused by ::
CallbackCanceled: Callback canceled


texts: Is it possible to rebuild a dropped index?

texts: Is it possivle to drop and index, and rebuild it, instead of disable it and rebuild?


texts: PostgreSQL update JSONB without jsonb_set

texts:
Would there be any drawbacks to using a normal update statement to update a json(b) column like so:

update "events" set "properties" = '{"type":"graph"}'

Instead of using the jsonb_set function provided by PostgreSQL, which would turn into this statement:

update "events" set jsonb_set("properties", {'type'}, 'graph')

When using eg. an ORM, and calling .save() on a Model for which you've just updated a JSON field, the first method will be called, but as there is no mention of this way of doing things in the  PostgreSQL documentation, I fear this may have some drawbacks.

Given that I'm not incredibly familiar with anything regarding performance in databases, I thought I'd come and ask a question here.

Thanks in advance!


texts: Write a slow SQLITE query to test timeout

texts:
Is it possible to write a 'simple' SQLite query that will take more than 5 seconds to run.

I am writing some unit-tests in my application to log slow queries, to that end, I would like a slow query that I know will take more than 5 or 10 seconds to run, (and create a warning to be raised/logged).

I was thinking of creating 10 tables with 10 entries in each table and doing a join select on all the table.
But I am not sure if that is a good way of testing a slow select, (rather than a large return set).


texts: Disabling TLS 1 & 1.1 kills Biztalk 2016 fp3 with MSDTC on SQL 2016

texts:
We are mandated to harden the TLS/SSL and use TLS 1.2.

Upgrading Biztalk to 2016 fp3 should default to TLS 1.2 and it does - for outging traffic. The problem is that I cannot get MSDTC to call back with TLS 1.2.
If TLS 1.0 and 1.1 is disabled for Incoming on the biztalk server BT does not start.

I am unable to find any information on this.


texts: How to pivot a table for a set number of combinations?

texts:
Ok so I have a table of components and a colleague needs some data from this table in a different format, now I could do it with some python or java code but wondered if there is a simpler way in MS-SQL with a PIVOT maybe?

Components Table:-

ID_RECIPE  COMPONENT    QTY
   1          CPA       1.00
   1          CPC       0.50
   2          CPA       10.0
   2          CPF       7.50
   2          CPH       3.00
   2          CPJ       2.25
   2          CPP       0.25

There is never more than 5 components to a recipe so I want to be able to output something like this:-

ID_RECIPE  COMP_1  COMP_2  COMP_3  COMP_4  COMP_5  QTY_1  QTY_2  QTY_3 QTY_4  QTY_5
   1        CPA     CPC     NULL    NULL    NULL   1.00   0.50   NULL  NULL   NULL
   2        CPA     CPF     CPH     CPJ     CPP    10.00  7.50   3.00  2.25   0.25

Any ideas would be appreciated.


texts:
I am developing Point of sales software, which have multiple branches and one main branch.
Main branch is web based while all other branches (9 now, which may exceed) are desktop based.
Client required that all branches must sync main branch whenever internet connectivity is available.
So my question is that which sync functionality should i use?
I have not much idea about syncing as never work before on syncing.
Some of basic requirements are as follow:
Main branch can purchase or sell any item for any branch and stock + accounts will be hit.
Other branches can purchase and sale for them not for other branches but they can see other branches stock, their customers and accounts.
Remote branch can move stock to any branch.
These are some basic requirements which may change according to clients wish.
So kindly guide me how can i sync complete data bases. Should i use my own logic or some builtin functions.
Thanks


texts: What SQL Server database version do I need?

texts:
I am not a database experts but part of the project I am currently interested in accepting requires to do a DB replication.

The system will have many outlets... says Site A, Site B, Site C, etc..

All sites will have a local DB, but only one of the site (says Site A) DB will be a central DB..

Other sites will push their data to Site A once a day (through intranet)....and it's only one way replication.. i.e. Site B does not need to receive data/update/etc from Site A or Site C... so basically Site A will be a hub and other sites will be client to Site A and they only need to push their data to Site A....

What kind of SQL Server edition for each of the sites (assuming that the size of the db is not so big - 10GB will be enough) I am thinking to use SQL Server 2008 R2.

Also, What kind of replication does it require? Merge-replication?

from my understanding so far, I need

Site A: SQL Server Standard (or Enterprise)
Site B, C, etc.: SQL Server Express


texts: Inserting Data using Cursor but check if the data already in the table

texts:
I want to check if data already exist in a table before I do an insert using a cursor. So the cursor will pick a record, check that record doesn't exist in the table. IF it Doesn't EXIST then Insert, If it does EXIST jump that row (Avoid the insert) and continue to FETCH the next row.
What is the best way to do this? Is this even possible with a cursor?
Thanks everyone for the rapid response. Just to add some meat to the bone. I am querying customer data to send retention emails to customers that have been inactive for a certain number of days. For example customers that haven't purchased anything for 30 days since their first purchase will receive an email just to check up on them.
Once I have this data, I'm passing it to a Stored Procedure (don't have control over this SP) that requires it to be in a certain format and from there an email is sent out. I also have a log table that tracks all the emails data that I send to this SP. I want to check this log table for the customerID and the email template that I have sent to the SP in the past to avoid sending the same email template twice. Because I have to send the data in a certain format to the SP, I am forced to use a cursor so that I can format each row before I send it out. So I want to check the data at that point where am formatting it.
Snippet of the script:
OPEN CURSOR_CustomerID;
FETCH NEXT FROM CURSOR_CustomerID
INTO @CustomerID,@Name, @OrderNumber,@Language, @FromEmail, @Email,@templatetouse
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @MessageDetails ='
    [ {"Key":"&lt;*CustomerID*&gt;","Value":"'+LTRIM(@CustomerIDr)+'"},
    {"Key":"&lt;*Name*&gt;","Value":"'+RTRIM(@Name)+'"},
    {"Key":"&lt;*OrderNumber*&gt;","Value":"'+RTRIM(@OrderNumber)+'"}   ]'
EXEC SendAnEMail @Email,NULL,NULL,@language,@FromEmail,@Templatetouse,@CustomerID@MessageDetails

Exec Insert_Email_Log @PCustomerID, @Name, @Email, @Templatetouse
FETCH NEXT FROM CURSOR_CustomerID
INTO @CustomerID,@Name, @OrderNumber,@Language, @FromEmail, @Email,@templatetouse
END
CURSOR_CustomerID;
DEALLOCATE CURSOR_CustomerID;
END


texts:
We are currently running our database server on SQL Server RTM; i.e. no service packs or cumulative updates have been installed.

We need to install the latest Service Pack for SQL Server.

I had the following questions based on this subject, I was told I needed to stand up new servers and transfer the databases to that server.

Will installation of a SQL Server service pack affect any of our production databases? We will do this in test first but still wanted to ask.
Do we need to restore the databases, after installation of a service pack? I will be doing a backup of the databases before we apply the SP, just want to know how it affects the database.
Are there any particular prerequisites we need to follow before the installation of service packs? Backup of course, but are there any others?
How can I determine if there are any known bugs in a given service pack? Should any Cumulative Updates be installed after the service pack?
Can I directly jump on to install SP3, instead of installing SP1 or SP2 first?


texts: Install MariaDB 10 on Ubuntu without prompt and no root password

texts:
Pretty much the same as this question but I don't want to have a root password (this is just a dev machine).

Here's what I've got:

export DEBIAN_FRONTEND=noninteractive
sudo debconf-set-selections <<< 'mariadb-server-10.0 mysql-server/root_password password PASS'
sudo debconf-set-selections <<< 'mariadb-server-10.0 mysql-server/root_password_again password PASS'
sudo apt-get install -y mariadb-server

This will install MariaDB silently but it will set the root password to "PASS". If I delete that it does a weird partial install because it's still trying to prompt me.


texts: Sync two Oracle production server databases

texts:
I have an Oracle database that runs a 6 hours batch job every day. This process slows down performance during the 6 hours timeframe.

Is there any method that I could use to build another server that runs the batch job, and once is done, sync the data to the production server? (The time taken must be shorter than 6 hours.)


texts:
I have installed XAMPP 5.5.30 for Linux(64 bit), which consists of phpmyadmin version 4.5.1. For the first time when I went to phpmyadmin, saw a warning at the bottom saying

  The phpMyAdmin configuration storage is not completely configured,
  some extended features have been deactivated. Find out why.
        Or alternately go to 'Operations' tab of any database to set it up there.

I didn't give much attention to that. I tried creating a database. At that time I got another error,

  Table phpmyadmin.pma__tracking doesn't exist in engine

I tried to create the database with the same name again and this time the error was

  Can't create database,database exists

So I refreshed the list of databases and my new database was there and when I clicked on it a "Loading..." loader appeared and it remained there forever. So I tried opening the database in a new tab and this time I got,

  1932 - Table 'phpmyadmin.pma__column_info' doesn't exist in engine

After some research I came to know that all these tables (that did not exist) are a part of the phpmyadmin database. So I clicked on it and I got

  Table phpmyadmin.pma__tracking doesn't exist in engine

I am stuck now. As the phpmyadmin database cannot be selected I cannot add or edit any tables in it. Somebody please suggest a way to fix this issue.


texts: How to prevent Deadlock on SELECT queries?

texts:
I have a multi-session DB.
Few sessions inserting many rows to the DB. and one session selects from this table.

Sometimes I'm getting a deadlock on the SELECT query and I don't understand why.

For example: Assume this table

Create Table t1([id] int identity(1,1), [column] varchar(max))

Insertions go like this: (I'm doing it from an 'after' trigger)

Begin Transaction
Insert into t1 WITH (TABLOCKX) ([column]) values ('value 1'), ('value 2') ... ('value N')
Commit Transaction

The selection goes like this:

Select TOP 10 [id],[column] from t1 order by id

The lock on the insertions meant to keep the sequence of session insertions in the DB.

Each time I lock only one table in a transaction. I've tried to recover the deadlock with one session inserting and second one selecting but I've failed.
Before I've added the TABLOCKX I've never got the deadlock.

So my questions are:

How come a deadlock possible? (Maybe in a nested transaction?)
Is there's an option in MSSQL to do a write lock in a manner to not block the selects?
Any idea to prevent deadlock? (but keep session insertion sequence in the DB without changes...)


texts: Remove duplicate value when we use rollup


texts: MySQL Monitoring Script in Shell or Perl for Linux

texts:
I need a MySQL Monitoring Script in Bash or Perl for Linux.

I found some but they are quite big with a lots of checks that are not
useful at all for me and in this case I should customise the script
a lot.

Any idea for most important parameters of MySQL monitoring in a Shell or Perl
script?

Following parameters should be monitored:

qcache_hits
SHOW GLOBAL STATUS;
SHOW SESSION STATUS;
slow_queries
max_connections
key_buffer_size
mysql availability  STATUS
Error log
SHOW ENGINE INNODB STATUS;
max_used_connections
Handler_read%


texts: What permissions should the account for MS SQL Server Agent have?

texts:
I'm in a situation where I need to change the account for the Server Agent of SQL Server from NETWORK SERVICE to an AD domain account. All I'm doing is making the domain account sysadmin in the instance of SQL where the Server agent is. I'm not making it a Windows administrators.

There are currently jobs running there and I really don't want to make crash.

What should I take into account?


texts: Unable to migrate local database to RDS instance

texts:
I recently created an MySQL instance on Amazon RDS. Unfortunately, I'm not able to connect to the instance from the MySQL command line. I will brief you the details.

1) I want to move/migrate my MySQL database from local machine to this RDS instance.

I did a bit of searching in stackoverflow & found few links.

https://stackoverflow.com/questions/11731714/how-do-i-import-a-local-mysql-db-to-rds-db-instance

https://stackoverflow.com/questions/18565634/how-to-import-mysql-dump-into-amazon-rds?rq=1

http://trisummittechnologies.com/importing-mysql-data-into-rds/

https://stackoverflow.com/questions/11769605/php-site-not-connecting-to-mysql-on-amazon-rds

2) I have an RDS instance with the following details.

Endpoint: end.awsabc.com
My RDS db master username: iammaster.
I don't know the password.
RDS db name: rdsdb
local machine db name: localdb
name of SQLdump file of localdb: localdump.sql

3) These are the commands that I used in command prompt:

mysql -h end.awsabc.com -u iammaster-p  rdsdb< D:\localdump.sql

ERROR 2003: Cannot connect to MySQL server on abcdtrash.rds.com

Then I tried another method: This is the command I typed in the command prompt:

C:\wamp\bin\mysql\mysql5.6.17\bin>mysqldump -u root localdb| mysql --host=end.awsabc.com --user=iammaster rdsdb

Error: Access denied.

Is this because I don't have a password?? Can anyone tell me how to accomplish the task?


texts: SQL Server - Service User Accounts and Licensing?

texts:
Maybe this is the wrong place to post, since its not necessarily sql server related, but here goes.

If I want to setup some service user accounts in the domain to use to run sql services like the sql server instance itself, sql agent, and others, do I need to be worried about using up user licenses for them?

I believe we are currently using CALs for active directory. I just don't know if this would apply because they aren't real users, just service accounts, but I also don't know if active directory will actually differentiate between the two.

Thanks


texts: Cube with 2 measure groups (with different relationships to dimensions) returns too many dimension members in reports

texts:
I have a cube that contains stock-take data from retail operations. It has 2 measure groups - one containing the number of stock units counted in each stock-take batch (which is related to the Product, Batch and Time dimensions), and another containing product prices (which is just related to the Product dimension).

The measures in the prices measure group use the Min and Max operators (i.e. they show the minimum or maximum price across the selected members of the Products dimension).

I seem to have configured the cube and dimensions correctly (the price measures display as expected) ... except in the following case:

If I query the cube in Excel and get Units by Time and Batch, I see the expected result (see image below, filtered on Time=2015, and hiding rows with no data), i.e. just the batches with Units fact data for the selected Time dimension members.

If I now add one of the price measures (without changing any filters), the Units total is unchanged (as expected) but I now see every member in the Batch dimension (see image below). This is a problem for end-users, because all they want is to see the prices for the product in the batches previously selected.

From a technical point of view, it kind of makes sense - the price facts have no relationship to Time, so cannot be filtered by my Time filter the way the Units were.

Is there anything I can do (in the cube design or Excel) to eliminate the Batch members that have no Units for the given filters? Note: I can't get the users to write MDX queries - report building needs to remain "point and click"!

EDIT 27/1 (adding extra screenshot to illustrate strange side-effect of setting Ignore Unrelated Dimensions = False per TomV's answer)


texts: Execute Queries Against CMS within SSRS Reports?

texts:
Is it possible to execute queries against a Central Management Server (CMS) using SSRS?

For example, I know of no way to tell a stored procedure that it is inherently supposed to call the CMS, so it would seem some sort of qualifier to indicate that you are querying CMS (and which CMS) would need to be specified.


texts: SQL Server Audit Changes in user permission

texts: Is there any way to know that which privileges user previously had? Previously SQL Server user had rights on some views in a given database. But now these have been revoked, is there any way to get audit logs? Who and when these rights have been revoked?


texts: SSRS 2008. subreport page breaking issue migrating SSRS 2005 reports over to 2008

texts: I'm moving over several hundred reports that were created in SSRS 2005 over to an SSRS 2008 server, and most run fine when copying the .rpt files over, but the reports that have sub-reports don't page break correctly and the reports look terrible.  Does anyone know of a way to correct this issue or do all of these reports need to actually be re-created from scratch in SSRS 2008?


texts: How to add an additional table over two one to many relations tables?

texts:
I have two tables TableA and TableB which have a relation of one to many

Later I am told that there must be an additional table that links the initial tables with additional information. To carry this out using the entity relationship model, I have in mind these two proposals:

Which of the two options would be the most recommended?
For this third table can you define some type of cardinality, with respect to the two tables already related?


texts: Protect table from wrong data using locks or transactions

texts:
I have two tables

flats

   id - primary key
   title - text

flats_reservations

   id - primary key
   flat_id - foreign key to flats(id)
   creator_id - foreign key to users(id)
   is_reserved - boolean
   created_at - current_timestamp

If flats_reservations has these rows

id=1, flat_id=12, creator_id=5, is_reserved=1, created_at=2016-10-10 00:00:00
id=2, flat_id=12, creator_id=5, is_reserved=0, created_at=2016-10-10 00:00:05
id=3, flat_id=12, creator_id=4, is_reserved=1, created_at=2016-10-10 00:00:10

Then only user with id=4 can remove reservation (e.g. insert following row (is_reserved=0)

id=4, flat_id=12, creator_id=4, is_reserved=0, created_at=2016-10-10 00:00:10

No other user can make this. At any point of time only one user can reserve flat.

Can I use mysql transactions to ensure that two users at the same time can not reserve flat?

I want to be sure, that following rows can not be inserted at once

id=5, flat_id=12, creator_id=4, is_reserved=1, created_at=2016-10-10 00:00:15
id=6, flat_id=12, creator_id=4, is_reserved=1, created_at=2016-10-10 00:00:15

In application I use following queries in transactions

# transaction start
select is_reserved from flat_reservations where flat_id=12 order by created_at desc limit 1;
# if is_reserved == 0 or query returned 0 rows then
insert into `flat_reservations` (`flat_id`, `creator_id`, `is_reserved`) values (12, 4, 1)
# commit

Is there some way to lock table during execution of these two queries and only when flat_id=12?

Users must be able to reserve other flats (e.g., where id is not 12).

If it is not possible then should I lock whole table for writing?


texts:
I am trying to figure out how to find some information about a table that received a update. The database has logs, but it is a old operation (more than 5 months, and we store only 3 months of log).

We have the wal enabled, i am trying to think if there is a way to go to the past in the wal and try to found some information about it, but i know that will be probably impossible.


texts: Putting a condition related to a table in JOIN is better?

texts:
I have seen this a lot that people are putting table related condition on JOIN clause. See this example,

SELECT i.Name
  FROM sys.TABLES AS tbl
  INNER JOIN sys.indexes AS i
    ON (i.index_id > 0 AND i.is_hypothetical = 0)
    AND (i.object_id = tbl.object_id)
  WHERE (i.is_unique = 1 AND i.is_disabled = 0)
    AND (tbl.Name = 'Warehouse')

SELECT i.Name
  FROM sys.TABLES AS tbl
  INNER JOIN sys.indexes AS i
    ON (i.object_id = tbl.object_id)
  WHERE (i.index_id > 0 AND i.is_hypothetical = 0)
  AND (i.is_unique = 1 AND i.is_disabled = 0)
  AND (tbl.Name = 'Warehouse')

The query is same but the difference is that,
First one is using (i.index_id > 0 AND i.is_hypothetical = 0) in JOIN clause where second one using the same in WHERE clause. Is this impact on query or performance?


texts: Do I need to change the delimiter when creating a procedure using PHP MyAdmin?

texts:
I know I need to change the delimiter when manually creating a MySql Procedure but I can't find evidence on how to do it when using the PHP MyAdmin new procedure form or is it even necessary to change it when using this form? Can anyone help?

Thanks in advance.


texts: How are the following situations handled in MySQL

texts:
Q1. What happens if two rows are inserted simultaneously in a table where a column is marked unique and both rows have same value for that column.

Q2. What happens if a row is being updated and at the same time another query tries to delete it.


texts: Get total amount of time between records when changed state

texts:
I have a table, showing the status (healthy, broken) for each device at a given timestamp. I need to get the total amount of time of a whole breakdown.

So the data is stored like this:

device_owner   device_id    timestamp          status
owner1         device_1     2001-01-01 09:00   0
owner1         device_2     2001-01-01 09:15   0
owner1         device_1     2001-01-01 09:30   1
owner1         device_2     2001-01-01 09:45   1

and so on.

The example above shows

device_1 was down from 09:00 to 09:30 (total 30 mins),
device_2 was down from 09:15 to 09:45 (total 30 mins)

What I need

Need to calculate the total amount of time for the whole breakdown for owner_1 will be 45 mins (from 09:00 to 09:45) instead of 60 mins (30 + 30).

device_owner   total_breakdown_min
owner_1        45

What I've done

Currently I can define a breakdown time for each device separately (device_1 = 30mins, device_2 = 30mins). For this I transformed the table using LEAD over partition by device_owner, device_id and date part from timestamp:

device_owner  device_id     timestamp        status lead_timestamp   lead_status
owner1        device_1     2001-01-01 09:00   0     2001-01-01 09:30     1
owner1        device_1     2001-01-01 09:30   1     NULL                 NULL
owner1        device_2     2001-01-01 09:15   0     2001-01-01 09:45     1
owner1        device_2     2001-01-01 09:45   1     NULL                 NULL

So the datetime difference between timestamp and lead_timestamp in sum gives me the total breakdown time for a device.

;WITH LeadStatus AS
(
    SELECT
        D.*,
        lead_status = LEAD(D.status) OVER (PARTITION BY D.device_owner, D.device_id, D.date ORDER BY D.datetime ASC),
        lead_timestamp = LEAD(D.datetime) OVER (PARTITION BY D.device_owner, D.device_id, D.date ORDER BY D.datetime ASC)
    FROM
        #DeviceStatus AS D
)
SELECT * FROM LeadStatus

It is the same device list for any owner. The data snapshot is to be taken between 2 dates (StartDate, EndDate).
The records are written to a DB each n seconds and also when the device status changes.


texts: What should I consider before I upgrade my SQL Server memory?

texts:
I want to upgrade the memory in my SQL Server.  What should I check first?

Version: SQL Server 2012 64-bit Standard edition


texts: Start Windows Service If Replication Fails

texts:
Setup:

We have SQL Server Management Studio 2005 SP3 on all of our Servers.
We have 1 Main Server, and 1 Secondary Server that has the database from the Main Server being replicated on it. (If it matters, this is a "Pull" replication instead of a "Push" replication)

Question:
Is there a way to use the Alerting tools in SSMS 2005 to start a Windows Service on the Secondary Server in the event of Replication Failure?

If not, is this feature available in a newer version of SSMS?


texts: How to remove duplicate records in MySQL?

texts:
Below is my SQL syntax to query for a given name Vincent Ho, and returning all the matching records associated with the name.

However, I would like to only keep the first 2 rows of the output and remove the last 2 rows as they are duplicates. Please advise.

SELECT *
FROM (
SELECT * FROM same_staff ss, staff s
where ss.staff_ID1 = s.staff_ID and staff_name = 'vincent ho'
) as Firstset
left join
(
Select staff_name, staff_ID, email from staff
)as SecondSet

ON Firstset.Staff_ID2 = SecondSet.staff_ID;


texts: PLPGSQL function(multiple column output)--gives error

texts:
create or replace function help_constraint(p_schemaname text, p_tablename text)
  returns table(constraint_type text, constraint_name text, delete_action text, update_action text, status_enabled boolean, status_for_replication text, constraint_keys text)
as
$body$
  SELECT
 case when substr(tc.constraint_name, 1, 3) = 'ck_' and tc.constraint_type = 'CHECK' then 'CHECK Table level '
when tc.constraint_type = 'CHECK' then 'CHECK on column ' || ccu.column_name
when tc.constraint_type = 'PRIMARY KEY' then 'PRIMARY KEY (clustered)'
 when tc.constraint_type = 'DEFAULT' then 'DEFAULT on column ' || ccu.column_name
else '' end AS constraint_type,
tc.constraint_name,null as delete_action,null as update_action,null as status_enabled,
null as status_for_replication,coalesce(cc.check_clause, ccu.column_name) as constraint_keys,null as data_compression,null as default_uid,null as partition_qty,ccu.column_name
from information_schema.table_constraints AS tc
left JOIN information_schema.constraint_column_usage ccu ON tc.constraint_catalog = ccu.constraint_catalog
 AND tc.constraint_schema = ccu.constraint_schema
 AND tc.constraint_name = ccu.constraint_name
left JOIN information_schema.check_constraints cc ON  tc.constraint_catalog = cc.constraint_catalog
 AND tc.constraint_schema = cc.constraint_schema
 AND tc.constraint_name = cc.constraint_name
WHERE lower(tc.constraint_type) in ('foreign key','primary key','check')
and lower(ccu.table_schema) = 'compare' and lower(ccu.table_name) = 'testpostgresql'
union all
SELECT 'DEFAULT on column ' || cc.column_name,
'DF_' || cc.column_name as constraint_name,null as delete_action,null as update_action,null as status_enabled,
null as status_for_replication,column_default as constraint_keys,null as data_compression,null as default_uid,null as partition_qty,cc.column_name
FROM information_schema.columns cc
WHERE (table_schema, table_name) = ('p_schemaname', 'p_tablename')
and column_default is not null;
$body$
language sql;

ERROR:  return type mismatch in function declared to return record
DETAIL:  Final statement returns text instead of boolean at column 5.
CONTEXT:  SQL function "help_constraint"


texts: What is a basic model for making a database with users and groups?

texts:
I'm trying to find the best way for a basic security system for a website.  I know I want users and groups.

I thought I'd have:

user_table
user_id
user_name
...

group_type
group_id
group_name
parent_id
...

group_table
id
user_id
group_id

First one is the user, second one is the group, and the third one is the intermediary table that connects the two.  One user has many groups.

Does this sound ok?


texts: Joining two tables with unrelated where clauses

texts:
I have two tables: (EMPAssignments) and (EMPloyees). Both have an employee id column.

EMPAssignments has columns for employeeid, assignment, and ftepercentage:

employeeid  assignment  ftepercentage
emp001      sales       0.5
emp001      shipping    0.3
emp001      marketing   0.2
emp002      sales       0.2
emp002      shipping    0.7
emp002      marketing   0.1
emp003      sales       0.5
emp003      shipping    0.5
emp003      marketing   0.5

EMPloyees has columns for employee id and enabled

employeeid  enabled
emp001      1
emp002      1
emp003      1
emp004      0
emp005      1

I want my query to return the assignment with the highest FTEpercentage or a blank assignment if they do not exist in the EMPAssignments table.  I should have rows for employees 1,2,3,and 5, not 4.

In the case of a tie, I don't care which assignment is returned as long as it is only one, but there should always be a row for every active Employeeid.

I really am not much with SQL but I have searched about and tried various things.

I can use this to get the highest ftepercentage for each employeeid in the EMPAssignments table.

SELECT "employeeid", MAX("ftepercentage")
AS top_assignment
from EMPAssignments
group BY "employeeid"

And I can use:

SELECT Employeeid
FROM EMPloyees
WHERE enabled !=0

to return the enabled employees.

But what I can't figure out is how to join the two because emp004 and emp005 don't exist in EMPAssignments.
My join attempts either return all the assignments for all the active employeeids, or the top ftepercentage for only those employeeids in the EMPAssignments table.

Can anyone point me in the right direction?

Thanks in advance,

Bill


texts: Scape single quote in dynamic query Oracle

texts:
I have a field street name that would accept names like : O'connor but I have tested with replace function that works fine, but not sure if this is going to cause an exception with numeric values(int). Since my query is built in a v_msql variable dynamically I want to set the value to replace the single quote with two single quotes.

select   replace( ISR.STREET_NAME1 , '''','''''')
           || chr(3) || isr.agency_code || chr(3) || ''
           from isr, isr_udf where isr.isr_no = isr_udf.isr_no and isr.isr_no = 'XXX-20160718-00022'   ;

So I am wondering if Replace would handle the scape correctly. I tested it and shows no problem. Are there other ways to  scape a single quote on street names?


texts: Create previously created MySQL database without .sql file

texts:
I had a bad server crash in which I was able to restore my MySQL files, but I was unable to do a dump in order to get the .sql file. In other words, I have each individual part that was saved in the database. (i.e "databasename.table")

So, I have all my information, but after resetting the server, I am now unable to put my database back online. When I copy the files over, phpMyAdmin and MySQL Workbench show the table names, but act as if they don't exist.

So the files are there, and the names are read, but the information is not there. (i.e phpMyAdmin says something like, "databasename.table does not exist") I am unsure of what to do and I hope I was able to describe it well enough for you guys to understand it as well. If anyone is able to help me, whether it be describing how I can compile those into a .sql, or moving them over for the new server to read them, I would greatly appreciate it.

Thank you!

Note: I have replaced the hard drive in the server, so I still have everything that was saved on that hard drive, but I am unable to get it to boot, so if you guys find that I need to go through it again and find some other files, that can be done.

What I've learned: Create constant backups of databases, it just makes sense that way...


texts: Hash function and custom C type for PostgreSQL

texts:
I am creating a custom type for PostgreSQL. The type is as follow:

typedef struct {
    unsigned long prefix;
    unsigned long long id;
} CustomType;

I have build all the in and out functions. Comparison functions and such, but I don't understand how to build the hash function. Since I will need it for hash joins. I had a look at the hash_numeric function in https://doxygen.postgresql.org/backend_2utils_2adt_2numeric_8c.html#a1358689e8be944cad3a3fad87eb237f1 and don't quite understand it.

How does a hash function works and what is its purpose?


texts: SQL Server, Allow remote connections to this server option. What does this do?

texts: Can someone clue me in on what this option actually does and what would it affect if I were to suddenly uncheck it in an established production environment?


texts: Multi-tenant reduce number of MongoDB connections

texts:
Our multi-tenant app has about 4000 databases per tenant and we are having a problem with performance because of too many connections. Creating MongoDB connections on each database is a heavy operation.
We tried the following approaches but still could not reduce the number of connections.

Sharding only applying for the datasets
Increasing the pool size already applied

How to reduce the number of MongoDB connections? Is there any way to split databases into 1000,1000?


texts:
This is my current query
SELECT
project_id,
release_id,
array_agg(DISTINCT version_bom_id) AS version_bom_ids,
sum(case when review_status='REVIEWED' then 1 else 0 end) AS reviewed_count,
sum(case when review_status='NOT_REVIEWED' then 1 else 0 end) AS not_reviewed_count,
sum(case when severity='BLOCKER' then 1 else 0 end) AS policy_blocker_count,
sum(case when severity='CRITICAL' then 1 else 0 end) AS policy_critical_count,
sum(case when severity='UNSPECIFIED' then 1 else 0 end) AS policy_unspecified_count,
array_agg(DISTINCT ignored) as ignored_array
--problem aggregating the policy_ids
--array_agg( DISTINCT policy_ids) FILTER (where policy_ids is not null) AS policy_ids,

FROM (

   SELECT vbc.review_status,
   vbc.version_bom_id AS version_bom_id,
   vbc.project_id AS project_id,
   vbc.release_id AS release_id,
   vbc.ignored as ignored,
   min(prrd.severity)  FILTER (where vbap.status = 'IN_VIOLATION') AS severity,
   array_agg(DISTINCT vbap.policy_id::UUID) FILTER (where vbap.status = 'IN_VIOLATION') AS policy_ids
   FROM
    version_bom_component vbc
    LEFT JOIN version_bom_entry vbe ON vbc.id = vbe.version_bom_component_id
    LEFT JOIN version_bom_active_policies vbap ON vbap.version_bom_entry_id = vbe.id
    LEFT JOIN policy_rule_rule_definition prrd ON prrd.id = vbap.policy_id
    GROUP BY version_bom_id, project_id, release_id, review_status, vbc.ignored
 ) t GROUP by project_id, release_id

The sub-query is giving me this result:
-[ RECORD 1 ]--+----------------------------------------------------------------------------
review_status  | NOT_REVIEWED
version_bom_id | 27
project_id     | 5468e2d4-e43b-4d83-b832-b654280584d0
release_id     | c29d7004-5678-49a1-8e4e-56ad3831a5a3
ignored        | f
severity       | BLOCKER
policy_ids     | {2074e6d1-3507-4fec-8a9e-f8b3c8c81caa,a9893b83-6444-400e-9ce0-f987156bbee2}
-[ RECORD 2 ]--+----------------------------------------------------------------------------
review_status  | NOT_REVIEWED
version_bom_id | 28
project_id     | 5468e2d4-e43b-4d83-b832-b654280584d0
release_id     | c29d7004-5678-49a1-8e4e-56ad3831a5a3
ignored        | f
severity       | CRITICAL
policy_ids     | {a9893b83-6444-400e-9ce0-f987156bbee2}
-[ RECORD 3 ]--+----------------------------------------------------------------------------
review_status  | NOT_REVIEWED
version_bom_id | 28
project_id     | 4859fdaf-5de6-42b7-9a6f-70354f24676c
release_id     | 1eb1ee65-49fc-44a9-b673-8b85b67eaf6d
ignored        | f
severity       |
policy_ids     |

I want my final output this way:
-[ RECORD 1 ]------------+----------------------------------------------------------------------------
project_id               | 5468e2d4-e43b-4d83-b832-b654280584d0
release_id               | c29d7004-5678-49a1-8e4e-56ad3831a5a3
version_bom_ids          | {27,28}
reviewed_count           | 0
not_reviewed_count       | 2
policy_blocker_count     | 1
policy_critical_count    | 1
policy_unspecified_count | 0
policy_ids               | {2074e6d1-3507-4fec-8a9e-f8b3c8c81caa,a9893b83-6444-400e-9ce0-f987156bbee2}
ignored_array            | {f}
-[ RECORD 2 ]------------+----------------------------------------------------------------------------
project_id               | 4859fdaf-5de6-42b7-9a6f-70354f24676c
release_id               | 1eb1ee65-49fc-44a9-b673-8b85b67eaf6d
version_bom_ids          | {28}
reviewed_count           | 0
not_reviewed_count       | 1
policy_blocker_count     | 0
policy_critical_count    | 0
policy_unspecified_count | 0
policy_ids               |
ignored_array            | {f}

I'm having trouble aggregating the policy_ids from the sub-query.
Notice that the policy_ids in the final result should be distinct. Can you anyone please help me out!
Thanks in advance


texts: truncate table for all db in sql server 2000

texts:
i need a script that will go to every db in server and if exsistes a table named "table1" will do

truncate table table1


texts: how to ignore the SSMS (sql server management studio) sql server ansi settings?

texts:
The options in Management Studio

On Management studio Tools->Options->Query Execution->SQL Server
there are options that are set and these prevail over the same options that are set on database level.

The Options at Database level

on management studio you right-click on a database and you can see what are the defaults for a number of options: (in yellow on the picture below)

using sys.databases you can get hold of these options using T_SQL.

The Options At Session Level

Using sys.dm_exec_sessions you can get hold of the options as they are set for the current session.

Questions

Is there a way to make sure the database setting will prevail over the management studio settings?

I thought about creating a plan, and use OPTION (USE PLAN)
but that has other consequences that is beyond the scope of this question.


texts:
Does Microsoft SQL Server support unsigned integer data types?

I am trying to learn a bit more about the data type choices and helping a friend in his database project, I know that MySQL does.


texts: Azure Sql database AAD user not getting created

texts:
I need to create an AAD user for a Windows security group that was recently synchronised with Azure AD. This security group has spaces in its name.

I am using the below SQL script but it keeps failing because of the spaces:

Create user [security group name@xyz.com] from external provider;

If I look for this group under active directory in portal, I don’t see its corresponding UPN. Does it mean that all accounts sourced from on-premise do not contain UPN?


texts: Normal form for a relation where the number of tuples having the same value in a column is the same for all the possible values in that column

texts:
Is there a normal form for a relation that would permit the following table :

id | alpha
----------
1  | a
1  | b
1  | c
2  | g
2  | u
2  | k
.
.
.
n  | x
n  | y
n  | z

But not this table :

id | alpha
----------
1  | a
1  | b
2  | g
2  | u
2  | k
.
.
.
n  | x
n  | y


texts: How to view tuples changed in a PostgreSQL transaction?

texts:
BEGIN;

   INSERT INTO foo_table(foo_column) VALUES('a'),('b'),('c');

   -- SELECT * FROM pg_stat_activity WHERE ...
   -- shows records with a flag if they have been added/deleted/updated

END;

Is there a way to view the uncommitted inserts/deletes of a transaction for a given table in PostgreSQL 9.x? This is purely for visual confirmation.

While my initial thought is no (just perform a SQL-Select of the table), certain users wanted to visualize differences within the transaction.  I could see how this might be helpful if having a lot of savepoints and transactions. Thus, it was worth asking to the community at large.


texts: Putting the database in backup mode before making file system changes

texts:
We are planning to make a small change in the Linux file system. To be on the safe side, I thought I would take a SAN-level snapshot of all the drives beforehand. I was thinking I should put the Oracle database in backup mode:

Alter system archive log current;
Alter database begin backup;
-- after the SAN snapshot finish--
Alter database end backup;
Alter system archive log current;

Someone said this command is deprecated from 9i.

My question is, if I go ahead and use this, will it be ok? Or is there another way to do this?

Note: I don't want to use RMAN's full backup as this will take 6 hours.


texts: Old user removed, now having DEFINER errors

texts:
Old users were removed from the mysql.user table and now while running some views, I got the error that the user@host does not exist. Also while taking a dump, a view is unable to be dumped due to user not existing. I have been researching on the best way to update definers when I remove an old user from the mysql.user table. Of course, the old users were DEFINERS in one view, or sp. A quick fix I did was to take a dump of the db with a regex that removes the definers from the dump file and restore the db - this way the new definer will be the user performing the restore operation.
Also I found 2 other ways online (https://www.adminbirds.com/mysql/how-to-change-the-definer-for-views/) - I need to verify this method.

to change definers in views: SELECT CONCAT("ALTER DEFINER=youruser@hostVIEW ", table_name, " AS ", view_definition, ";") FROM information_schema.views WHERE table_schema='your-database-name'; this will generate the alter statement for manually changing the definer in the information_schema.views table.
To change definers in store procedures: UPDATEmysql.procp SET definer = 'user@%' WHERE definer='root@%' this will manually change the definer in the mysql.proc table. Although I read from MySQL doc page that It is not supported that the server will notice manual manipulation of this table.

So here is my question: If I run the above commands, will it affect the data? Is it safe to run? I know the ALTER statement only affects structure, but just want to be sure, since I will be running this in a prod environment as well. The UPDATE statement definitely changes the data in the mysql.proc table and I need to be sure this is safe to proceed. Please advise.


texts: Why is Oracle so much slower for small atomic inserts compared to Postgres? And what defaults can I change to remedy this?

texts:
If I create a brand new database from a Docker image and run a fresh database with the default settings.
CREATE TABLE "TEST_TABLE" (
    "ID" VARCHAR2(32 BYTE) NOT NULL ENABLE,
    "CREATED" TIMESTAMP (6) DEFAULT NULL NOT NULL ENABLE,
    "REFERENCE" NVARCHAR2(500),
    PRIMARY KEY(ID)
);

This insert statement takes between 15ms and 30ms to complete (after being run a couple of times).
INSERT INTO test_table VALUES ('1', '10-SEP-02 14.10.10.123000000', 'hello world');

However if I run the same query in Postgres it takes 2-3ms to complete (after being run a couple of times).
What defaults in Oracle cause such poor insert performance?


texts:
I was hoping to use user impersonation to setup a user that has permissions to modify DDL, and only when I need to deploy DDL changes would I impersonate that user.  This way I can use my windows integrated account to query data as needed, but cannot modify the database except when I explicitly impersonate that other user.

According to the MSDN article on impersonation, you can only execute impersonation if you are a sysadmin.

So the challenge is, if my default Login, we'll call it LoginAaron, is a sysadmin of the database, how do deny myself DDL write permissions, so that I cannot execute DDL without explicitly impersonating another user with ddl_admin role(we'll call user UserDdlDeploy)?

Or is there another elegant way to accomplish this?

This is not so much a security procedure, as it is more of a safety/precautionary procedure.


texts: With multiple files in a file group, when does data start being placed on the 2nd file in the filegroup?

texts:
In a previous question, it was suggested that I should add an extra "File" to my existing (Primary) Filegroup to easily add more disk space for my DB.

So I did that and nothing broke.

But I'm not sure when Sql Server will decide to stop putting data into the original File and start putting data over to the new File?

From @davidbrowne's excellent answer to my question, he mentioned:

  SQL Server uses a "proportional fill algorithm" when a filegroup has multiple files.

Okay, great - but when does it 'know' to start filling the other file?

Currently, this is what I have setup ... and please note the MAX FILE SIZE.

Do I need to give a MAX file size (e.g. LIMIT to 1T) for the original File which will then help Sql Server to 'know' when to start using the next (new) File?

There's currently 19GB left on disk F:\.


texts: What is the max length of oracle user passwords?

texts: I'm using the Oracle Verify Function 12c - the max password length seem to be < 30 charcters. Can I change that?


texts:
I am trying to implement some parts of MERGE in the MySQL driver in Drupal. Of course, Drupal has something but in truth it only works because the most frequent MERGE issuer just eats exceptions.

So, whatever we try, deadlocks occur. What we do, we start a transaction, then SELECT ... FOR UPDATE, try an INSERT and if it causes an 23xxx integrity error try an UPDATE instead. Deadlocks. We removed the FOR UPDATE cos we decided that for our use, it's OK. Still deadlocks.

I can't just switch isolation levels because READ COMMITTED needs row logging per SET TRANSACTION:

  As of MySQL 5.1, if you use READ COMMITTED [...] you must use row-based binary logging.

And per READ UNCOMMITTED also needs row logging. And here comes Binary Log Setting:

  To change the global binlog_format value, you must have the SUPER privilege. This is also true for the session value as of MySQL 5.1.29.

I can't require every Drupal setup to have SUPER nor we can say that Drupal is incompatible with statement based binlogs when that's the default and the most widespread.

INSERT ... ON DUPLICATE KEY is neither versatile enough nor is it deadlock free.

So what now?


texts: Will altering order of columns in GROUP BY affect performance?

texts:
I have a SQL query like this:

SELECT A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END), COUNT(D)
FROM SomeTable
GROUP BY A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END)

On a huge dataset the actual execution plan attributes most of the time to a node that corresponds to logical operation Aggregate and physical operation HashMatch.

Will changing the order in which the columns and conditions are listed in the GROUP BY list affect performance?


texts: SELECT COUNT of multiple column under the same table

texts:
Im blowing my brain on this one. I think I am making this harder than I need to. Im trying to run a mySQL query to SELECT a COUNT of members which have any values in any column. my example table is as such:

So far, I have got

SELECT COUNT(members)
FROM table_1
WHERE '1' IN column_1,
OR column_2,
OR column_3,
OR column_4

Please help, I am returning the craziest results

My results should look like

Member----------3


texts:
I need autocompletion for mysql console in Linux.

I found this solution.

Add this to .my.cnf. file in home folder.

[mysql]
auto-rehash

But it doesn't work, for ubuntu 19.04 and mysql 14.14.

And just one question, is it possible to do it for small characters? I mean 'select' instead 'SELECT', etc.

Thank you.


texts: How to select values consisting of numerals only?

texts:
I have a column like this:

WW0476091
YA0457514
547856232
856987452
254785W11

I want to get the values that contain numerals only (decimal digits from 0 to 9). For the example above, I want only these records to be printed:

547856232
856987452

How can I do that?


texts: Is a dropped (or altered) function still available inside already opened transactions?

texts:
I found

PostgreSQL - Are CREATE FUNCTION and DROP FUNCTION locking when used inside different transactions?

but there are no answers and isn't exactly the same as my question (though very similar).

Let's say I do the following:

Create a function myfunc()
Start a transaction from client A
Start a transaction from client B
In transaction B, use "create or replace function" to revise the definition of myfunc()
Commit transaction B
Call myfunc() from transaction A

What happens in step 6?  Am I calling the original function as defined in step 1?  Or the modified form from step 4 (committed in step 5)?

And if the function is dropped in step 4 rather than being modified, will step 6 fail or succeed?  (This is probably the same question but modifications may work differently.)

Where is the documentation about this?


texts: How to import db-sample-schemas into Oracle's DB?

texts:
I need to import some sample dump file for educational and testing purposes. I've found the next dump file and tried to import it using:

imp testuser/oracle file=/home/oracle/PurchaseOrders.dmp

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

IMP-00037: Character set marker unknown
IMP-00000: Import terminated unsuccessfully

Also, I've tried to import it using:

impdp testuser/oracle directory=dmp_dir dumpfile=PurchaseOrders.dmp full=y;

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31619: invalid dump file "/home/oracle/PurchaseOrders.dmp"

How could I import this dump into my test Oracle DB?

Thank you!


texts:
I have a SET type field in my table like:

car_features SET('Air Conditioning'
       ,'Power Windows'
       ,'Power Steering'
       ,'Moonroof'
       ,'Disk Brakes'
       ,'Power Seats'
       ,'Leather')

I'm trying to test this field in the order that the client gives to me in a input string,like:

                     "Power Windows,Moonroof,Leather,Disk Brakes"

then I want to test the field then give the result back to client like:

               +---------------+----------+---------+-------------+
               | Power Windows | Moonroof | Leather | Disk Brakes |
               +---------------+----------+---------+-------------+
               | 1             | 0        | 1       | 1           |
               +---------------+----------+---------+-------------+

according to whether the specific option is set in this field or not.

the question is,I can easily get single result like:

      select find_in_set(a_function_fetch_option_oneByone(theInputString),car_features)>0;

but I cannot combine every result into one just like I said...

somebody help me!!!


texts: column name as variable in sql statement

texts:
I have a MySQL database with a table that contains 4 columns, A, B, C, and D.
I need to access those fields dynamically i.e., instead of saying Table.col_A , I need to say Table.x where x can have the value of any of the columns A or B or C or D. I am getting x from the join with another table.


texts:
We have a 2-node clustered SQL Server 2014, Enterprise, environment for our data warehouse. Storage, network, and servers are all high performing (1tb mem, 32 cores CPU per node, all SSD)

We are trying to come up with where to put SSIS itself. Based on Microsoft documentation, it isn't recommended that it be clustered and, therefore, shouldn't be on the 2 nodes (though I did install it with the instances, we don't have to use if from there can could disable the services.) On the flip side, we don't really want to spend money on licensing of 8 vCPU's to put it on an available server in the same ecosystem (used for a proprietary ETL from vendor.)

What are the best practices and recommendations on how/where to install SSIS for a clustered SQL Server database environment?

Thank you,
Wes

EDIT: Microsoft discusses this here: https://docs.microsoft.com/en-us/sql/integration-services/service/integration-services-ssis-in-a-cluster but does not go over the recommended approach as an alternative.


texts: hide system databases sql server management studio

texts:
I have installed sql server express 2008 and also sql server management studio. I've successfully configured remote connectivity and created a database user and password.
my problem is that in the remote sql server management studio - i want the user to only see the database i assign to them and not see System Databases

I've noticed that the sa login also appears in the folder tree in my remote session which i do not want

How do I go about hiding the system databases as well as logins so that the remote connection only sees logins and database relevant to them


texts:
I try to generate unique identifiers for the ticket table's records.
A ticket belongs to a file record, which also have an identifier.
If the file's identifier is 'SOMEPREFIX/F01' then its tickets should be
'SOMEPREFIX/F01/PT001', 'SOMEPREFIX/F01/PT002', 'SOMEPREFIX/F01/PT003' ...

The architecture: JS Client, a node.js API with some ORM, but I am also able to run raw SQL statements. DB: PG 9.6

The problem: If the client send (almost) the same time a bunch of ticket create requests, the DB generates sometimes the same identifier.

Here is an sql query I generate dynamically in the API after the ticket is already inserted:

      BEGIN;
      UPDATE ticket SET
      ticket_number_index = (COALESCE((SELECT MAX(ticket_number_index)
                        FROM ticket
                        WHERE file_id = 'D530'), 0) + 1),
      ticket_number = CONCAT('SOMEPREFIX\F717\PT', TO_CHAR((COALESCE((SELECT MAX(ticket_number_index)
                        FROM ticket
                        WHERE file_id = 'D530'), 0)  + 1), 'fm00000'))
      WHERE id = 'D3571';
      SELECT * FROM ticket WHERE id = 'D3571';
      COMMIT;

I would assume that BEGIN and COMMIT makes the code atomic, and after the first UPDATE the next one can't produce the same identifier.
Although I have just saved saved two tickets (two POST queries started almost the same time by the client), and I ended up with 'SOMEPREFIX\F717\PT1' and 'SOMEPREFIX\F717\PT1'


texts: How to verify data integrity of data folders after data recovery in MySQL and PostgreSQL?

texts: I wonder if there is a way to verify data integrity of data folders from a data-recovered hard disk, both MySQL and PostgreSQL?


texts: Where to find documentation about the log reader procedures?

texts:
From the replication monitor in sql server transactional replication,
when I have a look at what has been replicated, from publisher to the distributor,
I get the following picture:

It does not show on the replication monitor, therefore running the internal procedures to get hold of what has been read from the transaction log and replicated to the distributor I use the following procedure:

use [master]
exec [distribution]..sp_MSenum_logreader_sd
@name = N'myserver-Product-102',
@time = N'20170410 16:36:16.917'

this gives me the following results:
(same data as on the picture above, plus more information)

Question:

I am struggling to find any documentation on the stored procedure that I use to find out what the log reader agent has been doing:
sp_MSenum_logreader_sd

where can I find information about this procedure?

I also would like to save this info into a temp table or table variable, so that when I spot an error_id  that is not zero
I can get extra info as you can see below, based on the error_id from the picture above.

-- error_id 5467351 comes from the above result set
use [master]
exec [distribution]..sp_MSget_repl_error
@id = 5467351


texts: Check a constraint after adding a Foreign Key

texts:
If I add a foreign key constraint with the following command:

ALTER TABLE [abc]  WITH CHECK ADD  CONSTRAINT [fk_some_col]
   FOREIGN KEY([some_col_id]) REFERENCES [xyz] ([some_col_id])

Is there any reason or advantage to follow it with this line?

ALTER TABLE [abc] CHECK CONSTRAINT [fk_some_col]


texts:
I want to see the size of a spacial index. I am usually using one of the following code blocks to get the indexes sizes of a given table, but none of them is working for a spatial index.

DECLARE @DataSource TABLE
(
    [TableName] SYSNAME
   ,[IndexName] SYSNAME
);

INSERT INTO @DataSource ([TableName], [IndexName])
VALUES ('[dbo].[Table]', 'Index');

SELECT OBJECT_NAME(INX.[object_id]) AS TableName
      ,INX.[name] AS IndexName
      ,INX.[index_id] AS IndexID
      ,8 * SUM(AU.[used_pages]) AS 'Indexsize(KB)'
FROM [sys].[indexes] AS INX
INNER JOIN [sys].[partitions] AS PAR
    ON PAR.[object_id] = INX.[object_id]
    AND PAR.[index_id] = INX.[index_id]
INNER JOIN [sys].[allocation_units] AS AU
    ON AU.[container_id] = PAR.[partition_id]
INNER JOIN @DataSource DS
    ON INX.[object_id] = OBJECT_ID(DS.[TableName])
    AND INX.[name]  = DS.[IndexName]
GROUP BY INX.[object_id]
        ,INX.[index_id]
        ,INX.[name]
ORDER BY OBJECT_NAME(INX.[object_id])
        ,INX.[index_id]

OR

CREATE TABLE #CompressionResults
(
     [object_name] SYSNAME
    ,[schema_name] SYSNAME
    ,[index_id] INT
    ,[partition_number] INT
    ,[size_with_current_compression_setting (KB)] BIGINT
    ,[size_with_requested_compression_setting (KB)] BIGINT
    ,[sample_size_with_current_compression_setting (KB)] BIGINT
    ,[sample_size_with_requested_compression_setting (KB)] BIGINT
)

INSERT INTO #CompressionResults
EXEC sp_estimate_data_compression_savings 'dbo', 'A', NULL, NULL, 'ROW' ;

SELECT CR.[object_name]
      ,IDX.[name]
      ,[size_with_current_compression_setting (KB)] / 1024  AS [current size (MB)]
      ,[size_with_requested_compression_setting (KB)] / 1024  AS [size after compression (MB)]
      ,([size_with_current_compression_setting (KB)] - [size_with_requested_compression_setting (KB)]) / 1024 AS [saved size (MB)]
FROM #CompressionResults CR
INNER JOIN [sys].[indexes] IDX
    ON CR.[index_id] = IDX.[index_id]
    AND OBJECT_ID(CR.[object_name]) = IDX.[object_id]
UNION ALL
SELECT CR.[object_name]
      ,'ALL'
      ,SUM([size_with_current_compression_setting (KB)]) / 1024  AS [current size (MB)]
      ,SUM([size_with_requested_compression_setting (KB)]) / 1024  AS [size after compression (MB)]
      ,SUM([size_with_current_compression_setting (KB)] - [size_with_requested_compression_setting (KB)]) / 1024 AS [saved size (MB)]
FROM #CompressionResults CR
GROUP BY CR.[object_name]

DROP TABLE #CompressionResults


texts: How to delete all records which are not referenced from other tables

texts:
I have a table to which a bunch of other tables has an FK reference. Is there any way of deleting records from this table only if they are not being referenced?
I know that I can left join the referencing table and check for null, but I have about 10 tables (more will be added) with FKs referencing this table, so it would be cool to have a generic way of doing it.
There are often not more than a handful of records I need to remove. I suppose I could loop and try to remove each record individually and protect each deletion with BEGIN/EXCEPT, but that is an ugly concept.
Does this kind of functionality exist in Postgres? Kind of a soft delete, or delete-if-allowed.


texts:
I am trying to install CU1 for SQL Server 2016 SP1 on a clustered node and it fails with an error that I am not able to find information on. In the summary log file it has the following:

  Instance MSSQLSERVER overall summary:
  Final result:                  The patch installer has failed to update the shared features. To
  determine the reason for failure, review the log files.
  Exit code (Decimal):           -2146233079
  Exit facility code:            19
  Exit error code:               5385
  Exit message:                  The given key was not present in the dictionary.
  Start time:         2017-02-14 10:02:56
  End time:                      2017-02-14
  10:03:18
  Requested action:              Patch

When I look in the detail log, I am not seeing anything that is helpful (at least to me). There are a lot of occurrences of these lines before the error,

"Sco: Attempting to..."
and
"Skipping service control for the following instance because it has one or more clustered features: MSSQLSERVER"

I installed this CU to another cluster without any issues. The only difference between them, the one that it succeeded on was 2016 Standard with SP1, and the one that does not work is 2016 Developer with SP1.


texts:
I have a short question, why do I use use master; to create a database?
Here is the example from the Microsoft documentation

USE master ;
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
    FILENAME = 'C:\Program Files\...\saledat.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
    FILENAME = 'C:\Program Files\...\salelog.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB ) ;


texts: WHERE IN query to very large table is slow

texts:
I currently have a table with around 300,000,000 rows with the following columns: user_id, item_id, item_value, and item_add_date

I also have a very long list of about 7000 user_id values which are associated with a date range.

I'm trying to query the really large table in order to obtain all rows where the user_id is in my list and where the item_add_date falls within the provided range.

My current approach is programatically looping through the list of ids and running the following query:

FROM all_items
WHERE user_id in [insert_user_id_programatically]
AND item_add_date >= [insert_start_date]
AND item_add_date <= [insert_end_date]

which returns the results I want but takes about one hour per query, and for 7000 queries would take almost a year to run. Is there a faster way of achieving this? I'm not familiar with SQL and do most of the data manipulation for this project in python normally.

EDIT:
Here is the output for EXPLAIN (ANALYZE, BUFFERS, VERBOSE):
https://pastebin.com/GYL8JWk4


texts: Why CONTROL permission on certificate to be able to use it?

texts:
As per https://technet.microsoft.com/en-us/library/ms190499.aspx, to open a symmetric key that is encrypted by a certificate, a user needs the CONTROL permission on the certificate.

I tested this and indeed, nor VIEW DEFINITION, nor ALTER, nor REFERENCES suffice.

I wonder why. Moreover, I wonder how secure it is if everyone needs the CONTROL permission (just to be able to use it), and as a consequence, can easily grant this permission to everyone else (confirmed by own test).

(I also tried granting CONTROL and denying all the others, but I guess that this doesn't make much sense because CONTROLoverrides the others. Or so I suppose. Please correct me if I'm wrong.)


texts: How to solve problem when oracle apex does not let me do anything with my created schemas even though I should have privileges?

texts:
I have a problem with with latest version of APEX, I have installed it on my pc, i logged in as and admin created workspace with schema, but when I log in to that workspace as a user with admin's privileges or developer's privileges and try to use SQL Workshop I get an error:

  User <MyUserName> has no privileges on the schema.
  Contact your application administrator.

When I try create a table with SQL command I get

  Schema "DB" does not exist in the database.  Please contact your workspace admin to set correct default schema for you.

even though user has been assigned that schema and it does exist.

When I try to create another schema for the workspace I can't and I get an error

  ORA-65096: invalid common user or role name

Even when I try create new workspace create workspace I get an error

  Error provisioning <workspaceName>.
  ORA-20001: Request 1246482779115523 could not be processed. PROVISION_COMPANY

but when I press return and press create workspace again it succeeds for some reason.

I have no idea what to do, how to solve this problem, I tried everything in settings: granting privileges, setting default schemas, creating new users, setting role as developers, yet the issues do not go away. Any help is appreciated.


texts: PostgreSQL update column in one table with value from another, based on key from first table

texts:
Sorry about the title, I wasn't sure how best to word this. My situation is this:
I have two tables:
paper_author [paper_id, author_id, author_name]
authors [author_id, author_name]

Right now, the author_name column in paper_author is NULL. I would like to select the author name from the authors table and insert it into the author_name column in the paper_author table. I've written the following query:
UPDATE paper_author pa SET author_name = (SELECT author_name FROM authors a WHERE a.author_id = pa.author_id);

Which I think will do what I want, but this is taking a very long time to run (days). For reference, the table paper_author has ~900M rows and the table authors has ~200M rows. The author_id is the primary key in the authors table. author_id has an index in the table paper_author. The rows in paper_author are not unique on author_id (i.e. each author_id may appear multiple times associated with different papers).
Is there a more efficient way to write this? Have I missed something?


texts: A login is a member of db_datareader and public roles only, but it is not read-only

texts:
I'm an accidental DBA in a small company that doesn't have a dedicated DBA.

I need to set up a read-only login/user for SQL Server 2008 database using SQL Server authentication that could be used by power users to run some ad-hoc queries/reports.

I found many posts and articles describing setting up a read-only user, for example:

How to grant users read-only access to all databases

https://www.itsupportguides.com/server-side-tips/sql-management-studio-how-to-create-read-only-users/

http://www.joellipman.com/articles/microsoft/sql-server/create-read-only-database-user-in-sql-server.html

I tried to follow these guides and made sure that the login in question is a member of only db_datareader and public roles, but I still can make changes to the data when I'm logged in as this "read-only" user.

For example, I can run this query in SSMS

UPDATE [dbo].[SoftwareRunLogs]
SET [RunDateTime] = '2010-10-29 13:31:06.133'
WHERE ID = 1

and it updates the row in a table:

(1 row(s) affected)

I expect such query to fail.

When I try to run a CREATE TABLE statement, it fails with the message CREATE TABLE permission denied in database ... I expect UPDATE, INSERT and DELETE statements to fail in a similar way, but it doesn't happen.

Maybe I inadvertently gave write permissions to that user, but I don't know how to check and where to look. I must have missed something trivial.

Any ideas?

There is a server login called ReadOnlyUser and here are its properties:

In the database there is a user called ReadOnlyUser and here are its properties:


texts:
I have a database where the owner is Login1 but the username dbo is assigned to Login - login2. I understand in order to change the login name for the username dbo to sa I will have to use the below
EXEC sp_changedbowner 'sa' but the problem here is that this will change the databasowner from Login1 to sa. How do I only change the dbo login name to sa and keep the database owner to Login1


texts: Syntax error on declaring a variable inside a transaction

texts:
I want to create a variable inside this transaction but I'm getting

  ERROR: syntax error at or near "DECLARE" LINE 4: DECLARE new_id INTEGER;.

The variable needs to hold the value of the id of the newly created message.
This seems to be the cleanest and race condition resistant way of doing so, but I don't know how to correctly declare the variable.

BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE new_id INTEGER;
INSERT INTO messages (author) VALUES ($author) RETURNING id INTO new_id;
-- Other statements..

COMMIT;


texts: Implementing application locks within SQL Server (Distributed Locking Pattern)

texts:
In my application I have to perform a distributed locking pattern. Because we already have an instance of SQL Server to use, we decided that it would be easiest to implement the locking at SQL layer of our web application.

A lock can be obtained based on numerous conditions including:

The type of lock requested
An arbitrary application identifier

For all intents and purposes, treat the above two conditions as int data types.

In this pattern, we wish to treat all of our locks as FIFO, which I believe the SERIALIZABLE isolation level will give us.

Here is how we propose to perform the "lock":

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

IF EXISTS (SELECT 1 FROM locks WHERE LockType = @LockType AND ApplicationIdentifier = @ApplicationIdentifier)
    BEGIN
        -- Awesome, the lock will be acquired
        INSERT INTO locks OUTPUT INSERTED.LockId VALUES (2,3)
    END
ELSE
    BEGIN
        -- Someone already has the lock
        SELECT -1
    END

SET TRANSACTION ISOLATION LEVEL READ COMITTED

And the "unlock":

DELETE FROM locks WHERE LockId = @LockId

So my question is two-fold:

Do I need to make the "unlock" SERIALIZABLE as well?
Are there any other approaches that I could use/anything that I have forgotten?

SQL Server can either be 2008/2012


texts: What is the purpose of the keyword "ID" in PostgreSQL?

texts:
I'm new to PostgreSQL and I ran into problems using the following query:

SELECT * FROM table1 WHERE ID = 1

  ERROR:  column "id" does not exist
  LINE 2: WHERE ID = 1

where ID is a column I added via a GUI-tool.
I then found out that ID is a keyword in my current version of PSQL and should be quoted. But I can't recognize what is this ID for.

On the linked page there is the following notice:

  It is important to understand before studying Table C-1 that the fact that a key word is not reserved in PostgreSQL (==true) does not mean that the feature related to the word is not implemented.

So, ID is not marked as a PostgreSQL keyword and there seems to be a feature connected to it, but I can't find out which. SELECT ID itself turns into the same error message as posted above.


texts: What are the reasons to create a key-less, index-less database?

texts:
TL;DR: why would you create a database with no keys of any kind (not even primary), and no indexes?

I joined a non-profit organization that was 14 months into migration to a custom built front-end for their financial processing.  The organization has about 60 staff.

I have 6 years of software development experience, so they trust me enough to give me access to their database.

Lo and behold, the front-end SQL Server production database has absolutely no keys of any kind and no indexes.  However, the interface actually works well.  The external company that created the system are relatively responsive in creating new features, and the system actually works.

I've been working there for about 6 months.  Most of the problems are relatively minor.  I haven't seen any catastrophic errors due to mistakes on the company's part.  Then again, there are no UAT (user acceptance tests) at all, and I have no idea how QA works at this external company.  I only have contact with a sales/technical liaison.

I highly suspect this company is simply fleecing the non-profit for maintenance fees in the future.  I got so sick of the slow DB that I added in a bunch of multi-column, non-clustered, non-unique indexes based on the most frequently used reports, and increased the DB speed by probably 100 times (I didn't bother to benchmark).

I have never seen this type of setup before, and the solution is so stupidly easy that I'm having difficulty understand why it wasn't done.  I tried asking the sales contact but he just doesn't know what I'm talking about.

Am I correct with my assessment or is there a legit reason to do this?

Clarifications:

"Is the DB relationship in nature?"

I believe so.  They run weekly and monthly DR/CR reports.  It is very much an accounting DB.  Even though there is no primary key setup, they have enforced the uniqueness at the application level.

"How old is the DB"

It is suppose to be a brand new, custom built system, running on SQL Server 2014.  However, I also found out they put the DB in 2008 compatibility mode (compatibility code 100).  I've asked about this but my contact haven't replied to me.