All instances upgrade from Command Prompt I want to create a cmd script to upgrade sql servers in the organization(mostly 2014) to 2016. I've read Microsoft documentation
Install SQL Server 2016 from the Command Prompt regarding this issue.
The problem is that there is no option written there to upgrade all server instances like there is in installing updates: Installing Updates from the Command Prompt using /allinstances.
Is there any way to auto upgrade all instances on the server using one command line?

You can first download the SQL server 20XX edition of your needs here.
Then you can download and extract the local content media on your machine.
After that you should see in the downloaded folder the SETUP.EXE file
Then you can open the command prompt and type (By considering your sql standard instance name is MSSQLSERVER):
C:\Path_to_file\SETUP.EXE /QS /IACCEPTSQLSERVERLICENSETERMS /ACTION=UPGRADE /INSTANCENAME=MSSQLSERVER

When you connect to your database, you should see the new version installed
. In my case I upgraded from SQL server 2017 to SQL server 2019. Old Maj version was 14.XX, now the new Maj version is 15.XX. See below.

And there are plenty of cool explanations on this page
EDIT: I think its not possible to upgrade all instances at one with the allinstances parameter, because not allowed (Tried it).
Here is what the command line says:
 ALLINSTANCES                 Specifies that all instances are to be included
                              in the Setup operation. This parameter is
                              supported only when applying a patch.

Bu you can loop with powershell on all your instances with the above command. If you dont have 1 million instances, it should be fine.


How to solve Error 927 'can not open database' while configuring DB mirroring Steps I followed:

Create DB on primary.
Took full backup and log backup.
Restored full and log on secondary in NORECOVERY mode
(now tb shows restoring satus on secondary I guess this is because NORECOVERY mode).
Tried configuring mirror from primary.

I was able to configure it successfully with windows 2012 and SQL server 2012 using SSMS 17.6 but it's giving above error with Win2k16 and SQl server 2016

It seems there are bugs with SSMS (2016) to configure mirroring
https://stackoverflow.com/questions/38008208/sql-server-2016-cant-start-mirror

Try to use TSQL or SSMS 2014

https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-establish-session-windows-authentication

Service accounts running SQL Server in SQL Config manager need to use the same domain user on both primary and secondary.


SQL Server 2012 Very Slow to Consume Memory I have a bit of a conundrum.  I recently upgraded a ~20gb database from Window Server 2008 R2 || SQL Server 2008 R2 to Window Server 2012 || SQL Server 2012. The previous server had 32gb of memory, upon booting the Max Server memory of 28gb would be consumed within one day.  The new server has 48gb of memory however upon booting the database

does not take the minimum memory of 4gb
after a week of running has only consumed 7gb of data

The database is mainly ad-hoc workload that does not utilize stored procs. So the hope in adding memory that was more of query plans would stay cached.

Any idea what could be causing this ?

Edit

dm_os_bufferpool_descriptors:

My first question where are you looking for SQL Server memory consumption. Please don't use task manager or process monitor, these do not show correct value when SQL Server service account has Locked pages in memory privilege.  just use below query to see memory consumed by SQL Server

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

There was notable change in SQL Server memory code in SQL Server 2012 but no where its related to what you are seeing. SQL Server memory allocation is dynamic in nature when SQL Server boots up it would grow its memory notification till the highMemoryResourceNotification resource notification is revoked.SQL Server will keep allocating memory based on its need as long as there is memory available I.e. as long as MEMPHYSICAL_HIGH (HighMemoryResourceNotification )notification is signaled in widows  and will scale down its usage when there is MEMPHYSICAL_LOW (LowMemoryResourceNotification) signaled in windows. When available memory is between the low memory and high memory SQL Server will try to maintain the memory usage stable( RESOURCE_MEM_STEADY) with some exceptions.

With 48G memory you have set max server memory to 44G and only given 4 G to OS. IMO this is not correct you must give at least 6 G OS. You should give enough memory to OS so that it would have enough to allocate otherwise it would start signaling low memory notifications and SQL Server would have to trim its memory consumption.

Does SQL Server service account has Locked pages in memory privilege. If not you should give it, but before doing that please leave 6-7 G for OS( I am assuming here that this is dedicated system for SQL Server)

If you want SQL server to consume more memory create a dummy table and then start inserting large number of records in it at same time start running multiple Select * statements you would see SQL Server memory consumption rising. If you want to check SQL Server memory consumed by SQL Server use query posted above

Start by looking at the min server memory and max server memory configuration options detailed at http://msdn.microsoft.com/en-us/library/ms178067.aspx.

You can pull them up by running:

exec sp_configure 'min server memory'
exec sp_configure 'max server memory'

Ultimately, you have to decide if performance is being affected by these settings or not.


Guidelines on best indexing strategy for varying searches on 20+ columns I’m running a search engine for cars. It’s backed by a postgresql 9.3 installation.

Now I’m unsure about the best approach/strategy on doing index optimization for the fronted search.

The problem:

The table containing the cars holds a around 1,5 million rows. People that searches for cars needs different criteria to search by. Some search by brand/model, some by year, some by mileage, some by price and some by special equipment etc. etc. - and often they combine a whole bunch of criteria together. Of cause some, like brand/mode and price, are used more frequently than others. In total we offer: 9 category criteria like brand/model or body type, plus 5 numeric criteria like price or mileage, plus 12 boolean criteria like equipment. Lastly people can order the results by different columns (year, price, mileage and a score we create about the cars). By default we order by our own generated score.

What I’ve done so far:

I have analyzed the usage of the criteria “lightly”, and created a few indexes (10). Among those, are e.g. indexes on price, mileage and a combined index on brand/model. Since we are only interested in showing results for cars which is actually for sale, the indexes are made as partial indexes on a sales state column.

Questions:

How would you go about analyzing and determining what columns should be indexed, and how?
What is the best strategy when optimizing indexes for searches happening on 20 + columns, where the use and the combinations varies a lot? (To just index everything, to index some of the columns, to do combined indexes, to only do single column indexes etc. etc.)
I expect that it does not make sense to index all columns?
I expect it does not make sense to index boolean columns?
Is it better to do a combined index on 5 frequently used columns rather than having individual indexes on each of them?
Would it be a good idea to have all indexes sorted by my default sorting?
Do you have so experiences with other approaches that could greatly improve performance (e.g. forcing indexes to stay in memory etc.)?

Capture the queries that people actually make.
Analyze data from #1; then devise short, multi-column, indexes (2, maybe 3, columns each)
No.  Especially not for yes/no flags.  It's ok to combine such, as in #2.
See #3.
The order of columns is important in an index.  A 5-column index is (roughly) equivalent to the 1-col index on its first column, but is not useful if you don't query on that column.
Probably.
Forcing into memory is counter-productive because it takes away space for caching other stuff.

More discussion:  http://mysql.rjweb.org/doc.php/eav


SQL Server 2005 and Windows 8.1 - Getting error 1053 when trying to start sqlexpress service We've been running SQL Server 2005 and Windows 8.1. Today for the first time we can't get sqlexpress running in services. It was running fine yesterday.

Getting

  error 1053 - service did not respond to the start or control request.

What steps should I take to troubleshoot this?  Thanks.

SQL Server Express does not have SQL Server Agent, so it won't start, no matter how hard you try.

Which service does not start? SQL Server or SQL Server Agent? For SQL Server, please refer to this great article by Gail Shaw.


Moving DataFile to Another Location ORACLE I'm having some problems moving DATAFILE from one location to another due to the shortage of storage space. I've managed to change the path file of control file via command(linux). Now I would like to change the path file of the datafile and logs to another since I've already manual moved the folder of the database.
 SQL> startup;
 ORACLE instance started.

 Total System Global Area 6710886400 bytes
 Fixed Size                  2939560 bytes
 Variable Size            3506439512 bytes
 Database Buffers         3187671040 bytes
 Redo Buffers               13836288 bytes
 Database mounted.
 ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
 ORA-01110: data file 1: '/app/oracle/oradata/ENOVIA/system01.dbf'

Apparently, the data files have already been moved to another location, whereas the path of the files has yet to be changed.
How can I check for the current location of Database in SQL and which command is needed to be used to alter the path file?
The new location is home/oradata/ENOVIA.

The SQL statement required to list the database files locations would be something like:
set lines 230
set pages 50
column file_name format a50
select ddf.FILE_NAME, ddf.TABLESPACE_NAME, ddf.STATUS, ddf.ONLINE_STATUS from dba_data_files ddf;

This will return something like the following:
|              FILE_NAME                  | TABLESPACE_NAME |  STATUS   | ONLINE_STATUS |
|-----------------------------------------|-----------------|-----------|---------------|
| /app/oracle/oradata/ENOVIA/SYSTEM01.DBF | SYSTEM          | AVAILABLE | SYSTEM        |
| /app/oracle/oradata/ENOVIA/SYSAUX01.DBF | SYSAUX          | AVAILABLE | ONLINE        |
| /app/oracle/oradata/ENOVIA/UNDOTS01.DBF | UNDOTS          | AVAILABLE | ONLINE        |
| /app/oracle/oradata/ENOVIA/USERS01.DBF  | USERS           | AVAILABLE | ONLINE        |

Once you have found the contradicting entry, you can modify it with the following SQL statement:
ALTER DATABASE
    RENAME FILE '/app/oracle/oradata/ENOVIA/system01.dbf'
             TO '/home/oradata/ENOVIA/system01.dbf'

Reference: Renaming and Relocating Datafiles (Oracle | Docs)

Note:
To rename or relocate datafiles of the SYSTEM tablespace, the default temporary tablespace, or the active undo tablespace you must use this ALTER DATABASE method because you cannot take these tablespaces offline.

Good luck.

Look at the alter database rename file command; this should update the location of the file in the control file.


Bad bind variable in audit trail trigger in Oracle 11g R2 I have been trying to construct audit trail triggers and procedures in oracle and I am facing a problem with bind variables. Following is the code

create or replace trigger trg_audit_trail
for insert or update or delete
on tbl_mst_books
referencing NEW AS New OLD AS Old
for each row
declare
  colName VARCHAR2(50);
  cursor audit_cur is
   select column_name from all_tab_columns where table_name = 'tbl_mst_books';
begin
  for rec_audit in audit_cur loop
   colName := rec_audit.column_name;
   if inserting then
    if (inserting(colName)) then
     prc_audit_trail('tbl_mst_books',colName,:new.colName);    /*stored procedure call
              to insert audit trail data from multiple triggers to audit trail table */
    end if;
   end if;
 end loop;
end trg_audit_trail;

