texts: Oracle 12cR2 / ORA-04031 - Unable to allocate xx bytes of shared memory (Shared Pool) (Hard Parse & Parallel Queries)

texts:
Sometimes I'm getting ORA-04031 error on my DWH database. The last one like this:

"ORA-04031: unable to allocate 56 bytes of shared memory ("shared
pool", "unknown object", "KKSSP^4366", "kglseshtSegs")"

Here is another ORA-04031 for different days:

ORA-12801: error signaled in parallel query server ...
ORA-04031: unable to allocate 40 bytes of shared memory ("shared
pool", "unknown object", "KGLH0^4ad1e9c3", "kglHeapInitialize:temp")

And another one:

ORA-00604: error occured at recursive SQL level 1
ORA-04031: unable to allocate 40 bytes of shared memory ("shared
pool", "unknown object", "KGLH0^4ad1e9c3", "kglHeapInitialize:temp")

My SGA parameters are as follows:
SQL> show parameter sga_target

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_target                           big integer 10G

SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_max_size                         big integer 10G

SQL> show parameter sga_min_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_min_size                         big integer 0

And here is my cursor_sharing parameter (Oracle recommends EXACT value for the best practice):
SQL> show parameter cursor_sharing

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

cursor_sharing                       string      EXACT

And here is the my free memory for sharing pool (even if the values are changing, they are generally close to that):
SQL> select inst_id, name, bytes/1024/1024 MB from gv$sgastat where pool='shared pool' and name='free memory';

   INST_ID NAME                                                                           MB

---------- ---------------------------------------------------------------------- ----------

  1        free memory                                                            183.724869

  2        free memory                                                            258.926163

In document for 12c, Oracle says:

"If free memory is always available in the shared pool, then
increasing its size offers little or no benefit."

So, I will not increase the shared_pool size.
I'm suspecting hard parse and parallel queries for ORA-04031.
And here are some statistics:
SQL> select name, sum(value) from gv$sesstat s1 join gv$statname s2 on s1.statistic#=s2.statistic# where name like '%parse count%' group by name order by 1,2;

NAME                                                                   SUM(VALUE)

---------------------------------------------------------------------- ----------

parse count (describe)                                                         68

parse count (failures)                                                        490

parse count (hard)                                                          40772

parse count (total)                                                       1295326

Here is a temporary (workaround) solution:
alter system flush shared_pool;

Do you have any advice to solve this problem completely? If other information is needed I can provide it.
NOTE: I edited my question.
Best regards,


texts: Denied access sending dbmail while impersonated under a login with sysadmin privilege

texts:
This question is similar to but not the same as this.

I created a stored procedure and set it to execute under the context of a sysadmin login (let's called it sys1). For testing purposes, I only included an execute sp_send_dbmail statement in there:

create procedure temp_impersonation_test with execute as 'sys1'
as
exec msdb.dbo.sp_send_dbmail
    @subject = 'test',
    @body = 'test',
    @recipients = 'james.jiao@someplace.com'

Now here are some facts. Both sys1 and I are sysadmins on the server. Neither login has trouble running the standalone sp_send_dbmail statement. Neither login has trouble running the stored procedure when it is not set to execute under a different context.

However when the sp is set to execute under a different context, it fails with the following error. Note that this even happens when sys1 tries to execute it (effectively, it will try to impersonate itself).

The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

I have tried the usual things like re-attaching the database user to the server login for the current database context and for msdb (I actually had to create the user for msdb as it wasn't there before.), recreate the mail profile and putting both users in the databasemailuserrole in msdb.

I think it's worth it to note that this issue started happening after we moved to a new server with SQL 2008 R2. The code worked fine on the old server which ran SQL 2005.

I am baffled. How could this happen? Both accounts are sysadmins!

Update: I just tried this on some of our other database servers and this issue occurs on all of them regardless of the server version. I think there must be a database setting I've missed out somewhere!


texts: Increase AUTO_INCREMENT value in phpMyAdmin

texts:
So a website that I've inherited had the AUTO_INCREMENT value for a particular table set to a value of 65536. The website is an online store, and this particular table is set for product variations. Well, we've finally hit 65,536 variations and cannot add anymore. I'm now getting an error of Duplicate entry '65535' for key 'PRIMARY'

I've found where to increase this value in phpMyAdmin, but I still get the same error. Is there something I'm doing wrong? Any advice would be mucho helpful.


texts: Restore a copy of the encrypted database

texts:
I have encrypted the backup using the method followed by the file server. Unfortunately, I did not keep a copy of the certificate of the process of encryption and unfortunately also the server was completely destroyed and is impossible to run again and I have to restore a copy of the database.
I have a copy of the rule but it is encrypted. Is there a solution to this problem?


texts: Best practices to run time offset delayed replication

texts:
I want to setup replication system on postgresql like #1 MASTER SERVER and two replicated servers: #2 SERVER in realtime (for load balancing), #3 SERVER delayed with some time offset (for example 2 days) to prevent application and human mistakes on data update/delete

Someone got some experience with that kind of setup? What are real life disadvantages and problems with that solution?


texts: Why does Postgresql tell me the database does not exist?

texts:
I created a Ruby on Rails application just for demonstration to a colleague and then deleted the application, but I did not delete the database which was also created using Postgresql.

I go into posgresql like so:

psql postgres

postgres=# \l
                                            List of databases
                 Name                 | Owner  | Encoding |   Collate   |    Ctype    | Access privileges
--------------------------------------+--------+----------+-------------+-------------+-------------------
 GenerateApp_development              | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 GenerateApp_test                     | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 GeneratorApp_development             | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 GeneratorApp_test                    | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 Overtime_development                 | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 Overtime_test                        | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 RoutingApp_development               | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 RoutingApp_test                      | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 dancortesPortfolio_development       | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 dancortesPortfolio_test              | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 freelance_camp_documents_development | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 freelance_camp_documents_test        | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 freelance_camp_proposal_development  | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 freelance_camp_proposal_test         | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres                             | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0                            | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/danale        +
                                      |        |          |             |             | danale=CTc/danale
 template1                            | danale | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/danale        +
                                      |        |          |             |             | danale=CTc/danale
(17 rows)

I want to delete the ones that say RoutingApp_development and test.

postgres=# DROP DATABASE RoutingApp_development;
ERROR:  database "routingapp_development" does not exist

I get that error above. I have tried to search for why this is happening online and I have been unsuccessful in finding the answer, please help.


texts: SQL : get rows from two tables relatively

texts:
I've two tables named categories and products. Simple structure is given below.

categories

cat_id        cat_name
    1           c1
    2           c2
    3           c3

products

prod_id     cat_id      prod_name
    123         1           p1
    546         2           p2
    562         3           p3
    567         3           p4
    234         1           p5

I want to get product list along with their related category name. ex.

Category : c1, Product : p1
Category : c2, Product : p2
Category : c3, Product : p3
Category : c3, Product : p4
Category : c1, Product : p5

For that i'm using below simple queries,

if($res1 = mysqli_query("SELECT cat_id, cat_name FROM categories")) {
    while($tbl1 = mysqli_fetch_assoc($res1)) {

    if($res2 = mysqli_query("SELECT prod_name  FROM products WHERE cat_id = ".$tbl1['cat_id'])) {
        while($tbl2 = mysqli_fetch_assoc($res2)) {
            echo "Category :". $tbl1['cat_name'] . " Product". $tbl2['prod_name'];
        }
    }

}
}

It can be Improved in a single query but having no idea about it.
Please help.

Also suggest list those products according to Category order. (ex. first c1 categories products, c2 categories products and so on... )

Thank you...


texts: Oracle schema object name when fire trigger

texts:
I need to get the object name and user name from oracle schema after fire a trigger. It executes on DDL statements. I need to get executed user name and object name which trigger executed on. Below is my PL/SQL code.

CREATE OR REPLACE TRIGGER orders_changes
after drop or alter or create on schema

DECLARE
 v_username varchar2(10);
 obj_name varchar2(300);

 BEGIN

  -- Find username and object name
  SELECT user, object_name INTO v_username, obj_name
  FROM dual;

  insert into table1 values (v_username, obj_name);

END;

Seems issue is with the way I access the object name. It gives an error. Please any one can help me to solve this.


texts: Can this method for unit testing a stored procedure be improved?

texts:
When I have written unit tests for my stored procedures, they usually look something like this:

/* Unit test for stored procedure which runs a query */

/* Define #temp table with structure of query results */

/* Define and set variables for stored procedure parameters */

/* If stored procedure queries from a particular table...*/

/* Truncate table and populate with sample data */

/* Call stored procedure using variables but insert results into table */

/* INSERT #temp EXEC usp_TestProc @P1=1, @P2 = 'X' */

/* Query #temp table to confirm that all fields have certain values */

/* Query #temp table to confirm that certain number of rows exist */

/* If results are incorrect, display error message */

While this will work, it feels clunky somehow and is very difficult to maintain over the course of a long-term project where the requirements are constantly changing.

Is there a better way (or standard way) to test a stored procedure
like this?
Is the difficulty of maintenance simply part of the job and worth
the effort in order to have the unit tests? I have to say that at
most of my jobs, the DBAs rarely do unit testing in the first place.


texts: Is there a Document based nosql with deep json update?

texts:
I'm currently looking in a Document based nosql (possibly with sharding/replication/NSPOF) database that allow (is specialized in) deep json document update.

By that I mean that I would like a nosql that gives me the ability to change any json values without having to fetch the whole document modify it and send it back to the server.

I'm going to work with huge documents, with known schema, and it would be useful not to retrieve and send back the document since the database is on a different dedicated server.
It will help me save a lot of bandwidth.

Thanks.


texts: How to drop a procedure across a lot of databases (SQL Server 2016)

texts:
I have a simple procedure that exists in over 150 databases. I can delete them one by one, which is the most stupid work to do in the world of software development. Or I can somehow dynamically delete them all in one query, which is smart but doesn't work.

I've tried:

execute sp_msforeachdb 'drop procedure Usages'

I got:

  Cannot drop the procedure 'Usages', because it does not exist
  or you do not have permission.

So I thought maybe I create a cursor for it. Thus I wrote:

declare @command nvarchar(max) = '
drop procedure Usages
';
declare @databaseName nvarchar(100)
declare databasesCursor cursor for select [name] from sys.databases where database_id > 5
open databasesCursor
fetch next from databasesCursor into @databaseName
while @@fetch_status = 0
begin
    print @databaseName
    execute sp_executesql @command
    fetch next from databasesCursor into @databaseName
end
close databasesCursor
deallocate databasesCursor

Again, I got that same message. I thought, maybe it doesn't change the context of the database, thus I prefixed the delete command with the name of the given database, so commands would become similar to drop procedure [SomeDatabase].dbo.Usages, but then I received:

  'DROP PROCEDURE' does not allow specifying the database name as a
  prefix to the object name.

So, I figured out to dynamically execute use @databaseName so that I can drop procedure in the context of that database. But it's not working. What should I do? I'm stuck at the smart method, and I've spent more time than the stupid method now.


texts:
If I say:

Table1 left join Table2

is it the same as saying?:

Table2 right join Table1

In other words, should I expect to get the same results from 2 identical queries where the only thing that is changed is which table is written first and whether a left or right join is used (following the same pattern I have described above?)


texts: how to check who has killed the job which was running ?if logs are not generated

texts:
Unspecified error occurred on SQL Server. Connection may have been terminated by the server.

This is error I am getting.(when the job is executed)

  [SQLSTATE HY000] (Error 0)  Cannot continue the execution because the session is in the kill state.
  [SQLSTATE HY000] (Error 596).  The step failed.


texts: How to link customers and suppliers in a database

texts: Some customers can be suppliers at the same time, so: how to design the database so we don't have two tables having the same columns, and, at the same time, we can link a customer and a supplier together if needed?


texts:
All,

Lets say devoper sets the write concern as Majority in MongoDB

As a DBA can i have the access to change the write concern of whole cluster or only the developers has the rights to do through coding ?

In replica sets we can do the change through conf settings but i beleive it wouldn't override the default one


texts: Using OPENROWSET to query LDAP via SQL requires the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider

texts:
We have clients that are using SQL Server 2008 R2, 2012, 2014, 2017 and 2019, and I'm facing a situation where we're going to need to ask them to manually create registry entries on their database servers to enable AdHoc query functionality for Linked servers for non-administrators.  We believe this is the final step for getting them the functionality they need, but the lack of very clear information on where this missing registry key needs to be placed is disconcerting.  Assistance in identifying the correct location(s) is requested.  We will need a proven and authoritative answer that will work for the above servers if we're going to be successful in petitioning our clients for this modification.  Thank you in advance.
History:
Although this question is similar to Query Extended LDAP Server from SQL Server, we encountered and resolved that one already (we ran the Linked Server creation script as an Administrator.)  However, even if you manage to accomplish EXEC master.dbo.sp_addlinkedserver and EXEC master.dbo.sp_addlinkedsrvlogin, you will face another problem: you won't be able to actually use OPENROWSET or OPENDATASOURCE unless you're a database administrator.
You can create a registry setting on the database server that enables this AdHoc functionality.  However, as this is on our client's database server, which also serves as testbeds for other software, I am very hesitant to request that they take steps that seem to be in place for improved security.
The instructions in the link indicate that if the registry keys are missing, they may need to be added.  Here is where I encounter my biggest problem.
On neither of the two servers I have tested this on so far, have I been able to locate any registry settings for our newly-created Linked Server, and therefore, I have no clue exactly what registry settings to create.  There is no way I am going to be asking a client to drop new keys in 3 or 4 locations, because one of them may work.  When we have completed the testing of this, we will apply to have the same changes made on their Production servers, so we need to know exactly what is going to work.
The command used to successfully create the linked server:
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=@LDAPUser,@rmtpassword=@LDAPPassword

Searching for DisallowAdHocAccess in Google returns that the necessary key needs to be located in one of the following locations (from the above link:)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers<ProviderName>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\Providers\<ProviderName>

(from this article)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQLServer\MSSQL<major version>_<minor version>.<instance name>\Providers\<ProviderName>
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQLServer\MSSQL_.\Providers\<ProviderName>

Our Windows Server 2012 R2 Standard Registry listing showing the HKLM\SOFTWARE\Microsoft\Microsoft SQL Server tree, cannot locate our Linked Server Provider.


texts:
I have this triggers, that to put it simple do this things:

Before Insert:creates new record with new id,name and puts value in start time cols.

After insert: updates value in end time cols.

create or replace trigger stg_control_inserts_before
  before insert on Distributionplan
  for each row
declare
  GetError varchar2(280);
  Jobname varchar2(20) := 'DistributionPlan';
  HoraActual  timestamp (6) := systimestamp;
  FullJobName varchar2 (80) := Jobname ||' '|| HoraActual;
Begin
  insert into stg_control  (job_name,job_id,execution_start,exec_tocore_start)
   values (FullJobName,stg_control_seq.nextval,HoraActual,HoraActual);
end;
/

create or replace trigger stg_control_inserts
  after insert on Distributionplan
  for each row
declare
  HoraActual  timestamp := systimestamp;
Begin
  update stg_control set execution_end='14/05/19 00:00:00,493000000'
  where job_id in (select max(job_id) from stg_control);
end;
/

The issue is that the second trigger is ignored, the first one that creates the new record is executed but the second one, that should update the end time doesn't do anything. I tried passing a hardcoded value instead of the variable but doesn't work either.

Regards.


texts: Combining separate ranges into largest possible contiguous ranges


texts: no pg_hba.conf entry for host "10.1.1.91", user "user" database "testing", SSL off

texts:
I've come across different versions of this same issue on stackoverflow and on this forum. I've tried the recommendations posted on these forums but I am still unable to connect to a database. I've run out of leads to try and I'm hoping someone can explain to me what is happening and why I am unable to connect. The context of my question follows:

I pg_dumped a database on a server host named test-crawl-1 and have pg_restored the database onto a new server host named test-qa-db where it resides with 4 other databases. I've changed the database. yml file to look like this:

testing:
  db_host: test-qa-db
  db_port: 5432
  db_name: testing
  db_user: user
  db_pass: password

Next, because of the error I'm receiving I tried the following, I sshed into a VM named test-utility-1 and modified the pg_hba.conf file there to reflect the following:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

#Trying to fix the SSL error
host    all             all             0.0.0.0/0               md5

Then I tried this:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

#Trying to fix the SSL off error attempt 2
 local  all      all   (test-utility-1 address)10.1.1.1/32      md5

And then I tried to implement in the postgresql.conf file:

postgresql.conf

listen_addresses = "*"

I pretty much tried that line with the above variations but I have still yet been able to make it work. Is there anything that I'm missing. I'm not sure what else is there to try. Help would be appreciated thank you.

------------EDIT----------

I forgot to mention that I also hard coded the database, user, and Ip address into the corresponding fields but the SSl but the error still persists.


texts:
I have the below query and would like to insert the result into a table I created in Sql Server. Is there a way this can be done? The idea behind I'll create a SQL Agent job where the cursor result is stored on a table within my database.

Declare @DB sysname, @SQL nvarchar(4000)
Declare DBCur Cursor for Select Quotename(Name) from sys.databases where name like '%Prod%'
Open DBCUR
Fetch Next from Dbcur into @DB
While @@Fetch_Status = 0
Begin

      Set @SQL = 'select /*@@Servername as servername,*/ '+ Char(39) + @DB + Char(39)+' as databasename, o.Name as triggerName from '+@DB +'.'+'sys.objects o inner join  '+@DB +'.'+'sys.triggers t
      on o.[object_id] = t.[object_id]
            where o.type = ''TR''
            and t.is_disabled = 0'
      Print @SQL
      Exec SP_ExecuteSQL @SQL
      Fetch Next from Dbcur into @DB
end
CLose DBCur
DEAllocate DBCUr


texts: Is it possible to export/import ssh hosts from SQL Developer? The hosts are listed under C:\Users\name\AppData\Roaming\SQL Developer\system17.4.0.355.2349\o.sqldeveloper.ssh but I don't find a way to export/import them into a SQL Developer.


texts: How to set up tns listeners for Multiple Oracle Installations on One machine

texts:
So I have two oracle installations on a development/testing machine (10g and 11g, respectively).

The platform is RHEL4.

Now I need to have both these oracle up and have tns listeners for them running.

How do I do that?

I've tried having separate listener.ora files in 10g's home dir and 11g's home dir, and try to start them up separately (under different users, oracer10 for 10g, oracer11 for 11g).

However, as soon as one of the tns listener is started, the other wont start and complain something like "TNS-01106: Listener using listener name LISTENER11 has already been started".

I do have different name for the two listeners. (LISTENER10 and LISTENER11)

Thanks,

Here are the listener.ora files

For 10g:

SID_LIST_LISTENER10 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /home/oracer/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CER)
      (ORACLE_HOME = /home/oracer/oracle/product/10.2.0/db_1)
    )
  )

LISTENER10 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
     (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = SAP_1)(PORT = 1521))
      )
    )
  )

