texts:
Data balancing fails on Mongo cluster with 1 mongos and 3 mongod instances. All the data for the collection fs.chunks stays in the same shard with the following error.

Env: Ubuntu- AWS m3.large,

error from sh.status()

  6 : Failed with error 'could not acquire collection lock for test.fs.chunks to migrate chunk [{ : MinKey },{ : MaxKey }) :: caused by :: Lock for migrating chunk [{ : MinKey }, { : MaxKey }) in test.fs.chunks is taken.', from shard0001 to shard0000
  3826 : Failed with error 'moveChunk failed to engage TO-shard in the data transfer: can't accept new chunks because there are still 7 deletes from previous migration', from shard0000 to shard0001
  7655 : Failed with error 'moveChunk failed to engage TO-shard in the data transfer: can't accept new chunks because there are still 4 deletes from previous migration', from shard0001 to shard0000


texts:
In the "Effectively Troubleshooting Latency and Failover of Always On" by Sourabh Agarwal At PASS on Friday, November 3, 2017 3:30" about 30 minutes in he talks about how Synchronous mode commits are quicker, as the hardening is just about getting the t-logs to disk on the secondary.  The Redo is an async operation outside of the AlwaysOn commit process.

I may be missing something, but it seems like this "could" mean that the primary OLTP will not be impacted by long running (and blocking) queries on the secondary.  I envision that the logs just build in size on the secondary until the blocking report query completes, and then applies all the changes, that have occured on the primary and been hardened to disk on the secondary.

Related Will running a large query on a secondary database in an availability group affect transaction performance in the primary database? In this question the answwer by Kin suggests the answer does not address 2016+

Does SQL Server 2016 eliminate poor primary performance when reports take locks on seconday?

For this question: Assume multiple disks, with data (MDF) and logs (LDF) on seperate drives.


texts: News Database Design for a Desktop Application

texts:
I am making an application that will process a big chunk of information. This information was retrieved using some web crawlers and is about news, containing data such as News Title, URL, Publication Date, Category and Content. The crawled data is in XML format, and I will load that information into my application.

From there, 10 users will process every news and tag the category of the news manually by reading the title. I've used 9 main categories to be used, and for each news the users will decide from 0-5 how much this particular news belongs to each category.

Users will also search for news by title and decide if this news is discussing the same event as another news, or if this news is similar to another news (like a news about a football game at 3 PM and a news about a football game at 5 PM).

I have no problems with making the application itself, I just need some help with the part of how to design one/many tables that can link news that talk about similar events or about the same event, since there can be many many news that can talk about the same event.

So far I've done something like this:

Table News

ID
Title
URL
PublicationDate
NewsContent

Table Category

NewsID
User_ID
Economy
Politics
Present_Day
Sport
Technology
Showbiz
Culture
Region
World

Table User

ID
FirstName
LastName

Each category field in the Category table holds a tinyint data type (I'm using SQL Server) and I've restricted the values the user can input via a check constraint so that it allows values from 0 - 5. I don't know if this is the right approach so far for the database design, and I need to add the table/s that allow to add information about similar news or news that talk about the same event, such as a table Similar_News containing some fields like News_ID, SimilarNews1_ID, SimilarNews2_ID and so on, and must contain the information about which user 'said this', but this sounds like a flawed design to me.

Any help is appreciated, thank you.


texts:
I have a Issue in Oracle I have made two database one for Demo and one for Dev  for same mount point. When I connect with Demo this can be connected but when I try to connect with DEV its not able connect. I have given to add the listner details as given below  but when I am tnsping with DEV its giving TNS 12541 error. I try to find out but not able to connect.

[oracle@CVM-1429 ~]$ sqlplus sys/sys as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Oct 8 18:11:55 2016

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

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

SQL> select name from v$database;

NAME
---------
CSMEC

SQL>

Some more information:

[oracle@CVM-1429 ~]$ ps -ef|grep pmon
oracle    1286     1  0 Oct07 ?        00:00:10 ora_pmon_CSDEV
oracle   17032     1  0 Sep27 ?        00:01:40 ora_pmon_CSMEC
oracle   30817 30328  0 18:12 pts/2    00:00:00 grep pmon
[oracle@CVM-1429 ~]$
[oracle@CVM-1429 admin]$ pwd
/u03/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin

The content of the listener.ora:

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=10.56.184.32)(PORT=1525))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))
    ))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=CSMEC)
      (ORACLE_HOME=/u03/oracle/app/oracle/product/12.1.0/dbhome_1/)
      (SID_NAME=CSMEC))
   ))

LISTENER1=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=10.56.184.32)(PORT=1530))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))
    ))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=CSMEC)
      (ORACLE_HOME=/u03/ORCDEV/oracle/app/oracle/product/12.1.0/dbhome_1/)
      (SID_NAME=CSDEV))
   ))

The tnsnames.ora:

# tnsnames.ora Network Configuration File: /u03/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

CSMEC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.56.184.32)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CSMEC)
    )
  )

CSDEV =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.56.184.32)(PORT = 1530))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CSDEV)
    )
  )

The result of the tnsping:

[oracle@CVM-1429 admin]$ tnsping CSDEV

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 08-OCT-2016 18:20:18

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.56.184.32)(PORT = 1530)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CSDEV)))
TNS-12541: TNS:no listener


texts: Effiiency of multiple OR predicates versus a single range

texts:
Say I have a SQL statement:

SELECT *
 FROM MyTable
WHERE KeyColumn1 = 1 OR
      KeyColumn1 = 2 OR
      KeyColumn1 =3 OR
      ...
      KeyColumn1 = 10

And another:

SELECT *
 FROM MyTable
WHERE KeyColumn >= 1 AND
      KeyColumn <= 10

Are they actually different and which one would be more efficient?


texts: Powershell: Does System.Data.SqlClient.SqlDataAdapter usange in powershell need some ADO.Net installation?

texts:
In PowerShell, goal is to query some data from Sql Server and put that into a preformatted Excel file. Towards that, the following PowerShell script snippet, throws an error saying "assemblies are missing".

From what I could gather online, SqlClient.SqlDataAdapter is part of ADO.Net, which does not require including any assemblies (as it is always present in the OS .Net framework installation). Is this correct? How do I rectify this?

...
... other connection code
...
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
$SqlCmd.Connection = $SqlConnection;
$SqlCmd.CommandText = $ActiveTransactions_Count_Query;

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
$SqlAdapter.SelectCommand = $SqlCmd;

$DataSet_ActiveTransaction = New-Object System.Data.DataSet;
$SqlAdapter.Fill($DataSet_ActiveTransaction);
$DataSetTable = $DataSet_ActiveTransaction.Tables[0];
$DataSetTable

Error:

New-Object : Cannot find type [Sql.Data.SqlClient.SqlDataAdapter]: make sure
the assembly containing this type is loaded.
At M:\Build.ps1:21 char:25 + $SqlAdapter = New-Object <<<<
Sql.Data.SqlClient.SqlDataAdapter;
    + CategoryInfo          : InvalidType: (:) [New-Object],
    PSArgumentException
    + FullyQualifiedErrorId :     TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand


texts: connect-mongo latest version return some error in my project

texts:
I have updated my connect-mongo npm to the latest version 2.0.1 in node v8 its returning some error, if i comment the below code my projects works pretty well if not returning the below error

here is a bit of code

var session = require('express-session');
var MongoStore = require('connect-mongo')(session);
    app.use(session({
        resave: true,
        saveUninitialized: true,
        secret: '6256346324',
        store: new MongoStore({
            db: global.db
        })
    }));

Error

.then(() => this.changeState('connected'))
                     ^

TypeError: this.setCollection(...).setAutoRemoveAsync(...).then is not a function
    at MongoStore.handleNewConnectionAsync

modules used in package.json(i have listed only few)

connect-mongo": "^2.0.1",
    "express": "^4.16.2",
    "express-session": "^1.15.6",
    "mongodb": "^2.2.35"

so how can i solve this error?


texts: Can you update statistics on a sys change tracking table

texts:
I'm tracking down some poor query times doing the typical query to get changed records:

select StoreItemId from changetable(changes [dbo].[StoreItem], @clientChangeTrackingVs) as ChangedRow where [StoreId] = @storeId;

The execution plan shows a clustered index scan instead of a clustered index seek on the sys change tracking table exactly as described in this article:

https://port1433.com/2015/01/29/change_tracking_table_statistics/

In that article it suggests:

  Could it really by so simple as:

  UPDATE STATISTICS sys.change_tracking_315968302 Sure enough, that did
  the trick. The plan instantly got happier.

My login is running with full control granted, but that still will not let me run that command agaonst a sys table:

UPDATE STATISTICS sys.change_tracking_1198627313

Msg 1088, Level 16, State 12, Line 1
Cannot find the object "sys.change_tracking_1198627313" because it does not exist or you do not have permissions.

Is it possible to run this command, or otherwise force a seek instead of a scan?  Am I barking up the wrong tree?

Edit1:  Adding the scan image

Edit 2:  Just to confirm that the scan vs seek was causing the slowdown, I applied a WITH (FORCESEEK).  This took me a while to figure out, because changetable is not a table so the syntax was invalid.  I managed to use a CTE and then make the syntax work:

WITH ChangedRowCTE (StoreId, StoreItemId)
AS
(
    select StoreId, StoreItemId from changetable(changes [dbo].[StoreItem], @clientChangeTrackingVs) as ChangedRow
)
select
   StoreItemId
from
   ChangedRowCTE WITH (FORCESEEK)
where [StoreId] = storeId;

Execution planned shows the seek and execution time was 4 times faster.

Edit 3:

This is what I meant by full control:

The following describes the general conventions that are followed for naming permissions:

  CONTROL

  Confers ownership-like capabilities on the grantee. The grantee
  effectively has all defined permissions on the securable. A principal
  that has been granted CONTROL can also grant permissions on the
  securable. Because the SQL Server security model is hierarchical,
  CONTROL at a particular scope implicitly includes CONTROL on all the
  securables under that scope. For example, CONTROL on a database
  implies all permissions on the database, all permissions on all
  assemblies in the database, all permissions on all schemas in the
  database, and all permissions on objects within all schemas within the
  database.

That being said, I asked the sys admin to run this command and he stated he was able to run the command.  So I guess the answer to my own question is "yes" you can, but you have to be the admin.


texts: Best way to determine if a MySQL InnoDB table is in its own file, not in system tablespace

texts:
I have a MySQL development server that uses InnoDB tables in some of the databases. It was originally setup with the defaults so all data went into the system tablespace (e.g the main ibdata1 file). Along the way, I enabled innodb_file_per_table so new database tables get into their own files.

I now want to reclaim disk space from dropping some development databases. I know I have to mysqldump and re-import those databases that are in the system tablespace. What is the best way to determine which ones to focus on?

