texts:
The following code was added by one of our developers to delete duplicate records from the table:

DELETE  SubQuery

FROM
(
    SELECT  ID
            ,FK1
            ,FK2
            ,CreatedDateTime
            ,ROW_NUMBER() OVER(PARTITION BY FK1, FK2 ORDER BY CreatedDateTime) AS RowNumber

    FROM    Table
)
AS SubQuery

WHERE   RowNumber > 1

When reviewing the code, I assumed that it wouldn't work, however testing it in our test environment (SQL 2014) shows that it does!

How does SQL know to resolve the sub query and delete the the records from table?


texts: SQL Server Express 2016 high CPU Usage as soon as I log into Windows

texts:
I have a Windows Server 2012 R2 configured for ASP.net applications that connect to SQL Server 2016 Express installed on the server. This server is a VPS with 3 cores and 1.5 GB RAM. The load on the server is not high at all.

I am having problems with the servers CPU Usage. Some seconds or minutes, as soon as I log in to the windows account through RDP, the SQL Server process starts consuming between 15% and 33% of CPU constantly, which is equivalent to using 1 CPU almost completely.

I first checked for running queries with SP_WHOISACTIVE and there are no running queries. (http://whoisactive.com/)

I then followed the instructions at: http://logicalread.solarwinds.com/troubleshoot-high-cpu-sql-server-pd01/#.V2xtwLvhCUk
and have found that the culprit is “% User Time” when diagnosed by the performance monitor.

I have not been able to identify what the problem is after this point. All I know is that as soon as I log into the windows account, the CPU starts being consumed. As soon as I log off, the cpu usage of the process drop down to below 3%. Sometimes, while I am logged into windows, the cpu usage drops for some minutes, but it goes up again. I don't have to open any specific Windows application for this behavior to start.

Does anyone know what could be happening?


texts: What is the best option for a large slowly changing dimension lookup table

texts:
This question is an extension of a previously asked question Index Strategy and Query optimization, which further extends this question: Search between IP string addresses

I implemented the two new indexes that Ypercube recommended. While the query for the InsideIp lookup completed much faster (1.5 minutes versus 9 minutes), the OutsideIp lookup is still running after 1.5 hours. I will let the query run over the weekend to see if it even completes. Note: the InsideIp list contains 3 DISTINCT IPs while the OutsideIp list contains approximately 60K DISTINCT IPs.

I understand the server may be constrained memory-wise and probably needs more memory and potententially a combination of more CPUs and disk(s) (for multiple database files that allow for LUN/stream optimization).

Before I travel down that path, I am thinking three alternative approaches.

Change the clustered index as previously mentioned (ProtocolKey, NetworkStartIpHex, NetworkEndIpHex, RowKey)
Place the lookup columns (ProtocolKey, NetworkStartIpHex, NetworkEndIpHex, RowKey) in an in-memory (OLTP) table.
Partition the table. But, not sure how to effectively partition over a VARBINARY(16) column. I know VARBINARY(MAX) is explicitly ruled out by Microsoft, but is VARBINARY(16) ruled out as well?

Can anyone provide some insights as to which might be better?


texts: Updating varchar to longtext leads to data loss

texts:
I run a number of MySQL servers for my Java application. I have a table that looks like this:

 +---------------+----------------+------+-----+---------+-------+
 | Field         | Type           | Null | Key | Default | Extra |
 +---------------+----------------+------+-----+---------+-------+
 | app_xid       | varchar(200)   | YES  |     | NULL    |       |
 | attr_key      | varchar(200)   | NO   | PRI |         |       |
 | entry_xid     | varchar(200)   | NO   | PRI |         |       |
 | entry_version | int(11)        | NO   | PRI | 0       |       |
 | date_value    | datetime       | YES  |     | NULL    |       |
 | int_value     | int(11)        | YES  |     | NULL    |       |
 | string_value  | varchar(10000) | YES  | MUL | NULL    |       |
 | clob_value    | longtext       | YES  |     | NULL    |       |
 | blob_value    | blob           | YES  |     | NULL    |       |
 +---------------+----------------+------+-----+---------+-------+

Now, I have some data that are in the string_value column and I want them in the clob_value column.

Initially the data looks like this:

************** 65. row *************
string_value: [a2:hoj]
clob_value: NULL
attr_key: member
************** 66. row *************
string_value: [a2:hoj]
clob_value: NULL
attr_key: member

Now I run this command:

update attribute
set clob_value=string_value,
    string_value=null
where string_value is not null
  and clob_value is null
  and attr_key in ('member');

I would expect that the resulting values to be

string_value: NULL
clob_value:  [a2:hoj]
attr_key: member

but on 4 out of my 35 servers they end up being

string_value: NULL
clob_value:
attr_key: member

where the nothing in the clob_value is actually 8 characters with ASCII value 0.

If I instead of that update run this one:

update attribute
set clob_value=string_value
where string_value is not null
  and clob_value is null
  and attr_key in ('member');

i.e. I leave out the setting of string_value to null, the result is the expected

string_value: [a2:hoj]
clob_value:  [a2:hoj]
attr_key: member

I'm puzzled by this behavior, could someone help?

MySQL version info:

  mysql Ver 14.14 Distrib 5.5.44, for debian-linux-gnu (x86_64) using readline 6.2

The create table of the table is

CREATE TABLE `attribute` (
   `app_xid` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `attr_key` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `entry_xid` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `entry_version` int(11) NOT NULL DEFAULT '0',
   `date_value` datetime DEFAULT NULL,
   `int_value` int(11) DEFAULT NULL,
   `string_value` varchar(10000) COLLATE utf8_unicode_ci DEFAULT NULL,
   `clob_value` longtext COLLATE utf8_unicode_ci,
   `blob_value` blob,
   PRIMARY KEY (`entry_xid`,`entry_version`,`attr_key`),
   KEY `attribute_string_value_idx` (`string_value`(30)),
   CONSTRAINT`attr_to_entry` FOREIGN KEY (`entry_xid`, `entry_version`)
                             REFERENCES `entry` (`entry_xid`, `entry_version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


texts: What are the best practices for ETL of data from one OLTP system to another, Part 2?

texts:
Ok, I originally started a question a ways back called "What are the best practices for ETL of data from one OLTP system to another?". And as you can probably tell from most of my questions so far, they have to deal with ETL. This is because as a developer I am seeking knowledge from those much closer to databases than I am. Also I didn't want to muddy the waters on the question I originally asked, since I did get an answer to that question. Hence, my "part 2".

With that said, I am probably going to be using custom Java code per high level design review with the architects and other key stakeholders. Now I am on to the detailed design for the ETL process. What I am wondering is what is the best way for me to insert/update the data in the target database?

Should I just use standard code looping techniques and insert/update record-by-record as I process them? Or is it better to generate a .sql flat file of all changes and then execute that at the end? Since I need to acquire target DB record keys for a key mapping database for my ETL I'm not sure the second choice is the best idea?

Any ideas? Guidelines? Help? What are the pros and cons of each? Are there any requirements (functional or non) that help you decide which option? Any help here with me understanding what I'm up against is appreciated.


texts: Change the Existing Topology of a MySQL Replication Setup

texts:
I want to be able to edit the topology of an existing replication, is that possible?

For example if I have a star topology, I want to edit it to make it a ring one.
How can I do that?

I'm thinking maybe first I need to reset all masters and servers. If I have the following topology:

    a -> b
    b -> c
    c -> a

and I want to transform it to:

    a -> c
    b -> c

I tried change master but it didn't work well because the old topology still exists.


texts: Why do VSS Snapshots cause Availability Groups to fail over?

texts:
Why do VSS snapshots cause Availability Groups to fail over?
Back in 2018, Ken Kellman and Erik Darling wrote that by freezing I/O on databases, VSS snapshots can cause Availability Groups to fail over.  https://www.brentozar.com/archive/2018/01/perils-vss-snaps/
According to Erik, "the redo threads go buck wild on the other end."
OK.  I've got buck wild redo threads.  What does that mean?
I'm seeing one of my Availability Groups fail over on nearly a daily basis.  This usually happens right around the time our Avamar server backups run.  Is there anything that can, or should, be done to tame these buck wild redo threads of mine, and prevent this daily failover?


texts: how to insert the remote table data into another table in my local computer in mysql?

texts:
Can any body tell me how to accomplish this?

I have a remote table from which I need to import part of the data and put it into a table on my computer(local). I know select into command and its use but I don't know how to use it for this kind of situation.


texts: How to model product part selection with restrictive rules?

texts:
I need to model database for product configurator where the user first selects a product template and adds features into that template. Available features need to be validated so that only features that fit previous choices are available for selection.

Example:
 1. User chooses "Car Model 1"
 2. We list available engine models
    based on "Car Model 1".

So far model for database is still manageable because it's hierarchical, we'll use some kind of dependency table:

ParentID  | ChildId
- - -- - - - - - --
<Car1>.id  | <Engine1.id>
<Car1>.id  | <Engine2.id>

Now the question is, how can I model all the cases where a certain choice restricts some hierarchically "same level" features? For example we would like to implement following logic:

"Car Model 1 can include Headlight1,Headlight2 or Headlight3"
"If you include Engine1 (in Car Model 1), you can't include Headlight2."

There is not a top-down dependency between Engine and Headlights. It's only an exception to the rule.


texts: Referring system views and functions in production functions

texts:
I am using the following system objects in a stored procedure in order to build dynamic T-SQL statements:

[sys].[objects]
[sys].[parameters]
[sys].[dm_exec_describe_first_result_set]
[sys].[columns]

As I have never used system objects in production code I am wondering are there any drawbacks or issues that could appear (for example concerning performance) or it is OK to use them as regular user defined objects.


texts: Move Our Google Cloud SQL Instance to Compute Engine

texts:
We are running several Cloud SQL Instances on GCP and plan to move all of them to some Compute Engine instances, depending on the applications that use it. Our main concern is cost of the server.
We are stil using PostgreSQL 9.6 (and have a plan to upgrade to 12.x in a few months).
I looked into Jetware with PostgreSQL 9.6 and thinking it may be the best solution. My questions are:

What kind of things we must consider when selecting an image / compute engine instance on GCP? Again, our main concern is the cost of Cloud SQL instance, so the performance should come second.

Is there any documentation I can read that recommends a non-managed services rather than a "sponsored" article that leads us to choose the more expensive Cloud SQL Instance? Yes, we must admit that it is easy to manage, etc. But again, for now, our main concern is to reduce our server cost. We have used GC SQL Instance for about 5 years.

Thank you.


texts: LOOP in a stored procedure to calculate sum

texts:
I'm trying to write a stored procedure that does the sum calculation on a field using parameters ID and date.

For this purpose I have two tables. My table that contains data

ID      CAF     SDATE
1       12345   15/06/2018
1       04564   16/06/2018
1       03589   30/06/2018
1       00099   01/07/2018
3       00056   15/06/2018
3       00325   20/06/2018
4       00123   01/06/2018
5       04445   12/06/2018
5       16676   23/06/2018

And the table that contains parameters

Prameter table

ID  SDATE1      SDATE2
1   01/06/2018  30/06/2018
3   15/06/2018  30/06/2018
4   01/06/2018  30/06/2018
5   15/06/2018  01/07/2018

My query that I want to use in my stored procedure:

select T1.ID,SUM(T1.CAF) From
(SELECT *
  FROM TABLE
  where ID = @ID
  and SDate between @SDATE1 and @SDATE2) T1
Group By ID

The result I'm expecting is the following:

ID  SUM(CAF)
1   20498
3   381
4   123
5   16676

I believe that I need to run some kind of loop to run my query for every ID, but I'm struggling a little bit.


texts: Why would SequelPro only import 23k rows out of 130k?

texts:
I use SequelPro for MySQL on a Mac OS X. I used the import function to upload a 130k .csv file to my database. Everything seems to work fine, then I get the message:

  File Read Error: An error occurred when reading the file, as it could not be read using the encoding you selected (Auto-detect - Unicode (UTF-8)). Only 23,000 rows were imported.

When I hit "Ok," everything else seems to work relatively fine, I'm just missing about 107,000 rows.

Any idea as to what it could be? Maybe I should use something other than auto-detect during the import? I thought that it might have been some extra commas floating around in the actual .csv file, which there were, but I got rid of those and the same thing happened.

Out of 130,000 rows, there's definitely the possibility for some non-English characters. Which ones doesn't MySQL accept and how would I find and replace them?

This is what I'm getting when I run the character set query:

show variables like 'character_set%';

Variable_name               Value
character_set_client        latin1
character_set_connection    latin1
character_set_database      latin1
character_set_filesystem    binary
character_set_results       latin1
character_set_server        latin1
character_set_system        utf8
character_sets_dir          /usr/local/mysql-5.6.10-osx10.7-x86_64/share/charsets/


texts: Schema design for infinitely expanding variables

texts:
I'm putting together a schema for a progressive story that will track user choices. Because it will unfold over at least a year, I need a schema that is safe to handle constant additions. I have a feeling that constantly adding new columns to existing tables is bad practice. My other consideration was that, using Postgres, I could store a JSON that could be read and replaced over time, but then nesting might be an issue if I wanted more complexity.

Are the best practices for creating a schema in which not all eventual variables are known?


texts:
I am adding column encryption to a few tables using symmetric key encryption. In each one of the procs, I write code to open and close a keys. It seems a bit verbose to open and close them in each proc. (example below )

Can I leave a key open permanently?
MSDN says keys close when a session ends. Is it possible to open a key at the beginning of a session and leave it open for all queries?

CREATE PROCEDURE [dbo].[person_sel]
AS
BEGIN

open symmetric key demoKey decryption by certificate demoCert;

select
    personId,
    name,
    email,
    cast( DECRYPTBYKEY( pin_enc) as varchar(MAX)) as pin
     from person;

close SYMMETRIC KEY demoKey;
END


texts: How to design for scalable set operations?

texts:
This is the requirement for an educational application to teach words to language learners:

Learner can declare that he/she knows a given word
Learner can memorize the given word (to be managed by the application later)
Learner can choose to ignore the given word, so that it won't be given to him/her later
Admin can ignore a word in general, so that it won't be given to any learner
A list of words exist in database, as the reference
Next word to be given to the student, should be new to him/her

This is our design:

Words
    - Id
    - Word
    - Meaning
    - PartOfSpeech

LearnerWords
    - Id
    - LearnerId
    - WordId
    - TypeOfInteraction (ignored, memorized, memorizing, reviewing, etc.)

Learners
    - Id
    - Name

And this is a pseudo-query that we execute to find the next new word for a given learner:

select top 1 *
from Words
where Id not in
(
    select WordId
    from LearnerWords
    where LearnerId = @learnerId
)

Of course the overall design and query is much more complex and more details are in action in selecting next new word for a given learner.

Now, imagine that words list contains 100K words, and a learner has already learnt more than 5K words. Using the given query, this gets slower and slower and slower by more learners learning more words.

Is there a better design for these types of business requirements? How to design for scale in this case?


texts:
I have a SQL Server query that gets the sales data for a 44 store market chain, it includes 4 values for each store (Sales, COGS, GP and GP Margin), I declared a CTE and joined it 44 times for each store to get the 4 values as columns for each store as shown below:
Query: Query1

but when I try to use PIVOT function with dynamic SQL it returns multiple nulls as shown below:
Query:Query2

the table ##tbl1 includes the data set that I want to pivot:

I used the following query: (the three dots represent the rest of the columns to be pivoted)
select * from ##tbl1

pivot (sum(total_sales)     for s in ([50001 Sales],[50002 Sales],...)) as pv_tb
pivot (sum(Margin)          for m in ([50001 margin],[50002 margin],...)) as pv_tb1
pivot (sum(total_profit)    for p in ([50001 profit],[50002 profit],...)) as pv_tb2
pivot (sum(total_cost)      for c in ([50001 cost],[50002 cost],...)) as pv_tb3

I also use dynamic SQL to pass the column names inside the pivot without having to type each one of them individually, but for the sake of troubleshooting I just showed you how the query would look.
I can't get around using CROSS APPLY and UNPIVOT with this 132 column result, it's very hard to maintain.
Can anyone help me find a way is there anyway easier than this?


texts: Slow access to table in postgresql despite vacuum

texts:
I'm running PG 8.4

I had a table with about 20K records. Any query accessing this table with more than 1 record (e.g. joins) would be very very slow. Even a count would take like 20 seconds.

Stats showed 5 million live_tuples.

I tried vacuum, vacuum analyze, and in the end, vacuum full. Nothing changes in terms of speed. And stats still showed the same thing.

I ended up creating a new table, insert all records in the new table and drop/rename the old one.

The new table now runs lightning fast.

Any idea of:

1) What would have been the more correct handling? I'm looking for a better way to do this as one of the side effect is that the views which were pointing to the table got modified by the "alter table rename to" statement and unbeknownst to me, the views were pointing to the old table for 2 days!

2) Why would that happen in the first place?


texts: How to update conflict resolver when upgrading from SQL-Server 2005 to SQL-Server 2008

texts:
We have recently upgraded from SQL Server 2005 to SQL Server 2008 (R2, SP1). This upgrade included some publications, where all tables are published with a default conflict resolver based on the "later wins" principle.

As you all know, once a table is published with a conflict resolver, the same conflict resolver must be used in all later publications using this table. Fair enough, but, when adding previously published tables to new publications, and specifying the very same conflict resolver to be used for this table, , we are getting an error message:

use [myDb]
exec sp_addmergearticle
    @publication = N'myDb_Pub',
    @article = N'Tbl_blablabla',
    @source_owner = N'dbo',
    @source_object = N'Tbl_blablabla',
    @type = N'table',
    @description = N'',
    @creation_script = N'',
    @pre_creation_cmd = N'drop',
    @schema_option = 0x000000000C034FD1,
    @identityrangemanagementoption = N'none',
    @destination_owner = N'dbo',
    @force_reinit_subscription = 1,
    @column_tracking = N'false',
    @article_resolver = N'Microsoft SQL Server DATETIME (Later Wins) Conflict Resolver',
    @subset_filterclause = N'',
    @resolver_info = N'ddmaj',
    @vertical_partition = N'false',
    @verify_resolver_signature = 0,
    @allow_interactive_resolver = N'false',
    @fast_multicol_updateproc = N'true',
    @check_permissions = 0,
    @subscriber_upload_options = 0,
    @delete_tracking = N'true',
    @compensate_for_errors = N'false',
    @stream_blob_columns = N'false',
    @partition_options = 0
GO

And this is the error we get:

The article '...' already exists in another publication with a different article resolver.

By trying to understand how the same conflict resolver is not considered by the machine as 'the same conflict resolver', I discovered that there were two conflict resolvers with the same name, different versions, in the registry:

the 2005 version:

file ssrmax.dll,
version 2005.90.4035.0,
cls_id D604B4B5-686B-4304-9613-C4F82B527B10

the 2008 version:

file ssrmax.dll,
version 2009.100.2500.0,
cls_id 77209412-47CF-49AF-A347-DCF7EE481277

And I checked that our 2008 server is considering the second one as the 'available custom resolver' (I got this by running sp_enumcustomresolvers). The problem is that both references are available in the registry, so I guess that old publications do refer to the 2005 version, while new publications try to refere to the 2008 version, which is indeed different from the previous one.

So the question is: how can I have the server consider only one of these 2 versions, and this (of course) without having to drop and recreate the existing publications (which would turn our life into hell for the next 2 weeks).


texts: MySQL - UUID/created_at cursor based pagination?

texts:
For a large dataset, paginating with an OFFSET is known to be slow and not the best way to paginate. A much better way to paginate is with a cursor, which is just a unique identifier on the row so we know where to continue paginating from where we last left off from the last cursor position.

When it comes to a cursor where it is an auto incrementing id value, it's fairly easily to implement:

SELECT * FROM users
WHERE id <= %cursor // cursor is the auto incrementing id, ex. 100000
ORDER BY id DESC
LIMIT %limit

What we're not certain about, is if instead of an auto incrementing id cursor, the only unique sequential identifiers for the cursor are uuid and created_at on the table rows.

We can certainly query based on the uuid to get the created_at, and then select all users that are <= created_at but the issue is what if there are multiple instances of the same created_at timestamp in the users table? Any idea how to query the userstable based on uuid/created_at cursor combination to ensure we get the correct datasets (just as if we were using auto incrementing id)? Again, the only unique field is uuid since created_at may be duplicate, but their combination would be unique per row.


texts: Unexpected Table Scan with Parameterized LIKE

texts:
I'm experiencing an unexpected table scan on SQL Server 2005 against a heap table when parameterizing a LIKE statement... but when the same value as the variable is hard-coded, the expected Index Seek occurs instead.

The issue only happens given this specific scenario... so I'm not confused as to how to resolve the issue, I'm confused as to why this is happening.

The following T-SQL should recreate the issue on SQL Server 2005:

IF (OBJECT_ID('tempdb.dbo.#tblTest') IS NOT NULL)
    DROP TABLE dbo.#tblTest
GO

CREATE TABLE dbo.#tblTest (
    ID INT IDENTITY(1, 1),
    SerialNumber VARCHAR(50)
)
GO

-- Populate the table with 10,000 rows
SET NOCOUNT ON
DECLARE @i INT
SET @i = 0

WHILE @i < 10000
BEGIN
    INSERT INTO dbo.#tblTest VALUES(CAST(@i AS VARCHAR(10)))
    SET @i = @i + 1
END
GO

-- To recreate the issue, the table must be a heap.
ALTER TABLE dbo.#tblTest ADD CONSTRAINT PK_tblTest PRIMARY KEY NONCLUSTERED (ID)
GO

-- Create a (non-covering) index on serial number.
CREATE NONCLUSTERED INDEX IX_tblTest_SerialNumber ON dbo.#tblTest (SerialNumber)
GO

DECLARE @Criteria VARCHAR(50)
SET @Criteria = '1234%'

-- This produces a Table Scan.
SELECT *
FROM dbo.#tblTest
WHERE SerialNumber LIKE @Criteria

-- This produces an Index Seek
SELECT *
FROM dbo.#tblTest
WHERE SerialNumber LIKE '1234%'

I was directed towards this article by Paul White which seems very closely related, but the conclusions / explanations don't match my specific issue.

Any insight is appreciated.


texts: How do I choose primary keys for star schema BI data warehouse?

texts:
I am extracting sales turnover data from a Microsoft Dynamics Database into another SQL database that will be used by the BI tool of choice.

This data is coming from G/L (general ledger) entry items.
Each entry contains information for the amount, date, branch, and sales division.

In the DW, I intend to use a star-schema, where the sales figure per day is given in dollars. (fact).

DaySales_Fact

DaySalesId
Sales (Fact)
Date
BranchCode
SalesDiv

Branch_Dim

BranchCode
BranchName

Assume the same thing for Date and SalesDiv tables.

The source data is unreliable and historical data may change at any time. The data warehouse is emptied each day for this reason, and restocked with the live data. This process is scheduled to run once daily.

The source data uses a varchar(2) to store the BranchCode as a primary key.

Would it be best practice to use the business branch code, or create an auto incrementing primary key (see below).

All of the branches are refreshed daily too.

DaySales_Fact

DaySalesId
Sales (Fact)
DateId (fk)
BranchId (fk)
SalesDivId (fk)

Branch_Dim
    BranchId - auto inc PK
    BranchCode
    BranchName


texts:
Problem: How can I run Distributed Transaction Coordinator (DTC) on all servers in an AlwaysOn Availability Group (AG)? I do NOT need to maintain transactions over failover/switchover events.

Setup: I have a Windows Failover Cluster (WSFC) with three Windows 2008 R2 servers where they are all running SQL 2012. Two servers are in one data center and are part of an AlwaysOn Failover Cluster (FCI), while the third server is in a second data center. The WSFC is a multi-subnet cluster. Here's a sketch of the setup:

I've been able to install and configure DTC to work between the two FCI nodes because they are on the same subnet and share storage. I have configured a couple of AGs and they have been working fine. This screenshot shows DTC installed on the FCI:

This screenshot shows that I can configure DTC on one of the FCI nodes (whichever is active):

I'd like to migrate an application that uses DTC onto this cluster and use an AG. I've read that DTC is not supported with AGs (Reference). I have not been able to find a way to configure DTC on the third node in the second data center. When I try to configure DTC on the third node, it seems to be unavailable, as shown in this screenshot:

In Brent Ozar's Free Setup Checklist PDF for Availability Groups he lists:

Cluster Installation...

29. If an FCI is involved, configure DTC per your Planning section decisions.

In the comments on SQL Server 2012 AlwaysOn Availability Groups Rock Brent says that "... nothing changes when AGs are in play. Just keep in mind that databases in an Availability Group don’t support transactional consistency when failed over together to another replica..."

This makes it seem that DTC can be used in Availability Groups as long as you understand that transactions will not be maintained in a AG switchover. I would not need it to maintain transactions from the FCI nodes. I would just need DTC available for the application to use in the case of a catastrophic disaster (where I lost my primary data center).

How do I configure DTC on my third node? Or, is it the case that I'm just out of luck when it comes to using AGs and an application that needs DTC?

UPDATE: The solution I've settled on is to use Log Shipping. However, in the case of a fail over, I still need DTC to be available on Node3. I've discovered that it becomes available by uninstalling the clustered MSDTC-MSSQLSERVERCLU instance of DTC that is shared between Node1 and Node2. Once removed, I can setup and configure a LocalDTC instance on Node3. Afterwards, I can reinstall the clustered MSDTC-MSSQLSERVERCLU instance. Doing the install sequence in that order seems to work. I've been running like that for a little while now and I have not discovered any ill effects. It seems like this would also work for running an AlwaysOn Availability Group. I understand that distributed transactions would not be preserved in an AG failover, I would just need new ones to work after the failover. But I haven't gone there...


texts: Installing SQL Server 2014 Enterprise in Windows 7 Ultimate 64 Bit Incompatibility Error

texts:
This error occurs when I run the setup.exe of SQL 2014.

It tried changing the compatibility mode of the setup from Windows 95 through Windows Vista (Service Pack 2) and run the setup as Admin but same error occurs.

I also installed .Net Framework 4.0 and 4.5. Under the Programs And Features > "Turn Windows features On and Off" > I enabled the "Microsoft .NET Framework 3.5.1" but I found a guide that there should be .Net Framework 2.0 and 3.0 under the Microsoft .NET folder but those 2 versions are missing in my pc.

https://answers.microsoft.com/en-us/newmsn/forum/newmsn_install-newmsn_updateapp/i-cant-install-sql-server-2014-on-windows-10/6d311401-796f-4b3f-b5be-d310cfa6454c?auth=1

When I tried installing those two versions I get the following error
"You must use Turn Windows features On and Off in the Control Panel to install or configure Microsoft .NET Framework 2.0 SP2"

same as the SP1 version of .Net Framework 3.0


texts: linux mysql case insensitive lower_case_table_names=1 make server cannot be started

texts:
I use mysql in linux ubuntu an I want to set lower_case_table_names = 1 in /etc/mysql/my.cnf file. But after I inserted the variable, the mysql server cannot be started.

o

Is there any way to set lower_case_table_names in my.cnf file or an other way to set it?

I have done this before, but it was in centos and mysql server was manual started.

Thank you.


texts: What is the default sample size of statistics in SQL Server?

texts:
From MSDN:

  When none of the sample options (SAMPLE, FULLSCAN, RESAMPLE) are
  specified, the query optimiser samples the data and computes the
  sample size by default.

How to identify default sample size of statistics?

I went through MSDN but didn't find any formula or method to identify the default sample size. Everywhere there are only formulas present to trigger the auto statistics update. Any pointers will be helpful.


texts: How to use SSIS , server side, without SSDT?

texts:
Is it required to use SSDT or Visual Studio to create packages for server side SSIS?

I thought there was another way, but the docs I find keep referring to the above tools.

(When I use the SQL Server "Import & Export Data" tool, it offers me the option to save the profile to a server, but I don't know where it goes?)


texts:
Is there any way in psql (Greenplum flavour) of exiting a script?

I have tried with \q and \quit, e.g.

\echo "Hello";
\q -- or \quit

but I get:

my_database> \i my_script.sql
psql:/home/josh/my_script.sql:2: invalid command \quit;

(and a similar result for \q)


texts: TIME Function changes between 5.5 and 5.6

texts:
I'm facing an issue, where I can't get the same result with the TIME function when migrating from MySQL 5.5.46 to MySQL 5.6.34.
The thing is, when I use the TIME function in 5.5, it returns NULL when datetime is '0000-00-00 00:00:00', but only if it's that value comming from the table, but with 5.6 I'm getting '00:00:00'. It doesn't sound bad, but there's many queries that are relying in the behavior from 5.5 (Result is NULL).
Here's an example I did to test exactly that behavior:

First, the table:

CREATE TABLE `prueba_time` (
  `check_in` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Then I insert one row with value 0000-00-00 00:00:00

mysql> INSERT INTO prueba_time VALUES ('0000-00-00 00:00:00');

And then I test the TIME function... The result in 5.5 is:

mysql> SELECT TIME(check_in),check_in,TIME('0000-00-00 00:00:00') FROM prueba_time;
+----------------+---------------------+-----------------------------+
| TIME(check_in) | check_in            | TIME('0000-00-00 00:00:00') |
+----------------+---------------------+-----------------------------+
| NULL           | 0000-00-00 00:00:00 | 00:00:00                    |
+----------------+---------------------+-----------------------------+

And the same query in 5.6:

mysql> SELECT TIME(check_in),check_in,TIME('0000-00-00 00:00:00') FROM prueba_time;
+----------------+---------------------+-----------------------------+
| TIME(check_in) | check_in            | TIME('0000-00-00 00:00:00') |
+----------------+---------------------+-----------------------------+
| 00:00:00       | 0000-00-00 00:00:00 | 00:00:00                    |
+----------------+---------------------+-----------------------------+

So... different result and I can't find out why...

The sql_mode it's different. In 5.5 it's empty, and in 5.6 I'm using STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION but according to what I've read there's no affecting in this. I hope you can help me with this.


texts: Is it possible to Clone an Existing MYSQL Slave Database into Amazon RDS with the RDS clone being a slave to an external Master?

texts:
We are interested in migrating our data from one of our MYSQL slave databases to an Amazon RDS instance such that the new RDS database is a slave database to an external master DB from our colocation/facility.

I was reading through the documentation for RDS and was not keen on bringing our master mysql server down/stop writes while the import into RDS is taking place.  I would prefer doing that import processing on an existing slave database.

We use some of the existing Percona tools to create slaves from existing slaves but I'm sure there are limitations on RDS so any helpful advise is appreciated.

Master MYSQL Version: 5.6.11-log MySQL Community Server (GPL)
Slave MYSQL Version:  5.6.11-log MySQL Community Server (GPL)


texts:
I'm sorry if it's a very basic question, but would really appreciate educated advice on this:

Say I have an operation, which first has to read from a table and then insert a row if the read value satisfies a certain condition, and throw if it doesn't, e.g. I have a following table:

id     | value
---------------
serial | integer

And before inserting a row I need to first:

select sum(value) from table;

then if sum + value of the row, which I wanna insert is less than 100 - insert, if no - throw.

I need to somehow make those reads be processed only one-by-one, because if I have 2 concurrent queries, both would read, get that the sum is less than 100 and both insert, which would result in an undesired state, which would have been avoided if queries were executed sequentially.

What would be optimal solution to ensure one-by-one processing here?

So far I've tried doing select for update to lock concurrent read operations, but I have a feeling it might be not optimal here...

I'm also aware I could do that with a before insert trigger but the nature of this logic seems not to fit there because the threshold (100 in the example) comes from application layer runtime and is actually different every time.

I've read a bit about transaction modes (e.g. serializable) but not sure if that's what I need here...


texts: Cannot Connect Using SSMS to Local SQL Server 2008 R2

texts:
About six months ago my vendor installed SQL Server 2008 R2 into a clustered pair of servers, DB01 and DB02.
I've been logging on, periodically, to DB01 to audit some of the data contained in the database instance.
I've been using SSMS the whole time, and for six months there have been no problems.
Suddenly yesterday, I am unable to connect using SSMS.
I get the following error:

Cannot connect to DB01.
Additional information:
A network-related or instance-specific error occurred while establishing a connection to SQL Server.  The server was not found or was not accessible.  Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)(Microsoft SQL Server, Error: 53.)

This is very confusing, very little has changed since the last time I've logged in to SSMS.
TCP/IP is enabled on SQL Server, as are named pipes.
One thing I know has changed is that for the CLIENT machines that hit it, the TcpTimedWaitDelay parameter has been set to 30, and the MaxUserPort has been set to 10000.
But that's on the client machines only.
Could there be an issue arising from from these parameters not being mirrored on the database servers?
Could there be an issue arising from these two boxes sharing an IP for the clustering?  The database is normally accessed through a shared IP, where DB02 takes the IP if DB01 fails.
I've considered restarting the SQL Server service but that scares the bejeezus out of me.
What if this issue spreads to the application currently using the databases?


texts:
I would like to count the number of null's present per row in a table without enumerating column names. For example:

WITH t as (VALUES
  (NULL ,'hi',2,NULL,'null'),
  ('' ,'hi',2,3,'test'),
  (NULL ,'hi',2,3,'null')
)
SELECT countnulls(t)
FROM t;

Would result in:

numnulls
2
0
1

The closest I can get is with the following hack of row_to_json():

select
(CHAR_LENGTH(row_to_json(t)::text)
 - CHAR_LENGTH(REPLACE(row_to_json(t)::text, 'null', '')))/4 from t;

Which is... quite the hack (not in a good way). It works, sort of, but it counts the string 'null' as a NULL when it is present in the actual data or in the column names. So it is incorrect in the above case.


texts: SQL Server Agent not observing "execute as" permissions

texts:
I have a curious permissions problem.  Something recently happened that caused existing SQL Server Agent Jobs that were configured to run as a particular User to stop working.  They previously worked; they are now throwing basic permissions errors (e.g., SELECT permission denied).

Here's a specific test case.  The user johndoe has the server-level sysadmin role. The following query works fine when run by johndoe in SQL Server Management Studio:

select * from TableA into TableB

But when I put that exact same query into a single-step SQL Server Agent Job, with that step configured to run as user johndoe, I get the following error.

  Executed as user: johndoe. The SELECT permission was denied on the
  object 'TableA', database 'MyDatabase', schema 'dbo'. [SQLSTATE 42000]
  (Error 229).  The step failed.

Any suggestions on what might have caused this seemingly spontaneous change?


texts: Strategies for reducing data to summaries within a table over time?

texts:
I have a large data set where large numbers of entries differ only by timestamp.  In the short term it's beneficial to maintain individual entries so that we can correlate by time for individual data points.  But after enough time passes, that level of granularity is unnecessary and I'm comfortable replacing those individual entries with a summarized entry by means of a count column.

So if the table has entries this when it's fresh:

+---------------------+------+-------+
| stamp               | data | count |
+---------------------+------+-------+
| 2016/01/01:12:54:00 | abcd | 1     |
| 2016/01/01:12:56:58 | abcd | 1     |
| 2016/01/01:13:02:36 | abcd | 1     |
+---------------------+------+-------+

I'm happy to combine all the entries of a given hour after, say, a week:

+---------------------+------+-------+
| stamp               | data | count |
+---------------------+------+-------+
| 2016/01/01:12:00:00 | abcd | 2     |
| 2016/01/01:13:00:00 | abcd | 1     |
+---------------------+------+-------+

And maybe after a month I only care about the granularity of a day:

+---------------------+------+-------+
| stamp               | data | count |
+---------------------+------+-------+
| 2016/01/01:00:00:00 | abcd | 3     |
+---------------------+------+-------+

This isn't hard to do in SQL (PostgreSQL shown here):

select distinct(date_trunc('hour', stamp),data,sum(count)
into table tmpdata
from data
where stamp < now() - interval '7 days'
group by date_trunc('hour', stamp),data;

...and then clear the rows I just summarized out of data and replace them with the summaries I created in tmpdata...  It seems preferable to keep the data within a single table rather than have separate tables for different granularity of data.

This all works, but it's awkward, and I'd like to learn a better way if there is one.  So I have two questions:

What is the name for this sort of summary-replacement-of-data-over-time?  I would say the data is being coalesced, but COALESCE has a distinct other meaning in SQL.  It would help to know the terminology used to describe this sort of thing so I can tag it and talk about it properly.
What strategies / algorithms / methods of achieving this goal would you recommend, with an eye towards elegance?


texts:
How to remove existing content before entering a replicaset the first time?

mongodb worries about:

  ... has data already, cannot initiate set.  All members except initiator must be empty.


texts: Why do I get leading zeros when converting from decimal to char in a db2 query?

texts:
I have a column named length defined as:

TYPE_NAME: DECIMAL
DATA_TYPE: 3
COLUMN_SIZE: 9
BUFFER_LENGTH: 11
DECIMAL_DIGITS: 5
NUM_PREC_RADIX: 10

When I run the following select:

select char(length) st_length, length from atable

I get leading zeros on the st_length column:

st_length   length
0043.00000  43.00000
0043.00000  43.00000
0045.00000  45.00000
0044.00000  44.00000
0044.00000  44.00000
0046.00000  46.00000

Why am I getting the leading zeros and how do I get rid of them?

Edit: I meant to say this earlier but forgot. I'm using db2 v9.5.8 LUW.

Resolution: My original goal was to get the left most character of the length. I ended up with this:

select left(char(cast(length as int)),1) st_length, length from atable

which yielded,

st_length  length
4          43.00000
4          43.00000
4          45.00000
4          44.00000
4          44.00000
4          46.00000

Improvements are welcome.


texts: Is there any way to get the estimate cost of executing a query in MySQL?

texts: In PostgreSQL, EXPLAIN or EXPLAIN ANALYZE will show the estimate cost of executing a query. But EXPLAIN in MySQL doesn't provide this information. How can I get the estimate cost without installation of other tools? I'm using MySQL-5.6.16.


texts:
deliveries Table has customer_id and the item_id they want to buy. items table has items with item_id as primary as below

 deliveries                                 items
id  customer_id  item_id               item_id       item_name
1    10           100                     100        Delicious
2    10           111                     111        Super Delicious
3    12           100

I want to see for every customer (10, 12) whether they bought the two items (Delicious and Super Delicious).

This I tried:

SELECT * FROM items LEFT JOIN deliveries ON items.item_id = deliveries.item_id

Here specifically, I want to see customer with id 12 has not bought item_id 111 as Null, which would be 4th row in the result.


texts:
I have installed MySQL 5.5.31 in Opensolaris. When I tried starting the MySQL server, it says
 bash: mysql: command not found
So, I decided to uninstall the whole lot and try doing again.
But I could not find any spporting documents online to do so, because either they refer to .rpm file unistallation pkgrm.
I used .tar extension and pkgrm is not supported in my machine.


texts: remove duplicate rows in mysql table that does not contain primary key

texts:
I have a table item having just one column name:-

name
-----
toys
shirt
mobile
Shirt
speaker
Toys
....
....

I am trying to delete duplicate rows except one duplicated record with the help of below query

DELETE FROM
(
SELECT `name`,`rownumber` FROM (SELECT GREATEST(0,@num := IF(`name` = @NAME,
@num + 1, 1),LEAST(1, LENGTH(@NAME := `name`))) AS rownumber,`name` FROM
item ORDER BY `name`) AS result1
) AS result
WHERE rownumber >1

The Inner query (Starts from SELECT name and ends with result1) returns individual auto incremented number for each repeating group but when i run entire query then it does not work.

I would like to know what changes i need to make in the inner query (contained in round braces) to delete duplicate rows.


texts:
I am working with a system that pulls data using API requests, stores it and pushes data using some other API requests.

We are using a table to store unprocessed data that is being fetched through API requests using InnoDB and the size is around 40 GB for the single table.

  And by the way, the system I am talking about was made some time ago
  with Joomla 3.7.3.

The server we are using is

CentOS 7
CPU count               8
Total memory            16380312 kB
Total swap              4194300 kB

Some of the alerts I am getting are

Aborted connects            1.2 k
Created tmp disk tables     230.1 k
Handler read rnd            42.7 M
Handler read rnd next       37.5 G
Innodb buffer pool reads    2.6 G
Innodb row lock time max    843
Innodb row lock waits       34.2 k
Opened tables               742
Select full join            6.3 k
Select range check          78.3 k
Slow queries                9
Sort merge passes           1
Table locks waited          2.5 k

We tried adding more CPUs to the machine but was of no use. Because the system uses Joomla and we have a lot of changes to the core, we are out of ideas about things we can do to improve anything, as making major changes in the system might break things.

Please provide some suggestions. Any type of input is appreciated.
Thanks in advance.


texts: Update and select concurrently running on same set of rows but different indexes are referred?

texts:
In multithreaded application where update and select can go simultaneously assuming update is used within transaction. If table has non clustered index and clustered index, what are the chances of having deadlock in such situation?
clustered index is used by update statement for example Cluster Index Update <- Clustered index seek
whereas non clustered index used by select statement for example select <- nonclustered index seek
What happens when one thread updates and other is using select assuming same rowids referred(where clause returns same rows)?
Are there any chances of having deadlock? how such situations can be handled if application allows update and select to run concurrently?

What are the chances of occurring index row level locking deadlock?


texts: Select query to retrieve rows with null values

texts:
I need to retrieve data from a table even if one of the fields has null value. Here's an example:

Select name, SUM(credit) as credit
From expenses
Where name like 'vendor0%'
and date like '2013%'
Group by name
Order by name asc

This example retrieves name and SUM(credit) only when credit has values. I need to retrieve all from name even if credit has no value at all.

Is this possible?


texts: PostgreSQL Linux PAM Authentication Error

texts:
I'm using PSQL version 12.5 on Ubuntu 20.04.1. My pg_hba.conf file looks like this:
# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# PAM Authentication
host    all             all             0.0.0.0/0               pam
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

I create a USER\Role with:
CREATE USER test WITH LOGIN;

Where test is a Linux user and is working fine. But when I run:
psql -U test

I get the error:
postgres@ubuntu2004:~$ psql -U test
psql: error: FATAL:  Peer authentication failed for user "test"

Am I missing something?


texts:
I have a working query that show success rate of staff.  When I run it in for all staff it works flawlessly.
However, when I insert it as a function within a java based page I get an error:

ERROR: division by zero

Here is the query and what I think the actual problem is:

SELECT st.staff_id,
  round((count(s.code IN ('10401','10402','10403') OR NULL) * 100.0) / count(*), 1) AS successes
  FROM   notes n
  JOIN   services s  ON s.zzud_service = n.zrud_service
  WHERE n.zrud_staff = ? AND n.date_service >= DATE '07/01/2013'
    AND n.date_service <= CURRENT_DATE
    AND  s.code IN ('10401','10402','10403','10405')
  GROUP  BY st.staff_id

The 10401, 10402, 10403 codes reflect successful discharges.
The 10405 reflect unsuccessful discharges.

If a staff has no notes (new staff for example) I get the division by zero error.

So I believe if a staff does not have at least one entry for one of the codes, it will error.  This prevents them from logging in.

I read of an operator called NULLIF() that would prevent this, but I do not have any idea how to insert it, and, if this will actually work.

Can anyone comment?


texts: could not receive data from WAL stream: ERROR: requested WAL segment XXXXXX has already been removed

texts:
I did a vacuum on the master server today and the read / slave fell behind.

When I check, that WAL file is in the pg_xlog directory on the slave and in the wal_archive directory which I created on the master.

Why is Postgres not "seeing" that WAL file.

(I saw an answer on Postgres requested WAL segment has already been removed (however it is actually in the slave's directory) however I'm not keen to copy the file into the pg_xlog of the master in case it causes an issue on the master.)


texts: How do i copy my mongodb data-files to mongodb server

texts:
I actually copied above two files in MongoDB's data folder which is default in windows machine (C:\data\db ). But when I start mongo shell and type show dbs, it will not show my database.

Steps I followed is as below.

1. copy the data files to C:\data\db
2. start the MongoDB server by mongod --dbpath C:\data\db
3. start the MongoDB shell by mongo command in command prompt
4. type show DB's command to list the databases

But I am not able to see my databases. Any help would be appreciated.

Thanks, Krishna


texts: Why does MySQL ignore the index even on force for this order by?


texts: Installed SQL Server 2000 with wrong collation.. how to correct for entire server instance?

texts:
I installed SQL 2000 Developer (yes, old, don't ask) on my laptop ... with the wrong collation.  Oops!

It's showing Latin1_General_CI_AS, but it turns out I need to use SQL_Latin1_General_CP1_CI_AS to match another instance we're working with.

I found some references with Google on how to change the collation for a specific database, but I'd like to change it for the entire server instance.  Is there a way to fix this without having to re-install SQL Server 2000? It's a pain with the service pack, security fixes, etc.

Thanks


texts:
We have a routine DBCC CHECKDB running at night when the database is still accessible but with very little traffic.  Since last month we had several occasions of DBCC CHECKDB crashing to this error:

  DBCC CHECKDB (Database1) WITH no_infomsgs executed by sqladmin terminated abnormally due to error state 5. Elapsed time: 0 hours 47 minutes 9 seconds.

This is preceded by several SQL Alert Severity 17, which is insufficient resources, as well as output from DBCC MEMORYSTATUS in the SQL server log.  Thus I believe DBCC CHECKDB crash due to insufficient memory.

Running DBCC CHECKDB again would return no error.  A non DBA even did it once during business hour, while it drag down the performance and it took nearly 3 hours to complete, it did not result in a memory problem.  (He was told not to do it again).

The server itself has 12GB of RAM, but there is no minimum and maximum limit set for SQ server.  SQL Server itself use about 10GB of memory, while all other processes use 1GB.  I am not aware of anything else taxing the server at that time.

edit:

there is no min or max memory limit set, original question is missing the key word "no"
there is enough room for temp DB to grow
there was no disk space alert on any of the volume, so I assume disk space is not an issue
database is about 50GB in size
I am not aware of any system changes other than Windows Update
Normally CHECKDB would take 60-90 minutes to complete at night


texts: What is the difference between Aurora Mysql 5.7.x and Aurora 2.x.x

texts:
I have an RDS instance with Aurora Mysql 5.7.12 and I noticed that there are other versions of Aurora Mysql 5.7, but I don't understand what is the difference between them.

Some of the version listed are:

Aurora (MySQL)-5.7.12   <- The one that I have.
Aurora (MySQL 5.7)-2.03.2
Aurora (MySQL 5.7)-2.03.3
Aurora (MySQL 5.7)-2.03.4
Aurora (MySQL 5.7)-2.04.1
Etc...

I attach an image of the AWS Console:

I want to chose the right version (the one that have most bug fixes in order to have better stability in my system).


texts: Sync data from tables in two SQL Servers

texts:
In a following app arhicecture, local C# desktop app with SQL Server Express, which need to work offline sometimes and a central web app with SQL Server Enterprise, with db per local app.

I need to sync those two databases semi-automatically, but with some controls (and info) from an local desktop and web apps. For example, I want for user to see when sync start, when it ended, etc. Also, if synced ok or there was an error. In addition, I don't need direct replication between two databases, but I need to copy some rows which belong to the client in the db table from and to the server.

What is the best solution for this problem ?


texts: AWS RDS Showing CPU spikes on a MySQL system every 5 minutes -- how can I track the cause?

texts:
I'm seeing a CPU spike every 5 minutes (though it's not FIXED on the 5 minutes, every so often it's 6 minutes between a spike, then 5, 5, 5 again).  None of my code runs on a 5 minute interval (it either runs statements EVERY minute, or every few HOURS).

This is what the graph looks like:

Is there any way I can analyze what is causing the CPU to spike like this?  Seeing a list of queries is good, but seeing their CPU impact is more what I'm after; a direct "A is causing B" way to dig down.

Does it exist?


texts: Import all database links using dbms_datapump

texts:
Source DB version:      11.2.0.3
  Destination DB version: 12.1.0.2

I'm attempting to import all database links from one database to another using dbms_datapump.  The code is running on the above destination DB, and pulling the DB Links from the above Source DB via a Database Link.

I've used dbms_datapump.metadata_filter in the past to only import certain objects (like functions or procedures).  However, the following returns ORA-39001: invalid argument value:

dbms_datapump.metadata_filter(h1,'INCLUDE_PATH_EXPR','IN (''DATABASE LINK'')');

Therefore, I tried the plural version which also returned ORA-39001:

dbms_datapump.metadata_filter(h1,'INCLUDE_PATH_EXPR','IN (''DATABASE LINKS'')');

I know I must be missing something basic here, but searching on this issue has been annoying since search results with "dbms_datapump" and "database link" in the same search has only returned results with how to import over a Database link, and not how to import the Database Links directly.

I used to be able to import the Database Links directly using:

select DBMS_LOB.SUBSTR@dblinkimp(DBMS_METADATA.GET_DDL@dblinkimp
('DB_LINK', '[DBlinkName]', '[SchemaName]')) from dual@dblinkimp;

Then I would run the output from the above statement using execute immediate, but this is no longer a valid option starting in 11.2.0.4 (See Metalink Doc ID 1905221.1).


texts: Backup data only through command line in SQL Server

texts:
I must create a post deploy script based on data from a previous sql server database. I'm making a data-only backup, but it must be done table by table due to avoid the cyclic dependency from the forward keys.
For each table, I go to SQL Management Studio, go to task, generate script, configure the backup and generates data only backup. But this proccess done table by table takes much time and due to have to reconfigure each one of those times and is a quite boring process, it is also very sensitive to human error.

I would like to know if there's a way to create a script to make it. Can be some command in tsql, sqlcmd or powershell. Google just gives me the full backup for line command or some another process involving some kind of "windowed wizard" in case of backuping just one table, and it does not satisfy my restrictions.


texts: Booleans, CONSTANTS or mapping table for 'status'-like fields?

texts:
I am modelling a User table which needs to have the following information about the users:

is_active?
is_detained?
has_voluntarily_deactivated?
is_temporarily_suspended?

and so on...

Basically, these are boolean flags with true or false. So, I am considering few approaches other than boolean flags which are as follows:

Create a single varchar field with values like 'active', 'detained', 'deactivated', 'suspended', etc.
Create a tinyint field and map the integers to another table containing status strings
Create a tinyint field and map the integers in code itself using constants, such as ACTIVE = 1, DETAINED = 2, etc. Is Python's enum type the best solution to this?
Create a tinyint field and map the integers to status strings in an XML or JSON file

Which of the above 4 or the original boolean style approach is preferable, or if there could be a completely different approach or a modified version of the above approaches, please let me know?

Also, in my code, how should I call these fields, like:

if (user.status == 1), or something like
if (user.status == STATUS.ACTIVE), or
if (user.status == 'active')

(I think this will depend on which approach I follow)

These status values are not limited and may be added, edited or removed in future. Request you to answer in a database agnostic way and the programming language that I am using is Python.

Thank you for your answers


texts: Stored procedure failing on linked server table, despite it working in select query

texts:
I've got a stored procedure on my server that extracts data from another server.

It's failing on a particular table each time saying "Msg 7314, Level 16, State 1, Line 1" "The table either does not exist or the current user does not have permissions on that table."

I can both see the table in SSMS, and run a select statement on the table from my server.

Can anyone point me in the direction of what might be wrong with this particular table?


texts: Deleting aged postgresql rows with tricky condition

texts:
I have two postgresql tables I need to clean up as part of maintenance. The tables, as described in my Django front-end, are:

class Group(models.Model):
    owner = models.ForeignKey(User)
    unique = models.CharField(max_length=36, unique=True)
    created_at = models.DateTimeField(auto_now_add=True)

class Reply(models.Model):
    text = models.TextField(validators=[MaxLengthValidator(500)])
    which_group = models.ForeignKey(Group)
    writer = models.ForeignKey(User)
    submitted_on = models.DateTimeField(db_index=True, auto_now_add=True)

I essentially want to delete all groups where the most recent reply was submitted more than 14 days ago. What will be the correct sql queries to accomplish that? I'm thinking of:

DELETE FROM links_group WHERE id IN (SELECT which_group_id FROM links_reply WHERE "submitted_on" < now() - interval '14 days');

But this is missing the logic of most recent reply. How do go about with this?

Relevant portions of \d+ links_group; are:

                                                          Table "public.links_group"
     Column     |           Type           |                        Modifiers                         | Storage  | Stats target | Description
----------------+--------------------------+----------------------------------------------------------+----------+--------------+-------------
 id             | integer                  | not null default nextval('links_group_id_seq'::regclass) | plain    |              |
 owner_id       | integer                  | not null                                                 | plain    |              |
 unique         | character varying(36)    | not null                                                 | extended |              |
 created_at     | timestamp with time zone | not null                                                 | plain    |              |
Indexes:
    "links_group_pkey" PRIMARY KEY, btree (id)
    "links_group_unique_key" UNIQUE CONSTRAINT, btree ("unique")
    "links_group_owner_id" btree (owner_id)
    "links_group_unique_like" btree ("unique" varchar_pattern_ops)
Foreign-key constraints:
    "links_group_owner_id_fkey" FOREIGN KEY (owner_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_reply" CONSTRAINT "links_reply_which_group_id_fkey" FOREIGN KEY (which_group_id) REFERENCES links_group(id) DEFERRABLE INITIALLY DEFERRED
Has OIDs: no

Relevant portions of \d+ links_reply; are:

                                                          Table "public.links_reply"
     Column     |           Type           |                        Modifiers                         | Storage  | Stats target | Description
----------------+--------------------------+----------------------------------------------------------+----------+--------------+-------------
 id             | integer                  | not null default nextval('links_reply_id_seq'::regclass) | plain    |              |
 text           | text                     | not null                                                 | extended |              |
 which_group_id | integer                  | not null                                                 | plain    |              |
 writer_id      | integer                  | not null                                                 | plain    |              |
 submitted_on   | timestamp with time zone | not null                                                 | plain    |              |
Indexes:
    "links_reply_pkey" PRIMARY KEY, btree (id)
    "links_reply_submitted_on" btree (submitted_on)
    "links_reply_which_group_id" btree (which_group_id)
    "links_reply_writer_id" btree (writer_id)
Foreign-key constraints:
    "links_reply_which_group_id_fkey" FOREIGN KEY (which_group_id) REFERENCES links_group(id) DEFERRABLE INITIALLY DEFERRED
    "links_reply_writer_id_fkey" FOREIGN KEY (writer_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Has OIDs: no


texts: Does the length of a table name affect performance?

texts:
PostgreSQL table names are limited to 63 chars.

Is there a performance gain to be had if I limit my table names to something like 5 chars or can I use the full 63 chars without consequence?

I understand it will be longer to type long names when creating queries, but that's what aliases are for.


texts: Is there any way to save the execution plan in a file in PostgreSQL?

texts:
By saving a plan in a file, I don't mean to view it, but force the optimizer to use the saved plan to execute some query.

For example, if the optimizer chooses to do a hash join for a two-table query and I manage to force the optimizer to use a nest loop join, is there any way to save the nest loop join plan and let the optimizer use it next time when I execute the same query?


texts: PostgreSQL bug(?) - logging with a minimum log level

texts: I am using the log_min_error_statement - Setting in the PostgreSQL configuration file, but the logger does not react on the setting, either if I turn it on, or off, or set it to another level, the logger logs every statement. Could this be a possible bug in PostgreSQL logging?


texts: Linked Server SS 2012(SP4) to SS 2016(SP1) just stopped working. It times out with the error "Server is not found or not accessible". However I can connect and query the target server successfully with SSMS. How can SSMS work yet Linked Server cannot even "see" the target server?


texts: Cluster of Clusters (Not using MySQL-Cluster)

texts:
I'll start with an apology, I'm an Ops guy at heart, not a DBA, so I may use the wrong terminology, not in an effort to annoy, merely because I don't know any better.

OK, that over with, on to the question.

I've inherited a MySQL Cluster (using CentOS, Corosync, Pacemaker and MySQL Enterprise).  It's a 2-node cluster, and it has a 3rd node (no MySQL, merely Corosync/Pacemaker) as a quorum node.

There are 2 VIP's set up on the cluster, one allocated to each box (Writer and Reader respective to Master/Slave).

This is all working fine, and I'm more (or less) happy with how it's set up and functioning. I should mention it's using GTID replication with full local storage (no SAN or DRBD).

Tacked onto the edge of this, is another node (MySQL, no Corosync/Pacemaker) This node is set up to slave from the Reader VIP that is allocated to the slave in the original cluster.

I've now been tasked with turning this extra backup into a Master/Slave cluster in it's own right, and I'm not sure where to begin with this.  I have managed to get all 4 servers running as 2 clusters (independent to each other) but am failing to understand the Corosync/Pacemaker config I would need in order to make the Master of the second cluster become a Slave of the first cluster (or even if this is possible with the current software choices).

I'm just hoping someone out there has some experience on how to cluster clusters in this fashion and would be willing to point me in the correct direction.

       Cluster 1                                Cluster 2
┌──────────────────────┐                 ┌──────────────────────┐
│  Master       Slave  │                 │  Master       Slave  │
│ ┌─────┐      ┌─────┐ │                 │ ┌─────┐      ┌─────┐ │
│ │     │ Repl │     │ │    Reader VIP   │ │     │ Repl │     │ │
│ │ DB1 ├──────┤ DB2 ├─────────────────────┤ DB3 ├──────┤ DB4 │ │
│ │     │      │     │ │    Replication  │ │     │      │     │ │
│ └─────┘      └─────┘ │                 │ └─────┘      └─────┘ │
└──────────────────────┘                 └──────────────────────┘

I have access to all the config files and log files, but I wasn't sure which ones would count as relevant to this discussion (should it become a discussion).


texts:
my coworker and I had a debate and I am looking for Reddit to resolve it! We have PHP web apps running on IIS and are looking to setup the security to the database using one approach:

-What would you call the method where the service account running the application pool authentication to the SQL Server database using its own credentials?

-What would you call the method where the users connecting to the app on the network authenticate to the apps SQL Server database using their own network credentials?

Which is “integrated authentication”?!


texts:
We're running a full SQL backup each day, then differentials each hour. This has been working on multiple servers for a long time now. I'm not aware of any changes that have been made but, on one server, in particular, the differentials are just getting bigger and bigger, instead of resetting back to a smaller size after a full backup. Normally on other servers, the first differential backup is a few hundred MB, but we're getting a 6GB file every time.

Screenshot of the current backup files attached

The SQL commands being run to perform the backups are:

For full:
BACKUP DATABASE <DBName> TO DISK = <Path> WITH INIT;

For diff:
BACKUP DATABASE <DBName> TO DISK = <Path> WITH DIFFERENTIAL, INIT

Any help would be greatly appreciated.


texts: Oracle Hierarchical query: how to follow max on each level

texts:
I'm trying to get a query to "follow" the max values in each level excluding the max values in other branches.
Here some examples to illustrate what I'm trying to achieve:

Given this hierarchical structure:

    38
      15
        10
        5
          2
          3
      17
        9
        8
      6
    26
      13
      1
      12
    18
      11
      7

I would like to get:

    38
      17
        9

and not:

    38
      17
        10
          3

Like I would get by using the sql query I've come up with until now:

select lpad(' ',2*(level-1))|| max(child)
from test_connect_by
start with parent is null
connect by prior child = parent
group by level

That get the absolute max from each level without excluding the other branches on each iteration over the levels.

The example data comes from http://www.adp-gmbh.ch/ora/sql/connect_by.html

Thanks in advance for any hint
Vlax


texts:
When trying to create a maintenance plan locally on two prod servers through SSMS, I receive this error:

No such error occurs on my UAT system. They're all running SQL Server 2017 (14.0.3162.1), and they were all installed with the same feature set. The UAT machine is Windows 10, while the prod servers are Windows Server 2016.

Which components/features specifically need to be installed such that I can create a maintenance plan?


texts: MYSQL: How to optimize a date sorted table with a triple primary key index

texts:
I have been maintaining a MYSQL database for a few years, dating from back when i took database managing classes. Its biggest table has since grown (15 million entries for 1,8 GB) and this query began to take minutes to load (pkey1 is a DATETIME, pkey2 and pkey3 are int(11)) :

SELECT * FROM table
WHERE pkey2 = ...
  AND pkey3= ...
  AND HOUR(pkey1) = 0
ORDER BY pkey1 DESC

The index of this table is (pkey1,pkey2,pkey3) BTREE, and the column count is 20.

The ORDER BY used to be ASC and it was running quite slowly, I then changed it to DESC (since i will change this request for a pagination, if this cannot be optimized), and migrated from 5.7x to 8 to be able to recreate this table with a DESC index. It's now even slower.

The questions are :

Would changing the DATETIME to a DATE (and thus remove HOUR(0), no longer needed) resolve the problem in a satisfying enough way ? I don't need it anymore, especially if it causes problems.
If not, what can you advise me to do ?

To go further :

Allegedly because of this table, my whole system (weak: 2Gb RAM, Single core, 20GB SSD) is slowed down, and even a simple database connection is delayed by several seconds. If this table is not the case for the slow down (even though the triggering of this request seems to be the problem), here are the alarming variables i got from PHPmyadmin :

As you can see, things are probably wrong.

Thank you for the read !


texts: DBeaver: Access denied for user 'root'@'localhost' (using password: YES)

texts:
Context: I am creating a new Schema and looking to import .sql file generated from mysql dump. I have server running: MySQL 8.0.19.

I am using DBeaver client Version 7.0.2 and have setup a new MySQL 8+ Database connection. However, when I attempt to connect to the DB, I run into the following error:

Access denied for user 'root'@'localhost' (using password: YES)

A root user exist (verified through mysql shell). Here are a few things I have attempted, however, haven't been able to resolve the issue yet:


texts:
My question is about database permissions and rights in Postgres. How can I create reader rights by default when objects are uploaded into PGadmin 4 schemata?

I created the following schema in Postgres called test_schema:

CREATE SCHEMA test_schema
    AUTHORIZATION jessica_admin;

GRANT ALL ON SCHEMA test_schema TO group_members;

ALTER DEFAULT PRIVILEGES IN SCHEMA test_schema
GRANT SELECT ON TABLES TO group_members

CREATE TABLE test_schema.test_table
(
)

WITH (
    OIDS = FALSE
);

ALTER TABLE test_schema.test_table
    OWNER to jessica_admin;
GRANT SELECT ON TABLE test_schema.test_table TO group_members

Now the question is the following:
how can I include the following code

ALTER TABLE test_schema.test_table
    OWNER to jessica_admin;
GRANT SELECT ON TABLE test_schema.test_table TO group_members

into the default settings of PgAdmin4? I don't want to do this setting everytime when I add new shapefiles into the scheme, but I want that user (here called group_members) can read the shapefile in PostgreSQL and add this simply into the GIS Interface.


texts: DSV - SSAS Project - New 2 Columns In Table Change More then I Planned

texts:
I encountered a very weird thing.
I added in my DWH table 2 new columns but when I refreshed the DSV after adding this to my multidimensional project I saw that more (3) columns have been "changed".
I did not touch these 3 columns(Marked in red ) they were already in my database before and have been handled .

Someone have an idea why this happened? Can I know what "changed"?
Also you can see each "changed column appears twice.

Here Is the PrintScreen After I refresh the DSV.
Marked In blue - the columns I added
Marked In red - the Columns I did not touch and was existing in the Table.


texts: Multiple inserts across tables with serial primary keys

texts:
I asked this question on stack overflow, but I'm thinking it's better on this stack. Apologies if I've got that wrong. The motivation for this question is to prepare some sql scripts I can run during unit tests and production to define a bunch of master data for a web-based application that I'm building.

I have three tables, which I've abbreviated into the following

create table top (top_id serial, top_name text);
create table mid (mid_id serial, top_id integer, mid_name text);
create table bot (bot_id serial, mid_id integer, bot_name text);

what I want to achieve is

insert a value into top, and capture it's ID
insert multiple values into mid, using the same top_id,
for each value in mid, insert multiple values into bot, using each mid_id.

So, the final result might look like

top
----
1, first_top
2, second_top

mid
---
1, 1, first_mid_for_first_top
2, 1, second_mid_for_first_top
3, 2, first_mid_for_second_top

bot
---
1, 1, first_bot_for_first_mid
2, 1, second_bot_for_first_mid
3, 3, first_bot_for_third_mid

My first approach was to try to have a bunch of nested CTEs, but I couldn't make that into valid syntax (and on consideration, I think that it's not the right approach).

My second approach was to try to store the values into some sort of variable, but I don't know how to do that outside of the pg command line tool.

What I'm after is something analogous to

top_id_one = insert into top(null, 'first_top') returning top_id
mid_id_one = insert into mid(null, top_id_one, 'first_mid_for_first_top) returning mid_id
insert into bot(null, mid_id_one, 'first_bot_for_first_mid')

in a "pure sql" that I could paste into PG admin for example.

I realise that there's a few problems in the example above (for example using 'top' as a table name). My explorations have avoided these issues, but I was trying to find table names that would make my example make sense.


texts: Linking two fields from different tables (are relationships required?)

texts:
For example, I have two tables.  Employees and Products.

Inside Employees I have the fields: emp_ID, emp_fname, emp_lname, emp_products
Inside Products I have the fields: pro_ID, pro_name, pro_warehouse, pro_employees

Any employee can sell any product.  I want to track which employees sell which products, and which products are sold by which employee.

If I do a lookup wizard in Employees: emp_products and allow multiple selections, and the same thing in Products: pro_employees...then in the Employee: emp_products field I select "Dodge Ram" and "Jeep Rubicon"  I want that employee added to the respective Product record in Products: pro_employees.

Is this even possible, and if so...how do I go about this?  I'm pretty new and I'm having a hard time searching the right words/phrases for this without coming up with relationship support.


texts: How to configure Oracle traffic director for full site failover to have high availability?

texts:
I have two server systems, each server having both Apache Tomcat server (deployed web app) and Oracle 11g database server. The database on the two servers is synchronised by Oracle Data Guard for high availibility. Tomcat web server is running individually on each server system with no load balancing. One server acts as primary server and other server acts as redundant server.
Usually for any failure on primary server, I have to manually change the IP/Domain name to access the other server.
I have came across Oracle Traffic Director which may solve my isssue to switch any client request to redundant server in case of failure of primary server. Since I am new to Oracle Traffic Director, can anyone guide me to configure OTD for full site failover?


texts: How to change primary key's data type from int to bigint in a huge transactional database under tremendous load?

texts:
This is a legacy system, with more than 1 billion records in a table that undergoes 10 million transactions per day.

Now the application complains that:

  Arithmetic overflow error converting IDENTITY to data type int

We want to change that id column's data type to bigint, but it can't do it and times out.

What should we do? I have no clue at all. We can't stop the system, because it's a monolithic application and database and does many things. Thus we prefer not to stop the whole system.

We're using SQL Server 10.50.6000.34, that is SQL Server 2008 R2 SP3 (September 2014)


texts:
Does adding Tempdb files require a restart? We would like to Add files on the same drive, Not Delete and Not modify. We tested in Dev and QA environment, restart was not required. However for production I would like to be sure everything works properly. Some are stating conditions, not always necessary, sometimes necessary, I am reading resources below:

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'G:\tempdb2.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'G:\tempdb3.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0

Do You Need an Outage to Add tempdb files? Brent Ozar
https://www.brentozar.com/blitz/tempdb-data-files/
"Technically, not always. But practically speaking, yes. If you have to shrink files, that’s very difficult while SQL Server is in use, and we’ve seen cases where anti-virus/file control tools didn’t act up until SQL Server was restarted after the files were added."

http://jackworthen.com/2017/08/24/adding-additional-data-files-to-the-tempdb-database-in-sql-server/
"Once the additional files have been created, it’s not always necessary to perform a service restart.  However, in many production environments, simply modifying the filesize of the existing TempDB datafile (tempdev) is not possible without restarting services first. "

The following resources are saying no restart required:
https://www.codykonior.com/2015/08/10/modifying-tempdb-database-files-without-a-restart/
http://jackworthen.com/2017/08/24/adding-additional-data-files-to-the-tempdb-database-in-sql-server/


texts:
Is it possible to clear all tables with one request?
I have tried the following with no result.

DELETE a,b,c,d,e,f,g,h,i,j,k,l,m,n,o
FROM jos_bet_details as a, jos_bet_1x2 as b, jos_bet_1x2_best as c, jos_bet_1x2_prev as d,
     jos_bet_1x3 as e, jos_bet_1x3_best as f, jos_bet_1x3_prev g, jos_bet_hcp as h,
     jos_bet_hcp_best i, jos_bet_hcp_prev j, jos_bet_ou k,jos_bet_ou_best l,
     jos_bet_ou_prev m, jos_bet_debug n, jos_bet_deleted o


texts: Redshift- How to use previous row's calculations in current row

texts:
I am using redshift to determine projected inventory for next few week. I have receipts, current inventory and forecast.

ei= ei(previous row) + receipts - forecast

wk  bi  r   f   ei
1   100 20  80  40
2           50  0
3       30  15  15
4       40  14  41
5           10  31

create table ra_analytics.Test_1Test (wk int, bi int ,r int ,f int ,ei int);
insert into ra_analytics.Test_1Test values (1,  100 ,20 ,80 ,0);
insert into ra_analytics.Test_1Test values (2,  0   ,0  ,50 ,0);
insert into ra_analytics.Test_1Test values (3,  0   ,30 ,15 ,0);
insert into ra_analytics.Test_1Test values (4,  0   ,40 ,14 ,0);
insert into ra_analytics.Test_1Test values (5,  0   ,0  ,10 ,0);

select * from ra_analytics.test_1test order by wk

--Update first week of inventory
update ra_analytics.test_1test
set Ei= bi+r-f
from ra_analytics.test_1test where wk=1

--for next weeks inventory using lag function
select wk, bi, r, f, lag(ei,1) over(order by wk) +r -f as endinv
from ra_analytics.test_1test
order by wk


texts: Access for multiple domain users to SQL Server using Windows Authentication

texts:
We are delivering SQL Server 2012 SP1 Express edition as part of database with our application. We setup SQL Server express edition as silent installer that user can choose to install or not.

However, I am seeing problem with windows authentication when Domain\User1 setup  SQL Server and Domain\User2 on same machine tries to connect to the same instance created by User1. Though, User2 can log in to the instance created by User1, User2 can't create database or access database created by User1. Following is the error message shown when User1 tries to access database.

I know that we can add domain users when installing SQL Server manually. However, because we should setup SQL Server as silent installer with command-line parameter, We can't add other domain users. Is there anyway that we can allow multiple domain users to connect to database while still using Windows Authentication?


texts: I'm trying to make a custom button that exports filtered tables to a csv and downloads them to my computer. I am aware that you can do this with the '...' button but this functionality is for other users that have requested this custom button. I was able to write an R script that achieves this in Power BI Desktop but I need this to work in Power BI Service. The way the desktop version is currently implemented is by using a button that goes to an 'Export' page that runs the R-script that downloads the filtered table as a csv. If anyone has any idea how I might run a script that exports and downloads tables as csv's from Power BI Service I would really appreciate it.


texts:
I have a table like this: L(A, B, C, D, E) and functional dependencies are:

AB -> CDE
C -> D
D -> B
D -> E

I need to transform this table into 3NF. I think it's not even in 2NF. I found 3 candidate keys:

first one is obviously AB
from D->B we can change AB->CDE to AD->BCE. So another candidate key is AD
using similar logic we get another key AC

(I am not sure if I did this correct).

From D -> E (I think there are more dependencies like that) I assume that this table is not in 2NF. What is the correct way to split this table up to get 3NF?


texts: Is execution of synonyms not logged in target database?

texts:
I've been working on an issue where a single select within a stored procedure failed due to permission-issues, and we traced it to a call to a Synonym:

USE DatabaseA
GO
CREATE SYNONYM [dbo].[DatabaseB_dbo_TableT] FOR [DatabaseB].[dbo].[TableT]
GO

When debugging this, we set up a number of traces in the SQL Server Profiler to see what was going on.

DatabaseB, being the database holding the table that ultimately delivered the data has few connections, so we were able to set up the profiler without any other filters than the DatabaseName (and excluding the profiler's Application Name).

If I SELECT directly from TableT, my query shows up in the profiler for DatabaseB as expected.

But if I access the same data through the synonym, it only shows up in the profiler for DatabaseA - not for DatabaseB, which I would have thought.

Is this really as intended? While I haven't checked this with different users/schemas (experimenting with the auditing), it seems counterintuitive to me that I can access data in a database, without having this reflected in a profiler that is supposedly logging anything going on in the database.

I hope somebody can offer some insight to this.


texts: Build a Hyper-V template w/ SQL Server 2016 pre-installed

texts:
The problem that I've encountered is in renaming the "template" to the "production" server name. SQL Server sys.servers, server_id =0 retains "template", not "production".

The only way around this seems to be to re-install SQL Server -- defeating the purpose of a template.

Note: sp_addserver has been discontinued; sp_addlinkedserver cannot create server_id =0 (local).


texts: Log to disk all queries that waited for a lock over a period of time

texts:
I manage a mariadb 10.4.17 server, and I'm having the issues with a table, it seems randomly I will get lots of queries "waiting for metadata lock" causing my entire database to be useless.
I usually have to restart my server to get the database running again.
I'm looking for methods that can consistently log to disk all the queries who can use problems, without affecting performance too much.
I like something like this

watch -n 0.5 'mysqladmin -u root -ppassword "processlist"' > log.txt

But I don't know how to order by state. Anyway I'm open to any ideas. Looking for something I can look at to see what happened in the past because when the issue happens I just want to restart the db to get back online asap and don't have time to dig the root of the issue


texts: Formatting a DATE type in an Oracle database to only accept MMDD

texts: Is there a way to limit the inputs into a DATE type in an Oracle database to only accept inputs formatted with only the day and month (not including the year)?


texts: How to group items by path (graph structure) and leave only those with highest score using t-sql?

texts:
I have a table with records and their scores (table 'Offers'):

OfferId     OfferScore
----------- -----------
1           1
2           20
3           3
4           4
5           5
6           6
7           7
8           8

These records might be related. All relations are stored in the table 'OffersRelation':

OfferId1    OfferId2
----------- -----------
1           2
1           3
1           4
1           5
2           3
3           5
7           8

I want to remove all related Ids (within the group) from table Offers and leave only ones with the highest score.

In my case :

Offers with Ids: 1,2,3,4,5 are related ("group") and the offer with id:2 has the highest score:20 so I want to remove ids: 1,3,4,5 and leave only the one with id:2 from this "group".

Offer with Id:6  doesn't have any related items (no relations in the table OffersRelations) so it stays in the table Offers.

Offers with Ids:7,8 are related and Offer with id:8 has higher score than 7 so i want to remove the offer with id:7 and leave the offer with id:8 from this "group".

Expected result in the table Offers:

OfferId     OfferScore
----------- -----------
2           20
6           6
8           8

How to do it in t-sql?

Data from examples:

drop table if exists dbo.Offers
drop table if exists dbo.OffersRelation
CREATE TABLE [dbo].[Offers](
    [OfferId] [int] NOT NULL,
    [OfferScore] [int] NOT NULL
)
GO

CREATE TABLE [dbo].[OffersRelation](
    [OfferId1] [int] NOT NULL,
    [OfferId2] [int] NOT NULL
)
GO

INSERT INTO [dbo].[Offers]
           ([OfferId],[OfferScore])
     VALUES (1,1),(2,20),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8)

INSERT INTO [dbo].[OffersRelation]
           ([OfferId1]
           ,[OfferId2])
     VALUES
            (1,2),
            (1,3),
            (1,4),
            (1,5),
            (2,3),
            (3,5),
            (7,8)


texts: Available space differs between sys.database_files and database properties dialog in SQL Server Management Studio

texts:
I am looking at this for a few minutes now and could not find a thread mentioning this.

I wanted to query currently used space and available space from a database on SQL Server.
The numbers I query should match the numbers I see, when I open the properties dialog on a database in SQL Server Management Studio, like this:

So I browsed a bit and found that I can query against sys.database_files like so (from here):

SELECT
        DB_NAME() AS DbName,
        name AS FileName,
        size/128.0 AS CurrentSizeMB,
        size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
    FROM sys.database_files;

This gets me this result:

DbName       FileName         CurrentSizeMB   FreeSpaceMB
databasename databasename     17338.000000    16795.312500
databasename databasename_log   351.000000      331.234375

Trying to recognize my numbers from the previous dialog:

OK, adding 17338and 351 gets me to 17689 which is exactly the size the dialog lists
But for available space I cannot find it, 16795.312500 is off by almost half a MB

What can I do to get exactly the same number the properties dialog shows for Space Available?

Did I use the query/formulae in a wrong way or is this simply not possible at this point due to some rounding (converting pages/extents to MB...)?


texts: Slow running query with inner SELECT in where clause

texts:
I have a query that looks like the following:

USE DATABASE3;
GO
SELECT t1.COL1 AS COLUMN1,
      t2.COL2 AS COLUMN2,
      t1.COL3,
      t1.COL4 AS COLUMN4,
      t1.COL5 AS COLUMN5,
      t1.COL6,
      t1.COL7,
      t1.COL8,
      t1.COL9,
      t1.COL10,
      t1.COL11,
      t1.COL12,
      t1.COL13,
      t1.COL14,
      t1.COL15,
      t1.COL16,
      t1.COL17,
      COL18,
      COL19
FROM dbo.TABLE1 t1 with (nolock)
INNER JOIN dbo.TABLE2 t2 with (nolock)
ON t1.COL1 = t2.COL1
WHERE  (t1.tran_type_cd = [smallint value])
            and t2.COL2 in (SELECT COL20 FROM database1.dbo.TABLE3 with (nolock)
                        WHERE     (COL21 = 'varchar 250 text'))
            and t1.COL3 >= (select COL22
                              from database2.dbo.TABLE4 with (nolock)
                              where COL23 = (SELECT COL24 FROM dbo.TABLE5 with (nolock)))
            and t1.COL3 <= (select COL25
                              from database2.dbo.TABLE4 with (nolock)
                              where COL23 = (SELECT COL24 FROM dbo.TABLE5 with (nolock)))
            and t2.COL26 = (SELECT COL24 FROM dbo.TABLE5 with (nolock))

and it's running ridiculously slow.  Here's a quick rundown on some relevant details:

The following tables both just have one record -

database2.dbo.TABLE4
database3.dbo.TABLE5

t1.COL3
Table4.COL22
Table4.COL23
Table4.COL25
Table5.COL24
t2.COL26

are all datetime fields.

What's weird is that when I put in exact dates for instead of the inner SELECTs

t1.COL3 >= 'exact date'
t1.COL3 <= 'exact date'
t2.COL26 = 'exact date'

the query runs instantaneously.

Initially thinking it's utilizing a bad execution plan, I've cleared the plan cache via the following t-sql:

DBCC FREEPROCCACHE;

Also, couldn't view the execution plan of the complex query because it's just keeps running & running.  This is a production server so a reboot needs to be avoided if at all possible.

Any help is greatly appreciated.  Thank you!

P.S. Sorry for the bad formatting with the sql statement...still trying to figure things out around here.


texts: Insert characters into entire row and union with continue of row_number


texts: Duplicate records referenced across different databases

texts:
I have a table holding 'People' data (about 70K records) that coexists with an 'Address' table so there's one AddressID per every person in the 'People' table.

The idea was to centralize the data in this single 'Population' database, problem started once duplicated records started to appear due to a poorly executed Import process (Data coming from different sources), this left the table with as much as 7 records for a single Person.

Additionally the Address table started collecting 'all sorts', so let say I live in "24 Wickam Heights" you can found this address in the following ways:

24 Wickam
24 Wickam St
24 Wickam Street
24 Whikam H.
24 Wikam Str.

In some cases as bad as over 20 different versions of the same street...

The most beautiful part is that the data from this database is being referenced from at least 5 other databases in the same server, making every change a very risky process.

So I'm thinking, what steps can be taken to get rid of the duplicates?, What alternatives there is for avoiding the address table accumulating such an amount of data inconsistency?.

Maybe even to ask, is there any salvation for such a big mess? It really is a nightmare.


texts: How do I know what indexes to create for a table?

texts: Is there a way I can figure out the best way to know which indexes to create for a table?


texts: SQL Server Extend dynamic SQL query with where part fetched from Stored Procedure

texts:
I have a stored procedure that creates a dynamic SQL Query. I use sp_executesql, to have it SQL injection safe.

I have a second stored procedure that generates a where logic.
For example if I run the second procedure EXECUTE dbo.getWhereLogic @FilterID, @parOutput = @WhereLogic OUTPUT it will return as output (1 = 1)
I want to extend my first stored procedure with this where logic.

Let's assume that @SQLQuery = "Select * FROM Blabla Where 1 = 1" and @WhereLogic = (1=1)

If I do it like this:
SET @SQLQuery = @SQLQuery + ' AND ' + @WhereLogic
It will work, but it is not SQL injection safe.

If I do it like this:
SET @SQLQuery = @SQLQuery + ' AND @WhereLogic'

I get an error that there is a non-boolean type defined. There is a comparison missing. I've tried a couple of possibilities.

I think running the second procedure with sp_executesql will have no effect. That procedure returns the WhereLogic as varchar without problems, even if I simulate an SQL injection attack.

The two procedures are too long and complex to post it here. The main problem is also without code example clear.

To put the second procedure in the first and have a logical where query would be a possibility, but then I have to re write the second stored procedure and it was a lot of work :-( This should be the last option.

If someone has faced this problem already, I would appreciate an answer.
Links to other posts would be helpfull too.
Thanks in advance!

EDIT:
I try to create later a dbfiddle example.


texts: Adding a new slave slowing down previous replicas?

texts:
I have a master I was going to start slaving to a new server to do some tests on. This master is currently being slaved to 3 other boxes.

After bringing the new slave online it became clear it wasn't able to keep up.  I was hoping this was just an issue of caches needing to get warmed, but after a couple hours it was clear it was just falling farther and farther behind.

It's not as beefy hardware wise as the other slaves (less ram, slower disks) so that seemed like likely culprites.

However one thing that seemed odd was right around the time the new slave started going the other preexisting slave, which had no problem keeping up with the load for that time of day started falling farther and farther behind as well.

Any pointers on what to look at or why this might have happened.  We're running MySQL 5.1.  The new slave is in the same DC as the master and preexisting slave.

Edit: After we stopped slaving on the new slave to get some faster disks in, the preexistings slave's replication started to catch up again.

Edit: (Solution Found) I had copied a my.cnf from another slave (the one that was falling behind mysteriously).  I updated some configs such as innodb buffer size to be more appropriate for the memory the new slave had but neglected to update the server-id.  Apparently mysql does nothing to prevent this but everything described in http://bugs.mysql.com/bug.php?id=16927  was going on (Slave error's mentioned in the error log, an INSANE amount of relay logs getting generated).

I updated the server id on the new slave and things look to be going well.  Well, insofar as it's not causing the other slaves to fall behind.


texts: Activity Monitor showing long duration for WAITFOR RECEIVE

texts:
In Activity Monitor, the following query is causing high CPU and 281ms average duration.

WAITFOR (
RECEIVE conversation_handle
    , service_contract_name
    , message_type_name
    , message_body
FROM ExternalMailQueue
INTO @msgs
)
, TIMEOUT @rec_timeout

Any idea or steps how to resolve this?


texts: Display a one-to-many relationship in one row

texts:
I have a "one to many" relationship in two tables:

Table "products" contains: product_id, product_descr
Table "packing" contains: product_id, package_id, package_desc

One product has often more than one package. I want to create a query in Microsoft Access, where I get all the package options for one product in one row:

product_id package_id1 package_desc1 packageid2 package_desc2 packageid_n package desc_n

The products do have a different number of packages, it's not a fixed number.