texts: Resolving TNS Permision Denied for XE on Windows 7

texts:
I am working with Oracle 11g XE on a Win7 laptop.  I have had this up and running without issue for more nearly two years.  Went about a month without connecting, and the next time I tried to I got “ORA-12546: TNS:permission denied”.

Since I first ran into this about a week ago I have:

Tried connecting with multiple clients (SQLPlus, PLSQL Developer, DB Forge Studio) all of which report the same error.
Tried connecting to other instances with the above clients, all seem to be working fine.
Tried connecting using the full connection descriptor instead of the XE alias.
Completely removed all oracle clients and installations from my laptop, and reinstalled them.  Including cleaning out residual registry entries, as noted here https://superuser.com/questions/256762/how-do-i-completely-remove-oracle-11g.

As far as I can tell, everything seems to have reinstalled correctly in terms of files being where they belong and services running, but I am still getting the same TNS errors.

What should I be looking for at this point?


texts: Is there a MySQL character set and encoding that will allow for both emojis and accents?

texts:
I've got a database of terms that get added to by one group of users, and queried against by another.

I was running into problems when people would query for an emoji in the database and my React app would throw an error (oddly a CORS error, but that's a different issue). I eventually solved this by changing my table's character set to utf8mb4 with utf8mb4_unicode_ci collation.

Now I'm getting errors when adding new terms saying, for example, that a duplicate row for "beyoncé" already exists. However, when I query the db for "beyoncé", it returns the row with "beyonce" in it. Is there a combination of charset and collation that can handle this properly?

My DB is MySQL 5.6.40 running on Amazon RDS.


texts: Anything obvious that could be improved upon? :)  I am positive there are no direct errors.  More what I am looking for is errors in normalization and areas that I could simplify data storage. Please see my previous question for the context and motivation.


texts: If calculation is negative it shows NULL, how to fix this?

texts:
So I am validating count between source and target tables, if count is not the same it can go in positive or negative number, however when is negative it would show NULL. I used to know this but seems to me I have short memory. My code is below.
with cte as (
            select 'source' [object],count(contract_id) as total_count
            FROM [account].[dbo].[account] act
            left join [account].[dbo].[contract] cont
            on act.account_id = cont.account_id)
,cte1 as(
          select 'target' [object], count(contract_id) as total_count
          from [account].[dbo].[action]
          union
          select * from cte)
          select * from cte1
          union
          select 'diff' [object],
                 (select total_count from cte where [object] = 'source')
                -
                 (select total_count from cte where [object] = 'target')

Results is as follow
object  total_count
target  28402
source  28401
diff    NULL -- here it should be negative 1 (-1)


texts: How do retrieve unique rows where all all columns are the same except id

texts:
So it looks like for some entries to the table the data is being written multiple times from our system, so all the rows entered for a specific data item are written 3 times, with exactly the same data apart from the auto-generated ID is different (incremented by 1 each time).

How would I retrieve all the essentially unique rows from the table? A join on itself doesn't seem to be working as the IDs are different.


texts: Two "Connect SQL" permissions in SQL Server 2012

texts: What is the difference between these two "Connect SQL" permissions in SQL Server 2012?


texts: Is it possible to create Dynamic SQL statements purely in SQLite?

texts:
A lot of modern RDBMS have a feature called Dynamic SQL (MS SQL Server, for example) which allows you to build your SQL statements in strings and then execute that string of SQL.
Does such a feature exist purely in SQLite?
I'm aware I can build my statements dynamically using a functional language that uses SQLite as a database. But that's not applicable to the problem I'm trying to solve because the dynamic statements I'm trying to build are based off of relational logic and involve sqlite_master and PRAGMA, and translating that logic into a functional language makes trying to solve this problem not worth it.
For all intents and purposes, I have the flexibility to use any version of SQLite to solve this problem.


texts: What Happens when Cartesian Product is applied to Relations with same attribute name

texts:
I understand that the Cartesian product (X) operation on two databases does not need to be UNION compatible. So,if there is a same attribute called name in two relations R and S where  name in R is the first name and name in S is the second name, how can the related values be identified by the following selection operation?

Q=RxS

I want to get the set of tuples whose firstname = lastname. How am I supposed to write the selection statement?

σ Name=Name(Q)

Will there be a problem using the same attribute name in the selection operation?

I'm just referring here to the problem of writing the relational expression (selection) when same attribute name is used.


texts: Append Records to a fixed number of columns using dynamic data

texts:
I have a table with 22,000 records formatted like so:
Input
[ID],[Cost Center],[Function]
1,01A1001710,GF000001
2,01A1001710,GF000802
3,02R1012000,GF000001
4,02R1012000,GF000059
5,02R1012000,GF000013

What I would like to do is append these to a new table formatted like so:
Output
[Cost Center], [Node1], [Node2], [Node3], [Node4], [Node5], [Node6]
01A1001710,GF000001,GF000802
02R1012000,GF000001,GF000059,GF000013

What is unknown is the number of [Function] per [Cost Center] it could range from a single function to a maximum of 6.
I tried to pivot but wound up with 60 different columns representing [Function]


texts: Why does a rollback of a CREATE INDEX statement take longer than expected?

texts:
Recently, I needed to apply a clustered index to a table. I first performed the operation in a Staging environment. The operation completed in about 20 minutes, so we planned for PROD to take approximately the same time. After I executed the statement, we let it run for about 4 minutes before we noticed some unanticipated blocking that was causing application queries to time out. Due to the timeouts, we decided to stop the execution, so I cancelled it.

Now, I assumed that either one of two things would happen when I pressed the cancel button:

1) SQL Server would stop organizing the data on disk and leave anything it had already moved where it was, thereby causing the rollback to be almost instant.

OR

2) SQL Server would take the data it had already organized on disk and move it back to where it was before, thereby causing the rollback to take approximately as long as the statement had been executing. In this case, the statement had been running for 4 minutes, so the rollback should make the total time 8 minutes.

Well, neither one of those things happened. The rollback actually took 20 minutes by itself, which was the amount of total time the index took to apply in Staging. This made the total time 24 minutes (execution for 4, rollback for 20).

Now, I am no internals expert, but what I infer from this is that after I clicked the cancel button, SQL Server had to scan the entire index on disk to find what data it had moved, and then move it back.

Does anyone have any more insight on this? Am I even in the ballpark here?

FYI, the only thing different between the Staging and Prod environments is that Staging has about 1/6 of the RAM. Also, the table was around 28 million rows and was a heap.

Thanks all!


texts:
I am trying to setup 2-node BDR replication and i am getting the following error while running bdr_init_copy.

  /usr/pgsql-9.4/bin/bdr_init_copy -d "host=10.10.145.111
  dbname=apimgtdb port=5432" -U postgres
  --local-dbname="host=10.10.145.110 dbname=apimgtdb port=5432" -n nodeawswest -D  /postgres/pgsql-9.4/data

< 2015-06-05 14:56:55.846 PDT >LOG:  database system was interrupted; last known up at 2015-06-05 14:49:09 PDT
< 2015-06-05 14:56:55.951 PDT >LOG:  entering standby mode
< 2015-06-05 14:56:55.951 PDT >LOG:  starting up replication identifier with ckpt at 9/F5000060
< 2015-06-05 14:56:55.956 PDT >LOG:  redo starts at 9/F5000060
< 2015-06-05 14:56:55.972 PDT >LOG:  consistent recovery state reached at 9/F5000128
< 2015-06-05 14:56:55.972 PDT >LOG:  database system is ready to accept read only connections
< 2015-06-05 14:56:55.977 PDT >LOG:  started streaming WAL from primary at 9/F6000000 on timeline 1
< 2015-06-05 14:56:56.639 PDT >LOG:  recovery stopping at restore point "bdr_6157013497737678047", time 2015-06-05 14:56:57.137408-07
< 2015-06-05 14:56:56.640 PDT >LOG:  recovery has paused
< 2015-06-05 14:56:56.640 PDT >HINT:  Execute pg_xlog_replay_resume() to continue.
< 2015-06-05 14:56:56.821 PDT >ERROR:  cannot execute DELETE in a read-only transaction
< 2015-06-05 14:56:56.821 PDT >STATEMENT:  DELETE FROM pg_catalog.pg_shseclabel WHERE provider = 'bdr';
< 2015-06-05 14:56:56.837 PDT >LOG:  received smart shutdown request
< 2015-06-05 14:56:56.838 PDT >FATAL:  terminating walreceiver process due to administrator command
< 2015-06-05 14:56:56.840 PDT >LOG:  shutting down
< 2015-06-05 14:56:58.169 PDT >LOG:  database system is shut down


texts: Avoiding a many-to-many relationship cycle between 3 tables

texts:
I'm designing a database for a IDLE CPU time donation system. and I struggled to design the database.

The problem is the following.
I have 3 entities (tables):

donors: hold donors' information
devices: a big array of various machine types (Phones, PCs, Tablets...)
events: hold information about the events, that will receive the CPU donations.

The rules the database is based on are.

Each donor owns many personal devices.
A donor can register to events.
By registering to an event, the donor choose which device(s) to donate CPU time from.
A donor can register to as many events he chose to.
A donor can register a device to multiple events.

At first, I created 2 additional tables besides the ones listed above(donors, devices, events):

donor-device : this table contains the various devices (Phone, PC, TV ...) owned by clients.
donor-event: this table contains the donors registered in events and the status of their participation (pending, rejected, accepted).

Then I discovered that this data model is not relevant in my use case. in this case I cannot know the devices that the donor chooses to donate for a specific event.

Here are some solutions I thought of, though I'm not convinced of their optimality.

Adding a column in the client-event table containing a string with the id of the devices donated + the amount of CPU time donated.
Using a JSON field instead if the string.
Adding a third many-to-many relationship device-event-client.

PS: I'm not tied to a relational solution, I can try other models as well.


texts:
Housekeeping on an old SSRS, this particular server is 2008R2.

I have the query below which gets me the name, location, creation and modification overview for all the reports.  I would also like to include the 'Connection String' at least, and some other details if possible from the data sources properties.  But I can't find it in ReportServer.  I have seen people use PowerShell to get it out of SSRS web service, I was hoping to find a T-SQL solution.

How can I display Data Source details with a SQL query?

--List all the reports on SSRS via "ReportServer" database

USE [ReportServer]
GO
SELECT Name
--, [ItemID] --Primary key
, [Path]
, [Description]
--, [CreatedByID] --need link to get anything usable from here
, Created.UserName as CreatedByUser
, [CreationDate]
--, [ModifiedByID] --need link to get anything usable from here
, Modified.UserName as ModifiedByUser
, [ModifiedDate]

FROM [dbo].[Catalog]
left join (select [UserID]
            , [UserName]
            from [dbo].[Users]) as Created
        on Catalog.CreatedByID = Created.UserID
left join (select [UserID]
            , [UserName]
            from [dbo].[Users]) as Modified
        on Catalog.ModifiedByID = Modified.UserID

WHERE [Type] = 2 -- value per foundation Source http://sqlsrv4living.blogspot.com/2014/01/ssrs-get-list-of-all-reports-using.html

