select row data as column list In my database I have a set of custom field values that can be setup by the customer.  They can create labels for these fields and then assign values which are linked back to particular tables in the database.  I need to be able to report this information such that the labels appear as columns and the values are listed below.  I could do this with a PIVOT function except that the information in the values is not necessarily aggregatable.  (I'm making up new words)  I'm using the following code for testing.

create table custlabel (custlabelid int identity(1,1) not null, custlabel varchar(50))
CREATE table custvalue (custvalueid int identity(1,1) not null, custlabelid int not null, custvalue varchar(50), linkageid int not null)
create table linkage (linkageid int identity(1,1), blah varchar(50))

INSERT into linkage(blah)
values('test'), ('test2'), ('test3')

INSERT INTO custlabel(custlabel)
values('info1'), ('info2'), ('info3'), ('info4'), ('info5')

INSERT INTO custvalue(custlabelid, custvalue, linkageid)
VALUES(1, 'info1 value1', 1),
(1, 'info1 value2', 1),
(1, 'info1 value3', 1),
(2, 'info2 value1', 1),
(2, 'info2 value2', 1),
(2, 'info2 value3', 1),
(2, 'info2 value4', 1),
(3, 'info3 value1', 1),
(3, 'info3 value2', 1),
(3, 'info3 value3', 1),
(4, 'info4 value1', 1)

What I need to see come back is

blah, info1, info2, info3, info4, info5
test, info1 value1, info2 value1, info3 value1, NULL, NULL
test, info1 value2, info2 value2, info3 value2, NULL, NULL
test, info1 value3, info2 value3, info3 value3, NULL, NULL
test, info4 value1, NULL, NULL, NULL, NULL

You should still be able to get the result that you want using the PIVOT function.  If your values that you need to aggregate are a string, then you would use either min or max to get the final result.

Based on your sample data and the desired result, you will need to generate a distinct value that will be used when grouping in the PIVOT. I would use row_number() to create a sequenced number for each blah and custlabel, this will allow you to return multiple rows for each blah.

If you have a limited number of custlabels, then you can hard-code the query:

select blah, info1, info2, info3, info4, info5
from
(
  select cl.custlabel,
    cv.custvalue,
    l.blah
    , row_number() over(partition by l.blah, cl.custlabel
                      order by cl.custlabel) seq
  from custlabel cl
  inner join custvalue cv
    on cl.custlabelid = cv.custlabelid
  inner join linkage l
    on cv.linkageid = l.linkageid
) d
pivot
(
  max(custvalue)
  for custlabel in (info1, info2, info3, info4, info5)
) piv;

See SQL Fiddle with Demo.

But if you are going to have an unknown number of custlabels, then you will want to look at using dynamic SQL to get the final result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(custlabel)
                    from custlabel
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT blah, ' + @cols + '
            from
            (
              select cl.custlabel,
                cv.custvalue,
                l.blah
                , row_number() over(partition by l.blah, cl.custlabel
                                  order by cl.custlabel) seq
              from custlabel cl
              inner join custvalue cv
                on cl.custlabelid = cv.custlabelid
              inner join linkage l
                on cv.linkageid = l.linkageid
            ) x
            pivot
            (
                max(custvalue)
                for custlabel in (' + @cols + ')
            ) p '

execute sp_executesql @query;

See SQL Fiddle with Demo

Both versions will give a result:

| BLAH |        INFO1 |        INFO2 |        INFO3 |        INFO4 |  INFO5 |
| test | info1 value1 | info2 value1 | info3 value1 | info4 value1 | (null) |
| test | info1 value2 | info2 value2 | info3 value2 |       (null) | (null) |
| test | info1 value3 | info2 value3 | info3 value3 |       (null) | (null) |
| test |       (null) | info2 value4 |       (null) |       (null) | (null) |


Table Valued Function Very Slow I have a TVF that when called takes 10 secs to run.

However if I take the query inside that TVF, it takes only 1 sec. I have added correct indexes on this underlying query tables.

In the Execution Plan of the TVF it is performing a table scan. How do I find out why it is doing this?

Thanks.

Query...

select Username, Identifier
from [dbo].Unmapped(@CustID, @StartDate, @EndDate)

Table Scan properties...

T-SQL manages the execution plan of non-inline table-valued functions separately from the code that calls the function. It treats it like a black box, which is why you see the UDF in the query plan instead of the UDF’s contents. This probably is related to the fact that non-inline table-valued functions can have multiple statements and a query plan is only capable of showing a single statement(?) (or at least it looks that way).

Mikael Eriksson commented:

  You have a multi statement table valued function and SQL Server can not look into that and show the execution plan for the function. The scan you see here is a scan of the rows returned by the function and since you have no where clause, a scan is the best thing to do.

...and linked to this pastebin showing how to see the cached query plan of the non-inline table-valued function.

If you have executed your function at least once (e.g., SELECT * FROM myUdf()), then it should be cached in SQL Server. To show that cached plan, take the name of your function minus any schema and plug it into the query below (quoted from Mikael's code):

select
  PlanCreated       = qs.creation_time,
  ObjectName        = object_name(st.objectid),
  QueryPlan         = cast(qp.query_plan as xml),
  QueryText         = substring(st.text, 1 + (qs.statement_start_offset / 2), 1 + ((isnull(nullif(qs.statement_end_offset, -1), datalength(st.text)) - qs.statement_start_offset) / 2))
from sys.dm_exec_query_stats as qs
  cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
  cross apply sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) as qp
where object_name(st.objectid) = 'FunctionName'

In the result set, the QueryText column should show a statement from the function and the QueryPlan should contain an XML query plan. In SSMS, if you click on a QueryPlan cell, it will automatically open up a new query plan viewer window. You will need to look at the source of your function and cross reference it to the statements and click through the query plans to search for performance issues.


Is it impossible to force MariaDB users to SSL? First of all, my english is really bad, so sorry for my english mistakes.
I have a MariaDB 10.2.15. I installed SSL, and its working fine, but I cant force use SSL to users.

my.cnf:

[mysqld]
ssl
ssl-ca=/etc/mariadb/ssl/ca.pem
ssl-cert=/etc/mariadb/ssl/server-cert.pem
ssl-key=/etc/mariadb/ssl/server-key.pem
ssl-cipher = AES128+EECDH:AES128+EDH

ssl variables:

+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| have_openssl        | YES                              |
| have_ssl            | YES                              |
| ssl_ca              | /etc/mariadb/ssl/ca.pem          |
| ssl_capath          |                                  |
| ssl_cert            | /etc/mariadb/ssl/server-cert.pem |
| ssl_cipher          | AES128+EECDH:AES128+EDH          |
| ssl_crl             |                                  |
| ssl_crlpath         |                                  |
| ssl_key             | /etc/mariadb/ssl/server-key.pem  |
| version_ssl_library | OpenSSL 1.0.1e-fips 11 Feb 2013  |
+---------------------+----------------------------------+

I found this variable:

require_secure_transport

and I also found this:

  MySQL-only variable determining whether client to server connections
  need to be secure.

So, can I do anything else? Cause now, the users can connect without SSL. I feel its really unnecessary without forcing.

UPDATE for clear:
SHOW GRANTS:

GRANT ALL PRIVILEGES ON *.* TO 'denes'@'%' IDENTIFIED BY PASSWORD '*SOMETHINGPASSWORD' REQUIRE SSL WITH GRANT OPTION

And I still can login to MariaDB over TCP without SSL

REQUIRE SSL grant option on users is what you need. It works for me as intended:

MariaDB [(none)]> CREATE USER testssl@localhost;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> GRANT USAGE ON *.* TO testssl@localhost REQUIRE SSL;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> Bye

$ sudo mysql -u testssl --ssl
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.7-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show grants;
+---------------------------------------------------------+
| Grants for testssl@localhost                            |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testssl'@'localhost' REQUIRE SSL |
+---------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> Bye

$ sudo mysql -u testssl --skip-ssl
ERROR 1045 (28000): Access denied for user 'testssl'@'localhost' (using password: NO)

$ grep ssl /etc/my.cnf
ssl-ca = /etc/mysql/ca.pem # [client]
ssl-cert = /etc/mysql/client-cert.pem
ssl-key = /etc/mysql/client-key.pem
ssl-ca = /etc/mysql/ca.pem # [server]
ssl-cert = /etc/mysql/server-cert.pem
ssl-key = /etc/mysql/server-key.pem

You can see how workbench works with SSL, but fails to connect if I force it to not use SSL:

require_secure_transport is MySQL 5.7+ only, and while it has something to do with forcing TLS, it also considers secure local unencrypted connections.


Replication stops with GTID_NEXT error after creation/drop of memory table in mysql5.6 We have recently upgraded to mysql5.6.25 from mysql5.5.x/mysql5.1.x on our mysql-cluster.
Below is a brief snapshot of our architecture.

Since we have upgraded and enabled gtid-mode we have been intermittently getting slave errors similar to :

Last_SQL_Error: Error 'When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is 'd7e8990d-3a9e-11e5-8bc7-22000aa63d47:1466'.' on query. Default database: 'adplatform'. Query: 'create table X_new like X'

Our observations are as below..

These slave errors are resolved simply by restarting the slave.
Such errors are always with Create/Drop of tables which have Memory Storage Engine.
Errors on Complete-Slave(B) show up continuously at a fixed minute (39th) of the hour and have been repeating since we have upgraded, almost a week.
Errors on Complete-Slave as well as Partial slave are observed whenever its master is restarted.
Cluster-1 and Cluster-2 have centos machines and Cluster-3 have ubuntu-machines. Slaves on centos machines also fail with the same error whenever its master(C/D) is restarted, but slave on ubuntu machines do not fail!!.

We have temporarily been able to live with this issue by setting up an action-script on our monitoring system which fires on slave error alert on any machine.

A look into gtid_next section in replication-options doc of mysql tells following

  Prior to MySQL 5.6.20, when GTIDs were enabled but gtid_next was not
  AUTOMATIC, DROP TABLE did not work correctly when used on a
  combination of nontemporary tables with temporary tables, or of
  temporary tables using transactional storage engines with temporary
  tables using nontransactional storage engines. In MySQL 5.6.20 and
  later, DROP TABLE or DROP TEMPORARY TABLE fails with an explicit error
  when used with either of these combinations of tables. (Bug #17620053)

This seems related to my issue but still doesn't not explain my scenario.
Any hints/direction to solve the issue would be greatly appreciated...

EDIT :
I managed to find a similar recently reported bug in mysql(#77729), description of which is as follows :

https://bugs.mysql.com/bug.php?id=77729

  When you have table with Engine MEMORY working on replication master,
  mysqld injects "DELETE" statement in binary logs on first access query
  to this table. This insures consistency of data on replicating slaves.

  If replication is GTID ROW based, this inserted "DELETE" breaks
  replication.  Logged event is in STATEMENT format and do not generate
  correct SET GTID_NEXT statements in binary log.

Unfortunately, the status of this bug is marked as Can't Repeat...

I encountered a similar problem that generated the same error. On a master instance I dropped a database.
On a slave there was a file left over <tablename>.exp (I forget what purpose this serves).
The slave replication died with

last_Error: Error 'DROP DATABASE failed; some tables may have been
dropped but the database directory remains. The GTID has not been
added to GTID_EXECUTED and the statement was not written to the binary
log. Fix this as follows: (1) remove all files from the database
directory ./db_to_drop/; (2) SET
GTID_NEXT='c62b6474-84e6-11e8-bf49-00164ef9ea6c:73066506'; (3) DROP
DATABASE db_to_drop.' on query. Default database: 'db_to_drop'.
Query: 'drop database db_to_drop'

I removed the file, ran the SET and restarted replication:

SET GTID_NEXT='c62b6474-84e6-11e8-bf49-00164ef9ea6c:73066506';
start slave; ERROR 1837 (HY000): When @@SESSION.GTID_NEXT is set to a
GTID, you must explicitly set it to a different value after a COMMIT
or ROLLBACK. Please check GTID_NEXT variable manual page for detailed
explanation. Current @@SESSION.GTID_NEXT is
'c62b6474-84e6-11e8-bf49-00164ef9ea6c:73066506'.

And this, from following the suggestion. Since it's only a development cluster, I wasn't worried about losing it entirely, so I punted and tried setting it to the next GTID.
SET GTID_NEXT='c62b6474-84e6-11e8-bf49-00164ef9ea6c:73066507';
(i.e. + 1). Restarted replication and things got back to normal.


how to create user with less rights I have created a sysdba user test_dba and then created database schema testdb.
In that I created 50 tables.
Now I need to create one more user "testdb1" for that schema with less rights .
That can select/update/alter data but can not create a new table.
Please help me for this.

Generally when someone wants to create a user to run an application, but not own the tables, they create a group that has select on the necessary tables and views and execute on the necessary stored procedures. They can then either create synonyms for the objects that they need or create public synonyms for the objects that everyone needs. You might want to have a separate roll for admin users compared with regular users.

Once you do that then when you create a user they would need connect or create session and the role that was created for all users. If they have temporary tables they either need the create table privilege or to just have the tables created for them.

Generally giving each user a separate login directly to the database is something that was done with client server apps. Most web based apps handle the logins on the app server and don't give direct access to the database for users. They just pool the connections and have business logic to control which user can use which function. You may want to rethink why you are creating individual users directly in the database.


How to modify `proxies_priv` in MySQL I recently received a warning from MySQL that stated:

[Warning] 'proxies_priv' entry '@ root@serverdomain' ignored in --skip-name-resolve mode.

I looked in mysql.proxies_priv and there is definitely an entry for "serverdomain", however there is no user with "@serverdomain".  Documentation on this is very sparse, this is all I could find.

How do I remove this entry and how was it ever created?

Given the link from your question, look at the table mysql.proxies_priv
mysql> show create table mysql.proxies_priv\G
*************************** 1. row ***************************
       Table: proxies_priv
Create Table: CREATE TABLE `proxies_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Proxied_host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Proxied_user` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `With_grant` tinyint(1) NOT NULL DEFAULT '0',
  `Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Host`,`User`,`Proxied_host`,`Proxied_user`),
  KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User proxy privileges'
1 row in set (0.00 sec)

mysql>

Since it is a MyISAM table, you could just truncate the table.
In case you need it back, make a backup of it and truncate it after.
ALTER TABLE mysql.proxies_priv RENAME mysql.proxies_priv_backup;
CREATE TABLE mysql.proxies_priv LIKE mysql.proxies_priv_backup;

Then, go restart mysql
service mysql restart

A far simpler way would be to use the REVOKE command:
REVOKE PROXY ON user
    FROM user [, user] ...

Due to the warning, you would have to restart mysql to make sure the warning does not come back.
Give it a Try !!!


moving servers and mysql dbs We have a number of websites using mysql dbs. We are moving these sites to a new server. We also moving the dbs to a new server seperate server. To make this easier we have setup master slave replication.

The question is as we start moving the sites to the new server is there any problem with the using the slave db until we have moved all the sites across and can turn off the master?

No issues with using the slave DB if you are ok with having the site in read-only until the cutover.

You could turn the READ_ONLY=1 in my.cnf (set global read_only=1) in the slave just to make sure there are no unintentional writes to the slave.


How to check if subscriber replication initialization has timed out I've added merge replication to a SQL Server 2005 database and have added a SQL Server 2000 subscriber. Creation of the publisher, subscriber and snapshot creation went fine, but there seems to be a problem with the initialization.

After about 1 hour and 40 minutes the following error appeared in replication monitor during the initialization stage

  The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

As far as I can tell this process is still running as sp_who2 gives the following:

SPID  Status                         Login HostName BlkBy DBName   Command     CPUTime DiskIO LastBatch      ProgramName                                  SPID
----- ------------------------------ ----- -------- ----- -------- ----------- ------- ------ -------------- -------------------------------------------- -----
56    RUNNABLE                       sa    HOST   .   database BULK INSERT 27126   287567 09/26 10:03:16 HOST-database-database_pub-REMOTEHOST-6 56

and select * from sysprocesses gives

spid   kpid   blocked waittype waittime    lastwaittype                     waitresource dbid   uid    cpu         physical_io          memusage    login_time              last_batch              ecid   open_tran status                                                                                nt_username                                                                                net_address  net_library  loginame
------ ------ ------- -------- ----------- -------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ ------ ----------- -------------------- ----------- ----------------------- ----------------------- ------ --------- --------
56     5392   0       0x0042   31          OLEDB                                          7      0      27266       288945               0           2011-09-26 10:02:29.717 2011-09-26 10:03:16.780 0      2         runnable                                                                                000C29092F59 TCP/IP       sa

Is the process still running or has it got stuck and are there any tools I can use to see what's going on here?

Thanks,

Tom

Update

The process described above was no longer visible from sp_who2 or sysprocesses around 6 hours later. There don't seem to have been any errors in the logs, and it doesn't look like the subscriber was correctly initialised as the agent attempts to initialise it again on synchronization. Does anyone have any further ideas?

Kind regards,

Tom

Use SQL Server Replication Monitor in order to review the current state of your Replication topology.

See Monitoring Replication with Replication Monitor


MySQL client out of memory - Linked Table MsSQL to MySQL I'm currently having an issue on 2 out of 3 SQL servers when copying large amounts of data to a MySQL table. The MySQL table currently houses ~6 million records, and the query that is running is trying to update / delete / insert another 100,000 records.

I've attempted to use OPENQUERY on the commands I'm running to attempt to do this but that's not working either and I am just presented with the error:

"[MySQL][ODBC 5.1 Driver][mysqld-5.6.35]MySQL client ran out of memory"

I thought it could be a driver issue so updated to the latest ODBC driver (5.2) but that did not work either.

The two servers i am using are both 32 bit operating systems which may have a great deal to do with it (the 64 bit server hasn't had an issue as yet).

Is there another setting i can have on the server that will let it pool values and process with what memory it has available or am I just stuck with an issue of needing more memory for processing a result list that is this large?

UPDATE: the third server is now not working either, all showing same results

My cnf settings just have:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 512K
innodb_buffer_pool_size = 1G
innodb_log_file_size = 64M
join_buffer_size = 128K
innodb_buffer_pool_instances = 1

Have to setup the symbolic-links section still, but at the moment this is still a non-production server so not a big issue.

The two statements that are failing are inserting and deleting rows from a MySQL table that is > 4 million rows. Both are inside a stored procedure that reads values from a transfer table (transfer table is filled via triggers on the main tables which read in whether the query should send a delete or insert request to the mysql table)

    DELETE
        a
    FROM
        [LinkServerTable]...Table1 as a
    INNER JOIN
        [dbo].[TransferTable] as b
    ON
        a.Key1 COLLATE DATABASE_DEFAULT = b.Key1 COLLATE DATABASE_DEFAULT
    AND
        a.Key2 = b.Key2
    AND
        a.Key3 = b.Key3
    AND
        a.Key4 = b.Key4
    AND
        a.Key5 = b.Key5
    WHERE
        a.Branch = 'Databases Branch'
    AND
        (b.QueryType = 'Delete' OR b.QueryType = 'Update')
    AND
        b.DateQueried < @DateNowVar

    INSERT INTO [LinkServerTable]...Table1
    (
            Columns,
            Branch
        )
    SELECT
        b.Columns,
        Branch = 'Databases Branch'
    FROM
        [dbo].[TransferTable] as a
    INNER JOIN
        [dbo].OriginalTable as b
    ON
        a.Key1 COLLATE DATABASE_DEFAULT = b.Key1 COLLATE DATABASE_DEFAULT
    AND
        a.Key2 = b.Key2
    AND
        a.Key3 = b.Key3
    AND
        a.Key4 = b.Key4
    AND
        a.Key5 = b.Key5
    WHERE
        (QueryType = 'Insert' OR QueryType = 'Update')
    AND
        DateQueried < @DateNowVar
    AND
        NOT EXISTS
            (SELECT
                1
             FROM
                Transfertable as c
             WHERE
                a.Key1 = c.Key1
             AND
                a.Key2 = c.Key2
            AND
                a.Key3 = c.Key3
            AND
                a.Key4 = c.Key4
            AND
                a.Key5 = c.Key5
             AND
                a.DateQueried < c.DateQueried)

That extra statement at the end of the insert section is so it won't process multiple updates / deletes if not required (if they've been updated multiple times in the time it takes to run the procedure)

Do the DELETE in chunks.  More discussion .


unknown opcode 2011

You are trying to use an older version of mongosniff to inspect traffic for a MongoDB server that uses a newer version of the MongoDB wire protocol.

Specifically, it looks like you are using a version of mongosniff older than 3.2 to inspect traffic from MongoDB 3.2 (which added the 2011 opcode for dbCommandReply).

To fix this you should use a version of mongosniff that matches your MongoDB server deployment.


Update the record from query result Lets say I have a table X with following definition:
CREATE TABLE X( id INT PRIMARY KEY, field1 varchar(100), field2 int, field3 double);

Then I issue a query:
SELECT field1, field2 from X;

Let's say this query returned 10 records.
Is there a way to write a query to update the record #5 from the SELECT? What would the query will look like? Or I will have to include id in that query, somehow find its value in a record #5 and then use it in a query below?
Something like UPDATE X SET field1 = 'def' WHERE....
I have a problem with the condition - what to write for the WHERE clause. I'm working with multiple DBMSes, so need a generic way if possible.
More details:
I can have a query like this:
SELECT field1, field2 FROM X WHERE field3 = "abc";

or I can have a query like this:
SELECT X.field1, Y.field2 FROM X, Y WHERE X.pk = Y.pk AND X.field3 = "abc";

Let's say when I skip to record 5, X.field1 will show "abc". I want this field to be updated to "def". So I type "def" and skip to record 6. In both cases I want to update record 5 X.field1 of the resulting recordset.
As you can see I'm going by the records in a recordset (results of executing a query). I want to emulate what Access is doing.
Let's say I open the new form in Access and I base it on the query.
I re-arrange the field on the form to my liking and execute the form.
Then I go to display the record #5 in the form and then update a field.
This update is instantaneous, no matter the complexity of the query behind the form.

SELECT  *
FROM    Table t
WHERE   1 = 1
        AND EXISTS (Define criteria here)

The above is a select statement. It returns rows stored in a table.
To make this an UPDATE statement, use exactly the same syntax, except instead of using SELECT syntax, use UPDATE syntax.
UPDATE  t
SET     Column1 = 'SomeNeatValue'
        ,Column2 = 'AnotherNeatValue'
FROM    Table t
WHERE   1 = 1
        AND EXISTS (Define criteria here)

How you identify which records to select/update is on the developer. If you need assistance, I'd start at www.w3schools.com and bone up on query syntax.


Trouble with SQL Server authentication in SQL Server 2012 I am working with a SQL Server 2012 database. I am able to log into the server using Windows authentication. Although I have the SQL Server authentication username and password which is provided to me by the team, but authentication fails.

Is there anyway to find the password for the username : sa. Also, how to create new username and password without affecting the existing sa username.

New accounts with system administrator access can be created by other equivalent accounts but there is no way to reverse engineer the password for an existing account. You should also verify that the SQL Server you're connecting to allows for mixed authentication (Windows and SQL Server).

This StackOverflow question describes how to access authentication via SQL Server Management Studio (https://stackoverflow.com/questions/1393654/how-to-change-from-sql-server-windows-mode-to-mixed-modesql-server-2008)


Does SQL Server Snapshot replication completely copy the data every time or does it issue deltas? I am looking at snapshot replication between two servers. Here is what I have:

500GB database
~500MB nightly bcp loads
~50MB daily transactions

I was asking other DBAs in the company about which replication type to use. I was told to use Snapshot Replication. However, from what I understand and have been reading, every night after the load the snapshot will completely copy the database over to the distributor and then completely overwrite the other server.

Do snapshots work in deltas or is it a complete copy every time?

From Books On Line:

  Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

  Using snapshot replication by itself is most appropriate when one or more of the following is true:

  Data changes infrequently.
  It is acceptable to have copies of data that are out of date with respect to the Publisher for a period of time.
  Replicating small volumes of data.
  A large volume of changes occurs over a short period of time.

  Snapshot replication is most appropriate when data changes are substantial but infrequent. For example, if a sales organization maintains a product price list and the prices are all updated at the same time once or twice each year, replicating the entire snapshot of data after it has changed is recommended. Given certain types of data, more frequent snapshots may also be appropriate. For example, if a relatively small table is updated at the Publisher during the day, but some latency is acceptable, changes can be delivered nightly as a snapshot.

  Snapshot replication has a lower continuous overhead on the Publisher than transactional replication, because incremental changes are not tracked. However, if the dataset being replicated is very large, it will require substantial resources to generate and apply the snapshot. Consider the size of the entire data set and the frequency of changes to the data when evaluating whether to utilize snapshot replication.

Therefore, deltas are not generated, the entire database is snapshotted and replicated.


combining two different record into single result I am fetching data from 3 tables with the help of join. My output is in JSON format.

Output in JSON format:-

{"order_id":"2",
 "location_address":"Testing location Delhi",
 "emp_first_name":"**Ranjeet**",
 "order_status":"0",
 "from_date":"0000-00-00 00:00:00",
 "actions":"\r\n <\/i> Edit <\/a>\r\n \r\n <\/i> Delete <\/a>"},

{"order_id":"2",
"location_address":
"Testing location Delhi",
"emp_first_name":"**abhijeet**",
"order_status":"0",
"from_date":"0000-00-00 00:00:00",
"actions":"\r\n <\/i> Edit <\/a>\r\n \r\n <\/i> Delete <\/a>"},

The problem is, In above output, All the data is same but emp_first_name are different because both are having seperate id's. Now i want to combine the data of both the emp_first_name and display it as single output like

The output which i want :-

{"order_id":"2",
 "location_address":"Testing location Delhi",
 "emp_first_name":"**Ranjeet,abhijeet**",
 "order_status":"0",
 "from_date":"0000-00-00 00:00:00",
 "actions":"\r\n <\/i> Edit <\/a>\r\n \r\n <\/i> Delete <\/a>"},

QUery I Used :-

$this->datatables
   ->select('o.order_id,
        concat_ws(" " , `l.location_street`, `l.location_city` ) as location_address,
        e.emp_first_name, o.order_status, o.from_date')
   ->from('orders o')
   ->join('location l', 'o.location_id = l.location_id')
   ->join('order_details od', 'od.order_id = o.order_id')
   ->join('employee e', 'e.emp_id = od.emp_id');

Any help would be appreciated. Thank you in advance.

You should use GROUP_CONCAT on emp_first_name. Maybe something like:

$this->datatables
   ->select('o.order_id,
        concat_ws(" " , `l.location_street`, `l.location_city` ) as location_address,
        group_concat(e.emp_first_name SEPARATOR ','), o.order_status, o.from_date')
   ->from('orders o')
   ->join('location l', 'o.location_id = l.location_id')
   ->join('order_details od', 'od.order_id = o.order_id')
   ->join('employee e', 'e.emp_id = od.emp_id')
   ->group_by('o.order_id');


PostgreSQL: restore a record from a backup We have a Rails app backed by a PostgreSQL DB and hosted on Heroku.

We recently had a bug that deleted some (very few before we caught it) records.

Excuse my ignorance but is there a straightforward way to directly pull a record (or 3) off of a backup and insert it back into the live DB? Or must these be recreated manually from the Rails console?

Thanks for any advice, warnings, etc.

On way would be to restore only the relevant table to a new empty dummy database. Then find rows with standard SQL tools. Not sure if you are restricted by Heroku in any way. On the shell, as postgres user (or any user with sufficient privileges) first create an empty staging DB:

createdb -T template0 dummy

If the DB isn't too big and you have plenty of disk space, just restore the whole DB for simplicity. Else, selectively restore tables:

pg_restore --dbname=dummy --schema=public --table=foo my_backup

Details in the manual.

Or if it's just 1 or 3 records I would open the (default plain-text) backup with vim or your editor of choice, search manually, copy selected rows (lines in the backup) to a new file my_copy_file.sql. If you are sure about the data you can COPY into existing table directly, rows will be added.
If you need to manipulate data first COPY to a new, empty table. Create it first if it's not there, yet:

CREATE TABLE staging (Like foo);
COPY staging FROM 'path/to/my_productive_db`

Then proceed from there.


Return 2 lines from a table if one line contains a column > 0 and the other is NULL How to return 2 lines even if one line is null but the other > 0

 item_id  | location    | quantity
-------------------------------------
    14    |    1        |    10
    14    |    2        |   <null>
    21    |    1        |   <null>
    21    |    2        |   <null>

The result should return:

 item_id  | location    | quantity
-------------------------------------
    14    |    1        |    10
    14    |    2        |   <null>

I need a query with a clause where quantity > 0. If quantity is null on location 1 and 2 do not return, if quantity > 0 in one location return both lines.
I have 10 locations (1 to 10) and I need to look quantity where location in (1,2).

select t1.*
from the_table t1
where t1.location_id in (1,2)
  and exists (select *
              from the_table t2
              where t1.item_id = t2.item_id
                and t2.quantity > 0
                and t2.location_id in (1,2));

The condition t2.quantity > 0 will be false for null values, so if all quantities for an item_id are null the where condition will be false, and the corresponding item will not be returned

Online example: http://rextester.com/NSRQ98002


how to set "result to text" with a tsql statement in sp? is there any way to set "result to text" within of SP while this execute a process? a tsql stament?

Thanks.

No, how the results are displayed (if at all) is the job of the client application.


I want to count all the rows for todays / yesterdays date based on a DATETIME - mysql+php I have database "db2" with table "menjava"

In table menjava have "id", "author" and "date_submitted" field

id - auto_increment
author - int(11)
date_submitted - datetime

I want to count all the rows for todays date and all the rows for yesterdays date (so there will be two codes with conditions) based on a DATETIME field called 'date_submitted' that holds the date and time of each record's creation.

In the file result.php, there is this count displayed, but it does not work. In the same file (result.php) I have some other code to display data from different database, so I think that povezava.php is working ok.

My code:

 <?
    require "povezava.php";
    $q=mysql_query(" SELECT COUNT(*) AS total_number FROM menjava
WHERE date_submitted >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)",$link2);
// now you can
if ( $nt = mysql_fetch_array($q)){
echo $nt["total_number"];
$q=mysql_query($nt) or die(mysql_error());
}

    ?>

my file povezava.php looks like this:

<?
$servername='localhost';

$dbusername='user';
$dbpassword='pass';

$dbname1='db1';
$dbname2='db2';

$link1 = connecttodb($servername,$dbname1,$dbusername,$dbpassword);
$link2 = connecttodb($servername,$dbname2,$dbusername,$dbpassword);

function connecttodb($servername,$dbname,$dbusername,$dbpassword)
{
    $link=mysql_connect ("$servername","$dbusername","$dbpassword",TRUE);
    if(!$link){die("Could not connect to MySQL");}
    mysql_select_db("$dbname",$link) or die ("could not open db".mysql_error());
    return $link;
}
    ?>

Error that I get:

A PHP Error was encountered

Severity: NoticeMessage: Array to string conversionFilename: templates/master.phpLine Number: 231 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Array' at line 1

Fixed:

<?
    require "povezava.php";
    $q=mysql_query("SELECT COUNT(*) AS total_number FROM menjava WHERE date_submitted >= DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY)",$link2);
// working
if ( $nt = mysql_fetch_array($q)){
echo $nt["total_number"];
}

    ?>

Thank you!

You need to access column by index :

if ( $nt = mysql_fetch_array($q)){
echo $nt[0];
}

(Note if instead of while - it's not required, but your query returns at most 1 row, so no need for while).
If you want to access column by name, change the query to use aliases:

$q=mysql_query(" SELECT COUNT(*) AS total_number FROM menjava
WHERE date_submitted >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)",$link2);
// now you can
if ( $nt = mysql_fetch_array($q)){
echo $nt["total_number"];
}

Also, while debugging it makes sense to output errors if any :

$q=mysql_query(....) or die(mysql_error());


Failed copy job deletes all users Since the progression of this was apparently somewhat difficult to follow the first time around:

I attempted a completely boring, been-done-a-thousand-times-before copy of a database using the copy database wizard with the detach/reattach method.

The copy failed.  The log indicates that it was unable to execute a CREATE VIEW action for a particular view, because the datasource for the view did not exist.  This is interesting in its own right, as the source most certainly exists, and the view(s) in question are fully functional in the source database.  I'm not really clear, just yet, on how significant this is, as I've yet to figure out precisely why this generated an error.

This resulted in the deletion of all non-system user associations from the source database, leaving me with users dbo, information_schema, sys, and guest.  Non-system roles were also deleted.  Schemas were unaffected.

I have since restored the damaged database from backup.  Academically, however, I would like to know the following:

Why would a failed copy operation strip the user associations from the source database?
Is there any sort of maintenance/rebuild/repair operation that could be performed on the source database to repair it?
The loss of the users was immediately obvious, but given the rather mysterious nature of a failed copy job apparently damaging the source database, how concerned should I be about less obvious effects?  In theory, I would expect restoring from backup would alleviate this concern, but do I have any cause to worry about, e.g., the master database?

This is entirely repeatable.  I've made a handful of copies (manually) for the sake of experimenting with this particular issue, and in each case, the failed copy job obliterates the users and roles from the source database.

Removing the views that generated errors allows the copy to complete, and, as one would expect, produces a copy with identical data, users, etc., in addition to leaving the source database unaltered.

If it's important, I've tried rebuilding the indexes of the system databases, as well as the damaged database, to no appreciable effect.

The error generated:

1:00:25 PM,5/28/2013 1:00:25 PM,0,0x,ERROR : errorCode=-1073548784 description=Executing the query "CREATE VIEW [Sourcing].[PermittedArrProducts]
AS
SELECT     dbo.tblArrProducts.ArrProductID, dbo.tblArrProducts.ArrangementID, dbo.tblArrProducts.ContainerTypeID, dbo.tblArrProducts.Quantity
FROM         Sourcing.PermittedArrangements INNER JOIN
                      dbo.tblArrProducts ON Sourcing.PermittedArrangements.ArrangementID = dbo.tblArrProducts.ArrangementID

" failed with the following error: "Invalid object name 'Sourcing.PermittedArrangements'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

So, after Mark's question above, I did some more searching around with much more generic terms (essentially ignoring the loss of the users and just focusing on the fact that the copy job was failing), and came across this thread, this thread, and this thread all of which seem to pretty closely match what I'm experiencing.  This lead me to the observation that, while the views functioned in use, their dependencies were no longer marked, which was what caused the copy job to fail.

While I'm still a bit curious as to why a failed copy job would wipe out the users in the source database (and why it seems "stuck" in SMO mode), I think this should more or less answer my question.  Thanks everyone.


Moving a hard disk with PostgreSQL data folder to another server I'm running a NAS server with an SD card mounted as / and RAID-1 storage mounted under /media/hdd.  /etc folder is on SD card.  PostgreSQL data folder is on the RAID-1 disk.

/-+
  +- etc
  |  +- postgres
  |     +- 10
  |        +- mycluster
  |
  +- media
     |
     +- hdd
        +- pgsql
           +- data
              +- mycluster

If I want to move RAID-1 disks to a different box, what is the correct way to re-create a PostgreSQL cluster using exiting data folder?  Let's assume I stopped the cluster, shutdown the system, took out hard disks and attached them to another box.  Can I create a new cluster on that box using the existing data folder on the attached hard disks?

Thank you in advance.

Yes, that is no problem, as long as

you are using the same major version of PostgreSQL on the new machine
PostgreSQL was built in the same way
both machines have the same architecture and operating system version (the version is relevant because it might affect the collations, changing versions requires a REINDEX)

Running PostgreSQL on a NAS file system is dangerous, because many NAS implementations are not reliable.


postgreSQL 9.3 streaming replication delay I'm running a fairly simple WAL streaming replication setup between two postgreSQL 9.3 servers that are on a private 1Gb LAN, but recently I've noticed that there is an increasing amount of replication delay:

# SELECT extract(epoch from now() - pg_last_xact_replay_timestamp()) AS slave_lag;
slave_lag
-----------
5.50896

The servers aren't particularly busy, so I was wondering what I could do to investigate this problem or possibly fix it.

Have you tried looking at:

SELECT
     CASE
          WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0
          ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())::INTEGER
     END
AS replication_lag;

The idea here is if both servers are synchronized, then there is no delay.  If they are not synchronized, then display the delay.

One potential issue here is if PostgreSQL replication stops, this won't work properly, so you'd need to check periodically by some other mechanism to see if replication is running.


Is there a way remove SQL Server listener IP on maintenance Is there a any way to stop listening listener on the SQL Server on production server. I want to be sure nobody is trying to connect my production server when it is maintenance mode.

I disabled all users when they have permissions to connect any database. I am sure nobodys can connect any database on that server. But i don't want to be busy my network traffic from any services or application or user while it is on maintenance. So i want to delete Listeners IP to that server.

My possible actions are listed

I can delete listener on SQL Server or Listener resource on WSFC.
I can delete Ip of listener on    the listener on SQL Server.

better approach than deactivating all listeners is to set your instance in single user mode. Single user mode is recommended for maintenance work and dont allow normal users to connect to the instance. you do not need to change any listeners and permissions on your database. Check this msdn page.


Single address table for company, company_branch, users I'm a database noob and i'm asking this simple question because i found no answer to my question. I'm using MySQL.
I have 3 tables Company, Company Branches, Users and these 3 tables have similar fields like address. So i want to store address information of company, company branch and user in a separate table "address_information".
Can i achieve this?

I would suggest something like this.

The tool used to data modeling is SQL Developer Data Modeler


MySql - ERROR 1071 Specified key was too long; max key length is 1000 bytes while restoring dump while trying to restore a MySql Dump, i get the following error:

ERROR 1071 (42000) at line 25: Specified key was too long; max key length is 1000 bytes

any ideas?

You may to have adjust some VARCHAR length in some table  based on the Character Set you are using.
That exact error message is actually posted as a bug report from Nov 2004, when in fact, it is not really a bug. That should direct you on how to adjust key lengths, especially your PRIMARY KEYs.
If you know which table is causing the Error 1071, you have to do the following:
Step 01) mysqldump only database schema
mysqldump --routines --triggers --all-databases --no-data > MySQLSchema.sql

Step 02) mysqldump only data
mysqldump --all-databases --no-create-info > MySQLData.sql

Step 03) Using vi or some other editor, edit the table's PRIMARY KEY to manually limit the PRIMARY KEY in such a way that the key does not exceed 1000 characters.
Step 04) Load the schema
mysql -A < MySQLSchema.sql

Step 05) Load the data
mysql -A < MySQLData.sql