The stored procedure prc_audit_trail is now inserting the values passed as parameters into audit trail tables.
The problem I am having is with the bind variable :new. I am getting an error of bad bind variable. Apparently I am not being able to do :new.colName but instead of colName if I directly write the name of the column(eg: BOOK_NAME or BOOK_AUTHOR) it's not giving any error. Since I have to fetch the column name of the affected column I am fetching the column name to a variable and trying to do :new.colName. How can I resolve the issue?? Please help me. Also can I get column names of the affected columns(during insert, update and delete) in some other way?
Why am i getting the bad bind variable error for :new.colName? How can I resolve it??

P.S. I have just shown the portion for IF INSERTING but I am following the same process for IF UPDATING in the same trigger where I am getting the same error also for :old.colName

Thank you.

If you write :new.colName than you are referencing the new value of a column named "COLNAME" of table tbl_mst_books. But table tbl_mst_books has no column named "COLNAME". Actually there is no possibility to do this this way because dynamic sql does not work with :new and :old bind values. You have to program it explicitly for every column in your table and have to change your code if your table structure changes.

see AskTom

Try removing the ':' because new is not a bind variable.


Availability Group database stuck in Not Synchronizing / Recovery Pending mode While upgrading the storage in a SQL Server 2014 SP1 (12.0.4422.0) instance we ran in to an issue where two of the databases would not start on the secondary after restarting SQL Server. The server had been offline for a few hours while we installed new (larger) SSDs and copied the data files over to the new volume. When we restarted SQL Server all but two of the databases started synchronizing again. The other two were displayed in SSMS as Not Synchronizing / Recovery Pending.

Having had a similar Not Synchronizing / In Recovery issue before, I checked the status under the Availability Groups -> Availability Databases section but they displayed a red X:

and even trying to Suspend Data Movement generated an error message:

  Failed to suspend data movement in database 'StackExchange.Bycycles.Meta', which resides on the availability replica 'ny-sql03' in the availability group 'SENetwork_AG'. (Microsoft.SqlServer.Smo)

  Additional Information:
  An exception occurred while executing a transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

  Database 'StackExchange.Bycycles.Meta' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details. (Microsoft Sql Server, Error: 945)

I checked and the files existed and did not have any permission issues. I also checked the SQL Server logs in SSMS under Management, but did not see anything about pending recovery or any issues with the two databases.

Searching for help I found two different articles that said the databases would need to be restored.

Is there any way to resume data replication on a secondary when a database is stuck in Recovery Pending?

Since the server had been offline for a while we thought it may have gone outside the recovery window of the primary. We decided to try applying the latest transaction logs on the database to see if that would kick-start the recovery process:

-- Remove database from Availability Group:
Alter Database [StackExchange.Bicycles.Meta] SET HADR OFF;

-- Apply t-logs to catch up. This can be done manually in SSMS or via:
RESTORE LOG [StackExchange.Bicycles.Meta] FROM DISK = '\\ny-back01\backups\SQL\_Trans\SENetwork_AG\StackExchange.Bicycles.Meta\StackExchange.Bicycles.Meta_LOG_20160217_033201.trn' WITH NORECOVERY;

-- Re-join database to availability group
ALTER DATABASE [StackExchange.Bicycles.Meta] SET HADR AVAILABILITY GROUP = [SENetwork_AG];
ALTER DATABASE [StackExchange.Bicycles.Meta] SET HADR RESUME;

Afer running the above on the secondary server for both databases they were able to start synchronizing again.

UPDATE: We had a similar issue where after a Manual AG Failover one of the databases on the new primary replica was stuck in Not Synchronizing mode (switched to Not Synchronizing / Recovery Pending after restarting SQL Server), and the above steps worked to resolve that issue as well.

Next time, try failing over the primary to the 'not synchronizing' secondary and back again.  The secondary should now be synchronized.


Why should I create an ID column when I can use others as key fields? Possible Duplicate:
  Why use an int as a lookup table's primary key?

So far, I'm accustomed to creating an ID column for every table and it is practical in a way that it makes me not think about decision making about primary key theories.

The professor in my university suggested the class to make primary keys from one or more fields which make up to one unique info about each column. And yes, I want to have a habit of applying natural keys instead of surrogate keys. On Wikipedia, the advantages and disadvantages of surrogate keys are listed, I strictly recommend This Article

I have seen people use integer ID fields for everything and nobody judges this method because

it "looks" efficient
a number field is used and it looks cooler because of its size per
row in memory

I am beginning to think an extra ID field is just creating redundant data with no actual benefit. So why should I create an ID column when I can use other columns as key fields?

If your ID field is 32 bits, it's equivalent to 4 ASCII characters
already.
If your Id field is 64 bit integer, it's 8 characters string so it
doesn't actually save that much of  memory (what implied here is the memory used on comparison. extra id column is already adding to the memory used (both HDD and RAM) )
An extra ID field doubles your indexing cost because you will also
    index a unique field you can use as a primary key.
You make extra joins if you need the data you could have used as a key field, for example, if you stored a unique user ID in one
blog post, to show the name of the author, you make a join query, if
your key field was the author's name, you don't need to join because
you store the relevant data in the blog post table. a foreign key
field with meaningful data reduces the need for subquery or join

Creating an extra id field "adds" to the memory load, it's not a
replacement of a unique string field, you are not replacing a
char-varchar field with an integer, you are adding an extra
column and it creates extra data flow. so any comparison of
data store should be done between "string" and "int+string". adding
an integer id field does not save space.

on the other hand

assigning a primary key data which gets value from user input, can be
problematic because people can enter, for say, their social security
number wrong and the actual person who wants to register won't be
able to register because of the unique policy. This can be
circumvented by adding extra digit or digits to the original number.

Extra resources:

Comparison of Natural vc Surrogate keys

My conclusion from reading articles is that I should use natural keys whenever possible instead of skipping thinking about natural keys and using surrogate keys each time, as if it is a standard.

1 - It's faster.  A JOIN on an integer is much quicker than a JOIN on a string field or combination of fields.  It's more efficient to compare integers than strings.

2 - It's simpler.  It's much easier to map relations based on a single numeric field than on a combination of other fields of varying data types.

3 - It's data-independent.  If you match on the ID you don't need to worry about the relation changing.  If you match on a name, what do you do if their name changes (i.e. marriage)?  If you match on an address, what if someone moves?

4 - It's more efficient  If you cluster on an (auto incrementing) int field, you reduce fragmentation and reduce overall size of the data set.  This also simplifies indexes needed to cover your relations.

EDIT

To the specific points you just added:

1 and 2 - It's still much quicker to compare an int than a string, space considerations aside.  You are also conveniently ignoring overhead needed to store length of variable length fields (normally 2 bytes per field per row).

3 - If you cluster on the ID field then it doesn't add anything extra.  It SAVES space since you are using a more efficient row id.

4 - And then when that person changes their username your links all break.

5 - You really don't know what you are talking about here.  You do have to store the data, that's correct, but it's much more efficient to index and JOIN on the int than on a combination of other fields.

Because ID is used to identify everything. Take an user as an example - searching user by their username is slower than by Integer (ID)


Redirecting MySQL errors and slow logs into another database I am running a number of MySQL servers as slaves and masters. It is quite annoying to look for slow logs and errors in every server, so I would like to log all the errors into an external database.

    DB1    DB2    DBn ...
      \      |      /
       \     |     /          <-- error logs and slow logs
        \    |    /
[MySQL error & slow log database]

Is there some simple way of doing it?
Or I do need to write some scripts and stuff?

P.S. I hope you appreciate my superb ASCII diagraming skills

Have you ever considered converting the slow log into a MyISAM table?

As for MySQL 5.1.30, you can choose the format of the slow log and general log as either TABLE or FILE by means of the log_output option. It is also available in MySQL 5.5.

To support the TABLE option for log_output, MySQL provided two tables:

mysql.slow_log
mysql.general_log

For the sake of this question, I will discuss only mysql.slow_log

By default, this is mysql.slow_log:

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.10 sec)

mysql>

The slow_log table uses the CSV storage engine. Please note that a CSV table cannot be indexed. That's not very useful for doing analysis by date and time. GOOD NEWS: You can convert it to a MyISAM table.

In order to facilitate a MyISAM-based slow log, perform the following:

Step01) Make mysql.slow_log a MyISAM table

ALTER TABLE mysql.slow_log ENGINE-MyISAM;

Step02) Index to timestamp column called start_time

ALTER TABLE mysql.slow_log ADD INDEX (start_time);

Step03) Add the following lines to the my.cnf (or my.ini)

[mysqld]
slow-query-log
log-output=TABLE

Step04) Restart MySQL

For Linux

service mysql restart

or for Windows

net stop mysql
net start mysql

Step05) Run the following command in MySQL

mysql> SHOW VARIABLES LIKE 'long_query_time';

By default long_query_time is 10 seconds

Step06) Run this command

mysql> SELECT SLEEP(15);

If long_query_time is 10 seconds, this SELECT should deliberately be placed in mysql.slow_log.

Step07) Run this command

mysql> SELECT sql_text FROM mysql.slow_log;

If you see any row in the table with the SELECT SLEEP(15);, CONGRATULATIONS !!!

You can now run SELECT queries against mysql.slow_log to copy some or all of the rows to other tables or other databases.

I have posted past answers in conjunction with this subject:

Mar 30, 2012 : MySQL slow log configuration
Nov 18, 2011 : Create a custom log table in MySQL
Jun 30, 2011 : How do I output MySQL logs to syslog?

UPDATE 2012-11-12 17:00 EDT

In all fairness, the question did ask about error logs. I did not explicitly include anything about it. I will include some code that addresses how to

Let's say the error log is defined on any DB server as follows:

[mysqld]
log-error=/var/log/mysqld.log

You could create a script like this:

ERROR_LOG=/var/log/mysqld.log
LOG_LINES=/tmp/general-log-lines.txt
ERROR_FOLDER=<folder where to copy logs>
ERROR_TARGET=${ERROR_FOLDER}/`hostname`-DB-Errors.txt
NEWCOUNT=`wc -l < ${ERROR_LOG}`
if [ -f ${LOG_LINES} ]
then
    OLDCOUNT=`cat ${LOG_LINES} | awk '{print $1}'`
    if [ ${OLDCOUNT} -lt ${NEWCOUNT} ]
    then
        (( DIFF = NEWCOUNT - OLDCOUNT ))
        tail -${DIFF} < ${ERROR_LOG} >> ${ERROR_TARGET}
        echo ${NEWCOUNT} > ${LOG_LINES}
    fi
fi

or if you want to collect and reset the error log without a mysql restart (linux only) you can use this (This technique could never be done in Windows because the Windows OS locks the error log file from external writing):

