Pick the most non-default value from a selection of values Given the following tables:

CREATE TABLE FeeTestClient (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name VARCHAR(16))
INSERT INTO FeeTestClient (Name)
VALUES ('Test'), ('Test 2'), ('Test 3')

CREATE TABLE FeeTest (FeeId INT IDENTITY(1,1) NOT NULL PRIMARY KEY, ClientId INT, Fee INT, Val VARCHAR(16), Val2 VARCHAR(16))
INSERT INTO FeeTest (ClientId, Fee, Val, Val2)
VALUES (1, 15, 'Default', 'Default'),
       (1, 10, 'Default', 'asdf'),
       (2, 15, 'Default', 'Default'),
       (2, 20, 'Default', 'qwer'),
       (2, 10, 'zxcv', 'asdf'),
       (3, 20, 'Default', 'Default')

My goal is to select all FeeTestClient elements, and pick the fee that is least default. The rules on a default fee are pretty simple: if Val2 is 'Default', then Val cannot be anything except 'Default', and for each fee, we want the first one where Val is not 'Default', or the first one where Val2 is not 'Default', otherwise we are guaranteed a Val = 'Default' AND Val2 = 'Default' match.

The customer will only ever have one item that matches 'Default'/'Default', one item that matches 'Default'/____ and one item that matches ____/____. (Though the last two rows may not exist.) If they have a ____/____ then they'll always have a 'Default'/____, every customer will have a 'Default'/'Default'. They can never have a ____/'Default' — this is an invalid state on the application, and they can never have more than one of the same x/y, this is enforced by a UNIQUE constraint on the table.

It is possible (in the database) for the customer to have a 'Default'/a, and a 'Default'/b, but that is considered an invalid state in the application, and there is a test for that. (The user must delete one of the two.)

This is similar to a previous question of mine (Select all records, join with table A if join exists, table B if not), but much less pleasant. Because they're INT (actually FLOAT in the database, but the same issue applies) they're being aggregated together like I don't want.

I want to get a result of:

Id  Name    (No column name)
1   Test    10
2   Test 2  10
3   Test 3  20

I've tried:

SELECT Id, Name, COALESCE(f1.Fee, f2.Fee, f3.Fee)
FROM FeeTestClient
LEFT OUTER JOIN FeeTest AS f1 ON f1.ClientId = Id AND f1.Val <> 'Default' AND f1.Val2 <> 'Default'
LEFT OUTER JOIN FeeTest AS f2 ON f2.ClientId = Id AND f2.Val = 'Default' AND f2.Val2 <> 'Default'
LEFT OUTER JOIN FeeTest AS f3 ON f3.ClientId = Id AND f3.Val = 'Default' AND f3.Val2 = 'Default'

Which is ungodly slow on the live data-set, but returns the proper result (roughly 15 seconds, running the basic select on the data, without this selection of joins, is 7, plan is here), and I've also tried (by suggestion of Joe Obbish):

SELECT Id, Name, MAX(COALESCE(CASE WHEN Val <> 'Default' AND Val2 <> 'Default' THEN Fee END, CASE WHEN Val = 'Default' AND Val2 <> 'Default' THEN Fee END, CASE WHEN Val = 'Default' AND Val2 = 'Default' THEN Fee END))
FROM FeeTestClient
LEFT OUTER JOIN FeeTest ON FeeTest.ClientId = Id
GROUP BY Id, Name

Which is just as slow (plan is here), and produces the wrong output (though if you COALESCE the MAXs then it works correctly). (Maybe worse.)

I'm at a loss, and writing these queries has been very painful, so any advice on building the desired output is appreciated.

I've provided the actual plans, but they differ very heavily from the test ones (this MCVE is to demonstrate the result I want, answers are not obligated or even expected to make any performance claims), it seems. Please disregard the extra joins shown in the plans above - they are unimportant to the example.

As far as distribution, in the live data-set, 1.5% only have a 'Default'/'Default', 44.9% have 'Default'/___ and 'Default'/'Default', and 53.6% have all three.

I'm not sure that your MCVE fully represents the problem that you have, but I'll answer the question as given. This question is about performance, so having just a few rows in the table won't cut it. I duplicated your sample data a million times for a total of 6 million rows for FeeTest and 3 million rows for FeeTestClient. Code to do that below:

DROP TABLE IF EXISTS FeeTestClient;

CREATE TABLE FeeTestClient (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, [Name] VARCHAR(16));