I don't see anything obvious in the information_schema database. I could manually poke around the server with an SSH shell, but I was looking for a more automated or elegant way to do that.

In some older databases, I only see .frm files in the database directory, in newer ones I see .frm and .ibd files. That seems to be the key to know where the data is ...

Sanity Check: Is it even possible to mysqldump half the databases to reclaim space, or do you really have to do every database? There are 54 databases inside this server that use InnoDB tables.

Note: Using MySQL v5.1 on UNIX.


texts: "invalid transaction termination" in Postgres when trying to update a value using a function

texts:
I'm new to postgres. I defined a function like this:
CREATE OR REPLACE FUNCTION increment_count()
  RETURNS TRIGGER LANGUAGE plpgsql
  AS $$
   BEGIN
     UPDATE posts SET count=count+1 WHERE pid=NEW.pid;
   COMMIT;
   END;
  $$;

This gives me error invalid transaction termination


texts: My uncle gave me access to his server so I could play around with mysql and php. He installed php and mysql on it created a user as well folder for me to play around in. I was wondering how to access mysql from my laptop. I am using filezilla to connect to the server, through ssh. Do I need a client version that connects to the server or anything like that?


texts: MySQL error 1593 on replication between 5.5 on WIndows and 5.6 on CentOS 7

texts:
I'm setting up replication between the live server (running Windows 2008 R2 and MySQL 5.5.19) and a dedicated replication server (running CentOS 7 and MySQL 5.6.30). The CentOS box does have MySQL not MariaDB.

In the master my.ini, I've set:

server-id=131025185082

In the slave my.cnf, I've set:

server-id=192168001182

The server is generating a mysql-bin and I've configured the slave to connect. But; I'm getting a 1593 error:

Fatal error: The slave I/O thread stops because master and slave have  equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). The log position given is the start point from SHOW MASTER STATUS on the master.

When I do:

show variables like 'server_id';

on both machines, they both come back with:

+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| server_id     | 4294967295 |
+---------------+------------+

Can anyone suggest what's gone wrong here?

Thanks.


texts: create column name from other table rows mySQL

texts:
i have following code, for this i have two tables, one table contain questions and another table contain answers.
answers table something like following : (ans_question_1 to ans_question_95)

| answersId| ans_question_1| ans_question_2| student_id
|-----------|------------  |---------      |
| 1         | Male         | 23            |11
| 2         | Female       | 21            |12

Questions table (questions 1 to 95)

| question_id | Question|
|-----------  |--------
| 1           | Gender
| 2           | Area code

Want table something like this (need stored procedure with parameter student_id)
i also need this table creation dynamically, because my number of questions change regularly.

| student_id  | Gender    | Area Code
|-----------  |--------  -|--------
| 11          | Male      | 23
| 12          | Female    | 21


texts: I have changed sql db name from database.mdf to database_log.mdf and can't get access to it!

texts:
I have encountered with a problem.
I was serfing the google how to change the mdf file location and found this post:
How do I move SQL Server database files?.
But as it usually happens I did an error. I have used this code:

USE master;
ALTER DATABASE foo
MODIFY FILE (name='DB_Data1', filename='X:\NewDBFile\DB_Data1.mdf');

and modified DB_Data1.mdf to DB_Data1_log.mdf.
And now I can't attach this database to sql studio or open it in Visual Studio.

I need help please!


texts: how to optimize mysql query/ structure/ approach for large datasets?

texts:
Mysql Database structure:

I have two tables:

Users Table with autoincrementing id contains about 20K rows and growing:

id userName

1 John
2 Doe
3 Alice

Contents Table with autoincrementing id  contains about 10K rows and growing:

id Content

1 Content1
2 Content2
3 Content3

UserContent Table for association with both userId and contentId as foreign Keys and autoincrementing id:

id userId contentId
1  1      1
2  1      2
3  1      3
4  2      1
4  2      2
...

Problem Statement:

The problem is that i have a process which runs everyday and runs a query which picks up the next content item for every user that he or she has not received previously, and adds that unique combination of userId and contentId in the userContents table.

The Contents should be added to each user's account in the same order.

For example, the process runs on day 1 and adds 20k rows (one row for each user) in the table. on Day 2, another 20k rows are added and so on. As you can imagine, this has lead to alot of data in the userContent table which is slowing down the query. Currently, there are about 4.4 Million records on the userContent table and it is growing day by day.

users cannot modify/clear the content that they have received. the records in userContent table will remain the same always.

Current Sql Query:

the current query that i am using is this:

select u.id as userId, content.id as contentId
 from users u, contents c
where
u.id not in
(select distinct uc.userId from userContent uc where uc.userId = u.id and uc.contentId = c.id)
and c.id not in
(select distinct uc.contentId from userContent uc where uc.userId = u.id and uc.contentId = c.id)

What this query does

this query will pick out a unique content for each and every user in the database which that user has previously not received.

Lets dry run this:

Day 1: there are three users in the system: User A, User B and User C. Each of these users will get content A.

Day 2: Each of the three users A, B and C will receive Content B.

Day 3: A new user D signs up. the first three users A, B and C will receive content C whereas User D will receive content A.

Major problems that i have identified in this query:

since there is nothing common between users and contents, i have to make a cross join which results in a total dataset of 200,000,000 rows! (10k content rows and 20k users rows).
For each of these 200,000,000 rows, the query checks whether the combination of userId and contentId exists in the userContent table or not by making two separate sub queries.

Help needed in:

Currently, this query takes well over a minute to execute. Is there any way i can speed up this query by changing the query or the database structure or even the approach itself?

any help in this regard will be highly appreciated! Also, let me know if further clarifications are needed.

thanks!


texts: Fragmented clustered primary key (sequential GUID) index after processing - SQL Server

texts:
I'm in the process of evaluating a schema change on the primary keys of several of our biggest tables in our software.
Till now we used varchar(36) for all of our PKs and FKs and Hibernate generated the random GUIDs in insert operations.
Moreover SQL server just makes all those columns clustered indexes also, so I'm worried now (after researching about) for the index fragmentation that all those inserts (and we have a lot of inserts and deletes and updates) will cause to the indexes.
A query made on the tables showed that almost all indexes are heavily fragmented.

So I decided to change the column type for several of our biggest tables (between 3 million and 1 million, so not that big) from varchar(36) to unique identifier with the DEFAULT option newsequentialid().

I did a test yesterday on a much smaller db: after changing those PKs and after a big processing (about 20k inserts and I think the same amount of deletes) it shows an index fragmentation for the clustered keys (id) from 30% to 40%.

How is this possible?

Of course I rebuilt the indexes before the processing.

These are the fragmentation data of ax example table (46528 rows) -> 42% fragmentation:

Schema  Table   Index   avg_fragmentation_in_percent    page_count  fragment_count
dbo e2_per_out_presenze PK_e2_per_out_presenze_id   42,91   3714    1704
dbo e2_per_out_presenze FK_e2_per_out_presenze_2    1,78    448       36
dbo e2_per_out_presenze FK_e2_per_out_presenze_3    0       194       11
dbo e2_per_out_presenze FK_e2_per_out_presenze_4    0       444       12
dbo e2_per_out_presenze FK_e2_per_out_presenze_6    0       161       16
dbo e2_per_out_presenze FK_e2_per_out_presenze_7    0       166       14
dbo e2_per_out_presenze Index_8                     0       466       28

Now I'm worried that this schema change won't help that much.

EDIT: I think it's important to note that the fill factor on the tables is set to 80 and that after the insert it's very possible that the row gets also updated.

For the sake of the test I also include the fragmentation data of the table after the same test but without the sequential GUID (it shows anyway a big improvement in fragmentation, but I wonder how will it perform on bigger tables)

Schema  Table               Index                       avg_fragmentation_in_percent    page_count  fragment_count
dbo     e2_per_out_presenze FK_e2_per_out_presenze_7    95,03676471                     544         524
dbo     e2_per_out_presenze FK_e2_per_out_presenze_3    94,43413729                     539         515
dbo     e2_per_out_presenze FK_e2_per_out_presenze_6    93,56060606                     528         501
dbo     e2_per_out_presenze FK_e2_per_out_presenze_2    92,22343921                     913         854
dbo     e2_per_out_presenze PK_e2_per_out_presenze_id   86,9864487                      4649        4102
dbo     e2_per_out_presenze FK_e2_per_out_presenze_4    68,6827957                      744         522
dbo     e2_per_out_presenze Index_8                     0                               501         21


texts: how I can create check sum to validate no changes at int string pairs on table

texts:
Business case is.
1)Users print document on paper
2)users carry printed paper to Boss, Boss sing on paper
3)after that users going to administrator to digitally sign that paper.

My task is to help administrator to on easy way be sure that there is no changes between paper document and digital document.

Document is written in three sql tables.
On 1st table integrity is string+date
On 2nd table integrity is combination int+float
On 3rd table integrity is int+string.

There is others columns in these tables which can be updated freely after Boss sing document without needs to Boss sing it again.

My idea is to from these three table / combination make some kind of SHA or CheckSum.
Which is going to be same while there is no changes on document.
I do not want to my checksum be infinite or too long so administrator can easily check is
there violation on my custom integrity.

I start thinking that I can for int - float make sum( int * folat)
For stings for each letter I can use ASCII number and do sum(int * (ascii(S)+ascii(T)+ascii(I)+ascii(N)+ascii(G))).
For date - int same make int * (date to int)
After that I can totalize (sum) these three numbers and convert to HEX.
But perhaps my number is going to be out of range or too large for comparing by human eye.

How I can shrink extra large numbers to be easily readable and comparable by humans ?
Is there another way to I ensure that there was no changes on my protected columns in one period of time ?

I am using MSSQL2005/8 server.
My reporting engine is most of rime SqlReportingServices, but I prefer to do this loginc
on database level not reporting level.

I do not want to lock editing of document because sometimes Boss push users to go back and correct document before signing.
I also can't push boss to take mouse in hand.


texts:
My task is to reconfigure a main server that houses the company's applications and data.  The server is capable of handling everything.  I wanted to find out if anyone has a suggestion on the best hard drive configuration.

They have 2 300GB hard drives and 8 600GB hard drives.  Currently they are configured as the following:

2 300GB (OS, RAID 0+1)
2 600GB (Hyper-V Virtual Disks, RAID 0+1)
2 600GB (QuickBooks & Personal User Data, RAID 0+1)
2 600GB (Files Used/Stored, RAID 0+1)
2 600GB (Files Used/Stored & Exchange Database, RAID 0+1)

This is a Windows 2012 R2 member server contains the following applications:

Hyper-V with RemoteFX,
Exchange 2013,
Software IP-PBX,
QuickBooks Data Files,
SQL Server 2014,
User Files, Used/Stored files.