For 11g:

SID_LIST_LISTENER11 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /home/oracer11/app/oracer11/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CID)
      (ORACLE_HOME = /home/oracer11/app/oracer11/product/11.2.0/dbhome_1)
    )

  )

LISTENER11 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
     (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = SAP_1)(PORT = 1522))
      )
    )
  )


texts: Postgres, `where` condition, sort by first column, but I don't want that

texts:
I am using PostgreSQL 11.5 on Ubuntu

Issue

I have sql with joining tables and with where condition, which looks like this.

...

where
s_product.product_shop_id in (
'237002',
'230041',
'467173',
'464431',
'318417',

...

When I run sql.

copy(
select distinct on ("HOBIS")
-- s_product.product_id,
product_shop_id as "HOBIS",
product_name as "NAME",
concat('https://eshop.unihobby.cz/',product_url,'/',s_product.product_id,'p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019') as "URL",
concat('https://eshop.unihobby.cz/bin/product/4/',filename) as "IMG URL",
price_tax as "PRICE VAT",
case when price_rec > price_tax then concat(price_rec) end as "PRICE ORIGINAL"
from
s_product
left join s_product_image on s_product.product_id = s_product_image.product_id
left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
where
s_product.product_shop_id in (
'237002',
'230041',
'467173',
'464431'
)
and image_order = '0'
and s_pricelist_generated_lists.group_id = '1'

--order by "HOBIS"
) to stdout (format csv, quote '"');

Results are sorted by s_product.product_shop_id.

...

230041,"Interiérové dveře Klasik plné dub archico, 3D povrch - 60 cm L",https://eshop.unihobby.cz/stavba-dvere-interierove-dvere-dvere-klasik-plne-dub-archico-3d-povrch/136280p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019,https://eshop.unihobby.cz/bin/product/4/klasik-plne-dub-archico.jpg,990.00,1390.00
237002,Vinylová podlaha Kronostep - True Grit,https://eshop.unihobby.cz/stavba-podlahy-vinylove-podlahy-podlaha-vinylova-kronostep-true-grit-5-0mm/151328p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019,https://eshop.unihobby.cz/bin/product/4/237002-krono-original-krono-xonic-r018-true-grit-3.jpg.big.jpg,1058.00,1323.00
318417,Primalex Malvena na fasády 1 l,https://eshop.unihobby.cz/bydleni-barvy-a-laky-venkovni-barvy-primalex-malvena-na-fasady-1l/153358p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019,https://eshop.unihobby.cz/bin/product/4/318417-malvena-5l.jpg,199.00,
464431,Benzínová řetězová pila CSH46 Scheppach,https://eshop.unihobby.cz/zahrada-zahradni-technika-retezove-pily-benzinove-retezove-pily-pila-retezova-benzinova-csh46-scheppach/150162p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019,https://eshop.unihobby.cz/bin/product/4/464431-csh46-scheppach-diy-garten-titel-neu-na.jpg,3290.00,

...

But I want to have results as they are in where condition. Is possible to have results as they are in where condition? And why PG sort results when I  have no sort condition in my sql?

Thanks.


texts: restore database using bak file - resorting to old date?

texts:
Last night I made a full backup of my DB in SQL server management, when I restore the DB instead of being complete - it is missing all its data from the last few month.

The full backup was taken last night and is market as - 07/05/2014 16:54
However the information in the tables has restored itself to 24/02/2014

any help would be greatly appreciated -


texts: don't the users SYS and SYSTEM share the table dba_users?

texts:
While being logged in as SYS, I created a new user. I ran

 select username from dba_users order by username;

and I was able to see the user that i just created;

Then, I logged as SYSTEM, I ran the same query, but the user was missing this time.  This leads me to the question: don't all users have the same instance of the table DBA_USERS? I thought that this was a system related table, and all users get the same instance (as long as they have the right privileges to access this table).


texts: Which all system parameters to be considered for standard Vacuum process

texts:
We want to run standard vacuum process on our production database which is over 100 GB and have millions of dead tuples.

Can anyone suggest what system parameters we need to keep in mind for setting cost-based vacuum settings? I mean like CPU/IO/Memory/Disk.

We cannot run vacuum full as the database should be up and running continuously so we just want to attain most appropriate value without affecting the system much.


texts: T-SQL stored procedure: granting general user access to specific stored procedure

texts:
I'm attempting to alter a stored procedure that needs the line added:

grant exec on dbo.sproc_name to exec_proc

I believe the admin setup the exec_proc as a group. I added this grant line following the final go statement.

However, whenever I do this I get two errors:

  Cannot find the object 'x', because it does not exist or you do not have permission.

This is strange because when I try to call the stored procedure it says I also do not have permission now. Any thoughts?

Thanks in advance.


texts:
I have a query that takes more than 9 hours to complete.

Looking at the execution plan, I find this high-cost operator (query plan link):

How can I improve this job?


texts:
I have a client that really (really, REALLY!) wants to use a standard database server through a specific hosting company. That company offers vanilla installs of PostgreSQl and MySQL only.

The client has several running instances of web application software. They want those applications to be able to share a certain portion of their data.

That data is kept in a small handful of tables on each instance's database.

I immediately thought of some type of data replication or synchronization. Master-Slave or Master-Master replication would work fine. MySQL federation would probably work too.

But the problem comes back again to the hosting company. We won't have superuser access on the OS or the DB server, nor will we be able to modify the DB server's configuration files.

Question:
Without superuser access or access to the DB server's configuration files, is it possible to set up some type of data synchronization?

Everything I've read so far suggests that this is impossible. But, perhaps there's a method I've overlooked?

Note:
If it helps, each app's database will be hosted on the same DB server.


texts: What "tool" to use for monitoring instances and where to store this data?

texts:
I have three SQL Server instances in my DW project, development, test and production. I also have two SSIS servers (dev/test, production). My intention is to query data from these three SQL Server instances for monitoring purposes. My problem is what "tool" to use and where to store this administration data. My choices are

Create an admin database for each instance and store monitoring
data, queries and stored procedures separately
Create an admin database for e.g development database and query data from all instances into this database
Use SSIS to gather data from all instances and store this data into admin database (dev instance)?
Use PowerShell to query data from all instances and store this data into admin database (dev instance?)

What other choices do I have?


texts: Alternative to xp_cmdshell for emailing a report as a CSV file

texts:
I've got an issue that I could do with some ideas as to how to achieve what's needed without using (or enabling) xp_cmdshell if possible.

I know that xp_cmdshell itself poses risks, even with a proxy account, however - in our environment it's disabled and convincing the IT Manager to enable it is going to be difficult at best.

The issue I'm having is that I have a stored procedure, it looks up data from various tables and puts it all into a table variable.

I want to do the following:

Export the content of the table variable to a CSV file.
Attach the created CSV file to an e-mail and send this to a specific address.

This needs to happen automatically without user input of any kind, the e-mail side of it will be provided by variables in the same stored procedure.

I've thought of 2 ways to potentially do this, both use bcp and xp_cmdshell.

The first method was to create a temporary table, select the records I want from the table variable into the temp table, then use bcp to fire off an SSIS Package or SQL Agent Job which will query the temp table, export to CSV and e-mail for me.

The second method was to create a temporary table, select the records I want from the table variable into the temp table, then use bcp to select the temporary table into a CSV file, then use sp_send_dbmail to send it.

A third alternative might be to fire another SP/function and have it do either of the above methods instead, keeping the new functionality separate - but this is simply a thought to keep things simple.

So, what I'm asking for is any ideas as to how I can achieve this safely, preferably without using xp_cmdshell. I can't use PowerShell or a similar technology as no-one here knows PowerShell, although I'm familiar with VB.NET, however I'm not sure how that helps (if at all).

If you need more information to be able to help, please let me know.


texts: 2 SSRS Instances, one in native mode and one in sharepoint integrated mode with PowerPivot and PowerView on same server

texts:
Wanted to know if I can have two SSRS instances on the same server, one in native mode and one in SharePoint integrated mode.

I have one SSRS instance in native mode on a SQL server default instance (mssqlserver). I added the second SSRS PowerPivot instance to get PowerPivot/PowerView in SharePoint, but realized I have to have an SSRS instance in integrated mode as well to get this feature to work. When trying to add this SSRS instance in integrated mode to the PowerPivot SQL  instance, it only gives me the option for native mode and integrated mode is grayed out.

I am running SQL 2012 SP2 enterprise on the same server as SharePoint 2013 enterprise. I wanted to have both because our ERP utilizes the SSRS native mode in their reports and you can set security through the ERP and have the users view the reports within the ERP system. If this cannot be done, we will have to take all the reports and dashboards out of the ERP and manage them separately with the windows security in SharePoint.


texts: Read system_health event_file instead of ring_buffer for deadlocks extended events

texts:
I'm currently using this disaster to locate recent deadlocks by reading the system_health Extended Events ring buffer.

select top 2000000000
      XEvent.value('@timestamp', 'datetime2(3)') as CreationDateUtc,
      --
      -- Extract the <deadlock>...</deadlock> tag from within the event
      -- Todo: Surely there is a better (xml) way to do this.
      --
      substring(convert(varchar(max), XEvent.query('.')),
          -- start
          patindex('%<deadlock%', convert(varchar(max), XEvent.query('.'))),
          -- end
          patindex('%</deadlock%', convert(varchar(max), XEvent.query('.'))) -
              patindex('%<deadlock%', convert(varchar(max), XEvent.query('.'))) + 11 -- 11 to include for '</deadlock>'
          ) AS XdlFile
 from
    (
      select cast (target_data as xml) as TargetData
        from sys.dm_xe_session_targets st with (nolock)
        join sys.dm_xe_sessions s with (nolock)
          on s.address = st.event_session_address
       where [name] = 'system_health'
         and st.target_name = N'ring_buffer'
    ) as Data
 cross apply TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)
 order by CreationDateUtc desc

