texts: SQL Server : Memory pressure,Spiked user connections and CPU usage

texts:
I have the below sql server environment.

SQL Server 2016 standard edition.
128 GB  RAM
96 GB alllocated to SQL Server
8 tempdb's with 1 GB each.
Have a major database with 2.5 TB size.
Reporting also done from the same sql instance.Reporting is minimum

There is memory pressure as i see that my plan cache is getting created periodically.Also i have noticed the flag which shows memory pressure when i queries ring buffer.
Also i see tempdb memory spill,huge memory grant issues when i see the execution plan of slow query at times.
Most of the read operations coming to the database has complex queries and to help with performance,the read operations are run in parallel with the below settings.

MAX DOP : 8
Cost Threshold for Parallelism = 20
40 Logical CPUs

I have been trying to improve performance and i was able to achieve it to some extend.But some days server becomes slow and the transactions(read mostly) take like 30 sec or even 1 minute to finish.Since most of the read transactions are running in parallel,i see the CXCONSUMER wait time and for the transactions and also this is making other queries also to wait for the resources.

I am confused where i should start to get things under control.
Do i need more memory to support to 20 times bigger data here.?

Additional info:
I use the monitoring tool grafana from TICK stack,to see the CPU usage and other performance counters.Sometimes,i see the COU usage going really high and along with it number of user connections going really high.The fact is those are not real users(when i checked with the team) and some other process is causing this.
I am not sure,if some locking mechanism can cause this kind of issues were user connections spike up,also CPU usage.


texts:
I've got a question on how to "prettify" data entry and removal for a many to many relationship.
Imagine if you will a DB with 3 tables. Tables called Users, Roles, and RoleAssignment.
-- `Users`
CREATE TABLE `Users`
(
 `userID`   int NOT NULL AUTO_INCREMENT ,
 `userName` varchar(40) NOT NULL ,
 `realName` varchar(40) NOT NULL ,

PRIMARY KEY (`userID`),
UNIQUE KEY `username` (`username`)
) AUTO_INCREMENT=1;

-- `Roles`
CREATE TABLE `Roles`
(
 `roleID`       int AUTO_INCREMENT NOT NULL ,
 `roleName`     VARCHAR(40) NOT NULL ,

PRIMARY KEY (`roleID`)
);

-- `RoleAssignment`

CREATE TABLE `RoleAssignment`
(
 roleAssignID int NOT NULL AUTO_INCREMENT ,
 roleID       int NOT NULL ,
 userID       int NOT NULL ,

PRIMARY KEY (roleAssignID),
FOREIGN KEY (userID) REFERENCES Users(userID),
FOREIGN KEY (roleID) REFERENCES Roles(roleID)
) AUTO_INCREMENT=1;

This is a database where RoleAssignment is essentially a child table only meant to connect two Parent tables together. I made them this way because the user<->role relationship is many to many.
I found a way of getting this to output just relationships between users and their roles... Both queries output the same data but query 2 is better organized.
SELECT
    Users.userName,
    Users.realName,
    Roles.roleName
FROM Users
JOIN RoleAssignment ON Users.userID = RoleAssignment.userID
JOIN Roles ON Roles.roleID = RoleAssignment.roleID;

SELECT
Users.userName,
Users.realName,
GROUP_CONCAT(Roles.roleName)
FROM Users
JOIN RoleAssignment ON Users.userID = RoleAssignment.userID
JOIN Roles ON Roles.roleID = RoleAssignment.roleID
GROUP BY Users.userID;

Is there an easy way to add an entry to the RoleAssignment table by name instead of ID in a single query? i.e. "Add user jack to role moderator"? Or would I need to write a script to find the ID of the user, then the ID of the role, and finally add an entry to RoleAssignment?
Any help or guidance would be appreciated!


texts: Benchmarking MariaDB/InnoDB using TATP - Writers slow down readers?

texts:
I'm benchmarking MariaDB 10.4 with TATP. The InnoDB config file has the following parameters set:

[mysqld]
innodb_data_home_dir=/usr/local/mysql/data
innodb_undo_directory=/usr/local/mysql/data
innodb_undo_log_truncate=1
innodb_undo_tablespaces=3
innodb_flush_method=O_DIRECT
innodb_page_size=65536

When I run just the read-mix of TATP (i.e, the read queries alone), I get a result as follows:

I 2019-10-06 16:06:06 STATISTICS 90% response time (us), by transaction
I 2019-10-06 16:06:06 STATISTICS GET_SUBSCRIBER_DATA: 744
I 2019-10-06 16:06:06 STATISTICS GET_NEW_DESTINATION: 1323
I 2019-10-06 16:06:06 STATISTICS GET_ACCESS_DATA: 738
I 2019-10-06 16:06:06 CONTROL MQTh for test run is 6772

Focusing on the first query, GET_SUBSCRIBER_DATA, it has a 90% response time of 744us.

However, when I run the R90/W10 mix (i.e, 90% read requests, 10% write requests), the result is as follows:

I 2019-10-07 01:23:27 STATISTICS 90% response time (us), by transaction
I 2019-10-07 01:23:27 STATISTICS GET_SUBSCRIBER_DATA: 3771
I 2019-10-07 01:23:27 STATISTICS GET_NEW_DESTINATION: 4712
I 2019-10-07 01:23:27 STATISTICS GET_ACCESS_DATA: 3687
I 2019-10-07 01:23:27 STATISTICS UPDATE_SUBSCRIBER_DATA: 18801
I 2019-10-07 01:23:27 STATISTICS UPDATE_LOCATION: 18831
I 2019-10-07 01:23:27 STATISTICS INSERT_CALL_FORWARDING: 20017
I 2019-10-07 01:23:27 STATISTICS DELETE_CALL_FORWARDING: 19027
I 2019-10-07 01:23:27 CONTROL MQTh for test run is 1464

The response time of GET_SUBSCRIBER_DATA is much slower.

Queries are generated probabilistically, and keys are chosen uniformly at random. However, what I don't understand is, how are the writers slowing down the readers by that much, especially when MariaDB/InnoDB has row-level locking, to avoid precisely this issue of writers blocking readers. Are there any configuration parameters that need to be tweaked?


texts: Get average of multiple rows with criteria and return those rows

texts:
I'm attempting to get any items that are the same type, but are ranked one higher that have an average equal or greater than the item ranked below.

I have a table and query like http://www.sqlfiddle.com/#!9/e14ca/4/0 although instead of returning 'd' and 'e' separately, I would like them first to be averaged (50+65)/2 = 57.5 and then check if they are greater than or equal to 'a' * 10 (50). If that is the case, return 'a', and 'd' & 'e'.

My expected output per the example is

-------------------------------------------------------------
| title | type | rank | price | title | type | rank | price |
|-------|------|------|-------|-------|------|------|-------|
| a     | 2    | 2    | 5     | d     | 2    | 3    | 50    |
|       |      |      |       |-------|------|------|-------|
|       |      |      |       | e     | 2    | 3    | 65    |
-------------------------------------------------------------

Thanks.


texts: How can I connect Microsoft SQL Server to MySQL

texts:
I have a SQL Server running on a local server that is connected to a POS (Point of Sale) system.
I want to create an online application to enable online sales of the products in the POS system.  I'm using MySQL for this application's database.  I would like to connect the MySQL instance to the SQL Server instance to avoid the need to enter duplicate data into each system.  Ideally, changes made to the SQL Server database should be automatically reflected in the MySQL database.
Is there a way to do that with a Linked Server in SQL Server?  Or should I use some other method?  There are around 10,000 products in the SQL Server instance, and we sell around 5,000 each day.


texts: xp_cmdshell - Enablement Consequences - Clarifications

texts:
Recently we have disabled a module which is dependent on xp_cmdshell. Though we are not directly using the xp_cmdshell (instead we are using the proxy), we would now like to disable the xp_cmdshell itself.

Can anyone explain to me with a simple scenario based on the the statement below, why some people are for xp_cmdshell and most of the people are against xp_cmdshell?

  "xp_cmdshell" is an extended stored procedure provided by Microsoft and stored in the master database. This procedure allows you to issue operating system commands directly to the Windows command shell via T-SQL code. It is dangerous as it can be used by trojans and worms to gain access to the system.

Reference: Restrict Microsoft SQL Server XP_CMDSHELL Procedure (Trend Micro)

Though only SQL Server admin can use it, why people are still against ?
(Not in a negative sense) Is it really possible for SQL Injection using xp_cmdshell? Any simple hint / example of a scenario of SQL Injection via xp_cmdshell, I just want to understand the background.
What are the dangerous Windows OS commands one can think of?

Apologize, though there are many articles and write-ups on xp_cmdshell, I am bit confused on what are the dangerous stuff it could do.


texts: Delayed availability of historical data when using CDC

texts:
I am using the SQL Change Data Capture feature to retrieve historical data from my tables.

During testing, I noticed that when I insert/update a row and query the change data right afterwards (see my earlier question on how I do that), the result will turn up empty.

I played around with it and noticed that if I wait 10 seconds between the data manipulation and the history query, the history will be up to date.

I am aware that CDC functionality is based on the transaction logs somehow and that this probably is the reason why there is a delay between data changes and the cdc history table being updated.

Now my question:

Is this delay somewhere explained in the CDC documentation? I could not find any mention of it.
Is there a way to manually make sure that the history table is up to date before I query it? (somehow triggering CDC to check the logs?)
How does CDC work together with transactions? Will I for example be able to query historical data that I have overwritten in the same transaction? Or does the transaction first have to commit for the cdc data to become availalbe?


texts: disable constraints before using pg_restore.exe

texts:
When I try to execute pg_restore.exe of a dump file from a database, it throws dozens of errors, all the same:

ERROR: insert or update on table "someTable" violates foreign key constraint "aConstraintName"

This is obviously due to the fact that I've emptied the data base before restoring it from the dump file (this file comes from a production database)... then of course no foreign keys constraint can be ok if one referenced table is empty...

Is there a way to disable the constraints and all foreign keys, for all tables, before I call pg_restore.exe, and afterwards, re-enable the constraints and foreign keys.

In SO I found something interesting: deferring constraint checking to commit time.
But I don't think I can call pg_restore.exe from inside psql.exe after deferring the constraints.

There is also this post, dating back 10 years ago, suggesting to drop then re-add the constraints. Or to change the value of pg_class reltriggers to 0 and that would also be possible for constraints... but I'm afraid it is more hacking than good practice...

What do you advise, what is the best practice in this case ? Is using pg_dump.exe with the -clean flag creates a dump that bypasses the constraints checking when restoring the database ?


texts:
I am collecting IO_STALLS from sys.dm_io_virtual_file_stats every 5minutes and then doing a delta to see which files are being affected most by IO.

In one 5min period I get a delta of 5826331 ms which is 97minutes.

I am a little confused by this, is this saying that an operation started 97mins ago only just finished at that point and hence recorded that wait time?

Thanks

Added code as requested:

/*

USE [SysDBA]
GO
*/
/****** Object:  Table [dbo].[DISKIOPS]    Script Date: 04/07/2013 11:40:15 ******/
/*
DROP TABLE [dbo].[DISKIOPS]
GO
*/
--Create the table
/****** Object:  Table [dbo].[DISKIOPS]    Script Date: 04/07/2013 11:40:15 ******/
/*
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[DISKIOPS](
    [IO_STALL] [bigint] NULL,
    [IO_STALL_READ_MS] [bigint] NULL,
    [IO_STALL_WRITE_MS] [bigint] NULL,
    [NUM_OF_READS] [bigint] NULL,
    [NUM_OF_WRITES] [bigint] NULL,
    [SIZE_ON_DISK_MB] [bigint] NULL,
    [DBNAME] [varchar](max) NULL,
    [NAME] [varchar](max) NULL,
    [FILE_ID] [int] NULL,
    [DB_FILE_TYPE] [varchar](max) NULL,
    [DISK] [varchar](max) NULL,
    [FILE_LOCATION] [varchar](max) NULL,
    [TIMESTAMP] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

*/