ERROR_LOG=/var/log/mysqld.log
LOG_LINES=/tmp/general-log-lines.txt
ERROR_FOLDER=<folder where to copy logs>
ERROR_TARGET=${ERROR_FOLDER}/`hostname`-DB-Errors.txt
NEWCOUNT=`wc -l < ${ERROR_LOG}`
if [ -f ${LOG_LINES} ]
then
    OLDCOUNT=`cat ${LOG_LINES} | awk '{print $1}'`
    if [ ${OLDCOUNT} -lt ${NEWCOUNT} ]
    then
        (( DIFF = NEWCOUNT - OLDCOUNT ))
        tail -${DIFF} < ${ERROR_LOG} >> ${ERROR_TARGET}
        echo -n > ${ERROR_LOG}
        echo 0 > ${LOG_LINES}
    fi
fi

Place this script on every DB server. Setup a crontab for this script to go off every 5 minutes. Make sure ERROR_FOLDER is set to a common shared folder.

UPDATE 2012-11-12 17:24 EDT

For clarification, running MySQL FLUSH LOGS; is insufficient because...

For all text-based logs, FLUSH LOGS; closes and reopens. No rotation is provided by mysqld
For binary logs, FLUSH LOGS; will...

Close current binary log
Rotate to a new binary log
Open new binary log

For MySQL 5.5, FLUSH ERROR LOGS; closes and reopens the error log. No rotation is provided

This is why some scripting is necessary

The slow logs are just text files, so there's no MySQL way of dealing with them.

The general process is to move/rename the files and then do a MySQL FLUSH, which will tell MySQL to start a new log file.

Then take the moved/renamed file and ship it off to your log server.  Seems like a simple bash script would work.

Hope that helps.


psql asks for password, CREATE USER didn't specify one I used to have a user, say 'jack' in postgresql, and then I forgot what the password. I reassigned all its ownerships to user postgres, removed the user, and then created it again with CREATE USER jack;.

Now if I try to connect with it, psql asks for a password.

$ psql db_name jack
Password for user jack:
psql: fe_sendauth: no password supplied

What is going on and how do I check if a user has a password or not?

A user having a password or not won't affect if you're asked for a password when trying to connect.  That is controlled by the pg_hba.conf file.

If you're being asked for a password that indicates that a password is required.  If you try to connect to an account which doesn't have a password set that will simply fail.  You need to either set a password for the account and use that, use a different type of connection or modify the pg_hba.conf file to use a different authentication method.

If you want to have postgres never ask for a password for a specific user, then supply the -w option during user creation.

createuser -w user

(note that -W (capital w) will always prompt for a password)

By default users made in postgresql with CREATE USER user don't have a password.

http://www.postgresql.org/.../app-createuser.html


SQLite - get every row that has a common entry in the second column I am working on an Android studios project and I have a table that has two columns item and listname.

I want to get all the items that have a common Listname so in this case if I were looking for items that have Market as a listname I only want to get the last 4 from the table, I only want to return the items. I have this but it keeps crashing.
   public Cursor getItem(String listname) {
    SQLiteDatabase db = this.getWritableDatabase();

    String selectQuery = "SELECT  * FROM " + LIST_ITEM_TABLE + " WHERE "
            + ItemListName + " = " + listname;

    Cursor c = db.rawQuery(selectQuery, null);

    return c;
}

String have to be in single quotes
Also i don't see ItemListName  be filled, i hoe there is Listname in it
See like:
String selectQuery = "SELECT  * FROM " + LIST_ITEM_TABLE + " WHERE "
        + ItemListName + " like  '" + listname +"'";


ADD and DROP triggers frequently on a database I have a table from which I am deleting the data. Whenever a row is deleted, the row matching the data from other tables must also be deleted. So I am planning to achieve the same using triggers. The trigger will be only on DELETE condition.

The application from which I am achieving the same runs 4-5 times a day. So, at the start of the application, I am adding the triggers and on exit of application, I am dropping the triggers (because triggers slow down the database performance).

This too much frequent adding and dropping triggers is OK for Postgres or is there any better idea to achieve the same?

There are other applications as well which keep on running and they are using the database. Other applications do only INSERT and UPDATE operations. Only my application does the deletion part. It is a utility for daily cleanups.

You may use

ALTER TABLE tblname ENABLE\DISABLE TRIGGER


Only Return Rows Where Row Count >= 2 Table has duplicate data in it.  I am in need of identifying the data that is duplicated in a structure like such - identifying each storeID that is duplicated by Hambasa

ABC BLUE 2
ABC Green 2
ABC Orange 2
BET Blue 2
BET Green 2
BET Orange 2

This is sample DDL and the query using Row_Number() I attempted.  What would be the proper way to get the result I am after?  I am actually wanting to delete the duplicates, so that each storeID only has 1 entry for each Hambasa.

    Declare @TMI Table (HAMBASA varchar(10), storeid varchar(10))
Insert Into @TMI (storeid, hambasa) VALUES
('ABC', 'Blue'), ('ABC', 'Green'), ('ABC', 'Orange'),
('ABC', 'Blue'), ('ABC', 'Green'), ('ABC','Orange'),
('NYC', 'Blue'), ('NYC', 'Green'), ('NYC', 'Orange'),
('BET', 'Blue'), ('BET', 'Green'), ('BET', 'Orange'),
('BET', 'Blue'), ('BET', 'Green'), ('BET', 'Orange')

;With pan As
(
    Select HAMBASA, storeid
    ,RN = Row_Number() Over (Partition By storeid Order By HAMBASA ASC)
    FROM @TMI
)

Where's the primary key?

DELETE records FROM (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY HAMBASA, storeid ORDER BY HAMBASA) AS HambasaCount
    FROM @TMI) records
WHERE records.HambasaCount > 1

You can write code such as following:

SELECT *
FROM TestTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM TestTable
GROUP BY NameCol)
GO

Refer: https://blog.sqlauthority.com/2012/12/19/sql-server-select-and-delete-duplicate-records-sql-in-sixty-seconds-036-video/


SQL Server 2008 R2 "Ghost Memory"? We have a dedicated SQL Server 2008 R2 machine that is experiencing some strange memory issues.. The machine itself has plenty of resources including two quad-core processors, 16gb of RAM and 64bit Windows Server 2008 R2 Enterprise (it is a Dell PowerEdge 2950).

The strange problem is that the system is reporting 82% of memory in use but sqlservr.exe is only reporting 155mb in use. The reason that I suspect SQL Server is the issue is because if I restart the sqlservr.exe process the memory consumption returns to normal for a period of time.

Does anyone have any ideas on how I can start to track this issue down?

Thanks, Jason

You won't get a true picture of memory usage from Task Manager if the account the service is running under has the lock pages in memory privilege (edit: as per Mark Rasmussen's comment/link). To determine how much memory is being used you can look at:

SQLServer:Memory Manager\Total Server Memory perfmon counter
DMVs

I can't recall if there is a DMV or combination of that will give you the total memory allocation but the following will show the bulk of it.

SELECT TOP(10) [type] AS [Memory Clerk Type], SUM(single_pages_kb) AS [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(single_pages_kb) DESC OPTION (RECOMPILE);

SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

The second is the most interesting usually, buffer pool allocations by database. This is where the lions share will be used and it can be useful to understand which of your databases are the biggest consumers.

The amount of memory used by SQL, as shown in the task manager, will mostly be the max-memory setting. This is how the min/max setting works:

When SQL server starts up, it starts taking memory up to the min-memory setting. As your SQL needs increase, SQL will start using more memory up to the max-memory setting. The memory then stays at this (max) point even when the SQL usage goes down. This gives the impression of SQL performing huge tasks and using up that much memory. In reality, this memory is reserved by SQL.

When there is non-SQL memory pressure on the server, SQL will release memory down to the min-memory setting point. This is how the memory settings are used. You can use Mark's scripts to see how SQL is using this memory.


How to determine if an Oracle table is locked or not? We've been using BI software and a repository database that are installed on Oracle Enterprise 11gR2.

Some of these batch reports will try to access a database table which may still be locked. How can I find out if an Oracle table is locked or not? Is there any SQL statement that displays like history details to analysis?

Following query gives details of all locks.

SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name
FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID

Select the session id and kill the process which is holding the lock on schema user.
select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status,
b.osuser, b.machine from v$locked_object a, v$session b, dba_objects c
where b.sid = a.session_id and a.object_id = c.object_id;

and then
SQL> select inst_id,sid,serial# from gv$session where username='SCOTT';
INST_ID        SID    SERIAL#
---------- ---------- ----------
    1        130        620
SQL>  alter system kill session '130,620,1';
alter system kill session '130,620,1'
*
ERROR at line 1:
ORA-00026: missing or invalid session ID
Now, it works:
SQL>  alter system kill session '130,620,@1';
System altered.


SSRS Azure SQL Database Active Directory - Universal with MFA Does anyone know when support for Active Directory - Universal with MFA will be included in Reporting Services?

It's available in SSMS.

Visual Studio 2017 Data Source Properties

Reporting Server Data Source Properties

The MFA auth flow is interactive.  For me, for instance, it opens a browser window, displays an code, and I get a notification on my phone to approve the login.
So you can't use it in background services like SSRS.

Use OLEDB connector and type the connection string directly:
Provider=MSOLEDBSQL;Data Source=myServer;Initial Catalog=myDatabase;Authentication=ActiveDirectoryInteractive;User ID=myUserName;Use Encryption for Data=true;


Migrate SQL Server database to multiple files (shrinkfile emptyfile vs log shipping) I have a SQL Server 2008 database in production that we are moving onto a new server. The current database has a single ~400GB .MDF file. The new server will be running SQL Server 2012, and we are running mirrored Intel 910 SSDs. These drives will present us with 4x 200GB partitions.

To make this work, we will need to split the single .MDF into 4 smaller ones using DBCC SHRINKFILE with EMPTYFILE. We have done this in test, and it still takes ~ 3.5 hours to do which is too long. The existing database is OLTP, and 365/24/7 and I know blocking will occur during this process, so we can't do it on production first.

My question, is there a way to backup and restore the database to the new server in a temp location. create the new files, EMPTY the temp .MDF into the new locations, then apply transaction logs after? That way we can move the data while current old production is up and running, then do a short shutdown, apply logs, and bring up the new DB?

Or are there any other options to get from Server A with one file and Server B with 4 files on different drives with minimal downtime?

In a word, no.  You have to do it live.

What you'll want to do it add 3 new files.  Then simply start doing index rebuild operations.  As long as you are doing rebuilds not defrags SQL will start spreading the data across all the data files.

I'm assuming that you are running on SQL Enterprise Edition and can do online index rebuilds.

I would set up striping in the operating system or hardware. That will be easier to manage than multiple files in SQL Server.

Can you create a hardware RAID 1+0 across all 8 SSDs? Can you do it in software, using Windows storage?

Otherwise you could create a new database using 4 files then use either the Database Object Copy Wizard or Transactional Replication to move the objects from old database to new database. I'm not sure how the duration of this will compare to the 3½ hours for splitting. Since these methods will lead to split-brain you will have to have some down-time while the objects copy across (the strategy is, disable updates to the old server, allow enough time for all the new data to have replicated to the new server, test data integrity, enable updates on the new server).


Does the order of columns in a covering index matter? I have the following covering indices:

INDEX (col1, col3); -- index 1
INDEX (col1, col2, col3); -- index 2

because I would like to support the following type of queries:

1)

  SELECT col3
    FROM my_table
   WHERE col1 = ... AND
         col2 = ...
