texts: Proper table design for sparse primary key

texts:
In my system I have temporary entities that are created based on rules stored in my database, and the entities are not persisted.

Now, I need is to store information about these entities, and because they are created based on rules and are not stored, they have no ID.

I came up with a formula to generate an ID for these temp entities based on the rule that was used to generate them: id = rule id + "-" + entity index in the rule.
This formula generates unique strings of the form 164-3, 123-0, 432-2, etc...

My question is how should I build my table (regarding primary key and clustered index) when my keys have no relation or order?
Keep in mind that I will only (99.9% of the time) query the table using the id mentioned above.

Options I thought about after much reading, but don't have the knowledge to determine which is better:

1) primary key on a varchar column with clustered index.
-According to various sources, this would be bad because of fragmentation and the wideness of the key. Also their format is pretty weird for sorting.

2) primary key on varchar column without clustered index (heap table).
-Also a bad idea according to various sources due to indexing and fragmentation issues.

3) identity int column with clustered index, and a varchar column as primary key with unique index.
-Can't really see the benefit of the surogate key here since it would mainly help with range queries and ordering and I would never query the table based on this key because it would be unknown at all times.

4) 2 columns composite key: rule id + rule index columns.
Now I don't have strings but I have two columns that will be copied to FKs and non clustered indexes. Also I'm not sure what indexes I would use in this case.

Can anybody shine a light here? Any help is appreciated.

--Edit

Here is what I think I'll be using after reading the comments, and the rationale:

clustered identity int Id as PK
non-clustered unique index on (rule id, index)

Rationale:
1 - I will often query the table by rule id only, which is why it is the leftmost index column;
2 - The Id column can be used for subsequent operations after the first query by rule id (the id will be stored client side);
3 - I will never query by index only;
4 - The unique index guarantees insert consistency, though it still slower than a single int;


texts: pg_stat_activity shows lots of easy queries running for minutes

texts:
After application server start its connection pool fills quickly and application can't work properly.
I went through pg_stat_activity and noticed lots of queries running for minutes:

But postgres's slow query log does not show any long-running queries (i've set 100ms as minimum query time).

select max(x2.x3) from (select "persistence_id" as x4, "sequence_number" as x3 from "persistence_journal" where "persistence_id" = $1 order by "sequence_number" desc) x2 where x2.x3 >= $2

I suggested that there are some locks on that table but SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED; shows nothing.

Structure of persistence_journal is pretty easy:

CREATE TABLE IF NOT EXISTS public.persistence_journal (
  persistence_id VARCHAR(255) NOT NULL,
  sequence_number BIGINT NOT NULL,
  created BIGINT NOT NULL,
  tags VARCHAR(255) DEFAULT NULL,
  message BYTEA NOT NULL,
  PRIMARY KEY(persistence_id, sequence_number)
);

persistence_journal contains 1 678 121 rows with ~37 000 unique persistence_ids with average 44 rows per persistence_id.

Running SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
 shows:

active  1
idle    43
idle in transaction 45

This "transaction" contains two pretty simple queries:

select max(x2.x3) from (select "persistence_id" as x4, "sequence_number" as x3 from "persistence_journal" where "persistence_id" = $1 order by "sequence_number" desc) x2 where x2.x3 >= $2
select max("deleted_to") from "persistence_deleted_to" where "persistence_id" = $1

persistence_deleted_to contains zero rows.

System's top shows almost zero CPU load.

PostgreSQL 9.4, Ubuntu 14.04


texts: Open Source Multi-master replication ideas?

texts:
I have multiple geographically separated data centres on a private network and would like some ideas for WAN outage tolerant open-source multi-master replication databases. I'm OK with RAM only dbs.

I need to be able to write to a common table on the local master at each data centre and have that information change in all versions of that table in all the data centres.

During a WAN outage I need to be able to continue to write to this common table at each data centre locally. When WAN connectivity resumes I need it to automatically merge all the changes together with some kind of conflict resolution.

Handling latency degregation would be an optional plus.

Is MySQL Cluster Multi-Site Clustering what I'm looking for? I find it hard to figure it out from their documentation.

I don't need an SQL interface or MySQL specifically, simple key:value would work too.

I really hope some more knowledgeable people could point me in the right direction!


texts: Partition tables based on month and year in SQL Server

texts:
I have a sales table which I need to partition based on month and year of sales

table structure

S_ID           numeric(18,0)
S_CustID       numeric(18,0)
S_TotalCash    numeric(18,0)
S_Paid         numeric(18,0)
S_Date         datetimeoffset(0)

I want to enter sales records between 01/01/2014 to 31/05/2014 to the first sales tables
and from 01/06/2014 to 31/12/2014 to the second table
and use a partitioned view to insert and select records from both the table so I add a constraint as

