Why doesn't `1` fit into a decimal(4, 4) column? I have a decimal(4, 4) column in MS SQL Server 2008 R2.  If I understand correctly, that means:

Precision of 4, ie up to four digits can be stored after the decimal place
Scale of 4, ie a a total of four digits can be stored

When I run an update command to set the column to 1 (update myTable set myDecimalColumn=1), I get this error:

  Arithmetic overflow error converting numeric to data type numeric.

I don't understand how that is true.  1 has no digits after the decimal and is only one digit long.

The Precision specifies the total number of digits that can be stored, the Scale specifies, how many of those digits live behind the decimal point.

A DECIMAL(4,4) therefore has four digits and all are behind the decimal point. That means you can store values form -0.9999 to 0.9999. You cannot store -1 or 1 as they have a digit before the decimal point.

SQL Fiddle

Query 1:

SELECT CAST(-0.9999 as DECIMAL(4,4)),CAST(0.123456 as DECIMAL(4,4)),CAST(0.9999 as DECIMAL(4,4))

Results:

| COLUMN_0 | COLUMN_1 | COLUMN_2 |
----------------------------------
|  -0.9999 |   0.1235 |   0.9999 |

To store 1 you have to add an additional digit as in DECIMAL(5,4). That allows you to store values from -9.9999 to 9.9999:

SQL Fiddle

Query 2:

SELECT CAST(-9.9999 as DECIMAL(5,4)),CAST(1 as DECIMAL(5,4)),CAST(9.9999 as DECIMAL(5,4))

Results:

| COLUMN_0 | COLUMN_1 | COLUMN_2 |
----------------------------------
|  -9.9999 |        1 |   9.9999 |


PostgreSQL parses string literal as record before calling cast In my PostgreSQL 9.6 database, I have a composite type, let's call that type widget.  I have a PL/pgSQL* function called text_to_widget that parses textual representations of widgets to widget values, and I have an implicit cast that calls this function:

CREATE CAST (text AS widget) WITH FUNCTION text_to_widget(text) AS IMPLICIT;

My cast is working well from variables or table values, but when I try to use the cast (either implicitly or explicitly) from string literals, I get an error:

postgres=# select 'my widget value'::widget;
ERROR:  malformed record literal: ""
LINE 1: select 'my widget value'::widget;
               ^
DETAIL:  Missing left parenthesis.

If I explicitly cast my string literal as text first, then it works fine:

postgres=# select 'my widget value'::text::widget;
      widget
-------------------
 (my,widget,value)
(1 row)

I'm just confused why Postgres is trying to parse my string literal as a record before passing it to my cast, and if there's a way to make it stop doing that.

* Note that this database is hosted in AWS RDS, so I can't use a C function for the cast.

As the linked answer on SO explains, a cast is only applicable to the exact input data type declared in the CREATE CAST definition. And a string literal is of data type unknown initially - even though the default data type of a string literal is text.

Postgres 10 ties up some loose ends for the data type unknown with this commit (with explanation).
And unknown is a pseudo-type now. Details in this commit.

But your problem still does not go away, because you try to cast a string literal directly, before the new default kicks in.

You write:

  PostgreSQL parses string literal as record before calling cast

That's almost but not exactly what happens: A string literal representing a composite or row type is required to be enclosed in parentheses. That's the immediate cause of the error. The literal is not "cast as record" and the cast would never be called. This would actually work with the default "I/O conversion cast":

SELECT '(my,widget,value)'::widget;

You might do without the custom cast altogether and just use your function directly instead:

SELECT text_to_widget('my widget value');

Does exactly the same anyway. And you don't need to cast the function parameter because Function Type Resolution is smart enough to chose the right function (as long as the call is unambiguous).


Unique constraint on multiple columns and one of them already has index There is much stuff around about 'unique' vs 'index' and I read lot of them, but still can't find explanation for my case.

In short, my database currently has a non-clustered index on two columns.
We have some inconsistent state as a result of data duplication and I want to add a unique constraint on all four columns of the table.
Since 'unique' will create an index under the hood does it still make sense to keep the old index around?

More specifically, it goes something like:

create table Stuff (
  id PK,
  col1 varchar not null,
  col2 varchar not null,
  col3 bigint not null,
  col4 bigint not null);
create index for_search on Stuff (col3, col4);
alter table Stuff
  add constraint data_integrity_above_all
  UNIQUE (col1, col2, col3, col4);

and if the answer is 'yes', then would it be a good move to specify UNIQUE (col3, col4, col1, col2); column order or is it too much implicit and not end well on maintenance?

More information about the nature of the data: col3 and col4 are another table ids (but not FK); col1, col2 -- user data.

DB: SQL Server 2012

If the purpose of the unique constraint (with its associated unique index) is simply to enforce uniqueness then you should change the column order for your unique constraint to col3, col4, col1, col2 and then your index will be redundant and can be dropped.

Otherwise there may be some benefit from having another index on your table with different leading columns, you should consider your workload, if the table is often accessed with a predicate on col1 or on both col1 and col2 and maybe that is a good case to have those columns first in the key.

Also @srutzky comment about the clustered index has merit and is worth considering.


PostgreSQL data files have size more than data itself We have a system that does some data archiving to a PostgreSQL DB. We found out that the PC storage was full due to the DB archiving. The problem is that I checked the data files residing in /var/lib/pgsql/data/base/ and they were about 70 GB in total, while when I dumped all the databases using pg_dump the output files did not exceed 24 GB. Am I missing something here or misunderstanding something? Where is this large difference in size going?

Edit: I did pg_dump to contain schema and data with the option -c to allow drop and create.

Edit 2: I investigated the DB schema file and I found out that table that contained almost 23.9 GB of the 24 GB (About 332.4 Milions rows) of data has an index on it. There is another index on another table but the table is empty.

Edit 3: The program stores values of about 1500 variables periodically, I mean all variables are recorded from 0.1 second to 1 minute or a bit more, so I think there is a huge DB access here.

Edit 4: I executed the second query here to find the size of each relation in the schema and I found out the following:

28 GB for the main data table.
about 42 GB for 3 indexes only! 24, 9, 9.

My purpose is I want to do a backup and restore frequently (Every few months). Should I care about these DB indexes when doing backup and restore or just focus on my data tables?

Size can differ due to several reasons:

indexes take some disk space,
there can be several copies of the same record on disk,
slack space in pages.

Indexes take up disk space in order to facilitate faster lookup. The more indexes you have, the more disk space your DB will take up. GIN indexes are usually smaller, but aren't useful if you use range queries.

PostgreSQL supports concurrent access, which is implemented so, that updates and deletes on records only change the visibility of the records and not actually delete or overwrite the data, since one record may be used by another transaction. With update, a new (updated) copy gets then added. Both mean that old data is still written on disk. To free it up, PostgreSQL periodically performs vacuuming, which really removes the deleted records (after no transaction is using them).

PostgreSQL has default block size of 8KB. If your records are big, say 5KB, you can only get one record in one block, having fairly large (~3KB) slack.

Some possible solutions are:

Be sure to know how to check for disk usage in PostgreSQL,
reevaluate if you really need all the indexes,
think about what your data access pattern is - if it's append only table, then there should be very little invisible records. If on the other hand your data is changed for a time and gets archived after a period, then it's quote possible, that multiple records are on disk. If this is the case, then partitioning your table could help, since you can vacuum partitions separately.
you can vacuum the table manually. Don't use VACUUM FULL, since it will write a new copy of the table and requires appropriate amounts of disk space.
you can use some of the online (online as in you don't need to shut down the DB) repacking tools, such as pg_repack.

Edit:

pg_dump

pg_dump is okay, if you specify to dump data, as you've said you did. If you use custom format (-Fc flags), pg_restore will be able to do some extra stuff with it, such as load only specified tables, also see pg_dump's man page. Custom format gzips dump by default. This may slow your dumping, so you may want to disable that, and if you still want data gzipped, there's a parallel gzip (pigz).

Backups

When you backup, you backup data in tables. Indexes get recreated from data in tables. If you can afford heavy IO on the database when you are doing backups and restores, then pg_dump and pg_restore may suit your needs. When dumping, pg_dump only dumps tables, and when restoring, indexes get rebuilt automatically as part of restore. So to make it clear: backups don't care about indexes.

If heavy IO and degraded performance is not acceptable, then you might want to have a secondary replication server, which will have a copy of data, but won't serve your ordinary queries, so you can use it to make dumps.

If you need point in time restore capability, you could setup WAL log (write-ahead log) archiving, which then enables you to restore to a specific transaction, but this is quite advanced. There are tools to help, such as Barman.

Big indexes

As you've already discovered, indexes can take up a lot of space. If you add up your data and indexes, you get your DB size: 28GB + 42GB = 70GB.

Having big indexes means there's a lot of extra disk space used. Index data is also cached in memory, so having big indexes means you may have two copies of indexed data in RAM, which means there's less RAM for caching data on disk and you get more cache misses. There are some options to evaluate to make indexes smaller:

partial indexes: If the application only queries where a one field is set to a specific value, you can limit which rows are indexed with a WHERE clause on CREATE INDEX.
make use of index scan: If you can include all the queried data in an index, PostgreSQL can return by reading index data only. Works since PostgreSQL 9.2, see more about index scans on wiki.
use another type of index: If your application only does equality lookup (WHERE a = 4), GIN indexes can be a lot smaller, see btree_gin extension. PostgreSQL 9.5 will also bring BRIN indexes, which are really useful for fields, which have a monotonous increasing or decreasing pattern, such as timestamps and they are really really small, so you can free up both disk space for more storage and RAM for caching more data.

But ... a lot of this depends on how data is accessed, which is often only known by developer of the application.


How to avoid using the IN clause in sql query used in a stored procedure Is there a better way to write the SQL other than using the IN clause in the below given SP. When i am using this IN clause, i suffer a performance dip due to large number or records involved in the User and Member tables.

CREATE PROCEDURE [dbo].[sp_MemberIdsFromUserIds] @dtUserIds UNIQUETABLE readonly
AS
  BEGIN
      SELECT userID,
             Memberid
      FROM   Member
             INNER JOIN USER
               ON UserMemberID = MemberiD
      WHERE  userID IN (SELECT UniqueId
                        FROM   @dtUserIds)
  END

Kindly suggest the alternative as i don't have a full knowledge in writing SP

Where exists is gnerally faster than IN

SELECT userID,
             Memberid
      FROM   Member
             INNER JOIN [USER]
               ON UserMemberID = MemberiD
      WHERE  EXISTS  (SELECT *
                        FROM   @dtUserIds a where [user].userId = a.Uniqueid)


SQL Server 2008 - SIMPLE database log file is HUGE relative to the actual DB? So... I'm not a SQL Server DBA but was asked to look at a customer's situation where they have a 2GB database that has a 60GB transaction log. I've read up on a whole lot of articles on the web (Aaron Bertrand's information comes up frequently) but am still unable to explain the reason or the fix.

