texts: Cannot figure out how to remove this Using Temporary

texts:
I have what seems like a simple query, and my indexes seem to be sufficient without the INNER JOIN, but after I add the join, I end up seeing "Using Temporary". Would also appreciate any good resources for learning more about query optimization!

Query:

SELECT SQL_CALC_FOUND_ROWS DISTINCT m.id
FROM messages m
INNER JOIN message_entries t2
    ON m.id = t2.message_id
WHERE m.user_id = 1234
    AND m.type = 1
ORDER BY m.sent_on DESC;

The messages table has a covering index for (user_id, type, sent_on, created) and it uses this index for the query, and the message_entries table has an index on message_id (through foreign key).

I've tried adding id to the covering index on the messages table (at the beginning or end of the index, i've tried both), but it still chooses to use the original index for user_id, type, sent_on, created.

What can I do to improve performance when adding joins to the base query?


texts: IntelliSense alternative using SSMS 2012 in a contained database as a contained user?

texts:
According to this answer about contained database disadvantages:

  If you connect to a contained database as a contained user, SSMS will
  not fully support IntelliSense. You'll get basic underlining for
  syntax errors, but no auto-complete lists or tooltips and all the fun
  stuff. I filed a bug about this issue, and it remains open.

So is there any way to get IntelliSense-style functionality? Can anyone vouch for a product that will work in this scenario?


texts: SQL Server Always on DBCC Check DB and Backups startegey

texts:
Can we run DBCC CheckDB on secondary and perform our database backups with checksum on Primary.
This will make sure if corruption happens on primary, backup will fail and Primary will use sys.dm_hadr_auto_page_repair to repair page from secondary node
Also running CheckDB on Secondary node will always keep DBCC related resource utilization on secondary only.


texts: A Non-DBA Asks: How to Painlessly Copy/Move a SQL Server instance to another Server via Backup/Restore?

texts:
We've just got a new server so I need to copy the existing SQL Server 2008 instance over from the old db box.

I normally do this by copying .mdf and log files over and attaching them but I'm not able to do take the dbs off line as they're used 24/7 so I've backed up the databases and restored them on the new machine. However I've ended up with a few issues one of them being related to orphaned users. So what I'm looking for is a fail-safe process to move everything across with minimal fuss/bother/headache. I'm a .Net dev with a fair amount of SQL under my belt but the inner workings of SQL Server are largely a mystery to me and I find the MS documentation painful to trawl.

Please help.


texts:
I have logged into postgresql server using

sudo -u postgres psql

Then I also created a database from within /usr/local/bin directory using the command.

createdb -h localhost -p 5432 -U postgres testdb

When prompted for password, I entered the PostgreSQL admin user password (that I had changed from 'postgres' to 'upendra').

When I do

\l

in the postgres prompt, I get the following output

When I connect to testdb from within the postgres prompt, I am not required to enter the password. the connection is instantaneous.

However, when I try to connect to this database from the terminal using

psql -h localhost -p 5432 postgres testdb

I am required to enter password. Why is it so?Moreover, no matter what password I type it always says that authentication failed. what's the catch?

Also, why does the prompt say password authentication failed for user "testdb". From what I have understood, testdb is the name of the database and not the name of the user. The user  name is postgres.


texts: How do I change table used in from clause of a select based on udf parameter?

texts:
I have two tables A and B (with exactly the same columns and overall structure) and I'm trying to create a TVF with a select inside it that chooses to use A or B based on a parameter passed to the TVF:

create function dbo.testing(@switch varchar(1))
returns @SomeData table (data int)
  begin
    insert into @SomeData
    select
      number
    from
      [[either A or B depending on the value of @switch]] <===== ???
  return
 end;

The arrow coming in from the right indicates the line I"m not sure how to handle.

What's the right way to do this?  I know I can't just slap a string in there so what's the best way to proceed?


texts: Updating a SQL Server Extended Event session to add data storage - is it possible?

texts:
I have an extended event session to track deadlocks, and have the data storage set up for Event_File, max file size of 20 MB, max number of files is 5, enable file rollover is true.
It looks like I can't change the data storage, even after I stop the session. It's all grayed out. I need to be able to increase the number of files, because for some reason it's not actually going to the max file size of 20 MB. I have 4 files already for this session, and the largest size is 47 KB.
How can I increase this? Do I just need to re-create the session? I already have history in it that I want to keep.


texts: When should the IGNORE_DUP_KEY option be used on an index?

texts:
Someone said it's preferable to craft your queries to avoid duplicate key exceptions, but I'm not convinced that's more performant that just setting IGNORE_DUP_KEY = ON for the index.

My goal is to ensure a row or set of rows exists for one or more users, before attempting to update those rows. I do this, so that when I attempt to update the row with an update statement like the one below, and no rows are affected, it's because the [Count] portion of predicate wasn't satisfied, as opposed to the row not existing at all (i.e. the [ID] portion of the predicate not being satisfied):

UPDATE [Inventory]
SET [Count] = [Count] + 1
WHERE [ID] = 3
AND ([Count] + 1) <= @MaxInventory