Any suggestions on best performance and best configuration would be greatly appreciated.  Also, please suggest what I should to do to back up their data so that I can re-do their drives without losing any of their data.  SQL Server is installed but the databases are empty, they haven't used that yet, so I could just uninstall.


texts:
I want to create a basic function which takes an input and throws a table with multiple columns and rows as an output . I have written the following query for the same :

create or replace function proc_name_1 (store text)
returns table (question text , resp numeric , gendr text , resp_count integer)
as $$
begin
return query
select question_type , response, gender, sum(response_count)
from fashtagcompute.response_count
where store_id = store
group by question_type , response, gender ;
end ;
$$
language plpgsql ;

CREATE FUNCTION

The function is succesfully created. However if I try and execute  it , I get the following error

select * from proc_name_1('ab1f47ff-5c96-46fb-b975-81bf92c01b63')

ERROR: structure of query does not match function result type
DETAIL: Returned type character varying(80) does not match expected type text in column 1.
CONTEXT: PL/pgSQL function proc_name_1(text) line 3 at RETURN QUERY

Please help.
Thanks


texts: Why are these two queries having such different executions?

texts:
I'm trying to get all users that haven't been sent a message in a given day. I have two tables, one with the messages that have a reporterid indexed column, and a reporter table.

Let me walk you through it:

This inner query returns reporters that have received messages in a given day. It takes tenths of a second on my local dev machine:

select  m.reporterid
    from  nubamessage m
    where  m.messagetypeenum =7
      and  m.createdOn>='2016-06-18 00:00:00'
      and  m.createdOn<='2016-06-18 09:30:00';

There is no difference in the execution time of the above query if I do select(distinct) or not, and it returns the same rows.

But if I used that in an outer join query, it basically never completes:

select  *
    from  reporter r
    left outer join
    (
        SELECT  m.reporterid
            from  nubamessage m
            where  m.messagetypeenum =7
              and  m.createdOn>='2016-06-18 00:00:00'
              and  m.createdOn<='2016-06-18 09:30:00'
    ) as mm  ON mm.reporterid=r.id
    where  r.enabled=1 and m.reporterid is null;

However, if I change the inner query to distinct as the below, it takes a fraction of a second:

select  *
    from  reporter r
    left outer join
    (
        SELECT  distinct(m.reporterid)   -- the only difference
            from  nubamessage m
            where  m.messagetypeenum =7
              and  m.createdOn>='2016-06-18 00:00:00'
              and  m.createdOn<='2016-06-18 09:30:00'
    ) as mm  ON mm.reporterid=r.id
    where  r.enabled=1 and m.reporterid is null;

The execution plan for the non-completing query:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  r   NULL    ALL NULL    NULL    NULL    NULL    11538   50.00   "Using where"
1   SIMPLE  m   NULL    ALL NULL    NULL    NULL    NULL    968388  100.00  "Using where; Using join buffer (Block Nested Loop)"

The execution plan for the query where I just add "distinct", it adds some auto-key:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   PRIMARY r   NULL    ALL NULL    NULL    NULL    NULL    11538   50.00   "Using where"
1   PRIMARY <derived2>  NULL    ref <auto_key0> <auto_key0> 5   r.id    10  100.00  "Using index"
2   DERIVED m   NULL    ALL NULL    NULL    NULL    NULL    968388  1.11    "Using where; Using temporary"

If someone could find it in their heart to explain to me why 1. the first query never completes/is so slow and 2. why the distinct makes it run fast, even though the inner query returns the exact same rows in both cases, I'd be most grateful.

Thanks

Edit: Notice that I write "all users that haven't been sent a message. This is why I have the left join - later in the query I have "where m.reporterid is null", in order to only get out the reporters that don't already have a message in the messagetable. I have edited the questions above to reflect that.

I could have used a "not in" instead but from what I've read and in my tests it was a bit slower than left join.


texts: How do I replace a substring within a string, but only if substring is matched within a regex group?

texts:
I need to extract information from a malformed json-formatted string. Due to an error, all quotes have been stripped, so instead of:
{"receipt_type":"Production","adam_id":1233,"app_item_id":1233,"more":[{"h":234}]}

I have:
 {receipt_type:Production,adam_id:1233,app_item_id:1233,more:[{h:234}]}

Using
replace(replace(replace(replace(replace(replace("transactionReceipt", ':', '":"'),',','","'),'{','{"'),'}','"}'),':"[{',':[{'), ']"}', ']}')

I can get mostly fine json, HOWEVER this incorrectly turns "receipt_creation_date":"2021-01-24 03:11:53 Etc/GMT" into "receipt_creation_date":"2021-01-24 03":"11":"53 Etc/GMT"
Using
REGEXP_REPLACE(replace(replace(replace(replace(replace(replace("transactionReceipt", ':', '":"'),',','","'),'{','{"'),'}','"}'),':"[{',':[{'), ']"}', ']}'),'\d\d\"\:\"\d\d\"\:\"\d\d','','g')

I can match the 03":"11":"53 and remove it totally, yielding correct JSON.
But I want to retain the time, meaning that I swap ":" with : but ONLY if ":" is within the regex pattern of '\d\d\"\:\"\d\d\"\:\"\d\d'?
Thank you!
edit: a complete malformed record (it's actually an IAP transaction receipt from Apple) looks like
{receipt_type:Production,adam_id:123456789,app_item_id:123456789,bundle_id:example.app.yo,application_version:101,download_id:123456789,version_external_identifier:123456789,receipt_creation_date:2020-03-19 16:18:27 Etc/GMT,receipt_creation_date_ms:1584634707000,receipt_creation_date_pst:2020-03-19 09:18:27 America/Los_Angeles,request_date:2020-03-19 16:18:28 Etc/GMT,request_date_ms:1584634708876,request_date_pst:2020-03-19 09:18:28 America/Los_Angeles,original_purchase_date:2020-02-28 17:55:33 Etc/GMT,original_purchase_date_ms:1582912533000,original_purchase_date_pst:2020-02-28 09:55:33 America/Los_Angeles,original_application_version:101,in_app:[{quantity:1,product_id:com.eample.iap.id,transaction_id:123456789,original_transaction_id:123456789,purchase_date:2020-03-19 16:18:27 Etc/GMT,purchase_date_ms:1584634707000,purchase_date_pst:2020-03-19 09:18:27 America/Los_Angeles,original_purchase_date:2020-03-19 16:18:27 Etc/GMT,original_purchase_date_ms:1584634707000,original_purchase_date_pst:2020-03-19 09:18:27 America/Los_Angeles,is_trial_period:false}]}


texts: Error configuring SQL Server as Distributor

texts:
I'm trying to configure replication on a new install (moving to SQL 2012) and I keep getting the same error even after messing with the service name, folder permissions, etc...

Using the Configure Distribution Wizard, I'm getting the following error:

SQL Server could not configure 'SQL01' as a Distributor
Additional information:
An exception occured while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Connectioninfo)

Destination path E:\MSSQL\Data is not valid. Unable to list directory contents. Specify a valid destination path.
Changed database context to 'master'. (Microsoft SQL Server, Error: 14430)

I logged in as the local Administrator. I connected to SQL using the local Administrator account. I tried running SQL Server/Agent as NETWORK SERVICE as well as making a local user and using the local user. The local user has full permissions to E:\MSSQL\Data folder which is where all the databases are kept.

Windows Server 2008 R2
SQL Server 2012 SP2

Thank you,
Adrian


texts: Generate Database Creation Script Within A Stored Procedure

texts:
I'm looking to automate a wipe of our test environment to bring it up to be in-line with production at the end of every sprint.

As it stands this is currently being completed by the test enviroment grabbing the full backup and doing a restore with move&replace however this is using up most of the space we have.

The intention is to create the environment from scratch and populate only the needed tables.

I can use the 'Right Click > Tasks > Generate Scripts...' to create the framework of the database.

Is there a way to create that script from within a stored procedure so that can be used to re-create the database

Also I'm working on this bit as it stands but obviously that script will just create the database at its current size so all of the File Sizes need to be modified once the script has been generated

Cheers for any help


texts: Executing shell commands from SSDT Post Deploy scripts to generate SQL

texts:
tl;dr: I'm looking for the equivilant of !! sqlcmd -S $(Server) -o foo.sql -d $database -Q "EXEC sp_generate_merge 'table_name'" in a SSDT post deploy script, but !! doesn't seem to be supported.

I have a SSDT database project with tables of "static data" that I populate via MERGE statements in the post deploy script. Many of these tables are reverse engineered from   I found this article about using :r foo.sqlto have multiple files for post deploy.

I'd like to take this another step. I'd like generate the the merge statement by calling sp_generate_merge from a reference database on a remote server, and then executing the resulting merge statement on my database. I could use !! SHELL_CMD in sqlcmd or SSMS with SQLCMD mode enables, but that does not work in SSDT. What are my options?


texts: Error: "The certificate cannot be dropped because one or more entities are either signed or encrypted using it."

texts:
I have a certificate I would like to remove from the database.
If I issue the command
DROP CERTIFICATE <FooCert>

I get the error
The certificate cannot be dropped because one or more entities are either signed or encrypted using it

According to Jason Strate, I should be able to find out what's signed by the certificate.
The following query returns 0 rows:
SELECT OBJECT_SCHEMA_NAME(co.major_id) + '.' + OBJECT_NAME(co.major_id)
FROM sys.certificates c
INNER JOIN sys.crypt_properties co ON c.thumbprint = co.thumbprint
WHERE co.crypt_type_desc = 'SIGNATURE BY CERTIFICATE'
AND c.name = 'FooCert'

I've also tried decoupling the entities per this SO question.
https://stackoverflow.com/questions/52460/how-do-i-find-and-decouple-entities-from-a-certificate-when-upgrading-ms-sqlserv
How can I remove the dependencies on this cert so I can remove it?


texts: SSHtunnelForwarder to Linux Box, then connect to Postgres RDS (AWS)

texts:
I'm trying to write a python script to update DDL in a Aurora RDS (postgres) on AWS.  In Order to do this, I need to SSHTunnel to a linux box that sits at the edge of the VPC, then do another jump to the RDS server.  When I am connecting to the RDS via psql client on the Linux Box, I'm able to connect fine.  But, when trying to sshtunnel via SSHTunnelForwarder in python, I get an error saying: sshtunnel.BaseSSHTunnelForwarderError: Could not establish session to SSH gateway

Here is my code:

with SSHTunnelForwarder(
    (LinuxBoxHost,22),
    ssh_username='ubuntu',
    ssh_pkey="/path/to/.pem file",
    remote_bind_address=(RDSHostName,5432)) as server:

    server.start()
    print(server.local_bind_port)

I can't even test my connection to the RDS DB because I can't event hit the Linux Box via SSH.