ORDER BY [Path], Name


texts: Percona PAM with AD authentication using SSSD

texts:
I have installed percona PAm plugin on my Percona server as shown below:

mysql> show plugins;
...
| auth_pam                      | ACTIVE   | AUTHENTICATION     | auth_pam.so        | GPL     |
| auth_pam_compat               | ACTIVE   | AUTHENTICATION     | auth_pam_compat.so | GPL     |
+-------------------------------+----------+--------------------+--------------------+---------+

And also have this configured:

cat /etc/pam.d/mysqld
auth required pam_sss.so
account required pam_sss.so

I have a group on the AD server called "dba", and added an AD user 'john.d' in this group. So i would like to log into MySQL using Ad users e.g., john.d, who should also inherit all privileges granted to the "dba" group. Below is how this AD group, "dba", is a setup to allow its users access the Percona server:

CREATE USER ''@'' IDENTIFIED WITH auth_pam AS 'mysqld,dba=dbarole';
CREATE USER 'dbarole'@'%' IDENTIFIED BY 'dbapass';
GRANT ALL PRIVILEGES ON *.* TO 'dbarole'@'%';
GRANT PROXY ON 'dbarole'@'%' TO ''@'';

When i log in into mysql as dbarole, everything works well with all granted privileges. But when i login as john.d, one of the AD users included into the "dba" AD group, this user does not inherit the privileges (ALL) granted to its group, but only has the USAGE privilege as shown below:

mysql> show grants;
+-----------------------------------+
| Grants for @                      |
+-----------------------------------+
| GRANT USAGE ON *.* TO ''@''       |
| GRANT PROXY ON 'dba'@'%' TO ''@'' |
+-----------------------------------+
2 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)

My Question is, how can i get an AD user inherit privileges granted to their group in MySQL?


texts: Is is possible to change a View's table in PostgreSQL?

texts:
I have the following scenario. I have 2 views - view1 & view2 both reading data from table1. Is it possible to change view1 and view2's table from table1 to table2 if both table1 and table2 share the same schema?
Or I will have to drop the 2 views and recreate them so that they query table2?


texts: Summary statistics: Multiple joins and group by

texts:
I have a sidewalk inspection system:

SIDEWALK_INSP

   INSP_ID
----------
         1
         2
         3

SIDEWALK_DEFICIENCY

    DEF_ID      INSP_ID   DEF_LENGTH
----------   ----------   ----------
        10            1           .5
        11            1            1
        12            1          1.5
        13            2            2
        14            2          2.5

SIDEWALK_OBSERVATION

    OBS_ID      INSP_ID   OBS_LENGTH   OBS_TYPE
----------   ----------   ----------   --------
       100            2            5      a
       101            2            6      b
       102            2            7      b
       103            2            8      b
       104            3            9      a
       105            3           10      a
       106            3           11      b

create table sidewalk_insp (insp_id number(10));
insert into sidewalk_insp values (1);
insert into sidewalk_insp values (2);
insert into sidewalk_insp values (3);
commit;

create table sidewalk_deficiency (
    def_id number(10),
    insp_id number(10),
    def_length number(10,2));
insert into sidewalk_deficiency values (10,1,.5);
insert into sidewalk_deficiency values (11,1,1);
insert into sidewalk_deficiency values (12,1,1.5);
insert into sidewalk_deficiency values (13,2,2);
insert into sidewalk_deficiency values (14,2,2.5);
commit;

create table sidewalk_observation (
    obs_id number(10),
    insp_id number(10),
    obs_length number(10,2),
    obs_type varchar(5));
insert into sidewalk_observation values (100,2,5,'a');
insert into sidewalk_observation values (101,2,6,'b');
insert into sidewalk_observation values (102,2,7,'b');
insert into sidewalk_observation values (103,2,8,'b');
insert into sidewalk_observation values (104,3,9,'a');
insert into sidewalk_observation values (105,3,10,'a');
insert into sidewalk_observation values (106,3,11,'b');
commit;

I want to calculate the following in a single query:

Length of deficiencies (per inspection)
Length of observations, where observation type = 'a' (per inspection)
Length of observations, regardless of type (per inspection)

The output would look like this:

+---------+------------+-------------------+----------------+
| INSP_ID | DEF_LENGTH | OBS_TYPE_A_LENGTH | OBS_ALL_LENGTH |
+---------+------------+-------------------+----------------+
|       1 |          3 |                   |                |
|       2 |        4.5 |                 5 |             26 |
|       3 |            |                19 |             30 |
+---------+------------+-------------------+----------------+

How can I create this query?


texts: Query to get data from 2 different tables is taking long time

texts:
I have this query,

select Column1,Column2, etc..
from Db1.dbo.Tb1 b Where b.Id1 in (
    select distinct Column3 as Id1 from Db2.dbo.Tb2 s where s.Id2 <> ''
    union
    select distinct Column4 as Id1 from Db2.dbo.Tb2 s where s.Id2 <> ''
)

It's taking more then 10 minutes and still processing on a good spec database.

I can't change existing DB or add indexes, Is there anyway I could improve this query ?

It's getting data 2 columns from a table and then based on results getting data from another database's table.


texts: Problem with connect mysql databases of remote

texts:
I'm getting error with connect remote mysql database. Firstly;
1-) I use windows 10 machine and my remote server also windows 10 machine.
2-) My database at locale at remote windows machine with root.
I tried so many things but  and
ı took error picture
Also I CREATE USER user but again ı have a problem. How can ı fixed and connect remote database
Please help me?