I could run EXISTS(SELECT 1 From [Inventory] WHERE [ID] = 3 to check for that single row, and only insert the row if it does not exist. That simply avoids unnecessary inserts. The insert, if necessary, would still have to contend with concurrent transactions, so duplicate key exceptions can still occur.

I'm curious whether it's more performant to just turn on IGNORE_DUP_KEY in this scenario, rather than allowing the error to be thrown and caught. Specifically, I'm curious if it's as-fast or possibly even faster than running an exists check, to just attempt to insert the record and let it ignore duplicate keys.

This becomes even more important, when I'm checking for and initializing multiple records at once. For example, if I need to ensure records for thousands of users exist in a single update statement, the logic would be much simpler if I just ran that insert statement up-front, letting it ignore duplicate keys. Avoiding duplicates would be more complex, because I'd have to first query the table for which records don't exist, then attempt to add just those records (again, ignoring duplicate keys). Just inserting may be faster, even if all the records exist.

I could meet it halfway and check whether any of the records are missing, such as with a left join or a COUNT comparison, but why bother if the insert ignoring duplicate keys is just faster?

Is is a good idea to use IGNORE_DUP_KEY and just attempt inserts instead of bothering with checking for row existence ahead of time? If not, why?


texts: Mysql import csv not complete by Mysql bench

texts: I upload the csv file which has more than 5 thousand rows  table data for option import wizard by Mysql Workbench. As the result the output table only 335 rows, have no idea how to overcome the limitation.


texts: Is sslmode=verify-full enough to connect securely to postgresql?

texts:
I'm using Amazon's postgresql RDS service to run a few shared development/testing databases. I'm connecting to it via something like:

 psql " dbname=postgres user=myrootuser sslrootcert=rds-combined-ca-bundle.pem sslmode=verify-full host=myhostname.asdfqwer.us-west-2.rds.amazonaws.com"

The password is in a pgpass file which no one else should be able to read.

Is this enough to connect securely to the database? Or are there vulnerabilities that can, say, downgrade the connection to plain text or something that I should be aware of?

(I know it would probably be wise to put the DB behind a VPC/firewall not make it globally accessible, but that's not practical for us at the moment).


texts:
I want to monitor accessNotInMemory of mongodb, before I can use

db.adminCommand({'serverStatus': 1})['recordStats']

but according to mongodb doc,

  Changed in version 3.0: serverStatus no longer outputs the workingSet, indexCounters, and recordStats sections.

how can I get the recordStats in mongodb 3.2?


texts: Is there a way to improve this query further?

texts:
I've got a query that over time I've been improving. Originally it simply selected * from the records table, and the data was formatted on the webapp side. But that was slow and I wanted to make things faster. I found that I could format the output into the correct json in postgres very quickly but the query was UGLY as sin. Then postgres 9.4 came out and brought json_object_agg which made the query nice and simple, and even faster than before, and all I had to do was change the data type of the data column to be json (it had been storing json-formatted data anyway).

The current version of the query is:

select json_object_agg(day,data order by day) as jsobj from records

The records table is:

\d records
                         Table "public.records"
 Column |  Type   |                      Modifiers
--------+---------+------------------------------------------------------
 id     | integer | not null default nextval('records_id_seq'::regclass)
 day    | date    | not null
 data   | json    | not null
Indexes:
    "records_pkey" PRIMARY KEY, btree (id)
    "records_day_key" UNIQUE CONSTRAINT, btree (day)

What I'm wondering is this: is there a way to improve the query further? Either making it more readable or more performant?

I think that because I never look inside the value of data and simply operate on it as a whole (simply inserting it into more json which can be done with blind inlining), that the json datatype is still faster.

The syntax for ordering within an aggregate function is weird, but helpful.


texts: Availability Group how to determine last Failover time

texts:
I'd just like to know if there is any way to query when the AG group failed over.

eg - this is the Primary Replica now, but I'm pretty sure it was the secondary yesterday?  How can i find when the failover took place

is there something specific in the Logs I should be looking for, or is there a tsql script to use?


texts:
Currently my team develop additional web app to let customers analyze user statistics to theirs portals.

So we have a table with all logins. It contains about 100 millions of rows. One user could return to the portal during the day multiple times.

This table structure something like:

| UserId | LoginDate |

Also we have a hierarchy of customers, portals and countries, it looks like:

Users           | Customers      | Portals         | Countries
FK-> CustomerId
                  FK -> PortalId
                                   FK -> CountryId

Base on user activity we are building daily incremental snapshots to prevent using big login history table. And we store there Total Logins, Unique logins etc. This allows us show nice chart to the end users.

But now we are facing on interesting issue with this. Currently we need implement total Unique login for period. So the user select period, and see this total.

And this cause the story goes on. We can't just sum snapshots total Unique logins, it won't be a real unique. And we can't count each time from history table, because of performance. And I don't have idea how to cache totals.


texts: connection closes unexpectedly with different kind of errors

texts:
When I execute a query which returns more than 300 rows I’m getting random errors and the connection closes for example

  An error occurred while executing batch. Error message is: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Or

An error occurred while executing batch. Error message is: Internal connection fatal error. Error state: 15, Token : 73

Or

An error occurred while executing batch. Error message is: Internal connection fatal error. Error state: 15, Token : 196

And it only happens when I’m logged in in my computer (windows 7) with my profile, but when I’m logged in in a different computer with my profile or a another user in my computer it does work

Any Help??


texts: self referencing tables with SQL and entity framework

texts:
I have many tables in a database and the tables are nearly identical. There are limitations currently on what objects reference other objects. For example I have company,contact and project. Contact is a child of company but no relation to project although some of our users would like there to be.

Since the tables are all similar I wondered if it would be a good idea to create a self referencing table with a type property on each record. Each record in the table would be a specific type ie company,contact etc and have X number of foreign key fields which would reference the same table.

This gives our users the ability to create their own record types and link them however they choose.

Is this going to cause me any problems in the future that I haven't thought of? Our system allows users to create their own fields on an object so this seems perfect. But they can't also report on this data. Performance wise I am thinking this should be OK as data would only ever be in 1 table.


texts: db2 SQL0930N error on linux - not enough space to process this statement

texts:
This is another newbie question on db2, related to space.
How to I start diagnosing and then solving what the problem is so that users can connect to this database?
This error is displayed when both connecting to an existing database as well as when creating a new database.

db2 => list database directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = SAMPLE
 Database name                        = SAMPLE
 Local database directory             = /home/mylogin/sqllib
 Database release level               = f.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

db2 => connect to sample
SQL0930N  There is not enough storage available to process the statement.
SQLSTATE=57011
db2 => !uname -a
Linux myhostname 2.6.32-131.0.15.el6.x86_64 #1 SMP Tue May 10 15:42:40 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux
db2 => create database newdb
SQL0930N  There is not enough storage available to process the statement.
SQLSTATE=57011
db2 =>


texts: SQL Server TDE Vs SQL Server Database Backups

texts:
Can anyone please help me understand below case:

Kindly advise on below case:

SQL Server is on 2014 SP2 GDR version

I read Compression and TDE does not go hand in hand .

I have 2 PRD Environments:

The table structures between server 1 and Server 2 are same.

With DB Size around 400 GB in Server 1 with FileStream Enabled.
FileStream content sized around 300 GB.
With DB Size around 687 GB in Server 2 with FileStream Enabled.
FileStream content sized around 600 GB

Currently the above Databases are with TDE Enabled.

When I backed up below are the details when they are NOT on TDE:

With DB Size around 400 GB in Server 1 took around 51 Minutes

With DB Size around 687 GB in Server 2 took around 108 Minutes

When I backed up below are the details when they are WITH TDE:

With DB Size around 400 GB in Server 1 took around 57 Minutes

With DB Size around 687 GB in Server 2 took around 207 Minutes

My Observations

In Server 1 with same specifications as Server 2 , time taken is 51 Minutes (Pre TDE) --> 57 Minutes after TDE Enabled

In Server 2 with same specifications as Server 1 , time taken is 108 Minutes (Pre TDE )--> 207 Minutes after TDE Enabled

My Question
Why is that difference when compared to time taken is almost doubled in Server 2 but Server 1 it is only 7 Minutes .

NOTE: Also , my Databases are with TDE + EFS (Encrypting File System) when backed up.

Kindly advise , as Compression and TDE does not go hand in hand . What is the reason why in Server 2 the time taken is DOUBLE , where as in Server 1 it is only 7 Minutes after TDE enabled.

Regards


texts: Mysql settings for query_cache_min_res_unit

texts:
What is the best setting for query_cache_min_res_unit for these results:

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 35327     |
| Qcache_free_memory      | 295242976 |
| Qcache_hits             | 236913188 |
| Qcache_inserts          | 49557287  |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 7128902   |
| Qcache_queries_in_cache | 195659    |
| Qcache_total_blocks     | 426870    |
+-------------------------+-----------+

Do I need to change any other settings?

My website creates very large results. This is the current setting:

query_cache_min_res_unit = 4096

Info on the mysql dev website

If most of your queries have large results (check the Qcache_total_blocks and Qcache_queries_in_cache status variables), you can increase performance by increasing query_cache_min_res_unit. However, be careful to not make it too large (see the previous item).


texts: How to copy part of a database with inconsistent privileges?

texts:
I have a Postgres database and I would like to copy certain tables from a single schema into a new empty database.

So far I have copied a schema-only dump from from the old DB and applied it to a new empty DB.

I have also created a data-only dump of the specific tables I want using -t [table-name].

Unfortunately the tables are owned by different roles, so when I try to restore the DB or modify the schema prior to data loading, I am getting all kinds of errors.

My guess is that I need to do some kind of check/modification of roles prior to creating the dumps.

What is the correct process for duplicating part of a DB including roles? - keeping in mind tables could have different owners.


texts: Querying for multiple values when an error message is present in AWS Timestream

texts:
I am using AWS Timestream so the DB structure is a bit different than normal but effectively it fairly similar to a normal structure.
What I need to do:
When an error message of a certain type (Ie measure_name = "DriveError") I would like to query that error message and the last value of of several other measures all of which will have a different measure_name. The point of this is to help quickly view my systems state when that error occurred. I am using this to watch a machines in a factory so the values are not server related but one could basically view an example as :
*An error occurs,
report in a table:
the error,
the the cpu temp,
the cpu utilization,
the fan speed,
ect.
*
The following would be the normal way I could select a error message
SELECT time, measure_value::varchar AS error
from "Main"."Machine1"
where measure_name = 'DriveError'
ORDER BY
    time DESC

But in this case what I will need to is select the last value of all my drive parameters (again each has its own measure_name) when there is measure_value::varchar with measure_name = DriveError.


texts: Categorize age ranges and display in one column

texts:
I want to see the data as follows

Member   Age  Category
Test1    25    Under 30
Test2    55    51-60
Test3    67    Above 60

I have used the below query (from an answer to my previous question) to calculate the age from DOB and current date and then categorize each age under one name.

This query is creating separate columns, instead I want to see all the categories under one column so that I can group this category column

WITH AgeData as
(
SELECT
    Username,
    Birthdate,
    DATEDIFF(YEAR, birthdate, GETDATE()) AS AGE
FROM @Table)
SELECT
    Username,
    Birthdate,
    Age,
    CASE
        WHEN AGE < 30 THEN 1
        ELSE 0
    END AS 'Under 30',
    CASE
        WHEN AGE BETWEEN 31 AND 40 THEN 1
        ELSE 0
    END AS '31 - 40',
    CASE
        WHEN AGE BETWEEN 41 AND 50 THEN 1
        ELSE 0
    END AS '41 - 50',
    CASE
        WHEN AGE > 50 THEN 1
        ELSE 0
    END AS 'Over 50'
FROM AgeData


texts:
I was able to set up a replica set with self signed certificates on Windows. However, I suspect there are some communication issues between primary and secondary.

When I look at the log, I see these errors several times per second:

"2016-04-10T17:04:06.502Z E NETWORK  [conn321670] no SSL certificate provided by peer; connection rejected",
"2016-04-10T17:04:06.503Z I NETWORK  [conn321670] end connection xxx.xxx.xxx.xxx:54350 (12 connections now open)"

xxx.xxx.xxx.xxx is the secondary IP number.

Now, that's weird to me because I'm running the service on port 40000, so I don't know why these connections are using other port numbers. Those ports are not even opened in my firewall.

I think the certificate rejection has something to do with the fact that my certificates were created based on host names (m1.mydomain.com and m2.mydomain.com) and these messages show IP numbers.

rs.status() says that everything is fine.

Here's my config file:

storage:
   dbPath: c:\mongossl\data
systemLog:
   destination: file
   path: c:\mongossl\log\mongod.log
   logAppend: true
   timeStampFormat: iso8601-utc
replication:
   replSetName: rs
net:
   port: 40000
ssl:
    mode: requireSSL
    PEMKeyFile: c:\certs\m1.pem
    CAFile: c:\certs\ca.crt
    clusterFile: c:\certs\m1.pem
security:
   authorization: disabled
   clusterAuthMode: x509

It would be great if someone could shed some light on why I'm seeing all these connection errors.


texts: weight of replication on SQL Server 2008 R2 instance

texts: How can I see what impact replication is having on a server/instance, replication vs all other things.


texts: How to best manage Index Builds with Stats Updates?

texts:
This is a follow-up to what I read on Is there any reason to stop transaction log backups during a maintenance window? in the answer proposed by sp_BlitzErik.

I use Ola Hallengren's indexing script and have the settings specified as per the below. I run this once a week through an Agent job.

@Databases nvarchar(max),
@FragmentationLow nvarchar(max) = NULL,
@FragmentationMedium nvarchar(max) = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh nvarchar(max) = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 int = 5,
@FragmentationLevel2 int = 30,
@PageCountLevel int = 500,

I know as apart of an Index Re-build an update stats is done automatically, but I have the following database properties set against all my databases which I believe should take care of updating stats:

Auto Create Statistics = True
Auto Update Statistics = True
Auto Update Statistics Asynchronously = True

What is generally best practice here for these settings and regularly updating stats though? Should you update stats on a nightly basis? I'm not sure how to measure whether stats should be updated or not which is why I have those database properties set.

I see in the answer by sp_BlitzErik he mentions "You're still going to want to update stats regularly though, and you can do that with this command:"

with the following command, but update stats regularly is very general.

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
                          @FragmentationLow = NULL,
                          @FragmentationMedium = NULL,
                          @FragmentationHigh = NULL,
                          @UpdateStatistics = 'ALL',
                          @OnlyModifiedStatistics = 'Y',
                          @StatisticsSample = NULL,
                          @LogToTable = 'Y';


texts: Should the index on an identity column be nonclustered?

texts:
For a table with identity column, should a clustered or non-clustered PK/unique index be created for the identity column?

The reason is other indexes will be created for queries. A query which uses a nonclustered index (on a heap) and returns columns that are not covered by the index will use less logical I/O (LIO) because there are no extra clustered index b-tree seek steps?

create table T (
  Id int identity(1,1) primary key, -- clustered or non-clustered? (surrogate key, may be used to join another table)
  A .... -- A, B, C have mixed data type of int, date, varchar, float, money, ....
  B ....
  C ....
  ....)

create index ix_A on T (A)
create index ix_..... -- Many indexes can be created for queries

-- Common query is query on A, B, C, ....
select A, B
from T
where A between @a and @a+5 -- This query will have less LIO if the PK is non-clustered (seek)

select A, B, C
from T
where B between @a and @a+5

....

Clustered PK on identity column is good because:

It increase monotonously so no page splits when inserting. It's said a bulk insert can be as fast as on a heap (nonclustered) table
It's narrow

However, will the queries in the question be faster without setting it clustered?

** Update:**
What if the Id is the FK of other tables and it will be joined in some queries?


texts:
Mysql became slow after a while until I reboot, it's fast again, then gets slow after a while.

This started after I accidentally deleted everything in /var/log.
Mysql and mariadb wouldn't start at all after that but I managed to recreate some of the logs and it works but it gets slow after a while.


texts: Follow up to certificate sharing question

texts:
Follow up to the post Sharing certificates encryped by password between DBs and instances

I created a certificate with a password

CREATE CERTIFICATE testcert
ENCRYPTION BY PASSWORD = 'test123'
WITH SUBJECT = 'Certificate for stored procedures using dynamic SQL',
START_DATE = '2010-01-01',
EXPIRY_DATE = '2100-01-01'

I then removed the private key

ALTER CERTIFICATE testcert REMOVE PRIVATE KEY

Finally, I attempt to backup the certificate

BACKUP CERTIFICATE testcert TO FILE = 'd:\mssql\testcert.cer'
WITH PRIVATE KEY ( DECRYPTION BY PASSWORD = 'S3creT!' ,
FILE = 'd:\mssql\testcert.pvk' ,
ENCRYPTION BY PASSWORD = 'test123' );

And I get this error:

  Msg 15246, Level 16, State 1, Line 1 Cannot dump the private key of
  certificate 'testcert' because the private key cannot be found.

Which seems normal since I dropped the private key.

1st Question: Using the method explained in the previous post, does this mean that to backup the certificate I would use this statement?

BACKUP CERTIFICATE testcert TO FILE = 'd:\mssql\testcert.cer'

2nd Question: What would you do to then to restore the certificate to another server and database?

Example :

CREATE CERTIFICATE testcert2
ENCRYPTION BY PASSWORD = 'test123',
FROM FILE = 'd:\mssql\testcert.cer'
WITH PRIVATE KEY ....

Thanks.


texts: Understanding BinLogDiskUsage Metric behavior

texts:
We are using AWS-RDS. The following is the screenshot of how the binary log disk usage has varied over the last 6 weeks.

The log retention period is 3 days. Tables are optimized and archived once a week. We were expecting the BinLogDiskUsage to be almost constant(assuming periodic purging of logs by AWS ) with spikes during archival tasks.

Can anyone explain this wavy nature of the graph?


texts: Availability Group of SQL Servers 2012, 2012 and 2016 possible?

texts:
We have two SQL servers combined into Availability Group in primary data center (dc1):

dc1 server 1: SQL Server 2012 SP3 on Windows 2016
dc1 server 2: SQL Server 2012 SP3 on Windows 2012 R2

We are going to open new data center (dc2) which is going eventually to be new primary data center
In dc2 there is going to be third SQL server

dc2 server 3: SQL Server 2016 (or 2012) on Windows 2016

We would like to have SQL Server 2016 on server 3, but here are my questions:

1) --> What are potential problems if we add SQL Server 2016 as a third node to existing Availability Group that has two SQL Servers 2012?

2) --> Will AG be working properly or ? If you have any practical experience of doing so, please share