You may have to resort to this if you cannot otherwise figure this out,
UPDATE 2012-01-23 11:43 EDT
Since you mentioned a certain table has the wrong storage engine, here is what you do:
Look above at Step 03. Go find the table in the file. Look for ENGINE=MyISAM in the file and change it to ENGINE=InnoDB. Then, load the schema file first and the data file afterwards.
UPDATE 2012-01-23 11:52 EDT
You should add this to to /etc/my.cnf
[mysqld]
default-storage-engine=InnoDB

then run service mysql restart. If you cannot restart mysql at this time, run this in the mysql client:
mysql> SET GLOBAL default_storage_engine = 'InnoDB';

However, the mysqldump will still have the ENGINE=MyISAM at the end of each CREATE TABLE. You will have to perform the 5-Step plan I mentioned above including changing the ENGINE=MyISAM to ENGINE=InnoDB. DO NOT CHANGE THAT FOR THE mysql DATABASE !!! Change only those tables that are yours.


Newbie question: database for a language school I'm building a database to create lessons for an online language school. I'm not an expert but this is what I came up with. Each lesson will be composed by "blocks" that can be pretty much anything (language presentation, listening, etc) that can contain some media. I would like to know if at a first look it makes sense or there is something that looks out of place. Thank you!

This is a very broad question - you're essentially asking us to comment on an entire system design. Speaking from experience, that's virtually impossible to do on a first pass - you have some tables and you're going to put data in them. Great.