ORDER BY col3

2)

  SELECT col3
    FROM my_table
   WHERE col1 = ...
ORDER BY col3

I am unfamiliar with how a covering index works.  Is index 1 redundant? Or does a covering index requires that the columns be side by side?

"Covering" is the wrong term to start with.  I'll get back to that in a minute.

The optimal index for your queries can be summarized:

The first columns in the index must be all the = columns in the WHERE, in -any_ order.
The last columns in the index must be the ORDER BY columns in the same order, and either be all ASC or all DESC.  (MySQL 8.0 has an exception here.)

Your index 1 is necessary and sufficient for query 2.
Your index 2 is necessary and sufficient for query 1.
Anything different would be sub-optimal.

Note that they would also be "covering" in that all the columns anywhere in the SELECT are found in the respective indexes.

Index 2 "covers" both queries, but it is not very good for Query 2.

Devise the optimal index for each important query.
Eliminate redundancy.  Example: Given INDEX(a), INDEX(a,b), toss the former.
Then think about adding some columns on the end to make an index "covering".

For those tips, plus more, see http://mysql.rjweb.org/doc.php/index_cookbook_mysql

This is "covering", but not efficient because col99 in first in the index, but not used for filtering or sorting:

INDEX(col99, col1, col2, col3)

It might be used, but only because it is covering.  It can't be used for filtering or sorting.

This is "covering" and might be used to avoid sorting because the ORDER BY column(s) are first:

INDEX(col3, col2, col1)

Query 1 would be just as happy with this regardless of the cardinality of the individual columns:

INDEX(col2, col1, col3)

Back to the title question:

  Does the order of columns in a covering index matter?

Answer:

For the sake of "covering":  No.
For speeding up the query:  It depends.  And that is why I recommend starting with other 'rules' for building a good index.

An analogy

A table is an unordered set of things.  Analog:  a textbook.
An index is an ordered list, referencing those things.  Analog:  the index in the back of the textbook.
When everything you need to answer a question is sitting in the index (and you don't need to leaf back into the body of the textbook), then the index is "covering".

A B+Tree has two important properties:

It is quick to jump into the middle at any specific word.
It is fast to scan forward from that word.

I created a dbfiddle

All columns are evenly distributed (all columns have equal number of different values).

Dropping index1 does not alter any (tested) execution plan.

dus to comments from @Akina about not using 'covering indexes',  I added some queries to this dbfiddle

Still index1 does not alter any (tested) execution plan.


Get Quarterly Data For a Year I need a way to query a SQL Table and pull back data on a quarter by quarter basis.  I know that SQL Server has a DatePart() function which has a parameter of q = Quarter and I came up with this syntax, but for a table that holds roughly 50,000 rows this syntax is extremely slow in it.  Is this the best way to achieve this result or is there more optimization that can occur?

Declare @startdate date = '20170101', @enddate date = '20171231'
Select
Employeename
,[Total Amount Paid] = SUM(ISNULL(Totalcheckamt,0))
FROM dbo.PaymentHistory
WHERE DatePart(q,[DatePaid]) = 1
AND [DatePaid] BETWEEN CAST(DateAdd(yy, -1, @startdate) As Date)
                   AND CAST(DateAdd(yy, -1, @enddate) As Date)
GROUP BY Employeename
Order By Employeename ASC

EDIT
My desired returned results is like so

Employee Name -- Q1 ---   Q2 ---   Q3 ---    Q4
James           XXXXX.XX  XXXX.XX  XXXX.XX   XXXX.XX
Roger           XXXXX.XX  XXXX.XX  XXXX.XX   XXXX.XX

EDIT 2
I tried this syntax using a case statement but it gives me an error of the error below.  What should I alter in order to be able to succesfully execute this statement?

  Msg 130, Level 15, State 1, Line 18
  Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

SELECT
Employeename
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2016' AND DATEPART(Quarter, [DatePaid]) = 1
        Then SUM(ISNULL(TotalCheckamt,0))
    end
) As Q12016
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2017' AND DATEPART(Quarter, [DatePaid]) = 1
        Then SUM(ISNULL(TotalCheckamt,0))
    end
) As Q12017
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2016' AND DATEPART(Quarter, [DatePaid]) = 2
        Then SUM(ISNULL(TotalCheckamt,0))
        end
) As Q22016
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2017' AND DATEPART(Quarter, [DatePaid]) = 2
        Then SUM(ISNULL(TotalCheckamt,0))
        end
) As Q22017
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2016' AND DATEPART(Quarter, [DatePaid]) = 3
        Then SUM(ISNULL(TotalCheckamt,0))
        end
) As Q32016
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2017' AND DATEPART(Quarter, [DatePaid]) = 3
        Then SUM(ISNULL(TotalCheckamt,0))
        end
) As Q32017
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2016' AND DATEPART(Quarter, [DatePaid]) = 4
        Then SUM(ISNULL(TotalCheckamt,0))
        end
) As Q42016
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2017' AND DATEPART(Quarter, [DatePaid]) = 4
        Then SUM(ISNULL(TotalCheckamt,0))
        end
) As Q42017
FROM dbo.PaymentHistory
    GROUP BY Employeename
    Order By Employeename ASC

Using your CASE expression example, here's how it could be written:

DECLARE @PaymentHistory TABLE (
    EmployeeName VARCHAR(100)
    ,DatePaid DATE
    ,TotalCheckAmt DECIMAL(11, 2)
    )
insert into @Paymenthistory values ('James','2016-01-01',100.00)
insert into @Paymenthistory values ('James','2016-02-01',100.00)
insert into @Paymenthistory values ('James','2016-03-01',100.00)
insert into @Paymenthistory values ('James','2016-04-01',100.00)
insert into @Paymenthistory values ('James','2016-05-01',100.00)
insert into @Paymenthistory values ('James','2016-06-01',100.00)
insert into @Paymenthistory values ('James','2016-07-01',100.00)

insert into @Paymenthistory values ('Roger','2016-01-01',100.00)
insert into @Paymenthistory values ('Roger','2016-02-01',100.00)
insert into @Paymenthistory values ('Roger','2016-03-01',100.00)
insert into @Paymenthistory values ('Roger','2016-04-01',100.00)
insert into @Paymenthistory values ('Roger','2016-05-01',100.00)
insert into @Paymenthistory values ('Roger','2016-06-01',100.00)
insert into @Paymenthistory values ('Roger','2016-07-01',100.00)
SELECT Employeename
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2016'
                    AND DATEPART(Quarter, [DatePaid]) = 1
                    THEN TotalCheckamt
                END), 0) AS Q12016
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2017'
                    AND DATEPART(Quarter, [DatePaid]) = 1
                    THEN TotalCheckamt
                END), 0) AS Q12017
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2016'
                    AND DATEPART(Quarter, [DatePaid]) = 2
                    THEN TotalCheckamt
                END), 0) AS Q22016
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2017'
                    AND DATEPART(Quarter, [DatePaid]) = 2
                    THEN TotalCheckamt
                END), 0) AS Q22017
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2016'
                    AND DATEPART(Quarter, [DatePaid]) = 3
                    THEN TotalCheckamt
                END), 0) AS Q32016
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2017'
                    AND DATEPART(Quarter, [DatePaid]) = 3
                    THEN TotalCheckamt
                END), 0) AS Q32017
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2016'
                    AND DATEPART(Quarter, [DatePaid]) = 4
                    THEN TotalCheckamt
                END), 0) AS Q42016
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2017'
                    AND DATEPART(Quarter, [DatePaid]) = 4
                    THEN TotalCheckamt
                END), 0) AS Q42017
FROM @PaymentHistory
GROUP BY Employeename
ORDER BY Employeename ASC

After you accepted my original answer, I noticed where other posters were advocating the use of PIVOT to achieve the same result as multiple CASE expressions.  For completeness of my answer, I decided to try my hand at using PIVOT to provide additional options for you (and learn something myself in the process).  I've found the information from Questions About Pivoting Data in SQL Server You Were Too Shy to Ask to be quite useful when trying to understand how to use PIVOT.  You'll learn a lot about PIVOT by working through the examples (static and dynamic PIVOT) in the link - I know I did.

To summarize some of the key points of PIVOT (from the link):

  You use the PIVOT operator within your query’s FROM clause to rotate
  and aggregate the values in a dataset. The data is pivoted based on
  one of the columns in the dataset. Each unique value in that column
  becomes its own column, which contains aggregated pivoted data.

  To better understand how this all works, let’s start with the basic
  syntax for a query that uses the PIVOT operator:

SELECT column_list
FROM table_expression
  PIVOT
  (
    aggregate_function(aggregate_column)
    FOR pivot_column
    IN( pivot_column_values )
  ) [AS] pivot_table_alias
[ORDER BY column_list];

  For the SELECT clause, you can specify an asterisk (*) or the
  individual columns, and for the FROM clause, you can specify a table
  or table expression. If you use a table expression, then you must also
  define a table alias. You can also include an ORDER BY clause, but
  that’s optional. You’ll get to see these clauses in action as we
  progress through the questions. For now, let’s focus on the PIVOT
  clause. You need to understand how this clause works to make sure your
  pivots work the way you want.

  After you specify the PIVOT keyword, you pass in what are essentially
  three arguments, enclosed in parentheses. The first is the aggregate
  function and the name of the column to be aggregated. You can use any
  aggregate function except the COUNT function, when used with an
  asterisk, as in COUNT(*).

  Next, you define the FOR subclause, which specifies the column on
  which the pivot will be based. It is this column whose distinct values
  are turned into their own columns. The FOR subclause also includes the
  IN operator, which is where you specify the pivot column values that
  will be turned into columns. The values you specify here must exist in
  the pivot column or they will be ignored.

I'm by no means a PIVOT expert and other posters may have better solutions (hey, I'm willing to learn a better way), but here's my take on it.

I'm including two examples of PIVOT - one static and one dynamic.
(Hopefully, by using my examples and the information in the link provided, you'll understand what I'm doing)

The first example is a static PIVOT and is similar to the CASE expression solution I originally provided (except it uses PIVOT).

Notice that my sample data now includes years 'outside' of the CASE expression.  That information won't show up in the static version, but you'll see how we can solve that in the dynamic version later.

Static PIVOT

DECLARE @PaymentHistory TABLE (
    EmployeeName VARCHAR(100)
    ,DatePaid DATE
    ,TotalCheckAmt DECIMAL(11, 2)
    );