I'm not sure what I'm doing wrong here. I've looked at various examples online, and it seems like the parameters I'm inputting seem accurate.

I'm wondering if I am just missing something conceptual here? Is this even possible? Perhaps the way I'm imagining our network infrastructure is not correct, but I've talked to our IT person, and it seems like I have the right idea. Any advice would be appreciated, thank you in advance!


texts: Generic script for stored procedure rollback

texts:
We're trying to switch to feature-driven development in my organization. We're planning on using a new branch on our source control for each feature and merge when development is complete. I think it will work great for application code since the rollback is to remove the merge check-in. For our db schema work might be difficult though.

Is there a way of retrieve the current version of a stored procedure, by code, at the time a new version is being deployed in order to create a rollback script automatically?


texts: How to estimate needed undo retention for a operation?

texts:
Recently I needed to create a new table with CTAS. But after 4 hours of work I've received the erro snapshot too old
How can I estimate the needed undo retention for a operation?


texts: What is the instance name of the box I'm logged into?

texts:
I'm at the linux prompt of a computer running Oracle 11.

Without connecting to the database, how can I determine what is the instance name of the Oracle server running on this box?


texts: TRC: Select an ID if it appears on all measurements

texts:
I have these two "tables":

Station (station_id, city)            // Stations details
Rainfall (station_id, date, amount)   // Rainfall measurements

With Tuple Relational Calculus (TRC), I need to get the IDs of all stations that have related measurements on every date in which any measurement was taken.

So with the following tables I'm supposed to select 5 because this station has measurements on every date that has a measurement.

       Station                             Rainfall
       -------                             --------
--station_id-----city--      --station_id-----date-----amount--
      5           LA               5          01/01      4
      7           NY               7          02/02      8
                                   5          02/02      3

I've tried this but I'm not quite sure if this would work if there are no measurements at all (in this case I'm supposed to select all station IDs):

{ t | ∃s ∈ Station (t[station_id] = s[station_id] ∧
    ∀r ∈ Rainfall (∃q ∈ Rainfall (r[date] = q[date] ∧ s[station_id] = q[station_id])) ) }

I need your help in understanding if what I did is correct.


texts: How to enforce a column value on duplicate values on postgresql using constraint?

texts:
I have the following table:
mytable
---------
id Serial PK
action Integer
model VARCHAR
model_id integer
active BOOL

But due to my logic there cannot be duplicate values having the same values for model,model_id,action when active is true.
Therefore, I place a unique constraint:
ALTER TABLE mytable ADD CONSTRAINT constraint_name UNIQUE (model,model_id,action,active);

But that does not avoid me having the unique constraint only when active=true. Also, my table is pre-populated with duplicate values therefore I want to set active=false on dduplicate ones and keep only one record with value=true do you know any good idea on how I can do this beyond just writing an update statement before applying the constraint?


texts:
I have This table :

    SET NAMES utf8;
SET time_zone = '+00:00';

CREATE TABLE `app_log` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `serial` int(11) NOT NULL,
  `dataEntryIP` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `dataEntryUA` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `domain` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `timeStamp` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `serial` (`serial`)
) ENGINE=InnoDB AUTO_INCREMENT=4328 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Sample of the possible data would be like :

INSERT INTO `app_log` (`ID`, `serial`, `dataEntryIP`, `dataEntryUA`, `domain`, `timeStamp`) VALUES
(6,33605,'2.5.29.18','Google Chrome','www.domain1.com','2017-08-18 18:44:23'),
(7,33606,'8.102.167.213','Google Chrome','domain1.com','2017-08-18 18:45:48'),
(8,33607,'8.24.10.14','Google Chrome','www.domain0.com','2017-08-18 18:58:13'),
(9,33608,'8.24.10.14','Google Chrome','domain0.com','2017-08-18 18:58:13'),
(10,33609,'8.24.10.14','Google Chrome','domain1.com','2017-08-18 19:06:44'),
(11,33610,'8.24.10.14','Google Chrome','www.domain0.com','2017-08-18 19:06:44'),
(12,33611,'7.10.14.11','Google Chrome','domain2.com','2017-08-18 19:12:48'),
(13,33612,'7.10.14.11','Google Chrome','www.domain2.com','2017-08-18 19:12:48'),
(14,33613,'9.45.21.18','Google Chrome','domain3.com','2017-08-18 19:18:10');

Now I am in need of selecting any IP which have used more than one domain, for this sample, it is : 8.24.10.14 because it have used both domains : domain0 and domain1

How would I do this ??

Thank you very much


texts: No Data for RING_BUFFER_SCHEDULER_MONITOR

texts:
I am attempting to add a query for monitor recent CPU usage for our SQL servers into an SSRS dashboard. I have encountered numerous examples online recommending querying the sys.dm_os_ring_buffers DMV, where the relevant CPU information can be extracted from the XML records where
ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
However, none of our instances return any rows for this ring_buffer_type value. There is plenty of data within the DMV for other ring_buffer_type values including RING_BUFFER_SCHEDULER, but none for RING_BUFFER_SCHEDULER_MONITOR.
To clarify, if I run hte following query :
SELECT *FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'

then it always comes up empty.
I am wondering if something needs to be enabled at the server or DB level to allow the recording of this data but have been unable to locate this information. Any help would be appreciated. All of our instances are on SQL Server 2016


texts:
I have something around 200 GB worth data stored in a mongo cluster. The physical memory on one of the instances which runs mongo is 8GB. Nothing else of any consequence runs on this instance. As near as I can understand based on Mongo's docs (like this one: http://www.mongodb.org/display/DOCS/Checking+Server+Memory+Usage) this means that the mongod process should be using about 100% of the available physical memory. But if you look at the following output from the top command you'll see that the mongod instance is only using 2GB of resident memory and theres a full 2GB of free physical memory available which isn't being used at all.

Can someone explain this behavior to me? Why is there 2GB of free memory?

top output:

top - 23:19:43 up 89 days, 20:05,  2 users,  load average: 0.41, 0.55, 0.59
Tasks: 101 total,   1 running, 100 sleeping,   0 stopped,   0 zombie
Cpu(s):  2.0%us,  1.3%sy,  0.0%ni, 93.9%id,  2.6%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:   8163664k total,  6131764k used,  2031900k free,    54976k buffers
Swap: 16771848k total,    10604k used, 16761244k free,  5367700k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 1401 mongodb   20   0  174g 2.0g 1.9g S   23 26.2  18070:55 mongod
 ...

System Info:

$ uname -a
Linux aluminum 2.6.32-31-server #61-Ubuntu SMP Fri Apr 8 19:44:42 UTC 2011 x86_64 GNU/Linux

Notes:

There is another instance in this cluster where mongod is behaving as I would expect and utilizing all available memory.
Looking at mongostat it looks like were consistently having some page-faulting, so the amount of memory used should be growing:
(I asked this same question on the mongodb-user google group but got no response.)


texts: Why we need to optimize CONTAINED IN clause of FOR_SYSTEM TIME using check constraint?

texts:
Using temporal tables one is able to use the FOR_SYSTEM TIME clause with combinations with other options to get valid rows for specific time intervals.

For example, we have:

FOR SYSTEM_TIME ALL
FOR SYSTEM_TIME AS OF @dt
FOR SYSTEM_TIME clause is FROM @start TO @end
FOR SYSTEM_TIME clause is BETWEEN @start AND @end
FOR SYSTEM_TIME clause is CONTAINED IN (@start, @end)

What is said in the documentation and I believe is not true is:

  If you search for non-current row versions only, we recommend you to
  use CONTAINED IN as it works only with the history table and will
  yield the best query performance.

For example, you can see below both table are queried:

Fortunately, Itzik Ben-Gan advises to create check constraint to the temporal table like this in order to help the SQL Engine:

ALTER TABLE [dbo].[SecurityUsersEncrypted]
ADD CONSTRAINT [CK_SecurityUsersEncrypted_SysEndTime]
CHECK ([SysEndTime] = '9999-12-31 23:59:59.9999999');

And the main table is not queried:

If I am using the maximum end period (forcing reads of the main table), the execution plan is changed, too (which is normal):

And if I use one of the other clauses, even my query do not return data from the main table (only history data is returned), the optimization is not applied (as pointed in the documentation):

So, my questions are:

Why in the documentation is said a optimization is working for CONTAINED IN clause, but actually you need to create such check constraint to use it? Should I always create such constraint or this is going to change?
All clauses can return rows from both tables and one of them. Why this optimization is working only for the CONTAINED IN clause?


texts:
Is it possible to write scripts that contain SQLite dot commands ( vis. .read file.sql; .separator ,; .import file.csv; )?

I'm building and repeatedly rebuilding an SQLite database and need to type in roughly twenty four dot command statements every time I rebuild the database. It would be really nice if I could put those commands in a script and have SQLite read them.

Is there a way to put dot commands ( not SQL statements ) into a script and have SQLite run them?

I'm on Mac OS X using bash.


texts: Can I fix Lost arbitrator node errors in MySQL Cluster / NDB?

texts:
I have a cluster of eight MySQL Cluster nodes running NDB. At seemingly random intervals (around 1.5 weeks on average, but they seem to come in spurts when they show up), I see a pair of messages like the following in ndb_${node_id}_out.log:

2016-08-11 06:45:25 [ndbd] INFO     -- Lost arbitrator node 1 - process failure [state=6]
2016-08-11 06:45:31 [ndbd] INFO     -- Prepare arbitrator node 1 [ticket=09770027de4ab90a]

For about fifteen seconds after that first message, all queries to the cluster fail with the message Got error 157 'Unknown error code' from NDBCLUSTER, which I gather from https://stackoverflow.com/questions/18522730/mysql-cluster-error-1296-hy000-got-error-157-unknown-error-code-from-ndbclu means we failed to connect.

I haven't been able to find any more information about this "Lost arbitrator" error or how to fix it. Is there a way to not lose my arbitrators?


texts:
How can I trim last (or first) characters from a string in SQL server?
Given a string like '~~abc~~', the output will be '~~abc'.

For example, in Postgresql, SELECT trim(trailing '~' FROM '~~abc~~') returns '~~abc'.


texts: Allocating Rows From One Table to Available Date Range in Second Table

texts:
I designed a Shop Floor Scheduling System and it’s been running well for years, but the managers are now asking for something more precise and what they are asking for puts me outside my comfort zone. I hope you can help.

We recently upgraded to SQL Server 2017 (Purchased Version)

The current Scheduling System operates on a fixed number of hours (XX per resource) every day, seven days a week, but now they want to schedule based on more flexible hours per day that can account for scheduled maintenance, vacation time and employee availability. There are about 3,000 active operations at any given time and about 30 resources, so efficiency is important.

I'm starting with one Table and one View…

The Operation_Start_By_Date = OperationDueDate - Operation_Duration (Hours)  This way we an operation with more hours will be started before an operation with less hours if they are both due to complete on the same day.

