texts:
Task

I need database design to record Recipes.

Recipes can contain several Ingredients and several ordered HowToSteps.
Each HowToStep can be split into ordered HowToSections.
A single recipe can contain a mix of several (ordered) HowToStep and (ordered) HowToSection elements.

My Solution

Table HowToSection with FK to the table HowToStep with FK to the table Recipe.
Table Ingredient with FK to the table Recipe.

Issue

I have no idea how to handle a business rule which stipulates that

"A single recipe can contain a mix of several (ordered) HowToStep and (ordered) HowToSection elements".


texts:
we're running MariaDB on our DB server. We have to install multiple databases on each DB instance (in fact each DB server runs multiple MariaDB instances) because each database must be separated.

We're nearly like an hoster (excepted that we do not provide any file hosting). When you subscribe an hosting plan you often have 1 to X databases and that's what we want to do.

In our company we have 2 DBA: 1 says that we can't have more than 500 DB by instance (due to memory limits) and the 2nd one says that's there's no limitation (excepted FS limitations).

Could you help me to understand or give me some tips (because I'm a project manager and dev. solution need to be chosen and I not a DB expert).

Thanks


texts:
We are currently backing up some schemas in Postgres using this command:
pg_dump -h localhost -n test_schema mydb | gzip > /data1/backup/test_chema.dmp.gz

And we are getting a rate of 50 Megabytes per minute, which is pretty slow, we think this could be improved.
Disk seems to be OK so is CPU.
Any thoughts on how this can get improved?


texts: Converting from InnoDB tables to innodb_file_per_table

texts:
I run MySQL and I have 7 schemas, 6 of the 7 schemas are MyISAM.

In one of my schemas I have 20 tables that are InnoDB, the rest are MyISAM.

I really want to convert those 20 tables over to file_per_table.

I read some old documentation on how to do this, but I'm wondering if this approach will work.

Export the foreign keys off the 20 tables.
Drop the foreign keys.
Convert the tables to MyISAM.
Stop MySQL.
Change the my.cnf/ini to put MySQL in innodb_file_per_table mode.
Delete the idbdata files.
Start MySQL.
Switch those tables back to InnoDB (which should be in file mode now).
Re-apply the foreign keys.

Am I missing any steps? Or am I making a mistake somewhere?


texts: Unable to see list of tables in Oracle SQL Developer

texts:
I am using SQL Developer and now I can SELECT but viewing those tables in the list is not available. Is there some configuration I need to do in order to see the tables? User has select all tables privileges.

I am expanding the tree under my own user, not under the user who owns the tables.


texts: Any merit to MSSQLSERVER Trace from logman as opposed to XEvent Package 0

texts:
The general understanding of monitoring SQL Server activity is that using SQL Profiler is very heavy, server side traces are less heavy, but extended events are even less heavy.

Today I discovered you can capture SQL traces with logman.exe

logman query providers
...
MSSQLSERVER Trace                        {17531BCD-C627-46A2-9F1E-7CC920E0E94A}
...
XEvent Package 0                         {60AA9FBF-673B-4553-B7ED-71DCA7F5E972}
...

Assuming I'm running SQL Server 2008 or later, is there any reason to ever prefer a tracing session as opposed to extended events here? Does doing tracing through logman eliminate any of the performance issues of a traditional server side trace, or am I still just running a server side trace and just writing the results to an etl file with logman?


texts: Index Reorgs Blocking Statistics Updates Blocking Inserts

texts:
After upgrading from SQL Server 2014 to SQL Server 2016 last month, we are seeing statistics updates on a table being blocked for hours while a table’s indexes are being reorganized. Inserts are in turn being blocked by the statistics update.

The database in question initially had Auto Update Statistics enabled and Auto Update Statistics Asynchronously set to disabled. We tried enabling Auto Update Statistics Asynchronously but saw the same problem. Last Friday we disabled both Auto Update Statistics and Auto Update Statistics Asynchronously and have not experienced the blocking chain again.

This has been extremely problematic since we get a large bolus of data in each night and this blocking chain prevents that data from filing prior to the start of business hours. I am discussing running index reorganizing less frequently with the rest of the team but am getting some resistance.

Are reorgs blocking statistics updates blocking inserts something you have seen before? What are we losing by disabling Auto Update Statistics and Auto Update Statistics Asynchronously? We are already using Ola’s scripts to update all statistics on a weekly basis.


texts: PostgreSQL Locking Behavior w/ Partial Index

texts:
For PostgreSQL, will create index foo on bar where condition=true, i.e. without concurrently, lock an entire table for a partial index?

Or will only the affected rows, i.e. where condition=true be locked?

I did not see it spelled out in https://www.postgresql.org/docs/9.6/sql-createindex.html.


texts: Should the host machine always be restarted after upgrading an SQL Server 2008 instance?

texts:
My organization recently upgraded all its production SQL Server 2008 instances to Service Pack 3, Cumulative Update 1. It's my responsibility to make sure that our development database environments are running the same version as production does, while minimizing downtime and disruption for our developers.

Each development environment contains six SQL Server instances. We have eight development environments shared equally between two machines. For each instance, I have to install Service Pack 3, then install Cumulative Update 1. Each installer restarts the instance's Windows service during the upgrade process, so I have to give advance warning to developers using the instance. The installer takes a long time to set itself up, so I'm upgrading each environment as a batch to quicken the process.

Because one machine hosts several instances, a restart potentially disrupts all the developers using the machine, even if their instance is not being upgraded. Therefore, I want to avoid unnecessary restarts. But only on the first run did the install tell me "You must restart the computer to complete SQL Server Setup".

Where the installer does not tell me that I must restart the machine, I don't want to a restart unnecessarily. My senior fellow DBA told me that I should restart the host machine anyway, to keep the environment 'clean'.

What bad things could happen if I don't restart the host machine after upgrading an instance?


texts: Is there a more efficient way to resolve this "one to many" circular reference?

texts:
In our ERP database, there is 1 table for products called PRODUCTS, which contains both kits and single SKU product information, and a table called PRODUCT_KIT_ITEMS that contain the relationships between kit SKUs and their components.

In the PRODUCT table, each kit has a KIT_ID on the parent SKU row and the PRODUCT_KIT_ITEMS table lists each component PRODUCT_ID from the PRODUCTS table and the kit id of the parent SKU associated with it.

What I wanted was a list of all the SKUs of components associated with a parent SKU, both of which would be selected from the same column on the PRODUCTS table.

This was the solution I came up with:

SELECT   Parent.[PRODUCT_SKU] AS KitParent,
         Child.[PRODUCT_SKU] AS ChildSKU
FROM     [PRODUCT_KIT_ITEMS]
JOIN     (
          SELECT [PRODUCT_ID],[PRODUCT_SKU]
          FROM   [PRODUCTS]
         ) AS Child
ON       Child.[PRODUCT_ID] = [PRODUCT_KIT_ITEMS].[PRODUCT_ID]
JOIN     (
          SELECT [PRODUCT_ID], [PRODUCT_SKU],[KIT_ID]
          FROM   [PRODUCTS]
          WHERE  [KIT_ID] IS NOT NULL
          AND    [KIT_ID] != 0
         ) AS Parent
ON       Parent.[KIT_ID] = [PRODUCT_KIT_ITEMS].[KIT_ID]
ORDER BY Parent.[PRODUCT_SKU];

I'm a bit of a newbie and was wondering if there was a better way to accomplish this, for instance, without running two sub-queries on the PRODUCTS table.


texts: Using a string with single quotes in a table

texts:
I'm trying to insert the following string, as written, into a table:

'affinity mask', 0

However I can't figure out how exactly I need to escape the quotes. It always wants to treat the , 0 as a separate column.

How can I escape the comma properly?


texts: how to search this field in Postgres for an array of characters

texts:
I have a users table which is an id, name, and tags. I'd like to search tags for a specific piece of text. How would I do do this?

id                      | integer                     | not null default nextval('users_id_seq'::regclass)
name                    | character varying           |
tags                    | character varying[]         | default '{}'::character varying[]

How would I search for a tag like "skiing"?


texts: Database Tuning Advisor error: ...% of consumed workload has syntax errors

texts:
Hi I've generated a trace file using SQL Server Profiler and save that trace as a table in SQL server DB. The trace file workload was generated by executing a query directly from SQL Serve Management Studio. So there were no syntax errors in the query.

Now I'm using SQL Server Database Tuning Advisor to see analysis recommendations. To achieve it specified the table name as a workload source and selected all interested databases. when database Tuning Advisor completes the analysis the "Process" tab appears and displays the following message for the "Consuming Workload" in the "Action" column.

TITLE: DTAEngine

"50% of consumed workload has syntax errors. Check tuning log for more information."

I'm noticing three issues described below:

1) Before starting analysis I selected the "Save Session Log" checkbox. But the Tuning Log on the Process tab is empty;

2) I have only two types of reports generated after the analysis completes: the "Index Detail Report (current)" and the "Index Detail Report (recommended);

3) No Recommendation were generated.


texts: Reporting services scheduled reports are clogging up sql job history log

texts:
This is a bit of a cross-disciplinary question - Hopefully I’ve posted in the right place.

SQL server 2008 R2

Question:
How can I limit the number of rows in [sysjobhistory] for specific jobs, especially jobs inserted into SQL Agent from High Frequency Reporting Services Subscriptions?

History:
I have a SQL server that where the SQL Agent has to handle a mix of ETL jobs and jobs coming from reporting services schedules. Recently I am finding that I only have 2 or 3 days of history on the ETL jobs where I used to have 5-7 days or more.
 The SQL agent properties for Job History retention are:

Maximum Job History Size (in rows): 5000
Maximum Job History rows per job: 1000

If you were thinking “reporting services jobs are going to be the problem…” you would be right. We recently introduced some reporting services reports that are responsible for populating data for overhead displays, running at 3 min intervals all day. Of course these jobs are hitting their max rows per job (1000) in no time flat and leaving only 1000 rows for all the other jobs total.

I don’t want to change the SQL Agent Job History row limits – but I would like to have longer histories on the ETL jobs.

The Reporting services DB carries its own logs internally (ExecutionLog), so I don’t really need 600+ log entries per day per job in sysjobshistory for these High Frequency subscriptions.

The Jobs inserted into SQL Agent by Reporting Services scheduler specifically warn against modifying them outside of Report Manager..but as far as I can see the Report scheduler has no options for this.


texts: Split values from field and count them according to another field

texts:
I have following MySQL db structure:

content
=======
id | tags
---------
1  | ,abc,def,ghi,
2  | ,abc,foo,
3  | NULL
4  | ,foo,bar,

categories
==========
id
--
1
2

categories_to_content
=====================
id | category_id | content_id
-----------------------------
 1 |           1 |          1
 2 |           1 |          2
 3 |           2 |          3
 4 |           2 |          4

What I'm trying to do is to SELECT tags assigned to category and count them. Result for given schema:

tag | category | count
----------------------
abc |        1 |     2
def |        1 |     1
ghi |        1 |     1
foo |        1 |     1
foo |        2 |     1
bar |        2 |     1