INSERT INTO FeeTestClient WITH (TABLOCK)
([Name])
SELECT 'ZZZZZZZ' + CAST(RN AS VARCHAR(7))
FROM
(
    SELECT TOP (3000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t
OPTION (MAXDOP 1);

DROP TABLE IF EXISTS FeeTest_source;

CREATE TABLE FeeTest_source (ClientId INT, Fee INT, Val VARCHAR(16), Val2 VARCHAR(16));
INSERT INTO FeeTest_source (ClientId, Fee, Val, Val2)
VALUES (1, 15, 'Default', 'Default'),
       (1, 10, 'Default', 'asdf'),
       (2, 15, 'Default', 'Default'),
       (2, 20, 'Default', 'qwer'),
       (2, 10, 'zxcv', 'asdf'),
       (3, 20, 'Default', 'Default');

SELECT 3 * client.Id - 2 + client.Id % 3 AS ClientId
, src.Fee
, src.Val
, src.Val2 into #t
FROM FeeTest_source src
INNER JOIN FeeTestClient client ON src.ClientId = 1 + client.Id % 3;

DROP TABLE IF EXISTS FeeTest;

CREATE TABLE FeeTest (FeeId INT IDENTITY(1,1) NOT NULL PRIMARY KEY, ClientId INT, Fee INT, Val VARCHAR(16), Val2 VARCHAR(16));

INSERT INTO FeeTest WITH (TABLOCK)
(ClientId, Fee, Val, Val2)
SELECT * FROM #t;

DROP TABLE #t;

Using GROUP BY and only keeping the relevant aggregate could be a good approach depending on the nature of the data in the table and the indexes that are defined against the table. The query below finishes in 2 seconds on my machine:

SELECT Id, Name, MAX(COALESCE(CASE WHEN Val <> 'Default' AND Val2 <> 'Default' THEN Fee END, CASE WHEN Val = 'Default' AND Val2 <> 'Default' THEN Fee END, CASE WHEN Val = 'Default' AND Val2 = 'Default' THEN Fee END))
FROM FeeTestClient
LEFT OUTER JOIN FeeTest ON FeeTest.ClientId = Id
GROUP BY Id, Name

The plan is as I expect. There's a hash join along with a hash aggregate at the end.

However, you have other options because you have a FeeTestClient table. Another strategy is to calculate what you're looking for per row with OUTER APPLY. One way to do that is below:

SELECT Id, [Name], oa.Fee
FROM FeeTestClient ftc
OUTER APPLY (
    SELECT TOP 1 ft.Fee
    FROM FeeTest ft
    WHERE ft.ClientId = ftc.Id
    ORDER BY
        CASE WHEN Val <> 'Default' THEN 2 ELSE 0 END
        + CASE WHEN Val2 <> 'Default' THEN 1 ELSE 0 END
    DESC, ft.ClientId
) oa;

With APPLY and TOP you almost always want a good index on the inner table. The query optimizer builds a temporary index for us via the spool, and the query takes 14 seconds to run on my machine:

We're searching on ClientId so let's try an index on that:

CREATE INDEX NOT_COVERING ON FeeTest (ClientId);

The index isn't covering so the optimizer doesn't want to use it. Using it would require a lot of key lookups to get the columns that aren't on the index. I can force the index to be used with a lazy, undocumented trick that you should not use in production:

SELECT Id, [Name], oa.Fee
FROM FeeTestClient ftc
OUTER APPLY (
    SELECT TOP 1 ft.Fee
    FROM FeeTest ft
    WHERE ft.ClientId = ftc.Id
    ORDER BY
        CASE WHEN Val <> 'Default' THEN 2 ELSE 0 END
        + CASE WHEN Val2 <> 'Default' THEN 1 ELSE 0 END
    DESC, ft.ClientId
) oa
OPTION (QueryRuleOff BuildSpool);

Now the query runs in 5 seconds. We can see the key lookups in the plan:

Our final attempt with be with a covering index:

CREATE INDEX COVERING ON FeeTest (ClientId) INCLUDE (Val, Val2, Fee);

The query from before now runs in four seconds:

How can you apply this to your giant, anonymized query? You have a lot of key lookups and an index spool. Try defining covering indexes so that you can access the data you need more efficiently. I can't make any kind of guarantee about the overall runtime but it should help to some degree. Please note that I did not have time to take a very careful look at the plans that you posted.

In case anyone is following along at home, there's a bizarre edge case that can cause the covering index to still not be used. Here's one way to work around it:

ALTER TABLE FeeTest
ADD MAGIC_COLUMN AS CASE WHEN Val <> 'Default' THEN 2 ELSE 0 END + CASE WHEN Val2 <> 'Default' THEN 1 ELSE 0 END;

CREATE INDEX COVERING_2 ON FeeTest (ClientId) INCLUDE (MAGIC_COLUMN, Fee);


In SQL Server 2005 how would I determine the minimal permissions a user requires? I'm the accidental DBA of my group and I've inherited a database that follows a number of bad practices.  Currently every login is a member of the sysadmin group.

What is the easiest way to determine the minimal amount of privileges needed for each account?  I know I can use SQL Server Profiler to log all the SQL commands executed but it will return a bunch of information.  I can not think of an easy way to summarize all that data.

Any help would be appreciated.

If you want to cut the noise out of a Profiler trace, ClearTrace is invaluable. ClearTrace “normalizes” queries, removing parameters such that:

SELECT x FROM y WHERE z = 1
SELECT x FROM y WHERE z = 2

are both reported as

SELECT x FROM y WHERE z = #

If you capture sufficient activity from the database and order the ClearTrace report by execution count, you’ll have a better idea of the common/typical query patterns and from this the permissions required. You can then investigate the non-typical activity and establish if these require a different set of permissions and/or are generated by a subset of the user base.


Firebird 2.5. Windows 8.1. Can't connect to any database! Why? I'm trying to use a FireBird database engine.

OS: Windows 8.1 x64(SmartScreen enabled, UAC enabled, Custom AntiVirus installed(with firewall))

FireBird: Version 2.5, Installed as SuperServer, as application without guardian.

I'm trying to manage this engine using many applications, such as FlameRobin or FireBird Maestro.

At the first:
After installation db said, that the "firebird.msg" not found in windows directory, when I tried retrieve server version.

Ok. I'm copied it to system directory from program files.

At the second(fatal):
I cant connect to my databases!

Firebird Maestro said, when I'm trying connect to created database:

Or FlameRobin(choosed context menu "Retrieve server version" for "localhost"):

I'm nothing changed. Never installed firebird database.
Default user(SYSDBA) and password(masterkey) is used, tried in any сase sensitivity.

Today, I'm tried install FireBird on Windows Server 2003, and do some in FlameRobin, and all is works!

Whay the DB not works under Windows 8.1?

If you start Firebird as an application, then Firebird must either be located in a normal folder (not a system folder under UAC protection like Program Files), or you need to run it as Administrator.

The reason is that Firebird needs write access to several files in its own program folder, which doesn't work without privilege elevation.

Better yet: run it as a service. The service by default has sufficient rights to start Firebird.


SQL linked server returning error suddenly upon query We are facing error when trying to connect to and execute queries using a linked server. The SQL Server and the Linked Server are able to ping and telnet each other. Also testing the link returns positive. However, the following error is returned when we execute a query accessing the linked server:

OLE DB provider "SQLNCLI11" for linked server  returned message "Protocol error in TDS stream".
OLE DB provider "SQLNCLI11" for linked server  returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server  returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server  returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.

I'm trying to prove that this is a network issue, but since the communication channel is open and ping and telnet work, the task fell back in my basket.

Any advice will be greatly appreciated.

This does look like a network issue, as per this question on MSDN the "Protocol error in TDS stream" indicates as such.

This seems to be a very similar question that was resolved by disabling TCP Chimney Offload (think link in that question directs to a veritas article that describes how to do it).

Which may be a quick (and possibly dirty) was of trying to resolve it. If that is not the problem, then it is almost certainly a network problem (maybe a nic problem).

--

Edit:

Might be worth making sure the native client is fully up to date (by updating SQL Server, which is no bad thing anyway).

Also, if you're still struggling, add some more information about your environment to your question e.g. OS version, SQL Server version & update level, information about your nics etc.


sqlldr running slow after indexes created let me explain this....

I had running 1 session of sqlldr while I run indexes into table for 6 fields... now the indexes are completed but the sqlldr running very slow it load one file after each 30 minute...

now when I do any alter to set the indexes off it won't let me do it and though an error of ( ORA-000054 and ORA-000604) even if I run another sqlldr....

any suggestion will be highly appreciate.... bellow is sample of my control files header...

OPTIONS(DIRECT=TRUE,ROWS=10000,BINDSIZE=209700000,readsize=209700000)
load data
append
into table name
FIELDS TERMINATED by '!'
OPTIONALLY ENCLOSED by '"'
trailing nullcols

When I run following query

select * from dba_dml_locks

session_id,owner,name,mode_held,mode_requested,last_convert,blocking_others
123,username,tablename,exclusive,none,1228,not blocking

any suggestion ... this is my daily scheduler to load around 8000+ files around 20GB so any suggestion

Since no other sessions are accessing your table now, the lock shows as NOT BLOCKING. As soon as an other session tries to manage that table, it suddenly feels it is BLOCKED. The index is used so you can not manage it as long as other sessions are using it.

Having indexes on havily loaded tables is for sure like putting a brake on the loader process. You could help yourself by checking out the docs. See Conventional and Direct Path Loads

Maybe partitioning can help you. Than you can prepare a single table, load that, finish it's indexing and perform partition exchange. Just don't create global indexes on such a table.


PSQL: create random data sets with the same statistic distrbution as another table For test purposes, I want to create data sets (tables) where the statistic distribution of the values in the columns is the same as my actual data, but randomized.

Example : consider the following table ('ARTICLES' for a clothes shop):

| id | type    | colour | size | price |
----------------------------------------
| 1 | shirt    | yellow | M    | 14.99 |
| 2 | trousers | brown  | L    | 20.00 |
| 3 | shirt    | red    | L    | 14.99 |
| 4 | trousers | red    | L    | 20.00 |
| 5 | cap      | yellow | M    | 5.00  |
| 6 | cap      | brown  | S    | 5.00  |
| 7 | shirt    | red    | M    | 14.99 |
| 8 | trousers | red    | L    | 20.00 |

So, out of these 8 rows, I have the following distributions for each column:

type : 3 shirts, 3 trousers and 2 cap
colour : 4 red, 2 yellow, 2 brown
size : 1 S, 3 M, 4 L
price : 2 x 5.00 , 3 x 14.99 , 3 x 20.00

What I want is to create another table, with the same columns and the same number of rows, in which I have for each separate column the same statistic distribution of values, but assigned randomly and independently from each other.

Doing the stats for each column is pretty easy :

SELECT column_name, COUNT(1) AS number_of_rows
FROM ARTICLES GROUP BY column_name ;

I can also easily record these stats in a dedicated table :

CREATE TABLE articles_stats ( column_name varchar(255), value varchar(255), number_of_rows integer );

INSERT INTO articles_stats (column_name, value, number_of_rows)
SELECT 'type', type, COUNT(1) AS number_of_rows
    FROM ARTICLES GROUP BY type
UNION
SELECT 'colour', colour, COUNT(1) AS number_of_rows
    FROM ARTICLES GROUP BY colour
UNION
SELECT 'size', size, COUNT(1) AS number_of_rows
    FROM ARTICLES GROUP BY size
UNION
SELECT 'price', price::varchar(255), COUNT(1) AS number_of_rows
    FROM ARTICLES GROUP BY price ;

In this example, it would create the following articles_stats table :

| column_name | value    | number_of_rows |
-------------------------------------------
| type        | shirt    |        3       |
| type        | trousers |        3       |
| type        | cap      |        2       |
| colour      | red      |        4       |
| colour      | yellow   |        2       |
| colour      | brown    |        2       |
| size        |    S     |        1       |
| size        |    M     |        3       |
| size        |    L     |        4       |
| price       |    5.00  |        2       |
| price       |   14.99  |        3       |
| price       |   20.00  |        3       |

But how do I then create inserts into the target table (lets' call it 'RANDOM_ARTICLES') ?

PS: I have to do this many times so I hope to create a PSQL function for this.

If you only need the distribution of cardinalities to be the same, and not the ordering, or the length of the text strings, then you can use a hashing function.

create new_table as select
    md5('Aiw7Pa6y'||col1) as col1,
    md5('coh9ahMo'||col2) as col2,
    md5('ii5eFohj'||col3::text) as col3
from old_table;

For non-text columns, you would have to decide if they need to be randomized at all, and if so whether conversion to text (keeping the cardinality of each distinct value, but not the order or relative gaps) would be suitable.


Mongo takes too much memory when insert data I'm using mongo as a database log. I store on it all the logs of an application, so I have a high rate of inserts, for example 100 inserts per second. To accomplish that I developed a part of the application dedicated to store in mongo the data. this app only uses a mongo connector.

But the memory consuption is incredible high, I mean after start to use mongo the heap memory has increased in about 300MB and the CPU takes over 90%. Anyone knows how I can decrease this memory consuption or why it is happening?. I've tried to use Mysql and the heap memory keeps normal (only is increased in 20MB) and Cpu doesn't increment as mongo situation.

The data to store is very simple.
in fact I used the following json for separate test with the same results.

{testData:'something i'}

Is there any way to create a datasource with mongo?

see this link.   https://groups.google.com/forum/#!topic/mongodb-user/w7G1xRy3TZQ

the bottom line is Mongo caches indexes and data in available RAM


db_recovery_file_dest_size issue RMAN gives me this error how should i resolve this issue:

    ORA-19815: WARNING: db_recovery_file_dest_size of 7516192768 bytes is 100.00 used, and has 0 remaining bytes available.

Thanks in advance

Here is Oracle's answer.

Basically your options are these:

Delete transient files no longer needed for current policies.
Change something stored in the Fast Recovery Area to be stored elsewhere This could be temporarily or permanently.
Increase the db_recovery_file_dest_size to be larger than 7 GB.
Decrease your backup window or redundancy.
Change your archivelog retention policy.
Drop guarenteed restore points.


Date a backup was taken even if restored to another server I have a data warehouse platform that is loaded from backups of a set of application databases restored locally onto the server.  We have a requirement to know what time the backup was taken - i.e. the as-at time on the source system that the data loaded into the EDW was taken.

This question discusses getting last restore dates.

The table msdb.restorehistory has a column backup_taken_from.  Will this still hold the backup date if the backup is restored onto another server - i.e. is this populated from metadata in the backup files?

The problem with using MSDB and Master is that those reference the server they reside on. So if you run those commands, it will refer to restores on the server you are querying and not the restores on the server you are referring to in this circumstance.

The backup files itself should contain the information you need however.

RESTORE HEADERONLY
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014.bak' ;
GO

The above example is doing a check on a backup file and in there, I can see the BackupStartDate shows '2014-07-17 16:18:18.000' and BackupFinishDate shows '2014-07-17 16:18:22.000'.

When I use the query you had linked in the example referenced by Marc_c on Stack Exchange, I can see the same backup I stated above. The Create_Date is '2018-05-17 13:32:39.700' and the Restore_Date is '2018-05-17 13:32:39.357'. This illustrates my point that the Master and MSDB only refer to the local server, not the backup metadata information.

Query posted from Marc_c for preservation:

;WITH LastRestores AS
(
SELECT
    DatabaseName = [d].[name] ,
    [d].[create_date] ,
    [d].[compatibility_level] ,
    [d].[collation_name] ,
    r.*,
    RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
)
SELECT *
FROM [LastRestores]
WHERE [RowNum] = 1

See the MSDN on HEADERONLY.

See the MSDN on Result Sets from HEADERONLY

I would create a procedure that would loop through the files you need and have it capture the output of the RESTORE HEADERONLY and then log that to the table for your reference later to meet your requirements.

Martin Smith on Stack Exchange with an OPEN ROWSET solution to logging the data.


Primary key with "NOT FOR REPLICATION" option I have recently taken over a project, and I have discovered that in most tables the primary key has the property "NOT FOR REPLICATION".

I'm no DBA, but surely in most databases, a record without a primary key would be considered corrupted. The primary key of most of these records would almost certainly be used as a foreign key somewhere.

Was this an error on the part of the previous developer (who no longer works for the company), or is there some other logic involved? We don't even use replication in the production environment, so this doesn't actually affect anything serious anyway but I was wondering if there are any other side-effects of removing all these directives that I'm not aware of.

I haven't found many useful hits for a number of search terms related to this topic, so I'm fairly certain that this is just a silly error that I need to reverse, so this question is really to assuage my paranoia.

NOT FOR REPLICATION indicates that when a record is replicated to this table, any value that is inserted into the identity column via the replication agent keeps it's original value from the source system, but any records added locally still increment the identity value.

UPDATE: Old Link broken

Here is the link to the table_constraint docs that details what setting NOT FOR REPLICATION will do:

Table Constraint


Error when trying to install SQL Server 2012 Ent in to a Server Core 2008 VM I have a problem that I hope you can help with.

I am studying for my 70-462 exam and in order to do so, must setup virtual labs as follows.

DC: Windows Server 2008 R2 Enterprise (Full Installation) - Domain controller for the lab network, contoso.com
SQL-A: Windows Server 2008 R2 Enterprise (Full Installation) - Domain member for hosting SQL Server
SQL-B: Windows Server 2008 R2 Enterprise  (Full Installation) - Domain member for hosting SQL Server
SQL-C: Windows Server 2008 R2 Enterprise (Full Installation) - Domain member for hosting SQL Server
SQL-D: Windows Server 2008 R2 Enterprise (Full Installation) - Domain member for hosting SQL Server
SQL-CORE: Windows Server 2008 R2 Enterprise (Server Core Installation) - Domain member for hosting SQL Server

I have created all of these VMs and installed the versions of Windows Server listed. I have also created the domain and joined VMs SQL-A, SQL-B and SQL-Core as well as creating a domain user on the Domain Controller called Contoso/Kim_Akers. I am able to log in to all of the VMs with this user.

I have since installed SQL Server 2012 Ent on SQL-A and SQL B, but get the following error when trying to install SQL-Core.

Overall summary
Final result:                  Failed: see details below
Exit code (Decimal):           -2068578304
Exit facility code:            1204
Exit error code:               0
Exit message:                  The Windows account Contoso/Kim_Akers does not exist and cannot be provisioned as a SQL Server system administrator.
Start time:                    2016-07-06 20:02:52
End time:                      2016-07-06 20:04:17
Requested action:              Install

If I run whoami on the SQL-Core VM it comes back as follows:

Consto.Kim_Akers

If I run SConfig, it shows that I am connected to the Contoso domain, so I don’t understand where the error is coming from.

To provide further background on this, the command I am running to install SQL Server 2012 Enterprise on the Server Core 2008 VM is as follows.

Setup.exe /qs /Action=install /Features=SQLEngine,IS,Conn
 /InstanceName=MSSQLServer /SQLSYSADMINACCOUNTS="Contoso\kim_akers"
 /IAcceptSQLServerLicenseTerms

What may be wrong with my setup?

Nothing seems wrong with your setup. I too am doing the training and noticed the books had some errors in them.

But notice the difference between the 'WHOAMI' output, and your installation command line.
The book has several typos where it calls the domain: Contoso, while before you created a domain called Contso.

Change your command line to: /SQLSYSADMINACCOUNTS="Contso\kim_akers" and it should work.


MongoDB Configuration File Locations Some databases search/check multiple locations for configuarion files, if it finds many, it merges and does a priority if same options defined.

Does mongodb search multiple locations for configuration files? (starting as a service, not using --config option)

From searching I know it checks /usr/local/etc/mongod.conf by default.

I am hoping to do is separate my global config and production/development config.

The MongoDB server actually does not search any locations for config files, and there is (as at MongoDB 3.2) no way to specify multiple config files or include config file fragments. There was a feature suggestion for includes discussed (see: SERVER-14911 in the MongoDB issue tracker), but it was decided that includes are not part of standard YAML and configuration management systems are likely the best option.

  From searching I know it checks /usr/local/etc/mongod.conf by default.

This is the default provided in the service recipe for the mongodb Homebrew package. The official Linux packages generally use a convention of /etc/mongod.conf.


Mysql: Insert into JSON column type with GROUP_CONCAT I'm trying to insert a list of words into a column with the JSON type:

create table my_table (
    ...
    synonyms JSON
    ...
)

My query looks like this:

INSERT into my_table (id, synonyms)
SELECT id, GROUP_CONCAT(DISTINCT synonyms) as synonyms from (
  SELECT id, name1,name2, GROUP_CONCAT(DISTINCT name1) as synonyms
  FROM products
  GROUP BY id

  UNION

  SELECT id, name1,name2, GROUP_CONCAT(DISTINCT name2) as synonyms
  FROM products
  GROUP BY id
) t group by id;

If run without the INSERT in line one, the synonyms column returns a list of comma separated words:

id       name1    name2    synonyms
------------------------------------
1        one      a,b      one,a,b
2        two      c        two,c

I want to insert those synonyms as a JSON_ARRAY.  However, the INSERT fails when the individual words are not double quoted.  If I add JSON_ARRAY in my select:

SELECT name1,name2,id, JSON_ARRAY(GROUP_CONCAT(DISTINCT synonyms)) ...

I get the following result:

name1    name2    synonyms
----------------------------
one      a,b      ["one,a,b"]
two      c        ["two,c"]

When what I want is this (so it can be inserted directly into the synonyms column):

name1    name2    synonyms
----------------------------
one      a,b      ["one","a","b"]
two      c        ["two","c"]

Is there a way to do this with SQL, or is it better handled within application code?

Use JSON_ARRAYAGG() instead of GROUP_CONCAT(). - akina

It is available only in 5.7.22+

Does it have an implicit DISTINCT? I don't know...if not, subquery with DISTINCT will help.


1 out of 3 Transactions in DB2 fail with SQL 998, State 58005, error code 8, sub code 2 We have a few back-end applications using an XA switch module connected to a DB2 (10.5).
2 or 3 transactions will pass until this shows up:

I’ve found this document that talks about “Code 998, state 58005, reason code 9, subcode 2".
Unfortunately, all they say is:

  For reason code 9, subcode 02, ensure that the tp_mon_name configuration parameter contains the name of the dynamic library in the external product which has the ax_reg() function used for dynamic registration of transactions.

On the other hand, I found this document where, on the explanation of tm_database, they say "don't use tp_mon_name, if you are using XA switch module", which I am. But they don't explain tm_database.
I have two versions of the switch module, one with CONSTANT DYNAMICREGISTRATION "Y" and one with CONSTANT DYNAMICREGISTRATION "N".
The latter exists somewhere and will take me huge time to find.
Do you think it could be it?

Apparently, this was the switch module.
The RM OPEN STRING definition included: rmopenstring=db=mydatabase,uid=uuuuuu,pwd=pppp,,axlib=somelib
Once the value for axlib was supplemented with an extention, .so, the error has gone away.


phpMyAdmin configuration storage is not completely configured message Today i have updated my PhpMyAdmin to 4.1.2
Now when i log in to PMA i am getting the message:

The phpMyAdmin configuration storage is not completely configured, some extended features have been deactivated. To find out why click here.

When i click on the link:

PMA Database ... not OK [ Documentation ]
General relation features Disabled

Do i need this features? Or i can ignore that message? Thanks in advance

You do not need the features to use phpMyAdmin. You can read more about what they include in the phpMyAdmin documentation.

To disable, just remove the corresponding lines from your config.inc.php (you can start here in the documentation; each entry up to and including $cfg['Servers'][$i]['designer_coords'] relates to the phpMyAdmin Configuration Storage features; if you have any of those lines in your config.inc.php you can simply remove them). You also may have $cfg['Servers'][$i]['controluser'] and $cfg['Servers'][$i]['controlpass'] entries, which generally are only used for the Configuration Storage features as well and in most cases can be removed.


Moving DBs (including System DBs) to new LUNs on a single node cluster We have a single node cluster (SQL2K8 R2) currently utilizing storage on our old SAN.  We'd like to migrate to storage on the new SAN without rebuilding or moving the user DB.  The infrastructure team's idea is to present the new LUNs to the server/cluster and then move all the files to these new LUNs.

The obvious problem here is that the system DBs can't simply be moved via the methods user DBs can be moved.  And, while I could use the documented methods to move the system DBs, under SQL2K8 and later, the Resource DB can't be moved at all.  So, it doesn't seem like this is possible in a straightforward way.

I'm thinking this would possible using sort of a shell game of shutting down SQL Server, copying all files to the new LUNs, renaming the old LUNs, renaming the new LUNs to the original old LUN names, and then bringing up SQL Server.  I've not tried this before but it seems like as long as the new LUNs eventually had the original name of the old when the instance came back up (and we'd properly presented the LUNs to the cluster), this might work.

I know this isn't ideal.  Ideally, I'd move all these DBs to the existing "real" cluster on the new SAN but changing client connectivity is a challenge right now.

Any gotchas to consider?  There's no failover to test since it's a single-node cluster.

As long as the path names remain the same for your database files, this approach should work.  This assumes that when you say "copying all files", your are ONLY moving the database files (data/log) and not the SQL Server binaries.  If your SQL binaries and/or resourcedb need to be moved, you have a much stickier task ahead of you.

Your biggest gotcha should be maintaining the directory and database file permissions.  I would handle this using ROBOCOPY with the /copyall switch to move the files while keeping all perms and attributes intact.

While outside of scope, if your end goal is to move the database to your full cluster and you're hurdle is client connectivity, have you considered using a DNS Alias to redirect traffic?  I understand that the connectivity change could be larger in scope, but it could give you a path to consolidating your databases to where you want them.


Managing SQL Server logins and database users I am getting a little further into the database administration side of things and starting to set up security access to SQL server databases for users. I have a question about server logins and database users in the following scenario:

We have 30 database users that require the same permissions, each with their own AD accounts.

When setting up SQL server access for these SQL writers what is the most performant approach for giving a group of users with the same permissions access to a server/database?

I appreciate that this could be achieved via several approaches but am keen to learn the performant approach to managing relationships between server logins/database users/database roles.

Thanks :)

The easiest and most manageable way to achieve this is using Active Directory groups.

Have your ad administrator create a security group called SQL_Writers (or something similar) for those users and add them (and any new users to that group).

Then in SQL Server, simply grant that security group the appropriate permissions to the server / databases.

If you want the permissions to be inherited to any newly created database on your instance, remember to grant the security role to the model database as well.


After update triggers that deal with multi-row updates Currently working on an database Audit project based on triggers that are fired on update on specific tables. The triggers write the changes into a table; information written are: table name, updated column, timestamp, user, old value and new value.

Triggers work fine with single updates, but when it comes to multi-row updates, it is not working.

My code is like this:

IF (UPDATE(Priority))
BEGIN
    SET @UpdatedColumn = 'Priority'
    INSERT INTO dbo.AuditTable
        ( [TableName] ,
          [Source] ,
          [RecordId] ,
          [User] ,
          [TimeStamp] ,
          [UpdatedColumn] ,
          [OldValue] ,
          [NewValue]
        )
    SELECT
        N'BookingItem' , -- TableName - nvarchar(max)
        (SELECT CODE FROM TBL_LEG_SOURCE
                     INNER JOIN INSERTED INS ON LEG_SOURCE_ID = INS.SourceId) ,
        INS.Id , -- RecordId - bigint
        (SELECT USERNAME FROM INSERTED
                     INNER JOIN TBL_USER
                     ON ModifiedById = USER_ID) , -- User - nvarchar(max)
        GETDATE() , -- TimeStamp - datetime
        @UpdatedColumn , -- UpdatedColumn - nvarchar(max)
        DEL.Priority , -- OldValue - nvarchar(max)
        INS.Priority  -- NewValue - nvarchar(max)
    FROM
        INSERTED INS INNER JOIN DELETED DEL ON INS.Id = DEL.Id
    WHERE
        (
            (INS.Priority <> DEL.Priority)
            OR (INS.Priority IS NULL AND DEL.Priority IS NOT NULL)
            OR (INS.Priority IS NOT NULL AND DEL.Priority IS NULL)
        )
END

Error message:

  Msg 512, Level 16, State 1, Procedure MyTrigger, Line 818
  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any suggestions on how to fix my trigger in order to handle multi-row operations?

Here is how to fix the errors, using proper joins (and if this isn't "fast enough" then look at your indexing):

INSERT dbo.AuditTable
(
  [TableName],
  [Source],
  [RecordId],
  [User],
  [TimeStamp],
  [UpdatedColumn],
  [OldValue],
  [NewValue]
)
SELECT
  N'BookingItem', -- TableName - nvarchar(max)
  ls.CODE,
  INS.Id, -- RecordId - bigint
  u.USERNAME,
  GETDATE(), -- TimeStamp - datetime
  @UpdatedColumn, -- UpdatedColumn - nvarchar(max)
  DEL.Priority, -- OldValue - nvarchar(max)
  INS.Priority  -- NewValue - nvarchar(max)
FROM
  INSERTED AS INS
INNER JOIN
  DELETED AS DEL ON INS.Id = DEL.Id
INNER JOIN
  dbo.TBL_LEG_SOURCE AS ls ON ls.LEG_SOURCE_ID = INS.SourceId
INNER JOIN
  dbo.TBL_USER AS u ON INS.ModifiedById = u.USER_ID
WHERE
(
  (INS.Priority <> DEL.Priority)
  OR (INS.Priority IS NULL AND DEL.Priority IS NOT NULL)
  OR (INS.Priority IS NOT NULL AND DEL.Priority IS NULL)
);

However I think it is quite foolish to run 50+ different inserts of this variety in order to capture every single column change. Why not just create a table with columns for time and table name (you don't need to store username because you can always look that up later), and then whenever there is an update, store the old and new version of the row? You can even use a SEQUENCE to ensure that you can identify the set of rows that were modified together (since timestamp may not be unique enough to do that).

CREATE SEQUENCE dbo.AuditSequence
  AS INT START WITH 1 INCREMENT BY 1;

CREATE TABLE dbo.AuditData
(
  AuditSequenceID INT,
  TableName SYSNAME,
  [TimeStamp] DATETIME,
  RowState CHAR(1), -- e.g. 'B' = before, 'A' = after
  ... all your 50 columns, including ModifidById ...
);

Now in your trigger:

CREATE TRIGGER dbo.MyTrigger
  ON dbo.BookingItem
  FOR UPDATE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @as INT = NEXT VALUE FOR dbo.AuditSequence,
          @now DATETIME = CURRENT_TIMESTAMP;

  INSERT dbo.AuditData(AuditSequenceID, TableName, [TimeStamp], RowState,
    ... the rest of your 50 columns)
  SELECT @as, N'BookingItem', @now, 'B', * FROM deleted;

  INSERT dbo.AuditData(AuditSequenceID, TableName, [TimeStamp], RowState,
    ... the rest of your 50 columns)
  SELECT @as, N'BookingItem', @now, 'A', * FROM inserted;
END
GO

Now, write complicated queries against this much simpler audit structure that are inefficient and try to track exactly which columns have changed and all of that. Much better to pay that price when you're reviewing audit data than to pay that price on every single update operation.


Explicit locks in RedShift I'm new to AWS Redshift and I wanted to know How i can implement my use-case.

I have a Redshift table which gets lot of Inserts/Selects.
In some cases I need to delete some data from that table. I don't want my Delete to impact other writes and reads.

Can someone suggest what is the best locking mode available in Redshift to implement my use case.

I don't want to end up with dadlocks and some locks hanging in my application.

I tried without locks and I'm observing the following violation:

Serializable isolation violation on table - 156300, transactions forming the cycle are: 6416378, 6416913 (pid:3770);

Also I went through the Redshift cxplicit locks but couldn't find any Official documentation for the same.

I know that Redshift is a wrapper on PostgreSQL, so can I use that documentation for implementing?

Assuming that it works like PostgreSQL in this case:

This is a serialization error that you get because you are using the SERIALIZABLE transaction isolation level.

That is nothing to worry about, just repeat the transaction.


Can I reorganize a Primary Key after deleting some rows? In my project there is a table in which I add testing data for smoke tests, proceed with some tests, then delete the data I added. The Primary Key on that table is and not null int and has an auto incrementing identity, so after the deletion the highest number is left as it is.

My question is: is there a way to reorganize this number so it can return to the value before my smoke test inserts?

I tried to do Rebuild and Reorganize on the primary key hoping for a miracle but it didn't work.

Remark: I can't drop the Id column and create it again, as the existing data must have the same Ids after my tests.

Both options delete all records from your table and restarts IDENTITY values.

TRUNCATE TABLE

TRUNCATE TABLE <schema>.<table_name>;

  Removes all rows from a table or specified partitions of a table, without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

or

DBCC CHECKIDENT

DELETE FROM <schema>.<table_name>;
DBCC CHECKIDENT ('<schema>.<table_name>', RESEED, 0);

  Checks the current identity value for the specified table in SQL Server 2017 and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.


The mysql-bin.xxxx files are growing very fast I have a replication enviroment and is working fine, but today my disk going full.

I check and see that the mysql-bin.xxx files are growing very very fast (I know they contain all changes in the database(s), so naturally they will grow, but today it is not normal the way that they are growing... that is, very fast and continuously!).

Then I use mysqlbinlog to see the content of de last mysql-bin.xxx file but is to dificult to see any error.

My question

Is there some way to perceive, check, anything... where or why my mysql-bin files are growing?

Are you using replication?  If not, do you have the binlogs turned on for backups?

Do you have expire_logs_days set?  To what?

Give us some clues of what write queries are going on.

Are you UPDATEing all the rows in large tables?  If so, this is a sign of a mis-designed schema.

Are you doing massive DELETEs?  Let's discuss alternatives.


How to rebuild entire MySQL data after server re install? I have a MySQL server running with multiple databases in a 32 bit environment. I need to rebuild my system to a 64 bit environment.

Is it possible to replicate the entire MySQL databases, users, privileges, settings, etc. after the rebuild?

MySQL replication works with mixed hardware architecture
http://dev.mysql.com/doc/refman/4.1/en/replication-faq.html
By default all MySQL databases, users, previleges, settings etc.. unless you specify

Replicate_Ignore_DB
Replicate_Ignore_Table

option while starting a slave server.


Environmental Variables for OracleDB 12 in Oracle Linux are Overriding Each Other with SQLPlus Variables I want to set the environmental variables for OracleDB and SqlPlus properly. However, they are overriding each other. I'm doing something wrong, and I would like to get a hand finding where I went wrong.

Setup:
Oracle Linux 7
OracleDb 12.1.0.2.0 (12c)
Sqlplus 12.2.0.1.0

Here is my scenario.
Step 1: First, I set the environmental variables for OracleDB in "One" terminal window, as follows
[myuser]$ export ORACLE_HOME=/u02/app/oracle/product/12.1.0.1/dbhome_1
[myuser]$ export ORACLE_SID=o12c
[myuser]$ export LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib
[myuser]$ export PATH=$ORACLE_HOME/bin:$PATH

Step 2: Second, I set the Environmental Variables for SQLPLUS in the "One" terminal window used in step 1, as follows:
[myuser]$ ORACLE_HOME=/usr/lib/oracle/12.2/client64
[myuser]$ PATH=$ORACLE_HOME/bin:$PATH
[myuser]$ LD_LIBRARY_PATH=$ORACLE_HOME/lib
[myuser]$ export ORACLE_HOME
[myuser]$ export LD_LIBRARY_PATH
[myuser]$ export PATH

Then, I perform the following command.
[myuser]$ sqplus / as sqldba

Which comes back with the following error.
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 27 18:57:44 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

ERROR:
ORA-12545: Connect failed because target host or object does not exist

Enter user-name:

If I enter any user name (currently logged on user included), I get the following error:
Enter user-name: sysdba
Enter password:
ERROR:
ORA-12545: Connect failed because target host or object does not exist

Lastly, if I try to execute the "nolog" command, I get the following.
[oracle@localhost Desktop]$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 27 19:01:10 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

And, I tried to enter the conn / as sysdba command, and I get the following:
SQL> conn / as sysdba
ERROR:
ORA-12545: Connect failed because target host or object does not exist

If I run the command, "env" I get the last Oracle_home variable set. In the scenario described above. It's SQLPlus Oracle_Home.

I found a solution.

I noticed that the path ORACLE_HOME wasn't working properly. I used a deductive method. I determined the error by executing common commands that normally run by supplying the name of the application (Within OracleDB suite). For example:

First I checked the oratab file, as follows:

 vi /etc/oratab

Which gave me the following output:

orcl:/u02/app/db12c/product/12.1.0/dbhome_1:Y

Then, I started executing common commands. For example, I ran the "Listener Controller application" as follows:

lnsrctl status

Which gave me the following error:

bash: /u02/app/db12c/product/12.1.0/dbhome_1/lnsrctl: No such file or directory

Then, I decided to use the command above by stating the path explicitly.

/$ORACLE_HOME/bin/lsnrctl

Which gave me the following:

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 27-JUN-2017 20:28:22

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

Then I ran the status of the Lnsrctl, as follows:

LSNRCTL> STATUS

Which gave me the following:

STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                27-JUN-2017 20:28:37
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u02/app/db12c/diag/tnslsnr/{ServerURL}/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST={ServerURL})(PORT=1521)))
The listener supports no services
The command completed successfully

After the above, I was able to run the commands normally. For instance,

sqlplus / as sysdba

Which gave me the familiar screen of:

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 27 20:29:11 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>

And that was the problem. I'm not really sure if this is a glitch, setup problem, or design feature, but I hope this post can help someone who is going through the same situation. From now on, I have to use the command:

/$ORACLE_HOME/bin/lsnrctl

explicitly.

Thanks for everyone who posted.


Medians, Modes, Percentiles and OLAP I'm newbie trying to wrap my head around OLAP, and I have a few questions.

Question 1: Can an OLAP cube store medians, modes, percentiles?
Question 2: Can an user-written MDX query return a summary of row-level data? (ex: % transactions > $100). Or must the cube designer add this to the cube?
Question 3: Do any OLAP products now provide mechanisms for accessing row level data? Which?

Our IT Department are looking for feedback on what sort of issues we're having with a particular MS Analsis Services ROLAP cube. We don't have access to the relational database behind it and need to perform calculations that aren't currently available as measures in the cube.

Let me see if I have this right.

A cube can provide statistics for counts, means, proportions, standard deviations.
If a particular statistic hasn't been catered for in a measure provided by the cube designer, can we write an MDX query to get it? Or do they need to change the cube in order to pre-calculate it from the row level data?
A cube cannot provide statistics likes medians, modes or percentiles, beacuse these statistics don't aggregrate properly.

I'm reading Leland Wilkinson's The Grammar of Graphics and in his chapter on Data Mining and OLAP, he says

  These [cube operations] work well with statistics like counts, means,
  proportions, and standard deviations. Simple aggregations over
  subclasses can be computed by operating on sums, sums of squares, and
  other terms that are combined in linear functions to produce basic
  summary statistics.

  They do not work properly with statistics like the median, mode and
  percentiles because the aggregate of these statistics is not the
  statistic of their aggregates. The median of medians is not the median
  of the aggregate, for example.

He goes on to add:

  A more sophisticated ROLAP model has emerged recently, however. It is
  possible, through several technologies, to give statistical algorithms
  access to raw data through the relational model in real time.
  This approach is more promising than the rigid aggregations offered by
  structures such as data cubes.

  In the most elegant form of this
  architecture, applications can request remote connections to provide
  information about their data-handling methods and take suitable action
  depending on the returned information. In this form, component
  architecture can achieve the real promise of distributed computing:
  design and execution that are independent of site, operating system,
  or language.

That was written circa 2005. Is anyone aware of products employing this methodology to allow for row-level data access?

To answer your questions in order:

The cube doesn't store medians, modes (or even averages), but you can write queries that calculate them and embed them as calculated measures in the cube.  The ability to embed this sort of computation is one of the main unique selling points of OLAP technology.
If you have a dimension that can identify individual rows (which could be a degenerate or 'fact' dimension derived from an identifier on the fact table) then you can do a query based on individual rows.  However, OLAP works in terms of dimensions and aggregates, so you would need to have a dimension capable of identifying individual rows (with an aggregate composed of one value).
Any OLAP tool can do what's described in (2), plus they generally support a mechanism known as 'drill-through' where the cube will return a bordereaux of the transactional data underlying a given slice that you drill through into.

If you want to do calculations that aren't directly available in the cube script, many OLAP tools such as the late, lamented ProClarity will allow you to formulate queries involving custom MDX based calculations.  Unless the cube doesn't have the information you need to do the actual calculations, the custom MDX calculations should be able to support any computation you need.

Although OLAP queries are traditionally associated with statistical queries in aggregate, if you have a dimension that allows drill down to detail you need it is certainly possible to formulate queries that will calculate medians, percentiles or histogram queries from which modes can be inferred or computed.

For example, this has an example of a pareto analysis query, which is based on rankings.

Many cube products can operate in a hybrid or relational OLAP mode where they do not persist the data themselves but query it from an underlying database.  In addition, pure ROLAP tools such as Business Objects, Report Builder or Discoverer can query from an underlying database and do work row by row.  However, they tend to lack the sophistication of dedicated OLAP products, and they don't have much in the way of statistical analysis capability out of the box.


can I give permission to insert into view, but not to referred table? Here I have a view in public schema referring to the table in private schema:

CREATE OR REPLACE VIEW public.products AS
 SELECT products.id, products.product_id, products.title, products.company_id
   FROM private.products;

ALTER TABLE products
  OWNER TO postgres;
GRANT ALL ON TABLE public.products TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE products TO viewers;

CREATE OR REPLACE RULE "_INSERT" AS
    ON INSERT TO products DO INSTEAD  INSERT INTO private.products (product_id, title, company_id)
  VALUES (new.product_id, new.title, new.company_id);

the whole thing works fine,
but I want the client to only be able to operate in public schema and not in private. Is there a way to setup such permissions?

Yes you can. Per documentation:

  Note that the user performing the insert, update or delete on the view
  must have the corresponding insert, update or delete privilege on the
  view. In addition the view's owner must have the relevant privileges
  on the underlying base relations, but the user performing the update
  does not need any permissions on the underlying base relations.


Joining 4 tables and selecting distinct values based on 3 timestamps I have been trying to remove duplicates from my query results based on the time but I would need to consider 3 different times and i am not sure how to do that. As some of the testing happen multiple times, there are duplicates on the part_id and i would only like to have the last tested results from all the 3 tables.

I am running this query now

select lb.request_ky,
lb.build_dm,
lb.part_id,
dv.date_test,
dv.result,
dw.date_test,
dw.result,
tt.date_test,
tt.result

From tableA lb,
tableB dv,
tableC dw,
tableD tt

Where lb.part_id = dv.part_id
And lb.part_id = dw.part_id
And lb.part_id = tt.part_id

This query would give me duplicates on the part id. How can i just get the latest result from each table and join them together?

You have to define first how a "testing" is identified and what "latest" is. Which of the columns identifies a testing and which of the date columns is going to be used for the ordering?

Then, one way to write the query is using window functions:

With cte As
  ( Select
        lb.request_ky,
        lb.build_dm,
        lb.part_id,
        dv.date_test As date_test1,
        dv.result    As result1,
        dw.date_test As date_test2,
        dw.result    As result2,
        tt.date_test As date_test3,
        tt.result    As result3,
        Row_number() Over (Partition By          -- pick a column for the
                             lb.part_id          -- identification
                           Order By              -- and another for the
                             dv.date_test Desc)  -- order
            As rn
    From
        tableA lb
      Join tableB dv
        On lb.part_id = dv.part_id
      Join tableC dw
        On lb.part_id = dw.part_id
      Join tableD tt
        On lb.part_id = tt.part_id
  )
Select
    request_ky,
    build_dm,
    part_id,
    date_test1,
    result1,
    date_test2,
    result2,
    date_test3,
    result3
From
    cte
Where
    rn = 1 ;


When did sp_OACreate et. al. become deprecated? I had an issue on an instance of SQL Server I'm supporting and many of the Stack sites have information on these features, but most of them basically say "Don't use those; they are deprecated/buggy/leaky".  I'm going to try to implement work arounds for my specific issue, but my question is when did features such as sp_OACreate and sp_OAMethod become deprecated?  I have to explain this to my Sys Admin and boss in order to justify a rewrite and I haven't come across much just by Googling (although perhaps my search terms are poor).

While I have been unable to find any official notice of deprecation for the OLE Automation stored procedures (sp_OA*), I believe they are generally regarded as deprecated as of the release of SQL Server 2005. Extended Stored Procedures, on the other hand, were officially deprecated as of SQL Server 2005. Both of those features were obsoleted by the introduction of the CLR Integration feature (i.e. SQLCLR).

When features become deprecated, they are listed in the following documentation:

Deprecated Database Engine Features in SQL Server 2005

That link takes you to the SQL Server 2005 version of the document. You can use the drop-down towards the top to check other versions. No version seems to list either "OLE Automation" or "sp_OA", so it would appear that the feature is not officially deprecated.

Yes, you heard correctly that the OLE Automation stored procedures are buggy and leaky (or at least have been at various times / service pack levels). They are less secure than SQLCLR (the "OLE Automation Stored Procedures" feature is either enabled or disabled; there is no control over who uses the feature if enabled, or how it is used, both of which are controllable -- to a degree -- in SQLCLR).

Also, as far as I have seen, the OLE Automation API (i.e. COM) has not been updated to work with any datatypes introduced as of SQL Server 2005 (i.e. MAX types, XML, DATE, TIME, DATETIMEOFFSET, CLR types, etc.).

You should be using SQLCLR instead of sp_OA* stored procedures for any new code. For existing code that is working, it is probably fine to leave it as is. But if that existing sp_OA* code should break, then it would be better to rewrite as SQLCLR instead of trying to get the sp_OA* approach working.


Super simple mysql query taking too long I have the following:

SELECT * FROM autorepair
WHERE zip_code IN (91316,91356,91416,91426,91436)
AND specialty = 'transmission'
ORDER BY ABS(91316 - `zip_code`)
LIMIT 0,10

Indexes:

-zip_code
-specialty

How can I improve the performance on queries like these?

BTW, I'm running this on XAMPP.

Create a compound index as follows:

ALTER TABLE autorepair ADD INDEX specialty_zipcode_ndx (specialty,zipcode);

and drop the single column specialty index

ALTER TABLE autorepair DROP INDEX specialty;


MYSQL Find (and Delete) Word Prefixes and Suffixes Let's say I have a MYSQL table with the following data in one column:

marine
submarine
way
subway
subjugate
submission
mission

I want to find (and delete) rows that have "sub" as the prefix IF AND ONLY IF another row exists with the root word. So in this example I want to delete "submarine", "subway", and "submission" but NOT "subjugate" (since "jugate" doesn't exist).

How can I do this?

UPDATE: Okay, thank you @Akina. I am getting a:

#1054 - Unknown column 't1.word' in 'where clause'

when I try. This is my version:

SELECT t1.*
FROM `en` AS t1, `en` AS t2
WHERE LEFT(t1.word, 3) = 'sub'
AND t1.word = CONCAT('sub', t2.word)

What am I doing wrong?

DELETE t1.*
FROM table t1, table t2
WHERE LEFT(t1.word, 3) = 'sub'
  AND t1.word = CONCAT('sub', t2.word)


Hard and Fast rule for include columns in index Is there any hard and fast rule to decide what columns and in which order it should be put in Included in non clustered index. I was just reading this post https://stackoverflow.com/questions/1307990/why-use-the-include-clause-when-creating-an-index
and I found that for the following query :

SELECT EmployeeID, DepartmentID, LastName
FROM Employee
WHERE DepartmentID = 5

The poster suggested to make index like this:

CREATE NONCLUSTERED INDEX NC_EmpDep
  ON Employee(EmployeeID, DepartmentID)
  INCLUDE (Lastname)

here comes my question why can't we make index like this

CREATE NONCLUSTERED INDEX NC_EmpDep
      ON Employee( EmployeeID, DepartmentID, LastName)

or

    CREATE NONCLUSTERED INDEX NC_EmpDep
          ON Employee( EmployeeID, LastName)
INCLUDE (DepartmentID)

and what thing leads the poster to decide to keep the LastName column included. Why not other columns? and how to decide in what order we should keep the columns there?

That index suggestion by marc_s is wrong. I've added a comment. (And it was my answer accepted too!)

The index for this query would be

CREATE NONCLUSTERED INDEX NC_EmpDep
  ON Employee(DepartmentID)
  INCLUDE (Lastname, EmployeeID)

An index is typically

CREATE INDEX <name> ON <table> (KeyColList) INCLUDE (NonKeyColList)

Where:

KeyColList = Key columns = used for row restriction and processing
WHERE, JOIN, ORDER BY, GROUP BY etc
NonKeyColList = Non-key columns = used in SELECT and aggregation (e.g. SUM(col)) after selection/restriction


How should I log changes to an object when the datatype of the changed property can vary? I am trying to create a log-table for storing events to a company-object, and I am afraid I might be taking the wrong route. I've arrived at the conclusion that I should log different data-types in the same column, and it doesn't feel right. I'll explain the basic use-case with 2 tables; company and user.

company
- id int
- name nvarchar
- description

user
- id int
- name nvarchar
- company int (references company.id)

Now, say I want to log different types of events on the company-object. I'd make a log-table called company_log and company_log_type and store events there. These tables would look like this:

company_log
- id int
- old_value nvarchar
- new_value nvarchar
- log_type int (references company_log_type.id)

company_log_type
- id int
- name

Examples of interesting events are

Company-name was changed
Company-description was changed
A user was added to the company
A user was removed from the company

For the two former events, I'd store the type of event, the old value and the new value. This works fine as long as I stop here. However, if I want to log the 2 former-events, I would need to be able to store different data-types in the same column. The changes would look something like this:

old_value     new_value     log_type
----------------------------------------------
Acme          PiedPiper     CompanyNameChanged
NULL          132           UserAdded
97            NULL          UserRemoved

At this point, I would need to store the textual old/new value of the company name, as well as the id of the recently added user.You might see that I'm already headed off in the wrong direction, and this is where I ask for help. I have 2 questions:

Should I just use sql_variant-datatype, or if this is considered poor design then what would be a sensible way to store these log-events?

Thanks in advance.

There are restrictions and difficulties with sql_variant type.
See MSDN for details: SQL Variant

As you already "feel" you might also trap into problems with casting and converting lateron. For samples see 10 reasons to explicitly convert SQL Server data types.

I would try to avoid variant in this and any other case if possible.

But your basic problem is not the variant type but more your table design. Even if you had only integer values in your log tables, I would still think about changing it to some more normalization.

However after thinking about your problem I came to another approach that maybe worth a try: Why don't design your log table the same as the original table.

For example:

company_log
- id int
- name nvarchar
- description
- log_stamp datetime
- Log_type

That way you could add a new row each time the original row was changed and add the previous row into the log table. This will give you a complete history of the rows without any datatype issues.

You could also limit the logging to changes of specific columns. I think you will need triggers to realize it.

It will give you multiple log tables but if you keep them similar regarding log_type and log_stamp you could still query the entire history of any changes (of tracked tables) by UNION them.

I have found another question where this and other possible ways to solve it are explained pretty detailed: How to store historical records in a history table in SQL Server


Cognos, modifying canned report. Namespace of tag doesn't exist I want to modify a canned report to add more information to what it already provides. However, when I go to one of the data items, to see where it originates from so that I can know where to add my own data item from, I don't understand the expression given.

For instance, the finished Query has a data item Patients Name. Here is the expression for Patients name:[NO Group No Resource].[CPT With OUT Resource Proivder No Groups].[Patient Name]

However, there is no namespace[NO Group No Resource].

Can anyone help?

I'm copying the comment I made above here so that I can set this question as 'Answered'.

I think the reason for this was because the entire thing was written in sql. Some of the reports provided for us do not use the Data Items given to us, they are just written in SQL


Split a large MySQL dump per database I have a large MySQL data backup file which consists all the databases. But I want to restore only a few number of databases from above.

Is there a good way to split the sql file into several files per database?

Without splitting you can restore specific database using this command

 mysql -u root -p --one-database database_name < main-db-dump.sql

First create the database. Then apply this command with correct database_name. Follow the steps for each database.


DateDiff Returning Dates and Not Integer Values I've added a [DAYS_FROM_TODAY] column to my table to calculate the number of days between today's date and the date a check was written for each record in that table.

I used the following code to update the table:

UPDATE [dbo].[Unpaid]
SET [DAYS_FROM_TODAY] = DATEDIFF(dd, CHECK_DATE, GETDATE())

However when I look at the top 1,000 records of the [dbo].[Unpaid] table, the [DAYS_FROM_TODAY] column has the following appearance instead of integers:

DAYS_FROM_TODAY

1900-09-03 00:00:00.000

1900-01-23 00:00:00.000

Both the CHECK_DATE and DAYS_FROM_TODAY columns are datetime types.
Am I missing an important step?

It's converting the integer to a datetime- the column is a datetime.

You're seeing the datetime conversion of the results of the DATEDIFF function. Implicit conversion from integer to datetime is in days beginning with 1/1/1900 as 0.

  SELECT CAST(1 AS DATETIME)

Returns

1900-01-02 00:00:00.000

If you want to store the integer result in the column, just change the data type to integer.


Database query: join tags with exact matches or at least one Given I have a query like:

SELECT count(mm.id) AS c
FROM merchant_mobile mm
INNER JOIN mobile m ON mm.mobile_id = m.id
LEFT JOIN merchant_mobile_customer mmc ON (mm.id = mmc.merchant_mobile_id)
INNER JOIN merchant_mobile_tag mmt ON mm.id = mmt.merchantmobile_id
INNER JOIN tag t ON t.id = mmt.tag_id
WHERE mm.merchant_id = ?
  AND mm.status LIKE ?
  AND (mm.last_promo_sms_date <= ?
       OR mm.last_promo_sms_date IS NULL)
  AND t.name = ?
  AND t.name = ?
ORDER BY mm.last_promo_sms_date ASC

I need to perform queries to select:

Matches for exact tag combination: eg: pretty AND blonde
Matches for any combination, but at least one of them: pretty OR blonde

Is it possible to make with just one query?
Do I need a subquery?
Is my schema ok?

This is how I solved it:

For exact combination

SELECT count(mm.id) AS c
FROM merchant_mobile mm
INNER JOIN mobile m ON mm.mobile_id = m.id
LEFT JOIN merchant_mobile_customer mmc ON (mm.id = mmc.merchant_mobile_id)
INNER JOIN merchant_mobile_tag mmt ON mm.id = mmt.merchantmobile_id
INNER JOIN tag t ON t.id = mmt.tag_id
WHERE mm.merchant_id = ?
  AND mm.status LIKE ?
  AND (mm.last_promo_sms_date <= ?
       OR mm.last_promo_sms_date IS NULL)
  AND mm.id IN
    (SELECT mm.id
     FROM merchant_mobile_customer mmc
     JOIN merchant_mobile mm ON mm.id = mmc.merchant_mobile_id
     JOIN merchant_mobile_tag mmt ON mm.id = mmt.merchantmobile_id
     RIGHT JOIN tag t ON mmt.tag_id = t.id
     WHERE t.name = ?
       OR t.name = ?
       OR t.name = ?
       AND mm.merchant_id = ?
     GROUP BY mm.id
     HAVING COUNT(*) = 3 )
ORDER BY mm.last_promo_sms_date ASC ;

For at least one

SELECT count(mm.id) AS c
FROM merchant_mobile mm
INNER JOIN mobile m ON mm.mobile_id = m.id
LEFT JOIN merchant_mobile_customer mmc ON (mm.id = mmc.merchant_mobile_id)
INNER JOIN merchant_mobile_tag mmt ON mm.id = mmt.merchantmobile_id
INNER JOIN tag t ON t.id = mmt.tag_id
WHERE mm.merchant_id = ?
  AND mm.status LIKE ?
  AND (mm.last_promo_sms_date <= ?
       OR mm.last_promo_sms_date IS NULL)
  AND mm.id IN
    ( SELECT mm.id
     FROM merchant_mobile_customer mmc
     JOIN merchant_mobile mm ON mm.id = mmc.merchant_mobile_id
     JOIN merchant_mobile_tag mmt ON mm.id = mmt.merchantmobile_id
     RIGHT JOIN tag t ON mmt.tag_id = t.id
     WHERE t.name = ?
       OR t.name = ?
       AND mm.merchant_id = ? )
ORDER BY mm.last_promo_sms_date ASC ;


How to List all roles and their members (users/groups) of an Azure Analysis Services tabular model/database I have a tabular model/database in an Azure Analysis Server instance. Model/database has roles and users/groups were added on each role. I can see the list by right click on the role and then go to membership.

My questions are:

1-Is there a script I can use to list the roles and the users in each role without using the method described? It can be using powershell, TMSL or any other ideas.

2-When you add a group as a role member, the group is displayed as a long hexadecimal string, is there a way to get the name of the group?

this should get you started (boogles my mind that there isn't a Get-RoleMember cmdlet)

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

$ServerName = "asazure://aspaaseastus2.asazure.windows.net/alignpocaas"
$DB = "alignpocaas"

$MyServer = New-Object Microsoft.AnalysisServices.Server
$MyServer.Connect($ServerName)

$myDatabase = $myServer.Databases.Item($DB)
$myDatabase.Roles


At what point does one database per client become unfeasible? For one of our systems, we have sensitive client data and store each client's data in a separate database. We have about 10-15 clients for that system.

However, we're developing a new system that will have 50-100 clients, maybe more. I am thinking it might be unfeasible to have one database per client in this instance (to store sensitive records and audit history). However I don't know if this is perfectly normal or not, or if there's another way of maintaining security.

Any thoughts on this?

Managing 100 or 500 databases is really not all that different from managing 5 or 10 - you just have to embrace automation and have a scalability plan in place (and don't plan to use high-cost-per-database features like mirroring across all clients).

At my previous job we used this architecture and I would never once have ever thought of merging two clients into a single database, even though some of the challenges can be "hard."

The big benefits are independent recovery models (a can be simple, b can be full, etc.), the ability to restore to a point in time (or remove entirely) a client without disrupting others, the ability to seamlessly move a resource-heavy client to its own storage or to a completely different server with very little in the way of transparency (you update a config file or table that tells the app where to find that client).

I address several of the objections, and/or how to approach the problems, in these posts:

Handling growing number of Tenants in Multi-tenant Database Architecture
Multi-tenant database using SQL Server 2008?
Automation of backups of Large Number of Database

That all said, I don't think any of us can tell you the point at which management becomes impractical for you - just know that whatever specific challenges you come across, you can ask about those problems individually.


Service Broker activation procedure in C# with external dependencies I am not entirely sure if I have come to the right place, because this question involves both SQL Server and .Net. Apologies if this is not an appropriate place for my question!

I am currently working on an in-house tool to transfer data from our
ERP system to our SharePoint. Specifically, my boss wants the closing
of a project in the ERP system to trigger a workflow in SharePoint.

My current line of thinking is to use the SQL Server Service Broker -
create a T-SQL trigger on the projects table in the ERP database to
send a message whenever a project is closed. The message would like
carry an XML payload with some information on the project.
I have done something very similar before, so that should not be too
much of a problem.

In the next step I would have to receive these messages, extract the
information from the XML payload and create entries in a list in our
SharePoint server.
Adding items to a SharePoint list from a C# program is also something
I have done before, so that should not be a problem either.

My question is how I should approach the transfer from the Service
Broker Message Queue to the SharePoint list.
My first idea was to write a stored procedure in C# to receive
messages from the queue, extract the relevant information from the XML
payload, and then create a list item SharePoint. (Stored procedures in C# is not something I have done before!)

As far as I could find out, this should not be a problem per
se. However, it would require access to the SharePoint client
assemblies from the stored procedure.
Also, I would like to do some logging, and being a lazy slob, I would
like to use Log4Net for that. So I would need access to two
assemblies. (More actually, I think the SharePoint client library is
split up into several assemblies.)

Can I just load these assemblies into the SQL Server along with the
one for my stored procedure, and then reference them from within the
SQL Server? If so, are there any pitfalls, things I need to keep in
mind?
Or can I just install the required assemblies on the database server
and reference them from within my stored procedure?
Another option just comes to my mind - I could use Fody Costura, I
think, to massage the additional assemblies into the one carrying my
stored procedure... Are there any significant downsides to this?

The alternative, of course, would be a program running externally from
the SQL Server. This seems in some ways to be a better alternative -
the problem with the SharePoint and Log4Net assemblies
disappears. Also, I could deploy the resulting program on an arbitrary
machine.

In that case, though, I am not sure how to interact with the Service
Broker and the Message queue. Can I just issue a RECEIVE statement via
an SqlCommand instance and extract the fields like from a regular
SELECT statement?
Would I have to finish the conversation? I have found some hints to a
Service Broker API, but where do I get the assemblies?

So, my questions, in short, are:
- Can I reference other assemblies from a CLR stored procedure?
- I so, how? What are the pros and cons?
- If now, how do I speak to the Service Broker from an external
  program? Do I need other assemblies besides the SQL Server Client?

Thank you very much for any insight you might be able to share with
me,
Benjamin

I suggest external activation rather than SQLCLR. That will provide capabilities like log4net that aren't easily doable in SQLCLR.

The trigger would begin a conversation and sends the project closure message.  The C# app would RECEIVE the closure message from the queue, do the SharePoint stuff, and end the conversation.  You'll also need to close the initiator side of the conversation to avoid endpoint leakage. Below is a single-queue example with the same SB service acting in both initiator and target roles.  Note that your C# will receive EndDialog messages for both sides of the conversation with this method.

CREATE QUEUE dbo.ProjectClosureQueue;
CREATE MESSAGE TYPE ProjectClosureRequest
    VALIDATION = WELL_FORMED_XML;
CREATE CONTRACT ProjectClosureContract (
      ProjectClosureRequest SENT BY INITIATOR
    );
CREATE SERVICE ProjectClosureService
    ON QUEUE dbo.ProjectClosureQueue ([ProjectClosureContract]);
GO

Trigger code snippet:

DECLARE @ProjectClosureConversationHandle  uniqueidentifier;
BEGIN DIALOG CONVERSATION @ProjectClosureConversationHandle
    FROM SERVICE ProjectClosureService
    TO SERVICE 'ProjectClosureService', 'CURRENT DATABASE'
    ON CONTRACT ProjectClosureContract
    WITH ENCRYPTION = OFF;
SELECT @ProjectClosureConversationHandle;

SEND ON CONVERSATION @ProjectClosureConversationHandle
    MESSAGE TYPE ProjectClosureRequest (@ProjectClosureXmlMessage);

Get next message in C# app:

WAITFOR (
    RECEIVE TOP (1)
           conversation_handle
           CAST(message_body AS xml)
        , message_type_name
    FROM dbo.ProjectClosureQueue
    ), TIMEOUT 10000;

If no message received, repeat receive.

If message_type is ProjectCloseRequest, do SharePoint stuff

If message_type is not ProjectCloseRequest or '"http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog', log error

Finally, END CONVERSATION


SQL Server DB restore fails I am trying to do a SQL Server database restore using this T-SQL code:

----Make Database to single user Mode
ALTER DATABASE xyz
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----Restore Database
RESTORE DATABASE xyz
FROM DISK = 'D:\\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\xyz_backup_201204100301.bak'
WITH MOVE 'abc' TO 'D:\\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\xyz.MDF',
MOVE 'abc_log' TO 'E:\\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\xyz.LDF'

but while running the above i get the following error:

  Msg 5064, Level 16, State 1, Line 1
  Changes to the state or options of database 'xyz' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
  Msg 5069, Level 16, State 1, Line 1
  ALTER DATABASE statement failed.

How can I run my restore successfully?
Is restarting the database the only way to solve the problem. Any other suggestions?!

Msg 5064, Level 16, State 1, Line 1 Changes to the state or options of
  database 'xyz' cannot be made at this time. The database is in
  single-user mode, and a user is currently connected to it.

This error does not occur if YOU are the single-user in the database.  It only occurs if someone else is in it AND it is ALREADY in single-user mode.

For example, try this:

create database test
--
use test
--
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Note: there are 4 separate batches, and the 4th one doesn't error.  You're setting the mode while IN the database, and also setting it a 2nd time.  No error.

Assuming you have been given the mandate to restore the DB regardless of what is happening, do an sp_who2 to look for the user who is connected, and KILL the spid.  You can then proceed with the RESTORE.


Elasticsearch: Versioning a document on revisions I currently have a document which gets revised regularly, I want to keep track of the document by keeping each old version of the document. So if document A has a summary and update date I want to, after every update, keep the previous version along with its update data.

The problem is that I'm not sure how I should do this efficiently:

...{
        Title: A
        Summary: {update_date:content, update_date:content, ...}
    }

The problem is that if I take the key as value then the automatic generated schema will take all dates as possible keys. Which is not something you want.

So my question is what's the most efficient way of tracking all revisions by date in ElasticSearch?

Elasticsearch stores data in on a document basis. Even if you are to put a slightly different data, each of the resulting document must be stored as a different document.

Note that the versioning in Elasticsearch is used for consistency, not for data tracking across different versions. That is, in short, only the latest version of a document is saved.

I think what you can do is to prepare a document for each separate version, and that's only thing you can do as long as you are using Elasticsearch.

{
    "Title": "A",
    "Summary": "CONTENT",
    "updated_date": "The date"
 }


Adding Partitions to cube In SSAS via VS 2013 I am trying to add a new partition to my cube via SSDT 2013.  I am using the Wizard and doing all stages that needed. After I click "Finish" nothing happens and I cannot see that a new partition has been added.

The answer I found from Google is to update SSDT to the latest version.  I did that and still nothing happens.

Anyone encountered this problem? Any ideas?

My SSDT version is 12.0.60525.0

I've found a solution!!! :)

Thanks to Darren Gosbell(MCC, MVP) and bayFX BITALENT(Partner) on MSDN.

This appears to be caused by a missing dll. For VS2012 / SQL 2012 running the following command fixed this for me.

copy "c:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SqlWorkbench.Interfaces.dll"
"c:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies"

For Visual Studio 2013 ( Version 12.0.21005.1 REL ) and SQL Server 2014 you can use this paths.

Copy "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\SqlWorkbench.Interfaces.dll" file to "C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies" folder.

Restart Visual Studio and Voila!


Can multi indexing speed up "AND" of comparisons? I am using PostgreSQL and have query of type:

SELECT * from t
WHERE a > 30 AND b < 20

where a and b are columns of table t. I have indexed columns a and b. Would that speed up this query or do I need to add a multi index (a, b)?

This post mentions that multi index speeds up query of type a = 3 and b = 4 and a = 3 but I am not sure if same applies for comparisons.

Also official PostgreSQL docs says that it can speed up queries of type SELECT name FROM test2 WHERE major = constant AND minor = constant, but in the later text it says many things which I don't really understand and hence asking this question here.

A multi-column index cannot be used for both conditions. For example, an index on (a, b) could be used for the condition a > 30, but not for b < 20.

If one of the conditions alone is selective enough and the other does not reduce the result set considerably, just create an index for that condition.

If you need index support for both conditions because neither of them is selective enough on its own, create two indexes, one for each condition. Then PostgreSQL can use a bitmap index scan on both indexes and a “bitmap or” to combine the results.


Migrating from PostgreSQL to SQL Server I have been tasked to migrate a database from PostgreSQL 9 to SQL Server 2008 R2, and I have found some issues I need to address. I have quite limited experiences with PostgreSQL I'm afraid.

Most keys are BigInt. In PostgreSQL a function is called to get the next ID and this is done in advance in the code for whatever reason.  Can this be done in the same manner in SQL Server or am I forced to create an key index table to keep track of the current ID. IDENTITY(1,1) will afaik deliver a new ID on the insert.
There are some stored procedures that returns NEW as a TRIGGER. What would be the equivalent in the SQL Server language?
What impact has the difference in triggers given that PostgreSQL triggers are row based?

You can create your keys using a SEQUENCE. Every time you need a new ID from the sequence, you just have to issue SELECT NEXT VALUE FOR SomeSequence;. Unfortunately not all versions of SQL Server support sequences (it's 2012 and up), so if you're stuck with SQL Server 2008 R2 you will have to code it by yourself.

See Sequence Tables by Paul White for an example.
As far as I know, returning NEW from a BEFORE trigger means the INSERT or UPDATE operation that originally fired the trigger will take place. The trigger function may change the column values of it.  For a description and examples check the documentation.

You can achieve the same thing in SQL Server with regular DML statements such as UPDATEs and INSERTs, joining the destination table with the logical tables INSERTED and DELETED. You could also decide to implement the operation on behalf of the original statement, using an INSTEAD OF trigger.
Triggers in Postgres are either row-based or statement-based. Translating FOR EACH ROW into joins to INSERTED/DELETED is just a matter of using the correct keys to correlate the tables.


Does a transaction cover the subroutines? When I begin transaction and exec a stored procedure, does that transaction cover the changes made by the stored procedure I executed? Would a rollback transaction cancel the changes made by the stored procedure which is executed?

Yes the rollback transaction would rollback all changes made inside the stored procedure except if the stored procedure was itself to issue an unbalanced COMMIT which would raise an error

  Transaction count after EXECUTE indicates a mismatching number of
  BEGIN and COMMIT statements.

but still lead to the transaction being committed. Both cases shown below.

CREATE TABLE T(proc_name sysname)

GO

CREATE PROC proc_1
AS
INSERT INTO T VALUES ('proc_1')

GO

CREATE PROC proc_2
AS
INSERT INTO T VALUES ('proc_2')
COMMIT;

GO

BEGIN TRAN
EXEC proc_1
ROLLBACK
/*Table is empty. Transaction rolled back*/
SELECT * FROM T

GO

BEGIN TRAN
EXEC proc_2
ROLLBACK
/*Table is not empty. Transaction was committed*/
SELECT * FROM T


Finding Item's distribution over customer routes Please help me to find a solution.

I have two tables [CustomerMaster and SalesData] as shown below.

CustomerMaster

| CODE |       NAME | ROUTE | DIVISION |
|------|------------|-------|----------|
| 1001 |  Customer1 |    R1 |     Div1 |
| 1002 |  Customer2 |    R1 |     Div1 |
| 1003 |  Customer3 |    R1 |     Div1 |
| 1004 |  Customer4 |    R2 |     Div1 |
| 1005 |  Customer5 |    R2 |     Div1 |
| 1006 |  Customer6 |    R2 |     Div1 |
| 1007 |  Customer7 |    R3 |     Div2 |
| 1008 |  Customer8 |    R3 |     Div2 |
| 1009 |  Customer9 |    R3 |     Div2 |
| 1010 | Customer10 |    R4 |     Div2 |

SalesData

| CUSTOMERCODE |               DATE | ITEM | BRAND | QUANTITY |
|--------------|--------------------|------|-------|----------|
|         1001 |  January, 01 2014  |  IT1 |   BR1 |        2 |
|         1001 | February, 01 2014  |  IT2 |   BR2 |        5 |
|         1003 |  January, 01 2014  |  IT1 |   BR1 |       20 |
|         1003 | February, 01 2014  |  IT3 |   BR1 |        6 |
|         1005 |  January, 01 2014  |  IT1 |   BR1 |        5 |
|         1005 | February, 01 2014  |  IT2 |   BR2 |       11 |
|         1007 |  January, 01 2014  |  IT1 |   BR1 |       15 |
|         1007 | February, 01 2014  |  IT2 |   BR2 |        7 |
|         1007 |    March, 01 2014  |  IT3 |   BR1 |        9 |
|         1007 |    April, 01 2014  |  IT4 |   BR2 |       18 |

I need to answer below questions

To get the Brand distribution over Route and Division

ex:BR1 reached 2 customers out of 3 in R1
ex:BR1 reached 3 customers out of 6 in Div1

To get the Item distribution over Route and Division

ex:IT2 reached 1 customers out of 3 in R1
ex:IT2 reached 2 customers out of 6 in Div1

I am looking for something like this

| Item  |  Brand|Route  | Division  | TotalCustomersCount   | BuyingCustomersCount|
|-------|-------|-------|-----------|-----------------------|---------------------|
| IT2   |BR2    |R1     | Div1      |                   3   |                   1 |
| IT2   |BR2    |R4     | Div1      |                   1   |                   0 |
| IT1   |BR2    |R1     | Div1      |                   3   |                   2 |
|       |       |       |           |                       |                     |

I am trying some queries like

select s.item,s.brand,c.division,c.route,
TotalCustomersCount=(select COUNT(distinct  x.CODE) from  CustomerMaster x where x.Route=c.route),
BuyingCustomersCount=(select COUNT(distinct y.CUSTOMERCODE) from  SalesData y where y.CustomerCode=c.Code and y.item=s.Item )
from CustomerMaster c join SalesData s on c.code=s.customercode

It will be a great if someone can help me.
My logic is wrong?

select sd.item,
       sd.brand,
       cm.Route,
       cm.Route,
       count(*) as TotalCustomersCount
from SalesData sd with (nolock)
  inner join CustomerMaster cm
    on sd.CUSTOMERCODE=cm.CODE
group by cm.ROUTE

I am not sure what BuyingCustomersCount is.


Can this duplicate 'points' data be prevented? I'm building a game in which a User does Activities, in each activity the player passes certain Areas, now after the game the player is awarded points for each area he passed through during an activity.
Therefor I assign a certain amount of points in the activity_area table.
What also happens is that the player can receive a points for a certain area of his at random moments, so points can be given for a certain area of that player without concerning an activity.
I created the user_area table to save the areas that a user owns (these are areas he has passed though during an activity). and the user_area_points table to save the random points received.
What I'm now worried about, is that 'points' are stored in two places, in the activity_area table and the user_area_points table.
Should this be further optimized?

No, there is no problem.
activity_area points are the attribute for the activity.
And the user_area_points points are an attribute to user_area.
i don't see why you put the pints into its own table, it is clearly an attribute to user_area


High CPU for SQL server running on VmWare I am trying to understand the below behavior for one of our SQL server running on VM.
SQL Server version 2017 has 64vCores and Host has 96 cores with base speed 2.1 GHZ
Based on the article from https://sqlperformance.com/2017/05/monitoring/troubleshooting-cpu-vmware i collected the counters and can see below"-
Processor% Processor Time in Task Manager or perfmon: Toggles around 80 in peak business hours
% Processor Time for VM ware is always pegging around 100%
Effective VM speed MhZ averages around 140,305 so does not look to be limited anywhere. ( Minimum 8219 and maximum 157,612)
Host processor speed is 2091
Is there a way to find what does that 100% coming from? Or is that OK to be 100 % for that counter where SQL seems to be between 75-80%?
Additional info:-
Avg Batch re/sec on high time = 30K
Blocking/deadlocks= none
Queries are generally fast like 100K executions/min and avg CPU they spend is few ms.
Mostly all the above queries are single threaded due to various reasons.
CTOP= 200 and MAXDOP =8
Thanks

thats seems to be a lot of CPU being burned and should be approached IMHO.
Do you have any performance issue?
I would try sp_blitz https://www.brentozar.com/blitz/
and if you have querystore enabled, take a look at queries that eat up the CPU.


Firebird execute block where External datasource is localhost I'm writing a .net application that connect to a remote database server hosted on internet to make a sort of replication.

The database source is on my local firebird server, the destination database is on the Firebird server hosted on the internet

This is the query

execute block as DECLARE panum INT; DECLARE nom varchar(50); DECLARE prenom
varchar(50); DECLARE dna date; DECLARE datemodif date; DECLARE VARIABLE VSQL
VARCHAR(500); begin VSQL = 'select panum, nom, prenom, dna, DATEMODIF from
patient where (datemodif is not null) and (datediff (day from current_date
to cast(DATEMODIF as date)) >= 0) ';

FOR execute statement VSQL ON EXTERNAL DATA SOURCE
'127.0.0.1:C:\BDD\D2018.gdb'
AS USER 'sysdba' PASSWORD 'masterkey' INTO :panum, :nom, :prenom, :dna,
:DATEMODIF DO

merge into patient bb using (select :panum panum, :nom nom, :prenom prenom,
:dna dna, :DATEMODIF datemodif  from RDB$DATABASE ) n on bb.panum = n.panum
when matched
then update set bb.nom = :nom, bb.prenom = :prenom, bb.dna = :DNA,
bb.DATEMODIF = :DATEMODIF when not matched
then insert(panum, nom, prenom, dna, datemodif) values(:panum, :nom,
:prenom, :dna, :datemodif);

suspend;  end;

Both servers run Firebird 3.0.3 on Windows 64 machines.

It fails with this error message :

  Error while trying to open file 12515659 : The system cannot find the
  path specified.  Data source : Firebird::127.0.0.1:C:\BDD\D2018.gdb

I have also tried localhost and my local ip adress as well as server name .

If I flip the direction so my source database is the remote server and the destination is my local server it works.

I know that's a little weird, but I have some constraints for doing it like this.

If I understand correctly from your comments, the execute block is executed on the external server, and it needs to query data from the local server.

The problem with your code - as far as I can tell - is that you seem to think the IP addresses are resolved from the perspective of the process initiating the execution (so 127.0.0.1 would be your local database). It is not, address resolution is from the perspective of the process executing the execute block (in this case: the Firebird process on the external server, so 127.0.0.1 is that external server).

You will need to replace 127.0.0.1 with the IP address of the local server for this to work. This IP address should be public, or at least accessible from the external server.

Be aware though, that if you expose both the local and external server to the public internet, that is a security risk. Consider using a VPN to connect between the local and external server(s).


Adding a CHECK constraint doesn't improve performance of ATTACH PARTITION on postgres table I'm attempting to merge a table into a partition, the merge works but the performance isn't good. I've read this link, in particular this quote....

Before running the ATTACH PARTITION command, it is recommended to create a CHECK constraint on the table to be attached describing the desired partition constraint. That way, the system will be able to skip the scan to validate the implicit partition constraint. Without such a constraint, the table will be scanned to validate the partition constraint while holding an ACCESS EXCLUSIVE lock on the parent table. One may then drop the constraint after ATTACH PARTITION is finished, because it is no longer necessary.

Here's the SQL I'm using....
ALTER TABLE mess_201811 ADD CONSTRAINT const_201811 CHECK ( loadedtime >=  DATE '2018-11-01' AND loadedtime < DATE '2018-11-23' );
ALTER TABLE mess ATTACH PARTITION mess_201811 FOR VALUES FROM ('2018-11-01') TO ('2018-11-23');
ALTER TABLE mess_201811 DROP CONSTRAINT const_201811 ;

As close as possible I've copied my SQL from the link mentioned. The three commands all work without problem, the issue is the time it takes. Adding the constraint takes about 60 seconds (fair enough, there are about 2 million records in the table). However the ATTACH PARTITION command also takes about 60 seconds, this suggests to me that table is being scanned a second time validate the partition. My understand was that with the CHECK constraint in place the ATTACH should be a simple DDL statement and would only take a fraction of a second.
The problem is that ATTACH PARTITION requires an exclusive lock on the table mess which prevents inserting records for 60 seconds.
I'm using postgres v10.5. the definition for table mess ends with
PARTITIONED BY loadedtime;

At the time of my testing there were no other users on the database and no lock issues.

Following jjanes solution, I've checked the original documentation, the example quoted does use a NOT NULL field constraint although it make no mention of this being mandatory. I'm hoping that the additional constraint won't have a noticeable effect on normal inserts to this table.

I can only reproduce this if mess_201811.loadedtime is nullable, in which case it has to be scanned for NULL values upon attachment.  If the column is NOT NULL, then the scan upon attachment is skipped.


ORA-01122: database file 5 failed verification check Oracle version: 11g r2
OS: win server 2008

SQL> alter database open;
  alter database open
  *

  ERROR at line 1:
  ORA-01122: database file 5 failed verification check
  ORA-01110: data file 5: '[file_path]'
  ORA-01210: data file header is media corrupt

  SQL> alter database datafile '[file_path]' offline;
  alter database datafile '[file_path]' offline
  *

  ERROR at line 1:
  ORA-01145: offline immediate disallowed unless media recovery enabled

  SQL> archive log list;
  Database log mode              No Archive Mode
  Automatic archival             Disabled
  Archive destination            USE_DB_RECOVERY_FILE_DEST
  Oldest online log sequence     8231
  Current log sequence           8235

  SQL> shutdown immediate;
  ORA-01109: database not open

  Database dismounted.
  ORACLE instance shut down.
  SQL> startup mount;
  ORACLE instance started.

  Total System Global Area 2.7392E+10 bytes
  Fixed Size                  2188768 bytes
  Variable Size            1.5032E+10 bytes
  Database Buffers         1.2348E+10 bytes
  Redo Buffers                9183232 bytes
  Database mounted.

  SQL> recover database;

  ORA-00283: recovery session canceled due to errors
   ORA-01110: data file 5: '[file_path]'
   ORA-01122: database file 5 failed verification check
   ORA-01110: data file 5: '[file_path]'
   ORA-01210: data file header is media corrupt

You cannot put the datafiles in Offline when your DB is in No Archive Log mode.

So below are the steps to recover the database considering current situation :

Shutdown immediate;
startup nomount;
Recreate the control file using backup trace. :

alter database backup controlfile to trace as '$ORACLE_HOME/dbs/control_new.sql';

Now make changes in the created control_new.sql:

CREATE CONTROLFILE REUSE DATABASE "db_name" RESETLOGS ARCHIVELOG
   MAXLOGFILES 16
   MAXLOGMEMBERS 3
   MAXDATAFILES 100
   MAXINSTANCES 8
   MAXLOGHISTORY 2921
 LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/db_name/redo1.log' SIZE 400M,
  GROUP 2 '/u01/app/oracle/oradata/db_name/redo2.log' SIZE 400M,
  GROUP 3 '/u01/app/oracle/oradata/db_name/redo3.log' SIZE 400M,
  GROUP 4 '/u01/app/oracle/oradata/db_name/redo4.log' SIZE 400M,
  GROUP 5 '/u01/app/oracle/oradata/db_name/redo5.log' SIZE 400M,
  GROUP 6 '/u01/app/oracle/oradata/db_name/redo6.log' SIZE 400M
 -- STANDBY LOGFILE
 DATAFILE
  '/u01/app/oracle/oradata/db_name/system01.dbf',
 ...
  '/u01/app/oracle/oradata/db_name/FCJ_DATA_TS_13.dbf'
 CHARACTER SET AL32UTF8;

RUn the file in SQL prompt :

@$ORACLE_HOME/dbs/control_new.sql

5.Mount the database :

alter database mount;

Recover database using control file:

Recover database using backup control file until cancel;
Now open the database :
alter database open;