display numbers with zero pad I have some data stored as an unsigned tinyint.  When displaying it I want it to be zero padded only if it is 1 digit in length.  I have issues with LPAD(CAST(NUMBER AS CHAR CHARSET UTF8),2,'0') when I have three digit long numbers.  How do I get what I'm looking for?

Example of data in table:

NUMBER
1
2
3
...
10
11
...
101
102

What I currently get when running with LPAD

LPAD_NUMBER
01
02
03
...
10
11
...
10
10

What I want

UNKNOWN
01
02
03
...
10
11
...
101
102

SOLUTION I USED

select case
    when number between 0  and 9 then
        LPAD(CAST(number AS CHAR CHARSET UTF8), 2, '0')
    else
        CAST(number AS CHAR CHARSET UTF8)
    end as newNum;

This gets me what I'm looking for and is based off of the accepted answer.

You can use a case expression:

select case when number between 0 and 9 then '0' else '' end
    || number as n
from t;

Note that || is the sql-standard operator for string concatenation. Unless properly configured MySQL will treat || as logical OR. This behavior is determined by the setting PIPES_AS_CONCAT. You can also enable this and other sane settings with ANSI. You can see what settings you have with:

select @@sql_mode;

It can be changed using:

set @@sql_mode=@@sql_mode || ',PIPES_AS_CONCAT';

or by modifying the config file

However this is purely presentation so I think you should do the formatting of the result in the application instead.


