texts:
I think I'm close, but the apostrophes are killing me:

    create procedure denull (@database varchar(255), @table varchar(255))

    as

    declare @sql nvarchar(max)

    set @sql = ' declare @name varchar(255), @column varchar(255))

    declare c cursor local for

    select st.name, sc.name from ' + @database + '.sys.tables st
    inner join sys.columns sc on st.object_id = sc.object_id
    where st.name = ''' + @table + '''

    update ''''@name'''' set ''''@column'''' = '''' where ''''@column is null''

    fetch next from st.name, sc.name

    close c

    deallocate c
    '
    print @sql

    exec sp_executesql @sql

it prints out to:

     declare @name varchar(255), @column varchar(255))

    declare c cursor local for

    select st.name, sc.name from livendbmirror.sys.tables st
    inner join sys.columns sc on st.object_id = sc.object_id
    where st.name = 'CMS_27_Psych_results'

    update ''@name'' set ''@column'' = '' where ''@column is null'

    fetch next from st.name, sc.name

    close c

    deallocate c
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ')'.
    Msg 102, Level 15, State 1, Line 9
    Incorrect syntax near ''.
    Msg 105, Level 15, State 1, Line 9
    Unclosed quotation mark after the character string '

    fetch next from st.name, sc.name

    close c

    deallocate c
    '.

Question: How can I dynamically remove null values and replace them with '' for fussy end users?


texts: Is MySQL replication okay after writing to slave then making identical write to master?

texts:
I'm running MySQL 5.5.31 and I have a master/slave setup. I accidentally ran a mysql UPDATE on the slave, a simple one-row update like:

UPDATE thetable SET somefield = 'asdf' WHERE id = 1234

I realized my mistake quickly and then ran the same exact statement on master. I probably should have looked into possible issues after the update to slave before the update to master, but I wasn't thinking clearly, so I don't know what the state was like between these operations.

Anyway, after that, worried that I may have caused a sync problem, I did a SHOW SLAVE STATUS and the replication seemed to be working fine, fully caught up with master, and with no error messages. I also did a CHECKSUM TABLE thetable on both master and slave, and the value returned was identical.

So everything seems to be running fine, but I want to avoid running into any potential problems. Given what I described, should everything be okay here on out? Is something about this resulting state going to pop up and bite me? In general, if you make an update to slave followed immediately by an identical update to master, will sync problems be avoided?


texts: How does indexes work on an Access query

texts:
I'm working on an Access 97 Db and I'm trying to speed up some low performance issues, as of right now I'm wondering how indexes works in Access.

Using this table with 300k rows i get awful results if I do a Select using all of the indexes :

>Office|Product|Colour|Collection|Theme|SubTheme|Description|.....
>Index(One for all these columns) : Office,Product,Colour,Collection,Theme,SubTheme

>Query : SELECT * FROM Table WHERE Office = 01 AND Product = "AAAA" and Colour = "091" and Collection = "01" AND Theme = "AA" and SubTheme "BB"

To solve the performance issue I tried to insert in the index a string containing all the value of the index (Office,Product,Colour,Collection) and just use that in the query.

>Query : SELECT * FROM Table Where Code = "01AAAA09101AABB"

The last query is a lot faster, can someone explain why this is happening ?


texts: Why does restoring postgresql database from dump produce errors

texts:
I did the following commands:

pg_dumpall --clean | gzip > db.gz
gunzip db.gz
psql < db

I expected the third command (loading the database back) to just run with zero errors, but I got some errors:

ERROR:  multiple primary keys for table "pga_exception" are not allowed
ERROR:  multiple primary keys for table "pga_job" are not allowed

There are more errors than those listed, but they are a combination of primary key violations, constraints already existing, and triggers already existing.  Why would recreating a database from a backup sql dump produce errors?


texts: Does having Default Database Engine MyISAM and keeping InnoDB tags in my.ini create any problem?

texts:
Recently I am getting table corruption issue very often and every time when I check the logs it says "Binlog has bad magic number; It's not a binary log file that can be used by this version of MySQL"

Every single time I've to move binary logs from it's existed location to somewhere else and then only I could start the mysql services. I need to know why it happens.

My database engine type is MyISAM, all the database files are in .MYD format. I also have InnoDB parameters in my.ini

(commented)Innodb parameters

innodb_flush_log_at_trx_commit=0
innodb_log_files_in_group=2
innodb_log_file_size=256M
default_table_type = MYISAM
transaction_isolation = REPEATABLE-READ
read_rnd_buffer_size = 8M
(commented)innodb_data_home_dir=D:/eClinicalWorks/mysql/data
innodb_data_file_path = mobiledoc_InnoDb_data1:300M:autoextend
innodb_file_per_table
innodb_additional_mem_pool_size=8M

(commented) configure innodb_buffer_pool_size according to available memory
innodb_buffer_pool_size=256M

innodb_log_buffer_size=8M

(commented)set-variable=key_buffer=16M
innodb_data_home_dir=D:\eClinicalWorks\\mysql\\data

Questions

Will keeping InnoDB tags enabled in my.ini create problem in?
Do you think these tags are creating problem?

Need help ASAP.

Regards
Suhaas


texts: MariaDB ERROR 1048 GeomFromText returning as null

texts:
I am trying to restore a databese from an old server onto a new server and my geomertry columns are not importing
example
UPDATE `property_floor` SET
`floorBounds` = ST_GeomFromText('MULTIPOINT((115.823402 -32.064224),(115.823509 -32.079125),(115.843336 -32.064224)')
WHERE `floorID` = '1'

Error in query (1048): Column 'floorBounds' cannot be null

I have tried it both with the SRID and without it. tried both ST_GeomFromText and GeomFromText
I am at a loss for what is wrong, Is this a bug? is my data malformed (looks good to me, but I could be having one of those blind moments) or has something changed?
floorBounds is of type multipoint and the data I am trying to import was exported from a functioning database.
mariaDB version 10.3.25-MariaDB-0+deb10u1
I only just noticed that the old server WAS NOT using mariaDB but MySQL (ver 5.7.31-0ubuntu0.16.04.1) But it should still be compatible, correct?


texts: How can I order the results from a DISTINCT ON query

texts:
I have a table with the equivalent of:

group_id integer REFERENCES <some other table>
item_id integer REFERECNES <some other table>
timestamp date
data text
sort_order text

I want to get the latest data for every item before some date. I.e. the maximum timestamp less than some value. I also want these rows sorted on sort_order.

After som Googling I find that SELECT DISTINCT ON is what I want, so I get something like

  SELECT DISTINCT ON (item_id)
         data
    FROM [this table]
   WHERE group_id = [some value]
     AND timestamp < [some value]
ORDER BY item_id, timestamp DESC

Now, I actually want the data sorted on sort_order, but DISTINCT ON requires the ORDER BY to be listed first.

One solution that comes to mind is to wrap all of this in an outer SELECT that will ORDER BY sort_order. Is that a good efficient solution, or is there a better "correct" way to do this.

(Or, am I in fact not even close...)


texts: How does MySQL store data, can I detach a mysql Database?

texts:
I have used SQL Server in the past which each database is kept in separate files (a data file and a log file) but things are different in MySQL as it seems. The file sizes of these files translates to the actual database size. They can compressed and considerably reduced in size, but things seems to be different in MySQL.