Whether it will "hang together" as a coherent system once you start working with and querying it is another kettle of fish altogether!

In order to help you, I Googled "open source language school software" and the first non-advertisement was this which might either serve as a basis for your own system or you might decide to abandon the dev route and just use it. There are a shedload of other interesting links there. You could also visit this page and search for the word "school" - there's goodies there also!

As I said above, you're asking for a comment on an entire system before it's even been written - this is more of a "I'm developing a system, I have these tables with structures x, y and z and I'm having a problem with doing a, b or c" kind of site.

Your question requires more of a position paper than a simple answer. I know myself from having developed systems that it's above all else an iterative process and that you'll go down many roads which will turn into dead ends for your particular requirements (and that's fine!), but to ask anybody to just cast an eye over a series of table definitions and even be able to comment on how well they'll work is basically impossible!

I suggest you look at the software from the Google and then move from there - if you can find programmes that meet (or come close to meeting) your requirements from the already existing F/LOSS (Free/Libre and Open Source) world, then great! The other great thing about F/LOSS is that you can adopt and adapt. HTH, and welcome to the forum! :-)


Mongo CP, Cassandra AP? I have read lot of articles on net but still confused why  Mongo  CP, Cassandra AP,  RDBMS CA ?
Will explain my understanding and query along with that .
Mongo
Consider a scenario where I have one master  ans two slaves.  consider