--Capture IO information from DMV and query to find deltas over time.
/*
USE [SysDBA]
GO

INSERT INTO [dbo].[DISKIOPS]
           ([IO_STALL]
           ,[IO_STALL_READ_MS]
           ,[IO_STALL_WRITE_MS]
           ,[NUM_OF_READS]
           ,[NUM_OF_WRITES]
           ,[SIZE_ON_DISK_MB]
           ,[DBNAME]
           ,[NAME]
           ,[FILE_ID]
           ,[DB_FILE_TYPE]
           ,[DISK]
           ,[FILE_LOCATION]
           ,[TIMESTAMP])
SELECT a.io_stall, a.io_stall_read_ms, a.io_stall_write_ms, a.num_of_reads,
a.num_of_writes,
--a.sample_ms, a.num_of_bytes_read, a.num_of_bytes_written,
( ( a.size_on_disk_bytes / 1024 ) / 1024.0 ) AS size_on_disk_mb,
db_name(a.database_id) AS dbname,
b.name, a.file_id,
db_file_type = CASE
                   WHEN a.file_id = 2 THEN 'Log'
                   ELSE 'Data'
                   END,
UPPER(SUBSTRING(b.physical_name, 1, 2)) AS disk_location,
b.physical_name AS File_location,
GETDATE() AS Timestamp
FROM sys.dm_io_virtual_file_stats (NULL, NULL) a
JOIN sys.master_files b ON a.file_id = b.file_id
AND a.database_id = b.database_id
GO
*/
DECLARE @File_Name VARCHAR(8000),
        @Disk VARCHAR(5)
SET @File_Name = 'DBTEST'
SET @Disk = 'I:'
--Code to pull out deltas between collected IO stats.
;WITH IOPS   ([IO_STALL]
           ,[IO_STALL_READ_MS]
           ,[IO_STALL_WRITE_MS]
           ,[NUM_OF_READS]
           ,[NUM_OF_WRITES]
           ,[SIZE_ON_DISK_MB]
           ,[DBNAME]
           ,[NAME]
           ,[FILE_ID]
           ,[DB_FILE_TYPE]
           ,[DISK]
           ,[FILE_LOCATION]
           ,[TIMESTAMP]
           ,[ROW])
AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY FILE_LOCATION ORDER BY TIMESTAMP DESC) AS [ROW]
FROM dbo.DISKIOPS
)

SELECT MAX([IO2].[IO_STALL] - [IO1].[IO_STALL])
FROM IOPS IO1 JOIN IOPS IO2 ON IO1.ROW = (IO2.ROW+1)
WHERE IO1.NAME = IO2.NAME
AND IO1.Disk = @Disk


texts: What's the best way to diagram this "relation" looking for performance and|or disk space savings

texts:
I am working in a DB diagram and I have a table called cm_identifier_type. Each record of this table can have or not a downloadable file (saved a file path). I have identified two possibles ways to achieve this.

Place a column download_file as VARCHAR(250) and set to NULL by
default (this because not all of the record will have a download
related)

Create a relation between cm_identifier_type and a new table cm_download_file:

Now, regarding performance, disk space savings, query savings and so on, how would you do that? What is your recommendation on this edge case?

Note: for the moment I am using MariaDB 10.1.x but this will be in a MySQL instance, probably 5.x or so I am not sure at all since I didn't got those details yet


texts: Slow Postgres updates if OTHER columns are indexed?

texts:
Certain updates take far too long on large Postgres tables. Given these conditions:

only one column is being updated, and it is non-indexed
the column already has data in it for every row due to a prior update
the data is not changing size (e.g., rewriting a boolean value)
there is no other column in this or any other table that depends on the value of the column being updates
there are no other queries being performed on the database (this is a personal research database on a workstation, not an enterprise database)
there are indexes on other columns
spinning drive (not SSD) with Bitlocker and fast PC with Windows 8.1 x64
the table has 10 million rows and 60 columns

...you'd think that the update would take a reasonable amount of time relative to expectations of spinning media with Bitlocker. We're not creating more data, so existing data shouldn't need to be moved around on the HDD, it just needs to be overwritten. The other indexes shouldn't need changes. Etc. Instead, after 20 hours of constant HDD grinding, I get tired of waiting and stop the query. If I drop all indexes on other columns and re-run the query, it takes only about 30 minutes.

Why do indexes on columns irrelevant to this query balloon the update time?


texts: How to get 5min date interval from stock data


texts: what is the best configuration for innodb_flush_log_at_trx_commit?

texts:
There are 3 different configuration is available for innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit = 0

innodb_flush_log_at_trx_commit = 1

innodb_flush_log_at_trx_commit = 2

Which is the best configuration for innodb_flush_log_at_trx_commit?

what is the difference between this three configuration?


texts: In a school database, do I need to have repeating data when a student can also be a teacher?

texts:
I've seen lots of ERDs of schools, teachers, and all the junction/associative tables.  They always have three tables:

Teachers
Students
Associative table betweeen them, maybe subjects of something like
that.

it is assumed that a teacher cannot be a student and vice-versa.  But what if a student can also be a teacher (a non-grade school, obviously)?  If I keep the traditional view, then I end up with repeating data between teachers and students, such as the name.  What am I missing?

Would it be bad design to have:

Person table
----
person_unique_id
lastname
firstname
...

Teacher table
----
teacher_unique_id
person_unique_id
subject_unique_id #if I want to know what this person can teach.

Subject table
----
subject_unique_id

I would still have to have an associative table for all the many-to-many relationships like:

schedule
----
schedule_unique_id
person_unique_id
teacher_unique_id
subject_unique_id
time
date
room
....

Then I could do something like:

SELECT * FROM Person p, Teacher T, Subject s
WHERE p.person_unique_id = t.person_unique_id
AND t.subject_unique_id = s.subject_unique_id
AND p._person_unique_id = 12;


texts: DACPAC Deployment Rebuilding All Indexes

texts: I'm in process of deploying a DACPAC provided by our ISV.  The DACPAC is rebuilding every index in the database.  We're testing in a Lab now, however, this will create a significant impact to our maintenance window when we move into production.  Is anybody aware how I can skip this index rebuild process in the DACPAC deployment?


texts:
I want to report a script which contains a Select from (Select), when I just run the second select I have no problem but when I put the whole script it loads and nothing happens.

Here's my code :

SELECT top 100
   *,
   case
      when Frequency = '1' and DATEDIFF(HOUR, T.MeasurementDate, T.nextMeasurement) > 1
        Then 'Missing Hour(s)'
      when Frequency = '1' and T.MeasurementDate = T.maxMeasurement and DATEDIFF(HOUR, T.maxMeasurement, CONVERT(DATE, @EndDate)) > 1
        Then 'Missing Max Hour(s)'
      when Frequency = '2' and DATEDIFF(DAY, T.MeasurementDate, T.nextMeasurement) > 1
        Then 'Missing Day(s)'
      when Frequency = '2' and T.MeasurementDate = T.maxMeasurement and DATEDIFF(DAY, T.maxMeasurement, CONVERT(DATE, @EndDate)) > 1
        Then 'Missing Max Day(s)'
      ELSE 'OK'
   END AS [Verification]
into #tt
FROM
    (SELECT TOP 100
         Flow.Id as idFlow,
         Flow.ComponentId,
         Flow.Frequency,
         pip.Name as PipelineName,
         LEAD(Flow.MeasurementDate) OVER (Partition by ComponentId, NominationCycle, LocationType, Frequency, FlowTypeId, UnitofMeasurement ORDER BY  MeasurementDate) nextMeasurement,
         MAX(Flow.MeasurementDate) OVER (PARTITION BY ComponentId, NominationCycle, LocationType, Frequency, FlowTypeId, UnitofMeasurement) maxMeasurement
     FROM
         BentekDatabase.dbo.Flow Flow
     inner join
         BentekDatabase.dbo.Pipeline pip on Flow.PipelineId = pip.id
     Where
         ScheduledVolume IS NOT NULL
         and MeasurementDate BETWEEN @StartDate AND @EndDate) AS T

select count (*)
from #tt
where Verification != 'OK'

PS : I want to display the result of the last query

select count (*) from #tt where Verification != 'OK'

in a new page result, which will contain the result of all my other queries too.

Any Idea ?


texts: How to avoid empty rows in SSIS Excel Destination?

texts:
Does anyone have a way to avoid empty rows when using SSIS to export to Excel. Here's a simple example of one data flow task:

OLE DB Source:

Data Conversion (to handle the annoying UNICODE / NON-UNICODE deal):

The end result is either of the two below depending on value of "FirstRowHasColumnName" in the Excel Connection Manager. Note, the blank rows.


texts: Problem setting MSDTC on client computer

texts:
I created an application that is making use of MSDTC (to open a transaction on multiple databases within the same MS SQL server), our local environment was setup successfully and both DTCPing, DTCTester and the application work. However when it came to deployment I encountered issues when setting up the client computer to make use of the server's coordinator.

On Client site I have tried the following:

DTC Ping passed successfully
Setting Coordinator to the server one results into the following error "The specified remote host machine does not exist"
I am able to ping the server using host name

When I tried running DTC tester, it output the following:

Executed: dtctester.exe
DSN:  accdim
User Name: sa
Password: <hidden>
tablename= #dtc16397
Creating Temp Table for Testing: #dtc16397
Warning: No Columns in Result Set From Executing: 'create table #dtc16397 (ival int)'
Initializing DTC
DtcGetTransactionManager failed: 8004d01b
DtcGetTransactionManager Call Error: Indicates unable to connect to the transaction manager, or the transaction manager is unavailable.


texts: How to store lots of small text in a SQL Server database?

texts:
I have a table with a varchar(max) column that stores text ~4-10KB in size. Data is just being added - never modified, it is a log. My problem is that a table grows too large in size - I would like to keep about 1000000 rows in this table.

I am limited to SQL Server Express and hit the limit on database size. Compression is not available in SQL Server Express and I afraid that Filestream may not be appropriate and the number of files would be too big.

What is the best solution in this case?


texts: Scripted SQL Deployment in a Transaction with Rollback

texts:
Note: I believe this differs from other questions simply due to the size of the creation script I want to run under a single transaction.

Background

I'm working on automating the deployment of the data side of an application. I'm working with the team's existing scripts. They seem to drop everything and recreate. I don't have much time to make it perfect.

I'm thinking of running their drop-all script outside of a transaction, but running a single long "genesis" creation script inside a transaction at least so if that part should fail, its at a clean point and easier to recover from.

Genesis.sql contains 34k lines and seems to all be CREAT(ions) of functions, procedures, tables, types etc.

Question

Can I do this:

SET XACT_ABORT ON
BEGIN TRAN

# Genesis!

COMMIT TRAN

?

What's really confusing everyone is the impact of the GOs in the script with some saying that it will commit the transactions and other people (on the web) saying GO is just an instruction for the client to send a batch.

Follow-up

So far, this hasn't worked, new stuff is written and I see this message:

  The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

However, I think its mostly due to syntax errors in one or two scripts (that all get concatenated to 30k lines) and the parser is thrown out.

It seems I'll need to dummy run the scripts into an empty, local DB first, to discover syntax errors, then attempt against the target environment server.


texts: Oracle how to make primary key index usable from unusable?

texts:
I have table where it's primary key index got unusable due to partition merge ( I may have done it wrong).