It works fine however the events don't seem to hang around very long (like 24 hours?)  I guess this is the "ring buffer" part of it.  Now I stumbled onto a link that was reading the system_health "file" which has similar information:

select event_data = CONVERT(XML, event_data)
from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
where event_data like '%xml_deadlock%'

Is this file the same as the ring buffer but just stays around longer?  Any disadvantages to using a file?  Does anyone with some XML skills want to convert the top script?

The goal is to copy/paste the XdlFile field into a new file and read it directly into SSMS or Sql Sentry Plan Explorer using "File Open".

Ring Buffer Links:

https://connect.microsoft.com/SQLServer/feedback/details/754115/xevents-system-health-does-not-catch-all-deadlocks#tabs

https://www.sqlskills.com/blogs/jonathan/why-i-hate-the-ring_buffer-target-in-extended-events/

http://www.sqlskills.com/blogs/jonathan/multi-victim-deadlocks/

https://www.sqlskills.com/blogs/jonathan/graphically-viewing-extended-events-deadlock-graphs/

http://www.mssqltips.com/sqlservertip/1234/capturing-sql-server-deadlock-information-in-xml-format/

http://blogs.msdn.com/b/sqldatabasetalk/archive/2013/05/01/tracking-down-deadlocks-in-sql-database.aspx

Deadlock error isn't returning the deadlock SQL

File:

https://www.mssqltips.com/sqlservertip/3636/query-data-from-extended-events-in-sql-server/

@@version = Microsoft SQL Server 2012 (SP3-CU5) (KB3180915) - 11.0.6544.0 (X64)


texts: Find exact and partial matches in the tables

texts:
I have two tables products and users. I need to find exact and partial matches in these tables.

For example, there is a user with the username cat, and there is a product with the name cat. And there are products with the names of a good cat, red cat.

I want to get exact matches and partial matches. That is, get a user and three products.

I tried to do so, but the query returns an empty body.

SELECT P.*, U.*
    FROM PRODUCTS AS P
        LEFT JOIN USERS AS U ON (U.USER_ID = P.STORE_ID)
            WHERE
                P.PRODUCT_NAME = 'cat' OR P.PRODUCT_NAME LIKE ('%cat%')
                AND U.USERNAME = 'cat' OR U.USERNAME LIKE ('%cat%')
                    GROUP BY P.PRODUCT_ID, U.USER_ID