Write request arrives and goes to master.
It gets committed on master only but master goes off(crashed) before it is written to slaves
Till master is re-elected, write requests need to wait and system is not available
Once the previous node(node crashed in step 2) comes back, writes pending from that node are written back to slaves. This is called
eventual consistency.

Per my understanding because of step 3 and 4, Mongo is said to be CP where C stands for eventual consistent. Correct ?
Cassandra
Here  there is no master/slave model and every node takes its share write and read request based on shard key.

Write request arrives to any node(called coordination node).
Coordination node redirects to one of the node based on shard key
It gets committed  but node goes off(crashed) before it is written to other replication node.
Again write request with same shard key, now coordinated node redirect it immediately to replica node(replica of crashed node)
Once the previous node(node crashed in step 3) comes back, writes pending from that node are written back to replica node. So cassandra seems
to be eventual consistent too ?

Step 4 explains why cassandra is highly available but step 5 also depicts its eventual consistent. So Per my undsertanding , cassandra provides
provides both eventual consistency ans availability. Then why it is said it does not provide Consitency ?

C stands for eventual consistent. Correct ?

Consistency in the CAP theorem is referring to strong consistency where every read receives the most recent write or an error. By default MongoDB drivers direct all reads & writes to the primary of a replica set, which is strongly consistent.