texts:
Greetings!
I tried to optimize MariaDB configuration with my.cnf. Everything was OK, but available memory falling down each hour. I'm using MariaDB with Zabbix server on the same server. CPU: 4 Cores, RAM: 8 GB (before was 4 GB and it was OK), HDD: 128 GB.
Could you please tell me what should I fix?
[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

connect_timeout                 = 60
wait_timeout                    = 28800
max_connections                 = 200
max_allowed_packet              = 64M
max_connect_errors              = 1000

tmp_table_size                  = 256M
max_heap_table_size             = 256M
table_open_cache                = 1024

log_error                       = /var/log/mysql/mysql-error.log
slow_query_log_file             = /var/log/mysql/mysql-slow.log
slow_query_log                  = 1
long_query_time                 = 5

innodb_file_per_table           = 1
innodb_status_file              = 1
innodb_buffer_pool_size         = 5G
innodb_flush_method             = O_DIRECT
innodb_io_capacity              = 2000
innodb_log_file_size            = 640M
innodb_log_buffer_size          = 16M
innodb_buffer_pool_instances    = 5

skip-external-locking
bind-address                    = 127.0.0.1
event_scheduler                 = 1
query_cache_type                = 0
join_buffer_size                = 256K
read_buffer_size                = 256K
read_rnd_buffer_size            = 256K
sort_buffer_size                = 256K
key_buffer_size                 = 16M
thread_stack                    = 192K
thread_cache_size               = 8
myisam-recover-options          = BACKUP
query_cache_limit               = 2M
query_cache_size                = 128M
expire_logs_days                = 10
max_binlog_size                 = 100M


texts: Restrict Oracle DB Login based on SQL Client being used

texts:
We have an application in our organization that is based on Oracle Forms 11g (on Oracle Fusion Middleware/WLS). Users have access to the application through accounts created at the database (i.e. they exist in dba_users). Each user is assigned a specific role based on their area of work and they use the user id to log on to the application. The user may have read/write access to certain functionality in the application and for this their roles have the following permissions-

EXECUTE ANY LIBRARY
SELECT  ANY SEQUENCE
EXECUTE ANY TYPE
EXECUTE ANY PROCEDURE
UPDATE  ANY TABLE
SELECT  ANY TABLE
DELETE  ANY TABLE
EXECUTE ANY INDEXTYPE
INSERT  ANY TABLE

Now we have some users requesting for SQL Client access (i.e. TNS settings) so that they can connect to the DB and perform queries for their research. These business users are ofcourse knowledgeable in SQL but we want to restrict their access based on the type of client they use to log on to the database. Any client other than the application itself, should restrict them to "read only".

Is there a way to achieve this?


texts:
My organization has been successfully using service broker, dialog security, for a few years.

I have a new instance of service broker that I would like to configure, but I believe I am running into a situation where the order in which the new broker instance is being incorrectly placed into the databases (with existing broker instances in place) causing the new broker instance to fail.

My error is on the target server; Certificate Not Found. Security context could not be retrieved.

My situation is:
Source server A (dmz) sending to target server B, where there are already existing broker instances sending messages both ways but on different databases.

Test 1: I switched the role of the source and target server around, making server B my source and server A (dmz) my target while using the same databases.  This was successful.

Test 2: I dropped the test 1 instance and created 2 new databases on each server. I have again, reconfigured the source and target roles, now sending from server A (dmz) to server B and this was successful. There is only the one service broker instance configured in the new databases, so the order of placement is not an issue.

I have come into this situation in the past when we first set up our dev/test environments; However, the situation was slightly different as many other broker instances were also failing. Not all of them, but some. Once I realized what was happening, dropping all instances and recreating them in a specific order, source instances before target instances, the situation was resolved.

I would think that service broker would add a new instance in the correct place of order, automatically.

Am I correct in thinking that the broker instances must be configured within a specific order? If so, how can I add new broker instances without interrupting what is already there?

Please share your thoughts and suggestions.


texts:
We are using SQL Server 2012 and we have the Main office server. All modification on the product pricing table is done through the Main office.

We have 10 remote sales branches. Those remotes branches need to be updated by the products prices from the main office (read-only data, not changed by the branches). Each branch has an unique code to be identified.

All the sales process performed by the remote branches only. We need all the sales invoice tables (header & details) in the branches to be sent to the main office for consolidation and other activities.

In summary we need to send items table to the 10 remote branches and receive from the remote branches two tables (salesHeader and salesDetails tables).

Which replication type is good for our situation?


texts: Extract column definitions from an IXF file of unknown origin

texts:
Suppose someone exported the table PROPERTIES using the command:

 CALL SYSPROC.ADMIN_CMD( 'EXPORT TO /tmp/properties.ixf OF IXF
    MESSAGES ON SERVER select * from properties' );

Since then the column order has changed, some were dropped, some added, but I need to load data from that IXF file into the columns that still exist. I don't know what columns the file contains. Running IMPORT without mapping them explicitly will fail.

I can only think of loading the file into a temporary table, then create a proper mapping:

CALL SYSPROC.ADMIN_CMD( 'IMPORT FROM /tmp/properties.ixf OF IXF
    MODIFIED BY FORCECREATE ROWCOUNT 0 MESSAGES ON SERVER CREATE INTO TEMP12345' );

Problems:

IMPORT command options CREATE and REPLACE_CREATE are deprecated
ROWCOUNT 0 not honored in this mode and the data is still loaded from a potentially big file


texts: Log shipping from SQL Server 2014 Enterprise db to SQL Server 2017 Standard with partitioning

texts:
I wish to implement log shipping from my current primary which is a SQL Server 2014 Enterprise database to a SQL Server 2017 Standard edition database. I also wish to implement table partitioning on my primary (2014 Enterprise) database. Table partitioning only became available at Standard edition level from SQL Server 2016 Standard SP1 onward.

I believe this will work I need to test it first. My problem is that SQL Server Evaluation edition seems to only correspond to Enterprise/Developer edition or at least that is my understanding. I do not have access to 2017 Standard edition without purchasing but do not want to purchase it without knowing if my proposed log shipping implementation would work for sure.

Has anyone else implemented similar, or does anyone know how to restrict an evaluation edition so that it corresponds to Standard?


texts: Duplicate Ips for cluster nodes causing backup issues

texts:
We have a four node cluster (SQL Server 2014 on Windows 12) and we recently setup heart beat network on the nodes following a best practices source after experiencing some cluster instability issues. Ever since a couple of secondary nodes are returning multiple IPs in the DNS. We have removed the entries from the DNS server for both the nodes multiple times but the IPs of the heartbeat network (the IPs are supposed to be non-routable) keep popping ultimately returning two IPs for the same node and this is causing issues with our backups. I have checked the settings on all four nodes and they are the same in the network adapter properties. What am I doing wrong?

PS: In addition, I have also changed some settings in the TCP/IPv4 properties such as removing the DNS server addresses and unchecking the 'register this connection's address in DNS'


texts: Scalar Operator in Seek Predicate and a Row Estimate of 1

texts:
I'm trying to understand some behavior I'm seeing in a query plan that's rather large and unruly.  In particular, I'm looking at a Clustered Index Seek Operation containing a Scalar Operation within its Predicate.  I suspect the Scalar operation is simply the aliasing of one of the tables (as described in Scalar Operator in Seek Predicate) as both columns are of the same type and this operation feeds into a Parallel Nested Loops (Left Outer Join) operator.

My question though is more about the Row Estimate coming in at 1 instead of a number closer to the Actual Number of Rows (~6.7 million).  Is the Scalar Operation killing the optimizer's ability to estimate the rows properly?  I assume so and also assume this is hurting my query execution plan from being optimal, but I don't really know for certain.  Can someone confirm or refute my suspicions along with why?

Here's the operation in question:

Version: SQL Server 2012 Enterprise


texts: Schema binding seems to not be enforced for view doing temporal query

texts:
I am using Microsoft SQL Server 2019.
There are two strange behaviors that I don't see documented (if it is, please point me to the Microsoft docs link), I suspect they're related.

The INFORMATION_SCHEMA.VIEW_COLUMN_USAGE system view does not show information about view queries that do temporal queries.

If I create a view which does a temporal query, schema binding is not enforced. I can alter the columns on the underlying table that the view's query uses. Normally, creating the view with the "WITH SCHEMABINDING" option would cause such attempts to alter the table to fail.

Try the sample code below to see that behavior. I added "!!" in the comments at the spots where things did not work out as I expected them to.
My main question: is this a bug or is this expected behavior?
--cleanup from past run
DROP VIEW IF EXISTS [vwTemporalTest_doingTemporalQuery]
DROP VIEW IF EXISTS [vwTemporalTest_noTemporalQuery];
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TemporalTest')
BEGIN
    ALTER TABLE [TemporalTest] SET (SYSTEM_VERSIONING = OFF);
END
DROP TABLE IF EXISTS [TemporalTest];
DROP TABLE IF EXISTS [TemporalTestHistory];
GO

--create the temporal table
CREATE TABLE [TemporalTest]
(
    [Id] [int] NOT NULL,
    [Name] nvarchar(500) NOT NULL,
    [Description] nvarchar(100) NULL,
    [period_start] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
    [period_end] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([period_start], [period_end]),
    PRIMARY KEY CLUSTERED ([Id])
) ON [PRIMARY]
WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[TemporalTestHistory] , DATA_CONSISTENCY_CHECK = ON ))
GO

--insert some data
INSERT INTO [TemporalTest] ([Id],[Name]) VALUES
(1, 'Alice'),
(2, 'Bob');
--just do an update so some history row gets populated
UPDATE [TemporalTest] SET [Name] = 'Bobby', [Description] = 'blah' WHERE [Id] = 2;
GO

--create the views (first one won't do a temporal query, second one will do a temporal query)
-- and they'll reference different columns to showcase the issue (first one uses Name, second uses Description)
CREATE VIEW [vwTemporalTest_noTemporalQuery]
WITH SCHEMABINDING
    AS SELECT [Id], [Name] FROM [dbo].[TemporalTest];
GO
CREATE VIEW [vwTemporalTest_doingTemporalQuery]
WITH SCHEMABINDING
    AS SELECT [Id], [Description], [period_start], [period_end] FROM [dbo].[TemporalTest] FOR SYSTEM_TIME ALL;
GO

--test out the views
SELECT * FROM [vwTemporalTest_noTemporalQuery]; --returns 2 current rows
SELECT * FROM [vwTemporalTest_doingTemporalQuery]; --returns 3 rows (including 1 historical row)

--look at information about the views
select * from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE where View_Name = 'vwTemporalTest_noTemporalQuery' --returns 2 rows (one for each column used)
select * from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE where View_Name = 'vwTemporalTest_doingTemporalQuery' --!!returns no rows (I expected 4 rows at least since the view uses 4 columns)

/*
--now verify schema binding prevents the underlying table from being altered
ALTER TABLE [TemporalTest] ALTER COLUMN [Name] nvarchar(600) NOT NULL; --fails as expected - The object 'vwTemporalTest_noTemporalQuery' is dependent on column 'Name'.
ALTER TABLE [TemporalTest] ALTER COLUMN [Description] nvarchar(255) NULL; --!!unexpectedly succeeds. I expected it to fail because vwTemporalTest_doingTemporalQuery uses the Description column.
*/


texts: Query for frequency of e-mail addresses in a field

texts:
Newbie here. I know the answer is out there, but lack the vocabulary to ask the right question...

I am looking for a MYSQL query which gives me the frequency of addresses in the field emailaddress. I would like to know how many are unique, how many occur twice, three times etc.


texts: Cannot see other databases in SQL Server Managed Instances using Azure AD user

texts:
we are running PoC on SQL Server Managed Instance but it doesn't seem to be walk in the park and we're hit some blockers. Hoping some of you may have resolved this already and can share some experiences :)

Our Business Acceptance Criteria:

Users must be able to run standard SQL queries- OK
Users must be able to correlate data between databases on cloud-based server- BLOCKED
Users must be able to access from and to on-prem linked servers- BLOCKED
Users must be able to access data with existing tools
SSMS - OK
Spotfire- OK
PowerBI - BLOCKED, AAD user doesn't work!
Excel - BLOCKED, AAD user doesn't work!
Tableau - Pending

Given I have SQL Server Managed Instance with these setup

SQL Managed Instance, General Purpose
MI on private VNet, we have Azure AD synced with our ON-PREM AD
Lifted two databases DB1, DB2 from ON-PREM instances
MI Containment = Disabled, DB1 and DB2 Containment = None
Mapped existing user groups from Azure AD (AAD) DB_ADM_USERS
Granted DBO role on group DB_ADM_USERS on each database DB1, DB2
Granted DBO role on group DB_ADM_USERS on MASTER
Added linked servers to ON-PREM instances using option #4 Security Context with remote user and password
I have USER1 who is part of AAD Group DBM_ADM_USERS
USER1 connects via latest version of SSMS
USER1 connects using Active Directory - Integrated

Questions:
When user logs in Uses Active Directory - Integrated

If USER1 doest not specify databases in connection, we get connection error

  Cannot connect to xxxx.xxxx.database.windows.net. Login failed for
  user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error:
  18456)

If USER1 specifies the database to connect to such as DB1

User can successfully connects, DB1 objects are accessible
User CANNOT see DB2 is not accessible and all other objects including the Linked Servers

If USER1 specifies the database to connect to such as DB2

User can successfully connects, DB2 objects are accessible
User CANNOT see DB1 is not accessible and all other objects including the Linked Servers

Thanks in advanced - Ardi


texts: What does 'sp_configure allow updates' do?

texts:
Today I got an error ( DTSER_FAILURE  ) but I could fix it by editing the maintenance plan ( this error is due to the maintenance plan searching for a database that was deleted, and I was using "selected databases" in the setup ).

But over the internet, I read some posts saying that I should SP_CONFIGURE 'ALLOW UPDATES',0.

What effects has this option?

I read on the Microsoft page that this option has no effect and it is deprecated.


texts:
I recently got an Ora-1688 alert and realized my SYSAUX is 99.7% full.
Is this a critical issue that has to be dealt with immediately?
Will this cause my system to stop?


texts:
My query looks like ..
SELECT
        uid
    FROM
        user

    WHERE
        plz IN(24718,32326,32329,32328)

    ORDER BY
        FIELD(plz,24718,32326,32329,32328),
        uid DESC

Output:
97462
97407
97112
.
.
3037
891
15
91967
29360

I need to select / get first (97462) and last (29360) uid.
SELECT uid, MAX(uid), MIN(uid) does not work because of the ORDER BY.


texts: multiple prefixes on wordpress databases

texts:
I just noticed that, somehow, the databases on a wordpress site I run all have multiple prefixes.
It seems to be causing fatal errors. Would that be the case?
If so, how do I fix that? SQL noob here so any help would be much appreciated! It's a mature site with bajillions of bbpress entries and woo subscriptions so it's important that it doesn't go down!


texts: Update expiring Mongo cluster SSL certificate

texts: I have a multi-node mongo cluster using SSL requireSSL and the certificates are going to expire. Is there a zero-downtime method to update the certs? I know the easiest way would be to stop all nodes, swap out the certs, and restart mongo. Same for all clients. I have not found a way to execute it without a service interruption.


texts:
Attempting to select a table with a column where the customers total drinks ordered per day are calculated by the odate and drink number.
EG.
Claude orders 10 drinks total on the 2020-1-12

drinker | odate      | total_drinks
Claude  | 2020-01-12 | 10
Claude  | 2020-01-15 | 3

How do i condense my current statement


texts: How does PostgreSQL store timestamp internally?

texts:
In case of MySQL,

