texts:
I have a Percona 5.6.28 servers with a=some TokuDB tables. The biggest table has ~120 million rows. Statics keeps changeing in the past hald year:

At first. Analyze table with recount model toke accurate statics back
Recenly. The statics were updated to the same value including some column as gender.

I have tried with below ways but don't work:

Analyze table
Drop some index and re-create
Optimize table & Analyze table

I found a lot people had get the same problem before. Can anyone share some experience?


texts: How to improve performance merging multiple table via cursor

texts:
I have the following stored procedure to merged multiple tables into 1 table. The records that im tryin to merge is about 150M. The procedure is quite slow. Here is the query
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE Category CHAR(200);
DECLARE cur1 CURSOR FOR SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='technology2' AND TABLE_NAME <>'technologyRepo';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN cur1;
  read_loop: LOOP
  FETCH cur1 INTO Category;
     IF done THEN
       LEAVE read_loop;
     END IF;

  SET @table_name:=Category;
  SET @QRY=CONCAT('INSERT INTO
  technologyRepo (
                  documentId,
                  url,
                  version,
                  first_detected,
                  last_detected,
                  technology_name)
                  SELECT
                  id,
                  url,
                  version,
                  first_detected,
                  last_detected,', QUOTE(Category));
SET @QRYFRM=concat(@QRY, ' FROM technology2.');
SET @sql:=CONCAT(@QRYFRM,@table_name);
PREPARE dynamic_statement FROM @sql;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;
END LOOP;
CLOSE cur1;
END

Here is the total number of tables that i need to merge:
+------------+
| TABLE_NAME |
+------------+
|       1178 |
+------------+
1 row in set (0.16 sec)

mysql> SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA
= 'technology2' AND TABLE_NAME <>'technologyRepo';
+-----------------+
| SUM(TABLE_ROWS) |
+-----------------+
|       150116963 |
+-----------------+
1 row in set (0.02 sec)

Any performance optimization for this? I'm not that familiar with cursor.


texts: Do I need to `mysql_upgrade --verbose -u root -p` each time when `source backup.sql`?

texts:
I am upgrading mysql 5.1 to mysql 5.5 in centos 7 server.

As time limit,I only mysqldump and source several small database to mysql 5.5,and then mysql_upgrade --verbose -u root -p,everything is ok.

Several days later, I import a big database db3 to mysql 5.5.

[root@localhost ~]# mysql -u root -p < db3.sql
Enter password:
[root@localhost ~]# mysql_upgrade --verbose -u root -p
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
This installation of MySQL is already upgraded to 5.5.60-MariaDB, use --force if you still need to run mysql_upgrade
[root@localhost ~]# mysql_upgrade --verbose --force -u root -p
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Phase 1/4: Fixing views

Pls notice This installation of MySQL is already upgraded to 5.5.60-MariaDB, use --force if you still need to run mysql_upgrade.
Do I need to run mysql_upgrade --verbose -u root -p again?


texts: SQL Server 2016 login language display is wrong

texts: Even after changing the language for a login from Arabic to us_english it doesn't save it. It continues to show Arabic. The server default language is us_english. How do I fix this. Thanks !


texts:
I wanted to try SQL Azure Hyperscale, after I selected it seems I no longer have the option to move out of it. If I try, I get this message "databases cannot be moved out of hyperscale tier".

The Copy option is not available and restore does not give you the capability of choosing a different configuration.

Is there a way to move to another Tier ?


texts: SQL Service Using Group Managed Service Account does not start after reboot

texts:
We are using group managed service accounts for our SQL Server 2016 servers.  For some reason, when we reboot the server, the service does not start and we see this in the event viewer:

  The MSSQLSERVER service was unable to log on as ds\gsaNQSQLRSNSVC$ with the currently configured password due to the following error:
  The specified domain either does not exist or could not be contacted.

I then go to SSCM and start up the service and it starts up just fine.  The workaround for this is to start it with Automatic (Delayed Start).  I can't figure out why it won't just start up on reboot.  Does anyone know why this is happening?

The DCs are Win 2016 servers. The servers are in AWS and they are in the same VPC, the SQL Server and DC.


texts: Oracle Enterprise Manager Jobs Failing Initialization

texts:
The Enterprise Manager jobs on our RAC system recently started intermittently failing initialization with the following error:

  Error evaluating parameter sources/security checks/locks ORA-06508:
  PL/SQL: could not find program unit being called

We have tried bouncing DBConsole, re-submitting the job from the library and even recompiling the package being referenced.  This is happening to multiple jobs with about one execution in every ten scheduled succeeding.  Any ideas on what can be tried or checked?

Update: I've opened a SR with Oracle.


texts:
my table has this structure:

 User_ID | Followed_ID

Example row:

 33 | 36

Means that user 33 follows user 36.

If exists a record like

 36 | 33

then the two users are considered friends.
What's the cleanest way to get all friends of(for example) user 36? Thanks.


texts: Can SQL Server 2008 R2 get email name resolution from Office 365?

texts:
We just moved from an on premise Exchange server to Office 365's Exchange Online.  My question is, is there any way to still get email name resolution from Office 365?  I have many stored procedures and SQL Agent jobs where the name is specified instead of the email address, and I'd like to know if I can do something to avoid needing to find and change all of them.

So, can "John Doe" be translated to "jdoe@domain.com" using Sql Server 2008 R2 db_mail and Office 365 Exchange Online, like I could do with the old on premise Exchange?


texts: How does MySQL Workbench decide what number to put in parentheses after a datatype?

texts:
I create columns in MySQL Workbench and it often puts different numbers in parentheses after datatype, e.g. I input this:

Column Name          Datatype:
id                   INT
account_parameters   INT
accounts             TINYINT
account_properties   TINYINT

and after executing the query I get

Column Name          Datatype:
id                   INT(10)
account_parameters   INT(11)
accounts             TINYINT(1)
account_properties   TINYINT(2)

Why does it do it? Is there a reason why one should be INT(11), while others are INT(10)?


texts: Associative property inside functional dependancy

texts: I have a simple question regarding functional dependancy.. is AB --> C the same as BA --> C? thanks.


texts:
I have multiple tables with the same name but different schemas.  I want to script out the drop of one of these tables from replication.  I can't find where or how I can declare the schema name.  It only defaults to dbo if I run below

EXEC sys.sp_dropsubscription
@publication = 'SGSysTestLVSREP',
@article = 'DropArticleTableName',
@subscriber = 'Server11',
@destination_db = 'SysTest'

EXEC sys.sp_droparticle @publication = 'SGSysTestLVSREP',
@article = 'DropArticleTableName',
@force_invalidate_snapshot = 0


texts: Is it possible to step through TSQL that uses variables, in SSMS?

texts:
If I enter TSQL into SSMS Query editor, I know I can "step through" the statements (ie. execute them one at a time) by highlighting a statement and hitting F5 on the keyboard.

It seems by default though, that session does not have an open transaction. Every execution is committed.

I can write my TSQL so that it begins with BEGIN TRAN and ends with a COMMIT or ROLLBACK - and this ensures that as I step through, I have the option of backing out of changes.

However the issue I have is when I declare variables. Consider:

declare @flag bit = 0;

select case when @flag = 0 then 'off' when @flag = 1 then 'on' end as FlagCheck;

Regardless of whether the above is wrapped up in a transaction, if I run the first statement on its own, then the next statement on its own, the second statement will fail because the variable has not been declared.

Is it possible to step through code like this and have SSMS understand that the effects of the earlier statement (declaring the variable, and assigning a value) should be carried over into the next step? (Again, even if wrapped in a transaction, this persistence of variables fails - even though the persistence of the data update is dependent on the transaction resolution - commit or rollback).


texts: Can I implement clustering with just 2 servers and one NAS?

texts:
I have the following architecture, it consists in only 2 servers, connected to a NAS.

I'm having trouble in implementing clustering because I can't have a domain controller in any of them (if I do the sql server complains...). Is there a way to implement clustering with these limitations?


texts:
For an assignment I have to build an application that is able to store tests. Every test has to be validated by a a different user when created or updated. When it has been validated by an user, the test gets an expiry date of the last time it was validated plus 2 years.

Everytime a test gets updated, it creates a new version: v1, v2, v3, etc. This versioning information is stored in the test_versions table.

The tests itself are all stored in the same tests table with a foreign key to the test_versions table (version_id)table. Thetest` table consists of multiple many-to-many relations with joining-tables. For the sake of simplicity, I didn't include these in my table definition.

create table tests
(
    id                  int auto_increment   primary key,
    name                varchar(255)         not null,
    description         varchar(2048)        not null,
    version_id          int                  not null,
    is_active           tinyint(1) default 1 null comment,
    created_at          timestamp            null,
    updated_at          timestamp            null
)
    collate = utf8_unicode_ci;

Every new test is made with a new revision. The tests table has an version_id column. This is referring to the test_versions table which has the following columns:

version_id
description
original_version_id (The test where this version is made off)
tag (v1, v2, v3, etc.)
is_current_version (An indicator if it's the test's current version)
expiry_date (The expiry date of the current version, after this date, the test would not be showing).

It's possible to have unlimited versions for every test.

Now the problem is that when a new test is created, a new version should also be created in the test_versions table. This is not possible in the current structure since every new record in the test_versions table needs an test_id.

Dropping, or not working with foreign keys would not be an option.

Questions

How can I create the table structure in a way that I can still have unlimited versions without having the foreign key problems? How does the StackExchange site do it for example?
In my structure, every new test needs a version. Can this be done any better or is it efficient to split data over multiple tables?
Could there be another table structure for not working with these FK's in both directions?
Is it efficiënt to store more versions in the same tests table? Is there any other/better way?


texts: SQL Server Index and Statistics Maintenance performance question

texts:
I tried to use IndexOptimize stored procedure on a database with a very high number of indexes (> 250.000). The initial step where stored procedure collects data what needs to be processed takes hours, even if I set @Indexes parameter to narrow down the work.

  SQL Server Maintenance Solution Version: 2019-02-10 10:40:47 SQL
  Server 2017 Standard Edition with latest CU14 installed.

At a customer I saw a database with > 500.000 indexes. After 12 hours data collection step was still running.

I would expect if I set @Indexes to a single index execution should be started immediately.

Here is an example of my stored procedure call.

EXECUTE dbo.IndexOptimize
@Databases = 'db',
@Indexes =  'db.dbo.'
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 50,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@LogToTable = 'Y',
@LockTimeout = 60

Can somebody share with me his experience in using IndexOptimize for a database with a very high number of indexes ?


texts: Postgres: get total number of matched records using UNION

texts:
I'm running a paginated SELECT ... UNION query, and I need to determine the number of pages that are in the resultset. For example, consider the query:

(SELECT id, 'table1' AS type, COUNT(*) OVER() AS matches FROM table1)
UNION
(SELECT id, 'table2' AS type, COUNT(*) OVER() AS matches FROM table2)
LIMIT 3

When I get the following resultset, I can determine the total number of matched records:

| id | type   | matches |
|  1 | table1 |      12 |
|  2 | table1 |      12 |
|  1 | table2 |     150 |

Summing the distinct matches columns will then give me the total number of records returned, 162. But then if my current LIMIT/OFFSET doesn't give me any records from table2, I can no longer tell how many pages of data to expect:

 | id | type   | matches |
 |  1 | table1 |      12 |
 |  2 | table1 |      12 |
 |  3 | table1 |      12 |

How can I find the total number of matched records from all clauses in a UNION even if not all clauses return records within the current LIMIT/OFFSET scope?


texts:
I have enough PowerShell skills to get off the ground, but have none with SQL server. Can someone please point me in the right direction of how to use PowerShell to get information on remote SQL servers to identify what databases are running on them.

Thanks for any help.

Edit - Using SMO Code

I have tried to put this code together, but get the error: Cannot find type [Microsoft.SqlServer.Management.Smo.Server] make sure the assembly containing this type is loaded

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") "MSQLULTDBS04"
foreach($sqlDatabase in $sqlServer.databases) {$sqlDatabase.name}


texts: HA - MySQL Master Master replication - Duplicate entry errors

texts:
I am running Percona MySQL 5.5.39 on two masters with keepalived monitoring a VIP which will failover to the standby master if the primary master goes dark. While testing the fault tolerance of my setup in the lab I noticed that I am getting duplicate key entries on both masters.

Basically I run siege to emulate user load, for a duration of five minutes, within that time duration I shutdown the primary master, and after about 10 seconds for keepalived to detect the outtage, the VIP is switched the standby master, at which time everything is working as expected. Then after a minute or so, while siege is still running, I power on the primary master, and when online, it assumes the VIP.

It is during the swapping of the VIP that I think the duplicate entry issue is occurring. I followed this post explaining how to implement 'crash resilient' replication, but after implementing the suggested my.cnf changes and rerunning the user simulation, I ended up with the same broken replication.

There is also the ability for the standby master to increment it's indices by 2 instead of 1 to avoid collisions with the primary master, but I feel this is more of a hack than a solution.

Is there any better way to avoid primary key collisions in an HA environment?


texts: Restore the latest backup on the database

texts:
We have a stored proc which takes the backup from a database to the specified location. Sometimes the backup process runs more than once. It causes having more than one backup set on the same file name. When we write T-SQL to restore the target db using this backup file, how can we tell SQL to restore the latest db backup set on the target database (using the T-SQL command)?

When we restore the backup using MS SQL management studio, we can simply select the back up set we want to restore. But I need to run the restore using T-SQL and wondering how can determine SQL uses the latest db backup set to restore. For example from the image below, I want to restore the last backup set (highlighted in yellow):

I checked this website, but couldn't find the answer. I appreciate it if you could help.

Thanks.


texts:
What's the difference between REGEXP_SUBSTR and REGEXP_EXTRACT?
As far as I can tell, they seem to do the same thing. Is there some difference in side-effects, or how they perform for certain inputs? How do I know which to choose?


texts: Listener pointing to incorrect AlwaysOn Availability Group

texts:
All, Thanks in advance
I have two instances of SQL Server 2012 SP1 on a three node WSFC with a HAdr group on each instance with a corresponding listener.  Instance 1's listener points to Instance 1, no issue.
Instance 2 listener also points to instance 1.  I'm at a loss why listener #2 is showing databases for Instance 1.  Ive recreated instance 2's HAdr groups from scratch but still no change.  Here is some additional information -

Both instances Database mirroring endpoints are configured on different ports.
The SQL Server instances are named instances also using different ports.
Listeners have different names and different IP addresses and ports.
Opening up each SQL instance on each of the three nodes show the correct HADR information and databases.

The Listener seems to be getting confused and is looking at the wrong instance.

Any help would be greatly appreciated
thanks
James


texts: I have SELECT field(foo,'this','that','bar','baz') FROM t WHERE f1 IN ('this','that','bar','baz') How can I avoid listing ('this','that','bar','baz') twice?


texts: Install Full Text search on existing instance

texts:
We have an existing sql server 2012 instance but without full text search.
before I add the feature, I want to know if it stops or restart the instance since it is for production.

thanks in advance.


texts: Cost of updating one of several toasted columns

texts:
I have a table in Postgresql 12 which has --- among other columns --- two columns a and b of type bytea which are most of the time TOASTed. STORAGE is set to EXTENDED for both of them.

Assuming all other columns could be stored in the non-toasted table, would an update to column b require writing column a again? Would Postgres store the values of column a and b of one row in one row of the TOAST table or in two or does it depend? And then on what?

You can assume the values in a and b occupy usually multiple kilobytes, up to several hundred kilobytes.


texts: Exclude IP addresses of Vulnerability Scanner

texts: I am working with the SQL team to see if there is a way to globally whitelist IP addresses that are causing alerts within sp_Blitz? Would like to have them exclude 5 IP addresses.


texts: Issue migrating from SQL Server 2008 R2 to SQL Server 2017 Standard (on Linux)

texts:
I am currently trying to carry out a database migration from SQL Server 2008 to SQL Server 2017 (Linux) using the DMA tool.

I get the all green after running the assessment, however I get hit with an "out of memory" exception during the migration process.

Any advice on how I can resolve this issue? Both source and destination servers have 16GBs of RAM. This should be sufficient, no?


texts: Ms-access defining additional Index Fields

texts:
I am developing an access database that eventually will grow in size. For that reason scalability has to be taken in account.

My background: no official studies about databases.

I have read Access Database Design & Programming (O'Reilly) and I have taken multiple courses regarding databases (I keep studying and reading)
So far I've been working on developing a database from scratch with tables up to 50-60k rows (but will get bigger).
Advanced VBA user and comfortable with C#

I would like to confirm/deny some assumptions that might jeopardize the project.

Let say tblA has minimal superkey uniqueID. uniqueID is a text string of 7+ char. Is It necessary(recommended) to define a new field type numeric(integer) that will be used as the FK in other tables to generate the relationship?
Let's say, in the future will be migrated to a SQL server. Will it help?
Applying the prior principle.
When connecting the database with Excel, user needs to see information attribute that helps him to identify a record but when I send it back to the database I need to pass the numeric value that connects both records.
Let's say we need to update a record in tblInventory that has relationships with tblEmpl/tblClients/tblPrices.

How should I handle the conversion of empl_name="Carlos" to emplID=1?

Maintaining a dictionary key/item?
Generating recorset to lookup the value in tblEmpl?
Is there any SQL {INSERT INTO;UPDATE} syntax to create INNER JOIN so it automatically converts it? (I haven't found anything that applies to SQL Access)

By using numeric ID to lookup values in table (when the table has 3/4/+ relationships), SELECT statement results in a nested INNER JOINs that look really ugly:

(ie: want to find values on tblC where the emplName is "MyName" and clientName is "MyClient". Need nesting tables to find those attributes)

SELECT tblC.ID,tblA.Name,tblB.Client
FROM tblC INNER JOIN (tblA INNER JOIN tblC ON tblA.ID = tblC.FK_A)
ON tblB.ID = tblC.FK_B
WHERE (((tblA.Name)="MyName",(tblB.Client)="MyClient"));

If I have 4 different relationships, It gets scary.

As I self-taught, I am never quite sure if I just read the wrong post in stackoverflow or this is the way it is and I just should bear with it.

Any light upon those questions will be greatly appreciated.


texts: What's the purpose of the cmd column in the sp_who stored procedure result set?

texts:
When executing sp_who I get this

I want to know what's the purpose of cmd column. What parameter does it represent?
Thanks for your time!


texts:
We currently connect to SQL Server 2014 with the Native Client without any issues.

However, the SQL Server is being upgraded to SQL 2017. I have been told that SQL Native Client will no longer work for connecting, and we need to install the OLE DB driver instead.

Is this correct? I have not found much online other than the Native Client is deprecated and also this article on the lifecycle.

What impact would this have on existing .Net applications and SSIS packages that connect to the database.

Here is a sample connection from an application...

connectionString="metadata=res://*/LoggingModel.csdl|res://*/LoggingModel.ssdl|res://*/LoggingModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=myServer;initial catalog=DBLogging;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient"


texts:
I am trying to learn SQL development using SQL Server 2008 R2 Database Management Studio.  I was trying to model a car rental company, where clients can rent cars, and I have to know which client has rented which car on which date, and also the date the car was returned.  I have tried to the following:

--create the database
create database RentACar;
use RentACar;

--create the tables
create table Clients(
Id int not null constraint pk_ClientID primary key,
Name nvarchar(255),
LastName nvarchar(255)
);

create table CarPool(
Id int not null constraint pk_CarPoolID primary key,
Brand varchar(40) not null,
Price decimal not null
);

create table DateOut(
Id int not null constraint pk_DateOutID primary key,
DateOut datetime not null
);

create table DateIn(
Id int not null constraint pk_DateInID primary key,
DateIn datetime not null
);

--fill the tables with some values
insert into Clients values (1, 'Richard', 'Castle');
insert into Clients values (2, 'Jane', 'Rizzolli');
insert into Clients values (3, 'Jack', 'Bauer');

insert into CarPool values (1, 'BMW', 125.99);
insert into CarPool values (2, 'Lexus', 105.99);
insert into CarPool values (3, 'Honda', 95.99);

insert into DateOut values (1, 2012-03-10);
insert into DateOut values (2, 2012-03-11);
insert into DateOut values (3, 2012-03-12);

insert into DateIn values (1, 2012-04-10);
insert into DateIn values (2, 2012-04-11);
insert into DateIn values (3, 2012-04-12);

--now try to connect the tables somehow...

I don't know how to connect the tables with primary and foreign keys, so as to be able to for example query a list of all people who did not return the car yet, and a field that calculates how long a car has been with a client.  I would also like to create a query that allows me to update the datetime for clients that have not returned their cars.

I am only familiar with the basic SQL commands like create, add, update, insert into, and i am at the end of my wits here.  Nothing I try works.  I always get errors no matter what i try. I am going slowly through MS Tranact-SQL documentation but I cannot seem to find what I am looking for.  I know the tables should be normalized and kept to a minimum, data should not be redundant etc... I just can't seem to find the right combination.  Can someone please point me in the right direction?

@BryceAtNetwork23
thanks for editing my post with code.

@Lumpy
thank you for your help. I didn't have time to check if someone had replied to my question here, and last night i was thinking how to finally solve this and came up with the same conclusion. I should have two tables, one for Clients and one for the Cars, and then one table to connect them, by simply creating a column with both dates, in and out, and then the key fact being the two columns that reference the IDs of the particular Client and a particular Car using foreign keys. Also the date(s) being in the same row with these Client ID and Car ID is the connection to the Client and Car tables.  Eureka! I felt so stupid, haha, it was right there in front of my nose.

Anyway, I think SQL is amazing, and I am just beginning to grasp the possibilities as I go deeper into things, and thank God for dba.stackexchange and folks like yourself, it makes learning  a whole lot easier.

Thank you


texts: Slow Connection to Azure SQL Database using SQL Server Authentication

texts:
In SSMS, when connecting to any of our Azure SQL Databases using service accounts and SQL server authentication, the connecting itself can take between 10 to 15 seconds to establish.

Once connected, right click on any table and click on "select top 1000 rows" this also takes between 10 and 15 seconds to respond and present the rows in SSMS.

None of this delay/lag occurs when using "Active Directory - Universal with MFA Supprt" authentication - however, this isn't using the same service accounts.

How can the SQL Server Auth lag be resolved/investigated?


texts: Grant MonitorAdmin / PROCESS privilege to user

texts:
I need to assign the monitoradmin privilege to a user because this is what an application called "dynatrace" needs to monitor the server (some details in a post on their site).

According to https://mariadb.com/kb/en/grant/ there is a PROCESS privilege but I can't assign it.

MariaDB [(none)]> CREATE USER dynatrace IDENTIFIED BY 'secret';
Query OK, 0 rows affected (0.007 sec)

MariaDB [(none)]> GRANT MonitorAdmin TO dynatrace;
ERROR 1959 (OP000): Invalid role specification `MonitorAdmin`

Same result with PROCESS:

MariaDB [(none)]> GRANT PROCESS TO dynatrace;
ERROR 1959 (OP000): Invalid role specification `PROCESS`

Where is my mistake ? Although my knowledge about mariaDB is more than limited I have the password of the root account ;-) and can run the commands as this user.


texts: Unable to mirror a database SQL Server 2012

texts:
When trying to mirror a database using the following command

ALTER AVAILABILITY GROUP SQLAlwaysonGroup ADD DATABASE test0916aj8CJ

I get the following error

  Msg 1475, Level 16, State 105, Line 1
  Database "test0916aj8CJ" might contain bulk logged changes that have not been backed up. Take a log backup on the principal database or primary database. Then restore this backup either on the mirror database to enable database mirroring or on every secondary database to enable you to join it to the availability group.

Can this be done without backing the database? Or should I backup and then discard the backup. It is for a newly created db, so I do not need the backup anyway at this point.

I have tried the following...

BACKUP
DATABASE [test0916aj8CJ] TO DISK = N’NUL’
WITH COPY_ONLY, NOFORMAT, INIT,
NAME = N’test-Full Database Backup’,
SKIP, NOREWIND, NOUNLOAD
GO

but the above method did not work either.

Thanks


texts: Issues with Log Shipping in SQL Server 2019

texts:
We're in the process of migrating a customer from a SQL Server 2012 environment to 2019.

I've been trying to get the log shipping up and running as it was in the old environment but I keep coming up against errors.

The first error I get is in the restore job on the secondary server:

2020-02-25 16:04:04.77  Skipped log backup file. Secondary DB: 'DB_Name', File: 'E:\LogShipping\DB_Name_20200225154501.trn'
2020-02-25 16:04:04.77  Error: Could not log history/error message. Microsoft.SqlServer.Management.LogShipping)
2020-02-25 16:04:04.77  Error: Failed to convert parameter value from a SqlGuid to a String.(System.Data)
2020-02-25 16:04:04.77  Error: Object must implement IConvertible.(mscorlib)

If you google that, it leads you to a page suggesting the fix is to install cumulative update 2:  https://support.microsoft.com/sq-al/help/4537869/fix-log-shipping-agent-is-not-able-to-log-history-and-error-informatio

I tried installing that but immediately started noticing an issue where when you open Job Activity Monitor, the list of jobs does not load and you get an error message including the text "Failed to retrieve data for this request".  You also cannot do things like edit Log Shipping schedules for servers affected.  Nothing will work until you restart SQL Server Agent on the affected server.  If you check the job history, you can see that no scheduled tasks run during the time that SQL Server Agent is stalled.  SQL Server Agent doesn't appear to crash completely which is a shame in a way, because it should re-start itself in that case.

Searching for this error leads you to a dba.stackexchange.com page describing the same issue with the only resolution being uninstalling CU2.

I tried uninstalling CU2 but am immediately back to the original error message.

This leads me to believe that Log Shipping is broken in SQL Server 2019.

Is anyone actually using it successfully?

Did you come across either of these issues and manage to resolve them?

Should I be looking at mirroring or replication instead in 2019, or is Log Shipping still an acceptable warm-standby solution?


texts: Service Account Groups in SQL Server 2012

texts:
I was tracking down some permissions issues on SSRS 2012 and I started looking for the local groups that I am used to seeing for SQL Server.  I noticed something I had not seen before, the groups have changed in 2012.

I have checked on some of my other 2012 instances and am seeing similar.  Is everyone seeing this?

I have checked BOL but am a finding it a little unclear.  I found this information from Bob Beauchemin.  It looks like he is seeing the same thing I did.

My question is, does everyone else running 2012 see the same thing?


texts: Adding a CHECK constraint doesn't improve performance of ATTACH PARTITION on postgres table

texts:
I'm attempting to merge a table into a partition, the merge works but the performance isn't good. I've read this link, in particular this quote....

Before running the ATTACH PARTITION command, it is recommended to create a CHECK constraint on the table to be attached describing the desired partition constraint. That way, the system will be able to skip the scan to validate the implicit partition constraint. Without such a constraint, the table will be scanned to validate the partition constraint while holding an ACCESS EXCLUSIVE lock on the parent table. One may then drop the constraint after ATTACH PARTITION is finished, because it is no longer necessary.

Here's the SQL I'm using....
ALTER TABLE mess_201811 ADD CONSTRAINT const_201811 CHECK ( loadedtime >=  DATE '2018-11-01' AND loadedtime < DATE '2018-11-23' );
ALTER TABLE mess ATTACH PARTITION mess_201811 FOR VALUES FROM ('2018-11-01') TO ('2018-11-23');
ALTER TABLE mess_201811 DROP CONSTRAINT const_201811 ;

As close as possible I've copied my SQL from the link mentioned. The three commands all work without problem, the issue is the time it takes. Adding the constraint takes about 60 seconds (fair enough, there are about 2 million records in the table). However the ATTACH PARTITION command also takes about 60 seconds, this suggests to me that table is being scanned a second time validate the partition. My understand was that with the CHECK constraint in place the ATTACH should be a simple DDL statement and would only take a fraction of a second.
The problem is that ATTACH PARTITION requires an exclusive lock on the table mess which prevents inserting records for 60 seconds.
I'm using postgres v10.5. the definition for table mess ends with
PARTITIONED BY loadedtime;

At the time of my testing there were no other users on the database and no lock issues.

Following jjanes solution, I've checked the original documentation, the example quoted does use a NOT NULL field constraint although it make no mention of this being mandatory. I'm hoping that the additional constraint won't have a noticeable effect on normal inserts to this table.


texts:
Moving application from SQL Server to PostgreSQL Database. I am getting this error from one of the queries used in a Java file:

ERROR: collation "database_default" for encoding "UTF8" does not exist
Position: 467. Stacktrace follows:
org.postgresql.util.PSQLException: ERROR: collation "database_default" for encoding "UTF8" does not exist
Position: 467

Can you please let me know how to resolve this issue?


texts:
I got a reasonable simple query:

With RowsToDelete AS
(
    SELECT TOP 500 Id
    FROM ErrorReports
    WHERE IncidentId = 611
)
DELETE FROM RowsToDelete

However, it do not complete. I have tried several times. The last time I waited 8 minutes before canceling it.

ErrorReports contains about 22 000 rows. ErrorReportOrigins about the same.

Estimated execution plan:

Actual execution plan (for top 10, takes 28 seconds to complete):

Execution plan:
https://www.brentozar.com/pastetheplan/?id=S1jUXDruz

Client stats:

What I've tried:

ErrorReportOrigins did not have a clustered index (id), only a FK to ErrorReports.Id. I've added an id column (pk&identity).
I've rebuilt all indexes (using this).
Tried to delete from ErrorReportOrigins first (using the same CTE). No difference
(The original CTE had a ORDER BY Id, I removed it to see if there was a difference)

I'm lost. Why on earth does it take so long? All SELECT statements are fast.
And the DB isn't really big. The ErrorReports table is the largest one.

(This is an SQL Azure DB in an elastic pool)

Update

CREATE TABLE [dbo].[ErrorReports](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [IncidentId] [int] NOT NULL,
    [ErrorId] [varchar](36) NOT NULL,
    [ApplicationId] [int] NOT NULL,
    [ReportHashCode] [varchar](20) NOT NULL,
    [CreatedAtUtc] [datetime] NOT NULL,
    [SolvedAtUtc] [datetime] NULL,
    [Title] [nvarchar](100) NULL,
    [RemoteAddress] [varchar](45) NULL,
    [Exception] [ntext] NOT NULL,
    [ContextInfo] [ntext] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

Indexes:

CREATE NONCLUSTERED INDEX [Application_GetWeeklyStats] ON [dbo].[ErrorReports]
(
    [ApplicationId] ASC,
    [CreatedAtUtc] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

CREATE NONCLUSTERED INDEX [ErrorReports_IncidentId] ON [dbo].[ErrorReports]
(
    [IncidentId] ASC,
    [CreatedAtUtc] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

The data is fairly large (used datalength in the query):


texts: Postgres 12: Are there performance differences between PRIMARY KEY(A, B) and PRIMARY KEY (B, A) on a table PARTITION BY (A)?

texts:
Given a table this table:

CREATE TABLE tbl (
  a int not null,
  b int not null,
  created timestamp not null default now(),
  primary key(a, b)
) PARTITION BY LIST(a)

The query plan for a lookup on columns a and b looks like this:

EXPLAIN ANALYZE SELECT EXISTS(SELECT * FROM tbl WHERE a = 1 AND b = 1)

Result  (cost=2.37..2.38 rows=1 width=1) (actual time=0.013..0.013 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Index Only Scan using pkey on partition  (cost=0.15..2.37 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=1)
          Index Cond: ((a = 1) AND (b = 1))
          Heap Fetches: 0
Planning Time: 0.860 ms
Execution Time: 0.033 ms

I'm wondering if defining the primary key in reverse column order (PRIMARY KEY (b, a) ) PARTITION BY LIST (a)) results in better performance as the index used in the lookup contains the interesting column first, as filtering by a is already achieved by the query planner picking a single partition to run the query on.


texts: After trying to restart moved database: Unable to open the physical file "D:\Data\MYDBNAME.MDF"

texts:
So the default location for databases on our server is

c:\program files\microsoft sql server\mssql11.mssqlserver\mssql\DATA

but due to space limitations we instead use

d:\data

I have now since corrected the default location to be d:\data

However I am unable to move a database that was created in the wrong location i.e. from c:\...\data to d:\data

Trying

ALTER DATABASE MYDATABASE set offline

ALTER DATABASE MYDATABASE
MODIFY FILE ( NAME = 'MYDATABASE', FILENAME = 'D:\Data\MYDATABASE.MDF')

ALTER DATABASE MYDATABASE
MODIFY FILE ( NAME = 'MYDATABASE_LOG', FILENAME =  'D:\Data\MYDATABASE_LOG.LDF')

ALTER DATABASE MYDATABASE SET ONLINE

fails with the following error message

  Msg 5120, Level 16, State 101, Line 1
  Unable to open the physical file "D:\Data\MYDATABASE.MDF". Operating system error 2: "2 (The system cannot find the file specified.)". File activation failure. The physical file name "D:\Data\MYDATABASE_log.lDF" may be incorrect.

  Msg 5181, Level 16, State 5, Line 1
  Could not restart database "MYDATABASE". Reverting to the previous status.

  Msg 5069, Level 16, State 1, Line 1
  ALTER DATABASE statement failed.

The server can definitely access the new location as all other databases in d:\data work ok


texts:
I have accidentally deleted a flashback point in a oracle 11g database,Is there a way to recover the restore point

PS:If I have a flashback point A which was created first, and flashback point B which was created later.If I drop flashback point B and flashback to flashback point A will I be able to recover flashback point B.

PPS: there are no other backups to recover from.


texts: return age in years and months from date of birth

texts:
Using MySQL, I return the age in years and months as separate fields in a query.

This may well be a premature optimization but the duplicate calls to datediff and from_days in this query are upsetting.

Is it possible to get the from_days(datediff(now(), user_birthdate)) factored into a single call without a nested query?

SELECT
    user_id,
    year(from_days(datediff(now(), user_birthdate))) as years,
    month(from_days(datediff(now(), user_birthdate))) as months

or... would a nested query be a win over this current query?

Like this:

SELECT
    user_id,
    YEAR(age) AS years,
    MONTH(age) AS months
FROM
    (SELECT
        *, FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(user_birthdate)) AS age
    FROM
        users) AS users_temp

If someone can give me some hints about the best way to test the relative performance of these two methods empirically, that would be useful too


texts: SQL Server 2000 Hitting 8000 Byte Limit Exporting BLOB

texts:
I'm trying to help out my security by exporting user data from a legacy access system. The back end is SQL server 2000. The data I'm particularly interested in is the user photograph, which is stored as a BLOB in the database.

With a bit of Googling I found a stored procedure to help with this, but when I tried to create it I was getting errors when declaring a variable as

DECLARE @ImageData VARBINARY (max)

A similar error occurred in this line;

SELECT convert (VARBINARY (max), Face, 1)

I was getting an error stating "max" was invalid. After a bit more Googling I realised that server 2000 wouldn't accept "max" and instead I used 8000, which is the maximum varbinary size.

My export worked to some extent, in that the image data exported, but only the first 8KB. Obviously this is due to the maximum varbinary size being 8000. the images them selves are OK for the top of the picture, but then just flat grey.

Is there a way to get the whole picture out? the stored procedure I'm using is as follows;

CREATE PROCEDURE dbo.JB_Export_Photos (
   @PicName NVARCHAR (100)          -- name of picture in db USE cardID
   ,@ImageFolderPath NVARCHAR(1000) -- name of folder which will be saved to
   ,@Filename NVARCHAR(1000)            -- filename after export add .jpg
   )
AS
BEGIN
   DECLARE @ImageData VARBINARY (max);
   DECLARE @Path2OutFile NVARCHAR (2000);
   DECLARE @Obj INT

   SET NOCOUNT ON

   SELECT @ImageData = (
         SELECT convert (VARBINARY (max), Face, 1)
         FROM picTable-- needs changing to real database name
         WHERE picturename = @PicName                       -- 'pictureName' needs changing to match field in db, probably cardID
         );

   SET @Path2OutFile = CONCAT (
         @ImageFolderPath
         ,'\'
         , @Filename
         );
    BEGIN TRY
     EXEC sp_OACreate 'ADODB.Stream' ,@Obj OUTPUT;
     EXEC sp_OASetProperty @Obj ,'Type',1;
     EXEC sp_OAMethod @Obj,'Open';
     EXEC sp_OAMethod @Obj,'Write', NULL, @ImageData;
     EXEC sp_OAMethod @Obj,'SaveToFile', NULL, @Path2OutFile, 2;
     EXEC sp_OAMethod @Obj,'Close';
     EXEC sp_OADestroy @Obj;
    END TRY

 BEGIN CATCH
  EXEC sp_OADestroy @Obj;
 END CATCH

   SET NOCOUNT OFF
END
GO

Thanks in advance.


texts: SQL Join to a Same Combination of Tables

texts:
I want to join a first table to same combination of joins: X,Y,Z. The first table can be different table types, but they all join to same set of tables. Table A may have 8 columns of different data types, Table B may have 15 columns of different table types. How do I code for this without repetition? The tables join to other tables later. How would I save this combinations of joins in a view or table function, etc, if the first table is always different?

We have 100+ queries like this, and eventually may want to add more tables to saved X-Y-Z combination, maybe 7 in the future. S-T-U-V-X-Y-Z. The question becomes more of code versatility. Would like to change in 1 spot, rather than 100 different queries.

Note: Table X,Y,Z have different columns and data types, except on ProductId and ProductType.

Select a.*, x.*,y.*,z.*
from TableA a
left join DimShelf x
  on b.ProductId = x.ProductId
  and b.ProductTypeId = x.ProductTypeId -- 1
left join DimDesk y
  on b.ProductId = y.ProductId
  and b.ProductTypeId = y.ProductTypeId -- 2
left join DimCouch
  on b.ProductId = z.ProductId
  and b.ProductTypeId = z.ProductTypeId -- 3
left join other items of variation with where clauses at end...

Select b.*, x.*,y.*,z.*
from Tableb b
left join DimShelf x
  on b.ProductId = x.ProductId
  and b.ProductTypeId = x.ProductTypeId -- 1
left join DimDesk y
  on b.ProductId = y.ProductId
  and b.ProductTypeId = y.ProductTypeId -- 2
left join DimCouch
  on b.ProductId = z.ProductId
  and b.ProductTypeId = z.ProductTypeId -- 3
left join other items of variation with where clauses at end...

create table dbo.DimChair
(
    DimChairId int primary key identity(1,1),
    ProductTypeid int,
    ProductId int,
    Color varchar(55),
    LegNumber int
)

create table dbo.DimShelf
(
    DimShelfId int primary key identity(1,1),
    ProductTypeid int,
    ProductId int,
    Length float,
    Height float,
    NumberofShelves varchar(55)
)

create table dbo.DimCouch
(
    DimCouchId int primary key identity(1,1),
    ProductTypeid int,
    ProductId int,
    FabricType varchar(255),
)


texts: MySQL Scheduled Event and server offline?

texts:
I have an event planned to run a 00:00. But the server won't be online at that point, seeing it's a local development server. Will it run the next time the server is online, or will it just be forgotten?

The event is as follows:

    CREATE DEFINER=`root`@`localhost`
    EVENT `winners`
    ON SCHEDULE EVERY 1 DAY STARTS '2013-02-19 00:00:00'
    ON COMPLETION PRESERVE ENABLE
    DO
    UPDATE `data`
    SET wins = wins + 1
    ORDER BY cur_score DESC
    LIMIT 1


texts:
My requirement is as follows:  An app with internationalization support and starting with EN and FR versions of text. So, I have 2 approaches for data in the table:

1. One column for each language:  So, my table columns will be as below, which means it will have one column for each language. So, in future if I add one more language, lets say German then I will add one more column LABEL_GM and will write update queries to add German labels.

    "LABEL_EN" VARCHAR2(1000 BYTE),
    "LABEL_FR" VARCHAR2(1000 BYTE),

2. One row for each language:  So, my table columns will be as below, which means it will have one row for each language. So, in future if I add one more language, lets say German then I will write insert queries to add German labels.

    "LABEL" VARCHAR2(1000 BYTE),
    "LANGUAGE" VARCHAR2(2 BYTE),

Questions:

Which is more space and performance efficient way in today situation when I am having only 2 language support.
Which is more space and performance efficient way from future perspective, in case if we need to add one more language support, so main concern is how efficient will be data insertion/updation.


texts: Dealing with identity ranges for transactional replication

texts:
I've noticed that when you set up a transactional replication, SQL Server will set identity range management to manual.  What this means is that in my subscription database, when I try to insert a new record into a table whose PK is an identity column, it will give me an error and say that it tried to insert a PK of "1", "2", "3", etc.  This is because the current identity value for all identity columns on the subscriber gets reset to the seed value (usually 1) instead of staying at what it was on the publisher.

I understand why SQL Server does this - you're supposed to leave the subscriber table as read-only.  However, my scenario is a little unorthodox - I update my subscriber from time to time through replication, make an immediate backup of that DB, then I want to do some updates to the subscriber that WON'T be pushed back to the publisher, then when I go to update the subscriber again, I restore its database from the earlier backup and pull the latest updates.  Because I want to do updates to the subscriber in between these updates ('temporary deltas' if you will), I need the identity column to work and not to reset to 1 when replicated.

I tried turning on automatic identity range management when setting up my publication, but that just gives me the following error when I try to add a table to the publication:

  Msg 21231, Level 16, State 1, Procedure sp_MSrepl_addarticle, Line 2243
  Automatic identity range support is useful only for publications that allow updating subscribers.

Is there any way I can get round this problem?  I do kind of want to present this replication to SQL Server as if it were read-only at the subscriber end because I don't plan on making updates that will be pushed back to the publisher, but I do want to make temporary updates that will be erased before the next replication.

I have also considered that snapshot replication might be a more appropriate method than transactional replication for my usage pattern, but the trouble is that snapshot replication requires sending the whole darn DB every single update; because I'm planning on taking an immediate backup of the DB after the latest replication, I shouldn't need to do that whole transfer every time; just the changes since last time.


texts: Is it possible give an SA like permission minus any access to user databases?  I want people to be able to collect information about Sql Server from the system databases but I want to make sure the User databases cannot be touched.  What is the best way to approach that?


texts: Error while creating CMK(column master key) in always encrypted

texts:
I am trying to explore SQL 2016 always encrypted feature but i am getting an error while generating new column master key, after entering name and selecting 'Windows Certificate Store-Current User' as key store through SSMS when i click on ok its throwing an error as  object reference not set to an instance sql(sqlmanagerui)
 I googled it and checked the permission on registry but unable to solve this.


texts:
I am trying to deploy galera multimaster replication but faced some problems, google does not solve it it seems, but I see some people had the same problems in the past.

Say, I have 3 servers

192.168.1.1
192.168.1.2
192.168.1.3

first I figured out that primary node has to be intianalized like this only once (init system independent below).

/etc/init.d/mysql start --wsrep-new-cluster

how should the wsrep_cluster_address="gcomm://" kook like on the each node?

somewhere I read it has to list each address on the each node:

like wsrep_cluster_address="gcomm://192.168.1.1,192.168.1.2,192.168.1.3"

Further googling showed that primary node should only have

wsrep_cluster_address="gcomm://"

then the second node:

wsrep_cluster_address="gcomm://192.168.1.1"

and the thrid:

wsrep_cluster_address="gcomm://192.168.1.1,192.168.1.2

Sorry if it looks too long, I have been digging up for 4 days already every day :/


texts: Database Offset & Limit without order by?

texts:
To my understanding, in MySQL/Postgres database there is no guarantee for the order of values with a select statement without an order-by clause, thus one should be getting rows arbitrarily. If so, then why are there usually no duplicates when I perform such query?

SELECT * FROM `table` LIMIT 0,5
SELECT * FROM `table` LIMIT 5,5
SELECT * FROM `table` LIMIT 10,5

Aren't these queries safe to use, considering that one only cares about not fetching duplicates and does not mind about the ordering? If so, how then?


texts: How to handle nested groupings with possible null elements in one query

texts:
As you can see in this sql-fiddle, I have three tables: events, event_dates, and event_times.

The original tables contain various other fields, which I omitted as they aren't relevant, I just need to have them in my final result, but I do no operation/sort/filtering/grouping on them.

An event has 1+ event_dates and an event_date has 0+ event_times.

From an object point of view, an event object has a list of event_date objects, which in turn can have a list of event_time objects.

My final goal is to obtain an array of event objects.

My plan is to design a query returning the ids of matching records and from these results fetch the records from their tables in 3 queries and assemble everything in code (this is necessary because of various data-transformations outside the scope of SQL).

My problem is getting the first query right

At the moment I have the following query

SELECT events.id,
CONCAT('[', GROUP_CONCAT(event_dates.id SEPARATOR ','), ']') AS event_date_ids,
CONCAT('[', COALESCE(GROUP_CONCAT(event_times_per_event_date.event_time_ids SEPARATOR ','), '[]'), ']') AS event_time_ids
FROM events
INNER JOIN event_dates ON event_dates.event_id = events.id
LEFT JOIN (
  SELECT date_id AS event_date_id, CONCAT('[', COALESCE(GROUP_CONCAT(event_times.id SEPARATOR ','), ''), ']') AS event_time_ids
  FROM event_dates
  LEFT JOIN event_times ON event_times.date_id = event_dates.id
  GROUP BY event_dates.id
) AS event_times_per_event_date ON event_times_per_event_date.event_date_id = event_dates.id
GROUP BY events.id

which fails in the following situation:

Given event e with three dates d1,d2 and d3, where d1 has two times (t1,t2), d2 has zero times, d3 has two times(t3,t4).
The result of the query would be:

event_id  |  event_date_ids  |  event_time_ids
1         |  [1,2,3]         |  [[1,2],[3,4]]

What I'd expect would be an empty array in event_time_ids, so that I could match the indices of the array in event_date_ids with the one for the array in event_time_ids:

event_id  |  event_date_ids  |  event_time_ids
1         |  [1,2,3]         |  [[1,2], [], [3,4]]

How can I obtain this?

P.S.: I deeply apologise if my question is chaotic or unclear and I'm ready to provide every other necessary information.

Thank you!

UPDATE 1

Thanks a lot to @Andriy M for spotting my mistake.

Moving on, I have to add to the equation event_categories where events have 1+ categories.

Adding a DISTINCT in the GROUP_CONCAT would break my current solution as empty arrays would be removed.
The solution is to make this query into a subquery, and join its results to a query fetching all other event data and grouping by events.id.
The event_time_ids would be duplicate for every event row, thus selecting any row for that column is fine.


texts: Keep both accented and unaccented versions in a tsvector column?

texts:
I am facing a situation where users SHOULD submit items that are written in plain English. However, it happens that names have accents - coming from various languages. I have a tsvector column in my table which is indexed for fulltext search.

Here is the info about this column, and relevant info about the table:

                                                         Table "public.companies"
     Column     |          Type          |                       Modifiers                        | Storage  | Stats target | Description
----------------+------------------------+--------------------------------------------------------+----------+--------------+-------------
 id             | integer                | not null default nextval('companies_id_seq'::regclass) | plain    |              |
 name           | character varying(128) | not null                                               | extended |              |
 description    | text                   |                                                        | extended |              |
 tsmeta         | tsvector               |                                                        | extended |              |

Indexes:
    "companies_pkey" PRIMARY KEY, btree (id) CLUSTER
    "companies_search_idx" gin (tsmeta)
Triggers:
    companies_tsmeta_update BEFORE INSERT OR UPDATE ON companies FOR EACH ROW EXECUTE PROCEDURE companies_tsmeta_trigger()

Now the problem comes when the user creates a name like français. Not all the users have the ç character at hand and those who don't will have troubles finding this entry - meaning that it will be stored with the cedilla-c in the tsvector column. So my thought was to use the unaccent extension in order to strip these accents. However now users that just type français will have problems and will not find the entry. So I came up with a function that just concatenates both versions (accented and accent-stripped) of the name and descriptions. However, descriptions can get pretty large and I worry the index will take too much storage.

create or replace function companies_tsmeta_trigger() returns trigger as $$
begin
  new.tsmeta :=
    setweight(to_tsvector('english', coalesce(new.name, '')), 'A') ||
    setweight(to_tsvector('english', unaccent(coalesce(new.name, ''))), 'B') ||
    setweight(to_tsvector('english', coalesce(new.description, '')), 'C') ||
    setweight(to_tsvector('english', unaccent(coalesce(new.description, ''))), 'D');
  return new;
end;
$$ language plpgsql;

What are your suggestions?


texts: How to use an array as argument to a VARIADIC function in PostgreSQL?

texts:
I am trying to make a case-insensitive version of json_extract_path_text(), using the citext module.

I would like this to be a simple wrapper around the built-in function, the only difference being that it accepts citext as the first parameter instead of json. I want this to be a straight pass-through to the native implementation, just with a type conversion beforehand. Here's what I have so far:

create extension citext;

create or replace function json_extract_path_text ( string citext, variadic params text[]) RETURNS text IMMUTABLE AS
$$
    BEGIN
        SELECT json_extract_path_text(string::json, params);
    END;
$$
LANGUAGE 'plpgsql';

This doesn't work properly, however, because of the type mis-match:

ERROR:  function json_extract_path_text(json, text[]) does not exist
LINE 1: SELECT json_extract_path_text(string::json, params)
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT json_extract_path_text(string::json, params)
CONTEXT:  PL/pgSQL function json_extract_path_text(citext,text[]) line 3 at SQL statement

I've tried hacking around a solution using dynamic string construction and EXECUTE, but this is a real hassle and I feel like there must be a cleaner way to pass VARIADIC params through to the inner function. I can't see any obvious way to do so, however. How can I accomplish that?


texts:
When I tried to restore a backup an error occurred showing access denied. The error message is as follows:

  Restore failed for Server 'VAIO\sqlexpress'.
   (Microsoft.SqlServer.SmoExtended)

  System.Data.SqlClient.SqlError: The operating system returned the
  error '5(Access is denied.)' while attempting
  'RestoreContainer::ValidateTargetForCreation' on 'C:\Program
  Files\Microsoft SQL
  Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ihrd_mvk_newspaperboy.mdf'.
  (Microsoft.SqlServer.Smo).


texts: Production (20GB+) Database running slowly and timing out

texts:
I know the title thread is a bit ambiguous (and my first post here!) but I am having a very severe production database problem. I am not a DBA, though I know how to work with queries and SQL Manager. The database is constantly under load, as there's a constant input and output. The problems started about a week ago and I've done everything I know to get it working... from Rebuilding Indexes to Updating Statistics to Shrinking DB. As an example of the recent problem I used to run a daily backup at 5PM and it took 20-25 minutes, now its taking over an hour. Some days the thing will work just fine, for example yesterday we didn't have an issue and today its all over the place. Its a 20+GB Database running in a VM with Windwos Server 2008R2, dual Xeon @ 2.4GHZ and 12GB of RAM plus a partition for OS, one for DB and one for backups. As one of the measures I am taking I created a separate Log partition to move the log from the DB HardDisk to its own separate HDD. Haven't yet done it as I need to detach and take everything offline. I checked the fragmentation for the main table (5+ million entries) and it was over 99%... this was AFTER rebuilding the index.

At this point, I am just stuck. The application users use is timing out, transactions not going in or out (financial institution) sometimes. I am constantly monitoring the performance, and heck even the SQL Server Activity Monitor timed out for me a few moments ago. Any suggestions and what else should I do (aside from separating the log and DB) would be greatly appreciated.

EDIT: Server RAM Usage just in case:
[1]

EDIT2: Code used for rebuilding Index

  ALTER INDEX ALL ON DailyTransactions
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
          STATISTICS_NORECOMPUTE = On);


texts: Find tables with columns with empty and NULL values in Postgresql

texts:
After some research I found an example where I can find tables and its columns with NULL values. But the function also returns true when the column is empty. It gives me errors when I try to add an or statement. How can I modify the function so it returns also true when the column contains blank values? This is the function I found:
create function has_nulls(p_schema in text, p_table in text, p_column in text)
                returns boolean language plpgsql as $$
declare
  b boolean;
begin
  execute 'select exists(select * from '||
          p_table||' where '||p_column||' is null)' into b;
  return b;
end;$$;


texts: "Recheck Cond:" line in query plans with a bitmap index scan

texts:
This is a spin-off from comments to the previous question:

Postgres 9.4.4 query takes forever

Using PostgreSQL 9.4, there always seems to be a Recheck Cond: line after bitmap index scans in query plans output by EXPLAIN.

Like in the EXPLAIN output of the referenced question:

->  Bitmap Heap Scan on table_three  (cost=2446.92..19686.74 rows=8159 width=7)
      Recheck Cond: (("timestamp" > (now() - '30 days'::interval)) AND (client_id > 0))
      ->  BitmapAnd  (cost=2446.92..2446.92 rows=8159 width=0)
            ->  Bitmap Index Scan on table_one_timestamp_idx  (cost=0.00..1040.00 rows=79941 width=0)
                  Index Cond: ("timestamp" > (now() - '30 days'::interval))
            ->  Bitmap Index Scan on fki_table_three_client_id  (cost=0.00..1406.05 rows=107978 width=0)
                  Index Cond: (client_id > 0)

Or in the output of EXPLAIN ANALYZE for a simple, huge table (with very little work_mem):

EXPLAIN ANALYZE SELECT * FROM aa WHERE a BETWEEN 100000 AND 200000;

Bitmap Heap Scan on aa  (cost=107.68..4818.05 rows=5000 width=4) (actual time=27.629..213.606 rows=100001 loops=1)
  Recheck Cond: ((a >= 100000) AND (a <= 200000))
  Rows Removed by Index Recheck: 758222
  Heap Blocks: exact=693 lossy=3732
  ->  Bitmap Index Scan on aai  (cost=0.00..106.43 rows=5000 width=0) (actual time=27.265..27.265 rows=100001 loops=1)
        Index Cond: ((a >= 100000) AND (a <= 200000))

Does that mean index conditions have to be checked a second time after a bitmap index scan?
What else can we learn from the EXPLAIN output?


texts: How do I determine if a column is defined as a serial data type instead of an integer based off the catalog?

texts:
So I'm currently creating some SQL to read through the postgres (9.1) catalogs to build table definitions. However, I am encountering a problem with SERIAL/BIGSERIAL data types.

Example:

CREATE TABLE cruft.temp ( id BIGSERIAL PRIMARY KEY );
SELECT * FROM information_schema.columns WHERE table_schema='cruft' AND table_name='temp';
"db","cruft","temp","id",1,"nextval('cruft.temp_id_seq'::regclass)","NO","bigint",,,64,2,0,,,,,,,,,,,,,"db","pg_catalog","int8",,,,,"1","NO","NO",,,,,,,"NEVER",,"YES"

It gives me database name (db), schema name (cruft), table name (temp), column name (id), default value (nextval( ... )), and data type (bigint and int8 .. NOT bigserial) ... I realize that I could just check to see if the default value was a sequence - but I don't believe that would be 100% accurate since I could manually create a sequence and create a non serial column where the default value was that sequence.

Does anyone have a suggestion for how I might accomplish this? Anything other than checking the default value for a nextval(*_seq)?

Edited for SQL solution added here in case of TL;DR or new users unfamiliar with the pg_catalog:

with sequences as (
  select oid, relname as sequencename from pg_class where relkind = 'S'
) select
  sch.nspname as schemaname, tab.relname as tablename, col.attname as columnname, col.attnum as columnnumber, seqs.sequencename
from pg_attribute col
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid and deps.deptype = 'n'
left join sequences seqs on deps.refobjid = seqs.oid
where sch.nspname != 'information_schema' and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
  and col.attnum > 0
  and seqs.sequencename is not null -- TO ONLY VIEW SERIAL/BIGSERIAL COLUMNS
order by sch.nspname, tab.relname, col.attnum;


texts:
I've added a new index to account for some performance issues however the queries that I would expect to be using the new index are still are not using it.  In SQL Server you can flush an execution plan a couple of different ways to correct a poor performing query and I'm looking to understand if that is also possible in MySQL.

Can a specific execution plan be removed?
Can a all of the stored execution plans be removed?


texts: MySQL - Key Column Doesn't Exist in Table

texts:
I'm having what I hope is a simple problem - in running a Select statement to join multiple tables together, I'm getting the following error: 'Key column 'id' doesn't exist in table'

Very rarely the query will successfully complete as a preview, but fails each time it's run as a job. I'm easing into SQL for analysis, so I apologize if I'm overlooking something blindingly obvious.

SELECT `itse_blossom_applications_view`.`id` AS 'Blossom CI'
, `itse_blossom_applications_view`.`name` AS 'Blossom Name'
, `itse_blossom_applications_view`.`regulatory_impact` AS 'Compliance Obligation'
, `GCPID`
, `GCP_CI`
, `GCP_App`
, `GCP_Project`
, `GCP_Instance`
, `TAP Name`
, `TAP_CI`
, `Cluster0Environ`
, `Cluster1Environ`
, `OpenStack CI`
, `OpenStack Name`
, `OpenStack UUID`
, `instances.0.display_name`
, `VMAAS Name`
, `VMAAS CI`
, `VMAAS Environ`
, `VMAAS Owner`
FROM `itse_blossom_applications_view`
  LEFT JOIN `gcp_normalized` ON `gcp_normalized`.`GCP_CI` = `itse_blossom_applications_view`.`id`
  LEFT JOIN `tap_cleaned_inventory` ON `tap_cleaned_inventory`.`TAP_CI` = `itse_blossom_applications_view`.`id`
  LEFT JOIN `openstack_renamed_inventory` ON `openstack_renamed_inventory`.`OpenStack CI` = `itse_blossom_applications_view`.`id`
  LEFT JOIN `vmaas_normalized` ON `vmaas_normalized`.`VMAAS CI` = `itse_blossom_applications_view`.`id`
WHERE
  `GCP_CI` IS NOT NULL
  OR `TAP_CI` IS NOT NULL
  OR `OpenStack CI` IS NOT NULL
  OR `VMAAS CI` IS NOT NULL

GROUP BY `TAP Name`
, `GCPID`
, `OpenStack Name`
, `VMAAS Name`

The source data are relatively small tables (2k - 20k rows) which are normalized (Capitalizing/Trimming/Removing Nulls/Empties on certain columns) prior to being ingested into the MYSQL database.


texts: How to log which executed SQL statement is deleting records from SQL Server table?

texts:
I have a SQL Server table where rows randomly get deleted. I want to check what is causing this as I see no issues from the application side.

I did create an after delete trigger and I am able to see which rows are being deleted, but that is still not helping pin-point on why some records are being deleted.

I am wondering if it is possible to log/track which executed SQL statement are deleting the rows from my table.

Please note I am using SQL Server 2016 Express edition.


texts: Why does an alias with a 'having' clause not exist in PostgreSQL?

texts:
I am trying to calculate distance between two coordinates and fetching some information based on a few conditions. A similar query with the 'having' clause was working in MySQL. But why is it not working in PostgreSQL?
This is my query:
    SELECT *, ( 6371 * acos( cos( radians(latitude) ) * cos( radians( latitude1 ) ) *
    cos( radians( longitude1 ) - radians(longitude) ) + sin( radians(latitude) ) *
    sin( radians( latitude1 ) ) ) ) AS distance from table WHERE
    verified=true AND best_for LIKE '%xyz%' AND uuid NOT IN (SELECT uuid::uuid FROM table2 WHERE
    from_date BETWEEN '2020-12-17 06:30'::date AND '2020-12-18 12:30'::date AND
    to_date BETWEEN '2020-12-17 06:30'::date AND '2020-12-18 12:30'::date  AND )
    HAVING distance < 50 ORDER BY distance

Why does distance not exist? And how can I modify this query to make it work in PostgreSQL?


texts: Why does my server have thousands of "HIDDEN ONLINE" schedulers?

texts:
We have a SQL Server 2012 instance with 16 CPU cores.  When looking in sys.dm_os_schedulers we see the expected 16 rows that are "VISIBLE ONLINE", one per core.  The error log shows the following standard entry on startup:

SQL Server detected 2 sockets with 8 cores per socket and 8 logical
processors per socket, 16 total logical processors; using 16 logical
processors based on SQL Server licensing. This is an informational
message; no user action is required.

However sys.dm_os_schedulers also contains approx. 1200 rows that are "HIDDEN ONLINE".

The server is not using Availability Groups or Mirroring, and Resource Governor is not enabled.
The problem is that each hidden scheduler appears to be assigned one worker thread, so our monitoring software is constantly alerting that the server is running out of threads, even though queries are running perfectly happily and there does not appear to be any actual impact to performance.
I'm aware that hidden schedulers are only available to internal system processes, but do you know what would cause so many to be shown?


texts: Adding a column to the view which shows the date in format YYYYMM

texts:
In the following view, I want to add a column (not alter the view) named say "ABC" which shows the date in format YYYYMM (no date, only year and month). How can I do that? I am using db2.

create view VIEW_IRS1 as Select
MBR.PRSN_MBRSH_ID
, Atr.Ssn_Tx
, Names.First_Na
, Names.Middle_Na
, Names.Last_Na
, Reff.Ref_Cd_Desc_Tx Suffix_Na
,'FALSE' AS "Request Title II Monthly Income Verification"

From Hix.T_Enrt En
Inner Join Hix.T_Prsn_Enrt Pe On En.Enrt_Id = Pe.Enrt_Id And En.Enrt_Status_Cd In
(428,439, 430 ) And Pe.Active_In = 'Y' And (Pe.Retro_In Is Null Or Pe.Retro_In = 'N')

Left Outer Join Hix.T_Aptc Ap On En.Enrt_Id = Ap.Enrt_Id

Left Outer Join Hix.T_Prsn_Addl_Attr Atr On Pe.Prsn_Mbrsh_Id = Atr.Prsn_Mbrsh_Id

Left Outer Join
(
    Select nAc.Prsn_mbrsh_Id,  First_Na , Middle_Na , Last_Na, Suffix_Cd From
    Hix.T_Prsn_Name Na, Hix.t_prsn_mbrsh_prsn_name_ac nAc
    where na.prsn_name_id = nAc.prsn_Name_Id
) Names On Pe.prsn_mbrsh_id = Names.prsn_mbrsh_id

Left Outer Join Hix.T_Ref_Data Reff On Names.Suffix_Cd = Reff.Ref_Data_Id

Left Outer Join Hix.T_Ref_Data Re On Atr.GENDER_CD = Reff.Ref_Data_Id

Left Outer Join Hix.T_PRSN_MBRSH MBR on MBR.PRSN_MBRSH_ID=Pe.PRSN_ENRT_ID;


texts: Postgres - window function rank and count

texts:
I have some tables that track athletes time around a track:

competitions, competition_users and competition_user_sessions

I am constructing a query that extracts the current rank and total no of competitors for each supplied 'competition_user', I can get rank ok, but my count (totalUsers) doesn't count all competitors in the competition, it only seems to count up to the supplied user (eg. gives the same as the rank)

SELECT compUserId, rank, totalUsers
    FROM (
        SELECT cu.competition_user_id as compUserId, cu.user_id as userId,
    count(*) OVER w as totalUsers, rank() OVER w as rank
        FROM competition_users cu
        LEFT JOIN current_competition_sessions ccs ON cu.competition_user_id = ccs.competition_user_id
        LEFT JOIN competition_user_sessions cus ON cus.competition_user_session_id = ccs.competition_user_session_id
        WHERE cu.left_competition = false
        AND cu.competition_id in (:compIds)
        WINDOW w AS (PARTITION BY cu.competition_id ORDER BY cus.time_in_seconds ASC)
    ) as sub
WHERE compUserId in (:compUserIds)

My understanding was that the default frame for was the entire window, whereas this seems to be counting from frame start to current row?


texts: Oracle Datapump: modify fully qualified objects when cloning a schema

texts:
I am confronted with a problem for which I have not found an elegant solution:

I need to clone a schema within the same database, so I use datapump's remap_schema import parameter.

The problem is that the PL/SQL Packages contain lots of fully qualified object names (such as select * from FROM_SCHEMA_A.table_name. These qualifiers are cloned. This makes the cloned package invalid.

I could obviously grant all on FROM_SCHEMA_A ... to TO_SCHEMA_A to make the problem go, but this is not what I want. I'd rather want the qualified names to change as well.

The export parameters are

content=metadata_only
directory=DATAPUMP_DIR
dumpfile=clone_schema.dmp
schemas=from_schema_a
exclude=trigger

The import parameters are

directory=DATAPUMP_DIR
dumpfile=clone_schema.dmp
remap_schema=from_schema_a:to_schema_a
transform=OID:n


texts: How to find the total disk writes in MySQL

texts:
I'm trying to find the total number of disk writes in MySQL. I used the following command in the MySQL terminal.

>SHOW GLOBAL STATUS;

I got a list of different attributes status and few which made sense where :

Innodb_data_writes

Innodb_data_written

Innodb_dblwr_writes

So, which one represents the total disk writes in MySQL?


texts: When we are estimating the table size for mySQL table, why do we add primary key twice?

texts:
When we are estimating the table size for mySQL table, why do we add primary key size twice?
I saw this post How to estimate/predict data size and index size of a table in MySQL , and I noticed that the size of primary key is already in the row size.  Why do we add the primary key size again?


texts: Dynamic ORDER BY clause in PL/SQL cursor

texts:
I am not sure if this is simply impossible or if I need to approach this from a different angle.

Simplified, here is what I am trying:

create or replace FUNCTION someFunction(SortCol1 varchar2 default 'column1',
                                        SortDir1 varchar2 default 'ASC')
RETURN STUFF pipelined
AS
CURSOR c1 is
    SELECT
        *
    FROM
        table1
    ORDER BY
        SortCol1 SortDir1;

BEGIN
    FOR c1_rec IN c1
    LOOP
        -- Some logic, etc...
    END LOOP;
END someFunction;

Upon compiling this simply tells me that my SQL is improperly terminated. It does not like the SortCol1 SortDir1


texts:
I hate to ask a stupid question like forgetting the password, but this one is really hard to get back. Tried all the methods I can find online, and none is working. Even deleting the database requires my password, which means if I don't get my password back somehow, or reset, I am basically done with using it for my life.

I am using Mac OS 10.10.2 Here is what i have tried: Tried to stop it first, using a script from the oracle site:

kill `cat /mysql-data-directory/host_name.pid`

...getting no such directory error. I terminated MySQL manually from system preferences, and did the following:

Everyone is saying to use:

mysqld_safe --skip-grant-tables

...but it is not working here. No mysqld_safe command even exists when I punched it into cmd. Then I digged into the directory files, and used:

sudo ./usr/local/mysql/bin/mysqld_safe --skip-grant-tables &

After i did, it still asked for my password! Then I tried to remove sudo:

./usr/local/mysql/bin mysqld_safe --skip-grant-tables

it becomes:

fan$ ./mysqld_safe --skip-grant-tables
./mysqld_safe: line 522: /usr/local/mysql/data/mysqld_safe.pid: Permission denied
awk: i/o error occurred while closing /dev/stdout
 input record number 8, file
 source line number 1
160204 00:21:13 mysqld_safe Logging to '/usr/local/mysql/data/guiyang-Fans-MacBook-Pro.local.err'.
touch: /usr/local/mysql/data/guiyang-Fans-MacBook-Pro.local.err: Permission denied
chmod: /usr/local/mysql/data/guiyang-Fans-MacBook-Pro.local.err: Permission denied
160204 00:21:13 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
./mysqld_safe: line 130: /usr/local/mysql/data/guiyang-Fans-MacBook-Pro.local.err: Permission denied
rm: /tmp/mysql.sock: Permission denied
rm: /usr/local/mysql/data/guiyang-Fans-MacBook-Pro.local.pid: Permission denied
rm: /usr/local/mysql/data/guiyang-Fans-MacBook-Pro.local.pid.shutdown: Permission denied

Please help to save my life with this mess! I really really don't understand why Oracle wants to give me a so sophisticated password in the beginning instead of asking me to set one! I jotted down the password it gave me, which was a combination (&*,<>AaFz0).

I have updated my termination procedures, and it is not working at all. I have used http://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html to try to solve the problem. I am getting the same thing,the big chunk of nonsense above.

Here is what I got:

/usr/local/mysql/data/mysqld_safe.pid: Permission denied
awk: i/o error occurred while closing /dev/stdout
 input record number 1, file
 source line number 1
160204 01:11:50 mysqld_safe Logging to '/usr/local/mysql/data/Fans-MacBook-Pro.local.err'.
touch: /usr/local/mysql/data/Fans-MacBook-Pro.local.err: Permission denied


texts: SQL Server stored procedure running in infinite loop

texts:
I am running this stored procedure that updates a column. It runs in an infinite loop. When I run the internal query separately, it completes just fine. When I cancel the execution of the stored procedure after it has ran for a while, it shows several 5000 row batches affected and asks if I want to commit. Please help.

CREATE PROCEDURE [dbo].[UPDATE_INDATE_PARENT_EMAILS_AND_EDOCS_revised]
    @FieldName VARCHAR(16),
    @StartDate varchar(15),
    @EndDate varchar(15),
    @CustodianIDs varchar(500)
AS
    PRINT ('NUMBER OF CUSTODIANS:')

    DECLARE @XML AS XML
    DECLARE @Delimiter AS CHAR(1) =','

    SET @XML = CAST(('<X>'+REPLACE(@CustodianIDs,@Delimiter ,'</X><X>')+'</X>') AS XML)

    DECLARE @cust_tbl TABLE (custodianid INT)

    INSERT INTO @cust_tbl
        SELECT N.value('.', 'INT') AS custodianid
        FROM @XML.nodes('X') AS T(N)

Declare @cust_id    varchar(20)
Declare @update_sql varchar(max)

declare cust_curs  cursor for
SELECT * FROM @cust_tbl
ORDER BY 1
open cust_curs
fetch next from cust_curs into @cust_id
while @@FETCH_STATUS = 0

BEGIN -- custodian loop

set @update_sql = '

SET NOCOUNT ON;

Select ID into #DOCS from tbldoc (nolock) where custodianid = ' + @CUST_ID + '

SET NOCOUNT OFF;

WHILE (1=1)
BEGIN

    BEGIN TRANSACTION

update TOP(5000) tbldoc set ' + @fieldname + ' = 1
from dbo.tbldoc d (nolock)
join #docs d2 on d2.id = d.id
where (
(d.AttachPID in
(select d3.AttachPID from tblDoc d3 (nolock)
join #docs d4 on d4.id = d3.id
where isnull(d3.Exclude,0) = 0 and d3.AttachPID > 0 and d3.AttachLvl = 0 and
(d3.datesent between ''' + @StartDate + ''' and ''' + @EndDate + ''' or
 d3.datelastmod between ''' + @StartDate + ''' and ''' + @EndDate + ''' or
 d3.datecreated between ''' + @StartDate + ''' and ''' + @EndDate + ''')))
or
(isnull(d.Exclude,0) = 0  and d.AttachPID = 0 and d.AttachLvl = 0 and
 (d.datesent between ''' + @StartDate + ''' and ''' + @EndDate + ''' or
  d.datelastmod between ''' + @StartDate + ''' and ''' + @EndDate + ''' or
  d.datecreated between ''' + @StartDate + ''' and ''' + @EndDate + ''')))

       IF @@ROWCOUNT = 0
            BEGIN
            COMMIT TRANSACTION
            BREAK
        END
    COMMIT TRANSACTION
END
'

PRINT ('UPDATED INDATE FOR CUSTODIANID ' + @CUST_ID)

exec (@update_sql)

IF OBJECT_ID('tempdb..#DOCS', 'U') IS NOT NULL
  DROP TABLE #DOCS;

  FETCH NEXT FROM cust_curs  INTO @cust_id
END

CLOSE cust_curs
DEALLOCATE cust_curs


texts: How to get a 2 row output in a single line in mysql?

texts:
I want to get the single line output from the below query output

select @@hostname,count(*) "RECOMMENDED_PATCHES" from test.PATCHES
union all
select @@hostname,count(*) "PATCH_VERSIONS" from test.PATCH_VERSIONS

output:
ct-test-02 79
ct-test-02 0

expected output:
ct-test-02 79 0


texts: Should a inherited table have a new surrogate key, or just use parent table's key?

texts:
I am modeling some things with an "is a" relationship (aka inheritance), and there were two ways to go about it in regards to the inherited table's primary key and foreign key pointing back to the parent table:

Strategy #1 was to always create a new surrogate key on each inherited table, and then store the reference to the parent table's primary key in a separate column (and have unique constraint on that column to ensure a 1 to 1 mapping).
Strategy #2 was to make the primary key value of the inherited table have the same value as the parent table's primary key.

I think there is a clear winner here (Strategy #2), but I want to make sure there isn't some potential issue I am missing.

Here's the code (targeting SQL Server, but maybe generic) elaborating each strategy:

------Strategy #1: always create a surrogate key for a child table (separate column holds parent table's primary key)----------
drop table if exists TruckAsset
drop table if exists VehicleAsset
drop table if exists Asset

create table Asset
(
    AssetId int not null,
    primary key (AssetId)
)

create table VehicleAsset
(
    VehicleAssetId int not null, /*surrogate key*/
    AssetId int not null,
    VehicleRegistrationExpirationDate date not null, /*specialization of Asset*/

    primary key (VehicleAssetId),
    constraint UQ_AssetId unique (AssetId),
    foreign key (AssetId) references Asset(AssetId)
)

create table TruckAsset
(
    TruckAssetId int not null, /*surrogate key*/
    VehicleAssetId int not null,
    CargoCapacityCubicMeters float not null, /*specialization of VehicleAsset*/

    primary key (TruckAssetId),
    constraint UQ_VehicleAssetId unique (VehicleAssetId),
    foreign key (VehicleAssetId) references VehicleAsset(VehicleAssetId)
)

INSERT INTO Asset(AssetId)VALUES(1234)
INSERT INTO VehicleAsset(VehicleAssetId, AssetId, VehicleRegistrationExpirationDate) VALUES (5678, 1234, '2020-01-01')
INSERT INTO TruckAsset(TruckAssetId, VehicleAssetId, CargoCapacityCubicMeters) VALUES (9999, 5678, 8.80977)

GO

versus this

------Strategy #2: primary key references parent table's primary key directly----------
drop table if exists TruckAsset
drop table if exists VehicleAsset
drop table if exists Asset

create table Asset
(
    AssetId int not null,
    primary key (AssetId)
)

create table VehicleAsset
(
    VehicleAssetId int not null,
    VehicleRegistrationExpirationDate date not null, /*specialization of Asset*/

    primary key (VehicleAssetId),
    foreign key (VehicleAssetId) references Asset(AssetId)
)

create table TruckAsset
(
    TruckAssetId int not null,
    CargoCapacityCubicMeters float not null, /*specialization of VehicleAsset*/

    primary key (TruckAssetId),
    foreign key (TruckAssetId) references VehicleAsset(VehicleAssetId)
)

INSERT INTO Asset(AssetId)VALUES(1234)
INSERT INTO VehicleAsset(VehicleAssetId, VehicleRegistrationExpirationDate) VALUES (1234, '2020-01-01')
INSERT INTO TruckAsset(TruckAssetId, CargoCapacityCubicMeters) VALUES (1234, 8.80977)
----------------------------------------


texts: Efficient query plan for selecting all data duplicated on several columns

texts:
The flavour in question is PostgreSQL, but it is a generic enough query pattern that your techniques for optimising against other RDBMs should help as well.

The purpose of the query is to show all records from a table that are duplicated along several columns (user selectable).

  SELECT usr_id,
         team_name, team_streetno, team_city,
         usr_firstname, usr_lastname, usr_jobtitle, usr_department, usr_email
    FROM team c
    JOIN usr p on p.team_id=c.team_id
    JOIN
    (
        select match_firstname, match_lastname, match_jobtitle, match_department, match_email ,
               match_team_name, match_team_streetno, match_team_city
          FROM usr
          JOIN team on usr.team_id=team.team_id
      GROUP BY match_firstname, match_lastname, match_jobtitle, match_department, match_email,
               match_team_name, match_team_streetno, match_team_city
        HAVING count(usr_id)>1
    ) AS sub
      ON sub.match_team_name = c.match_team_name
     AND sub.match_team_streetno = c.match_team_streetno
     AND sub.match_team_city = c.match_team_city
     AND sub.match_firstname = p.match_firstname
     AND sub.match_lastname = p.match_lastname
     AND sub.match_jobtitle = p.match_jobtitle
     AND sub.match_department = p.match_department
     AND sub.match_email = p.match_email
ORDER BY usr_firstname, usr_lastname, usr_jobtitle, usr_department, usr_email,
         team_name, team_streetno, team_city

The EXPLAIN output looks like this:

I tried this variation to make arrays out of the duplicated usr_id's and JOIN back to the usr table using = ANY; that increased the duration by about 15%.

 SELECT u.usr_id
     , t.team_name, t.team_streetno, t.team_city
     , u.usr_firstname, u.usr_lastname, u.usr_jobtitle, u.usr_department, u.usr_email
  FROM (
      SELECT array_agg(u1.usr_id) dups
        FROM usr u1
        JOIN team t1 ON u1.team_id = t1.team_id
    GROUP BY match_firstname, match_lastname, match_jobtitle, match_department, match_email
           , match_team_name, match_team_streetno, match_team_city
      HAVING count(usr_id)>1
       ) AS dup
  JOIN usr u on u.usr_id = ANY (dup.dups)
  JOIN team t on t.team_id = u.team_id
ORDER BY u.usr_firstname, u.usr_lastname, u.usr_jobtitle, u.usr_department, u.usr_email
       , t.team_name, t.team_streetno, t.team_city

Look forward to your suggestions.

Consolidating comments

Tried WITH TMP AS (... ,count(*) over (partition by...) c FROM ..) WHERE c>1, which fared worse than the arrag_agg/ANY approach. Seems like windowing functions in PostgreSQL is not that great, or it needs to be rewritten in another form.

For context, consider census data. If someone else filled it for you, he may fill in Tom one year, Thomas or Tomas the next. The "match" columns are derived from the base columns.

EXISTS has been tried, but didn't perform as well as the code shown above.


texts: How to select max value faster from large size table

texts:
I have query

SELECT max(ParameterID) FROM modelparam_ems

Table Name: modelparam_ems

No Of Rows: 9000

It takes time to retrieve max value of parameterId, ParameterId in Unique value.

Output Time :

(Below row copied from Mysql Workbench).

13:51:15    SELECT max(ParameterID) FROM modelparam_ems 1 row(s) returned   0.109 sec / 0.000 sec


texts: database design for multiple and all pepople

texts:
Assume I writing an email applicaiton.
Now I want to send some messages to specified person, some messages to multiple person, some messages to specified group and some to all members.
I am wondering in the best practice I have to treat every mail like a one instance mail, I mean I if I am sending mail to all I have to assign one record for every person or I can define a type field and from the code I must handle with codes.


texts:
Is there any way to have a Postgres LIKE query on a ARRAY field?

Currently I want something like that:

SELECT * FROM list WHERE lower(array_field) LIKE '1234%'

Currently lower is not needed that much. However it should find ONE matching field inside the ARRAY. Is that even possible?

Currently I use a materialized view to generate the "list" table with a JOIN and a ARRAY_AGG(), since I JOIN a table where more values could be on the right table. Which would duplicate fields on the left table, which is not what I want.

Edit this is how I create the view (really sluggish and ugly):

CREATE MATERIALIZED VIEW article_list_new AS
SELECT a.id,
       a.oa_nr,
       a.date_deleted,
       a.lock,
       a.sds_nr,
       a.kd_art_nr,
       a.kd_art_index,
       a.kd_art_extend,
       a.surface,
       a.execution,
       a.surface_area,
       a.cu_thickness,
       a.endintensity,
       a.drilling,
       array_agg(o.id::text) AS offer_list
FROM article_list a LEFT JOIN task_offer o ON o.article = a.oa_nr
GROUP BY .....;

I also need to return the IDs of the task_offer table.


texts: Slow Parallel SQL Server query, almost instant in serial

texts:
I have a SQL Server query as follows (obfuscated):
UPDATE  [TABLE1]
SET     [COLUMN1] = CAST('N' AS CHAR(1))
FROM    [TABLE1]
WHERE   (COLUMN1 = '2' AND COLUMN2 IN('VAL1', 'VAL2', 'VAL3')) OR
        (COLUMN1 <> 'N' AND (
                                    SELECT  COUNT(*)
                                    FROM    TABLE2 wle
                                            JOIN TABLE3 wl
                                                    ON wl.COLUMN3 = wle.COLUMN3
                                    WHERE   TABLE1.COLUMN4 = wle.COLUMN4 AND
                                            (wl.COLUMN5 = '1' OR wl.COLUMN6 = '1') AND
                                            wle.COLUMN7 = (
                                                            SELECT  MIN(alias.COLUMN7)
                                                            FROM    TABLE2 AS alias
                                                            WHERE   TABLE1.COLUMN4 = alias.COLUMN4
                                                            )
                                ) > 0

        )

We have just upgraded our (test) server to SQL Server 2016 SP2 from SQL Server 2014 SP3.
The performance of the query above appears to have fallen off a cliff as a result of this.
When the server was on SQL Server 2014, the database compatibility level was 120. Now it is on SQL Server 2016, the compatibility level for the database is still 120, however I have tried the query in 110,120 and 130, all with the same result.
When I run sp_whoisactive, I can see the wait_info is (48847425ms)CXCONSUMER suggesting that the query has been waiting for CXCONSUMER for the last 48847425ms (814 minutes) The query has currently been running for 13:34:07.587.
The wait info suggests that the query has been waiting on CXCONSUMER for the majority, if not all of its execution time.
This to me suggests some issue with paralellism so I ran the query with the hint OPTION (MAXDOP 1) and it finished in an acceptable time (around 30 seconds)
The plan shape is as follows.

The plan shape for the MAXDOP 1 query is as follows:

(the same plan with no parallelism operators)
When I run the parallel query with the live execution plan enabled, the operator highlighted in green (Clustered Index scan on TABLE2) shows 100% but it's execution time continues clocking up.
The operator highlighted in red (Clustered Index Scan on TABLE1) gets "stuck" on 4 rows of 180,215.
What could be causing this problem? In my head am I thinking it is parallelism skew (uneven workload) but given that the serial query finishes in less than a minute I would have thought even if the query went parallel but only used one thread it would still complete in a time close to the serial query.
Also, given that the live plan appears to show the red clustered index scan not progressing at all I am unsure what is happening.
Processor and I/O affinity are set to to Automatic
I have found this article that describes similar behavior though the purpose of the article appears to be showing that CXCONSUMER is not necessarily a benign wait and doesn't say how / if it can be fixed.
In terms of fixing it, I know the code can be re-written in a more efficient way (both the COUNT and MIN subqueries could be selected into variables) but unfortunately changing the query isn't an option I have.
I could force a MAXDOP hint but again, this means changing the code, perhaps I could force it with a plan guide, though such practice of forcing the opimizer usually isn't advisable?
What causes this problem?
Why is the query running slow in SQL Server 2016?
Why is the Clustered Index Scan on TABLE1 getting "stuck" on 4 rows of 180,215?
Is there a way to fix this without changing the code?


texts: How should we be alerted of failures in the SQL replication distribution agent job?

texts:
When setting up SQL replication, the default retry count for the replication agent job step is 2147483647, retrying every minute. This means that if replication fails, the failure notification for the SQL Agent job won't be sent for 4085 years.

The issue is discussed in this MSDN blog, along with the suggestion that the retry count be changed from 2147483647 to 60 - so we would only wait one hour before being alerted to the failure:
http://blogs.msdn.com/b/repltalk/archive/2010/08/25/sql-replication-agent-will-retry-for-4085-years.aspx

However it seems to me that the Microsoft SQL team must have selected the default of 2147483647 for a reason, and changing it to a much lower value must therefore have some negative impact.

So the question is - what is the best way to be alerted when a subscription breaks?  (In our scenario, we are only the subscriber - and another company is the publisher)


texts: Log full and an Active_transaction on database

texts:
I have limited access to this server eg I cannot SELECT * FROM sys.dm_tran_session_transactions for example

I, perhaps foolishly, ran this query to insert some test data...

begin transaction

update aqt_tblquote
set intVisits = ABS(CHECKSUM(NewId())) % 10

select top 100 intVisits FROM aqt_tblquote

rollback transaction

This sat about for a while and then informed me the log was full. From my experience this is not good news!

I was hoping that given it is in a ROLLBACK that it would just return the table to its previous state and clear the log.

However if I select from sys.databases I get log_reuse_wait_desc = 'ACTIVE_TRANSACTION'

I've closed Management Studio but that didn't do it. How can I kill it? Or do I need to contact the virtually unobtainable DBAs? (I work in a large organisation where there are many layers of bureaucracy to cut through).


texts: Order by of records of a table in SQL Server

texts:
I have the following tables:

Customer (ID, Name, CountryID)

Country (ID, Name, Code)

From the web application, user selects the number of Customers and the Countries then based on these parameters the system shows the result.

For example, user selects the number of customers as 2 and countries as US and UK then the system must show the result as follow:

CustomerID    CustomerName    Country
--------------------------------------
1             A               US
2             B               US
3             C               UK
4             D               UK
.
.
.

How can we achieve the above result?


texts: Powershell Script Error Connecting To SQL Server

texts:
Problem
I have a PowerShell script that pulls user information (Domain and/or SQL users, their groups and other information) and spits it out to an Excel/csv file. This script worked in my virtual lab environment; however, when executed from client issued laptop or when I remote directly into server I get the following error:

Error connecting to server  [Server Name]

Notes
I have also gone into SSMS and opened PowerShell directly from within and get same error.
I mentioned my virtual lab environment. That is a virtual server cluster I have on my personal laptop. When I execute the script on my virtual environment it executes fine and spits out the Excel file. This environment is a 2 node cluster using Windows Server 2012. My SQL Server is the 2012 Developer version. Powershell version 4.0.
Environments
Client environment varies. On desktop using Windows 7, Powershell 5.0 and SQL Server 2014. The particular server I am connecting to is Windows 2008 R2 and SQL Server 2008 R2 and Powershell 4.0
Script
    clear
Write-Host -ForegroundColor Green "Starting"

$MaximumErrorCount = 15000
#import SQL Server module
Import-Module SQLPS -DisableNameChecking

$today=(get-date -f yyyyMMdd-HHmmss)

#a list of instances you want to send the query to
$instances = Get-Content "C:\Users\Jimmy.Johns\Desktop\Instance_list\instance.txt"
$query = 'TSQL. Left out full code for space/formatting'
$databasename = "master"
$instances |
ForEach-Object {
try {
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $_
     Invoke-Sqlcmd -ServerInstance $_ -Database $databasename -Query $query -QueryTimeout 15   | Export-Csv -Append -Path C:\Users\Jimmy.John\Desktop\Output\ListPermisions-$today.csv
} Catch {
    $_ | Out-Null
    "Error connecting to server  $server" | Export-Csv -Append -Path C:\Users\Jimmy.John\Desktop\Output\ListPermisions-$today--Errors.csv
    Write-host "Error connecting to server  $server"
 }
}

Write-Host -ForegroundColor Green "Done"

I have read http://www.sqlshack.com/connecting-powershell-to-sql-server/ and used connection string and still will not connect.
Update
After changing $_|out-null with $error[0].exception and running from the client desktop I got the following error:

Could not file or assembly 'Microsoft.SQLServer.BatchParser, Version= 12.0.0.0, Culture=Neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system could not load the file specified.

I also got this error when I ran the edited script inside the server.

Import-Module : The specified module 'ActiveDirectory' was not loaded because no valid module file was found in any module directory.