How do I get a user and products for the word cat? Partial and exact matches.


texts: How to see the original text of the the function that has been wrapped by "DBMS_DDL.WRAP"

texts:
As I'm new to Oracle PL-SQL,I'm trying to be familiar with DBMS_DDL package and I used it to hide my source code,below you can see the example I'm working on:
Declare
  v_string varchar2(32676);
begin

  v_string := 'create or replace function get_os_user
                 return varchar2 ' || ' is  ' || ' begin ' ||
              ' return (sys_context(''userenv'',''os_user''));' || ' end; ';

  execute immediate sys.dbms_ddl.wrap(v_string);
end;

The function get_os_user is created in the function section and In the view mode , I see the non readable hex format of the original function.Now my question is how I can see the original format of the function? The way the book from which I read about the package just said this:
The original text can be viewed in DBA/ALL/USER_SOURCE

Where is DBA/ALL/USER_SOURCE? Are there other ways to see the original format of wrapped functions and procedures?


texts: Does SQL Server 2008 store the creation date of execution plans?

texts:
We recently upgraded an application we use, which involved modifying the schema for the database. These changes could have forced cached execution plans to be discarded. If SQL Server was forced to create a bunch of new plans, this could have slowed down the user experience. I'd like to find out if this was the case.

So, my question is, does SQL Server 2008 store the creation date of cached execution plans? The management view sys.dm_exec_cached_plans doesn't have any date fields, so I suspect not.


texts:
I am trying to install Oracle Database 10.2 on OEL5U6(Linux System). I followed all the instructions from HERE.
When I am executing installer this return:

  Preparing to launch Oracle Universal Installer from
  /tmp/OraInstall2013-08-06_12-57-33AM. Please wait ...The Java RunTime
  Environment was not found at
  /tmp/OraInstall2013-08-06_12-57-33AM/jre/bin/java. Hence, the Oracle
  Universal Installer cannot be run. Please visit
  http://www.javasoft.com and install JRE version 1.3.1 or higher and
  try again. : No such file or directory.

But Java is already installed on my system.


texts: Query to identify people exceeding a particular amount within an interval of 6 months

texts:
I have tried searching in Google but am just not finding the right post.
I have this table with fields trans_date, CID (which is customerid), and amount.

How do I find the CID exceeding an amount of say $1000 within any time period of 6 months or 180 days (eg. Jan-June or Feb-July, or November 2014-April 2015 (periods overlapping years too)) starting from the beginning of time?

trans_date             CID        amount
22-Jun-2015            123        550
24-Aug-2015            999        200
15-Aug-2015            123        500
24-nov-2014            321        200
08-Jan-2015            321        900
24-oct-2015            999        200

In the result CID 123 and 321 should be the output both exceeding the total amount 1000 in a span of 6 months.

Any response would be highly appreciated.


texts:
I'm using ExpressMaint and Windows Scheduled Tasks to create weekly full and daily differential backups of some MS SQL Server 2008 R2 Express databases

Weekly fulls

expressmaint -S (local)\SQLExpress -D ALL -T DB -R D:\Backup\Reports -RU WEEKS -RV 1 -B D:\Backup\Data -BU WEEKS -BV 4 -V -C

Daily Diffs

expressmain -S (local)\SQLExpress -D ALL -T DIF -R D:\Backup\Reports -RU WEEKS -RV 1 -B D:\Backup\Data -BU days -BV 7 -V -C

When I come to restore these to a certain point, I have to restore each backup individually. Is there any way I can chain a series of backups into a single restore that will be re-played in the correct order?

When I try this, I get the error

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

  ADDITIONAL INFORMATION:

  The media loaded on "C:\Foo\Bar_FullBackup_20110130_2346.bak" is formatted to support 1 media families, but 2 media families are expected according to the backup device specification.
  RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3231)

  For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=3231&LinkId=20476

Edit

I am using the restore dialog from the tasks menu when you right click on a database. From there, I select "Restore from device" and Add the .bak files I wish to restore from.

If I only add a single .bak file, I'm OK, if I add multiple files, I receive the error above.


texts: ORA-12514 for Oracle Devsuite 10g and Oracle DB 11g on the same host

texts:
I have oracle devsuite 10g (forms, reports, etc...) on the same host as Oracle database 11g... Devsuite was installed prior to Oracle DB 11g.

When I go in Oracle DB 11g and launch sqlplus sys as sysdba, it works fine.

When I go to DevsuiteHome/bin and launch its version of sqlplus, it does not. The descriptor I supply here is orcl

The two products have their tnsnames.ora file. I made sure that both use the same values as orcl, hence, we have for both :

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

But, it does not work. When I try to connect in Oracle forms or using DevsuiteHOme/bin sqlplus, I get the error :

  ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

How can I solve this?


texts: how to view data as history of field value change

texts:
Example

i have data like

id   employeeId Salary       Year
1      1           12000     2001
1      2           10000     2001
1      1           16000     2002
1      3           12000     2004
1      1           18000     2005
1      3           14000     2006

So employee ID 1 has 3 records in this and 2  has one and 3 has two record
i expect output as