Operations_TBL

Operation_ID
Operation_Name
Operation_Duration
Resource_ID_Link
Operation_Due_Date
Operation_Start_By_Date

Resource_Availability_View

SELECT
TOP (100) PERCENT
Resource_Availability_ID,
Resource_ID_Link,
Available_Start_Date,
Available_End_Date,
DATEDIFF(hh, Available_Start_Date, Available_End_Date) AS DayHours
FROM
dbo.Resource_Availability_TBL
WHERE
(Available_Start_Date_FLD > GETDATE())
ORDER BY Available_Start_Date

The Operation_Duration is an integer data type representing hours. It is reduced each time an employee books time to that operation.

The Available_Start_Date and Available_End_Date are datetime data types representing the date and time each resource will be available each day.

This is where I need help…

Assuming we will start processing the operations in order of the Operation_Start_By_Date. I need a query to generate the operation start and end dates by subtracting Operation_Duration (hours) from a running sum of the available resource hours for each resource.

What query or maybe temp table would be used for output like this?

The first 34 hour Operation ID 1 on Resource ID 1 will run

8 hours on 2019-06-14 leaving 26 hours left at the end of the day

6 hours on 2019-06-15 leaving 20 hours left at the end of the day

5 hours on 2019-06-16 leaving 15 hours left at the end of the day

8 hours on 2019-06-17 leaving 7 hours left at the end of the day

7 hours (of 8) on 2019-06-18 to finish leaving 1 hour left to start the next 20 hour operation ID 3

Then show when the operations will start and end like this.

Temp tables maybe?


texts: InnoDB cannot find a row - possible index corruption?

texts:
I have this table:

+----+------------------+------------------+
| Id |      Start       |       End        |
+----+------------------+------------------+
|  1 | 2018-02-04 11:30 | 2018-02-05 12:00 |
|  2 | 2018-02-04 15:40 | 2018-02-05 08:00 |
|  3 | 2018-02-05 10:00 | 2018-02-06 09:00 |
+----+------------------+------------------+

I found this strange situation using MariaDB 10.1.18 InnoDB.

I called this query:

SELECT * FROM mytable WHERE start BETWEEN '2018-02-04 00:00' and '2018-02-04 12:00'

and I didn't find any rows! The first row (id=1) should have been shown.

Calling this query using another colum:

SELECT * FROM mytable WHERE end BETWEEN '2018-02-05 10:00' and '2018-02-05 13:00'

I found the first row, so I was sure the row with id=1 was there.

I solved the problem when I updated the first row:

UPDATE mytable SET Start='2018-02-04 11:31' where id=1;
UPDATE mytable SET Start='2018-02-04 11:30' where id=1;

Now with this apparently useless update I can find the first row with both queries.

Now I don't have this problem anymore and I cannot investigate, but I want to understand why it happened.

Can it be connected with index corruption? I have two different indexes in start and end columns. If yes, maybe, I should have repaired the indexed, but I'm a bit scared about it, I could not have noticed the problem.

Should I do anything at this point, to make sure that there isn't any corruption of the indexes now?


texts: SSRS 2012, push permission changes down the structure?

texts: I want to make a permission change for someone at the top level of SQL Server Reporting Service and have it trickle down to each folder and report, this is automatic if that folder/report is set to inherit but if it is set to override then it does not. We have hundreds of reports that this 1 permission has to be changed for and I don' want to revert customer permissions on the reports. How can I push this permission change to all the folders and reports?


texts: How can I create a dblink from Postgresql 8.3 to SQL Server (2000 and 2008)?

texts:
I have a Postgresql database that need to read data from two SQL Servers (one SQL Server 2000 and one SQL Server 2008).

I want to setup two dblinks to the SQL Servers.

After googling, foruming, reading documentation, I struggle to find something production ready.

What tool can I use ?


texts: SQL Server 2012 Data Warehousing Overall Flow

texts:
I am studying Data Warehousing in SQL Server 2012

I am attempting to learn the various tools included by practicing on the different tools separately.

I have practiced with:

AdventureWorks samples using Integration Services.
Analysis Services for creating cubes, mining structures and data quality services.
Creating Domains and Knowledgebases with Integration services.

Can anyone give me some clear directions on how to start my first Data Warehousing project?  Which tools do I use, and in what order?


texts: Need some expert eyes to look at my schema

texts:
I am trying to make an online site for local movies. Will be written in PHP and will be using MySQL as the database.

What I want is to create a mini IMDB type site, with movies, people(casts/crew), and show which movies are showing at theaters. (Genres not required).

Have learned from experience that getting the database schema wrong can cause a lot of headache and time later on, especially when it comes to complex queries. So, would appreciate if I could get some advice from DBA experts.

So far, this is what I have come up with.

Database Schema

movies:

id | title | synopsis | release_date | run_time

people:

id | first_name | middle_name | last_name | biography | dob | gender

movies_people:

id | type (cast/crew) | movie_id | people_id | role | lead_role (yes/no) | order

trailers:

id | movie_id | desc | src | src_type | order

images:

id | type | resource_id | src | desc

theaters:

id | name | location | longitude | latitude | city | phone | email | website

movies_showing:

id | movie_id | theater_id | start_date | end_date | description (time, price, etc.)

Example Query: Retrieve all currently showing movies along with all the theaters they are showing at.

I have the following steps in mind.

Get the DISTINCT IDs of all the movies from the movies_showing table as per a date constraint(Today, This week).
Loop through the result, and inside the loop do a query again on the movies_showing table where the movie_id is the ID in the loop, to get all the theaters for that movie.

But this would result in a lot of queries hitting the DB.


texts: Fixing MySQL errors recorded in error logs

texts:
Below is the extract of the most common lines of error being recorded in error logs on my PC:

110905 16:16:54 [Note] Plugin 'FEDERATED' is disabled.
110905 16:16:54 InnoDB: The InnoDB memory heap is disabled
110905 16:16:54 InnoDB: Mutexes and rw_locks use Windows interlocked functions
110905 16:16:54 InnoDB: Compressed tables use zlib 1.2.3
110905 16:16:55 InnoDB: Initializing buffer pool, size = 500.0M
110905 16:16:55 InnoDB: Completed initialization of buffer pool
110905 16:16:55 InnoDB: highest supported file format is Barracuda.
110905 16:16:57  InnoDB: Waiting for the background threads to start
110905 16:16:58 InnoDB: 1.1.8 started; log sequence number 14539513
110905 16:16:59 [Note] Event Scheduler: Loaded 0 events
110905 16:16:59 [Note] C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: ready for connections.
Version: '5.5.15-log'  socket: ''  port: 3306  MySQL Community Server (GPL)
110905 16:46:29 [Warning] Aborted connection 1 to db: 'stockist' user: 'root' host: 'localhost' (Got an error reading communication packets)
110905 16:50:42 [Warning] Aborted connection 2 to db: 'dummy_db' user: 'root' host: 'localhost' (Got an error reading communication packets)

How to fix these issues? I am using MySQL Community Server 5.5.14. It is installed on a PC with 3 GB RAM on NTFS partion. OS is Windowx XP SP2.

Below is the my.ini file:

# MySQL Server Instance Configuration File
# ----------------------------------------------------------------------

# CLIENT SECTION

[client]

port=3306

[mysql]

default-character-set=latin1

# SERVER SECTION
[mysqld]
log-bin=E:/CRITICAL_MYSQL_LOGGING/AUTOMATED_BINARY_LOGS/MySqlBinLog

# The TCP/IP Port the MySQL Server will listen on
port=3306

#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"

#Path to the database root
datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"

# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=latin1

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# The maximum amount of concurrent sessions the MySQL server will
# allow.
max_connections=02

# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again.
query_cache_size=15M

# The number of open tables for all threads.
table_cache=256

# Maximum size for internal (in-memory) temporary tables.
tmp_table_size=9M

# How many threads we should keep in a cache for reuse. When a client
# disconnects
thread_cache_size=8

#*** INNODB Specific options ***

innodb_additional_mem_pool_size=20M

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=1M

innodb_buffer_pool_size=500M

innodb_log_file_size=10M

innodb_thread_concurrency=8

log-queries-not-using-indexes

log-warnings

log=E:/CRITICAL_MYSQL_LOGGING/QUERY_LOGS/QueryLog

log-error=E:/CRITICAL_MYSQL_LOGGING/ERROR_LOGS/ErrorLog

log-slow-queries=E:/CRITICAL_MYSQL_LOGGING/SLOW_QUERY_LOGS/SlowQueryLog

log-bin-index=E:/CRITICAL_MYSQL_LOGGING/BinLogIndexFile

long_query_time=2


texts: How do I query PostgreSQL enums like in MySQL?

texts:
With the following table in MySQL:

CREATE TABLE bob(foo ENUM('a','b','c'));

INSERT INTO bob (foo) VALUES ('a'),('b'),('c'),('a'),('a');

SELECT * FROM bob WHERE foo >= 2;
+------+
| foo  |
+------+
| b    |
| c    |
+------+

With the following table in PostGres:

CREATE TYPE stuff AS ENUM ('a', 'b', 'c');
INSERT INTO bob (foo) VALUES ('a'), ('b'), ('b'), ('c'), ('c');
SELECT * FROM bob WHERE foo > 2;
(HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.)

I've read that there are some fancy ways of getting around this problem, but is there a way to create a Type that mimics the MySQL capabilities?  Or is there a data type with the capabilities that I want?  (Ability to check string on insert, but also to make where cases against an aggregate).


texts: MySQL replication RDS Master - RDS Slave

texts:
I have read many posts about replication from RDS to external and vice versa. But my need is actually a bit different. I need to set replication between two RDS instances.

Of course I know about Multi AZ, but the need to replicate is due to this:

I have already allocated 1TB for my RDS instance, but my DB is around 300GB only. The additional  disk is costing very much. RDS doesn't provide an option to downgrade in terms of disk space. So my only option is either do a manual mysql dump and import to new RDS instance, because my DB size is about 300GB doing a dump and importing is not a good idea is it?

Only option I am left with is, setup master-slave, master will be current RDS and slave will be new RDS with maybe a 350GB disk. Once slave is in Sync with master I will stop master and start using the slave. Here again I am stuck with one issue, I will have to import data from master to slave to initialize replication isn't it? And with 300GB of data how will I do it?

Also all the docs I have read is about replication between external MySQL and RDS, not between RDS instances. Has anybody ever had the need to do the same? How did it go? Any detailed steps to follow?


texts:
I've edited added new TNS name to tnsnames.ora.
Toad for Oracle started to work at once. SQL Plus doesn't work even after logoff/logon, the same problem with my build scripts.

Getting the error: ORA-12154: TNS:could not resolve the connect identifier specified

How could I fix it?