3) --> Do we really need to deploy server 3 as SQL Server 2012 (instead of 2016) in order for AG to be working ?

4) --> What are best options (aside of AG) to keep SQL servers 1, 2, and 3 in sync if we chose to deploy server 3 as SQL Server 2016 ? is that transactional replication

Thanks in advance!


texts: Msg 2627 Violation of PRIMARY KEY constraint

texts:
trying to make a database for teams in a tv show here. but when I try and insert data into tblShowteam the following error made its appearance.

Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK__tblShowt__F693078C03317E3D'. Cannot insert duplicate key in object 'dbo.tblShowteam'.

tables

-- tabbellen aanmaken
create table tblShow(
setId       int,
Datum       date,
teams       int
primary key (setId));

create table tblShowteam(
SetId           int,
datum           date,
teams           int,
primary key (teams));

create table tblTeam(
TeamId      int,
Coach       varchar(35),
CoachId     int,
teams       int
primary key (CoachId));

-- participant table
create table tblDeelnemer(
DeelnemerId     int identity(1, 1),
DeelnemerV      varchar(35),
deelnemerT_V    varchar(10),
DeelnemerA      varchar(35),
CoachId         int,
datum_optreden  date
primary key (DeelnemerId));

--table for the public viewers
create table tblKijker(
Kijkerv         varchar(35),
KijkerT_V       varchar(10),
KijkerA         varchar(35),
Stoelnummer     int identity(1,3),
ShowId          int Not null,
Email           varchar(35)
primary key (Email));

my inserts would look like this:

insert into tblShowteam values (1, '2014-06-28', 1)
insert into tblShowteam values (2, '2014-06-05', 1)
insert into tblShowteam values (3, '2014-06-12', 1)
insert into tblShowteam values (4, '2014-06-19', 1)
insert into tblShowteam values (5, '2014-06-26', 1)
all other inserts (in diffrent tables) work like normal.

what am i doing wrong here?


texts: How to find out those users which have two different access right in postgresql

texts:
Thanks for your support and help,
Dear the scenario is like this i have 3 tables

1: users
2: users_groups
3: users_groups_rel

users data

id      name
1       zubair
2       ali
3       bob
4       john

users_groups data

   id    name
    1   report
    2   finance
    3   personnel

users_groups_rel data; it is associated table of users and users_groups

 uid    gid
    1   1
    1   2
    1   3
    2   3
    3   2
    4   2
    4   1

  Now i want to have those users which have access just to (Report +
  finance) When i am running this bellow query the result is incorrect,
  it shows those users which have id of (1 , 4) but i don’t need this i
  need that users which mentioned the exact access right for them.

  example:
  i need those users which have access to these access right
  (report and finance)

