texts: Alias for multiple instances on a SQL Server

texts:
I have 2 SQL named instances on a SQL Server, trying to add aliases for both so that application teams can use the alias names in their connection strings.

There are 2 IP Addresses assigned for the server. 10.10.1.1, 10.10.1.2

I've assigned 10.10.1.1 to ServerName\Instance1 and 10.10.1.2 to ServerName\Instance2.
Ports for the instances are 1433 and 1435 respectively

I have created 2 aliases from the SQL Server Configuration Manager:
Test1, Test2

A DNS entry has also been created with IP Address specified for each alias.

Connections work only with Test1. I or the application team is unable to connect to Test2 instance.

How do I fix this?


texts: Getting all databases which a sql logon can connect

texts: I need to know all databases in a SQL Server on which a user can connect. With sys.databases I get all databases and with sys.server_principals or sys.syslogins I get all server logons. But I can't find a table which contains a connection between these tables. Has anyone an idea how to solve this? Thanks.


texts: Mirroring: Microsoft SQL Server, Error: 1431

texts:
We had a mirroring currently going to DR on SQL Server 2014. The server to the principal crashed and database was restored to new server. We are trying to proceed with the mirroring however upon click of "start mirroring" button, we are getting this error:

  Neither the partner nor the witness server instance for database "DBName" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)


texts:
I am trying to trick an application into executing a DML statement AFTER starting a SELECT statement without using stacked queries so I cannot use the ; character anywhere in my query. I have resorted in using the WITH keyword but while it works OK in the

select 2,
    case when (WITH a as (select 1 as b) select b from a)=1
    then 'one'
    else 'two' end

scenario it fails when I try an insert into such as

select 2,
    case when (
        WITH a as (insert into mytable(fld) values('b from with')) select fld from a)=1
    then 'one'
    else 'two' end

because DML statements in WITH need to be at the top layer. I have also tried using it in a UNION statement in hopes that the UNION will cancel out everything and it will be assumed as a first level statement like

select null union all (
    WITH a as (insert into mytable(fld) values('b from with')) select fld from a)

but I get the same top-level error again.

Can anyone think of a way to achieve my goal either using the WITH keyword or any other way. My requirements are

must start with select and
can't contain ;

I know I can use the SELECT INTO to put data on tables, I just want more freedom as in UPDATE and DELETE.

The application imposes a filter that says the SQL command must start with a SELECT clause and not contain a ; in it. For me to wrap my DML in a function and call that I would have to use CREATE FUNCTION somehow and that would break the rules.

I cannot connect to the database directly, only through an app. My main use case is trying to circumvent the filter imposed by that app. The app is a CMS like application; however someone decided it was a good idea to add a SQL client to it as well so an application admin can query the database directly. To keep users from trashing the database behind it, a filter was added on that SQL client. Initially it was a must start with SELECT only and later on the ; character was added as well to enhance it. The app is not Open Source.

As a process it is called security research. At the end of it the devs will be notified and someone will ship a better product - for free - and I will have learned something in the process. Just as they have been notified of the stacked queries and closed that door as well and as they will be notified of the select into shortcoming.


texts:
I'm trying to replicate a SQL database but I want to apply a filter based on date.
The database has more than 6000 tables and all of them have a datetime column (stamp) with the same name "usrdate".
Is it possible to apply a filter to be applied to all columns?
The goal is to have multiple databases in the replication site with data between periods.
Thanks


texts:
I’ve been trying to find out if the same CPU limitations database mirroring has apply to Availability Groups. My guess is that the same limitations exists, given that Availability Groups was built on the same technology as Mirroring, so the following would still apply.

Principal server: 1 global thread and 2 threads per each of the mirrored databases.
Mirror server:
– 64 bit architectures: 1 global thread, 2 threads per mirrored databases and one additional thread for each mirrored database for every 4 processor cores.

Is this correct?
Thank you!


texts: minimum space is required on disk to restore a backup in SQl Server?

texts:
Suppose I am going to restore a backup (2 GB) in my sql server, I have sufficient space (20 GB) in my disk, but is there any algorithm that SQL server follows to calculate the minimum disk space required or just randomly checks the disk space and the backup to do a successful restore.

As I want to know the internal working of SQL Server.

Thanks in Advance.


texts:
I want to find the best solution for an insert/update oracle function. My task is to collect some date from multiple tables (source tables) and bring them to a single table (destination table). Source tables getting inserts and updates in real time. My function must run ones/day to getting all new data and updated data to destination table. I identify my new / updated data by using a column (date) from source table based on a trigger witch update the date column with systimestamp at every insert/update.
My current code looks like this
for rec in (
select firstname, lastname, email, id
      from people_source
where people_source.date > last_run_date
)

loop

merge into people_destionation dest
  using ( select firstname, lastname, email, id from dual ) src
  on (src.id=dest.id)
 when matched then updated set
 firstname=src.firstname,
 lastname=src.lastname,
 email=src.email
when not matched then
insert
(
firstname,
lastname,
email,
id
)
values
(
rec.firstname,
rec.lastname,
rec.email,
rec.id
);

exception when others ....

This solution make what I want, but I tested for 100k rows. I have source tables with 100 mil. rows and I try to find the best performance solution.
I also read about "Switching between PL/SQL and SQL engines" and I think this applies to me. Will it be a better idea to try to make functions with BULK COLLECT and FORALL?
I have not a very long experience in pl/sql, so I need more opinions and I thing here is a good place. Thanks. Best wishes.


texts: sysadmin user can't connect to explicitly specified database

texts:
I noticed some Failure Audit errors, in the Windows Application event log of the server that my SQL Server runs on, stating:

Event ID: 18456
Login failed for user 'DOMAIN\username'.
Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

The user mentioned in the errors was in the sysadmin server role, and I checked that the user had a default database associated with it. The errors recurred every five minutes. Does anyone have any insight into this issue?

The SQL Server service itself and SQL Server Agent run as that account.


texts: What hardware to purchase for MySQL server? Details below

texts:
I am planning to build a basic server machine dedicated to running MySQL for my small business.
What I need the database for...
I will be setting up a database to hold records of registered voters in various states for business purposes. Each table will contain the records for registered voters in one state. The average number of records for each table will be approximately 6 to 7 million records. But the largest one will be approximately 17 million records.
I will only be doing 2 things with these tables. Querying them to search for a single record, then marking that record in a separate field to indicate that the record has been found.
I will have an average of 10 to 20 users (but as many as 100 users) simultaneously running the aforementioned queries. This should result in as few as 100 queries per hour, or as many as 10,000 queries per hour.
These queries need to return results as quickly as possible. Preferably in less than 1 second. Or even close to instantaneous as a user is typing in a search box on the front end.
I need to know what kind of hardware resources I should purchase with the above requirements in mind. What kind of cpu? How many cpus? How many cores? How much ram? How much disk space? How many drives? What kind of drives? Raid configuration? Also, how much internet bandwidth should I need?
Thanks for helping out a noob.


texts: How to activate REST services on Db2 Developer Edition

texts:
I am trying to activate the Db2 REST services on my local IBM DB2 Developer-C Edition (Community).

Using the Data Server Manager, I wasn't able to figure out how to configure and run sample job DSNTIJRS in the SDSNSAMP library (as described in the documentation (https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/restserv/src/tpc/db2z_createdbobjects4restserv.html).

Can somebody point me in the right direction?


texts: Columnstore index and low selective columns

texts:
I have a table with almost 1T rows.
create table bigtable (
  K1 int, K2 date, -- PK columns
  C1 ..., C8 ...., -- columns with various data types like float, date, varchar, ...
  B1 bit, B2 bit, ......, B10 bit -- 10 or so bit columns
  primary key (K1, K2)
)

I want to use columnstore to increase the query performance. There are the following options.

Create a cluster columnstore index
Create a noncluster columnstore index for all the columns
Create a smaller noncluster columnstore index for all the columns except these bit columns and other low selective columns?

The users will write queries themselves. A lot of the queries will be aggregation queries. Will the option 3 run as faster as the other two with smaller size?
I'm using SQL Server 2014. I can drop/recreate the nonclustered columnstore index periodically in my case because the data is not updated frequently.


texts: How to get a list of current Oracle CDBs on server

texts:
If I have a server with one or more CDBs, what command can I run on the server in order to obtain a list where each item uniquely identifies one of the CDBs?

(a "CDB" is an Oracle Container Database)

Note 1:
I realize that it is rare to have more than one CDB on a server.  But in that case I would still want to obtain the unique CDB name of that one CDB.

Note 2: I know that the command cat /etc/oratab will list a bunch of DB installations on the server.  But this includes installations that no longer exist.  I want a list of only the currently existing CDB installations.


texts: SQL "select" query for getting 0 instead of null?

texts:
In the database I have some value as null . So while fetching these null value from database it should be treated as 0 not as null.
Note:- I don't want to be it done by code but from SQL query.


texts: Sql Server Backup - Log shipping vs VM snapshot

texts:
Setup

I have Sql Server Standard running on an AWS VM

For one of my DBs on this server, I perform:

1x daily backup
And ship logs every 15mins to another DB Server VM, dedicated to log shipping for this DB.

Longer term, I had intended the log shipping VM to be a reporting DB and play a part in reducing load on the primary server. But this requirement is now secondary to saving money.

Recovery Criticality (RTO/RPO)

There is no offcial SLA. But the following is the general concensus: Recovering to within minus 1 hr of live data is seen as pretty much acceptable for this system. Although if we had to go to last night's backup then we would still survive, after taking some abuse. Recovery within 2 hours would be ideal, but up to 12 could be tolerated.

New approach to save money - VM Snapshots

I am considering switching off the log shipping VM and switching to snapshotting the production DB VM every 1hr. (This will save many $1,000s per year.). Note I am referring to full AWS EC2 VM EBS snapshots, not Sql DB Snapshots.

I have been very happy spinning up these VM snapshots in the past for non-critical investigations into old data. But I haven't relied on them as part of a DR plan.

Question

-Given the DB recovery profile I have alluded to above, would you switch to snapshotting, or do something else?
- Has anybody had issues spinning up old Sql Server VM Snapshots?

thanks

(This answer below was kind of helpful, but I would be interested in real-life experiences too.
Are VM snapshots enough to backup SQL?)


texts: Get all (non repeated) distribuidores with their paises given a producto_solicitud

texts:
I have this schema:

And as title says I need to get all records from fabricante_distribuidor by given producto_solicitud.id value. I'm doing in this way:

SELECT
    nomencladores.fabricante_distribuidor.*,
  nomencladores.pais.*
FROM
    nomencladores.fabricante_distribuidor,
    nomencladores.pais
LEFT JOIN negocio.distribuidor_producto_solicitud ON (
    negocio.distribuidor_producto_solicitud.fabricante_distribuidor_id = nomencladores.fabricante_distribuidor. ID
)
LEFT JOIN nomencladores.pais_distribuidor_producto_solicitud ON (
    negocio.distribuidor_producto_solicitud. ID = nomencladores.pais_distribuidor_producto_solicitud.distribuidor_producto_solicitud_id
)
WHERE
    negocio.distribuidor_producto_solicitud.producto_solicitud_id = 1