texts: Giving Special Permissions to a Stored Procedure in SQL Server

texts:
I have a restricted database (that contains confidential data) called database B with only a couple of users with access to it. However, I need some select data from a couple of tables within B to be available to the users who request it. The users requesting the data are not added as database users to database B as they are not allowed to have access. These users, however, have access to database A (they are added as DB users of DB A). Is there a way to give a stored procedure access to the confidential database (B) so that any user running the SP could get the data they are requesting? If so, how? I’ve studied that this is possible, but without mention of how.

NOTE: it is NOT an option to move the needed data from Database B to A through IS package (given real-time access is needed). I’ve thought of using views too (but again, how? If you don’t have access to the base tables, you won’t have access to the data through the view either).


texts: Why am I getting drastically different performance results between staging vs. prod for a table?

texts:
I have two amazon rds postgres instances, one is staging, one is production...

In staging, a table has 21 million rows, and in production that same table has 29 million rows.

If I do select count (*) on staging, it takes approx 3 seconds to get the count.

On the production database, that same call takes around 160 seconds.

Production's total relation size is 16168345600, and relation size is 9859072000
Staging's total relation size is 13933518848, and relation size is 8604483584

Autovacuum is on both databases...

Is there anything I can do to narrow down what is causing the drastic difference in performance?


texts: How to find out the oldest full backup still existing on disk?

texts:
one of the things I like to find out is what is the oldest full backup file still living in my servers.

it is the custom to delete or move those files to a different place after a number of days. How far can we go back in time, with what we have on the server?