The database is in SIMPLE recovery mode. The log file has 0 available space.  I've tried to shrink it and only reclaimed ~6GB -- I know this is bad but the customer is at 1.5GB of free space on the drive so they're concerned about the other DBs on the instance.

I know the application does a huge number of imports but not sure if that's the reason for the huge log size and performs replication.  I ran looked at the sys.databases information:

[REDACTED-DBNAME]   46  NULL    0x01    2014-04-30 17:43:52.713 100
SQL_Latin1_General_CP1_CI_AS    0   MULTI_USER  0   0   0   0   ONLINE  0   0   0   0
OFF 0   3   SIMPLE  2   CHECKSUM    0   1   0   0   0   0   0   0   0   0   0   0   0
0   1   0   0   0   0   1   0   0   0   0   {REDACTED-GUID} 0   6
REPLICATION 0   0   0   0

The only different/possibly-interesting information for this DB (relative to the others on this instance):

is_auto_create_stats_on = 1, where almost all other DBs are 0
is_published = 1, where almost all other DBs are 0
log_reuse_wait = 6, where almost all other DBs are 0
log_reuse_wait_desc = REPLICATION

The logs file was set to 10% growth with a max size of 2GB but it's long since blown that out of the water. I reduced the 10% auto-growth size down to 25MB but left the 2GB -- even though the max size apparently has no effect.

A full backup was performed in troubleshooting and it made no effective change to the log file size.

A lot of what I seem to have read indicates that logs grow because of applications or operations and that you should just let it happen but a 2GB database and a 60GB log file seems absurd?

Or are my minimal SQL skills just incorrect in thinking this is bad?  Any guidance would be greatly appreciated.

If either of the comment-helpers post an answer, I'll re-mark the answer for them.  I keep checking and will eventually forget to come back to check so I'm going to answer in case someone else runs into this:

The problem was exactly what Kenneth Fisher, 8bit, and Kin thought, the Transaction log was gigantic on my small database (60GB/2GB, respectively) because of failed replication.

Replication had been configured on the database but disabled for about 7 months.  I assume SQL was queuing up all of the changed-data for replication once the the repl-configuration became re-enabled.

This was absolutely a case of misconfiguration.  The customer had disabled replication in testing, moved away from using replication, but never went back and deleted the config, thereby, over time, creating the problem.

After going into SQL Replication in SSMS and deleting the configuration, ~3 mins later, the log file went from 60GB to 43MB.

Now, I'm not sure if it would've done that on it's own.  I ended up running 'Checkpoint' on the database 2x as was previously suggested with no immediate effect.  Immediate checks after those operations yielded no results.  Spot-checking the log file size a few minutes later saw the dramatic difference so again, I'm not sure if I needed to run the checkpoints but ultimately, the transaction log effectively disappeared as a result.


SQL Server Standard License over written because of Report Builder Evaluation version A colleague of mine rang me today to told me that on one of our servers the SQL Server has stopped working. If he tries to run the management studio it throws and error saying The evaluation period has expired...

But the person claims that initially he installed SQL Server 2008 R2 Standard Version (licensed copy) on the server, but later on someone by mistake has installed Report Builder 3.0 enterprise evaluation version on that machine and this has caused the SQL server license to expire.

I have never heard of such a thing. I thought Report Builder was a client application and as long as the server you are connecting to is licensed you should be able to use it, but it would never affect the licensing on the server it is connecting to.

Anyway I connected to the server itself and started going through the windows log to check when the SQL Server was installed.

In window's log almost a year ago when the SQL Server was installed , all the MsiInstaller events during SQL Server installation points to a directory

C:\appstore\2008 R2 Standard\Standard Edition\x86\setup\.....

which I think indicates that it was standard edition that was installed originally, yet after the installation was finished and the sql server service started for the first time the windows log show the following:

Question 1

am I correct in concluding that even if the person thinks he installed a standard version of sql server, it was actually an enterprise evaluation version of sql server ??

Question 2

Is it possible that an Enterprise Evaluation version of Report Builder can affect License of already installed SQL Server standard and cause it to expire?

The person is wrong; that is clearly an Evaluation Edition install, not Standard Edition. The SKUs are all on the same ISO typically, so even if you download an ISO that says Standard Edition, you can still choose Evaluation or Express during install.

Report builder should not cause this situation; however, it could also happen if you install a Standard Edition engine, and then install Management Studio from an evaluation download (for the same or different version than the engine).


Postgres - Where condition on nested joins table not working I have 3 tables:
order_items, invoice_items and invoices

In order_items model:

has_many :invoice_items

P.S an order_item may or may not have any invoice items associated with it

In Invoices model:

has_many :invoice_items

In InvoiceItem model

belongs_to :order_item
belongs_to :invoice

order_items table has booked_quantity and cancelled_quantity fields in it

invoices table has state field in it. state can be cancelled or approved

invoice_items table has quantity field in it.

An order item is considered to be invoiceable when the following condition holds:

  (booked_quantity - cancelled_quantity - SUM(invoice_items.quantity where associated invoice is not cancelled)) > 0

I am trying to fetch all the order items which are invoiceable using single sql query. Here are the two solutions that i tried:

Solution 1:

SELECT * FROM order_items
       LEFT OUTER JOIN invoice_items ON invoice_items.order_item_id = order_items.id
       INNER JOIN invoices ON invoices.id = invoice_items.invoice_id
       WHERE invoices.state != 'cancelled'
       GROUP BY order_items.id
       HAVING ((booked_quantity - cancelled_quantity - COALESCE(SUM(invoice_items.quantity))) > 0)
       ORDER BY order_items.id ASC

Solution 2:

(
  WITH ii_ord_items AS
  (
    SELECT invoice_items.order_item_id, COALESCE(SUM(invoice_items.quantity), 0) AS uncancelled_invoiced_quantity from order_items
    LEFT OUTER JOIN invoice_items ON invoice_items.order_item_id = order_items.id
    INNER JOIN invoices ON invoices.id = invoice_items.invoice_id
    WHERE invoices.state != 'cancelled'
    GROUP BY invoice_items.order_item_id
  )
  SELECT order_items.* FROM order_items
  LEFT OUTER JOIN ii_oitems ON order_items.id = ii_oitems.order_item_id
  WHERE ( ii_ord_items.uncancelled_invoiced_quantity < (order_items.booked_quantity - order_items.cancelled_quantity))
) AS order_items

Both of them are working fine when invoiced is not in cancelled state. But in case when invoice is cancelled both of them are giving incorrect results. They seem to be considering cancelled invoices quantity as well. So i guess there's some problem with the where condition but i couldn't figure out what's the issue exactly. Any help would be appreciated. Thanks in advance !!

Sample Data:

order_items table

id      booked_quantity   cancelled_quantity
1          10                    2

invoices table

id      state
1       cancelled

invoice_items table

id      invoice_id    order_item_id     quantity
1           1              1               8

Expected Result: If i try to fetch all the order items for which invoice_items can be generated should include order_item with id 1 as well as invoice is currently cancelled. If the invoice state would have been anything other than "cancelled" then order_item with id 1 should not be present in the result.

The inner join between invoices and invoice_items turns the outer join between order_items as invoice_items back into an inner join. You need an outer join for invoices as well. And then you need to move the restriction on the invoice state into the join condition.

SELECT oi.*
FROM order_items oi
  LEFT JOIN invoice_items ii
         ON ii.order_item_id = oi.id
  LEFT JOIN invoices inv
         ON inv.id = ii.invoice_id AND inv.state <> 'cancelled'
GROUP BY oi.id
HAVING (booked_quantity - cancelled_quantity - COALESCE(SUM(invoice_items.quantity),0)) > 0
ORDER BY oi.id ASC;

Maybe an EXISTS query is what you are looking for:

select oi.*
from order_items oi
where exists (select ii.order_item_id
              from invoices inv
                join invoice_items ii ON inv.id = ii.invoice_id
              where ii.order_item_id = oi.id
                and inv.state <> 'cancelled'
              group by ii.order_item_id
              having booked_quantity - cancelled_quantity - coalesce(sum(ii.quantity), 0) > 0)
order by oi.id asc;


SQL developer licensing SQL developer is free or do you require license for oracle database to use the sql or pl/sql developer? Also, Is it secure to connect to the oracle database since it connects to the db over a remote port right?

SQL Developer the client application is free of charge.  Of course, the Oracle database would need to be licensed in a way that allowed you to connect with any tool.

PL/SQL Developer is a completely different tool produced by a different company that you would need to license.

Define "secure".  Assuming a default database install, using any tool to connect to the database is secure in the sense that, say, the password isn't sent in clear text over the network.  But all the data is, by default, sent in clear text unless you configure Advanced Security.  But there are many, many ways that a database could be relatively secure or relatively insecure because there are many, many ways to set up security and many types of things that people may want to prevent.


Assign object owner to a role other than the creator automatically on creation for a database Background:

I'll start by saying I'm fairly new to postgres, I generally work with web apps and I know my way around SQL but I'm new to both postgres and database administration. I have however read through the postgres docs for database roles.

My team at work is rather small and we're responsible for the building and deployment of a web application which uses postgres as our main database. There are about 3 of us who actively work on the database. This includes creating, altering (and occasionally dropping) tables, and doing the same to functions and types.

We've recently run into the problem of object ownership. It seems only the owner of an object can drop it from the database, be that any database object. We want reasonable freedom to be able to manipulate the database as required without messing around too much with object ownership all the time.

At the moment we haven't had the chance to automate the deployment of our database changes via migration scripts and the responsibility of running these falls to the individual leading a feature development and deployment.

Question:

We'd like a way to assign object ownership to a group role (which we would all have access to) without having to do it manually after every object creation. Is there a way to set this up globally for a schema or database, or even for a group role? If in the (likely) event that this is a really silly thing to do, does anyone have any suggestions of how we should be working in this scenario?

Things we've explored:

I have already gone through the alter default privileges but it seems this must be set for every login role (user), which feels like there's some friction meaning we might be going in the wrong direction. Past that, google and stack exchange have turned up little, though I may not be using the correct terminology.

You can't use alter default privileges because it can't reassign ownership.

What you could do instead is run for each dev:

alter user john_q_developer set role = feature_role;

This way all of the developers will by default create all their objects with feature_role as the owner.  This is pretty much the same thing as all the developers just logging on to the database as the same user, except this way they all get their own password (or their own authentication method of choice) rather than sharing one.

This has the same "friction" that alter default privileges does, in that you have to run the alter user ... one time for each create user ..., but that level of friction seems pretty small to me.

The objects won't be tagged inside the database with who created them, but you can set up the server to log that information if you want (include "%u" in the log_line_prefix, and set log_statement = 'ddl' in the configuration file postgresql.conf.)

A slightly more restricted approach would be to create a feature_owner role that can create objects, and a feature_dev noinherit in role feaure_owner role which has all rights other than to create objects, and grant all devs the role feature_dev.

That way attempts to create objects will fail by default, but anyone can just execute set role = feature_owner and then repeat the creation in order to create objects under that ownership.  It would just be common knowledge that only the feature lead is supposed to make use of this power under normal circumstances.


SQL 2008 Merge Replication and table-level data compression What would happen if you enable data compression (either row or page-level) on a replicated table (merge replication) where the publisher is SQL 2008 Enterprise (Supports data compression) and the subscriber was SQL 2008 Standard (does not support data compression). Would the compression attribute get politely dropped  and the subscriber continues happily not-compressed or would it cause replication to hit an error and stop?

It will fail if you try to save replicate including compression, but if you set it to only "replicate the partition scheme but not compress the data on the Subscriber."

  The Distribution Agent does not check for down-level Subscribers when it applies scripts. If the replication of compression is selected, creating the table on down-level Subscribers will fail. In the case of a mixed topology, do not enable the replication of compression.

Source : http://msdn.microsoft.com/en-us/library/cc280449.aspx

Following from that, what happens when you restore a backup from sql 2008 enterprise with some table compression enabled, onto a sql 2008 standard server instance.. I've not actually tried and it's an interesting concept.


Order by column which casted to varchar situation is that, i want to prepare datasets for front end chart. the raw data from table contains
LAC | FLUID_MERIT|FA_BDATE   |GRP |FARM_FK
------------------------------------------
0   |234.56      |2020-01-01 |12  |10048
1   |234.56      |2009-01-01 |13  |10048
10  |234.56      |2020-01-01 |13  |10048
0   |234.56      |2020-01-01 |13  |10049
2   |234.56      |2009-01-01 |12  |10049
3   |234.56      |2009-01-01 |12  |10048
0   |234.56      |2020-01-01 |12  |10048

so to prepare the average dataset i want is group by LAC and beside there is condition for LAC = 0 where it must be separated by month range. the query below is a sample
select N'0 ( 01-06 M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet
from HerdAnalytics_tbl
where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) <= 6

union

select N'0 ( 07-12 M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet
from HerdAnalytics_tbl
where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) > 6 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) <= 12

union

select N'0 ( 12 < M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet
from HerdAnalytics_tbl
where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) > 12

union

select CAST(LAC as varchar),CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet
from HerdAnalytics_tbl where GRP = 12 and LAC in (select LAC from HerdAnalytics_tbl
where FARM_FK = 10048 and LAC != 0)
group by LAC

union

select 'TOTAL' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet
from HerdAnalytics_tbl
where GRP = 12

when i use union to merge them i could not use order by in the 4th query and also it sort by LAC as a varchar data so lac 10 comes after 1 since it is a varchar as shown below
YDS           | DataSet
------------------------
0 ( 01-06 M ) | 117.78
0 ( 07-12 M ) | 465.26
0 ( 12 < M )  | NULL
1             | 292.58
10            | -62.55
2             | 321.40
3             | 278.24
4             | 308.68
5             | 267.48
6             | 229.36
7             | 165.18
8             | 105.14
9             | 65.68
TOTAL         | 149.95

is there any way to sort properly the 4th query

The Order by clause is the only one that guarantee the order.
But if you really want in back end , then you can add some additional columns/attributes to make it easier.
I added a  grp = group level and a subgroup grp_2 =group level 2.
For 0 ( 01-06 M ),0 ( 07-12 M ),0 ( 12 < M ), the grp = 0
For monthly records, grp = 1
For TOTAL, grp = 2
And for grp_2, we have: 0 ( 01-06 M )grp_2 = 1,0 ( 07-12 M ) grp_2 = 2,0 ( 12 < M ) grp_2 = 3
For monthly records, grp_2 = LAC
For TOTAL, grp_2 = 0
And so, we are ordering by ORDER BY a.grp,a.grp_2
SELECT a.YDS,a.DataSet
FROM
(
    select N'0 ( 01-06 M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet ,0 as grp,1 as grp_2
    from HerdAnalytics_tbl
    where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) <= 6

    union

    select N'0 ( 07-12 M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet,0 ,2
    from HerdAnalytics_tbl
    where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) > 6 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) <= 12

    union

    select N'0 ( 12 < M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet ,0,3
    from HerdAnalytics_tbl
    where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) > 12

    union

    select CAST(LAC as varchar),CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet ,1,Lac
    from HerdAnalytics_tbl
    where GRP = 12
        and LAC in (select LAC from HerdAnalytics_tbl where FARM_FK = 10048 and LAC != 0)
    group by LAC

    union

    select 'TOTAL' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet ,2,0
    from HerdAnalytics_tbl
    where GRP = 12
)as a
ORDER BY a.grp,a.grp_2

output:
YDS             DataSet
0 ( 01-06 M )   NULL
0 ( 07-12 M )   NULL
0 ( 12 < M )    234.56
3               234.56
4               234.56
10              234.56
TOTAL           234.56

dbfiddle


Report server scheduled job location I inherited a SQL Server environment. In (database engine) SQL Server Agent are two jobs defined that apparently belong to the report server.
First and only step in the first job:
exec [ReportServer].dbo.AddEvent @EventType='CacheInvalidateSchedule', @EventData='c5b50dad-33bb-40b8-a696-59b1a555e8e4'

First and only step in the second job:
exec [ReportServer].dbo.AddEvent @EventType='RefreshCache', @EventData='f1ae0c17-b150-4861-8d9a-8552036123a3'

Any ideas how these two jobs were originally created and what they do? When I check the report server, there are no schedules defined:

The connected report server is empty:

First I ran
Use ReportServer
select * from ExecutionLog3 order by TimeStart DESC

This gave me a list of events that I matched with the scheduled times of the jobs:

So I went to the portal and checked the dataset. Turns out caching was enabled:


alter table alter column causes huge log and data file growth I have a table with 2.6B rows in it and I need to change a column from Char(1) to Varchar(64).  the table is over 300GB with a few indexes.  The column in nullable, so the transaction i'm running is:
ALTER TABLE XXXX ALTER COLUMN YYYYY varchar(64) NULL

I understand that this is a logged operation, so i pre-sized the transaction log to 300GB thinking it would be enough especially with log backups every 5 minutes to allow log space reuse.  well the transaction log grew to 812GB before I had to cancel the transaction due to drive space issues.
I also experienced a very large increase in data file USED size which I don't know why that would be.  data file used size came to right around 200GB (very minimal transactions are done in this DB so i know the increase is from this alter table command).
I have some questions:

Why would the data files experience an increase when just changing from char(1) to varchar(64)?  I was under the impression that this should not change the amount of data stored unless that space is actually needed since its varchar.  all existing values were either null or 1 byte since char(1) is the existing datatype, no existing data needed to be expanded.
While trying to figure out how to do this better, I came across this answer from Aaron Bertrand and it seems like it would work in my case as well.  would this be a better way to accomplish this task?
this DB is in a 2 node synchronous commit AG and i noticed the redo_queue_size got to over 130GB, which means that the primary was sending log data at a faster pace than the secondary could apply them. This means that the log on the primary could not truncate when the log backups were completed.  is this a normal behavior for something like this?   is the alter column statement processed as one large transaction?  if so that would explain why the log kept growing.

minimum storage for a VARCHAR is 4 bytes, I think, so you did change the size of the column from 1 byte to at least 4 bytes, causing (as you have observed) large amounts of data movement as the column grows and it shuffles other things around it.

I would recommend Aaron's approach as a faster method to achieve the same end.  The downside to it is that column order is not preserved....  but since we should always (lol) use named columns for our selects and inserts, the order of storage shouldn't matter.   (may need to refresh frameworks....)

You are correct.  DDL is a logged transaction and thus the behavior you observed in your log file / AG health is to be expected.


Postgres won't use btree index in left-anchored LIKE query Using Postgres 9.5. The Postgres documentation says that the btree index can be used for left-anchored "like" queries (i.e. "%" only at end). But my left-anchored "like" queries on a 1 billion row table won't use a btree index, and my queries are taking forever. What am I missing? Here's a small example that demonstrates the problem:

-- generate a table of 100K integers with random texts
CREATE TABLE test(num integer, string text);
INSERT INTO test(num, string) (SELECT generate_series(1, 100000), md5(random()::text));

CREATE INDEX test_string ON test USING btree (string); -- b-tree index

EXPLAIN SELECT * FROM test WHERE string LIKE 'asdf%'; -- will use seqscan
EXPLAIN SELECT * FROM test WHERE string = 'asdf'; -- will use index
SET enable_seqscan=off; -- tell query planner to avoid seqscan if possible
EXPLAIN SELECT * FROM test WHERE string LIKE 'asdf%'; -- will still use seqscan

Finally found this after digging through a bunch of tutorials that suggested I was already doing it the right way: http://www-old.bartlettpublishing.com/site/bartpub/blog/3/entry/329 . Relevant documentation is here: https://www.postgresql.org/docs/9.5/static/indexes-opclass.html

Looks like I'm using locale settings (that I didn't touch, just left default) that won't work for btree "like" index usage. I could have sworn that I wasn't seeing this problem before, and maybe I wasn't. I now have to do CREATE INDEX test_string ON test USING btree (string text_pattern_ops);


Create DDL Trigger Dynamically I'm trying to create a DDL Trigger dynamically so I can create a job that checks each database and creates the trigger if it's not already in place. To do this, I'm using sp_foreachdb. The code works on it's on but I can't get it to work with sp_foreachdb so I'm guessing I've missed a single quote. However, I'm struggling to find the error. Any help is greatly appreciated.