But get this error:

[Err] ERROR:  invalid reference to FROM-clause entry for table "fabricante_distribuidor"
LINE 8:> ...r_producto_solicitud.fabricante_distribuidor_id = nomenclado...
                                 ^
HINT:  There is an entry for table "fabricante_distribuidor", but it cannot be referenced from this part of the query.

I've tried also this one:

SELECT
    nomencladores.fabricante_distribuidor.*
FROM
    nomencladores.fabricante_distribuidor
JOIN negocio.distribuidor_producto_solicitud ON (
    negocio.distribuidor_producto_solicitud.fabricante_distribuidor_id = nomencladores.fabricante_distribuidor. ID
)
JOIN nomencladores.pais_distribuidor_producto_solicitud ON (
    negocio.distribuidor_producto_solicitud. ID = nomencladores.pais_distribuidor_producto_solicitud.distribuidor_producto_solicitud_id
)
WHERE
    negocio.distribuidor_producto_solicitud.producto_solicitud_id = 1

But this one return repeated results, any help?

I'm using PostgreSQL but assume that will be pretty much closed to any SQL statement.


texts: "Failed to load SQL modules into the database cluster" when installing PostgreSQL 12.3 in a drive other than C

texts:
When I try to install PostgreSQL 12.3 in a drive other than C, I get the following error message:

"Failed to load SQL modules into the database cluster"

I have previously raised a related issue (Permission denied to create new tablespace for PostgreSQL data), but no one has been able to help so far.

NOTE: Before running the pg installation program, I have made sure that the user 'NETWORK SERVICE' has full control on the folder (whole drive actually) where I want to save my pg data.


texts: Recommended Auto Grow settings suitable for database whose size is 867666.25 MB

texts:
I need recommendations for setting/modifying the auto grow options for our production database.

size of FUNDSDB  867666.25 MB

Also,
I have attached the current autogrowth statistics , looks like it is set to 200 MB which I think is considerably less considering the database size and also it should be modified to some realistic value else these auto-growth events can cause performance degradation for our current db transactions.

Can anyone suggest the auto growth settings best suitable for this heavy OLTP production database?
We are using SQL Server 2012 standard RTM version currently.
The main reason we are planning to change the autogrowth factor is that we have observed, we are experiencing frequent up to 11 times auto grow a day and also, many frequent CPU spikes.


texts: Postgres - caching of recently inserted data?

texts:
Does Postgres cache recently INSERTed data? And if so, how can I increase the amount?

Here’s why I’m asking. I have a process that continuously inserts simple text messages into a table at an average rate of several million records a day (and contains several months of data). I have a second process that reads recent records from this table for processing. I’ve noticed that if I SELECT (for example) 100 records that have arrived in the last 6 hours it might take me 1 second. However if I select 200 record from the last 12 hours it might take (typically) 8 seconds and if I select 400 records from the last 24 hours it might take more than a minute.

Or in other words, there seems to be an almost exponential increase in the time to select data as it’s age increase. In my naiveite I had assumed there would be an approximately linear relationship between the period specified in the WHERE clause, and the time taken to retrieve the data.

For the record, here’s the table definition….

CREATE TABLE message
(
  messid SERIAL UNIQUE,
  rawmessage character(250), // raw, unprocessed message (ascii string)
  loadedtime timestamp without time zone, // Time this message was loaded
  deviceNumber   INTEGER     // unique identifier of device that generated message.
)

And here’s the sort of query I’m running (deviceNumber and loadedTime are both indexed)

SELECT * FROM message
WHERE deviceNumber = 1234567
AND loadedtime >='10-May-2018 21:00:00’
AND loadedtime <='10-May-2018 22:00:00’

The problem is that the process that reads the table occasionally falls behind schedule and needs to catch up, but because of the exponential time increase in retrieving slightly older data it has difficulty getting the backlog down.

My question comes in two parts….

Why is there such a discrepancy in the time taken to select recent
data and older data. Is it because recent INSERTS are also held in
memory? 2)
Is there anyway to improve the time to select data added
in the last 12 hours to be close to that achieved with data only one
hour old?

Thanks

Update - I'm will attempt to add a composite index on both loadedTime and deviceNumber as suggested (both already have individual indexes). This may take some time.
Once I've added a composite index, do I still need the individual indexes on those fields?


texts: Loading 'Image Service Package' crashes Management Studio

texts:
I'm getting the following error on my SQL Server when I log into the system:

I ran a repair on SSMS successfully, but it did not resolve the issue.

The following error message is recorded in Event Viewer:

  Faulting application name: Ssms.exe, version: 2017.140.17289.0, time stamp: 0x5befcf0a
  Faulting module name: KERNELBASE.dll, version: 10.0.17763.348, time stamp: 0x53015794
  Exception code: 0xe0434352
  Fault offset: 0x0011c632
  Faulting process id: 0x4b78
  Faulting application start time: 0x01d4ea2b07126bd9
  Faulting application path: C:\Program Files (x86)\Microsoft SQL
  Server\140\Tools\Binn\ManagementStudio\Ssms.exe
  Faulting module path: C:\WINDOWS\System32\KERNELBASE.dll
  Report Id: 7ad5c801-9ad5-42e0-9f32-933c4f3eb0c6
  Faulting package full name:
  Faulting package-relative application ID:


texts: Finding posts with exactly set of tags per user

texts:
I'm trying to determine the best way to find all posts that have an exact match with a list of user tags. For example, user has tag IDs: 1,5,9, and 11, while post has tag IDs 1, 5 and 9, thus this is selected as a valid post to display to the user.

The only way I could think of to do this is to count the number of unique tags for each post then count the number of tags a user has in common with the post, compare two values, if equal then it is a match (see query below). Is this truly the best way?

Schema:

Tags
Posts
Posts_tags (tag_id, post_id) unique enforced
Users
Users_tags (tag_id, user_id) unique enforced

Current query (broken down so more legible):
Sub-Query #1:

SELECT     COUNT(*) as numrows, pt.post_id AS id
FROM       users_tags AS ut
INNER JOIN posts_tags AS pt
ON         ut.tag_id = pt.tag_id
WHERE      ut.user_id = 1
GROUP BY   pt.post_id

Sub-Query #2:

SELECT   COUNT(*) AS numrows, post_id AS id
FROM     posts_tags
GROUP BY post_id

Master Query:

SELECT     t1.id, t2.id
FROM       (Sub-Query #1) t1
INNER JOIN (Sub-Query #2) t2
ON         (t1.numrows = t2.numrows)
AND        (t1.id = t2.id)

Also, would a graph database like neo4j be better at this type of query?


texts:
I have two separate UPDATEs as follows:
UPDATE mytab
SET colA = 1
WHERE id IN ( SELECT id FROM mytab
              JOIN othertab ON mytab.otid = othertab.id
              WHERE othertab.Lock = 'SomeLock' AND mytab.colA IS NULL);
UPDATE mytab
SET colB = 2
WHERE id IN ( SELECT ID FROM mytab
              JOIN othertab ON mytab.otid = othertab.id
              WHERE othertab.Lock = 'SomeLock' AND mytab.colB IS NULL);

I wish them as one merged UPDATE query. What I did is:
UPDATE mytab
SET colA =
CASE WHEN colA IS NULL THEN 1 ELSE colA END,
colB =
CASE WHEN colB IS NULL THEN 2 ELSE colB END
WHERE id IN ( SELECT id FROM mytab
                  JOIN othertab ON mytab.otid = othertab.id
                  WHERE othertab.Lock = 'SomeLock');

In general it does what I want on both SQL Server and Oracle. What is concerning me here is the performance of the merged query. In that shape it will update every row that is returned after filtering by predicate. Any better solutions?


texts: How to add more partitions to Existing partitioned Table

texts:
I have a Big table Which is having monthly Partitions From 01-2013 to 12-2016, I have not created partitions after December 2016.

SQL Server is using Last partition to Insert the data from December,2016.

Can someone help:

Which is the best way to Extend the Partition and move the data of
Last partition into Separate Partitions month wise?
Create more partitions for future use?

I have a Clustered Index and 3 Non Clustered Index on the Table.

Thanks in advance.


texts: Recreate ##MS_SSISServerCleanupJobLogin## user as [##MS_SSISServerCleanupJobUser##

texts:
During an install of SQL Server 2014 Service Pack 3 we received the following error:

Error: 15151, Severity: 16, State: 1.

Cannot find the user '##MS_SSISServerCleanupJobUser##', because it does not exist or you do not have permission.

Error: 912, Severity: 21, State: 2.

Script level upgrade for database 'master' failed because upgrade step 'SSIS_hotfix_install.sql' encountered error 15151, state 1, severity 16.

On the instance there is the SQL login [##MS_SSISServerCleanupJobLogin##]

On the SSISDB database there is a SQL user linked to this login called [##MS_SSISServerCleanupJobLogin##] Created the day after the login.

The SSISDB user should be [##MS_SSISServerCleanupJobUser##] and created at the same time as the login.

The solution appears to be to drop the [##MS_SSISServerCleanupJobLogin##] user and create the correct [##MS_SSISServerCleanupJobUser##] user.

USE [SSISDB]
GO
DROP USER [##MS_SSISServerCleanupJobLogin##]
GO

USE [SSISDB]
GO
CREATE USER [##MS_SSISServerCleanupJobUser##] FOR LOGIN [##MS_SSISServerCleanupJobLogin##]
GO
USE [SSISDB]
GO
ALTER USER [##MS_SSISServerCleanupJobUser##] WITH DEFAULT_SCHEMA=[dbo]
GO

Can anyone see any problems arising from dropping and recreating the SSISDB user?

This is a production instance. All test instances have the login and user configured correctly so the service pack installed successfully.

Many Thanks

Simon


texts:
There's a way to calculate how many DTU's a given regular server outputs, but i need the other way around!

I'm planning on leaving the azure environment, and I need a way to measure how much CPU/Memory I should use on my RDS.
Right now I know I consume around 100 DTUs...

so, roughly speaking, which machine is capable of such output?


texts: Can't SQL Server store NULLs in fixed-length columns?

texts:
I came across this statement in the official Oracle documentation:

  In Microsoft SQL Server, only columns with variable-length data types
  can store NULL values. When you create a column that allows NULLs with
  a fixed-length data type, the column is automatically converted to a
  system variable-length data type...

I have never read about this in the SQL Server docs nor have I experienced such a thing. On the contrary: in SQL Server, fixed-length data types (such as int and float, but also char) are heavily used and stored very efficiently even when NULLable.

Is there any rationale behind this Oracle statement?!


texts: How to SUM values with same value by using WHERE IN()

texts:
I'm trying to sum the total of items in a cart by ID, so i was trying simply to SUM the values by passing IDs in the WHERE clause.
But if i'm using the WHERE IN to add the IDs the SUM is going to sum only different IDs so if i have in WHERE ID 104 twice it will sum it only once.
My query looks like this:
SELECT SUM(IMPORTO) AS TOTAL
FROM (
    SELECT SUM(PRE_PRP) AS IMPORTO
    FROM vo_plurep
    WHERE ID_PLUREP IN (12782 , 12800, 12804, 12810, 12802, 12798, 12784, 12798, 12802)

    UNION ALL

    SELECT SUM(PRE_VAR) AS IMPORTO
    FROM vo_varianti
    WHERE ID_VARIANTI IN (104 , 104, 142)
) AS TOT

Is there a way to force MySql to sum even the same values in where clause?


texts: Why sum with previous and current column do't working

texts:
I want to update my specific row with that row previous value, after
  searching i found some post about this but those solution not working
  with me. So what is wrong with my query or how can i archive my goal.?

This is my query:

select d.innings_no,
       e.Player_Name,
       (@total := @total + ifnull(sum(b.runs_scored) + sum(c.extra_runs), 0)) as Runs,
       count(b.runs_scored)                                                      Balls
from wicket_taken d
       inner join ball_by_ball a on d.match_id = a.match_id and d.player_out in (a.striker, a.non_striker)
       left join batsman_scored b
                 on (a.match_id, a.over_id, a.ball_id, a.innings_no) = (b.match_id, b.over_id, b.ball_id, b.innings_no)
       left join extra_runs c
                 on (a.match_id, a.over_id, a.ball_id, a.innings_no) = (c.match_id, c.over_id, c.ball_id, c.innings_no)
       inner join Player e on d.player_out = e.Player_Id
       cross join
       (select @total := 0) params
where d.match_id = 335987
group by d.player_out, d.innings_no
order by d.player_out asc;

This is my current output:

But i want those to be like this (previous column runs + current column runs):

  1  SC Ganguly  61  32

  1  RT Ponting  112 (51+61) 41

  1  DJ Hussey   172 (112+60)    30

So how can i solve this.?


texts:
The version of MySQL is 8.0.16. This is installed on an EC2 Linux and is a fresh install. I have run the mysql_secure_installation command after installation and was able to change the root password successfully and was also able to add a second user with basic privileges.

To test the connection I made a simple script:

$link = mysqli_connect($dbhost, $dbuser, $dbpass);
    if (mysqli_connect_error() ){
        echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    }else{
        echo "Connection successful";
    }

I have double checked the values of the variables and they are all correct. When I try to visit the URL it says:

  Debugging errno: 2054

How to resolve this?


texts: The InnoDB log sequence number is in the future

texts:
Our site was getting intermittent database errors and I asked my webhost to check what's happening. After some inspection they found that the database has few issues and tried repairing it. Finally I got following message from them-

  I have attempted every repair on the InnoDB database and we are still
  getting  the InnoDB log sequence number is in the future. At this
  point to get the  ibdata and the iblogfile to match up once again we
  will need to restore the  MySQL directory (which includes databases)
  from the backup we have on the  server. The process should not take to
  long but there will be some downtime  associated with a restore like
  this. If this is not the best time to restore  the MySQL directory I
  can schedule this for a different time. Please let me  know how you
  would like to proceed with this.

Can someone tell me what'd be the best way to address this issue. I really don't want to lose out on any data and want the dB to be repaired.

PS: If you need more information, please let me know and I'll get it from our web hosts.

Would really appreciate your help.


texts: Migrate from Standalone Instance to Failover Node Instance

texts:
I am fairly new to the MS SQL and i am currently working on a project at my company. We have a ERP System built on a SQL Server and we want to create a SQL failover node.
The original configuration is like this :
SQL Standalone Instance SERVER -> SAN Storage (Storage for MDF & LDF).
Is it possible to create a new server, install SQL Server Failover Instance from scratch, then connect the same primary SAN Storage. Import all the user credentials from standalone (original instance) to the new server.?
And when ready to migrate, we simply turn off the Standalone SQL Server, and attach all mdf files in the new server? will this method be good to migrate from standalone instance to failover instance?
Then we have a one node sql cluster available for users to work on
Later we uninstall standalone instance and install a failover sql node on the source Server, then WSFC etc, making it 2 node SQL Cluster?
I thank you for any help that you could provide.
Asif


texts:
I want to convert string to int my string type  [DT_WSTR] and my expression

ISNULL(col) || TRIM(col) == "" ? (DT_I4)0 : (DT_I4)col

I get this error
[Derived Column [518]] Error: The conditional operation failed.


texts: Trouble killing sessions then immediately dropping users in Oracle 11g XE

texts:
I would like to be able to kill active sessions by a given user, and then drop that user, and do so within a single database call.  Here is what I have so far:

DECLARE
cursor c is select *
                 from v$session
                where username = 'USER_9FFB9';
c_row c%ROWTYPE;
begin
    open C;
    loop
FETCH c INTO c_row;
EXIT WHEN c%NOTFOUND;
        execute immediate 'alter system kill session ''' ||
                 c_row.sid || ',' || c_row.serial# || '''';
    execute immediate 'drop user user_9FFB9 cascade';
    end loop;
end;
/

The problem with this is that I'm getting back this error when I add the drop user call right after the kill session call:

ORA-01940: cannot drop a user that is currently connected

However, if I execute the drop user statement after waiting only a couple of seconds in a separate statement, it works fine.  What do I need to do to be able to kill the sessions and drop the user in one go?  Is there a loop on v$session that I should do, checking to see if it's still open?

Update  I've updated my script to this:

DECLARE
cursor c is select *
                 from v$session
                where username = 'USER_abc';
c_row c%ROWTYPE;
begin
    open C;
    loop
FETCH c INTO c_row;
EXIT WHEN c%NOTFOUND;
        execute immediate 'alter user USER_abc ACCOUNT LOCK';

        execute immediate 'alter system kill session ''' ||
                 c_row.sid || ',' || c_row.serial# || '''';

    end loop;
    DBMS_LOCK.SLEEP(5);
end;

/

drop user user_abc cascade

I'm still getting this error, however:

ORA-01940: cannot drop a user that is currently connected

Is there a commit or something like that which I need to add in there?

COMPLETED SCRIPT

With Justin Cave's help, here is the final script I have which works great:

DECLARE
  l_cnt integer;
BEGIN
  EXECUTE IMMEDIATE 'alter user user_#databaseName# account lock';
  FOR x IN (SELECT *
              FROM v$session
             WHERE username = 'USER_#databaseName#')
  LOOP
    EXECUTE IMMEDIATE 'alter system disconnect session ''' || x.sid || ',' || x.serial# || ''' IMMEDIATE';
  END LOOP;

  -- Wait for as long as it takes for all the sessions to go away
  LOOP
    SELECT COUNT(*)
      INTO l_cnt
      FROM v$session
     WHERE username = 'USER_#databaseName#';
    EXIT WHEN l_cnt = 0;
    dbms_lock.sleep( 2 );
  END LOOP;

  EXECUTE IMMEDIATE 'drop user user_#databaseName# cascade';
END;

Obviously #databaseName# is a placeholder for a simple value like ABC.  Thanks Justin!


texts: Creating Production -> staging flow, how to skip email addresses when doing mysqldump?

texts:
I want to run mysqldump or basically, anything that lets me easily export / import mysql db from one server into another.

Thing is: I want to export production data as staging / integration / demo data. That means, prior to importing the data, all user data must be gone. We had it one time, that the users got imported, and because the system ran a cron job, the server started sending out 200'000 emails. Can you imagine? yes yes, just disable the cronjob prior to import, but accidents happen. So importing user data and then deleting them is not an option.

Optimally I want to just ignore certain columns where there is an email, or replace that email with a test email string.

Is there something that helps me with that?

php is what we use for the application, so any php import export tool is a possibility as well.


texts: Do you still need both the 32 bit and 64 bit connectors installed for SSIS 2016?

texts: We've recently installed SQL Server 2016 on development including SSIS, and we're in the process of migrating our work over to this new server. Part of this work involves migrating packages that involve interfacing with other DBMSs. To do that, I need to install connector drivers again. I know that for past versions of SSIS you had to install both the 32 and 64 bit versions of ODBC connector drivers, as parts of SSIS ran in 32-bit no matter what while other parts might run in 64-bit. Does this behavior and requirement hold for SSIS 2016, or does it suffice to just install the 64-bit drivers now?


texts: How do I resolve issue with deleted table in SQL Server Database Diagrams?

texts:
I am working on a database design using the SQL Server Database Diagram tool.  I created a table with the name dbo.Example and before defining any columns, attempted to save.

Got an error that SQL can't save a table without any columns and as I wanted to save the other work I had done before completing this table, I deleted it and saved successfully.

I then make many more changes and save the diagram.  All is good.

UNTIL I am ready to correctly define my dbo.Example table and get the

'Table 'dbo.Example' already exists' error.

The table is not among the tables returned from

select name from sys.tables;

and does not appear in the Database->Tables GUI view.

I can't find where I need to clean up and drop this table.

Where does SQL Server keep the metadata about the database diagrams?


texts: Location of the mdf file of the database

texts:
I have a database Project. My problem is where can I find the .mdf and _log.ldf of my database so that I can transfer my database to another user. I am using Microsoft SQL Server 2008 Management Studio

My Ssms.exe is stored in this path

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe


texts: System account locked, insufficient privileges to log in as sysdba

texts:
I've researched the web but I could not found exactly the same situation than mine so I'm posting this message.

I'm connecting via Remote Desktop Connection to a remote server (Win2k3 Server). This server has installed (select * from v$version):

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for 32-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

Running both of these sentences returns error:

c:\> sqlplus
SQL> conn / as sysdba
Insufficient privileges
SQL> connect system/****
Account locked

The remote computer is where the db is installed, so I'm not connecting to a remote db. The windows user account belongs to Administrator and Ora_DBA Windows groups.

Db has only one user available (the one used by the application we develope), but it hasn't enough privileges to alter SYSTEM account and unlock it.

I have no idea what else can I do, because I have no way to connect to de db as an administrator. I hope I have provided enough data for my question, if I missed anything please ask.

EDIT: I've checked again Windows user membership: Administrators, ora_dba, Users and "Remote Workplace Users" (hope I've translated last one right).
PWD%ORACLE_SID%.ora file does not exist (suppose this is right).

EDIT2: I'm connecting to local db. I've found 3 installed SQLPLUS: one from Oracle Forms 6i (yeah, I know it's really old version), another one from Oracle 10 client, and the last one from Oracle 10 DB. I did not know about looking for ORACLE_HOME in HKLM/SOFTWARE/ORACLE, as I've read in a comment. In registry, all entries I've found point to Oracle Forms folder. I tried running sqlplus with full path, the sqlplus belonging to DB installation, and finally I've connected conn / as sysdba and I have run unlock account sentence, so problem is solved as my goal was to unlock system account.


texts: SQL Server 2014 Buffer Pool Extension - There is insufficient memory available in the buffer pool

texts:
I am running Microsoft SQL Server 2014 Enterprise Edition SP1 (build 12.0.4213.0 (X64)), with 288GB of RAM, and max memory set to 280GB (I arrived at that number based on many recommended suggestions from SQL Server MVPs).

Twice in the last month I have received:

  There is insufficient memory available in the buffer pool

...followed by other errors, most notably:

  The log for database 'db' is not available

The database goes into suspect mode.  If I restart the SQL service, it comes back fine. I have an SSD that is 1,863GB in size and buffer pool extension on it with a size of 1,859GB in size (I left almost a 5 GB cushion on the SSD).

The error seems to indicate RAM memory pressure. At the time it errors, the server is under high pressure. But this is not uncommon, however, the errors appearing are new. Should I be targeting my RAM modules for testing? Or is somehow the Buffer Pool Extension causing the error?

We monitor this box with SolarWinds DPA, plus I use DMVs to get realtime info, so I have a real good idea of what was running at the time.  Most waits were memory related.

The buffer pool extension data was around 1TB when it went down, so there was plenty of room there. This is a production server, with a very small maintenance window one day a week. Db is in full recovery model, with 1 full backup a day, and trans logs backed up every 30 minutes.

This is a dedicated SQL Server box.
The first error that shows is the insufficient memory error.
The database that errored is 3.22 TB on an iSCSI mapped drive, set to auto grow by 100GB. There is 800GB free currently. Log file is 646GB in size, set to autogrow by 30GB. There is 154GB free. All dbs together is 4TB. All mdf's and ldf's are on their own iSCSI mapped drive.

No hardware or disk related errors in Event Viewer. The only error in the SQL Server error log before it dumps is:

  04/04/2016 07:42:32,spid77,Unknown,Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 24

sys.dm_os_loaded_modules shows only my ODBC driver.

I think raising or lower the SQL Server max memory wont change the problem.  SQL Server should be able to release memory on its own.  We have been running in this configuration for years without a problem.  However, this new issue has happened twice recently.  The only changes I can think of are that we added an SSD for the buffer pool extension.

I did a diagnostic test on the RAM to see if anything turned up, it did not.  I also updated SQL Server 2014 to SP1 CU5, however the error occurred again.  This time though, it did not take the database into suspect/recovery mode.  So that helped.  The questions still remains as to why it is occurring.


texts: CHANGE_TRACKING_CURRENT_VERSION() incorrect on database in standby

texts:
We are using a change tracking solution to export increments for BI purposes for years now. But it was running directly on the production database. To lower the load on the system we wanted it to run on a separate set of databases.

We created these databases by restoring backups and adding transaction log backups. The databases are in standby mode, so readonly.

After switching to these new databases we started seeing differences after processing the increments and checking counts with total counts we provide with the increments.

The differences are usually just a few records per table, but on tables with millions of rows that mutate frequently in the thousands. And the transaction logs backups are done every 15 minutes. And the changes appear quite a bit smaller than the contents of one of these backups.

So we had the feeling this has something to do with non-committed transactions.

We found this, which explains a similar issue:

Change Tracking Current Version Is Not Always Reliable

But the solution, using MAX(commit_ts) from sys.dm_tran_commit_table instead of CHANGE_TRACKING_CURRENT_VERSION() doesn't seem to solve the differences.

Since the changetracking functions also seem to use the current version internally.

After appending another transaction log backup the previously returned changetracking version seems to be consistent again with the data.
Also bringing the databases online, fixes the issue as well.

Bringing the databases online is not an option since we would need to do full restores again to get the data up to date, every day. And the performance is quite bad on a freshly restored databases, compared to the current situation.

The question is, how do we get the last consistent change tracking version from a database in standby mode.


texts:
I recently experienced a filename collision with PostgreSQL's wal archives.

This is with Postgresql 11.2, containerized, on Ubuntu 18.04.

I also noticed the container itself was restarted some 4 weeks ago.

The archive command starts with test, as is recommended on the documentation page: https://www.postgresql.org/docs/11/continuous-archiving.html

And it turned out that file that postgresql was trying to create, already existed.

So, a name collision.

What I'd like to know is how this is possible. I was expecting postgresql to name its wal_archives continously, not restart its naming scheme upon a restart of the process.

Or am I wrong in my analysis and is something else going on here?


texts: Unable to get the latest value added to Mysql table based on posted time

texts:
SELECT * FROM discussion_comments GROUP BY disc_id ORDER BY posted_date DESC

I have table example like given below:

CREATE TABLE example
(
id int(11),
cname varchar(11),
posted_date date,
posted_time varchar(20)
);

with values like:

INSERT INTO example
VALUES (1,'abc','2015-03-26','04:25 PM');

INSERT INTO example
VALUES (1,'def','2015-03-27','04:30 PM');

INSERT INTO example
VALUES (2,'ghi','2015-03-11','02:25 AM');

INSERT INTO example
VALUES (2,'jkl','2015-03-15','12:25 PM');

and I am trying to get only the latest value added to the table for an id based on posted_date & posted_time fields.

The result I am trying to achieve is:

(1,'def','2015-03-27','04:30 PM')
(2,'jkl','2015-03-15','12:25 PM')

The query I tried is as follows:

SELECT * FROM `example GROUP BY id ORDER BY posted_date DESC

I am not getting the desired result. Where did I go wrong??


texts: Consistent mysqldump of mixed InnoDB and MyISAM tables from RDS instance

texts:
I have MySQL db on dedicated server. I would like it to be an external master for an RDS instance. And I want to make backups of RDS. (Currently we have mix of both InnoDB and MyISAM but we are slowly transitioning to full InnoDB).

I did flush tables with read lock on our master(on dedicated server) and took a dump. Restored that dump on a RDS instance and configured external master. The RDS replication caught up. I am trying to create second slave in RDS from the first slave we have up to date with master.
This is where I run into trouble. I can not get consistent copy to run replication. I tried doing snapshot and building replication from the restored snapshot - but replication gets errors about duplicate entires and foreign constraints. If I skip some errors - replications runs for few minutes and errors again. The tables in the error are not MyISAM.

I tried doing dump. Tried locking tables. Flushing tables.

Because you can not do flush tables with read lock on RDS - I tried doing what is said in this AWS article: (http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.BackingUpAndRestoringAmazonRDSInstances.html).

Because the errors are not connected to MyISAM tables I though it is not a problem, but still tried by:

use my_db;
flush tables [list of MyISAM tables];
lock each MyISAM table for read: `lock table <MyISAM table> read`

But the problem is still the same.

Database is around 100G so I dump it table by table with shell one liner.

Any ideas what and where I am doing wrong ?


texts:
I am trying to add data from a text file into SQL Server Database Table.

Sample Data from text file (I donot know how to attach file here so added image sample) :

I first used Import/Export option but somehow the data is getting inserted but showing NULL Values ? Any inputs why is it happening ?

Now,I am using bcp utility but I am facing issue while doing the task. Can I get some help on this.

Use Master
 GO

 EXEC master.dbo.sp_configure 'show advanced options', 1
 RECONFIGURE WITH OVERRIDE
 GO

 EXEC master.dbo.sp_configure 'xp_cmdshell', 1
 RECONFIGURE WITH OVERRIDE
 GO

DECLARE @sql VARCHAR(MAX)
DECLARE @path VARCHAR(512) = 'C:\Users\SQLBoy\Desktop\'

--Use BCP to copy files in character format from the target directly into the table.
SET @sql = 'bcp [MyDB].[Schema].[Destinationtable] in ' + @path + 'new1.txt -c -T'

EXEC master..xp_cmdshell @sql

I am getting error.

  Configuration option 'show advanced options' changed from 1 to 1. Run
  the RECONFIGURE statement to install.

  Configuration option 'xp_cmdshell' changed from 1 to 1. Run the
  RECONFIGURE statement to install.

  Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 12

  Procedure expects parameter 'command_string' of type 'varchar'.

I used VARCHAR(1000) and I am getting below issue :

output

  NULL

  Starting copy...

  SQLState = S1000, NativeError = 0

  Error = [Microsoft][ODBC Driver 11 for SQL Server]Unexpected EOF
  encountered in BCP data-file

  NULL

  0 rows copied.

  Network packet size (bytes): 4096

  Clock Time (ms.) Total     : 1

 NULL


texts: Postgresql query slowed with table growth

texts:
I have a very simple table in Postgresql, and need some guidance to speed up my queries. Recent large expansion of the data in the table has made everything grind to halt.

Table body_feature:

    Column     |       Type       | Modifiers
    -----------+------------------+-----------
     feature   | text             | not null
     body      | text             | not null
     start     | integer          | not null
     stop      | integer          | not null
Indexes:
    "t_feature_ind" btree (feature)

In the old version of the database, there were ~32 million rows in the database.

Here is a simple query:

explain analyze select * from body_feature where feature='ABC';

       QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using t_feature_ind on body_feature  (cost=0.00..49544.92 rows=15986 width=69) (actual time=175.967..261.175 rows=7285 loops=1)
   Index Cond: (feature = 'ABC'::text)
 Total runtime: 261.555 ms

With the recent expansion, number of rows has grown to more than 110 million, and the same query gives:

                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on body_feature  (cost=944.33..155543.90 rows=42307 width=73) (actual time=1118.598..204052.203 rows=23132 loops=1)
   Recheck Cond: (feature = 'ABC'::text)
   ->  Bitmap Index Scan on t_feature_ind  (cost=0.00..933.75 rows=42307 width=0) (actual time=952.948..952.948 rows=23132 loops=1)
         Index Cond: (feature = 'ABC'::text)
 Total runtime: 204065.405 ms
(5 rows)

The original machine running the server is quite old and has 8 Gb of RAM, but I have access to a 1Tb RAM machine for testing.

What would you suggest I change in the server configuration to make such simple query revert back to the simple index scan?

I tried extracting feature and body values (which are text) into separate tables, and using simple integer IDs in the body_feature table (which cuts the raw size down significantly), but I still get the same results.

Thank you for any suggestions/help.


texts: How do foreign keys affect partial and transitive dependencies (databases)

texts:
I'm struggling to work out how foreign keys can affect partial and transitive dependencies when normalising databases.
I have multiple tables, two of which are job offer and accepted job.
in job offer, offerID -> expectedStart, expectedEnd, hourlyPay, in the accepted job table jobID -> startDate, endDate, totalPay, offerID(FK).

My thinking is that the expected start and end dates can change after the job offer, so start and end dates are derived just from jobID, however totalPay is derived from startDate, endDate and offerID as the hourly pay is in the job offer table (of which offerID is the foreign key).

I'm trying to normalise the tables but am really struggling to work out if totalPay is causing partial or transitive dependencies as it is derived from two other entries in accepted jobs and also a foreign key.


texts: Estimated plan generation succeeds for batch creating and using #temp table but fails with a permanent table

texts:
In reviewing the estimated execution plan creation process, I came across this section in SQL Server Execution plans by Grant Fritchey, He mentions:

  The optimizer, which is what generates estimated execution plans,
  doesn't execute T-SQL. It does run the statements through the
  algebrizer...that is responsible for
  verifying the names of database objects. Since SQL Server has not yet
  executed the query, the temporary table does not yet exist. This is
  the cause of the error. Running this same bit of code through the
  actual execution plan will work perfectly.

In the book he references this code:

CREATE TABLE TempTable
(
Id INT IDENTITY(1, 1)
);

INSERT INTO TempTable
DEFAULT VALUES

SELECT *
FROM TempTable;

and the Error you will receive is

  Msg 208, Level 16, State 1, Line 7 Invalid object name 'TempTable'.

To me, this wasn't a temporary table but instead an actual table. So when I tried with a temporary "#" table. I found that the estimated execution plan generated without issue. The estimated plan that was generated is shown here.

Create table #temp
(
ID INT IDENTITY (1,1)
)

Insert into #temp
DEFAULT VALUES

SELECT *
FROM #temp

Can someone explain why there is this difference? Shouldn't the #table not be generated?

Fritchey, G. (2012). SQL Server Execution Plans. Springfield, USA: Simple Talk Publishing.


texts: Why does SQL Server Management Studio crash?

texts:
When I open SSMS I get this message:

  SSMS - SQL Server Management Studio has encountered a problem and needs to close. We are sorry for the inconvenience.

What can I do?

The issue persists after a reboot and I do not have any add-ins installed.

This is SQL Server 2008 R2 SSMS running on Windows XP.

SSMS was running without errors before on this machine.

SSMS was installed from SQL Server package not alone. Is it recommended to reinstall the package? Or how to install SSMS alone?

The same issue occurs for all users.


texts: How do I minimise logging during a large insert?

texts:
During a nightly scheduled task I am creating a large table from scratch using select into and then adding a clustered index. I would like to minimise the amount of logging during the whole process as I have limited bandwidth for log backup shipping.

I'm happy to consider an alternative approach if that would help.


texts: Deleting duplicate rows with condition(Using SQL and LINQ)

texts:
I have a table with 5 columnsID,CPT4,CPT4Mod,ItemCharge,Eff_Date.

Deletion Condition:

When rows exists with duplicate CPT4and CPT4Mod field values but different ItemCharge and Eff_Date values, exclude all but the most recent Eff_Date records.

I need a query which can achieve this in two scenarios.

When CPT4Mod is given by the user.
When  CPT4Mod is not given by the user.

Later I need to achieve the same using LINQ.


texts: how to identify cross login in azure synapse analytics?

texts:
In my environment user are logged in to SQL server via MFA authentication with their email id.
Its possible for single user to log in to multiple laptops with same credentials.

Can we track user login details machine specific ??

Application name : SQL SERVER MANAGEMENT STUDIO
AUTHENTICATION : MULTI FACTOR AUTHENTICATION


texts: SQL Server Error - HRESULT E_FAIL has been returned from a call to a COM component

texts:
I have a huge 200MB .SQL file. When I try to open this in SQL server management studio 2008, I get the error -Error HRESULT E_FAIL has been returned from a call to a COM component.

What could the reason be and how to fix it ? Is this caused due to big files ? It does not happen when I open a 2-3 KB file.


texts: Can't help the engine to choose the correct execution plan

texts:
The stuff are pretty complex to share the original code (a lot of routines, a lot of tables), so I will try to summarize.

Environment:

SQL Server 2016
standard edition

Objects:

wide table with the following columns:

ID BIGINT PK IDENTITY
Filter01
Filter02
Filter03
.. and many columns

stored procedure returning visible ID from the given table depending on filter parameters
the table has the following indexes:

PK on ID
NCI on Filter01 INCLUDE(Filter02, Filter03)
NCI on Filter02 INCLUDE(Filter01, Filter03)

Basically, in the routine I am creating three temporary tables - each holding current filtering values and then join them with the main table. In some cases, Filter02 values are not specified (so the join with this table is skipped) - the other tables are always joined. So, I have something like this:

SELECT *
FROM maintable
INNER JOIN #Filter01Values -- always exists
INNER JOIN #Filter02Values -- sometimes skipped
INNER JOIN #Filter03Values -- always exists

So, how the IDs are distributed - in 99% of the cases it will be best to filter by Filter02Value and I guess, because of this, the engine is using the NCI on Filter02 INCLUDE(Filter01, Filter03) index.

The issue is that in the rest 1% the query fails badly:

In green is the Filter02 values table and you can see that filtering on this does not reduce the read rows at all. Then when the filtering by Filter01 is done (in red) about 100 rows are returned.

So, this is happening only when the stored procedure is executed. If I execute its code with these parameters I nice execution plan:

In such case, the engine is filtering by Filter01 first and Filter02 third.

I am building and executing dynamic T-SQL statement and I add OPTION(RECOMPILE) at at the end, but it does not change anything. If I add WITH RECOMPILE on the stored procedure level, everything is fine.

Note, the values in the temporary tables for filtering are not populating in the dynamic-tsql statement. The tables are defined, populated and then the statement is built.

So, my questions are:

is the engine building a new plan for my dynamic statement as I have OPTION(recompile) - if yes, why is wrong
is the engine using the values populated in my filter02 temporary table to build the initial plan - maybe yes, that's why it is choosing the wrong plan
using recompile on procedure level feels very hard/lazy fix - do you have any ideas how I can assist the engine further and skip this option - new indexes for examples (I have try a lot)


texts: Fetch one value from random row where value is not x

texts:
I have a games table in my db, which has id column(pk) and slug column(unique) - other columns are not relevant. Now, what I'd like to get is one row, or it's slug value only, which would be random row, but the slug must not match myslug. I've come up with:

SELECT slug, nameslug FROM games
JOIN
  (SELECT ROUND(MIN(id) + RAND() * (MAX(id) - MIN(id))) AS pk
   FROM games) AS rand
WHERE id >= rand.pk AND slug <> '20piaedfl6ah'
LIMIT 1;

but it sometimes returns false as it doesn't check if that row's slug column value is the same at the point where it searches for random row.


texts:
I'm new in SQL Server and i have installed SQL Server on my system for testing purpose.
Issue 1: I want to convert the name of all the toolbar in menu tab to icon. I have no clue from where does it got changed and i need to change to icon option. I have option to add and/or remove buttons but it doesn't have any icon in it. So, it's confusing without the icon to change it.

Attached are the image 1 and 2

enter image description here

Issue 2: SSMS keep on crashing. If i open any new table and select top 100 rows, it gives me result correct but when i select any row values and copy any values from the table, it crashes and automatically restart.

For second problem, i have followed few links to solve it, but after running the query, if i copy a value from the result grid, it crashes there.

https://social.technet.microsoft.com/Forums/en-US/baaace41-1286-4303-b31f-0ac935452e95/why-is-ssms-1791-crashing?forum=sqltools

If anyone have any suggestion, it will be very useful for me.


texts: Pentaho design reporter: Parameter error

texts:
I am new to Pentaho platform. i have created view using Mysql workbench. Now i want to design a report for output. Running this error " Parameters for this report are not valid". How do i solved this problem.?

Below here are my data value for my report design.

Streamtype(datepicker)
TrailerDate(drop down with values)
Started(datepicker)


texts: Grouping numbers based on closest sum to a specific value with Postgres


texts: Optimizing removal of duplicate rows from recursive CTE query

texts:
I need to write a recursive SQL query to fetch multiple projects together with their lineage from the following table:

CREATE TABLE #Project
(
Name VARCHAR(10),
ParentName VARCHAR(10)
)

INSERT INTO #Project(Name, ParentName) VALUES ('P', NULL);
INSERT INTO #Project(Name, ParentName) VALUES ('C1', 'P');
INSERT INTO #Project(Name, ParentName) VALUES ('C2', 'P');
SELECT Name, ParentName FROM #Project

(this is just a toy example, so please ignore table design issues and the lack of indexing)

So, given multiple project names, I need to fetch a table that contains the projects as well as all their ancestors, because the result table will have to support lineage queries for a given Project name

In order to do this, I came up with the following recursive CTE query:

WITH Lineage (Name, ParentName)
AS
(
SELECT        Name, ParentName
FROM            #Project WHERE Name = 'C1' OR Name = 'C2'
UNION ALL
SELECT        #Project.Name, #Project.ParentName
FROM            #Project INNER JOIN Lineage ON #Project.Name = Lineage.ParentName
)
SELECT Name, ParentName FROM Lineage

And, here is the result of the query:

Name | ParentName
C1   | P
C2   | P
P    | NULL
P    | NULL

As expected, this returns projects C1 and C2, as well as their parents, which will be duplicated for each child, unfortunately.

In order to get rid of the duplicated parents, I added DISTINCT to the final SELECT:

SELECT DISTINCT Name, ParentName FROM Lineage

Which returns:

Name | ParentName
P    | NULL
C1   | P
C2   | P

Even though this produces the desired result, I am concerned about the performance of this query. The Lineage CTE will surely contain a lot of extra rows if I let it return these duplicates, which I remove at a later step in the query.

Is there any way to rewrite the CTE such that it removes these duplicates directly?

UPDATE: Just in case my example wasn't clear enough, I need to somehow specify the project names for which to fetch the lineages.


texts: How to optimize query with ORDER and LIMIT?

texts:
I have simple query:

SELECT *
FROM "reviews"
WHERE (reviews.branch_id = '12345678')
    AND (reviews.provider IN('first', 'second'))
    AND (is_hidden = FALSE)
ORDER BY reviews.date_created DESC
LIMIT 13

Table structure:

CREATE TABLE reviews (
    id bigint NOT NULL,
    provider character varying(32) NOT NULL,
    branch_id bigint NOT NULL,
    text text NOT NULL,
    date_created timestamp with time zone DEFAULT now() NOT NULL,
);
CREATE INDEX reviews_provider_branch_id_date_created_idx ON reviews(provider, branch_id, date_created DESC) WHERE is_hidden=false;

Explain:

Limit  (cost=3278.96..3278.99 rows=13 width=1183) (actual time=15.657..15.668 rows=13 loops=1)
Buffers: shared hit=1834
->  Sort  (cost=3278.96..3283.32 rows=1743 width=1183) (actual time=15.654..15.665 rows=13 loops=1)
    Sort Key: date_created DESC
    Sort Method: top-N heapsort  Memory: 41kB
    Buffers: shared hit=1834
    ->  Index Scan using reviews_provider_branch_id_date_created_idx on reviews  (cost=0.43..3238.00 rows=1743 width=1183) (actual time=0.082..13.388 rows=1879 loops=1)
          Index Cond: (((provider)::text = ANY ('{first,second}'::text[])) AND (branch_id = '12345678'::bigint))
          Buffers: shared hit=1834
Planning time: 0.548 ms
Execution time: 15.791 ms

Without ORDER:

Limit  (cost=0.43..24.58 rows=13 width=1183) (actual time=0.047..0.088 rows=13 loops=1)
Buffers: shared hit=11
->  Index Scan using reviews_provider_branch_id_date_created_idx on reviews  (cost=0.43..3238.00 rows=1743 width=1183) (actual time=0.046..0.086 rows=13 loops=1)
    Index Cond: (((provider)::text = ANY ('{first,second}'::text[])) AND (branch_id = '12345678'::bigint))
    Buffers: shared hit=11
Planning time: 0.387 ms
Execution time: 0.135 ms

Is there any way to speed up this query?


texts: Sql Server Replication - Modify Statement Delivery Stored Procedures before the replication starts

texts:
I want to create a subscriber (replica) where fields like Username, Email, and other personal customer info is masked (I'm creating a replica of production to help developers troubleshoot issues).

So I know that I should modify the sp_MSins_... and sp_MSupd_... procedures. But the problem is that these procedures exist in the subscriber (right?) so whenever I modify them and then reinitialize the replication to apply their logic to the existing data, the procedures get reverted to their initial state.

I couldn't find anything online about how to handle this.
What are my options?


texts: MySQL query performance of Date sorted data

texts:
I found some articles and questions on this issue but not sure if I got the answer. very new to db modeling so please bear with me
My use case is:

Save user activities: I'm doing this by creating a one table and saving all user activities. The table has a ID, user_id(foreign key), time(Datetime), type(varchar), description
Query the table for activities for a user for a time frame and potentially for a activity type.

My concern is the performance when the table has a large number of rows. My steps that I think will help improve is

index the user_id
index the time

Then I can query for "user_is = 'abc' and time > somedate" or "user_is = 'abc' and time > somedate and type = xyz"

should I index type too? will having too many index have performance issues (when inserting new row or querying)?
What else can I do. Any advise will be greatly appreciated.
Thanks in advance


texts: Why do people recommend not using the name "Id" for an identity column?

texts:
I was taught not to use the name Id for the identity column of my tables, but lately I've just been using it anyways because it's simple, short, and very descriptive about what the data actually is.

I've seen people suggest prefixing Id with the table name, but this just seems to make more work for the person writing the SQL queries (or the programmer if you're using an ORM like Entity Framework), particularly on longer table names such as CustomerProductId or AgencyGroupAssignementId

One third-party vendor we hired to create something for us actually named all their identity columns Ident just to avoid using Id. At first I thought they did that because Id was a keyword, but when I looked into it, I found that Id isn't a keyword in SQL Server 2005, which is what we are using.

So why do people recommend not using the name Id for an identity column?

Edit: To clarify, I am not asking which naming convention to use, or for arguments to use one naming convention over the other. I just want to know why it's recommended to not use Id for the identity column name.

I'm a single programmer, not a dba, and to me the database is just a place to store my data. Since I usually build small apps, and typically use an ORM for data access, a common field name for the identity field is much easier to work with. I want to know what I am missing out on by doing this, and if there are any really good reasons for me not to do this.


texts: Can I make my distribution database use In-Memory OLTP?

texts:
Is it possible to have all the distribution tables use In-Memory OLTP (formerly known as Hekaton) for enhancing performance of the distribution database?

Have you tried it?

Did that increase the overall performance when the distribution database was the bottleneck for your replication?


texts: Sensible database backup retention policy?

texts:
Scenario:

DB is replicated in two availability zones (for high-availability)
DB is backed-up daily in geographically separate location (for disaster recovery)

App-level user events are stored in the DB (for app-level audit/history). This means point-in-time restore is probably not going to happen on the DB-level but rather on the app-level, unless a rouge app user deliberately messed with the whole DB, making DB-level restore more practical.

My question is, what's a sensible retention policy for the daily database backups? E.g., Does it makes it sense to store 30-days worth of backups? What's the general best practice in this typical scenario?


texts:
We have main database, through out day we insert/update this DB through main application(windows). We have another web application which is suppose to just display content from main DB but instead we have created new DB which we update through out day either manually or from code.

I want to get rid of this manual/code process of updating one DB to another DB. What are the options? Eliminate second DB and just read from main DB? Replication? Mirroring?

It has to be real time update.
Thanks,


texts:
I'm designing a database structure wherein I like to apply the concept of inheritance.
There's a base tables and derived tables in which the relationship is one-to-one. I'm quite new as of how could this be achieved and how the data are to be fed to the tables because I'm getting errors I can't solved.

I'm wondering if I'm on the right track doing this.

Here's the design view of the database:

I get the error of

You cannot add or change a record because a related record is required in the table **PatientRegular**

In which case the table I'm on currently working?

And the relationship view:

What is the right way to do that? I want to inherit all those similar fields to another table?

Update

Either way, I cannot also insert new data in the base table (I must be in a wrong track)


texts: Why does SQL Server table partitioning stop working?

texts:
For a number of reasons, but mostly the sheer volume of data, we have 5 MS-SQL (2017) sharded servers to break up the overall load on any individual server. Secondly we have a real-time table that is partitioned by asset ID in order to store the real-time data. Data packets arrive approximately every 2 seconds for each asset. Each data packet can have from 300 to 12000 data points.

The table partitioning and sharded servers work very well. But every once in a while it seems that one of the servers seems to stop using the partitioning which results in deadlocks and timeouts as new data arrives when deleting old data and bulk loading the new data.

My question is why is this occurring, and is there a way to check to see when or why this is happening. I know that this is the issue since I can move the data to a staging table, drop the original table, recreate the original table with the same partition function and scheme and finally copy data back from the staging table to the new "original" table and the issue is resolved.

As requested the partition scheme.  The asset ID is what the function uses as stated above

CREATE PARTITION SCHEME [RMAPartitionScheme] AS PARTITION [RMAPartitionFunction]
TO ([HULL269_484_LymanMartin], [HULL275_541_ClarenceTriche],
    [HULL277_546_RussellAdams], [HULL278_550_CharlieComeaux],
    [HULL281_561_CInstaller], [HULL284_587_GrandIsle],
    [HULL290_621_ShipIsland], [HULL291_638_HornIsland],
    [HULL296_653_StimStarIV], [HULL293_654_CatIsland],
    [HULL292_655_SanibelIsland], [HULL297_678_DauphinIsland],
    [HULL137_679_StimStarBrasil], [HULL809_680_Robin],
    [HULL1304_690_FastTrack], [HULL174_691_CRuler],
    [HULL1328_692_FastTiger], [HULL240_695_CFighter],
    [HULL778_730_Kudu], [HULL064_738_FastCheetah],
    [HULL063_739_FastServer], [HULLT100_746_ITS100BSM],
    [HULLBT10_749_ITSBuckeye], [HULL315_779_Elrington],
    [HULL302_780_MarshIsland], [HULL321_781_Courageous],
    [HULL316_782_LaTouche], [HULL317_784_BainBridge],
    [HULL324_787_Contender], [HULL325_788_Champion],
    [HULL318_790_Ingot], [HULL326_792_Challenger],
    [HULLN01_797_SeawayMoxie], [HULLAS1_799_IslandCommander],
    [HULL301_800_DeerIsland], [HULL810_804_Lucy],
    [DEFAULT_FILE_GROUP])

And the function

CREATE PARTITION FUNCTION [RMAPartitionFunction](int)
AS RANGE LEFT
FOR VALUES
     (484, 541, 546, 550, 561, 587, 621, 638, 653, 654, 655, 678,
      679, 680, 690, 691, 692, 695, 730, 738, 739, 746, 749, 779,
      780, 781, 782, 784, 787, 788, 790, 792, 797, 799, 800, 804)


texts: Select date ranges with a 50% overlap to another date range

texts:
I have a series of date ranges with columns e.g.
start_date, end_date, period_name

   1         100     Roman
 -50          75     Iron Age b
-100          20     Iron Age a

My team have asked me to build 2 queries:

Select the Roman period and any which overlaps
same as 1 but only those ranges which overlap >40%

I'm assuming I would have to write a function in postgresql (PL/pgSQL), any help is very welcome.


texts:
I have a MySQL database with table names like 1,2,3... upto 1000

Each tables have 2000 rows and has the same structure.

This is how all my 2000 table's structure look like

id,url,title,content

id has type int, primary key,auto increment.

Now I would like to create a new database with name merged. Also a table with name merged_table.

Can someone tell me how to import all my tables in merged_table?

My problem here is all my tables has id from 1 to 2000. So I have no idea how to import them.

I would like to have unique ids from 1 to 2000000 in my new merged_table

I'm using ubuntu. So if someone suggest me a terminal command, that would be awesome.


texts: Confounding NUMA configuration in SQL Server for 24 vcore system in Azure

texts:
I need some help understanding how SQL Server takes a 24 core VM and displays it as 11 NUMA nodes under properties.
At startup, this is the key message about hardware SQL Server found:
SQL Server detected 1 sockets with 7 cores per socket and 14 logical processors per socket, 24 total logical processors; using 24 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

I do not understand how it detects 1 socket with 14 total vCores and then says it has 24 total logical processors.  Can someone help me understand that?
The VM is in Azure and is an E96-24as_v4 which is based on AMD EPYC 7452.  That CPU appears to have 32 cores for each socket of whatever Azure has it running on.
The E96-24 means it is a 96 core VM suppressed down to 24 by Azure to give a better core to memory ratio.
SQL Server shows 11 NUMA nodes with the first 2 nodes (NODE 0 and NODE 1) with 3 vCores and the remaining nodes with 2 vCores each.
The workload on this server is OLAP/DSS.
Soft NUMA is off.
As requested in comments here is the output from os_nodes and os_memory_nodes:

osn.node_id
osn.online_scheduler_count
osn.cpu_count
osn.memory_node_id
osmn.target_kb
osn.processor_group

0
3
3
0
60023896
0

1
3
3
1
60023896
0

2
2
2
2
60023896
0

3
2
2
3
60023896
0

4
2
2
4
60023896
0

5
2
2
5
60023896
0

6
2
2
6
60023896
0

7
2
2
7
60023896
0

8
2
2
8
60023896
0

9
2
2
9
60023896
0

10
2
2
10
60023896
0


texts: Migrating database from old software to new software

texts:
I am a young web developer and I am facing a problem I cannot solve on my own and need some help and/or orientation.

My client has an agenda (Lotus), which is a very old software, and I have developed a web-based agenda (which uses MySQL). What I need to do is to migate all the contact database from the old agenda to the new one.

I managed to export the database from Lotus(old software) in the following formats: ANSII, ASCII, DBASE, VCARD. Which one will be the most useful?

There is another problem, my new database is slightly different (It does not have all the same fields, and, per example, it has another table for other numbers or other e-mail addresses, and some other differences.)

I thought of importing the same exact database (both structure and data) from the old software to SQL, and then, write a .php script whichs converts it to my new structure. (This because I am used to sql and php, and I can't figure out a better way to do it)

Is my idea a good way to do? Can you recommend a better one? How do I import this database to SQL?

EDIT: Answering to Max Vernon, in my new software I am using MySQL database. I am not sure if that was your question, I am a novice to databases and had to google up RDBMS. And to the other question, the ansii and asci backups looks like the table column names between quotes separed by comas, and values between quotes separed by comas

I edited the whole post because it has been marked as unclear.


texts:
There's two columns dateRequested and dateCompleted in DATE type and I want to select only 4 of the rows with the least time between the two.

SELECT *, DATEDIFF(dateCompleted,dateRequested) AS Time
FROM ticket
ORDER BY Time;

With the above statement I get what I want but since there are nulls and in the case of the question for the sake of a challenge I guess there is negative time between the two so I really only want those where Time >= 0

I've tried with a CASE statement but I don't fully grasp that yet so any help would be appreciated.

Thanks


texts:
I don't know if NetApp SnapManager is a niche product.  But the Restore Wizard is very slow, and I was wondering if anyone else had encountered this problem.  I kill the SPID after 15 minutes of waiting.  For DR, we have would to completely avoid the restore wizard, and instead do a clone copy to development, and copy data from there.

The waiting happens on the screen that says "Double-click to select a backup under the database you want to restore."  After double-clicking, that's when the waiting starts.  Looking at the database activity, SnapManager is gathering the database names from each backup in the snapshot. ( RESTORE FILELISTONLY )

The problem is that there are 38 databases listed under the snapshot, with 40 items each, which means it is trying to restore file lists 1,500 times.

The only reason I would use this feature is in an emergency situation, but it's so slow that it won't cut it.

Example:
Under backup "sqlsnap__SQL_09-15-2013_23.36.08__Daily", it looks like this on the right pane:

Database - 1 Admin
Admin 09-15-2013_23.36.08
Admin 09-15-2013_23.56.19
Admin 09-16-2013_00.11.20
Admin 09-16-2013_00.26.15
Admin 09-16-2013_00.41.15
Admin 09-16-2013_00.56.16
...and so on... on to the next database
Database - 38 WHD
WHD 09-15-2013_23.36.08
WHD 09-15-2013_23.56.19
WHD 09-16-2013_00.11.20
WHD 09-16-2013_00.26.15
WHD 09-16-2013_00.41.15
WHD 09-16-2013_00.56.16

For a total of over 1,500 items.

Did we configure something incorrectly?


texts: Availability Group Endpoints Windows Authentication Error

texts:
I have a two-node SQL Server 2016 Standard Always-On environment and when I create a new availability group, I receive the error message below:

  The Endpoints tab lists at least one endpoint that uses only Windows Authentication. However, the server instance might be running under a nondomain account. To use the listed endpoint, change the corresponding SQL Server service account to a domain account. To continue using the nondomain account, alter the endpoint to use a certificate.

  Do you want to use the listed endpoints?

Now there is a fair amount of information to correct this, but I am struggling to find a fix for my scenario.
All im finding, is when you are running SQL Server and the endpoints as a non-domain user, as the message suggests.

I am running both instances as the same domain user, which has admin on both nodes and sysadmin in each instance of SQL Server, and this can be seen on the endpoints tab, both endpoint SQL Service account entries are the Domain\svc_account that is running SQL Server.

The Availability groups get created perfectly, i occasionally have do grant CREATE DB to the secondary node to initialize the seeding, but other than that all is great. Failovers are perfect etc. We do have over 20 AG's. Could that be an issue? I believe AG have been tested successfully at up to 100 groups. Being SQL Server Standard, we can only do 1 DB per group, and most of the DBs are small at <5GB.

So I am assuming its not causing any issues yet, but regardless I would like to know if its something I can ignore, or fix, in my scenario.

The output of Node 1:

Node 2:

Thank you for all your assistance.


texts: Is it possible to deny a connection from changing ANSI_PADDING to OFF?

texts:
Our application occasionally throws errors if we ever apply filtered indexes because some of the C# application connections change the ANSI_PADDING to OFF for some routines.
Is it possible to deny setting change on the session without raising an error, or use a database trigger to catch and reverse it?


texts: Sync Server Objects when having two or more Availability Groups

texts:
Lets imagine that i have two servers/nodes with TWO availability groups Group01 and Group02. I will need to ensure that the server objects are all the time recreated over the secondary replica.

If i have the Group01 in one node as primary, and the Group02 in the other node as primary, how will the sync of server objects be processed?

I was thinking of using dbatools with one command line only:

Start-DbaMigration -Source $PrimaryInstance `
                   -Destination $SecondaryInstance `
                   -Exclude Databases

What am I missing here?


texts: Order by column with specific adjacent value

texts:
Apologies for the vague title. I wasn't really sure how to describe this.
I have the following tables:
items:
id | name
---|----------
1  | first-item
2  | second-item
3  | third-item

fields:
id | name
---|------------
1  | title
2  | description
3  | available

values:
id | item_id | field_id | value (json)
---|---------|----------|-------------------------
1  | 1       | 1        | "First Item"
2  | 1       | 2        | "This is the first item"
3  | 1       | 3        | 1
4  | 2       | 1        | "Second Item"
5  | 2       | 2        | "This is the second item"
6  | 2       | 3        | 1
7  | 3       | 1        | "Third Item"
8  | 3       | 2        | "This is the third item"
9  | 3       | 3        | 0

Here's an example query with the order by represented as a select to try to illustrate the desired logic :
select `items`.*
from `items`
         left join `values` on `items`.`id` = `values`.`item_id`
         left join `fields` on `values`.`field_id` = `fields`.`id`
where (`fields`.`name` = 'available' and `values`.`value` = 1)
group by `items`.`id`
order by (select `values`.`value` from 'values' where `fields`.`name` = 'title') desc

What I'm trying to do it order the items by values.value for one or more specific fields. Hopefully that makes sense?
The end result should look something like this:
id | name
---|----------
2  | second-item
1  | first-item

I'm really not sure where to start with this and searching has yet to yield any leads. Any help would be greatly appreciated.


texts: Reset MySQL root password in LAMPP server on Ubuntu

texts:
I forgot the password I changed for the root user of MySQL. I'm logged in as root user and when I do
# /opt/lampp/lampp security
it gives result as

XAMPP: Quick security check...
XAMPP: Your XAMPP pages are secured by a password.
XAMPP: Do you want to change the password anyway? [no] n
XAMPP: MySQL has a root passwort set. Fine! :)
XAMPP: The FTP password for user 'nobody' is still set to 'lampp'.
XAMPP: Do you want to change the password? [yes] n
XAMPP: Done.

How do I reset the password. I checked to see, but couldn't find the password in any file (including my.cnf).


texts: How can I find a table in the whole instance?

texts:
I am trying to create a report which selects data from a table called costomersHR. I cannot find it as we have hundreds of databases in the instance.

How can I search for a table in the whole instance?


texts: Specify order of attributes in crosstable

texts:
I'm trying to create pivot tables on PostgreSQL.

I successfully created my first example. But I have on problem:

I use the two-parameter form of crosstab, which takes a query for the data and a second query for the categories.

I also have to provide a list of category names in the AS statement.

But how can I make sure that they are in the same order?

The documentation uses SELECT DISTINCT category FROM table. But that can't guarantee me any order of the categories, right?

I have three test objects/experiments X_E001-3 with three fields/categories/attributes:

MR_Type (out of: Tw2, T2w, MRx)
groundTruth (out of: head, liver,
abdomen)
origin (out of: Hamburg, Cambridge, Paris)

CREATE TABLE x_experiment_fields (
    x_experiment_id text NOT NULL,
    field text NOT NULL,
    value text NOT NULL
);

INSERT INTO x_experiment_fields VALUES ('X_E001', 'MR_Type', 'T2w');
INSERT INTO x_experiment_fields VALUES ('X_E001', 'organ', 'liver');
INSERT INTO x_experiment_fields VALUES ('X_E002', 'MR_Type', 'Tw2');
INSERT INTO x_experiment_fields VALUES ('X_E002', 'organ', 'abdomen');
INSERT INTO x_experiment_fields VALUES ('X_E002', 'origin', 'Hamburg');
INSERT INTO x_experiment_fields VALUES ('X_E001', 'origin', 'Paris');
INSERT INTO x_experiment_fields VALUES ('X_E003', 'origin', 'Cambridge');
INSERT INTO x_experiment_fields VALUES ('X_E003', 'organ', 'head');
INSERT INTO x_experiment_fields VALUES ('X_E003', 'MR_Type', 'MRx');

I then make may query as proposed:

SELECT * FROM crosstab(
    'SELECT x_experiment_id, field, value FROM x_experiment_fields ORDER BY 1',
    'SELECT DISTINCT field FROM x_experiment_fields')
AS (x_experiment_id text, MR_Type text, organ text, origin text);

which outputs:

 x_experiment_id | mr_type | organ     | origin
-----------------+---------+-----------+--------
 X_E001          | liver   | Paris     | T2w
 X_E002          | abdomen | Hamburg   | Tw2
 X_E003          | head    | Cambridge | MRx

which is wrong (or better not intended).

The reason is, that SELECT DISTINCT field FROM x_experiment_fields gives me

 field
-------
organ
origin
MR_Type

whereas I want them in order MR_Type, groundTruth, origin.

Even if I change my order, I can't be sure the query always gives the identical order, right?

I could order the query and my list. But then the order might not me as wanted (and it makes it harder to extend the attributes list later on).

Is there any common use?

Robin


texts:
I have three tables in Access:

employees
----------------------------------
id (pk),name

times
----------------------
id (pk),employee_id,event_time

time_notes
----------------------
id (pk),time_id,note

I want to get the record for each employee record from the times table with an event_time immediately prior to some time.  Doing that is simple enough with this:

    select employees.id, employees.name,
(select top 1 times.id from times where times.employee_id=employees.id and times.event_time<=#2018-01-30 14:21:48# ORDER BY times.event_time DESC) as time_id
from employees

However, I also want to get some indication of whether there's a matching record in the time_notes table:

select employees.id, employees.name,
(select top 1 time_notes.id from time_notes where time_notes.time_id=(select top 1 times.id from times where times.employee_id=employees.id and times.event_time<=#2018-01-30 14:21:48# ORDER BY times.event_time DESC)) as time_note_present,
(select top 1 times.id from times where times.employee_id=employees.id and times.event_time<=#2018-01-30 14:21:48# ORDER BY times.event_time DESC) as last_time_id
from employees

This does work but it's SOOOOO SLOW.  We're talking 10 seconds or more if there's 100 records in the employee table.  The problem is peculiar to Access as I can't use the last_time_id result of the other sub-query like I can in MySQL or SQL Server.

I am looking for tips on how to speed this up.  Either a different query, indexes.  Something.


texts: Difference between backup methods: Oracle and MySQL

texts:
I was reading that Oracle databases have cold, warm, full and redo backups.

Questions

How is it possible that MySQL doesn't have as many options?
I have read that MySQL has cold backups (it puts the DB into a lock mode by itself); are there any other methods?
How can I implement methods similar to those that Oracle has in MySQL?


texts: MySQL replication - slave is continuously lagging behind master

texts:
I am using MySQL-5.1.50 with a Master-slave replication setup.

Most of the time the slave is lagging behind the master.

When I run show processlist;, there is no query that's taking a long time.  I enabled slow_log as well. However, it does not find any slow running query.

The slave is continuously giving alerts that replication is seconds behind the master.  Sometimes, the lag time increases.

How do I diagnose the cause of the problem?

I need urgent help, since this problem has persisted for the last 20 days.


texts:
(could be) possible duplicate with An inconsistency was detected

I have small table that returns from query

  UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult" is
  inactive.UDA-SQL-0564 [Microsoft SQL Server Native Client 10.0]An
  inconsistency was detected during an internal operation. Please
  contact technical support. (SQLSTATE=HY000, SQLERRORCODE=5243)

before any further steps on my side I want to ask if is there another way as is described in linked thread,
and in this case there isn't any issue to re_creating table with proper contents on fly


texts: Multiple instance of Mysql realtime backup

texts:
My current mysql backup strategy is replication. I setup one or more slaves following a master. If the master failed, I still have a clean and up to date copy of the data from one of the slaves. It works without any problem. Now I started to have more master nodes to manage, each master node holds different data.

Now if I wanted to use the same backup strategy, I needed to setup at least one slave instance for each master. It's going to double the number of mysql nodes. Ideally I'm hoping to have only one server for all backups. Possible solutions I can think of are:

To run multiple instances of mysql slaves on different ports on the same machine;
Crontab + Rsync;

For possible solution #1, it should work, but it costs memory to running slave instances. For possible solution #2, I'm afraid when the data is being rsync'ed, the master failed, the backup might not contain a complete copy of the data. So is there any other better strategy to safely backup all master node to a single place?


texts: Policy of archivelog backup using redundancy politcy?

texts:
For example. If I create a policy using configure retention policy to recovery window of 5 days all backups before the 5 days will be obsoletes (datafiles, archivelogs backup etc..)

But, if my policy to be configured using configure retention policy to redundancy 2, only backupsets about datafiles will be obsolete and the backup of archives not.

My question is, is there some way to make the rman understand when the backup of archivelog don't have either "father" incremental backup  or a "father" full backup and isn't more necessary?

Usually in my routines I need create something like this to delete the archivelog backup that isn't more necessary (let's think that my retention is of two backups full in every sunday).

delete backup of archivelog until time=’sysdate-16′

Resume:

Is there some way to make RMAN understand when the archivelog backup isn't more necessery?


texts: Reuse alias that shares an existing column name

texts:
In MySQL I know I can reuse an alias in a query like this:
SELECT
  value_a + value_b as value_sum,
  (SELECT value_sum) + 5 as padded_sum

Is there any way to make this work if padded_sum is also the name of an existing column in one of the joined tables?
http://sqlfiddle.com/#!9/48c2c9/6


texts: pg_basebackup not running even after trying all the methods

texts:
I configured pg_basebackup on another server, where it is running perfectly, but in one of the servers pg_basebackup is not running at all. Below are the error details. I tried multiple ways to troubleshoot, but didn't solve the issue; please help. The PostgreSQL version is 11.7.

-bash-4.2$ pg_basebackup -p 5432 -U postgres -Ft -D /var/lib/pgsql/backup/cluster1day1
pg_basebackup: could not get write-ahead log end position from server: ERROR:  could not open file "./.pg_ident.conf.swp": Permission denied
pg_basebackup: removing contents of data directory "/var/lib/pgsql/backup/cluster1day1"


texts: What causes "attribute key cannot be found when processing" errors?

texts:
I'm consistently getting variations on this error when I process the cube in my first SSAS project:

  Errors in the OLAP storage engine: The attribute key cannot be found
  when processing: Table: 'dbo_Transactions', Column: 'TransactionSK',
  Value: '68342998'; Table: 'dbo_Transactions', Column:
  'TransactionDateTimeUTC', Value: '7625037'. The attribute is
  'Transaction SK'.

The phrase "attribute key cannot be found when processing" gets a number of Google hits, and several hits on Stack Overflow too (1, 2, 3).  However, these address broken FKs, NULL values, or duplicate values (e.g., due to inconsistent case sensitivity).  None of those situations apply.

There's just one table here: dbo.Transactions.  It has fields TransactionSK and TransactionDateTimeUTC, both INT NOT NULL.  As you can guess, the TransactionDateTimeUTC field is an FK to a DateTimes table, but there are no missing records, that's enforced in ETL and I've verified it in SQL.

What else could be causing this error message?  Why does it list two fields, TransactionSK and TransactionDateTimeUTC?  The similar situations I've bumped into so far consistently only reference a single field in the error message.

The table is being updated in near-real-time, including occasional deletions, though the two fields in question are not updated.  If a record is removed from the table after SSAS scans one column and before it scans another, will that produce this error?  Spot-checking a few problem records, they were not created in the middle of process the cube.


texts: How do I report progress of a long running transaction in MySQL?

texts:
I have this transaction in a stored procedure:

...
START TRANSACTION;

WHILE @Codes > 0 DO

    EXECUTE qry_rand_stmt;
    EXECUTE qry_insert_stmt;

    SET @Codes = @Codes - ROW_COUNT();

END WHILE;

COMMIT;
...

This could be inserting anywhere up to 1M unique rows taking up to 10 minutes which is fine and dandy but I need a way to report back its progress.

I would normally add an if statement that would run a query to update a job table of its progress every 1000 inserts but because this is in a transaction the update wouldn't appear if queried externally until the entire transaction is finished which defeats the purpose of having a status as you would only see when the job started and finished.

I have even thought of committing every 1000 inserts so I could report progress that way but then I lose the ability to roll back all inserts if something goes wrong later, or if the job is canceled before it is finished.

So, is there a way to run an update query inside a transaction that doesn't become part of the transaction and is written to the table/row with immediate effect?

If not, do you have any workarounds?


texts:
I am a fresh DBA just being promoted, I am looking for few good sites which constains T-sql Scripts for DBA tasks. Is there a common place from where you can get a collection scripts to perform operation like tuning, re-indexing, DMV, restore, mirror etc.

regards Thanks for listening


texts: NonParallelPlanReason = MaxDOPSetToOne when maxdop set to 4

texts:
Running SQL Server 2014 on both servers.

While I was attempting to find out why a procedure was running faster on Server B than it was on Server A I found that on Server B the query plan says the Degree of Parallelism = 1 while Server A says Degree of Parallelism = 0 and the NonParallelPlanReason = MaxDopSetToOne.  I'm guessing this is why I'm seeing the difference in my timings for the procedure.  However both servers are set to MaxDop 4 with the same threshold and both plans are returning as almost identical.

I'm at a loss for why one server is choosing parallelism while another is not when the configurations are the same and the code is exactly the same.  Can anyone point me in the right direction?
Why would a NonParallelPlanReason be given as MaxDopSetToOne when the MaxDop is configured to 4?

I've posted the plans on brentozar's pastetheplan.

ServerA plan
ServerB plan

Both servers are showing:

name                            minimum    maximum  config_value  run_value
cost threshold for parallelism      0       32767       1000         1000
max degree of parallelism           0       32767          4            4

Resource Governor is disabled
@@Version for ServerA = Microsoft SQL Server 2014 (SP2-CU5)
@@Version for ServerB = Microsoft SQL Server 2014 (SP2-CU2-GDR)
ServerA is not a VM
ServerB is an Azure VM


texts:
racing_couple
ID (PRIMARY)

breeding_couple
ID (PRIMARY)

egg
IDpar(FOREIGN KEY)

I have 2 IDs from 2 different tables (racing_couple and breeding_couple) and they are primary keys. In 3. table (egg) I have IDpar which is foreign key and references ID in racing_couple  and breeding_couple. Tables represents racing and breeding couple of pigeons, table "egg" represents egg of racing or breeding couple. And I'm using IDpar to identify which egg belongs to which couple.

Note There are other fields in both database but they are mostly varchar and not so relevant to this problem.

If I have something like this in both databases. How to know which IDpar has value from racing_couple and which IDpar has value from breeding_couple.
I think I made mistake by making my database like this, but is there any way to make that work?

ID(racing_couple)
1
2

ID(breeding_couple)
1
2

IDpar(egg)
1 (ID racing_couple)
1 (ID breeding_couple)
2 (ID racing_couple)
2 (ID breeding_couple)


texts:
I wonder if it is possible to rename Postgres table without cascading type change to dependent objects? For example,

create table test_1(id int);
create function test_1_funct (param test_1[]) returns void as $body$
begin
    return;
end
$body$
language plpgsql;
alter table test_1 rename to test_2 ;
 -- function test_1_funct signature silently changed,
 -- so it now accepts test_2[].

I want to emulate behaviour similar to what Oracle would do - mark function invalid, let me create table with the old name, and recompile the function on demand .  As it is, it doesn't let me drop table test_2.


texts: Postgres 'could not write to hash-join temporary file: No space left on device'

texts:
I was getting this error a while ago and I was curious about two things -

1- How do the internals of postgres work that cause this message to occur in the first place? What is the hash-join in reference to the creation of the temporary file that the db creates? I know it's related to creating a temp file that is out of scope of the memory allocation for the database but I don't know why it's called a hash-join.

2- How can I predict and prevent errors like this (related to performance and memory) occurring in the future?


texts:
I have 2 remote SQL Server 2014 instances: Server A with version 12.0.4100.1 and Server B with version 12.0.4213.0.

I have exported a DB from server B, copied and restored it to server A for tests. I have a query (really long) which I execute on both servers. The Server B returns the results in 2 min and server A in 1-2 sec. I have tried to export back the DB from Server A to server B, the same DB and nothing changes.

I have noticed the following on server B: if I export all the tables of the DB into a TempDB, then delete all the tables from DB and then import all the tables from TempDB back into DB the problem is gone. This happens for both the old (original) DB and the new (exported back from server A) one as well.

My problem: what can cause this "problem" and how to prevent it?


texts:
I plan on creating my first database so this is going to be a newbie question. My database is supposed to be for a Borrowing-Lending-Network where users can borrow products from other users. Creating one user account should be sufficient for being able to borrow and lend and the same time. The only exception should be that a user cannot borrow products that were offered by himself.

So my question is which of these two options is the better one:

Having one entity for user which can borrow and lend. Entity name "USER".
Having two different entities for both borrowers and givers in order to prevent a user from borrowing his own products?


texts:
R(HIJKLMNO)

L -> MNO
HI -> JKLMNO
J -> KL
K -> H

HI is a key.

So, H is a prime attribute.

But, H is also in non-key part.

What are the prime and non-prime attributes in this relation?