texts:
I am unable to restore the database to a previous time using the PITR steps mentioned on PostgreSQL website Article 23.3.3. Recovering with an On-line Backup (http://www.postgresql.org/docs/8.1/static/backup-online.html#RECOVERY-TARGET-XID)

The following are the steps I followed:

I made the below changes to postgresql.conf file which is located in the Data Folder:

wal_level = archive
archive_mode = on
archive_command = 'copy "%p" "C:\\archivedir\\%f"'

I created a folder named 'archivedir' on C Drive
I made the below changes to recovery.conf file:

recovery_target_time = '2014-02-05 11:45:00 CST'
primary_conninfo = 'host=localhost port=5433 user=owner password=xxxxx'
restore_command = 'copy "C:\\archivedir\\%f" "%p"'

Stopped the service of postgresql and Deleted Folder contents of pg_xlog
Re-started the service
Recovery.conf is changed to recovery.done
But I am unable to restore the database to a previous state.

I have checked multiple times by dropping tables and trying to restore the database to an earlier state. Is there anything that I am missing?


texts: Is there a way to implement a cross-database task on SQL Server 2012 with the Availability Groups feature?

texts:
We use SQL Server 2012 and its new Availability Groups (AG) feature. There is a task for moving old data of some tables from one database to another database. Both databases are included into different availability groups.

Previously (before using the AG feature) the task was resolved by adding the second server instance as a linked server (sp_addlinkedserver) and executing a distributed transaction in the following way:

begin transaction
insert old data into server2.table2 from server1.table1
delete old data from server1.table1
commit transaction

Unfortunately, distributed transactions are not supported for AG because databases may become inconsistent in case of failover (http://technet.microsoft.com/en-us/library/ms366279.aspx).

Is there some way to implement this task with keeping the AG feature and without implementing the rollback logic in case of exceptions?


texts: Make View Supersede Table in Same Database?

texts:
For testing purposes we have a sanitized copy of our production database in our test world. Because of the way it is sanitized, we now have empty fields where encrypted data previously existed. We would like to be able to run against the database and have dummy data appear to be in the empty sanitized fields.

My initial thought is to have views with the same name as the sanitized tables that would return dummy (probably fixed) data in the empty fields. However, my understanding is that a View cannot exist with the same name as a Table. Is it possible to work around this?

My first thought was to use schemas, but I don't know enough about schemas at the moment. My second thought is to have a second "wrapper" database that references objects in the first database.

Is there a common practice for handling this sort of task?


texts: Restoring from PostgreSQL 9.5 dump to 10 is super slow

texts:
I am trying to restore database dump created on PostgreSQL 9.5.16 to PostgreSQL 10.7 and it is taking a lot of time to restore. Restoring the same dump to 9.5 doesn't take that much. So I am wondering what could be reason behind this slowness.

Some numbers to show how slow it is:

Small tables (<500MB):

9.5 to 9.5 took 1m4.110s
9.5 to 10 took 1m4.575s

Medium tables (500MB to 5GB):

9.5 to 9.5 took 6m53.132s
9.5 to 10 took 6m0.998s

Large tables (5GB+):

9.5 to 9.5 took 131m35.106s
9.5 to 10 took 831m25.539s

Underlying hardware and OS used in both cases are exactly the same.

Thinking that there could be data format difference between Postgres 9.5 and 10  and that could be the reason to this slowness, I tried dumping from Postgres 10 and restoring it on Postgres 10, but that is also super slow.

Here are some numbers of that:

Small tables (<500MB):

10 to 10 took 0m51.248s

Medium tables (500MB to 5GB):

10 to 10 took 5m40.224s

Large tables (5GB+):

10 to 10 took 832m36.850s

Restore commands

The commands I am using to restore:

pg_restore -v -F d -n public -U <user> -d <database> -h localhost -j 4 --disable-triggers -e -L <file with table names> /var/lib/pgsql/20190425/pg_dump_data

Hardware characteristics

The hardware I am running on:

CPU: Intel(R) Xeon(R) CPU E5-2686 v4 @ 2.30GHz (2 cores)
Memory: 16GB
HDD: nvme 300GB
FS: ext4

Postgres settings

Postgres configuration that I have on this setup:

shared_buffers = 128MB

work_mem             = 1MB
maintenance_work_mem = 16MB
dynamic_shared_memory_type = posix

huge_pages     = try
vacuum_cost_page_hit   = 1
vacuum_cost_page_miss  = 10
vacuum_cost_page_dirty = 20
bgwriter_lru_maxpages   = 100
full_page_writes = on
seq_page_cost        = 1.0
random_page_cost     = 4.0

wal_level = minimal
wal_sync_method = fsync
wal_log_hints    = off
wal_buffers      = -1
wal_writer_delay = 200ms
min_wal_size                 = 1GB
max_wal_size                 = 80GB
max_wal_senders    = 0
wal_keep_segments  = 0
wal_sender_timeout = 60s
wal_receiver_status_interval = 10s
wal_receiver_timeout = 60s

autovacuum = on
log_autovacuum_min_duration = -1
autovacuum_max_workers = 3
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
autovacuum_freeze_max_age = 200000000
autovacuum_multixact_freeze_max_age = 400000000
autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = -1

Considerations

Some more things observed:

Running strace on Postgres process showed continuous stream of lseek/read/write calls and it also showed bunch of futex wait and resume calls. These futex calls are not easily seen on Postgres 9.5
Looking at pg_stat_activity table showed WALWriteLock under wait_event column. It used to change but could see this coming up very frequently.


texts:
I'd like to ask about how to calculate forecast data in parallel in a relational DB like PostgreSQL, which seems like a very typical problem when doing forecasts. Let's say we're trying to get the average sales rate for the past X hours for every item we have, and our data looks like this

Table: items

 item_id  | avg |
----------+-----|
 86401    | tbd |
 1234     | tbd |
 22779195 | tbd |
 .
 .
 .

Table: sales

 item_id | qty_sold | time
 --------+----------+--------------------
 86401   | 5        | 2020-01-01T00:00:00
 1234    | 5        | 2020-01-01T00:00:00
 86401   | 2        | 2020-01-01T21:04:04
 .
 .
 .

A query to get the average sales rate for one item would be simple (forgive syntax), like

SELECT item_id, avg(qty_sold)
FROM sales WHERE item_id=86401 BETWEEN '2020-01-01T00:00:00' AND '2020-01-01T23:59:59';

But, how would you do that for every item efficiently and quickly and then save that data in the items table in the avg column for quick reference later? And furthermore, let's say items could be in different locations, and you had to get the sales rate for each item for each location, what would the query be like?

I guess I'm not understanding how to think in SQL because programming wise, it's like you'd just iterate all the items in the items table, and perform that query, but that's not quite the right way to think when doing SQL. With locations in the mix, you'd iterate through the items and do it for each location the item is in, like a nested for loop, which is terrible performance. Would like some help understanding because this seems like something a pattern that would come up often when working with forecasting data.


texts: Does SQL Server TDE decrypts when it enters the buffer pool and encrypts when it leaves

texts:
Been searching but no clear understand.  TDE encrypts at rest not in flight.  But does in flight refer to only between server and client or also between server and SAN storage.

When a database uses TDE, does a page, when it is written to the disk, leave the buffer pool encrypted.  So if somebody were to sniff the wire between the DB server and the SAN see only encrypted data.  Or does it flow unencryted to the SAN and then is encrypted as its being saved.  Same when is it decrypted, when it reaches the buffer pool or when it leaves the SAN? (note I understand when it goes to the client it is not encrypted)

This link was related to backups
Is network traffic encrypted when writing remote backups using SQL Server TDE?
Does it apply to data also?

Reference to docs would be great.


texts: Should I create separate table or have the information as columns in the same table

texts:
Sorry for the title but I could not find anything better that could suit my needs. I'm designing the database of a Document Management (Circulation) application. There are two types of documents - A and B. I've created a table one for each. Any document independent from its type has Instructions information that has to be attached to it. Here are the details of the Instrcutions:

Executors (1 to many on another table)
Deadline
Target

I know that there's a one to one relationship between the document types and Instructions, so according to database normalization I should not have a separate table for it. But on the other hand I'll have to include the same columns in two different tables. What do you think?

P.S. I won't be able to create a foreign key constraint between Instructions table and the two other.


texts:
I have a table called Players and columns Name, PlayerID. I am using sqlite under DB Browser for SQLite.

Unfortunately, all my player's names have a something like a "\n" (turned out to be a /r) at the end of the name.

Ex:

"Mark
"

I tried to Update & Replace all the names with the following query (I have like 450 rows in the table):

UPDATE Players
SET Name = REPLACE(Name,CHAR(10),'')
WHERE PlayerID <= 500

When I execute something like:

SELECT * FROM Players
WHERE Players.Name LIKE 'Mark'

it'll return no rows because of the end line.

I want to change all my rows from this format

"Mark
"

to this

"Mark"

and save all the changes.

How can I solve my problem? What's wrong?


texts: AppFabric is killing the page life expectancy and disk read metrics on my SQL Server.  Does anyone know if I can add indexes to the AsStaging table or will that break things? There are some very expensive queries happening on that table and there is only a clustered index for a table of almost 300 million records. I know very little about the product except that it is on the way out.  I'm still stuck with it right now though and I have other databases on this SQL instance that will be competing for resources in a big way very soon.


texts: How to send only updated values of a row?

texts:
CREATE OR REPLACE FUNCTION notify_col()
  RETURNS trigger AS
$BODY$
    BEGIN
        PERFORM pg_notify('channel', row_to_json(NEW)::text);
        RETURN NEW;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
COST 100;

I'm trying to listen on channel, for any updates in the row (transformed with row_to_json()). But I want pg_notify() to send only updated values of the row, not the whole row.
Do I need an if condition or is there is a better way to do this?


texts: What does Oracle XE perform during installation?

texts:
I have oracle11g2 installed on my noteobook.

I then installed the Oracle Database 11g XE. Now a .dll which uses the oracle11g2 client does not work anymore. First I recogniced that I had now two tnsnames.ora, which I resolved by linking the new one to the old one. But still my program cannot connect via the dll and throws an error.

I am relatively sure that the installation broke something which worked before so I need to know which steps the Oracle 11g XE installation procedure performs to system variable (like PATH) or to registry entries which might be important? Is there something like an installation log or can I somehow revert all changes which were done during installation?

My system is running Windows 7 x64.

Best regards, Fox


texts:
I am wondering what is the correct way to arrange a db schema migration script/scripts.

My current script is a c# code made out of {version}.cs files which I am running one by one in an incremental way.

Is it recommended to put your schema migrations script in a C# code or should I always keep it as pure sql file?
When using Alter table I need to add a condition and check whether the Alter had been done already before. So if creating a SQL script I should add something like IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
       WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME='table' AND column_name='col'). looks like a lot of code, Is this the way to go?
One of the guys suggested using a Stored-procedure and write the schema migration script inside it to leverage on the advantages of a Stored-procedure, What do you think about this approach?


texts: after deleting 100 millions of records in postgresql free space doesn't change

texts:
Recently I configured netflow for monitoring traffic of my company. I use pmacct to collect netflow packets and PostgreSQL for storing them.
I only keep netflow records for a week and after that every day I delete all records that older than 7 days.
this script runs every day with cronjob:
date >> /root/log/cronlog

du -h /var/lib/postgresql/data/base/ >> /root/log/cronlog

psql -U pmacct -d pmacct_switch -c "DELETE FROM acct_v9 WHERE stamp_updated < NOW() - INTERVAL '7 days';" >> /root/log/cronlog
psql -U pmacct -d pmacct_switch -c "VACUUM;" >> /root/log/cronlog

du -h /var/lib/postgresql/data/base/ >> /root/log/cronlog

date >> /root/log/cronlog

and this is the cronlog file:
Wed Jul 8 01:00:02 +0430 20207.4M

/var/lib/postgresql/data/base/17.5M

/var/lib/postgresql/data/base/1299480G

/var/lib/postgresql/data/base/167654.0K

/var/lib/postgresql/data/base/pgsql_tmp7.4M

/var/lib/postgresql/data/base/129936.5M

/var/lib/postgresql/data/base/1638480G

/var/lib/postgresql/data/base/

DELETE 100424891
VACUUM

7.4M

/var/lib/postgresql/data/base/17.5M

/var/lib/postgresql/data/base/1299481G

/var/lib/postgresql/data/base/167654.0K

/var/lib/postgresql/data/base/pgsql_tmp7.4M

/var/lib/postgresql/data/base/129936.5M

/var/lib/postgresql/data/base/1638481G
/var/lib/postgresql/data/base/Wed Jul 8 02:29:33 +0430 2020

Ss you see after deleting 100 millions of records free space doesn't increase!
So, what's the solution?


texts: Update Table data using variables depending on other tables

texts:
Suppose the following tables:

faculty:
+----+--------+
| id | headFK |
+----+--------+

department:
+----+----------+--------+
| id | facultyFK| headFK |
+----+----------+--------+

teacher:
+----+--------------+-------+
| id | departmentFK | chief |
+----+--------------+-------+

What is the best way to go through each row of teacher. Save some column values as variables (suppose: id, departmentFK) to use it in further Queries (Update to be specific)

My aim is to set up subordinations of teacher which is like this chain 'Faculty Head' > 'Department Head' > 'Teacher'

Formally it should look like:

FOR EACH ROW IN teacher
{
    Set curDepartment = Current.departmentFK;
    Set curTeacher = Current.id;
    Set curFaculty = (Query to get faculty);
    Set FacultyHead = (Query to get current faculty head by var curFaculty );
    Set departmentHead = (Query to get current department head by var curDepartment );

    (Query to update teacher.chief with conditions using variable acquired before)
}

Could you please tell the way (Procedures, Functions or ordinary queries) how can I do it because I've never had experience with procedures or local variables in MySQL and I cannot figure out how can I implement it

PS: I'm not asking to write code (that would be too much I think) but an approach to implement it

Update

Thanks to the Rick James's note I came up with the following solution:

UPDATE teacher
    JOIN department ON teacher.DepFR = department.DepPK
    JOIN faculty ON department.FacFK = faculty.FacPK
SET teacher.ChiefFK = CASE
    WHEN teacher.TchPK = faculty.DeanFK THEN NULL
    WHEN teacher.TchPK = department.HeadFK THEN faculty.DeanFK
    ELSE department.HeadFK
END;

Is it a good way of doing it?


texts:
I am having troubles with this simple query which takes a few minutes.

db.data_items.find({'observation_target.observation_target_id': 2190}).limit(20000)

The explain output shows that the query uses the right index:

{
    "cursor" : "BtreeCursor observation_target.observation_target_id_1",
    "isMultiKey" : false,
    "n" : 20000,
    "nscannedObjects" : 20000,
    "nscanned" : 20000,
    "nscannedObjectsAllPlans" : 20000,
    "nscannedAllPlans" : 20000,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 4515,
    "nChunkSkips" : 0,
    "millis" : 174037,
    "indexBounds" : {
        "observation_target.observation_target_id" : [
            [
                2190,
                2190
            ]
        ]
    },
    "server" : "xxx.mongolab.com:49710",
    "filterSet" : false
}

Moreover, when limiting the number of results successively to 2000, 4000, 6000 ... 20000, each individual query is fast.  The MongoDB version is 2.6.5.


texts:
I have a SQL Server 2005 which I have configured the database mail to setup monitoring alerts and notifications. I also create a email distribution list containing a couple of emails. The thing is that when I send the email from the database to a distribution list, it never gets to the emails inbox, but if I do it individually it does.

I tried to send an email from the outlook to the distribution list and it worked fine. I do not understand what is happening. Please can you help me?


texts: How to rewrite this correlated query as a JOIN?

texts:
I'm just getting into SQL and am encountering some difficulty wrapping my head around correlated queries. I'm still trying to master it but am wondering what would be the right syntax for rewriting this statement as a JOIN. Essentially I have an employee list by department and want to know the highest salary in each department. Here's the query as a correlated query:

SELECT dept, name, salary
FROM employees AS a
WHERE salary >= ALL
(
  SELECT salary
  FROM employees b
  WHERE a.dept = b.dept AND salary > 0
)


texts: In Oracle 12c, how can we implement pagination on large table using rowid?

texts:
I am new to the database. I would like to implement pagination on large tables using rowid but what I found that rowid can be alphanumeric digits and not necessarily they are in any order so I wonder if we can use them to paginate the tables. The table from which I would like to get the records in pagination is very large table having more than 2 billion records and if we execute the query based on offset and length then that comes up with an error Ora1652 unable to extend temp segment in tablespace temp. So is there way to get the results in pagination using rowid.

Any help would be appreciated thank you in advance.

Edited:

Let's suppose I have a Table say LARGE_ROWS having ID and RANDOM two columns whereas ID is the primary key column.

SELECT /*+ ordered use_nl(p s) */
    *
FROM
    (
        SELECT
            ROWNUM RN,
            RD
        FROM
            (
                SELECT
                    ROWID RD
                FROM
                    large_rows
                ORDER BY
                    large_rows.id
            ) T
        WHERE
            ROWNUM < 1000
    ) P,
    large_rows S
WHERE
    RN > 900
    AND P.RD = S.ROWID;


texts: Any caveats using SQL Server 2008 CDC off replicated tables?

texts:
Main TRX System ---- Replicated to ---> "Archive"  --- CDC/ETL --> Some NOSQL DB...

For performance reasons we never run any ETL off the main transnational system only off the archive.

I enabled CDC on the archive on the replicated tables and it seems to work. So as soon as the archive receives replication data from trx system, CDC finally sees the changes. Just wondering if any issues with this?


texts: How to query and group based on matched multiple conditions?

texts:
I have a tableName with records as sample as following, the mainID is unique at all for same "name", but subID has different values (one or more) for same mainID(s),

name  mainID  subID
___________________
A     1234    12
A     1234    19
B     2341    12
C     3412    19
A     1234    3
B     2341    3
D     4123    12
C     3412    12
E     5123    3
F     1235    19

I want to have a result as following and mapping them with a "String" as sample like below.

name  mainD  subID
__________________
A     1234   X
B     2341   YY
C     3412   Y
D     4123   Z
E     5123   V
F     1235   Q

the mapping I'm defining are like below,

if (subID for same mainID matched _all_ 12, 19 and 3) then map it to "X"
if (subID for same mainID matched _only_ 12, and 3) then map it to "YY"
if (subID for same mainID matched _only_ 12 and 19 ) then map it to "Y"
if (subID for same mainID matched _only_ 19 and 3 ) then map it to "YYY"
if (subID for same mainID matched _only_ 12) then map it to "Z"
if (subID for same mainID matched _only_ 3) then map it to "V"
if (subID for same mainID matched _only_ 19) then map it to "Q"

What I tried was/is make them inside a CASE WHEN ... THEN ... END, but this matched one name like "A" print in multiple rows, with mapped to "Z", "V", "Q".

Can anyone please help to achieve the expected query result, thanks in advance


texts: integer variable for query string - option(optimize for variables)

texts:
I have problem related to my post variable for query string (or dynamic query?).

I encountered a hurdle related to using an integer parameter. The query looks like this:

DECLARE @area NVARCHAR(255)
SET @area = 'here_is_the_name'    --i have specyfic names here
DECLARE @country NVARCHAR(255)
SET @country = 'here_is_the_country_name'     --and there
DECLARE @instance INTEGER
SET @instance = 41

DECLARE @SQLq NVARCHAR(MAX)  --declaring variable for query string
SET @SQLq ='

              SELECT
                  a.something,
                  b.somethingelse,
                  c.entirelysomethingelse,
                  c.diffrentthanthoseabove

              FROM
                  tablea a
                  LEFT JOIN tableb b ON ...
                  LEFT JOIN tablec c ON ...
                  LEFT JOIN tabled d ON ...
              ...

              WHERE
                  b.area = (@v1)  --variable 1 outside of query string
              AND
                  c.country = (@v2)   --variable 2 outside of query string
              AND
                  ((b.something = ''aaa'') OR (b.somethingelse = ''aaaa'')) --this is connected to the query so there is no problem with this part
              AND
                  a.problematic_integer_attribute != (@v3) -- problematic integer variable 3 outside of query string
              AND
                  d.something LIKE (''%@%'')  --not important in this case

              GROUP BY
              ...
              ORDER BY
              ...

--and here lies the biggest hurdle with option(optimize for variables)

OPTION(OPTIMIZE FOR (@v1 = ''' +@area+ ''', @v2 = ''' +@country+ ''', @v3 = '+@instance+'))'  --end of a string query

EXEC SP_EXECUTESQL @SQLq,
N'@v1 NVARCHAR(255), @v2 NVARCHAR(255), @v3 INTEGER',
@v1 = @area, @v2 = @country, @v3 = @instance

To be able to use variables from outside of the string query I had to "break" a string chain as @mortb wrote in related post. But even though I did the same with @instance (the integer variable) it's not working.
I'm getting an error:

Msg 245, Level 16, State 1, Line 9
Conversion failed when converting the nvarchar value '...here is the query...' to data type int.

The error is connected with @v3 or @instance in this part:

option(optimize for (@v1 = ''' +@area+ ''', @v2 = ''' +@country+ ''', @v3 = '+@instance+'))'

I can't find solution for this problem. I tried to "break" the string chain with the same method as I used for string/varchar variables like:

 option(optimize for (@v1 = ''' +@area+ ''', @v2 = ''' +@country+ ''', @v3 = '''+@instance+'''))'

But it's not working. Is there a different method for using integer variables?

I'm using query string (it's called dynamic query I suppose) because I have to make a research connected to parameter sniffing (here option(optimize....)) without creating stored procedures and being able to use variables. Does anyone encountered similar problem and found a solution? If someone does please help me with overcome this hurdle.