I look up the files of MySQL (InnoDB) where they are stored in /var/lib/mysql/ folder. There is files for each database (I can't share that) and there are these three files which seems to contain data

-rw-rw---- 1 mysql mysql 50M Feb 24 16:17 ibdata1
-rw-rw---- 1 mysql mysql 48M Feb 24 16:17 ib_logfile0
-rw-rw---- 1 mysql mysql 48M Feb 22 23:49 ib_logfile1

First I am not sure if all the database data is kept in one file, that ibdata1, second some of my database are large (1.4 GB one one of them) but these files are very small. I am wondering how data is maintained in MySQL.

Here is my query to find my database size Engine wise

SELECT Engine "DB Engine",
        ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables
GROUP BY engine;

And the result

+--------------------+---------------+
| DB Engine          | DB Size in MB |
+--------------------+---------------+
| NULL               |          NULL |
| CSV                |           0.0 |
| InnoDB             |        3695.8 |
| MEMORY             |           0.0 |
| MyISAM             |        3120.5 |
| PERFORMANCE_SCHEMA |           0.0 |
+--------------------+---------------+

Obviously my database a lot larger than the datafiles that stores that data. On a side note, I also want to know if I can detach a database in mySQL just like in SQL Server. Part of the question is because I want to optimize my database, remove the ones that are no longer used. Also currently there is only 30GB on this drive and I want to free up some space too. Note I have additional drives on my Linux (centos) server as well (extra). Thanks


texts: Export mutliple query output into one excel sheet using Redshift and SQL Developer

texts:
I am connected to Redshift from SQL Developer 19.2.1.
I have a requirement to export multiple query output into a single excel sheet, one query output should be in one Tab. Please advise on how to achieve this.
Thanks


texts:
Structure of my table

mysql> show create table t_group_tag_relation\G
*************************** 1. row ***************************
Table: t_group_tag_relation
Create Table: CREATE TABLE `t_group_tag_relation` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`domain_id` int(11) NOT NULL,
`group_tag_id` int(11) NOT NULL,
`resource_id` int(11) NOT NULL,
`resource_type` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `group_tag_id_resource_id` (`group_tag_id`,`resource_id`),
KEY `domain_id` (`domain_id`,`group_tag_id`,`resource_type`,`resource_id`),
KEY `domain_id_resource_type` (`domain_id`,`resource_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1613462 DEFAULT CHARSET=latin1
1 row in set (0.03 sec)

Explain plan of my query

mysql> explain select * from t_group_tag_relation WHERE resource_id = 575868070 AND domain_id = 476 AND resource_type = 2;
+----+-------------+----------------------+------+-----------------------------------+-----------+---------+-------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+------+-----------------------------------+-----------+---------+-------+-------+--------------------------+
| 1 | SIMPLE       | t_group_tag_relation | ref   | domain_id,domain_id_resource_type | domain_id | 4 | const | 28400 | Using where; Using index |
+----+-------------+----------------------+------+-----------------------------------+-----------+---------+-------+-------+--------------------------+
1 row in set (0.57 sec)

It is using indexes already but performance is not good.
How to improve performance?


texts: Redis Performance Optimization - Does a slave's replication take as much compute resources as being a master?

texts:
I have the need for two desperate Redis Instances.

One's job is mostly as a session store (currently deployed), the others role will be as an usage statistics depot. Assume my servers are single-threaded (so just adding more instances will not be a performance boon)

Currently, I am "wasting" a lot of computing resources for high availability purposes, I am wondering if this can be remedied.

My purposed architecture would be as follows (Assume a working three server sentinel configuration):

+----+    +----+
| Ma |    | Mb |
| Rb |    | Ra |
+----+    +----+

     M = Master
     R = Slave
+----+
|    | = Physical Server
+----+

If replication takes as much computational resources as processing requests as master, then this configuration will not do me much good on a single cpu server.

I am doing basic command, stringget, stringset, set adds, ect. Obviously, performance will be impacted if there is down time on a server, but that is not the usual case. Will this configuration let me increase throughput while utilizing more of my hardware?

Edit: There doesn't seem to be a lot of Redis activity on this site, can someone suggest a better site to migrate this question to?


texts:
I have created the following table in postgres.

test=# \d leaderboard_scores;
                                        Table "public.leaderboard_scores"
   Column   |            Type             | Collation | Nullable |                    Default
------------+-----------------------------+-----------+----------+------------------------------------------------
 id         | integer                     |           | not null | nextval('leaderboard_scores_id_seq'::regclass)
 user_id    | character varying(45)       |           | not null |
 score      | integer                     |           |          |
 created_at | timestamp without time zone |           |          |
Indexes:
    "leaderboard_scores_pkey" PRIMARY KEY, btree (id)
    "leaderboard_scores_user_id_key" UNIQUE CONSTRAINT, btree (user_id)
    "score_back_user_id" btree (score DESC) INCLUDE (user_id)

My table size is 2M rows

My Use cases are:

To fetch users with max scores (this is running fast)
To get the rank of a particular user

To acheive number 2 I was hoping to use row_number window function but that is running very very slow.

The query I am using

select user_id, row_number() over (order by score desc) from leaderboard_scores order by score desc offset 500000 limit 20;

This query is taking around 900 ms which is too much to get a rank for a given user.

                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=52083.47..52085.56 rows=20 width=49) (actual time=1074.712..1074.755 rows=20 loops=1)
   ->  WindowAgg  (cost=0.43..208332.50 rows=1999999 width=49) (actual time=0.086..1043.536 rows=500020 loops=1)
         ->  Index Only Scan using score_back_user_id on leaderboard_scores  (cost=0.43..178332.52 rows=1999999 width=41) (actual time=0.074..807.340 rows=500021 loops=1)
               Heap Fetches: 500021
 Planning Time: 0.097 ms
 Execution Time: 1074.783 ms
(6 rows)

How can I optimize to get a user's rank?


texts: Why would Stolen Memory counter be at a constant high?

texts:
I'm trying to understand memory stats a lot better and have been troubleshooting an issue with one of our SharePoint DB servers for a while now. Low PLE, frequent lazy writes, slow performance etc

I noticed today that the stolen memory counter was high, and remained high. It didn't really fluctuate.

My server has 16GB RAM and SQL Server is allocated a maximum 12GB. The stolen memory counter is around 8.5GB. Is that normal?

What could that be and how to pinpoint the cause? What should I be looking at?

SQL Server 2012 is patched to SP3.

UPDATE:
Here's an output of a query of os_performance_counters showing the high Stolen Memory (high being relative to the max memory of 12GB given to SQL Server)

UPDATE 2:

By looking at trend data in SCOM I can see that Stolen Memory has increased steadily over the last 120 days from 2GB to 8.5GB... this is really puzzling me.

Thanks


texts: What is the use of "Backup set will Expire:" option while creating Backup Database task in Maintenance plan

texts:
While backing up the database, I would like to use "Backup set will Expire:" option to delete/overwrite old database backups. I am not able to use this option.

Any advice on how to use "Backup set will Expire:" options while creating Maintenance plan will be appreciated.


texts:
On a SQL Server 7 server, I have a linked server to a Sybase database server running this command:

select * from openquery(SYBASE_SERVER, 'select * from DBA.TESTTABLE')

works fine if I'm logged in as an SA.

If I try to run it while logged in as non-SA, it fails with the error:

  Server: Msg 7302, Level 16, State 1, Line 1 Could not create an
  instance of OLE DB provider 'ASAProv.90'.

What permissions do I need to grant the user to let it work as a NON-sa account?


texts: Issue connecting to remote SQL Server database through VPN

texts:
I need to connect to a remote SQL Server database of a client to do some syncing and other stuff. They use a VPN connection and have provided me the VPN address, RDP to SQL Server, username and password.

VPN Address: *...
RDP to SQL Server: ..**.* : 4001
Username: *************gonal
Password: **********
DB name: IS_*****

What I managed to do was connect with a new VPN connection I created with the VPN address, username and password that were given to me.Worked fine on a linux and a windows machine.

The main issue I seem to be having is when I try establish a connection to their DB. I use DBeaver on Linux and HeidiSQL on windows. Both give me errors, see below:

DBeaver (Linux):

HeidiSQL (Windows):

Is there something I am missing? I'm trying everything and would appreciate any help at all. Could it be a firewall issue? Thanks in advance!


texts: PostgreSQL equivalent of SQL Server SQL_VARIANT_PROPERTY

texts:
Does PostgreSQL have an equivalent function for determining column metadata?

Reference: SQL_VARIANT_PROPERTY

pg_typeof() appears to be close, but it does not show the precision and scale. Looking up the type in pg_type only shows the generic type info, not the column specific, e.g. SQL Fiddle.


texts: Preventing orphaned users in SQL Server 2008 R2

texts:
I've recently had a situation where a database was refreshed from production to staging, and the users on the database ended up orphaned.

These are sql server users, not mapped to windows credentials.
The database users use the same credentials on both PRD and STG.
The database users are called the same on both PRD and STG.
The SQL Server logins are called the same on both PRD and STG.
The database was refreshed by using a RESTORE with REPLACE and MOVE to rename the data files.

As I understand it, this is because the SID's of the logins don't match between our two environments.

How could I make it so that the SID's of the sql server logins on our staging environment are the same as on our production environment going forward?

My thought is to use CREATE LOGIN WITH SID ='SID value from PRD' but this has the disadvantages of needing to drop the logins first, and I'm not entirely sure that manually defining an SID is healthy / wise.

Maybe there is a way to have the logins migrated with the backup? (if sp_help_revlogin has been available to manually copy logins, why don't backups have this option)


texts:
I'm trying to insert the value of an attribute of a tuple, which would be an array of date ranges, i.e. a daterange[], in a previous tuple accepted the value:
{"[2013-05-08,2018-03-22)"}

But now I would like to insert this array, with more than one range:
{"[19-03-2018,29-12-2020)","[21-06-2018,29-12-2020)", "[17-07-2018,29-12-2020)",
        "[18-04-2018,30-07-2018]", "[01-08-2019,19-11-2019]"}

I have tried all possible solutions,putting the outer quotes
 '{"[19-03-2018,29-12-2020)","[21-06-2018,29-12-2020)", "[17-07-2018,29-12-2020)",
        "[18-04-2018,30-07-2018]", "[01-08-2019,19-11-2019]"}'

Putting the internal single quotes instead of the double ones, but nothing works.
Gives me the error:

argument formats can't be mixed


texts: Restoring a replicated SQL Server database in a non-replicated testing enviroment

texts:
We have a SQL Server 2008R2 database in production. Originally, the database didn't have replication. Every time there was some problem with our software, we'd just create a backup from the production server, restore it on our testing environment, and reproduce the problem to solve it. Simple as that.

A new gov-related requirement pushed us to set up a Transactional replication on the Production database. We modified our software accordingly, and it's now working well. Our software is able to function both with a replicated or non-replicated database.

The problem now, is that we can't simply create a backup and restore in our environment as simple as before, every time there's a problem we need to reproduce. When we restore a backup, we can't modify any of the schema and/or functions of it, because SQL Server says the database is tied to a non-existent replication (because it obviously copies the replication config from the Production environment).

Is there any way to create a backup of a replicated database and restore it cleanly in a non-replicated environment? Just the schema, data, and functions. 'Generate scripts' isn't possible because there's a LOT of data.

I see many questions related to how to restore the database keeping the replication active, but I just need the clean database for testing purposes.


texts: How to clear the data buffer cache for an Azure SQL database

texts:
I want to do some performance testing on an Azure SQL database.

For SQL Server, I've read that DBCC DROPCLEANBUFFERS can be used
to clear the data buffer cache, and DBCC FREEPROCCACHE can be used to clear the execution plan cache.

But neither are supported for Azure SQL databases. For the execution plan cache, ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE
seems to be an alternative. But I can't find an alternative for the data buffer cache.

How do I clear the data buffer cache for an Azure SQL database?


texts: SQL 2008 R2 performance problems - mixed signals on disk performance

texts:
I'm having no end of trouble with one of our production SQL boxes - users are experiencing massive drop offs in performance and this is having severe impacts on the business. I've been investigating mainly from a SQL/dba perspective and using sp_BlitzFirst I've been seeing intermittent reports of very slow read/write times (typically ~100-300ms but at points as high as 1000ms+).

The real head scratcher is that after getting our infrastructure guys involved they can't see anything amiss from their end.

The server is a virtual machine (Windows Server 2008 R2, SQL 2008 R2 Standard with 12 cores and 32Gb of RAM providing the databases for a Dynamics CRM 4.0 instance and another web application that is integrated with CRM) running under vmware with storage being provided by a Dell EqualLogic SAN and they are seeing times more in the 30-40ms range. Now it's entirely possible that the samples taken by BlitzFirst and vmware are simply not coinciding and causing the disparity but if not could there be any other explanation? I've used perfmon on the server and while the averages for the disk read/write are pretty reasonable I see definite spikes that are much higher then vmware is reporting - much closer to the figures that BlitzFirst is reporting. Is there anything else I should be looking at?

I'm reasonably certain that the problems BlitzFirst is reporting are real as they always occur at the same points the users experience major problems. To be honest the application performance of this CRM has always been pretty crap but even with a pretty low bar it's still falling well short.


texts: How to join to the same table multiple times?

texts:
I have two tables, "hierarchy_table" and "name_table".

The hierarchy table contains an object which has multiple parents and children. Each parent and child is referenced by id.

|  object_id  |  parent_id_1  |  parent_id_2  |  child_id_1  |  child_id_2  |
-----------------------------------------------------------------------------
|     1234    |      9999     |      9567     |     5555     |     5556     |
-----------------------------------------------------------------------------

Each object id in the hierarchy_table has an entry in the name_table:

|  name_id  |    name    |
--------------------------
|   1234    |   ABCD     |
--------------------------
|   9999    |   ZYXW     |
--------------------------
| ...

How do I join each id in the hierarchy_table to the name_table multiple times so that I can have a result where every name is populated?

Like this:

|   object    |   parent_1    |   parent_2    |   child_1    |   child_2    |
-----------------------------------------------------------------------------
|     ABCD    |      ZYXW     |      BBBB     |     CCCC     |     DDDD     |
-----------------------------------------------------------------------------

Note:  the table names in the example are just for clarity / simplicity, the real names have proper names.


texts: How does CALs Licenses work with Azure SQL

texts:
I looked all over the web for concrete information regarding CALs licenses in Azure  SQL but couldn't find much event on Microsoft Azure site.
Is this included within subscription ?
Or
Is it part of Azure Active Directory ?


texts: How to get back the PostgreSQL database to a specific point in time?

texts:
I was running a batch script in a PostgreSQL database which inserted values in different tables between 10:40 and 11:00. Later I came to know that the script was executed mistakenly. How can I get back my database to a state before 10:40 without stopping the server? I don't want the values which were inserted by batch during 10:40 to 11:00.

WAL has been configured.

I was struggling with this for the last three days. I have been trying with creating recovery.conf file with:

restore_command='path to my archivefile/%f %p'
recovery_target_time = '2014-10-15 10:40:00'
recovery_target_inclusive = true

Still I am not able to get my database backup to previous state.


texts: SQL - double column used from primary key with autoincrement

texts:
I am user the following as primary key in the SqlDB:

I am doing the Index as Autoincrement, but it is autoincremented in general not per user.
Example userid=1,index=1, then insert another for userid=2 then inserted userid=2index=2
Then we insert a new entry for userid=2 theninserted userid=2 index=3 and not 1.

Is it possible to do what I want?


texts: Can you have more than 65536 sessions on an Oracle RAC database?

texts:
I put this same question on Oracle's community site but this site gets a lot of activity so I am posting here also. Apologies to anyone who is on both.
Other post: https://community.oracle.com/tech/developers/discussion/4482456/can-you-have-more-than-65536-sessions-on-a-rac-database
On Oracle 19c you cannot set the sessions parameter to more than 65536 so I believe that means that you can have at most 65536 sessions on a single node database.
If I had a two node RAC database could each node have 65536 sessions or are we limited to 65536 total for both nodes?
Bobby


texts: How do you concatenate an entire column with a group by?

texts:
Today, I saw that our residents have put a bajillion hours into an excel spreadsheet, tracking everything they need to know about our patients.

The excel sheet is really good for humans to read from, but absolutely horrendous for machines. I'd like to help them out, but I'm stuck on one part: creating a list of every diagnosis per patient - and presenting it in a single field.

I've got this, which will concatenate the entire table:

    select stuff( (
    select top 12  ', ' + icd10 + ' ' + icd10.description   from
    AdmVisitDiagnoses dx
    left join ehs_icd10 icd10 on replace(dx.Code,'.','') = icd10.icd10
    where icd10 is not null
    for xml path ('')),1,2,'')

which will produce a result like this:

I10 Essential (primary) hypertension, E780 Pure hypercholesterolemia, E780 Pure hypercholesterolemia, I10 Essential (primary) hypertension, I8291 Chronic embolism and thrombosis of unspecified vein, I8291 Chronic embolism and thrombosis of unspecified vein, F205 Residual schizophrenia, F209 Schizophrenia unspecified, F259 Schizoaffective disorder unspecified, F259 Schizoaffective disorder unspecified, F259 Schizoaffective disorder unspecified, F259 Schizoaffective disorder unspecified

but I think I'm barking up entirely the wrong tree. Also, the performance is horrendous (not a deal breaker, since it will be automated, but still something to be mindful of).

Question: How would you break this up by a foreign key?

Some sort of pivot? The number of diagnoses is quite variable...

Ideally, it would be something I can easily smush into a larger query, but there's obviously ways to work around that if needed.

Here's my current solution, but I'm pretty sure it's not a good one:

    create function dbo.stuffDx(@VisitID varchar(55))
    returns varchar(max)

    as
    begin
    declare @string varchar(max)

    set @string = (select stuff( (
    select top 500  ', ' + isnull(icd10,'') + ' ' + isnull(icd10.description,'')   from
    AdmVisitDiagnoses dx
    left join ehs_icd10 icd10 on replace(dx.Code,'.','') = icd10.icd10
    where icd10 is not null
    and VisitID = @VisitID
    for xml path ('')),1,2,''))

    return @string

    end

Here's the data:

AdmVisitDiagnoses:

    VisitID             Code
    OL0-20100812131135  250.00
    OL0-20100812131135  295.90
    OL0-20100812134136  250.00
    OL0-20100812134136  401.9
    OL0-20100812134506  250.00
    OL0-20100812134506  401.9
    OL0-20100812135224  295.30
    OL0-20100812135224  250.00
    OL0-20100817085834  295.90
    OL0-20100817085834  272.0

icd10:

    icd10   description
    A000    Cholera due to Vibrio cholerae 01 biovar cholerae
    A001    Cholera due to Vibrio cholerae 01 biovar eltor
    A009    Cholera unspecified
    A0100   Typhoid fever unspecified
    A0101   Typhoid meningitis
    A0102   Typhoid fever with heart involvement
    A0103   Typhoid pneumonia
    A0104   Typhoid arthritis
    A0105   Typhoid osteomyelitis
    A0109   Typhoid fever with other complications


texts: I need help optimizing a not that complicated query that returns unexpedcted performance problems

texts:
I have a table that stores details about incoming and outgoing phone calls such as when a call was made, or when a call was received, the phone number of the caller, and the phone number of the receiver.
Before you tell me to separate the data in two tables, one for incoming calls and another for outgoing calls, I tried to do it but my thesis partners didn't agree so I had to go along with a single table, differentiating them with an "incoming" int column.

We are trying to match each outgoing call to an incoming call. The criteria for that is that the incoming call will have been created within 30 seconds of the outgoing call, and that their numbers match.

select
  *
from
  tesis.call oc --outgoing calls
join tesis.call ic --incoming calls
  on oc.sourceNumber = ic.targetNumber
  and ic.dateCreated > oc.dateCreated
  and ic.dateCreated <= (oc.dateCreated + interval 30 second)
where
  oc.incoming = 0
  and ic.incoming = 1
  and oc.neighborhood_id is not null

We have an index for "incoming", an index consisting of (sourceNumber, targetNumber), and for in dateCreated.
tesis.call has around 13000 rows
sourceNumber and targetNumber are varchar(20)
dateCreated is DateTime
incoming is int

For some reason, the query takes around 100 seconds to execute, tested in two computers with the DB's on localhost, running with an i5 processor, one of them on MySQL and the other on MariaDB.
I tried many index combinations and none of them work.

This is the execution plan with the provided indexes:

Thank you.

Right now the provided query with the specified indexes took: 0,780 sec. (+ 116,455 sec. network) to execute.
The network part looks very weird to me, since "select * from tesis.call" takes 0,000 sec. (+ 0,062 sec. network) to execute so it doesn't look like a connectivity issue.

EDIT: Here is the result of SHOW CREATE TABLE tesis.call:

CREATE TABLE `call` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `dateCreated` datetime(6) DEFAULT NULL,
  `incoming` int(11) DEFAULT NULL,
  `operatorName` varchar(20) DEFAULT NULL,
  `sourceNumber` varchar(20) DEFAULT NULL,
  `targetNumber` varchar(20) DEFAULT NULL,
  `batteryLevel` double DEFAULT NULL,
  `currentSignal` double DEFAULT NULL,
  `locationLat` double DEFAULT NULL,
  `locationLon` double DEFAULT NULL,
  `dispatchDate` datetime DEFAULT NULL,
  `insertionDate` datetime DEFAULT NULL,
  `matchId` int(10) unsigned DEFAULT NULL,
  `callTime` int(10) DEFAULT '0',
  `neighborhood_id` int(11) DEFAULT NULL,
  `Geom` point DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `matchId_UNIQUE` (`matchId`),
  KEY `Geom` (`Geom`(25)),
  KEY `incoming` (`incoming`) USING HASH,
  KEY `dateCreated` (`dateCreated`),
  KEY `sourceNumber_targetNumber` (`sourceNumber`,`targetNumber`),
  KEY `dateCreated_incoming_targetNumber` (`incoming`,`targetNumber`,`dateCreated`),
  KEY `dateCreated_incoming_sourceNumber` (`incoming`,`sourceNumber`,`dateCreated`)
) ENGINE=InnoDB AUTO_INCREMENT=15154 DEFAULT CHARSET=utf8

EDIT 2: I uploaded a script to recreate the table in case anyone wants to do some tests.
https://www.mediafire.com/?ph0gk06dxntohmg


texts: How to count items in specified ranges in one query?

texts:
I have this table:

Id | Username | FollowersCount
 1 | user1    | 1500
 2 | user2    | 700000
 3 | user3    | 94000
 4 | user4    | 117000

I want to find the number of users who have less than 50k followers, users who have between 50k and 100k, users who have between 100k and 150k, and so on.

Of course I can write many queries to get the result. But is there a way to somehow mix aggregate functions with between clause?


texts: When to use multiple tables in DynamoDB?

texts:
The DyanmoDB best practices make it clear that:

  You should maintain as few tables as possible in a DynamoDB application. Most well designed applications require only one table.

I find it amusing then that just about every single tutorial I've seen dealing with DyanmoDB has a multi-table design.

But what does this mean in practice?

Let's consider a simple application with three main entities: Users, Projects, and Documents. A User owns multiple projects, and a Project can have multiple Documents. We typically have to query on the Projects for a User, and on the Documents for a Project. Reads outnumber writes by a significant margin.

A naive tutorial's table design would use three tables:

Users
Hash key
user-id

Projects
Hash key       Global Index
project-id     user-id

Documents
Hash key       Global Index
document-id    project-id

We could pretty easily collapse Project and Document into one Documents table:

Documents
Hash key    Sort key        Global Index
project-id  document-id     user-id

But why stop there? Why not one table to rule them all? Since the User is the root of everything...

Users
Hash key    Sort key
user-id     aspect
---------   ---------
foo         user                   email: foo@bar.com ...
foo         project:1              title: "The Foo Project"
foo         project:1:document:2   document-id: 2     ...

Then we would have a Global Index on, say, the email field for user record lookups, and another on the document-id field for direct document lookups.

Is that how it's supposed to work? Is it legit to throw such wildly-divergent kinds of data into the same table? Or is the second, two-table design a better approach?

At what point would it be correct to add a second table?


texts: Running select filename from dbo.sysfiles returns short foldernames with tilde

texts:
I am running the following statement in SQL Server 2014(in Windows 2012R2):
select filename from dbo.sysfiles where fileid = 1

However it returns:
c:\progra~1\micros~1\mssql1~1.sql\mssql\data\master~1.mdf

I wish for the full path without the tilde.
(e.g C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\master.mdf)
I tried upgrading the SQL Server 2014 Express to 2016 Express but it made no difference.
Any ideas?
Thanks.
Dave


texts: Linked server to Oracle doesn't use filters. Are there any performance changes in different versions of SQL Server?

texts:
My customer somehow only has SQL Server 2005 instance. It has linked server to oracle database. I've noticed that sql server doesn't send any filters to oracle side and apply them only after it retrieves all data from oracle. Which is quite opposite to work of sql server - sql server linked server.

I'm wondering - could it be because of sql server version? Maybe 2005 is to old for that?

For example:

SELECT * FROM OPENQUERY (ORCLLS, 'Select * from ADMIN.IE_Docs_Subscrs where F_DRSO = ''84c9e2c5-2634-4f6a-b1b7-e1505d4a1c39''')

Result: 0 seconds. 24 rows

SELECT * FROM OPENQUERY (ORCLLS, 'Select * from ADMIN.IE_Docs_Subscrs')
WHERE  F_DRSO = '84c9e2c5-2634-4f6a-b1b7-e1505d4a1c39'

Result: 1m 33 seconds. 24 rows

EDITED

Got it. It's because of OPENQUERY. But how can I turn it into four-part name?


texts: How to set the trigger when field(contain json value) is updated?

texts:
I have one table in which there is one field called info.This field hold json value. I want to create trigger on this field.I want to split this json format string into record I mean I want to insert new record based on this json field.Is this thing possible?

e.g

jsonfield :{"part_req":{"p_id":"111","category":"stock","order_no":"23001"}}

I want to save this field 's value in product table

Output Should be :
Table : product

p_id -> 111
category -> stock
order_no -> 23001

is this things possible through trigger ???


texts: What is the best way to check if a value in a table has changed when writing to a database?

texts:
Let me provide you with an example. Let's say I have an html_tag with two columns, tag_type and tag_value

For example, let's say I have a record with the value "link" under thetag_type field and "https://dba.stackexchange.com" under the tag_value field.

Now, how would I approach this for auditing purposes? I want to only INSERT into this table when one of the tag_value fields corresponding to a tag_type has changed, because it would not make sense to add redundant records to this table.

Is this a database design problem I'm having, or is there advanced SQL I'm unaware of that would accomplish this? Or, rather, would the Java I am getting these values from initially be the place to handle such a thing? Example of how I would do this in Java (psuedo code):

var newTag = extract the tag's value from my webpage;

var tag_value = SELECT tag_value from html_tag
WHERE tag_type = 'link'

if (new_tag != tag_value) then (INSERT INTO html_tag)...

Should I handle this at the database level, or java level, like directly above?


texts: Query Relating Rows W/O A Corresponding Value

texts:
I have a table setup like this:

ID Code      DateActive   DateInactive  statusCode
  15          20090413       null           A
  15            null         20090416       I
  15          20100101       20130101       I
  16          20130515       20151225       I
  16          20151226       20160101       I

I want to pull results that do not possess a status code of "A" in any of the corresponding rows.  For example, in the table above, I would want only the result set from 16, with a single result grouping all of the possible rows.

    Desired Result:
ID Code       StatusCode
  16               I

I only want those that have an I and do not have an A row in my results.

Have a feeling this might be an easy one, but I can't seem to find a way to solve it.


texts: How do I modify a stored procedure in IBM DB2?

texts:
I have a DB2 database and I'm using Control Center. I've found a procedure in the 'Stored Procedures' folder, which I need to modify. In MS SQL Server and Oracle you can accomplish this right from the DBMS.

Is there a way I can modify this procedure with an editor in DB2 (9.7.0), similar to SQL Server and Oracle?


texts: Party Relationship Model in Practice - Insertions

texts:
I'm moving this question for StackOverflow in hopes to get perspective from a more database-oriented community.

Here is a basic representation of what I'm doing (in pseudo sql):

Parties
- ID, AUTO_INCREMENT
- Type
- Shared_Data
- Primary Key (ID, Type)

User_Sub
- ID
- Type, Default 'U'
- User_Data
- Primary Key (ID, Type)
- Foreign Key 'User-Party' (ID, Type) references (Parties.ID, Parties.Type)

Organization_Sub
- ID
- Type, Default 'O'
- Organization_Data
- Primary Key (ID, Type)
- Foreign Key 'Organization-Party' (ID, Type) references (Parties.ID, Parties.Type)

Because data is spread across multiple tables, I'd like to be able to read from and insert into the parent and child tables at once. Reading is fine, and can be done with a:

CREATE VIEW Users as
SELECT P.*, U.User_Data FROM Parties P
Inner Join Users_Sub U on P.id=U.id, P.type=U.type

Inserting, though, is more difficult.

I can do two consecutive queries: One into the parties table, then one into a sub table.
I can create a stored procedure in order to perform multiple queries in one transaction.
This answer mentioned moving columns into the super table and using a trigger after insert on the super table to insert into the sub table.

It seems to me that it might be more fitting to consolidate these sub-table columns back into the super-table and get rid of the sub-tables entirely. Since I'd probably need to join or union all of the subtables if I wanted to query values from the parties table anyway.

Is this generally how it is done in practice? There will be many unused columns if this is the case, and it will be difficult (though not impossible with triggers) to enforce 'not null' constraints. The case above shows only two sub table types - as the number of sub-types increase, the super table will become more bloated.

Any thoughts or suggestions on this would be much appreciated.


texts: Writing a query to find all employee salaries that are higher than their managers

texts:
I have two tables with some dummy data. I need to find all employees that make more than their managers. I was led to believe the point of the exercise is to learn how to do subqueries.

I can isolate the managers, their salaries, and the companies they work for but I don't know how to proceed to compare their employee salaries to their managers in SQL. The error that gets kicked back to me is that "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." Which I understand but if I can't pass at least the manager's name and salary from a subquery then I don't know how to solve the problem.

If someone can point me towards a similar post that was answered or some reading that can provide some insight, I'd be very grateful.

My query getting info on the managers

SELECT DISTINCT w.salary, w.pname, w.cname
FROM WorksFor w
INNER JOIN Manages m
ON w.pname = m.mname

Employee/Company Table

CREATE TABLE WorksFor (
employerID int identity(1,1) primary key,
pname varchar(30),
cname varchar(20),
salary int
);

INSERT INTO WorksFor(pname,cname,salary)
VALUES
('John Smith','BigStore',27500),
('Jane Doe','SmallStore',19000),
('Adam Scott','BigStore',50000),
('Bonnie Noel','SmallMfg',25000),
('Cassie Johnson','BigStore',35000),
('Donald Eckerson','SmallStore',29000),
('Erin Joel','SmallMfg',49000);

CREATE TABLE Manages (
manageID int identity(1,1) primary key,
pname varchar(30),
mname varchar(30)
);

Manager Table

INSERT INTO Manages(pname,mname)
VALUES
('John Smith','Adam Scott'),
('Jane Doe','John Doe'),
('Bonnie Noel','Erin Joel'),
('Cassie Johnson','Adam Scott'),
('Donald Eckerson','John Doe');


texts:
I'm just getting started on an intro to databases course and have a question about writing relational algebra expressions.

Schema:

Person ( name, age, gender )
Frequents ( name, pizzeria )
Eats ( name, pizza )
Serves ( pizzeria, pizza, price )

I have a practice question like such: Find all pizzerias that serve at least one pizza that Amy eats for less than $10.00.

My answer is: πpizzeria (σname = 'Amy' ^ price < $10 (Person ⨝ Serves))

A correct answer is: πpizzeria (σname = 'Amy' (Eats) ⨝ σprice < $10 (Serves))

I would like to know if these two are equivalent to each other, and if not, why not?


texts: Select row with more of one type than another in a separate table

texts:
I have two tables.

I'm attempting to select customers that have bought more of one type of product than another with at least one of the first type. Let's say for example that I want to get all customers that have purchased more apples than pears but have bought at least one apple.

So far I've come up with the following query:

select customers.id
from customers
where 'apple' in
(
  select type
  from products
  where products.customer_id = customers.id
)
and
(
  select count(p1.id)
  from products p1
  where p1.customer_id = customers.id
  and p1.type = 'apple'
)
>
(
  select count(p2.id)
  from products p2
  where p2.customer_id = customers.id
  and p2.type = 'pear'
)

This query works as is, however is slow, I understand that the subquerys are slowing down the query but haven't been able to find a solution involving joins that is faster for example.

SELECT c.id
      FROM customers as c
      JOIN products p1 ON (c.id = p1.customer_id)
      LEFT OUTER JOIN products p2 ON (
         c.id = p2.customer_id
         AND p2.type = 'pear'
         AND p1.id < p2.id
      )
      WHERE p1.type = 'apple'
      AND p2.id IS NULL


texts: Installing MySQL from a noinstall Zip Archive - Should I Overwrite System Tables and Data?

texts:
I am performing a MySQL upgrade on a system that had MySQL installed using the noinstall Zip archive approach. (It's installed in c:\mysql). In order to keep with this approach, we are avoiding the MySQL Installers and MSI's. More information on noinstall Zip Archives can be found here.

We were upgrading from a lower 5.5.* version to 5.5.52, and the upgrade was a success for the most part. As directed (and recommended) in this documentation, we simply copied the 5.5.52 install over the existing c:\mysql directory.

In doing so, we copied over (overwrote) everything, including the \data\mysql\* files that were in the 5.5.52 installation package, which includes the default system files, such as user table files (i.e. user.frm, user.MYD, user.MYI), and many other various tables, and artifacts.

Due to these system tables and artifacts being written over, we lost our existing database MySQL users. i.e. the only users in the table are the root users with empty passwords.

There's probably other system level details that were stored in the MySQL tables that we lost.

(Thankfully this was our QA environment, and we did take a mysqldump beforehand)

Question:

Is there a way to avoid this data loss (i.e. not copy over the data directory), and still maintain the integrity of the upgrade?

For example could we skip overwriting the data directory altogether? Or perhaps skip it, and run the mysql_upgrade tool (which we need to run anyways), though I'm not sure if this is relevant to this context.


texts: Repreparation of prepared statements in MySQL/MariaDB, how to find a cause?

texts:
On our server running MariaDB 10.1 (the latest Debian package), with low load (load average: 0.79, 0.88, 1.00), I often receive error message that "Prepared statement needs to be re-prepared".

It is difficult to reproduce, it appears randomly, approximately 40-50 times per day.

I have read documentation about repreparation, where is the following explanation for repreparation:

  Repreparation occurs after DDL statements such as those that create, drop, alter, rename, or truncate tables, or that analyze, optimize, or repair tables.
  Repreparation also occurs after referenced tables or views are flushed
  from the table definition cache, either implicitly to make room for
  new entries in the cache, or explicitly due to FLUSH TABLES.

I'm not aware of any DDL statements running on the server. Our application does not use "FLUSH TABLES". Except our application only internally used Phabricator is running (used by 8 devs) and two other minimally visited applications written in PHP using MariaDB. Here are command statistics:

Do you have any idea, how to find the cause for repreparation?
I don't want to blindly increase table_definition_cache (currently 400) or table_open_cache (2000). Or do you think it is to small and it is safe to increase?

For debugging info: SHOW GLOBAL STATUS, SHOW VARIABLES and memory info from the server.


texts: MySQL Triggers Different Database Different Server

texts:
I have two databases, each on a separate server. Each database has a number of similar but not identical tables. I need to combine the records from these similar tables into a new database, which is on one of the two servers.

SERVER A - Database 1

SERVER B - Database 2, Database 3 (new)

My thinking (as a relative novice re MySQL) is to first Insert existing records into the tables of the combined database, then use Insert|Update|Delete Triggers to maintain those records in the new db.

This would allow me to start with one roughly matching table from each of the two original databases, and gradually go through all the rest of the tables.

Is it possible to create Triggers on Database 1, which updates a table in Database 3 (new), which resides on a different server?

Any suggestions would be most welcome on how to accomplish this.


texts:
In our app, we are building a search for real estate listings.
There are a number of search types:
1. Based on predefined parameters
This one is pretty straightforward and achievable with a simple, potentially long, where clause (unless I am not aware of a more fancy/efficient way to do it).
2. Free text search
This option is a bit more complex, but also clear. It was done by creating a weighted vector column and the bases for it looks something like this:
ALTER TABLE listing ADD COLUMN if not exists weighted_tsv tsvector;

UPDATE listing SET
    weighted_tsv = l.weighted_tsv
FROM (
    SELECT id,
           setweight(to_tsvector('english', COALESCE(mls_num,'')), 'A') ||
           setweight(to_tsvector('english', COALESCE(city,'') || ' ' || COALESCE(state_or_province,'')), 'B') ||
           setweight(to_tsvector('english', COALESCE(unparsed_address,'') || ' ' || COALESCE(city,'') || ' ' || COALESCE(state_or_province,'') || ' ' || COALESCE(postal_code,'')), 'C')
           AS weighted_tsv
     FROM listing
) AS l
WHERE l.id = listing.id;

CREATE OR REPLACE FUNCTION listing_weighted_tsv_trigger() RETURNS trigger AS $$
    BEGIN
      new.weighted_tsv :=
         setweight(to_tsvector('english', COALESCE(mls_num,'')), 'A') ||
         setweight(to_tsvector('english', COALESCE(city,'') || ' ' || COALESCE(state_or_province,'')), 'B') ||
         setweight(to_tsvector('english', COALESCE(unparsed_address,'') || ' ' || COALESCE(city,'') || ' ' || COALESCE(state_or_province,'') || ' ' || COALESCE(postal_code,'')), 'C');
      return new;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_listing_weighted_tsv BEFORE INSERT OR UPDATE
ON listing
FOR EACH ROW EXECUTE PROCEDURE listing_weighted_tsv_trigger();

CREATE INDEX if not exists listing_weighted_tsv_idx ON listing USING gin (weighted_tsv);

Of course, the document might be changed and adobted as we go.
A search query example might look like this:
select l.*, ts_rank_cd(weighted_tsv, query) as rank
from listing l, to_tsquery('english', 'NB05:* | vancouver') query
where weighted_tsv @@ query
order by rank desc;

3. Facets
Here is where we are stuck. The main target is to create an autocomplete for the user when he types in free text in a search. And the idea is to create a categorized list of suggestions ordered by relevance.
For example: If the user starts typing in something that matches mls number, some listing addresses, and some in description, we need to create a categorized list similar to:
[
  {
    match: '<mls num>',
    type: 'listing'
  },
  {
    match: '<address>',
    type: 'address'
  },
  {
    match: '<address>',
    type: 'address'
  },
  {
    match: '<description>',
    type: 'description'
  },
]

For every element, we'll need to add more details. This example is just to demo the idea.
Now, this could be achieved with a bunch of union statements, one for each category. However, feels like it is not the most efficient way. It will also create maintenance and indexes nightmare.
Is there a way to build facets like search with postgres?
Searching for this, I didn't find anything remotely useful without using elasticsearch or a like.


texts: What db permissions are needed for pgAgent user?

texts:
I have successfully set up pgAgent daemon (running on the same server as Postgres 9.3). I would like to restrict permissions on pgAgent. Created 'pgagent' login role, and granted it (via group role) all permissions on postgres.pgagent schema:

CREATE ROLE pgagent LOGIN
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE scheduler
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT scheduler TO pgagent;
grant all on schema pgagent to scheduler;
grant all on all tables in schema pgagent to scheduler;
grant all on all functions in schema pgagent to scheduler;
grant connect on database postgres to scheduler;

However pgAgent refuses to execute any jobs, and just sits there idly. No error messages in logs. If I start pgagent as 'postgres' user it runs fine. Or, if I 'grant postgres to scheduler' it also runs fine.

What permissions am I missing here?


texts: Is columnstore a good option for UPDATES with WHERE clauses?

texts:
I am developing a database project on SQL Server and I am thinking whether using columnstore index is a good idea.
The project consists of a table (A) that will hold a large number of rows, with many repeated values for a column. Every day, a pack of new rows will be added to the table, with a "DateId" for each pack.
After that, I will need to update a different table (B) joining with A and filtering A for the "DateId" and other columns.
Example in SQL:
CREATE TABLE A (
  [Id] [BIGINT] IDENTITY(1,1) NOT NULL,
  [DateId] [INT] NOT NULL,
  [B_Id] [BIGINT] NOT NULL,
  -- other columns...
  INDEX cci_A CLUSTERED COLUMNSTORE
)

CREATE TABLE B (
  [Id] [BIGINT] IDENTITY(1,1) NOT NULL,
  -- other columns...
  INDEX cci_B CLUSTERED COLUMNSTORE
)

UPDATE B
SET ...
FROM A
INNER JOIN B ON A.B_Id = B.Id
WHERE A.DateId = @myDateId

Is columnstore a good choice in this case?


texts: How to fetch the string from the column of table in postgresql?

texts:
I have the following table

Userid  District_code
1        05
1        03
2        03
2        04
3        06

and want to get this output:

userid=1 dist_code(05,03)


texts: Upgrade SQL Server 2008 Express to SQL Server Workgroup 2008 Volume License

texts:
I'm in need of some help with SQL Server. I’m fairly new to SQL Server so bear with me.

My situation:

We currently have SQL Server 2008 Express installed and running on Windows Server 2003. We are reaching the max size for SQL Server 2008 Express, so we purchased SQL Server Workgroup 2008 volume license version. Now because the volume license product key is built in, I can’t do a standalone upgrade from the SQL Server 2008 Express version.

How do I go about installing the new workgroup version? Do I uninstall the express version and install the workgroup version? Do I install the workgroup side by side of the express? How do I transfer the data over from the express version to the workgroup?

As you can see, I need help on how to tackle this project.  Please! Any help would be well appreciated


texts: What is the name of the part between SELECT and FROM in a SQL query?

texts:
I am trying to find the right description for the parts of a query:

What is the name of the part between SELECT and FROM? If I only had some columns then I would call it "column identifiers". But if there are any function or subqueries this would be quite a bit more. Is "SELECT clause" a valid description?
Same thing for the FROM part. If I have only one table I would simply call it "table". But if there is a more complicated structure maybe using joins, etc. what would be the correct naming?

To make my questions more clear:

In Postgres I could use a JSON function, e.g. json_array_elements in both parts:

SELECT
    json_array_elements(my_json_column)
FROM
    my_table

or doing this

SELECT
    elem.*
FROM
    my_table,
    json_array_elements(my_json_column) AS elem

If I have to tell somebody that this function can be used within the SELECT clause or as part of the FROM clause which terms are more conventional?


texts: Using Join to get data from multiple tables in SQL Server

texts:
A(a_id, a1, a2)
B(b_id, b1, b2)
C(c-id, a_id, b_id)

Now, I want to fetch all the data of table A, and from table B, I only need a record of b1. How can I use join query in SQL Server to fetch the desired data?


texts: Does PL/SQL code run differently in sql plus vs sql developer?

texts:
Today, I stumbled upon a question someone posted about the / character and ; character.  The Answer doesn't seem to be down-voted, so I questioned it a little more as to why it wasn't down-voted.  Something tells me that sql plus (command line) and sql run via sql developer behave differently.  And the usage of these characters is one example  Is this true?  If so, how can I find these differences?

Reference:

https://stackoverflow.com/questions/1079949/when-do-i-need-to-use-a-semicolon-vs-a-slash-in-oracle-sql


texts: PostgreSQL slower query after partitioning

texts:
I have a 75GB table with more than 100M records.
I've partitioned that table by month. Each partition is ~8M records big.

Most of the queries works on recent data and almost every query filters by the timestamp field used for partitioning. Also, monthly, old (more than 2 years) data is deleted. So it seemed to me that partitioning could help both query performance and old data deletion.

I followed this https://www.postgresql.org/docs/9.5/static/ddl-partitioning.html to partition the tables (the case there is very similar to mine)

Every child table has a CHECK constraint

CONSTRAINT p_inbox_201702_datarilev_check
    CHECK (datarilev >= '2017-02-01'::date
       AND datarilev < '2017-03-01'::date)

and an index on the timestamp field (datarilev).

I've also dropped all the indexes from the parent table (it doesn't seem to make any difference).

Now, if I query the parent table using a date range :

select id
from p_inbox
where datarilev > '2017-02-02'::date
  and datarilev < '2017-02-03'::date
limit 10;

it takes ~ 10 seconds.

If I query a single partition

select id
from p_inbox_201702
where datarilev > '2017-02-02'::date
  and datarilev < '2017-02-03'::date
limit 10

it takes 50 milliseconds.

If I query the non-partitioned table

select id
from p_inbox_full
where datarilev between '2017-02-02' and '2017-02-03'

it takes 200 milliseconds.

These are the results from an explain analyze of the three queries:

Query the parent: https://explain.depesz.com/s/EOoW

Query the partition: https://explain.depesz.com/s/MMnT

Query the full, non-partitioned table: https://explain.depesz.com/s/Bk4Z

It seems that the partitions are excluded correctly: disabling constraint_exclusion, the plan changes accordingly.

So, my question is: is it normal that checking the constraints to exclude out of range tables takes 10 seconds?

Version is PostgreSQL 9.5.7 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit


texts: Multi master single slave replication mysql

texts: I have a scenario in which there are two masters master 1 and master 2 and i want to add slave to master 2 , so when i update in master 1 it will replicate for master 2 so how do i get slave of master 2 to update form it ? What is the  parameter to be set for it?


texts: Postgres cannot simulate table with a view: column must appear in the GROUP BY clause or be used in an aggregate function

texts:
I am trying to replace a table in our application by using a View. It works well for the most part, but I can't get past this error: "column must appear in the GROUP BY clause or be used in an aggregate function"

Steps to reproduce:

CREATE TABLE example_t (
    did serial PRIMARY KEY,
    a text,
    b text
);
INSERT INTO example_t(a, b) VALUES ('a', 'b');

CREATE VIEW example_t_v AS
    SELECT t.did as did, t.a as a, t.b as b
    FROM example_t t;

Now this query works fine:

SELECT t.a, t.b FROM example_t t GROUP BY (t.did);

But an identical query from the view fails:

SELECT t.a, t.b FROM example_t_v t GROUP BY (t.did);

ERROR:  column "t.a" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1:   SELECT t.a, t.b FROM example_t_v t GROUP BY (t.did);

It would be pretty hard to rewrite the application layer (since Django is generating the queries and it likes to use GROUP BY a lot) so is there a solution on the db layer?


texts:
I have three tables:

users

+----+-------+
| id | name  |
+----+-------+
| 1  | dave  |
| 2  | cher  |
| 3  | meg   |
| 4  | sarah |
+----+-------+

groups

+----+-------+
| id | name  |
+----+-------+
| 1  | admin |
| 2  | super |
+----+-------+

user_groups

+----+---------+----------+
| id | user_id | group_id |
+----+---------+----------+
| 1  | 1       | 1        |
| 1  | 2       | 1        |
+----+---------+----------+

I want to create a query so that I return an array of group names which contains an array of users. I don't want to return groups which have no users, but I would like to return users which have no group.

admin
    - dave
    - cher
- meg
- sarah

At the moment I am getting the groups which have items first.

SELECT name FROM groups
INNER JOIN user_groups ON groups.id = user_groups.group_id

Then iterating through the groups and adding users.

SELECT id, name FROM users
INNER JOIN user_groups ON users.id = user_groups.user_id
WHERE user_groups.group_id = $group_id

Is there a way to combine these queries?


texts: Writing query for sorting based on foreign attribute

texts:
I am creating a voting system. My database looks like .

Vote is a 'wrapper' for subitems. A vote has many subitems. When an action on that wrapper is performed (create/update/edit),a row is added in the vote_action table with the appropriate data.
If an action is done on a subitem(create/update/edit, most importantly vote cast/uncast) , a row is added in vote_sub_item_action.
To get an array of user ids that have voted on a particular subitem, I use this query:

select act.user_id,act.anonymous
from ( select sub_item_id,user_id ,max(created_at) as newestAction
       from vote_sub_item_action
       where sub_item_id =:sub_item_id AND action_type IN('ACTION_CAST_VOTE', 'action_uncast_vote')
       GROUP BY user_id ) as x
inner join vote_sub_item_action as act on  act.user_id = x.user_id and
act.created_at = newestAction and act.action_type='action_cast_vote' and
act.sub_item_id =x.sub_item_id;

The subitem 'belongs' to a user if the last action that he performed from action:cast and action:uncast , is cast. That means he has a vote on that subitem.

It is not in the image that I have provided, but now vote wrappers can have a deadline(timestamp).
I want to display the vote wrappers that are ending soonest(this can be easily done) in which a particular user has NOT YET VOTED.
After that, display other votes that are ending soonest.


texts: Unable to change innodb_log_file_size in MariaDB Galera Cluster 10.0

texts:
I have a MariaDB Galera cluster (v. 10.0.19) with 3 nodes. I want to increase the innodb_log_file_size (i.e. the size of the InnoDB redo log files) on one of the nodes to match what it is on the other two nodes, i.e. 750M.

On this particular node, just changing the variable in the .cnf file + restarting mysql does nothing. MariaDB appears to just ignore it, and there are no warnings or errors or anything in the MariaDB error log on the node. The size of the log files is stuck at 100M.

On the other two nodes I can change the value of this variable in the .cnf file and then do "service mysql restart", and the two InnoDB log files will resize accordingly. The size agrees with the result from

  show global variables like 'innodb_log_file_size';

I'm using the same version of MariaDB Galera cluster on all 3 nodes, and the cluster and all 3 nodes are otherwise healthy as far as I can tell.

There was an issue previously with importing a backup with a large BLOB / TEXT value, and I do have the below in the error log, although this is a while ago now:

160408 15:09:55 [ERROR] InnoDB: The total blob data length (60192222) is greater than 10% of the total redo log size (209715200). Please increase total redo log size.
160408 15:09:56 [ERROR] Slave SQL: Could not execute Write_rows_v1 event on table confluencedev.PLUGINDATA; The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size., Error_code: 1118; Got error 139 "Too big row" from storage engine InnoDB, Error_code: 1030; handler error HA_ERR_TO_BIG_ROW; the event's master log FIRST, end_log_pos 60193176, Internal MariaDB error code: 1118
160408 15:09:56 [Warning] WSREP: RBR event 2 Write_rows_v1 apply warning: 139, 286800
160408 15:09:56 [Warning] WSREP: Failed to apply app buffer: seqno: 286800, status: 1
     at galera/src/trx_handle.cpp:apply():351
Retrying 2th time

And then:

160408 15:09:57 [ERROR] WSREP: Failed to apply trx 286800 4 times
160408 15:09:57 [ERROR] WSREP: Node consistency compromized, aborting...

The node died and it was resurrected with SST. During this process I got this:

160419  9:56:46 [Note] InnoDB: The log sequence numbers 15287730891 and 15287730891 in ibdata files do not match the log sequence number 25623829121 in the ib_logfiles!
160419  9:56:46 [Note] InnoDB: Database was not shutdown normally!
160419  9:56:46 [Note] InnoDB: Starting crash recovery.
160419  9:56:46 [Note] InnoDB: Reading tablespace information from the .ibd files...
160419  9:56:48 [Note] InnoDB: Restoring possible half-written data pages
160419  9:56:48 [Note] InnoDB: from the doublewrite buffer...
160419  9:56:49 [Warning] InnoDB: Resizing redo log from 2*48000 to 2*6400 pages, LSN=25623829121
160419  9:56:49 [Warning] InnoDB: Starting to delete and rewrite log files.
160419  9:56:49 [Note] InnoDB: Setting log file ./ib_logfile101 size to 100 MB
InnoDB: Progress in MB: 100
160419  9:56:50 [Note] InnoDB: Setting log file ./ib_logfile1 size to 100 MB
InnoDB: Progress in MB: 100
160419  9:56:51 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
160419  9:56:51 [Warning] InnoDB: New log files created, LSN=25623829516
160419  9:56:51 [Note] InnoDB: 128 rollback segment(s) are active.
160419  9:56:51 [Note] InnoDB: Waiting for purge to start

innodb_log_file_size is stuck at 100M:

MariaDB [(none)]> show global variables like 'innodb_log_file_size';
+----------------------+-----------+
| Variable_name        | Value     |
+----------------------+-----------+
| innodb_log_file_size | 104857600 |
+----------------------+-----------+
1 row in set (0.00 sec)

Yet /etc/my.cnf.d/server.cnf defines the value as 750M:

  [mariadb-10.0]
  ...
  innodb_log_file_size=750M

What am I missing?

Edited to add: I've found that I'm able to force the innodb_log_file_size by giving it as a parameter on the command line:

sudo /sbin/service mysql start --innodb-log-file-size=786432000


texts: Create copy of Azure SQL Database using SQL Server Management Studio

texts:
When running the following query on an Azure SQL Database "Master" in SQL Server Management Studio:

CREATE DATABASE myDbBackup AS COPY OF myDb;

The following error is produced:

Msg 40818, Level 16, State 1, Line 0
Operation on database 'myDb' failed because there are alter operations pending on the database. Try again after the pending operations have completed.

Any guidance on how to resolve this and end all alter operations that are pending so I can go through with the copy? There is only one other person using this DB and neither he nor I are running a query. I even went so far as to revoke the firewall permissions to the server.. not even this worked. Any thoughts?


texts: logged on as sysdba, how to run dml script as another user if you do not have the passwrod

texts: logged on as sysdba to an oracle database, how to run dml script as another user (Joebloggs) if you do not have the password. The scripts will run dml that updates objects in (jobbloggs) schema.


texts: Can't create any objects within OLAP database

texts:
Problem

I can't create any objects under my databases in my Analysis Server. My context menus on all the folders under this test database just have Refresh as an option.

Details

My Analysis Server is running locally. I am a local admin. I am running SSMS as Administrator.

I have tried running the Analysis Services as both Local System and my user.

During setup, I added my user to the list of users who can administer Analysis Services.

More Information


texts: Can we downgrade oracle database form 12cR2 to 12cR1

texts: I have upgraded the Oracle database to 12cR2, now I want to downgrade it to 12cR1.Is this possible?What are the conditions?


texts:
I am using Oracle 12c Standard Edition.

I accidentally drop the table and try to recover it, but the table does not appear in Recyclebin.

I think the tablespace is due to SYSTEM.

Create table test(
 aaa varchar2(20)
 ....
)
 TABLESPACE SYSTEM
 STORAGE (
  NEXT 1024K
 )
/

thank!


texts: I have a Mysql database deployed on Amazon web services RDS. I am running a crontab which runs mysqldump command. The problem is it freezes the rds instance and my website will be down for more then half and hour almost. It will be inaccessible. Is there any way to stop the freeze during the dump and website will be accessible during the dump also.


texts:
I have a table and an indexed view on it like

Create table mytable1 (ID int identity(1,1), Name nvarchar(100))

Create table mytable2 (ID int identity(1,1), Name nvarchar(100))

Create view myview
with schemabinding
as
   select a.name, b.name
   from mytable1 a
   join mytable2 b on a.Id = b.Id

Now if I run the following query

select a.name, b.name
from mytable1 a
join mytable2 b on a.Id = b.Id

It does not use my indexed view. Is there any hint (or other way) to force SQL Server to use indexed view instead?

I have a big system, and need to optimise it. I can't change all my SQL scripts to select from the view instead of tables. I want to create indexed views and force SQL Server to get data from them instead of tables.

I am using SQL Server 2014 Enterprise Edition.


texts: How can I improve my query, using joins & improving performance?

texts:
I have a really smelly query which I'm not sure how to simplify, this is quite an intensive query.  I have tried various union and inner joins in an attempt to simplify it and my biggest problem is getting around the following error:

  Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column

My query below solves this but it is not very efficient.

What am I trying to achieve?

When a media item has been deleted, I wish to soft delete all products where that media is associated providing that product does not have any other media files associated.

Schema

+-----------+----------+
| entity_id | media_id |
+-----------+----------+
|       192 |        1 |
|       193 |        1 |
|       193 |        2 |
+-----------+----------+

I have a composite Primary Key index on entity_id and media_id
I have a Foreign Key constraint on entity_id to product table
I have a Foreign Key constraint on media_id to product_media table

When I delete media #1 I would like to be able to update entity_id #192 to set deleted_at to the current timestamp.  I DO NOT want to update entity_id #193 because another media is associated to it.

UPDATE product
SET deleted_at = NOW()
WHERE id IN (
    SELECT entity_id
    FROM product_media
    WHERE media_id = ?
    AND entity_id NOT IN (
        SELECT entity_id
        FROM product_media
        GROUP BY entity_id HAVING COUNT(media_id) > 1
    )
)

As you can see there are two sub-queries, the fact I have 1 sub-query is somewhat of a concern but having two I know i'm doing something wrong but I cannot think for the life of my how to get around this?


texts: How to break out of error loop in transactional replication?

texts:
I was running some simple tests on a publisher and subscriber on the same instance:

I inserted 50,000 rows into a published article and the data was
correctly pushed to the subscriber.
I inadvertently deleted the 49,985th record on the subscriber (but
didn't realize it at the time)
I deleted 50,000 rows from the published article

As I was watching the size of the subscriber table, I noticed it would drop down in row count almost to zero and then go back to 50,000. Deletes would start running again, the row count would drop, and then it back to 50,000. This kept happening over and over.

I ran a standard trace and saw all the deletes running normally. After it was almost complete, this statement appeared:

IF @@TRANCOUNT > 0 ROLLBACK

Then I modified the trace to incude error messages and saw this:

The row was not found at the Subscriber when applying the replicated command.

So it looks like when the distribution agent is executing all the deletes one-by-one, the process is still included in a transaction (the original DELETE was one line). If there is an error, it performs a ROLLBACK and then starts over.

My questions:

Is there a standard way to break out of the loop?
Would it be acceptable to recreate the missing row so the process
can complete?
What do you do if this happens on production? I guess that's not
really a question. But this seems like a simple error could have
some pretty serious complications unless replication is monitored
very carefully.


texts:
Say we have two tables 'Vendors' and 'Invoices'.
Then I understand a reason for doing a left join of
'Vendors' with ' Invoices'  along the VendorID column is to obtain information on Vendors without Invoices. A right join is basically a left
join with tables interchanged (This is the main motivation,right)?

But I cannot see a general motivation from cross joins in this sense,
i.e., if I am given tables Vendors, Invoices joined along VendorID like above, what information is it that Vendors Cross Join allows me to obtain that I cannot obtain through a left join, or otherwise?

Thanks.


texts: How to reinsert corrected rows from the conflict table?

texts:
I have a bidirectional merge replication. I had failure constraints because the primary key was just integer. I change the primary key to the old primary key + a location identifier.The problem is how can I reinsert the old rows of conflict table ( that I can correct manually from MSmerge_conflict_) to the publishers and subscribers.
can you help me please?

sorry for making faults, I'm not english speaker


texts: Sum Up values in a query based on other information

texts:
I am trying to grab the sum of 2 columns if 1 column is the same. I currently have a record-set that looks like this:

I get these results by running this statement:

 select distinct a.eventnum, a.num_cust, a.out_minutes, d.xpers
 FROM mv_outage_duration a
 INNER JOIN mv_aeven d
 ON d.Num_1 = a.eventnum
 and (d.DEV_NAME = 'T007F12127')
 and d.rev_num = (select max(rev_num)
 from mv_aeven d
 where a.eventnum = d.Num_1)
group by a.eventnum, a.num_cust, a.out_minutes, d.xpers

How do I get the sum of Num_cust and Out_Minutes for the record if the eventnum is the same?

I'd like to return 1 and only 1 row for each event number, and if there are more than 1 step, I'd like to add the Num_cust and Out_minutes for each step.

I've tried

 select distinct a.eventnum, a.num_cust, a.out_minutes, d.xpers, sum(a.Num_cust)
 FROM mv_outage_duration a
 INNER JOIN mv_aeven d
 ON d.Num_1 = a.eventnum
 and (d.DEV_NAME = 'T007F12127')
 and d.rev_num = (select max(rev_num)
 from mv_aeven d
 where a.eventnum = d.Num_1)
group by a.eventnum, a.num_cust, a.out_minutes, d.xpers

and it just returns the results as a new column sum(a.num_cust).

I also tried

 select distinct a.eventnum, a.num_cust, a.out_minutes, d.xpers, sum(select Num_cust from mv_outage_duration a where a.eventnum = d.num_1)
 FROM mv_outage_duration a
 INNER JOIN mv_aeven d
 ON d.Num_1 = a.eventnum
 and (d.DEV_NAME = 'T007F12127')
 and d.rev_num = (select max(rev_num)
 from mv_aeven d
 where a.eventnum = d.Num_1)
group by a.eventnum, a.num_cust, a.out_minutes, d.xpers

...but that just wouldn't run at all.

Here's some statements to set everything up

Create table mv_outage_duration( eventnum, num_cust, out_minutes, restore_dts, off_dts, cause, feeder, dev_name)
create table mv_aeven (Num_1, rev_num, xpers, weather_code, completion_remarks)

Insert into "mv_outage_duration" (EVENTNUM,NUM_CUST,OUT_MINUTES,RESTORE_DTS,OFF_DTS,CAUSE,FEEDER,DEV_NAME) values ('T00000000133',79,11,'20130307085914CS','20130307084811CS','10','17FL012011','T007F12127');
Insert into "mv_outage_duration" (EVENTNUM,NUM_CUST,OUT_MINUTES,RESTORE_DTS,OFF_DTS,CAUSE,FEEDER,DEV_NAME) values ('T00000000133',61,13,'20130307090200CS','20130307084811CS','10','17FL012011','T007F12127');
Insert into "mv_outage_duration" (EVENTNUM,NUM_CUST,OUT_MINUTES,RESTORE_DTS,OFF_DTS,CAUSE,FEEDER,DEV_NAME) values ('T00000000014',61,4,'20130304140400CS','20130304135945CS','09','17FL012011','T007F12127');
Insert into "mv_outage_duration" (EVENTNUM,NUM_CUST,OUT_MINUTES,RESTORE_DTS,OFF_DTS,CAUSE,FEEDER,DEV_NAME) values ('T00000000173',79,1,'20130307161532CS','20130307161424CS','01','17FL012011','T007F12127');
Insert into "mv_outage_duration" (EVENTNUM,NUM_CUST,OUT_MINUTES,RESTORE_DTS,OFF_DTS,CAUSE,FEEDER,DEV_NAME) values ('T00000000173',61,3,'20130307161800CS','20130307161424CS','01','17FL012011','T007F12127');

Insert into "mv_aeven" (NUM_1,REV_NUM,XPERS,WEATHER_CODE,COMPLETION_REMARKS) values ('T00000000014',10,796072,'LIGHTNING IN AREA','COMPLETETION REMARKS FROM TRUCK ON TOFS. ');
Insert into "mv_aeven" (NUM_1,REV_NUM,XPERS,WEATHER_CODE,COMPLETION_REMARKS) values ('T00000000014',11,796072,'NORMAL FOR SEASON','COMPLETETION REMARKS FROM TRUCK ON TOFS.');
Insert into "mv_aeven" (NUM_1,REV_NUM,XPERS,WEATHER_CODE,COMPLETION_REMARKS) values ('T00000000173',7,79607,'LIGNTNING IN AREA','wetr');
Insert into "mv_aeven" (NUM_1,REV_NUM,XPERS,WEATHER_CODE,COMPLETION_REMARKS) values ('T00000000173',6,79607,'LIGNTNING IN AREA','wetr');
Insert into "mv_aeven" (NUM_1,REV_NUM,XPERS,WEATHER_CODE,COMPLETION_REMARKS) values ('T00000000133',7,796072,'THUNDERSTORM','Testing Step Restore for Kasey');
Insert into "mv_aeven" (NUM_1,REV_NUM,XPERS,WEATHER_CODE,COMPLETION_REMARKS) values ('T00000000133',6,796072,'THUNDERSTORM','Testing Step Restore for Kasey');


texts:
Taking too long to drop a 500gb+ SQL Server database. My question being what ways can we drop a database other than through GUI or T-SQL? Would deleting the .mdf and .ldf files from the server help?
I can detach and delete the mdf and ldf files. I just want to know the fastest way to drop it. It took me close to 11hrs without any results so I killed it and was hoping someone might know how to do it.


texts: SQL Server account permissions for New ReportServer database from wizard

texts:
I'm trying to create a ReportServer database on a different server (Server_AT) other that my Reporting Service server (Server_RT).
ON Server_AT, I created the RSExecRole as per the script here. Then created a login 'Reporter' with user 'Reporter' and assigned the RSExecRole.
Now when I use the configuration wizard -> create a new ReportServer database-> enter the SQL Server credential for Reporter as prompted-> and finish.

Now the process fails - constantly with on error after another that one of permissions in the RSExecRole cannot be found.
The ReportServer database does gets created on Server_AT but the user is dbo- but I am not able to connect to this database either (when I try next time).

What am I missing here?


texts: SQL Query: Print string (i.e. header) in output before returning table results

texts:
I’m using the following query to generate a summary of invoices for a particular customer.

SELECT
customer_account,
store_number,
invoice_total

FROM invoices WHERE company LIKE '%Joe’s Bait Shop%';

This SQL query is used as an input in a homegrown application which executes the query against the DB, and outputs the results to an Excel spreadsheet. The output looks something like:

customer_account    store_number    invoice_total
222                 13              22.90
333                 16              56.00
444                 21              18.21

Before this spreadsheet can be sent to the customer, text or “header” information must be manually added to the file such as:

Frank’s Fish Supply
Remit To
123 First St., Wonderland, HI 12345

Is there anything I could add to the SQL query that display this text in the output, before returning the table data? Ideally, I would want the SQL query to return:

Frank’s Fish Supply
Remit To
123 First St., Wonderland, HI 12345

customer_account    store_number    invoice_total
222                 13              22.90
333                 16              56.00
444                 21              18.21

I tried using something like:

PRINT N’Frank’s Fish Supply’;
PRINT N’Remit To’;
PRINT N’123 First St., Wonderland, HI 12345’;
PRINT N’’;

SELECT
customer_account,
store_number,
invoice_total

FROM invoices WHERE company LIKE '%Joe’s Bait Shop%';

However, the results of the PRINT commands only show up in the “Messages” tab of SSMS rather than the “Results” tab, and do not appear in the Excel output when run through the homegrown application. Are there any SQL commands I could use to output this static text before the results of SELECT statement?


texts: Prevent SSMS changing tab to space when copying text from Result Tab

texts:
SSMS 2016 changing tabs to spaces when copying from Result Tab (does not matter ctrl+c or drag&drop).
For example if execute this code

SELECT 'a' + CHAR(9) + 'b'

then copy text from result tab and paste in any editor it will be space instead tab between 'a' and 'b' characters.

How to prevent this behaviour?

P.S.: Saving result to CSV working properly and saving tab (code 9). Error caused only on copying.


texts: Pulling data from identical tables in different databases

texts:
I'm dealing with a situation in SQL Server 2008 where each client is setup as their own database, with each database having identical table structures.

I have a query that selects from across ~20 different tables within a database, and inserts the records into a reporting database.  What we'd like to do is repeat this process for each database, so that all reporting information across all the client databases are then contained in a consolidated reporting database.

The "easy" way would be to just copy/paste the code and do a find/replace all on the database names to have the script execute across all clients.  Is there any better way than that though?


texts: Stored Procedure triggered by Service Broker can't access another database in SQLServer 2017

texts:
I make an extensive use of SQL Server Service Broker to trigger/parallelize data science tasks (data imports with SSIS or direct queries to another database on the same server, then data management and machine learning with R and/or Python external scripts).
I wanted to upgrade from SQL Server 2016 to SQL Server 2017, exact same architecture and scripts, but now I get errors I don't understand, apparently caused by the service broker impersonations and privileges.

The activated proc '[StoredProcTriggerdByServiceBroker]' running on queue 'queuename' output the following:  'The server principal "sbuser" is not able to access the database "mainDB" under the current security context.'

To explain a bit a simplification of my process:
There are two DBs, mainDB and testDB. ServiceBroker and stored procedures are running on testDB.
We mainly use Windows logins to use SQL Server and that was less than ideal for some SSIS purposes, so I chose to create a user called sbuser for which connection is disabled but which we can impersonate, with sufficient privileges on both DBs to do everything required. The queues are triggering the stored procedure as sbuser.
The stored procedure fails on statement
SELECT * INTO testDB..targetTable from mainDB..sourceTable

Same select query, impersonating sbuser manually works fine.
Same select query, in a stored procedure, with a execute as sbuser / exec StoredProcedure works fine.
But it fails whenever the stored procedures are triggered by Service Broker.
I added to the stored procedure a logging of CURRENT_USER and results of
SELECT * into tmp_session FROM sys.dm_exec_sessions WHERE session_id = @@SPID;

CURRENT_USER give me sbuser, which is what I expect, but the content of dm_exec_sessions puzzles me as it's different from what I get when I log in as SA, then execute the procedure impersonating sbuser with an execute as.

security_id
login_name
context_info
original_security_id
original_login_name

0x01
sa
0x
0x01
sa

Nothing about sbuser... then again the error message in the log is about sbuser's security context...
So, what has changed between SQL Server 2016 and 2017 in the way the Service Broker works and impersonation of logins and their privileges, and how can I run my select into query now?


texts: MySQL Transaction with rollback upon @@error

texts:
I have done several transactions with MySQL but mainly using php's libraries as the handle. Using php you would have something as:

$pdo->beginTransaction();
$pdo->execute('
    query1;
    query2;
    query3;
    query4;
');
if (exception)
    $pdo->rollback();
else
    $pdo->commit();

At this moment I know that the transaction will rollback because PDO is expecting an error from MySQL that will trigger the command to rollback the transaction.

Is there anyway with mysql to do something as:

begin transaction;
query1;
if SQLEXCEPTION
    rollback and exit;
query2;
if SQLEXCEPTION
    rollback and exit;
query3;
if SQLEXCEPTION
    rollback and exit;
query4;
if SQLEXCEPTION
    rollback and exit;
commit;

The idea would be to avoid continuing execution of the query if an error has been detected.

When we are dealing with a batch of 5 queries that might be ok to run through all but when dealing with a batch of 200 of them, that is a cost that I would prefer to be avoided.

I heard that there are other databases capable of doing this so can MySQL perform such task and how that would be done ?


texts: Multi-Site clustering on Windows 2012 for SQL Server 2012

texts:
I am hoping someone can point me in the right direction here.  We are trying to do a proof of concept for a 3 node multi-site cluster on Windows Server 2012 R2 for our SQL Server environment.  There will be two different subnets, one in each datacenter.  I have come across many articles telling me how to set up the cluster but what I'm having trouble with is the IP address for the cluster.

Does anyone know how I can add the IP from the second datacenter into the cluster?  I have come across articles that mention I would have to add an OR dependency for the second IP but I am not sure how to make this IP even appear as an option to add.  Am I supposed to have the IP address added to the NIC?

I hope my questions aren't confusing and that I'm conveying them clearly.


texts: How can I alter the output for the sql desc command?

texts:
When I do desc table, I get back the columns Name, Null? and Type.  What I also wish to get back is a column like UNIQUE to tell me if the column is in fact unique.  I realize that this is a combination of columns labeled UNIQUE with the column (if present) labeled as the primary key for that table.

As an alternative, I have tried:

SELECT
column_name "Name",
nullable "Null?",
concat(concat(concat(data_type,'('),data_length),')') "Type"
FROM user_tab_columns
WHERE table_name='TABLE_NAME_TO_DESCRIBE';

which I found somewhere, however I get 0 rows returned when I replace the TABLE_NAME_TO_DESCRIBE with the name of my table.

I am running from sqlplus.  Any way to accomplish this?  Either specific (returning of UNIQUE constraint) or general (how to alter desc command results) answers appreciated.


texts: Find the last SQL statement executed for a given connection

texts:
MySQL running on Windows 7 64 bit.

I'm trying to track down a leak of connections, I'm pretty sure the only ASP.NET app using the server is at fault here.

We received the "Too many connections" error and checking the server we saw many connections with status Sleep.

I know that, fiddling with my.cf, MySQL can be forced to close sleeping connection after a shorter time, but I would rather fix the actual problem instead of the symptoms.

So I was wondering if there's a way to retrieve the SQL statement that was last executed on a given connection.
I think if I could do that on older sleeping connections, I might have an easier time tracking down this leak.

Of course, if anyone can think of an alternative solution for this scenario, any suggestion is very welcome.


texts:
In that past year we started using a D2D network device to store our SQL Server backups.  This device does dedup and compression. It looks just like any standard CIFS share (\\d2d\sql_backups\...). Every thing works normally except we get horrible restore throughput. We only get 2-4MB/sec. We have worked extensively with the vendor. We are doing simple SQL Server restores like the following.

RESTORE DATABASE [test]
FROM  DISK = N'\\d2ddc2\sql_backup\test_backup_2013_02_10_210004_0520927.bak'
 WITH  FILE = 1,
 NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 1
GO

When doing diagnostics the vendor tells us that the SQL Server restore is NOT reading the backup file sequentially but rather it skips forward at times and then back to read the data. This causes numerous buffer problems as the D2D is attempting rebuild the deduped data.

Can anyone confirm or deny this. I have searched extensively on MS, google, various other sites and can't find a definite answer. If it's not true can you point me some authority I can give to our vendor.

Just to clarify. I'm only concerned about how the actual .bak is read. This has nothing to do with the database files or IO.

Thanks
Mike


texts: seafile-server error: sqlite3_prepare_v2 failed - disk I/O error (or http error?)

texts:
Two days ago my seafile-server (v7.0.5) stopped working, with error logs that look like below. What could be going on - not clear to me if this is an error in the database, in http-server, or in sqlite3_prepare_v2... any advice would be most welcome!

[12/11/2019 08:01:54 AM] http-server.c(853): DB error when check repo existence.
[12/11/2019 08:01:55 AM] ../common/seaf-db.c(425): Error prepare statement SELECT 1 FROM Repo WHERE repo_id=?: sqlite3_prepare_v2 failed: disk I/O error.
[12/11/2019 08:01:55 AM] http-server.c(853): DB error when check repo existence.
[12/11/2019 08:01:55 AM] ../common/seaf-db.c(425): Error prepare statement SELECT 1 FROM Repo WHERE repo_id=?: sqlite3_prepare_v2 failed: disk I/O error.
[12/11/2019 08:01:55 AM] http-server.c(853): DB error when check repo existence.
[12/11/2019 08:01:55 AM] ../common/seaf-db.c(425): Error prepare statement SELECT 1 FROM Repo WHERE repo_id=?: sqlite3_prepare_v2 failed: disk I/O error.
[12/11/2019 08:01:55 AM] http-server.c(853): DB error when check repo existence.
[12/11/2019 08:02:07 AM] ../common/seaf-db.c(425): Error prepare statement SELECT repo_id, commit_id FROM Branch WHERE name='master' AND repo_id IN ('5f8cb2cc-78c4-42c2-8e61-45d3e08980f4','16e81793-8d3f-4252-8bb1-2e289f6ca1cb','7c86a0df-d745-4018-be49-325eade14d8a','f7428c97-1934-49b4-9228-17c2302aea30','cd1a0ccd-4650-438d-b090-3884c3bbf536','e80476b5-fc66-4eaf-a63c-5018ff08814b'): sqlite3_prepare_v2 failed: disk I/O error.


texts: Mirror data on local SSD storage to conserve EBS I/O?

texts:
I have an AWS instance with local (ephemeral) SSD storage backed by a permanent EBS storage. Is it possible to configure the local storage to act as a mirror/cache for row data in MySQL? I do not wish to cache resultsets, but the actual tables. Naturally, I want my writes to hit not only the local disk but also EBS for safety.

If I could offload majority of my queries to local SSDs, I'd have more consistent response latency and significantly lower I/O bill in AWS.

The instance memory cannot fit all of the data in RAM (yes, that would be ideal) but my local SSD is large enough fit all of the data.

RAID1 involving local SSD and EBS is not going to help me reduce the I/O bill, but it would improve latency. An ideal solution would give me both.

Creating another read-only slave DB with local SSDs is another option but perhaps there is a way to get what I want in a single-instance setup?

Any advice greatly appreciated.

Follow up: I'm currently investigating a setup similar to what's described here: http://tansi.info/hybrid/

This is the setup that appears to work well:
http://hatim.eu/2014/05/25/leveraging-ssd-ephemeral-disks-in-ec2-part-2/


texts:
Based on this query, if I see a low amount of total reads (very close to 0 or 0, like 1 or 2) and a high or moderate amount of user updates (I couldn't find inserts or deletes with this query) with a large row count, I should in theory remove the index.

SELECT DISTINCT
    OBJECT_NAME(s.[object_id]) AS ObjectName
       , p.rows TableRows
       , i.name AS [INDEX NAME]
       , (user_seeks + user_scans + user_lookups) AS TotalReads
       , user_updates UserUpdates
FROM sys.dm_db_index_usage_stats s
    INNER JOIN sys.indexes i ON i.[object_id] = s.[object_id]
        AND i.index_id = s.index_id
    INNER JOIN sys.partitions p ON p.object_id = i.object_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
       AND s.database_id = DB_ID()
       AND i.name IS NOT NULL
ORDER BY (user_seeks + user_scans + user_lookups) ASC

I want to cross-check the accuracy of this assumption here.  For instance, an index that has existed for over a year but has never been read, but highly updated, seems like it would be a bad idea to have.  Is there a scenario where this assumption is invalid?


texts: SQL query to get monthly sales per product, including products with no sales

texts:
Let's say we have a Product table (product_id, product_name) and a Sales table (product_id, date, qty, amount).

What sql query would return the monthly sales per product, and include products with no sales?

(I'm using sql server if that makes any difference).

Clarification: I want a row for each possible month/product tuple. If there was no sales for a given month/product it should show zero qty/sales


texts: Very weird result of CTEs and subqueries

texts:
WITH t1 AS (SELECT a.id,
                   AVG(standard_qty) std_avg,
                   AVG(poster_qty) pos_avg,
                   AVG(glossy_qty) gloss_avg
            FROM accounts a
            JOIN orders o
            ON a.id = o.account_id
            GROUP BY 1),

     t2 AS (SELECT MAX(std_avg) max_std_avg,
                   MAX(pos_avg) max_pos_avg ,
                   MAX(gloss_avg) max_gloss_avg
            FROM t1)

SELECT std_id , max_std_avg, pos_id , max_pos_avg, glos_id , max_gloss_avg
FROM(SELECT
        (SELECT id std_id  FROM t1,t2 WHERE std_avg = max_std_avg),
        (SELECT id pos_id  FROM t1,t2 WHERE pos_avg = max_pos_avg),
        (SELECT id glos_id FROM t1,t2 WHERE gloss_avg =max_gloss_avg)
    )foo ,t1,t2

RESULT:

std_id  | max_std_avg| pos_id | max_pos_avg | glos_id |  max_gloss_avg
-----------------------------------------------------------------------
   1341 | 1891.7777  |   4251 | 2184.4615   |    4211 | 523.258
   1341 | 1891.7777  |   4251 | 2184.4615   |    4211 | 523.258
'
'
'
'
'
   1341 | 1891.7777  |   4251 | 2184.4615   |    4211 | 523.258
(350 ROWS)

However, the output should be only 1 single row:

std_id  | max_std_avg| pos_id | max_pos_avg | glos_id |  max_gloss_avg
-----------------------------------------------------------------------
   1341 | 1891.7777  |   4251 | 2184.4615   |    4211 | 523.258

I would really appreciate if anyone can explain why?
Thanks.


texts: Full backup at 1am, not started by my maintenace plan

texts:
I set up a complete set of Maintenance Plan.

Full backup of all databases at 3AM.
Differential backup at 6h30, 12h30, 18h30 and 00h30.
Log backup at each hour, at 45 minutes (1h45, 2h45...)

In my SQL history list, (I use this script to find it: http://luvit.me/1GAfpPG ) I see a full backup a 1AM, with a GUID as the "physical_device_name" and a NULL value at as the "backupset.name".

WHO start the backup? How can I found if another guy use another tool for backup? Did this backup is started by SQL Server himself?

The problems is currently, we have some tools that do a database conversion for a new and not yet ready ERP, and the conversion tool change the Recovery mode: if the recovery mode is changed while this weird backup is running, the conversion tool crash...

Sorry if it's a noob question, but I didn't find anything ...

Thank you


texts: What happens if you accidentally insert data into a replication target table?

texts: We use transactional replication to replicate data from table1 on server1 to table2 on server2. However, one of my colleagues accidentally inserted some data into table2. In order to keep the two tables synced up, should we delete the rows inserted? If so, do we need to reset identity?


texts:
As a newbie, I expected Mysql to return the rows of this query ordered by their ids order in the id clause

select * from test where id in (3,1,2)

Unfortunatelly for me, that's not true. The results comes in id asc. order.

I have read that the easiest way to get what I want is using order by field

select * from test where id in (3,1,2) order by field(id, 3,1,2)

But I also read that is not ver efficient for queries with lots of ids in the sorting.

So, Do you think that this query below would performance better?

select * from test where id=3 union select * from test where id=1 union select * from test where id=2


texts: Select all data from IBM DB2 member table

texts:
Old as400 system has this thing called 'Members'. Basically you can have multiple members on a table and then work with only selected one.
More Info

When you select from a table that has members you have a few options. Just doing a select will retrieve data from the first member. Or call a stored procedure to set the member for the table and retrieve it. Or create an alias for the table with members to access it. But there is no way to just get a

Is there a way to select ALL data from the table from ALL the members?