EXEC master.dbo.sp_foreachdb
'
IF NOT EXISTS (SELECT * FROM sys.triggers T WHERE T.name =
''Database_DDL_Audit'') AND ''?'' <> ''TempDB''
BEGIN
EXEC
(
''
CREATE TRIGGER Database_DDL_Audit ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
DECLARE @EventDataXml   XML
DECLARE @SchemaName     SYSNAME
DECLARE @ObjectName     SYSNAME
DECLARE @EventType      SYSNAME

SET @EventDataXml = EVENTDATA()

SELECT
@EventType  = @EventDataXml.value(''''(/EVENT_INSTANCE/EventType)[1]'''', ''''SYSNAME'''')
, @SchemaName = @EventDataXml.value(''''(/EVENT_INSTANCE/SchemaName)[1]'''', ''''SYSNAME'''')
, @ObjectName = @EventDataXml.value(''''(/EVENT_INSTANCE/ObjectName)[1]'''', ''''SYSNAME'''')

INSERT [ServerAdmin].[dbo].[ServerChangeLog]
(
[CreateDate]
, [LoginName]
, [ComputerName]
, [ProgramName]
, [DBName]
, [SQLEvent]
, [SchemaName]
, [ObjectName]
, [SQLCmd]
, [XmlEvent]
)
SELECT  GETDATE()
        , SUSER_NAME()
        , HOST_NAME()
        , PROGRAM_NAME()
        , @EventDataXml.value(''''(/EVENT_INSTANCE/DatabaseName)[1]'''', ''''SYSNAME'''')
        , @EventType
        , @SchemaName
        , @ObjectName
        , @EventDataXml.value(''''(/EVENT_INSTANCE/TSQLCommand)[1]'''', ''''NVARCHAR(MAX)'''')
        , @EventDataXml
''
)
END
'

The error I'm getting is

  Msg 102, Level 15, State 1, Procedure Database_DDL_Audit, Line 40 [Batch Start Line 0]
  Incorrect syntax near '@EventDataXml'.

You are missing an END. See updated as below:

Note: Use Aaron's version of sp_msforeachdb

declare @sql nvarchar(max)

set @sql = '
IF NOT EXISTS (SELECT * FROM sys.triggers T WHERE T.name =
''Database_DDL_Audit'') AND ''?'' <> ''TempDB''
BEGIN
EXEC
(
''
CREATE TRIGGER Database_DDL_Audit ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
DECLARE @EventDataXml   XML
DECLARE @SchemaName     SYSNAME
DECLARE @ObjectName     SYSNAME
DECLARE @EventType      SYSNAME

SET @EventDataXml = EVENTDATA()

SELECT
@EventType  = @EventDataXml.value(''''(/EVENT_INSTANCE/EventType)[1]'''', ''''SYSNAME'''')
, @SchemaName = @EventDataXml.value(''''(/EVENT_INSTANCE/SchemaName)[1]'''', ''''SYSNAME'''')
, @ObjectName = @EventDataXml.value(''''(/EVENT_INSTANCE/ObjectName)[1]'''', ''''SYSNAME'''')

INSERT [ServerAdmin].[dbo].[ServerChangeLog]
(
[CreateDate]
, [LoginName]
, [ComputerName]
, [ProgramName]
, [DBName]
, [SQLEvent]
, [SchemaName]
, [ObjectName]
, [SQLCmd]
, [XmlEvent]
)
SELECT  GETDATE()
        , SUSER_NAME()
        , HOST_NAME()
        , PROGRAM_NAME()
        , @EventDataXml.value(''''(/EVENT_INSTANCE/DatabaseName)[1]'''', ''''SYSNAME'''')
        , @EventType
        , @SchemaName
        , @ObjectName
        , @EventDataXml.value(''''(/EVENT_INSTANCE/TSQLCommand)[1]'''', ''''NVARCHAR(MAX)'''')
        , @EventDataXml
END
''
)
END
'
print @sql


Query Multiple SQL Server Instances using Powershell referencing the list of instances in a text file Rather than issuing a query per instance via SQL Server Management Studio, we are trying to create a powershell script to query multiple instances by going through a loop referencing a text file where it has let's say 100 SQL Server instances.  Go through each SQL Server instance, issue a query, and export to CSV.

The below is the powershell script we currently have:

$ServerInstance = "C:\Users\<NAME>\Documents\InstanceList.txt"

foreach ($i in $ServerInstance)
{

$sql = "SELECT
    DB_NAME(dbid) as DBName,
    COUNT(dbid) as NumberOfConnections,
    RTRIM(loginame) as LoginName,
       RTRIM(Hostname) As HostName, Login_Time,Program_name
FROM
    sys.sysprocesses
WHERE --DB_NAME(dbid) = 'genesys_wfm' and
    dbid > 5
       --and HostName = 'xxxx'
       and loginame not in ('NT AUTHORITY\SYSTEM','ACE','domain\xxxx')
GROUP BY
    dbid, loginame,Hostname, Login_Time,Program_name
       order by Login_Time desc;"

Invoke-Sqlcmd -ServerInstance $i -Query $sql -ConnectionTimeout 60 -QueryTimeout 99999

The below is the InstanceList.txt:

servername\instance name1
servername2\instance name2

and so forth.

The easiest way to do this (read: least amount of additional code you need to write) is to use Invoke-DbaQuery from the dbatools PowerShell module.

Remove the foreach loop - this function will handle it for you. Pass the list of instances into Invoke-DbaQuery and include the -AppendServerInstance switch, and all the instances will be queried and the name of each included in the output - just like when you do a multi-instance query in SSMS. You'll get one object back which is a collection of the results of the query against each instance.

$InstanceList = get-content "C:\Users\<NAME>\Documents\InstanceList.txt";
Invoke-DbaQuery -ServerInstance $InstanceList -Query $sql -QueryTimeout 99999 -AppendServerInstance

Since you don't mention script performance/elapsed time being an issue, I intentionally did not mention running multiple queries in parallel and then merging the results.


Error PRVG-11322 while atempting to install ODAC 12c release 3 I have been trying to install the Oracle Developper Tools with ODAC update 3 (because I need it to work with Entity framwork 6) but the Unified installer bring me a PRVG-11322 Error.

It seem to be linked to the fact that my computer have an underscore in his hostname (and I can't change it, work domain-joined workstation)

Altought the install instructions mention installation using xcopy, I can't find the instruction that are supposed to be in the zip package.

I am pretty much out of ideas. Do you know how to do it.

use the installer in commandline mode and use the option to ignore prerequisite checks


Unable to connect to remote SQL Server instance after thorough troubleshooting I am at the end of my rope troubleshooting a remote SQL Server connection problem. I am a DBA but not a networking expert.

Certain information below has been sanitized using "x" in place of a letter.

I have access to a remote Windows 2008 R2 server through RDP. I connect to this server with the address jxxxxxx.sxxxx:5000. This succeeds.

Using this RDP connection I installed MS SQL Server 2014 Express on the server. This has been upgraded to SP2 Cumulative Update 8. I used the checkbox to install a default, unnamed instance. Using SQL Server Management Studio from the server I can connect to and use this new instance.

I have already performed the following configuration steps:

SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for MSSQLSERVER > enabled TCP/IP. I did remember to restart the service by going to SQL Server Services and restarting SQL Server (MSSQLSERVER).
In the firewall I created an inbound rule to allow all TCP traffic on 1433.

On my home computer I opened SQL Server Management Studio 2017 and attempted to connect with jxxxxxx.sxxxx. I am unable to connect, receiving the error message below:

Cannot connect to jxxxxxx.sxxxx.

------------------------------
ADDITIONAL INFORMATION:

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

To troubleshoot this I have gone to extremes:

On the server I used SSMS to connect to the instance and checked the instance properties. "Allow remote connections to this server" is checked.
I pinged jxxxxxx.sxxxx successfully and tried using that IP address for my home computer's SSMS connection string.
In Configuration Manager I right-clicked on the TCP/IP protocol and scrolled down to IP/All. Dynamic Ports is null and TCP Port is 1433. Though it shouldn't be necessary, I set every other IP type in that menu to "Enabled".
By using SSMS on the server I checked the SQL Server log and confirmed that it is listening to ip4  and ip6  for port 1433.
Though it shouldn't be necessary, I enabled SQL Server browser (I allowed UDP port 1434 inbound on the server firewall, set the browser service to automatic in Configuration Manager, and started it)
Though it shouldn't be necessary, I tried a more explicit connection string from my client SSMS: jxxxxxx.sxxxx,MSSQLSERVER:1433.
Again shouldn't matter because outbound should be allowed, but I fully turned off the client firewall.
Enabled named pipes, and in the Native Client settings enabled TCP/IP and Named Pipes, and restarted the service. I know that this should be unnecessary, but I'm going crazy.
Restarted both computers
Slept on it.

I have no more ideas and I need help. Is there some way that my connection string is wrong, or could be incorrect for some network environments? The Server is not in a domain and is named Oxxxxxxx, if that helps...

Something else you can have a look at is whether the TCP protocol for SQL Server is listening on the correct IP address(es). You can do this from SQL Server Configuration Manager.

Go the same screen where you enabled the TCP/IP protocol, in your case SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for MSSQLSERVER and right-click on TCP/IP. Once the new screen loads, click on the IP Addresses tab, and you will see something similar to this:

The IP that you are connecting to remotely should show up in one of these fields (e.g. IP1, IP2, etc.). To find out what the IP you are using resolves to, run
the following in a cmd session:

    C:\Users\hs>nslookup jxxxxxx.sxxxx
    Server:  UnKnown
    Address:  192.xxx.xxx.xxx

    Non-authoritative answer:
    Name:    jxxxxxx.sxxxx
    Addresses:  192.xxx.xxx.xxx <- This is the address

By default SQL Server listens on all addresses, but in some cases, the default isn't used and addresses need to be explicitly added. Note that if you are using NAT or port-forwarding, you might not see the external IP, in which case you will need to work with your network administrators to make sure the translation or forwarding address is correct.


Query that returns a unique ID for each different clause matched? I'm in a situation where I'm being submitted a (potentially quite long) list of "match entities", each of which contains user data to match, along with a unique ID for that match information.  Actual user data from matched users, along with the unique ID for that match, needs to be returned from my SQL query.  So, let's say I were given 2 entities to try and match users against, both of which were trying to match phone numbers against a user's phone number; I could match any users associated with the entities submitted along with the "match entity"'s unique ID by using a union like this (client_handle is the submitted unique ID):
SELECT
    [client_handle] = 'axtwe-wasst',
    [user_id],
    [email],
    [mobile_no],
    [firstname],
    [surname]
FROM
    [dbo].[vAPP_UsersActive]
WHERE
    [mobile_no] in ('+44 7747 122123', '+44 7904 223323')

UNION

SELECT
    [client_handle] = 'zjfft-albwq',
    [user_id],
    [email],
    [mobile_no],
    [firstname],
    [surname]
FROM
    [dbo].[vAPP_UsersActive]
WHERE
    [mobile_no] in ('+44 7758 444111', '+44 7758 444222', '+44 7758 444333')

The trouble with this method is that it could potentially result in a very large number of UNIONs if large numbers of match entities are submitted to me.  1000 submitted match entities would result in 999 UNIONs.  Is this actually an issue performance-wise, and is there a better way to achieve the result I want?  Alternatively I could just loop through each submitted match entity and run a query to match each, but then I'd have 1000 separate queries if 1000 match entities are submitted, which seems even worse.

Schematically:
CREATE TABLE tmp ([parameters group] INT, [client_handle] CHAR(11), [mobile_no] CHAR(15));

INSERT INTO tmp VALUES
(1,'axtwe-wasst','+44 7747 122123'),
(1,'axtwe-wasst','+44 7904 223323'),
(2,'zjfft-albwq','+44 7758 444111'),
(2,'zjfft-albwq','+44 7758 444222'),
(2,'zjfft-albwq','+44 7758 444333');

SELECT DISTINCT
    [client_handle] = tmp.[parameters group],
    [user_id],
    [email],
    [mobile_no],
    [firstname],
    [surname]
FROM
    [dbo].[vAPP_UsersActive] t
JOIN
    tmp ON t.[mobile_no] = tmp.[mobile_no];

PS. [parameters group] is not used - on shown source data it is excess, but may be useful in real task (if so then GROUP BY instead of DISTINCT must be used).


What is the standard de facto unit test framework for Postgres? I am new to Postgres and as I am about to start writing more and more code with it (script with procedures, etc.) I am wondering what is the "standard de facto" (more used, bigger community, open source, etc) unit test framework for it. Googling I have found a few options. Is PgTap the right choice?

pgTAP

pgTAP is the only I know that is ever even talked about.

  pgTAP is a suite of database functions that make it easy to write TAP-emitting unit tests in psql scripts or xUnit-style test functions.

github
web
pgxn


Does wsrep_cluster_address only accept the intranet IPs? I was installing Percona XtraDB Cluster with 3 nodes.

Each node has a unique Internet IP address.

Following the docs Quick Start Guide for Percona XtraDB Cluster, I got the first node running but failed to get the other two nodes working.

So I am doubting that the wsrep_cluster_address only accepts the intranet IPs, is that true?

You can use any IP address in wsrep_cluster_address (you can use hostnames too). You need to make sure that all ports required are open for Galera communication (3306, 4444, 4567, 4568). There are many other reasons for "failed to get the other two working" so log files would be very helpful.


Oracle save mandarin characters with we8iso8859p1 Im trying to save Mandarin characters into Oracle instance database 11g.
Field datatype - NVARCHAR2 ;
NLS_LANG - AMERICAN_AMERICA.WE8ISO8859P1

If I change NLS_LANG, it will affect other data already in this huge database but I need it to support manadarin as well.
Im also searching for some way if available on JAVA side while inserting these characters into DB.
Please help.

Changing the NLS_LANG value does not affect any data in the database. However setting character set to WE8ISO8859P1 will not work.

When you set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 then you tell the database "The client uses character set WE8ISO8859P1." Thus any Mandarin characters will not be accepted by the database because they are not supported by WE8ISO8859P1.

Use AL32UTF8 or any other character set which supports Mandarin, for example ZHS32GB18030

Apart from that, Java does not use the NLS_LANG setting at all, see Database JDBC Developer's Guide - Globalization Support:

  Starting from Oracle Database 10g, the NLS_LANG variable is no longer
  part of the JDBC globalization mechanism. The JDBC driver does not
  check NLS environment. So, setting it has no effect.

Please have a look at this document how to setup Unicode in Java environment.

Of course, the database NCHAR Character Set has to support Mandarin. Usually this is set to default AL16UTF16, so I assume that's not the issue here.


Mongodb not listening on all interface Infrastructure : I have 3 virtual machines ip in a DMZ on a same lan.
one of them is running mongodb . OS : RHEL

Mongod.conf : I have commented out the bindIP option

But still mongodb is not accpeting connection from other servers on LAN. Telnet is failing from other 2 servers

the netstat output is

Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:27017           0.0.0.0:*               LISTEN      24251/./mongod
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1508/sshd
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1621/master
tcp6       0      0 :::22                   :::*                    LISTEN      1508/sshd
tcp6       0      0 ::1:25                  :::*                    LISTEN      1621/master

i m guessing the foreign address for 27017 port should also look like ":::*"
I m not able to catch the problem here.Please help!

If you have firewall running, allow connections on port 27017, MongoDB default port.

Not sure which RHEL version you are running so below commands might change.

1. Any connections can connect to MongoDB on port 27017

iptables -A INPUT -p tcp --dport 27017 -j ACCEPT

2. Only certain IP can connect to MongoDB on port 27017

iptables -A INPUT -s <ip-address> -p tcp --destination-port 27017 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -d <ip-address> -p tcp --source-port 27017 -m state --state ESTABLISHED -j ACCEPT

For Eg:

    iptables -A INPUT -s 192.168.6.100 -p tcp --destination-port 27017 -m state --state NEW,ESTABLISHED -j ACCEPT
    iptables -A OUTPUT -d 192.168.6.101 -p tcp --source-port 27017 -m state --state ESTABLISHED -j ACCEPT

Also i personally believe commenting bind option is not a good idea.

Instead you can use below in /etc/Mongod.conf :

# Listen to local and LAN interfaces.
bind_ip = 127.0.0.1,x.x.x.x, x.x.x.x

and then

service mongod restart


Strange UPDATE results in presence of merge replication I have an MS Access front-end database that accesses an SQL Server backend. This (as far as we can tell) works flawlessly when the SQL Server database is not replicated. Once we introduce replication (from SQL Server Standard on a proper server as the publisher to SQL Server Express on a laptop as a subscriber using Merge Replication) we start getting weird database corruption that I can't explain.

What happens is that we have UPDATE queries which run against several tables in the database, and we also have Access itself doing CRUD operations against multiple tables as well (this corruption is not limited to one table). The result we see is that randomly (about 1 in 10 operations) a row other than the one we wanted to update is being updated, which overwrites data we didn't want to overwrite.

The replication, when set up, runs on demand on the laptop as a pull merge, and the corruption happens regardless of whether replication is performed. It only has to be enabled. No corruption seems to occur when replication isn't enabled.

I'm not accusing Microsoft of any fault here - it's entirely probable I've just forgotten to tick some box to prevent this. I'm just not sure what it is that I need to look for.

Edit: What I mean by corruption is this: Let's say I have rows:

ID | FirstName | LastName
--------------------------
1  | John      | Smith
2  | Emma      | Citizen
3  | Bob       | May

I then run something along the lines of:

UPDATE Table SET FirstName = "Test" WHERE ID = 1

And after that happens I end up with this:

ID | FirstName | LastName
--------------------------
1  | Test      | Smith
2  | Test      | Citizen
3  | Bob       | May

There are no error messages in any of the system tables dealing with replication. The only change in the schema is that when replication is enabled it creates the rowguid column.

So, it turns out (after hours upon hours of trying to diagnose this) that I did have an issue caused by merge replication being enabled, just not the one I thought I did. For future reference, here's what was happening...

I had a DAO query written in VBA that looked like this:

Set rs = CurrentDb.OpenRecordset("tblExample", dbOpenDynaset, dbSeeChanges)
rs.AddNew

rs.FirstName = "John"
rs.LastName = "Citizen"

rs.Update

rs.Move 0, rs.LastModified
txtPrimaryKey.Value = rs!PrimaryKeyID

rs.Close

The issue was the rs.Move 0, rs.LastModified line, which moves to the most recently updated row (the one just inserted) and then the next line retrieves the new primary key. This had multiple issues:

If two users were inserting records at exactly the same time, this would retrieve the same primary key for both users.
And of course, the triggers that run after an insert to do merge replication also change things (in other tables) and so this again returns a primary key for what the replication was doing, not what we were doing.

Basically, this code works for single-user databases (which is where it originated) but doesn't work for multi-user scenarios.

The solution (for now) is something along the lines of this, manually retrieving the correct row using all the values just inserted:

txtPrimaryKey.Value = DLookup("PrimaryKeyID", "tblExample", "FirstName=""John"" AND LastName=""Citizen""")

This has other potential issues (if you have two rows with exactly the same data, you'll probably get back the wrong primary key ID) but in this case, for this table, that isn't an issue.


How can I return the number of values in each row which match a criterion? I have a table "trial_volts" like this in an oracle database (I think it's 10g):

trial_index NUMBER
voltage_1 NUMBER
voltage_2 NUMBER
voltage_3 NUMBER
voltage_4 NUMBER
voltage_5 NUMBER

And I want to get a list of trial_index where more than N voltages are above some threshold. I had hoped there would be some function that would let me do this:

SELECT trial_index, SOME_FUNCTION(voltage_1>10,voltage_2>10,voltage_3>10,voltage_4>10,voltage_5>10) AS vOver
FROM trial_volts
WHERE vOver>3;

...but there does not seem to be such a function. I know this table design is terrible, but I didn't design it and I can't change it, so I've got to find something that works.

I think you are going to have to roll your own.

I'd do something like the below. (last query finds first 10 records that have exactly 2 readings above my threshold).

/*

Script for trial voltage query on DBSE

*/

drop table voltages;

Create table voltages(
    id  number
,   v1  number
,   v2  number
,   v3  number);

alter table voltages add constraint pk_voltages
primary key(id);

/*
add 10,000 rows
v1 between 0 and 10
v2 between 0 and 100
v3 between 5 and 15
*/

insert into voltages (id,v1,v2,v3)
select rownum, dbms_random.value(0,10),dbms_random.value(0,10),dbms_random.value(0,10)
from dual
connect by level <= 10000;

commit;

create or replace function above_threshold(p_thresh in number, v1 in number, v2 in number, v3 in number)
return number
as
l_retval number := 0;
begin
   if  v1 > p_thresh
    then l_retval := l_retval + 1;
   end if;
   if
       v2 > p_thresh
    then l_retval := l_retval + 1;
   end if;
   if
       v3 > p_thresh
    then l_retval := l_retval + 1;
  end if;
  return l_retval;
end;
/

/* display those that have 2 readings above threshold */

select id,v1,v2,v3
from voltages
where above_threshold(8,v1,v2,v3) = 2
and rownum <= 10;


Is our use case suitable for a NoSQL Database solution? We are currently running a Python web app querying a SQL MariaDB (through SQLAlchemy). Unfortunately we have run into some hard limitations of MariaDB (number of joins are limited to 61) and are looking for alternatives. Our database is not very large (generally under 2GB and growing slowly) and the entries could actually very well be broken down to simple json objects, which made us look at a NoSQL solution.

Initially this seemed like a great solution. Queries were super fast (as the DB fit into RAM) and we were happy (for a few days).

I came here to seek advice if the NoSQL DB is a recommended solution for our needs, though. So let's talk about the data:

The nature of our data is that multiple group of entries / rows generally reference back to the same object, pretty much as if you had multiple versions that relate to a single object.

Assume a document entry could look like this:

_id: 5a31...
description: Object
    location: "XYZ"
    name: "ABC"
    status: "A"
    m_nr: null
    k_nr: null
    city: "QWE"
    high_value: 17
    right_value: 71
more_data: Object
    number: 101
    interval: 1
    next_date: "2016-01-16T00:00:00Z"
    last_date: null
    status: null
classification: Object
    priority_value: "?"
    redundancy_value: "?"
    active_value: "0"

Imagine the description.location will often need to be grouped and sorted so that I can only display the $last entry for each of these grouped entries. A common query may look like this (in MongoDB):

db.getCollection('a').aggregate(
    [{ $sort:
        {"description.location": 1}
     },
     { $group:
        {_id: "$description.location"}
     }]
)

I have unfortunately found that this particular query takes very long, when the DB does not fit into memory - even with an index for description.location present in the MongoDB. (For some reason the $group aggregation operation never appears to be using the available index, while $sort actually does use it).

Either way, is this data / layout / query strategy something that resonates well with a NoSQL DB ?

I see your interest in JSON objects.

  Our database is not very large (generally under 2GB and growing
  slowly) and the entries could actually very well be broken down to
  simple json objects, which made us look at a NoSQL solution.

MongoDB is better suited for huge databases, in your case, I suggest  PostgreSQL.

You can use JSON types in PostgreSQL: https://www.postgresql.org/docs/current/static/datatype-json.html

Should be easy enough to migrate your app to PostgreSQL, because it's SQL all the way, and I doubt that you will find that stupid join limit.

  Either way, is this data / layout / query strategy something that
  resonates well with a NoSQL DB ?

Performance has costs when using NoSQL, many times, you will have to modify your code, your database schema, your server configuration. Are you prepared to pay the price?

Answering your question, I try to avoid MongoDB aggregation, preferring to manually aggregate on the spot (my source code is my SQL), but I do it for small ranges. If doing the entire table/collection, would be better to use Map-Reduce or Aggregation or custom code? It depends, who is doing the job faster?

Most of the time, people will advise to denormalize your data, but doing this can cause another range of issues. Again, pay the price.


SQL Server 2005 to 2012 Replication, cannot access replicated data I have created publication (snapshot, small table with 10 rows) on MS SQL Server 2005. Then, I created push subscription on MS SQL Server 2012. When I try to select data form table, process takes several minutes after that I got error. It looks like table is locked.
My Observations

Replication monitor shows everything is OK.
Distribution service is running on MS SQL Server 2005.
I see replication files created on network share.

Why can I not access the table ?

This is covered in Frequently Asked Questions for Replication Administrators:

  Are tables locked during snapshot generation?

  The length of time that the locks are taken depends on the type of
  replication used:

For merge publications, the Snapshot Agent does not take any locks.

For transactional publications, by default the Snapshot Agent takes locks only during the initial phase of snapshot generation.

For snapshot publications the Snapshot Agent takes locks during the entire snapshot generation process.

  Because locks prevent other users from updating the tables, the
  Snapshot Agent should be scheduled to execute during periods of lower
  activity on the database, especially for snapshot publications.


How expensive is a connection to pgbouncer? Is a connection to pgbouncer cheaper than a connection to PostgreSQL (assuming pgbouncer has already established a connection to PostgreSQL) ? How is the connection to pgbouncer made ? I found the documentation a bit unclear.

PgBouncer speaks the normal PostgreSQL protocol.  So a client application connects to PgBouncer normally, except by specifying the host and port where PgBouncer runs instead of the host and port where the PostgreSQL server runs.  (The default port of PgBouncer is 6432.)

If PgBouncer has already established connections to the PostgreSQL backend, then opening a new connection from the client to PgBouncer is very fast.  That is one of several reasons to use PgBouncer.


Unique key in SQL Power Architect? Forward Engineering I want to know if there is a way to create unique keys (not PKs) in SQL Power Architect using forward engineering.

I've searched google, saw the tutorials by the software vendor, and checked out in youtube, but I didn't came across with a solution.

Can anybody here help me out?

What I want to do is to define an unique (or alternate) key (UK/AK) for some attributes in one row.

Thanks a lot! :)

I'm running Power Architect 1.0.6 on a Mac and the following instructions work for me.

Highlight the table you want to add the constraint to
Click on the "New Index" button on the toolbar and the "Index Properties" dialog appears, with sections for the name and type of constraint and a list of columns.
Enter a name for your constraint
Tick the "Unique" button
Tick the check box next to each column over which you want to define the constraint
Finally, click the "OK" button to add the constraint.

Hope this helps.


Updating column with multiple sub-queries I have created the following query which is used to calculate a total from the results of a spatial query:

SELECT
  SUM(med_price)
FROM (SELECT
  ST_INTERSECTION(a.geom, (SELECT
    ST_SETSRID(geom, 4326)
  FROM catalogue_table
  WHERE id_number = '2211654')
  ) AS geom,
  small_price,
  med_price,
  large_price
FROM pricing_table AS a
WHERE ST_INTERSECTS(ST_SETSRID(a.geom, 4326), (SELECT
  ST_SETSRID(geom, 4326)
FROM catalogue_table
WHERE id_number = '2211654')
)) AS total_price

My tables look like this:

CREATE TABLE pricing_table
(
  gid serial NOT NULL,
  tile_id numeric,
  small_price numeric,
  med_price numeric,
  large_price numeric,
  geom geometry(MultiPolygon,4326)
)

And:

CREATE TABLE catalogue_table
(
  ogc_fid serial NOT NULL,
  geom geometry(Geometry,4326),
  id_number character varying,
  scale integer,
  scale_code character varying
)

I am trying to modify this query so that it calculates the total price for all rows, instead of just a single row with a specific id_number. However, whenever I try and modify my query to do this, I get:

ERROR: more than one row returned by a subquery used as an expression. SQL state: 21000

I would like to create a 'price' column on the catalogue table, and populate this with calculated prices, which, depending on the 'scale_code' from the catalogue_table (either small, medium or large) will calculate a total from the relevant column in the 'pricing_table'.

The trick here is to not to get overwhelmed by the fact that there is a spatial datatype involved.  Then it becomes a fairly simple aggregate query over a join between the pricing table and the catalog table where the join just happens to be a spatial intersection.

SELECT c.id_number, SUM(med_price)
FROM pricing_table p
    INNER JOIN catalog_table c ON ST_INTERSECTS(ST_SETSRID(p.geom, 4326), ST_SETSRID(c.geom, 4326))
GROUP BY c.id_number

Then to incorporate the scaling factor into the sum you can use a case statement in a subquery, similar to the following

SELECT p.id_number, SUM(price)
FROM (
SELECT c.id_number,
    CASE WHEN scaling_code = 'small' THEN small_price
        WHEN scale_code = 'medium' THEN med_price
        ELSE large_price
    END price
FROM pricing_table p
    INNER JOIN catalog_table c ON ST_INTERSECTS(ST_SETSRID(p.geom, 4326), ST_SETSRID(c.geom, 4326))
    ) p
GROUP BY p.id_number


Transaction log filling the drive if mirroring fails I am having two SQL Server 2016 machines with mirroring set up. This is supposed to be hot-standby scheme.

HDD size is 1TB, and database size is around 600GB (just one DB). This is 90 days worth of archived data, everything older than 90 days is getting deleted every night (automatically through an external application which is using/filling the database in the first place). So 600GB is the peak DB size, it will not go beyond as it is being cleaned up regularly.

The problem is with the transaction log if one server fails, or if mirroring gets suspended for any other reason. If I understood the principle correctly, healthy server will retain the transaction logs as long as it doesn't get information from partner that everything is OK. So if mirroring fails, the HDD will get filled within several hours.

Is there any suitable technique to prevent this? I have backups of logs every 15 minutes and everything works fine, but if mirroring gets suspended, backups are not worth much, as the log will keep growing in spite of the backups. And the situation on site is a bit specific, there are no engineers, only operators who are accessing this data once or twice per day, so it's impossible to react straight away. It can take more than 24 hours to have someone attend the problem.

Only thing I could think of is some sort of trigger that would remove the mirroring completely once it was suspended for some time (or maybe if it is suspended and HDD space is too low). This will prevent the healthy server from crashing completely, but someone will again have to come to site and set the mirroring up from scratch. And due to bad design from the start, DB size is bigger than half of the HDD size, so I can't even create local backup/restore, I would have to do everything via the 100Mbps NAS that belongs to the client. And this would take more time than it would take the transaction log to fill the drive again.

Now I am thinking to set up a script that would execute every half an hour for example, and remove mirroring if log size is higher than let's say 200GB. Any prettier way to do this? Also, what will happen with mirroring if log hits the maximum size? All transactions are small and can be rolled back quickly, so I am also thinking to set the maximum log size a bit higher than previous threshold (250GB maybe) as an additional precaution. Any downside to this?

Edit 1: I want to put this in initial question as well, maybe my question was not clear enough, but I do not have issues with mirroring getting suspended, it is working perfectly (for now), I am just preparing for worst case. Main goal is full redundancy, and without any precautions, application will become unusable because of full hard drive if just one computers fails, it will happen within hours.

What I see from your question is that you are not addressing the root cause, instead you are building solutions around the core problem which will resurface until you address the root cause - why does mirroring get suspended ? Did you see any errors in error logs (windows or sql server) ? Does it happen on a frequent basis, what are the patterns that contribute to this ?

Address the above ones first. You wont have to re-setup mirroring for 600GB DB once you address the root cause.

This is what I have done in my current environment -

Have a process to check for mirroring health like using powershell or sql agent job :

select db_name(database_id) as MirrorSuspendedDBs
        from sys.database_mirroring
    where mirroring_state_desc = 'SUSPENDED'
    and mirroring_role = 1

If it finds any db/s that are suspended, try to resume mirroring using

ALTER DATABASE dbname SET PARTNER RESUME;

if above fails (use error codes to catch errors, may be retry 2-3 times by sleeping in between a retry), generate a ticket or email for the DBA team.

This way, you know that something is wrong and address that for a permanent fix.

see this : Why Does the Transaction Log Keep Growing or Run Out of Space?

Edit:

  The problem is with the transaction log if one server fails, or if mirroring gets suspended for any other reason. If I understood the principle correctly, healthy server will retain the transaction logs as long as it doesn't get information from partner that everything is OK. So if mirroring fails, the HDD will get filled within several hours.

For mirroring or AlwaysON, the secondary has to be up for the tlog to get truncated and reused by sql server.

If your concern is that secondary goes down and your drive fills up due to Tlog not getting truncated, then look into switching to logshipping provided it does not break your RTO (how fast you can bring your system up) and RPO (how much data loss is acceptable to your business).

Failover for mirroring is almost instantaneous vs logshipping (if you dont have a witness configured) is manual.

You can even go with clustering - DR-1 (2 nodes in each side clustered) and mirroring between the 2 sites.

Again - preparing for failure is a good thing, but do test out all your scenarios when choosing between HA/DR technologies.


Adding a check constraint on a new column with default value on a large table I have a large table (1–2m rows) and need to add a column. It is an integer column with a default of 0. I am also adding a check constraint that column >= 0.
Currently, I am making the change in these steps:

Add the column with the default
Add a check constraint with NOT VALID so it doesn't lock the table
VALIDATE CONSTRAINT

A coworker brought up that Postgres could be smart enough to know that adding a column with a default and then, in the same transaction, immediately adding the check constraint. Since the default passes  the check constraint, all rows are guaranteed (I think?) to pass and so it wouldn't need to do a full table scan.
Is that correct? Are there better ways of doing this?

It doesn't seem to be that smart, no, from my trying it in psql with \timing on. Increasing the size of the table causes ALTER TABLE to get slower, and it seems linear, so I assume it's still doing a scan. You can verify this pretty easily interactively:
testdb=# \timing on
Timing is on.
testdb=# create table target(a bigint);
CREATE TABLE
Time: 4.176 ms
testdb=# insert into target select x from generate_series(1, 15000000) x;
INSERT 0 15000000
Time: 8661.110 ms (00:08.661)
testdb=# begin; alter table target add column y bigint default 0 check(y>=0); rollback;
BEGIN
Time: 0.128 ms
ALTER TABLE
Time: 1130.251 ms (00:01.130)
ROLLBACK
Time: 0.106 ms
testdb=# insert into target select x from generate_series(1, 15000000) x;
INSERT 0 15000000
Time: 8948.767 ms (00:08.949)
testdb=# begin; alter table target add column y bigint default 0 check(y>=0); rollback;
BEGIN
Time: 0.139 ms
ALTER TABLE
Time: 1955.840 ms (00:01.956)
ROLLBACK
Time: 0.129 ms
testdb=# insert into target select x from generate_series(1, 15000000) x;
INSERT 0 15000000
Time: 8836.126 ms (00:08.836)
testdb=# begin; alter table target add column y bigint default 0 check(y>=0); rollback;
BEGIN
Time: 0.114 ms
ALTER TABLE
Time: 2751.619 ms (00:02.752)
ROLLBACK
Time: 1.010 ms

I think your planned approach is fine.


GMT timezone in Mysql timestamp literal value In brief

We want to input value for a timestamp column WITH timezone value in the literal string e.g. '201708-09 10:11 GMT +07:00'

In full

We have a sample table

create table some_table (
  start_time timestamp default current_timestamp
);

We can insert this value

into some_table values('2017-08-09 10:11');

but this will fail

into some_table values('2017-08-09 10:11 GMT +07:00');

How can we set timezone value for that column when insert?

In MySQL any date/time values inserted into timestamp are automatically converted and stored as UTC.

To quote MySQL documentation:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions.

So the answer to your question would be to set the time_zone value for your connection to the database by setting the session variable called time_zone before inserting/updating the value into the database and do the same before fetching the value back from DB. By doing so, the conversion to the actual value of date/time to reflect the Timezone will be automatically handled by MySQL.

This is how you can set the session Timezone on MySQL Client:

mysql> SET time_zone = timezone;

To set global Timezone on MySQL Client:

mysql> SET GLOBAL time_zone = timezone;

There will be ways to do this in the Programming Language you are using.

More details from MySQL manual here:

https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html


High CPU Usage on certain query when changing DB Compatibility Level We migrated from SQL 2008R2 to SQL 2016 a few years ago and have been running on Compatibility Level 100 since that point.
We are looking to make use of memory optimization introduced in SQL 2016 and that requires setting the compatibility level to 130.
We have had a few instances where a certain query will max out the CPU (that normally only takes a few seconds to run on Compatibility Lvl 100, but when we change it to Level 130 the CPU maxes out and does takes 2 minutes and 37 seconds to run  vs about 2 seconds on the old compatibility level)

I did pull the execution plan for each of these
CompatibilityLvl100 Plan
CompatibilityLvl130 Plan
I am not a DBA so I can see there are differences between the execution plans but I could not see any reason for it to max out the CPU and take almost 6000% longer to run the same query with the same indexes etc.
I also executed this query to get some more details about the running query, I have uploaded the results as a comparison to a Google Sheet here.
One thing i noticed is that the slow running query was using 192 worker threads, whereas the fast running query only used 32.  But I still couldn't figure out what was causing the high CPU usage.
Be glad of anyone's help or direction of something I may have missed!

On higher compatibility level SQL Server starts to use new cardinality estimator which leads to different execution plan. Sometimes this new plan may be less efficient but it doesn't mean that new cardinality estimator works worse. Usually it means you need to review your query and probably adjust your indexes or statistics because probably your original plan was far away from optimal too. You attached estimated execution plans to question description. To find out the root reason of the issue you need to compare actual plans.
You can use traceflag or hint to force SQL Server to use old cardinality estimator for your query: OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))


SQL Server Configuration manager doesn't show the IP Sorry for stupid question.
I'm trying to set up the remote connection to the SQL Server.
In all tutorials I watched people have their IPs shown in the TCP/IP properties window(under SQL Server Network Configuration-->Protocols for MSSQLSERVER). Please see the picture.
But I have only the port.
Where can I see the IP? What should I do to see it?

What the version of SQL Server are you using?
Please make sure you open the same version of SQL Server Configuration Manager if you have multiple different version SQL Server instances.
You can use the following query to view the raw data with your network protocols:
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE registry_key LIKE '%SuperSocketNetLib%'

See this for more details.
Regards,
Amelia


100x less insert performance with Galera multi-master I am running an application which inserts a lot of rows and we were experiencing some low performance. So i started benchmarking the Galera Cluster with sysbench and oltp_insert.lua (practically inserts with random key).

The performance of the cluster is really bad.

With the same test:

As a standalone DB i can perform about 35000 tps
With Galera multi-master the performance is: 350 tps

The table structure is this one:

       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

What i tried so far (with no success):

increase wsrep_slave_threads=16
no matter how many client threads i use, the TPS is still 350 (looks like the transactions are serialized somehow by the DB)
set wsrep_sync_wait=0 (although i perform no reads)
the db does not use binlog, its not enabled.

Configuration:

[mysqld]
binlog_format=ROW
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
datadir=/var/lib/mysql

innodb_buffer_pool_size = 300MB
innodb_large_prefix=1
innodb_file_format=Barracuda

transaction-isolation = READ-COMMITTED

wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_slave_threads = 16
wsrep_max_ws_rows=131072
wsrep_max_ws_size=1073741824
wsrep_convert_LOCK_to_trx=0
wsrep_causal_reads=OFF
wsrep_sst_method=xtrabackup-v2

I've hit the same issue.
The first thing to do is to check the following variables:

select @@innodb_flush_log_at_trx_commit;

select @@sync_binlog;

Setting innodb_flush_log_at_trx_commit to 2 and sync_binlog to 0 will increase the performance dramatically.

The reason is in certification mechanism and the nature of multi master behaviour. Inserts will be very slow because in order to commit the transaction cluster will ensure that all logs are flushed at the time of commit at every node.

Please be sure that your caches fit RAM with the following query as well:

select ((@@thread_stack +@@binlog_cache_size +@@read_buffer_size + @@read_rnd_buffer_size +@@sort_buffer_size +@@join_buffer_size + @@global.net_buffer_length +@@global.query_prealloc_size + @@binlog_stmt_cache_size) * @@max_connections + (@@query_cache_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@key_buffer_size))/(1024*1024);


POSTGRESQL JOIN not working Running the following...

SELECT
      a.allocation_date, a.trade_account, a.trade_broker, a.account,
      a.custodian, a.book, a.symbol, a.side,
      SUM(a.quantity)::integer,
      SUM(a.quantity*a.price) / SUM(a.quantity) AS price
    FROM backoffice.allocations a
    inner join symdb.products.product s
    on (symbol=products)
    WHERE a.allocation_date BETWEEN '2020-01-01' AND '2020-12-30'
    GROUP BY
      a.allocation_date, a.trade_account, a.trade_broker, a.account,
      a.custodian, a.book, a.symbol, a.side
    ORDER BY
      a.allocation_date, a.trade_account, a.trade_broker, a.account,
      a.custodian, a.book, a.symbol, a.side;

...is throwing...

  SQL Error [0A000]: ERROR: cross-database references are not implemented: "symdb.products.product"

The INNER JOIN part is what I can't get to work. How do I fix it?

If you do have dots in your schema name, this wouldn't be very good practice as postgres expects the input to be schemaname.tablename so now its getting extra arguements.


How to find givien index by name in PostgreSQL I know how to find index by name in MS SQL Server just by search for %name%
But where I can do the same in PGAdmin4 ?

As a horse_with_no_name provided, you can search the pg_indexes meta-table to find an index by name with the query:
SELECT *
FROM pg_indexes
WHERE indexname LIKE '%NameOfIndexYoureSearchingFor%';

Regarding your second question in the comments on how to delete an index in PostgreSQL, you can script it out with a query like this:
DROP INDEX NameOfIndexYouWantToDrop;

PostgreSQL documentation on DROP INDEX for more information.


Remote execution of SQL Agent job Working on setting an automated restore of dbs from Production to the test/dev environments using Net App SQL Snap Manager. The prod job handles the kick off of the restore, nothing being ran on the test/dev sql agent side. Not really familiar with the Net App process either, being handled by storage team. My question is, without using linked servers, is it possible to kick off a SQL agent job on the test/dev box remotely from the prod box? Would powershell give me the functionality I'm looking for? Or would linked servers be the better way to go?

Thanks

You should be able to achieve this using plain SQLCMD from the production server, but have it pointing to your other server

sqlcmd -S "servername" -Q "execute msd.dbo.sp_start_job @job_name='MyJobOnRemoteServer'"


Share data from one database to two different db For example I have:

database_root (with "table1_test"..)

and then I have (but those tables doesn't have "table1_test")

db_1
db_2

I want to share the data of database_root to db_1 and 2, and if someone is looking for data on table1_test on db_1 or 2... automatically the data is collected from database_root.

I don't really know if it's complicated, because I don't know so much of MySQL so... tell me if it's possible (if it's possible please tell me how to) or no.

SELECT a.this, b.that
     FROM database_root AS a
     JOIN db_1 AS b  ON a.x = b.x;


DBCC SHRINKFILE for Log and Data when using Availability Groups We had an issue where one of our database tables grew by 40GB by mistake. The database is part of an Availability Group with one primary and two replicas. While trying to delete the extra records we also managed to explode our log file sizes by ~70GB (Side note: always delete in small batches!) which started causing space issues on our primary and replicas. We were able to survive that by live-expanding the drives on the VMs and waiting for replication to finish, but now that the records are removed we would like to reclaim the unused space in the data and log files so it can be used by other databases.

How do you use DBCC SHRINKFILE for databases that are part of an Availability Group?

Denis P. wrote a basic SQL Script to resize the log files in an AG, so here is the overall process:

Make sure replication has finished, as you can't shrink the files while the logs are still being sent to a secondary replica
Figure out what new size you want the data and log files to be. There are plenty of SQL scripts to find this information, or you can use the database properties or Shrink File Task wizard in SSMS to figure out how much space is still in use.
On the primary use the script below to resize your files. It may require multiple executions to take effect.
Once the primary files have the correct size and the replication queue has stabilized, you may need to restart the SQL Server service on the secondaries for the new file size to take effect on those systems.

TSQL Scripts:

--Run on primary to shrink db file to 10000 MB. May need to run multiple times.
DBCC SHRINKFILE (N'MyDatabaseName', 10000, NOTRUNCATE)
DBCC SHRINKFILE (N'MyDatabaseName', 10000)

--Check data file size
USE Master
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'MyDatabaseName'
ORDER BY SizeMB DESC

--Once data file is resized and replication has finished, start resizing log file

--Sorted view of log file sizes and percent used
USE MyDatabaseName
Drop table IF EXISTS #tmplogs
CREATE TABLE #tmplogs (
  DatabaseName varchar(100)
  , LOGSIZE_MB decimal(18, 2)
  , LOGSPACE_USED decimal(18, 2)
  , LOGSTATUS decimal(18, 0)
)
INSERT INTO #tmplogs EXEC('DBCC SQLPERF(LOGSPACE);')
SELECT * from #tmplogs ORDER BY LOGSIZE_MB DESC, LOGSPACE_USED DESC

--Run on primary to shrink log file. You must take a backup of the log file before it can be truncated
BACKUP LOG MyDatabaseName TO DISK = '\\Backups\SQL\_Trans\My_AG\MyDatabaseName\MyDatabaseName_LOG_20170721_200008.trn' WITH NOFORMAT, INIT, NAME = N' Trn Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
DBCC SHRINKFILE (N'MyDatabaseName_log' , 2000, NOTRUNCATE)
DBCC SHRINKFILE (N'MyDatabaseName_log' , 2000)

--Again this may require multiple executions to remove unused space
--Also the file size on the replicas may not decrease until the SQL Server service is restarted on those systems

And of course once you are finished you may have index fragmentation or other performance issues that can occur when using DBCC SHRINKFILE. In our case this is a small database where we don't care too much about the performance and there are weekly jobs to handle index maintenance, so we would rather reclaim the unused space.

The script helped us reclaim a total of over 250GB of unused data and log file space!


Unable to connect oracle 12 database remotely while local connection works I have two oracle test nodes. On node 2 as root I am able to connect without credential with the command,

su - oraabc -c "/oracle/ABC/12102/bin/sqlplus / as sysdba"

Output:

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 15 12:18:20 2019

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

I'm trying to connect from node 1. Passwordless login is already established using keys between node 1 and 2.

Command:

ssh root@node2 su - oraabc -c "/oracle/ABC/12102/bin/sqlplus / as sysdba"

But it prompts for credentials,

Output:

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 15 12:23:07 2019

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

Enter user-name:

Why does it prompt for credentials if oraabc is already a part of the dba group? Is there a configuration missing in order enable remote connection as oracle user without credentials?

Env:

[root@o71 ~]# tail -5 /home/oracle/.bash_profile

export ORACLE_SID=MIN18
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/18.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

Local:

[root@o71 ~]# su - oracle -c "sqlplus / as sysdba"

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jan 15 15:54:16 2019
Version 18.4.0.0.0

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

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

SQL>

Remote:

[root@o72 ~]# ssh root@o71 su - oracle -c "sqlplus / as sysdba "

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jan 15 15:55:15 2019
Version 18.4.0.0.0

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

Enter user-name:

Remote with better syntax:

[root@o72 ~]# ssh root@o71 'su - oracle -c "sqlplus / as sysdba "'

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jan 15 15:55:39 2019
Version 18.4.0.0.0

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

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

SQL>

Instead of:

ssh root@node2 su - oraabc -c "/oracle/ABC/12102/bin/sqlplus / as sysdba"

Use this:

ssh root@node2 'su - oraabc -c "/oracle/ABC/12102/bin/sqlplus / as sysdba"'

Also I find it really bad practice to set these variables in profile by default. Just use oraenv.

Example:

[root@o72 ~]# ssh root@o71 'su - oracle -c "export ORAENV_ASK=NO; export ORACLE_SID=MIN18; . oraenv; sqlplus / as sysdba "'
The Oracle base remains unchanged with value /u01/app/oracle

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jan 15 15:56:51 2019
Version 18.4.0.0.0

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

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

SQL>


Error while configuring REST for Oracle APEX on Windows I'm trying to deploy Oracle APEX 18 on Windows 8.1 PC using Oracle REST Data Services and Oracle Database 18c XE following this guide.

But when I'm trying to run @apex_rest_config.sql I get this error - Enter: GetConsoleMode failed, LastError=|6| at Drive_letter:/DB_install_folder/dbhomeXE/perl/site/lib/Term/ReadKey.pm line 334.

In CMD it looks like this:

Every apex_rest_config#.log file looks similar to this:

What could be causing this problem? Why @apex_rest_config.sql doesn't ask new passwords for APEX_LISTENER and APEX_REST_PUBLIC_USER, as it does in the guide?
Does it have something to do with this ORA-03113: end of file on communication channel error? Is there any fix or workaround?

Update 1: trying to implement this solution.

Everything works if you:

create a new database (or use existing)
do alter session set container = yourDBname
and only then run @apexins.sql and other installation and config scripts (e.g., @apxchpwd.sql, @apex_rest_config.sql).

This will lead to creation of a new APEX instance inside current DB.


Relational Algebra Queries - How to Approach Problems? I have the following schema:

employee(sin, emp_name, emp_address, salary, supervisor_sin, dept#)

works_on(emp_sin, project_number, hours)

The question is:

Assume that no employee works on more than 2 projects. For those employees
working on two projects, give the employee name and the two project numbers.
Each employee name should appear once only

My solution so far:

R1 = works_on [theta-join]emp_sin=d.emp_sin [Rename]d(works_on) - gets tuples in the form "emp_sin | project_number | d.project_number | hours | d.hours"?

Result = [Projection]emp_name,project_number,d.project_number(employee [theta-join]sin=emp_sin R1) - gets all names, and 2 project numbers in the form "emp_name | project_number | d.project_number"?

Is this correct thinking? Am I missing something?

I think you're on the right track, but I'm not seeing a provision for restricting the results to employees who are actually working on two projects and the rename notation is confusing to me.  You'll probably want to leverage a theta-join on a self join to accomplish this.

To start out, I would rename the relevant attributes in the work table in preparation for a self-join:

  ρsin/emp_sin,project_number1/project_number works_on

Then project just those attributes for simplicity's sake:

  πsin,project_number1 ( ρsin/emp_sin,project_number1/project_number works_on )

For visualization purposes, the equivalent SQL here would be something along the lines of:

SELECT  emp_sin
            AS sin,
        project_number
            AS project_number1
FROM    works_on;

I'd then prepare for the self join by creating another relationship, renaming the project_number differently:

  πsin,project_number2 ( ρsin/emp_sin,project_number2/project_number works_on )

At this point, a theta-join can be applied, exploiting the maximum of 2 carnality as defined in the assumption.  This theta-join creates a relationship with both project_numbers by emp_sin:

  ( πsin,project_number1 ( ρsin/emp_sin,project_number1/project_number works_on ) ) ⋈sin, project_number1 < project_number2 ( πsin,project_number2 ( ρsin/emp_sin,project_number2/project_number works_on ) )

In effect, this is similar to SQL as follows:

SELECT  *
FROM (  SELECT  emp_sin
                    AS sin,
                project_number
                    AS project_number1
        FROM    works_on ) AS w1
INNER JOIN (
        SELECT  emp_sin
                    AS sin,
                project_number
                    AS project_number2
        FROM    works_on ) AS w2
    ON  w1.sin = w2.sin
    AND w1.project_number1 < w2.project_number2;

The little exploit in the requirements here lets us do a natural join and a final projection to finish up ( since I've already renamed the emp_sin attribute to sin ):

  πemp_name, project_number1, project_number2 ( employee ⋈ ( πsin,project_number1 ( ρsin/emp_sin,project_number1/project_number works_on ) ) ⋈sin, project_number1 < project_number2 ( πsin,project_number2 ( ρsin/emp_sin,project_number2/project_number works_on ) ) )

Again, as visual proof, this is approximately the equivalent of the following SQL, shown in action in this SQL Fiddle:

SELECT  e.emp_name, w1.project_number1, w2.project_number2
FROM    dbo.employee e
INNER JOIN (
        SELECT  emp_sin
                    AS sin,
                project_number
                    AS project_number1
        FROM    dbo.works_on ) AS w1
    ON  e.sin = w1.sin
INNER JOIN (
        SELECT  emp_sin
                    AS sin,
                project_number
                    AS project_number2
        FROM    dbo.works_on ) AS w2
    ON  w1.sin = w2.sin
    AND w1.project_number1 < w2.project_number2;