Decomposition isn't possible (I hook up into existing CMS database).

My existing query:

SELECT tags, category_id as category FROM content
JOIN categories_to_content ON content_id = content.id


texts:
I am working with a table that has this format :

CREATE TABLE dbo.ID_STORE
( WORKING_ID bigint PRIMARY KEY CLUSTERED )

The table stores around 2 millions rows, but the stored ids are not consecutive,  MAX(WORKING_ID)-MIN(WORKING_ID) is around 24 millions .

When I look at  used space, I find around 57 Megabytes, when I expected slightly above  2 10^6 x 8 = 16 Megabytes. Can anyone explain the difference?

EDIT : these figures were obtained from the very first import into said table. This table is also TRUNCATEd before it is filled.


texts: Testing postgresql 9.4 streaming replication

texts:
I have setup the replication as per https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps.

And I see the slave is in streaming replication mode.

But I do have a question that, I have a bunch of databases in my master. Now should I create the same databases/tables and have the existing data in master, in slave, before testing to ensure my replication works?

Any suggestion highly appreciated. Thanks.


texts: Seeking help with Extended events behaviour

texts:
I'm using Extended Events to track the procedures that are being executed on the server, along with the Hostname of its executor.

I've used the following XE script for the same:

CREATE EVENT SESSION [EXEC_SP] ON SERVER
ADD EVENT sqlserver.sp_statement_completed
(
  SET collect_statement=(0)
  ACTION
  (
    sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,
    sqlserver.nt_username,sqlserver.server_instance_name,
    sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,
    sqlserver.transaction_id,sqlserver.username
  )
  WHERE ( [sqlserver].[is_system]=(0))
)
 ADD TARGET package0.asynchronous_file_target
 (
   SET FILENAME = N'C:\EXEC_SP.xel',
   METADATAFILE = N'C:\EXEC_SP.xem'
 )
 WITH
 (
   MAX_MEMORY=4096KB,
   EVENT_RETENTION_MODE=NO_EVENT_LOSS,
   MAX_DISPATCH_LATENCY=15 SECONDS,
   TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
 );

However, every statement inside the body of the procedure being executed constitutes a row at the target.

For instance, if the body of my procedure has 5 statements in it, the event will be fired 5 times, and there will be 5 different entries corresponding to the same at the target.

I seek just a single entry for every procedure being executed instead of multiple entries.

That is every procedure being executed should constitute just 1 row instead of multiple.

How to resolve this?


texts:
Lets say that we have two entities
Entities:

Bus        (idBus*)
Stop       (idStop*,stopDescription)

Where a Bus passes from many Stops and vice versa, therefore the cardinality is N:N.

My question arises from the fact that when we consider to know the idStop and the idBus then we will also know a number of attributes like Direction,NextStopID, TimeOfArrival
How should i map those attributes?

My thought is to make a bridge table having idStop and idBus as Foreign Keys and a number of extra attributes that are not present in either the Bus or the Stop table, but I don't know if this is doable or the correct way to go.

Any thoughts?


texts: Database schema design to send unique messages to user

texts:
We are working on a system that users (hustler) can post their skills, and based on their listed skills, a client can try to hire them to assist them on a project. If a client enters specific text defining the skills sought, our system will search the database for matching text from skills posted by hustlers.

But apart from just producing results sets based on client search, we want to be able to send client posts to hustlers that match the criteria of skills a clients is looking for just like job sites find matching jobs for users.

So the question is, how can we send matching jobs requests to the hustler posted by clients without having to send duplicate records? I would like to know how this can be handled on the database level? One solution I thought of was to search on matching skill set AND also date posted e.g., if we have to send daily matching posts to a hustler, then we have to search matching skills AND created_date <= DATE_SUB(CurrentTime(),INTERVAL 24 HOUR). Something like this.

So please advise on how to best go about implementing this, and giving an example of a data model to handle this feature.


texts: Error when creating a stored procedure in postgreSQL-10

texts:
I couldn't get my first stored procedure to work, so I copied this example from the manual:

CREATE PROCEDURE insert_data(a integer, b integer)
LANGUAGE SQL
AS $$
INSERT INTO tbl VALUES (a);
INSERT INTO tbl VALUES (b);
$$;

CALL insert_data(1, 2);

and I get the following error message:

ERROR:  syntax error at or near "PROCEDURE"
LINE 1: CREATE PROCEDURE insert_data(a integer, b integer)
               ^
SQL state: 42601
Character: 8

What am I doing wrong?

I'm running the database on localhost, running as admin user. I installed it myself. Installation process completed without errors, and I am able to create tables, functions, triggers, etc.


texts: How to put one table on one disk and anther table on another (a ramdisk)?

texts:
I need to build a database with one table stored on one disk and anther table stored on a different disk (a ramdisk).

Is it possible to do it?

As far as I know, using two separate databases, one for each disk, is not efficient.

I am considering PostgreSQL, but other options can be ok. The reason I want to use a ramdisk is to deal with non critical data that can be deleted at any time, to avoid SSD wearing.


texts: MySQL Like for Like - 100% accurate Backup/ Restore

texts:
I've struggled to find the answer to get a perfect backup and restore for MySQL.
I need to do performance test on a database so I need to make sure that the page files, indexes, fragmentation - the whole things is like for like - 100% preserved.
I tried copying the raw files (.frm, .ibd, .trn, .myi etc) files across but get
Table '' doesn't exist in engine.
I also tried dBForge to copy the database which worked, but after looking at the fragmentation levels I can see differences, so I think this isn't a like for like (although I'm copying from production so perhaps I saw some anomaly there).
mysqldump - Does this do it?


texts:
What is difference between SELECT * FROM table and SELECT * FROM table WHERE 1

  Query 1:

 SELECT * FROM table

Number of records : 103
Load time : 0.0005 sec

  Query 2:

 SELECT * FROM table WHERE 1

Number of records : 103
Load time : 0.0003 sec

  Question 1 : What is the difference between these 2 queries, if it
  loads the same data.

  Question 2 : Why there is these much time difference, if it loads the
  same data.

  Question 3 : Why phpmyadmin by default loads SELECT * FROM table WHERE 1 query ?

  Question 4 :What MySQL do for ABOVE queries in MySQL Query Caching?

Note :

The difference is minor here because number of records are small
(103),

  but when number of records increases then time difference is    also
  increase.

- So let's when we have 100000 number of records then we can see
   notable time difference.


texts: Can MySQL Index Merge an Composite index with a single column index?

texts:
I am tuning a query and currently have an index as following :

Index 1 : (col1, col2)

An example query would be :

SELECT * FROM t1 WHERE col1 = '1' AND col3 = '2';

I have created an index on col3 so the query uses that as it is the most selective. However, I am wondering if it is able to use the first key in the composite index as an index merge. I would imagine this is not possible as far as an intersecting index merge but just making sure. I could not find this in the MySQL documentation and I am using 5.5.

To verify my results :
I have created a single column index on col1 so my new indexes are as follows :

Index 1 : (col1, col2)
Index 2 : (col3)
Index 3 : (col1)

The query now index merges Index 2 and Index 3.

Is it true that you cannot index merge the first key in a composite index with another single column index?


texts: How to outer join multiple tables to retrieve uncommon records

texts:
I'm trying to do an outer join in MySQL to identify records not common to 3 tables. Here's my current effort http://sqlfiddle.com/#!2/02b92/30

I'm trying to retrieve the records not common to all three tables: 2,C,c (tbl2) 6,E,c (tbl3). Instead, I'm getting back the one common record: 1,A,c.

Thanks

SELECT *
FROM tbl1 A
LEFT OUTER JOIN tbl2 B
ON(
  A.a = B.a
  AND A.b = B.b
  AND A.c = B.c
)
RIGHT OUTER JOIN tbl3 C
ON(
  B.a = C.a
  AND B.b = C.b
  AND B.c = C.c
)
WHERE A.a is not null;


texts: Identity seed increased when using IDENTITY_INSERT

texts:
Running this snippet on SQL Server 2008 produces different result than on SQL Server 2014. I need them to produce the same result.

My scenario is that I am writing unit tests (using tsqlt) and this code is part of a huge procedure which I am trying to test. In my test code I can do anything because that code will never end up in production. This "bug" does no damage in production, but is giving me problems when writing my tests. Meaning, I do not want to change the code, only the tests.

My local dev environment is SQL Server 2014 but some of our CI environments are still SQL Server 2008.

My first question is if there is any way to avoid this (bug) on 2008?
My second question is if there is any way to make 2014 behave the same as 2008  and thus making the tests pass on both 2008 and 2014. Any setting or trace flag or hack?

My current hack is to reseed the table to 0 (instead of 1) in case server is 2008. I do no not like this as I will need to change it or remove it when our environments are upgraded.

IF CHARINDEX('2008', @@VERSION) > 0 BEGIN
        -- HACK to fix identity bug in SQL 2008.
        DBCC CHECKIDENT('...peter', RESEED, 0);
END

Troublesome code:

IF OBJECT_ID('tempdb..#peter') IS NOT NULL DROP TABLE #peter;
CREATE TABLE #peter(ID INT IDENTITY(1, 1), VALUE CHAR(10));

SET IDENTITY_INSERT #peter ON;
INSERT INTO #peter( ID, VALUE )
VALUES  ( -1,'Thing' ); -- Explicit negative ID value inserted here
SET IDENTITY_INSERT #peter Off;

INSERT INTO #peter( VALUE )
VALUES  ( 'Stuff' );

SELECT * FROM #peter;

Result in SQL Server 2014:

SQL Server 2008:

Server versions:

Microsoft SQL Server 2008 (SP4)
Microsoft SQL Server 2014


texts: Deadlocks With Trigger Based Replication

texts:
A table ORIGINAL exists with the following structure:

ID    VARCHAR Length 10 (key)
VALUE VARCHAR Length 10 (non-key)

A table REPLICATION exists with the following structure:

ID               VARCHAR Length 10 (key)
CHANGE_TIMESTAMP NUMBER  Length 15 (non-key)

I want to log every changed primary key to the REPLICATION table, with the latest change time stamp.

Therefore I have created this trigger in oracle:

CREATE OR REPLACE TRIGGER REPLICATION_TEST
  AFTER INSERT OR UPDATE ON ORIGINAL
  FOR EACH ROW

  DECLARE timestamp DEC(15);
  BEGIN
    SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'yyyymmddhh24miss'))
      INTO timestamp
      FROM dual;

    INSERT INTO REPLICATION
      FIELDS ("id", "change_timestamp")
      VALUES (:NEW."id", timestamp);
    EXCEPTION WHEN dup_val_on_index THEN
      UPDATE REPLICATION
        SET "change_timestamp" = timestamp
        WHERE "id" = :NEW."id"
END;

Functionally, this works just fine. But in a productive environment with multiple sessions where arbitrary data changes can happen at any time, this infrequently leads to deadlocks. Presumably because of the UPDATE statement.