insert into @Paymenthistory values ('James','2016-01-01',100.00);
insert into @Paymenthistory values ('James','2016-02-01',100.00);
insert into @Paymenthistory values ('James','2016-03-01',100.00);
insert into @Paymenthistory values ('James','2016-04-01',100.00);
insert into @Paymenthistory values ('James','2016-05-01',100.00);
insert into @Paymenthistory values ('James','2016-06-01',100.00);
insert into @Paymenthistory values ('James','2016-07-01',100.00);
insert into @Paymenthistory values ('James','2017-01-01',100.00);
insert into @Paymenthistory values ('James','2018-10-01',900.00);

insert into @Paymenthistory values ('Roger','2016-01-01',100.00);
insert into @Paymenthistory values ('Roger','2016-02-01',100.00);
insert into @Paymenthistory values ('Roger','2016-03-01',100.00);
insert into @Paymenthistory values ('Roger','2016-04-01',100.00);
insert into @Paymenthistory values ('Roger','2016-05-01',100.00);
insert into @Paymenthistory values ('Roger','2016-06-01',100.00);
insert into @Paymenthistory values ('Roger','2016-07-01',100.00);
insert into @Paymenthistory values ('Roger','2020-10-01',900.00);
;
WITH cte_Paymenthistory
AS (
    SELECT CASE
            WHEN Datepart(Year, [DatePaid]) = '2016'
                AND DATEPART(Quarter, [DatePaid]) = 1
                THEN 'Q12016'
            WHEN Datepart(Year, [DatePaid]) = '2016'
                AND DATEPART(Quarter, [DatePaid]) = 2
                THEN 'Q22016'
            WHEN Datepart(Year, [DatePaid]) = '2016'
                AND DATEPART(Quarter, [DatePaid]) = 3
                THEN 'Q32016'
            WHEN Datepart(Year, [DatePaid]) = '2016'
                AND DATEPART(Quarter, [DatePaid]) = 4
                THEN 'Q42016'
            WHEN Datepart(Year, [DatePaid]) = '2017'
                AND DATEPART(Quarter, [DatePaid]) = 2
                THEN 'Q12017'
            WHEN Datepart(Year, [DatePaid]) = '2017'
                AND DATEPART(Quarter, [DatePaid]) = 3
                THEN 'Q32017'
            WHEN Datepart(Year, [DatePaid]) = '2017'
                AND DATEPART(Quarter, [DatePaid]) = 4
                THEN 'Q42017'
            END AS ColumnLabel
        ,EmployeeName
        ,TotalCheckAmt
    FROM @PaymentHistory
    )
SELECT EmployeeName
    ,coalesce([Q12016], 0) AS [Q12016]
    ,coalesce([Q12017], 0) AS [Q12076]
    ,coalesce([Q22016], 0) AS [Q22016]
    ,coalesce([Q22017], 0) AS [Q12017]
    ,coalesce([Q32016], 0) AS [Q32016]
    ,coalesce([Q32017], 0) AS [Q32017]
    ,coalesce([Q42016], 0) AS [Q42016]
    ,coalesce([Q42017], 0) AS [Q42017]
FROM cte_Paymenthistory
PIVOT(SUM(TotalCheckAmt) FOR ColumnLabel IN (
            [Q12016]
            ,[Q12017]
            ,[Q22016]
            ,[Q22017]
            ,[Q32016]
            ,[Q32017]
            ,[Q42016]
            ,[Q42017]
            )) AS pvt
ORDER BY employeename;

Did you notice the data outside the CASE expressions didn't show up?

Let's try to solve that by using a dynamic PIVOT.

Dynamic PIVOT

  How do I pivot data if I don’t know the values in the pivoted column?

(Due to using dynamic SQL, we have to switch to using a temporary table instead of a table variable)

set nocount on
DECLARE @sql AS NVARCHAR(2000);
DECLARE @col AS NVARCHAR(2000);
DECLARE @colCoalesceNull AS NVARCHAR(2000);

IF OBJECT_ID('tempdb..#PaymentHistory') IS NOT NULL drop Table #PaymentHistory
CREATE TABLE #PaymentHistory (
    EmployeeName VARCHAR(100)
    ,DatePaid DATE
    ,TotalCheckAmt DECIMAL(11, 2)
    );
insert into #Paymenthistory values ('James','2016-01-01',100.00);
insert into #Paymenthistory values ('James','2016-02-01',100.00);
insert into #Paymenthistory values ('James','2016-03-01',100.00);
insert into #Paymenthistory values ('James','2016-04-01',100.00);
insert into #Paymenthistory values ('James','2016-05-01',100.00);
insert into #Paymenthistory values ('James','2016-06-01',100.00);
insert into #Paymenthistory values ('James','2016-07-01',100.00);
insert into #Paymenthistory values ('James','2017-01-01',100.00);
insert into #Paymenthistory values ('James','2018-10-01',900.00);

insert into #Paymenthistory values ('Roger','2016-01-01',100.00);
insert into #Paymenthistory values ('Roger','2016-02-01',100.00);
insert into #Paymenthistory values ('Roger','2016-03-01',100.00);
insert into #Paymenthistory values ('Roger','2016-04-01',100.00);
insert into #Paymenthistory values ('Roger','2016-05-01',100.00);
insert into #Paymenthistory values ('Roger','2016-06-01',100.00);
insert into #Paymenthistory values ('Roger','2016-07-01',100.00);
insert into #Paymenthistory values ('Roger','2020-10-01',900.00);
;
;
SELECT @col =
        Coalesce(@col + ', ', '') + QUOTENAME(PvtColumnName)
    ,@colCoalesceNull =
        Coalesce(@colCoalesceNull + ', ', '') + 'coalesce(' + QUOTENAME(PvtColumnName) + ',0) as ' + QUOTENAME(PvtColumnName)
FROM (
    SELECT DISTINCT
    'Q' +
    CONVERT(VARCHAR(1), DATEPART(Quarter, [DatePaid])) +
    CONVERT(VARCHAR(4), year([DatePaid]))
        AS PvtColumnName
    FROM #PaymentHistory
    ) AS PaymentHistory;

PRINT @col
PRINT @colcoalescenull

SET @sql = N'
with cte_PaymentHistory as
(
select
''Q'' + CONVERT(varchar(1),DATEPART(Quarter, [DatePaid])) + CONVERT(varchar(4),year([DatePaid])) as PvtColumnName
,EmployeeName
,TotalCheckAmt
from #PaymentHistory
)
SELECT EmployeeName, ' + @colCoalesceNull + 'FROM cte_PaymentHistory
    PIVOT(SUM(TotalCheckAmt)
    FOR PvtColumnName IN (' + @col + ')) AS PivotPaymentHistory';

EXEC sp_executesql @sql;

Your question prompted me to research and learn more about PIVOT - thanks.

Yes, what you are asking is easy to do. You can create a common table expression that groups by both EmployeeName and DATEPART(qq,DatePaid). Then you can use a PIVOT statement on that common table expression to format the data to your target output.

What you are doing with CASE and SUM is what PIVOT is for. See Using PIVOT and UNPIVOT.

PIVOT can be tricky, the mistake most people make is to use PIVOT without a CTE. While in some cases it can be done, in practice it's nearly always going to lead to trouble. Use a CTE to isolate just the columns and rows you need before applying the PIVOT.


Selecting data from multiple rows into a single row I have a table that has columns like following. Batch number is a database design thing like row number

demand_id        batch_number    debit status     customer
     34               1             yes
     34               2                             jack
     35               1             no
     35               2                             kate

I want to create a query that would return this:

   demand_id         debit status      customer
     34                  yes             jake
     35                  no              kate

How can I do that?

Join the table to itself.

So:

select yt1.demand_id, yt1.debit_status, yt2.customer
from yourtable yt1, yourtable yt2
where yt1.demand_id = yt2.demand_id
and yt1.debit_status is not null
and yt2.customer is not null;