select INDEX_NAME, STATUS from ALL_INDEXES WHERE TABLE_NAME = TRANSACTION' AND INDEX_NAME = 'PK_TRAN';

Status from above query is N/A (unusable)

Then I tried to drop the index

drop index PK_TRAN;

resulted following error.

SQL Error: ORA-02429: cannot drop index used for enforcement of unique/primary key

Then i tried to drop the constraint

alter table TRANSACTION drop constraint PK_TRAN;

resulted following error,

SQL Error: ORA-02273: this unique/primary key is referenced by some foreign keys

Then  i tried to drop the problematic reference constraints

select CONSTRAINT_NAME from all_constraints where constraint_type='R' and r_constraint_name='PK_TRAN';

alter table A drop constraint FK_1;

which resulted following error.

SQL Error: ORA-14650: operation not supported for reference-partitioned tables

How can i make transaction table's pk index valid(usable). Currently operation on this table fails with

ORA-01502: index 'PK_TRAN' or partition of such index is in unusable state


texts: Stop never ending backup in SQL Server 2012 Express

texts:
Our production database log file has grown to consume all disk space on our server.

I know it's bad practice but I want to be able to shrink it in order to restore the service as soon as possible.

Still, when I go and launch my SHRINKFILE command,I get this error :

  Msg 3023, Level 16, State 2, Line 2
  Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on
  a database must be serialized. Reissue the statement after the current
  backup or file manipulation operation is completed.

It seems to me that the backup will never end. Is there a way to stop it manually ?

(I tried the SQL Server Agent thing, as told in the MSDN documentation, but it is not present in my SQL Server Express)


texts:
I'm going to use AlwaysOn technology on my servers, but it's some unclear points in the documentation:

Automatic failover looks like

Primary replica (A) unaccessable
Secondary replica (B) becomes first replica
When A become available it sets to secondary replica and get all changes from primary replica (B)
???

in documentation is written that:

  Later, when the server instance that is hosting the former primary replica restarts, it recognizes that another availability replica now owns the primary role. The former primary replica transitions to the secondary role, and its databases become secondary databases. The new secondary replica connects to the current primary replica and catches its database up to the current primary databases as quickly as possible. As soon as the new secondary replica has resynchronized its databases, failover is again possible, in the reverse direction.

Does it mean that former primary replica (A) will become primary replica automatically? Or it (A) will be secondary and failover in reverse direction must be done manually?

Thanks for your help


texts: MySQL under what scenario suitable to use WHERE EXISTS

texts:
I recently just noticed that WHERE EXISTS can slow down the operation badly. E.g.

select SQL_NO_CACHE count(*) as aggregate
from `table_1`
where exists (select *
              from `table_2`
              where `table_1`.`fk_id` = `table_2`.`id`
                and `name` = 'john');

79 seconds

VS

select SQL_NO_CACHE count(*) as aggregate
from `table_1`
inner join `table_2` on `table_1`.`fk_id` = `table_2`.`id`
where `table_2`.`name` = 'John';

72 milliseconds

table_1 has total 4,723,710 records
table_2 has total 1,236 records

Just wanted to know, since the performance of WHERE EXISTS so bad, under what scenario do we need to use this?


texts: WorkAround for PHP PDO(with libpq V 9.1.4) binding for use of CITEXT?

texts:
The scenario

Two systems(not server) running PHP and PostgreSQL with the following versions

Fedora 15:

  PHP

  PHP 5.3.13 (cli) (built: May  9 2012 14:38:35)
  Copyright (c) 1997-2012 The PHP Group
  Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend Technologies
  pdo_pgsql
  PostgreSQL(libpq) Version     9.0.7
  Module version    1.0.2

  PostgreSQL

  PostgreSQL 9.1.4
  With CITEXT extension enabled.

ArchLinux:

  PHP

  PHP 5.4.6 (cli) (built: Aug 16 2012 12:50:09)
  Copyright (c) 1997-2012 The PHP Group
  Zend Engine v2.4.0, Copyright (c) 1998-2012 Zend Technologies
  pdo_pgsql
  PostgreSQL(libpq) Version     9.1.4
  Module version    1.0.2

  PostgreSQL

  PostgreSQL 9.1.4
  With CITEXT extension enabled.

when a simple query such as

select column1 from schema1.table1 where column1= ?

Where column1 is of the type CITEXT, is executed through PHP PDO

On Fedora with PHP 5.3.13,libpq 9.0.7, the query performs as expected with CITEXT(a case insensitive search occurs).
On ArchLinux with PHP 5.4.6,libpq 9.1.4, the query does not perform as expected with CITEXT(a case sensitive search occurs).

I am guessing the newer version of PHP PDO libs are doing something similar to this:

select column1 from schema1.table1 where column1= 'value'::text;

during binding.

Am I right?
Is there a workaround? Otherwise, the use of CITEXT as a column data type to gain the advantage of case insensitive search is useless when using newer versions of PDO.

Update

After turning statement level logging on, on ArchLinux with PHP 5.4.6,libpq 9.1.4 :

LOG:  execute pdo_stmt_00000001: select column1 from schema1.table1 where column1 = $1
DETAIL:  parameters: $1 = 'value'
LOG:  statement: DEALLOCATE pdo_stmt_00000001

where the actual value of the column column1 is VALUE.

Still comes back with 0 elements.

When the statement

select column1 from schema1.table1 where column1 = 'value';

is executed directly on the PSQL prompt comes back with a single row.

 column1
---------
  VALUE
 (1 row)

So, the type casting does not take place! I still am not able to understand the behaviour of PDO/postgresql.

Update 2012-08-27 16:15:43.669142+00 (UMT + 0)

After trying to directly execute a query without preparing a statement.

Here is the code that was used to test :

try {
    $db = new PDO('pgsql:dbname=database1;user=user;password=pass;host=localhost');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "SELECT column1 from schema1.column1 where column1 = 'value'::citext ";
    $retval=$db->query($sql);
    foreach ($retval as $row) {
        print $row['uname'] . '
';
    }
}catch (PDOException $PDOerr) {
    echo 'An error occured :
';
    var_dump($PDOerr);
    exit;
    //some thing went wrong while performing the action on db.
    }

I get the error :

object(PDOException)#10 (8) { ["message":protected]=> string(211) "SQLSTATE[42704]: \
Undefined object: 7 ERROR: type "citext" does not exist LINE 1: ...

I do not understand why citext is not getting detected!
When the statement is executed directly on the PSQL prompt, everything works fine as mentioned above.


texts: Why getting Error for 1 Table when migration Database to another SQL Server Express 2014 in SQL Compare?

texts:
I'm trying to move Data form one SQL Server Express 2014 to another.
First I have Imported Data though SQL Management Import/Export Wizard. THen I tried to synchronize all Tables using SQL Compare and I got following Error related to one Table:

  The following error message was returned from the SQL Server:

  [1105] Could not allocate space for object 'dbo.SORT temporary run
  storage:  422212498751488' in database 'DatabaseName' because the
  'PRIMARY' filegroup is full. Create disk space by deleting unneeded
  files, dropping objects in the filegroup, adding additional files to
  the filegroup, or setting autogrowth on for existing files in the
  filegroup. Could not create constraint or index. See previous errors.
  Error executing the following SQL: ALTER TABLE [dbo].[Table1] ADD
  CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED  ([Table1ID])

  The following SQL command caused the error:

  ALTER TABLE [dbo].[Table1] ADD CONSTRAINT [PK_Table] PRIMARY KEY
  CLUSTERED  ([Table1ID])

This Table is quite big with Data but not bigger than 4GB.

Reserved(KB): 3,151.496
Data(KB): 2,616.680

Free Disk Space on both Servers is > 200GB

Any idea why I'm getting this Error?


texts: How to view the full, flattened query underlying a Postgresql view?

texts: If I have a view on a view on a view (etc.), is there a way to show the full query that this expands/flattens to?


texts: Change database and execute update query (T- Transact)

texts:
Could anyone face with issue like me?

I need to update some rows in table, but I don't know exact name of database. I am able to figure out name with:

Select name
from sys.databases
where name like '%%'

and then I need to execute update query, but I can't because I don't know how to force SQL server change database.

use "name of database"

update table
set column1 = column1 + 1

I tried to declare variable and set it as exact name of database.

*EXEC ('use ' + '@dbname')
update table
set column1 = column1 + 1*

In this case, I got error about invalid object "table"

Does anyone know how to do it?


texts: How do I decompose ctid into page and row numbers?

texts:
Each row in a table has a system column ctid of type tid that represents the physical location of the row:

create table t(id serial);
insert into t default values;
insert into t default values;

select ctid
     , id
from t;

ctid  | id
:---- | -:
(0,1) |  1
(0,2) |  2

dbfiddle here

What's the best way of getting just the page number as from the ctid in the most appropriate type (eg integer, bigint or numeric(1000,0))?

The only way I can think of is very ugly.


texts: Using Resource Governor with SQL Agent Jobs

texts:
I'm trying to setup Resource Governor for limiting IOPS for processes running using SQL agent jobs. The classifier function is set to identify a particular login (any spid that is running using that login should use the resource group it is assigned to). I then added EXECUTE AS LOGIN = 'ResourceGovernerUser' within the job, but I cannot get it to work and it falls back to the default pool because the jobs are 'EXECUTED AS 'ServiceAccount''. Although if I look at the active processes using sp_WhoisActive, the login name is displayed as 'ResourceGovernerUser' instead of the Service Account. So I modified the classifier function to use the Service Account and then it just worked. I verified this using the perfmon counters 'Disk Read IO/Sec' and 'Disk Write IO/Sec' under 'Resource Pool Stats' object.

The question is - how do I get Resource Governor to use a login other than the Service Account (when used with agent jobs)? I do have some untested ugly ideas like - running the jobs using a CmdExec or PowerShell proxy. If anyone has come across a similar situation or has any better ideas, I would really appreciate it. Thank you.

    --Example Setup:
    USE [master]
    GO

    CREATE RESOURCE POOL [SqlJobPool] WITH(
            min_iops_per_volume=1,
            max_iops_per_volume=5000);
    GO

    CREATE WORKLOAD GROUP [IOGroup]
        USING [SqlJobPool];
    GO

    CREATE FUNCTION [dbo].[fn_LimitedIO]()
    RETURNS SYSNAME WITH SCHEMABINDING
    AS
        BEGIN
            DECLARE @grp SYSNAME;

            IF SUSER_NAME() = N'ResourceGovernerUser' -- When this is set to the Service Account, it works
                BEGIN
                    SET @grp = N'IOGroup';
                END
            ELSE
                BEGIN
                    SET @grp = N'default';
                END
            RETURN @grp;
        END
    GO

    ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fn_LimitedIO);
    ALTER RESOURCE GOVERNOR RECONFIGURE;
    GO

    --Within the Agent job
    EXECUTE AS Login = 'ResourceGovernerUser'
    EXECUTE SomeSQLStuff


texts: Is there any way to clear all tables using Liquibase?

texts:
I'm currently using Liquibase for creating test data in my database before each test run.

I came across a problem where some of the test cases need special data, while others need the tables to be empty.

So I decided to give Liquibase contexts a try and only insert the data I really need for the current testcase plus - in an abstract superclass - all the data every test case needs (user accounts and stuff)...

I would like to clear the tables before each run, however I can't seem to find anything for this case except dropAll(), which seems to be overkill...

I'm not quite sure if it is even possible - because of foreign keys and other constraints - but I wonder if there is a way to delete the content of ALL the tables without dropping them completely?


texts: mysql error connecting to the server did not ask for password during the installation process