The CAP theorem asserts that a distributed system must choose between consistency and availability in the event of a network partition. MongoDB's replica set approach uses a single primary for write consistency (CP), while Cassandra's replication strategy favours write availability (AP). Strong consistency is not possible with a network partition because there could be a conflict if both sides of the partition update the same data. To maintain write availability AP database systems need a solution for conflict resolution, which is a separate consideration from eventual consistency.

However, CAP is a simplification of real-world behaviour: MongoDB and Cassandra both have tunable levels of consistency for reads and writes. For example: MongoDB has write concerns to determine the level of acknowledgement required for write operations, read preferences for routing requests to members of a replica set, and read concerns to control the recency, consistency, and isolation properties of data read from replica set and sharded deployments.

Eric Brewer, author of the CAP Theorem, revisited this in 2012 with a more nuanced take: CAP Twelve Years Later: How the "Rules" Have Changed.

  Till master is re-elected, write requests need to wait and system is not available

There are no writes without a primary, but replica sets still have read availability. MongoDB 3.6 added a Retryable Writes feature which helps applications better handle replica set elections and transient network errors.

  Once the previous node(node crashed in step 2) comes back, writes pending from that node are written back to slaves.

If the primary in a MongoDB replica set becomes unavailable, the remaining members of the replica set will elect a new primary if there is an eligible secondary and a quorum of voting members. In your example, the voting majority would be 2/3 members of your replica set. Any writes accepted by a former primary that were not written to a majority of replica set members will be rolled back (saved to disk) so the former primary resumes syncing from a state consistent with the history of the current primary.


Isolation level serializable not working as expected? I have this table

create table testing(
c1 text,
c2 text);

I open two transactions simultaneously. t0 t1.. denote series of time snapshots in increasing order
Transaction 1

BEGIN; -- t0
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- t2
SELECT * FROM testing where c2 = 'rand'; -- t4
INSERT INTO testing VALUES ('rand', 'xyz'); -- t6
COMMIT; -- t8

Transaction 2

BEGIN; -- t1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- t3
SELECT * FROM testing where c1 = 'rand1'; -- t5
INSERT INTO testing VALUES ('rand1', 'abc'); -- t7
COMMIT; -- t9

After t9 I get this error
ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

But if you see the statements, they are operating on difference values rand and rand1. It doesn't make sense for this error to be thrown as they can never end up in a bad state.

Is my understanding correct?

Your understanding is fundamentally correct. What you didn't realize is that there can be false positive results when with the SERIALIZABLE isolation level.

The documentation says:

  While PostgreSQL's Serializable transaction isolation level only allows concurrent transactions to commit if it can prove there is a serial order of execution that would produce the same effect, it doesn't always prevent errors from being raised that would not occur in true serial execution.

In this particular case, I would assume that the problem is that you don't have an index on c1 and c2 or PostgreSQL chooses to do a sequential scan anyway. As you can read further down on the same page:

  A sequential scan will always necessitate a relation-level predicate lock. This can result in an increased rate of serialization failures.


Hint: There is a column named "pma_timestamp" in table "table_a", but it cannot be referenced from this part of the query I want to use a user defined function so that while inserting values in column i want to insert last_day of the month in one column by referencing other column in same table.
my code is-
insert into table_A
(id,pma_timestamp,monthend)
values
(103,'2017-08-15',last_day(pma_timestamp));

but getting the error:

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

how to resolve this

You can't use a column if the target table in the values() clause. You will need to repeat the value you provided for that column:
insert into table_A
(id, pma_timestamp, monthend)
values
(103, '2017-08-15', last_day('2017-08-15'));

If you don't want to repeat the value, you could write an INSERT trigger that populates the monthend column. Then you wouldn't need to provide a value for it at all.

In a relational database, it's usually not a good idea to store data that can easily be derived from existing values. The monthend column is such a derived attribute. I would not store it at all, because calculating it from pma_timestamp can easily be done during a SELECT query. If you don't want to repeat the calculation, create a view that provides that information.


Dividing values from a single column into 2 based on a rule I am trying to create an SQL query to divide into 2 separate columns based on a rule. My table is as follows

Name   | Item ID  | Parent ID
-----  | -------- | ----------
A      | 1        | Null
B      | 2        | Null
C      | 3        | Null
D      | 4        | Null
A1     | 5        | 1
A2     | 6        | 1
A3     | 7        | 1
B1     | 8        | 2
B2     | 9        | 2
B3     | 10       | 2

Is it possible to have the values in the [Name] column be divided into 2 columns?

e.g. if the value of the [Parent ID] is Null, the value in [Name] column be in column 1 and if the value of [Parent ID] is 1, it would be placed in column 2?

Please advice and thanks in advance!

Use a CASE statement in the column list to check if ParentID is 1 or null.

SQL Fiddle

PostgreSQL 9.3 Schema Setup:

create table YourTable
(
    Name varchar(10),
    ItemID int,
    ParentID int
 );

 insert into YourTable(Name, ItemID, ParentID) values('A', 1, null);
 insert into YourTable(Name, ItemID, ParentID) values('B', 2, null);
 insert into YourTable(Name, ItemID, ParentID) values('C', 3, null);
 insert into YourTable(Name, ItemID, ParentID) values('D', 4, null);
 insert into YourTable(Name, ItemID, ParentID) values('A1', 5, 1);
 insert into YourTable(Name, ItemID, ParentID) values('A2', 6, 1);
 insert into YourTable(Name, ItemID, ParentID) values('A3', 7, 1);
 insert into YourTable(Name, ItemID, ParentID) values('B1', 8, 2);
 insert into YourTable(Name, ItemID, ParentID) values('B2', 9, 2);
 insert into YourTable(Name, ItemID, ParentID) values('B3', 10, 2);

Query 1:

select case when ParentID is null then T.Name end as Column1,
       case when ParentID = 1 then T.Name end as Column2
from YourTable as T;

Results:

| column1 | column2 |
|---------|---------|
|       A |  (null) |
|       B |  (null) |
|       C |  (null) |
|       D |  (null) |
|  (null) |      A1 |
|  (null) |      A2 |
|  (null) |      A3 |
|  (null) |  (null) |
|  (null) |  (null) |
|  (null) |  (null) |


Insert new row into table after performing checks in other tables I want to insert a new row in a table only if a value of the tuple I want to insert exists in another table. I want to perform that check for more than one value.

Let me show you an example to better understand what I want to do:

I have a table called personal_transaction. I want to perform the following insert:

INSERT INTO personal_transaction
(id, type, date, fund, paid, caid)
VALUES (3, credit, `2016-12-28`, 30, 2, 4)

Before inserting the tuple into the table, I want to check if 4 (=caid) is a valid key in table called commercial_account and also check if credit is greater than or equal to 30 (=fund) in the table called personal_account where id = 2 (=paid)

How can I do this?

personal_transaction has 2 columns paid and caid which are foreign keys to personal_account (column aid) and commercial_account (column aid).

For better understanding these are the checks I want to do before inserting:

SELECT * FROM personal_account
WHERE aid = 2 AND credit >= 30;

SELECT * FROM commercial_account
WHERE aid = 4;

If those selects both produce a row each as a result then the following query:

INSERT INTO personal_transaction
(type, date, fund, paid, caid)
VALUES
(credit, `2016-12-29`, 30, 2, 4)

is clear to be done, otherwise no insert occurs.

For checking if 4 or ciad is the valid value or not you can define ciad as the foreign key in your personal_transaction table referencing the primary key of the commercial_account table.
Please check the usage here:
https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

Try this:

INSERT INTO personal_transaction (id, type, date, fund, paid, caid)
SELECT 3, credit, `2016-12-28`, 30, 2, 4
where (SELECT credit FROM personal_account WHERE id = 2) >= 30;

I have assumed fund and paid are credit value and id fields respectively in personal_account table please adjust query according to actual fields names and try:

INSERT INTO personal_transaction (id, type, date, fund, paid, caid)
SELECT 3, credit, `2016-12-28`, 30, 2, 4
where (SELECT fund FROM personal_account WHERE paid = 2) >= 30;