Or (if batch_number can reliably be used to fetch the 2 rows needed to make a single row:

select yt1.demand_id, yt1.debit_status, yt2.customer
from yourtable yt1, yourtable yt2
where yt1.demand_id = yt2.demand_id
and yt1.batch_number = 1
and yt2.batch_number = 2;

SQL fiddle.

Getting multiple rows from db and iterate them

if (Collection.Count > 0)
        {
            bool firstRecord = true;
            bool secondRecord = false;
            FirstRowEntiry ent = null;

            foreach (SecondRowEntity ope in opc)
            {

               if (firstRecord == true)
               {
                   //saving first row data in Ent row
                   ent = ope;
                   firstRecord = false;
                   secondRecord = true;
                   continue;
               }

               if (secondRecord == true)
               {

        -----------------------------------------------------
        //checking first and second records match exists
        ------------------------------------------------------
                   if (ope.PrimaryKeyId == ent.PrimaryKeyId && ope.Type == ent.Type)
                   {

        // Add all items to listview
                       lvi.SubItems.Add(ent.PrimaryKeyId.ToString());
                       lvi.SubItems.Add("");
                       firstRecord = true;
                       secondRecord = false;
                       listView1.Items.Add(lvi);
                   }
                   else
                   {
        -----------------------------------------------------------
        // If id and type doenotmatch at all then will follows
        ---------------------------------------------------------------

                       bool entDone = false;
                       bool opeDone = false;

                         if (ent.Type == "Type1" && entDone == false)
                           {

              // Add items for required columns and other columns make empty
               lvi = new ListViewItem(ope.PrimaryKeyId.ToString());
                               lvi.SubItems.Add(ent.PrimaryKeyId.ToString());
                               lvi.SubItems.Add(ent.Type.ToString());
                               lvi.SubItems.Add("");
                               lvi.SubItems.Add("");
                               lvi.SubItems.Add("");
                               lvi.SubItems.Add("");
                               listView1.Items.Add(lvi);
                               entDone = true;
                           }
                           if (ent.Type == "Type2" && opeDone == false)
                           {
             // Add items for required columns and other columns make empty
                               lvi.SubItems.Add("");
                               lvi.SubItems.Add("");
                               lvi.SubItems.Add("");
                               lvi.SubItems.Add("");
                               lvi.SubItems.Add(ent.PrimaryKeyId.ToString());
                               lvi.SubItems.Add(ent.Type.ToString());
                               listView1.Items.Add(lvi);
                               entDone = true;
                           }

                           if (ope.Type == "Type1" && opeDone == false)
                           {
                               lvi = new ListViewItem(ope.PrimaryKeyId.ToString());
                               lvi.SubItems.Add(ope.Type.ToString());
                               lvi.SubItems.Add(ope.Type.ToString());
                               lvi.SubItems.Add("");
                               lvi.SubItems.Add("");
                               lvi.SubItems.Add("");
                               lvi.SubItems.Add("");
                               listView1.Items.Add(lvi);
                               opeDone = true;
                           }
                           if (ope.Type == "Type2" && opeDone == false)
                           {
                               lvi = new ListViewItem(ope.PrimaryKeyId.ToString());
                               lvi.SubItems.Add("");
                               lvi.SubItems.Add("");
                               lvi.SubItems.Add("");
                               lvi.SubItems.Add("");
                               lvi.SubItems.Add(ope.Type.ToString());
                               lvi.SubItems.Add(ope.Type.ToString());
                               listView1.Items.Add(lvi);
                               opeDone = true;
                           }

                       firstRecord = true;
                       secondRecord = false;
                   }
               }

            }
        }


Empty v$asm_diskgroup with sqlplus I have an Oracle Database instance installed under an OL7. It is a 12c2.
Oracle GI also installed and my database has some files stored in ASM.

I am  using sqlplus to connect to the database instance and query v$asm_diskgroup. My ORACLE_HOME points to the home of my database instance.

When I connect without specifying  a "@CONNECTION" (e.g using: sqlplus user/name), then the view v$asm_diskgroup returns no rows.
When I connect specifying a @TNSNAME (e.g: sqlplus user/name@TNSNAME ), then the view v$asm_diskgroup returns data correctly.

Also, when I connect using sql developer, from another machine, the v$asm_diskgroup returns the rows.

Why are  no v$asm_diskgroup rows returned when I use sqlplus without @CONNECTION, that is, the default logon?

You would need to connect to the ASM instance using a user ID that has the SYSASM system privilege:

connect scott as sysasm;

Balaz Papp's comment above is correct in the explanation why connected without a connect string is working. And his comment below is correct; any user could be granted query access to the view, when connected to the regular Oracle database instance.

you need to set the search string in ASM.

Failing to do so, will result in the gv$asm_diskgroup view to be empty.

Tomorrow morning with a clearer head and a desktop to give you exact commands I will edit and extend this answer.


XAMPP Installation - MySQL shutdown unexpectedly I'm installing XAMPP on my machine. I'm following Derek Banas youtube tutorial, and I also tried myself, but I'm constantly running into the same issues.

I have changed my Apache ports to 1234 (main), and 8181 (SSL) and MySQL ports to (3307). Apache runs fine, but when I run MySQL from the XAMPP control panel it get this error log.

  23:11:36  [mysql]  Error: MySQL shutdown unexpectedly.

  23:11:36  [mysql]  This may be due to a blocked port, missing dependencies,

  23:11:36  [mysql]  improper privileges, a crash, or a shutdown by another method.

  23:11:36  [mysql]  Press the Logs button to view error logs and check

  23:11:36  [mysql]  the Windows Event Viewer for more clues

  23:11:36  [mysql]  If you need more help, copy and post this

  23:11:36  [mysql]  entire log window on the forums.

I have also deleted files from the MySQL folder in the xampp folder recommended in a previous stack overflow question https://stackoverflow.com/a/18162264/9182716

Also when trying to navigate to localhost phpmyadmin I'm prompted with this warning screen.

https://imgur.com/a/7iagc

I had this problem too. If changing MySQL port doesn't solve the issue, which was my case. Simply copy the files in xampp/mysql/backup to xampp/mysql/data. Replace any conflicting files and leave the rest.
Then restart Apache and MySQL from the Xampp control panel.
This should solve the problem.

I uninstalled and re-installed XAMPP and then went to msconfig and Reduced the UAC to "Never Notify me " and then went back and refreshed mysql on XAMPP. It worked!


What does repair and optimize tables in PHP MyAdmin actually do? Sometimes I repair or optimize (normally both) my tables in PHP MyAdmin  Often it fixes a problem I'm having.  My question is what am I actually doing when I undertake these actions?

Is there any danger?  Is any data changed or moved?

Someone told me Optimize is...

Do you remember when we used to defrag HDDs? Table optimization is something similar to that. It restructures a table so that data pages are next to each other and releases unused pages. If you ever delete a large part of the table data, optimization shrinks the table file down to the proper size.


Storing Address Data Model I'm revising our customer database from a one-to-one relation to a one-to-many. Specificaly we are introducing a new billing address and a shipping address. When researching I came across this post Storing a Billing Address Best Practice in Orders Table and this post Is there common street addresses database design for all addresses of the world?

Thinking about this I have some questions. I'm seeking answers so to implement the data model as provided by both answers, but currently I have doubts about implementing it this way, even though I understand from a technical point of view this is the way to do it. At the moment my main question is about the relation between the Address and CustomerAddress table.

We will have customers enter there own address. If they make a mistake and not receive anything it's their bad. This means two people can live at the same physical address, but have a slightly different printed address. One customer could for example abbeviate 'boulevard', but another doesn't have to do that. We want to implement it this way so when someone changes his address the address for different people on the same physical address doesn't change as well. So there will be no standardized address table. (If I would want that and still not want the aforementioned scenario I would my self have to organize an administrative process to create standardized addresses. This is not going to happen.) Every address record will be unique to a customer. So I think there is a one-to-one relation between Address and CustomerAddress.

The one-to-many relationship for addresses is provided, as in both data models in the provided links, through multiple records in the CustomerAddress table. The CustomerAddress table provides different purposes for each address through either a bit (data model 1 ) or a type (data model 2). Changes to these can be recorded in the CustomerAddressHistory table.

Due to the one-to-one relation between Address and CustomerAddress I thought about merging Address and CustomerAddress, but that would mean more storage when changing only a 'purpose' bit or type in the CustomerAddressHistory table.

So the main question is what would be the scenario that is the main objection to not implement a one-to-one relation between Address and CustomerAddress?

When working with addresses, one thing you may consider -- at least give it a serious look -- is using the USPS to convert the user-supplied address into the USPS standard address. So the same address, just with different abbreviations or punctuations, will converge to the same address. There are several remote apis the Post Office makes available to your app.

As for the database, yes, the literature shows that the place to put a 1-to-many FK is in the "many" tuple. You have also stated you want to maintain a history of address changes so this format makes even more sense.

However, there are many advantages to having a 1-to-many intersection table. This is similar to a many-to-many intersection table except that one side is made part of the PK to enforce the "1" side of the 1-to-many cardinality.

create table OneToMany(
  OneID    int   not null
    references UserTable( ID ),
  ManyID   int   not null
    references AddressTable( ID ),
  TypeID   char( 1 ),    -- 'H' for Home, 'W' for work, etc.
  constraint PK_OneToMany primary key( OneID, TypeID )
);

Each user has only one entry for each kind of address and even if multiple users share the same address, if one user moves to a different address, the other users are not affected.

There is a small alteration needed to maintain the history of changes. All that is needed is the addition of a datetime field.

create table OneToMany(
  OneID    int   not null
    references UserTable( ID ),
  ManyID   int   not null
    references AddressTable( ID ),
  TypeID   char( 1 ),    -- 'H' for Home, 'W' for work, etc.
  Effective datetime not null default NOW,
  constraint PK_OneToMany primary key( OneID, TypeID, Effective )
);

Now all changes are maintained and it is very easy to look back at any time in the past to see what the address was at that time. Here is an answer I posted that will refer you to more information. Mine is not the accepted answer, and I would highly recommend you take the time to become familiar with both mine and the accepted answer. Choose the one (or design your own with what you learn) that is best for your situation.

Frankly, that model is awful. Don't use it. For starters, you won't have proper address history on your orders.

Customer(CustomerID, ...)
Address(AddressID, AddressType, CustomerID, EndDate, ...)
Order(CustomerID, ShippingAddressID, BillingAddressID, ...)

History is kept in the address table (whenever you change an address, set the end date and create a new record), so don't forget to do EndDate IS NULL to get the active one.


How to deny writes in all SQL Server databases (including new ones & restored ones) I want to create a login that is denied the ability to write to any databases - not just databases that exist today, but any newly created databases or databases that are restored from other servers.
I can't use database-only roles (not even in model), because those won't take effect on newly restored databases.
(Business purpose: I'm writing a blog post on how folks can set up a new login for themselves that has low privilege, and they can make sure that they don't accidentally have an "oops" moment when they copy/paste code or run it without a where clause.)

If you could live with a finite time (minimum 10-seconds) between executions to catch new/restored databases, you could create a scheduled SQL Server Agent Job and put something like this in a T-SQL Job Step:-
SET NOCOUNT ON

DECLARE @command nvarchar(max) ;

SET @command =
N'SET NOCOUNT ON

DECLARE @UserName sysname ;

SELECT @UserName = USRS.[name]
FROM sys.database_principals AS USRS
INNER JOIN sys.server_principals AS LGNS ON LGNS.[sid] = USRS.[sid]
WHERE LGNS.[name] = ''LoginName'' ;

IF IS_ROLEMEMBER(''db_denydatawriter'', @UserName) = 0
   BEGIN
      EXEC sp_addrolemember ''db_denydatawriter'', @UserName ;
   END ;' ;

EXEC sp_ineachdb
   @command      = @command,
   @state_desc   = N'ONLINE',
   @is_read_only = 0 ;

I think would use a server scoped trigger, and any Create Database or Restore database commands would immediately follow a create user and deny IUD on the newly created or restored database:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver15
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS

GO


Getting midnight of today I want to get 00:00:00 of a day.  Should be done without using CONCAT. What options are there?

We can use CURDATE() function in select query but need to display like 2014-05-05 00:00:00. If NOW() will give like 2014-05-05 12:05:37.

I want add default value 00:00:00 with CURDATE() in query.

The documentation surprisigly contains a solution:

SELECT timestamp(current_date);

(which is basically the same as a comment above).

Another solution would be: cast getdate() to a date, and that back to a datetime.

Code (for MS SQL Server, but the idea applies also to mysql):

select cast(cast(GETDATE() as date) as datetime2)


SQL Server Cannot drop database  because it is currently in use... but no sessions displayed When I try to drop a database I get the error "Cannot drop database "dbname" because it is currently in use". However, when I run sp_who2, there are definitely no sessions connected to this database.  I've also set the database to single_user mode with rollback immediate.

Why is this happening?

A session connected to another database might have an open transaction that also affects your database - sp_who2 will only show one database. It could also be something as simple as Object Explorer or Object Explorer Details open in SSMS, which again would only show one database in sp_who2.

Don't bother trying to find the session that is responsible; just kill them all with one statement (and make sure it isn't your copy of SSMS that is connected, e.g. another query window, Object Explorer, etc.):

USE master;
GO
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

Now you will be able to drop it, and do that using DDL, not the UI:

DROP DATABASE dbname;

Found really simple answer on StackOverflow that worked first time for me:

https://stackoverflow.com/a/7469167/261405

Here's the SQL from that answer:

DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'YOUR_DABASE_NAME'

DECLARE @SQL varchar(max)

SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

--Use this to see results
SELECT @SQL
--Uncomment this to run it
--EXEC(@SQL)


Deadlock when delete using OR operator I got a deadlock when having more than one sessions doing the following:

start transaction
insert data to table A
delete data from table A
commit

at the same time