texts: I wanted to learn MySQL, so I decided to download it from https://dev.mysql.com/downloads/installer/. However, during the installation it did not ask me for any password, now I cannot connect to the server as it needs a password. I have tried reinstalling it but it still does not ask for the password. Can somebody tell what the problem is?


texts: How to Migrate complete Transactional Replication to other servers without Snapshot?

texts:
We have large databases that are being kept in sync using Transactional Replication. They are all running in VMs and we are going to move to a new provider and reorganize the network structure and application distribution.
Is it possible to migrate the Transactional Replication: Publisher, Subscriber, Distribution Database without having to take a new Snapshot?
Currently we have in Server_1 the Publisher and in Server_2 the Subscriber and Distribution Database.
We are going to move the Publisher to Server_3 and the Subscriber and Distribution Database to Server_4
I don't have much experience with SQL Replication so I have been reading a lot. I found this answer here. Reading it, I understand that if I follow these steps, moving the Replication to the new 2 servers should be possible:

During downtime we wait until all objects are in sync
Then Script out the Replication (does this include, Publisher, Subscriber and Distribution Database?),
Migrate the Publisher and Suscriber Databases to their new Locations
Create new Transactional Replication from Scratch (using the scripts generated from the existing Replication) with replication support only
Run snapshot jobs to ensure the creation of any System Objects missing.

However I don't know If I have all the data/steps/understanding required and if I am  missing anything important.
Also, would it be possible not to wait for all the objects to be in sync? I would like to test the process but I don't know if I will be able to shutdown the databases for that long just to test. So could We migrate both databases without having them perfectly in sync and regenerate the Replication without Creating a Snapshot? Maybe without the "replication support only" option and running the snapshot Jobs. If we run the snapshot jobs in that situation will they create a whole snapshot since the DBs won't be in Sync or will it only look for the differences?
I am sorry if my questions seem rather obvious, I haven't worked with this Technologies before and I want to make sure to do everything right.


texts: How to store thousands of properties related to a record in PostgreSQL?

texts:
I have a database of approximately 300K records. For each of theses records there are up to 1600 related properties. Each of these properties have a simple key=>value format. This data is very static and nearly all operation will be reads.

Below are the options I'm thinking about to solve this problem.

1) A column for each property

This falls apart quickly as the max number of columns available in postgres is ~1600. So really, this isn't an option.

2) Create an hstore or JSONB column to store the key value columns.

This seems to work but I've read there are practical limits to how much data should be stored in hstore or JSONB. There are a lot of key/values and I think both hstore and JSONB will store these in TOAST which could have a negative impact on performance.

What potential issues/practical limits should I be aware of if using approach #2?

What are some other approaches to solving this problem?


texts: Move database from windows server 2003 to windows 7/8?

texts:
I am working with this company currently running their SQL Express database in a virtualized Windows Server 2003.

What would you experts say about replacing that for a virtualized slimmed-down windows 7 or 8.1?

I am aware this is not ideal but they are not willing to pay for a new server licence. (Please understand that this cost can be a higher concern depending on where you are).

This is a small network with only 8 local users and some RDP users (2 regular users, 2 very casual users).

If I understand correctly, Windows 7 has raised TPC connections limits to 20, but  I am not sure how this or other limits will affect an SQL instance on top of it.

The databse is purged at the beginin of the year and then its size is ~1.5 GB, by the end of the year it grows (*) to ~10 GB.

() It grows unnecesarily as there is absolutely not that much valuable data in it, it is moslty logs...*

What would you suggest?
Thank you.


texts: How to Get Median Value From Row in SQL Server 2008 R2 Without Using Pivot and Unpivot Table

texts:
Create Table Script

create table temp
(
    id int identity(1,1),
    a decimal(6,2),
    b decimal(6,2),
    c decimal(6,2),
    d decimal(6,2),
    e decimal(6,2),
    f decimal(6,2),
    g decimal(6,2),
    h decimal(6,2),
    i decimal(6,2),
    j decimal(6,2),
    k decimal(6,2),
    l decimal(6,2),
    m decimal(6,2),
    n decimal(6,2),
    o decimal(6,2),
    p decimal(6,2),
    q decimal(6,2),
    r decimal(6,2),
    s decimal(6,2),
    t decimal(6,2),
    u decimal(6,2)
)

Insert Script

insert into temp
    (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u)
values
    (1,5,6,7,8,2,6,3,4,5,2,1,6,5,7,8,2,7,6,2,8)

insert into temp
    (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u)
values
    (1,5,6,7,8,2,2,3,2,4,2,1,4,5,9,8,2,7,6,2,8)

Expected Result

Median
======
first row  - 5.00
second row - 4.00

Non-working Solutions

I tried the below query which is working fine in SQL Server 2014, but has issues in SQL Server 2008 R2.

select id, avg(val)
from (
    select id, val
         , count(*) over (partition by id) as c
         , row_number() over (partition by id order by val) as rn
    from temp unpivot (
             val for col in (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u)
         ) as x
) as y
where rn IN ((c + 1)/2, (c + 2)/2)
group by id;

I ran the above query in 2014 version and it's working properly, but it's not working in 2008 R2. I get this error in SQL Server 2008 R2:

  Incorrect syntax near the keyword 'for'

The reason must be because my database's compatibility level is 80. But if I change the compatibility level, it will affect my application, so I can't do that.

I've also tried this query:

select id,
(select cast(Avg(TotAvg)as decimal(6,2)) as Median from (values (convert(decimal(6,2), a)),(convert(decimal(6,2), b)),
(convert(decimal(6,2), c)),
(convert(decimal(6,2), d)),(convert(decimal(6,2), e)),
(convert(decimal(6,2), f)),(convert(decimal(6,2), g)),(convert(decimal(6,2), h)),(convert(decimal(6,2), i)),
(convert(decimal(6,2), j)),(convert(decimal(6,2), k)),(convert(decimal(6,2), l)),(convert(decimal(6,2), m)),
(convert(decimal(6,2), n)),(convert(decimal(6,2), o)),(convert(decimal(6,2), p)),(convert(decimal(6,2), q)),
(convert(decimal(6,2), r)),(convert(decimal(6,2), s)),(convert(decimal(6,2), t)),(convert(decimal(6,2), u))) as Totalavg(TotAvg))
Median
from tempone

Obviously it calculates the average, but I need the median.


texts: Zero Date Values & Converting Entire DB From MyISAM to InnoDB

texts:
I'm trying to update the MYSQL DB for an older wordpress/woocommerce site that's currently using  MyISAM by switching the engine type to InnoDB. The issue I'm having using Alter Table is that there's a number of tables that have a zero date set as the default value, which SQL is not allowing me to change to null.

I don't want to set mysql to ignore the error since it's been deprecated and will likely cause additional problems down the road. Any suggestions would be appreciated.

Also, should I be concerned about causing conflicts by converting all tables to InnoDB from MyISAM?


texts: MySQL failover: how application can be switched to a new master in master-slave configuration?

texts:
In my case I have two MySQL servers. One is master and second one is slave.

When slave is not available, my application can read and write from/to master.

When master is not available, assume there is a tool mysqlfailover which successfully nominates a new master (from slave).

It sounds good, but application has master hostname inside the configuration. So it fails to find it.

What are available options to solve this problem? How application can be switched to a new master?

I know that I can switch hostname to point to the new IP, use Kubernetes (StatefulSet) + DB in a container, but anything else as free option on bare metal?

If speaking broadly, how this problem is solved using HA/failover tools?

Thank you


texts: Which is better in performance at these tables design?

texts:
I have been asked to create something which tracks the daily cost to collect on images, and I am trying to figure out a database table schema that would boost this.

Company has over 1.3 million crawled products which is processed to be verified to be products to be 900K~1M.

Current database:
Courses 1-1 to CrawledCourses 1-n to CrawledCoursesImages

When crawledCourses can't be indexed in the search till they are verified,when verified=true it is copied to Courses indexed in search and images are accessed from crawledCoursesimages through crawledCourses

My issue with this is this table is going to get images slowly.
Two ideas are here to boost it a bit.

First method is more denormalized and create arrayfield to copy paths to images in where another database column will need to be added..
Second method is to add a foriegnkey to CrawledCoursesImages and get images from this relation.

Whick one of the two methods, which do you prefer? Why? Is there another alternative that you can think of which would handle this better?

The database is Postgresql
Another note: Images are not queried in all queries but most.


texts: Where does Oracle Enterprise Manager derive the "User Logon Time" metrics from?

texts:
Oracle's enterprise manager has a metric named "User Logon Time" and will show an alert if that exceeds a certain threshold.

I would like to retrieve the numbers stored for this metrics directly using a SQL query.

However, I cannot find any view where this is stored. I can find "Logons Per Sec", "Logons Per Txn", "Current Logons Count" in the DBA_HIST_SYSMETRIC_SUMMARY view and "logons cumulative" and "logons current" in the DBA_HIST_SYSSTAT  view.

The view DBA_HIST_METRIC_NAME also doesn't list any metric of that name.

So where does the Enterprise Manager get that information from?

I'm talking about this alert:


texts:
Possible Duplicate:
  Find out what queries are causing the biggest amount of network traffic

We are getting over 90 Mbps from SQL Server to each web servers on port 1433.

We are using connection pooling.
We have CLR Integration enabled.
We are running CDC.
We have snapshot transactions turned on.
We are using SQL Server 2008 R2 SP1

But when we get SQL Profiler on the case and remove all the Login and Logout events we are seeing low activity.

Any recommendation of getting great visibility on what's going over the wire?

Or are the Login and Logout events very chatty over the network?


texts: Prevent COUNT() Subquery using window functions

texts:
A developer colleague of mine has asked me to optimize statements for him. I have had a brief introduction to current level SQL technology and functionality and have the feeling that especially the window functions could help me.

The query in question is currently like this:

SELECT
T1.FIELD1,
T1.FIELD2
...
(SELECT COUNT (T2.FIELD1) FROM OUTER_TABLE_NAME T2 WHERE T2.FOREIGN_KEY = T1.PRIMARY_KEY) AS COMMENTS
FROM T1
;

This forces the database to run the subquery for each record from the primary table T1, which is generally slow. A better way would be if there was a window function that could express the same. However, it must not multiply the records from the primary table T1.

I thought about something like this:

SELECT
T1.FIELD1,
T2.FIELD2
...
COUNT(T2.FOREIGN_KEY) OVER (PARTITION BY T2.FOREIGN_KEY)
FROM T1
LEFT OUTER JOIN T2 ON T2.FOREIGN_KEY = T1.KEY
;

It needs to be an OUTER JOIN as to not filter records from the primary table T1. However, this way would multiply the records from the primary table T1 for every matching record from the secondary table T2.

Is there a way to express the count() using either window functions or any other non-proprietary methodology in Oracle 12.2?


texts: A Query in the Query Store shows high Logical Reads

texts:
When I look at the Query Store reports for Top Resource Consuming Queries,
there is one query that skews the report so much that I can barely see the next query because this one is so high.

Highest one:
Total logical reads: 12956022792
execution count:     2458
plan count:          6

The next highest one:
Total logical reads: 76670280
execution count:     106
plan count:          1

What are some recommendations for improving this query?

Here is the plan:
https://www.brentozar.com/pastetheplan/?id=B1AlsOMn-


texts: Pair of 2-tuple composite FKs pointing to same table (closure table pattern)

texts:
I don't have much experience with DB design, and having a hard time figuring out what's wrong with the following:

Table father has a composite PK (col1, col2)
Table closure has ancestor(a-col1, a-col2), descendant(d-col1, d-col2) and length. Ancestor and Descendant are composite FKs, each pointing to the PK of father. The 4 columns (a-col1, a-col2,d-col1, d-col2) are a composite PK.