ALTER TABLE [dbo].[sales_1]  WITH CHECK ADD  CONSTRAINT [CK_sales_1_1] CHECK  ((datepart(mm,[S_Date]) >= (01)) and (datepart(mm,[S_Date]) < (06)) and ((datepart(year,[date]) = (2014)))

for the first table
and

ALTER TABLE [dbo].[sales_2]  WITH CHECK ADD  CONSTRAINT [CK_sales_2_1] CHECK  ((datepart(mm,[S_Date]) >= (01)) and (datepart(mm,[S_Date]) < (06)) and ((datepart(year,[date]) = (2014)))

AND
used a partitioned view

Select * from Sales_1
union all
Select * from Sales_2

to insert and update records
 but when I try to insert data ssms shows error as

  "UNION ALL view 'Test.dbo.Sales_View' is not updatable because a partitioning column was not found."

How to solve this?


texts: Shedding database server load from a web application: how to measure?

texts:
I work on a dotnet / WebForms web application that's load balanced over a number of web servers. The web servers connect to a SQL Server 2016 instance.

This web application includes several ways for end-users to request reports. Some of those reports take a lot of resources. I'm hoping to add a feature to the web app that says "Sorry, we're too busy to honor this request now. Please try again later."

Is there a good in-band way -- a good query or SP -- to ask SQL server how loaded it is at the moment? It happens that most overloads in our environment involve high cpu utilization.

If there's no good query, can anyone suggest an out-of-band way to do this reliably and simply? Maybe some sort of WMI operation.

Once I know how loaded the DBMS machine is, then I can decide whether to ask my end-users to come back later.

Edit. Two reasons to do this rather than add a server.

the user-frustration factor. If a request takes too long, the user may figure something's wrong and request it again. The way things work in web apps, SQL queries keep going even when the user has abandoned the request.
more servers cost money. The business I'm serving is self-funded. Managing peak load is as good a strategy as adding 24x7 base load capacity.


texts:
I'm using MongoDB 3.0.4 with WiredTiger storage engine. I was looking at collection stats and I'm seeking explanation on compression stats fields.
What does "page written failed to compress" indicate? Is it an error I should be concerned about with regards to data integrity and why does it happen?

Sample stats output from one of my collections:

"compression" : {
                        "raw compression call failed, no additional data available" : 0,
                        "raw compression call failed, additional data available" : 0,
                        "raw compression call succeeded" : 0,
                        "compressed pages read" : 205883,
                        "compressed pages written" : 306168,
                        "page written failed to compress" : 3147,
                        "page written was too small to compress" : 51992
                }


texts: Add a constraint to a column on an Oracle table so that only positive values are allowed

texts:
I'm not allowed to drop the table, or delete the columns.
The task is to add a constraint to a column possibly by using an ALTER statement so that it disallows any negative numbers. Is this possible?

I tried the following:

SQL> alter table SPEND_PLAN_DETAIL_VALUE modify (SP_DETAIL_VALUE_ID CHECK (SP_DETAIL_VALUE_ID > 0));

but this doesn't work.


texts: The system stored procedure sp_dboption is not available in SQL Server 2012 anymore. How could it be replaced?


texts: How to change type from one owner to two different types of owners

texts:
I currently have (tables changed):

User

UserID uniqueidentifier,
CompanyID uniqueidentifier, (for future reference)
.. more unrelated fields

(PK: UserID)

UserRating

UserID uniqueidentifier,
MovieID uniqueidentifier,
RatingTypeID uniqueidentifier,
Rating tinyint

(PK: UserID, MovieID, RatingTypeID)

RatingType

RatingTypeID uniqueIdentifier,
Title varchar(25)

(PK: RatingTypeID)

The RatingType table contains "Favorite", "Need to Watch", etc.  So actual Rating value is how much they liked a Favorite, or how badly the want to watch a movie etc.

Now I introduce a new table:

Company

 CompanyID uniqueidentifier,
 .. more unrelated fields

The idea is that if a user becomes part of a company, due to requirements that cannot change, all the Ratings need to be Company ratings and not User Ratings.  I'm not a normalization guru, but for some reason creating another table (below) seems redundant, but maybe I'm thinking to much into it.

CompanyRating

CompanyID uniqueidentifier,
MovieID uniqueidentifier,
RatingTypeID uniqueidentifier,
Rating tinyint

(PK: CompanyID, MovieID, RatingTypeID)

This feels incorrect because it almost matches the rating table and if I need to do any averages it becomes more difficult to average across two tables.  Is there a better design for my needs?


texts: Set SPN Service principle name for SQL Server Service account - Best practice

texts:
I am looking for guidance on Microsoft recommended best practice regarding setting the SPN for the service account that runs the SQL Server service (MSSSQLSERVER)
When setting up a new server (or changing the service account on an existing server) should I always explicitly set the SPN (even if the server has no linked servers defined) or should it only be set in cases where you experience issues such as a double hop authentication issues when using a linked server?
I want to set the SPN for every service account that runs SQL and our security/AD person only wants to set it for those servers where a linked server is defined.
Is there a downside or security risk in setting SPN if you don't have linked servers.
This question applies to SQL 2012, 2014 and 2016.
I have found a few links that seem to suggest that setting the SPN explicitly should be the best practice  so that Kerberos authentication (SQL's first choice) can be used rather than having to fall back to Windows NT Challenge/Response (NTLM) authentication
Some articles that seem to suggest setting it should always be done

FAQS around Kerberos and SQL

Security Considerations for a SQL Server Installation

Resolving Connectivity Errors

SQL Server Kerberos and SPN Quick Reference


texts: Does a database backup reduce size/increase free space of the log file?

texts:
I have a SQL Server database where the log file is massive and keeps growing every day.

I've archived data from the database and then done a full backup.  I was expecting the log to then be basically "empty".  e.g. It might be the same physical size, but now have lots of free space.  However, it's currently only got 0.1% free.

Any ideas why there is still almost no free space in the log?


texts: Splitting array of string values into Rows Numbers Table Ref Error

texts:
I am trying to create a table that queries individual database records, but splits out values from an associated array into individual rows for the individual database record. So far I have created the numbers table that has numerical rows up to 255, but when I try to join that table to a table where I am splitting the array by string in the number table order, I receive an error function split_part(character varying, "unknown", double precision) does not exist. Any idea what is off with my setup?

Note: I am using Amazon Redshift

Numbers Table:

Sample query results

number
1
2
3
4
...
255

Query

SELECT
    p0.n
    + p1.n*2
    + p2.n * POWER(2,2)
    + p3.n * POWER(2,3)
    + p4.n * POWER(2,4)
    + p5.n * POWER(2,5)
    + p6.n * POWER(2,6)
    + p7.n * POWER(2,7)
    as number
  FROM
    (SELECT 0 as n UNION SELECT 1) p0,
    (SELECT 0 as n UNION SELECT 1) p1,
    (SELECT 0 as n UNION SELECT 1) p2,
    (SELECT 0 as n UNION SELECT 1) p3,
    (SELECT 0 as n UNION SELECT 1) p4,
    (SELECT 0 as n UNION SELECT 1) p5,
    (SELECT 0 as n UNION SELECT 1) p6,
    (SELECT 0 as n UNION SELECT 1) p7

Parser table query:

Ideal query results

row_id, record_id, medium_name
1, 24, work
2, 24, test
3, 24, success

Query

SELECT row_number() over (order by 1) as row_id
        , test_lead.id as lead_id
        , split_part(test_lead.test_mediums, ', ', numbers.number) as medium_name
      FROM ${lead.SQL_TABLE_NAME} test_lead
      JOIN ${marketing_model_numbers.SQL_TABLE_NAME} numbers
      ON numbers.number <= regexp_count(test_lead.test_mediums, ',\\s') + 1

Array value structure:

["work", "test", "success"]


texts: Dumping and restoring tables in PostgreSQL - ERROR: extra data after last expected column

texts:
I am trying to dump and restore multiple tables from a database on one server, to a different database on another server. Both are running Postgres 9.4 on linux.

The command for the backup was:

$ pg_dump -t table1 -t table2 -t table3 -t table4 dbname > backup.sql

Restore command:

$ psql new_dbname --set ON_ERROR_STOP=1 < backup.sql

The restore would start, but then I get the following error:

ERROR: extra data after last expected column

Tables are created, but there is no data.

After a bit of searching the error normally seems to pop up when trying to import CSV or other text files, but this is obviously not the case here.

Just to add some confusion, the restore works perfectly if I split the backup into 2 separate SQL files. One of the tables is much larger, so I did that one on it's own and the other 3 into a single SQL file.

Further info:

From Daniel's suggestion I have taken a look at the line where the error is occurring. There are 2 rows where the data looks bad. The first row is completely missing data in 3 fields. The other row has one field where the data does not appear as it should (almost as if the data has partly been taken from a different field).
Going back to the original database, these problems are not there, the data is fine.

What could be causing this problem in the SQL file?


texts: Why does sqlcmd -Lc not show the local instance?

texts:
I have two machines, each with a default instance of SQL Server 2008 installed; each exhibiting a different behavior.

On PC1, when I run sqlcmd -Lc, I do not see PC1 in the list of SQL servers on the local network when the SQL Browser Service is not running.  When I start the SQL browser service, PC1 appears in the list.
On PC2, when I run sqlcmd -Lc, I see PC2 in the list, whether or not SQL Browser Service is running.  If SQL Browser Service is running, I also see PC2\SQLEXPRESS.

Why does PC1's default SQL instance not show up in the list of SQL Servers?


texts: Is sp_getapplock recommended for long running locks (> 10 minutes)?

texts:
I'm trying to get rid of a Redis dependency in our application.

We notice that is worse for our uptime to have a dependency on both SQL and Redis, above the performance benefits Redis would give us.

The last part we need to fix is the multi-instance locks. Right now I use a wrapper called RedLock. See the code below:

using (var lock = RedisLocker.Create(key, expiryTime: TimeSpan.FromMinutes(1), waitTime: TimeSpan.FromMinutes(10), retryTime: TimeSpan.FromSeconds(10)))
{
    if (!lock.IsAcquired)
        throw new InvalidOperationException("Can't acquire lock");

    [code]
}

I want to replace this with a SQL alternative and came across sp_getapplock. I noticed that you actually have to keep the connection open to use sp_getapplock.

For short locks that feels fine, but for long locks (>10 minutes) it feels "expensive" to keep a connection open.

Should I go for a polling strategy similar to the Redis version?
Or is my assumption wrong that connections are expensive?
I'm planning to wrap the code in something similar like the Redlock call - any other recommendations?

Extra notes

Everything is running on Azure.
The application is high traffic on peak moments and can have SQL performance problems.
The lock is not in a high frequency path though (just a few processes).


texts:
I looked at the postgres website and to add a integer to a intarray it says int[] + int.  I have a int array in a table and I eventually want to insert the array with another value added to it into another table.  So testing with a select statement I am not sure where I am messing up.

So a am doing:

SELECT myarray + 9::integer from foo
ERROR: operator does not exist: smallint[] + integer

What am I missing.  It looks just like the postgres website on intarrays from 9.1 postgres.


texts: disallow SET command in a postgresql server

texts:
Im planning to publish my postgresql server to a few untrusted clients.

I dont want them to modify any runtime setting, like work_mem or something risky to my server. In general I assume the pg_catalog schema is public but I don't want to allow updating pg_settings at all.

Is it possible?


texts: How to select the mail profile in a sql agent job

texts: I have several mail profiles configured on sql agent, how can I choose which of these profiles gets used for sending out notifications on a specific sql agent job ?


texts: CROSS APPLY query with CASE and running totals

texts:
I'm trying to make this business logic make sense, but I'm not having any luck.
The rule is I can have a sale with discounts from percentage and value. But the value is to be subtracted after discounted percentage, from the sequence column.

If this was only about percentages, I could make it work, the problem is the case in the middle when in the same sale there's a value to subtract from Current value.

+--------+-----------+------+----+---+
| SaleId | Discount  | Type | S  | q |
+--------+-----------+------+----+---+
|      1 |      0.15 | P    | rc | 1 |
|      1 |     10.05 | V    | l  | 2 |
|      2 |      0.05 | P    | rc | 1 |
|      3 |      0.05 | P    | rc | 1 |
|      3 |      8.00 | V    | l  | 2 |
|      3 |      0.25 | P    | rc | 3 |
+--------+-----------+------+----+---+

+--------+--------+
| SaleId | Amount |
+--------+--------+
|      1 | 100.00 |
|      2 | 200.00 |
|      3 |  50.00 |
+--------+--------+

+--------+--------+----------+------+-----+--------+--------------+
| SaleId | Amount | Discount | Type | Seq | T_Disc | CurrentValue |
+--------+--------+----------+------+-----+--------+--------------+
|      1 |    100 |     0,15 | Perc |   1 |   0,85 |           85 |
|      1 |    100 |    10,05 | Val  |   2 |      0 |        74,95 |
|      2 |    200 |     0,05 | Perc |   1 |   0,95 |          190 |
|      3 |     50 |      0,1 | Perc |   1 |   0,95 |           45 |
|      3 |     50 |        8 | Val  |   2 |      0 |           37 |
|      3 |     50 |     0,25 | Perc |   3 |      0 |        27,75 |
+--------+--------+----------+------+-----+--------+--------------+

Sale 3:

50 minus 10%
45 minus 8
37 minus 25%

After this I need to display the last row from each SaleId.

Can you please advise if this is possible?

I'm trying CROSS APPLY and a CTE (WITH clause):

WITH CTE_CalcTotalDisc
AS
(
    Select A.SaleId
    ,CONVERT(DECIMAL(8,3),B.Amount) Amount
    ,Discount
    ,Tipo
    ,A.Seq
    ,CASE WHEN Tipo='Val' THEN convert(decimal(8,3),Discount)
        ELSE convert(decimal(8,3),1-(1*Discount))END AS T_Disc
    ,CASE WHEN Tipo='Val' THEN convert(decimal(8,3),Amount-Discount)
          WHEN Tipo='Perc' THEN convert(decimal(8,3),Amount-(Amount*Discount))
        ELSE convert(decimal(8,3),0.00) --convert(decimal(6,5),1-(1*Discount))
        END
    AS CurrentValue
    From #disc A, #Sales B
    where Seq=1 AND A.SaleId=B.SaleId

    UNION ALL

    Select d.SaleId
    ,CONVERT(DECIMAL(8,3),0.00)
    ,d.Discount
    ,d.Tipo
    ,d.Seq
    --,convert(decimal(6,5),(1-(1*d.Discount))*T_Disc) T_Disc
    --,CASE WHEN d.Tipo='Val' THEN d.Discount ELSE convert(decimal(6,5),1-(1*d.Discount))END
    , CONVERT(DECIMAL(8,3),0.000) AS T_Disc
    ,CONVERT(DECIMAL(8,3),0.000) AS CurrentValue
    From #disc d --, #Sales B
        JOIN CTE_CalcTotalDisc ad on d.SaleId=ad.SaleId
                            AND d.Seq=ad.Seq+1
)
SELECT * FROM CTE_CalcTotalDisc I1
CROSS APPLY(
  SELECT
  --SUM(CurrentValue)   RunningTotal
  I1.CurrentValue-(I1.CurrentValue*I1.Discount)   RunningTotal
  FROM CTE_CalcTotalDisc I2
  WHERE I1.SALEID = I2.SALEID
  --AND I1.Tipo = I2.Tipo
  AND I1.Seq <= I2.Seq
)C


texts: Updating Multiple Rows with Data from Column A into Column B?

texts:
I have about 60,000 rows that I'm needing to update the information from column_a to column_b.  For example, 60,000 customers need their arrival_time to match their departure_time.  I understand that I can do this with one row and it work with a nested select statement in an update statement.  When trying to update multiple rows though I believe I'm getting stuck on having to have 60,000 unique key identifiers.

This was what I used to update 1 of the records, I'm using Oracle SQL:

UPDATE patient
SET discharge_dt = (SELECT admit_dt
                    FROM patient
                    WHERE pat_seq = 'XXXXXX')
WHERE facility_id = 'X'
    AND pat_seq = 'XXXXXX'

Apologize for the confusing description, not really a database administrator myself.

Any help on this would be greatly appreciated.


texts: How to predict the growth of a PostgreSQL database

texts:
I'm looking for a way to predict growth of the database given its fields, indexes, constraints (as foreign key), number of lines, and so.

I searched on Google, but I found only means to determine the actual space occupied by the database, and can not predict their growth.

For example, how can I determine how many bytes would be occupied on disk for the following tables, in the following cases

1) 100,000 rows in the account table, 10 rows in the role table and 100,000 lines in account_role table.

2) 500,000 lines per account table, 20 lines on paper table and 600,000 rows in the table account_role?

CREATE TABLE account(
    user_id serial PRIMARY KEY,
    username VARCHAR (50) UNIQUE NOT NULL,
    password VARCHAR (50) NOT NULL,
    email VARCHAR (355) UNIQUE NOT NULL,
    created_on TIMESTAMP NOT NULL,
    last_login TIMESTAMP
);

CREATE INDEX username_index ON COMPANY (username);

CREATE TABLE role(
    role_id serial PRIMARY KEY,
    role_name VARCHAR (255) UNIQUE NOT NULL
);

CREATE TABLE account_role
(
  user_id integer NOT NULL,
  role_id integer NOT NULL,
  grant_date timestamp without time zone,
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT account_role_role_id_fkey FOREIGN KEY (role_id)
      REFERENCES role (role_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT account_role_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES account (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);


texts:
I am preparing some scripts to setup merge replication in our production environment.
After completing the export from UAT, I've come across something that doesn't appear like it would work in the production environment?

exec sp_addmergesubscription
    @publication = N'<snip/>',
    @subscriber = N'<Server Name>',
    @subscriber_db = N'<snip/>',
    @subscription_type = N'pull',
    @subscriber_type = N'local',
    @subscription_priority = 0,
    @sync_type = N'Automatic'

The problem I have with this is that the subscriber name is hardcoded to the physical server name of the subscriber DB.  In UAT that's fine because the subscriber is a single server, in production that's a problem because there are three production subscribers with mirror partners.

What name should I be using for the subscriber?  If I leave it as NULL as the documentation indicates I can, what are the implications of that?


texts: How to get the current date without the time part

texts: In SQL Server 2005 how do I get the current date without the time part? I have been using GETDATE() but would like it to have a time of 00:00:00.0


texts: SQL 2019 Linux, slow at GUI fast at sqlcmd/Query Analyzer

texts:
Server information
OS Redhat 7
Memory 100GB
CPU usage 1%
User db size 2 gb each on avg
total 7 user databases
SSMS installed on a jump server
Azure Data studio is also installed
App Gui on application server
Latency between jump server to db server <1ms
Latency between application server and db server  <1ms
all are in same network, only switch is between them
all are physical machines
Issue

SSMS - When I connect to SSMS, the databases in object explorer takes around 30 seconds to populate. Opening database properties takes upto 50 seconds to open
Query analyser/sqlcmd from SSMS runs "select name from sys.sysdatabases" instantly without delay

Azure data studio - Populates Databases list fairly quickly but takes more than a minute to show database properties.

App Gui - also have the same behaviour as SSMS.

From every location, querying seems fast enough but GUI is found to be slow.
Different scenario
I have other SQL Server on windows OS, the SSMS, Azure data studio behaviour is usual where they populate information on GUI instantly.
We also have few database on Azure SQL Database and Azure VM, they also connect instantly and populate information instantly at SSMS GUI
What we want to achieve?
We need our App gui, SSMS to perform as fast as SQL Server on Windows OS at gui level.
So what could be the issue and how I can fix this?
Please help!
Edit -
I missed the most important part. The said instance is Express edition. So the super impressive hardware they put into it was not being fully utilised. Plus the default auto close feature.


texts:
I'm new to DBA and I'm attempting to create a trigger that will update the a specified column in one table whenever an insert is made into every table across the database.

I understand it is possible to implement many triggers to solve this problem however it seems to me that it would be much more maintainable if it were to be a single trigger activating at a database wide level.

If having a single trigger is possible, is there then also reason I should not have one trigger to manage all of the tables?

Below is the current way in which I would implement the triggers at a table by table basis:

CREATE TRIGGER [dac].[T_UpdateModelTime] ON [dac].[SubSpeciality]
 AFTER INSERT
 AS

 DECLARE @Table_Model_ID int

 SELECT @Table_Model_ID = Model_ID FROM SubSpeciality

 UPDATE ModelVersion SET DateLastEdited = GETDATE() WHERE ID = @Table_Model_ID;

GO

Additional details

The data is inserted into the tables through stored procedures.

The updated column requires time sensitivity within seconds.


texts:
Is there a way that I can create a month-year partition on the below table?  Without having to define all the permuations of month/year combos ?

CREATE TABLE  visitor_visits (
  id int(10) UNSIGNED NOT NULL auto_increment,
  visitor_id int(10) UNSIGNED NOT NULL,
  source varchar(64) NULL DEFAULT NULL DEFAULT NULL,
  medium varchar(64) NULL DEFAULT NULL,
  campaign varchar(256) NULL DEFAULT NULL,
  page varchar(32) NULL DEFAULT NULL,
  landing varchar(32) NULL DEFAULT NULL,
  type enum('fundraiser_view') NULL DEFAULT NULL,
  visit_date date NOT NULL default '0000-00-00',
  created_at datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE visitor_visits ADD UNIQUE INDEX (visitor_id,type,visit_date);
ALTER TABLE visitor_visits ADD CONSTRAINT FK_visits_visitor_id FOREIGN KEY (visitor_id) REFERENCES visitors(id);


texts:
I have a powershell script that is restoring from production into a test availability group database. It
copies the full and t-log backups from production to test,
takes the target db out of the availability group and kills all connections,
sets the target database to single user,
does the restore on test (not point in time, just up to however many t-log backups are present in production) with replace,
sets the database to multi-user, makes sure it's in FULL recovery, sets the owner then shrinks the database (truncateonly),
takes a full backup,
takes a log backup,
moves the backups to the secondary, restores, adds the databases to the AG etc...

My issue is that there's a fork point being created when I take the log backup which gives me an error when restoring on the secondary. It makes no sense because the log backup doesn't start until after the full has been taken.

Here is the error:

  The log in this backup set terminates at LSN 67867000000017200001, which is too early to apply to the database. A more recent log backup that includes LSN 67869000007776500001 can be restored.

Here is the output from the backupset:

and here is the info about the last restore:

My eyes are crossing looking at this, and it's probably something simple. Ideas?


texts:
I am running MSSQL 2008 Enterprise and in the configuration of the server the Max memory amount is set to 2147483647 MB.
My Question is, the server has 192 GB of ram,the number in the config can't be increased, if the config is set to that, does that mean the server can use whatever it needs? Or how can i enable it that the server uses all the ram that is available.

If i open the task manager, the memory usage never goes above 19 GB.

Thank you for the help


texts: Help to understand explain plan in Oracle

texts:
I am running a query in some big tables, and although it runs fine even tough is a lot of data, I'd like to understand what part of it weighs on the execution. Unfortunately I am not too good with explain plans so I call for help.

Here is some data about these tables:

history_state_table 7.424.65 rows (of which only 13.412 are left after t1.alarm_type = 'AT1' )
costumer_price_history 448.284.169 rows
cycle_table 215 rows

This would be the query (don't mind the logic, is just for the reference):

SELECT t1.id_alarm, t2.load_id, t2.reference_date
  FROM history_state_table t1,
       (SELECT   op_code, contract_num,
                 COUNT (DISTINCT id_ponto) AS num_pontos,
                 COUNT
                    (DISTINCT CASE
                        WHEN vlr > 0
                           THEN id_ponto
                        ELSE NULL
                     END
                    ) AS bigger_than_zero,
                 MAX (load_id) AS load_id,
                 MAX (reference_date) AS reference_date
            FROM costumer_price_history
           WHERE load_id IN
                            (42232, 42234, 42236, 42238, 42240, 42242, 42244) /* arbitrary IDs depending on execution*/
             AND sistema = 'F1'          /* Hardcoded filters */
             AND rec_type = 'F3'         /* Hardcoded filters */
             AND description = 'F3'      /* Hardcoded filters */
             AND extract_type IN
                    ('T1', 'T2', 'T3')
        GROUP BY op_code, contract_num) t2
 WHERE t1.op_code = t2.op_code
   AND t1.contract_num = t2.contract_num
   AND t1.alarm_type = 'AT1'
   AND t1.alarm_status = 'DONE'
   AND (   (    t1.prod_type = 'COMBO'
            AND t2.bigger_than_zero = t2.num_pontos - 1
           )
        OR (    t1.prod_type != 'COMBO'
            AND t2.bigger_than_zero = t2.num_pontos
           )
       )
       /* arbitrary filter depending on execution*/
   AND t1.data_tratado BETWEEN (SELECT data_inicio
                                  FROM cycle_table
                                 WHERE id_ciclo = 160) AND (SELECT data_fim
                                                              FROM cycle_table
                                                             WHERE id_ciclo =
                                                                           160)

And finally the explain plan:

Plan
SELECT STATEMENT  ALL_ROWSCost: 5,485
    13 NESTED LOOPS
        7 NESTED LOOPS  Cost: 5,483  Bytes: 115  Cardinality: 1
            5 VIEW  Cost: 12  Bytes: 59  Cardinality: 1
                4 SORT GROUP BY  Cost: 12  Bytes: 85  Cardinality: 1
                    3 INLIST ITERATOR
                        2 TABLE ACCESS BY INDEX ROWID TABLE RAIDPIDAT.COSTUMER_PRICE_HISTORY Cost: 11  Bytes: 85  Cardinality: 1
                            1 INDEX RANGE SCAN INDEX RAIDPIDAT.IDX_COSTUMER_PRICE_HISTORY_2 Cost: 10  Cardinality: 3
            6 INDEX RANGE SCAN INDEX RAIDPIDAT.IDX_HISTORY_STATE_TABLE_1TPALM Cost: 662  Cardinality: 102,068
        12 TABLE ACCESS BY INDEX ROWID TABLE RAIDPIDAT.HISTORY_STATE_TABLE Cost: 5,471  Bytes: 56  Cardinality: 1
            9 TABLE ACCESS BY INDEX ROWID TABLE RAIDPIDAT.CYCLE_TABLE Cost: 1  Bytes: 12  Cardinality: 1
                8 INDEX UNIQUE SCAN INDEX (UNIQUE) RAIDPIDAT.PK_CYCLE_TABLE Cost: 0  Cardinality: 1
            11 TABLE ACCESS BY INDEX ROWID TABLE RAIDPIDAT.CYCLE_TABLE Cost: 1  Bytes: 12  Cardinality: 1
                10 INDEX UNIQUE SCAN INDEX (UNIQUE) RAIDPIDAT.PK_CYCLE_TABLE Cost: 0  Cardinality: 1

Mind that I am not asking "how to rewrite it more efficiently", but rather how do I find witht the explain plan what the most costly operation there. Meanwhile I am reading about it, but I'd appreciate some help.


texts: SQL Server - Company wants HA for single instance with 1 userDB - sanity check and questions

texts:
I am new to SQL Server (2 months) and learning at a gov company (europe).

The job is to make a HA solution for this 1 productive instance (1 database).

Following is given

SQL Server 2012
Windows Server 2008 R2
single instance with single user database
the instance uses an Agent job for backups (once per day)
24h dataloss is OK
Disaster backup is not my job
RTO is the typical ASAP but IMO 1 min is ok (which is insanely high?)
Local Logins (is in Domain, but not AD)
max 300 clients (Citrix Xen Mobile)
Database is 300MByte with only 30 clients currently
all local storage at the moment
most likely more clusters for other applications in the future

What are my choices here and why is this one better?

I know about FCI and AG but I can't wrap my head around some things.

what are the downsides with doing an WSFC without AD?
whats the downside in using a SMBShare instead of Fibrechannel/IScsi?

The possibilities I see (up/downsides would be awesome for these):

put application server(s) into AD (we do have one),get SAN storage, install FCI

use WSFC to add future clustergroups for other applications + SQLServer with the 3 wizards in no time
downside I see is cost, upside is reusability and support is best

same as aboth with SMB which is way easier and cheaper
use AG without AD and replicate Logins, Agent Jobs between nodes manually (same with AD)

I'm kinda lost after stumbling into the topic.

Thanks to everybody helping!


texts: How to generate unique Auto increament id field across multiple data centers

texts:
I have multiple mysql servers deployed in three different data centers. Now my database has few tables with Auto increment id column. I want these columns in all three data center must have unique id across the data centers. Is it possible in Mysql or i need to generate these columns programmatically ?

For example in data center D1 has table T1 with id 1, then data centers D2 and D3 must not generate id 1 for table T1.


texts: Creating Bitmap Indexes on a Materialized View

texts:
I've been reading this article, discussing the benefits, tradeoffs, and potential problems with Bitmapped Indexes. It is mentioned:

  Also, remember that bitmap indexes are only suitable for static tables and materialized views which are updated at night and rebuilt after batch row loading.  If your tables are not read-only during query time, DO NOT consider using bitmap indexes!

Most of our Materialized Views are updated nightly using the fast refresh method. Does the above quote mean that the MV should be rebuilt, or that the indexes should be rebuilt?


texts: Most efficient way to get oldest row in MySQL by id

texts:
I have 2 tables:

conversation

CREATE TABLE `conversation` (
  `conversation_id` int(11) NOT NULL,
  `title` varchar(200) COLLATE utf16_czech_ci NOT NULL,
  `beginning_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` int(11) DEFAULT NULL
)

message

CREATE TABLE `message` (
  `message_id` int(11) NOT NULL,
  `text` varchar(5000) COLLATE utf16_czech_ci NOT NULL,
  `add_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_seen` int(11) NOT NULL,
  `conversation_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL
)

When I create a new conversation, I also need to create a new message (the beginning message for the conversation). Later, this conversation may have thousands of messages. But what I need to do in certain situations is that I need to take a conversation with certain conversation_id from database and get the oldest message (the beginning message for the conversation) for this conversation. What is the most efficient way to get this message?

I did something like this but the ordering may slow it later, when conversations have maaany messages:

SELECT * FROM message WHERE conversation_id=some_id ORDER BY add_date ASC LIMIT 1


texts: Select same data-set at different date ranges in a single query

texts:
what I am trying to do is performing a query that has multiple results of the same query by just editing only a where clause.

The goal is to query a result of the same dataset in different date ranges, but instead of performing multiple separated queries, having one single query that queries the tables X times (depending on the selected date ranges).

For example:

SELECT database_one.users.firstname,
       database_one.users.lastname,
       COUNT(*) AS "Total"
FROM database_two.announcements
INNER JOIN database_one.prospect_evaluations ON database_one.prospect_evaluations.announcement_id = database_two.announcements.id
INNER JOIN database_one.users ON database_one.prospect_evaluations.user_id = database_one.users.id
WHERE (database_two.announcements.deleted_at IS NULL
       AND (database_two.announcements.archived_at IS NOT NULL
            AND database_two.announcements.crmstate != 'Scartata'))
  AND (database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 MONTH)) /* THIS IS THE DATE RANGE TO EDIT IN THE SUBQUERY */
GROUP BY database_one.prospect_evaluations.user_id
ORDER BY database_two.announcements.id DESC

What I'd like to have is something like this, in pseudo-sql:

SELECT database_one.users.firstname,
       database_one.users.lastname,
       COUNT(*) AS "Total",
       /* PSEUDO-SQL START { */
       COUNT(*) WHERE (database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 MONTH)) AS "1 Month"
       COUNT(*) WHERE (database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 3 MONTH)) AS "3 Months"
       COUNT(*) WHERE (database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 6 MONTH)) AS "6 Months"
       COUNT(*) WHERE (database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 YEAR)) AS "1 Year"
       /* } PSEUDO-SQL STOP */
FROM database_two.announcements
INNER JOIN database_one.prospect_evaluations ON database_one.prospect_evaluations.announcement_id = database_two.announcements.id
INNER JOIN database_one.users ON database_one.prospect_evaluations.user_id = database_one.users.id
WHERE (database_two.announcements.deleted_at IS NULL
       AND (database_two.announcements.archived_at IS NOT NULL
            AND database_two.announcements.crmstate != 'Scartata'))
GROUP BY database_one.prospect_evaluations.user_id
ORDER BY database_two.announcements.id DESC


texts: Database backup - Use case of FORMAT and INIT

texts:
I understand that FORMAT looks at mediaset level and if set then creates a new media set so if there are any existing backupsets then they will no longer be available.

Also, INIT looks at backupset level and if set then overwrites the existing backupset, so if there are any existing backupset then they will no longer be available. Will it impact that backupset or all backupsets on the media set?

So how do these options work? I am trying to get an use case scenario to understand this better.


texts: SSMS (2012) limit # of characters on editor window line

texts:
Is there a way to enforce a hard limit on the number of characters allowed on a line in the SSMS editor window? I have searched, but am unable to find an option to set this.

I'd like to force the line to wrap at 100 characters if possible.

Alternately, is there a way to set a visible cue at 100 characters (similar to Vim's colorcolumn setting)?

Thanks.


texts: Parsing DATE while copying csv file into PostgreSQL table

texts:
I have a long series of .csv files, which I want to import into a local database. I believe my query is correct, but there are some problems in parsing DATE and TIMESTAMP columns. PostgreSQL reads these columns expecting an ISO format "yyyy/mm/dd", but my data has it in another format: "dd/mm/yyyy".
I read online and on other Stack Overflow answers that one can SET the datestyle to be different, but it's not recommended.
Is there a way to specify the format of the columns to import? Also, I do not need to import all columns from the csv file: can I leave some out?
Details
First, I wrote the code to create the table (sorry if column names are in Italian, but it's not important):
CREATE TABLE IF NOT EXISTS bikes (
    bici INT,
    tipo_bici VARCHAR(20),
    cliente_anonimizzato INT,
    data_riferimento_prelievo DATE,
    data_prelievo TIMESTAMP,
    numero_stazione_prelievo INT,
    nome_stazione_prelievo TEXT,
    slot_prelievo SMALLINT,
    data_riferimento_restituzione DATE,
    data_restituzione TIMESTAMP,
    numero_stazione_restituzione INT,
    nome_stazione_restituzione TEXT,
    slot_restituzione SMALLINT,
    durata VARCHAR(10),
    distanza_totale REAL,
    co2_evitata REAL,
    calorie_consumate REAL,
    penalità CHAR(2)
);

Then I add the query to copy data into the table:
COPY bikes(
    bici,
    tipo_bici,
    cliente_anonimizzato,
    data_riferimento_prelievo,
    data_prelievo,
    numero_stazione_prelievo,
    nome_stazione_prelievo,
    slot_prelievo,
    data_riferimento_restituzione,
    data_restituzione,
    numero_stazione_restituzione,
    nome_stazione_restituzione,
    slot_restituzione,
    durata,
    distanza_totale,
    co2_evitata,
    calorie_consumate,
    penalità
)
FROM '/Users/luca/tesi/data/2019q3.csv'
DELIMITER ','
CSV HEADER;

The code seems fine, except the following error pops up:
ERROR:  date/time field value out of range: "31/07/2019"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  COPY bikes, line 25296, column data_riferimento_restituzione: "31/07/2019"
SQL state: 22008

How can I specify in the CREATE TABLE portion of the code the format to parse? Also, I do not actually need all the cols of this csv, how do I leave these out? I tried to specify only those I need but I get an import error:
ERROR:  extra data after last expected column


texts:
I have a dataset with a calculated field:

 = IIF(String.IsNullOrEmpty(Fields!Event.Value), "\t", Fields!Event.Value) & ", " &
   IIF(String.IsNullOrEmpty(Fields!Action.Value), "\t", Fields!Action.Value) & ", " &
   IIF(String.IsNullOrEmpty(Fields!RequestedBy.Value), "\t", Fields!RequestedBy.Value)

This field is then accessed inside a Table within the report using a lookup expression:

=Join(LookupSet(Fields!contactid.Value, Fields!ContactParticipantValue.Value, Fields!RowSummary.Value, "EventRequests"), Environment.NewLine)

When I run this, initially everything works fine and the correct data is shown in the cell.  However there is an warning displayed:

Warning 1   [rsRuntimeErrorInExpression] The Value expression for the field ‘RowSummary’ contains an error: (processing): (field.ExprHost != null)  0

Then if I go to the next page, all of the cells now contain #Error instead of the 'RowSummary' info.  If I then go back to the previous page, which used to have all the correct data in it, all of the cells are now replaced with #Error.

I have found a work around whereby I add a hidden list container to the END of the report and set DataSet property to the 'EventRequests' dataset with the calculated field in it.  I don't have to add any fields or make this list visible, but somehow just having the list on the report prevents the #Error from replacing all of my data.  This does not solve the problem completely because when I go to export to PDF, I still receive errors.

Anyone know why this is happening?


texts: SQL query help: Calculating days between observations

texts:
I'm extremely new to SQL, in fact, I just really started playing around with it.  So I'm not exactly sure how to search for appropriate terms related to the question I have.  I'll do my best.

CONTEXT:

I have a table that I'd like to query using SQL Server.  That table has behavioral data for hundreds of thousands of animals over multiple days (I know! Lots of animals! Rats, actually, and it is data that has been compiled over many many years by many many people).  Here are the columns:

AnimalID (an identifying ID unique to that animal)
DateID (Date of observation-- for example, 20151111)
Behavior1 (# of times observed eating on that DateID-- could be 0, max is 337!)
Behavior2 (# of times observed grooming on that DateID-- could be 0, max is 36)
Behavior3 (# of times observed mating/attempted mating on that DateID-- could be 0, max is 77!)

There are other columns like "gender," "AgeInDays," and several more behavior types.  I don't think it is worth listing them all out.

I'd like to be able to use WHERE and GROUP BY in a query to summarize this dataset.

So far I have been able to write queries that give me the total number of days that each animal does a behavior or the total number of times that an animal has done a behavior across all records for each animal.  For example, I've done this by making WHERE [Behavior1] > 1 and GROUP BY [AnimalID].

This is actually pretty cool!  And I'm stunned at how quickly these queries run!  But I'm stuck on a different type of query.

QUESTION:

I want to write a query that will tell me the number of days (DateID) between the first date an animal (each unique AnimalID) tried to mate (Behavior3) and the second date the same animal tried to mate.  In the same output, I'd like to have the number of days between the SECOND date they tried and the THIRD date they tried.  All while having WHERE [Behavior1] > 1 and GROUP BY [AnimalID].

Ideally the output would have columns like this:

AnimalID
NumDays1 (number of days between first and second attempts for that AnimalID to mate)
NumDays2 (number of days between the second and third attempts for that AnimalID to mate)

Is this even possible to do in a single query?  I know I need to use DATEDIFF...   Any help would be greatly appreciated!

Apologies if I've messed up the way I was supposed to ask this question on this forum.

Thanks!


texts: Permission Denied for a User that is part of AD

texts:
I have a group created in Active Directory. This Group contains 3 users. For some strange reason one of the user within the group can't select  from specific type of SCHEMA whilst the 2 other users can select from the same schema
I ran some test EXECUTE AS USER for the other 2 users and everything seems fine except for that one user and i get the following error:

  Msg 229, Level 14, State 5, Line 4 The SELECT permission was denied on
  the object '', database 'Somethng_Database', schema 'sch'.


texts:
I have a table with a multicolumn index, and I have doubts about the proper sorting of the indexes to get the maximum performance on the queries.

The scenario:

PostgreSQL 8.4, table with about one million rows
Values in column c1 can have about 100 different values. We can assume the values are evenly distributed, so we have about 10000 rows for every possible value.
Column c2 can have 1000 different values. We have 1000 rows for every possible value.

When searching data, the condition always includes values for these two columns, so the table has a multicolumn index combining c1 and c2. I have read about the importance of properly ordering the columns in a multicolumn index if you have queries using just one column for filtering. This is not the case in our scenario.

My question is this one:

Given the fact that one of the filters selects a much smaller set of data, could I improve performance if the first index is the most selective one (the one which allows a smaller set)? I had never considered this question until I saw the graphics from the referenced article:

Image taken from the referenced article about multicolumn indexes.

The queries use values from the two columns for filtering. I have no queries using just one column for filtering. All of them are: WHERE c1=@ParameterA AND c2=@ParameterB. There are also conditions like this: WHERE c1 = "abc" AND c2 LIKE "ab%"


texts:
I have a seemingly easy question. How can I move a xxx.bak file to another location with T-sql?

I have a job that takes a copy from database a to c:mycopy/copy1/a.bak and I want to create a step that moves the file to c:mycopy/copy2/a.bak
I tried:

 MOVE N'c:mycopy/copy1/a.bak'  TO N'c:mycopy/copy2/a.bak'

Sorry for the stupid question and thanks for your help


texts:
I need to login to MS Sql from ODBC to my MS Sql instance on Linux server. Following are my question

Is there a way to do it without Active Directory (maybe with LDAP or ADLDS if it does not change user profile)?
I created a SQL Server Login without password by deselecting enforce password policy. I can finish adding ODBC and Test Connection is successful, but when I run the program it gives an error the the login has failed and in the SQL Server Log it shows:

  Login failed for user 'gp'. Reason: Password did not match that for the login provided".

The program I am running is a old program and I don't have the source code to change anything in it. Currently it works with Windows Authentication on a server running Windows Server and Microsoft SQL Server.


texts: Generate due dates using function in a select statement

texts:
I want to create a semi - monthly amortization schedule by using a function.

Here is my function:

CASE WHEN day(@dt) < 16
THEN
   CASE WHEN month(@dt) = 2 -- IF FEBRUARY, THEN GET LAST DAY OF MONTH
   THEN
      eomonth(@dt)
   ELSE -- SET AS 30
      dateadd(day, 30-day(@dt), @dt) end
ELSE
   dateadd(month, 1, dateadd(day, 15-day(@dt), @dt)) -- SET AS 15
END

Let's say I already have a table of schedule like this

instno |  amount   |  duedate
------------------------------
1      |  1000.00  |  1-30-2020
2      |  1000.00  |  NULL
3      |  1000.00  |  NULL
4      |  1000.00  |  NULL
5      |  1000.00  |  NULL

I want to fill the remaining 4 installments using the function I created or similar.

So far, I have tried doing,

select *,
    dbo.duedategenerator(LAG(duedate,1,'1-30-2020') OVER (ORDER BY b.ilno))
from table

Output should be like this:

instno |  amount   |  duedate
------------------------------
1      |  1000.00  |  1-30-2020
2      |  1000.00  |  2-15-2020
3      |  1000.00  |  2-29-2020
4      |  1000.00  |  3-15-2020
5      |  1000.00  |  3-30-2020


texts: I accidently dropped the REPCAT$_USER_PARM_VALUES table from the system schema in my oracle db. Will it cause any problems in the future? What are these repcat$ tables for?


texts: SQL Server mirroring for more than 10 databases

texts:
I have SQL Server instance that has more than 40 databases and I want to make mirroring to another instance server.

But when I read about mirroring I find this restriction:

  On a 32-bit system, database mirroring can support a maximum of about 10 databases per server instance because of the numbers of worker threads that are consumed by each database mirroring session, for 64-Bit Operating system you can mirror more than 10 databases depending on the number of processors and worker threads.

Question

How can I make mirroring for all my databases? Or, what I need as a servers equipment to make mirroring for all my 40+ databases?


texts: Creating a User to access multiple databases

texts:
Is it possible in SQL server to create a User without a Login that can access multiple databases?

I am creating a user setting up a service broker queue and the receiver of the message needs to be able to collect data from several databases.


texts:
The skip on mysql slave was set very high and it appears to have skipped a table that was created on the master. I've corrected the skip and now the slave has stopped because new data trying to write to the table that is missing on the slave is generating errors. So doing a skip 1 won't work until the slave creates the missing table from the master.

My question is, how do I force the slave to query the master for all the tables/data which it had skipped?  I effectively want something that will compare the differences between master/slave and update the missing information on the slave. Is this possible? Or will I need to do a stop of the master and just rsync everything to the slave (which might take all day for 350Gigs).

Suggestions?


texts: What does a deterministic symmetric key mean?

texts:
In sqlservercentral blog I've encountered this phrase

Symmetric keys are deterministic
when created, meaning that the same parameters run in different
databases will result in the same key. That means that the same key in
a different database (or instance) can decrypt data that was encrypted
in your production instance. Keep control of the parameters used to
create symmetric keys and secure them. That means watch out for
storage of these items in source control, in installation files,
upgrade scripts, etc.

I can't understand what it tries to say. Symmetric keys are protected by DMKs, so how can they be the same on different instances?
Thanks.


texts:
We have a new SQL server (Dell PowerEdge R730xd). It has a RAID 5 for the OS and the database files for SQL, and also has two SSDs. It is running Windows Server 2012 R2 Standard and SQL Server 2014 Standard.

One of the SSD drives holds the log files for the SQL databases. The other holds the backup folders and files for the same.

We only have one instance.

One of the SSDs is the subject of troubleshooting, so we need to remove it and replace it with the replacement SSD.

The drive to replace is the drive that holds the SQL backup files.

What steps do I need to take to switch out the SSD with a new one so there is no corruption, lost data, etc?

I have been researching this and I think that I should be able to shut down the SQL services, copy over the backup folders to another drive temporarily, switch the SSDs (they are supposed to be hot swappable, but I may need to reboot if they don't show), copy the backup folders back over to the new SSD, start the SQL services and everything should work fine.

Is that correct? Do I need to take any other steps, like detaching databases or something? I am extremely new to SQL Server. Advice from an experienced DBA would be greatly appreciated.

Also, would these steps be the same if I needed to switch out the other SSD that currently holds the LDF files?

I did do some Google searches, but all I found were topics on how to move the backup folder location to a different drive. I have not done that before. I could change the backup files location in SQL Server Management Studio 2014 to a different drive, then go about switching SSDs, then change the location back, but I am not sure if there are "gotcha" steps that I would need to be aware of in that process, like checking for changing Windows permissions, disabling SQL service accounts, etc.

Also, I am asking because I am not sure if it is simple as it seems. I could just move the backup folders and files, but I am not sure what steps I need to take to shut down/disconnect/detach the SQL databases properly.


texts: MongoDB user password changed for already open connection

texts:
We're introducing this new password management infrastructure that changes passwords every X days, but there're these existing implementations in place that use singleton repositories, so once a connection gets established, the object stays in memory and does not have to re-authenticate/open connection next time the database access is required.

My question is, would existing open connections for users get destroyed if their password get changed?

Drivers in question are .NET and Java.

I would be surprised if connections stayed open, but would also like to know how MongoDB does cleanup of unauthorized connections and how quick can this cleanup happen after the password change.


texts: How do I limit dynamic data masking with a where clause?

texts:
I am interested in using SQL Server dynamic data masking on a subset of rows within a table. Is it possible to do this?
As an example, I have a table of users with email addresses. I do not want to mask records for my company's internal users. I would like to use a where clause such as where email not like '%acme.com'.
After reviewing the responses, I realized that I need to clarify further. I would like for all non-administrative users to see the same query results. When they query the users table, the resulting rows with an email of acme.com should not be masked and the resulting rows with a different email domain should be masked.


texts: Oracle Missing Comma error on check constraint

texts:
I'm attempting to create a table in Oracle, but keep getting the "ORA-00917":missing comma error at the end of my first constraint (P_IMAGEFILE_CHECK). I've tried adding and taking away commas but nothing has worked, and I do not see how the statement is incorrect. Any suggestions?

CREATE TABLE P_SPECIES (
GENUS                         VARCHAR2(40 BYTE)         NOT NULL,
SPECIES                       VARCHAR2(40 BYTE)         NOT NULL,
COMMONNAME                    VARCHAR2(80 BYTE)         NULL,
SPECIES_DESC                  VARCHAR2(1000 BYTE)       NULL,
SPECIES_IMAGEFILE             VARCHAR2(20 BYTE)         DEFAULT 'NOIMAGE.JPG' NOT NULL,
CONSTRAINT                    P_IMAGEFILE_CHECK         CHECK
              (REGEXP_LIKE(SPECIES_IMAGEFILE(UPPER('[a-zA-Z0-9._%-]+.JPG')))),
CONSTRAINT          P_SPECIES_COMMONNAME_UK1            UNIQUE(COMMONNAME),
CONSTRAINT          P_SPECIES_PK                        PRIMARY KEY(GENUS, SPECIES),
CONSTRAINT          P_SPECIES_FK1                       FOREIGN KEY(GENUS)
              REFERENCES P_GENUS(GENUS)

);


texts: how to associate columns between differnt tables

texts:
So i'm designing a database for my application which is basically a qustionaire. I designed my db as best as i could and i think it kinda works.
The questionair isn't straightfoward. by this i mean there are multiple questionairs and the questions are not a simple (yes/no). some questions are multiple choice (lists) some are a yes/no (enum) and for some qusetions i have a other 'option' in which users can input string.
Here are some questions which i think illustrate the returned data types:
q1) do you like cats?

yes
no

q2) pick toppings on your burger?

onions
tomatoes
cheese
pickles

q3) what is your favourite number?

one
two
three
other    [ ' enter your favourite number ' ]

What i am trying to figure out
How can i associate questions with their possible "options"?


texts: "ORA-24454: client host name is not set" when connecting from Ubuntu instance on AWS EC2

texts:
Some background: I am attempting to connect to an Oracle database. I have a username, password, IP address, port, and service name. I only have access to this database from an Ubuntu EC2 instance on AWS because the Oracle database is located inside another organization. Due to this, I do not have access to a DNS hostname for this machine.

I downloaded the "Oracle Instant Client with SQL Plus" client package from Oracle's website and extracted it to a folder. When I try to run it, I get this message:

  ORA-24454: client host name is not set


texts: How many partitions should I make for my clustered columnstore index tables? Should I partition the rowstore tables also?

texts:
I have a data warehouse comprised of four clustered columnstore index tables (CCI) and nine rowstore tables. These tables are used only for analytics and the CCI data is inserted from staging tables every 15 minutes. I am looking to optimize query performance by adding partitions and sorting.

All queries of this data are predicated on an integer field with about 350 distinct values.The leftmost CCI has 100M records and 125 columns. There are three child CCIs that have that same integer field. CCI 2 has 15M records and 150 columns, CCI 3 and 4 both have about 30M records and 25 columns each.

Of these 350 distinct integers the distribution of record count in the leftmost table is as follows:

5% Greater than 1M
46% Greater than 100K
83% Greater than 10K

Additionally, there are nine other rowstore tables that also join to the CCIs. These have trickle inserts, are children of the CCIs, and they all contain the same integer field. These rowstores have similar or smaller record volumes, < 10 columns each, two contain LOBS, and two undergo mass-updates frequently (these updates are also predicated on the ID field).

How many partitions should I make?

Should I partition the rowstore tables also?

Are there important considerations I am overlooking?

Note regarding the "sorting" I mentioned earlier:

A date field in the leftmost CCI is often a secondary predicate in these queries, therefore I am looking into re-sorting that CCI by date every four weeks or so as maintenance. I will achieve this sort by dropping the CCI, adding a clustered rowstore index on the date, dropping that index, and then re-adding the CCI with MAXDOP=1. I am also looking at sorting the child CCIs by the join key to their parent.


texts:
I want to create an alias for a SELECT statement, but then I want to limit the resultset with a TOP clause. If the procedure TopCount parameter is bigger than 0, I want to put limit result set with TOP clause.

But after the WITH clause, the IF statement is producing an error. Can I continue with IF or different than the SELECT statement after the WITH clause?

WITH
   alias AS (...)
IF @TopCount>0
   SELECT TOP (@TopCount) * FROM alias;
ELSE
   SELECT * FROM alias;
GO


texts:
Current Structure in MYSQL:

CREATE TABLE `JOB_ONPREM` (
       `JOB_ID` bigint(20) NOT NULL AUTO_INCREMENT,
       `JOB_NAME` varchar(255) NOT NULL DEFAULT '',
       `SANDBOX` varchar(100) NOT NULL DEFAULT '',
       `JOB_SUB_TYPE` varchar(500) DEFAULT NULL,
       `PARENT_JOB_NAME` varchar(255) NOT NULL DEFAULT '',
       PRIMARY KEY (JOB_ID,`JOB_NAME`,`SANDBOX`,`PARENT_JOB_NAME`)
     ) ENGINE=InnoDB AUTO_INCREMENT=10493 DEFAULT CHARSET=utf8

  I want to keep JOB_NAME,SANDBOX,PARENT_JOB_NAME as Primary KEY and
  JOB_ID as Auto Increment because I am using "ON DUPLICATE KEY UPDATE"
  and because of auto-increment it is inserting new rows and creating
  duplicate in table.

  And while removing job_id from primary key. I am getting error as
  "Incorrect table definition; there can be only one auto column and
  it must be defined as a key"


texts:
Forgive me if this question has already been asked - I've hunted around, but can't find something that matches my situation.

Say I have my Principle server (DB1), with a database Mir1.  I set up mirroring with my Mirror server (DB2).  Everything is fine.  I can failover, and see the Principle move from DB1 to DB2, and I can see it go back again.

Now, imagine we need a new database called Mir1DW, which is a replicated database off Mir1.  The distributor is on another machine altogether (DB3).

I need this Mir1DW database to be available, and to be continually updated (via replication) after the mirroring has done a failover.

I'm struggling to work out what's required.  I can find plenty of examples of mirroring with replication where the agents (using PublisherFailoverPartner) change where the data is coming from, but (from what I gather), the Mir1DW database itself is not included in the mirroring.

I've got a few limitations that I need to work within -

Must be in SQL 2012 Standard (Enterprise not an option)
Both the Mir1 and Mir1DW databases must be available and functioning correctly after a failover
The Mir1 and Mir1DW databases must be on the same server (i.e., both on DB1 when running normally)

Now, I assume I would need a publisher on DB1 and DB2 (and probably DB3), and a subscription on either/both DB1 and DB2.  But I can't get my head around how to get subscriptions to play ball with mirroring.

Any pointers would be greatly appreciated!


texts: How to use NOTIFY with postgres to send previous record and new record

texts: Say we update a record in a database table in postgres. How can I set up NOTIFY, to send a json blob of the previous version of the record and the new version of the record? I have read about NOTIFY quite a few times and never figured out how to use it.


texts: Can I rename the values in a MySQL ENUM column in one query?

texts:
Suppose I have a Database table with an ENUM('value_one','value_two').
I want to change that to an ENUM('First value','Second value'). I an currently doing that as follows:

ALTER TABLE `table` MODIFY `column` ENUM('value_one','value_two','First value','Second value');
UPDATE `table` SET `column`='First Value' WHERE `column`='value_one';
UPDATE `table` SET `column`='Second Value' WHERE `column`='value_two';
ALTER TABLE `table` MODIFY `column` ENUM('First value','Second value');

Is there a more efficient way to do this, E.G. a way to accomplish this with a  single ALTER TABLE statement?


texts: Compare Indexes/Constraints between two SQL Server databases

texts:
I have two databases on one SQL Server that I would like to compare Index/Constraints. The two databases should be identical but I have a suspicion that one database is missing a index or constraints. Is there a way to script this out to compare the two?

Thank you!


texts: Store a revenue based dataset in a relational database

texts:
I have several revenue based datasets with below structure:

Subject                 2012   2013    2014    2015
online marketing         54      50     60      80
website ads             900     850    320     250
mobile ads               60      80    120     130
video ads                30      40     50      80

there are over 10 tables (example: website_1 / website_2 / website_3). I was planning to change the dataset to below structure so I can join tables and do comparison of data based on year.

Subject                 revenue   year
online marketing        54        2012
online marketing        50        2013
online marketing        60        2014
online marketing        80        2015
website ads             900       2012
....
mobile ads              60        2012
....
video ads
...

I dont think the above structure is efficient.
what would be the best way to store such data in a relational database?


texts: What is the Best Practices for Enabling and Disabling the MaxDop Setting for SQL Server on regular basis. Is it ok to create a job and run the job as required or have the script stored as SP?


texts:
I'm trying to find all possible character combinations in a variable length string.

For example: '--' would have 2^n = 2^2 = 4 possibilities, 'x-', '-x', 'xx', '--'

I think that essentially I need to loop through c(2,2) + c(2,1) + c(2,0) where
c(n,r) = n! / (r! * (n-r)!) but I'm having trouble getting things to work in with a cte. So far everything starts to break down with you add characters to the string.

Using a numbers table -

declare @s varchar(15)
set @s = '--'

;with subset as (
select cast(stuff(@s,number,1,'x') as varchar(15)) as token,
     cast('.' + cast(number as char(1)) + '.' as varchar(11)) as permutation,
     cast(1 as int) as iteration ,
     number
from numbers where number between 1 and len(@s)
union
select @s, '.0.', 1, 0
) ,

combination as (
select  cast(stuff(token,n.number,1,'x') as varchar(15)) as token ,
    CAST(permutation+CAST(n.number AS CHAR(1))+'.' AS VARCHAR(11)) AS permutation,
    iteration + 1 as iteration,
    n.number
from subset s   inner join numbers n on substring(s.permutation,2,1) = n.number + 1
where n.number between 1 and len(@s)
)

select * from subset union combinations

This returns

token           permutation iteration   number
--------------- ----------- ----------- -----------
--              .0.         1           0
x-              .1.         1           1
-x              .2.         1           2
xx              .2.1.       2           1

I can't figure out how to get it working past two characters ( '---', '----' ...)
Maybe I'm looking at this wrong any pointers would be greatly appreciated. Sorry about the sql i know it's pretty ugly with lots of errors. Even after days of research my knowledge of cte's is horrid.


texts: Upgrading SQL Server with Integration Services

texts:
We have a database running SQL Server 2008R2 which requires an upgrade (to SQL Server 2014) because it is a pre-requisite for the latest edition of the software which the database is the back end of.

Integration Services (2008R2) is also running on this server, with many .dtsx packages stored in MSDB. They are mainly used for ETL purposes and transforming data for reporting reasons.

Due to the nature of the environment, it will need to be an in-place upgrade. The database engine upgrade will be a breeze, but I was wondering specifically about the .dtsx packages in MSDB. Will they automatically upgrade when the database engine and integration services is upgraded, or will there be a need to manually upgrade them after this.

Are there any known issues with upgrading Integration Services between 2008R2 and 2014 SP2?

Fortunately we have a mirrored environment running in a VM which we can mess around with in terms of testing the application (and packages) before we move to Live.

Thanks


texts: ORA-00942: table or view does not exist- Can it be solved by granting privileges to user?

texts:
I am creating a Java application that connects to an Oracle Database.

In the application I connect to the DB as User A, which works as intended.

I then try to access tables in Schema 1 and Schema 2.

However when I do so I get the error:

ORA-00942: table or view does not exist

I looked in SQLDeveloper and in the "other users" section of User A I cannot see any tables for Schemas 1 and 2.

How can I solve this error so that I can see and access tables in Schemas 1 and 2?

Can I do this by assigning GRANT SELECT privileges on User A for schemas 1 and 2?


texts: Cannot access any database in SQL Server 2008 R2 Express after moving workstation from domain to workgroup

texts:
My workstation that has SQL Server 2008 R2 Express for development purposes installed has been moved out of a domain into a Windows workgroup. While being part of the domain I used the account "MyDomain\MyUserName" that had domain admin rights. Now I log in to my computer as "MyComputerName\MyUserName" that has local admin rights.

In the SQL Server Configuration Manager I have changed the account that starts the "SQL Server (Express)" service from "MyDomain\MyUserName" to "MyComputerName\MyUserName".

I can connect with the instance in SQL Server Management Studio (using Windows authentication) and I can see all databases in the Object Explorer in the left side. But once I try to open any database node in the Object Explorer I get a message that access to the database is not possible. I get this message for all databases except system databases like "master" or "tempdb".

If I try to run any query on one of the databases I get a "SQL Server Message 916":

  The server principal "MyComputerName\MyUserName" is not able to access
  the database "MyDatabase" under the current security context.

Is there any way to the get access to my databases again?

Edit

I also tried to create a new database manually in SSMS but it throws the error:

  CREATE DATABASE permission denied in database 'master' (Microsoft SQL
  Server, Error: 262)

Edit 2

I forgot to mention that the workstation runs with Windows XP (Service Pack 3) and the domain the workstation has been removed from was an old Windows NT domain - in case this should be important to find the reason for the problem.


texts: How do I auto-fill data in Microsoft Access 2003?

texts:
I am creating a Purchase Order Form at work using Microsoft Access 2003 so that it can be saved and archived in a database and reduce paper costs.

These are my current dilemmas:

I am unable to figure out how to auto-fill certain fields in the form. For example, I have an PO# Field which will serve as the ID section of the form. I want this to be auto-filled with each new form so that the first form created will have 001 and the second form created will have 002. I haven't been able to figure this out.
I have a section that has Quantity, Unit, Description, Unit Price, Price Total etc. How do I get Price Total automatically filled by doing the following: Quantity * Unit Price
I have Subtotal, S&H, Sales Tax, and Total. How do I get Total to be the sum of all these things?

I have been trying to figure these out using queries and tables, but I can't get it to work. I am currently using Microsoft Access 2003 in Form Design View.


texts: Calculate change in value window function when the identifier has dropped of the current period

texts:
I was wondering how can i got about finding the change in value from one period to another but in my scenario the identifier has dropped off the current period.

I'm think this could be done with a cte and a select distinct id but i'm not sure how to implement.

Some demo date below

CREATE TABLE basic_pays (
    employee_id int,
    fiscal_year INT,
    salary DECIMAL(10 , 2 ),
    PRIMARY KEY (employee_id, fiscal_year)
);

INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(1,2017,24000);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(2,2017,17000);
INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(1,2018,25920);

SELECT
    employee_id,
    fiscal_year,
    salary,
    salary - LAG(salary)
        OVER (PARTITION BY employee_id ORDER BY fiscal_year) salary_change
FROM
    basic_pays
GROUP BY
    employee_id, fiscal_year, salary
ORDER BY fiscal_year, employee_id

DROP TABLE basic_pays

The expected output would be

| employee_id | fiscal_year | salary   | salary_change |
|-------------|-------------|----------|---------------|
| 1           | 2017        | 24000.00 | NULL          |
| 2           | 2017        | 17000.00 | NULL          |
| 1           | 2018        | 25920.00 | 1920.00       |
| 2           | 2018        | 0.00     | -17000.00     |


texts: How to update an element in json array with where clause?

texts:
I came across this question on how to update nth element of array, but it has nested json values in it instead of a plain array of strings.

Here are some sample json values from jsonb column of the table.

{"pattern": {"values": ["foo", "edfgh"]}}
{"pattern": {"values": ["abc", "abdhi", "foo"]}}
{"pattern": {"values": ["these", "abcd", "para", "avil"]}}

We can select the nth element using

select data #> '{pattern, values, 0}' from table;

How can we loop through array and update foo to bar if it is present in that array?


texts: How to grant all privileges to a user from root user in MySQL on command line

texts:
I want to execute a command as root to grant privileges to a certain user to a certain "empty" database.

I have tried many attempts and cannot get the syntax to work:

mysql --host=localhost --user=root --password={root password} --execute="GRANT ALL PRIVILEGES ON `otft_oms_dev_userID-001.*` TO `otft_omsUser`@`localhost` IDENTIFIED BY `{user password}` WITH GRANT OPTION"

-bash: {part of user password}`: event not found

mysql --host=localhost --user=root --password={root password} --execute='GRANT ALL PRIVILEGES ON `otft_oms_dev_userID-001.*` TO `otft_omsUser`@`localhost` IDENTIFIED BY `{user password}` WITH GRANT OPTION'

ERROR 1046 (3D000) at line 1: No database selected

mysql --host=localhost --user=root --password={root password} --execute="GRANT ALL PRIVILEGES ON 'otft_oms_dev_userID-001.*' TO 'otft_omsUser'@'localhost' IDENTIFIED BY '{user password}' WITH GRANT OPTION"

-bash: {part of user password}`: event not found

mysql --host=localhost --user=root --password={root password} --execute="GRANT ALL PRIVILEGES ON 'otft_oms_dev_userID-001.*' TO 'otft_omsUser'@'localhost' WITH GRANT OPTION"

ERROR 1064 (42000) at line 1: 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 ''otft_oms_dev_userID-001.*' TO 'otft_omsUser'@'localhost' WITH GRANT OPTION' at line 1046

mysql --host=localhost --user=root --password={root password} --execute="GRANT ALL PRIVILEGES ON otft_oms_dev_userID-001.* TO 'otft_omsUser'@'localhost' WITH GRANT OPTION"

ERROR 1046 (3D000) at line 1: No database selected

mysql --host=localhost --user=root --password={root password} --execute="GRANT ALL PRIVILEGES ON `otft_oms_dev_userID-001.*` TO 'otft_omsUser'@'localhost' WITH GRANT OPTION"

ERROR 1064 (42000) at line 1: 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 'TO 'otft_omsUser'@'localhost' WITH GRANT OPTION' at line 1


texts: ODBC Curtime() after migration from 2000 to 2012 omits datepart

texts:
A client migrated a db from SQL Server 2000 to 2012.

Their application runs into trouble afterwards. The cause is the following function invocation:

SELECT {fn curtime() }

Which returned date + time on SQL 2000 ('2017-01-20 07:24:26.997'), but only returns the time part ('07:24:26.997') on SQL 2012.

The compatibility mode cannot be set back further then 90 (SQL 2005).

Given that the application cannot be changed, is there any alternative solution for this issue?


texts: Why is there a collation conflict between system views

texts:
I'm using the following statement to retrieve a list of triggers from the database:

select tr.name
from sys.triggers tr
union all
select tr.name
from sys.server_triggers tr;

However on my local SQL Server 2012 Express installation this fails with the error:

  Cannot resolve collation conflict between "Latin1_General_CI_AS" and "Latin1_General_CI_AI" in UNION ALL operator occurring in SELECT statement column 1

I can "fix" this by changing the statement to explicitely use a different collation in the second part of the union:

select tr.name
from sys.triggers tr
union all
select tr.name collate database_default as name
from sys.server_triggers tr;

However I feel this is an ugly hack, and I don't really understand why there is a conflict in the first place. Both views ultimately retrieve the name from sys.sysschobjs.name and thus all values should have the same collation.

So my two questions are:

why is there a collation conflict in the first place?
is it safe to use collate database_default only in the second part, or should I better use it for both queries (performance is not an issue for this).

Interesting enough the same query does not create a problem when using it against a SQL Server 2005 (standard edition) with the same triggers defined.


texts:
We have a table for countries, using ISO 3166-1 standard, also languages, using ISO 639-1. Now we need a table for nationalities, but I can't find a standard for abbreviating or indexing them.

Country and nationality does not always relate, i.e. there is Scottish nationality, but Scotland doesn't have it's own alpha-2 country code.


texts: pgAdmin 4 v4 does not prompt to save on page refresh

texts:
(I can't seem to find this issue anywhere else despite being a seemingly common issue)

Typically when I'm using the Query Editor I press F5 to run my query. With pgAdmin being a browser application, this can be an issue since F5 also refreshes web pages. In pgAdmin 4 v3, it would prompt to save my file if it tried to refresh the page, so it wasn't a big deal. It doesn't do that in version 4, so I have to save frequently and, when I forget, I just lose my progress with no warning.

How come pgAdmin 4 doesn't prompt to save? Is there a way to turn this back on? Is there a way I can reach out to the developers and request this be a feature, if it isn't already?

Thank you in advance.


texts: How would I sync tables in two separate servers in mysql?

texts:
DB: mysql  Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using
readline 5.1

I have two separate servers running MariaDB 5.5, both in production.
Both setups are live, and users might access any setup they want.
I need to sync certain table columns from these two databases.
The tables I need to be synced are Userprofile and Devicedetails. Both have a lastlogin and lastused column respectively, and the one with the later date would need to be replaced in the other database.
Schema for Userprofile:
+-------------------------+------------------+------+-----+---------+----------------+
| Field                   | Type             | Null | Key | Default | Extra          |
+-------------------------+------------------+------+-----+---------+----------------+
| ProfileId               | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Username                | varchar(255)     | NO   |     | NULL    |                |
| Disabled                | int(11)          | NO   |     | 0       |                |
| Tokenhash               | varchar(50       | NO   |     | 0       |                |
| LastLoginTime           | datetime         | NO   |     | NULL    |                |
+-------------------------+------------------+------+-----+---------+----------------+

Schema for Deviceprofile:
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| Userid           | varchar(255) | NO   |     | NULL    |       |
| Registerdate     | date         | YES  |     | NULL    |       |
| Approved         | int          | YES  |     | NULL    |       |
| MACaddress       | varchar(512) | NO   |     | NA      |       |
| Hostname         | varchar(255) | NO   |     | NA      |       |
| Domain           | varchar(255) | NO   |     | NA      |       |
+------------------+--------------+------+-----+---------+-------+

As a user logs into a server, their lastlogintime would update. If they create a new secret, that would also update (only used for login, think something like a TOTP Token). This user could tomorrow login to the other server, and they should be able to use their existing tokens.
Similarly, their device details would be captured, and Approved would be set to 1, via an external process. This should also be carried over, in case the user accessed the other setup the next day.
If the data from the remote is latest, it should replace in the current table.
I have looked into I..ODKU, but it seems that requires a primary key to clash to update the row, but Profileid is auto-increment, so that would just add rows to the table.
So far, I've exported the data I need into a CSV, and I'm stuck at how I would check which is greater, by looping over the file and checking each individual entry, but there must be a better way of doing this that is far more efficient.


texts: SSIS job runs successfully but doesn't load data

texts:
I am running a job from SQL Server Agent and the job runs successfully. However it doesn't load any data (in my case Excel files from a local folder). I created a new Proxy Account to run this job but it still doesn't help. I checked that the proxy user has SYSADMIN rights and also permissions to access the Excel files from the folder.

I run the package from Visual Studio and it runs successfully and the files are loaded. This is done manually every time.

We want to fix this issue. Can someone please help me with this. I can share any more information as needed.


texts: Can we determine FDs if we are given some tuples?

texts: I am taking a databases course and i'm learning about functional dependencies. I have searched around and did not find any information on this. My questions is if we are given a table with some tuples, are we able to determine the FDs for this relation instance?


texts: Effect on execution plans when a table variable has a primary key

texts:
Having read a great deal about the differences between temporary tables and table variables in SQL Server, I am experimenting with switching from mostly using temporary tables to mostly using table variables.  (They seem to be a better fit for the types of queries I usually work with.)

In these queries, the tables hold unique identifiers that drive the lookup process.  It's been my habit, when working with temporary tables, to include a PRIMARY KEY constraint so that the query optimizer is aware that it won't see any duplicates.  However, given that the optimizer (in most circumstances, and for my queries) assumes that a table variable only holds a single row*, which is unique by definition, is the query optimizer going to make choices any differently if there's a PRIMARY KEY constraint?

* Technically, it assumes there are no rows, but replaces the zero with a one.  (Because the zero interacts very poorly with the rest of the estimation process.)  But it also depends on whether the table variable is populated or not when the query is compiled.  There is some background information here: What's the difference between a temp table and table variable in SQL Server?.

I'm currently using SQL Server 2014, but I would be curious if the behavior changes in newer versions.

As has been pointed out, a PRIMARY KEY constraint comes with a  clustered index that gives the query optimizer more choices on how to get data out of the table variable.  I was aware of this and thinking about the rest of the query plan.  But after attempting to clarify my question, I've decided that the question I was attempting to ask was too broad and probably particular to my extreme situation.  (Nothing but navigational-type queries into half-a-trillion-row tables with an expectation of sub-second performance.)  So I am going to leave my question as-is.


texts: Postgres complex insert or update with 2 unique constraints

texts:
I have a table that has these key columns. Other columns are present but these are the key columns for this question.

user_id: string,
external_id: string
disabled_by: string

Additional notes:

All columns have the not null constraint.
unique constraint ('external_id_constraint_1') (external_id, disabled_by)
unique constraint ('external_id_constraint_2') (user_id, external_id)

Operations:

Add external id

Adding an new external id for user 1 it would look like this:

insert into external_ids (user_id, external_id, disabled_by) values ('user1', 'exid2', '');

Disabling an external id

If the external id is later revoked for user 1:

update external_ids set disabled_by='admin1' where user_id='user1' and external_id='exid2';

Reenabling the external_id

A user can't have more than 1 entry for a given external_id. If a row exists ('user1', 'exid2', 'admin1') then the row needs to be updated:

update external_ids set disabled_by='' where user_id='user1' and external_id='exid2';

Rather than a new row being created. However another user between the disable and the reenable attempt could have claimed the external_id. If this has happened the update should do nothing.

Solutions attempted

I am trying to get a single postgres sql statement to handle this case.

Things I tried:

an insert into .... on conflict on constraint ... except that I need to handle 2 different constraint violations differently.
update does not have the ability to do the insert if there is nothing to update
update doesn't have the ability to handle a constraint violation.

Question:

Is it possible to do this in a single sql statement or am I doomed to be frustrated.


texts: Not able to restore a backup to SQL Server 2019

texts:
I am trying to a restore a SQL Server from a .bak file and always get the same error :

The database was backed up on a server running version 15.00.2000.
That version is incompatible with this server, which is running
version 15.00.2000.

I have already seen the issue in Stackoverflow but I'm struggling because the versions here are the same ..
I am using SSMS and I already tried restoring to a SQL Server 2017, to the latest Cumulative update of SQL Server 2019 and to the first SP of SQL Server 2019. Wondering if I am missing something (configuration, code, ..) in SSMS to make it work.


texts:
There could be a lot of reasons why an Oracle account can be locked. When an account is locked and the users is asking for our help, following could be the possible cause of account lock:

A user with appropriate privileges locks an account manually (aka ALTER USER user_name ACCOUNT LOCK)
Password parameters from profile

User exceeds FAILED_LOGIN_ATTEMPTS
PASSWORD_LIFE_TIME is exceeded, or both that and PASSWORD_GRACE_TIME is exceeded.

Are there any other places we should see, or any other ways an account could get locked?


texts: Check and Retrieve those results where a cell(row) contains atleast one value from many values in a column

texts:
Let me give you an example :

I have a table (name = posts) which contains following rows:
   id    |    title   | .... |  names
    1    |      .     |   .  |
    .    |      .     |   .  |
    4    |     ABC    |   .  |  joe  , nash  , brad
    5    |     XYZ    |   .  |  jeff , michael ,gerard, pique
    6    |     JKQ    |   .  |  archeo , michael ,neymar, schultz

Another table (name = followed) containing rows:
   id    |  names
    1    |  jeff
    2    |  michael
    3    |  cheryl
    4    |  zohan

Given : Two tables
To fetch : Titles from posts table where names.posts contains atleast one name from names.followed in followed table
Result I should get in the above example : XYZ , JKQ
query form : mysql , PDO

I dont know if its possible .The reason I am storing values in cells with names concatenated with commas is to reduce redundancy. I have basic query knowledge only .If its possible it will be great if some one can answer me a query/queries to get asked result . If not possible just give me an alternative method with a different table structure and an explanation why its the best alternative solution .
Big Thanks to all who can help me in any way possible


texts: Why I get Cannot achieve consistency level ONE (Code 4096)?

texts:
Why I write to table, I get error:

Cannot achieve consistency level ONE (Code 4096)

What could be wrong?
Here is my descriptions:

DESCRIBE schools

CREATE KEYSPACE schools WITH replication = {'class': 'NetworkTopologyStrategy', 'eu-central-1': '2'}  AND durable_writes = true;

CREATE TABLE schools.locations (
    school_id int,
    bus_id int,
    date text,
    unix_time bigint,
    lat double,
    long double,
    round_id int,
    PRIMARY KEY ((school_id, bus_id, date), unix_time)
) WITH CLUSTERING ORDER BY (unix_time ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';


texts: Why am I getting blocked when I call OBJECT_NAME(object_id, database_id) from sys.dm_tran_locks?

texts:
I'm working on a query to tell what objects have locks from a given session and I'm running into some blocking issues.

Here is my basic query:

SELECT request_session_id AS session_id,
    request_owner_id AS transaction_id,
    OBJECT_SCHEMA_NAME(resource_associated_entity_id, resource_database_id),
    OBJECT_NAME(resource_associated_entity_id, resource_database_id),
    COUNT(1) AS lock_count
FROM sys.dm_tran_locks WITH (NOLOCK)
WHERE resource_type = 'OBJECT'
GROUP BY request_session_id, request_owner_id,
    resource_database_id, resource_associated_entity_id

When I run this query I am occasionally blocked by one of the sessions actually holding the locks.  If I remove OBJECT_NAME and OBJECT_SCHEMA_NAME then I don't have any problems.  I've tried dumping the information into a table and then using the functions on the values in the table with similar problems.

This leads me to believe the problem is in the OBJECT_NAME and OBJECT_SCHEMA_NAME functions but I'm not sure why or how to get around it.  I'm also not certain why I am sometimes blocked and sometimes not.  Does anyone have any suggestions?


texts: Postgres sharding installation with citusdb

texts:
We are developing a prototype for a BIG data product. We have almost 2 billion records. We have used PostgresSQL 9.5 as a back-end and Python as front-end.

We are using a 16*2.4 GHz processors with 160 GB RAM on Amazon servers.

Our bench mark for query results are 10 seconds maximum, although simple count queries on tables with index are taking approximately 30 minutes to 1 Hour .

To overcome our performance issues I required changes in configuration file:

max_connections = 20
shared_buffers = 14GB
effective_cache_size = 42GB
work_mem = 367001kB
maintenance_work_mem = 2GB
checkpoint_segments = 128
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500

I created the monthly partition on sale_date column. Even though performance are still bad. I was reading some articles and found out that PostgreSQL uses a single CPU for query processing from a single connection.

To use all the CPUs for query processing we can use sharding, and pg_shard is the opensource component created by cistusdata for this exact purpose.

I have installed the citusdb as explained on multi-node-setup-page.

When I execute CREATE EXTENSION pg_shard;, I get this error:

  ERROR:  could not open extension control file "/usr/share/postgresql/9.5/extension/pg_shard.control": No such file or directory

I manually copied the pg_shard.control file in the extension folder and I then started getting this error:

  pg_shard--1.2.sql not found.

Any help is appreciated. How can I solve this problem?


texts: Possible Double-Hop issue. SSIS User changing to anonymous when making a remote query

texts:
I am attempting to execute a PowerShell script that uses Microsoft.SqlServer.Management.IntegrationServices in an Azure pipeline. The SSIS package is successfully invoked, however, the package fails when a remote connection is attempted. I feel this is double-hop related but can't figure out how to resolve the issue.
Build Server(Domain\ServiceUser) --> Integrations Server(Domain\ServiceUser) --> Database Server(Domain\ServiceUser)
NOTE: Domain\ServiceUser has rights to the SSISDB on that instance and to the remote database.
My Project Import:Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.".

In order to help diagnose if the problem is security related. I am using the procedure below to attempt to invoke the package. When I do that, I get the following error:
--EXECUTE AS LOGIN  = 'Domain\ServiceUser'
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx',
    @execution_id=@execution_id OUTPUT,
    @folder_name='ProjectFolder',
    @project_name='ProjectFolderImport',
    @use32bitruntime=False,
    @reference_id=Null
EXEC [SSISDB].[catalog].[start_execution] @execution_id

The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.
Msg 6522, Level 16, State 1, Procedure start_execution_internal, Line 0 [Batch Start Line 0]
A .NET Framework error occurred during execution of user-defined routine or aggregate "start_execution_internal":
System.Data.SqlClient.SqlException: The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.
System.Data.SqlClient.SqlException:

And when I uncomment the EXECUTE AS, I get the following:
Cannot execute as the server principal because the principal "Domain\ServiceUser" does not exist, this type of principal cannot be impersonated, or you do not have permission.

I know the package can make a remote connection to the db server because when I create a task to execute the package and select "Run as" SSIS Package Execution Proxy User "SSIS_Exec" it works fine. I need to figure out how to have my code or stored procedure do something similar to run as SSIS_Exec.