texts: sp_send_dbmail sends query results from one server but not a second server

texts:
Both servers are able to normally send email without issue

server a is able to send the email for the query below, but server b has an error message saying, "mail could not be sent to the recipients because of the mail server failure. Exception message: Cannot send mails to mail server."

Test emails and other jobs with sp_send_dbmail steps work fine on both servers.

Doesn't matter if done as job or in a query window behavior on both servers is consistent.

Failing server is at 12.0.5203.0, working box is 12.0.5511.

Code is:

DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)
select cast(getdate() as date)
SET @subject = 'Detail' + cast(cast(getdate() as date) as varchar)

SET @tableHTML =
N''+
N'
Header
' +
N'
' +
N'

operation_id
event_message_id
Date
Time
Message
EventName
message_source_name

' +
CAST ( (

SELECT
td = operation_id,'',
td = event_message_id,'',
td = cast(message_time as date) ,'',
td = cast(message_time as time)  ,'',
td = MESSAGE ,'',
td = event_name ,'',
td = message_source_name
FROM   (
       SELECT  em.*
       FROM    SSISDB.catalog.event_messages em
       WHERE   em.operation_id = (SELECT max(execution_id) FROM SSISDB.catalog.executions where package_name='packagename.dtsx')
           AND event_name NOT LIKE '%Validate%'
       )q
ORDER BY message_time
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'
'

EXEC msdb.dbo.sp_send_dbmail @recipients='someone@somewhere.com',
@subject = @subject,
@body = @tableHTML,
@body_format = 'HTML' ;

Body is inline so CU4 isn't the issue. Any thoughts or suggestions would be appreciated.


texts:
I try to export data to a flat file from OLE DB source. Here is what I do under OLE DB source editor. In SQL command Text:

declare @t
   table (customerid varchar(100),[enrollment_Date] varchar(100))
insert into @t exec master.dbo.my_stored_procedure
select * from @t

In the preview, I can see data but when I execute it, I get empty text file. Do you know why?


texts: DB design for Shifts for Services/Events

texts:
I need to design DB to store Employee Shifts where employees work on different Services. Shifts for Services will be mostly weekly and for someday there could be specific shifts for employees like extra shifts other than weekly shifts.

I had 2 designs in mind :

to store weekly shifts and special shifts together in one table
to store weekly shifts and special shifts in different table

Most of the time we will be either displaying the shifts for Services, fetching the shifts to help book appointments.

Service

id
name

EmpService

 emp_Id
 id (reference Service.id)

Two solutions are :

WeeklyAndSpecial Shift (to store weekly shifts and special shifts together in one table)

id
service_Id
emp_Id
day_Of_Week
start_Datetime
end_DateTime
available

Or

Weekly Shift (to store weekly shifts and special shifts in different tables)

id
service_Id
emp_Id
day_Of_Week
start_Datetime
end_DateTime
available

Special Shift

id
service_Id
emp_Id
start_Datetime
end_DateTime
available

In both cases:

For weekly shifts, start_DateTime and end_DateTime will store time with a constant date.

day_Of_Week indicates 0(Monday), 1 (Tuesday) and so on.

available indicates whether the service/emp is available or not available.

I am little confused about which approach I should go for.

The challenging aspect will be to calculate available shifts to book an appointment by considering weekly and special shifts.


texts: Speeding up conversion of MyISAM to InnoDB

texts:
I've got a mysql 5.1 server with a database of approximately 450 tables, taking up 4GB . The vast majority of these tables (all but 2) are MyIsam. This has been fine for the most part (don't need transactions), but the application has been gaining traffic and certain tables have been impacted because of table-locking on updates. That's the reason 2 of the tables are InnoDB now.

The conversion on the smaller tables (100k rows) don't take long at all, causing minimal downtime. However a few of my tracking tables are approaching 50 million rows. Is there a way to speed up an ALTER TABLE...ENGINE InnoDB on large tables? And if not, are there other methods to convert minimizing downtime on these write-heavy tables?


texts:
I am trying to join two datasets and find the most recent certification date
I the following 2 datasets
Certifications

(1, 'Judge Level 1', 'Judge', 10, 2),
(2, 'Judge Level 2', 'Judge', 20, 2),
(3, 'Judge Level 3', 'judge', 30, 2),
(4, 'Olympic Judge', 'olympic judge', 30, 2),
(5, 'Junior Leader', 'leadership', 10, 1),
(6, 'Trainee', 'leadership', 10, 1),
(7, 'Instructor', 'leadership', 10, 1);

members_certifications

(1000052, 7, '2016-07-01'),
(1000053, 1, '2016-07-01'),
(1000053, 3, '2018-07-01'),
(1000053, 4, '2019-07-01'),
(1000053, 5, '2020-07-01'),
(1000059, 1, '2016-07-01'),
(1000059, 2, '2017-07-01'),
(1000059, 5, '2016-07-04'),
(1000059, 6, '2016-07-01'),
(1000059, 6, '2016-07-02');

I would like the result set to be

1000059, Junior Leader , leadership, 10, 2016-09-04
1000059, Judge,Judge Level 2, 20, 2016-09-04

I created a exact example http://sqlfiddle.com/#!9/20c30b1/3/1  MySql version 5.6
The first query produces a large data set when I reduce the query by setting a specific id (in this case 100059) I get 0 results.
Also is there a more query efficient or less bulky way create this query.
CREATE TABLE `certifications` (
  `id` int(11) NOT NULL,
  `title` varchar(254) NOT NULL,
  `type` varchar(254) NOT NULL,
  `sort_order` int(11) NOT NULL,
  `expiration_interval` tinyint(4) NOT NULL DEFAULT '2'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `members_certifications` (
  `member_id` int(11) NOT NULL,
  `certification_id` int(11) NOT NULL,
  `effective_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query 1
SELECT members_certifications.member_id AS member_id,certifications.title AS title,
certifications.type AS type,certifications.sort_order AS sort_order,
MaxTable.effective_date AS effective_date FROM members_certifications, certifications,
(SELECT     members_certifications.member_id AS member_id,     certifications.type AS type,
 MAX(members_certifications.effective_date) AS effective_date
 FROM     members_certifications, certifications
 WHERE     members_certifications.certification_id = certifications.id
 GROUP BY      certifications.type,members_certifications.member_id   ) MaxTable
 WHERE  members_certifications.certification_id = certifications.id
 AND certifications.type = MaxTable.type  AND members_certifications.member_id = MaxTable.member_id  AND members_certifications.effective_date = MaxTable.effective_date
ORDER BY  MaxTable.member_id, certifications.sort_order, MaxTable.type, certifications.title;

query II (0 results)
SELECT members_certifications.member_id AS member_id,certifications.title AS title,certifications.type AS type,certifications.sort_order AS sort_order,MaxTable.effective_date AS effective_date FROM members_certifications, certifications,  (SELECT     members_certifications.member_id AS member_id,     certifications.type AS type,     MAX(members_certifications.effective_date) AS effective_date   FROM     members_certifications, certifications   WHERE     members_certifications.certification_id = certifications.id   GROUP BY      certifications.type,members_certifications.member_id   ) MaxTable WHERE  members_certifications.certification_id = certifications.id  AND members_certifications.member_id = 100059  AND certifications.type = MaxTable.type  AND members_certifications.member_id = MaxTable.member_id  AND members_certifications.effective_date = MaxTable.effective_date ORDER BY  MaxTable.member_id, certifications.sort_order, MaxTable.type, certifications.title ;


texts: SQL Server 2012 Failover Cluster and attached SAN

texts:
I have three servers in a Windows Server failover cluster.  SQL Server 2012 Failover Cluster is installed on all nodes. I have an issue with shared disks attached from SAN.

overview with the same configurations for all

DNS are SQL1, SQL2, SQL3
RAID1 + 5 SAS, 50Gb from SAN for Windows failover, 1TB for SQL failover
Windows Server failover cluster was initialized from SQL1
all installations before Windows Server failover cluster was created were initialized from SQL1
I shared SQL2 -> SQL1

my questions are:

All shared disks (SAN 50GB + 1TB) are added to Windows Server failover cluster, but only from SQL1 is possible to leave available storage for SQL Server 2012 Failover Cluster. I'm not able to set available disk on SQL2 / SQL3 node, they can be only members of clustered storage or in offline mode. Is this possible? If yes, then how?
(Windows OS specific question) built_in SAS HDDs in RAID1 + 5 isn't possible to add to the pool in Windows Server failover cluster (members are empty) but is possible to create a pool for each of noded in Server view, e.g. then to create iSCSI. Why those two options seems like as not doing the same things; specific server can see available HDD for pool, from Windows failover is/are member empty.
Is it possible to migrate with installated SQL Server 2012 Failover Cluster and their files from one storage to another, for example when I attach a new SAN to Windows Server failover cluster?
Then is possible to install Windows Server failover cluster only from SQL1? On SQL2 / SQL3 node installations ended with no available storage.


texts:
So I created a procedure that calculates the average and median of a string array of numbers:

CREATE PROCEDURE dbo.spAvg_Median
    @input nvarchar(20)
AS
BEGIN
    declare @split table(id int primary key identity (1,1), number int)

    INSERT INTO @split SELECT value FROM string_split(@input, ',')

    SELECT Average FROM (
        SELECT SUM(number)/COUNT(number) as [Average]
        FROM @split
        ) AS T1
    UNION
    SELECT Median FROM (
        SELECT o.number AS [Median], rn=ROW_NUMBER() OVER (ORDER BY o.number), c.c
        FROM @split AS o
        CROSS APPLY (SELECT c=COUNT(*) FROM @split b) AS c
        ) AS x
        WHERE rn IN ((c + 1)/2, (c + 2)/2)
END
RETURN 0
GO

However, when you run this query you get the following result:

EXEC dbo.spAvg_Median'1,5,9,8,7'

What I want it to output is Average in one column and Median in another column next to it.

The UNION command is there so it doesn't output it as two different queries like this, since that is not what I want:


texts: Migration 100 TB of data from SQL Server 2008 to 2016

texts:
We are planning to migrate to 100 TB of data from one of our client to new SQL Server environment. We just started receiving information from client. It will take couple of months to complete the project. But, can anyone please provide detail plan for migration such a large number of SQL Servers and data. New environment will have 4 fail-over cluster node at 4 different location.

Current environment: SQL Server 2008 R2
New environment : SQL Server 2014/16


texts: TempDB causing high IO writes - dependent on server's max memory setting

texts:
I have a mirrored SQL Server 2012 database. The first server has a hugh tempdb write IO load when the maximum memory limit is set above 8192 mb.

These 8192 where the original setting for both the first and second server and I can only assume this can be an origin of this wierd behavior. However, when I increase the limit on the second server, the first still causes high io load. Once I set it back to 8192 or lower, tempdb activity zeros.

Also changing the mirroring model between High Performance / High Safety / Monitor has no effect. I have not yet tried to remove the mirror as this will cause an application downtime...

Have I found a bug?


texts: Postgres - Selectively either null or delete foreign key rows (without cascade)

texts:
I understand this can easily be achived with cascade behaviour, but cascade isn't desirable in this case.

Is there a way of automatically either nulling the column or deleting the row referencing a foreign key depending on whether or not the column is nullable in the referencing table?

Say we have the following tables:

              Table "public.a"
           Column           |  Type   |     Modifiers
----------------------------+---------+--------------------
 pk                         | integer | not null

              Table "public.b"
           Column           |  Type   |     Modifiers
----------------------------+---------+--------------------
 fk                         | integer | not null
"b_a_fk_fkey" FOREIGN KEY (fk) REFERENCES a(pk)

              Table "public.c"
           Column           |  Type   |     Modifiers
----------------------------+---------+--------------------
 fk                         | integer |
"c_a_fk_fkey" FOREIGN KEY (fk) REFERENCES a(pk)

We can get tables referencing a table with queries such as this. Does anyone have an example of how to generically delete rows in table 'b' and null columns in table 'c' referencing a given value for a.pk?


texts:
I have result of a query as below first table but the requirement is to display columns first which contain data in it.

Result:

Requirement:

Sample data generation:

Create table #CustData
(
CustID int,
CustName varchar(500),
VL1 varchar(10),
VL2 varchar(10),
VL3 varchar(10),
VL4 varchar(10),
VL5 varchar(10),
VL6 varchar(10),
NUMBER INT
)

INSERT INTO #CustData (CustID,CustName,VL1,VL2,VL3,VL4,VL5,VL6,NUMBER)
SELECT 1,'ABC', NULL, 'dl2','dl3','dl4','dl5','dl6',08586
UNION ALL
SELECT 2,'DEF', 'dl1', 'dl2',NULL,'dl4','dl5','dl6',54646
UNION ALL
SELECT 3,'GHI', 'dl1', 'dl2','','dl4',NULL,'dl6',6464
UNION ALL
SELECT 4,'JKL', NULL, 'dl2','dl3','dl4','dl5',NULL,44645
UNION ALL
SELECT 5,'MNO', 'dl1', NULL,NULL,'dl4','dl5','dl6',46457256


texts: How should I handle unknown primary keys?

texts:
I'm working as a database-administrator at a school. Recently the school upgraded its pupil administrative software into a web-based solution. This works great, but this web-based solution is missing some features which we used to have. Thankfully the new administrative software enables the possibility to synchronize local databases with their online database through an API.

In my local database each pupil used to have a primary key like 2015XXXX where XXXX is some number. The online application uses a different system. As far as I see the identification number currently ranges from XXXX (number in the thousands) to XXXXX (ten thousands). I have no idea if there exists a logic in these numbers.

I prefer to use the new identification numbers, since it's a lot easier to keep pupils in my local database identical to the online system. However I wish to keep the previous pupils which don't have a new identification number. What kind of primary key should I give those, preventing a collision of primary key's?

I see some options:

(what I have done so far) I've changed previous pupils id's as a huge number. Pupil 20050123 becomes 1020050123. The school will not reach this number of pupils any time soon. But I have nog guarantee the online system won't construct big numbers?
Using negative numbers? Pupil 20050123 becomes -20050123
Other?


texts: setting a default collation for a postgresql database

texts:
I have created a database with Turkish collation.

createdb -l tr_TR test_tr -E LATIN5 -T template0

Then a table with a row.

\c test_tr
create table turkish(one text);
insert into turkish values('inci');

Everything is expected.

test_tr=# \l
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
------------+----------+----------+-------------+-------------+-----------------------
 test_tr    | postgres | LATIN5   | tr_TR       | tr_TR       |

The problem, I expect İNCİ but it gives me INCI. This is wrong for Turkish language.

test_tr=# select upper("one") from turkish ;
 upper
-------
 INCI
(1 row)

It turns out, the default collation is still English.

test_tr=# select upper("one" collate "tr_TR"),upper("one"), "one" from turkish ;
 upper | upper | one
-------+-------+------
 İNCİ  | INCI  | inci

I know that this could be fixed via giving tr_TR collation while table creation like following:

create table turkish (one text collate "tr_TR");

So the question:

Is there any way to set default collation that Postgresql uses for table creation? That way, I can omit unnecessary collation specification on every table creation script.


texts: "where" on json field causing Arithmetic overflow error

texts:
I have a table:

when I select all from the table:

 SELECT JSON_VALUE(LastReadings, '$.lastReadings."7-temp".value') as x FROM
(VALUES
(2,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value":12.0},"7-temp": {"name": "Temperature","value":12}},"customId":null}'),
(3,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value":12.0},"7-temp": {"name": "Temperature","value":32}},"customId":null}'),
(4,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value":12.0},"7-temp": {"name": "Temperature","value":22}},"customId": null}'),
(5,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value": 12.0},"7-temp": {"name": "Temperature","value":123}},"customId": null}')
)
[AmirTestTable](Id,LastReadings )

I get

+-----+
|  x  |
+-----+
|  12 |
|  32 |
|  22 |
| 123 |
+-----+

However when i try to add a where clause:

 SELECT JSON_VALUE(LastReadings, '$.lastReadings."7-temp".value') as x
 FROM
(VALUES
  (2,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value":12.0},"7-temp": {"name": "Temperature","value":12}},"customId":null}'),
  (3,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value":12.0},"7-temp": {"name": "Temperature","value":32}},"customId":null}'),
  (4,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value":12.0},"7-temp": {"name": "Temperature","value":22}},"customId": null}'),
  (5,N'{"groups": [],"lastReadings": {"amir": {"name": "Ink","value": 12.0},"7-temp": {"name": "Temperature","value":123}},"customId": null}')
)
[AmirTestTable](Id,LastReadings )
where JSON_VALUE(LastReadings, '$.lastReadings."7-temp".value') > 12.0

I get the error:

  Arithmetic overflow error converting nvarchar to data type numeric.

If I change the 123 value to 99 the query works, or if i change the where clause to > 12. It also works if i cast it explicitly to a number.

I don't understand why it doesn't work directly though.


texts:
I created SSIS project solution in VS2008.
I created 5 packages and deployed them to SSIS Storage succesfully.
I executed 4 packages successfully.
I leave SQL server few days alone (holiday!).
I open SSIS solution and made little change in 5th package. Save and Build  solution.
I execute an deploy (same way as before, using dtsinstall.exe ..) Change does not apply.
I deleted package from SSIS storage.
I Execute deploy again.. but.. package is missing..

*Deploying is allways successfull

why packages are not loaded to server ?

What am i missing ?

Thank you


texts: getting error while starting memecached service with ndb engine

texts:
i am installed memcached library by following the steps in the internet.
now there is a binary file created in bin folder of mysql

now i exicuting the command memcached i am getting error like this

[root@centos1 ~]# /usr/local/mysql/bin/memcached E /usr/local/mysql/lib/ndb_engine.so -e "connectstring=10.10.111.111:1186;role=db_only" -vv -c 20 -u root

24-Feb-2012 15:21:06 IST NDB Memcache 5.5.19-ndb-7.2.4 started [NDB 7.2.4; MySQL 5.5.19]
 Contacting primary management server (10.10.111.111:1186) ...
FAILED.
Could not connect to NDB. Shutting down.
Segmentation fault

please help me to resolve this problem


texts: How can I override the MySQL DELETE command to update a field rather than to remove the row?

texts:
I wonder whether it's possible to override the functionality of MySQL's DELETE command to update a field on the selected row rather than to delete it completely.

This would make it possible to set a flag, for example deleted, from 0 / null to 1, without using UPDATE.

Is it possible to do, and if so how?


texts: Why my indexes monitoring status change to 'no'?

texts:
On one of my production Oracle database 11g Standard Edition I have a lot (a very lot of) indexes. Some of them (majority) are created by the application installer, others are created by me when I identify a request who need some dba work.
I'm pretty sure that some of them aren't used at all and I want to identify them.

For that task, I alter all my table & index setting MONITORING status.

After a while (several weeks) I watched about used status :

SELECT index_name, table_name, monitoring, used,
       start_monitoring, end_monitoring
  FROM v$object_usage
 ORDER BY index_name;

I notice that a lot of indexes have now a NOMONITORING status ...

I search to understand what happened and why about this status.

Except the regular ALTER INDEX index_name NOMONITORING USAGE query, what is likely to change this? Does anyone know a process (a standard Oracle's in-dictionary process) that remove the MONITORING status?


texts: Need to move TempDB to another drive. But its files cannot be dropped or shrunk

texts:
Today I had a task to move all TempDB files to a new, smaller drive (RAMdisk).
I analysed TempDB
files space usage - not all the space inside of its files was used so I decided that new drive is ok.
So I had to shrink all the 8 files to make them small enough. I tried to do it with:

USE [tempdb]
GO
DBCC SHRINKFILE (N'<logicalname>' , <size>)
GO

But it took a lot of time and eventually it didn't/almost didn't shrink files.

If I tried to empty a file :

USE [tempdb];
GO
DBCC SHRINKFILE (N'<logicalname>', EMPTYFILE);
GO

it still failed with a message:

DBCC SHRINKFILE: Page <pageNo> could not be moved because it is a work table page.

Then I found some posts about clearing the procedure cache, and the session cache:

DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
DBCC FREESESSIONCACHE
GO
DBCC FREESYSTEMCACHE ( 'ALL')
GO

and I cleared everything, but no luck.


texts:
We have a log backup job running every hour. The log backups are being taken for all the databases except one of them. For security purposes lets call this database FAILED_DB.
These are the things I have tried:

I looked at the history of the job and it has been failing since Friday.

In the job history, it states that Executed as user: ...tabase backup. [SQLSTATE 42000] (Error 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). However, FAILED_DB is set to Full recovery model & there has been a full backup taken last night.

In the SQL Server logs, it states BACKUP failed to complete the command BACKUP LOG FAILED_DB. Check the backup application log for detailed messages.

I also checked the Event Viewer, and it still states to check the backup application log for detailed message.

Has anyone ever faced this error before? And if so, what was your way to resolve this? Any pointers would be much appreciated.


texts: What is wrong with this statement? 'Unknown column'?

texts:
I'm not used to the 'as' keyword and I thought it would be a nice idea to use it.
So why do I get an 'unknown column' (eid) error? I use mysql...

select t1.index as eid, t1.col1 as col1, t1.col2 as col2,
t2.index, t2.col1, t2.col2,
t3.index, t3.col1, t3.col2
from table1 t1
left join table2 t2 on t2.index=eid
left join table3 t3 on t3.index=eid;

The full statement is too long to present here without boring everybody.
The column and table names are random.


texts:
I'm experiencing an annoying problem with a table named SysMessages in one of our databases.
When I run a select statement I don't get the contents of this table, but of the sys.sysmessages view in the master database.
A use [DBNAME] doesn't help, nor select * from [DBNAME].dbo.SysMessages.

I'm in the middle of a migration to a newer SQL Server version.
In SQL Server 2008 R2 I didn't encounter this problem with this database, but in SQL Server 2016 I'm not able to query my own SysMessages table.

Any help would be greatly appreciated.


texts: MySQL Config for server with 8gb RAM and 4 cpus

texts:
I have a server with 8gb ram and 4 cpus. I don't have enough time for testing and bench marking so I would like to get the advise of experts like you guys, let me know if something is wrong with my mysql config.

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
#connection setting
max_connect_errors      = 10
max_connections         = 400
wait_timeout            = 60
connect_timeout         = 10
interactive_timeout     = 60
#cache setting
query_cache_limit       = 2M
query_cache_size        = 50M
query_cache_type        = 1
table_open_cache        = 5000
thread_cache_size       = 100

#buffer sizes
key_buffer_size         = 20M
sort_buffer_size        = 2M
read_buffer_size        = 2M
join_buffer_size        = 2M

#tmpdir / temp table sizes
tmp_table_size          = 256M
max_heap_table_size     = 256M

#misc. settings
default-storage-engine  = innoDB
datadir                 = /var/lib/mysql
skip-external-locking
server-id               = 1
open-files-limit        = 65535
max_allowed_packet      = 64M

#innodb settings
innodb_data_file_path     = ibdata1:10M:autoextend
innodb_thread_concurrency = 0
innodb_buffer_pool_size   = 4G
innodb_log_buffer_size    = 8M
innodb_file_per_table     = 1
log_error               = /var/log/mysql/error.log
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time         = 2

#log-queries-not-using-indexes
[mysqldump]
quick
[mysql]
[isamchk]
key_buffer              = 16M
!includedir /etc/mysql/conf.d/


texts: Can SET IDENTITY_INSERT be allowed with less privileges than db_ddladmin?

texts:
I've inherited a system where the application is running under sysadmin account.
I limited this account to db_datareader + db_datawriter + EXECUTE on all database and set extended events session to catch the permission errors on the server.
It was very surprising to me to see some inserts failed even if the user is a member of db_datawriter and no granular restrictions on tables are applied.

Then I note that only inserts that set IDENTITY_INSERT failed.
The database in question is full of identity and there is too much SET IDENTITY_INSERT in their code.  The code means not only modules stored on server but also C# code.

To be able to set identity_insert user must own the table or have ALTER permission on the table. But the fact is that you CANNOT grant ALTER only on tables, I'm forced to grant ALTER on a whole database to this user
(I'll make this user member of db_ddladmin role that will add only 42 permissions(!!!)  vs 51 permissions that could be added to user permissions by granting ALTER on database)

I'm not interested in refactoring the database with sequence (server version is 2014 so I could do it theoretically) and I cannot just add execute as dbo to every sp that use identity_insert because there is also application code to rewrite, I just wonder WHY does Microsoft make such a strange permissions design that db_datawriter cannot set identity_insert?

Is there other way to make the user be able to set identity_insert that will add less permissions than db_ddladmin?

UPDATE

I tried the solution offered by LowlyDBA with synonyms:

create table dbo.t(id int identity);
go

alter schema sch transfer dbo.t;
go

create synonym dbo.t for sch.t;
go

set IDENTITY_INSERT dbo.t on;

This causes the error

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

The solution of Antoine Hernandez to grant the user ALTER only on schema seems to be le lesser of evils, so I accept it


texts: Best practice Azure Datawarehouse for less data-movements

texts:
I have a Table with a HASH on a Employee_Key, it also Hold a Date_From and Date_To.

To build the facts I need to change it on a by_date column.

But what is the best practice in Azure DataWarehousing with the less DataMoving as possible.

I've tried with a INNER JOIN to a Dates table that was in ROUND_ROBIN, or tried to put all the dates in a View (with a trick that only sys.objects is called), .. All working but ... not sure what is the better way on performance due to the Data-Movements between the 60 nodes ...

Any suggestions ?

Here some parts of the tables ...

CREATE TABLE [edw_hr_temp].[DIM_EMPLOYEE]
(
    [EMP_UID] [BIGINT] NULL,
    [EMP_BK] [CHAR](8) NULL,
    [EMP_DATE_FROM] [DATE] NULL,
    [EMP_DATE_TO] [DATE] NULL,
)
WITH
(
    DISTRIBUTION = HASH ( [EMP_BK] ),
    CLUSTERED COLUMNSTORE INDEX
)

1st Option a Real Table for Dates

CREATE TABLE [bi_msft].[ALL_DATES]
(
    [ADATE] [DATE] NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
)

2nd option is in the Stored Procedure user this as a subquery of store it in a #tempTable

  SELECT d
    FROM (SELECT d = DATEADD(DAY, rn - 1, @FROM_DATE)
            FROM (SELECT TOP (DATEDIFF(DAY, @FROM_DATE, @TO_DATE))
                         rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
                    FROM sys.all_objects AS s1
                         CROSS JOIN sys.all_objects AS s2
                   ORDER BY s1.[object_id]
                 ) AS x
         ) AS y;

  So basically my question is, when using the 2nd variant, would this
  query run on each node and so no data movement needed of will it run
  on one node and will all be moved....


texts: pgBouncer does not use all available CPUs

texts:
I have a server with multiple cores that is supposed to run pgBouncer (1.7).

It's working pretty well, but it utilises only one CPU core. I get pretty high latency values when I do a load test with pgbench, emulating several clients (27 ms without pgBouncer, and 62 ms with pgBouncer).

How can I make pgBouncer use all available CPU cores?


texts: Adding unsigned 256 bit integers in PostgreSQL

texts:
I was wondering if there would be any way to do the following in PostgreSQL:

UPDATE cryptotable SET work = work + 'some big hexadecimal number'

where work is an unsigned 256 bit number. Right now my column is a character varying(64) column (hexadecimal representation) but I would be happy to switch to another data type if it lets me do the operation above.

If it's not possible with vanilla PostgreSQL, are there extensions that could help me?

(I also posted this to pgsql-hackers so check out the thread there for more ideas.)


texts:
I am running SSMS as a different user from my local machine and it works fine.

When I try to do the same, even using my own credentials, on server01 I get the following error:
access is denied

I am currently in the Administrators group in that server:

The way to run ssms as a different user is:

1- right clink on the ssms icon if it is already running otherwise follow from 2
2- witht eh shift key pressed right click the ssms icon and it will give you the options as shown on the picture below.

considering the password is correct and I am sysadmin what could be missing?


texts:
I am using mariadb 10.4 with nignx  with joomla 4 on Debian 8 system.

Is it possible for a table that is already created with records in a working database to be moved on memory?

I find only tutorial to create the table from scratch not for changing it after it is created.

Is it possible to have some records on the disk and the rest on the memory?


texts: SQL Server alerts not sending notification emails to the operator

texts:
I have setup few SQL Server alerts and added Operator to notify if any alert occurred.

I just noticed that couple of alerts did occurred but we didn't receive any alert email. I did send a test notification email to the operator and it worked and we received email.

Any idea why we are not getting email alerts when actual alert occurred?

Nothing in Database Mail Log.

The SQL Server is part of the Availability Group and I'm only providing email name in operator not doing any 'Net send address' or 'Pager e-mail name'. Also all the alerts are listed on the Properties of the Operator.

Alert History got 'Number of occurrences :266' and Date of last alert is yesterday 29th June but I didn't receive alert email at all. Even Operator history got no history at all (Never e-mailed).


texts: How to dynamically generate rows based on value in a column

texts:
I'm attempting to write a query to denormalize some data for integrating to our data warehouse, specifically around generating some time slots.

In my source system, I have table that has a start time and and end time and then a column that designates how many appointment slots are available per 15 minute interval.

Example:

    Location | Start Time | End Time | Appointment Slots
    A        | 9:00 am    | 5:00 pm  | 2
    B        | 9:00 am    | 5:00 pm  | 1

I have already been able to generate a list of 15 minute blocks using a CTE. However, each 15 minute slot just says that 2 slots are available for location A and 1 slot is available for location B. What I'd like is to be able to generate 2 rows per 15 minutes for location A and 1 row per 15 minutes for location B.


texts: Is it possible to save DMV's in SQL server database backup, which can be restored on different server, I mean with master db details( views and all)? If not, what's the best way to maintain 2 environment( one for Performance Testing and replica of it for analysis), so that database which has been used for testing can be analysed by team for optimizing queries without stopping further test execution window.


texts: Transposing repeated rows of datetime into columns

texts:
I am working with a dataset of inpatient data in SQL Server 2017. The fields contain anonymized ID, admission date, discharge date and other relevant fields. Since some of the patients are readmitted, the ID field is not unique. The table looks like this:

ID       AdmDate1      DischDate
10001   2012-10-16    2012-10-26
10001   2014-06-15    2014-06-18
10001   2014-12-21    2014-12-29
10002   2013-02-14    2013-02-20
10003   2013-01-23    2013-01-31
10004   2012-11-15    2012-11-19
10004   2014-09-26    2014-10-06
10005   2014-12-12    2014-12-23
10006   2013-10-23    2013-10-28

Since I want to calculate readmission rate and intervals between readmissions, I want to create a table like this:

ID       AdmDate1     AdmDate2    AdmDate3   DischDate1 DischDate2 DischDate3
10001   2012-10-16   2014-06-15  2014-12-21
10002   2013-02-14   None        None


texts: Deny access to information schema in SQL Server

texts:
I am looking for the best way to disable access to the sys.tables / Information Schema for a user / group in SQL Server.

I found this thread from 2008

It shows a way how to deny access on [sys].[something] like so:

 DENY SELECT ON [sys].[columns] TO DenySystemTableSelectRole
 GO
 DENY SELECT ON [sys].[tables] TO DenySystemTableSelectRole
 GO
 DENY SELECT ON [sys].[syscolumns] TO DenySystemTableSelectRole
 GO
 DENY SELECT ON [sys].[sysobjects] TO DenySystemTableSelectRole
 GO

But no way how to disable access on the Information Schema:

DENY SELECT ON INFORMATION_SCHEMA.TABLES To DenySystemTableSelectRole

This seems not to work.

How can I disable access to information_schema?

And is there an easier way disable access to all sys / information_schema?

Update:
Actually I can not run both ot the following statements:

DENY SELECT ON [sys] TO reducedDBO
GO
DENY SELECT ON INFORMATION_SCHEMA To reducedDBO
GO

I tried to run them on the specific DB where the User exists, and I also tried on the "master".

I still can run:

 SELECT * from
 INFORMATION_SCHEMA.TABLES

-->still returns results

 SELECT * from
 sys.TABLES

-->no results anymore

Including SCHEMA:: in the query made it possible to create the securables

DENY SELECT ON SCHEMA::[sys] TO reducedDBO
GO
DENY SELECT ON SCHEMA::INFORMATION_SCHEMA To reducedDBO
GO

But now I still can select all the information from the DB.

I had a look at the "Securables"-Tab in the users Property-window in Management Studio 2008, it looks like this:

Entry that does block the selecion of sys.tables

Schema:sys, Name:tables, Type:View

Permissions for sys.tables: Permission:Select, Grantor:dbo, Deny is checked

Entry that do not block any selection

Schema:, Name:INFORMATION_SCHEMA, Type:Schema

Permissions for INFORMATION_SCHEMA: Permission:Select, Grantor:dbo, Deny is NOT checked (I tried to check it, but no chance..)

Permission:Select, Grantor:INFORMATION_SCHEMA, Deny is checked

I tried to set the permissions over the GUI, but then I get the same error that setting permissions would be possible only on the master DB.
But I not have the user/login added to the master DBs security.

Solution:

The only way I could make the deny work for the information_schema was to add the user to the master-db and run the deny select on the master:

DENY SELECT ON [sys].[tables] TO reducedDBO
GO
DENY SELECT ON INFORMATION_SCHEMA.TABLES To reducedDBO
GO

And as in this code, it can only be executed for single tables.


texts: Publishing database changes with SSDT that include altering data types of columns

texts:
I have a SQL Server Data Tools (VS2012) project that's published automatically during the build process. A column was recently updated from an int to decimal(18,4). As a result of this change, the publishing fails with the error

  (49,1): SQL72014: .Net SqlClient Data Provider: Msg 50000, Level 16,
  State 127, Line 6 Rows were detected. The schema update is terminating
  because data loss might occur. (44,0): SQL72045: Script execution
  error.  The executed script: /* The type for column QuantityReceived
  in table [dbo].[Reconciliation_Receiving] is currently  INT NOT NULL
  but is being changed to  DECIMAL (18, 4) NOT NULL. Data loss could
  occur.
  */

  IF EXISTS (select top 1 1 from [dbo].[Reconciliation_Receiving])
      RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT An error
  occurred while the batch was being executed.

I understand why I'm receiving that error, and I know it could be resolved by disabling the "Block Incremental Deploy if Data Loss May Occur" flag. However, there's very strong opposition to disabling that feature, so it's not going to be an acceptable solution.

The only other solution I can think of is to do the following:

Make a temporary table and copy the existing table's contents into the temporary table
Truncate the existing table
Let SSDT update the data type
Fill the data back in from the temporary table

That seems horribly clunky and inefficient, though.

Is there a better alternative?


texts: Mongodb sharded cluster backup user issue

texts:
We're using a simple python script to backup our mongodb cluster. the script connects to each mongod instance via ssh, and performs mongodump, all without requiring user/pass

ever since we started using the mms monitoring agent, and thus creating an administrative user on each shard for the mms agent to connect to, we're not able to use the mongodump without credentials.

out problem is that all our users are configured on the mongos instances and not directly on the shards, so we cannot perform the mongodump.

One last thing, Is it possible to disable authentication for a specific shard in order to create/remove users from it?

do you have any ideas?


texts: What is the Postgres's equivalent/closest lock of Microsoft SQL Server's IX (Intent Exclusive) lock?

texts:
In Microsoft SQL Server, the code below is used to get the IX (Intent Exclusive) lock:
SELECT request_mode FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID() AND resource_associated_entity_id = OBJECT_ID(N'dbo.cars');

In PostgreSQL, pg_locks table is available to tell locks information. However, there are many types of locktype, which are:

relation, extend, frozenid, page, tuple, transactionid, virtualxid, object, userlock, or advisory

Which types of locktype from pg_locks is equivalent to IX (Intent Exclusive) lock from Microsoft SQL Server?
If locktype is the wrong column to look at pg_locks, please advise which column in pg_locks should I use?
Thanks


texts:
With this SQL-query, I am able to obtain the count(*) of items in subcategory, but I want to be able to SUM total items, from both main (parent) category and subcategory (child), how can this be achieved?

SELECT parent.name,
COUNT(sub.item_id) AS total
FROM categories parent
LEFT JOIN subcategories child ON parent.category_id = child.category_id
LEFT JOIN item_subcategory sub ON child.subcategory_id = sub.subcategory_id
LEFT JOIN items i ON sub.item_id = i.item_id
GROUP BY parent.name;

EDIT: http://sqlfiddle.com/#!9/7ec5b8/1

Note, I am using the latest MySQL on my PC, but the one supported there is 5.6 at highest, for some reason I keep getting syntax error.

EDIT2: This QUERY currently COUNTs ITEMS in subcategories, is IT possible to within the same query count for items in main categories and then SUM these two counts.


texts: Large Fact table and partitioning key dilemma

texts:
I have fairly large fact table (2 billion records, approx 120 GB). This table is not partitioned and the queries are very slow to respond. I am planning to partition the table and indexes.
The table has an identity column which is the primary key and has a clustered index on it. There are other non-clustered indexes on it but I won't go in the details much here. The column, I am trying to partition is not part of primary key but is not null and this is creating a slight dilemma for me. I have two options.

I add this column as part of primary key i.e. composite primary key. Since the first column is identity, the combination would always be unique which means I don't have to worry about the applications accessing the table. The clustered index will automatically be partition aligned and other indexes can also be partition aligned.
The seconds option is to remove the clustered index on the identity column and make it unique non clustered. This index cannot be partitioned aligned since partition key is not part of it and hence would have to sit on one drive. Then create a clustered index on the partition key column which can be partition aligned and so all the other non clustered indexes.

Our DBA is in favour of second option since he doesn't want to change primary key. I am worried about the performance hit in option 2 since the index is not partition aligned.

I would appreciate any feedback plus any other method you would have used in such situation.


texts:
I realised today that my index file(which is about 250GB) is smaller than yesterday for about 7GB.

Everything, I done in the meantime, was index rebuild.

Is it possible that index rebuild is the reason that file became smaller?

Tnx.


texts:
I have enabled automatic login in pgAdmin and would like to disable it to make it possible to connect to the servers using different roles.

How do I do this?


texts: Microsoft SQL Server 2008+ TIMESTAMP problem

texts:
I'm currently facing a Problem, where the SQL Server refuses my Timestamps.
I am accessing the Server via EclipseLink (Java) but this shall not be the problem, as i can test the queries on the Server and receive the same results.

i created a small sample to reproduce this problem :

CREATE TABLE testDateTimes (
    id int not null,
    ts datetime not null default CURRENT_TIMESTAMP
)

insert values (one value generated by the server)

insert into testDateTimes (id) VALUES (1)

querying the values from the datetime

select * from testDateTimes;

returns me:

1   2017-05-24 10:45:17.267

now trying to manually insert a new datetime:

insert into testDateTimes (id, ts) VALUES (2, '2017-05-24 12:34:56.789')

which fails with error message: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

i also noticed that this would be valid too:

insert into testDateTimes (id, ts) VALUES (2, '20170524 12:34:56.789')

but i can't change the way/format how EclipseLink sends the Data.

if i transform the datetime to another format:

insert into testDateTimes (id, ts) VALUES (2, '24.05.2017 12:34:56.789')

it is working...

NOTE: i am sure there are no errors in the DateTime itself (this is verified by Java)
How can i specify to take the correct format? standard and unique SQL/ISO date format instead of the one with the Dots.?
Note that i cannot do this in every query. It should be a default setting from the Server.

(i might be wrong, but this problem seems to pop up after moving from MSSQL2000 to MSSQL2008)

when i execute

set language 'croatian'

sys.syslanguages says croatian incldues dateformat ymd, the queries work as expected.

also
    SET DATEFORMAT ymd;

does solve the problem, but it is not a permanent solution.

update sys.syslanguages
set dateformat = 'ymd'
where name = @@LANGUAGE

would solve the problem, BUT this is prevented by the server!

ad hoc updates to system catalogs are not allowed


texts: Table Value Constructor as Procedure Parameter

texts:
I can find information on creating procedures with table-valued parameters, and information on table literals, but I can’t find any mention of the two combined.

Suppose I have a procedure defined something like:

create type info as table(stuff int, more text);

create procedure test(@id int,@data info) as
begin
    -- etc
end;

I would like to execute the procedure using a table literal instead of assigning values into a variable. I would like to be able to do something like this:

execute test 42,(values((1,'one'),(2,'deux'),(3,'drei')) as (stuff,more));

It doesn’t seem to work.

I know I can create a variable and do it that way, and that is the method generally used in examples.

Is there a way to use a a syntax similar to the above? If so, how?

I want to this partly for the exercise, but more importantly, it seems to me that since table literals are part of the language, it should be possible to use them wherever tables are required.

In this regard I think there are gaps in the implementation. Finally, I think that using a literal is sometimes simpler and more intuitive, so creating a variable just to pass information through is a kludge.


texts:
Hello I've messed up a certain db migration. I'm more of a postgres person than oracle, and I googled for a while and haven't find a way to do something like alter if exists.

Basically what I need is to drop db columns if they exist, if they don't exist do nothing.

Example :

ALTER TABLE MY_PROPERTY_LOCK DROP COLUMN PROP

Fails if the PROP doesn't exist.

Edit:

Tried this, among other things :

declare p_count NUMBER;

select count(1) int p_count
from ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'MY_PROPERTY_LOCK'
and COLUMN_NAME = 'PROP';

IF p_count = 1 THEN
    ALTER TABLE MY_PROPERTY_LOCK DROP COLUMN PROP
END IF;

This is the error I get :

begin function pragma procedure subtype type <an identifier>
   <a double-quoted delimited-identifier> current cursor delete
   exists prior
The symbol "begin" was substituted for "SELECT" to continue.
ORA-06550: line 3, column 21:
PLS-00103: Encountered the symbol "P_COUNT" when expecting one of the following:

   , from into bulk
The symbol "," was substituted for "P_COUNT" to continue.
ORA-06550: line 10, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:

   ( begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier>
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:


texts:
I would like to know if Too many Dormant Users/sessions on a SQL Server instance can force SQL server to not to create new connections or give connection denied error to the application which is trying to connect to server. Or what is the harm of having too many (70-100) dormant sessions/users?

Thanks in advance.


texts: ORA-12631 using Database Links with Oracle DBMS 19c on Windows Server 2019

texts:
We had a set of long-time Oracle 12c Database installations (multiple instances on separate VMs) on Windows Server.  Over the past year we transitioned to Oracle 19c on Windows Server 2019.  We found that database links that worked in the 12c environment did not work in the 19c environment; a query referencing such a link would fail with the error ORA-12631: Username retrieval failed.  SQLNET traces showed
naun5validate: SPP is NEGOTIATE

naun5validate: SSPI: 0x8009030c error in AcceptSecurityContext


texts: Installing PostgreSQL 9.2 and PostGIS 2.0 on Ubuntu 12

texts:
At the current moment, March 4th 2013, can PostGIS2.0 be install with PostgreSQL 9.2?

I checked their website and to my understanding it is not possible...

I hope that's not the case. Can anyone tell me and point out the instructions on how to install PostGIS 2.0 on PostgreSQL 9.2 on Ubuntu?


texts: Inserting Serbian Latin and Cyrillic characters

texts:
I am using SQL Server 2012 Express as part of my asp.net development workstation. Database and table collation is set to Latin1_General_CI_AI. Attribute types are nvarchar(max) and varchar(max).

When I pass a string containing Serbian Latin characters žđšćč žđćčžđšćčŽĐŠĆČ, the model from my asp.net application passes the string as it is, but the database is not accepting it, writing ždšcc ždccždšccŽÐŠCC into the table instead.

I also tried with Cyrillic string, but ????? ????? is written into database.

I am using MVC5 and EF6. I don't have parameters or datareader. I am just passing the model into the framework, and EF is doing everything else.


texts: Search results mismatch after postgresql update

texts:
Today we had to update the PostgreSQL version on our database servers. Before update the version was 9.3.4 and the new one is 9.3.6. Despite the small difference in the database version and the absolutely identical configs, the search results changed drastically. The number of results decreased many times for some search queries. The rows added today (after update) were found each time, while some older ones cannot be found by a given phrase.

We use tsvector to perform full text search.

Any ideas what might be causing this?

Example query:

SELECT * FROM content, to_tsquery('bulgarian_utf8', '{keyword}') q
WHERE (tsv_title @@ q OR tsv @@ q) ) AS c


texts: Is there a way I can shorten the below query into a single query?

texts:
I have the below query. IS there a way I can put this into one single query? If not, can I shrink it further anyway? Please advice.

    DECLARE @highRegion TABLE(regionId INT, countR INT)
    DECLARE @lowRegion TABLE(regionId INT, countR INT)
    DECLARE @midRegion TABLE(regionId INT, countR INT)

    INSERT INTO @highRegion
    SELECT c.fRegionID,
           COUNT(1) AS VALUE
    FROM   census.Country c
           INNER JOIN census.IncomeGroup ig
                ON  c.fIncomeGroupID = ig.IncomeGroupID
    WHERE  ig.Name IN ('High income: nonOECD', 'High income: OECD')
    GROUP BY
           c.fRegionID

    INSERT INTO @lowRegion
    SELECT c.fRegionID AS VALUE,
           COUNT(1)
    FROM   census.Country c
           INNER JOIN census.IncomeGroup ig
                ON  c.fIncomeGroupID = ig.IncomeGroupID
    WHERE  ig.Name = 'Upper middle income'
    GROUP BY
           c.fRegionID

    INSERT INTO @midRegion
    SELECT c.fRegionID,
           COUNT(1) AS VALUE
    FROM   census.Country c
           INNER JOIN census.IncomeGroup ig
                ON  c.fIncomeGroupID = ig.IncomeGroupID
    WHERE  ig.Name IN ('Lower middle income', 'Low income')
    GROUP BY
           c.fRegionID

    SELECT r.Name,
           r.NoOfCountries,
           h.countR AS HIGH,
           l.countR AS LOW,
           m.countR AS mid
    FROM   census.Country c
           INNER JOIN @highRegion h
                ON  c.fRegionID = h.regionId
           INNER JOIN @lowRegion l
                ON c.fRegionID = l.regionId
           INNER JOIN @midRegion m
                ON c.fRegionID = m.regionId
           INNER JOIN census.Region r
                ON  c.fRegionID = r.RegionID


texts: UOW's: Relating UOW's listed in MSDTC to SQL Server info

texts:
We have some active transactions showing up in MSDTC that have been there for a very long time and don't go away. They're not orphaned or in-doubt, just normal-looking active transactions.

We're trying to figure out what they are and why they're there.

To that end, I'm trying to find a way to map one of these active transactions that are being presented in MSDTC Transaction List (Component Services -> My Computer -> Distributed Transaction Coordinator -> Transaction List) to any information at all in MS SQL Server.

Obviously, all I have to work with is a UOW ID.

After several hours of research, I can't find anything outside of sys.dm_tran_locks that would have this info.  The problem I'm having with sys.dm_tran_locks is that my transactions do not show up, presumably because they don't currently hold any locks.

Other important info:
SQL Server 2005. (Yes, 2005 :(  )
Again, nothing is "orphaned" or "in doubt". The transactions just show as active, but they stay there forever.
There is no clustering or anything else exotic. It's all very plain-vanilla SQL Server 2005 standard edition.

EDIT: I should also note that I tried:

* SP_WhoIsActive (both with default settings and with more info gathering turned on)
* sys.dm_tran_locks
* sys.dm_exec_sessions
* sys.dm_exec_requests.
* sys.dm_tran_database_transactions
* sys.dm_tran_database_transactions
* sys.dm_tran_active_transactions

I'm beginning to think you just can't get there from here ;-)
Again, I want to associated a UOW in MSDTC to a UOW in SQL Server where, apparently, the distributed transaction is still active but holding no locks.

As requested, here is a screenshot of MSDTC:


texts:
I was wondering if there is any information on what scenarios might cause a DB Mirror to have to be recreated?

I am attempting to research how sturdy the technology is and under what scenarios do we have to proceed with caution such as long running maintenance tasks or large bulk transactions.


texts: How to return all the unique column values on a per column basis for all metadata tables?

texts:
assume multiple tables of various sizes

table one

col one, col two, col three,...
a,d,j
a,e,k
a,e,m
b,e,o
c,e,o
d,e,o
f,e,o

return (preferably in this format)

col one, col two, col three
a,d,j
b,e,k
c, ,m
d, ,o
f, ,

the idea is to be able to give a summary of the data to the users to check data quality prior to a migration


texts: Slow query that includes a WHERE clause with SUBSTRING

texts:
I have the following query in SQL Server 2016, as there is no code for the "enter code here" column, I had to do a search with SUBSTRING. But as expected it is slow, the query ends in 00:03:37.

with cte as
(
    -- De Incidente para Requisição
    select
           cdchamado                       AS [Chamado]
          ,'De Incidente para Requisição'  AS [Tipo de Alteração]
          ,dtacompanhamento                AS [Alterado Em]
    from
        [dbo].[hd_acompanhamento]
    where
        SUBSTRING(dsacompanhamento,45,36) = 'incidente para requisição de serviço'
    union
    -- De Requisição para Incidente
    select
           cdchamado                       AS [Chamado]
          ,'De Requisição para Incidente'  AS [Tipo de Alteração]
          ,dtacompanhamento                AS [Alterado Em]
    from
        [dbo].[hd_acompanhamento]
    where
        SUBSTRING(dsacompanhamento,45,36) = 'requisição de serviço para incidente'
)
select * from cte

I thought about making a temporary table and inserting only the data I need, so this table would update itself through a job in SQL Server Agent. But I do not know if it is the best solution.


texts:
I have a simple SSIS package that loads a dummy file that contains 100000 rows.  Each row is around 4k long, one int column and one long text column.  I'm trying to test TF610 when loading this data into a table with a clustered index.

In my SSIS package, my Control Flow has a Execute SQL task to enable TF610, then on success go to my Data Flow Task which loads the flat file into the table.  Both the Execute SQL and OLE DB Destination use the same Connection.

If I start a profile while running the SSIS package, and watch the commands, I can see DBCC TRACEON(610) executed then the INSERT BULK operations begin to fire.  They both are using the same PID, so I'm assuming it's the same session.

When I check the log record length though, the insert is NOT being minimally logged.

If I enable TF610 globally and run the same SSIS package though the transaction is minimally logged.

I must be doing something wrong when turning on the TF610 in my SSIS package but I can't figure out what...


texts:
We are having memory pressure issues (RESOURCE_SEMAPHORE waits) on one of our servers but there are very few active connections going on at one time. Logically, it wouldn't seem like sleeping connections would hold on to memory but I'm wondering if they actually do?  IderaDM shows several sleeping connections with high Memory Usage.

This is a SQL 2008 SP3 server with 27 Gb of memory.  There's typically only two to 10 active calls going on at any one moment and a good portion of them are having resource_semaphore waits.  This server holds our third party DBs so we can't change most of the calls/procs.

So, is it possible that sleeping connections are holding onto memory or should I be looking elsewhere?


texts: Fastest way to move Collection from Sharded MongoDB Cluster to a another Sharded MongoDB Cluster

texts:
I have to move a MongoDB Collection (Size: 1TB+) from Sharded MongoDB Cluster to another[5 shards on both clusters]. During this operation, it is assumed that the accompanying program will be offline, so no need to worry about inconsistent data, but I need to minimize the downtime.

I've tried testing it with mongodump and mongorestore, but looks like that is taking ages to finish.

Please share your experiences for such kind of scenarios.

Note: Cluster has only one collection, so I am open at cluster level sync as well.


texts: Having some trouble figuring out how to alter the core usage on SQL Server 2012 Enterprise. On a server with 16 cores, is there any way to alter which ones will actively use the SQL database, and not on others?


texts:
I have a database containing three tables: tbl_database (main table is the primary), tbl_cmdatabase and tbl_blacklist. The three tables share the same structure but differ in the number of records that can be. This is the SQL structure, that basically is the same:

CREATE TABLE IF NOT EXISTS `tbl_ndatabase` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_telefonica` int(11) NOT NULL,
  `number` varchar(11) COLLATE utf8_spanish2_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci AUTO_INCREMENT=1 ;

Table tbl_database contains ~194 074 records and they should continue to grow in large numbers, table tbl_cmdatabase contains ~45,742 records and similarly can increase but perhaps not so exaggerated as tbl_database but would increase and finally the table tbl_blacklist having ~92,038 records. I am developing an application where I need to verify that the number that I will insert into table tbl_database is not in tables tbl_blacklist and tbl_cmdatabase so I need to check for every row looking for the number (this queries are slow in this amount of records). What I need to do is to optimize some tables or change the structure or not because when I perform these queries the server tends to fall as the shared hosting does not support large queries, anyone can help me with this issue? Any suggestions?

Edit: Added a file for test data


texts: MongoDB: Lost dry run election due to internal error

texts:
I am trying to solve a Replication issue here in MongoDB 4.2, but I can no longer get my Primary server back to primary, it always stays in SECONDARY mode. I've tried several types of solutions, like this Or here

But without success.

In the logs, I came across these messages:

 Scheduling remote command request for vote request: RemoteCommand 729 -- target:mongo2:27021 db:admin cmd:{ replSetRequestVotes: 1, setName: "MyReplicaSet", dryRun: true, term: 2, candidateIndex: 0, configVersion: 125668, lastCommittedOp: { ts: Timestamp(1570799675, 1), t: 2 } }
 2019-10-11T11:17:25.041-0300 I  ELECTION [replexec-2] VoteRequester(term 2 dry run) received an invalid response from mongo2:27021: NotYetInitialized: no replset config has been received; response message: { operationTime: Timestamp(0, 0), ok: 0.0, errmsg: "no replset config has been received", code: 94, codeName: "NotYetInitialized", $clusterTime: { clusterTime: Timestamp(1570799675, 1), signature: { hash: BinData(0, 0000000000000000000000000000000000000000), keyId: 0 } } }
 2019-10-11T11:17:25.041-0300 I  ELECTION [replexec-2] not running for primary, we received insufficient votes
 2019-10-11T11:17:25.041-0300 I  ELECTION [replexec-2] Lost dry run election due to internal error

How to fix this? Possible a bug? I have tried 4.0.12 and It is work fine


texts: Error Unknown command '\U' when restoring MySQL dump on Windows

texts:
I am updating software that used MySQL 5.4.3 in its old version, and now uses MariaDB 10.2.11.
So, I dumped the old data with that command:

mysqldump -uroot -p --no-create-info --skip-triggers --complete-insert theDBName -r C:\Users\myUserName\theDBName_data.dump

Then I uninstalled MySQL and replaced it by MariaDB. After that I created the database and imported the new data structure (a few columns have been added with default values).

The issue occurs when restoring the old data:

mysql -uroot -p --default-character-set=utf8 theDBName
Enter password: ****************

MariaDB [theDBName]> SET names 'utf8';
MariaDB [theDBName]> SOURCE C:\Users\myUserName\theDBName_data.dump
ERROR: Unknown command '\U'.
ERROR: Unknown command '\a'
ERROR: Unknown command '\a'
ERROR: Failed to open file 'C:\Users\myUserName\theDBName_data.dump', error: 2

Restoring the exact same dump with that procedure works well on Linux, but doesn't on Windows.

Thanks for your help.


texts: JDBC - maximum allowable cursors - how do I see my open statements?

texts: I've got some resource leaking and I'm trying to find what's causing them. I think I have some statements or resultsets that aren't being closed properly. Is there any way to see all of the cursors that my JDBC connection currently has open? I am using Eclipse.


texts: How can I use Oracle's CONNECT BY PRIOR to only get specific revisions of hierarchical records?

texts:
I've got a table schema in Oracle 11 which is storing records that are linked together. The table schema is roughly like this

CREATE TABLE "SOMEDATA" (
    "ID" INTEGER NOT NULL,
    "ITEMNAME" VARCHAR2 NOT NULL,
    "LINKLEFT" VARCHAR2 DEFAULT NULL,
    "LINKRIGHT" VARCHAR2 DEFAULT NULL,
    "CREATE_TIMESTAMP" TIMESTAMP NOT NULL,
    "VALUE1" VARCHAR2 DEFAULT NULL,
    "VALUE1" VARCHAR2 DEFAULT NULL
)

The purpose of this table is to store timestamped records which may link to other records, similar to a hierarchy. My challenge is build a query that at any given specific timestamp, and for a given itemname, recursively fetch the latest versions of the rows that chain to the right of the item. E.g. in the following data:

+----+----------+----------+-----------+---------------------+------------+----------+--+
| Id | Itemname | Linkleft | Linkright |  create_timestamp   |   value1   |  value2  |  |
+----+----------+----------+-----------+---------------------+------------+----------+--+
|  1 | item1    | (null)   | item2     | 23/08/2014 00:00:00 | Test       | Data     |  |
|  2 | item2    | item1    | item3     | 23/08/2014 00:01:25 | Some       | More     |  |
|  3 | item3    | item2    | (null)    | 23/08/2014 00:05:00 | Even       | MoreData |  |
|  4 | item2    | item1    | item3     | 24/08/2014 00:00:00 | Changed    | Data     |  |
|  5 | item1    | (null)   | item2     | 25/08/2014 12:37:02 | New        | Better   |  |
|  6 | item3    | item2    | item4     | 27/08/2014 00:00:00 | Lipsum     | Lopsum   |  |
|  7 | item4    | item3    | (null)    | 27/08/2014 00:01:00 | Additional | Stuff    |  |
+----+----------+----------+-----------+---------------------+------------+----------+--+

For the query ITEMNAME='item1' and CREATE_TIMESTAMP=23/08/2014 14:58:59, I need to return records #1,2 and 3. This is because these are the latest linked records that link to the latest record for 'item1' at that particular point in time. However, for the query ITEMNAME='item1' and CREATE_TIMESTAMP=01/01/2015 12:00:00, I need to return records #5,4,6 and 7, because at that time, these records were the latest versions.

I've been playing about with Oracle's CONNECT BY PRIOR to build something like

SELECT * FROM "SOMEDATA" START WITH "ITEMNAME"="item1" CONNECT BY PRIOR "LINKRIGHT"="RIC"

This obviously chains the records together, but pays no attention to timestamps and returns all values for rows which match.

It seems that hierarchical records is a nicely solved problem, but not in the case where you want to also store the historical values for records in the hierarchy and query for any time. Is this sort of thing possible in this table structure, or am I heading down the wrong track entirely?


texts: Optimizing a CTE query - overhead in inserting into a new table from select statement

texts:
I have two tables with the same structure like so (let us call them initial_table1 and initial_table2):

tempid  name    num_records
1       a       2
2       b       2
3       c       2
4       d       2
5       e       2

I want to perform two operations to arrive at my desired end result (lets call it final_table).

Perform a union on intial_table1 and initial_table2.
Duplicate each row in the resulting table by num_records.

I achieve this by creating an auxiliary table of numbers and joining on the range over 1 and num_record. The union is straightforward creating a temporary table on the fly using UNION ALL. What I have shown below is the statement I have come up with:

WITH
    N1 AS (SELECT N1.[n] FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS N1 ([n])),
    N2 AS (SELECT L.[n] FROM N1 AS L CROSS JOIN N1 AS R),
    N3 AS (SELECT L.[n] FROM N2 AS L CROSS JOIN N2 AS R),
    N AS (SELECT ROW_NUMBER() OVER (ORDER BY @@SPID) AS [n] FROM N3) -- table of numbers going from 1 to 100 million sequentially
SELECT * INTO [dbo].[final_table] FROM
    (
    select * from initial_table1
        union all
    select * from initial_table2
    ) AS tmp
INNER JOIN (
    SELECT
        [n]
    FROM
        n
    ) AS numbers
ON
    numbers.[n] BETWEEN 1 AND [tmp].[num_records];

This works well but the query times are quite high. The execution plan says the table insert step takes the highest toll (91% of cost). Both initial tables have about 100,000 rows. Can this query be optimized in some way or is the CTE approach not the most optimum in this case? I am using Microsoft SQL Server 2012


texts: Sql Server view with very limited access

texts:
I have a requirement to limit a view in Sql Server 2008 R2 that user:

user has access to select from view.
user has not access to copy the result from select statement.
user has not access to join the view.

I have to use Sql Server view, is it possible?


texts: How do transactions affect PostgreSQL's REFRESH MATERIALIZED VIEW CONCURRENTLY command?

texts:
PostgreSQL 9.4 supports REFRESH MATERIALIZED VIEW CONCURRENTLY.

I'm not sure, but I think the REFRESH command is viewed as DDL by PostgreSQL, and PostgreSQL wraps all DDL within transactions so that simultaneous transactions can't see the effects of DDL commands issued by other concurrent transactions.

I verified this works by opening two transactions, inserting data and then refreshing the materialized view in one of the transactions, and then querying the materialized view in both transactions. Only the first transaction saw the updated materialized view.

There's a few edge cases I'm curious about though--I tried testing these, but my dataset was too small so the refresh finishes too fast:

What happens if two transactions try to refresh a materialized view in parallel? Is this allowed or will it throw an error?
If allowed, will the refreshes be executed in parallel or serial? Will they slow each other down? Which result will ultimately 'win'?
Let's say I have an application with many users. Can this create problems where the application opens a transaction for the first user, realizes the materialized view is missing an entry so it refreshes the materialized view before returning the data, and then another user's transaction comes along before the first refresh finishes and does the same thing. Regardless of whether they're in parallel or serial, it seems this could create a temporary backlog effect that quickly stacks up where the refresh commands are being emitted faster than they're processed. As soon as one of the refreshes finishes first one finishes, any new transactions won't try to refresh it, but this could still take a long time. Especially if concurrent refreshes slow each other down, this could get painful quickly. Do I instead need to manually set a global flag in my app indicating that a refresh is currently happening so that this scenario doesn't happen?


texts: Does MariaDB support schema changes inside a transaction?

texts:
MySQL doesn't support schema changes, inside a transaction so there's no way to roll back after during a failed migration involving schema changes.

This capability is supported by Postgres and SQLite. Any chance MariaDB supports it?


texts: Action_id group by for last 3 days in 3 different columns


texts: Updating a WHERE clause to check if a value is NOT in a separate table

texts:
I have a query which uses a WHERE clause, and I happen to use the exact same WHERE clause in many queries on this table (et al).

The query is:

SELECT
    DATENAME(DW, [AtDateTime]) AS [Day of Week]
    ,COUNT(*) AS [Number of Searches]
    ,CAST(CAST(COUNT(*) AS DECIMAL(10, 2))
         / COUNT(DISTINCT CONVERT(DATE, [AtDateTime])) AS DECIMAL(10, 2))
       AS [Average Searches per Day]
    ,SUM(CASE WHEN [NumFound] = 0 THEN 1 ELSE 0 END)
       AS [Number of Searches with no Results]
    ,CAST(CAST(SUM(CASE WHEN [NumFound] = 0 THEN 1 ELSE 0 END)
         AS DECIMAL(10, 2)) / COUNT(*) AS DECIMAL(10, 4))
       AS [Percent of Searches with no Results]
FROM [DB].[dbo].[SearchHistory]
WHERE
    [CustomerNumber] <> '1234' AND [CustomerNumber] <> '5678'
GROUP BY DATENAME(DW, [AtDateTime]), DATEPART(DW, [AtDateTime])
ORDER BY DATEPART(DW, [AtDateTime])

The part I wish to change is the WHERE clause, to instead allow me to use a table so that if I have to add a customer number to be ignored, I don't have to update all my queries. (And there are quite a few queries that have this same WHERE clause.)


texts: Dropping a column generates an error: SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'

texts:
When I run the following statement ALTER TABLE MyTable DROP COLUMN MyIdentityColumn I receive the following error message:

  SELECT failed because the following SET options have incorrect
  settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'. Verify that SET options are
  correct for use with indexed views and/or indexes on computed columns
  and/or filtered indexes and/or query notifications and/or XML data
  type methods and/or spatial index operations.

Firstly I'm confused because I'm not running a select statement. The column is an identity column which might explain the reference to computed columns but there are no keys, constraints, triggers or indexes on this table (the table was created from another table with a select * from SourceTable into MyTable).

Secondly, what do I need to do to drop this column? Even deleting from the SSMS menu fails with the same message.


texts: Database Tuning Advisor can't find tables across two databases

texts:
I've got a Python script which accesses two SQLServer 2008 databases. If I run it and take a trace to capture all events that occur as a result of the script, and then put that trace through the Database Tuning Advisor it has the error message "invalid object name" about many of the tables in one of the databases.

I have selected both databases on the first tab of the tuning advisor. It seems that the trace doesn't capture which database the SQL has been run on, and I can't find a place to say that it should.

How can I analyse a trace from this script without these errors?


texts: AWS RDS upgrade from 8.0.11 to 8.0.19 fails

texts:
I am trying to upgrade an RDS database from 8.0.11 to 8.0.17. or 8.0.19.
I started by creating a snapshot, restoring that snapshot, and then trying to upgrade to 8.0.17. The upgrade says it was successful, but the version is still showing 8.0.11
The upgrade log starts out with:
2018-11-22 16:11:40 Checking server version.
2018-11-22 16:11:40 Running queries to upgrade MySQL server.
2018-11-22 16:11:45 Upgrading system table data.
2018-11-22 16:11:52 Checking system database.

then checks all the tables, each line which ends with an "OK".
2018-11-22 16:11:53 sys.sys_config OK
2018-11-22 16:11:53 Upgrade process completed successfully.
2018-11-22 16:11:53 Checking if update is needed.
END OF LOG

However, there are some errors in the PrePatchCompatibility.log:
7) Issues reported by 'check table x for upgrade' command
<schema>.<table> - No database selected
<schema>.<table> - Corrupt
<schema2>.<table> - No database selected
<schema2>.<table> - Corrupt
<schema3>.<table> - No database selected
<schema3>.<table> - Corrupt

When I connect to the database, I can't find anything wrong with that table, although it is in fact a view:
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `<table>` AS ...

Any ideas? Removing the view allows the upgrade to succeed.


texts: Documenting a collection of databases and their relationships with each other

texts:
As part of a project I'm currently involved with we need to identify where various data points are held within existing systems, with a view to integrating them into our central integration engine. We're part of an NHS Trust, so there are many databases, reporting databases, etc.

There seem to be many tools to design & document a single database, but what I'm after is something a level higher, which can record the relationships between data elements in different databases. I can't find anything on Google, but I wonder whether I'm just using the wrong terms.

At the moment we're solving the problem by recording the database, table, attribute & a sample query for each data item. A system which could at least group the different data elements by table, database, etc. would be useful.

It seems this would be useful to any organisation with many disparate databases, so want to check it doesn't already exist (or that there's not a better solution for the problem).


texts: How to partition a very large table with limited db space?

texts:
I have a very big table. I want to partition it, but I cannot.

Database size: 1TB, Free space 200GB

Table:

Size: 165 columns (row lengh 4216 KB, no LOBs), 500 million rows, 600GB of data.
Possible partitioning: a day per partition
Number of rows per day/parition: 2 million

In order to partition it, I need to create a clustered index. But creating parition requires free space of the same size as the table, and I do not have extra 600GB.

Are there any options how I partition this table?

EDIT 1:

I have tried copying data into separate table.

However when I try to DELETE (or INSERT) 1 day of data into another table, I get an error, that tranasaction log is full and my transaction is being rolled back. My transaction log is approx 20 GB and I cannot make it any bigger.


texts: How can I view when mysql mixed mode replication is logging a statement using row or statement format?

texts:
I have a couple of servers running in MIXED mode replication and I do maintenance regularly on them deleting a decent sized set of data. There is one table that had no primary key and is just used for logging. When we delete old data out of this table it runs fast on either the slave or the master but when it's part of a script that runs over night it blocks and runs very slowly on the slave.  This was working fine when we were using statement based replication everywhere on an older version of MySQL.

The table has a structure something like this:

CREATE TABLE `user_events` (
  `event_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `event_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `type` tinyint(3) unsigned DEFAULT '0',
  `ipnum` int(10) unsigned NOT NULL,
  INDEX(`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The delete statement is a simple.

 DELETE FROM `user_events` WHERE `event_time` < CURDATE()

Doing research I discovered that it is possible that this is because the system is switching over to row-based replication for this particular statement. In particular it looks a lot similar to this bug: http://bugs.mysql.com/bug.php?id=53375

Only this delete does not use RAND() or LIMIT so it should not be nondeterministic. So I'm not sure why it is using row based. It might be because the script it is being run in is using temporary tables. I could confirm this by running the delete inside and outside a script that uses temporary tables easily enough if I could see whether the statement was logged using row or statement based replication.

If I can confirm when a particular statement is run whether it was written row or statement based I can quickly solve the problem. I would also like to know this in general so I can determine which if any statements we are using are being pushed via row based replication.

I know the ultimate solution is to change to partitioning or adding a primary key and breaking the deletes into chunks and I'm also aware I can force the replication mode to statement for a session or even tell the sql statement not to write to the bin log and run an equivalent delete on the slave, but I'm right now just looking for a better way to understand and diagnose the problem. Thanks.


texts:
I would like to confirm if MySQL uses SHA1 for 'PASSSWORD' hashing and why test below doesn't work:

mysql> GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> SELECT user,host,password FROM mysql.user WHERE user='test';
+------+------------+-------------------------------------------+
| user | host       | password                                  |
+------+------------+-------------------------------------------+
| test | localhost  | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+------+------------+-------------------------------------------+
1 row in set (0.01 sec)

mysql> \q
Bye
[root@test ~]# echo -n "password" | sha1sum | awk '{print toupper($1)}'
5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8


texts: MYSQL Including Missing Values Using the Previous Most Recent Record

texts:
Given a table containing a date for every day over a period

CREATE TABLE `tbl_calendar` (
  `date` date NOT NULL,
  PRIMARY KEY (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tbl_calendar` (`date`)
    VALUES
        ('2016-12-10'),
        ('2016-12-09'),
        ('2016-12-08'),
        ('2016-12-07'),
        ('2016-12-06'),
        ('2016-12-05'),
        ('2016-12-04'),
        ('2016-12-03'),
        ('2016-12-02'),
        ('2016-12-01')
 ;

And a table containing values, of different types, with values missing for random days where they have not been populated.

CREATE TABLE `tbl_values` (
  `value_id` int(11) NOT NULL AUTO_INCREMENT,
  `type_id` int(11) NOT NULL DEFAULT '0',
  `date` date DEFAULT NULL,
  `value` double(15,2) DEFAULT '0.00',
   PRIMARY KEY (`value_id`),
   KEY `type_id_date` (`type_id`,`date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tbl_values` (`type_id`, `date`, `value`)
VALUES
    (100, '2016-12-02', 1),
    (100, '2016-12-04', 2),
    (100, '2016-12-06', 3),
    (100, '2016-12-08', 4),
    (100, '2016-12-10', 5)
;

How can the values for the missing days be returned in a SELECT, using the most recent previous record for that type? here is what I have so far.

SELECT
    v1.type,
    c.date,
    v1.value

FROM
   tbl_calendar c

   LEFT JOIN tbl_values v1 ON (
            v1.type_id = 100
        AND v1.date <= c.date
   )

   LEFT JOIN tbl_values v2 ON (
            v2.type_id = 100
        AND v2.date < c.date
        AND v2.date > o1.date
   )

WHERE
       v1.date = c.date
    OR v2.date IS NULL

The problem with this query is that it returns the previous most recent records value when there is a value for that date, along with a record with the correct value.

Expected Output

a_vlad answer is correct in results, however poor on performance, which is expected.

SELECT
    t1.date,
    (SELECT v1.type_id FROM tbl_values v1 where v1.date <= t1.date ORDER BY v1.date desc limit 1) as `type`,
    (SELECT v1.`value` FROM tbl_values v1 where v1.date <= t1.date     ORDER BY v1.date desc limit 1) as `value`

FROM tbl_calendar t1
HAVING `type` IS NOT NULL

The final solution that I used was to use a_vlad query to create a summary table. However, it turned out not to increase performance of the system (as previously the missing values were filled in in a PHP loop, which worked out to be as fast)


texts: Query to get the users who have a application approved

texts:
I have two tables Users and Submissions . Need to find the query to get the users who have submitted atleast one grant and have never won .

I can write a subquery and achieve it .But I would like to know if there is an efficient way ?

Users
ID | name | email | registered | etc

Submissions

ID | userID | status | eventID | etc

Now Submissions can have multiple entries of users corresponding to different event. A user can have a status as approved ,rejected .

How do I write the MySQL query to fetch the users who have atleast one submission but never approved .


texts:
Can a database detect that data is out of sequence if each datapoint has a sequential ID?
E.G A trading DB server receives trade ID 9, and next receives trade ID 11. In this case, I would like the DB to return an error because trade ID 10 is missing. Is this possible? I don't need the code, just some context and the name of the solution. E.G. would this be some sort of DB input validation command, and how would it be enabled?


texts: Disabling binary logging when restoring a compressed MySQL dump

texts:
I am busy build a slave of an existing database. I don't want it to build bin logs for the data I import before bringing the slave into the same state as master.

This is mostly to save space on importing 100 G of data.

mysqldump somelargedb | gzip > /somewhere/withspace/dump/somelargedb.sql.gz

Uncompressed this file is in the 100 Gb range.
Compressed it is around 2Gb


texts: Oracle SQL 12c How to correctly use compound foreign keys in compound primary keys?

texts:
DISCLAIMER: I am a student. I do not work for an airline. I do not pretend to be a professional programmer.

Greetings, I want understand what I need to correct in order for these tables to work together. I'm sure it is obvious, but it isn't to me. To me, it looks like I'm matching my columns correctly. I'm sure it has to do with using a compound foreign key as part of my compound primary key - but I don't know how to correct this. My full code, with many 'TODOs' still remaining, is on Github Gist here.

I do not have access to an Oracle DBMS at this time, and won't for several days, so I'm using SQLFiddle; which doesn't give the full output SQLPlus or Developer could. It is also not optimal because I'm studying 12c, but SQLFiddle only offers 11g support. Thank you ahead of time.

This is SQL fiddle's error output:

ORA-02256: number of referencing columns must match referenced columns

And here are the two tables which don't agree with each other.

CREATE TABLE FLIGHT (
  FLIGHT_ID NUMBER(3),
  AIRCRAFT_ID VARCHAR(8),
  FLIGHT_DATE DATE,
  CONSTRAINT PK_flight_comp_pk PRIMARY KEY (FLIGHT_ID,FLIGHT_DATE),
  CONSTRAINT FK_flight_route_ID FOREIGN KEY (FLIGHT_ID)
    REFERENCES FLIGHT_ROUTE(ROUTE_ID),
  CONSTRAINT FK_flight_serial_ID FOREIGN KEY (AIRCRAFT_ID)
    REFERENCES AIRCRAFT(AIRCRAFT_ID)
);

CREATE TABLE RESERVATIONS (
  CUSTOMER_ID NUMBER(4),
  FLIGHT_ID NUMBER(3),
  QTY_RESERVED_SEATS NUMBER(3),
  CONSTRAINT PK_reservations PRIMARY KEY (CUSTOMER_ID,FLIGHT_ID),
  CONSTRAINT FK_reservations_customer_ID FOREIGN KEY (CUSTOMER_ID)
    REFERENCES CUSTOMER(CUSTOMER_ID),
  CONSTRAINT FK_reservations_flight_ID FOREIGN KEY (FLIGHT_ID)
    REFERENCES FLIGHT(FLIGHT_ID,FLIGHT_DATE)
);


texts: Changing table types from MyISAM to InnoDB in a MySQLdump file

texts:
I have a few hundred systems running running on a MariaDB 10.1.x. On these systems there is a master database which, for various legacy reasons, has to have MyISAM tables. I wish to produce slaves for these masters which have InnoDB versions of all the master MyISAM tables. For operational reasons, I would prefer not to have to perform various ALTER TABLE operations on the slaves.

I was thinking (possibly simplistically) of taking the MySQLdump file from the master required as part of the initial slave setup, and then using some sort of script simply to alter its CREATE TABLE commands to specify a table type of InnoDB rather than MyISAM.

Is this a sensible approach, and has anyone else tried it? Is there a better approach which would get me a slave which uses InnoDB without having to do loads of ALTER TABLEs.

I am keen for this to be something which can be automated/script based, as we have literally hundreds of these systems in the field and so a manual upgrade process would be labour intensive and very error prone.

As an aside, I note that MySQL/MariaDB itself uses many MyISAM tables internally e.g. the mysql.user table. Do they use MyISAM for any particular reason, or could these tables also safely be converted to InnoDB. Or am I better ensuring that any script I use to do the conversion avoids the tables in the mysql database altogether?


texts: Call to Function in Oracle says it's not declared

texts:
When I call the next stored-function in oracle:

CREATE OR REPLACE FUNCTION SOLICITAR_DISPONIBILIDAD (parqueo IN NUMBER, semestre IN NUMBER,
año IN NUMBER, jornada IN NUMBER)
RETURN INTEGER IS
cupo_disponible INTEGER := 0;
cupo_tomado INTEGER := 0;
BEGIN
SELECT capacidad
  INTO cupo_disponible
  FROM estacionamientos
  WHERE numero = parqueo;
SELECT COUNT(*)
  INTO cupo_tomado
  FROM asignacion_parqueos ap
  WHERE ap.par_año = año
    AND ap.par_ciclo = semestre
    AND ap.par_numero = parqueo
    AND ap.par_jornada = jornada;
RETURN cupo_disponible - cupo_tomado;
EXCEPTION
WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END SOLICITAR_DISPONIBILIDAD;

It says 'SOLICITAR_DISPONIBILIDAD' is not a procedure or is undefined. Does anyone knows why?

I call it like this:EXECUTE SOLICITAR_DISPONIBILIDAD(1,0,2014,0);


texts: Get equivalent SQL query for any Postgres meta-command

texts:
I hope the title is self-descriptive.
I want to be able somehow to translate any Postgres meta-command into its corresponding/underlying SQL query, at least to learn more about Postgres and the way it stores meta-info in its tables.

Any ideas if this is possible?

E.g.:

When connected to the database EXAMPLE,
\dt and SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name; return the same result.

I want to find, if possible, a way to obtain the value SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name; when inputting \dt to the function/macro/whatever.


texts: Results for every hour of the day even data if not present

texts:
I am trying to write a query to show the count of users for each hour of the day. If there is no data for that hour, I want to record the hour with a count of zero.

Source data

╔════════╦═════════════════════════╗
║ userId ║      creationDate       ║
╠════════╬═════════════════════════╣
║      1 ║ 2014-10-08 14:33:20.763 ║
║      2 ║ 2014-10-09 04:24:14.283 ║
║      3 ║ 2014-10-10 18:34:26.260 ║
╚════════╩═════════════════════════╝

Desired output

╔═════════════════════════╦═══════════╗
║          Date           ║ UserCount ║
╠═════════════════════════╬═══════════╣
║ 2014-10-08 01:00:00.000 ║         1 ║
║ 2014-10-08 02:00:00.000 ║         1 ║
║ 2014-10-08 03:00:00.000 ║         0 ║
║ 2014-10-08 04:00:00.000 ║         1 ║
║ ....                    ║           ║
║ .....                   ║           ║
║ 2014-10-10 23:00:00.000 ║         1 ║
║ 2014-10-10 00:00:00.000 ║         0 ║
╚═════════════════════════╩═══════════╝

Attempt 1

SELECT
    CAST(creationDate as date) AS ForDate,
    DATEPART(hour,date) AS OnHour,
    COUNT(distinct userId) AS Totals
FROM Table
where primaryKey= 123
GROUP BY
    CAST(creationDate as date),
    DATEPART(hour, createDate);

This only gives me counts per hour for records that are present, nothing for the missing hours. I think there is a way by using a cross join to get a zero count for the missing hours.

Something like this, I came across, but not able to construct a proper query with it:

cross join (select ROW_NUMBER() over (order by (select NULL)) as seqnum
from INFORMATION_SCHEMA.COLUMNS) hours where hours.seqnum <= 24

I am not a SQL expert, but I am trying hard to construct this result set.

Attempt 2:

with dh as (
     select DATEADD(hour, seqnum - 1, thedatehour ) as DateHour
     from (select distinct cast(cast(createDate as DATE) as datetime) as thedatehour
           from Table a
          ) a
          cross join
          (select ROW_NUMBER() over (order by (select NULL)) as seqnum
           from INFORMATION_SCHEMA.COLUMNS
          ) hours
          where hours.seqnum <= 24
    )
select dh.DateHour, COUNT(distinct c.userId)
from dh cross join Table c
--on dh.DateHour = c.createDate
group by dh.DateHour
order by 1


texts: Out-of-Range and Overflow Handling for float values

texts:
am using mysql 5.5 log version with sqlmode='' .I have many columns with float(9,3) as the type. Say for ex one column taxvalue (9,3)

Currently if the user enter the  value above 999999.000 MYSQL will save its maximum value,ie
1000000.000  and it will not throw any error.

If I set the sqlmode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' my insert procedure will throw error, if the value for taxvalue  is greater than 999999.000

Am using this column in a user entry page and that application is written in C#. I wanted to show a validation message to user if he tries to enter the value above 999999.000.

what is the best method I can use to make sure,user is getting a validation message before he clicks the save button.?I have tried with regular expression but its not giving me the desired result because user can enter any value with the range of (9,3)..


texts: Adding filter for a decimal column slow down SELECT query significantly

texts:
I have a long query that returns 2000 rows in less than 2 seconds:

SELECT  target.Id, target.Decimals
FROM    dbo.t1
        JOIN dbo.t2 on ...
        JOIN dbo.t3 on ...
        JOIN dbo.t4 on ...
        JOIN dbo.target on ...
WHERE   t1.Id = ...
        AND t2.CreatedAt >= ''
        AND t2.Type = ''
        AND target.Id NOT IN (
            SELECT  s1.Id
            FROM    dbo.s1
                    JOIN dbo.s2
            WHERE   s1.Type = '' AND s2.Type = ''
        )

This is all good until I add one more condition to filter out a decimal column (NOT the sub-query):

        AND target.count = 0

The query than run really slow (> 1 min). If I select all 2000 rows, copy paste them on excel and filter manually, then return to SQL, the query would still running.

My table target has over 140k rows. I tried with another DB (different data set) with ~40k rows and it just need 1 second to filter all 6000 rows returned by the initial query.

In addition, changing the filter value would also affect the performance. The bigger it is (less likely to exist), the faster the query run. Ex: AND target.count = 100000 would return immediately.

I am not able to share any execution plans.


texts:
in High Performance MySQL on page 159 they talk about breaking up complex queries into simple ones:

Converting

SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';

To

SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);

And sort of doing the actual join yourself in your application.

My Question is whether this is stil such a good idea when the final query has a where-clause with a few thousand IDs it needs to match (the actual table itself has about 500k entries).

What I mean is, will there be a big penalty for having a query like

SELECT * FROM post WHERE post.id in (123,456,567, ... <a few thousand IDs here> ... ,9098,8904);

instead of the join-statement above? Would it help to move this logic to Stored Procedures inside the Database (while considering how poorly stored procedures are implemented in MySQL)?


texts: MySQL 5 on Mac 10.5: "problem connecting to the database"

texts:
I have worked with MySQL for years but I am stuck on a seemingly trivial problem.

I installed MySQL 5.0.96 on Mac 10.5, created a database, created a user with privileges on everything, and problem:
Apache/PHP (osCommerce) installed on the same machine says:

There was a problem connecting to the database server. The following error had occured:
Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (2)
Please verify the connection parameters and try again.

It is my first time installing MySQL on Mac, and there might be a bug with %, so I added for all addresses I could imagine (%, localhost, 127.0.0.1, my IP) but no luck:

mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'monty'@'127.0.0.1' IDENTIFIED BY 'some_pass';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'127.0.0.1' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'monty'@'192.168.43.152' IDENTIFIED BY 'some_pass';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'192.168.43.152' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

Default install, I did not change any parameter.
skip_networking is OFF.
The Mac firewall is set to accept anything.

Am I doing something wrong?


texts:
We are getting a bizarre error with some of our users while syncing with SQL Server 2008 R2 Service Pack 3. Validation fails even though the row count and expected row count actually match!

Error messages:

  Data validation failed for one or more articles.
  Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200953
  Article 'Quote' failed data validation (rowcount and checksum).
  Rowcount actual: 973, expected: 973.

Would it cause a problem disabling the validation? Is there a good example on how to create your own validation?


texts: Replication Subscribers and AlwaysOn Availability Groups (SQL Server)

texts:
I have AlwaysOn Availability Groups configured with Replication Subscribers, and  is currently running successfully with one publisher using the command below.

-- commands to execute at the publisher, in the publisher database:
EXEC sp_addsubscription @publication = N'yocc_pub',
       @subscriber = N'repl_agl',
       @destination_db = N'YOCCDB',
       @subscription_type = N'Push',
       @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0;
GO

EXEC sp_addpushsubscription_agent @publication = N'yocc_pub',
       @subscriber = N'repl_agl',
       @subscriber_db = N'YOCCDB',
       @job_login = null, @job_password = null, @subscriber_security_mode = 1;
GO

All commands complete successfully.

My first problem is "I want to add one more publisher " to the AlwaysOn subscriber but I am getting the error below:

  sql server error-21488 sql server error-18752

Note:-

node-1 (alwayson primary node and replication subscriber)
node-2 (secondary replica and report server)
node-3 (publisher 1)/yocc_pub(work as distributor)
node-4 (publisher 2)/yocc_pub2(configure successfully but got error-21488,18752)
listener name - repl_agl
database - yoccdb

Second problem-

How to maintain primary key value to avoid conflict between publisher 1 and publisher 2 to sync with node-1 because database are same ??


texts: Best way to archive old data from SQL Azure?

texts:
I have an app that stores event data. We use SQL Azure. Right now it's very expensive. Over $800/month to host. Once the event is over, I want to move the data to a DB that has a lower cost. Thus this will keep my SQL Azure usage lighter, and allow me to downgrade to a cheaper plan.

What is the best option?

Move data to a SQL Server Standard Edition (on-prem)
Move data to an MySQL Server (on-prem)
Use a new NoSQL DB?

The end-goal is to update our API, so it will pull data from both DBs in the results.


texts:
I'm a resident developer who is voluntold to be a database admin of MSSQL 2016.  Please bear with me as I'm still trying to self taught the art of DB admin.
I recently setup a DR solution using the architecture (Always On High Availability Group) below and things are working well for me.  All databases are synchronizing.

In my primary data center, I have two nodes (active & passive) that share the same disk (Log disk, data, ect..).  Currently I see the following on my active node, they're were setup there by my predecessors.

All databases are all in full recovery mode.
It has a Full Backup maintenance plan where a full DB backup is done at 12am daily.
It has a Log Backup maintenance plan where transaction logs are backed up every 5 minutes.

I understand that Always On High Availability Group use transaction log to sync data between the Primary Data node and the Disaster Recovery Node.  Can I disable the transaction log backup in #3?
Do you see any issue if I do not backup my transaction log at a 5 minutes interval?  Can I use my DR node as a "backup" in case something went wrong instead of restoring my database using our last daily backup and then procedurally  restore the entire transaction log backup from that day?