Look at the following fiddle: http://sqlfiddle.com/#!2/71ad1/1

I am working with MySQL.

I have constraints in CASCADE, but when I try to update the father table, the constraints give me errors. I can't do the following update to father table row:

UPDATE father
SET `col1` = "UPDATE_MY_ID_PLEASE"
WHERE `col1` = 'A' AND `col2` = 1;

Is the design wrong? How should this be done?


texts: When is an application using too many sessions?

texts:
I'm working on a client-server application that uses OCI to communicate with Oracle. In the past our application often treated sessions as an infinite resource, quite possibly to the detriment of other applications that are sharing that Oracle server.

I'm trying to convince my fellow developers that it is important for our application to play nice with others and not be such a resource hog.

At what point would you Oracle DBAs consider an application to be using (or abusing) sessions excessively?

EDIT: Currently one of the biggest consumers of sessions is this one component that uses direct path loading to bulk load several (20-30) tables in parallel.


texts: Copy over data from previous rows in oracle 11g table

texts:
Our DB is Oracle 11g. I have a table with the structure is as shown below:

Person_Id |  Eff_Dt    |   Wid | Prv_Wid
----------------------------------

P1       |   1/1/2001   | 2001 |
P1       |   1/10/2001  | 2001 |
P1       |   10/10/2001 | 2002 |
P1       |   1/1/2002   | 2003 |
P1       |   5/4/2002   | 2003 |
P1       |   8/6/2002   | 2002 |
P1       |   1/1/2005   | 2001 |
P1       |   1/10/2006  | 2001 |

I have a requirement that the Prv_Wid should be derived from the previous row's WID. But if the current and previous WIDs are same, then I have to go to the old previous row where the WID is different and put it in my current row's Prv_Wid.
It should be as shown below:

Person_Id | Eff_Dt | Wid| Prv_Wid
----------------------------

P1        | 1/1/2001    |    2001   |    0
P1        | 1/10/2001   |    2001   |    0
P1        | 10/10/2001  |    2002   |  2001
P1        |  1/1/2002   |    2003   |  2002
P1        |  5/4/2002   |    2003   |  2002
P1        |  8/6/2002   |    2002   |  2003
P1        |  1/1/2005   |    2001   |  2002
P1        | 1/10/2006   |    2001   |  2002

I have tried several ways like lead, lag, first_value, last_value and procedure to achieve. But, I am not successful. Could you please provide any solution?


texts: PostgreSQL: Prevent SQL injection for like query with % input

texts:
We are allowing the user to post the contents on our application, in a case user entered the % in the title of the post and we are running like query to fetch the data from the database.
Here is the snippet from the post table

id |         title           | short_description |   long_description
 1   how many % amounts you       ----------          --------------
     should save                --------------       -----------------

Query we are running:

SELECT id, title from post where post.title LIKE '%string entered by user%'

Now if the user enters amounts then query works fine and returns something like.

SELECT id, title from post where post.title LIKE '%amounts%'

and returning the desired result.

But, when a user enters how many % amounts in the search field, it is returning all the rows which are starting from how many and contains amounts in the upcoming words, which is wrong because the generated query is.

SELECT id, title from post where post.title LIKE '%how many % amounts%'

And another thing is if the user enters only % the query returns all the results.

If we try escaping the % or other special characters and the result is way more different than it should be.

Please suggest a solution if you have any.


texts:
I made the tables blood_types and persons. Should the foreign key  be created in the blood_types or in the persons table like I did?

A person can have 1 blood type. A blood type can be assigned to multiple persons.

CREATE TABLE blood_types
(
 blo_id serial NOT NULL,
 blo_name character varying(10),
 CONSTRAINT blood_types_pkey PRIMARY KEY (blo_id)
)

CREATE TABLE persons
(
  per_id serial NOT NULL,
  per_passport character varying(50),
  per_name character varying(50),
  per_blood_type integer,
  CONSTRAINT persons_pkey PRIMARY KEY (per_id),
  CONSTRAINT persons_per_blood_type_fkey FOREIGN KEY (per_blood_type)
      REFERENCES per_blood_types (blo_id) MATCH SIMPLE
)

Or maybe should the foreign key be created in the blood_type table?
I have more similars and simples examples but I can't understand.
Should I think as weak and strong entities (I think both are strong) or maybe having in mind the cardinalities?


texts: How to set SSRS parameter Default to uncheck specific options?

texts:
In a multi-select Parameter for SSRS (2008), I want the default state of an input parameter to be such that one option is unchecked while all others are checked. Like in the image that follows:

Is there any way in SSRS that this can be accomplished? I guess there is no way to refer to a "checked" state so this can be handled?


texts:
I have SQL server 2017. I have one 3 TB size database there. Somehow due to long running transaction the database got stuck 'IN Recovery' mode after SQL server Restarted. When I checked the sql error logs it says 2 189 255 seconds remaining (Phase 2 of 3) completed which is almost 25 days. My goal is to bring the database online even if I lose some data.

So I've ran below commands but no luck.

  USE [master]
  GO

  RESTORE DATABASE test WITH RECOVERY
  --Msg 3101, Level 16, State 1, Line 6
  --Exclusive access could not be obtained because the database is in use.
  --Msg 3013, Level 16, State 1, Line 6
   --RESTORE DATABASE is terminating abnormally.

  ALTER DATABASE test SET EMERGENCY;
  GO
  --Msg 5011, Level 14, State 7, Line 13
  --User does not have permission to alter database 'DragonDriveConnect',
  the database does not exist, or the database is not in a state that
  allows access checks.
  --Msg 5069, Level 16, State 1, Line 13
  --ALTER DATABASE statement failed.

  DBCC CHECKDB (DragonDriveConnect, REPAIR_ALLOW_DATA_LOSS) WITH
  ALL_ERRORMSGS;
  GO
  --Msg 922, Level 14, State 1, Line 22
  --Database 'DragonDriveConnect' is being recovered. Waiting until
   recovery is finished.

At last I've tried to Delete the database too but that is also not working and giving me error saying Cant Delete.

How do I get out of this situation?


texts: Database design for products with multiple units

texts:
I am designing a database for retail business. There are some products that can be sold in multiple units, for example, pencils can be sold in ea and dozen, paper can be sold in sheet, ream, and canton. Basically, each product can be sold in more than one unit.

The software needs to supports

Can receive products from suppliers in many unit. Sometime we might order 1 pencil and the next time we order 2 boxes of pencil.
Can sell products in multiple unit, for example, we must be able to sell 1 box and 2 pencils in the same bill.
Can check the actual items in stock.

The following are my initial design.

Table Products
ProductId | Barcode | Name   | CurrentPriceId
1         | XXXX    | Pencil | 1

Table Prices
Id | Amount
1  | 0.49

Table Units
UnitId | Name
1      | Ea
2      | Box

Table UnitConverter
ProductId | FromUnitId | Multiplier | ToUnitId |
1         | 1          | 24         | 2        | // 24 pencils equals 1 box

Table Inventories
Id | ProductId | UnitId | Quantity | PurchasePrice
1  | 1         | 1      | 48       | 0.23

Table Invoices
Id | ProductId | UnitId | Quantity | PriceId
1  | 1         | 1      | 27       | 1

Is there any flaws in my design? Is there anything that I miss?


texts: SQL Server to MySQL migration - how can I remove UCS-2 surrogate pairs in SQL Server?

texts:
I have been tasked with migrating a SQL Server 2005 database to MySQL 5.6 (these are both database servers runnig locally) and would really appreciate some help.

-------Edit - Answered-------

This issue has now been resolved. I used Remus Rusanu's suggestion for finding the rows with these surrogate pair characters using CHARINDEX and have decided to use SUBSTRING to exclude the troublesome characters like so:

UPDATE test
SET a = SUBSTRING(a,  1,   (CHARINDEX(0x83dc, CAST(a AS VARBINARY(8000)))+1)/2 - 1) -- string before the unwanted character
+ SUBSTRING(a, (CHARINDEX(0x83dc, CAST(a AS VARBINARY(8000)))+1)/2 +1, LEN(a) ) -- string after the unwanted character
WHERE CHARINDEX(0x83dc, CAST(a AS VARBINARY(8000))) % 2 = 1 -- only odd numbered charindexes (to signify match at beginning of byte pair character)

-----------------------------

SQL Server source database has latin1 collation (so has ISO 8859-1 character set right?) but doesn't have any char/varchar fields (any string field is nvarchar/nchar) so all this data should be using the UCS-2 character set.
MySQL target database wants the character set UTF-8.

I decided to use the database migration toolkit in the latest version of the MySQL workbench. at first it worked fine and migrated everything as expected. But I have been totally tripped up upon encountering UCS-2 surrogate pair characters in the SQL Server database.

The migration toolkit copytable program did not provide a very useful error message:

  Error during charset conversion of wstring: No error.

It also did not provide any field/row information on the problem-causing data and would fail within chunks of 100 rows. So after searching through the 100 rows after the last successful insert I found that the issue seemed to be caused by two UCS-2 characters in one of the nvarchar fields. They are listed as surrogate pairs in the UCS-2 character set. They were specifically the characters DBC0 and DC83 (I got this by looking at the binary data for the field and comparing byte pairs (little endian) with data that was being migrated successfully).

When this surrogate pair was removed from the SQL Server database the row was migrated successfully to MySQL.

Here is the problem:

I have tried to search for these characters in a test SQL Server table (this chartest table is just various test strings an nvarchar field) to prepare a replacement script and keep getting strange results... I must be doing something incorrectly.

Searching for

SELECT * FROM chartest WHERE text LIKE NCHAR(0xdc83)

Will return any surrogate pair character (whether or not it uses DC83), but obviously, only if it is the only character (or part of the pair) in that field. This isn't a big deal since I would like to remove any instance of these anyway (I dont like to remove data like this but I think we can afford it).

Searching for

SELECT * FROM chartest WHERE text LIKE '%' + NCHAR(0xdc83) + '%'

Will return every row! Regardless of whether it even has a unicode character present in the field let alone the DC83 character. Is there a better way to find and replace these characters? Or something else I should try?

I have also tried setting the target databse, table, and field character set to UCS-2 but it seems as though it does not make a difference.

I should also mention that this migration is using live data (~50GB database!) while one of the sites that feeds it is taken offline so any solutions to this need to have a quick running time...

I would appreciate any suggestions very much! Please let me know if there is any information I have left out.


texts: How to get row number without windows functions in SQLite?

texts:
Assume there is the following table schema and data

create table tbl(
    id integer primary key,
    name text,
    pid integer
);

insert into tbl
values
    (1,'d',0),
    (2,'c',1),
    (3,'b',1),
    (4,'e',1),
    (5,'b',0),
    (6,'a',0);

It is a one-level depth hierarchy of parent and child items.

I need to aggregate children names into their parents like so

 id | name | children_names
----+------+------------
  6 | a    |
  1 | d    | c, e, b
  5 | b    |

children names need to be sorted within each row and the entire result need to be sorted by name column alphabetically, but all b names must always go last.

In PostgreSQL I would do it with row_number() over() window function like this

with
t as (select * from tbl order by name),
t2 as (select * from t where name<>'b' union all select * from t where name='b'),
t3 as (select *, row_number() over() from t2)
select a.id, a.name, string_agg(b.name, ', ' order by b.row_number)
from t3 a left join t3 b on a.id=b.pid and a.id<>b.id
where a.pid=0
group by a.id, a.name, a.row_number
order by a.row_number

but I need this in Android Room which lacks window functions yet.

So how to get the same result without window functions used?


