texts: Column Name in separate table SQL Server

texts:
I am working with an accounting package backend.

In SQL Server 2012 there is a custom fields table with Values (see sample below) and another table with custom header names (see sample below).

I have created a query that joins the 2 tables multiple times and transposes data into columns and uses aliases to name the column.  It was a tedious task with over 50 custom fields.

There have been some field changes and Executives are talking about adding even more fields.

I am looking for a better solution to dynamically accommodate field name changes or adds.  Any suggestions will be appreciated.

Values Table:

Company,JobNumber,FieldNumber,Information
'01',12345,'01','Value1'
'01',12345,'02','Value2'
...
'01',12345,'50','Value50'
'02',12345,'01','Value2_1'

Header Name Table:

Company,FieldNumber,Description
'01','01','ColumnName1'
'01','02','ColumnName2'
'01','03','ColumnName3'
...
'01','50','ColumnName50'
'02','01','ColumnName2_1'

The query below will dynamically Name ColumnName1 and show values in Column for Company 1.

Declare @CompanyCode nvarchar(2)
SET @CompanyCode = '01'
Declare @CustomInfo1 varchar(20)
Set @CustomerInfo1 = (Select TOP 1 H.Description
                      from dbo.HeaderTable AS H
                      where H.CompanyCode = @CompanyCode and H.FieldNumber = '01')

DECLARE @sql nvarchar(2000)