TIMESTAMP values are stored as the number of seconds since the epoch ('1970-01-01 00:00:00' UTC)`

In case of PostgreSQL with the version less than or equal to 9.6

timestamp values are stored as seconds before or after midnight 2000-01-01

In case of PostgreSQL with the version greater than or equal to 10, there is no explanation about this
I have two questions about the internal logic of PostgreSQL.

Does it still use the same standard as the version 9.6?
Why "midnight 2000-01-01"? Unix epoch starts from 1970-01-01 00:00:00 UTC. J2000 epoch starts from 12 noon (midday) on January 1, 2000.

It seems like only a few systems use 2000-01-01 00:00:00.
Because PostgreSQL provides functions to convert UNIX epoch into the timestamp to_timestamp or vice versa EXTRACT(EPOCH FROM ...), using the standard that is different from UNIX epoch seems like to require additional offset calculations.


texts:
I'm having difficulty in retrieving data from tables.
Bellow is the scenario I'm stuck at.
TableA

Id
Name

1
ABC

2
DEF

3
GHI

4
JKL

TableB

Id
TableA_Id
Tag

1
1
X

2
2
Y

3
0
Z

TableC

Id
TableA_Id
Something

1
1
XXX

2
1
XXX

3
1
XXX

4
2
XXX

5
2
XXX

6
3
XXX

7
3
XXX

8
4
XXX

9
4
XXX

TableA joins With TableB by TableA_Id column, the only difference is that where there is no reference of TableA id then we are supposed to return the row with TableA_Id = 0 from TableB.
Required Output

Name
Tag
Something

ABC
X
XXX

ABC
X
XXX

ABC
X
XXX

DEF
Y
XXX

DEF
Y
XXX

GHI
Z
XXX

GHI
Z
XXX

JKL
Z
XXX

JKL
Z
XXX


texts: Designing a table for large amount of rows. No relationships

texts:
I am looking to design a database that'll allow me to store references to files. The table is pretty simple:

Id (GUID)(PK)
Path (nvarchar(250))
IsPublic (bit)
FileType (int)

The files can be accessed via url by putting the GUID in the url, such as files/{GUID}, so if request comes in, I can query the table by the GUID as Primary Key.

Do you think this is a valid approach? What insert/query performance am I looking at with hundreds of millions of records?

The database engine is Microsoft SQL Server.

Some queries I am predicting:

SELECT * FROM Files where ID = {GUID}

INSERT INTO Files () VALUES ()

UPDATE Files SET IsPublic = true WHERE ID = {GUID}

UPDATE Files SET Label = {NewLabel} WHERE ID = {GUID}

DELETE FROM Files WHERE ID = {GUID}

Most work will happen in select and insert. There will be very few updates and deletes. Maybe 10 inserts per second at its peak.

Each service stores the FileIds next to the tables that use it. Items service has ItemFiles which will then retrieve the Files from the service/table.


texts:
In the past I have stored all generic settings and configuration items such as: custom application title, home address, version, debug, etc.. all in a "settings.php" file that I would include into all of my scripts.

I would assume that it is better practice to have a database table specifically setup for application settings such as these, in that way you dont need to edit a php file to adjust or make changes. Yet I am a bit cautious about how I would set this up.

There are two options I have been considering:

I have a table with one row, many columns, making each column a unique variable value.
I have two columns with the first being the variable name, and the second being the variable's value.

Any suggestions or things I should be aware of from a database design perspective?


texts: Decompostion with loss of information shown by junction

texts:
F={A → B; B → C; C → D}

R is decomposed in R¹(A,B) & R²(A,D) & R³(C, D)

Is it without loss of information?

I would have said yes as far as with the following array

A → B x²²= b

B → C x¹³=x²³=c

Therfore we should see that it is without loss of information (we should have a straight line of determinated data). However, the answer says that it clearly lacks informations and in order to show that it is with loss of information, one should find an instance r such that

r ≠ r¹⋈ r²⋈ r³

I know that I have then to do some arrays and some junctions but I don't even know how to start the arrays...


texts: Mirroring problems after removing domain

texts:
I successfully created a mirror with certificates between two servers. One of those servers was a domain controller. After removing the domain the mirror was interrupted. After hours of searching I reinstalled SQL Server hoping all possible domain removal issues would be gone, but still the mirror refuses to work. I keep getting the vague error:

  Msg 1418, Level 16, State 1, Line 1
  The server network address "TCP://..." can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

If I check the eventlog of the mirrorserver I see 2 errors:

A cryptographic operation failed. This error indicates a serious problem with SQL Server. Check the SQL Server error log and the Windows event logs for further information.
A system cryptographic call failed during a Service Broker or Database Mirroring operation: system error '5(failed to retrieve text for this error. Reason: 15105)'.

Any ideas/suggestions?


texts: SQL Server 2016 AlwaysOn DBCC CHECKDB on async replica

texts:
We have an AlwaysOn environment, which includes a replica in our DR site that is setup with async commit and readable secondary = No.

When we were running on SQL Server 2014 SP2 we were able to run DBCC CHECKDB against the databases on our DR replica. But since upgrading to SQL Server 2016 we're unable to and our weekly integrity job is failing with the error.

'The target database is participating in an availability group and is currently not accessible for queries.
Either data movement is suspended or the availability replica is not enabled for read access.
To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.  For more information,
see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

Obviously something has changed in 2016 to prevent this, I just don't know what?


texts:
I'm a programmer, not a DBA.  Be gentle :)

Overview

InnoDB, MySQL
mod_perl script, persistent connections
script called every 20 seconds by thousands of users

Problem

High Disk IO (presumably caused by updates[?]) slows everything down, creating a huge bottleneck.

Queries

UPDATE [single table] SET refreshTime to current timestamp, with two same table checks in the WHERE clause
SELECT COUNT(*) [four table join, with indexes], and a bunch of ANDs in the WHERE clause (still pretty simple)
SELECT a,b [four table join, same four tables], and a bunch of ANDs in the WHERE clause (also pretty simple)

Query cache is on.

Solutions?

I'm not a DBA, but I suspect that it's possible to have a table in RAM that periodically (every 10 seconds?) updates onto disk, and in the event of a catastrophic failure, will automatically populate the RAM table from the disk table upon restart, but I have no idea if it's actually possible, if it's the best solution or what other options there are out there.
Any thoughts or suggestions?  Again, I'm a programmer so if someone either knows someone who does this for a fee or can point me to very specific resources, I'd be very appreciative.

~~~~~~~

CREATE TABLE `openInvitations` (
`id` int(99) NOT NULL auto_increment,
`createTime` timestamp NULL default NULL,
`repAcceptTime` timestamp NULL default NULL,
`rep_id` varchar(64) NOT NULL default '',
`repRefreshTime` timestamp NULL default NULL,
`customer_macAddr` varchar(14) NOT NULL default '',
`customerRefreshTime` timestamp NULL default NULL,
`stage` char(1) NOT NULL default 'P',
`parent` varchar(25) default NULL,
`reason` varchar(64) default NULL,
PRIMARY KEY  (`rep_id`,`customer_macAddr`),
UNIQUE KEY `id` (`id`),
KEY `customer_macAddr` (`customer_macAddr`),
CONSTRAINT `openInvitations_ibfk_1` FOREIGN KEY (`rep_id`) REFERENCES `rep` (`id`),
CONSTRAINT `openInvitations_ibfk_2` FOREIGN KEY (`customer_macAddr`) REFERENCES `customer` (`macAddr`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  oi  ref     PRIMARY,customer_macAddr    customer_macAddr    16  const   1   Using where; Using index
1   SIMPLE  r   eq_ref  PRIMARY,FK_rep_1    PRIMARY     66  xxx.oi.rep_id   1   Using where
1   SIMPLE  s   eq_ref  PRIMARY,FK_subscriber_1     PRIMARY     27  xxx.r.subscriber_id     1   Using where
1   SIMPLE  c   eq_ref  PRIMARY     PRIMARY     4   xxx.s.charge_id     1   Using where

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  oi  ref     PRIMARY,customer_macAddr    customer_macAddr    16  const   1   Using where
1   SIMPLE  r   eq_ref  PRIMARY,FK_rep_1    PRIMARY     66  xxx.oi.rep_id   1   Using where
1   SIMPLE  s   eq_ref  PRIMARY,FK_subscriber_1     PRIMARY     27  xxx.r.subscriber_id     1   Using where
1   SIMPLE  c   eq_ref  PRIMARY     PRIMARY     4   xxx.s.charge_id     1   Using where

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  openInvitations     ALL     customer_macAddr    NULL    NULL    NULL    5258    Using where

After fixing query:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  openInvitations     ref     customer_macAddr    customer_macAddr    16  const   1   Using where


texts:
I've got the following scenario:
( loggend in on SQL-Server with full admin-privileges )

Creating LogIn, User, Role and associate them.

IF NOT EXISTS( SELECT "name" FROM "master"."dbo"."syslogins" WHERE "name" = 'ocmb_admin' ) BEGIN CREATE LOGIN ocmb_admin WITH PASSWORD = 'pw', CHECK_POLICY = OFF; END

IF NOT EXISTS ( SELECT 1 FROM "sys"."database_principals" WHERE "name" = N'ocmb_admin' ) BEGIN CREATE USER ocmb_admin FOR LOGIN ocmb_admin;END

if not exists ( select 1 from "sys"."database_principals" where "name" = N'ocmb_grp_admin' ) begin CREATE role "ocmb_grp_admin"; end

EXECUTE sp_addrolemember N'ocmb_grp_admin', N'ocmb_admin';

Works fine. Needed permissions on objects for created login ( ocmb_admin ) are granted in separate queries, not completely neccessary for this question.

Now, logging in with this login I have to query sys.syslogins which is possible but only returns data for current login and sa.

I tried several ways to grant explicit permissions to the group ( ocmb_grp_admin ), but none of them leaded to the fact, that I can query the complete content of the view.

I need to determine, whether some logins exist while logging in with an random account, which is assigned to the prior created role 'ocmb_grp_admin'. Permissions can and SOHULD be granted to this role, to avoid having to grant them for each new user.

What I tried so far:

A:

USE master
GO
GRANT VIEW ANY DEFINITION TO "<username>"

This one works, but is completely uncomfortable, because every user has to be added separately.

B:

USE master
GO
GRANT VIEW DEFINITION ON master.sys.syslogins TO ocmb_admin

Error - Definition privilege is not compatible with object.

C:

USE master
GO
GRANT VIEW DEFINITION ON master.sys.syslogins TO ocmb_grp_admin

Same as B

D:

USE master
GO
GRANT VIEW DEFINITION ON LOGIN::ocmb_admin TO ocmb_admin

Can be executed, if ocmb_admin is also created on master-db, but does not have any effects on querying.

Thanks for hints!


texts: What happens behind the scenes when adding a nullable column in SQL Server?

texts: This question came up today. When you add a column that accepts null values to the end of a table what exactly happens? Does the table get fully rebuilt similar to what happens when you change column order?


texts: Why does NOLOCK make a scan with variable assignment slower?

texts:
I'm fighting against NOLOCK in my current environment. One argument I've heard is that the overhead of locking slows down a query. So, I devised a test to see just how much this overhead might be.

I discovered that NOLOCK actually slows down my scan.

At first I was delighted, but now I 'm just confused. Is my test invalid somehow? Shouldn't NOLOCK actually allow a slightly faster scan? What's happening here?

Here's my script:

USE TestDB
GO

--Create a five-million row table
DROP TABLE IF EXISTS dbo.JustAnotherTable
GO

CREATE TABLE dbo.JustAnotherTable (
ID INT IDENTITY PRIMARY KEY,
notID CHAR(5) NOT NULL )

INSERT dbo.JustAnotherTable
SELECT TOP 5000000 'datas'
FROM sys.all_objects a1
CROSS JOIN sys.all_objects a2
CROSS JOIN sys.all_objects a3

/********************************************/
-----Testing. Run each multiple times--------
/********************************************/
--How fast is a plain select? (I get about 587ms)
DECLARE @trash CHAR(5), @dt DATETIME = SYSDATETIME()

SELECT @trash = notID  --trash variable prevents any slowdown from returning data to SSMS
FROM dbo.JustAnotherTable
ORDER BY ID
OPTION (MAXDOP 1)

SELECT DATEDIFF(MILLISECOND,@dt,SYSDATETIME())

----------------------------------------------
--Now how fast is it with NOLOCK? About 640ms for me
DECLARE @trash CHAR(5), @dt DATETIME = SYSDATETIME()

SELECT @trash = notID
FROM dbo.JustAnotherTable (NOLOCK)
ORDER BY ID --would be an allocation order scan without this, breaking the comparison
OPTION (MAXDOP 1)

SELECT DATEDIFF(MILLISECOND,@dt,SYSDATETIME())

What I've tried that didn't work:

Running on different servers (same results, servers were 2016-SP1 and 2016-SP2, both quiet)
Running on dbfiddle.uk on different versions (noisy, but probably
same results)
SET ISOLATION LEVEL instead of hints (same results)
Turning off lock escalation on the table (same results)
Examining actual execution time of the scan in the actual query plan
(same results)
Recompile hint (same results)
Read only filegroup (same results)

The most promising exploration comes from removing the trash variable and using a no-results query. Initially this showed NOLOCK as slightly faster, but when I showed the demo to my boss, NOLOCK was back to being slower.

What is it about NOLOCK that slows down a scan with variable assignment?


texts: MongoDB scripts triggered by cron - how to achieve reasonable password security

texts:
We would like to do nightly purging of old data out of our mongo cluster. First thought is to write shell script which connects to one of the mongos and issues a remove instruction.

evalString='db.fs.files.remove({uploadDate: {$lte : ISODate("2017-01-01")}})'

mongo localhost:1234/databaseName -u USER -p PWD --eval $evalString

This works fine but of course I don't like to have plaintext PWD in scripts.  At a minimum could chmod 500 on it but still feels sub-optimal to have plain text pwd in script.

What are the best alternatives?

I'm running Mongo 3.4.4 on RHEL 7.3. It's the non-Enterprise version of Mongo so no LDAP/Kerberos.  we are not using x509 security either and I'm looking for alternatives that don't involve upgrading to Enterprise or going down x509 route, although I'd be fine with hearing why doing those things really is the right thing to do and also without doing those things what the gaps are that get left open.


texts:
Using version 2.6 (I know...)

My goal is to test a failover.
There are 3 nodes in a replica set.

- node1:Master
- node2:Secondary
- node3:Secondary

I shut node1 and node2 down. Where node3 is a secondary, in READ-ONLY mode.

Because the application requires changes to be written to a mongo node, the config is changed of node3 to leave the Replica set and act as a single node. Now node3 is accepting writes. This is fine so far.

When re-enabling the replica set, node3 isn't set as master but as secondary, therefore it loses the writes and tries to catch up with node1 or node2, which is a master. This is the part where I lose my data.

The question is:

How can I ensure no data is lost on node3 and enable the replica set agin


texts: query using the where clause if variables are not null

texts:
i am trying to query without success, what i am trying to do is that

  ALTER PROCEDURE [dbo].[spPagination] -- ORDER BY id
  @filterCol NVARCHAR(20) = NULL, --<<<<
  @filterValue NVARCHAR(40) = NULL, --<<<<
  @PageNumber INT,
  @PageSize   INT
AS
BEGIN
  SET NOCOUNT ON;
        SELECT      Emp.id , Emp.email, Emp.[firstName], Emp.[lastName], Emp.[salary], Emp.[startDateWork], Emp.age  , Rol.[name] as Role
    FROM        [dbo].tblEmployees5m    Emp
    inner join  [dbo].[tblRoles]        Rol
    ON          Emp.roleId = Rol.id
    WHERE       @filterCol like '%' + @filterValue + '%' --<<<<
    ORDER BY    id
    OFFSET      @PageSize * (@PageNumber - 1) ROWS
    FETCH NEXT  @PageSize ROWS ONLY OPTION (RECOMPILE);

    select count(1) as totalCount from [dbo].tblEmployees5m
END

i am trying to apply filter to the result, according to @filterCol and @filterValue - if they are not NULL then I want to return the results with the where clause (which don't work now).

ELSE, if there 2 values are NULL then don't apply the where clause.
@filterCol will hold the column name.
@filterValue will hold the column value.

how can I achieve that? there is a better way?


texts:
I'm having a problem connecting using my dedicated admin connection in SQL Server 2014. I just wanted to test it out.

I have DAC enabled in sp_configure. I have performed RECONFIGURE.

I have prefixed my connection string with ADMIN: in SSMS Connect To Server box.

I have not put a space between ADMIN: and the server name. The server name works just fine so long as I don't prefix it with ADMIN:

I am not trying to connect to object Explorer. I am attempting connection when I open a new query window.

I am using Windows Authentication, which works fine when not prefixing ADMIN: to the server name.

Its not a firewall issue, as I've tried it with the firewall turned off.

Having checked the SQL Server error log it says "dedicated admin connection support was established for listening remotely on port 1434". Does that mean it is connected? If so, why did I get the error below?

I've verified TCP port connectivity with the Powershell script mentioned by Dan Guzman, and it works fine. Powershell returns the message "server listening on TCP port 1434"

I am getting the following error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.)
(Microsoft SQL Server, Error: 11001)

No such host is known

Your help would be appreciated.


texts:
In my stored procedure, I have table variable with a lot of columns. Because I want to simplify the stored procedure I thought i could declare table variable in function like

CREATE FUNCTION [dbo].[ufn_DeclareMaterialTableVariable]
(
)
RETURNS @returntable TABLE
(
    [Id] [int] NULL,
    [Number] [int] NOT NULL,
    [Name] [nvarchar](255) NULL,
    ... dozens of columns
)
AS
BEGIN
    RETURN
END

but i realized i don't know, how to use it. My idea was something like

select *
into @table
from [dbo].[ufn_DeclareMaterialTableVariable]

but this is not gonna work, unless i use temporary table.

The second idea is to declare table with custom data type.

declare @table TABLE as CustomTableType

but this is also not working. Any idea ?


texts:
I am trying to dynamically create trigger for a particular row instead of for each row. So if a table has 1000 rows and I want notification only on 25 rows, I am creating triggers like:

CREATE TRIGGER Trig
AFTER UPDATE ON Sells
FOR EACH ROW
WHEN (row_id="something")
    EXECUTE PROCEDURE sendNotif();

So with this only when my particular row is edited only then my sendNotif() is called.

Is this a good approach ?? Is there a benefit when compared to trigger on the whole table ??

I am doing this because I want to send notifications only for rows which have a subscription.

EDIT: Scenario explained:

So I have two tables SELLS and SUBSCRIPTION. When a user wants to subscribe to sells resource (row), it creates a subscription resource (row). Once subscription resource is created, notification needs to sent whenever CRUD operations are performed to the subscribed-to (Sells) resource. So subscription resource is created once and after that notification is sent based on events (CRUD) on Sells resource. That is why trigger was on Sells resource to track it's changes.


texts: MySQL 5.6 Slaves Fail to Reconnect to older MySQL server when it goes down for backups

texts:
I have an existing replication setup with Masters and Slaves running MySQL 5.0. A long existing script runs at night which restarts MySQL on the master with skip-networking, runs a backup, and then restarts again without skip-networking. The slaves lose connection but then retry to connect every 60 seconds until they successfully connect a couple of minutes later.  Here's what the log looks like on an existing slave:

150520  4:12:01 [Note] Slave: received end packet from server, apparent master shutdown:
150520  4:12:01 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.<binlognumber>' position <binlogpos>
150520  4:12:01 [ERROR] Slave I/O thread: error reconnecting to master '<user>@<host>:<port>': Error: 'Lost connection to MySQL server at 'reading initial communication packet', system error: 111'  errno: 2013  retry-time: 60  retries: 86400
150520  4:14:01 [Note] Slave: connected to master '<user>@<host>:<port>',replication resumed in log 'mysql-bin.<binlognumber>' at position <binlogpos>

I introduced a few new slaves that are running MySQL 5.6. They are able to replicate the existing databases without any problems or errors and data is consistent. Everything seems to be working except at night when the original Master goes offline for backups the new servers fail to reconnect on their own.

This is what the log looks like on the new servers in the morning:

2015-05-20 04:12:01 32401 [Note] Slave: received end packet from server due to dump thread being killed on master. Dump threads are killed for example during master shutdown, explicitly by a user, or when the master receives a binlog send request from a duplicate server UUID <newslaveUUID> : Error
2015-05-20 04:12:01 32401 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.<binlognumber>' at position <binlogpos>
2015-05-20 04:12:01 32401 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2015-05-20 04:12:01 32401 [ERROR] Slave I/O: error reconnecting to master '<user>@<host>:<port>' - retry-time: 60  retries: 1, Error_code: 2003
2015-05-20 04:13:01 32401 [ERROR] Slave I/O: error reconnecting to master '<user>@<host>:<port>' - retry-time: 60  retries: 2, Error_code: 2003
2015-05-20 04:14:01 32401 [ERROR] Slave I/O: error reconnecting to master '<user>@<host>:<port>' - retry-time: 60  retries: 3, Error_code: 2003
2015-05-20 04:15:01 32401 [ERROR] Slave I/O: error reconnecting to master '<user>@<host>:<port>' - retry-time: 60  retries: 4, Error_code: 0
2015-05-20 04:16:01 32401 [ERROR] Slave I/O: error reconnecting to master '<user>@<host>:<port>' - retry-time: 60  retries: 5, Error_code: 0
2015-05-20 04:17:01 32401 [ERROR] Slave I/O: error reconnecting to master '<user>@<host>:<port>' - retry-time: 60  retries: 6, Error_code: 0

All I have to do is go into the servers and issue a STOP SLAVE; , START SLAVE; and everything starts right back up again without trouble. It just won't do it on its own.

If I replicate between 5.6 servers and one goes offline the 5.6 slaves can reconnect to the 5.6 master just fine.

The replication user has only replication client and replication slave privileges on everything. The Connect_Retry value is 60 on both new and old slaves.


texts: pypl store function to update a record is not working

texts:
I am using PostgreSQL 9.4.7 and Python 2.7.6. I am writing a plpython function to update a row in user table and my code is as below:

CREATE FUNCTION update_user(myid int, mymail text, myname text) RETURNS text AS $$
    from plpy import spiexceptions
    plan=plpy.prepare("UPDATE auth_user SET email=$2, username=$3 WHERE id = $1",
    ["int"] ["text"]["text"])
    rv=plpy.execute(plan, [myid,myemail,myusername])
    return rv
    $$ LANGUAGE plpythonu;

I am able to create this function successfully in postgres DB but while I am trying to execute it via below command on postgres shell:

select update_user(1,"xyz@xyz.com@sifymail.com","updatedname");

I am getting following error:

ERROR:  column "amarshukla@sifymail.com" does not exist<br>
LINE 1: select update_user(1,"amarshukla@sifymail.com","hell");

Can someone point me where am I making a mistake?


texts: How to calculate a percentage over a dimension attribute and get correct aggregates?

texts:
We have a fact table with the weight trasported by truck/driver/day.

And a dimension with the max weight per truck.

We would like to have the % of weight transported, so we started with

create MEMBER CURRENTCUBE.[Measures].[% WT]
 AS [Measures].[Weight]/[Dim Truck].[Max_weight].currentmember.properties("key"),
FORMAT_STRING = "Percent";

This only works for individual rows where one truck has only one leaf for the specific day and driver.

It doesn't work if a truck does two trips in a day, instead of sum(weight)/sum(max_weight) or if you prefer sum(weight)/(n*max_weight) what we get is sum(weight)/max_weight

Exactly the same occurs at any aggregate level. For example, month or year or total. Or simply at truck level.

We have been playing with all sorts of formula and scopes without success.

Any idea how to define it so the aggregates work as expected?


texts:
I am currently re-designing some of DB's for a project. There is one DB in particular that is giving me a little of a headache. The idea is that for every client_id and date (client_id + date would be the Pri. Key) each column would contain the usage for each product that we offer (around 12 products, and for each one Cost, Margin, Total needs to be stored). I want to do domething like this:

+-----------+------------+--------------+-------------+-------------+------+--------------+
| client_id |    date    | prod_1_Total | prod_1_Cost | prod_1_Paid | .... | prod_14_Paid |
+-----------+------------+--------------+-------------+-------------+------+--------------+
| aa1       | 2014-01-01 | 245          | 54.97       | 50.58       | ...  | 431.23       |
| ...       | ...        | ...          | ...         | ...         | ...  | ...          |
+-----------+------------+--------------+-------------+-------------+------+--------------+

We have to note that for a record to exist that client_id has to have had some activity in the given date, in other words, given a client_id and a date, the rest of the columns can't be all NULL, otherwise that record won't be on the table.

It seems to me a bad practice to merge all this products in one table like above, specially, when for a given client_id and date many of them will be NULL.

But the alternative that occurs to me, creating separate tables for each of the products, presents the problem that to calculate for example total_paid for all producst for a client_id across some dates, we have to do 14 left joins, moreover if we use lets say product_1 in the query as the main query and we left join against it the result would be incorrect since most-likely the for some days the client used product_x and not product_1 (Full-outer joins are not supported in MySQL)

What design strategy would you recommend?

Thanks for your suggestions.


texts:
For months now, the PLE on one of our servers has hovered around the 2 million seconds. If varied slightly from day to day but was pretty stable there.
This last weekend, we added 12 GB of virtual RAM and 1 virtual CPU core to the server.  We did not change the maximum used RAM in SQL Server to match the new RAM nor did we allocate the new CPU core to SQL Server.
Since this was done, our PLE has fluctuated wildly, going between 50 and 4 million seconds every 10-30 minutes. The changes are not a slow rise or fall. The metrics go straight from very low to very high and vice versa in less than a minute.
Our overall wait times for the server are fine. Latches are normal. Buffer and plan cache sizes haven't changed. There doesn't seem to be any consistent pattern of a specific query or type of query draining the resources.
I've never seen PLE do this before. Can someone point me to what I may be missing or need to look deeper into?

Additional information from comments:

We are at 5 CPUs total but only using 3 (we were at 4 using 3).
Our total memory is 49GB and SQL's max is 28GB.
We are using VMWare with an x64 OS (Windows 2008).
There are 14 User databases on the server with the primary one being around 250GB.
Buffer Cache Hit Ratio has stayed around 98+% since this all began.
The server power plan is set to Balanced (not High Performance); however, that has not changed in several years. With that said, I completely agree it should be High Performance.
Neither the SQL Server Error nor Windows Event Logs are showing anything out of the ordinary.
The activity on the server has not changed in the last several weeks.
The server is NUMA aware. The MAXDOP is 4, with a cost threshold of 10.


texts:
I have a requirement for a logon trigger. I need to capture the SQL query that is coming to SQL Server from an Excel Pivot file.

Currently I am using the following code:

CREATE TRIGGER [MyLogonTrigger] ON ALL SERVER FOR LOGON
AS
BEGIN
    IF PROGRAM_NAME() <> 'Microsoft%'

    DECLARE @sqltext VARBINARY(128)
    SELECT @sqltext = sql_handle
    FROM sys.sysprocesses
    WHERE spid = @@spid

    DECLARE @SQLQuery varchar(MAX)
    set @SQLQuery = (select TEXT
    FROM sys.dm_exec_sql_text(@sqltext))

    INSERT INTO TestDatabase.dbo.LogonAudit
             (
                    ... audit columns ...
             )
       Select
                    SUSER_SNAME()
                    ,GETDATE()
                    ,@@SPID
                    ,PROGRAM_NAME()
                    ,ORIGINAL_DB_NAME()
                    ,HOST_NAME()
                    ,client_net_address
                    ,@SQLQuery
   FROM sys.dm_exec_connections
   WHERE session_id = @@SPID
END;

The problem I am facing is that @SQLQuery is the Create Trigger definition code every time, and not the actual query that is passed to SQL from the Excel file when the pivot is refreshed.

I think this has something to do with the fact that the login and the actual select query execution happens on two different transactions (SPID's).

Any idea how I can get the actual select query which excel uses to retrieve the data in a login trigger?


texts: Combining Two Base Tables with a 1:M relationship Into One View with Distinct Rows

texts:
I have two tables, Students and Race. Each student in the Students table has one row. Each student in the Race table has multiple rows, one for each race.

I'm trying to combine the tables into a view where I have one row for each student and each of their races are listed in separate columns; for example:

Any help or advice is greatly appreciated!


texts: Why are column store databases the new thing now?

texts:
Column store databases have several advantages over traditional databases - most notably compression and faster seek. Column store seem to be the new fad for databases in the past few years, with HANA as the most obvious example, but Microsoft and Oracle (and others) following suite.

However, the idea of column store databases are not new:

  TAXIR was the first application of a column-oriented database storage
  system ... in 1969

(wikipedia)

Why has it taken until the last 8 years for column databases to take off? Surely compression in database was hugely useful in the 1970's when hard drive storage was not as dirt cheap as it is now (and faster seek never hurts). Is there another development in the last decade that enabled column store databases?


texts: How do I stop a query from running if the row is locked?

texts:
I have a section of code that locks the database, updates it and then confirms.

This is all working fine, if another user attempts to update the same row they cannot and their changes are discarded.

My problem is that there is a variable that is updated each time the query is run, this increases whether the query was successful or not.  So what I need is the query NOT to run in the first place if the row is locked, how do I do this?

James


texts:
Let's say that someone wants to follow sage advice and properly setup TempDb and TempDb's log. But let's also say that the chosen sizes are almost certainly too big/aggressive. E.g. pretend we've got some extremes where TempDb's log is filling up a 250GB drive while there are 8 equally-sized TempDb files filling up a 1TB drive.
Is there a way to tell from a query that the sizes are "too big" after the server's been running under "normal" load for say a week or month? I.e. how would one gather enough information to make a reasonable, educated guess that a 25GB log and total 120GB of TempDB files are sufficient?
Or is there no other way except to shrink them down to "almost certainly too small", turn on auto-growth, and hope performance doesn't suffer too much for a few weeks while they grow to "proper sizes"? ...'cause that sure would suck.


texts:
I have 15 databases running MariaDB with roughly 1000 tables each. Nightly I run mysqlcheck to repair and optimize the tables which takes around an hour. I've tail -F'd the log before and it looks like it runs through around 900 tables at once, outputs it's finding, then does a bunch more. I decided to run the output through a timestamper (ts from moreutils) so I can verify this and see which tables take the longest and potentially clean up unnecessary data. The output from the command shows the first table takes 13 minutes, then it does 900 tables at 0 seconds each, then it stops for 13 minutes on another table. None of the tables it stops on are particularly big nor slammed.
So my question is, is mysqlcheck buffering it's output and is there a way to prevent it from doing so, or am I looking at writing a manual script?

Command: /usr/bin/mysqlcheck -p<PASSWORD> -A --auto-repair -o

Server version: 10.0.29-MariaDB SLE 12 SP1


texts:
I am using SQL Server 2012. We have a table that specifies the incoming requests to be processed. Some of the columns are (total columns are 10),

requestId
requestStatus (Enum - Processed, NotProcessed, Failed, etc)
requestedTime
requestCompletionTime

The query to pick the new requests is

select top 10000
from RequestProcessingTable
where requestStatus = 'NotProcessed'
order by requestId

The total number of rows in this table is 300 million.

We have 2 indexes, one clustered (requestId - primary key of this table) and one non-clustered (requestStatus).

Since we want to process the requests based on the requestId we need to do the order by. When we introduce the order by it is taking 3 times more time to select. Is there a best way to create indexes or do it in a different approach so that the performance of the SQL query can be improved.

Thanks a lot for your help in advance.


texts: MySQL Cluster: Problem Connecting the SQL node

texts:
In a MySQL cluster I have three hosts, one with a manager node, and the other two are each both a data node and sql node. Connecting to the manager is possible, however only as [ndbd] and not [mysqld]... Meaning they connect to the manager as data nodes but not as sql nodes. Everything looks as it should other than the "not connected" part, and I receive no error messages. So my question is why is the manager node recognizing each machine as only a data node and not as mysql node as well?

Below you can see the ndb_mgm cluster configuration followed by the contents of config.ini:

    Cluster Configuration
    ---------------------
    [ndbd(NDB)]     2 node(s)
    id=2     @10.0.40.105    (mysql-5.5.25 ndb-7.2.7, Nodegroup: 0, Master)
    id=3     @10.0.40.100    (mysql-5.5.25 ndb-7.2.7, Nodegroup: 0)

    [ndb_mgmd(MGM)] 1 node(s)
    id=1     @10.0.40.119    (mysql-5.5.25 ndb-7.2.7)

    [mysqld(API)]   2 node(s)
    id=4 (not connected, accepting connect from any host)
    id=5 (not connected, accepting connect from any host)

config.ini:

    [ndbd default]
    # Options affecting ndbd processes on all data nodes:
    NoOfReplicas=2    # Number of replicas

    [tcp default]
    # TCP/IP options:

    [ndb_mgmd]
    # Management process options:
    hostname=10.0.40.119          # Hostname or IP address of MGM node
    datadir=/var/lib/mysql-cluster  # Directory for MGM node log files
    NodeId=1

    [ndbd]
    # Options for data node-1:
                                    # (one [ndbd] section per data node)
    hostname=10.0.40.105            # Hostname or IP address
    datadir=/usr/local/mysql/data   # Directory for this data node's data files
    NodeId=2

    [ndbd]
    # Options for data node-2:
    hostname=10.0.40.100           # Hostname or IP address
    datadir=/usr/local/mysql/data   # Directory for this data node's data files
    NodeId=3

    #one [mysqld] per storage node
    [mysqld]
    [mysqld]

/etc/my.cnf:

    [mysqld]
    ndbcluster
    ndb-connectstring=10.0.40.119

    [mysql_cluster]
    ndb-connectstring=10.0.40.119

ps -ef | grep mysqld | grep -v grep


texts:
I'm writing an algorithm to populate all tables in a database in Postgres. I already get all tables that have no relationship populated, as follows:

I get all the tables with the following query:

SELECT table_name
   FROM information_schema.tables
WHERE table_type = 'BASE TABLE' AND
   table_schema NOT IN ('pg_catalog', 'information_schema')

For the list of tables obtained, I make a loop, and for each table I get its properties (column name, data type, accepts null or not, maximum characters):

SELECT COLUMN_NAME, DATA_TYPE,
   IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_NAME = 'TABLE_NAME'

With this list of properties I dynamically create the INSERT

INSERT INTO TABLE_NAME(prop1, prop2, prop3) VALUES(value1, value2, value3)

Each value is randomly generated based on the property type, eg:

Integer: 65422
Character: "Lorem ipsum..."
Date: 2016-12-12 20:00

And so on for each data type accepted by the algorithm. If it finds some kind of data that is not accepted, the application terminates (which is expected).

And finally, with INSERT mounted I execute the query in the database

The steps in this algorithm work perfectly for tables where there are no relationships.

For tables that have relationships (1:N, 1:1, N:N), I would need to first find all the foreign keys and understand which tables they see, to enter the data in this table "Father" save the IDs and then insert In the "Daughters" tables thus associating their foreign keys. But how to do it in a simple way?

It is interesting to note that I can already get the list of all foreign keys through the following query:

SELECT conrelid::regclass AS table_from
      ,conname
      ,pg_get_constraintdef(c.oid)
FROM   pg_constraint c
JOIN   pg_namespace n ON n.oid = c.connamespace
WHERE  contype IN ('f', 'p ')
AND    n.nspname = 'public' -- your schema here
ORDER  BY conrelid::regclass::text, contype DESC;

I am grateful for suggestion.

Note: The algorithm is being developed in Python.


texts: Find rows that have the same value on a column in MySQL

texts:
In an employee table, some rows have the same value for the reportsTo column.

employeeNumber | reportsTo
---------------|-----------------
1056           | 1002
1076           | 1002
1088           | 1056
1102           | 1056
1143           | 1056
...

I want to be able to show the employee numbers of employees who report to the same person as does a particular employee.

For e.g. I want to know the other employees who report to the same person as employeeNumber 1088

The output should be:

   | employeeNumber |
   |----------------|
   | 1088           |
   | 1102           |
   | 1143           |

What simple MySQL statement I can use to get this result?


texts: Migrating from one SQL Server 2008 R2 instance to another

texts:
I just started a new dba job and have been given a new server and told to move everything from the old server to the new one, brand new windows and SQL Server installations. I know I have to transfer user databases, logins, SSIS packages and maintenance plans.

Is there anything else I should be aware of to move. Both instances are SQL Server 2008 R2.
Is there an easier way to move the maintenance plans other than scripting each one and applying these to the new server?

Thanks for your help.


texts: The Logical Type of The Document in CouchBase

texts:
How should the logical type of a document get specified in CouchBase? Using a field for the type? Or employing separators in keys like product::app::123id?

Currently I'm putting a document type, inside the document itself, inside a string field named Type like say Product.

But I see this pattern of using separators in document id, like product::app::123id. Done some playing around it, couldn't get the type part (product) from the key (Of-course parsing it, by splitting is possible, which to me seems to have same overhead in both N1QL and views).

So how should the document type (app logic type) get specified?

Env: CouchBase Community, inside Docker on Ubuntu 14.04, using Go client gocb.


texts: AlwaysOn AG: Current Host Server - Owner Node - Primary Instance

texts:
I'm building up a SQL Server AlwaysOn AG and I'm on the step "failover", manual and automatic:

I noticed that in the Windows Server Failover Cluster a "Current Host Server" exists. My knowledge says that this is the "master" of the cluster that the cluster hosts. But now I noticed that the "Owner Node", which hosts the Availability Group(-role) a other node could be. (The owner node is also the primary instance of the AG, right?)

Now for me there exist two questions for the normal operating of an AlwaysOn AG:

Q1: Should the "Current Host Server" the same as the Owner Node of the AlwaysOn AG?

Q2: Imagine if I want to maintain a host. Shall I first failover the AG (via SSMS or with ALTER AVAILABILITY GROUP...) to the second node/another node and at second a failover of the WSFC-Current Host?

Maybe someone could help me with this questions and give me some advice.

Thanks


texts: Query on a SQL Server view with openquery to Hive returning wrong answer

texts:
I have a view in a SQL Server instance which is running an OPENQUERY to Hive through MS OLE DB Provider. The view is getting all the records from the Hive table without any WHERE clause.

Now, I suspect that a query on the SQL view to find MAX value on a column is sometimes returning wrong value. In any case, is it possible that the MAX query ran on the view even in case it wasn't able to fetch all the records from the Hive table, due to which the query returned wrong values?

View script:

CREATE VIEW [dbo].[session]
AS
   SELECT distinct
      a, b, c, d
   FROM
      OPENQUERY(Hive, 'select distinct a, b, c, d from HiveDB.HiveTable')

Query on view:

SELECT top 1
    a
FROM
    [dbo].[session]
ORDER BY
    a DESC

a is date in the format yyyyMMddhh of datatype int


texts: A database differential backup without a full backup

texts:
At 10 pm, I scheduled a SQL Agent job to do a daily backups with the schedule:

Daily Full: 22:00
Differential: Every 4 hours
Log: Every 30 minutes

Now when I check my backup set(let's say at 22:30), I see that a differential backup has happened without any full backup!!.

I was not expecting to see the backup job up and running before 16:00 tomorrow when the first full backup will take place, however, I have a differential backup without a full backup? How is that possible?


texts: How can I alter the primary key of an index-organized table?

texts:
We started with a naive primary key:

SQL> CREATE TABLE books(
        title VARCHAR2(10),
        CONSTRAINT pk_title PRIMARY KEY(title))
      ORGANIZATION INDEX;

Table created.

Then realized it is not unique. Say, a book can be re-released under the same title:

SQL> ALTER TABLE books ADD(release_date DATE NOT NULL);

Table altered.

Now to insert a re-released book, we have to relax the uniqueness on title somehow.

But I cannot drop and recreate the PK due to the table being index organized:

SQL> ALTER TABLE books DROP PRIMARY KEY;
ALTER TABLE books DROP PRIMARY KEY
*
ERROR at line 1:
ORA-25188: cannot drop/disable/defer the primary key constraint for
index-organized tables or sorted hash cluster

I cannot manage to extend the existing key in-place either:

SQL> CREATE UNIQUE INDEX pk_title_date ON books(title, release_date);

Index created.

SQL> ALTER TABLE books MODIFY PRIMARY KEY USING INDEX pk_title_date;
ALTER TABLE books MODIFY PRIMARY KEY USING INDEX pk_title_date
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.

What are my options ? Is it possible without moving data or recreating the table (the actual table is huge) ?


texts: Two identical MySQL tables, one not using indexes

texts:
I have one MySQL Server (5.0.37) on a Linux Server that have many databases. Some of them (12) have each an identical table to log sensors data.

Schema of one table

CREATE TABLE `measuresHistory` (
  `keyMeasureHistory` int(10) unsigned NOT NULL COMMENT 'Key of the measure.',
  `keyMeasurePoint` int(10) unsigned NOT NULL COMMENT 'Measure point associated with the value.',
  `date` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT 'Date of the measure.',
  `value` double NOT NULL COMMENT 'Value of the measure.',
  PRIMARY KEY  (`keyMeasureHistory`),
  KEY `iDate` (`date`),
  KEY `iKeyMeasurePoint_Date` (`keyMeasurePoint`,`date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8$$