texts: I hope I can find some help with an issue in Oracle 11g on Windows 2008.  We've done a system copy by copying the datafiles from backup, and recreating the control files, and the database is up. However, within an hour or so, all sessions become taken by SYSMAN. We've found that stopping the dbconsole (emctl stop dbconsole) will terminate them. Is there something I should do to refresh dbconsole, or delete and recreate the configuration?


texts: Postgres. INSERT INTO table in stored procedure with array of composite type objects as procedure arguments

texts:
I have two composite types:

CREATE TYPE truck.truck_event_type AS
(
    event_global_key       VARCHAR(100),
    event_type             VARCHAR(50),
    datetime_utc           TIMESTAMP(4),
    file_keys              VARCHAR(200)[],
    status                 VARCHAR(50),
    address                VARCHAR(500),
    client_event_id        VARCHAR(100),
    geo_coordinate         truck.truck_locations_geocoordinate
);

And

CREATE TYPE truck.truck_locations_geocoordinate AS
(
    latitude         REAL,
    longitude        REAL,
    speed            REAL,
    direction        VARCHAR(2),
    datetime_utc     TIMESTAMP(4)
);

As you can see, type truck.truck_event_type has an ATTRIBUTE with another composite type. I want to create a procedure that takes an array of truck.truck_event_type and truck global key and insert them into table.

That's what I've tried to do. The procedure declaration:

CREATE PROCEDURE truck.create_event_collection
(
    _truck_global_key      VARCHAR(100),
    _events                truck.truck_event_type[]
)
AS
$$
BEGIN

    INSERT INTO truck.truck_events
    (
        truck_id,
        client_event_id,
        event_global_key,
        event_type,
        datetime_utc,
        file_keys,
        status,
        address,
        latitude,
        longitude,
        speed,
        direction
    )
    SELECT
        t.id,
        e.client_event_id,
        e.event_global_key,
        e.event_type,
        e.datetime_utc,
        e.file_keys,
        e.status,
        e.address,
        e.geo_coordinate.latitude,
        e.geo_coordinate.longitude,
        e.geo_coordinate.speed,
        e.geo_coordinate.direction
    FROM truck.trucks as t
    JOIN unnest(_events) AS e ON TRUE
    WHERE t.truck_global_key = _truck_global_key
    ON CONFLICT (client_event_id) DO NOTHING;

END
$$ LANGUAGE plpgsql;

And I've tried to call it from Postgres directly:

CALL truck.create_event_collection
(
    '0be11cd6-eead-47ad-af64-83d714f22ee2',
    ARRAY[
        ('egk_1', 'emergencyphoto', '2020-01-09 15:00:00', ARRAY['1', '2'], 'emergency', 'adr1', 'cid_1', (1.1, 1.2, 1.3, 's', '2020-01-09 15:00:00')::truck.truck_locations_geocoordinate)::truck.truck_event_type,
        ('egk_2', 'statuschanged',  '2020-01-09 16:00:00', ARRAY['3', '4'], 'moving',    'adr2', 'cid_2', (2.1, 2.2, 2.3, 'w', '2020-01-09 16:00:00')::truck.truck_locations_geocoordinate)::truck.truck_event_type
    ]
)

Then I got this error:

ERROR:  missing FROM-clause entry for table "geo_coordinate"
LINE 25:         e.geo_coordinate.latitude,
                 ^
QUERY:  INSERT INTO truck.truck_events
    (
        truck_id,
        client_event_id,
        event_global_key,
        event_type,
        datetime_utc,
        file_keys,
        status,
        address,
        latitude,
        longitude,
        speed,
        direction
    )
    SELECT
        t.id,
        e.client_event_id,
        e.event_global_key,
        e.event_type,
        e.datetime_utc,
        e.file_keys,
        e.status,
        e.address,
        e.geo_coordinate.latitude,
        e.geo_coordinate.longitude,
        e.geo_coordinate.speed,
        e.geo_coordinate.direction
    FROM truck.trucks as t
    JOIN unnest(_events) AS e ON TRUE
    WHERE t.truck_global_key = _truck_global_key
    ON CONFLICT (client_event_id) DO NOTHING
CONTEXT:  PL/pgSQL function truck.create_event_collection(character varying,truck.truck_event_type[]) line 4 at SQL statement
SQL state: 42P01

What am I doing wrong? My procedure has worked correctly before, when latitude, longitude, speed and direction fields were separate fields in truck.truck_event_type. How to insert objects which are of composite type of composite type (nested composite type)?


texts: voluntarily produce an error with DBCC CheckDb

texts:
Would anyone know how I can make DBCC CheckDB produce an error?
I'm testing a script and need DBCC Checkdb in the simplest mode to return an error, also while trying to repair and when trying to go into single user mode...

The simple dbcc checkdb command thant I'm using is part of a batch file and is as follows:

OSQL -S MYPC -E -d MyDB -l 10 -Q "DBCC CHECKDB(AG_DB_STORESQL)" -b -o c:\MyFolder\MSSQL_DB_CHECK.Log

If no errors are found the script ends.  If errors are found, the script tries to fix them with the REPAIR_REBUILD option and if that fails the script tries to fix them with REPAIR_ALLOW_DATA_LOSS option.  Obviously, before trying to Repair the script attempts to put the DB in Single user mode (which is also error trapped)

So All I'm looking for is a way to cause DBCC CheckDB to return an error.


texts: How to remove encryption from all objects in SQL server?

texts:
I have more than a hundred encrypted procedures and functions that I want to decrypt. I tried using dbforge sql decryptor decryption wizard for in place alter but I get the error "Definition is invalid. Can't find CREATE keyword".
When I try to see the DDL script of a stored procedure, I get the error "A definition for the object dbo.pt_blocks cannot be shown because it is enrypted by a third party tool"
I can not find a resolution to this. Are there any solutions or other tools available for this?
Edit: I found this resource which mentions "take the source code and issue an ALTER command without the encryption option. Just take the source code and remove the WITH ENCRYPTION"
How could I achieve this?


texts: Using a Replication Subscription Database for Publication

texts:
Hopefully, there's a very "simple" answer to this question:

Can a SQL Server 2012 subscription database in a transactional replication setup also be used for publication?


texts: Backup and restore for PostgreSQL + why number of backups increased while we limit the recovery window of 7 days

texts:
We are using the barman for PostgreSQL backup and restore on rhel 7.2 machines

In the barman configuration file - /etc/barman.conf we set the following

retention_policy_mode = auto
retention_policy = RECOVERY WINDOW OF 7 days

But when we go to the folder that hold all the barman backups we see more then 30 backup folders

Note - from barman.conf we set barman_home = /barman

pwd
/barman/main-db-server/base
drwxr-xr-x 3 barman barman 4096 2020 20200218T204628
drwxr-xr-x 3 barman barman 4096 2020 20200218T204702
drwxr-xr-x 3 barman barman 4096 2020 20200218T204802
drwxr-xr-x 3 barman barman 4096 2020 20200218T204901
drwxr-xr-x 3 barman barman 4096 2020 20200218T205002
drwxr-xr-x 3 barman barman 4096 2020 20200218T205102
drwxr-xr-x 3 barman barman 4096 2020 20200218T205202
drwxr-xr-x 3 barman barman 4096 2020 20200218T205302
drwxr-xr-x 3 barman barman 4096 2020 20200218T205402
drwxr-xr-x 3 barman barman 4096 2020 20200218T205502
drwxr-xr-x 3 barman barman 4096 2020 20200218T205602
drwxr-xr-x 3 barman barman 4096 2020 20200218T205702
drwxr-xr-x 3 barman barman 4096 2020 20200218T205802
drwxr-xr-x 3 barman barman 4096 2020 20200218T205902
drwxr-xr-x 3 barman barman 4096 2020 20200218T210002
drwxr-xr-x 3 barman barman 4096 2020 20200218T210102
drwxr-xr-x 3 barman barman 4096 2020 20200218T210202
drwxr-xr-x 3 barman barman 4096 2020 20200218T210302
drwxr-xr-x 3 barman barman 4096 2020 20200218T210402
drwxr-xr-x 3 barman barman 4096 2020 20200218T210502
drwxr-xr-x 3 barman barman 4096 2020 20200218T210602
drwxr-xr-x 3 barman barman 4096 2020 20200218T210702
drwxr-xr-x 3 barman barman 4096 2020 20200218T210802
drwxr-xr-x 3 barman barman 4096 2020 20200218T210902
drwxr-xr-x 3 barman barman 4096 2020 20200218T211002
drwxr-xr-x 3 barman barman 4096 2020 20200218T211102
drwxr-xr-x 3 barman barman 4096 2020 20200218T211202
drwxr-xr-x 3 barman barman 4096 2020 20200218T211302
drwxr-xr-x 3 barman barman 4096 2020 20200218T211402
drwxr-xr-x 3 barman barman 4096 2020 20200218T211503
drwxr-xr-x 3 barman barman 4096 2020 20200218T211602
drwxr-xr-x 3 barman barman 4096 2020 20200218T211702
drwxr-xr-x 3 barman barman 4096 2020 20200218T211802
drwxr-xr-x 3 barman barman 4096 2020 20200218T211902
drwxr-xr-x 3 barman barman 4096 2020 20200218T212002
drwxr-xr-x 3 barman barman 4096 2020 20200218T212102
drwxr-xr-x 3 barman barman 4096 2020 20200218T212203
drwxr-xr-x 3 barman barman 4096 2020 20200218T212302
drwxr-xr-x 3 barman barman 4096 2020 20200218T212402
drwxr-xr-x 3 barman barman 4096 2020 20200218T212502
drwxr-xr-x 3 barman barman 4096 2020 20200218T212602
drwxr-xr-x 3 barman barman 4096 2020 20200218T212702
drwxr-xr-x 3 barman barman 4096 2020 20200218T212802
drwxr-xr-x 3 barman barman 4096 2020 20200218T212902
drwxr-xr-x 3 barman barman 4096 2020 20200218T213002
drwxr-xr-x 3 barman barman 4096 2020 20200218T213102
drwxr-xr-x 3 barman barman 4096 2020 20200218T213202

In  crontab we defined to create backup each day as

30 23 * * * /usr/bin/barman backup main-db-server
* * * * * /usr/bin/barman cron

But since we limit the    retention_policy = RECOVERY WINDOW OF 7 days

From my understanding this means - limit the backup only to 7 days ,

But in spite this we get much more backups , why?

Note - the reason that we want to limit the number of backup is because we have small disk

meanwhile we delete the old backup manual

as

 barman delete  main-db-server 20191019T233002