SELECT r.uid,u.name
   FROM users_groups_rel r left join users u on u.id=r.uid
   GROUP BY r.uid,u.name
   HAVING count(DISTINCT gid in (select id from users_groups where id=2)) = (SELECT count(DISTINCT gid in (select id from users_groups where id=1))


texts:
I'm trying to create the following schema, but I'm getting errors when I'm trying to create project and staffphone tables.

Not sure what is it complaining about.

mysql> CREATE TABLE staff(
-> StaffID INT AUTO_INCREMENT PRIMARY KEY,
-> FirstName VARCHAR(15) NOT NULL,
-> LastName VARCHAR(15) NOT NULL,
-> Address VARCHAR(30) ,
-> City VARCHAR(15) ,
-> State VARCHAR(3) ,
-> Position VARCHAR(20) NOT NULL,
-> Wage INT NOT NULL
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE projects (
-> StaffID INT NOT NULL,
-> ProjectName VARCHAR(20) NOT NULL,
-> Allocation INT NOT NULL,
-> PRIMARY KEY (StaffID, ProjectName) ,
-> FOREIGN KEY (StaffID) REFERENCES Staff(StaffID) ON DELETE CASCADE
-> );
ERROR 1005 (HY000): Can't create table 'staff.projects' (errno: 150)

mysql> CREATE TABLE staffphone (
-> StaffID INT NOT NULL,
-> PhoneNumber VARCHAR(15) NOT NULL,
-> PRIMARY KEY (StaffID, PhoneNumber) ,
-> FOREIGN KEY (StaffID) REFERENCES Staff(StaffID) ON DELETE CASCADE
-> );
ERROR 1005 (HY000): Can't create table 'staff.staffphone' (errno: 150)
mysql>

Any idea would be helpful.

Thank you


texts: Do BIT columns offer any performance advantages for CCIs?

texts:
Do BIT columns offer any performance advantages when used in Clustered Columnstore Indexes? I am interested in any performance benefits gained from defining a column in a CCI as BIT instead of BIGINT, for example. I'm working with SQL Server 2016.

I have a very limited understanding of how CCI compression works, but based on what I've read and some testing it seems like the data type (limited to exact numerics that store whole numbers) really shouldn't matter when it comes to columnstore compression. For example, if I insert 10 full rowgroups into tables with BIGINT columns as opposed to BIT columns I don't see a size difference between the compressed rowgroups. Here is the source data for one test:

DROP TABLE IF EXISTS dbo.CCI_BIT_TEST_SOURCE;

CREATE TABLE dbo.CCI_BIT_TEST_SOURCE (
    ID1 BIGINT NOT NULL,
    ID2 BIGINT NOT NULL,
    ID_BIT BIT NOT NULL,
    ID_BIGINT BIGINT NOT NULL,
    INDEX CCI__CCI_BIT_TEST_SOURCE CLUSTERED COLUMNSTORE
);

INSERT INTO dbo.CCI_BIT_TEST_SOURCE WITH (TABLOCK)
SELECT
  t.RN
, t.RN
, t.RN % 2
, t.RN % 2
FROM
(
    SELECT TOP (10485760) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
    CROSS JOIN master..spt_values t3
) t
OPTION (MAXDOP 1);

Inserting into a CCI with 8 BIT columns took an average of 18729 ms of CPU time.  The table has 56960 KB of reserved space:

DROP TABLE IF EXISTS dbo.CCI_BIT;

CREATE TABLE dbo.CCI_BIT (
    ID1 BIGINT NOT NULL,
    ID2 BIGINT NOT NULL,
    ID_BOOL_1 BIT NOT NULL,
    ID_BOOL_2 BIT NOT NULL,
    ID_BOOL_3 BIT NOT NULL,
    ID_BOOL_4 BIT NOT NULL,
    ID_BOOL_5 BIT NOT NULL,
    ID_BOOL_6 BIT NOT NULL,
    ID_BOOL_7 BIT NOT NULL,
    ID_BOOL_8 BIT NOT NULL,
    INDEX CCI__CCI_BIT CLUSTERED COLUMNSTORE
);

INSERT INTO dbo.CCI_BIT WITH (TABLOCK)
SELECT
  ID1
, ID2
, ID_BIT
, ID_BIT
, ID_BIT
, ID_BIT
, ID_BIT
, ID_BIT
, ID_BIT
, ID_BIT
FROM dbo.CCI_BIT_TEST_SOURCE
OPTION (MAXDOP 1);

Inserting into a CCI with 8 BIGINT columns took an average of 18531 ms of CPU time.  The table has 56960 KB of reserved space, the same as before:

DROP TABLE IF EXISTS dbo.CCI_NO_BIT;

CREATE TABLE dbo.CCI_NO_BIT (
    ID1 BIGINT NOT NULL,
    ID2 BIGINT NOT NULL,
    ID_BOOL_1 BIGINT NOT NULL,
    ID_BOOL_2 BIGINT NOT NULL,
    ID_BOOL_3 BIGINT NOT NULL,
    ID_BOOL_4 BIGINT NOT NULL,
    ID_BOOL_5 BIGINT NOT NULL,
    ID_BOOL_6 BIGINT NOT NULL,
    ID_BOOL_7 BIGINT NOT NULL,
    ID_BOOL_8 BIGINT NOT NULL,
    INDEX CCI__CCI_NO_BIT CLUSTERED COLUMNSTORE
);

INSERT INTO dbo.CCI_NO_BIT WITH (TABLOCK)
SELECT
  ID1
, ID2
, ID_BIGINT
, ID_BIGINT
, ID_BIGINT
, ID_BIGINT
, ID_BIGINT
, ID_BIGINT
, ID_BIGINT
, ID_BIGINT
FROM dbo.CCI_BIT_TEST_SOURCE
OPTION (MAXDOP 1);

We can see this in the columnstore DMVs as well:

There are some advantages to using BIT columns in CCIs. For example, data loaded into delta stores will take up less space with BIT columns because delta stores are basically uncompressed heaps. In query plans, the formula for the estimated data size is based on the data types of the columns as opposed to the size of the table on disk. The table with BIT columns has a total data size of 250 MB and the table with BIGINT columns has a total data size of 880 MB. In some cases the estimated size of 250 MB could lead to a better plan.

Are there any other performance benefits of BIT columns for CCIs? Or does the data type not really matter as long as you're using an exact numeric that stores whole numbers (BIT, TINYINT, SMALLINT, INT, or BIGINT)?


texts: raise an error in sql server severity higher than 18

texts:
I can easily raise user defined errors where severity is not higher than 18. When is higher, I get this error

  Error severity levels greater than 18 can only be specified by members of the
  sysadmin role, using the WITH LOG option.

I have access to sysadmin role, so can someone specify the syntax for raising errors WITH LOG option.
Thanks.


texts: Why some triggers run exactly at the same time?

texts:
I wrote a simple audit with triggers. when a trigger called, the data inside the trigger (inserted and deleted rows) are sent through broker to a procedure to write them in the Master Audit Table. here is the trigger:

CREATE TRIGGER [dbo].[trg_personAudit]
ON [dbo].[person]
 AFTER INSERT, UPDATE, DELETE
AS
    DECLARE @auditBody XML = '<AuditMsg/>'
    DECLARE @oldAuditBody XML = '<AuditMsg/>'
    DECLARE @newAuditBody XML = '<AuditMsg/>'
    DECLARE @DMLType CHAR(1)
    DECLARE @tableName sysname
    DECLARE @guid varchar(100) = NULL;

    BEGIN
        DECLARE @change_date datetime2(7) = sysdatetime();
        SET @DMLType = 'I'
        IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
            SET @DMLType = 'U';
        ELSE IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
            SET @DMLType = 'I';
        ELSE
            SET @DMLType = 'D';

        declare @userid nvarchar(20)

        select @userid=splitdata from dbo.fnSplitString(APP_NAME(),'@') where splitdata like 'userid=%'
        select top 1 @userid=splitdata from dbo.fnSplitString(@userid,'=') ORDER BY splitdata asc
        select @guid=splitdata from dbo.fnSplitString(APP_NAME(),'@') where splitdata like 'gid=%'
        select top 1 @guid=splitdata from dbo.fnSplitString(@guid,'=') ORDER BY splitdata asc

        IF (@userid IS NULL OR @userid = '')
            SELECT @userid = '-1'

        SELECT  @oldAuditBody = (SELECT * FROM deleted  FOR XML AUTO, BINARY BASE64, ELEMENTS)
        SELECT  @newAuditBody = (SELECT * FROM inserted  FOR XML AUTO, BINARY BASE64, ELEMENTS)

        SET @tableName = 'person'

        print 'generated guid is: ' + @guid;
        SELECT @auditBody = (select
                                DB_NAME() as SourceDb,
                                @tableName as SourceTable,
                                @userid as UserId,
                                @DMLType as DMLType,
                                @oldAuditBody as OldData,
                                @newAuditBody as NewData,
                                @guid as gid,
                                @change_date as ChangeDate
                            for xml path('AuditMsg'))

        EXEC dbo.usp_SendAuditData @auditBody
    END

GO
EXEC sp_settriggerorder @triggername=N'[dbo].[trg_personAudit]', @order=N'Last', @stmttype=N'DELETE'
GO
EXEC sp_settriggerorder @triggername=N'[dbo].[trg_personAudit]', @order=N'Last', @stmttype=N'INSERT'
GO
EXEC sp_settriggerorder @triggername=N'[dbo].[trg_personAudit]', @order=N'Last', @stmttype=N'UPDATE'

as you can see, I get the current time stamp in this line:

DECLARE @change_date datetime2(7) = sysdatetime();

Suppose there is an insert and after that we have an update: sometimes this two triggers run exactly at the same time. even we have datetime2(7) but the times of the both rows are exactly the same. I can't find any solution to overcome this situation. Why the times are exactly equal?


texts: What is a good way to replicate code across multiple databases?

texts:
SQL Server 2005

I have two databases (Source1 and Source2) with the same data model and I plan to merge the data into a third database (Target) with an "abstracted" data model. In the future, it's possible to have additional Source databases that will need to be merged into Target.

Originally, I was hoping to build views and stored procedures that would reside in the Target database that I could dynamically run again Source1 to load Target then switch to Source2. That doesn't seem to be possible without dynamic SQL.

So plan B is to build the views and procedures in the Target database then deploy them to Source1 and Source2. What is the best way to replicate code to multiple databases?

I'm open to any new ideas that address this situation.


texts: How to access data inside a "before insert trigger" for every statement

texts:
I'm searching my ass of so you're my last hope. The task is quite simple:

I want to execute a trigger for each statement attached to a parent table (parent of partition tables) in Postgres 10.1. The triggered function will create a partition in case the corresponding range does not yet exist. The following function does not yet work for multiple years, I know ;).

Now my problem is, that I have no clue how to access the new data. When creating FOR EACH ROW, I have a variable 'NEW' (not allowed for partitions). When using INSTEAD OF INSERT, I should have something like 'inserted' as variable (not allowed for tables). So how can I access the new rows when using BEFORE INSERT FOR EACH STATEMENT? I just want all unique years which would be inserted afterwards.

Table:

CREATE TABLE ts AS (
  id BIGSERIAL         NOT NULL,
  start_ts TIMESTAMPTZ NOT NULL
);

Partitions:

CREATE TABLE ts_2014
  PARTITION OF ts
  FOR VALUES FROM ('2014-01-01') TO ('2015-01-01');

Trigger Function:

CREATE OR REPLACE FUNCTION create_ts_partition()
  RETURNS TRIGGER AS $$
DECLARE
  row_year           INT;
  dst_partition_name VARCHAR;
  partition_found    BOOLEAN;
  lower_bound        VARCHAR;
  upper_bound        VARCHAR;
BEGIN
  row_year = (
    SELECT
      extract(YEAR FROM start_ts)
    FROM NEW
    LIMIT 1);

  dst_partition_name = tg_table_name || '_' || row_year;

  partition_found = (
    SELECT
      exists(pg_c1.relname)
    FROM pg_class AS pg_c1
    WHERE pg_c1.relname LIKE dst_partition_name
  );

  IF NOT partition_found
  THEN
    lower_bound = row_year || '-01-01';
    upper_bound = (row_year + 1) || '-01-01';
    EXECUTE 'CREATE TABLE ' || dst_partition_name || ' [...]';
  END IF;
END;
$$ LANGUAGE 'plpgsql' PARALLEL UNSAFE;

Trigger:

CREATE TRIGGER ts_partition_creation
  BEFORE INSERT
  ON ts
  FOR EACH STATEMENT
EXECUTE PROCEDURE create_ts_partition();

Thanks in advance.
Kenneth

-- edit
Thanks to Daniel Vérité, I've encountered some inconsistencies with the names inside the example. Thanks!


texts: Why is context_info sometimes blank in sys.dm_exec_sessions?

texts:
I am trying to get information from the context_info column using Cast(context_info as varchar(128)) from sys.dm_exec_sessions as given in the article below:

Finding User Sessions from SPID in Dynamics AX 2012 by Amit R. Kulkarni

However, for some rows, the context_info column is empty. Does anyone know why this is?

The database is a Dynamics AX database and the query I'm using is:

select cast(context_info as varchar(128)) as ci,*
from sys.dm_exec_sessions
where program_name like '%Dynamics%'

Here is the Output, I have had to remove a couple of columns but this is pretty much how it looks. If you see, The Context_info is blank for session 63 and 87.


texts: copy/sync only data between 2 database with non identical table structure

texts:
I have the following issue and was wondering if someone knows if this is possible.

We have a MS SQL Server 2012 Instance with 2 databases database A and database B.

On Database A the data from 6 tables ( not the tables itself) has to be copied or synced to tables with the same name on Database B . I cannot use the sync within sql server because the layout of the tables is slightly different.

I got it to work in 3 steps now :

remove constraints from tables in database B and delete entire content from tables
Copy data with SSIS package from A to B
Re applying constrains from tables in database B

This is not a desirable situation users that have a connection to database B lose connection ( duh!! )

My question : Is it possible to copy or sync only the ( modified ) table data from A to B without users having to leave the database for small period of time.
I hope my explanation is a bit clear…


texts: We have log backups running every 15 minutes and nightly fulls/diffs (fulls are once a week per DB, but they are spread across the whole week for load balancing). Lets say for a particular DB I have a diff that starts at 10:05pm and finishes at 10:18pm and I have log backups at 10:00pm, 10:15pm, and 10:30pm. If I want to restore to 10:24pm that night, I know I need to restore the last full and the diff from that night, but then I'm not sure which transaction log I should start with after that.


texts:
I stuck in here.

How to implement:

CASE
WHEN m.MAXDATE is not null THEN '*'
WHEN m.MAXDATE IS NULL THEN ' '
END

In query:

SELECT Ename, Hiredate, m.MAXDATE[MAXDATE]
FROM EMP
LEFT JOIN (Select max(Hiredate)[MAXDATE]
FROM EMP) m
on EMP.HIREDATE = m.MAXDATE

I've checked few website in order to find the answer, but it's giving me errors in every variant.

More details:

Query is:

SELECT Ename, Hiredate, m.MAXDATE[MAXDATE]
FROM EMP
LEFT JOIN (Select max(Hiredate)[MAXDATE]
FROM EMP) m
on EMP.HIREDATE = m.MAXDATE

This is a result:

SMITH   1980-12-17 00:00:00.000 NULL
ALLEN   1981-02-20 00:00:00.000 NULL
WARD    1981-02-22 00:00:00.000 NULL
JONES   1981-04-02 00:00:00.000 NULL
MARTIN  1981-09-28 00:00:00.000 NULL
BLAKE   1981-05-01 00:00:00.000 NULL
CLARK   1981-06-09 00:00:00.000 NULL
SCOTT   1982-12-09 00:00:00.000 NULL
KING    1981-11-17 00:00:00.000 NULL
TURNER  1981-09-08 00:00:00.000 NULL
ADAMS   1983-01-12 00:00:00.000 1983-01-12 00:00:00.000
JAMES   1981-12-03 00:00:00.000 NULL
FORD    1981-12-03 00:00:00.000 NULL
MILLER  1982-01-23 00:00:00.000 NULL

I want to just put '*' instead of date in last column for ADAMS, and space ' ' where its NULL.


texts: Oracle DBLink with username and password

texts:
I am trying to connect to a second database via a dblink, but the password has not been included in the dblink.
Is there a way you can provide the password dynamically in the dblink when you call it

e.g.

SELECT * FROM TABLE@USERNAME/PASSWORD.DBLINK;

Thanks in advance.

K


texts: Automate DBs restore through TSQL by given folders

texts:
I searched in the internet but cannot find clear and direct way. I have a folder which include 100 databases as full backup, another folder contains all logs for these 100 DBs.

I need a script to do the automate restore for these DBs?

The names for backup files are different, for example

Adventure_Work_06162015_220000.bak
Adventure_Work_06012015_150000.bak

and so on, appreciate your help and cooperation.


texts: Minimizing the number of parent rows for a distinct column in a child table

texts:
I have three tables with the following format

A        B       C
-----    -----   -----
aKey     bKey    fk_c_b
         fk_b_a  c_enum
                 c_value

aKey and bKey are the primary key for each table, C does not have a primary key. fk_b_a and C.fk_c_b are the foreign keys that points to the parent table. c_enum is a enum of 87 different values. C.c_value is float that can be any value

The relationships between the tables are:

(A.aKey) 0..1 --- * (B.fk_b_a)
(B.bKey) 0..1 --- * (C.fk_c_b)

There are:

57,668 rows in A
650,768 rows in B
34,734 rows in C

I need to display a distinct list of all c_enum values and their parents, however If two rows in C share the same fk_c_b and c_value values but different c_enum values I can use not those two rows in the distinct list.

select aKey, bKey, c_enum, c_value, newid() as id
into #myResultSet
from A
inner join B on fk_b_a = aKey
inner join C as temp1 on fk_c_b = bKey
where c_value not in (select c_value from C as temp2
                         where temp1.c_enum <> temp2.c_enum
                            and temp1.fk_c_b = temp2.fk_c_b
                            and temp1.c_value = temp2.c_value)

--This is the step that I do not know how to optimize
delete #myResultSet where id <> (select top 1 id from #myResultSet as t1 where #myResultSet.c_enum = t1.c_enum)

Also in that process I need to minimize the the distinct count of aKey and bKey. So If I did

select count(distinct aKey), count(distinct bKey), count(*) from #myResultSet

the result would have the minimum values possible. The optimal result is 1, 1, 87 and the worst case would be 87, 87, 87. My current method does not attempt to get minimal values (my results are 14, 17, 87 which is not bad, but I think that is just luck with this dataset, running on a different dataset may return worse results) and I am having a lot of trouble figuring out how to do it the right way.

What would be the correct way to solve this problem? My current method is not optimal all and I have been having a lot of trouble figuring out what I need to do to build #myResultSet with minimal values for count(distinct aKey) and count(distinct bKey).


texts: How to restart MS SQL server right, what is best practice to do that?

texts:
I`m quite new to DB administration.

I have a problem when I restart SQL server, one of the databases goes into Recovery mode and automatically repairs the DB in 30 min. I don't know why this happens.

Here is what I did, but it seems that not enough:

I have SQL Server Microsoft SQL Server 2014 (SP2-GDR).

install Windows updates
SQL: shrink all DB and logs files
SQL: killed all Runtime Brocker`oв
SQL: stopped SQL agent
SQL: stopped SQL server
Restart Windows Server.

UPDATED :

1) How big is the database?