EmployeeID  NewSalary Oldalary
1             18000    16000
1             16000    12000
2             10000    -
3             14000    12000`


texts: Which should be used after messing up clustered index? Update statistics, Reindex or Reorganize

texts:
If I have a clustered single column index, e.g FooId in SQL Server 2008, 2012 and I know I have messed up the order of the rows to not have sequential order of the FooId column anymore, which command/s should be used:

Update statistics
Rebuild
Reorganize

EDIT

Scenario
FooId is a clustered pk of type sequential uniqueidentifier, but for keeping it simple it this post we use #1 as id values.

#1 inserted

#2 inserted

#3 inserted

#1 selected

#1 deleted

#2 selected

#2 deleted

#2 inserted (that is reusing the clustered PK of #1)

#1 inserted (that is reusing the clustered PK of #1)

Isn't this going to break the sequential order? Will it not be stored like this now:

#3

#2

#1

//Daniel


texts: SQL query: insert values into polygon where features of the polygon intersects another polygon

texts:
I'm very new to sql so I apologize in advance for my very dummy question...
I'm looking for a way to insert into a table A some values stored in another table B only for the features of table A that intersects features of table B.
I tried this:
insert into schema.table_A(columnKJ1)
select "columnPO4" from schema.table_B
WHERE table_A.geom.STintersects(table_B.geom) = 1

but I get this error message: cross-database references are not implemented
What is the right way to do that please ?


texts: Alter the letters after "ACCOUNTS\" to lower case?

texts:
I have a database table, users, with different username cases (see the example). How can I update the letters after ACCOUNTS\ to lower case?

ACCOUNTS\James.McAvoy
ACCOUNTS\michael.fassbender
ACCOUNTS\nicholas.hoult
ACCOUNTS\Oscar.Isaac

Desired result after the update:

ACCOUNTS\james.mcavoy
ACCOUNTS\michael.fassbender
ACCOUNTS\nicholas.hoult
ACCOUNTS\oscar.isaac


texts: Database suggestion for a social network/ knowledge base community?

texts:
I am looking into various database types and DBMS's for a new project I am wanting to start in the summer.

I have built systems in MySQL and postgreSQL , now I am wanting to expand my knowledge and experience in Databases.

My project will be a type of social network / aggregate knowledge thing. (still havent developed a term to describe it yet).

I have been looking at:

Cassandra (use its own type of query language); It seems to be good for feature rich content and delivering high performance query execution. However I am not too keen on it because it requires a java environment to work on and I would prefer to have nothing to do with Oracle.
MongoDB (noSQL type of DBMS) ; great scalability however you lose all the capabilities already available on the proven SQL language like business information queries.

Requirements of the system:

Data Text, dates , times, xml, small ints, blob,
Structure/behavioir: normalised 3NF, non realtime, relational , scalable, robust
Environment: unix/linux , no JAVA!, preferably run on C

I was wondering if you could point me to any other Database systems that I should research into.

I have also had a look at Object Relational Databases , I quite like the idea of them working with PHP objects (PDO's) however their performance seems a bit poor.

Seeing as there will be DBA's here, any feedback on these systems that you have operated would be appreciated.

Thanks


texts: The application is not installed - Visual Studio 2013

texts:
I need to deploy packages using visual studio 2013 but I am seen this message, the application is not installed, as you can see on the picture below.

what do I need to install?


texts: What is the logic behind ISNUMERIC for certain special characters?

texts:
The ISNUMERIC function has some unexpected behavior. The MSDN documentation says:

  ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. Valid numeric data types include the following: int, bigint, smallint, tinyint, decimal, numeric, money, smallmoney, float, real.

And it also has a footnote:

  ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols, see money and smallmoney (Transact-SQL).

Okay, so +, -, and listed currency symbols are expected to be considered numeric. So far so good.

Now for the odd part. First up, some of the currency symbols from linked article are not numeric, including:

Euro-Currency sign, hex 20A0: ₠
Naira sign, hex 20A6: ₦
Rial sign, hex FDFC: ﷼

This is weird, and I can't seem to find out why? Is this version or environment dependent?

However, things get weirder. Here are a few others I can't explain:

/ is not numeric, but \ is (Huh?!)
REPLICATE(N'9', 308) is numeric, but REPLICATE(N'9', 309) is not

The first and most basic question is: what explains the above cases? More importantly though: what is the logic behind ISNUMERIC, so I could explain / predict all cases myself?

Here's a good way to reproduce things:

DECLARE @tbl TABLE(txt NVARCHAR(1000));

INSERT INTO @tbl (txt)
VALUES (N''), (N' '), (N'€'), (N'$'), (N'$$'),
       (NCHAR(8356)), (NCHAR(8352)), (NCHAR(8358)), (NCHAR(65020)),
       (N'+'), (N'-'), (N'/'), (N'\'), (N'_'), (N'e'), (N'1e'), (N'e1'), (N'1e1'),
       (N'1'), (N'-1'), (N'+1'), (N'1+1'), (N'⒈'), (N''), (N'¹'), (N'①'), (N'½'),
       (N''), (REPLICATE(N'9', 307)), (REPLICATE(N'9', 308)), (REPLICATE(N'9', 309)),
       (REPLICATE(N'9', 310));

SELECT  UNICODE(LEFT(txt, 1)) AS FirstCharAsInt,
        LEN(txt) AS TxtLength,
        txt AS Txt,
        ISNUMERIC(txt) AS [ISNUMERIC]
FROM    @tbl;

When I run this on my local Sql Server 2012 box I get the following results:

FirstCharAsInt   TxtLength   Txt        ISNUMERIC
---------------  ----------  ---------  ----------
NULL             0                      0
32               0                      0
8364             1           €          1
36               1           $          1
36               2           $$         0
8356             1           ₤          1
8352             1           ₠          0  --??
8358             1           ₦          0  --??
65020            1           ﷼          0  --??
43               1           +          1
45               1           -          1
47               1           /          0
92               1           \          1  --??
95               1           _          0
101              1           e          0
49               2           1e         0
101              2           e1         0
49               3           1e1        1
49               1           1          1
45               2           -1         1
43               2           +1         1
49               3           1+1        0
9352             1           ⒈         0
55356            2                     0
185              1           ¹          0
9312             1           ①          0
189              1           ½          0
55356            2                    0
57               307        /*...*/     1
57               308        /*...*/     1  --??
57               309        /*...*/     0  --??
57               310        /*...*/     0


texts: Find rows where column matches one of n strings

texts:
I am trying to find the best way to see if multiple email addresses already exist in my database. I have a table with member id's, emails, and other info, and I need to import members but to check if members already exist. The amount of member that I will import will vary because it will be CSV files from users.

I dont want to make a really long "email LIKE  OR " ... etc.

I was thinking of making a temporary table and then union that table to my members table, but worried about performance. Any other ideas?

Additional Information:

I read in a lot of names and email addresses, and I need to check if those email addresses are already in the database. But instead of making numerous calls to the DB, or a really long where clause similar to "WHERE email LIKE '' OR email LIKE '' OR ...", I wanted to find a better way or getting emails that are either already in the database.

In the end, I did something like this for the where clause:

"email IN ('" . implode( "','", $inviteEmails ) . "')"

where $inviteEmails is an array of emails I am going to insert as users for the site.


texts: Help to decrese query time in huge table of 100M+ rows on Ms-SQL Server

texts:
I am trying to get data using offset limit concept as follows:

CREATE PROCEDURE BaseUserIndex
    @Offset int,
    @Limit int,
    @SortCol int,
    @SortDir nvarchar(10),
    @Search nvarchar(255) = NULL
AS BEGIN
    SET NOCOUNT ON;
    SELECT id,
    name,
    address,
    city,
    FROM users
    WHERE deleted_at IS NULL
    ORDER BY
        case when (@SortCol = 0 and @SortDir='asc')
        then name
        end asc,
        case when (@SortCol = 0 and @SortDir='desc')
        then name
        end desc,

        case when (@SortCol = 1 and @SortDir='asc')
        then address
        end asc,
        case when (@SortCol = 1 and @SortDir='desc')
        then address
        end desc,

        case when (@SortCol = 2 and @SortDir='asc')
        then city
        end asc,
        case when (@SortCol = 2 and @SortDir='desc')
        then city
        end desc
    OFFSET @Offset ROWS
    FETCH NEXT @Limit ROWS ONLY
END

But, this code snippet is taking around 15 sec, to fetch data. Is there any way to improve this?

As I am new to Ms SQL. Is there any way to decrease this?


texts: Check Java Driver version in clients from Db2 server

texts:
I have a Db2 database that is being used by many applications in different servers. I want to check from the Db2 server if the Java driver used in the different client applications is up-to-date.

Is the any way to check the version of the java driver client from the Db2 server?
I have tried the following without success:

db2 list application show detail
db2 "SELECT *  FROM TABLE(MON_GET_CONNECTION(cast(NULL as bigint), -2)) AS t"

In the last command, the CLIENT_PRDID column could have some of this information, but it only shows: JCC04110 and I do not know what does it mean.


texts: Is it possible for MySQL v5.7.6 to to implement mesh replication with multi-source replication

texts:
As I know starting from MySQL v5.7.6 the multi-source replication is possible. So If we can have a slave that can get data from multiple masters. Can we have mesh replication, where each node act as a slave for all other nodes ?

Mesh Replication

If we have 4 nodes (N1, N2, N3, N4)

N1 is a slave for N2, N3, N4.
N2 is a slave for N1, N3, N4.
N3 is a slave for N1, N2, N4.
N4 is a slave for N1, N2, N3.

If yes we can do that ? How nodes detect duplicated events in such scenario:

A new change in N1 is happened.
N1 pushes this change into N2, N3, N4.
The new change is received at N2 (and the other nodes).
How N2 does't replicate it again ! Otherwise we will have a infinite loop !


texts:
I'm using a queue to crawl pages and track links between them.

I have a table called page with a primary key called page_id.

I have a table called links with two columns: link_from, link_to

The queue takes a page, saves it, collects all links from it and saves those. The link_to value in some rows points to a non-existent row (temporarily, until the target pops out of the queue).

From a long-term point of view, I'd like to connect them with foreign keys (for multiple reasons, e.g. delete cascading, automatic relationship discovery from an application which uses the database).

How do you do such a thing? I can't insert the row with a foreign key reference until the row exists.


texts: Creating Oracle 12c R2 user with default collation binary_ci fails

texts:
If anyone can give me some guidance on this one I'd really appreciate it.

I am attempting to get a user under Oracle 12c R2 configured with default collation binary_ci.

CREATE USER <some user> IDENTIFIED BY <some password> DEFAULT COLLATION BINARY_CI;

The output I get from the above is as follows:

CREATE USER <some user>IDENTIFIED BY <some password>DEFAULT COLLATION BINARY_CI
                                     *
ERROR at line 1:
ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD is set.

Now, I am doing this under a Vagrant installation from https://github.com/totalamateurhour/oracle-12.2-vagrant. This essentially sets up a basic Oracle database in PDB/CDB mode. (last time I touched Oracle was 9... something so bear with me).

My DBAs have given some guidance on this but they said they were not able to get this to work in PDB mode.

Under SQL*plus execute:

CONN / AS SYSDBA
ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE;
EXIT;

At the command prompt:

$ cd $ORACLE_HOME/rdbms/admin/
$ $ORACLE_HOME/perl/bin/perl catcon.pl -d $ORACLE_HOME/rdbms/admin -b /tmp/utl32k_output utl32k.sql

Then back in SQL*plus execute:

CONN / AS SYSDBA
SHUTDOWN IMMEDIATE;
STARTUP;

After executing the above and attempting to create the user as I stated earlier I still get the same error.

If I query the configuration I see the following:

SQL> show parameter max_string_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED
SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> show parameter max_string_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED
SQL>

As I said, any help would be GREATLY appreciated.


texts: Can I trust the Database Engine Tuning Advisor recommendations?

texts:
Our database has become large, and we are trying to optimize performance.

Can we follow the recommendations provided by the Database Engine Tuning Advisor by supplying it a .trc file generated with Profiler?

Is this a good way to optimize? How accurate are these recommendations?


texts: MySQL: I need to return rows according to certain conditions


texts:
SELECT name FROM sys.databases
WHERE
name
not in
('master','model','msdb','tempdb','ReportServer','ReportServerTempDB','1303VISBOC')
AND
CASE WHEN state_desc = 'ONLINE'
THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[_LOOK_UP_TABLE]', 'U')
END IS NOT NULL

I'm using the above to populate a combobox in a c# winforms project. -The reason I'm filtering out databases that don't include the table is for security purposes.

I added the WHERE clause as before I did that it was taking up to 21 seconds!?
(This is on a local dev machine with 4 databases(not including system DB's), only one of which has the table I was looking for.)

Now that I have added the where clause it only takes nine seconds.

I can't have it running this slowly, as the program I am writing is going to be in use on machines that can have as many as 20 databases on them. -All with around 35 tables in.
(These aren't servers, these are Support Monkey Machines!)

From the Actual Execution Plan I can see that there are three Clustered Index Seek's taking up 22%, 24% and 48%. -Because this is a 'system' query is there anything I can actually do about that?!

And finally, the bombshell to make this a tad bit harder:
I have no control over the indexes of the databases and I can't change them in anyway, shape or form.


texts: Harvester query to identify databases where the ID has reset

texts: I need a MySQL query to identify if a database has a table where the auto-incremented ID has reset back to 0. This can result in non-unique rows for those IDs. I'm not sure how to write this. Thanks in advance for any help!


texts: Nested set model: retrieve sub-tree containing nodes that match condition

texts:
I'm using the "nested set model" (the backend used by our web application is CakePHP and the Project model uses the TreeBehavior).

This is my "project" table:

+----+-------+-----------+-----+------+
| id | name  | parent_id | lft | rght |
+----+-------+-----------+-----+------+
|  1 | P1    | NULL      |   1 |    6 |
|  2 | P2    | NULL      |   7 |   12 |
|  3 | P2.1  | 2         |   8 |    9 |
|  4 | P2.2  | 2         |  10 |   11 |
|  5 | P1.1  | 1         |   2 |    3 |
|  6 | P1.2x | 1         |   4 |    5 |
|  7 | P3x   | NULL      |  13 |   18 |
|  8 | P3.1  | 7         |  14 |   15 |
|  9 | P3.2  | 7         |  16 |   17 |
+----+-------+-----------+-----+------+

I need to write a query that will return all projects which name match a certain string and also their parent or children.

So if the string used is "x" ( project.name like '%x%') then the query should return P3.x, P3.1, P3.2, P1.2x and P1.

You can assume that there won't be more than one level but if you have a solution that works for unlimited levels that is even better :)

I have a query that works but it is probably also the least efficient and does not take advantages of the fact that I'm using the nested set model.

SELECT
  *
FROM sr_projects
WHERE id IN (SELECT
  id
FROM sr_projects
WHERE parent_id IN (SELECT
  id
FROM sr_projects
WHERE name LIKE '%x%'))
OR id IN (SELECT
  id
FROM sr_projects
WHERE id IN (SELECT
  parent_id
FROM sr_projects
WHERE name LIKE '%x%'))
OR id IN (SELECT
  id
FROM sr_projects
WHERE name LIKE '%x%');

If cannot use stored procedure because CakePHP does not support them.


texts: SQL Server table size increased with column data type change

texts:
I have a SQL Server table with 39 million rows. I changed the data type of existing columns as follows:

Col A -> tinyint -> bit
Col B -> tinyint -> bit
Col C -> tinyint -> bit
Col D -> tinyint -> bit
Col E -> tinyint -> bit
Col F -> smallint -> tinyint
Col G -> smallint -> tinyint

The table size was supposed to have shrunk, however, the size increased from 11gb to 18gb.

I tried the following:

Rebuild all indexes
DBCC SHRINKDATABASE ('DBName')

however it did not work.

Any idea where to look for the problem?


texts: Designing User & its Sub User Database Diagram Problem

texts:
There are some business category e.g. Travel Agency, Hotel, Car Rental etc.
Roles:

Management rolesSuper Admin, Staff
Customer side roles are Owner, admin and sales counter
May be etc. customer side roles will be added

Status i.e. On-Hold, Active, In-Active, Blocked, Deleted etc.
Scenario:

Each user in the database has its role and status with other credentials
Super Admin or Staff creates a customer (USER) with category e.g. "Travel Agency" and provide owner details
Now this Travel Agency (USER) has its own SUB_USERS that may be one or many

Now I am confused between user and sub users.
The work that I done so far is as following in shape of ERD:

Please put me on the right path as to how can I create a USER with its SUB USERS?
Thanks in anticipation


texts:
I have two sql server S1(2008) and S2(2005).

S2 is added to S1 as linked server.

S2 have a table Product.

When I try to run the code

select ProductID from Product

I get the error:

Location:    rmt_meta.cpp:347
Expression:  fFalse
SPID:        71
Process ID:  3828
Description:     Unknown column in remote index! Skipping.

Any idea?


texts:
I have Oracle 9i installed on machine connected to the internet behind NAT, and the Oracle DB server is listening on default port 1521, when I connect over the LAN IP to the database server it works perfectly, and I did  port forwarding on port 1521 to the private IP address in tp-link router to make the Oracle client machine connect to Oracle DB server over public IP, for example: 1521-192.168.0.108 forwarding port and its successful on canyouseeme.org but when I use this command:

SQL>connect username/password@GLOBAL_IP/service_name
Error: ORA_12170: TNS : Connect timeout occurred

*note: when I try to connect to the Oracle database server using Teamviewer vpn, it also  works and connected so how to deal with this ?


texts:
How can I set up a server side trace that dumps to a database instead of a trace file?

I set-up and tested a trace configuration I want with Sql Server Profiler that dumps only needed info straight to a separate database (without a .trc file).
When I export this trace as sql statement however, I see it is configured to save to .trc file instead of a database.

Either my googling-fu is letting me down or nobody is interested in this.


texts: How do you directly modify the system catalogs in SQL Server 2017?

texts:
NOTE: I understand the risks involved, and the possibility of destroying a production system by doing this. I'm interested in doing it anyway.

Whenever I try to play with system catalogs, I gets these weird errors,

UPDATE sys.sql_logins
SET password_hash = PWDENCRYPT('pass')
WHERE name = 'sa';

Error produced,

  Msg 259 [...] Ad hoc updates to system catalogs are not allowed.

I've tried numerious ways to take the training wheels off,

sp_configure 'allow updates',`
go
reconfigure
go