They may delete the same records or different

I could replicate it with a simplified code like below, the table is empty at the start

Session 1:

BEGIN TRANSACTION
DECLARE @component_id  numeric = 1
DECLARE @relative_component_id  numeric = 0

INSERT INTO component_lock(area_id, component_id, generic_id, operator_id, session_id, lock_date, lock_type)
VALUES(1, @component_id, 1, 1, 1, '1/1/2018', 1)

WAITFOR DELAY '00:00:05'

DELETE FROM component_lock
WHERE area_id = 1
AND (component_id = @component_id OR component_id = @relative_component_id)
AND operator_id = 1
AND generic_id = 1
AND session_id = 1

COMMIT

Session 2

BEGIN TRANSACTION
DECLARE @component_id  numeric = 2
DECLARE @relative_component_id  numeric = 0

INSERT INTO component_lock(area_id, component_id, generic_id, operator_id, session_id, lock_date, lock_type)
VALUES(1, @component_id, 1, 1, 1, '1/1/2018', 1)

WAITFOR DELAY '00:00:05'

DELETE FROM component_lock
WHERE area_id = 1
AND (component_id = @component_id OR component_id = @relative_component_id)
AND operator_id = 1
AND generic_id = 1
AND session_id = 1

COMMIT

The primary key is (area_id, component_id, generic_id)

The foreign key is (operator_id)

There are 2 indexes on component_id and operator_id (nonclustered)

Deadlock graph

Actual execution plan of the delete query from session that was not chosen as the deadlock victim
https://www.brentozar.com/pastetheplan/?id=ryyPArbi7

The deadlock does not happen if I remove the OR operator and change it to 2 delete queries

DELETE FROM component_lock
WHERE area_id = 1
AND (component_id = @component_id)
AND operator_id = 1
AND generic_id = 1
AND session_id = 1

DELETE FROM component_lock
WHERE area_id = 1
AND (component_id = @relative_component_id)
AND operator_id = 1
AND generic_id = 1
AND session_id = 1

Actual execution plan
https://www.brentozar.com/pastetheplan/?id=Sy9ACrbsm

Can anyone please explain to me why the difference?

The issue is not about how many delete operations there are, it’s about how it finds the rows that are to be deleted.

If you look at the query plans you’ll almost certainly let see that your delete with OR is scanning the whole table. This will take out locks to make sure the rows don’t move around while it’s looking for ones to delete. Meanwhile, your inserts will take out locks, as the table and other indexes are maintained. Without the OR, you’re getting Seek operations, and only small parts of the table are locked.

Replace your delete query with a select to select all primary key columns and then delete the rows using only primary key columns

SELECT component_id, generic_id, area_id
INTO #temp
FROM component_lock
WHERE area_id = 1
AND (component_id = @component_id OR component_id = @relative_component_id)
AND operator_id = 1
AND generic_id = 1
AND session_id = 1

DELETE FROM component_lock cl
JOIN #temp t ON t.area_id = cl.area_id and t.generic_id = cl.generic_id and t.component_id = cl.component_id

This will change the locking pattern and hopefully will resolve the deadlocks.


Message file not found when starting SQLPlus I've installed Oracle XE 11.2 on Fedora 18 but have trouble starting SQLPlus. It quits with the following error:

Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

All of the environment variables are properly set (copied from installation guide)

# ~/.zshrc
ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe # It is in fact installed there
ORACLE_SID=XE
NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
PATH=$ORACLE_HOME/bin:$PATH

NLS_LANG is AMERICAN_AMERICA.AL32UTF8. As far as I understand Oracle ignores system language related variables but here they are anyway:

LC_CTYPE=en_US.UTF-8
LANG=en_US.UTF-8

Any suggestions?

This error usually occurs if you are executing sqlplus as a different user (other than the one which installed Oracle software), due to permission issues

Check Oracle Note: 356850.1

You need to export ORACLE_HOME to oracle home directory
and path to ORACLE_HOME/bin directory


How does a database use the SYS schema even when we change the password? From the thread What is the difference between sys and system accounts in Oracle databases?, I understand that sys schema is supposed to be used only by the database.

But still an administrator can change the password for the schema.

Can the database connect without the password? If not, how does it connect even after the password is changed?

The database doesn't bother authenticating itself to itself.

As the database does not use the password identification strategy when performing operations, changing the SYS password only impacts how humans can use the account.

yeah , you can conncet to database with no passwprd when you conncet as admin on serever :

$ sqlplus
username : sys as sysdba
password : (empty)

then the database open with idle . you can work with idle instance for some of activities


What does a RED X on a database user mean? I created two new AD groups and added them as Users of a database, but their icons show with a red X.
What does this mean?

It does not mean the user is disabled (you can only disable logins), it means the user does not have connect privileges to the database. I'm not sure exactly how your users were created, but the easiest way to demonstrate this is:

CREATE LOGIN u1 WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
USE tempdb;
GO
CREATE USER u1 FROM LOGIN u1;
GO
ALTER LOGIN u1 DISABLE;
GO
-- u1 has no red x even though the login has been disabled

CREATE USER u2 WITHOUT LOGIN;
GO
-- check Object Explorer, u2 has no red x

DENY CONNECT TO u2;
GO
-- check Object Explorer, u2 now has a red x!

CREATE USER u3 WITHOUT LOGIN;
GO
-- check Object Explorer, u3 has no red x

REVOKE CONNECT FROM u3;
GO
-- check Object Explorer, u3 now has a red x!

(You may need to refresh Object Explorer between GO commands because, well, caching.)

To fix it (assuming you actually want them to be able to connect to the database):

GRANT CONNECT TO [DomainName\BI360Consultants];
GRANT CONNECT TO [DomainName\BI360Users];

Surely you'll need to apply more permissions depending on what you need them to be able to do in the database.