Set @sql = 'SELECT V.JobNumber, V.Information AS [' + @CustomInfo1 + ']
FROM         dbo.ValuesTable AS V
Where V.CompanyCode = ' + '''' + @CompanyCode + '''' + 'AND V.Fieldnumber =   ''01'''

EXEC sp_executesql @sql

Results:

Company,JobNumber,ColumnName1
'01',12345,'Value1'

Desired Results:

Company,JobNumber,ColumnName1,ColumnName2,,...,ColumnName50
'01',12345,'Value1','Value2',...,'Value50'
'01',12346,'Value1a','Value2a',...,'Value50a'
'02',12345,'Value2_1','Value2_2',...,'Value2_50'
'03',12346,'Value3_1','Value3_2',...,'Value3_50'

The part I am struggling with is adding the next 49 columns. The ColumnNames could be potentially different for the other companies. I could copy this code 50 times with 50 @CustomerInfo 1-50 and Change FieldNumber from 01,02,03...50, then repeat again for company 2-4 with a Select query1, query2, query3, etc.  I cannot use a Union to connect each Company since Column Names may be different.

Maybe some kind of loop?

Select Loop(Query Above for Company1), From Table
Union
Select Loop(Query Company2), From Table
Union
Select Loop(Query Company3), From Table

Ultimately I would like use a Stored Procedure or SSIS to create a table, then update, insert, delete to maintain data.

I consider myself between beginner and intermediate in SQL so please go easy on me.

After @Duffy Answer my final query is

DECLARE @SQL NVARCHAR(MAX)
DECLARE @Columns NVARCHAR(MAX)
DECLARE @Company NVARCHAR(5) = '01'

SET @Columns = STUFF( (SELECT ',['+H.description+']' AS [data()]
                    FROM dbo.Header H
                    WHERE H.Company = @Company
                    ORDER BY H.FieldNumber
                    FOR XML PATH('')),1,1,'')

SET @SQL = '
SELECT Company,JobNumber,'+@Columns+'
FROM
(
    SELECT h.Company,RV.jobNumber,RV.information,h.description
    FROM dbo.Header h
            INNER JOIN dbo.RowValues RV
                ON RV.FieldNumber = h.FieldNumber
                   AND RV.Company = h.Company
    WHERE H.Company = ' + '''' + @Company + '''' + '
) as Data
PIVOT
(
    MAX(information) FOR [description] IN ('+@Columns+')
) as p
ORDER BY JobNumber ASC'

EXEC sp_executesql @sql;

The only change I needed to make was adding the single quotes in the Dynamic SQL statement.  Otherwise, SQL was treating the Variable '01' as an int type even though nvarchar was Declared.  Thanks again @Duffy


texts:
Oracle Version: 12.1.0.2

We have a discrepancy between our PROD and DR TEMP tablespace data files.

PROD = 30G + 30G + 30G data files
DR   = 30G + 30G + 30G + 21G + 2G + 1G + 15G data files

I want both my environments to only have the 30 GB TEMP tablespace data files only (In total 90 GB). I need to remove the additional 21G +2G +1G +15G tablespace data files in the DR environment.

It is critically important for me to know who added the other non standard tablespaces. Do we have a query to find the culprit?

Also: How safe is it to remove the other non-standard TEMP tablespace data files? Is there a safe way or an Oracle defined process to do that?


texts: How does PostgreSQL physically order new records on disk (after a cluster on primary key)?

texts:
Need to know how PostgreSQL orders records on disk.  In this case, I would like to take advantage of index combination as stated in the docs, which as I understand uses bitmaps to get matching rows and returns them according to their physical location.  The table in question has been clustered by its primary key.

As I understand it, PostgreSQL does not automatically continue doing clustering after a clustering is finished (although it does remember that it clustered according to a certain index).  Now, since this is the primary key, I wonder if the physical storage order would be according to that (which if true I would like to use to our advantage for a specific query).

In summary, how does PostgreSQL order its new records, especially after clustering?

Thanks very much!


texts: MySQL "Out of sort memory" error on a simple GROUP_CONCAT query

texts:
I have a (what it seems to me) simple query which fails to run on a simple table, the error is:

Out of sort memory, consider increasing server sort buffer size

These are the SQL statements which I'm trying to run:

CREATE TABLE `permissions` (
  `id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `name` (`name`)

)ENGINE=InnoDB
AUTO_INCREMENT=11 CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci';

INSERT INTO `permissions` (`id`, `name`) VALUES
  (1,'ocjena_dobavljaca.view'),
  (2,'ocjena_dobavljaca.edit'),
  (3,'ocjena_dobavljaca.delete'),
  (4,'zaposlenici.view'),
  (5,'zaposlenici.edit'),
  (6,'zaposlenici.delete'),
  (7,'vatrogasni_aparati.view'),
  (8,'vatrogasni_aparati.edit'),
  (9,'vatrogasni_aparati.delete'),
  (10,'organizacijska_shema_drustva');
COMMIT;

SELECT
    SUBSTRING_INDEX(name, '.', 1) as n,
    COUNT(*) as num,
    GROUP_CONCAT(name)as nn
FROM permissions
GROUP BY n

You can check it in this SQL Fiddle.

This answer states:

  While raising sort_buffer_size can help queries queries with GROUP BYs and ORDER BYs, you are better off improving the queries that you can improve and adding indexes that can be used by the Query Optimizer.

but I don't see any way to improve the query or add indexes to the table, as they are quite simple.

Increasing the sort_buffer_size from 64K (default) to 256K solves the issue, but I'm not sure if this is the right solution.
The other solution is to make the query with a subquery (two SELECTs), which seems a bit redundant:

SELECT
  n,
  COUNT(*) as num,
  GROUP_CONCAT(name)as nn
FROM (
    SELECT
        SUBSTRING_INDEX(name, '.', 1) as n,
        name
    FROM permissions) a
GROUP BY n;

Can anyone explain why the error?


texts: ORA-27100: shared memory realm already exists oracle

texts:
I'm trying to start our db after performing a shutdown abort on a oracle 10g server, but I'm getting the following message:

  ORA-27100: shared memory realm already exists
  Linux Error: 17: File

Oracle 10g running under Centos 6.x

Anyone know any leads about solving that issue? I found this link but since I don't have a extensive background on oracle db administration I rather confirm if I should  follow these instructions.

EDIT In 2017 : This is an old question when I was on a company that I no longer work for. Sadly I never had the change to solve this neither have the current scenario to reproduce this error.


texts: How do I allow Service Broker access to Master Key of database?

texts:
I am trying to set up SqlDependency on my dev system. The db on my system is a few months old production db which I have attached.

When running SqlDependency from ASP.NET application, I see the following error in Event Viewer.

Service Broker needs to access the master key in the database 'MyDb'. Error code:26. The master key has to exist and the service master key encryption is required.

I searched and found,

  When a database is first attached to a new instance of SQL Server, a
  copy of the Database Master Key (encrypted by the Service Master Key)
  is not yet stored in the server. You must use the OPEN MASTER KEY
  statement to decrypt the Database Master Key. Once the Database Master
  Key has been decrypted, you have the option of enabling automatic
  decryption in the future by using the ALTER MASTER KEY statement to
  provision the server with a copy of the Database Master Key encrypted
  with the Service Master Key. This option is discussed in ALTER MASTER
  KEY (Transact-SQL).

http://msdn.microsoft.com/en-us/library/ms174433(v=SQL.90).aspx

However, it is not very clear what password should I use for the OPEN MASTER KEY statement and from where would I get it? I used the Key_GUID value taken from sys.symmetric_keys from both the machines but got an error "Msg 15151, Level 16, State 1, Line 7
Cannot find the symmetric key 'master key', because it does not exist or you do not have permission."

How do I get this to work?


texts:
Restoring a single table on the server is very slow

From Fri Jul 22 13:54:48 CEST 2016 to Fri Jul 22 14:17:41 CEST 2016
Server
2016-07-22 13:55:11 CEST [88719-1] tester@test LOG:  duration: 22988.122 ms  statement: COPY testtable (id, updated, lang, q, results) FROM stdin;
2016-07-22 14:06:09 CEST [88719-2] tester@test LOG:  duration: 658189.147 ms  statement: ALTER TABLE ONLY testtable ADD CONSTRAINT testtable_lang_q_key UNIQUE (lang, q);
2016-07-22 14:06:10 CEST [88719-3] tester@test LOG:  duration: 1546.800 ms  statement: ALTER TABLE ONLY testtable ADD CONSTRAINT testtable_pkey PRIMARY KEY (id);
2016-07-22 14:06:13 CEST [88719-4] tester@test LOG:  duration: 2305.070 ms  statement: CREATE INDEX testtable_lang ON testtable USING btree (lang);
2016-07-22 14:06:15 CEST [88719-5] tester@test LOG:  duration: 2374.251 ms  statement: CREATE INDEX testtable_lang_like ON testtable USING btree (lang varchar_pattern_ops);
2016-07-22 14:17:38 CEST [88719-6] tester@test LOG:  duration: 683316.708 ms  statement: CREATE INDEX testtable_q ON testtable USING btree (q);
2016-07-22 14:17:41 CEST [88719-7] tester@test LOG:  duration: 2839.739 ms  statement: CREATE INDEX testtable_q_like ON testtable USING btree (q varchar_pattern_ops);

Local machine

From Fr 22. Jul 14:38:54 CEST 2016 to Fr 22. Jul 14:42:05 CEST 2016
Lokal
2016-07-22 14:39:32 CEST [23005-1] tester@test LOG:  duration: 37444.240 ms  statement: COPY testtable (id, updated, lang, q, results) FROM stdin;
2016-07-22 14:40:44 CEST [23005-2] tester@test LOG:  duration: 71899.607 ms  statement: ALTER TABLE ONLY testtable ADD CONSTRAINT testtable_lang_q_key UNIQUE (lang, q);
2016-07-22 14:40:46 CEST [23005-3] tester@test LOG:  duration: 2027.237 ms  statement: ALTER TABLE ONLY testtable ADD CONSTRAINT testtable_pkey PRIMARY KEY (id);
2016-07-22 14:40:49 CEST [23005-4] tester@test LOG:  duration: 3781.663 ms  statement: CREATE INDEX testtable_lang ON testtable USING btree (lang);
2016-07-22 14:40:52 CEST [23005-5] tester@test LOG:  duration: 2731.623 ms  statement: CREATE INDEX testtable_lang_like ON testtable USING btree (lang varchar_pattern_ops);
2016-07-22 14:42:02 CEST [23005-6] tester@test LOG:  duration: 69512.264 ms  statement: CREATE INDEX testtable_q ON testtable USING btree (q);
2016-07-22 14:42:05 CEST [23005-7] tester@test LOG:  duration: 3177.827 ms  statement: CREATE INDEX testtable_q_like ON testtable USING btree (q varchar_pattern_ops);

testtable

                                  Table "public.testtable"
 Column  |           Type           |                              Modifiers
---------+--------------------------+---------------------------------------------------------------------
 id      | integer                  | not null default nextval('testtable_id_seq'::regclass)
 updated | timestamp with time zone | not null
 lang    | character varying(2)     | not null
 q       | character varying(100)   | not null
 results | text                     | not null
Indexes:
    "testtable_pkey" PRIMARY KEY, btree (id)
    "testtable_lang_q_key" UNIQUE CONSTRAINT, btree (lang, q)
    "testtable_lang" btree (lang)
    "testtable_lang_like" btree (lang varchar_pattern_ops)
    "testtable_q" btree (q)
    "testtable_q_like" btree (q varchar_pattern_ops)

Tested with PG 9.4 and 9.5 on both machines with the same configuration. Restoring the dump takes even much longer on the server if auto vacuum is enabled...

What could be the problem? What's going wrong here? Any ideas how to debug?

Update: U 16.04 is running on server, U 14.04 LTS on local machine. Number of rows: 1574775, Size of plain SQL dump 1.8 GB.


texts: How do automaticly delete rows from other tables

texts:
I have a MySql DB.

I have a table - recipes.
Also have 2 tables -ingredients and instructions. both have for each entry - a foreign key to the related recipe.

I want that when I delete an entry from the recipes table - to automaticly delete the related ingredients entries and instruction entries.

How?


texts: Update statistics more often and stop defragmenting indexes

texts:
I have several vendor databases that have most (99%) of the tables clustered on GUIDs. Currently we are rebuilding the tables every weekend. Most tables are 80% or more fragmented by this time.

Instead of this endless cycle, would we be better off just not rebuilding?

Would it be more beneficial to update statics more often instead of rebuilding the indexes?

I have read Stop Worrying About SQL Server Fragmentation by Brent Ozar, but it doesn't really give an answer to my question. I was hoping for a more complete article and possibly one that was updated more recently.

I am trying not to do this as I know it is not necessary. If it helps to understand my situation better please see the beginning part of this article again from Brent's blog. I would like to have a formal plan with articles and use cases for this to propose to management and go through the proper documentation before making the change.

I am working to design a new maintenance plan for our production databases. Our only maintenance is the weekly rebuild. There are several (30+) databases that are all about 700 GB-1.2 TB. The tables range from 100-300 GB in size for the largest.

I would like to have a better maintenance plan that provides better performance in the end.


texts: How to Set Up Sync of two mysql databases without master slave

texts:
The purpose is to create the live database replication for staging server but with masking of personal identifiable information like email, mobile, passwords etc.

This has to be done once daily, not real time like master slave. Also, when the new rows inserted, they all should be masked like previous ones before insertion.

One way i found is using the binary logs of the mysql, but they can be inserted first, and then masked, which is not required.

Any other suggestion?

Database size is more than 40gb


texts:
Problem:
On one of the production boxes, TempDB transaction LOG file grew unexpectedly, filling the drive
It was 32 GB but now it is 240 GB
Questions:

What is the mechanism of TempDB log file usage ?

TempDB is in SIMPLE recovery model, what can delay TempDB log file truncation
(and cause its growth as a result) ?

How can I know or be alerted when TempDB data files or log file are growing ?

Is there an Extended Event for this, or other means to know, and can alert be sent by email when growth happens ?

Performance Monitor metrics

Log file may be 240 GB, but it is really empty at the moment
Is there a metric that can be used to monitor amount of used space INSIDE the TempDB data/log files at any given point in time ?
I can see there are Active Temp Tables, Temp Tables Creation Rate, Worktables Created/sec, etc. but these do not show exactly what I would like to see

Drive space monitoring

Is there native tool in SQL Server or Windows, that can alert me, when the free space on the TempDB drive reaches below 20%, 10% ?

How to find out historically which session or task filled the TempDB data or log file ?

I can see which user/system sessions CURRENTLY consume at TempDB by querying this: sys.dm_db_session_space_usage. But how can I know, which user/task consumed large portion of TempDB, say, 2 hours ago, without having to sit and query that view ?

sys.dm_db_session_space_usage

user_objects_alloc_page_count - (minus) user_objects_dealloc_page_count = the difference will be what session is CURRENTLY consuming (in pages) in TempDB ? Or it does not work that way ?
If you know at least few of those questions, your help is much appreciated


texts: Database Directory storage is full need to change link it to new Partition

texts:
My mysql Database Directory size is full. So I want to change it to another partition where there is large amount of space. The database version am using is MySQL 5.7.23 and OS is CentOS 7.

After searching internet i got 2 solutions. First one as follows:

Firstly,I created a Systemlink pointing to the new drive. Then I tried to start mysql, it fails everytime when I try to start. On removing systemlink it will start.

The error am getting is

[Note] InnoDB: Highest supported file format is Barracuda.
2018-08-21T10:08:04.839330Z 0 [ERROR] InnoDB: Operating system error number
13 in a file operation.
2018-08-21T10:08:04.839349Z 0 [ERROR] InnoDB: The error means mysqld does
not have the access rights to the directory.
2018-08-21T10:08:04.839360Z 0 [ERROR] InnoDB: os_file_readdir_next_file()
returned -1 in directory ./, crash recovery may have failed for some .ibd
files!
2018-08-21T10:08:04.839385Z 0 [ERROR] InnoDB: Plugin initialization aborted
with error Generic error
2018-08-21T10:08:05.440529Z 0 [ERROR] Plugin 'InnoDB' init function returned
error.
2018-08-21T10:08:05.440597Z 0 [ERROR] Plugin 'InnoDB' registration as a
STORAGE ENGINE failed.
2018-08-21T10:08:05.440610Z 0 [ERROR] Failed to initialize builtin plugins.
2018-08-21T10:08:05.440617Z 0 [ERROR] Aborting

2018-08-21T10:08:05.440665Z 0 [Note] Binlog end
2018-08-21T10:08:05.440770Z 0 [Note] Shutting down plugin 'CSV'
2018-08-21T10:08:05.441349Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

Secondly, https://www.digitalocean.com/community/tutorials/how-to-move-a-mysql-data-directory-to-a-new-location-on-ubuntu-16-04 I followed this url and after that when tried to start mysql keep on getting this error Cant connect to localhost (111) (Error: 2003). This time nothing is showing in mysqld.log file


texts: Unable to connect to postgresql EC2 instance from VPN

texts:
I have an EC2 instance of timescaledb running on AWS. For security purposes, my team members and I are using OpenVPN so that we have access to it and the same IP address has been defined in the inbound rules of Security group of the EC2 instance

Type         Protocol   Port range    source
Postgreqsl      TCP        5432        <my vpn ip address>
Postgreqsl      TCP        5432       172.31.32.0/20
SSH             TCP         22         <my vpn ip address>

and there are no outbound rules.

Now, I enter into the VPN and launch the instance and do

sudo su postgresql followed by

psql -h ec2-xx-xxx-xxx-xxx.eu-central-1.compute.amazonaws.com -d mydb -U myuser

I get the following error:

  psql: could not connect to server: Connection refused     Is the server
  running on host
  "ec2-xx-xxx-xxx-xxx.eu-central-1.compute.amazonaws.com"
  (172.31.40.110) and accepting     TCP/IP connections on port 5432?

I am checking the postgresql.conf file and it has the following:

# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
                                        # (change requires restart)
#unix_socket_group = ''                 # (change requires restart)
#unix_socket_permissions = 0777         # begin with 0 to use octal notation
                                        # (change requires restart)
#bonjour = off                          # advertise server via Bonjour
                                        # (change requires restart)
#bonjour_name = ''                      # defaults to the computer name
                                        # (change requires restart)

and the pg_hba.conf has

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
host    all             all             0.0.0.0/0               md5 #ipv4 range
host    all             all             ::0/0                   md5 #ipv6 range
host    all             all             all                     md5 #all ip

Can anyone please tell me what is the mistake i am commiting here and if i am missing something?


texts: MySQL service stops after trying to grant privileges to a user

texts:
I am using MySQL 5.6, I was trying to create a super user for specific database but I got

ERROR 1396 (HY000): Operation CREATE USER failed for 'superuser'@'%'

Then I thought maybe there is already a user with the same name and password. I flushed privileges and run the following query:

grant all privileges on db1.* to 'superuser'@'localhost'

After that query I get: ERROR 2013 (HY000): Lost connection to MySQL server during query
and MySQL service stops working.


texts: Optimization of a big MySQL weather archive table

texts:
I have a table that has an archive of daily weather conditions around the globe. There are around 150 million rows and the table is 1.7Gb excluding indexes.

The table is an archive so there are no writes to it. It is a MyISAM table.

Queries are showing up in my slow log as taking up to 45 seconds. These are well cached by the website, but this seems excessive.

CREATE TABLE `archive` (
  `latitude` float NOT NULL,
  `longitude` float NOT NULL,
  `condition` decimal(3,1) NOT NULL,
  `month` tinyint(4) NOT NULL DEFAULT '0',
  `day` tinyint(4) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `archive`
  ADD KEY `longitude` (`longitude`),
  ADD KEY `month` (`month`);

I have indexes on longitude and month (as I usually run a monthly average for a location). Just to note - I can't have a primary index on just longitude and latitude as these are all repeated for each day and month.

Here is an example of one of the slow queries (actually the only query type):

SELECT AVG(condition) AS average, MIN(condition) AS min_temp, MAX(condition) AS max_temp FROM `archive` WHERE `latitude`=42.75 AND `longitude`=132.75 AND `month`=4;

Server info:

64Gb RAM
key_buffer_size= 256M

Any thoughts on how I can speed things up? I have considered breaking the table up but am not sure on the best way forward.

Thanks.


texts: What is this expression changing process?

texts:
Consider this table definition:

CREATE TABLE [dbo].[Post]
(
    [Id] INT IDENTITY(1,1) NOT NULL,
    [PostType] VARCHAR(10) NOT NULL,

    CONSTRAINT [CK_Post_PostType] CHECK ([PostType] IN ('Question', 'Answer', 'Comment'))
)

If I run that and then look in sys.check_constraints:

select [definition]
from sys.check_constraints
where [name] = 'CK_Post_PostType';

This is the output:

([PostType]='Comment' OR [PostType]='Answer' OR [PostType]='Question')

So it changed my "in" statement to a series of "or" statements instead.

The same thing is true of (at least)

BETWEEN (gets changed to OR statements) and
CAST (gets changed to CONVERT).

I realize this isn't a functional problem in SQL Server, since the expressions are logically equivalent.  But it causes issues with schema compare tools like SSDT, because the source code is out of sync with the deployed code.  I've blogged in more detail about the problem here: SSDT problems: deploying the same change over and over

Is there a name for this conversion process / behavior?  Is there any documentation for the different conversions that can occur, so we can plan for them (or try to avoid them)?


texts: Is PostgreSQL replication production ready?

texts:
How does PostgreSQL native replication compare to MySQL?

I know asynchronous replication has been supported for longer than sync, which is recent. Is synchronous reliable to be used in real projects?


texts: What would be better long term: Creating many documents in MongoDB or fewer documents with nested arrays

texts:
I have a scenario I'm debating for my app.

Let's say I have a Quiz application with a MongoDB structure that looks like this:

{
_id: xxxxxx,
userId: xxxx,
quizData: [
{
name: xxxx,
quizId: xxxx,
questions: [...]
},
{
name: xxxx,
quizId: xxxx,
questions: [...]
}
...]}

I'm torn between what's better long-term for my application: let each user have just one document and for each quiz he/she creates, it will be added to the "quizData" array of objects...OR...let each user's quiz have its own document in the MongoDB, where I create the above structure for each quiz (obviously, the "quizData" will only contain one object instead of an array of many).

Theoretically, the user can create as many Quizzes as they want, but realistically, that probably won't be the case. I fear that if I let each user have it's own document for each quiz in the MongoDB, it will populate the collection fast and over time (with many users doing the same), it will slow down the querying process.

Anyone have any thoughts on the best way to go about this?


texts: Why would Vertica let me start a transaction if I can't roll it back?

texts:
I run the following statements against a Vertica database, one at a time:

BEGIN TRANSACTION;

UPDATE table
SET col1 = 'something'
WHERE col2 = 'something else';

SELECT COUNT(*)
FROM table
WHERE col1 = 'something';

ROLLBACK TRANSACTION;

I run the first line fine... OK, now I'm in a transaction.

I run my update... OK, that worked.

I run my SELECT test to make sure things worked as expected... Oh wait, it looks like I missed a condition in the WHERE clause of my UPDATE statement.

No worries! That's why I did this in a transaction.

Let's rollback:

=> ROLLBACK TRANSACTION;

[Vertica][JDBC](10040) Cannot use commit while Connection is in auto-commit mode.

Jones, hand over that roll of toilet paper you have on your desk.

So Vertica happily accepted my BEGIN TRANSACTION, knowing full well that very soon after that I would try to run either a ROLLBACK or COMMIT.

Yet, I can't! My connection is in auto-commit mode, so ROLLBACK and COMMIT mean nothing. My UPDATE was committed the moment it completed.

Did I miss something, or am I right in thinking this is just a very bad implementation on Vertica's part?

Why would Vertica accept a BEGIN TRANSACTION on a connection in auto-commit mode if the logical consequences (ROLLBACK or COMMIT) are illegal?


texts: In Postgres, how can I calculate cume_dist (relative rank) over the entire table, but only for a single row?

texts:
I have a table with the following schema:

CREATE TABLE foo (
  data  TEXT   NOT NULL,
  count BIGINT NOT NULL DEFAULT 1,
  PRIMARY KEY (data)
);

I'd like to get the relative rank of a single entry, based on count, as it relates to the entire table, but I'm not sure how to structure the OVER clause in order to do this. Running the following gives me a rank of 1, so I'm assuming the OVER clause is getting limited to a single row, rather than all the rows in the table, as dictated by the WHERE clause.

SELECT cume_dist() OVER(ORDER BY f.count ASC) AS rank
FROM foo AS f
  WHERE f.data = 'bar';

rank
------
    1
(1 row)

Thoughts?


texts: MySQL Workbench: Custom fields for Relationships

texts:
I have to manage an existing DB, created "manually" without mysql-wb.

I want to draw the printable ER diagram.

When I create a relationship, workbench creates its own external key fields.

How can I create the relationship binding to my own fields?


texts:
Is there a general Selectivity rule of when to apply Nonclustered index?

We know not to create an index on a bit column, 50/50.   "Rows with 50/50 distribution, it might buy you very little performance gain " Index Bit Field in SQL Server

So how selective should a query be in SQL Server, before an index should be applied? Is there a general rule in SQL Server guidelines? At 25% average selectivity distribution in a column? 10% Selectivity?

This article is stating around 31%? How Selective Should Index Be?


texts: Can Oracle Database run on Microsoft Nano Server?

texts: Microsoft Nano Server is attractive alternative to a heavier installation of Microsoft Windows Server 2016. Can Oracle Database be prepared to run this way?


texts: Find direct connections to database server instead of SQL Server Always On Listener

texts: We recently created a SQL Server Always On Availability Group. I have told developers to change their application's connection strings from main server to the listener. I want to check which applications (or servers) connect to main server and which applications connect to the listener. How can i do it?


texts:
I need to filter data in 1 column and have an output of three columns (Column A,Column B and Column C).

The output columns should display values of 11 for column A, values of 12 for column B, and Total of A & B for column C.

The current query I have written:

select count(zcsms_studentgender) as MaleStudents,
       count(zcsms_studentgender) as FemaleStudents,
       count(zcsms_studentgender) as TotalStudents
from zcsms_studentdefinition
where (zcsms_studentgender = '11' or zcsms_studentgender = '12')
and zcsms_studentactive = 'Y';

I can't seem to add filtering into the first two columns (MaleStudents & FemaleStudents) to display only count values of 11 (male) and 12 (female).

It's a report based on student gender. The value for male students is 11, the value for female student is 12. These are stored in 1 table, 1 column. My output has to filter these into a report that will display as Column A (Male Students count) Column B (Female Students count) and Column C
(total count of A and B).


texts:
When setting

sql_mode=""

in  /etc/mysql/my.cnf  server leaves this variable in it default value:

mysql> show variables like 'sql_mode';

+-------------------+------------------------------------------------------------------------+
| Variable_name | Value                                                                                |
+-------------------+------------------------------------------------------------------------+
| sql_mode         | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION  |
+-------------------+------------------------------------------------------------------------+

However, if i set it manually in mysql shell:

set global sql_mode="";

server sets it as desired - to empty value. Till next server restart, when server sets this variable to default value again.

Tried in single and double quotes - no matter.
No errors in error log. No other default configuration files of mysql loaded
What's wrong?

Why?


texts: I need to make a structure for a mysql database

texts:
I have a table that saves users:
Email || Username || age || password
and I need to save the series they have seen and the ones they are watching and as I have read it is not correct to insert two columns with vectors that save the series, how can I do it?


texts: SQL function which returns true/false depending on success of insert query

texts:
I want to write a SQL function which returns true if a certain INSERT inserted a row and false if no row was inserted. In this use case, we know that at most one row can be inserted.

I came up with the following solution:

create function test() returns boolean as
$$
    with
    dummy as
    (
        insert into    ...
        select         ...
        from           ...
        where          ... -- this will limit to at most one row
        returning      true
    )
    select exists(select true from dummy);
$$
language sql volatile;

This seems to work fine, but it is rather clumsy and verbose, in my opinion. Is there something better possible?

I first wanted to write the following:

create function test() returns boolean as
$$
    insert into    ...
    select         ...
    from           ...
    where          ...
    returning      true;
$$
language sql volatile;

The problem now is that not false but null is returned when no row was inserted.


texts: Recipe Health risk Database ERD structure

texts:
I have these tables:

First table(Recipe table),

  Recipe

  recipeid(PK,AI)

  recipename

  ingredients

Second table(Ingredients table),

  Ingredients

  ingredientid(PK,AI)

  ingredientname

  quantity

Then a 3rd one for display,

  Recipe_ingredients

  recipeid(FK)

  ingredientid(FK)

  quantity

How do i construct another table and its relation to the ingredient table to determine its nutritional value.

Should i add another column for nutrition on my ingredient table? Or create another table for nutrition which holds the ingredient id as FK.

  Nutrition

  Ingredientid(FK)

Say i am diabetic so i should avoid recipes with high amounts of sugar. So from ingredients... im confused where to move from there.


texts: What files of Mysql lib directory safe to be deleted and to be recreated with innodb_file_per_table settings?

texts: I have brand new mysql 8 setup and installed on a centos 7. Unfortunately I forget to set innodb_file_per_table. I would like to dump the database and recreate with the innodb_file_per_table settings. I have google and its say its safe to delete ibdata1 and ib_logfile0 and ib_lofile1. But I could see many other files in it example binlog.000001, binlog.index, .pem file. mysql.ibd, #innodb_temp,undo_001, undo_002, performance_schema, mysql, mysql.sock,mysqlx.sock, sys etc. My question is that which are the files I should delete to get a fresh setup with my new settings?


texts: Transferring grants from one mysql DB to another

texts:
I would like to migrate a mysql DB from one server (Server A) to another (Server B). I took a Mysqldump and imported into the B.

Now, I would also like to export all the grants from A to B, to do so I did the following:

Ran the following on server A and got all the grants:

mysql -u root -B -N -p -e "SELECT user, host FROM user" mysql

I specifically wanted the grants for user galaxy, readgalaxy and writegalaxy so I ran the following command on server A to get them:

mysql -u root -p -B -N -e"SHOW GRANTS FOR 'galaxy'@localhost";
mysql -u root -p -B -N -e"SHOW GRANTS FOR 'readgalaxy '@localhost";
mysql -u root -p -B -N -e"SHOW GRANTS FOR 'writegalaxy '@localhost";
mysql -u root -p -B -N -e"SHOW GRANTS FOR 'writegalaxy '@localhost";

I got the grants and then ran those grants against the server B like this:

GRANT USAGE ON *.* TO 'readgalaxy'@'localhost' IDENTIFIED BY
  PASSWORD '*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';

I get the following output:

Query OK, 0 rows affected (0.00 sec)

Ran the following on server B:

mysql> SELECT user,host,password FROM mysql.user;

But now when I try to see the grant that I allocated to user readgalaxy, I get the following error:

ERROR 1141 (42000): There is no such grant defined for user 'readgalaxy' on host 'localhost'

What's happening here?


texts: Determine if queries have small or large results

texts:
In the MySQL documentation for Query cache, specifically on the topic of query_cache_min_res_unit  "If you have a lot of queries with small results, the default block size may lead to memory fragmentation" & "If most of your queries have large results (check the Qcache_total_blocks and Qcache_queries_in_cache status variables)"

#my.cnf
query_cache_size = 20M
#SQL
SHOW STATUS LIKE "%qcache%";
Qcache_free_blocks  4250
Qcache_free_memory  13633864
Qcache_queries_in_cache 7027
Qcache_total_blocks 18381

Is it correct interpretation that total_blocks / queries_in_cache = average number of blocks for each cached query (in my case 2.6)? Would it make any sense to increase query_cache_min_res_unit from the default 4096?

edit
I don't believe the above interpretation is correct any more. A block can be any size between query_cache_min_res_unit (4K) to query_cache_limit (default 1MB) and each of query, result and table will consume a block. Therefore total_blocks / queries_in_cache shows the uniqueness of tables in queries - i.e as there are more queries on the same table, total_blocks / queries_in_cache would approach 2.
Two different queries using the same and only 1 table- (3 + 2 = 5 = total blocks) / (2 = queries in cache) = 2.5
Ten different queries using the same and only 1 table- (3 + 2 + 2 + 2 + 2 + 2 + 2 + 2 + 2 + 2) / 10 = 2.1

However I'm still struggling to reconcile this with my memory values as I would have thought that some approximation of the average query size would be given by the formula

 (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
 (20971520 - 13633864) / 7027 = 1044.2

Since 1044 is below the query_cache_min_res_unit, I must be wrong (or bad at maths, or both). And besides, this doesn't count for the each blocks consumed by the query and table(s). I think that a more accurate formula is below, but of course this gives a negative so again, something is off.

(
 (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache) -
 (Qcache_queries_in_cache - Qcache_queries_in_cache) * query_cache_min_res_unit
)

Conclusion so far
Very unlikely to change any settings as I can't make sense of it. I would be thankful if the average size of queries in cache can be determined from these values as it seems like it should be possible - but my numbers don't add up.


texts:
I have two tables:

SamplesTbl contains samples every 5 seconds of a bike ride
RxTbl contains the prescribed stages and other values of a bike ride

SamplesTbl

SamplesID int
SessionNum int
StageNum int
RunTime int

RxTbl

RxID int
RxNum int
RxStageNum int
RxRunTime int

In certain cases (not all) a prescribed bike ride is broken up into more stages than prescribed (5 stages are turned into 20 stages). Currently I use cursors and temporary tables to take these 20 stages to map them back to the 5 prescribed stages based on the number of stages it takes from the samples table to equal the prescribed run time.

For example, stage 1 is prescribed at 5 minutes but the actual exercise data would contain stages 1 through 5 that equal the 5 minutes (1-4: 65 seconds, 5: 40 seconds), stage 2 is prescribed at 3 minutes but the actual exercise data would contain stages 6 through 8 that equal the 3 minutes (1-2: 65 seconds, 3: 50 seconds), etc.

Is there a way to retrieve the result set without using cursors and/or temporary tables?


texts:
I'm trying to redefine a table to be partitioned.

What I did:

CREATE TABLE WERTEVAR_TMP(
    "WVTEIL" NUMBER(10,0),
    "WVMERKMAL" NUMBER(5,0),
    "WVUNTERS" NUMBER(5,0),
    "WVWERTNR" NUMBER(10,0),
    "WVWERT" FLOAT(126),
    "WVATTRIBUT" NUMBER(5,0),
    "WVPRUEFER" NUMBER(10,0),
    "WVPRUEFMIT" NUMBER(10,0),
    "WVMASCHINE" NUMBER(10,0),
    "WVNEST" NUMBER(10,0),
    "WVDATZEIT" DATE,
    "WVSTIANF" NUMBER(3,0),
    "WVCHARGE" VARCHAR2(20 BYTE),
    "WVAUFTRAG" VARCHAR2(20 BYTE),
    "WV0005" VARCHAR2(35 BYTE),
    "WV0009" VARCHAR2(255 BYTE),
    "WV0011" VARCHAR2(250 BYTE),
    "WV0014" VARCHAR2(40 BYTE),
    "WV0015" NUMBER(10,0),
    "WV0016" VARCHAR2(32 BYTE),
    "WV0017" VARCHAR2(32 BYTE),
    "WV0031" RAW(16),
    "MONITORING_VERSION" NUMBER DEFAULT 0,
    "WV0034" NUMBER(10,0),
    "WV0054" VARCHAR2(32 BYTE),
    "WV0055" VARCHAR2(32 BYTE),
    "WV0056" VARCHAR2(32 BYTE),
    "WV0057" VARCHAR2(32 BYTE),
    "WV0058" VARCHAR2(32 BYTE),
    "WV0059" VARCHAR2(32 BYTE),
    "WV0060" VARCHAR2(32 BYTE),
    "WV0061" NUMBER(10,0),
    "WV0062" NUMBER(10,0),
    "WV0063" NUMBER(10,0),
    "WV0080" VARCHAR2(64 BYTE),
    "WV0081" NUMBER(5,0)
)
STORAGE (INITIAL 65K NEXT 65K)
PARTITION BY RANGE (WVDATZEIT) INTERVAL (NUMTODSINTERVAL(1, 'day'))
SUBPARTITION BY HASH (WVMASCHINE) SUBPARTITIONS 8
(
    PARTITION P_BEFORE_2000 VALUES LESS THAN (TO_DATE('01-01-2000', 'DD-MM-YYYY'))
);

BEGIN
    DBMS_REDEFINITION.start_redef_table(
        uname         => 'CHYSTAT_STAGING_MONITORING',
        orig_table    => 'WERTEVAR',
        int_table     => 'WERTEVAR_TMP',
        options_flag  => DBMS_REDEFINITION.CONS_USE_ROWID
    );
END;

SET SERVEROUTPUT ON;
DECLARE
    l_errors  NUMBER;
BEGIN
    DBMS_REDEFINITION.copy_table_dependents(
        uname            => 'CHYSTAT_STAGING_MONITORING',
        orig_table       => 'WERTEVAR',
        int_table        => 'WERTEVAR_TMP',
        copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
        copy_triggers    => TRUE,
        copy_constraints => TRUE,
        copy_privileges  => TRUE,
        ignore_errors    => FALSE,
        num_errors       => l_errors,
        copy_statistics  => FALSE,
        copy_mvlog       => FALSE
    );

    DBMS_OUTPUT.put_line('Errors=' || l_errors);
END;

-- Complete the redefinition process
BEGIN
    DBMS_REDEFINITION.finish_redef_table(
    uname      => 'CHYSTAT_STAGING_MONITORING',
    orig_table => 'WERTEVAR',
    int_table  => 'WERTEVAR_TMP');
END;

But DBMS_REDEFINITION.finish_redef_table ends with:

Error report:
ORA-02448: constraint does not exist
ORA-06512: at "SYS.DBMS_REDEFINITION", line 105
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3520
ORA-06512: at line 2
02448. 00000 -  "constraint does not exist"
*Cause:    The named constraint does not exist
*Action:   Stop trying to do something with a nonexistant constraint

I would appreciate any hint.


texts: How to structure MySQL database tables for users and how to handle adding/removing permissions?

texts:
I am making a database structure for users, who can become part of a group, and within that group the admin of the group can assign as many permissions to a user as they would like.
This part seems easy. I have 4 tables:
 USERS           GROUPS             PERMISSIONS      PERMISSIONS_GROUPS
-------  ---------------------    --------------    -------------------
| UID |  | GID | UID(creator) |   | PID | NAME |    | UID | GID | PID |

I think this is the best way to handle this. I have a few questions about how to handle permissions for the creator of the group, and how to handle permissions if I were to delete a permission or add a permission.
For instance, when a user makes a group. Do I query all of the permissions and in a while loop insert a PERMISSIONS_GROUPS record for every row in the permissions table? If I add a new permission, do I have to query every UID from the GROUPS table and run a while loop to insert the new permission into PERMISSIONS_GROUPS? I just want to make sure I am not missing something simple here. For structure/scripting I am using MySQL and PHP.


texts: I have two servers. One is DB server another is RMAN Catalog DB server. From my RMAN Catalog, I connect to my remote DB server and I run the backup. Where will the backup files be stored? Will it be in my RMAN catalog db server or the source db?


texts:
I am attempting to enable slow query logging on our server in order to identify any queries that could use optimization. Sounds simple enough, however my file is not being written to. I get no errors or anything of the like, it just doesn't seem to be logging the slow queries. I have remembered to restart mysql after my config changes.

I am using MySQL Ver 5.1.61 . Here is what I have in my.cnf:

slow-query-log=1
slow-query-log-file=/var/logs/my.slow.log
long_query_time=1

the file /var/logs/my.slow.log has mysql as the owner, also in the interest of debugging I gave read/write to all on the log file.

I have the long_query_time set to 1 in the above as I just want to see if it is working. I have tried setting it lower(e.g 0.3) but I'm still not getting anything logged. I know that the queries my app are running take longer than 1 second and I've also ran intentionally log queries (SELECT sleep(10);) in the terminal for testing and the log is still empty.

I've looked through the docs, from what I can see this should be working. Anyone have any suggestions as to what I am doing wrong? Any advice would be appreciated, thanks much!

Edit: As asked in comments I ran the query:

 `SELECT variable_value FROM information_schema.global_variables WHERE variable_name IN ('slow_query_log','slow_query_log_file','long_query_time');`

The result:

10.0000000
/var/run/mysqld/mysqld-slow.log
OFF

Obviously my configuration changes are not being taken into account as I believe these are defaults. I am quite certain the my.cnf file I am altering is being parsed as if I put in an invalid value mysql will error on restart. What could be going on here?

Another Edit:

After taking @RolandoMySQLDBA 's advice and moving my slow query config lines to under [mysqld] my settings seem to be saving. Now the result of the above variable_value query is :

1.0000000
/var/logs/my.slow.log
ON

However I am still not seeing the file my.slow.log being written to. I don't think it is a permissions issue as the file is owned by mysql and I've added all permissions for all users on the file. Could anyone think of a reason why this would not be working?

Edit: Solved! The path to the slow query log was incorrect, it should have been /var/log/my.slow.log instead of /var/log*s*/my.slow.log . Thanks to all for the help, I've learned allot!


texts: Suppress repeated performance condition alerts

texts:
I have set SQL Server performance condition alert with details as follows:

Object           : SQL Server:Databases
Counter          : Percent Log Used
Instance         : My database name
Alert If counter : rises above >25
Delay between responses 10 minutes.

Please let me know what action I should take so that I do not get this alert again.

The hard drive where the transaction log gets saved has enough space, and recovery model is FULL.

What I want to ask is How can I find out why I started getting alert suddenly. My database full backup schedule on Sunday ,differential backup  scheduled daily and transaction log backup daily after 10 min. Where should I check to find out the cause of the issue. Auto growth property is set to 5120 MB and restricted to 86000 MB. All backups like Full,daily,transaction log backup are taking place correctly . My transaction log backup size is 1.08 MB ,Differential backup size around 8 GB  . From last Sunday I started getting alert . Till that there were not alert.
One more thing I would like to mention that I am also taking backup for TempDB. TempDB log file  size  is 5 GB. Is it the reason the  alert


texts: Identify rows that match on a key but differ in other columns

texts:
Supposed I have table temp A and table temp B:

IF OBJECT_ID('tempdb..#A') IS NOT NULL
BEGIN DROP TABLE #A END

IF OBJECT_ID('tempdb..#B') IS NOT NULL
BEGIN DROP TABLE #B END

CREATE TABLE #A
(
   ID Int,
   Descr VARCHAR(100),
   Qty Int
)

CREATE TABLE B
(
   ID INT,
   Descr VARCHAR(100),
   Qty INT
)

INSERT INTO #A VALUES (1, 'Data 1' , 10)
INSERT INTO #A VALUES (2, 'Data 2', 5)

INSERT INTO #B VALUES (1, 'Data x' , 8)
INSERT INTO #B VALUES (2, 'Data x' , 1)

The ID is a key in this case. The problem is, how we can fetch the column value differences and showing the column differ on our select query?

I have tried the following:

using except

SELECT * FROM A
EXCEPT
SELECT * FROM B

using join and case.. when

select Case When #A.Descr = Before.Descr THen Null Else #A.Descr End,
       Case When #A.Qty = Before.Qty THen Null Else #A.Qty End
from #A
Left Join #B Before on #A.ID = Before.ID

This query shows me if different values exist then it will show the differences column, else if no difference value found on certain column, then will show null value.

Can you recommend a better solution?


texts: Service Broker activation procedure in C# with external dependencies

texts:
I am not entirely sure if I have come to the right place, because this question involves both SQL Server and .Net. Apologies if this is not an appropriate place for my question!

I am currently working on an in-house tool to transfer data from our
ERP system to our SharePoint. Specifically, my boss wants the closing
of a project in the ERP system to trigger a workflow in SharePoint.

My current line of thinking is to use the SQL Server Service Broker -
create a T-SQL trigger on the projects table in the ERP database to
send a message whenever a project is closed. The message would like
carry an XML payload with some information on the project.
I have done something very similar before, so that should not be too
much of a problem.

In the next step I would have to receive these messages, extract the
information from the XML payload and create entries in a list in our
SharePoint server.
Adding items to a SharePoint list from a C# program is also something
I have done before, so that should not be a problem either.

My question is how I should approach the transfer from the Service
Broker Message Queue to the SharePoint list.
My first idea was to write a stored procedure in C# to receive
messages from the queue, extract the relevant information from the XML
payload, and then create a list item SharePoint. (Stored procedures in C# is not something I have done before!)

As far as I could find out, this should not be a problem per
se. However, it would require access to the SharePoint client
assemblies from the stored procedure.
Also, I would like to do some logging, and being a lazy slob, I would
like to use Log4Net for that. So I would need access to two
assemblies. (More actually, I think the SharePoint client library is
split up into several assemblies.)

Can I just load these assemblies into the SQL Server along with the
one for my stored procedure, and then reference them from within the
SQL Server? If so, are there any pitfalls, things I need to keep in
mind?
Or can I just install the required assemblies on the database server
and reference them from within my stored procedure?
Another option just comes to my mind - I could use Fody Costura, I
think, to massage the additional assemblies into the one carrying my
stored procedure... Are there any significant downsides to this?

The alternative, of course, would be a program running externally from
the SQL Server. This seems in some ways to be a better alternative -
the problem with the SharePoint and Log4Net assemblies
disappears. Also, I could deploy the resulting program on an arbitrary
machine.

In that case, though, I am not sure how to interact with the Service
Broker and the Message queue. Can I just issue a RECEIVE statement via
an SqlCommand instance and extract the fields like from a regular
SELECT statement?
Would I have to finish the conversation? I have found some hints to a
Service Broker API, but where do I get the assemblies?

So, my questions, in short, are:
- Can I reference other assemblies from a CLR stored procedure?
- I so, how? What are the pros and cons?
- If now, how do I speak to the Service Broker from an external
  program? Do I need other assemblies besides the SQL Server Client?

Thank you very much for any insight you might be able to share with
me,
Benjamin


texts: Simplifying the "repetitive" code of a query involving UNION ALL, WHERE and LIMIT

texts:
How can I simplify the code of the query shown below? Requirements are always the same as I describe as follows:

(select * from questions where points = 3 and type = 1 order by rand() asc limit 6)
union all
(select * from questions where points = 2 and type = 1 order by rand() asc limit 4)
union all
(select * from questions where points = 1 and type = 1 order by rand() asc limit 2)
union all
(select * from questions where points = 3 and type = 0 order by rand() asc limit 10)
union all
(select * from questions where points = 2 and type = 0 order by rand() asc limit 6)
union all
(select * from questions where points = 1 and type = 0 order by rand() asc limit 4)

Is there any way to do it without UNION ?

I am just curious if there is a faster/more efficient way of doing it. SQL Server or MySQL, I haven't decided yet, because I am at design stage right now.


texts: Is there slowdown inserting into an InnoDB table that has no index set?

texts:
I have an old application with lots of InnoDB tables, that have no indexes at all, not even a primary ID or such.

Those tables only contain a few thousand rows.

Would it be faster to INSERT data into these tables if I would set a primary index (that I don't need otherwise)?


texts: Do Inactive MySQL Databases Consume Memory?

texts:
I have inactive databases that are on the server just for archive purposes.

I know I can do mysqldump for all of them, but I am not 100% sure they are inactive.

When I started my system I didn't have a way to determine active/inactive. If there are no queries on them, will they still consume memory ?


texts: Table with relationship to multiple tables

texts:
I want to persist contact details for persons. This contact details can belong to a client, or a next of kin member, or whatever other entity. I'll be using Doctrine2 in a Symfony project, that will handle the data integrity.

It is a good practice to have such contact table which stores contacts for any kind of 'parent' entity or should a I have different tables like client_contact & next of kin?


texts: Rate-limiting query execution in a batch process

texts: I have a set of queries in my queries.sql file which I am feeding to psql for execution. But I don't want them to run instantly one after the other: I would like to set some sort of delay - I was looking at \watch and query buffer but I am not so sure how to proceed. I would prefer not to write PG_SLEEP after each of my queries.


texts:
I have a MariaDB database, in which I have this table:

ID | Item Name | Item replaced |
-----------------------------------------
0  | Item 1     | Replaced
1  | Item 2     |
2  | Item 3     | Replaced
3  | Item 3     |
4  | Item 3     |
5  | Item 1     | Replaced
6  | Item 2     | Replaced
7  | Item 2     |
8  | Item 3     | Replaced

I want to count the number of same items and display so ITEM 3 in this case would be '4' as there are four of ITEM 3 in my database but I also want to show how many are replaced so in this case it would be '2' as only two of ITEM 3 is marked as replaced.

End result I want to get is something line

ITEM 1        2 - 2 (2 item 1's and both are are replaced)
ITEM 2            3 - 1 (3 Item 2's and only 1 is replaced)
ITEM 3            4 - 2 (4 Item 3's and 2 are replaced)

I have tried a UNION but that doesnt seem to work for me.

Also it would be helpful if I could count replaced as NULL or Empty.

I found this answer on SO but it's not quite what I want, this case the user is listed more than once so this example wouldnt work for me.


texts: Recursive update for tree structure in PostgreSQL

texts:
I have the following structure of table.

id     chNum        parentid
---    ------       ---------
1        1            NULL
2        1.1          1
3        1.1.1        2
4        2            NULL  (Here add the new row)
5        3            NULL
6        4            NULL

After added new row in between the rows, this and all the following rows (based on chNum) should change.

Result as follows:

id     chNum        parentid
---    ------       ---------
1        1            NULL
2        1.1          1
3        1.1.1        2
4        2            NULL
7        3            NULL --\
5        4            NULL -- + -- these rows are changed
6        5            NULL --/

Is this possible to use recursive update SQL query in pl/pgsql and is this best way for faster data update than through application side?

Sample scenarios for "chNum" auto update:


texts:
I need to create a job that restores all user databases from UNC to my SQL Server.

All the backups, for 3 days, are located in the same folder and I need to restore the last modified backup.
What is the right way to do this? Build steps to all databases separately?

I need a suggestion on how to build it.


texts: Several Questions about SGAM & GAM & IAM & PFS in SQL Server

texts:
Have several questions about internal structure of SQL Server.

Lets assume that we have a table in a database.

SGAM & GAM pages keep track of shared & uniform extents within a single GAM interval(~4GB), and help us find appropriate type of extent when allocating pages/extents(initially first 8 pages are allocated from mixed extents & positions of those pages are recorder to IAM pages, and after that from GAM extents(uniform extents) and this information is stored in next chained IAM page).

Let's say, pages for the table were allocated, and table size is more than 8 pages, and sometime later, rows were deleted, leaving some re-usable space. How SQL Server knows that extents have free space ?

Pages have information about the size of free space, but it is too time consuming to check every page. Bits in SGAM & GAM pages don't tell us anything beside that extend is allocated or has some free pages(entire page is free, not partially).
File header & SGAM & GAM & IAM pages are among the first pages within a file. What data-structure actually points to them ?


texts: Indexing to support advanced search queries

texts:
I have a Person table 2.5 million rows, typical demographic fields.  Need to support an advanced search screen that allows up to 10 search params (f/l name, phone 1 through phone 6, email, nickname, etc.) all varchar fields.  The 3 name fields are a real puzzle -  all need to support partial string matches, front and back LIKE (% @LastName %) or something like that.  They can supply just one param, or all ten, or any combination.
So, forgetting about sniffing for a minute, I just want to design the best possible index(es) to support these searched.  Tried a number of things, what is working best is an index with all 10 fields (see below), leading with the one most likely to be filled in.  It's "just ok" performance wise, and I'm wondering if I'm missing something.
CREATE NONCLUSTERED INDEX [IN_Person_Search] ON [dbo].[Person] (FnameLname, LnameFname, NickName, Phone1, Phone2, Phone3, Phone4, Phone5, Phone6, Email)
I realize the answer might be wrapped up in the SELECT statement, so a quick word on that - I'm using the "Email = @Email OR @Email IS NULL" method to deal with empty params.


texts: MongoDB - sharding an collection with data already in it

texts: I'm using Mongo (shell version 2.6.12) in a clustered configuration.  Right now I have a few collections sharded, and was looking to shard another collection.  This collection already has data in it.  Once I run the command to shard the collection and give it it's key, will the cluster take the existing data and spread it across the multiple systems in my cluster, or will the existing data stay put on one server and any new data will then be spread across the other systems?


texts: Application == session == connection in DB2?

texts:
Can I consider as synonyms Applications, Sessions and Connections in DB2 LUW?

In db2top, I see "Sessions". There is a command "db2 force applications", views and other stuff. And from the developer point of view it is a "connection"

May I use them interchangeably when talking about connections?


texts: Backup from Azure SQL Managed Instance and restore on an On-Prem version

texts:
As per my understanding Azure SQL MI runs SQL Server under the hood. So a backup from MI can be restored on latest version of SQL Server (2019). I have a task to find a rollback strategy if the move to MI is called off after the migration.

Took backup from MI
Tried restore to SQL Server 2019 (15.0.2070.41)

I expected it a 50/50 chance of success but it failed. The error however is a bit misleading :

  Msg 3169, Level 16, State 1, Line 2 The database was backed up on a
  server running version 15.00.2000. That version is incompatible with
  this server, which is running version 15.00.2070. Either restore the
  database on a server that supports the backup, or use a backup that is
  compatible with this server. Msg 3013, Level 16, State 1, Line 2
  RESTORE DATABASE is terminating abnormally

If we can't go back smoothly to server we got the backup from then how do we plan the rollback. The bacpac/dacpac method is a it of pain when db size is 30GB and you get loads of errors.

Why is that error coming up? Need to know whats running in MI
Shall we reconsider moving to MI, with the pain of not having an easy/smooth rollback.

PS: AWS RDS provides versions which makes it much better as you have the option to just rollback if something goes wrong in the cutover.

.


texts: dbo.M7_* objects (stored procs) in my production database. What product are these from?

texts:
I have inherited a database from a predecessor who is long gone... There are about a hundred stored procs in the database that all start with dbo.M7_   They appear to be something to do with source control of some sort, but I have no idea what product they are associated with.  I don't see any connection between them and any other table/proc.  I would like to remove them from my system but I need to understand why they were there first.  No one who is still here remembers what they do, they just remember them showing up in the database one day.

Does anyone know what they are?


texts:
I would like to know how to check the growth of entire database and individual data files.

Also, what to suggest to the client if the database is growing rapidly?


texts: SQL Server 2008 R2 DDL Trigger to create DML Triggers on all new user tables

texts:
As the title says I would like to create a DDL trigger which in turn creates DML triggers for most new tables.

The trigger I have so far will create triggers on ALL tables, which is not what I'm after:

CREATE TRIGGER [CreateReplTriggers] ON DATABASE
    FOR CREATE_TABLE
AS
BEGIN
    SET NOCOUNT ON
    SET ANSI_PADDING ON
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON

    DECLARE @TABLE_NAME SYSNAME

    SELECT @TABLE_NAME = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')

    EXEC sp_CreateReplTrigger @TABLE_NAME
END

sp_CreateReplTrigger is a stored proc which creates DML trigger for table with passed name.

Unfortunately this fires off when some System Tables are created as well, e.g. dbo.MSreplication_subscriptions.

There doesn't seem to be anything useful in EVENT_DATA() which would help me differentiate user tables from system tables.

There's also many System Tables and their names don't have much in common: http://msdn.microsoft.com/en-us/library/aa260604(v=sql.80).aspx ... I can't exclude table that have 'MS' or 'sys' in name, because that would be hacky and bad, and would definitely cause issues 5 years later when someone forgets that user tables aren't supposed to have 'MS' or 'sys' in name.

Edit:

The final solution was a combination of the answer below + adding a whitelist array or table. So the stored proc was only called if the table name was in the whitelist. is_ms_shipped was not used.


texts: Error code 1054: Unknown column when inserting data into a table

texts:
I have been trying to join data from two separate tables with distinct columns into a third table, but

  Error code 1054: Unknown Column 'Company' in field list

keeps popping up.

My current code is:

INSERT INTO Company (Year, Company, Revenue, Profits)
(select Year, Company, Revenue, Profits from
(select Year, Comp_Name from Comp_Info
union
select Revenue, Profits from Comp_Financials)
AS tempTable);

Comp_Financials table:

Comp_Info table:

Both tables will be combined into the already created table:
Company (ID int auto_increment, Year int, Company varchar (20), Revenue int, Profits int, Primary key (ID))

Any help is greatly appreciated!


texts: Getting the percentage of a result set for same ID rows

texts:
I have the below table obtained as an output using the query provided below that.

;WITH cte AS (
       SELECT c.CaseID AS 'Case #',
       m.ManufacturerName,
       ou.OutcomeName
FROM Consumes con
INNER JOIN [Case] c
ON con.FKCaseID = c.CaseID
INNER JOIN Manufacturer m
ON m.ManufacturerID = con.FKManufacturerID
INNER JOIN Case_Outcome oc
ON oc.FKCaseID = c.CaseID
INNER JOIN OutCome ou
ON oc.FKOutcomeID = ou.OutcomeID
)

SELECT c.[Case #],
c.ManufacturerName,
       STUFF((SELECT ','+OutcomeName
       FROM cte
       WHERE c.[Case #] = [Case #]
       FOR XML PATH('')),1,1,'') as OutcomeName
FROM cte c
GROUP BY c.[Case #],c.ManufacturerNAme

For all the manufacturers, I need to list down the # events occurred due to their drug and the percentage of each outcome occurred.

How can I get this? I used the below query but it doesn't give the proper output percentage.

SELECT
  m.ManufacturerName,
  COUNT(c.CaseID) AS '# Events',
  COUNT(CASE WHEN ou.OutcomeName = 'Death' THEN c.CaseID ELSE NULL END) /COUNT(c.CaseID)*100.0 AS 'Death Events',
  COUNT(CASE WHEN ou.OutcomeName = 'Hospitalization' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Hospitalization Events',
  COUNT(CASE WHEN ou.OutcomeName = 'Life Threatening' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Life Threatening Events',
  COUNT(CASE WHEN ou.OutcomeName = 'Disability' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Disability Events',
  COUNT(CASE WHEN ou.OutcomeName = 'Congenital Anomaly' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Congenital Anomaly Events',
  COUNT(CASE WHEN ou.OutcomeName = 'Required Intervention' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Required Intervention Events',
  COUNT(CASE WHEN ou.OutcomeName = 'Other Serious' THEN c.CaseID ELSE NULL END)/COUNT(c.CaseID)*100.0 AS 'Other Serious Events'
FROM Consumes con
   INNER JOIN [Case] c ON con.FKCaseID = c.CaseID
   INNER JOIN Manufacturer m ON m.ManufacturerID = con.FKManufacturerID
   INNER JOIN Case_Outcome oc ON oc.FKCaseID = c.CaseID
   INNER JOIN OutCome ou ON oc.FKOutcomeID = ou.OutcomeID
GROUP BY m.ManufacturerName

The above query gives either 0.0 or 100.0 when all the outcomes are of the same type. When there are different outcomes for the same case, this query doesn't work as expected. What am I doing wrong here? Any help would be much appreciated.


texts:
We have some sort of logging table where a record gets inserted whenever a thread is working on a certain topic. It's possible that two separate threads try to start working on the same topic. Therefore, the threads have to verify if a record of the same type already exists in the table. As soon as the threads finishes its work, it deletes the record so other threads can start working again. In other words, a transaction issues a select followed by an insert if no record was returned.

When running under Read Committed or Repeatable Read it's possible that 2 transactions select the same (not existing) record at almost the same time, don't get any record returned and try to insert a record which results in a duplicate key error.

When using Serializable, it's possible to place an update range lock and block the second transaction that checks for the record using WITH(UPDLOCK, HOLDLOCK) . Although I've noticed that whenever the table is empty, each other transaction will be blocked, including transactions that verify the existence of other records. The transactions try to get place a RangeS-U lock on resource (ffffffffffff) which represents the infinity key.

Does anyone know another way to prevent these duplicate inserts or prevent the read statement from locking the complete table?

Many thanks!

Demo setup

CREATE TABLE dbo.test
(
id INT PRIMARY KEY IDENTITY(1,1)
, RecordType INT
);

CREATE UNIQUE INDEX UIX_dbo_test_RecordType ON dbo.test (RecordType);

Transaction 1

BEGIN TRANSACTION

SELECT * FROM dbo.test WITH(UPDLOCK, HOLDLOCK, ROWLOCK) WHERE RecordType = 1

-- if no record gets returned insert
-- INSERT INTO dbo.test(RecordType) VALUES(1)

-- COMMIT

Transaction 2

BEGIN TRANSACTION

SELECT * FROM dbo.test WITH(UPDLOCK, HOLDLOCK, ROWLOCK) WHERE RecordType = 2

-- if no record gets returned insert
-- INSERT INTO dbo.test(RecordType) VALUES(2)

-- COMMIT


texts: How can SUSER_SNAME return the localized user name?

texts:
I need to assign network service user to SQL Server database programmatically, so I am running this script:

USE [MyDB]
GO

DECLARE @user nvarchar(50)
DECLARE @SQLStatement nvarchar(500)
SET @user = SUSER_SNAME(0x010100000000000514000000);
SET @SQLStatement =
N'IF NOT EXISTS(SELECT principal_id FROM sys.database_principals WHERE name = ''' + @user + ''')
  BEGIN
      CREATE USER ' + quotename(@user) + ' FOR LOGIN ' + quotename(@user) + N' WITH DEFAULT_SCHEMA=[dbo]
      ALTER ROLE [db_owner] ADD MEMBER ' + quotename(@user) + '
  END'
EXEC sp_executesql @SQLStatement;
GO

That does not work. The problem is that, when running

PRINT SUSER_SNAME(0x010100000000000514000000)

This is returned:

NT AUTHORITY\NETWORK SERVICE

That is wrong, since my system is in Spanish. When I add that user manually using SQL Server Manager and then I see the users in the database, that user is actually added as:

NT AUTHORITY\Servicio de red

Is there a way to add Network service account independent of language?


texts: Creating a database link from Oracle Cloud server to remote access database

texts:
I work for a small company that currently use Apex to run reports on our accounts system. Oracle is installed on our local server and part of the data is gained via a database link to an access database.

We are changing our server and I was interested in trying out the Always Free cloud service to recreate the above.

Can anyone give me any pointers on if/how you could connect the cloud service to an access database held on of local server?

Please let me know what crucial information is missing from my question - I am sure there is some!

Any advice would be gratefully received.

Cheers

Andrew


texts: Providers in SQL Server linked server configuration

texts: How 'allow in process' works for providers in SQL server linked server configuration ? It looks like it's unchecked by default. What is the requirement for keeping this option checked and What are the pros and cons of using it.


texts: Recovering Mysql - how to get a dumpfile from wampserver and none running db

texts:
I had the following:
- Wampserver/mysql/drupal + batch file with auto backup by use of mysqldump.
- Backup was distributed off and online.

then : my laptop was stolen :-( and I need to recover but... the size of the backup is zero bites aahhhhh. I have a good mysqldump until 20/09/2014

In need to have a mysqldump until 19/12/2014 and this is still available in database in the wampfolder. I needed to re-install wamp so I have two folders now:

d:\wamp with the new wampserver
d:\wamp_old with the old wampserver with the database until 19/12/2104.

How to get a dumpfile from the none running db?

Y


texts: Functional dependencies applies to whole database or to a particular relation?

texts: I am new to dbms. I saw a lecture video about functional dependencies. But I am confused a bit that whether functional dependency is a constraint put on whole database or on a particular schema?


texts: How can I select rows from a hierarchical query with the lowest level?

texts:
I have a hierarchical query in Oracle 11gR2 that returns something like this:

Parent (Level 1)

Child (Level 2)

Grandchild (Level 3)

Child (Level 2)

Grandchild (Level 3)
Grandchild (Level 3)

Child (Level 2)

The query I would like to write should get all the rows matching some predicate, for the minimum level; i.e. nearest the parent. For example, if one of the child rows matches the predicate, it should return just that row, irrespective of whether any grandchild rows match. If multiple child rows match, it should return all of them, again irrespective of grandchild rows. If no child rows match, it should return any grandchild rows that match, etc. (In the real system I have a lot more than three levels, and lots more rows per level.)

I assume this is possible with analytic functions, but I'm not sure which one to use, or how to integrate it into my query. I've seen similar problems solved using min (level) keep (dense_rank last order by level), but that doesn't seem to do quite what I want.


texts:
I try to setup High Availability using Spider Engine and MariaDB 10.0.14, but I a m not sure what configuration to use to make it work as expected.

What I want is:

Accessing a remote table using Spider Engine on a remote server A (let's say "main server" below)
If main server A is down => access "backup server" B

Based on the documentation, I set up the following configuration:

CREATE SERVER server_main
 FOREIGN DATA WRAPPER mysql
OPTIONS(
 HOST '10.2.0.1',
 PORT 3306,
 DATABASE 'db01',
 USER 'spider',
 PASSWORD '123456'
);

CREATE SERVER server_backup
 FOREIGN DATA WRAPPER mysql
OPTIONS(
 HOST '10.2.0.2',
 PORT 3306,
 DATABASE 'db01',
 USER 'spider',
 PASSWORD '123456'
);

INSERT INTO mysql.spider_link_mon_servers
(db_name, table_name, link_id, sid, server)
VALUES
('%', '%', '%', 100, 'server_main'),
('%', '%', '%', 101, 'server_backup');

SELECT spider_flush_table_mon_cache();

-- Created on 10.2.0.1 (server_main)
CREATE TABLE `np` (
  `s` text
) ENGINE=InnoDB;
INSERT INTO np VALUES ('main');

-- Created on 10.2.0.2 (server_backup)
CREATE TABLE `np` (
  `s` text
) ENGINE=InnoDB;
INSERT INTO np VALUES ('backup');

CREATE TABLE `np` (
  `s` text
) ENGINE=SPIDER COMMENT='wrapper "mysql", srv "server_main server_backup", database "db01", table "np", mbk "2", mkd "2", msi "100 101", link_status "0 0"';

Half the time spider uses server_main and server_backup:

db_spider =# SELECT * FROM np;
+------+
| s    |
+------+
| main |
+------+
1 row in set (0.00 sec)

db_spider =# Bye
[...]
db_spider =# SELECT * FROM np;
+--------+
| s      |
+--------+
| backup |
+--------+
1 row in set (0.00 sec)

But if I stop main mysqld instance, it still try to access the failed node:

db_spider =# SELECT * FROM np;
ERROR 1032 (HY000): Can't find record in 'spider_tables'
db_spider =# Bye
[...]
db_spider =# SELECT * FROM np;
+--------+
| s      |
+--------+
| backup |
+--------+
1 row in set (0.00 sec)

Maybe I misconfigured or missed some settings? Options "msi" and "link_status" are not very clear for me in fact...


texts: How to make Greenplum 4.2.3 only scan the intended partition?

texts:
When I uses unnest() in a View, and uses that View in a select statement, Greenplum seems to fail in only searching for the intended partition and search through all the partition of the main table instead. The same thing also apply when using a Subquery instead of a View.

For example:

Select something from (select unnest(an_array_field) from table_y)
where date = 'somedate' and state = 'somestate'

We currently have 2 different servers running 2 different version of Greenplum. Server A run on an older version (4.2.1) while the Server B run on 4.2.3. Running the same query above will result differently. Server A (old) will return the query in few seconds while Server B (new) will take forever to return. Running an Explain of the query shows that Server A only do scan on one of the partitions (with the date and state in the where clause) while Server B will do a Scan on each partition, causing the slowness.

The table structure for both DBs are the same. Running a query without the unnest will not have the problem. So, I'm suspecting that there is something to do with new version. Is there anything I can do to solve this problem?


texts: Foreign keys to primary tables or nested table

texts:
In this hypothetical example should the foreign key constraint setup for the ProductId and UserId columns in the ProductUserCommentAction table be referencing the Product/User tables as shown in the first diagram OR is it OK for those columns to reference the ProductUserComment table as shown in the second diagram?

I like how it's setup in the second diagram as it reduces the spider web in visualizations.

Are there any downsides to this second approach?

Versus


texts: What does CREATE ASSEMBLY do with the registered dll?

texts:
I created a dll in .NET for some custom CLR spatial functions in SQL Server 2008 R2. I sent the dll to a colleague along with a script to deploy it (CREATE ASSEMBLY then create the external functions etc).

She asked whether she needed to keep the file in the same location after it had been registered using CREATE ASSEMBLY. I didn't know, so I experimented by registering an assembly and creating the functions from it in sql server. Then I renamed the dll and restarted sql server. I found that the functions still worked after this so it appears that sql server has loaded the dll in some way and it doesn't need to stay on the file system. What's puzzling, though, is that the table sys.assembly_files does store the original path of the dll. Why would it do that if it isn't relevant?

So I'm not sure my test for this was right. But does anyone know what CREATE ASSEMBLY does with a dll? Does it copy/import it somewhere? Or was my test flawed and sql server does require the original dll to stay in the same location?

Thanks in advance.


texts: OEM 11g alerts and notifications arrive together

texts:
I setup the OEM with the Idea to receive Critical messages (emails when the DB, listener, agents and host are down/up) but it doesn't seem to be working.  If I "shutdown abort" the database I wont received any message until I put it up and running, so I receive all the messages together.

Please see image Bellow, that's how I set the alerts, I tested the Email sending and it's working.

Any Idea what it could be?


texts:
I have a table that has two columns, one with the type of posting and another column with the value.

Source table:

+-----+-------+
|Type | Value |
+-----+-------+
|  C  | 381.22|
|  D  | 25.49 |
|  C  | 25.49 |
|  D  | 25.48 |
|  C  | 705.56|
|  D  | 80.00 |
+-----+-------+

I need to create a view that separates types C and D, and list their values.

I need it to look like this:

+------+-----+
|Credit|Debit|
+------+-----+
|381.22|25.49|
|25.49 |25.48|
|705.56|80.00|
+------+-----+

I tried to create two separate virtual columns with SELECTS, just to list the values for the respective types.

SELECT
    (SELECT [value] FROM [table] WHERE [type] IN ('D')) AS Debit,
    (SELECT [value] FROM [table] WHERE [type] IN ('C')) AS Credit

And the error occurs because both SELECTS return more than one result:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Also try to use the PIVOT statement, but still unsuccessful.

Could you give me a suggestion to solve this problem?


texts: SQL Server 2016 Collation and Internal table Glitch?

texts:
My Steps:

installed SQL 2016 with collation SQL_Latin1_General_CP850_CI_AS
Restored Database to Stage instances with same collation as Database.(original Backup from 2012)
Ran > "C:\Program Files\Microsoft SQL Server\MSSQL13.STAGE\MSSQL\Binn\sqlservr.exe"  -m -T4022 -T3659 -s"STAGE" -q"SQL_Latin1_General_CP1_CI_AS"
this was to Changed the Instances and all database collation to SQL_Latin1_General_CP1_CI_AS. (what doesn't get changed User Type Tables)
Run Query

Select i.name, c.collation_name,i.internal_type,i.internal_type_desc
from sys.columns c inner join sys.internal_tables i on c.object_id=i.object_id
WHERE c.collation_name <> 'SQL_Latin1_General_CP1_CI_AS'
AND object_name(c.object_id) NOT LIKE 'sys%'
AND object_name(c.object_id) NOT LIKE 'queue%'
AND object_name(c.object_id) NOT LIKE 'file%'
AND object_name(c.object_id) NOT LIKE 'spt%'
AND object_name(c.object_id) NOT LIKE 'MSrep%'

Will return the  internal tables with Old collation.

name         collation_name                  internal_type internal_type_desc
sqlagent_jobs SQL_Latin1_General_CP850_CI_AS   220         CONTAINED_FEATURES

Test 2:

Create New database on 2016 when the Instances collation is set to SQL_Latin1_General_CP850_CI_AS.
new database collation has SQL_Latin1_General_CP850_CI_AS.
Run this Query  where collation = SQL_Latin1_General_CP1_CI_AS

Select i.name, c.collation_name,i.internal_type,i.internal_type_desc
from sys.columns c inner join sys.internal_tables i on c.object_id=i.object_id
WHERE c.collation_name = 'SQL_Latin1_General_CP1_CI_AS'
AND object_name(c.object_id) NOT LIKE 'sys%'
AND object_name(c.object_id) NOT LIKE 'queue%'
AND object_name(c.object_id) NOT LIKE 'file%'
AND object_name(c.object_id) NOT LIKE 'spt%'
AND object_name(c.object_id) NOT LIKE 'MSrep%'

this will return results that don't match the current collation.
name          collation_name                internal_type internal_type_desc
sqlagent_jobs SQL_Latin1_General_CP1_CI_AS  220         CONTAINED_FEATURES

I don't think the application will be affected by this internal_table.
But still strange that the internal_table collation does not match, on Creating and after converting the instances to a new collation.

If I do the same process but this time on a 2012 Staging instances.

restore Database to a 2012 Stagging instances with collation SQL_Latin1_General_CP850_CI_AS
Ran > "C:\Program Files\Microsoft SQL Server\MSSQL11.STAGE12\MSSQL\Binn\sqlservr.exe"  -m -T4022 -T3659 -s"STAGE" -q"SQL_Latin1_General_CP1_CI_AS"
fix User Type Tables
Backup converted database.
restore database on SQL 2016 with collation SQL_Latin1_General_CP1_CI_AS.
Run Query

Select i.name, c.collation_name,i.internal_type,i.internal_type_desc
from sys.columns c inner join sys.internal_tables i on c.object_id=i.object_id
WHERE c.collation_name <> 'SQL_Latin1_General_CP1_CI_AS'
AND object_name(c.object_id) NOT LIKE 'sys%'
AND object_name(c.object_id) NOT LIKE 'queue%'
AND object_name(c.object_id) NOT LIKE 'file%'
AND object_name(c.object_id) NOT LIKE 'spt%'
AND object_name(c.object_id) NOT LIKE 'MSrep%'

Nothing returns because I converted it in 2012.

If anyone can explain what is going?  Is this a bug?
Anyone know how to fix the internal table collation?


texts:
I have a parent row and then many child rows, and I must aggregate statistics from these child rows.

For a more concrete example let's imagine I have one round of golf (parent row) and then child rows with the score of each hole. I need to aggregate statistics on these rows, like number of birdies, pars, bogies, putts and so forth. And then put these numbers in a round_detail table.

What would be the best solution to this?

I'm using postgres if it matters, but this is probably just a general sql propblem.

EDIT: As requested http://sqlfiddle.com/#!1/6549c


texts:
We have a weird problem on one of our SQL Servers (2016 standard). At about 4 am we have a couple of restore jobs starting. Let's say, that databases A, B and C are in a "restoring state". More or less at the same time there is a job, that executes a stored procedure in the database D. D is not in the process of restoration. Databases A, B, C and D are totally independent from each other. The stored procedure in D does not depend on any objects from A, B nor C.

The problem is, the job executing in the D database fails after about 3 seconds with the following message:

  Database 'xxx' cannot be opened. It is in the middle of a restore. [SQLSTATE 42000] (Error 927).  The step failed.

where 'xxx' is  more or less randomly A, B or C. Again the procedure running in the D database has (or should have!) absolutely nothing in common with A, B, C.

I'm totally stumped by this. Does anybody know how to pin down the problem?

-- EDIT --

Addresing your questions:

  Did you double/triple checked that their is no dependency ?

Quadruplechecked! There are no direct dependencies.

Job runs as "sa". "sa" account is disabled. I enclose the job script below. @database_name=N'D' is not in the process of a restore (anyways the error message never points to "D"). EXECUTE [job].[xxx] executes a prcedure in a [job] schema. The procedure does nothing fancy. I'm 100% sure it does not connect to any of A,B and C databases.

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'xxx',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'***',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'xxx',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=3,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=3,
        @retry_interval=10,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'EXECUTE [job].[xxx] ',
        @database_name=N'D',
        @flags=0


texts:
I'm struggling to find any confirmation within sql server or sql management studio that my index is in fact aligned with my partition.

I'm creating a primary key constraint on a partitioned table:

CREATE PARTITION FUNCTION [pfDATA](bigint) AS RANGE RIGHT FOR VALUES (0);
CREATE PARTITION SCHEME [psDATA] AS PARTITION pfDATA TO ([P_CURRENT], [P_CURRENT]);
CREATE TABLE [dbo].[DATA](
    [DATAID] [bigint] NOT NULL,
    [VALUE1] [int] NOT NULL,
    [VALUE2] [int] NOT NULL,
    [VALUEetc] [int] NOT NULL
    CONSTRAINT [PK_DATA] PRIMARY KEY CLUSTERED
    (
        [DATAID] ASC
    ) ON psDATA(DATAID)
) ON psDATA(DATAID);

SQL management studio doesn't seem to give me any feedback visually about the characteristics of this index.

What I normally do when the application doesn't tell me what I need to know visually, is let the application generate a CREATE script (for the key/index or table, it doesn't matter in this case). What I expect to see is the partition scheme being mentioned in the constraint clause, but what I get is no mention of any partitioning:

CREATE TABLE [dbo].[DATA](
    [DATAID] [bigint] NOT NULL,
    [VALUE1] [int] NOT NULL,
    [VALUE2] [int] NOT NULL,
    [VALUEetc] [int] NOT NULL
    CONSTRAINT [PK_DATA] PRIMARY KEY CLUSTERED
    (
        [DATAID] ASC
    )
);

Is there any way to check if my indexes are constructed correctly (and are in fact, aligned)?


texts: How to make SQL Server Management Studio use latest version of SQL Server?

texts:
I'm upgrading my dev machine from Windows 7 to 8. While installing earlier versions of Visual Studio it installed SQL Server 2005. I then went to this site and downloaded and installed SQL Server Management Studio (SQLManagementStudio_x64_ENU.exe file) and then SQL Server 2012 Express (SQLEXPR_x64_ENU.exe file). Then did a mandatory reboot.

But when I load up management studio and do:

SELECT @@VERSION

I'm still getting SQL Server 2005. Any idea how to make it use SQL Server 2012?

EDIT: This is a screenshot of what I'm getting from SQL Config Manager.


texts: Determine the command type in a DDL trigger / get names and datatypes of new columns

texts:
Given strings corresponding to DDL commands, how can I determine which DDL command (column addition, constraint addition etc.) it precisely is?

I have a DDL trigger on a database that will be fired whenever any table in the database is altered; a system table will be logging the DDL commands that have been fired on any table in the database.

So the commands will look like:

1. ALTER TABLE [dbo.table] ADD [column] VARCHAR(20) NULL;
2. ALTER TABLE [dbo.table] ADD [column] VARCHAR(20) NULL CONSTRAINT [cons] UNIQUE;
3. ALTER TABLE [dbo.table] WITH NOCHECK ADD CONSTRAINT [cons] CHECK ([column] > 1);
4. ALTER TABLE [dbo.table] WITH NOCHECK ADD [column1] VARCHAR(20) NULL,
   CONSTRAINT [cons] CHECK ([column] > 1);

Is there any way to discern among the above 4 DDL statements? I'm only interested in the DDL that adds a new column to a table, and wish to retrieve only the name and datatype of this newly column.

So far I've been fidgeting with functions like SUBSTRING(), LEN(), and CHARINDEX() without much luck.

Ps. My trigger is able to fetch the name of the table on which the DDL is being performed.

The trigger will simply have one of the above 4 options as input and needs to distinguish when a column change has occurred and only retrieve the new column name and its corresponding data type.

Following an alteration made on a table, the trigger will be fired, this exact alteration command is being logged in one of the system table as part of the CDC feature that I'm using to track changes(cinching my access to the command). This CDC system table also enables me to retrieve the name of the table on which the alteration has been made, ergo i have another keyword i.e. table name that I know from the ALTER command.


texts: How to display differential backup date in SSMS Object Explorer Details

texts:
SQL Server Management Studio exposes the Object Explorer Details (selectable from the View menu option) and its context menu that allows adding fields to the default display.

Full and Transaction Log backup dates are optional fields. Differential backup date is not.

I could build an SSRS report to display the information. I could also create a Job that builds/exports this information.

However, I want to know if anyone has discovered a method to add Differential Backup date to the context menu as an option.

Thank you.


texts: Is IDENTITY continuous in new versions of PostgreSQL?

texts:
Now I am use serial to generate table primary key in PostgreSQL 13, but today I found the ID jumps and has big gaps in the ID space. It will waste many IDs. I read the docs and found that PostgreSQL has identity as a SQL standard generated primary key. I found a recommendation to use identity in new version of PostgreSQL but I don't know whether ID is continuous.
What should I do get a primary key id without gaps in PostgreSQL 13?

Solution 1: I tried to change serial to identity like this:
BEGIN;
ALTER TABLE public.article ALTER id DROP DEFAULT; -- drop default

DROP SEQUENCE public.article_id_seq;              -- drop owned sequence

ALTER TABLE public.article
-- ALTER clientid SET DATA TYPE int,              -- not needed: already int
   ALTER id ADD GENERATED ALWAYS AS IDENTITY (RESTART 2270886);
COMMIT;

Still gaps.
Solution 2: I tried to add a redis distribution lock about my apps insert article like this:
def save_single(guid, pub_time, title, author, content, source, link):
    if content is not None and len(content) > 0:
        article = Article()
        article_content = ArticleContent()
        beta_parser = CnbetaParser()
        second_parsed_content = beta_parser.parse_cnbeta_content(content, link)
        article_content.article_content = second_parsed_content

        cover_image = None
        lock_identifier = None
        try:

            # cover_image = Utils.get_cover_image_url(self, content, link)
            lock_identifier = acquire_lock("article-save", 5, 5)
            article.save(title, guid, author, pub_time, article_content, link, source, cover_image)
        except Exception as e:
            logger.error("save article data error,rss:" + source.sub_url, e)
        finally:
            if lock_identifier is not None:
                release_lock("article-save", lock_identifier)
    else:
        logger.error("article content is null,title:" + title)

Still gaps.


texts:
I am running a replica-set in production with slaveOk = false using mongo 2.0.7 in AWS The replication lag on one of the servers is close to 58 hours. This replication lag is sometimes decreasing ( at very slow rate ) and sometimes increasing, but overall its lag is increasing 1-2 hours per day.

I restarted the server, but seeing no benefit
The read rate is 10 times of the read rate, compared to the other secondaries.
I checked the logs, but nothing weird
The lock % is very high ( close to 100% ) only on that server( and there is no such issues with the capped collection ). Other secondaries hardly have 10-20 lock%

One more weird thing that I noted about this server ( secondary ) is that mms is showing the version to be 2.2.1 and type as 'standalone' server, which is not the case ( cross checked using db.version() and rs.status() command )


texts: Upgrade Advisor Or DMA (Database Migration Assistant)

texts:
We are migrating from SSRS 2012 to SSRS 2017.
MS documentation suggests - "Even though you are migrating rather than upgrading the installation, consider running Upgrade Advisor on your existing installation help identify any issues that could affect migration."
In the Upgrade Advisor page, they are talking about upgrading to SQL 2014 and NOT SQL Server 2017.

Is the upgrade Advisor still relevant to check for potential upgrade issues if we are migrating to SSRS 2017?

Can the DMA (Database Migration Mssistant) tool be used to check for SSRS 2017 upgrade? OR is it only relevant for DB Engine upgrade checks?

Thanks for the help!


texts: Mysql backup- restore on multiple host in live replication

texts:
MySQL database has 1 primary host which is replicating to 2 Read replicas

RR1
RR2

RR1 is master of RR11 RR12 RR13
RR2 is master of RR21 RR22 RR23
I have to make a schema change so I started schema change in RR13 by stopping replication. Then I made schema change, loaded data back & started replication. Its all working good.
Now I want to restore this host schema with Data to rest of everything. Can someone help me with the process? I was planning to do the same on RR12 RR11 but not sure how to do on master RR1 & the other branch hosts(RR2 branch hosts)


texts: Can't see Create Database in Analysis Services SQL Server Management Studio

texts:
When I right click on Databases in Analysis services there is no Create Database menu Item.

Another person with this problem 'reinstalled with all options' but when I run SSDT setup there is only 'do you want it or not' type options - for me anyway.

I've checked that my user login is an administrator. I've also started Management studio in administrator mode. No luck.


texts: MySQL Replication issues on server placed in different continents

texts:
I am running 2 cpanel servers with MySQL Replication set on them,  one in france the other in US. Running a database hungary site. Replication was setup to load balance as well as for failover reason with mysql master being in US.

The issue is slave sometimes lag behind and leaves some rows which creates difference in dbs.

I want to know what could be the possible reason behind this.

Latency between servers is 250ms which some times rises to 400ms or more but sometimes.
Nodes are 32 GB Octa core, tables with around 400k rows and total database size below 5GB and table count around 700. And connectivity at around 100 Mbps.
I know there is always room for optimizations though databases have been optimised.
Our Application receives 10 -15 hits per second in peak time and every hit creates 2/3 database queries.

What could be a possible solution to this problem? What are standard practices followed in the industry for similar situations.


texts: How to Convert a Table into XML Document

texts:
I have a table say SCOTT.DEPT and I want to convert it into an XML document. Is there anyway to accomplish this task?

Kind Regards,


texts:
Execute immediate call causes 'variable not in select list' error

hi there, i'm practicing with collections and execute immediate by combining them in plsql and have the following code:

set serveroutput on;
declare
  type TTabList  is table of table1%rowtype index by pls_integer;
  vtabList       TTabList;
  vstrQuery      varchar2(32000);
  vNum           number(10)     := 100;
  vStrs          varchar2(100)  := '''45'',''11'',''20'',''71''';
begin
  dbms_output.enable;
  vstrQuery := 'select field1
                  from table1
                 where field1 =   '|| vNum ||'
                   and field2 in ('|| vStrs ||')';

  execute immediate vstrQuery bulk collect into vtabList;

  if vtabList.count = 0 then
    dbms_output.put_line('nothing to show!');
  else
    while vtabList.count > 0 loop
      for i in vtabList.first .. vtabList.last loop
        dbms_output.put_line('we have: '|| vtabList(i).field1);
      end loop;
      vtabList.delete;
    end loop;
  end if;
end;

But its results following error which i can't resolve:

Error report -
ORA-01007: variable not in select list
ORA-06512: at line 14
01007. 00000 -  "variable not in select list"
*Cause:
*Action:

Any ideas to fix this?


texts: PostgreSQL equivalent query which has order by clause alone

texts:
Please help me to get equivalent PostgreSQL query for the below oracle query

SELECT sum(COLUMN1),sum(COLUMN2)
FROM Table_name
ORDER BY COLUMN3;


texts: Does PostgreSQL JIT compile index creation?

texts: Does the LLVM-backed JIT compilation that shipped in PostgreSQL 11 JIT compile index creation (a CREATE INDEX query)?


texts: How to change UDP port 1434 ms-sql-m service

texts: I am running sage evolution 200 on a cloud server. Until recently the program was running fine. Then the cloud host decided to block UDP port 1434 on their router as they claim it was being attacked. When I use port query, it return TCP port 1433 is listening but port 1434 is filtered. I contacted my cloud server provider and was advised to change port 1434 to another port number. I am not a computer expert by any means and need assistance is changing this port even though it's the official port for SQL Server.


texts: PostgreSQL two different ways to generate a UUID: gen_random_uuid vs uuid_generate_v4?

texts:
What is the difference between the following two functions

gen_random_uuid() provided by pgcrypto extension
uuid_generate_v4() provided by uuid-ossp extension

Are they both the same behind the scenes? Any performance impacts on using one?