texts: MongoDB: Queries running twice slow on NEW server compared to OLD server

texts:
I transferred current/old running DB into a new standalone server for MongoDB. To do this, I performed the following:

Took dump of data from OLD server
Restored data from the generated dump into NEW server
Configured the server for authentication

Issue:
I noticed that after performing the above, few queries on the NEW server were running slow almost twice the time compared to their performance on the OLD server.
Configurations:
The configurations of both the servers are same however the NEW server has 32 GB RAM while the OLD server had 28GB RAM. OLD server had other applications and servers running as well. While the NEW server is a dedicated server only for this DB.
CPU consumption is similar however RAM is heavily occupied in the OLD server while it is comparatively less occupied on NEW server.
Therefore, NEW server is better equipped in hardware and RAM consumption. Also NEW server is standalone dedicated to only this DB.
Question:
Why could my NEW server even though it is standalone be slow compared to OLD one? How can I correct this?


texts:
I want to check open socket connection count to the database.

Is there any way to get the number of open sockets to the database?


texts: Distribution database corrupt - What to do?

texts:
The below error message has been received from one of our SQL Server databases:

  SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x6847f47b; actual: 0x15160a19). It occurred during a read of page (1:934295) in database ID 5 at offset 0x000001c832e000 in file 'C:\XYZ.MDF'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Obviously I have to run DBCC CHECKDB on the corrupt database. Before doing that, I tried to find out what the database name was (using database id) and what object the error was related to, using and the page number that was given in the error message.

So I understood that the database name was distribution and the table name was MSrepl_errors.

I'm not much familiar with SQL Server replication features, so would appreciate if someone can please help me with the below questions:

In order to investigate and/or possibly fix the issue, I have to first execute DBCC CHECKDB; however, CHECKDB cannot be run  without first putting the database in single user mode, is this correct?
Regarding the fact that the distribution database is not used by end users, do I still have to put it in the single user mode? And, if yes, what will be the impact on the replication jobs?


texts:
I am looking to partition a table (nearly 30M rows) by date ranges
(mysql version : 5.1.73)

CREATE TABLE `TABLE_X` (
  `name_x` varchar(50) NOT NULL,
  `type_x` varchar(5) NOT NULL,
  `status_x` tinyint(1) NOT NULL,
  `date_x` date NOT NULL,
  KEY `xdate` (`date_x`)
)ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (TO_DAYS(`date_x`))
 (
    PARTITION p13q1 VALUES LESS THAN (TO_DAYS('2013-04-01')),
    PARTITION p13q2 VALUES LESS THAN (TO_DAYS('2013-07-01')),
    PARTITION p13q3 VALUES LESS THAN (TO_DAYS('2013-10-01')),
    PARTITION p13q4 VALUES LESS THAN (TO_DAYS('2014-01-01')),
    PARTITION p14q1 VALUES LESS THAN (TO_DAYS('2014-04-01')),
    PARTITION p14q2 VALUES LESS THAN (TO_DAYS('2014-07-01')),
    PARTITION p14q3 VALUES LESS THAN (TO_DAYS('2014-10-01')),
    PARTITION p14q4 VALUES LESS THAN (TO_DAYS('2015-01-01')),
    PARTITION p15q1 VALUES LESS THAN (TO_DAYS('2015-04-01')),
    PARTITION p15q2 VALUES LESS THAN (TO_DAYS('2015-07-01')),
    PARTITION p15q3 VALUES LESS THAN (TO_DAYS('2015-10-01')),
    PARTITION p15q4 VALUES LESS THAN (TO_DAYS('2016-01-01')),
    PARTITION p16q1 VALUES LESS THAN (TO_DAYS('2016-04-01'))
);

when i do a 'select' query to retrieve data between two dates from the same month, the result is correct

explain partitions select count(*) from TABLE_X where date_x between '2013-10-01' and '2013-10-27' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: TABLE_X
   partitions: p13q4
         type: index
possible_keys: xdate
          key: xdate
      key_len: 4
          ref: NULL
         rows: 984405
        Extra: Using where; Using index

but when i try to retrieve rows between different months, i get this result

Eg 1:

explain partitions select count(*) from TABLE_X where date_x between '2013-10-01' and '2013-11-27' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: TABLE_X
   partitions: p13q1,p13q4
         type: index
possible_keys: xdate
          key: xdate
      key_len: 4
          ref: NULL
         rows: 1562732
        Extra: Using where; Using index

this query should use only p13q4 partition

Eg 2:

    explain partitions select count(*) from TABLE_X where date_x between '2013-10-01' and '2014-01-27' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: TABLE_X
   partitions: p13q1,p13q4,p14q1
         type: index
possible_keys: xdate
          key: xdate
      key_len: 4
          ref: NULL
         rows: 2085003
        Extra: Using where; Using index

this query should use p13q4 & p14q1 partitions.

 I don't understand why it's using the p13q1 partition,What am I missing?


texts: I have snapshot replication running on three different servers, two publishers and one subscriber.  The replication processes have associated jobs (either building the snap shot or grabbing the snap shot, I believe) that have ugly system-generated names.  Is it safe to rename these something that's readable and understandable?


texts:
Super n00b question, I know, but I can't seem to find it anywhere that's easy to find, so it would be good to document this here.

Say I connect to a Teradata system as a particular user, NICK, but I need to access tables owned by a different user, BOB.

Instead of having to qualify each table name with BOB. I'd like to just set my default database for the connection to BOB and then query the tables using their unqualified name.

In other words, instead of this:

-- Cartesian Join of Doom (CJD™)
SELECT COUNT(*)
FROM BOB.TABLE1, BOB.TABLE2, BOB.TABLE3;

I want to be able to do this:

-- CJD™ for the lazy
SELECT COUNT(*)
FROM TABLE1, TABLE2, TABLE3;

Is there a way to do this for the life of a connection?


texts: Disabling password change for a MySQL user

texts:
Is it possible for MySQL users to prevent/disable changing their password with just the USAGE option? There are no global permissions allowed.
I tried to google, but had no luck.


texts:
I'm trying to run vacuum delete only on a table with big amount of columns (800+) and 4 million rows.

I know it's recommended to use deep copy instead, but still am wondering if running vacuum is still possible on such big table.
This is my error:

vacuum_max_buffer is too small to vacuum table <table> - need at least: 3388MB

is it possible to change the value of vacuum_max_buffer (set vacuum_max_buffer=xxxxx), or is it best not to change this and go on with the deep copy?

Thanks,
Meny


texts: Portable client tool to connect to SQL Server Express LocalDB and execute a query

texts:
I am running out of ideas on this little problem:

I need to execute a query on a LocalDB database directly on user's computer. I  do not want to install anything there and there is only .NET (4.5.2) and SQL Server 2014 Express LocalDB installed.

I do not want to copy database file to my computer and back againt with a little modification, I need to run just one or two queries directly on that machine.

Is there any portable command line tool that would allow me to do this? SQLCMD is not usable, since it requires installation of ODBC driver, SSMS requires installation too.


texts: Why is ALTER faster than UPDATE (for full table rewrites)?

texts:
Let's create a garbage table..
CREATE TABLE foo
  AS SELECT x::text FROM generate_series(1,20e6) AS t(x);
Time: 14077.654 ms (00:14.078)

Now, let's look at boring UPDATE,
UPDATE foo SET x = 'foo';
Time: 22239.050 ms (00:22.239)

Compared with ALTER,
ALTER TABLE foo
  ALTER COLUMN x
  SET DATA TYPE text
  USING 'foo';
Time: 9480.027 ms (00:09.480)

It's approximately twice as fast.


texts: Difference between an endpoint and a port in SQL Server

texts:
While I know why are ports used in SQL Server, I wonder what is the difference between an endpoint and a port. Ports can be managed using Configuration Manager, is it also possible to manage endpoints with CM?
Thanks for your time.


texts: Setting up an alert to monitor where/when your databases are backed up

texts:
I was reading Who’s Backing Up That Database? by Kendra little, specifically the section, "IF THE BACKUP IS Succeeding, YOU CAN TRY ANOTHER TRICK".

I have been looking into monitoring my databases to send an email when the databases are backed up to another location outside of my backup path. So far what I have come up with has been is to set up a job that would fire off every 5 minutes which contained the following script:

BEGIN
IF (SELECT COUNT (bmf.physical_device_name)
FROM [msdb].[dbo].[backupset] bs inner join [msdb].[dbo].[backupmediafamily] bmf
on bs.media_set_id = bmf.media_set_id
where bs.[database_name] = 'logbackuptest'
and bmf.physical_device_name not like 'D:\backups\%') > 0

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBA Profile',
    @recipients = 'IT@ITCORP.com',
    @body = 'The database was backed up to a different location',
    @subject = 'A database was backed up to a different location' ;
END

Does anyone else have a good blog post or any other ideas? I'm not in a situation where backups are happening to non-default locations without my knowledge; the question is purely academic.