There may be other, more obscure ways to get into this state (e.g. adding a domain group to a role in a database without actually adding a user, as described in MichaelK's answer). Though I'll be honest, when I tried to do what the OP did, the old way or the right way, I was not able to add the domain group to a role without a user present:

-- the old way
EXEC sys.sp_addrolemember N'db_datareader', N'[CAKE\MyGroup]';

  Msg 15410, Level 11, State 1, Procedure sp_addrolememberUser or role '[CAKE\MyGroup]' does not exist in this database.

-- the right way
ALTER ROLE db_datareader ADD MEMBER [CAKE\MyGroup];

  Msg 15151, Level 16, State 1Cannot add the principal 'CAKE\MyGroup', because it does not exist or you do not have permission.

Of course with this outcome I did not see any such user in sysusers (deprecated; stop using it) or sys.database_principals. However, if I did this (thanks to sepupic's answer):

GRANT SELECT ON dbo.SomeTable TO [CAKE\MyGroup];

Then the user did show up in those views and did show up as a user in Object Explorer with the red x due to HAS_DBACCESS() = 0. Which still amounts to roughly the same thing: "can't access the database." So if the above GRANT CONNECT does not work (in my case, that did get rid of the red x, but I did not try to actually query the database as that account), also try the following, knowing that it might fail:

CREATE USER [DOMAIN\Group] FROM LOGIN [DOMAIN\Group];

In my case, when I granted connect to this user, it prevented me from running the CREATE USER command:

  Msg 15023, Level 16, State 1, Line 16User, group, or role 'CAKE\MyGroup' already exists in the current database.

This state will always be true for guest/INFORMATION_SCHEMA/sys - with the exception of the guest account on certain system databases. Ignore that and leave them alone.

From the sp_addrolemember topic:

From the sys.sysusers topic:

Red X means the logins are disabled w/in SQL Server


What's causing this ERROR 2000 (HY000): Unknown MySQL error I have a MySQL server with version 5.6.12-log (it's running on OpenSUSE). I can connect to it from another server (CentOS 6) using:

mysql -h **** -u **** --port **** -p

It prompts me for my password and connects just fine. The client version is "Ver 14.14 Distrib 5.6.12, for Linux (x86_64) using  EditLine wrapper."

However, from another server (running CentOS 7), using the same command, I get the following error after entering my password:

ERROR 2000 (HY000): Unknown MySQL error

This error is useless. There is nothing in the client machine logs that indicate the cause of the failure. However, I do know that the MySQL server is at least receiving the connect request because it logs a warning about the Reverse DNS lookup not matching. It's just a warning—not the cause of this problem—but it proves that firewalls and/or network configurations aren't blocking the connection. There is nothing else in the MySQL server log (or error log) that indicates the cause of the failure. This client version is "Ver 14.14 Distrib 5.6.23, for Linux (x86_64) using  EditLine wrapper."

Help??? I'm lost here. In 10+ years of MySQL admining, I've never seen this error before. I don't even know where to begin.

Well I figured it out, and it isn't pretty. I guess I'll have to file a bug with MySQL.

The server is compiled with OpenSSL.
The working client is compiled with OpenSSL.
The client that can't connect is compiled with yaSSL.

The user I was trying to connect with used the sha256_password authentication plugin. When I dropped and re-created the user with mysql_native_password instead, the problem went away and I can connect from all clients. It appears that the sha256_password plugin doesn't work if the server and client are compiled against different SSL providers. (It might not even work with yaSSL at all.)

I use this command "service mysqld stop" and "service mysqld start" to restart my mysql service, mysql work well.


Why does my query search datetime not match? select *
from A
where posted_date >= '2015-07-27 00:00:00.000'
  and posted_date  <= '2015-07-27 23:59:59.999'

But the result contains a record that has posted_date today: 2015-07-28.
My database server is not in my country.
What is the problem ?

As several others have mentioned in comments and other answers to your question the core issue is 2015-07-27 23:59:59.999 is being rounded to 2015-07-28 00:00:00.000 by SQL Server. Per the documentation for DATETIME:

  Time range - 00:00:00 through 23:59:59.997

Note that the time range can never be .999. Further down in the documentation it specifies the rounding rules that SQL Server uses for the least significant digit.

Notice that the least significant digit can only have one of three potential values: "0", "3", or "7".

There are several solutions/workarounds for this that you can use.

-- Option 1
SELECT
    *
FROM A
WHERE posted_date >= '2015-07-27 00:00:00.000'
  AND posted_date <  '2015-07-28 00:00:00.000' --Round up and remove equality

-- Option 2
SELECT
    *
FROM A
WHERE posted_date >= '2015-07-27 00:00:00.000'
  AND posted_date <=  '2015-07-27 23:59:59.997' --Round down and keep equality

-- Option 3
SELECT
    *
FROM A
WHERE CAST(posted_date AS DATE) = '2015-07-27' -- Use different data type

-- Option 4
SELECT
    *
FROM A
WHERE CONVERT(CHAR(8), DateColumn, 112) = '20150727' -- Cast to string stripping off time

-- Option 5
SELECT
    *
FROM A
WHERE posted_date BETWEEN '2015-07-27 00:00:00.000'
  AND '2015-07-27 23:59:59.997' --Use between

Out of the five options I've presented above I would consider options 1 and 3 the only viable options.  They convey your intent clearly, and aren't going to break if you update data types.  If you are using SQL Server 2008 or newer I think option 3 should be your preferred approach.  That is especially true if you can change away from using the DATETIME datatype to a DATE data type for your posted_date column.

Regarding option 3, a very good explanation about some issues can be found here: Cast to date is sargable but is it a good idea?

I don't like options 2 and 5 because the .997 fractional seconds is going to be just another magic number that people are going to want to "fix." For some more reasons why BETWEEN isn't widely embraced you might want to checkout this post.

I don't like option 4 because converting data types to a string for comparison purposes feels dirty to me.  A more qualitative reason to avoid it in SQL Server is it impacts sargability aka you can't perform an index seek and that will frequently result in poorer performance.

For more information on the right way and wrong way to handle date range queries checkout this post by Aaron Bertrand.

In parting you would be able to keep your original query and it would behave as desired if you change your your posted_date column from a DATETIME to a DATETIME2(3).  That would save storage space on the server, give you greater accuracy at the same precision, be more standards compliant/portable, and allow you to easily adjust the accuracy/precision if your needs change in the future. However, this is only an option if you are using SQL Server 2008 or newer.

As a bit of trivia the 1/300 of a second accuracy with DATETIME seems to be a hold over from UNIX per this StackOverflow answer. Sybase which has a shared heritage has a similar 1/300 of a second accuracy in their DATETIME and TIME data types but their least significant digits are a touch different at "0", "3", and "6". In my opinion the 1/300 of a second and/or 3.33ms accuracy is an unfortunate architectural decision since the 4 byte block for the time in SQL Server's DATETIME data type could have easily supported 1ms accuracy.

select * from A where date(posted_date) = '2015-07-27'


Moving a SQL Cluster to Another Data Center w/No Downtime We have a two node SQL 2008 Enterprise cluster in a data center in California, and are looking to move hosts from a leased environment to a colo environment much closer to our home office.  Our cluster is quite busy with around 600GB of live data, so we're looking for a way to migrate with as little downtime as possible.

This is the only thing that I believe will be the challenge in the entire environment.

My initial thought was to add a passive node to the SQL Cluster and allow it to slowly replicate over to the other data center.  Once caught up, it wouldn't be hard, in theory, to fail over to this node say overnight and be active, DNS propagated, and so on, by that morning.

Does this sound viable, and if not, are there some other suggestions?

Thanks in advance for your help.

Note that both environments are very beefy resource wise, and one has 100MBps unmetered, while the destination is 1GBps unmetered.  Bandwidth, space, and resources are not a concern.

In SQL 2008 you're looking for minimum downtime vs no downtime in these cases unless you're willing to shell out a lot of money/time for a few seconds.  This sounds like hopefully it's not too complex of an environment so you have several options.

How about SQL Mirroring?  If your data isn't coming in too fast, it'll sync up and you can 'failover mirror' option.  Also, it's a very easy rollback, you just hit failover mirror again and it'll fail right back to the old cluster.  If you go with High Safety mode, all writes have to be hardened on the other side before committing, thus you will have some transactional delay while the mirror is up.  The upside is that it's super fast to just fail over as the data is certainly up to date.  High performance mode is available in Enterprise.  Mirroring is much more simple than replication and requires a lot less configuration items/requirements.  For example, in Replication, every replicated table must have a PK, this isn't required in Mirroring.  You also have the option of a witness server which will help with it being transparent to the application.

Worst case, you can do log shipping with automated scripts to restore the tail of the log.  You can take a log backup, restore, put the DB in read only mode, take another log backup, and restore again.  Be sure to check your VLFS (DBCC LOGINFO;) because if you have hundreds upon hundreds, your log restores will be slow complicating things.

Ensure security matches on both sides.  Are they different domains?  Then you'll have different users you'll need to add to your DB as soon as they go live.  SQL user?  Copy the user and SID together.  There's scripts to do this.

Prior to the failover, run a trace or profiler to see what apps are connecting from where.  Make sure to account for them on the new cluster.  This includes user security.  Also run a trace or profiler looking for logins to the old server.  Let the app owner know they are connecting to the wrong DB.

Whatever you do, make sure to test and stage the process multiple times and automate it to where it's a couple of key strokes.  Test failure scenarios and go into this confident.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a7b2f83b-89eb-4277-9d69-3762c9133d46/existing-sql-cluster-easiest-method-to-move-to-new-datacenter?forum=sqldatabasemirroring

This link may help for people who require this informaiton


Getting a scan though I expect a seek I need to optimize a SELECT statement but SQL Server always does an index scan instead of a seek. This is the query which, of course, is in a stored procedure:

CREATE PROCEDURE dbo.something
  @Status INT = NULL,
  @IsUserGotAnActiveDirectoryUser BIT = NULL
AS

    SELECT [IdNumber], [Code], [Status], [Sex],
           [FirstName], [LastName], [Profession],
           [BirthDate], [HireDate], [ActiveDirectoryUser]
    FROM Employee
    WHERE (@Status IS NULL OR [Status] = @Status)
    AND
    (
      @IsUserGotAnActiveDirectoryUser IS NULL
      OR
      (
        @IsUserGotAnActiveDirectoryUser IS NOT NULL AND
        (
          @IsUserGotAnActiveDirectoryUser = 1 AND ActiveDirectoryUser <> ''
        )
        OR
        (
          @IsUserGotAnActiveDirectoryUser = 0 AND ActiveDirectoryUser = ''
        )
      )
    )

And this is the index:

CREATE INDEX not_relevent ON dbo.Employee
(
    [Status] DESC,
    [ActiveDirectoryUser] ASC
)
INCLUDE (...all the other columns in the table...);

The plan:

Why did SQL Server choose a scan? How can I fix it?

Column definitions:

[Status] int NOT NULL
[ActiveDirectoryUser] VARCHAR(50) NOT NULL

Status parameters can be:

NULL: all status,
1: Status= 1 (Active employees)
2: Status = 2 (Inactive employees)

IsUserGotAnActiveDirectoryUser can be:

NULL: All employees
0: ActiveDirectoryUser is empty for that employee
1: ActiveDirectoryUser  got a valid value (not null and not empty)

I don't think the scan is caused by a search for an empty string (and while you could add a filtered index for that case, it will only help very specific variations of the query). You are more likely a victim of parameter sniffing and a single plan not optimized for all of the various combinations of parameters (and parameter values) that you will be providing to this query.

I call this the "kitchen sink" procedure, because you are expecting one query to provide all the things, including the kitchen sink.

I have videos about my solution to this here and here as well as a blog post about it, but essentially, the best experience I have for such queries is to:

Build the statement dynamically - this will allow you to leave out clauses mentioning columns for which no parameters were supplied, and ensures that you will have a plan that is optimized precisely for the actual parameters that were passed with values.
Use OPTION (RECOMPILE) - this prevents specific parameter values from forcing the wrong type of plan, especially helpful when you have data skew, bad statistics, or when the first execution of a statement uses an atypical value that will lead to a different plan than later and more frequent executions.
Use the server option optimize for ad hoc workloads - this prevents query variations that are only used once from polluting your plan cache.

Enable optimize for ad hoc workloads:

EXEC sys.sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure 'optimize for ad hoc workloads', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure 'show advanced options', 0;
GO
RECONFIGURE WITH OVERRIDE;

Change your procedure:

ALTER PROCEDURE dbo.Whatever
  @Status INT = NULL,
  @IsUserGotAnActiveDirectoryUser BIT = NULL
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @sql NVARCHAR(MAX) = N'SELECT [IdNumber], [Code], [Status],
     [Sex], [FirstName], [LastName], [Profession],
     [BirthDate], [HireDate], [ActiveDirectoryUser]
   FROM dbo.Employee -- please, ALWAYS schema prefix
   WHERE 1 = 1';

   IF @Status IS NOT NULL
     SET @sql += N' AND ([Status]=@Status)'

   IF @IsUserGotAnActiveDirectoryUser = 1
     SET @sql += N' AND ActiveDirectoryUser <> ''''';
   IF @IsUserGotAnActiveDirectoryUser = 0
     SET @sql += N' AND ActiveDirectoryUser = ''''';

   SET @sql += N' OPTION (RECOMPILE);';

   EXEC sys.sp_executesql @sql, N'@Status INT, @Status;
END
GO

Once you have a workload based on that set of queries that you can monitor, you can analyze the executions and see which ones would most benefit from additional or different indexes - you can do this from a variety of angles, from simple "which combination of parameters are provided most often?" to "which individual queries have the longest runtimes?" We can't answer those questions based just on your code, we can only suggest that any index will only be helpful for a subset of all of the possible parameter combinations you're attempting to support. For example, if @Status is NULL, then no seek against that non-clustered index is possible. So for those cases where users don't care about status, you're going to get a scan, unless you have an index that caters to the other clauses (but such an index won't be useful either, given your current query logic - either empty string or not empty string is not exactly selective).

In this case, depending on the set of possible Status values and how distributed those values are, the OPTION (RECOMPILE) might not be necessary. But if you do have some values that will yield 100 rows and some values that will yield hundreds of thousands, you might want it there (even at the CPU cost, which should be marginal given the complexity of this query), so that you can get seeks in as many cases as possible. If the range of values is finite enough, you could even do something tricky with the dynamic SQL, where you say "I have this very selective value for @Status, so when that specific value is passed, make this slight alteration to the query text so that this is considered a different query and optimized for that param value."

this is just the original formatted

DECLARE @Status INT = NULL,
        @IsUserGotAnActiveDirectoryUser BIT = NULL

SELECT [IdNumber], [Code], [Status], [Sex], [FirstName], [LastName], [Profession],
       [BirthDate], [HireDate], [ActiveDirectoryUser]
FROM Employee
WHERE (@Status IS NULL OR [Status]=@Status)
AND (            @IsUserGotAnActiveDirectoryUser IS NULL
      OR (       @IsUserGotAnActiveDirectoryUser IS NOT NULL
           AND (     @IsUserGotAnActiveDirectoryUser = 1
                 AND ActiveDirectoryUser <> '')
           OR  (     @IsUserGotAnActiveDirectoryUser = 0
                 AND ActiveDirectoryUser =  '')
         )
    )

this is the revision - not 100% sure about it but (maybe) give it a try
even one OR is probably going to be a problem
this would break on ActiveDirectoryUser null

  WHERE isnull(@Status, [Status]) = [Status]
    AND (      (     isnull(@IsUserGotAnActiveDirectoryUser, 1) = 1
                 AND ActiveDirectoryUser <> '' )
           OR  (     isnull(@IsUserGotAnActiveDirectoryUser, 0) = 0
                 AND ActiveDirectoryUser =  '' )
        )