An alternative approach would be to add the CHANGE_TIMESTAMP field as additional key field to do only INSERTS into the REPLICATION table and skip the UPDATE in case of duplicates. This would work functionally just fine, but would obviously result in much more data being produced which I'd like to avoid.

What else can I do?


texts: Is it a good to move data out of the original table when it's deleted or archived?

texts:
I have a table with offers where I perform typical CRUD actions.

Each offer can has one out of the following statuses: active, deleted, archived, withdrawn, etc.

Most actions I perform only for active offers, so is it a good idea to move offers with other statuses to other tables, like offers_archived, offers_withdrawn?


texts: How do Databases Ensure Uniqueness of Primary Keys, Particularly UUID Keys?

texts:
First off, I am assuming that it is possible for a database to generate but not insert a duplicate UUID value for a column that has a unique constraint.

Is there any intelligent way that databases typically create UUID values for unique columns or does it simply go through the loop of:

Generate a random UUID value for the column.
Check to see if the value is unique in the context of that column.
Insert the value into the column if it is unique, go back to step 1 if it isn't.

until it generates a unique value. I'm aware that the possibility of the database having to do a second iteration in a situation like this is infinitesimally small, but it's still possible. I'm just wondering how database engines deal with this possibility and if my description of the loop is similar to what actually happens.


texts:
Is raid 5 suitable for a mysql installation?

Let me explain my application further. My application is a socket programming which will connect with gps device to receive gps string and there after do a further processing. The socket programming will be in another server and db in another server. So during the further processing is where it will query from the db. So here I guess there will lots of i/o rite. Minimally during the further processing there will be minimum of 5 select and the insert will be minimum 1 but at times it can be even minimum 4 or more and also a number of updates. Hope I am clearer now.


texts: Could not allocate a new page for database because of insufficient disk space

texts:
Using the SQL Server Import and Export Wizard, I get this error:

  Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
  Could not allocate a new page for database 'database' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

I'm trying to upload a series of 2GB files and while loading the 4th file, I continue to get the above error message even after I go to Database properties > Files and change the Filegroup's autogrowth to 2500 MB and maxsize unlimited.

What is the best workaround? This data will ultimately take up around 60-80GB.


texts:
I have a connection to a Sybase database which we stage in a SQL Server 2008R2 database. I can connect the the database and the connection tests out fine. I am using an OLE DB source with a Sybase Driver.

I can even preview data. Note I have shrunk this data set so the data can not be seen but it still demonstrates that the source does return rows.

However when I execute the package it results in failure.

[source 1] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "MyDataSource" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Any ideas as to why this is happening?

UPDATE
Adding other error message in spite of them being the opposite of useful.
[SSIS.Pipeline] Error: component "source" (1) failed validation and returned error code 0xC020801C.
[SSIS.Pipeline] Error: One or more component failed validation.
Error: There were errors during task validation.

UPDATE
After a few attempts the account sybase account gets locked. Must have something to do with the password.


texts: How can I join msdb.dbo.sysalerts to msdb.dbo.sysoperators?

texts:
I am using SQL Server 2008 R2 and I want to create a script of my jobs.

I need to find all objects that exist in one job.

To achieve this goal, I get all operators with this query:

SELECT * FROM msdb.dbo.sysoperators

and I can find all Alerts with this query:

SELECT * FROM msdb.dbo.sysalerts

Each alert has several operators.

I want to join the two tables together.

How can I get all operators for all alerts?


texts: sys.login_token doesn't include particular windows groups while they can be listed by sys.database_principals

texts:
When I query

SELECT principal_id, name, [type]
FROM sys.login_token

I found some local windows groups were not there.

However, when I check by

SELECT principal_id, name, [type]
FROM sys.database_principals

I can see those groups in the result.

I tried to remove then add back the associated users and logins as well as restart the SQL Server Service but didn't get lucky so far.

Not sure if I missed something obvious, any direction will be appreciated.

The problem appears to be more complicated than I thought.
The server is in domain B while the user account is under domain A.
Domain B has one-way trust to domain A so domain A user can login by their account to the server directly.

For policy or some technical problem, we can't add DoamainA\UserAccount to local group in the server straight; we have to contain DoamainA\UserAccount in a domain group of domain B then add this domain group to the local group on the server.

However, when I login by DoamainA\UserAccount in this condition, sys.login_token doesn't seems to recognize DoamainA\UserAccount as a member of local group.

I found this since when I create a new local account and add it to those disappeared local groups then login with this local account. Then I can see those disappeared groups be listed by sys.login_token without problem.

Therefore, did we mess up the one-way trust setting or it supposes to work in that way?
Is it normal that we can't add DoamainA\UserAccount directly when one-way trust? Or there should have some sort of um firewall or port setting we did wrong?


texts: SQL Server sp_BlitzCache - Many Plans for One Query

texts:
I'm in need of a bit of assistance.

I ran sp_Blitz on one of my SQL Servers and one of the results it returned is "1531 plans are present for a single query in the plan cache - meaning we probably have parameterization issues.".

I followed the link in the result.

I then ran sp_BlitzCache @ExpertMode = 1 to find queries with multiple plans but the #Plans and #Distinct Plans columns both return the value 1 for all rows.

How do I proceed to isolate the single query that has multiple plans?

Environment:
SQL 2012 SP3 CU8

sp_BlitzCache version 5.7


texts:
I am executing a select statement to retrieve fragmentation percentage of indexes and facing below error.

    SELECT A.NAME,B.NAME,C.avg_fragmentation_in_percent
      FROM SYS.TABLES A
      INNER JOIN SYS.INDEXES B ON A.OBJECT_ID=B.object_id
      CROSS APPLY SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),
        A.OBJECT_ID,B.INDEX_ID,0,DEFAULT) C
    WHERE B.NAME IS NOT NULL

And Error is

  Msg 413, Level 16, State 1, Line 1
  Correlated parameters or sub-queries are not supported by the inline function "SYS.DM_DB_INDEX_PHYSICAL_STATS".
  Msg 413, Level 16, State 1, Line 1
  Correlated parameters or sub-queries are not supported by the inline function "SYS.DM_DB_INDEX_PHYSICAL_STATS".


texts: Git as distributed and decentralized storrage with regard to ACID and CAP

texts:
When using Git to store documents distributed and decentralized it can be considered as a database.

How would the ACID properties and the CAP theorem correspond to git in this case?

I think one has to distinguish between a single repository and the whole network of repositories.

ACID - for a single repository:

A would be ok, as there are commits
C depends on the use case and thus is not relevant
I is the big question,

if one is using only one branch that should be fine
if one is considering multiple branches I would be fine if a rebase (resp. merge) without conflicts is possible to a master branch (or even all other branches) but is not fine if a rebase would result in a conflict.

D depends on your hard disk but should generally be fine

ACID - for a distributed setup:

would be the same as above plus, that I has to be seen with respect to all other clones on the network of repositories

CAP:

C

for a single repository this would be ok
looking at a distributed setup this would only be true, if every read operation is preceded by a pull.

A this would always be true since the local copy is always available
P if a pull is not possible than one has to decide whether to sacrifice A or C (as in the PACELC theorem). Also one can see if the majority of remote repositories is available and use some quorum approach.