4.5 GB + 400MB Logs

2) Why are you restarting the instance?
Install Windows updates, and also apps that connect to that SQL server become slower and slower with time. We restart the server once a month usually. It takes 15 minutes to start, but this time it took 45 min (while recovering DB).

3) Is this a server or a pseudo-server (desktop running Server OS)?

its virtual machine. Running on server hardware.

4) Is the Server configured for application or background processes?

The server runs SQL and additional background apps/ web services

5) How much RAM does the server have? How many processors?

16 GB RAM
4 CPU

6) SAN storage or local disks? .... The more details you provide the better we can provide you with an adequate answer.

We buy hosting as service, can`t tell exact hardware architecture. But we have SSD disk (where SQL DB stored).

7) 30 Minutes might well be the best SQL Server can do. It depends on a lot of factors.

It usually takes up to 15 min. I think I miss some steps that I need to do to close all DB in right way.


texts:
I want to generate  json grouping users by year.

I have got a "user" table in sqlite with the following columns:

date
name
surname
value

I would like to create a JSON with the following structure:

{
"App":[
    {
    "data": [
        {
            "name": "UserName1",
            "Surname": "Surname1",
            "v": 1231
        },
        {
            "name": "UserName2",
            "Surname": "Surname2",
            "v": 131
        },
        {
            "name": "UserName3",
            "Surname": "Surname3",
            "v": 121
        }
    ],
    "year": 2018
    },
    {
    "data": [
        {
            "name": "UserName4",
            "Surname": "Surname4",
            "v": 1231
        },
        {
            "name": "UserName5",
            "Surname": "Surname5",
            "v": 131
        }
    ],
    "year": 2019
    }

How to group all the data based on the year?


texts:
I'd like to add (or rather subtract) a number of days from a timestamp.
For example from 2020-04-13 00:00:00 Europe/Berlin I want to subtract 20 days and I expect the result to be 2020-03-24 00:00:00 Europe/Berlin.
If I run
select ('2020-04-13 00:00:00 Europe/Berlin'::timestamptz - INTERVAL '20 DAY') AT TIME ZONE 'Europe/Berlin'

I get 2020-03-23 23:00:00.000000.
That seems to be because PostgreSQL subtracts 20 * 24 hours (a DST switch happens during that date range) which is different from subtracting 20 days.


texts: How deterministic is execution plan creation in SQL Server?

texts:
Given the following constants:

The same database with the same structure (tables, indexes, etc)
The same data
The same SQL Server and hardware configuration
The same statistics
The same SET options in the client
The same SQL Server versions
The same trace flags

Given these constants, will SQL Server always produce the same plan for a given query?

If not, are there other considerations? Is there also an element of nondeterminism to consider as well?


texts:
I want to use a user defined function so that while inserting values in column i want to insert last_day of the month in one column by referencing other column in same table.
my code is-
insert into table_A
(id,pma_timestamp,monthend)
values
(103,'2017-08-15',last_day(pma_timestamp));

but getting the error:

Hint: There is a column named "pma_timestamp" in table "table_a", but it cannot be referenced from this part of the query

how to resolve this


texts:
Step 1 :I created a custom error message in sys.messages

EXEC sp_addmessage
    @msgnum = 50001,
    @severity = 10,
    @msgtext =
        N'Error 50001, severity 10, state 1 was raised, Please check mdf file.' ,
    @lang = 'us_english',
    @replace='REPLACE';

Step 2: Inside a Job when a condition is met I am raising the error

RAISERROR(50001, 10,1,@freespacePct, @dbname,@drive) with LOG;

I would like to see FreespacePCt,database name and drive details inside the error message.

But when I look at the raised error I am just seeing just what I declared inside the sp_addmessage. How can I log the other details?

Message I am seeing :

  Error 50001, severity 10, state 1 was raised, Please check mdf file.


texts: Understanding CleanupTime in Ola Hallengren's SQL Server scripts in relation to FULL backups and LOG backups

texts:
I am having trouble understanding what exactly to expect from the CleanupTime option in the Ola Hallengren Server Maintenance Solution. I'm finding some related questions, and elaborate answers, but the explanations still puzzle me a bit.

Specifically:

I am doing a weekly FULL backup, a daily DIFF backup, and an hourly LOG backup.
The FULL backup is using the default CleanupTime of 24h. The DIFF and LOG backup have NULL as CleanupTime.

From the documentation of the CleanupTime paramter, I fail to understand if setting the CleanupTime setting for a backup of BackupType FULL, will also delete older DIFF and LOG backup files, or only FULL backup files.

  Specify the time, in hours, after which the backup files are deleted.
  If no time is specified, then no backup files are deleted.

The latter paragraph makes me think that setting CleanupTime on backups of BackupType FULL will also delete older transaction logs. Yet it is unclear if this paragraph only applies to backups of the BackupType LOG, or also to backups of the BackupType FULL.

  DatabaseBackup has a check to verify that transaction log backups that
  are newer than the most recent full or differential backup are not
  deleted.

What I am trying to achieve, is that I can do a point-in-time recovery up to 1 week. (We have a very slowly changing database, so this is feasible) The way I understand it now, this would require a week old full backup, and a weeks worth of Transaction log backup. Since the full, and differential backups can only be used to restore to one specific point in time.

So, should I just set the CleanupTime option of my FULL backup job to 24*7? What I'm guessing now is that setting it to 24h, will cause the next FULL backup to delete all older Full, diff and Transaction log backup files, leaving me with a point-in-time recovery window of ... 0 hours. Right?


texts: Access denied when trying to run an agent job

texts:
Can someone please point me in the right direction. I keep getting the below error message when I try to run a job that executes a package I created and saved on the SQL Server.

12/03/2012 17:32:15,New_App_R,Error,1,SQLAAG01,New_App_R,New_App_R,,
Executed as user: NT Service\SQLSERVERAGENT.
Started:  5:32:15 PM
Could not load package "\MSDB\New_App_R" because of error 0xC00160AE.
Description: Connecting to the Integration Services service on the computer "SQLAAG01"
failed with the following error: "Access is denied." By default only administrators
have access to the Integration Services service. On Windows Vista and later the process
must be running with administrative privileges in order to connect to the Integration
Services service.

I also tried creating the same package and saved it on the file system (desktop) and when I try to execute the package using the below code in a job or in a query window

EXEC @returncode = xp_cmdshell 'dtexec /f "C:\Users\jvelez\Desktop\New_App.dtsx"'

I get another access denied error that says:

Description: Failed to open package file "C:\Users\jvelez\Desktop\New_App.dtsx"
due to error 0x80070005 "Access is denied.".  This occurs when loading a package
and the file cannot be opened or loaded correctly into the XML document. This can
be the result of specifying an incorrect file name when calling LoadPackage or the
specified XML file has an incorrect format.

If I double click the package on my desktop it runs and works perfectly, but what I need to do is schedule the package to run. I tried creating jobs in different ways and adding myself to the administrators group but nothing seems to work. Any advice would be greatly appreciated.

I'm running SQL Server 2012 on Windows Server 2008


texts: Multiple IPs are not getting bind in mongodb conf file

texts:
I am trying to bind multiple IP to mongodb config file, one is 0.0.0.0 and other is 127.0.0.1. I did the following change in configuration file

  /etc/mongod.conf

# network interfaces
net:
  port: 27017
  bindIp: "0.0.0.0,127.0.0.1"

Than I reloaded the configuration by the following command

sudo mongod --config /etc/mongod.conf

Than checked if the server is listening on localhost as well or not by the following command.

netstat -tlpn

It shows the output only for 0.0.0.0

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      -
tcp        0      0 0.0.0.0:27017           0.0.0.0:*               LISTEN      -

Why is it not listening to localhost as well?


texts: Use Stored Procedure or View to return Data from Multiple Tables

texts:
Is it just down to preference for whether to use a View or a stored procedure (or TVF) to simply return data joined from multiple base tables?

Is there any best practice regarding this? Should a stored procedure be used just for manipulating data? And is it best to use a view for the purpose of returning data from multiple tables?

Are there also likely to be any performance issues?

Query:

SELECT
    t.TagID, t.TagName,
    t.IsActive AS 'IsTagActive',
    tk.TaskID, tk.TaskName,
    tk.IsActive AS 'IsTaskActive',
    g.GroupID, g.GroupName
FROM
    Model.[Tag] t
INNER JOIN
    Model.[TagTaskMapping] ttm ON ttm.TagID = t.TagID
INNER JOIN
    Model.[Task] tk ON tk.TaskID = ttm.TaskID
INNER JOIN
    Model.[Group] g ON g.GroupID = t.GroupID


texts: Can auto failover cause split-brain? (SQL AlwaysOn)

texts:
I have been studying Cluster voting and AlwaysOn failover.  The topic of split-brain comes up and sounds terrible.  This following from Microsoft seems to indicate that only a person with SA, doing something stupid can cause it.

When more than one subset of voting nodes is able to establish a quorum on its own, that is known as a split-brain scenario. In such a scenario, the nodes in the separate quorums may behave differently, and in conflict with one another.
The split-brain scenario is only possible when a system administrator manually performs a forced quorum operation, or in very rare circumstances, a forced failover; explicitly subdividing the quorum node set. Source

There are a lot of variables, and I wonder if something like changing the Dynamic quorum (is on by default) could create a scenario where a split-brain scenario exist.
Can auto failover be configured so a split-brain scenario is possible?
I want to know, so that if it is possible I won't configure that way.


texts:
I am building a web based quoting system for a local automobile dealership.

I think I have figured out the logic for the databases but I want to get a second opinion before I start just to make sure I am doing it right.

I am working with the service plans for various vehicles and the client wants a drill down type search to find the required service plan e.g. Model of vehicle, transmission, etc...

The thing is the drill down is dynamic so some cars only have one type of transmission manual or automatic but with others you have to pick, the same applies to airbags and a few other things.

I was thinking I would have one table with the models and their specifications and then another table for each service plan at X number of KM's/Miles.

The table with models/specifications would populate the drop downs and I would then use this data to formulate a query to find the correct service plan table.

The reason for keeping the service plans in individual tables is because the data is so inconsistent.

Does this sound like the right approach?


texts: Size of data in secondary exceeded primary after its initial resync from primary in MongoDB

texts:
I step down the primary node, deleted its data and performed the initial resync from new primary. But when i checked the size of data after the completion of initial resync, I found that the size of data in this node was 409G whereas size of data in other node from where this node has synced its data was 326G. Hence the size was increased by 83G after the resync.

Earlier also i have done such initial resync, and every time I have found that the size of data decreases after the resync. But in this case its opposite.

Recently I have upgraded the version of cluster from 2.4.9 to 2.6.7. This was my first initial resync after the version upgrade. So is this increase in size is because of the new MongoDB version. Does the same data takes more storage space in version 2.6 than in version 2.4 ?


texts:
We have been getting a lot of issues pertaining memory consumption in Postgres. I am thinking of deploying PgBouncer to resolve this issue, but wanted to know what could be the cause for this.
ps aux --sort=-%mem | head -30

USER        PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
postgres  23438 41.0  6.2 78075200 8258220 ?    Ssl  Aug09 525:40 postgres: xxxxx: xxxxx(60028) idle
postgres   4225 31.7  5.9 77797888 7903220 ?    Ssl  Aug09 438:58 postgres: xxxxx: xxxxx(45012) idle
postgres 118999 39.3  4.8 76290020 6395136 ?    Ssl  Aug09 306:52 postgres: xxxxx: xxxxx(50026) idle

free -g
              total        used        free      shared  buff/cache   available

Mem:            125          36          35           3          54          84
Swap:            19           0          19

System RAM : 128 GB
CPU(s) : 32
Shared_buffer : 32 GB
DB Size : 24 GB
Max Connections : 200
Average Connections: 50
Effective_Cache_Size : 64 GB
Work_mem : 41MB
PG Version : 11
OS : Ubuntu

A lot of the idle connections are getting reused despite it being closed from the app side. I have tuned the database by changing Shared_buffer, work_mem to values as per PostgreSQL expectations, but still getting these issues.
PS : We have setup HAProxy between App and DB.


texts: How to reduce huge CXPACKET & LATCH_EX (ACCESS_METHODS_DATASET_PARENT) wait times?

texts:
Problem

We have been experiencing high levels of user disruption due to SQL timeouts accross our systems since the beginning of the year.

The SQL-Server instance in question has very high CPU usage (higher than 90% on all 16 cores all the time) during business hours.

We have also noticed very high wait times: The combination of CXPACKET & LATCH_EX accounts for about 97% of all waits. This is split about 50/50 between CXPACKET & LATCH_EX.

The non-buffered latch wait accounting for the vast majority (>95%) of LATCH_EX is ACCESS_METHODS_DATASET_PARENT.

This suggests the problem is to do with parallelism.

An example of the scale of wait times is:

CXPACKET : 332,301,799 ms
LATCH_EX : 267,955,752 ms
PAGEIOLATCH_SH : 2,955,160 ms

This was for the period between 08:00-16:24 on Jan 11th.

Options under consideration

1) Change MAXDOP from 0 to something between 4 and 8

2) Modify the cost threshold of parallelism from 50 to a higher number

Suggestions most welcome on how to ease the very high CPU load we are seeing, and reduce timeouts, in particular whether the proposed course of action is wise, and which numbers to change MAXDOP and cost threshold of parallellism to.

Background information

SQL-Server 2008 R2 running on AMD Opteron 6180 SE, of which 16 cores are given to this instance of SQL-Server.
Type of workload: something of the order of 800 connections at the same time during business hours; majority OLTP type workload with some OLAP mixed in.
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) ... Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1). Memory is appx 128 Gigs between 24 cores. 16 of the cores are available to this instance


texts: How can I apply heuristics to optimise an expression tree?

texts:
I've converted the following SQL query:

SELECT name
FROM dog d, works_at w
WHERE (d.owner_name = w.person_name) AND (w.kennal_name = "Breeders");

into the following expression tree:

My main question is how does one apply heuristics to optimise an expression tree?

And is it OK to use the abbreviaions (e.g. dog as d) in the query tree, or do I have to write out dog.owner_name in place of d.owner_name?


texts:
I have a table with a column of type jsonb called attributes.

I have rows with values like -

{
    "id1": "something1",
    "id2": "something2",
    "id3": "something3",
}

and I want to convert them into -

{
    "id1": ["something1"],
    "id2": ["something2"],
    "id3": ["something3"],
}

How can I do that?


texts: Problem with InnoDB "per table" file sizes

texts:
I Have configured the mysql server with innodb per table. There was a table watchdog having approximate 19GB of watchdog.ibd file.

I have truncated the table now it has zero record. But the watchdog.ibd have same size.

What's the issue.? Does truncate does not work for removing the data from the .ibd file..?

I don't want to drop the table because in need that table.


texts: Oracle database backup and restore options

texts:
We have one requirement that export data from two different source Oracle DBs and restore the data to another two different target Oracle DBs to do data comparison.  We care about the datafiles in these two source Oracle DBs.

The Oracle db are 11.2 RAC. Can we use Oracle data dump tools such like impdp and expdp to meet the above requirement? Or is there any limitation to use data dump tools for the above requirement?

Thanks a lot.


texts: Amount by which to increase checkpoint_segments (postgresql)

texts:
In my postgresql log, I see statements such as checkpoints are occurring too frequently (26 seconds apart). Currently in the conf, checkpoint_segments are 32, checkpoint_timeout is 15 mins and checkpoint_completion_target is 0.9.

I've seen many suggestions that I need to increase my checkpoint_segments. But they don't talk about by how much? Around 6GBs of postgresql log is generated daily (it's a write heavy chat application).

How do I go about tuning checkpoint_segments to a higher value?


texts:
I have the following query:

SELECT id,
   email,
   first_name as "firstName",
   last_name as "lastName",
   is_active as "isActive",
   password,
   access,
   CASE
     WHEN access < 3 THEN (
       SELECT
         CASE WHEN count(*) = 1 THEN true ELSE false END
       FROM user_rating_entity ure
       WHERE ure.user_id = u.id
         AND ure.rating_entity_id = :re_id
     )
     ELSE true
   END as "isResponsible"
FROM users u
WHERE u.id = :id

If access > 3, field "isResponsible" should be directly set to true and the subquery should not be executed.  I used explain analyze with both cases where access >= and < to 3 but I get the same output.

Why is that so?


texts: I'm unable to open a datafile in oracle, looking for a solution

texts:
I'm trying add more data into my schema, but I encounter the following error

ORA-01116: error in opening database file 14
ORA-01110: data file 14: '/u01/oracle/oradata/REVA/ra_tbsp07.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

I've run the following query to try and identify what's wrong with the datafile (I'm not the DBA - hes on leave : but i do have SYS access)

SELECT  Substr(df.tablespace_name,1,20) "Tablespace Name",
Substr(df.file_name,1,80) "File Name",
Round(df.bytes/1024/1024,0) "Size (M)",
decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used (M)",
decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free (M)",
decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"
FROM DBA_DATA_FILES DF,
(SELECT file_id, sum(bytes) used_bytes FROM dba_extents GROUP by file_id) E,
(SELECT Max(bytes) free_bytes, file_id FROM dba_free_space GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id AND df.file_id  = f.file_id (+)
ORDER BY df.tablespace_name, df.file_name

and I get the following output

Tablespace Name     File Name                               Size(M) Used(M) Free(M) % Used
RA_TBSP             /u01/oracle/oradata/REVA/ra_tbsp.dbf    32768   30548   1983    93
RA_TBSP             /u01/oracle/oradata/REVA/ra_tbsp02.dbf  32768   31953   640     98
RA_TBSP             /u01/oracle/oradata/REVA/ra_tbsp04.dbf  32768   28068   1745    86
RA_TBSP             /u01/oracle/oradata/REVA/ra_tbsp05.dbf  32768   28920   1689    88
RA_TBSP             /u01/oracle/oradata/REVA/ra_tbsp06.dbf  32768   28686   2014    88
RA_TBSP             /u01/oracle/oradata/REVA/ra_tbsp07.dbf      0       0      0

From what I can see is the datafile is empty, now I know there is no feature in oracle wherein you can drop a datafile (like how you drop objects) After reading through https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:515222144417, It appears that the only "clean" way of doing this is to export all tables in the other datafiles, and drop the tablespace and then re-import. That isn't an option for me.

I was wondering if there is some other alternative to this problem? If i add another datafile, say ra_tbsp08.dbf will I be able to add more data into my tablespace? Or will I still face this error?

Will resizing the datafile help? Or is this even an error caused by oracle? as in the settings have to be changed in UNIX via chmod command?

Basically I require a solution with as little modification to the DB as possible that will help me work around this! I hope such a solution exists!


texts:
When I tried executing a SSIS Package I came across following error.

  [OLE DB Destination [148]] Error: SSIS Error Code DTS_E_OLEDBERROR.
  An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record
  is available.  Source: "Microsoft SQL Server Native Client 10.0"
  Hresult: 0x80004005  Description: "The statement has been
  terminated.". An OLE DB record is available.  Source: "Microsoft SQL
  Server Native Client 10.0"  Hresult: 0x80004005  Description:
  "Violation of PRIMARY KEY constraint 'PK_user'. Cannot insert
  duplicate key in object 'dbo.users'. The duplicate key value is (-3)."

I was trying to pull data from one table to another table across two DB servers.
How can I proceed further?


texts:
I would like to move the tables in my database from one filegroup (Primary) to a another (a new one). I can find lots of advice on this concerning tables with clustered indexes but all these tables in question are heaps. So far I implemented the solution found here:

http://gallery.technet.microsoft.com/scriptcenter/c1da9334-2885-468c-a374-775da60f256f#content

which essentially provides a script to create a clustered index for the table on a secondary filegroup (which essentially moves the data) then removes the index to return it to being a heap. This seems to work fine but may be too slow for our production requirements.

Are there any alternative solutions? This doesn't feel quite elegant enough for me to believe as the only method.

Note1: The tables must remain heaps. A 3rd party app enforces this.

Note2: The DB is about 150GB in size across 200 tables. 197 tables and 99.9% of the data will move to the secondary filegroup.


texts: MySQL get values from the other table by using inner joins

texts:
In mysql I have 3 tables called as

ps_product_lang

ps_order_detail

and ps_image

here are the tables sample data

=== ps_product_lang ===

CREATE TABLE IF NOT EXISTS `ps_product_lang` (
  `id_product` int(10) unsigned NOT NULL,
  `id_shop` int(11) unsigned NOT NULL DEFAULT '1',
  `id_lang` int(10) unsigned NOT NULL,
  `description` text,
  `description_short` text,
  `name` varchar(128) NOT NULL,
  PRIMARY KEY (`id_product`,`id_shop`,`id_lang`),
  KEY `id_lang` (`id_lang`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `ps_product_lang`
--

INSERT INTO `ps_product_lang` (`id_product`, `id_shop`, `id_lang`, `description`, `description_short`,  `name`) VALUES
(1, 1, 1, '
this is dummy
', '
dummy
', 'dummy product')
);

===== ps_order_detail===
    CREATE TABLE IF NOT EXISTS `ps_order_detail` (
      `id_order_detail` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `id_order` int(10) unsigned NOT NULL,
      `product_id` int(10) unsigned NOT NULL,
      PRIMARY KEY (`id_order_detail`),
      KEY `product_id` (`product_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=16 ;

    --
    -- Dumping data for table `ps_order_detail`
    --

    INSERT INTO `ps_order_detail` (`id_order_detail`, `id_order`,  `product_id`) VALUES
    (1, 1, 2);