texts:
I have a table that contains all information about user called 'DataSource'. I have columns called 'First Name', 'Surname', and 'Address' (there's more but as an example). I have two tables called 'Address' and 'User'. What I'd like to do is take the Address column for a user in DataSource and add it to the Address table. I would then like to take the First Name, Surname, and AddressID (created upon Address insertion) and add it to the User table. (Basically, split the data from DataSource into two tables and have the AddressID link between the two of them via the auto-generated AddressID from the Address table).

I've got the code below but it's not behaving as expected. The User table is being filled in a loop so every user is inserted the number of rows (e.g. if there are two people in the DataSource table, each user is inserted twice and given every address ID each time)

DECLARE @ids TABLE (address_id INT);

INSERT INTO dbo.Address(address)
OUTPUT INSERTED.address_id INTO @ids
SELECT [Address]
FROM dbo.[DataSource]

SET IDENTITY_INSERT dbo.[Users] OFF

INSERT INTO dbo.[Users]
        (
          first_name,
          surname ,
          address_id
        )

SELECT  first_name, surname,i.address_id,
FROM dbo.[DataSource], @ids i

How can I essentially do this sequentially so that the correct address is assigned once to the user? This is not a time sensitive operation so performance is not important but the integrity of the data is crucial.

Thanks


texts: Can SQL Server 2016 R Services connect to SAS, SPSS, URLs etc?

texts:
The RevoScaleR package of SQL Server R Services (Microsoft R Server) has functions that allow you to connect to SAS, SPSS, HDFS and text files.
My question is:

Can R Services connect to SAS/SPSS to directly import data from SAS into SQL Server 2016, or do I have to download the SAS/SPSS dataset into a .SAS file and then import it via the RxImport function?
Can I directly import datasets from a URL into SQL Server 2016?


texts: Oracle 11gR2 permit NOLOGGING but ensure non-corrupt backups

texts:
I'd like to stand up a database which offers a single tablespace for NOLOGGING operations (all other perm tablespaces set with FORCE_LOGGING=YES).  A conventional weekend full-backup + daily incremental should guarantee that database is recoverable to time-of-failure, except NOLOGGING tables in FORCE_LOGGING=NO tablespace.  What I'd like to guarantee is that all NOLOGGING tables are recoverable to daily incremental backup (with applications responsible for re-playing any ETL processing between backup and failure).

However, I understand that if NOLOGGING DML operation is running during incremental backup, the backup will have corrupt blocks - and entire NOLOGGING table (or partition thereof) may need to be truncated after recovery.  I'd like to avoid this, but without application & dba scheduling / coordination.

To avoid NOLOGGING operations from running during backup, my idea is that the backup script (full and incremental) is wrapped with FORCE_LOGGING On/Off commands:
1) ALTER DATABASE FORCE LOGGING;
2) Take backup
3) ALTER DATABASE NO FORCE LOGGING;

Questions:
Q1: What will happen if a NOLOGGING DML operation is running when backup script starts and FORCE_LOGGING=YES runs?  Will DML proceed (and backup get NOLOGGING corruption on affected tables)?  Will ALTER DATABASE wait for NOLOGGING transaction to commit (while preventing others from starting)?  Will NOLOGGING DML operation quietly rollback and restart in LOGGING mode?
Q2: For recovery itself, is there special syntax to say, "Restore tablespace A, B, and C to time-of-failure, but only recover tablespace D to last backup?"
Q3: Can NOLOGGING tables be restored to incremental backup, or only full level-0 backups?


texts: Benefits of using WITH TABLOCK on an INSERT

texts:
Under some circumstances, doing an INSERT INTO <tablename> (WITH TABLOCK) will be faster due to minimal logging. Those circumstances include having the database in the BULK_LOGGED recovery model.

Is there any other potential performance benefit to using WITH TABLOCK on an INSERT on an empty table when the database (tempdb) is using the SIMPLE recovery model?

I'm working with SQL Server 2012 Standard Edition.

My use case is for creating and then immediately populating a temp table within a stored procedure using an INSERT...SELECT, which could contain as many as a few million rows. I try to avoid that kind of tempdb abuse, but it is sometimes needed.

I'm trying to build a case to require TABLOCK. It doesn't seem like it would hurt anything, and might have a benefit. I'm trying to figure out if there is enough potential benefit to add it wherever throughout our code base, where I'm sure there is no other process that wants to write to the table.

I'm usually inserting into a newly created local temp table with a clustered PK, but do sometimes use a heap.


texts: Distributed transaction coordinator between SQL Server and Oracle

texts: I'm not sure about distributed transaction coordinator so please bare with me on this, I'm probably asking this question wrong. I know distributed transaction coordinator is used in SQL Server and can be used to link 2 SQL Server databases, but I am wondering can distributed transaction coordinator be used to link a SQL Server database and an Oracle database?


texts:
I am setting up a new server and I am designing the server for performance over capacity.  Planning the disk partitioning style I would like to use GPT over MBR but am not sure which is best.  I am using SAN attached storage.

Which partition style, MBR or GPT, is better for a SQL Server 2008 R2 implementation?


texts: The equivalent of Common Table Expressions in MySQL

texts:
I have a query which I was running in Microsoft SQL server and it worked just fine. It made use of CTEs because I joined three tables. The first two in an inner query then an outer one. However when I tried this on my web server it only has MySQL installed and I could not run the query. Is there any way to run a query with that structure? Here is a sample:

WITH CTE_DatesTable
AS
(
SELECT CAST('20160401' as date) AS [date]
UNION ALL
SELECT DATE_ADD(dd, 1, [date])
FROM CTE_DatesTable
WHERE DATE_ADD(dd, 1, [date]) <= '20170331'
) Select A.[date],
(SELECT CASE DATEPART(WEEKDAY,A.[date])
WHEN 1 THEN 'SUNDAY'
WHEN 2 THEN 'MONDAY'
WHEN 3 THEN 'TUESDAY'
WHEN 4 THEN 'WEDNESDAY'
WHEN 5 THEN 'THURSDAY'
WHEN 6 THEN 'FRIDAY'
WHEN 7 THEN 'SATURDAY' End) WkDay,
Case When Isnull(C.Hdate,'')<>'' then C.Descr
When (DATEPART(WK,A.[Date])%2)=1 And B.WkOff=1 Then 'AlterWeekoff'
When B.WkOff=0 Then 'Weekoff'Else 'WD' End As WorkDay,
Case When (DATEPART(WK,A.[Date])%2)=1 And B.WkOff=1 or Isnull(C.Hdate,'')<>'' Then 'Holiday'
When B.WkOff=0 Then 'Holiday' Else 'WeekDay' End As WorkDescr
From CTE_DatesTable A Left Join BPC_WDD1 B on DATEPART(dw,A.[Date])=B.WDay
Left Join BPC_HCL1 C on A.date=C.Hdate
OPTION (MAXRECURSION 0);


texts: How to connect to a local SQL Server instance by IP Address in SQL Server Management Studio?

texts:
I'm writing some test code that is required to connect to a local SQL Server instance (SQL Server 2017 - Developer Edition) by IP address. I have the SQL Server instance up and running on my machine, but can't figure out the correct IP + Port to use for my connection string's Server property.
As a starting point, I'm trying to at least manually connect to my server instance with SQL Server Management Studio (via the IP address) but I can't seem to figure it out.

How do I find the IP Address and Port of my local SQL Server instance?
What is the correct format I should be inputting the IP Address and Port into the Server Name field of SSMS (and is it the same format as a connection string Server property)?

I've tried locating the IP address executing the following SQL code on it:
SELECT
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address

But the local_net_address and local_tcp_port fields are NULL and the client_net_address is set to <local machine>:

I've also tried going into the SQL Server Configuration Manager, under the SQL Server Network Configuration section, and enabling TCP/IP. Then I tried using a mix of the IP4 and IP6 addresses with Port 1433 (which is blank by default in the TCP/IP Properties, but I've tried leaving it blank and setting it to 1433) and that didn't work.

(I made sure to restart the SQL Server service and SQL Server Browser service every time I made changes in here.)
I've received the following errors when trying to connect via SSMS for the different combinations of the IP4 address with and without Port specified:
No Port Specified:

Port Specified w/ Colon:

Port Specified w/ Comma:

I've also tried connecting via 127.0.0.1 with the same combinations of Port 1433 above to no avail. (I receive similar errors except for the following combinations.)
127.0.0.1 w/o Port:

127.0.0.1 w/ Port via comma:


texts: Using 'Listagg' function and 'Regular Expression' to REVERSE the value of a specific column in Oracle

texts:
I have a table with below structure:
create table TBL_TEST
(
  col_id   NUMBER,
  col_name VARCHAR2(500)
)

Some example data :
col_id | col_name
-----------------
  1    | aetnap
  2    | elppa
  3    | ananab

What I want to do is to reverse the value of col_name column like this :
col_id | col_name
-----------------
  1    | pantea
  2    | apple
  3    | banana

I used listagg and regexp_replace to write the query:
select col_id,
       listagg(val) within group(order by val_row desc) original_value
  from (select col_id,
               val,
               row_number() over(partition by col_id order by col_id) val_row
          from (select col_id, trim(column_value) val
                  from tbl_test,
                       xmltable(trim(trailing ',' from
                                     regexp_replace(col_name, '(.)', '"\1",')))))
 group by col_id;

The query above works fine and gives me the desired result, I want to know if there are better ways of writing the query cause two sub queries are used in the query above and I want to know whether there are better ways than using a sub query.
Thanks in advance


texts: For loop in query - how can i use Union?

texts:
hello friends I ´m working on a module that graphs data from a database, the thing is that I must graph a series of data that went through a mathematical formula first. So far no problem, but I must take data from the months of a year, therefore I must repeat this process twelve times. My truth question is this:

  How can i get just one row an column per response?

If I try something like this ...

DECLARE @cnt INT = 0;
WHILE @cnt < 12
BEGIN
select idestatus from order
SET @cnt = @cnt + 1;
END;

I'll get 12 queries executed & 12 tables And i just need one.
I will eventually transform this into a json, so I would like to have everything in a single response table
.This my query:

declare
@a decimal(5,2) = 0,
@d decimal(5,2) = 0,
@f1 decimal(5,2) = 0,
@f2 decimal(5,2) = 0

set
@a = (
select
COUNT(idEstatus) A_D
from t1
where (year(dat)) = 2017 and (month(dat)) = 1
and idEstatus = 2
)
set
@d = (
COUNT(idEstatus) A_D
from t1
where (year(dat)) = 2017 and (month(dat)) = 1
and idEstatus = 3
)
set @f1 =
(
select
COUNT(idEstatus) A_D
from t1
where (year(dat)) = 2017 and (month(dat)) = 1
and idEstatus = 2
)
set @f2 =
(
COUNT(idEstatus) A_D
from t1
where (year(dat)) = 2017 and (month(dat)) = 1
and idEstatus = 3
)

select
 (((@a + @d)/2) *100)/((@f1 + @f2)/ 2) rf

If also some can tell me how to order this by a UNION , that could be really usefull to.


texts: Searching for keywords in fulltext indexes using the "contains" function

texts:
Lets assume "and" is not a stopword, in my language it should not be.

How do I search for it using the contains function?

It is a keyword, but how do I escape the keywordness?

No luck with:

SELECT pc.SearchData, from PageContent pc
where contains(pc.SearchData, 'AND')

(got no search hits) or

SELECT pc.SearchData, from PageContent pc
where contains(pc.SearchData, '[AND]')

(this was giving me an exception)...


texts: Extract street address from string with multiple delimiters and positions

texts:
Trying to pull just the Street addresses out of this data:

CREATE TABLE foo(Places varchar(50));
INSERT foo(Places) VALUES
('MARKET @123 NORTH RD'),
('HARDWARE @600 Main RD ;W1'),
('MAIN AV / NORTH RD'),
('500 NORTH RD @LIBRARY'),
('500 ANYSTREET ; *** SIDEWALK****'),
('MARKET @123 NORTH RD'),
('700 ANYSTREET'),
('(088.12345,088.12345) ;75 SOUTH RD');

I've trying to do use CASE, SUBSTRING and (CHAR/PAT)INDEX... But I'm just not doing this correctly.

Select
[Address] = CASE WHEN PATINDEX('%@[0-9]%' , Places) = 0 THEN     (SUBSTRING(Places, 0, CHARINDEX('@',Places)))
                WHEN PATINDEX('%@[a-z]%' , Places) = 0 THEN     (SUBSTRING(Places, CHARINDEX('@',Places) + 1, LEN(Places)))
                WHEN PATINDEX('%;[0-9]%' , Places) = 0 THEN (SUBSTRING(Places, CHARINDEX(';',Places) + 1, LEN(Places)))
                WHEN PATINDEX('[0-9]%' , Places) = 1 THEN (SUBSTRING(Places, 0, CHARINDEX(';',Places)))
                WHEN PATINDEX('%/%' , Places) > 0 THEN Places END