Sort a varchar type column (Alphanumeric Values) In my table VARCHAR data type column called CartonNo, that contain either numbers or number with letter it depends on. I want it to be sorted numerically, this would be an acceptable ascending sort order.

Issue No. 1

For example, contains like following data

1-2
1
3-5
6
1-2A
6-12
7-8
9-12D
9-12
17
9-12B
19
20-30
15-18

Now I want to order the data as below

1
1-2
1-2A
3-5
6
6-12
7-8
9-12
9-12B
9-12D
15-18
17
19
20-30

Issue No. 2

the bellow value How to sort alphanumeric?

PT15-8-2

PT15-12-10

PT15-12-3

PT15-12-572-575

PT15-12-31

PT11-96

PT11-133-36

PT11-133

PT16-136

PT8-644

PT09-655

PT9-655

PT12-752

PT14-752

PT13-752

PT14-753

PT10-778

PT13-977-978

PT13-979-988

Can anyone please tell me how should I write the query?

i am assuming before - you would have integer value always

declare @temp table
(val nvarchar(99))
insert into @temp values ('1-2'  )
insert into @temp values ('3-5'  )
insert into @temp values ('1-2A')
insert into @temp values ('6-12')
insert into @temp values ('6-7')
insert into @temp values ('6-12D')
insert into @temp values ('20-30')
insert into @temp values ('15-18')
--insert into @temp values ('3')
--insert into @temp values ('53')

QUERY

select * from @temp
order by CAST(LEFT(val,CHARINDEX('-',val)-1)  AS INT)

Exactly Ordered

select * from @temp
order by CAST(LEFT(val,CHARINDEX('-',val)-1)  AS INT)
,CAST(LEFT(RIGHT(val,LEN(val)-CHARINDEX('-',val)), patindex('%[^0-9]%', RIGHT(val,LEN(val)-CHARINDEX('-',val))+'.') - 1)  AS INT),
RIGHT(val,LEN(val)-CHARINDEX('-',val))

after run second query the output would be

UPDATE AFTER COMMENT: sometime varchar val won't come (3-5) or (1-12A) instead of whole number 3 or 5... at the time the above answer getting an error... how to correct way to sort.

-- DECLARE TABLE
DECLARE @temp TABLE
(val nvarchar(99))

--INSERT INTO TABLE
INSERT INTO @temp VALUES
('1-2'), ('1'), ('3-5'), ('6'), ('1-2A'), ('6-12'), ('7-8'), ('9-12D'), ('9-12'), ('17'), ('9-12B'), ('19'), ('20-30'), ('15-18')

-- QUERY
SELECT * FROM @temp
ORDER BY
CASE
    WHEN ISNUMERIC(val) = 0 THEN CAST(LEFT(val,CHARINDEX('-',val)-1)  AS INT)
    WHEN ISNUMERIC(val) = 0 THEN CAST(LEFT(RIGHT(val,LEN(val)-CHARINDEX('-',val)), patindex('%[^0-9]%', RIGHT(val,LEN(val)-CHARINDEX('-',val))+'.') - 1)  AS INT)
    ELSE val
    END ,val

RESULT:


Return ALL rows based on a matching date also matching a specific value in another column I'm using SQL Server 2014, exact version: 12.0.5579.0.

I've tried a subquery under a SELECT and WHERE statement and it brings back false results with rows of any row with a 1/1/2019 date. It just takes those dates unattached to its row ID and type and applies it to the outer query.

Example query:

SELECT A.ID, A.date1, A.type
FROM table1 A
WHERE A.ID IN (SELECT B.ID FROM table1 B WHERE B.type='blue')
AND A.date1 IN (SELECT B.date1 FROM table1 B WHERE B.type='blue')
AND type <> 'blue'

Example table:

I just want the other rows that match the same date and ID when there's a blue type.

Correct result would be:

Incorrect result:

It falsely included a row with ID 102 since 102 also has a row with the same date (1/1/2019) as the others, but not blue. If 102 had a blue type on 1/1/2019 then the date could match the green type row returned, but it doesn't.

What sort of subquery should I be doing? Seems simple at first but I've gotten myself into a mess! Can't figure out how to return row values that match values in two different columns together.

try this

create table #colors (
   ID       int,
   date     date,
   type     varchar(10)
)

insert into #colors values (100, '1/1/2019', 'red')
insert into #colors values (100, '1/1/2019', 'blue')
insert into #colors values (101, '1/1/2019', 'blue')
insert into #colors values (101, '1/5/2019', 'blue')
insert into #colors values (101, '1/1/2019', 'green')
insert into #colors values (101, '1/9/2019', 'green')
insert into #colors values (102, '1/5/2019', 'blue')
insert into #colors values (102, '1/2/2019', 'green')
insert into #colors values (103, '1/1/2019', 'blue')
insert into #colors values (103, '1/9/2019', 'green')

select c.*
  from #colors c
  join #colors c1
    on c.id = c1.id
   and c1.type = 'blue'
   and c.date = c1.date
 where c.type <> 'blue'


how to create database(1 CDB and 2 PDB) by dbca silent mode with response file, oracle 12c I want create a new database with 1 CDB and 2 PDB, so i write response file and use dbca tool with silent mode. But it must Manual input PDBADMIN password through terminal and only create 1 CDB and 0 PDB. Here is my response file sample:

[GENERAL]
RESPONSEFILE_VERSION=12.1.0
OPERATION_TYPE=createDatabase
[CREATEDATABASE]
GDBNAME=orcl1
SID=orcl1
CREATEASCONTAINERDATABASE=true
NUMBEROFPDBS=2
PDBNAME=pdb1/pdb2
TEMPLATENAME=General_Purpose.dbc
SYSPASSWORD=Welcome1
SYSTEMPASSWORD=Welcome1
EMCONFIGURATION=NONE
DATABASETYPE=MULTIPURPOSE
LISTENERS=LISTENER

anybody knows how can fix it?

I think you need to add a 'pdbAdminPassword' parameter to your response file.

The following worked for me:

[oracle@ora12c66 CDBORCL]$ dbca -silent -createPluggableDatabase -sourceDB cdborcl  -pdbName pdb4 -createPDBFrom default  -pdbAdminUserName pdb4  -pdbAdminPassword qwe123 -createUserTableSpace true -sampleSchema true -TEMPLATENAME General_Purpose.dbc
Creating Pluggable Database
4% complete
12% complete
21% complete
38% complete
85% complete
Completing Pluggable Database Creation
100% complete
Look at the log file "/u01/cfgtoollogs/dbca/cdborcl/pdb4/cdborcl0.log" for further details.


Finding indexes that aren't used often or at all Is there a facility or a recommended practice for determining which indexes are being used and which ones can be "purged"? At the minimum, I'm looking for a log file that over time can be analyzed for which indexes were utilized. From there I can begin researching indexes that were never hit for possible candidates for a clean up.

I'm specifically not interested in running "explain" across our space of queries.

You should enable the user_statistics plugin. After having this active for a while, check out the INDEX_STATISTICS table in the information_schema. The table shows which indexes have been used since last time MariaDB was started, so if they're not listed here then they're good candidates for being purged.

Also use pt-duplicate-key-checker to find duplicated indexes.

All this is nicely written up by Federico Razzoli in Dropping useless MySQL indexes.


Why would you put Identity Increment other than 1 in SQL? In SQL Server, you can set the increment of an identity column other than 1.

In what situation would you do that?

I have used identity increment other than one, especially in MERGE REPLICATION in different regions.

E.g. We have Publisher in NY and Subscriber in LD involved in Merge replication.

We set Publisher's Identity increment to ODD and LD server's identity increment to EVEN. This way you dont get conflicts where MERGE agent runs and tries to merge the changes.

I have talked here with a script that helps determine identity.


Need some assistance on MySQL Sub query I have a table in MySQL.

The table structure is as follows.

id   |  emp_id  |  call_start  |  call_end  |  call_type
--------------------------------------------------------
1    | 20043    | 20:20:10     | 20:21:19   | SALES
2    | 20043    | 20:25:34     | 20:27:12   | NON-SALES
3    | 20044    | 21:25:34     | 21:27:12   | SALES
4    | 20044    | 22:25:34     | 22:27:12   | SALES
5    | 20044    | 22:45:34     | 22:47:12   | NON-SALES
6    | 20043    | 21:25:34     | 21:27:12   | NON-SALES

I want to select data as total number of calls by an employee, total sales call by an employee.

What will be the appropriate query?

I have written something but result is not correct :

SELECT
  emp_id,
  count(*) total,
  (SELECT COUNT(*) AS TOTAL_CALL FROM Dash_Agent_Disp) total_calls,call_type
FROM
  `Dash_Agent_Disp`
where
  call_type like 'SALES'
GROUP BY
  employee_id

The out put of the query is :

emp_id  |  total  | total_calls  |  call_type
----------------------------------------------
20043   |   1     |   6          |  SALES
20044   |   2     |   6          |  SALES

My Expectation is to get :

emp_id  |  total  | total_calls  |  call_type
----------------------------------------------
20043   |   1     |   3          |  SALES
20044   |   2     |   3          |  SALES

You need to filter you subquery with call_type='SALES', then run:

I use your information:

INSERT INTO test.Dash_Agent_Disp
    (id,emp_id,call_start,call_end,call_type)
VALUES
    ('1','20043','20:20:10','20:21:19','SALES'),
    ('2','20043','20:25:34','20:27:12','NON-SALES'),
    ('3','20044','21:25:34','21:27:12','SALES'),
    ('4','20044','22:25:34','22:27:12','SALES'),
    ('5','20044','22:45:34','22:47:12','NON-SALES'),
    ('6','20043','21:25:34','21:27:12','NON-SALES');

Query:

SELECT
    d.emp_id,
    count(d.id) total,
    (SELECT COUNT(*) FROM Dash_Agent_Disp WHERE call_type='SALES') as total_calls,
    d.call_type
FROM Dash_Agent_Disp as d
WHERE d.call_type='SALES'
GROUP BY
    d.emp_id,
    d.call_type;

Result:

mysql> SELECT  d.emp_id,  count(d.id) total,  (SELECT COUNT(*) FROM Dash_Agent_Disp WHERE call_type='SALES') as total_calls, d.call_type FROM Dash_Agent_Disp as d WHERE d.call_type='SALES' GROUP BY  d.emp_id, d.call_type;
+--------+-------+-------------+-----------+
| emp_id | total | total_calls | call_type |
+--------+-------+-------------+-----------+
|  20043 |     1 |           3 | SALES     |
|  20044 |     2 |           3 | SALES     |
+--------+-------+-------------+-----------+
2 rows in set (0.00 sec)

mysql>


Derby slows down after 1.2 million records I'm using a Derby database, mass insertions slow down to 1/4 to 1/6 the speed once there are 1.2 million records (about 6 GB database size, one major table, other tables are tiny). Is that normal?  Is there something I can do to tweak it and make it run faster?  I compressed the main table.
Questions

Should I consider other databases?
How could I compare Derby's performance, with, say, MySQL in this kind of scenario?
Are there such comparisons already?

(Running on Windows 7-based server R2, 16GB RAM, 8-core machine).

DISCLAIMER : Not a Derby Expert

DERBY

There are options you can set to increase data pages for Derby

For a mass load of table prodtable, you may want to consider

create temptable just like prodtable, but with no indexes
load data into the temptable
rebuild indexes on temptable
rename prodtable to zaptable
rename temptable to prodtable
drop zaptable

MySQL

If you switch over to MySQL, you could do wonderful things to performance tune data processing

Start with using only InnoDB
You can configure InnoDB to

hyperthread read threads
hyperthread write threads
access multiple CPUs/Cores
setup partitions buffer pools

Here are some of my past posts on configuring InnoDB in MySQL for multicore performance

Is the CPU performance relevant for a database server? (Apr 26, 2012)
Multi cores and MySQL Performance (Sep 20, 2011)
Possible to make MySQL use more than one core? (Sep 12, 2011)
How do you tune MySQL for a heavy InnoDB workload? (Feb 12, 2011)

While I may be a little biased towards MySQL, make sure you have Derby's caching configured to accommodate mass INSERTs. That way, you need not perform a major data migration if Derby can be made to handle large transactions.


Can queries containing deterministic user defined functions be cached in mysql query cache? According to the mysql 5.6 docs:

  A query also is not cached under these conditions:
  ...
  It refers to user-defined functions (UDFs) or stored functions.

However, I have seen claims that if the UDF is completely deterministic, the query can in fact be cached.  Can someone confirm or deny that?  If indeed UDFs do prevent caching, is there any smarter way to allow the queries to be cached than to simply inline the UDF into each query?  (That would work, but would obviously be cumbersome.)

Edit: Also, is the behavior different for stored functions vs user-defined functions written in C?

Based on some experimentation with MySQL 5.5.33, the answer is "no".  I have a function definition which is deterministic (it's a simple date calculation) and is marked as DETERMINISTIC.  If I use it in a query, the query is not cached.  If I replace the call by its definition in terms of the built-in date_sub() function, the query is cached.


Testing postgresql 9.4 streaming replication I have setup the replication as per https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps.

And I see the slave is in streaming replication mode.

But I do have a question that, I have a bunch of databases in my master. Now should I create the same databases/tables and have the existing data in master, in slave, before testing to ensure my replication works?

Any suggestion highly appreciated. Thanks.

Simply put, if the slave can be started up, both contents of master and slave are perfectly same.

I'll explain the details.
The slave is restored from the base backup which is the physical database back up of the master by replaying WAL log (transaction log).  So, the slave is a perfect replica of master and the difference between master and slave never occurs if slave can be ran.  In other words, if there are any differences between master and slave, the slave cannot be ran.

The simplest test is to create a small table at the master and to confirm the table at the slave, which is mentioned at that document.

Master:

postgres=# create table tbl (data text); insert into tbl values ('test');

Slave:

postgres=# select * from tbl;

If you can find the new table at the slave, the databases of slave is a perfect replica of master data.

Additionally,  this is a very rare case,  but the piece of  data of slave may be corrupted  by a failure of the copy tool.
If you want to avoid this failure, you should do VACUUM FULL statement for all tables at the master server.
If you do VACUUM FULL, all tables will be reconstructed at the slave as well as the master. Therefore, the corrupted data of the slave will be restored. (It's a dirty trick.)


SQL Server 2016 - unexpected privileged time bursts MS SQL 2016 SP2 CU8 + Always On.

1 CLR module (occasional usage, no correlation with the issue)

I'm looking for methods to find the reason for such behavior. DBCC FREEPROCCACHE solves. But after approx. 10 such "solving" attempts I have to initiate failover. If not I may get an outage with stopping of query processing at all.

I tried to remove from the cache not all plans but some. Still have no stable progress here.

So, does anybody know a way to analyze privileged time consumption? And - yes - privileged time is consumed by the sqlserver service, confirmed.

The current workaround is running FREEPROCCACHE in case privileged time is greater when 7% at least for the 10 seconds and weekly manual failover. But this approach is not good enough for me.

In response to some requests for additional information:

The privileged time is not correlated with IO.  Actually, I tried to find any correlation including perf counters, waits and spinlocks but found nothing.
That server has columnstore only in the "tech" DB with occasional usage patterns. I.e. we can count "no columnstores in the active usage".
The server is a physical machine, not a VM. >
"What are you actually trying to solve here?" When the problem occurs, I may get an outage with stopping of query processing at all.

For "end users" this looks like server stopped to process their queries.

Disk drivers are up to date
Disk and Network IO are OK and I see no correlation with the issue

Here are assorted perfmon counters at the beginning of a typical reproduction of this problem:

And the same counters at the end of the repro:

From an article on the internet:

(emphasis mine)

  The Processor(_total)\ % Privileged Time counter shows the percent of time that the processor is spent executing in Kernel (or Privileged) mode. Privileged mode includes services interrupts inside Interrupt Service Routines (ISRs), executing Deferred Procedure Calls (DPCs), Device Driver calls and other kernel-mode functions of the Windows® Operating System.

  Most of the time a processor should be executing User mode operations, a high % privileged time might indicate a poorly written device driver or a faulty piece of hardware.

Reference: Windows Performance Counters Explained (AppAdminTools)

You might want to have a look at the DLL executing in the context of the CLR. It may not be releasing all its resources.

Equally interesting the information from Microsoft | SQL Docs:

(emphasis mine)

  Processor: % Privileged Time

  Corresponds to the percentage of time the processor spends on execution of Microsoft Windows kernel commands, such as processing of SQL Server I//O requests. If this counter is consistently high when the Physical Disk counters are high, consider installing a faster or more efficient disk subsystem.

    Note

    Different disk controllers and drivers use different amounts of kernel processing time. Efficient controllers and drivers use less privileged time, leaving more processing time available for user applications, increasing overall throughput.

Reference: [Monitor CPU Usage][] (Microsoft | SQL Docs)

Ensure your disk drivers are up to date
Ensure your disk I/O is ok
Remove any other services/applications from the SQL Server (instances)


Azure SQL - is it possible to assign CREATE PROCEDURE permission for the master database? I am trying to add a stored procedure to the master database (I deliberately am trying this for the master db) on Azure SQL. Is this possible? I suspect the answer is "no", but I'd like to see some confirming documentation on it if that is the case.
What I've tried so far:
Here's the procedure (just as a test):
CREATE PROCEDURE dbo.blah AS RETURN 0;

I've tried the above using the admin login, and it failed:
Msg 262, Level 14, State 18, Procedure blah, Line 2 [Batch Start Line 21]
CREATE PROCEDURE permission denied in database 'master'.

I then tried to create another login and user and then assigned them to the "dbmanager" role and tried the create procedure again - same failure.

Not if you are using Azure DB.  Probably if you use Azure Instance.
https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server#security


Oldest unsent transaction alert for Availability Group In SQL Server 2008 when using database mirroring, it was possible to set up alerts when the oldest unsent transaction exceeded a certain threshold.  We could check the state of any database mirroring using the Launch Database Mirroring Monitor menu command.

In SQL Server 2012 we now have Availability Groups.  It would seem to me that the same potential problem exists that if transactions cannot be sent to the secondary server then the mirroring will be suspended, and potentially the transaction logs will fill up all our disk space.  However, the Database Mirroring Monitor tool does not seem to recognise databases in Availability Groups as being mirrored.

Is there a way to set up an alert if the oldest unsent transaction exceeds a certain value?  Is there a way to set that certain value?

Very interesting question ..
Based on my understanding, I will try to answer this.
Other experts can correct me wherever I am wrong.

AlwaysON is different from Database Mirroring especially when sending the log blocks to the secondary replica/s.
The difference is how the log block gets send to the mirrored instance (for mirroring) or replica (for AlwaysON).
For Database Mirroring

it flushes the log block to the disk and once completed locally, it sends the log block to the secondary server.

For AlwaysON

it flushes the log blocks in parallel to the secondary using a secondary log scanner on primary.
The beauty is that the secondary could have hardened log blocks even before the primary I/O completes which increases performance and narrows the NOT IN SYNC window.
Now the interesting part is that to monitor above scenario, there are progress messages. The secondary sends messages to the primary indicating the hardened LSN level. The primary uses that info to help determine synchronization state.

So, there is no alert mechanism like Mirroring to actually be able to know the oldest unsent transaction. The only way to know is using DMVs. and possibly Extended Events.
As a side note, you can use Policy Based Management to monitor your AlwaysON health.
Below is the query that I am using to monitor my AlwaysON environment :
/************************************************************************************************
Author      :   Kin Shah
Purpose     :   Find "How far is secondary behind primary"

                Written for DBA.STACKEXCHANGE.COM
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Disclaimer  :   Any scripts found on internet you must irst
                    - understand what it is doing
                    - then test it if it suits your requirements
                I am not responsible for any data loss or any blue screen that you might get.
                     ^^^
                *** USE THIS AS PER YOUR OWN RISK ****
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
*************************************************************************************************/

SELECT AGS.NAME AS AGGroupName
    ,AR.replica_server_name AS InstanceName
    ,HARS.role_desc
    ,Db_name(DRS.database_id) AS DBName
    ,DRS.database_id
    ,is_ag_replica_local = CASE
        WHEN DRS.is_local = 1
            THEN N'LOCAL'
        ELSE 'REMOTE'
        END
    ,AR.availability_mode_desc AS SyncMode
    ,DRS.synchronization_state_desc AS SyncState
    ,DRS.last_hardened_lsn
    ,DRS.end_of_log_lsn
    ,DRS.last_redone_lsn
    ,DRS.last_hardened_time
    ,DRS.last_redone_time
    ,DRS.log_send_queue_size
    ,DRS.redo_queue_size AS 'Redo_Queue_Size(KB)'
    /*
    if the last_hardened_lsn from the primary server == last_hardened_lsn from secondary server
    then there is NO LATENCY
    */
    ,DATEDIFF(SECOND, DRS.last_redone_time, DRS.last_hardened_time)
FROM sys.dm_hadr_database_replica_states DRS
LEFT JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id
LEFT JOIN sys.availability_groups AGS ON AR.group_id = AGS.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
    AND AR.replica_id = HARS.replica_id
ORDER BY Db_name(DRS.database_id)
    ,is_ag_replica_local

References :

How It Works: Always On–When Is My Secondary Failover Ready?
AlwaysOn Availability Groups Dynamic Management Views and Functions


What factors do we need to consider in order to enable auto-growth settings for my PROD database I want to know the factors that we need to consider in case we need to enable auto-growth settings for my PROD database.

I have already checked the database growth history and the database is growing 1 to 1.5 GB daily on a daily basis(on an average).

Current Database size is 600 GB. Also, I have checked the available free space and we have sufficient space for the files to growth. However, if I set auto growth now to 1 GB, then this space will get filled up after some time.

Please suggest me some more factors that should be taken into account so that I can utilize this available free space effectively and thus eliminate the need for new disk for at-least 10-12 months.

You've made an impressive start, here, starting to gather raw data that you can monetise and present to the business.  "This much data, this much cash each year".

To make plans about data usage, you need to have an understanding of what's being stored in the database and, more importantly, how long your business should retain it (or, indeed, how long they're allowed to keep it).

All too often, business people just want to keep everything.  "Disk space is cheap".

That's fine, as long as they're prepared to put up the money for the extra disk space involved and for the overhead of [your] maintaining the table structures with things like Partitioning, to keep their Applications performing well.

These days, however, there are additional, regulatory restrictions on how long data can be kept for and when it must be deleted at a User's request and, again, your business needs to come up with mechanisms for getting rid of data that they're no longer entitled to have or have been asked to delete.

It's not something you can do on your own; you need to be working with your business to come up with the strategy together.


metadata could not be determined because stored procedure contains dynamic SQL I have a stored procedure that contains some dynamic SQL. I am trying to execute the stored procedure using OPENROWSET but am getting the error:

Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'EXEC sp_executesql @SQL, N'@row_count_out INT OUTPUT', @row_count_out = @row_count_table OUTPUT;' in procedure 'nachoTest'  contains dynamic SQL.  Consider using the WITH RESULT SETS clause to explicitly describe the result set.

My real stored procedure is a bit more complex but this is a working example of the error:
The stored procedure:
CREATE PROCEDURE nachoTest  @table_name NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @row_count_table INT
    DECLARE @SQL NVARCHAR(MAX)

    SET @SQL = N'SELECT @row_count_out = COUNT(*) FROM ' + @table_name
    EXEC sp_executesql @SQL, N'@row_count_out INT OUTPUT', @row_count_out = @row_count_table OUTPUT;

    SELECT @row_count_table AS row_count, @table_name AS table_name
END
GO

Calling it with OPENROWSET
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'EXEC dbo.nachoTest ''e_MFA_All_Tokens''')