But, I can't figure out the right option...

It even says in this answer:

  this is no longer possible (at least not without jumping through a ton of additional hoops beyond just an sp_configure option - it's not something you ever want to do on a production system), and all of the system catalog is now exposed through read-only views like sys.objects.

Well, what if I want to jump through those hoops. How can I do it?


texts: MySQL - SELECT on MyISAM Table on index - Does table size (rowcount) matter?

texts:
I was arguing with a coworker if SELECT * WHERE t BETWEEN ? AND ? on a MyIsam table where t is an index is significantly slower for huge tables. The hypothetically  returned dataset is always the same, but in one query makes up for .1% of the tables content, and for the other... say 90%.

The table we were speaking about is used for logging. This means the index for the time t is increased chronologically, which makes analyzing the table unneccessary.

My explanation was that other then searching the index to find the first and the last row (aka the first and the last reference to the data) the performance should be the same. It would take a bit longer to search a long index compared to a small one - but returning the data should take the same time. Is this right or am I completely off the track on this one?

Thanks!


texts: Optimizing simple query that performs a large table migration

texts:
There are two tables trains and train_statuses.
train_statuses already has a column train_status which is an ENUM.
CREATE TYPE train_status AS ENUM('queued', 'running', 'succeeded', 'failed', 'cancelled');

As part of the migration, I will be adding a new column status in trains table and updating it with only the final status from train_statuses. The final statuses are: 'succeeded', 'failed', 'cancelled'.
Other relevant details about the tables:
Indexes:
Trains table:
    "trains_pkey" PRIMARY KEY, btree (id)
     "idx_trains_queued_at" btree (queued_at DESC)

Train statuses table:
    "train_statuses_id_updated_at_key" UNIQUE CONSTRAINT, btree (id, updated_at)
    "idx_train_statuses_id_updated_at" btree (id, updated_at)
Foreign-key constraints:
    "train_statuses_id_fkey" FOREIGN KEY (id) REFERENCES trains(id) ON DELETE CASCADE

This is the query that I am planning on using to update status in trains table.
UPDATE trains AS t SET status = (
    SELECT status FROM train_statuses ts
    WHERE ts.id = t.id AND status in (?)
    ORDER BY ts.updated_at DESC LIMIT 1
) WHERE ID in (
    SELECT id FROM trains LIMIT 100
)

I would be calling this query in a for loop until I get the number of rows affected as zero.
Even though this is a simple migration, the table involved is extremely huge and occupies ~150GB on production. Please review this query and suggest any possible optimizations that could be done taking into consideration the size of the table.
Thanks


texts: Storing Arbitrarily Ordered Heirarchal Data in MySQL

texts:
I am trying to store a list where the users can insert new comments anywhere within the list and have it remain in that same ordering Moving subtrees around should not happen very often. The current UI demo is at http://spencerflem.github.io/.

Having done some research, I have found 2 solutions that seem good in different ways and was wondering which would be most recommended. From what I can tell, the simplest solution of simply storing the parent_id will not work with MySQL as it has no CONNECT BY and would need a much more complicated recursive function. Similarly, nested sets and path enumeration seem to be more complicated to maintain, and have more overhead when adding new items.

The first solution I have found is to use a closure table which stores the path from each comment to each descendant. The breadcrumbs are stored as a comma separated varchar list or the ID's, such that ORDER BY breadcrumbs would sort everything correctly alphabetically. https://stackoverflow.com/questions/14069674/sorting-a-subtree-in-a-closure-table-hierarchical-data-structure This seems far more complicated but more idiomatic.

It wasn't mentioned, but could the IDs have large gaps between such that inserting between would not have to renumber everything? I suspect that would be fine, however I have only ever seen AUTO INCREMENT.

A simpler way would be to merely store the breadcrumbs string, perhaps even as the PRIMARY KEY. This would also return the correct order, but it seems much less idiomatic.

Which solution would you recommend, or is there another one that I have missed? Thanks!


texts:
I have a table called tblOrderNumber which has 1 row and 1 column.  This table stores what the next order number will be for my ecommerce website.  It is ABSOLUTELY VITAL that the same order number is not used more than once.  Currently the team are using this stored procedure and it seems to work fine:

My question is does UPDLOCK guarantee this? I would have thought a Read Lock is required on the SELECT too (in the unlikely case that 2 orders are placed within a millisecond of each other and the first one has not done the UPDATE before the second one had done a SELECT, as I understand there is no read lock in this procedure)?

DECLARE @NextOrderNumber INT

BEGIN TRANSACTION

SELECT @NextOrderNumber = NextOrderNumber
FROM  tblOrderNumber (UPDLOCK)

UPDATE tblOrderNumber
SET   NextOrderNumber = NextOrderNumber + 1

COMMIT

SELECT @NextOrderNumber

--Customer Implementation (Would UPDLOCK OR SERIALIZABLE be better here as I don't think we need to lock the full table?)

UPDATE dbo.tblOrderNumber WITH (SERIALIZABLE)
SET @NextOrderNumber = NextOrderNumber,
    NextOrderNumber = NextOrderNumber + 1;
WHERE CustomerId=@CustomerId

I am using SQL Server 2014 but I will be changing to SQL Azure soon.


texts: Get contiguous date ranges in grouped data

texts:
CREATE TABLE `rings` (
`ID_RingType` char(2) default NULL,
`Number` date default NULL,
`ID_User` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `rings` (`ID_RingType`, `Number`, `ID_User`) VALUES
('AA', '2018-09-01', 1),
('AA', '2018-09-02', 1),
('AA', '2018-09-03', 1),
('AA', '2018-09-11', 1),
('AA', '2018-09-12', 1),
('AA', '2018-09-13', 1),
('AA', '2018-09-14', 1),
('AA', '2018-09-15', 1),
('AB', '2018-09-16', 1),
('AB', '2018-09-17', 1),
('AB', '2018-09-18', 1),
('AB', '2018-09-19', 1),
('AB', '2018-09-20', 2),
('AB', '2018-09-21', 2),
('AB', '2018-09-22', 2);

I wish to group the data based on ID_User and ID_RingType and for each contiguous range of dates list the MIN and MAX.

The results should look like:

  ID_User   ID_RingType    MIN( Number )    MAX( Number )
       1      AA            2018-09-01        2018-09-03
       1      AA            2018-09-11        2018-09-15
       1      AB            2018-09-16        2018-09-19
       2      AB            2018-09-20        2018-09-22

This is what i tried :

      SET @grp = NULL ;# MySQL returned an empty result set (i.e. zero
       rows).
      # MySQL returned an empty result set (i.e. zero rows).
      # MySQL returned an empty result set (i.e. zero rows).
       SET @preNum = NULL ;# MySQL returned an empty result set (i.e. zero
      rows).
      # MySQL returned an empty result set (i.e. zero rows).
      # MySQL returned an empty result set (i.e. zero rows).
      SELECT ID_User, ID_RingType, MIN( Number ) , MAX( Number )
     FROM (

     SELECT ID_RingType, ID_User, Number, @grp :=
     CASE WHEN Number = DATE_ADD( @preNum , INTERVAL 1
     DAY )
     THEN @grp
     ELSE DATE_ADD( @grp , INTERVAL 1
     DAY )
     END grp, @preNum := Number
     FROM  `Rings`
     ORDER BY ID_RingType, ID_User, Number
     )T
     GROUP BY ID_RingType, ID_User, grp

Any Help appreciated.


texts: RDS MySQL 8.0.16 has empty "INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS" table

texts:
On aws RDS I have an mysql 8.0.16 instance running.
My problem is now, that the INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS table is empty.

This is a problem for me because I would like to create the following table:

CREATE TABLE my_table
(
    ...
    coordinates            POINT         NOT NULL SRID 4326
    ...
);

But this leads to an error message that says There's no spatial reference system with SRID 4326.

Whats going on here? Why is the table empty? I would expect it to contain at least one entry for the SRID 4326.


texts: Is it possible to insert un-formatted data for money data type in PostgreSQL

texts:
By default, PostgreSQL's money data type inserts values with currency code and grouping. I have observed that it always inserts values with $.

How can I insert locale specific values?
How can I insert values with my own digit grouping and decimal grouping formats?
How can I read unformatted data?
Why is PostgreSQL's money data type different than MS SQL's money data type?


texts: How to get all data before and after 10 days of interval?

texts:
SELECT * FROM events  WHERE `date`
    BETWEEN NOW() AND DATE_ADD
    (NOW(), INTERVAL 10 DAY) order by date

I am using the above code to extract the events data of ten days of interval. Now I want to Extract out all the data before this 10 days of interval and after 10 days of interval. How can I do that in a perfect way?

Please help. Advance thanks for your time


texts: In SQL Server, is parallelism per operator, or something else?

texts:
I work with a really old DBA who says a lot of weird stuff. Dude has an O'Reilly book that only has an amoeba on the cover.

At lunch we were talking about parallelism, because our new server has 24 cores. He says that in a parallel plan, every operator gets DOP threads. So if you have MAXDOP 8 and your query has 4 parallel operators it'll use 32 threads at once.

That doesn't seem right because you'd run out of threads really fast.

I also read that it might just be 8 for the whole query, which seems like too few.

Why Do I see more threads per SPID in sysprocesses than MAXDOP?

Are either of them right?


texts: How to turn off logging of "Software Usage Metrics enabled" and ESENT errors

texts:
I am running SQL Server 2017 Standard CU 5 in a failover cluster with two nodes (single instance cluster). I have followed these two manuals to deactivate CEIP aka spyware features:

The CEIP service is disabled and stopped,
the CEIP role removed from the cluster
and all registry entries regarding CustomerFeedback and EnableErrorReporting are set to 0.

However I still get the message "Software Usage Metrics is enabled." in SQL Server error logs after a service restart. Additionally there are frequent errors from ESENT like the following in my windows event log:

There is a MS Knowledge Base article for this problem. However it is for SQL Server 2012. It says that this error is related to the "Software Usage Metrics feature". I already followed the advice to grant permissions for the SQL Server service account. However the error message keeps appearing and I don't want Microsoft to collect data anyway.

Here are my questions:

Is that the same feature as the CEIP stuff or something else?
How do I deactivate Software Usage Metrics correctly?


texts: replicate-wild-ignore-table does not stop already logged queries on slave

texts:
We have a master DB where where multiple queries where fired on a specific table, these queries were taking too long to execute on slave and it was lagging behind and as a result of which queries were queued and executing one by one.

Later we identified that this table was not required to be replicated and we added this table to replicate-wild-ignore-table on the slave's my.cnf and restarted the slave. But the slave again picked up the remaining queries for that table. Ideally it should have skipped the queries for that table.
We are not able to figure out why this is happening.

Can somebody explain?


texts:
I want to show a sum for each month that falls between the variables @startdate and @enddate - I think my issue is stemming from the Group By I have to add at the end so SSMS does not bark at me.

Current output:

Year Month Cash
1900  01   300

I want to see:

Year    Month   Cash
2016    01      150
2016    02      50
2016    03      100

Here is query I attempt:

Declare @startdate date = '20160101', @enddate date = '20160331'

SELECT
YEAR(satDate) [Year], MONTH(satDate) [Month],
SUM(ISNULL(A.truckrv,0)-ISNULL(A.DC,0)) As CashAmt
FROM
(
    SELECT
    ZT1.empName,
    SUM(ISNULL(ZT1.[truckrv],0)) AS truckrv,
    drivedate As TDate,
    0 As satDate,
    0 As DC
    FROM truck AS ZT1
    WHERE ZT1.[drivedate] >= @startdate
    AND ZT1.[drivedate] <= @enddate
    GROUP BY ZT1.empName, ZT1.drivedate
    UNION ALL
    Select
    t1.empName,
    0 As Tdate,
    satDate
    ,0 As truckrv
    ,SUM(ISNULL(t1.DC,0)) As DC
    FROM car t1
    WHERE t1.[satDate] >= @startdate
    AND t1.[satDate] <= @enddate
    Group By t1.empName, t1.satDate
) AS A
GROUP BY A.satDate

How should this query be altered in order to provide a "Month by Month" result set?


texts: Does simple recovery model not log bulk inserts?

texts:
I have a db in full recovery model.
There is a planned insert of a million rows that needs to be done next week.
In order to not increase the log size, I am told there are 2 options.
Option 1 is switch to bulk model, perform the insert and then switch to full model. This makes perfect sense.
Option 2 is switch to simple recovery model, perform the insert and then switch to full model. Is this really true? Does simple recovery model not increase the log size during bulk insert? I know that log is cleared at each checkpoint, but during bulk insert does the checkpoint occur?


texts: SSRS Timeline Chart for each day / minute

texts:
I'm asked to make a Timeline Bar Chart with SSRS 2012.
There should be 1 chart per day/employee, and this chart should have a x-axis of 1440 minutes (or 24 hours).
The bar should have 2 different colors, which represent the 2 different status of the employee (working / not working).

The table from which I get the data has following columns:

    Starttime_working (datetime), Endtime_Working (datetime), Duration_worktime (int), Duration_till_next_worktime(int)

There could be many of this entries per day for one employee. The duration columns are seconds.

I think I have to transform this data to get a result like:

    2013-03-02 16:52:00 - working
    2013-03-02 16:53:00 - working
    2013-03-02 16:54:00 - working
    2013-03-02 16:55:00 - working
    2013-03-02 16:56:00 - not working
    2013-03-02 16:57:00 - not working

Is this the right data format for generating a Timeline Chart?
Are there other ways to get what I want?
Any general tips for creating a timeline-chart with ssrs?


texts: Can I combine the results from multiple columns into a single column without UNION?

texts:
I have a table with several columns which I want to SELECT:

SELECT his_name , her_name, other_name FROM foo;

Bu, I instead I want to combine the results all into a single column.
As an example, I can do this with UNION ALL as

SELECT her_name AS name FROM foo
UNION ALL
SELECT his_name AS name FROM foo
UNION ALL
SELECT other_name AS name FROM foo

Is there more elegant way to do this operation?


texts:
In PostgreSQL (version 9.4) I am trying to construct a query to determine in various tranches how many times various numbers appear in the data set between ranges. When I query group-ing by "SettlementPointPrice" the count() function works correctly bucketing prices into individual tranches as I would expect. However, this creates hundreds of rows. I am looking for the ability (sub-query?) to aggregate the sum of count()'s in each bucket into one single row. What is the best way to manage this in SQL?

I am using a statement like this (full SQL below) for each bucket/tranche:

CASE WHEN (round(sum("DA-A"."SettlementPointPrice"),2)) BETWEEN 0 AND 10
     THEN count(*) ELSE 0 END AS "DA $0 - $10",

When I group by "SettlementPointPrice" (two prices in this example) to confirm the data counts the buckets correctly. As the table below shows.

Raw data from individual two days:

Row | "SettlementPointPrice" | 0-10 | 11-20 | 21-30
1   | 18                     | 0    | 1     | 0
2   | 22                     | 0    | 0     | 1

However, I am unable to get the aggregated summation by grouping them all together. I assume this is a sub-query?

I would like the result to be as such:

Row | 0-10 | 11-20 | 21-30
1   | 0    | 1     | 1

Full SQL code:

SELECT
  "DA-A"."SettlementPointPrice",
  CASE WHEN (round(sum("DA-A"."SettlementPointPrice"),2)) BETWEEN 0 AND 10
       THEN count(*) ELSE 0 END AS "DA $0 - $10",
  CASE WHEN (round(sum("DA-A"."SettlementPointPrice"),2)) BETWEEN 11 AND 20
       THEN COUNT(*) ELSE 0 END AS "DA $11 - $20",
  CASE WHEN (round(sum("DA-A"."SettlementPointPrice"),2)) BETWEEN 21 AND 30
       THEN COUNT(*) ELSE 0 END AS "DA $21 - $30"
FROM
  public.da "DA-A",
  public.rt_aggregate "RT-A"
WHERE
  "RT-A"."DeliveryDate" = "DA-A"."DeliveryDate" AND
  "RT-A"."SettlementPointName" = "DA-A"."SettlementPointName" AND
  "DA-A"."SettlementPointName" = 'John' AND
  "DA-A"."DeliveryDate" >= '2015-02-01' AND
  "DA-A"."DeliveryDate" <= '2015-02-20' AND
 ("RT-A"."DeliveryHour" = 14) and
  date_part('hour', "DA-A"."DeliveryHour") = "RT-A"."DeliveryHour"
GROUP BY
  "DA-A"."SettlementPointPrice",
  "DA-A"."SettlementPointName"


texts: Ideal values for wait time in SQL server

texts:
This is related to my own post here

The SQL server starts everyday at 8:30 AM and stops around 9:30 PM.( So wait times will reset everyday)
SQL server will be used by around 60 users simultaneously(used directly to modify data and also back-end database for application which we use in our office)
The server has 8 GB ram. Every hour log backup will be done for most of the databases.(Backup done by our own backup tool which automates SQL backup)

As described in this article I used below query.

select *
from sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP')
order by wait_time_ms desc;

And the result was as shown below(Only top 25 rows are shown)

Now after getting this result i am confused whether these values are really high or it is normal scenario.( I have also taken performance counter values related to SQL,but not able to troubleshoot using that, as most articles only describe which performance counters to be used,not about analysing it properly or it is hard part for me to find performance of my server)So what is the ideal value for these? Whether I have performance problem in my sql server? How to decide it?


texts: Does tempDB disk perf matter if you have plenty of unused memory?

texts:
We have a SQL Server 2012 Enterprise setup with a large amount of RAM (256 GB), and at this time fairly small databases (about 17GB). Right now we are experiencing performance issues due to various coding issues which will hopefully be resolved by some code reviews and optimization.  At the same time there is a movement to try to improve the performance by getting faster disk for the tempDB LUNs, like using a FusionIO PCIe flash card.

My concern is that given that SQL Server still has plenty of RAM to take advantage of, that we won't see any real performance improvement from adding faster disk.  I figure that most of the tempDB operations would take place in RAM, or take place on disk but end up cached in RAM (either on the host, HBA, or SAN).

Does anyone know if SQL Server 2012 will still end up waiting for IO blocking on tempDB disk operations when it has plenty of RAM to use, or is the tempDB disk activity we are seeing just there to provide persistence or durability?


texts: debugging PostgreSQL serialization failures

texts:
I am trying to migrate our PostgreSQL 9.4 database from transaction level READ COMMITTED to either REPEATABLE READ or SERIALIZABLE. In either case, I'm exposed to a new set of errors with the format:

(for both)
ERROR:  could not serialize access due to concurrent update
(just for SERIALIZABLE)
ERROR:  could not serialize access due to read/write dependencies among transactions

After reading the wiki page on SSI and the docs, I thoroughly understand the error conditions which could cause these errors, how to handle them, and even best practices to avoid them.

However, I see no way to determine the data dependency which caused them from any debug output that PostgreSQL could provide, or really any debug information at all. Is there any way to get this information from the database, either by executing additional queries at the time of the rollback or through some logging mechanism?

Having this information would allow me to make application-level changes (locking, different queries, etc.) that would eliminate some of the data races to avoid an excessive number of rollbacks.


texts: Oracle Standby Database Techniques for faster application of redo logs

texts:
I am quite new with the concept of standby databases in Oracle and I am not really a DBA, so help me out.

I have been reading a bit on the concept of Oracle standby databases and I am pretty much interested in logical databases. Currently, we have a potential significant slowdown in our standby database during our system's weekly DB maintenance.

During this maintenance, millions of records (usually the oldest records) will be deleted from several tables that have a number of records ranging from tens of millions to hundreds of millions. Usually, these records will be deleted using a single delete statement with a where clause referring to the oldest records.

From my understanding of how logical standby databases apply redo logs (do correct me If I am wrong), redo logs are process to create transactions which will then be applied to the standby database. So, when batch deleting millions of records in the primary database, what will happen in the standby database is something like a sequential deletion of these millions of records, which in turn is quite slow.

This leads me to my question. Can you share some thoughts/ideas on how to speed up the application of redo logs from the primary database to the standby database to avoid the issue I have raised above? The solution could be a different standby database configuration or a change in schema structures or a change in the way records are deleted.

Note that our tables should not be partitioned and are well-indexed. Standby database should be logical to enable querying for report generation.

Thanks for sharing your thoughts.


texts: How to Create Indexes Or Manage Performance For Complex Queries Before a Release?

texts:
Last night we had a major release that involved a massive data dump and some complex queries.   We semi-anticipated the slowness and were able to react quickly to creating some new indexes in SQL Server 2012.   However, the site was lagging for quite a long time post-release.

Is there a way to manage and prevent this ahead of time?   You can't create indexes before the data is on the production database, but there must be some way to prepare the database.

Part of the problem is not just indexes, but giving the database enough time to get statistics to make the indexes more efficient.

One thought I had was it might have something to do with sharding or replication, or perhaps some sort of db merge.


texts: SQL Server: Clustered index, sorting and pagination

texts:
In my application, several times I have to show results that are paginated and sorted by some field.
For example a simple user list sorted by last name. Because of that and because I also have logical deletion and it's a multi-tenancy application, I generally use a CLUSTERED INDEX like this:
CREATE CLUSTERED INDEX [idx] ON [Users]
(
     IsDeleted ASC,
     [AccountId] ASC,
     [LastName] ASC
)

That means that a query like paginated like SELECT TOP(20) * FROM Users WHERE IsDeleted = 0 AND AccountId = xxx is sorted by LastName.  I know it's not guaranteed to be sorted, but in practice it always is.
However, reading here Kimberly Tripp blog post on clustered indexes, she says it's a horrible idea to do it like so. And it's even worse because the IsDeleted (BIT) field doesn't let me to set the
However, if I change the CLUSTERED INDEX to just the unique ID, I would need to start using ORDER BY LastName, which in practice is very slow.
My table has a few million records (tens of millions at most) and it's used in general as follows:

Querying data. Most of the time.
Bulk updates/inserts, where only the data modified is under the subset IsDeleted = 0, AccountId = xxxx (only non-deleted data for a single account is updated in bulk).

Question:
What would be the recommended index (and how to sort) for these kind of tables?
Another example for these kind of tables would be a survey results table that has the following columns IsDeleted (BIT), AccountId (FK GUID), UserId (FK GUID), QuestionKey (NVARCHAR), AnswerValue (TEXT), where my CLUSTERED KEY would be probably (IsDeleted, AccountId, UserId, QuestionKey) and 99% of time I would query the table or update it in bulk by the 3 first fields
WHERE IsDeleted = 0
AND AccountId = xxx
AND UserId = yyy

or even the 4 fields: ... AND QuestionKey = 'country'
EDIT:
One of the primary reasons I did this is because the bulk updates and the queries are always limited to 1 or a small number of pages. Having an Identity column would require the queries and the updates to read/write in most pages.
EDIT 2:
Following Joe Obbish's example:
This query:
SELECT TOP (20) *
FROM Users2
WHERE IsDeleted = 0
AND AccountId = '46FC5693-7446-415A-8626-8937365460D1'
ORDER BY [LastName];

Having 1 cluster index in (IsDeleted, AccountId, LastName) results to this:

CPU time = 3 ms, elapsed time = 3 ms.
Table 'Users2'. Scan count 1, logical reads 5, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Having one cluster index in a new PK ID (NEWID()) column (that way the data is randomly sorted internally) and one non-clustered (IsDeleted, AccountId, LastName) results to this:

CPU time = 16 ms,  elapsed time = 18 ms.
Table 'Users2'. Scan count 1, logical reads 533, physical reads 5, read-ahead reads 1240, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Note the IO and time. It's way slower and requires more IO if the data is not stored together.  It might take more space, but the speed difference is notable. Am I wrong to do it like that?


texts: Subquery returned more than 1 value. This is not permitted + Trigger Error SQL Server

texts:
This trigger will update Is_Confidential_or_VIP column to 1 or 0 based on the text inserted or updated into ConfidentialRegistrationFlag and VIPType columns :

create trigger [dbo].[Is_Confidential_or_VIP]
on  [EDB].[dbo].[Patient_Info]
after update,insert
AS
BEGIN
    declare @type1 nvarchar(max)
    declare @type2 nvarchar(max)

    (select @type1 = ConfidentialRegistrationFlag from inserted)
    (select  @type2 = VIPType from inserted)

    declare @flag int

    if (@type2 like 'MDP-A' or @type2 like 'MDP-B' or @type1= 'confidential')
       set @flag= 1
    else
       set @flag= 0

    update EDB.dbo.Patient_Info
    set Is_Confidential_or_VIP = @flag
    where EPN = (select EPN from inserted)
 END

I have a problem when enabling this trigger cannot insert data and error msg:

  Subquery returned more than 1 value


texts:
We have a Microsoft Access database split into backend/frontend.  What I am looking for is to be able to automatically backup these files on a daily or weekly basis - what is the best way to go about this?  We don't want to have to worry about backups for the database, just know that it is occuring automatically on a schedule.

Thanks.


texts: Why isn't there a way to label a column as "keyword"

texts:
In a typical star schema, you end up with a bunch of columns that are an ID into some lookup table, where the lookup table is pretty much just this:

create table hostnames (
  id primary key,
  name varchar(200)
)

And when you select from your main table, you end up with a bunch of left joins, when you insert, you always have to lookup for the ID first, then insert if it's not there. It's a pain. 50% of my database related code is just for dealing with this.

It would be nice to have a way to indicate to the database that a given column has low number of distinct values, so that it can build its own internal lookup table and save space. The above would become:

create table sometablewithahostname (
    ...
    hostname varchar(200) keyword,
    ...
)

And there is no need to manage a the other tables.
Is there a RDBMS system that does that?


texts: List Roles on cassandra getting NoHostAvailable on one DataCentre

texts:
I have a 2 DC cluster, 'main' and 'bi' with a replication factor of 3 and a NetworkTopologyStrategy.

When I call LIST HOSTS; in cqlsh, my bi datacentre lists all the roles appropriately, but I get a NoHostAvailable: response from nodes in my main datacentre.

Any ideas?

Here is the keyspace description for the system_auth table --

CREATE KEYSPACE system_auth WITH replication = {'class': 'NetworkTopologyStrategy', 'bi': '3', 'main': '3'}  AND durable_writes = true;

All of my cassandra-rackdc.properties files correctly list their dc and rack.

As an aside, all selects work on all nodes.


texts: Should I add an auto-increment / IDENTITY field to a cross-reference table just for PK purposes?

texts:
I'm adding the following cross-reference table to my SQL Server-hosted DB:

company_id bigint not null (FK)
org_path nvarchar (2048) not null

The company_id field refers to the id field in another table (in which it's the primary key).

Given that there can also be multiple records with the same company_id, any primary key would have to use both fields. However, I'm unable to create a key using both fields because org_path is too long for SQL Server.

As for org_path, this is the only table in which it exists. There's every likelihood that queries to this table will be asking for either all entries or all org_path entries by company_id. Or to put it another way, it looks doubtful that this table will ever be queried by org_path. Furthermore, it's unlikely that org_path will be updated, and more likely inserted and - probably rarely - deleted.

I expect that the total number of rows will be in the low thousands.

Also, the reason it's nvarchar (2048) is because the value has to mimic that in a third-party DB. A typical example will be something like

\Translation Providers\[customer name]\[order name]\

and can contain diacritics.

So my question is this: would it be more efficient to add an auto-increment id field and use that in conjunction with company_id as the primary key, or would it add unnecessary overhead - and does the fact that company_id is the primary key in another table have any effect here?


texts: Why public is not mentioned as a fixed role in system database in SQL Server?

texts:
While running

select * from sys.server_principals

for public role, in the column is_fixed_role is shown 0

But the documentation states that it is a fixed server role.

Documentation Link

Although it is stated that public is a little bit different from other roles because we can assign permissions to it. Anyway it is mentioned as a fixed role.

Can anyone explain this dilemma?