========= ps_image =========
CREATE TABLE IF NOT EXISTS `ps_image` (
  `id_image` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_product` int(10) unsigned NOT NULL,
  `position` smallint(2) unsigned NOT NULL DEFAULT '0',
  `cover` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_image`),
  UNIQUE KEY `idx_product_image` (`id_image`,`id_product`,`cover`),
  KEY `image_product` (`id_product`),
  KEY `id_product_cover` (`id_product`,`cover`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=24 ;

--
-- Dumping data for table `ps_image`
--

INSERT INTO `ps_image` (`id_image`, `id_product`, `position`, `cover`) VALUES
(1, 1, 1, 1),
(2, 1, 2, 0),
(3, 1, 3, 0),
(4, 1, 4, 0),
(5, 2, 1, 0),
(6, 2, 2, 0),
(7, 2, 3, 1),
(8, 3, 1, 1),
(9, 3, 2, 0),
(10, 4, 1, 1),
(11, 4, 2, 0),
(12, 5, 1, 1),
(13, 5, 2, 0),
(14, 5, 3, 0),
(15, 5, 4, 0),
(16, 6, 1, 1),
(17, 6, 2, 0),
(18, 6, 3, 0),
(19, 6, 4, 0),
(20, 7, 1, 1),
(21, 7, 2, 0),
(22, 7, 3, 0),
(23, 7, 4, 0);

So here I want to get all the field like

id_order_detail id_product name id_image(from ps_image table where cover = 1)

To get id_order_detail id_product name I am using this mysql inner join

SELECT DISTINCT (a.id_order_detail),(b.id_product),(b.name)
        FROM ps_order_detail a join
        ps_product_lang b on a.product_id=b.id_product GROUP BY(name)

This one is giving me id_order_detail id_product name

Now I want to get id_image from the ps_image table where the cover value is 1 for the product_id

So can someone tell me how to do this one? Any help and suggestions will be really appreciable. Thanks


texts: Update column without dropping dependancies

texts:
I have a table with many dependancies such as: functions, views and materialised views. I'd like to update a column from an int to a bigint.

The issue I have is that doing so requires dropping a large number of dependancies in order and then re-building them in order.

Is it possible to turn the dependancy ladder off while I alter the column, update the required views/functions then re-enable it all.

This is coming from an Oracle background where it's perfectly fine to have temporarily invalid dependancies which makes migrations over various DB's environments a breeze.

In Postgresql I'm finding the process is very orchestrated and time consuming to engineer, whereas in Oracle I can just update all the columns & dependancies irrespective of order.


texts: How to join by the most recent preceding date?

texts:
Imagine I have a database with historic price data and a second table with dates

A new row was entered into the price history table every time the price changed.

How can I (efficiently) join the two tables so that I can get the price each customer had to pay on that day?

I need the syntax for teradata, thanks!


texts: Cannot access MySQL database after moving files in data directory

texts:
I am using Xampp locally and I recently did a computer upgrade where I went from XP to Windows 7 and also got a new drive to install Windows on.

I have all my old Xampp files in: G:\xampp
I installed the new Xampp in: C:\xampp (which is the directory location they were previously in)

I then copied over everything in  G:\xampp\mysql\data to C:\xampp\mysql\data.

When I view the specific database in SQLYog I can see everything fine apart from one table which says:

  Table 'qotm.wp_36rwq4kcgt_options' doesn't exist

...but when I try and connect to the database via a script I get:

  InnoDB: Cannot open table qotm/wp_36rwq4kcgt_options from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

I visited that link and the closest thing I could find relevant was the part under Problem Opening Table; however I do not have any  #sql-.ibd* files in that directory, nor any .ibd files whatsoever in there.

I then found this post but it only mentions .ibd files which I do not have any of.


texts:
I have a large query that I'm trying to tune. I write lots of queries but haven't done a lot of tuning. I've included a screenshot from SQL Sentry Plan Explorer Free (SSPEF):

In the above part of the plan the table pb_WorkRquestLog contains 229,001 rows. However, the query plan is showing approx. 348 million rows (229,001 x 1,520 iterations):

There is no where clause so the query is using a Clustered Index Scan. I've rebuild all indexes with FULLSCAN and updated all statistics.

The code that this part of the plan is executing is:

select distinct
            wrs.ServiceKey
            , owner.DepartmentName AS GroupName
            , owner.UserName AS UserId
            , owner.WRLCreateDateTime
            , owner.WRLNotes
            , wrx.CreatedDate as WRCreateDateTime
            , wrx.Id
            , wrx.Description
            , cast(wrx.Notes as varchar(2500)) as Notes
        from
            prism72ext.dbo.pb_WorkRequestService wrs
            Join prism72ext.dbo.pb_WorkRequest wrx on (wrs.WorkRequestId = wrx.Id and wrx.Status = 'Incomplete')
            left join (
                select
                    wl.WorkRequestId
                    ,   d.Name AS DepartmentName
                    ,   u.UserName
                    , wl.CreatedDate as WRLCreateDateTime
                    , cast(wl.Notes as varchar(2500)) as WRLNotes
                from
                    (
                        SELECT
                            MAX( Id ) AS Id
                        FROM
                                prism72ext.dbo.pb_WorkRequestLog WITH(INDEX(0))
                        GROUP BY
                            WorkRequestId
                    ) mwl
                    join prism72ext.dbo.pb_WorkRequestLog wl on mwl.Id = wl.Id
                    join prism72Ext.dbo.pb_Department d ON wl.DepartmentId = d.DepartmentId
                    left join prism72Ext.dbo.pb_User u on wl.UserId = u.UserId
                ) owner on wrs.WorkRequestId = owner.WorkRequestId

SSPEF is reporting the Actual Data Size is nearly 5GB, with 3 million Logical Reads! SSMS reports the table to be only 16 MB in size.

I've tuned the query down from 4m28s to 1m35s, but now I'm stuck. I'd be grateful if anyone could point me in the right direction to deal with this.

Edit: Someone suggested trying "OPTION (HASH JOIN, MERGE JOIN)". This made a dramatic difference. I/O was massively reduced and the query ran in 13 seconds.

Does anyone see any issues with this solution?


texts: Low drive space on c: partitions on clustered servers

texts:
I have a 2 node cluster on Windows Server 2008 R2, with a SQL Server 2012 AlwaysOn AG. Both C: partitions are running out of space.

Can I expand the C: partition on the secondary node, reboot, failover from the primary node to secondary and then expand the C: partition on the primary node? Is there a better way?

Also, I noticed the pagefile.sys on both servers are using a lot of space.  Each has a 72GB pagefile.  I am thinking of moving the pagefile to a different partition and leaving a small pagefile(1 gb) on C:.  I would start this option on the secondary server and then the primary, performing failovers as needed. Good idea or not?


texts: How to get SQL Server Developer edition for installation in non-production for the organisation

texts:
Kindly shed some light on this for me please.
Through my free membership of Visual Studio Dev Essentials, I have a free copy of SQL Server Developer edition installed on my personal machine.
How can we get free Developer edition copies for the organisation, we need to use this for a number of non-production instances?
I am aware that each user that accesses the non-production instance, through the application or back-end tool, needs to have a developer license.
Could you please give some guidance on how to go about doing this at the organisation level? Does each developer/tester/dba have to have their own Visual Studio Dev Essentials free subscription? That doesn't sound right to me, could you please guide or point me to an article that can help me?
The client has to use 2012 at this point in time, due to application support compatibility.
Thank you so much.


texts:
I would like to set FIRSTDATE to 6 (ie: saturday), i have read that to change it parmanently i could set the default language of the login to one of an existing language from the sys.syslanguages and alter the login of the user like:

USE [master]
GO
ALTER LOGIN [some_user] WITH DEFAULT_LANGUAGE = [Français]
GO

But:

SELECT * FROM sys.syslanguages;

i get many languages, but no one has the DATEFIRST set to 6 (they are all set to 1 or 7).

Question:

Can i add another language based on an existing one, and set de field datefirst to 6 ?

Can i modify an existing syslanguage ?

All what i want, is te set it to 6 parmanently, because i need it to get the right week number.

Thank you for help !


texts: "Waiting for initial communication packet" error 0 in MySQL

texts:
I am using MySQL server 5.1.51 on a Linux machine.

The application was working fine for the last 32 days, but since yesterday a number of clients are not able to connect to the database from our application. They are getting the following error:

-2147467259 [MySQL] [ODBC 5.1 Driver] Lost Connection to MySQL server
       at 'waiting for initial communication packet',system error: 0

What can I do to fix this? Where is my.ini located in Windows XP?


texts:
I have never worked with MySql.

I have some old/crude software running on my machine. It holds a lot of data that I would like access to since the UI is garbage. After searching around the file directories I found what appears to be a MySql database.

C:\Users\Me\AppData\Roaming\Program Name\mdb\bin

Inside of this directory is a my.ini file along with several others. Also several folders including data.

Inside C:\Users\Me\AppData\Roaming\Program Name\mdb\bin\data are ib_logfile0, ibdata1, and other folders/files including frm.

In Sql Server I'd attach it to SSMS, but I can't seem to do that here. I tried copying all the files to the local MySQL ProgramData folder then opening MySQL workbench. But I never see any tables in the Schema section.

My question is: Is there a way for me to access these tables? I have installed MySql and attempted to connect to this database with no luck.  Is there a way to access usernames or can I just use a root account?

Thanks in advance.


texts: Does the consistency part of the CAP theorem apply to updates and not first-time writes?

texts:
Using this definition of the CAP theorem:

Consistency All database clients see the same data, even with concurrent updates.
Availability All database clients are able to access some version of the data.
Partition tolerance The database can be split over multiple servers.

My understanding is by data they mean a row in the database.
If I choose a database which is AP and I have a table whose rows never get updates after the initial insert (say for example a log table), will that be consistent, available and partition tolerant?


texts: Saving and managing multiple database "states"?

texts:
I'm very sorry if this is the wrong place to ask this question.

I have been tasked with implementing a system to allow administrators to make changes to data in a database, and save these changes into "states" or "plans".  The states need to be compare-able, with a final state chosen to become the "primary state" and be used for all future queries.

Normally, for things like this, I would duplicate the database and make changes there, but the database I have been entrusted with is extremely large and impossible to duplicate quickly.  I'm using MS SQL Server, and interfacing with Entity Framework in .NET.

Do any of these tools provide inclusive solutions to this problem?  How could this be done realistically?  A coworker has suggested version control, which sounds promising, but I'm wondering if there is a better way.


texts:
I have a complete backup of my XAMPP installation.
Unfortunately I forgot to export my database to .sql files. But as mentioned i have all .frm, .idb and ibdata1 ib_logfile* and so on.

However if I simply copy the backed up files into the /opt/lampp/var/mysql directory I get the following error:

(Switching back to the original directory created by the XAMPP installation is completely fine, though.)

  Connection for controluser as defined in your configuration failed.

and

  phpMyAdmin tried to connect to the MySQL server, and the server
  rejected the connection. You should check the host, username and
  password in your  configuration and make sure that they correspond to
  the information given  by the administrator of the MySQL server.

For my /opt/lampp/var/mysql folder I did

sudo chown -R mysql mysql
sudo chgrp -R mysql mysql

What am I missing to restore the database from the XAMPP backup?


texts: Tenant catalog pattern - logical/physical separation of catalog database

texts:
In a multi-tenant sharded database model the catalog provides the application with a way to discover which databases stores the tenant of the current interest. If you take a closer look of Microsofts example and actually play it through, you can see that they place the catalog database on its own isolated database server.

For the once that have specific Azure knowledge - what is the advantage of putting the catalog database on its own database server away from the tenant databases in an elastic pool? Why wouldn't you put it on the same server - either outside the pool or even inside?
In general - are there any best practices related to separating the catalog database from the tenant databases?


texts:
Does Change Data Capture (CDC) work in a SQL Server 2012 AlwaysOn Failover Cluster Instances setup?

There would be two nodes in the AlwaysOn Failover Cluster Instances setup and two Domain Controllers.

We are not using AlwaysOn Availability Groups.

Will CDC work? and will it failover?


texts: MySQL 8 - user variables within expressions is deprecated [UDF calls with lot of parameters]

texts:
I have this:

SELECT
   @foo1 := UDF1(0, a, b, c, d) AS Foo1,
   @foo2 := UDF1(1, a, b, c, d) AS Foo2,
   @foo3 := UDF1(2, a, b, c, d) AS Foo3,
   @foo4 := UDF1(3, a, b, c, d) AS Foo4,
   @foo5 := UDF2( @foo1, @foo2, @foo3, @foo4) AS Foo5,
   @foo6 := UDF3( @foo1, @foo2, @foo3, @foo4) AS Foo6,
   @foo8 := UDF4( @foo5, @foo7, x, y, z) AS Foo8
FROM MyTable;

As you can see it's quite complicated and a, b, c, d, x, y and z are field names which are quite long [The names express their functionality].

I receive now this error message on MySQL 8.0.20:

  X Setting user variables within expressions is deprecated and will be
  removed in a future release. Consider alternatives: 'SET
  variable=expression, ...', or 'SELECT expression(s) INTO
  variables(s)'.

OK, this is the wrong place to discuss if it makes sense that @var := value is deprecated, so I have to move on and I want to assure that the program doesn't stop working if the next MySQL update is installed.

I could solve it like this:

SELECT
   UDF1(0, a, b, c, d) AS Foo1,
   UDF1(1, a, b, c, d) AS Foo2,
   UDF1(2, a, b, c, d) AS Foo3,
   UDF1(3, a, b, c, d) AS Foo4,
   UDF2(UDF1(0, a, b, c, d), UDF1(1, a, b, c, d), UDF1(2, a, b, c, d), UDF1(3, a, b, c, d)) AS Foo5,
   UDF3(UDF1(0, a, b, c, d), UDF1(1, a, b, c, d), UDF1(2, a, b, c, d), UDF1(3, a, b, c, d) ) AS Foo5,
   UDF3(UDF2(UDF1(0, a, b, c, d) , UDF1(1, a, b, c, d) , UDF1(2, a, b, c, d) , UDF1(3, a, b, c, d) ) ,
        UDF3(UDF1(0, a, b, c, d), UDF1(1, a, b, c, d), UDF1(2, a, b, c, d), UDF1(3, a, b, c, d) ), x, y, z) AS Foo6
FROM MyTable;

Honestly, doesn't this hurt and, what I find worst, it becomes so unreadable and changing any call I have to maintain many times --> buggy.

Also, in the current version the length of the SELECT grows from 2'334 bytes to 3'504 bytes.

I am trying to work with a temporary table but to fill the table is a quite long and [useless] complicated SELECT using LEFT JOIN as @foo5 depends on @foo1-4 and @foo6 depends on @foo5.

This works, but I am wondering if there may be another solution I am not capable to see. The suggested SELECT expression(s) INTO variables(s) I don't understand how this should help in my case.

As I wrote in the beginning, I don't understand why this feature is deprecated as it apparently can solve lot of troubles and makes complicated SELECT statements simpler...

Any suggestions?


texts: SQL Server 2008 Timeout expired rebuild and reorganize index solve the issue

texts:
I have a large database with a file stream located on a different disk the server that hosts this database has a large number of another none related databases but the specifications of this server are great, my database throws a timeout exception for some queries from time to time,

every time I rebuild and reorganize the indexes the queries back to work normally, I have created a maintenance plan that do Rebuild, Reorganize and update statistics every 3 hours to avoid such issues, the fill factor for the indexes is 90%,

What are the reasons for this? and how I can avoid this issue without the maintenance plan as it adds an extra overhead on the CPU and the RAM.


texts: How do long string field names affect MongoDB database size?

texts:
I am using MongoDB and the names of my fields are strings with 10-20 characters. A typical document consists of 30.000 columns filled mostly with floats, like 1.2, 10.5, 2.55. It's size is 1MB.

Do the long string field names affect the size of the MongoDB database ?


texts: Move database physical location using a loop for database names. MSSQL

texts:
I have a main database and several other databases that store documents in them.
I am trying to figure out a way to write a loop in my database move script that dynamically loops through the document databases and include them in my script however the amount of databases aren't fixed. For example I could have one documents database called AppDocuments1 or multiple document databases e.g. AppDocuments1, AppDocuments2, AppDocuments3 and so on.
Could someone help me with creating a loop that automatically loops the all databases so I can include them in my database script.
I have access to the number of databases there are in a field in the main product database. e.g. NoOfDocumentDatabases : value = 6 would indication 6 document databases all named as above.


texts: SQL Server 2008 encrypted connection using multiple server names

texts:
I have a SQL Server that must be accessed using various DNS names (SQL1, sql1.contoso.local, sql1.contoso.com, etc).

I'm struggling with creating a correct SSL certificate that can be used to secure all these names. Applications using Windows Integrated security are connecting to the server using it's local address, because from the Internet only the SQL Logins are enabled.

I have created SSL certificate with Subject (CN) containing sql1.contoso.com and with several SAN entries: DNS=SQL1, DNS=sql1.contoso.local, etc.

Using this certificate I can connect using encrypted connection only when I use the server sql1.contoso.com. Using other names will result in this error message in SQL Server Management Studio:

  A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate's CN name does not match the passed value.) (Microsoft SQL Server, Error: -2146762481)

I'm connecting to the server from a computer that trusts the CA that issued the SSL certificate used on the SQL Server and all the firewalls are correctly opened to allow connections using the 1433 port. The client also trusts the CA. The certificate was generated using Web Server template.

Is it possible to configure by SQL Server so I can connect to it using several domain names of the server using an encrypted connection?

I can only assign one certificate to a single SQL Server Instance, so I have to use one certificate for each accessible server name. If I could assign different certificates for the local connections I can use proper certificate signed by trusted CA instead of self-signed certificate.

I tried to generate a new certificate with multiple CN entries but I wasn't able to connect to the server using any of the defined names in CN entries.


texts: Can I force MSDTC to control only within a single database?

texts:
Using MS SQL Server 2008 R2 we have .NET code and we would like to use nested connections to the same database within the same transaction scope.

I understand and accept that this will require escalation to DTC.

My concern is that our sql instance houses database for distributed applications that, in some cases, require interaction through APIs only. If I enable MSDTC I believe it will apply across databases... this means that when we consume APIs or others consume our API on the same SQL instance we are potentially controller transactions between two theoretically separable applications.
Perhaps if the sql actions fail in the methods employed by the API it may be none of my business, and they shouldn't know or care whether I'm invoking their method inside if a DB transaction.

So, in my specific case, if I could use MSDTC solely within my one database it would be appropriate. I am ok with an exception if I do an action across databases requiring escalation.


texts: Cannot open `pg_hba.conf` in pgAdmin III

texts:
I am running:

version
-----------------------------------------------------------------------------------------------------
PostgreSQL 9.4.11 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
(1 row)

and:

pgAdmin III 1.22.2

on the same:

Description: Ubuntu 14.04.5 LTS
Release: 14.04

I can open the postgresql.conf configuration file with pgAdmin but not the pg_hba.conf configuration file.

Both files are in the same directory (/etc/postgresql/9.4/main):

-rw-r----- 1 postgres postgres  4641 Jun 10  2016 pg_hba.conf
-rw-r--r-- 1 postgres postgres 21501 Jun 10  2016 postgresql.conf

but I have noticed that the two configuration files have different permissions. I haven't modified the permissions of pg_hba.conf to make them different from those of postgresql.conf.

Are the permissions of these files different by default? Is there are reason why the pg_hba.conf has more restricted read permissions? Can I go ahead and modify the permissions of pg_hba.conf?


texts: What does it mean that a "clustered index does not store pointers to the data"?

texts:
I am a beginner in SQL and Database Administration and I am trying to undestand the difference between clustered and non-clustered indexes. More or less I understood what the different tasks are but I have some issues when it comes to the question of "pointers". I don´t come from CS so I have some issue to imagine what happens at the "leaf level" with the pointers and in which way they connect the index to the data. So, what does it mean that a clustered index does not store pointers to the data, while, the non-clustered one, does it?
Sorry in advance if these questions sound stupid but at this point I am pretty confused!


texts: Oracle user password with space character in it

texts:
I connected to an Oracle 19c database and executed the following SQL:

alter user sys identified by "a b" container=all;

It executed successfully. However, after that I can't connect to Oracle using SQLPlus. Below is my trying:

oracle:[~]$ sqlplus
Enter user-name: sys as sysdba
Enter password:
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
Enter user-name: oracle:[~]$
oracle:[~]$
oracle:[~]$
oracle:[~]$ sqlplus sys/"a b"@localhost:1521/ORCLCDB as sysdba
... (not work)
oracle:[~]$ sqlplus sys/\"a b\"@localhost:1521/ORCLCDB as sysdba
... (not work)
oracle:[~]$ sqlplus sys@localhost:1521/ORCLCDB as sysdba
... (type "a b" without quotes and not work)

How can I connect to the database?


texts:
After mysql 8.0.24 does master-slave replication, if the master database hangs, how does the slave database switch to the master database? If it can switch, what are the exact steps?
If switching is not possible, what mode can be used so that if a primary database hangs, another database, which is synchronised with the primary database in real time, can operate normally and can be read and written to?


texts: Mathematical equality of two SQL statements

texts:
Is there a way to get check the mathematical equality of two SQL statements?

I have two SQL statements:

SQL_STATEMENT_1
SQL_STATEMENT_2

Running both statements on data and comparing the output does not help at all.

The set maths behind the statements needs to be evaluated, like a equation solver does.

Out of scope of my question are things like:

comparisons other then equality (greater than, less than, LIKE, ...)
stored procedures, or triggers
Common Table Expressions (WITH)

In the scope:

Subselects: WHERE other_id IN (SELECT id FROM other WHERE ...)
JOINS