I have read a bit online but cannot figure out how to use WITH RESULT SETS to fix the code. Ideally I'd like to fix the stored procedure but if I have to change the OPENROWSET call then that is okay too.

The problem is not in your proc but in OPENROWSET/OPENQUERY.
Prior to SQL Server 2012 to figure out the resultset metadata set fmtonly was used. Starting with 2012 it was replaced by sp_describe_first_result_set that causes this error.

here how you can describe your resultset:

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'EXEC dbo.nachoTest ''e_MFA_All_Tokens'' WITH RESULT SETS
(
  (
    cnt int, name sysname
  )
)')

More on EXECUTE WITH RESULT SETS here: SQL Server 2012 T-SQL at a Glance – EXECUTE WITH RESULT SETS


Oracle not available error with Oracle XE 11.2.0-1.0 I have downloaded Oracle XE 11.2.0-1.0 and installed it on a CentOS 6.3 virtual machine with 1.5GB memory and 3GB of swap.

SYS and SYSTEM set to have password 123456. I have set ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe/ and ORACLE_SID=XE.

I can connect to this database with SQL Developer with the SYSTEM user. But when I want to connect with sqlplus it says:

ERROR.
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0

ps -fe | grep pmon shows this:

oracle    1164    1    0    11:26    ?    00:00:00    xe_pmon_XE

I have logged in as root user and I just type sqlplus with no arguments then it asks for user name and password.

Can anyone help?

I have removed the trailing / from ORACLE_HOME and it solved my problem.


Brent Ozar First Responder Kit filter About once a week the query
sp_Blitz @IgnorePrioritiesAbove = 50, @CheckUserDatabaseObjects = 0
 shows "errors" with DB backups that are very old.

It's correct to show this, but is it possible to filter this error in case of availability group DB's which are on a secondary node and are not backed up (I'm using Ola's scripts to backup the DBs)?

Yes, you can use the @SkipChecks parameters for this. Create a table with columns for:

DatabaseName NVARCHAR(128)
CheckID INT
ServerName NVARCHAR(128)

Then populate it with the list of databases & checks you want skipped. For example, if you want check 52 skipped for all databases, add a row with CheckID 52, and databasename null. If you want check 52 only skipped for the WebSite database, add a row with DatabaseName = WebSite, CheckID = 52.

Then when you run sp_Blitz, populate these parameters:

@SkipChecksDatabase - the database where your SkipChecks table lives, like DBAtools
@SkipChecksSchema - the schema, like dbo
@SkipChecksTable - the name of the SkipChecks table (could be BlitzSkipChecks for example)


Memory utilization for multiple instance (SQL server 2008R2 standard edition) If SQL Server 2008 R2 standard edition is limited to 64 GB of memory, then is it required to restrict the max memory for multiple instances (3) which are running SQL server 2008 R2 standard edition with 256 GB of RAM installed on windows server 2012?

You can leave it to default for all instances because since it is SQL Server 2008 R2 standard edition it will not use more than 64 GB and you ahve total of 256 GB RAM so you are on safer side. Please also note that 64GB limitation is for Database Engine only other components like SSIS,SSAS,SSRS does not falls under this restriction and yes they can also consume good amount of memory.


Convert clustered primary key to non-clustered and use another column for the clustered index I have a large table with a primary key (GUID) that is also the clustered index. There is already a integer sequence based field. So I want to leave the GUID as the PK and make the integer column the clustered index.

I can't figure any way to do this except drop the original constraint and create new PK and and new clustered index. But this takes a long time and from what I gather, rebuilds the table twice, once to go from clustered index to heap, and then heap back to clustered index.

I can't do a table rebuild (create new, migrate data, swap names) since I can't have an outage.

Any ideas?

Version: SQL Server 2008 Service Pack 2, Developer/Enterprise.

I don't think you can perform this operation online, since you can't just move the clustered index from one column to another, and you can't use DROP_EXISTING to reduce the amount of work that has to be done.

However, you can avoid an outage, if you're willing to perform the work.

Create a new table with the new structure
Rename the original table, and create a view with the original name - the view will be  a union of old and new table - performance won't be fantastic but it won't be offline
Create INSTEAD OF triggers on the view to apply DML to old or new table

inserts should just go to new table
updates should try both
deletes should try both

Copy rows, in chunks, from the old table to the new table. Will need SET IDENTITY_INSERT ON and you should perform each "chunk" in its own transaction.
Once the old table contains no rows that don't exist in the new table, in a single transaction:

drop the old table
drop the view
rename the new table


How to create a table in a database on a different server I need to create Table B in database B on SQL Server B by querying Table A in database A on SQL Server A. Table B does not exist in database B. SQL Server A and B are linked. I wonder how could this be done?

Thanks

You can easily achieve this using OPENROWSET.

Connect on SQL Server B;

SELECT *
INTO TABLE_B
FROM OPENROWSET('SQLOLEDB', 'server=ServerA;uid=Login;pwd=Password;database=Database_A',
'set nocount on
SELECT * FROM TABLE_A;
')


Final steps of data center migration Just to preface this, I am just a developer with some DBA experience but not an expert.

We are migrating from one data-center to another.  Basically we are doing this:

Create a full copy of existing database
Use the copy to get the target database up to date of the backup.
Turn on Mirroring from the existing database to the new one.
The web app will continue to use the existing database ( they are being migrated as well. )  So the old web app server and the new one will both point to the old data center.

ok, after step 4 , we are ready to switch the connection string for the database over to the new one.  My concern is... if mirroring is still on and we start to have all read/write operations happen on the target of the mirror... is that going to cause any problems??

ex. say the target of the mirror starts adding rows, will the mirroring catch that the two dbs are not in sync and start removing changes?  or... say a row gets deleted in the target db, is the mirror going to catch that it is not in sync and add that row back?

Basically my question is this:  If database mirroring is on can we start to read/write to the target of the mirror in preparation for turning the target into the primary database.

Below steps will help you :

Create full backup with compression
Ship the compressed backup to the new data center
Configure database mirroring in async mode
When you are ready for failover, change the mirroring mode to sync mode.
Initiate failover to secondary (new data center)
  alter database db_name set partner failover
change the web.configs for your application to the new server.
Follow the steps from my answer - it covers migrating your logins/users/jobs/ssis packages and other post restore steps.

  If database mirroring is on can we start to read/write to the target of the mirror in preparation for turning the target into the primary database.

When you initiate a failover, this is where your downtime is - your application end users will be getting an error and they will be able to reconnect to the new data center database server (provided you have changed the conf setting).

I am assuming that you are using enterprise addition since standard edition of sql server has limitations on database mirroring.

Also, database mirroring is deprecated and you should look into moving to AlwaysON (depending on edition and version of sql server you are using).