Problem

One of the tables has incredibly long query time, doing the same query on other tables is extremely fast, example :

SELECT
    *
FROM acs_spectotechnologies_com_enerconcept_0007.measuresHistory
WHERE
    date >= "2013-05-01 00:00:00"
    AND date <= "2013-05-01 05:59:59"
    AND keyMeasurePoint IN(1,2,3,4)
ORDER BY
    date;

Result : 1440 rows, 0.001sec (23487659 total rows)

SELECT
    *
FROM acs_spectotechnologies_com_enerconcept_0005.measuresHistory
WHERE
    date >= "2013-05-01 00:00:00"
    AND date <= "2013-05-01 05:59:59"
    AND keyMeasurePoint IN(1,2,3,4)
ORDER BY
    date;

Result : 1440 rows, 3.703sec (11749327 total rows)

Doing the EXPLAIN command in MySQL show me that indexes are used for the table acs_spectotechnologies_com_enerconcept_0007.measuresHistory but not for the table acs_spectotechnologies_com_enerconcept_0005.measuresHistory.

I've tried to remove indexes, recreated them, no changes. The two tables are on the same MySQL server. Also tried to force using the index, without difference.

Anyone have an idea?


texts: Identical SQL Server databases hosted in the same physical DB server

texts:
We have hosted similar SQL Server 2014 databases TestDB and TESTDB_Copy on the same physical server. The two databases are almost the same and there may be minor differences in the number of records (less than 1000) in few tables.