(I understand the CAP-C more like the ACID-A, see https://dba.stackexchange.com/a/202125/147543)

Is this a valid interpretation of ACID and CAP as it is used in the database domain? Or is there already an ongoing discussion about Git with regard to ACID and CAP?


texts: Is it possible to track a function's dependencies in Postgres?

texts:
When there is a function that is dependent on another object, is there any way to track that dependency?

For example, is there a way to tell that test2() relies on test1?:

create table test1 (id int);
create function test2() returns int language sql as
   $$ select id from test1 limit 1 $$;

Context: I have a system of materialized views, with a "refresh all" procedure implemented using the logic from https://wiki.postgresql.org/wiki/Refresh_All_Materialized_Views. Unfortunately, the database has evolved such that one of the matviews must call another matview through an intermediary function. The only workaround I can think of is to either scrap this system and define the refresh order manually, or introduce false dependencies on the function's source matview whenever the function is used. Neither option is appealing; is there any alternative?


texts: db_datawriter vs grant delete, update, insert

texts:
I have an application set up in which the service account uses a user defined role which has each object in the database defined with specific permissions. It seems that every table in the database is listed here and they all have update, delete, insert, and select - so, essentially, db_datareader (select) and db_datawriter (insert, update, delete). The role also has select on all views, and execute on all procs.

When a user tries to update a document using a word plugin, with only the user role permissions, the update fails. If i grant db_datawriter on top of the user role to the account and the update is then attempted, it succeeds.

Where could the discrepancy be here? Why if both of these options are essentially giving the same permissions can we be getting different outcomes?


texts: EAV - COUNT()/SUM() on column and row values with and without NULLs

texts:
Assume that there is a table

demo

with these data:

id
userID
title
data

1
1
DATE
01-01-2021

2
1
COLOR
RED

3
2
DATE

4
2
COLOR
YELLOW

5
3
DATE
04-01-2021

6
3
COLOR
RED

5
4
DATE
04-01-2021

6
4
COLOR
PINK

7
5
DATE
02-01-2021

8
5
COLOR
PINK

7
6
DATE
08-01-2021

8
6
COLOR
PINK

If the data of the row date is empty color is not picked-up yet.
The information overview i need to view on website php page is:
Picked-up of RED: 2
Total RED: 2
Picked-up of Yellow: 0
Total Yellow: 1
Picked-up of Pink: 3
Total  Pink: 3


texts: Expdp with metadata_only gives way too large dump. Why?

texts:
There is an oracle 11g schema with amount of data about 700 Gb. There are tables/indexes only in that schema, no procedures/packages.

Doing expdp on that schema

expdp schemas=TheSchema directory=dir dumpfile=dump logfile=log content=metadata_only

, I expected quite a litte dumpfile because of metadata_only. Doing tools/export-user-objects in pl/sql developer gives an sql file with text size a half of megabyte.

But instead there appeared a 20 Gb dumpfile!

Is there something large in dumpfile created by this expdp, like index contents?

What could be done to minimize the dumpfile done with expdp, if I want just to create tables like in TheSchema in another schema?

UP: There is quite a few number of tables/indexes in the text-sql-dump of schema:

> grep "create table" TheSchema.sql | wc -l
523

> grep "index" TheSchema.sql | wc -l
501

> grep "partition" TheSchema.sql | wc -l
96


texts: Oracle data Guard status (active/passive)- version 11g

texts:
I have 2 servers: Primary (ABCUAT) and Standby (ABCUAT1) configured as Active-Standby DataGuard by previous DBA.

When I took over the system, I think  DataGuard was not in sync, but I could not confirm that because the Archive Log List gives me the same log sequence on both server.

Questions:

If DataGuard is not in sync, why does the Archive log list show the same sequence  number in both server? I thought if the sequence number were the same, it meant the log have been applied to the standby database.
How to resume DataGuard if not in sync? The gap seem to be huge according to max(sequence#).
I tried to delete some old archive log files with RMAN> delete noprompt expired archivelog all; but got in response:

  specification does not match any archived log in the repository,

but physically in the directory there are 50GB of files dated year 2018.

Findings:

SYS@ABCUAT>archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     190144
Next log sequence to archive   190146
Current log sequence           190146

Standby server :

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     190144
Next log sequence to archive   0
Current log sequence           190146

Primary:

SYS@ABCuat>select max(sequence#) from v$log_history

MAX(SEQUENCE)   190251

Standby server :

SQL> select max(sequence) from v$log_history

MAX(SEQUENCE#)   7421

DataGuard configuration:

DGMGRL> show configuration;
Configuration - dg_config
  Protection Mode: MaxAvailability

  Databases:
    ABCUAT  - Primary database
      Warning: ORA-16629: database reports a different protection level from the protection mode
    ABCUAT1 - Physical standby database (disabled)
Fast-Start Failover: DISABLED

Configuration Status:
WARNING

DGMGRL> show database ABCUAT1

Database - ABCUAT1

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   (unknown)
  Apply Lag:       (unknown)
  Apply Rate:      (unknown)
  Real Time Query: OFF
  Instance(s):
O    ABCUAT1


texts:
I have keywords column in my table and there are comma (,) separated values. example: hello, world

I have search engine with same codes:

$search = $_GET ['search'];

$query = $db->prepare("SELECT COUNT(*) as num FROM table WHERE keywords LIKE ?");
$query->bindValue(1, "%$search%", PDO::PARAM_STR);
$query->execute();

$sql = $db->prepare("SELECT * FROM table WHERE keywords LIKE ? LIMIT $start , $limit");
$sql->bindValue(1, "%$search%", PDO::PARAM_STR);
$sql->execute();

When I type only 'hello' or only 'world' in my search bar, there is result, but if I type both words together, like 'Hello world' there is no result.

EDIT:
I edited my codes for FULL TEXT SEARCH

$query = $db->prepare("SELECT COUNT(*) as num FROM table WHERE MATCH (id,keywords) AGAINST ('$search*' IN BOOLEAN MODE)");
$query->execute();

and

$sql = $db->prepare("SELECT * FROM table WHERE MATCH (id,keywords) AGAINST ('$search*' IN BOOLEAN MODE) LIMIT $start , $limit");
$sql->execute();

It works better, but now I get some irrelevant results. Is there any way to filter results and order they relevance?
Thanks


texts: SQL Server HA Design (FCI) - Windows Storage Replication - Test-SRTopology - Physical Memory Error - AWS EC2

texts:
I'm trying to set up AlwaysOn Failover Cluster Instance (Not Availability Groups) on 2 VMs(each on different AWS Availability Zone) in AWS-EC2 instances.
To make this happen, I've to set up Windows Server(2016+ feature) stretch cluster Storage Replication between the disks in the respective VMs.
When I run the "Test-SRTopology" power shell command, I get the physical memory error as below. My newly spun-up VMs has nothing else running on it and has 32GB of memory and almost all of it is free.
Any idea why I'm getting this memory error? Is Windows Stretch-cluster Storage replica designed to only work on Physical machines and NOT VM? If not, any idea what is going on?
Thanks for your guidance.
Physical Memory Requirement Test: SQL-01 does not have the required physical memory to deploy Storage Replica. The minimum physical memory requirement to deploy Storage Replica is 2GB. Actual physical memory available on TM-SQL-01 is 0GB.
Physical Memory Requirement Test: SQL-02 does not have the required physical memory to deploy Storage Replica. The minimum physical memory requirement to deploy Storage Replica is 2GB. Actual physical memory available on TM-SQL-02 is 0GB.
Strange!


texts: Can a Postgres LIKE statement return an exact match?

texts:
I'm trying to search a Postgres table using LIKE and %, but the query is not returning results.

The column local_username will contains values formatted like: username@domain.local OR username@domain.

My query is searching this one column based on both the username and domain sections.

An example query is below:

SELECT *
FROM users
WHERE local_username LIKE 'username%'
  AND local_username LIKE '%domain%';

I'm concerned that, because there may be no characters after domain in the query, the second % is going to cause it to not return any results, since the % causes it to search for more characters, which in the case of the above query are not present.

Is there another keyword other than LIKE that I should be using? Or do I need to check each possibility (example below).

SELECT *
FROM users
WHERE local_username LIKE 'username%'
AND (local_username LIKE '%domain%' OR local_username LIKE '%domain');


texts:
The question is about the behavior of simultaneous SELECT and UPDATE in MySQL InnoDB table:

We have a relatively large table which we periodically scan reading several fields including a field named, say, LastUpdate. During the scan we update previously scanned rows. Updates are batched and performed in a background thread - using a different connection. It is important to note that we update rows that have already been read.

Three questions:

Will InnoDB will save previous versions of the updated rows since the SELECT is still in progress?
Would using READ-UNCOMMITTED for the SELECT help?
How can I confirm that InnoDB saves or does not save previous versions of the modified rows in its redo-log.


texts: How to link Users and Jobs with Addresses in my database structure?

texts:
If I have two entity types in my system: User and Job (a particular Job being an available position of employment somewhere in the country). Both entity types are defined by Addresses.
For example, a User will have a Home Address, a Locality, a Country, a Postcode, etc. A Job will also have this information.
Implementation considerations
Should I use one Addresses table or split it in two tables as, for instance, User_Addresses and Job_Addresses?

The idea of a single table is simple, but I don’t know what to think of it when it comes to mixing foreign Ids of two entity types that have nothing in common. I wouldn’t be able to add a foreign key because that column could hold an Id that is either defining a User or a Job.

On the other hand, I am not sure about having two tables with the same column types.


texts: How to restrict access to current user record and its rights in postgres

texts:
Postgres 9 database contains additional information about postgres users:

create table public.user
(
  user char(10) primary key,  -- CURRENT_USER in postgres
  name char(30)
);

and user roles;

create table public.userrole (
  user char(10) references user on delete cascade on update cascade,
  roleid char(30) not null );

User restricteduser should have limited access to its own records only.

I tried

revoke all on  user,userrole from restricteduser cascade;
grant select on user, userrole to restricteduser;
grant update (name) on user to restricteduser;

But in this case restricteduser can see other users record only and update other users names.

How to restrict access to restricteduser only to its own records in user and userrole tables and update access only to own name column in user table ?

Update

I need 2 things:

Retrieve and update current user name and some other fields from users table in
roced whose id is currnt_user
Retrieve list of roles for current_user  from roles table.

Users can use ODBC for data access so application level security is not possible.

Is it possible and reasonable to create plpgsql methods for those operations ?


texts: How can I get rid of this Lazy Spool, or otherwise improve this query's performance?

texts:
Here is the plan:  https://www.brentozar.com/pastetheplan/?id=rkM8d7ONS

I am mostly interested in how to get rid of the lazy spool?

This is the query:

SELECT DISTINCT
        SM.Security_ID 'Security_ID',
        Leg.Leg_Type 'Leg_Type',
        Leg.Leg_Side 'Leg_Side',
        Ct.Security_Type AS 'Swap_Type',
        Leg.CDX_Indicator AS 'CDS_CDX_Flag',
        SM.Currency AS 'Notional_Currency',
        Ct.Cross_Currency_Flag AS 'Cross_Currency_Flag',
        Ct.Custom_Overrides AS 'Special_Instructions',
        Leg.Protection_Indicator AS 'Buy_Sell_Protection',
        Leg.Commission_Direction AS 'Commission_Direction',
        Leg.Dividend_Payment_Indicator AS 'Undl_Asset_Dividend_Flag',
        SM.Issue_Date AS 'Effective_Date',
        SM.Maturity_Date AS 'Maturity_Date',
        Leg.Settlement_Frequency 'Settlement_Frequency',
        Leg.Reset_Frequency 'Reset_Frequency',
        Leg.Roll_Day AS 'Roll_Day',
        Leg.Reset_Business_Day_Convention AS 'Reset_Business_Day_Convn',
        Leg.Settlement_Business_Day_Convention AS 'Settlement_Business_Day_Convn',
        Leg.First_Payment_Date AS 'First_Period_End_Date',
        Leg.Day_Count AS 'Day_Count_Basis',
        Leg.Interest_Rate AS 'Interest_Rate',
        Leg.Spread AS 'Spread',
        Leg.CDX_Attachment AS 'CDX_Attachment',
        Leg.CDX_Detachment AS 'CDX_Detachment',
        Leg.Factor AS 'Factor',
        Leg.Commission AS 'Commission',
        Leg.Reset_Lag AS 'Reset_Lag',
        Leg.Initial_Index_Price AS 'Initial_Price',
        Ct.Principal_Exchange_Initial AS 'Principal_Exchange_Initial',
        Ct.Principal_Exchange_Final AS 'Principal_Exchange_Final',
        IsNull(Leg.Delay_Days, 0) AS 'Settlement_Delay_Days',
        Leg.Red_Code AS 'Red_Code',
        Leg.Referenced_Asset AS 'Referenced_Asset',
        SM.Short_Description AS 'Security_Description',
        Leg.Notional_Reset_Type AS 'Notional_Reset_Type',
        Leg.Reset_Arrears_Flag AS 'Reset_Arrears_Flag',
        SM.MIC AS 'Position_Market',
        SM.Currency AS 'Position_Currency',
        1,
        Ct.Security_Id,
        Txn.CLEARED_TRD_INDICATOR
    FROM
        ##AssetAddSwap_Tbl S
        INNER JOIN Sch_Core_Data.Security_Master SM (NOLOCK) ON S.Security_ID = SM.Security_ID
        INNER JOIN Sch_Core_Data.Security_Detail_SwapLeg Leg (NOLOCK) ON SM.Security_Id = Leg.Security_Id
        INNER JOIN Sch_Core_Data.Security_Detail_SwapContract Ct (NOLOCK) ON Leg.Contract_Security_Id = Ct.Security_Id
        LEFT JOIN Sch_Core_Data.VW_TRANSACTIONS_Abbreviated Txn WITH (NOLOCK) ON Leg.Security_ID = Txn.Security_ID
        LEFT JOIN Sch_Core_Data.Security_Alt_Identifier SAI (NOLOCK) ON SAI.Security_Id = Leg.Security_Id
            AND SAI.Identifier_Type = 'APXID' AND SAI.STATUS_FLAG = 'ACT'
    WHERE
        S.Retransmit_Flag = 1
        OR (--Txn.Transaction_ID IS NOT NULL
            --AND Txn.TRANSACTION_SOURCE_SYSTEM <> @TargetSystem
             Txn.TRANSACTION_SOURCE_SYSTEM NOT IN (SELECT svalue from SCH_CORE_DATA.DBL WHERE PROCESS = 'SYSTEMS' AND [FUNCTION] = 'LIST' AND DESCRIPTION3 = 'NON-TOM')
            AND Sch_Core_Code.Udf_Chk_BitValue(Txn.Account_Subscription, 3) = 1
            AND Sch_Core_Code.Udf_Chk_BitValue(Txn.Transmitted, 3) = 0
            AND IsNull(SAI.Identifier, '') = '')

Using this function:

-- =============================================
-- Author:      Anuj Kalra
-- =============================================
CREATE FUNCTION [SCH_CORE_CODE].[UDF_CHK_BITVALUE]
(
@VALUE INT,
@FIND INT
)
RETURNS BIT
WITH EXECUTE AS 'USR_CORE'
AS
BEGIN
    DECLARE @CHECK BIT;
    SET @CHECK = 0;
    IF ((POWER(2,@FIND) & @VALUE)<> 0)
    BEGIN
        SET @CHECK = 1;
    END
    RETURN @CHECK;


texts:
I need to create a trigger in a specific table that already has another trigger.
What I want to do is:

Disable the old trigger
Enable the new trigger (In a new history table)
Populate the values from the previous history table to the new one.

How should I control the transaction in this case to make sure I will not mess up the data with both tables?

I was considering using TABLOCK hint, so it would be something like this:

BEGIN TRAN

    SELECT 1
    FROM  table
       WITH (TABLOCK, HOLDLOCK)

--Disable the old trigger
--Enagle the new trigger
--Populate the data to the new table
COMMIT

Does it make sense?
Another question: Is there a way to automaticly add a column in the history table (trigger table) in case we add a new column in the main table?

Thank you in advance!


texts: Redundant transpose. Case is not enough to solve my problem

texts:
I have db problem in transposing rows to columns. I am half way through the result but getting redundant data. My table:

EMP_ID  EMP_NAME   SAL_PAID
01      ABC        JAN
01      ABC        FEB
01      ABC        MAR
02      PQR        JAN
02      PQR        MAR
03      XYZ        FEB

Result Table:

EMP_ID  EMP_NAME   JAN    FEB    MAR    APR
01      ABC         Y      Y      Y      N
02      PQR         Y      N      Y      N
03      XYZ         N      Y      N      N

I have used case and then the result is as below:

EMP_ID  EMP_NAME   JAN    FEB    MAR    APR
01      ABC         Y      N      N      N
01      ABC         N      Y      N      N
01      ABC         N      N      Y      N
02      PQR         Y      N      N      N
02      PQR         N      N      Y      N
02      PQR         N      N      N      N
03      XYZ         N      Y      N      N
03      XYZ         N      N      N      N

I have tried this for quite a bit now. Thank you.


texts: Does failed task considered to be run to completion in SQL Server maintenance plan terminology?

texts:
The replies to my previous question: "How to specify a SQL Server maintenance plan subtask to run unconditionally on success or failure of preceding tasks?" answered me that I should change "Evaluation operation:" in my maintenance plan from "Success" to "Completion".

Though, I am still in doubt about the term "Completion" and that the proposed solution will completely address my situation.

All tasks are configured over "All user databases" that:

I have not noticed immediately and added my own user (diagnostics, monitoring, testing) database which, then, took offline;
I am not permitted to change the maintenance plans without previous authorization from company management

So, the nightly databases backup (penultimate) task in screenshot above failed.
There is no diagnostics or logging except the fact that all tasks failed.

I corrected the situation by detaching the culprit offline database. So, after that, maintenance plan job started to complete successfully (with backup task).

But I could not understand:
if I leave a user database offline and all maintenance plan tasks fail to run due to this. Do the tasks considered coming to "completion"?

Some doubts remain because I made other changes which I am sure could not prevent maintenance pla completion. But who knows...

UPDATE:
As I could see by the duration of maintenance plan job in comparison to the successful job runs, the the tasks were not even started because their corresponding previous task did not start and they did not start because probably failed to find "All user databases".

So, the definition of:

  Completion requires only that the precedence executable has completed,
  without regard to outcome, in order for the constrained executable to
  run.

IMO. does not assure the previous task completion because it could have been simply not being started and run at all


texts: How to trim first digit off an integer value?

texts:
i have data like Column1, i want to convert the Column1 data as Column2.
Can any one suggest to me how i can achieve this in SQL?

Column 1    Column2
1000001         1
1000002         2
1000003         3
1000004         4
1000005         5


texts: Oracle RMAN Incremental update or merge questions

texts:
1- Does Oracle RMAN support Incremental Update/Merge option for device type SBT ? Or its only supported for Disk ?

2 - During an update/merge operation during the incremental backup, does RMAN write directly into the image copy by updating the changed blocks ?

Thanks in advance


texts: Database mirroring on Rackspace or Xeround

texts:
I´m trying to resolve following problem.

I have 2 application servers on Rackspace that are connected to a load balancer. Both servers must have the same applications and share the same data. Basically on each server is a Java EE application that will be writing some data into database. This data will be accessible from outside. Each server must have the same data (so he has to take them from the other servers database and viceversa).

This fact means that I need to have 2 database servers (so that one database server is not so busy if we have big traffic). To have the same data on both database servers, I was thinking of mirroring one to another (Master - Master connection). I found a Xeround database cloud, and I was thinking of creating two database instances there and accesing them from Rackspace. (Server A -> Database server A; Server B -> Database server B). And Database server A <-> Database server B would have Master - Master connection (so both servers will have the same data periodically updated).

Is this a possible and effective solution ? Or is there a better solution?


texts: Dividing values from a single column into 2 based on a rule

texts:
I am trying to create an SQL query to divide into 2 separate columns based on a rule. My table is as follows

Name   | Item ID  | Parent ID
-----  | -------- | ----------
A      | 1        | Null
B      | 2        | Null
C      | 3        | Null
D      | 4        | Null
A1     | 5        | 1
A2     | 6        | 1
A3     | 7        | 1
B1     | 8        | 2
B2     | 9        | 2
B3     | 10       | 2

Is it possible to have the values in the [Name] column be divided into 2 columns?

e.g. if the value of the [Parent ID] is Null, the value in [Name] column be in column 1 and if the value of [Parent ID] is 1, it would be placed in column 2?

Please advice and thanks in advance!


texts: why can I only install MySQL 5.1 on cent OS 6.2?

texts:
I had Ubuntu 10.10 on my server and MySQL 5.1 version. But since i wanted to update the system, I installed a fresh version of Cent OS 6 only so I could install the latest version of MySQL. But after installing cent OS 6 I am trying to install Mysql now, and it shows Mysql 5.1 as the only available package.

Please help


texts: How is an Active Log different from MinLSN?

texts:
MinLSN is the LSN of the oldest uncommitted transaction:

What exactly does Active Log hold?
Isn't it supposed to hold all the uncommitted transactions?
Does it hold any other log records which are already committed?
What happens to MinLSN and Active Log when a CHECKPOINT happens?


texts: Change SQL Server 2000 from Active/Active to Active/Passive

texts:
I know it's cringe-worthy to use this term, but I have a SQL Server 2000 cluster with one clusetered instance running on it with multiple resources running on one of the two servers in the cluster all the time.

For legacy licensing purposes, we'd like to change this cluster from "active/active" to "active/passive" to save on two Enterprise licenses - they only run on one server anyways.

Microsoft Premier support politely told to me to fly a kite.  Does anyone have any links or resources on how to do this?
Any help would be appreciated

From a licensing perspective, I don't want to pay for 4 Enterprise SQL Server licenses. I want everything running on one node at a time so I only have to pay for 2 Enterprise SQL Server licenses.


texts: Installing Full Text Search on SQL Server 2008 R2 Express

texts:
I can't for the life of me install full text search on my SQL Server 2008 R2 instance.

Every installer I have tried shows me this:

Feature selection only shows Replication under features http://www.vme2-server.co.uk/images/sql.jpg

I've tried:

SQLEXPRADV_x64_ENU.exe
SQLEXPRWT_x64_ENU.exe
SQLEXPR_x64_ENU.exe

Any ideas what i'm doing wrong?

The SQL Server is reporting a version of 10.52.4000.0

Thanks in advance,

Dave


texts:
Today I had a problem with the SQL Server. The SQL Server has crashed and I can't connect to it. When I try to restart the SQL Server it remained in the STOP_Pending state for 30 minutes, then I killed the process.
I have this error message in the error log. I can't find what the error means. Does anyone have any idea?
Thanks
2021-04-12 08:02:07.54 spid755     Unable to create stack dump file due to stack shortage (Location:     scheduler.cpp:2090
Expression:  !pWorker->WorkerQueueElem::IsInList ()
SPID:        755
Process ID:  2336)
2021-04-12 08:02:07.54 spid755     Stack Signature for the dump is 0x0000000000000000
2021-04-12 08:02:08.20 spid755     External dump process not executed.

2021-04-12 08:02:08.20 spid755     DoMiniDump () encountered error (0xC00000FD)
2021-04-12 08:02:08.20 spid755     Error: 17066, severity: 16, state: 1.
2021-04-12 08:02:08.20 spid755     SQL Server Assertion: File: <scheduler.cpp>, line=2090 Failed Assertion = '!pWorker->WorkerQueueElem::IsInList ()'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
2021-04-12 08:02:32.01 Server      Using 'dbghelp.dll' version '4.0.5'
2021-04-12 08:02:32.01 Server      **Dump thread - spid = 0, EC = 0x0000000000000000
2021-04-12 08:02:32.01 Server      ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\LOG\SQLDump0009.txt
2021-04-12 08:02:32.01 Server      * *******************************************************************************
2021-04-12 08:02:32.01 Server      *
2021-04-12 08:02:32.01 Server      * BEGIN STACK DUMP:
2021-04-12 08:02:32.01 Server      *   04/12/21 08:02:32 spid 9804
2021-04-12 08:02:32.01 Server      *
2021-04-12 08:02:32.01 Server      * Stalled IOCP Listener
2021-04-12 08:02:32.01 Server      *
2021-04-12 08:02:32.01 Server      * *******************************************************************************
2021-04-12 08:02:32.01 Server      * -------------------------------------------------------------------------------
2021-04-12 08:02:32.01 Server      * Short Stack Dump


texts: Receive strange error during mysql auto-repair check optimize

texts:
I am running the command:

/usr/bin/mysqlcheck -hlocalhost -uDATABASE_NAME -pPASSWORD --auto-repair --check --optimize DATABASE_NAME

And up until this past week, this command has always run just fine. Last week however, I started receiving the error:

Error : Table 'DATABASE_NAME.H1#~i' doesn't exist
status : Operation failed

This is not a table I created or recognize. I have no idea why the error would suddenly start occurring. Has anyone seen this or have any idea what might be causing this?


texts: Setting up database structure for Students, Managers, and Administrators

texts:
I'm trying to figure out how to set up the structure for users.

Users can have multiple Managers and Managers can manage multiple Business Units. Administrators have full control of the system.

I'm trying to figure out if I should separate the Users and Managers from the User table and have a table for Administrators and Managers or separate all three.

The Managers have multiple Business Units which a User can be managed by.

Total amount of users could be estimated about 1,000. What do you think the best approach to this?


texts: Balancer is down after stopping config servers

texts:
I had an urgent issue to solve in admin DB

I stopped all config servers, and made changes in the primary one.
After restarting all other servers - balancer working only on the server where I did changes, the other two have:

balancer:
        Currently enabled:  yes
        Currently running:  no

Also rs.status() returns:

configsvr> rs.status()
{
        "info" : "configsvr",
        "ok" : 0,
        "errmsg" : "not running with --replSet",
        "code" : 76
}

Need some help to fix it
Should I restart all config servers with some options, or need to build replication from the beginning?


texts: How to set up a user who doesn't have `admin` database access but use it as authentication database?

texts:
I want to setup a user in MongoDB. This user will not have admin database access. But it uses admin as authentication database. It will fail to connect the mongoDB by this command mongo --host localhost admin. Instead, it can use this command to connect to test database: mongo --host localhost --authenticationDatabases admin test.  How can I restrict the permission in this case?

I tried below command to create an user:

db.createUser({user: 'testUser', pwd: '123456', roles: [{role:'readWrite', db: 'SampleCollections'}]})

when I use that user account to login mongo shell, I am able to list the collections under admin database. How can I restrict the user only on SampleCollections database not admin?


texts: How to configure TCPS in JDBC thin client for oracle

texts:
An Java Application running with JDBC thin wtih TCP connection, Now require is to convert in TCPS for encryption transmission from application to database.
String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=servername)(PORT=XXXX))(CONNECT_DATA=(SERVICE_NAME=XXXX)))");

with using JKS truststore for certificate;
Here we havn't using sqlnet.ora file for setup TCPS encryption. Can anyone help for same that what are changes require in JDBC,Ciper, certificate etc ?


texts: Create new table values based on partial column value - access

texts:
I am trying to remove a ton of unnecessary rows in this table.

Currently there are 1.3m of them which are all an individual transaction id - with the country and destination assigned for each which is wayy wrong, given that the actual flight id is contained within the transaction id string.

The worst thing in the dataset which I've inherited, is the fact that within the transactions table there is a column with the extracted flight id as if it were a foreign key, but above is the 'flights' table. 'eyeroll'

My question is, how do I extract just the part which I have coloured in red? I can't use Left, Mid or Right, because they are variable lengths, some with 3 and 4 digits in the flight id itsself.

Is there a regex or something which can extract the text situated between 2 sets of underscores? which is just about the only viable rule in this sense.

Please bare in mind that I have never used regex before, and would need advice on how to format the expression in my query design view.

Any help is appreciated.


texts: SQL Calculation based on 'similar' rows within table

texts:
I'm using SQL Server 2008 R2, building a fact table for SSAS.

I can therefore accept solutions to this via either SQL query to SSIS task.

Let's say I have a fact table that looks a little like this, its a record of when people have eaten cakes, and how many (for illustrative purposes).

Surrogate Key | CakeType | PersonKey | CakesEaten | Date     | HadSeconds
1               20         1234        4            20120412   0
2               41         2345        3            20120401   0
3               25         3456        4            20120408   0
4               39         2345        2            20120402   0
5               20         4567        3            20120409   0
6               89         9876        2            20120405   0
7               31         1579        4            20120408   0
8               29         2709        3            20120402   0
9               27         3456        3            20120410   0
10              26         2345        2            20120408   0

CakeType and PersonKey and Date are all foreign keys to dimensions.

CakesEaten and HadSeconds are measures.

Techie type question: What I want to do, is to mark a fact as 'HadSeconds' where a Person has a record in the table within 2 days of the previous fact.

Business type question: Show me the instances of people going to eat cake when they've already had some cake in the previous 2 days.

For example, Person '2345' has 3 records in this fact table, IDs 2, 4, 10.

I want to mark 'HadSeconds' for ID '4' as a '1', because the Date for ID 4 is within 2 days of ID2, therefore the person ate cake in quick succession, and this instance needs to be marked as 'HadSeconds'. ID '10' has a datestamp which is 6 days after ID '4', therefore the 'HadSeconds' column for ID 10 does not need to be marked.

Note that only records were a Person has multiple entries in the fact need to be considered.

The best (lol) thing? This fact table has approx. 60 million records in it.

What's the most efficient way to achieve this? Breaking stuff down into temporary tables? Sorting? Cursors (ew...), Aggregration tasks in SSIS...


texts: Oracle12c R2 on Windows 10 starts only one of two instances. Manual start results in LRM-00109

texts:
I have installed Oracle 12c R2 on my local Windows 10 (64 Bit) machine. For the installation I choose only to install the software.

After the installation I have used the tool that comes with the installation to create databases. After the first database was created and started I was able to connect to it without any problems.

But after I created a second database and restarted the machine only the newly created database was started.

The first database is not started on boot and I cannot start it using sqlplus. I get the error

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: Parameterdatei 'D:\ORACLE\PRODUCT\12.2.0\DBHOME_1\DATABASE\INITDBNAME.ORA' konnte nicht ge┐ffnet werden

The said file does in fact not exist. But such a file does also not exist for the other database. The directory contains a SPFILE<DBNAME>.ORA though for both databases. As far as I know oracle uses this pattern SPFILE<DBNAME>.ORA when starting the database. If cant find said file it goes on to use INIT<DBNAME>.ORA.

I have created a INITDBNAME.ORA file and added the content:

SPFILE = 'D:\SPFILEDBNAME.ORA'

and tryed to start the database using startup pfile = 'D:\INITDBNAME.ORA'
but the result was a

ORA-00205: Fehler beim Identifizieren der Kontrolldatei

error. Also confusing was that if the pfile was inside the Oracle Directory the command failed saying it cannot access the file.

EDIT
After I have deleted both instances, rebooted and created both instances again I can currently access them both.

When I try to login to the first instance I get the following error:

ERROR:
ORA-01116: Fehler beim Íffnen der Datenbankdatei 5
ORA-01110: Datendatei 5:
'D:\ORACLE\ORADATA\INSTANCE\DATAFILE\O1_MF_UNDOTBS1_DPKMRD6L_.DBF'
ORA-27041: Datei kann nicht ge÷ffnet werden
OSD-04002: Datei kann nicht ge÷ffnet werden
O/S-Error: (OS 5) Zugriff verweigert

EDIT
The Mirosoft Service Manager reports both inscantec as runngin.

Setting ORACLE_SID does not make any difference. After the installation of both instances I can connect via

sqlplus sys@instance1 as sysdba
or
sqlplus sys@instance2 as sysdba

But this goes well only for a short time. After couple of minutes it starts to throw ORA-01110 when I connect to the first instance.

The data files are not missing. They are in the location in which Oracle reports them missing.


texts: Innodb: When bulk inserting or updating - how to disable transactions?

texts:
When doing a mass insert like INSERT INTO SELECT FROM the speed benefit of doing that in chunks of 10k records can be enormous, in my tests a query that was running for 5 days took only 2 hours using the chunk method.

To make things worse: if you let such an operation run for a few hours and KILL the job (or crash) then it can take days or weeks until ROLLBACK is finished.
Rollback is significantly slower than insert, no optimizations here.

The same counts for doing mass UPDATEs.

My question:
How can I completely disable transaction log for such a transactions ?

I tested this:    SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
But it showed no difference, when killing the job transactions start rolling back.

I really did my homework and tried to find anything about it, no luck.

P.S.
Of course I am aware that importing without transactions does not give a unique snapshot to rely on if anything is changed in meantime.


texts: Connection to Amazon RDS MySQL instance fails with `init_connect command failed`

texts:
I'm attempting to open a connection from my Amazon RDS instance to my local dev machine. I added my IP to the security policy in Amazon's panel (read: whitelisted myself in the firewall) and added my user in MySQL:

grant usage on bar.* to foo@0.0.0.0 identified by "foobar";
grant all privileges on bar.* to foo@0.0.0.0;

Assume that 0.0.0.0 is actually my IP.

I then attempt to connect via the command-line MySQL client:

[wk@batman ~]$ mysql  --host=1.1.1.1 --user=foo -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 66287753
Server version: 5.6.17-log

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    66287829
Current database: *** NONE ***

ERROR 1184 (08S01): Aborted connection 66287829 to db: 'unconnected' user: 'foo' host: 0.0.0.0 (init_connect command failed)

I'm not sure what could be causing this error - the results I could find on Google pertained to database replication. Does anyone have any idea what's wrong?


texts: Is there an easy way to debug and find out why a query runs slow?

texts:
I'm running onto a weird issue.

We've got two db servers, one is development and the other one is production.

Both have same specs.

VMs (Hyper-V)
Windows Server 2008 R2 64 Bit
SQL Server 2008 R2 64 Bit

If I run the exact same query on our dev server it takes 10 seconds to give me 10 results. The same query in the production server takes 1:30 minutes sometimes more.

Is there an easy way to find the source of the problem? Is it something related to indexes, workload?

This is just one example, the queries are dynamically generated, this is used by our web application and it generates queries on an advanced search menu

EDIT Added Query

SELECT Client_Id, Project_Id, Person_Id, Membership_No, Family_Name,
       First_Name, Password, Company, Country_Name, EMail, Member_Type,
       Member_Type_Description, Member_Status, Member_Status_Description,
       Category, Member_Category_Description, Balance, Date_Registered
FROM uvw_Members_Display
WHERE Client_Id='EHA'
  AND Project_Id='EHA'
  AND ( Client_Id='EHA'
    AND Project_Id='EHA'
    AND (  (( uvw_Members_Display.Member_Type LIKE '01' ))
        OR ( uvw_Members_Display.Member_Type LIKE '02' )
        OR ( uvw_Members_Display.Member_Type LIKE '03' )
        OR ( uvw_Members_Display.Member_Type LIKE '04' )
        )
    AND ( uvw_Members_Display.Member_Status LIKE 'I' )
    AND ( Balance =0 )
    AND ( uvw_Members_Display.Category LIKE '02' )
    AND Date_Registered IS NOT NULL
      )
ORDER BY Person_Id DESC


texts: sort a varchar field numerically in mysql

texts:
I have the varchar field named _tid, normally which contains numerical field, but it is also possible that may be contain string field. Now i wanted to to sort the column by numerically rather than lexicographically.
Here the query i've used,

mysql> select _tid,_name from teacher order by _tid;

which returns the following row set.

 +------+----------------------+
    | _tid | _name                |
    +------+----------------------+
    | 1    | A.MANIVANNAN         |
    | 10   | M.ELUMALAI           |
    | 100  | SAMPATH.R            |
    | 101  | S.PAULRAJ            |
    | 102  | A.ASHOK KUMAR        |
    | 103  | S.JAYAKUMAR          |
    | 104  | S.CINRAS             |
    | 105  | P.MURUGAN            |
    | 106  | S.VIJAY              |
    | 107  | N.KARTHIKEYAN        |
    | 108  | G.BALAKRISHNAN       |
    | 109  | C.THARANI            |
    | 11   | M.PONNUSAMY          |
    | 110  | J.KANNAN             |
    | 111  | V.MAHENDRAN          |

But i want row set like the following,

+------+----------------------+
| _tid | _name                |
+------+----------------------+
| 1    | A.MANIVANNAN         |
| 2    | A.PONNIVALAVAN       |
| 3    | B.MUTHU              |
| 6    | R.Kumar              |
| 4    | P.RAJAKALI           |
| 5    | N.Shaunmuga Sundram  |
| 8    | M.Balaji             |
| 7    | V.PALANI             |
| 9    | J.RANJITH            |
| 10   | M.ELUMALAI           |
| 11   | M.PONNUSAMY          |


texts:
I have a SQL Server database and the .MDF file is almost 9GB in size. However, the data and indexes only seem to be taking up a little over 1GB. I've shrunk the database (with and without reorganization) and I've shrunk the database files.

What's next? How do I find what is taking up so much space?


texts: SQL Server: Collation of the resource database

texts:
I'm trying to use stopwords so that some words are skipped by the Full-Text Indexer. I can add those stopwords in the table sys.fulltext_stopwords.

When trying to get a list of stopwords and system-stopwords in SQL Server 2012 an error encountered. I'm executing the following (simplified) query:

SELECT sys.fulltext_stopwords.stopword
FROM sys.fulltext_stopwords

UNION

SELECT sys.fulltext_system_stopwords.stopword
FROM sys.fulltext_system_stopwords;

The error message I get is:

  "Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_BIN" in the UNION operation."

The database collation for the first SELECT statement in my query is Latin1_General_CI_AS. The same is true for the master, model, msdb and tempdb databases.

Where does the Latin1_General_BIN collation come from? It looks like the sys.fulltext_system_stopwords table has a different collation, but why?

EDIT:

I can 'solve' my error by using COLLATE in my query, like this:

SELECT  sys.fulltext_stopwords.stopword COLLATE DATABASE_DEFAULT
FROM sys.fulltext_stopwords

UNION

SELECT sys.fulltext_system_stopwords.stopword COLLATE DATABASE_DEFAULT
FROM sys.fulltext_system_stopwords

I see that the system-stopwords are stored in the resource database, which can explain the difference in collation. The next question would be: why is the collation from the resource database different that the default?


texts:
I currently have the following constraint on a table:
ALTER TABLE myTable
            ADD CONSTRAINT unique_row UNIQUE (content_id, brand_id, language_id);

I want to add a condition to the constraint:  I want the constraint to work the same way, but with only a single row where is_archived is FALSE:
ALTER TABLE myTable
         ADD CONSTRAINT unique_row UNIQUE (content_id, brand_id, language_id, !is_archived);

However, when I do this I get a syntax error.
If is_archived = true then multiple rows with the same combination of content_id and brand_id are allowed.   Basically multiple rows that are the same can be archived, but only one can be unarchived.


texts: Failed Login on Registered Server When Opening Connection

texts:
I have a number of servers registered in the Registered Servers pane in SQL Server Management Studio.

When I right click a server group and select "New Query", a window opens in which I can execute a command against all the servers in that group.

Every time I do this, one of the servers (the same one each time) registers a failed login (We have Email alerts set up for failed logins) and gives the following error description

  DESCRIPTION:  Login failed for user 'MyUser'. Reason: Failed to open
  the explicitly specified database '<multiple>'. [CLIENT:
  XXX.XXX.XXX.XXX]

however, I can then run a query in the window and it executes against all servers including the one throwing the login error.

I thought there was maybe a configuration option on this server that was different to all the others but having compared sys.configurations between the servers I can see the only config that is out is the max server memory (MB) and affinity mask.

What could be causing this issue?


texts: Postgresql trigger update whole table after delete

texts:
I have a table "measurements" which looks like this (with empty cells in the last col)

ser_nr | meas_ser | meas_value | last_meas
-------+----------+------------+-----------
  A1   |    1     |    12      |  (False)
  A1   |    2     |    5.5     |  (False)
  A1   |    3     |    5.12    |  (False)
  A1   |    4     |    5.01    |  (True)
  B1   |    1     |    2       |  (False)
  B1   |    2     |    2.03    |  (True)

http://sqlfiddle.com/#!15/bcd6b (Link for create table etc.)

I need this to query "select ... where last_meas=True"

I wrote a trigger for "after insert" and this works for the newly inserted rows. But what can I do to update the whole table?

For example: I want to delete the A1 row with meas_value "5.01". There has to be a function which executes for the whole remaining table and updates the last_meas column (in this example the third row should be True now). I can trigger a function using "after delete for each row" but using this method i can only access all deleted rows.

I found a workaround by generating a view using

SELECT * FROM measurements AS m LEFT OUTER JOIN (SELECT m.ser_nr,
    max(m.meas_ser) AS meas_ser, TRUE AS last_meas FROM measurements AS
    m GROUP BY m.ser_nr ) AS last_ms USING (ser_nr, meas_ser);

but this only generates "True" and not "False".

Is there a more easy way doing this?


texts:
I have a database dedicated server with one quad-core cpu and 48GB Memory.

Total database size is about 6GB and my ultimate goal is to get the maximum performance out of this system.

At First, to get maximum performance I set innodb_buffer_pool_size to '35GB' with innodb_buffer_pool_instance to 5, thinking that each instance will take up 7GB so total 35GB will be optimum.

But after several days, Innodb_buffer_pool_pages_free says I had 26GB free space in the buffer pool.

So I increased the instances number to 10 and waited some weeks, but the Innodb_buffer_pool_pages_free says about the same thing.

Was my calculation(expecting that 7GB * 5 instances would take up 35GB of buffer pool) wrong? Then how can I use all the free buffer pool size? Should I increase the buffer pool instances even more? Or run 2 mysqld processes to get replication or something?

If anyone get any clue, please give some hint to get the point right about my issue.


texts:
Can we expand the disks in Azure VM without stopping the VM itself. (online)

We are looking for an online method to expand the disk which we are not able to crack. We have certain apps moving to IAAS which at times require disk expansion.


texts:
How do I peer to a new domain? I'm using NuoDB on MacOS
http://dev.nuodb.com/download-nuodb/request/download


texts:
I can't find a good way to rewrite this query or improve the performance:
query:
update company_holding c
set issuer_stim_buy_quarter = sq.estim_quarter_buy
from (select h.company_id
            ,h.issuer_cusip
            ,var_quarter as quarter
            ,min(h.quarter) as estim_quarter_buy
      from company_holding h
      where h.quarter <= var_quarter
        and h.issuer_tot_share > 0
      group by h.company_id, quarter, h.issuer_cusip
     ) sq
where c.company_id = sq.company_id
  and c.issuer_cusip = sq.issuer_cusip
  and c.quarter = sq.quarter

company_holding is a partitioned table.
This is the explain analyze: https://explain.depesz.com/s/5rjU


texts: how to find out how long the last DBCC checkdb took to complete?

texts:
In most of my servers I have dbcc checkdb scheduled to run.

I can get the last known good DBCC Checkdb through DBCC DBINFO().

I can read the tracks left on the sql server log and I get something like the picture below:

Or I can run the following script:

IF OBJECT_ID('TEMPDB..#MAINT') IS NOT NULL
   DROP TABLE #maint

CREATE TABLE #maint (Results varchar(255) NULL)
INSERT INTO #maint(Results) EXEC('master..xp_cmdshell ''sqlcmd -E -Q"dbcc checkdb([my_database])"''')

select * from #maint

and get something like this:

I could also have a look at the default trace.

what am I after?

How to find out how long did it take to run dbcc checkdb the last time.


texts: How to INNER JOIN and OUTER JOIN within the same table?

texts:
I am using PostgreSQL and I have the following tables:

Product

ID | Name | Price

Receipts

ID | Month | Quantity | Product_ID (FK)

Sales

ID | Month | Quantity | Product_ID (FK)

I am trying to come up with a query that displays something like this:

    Month | Product.Name | Amount received | Amount sold

It is very important to keep in mind that there can be more than one receipt or sale of the same product on the same month. I need to come up with a query that tells me the amount received and the amount sold of a particular product on a each month.  It is possible that a product is not sold in a month (that's why the OUTER JOIN below).

The best that I've got so far is this:

    SELECT s.month, p.name, r.quantity, SUM(s.quantity)
    FROM product AS p
    INNER JOIN receipts AS r
       ON p.id_product = r.product_id
    LEFT OUTER JOIN sales AS s
       ON p.id_product = v.product_id
    GROUP BY s.month, p.name, r.quantity

Let's say in the same month there were 2 receipts of keyboards. The first one with 100 keyboards and the second one with 50 keyboards. Also, there was a sale of 10 keyboards and then another one of 20 keyboards.

An example of what is returned by this query:

     Month | Product.Name | Amount received | Amount sold
       6   |   Keyboard   |       100       |    30
       6   |   Keyboard   |       50        |    30

This one works fine when there are mutiple sales (apart from the repetition) but not when there are multiple receipts. I was thinking that I should relate s.month and r.month with an INNER JOIN, but I don't know how.

Sorry for the bunch of information at once. If there is anything unclear, let me know.
Can anyone help me out?

Thanks!


texts: MySQL query to evaluate ranges in date and time elements of a datetime column separately


texts:
I have two postgres server with a master-slave architecture, the slave is replicating the DB from the master.

In case the master goes down, the slave takes over and becomes the master.

Now, the slave has some updated data, which I want to replicate to the master, once it boots back up. How do I achieve that?


texts:
I have a query which runs little slower.

SELECT b.BreakdownClassificationId,
       k.IsinCode,
       k.SedolCode,
       ClassificationDate,
       NAME,
       InstrumentType,
       GeographicalLocation,
       CapSize,
       Currency,
       ExchangeName,
       HoldingDomicile,
       MaturityDate,
       Sector,
       MajorSector
FROM   #BreakdownSet b
       OUTER apply (SELECT TOP 1 IsinCode,
                                 SedolCode,
                                 ClassificationDate,
                                 NAME,
                                 InstrumentType,
                                 GeographicalLocation,
                                 CapSize CapSize,
                                 Currency,
                                 ExchangeName,
                                 HoldingDomicile,
                                 MaturityDate,
                                 Sector,
                                 MajorSector
                    FROM   dbfinex.dbo.PfPortfolioHoldingClassificationFtid x WITH (nolock)
                    WHERE  ( x.isincode > ''
                             AND x.isincode = b.breakdowncode )
                            OR ( x.sedolcode > ''
                                 AND x.sedolcode = b.breakdowncode )
                            OR ( x.sedolcode > ''
                                 AND x.sedolcode = b.sedolcode )
                            OR ( x.isincode > ''
                                 AND x.isincode = b.isincode )
                    ORDER  BY CASE
                                WHEN x.sedolcode = b.breakdowncode THEN 1
                                WHEN x.isincode = b.breakdowncode THEN 2
                                WHEN x.sedolcode = b.sedolcode THEN 3
                                WHEN x.isincode = b.isincode THEN 4
                                ELSE 5
                              END,
                              classificationdate DESC) k

Execution Plan

Order By inside the Cross Apply is very costly, is there any better way to write this query ?


texts:
I have a newly installed MongoDB server running on an AWS Ubuntu EC2.
The server has nothing else installed on it.
The DB currently contains 35 documents of 3 MB each, meaning less than 110 MB of data.
I'm running an aggregation query that contains 2 stages: a simple $match stage that makes sure that only 31 documents are aggregated, and a $group stage with $accumulator inside (that stage doesn't allocate much new space, and even if it did, I expect that when handling with 3 MB documents it won't be much more). The query's result should be a single 3 MB documents that is a merger of all of the above.
Running the query when only 3 documents exist - finishes without a problem. But with 35 - I consistently get the following error:
MongoError: Out of memory
    at MessageStream.messageHandler (/app/node_modules/mongodb/lib/cmap/connection.js:268:20)
    at MessageStream.emit (events.js:314:20)
    at processIncomingData (/app/node_modules/mongodb/lib/cmap/message_stream.js:144:12)
    at MessageStream._write (/app/node_modules/mongodb/lib/cmap/message_stream.js:42:5)
    at writeOrBuffer (_stream_writable.js:352:12)
    at MessageStream.Writable.write (_stream_writable.js:303:10)
    at Socket.ondata (_stream_readable.js:717:22)
    at Socket.emit (events.js:314:20)
    at addChunk (_stream_readable.js:307:12)
    at readableAddChunk (_stream_readable.js:282:9)

/etc/mongod.conf:
# mongod.conf

# for documentation of all options, see:
#   http://docs.mongodb.org/manual/reference/configuration-options/

# Where and how to store data.
storage:
  dbPath: /var/lib/mongodb
  journal:
    enabled: true
#  engine:
#  mmapv1:
#  wiredTiger:

# where to write logging data.
systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log

# network interfaces
net:
  port: 27017
  bindIp: 127.0.0.1,<IP>

# how the process runs
processManagement:
  timeZoneInfo: /usr/share/zoneinfo

#security:
security:
    authorization: "enabled"

#operationProfiling:

#replication:

#sharding:

## Enterprise-Only Options:

#auditLog:

#snmp:

I've read a lot online and couldn't solve it, I've tried:

Upgrading the server's hardware to t2.large (8 GB RAM).
Adding allowDiskUse to the query:

const options = {
  allowDiskUse: true,
  // explain: true,
}
mongoConn.db(dbConfig.database).collection(collection).aggregate(aggregationPipeline, options).toArray((err: MongoError, result: any) => {

With htop, I can see the total memory usage of 210M/7.68G after restarting MongoDB, and during the query it climbs to a peak of 691M/7.68G, fails, and remains on 627M/7.68G afterward.

With db.enableFreeMonitoring() I can see a constant 2 GB virtual memory usage, with peaks to 2.1 GB:

The result of db.serverStatus().tcmalloc.tcmalloc.formattedString:

The result of db.serverStatus().mem:

Summary: I know that MongoDB has a 100MB memory limit, but I guess that it shouldn't reach it with 3 MB documents, and allowDiskUse. What am I missing here?


texts: Query gets slow when bind peeking is turned on

texts:
There is an old database that was upgraded from Oracle 10 to 11G R1. In Oracle 10 we had disabled bind peeking. Since Oracle 11 has adaptive cursor sharing, we want to turn bind peeking back on. When we did this we found that many queries went much much faster. Unfortunately, one critical query got very slow. As soon as we turn off bind peeking, the one query gets fast again but everything else goes back to being sluggish.

The question is: In Oracle 11, what would cause bind peeking to make a query slow? I thought adaptive cursor sharing was supposed to take care of the bad bind variable peeks problem.


texts:
To reload the configuration files, we send the SIGHUP signal to the
  postmaster, which then passes that on to all connected backends.
  That's why some people call reloading the server "sigh-up-ing".

  As reloading the configuration file is achieved by sending the SIGHUP
  signal, we can reload the configuration file just for a single
  backend using the kill command.

  First, find out the pid of the backend using pg_stat_activity. Then,
  from the OS prompt, issue the following:

kill  -SIGHUP pid

I dont understand bold words. Because we have many PIDs for backend  and  if we kill a PID, how can it get changes from reload configuration file (postgresql.conf)?


texts:
I have a datetime column with a time portion of 21:45; I need to convert it to 21.75. I tried:

CONVERT(VARCHAR(5), DATE,108)

This converts it to 21:45, now the client wants this be 21.75.


texts: Multiple Distributors in SQL Server Replication

texts:
Per this article, a Publisher can only have one remote Distributor in doing SQL Server Transactional Replication.

  Each Publisher can be assigned to only a single Distributor instance, but multiple publishers can share a Distributor.

If this is the case, I am wondering how to scale out the publication of a database to a large number of Subscribers. Supposing a Publisher had 200 Subscribers with a large number of transactions, what would be a workable distribution architecture that could handle that load?


texts:
I have two nodes with MSSQL2012 which work with AlwaysOn High Availability

After some crash I had to remove db from availability group on the one of the nodes. For restoration I've made backup (full+log) of db on the second node and restored it on the crashed one according to standard procedure. Seemed totally ok, but I've faced that when I give primary role to restored node then  the application service (which uses Service Broker) doesn't do anything.

sys.databases said that for my db is_broker_enabled=1, but I've found several articles about broker on restored db must explicitly be enabled.

SET ENABLE_BROKER failed due to AlwaysOn.

Internet says that the only way to activate service broker is to restore db again with ENABLE_BROKER option. But for adding db to AlwaysOn I must restore it with NORECOVERY.

Trying to run

RESTORE DATABASE [dbname]
FROM  DISK = N'D:\111.bak'
WITH  NORECOVERY, ENABLE_BROKER

I've seen notification about conflict between these two WITH options

Does anybody know correct way to make such restore? db is over 150GB and it always takes over two hours to check each versions:)

thanks in advance


texts:
I am trying to optimize this query :
SELECT sum(a-alias.s_count) AS count
FROM a-table a-alias
WHERE
(
    a-alias.created_at_minute_id >=
    (floor(extract(epoch from(
        select select_to_time
        from a_run_table
        where timeunit = 'hour'
        and status = 'done'
        order by select_to_time desc limit 1)) / 60))
AND(a-alias.s_id = 'some-uuid-number'));

This gives me the sum of s_count and the result is a number like 29087. This query takes 60 milliseconds.
I found that if I replace:
(floor(extract(epoch from(
    select select_to_time
    from a_run_table
    where timeunit = 'hour'
    and status = 'done'
    order by select_to_time desc limit 1)) / 60))

...with some constant number like say 246589, the query time reduces to 1.2 milliseconds.
I moved this part to the With clause, but I am not able to make it work:
WITH last_run AS
(
    SELECT (floor(extract(epoch from(
        select select_to_time
        from a_run_table
        where timeunit = 'hour'
        and status = 'done'
        order by select_to_time desc limit 1)) / 60)) as 'time'
)
SELECT sum(a-alias.s_count) AS count
FROM a-table a-alias
WHERE (a-alias.created_at_minute_id >= (select time from last_run)
AND(a-alias.s_id = 'some-uuid-number'));

I am not able to get value from last_run using (select time from last_run).
I expect a number from the sum function. From floor query, I want to get a number which would be used for >= in Where clause.
Other information
Running query gives :
WITH last_run AS (SELECT (floor(extract(epoch from(select select_to_time
from a_run_table where timeunit = 'hour' and status = 'done'
order by select_to_time desc limit 1)) / 60)) as time) select time from last_run;

Time:
+-------------+
|        time |
|-------------|
| 2.42925e+07 |
+-------------+

Also:
pgcli --version
Version: 0.20.1

Structure of a_run_table :
| id               | uuid                        |  not null   |
| status           | character varying(50)       |  not null   |
| select_to_time   | timestamp without time zone |  not null   |
| timeunit         | character varying(20)

How should I write my query so that I can take value dynamically from outer-query and send it to inner query?


texts:
I am looking to learn SQL server - specifically T-SQL programming, writing stored procedures etc and getting familiar.

I had a look at the MS site to download SQL Server express, however it gives me the below choices. Can someone please advise which should be the correct option considering my learning goal ?

SQL Server Express with Tools (with LocalDB, Includes the database
engine and SQL Server Management Studio Express)
SQL Server Management Studio Express (Tools only)
SQL Server Express LocalDB (MSI installer)
SQL Server Express with Advanced Services (contains the database
engine, Express Tools, Reporting Services, and Full Text Search)
SQL Server Express (Containing only the database engine)

These are present on below site -

http://www.microsoft.com/sqlserver/en/us/editions/2012-editions/express.aspx


texts: PostgreSQL won't allow remote connections

texts:
My Postgres installation won't allow me to connect remotely.
I set the listen_addresses field in the postgresql.conf to '*' and added the line

host    all             all             0.0.0.0/0               md5

to pg_hba.conf, restarted the database and tried

psql dbname uname -h 127.0.0.1

I always get

  Password authentication failed for user "uname"

(also when just setting

host    all             all             127.0.0.1/32               md5

in hba or testing from a remote host).

I can't find any more configurations or suggestions to fix this. I'm running PostgreSQL 9.4 on Ubuntu 15.04 (installed from the repository). Has anyone a suggestion on what to try next? Or am I missing something?


texts: Log Shipping causes high context switches

texts:
MSSQL - I have setup log shipping on many databases being sent to 2 different servers in NORECOVERY mode.

This is causing crazy context switches on the main server.

Is this normal?

16 Cores without HT, 192gb ram.


texts: What type of locks are needed when creating a Postgres index concurrently?

texts:
I know that when an index is being created concurrently, only a SHARE ShareUpdateExclusiveLock lock is needed.
But is there a more strict type of lock needed at the very start of the process for a small amount of time? Or is only a SHARE ShareUpdateExclusiveLock lock needed for the entire operation?
I'm asking because I'm wondering what types of timeouts are advantageous to set when creating an index concurrently.


texts: PAGE compression in SQL Server effect on running queries

texts:
I am planning to apply PAGE compression to some of the big tables in my database (a data warehouse). These tables are fairly big with over 15 Billion rows.

When I applied the compression in test environment, the whole process took around 22 hours. These tables are accessed daily with quite long running queries.

While the compression is being applied would there be any effect on the queries that are running? Any lock etc I should be aware of?
Is there a staggered approach to applying compression?
Any other input/feedback you might have?


texts: Can I use the database and query it while full-text indexing?

texts: I need to index my database, but I need to query it while it is indexing, it may take a long time, can I do that?


texts:
As a root user I did this:

GRANT DELETE, SELECT, INSERT, UPDATE, EXECUTE ON dbName.* TO
'user'@'localhost' IDENTIFIED BY 'apassword';

In 'dbName' I created a procedure 'proc_name' with definer set to 'user'@'localhost'.

Similarly I create a function 'func_name' with definer set to 'user'@'localhost'.

Now the issue is, I'm able to do select func_name(); but I'm not able to call the procedure proc_name()

When i did :

call proc_name(), I get the below error:

  ERROR 1044 (42000): Access denied for user 'user'@'localhost' to database 'dbName'

Any pointers to the solution would be appreciated.