Best practices for integer key types - mixed or all the same integer type? I am designing a postgres database for scientific data. It has about 30-40 tables, many of which are lookup tables with less than 10 records. The bulk of the data are concentrated in just a few tables with ~ 1,000,000 records that reference the lookup tables with foreign keys. There will be web user interface that accesses the data via a GraphQL API.
I always use the same INT data type for all primary and foreign key types. Is there merit in optimizing the primary keys on the lookup tables as SMALLINT to conserve space on the big tables (and perhaps a modest overall performance)? Or is it best to just keep all key columns the same data type to reduce complexity and confusion for humans and software accessing the data?
(The lookup data could change over time so I can't use ENUM.)

It's a little subjective, and there's certainly nothing wrong with reduced complexity by defaulting to INT (since it covers most use cases and there's almost no tangible difference in performance) but generally if your tables are going to be so small in record count, using SMALLINT is a good idea from a best practice standpoint. SMALLINT gets you +-32767 rows (for a total of 65,534). So it is definitely sufficient for your small tables.
When deciding on the size for a datatype, generally planning for anywhere between twice as much up to a magnitude more of the max amount values you think is possible is a decent rule of thumb, depending on how confident you are in that rough estimation.
At the end of the day, if 25% of your tables have 1,000,000 rows in them, switching one of the smaller tables to SMALLINT instead of INT will only save you about 20 MB of space overall (which is peanuts). If you did it for all your small tables, then at most you're saving about half a gigabyte of space. There's probably better optimizations to go after currently and you can let sleeping dogs lie. Moving forward if you want to take SMALLINT into better consideration on new tables, it's not a bad idea.


Problems Linking the max value of an attribute in a table to its primary Key I need to extract the order_id with the max number of items, I've figured out how to extract the max value but  I'm having problems linking it to the particular order_id.

PS: I'm a uni student so please go easy on me.

select MAX(f), x.order_id
from ( select count( distinct z.item_id) f, z.order_id as g
       from  Order_item z, Item i, Ordert o
       where o.order_id=z.order_id
       group by z.order_id
      ) AS T, Ordert x
having x.order_id=g;

Maybe you need simple

select count( distinct z.item_id) f, z.order_id as g
from  Order_item z, /* Item i, */ Ordert o
where o.order_id=z.order_id
group by z.order_id
order by f desc limit 1

?


Recreate all foreign keys in all tables as deferrable (batch) I want to make all foreign keys in my database deferrable. But there is no possibility to alter an existing constraint. So I need to drop and add again each foreign key. How to do it automatically?

You can do this by generating the necessary script.

select 'alter table '||quote_ident(ns.nspname)||'.'||quote_ident(tb.relname)||
       ' drop constraint '||quote_ident(conname)||';'||chr(10)||
       'alter table '||quote_ident(ns.nspname)||'.'||quote_ident(tb.relname)||
       ' add constraint '||quote_ident(conname)||' '||
       pg_get_constraintdef(c.oid, true)||' deferrable initially immediate;' as ddl
from pg_constraint c
  join pg_class tb on tb.oid = c.conrelid
  join pg_namespace ns on ns.oid = tb.relnamespace
where ns.nspname in ('public')   --<<< adjust the schema name(s) here
 and c.contype = 'f';

Spool the output of the above statement into a file and then run that generated script.


Why cannot connect remote report server instance? I have a SQL Server reporting service on ServerR.

I can easily use my local SSMS to connect ServerR's database engine and Reporting Service.

When I try to use my local Reporting Service Configuration Manager, even though I input Server Name as ServerR and click "Find", it shows "No report servers were found."

Why?

If you're using a different version of Reporting Service Configuration Manager, then it wont pick it up.

SSRS Config Manager 2016 doesn't see my local SSRS 2012 instance and vice-versa.
Hope that helps.


Simple MySQL table copy from one server to another? this question has kindof been answered in a number of ways. Nothing that quite fits the bill (as far as I understand it).

I have MySQL Table X on Server A. I would like an exact copy of this table, to be cloned on 3 other servers. Table X is quite big, and will get bigger. Currently 500,000 records.

Nothing gets deleted from that table, only added. So, don't really want to copy the whole big table across every time. Some kind of incremental solution, to pull across only the new record data on Table X, to Servers B, C and D. Is that possible?

I am running PHP on both servers. If it makes a difference - Server A is PHP4, and Servers B,C and D are PHP5.

If it were being copied over to only 1 other server - I could somehow mark records on Server A, as "already copied" - and only copy across records not flagged as such. I guess, I could add fields on Table X, to say "copied to Server B", "to Server C" etc. But, considering all this extra data will need to be copied to all other Servers too - it seems like a lot of dead data hanging around. Hopefully there is a better way.

I do not have root access or command line access. Some suggestions on Stackoverflow had solutions involving these.

Our old programmer, set up something across these servers before - where any change on Server A mysql, would immediately be mirrored on mysql on Server B. But, that was for the whole database, not 1 single table. And, he had access to command line etc. If there is a good solution there, I could speak to the server hosts and ask them to help me set it up?

Apologies if the question seems a bit dumb, I've never done this before (obviously). :) Any pointers or guidance you can offer will be appreciated.

Many thanks.

It sounds like your old programmer had a master -> slave setup which is a really good way to back up your whole database; any command run on the master is then replicated over to the slave. This seems to fit what you need, not sure if you can specify it for just one table. However there is a viable argument for keeping your whole DB backed up.


Using system tables to identify the columns of a table index in Sybase Is there a means of identifying the columns of table index using systematically populated system tables such as: syscolumns, sysindexes, sysobjects, etc.

I have tried to use syskeys unfortunately this table is only populated by sp_primarykey.

So far I have the following query:

SELECT
    i.name,
    o.name
FROM
    sysobjects o
    INNER JOIN sysindexes i on i.id = o.id
WHERE
    o.type = 'U'
    and o.name = 'XXXXX'
      and i.indid > 0
      and i.status & 2 = 2

I came up with the following solution - thanks to the comment by Phil:
SELECT
     distinct i.name as index_name, index_col(o.name, i.indid, c.colid) as name
FROM
    sysobjects o
    INNER JOIN sysindexes i on i.id = o.id
    INNER JOIN syscolumns c on c.id = o.id
WHERE
    o.type = 'U'
    and o.name = 'XXXXXX'
      and i.indid > 0
      and i.status & 2 = 2 -- Gives only system indexes. Skip it, and get all
      and index_col(o.name, i.indid, c.colid) <> null
    order by 1, 2


Access 2010 linked table - solve the "decimal field's precision" error I have an Access 2003 database (running on Win 7 64-bit, Office is also 64-bit) that has many linked tables pointing to an Oracle 11g instance through 64-bit Oracle ODBC driver. Most of the tables work fine, but some of them keep throwing at me this error:
"The decimal field's precision is too small to accept the numeric you attempted to add"

So far i have:

extensively surfed the web for a solution, but none apply in my case (my ODBC driver has no "enforce double" parameter, other suggestions i found were simply irrelevant)
checked that Access sets the correct precision and scale for numeric fields (for example, NUMBER(20,10) in oracle maps to Number data type, Decimal field size with precision 20 and scale 10 in Access). This is even true for the fields that generate errors.
concluded that if a record has at least 1 problematic field, all other fields in that record also display "#Error" in Datasheet view. The record is displayed normally if i set the problematic field to null.
figured that in my case the problem revolves about fields with WGS coordinates of type NUMBER(20,10) or NUMBER(8,6) (probably also other high scale numeric fields, not sure)
found out that if i round a problematic field in Oracle to the field's original scale, Access stops complaining. For example: in an Oracle view a NUMBER(20,10) field_name becomes round(field_name, 10), which makes Access happy.

I can't use the rounding workaround on tables and i don't have much freedom altering the Oracle db since it is a part of a very large and complex system.

I'm a newbie in regards of MS Access and have ran out of ideas on what else to try (except burning my computer and becoming a Jogi to curb my desperation).

Please, please help, kind database community!!

I had a similar problem, the fix was a linked table refresh.  Access was not aware that the precision had changed in my Oracle table and kept giving me the 'precision too small' error.


MySQL table concatenation - sort of! I have a db with the following:  Name, Team, Score, Points  I want to be able to do a query that returns Name, Score, Points for a particular team and another table that returns Points, Score, Name from another team...  Then I want to combine them as follows:  Name, Score, Points, Points, Name, Score
EXAMPLE

John, Boston, 127, 6
Mark, Boston, 120, 5
Paul, Newark, 110, 4
Carl, Boston, 105, 3
Matt, Newark, 101, 2

Would result in:
John | 127 | 6 | 4 | Paul | 110 |
Mark | 120 | 5 | 2 | Matt | 101 |
Carl | 105 | 3 |   |      |     |

OR (depending on which city is selected first)
Would result in:
Paul | 110 | 4 | 6 | John | 127 |
Matt | 101 | 2 | 5 | Mark | 120 |
     |     |   | 3 | Carl | 105 |

Thanks

YOUR SAMPLE DATA

DROP DATABASE IF EXIST johnc;
CREATE DATABASE johnc;
USE johnc
CREATE TABLE scores
(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    city VARCHAR(20) NOT NULL,
    score INT NOT NULL,
    points INT NOT NULL,
    PRIMARY KEY (id),
    KEY (city)
);
INSERT INTO scores (name,city,score,points) VALUES
('John', 'Boston', 127, 6),
('Mark', 'Boston', 120, 5),
('Paul', 'Newark', 110, 4),
('Carl', 'Boston', 105, 3),
('Matt', 'Newark', 101, 2);
select * from scores;

YOUR SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS johnc;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE johnc;
Query OK, 1 row affected (0.01 sec)

mysql> USE johnc
Database changed
mysql> CREATE TABLE scores
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     name VARCHAR(20) NOT NULL,
    ->     city VARCHAR(20) NOT NULL,
    ->     score INT NOT NULL,
    ->     points INT NOT NULL,
    ->     PRIMARY KEY (id),
    ->     KEY (city)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO scores (name,city,score,points) VALUES
    -> ('John', 'Boston', 127, 6),
    -> ('Mark', 'Boston', 120, 5),
    -> ('Paul', 'Newark', 110, 4),
    -> ('Carl', 'Boston', 105, 3),
    -> ('Matt', 'Newark', 101, 2);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

TABLE CONTENTS

mysql> select * from scores;
+----+------+--------+-------+--------+
| id | name | city   | score | points |
+----+------+--------+-------+--------+
|  1 | John | Boston |   127 |      6 |
|  2 | Mark | Boston |   120 |      5 |
|  3 | Paul | Newark |   110 |      4 |
|  4 | Carl | Boston |   105 |      3 |
|  5 | Matt | Newark |   101 |      2 |
+----+------+--------+-------+--------+
5 rows in set (0.00 sec)

mysql>

PROPOSED QUERY

SET @x1=0;
SET @x2=0;
SELECT CONCAT(A.name,' | ',A.score,' | ',A.points,' | ',
IFNULL(B.points,''),' | ',IFNULL(B.name,''),' | ',IFNULL(B.score,'')) Results
FROM
(SELECT *,(@x1:=@x1+1) k FROM scores WHERE city='Boston' ORDER BY id) A
LEFT JOIN
(SELECT *,(@x2:=@x2+1) k FROM scores WHERE city='Newark' ORDER BY id) B
USING (k);

PROPOSED QUERY EXECUTED

mysql> SET @x1=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @x2=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT(A.name,' | ',A.score,' | ',A.points,' | ',
    -> IFNULL(B.points,''),' | ',IFNULL(B.name,''),' | ',IFNULL(B.score,'')) Results
    -> FROM
    -> (SELECT *,(@x1:=@x1+1) k FROM scores WHERE city='Boston' ORDER BY id) A
    -> LEFT JOIN
    -> (SELECT *,(@x2:=@x2+1) k FROM scores WHERE city='Newark' ORDER BY id) B
    -> USING (k);
+---------------------------------+
| Results                         |
+---------------------------------+
| John | 127 | 6 | 4 | Paul | 110 |
| Mark | 120 | 5 | 2 | Matt | 101 |
| Carl | 105 | 3 |  |  |          |
+---------------------------------+
3 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

UPDATE 2016-06-23 18:08 EDT

PROPOSED QUERY #2

SET @x1=0;
SET @x2=0;
SELECT CONCAT(IFNULL(A.name,''),' | ',IFNULL(A.score,''),' | ',IFNULL(A.points,''),' | ',
IFNULL(B.points,''),' | ',IFNULL(B.name,''),' | ',IFNULL(B.score,'')) Results
FROM (SELECT k FROM (SELECT MAX(x) maxnum FROM
(SELECT COUNT(1) x FROM scores WHERE city='Boston' UNION
SELECT COUNT(1) FROM scores WHERE city='Newark') AAA) AA JOIN
(SELECT h*100+t*10+u+1 k FROM
(SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) h,
(SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t,
(SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) u ORDER BY k) BB
WHERE k <= maxnum) k
LEFT JOIN (SELECT *,(@x1:=@x1+1) k FROM scores WHERE city='Boston' ORDER BY id) A USING (k)
LEFT JOIN (SELECT *,(@x2:=@x2+1) k FROM scores WHERE city='Newark' ORDER BY id) B USING (k);

EXECUTED WITH Boston/Newark

mysql> SET @x1=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @x2=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT(IFNULL(A.name,''),' | ',IFNULL(A.score,''),' | ',IFNULL(A.points,''),' | ',
    -> IFNULL(B.points,''),' | ',IFNULL(B.name,''),' | ',IFNULL(B.score,'')) Results
    -> FROM (SELECT k FROM (SELECT MAX(x) maxnum FROM
    -> (SELECT COUNT(1) x FROM scores WHERE city='Boston' UNION
    -> SELECT COUNT(1) FROM scores WHERE city='Newark') AAA) AA JOIN
    -> (SELECT h*100+t*10+u+1 k FROM
    -> (SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) h,
    -> (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t,
    -> (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) u ORDER BY k) BB
    -> WHERE k <= maxnum) k
    -> LEFT JOIN (SELECT *,(@x1:=@x1+1) k FROM scores WHERE city='Boston' ORDER BY id) A USING (k)
    -> LEFT JOIN (SELECT *,(@x2:=@x2+1) k FROM scores WHERE city='Newark' ORDER BY id) B USING (k);
+---------------------------------+
| Results                         |
+---------------------------------+
| John | 127 | 6 | 4 | Paul | 110 |
| Mark | 120 | 5 | 2 | Matt | 101 |
| Carl | 105 | 3 |  |  |          |
+---------------------------------+
3 rows in set (0.01 sec)

mysql>

EXECUTED WITH Newark/Boston

mysql> SET @x1=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @x2=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT(IFNULL(A.name,''),' | ',IFNULL(A.score,''),' | ',IFNULL(A.points,''),' | ',
    -> IFNULL(B.points,''),' | ',IFNULL(B.name,''),' | ',IFNULL(B.score,'')) Results
    -> FROM (SELECT k FROM (SELECT MAX(x) maxnum FROM
    -> (SELECT COUNT(1) x FROM scores WHERE city='Newark' UNION
    -> SELECT COUNT(1) FROM scores WHERE city='Boston') AAA) AA JOIN
    -> (SELECT h*100+t*10+u+1 k FROM
    -> (SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) h,
    -> (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t,
    -> (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) u ORDER BY k) BB
    -> WHERE k <= maxnum) k
    -> LEFT JOIN (SELECT *,(@x1:=@x1+1) k FROM scores WHERE city='Newark' ORDER BY id) A USING (k)
    -> LEFT JOIN (SELECT *,(@x2:=@x2+1) k FROM scores WHERE city='Boston' ORDER BY id) B USING (k);
+---------------------------------+
| Results                         |
+---------------------------------+
| Paul | 110 | 4 | 6 | John | 127 |
| Matt | 101 | 2 | 5 | Mark | 120 |
|  |  |  | 3 | Carl | 105         |
+---------------------------------+
3 rows in set (0.00 sec)

mysql>

SECRET SAUCE OF QUERY #2

The subquery k generated a numbered sequence 1..N where N is the maximum number distinct city entries. Since Boston appear three times and Newark only 2 times, subquery k yields the sequence 1..3.

I then LEFT JOIN that sequence to both subquery A (Boston) and subquery B (Newark). As shown, reversing the city names reverses the output.


Is there anyway to know when a sql server instance was installed? I have 100+ sql server instances (from sql 2005 to sql 2016) installed in my environment, is there a good way to find when an instance was initially installed?

I tried to check the [master] database creation date, and found (for sql 2012 onward at least) it is always Apr 8, 2003.

The only possible date is [msdb] creation date, which I see it changing for different sql server instances. (I am not sure about this is because I am not sure whether a service patch will change the [msdb] creation date?)

Is there any other reliable way that can indicate the sql server instance creation date? I am open to use of t-sql or powershell to figure it out.

Thanks in advance.

This query:

SELECT create_date
FROM sys.server_principals
WHERE sid = 0x010100000000000512000000


Difference between Intel and AMD chips? I would like to know if there is a big dig difference between Intel and AMD chips in terms of how sql behaves?  I've been told that their is a big difference between the chips depending on application.  Is there any way I can test or someone can advise me on how I can go about troubleshooting this?

Thanks

Generally speaking, recent Intel processors perform better on most SQL Server workloads compared to recent AMD processors. Intel processors have better single-threaded performance and lower physical core counts (so the licensing costs are lower for SQL Server 2012). Even with the SQL Server Core Factor Table discount for AMD, it is pretty hard to make a case for using AMD processors for SQL Server 2012.


How to make a unique column with default value of UUID I have a key column for internal use which is just a increasing integer, but I would like to have a second unique column which is a UUID, but I don't know how to have a function called for the default value (so that SQL Server is creating the UUID and not java), is there any documentation that one could suggest to me for this?

CREATE TABLE TblUID
(
    ID INT NOT NULL,
    UID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID()
)


Where can I download an old version of PostgreSQL for Windows? I need version 8.4 for a legacy system, but EnterpriseDB (the official link) only shows 9.x

On the PostgreSQL FTP Mirror I see the 8.4.22 binary, but only have binary for solaris:

For good reason PostgreSQL 8.4 ceased being supported at July 2014. Moreover, PostgreSQL doesn't host compiled versions for Windows, and Enterprise DB likely doesn't want to incur the liability of hosting packages known to be insecure. Even AS-IS it becomes a question of reputation.

Upgrade your software 8.4 was released on July 2009. It's older than Windows 7.

If you must have 8.4 feel free to download the source and compile or to seek out a consultant,

  PostgreSQL for Windows can be built using MinGW, a Unix-like build environment for Microsoft operating systems, or using Microsoft's Visual C++ compiler suite. The MinGW build variant uses the normal build system described in this chapter; the Visual C++ build works completely differently and is described in Chapter 16. It is a fully native build and uses no additional software like MinGW.

You can find the source for 8.4 on git, or in a zip

Archives

As others have pointed out,

  If you are adventurous: http://oldapps.com/de/postgresql.php or http://oldversion.com/windows/postgresql – a_horse_with_no_name 30 mins ago

However, whether or not you want to trust oldapps.com or oldversion.com is another question. I certainly wouldn't.

Linux

You could always use Linux to bring yourself up to a recent version and then dump it and load it in a version supported by Windows and hosted by EnterpriseDB.


Why can't I read Oracles CLOB columns via SQL-SERVER 2008 linked server? I want to access data in an Oracle 11g database from SQL-Server 2008

I set up a linked server and when I execute

select * from [Link_server_name]..Oracle_schema.Oracle_table

and Oracle_table contains Number and varchar2 columns, all works as excepted.

But when the Oracle_table contains a CLOB column, I get the following error:

Der OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' hat die Meldung 'Unspecified error' zurückgeben.

Der OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' hat die Meldung 'Oracle error occurred, but error message could not be retrieved from Oracle.' zurückgeben.

Der OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' hat die Meldung 'Data type is not supported.' zurückgeben.

Msg 7306, Level 16, State 2, Line 1

Die '"MCCAPP"."DOGGRUPPEN"'-Tabelle vom OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' kann nicht geöffnet werden.

Using OPENQUERY

SELECT * FROM OPENQUERY([L_V407SR8T], 'Select CLOB_COLUMN from Oracle_table'  )

I get

Der OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' hat die Meldung 'Oracle error occurred, but error message could not be retrieved from Oracle.' zurückgeben.
Der OLE DB-Anbieter 'MSDAORA' für den Verbindungsserver 'L_V407SR8T' hat die Meldung 'Data type is not supported.' zurückgeben.

Please excuse German error messages.

My question: Is there any way to read CLOB columns via linked Servers?

EDIT:

SQL Server Import and Export Wizard
seems to depend on OLE DB too and
sucks on the same tables
I'm asking myself, if the problem
depends on the character sets uses,
but I can't change them
As practical work around, I use some
PowerShell scripts to get at the
data, but

Download and install an Oracle 11 database client on your SQL Server 2008 machine.

Set up a linked server using an Oracle OLE DB provider (OraOLEDB.Oracle).

Make sure "Allow InProcess" is enabled in Provider options.

The Oracle 11 OLE DB client supports CLOBs.


Circular referencing required for class? I'm taking a class called Intro To Database Systems.  I'm given a diagram and I need to create tables for it in SQL server.  However, the diagram appears to have circular referencing.  Am I correct in thinking that?  If so, how do I implement it?  Here is the diagram.

As you can see, DNO in EMPLOYEE references DNUMBER in DEPARTMENT, and MGRSSN in DEPARTMENT references SSN in EMPLOYEE.

Here is my code so far, which doesn't work for obvious reasons.

CREATE TABLE EMPLOYEE (
FNAME   Char(20)    NOT NULL,
MiNIT   Char(1)     NULL,
LNAME   Char(20)    NOT NULL,
SSN     Integer     NOT NULL,
BDATE   Date        NOT NULL,
E_ADDRESS   Char(35)NOT NULL,
SEX     Char(1)     NOT NULL,
SALARY  Money       NOT NULL,
SUPERSSN    Integer NULL,
DNO     Integer     NOT NULL,
CONSTRAINT EMPLOYEE_PK PRIMARY KEY (SSN),
CONSTRAINT DNO_Relationship FOREIGN KEY (DNO)
REFERENCES DEPARTMENT(DNUMBER),
CONSTRAINT SUPERSSN_Relationship FOREIGN KEY (SUPERSSN)
REFERENCES EMPLOYEE(SSN)
);

CREATE TABLE DEPARTMENT (
DNAME   Char(20)    NOT NULL,
DNUMBER Integer     NOT NULL,
MGR_SSN Integer     NOT NULL,
MGR_START_DATE  Date    NOT NULL,
CONSTRAINT DEPARTMENT_PK PRIMARY KEY (DNUMBER),
CONSTRAINT MGR_SSN_Relationship FOREIGN KEY(MGR_SSN)
REFERENCES EMPLOYEE(SSN)
);

I assume the error you're getting is due to trying to reference the DEPARTMENT table before it exists?

If so, then the solution is simple.

Create the EMPLOYEE table without the constraint that references the DEPARTMENT table;
Create the DEPARTMENT table as above;
Alter the EMPLOYEE table to add the constraint against the DEPARTMENT table.

Review the documentation on ALTER TABLE for how to use that command.

Given that there's no major changes needed once you know what the command is, here's a SQLFiddle link that shows you can create your tables if you pull the constraint against the then non-existent DEPARTMENT table out of CREATE TABLE EMPLOYEE, and add that constraint in using ALTER TABLE once both tables exist. (Click the "Build Schema" button to see that it builds successfully).

As pointed out by @a1ex07, you will probably want to initially make DEPARTMENT.MGR_SSN and EMPLOYEE.DNO NULL instead of NOT NULL; in fact, at least one will need to be NULL.

The problem would be that, initially, you have no rows in either table. You would be unable to insert a row into EMPLOYEE because there would be no valid value you could provide for DNO, and you'd be unable to insert a row into DEPARTMENT because you could provide no valid value for MGR_SSN.

Keeping in mind our above solution, you could

make DEPARTMENT.MGR_SSN allow NULL values initially
create the departments you need (with no managers)
add your employees (EMPLOYEE.DNO can be NOT NULL, as all the initial departments exist)
update DEPARTMENT with the appropriate MGR_SSN values
use ALTER TABLE to make DEPARTMENT.MGR_SSN NOT NULL.

Note, however, that you if you ever have a new department headed by a new employee, you'll need to:

Create the new employee tied to an existing department;
Create the new department, with the new employee as MGR_SSN;
Update the employee with the correct DNO.


SSIS Design consideration with object variables I have an SSIS package for SQL Server 2008 R2. In the package I am required to work with an Access database (one to many) and iterate through it running validation checks with the data. The validation checks are simply running particular queries against the Access database and if records are returned, the validation failed for that check.

My requirements:

I am pulling the list of Access DBs to validate from a SQL Server
database.
I iterate through the list doing the validation checks (about 36 tot
The records returned will vary on column count and data type.
I need to take only those records returned and generate a single
email.
A title or heading needs to accompany each recordset in the email in
order to associate the records with the validation check

EDIT: One additional requirement that might affect design: this package could will be called at minimum every 15 minutes.

Example:

Validation 1 errors
Column1 Column2
cv1     cv1
cv2     cv2

What I have setup so far:

Data Flow pulls the list of Access files to validate into an object
variable
Foreach loop iterates through each record (using ADO Enumerator), mapping each column to a variable Script task to validate the file is accessible (failures > Execute SQL Task updates database to skip the file)
Data Flow is setup for each validation in order to query the Access file(s)
Each Data flow is a OLE DB Source to a Records Destination I have each validation populating a object variable

I am populating an object variable for each validation. Since the column count can vary I did not see an option to send it to anything else; at least with my knowledge of SSIS.

What I have setup seemed good when I started but... I am now sitting here thinking the best way to check all these object variables for records and put in an email. I came across Andy Leonard's blog post on access the DataSet in an object variable. Then another blog post on retrieving data from the Recordset Destination.

Anyone have suggestion(s) on doing this differently or how to build that email with all these object variables?

My first thought would be to create a second foreach loop to iteration through each object variable. Grabbing each recordset and appending a string variable to contain each table, formatted as HTML or something to make it look pretty in the email.

I would replace your output Recordset Destinations with OLE DB Destinations and deliver the required data into database tables.  As well as making the remaining work easier, this could also give you a far more useful Audit/History of previous runs.

From that point I would then write SSRS reports against those tables and use SSRS subscriptions to prepare well formatted emails and deliver them.


How to generate and insert primary key when using SELECT DISTINCT in OracleDB? How do I insert (or generate and insert) a primary key when I use the SELECT DISTINCT query?

If I have a table made like this:

CREATE TABLE testUser(
    username VARCHAR2(50 CHAR),
    email VARCHAR2(50 CHAR)
)

And I had some table customer_orders with this data I could quickly populate this using:

INSERT INTO testUser(username, email)
SELECT DISTINCT user_name, user_email
FROM customer_info;

However if instead I have this table:

CREATE TABLE users(
   id NUMBER,
   user_name VARCHAR2(50 CHAR),
   user_email VARCHAR2(50 CHAR)
)

and try to use the same method (the SELECT DISTINCT INSERT method) I'll get an error like ORA-01400: cannot insert NULL into ("MYDB"."USERS"."ID").

This makes sense since the primary key is null, how can I autogenerate a primary key 1-n where n is the COUNT(SELECT DISTINCT user_name)?

Let the database generate it for you (identity column, 12c feature):

CREATE TABLE users(
   id NUMBER generated always as identity, -- <= HERE
   user_name VARCHAR2(50 CHAR),
   user_email VARCHAR2(50 CHAR)
);

If you are still on a version below 12c, then you can simply use rownum:

INSERT INTO testUser(id, username, email)
SELECT rownum, sq.* from
(select DISTINCT user_name, user_email
FROM customer_info) sq;

Or a sequence:

create sequence s1;
INSERT INTO testUser(id, username, email)
SELECT s1.nextval, sq.* from
(select DISTINCT user_name, user_email
FROM customer_info) sq;

Or a trigger, as described here: https://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle


What database supports over a thousand fields and tables I have a requirement to store several csv files with several fields in an database. This data does not need to be searchable but i need to query the data by Id. I will also need SQL to query the data so this may rule out many nosql options. Considering, each csv file is huge I will need to put each csv file in a separate table. i do not need to perform joins, subqueries or anything complicated - I just need to query by Id. Since the database will be getting hit hard with thousands of requests per hour horizontal scaling would be nice. Is there a database that can handle these requirements?

I have checked out MongoDB but it does not support sql. MongoDB would have been perfect if it supported basic sql.

My recommendation would be for you to check out Cassandra.  In my experience, and based on the requirements you seem to have, it seems like it would be a good fit.

It appears to me that you have essentially have these requirements:

(1) Key-value storage (look up data based on ID)

(2) No need for JOIN or sub-SELECT

(3) Need for a SQL-like language

(4) Horizontal scalability

As for (1), Cassandra will hash the primary key (in your case you can use a single column id as your primary key) and which node in the cluster stores the data, and the Cassandra internals ensure look up by primary key is very fast.  Cassandra doesn't support JOIN as in (2), but you appear to be OK with that.

Since you are essentially doing key-value lookups, I'm actually not sure why you need an SQL language.  Perhaps you are wanting to not only look up data by primary key, but also perform aggregations and use GROUP BY?  In this case, Cassandra won't work, but if you only need a simple SQL language to retrieve data as per (3), Cassandra has CQL, which may serve your needs.

Horizontal scalability is built right in to Cassandra, covering (4).

All that being said, Cassandra isn't a silver bullet.  It's a great technology for certain applications, and it sounds like it fits you needs.  Still, if you want aggregations, or if you want to query for multiple primary keys in a single query (e.g. SELECT * FROM table WHERE id IN (...)), then you may run in to some issues.

Now, if you can let go of constraint (4), I'd 100% be promoting PostgreSQL.  You stated the database will be getting hit with thousands of requests per hour, but that's nothing for a database like PostgreSQL, if your queries are only individual primary key lookups, and you aren't trying to transfer large amounts of data in those queries.

Best of luck!  If you provide more info based on my feedback, perhaps the community here can help you narrow down your selection even more.


Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries I have a PostgreSQL 9.2 instance running on RHEL 6.3, 8-core machine with 16GB of RAM. The server is dedicated to this database. Given that the default postgresql.conf is quite conservative regarding memory settings, I thought it might be a good idea to allow Postgres to use more memory. To my surprise, following advice on wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server significantly slowed down practically every query I run but it's obviously more noticeable on the more complex queries.

I also tried running pgtune which gave the following recommendation with more parameters tuned, but that didn't change anything. It suggests shared_buffers of 1/4 of RAM size which seems to in line with advice elsewhere (and on PG wiki in particular).

default_statistics_target = 50
maintenance_work_mem = 960MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 11GB
work_mem = 96MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 3840MB
max_connections = 80

I tried reindexing the whole database after changing the settings (using reindex database), but that didn't help either. I played around with shared_buffers and work_mem. Gradually changing them from the very conservative default values (128k / 1MB) gradually decreased performance.

I ran EXPLAIN (ANALYZE,BUFFERS) on a few queries and the culprit seems to be that Hash Join is significantly slower. It's not clear to me why.

To give some specific example, I have the following query. It runs in ~2100ms on the default configuration and ~3300ms on the configuration with increased buffer sizes:

select count(*) from contest c
left outer join contestparticipant cp on c.id=cp.contestId
left outer join teammember tm on tm.contestparticipantid=cp.id
left outer join staffmember sm on cp.id=sm.contestparticipantid
left outer join person p on p.id=cp.personid
left outer join personinfo pi on pi.id=cp.personinfoid
where pi.lastname like '%b%' or pi.firstname like '%a%';

EXPLAIN (ANALYZE,BUFFERS) for the query above:

Default buffers: http://explain.depesz.com/s/xaHJ
Bigger buffers: http://explain.depesz.com/s/Plk

The question is why am I observing decreased performance when I increase buffer sizes? The machine is definitely not running out of memory. Allocation if shared memory in OS is (shmmax and shmall) is set to very large values, that should not be a problem. I'm not getting any errors in the Postgres log either. I'm running autovacuum in the default configuration but I don't expect that has anything to do with it. All queries were run on the same machine few seconds apart, just with changed configuration (and restarted PG).

Edit:
I just found one particularly interesting fact: when I perform the same test on my mid-2010 iMac (OSX 10.7.5) also with Postgres 9.2.1 and 16GB RAM, I don't experience the slow down.
Specifically:

set work_mem='1MB';
select ...; // running time is ~1800 ms
set work_mem='96MB';
select ...' // running time is ~1500 ms

When I do exactly the same query (the one above) with exactly the same data on the server I get 2100 ms with work_mem=1MB and 3200 ms with 96 MB.

The Mac has SSD so it's understandably faster, but it exhibits a behavior I would expect.

See also the follow-up discussion on pgsql-performance.

First of all, keep in mind that work_mem is per operation and so it can get excessive pretty quickly.  In general if you are not having trouble with sorts being slow I would leave work_mem alone until you need it.

Looking at your query plans, one thing that strikes me is that the buffer hits are very different looking at the two plans, and that even the sequential scans are slower.  I suspect that the issue has to do with read-ahead caching and having less space for that.  What this means is you are biasing memory for re-use of indexes and against reading tables on disk.

My understanding is that PostgreSQL will look to the cache for a page before reading it from disk because it doesn't know really whether the OS cache will contain that page.  Because the pages are then staying in the cache and because that cache is slower than the OS cache, this changes the sorts of queries which are fast vs the sorts that are slow.  In fact reading the plans, aside from work_mem issues, it looks like all of your query info comes from the cache but it is a question of which cache.

work_mem: how much memory we can allocate for a sort or related join operation.  This is per operation, not per statement or per back-end, so a single complex query can use many times this amount of memory.  It isn't clear you are hitting this limit but it is worth noting and being aware of.  if you increase this too far, you lose memory that might be available for the read cache and the shared buffers.

shared_buffers: how much memory to allocate to the actual PostgreSQL page queue.  Now, ideally the interesting set of your database will stay in memory cached here and in the read buffers.  However, what this does is ensure that the most frequently used information across all backends gets cached and not flushed to disk.  On Linux this cache is significantly slower than the OS disk cache, but it offers guarantees that the OS disk cache dos not and is transparent to PostgreSQL.  This is pretty clearly where your problem is.

So what happens is that when we have a request, we check the shared buffers first since PostgreSQL has deep knowledge of this cache, and look for the pages.  If they are not there we ask the OS to open them from the file, and if the OS has cached the result it returns the cached copy (this is faster than the shared buffers, but Pg can't tell whether it is cached or on disk, and disk is much slower so PostgreSQL typically will not take that chance).  Keep in mind this affects random vs sequential page access as well.  So you may get better performance with lower shared_buffers settings.

My gut sense is that you probably get better, or at least more consistent, performance in high concurrency environments with larger shared_buffer settings.  Also keep in mind that PostgreSQL grabs this memory and holds it so if you have other things running on the system, the read buffers will hold files read by other processes.  It's a very large and complex topic.   Larger shared buffer settings provide better guarantees of performance but may deliver less performance in some cases.


Creating mongodb database with ansible playbook How i can create MongoDB database with Ansible? is it possible?

If not, can i do this with bash script?

I tried to use mongodb_user module for Ansible, but it cant work without existing table.

- name: Create MongoDB root user siteRootAdmin
  mongodb_user:
    login_port: "27017"
    database: "admin"
    name: "{{ mongodb_admin_user }}"
    password: "{{ mongodb_admin_password }}"
    roles: "root"

- name: Create MongoDB administrative user siteUserAdmin
  mongodb_user:
    login_user: "{{ mongodb_admin_user }}"
    login_password: "{{ mongodb_admin_password }}"
    login_port: "27017"
    database: "e-gov"
    name: "{{ mongodb_user }}"
    password: "{{ mongodb_password }}"
    roles:
      - { db: "admin", role: "readWrite" }
      - { db: "{{ mongodb_database }}", role: "readWrite" }

- name: Create MongoDB backup user siteUserBackup
  mongodb_user:
    login_user: "{{ mongodb_admin_user }}"
    login_password: "{{ mongodb_admin_password }}"
    login_port: "27017"
    database: "e-gov"
    name: "{{ mongodb_backup_user }}"
    password: "{{ mongodb_backup_password }}"
    roles:
      - { db: "admin", role: "backup" }

I did this, and it works with ansible 2.2.0^


In slow_log table sql_text column is BLOB We enabled slow_log to debug slow queries. But In slow_log table  sql_text column is BLOB.. It used to work fine before on 5.6 but now shows blob on 5.7

(Clarification)  When saving the slowlog to a TABLE, what happens to BLOBs and TEXT strings that cannot be safely stored in some standard column?  Apparently, TEXT values are stored into a BLOB.  In that case, how do I get back the TEXT in the desired CHARACTER SET?

In 5.7 ; column sql_text is changed to BLOB... Hence, you need to use
select CONVERT(sql_text USING utf8  ) from mysql.slow_log ;


Up to sync new members in replica set Let's say I have 2 nodes in my replica set with an arbiter running. Now when it comes to adding a new member to an existing replica set while it is still running for production, what steps do I need to do to make sure the data in new node will be in sync with other nodes? Especially when the new node is a fresh mongo instance without any data.

Do I just rs.add() new node to replica set and the data will be populated automatically? Or do I need mongodump latest data from current node then restore it to new node and do rs.add() to let it sync anything left behind?

I am thinking to use the same strategy when I need to upgrade the mongo machine ie. prepare 2 new machines with mongo instance, add them to replica set, let the data sync, remove mongo connection to the 2 old machines, remove 2 old machines from replica, shutdown old machines leaving me with 2 new upgraded machines. Would that be the best practice while running in production to avoid 0 downtime?

Answer to all you questions is YES.
You can just add (rs.add()) new nodes to RS and data will be synced. Faster way to do that is create those new nodes from backup/snapshot of old node and then rs.add().

What comes to upgrade, if you are just upgrading to new version of MongoD, there is no need to prepare new nodes and then add them to RS, but of course you can do that too. Proper procedure is just upgrade software at secondary node (one node at time) and then just "step down" primary and upgrade that node too.

What ever procedure you choose, there is no downtime.


How do I determine how much data is being written per day through insert, update and delete operations? The longevity of SSDs is largely determined by the amount of bytes written by insert, update and delete operations. What is the best way to accurately determine how much data is being written by MariaDB 5.5 on a daily basis so that I can use this to estimate the possible longevity of SSDs if used in a heavy write database environment?

Current setup is that all tables are InnoDB. Can I use Innodb_data_written and Uptime to determine a rough idea of bytes written per day, or is there a better way of doing it?

MONITORING

There are so many writes to keep track of

Data

Innodb_data_writes : The total number of data writes.
Innodb_data_written : The amount of data written so far, in bytes

SQL

Innodb_rows_deleted : The number of rows deleted from InnoDB tables
Innodb_rows_inserted : The number of rows inserted into InnoDB tables
Innodb_rows_updated : The number of rows updated in InnoDB tables

Double Write Buffer

Innodb_dblwr_writes : The number of doublewrite operations that have been performed
Innodb_dblwr_pages_written : The number of pages written to the doublewrite buffer

Operating System

Innodb_os_log_written : The number of bytes written to the InnoDB redo log files
Innodb_data_fsyncs : The number of fsync() operations so far. The frequency of fsync() calls is influenced by the setting of the innodb_flush_method configuration option.

MyISAM-related writes

Data writes come in the form of

External sources

DB Connections From Your Apps
Data Restorations

Reloading of mysqldumps
LOAD DATA INFILE

MySQL Replication via incoming I/O Threads (if DB Server is a Master)

Internal Sources

Read and Write I/O Threads for Writing to Transaction Logs
Insert Buffer from the InnoDB Buffer Pool
Double Write Buffer
MySQL Replication

IO Thread (if the DB Server is a Slave)
SQL Thread

Log Buffer Write
Individual Tablespace Files (.ibd)
FSyncs (pushing changes to disk)

InnoDB Architecture

SSD LIFESPAN

If you more concerned with the SSD's longevity, try moving some of the InnoDB parts away from the SSD over to a fast HDD. Which parts go where ?

HDD (RAID 10)

InnoDB System Tablespace (ibdata1)
InnoDB Transaction Logs (ib_logfile0,ib_logfile1)
Binary Logs
Relay Logs (if your server is a MySQL Replication Slave)

SSD

.frm files
.ibd files

See this good blog entry from a MySQL FaceBook Engineer on this suggestion

Here are some of my earlier posts on moving splitting logging mechanisms between SSD and HDD

Jun 25, 2013 : Postgres Write Performance on Intel S3700 SSD
Jun 18, 2013 : How big can innodb_doublewrite_file grow?

GIVE IT A TRY !!!


Delete missing database Event Viewer, I can fins this event logged :

  2016-07-10 11:45:01.83 Logon       Error: 18456, Severity: 14, State: 38.
  2016-07-10 11:45:01.83 Logon       Login failed for user 'Domain\Username'. Reason: Failed to open the explicitly specified
  database. [CLIENT: ]

So,I ran trace and found the database name,but how can I delete the missing database completely. Attached image.

I cannot find the database name in sp_helpdb or sys.databases.

  Cannot open database "DBTest" requested by the login. The login
  failed.

You don’t need to delete the already missing database.The user is configured to use a none existing or deleted database as a default database for the application. First, use trace and check for which application the user is failed to login, then on the application, change the default database to one of the existing databases. Sadequl’s post  might help you for step by step guide to fix the issue, https://www.mssqltips.com/sqlservertip/2581/sql-server-error-18456-finding-the-missing-databases/


SQL Server to Oracle Export - Data conversion error I have a rather weird problem with exporting a bunch of tables from SQL Server 2005 to Oracle database. I have SQL Agent job that deletes Oracle tables, recreates them and then exports the data using linked server and OPENQUERY. The entire script is here.

My problem is, the job fails while exporting data at the last table PSRPT_TEAM_ROLES_PSFT. The error message from the log files says the job failed due to a data conversion error: Msg 8114, Sev 16, State 5, Line 4 : Error converting data type varchar to numeric. [SQLSTATE 42000]. I have identified the column that is likely causing this error, BO_ID which is DECIMAL(31,0) at source and exported as NUMERIC(31,0) to destination. When I alter the destination column to NVARCHAR2 and export, the job doesn't fail but the Oracle guys told me that it significantly increased the size of the table. The weird part is, when I run the export query manually, even when the datatype NUMERIC(31,0) at destination I see no error. So I guess my question is

Am I missing something obvious?
Why does this table export fail when run as a SQL agent job and work perfectly when run manually?

I tried your script and recreated your environment completely and i didn't see any error.

Ran perfectly and created all tables and transferred all data successfully

i tried running under a sql agent job and still no error was generated

but i guess you can update your insert script by making sure the column gets converted under all conditions

[BO_ID] = CASE WHEN isnumeric([BO_ID]) = 1 THEN [BO_ID] ELSE CAST([BO_ID] as DECIMAL(31,0)) END

or

[BO_ID] = CASE WHEN isnumeric([BO_ID]) = 1 THEN [BO_ID] ELSE Null END

change your script to

INSERT INTO OPENQUERY (
    ZMMS_ORA
    'SELECT
        "COMPANYID",
        "BO_ID",
        "AS CM",
        "AS RSM",
        "AS SE",
        "TS CM",
        "TS RAE",
        "TS RSM",
        "TS SE",
        "BBONE AM",
        "ASM",
        "WCT ASM",
        "LS TSM",
        "WCT TSM",
        "TS TSM",
        "AS PM",
        "AS DEV",
        "TS PM",
        "TS DEV",
        "TS TECH",
        "BB EXEC",
        "CHM",
        "ANGEL Client Manager (CM)",
        "ANGEL Engagement Team",
        "BbOne Client Manager (CM)",
        "Blackboard Executive",
        "CE/Vista Hosting Mgr (ASM)",
        "CE/Vista Tech Support (TSM)",
        "Complex Hosting Manager (CHM)",
        "Learn Client Manager (CM)",
        "Learn Hosting Mgr (ASM)",
        "Learn Reg Sales Mgr (RSM)",
        "Learn Sales Engineer (SE)",
        "Learn Services Developer",
        "Learn Services ICM Mgr",
        "Learn Services Project Manager",
        "Learn Tech Support (TSM)",
        "Transact Client Manager (CM)",
        "Transact Project Manager",
        "Transact Reg Acct Exec (RAE)",
        "Transact Reg Sales Mgr (RSM)",
        "Transact Sales Engineer (SE)",
        "Transact Services Technician",
        "Transact Tech Support (TSM)",
        "Xythos Tech Support (TSM)"  FROM ZMMSUSER.PSRPT_TEAM_ROLES_PSFT'
    )
SELECT "COMPANYID"
    ,[BO_ID] = CASE WHEN isnumeric([BO_ID]) = 1 THEN [BO_ID] ELSE CAST([BO_ID] as DECIMAL(31,0)) END
    ,"AS CM"
    ,"AS RSM"
    ,"AS SE"
    ,"TS CM"
    ,"TS RAE"
    ,"TS RSM"
    ,"TS SE"
    ,"BBONE AM"
    ,"ASM"
    ,"WCT ASM"
    ,"LS TSM"
    ,"WCT TSM"
    ,"TS TSM"
    ,"AS PM"
    ,"AS DEV"
    ,"TS PM"
    ,"TS DEV"
    ,"TS TECH"
    ,"BB EXEC"
    ,"CHM"
    ,"ANGEL Client Manager (CM)"
    ,"ANGEL Engagement Team"
    ,"BbOne Client Manager (CM)"
    ,"Blackboard Executive"
    ,"CE/Vista Hosting Mgr (ASM)"
    ,"CE/Vista Tech Support (TSM)"
    ,"Complex Hosting Manager (CHM)"
    ,"Learn Client Manager (CM)"
    ,"Learn Hosting Mgr (ASM)"
    ,"Learn Reg Sales Mgr (RSM)"
    ,"Learn Sales Engineer (SE)"
    ,"Learn Services Developer"
    ,"Learn Services ICM Mgr"
    ,"Learn Services Project Manager"
    ,"Learn Tech Support (TSM)"
    ,"Transact Client Manager (CM)"
    ,"Transact Project Manager"
    ,"Transact Reg Acct Exec (RAE)"
    ,"Transact Reg Sales Mgr (RSM)"
    ,"Transact Sales Engineer (SE)"
    ,"Transact Services Technician"
    ,"Transact Tech Support (TSM)"
    ,"Xythos Tech Support (TSM)"
FROM CRMRPT..B_RPT_B_TEAM_ROLE;


TDE enabled database backup taking too long We have recently implemented TDE and backup compression is also enabled. Before TDE backup used to take about 20min but now it takes about one hour.

I have set @MaxTransferSize to 3MB, 2MB, 1MB and 0.5MB but didn't make any difference time wise, database is 90GB of size and it is SQL Server 2016 SP1 CU4.

We using Ola's Maintenance Solution/Scripts to backup the database. Any idea why it is taking 3x longer when TDE enabled please?

I managed to reduced the time to 28min now, changed @MaxTransferSize= 524288 to 262144 and also removed @Verify='Y' parameter which did the trick.

I believe backup @Verify option was the real issue here, as soon as I removed the option backup time cut to half.


Using OPENQUERY to execute a script I have a SQL Server 2008 instance which I want to use to import data from an Oracle server.

I have set up a linked server that works correctly when running simple queries like SELECT * FROM table. However, if I declare a variable or loop through the rows in a table or anything else inside the OPENQUERY, I get errors.

Is it possible to do this using OPENQUERY? Do I need additional permissions?

You are probably seeing an error like:

Msg 7357, Level 16, State 1, Line xx
Cannot process the object "<query text>".
The OLE DB provider "<provider>" for linked server "<server>" indicates that either
the object has no columns or the current user does not have permissions on that object.

This occurs when SQL Server tries to discover the shape of the data returned by the OPENQUERY statement. There are many fascinating details here, but the upshot is that OPENQUERY is often best used with only a single SELECT statement, as all the examples in Books Online do.

It is possible to do other things with OPENQUERY, including executing a remote stored procedure, but the procedure should only return a single result set, and there should be nothing before that SELECT that might break the way SQL Server checks the result set shape.

An alternative is to use EXECUTE ... AT, for example:

DECLARE @Script nvarchar(max) =
    N'
    <some amazing script>
    ';

EXECUTE (@Script) AT <linked_server_name>

See Books Online for the full syntax and options.

You will probably need to set the linked server's Server Options property Enable promotion of distributed transactions for RPC to false to capture results in a table using:

INSERT <table> (columns)
EXECUTE (@Script) AT <linked server>;

Note this is potentially much less efficient because the results of the EXECUTE are spooled to tempdb before being fed to the INSERT. This can be problematic if the results are large.


How is ORDER BY logically Processed Take this example:

SELECT SalesOrderID,
       CustomerID,
       SalesPersonID,
       OrderDate
FROM Sales.SalesOrderHeader
ORDER BY OrderDate,
         SalesOrderID DESC
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

I understand ORDER BY when one column is specified but how does the ORDER BY clause order the table by two or three columns?  Which one is ordered first?  What if the highest value in the SalesOrderID column does not correlate with the highest value in the OrderDate column?

Think of the way telephone directories are (typically) ordered.

If they were generated by an SQL query it would be

ORDER BY LastName ASC,
         FirstName ASC

The names are ordered by LastName first.

The secondary column is only used for ordering within groups of people sharing the same last name.


Can I create index on a table in a MySQL database while in use And if I can, any reason (performance/stability) why I shouldn't?

Note that if your table is using the InnoDB Plugin storage engine (which I highly recommend), if it's a secondary index (almost surely the case), you can still read from the table (i.e. it's non-blocking to SELECT statements).


Backing up / restoring a 20GB database with images stored as binary code I am trying to backup and restore a 20GB database from a SQL Server 2012 to another SQL Server 2014, but I have come across the following issues:

The developers [against best practice] have stored multiple images in fields within the database as binary code.

This therefore exceeds the 65532 character limit in some fields, so even though the images do show [based upon the data saved within this field], I cannot find the data in the field beyond this 65532 limit, within SQL Server.

How can I export / locate this data after the 65532 character limit?
When I have attempted to restore the database I am getting this error message:

  Restore of database 'zapkam' failed.
  (Microsoft.SqlServer.Management.RelationalEngineTasks)

  Additional information:

  System.Data.SqlClient.SqlError: RESTORE detected an error on page
  (1:1592996) in database "zapkam" as read from the backup set.
  (Microsoft.SqlServer.SmoExtended)

I have managed to restore two other smaller databases using the same technique, but am wondering if it's an issue with the database itself.
I have uploaded this database to the new server using FileZilla FTP Client, but it has cut out, painfully at 80% + 90% on a couple of occasions.

Is there a better solution for uploading these big files that I could possibly use please? For example, uploading table by table or similar...

Instead of using SQL Server SMO code to do a backup of the database, use the T-SQL BACKUP DATABASE command by connecting to the source SQL Server instance using either SQL Server Management Studio (free download) or sqlcmd.exe.  Use the optional WITH COMPRESSION parameter to ensure SQL Server compresses the backup, and use the INIT option to ensure the target backup file only contains a single backup.

Once you have the database backup completed, use 7Zip or WinRAR to compress the backup file even further.  Send the file to your host using a reliable FTP program, or preferably SecureFTP or SSH, ensuring you set the session to allow binary transfers prior to sending the file.  Do not even attempt to restore the database if the FTP transfer does not complete, since SQL Server will not be able to read the backup file.

Once you have the zip/rar file copied to your host, uncompress it using your compression utility of choice, then run RESTORE VERIFYONLY to ensure the target SQL Server can read the entire backup.  If that completes, run a RESTORE DATABASE command to actually restore the database onto the target SQL Server.


Can we create Fail over clustering for SQL Server 2012 standard edition on amazon EC2? We are planing to move on amazon's EC2 services. So my question is that can we create fail over clustering on amazon EC2 using SQL Server 2012 standard edition ?

Yes you can deploy that scenario to EC2 just as you would if it was VMWare environment. However with AWS I would highly suggest you follow the guidelines on how to properly set this up from Amazon.

  AWS provides a comprehensive set of services and tools for deploying Microsoft Windows workloads on its reliable and secure cloud infrastructure, such as Microsoft Windows Server Failover Clusters and SQL Server AlwaysOn Availability Groups. WSFC clusters and AlwaysOn Availability Groups, together with Active Directory Domain Services (AD DS) and Domain Name Server (DNS) functionality, provide the underpinnings for many enterprise-class Microsoft technology-based solutions including Microsoft SharePoint and .NET applications.

https://aws.amazon.com/windows/resources/whitepapers/alwayson/


MySQL Error 1114 "table is full" Using MyISAM Engine When executing the following query, we run into the error The table '/var/lib/mysql/temp/#sql_2d22_1' is full (actual table name varies). I've done a fair amount of research on this; most of the answers seem to hinge on there not being enough drive space or details related to the InnoDB engine. However, I've verified that we have (what seems like) enough free space and are using the MyISAM storage engine.

select a.* from some_table a,
   (select Account, Date, Amount, count(*) as Dupes from some_table
    GROUP BY Account, Date, Amount HAVING Dupes > 1) b
WHERE a.Account = b.Account and a.Date = b.Date and a.Amount = b.Amount;

Here are some details:

We're using MySQL version 5.5.36 on a CentOS 6 server with 6GB of RAM
The drive hosting MySQL's temp directory is 120GB in size, about 35 of which are in use
The table has about 70 million records and is about 3.2GB in size
big-tables is set to 1
max_heap_table_size is set to 4096M
tmp_table_size is also 4096M
myisam_data_pointer_size is 5

What could cause this error? I'd very much appreciate any suggestions, as I don't know that much about database configuration and dealing with large tables.

Update: I noticed yesterday that the temp directory does not reach capacity; as the query runs, the directory's size increases slowly by about 200MB and then I get the error.

A lot of helpfull information and hints can be found here:

https://dev.mysql.com/doc/refman/5.5/en/table-size-limit.html


MySQL block read time? My understanding is even if I need to read single record like select name from customer where id=1, I need to read complete specific block(block address can be found from index on column ID) in memory. Then MySQL goes through all records on that block to find that specific record. Is that correct ?

I believe default block size where MySQL write on disk is of 16KB(from google).
Also from diff sources on google I came to know on an average time taken to read 100MB of data from disk(HDD) is around 1 sec . It means an an average seek time to read the block should be around  16/(100* 1000) secs. correct ?

Seek time on a spinning disk is a few milliseconds -- a combination of the arm motion and the rotational delay waiting for the sector(s) to come under the read head.

arm motion (a few ms).
10K RPM (rotations per minute) = 667/sec --> 1.6 ms.  Consider half that for the average delay.
fetch the sectors.  One sector is 512 bytes; 32 sectors needed for an InnoDB 16KB block.  (Timing depends on how many sectors per track.)
Checksum and transfer to RAM
Poke around in the block to find the row(s) desired.
Work with the rows.

10ms is a simple, and often accurate enough, Rule of Thumb for the total time to read 1 block.

The 100MB/1s is probably predicated on optimal conditions of streaming consecutive sectors from each track, and hitting consecutive tracks -- thereby eliminating the first 2 steps in my list.  These are often the most costly two.

SSDs replace the first 2 steps by a much faster random fetch.

Yes, all row actions in MyISAM's InnoDB involve first having the desired 16KB block(s) sitting in the buffer_pool (in RAM).  Blocks will be fetched as needed, not pre-fetched, so don't expect the benefits of "streaming".  Also, most tables are not laid out on disk consecutively, so there will be frequent arm and/or rotational delays.  InnoDB is more optimized for efficiency when handling multiple threads than for speed of a single thread.


SQL statement to combine main table and child table as columns? Having a main table and a child table in a Microsoft SQL Server database (2005+), I want to show the user a single table in a grid view which includes rows from the main table and rows from the child table, with the latter one being shown as additional columns.

Please see the following example.

Main table:

ID | Name
---+--------
20 | Thomas
21 | Martin
22 | John
23 | Mike

Child table:

ID | MainID | Key | Value
---+--------+-----+-----------
1  | 20     | X   | Apple
2  | 20     | Y   | Microsoft
3  | 20     | Z   | Goole
4  | 21     | X   | Dell
5  | 22     | X   | Xing
6  | 22     | Y   | LinkedIn
7  | 22     | Z   | Cisco
8  | 22     | A   | Redhat
9  | 23     | Y   | Logitech
10 | 23     | Z   | Adobe

Now what I want to generate is an SQL statement that results in the following result set:

ID | Name   | X      | Y         | Z
---+--------+--------+-----------+--------
20 | Thomas | Apple  | Microsoft | Google
21 | Martin | Dell   | <null>    | <null>
22 | John   | Xing   | LinkedIn  | Cisco
23 | Mike   | <null> | Logitech  | Adobe

I.e. the child rows are transformed as additional columns.

My question is:

Is there a way to achieve this with standard SQL, i.e. without using the PIVOT statement or some XML statements? (because I want to use the query against a VistaDB database, too).

Update 1

Then number of detail keys is variable and not limited to 3 or to "X", "Y" and "Z" as names.

There are two ways that I see you can do this - one would be with a static query the other with dynamic SQL.

Static query (even though you stated you have an unknown number of columns - this is how you would do this).:

select m.id,
  min(case when [key] = 'X' then value else null end) as 'x',
  min(case when [key] = 'y' then value else null end) as 'y',
  min(case when [key] = 'z' then value else null end) as 'z'
from main m
left join child c
  on m.id = c.mainid
group by m.id

see SQL Fiddle with Demo

Dynamic SQL version, this will create the query:

DECLARE @query  AS NVARCHAR(MAX) = ' '
DECLARE @rowcount int
DECLARE @rowNumber int = 0
DECLARE @case as nvarchar(max) = ' '
DECLARE @keyValue as varchar(1)

select distinct [key]
into #temp
from child

-- get the number of unique keys
set @rowcount = (select count(*) from #temp)

while @rowNumber < @rowcount
  begin
    set @keyValue = (select top 1 [key] from #temp)

    set @case
      = @case + 'min(case when [key] = ''' + @keyValue
          + ''' then value else null end) as ''' + @keyValue + ''', '

    if @rowNumber < @rowcount
      begin
        set @rowNumber = @rowNumber + 1

        delete from #temp where [key] = @keyValue
      end
  end

set @query
  = 'select m.id, '+ substring(@case, 1, len(@case) -1) + '
     from main m
     left join child c
       on m.id = c.mainid
     group by m.id'

exec(@query)

see SQL Fiddle with Demo

The static version is portable but will not work with your dynamic requirement.


SQL Server: Is it possible to create a calculated index In SQL Server, is it possible to create an index on a calculation?

The following does not work for me:

CREATE INDEX customermonth ON customers(month(dob));

Some databases allow me to do this. Is there a trick to doing this in SQL Server?

Yes it is possible to do this, but not with the syntax you used. The key is that you need to create a computed column in the table and then index that:

ALTER TABLE customers ADD MonthOfBirth AS MONTH(dob) PERSISTED;
CREATE INDEX customermonth ON customers (MonthOfBirth);

Computed column is SQL Server's name for what other products call a generated column, and in place of STORED (cf. VIRTUAL) we use PERSISTED.

See How to create indexes on computed columns in SQL Server by Atif Shehzad.

Naturally, this requires that you have permission to alter the schema of the table, and you'll need to ensure that there won't be any nasty side effects of adding a column (like breaking reports that use SELECT * FROM customers;)

Computed columns do not always have to be PERSISTED before you create an index on them. See the documentation on the topic.


"user does not exist " when calling DBMS_LOGMNR.START_LOGMNR I have a CDB user called "MYADMIN" and I'm trying to make it connect to log miner.
-- enable calling admin username on CDB
ALTER SESSION set "_ORACLE_SCRIPT"=true
/
-- create unique table space for admin
CREATE TABLESPACE myadmints DATAFILE '/path/to/admints.dbf' SIZE 20M AUTOEXTEND ON
/
-- create admin user on CDB
CREATE USER myadmin IDENTIFIED BY P@ssw0rd DEFAULT TABLESPACE myadmints QUOTA UNLIMITED ON myadmints ACCOUNT UNLOCK
/
-- allow access to all PDBs to the admin user
ALTER USER myadmin SET CONTAINER_DATA=ALL CONTAINER=CURRENT
/
-- grant needed permissions
GRANT DBA to myadmin
GRANT CREATE SESSION TO myadmin
GRANT CREATE TABLE TO myadmin
GRANT EXECUTE_CATALOG_ROLE TO myadmin
GRANT EXECUTE ON DBMS_LOGMNR TO myadmin
GRANT SELECT ON V_$DATABASE TO myadmin
GRANT SELECT ON V_$LOGMNR_CONTENTS TO myadmin
GRANT SELECT ON V_$ARCHIVED_LOG TO myadmin
GRANT SELECT ON V_$LOG TO myadmin
GRANT SELECT ON V_$LOGFILE TO myadmin
GRANT RESOURCE, CONNECT TO myadmin

I've selected one line from 'v$archived_log' and trying to load the file.
BEGIN
  DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/path/to/archive/ARC000011_1061542581',Options=>DBMS_LOGMNR.new);
  DBMS_LOGMNR.START_LOGMNR(StartScn=>3083464, EndScn=>3388245, Options=>DBMS_LOGMNR.DICT_FROM_ONLINECATALOG+DBMS_LOGMNR.NO_ROW_ID_IN_STMT);
END;

I can run it from the sys as sysdba user, but when I run it from my "myadmin" user I'm getting:
Error report -
ORA-01435: user does not exist
ORA-06512: at "SYS.DBMS_LOGMNR", line 72
ORA-06512: at line 3
01435. 00000 - "user does not exist"
*Cause:
*Action:

The error is about the START_LOGMNR line when I remove it there is no error.
Which privilege I'm missing?

That is what you get when you break and ignore the basic concepts of the CDB architecture and common users and use unsupported "workarounds" (ALTER SESSION set "_ORACLE_SCRIPT"=true).
Above code works flawlessly when you create the user in a supported way.
-- create admin user on CDB
CREATE USER c##myadmin IDENTIFIED BY myadmin DEFAULT TABLESPACE users QUOTA UNLIMITED ON users ACCOUNT UNLOCK
/
-- allow access to all PDBs to the admin user
ALTER USER c##myadmin SET CONTAINER_DATA=ALL CONTAINER=CURRENT
/
-- grant needed permissions
GRANT DBA to c##myadmin                            ;
GRANT CREATE SESSION TO c##myadmin                 ;
GRANT CREATE TABLE TO c##myadmin                   ;
GRANT EXECUTE_CATALOG_ROLE TO c##myadmin           ;
GRANT EXECUTE ON DBMS_LOGMNR TO c##myadmin         ;
GRANT SELECT ON V_$DATABASE TO c##myadmin          ;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO c##myadmin   ;
GRANT SELECT ON V_$ARCHIVED_LOG TO c##myadmin      ;
GRANT SELECT ON V_$LOG TO c##myadmin               ;
GRANT SELECT ON V_$LOGFILE TO c##myadmin           ;
GRANT RESOURCE, CONNECT TO c##myadmin              ;

Then:
sqlplus c##myadmin/myadmin

BEGIN
  DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/fra/RYCDB19/archivelog/2021_01_18/o1_mf_1_130_j0bo2onq_.arc',Options=>DBMS_LOGMNR.new);
  DBMS_LOGMNR.START_LOGMNR(Options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.NO_ROWID_IN_STMT);
END;
/

PL/SQL procedure successfully completed.

SQL> select count(*) from v$logmnr_contents;

  COUNT(*)
----------
    159587

Whenever you use ALTER SESSION set "_ORACLE_SCRIPT"=true to create your own users and objects, they have their ORACLE_MAINTAINED property set to Y. This is not supported and can break functionality of features.


How could i join this two queries? I have two queries.

The first one is:

    WITH Numbered AS
(
SELECT
        B.id,
        B.masahat,
        B.tozihat,
        mahsol_name = G.mahsolname,
        ghate_code = G.ghate_code,
        M.cheshme,
        rn = ROW_NUMBER() OVER (
            PARTITION BY B.id
            ORDER BY G.id)
    FROM bahrebardarighate AS B
    JOIN ghatemanabeabi AS G
        ON B.id = G.bahrebardarighate_id
    JOIN manabeabi AS M
        ON M.id = G.manabeabi_id
)
SELECT
    N.id,
    N.masahat,
    N.tozihat,
    N.cheshme,
    [mahsol_name_1] = MAX(CASE WHEN N.rn = 1 THEN N.mahsol_name ELSE NULL END),
    [ghate_code_1] = MAX(CASE WHEN N.rn = 1 THEN N.ghate_code ELSE NULL END),
    [mahsol_name_2] = MAX(CASE WHEN N.rn = 2 THEN N.mahsol_name ELSE NULL END),
    [ghate_code_2] = MAX(CASE WHEN N.rn = 2 THEN N.ghate_code ELSE NULL END),
    [mahsol_name_3] = MAX(CASE WHEN N.rn = 3 THEN N.mahsol_name ELSE NULL END),
    [ghate_code_3] = MAX(CASE WHEN N.rn = 3 THEN N.ghate_code ELSE NULL END)
    -- Repeat the pattern up to 12
FROM Numbered AS N
GROUP BY
    N.id, -- key column
    N.masahat,
    N.tozihat,
    N.cheshme

The second query is:

    WITH Numbered2 AS
(
SELECT
        A.id,
        A.bahrebardarighate_id,
        fullname = S.fullname,
        nationalcode = S.nationalcode,
        rn = ROW_NUMBER() OVER (
            PARTITION BY A.id
            ORDER BY S.id)
    FROM ghate AS A
    JOIN shoraka AS S
        ON A.id = s.ghate_id
)
SELECT
    N.id,
    N.bahrebardarighate_id,
    [fullname_1] = MAX(CASE WHEN N.rn = 1 THEN N.fullname ELSE NULL END),
    [nationalcode_1] = MAX(CASE WHEN N.rn = 1 THEN N.nationalcode ELSE NULL END),
    [fullname_2] = MAX(CASE WHEN N.rn = 2 THEN N.fullname ELSE NULL END),
    [nationalcode_2] = MAX(CASE WHEN N.rn = 2 THEN N.nationalcode ELSE NULL END),
    [fullname_3] = MAX(CASE WHEN N.rn = 3 THEN N.fullname ELSE NULL END),
    [nationalcode_3] = MAX(CASE WHEN N.rn = 3 THEN N.nationalcode ELSE NULL END)
    -- Repeat the pattern up to 12
FROM Numbered2 AS N
GROUP BY
    N.id, -- key column
    N.bahrebardarighate_id

How could i join them in one query?

--Declare both of your CTE first
WITH Numbered AS
(
SELECT
    B.id,
    B.masahat,
    B.tozihat,
    mahsol_name = G.mahsolname,
    ghate_code = G.ghate_code,
    M.cheshme,
    rn = ROW_NUMBER() OVER (
        PARTITION BY B.id
        ORDER BY G.id)
FROM bahrebardarighate AS B
JOIN ghatemanabeabi AS G
    ON B.id = G.bahrebardarighate_id
JOIN manabeabi AS M
    ON M.id = G.manabeabi_id
),--Separating the two CTE declarations
Numbered2 AS
(
SELECT
    A.id,
    A.bahrebardarighate_id,
    fullname = S.fullname,
    nationalcode = S.nationalcode,
    rn = ROW_NUMBER() OVER (
        PARTITION BY A.id
        ORDER BY S.id)
FROM ghate AS A
JOIN shoraka AS S
    ON A.id = s.ghate_id
)

--Then your select/UNION statement goes here, ensuring the two selects have the same number of columns and datatypes.


Should i use multiple databases I have a situation, in which i need to design a database for some bakeries. I will need to design the same thing for many bakeries. For each bakery i will have for example, an Employee table, and a Product table (this will in reality much more complicated). The employees and the products do have relationships between them, but entities from one bakery never have any relationship with entities from another bakery. I was thinking, instead of making a database that has a Bakery entity as well, can't i just make several databases that only have Employees and Products in it, and then just pull data from the database i need based on which bakery is requesting it. Since every bakery will have the exact same tables, and the exact same attributes, and bakeries will never interact between each other, i was thinking this might be a good idea.

This is obviously a test example that i am working on for a web app    class project, so I'm pretty new to this, and I'm not sure if    something like this exist.

Do people do this (splitting up your data in multiple databases in
situations like this)?
Is is too slow?
Will just making a Bakery entity and then indexing it be better, even
though my database diagram will get MUCH more complicated that way?

In the real world, a lot will depend on how many customers you have, how paranoid those customers are/ how valuable the data is, and how skewed your customers are (i.e. Mom & Pop with 10 items and 100 sales a day vs Hostess Inc with millions of sales a day).

Separate databases create a variety of administrative problems.  Every time you want to do an upgrade, for example, you have to ensure that the same script gets run in every database.  If you support 10,000 bakeries, that's challenging.  Particularly when the script works perfectly on 9,900 of them and generates slightly different errors in 100 databases because they happen to have a data condition that you didn't anticipate or because different background jobs were running at the time and now you have to manually look at 100 error logs to see how to fix each database (meanwhile your middle tier code got pushed successfully and assumes that the new objects are present which isn't correct for 100 customers).  You can, of course, mitigate these problems by having fully automated deploys and fully tested rollback scripts and rolling back the 9900 databases where the upgrade succeeded because the 100 databases failed.  But that requires a pretty sophisticated deployment pipeline that has, for example, all 10,000 databases available in lower environments to test deployment scripts against and that everyone scrupulously verifies their rollback scripts (which would be rare).

If you have relatively small numbers of clients that are paying relatively large amounts to use your service to handle their very sensitive information, it may be perfectly reasonable to do this.  If you have 100 databases because you have 100 hospital chains as customers putting patient information in your system, this level effort is probably very reasonable.  It's probably not reasonable if you're trying to provide a low-cost way for lots of small bakeries to manage a relatively small number of employees and products.

If you have a mix of very large and very small customers such that different customers will likely want the same query to use different plans or such that certain large customers want to be able to partition out their workload (i.e. Hostess wants to know that there are 4 app servers and a database server that is theirs alone and that when you add 4 cores to the database server that they're going to get all of that and you want to charge Hostess for the Enterprise Edition of your application to give them the privilege of using dedicated hardware), it may make sense to put your handful of giant customers in their own database and group all the small fry into their own shared database.

Practically, that means that you'd probably design it with a bakery entity in the knowledge that some day in the future you might actually choose to deploy to multiple databases some of which would only have 1 row in the table.  Perhaps initially you put everything in one database, then move to a couple geographically separate databases (i.e. all the Aussie customers go in the database that you run out of the Aussie data center), then move the really large customers to their own database where the bakery entity only has a single row.  Then when Hostess comes to you in a few years and wants to separate Hostess Europe from Hostess America with separate sets of employees and products, you can set those up as separate bakeries in the same database.


When to use hashtable over binary search tree vice verse for Database Index Database Index can be constructed using binary search trees (BST) or HashTables ,

From my understanding so far , HashTables are superior to BST in terms of time taken for searching and insertions however BST beats HashTables hand down in terms of space efficiency

Under what situation would it be good to use hashtables instead of BST , vice-verse to construct the Database Index , any there any other points i missed out ???.

Use BST where returning ordered records by the indexed column(s) is a typical use case.

Use HT otherwise.


Abnormally high MySQL writes and larger than normal Binary Log files. How can I determine what caused this? We have a MySQL master database which replicates to a MySQL slave. We were experiencing issues where MySQL was showing a high number of writes (but not an increased number of queries being ran) for a short period of time (a few hours). We are trying to investigate the cause.

Normally our binary logs are 1 GB in file size but during the period that we were experiencing these issues, the log files jumped to 8.5 GB.

When I run mysqlbinlog --short-form BINARYLOG.0000 on one of the 8.5 GB binary log it only returns 196 KB of queries and data. When I run mysqlbinlog --short-form on a normal binary log (1 GB) it returns around 8,500 KB worth of queries and database activity. That doesn't make sense because it has 7 GB more of data yet returns less than a 1 GB binary log file.

I see lots of these statements with very sequential timestamps, but I'm not sure if that's related to the problem because they're in both the normal period as well as when we experienced these issues.

SET TIMESTAMP=1391452372/*!*/;COMMIT/*!*/;
SET TIMESTAMP=1391452372/*!*/;BEGIN/*!*/;COMMIT/*!*/;
SET TIMESTAMP=1391452372/*!*/;BEGIN/*!*/;COMMIT/*!*/;
SET TIMESTAMP=1391452372/*!*/;BEGIN/*!*/;COMMIT/*!*/;

How can I determine what caused those binary logs to balloon in size which also caused high writes, so much so it took the server offline at points, almost like a DDoS attack would?

How could mysqlbinlog return so much less data, even though the binary log file itself had 7 GB more? What can I do to identify the difference between a normal period where the binary logs are 1 GB to the period we had issues with the 8 GB binary log? Thank you for any help you can provide.

Bill

Don't use --short-form.  You are suppressing much of what you want to see.

--short-form
Display only the statements contained in the log, without any extra information or row-based events. This is for testing only, and should not be used in production systems.
— http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_short-form

That is a largely pointless option, since it returns only the statements contained in the log.  If you have a binlog that large, it's unlikely that it's full of statements -- it's full of row events.
--base64-output=decode-rows --verbose are the options you are looking for.
http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog-row-events.html
You will, at the very least, not be disappointed by the quantity of data returned... it will be much larger than the actual binlogs themselves, which are stored in a relatively tightly-packed format.


Database backup - Use case of FORMAT and INIT I understand that FORMAT looks at mediaset level and if set then creates a new media set so if there are any existing backupsets then they will no longer be available.

Also, INIT looks at backupset level and if set then overwrites the existing backupset, so if there are any existing backupset then they will no longer be available. Will it impact that backupset or all backupsets on the media set?

So how do these options work? I am trying to get an use case scenario to understand this better.

FORMAT recreates the header from scratch, effectively completely obliterating the backup file, or tape.  If the backup file or tape is corrupted, this option ignores the corruption, and enables you to reuse the file or tape.  From the Microsoft Docs:

FORMAT specifies that a new media set be created. FORMAT causes the backup operation to write a new media header on all media volumes used for the backup operation. The existing contents of the volume become invalid, because any existing media header and backup sets are overwritten.

INIT rewrites field values in the header info.  If the device is corrupt, this may not correct the issue.  Also from the Microsoft Docs:

Specifies that all backup sets should be overwritten, but preserves the media header. If INIT is specified, any existing backup set on that device is overwritten, if conditions permit. By default, BACKUP checks for the following conditions and does not overwrite the backup media if either condition exists:

Any backup set has not yet expired. For more information, see the EXPIREDATE and RETAINDAYS options.
The backup set name given in the BACKUP statement, if provided, does not match the name on the backup media. For more information, see the NAME option, earlier in this section.


Adding Time Limit parameter to Ola hallengren Index Maintenance I would like to limit the duration of the index maintenance procedure of Ola Hallengren to be 7200 seconds (2 Hours)

I found parameter TimeLimit defined as: Set the time, in seconds, after which no commands are executed. By default, the time is not limited.

Does this mean if a command is already running and the time limit reach it will force it to stop or will continue and will not run any command after ?

Thanks

If a command is already running and the time limit reach it: will continue and will not run any command after.
To answer your question I saw stored procedure definition. Find keyword @TimeLimit


service mongod cant start, permission denied mongod.lock when changing dbpath What i want to do: change dbpath of mongodb from /var/lib/mongodb to /home/user/mongodb/data

im using db version v3.4.7

what i've done

mkdir -p ~/mongodb/data
sudo chown -R mongodb:mongodb  /home/user/mongodb

change /etc/mongodb.conf

storage:
  dbPath: /home/user/mongodb/data/
  journal:

sudo mongod -f /etc/mongod.conf

this works. Also tried

sudo mongod --dbpath /home/user/mongodb/data/

command works fine

sudo service mongod start

command fails. checking the log file.

  Permission denied: "/home/user/mongodb/data/mongod.lock", terminating

what is going on? I've tried everything i know. even tried setting chmod 777 to the lock file, and still has permission denied.

If you now command:

sudo chown -R mongodb:mongodb  /home/user/mongodb
sudo service mongod restart

It will work!

Problems here was that you gave command sudo mongod --dbpath /home/user/mongodb/data/ what created ROOT ownded files to that /home/user/mongodb/data/ directory and your "normal" mongodb user don't have right to overwrite those.

So, NEVER start your mongod as root OR if you do so, remember reset ownership of those data files back to mongodb.


what databases are already set up in the log-shipping, and which ones are not? I am setting up log-shipping from server1 to server2.
there are many databases on server1 and I want to automate the process.
I have already done a few databases manually, while I am working on the automating process.
First question is:
How can I easily find out which databases are already set up in the log-shipping and which ones are not?
Currently to get a list of databases not yet on log-shipping I am doing the following:
    if object_id('tempdb.dbo.#log_shipping_monitor ') is not null
       drop table #log_shipping_monitor

    create table #log_shipping_monitor
    (
        status bit null
        ,is_primary bit not null default 0
        ,server sysname
        ,database_name sysname
        ,time_since_last_backup int null
        ,last_backup_file nvarchar(500) null
        ,backup_threshold int null
        ,is_backup_alert_enabled bit null
        ,time_since_last_copy int null
        ,last_copied_file nvarchar(500) null
        ,time_since_last_restore int null
        ,last_restored_file nvarchar(500) null
        ,last_restored_latency int null
        ,restore_threshold int null
        ,is_restore_alert_enabled bit null
        ,ts timestamp not null
        ,primary key (is_primary, server, database_name)
        ,unique (ts)
    )

    insert into #log_shipping_monitor
    ([status]
    ,[is_primary]
    ,[server]
    ,[database_name]
    ,[time_since_last_backup]
    ,[last_backup_file]
    ,[backup_threshold]
    ,[is_backup_alert_enabled]
    ,[time_since_last_copy]
    ,[last_copied_file]
    ,[time_since_last_restore]
    ,[last_restored_file]
    ,[last_restored_latency]
    ,[restore_threshold]
    ,[is_restore_alert_enabled])
exec master.dbo.sp_executesql    @stmt=N'exec sp_help_log_shipping_monitor'
                                ,@params=N'@verbose bit'
                                , @Verbose=0

SELECT d.NAME, d.recovery_model_desc
  FROM sys.databases d
where d.name not in
         (
            select l.database_name
            from #log_shipping_monitor l
            where l.[server] = @@SERVERNAME

          )
      and d.database_id > 5
      and d.state_desc = 'ONLINE'
order by d.name

this is working fine and gives me the following result:

that worked fine but there is a much easier way to monitor and have a look at the log shipping settings by using the log shipping tables in the msdb.
for instance:
msdb.dbo.log_shipping_primary_databases

Stores one record for the primary database in a log shipping
configuration. This table is stored in the msdb database.

 select * from msdb.dbo.log_shipping_primary_databases


SQL Server - materialised (indexed) view not working as expected I've created a materialised view in SQL Server, but when selecting from it I get the same performances and the same execution plan than when I select from the same query not "materialised".

The view is simple:

CREATE VIEW TestView WITH SCHEMABINDING
AS
SELECT TAB_A.C1, TAB_A.C2, TAB_A.C3
FROM TAB1
INNER JOIN TAB2 ON TAB1.C2 = TAB2.C2
INNER JOIN TAB3 ON TAB2.C1 = TAB3.C1;

CREATE UNIQUE CLUSTERED INDEX IX_TestView
    ON TestView(C1, C2, C3);

Now, when I do something like

SELECT C1 FROM TestView

I'd expect to see in the plan only an access to the view and nothing else, instead it accesses the underlying tables like the view does not even exists.
I get exactly the same plan and the same execution time, too.

What am I doing wrong? If SQL Server cannot "materialise" the view, I'd expect to at least get an error when creating it.

Like @sp_BlitzErik noted in the comments, you need to use the WITH (NOEXPAND) hint, like so:

SELECT something, something
FROM the.indexed_view WITH (NOEXPAND);

When SQL Server reads your query and replaces the name of the view with the definition of the view, that's what's known as "expanding" the view, so with NOEXPAND, you specifically tell SQL Server to not expand the view, but use the index that's there.

On the flip side, if there's no index (like if someone does ALTER VIEW!) the query will fail if you use WITH NOEXPAND.


Moving `order by` Significantly Improves Execution Time I provide a simply query that, I think, resembles my real query.

I'm using Oracle.

Given:

create table main_table(a NUMBER, b VARCHAR2(10))
create table rates_table(a NUMBER, rate NUMBER)
create table other_table(a NUMBER, c VARCHAR2(10))

and their data:

select * from main_table

1   foo
2   bar

select * from other_table

2  bar

select * from rates_table

2  42.2

The following query finds all a's in main_table where its b matches other_table's c, limiting to the first 1000 results. Then, it left joins rates_table, getting the rate from other_table:

select o.a, o.b, rt.rate from (
  select mt.a, mt.b from main_table mt
  left join other_table ot on mt.a = ot.a
  where mt.b = ot.c
  and rownum < 1000
) o
left join rates_table rt on rt.a = o.a
order by o.a, o.b

Output:

2  bar 42.2

In my real query, which, again, resembles the above query, I noticed a significant (11 seconds to < 1 second) execution time when moving the order by to within the inner SELECT:

select o.a, o.b, rt.rate from (
  select mt.a, mt.b from main_table mt
  left join other_table ot on mt.a = ot.a
  where mt.b = ot.c
  and rownum < 1000
  order by mt.a, mt.b -- <---
) o
left join rates_table rt on rt.a = o.a

It, too, returns:

2  bar 42.2

Here's the first query's EXPLAIN PLAN:

And the second's:

In general, does the placement of order by matter, i.e. will the results differ depending on where I put this clause? I'm not asking about this particular query, since the data only has a few rows.

Why would the placement of order by, i.e. in my real query, result in such an improvement - 11 seconds to < 1 second?

Comparing apples to oranges. The 2 queries and the results they return are not the same.

First query retrieves 999 rows from main_table + other_table, joins to rates_table and sorts the result. The result of the 3 table join can be 999 rows or 10 million rows as well, depending on the data. If it is 10 million, it has to sort 10 million rows.

Second query retrieves 999 rows from main_table + other_table and sorts them right after. Finally joins the rates_table, and does not sort the final result.

The first query sorts everything, the seconds query sorts only the result of the subquery. Sorting 10 million rows will most likely need more resources+time than sorting 999 rows. The cost of sorting is often overlooked.

The first query returns a sorted resultset, but for the second query, there is no guarantee of returning a sorted result.

The second query may return inconsistent results between different database versions because of how the optimizer works in different versions. For example if column a is indexed, the optimizer may choose to access the table using the index, and returns the first 999 rows sorted anyway, because data is already sorted in the index. Or it may choose to scan the table, get whatever 999 rows it can, then sort that. This can be easily tested with a hints such as: /*+ optimizer_features_enable('12.1.0.2')*/ and providing different versions.

The first query is guaranteed to return the same result on all versions, as long as the data is organized the same way. As there is no criteria specified for getting the first 999 rows (first, based on what?), after reorganizing the table (move, shrink, export/import, redefinition), it may also return different results for the same base data. In this aspect, both queries are inaccurate, unless you do not care about consistent results - which is a quite rare situation.


What is the difference between sys and system accounts in Oracle databases? There are two ways to connect to Oracle as a administrator using sqlplus:

sqlplus sys as sysdba
sqlplus system/manager

These accounts should be uses for different purposes, I suppose.

Which tasks are these two schemas meant for? When should I use one or the other among them?

SYS

Automatically created when Oracle database is installed
Automatically granted the DBA role
Has a default password: CHANGE_ON_INSTALL (make sure you change it)
Owns the base tables and views for the database data dictionary
The default schema when you connect as SYSDBA

Tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator, and no one should create any tables in the schema of user SYS. Database users should not connect to the Oracle database using the SYS account.

SYSTEM

Automatically created when Oracle database is installed
Automatically granted the DBA role
Has a default password: MANAGER (make sure you change it)
Used to create additional tables and views that display administrative information
Used to create internal tables and views used by various Oracle database options and tools

Never use the SYSTEM schema to store tables of interest to non-administrative users.

/via


How to select same number of data points over different time ranges Grafana is querying our postgres database using this SELECT statement:

SELECT
  tstamp AS "time"
  dvalue AS "weight"
FROM tablename
WHERE
  description = "exp_weight" AND
  tstamp BETWEEN 'time1' AND 'time2'
ORDER BY
  tstamp

How can I code this such that I'm displaying the same number of data points even if my time intervals are different (ex. display 1,000 data points in the last hour or display 1,000 data points in the last month).

One simple solution is:

SELECT *
FROM (SELECT tstamp AS time,
             dvalue AS weight
      FROM tablename
      WHERE description = 'exp_weight'
        AND tstamp BETWEEN 'time1' AND 'time2'
      ORDER BY random()
      LIMIT 1000
     ) AS q
ORDER BY time;

Explanation: The dataset is shuffled randomly, tgen the first 1000 values are taken and the result ordered by time. The subquery is just a syntactical necessity.


SQL Server Replication Snapshot Agent login fails I have setup TRANSACTIONAL REPLICATION from ServerA to ServerB

ServerA = Primary Server   =  SQL Server 2008 R2 Enterprise
ServerB = Secondary Server =   SQL Server 2008 R2 Standard Edition.

I have used a Domain Account for Snapshot Agent and Log Reader Agent, called SQLAgent.

The initial snapshot is initialised successfully, from there its all down hill.

Error Log:

  Login failed for user 'ServerName\SQLAgent'. Reason: Token-based
  server access validation failed with an infrastructure error. Check
  for previous errors. [CLIENT: ]

Replication Monitor:

Publication Status:  OK
Agents: for both Agents Snapshot and Log Reader Status shows it is Running..... and Last Run Property says Starting Agent...

As it is obvious from the error message the Login is failing to get access to SQL Server, So just for test purpose I gave this SQLAgent account sysadmin role. Yet the error persist.

All the material I can find online, suggests Giving more permissions to login will solve the issue. But if sysadmin role hasnt solved it, I have kind of ran out of ideas now.

Can someone please suggest what is that I am missing?

how can I fix it?

I have used a Domain Account for Snapshot Agent and Log Reader Agent,
  called SQLAgent.

If you check this answer:

The process could not connect to Subscriber

you will see that the SQLAgent account must have a login on your ServerB and must be a db_owner on your subscription database there.

It does not need to be a sysadmin on your ServerA.

It has though, to have sufficient permissions on your snapshot folder wherever you decide that should be.


SSMS 2008 R2 no longer connects to database Before Christmas I had no problem connecting SSMS 2008 R2 to a specific remote database. Since Christmas, I get this error when I try to connect:

  TITLE: Connect to Server

  Cannot connect to [IP ADDRESS].

  ADDITIONAL INFORMATION:

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  A transport-level error has occurred when receiving results from the
  server. (provider: TCP Provider, error: 0 - The semaphore timeout
  period has expired.) (Microsoft SQL Server, Error: 121)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=121&LinkId=20476

Any idea why this is happening? The link provided in the error message gives no information at all.

As far as I know, no updates or changes have been made to SSMS 2008 R2 or the database since the happy time when I could connect and now.

Any help much appreciated.

Your first test should be to just try and connect via telnet to port 1433 of the ip in question (assuming it isn't a named instance in which case check the start of your SQL error log to get the dynamic port number).  This will at least prove the server is listening correctly and you dont have any network/firewall issues.