One database is responding quickly and other is very slow. etc., Query in TestDB takes 1 sec, but TestDB_copy takes 5-10 seconds. We have ensured the indexes are similar in both the databases and the rebuilt the index in both the databases.

Queried master.sys.dm_os_performance_counters and sys.dm_exec_cached_plans and they are almost the same for both the databases.

What else would be the issue with the slow responding database (TESTDB_Copy)?

Finding 1) The clustered index seek is 16% in fast database and 27% in slow database.

The no of rows is close to 400k in the table undergoing index seek.


texts: In a recruiting website, should I force a choice between one table for jobseekers and employers or 2 separate tables?

texts:
I'm working on a recruiting web site and I was wondering what would be best between:

A User table, the employer accounts are linked to a company
(therefore when companyID is null then it's a job seeker)
Complete separate tables one for job seekers, one for employers.
That way on the long run, it's possible to optimize queries a bit
more if it becomes hugely successful.

Both approach have pros and cons:

With 1. it would be possible for exemple to create only one login form which would handle both job seekers and employers.

The data is the same but the entity is different.

I have mixed feelings. What do you think?


texts:
I know there are lot of articles with the same issue and I explored every other article trying everything and still this error persists.
Here are the steps I followed:

use admin.

created an admin user with role: dbAdminAnyDatabase

use mydb.

created an admin user with different name for mydb

created a readonly user with: db.createUser({user: "nameuser", pwd: "password", roles: [{role: "read", db: "mydb"}]});

setup a mongod.conf file with:

security:
authorization: enabled
net:
port: 12345
bindIp: 0.0.0.0   #default value is 127.0.0.1

then run the command: sudo mongod --port 27017 --dbpath ~/data/mongodb --config /etc/mongod.conf

and then on localhost i run the command to check:
mongo -u nameuser -p password localhost:12345/mydb

then the error come as:
connecting to: mongodb://localhost:12345/mydb MongoDB server version: 3.6.3 2020-10-04T03:05:37.054+0530 E QUERY    [thread1] Error: Authentication failed. : DB.prototype._authOrThrow@src/mongo/shell/db.js:1608:20 @(auth):6:1 @(auth):1:2 exception: login failed

and on server side the error looks like this:
2020-10-04T02:53:11.835+0530 I NETWORK  [listener] connection accepted from 127.0.0.1:60240 #3 (1 connection now open) 2020-10-04T02:53:11.835+0530 I NETWORK  [conn3] received client metadata from 127.0.0.1:60240 conn: { application: { name: "MongoDB Shell" }, driver: { name: "MongoDB Internal Client", version: "3.6.3" }, os: { type: "Linux", name: "Ubuntu", architecture: "x86_64", version: "18.04" } } 2020-10-04T02:53:11.836+0530 I ACCESS   [conn3] SCRAM-SHA-1 authentication failed for nameuser on mydb from client 127.0.0.1:60240 ; UserNotFound: Could not find user nameuser@mydb 2020-10-04T02:53:11.837+0530 I NETWORK  [conn3] end connection 127.0.0.1:60240 (0 connections now open)

Now please tell me if I can try anything new. I have tried so many articles from stackoverflow but none address the issue. Every resolved issue is the common mistake which I have checked already and I am not doing any of these. Tell me if you have any suggestion to work this out.


texts:
How to change CHAR (17 byte) to CHAR (16 byte) when there is already a data?

Values are something like this:

012345-000-00001

There is a space at the end.

I tried to trim it first before altering the column size but still gets the error due to column is already bigger than 16 byte (due the space at the end).

Thanks !


texts: Does Snapshot Isolation only give you what has been commited? (And General Snapshot Isolation questions.)

texts:
So, I have some deadlocking issues.

I have seen two options to resolve it.  Add read uncommitted to my db or do Snapshot Isolation and add read committed snapshot.

After doing some research into both, it seems to me that read uncommitted can allow reads of data that could possibly never get committed to the database.

On the flip side read committed snapshot will give only the latest (it is the latest right?) committed version of data (even if the data that may be in the middle of a change/transaction.)

Is this right?

If so, I am leaning towards Snapshot Isolation (I don't really want to return data that is in the middle of a change.)

However, my DBA told me that last he heard there were "problems" with Snapshot Isolation.  (He did not expand on the the problems were, just that it was not a Enterprise ready feature.)

So, here are my questions on Snapshot Isolation:

Is Snapshot Isolation robust?  Does it just work?

Or are there "gotchas" that I need to look out for?


texts:
I've been asked to check an entire database for all records with a question mark (?). I assume I can only do this on a table basis, is there any short cut or do I have to run

SELECT * FROM [table] WHERE [field] LIKE '%?%'

for each column?


texts: Find double qoutes inside a string which is in double quotes in mysql

texts:
I'm storing a string that includes, by necessity, double quotes, e.g.:

ID           Data
1   "This is "my" example."
2   "This is my example."

I want to find only those row which are like ID 1. How can i do this.

"This is "my" example." (I know it's hard to tell the difference here)

Please suggest.


texts:
I have a requirement is SQL Agent Jobs where their is a job called Job 4 which dependent on 3 other jobs say Job 1, Job 2 and Job 3. So this Job 4 has to be triggered whenever either one of the 3 jobs i.e. Job 1, Job 2 and Job 3 gets succeeded. Since days I have been breaking my head on this but unable to figure anything out. Can you please help me on this?

Thanks in advance
Manoj


texts: Passing a table name to a stored procedure

texts:
Once a new table is created, we have to execute the same SQL code segment, that grants some users rights to the table and does similar maintenance. This code is identical for all cases, differing only in the table name.

I wanted to write a stored procedure to abstract the entire set of statements into one stored procedure call, but I don't understand how to do it. For example, here is a simple one I created:

CREATE PROCEDURE dbo.spGrantTableRights
       @TableName varchar(255)
AS
BEGIN
    DECLARE @Name varchar(255)
    SELECT @Name = name
      FROM sysobjects
     WHERE name = @TableName

    GRANT SELECT            ON @Name TO mainUserName
    GRANT INSERT            ON @Name TO mainUserName
    GRANT DELETE            ON @Name TO mainUserName
    GRANT UPDATE            ON @Name TO mainUserName
    GRANT DELETE STATISTICS ON @Name TO mainUserName
    GRANT TRUNCATE TABLE    ON @Name TO mainUserName
    GRANT UPDATE STATISTICS ON @Name TO mainUserName
    GRANT TRANSFER TABLE    ON @Name TO mainUserName
END

But passing a string table name does not help -- I don't get a system object back. How do I convert this string into the system object? Or is there a different better way to do this altogether?

Note I understand dynamic SQL will work, but it makes code both ugly and unreadable. Is there a standard way to deal with this problem outside of the dynamic SQL framework?


texts: Does a NULL DATE use as much storage as a value?

texts:
I need to store a DATETIME value that may or may not have a date.
My options are:

| starts DATETIME     |
|---------------------|
| 0000-00-00 12:38:06 |

| starts_date DATE | starts_time TIME |
|------------------|------------------|
| NULL             | 12:38:06         |

If a NULL DATE value takes 0 storage, I may save a few bytes on most records (8 bytes on full DATETIME vs 3 bytes on just TIME). However, the following pages say that NULLs still take as much storage as the actual values on the fixed length columns, such as CHAR vs. VARCHAR. Is the DATE considered fixed-length too?

The byte size of NULL Decimal(19,4) or DATETIME (this is for SQL Server though)
Does an empty column value occupy same storage space as a filled column value


texts: How to create a trigger that will insert values into another table twice?

texts:
I'm trying to create a trigger that will insert two values into another table.
From table booking to table flight;

Here is my code for the booking table:

CREATE TABLE Booking (
Booking_ID            NUMBER(10)   NOT NULL PRIMARY KEY,
Company_ID            NUMBER(10)   NOT NULL REFERENCES
Penrhyn_Jet_Charter(Company_ID),
Customer_ID           NUMBER(10)   NOT NULL REFERENCES
Customer(Customer_ID),
Aircraft_ID           NUMBER(10)   NOT NULL REFERENCES
Aircraft(Aircraft_ID),
Assignment_No         NUMBER(10)   NOT NULL,
Booking_Date          DATE         DEFAULT SYSDATE,
Charter_Cost          NUMBER(14,2) CHECK(Charter_Cost > 0),
Departure_Date        DATE         NOT NULL,
Departure_Location    CHAR(3)      NOT NULL,
Arrival_Date          DATE         NOT NULL,
Arrival_Destination   CHAR(3)      NOT NULL
);

Here is my code for table flight:

CREATE TABLE Flight
(
Aircraft_ID  NUMBER(10),
Flight_Date  date,
constraint pk_flight primary key (aircraft_id, flight_date)
);

The point of the trigger is to check for double bookings, so the Aircraft_ID and Departure_Date will be taken to the flight table as one entry.

Then the Aircraft_ID and Arrival_Date will be taken to the flight table as another entry.

if any of the entries were to be the same, then it would result in an error as in table flight, Aircraft_ID and Flight_Date are composite primary keys, at least that's the idea anyway.

So I'm stuck here trying to create a trigger:

CREATE OR REPLACE TRIGGER Check_Double_Booking
BEFORE INSERT OR UPDATE ON Booking
FOR EACH ROW
BEGIN

Any ideas? Thank you!


texts: How do I interpret the log when I run DBCC TRACEON (3502, 3504, 3605, -1)

texts:
I have been using DBCC Traceon (3502, 3504, 3605, -1) because it was recommended in a blog for discovering performance issues related to I/O.  I'm running MS SQL Server 2008 R2 SP1

Results in my SQL Log file look something like this (numbers fudged a little):

  about to log checkpoint end

  last target outstanding 2, avgWriteLatency 40ms

  Average Throughput: 0.67 MB/sec, I/O Saturation: 79, Context Switches
  201

  FlushCache: cleaned up 125 Bufs with 69 writes, in 1447ms (avoided 0
  new dirty bufs)

  Ckpt dbid 9 phase 1 ended (8)

  about to log checkpoint begin.

I don't really know how to read this, or break it down in a way that I get get anything truly meaningful out of it.

What does 'last target outstanding mean?"

Does the average write latency mean the overhead time it takes per write?  or the time between writes?  40ms seems high, the physical drive is a 1TB, and it's RAID5 configured.

What is I/O saturation?

What does it have to do with the Context Switches.  I'm assuming Context switches have something to do with multi-tasking.  Changing between jobs/writes.

FlushCache.  I realize this has to do with clearing out the cache.  What are the Bufs? Are these pages of data that needed to be written?  What are the dirty Bufs?  Why would they be avoided?

A detailed breakdown would be appreciated.


texts: Multiple AG groups complicated set up question

texts:
Suppose I have two AGs:

AG #1

Server A (primary)
Server B (replica)

AG #2

Server C (primary)
Server D (replica)

Now I need Server F to have all the DBs from server A and C. Is it actually possible to have Server F serve as a replica in both AG #1 and AG #2?

Is this as simple as adding both the listeners (from AG #1 and AG #2) on server F?

Currently, servers on AG#1 and AG #2 are in two different WSFCs. Also, I need to add all the listeners from diff AGs to server F? In other words, server F can be the replicas server of 4 different set of AGs that is made of 8 other servers(2 node per set)?

Is there a limit to how many AG groups can be in server F for 2012 Enterprise?


texts: Review the ER diagram regarding a National Football Team

texts:
We need to store information pertaining to football players who are or
  were members of the national team.

  For each player we store the following: first name, last name, date of
  birth, debut date and the nominal position of each player.

  With each player is associated information about the history of his
  performances in matches, namely: date of the match, number of minutes
  on the field, shot goals, and penalty cards.

  Also information of his club carrier is stored. We would like to know
  which club he played for and for how long.

  In our system we also store information concerning matches of the
  national team. For each match we keep the following information: date,
  place, result, name of the opponent, name of the main referee, type of
  a match (friendly, World Cup, European Championship, qualification to
  WC or EC tournament), and names of players who shot goals.

Please, review my ER diagram.

Should "Type of match" be a separate entity?

I have included some extra attributes to the entities which were not described in the original description of the case-study.

I have used "Look Across" min-max notation.


texts: Translating ERD diagrams into SQL DDL statements

texts:
I am currently establishing a database for Students and Courses using ERD.  The style of ERD I am using is similar to the diagram below from the ERD Diagram Question:

Note: The picture above is NOT my ERD, this is just the style of ERD I am using.

A simple CREATE statment could be used for the attributes of each entity, but what other DDL statements enforce relationships, weak entities, and multi-way relationships?


texts: Storage accounts not showing up when creating Azure Storage Credential

texts: I'm trying to setup a Back Up Database Task in a Maintenance Plan on SQL Server 2017 (Web Edition) using SSMS 18.6 and when I get to the step to create an Azure credential I select my management certificate and subscription but when I try to select a storage account it refreshes, but no storage accounts show up. I've confirmed I have 2 storage accounts in that subscription, but they are not showing up for me to select them.