From dbo.foo

What is the better way to grab that data? I've looked at string selects for finding email addresses, but seem to fall flat again when dealing with the ';'

TIA!

edit for desired output, table containing:

123 NORTH RD
600 Main RD
MAIN AV / NORTH RD
500 NORTH RD
500 ANYSTREET
123 NORTH RD
700 ANYSTREET
7575 SOUTH RD


texts: What causes lost updates being observed in my SQL Azure database?

texts:
I have a SQL Azure database with the following tables:

CREATE TABLE [dbo].[UserBalances]
  (
     [UserId]         UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID(),
     [AvailableMoney] INT NOT NULL DEFAULT(0)
  );

GO

CREATE UNIQUE CLUSTERED INDEX [UserBalancesIndex]
  ON [dbo].[UserBalances]([UserId] ASC);

GO

CREATE TABLE [dbo].[UserBalanceChanges]
  (
     [EntryId]      UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
     [UserId]       UNIQUEIDENTIFIER NOT NULL,
     [PriorBalance] INT NOT NULL,
     [NewBalance]   INT NOT NULL,
     [Time]         DATETIME DEFAULT(GETUTCDATE())
  )

GO

CREATE CLUSTERED INDEX UserBalanceChangesIndex
  ON [UserBalanceChanges]( EntryId )

GO

and the following code being concurrently executed via several connections the database:

CREATE PROCEDURE [dbo].[usp_ChangeUserBalance] @userId UNIQUEIDENTIFIER,
                                               @change INT
AS
    BEGIN TRANSACTION

    DECLARE @priorBalance INT;
    DECLARE @newBalance INT;
    DECLARE @updateTime DATETIME;

    UPDATE dbo.UserBalances
    SET    @updateTime = GETUTCDATE(),
           @priorBalance = AvailableMoney,
           @newBalance = AvailableMoney = AvailableMoney + @change
    WHERE  UserId = @userId;

    INSERT INTO UserBalanceChanges
                (UserId,
                 PriorBalance,
                 NewBalance,
                 Time)
    VALUES     ( @userId,
                 @priorBalance,
                 @newBalance,
                 @updateTime );

    COMMIT TRANSACTION

    RETURN 0

and then I run the following query:

SELECT TOP(1000) PriorBalance,
                 NewBalance
FROM   UserBalanceChanges
WHERE  UserId = SomeSpecificId
ORDER  BY Time DESC

and I often see something like this:

1000 995
1005 1000 <<identical
1005 1000 <<changes
1010 1005
1015 1010

which looks like the two concurrently running updates read the same initial value and then one update is effectively lost.

Lost updates are a known anomaly but lost updates are impossible in SQL Server and presumably in SQL Azure too (at least with a single "SELECT from UPDATE" statement that I have.

Yet it looks like I actually see lost updates.

Why do I observe "impossible" lost updates?


texts:
Can you explain me the bold part?

    select   company_code,
             founder,
             (select count(distinct lead_manager_code)
              from Lead_Manager
              where  company_code = c.company_code
             )
    from     Company c
    order by company_code;


texts: Why does SQL Server run a subquery for each row of the table it's qualifying?

texts:
This query runs in ~21 seconds (execution plan):

select
    a.month
    , count(*)
from SubqueryTest a
where a.year = (select max(b.year) from SubqueryTest b)
group by a.month

When the subquery is replaced with a variable, it runs in <1 second (execution plan):

declare @year float
select @year = max(b.year) from SubqueryTest b
select
    month
    , count(*)
from SubqueryTest where year = @year group by month

Judging from the execution plan, the "select max..." sub-select is run for each of the millions of rows in "SubqueryTest a:, which is why it takes so long.

My question: Since the sub-select is scalar, deterministic and not correlated, why doesn't the query optimizer do what I did in my second example and run the subquery once, store the result, then use it for the main query?  I'm sure there's just a hole in my understanding of SQL Server, but I'd really like help filling it - a couple hours with google haven't helped.

The table is just over 1gb with almost 28 million records:

CREATE TABLE SubqueryTest(
  [pk_id] [int] IDENTITY(1,1) NOT NULL
  , [Year] [float] NULL
  , [Month] [float] NULL PRIMARY KEY CLUSTERED ([pk_id] ASC))

CREATE NONCLUSTERED INDEX idxSubqueryTest ON SubqueryTest ([Year] ASC)


texts:
I have one application using SQL Server Standard and would like the change to Premium, but I need to understand about the DTU. The application reaches 100% in DTU every day.

Would talk know if I need to buy Premium with more or equal DTU in Premium or with less DTU for being Premium is enough?

For example, I have the Standard 1600 DTU (S9) and would change to Premium 1000 DTU (P6), would lose the performance this?

Thanks for attention.


texts:
we just upgraded from Oracle 11g SE to Oracle 12c SE.  Now, when I try to compile this one package that references things over the DB Link to another 12c DB, it just sits there and hangs.   When I check v$session_wait, I just see this:   "SQL*Net message from dblink".
How can I tell what is causing the actual wait?


texts:
I created an Oracle's container database (CDB) at CentOS named demo0, then I also created an user named c##demo.

The issue is that the dabase is not showed at using ORACLE_SID=demo0 but it's only showed with ORACLE_SID=ORCLCDB:

[oracle@centos-8 ~]$ echo $ORACLE_SID
ORCLCDB
[oracle@centos-8 ~]$ sqlplus sys as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Jun 13 09:00:43 2020
Version 18.3.0.0.0

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

Enter password:

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> select name, open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB$SEED
READ ONLY

ORCLPDB1
MOUNTED

DEMO0
MOUNTED

SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

[oracle@centos-8 ~]$ export ORACLE_SID=demo0
[oracle@centos-8 ~]$ echo $ORACLE_SID
demo0
[oracle@centos-8 ~]$ sqlplus sys as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Jun 13 09:01:24 2020
Version 18.3.0.0.0

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

Enter password:

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> select name, open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB$SEED
READ ONLY


texts:
I have these tables:

DROP TABLE IF EXISTS books;
CREATE TABLE `books` (
  `bookId` mediumint(8) UNSIGNED NOT NULL,
  `title` varchar(10) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `books`
  ADD PRIMARY KEY (`bookId`),

DROP TABLE IF EXISTS movements;
CREATE TABLE `movements` (
  `movementId` mediumint(8) UNSIGNED NOT NULL,
  `movementTypeId` tinyint(3) UNSIGNED NOT NULL,
  `deletedFlag` tinyint(3) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `movements`
  ADD PRIMARY KEY (`movementId`),
  ADD KEY `movementId` (`movementTypeId`,`deletedFlag`) USING BTREE;

DROP TABLE IF EXISTS movements_types;
CREATE TABLE `movements_types` (
  `movementTypeId` mediumint(8) UNSIGNED NOT NULL,
  `title` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `movements_types`
  ADD PRIMARY KEY (`movementTypeId`);

DROP TABLE IF EXISTS movements_books;
CREATE TABLE `movements_books` (
  `movementId` mediumint(8) UNSIGNED NOT NULL,
  `bookId` mediumint(8) UNSIGNED NOT NULL,
  `bookSize` tinyint(3) UNSIGNED NOT NULL,
  `quantity` smallint(5) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `books` (`bookId`, `title`) VALUES
(1, 'Harry Potter'),
(2, 'Mysql Join'),
(3, 'Comedy');

INSERT INTO `movements` (`movementId`, `movementTypeId`, `deletedFlag`) VALUES
(7, 1, 0),
(8, 2, 0),
(9, 2, 0);

INSERT INTO `movements_types` (`movementTypeId`, `title`) VALUES
(1, "Bought"),
(2, "Sold");

INSERT INTO `movements_books` (`movementId`, `bookId`, `bookSize`, `quantity`) VALUES
(7, 1, 1, 3),
(7, 1, 2, 3),
(7, 2, 1, 3),
(7, 2, 2, 3),
(8, 1, 1, 2),
(8, 1, 1, 2),
(9, 2, 1, 3);

bookSize is just an integer indicating the size of the book.

movements_books actually has 111824 records, movements has 4534.
deletedFlag is 1 for a deleted movement (I prefer to keep them flagged) and 0 otherwise.

I need help creating indexes and I need these results:

1) sizes sold/bought/available for each book (bookId, bookSize, booksSold, booksBought, booksBought minus booksSold)

2) books sold/bought/available for each book of all size (bookId, booksSold, booksBought, booksBought minus booksSold)

To get the one of the statistics I tried:

SELECT
   books.title
   movements_books_grouped.bookId,
   SUM(IF(movements.movementTypeId=1, movements_books_grouped.quantity, NULL)) AS booksBought,
   SUM(IF(movements.movementTypeId=2, movements_books_grouped.quantity, NULL)) AS booksSold,
   (
       SUM(IF(movements.movementTypeId=1, movements_books_grouped.quantity, 0))-
       SUM(IF(movements.movementTypeId=2, movements_books_grouped.quantity, 0))
   ) AS booksAvailability
   FROM
   (
   SELECT bookId,quantity,movementId FROM movements_books GROUP BY bookId
   ) AS movements_books_grouped
   JOIN movements ON movements.movementId=movements_books_grouped.movementId
   JOIN books ON books.bookId=movements_books_grouped.bookId
   GROUP BY movements_books_grouped.bookId
   ORDER BY books.title

but it's very slow.
Edit: I needed to add another table to the example because that's the one that makes really slow the last query. I need to join to this table because I need to order the result by title.


texts: How to transfer data using expdp and impdp commands?

texts:
I'm an Oracle noob, and my intention is to transfer all data and metadata from one schema to another schema within an Oracle database. I'm planning to use datapump's expdp and impdp commands. I have questions regarding this:

Can I create a target schema without a user or should I create a user first (which creates a schema also)?
Can I execute expdp and impdp commands using SYS (as sysdba)
account? Is that a preferred method?
Does this statement take all the objects (data and metadata) from a schema and move these into a different schema?

expdp \"/ as sysdba\" schemas=<schemaname> directory=dumpdir dumpfile=<schemaname>.dmp logfile=expdp_<schemaname>.log

So is the target schema an exact copy of the source schema after impdp command?


texts: Backup-strategy for redundant data in postgresql-db.

texts:
I have a (postgresql-9.3)-table that contains images as a bytea-column as well as several other columns that contain extracted information from these images. (Some feature points, descriptors). These values are currently stored in the DB because they are computationally rather expensive, so that I want to cache them in the DB.

However, I do not want to integrate these columns in the backup (or the WAL files) as they are rather large. Is there a way to do this or should I rather try another approach (as e.g. writing the additional data to the filesystem)?


texts: SQL Server - create user for a local Windows login from another instance (AlwaysOn)

texts:
I have two SQL Server instances working as the AlwaysOn AG. There is a Windows login in each instance created from a local Windows account and mapped to respective users in the AG databases. Say I'm adding a new database to the AG.
Is there a way  to pre-provision a user mapped to the local Windows login from another (secondary) node without having to failover the AG - so that when the actual failover occurs, that user is fully operational?


texts: Stopping N number of nodes of a Cassandra cluster running on a single machine using bash script

texts:
I have set up a Multi-Node cassandra (3.11) cluster (with N nodes) in a single Ubuntu machine. I have written a bash script which

Creates N copies of conf folder
Configures the cassandra yaml file according to the need of N nodes (Such as separating the data and log directories)
Creates N copies of cassandra script inside the bin folder

It works correctly so far. But I am a little stuck after that.

I want to stop all the nodes effectively at the end of the script. Any help will be really appreciated at this point.

Thanks in advance!