PostgreSQL won't allow remote connections My Postgres installation won't allow me to connect remotely.
I set the listen_addresses field in the postgresql.conf to '*' and added the line

host    all             all             0.0.0.0/0               md5

to pg_hba.conf, restarted the database and tried

psql dbname uname -h 127.0.0.1

I always get

  Password authentication failed for user "uname"

(also when just setting

host    all             all             127.0.0.1/32               md5

in hba or testing from a remote host).

I can't find any more configurations or suggestions to fix this. I'm running PostgreSQL 9.4 on Ubuntu 15.04 (installed from the repository). Has anyone a suggestion on what to try next? Or am I missing something?

Okay ... Upon installation of postgres another service was using the port 5432 so postgres took 5434. I didn't notice. Works now


EC2 and setting up MYSQL Replication my database size is very big around 100gb and when I restore it takes 3 days to restore. I am using amazon ec2.

My question is I want to setup a new slave to MYSQL master, can I take a snapshot of the Master and create a new instance using the snapshot and then enable replication in between.Given that I take log position before the snapshot and my.cnf changes as required. Snapshot is very fast and takes 1-2hours to create a new machine, whereas mysqldump is taking very very long.

I was just worried if replication works using above method, can anyone help please.

on Amazon system you can use following steps to setup replication for MySQL

Choose a low/off production window
Note Master co-ordinate using show master status
You may consider using flush tables with read-lock, just to make sure you are sure that your co-ordinates doesn't change while you are creating snap shot
take snap shot and restore
Change server-id and relay log and issue change master

Hope it helps


showing table is full on alter mysql In my phpmyadmin , when am adding a new column to my table , mysql throwing me error table xxx is full
i also search on google , but did not get help.

am also gone through dba.stackexchange.com answer realeted to this question , but no help. https://dba.stackexchange.com/questions/43503/error-1114-hy000-the-table-is-full-with-innodb-file-per-table-set-to-aut

This is because your temp folder is full.
you can make a new temp folder where is more space after that let mysql know about your newly created temp folder(dont forget to restart mysql service),
or you can also clear your temp folder, it will work.
you can follow this stackoverflow link for more information ERROR 1114 (HY000): The table 'XXX' is full


Which parameters and metrics to look at when CDC based replication is causing high load? I'm investigating the reasons why a Tungsten Replicator extractor is causing heavy load on an Oracle 11g database. The replication is set up to use synchronous CDC and there is no unusual load while the extractor is not running, which leads me to believe that the triggers registered by the CDC package are working just fine, without causing any significant load.

When the extractor is running, the load average of the database server can go as high as 60, but it's not that high all the time. Most likely the variance depends on the number of writes.

What configuration parameters and/or metrics of the database should I look into to identify the cause of this load? Tungsten seems to be simply running standard CDC procedures as outlined below.

Initial prepartion steps of the extractor:

Get a list of tables to extract from a table prepared by a setup script:

SELECT * FROM TUNGSTEN_SOURCE_TABLES

For each source table, get a list of columns to extract.

SELECT
  UNIQUE CHANGE_SET_NAME,
  PUB.COLUMN_NAME,
  PUB_ID,
  COL.COLUMN_ID
FROM TUNGSTEN_PUBLISHED_COLUMNS PUB, ALL_TAB_COLUMNS COL
WHERE
  SOURCE_SCHEMA_NAME = '{SERVICE_NAME}' AND
  SOURCE_TABLE_NAME = '{TABLE_NAME}' AND
  SOURCE_SCHEMA_NAME = COL.OWNER AND
  SOURCE_TABLE_NAME = COL.TABLE_NAME AND
  PUB.COLUMN_NAME = COL.COLUMN_NAME AND
  CHANGE_SET_NAME = 'TUNGSTEN_CS_{SERVICE_NAME}'
ORDER BY COL.COLUMN_ID

Create a subscription with DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION
Create a subscriber view for each source table with DBMS_CDC_SUBSCRIBE.SUBSCRIBE
Activate the subscription with DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION

Inner loop of the extractor while it's running:

Extend the subscription window with DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW
Select each subscription view to fetch any changes

-- Receiving changes for the first time
SELECT
  COLUMN1, COLUMN2, ...,
  CSCN$,
  COMMIT_TIMESTAMP$,
  OPERATION$
FROM VW_TUNGSTEN_{SERVICE_NAME}{INDEX}
ORDER BY CSCN$, RSID$

-- Receiving changes after the first window
SELECT
  COLUMN1, COLUMN2, ...,
  CSCN$,
  COMMIT_TIMESTAMP$,
  OPERATION$
FROM VW_TUNGSTEN_{SERVICE_NAME}{INDEX}
WHERE CSCN$ > :lastSCN
ORDER BY CSCN$, RSID$

Purge the subscription window with DBMS_CDC_SUBSCRIBE.PURGE_WINDOW

Environment:

Oracle Database 11g Release 11.1.0.6.0 - Production

According to my client, this box is 5 years old
open_mode: read write
log_mode: archivelog
platform_id: 10
platform_name: Linux IA (32-bit)

Tungsten Replicator 3.0.0

I wasn't able to tune up the Oracle side of parameters, due to having limited access to the database instance, but the database load has seen improvements after stripping down the tables and columns that are being replicated.

1. Determine which tables are getting updated the most.

Try running the extractor for a few hours and let the THL grow. Then, you can count the number of changes logged for each table using a command like:

./bin/thl list | gawk -f thl_summary.awk | sort -n -r

Where thl_summary.awk is:

#!/bin/gawk
# Sum up rows from a `bin/thl list` output
BEGIN {
    OFS="\t"
}
match($0, /- TABLE = (.*)/, a) {
    table=a[1]
}
match($0, /- ROW# = (.*)/, a) {
    rows[table]++
}
END {
    for (k in rows) {
        print rows[k], k
    }
}

2. Replicate only those tables and columns that are necessary.

Looking at the list of tables, ask yourself if you can exclude any of them.

Exclude CLOB columns.

They aren't really supported by synchronous CDC.
Even if you specify a subset of the columns in DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE, each and every change will be logged to the change table1. But by excluding CLOB columns, we can lessen the load of costly writes and reads.

Exclude all tables that are not really needed on the downstream database.

If necessary, add a fallback table with a subset of the columns that you really need, even if it means denormalizing a bit.
The way Tungsten configures CDC, a single update to a source row generates two rows in the corresponding change table2. These extra updates can add up if you have a frequently updated table.

Once you've determined which tables and columns to replicate, you can specify them  in a file named tungsten.tables:

aSubsetOfTheTable<TAB>column1,...,columnN
wholeTable

Then, you need to re-run the setupCDC.sh script with setupCDC.conf:

specific_tables=1

1 https://community.oracle.com/thread/2288254

2 Updated rows will generate a record with operation$='UU' ... and all the old values, and another record with operation$='UN' or 'UL' with new values. (source)


SQL Server agent job scripts Being a newbie to database migration, can I script all the database jobs one by one and run them against a new server to create these jobs?

If not, why do we use these SQL job scripts?

Thanks in advance.

can I script all the database jobs one by one and run them against a new server to create these jobs?

Yes

But anything dependent will need to exist, i.e. users, proxies, if there is a path in the task it needs to work against the new server.

Normally there is some kind of migration process, i.e. "I'll need to change all D drives to E, I'll need to change the proxy name" etc.

Why don't you try one and see.


1 hour query, is there another way to do this? I saw a couple questions similar to this but none could answer my question, really.

I have two tables, and I am trying to find those rows where a match is found.
user has about 350,000 entries and match has about 55,000 entries. There are UNIQUE indices on both user.name as well as on match.string.

My query is:

SELECT user.name
FROM user
INNER JOIN match
on (
      (match.type = 'exact' AND user.name = match.string)
   OR (match.type = 'begin' AND user.name LIKE CONCAT(match.string, '%'))
   OR (match.type = 'end' AND user.name LIKE CONCAT('%', match.string))
)

The query is running for an hour now, and no end in sight.
Is there a faster way to do that?

I don't need this for regular production, but I think a query that runs for so long is unacceptable.

No way!  Every 'end' is not optimizable because of the leading wildcard.  That means that if there are, say, a mere 3K 'end' entries in match, then there will be over a billion (350K * 3K) tests to perform!

The query can be partially optimized by

INDEX(type, string) -- in `match`
INDEX(name) -- in `user`
SELECT ... FROM user JOIN match ON match.type='exact' AND user.name=match.string
UNION ALL
SELECT ... FROM user JOIN match ON match.type='begin' AND user.name LIKE CONCAT...
UNION ALL
SELECT ... FROM user JOIN match ON match.type='end' AND user.name LIKE CONCAT...;

The match INDEX minimizes the effort in finding the relevant rows.
The user INDEX is useful for the first two SELECTs.
The UNION lets the optimizer operate on each SELECT independently.
The ALL avoid a de-dupping phase that is (I assume) unnecessary.

Have you considered a FULLTEXT index?  If it works, it will be a million times as fast.  (Perhaps literally a million!)

Here's a kludge that can make the third SELECT sargable (gee, I learned a new word!) -- Reverse both the match.string and the user.name, then do the LIKE like 'begin'.  No, don't use the REVERSE function in this query, actually build the two tables (or maybe a separate pair of tables) with the reversed strings.


VARCHAR memory buffer appears to be breaking in ChineseTaiwan Collation It is difficult for me to write something in English. I hope you understand.
I used a translator and reviewed it.

I created two temporary databases. The collations are 'Korea_Wansung_CI_AS' and 'Chinese_Taiwan_Stroke_CI_AS' respectively.

I created a temporary table with a column of type VARCHAR(2048) and I just created a procedure using INSERT for this table data here.

When invoking the stored procedure from a server that uses c++ oledb, the VARCHAR value is encrypted and sent as UTF-8.

We sent

'한국어' -> 'Korean_Wansung'¨
'韓國人學台' -> 'Chinese_Taiwan'.

I have verified through debugging that the value is encrypted with normal UTF-8.

'한국어' -> UTF-8 -> '\xed\x95\x9c\xea\xb5\xad\xec\x96\xb4'
'韓國人學台' -> UTF-8 -> '\xe9\x9f\x93\xe5\x9c\x8b\xe4\xba\xba\xe5\xad\xb8\xe5\x8f\xb0'

Here I have a problem.

When the table was read back via SELECT statement in the database, we found that for Korean_Wansung, the string 'matched by the value of the Code Page 949' appears normally.

But not for Chinese_Taiwan. The value stored in memory itself appears to be broken.

If you take the value from the server and decode it, the original character will appear normal for Korean_Wansung.

But in the Chinese case, it doesn't come out normally.

I need your help.

If you need additional information, we will quickly get back to you and upload it.

Thank you.

You need to ensure the strings (stored procedure parameters) are sent NVARCHAR

DECLARE @CollationTest table (
     ID int NOT NULL,
     KoreanV varchar(2048) COLLATE Korean_Wansung_CI_AS NULL ,
     KoreanNV nvarchar(2048) COLLATE Korean_Wansung_CI_AS NULL ,
     ChineseTaiwanV varchar(2048) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
     ChineseTaiwanNV nvarchar(2048) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
    )

INSERT @CollationTest VALUES (1,
    '한국어',
    N'한국어',
    '韓國人學台',
    N'韓國人學台'
)

INSERT @CollationTest VALUES (2,
    N'한국어',
    N'한국어',
    N'韓國人學台',
    N'韓國人學台'
)
SELECT * FROM @CollationTest


Receiving error when running DBCC CheckDB - multiple IAM pages Running DBCC CHECKDB (dbname)  WITH NO_INFOMSGS, ALL_ERRORMSGS results in this error:

  Table error:
   Multiple IAM pages for object ID 813245952, index ID 1,
  partition ID 72057594073972736, alloc unit ID 72057594083213312 (type
  In-row data) contain allocations for the same interval. IAM pages
  (1:4611577) and (1:2040821). CHECKDB found 1 allocation errors and 0
  consistency errors in table 'tablename' (object ID 813245952). CHECKDB
  found 1 allocation errors and 0 consistency errors in database
  'dbname'. repair_allow_data_loss is the minimum repair level for the
  errors found by DBCC CHECKDB (dbname).

Has anyone experienced this issue before and have a solution for repair?

Note:- I have not run the statement with repair_allow_data_loss yet.

Several years after op. We ran into this error on SQL Server 2014.
Rebuilding the clustered Index on the table fixed the error
ALTER INDEX pk_table ON dbo.table REBUILD WITH (ONLINE = OFF)

after that DBCC CHECKDB (MyDatabase) ran with no errors.


Best way to store multiple values from multiple daily transactions I'm currently developing a Timeclock web application which will store the date, hour, and minutes which a user logs into and out of the application via a MYSQL Database. Users can log into and out of the application multiple times per day, thus 1 user can have up to 6 transactions per day. What would be the best way to store multiple transactions such as above, in a database? I'm a web developer, and our Database Admin is currently gone for summer, so I apologize if this is a pretty easy question, but I'm confused on how to optimize the storing (and later retrieval) of multiple values in this instance.

I would normally expect that you'd have at least the following:

User table: has user's login_id (probably literally the ID they use to login to the system), FullName, plus other user info (don't have enough info to know what else goes in here - department, job title, etc.). This is technically optional, but is helpful to tie logins to people's actual names.

TimeEntry table: user's login_id, EntryTime (probably using the timestamp data type), EntryType ("I" for login, "O" for logout - this is optional, but helpful if it's possible for login or logout values to be missing (for instance, an involuntary logout due to a power failure)); plus, other relevant values (if any).

The login_id makes sure the entries belong to the right user. The times give us the order in which things happened. As noted, the login/logout flag helps if an entry is missing for some reason. Not sure if this is being captured automatically or if it requires action on the user's part; missing entries much more likely with the latter. I'm assuming that, when populating the EntryTime column, you'll just use CURRENT_TIMESTAMP(); should provide maximum available time data, and help line up very rapid login/logouts if EntryType can't be set. (I've captured similar data from the database end on an MS SQL Server system, and the application would usually have one login that lasted for well under a second before actually connecting for real.)

NOTE: If it's possible to have a login without a logout, EntryType will help you properly match the pairs you have. If you have 5 entries for a user on a given day (say, "8AM", "10AM", "1PM", "4PM", "5PM"), and no EntryType flag, you don't know whether the missing entry is a login or a logout, or where it falls; if that is available (["8AM", "I"]; ["10AM", "O"]; ["1PM", "O"]; ["4PM", "I"]; ["5PM", "O"]), then you can easily identify the unmatched entry, and have at least a minimum confirmed hours logged in (3 hours).

AND: remember to allow for the possibility that login and logout aren't on the same calendar day (if applicable).


Copying database to different hard disk Total noob at database administration here, excuse my ignorance.

I have a server running a Rails application that's using PostgreSQL. The database is saved on drive /z/, which seems to have filled up. I want to put a higher capacity drive and shift the database to that. How should I go about doing this? Should I copy the data folder from drive /z/ to the new drive and then change the config files (could someone point me to what config changes I need to make?)

Or could I copy the data folder to the new hard disk, and then mount the disk as /z/ and then I'll need no config changes?

Or is there some helper commands in postgres that provide a functionality to migrate between disks?

I'm running Ubuntu 12.04.5 LTS. Output of version():

  PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc
  (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

if you add a second disk i suggest to put data on to different tablespaces. a tablespace offers additional storage and is VERY easy to use:

mkdir /whatever

then in SQL:

CREATE TABLESPACE myspace LOCATION '/whatever';
ALTER DATABASE x SET TABLESPACE myspace;

of course you can also do this for single tables only.
and yes, alternatively you can move the entire data dir and tell the system where the new location is.


Passing column names dynamically to UNPIVOT I have a table with following data

First         Second        Third         Fourth        Fifth         Sixth
2013-08-20    2013-08-21    2013-08-22    2013-08-23    2013-08-24    2013-08-25

And using UNPIVOT

SELECT Data
    ,DATENAME(DW, Data) AS DayName
FROM Cal
UNPIVOT(Data FOR D IN (
            First,
            Second,
            Third,
            Fourth,
            Fifth,
            Sixth  )) AS unpvt

I get the following result

Data        DayName
2013-08-20  Tuesday
2013-08-21  Wednesday
2013-08-22  Thursday
2013-08-23  Friday
2013-08-24  Saturday
2013-08-25  Sunday

Now my question is can we pass column names dynamically to the UNPIVOT so that when the columns in the table increases we may not have to alter the statement.

If you are going to have an unknown number of columns that you will need to unpivot, then you will have to look at implementing dynamic SQL.

You can use sys.columns to get the names of all of the columns in your cal table. If you use the following query you will get the list of all of the columns in your table:

select C.name
from sys.columns c
where c.object_id = OBJECT_ID('dbo.cal')

Now you can use this query along with FOR XML PATH to create a comma-separated list of the names to be concatenated to a string to be executed:

select @colsUnpivot
  = stuff((select ','+quotename(C.name)
           FROM sys.columns c
           WHERE c.object_id = OBJECT_ID('dbo.cal')
           for xml path('')), 1, 1, '')

Finally, you will take this list and place it into your query string to be executed so the full query will look like:

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

select @colsUnpivot
  = stuff((select ','+quotename(C.name)
           FROM sys.columns c
           WHERE c.object_id = OBJECT_ID('dbo.cal')
           for xml path('')), 1, 1, '')

set @query
  = 'select data, datename(dw, data) dayname
     from cal
     unpivot
     (
        data
        for d in ('+ @colsunpivot +')
     ) u'

exec sp_executesql @query;

See SQL Fiddle with Demo


How to quota a CDB only tablespace to a CDB user? I can create a CDB tablespace like this: (all using: sqlplus / as sysdba)

-- create unique table space for admin
CREATE TABLESPACE admints DATAFILE '/path/to/admints.dbf' SIZE 20M AUTOEXTEND ON;

I can create a CDB user with a default tablespace "system" since all PDBs have a "system" tablespace:

-- create admin user on CDB (the defaut tablespace is "system")
CREATE USER C##admin IDENTIFIED BY P@ssw0rd DEFAULT TABLESPACE system QUOTA UNLIMITED ON system ACCOUNT UNLOCK;

I can't create my user with default tablespace "admints":

CREATE USER C##admin IDENTIFIED BY P@ssw0rd DEFAULT TABLESPACE admints QUOTA UNLIMITED ON admints ACCOUNT UNLOCK
*
ERROR at line1:
ORA-65048: error encountered when processing the current DDL statement in
pluggable database ORCLPDB
ORA-00959: tablespace 'ADMINTS' does not exist

And I can't give quota to my created user on my created tablespace since it only on the CDB and didn't been created for each PDB. is that correct?

ALTER USER  C##admin quota unlimited on admints unlimited
*
ERROR at line1:
ORA-65048: error encountered when processing the current DDL statement in
pluggable database ORCLPDB
ORA-00959: tablespace 'ADMINTS' does not exist

The "admits" tablespace has been created once only for the CDB, not for each PDB.
I want to create the C##admin user's tables on the "admits" tablespace on the CDB and have an unlimited quota on it.
I want the C#admin to have at least read access on all PDBs, so I can't unload them.
Is it possible?

All containers have a default tablespace:
SQL> select p1.name, p2.property_name, p2.property_value
from v$containers p1 join cdb_properties p2 on (p1.con_id = p2.con_id)
where property_name = 'DEFAULT_PERMANENT_TABLESPACE' order by 1;

NAME       PROPERTY_NAME                  PROPERTY_VALUE
---------- ------------------------------ --------------------
CDB$ROOT   DEFAULT_PERMANENT_TABLESPACE   USERS
PDB1       DEFAULT_PERMANENT_TABLESPACE   USERS
PDB2       DEFAULT_PERMANENT_TABLESPACE   SALES

Create the user without specifying anything:
SQL> create user c##myadmin identified by password;

User created.

SQL> select p1.name, u.default_tablespace
from v$containers p1 join cdb_users u on (p1.con_id = u.con_id)
where username = 'C##MYADMIN' order by 1;

NAME       DEFAULT_TABLESPACE
---------- ------------------------------
CDB$ROOT   USERS
PDB1       USERS
PDB2       SALES

Then alter the user in the root container:
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> alter user c##myadmin default tablespace admints quota unlimited on admints container=current;

User altered.

SQL> select p1.name, u.default_tablespace
from v$containers p1 join cdb_users u on (p1.con_id = u.con_id)
where username = 'C##MYADMIN' order by 1;

NAME       DEFAULT_TABLESPACE
---------- ------------------------------
CDB$ROOT   ADMINTS
PDB1       USERS
PDB2       SALES

SQL> select c.name, q.tablespace_name, q.max_bytes
from v$containers c join cdb_ts_quotas q on (c.con_id = q.con_id)
where q.username = 'C##MYADMIN';

NAME       TABLESPACE_NAME                 MAX_BYTES
---------- ------------------------------ ----------
CDB$ROOT   ADMINTS                                -1


How to log ALL queries made by users in SQL Server 2017 Standard? Is there a possibility to do this? All queries and actions performed in any database need to be logged, because of in house compliance.

I need information (and/or source) if we need MS SQL 2017 Standard or Enterprise in order to ensure that the DB Admin's activities (run, modify, etc queries...) are logged and not only the activities performed by non-admin users.

Common Criteria Compliance is an enterprise feature

In the standard edition, well any edition,  you can create a trace and trace "Audit Schema Object Access Event" that will give you a statement level audit. You can then filter on objects or databases to minimize the logs and create the trace as a server side trace.

The Audit Schema Object Access Event seems not to be accessible using extended events but the server side trace can be set up to stop the server if logging is no longer possible and is detailed enough to be acceptable as a compliant audit for PCI requirements

As I am corrected below, in later versions of SQL Server you can setup a database audit in all editions and audit all statements against a database or specific objects.


MySQL Host 'host_name' is blocked - Which Database? We are getting the error that a host is blocked on MySQL

Host 'host_name' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'

I understand how to clear this error (Flush Hosts),

However we are a shared hosting company and would like to know to which database the connections are failing in order to rectify the issue and notify the customer. We have many databases and many customer websites.

We are running MySQL 5.5 - so the 'host cache' table does not exist (not sure if that would help anyway)

I am not sure that you can get the database, but you can identify the user with one of the following methods:

The general log
The error log (log_warnings = 2)
An extra patch called user statistics (included by default in Percona and MariaDB)
An audit plugin like McAfee's

The first three are mentioned on this article on MySQL performance blog.

Sadly, visilibity on the host cache was only added until 5.6 on performance_schema.host_cache.

As a temporary workaround, you would be required to set max_connect_errors to a large value instead of 10, and set MAX_CONNECTIONS_PER_HOUR and MAX_USER_CONNECTIONS per user to an appropriate value (you should be using different users if you are not, and/or different DNS names).


SSMS Permission Denied, Login with Windows Authentication My SSMS(SQL Server Management Studio) version 16.4, Windows 7. First install is normal use, sometimes i reinstalling my Windows and just now i install SSMS again, and got this problem.
I cannot do anything except view.

Object Explorer & Registered Servers Display

BUILTIN\Users (ServerRole: Public) > using windows authentication
sa (ServerRole: Public, Sysadmin) > SQL Server Authentication (with default password)

I cannot create any database, restore or anything that change the content of my server. Also i have try to Add new login, and i don't have permission to create new login. I run the SSMS as Administrator, but i still cannot do anything.

Anyone know how to add new logins from out of application. Maybe i can connect to Server with SQL Server Authentication that have all permission that required. Or any way to solve this permission problem.

Thanks

Attach Database -> Add

Restore Database

Try something to do

Error when add ServerRole to my BUILTIN\Users

Connection Properties if needed.

You are just a member of BUILTIN/Users when connecting to SQL Server, your USER\Administrator is not a member of sysadmin role, so you have no server permissions. Try to execute

select *
from sys.fn_my_permissions(null, 'server');

and all you see will be CONNECT SQL + VIEW ANY DATABASE.
Of course you cannot use DAC, you are NOT sysadmin to be able to do it.

What you should do instead is launch your server in single user mode, and as you are local admin, you'll be able to log in as sysadmin.
After that you should add yourself to sysadmin role.

Here are the steps to perform: Connect to SQL Server When System Administrators Are Locked Out


identify queries experiencing sos_scheduler_yield Is there a way to capture the queries experiencing the SOS_Scheduler_Yield wait type as it is happening.  My cpu is pegging out at night and I suspect its some xml processing that is causing it by chewing up all my processor cycles; however, the application design is such that what is coming back as having the highest worker time is a section of xml that should only be run one time per client.  So I need to catch the individual offending query each night to make my case.

The Extended Events option is by far the easiest solutions, once you get used to Extended Events.

Explanation

An article on SQLPerformance.com goes into the specifics behind this wait type...

  ...
  So there is one cause for SOS_SCHEDULER_YIELD: a thread exhausting its scheduling quantum and heavily recurring instances can lead to SOS_SCHEDULER_YIELD being the most prevalent wait along with high CPU usage.
  ...
  The only way to prove what's causing SOS_SCHEDULER_YIELD waits is to capture SQL Server call stacks when that wait type occurs, using Extended Events and debug symbols from Microsoft. I have a blog post that describes and shows how to perform that investigation, and there’s a great whitepaper about spinlocks and spinlock investigations that is worth reading if you’re interested in that depth of internals.

Reference: Knee-Jerk Wait Statistics : SOS_SCHEDULER_YIELD

It also relates to how you can perform an analysis (blog in above quote):

Reference: SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock (SQLSkills.com)

Extended Events

Add an Extended Event to your instance with the following code:

CREATE EVENT SESSION [Capture_Statements_SOS_SCHEDULER_YIELD] ON SERVER
ADD EVENT sqlos.wait_info(
    ACTION(sqlserver.client_hostname,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([wait_type]=(123)))
ADD TARGET package0.event_file(SET filename=N'C:\Temp\Capture_Statements_SOS_SCHEDULER_YIELD.xel')
WITH (STARTUP_STATE=ON)
GO

  Caution
  Check the filename and modify to your preference!
  Check the STARTUP_STATE!
  Modify the Extended Event to your preferences when finished.

Reference: Extended Events (Microsoft Docs)

Quick solution

A quick solution is to just run the following script when you are having issues:

SELECT sdow.session_id,
       sdow.wait_type,
       sder.status,
       sder.sql_handle,
       DB_NAME(sder.database_id),
       dest.text
FROM   sys.dm_os_waiting_tasks    AS sdow
       JOIN sys.dm_exec_requests  AS sder
            ON  sdow.session_id = sder.session_id
       CROSS APPLY sys.dm_exec_sql_text(sder.sql_handle) AS dest
WHERE  sdow.wait_type = 'SOS_SCHEDULER_YIELD'

References

Knee-Jerk Wait Statistics : SOS_SCHEDULER_YIELD (SQLPerformance.com)
SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock (SQLSkills.com)
sys.dm_exec_requests (Transact-SQL) (Microsoft Docs)
sys.dm_os_waiting_tasks (Transact-SQL) (Microsoft Docs)
sys.dm_exec_sql_text (Transact-SQL) (Microsoft Docs)
Extended Events (Microsoft Docs)


List population of provices (SQL Query) I was asked this question in an interview and I could not get my mind around it, and now it is bothering me a lot!

If you have a table CANADA(PROVINCE, CITY, POPULATION).
- Population is correlated with the population of one city.
- There are multiple cities in a province.

Question:
Write a query that would list the total population of each Province.

I am a beginner when it comes to SQL related questions. Knoweldge of SQL was not in my job description, but they just wanted to know if I could solve it.

I came up with this:
SELECT PROVINCE, POPULATION
WHERE CANADA
GROUP BY PROVINCE

However I did not know how to move forward with this. I would like to know of I am going the right direction please!

You need to include the aggregate operation to get the sum of population.  So:

SELECT Province, SUM(Population) AS PopulationSum FROM CANADA GROUP BY Province

In english it would be, I want the sum of the population column, by province from the Canada table.


A Non-DBA Asks: How to Painlessly Copy/Move a SQL Server instance to another Server via Backup/Restore? We've just got a new server so I need to copy the existing SQL Server 2008 instance over from the old db box.

I normally do this by copying .mdf and log files over and attaching them but I'm not able to do take the dbs off line as they're used 24/7 so I've backed up the databases and restored them on the new machine. However I've ended up with a few issues one of them being related to orphaned users. So what I'm looking for is a fail-safe process to move everything across with minimal fuss/bother/headache. I'm a .Net dev with a fair amount of SQL under my belt but the inner workings of SQL Server are largely a mystery to me and I find the MS documentation painful to trawl.

Please help.

If you cannot take the databases offline, tne you need to do backup/restore. I would suggest the following:

Install SQL 2008 on the new box, using the same file structure as the old box for MDF and LDF files.
Take backups of all databases on the old box.
Restore master from the old box to the new box having the SQL started in single user mode. Restore master method
Restore each database from the old box to the new box with NORECOVERY option in order to be able to apply future Diff or T-log backups.
Restore msdb from the old box to the new box.

If you build and configure the new box to look exactly like the old one, then you will have minimal fuss.

I have no idea how big your databases are, so this method could take a long time. The easiest thing to do would be to shut down the old box and copy the MDF and LDF files and then (after restoring master) you would only need to restart SQL to have your databases online. But you stated that is not an option, as the databases are not allowed to be offline.


I was interrupted while transferring a very large table. How can I pick it up from where it stopped? I wrote a python script to select and insert rows between tables in different databases (from SQL Server to Postgres). the table has around 2000000000 rows and the transfer stopped somewhere in the middle of it. I tried to select the rows with an offset as the number of rows already transferred. But count(*) takes up too much server resources and the server becomes unresponsive when I try. (memory usage rises from 100MB to ~8GB in a very short time)

I'm wondering if there's a way to pick up the transfer from where it stopped. It took around 3 days to transfer the number of rows I currently have in Postgres.

Thanks.

Also, I know the approximate number of the rows. So maybe I can do something like this: if there are approximately 12392320 rows, delete everything after the 12390000th row and just start from 12390001. Is something like that possible?

An alternative view:

Are you really using inserts for 2B rows?  You might be better off bulk loading the data.  2B rows shouldn't take 3 days unless they are spectacularly wide.

Also, pg_bulkload might be of interest for this.


Track deleted rows/records A project has the requirement that it shall not be possible to lose data from certain tables. That is, if I "delete" a row, that row's information should somehow be preserved.

Additional information

It is fairly unlikely that anyone would actually ever be interested in the deleted records, so speed of access is not particularly important.
The records being deleted may be the primary key for other records in the database; if they are the primary key, the delete will cascade to the foreign records.
The database will primarily be accessed through an API, so I can put the logic there if that's really the best place.

On SQL Server (2005 or later), what is the best way to accomplish such a thing?

As a note, the internet has provided me with a multitude of solutions. One seems to stand out as the best choice, but I can see some caveats. Rather than asking whether my solution is good, it seems better to just ask for a good solution. If it would be helpful, I can edit in my ideas.

It sounds from your comment on Marlon's answer like these are tables whose structure changes frequently. That could make the trigger solution unwieldy.

In that case, investigate the alternative of doing a Transactional Replication publication and excluding DELETE transactions from the publication. INSERT, UPDATE, and DDL changes would be replicated to your destination table. For SQL 2005, this is the optimal solution.

If you were to upgrade to SQL 2008, you could make use of Change Data Capture. That would be the most robust solution, and easier to maintain than the Replication method. But I know getting the approval/budget/time for such upgrades is not always easy. :)


Good video tutorials/series for learning Oracle? I am a better learner with visual content. Therefore I actively seek video tutorials when I start learning a subject. For oracle below links are my finds. I would like some additions to these.

Oresoft youtube Oracle Server Concept
http://www.youtube.com/user/TheOresoft#grid/user/8A9FE158DD9759AD
Oracle DBA Justin
http://www.youtube.com/user/jbleistein11#p/u
Oresoft Oracle SQL
http://www.youtube.com/user/TheOresoft#grid/user/EB058E78A206806C
Oresoft Oracle PLSQL
http://www.youtube.com/user/TheOresoft#grid/user/3245012E0631F7AE
Oracle VTC
http://www.vtc.com/products/oracle-10g-tutorials.htm

Search the Oracle Learning Library / OBE (Oracle-By-Example)


Backup of MongoDB collection data and rename the collections I want to take a collections data backup on a different host, with renaming the Collections.

I am very new to MongoDB. Please help me with this task.

You can with this command rename your collection:
    db.rrecord.renameCollection("record")

Then backup with this:
    mongodump --host mongodb1.example.net --port 3017 --username user --password "pass" --out /opt/backup/file

Tip: You can specify different host for save your backup file.


Error after SQL-Server Update: The prepared statement with handle xyz was not found In the last weeks we updated two of our customers. Both got a new SQL-Server and both have now this error: "The prepared statement with handle xyz was not found."

The error occures several times throughout the day but not on all mid tier servers. If the error arises the transaction stops. In some cases the mid tier server shows this error for some minutes (not to all users) and works correct some time later. In most cases we have to restart the mid tier service.

This is what changed:

both have now a VMWare VM (prior it was a Hardware Server)
Windows Server changed from Win 2008 / Win 2012 to Win 2016
SQL Server version changed from 2012 to 2017 (customer a) / 2014 to 2016 (customer b)

The error is logged in the Windows Eventlog on mid tier servers that interact with the databases and the users get a appropiate message box.
We don't see this at the SQL server. Maybe we don't look at the right log...

Do you have any hint or suggestion or advice what we can do, where we should look, what to change to get rid of this error?

Our customers use MS Dynamics NAV 2013R2.

Thanks in advance
Heinrich

You can potentially find the error message through Extended Events. Create and start the following session:

CREATE EVENT SESSION FailedQueries
ON SERVER
 ADD EVENT sqlserver.error_reported
 (
   ACTION
   (
     sqlserver.client_app_name,
     sqlserver.client_hostname,
     sqlserver.nt_username,
     sqlserver.sql_text
    )
  )
  ADD TARGET package0.asynchronous_file_target
  (
    SET FILENAME = N'C:\temp\FailedQueries.xel',
    METADATAFILE = N'C:\temp\FailedQueries.xem'
  );
GO

ALTER EVENT SESSION FailedQueries ON SERVER STATE = START;
GO

To make sure it is working, run a query that will definitely raise an exception:

SELECT 1/0 FROM sys.objects;

Check the session to make sure this event populated:

;WITH event_data AS
(
  SELECT data = CONVERT(XML, event_data)
    FROM sys.fn_xe_file_target_read_file(
   'C:\temp\FailedQueries*.xel',
   'C:\temp\FailedQueries*.xem',
   NULL, NULL
 )
),
results AS
(
  SELECT
    [host]      = data.value('(event/action[@name="client_hostname"]/value)[1]','nvarchar(4000)'),
    [app]       = data.value('(event/action[@name="client_app_name"]/value)[1]','nvarchar(4000)'),
    [date/time] = data.value('(event/@timestamp)[1]','datetime2'),
    [query]     = data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)'),
    [error]     = data.value('(event/data[@name="error_number"]/value)[1]','int'),
    [state]     = data.value('(event/data[@name="state"]/value)[1]','tinyint'),
    [message]   = data.value('(event/data[@name="message"]/value)[1]','nvarchar(250)')
  FROM event_data
)
SELECT * FROM results
WHERE error NOT IN (5701,5702,5703)
ORDER BY [date/time] DESC;

Then you can periodically check the output of this query (maybe adding a filter like AND [message] LIKE N'%prepared statement%'). Hopefully that gives you some additional clues.


move content from one table to another table and add a new value I need to move content from one table to another table, this part is done:

INSERT INTO `insc_mwc`
(`nome`,`empresa_idempresa`,`cat_mwc_idcat`,`points_pontos`,`n_registo`)
SELECT nome,empresa_idempresa,cat_mwc_idcat,0
FROM temp_mwc WHERE temp_mwc.empresa_idempresa=$id;

How I can give value to n_registo, this value is the same for each time the query is executed.

output of  SHOW CREATE TABLE insc_mwc

CREATE TABLE `insc_mwc` (
 `idwater` int(11) NOT NULL AUTO_INCREMENT,
 `nome` varchar(45) DEFAULT NULL,
 `categoria` varchar(45) DEFAULT NULL,
 `empresa_idempresa` int(11) NOT NULL,
 `mwccar_idcat` int(11) NOT NULL,
 `points_pontos` int(11) NOT NULL,
 `n_registo` varchar(45) DEFAULT NULL,
 PRIMARY KEY (`idwater`),
 KEY `fk_water_empresa1_idx` (`empresa_idempresa`),
 KEY `fk_water_mwccar1_idx` (`mwccar_idcat`),
 KEY `fk_water_points1_idx` (`points_pontos`),
 CONSTRAINT `fk_water_empresa1` FOREIGN KEY (`empresa_idempresa`) REFERENCES `empresa` (`idempresa`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_water_mwccar1` FOREIGN KEY (`mwccar_idcat`) REFERENCES `cat_mwc` (`idcat`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_water_points1` FOREIGN KEY (`points_pontos`) REFERENCES `points` (`pontos`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Your table needs to be like this

CREATE TABLE `insc_mwc` (
 `idwater` int(11) NOT NULL AUTO_INCREMENT,
 `nome` varchar(45) NOT NULL, -- Make nome NOT NULL
 `categoria` varchar(45) DEFAULT NULL,
 `empresa_idempresa` int(11) NOT NULL,
 `mwccar_idcat` int(11) NOT NULL,
 `points_pontos` int(11) NOT NULL,
 `n_registo` INT DEFAULT 0,
 PRIMARY KEY (`idwater`),
 UNIQUE KEY (`nome`), -- Add unique index on nome
 KEY `fk_water_empresa1_idx` (`empresa_idempresa`),
 KEY `fk_water_mwccar1_idx` (`mwccar_idcat`),
 KEY `fk_water_points1_idx` (`points_pontos`),
 CONSTRAINT `fk_water_empresa1` FOREIGN KEY (`empresa_idempresa`) REFERENCES `empresa` (`idempresa`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_water_mwccar1` FOREIGN KEY (`mwccar_idcat`) REFERENCES `cat_mwc` (`idcat`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_water_points1` FOREIGN KEY (`points_pontos`) REFERENCES `points` (`pontos`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

To get this, first make sure nome is unique by running this:

SELECT nome,COUNT(1) DupCount FROM insc_mwc GROUP BY nome HAVING COUNT(1) > 1;

If you get nothing back (Empty Set) from this query, then you can safely run

ALTER TABLE insc_mwc MODIFY n_registo INT DEFAULT 0, ADD UNIQUE INDEX (`nome`);

With a unique nome and n_registo as an INT, you can increment n_registo for each time there is a DUPLICATE KEY on nome. The INSERT can now be done like this:

INSERT INTO `insc_mwc`
(`nome`,`empresa_idempresa`,`cat_mwc_idcat`,`points_pontos`)
SELECT nome,empresa_idempresa,cat_mwc_idcat
FROM temp_mwc WHERE temp_mwc.empresa_idempresa=$id
ON DUPLICATE KEY UPDATE n_registo = n_registo + 1;


Oracle 11g varchar2 datatype with date and time as YYYY/MM/DD-HH24:MI:SS:FF3 I am working with a column 'LastUPDATE' in a database that is datatype of varchar2.
This column is listed as YYYY/MM/DD-HH24:MI:SS:FF3 format.
Example: '2012/09/06-21:17:30:019'

My first issue is that I cannot change the database or change this format due to the application that usses the data in this date and time format.

I need to know how I can work with only the date part, extracting it so that I can calulate the date from 30 days and so on.

I have been able to extract for the view by RegExpections but not able to get the data for calculation.

I am familar with Oracle because i have to work with the data in my work but not god at the date and time manipulation.

i have searched this site and others with no success on the same issue.

Can an Oracle person help me out.
Oracle 11g
Thank you
E

First you need to convert it to TIMESTAMP datatype (because DATE type does not handle fractions of second). Then you can truncate it to date for example:

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

with data as (select '2012/09/06-21:17:30:019' as lastupdate from dual)
select
  trunc(to_timestamp(lastupdate, 'YYYY/MM/DD-HH24:MI:SS:FF3')) date_only,
  trunc(to_timestamp(lastupdate, 'YYYY/MM/DD-HH24:MI:SS:FF3')) + 30 date_only_30
from data;

DATE_ONLY           DATE_ONLY_30
------------------- -------------------
2012-09-06 00:00:00 2012-10-06 00:00:00


Mariadb clustering Index design question Here are the columns of concern for a table describing tasks:

Org_ID
Task_ID
Task_property_columns

The only requirement for Task_ID is that it is unique per every Org_ID, however I would prefer not handle that programatically and that it be auto incremented.

The requirement for the Org_ID is that I would like to run metrics and analytics using sort operations with the table organised by Org_ID and ported nationally some of the task properties. The table being clustered on Task_ID would be useless,  but would be very beneficial to generate on Org_ID.

From reviewing the documentation, it seems that clustering is picked for you in MariaDB where it will use the primary key, a unique non-nullable index, or a background auto generated index in that order.

I am pretty  new to MariaDB and was hoping for some help in picking the right solution. From what I understand I can't find a good way to auto increment and cluster how I want it to as well.

First, to clarify: "cluster" in this Question refers to the "clustering" of the PRIMARY KEY with the data, not the "clustering of servers".

You have provided the 3 choices that InnoDB makes to picks the clustered PK.  Let me rephrase it from a user point of view.

If you have a column, or combination of columns, (all non-NULL) that are inherently UNIQUE, then use it as the PK.  This is a "natural" PK.
If choice 1 is not available, use an AUTO_INCREMENT.
Though available, do not use the third choice of an auto-generated hidden PK.  Some day, you will need to uniquely identify rows, perhaps for maintenance.

Unlike some other vendors, there is no ROWNUM, nor is there a non-clustered PK.  The lack of those is not important.

Secondary keys let you get at the data in other ways.  A secondary key contains a copy of the PK so that it can get to the data.  Both PK and secondary keys are organized as BTrees.  BTree is the best all-around indexing method; the lack of Hash, etc, is not important.

A natural PK lets you do a range scan over the PK column very efficiently.

If find that two-thirds of tables have a "natural" PK; only one-third need an auto_increment instead.

Back to your 'task'.  It sounds like you want

PRIMARY KEY(org_id, task_id)

but would like task_id to be automatically generated.  This is possible by saying

task_id, INT UNSIGNED AUTO_INCREMENT NOT NULL

but there will be gaps -- since the auto_inc is table-wide, not specific to a given org_id.  If you can live with the gaps, then that is your answer.

This was a feature that existed in ENGINE=MyISAM.  See this for a simulation of it using InnoDB.

You mentioned "ported nationally".  Are you trying to create unique ids that are locally, not centrally, generated?  See UUID and its evils.

If you are summarizing large quantities of data, then build and maintain Summary tables.

If you want to discuss further, please provide prototypes of schema and queries.


How to shred .docx XML? I am trying to import a xml (actually a docx file) to a sql server 2008 database. I am almost a novice in XML programming. I googled a lot but almost all the examples there are with simple xml file. Here the xml file is little complex(please see below). Can you please give me some idea how I should create the table for this XML and what query should I run in sql server. I need values for all the tags e.g. w:rsidP,w:rsidRDefault,w:rsidR of w:p,w:pStyle,w:bookmarkStart,w:t tags etc.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<w:document xmlns:ve="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:m="http://schemas.openxmlformats.org/officeDocument/2006/math" xmlns:v="urn:schemas-microsoft-com:vml" xmlns:wp="http://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing" xmlns:w10="urn:schemas-microsoft-com:office:word" xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main" xmlns:wne="http://schemas.microsoft.com/office/word/2006/wordml">
<w:body>
<w:p w:rsidR="00EF42E0" w:rsidRDefault="00EF42E0" w:rsidP="00EF42E0">
<w:pPr><w:pStyle w:val="Heading1"/>
</w:pPr><w:bookmarkStart w:id="0" w:name="_Toc212523610"/>
<w:r>
<w:t>Summary</w:t>
</w:r>
<w:bookmarkEnd w:id="0"/>
</w:p>
<w:p w:rsidR="00EF42E0" w:rsidRDefault="00EF42E0" w:rsidP="00EF42E0"><w:pPr><w:pStyle w:val="mainbodytext"/><w:ind w:right="-694"/><w:rPr><w:b/><w:bCs/></w:rPr></w:pPr><w:r><w:rPr><w:b/><w:bCs/></w:rPr><w:t>What is the Group Defined Practice for Integrity Management?</w:t></w:r></w:p>
<w:p w:rsidR="00EF42E0" w:rsidRDefault="00EF42E0" w:rsidP="00EF42E0"><w:pPr><w:pStyle w:val="mainbodytext"/></w:pPr><w:r><w:t xml:space="preserve">This Practice is derived from the GP Group Standard, GRP 01 January 2006, </w:t></w:r><w:proofErr w:type="gramStart"/><w:r><w:t>Integrity</w:t></w:r><w:proofErr w:type="gramEnd"/><w:r><w:t xml:space="preserve"> Management.  In developing QMS it has been possible to embed much of the content of the IM Standard directly into the Group Essentials statements.  For elements 2, 7, 8 and 9 of the Standard it was possible to do that in their entirety and therefore content of those elements are not repeated within this Practice.</w:t></w:r></w:p></w:body></w:document>

When working with XML in SQL Server you use the xml Data Type Methods and when shredding XML documents you typicly use the nodes() and value() methods. The XML you have here also include a number of namespaces so you have to specify the ones you need using WITH XMLNAMESPACES (Transact-SQL).

The XML is quite complex so without knowing how you want the data extracted I can only give you a couple of sample queries that you then can modify to whatever it is you need.

You have four w:p nodes and here is a query that fetches the attributes from those nodes. Using @ specifies that it is the value of an attribute you want-

with xmlnamespaces('http://schemas.openxmlformats.org/wordprocessingml/2006/main' as w)
select P.X.value('@w:rsidR', 'char(8)') as rsidR,
       P.X.value('@w:rsidRDefault', 'char(8)') as rsidRDefault,
       P.X.value('@w:rsidP', 'char(8)') as rsidP
from @doc.nodes('/w:document/w:body/w:p') as P(X);

SQL Fiddle

If you in addition to that want the text in the w:t node you need to do a cross apply to a second nodes() clause that will shred the XML within the w:p node.

with xmlnamespaces('http://schemas.openxmlformats.org/wordprocessingml/2006/main' as w)
select P.X.value('@w:rsidR', 'char(8)') as rsidR,
       P.X.value('@w:rsidRDefault', 'char(8)') as rsidRDefault,
       P.X.value('@w:rsidP', 'char(8)') as rsidP,
       T.X.value('text()[1]', 'nvarchar(max)') as Text
from @doc.nodes('/w:document/w:body/w:p') as P(X)
  cross apply P.X.nodes('w:r/w:t') as T(X);

SQL Fiddle

You said in your question that you want to get the values from all the tags. I don't know how useful this is to you but you can build a Name-Value list with all the attributes and elements in the XML.

This will give you all elements.

select T.X.value('local-name(.)', 'nvarchar(max)') Name,
       T.X.value('.', 'nvarchar(max)') Value
from @doc.nodes('//*') as T(X)

Change '//*' to '//@*' and you will get all attributes.

select T.X.value('local-name(.)', 'nvarchar(max)') Name,
       T.X.value('.', 'nvarchar(max)') Value
from @doc.nodes('//@*') as T(X)

And you can combine them in one query as well.

select T.X.value('local-name(.)', 'nvarchar(max)') Name,
       T.X.value('.', 'nvarchar(max)') Value
from @doc.nodes('//@*, //*') as T(X)

SQL Fiddle


Data guard broker cascaded standby delay parameter is ignored I have 3 databases: db01 primary, db02 standby cascading, db03 standby cascaded.

I have configured broker parameters by the following way:

DGMGRL> show configuration
Configuration – DB_HQ_DR
Protection Mode: MaxPerformance
Members:
db01 – Primary database
db02 – Physical standby database
db03- Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

DGMGRL> edit database ‘db01′ set property RedoRoutes='(LOCAL:db02)(db02:db03)’;
Property “redoroutes” updated

DGMGRL> edit database ‘db02′ set property RedoRoutes='(LOCAL:db01)(db01:db03)’;
Property “redoroutes” updated

--Configured delaymins parameter:

DGMGRL> edit database ‘db03′ set property DelayMins=’21600’;

When selecting log_archive_dest_2 parameter on db02 I see that there is delay parameter 21600 as it should be.

SQL> show parameter log_archive_dest_2

log_archive_dest_2   |  service="db03",  delay=21600 optional compression=disable  max_failure=0 max_connections=1 reopen=300

  db_unique_name="db03" net_timeout=30, valid
  _for=(standby_logfile,all_roles)

DGMGRL> show configuration
Configuration – DB_HQ_DR
Protection Mode: MaxPerformance
Members:
db01 – Primary database
db02 – Physical standby database
db03- Physical standby database (receiving archived redo)
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

Problem is that apply process is not delayed. It applied as soon as there is generated archive log.
What happens? Why delay parameter is ignored?

If you want delay apply on Cascaded Standby you need to set the DELAY of LOG_ARCHIVE_DEST_n on Primary.

  The DELAY value that a cascaded standby uses is the value that was set for the LOG_ARCHIVE_DEST_n parameter on the primary that shipped the redo to the cascading standby.

Reference:DELAY


Multiple instance in one server Could you please help to explain why one might need multiple instances of SQL Server on the same server? What is a good point to use separate instances? Why wouldn't one use two databases in one instance instead? At any point is it a good idea to have separate multiple instance in the same server?

The idea of having multiple instances on one server can have various reasons.

1. SQL Server License Costs

You can have multiple instances all running on one (big?) server and not have to pay large amounts of licensing fees.

  Disclaimer
  This depends on your licensing model, your license agreement and probably requires additional clearance by your certified reseller / licensor. Do not take this statement for granted. Your costs/licenses may vary. No guarantee given.

2. Upgrades / Service Packs / Cummulative Updtes / Hotfixes

Having multiple instances on one (big?) server, allows you to patch the individual instances to different levels. You can have once instance running Service Pack 1 and another instance running Service Pack 2. And on another instance you could even test drive the hotfix you're not sure it will work well.

3. Permissions / SQL Server Roles / ....

Having multiple instances allows you to assign the sysadmin server role to individuals, without giving them the ability to destroy all the databases if they would be located on one instance. You have a separation between databases of one customer and another customer.

4. Windows Server Licenses

You can save on Windows Server licenses, because you only require one server license for multiple SQL Server instances.

  Disclaimer
  This depends on your licensing model, your license agreement and probably requires additional clearance by your certified reseller / licensor. Do not take this statement for granted. Your costs/licenses may vary. No guarantee given.

5. Individual IP Addresses for Each SQL Server Instance

You can setup the individual SQL SErver instances to only respond to one IP address which in turn is linked to a CNAME/ALIAS. e.g. you could have your Windows Server running on 10.0.0.1 and each of your SQL Server instances running on 10.0.0.2 ... 10.0.0.5 which in turn would have their own CNAME:

10.0.0.1   hostname
10.0.0.2   hostanme-alias1
10.0.0.3   hostname-alias2

Each instance is then only reachable via the IP address and Port 1433 which in turn allows for IP to IP firewall configuration.

See my question Configure SQL Server Analysis Services (SSAS) to run on same IP as SQL Server instance for an example of how you could configure multiple instances. Or the question Multi-instance SQL Server Standard Editon MaxDop settings where I show how we setup multiple insances at our shop.


Storage provisioning for Microsoft SQL Server I need help with my Microsft SQL Server file setup!

I have a TEST Microsoft SQL Server with 3 RAID1 (SSDs):

C: (240 GB)  - O/S installed
L: (460 GB)
M: (900 GB)

I have a PROD Microssoft SQL Server with 4 RAID1 (SSDs):

C: (240 GB) - O/S installed
L: (900 GB)
M: (900 GB)
T: (240 GB)

What is the best way to spread the SQL files for Test and Prod Servers?

A back-of-the envelope "guess" would be to put the log files on one drive, the data files on another drive, and in the case of prod, put the tempdb files on a third drive.  You should also consider a location for SQL Server backup files that is not on the same drive subsystem as either your data or log files; preferably an off-machine location.

Having said that, you've provided too little data for us to really help you decide on the highest performance setup.


Need Help maintaining multiple databases for a multi-tenant application? i need some information about multiple databases for a multi-tenant application ?

I am working on a project in which we are maintaining separate database for every company?

The Companies Database contain customer Data.

There is a common Database to store common information across these databases. Example Customer ID,Customer Contact Number and Customer Email etc.

A New Database is created for every new company.

If one customer belongs to company1 and same customer is added in company 2
then we need to give same customer id from company 1 to that customer across all companies.

There are many such scenarios similar to the above example.

Can any one tell me how to manage multiple databases(companies) and same customers across all companies.

You should store all information that is common for all companies in one database and copy the data that are needed to the database that needs the information.
Adding a new customer would be done in the common db.
After that the information will be copied to company1.
If it's needed in company2, then the data will be copied to company2.
Make sure you also add an update process to keep the copied information in sync.
Alternatively, you could keep the customer information in the main db and link to it. However, this would only be recommended if all db's are on the same SQL Server instance.


Don't know how to transform variable entity into relational table INTRODUCTION AND RELEVANT INFORMATION:
The following example illustrates the problem I face:
Animal has a race, which can be a cat or a dog. Cat can be either Siamese or Persian. Dog can be a German shepherd or Labrador retriver.
Animal is a strong entity, while its race is an attribute that can have one of the two offered values ( cat or a dog ). Both these values are complex ( I have added here only the type of dog/cat to illustrate the problem, but there can also be the cat's / dog's name and bunch of other stuff ).
PROBLEM:
I don't know how to create relational tables for this example.
MY EFFORTS TO SOLVE THE PROBLEM:
I have tried to draw ER diagram, using Chen's notation, that represents the problem but being a beginner I don't know if I did it right. Here is what I have got:

I apologize if I drew something wrong, please correct me if that is the case. I don't wish to simply get "free solution" but also to learn how to deal with this problem so I can solve it on my own in the future.
The only thing that comes to my mind is to create two separate tables, one for cats and one for dogs. Also, the race attribute in the Animal table would only store cat or a dog value. Something like this:
Animal< # Animal_ID, race, other attributes >
Cat < # Cat_ID, $ Animal_ID, breed >
Dog < # Dog_ID, $ Animal_ID, breed >

I really have a bad feeling about my solution and I fear it is wrong, hence the below question.
QUESTIONS:

How can I transform my example into ER diagram ?
How to transform that ER diagram into relational tables?

If further info is required leave a comment and I will update my post as soon as possible. Also feel free to add appropriate tags since I am fairly new here.
Thank you.

The proper structure for this scenario is a SubClass / Inheritance model, and is nearly identical to the concept I proposed in this answer: Heterogeneous ordered list of value.

The model proposed in this question is actually quite close in that the Animal entity contains the type (i.e. race) and the properties that are common across all types. However, there are two minor changes that are needed:

Remove the Cat_ID and Dog_ID fields from their respective entities:

The key concept here is that everything is an Animal, regardless of race: Cat, Dog, Elephant, and so on. Given that starting point, any particular race of Animal doesn't truly need a separate identifier since:

the Animal_ID is unique
the Cat, Dog, and any additional race entities added in the future do not, by themselves, fully represent any particular Animal; they only have meaning when used in combination with the information contained in the parent entity, Animal.

Hence, the Animal_ID property in the Cat, Dog, etc entities is both the PK and the FK back to the Animal entity.
Differentiate between types of breed:

Just because two properties share the same name does not necessarily mean that those properties are the same, even if the name being the same implies such a relationship. In this case, what you really have is actually CatBreed and DogBreed as seperate "types"

Initial Notes

The SQL is specific to Microsoft SQL Server (i.e. is T-SQL). Meaning, be careful about datatypes as they are not the same across all RDBMS's. For example, I am using VARCHAR but if you need to store anything outside of the standard ASCII set, you should really use NVARCHAR.
The ID fields of the "type" tables (Race, CatBreed, and DogBreed) are not auto-incrementing (i.e. IDENTITY in terms of T-SQL) because they are application constants (i.e. they are part of the application) that are static lookup values in the database and are represented as enums in C# (or other languages). If values are added, they are added in controlled situations. I reserve the use of auto-increment fields for user data that comes in via the application.
The naming convention I use is to name each subclass table starting with the main class name followed by the subclass name. This helps organize the tables as well as indicates clearly (without looking at the FKs) the relationship of the subclass table to the main entity table.
Please see "Final Edit" section at the end for a note regarding Views.

"Breed" as "Race"-Specific Approach

This first set of tables are the lookup / types tables:

CREATE TABLE Race
(
  RaceID INT NOT NULL PRIMARY KEY
  RaceName VARCHAR(50) NOT NULL
);

CREATE TABLE CatBreed
(
  CatBreedID INT NOT NULL PRIMARY KEY,
  BreedName VARCHAR(50),
  CatBreedAttribute1 INT,
  CatBreedAttribute2 VARCHAR(10)
  -- other "CatBreed"-specific properties as needed
);

CREATE TABLE DogBreed
(
  DogBreedID INT NOT NULL PRIMARY KEY,
  BreedName VARCHAR(50),
  DogBreedAttribute1 TINYINT
  -- other "DogBreed"-specific properties as needed
);

This second listing is the main "Animal" entity:

CREATE TABLE Animal
(
  AnimalID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  RaceID INT NOT NULL, -- FK to Race
  Name VARCHAR(50)
  -- other "Animal" properties that are shared across "Race" types
);

ALTER TABLE Animal
  ADD CONSTRAINT [FK_Animal_Race]
  FOREIGN KEY (RaceID)
  REFERENCES Race (RaceID);

This third set of tables are the complimentary sub-class entities that complete the definition of each Race of Animal:

CREATE TABLE AnimalCat
(
  AnimalID INT NOT NULL PRIMARY KEY, -- FK to Animal
  CatBreedID INT NOT NULL, -- FK to CatBreed
  HairColor VARCHAR(50) NOT NULL
  -- other "Cat"-specific properties as needed
);

ALTER TABLE AnimalCat
  ADD CONSTRAINT [FK_AnimalCat_CatBreed]
  FOREIGN KEY (CatBreedID)
  REFERENCES CatBreed (CatBreedID);

ALTER TABLE AnimalCat
  ADD CONSTRAINT [FK_AnimalCat_Animal]
  FOREIGN KEY (AnimalID)
  REFERENCES Animal (AnimalID);

CREATE TABLE AnimalDog
(
  AnimalID INT NOT NULL PRIMARY KEY, -- FK to Animal
  DogBreedID INT NOT NULL, -- FK to DogBreed
  HairColor VARCHAR(50) NOT NULL
  -- other "Dog"-specific properties as needed
);

ALTER TABLE AnimalDog
  ADD CONSTRAINT [FK_AnimalDog_DogBreed]
  FOREIGN KEY (DogBreedID)
  REFERENCES DogBreed (DogBreedID);

ALTER TABLE AnimalDog
  ADD CONSTRAINT [FK_AnimalDog_Animal]
  FOREIGN KEY (AnimalID)
  REFERENCES Animal (AnimalID);

The model using a shared breed type is shown after the "Additional Notes" section.

Additional Notes

The concept of breed seems to be a focal point for confusion. It was suggested by jcolebrand (in a comment on the question) that breed is a property shared across the different races, and the other two answers have it integrated as such in their models. This is a mistake, however, because the values for breed are not shared across the different values of race. Yes, I am aware that the two other proposed models attempt to solve this issue by making race a parent of breed. While that technically solves the relationship issue, it doesn't help solve the overall modeling question of what to do about non-common properties, nor how to handle a race that does not have a breed. But, in the case that such a property were guaranteed to exist across all Animals, I will include an option for that as well (below).
The models proposed by vijayp and DavidN (which seem to be identical) do not work because:

They either

do not allow for non-common properties to be stored (at least not for individual instances of any Animal), or
require that all properties for all races be stored in the Animal entity which is a very flat (and nearly non-relational) way of representing this data. Yes, people do this all of the time, but it means having many NULL fields per row for the properties that are not meant for that particular race AND knowing which fields per row are associated with the particular race of that record.

They do not allow for adding a race of Animal in the future that does not have breed as a property. And even if ALL Animals have a breed, that wouldn't change the structure due to what has been previously noted about breed: that breed is dependent on the race (i.e. breed for Cat is not the same thing as breed for Dog).

"Breed" as Common- / Shared- Property Approach

Please note:

The SQL below can be run in the same database as the model presented above:

The Race table is the same
The Breed table is new
The three Animal tables have been appended with a 2

Even with Breed being a now common property, it does not seem right not to have Race noted in the main/parent entity (even if it is technically relationally correct). So, both RaceID and BreedID are represented in Animal2. In order to prevent a mismatch between the RaceID noted in Animal2 and a BreedID that is for a different RaceID, I have added a FK on both RaceID, BreedID that references a UNIQUE CONSTRAINT of those fields in the Breed table. I usually despise pointing a FK to a UNIQUE CONSTRAINT, but here is one of the few valid reasons to do so. A UNIQUE CONSTRAINT is logically an "Alternate Key", which makes it valid for this use. Please also note that the Breed table still has a PK on just BreedID.

The reason for not going with just a PK on the combined fields and no UNIQUE CONSTRAINT is that it would allow for the same BreedID to be repeated across different values of RaceID.
The reason for not switching which the PK and UNIQUE CONSTRAINT around is that this might not be the only usage of BreedID, so it should still be possible to reference a specific value of Breed without having the RaceID available.

While the following model does work, it has two potential flaws regarding the concept of a shared Breed (and are why I prefer the Race-specific Breed tables).

There is an implicit assumption that ALL values of Breed have the same properties. There is no easy way in this model to have disparate properties between Dog "breeds" and Elephant "breeds". However, there still is a way to do this, which is noted in the "Final Edit" section.
There is no way to share a Breed across more than one race. I am not sure if that is desirable to do (or maybe not in the concept of animals but possibly in other situations that would be using this type of model), but it is not possible here.

CREATE TABLE Race
(
  RaceID INT NOT NULL PRIMARY KEY,
  RaceName VARCHAR(50) NOT NULL
);

CREATE TABLE Breed
(
  BreedID INT NOT NULL PRIMARY KEY,
  RaceID INT NOT NULL, -- FK to Race
  BreedName VARCHAR(50)
);

ALTER TABLE Breed
  ADD CONSTRAINT [UQ_Breed]
  UNIQUE (RaceID, BreedID);

ALTER TABLE Breed
  ADD CONSTRAINT [FK_Breed_Race]
  FOREIGN KEY (RaceID)
  REFERENCES Race (RaceID);

CREATE TABLE Animal2
(
  AnimalID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  RaceID INT NOT NULL, -- FK to Race, FK to Breed
  BreedID INT NOT NULL, -- FK to Breed
  Name VARCHAR(50)
  -- other properties common to all "Animal" types
);

ALTER TABLE Animal2
  ADD CONSTRAINT [FK_Animal2_Race]
  FOREIGN KEY (RaceID)
  REFERENCES Race (RaceID);

-- This FK points to the UNIQUE CONSTRAINT on Breed, _not_ to the PK!
ALTER TABLE Animal2
  ADD CONSTRAINT [FK_Animal2_Breed]
  FOREIGN KEY (RaceID, BreedID)
  REFERENCES Breed (RaceID, BreedID);

CREATE TABLE AnimalCat2
(
  AnimalID INT NOT NULL PRIMARY KEY, -- FK to Animal
  HairColor VARCHAR(50) NOT NULL
);

ALTER TABLE AnimalCat2
  ADD CONSTRAINT [FK_AnimalCat2_Animal2]
  FOREIGN KEY (AnimalID)
  REFERENCES Animal2 (AnimalID);

CREATE TABLE AnimalDog2
(
  AnimalID INT NOT NULL PRIMARY KEY,
  HairColor VARCHAR(50) NOT NULL
);

ALTER TABLE AnimalDog2
  ADD CONSTRAINT [FK_AnimalDog2_Animal2]
  FOREIGN KEY (AnimalID)
  REFERENCES Animal2 (AnimalID);

Final Edit (hopefully ;-)

Regarding the possibility (and then difficulty) of handling disparate properties between types of Breed, it is possible to employ the same subclass / inheritance concept but with Breed as the main entity. In this setup the Breed table would have the properties common to all types of Breed (just like the Animal table) and RaceID would represent the type of Breed (same as it does in the Animal table). Then you would have subclass tables such as BreedCat, BreedDog, and so on. For smaller projects this might be considered "over-engineering", but it is being mentioned as an option for situations that would benefit from it.
For both approaches, it sometimes helps to create Views as short-cuts to the full entities. For example, consider:

CREATE VIEW Cats AS
   SELECT  an.AnimalID,
           an.RaceID,
           an.Name,
           -- other "Animal" properties that are shared across "Race" types
           cat.CatBreedID,
           cat.HairColor
           -- other "Cat"-specific properties as needed
   FROM    Animal an
   INNER JOIN  AnimalCat cat
           ON  cat.AnimalID = an.AnimalID
   -- maybe add in JOIN(s) and field(s) for "Race" and/or "Breed"

While not part of the logical entities, it is fairly common to have audit fields in the tables to at least get a sense of when the records are being inserted and updated. So in practical terms:

A CreatedDate field would be added to the Animal table. This field is not needed in any of the subclass tables (e.g. AnimalCat) as the rows being inserted for both tables should be done at the same time within a transaction.
A LastModifiedDate field would be added to the Animal table and all subclass tables. This field gets updated only if that particular table is updated: if an update occurs in AnimalCat but not in Animal for a particular AnimalID, then only the LastModifiedDate field in AnimalCat would be set.


Selecting data from another table using a foreign key I have two tables A & B.
Table A
id | name | num

Table B
id | date | roll

id in table B is a foreign key which relates to the id in table A.
I want to get the contents of table B where id is given and also along with that data, I need the name whose id is given from table A.
NOTE: cannot use multiple queries separated by commas; need to do both the SELECTs in one query.

Joining tables is a fundamental principle of relational databases. In your case, A and B are related with the id column, which means that you can use a syntax similar to this one:

SELECT a.id, a.name, a.num, b.date, b.roll
FROM a
INNER JOIN b ON a.id=b.id;

INNER JOIN means that you'll only see rows where there are matching records in A and B. If you want all the rows in A and matching records in B, you could change INNER JOIN to LEFT JOIN. Conversely, if you want all the records from B and only the matching ones from A, use RIGHT JOIN. Finally, if you need everything from both tables, matching or not, you can use FULL JOIN.


RETURN statement before DEALLOCATE CURSOR Question about use of Cursors in combination with RETURN in a SQL Server 2008 Stored Procedure

Consider this example:

CREATE PROCEDURE [dbo].[test]
    @ReturnEarly BIT = 0
AS
BEGIN
    SET NOCOUNT ON

    SELECT 1 AS Result INTO #Test

    DECLARE @Result INT, @HasResult INT = 1

    DECLARE TestCursor CURSOR FOR
        SELECT Result
        FROM #Test
        WHERE Result = 0

    OPEN TestCursor
    FETCH NEXT FROM TestCursor INTO @Result

    IF (@@FETCH_STATUS <> 0)
    BEGIN
        IF (@ReturnEarly = 1)
            RETURN 0
        ELSE
            SET @HasResult = 0
    END

    CLOSE TestCursor
    DEALLOCATE TestCursor

    DROP TABLE #test --technically superflous

    IF (@HasResult = 0)
        RETURN 0
    ELSE
        RETURN 1 -- in reality many more checks on the actual results
END
GO

-- Allow DEALLOCATE CURSOR to be called
DECLARE   @RC int, @ReturnEarly bit
SET @ReturnEarly = 1
EXECUTE @RC = [test] @ReturnEarly
PRINT @RC
GO

-- Exit before DEALLOCATE CURSOR is called
DECLARE   @RC int, @ReturnEarly bit
SET @ReturnEarly = 0
EXECUTE @RC = [test] @ReturnEarly
PRINT @RC
GO

DROP PROCEDURE [dbo].[test]

Both EXEC calls of the Stored Procedure return the same result. My question is this: is there a potential leak associated with returning from inside the Cursor? There is not for the temporary table (#test), for example.

Bonus: Can you point me to docs suggesting either way? My google skills weren't up to it.

Extra Bonus: If there is a problem, is there any way I could find evidence of the leak? perhaps a dmv, event log, etc which would show the problem?

Thanks in advance!

Copying part of my answer from here...

How long the resources hang around depends on whether you declared the cursor locally or globally (and what the default is in your environment - default is global but you can change it).

If the cursor is global, then it can stay "alive" in SQL Server until the last piece of code is touched in the scope in which it was created - and can often persist even beyond that. For example if you call a stored procedure that creates a global cursor, then call 20 other stored procedures, the cursor will live on while those other 20 stored procedures are running, until the caller goes out of scope. I believe it will stay alive at the session level, not the connection level, but haven't tested this thoroughly. If the cursor is declared as local, then it should only stay in scope for the current object (but again, this is theoretical, and I haven't done extensive, low-level memory tests to confirm). I don't know of any DMVs that show this usage individually, or even if on a global level you could detect memory changes in DMVs that probably don't get updated often enough to reflect the truth at any microsecond.

The general concept, though, should be: when you're done with something, say so. I believe there are several ways to write your code such that the RETURN doesn't have to happen within the cursor, and probably ways to re-write the code to eliminate the cursor.

In order to make my cursors as efficient as possible, I always use the following declarations:

DECLARE c CURSOR
  LOCAL STATIC FORWARD_ONLY READ_ONLY
  FOR SELECT ...

I've also heard that there can be memory issues if you only CLOSE or only DEALLOCATE so - even though I haven't proven or disproven it - I always do both when I'm done:

CLOSE c;
DEALLOCATE c;


Where is the data stored in MySQL databases? I don't know much about RDBMS but I am interested, I am working on a project which requires a database, I am planning to use MySQL to maintain that database.
I have installed MySQL on macOS and I have created a database which has one table in it.
now I have following questions in my mind :

1) Does turning on MySQL server turn my local computer into a server and stores databases in my computer when I create them using MySQL ?
If so can I locate the database ?

2)  Can I make a offline database using MySQL which can be located through 'Finder' ?

Does turning on MySQL server turn my local computer into a server and stores databases in my computer when I create them using MySQL ?

Basically yes. Please consider that you are using server with two distinct meanings here. When you talk of your MySQL server which you turn on, you mean a process running in background, listening and fulfilling requests. When you say turn my local computer into a server you mean a computer on which such a process runs. However, you can decide which other computers can talk to your server process. Your local MySQL clients will access it regardless, but it is possible that your MySQL server is configured by default not to listen to external requests, and it is the safest configuration. Opening up to other machines involves security concerns which will have to be addressed when the system goes into production on a real server, not now.

  If so can I locate the database ?

Sure. But where it is depends on the package you have chosen to install MySQL. Have a look at this SO answer for different options. /usr/local/mysql/ and /usr/local/var/mysql/ are usual locations.

  Can I make a offline database using MySQL which can be located through 'Finder'?

Not clear what you are asking. You can locate your DB files through Finder in the locations above, but they will be of little use without a running server process, so they are not what is usually meant by offline database such as SQLite. There are several utilities capable of accessing your data in the files without a running server, but they are meant for backup or emergency recover and they won’t give you a fully functional RDBMS.


Run query against all databases on server containing specific string in name If I have a query such as:

SELECT convert(varchar, DateTime, 100) AS "Date and Time"
,Text2 AS Area
,Substring(PText7, 1,Charindex(':', PText7)-1) AS Username
FROM the_database.dbo.AlgViewENU AS data
WHERE PText6 LIKE '%BypIn%' AND DateTime = (SELECT MAX(DateTime) FROM the_database.dbo.AlgViewENU WHERE Text1 = data.Text1 AND PText6 LIKE 'Set%')

How would I run the same query against all the databases in the server containing "the" (for example) in the name? I haven't had any luck with sp_MSforeachdb

You may try to generate a dynamic statement.

Basic example:

-- Declarations
DECLARE @stm nvarchar(max)
DECLARE @err int
SET @stm = N''

-- Dymanic statement
SELECT @stm = @stm + CONCAT(N'SELECT * FROM [', [name] , N'].sys.tables ')
FROM sys.databases
WHERE [name] LIKE '%the%'

-- Execution
EXEC @err = sp_executesql @stm

Example with your statement:

-- Declarations
DECLARE @stm nvarchar(max)
DECLARE @err int
SET @stm = N''

-- Dymanic statement
SELECT @stm =
    @stm +
    CONCAT(
    N'SELECT convert(varchar, DateTime, 100) AS "Date and Time", Text2 AS Area, Substring(PText7, 1,Charindex('':'', PText7)-1) AS Username FROM [',
    [name] ,
    N'].dbo.AlgViewENU AS data
    WHERE PText6 LIKE ''%BypIn%'' AND [DateTime] = (SELECT MAX(DateTime) FROM [',
    [name] ,
    N'].dbo.AlgViewENU WHERE Text1 = data.Text1 AND PText6 LIKE ''Set%''); '
    )
FROM sys.databases
WHERE [name] LIKE '%the%'

-- Execution
PRINT @stm
EXEC @err = sp_executesql @stm


Different syntax to add/substract interval Till now I used the following syntax to add an interval to a timestamp:

select now() + '5 year';

This worked fine till I tried to subtract the interval which results in a syntax error.

invalid input syntax for type timestamp with time zone: "5 year"
LINE 1: select now() - '5 year'

In the documentation I learned that the syntax actually is:

select now() - interval '5 year'

So my questions are:
Why does select now() + '5 year' work at all?
Does it work only by accident and it might break in a future Postgresql release?

My guess(!) is:

The + operator for timestamps only supports adding an interval (timestamp + interval). And thus it's clear that the string value '5 year' needs to be (implicitly) converted to an interval

The - operator on the other hand supports two different combinations:

timestamp - timestamp
timestamp - interval.

Apparently Postgres prefers to use the timestamp - timestamp option and tries to (implicitly) convert '5 year' to a timestamp which of course fails.


Impersonate user or login mapped to certificate Assume that there is a certificate created in database

create certificate certName
    with subject = 'subj';
GO

And a user mapped to this certificate

create user userName
    from certificate certName;
GO

Trying to impersonate this user directly

execute as user = 'userName';
GO

or specifying user in the execute as clause of the module

create procedure procName
with execute as 'userName'
as
    set nocount on;
GO

returns error

  Msg 15517, Level 16, State 1 ...Cannot execute as the database
  principal because the principal "userName" does not exist, this type
  of principal cannot be impersonated, or you do not have permission.

However, I was not able to find this restriction mentioned in documentation (here and here), where the only relevant statement seems is

  user_name must exist in the current database and must be a singleton
  account. user_name cannot be a group, role, certificate, key, or
  built-in account, such as NT AUTHORITY\LocalService, NT
  AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

Is it possible to impersonate user (or login) mapped to certificate or not?

Logins and Users created from Certificates and Asymmetric Keys cannot be impersonated. They are merely proxies for a set of permissions that will be added to any module that is signed with the same Certificate or Asymmetric Key.

Also, it doesn't really make much sense to impersonate these Logins and Users because:

these proxy permissions are added to the current security context (of an executing module that has been signed) instead of replacing the security context, which is what Impersonation does. It is an entirely different approach, not just a different mechanism to essentially the the same thing.
module signing is not meant for ad hoc queries. Part of what makes module signing a more secure approach than Impersonation is that the person granting the elevated permissions (by signing the module to associate it with an additional set of permissions) knows what the code is doing, and that once the code is given the elevated permissions, it cannot be altered to do something else, such as something that the person doing the module signing wouldn't approve of. This is handled by signatures being dropped when any change is made to a signed module, and the fact that the text of the module (including the optional EXECUTE AS clause of the CREATE statement) is used to create the signature. If the text changes, then the signature won't match.

In the end, using Logins and/or Users created from Asymmetric Keys and Certificates replace the need for Impersonation.

Still, if looking for a more official indicator, the MSDN page for CREATE USER states:

  Users that cannot authenticate These users cannot login to SQL Server or SQL Database.

  User without a login. Cannot login but can be granted permissions. CREATE USER CustomApp WITHOUT LOGIN;
  User based on a certificate. Cannot login but can be granted permissions and can sign modules. CREATE USER TestProcess FOR CERTIFICATE CarnationProduction50;
  User based on an asymmetric key. Cannot login but can be granted permissions and can sign modules. CREATE User TestProcess FROM ASYMMETRIC KEY PacificSales09;

However, not being able to login/authenticate does not mean "cannot impersonate", given that Users without a login are in that list, and you can do EXECUTE AS USER='{user_without_login}';.

The MSDN page for CREATE LOGIN states:

  Logins created from certificates or asymmetric keys are used only for code signing. They cannot be used to connect to SQL Server.

While the second sentence repeats what the CREATE USER documentation says, the first sentence is a bit more specific: they are only for code signing.

Finally, the statement of restrictions that you found in the documentation for both EXECUTE AS and the EXECUTE AS clause are correct, even though they are worded in a confusing manner. They state (bold emphasis added):

  name must be a singleton account, and cannot be a group, role, certificate, key, or built-in account, such as NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

That quote was taken from the EXECUTE AS documentation, and the EXECUTE AS Clause documentation (nearly the same wording) is quoted in the question. While both of those pages do use the terms "certificate" and "key", they are really meaning "Certificate-based Login/User" and "Asymmetric Key-based Login/User" respectively. I am assuming this based on the fact that the list of principal types given are all found in sys.server_principals (for Logins) and sys.database_principals (for Users), and neither Certificates nor Asymmetric Keys would be listed in either of those system catalog views.


Missing Oracle Schema in SSMA Object Explorer We are currently working on a project to move all of our disparate DBs (MySQL, Oracle, older versions of MS SQL) to SQL Server 2014. So far, pretty much everything has gone smoothly, however, there is one Oracle Database (10g 10.2.0.5), which we're trying to migrate using SSMA that just doesn't show up in the SSMA object explorer.

I have tried connecting to this database using the schema owner, the sys AND the system users and it just never shows up. ALL the other schemas on this instance show up and we can migrate them successfully.

Has anyone come across this issue before? Could anyone suggest possible reasons for this? I - and the SQL Server DBA - am stumped. In the interim, we're migrating subsets of data using smacked-together Perl scripts, but this is obviously not ideal.

Try this

1) Open the project

2) Remove the checkbox on the Schemas branch in the Oracle Metadata Explorer
Right-click on the Schemas branch in the Oracle Metadata Explorer and select Refresh from Database.  This will automatically put a checkbox on ALL schemas.
Let the refresh run.  This step can take a good amount of time.

3) Save the project


Selecting records based of other table records In an SQLite DB, I have teh following tables

*reports*
id: integer
name: string

*sections*
id: integer
warning_table: string
warning_kind: string
report_id: integer (foreign key)

*old_warnings*
id:integer
kind: string
creation_date: date

*new_warnings*
id:integer
kind: string
creation_date: date

sections#warning_table can be either old_warnings or new_warnings.
sections#warning_kind, old_warnings#kind, and new_warnings#kins can be one of the following values: small, medium, or large.

Now, given a date, I need to get the reports records for which there is at least a record in either old_warnings or new_warnings created that date of the kind as indicated in the sections.

Here an example to clarify

REPORTS
|id|name    |
|1 | first  |
|2 | second |

SECTIONS
|id|warning_table|warning_kind| report_id|
|1 |old_warnings |small       | 1        |
|2 |old_warnings |medium      | 2        |
|3 |new_warning  |small       | 2        |

OLD_WARNINGS
|id|kind  | creation_date|
|1 |small | 2020-02-01   |
|2 |medium| 2020-02-02   |

NEW_WARNINGS
|id|kind  | creation_date|
|1 |small | 2020-02-02   |
|2 |medium| 2020-02-04 |

So when the date is 2020-02-01 I need to get only the first report since there are neither medium old warnings or small new warnings created that day. And when the date is 2020-02-02 I should get back both reports since there is an old warning medium and a new warning small created that day.

Any ideas on how I can get what I need?
I have tried with join but I was lost. So I tried with count + union but also in that case I didn't go too far.

Putting data (here: "old"/"new") into the table name is a bad idea becaus it usually complicates queries. It should be stored in a column instead.

The easiest way to handle this is to create such a table will all warnings:

WITH all_warnings(warning_table, id, warning_kind, creation_date) AS (
  SELECT 'old_warnings', id, kind, creation_date FROM old_warnings
  UNION ALL
  SELECT 'new_warnings', id, kind, creation_date FROM new_warnings
)
SELECT *
FROM reports
WHERE id IN (SELECT report_id
             FROM sections
             JOIN all_warnings USING (warning_table, warning_kind)
             WHERE creation_date = ?
            );

If you need all warnings in other queries, then you should make all_warnings a view.


Approaches for handling nvarchar sources to data warehouse Situation is this...our data warehouse doesn't need nvarchar and is built using all non-unicode datatypes.  But all new source systems are all coming in with unicode (utf8 Oracle or nvarchar in sqlserver).

If we leave the source nvarchar to our staging then we get implicit datatype conversions if we have to join or compare to our EDW during loads. Plus it just takes up excess storage that is unnecessary.  Sounds great to stay true the source and not risk losing characters in conversion but the reality is we need to optimize storage and our load processes and that means not using unicode anytime we can avoid it.

Thoughts?

In SQL Server with Table and Index Compression (Row, Page or Columnstore) NVarchar columns don't use two bytes per character.  They use Unicode Compression.

Here's an example:

use master
go
drop database TestUnicodeCompression
go
create database TestUnicodeCompression

go
use TestUnicodeCompression
drop table if exists A
drop table if exists B
go
create table A(id int identity, a nvarchar(200) default cast(newid() as nvarchar(200) ))

create table B(id int identity, a varchar(200) default cast(newid() as varchar(200)))

go

with q as
(
  select top 1000000 row_number() over (order by (select null)) i
  from sys.objects o, sys.columns c, sys.columns c2
)
insert into A(A) select cast(newid() as varchar(200))
from q;

with q as
(
  select top 1000000 row_number() over (order by (select null)) i
  from sys.objects o, sys.columns c, sys.columns c2
)
insert into B(A) select cast(newid() as varchar(200))
from q;

go
alter table A rebuild with (data_compression=none)
alter table B rebuild with (data_compression=none)
go

select 'before compression' state,
       cast( object_name(object_id) as varchar(10)) table_name,
       used_page_count
from sys.dm_db_partition_stats
where object_id in (object_id('A'), object_id('B'))

go

alter table A rebuild with (data_compression=page)
alter table B rebuild with (data_compression=page)

go

select 'after compression' state,
        cast( object_name(object_id) as varchar(10)) table_name,
        used_page_count
from sys.dm_db_partition_stats
where object_id in (object_id('A'), object_id('B'))

outputs

(1000000 rows affected)

(1000000 rows affected)
state              table_name used_page_count
------------------ ---------- --------------------
before compression A          11114
before compression B          6581

(2 rows affected)

state             table_name used_page_count
----------------- ---------- --------------------
after compression A          6210
after compression B          6062

(2 rows affected)


Keepalived vIP as Galera wsrep_cluster_address I have a MariaDB Galera cluster.
If some nodes fail, I cannot blindly restart them, I have to determine a good wsrep_cluster_address first.

If I can keep a keepalived virtual IP on one of the healthy nodes, can I use this IP as wsrep_cluster_address on other nodes? So in case of node failure, the joining node would always have a right wsrep_cluster_address? Or are there any other solutions enabling automatic rejoin?

I feel it should be somehow possible to keep the cluster up and automatically rejoin nodes as long as there is at least 1 healthy node (or Primary Component?) up.

(Note: I am aware of the answer in Galera cluster without having to specify all hosts on wsrep_cluster_address, but multicast is unfortunately not an option.)

You can put multiple addresses into wsrep_cluster_address, e.g. gcomm://10.1.1.1,10.1.1.2,10.1.1.3, and Galera Cluster will detect it smartly.

I has used Percona XtraDB Cluster (which is based on Galera Cluster too) to run serveral (> 10) clusters in production environment:

One has been running with 2 DB servers and 1 garbd server, and we put all three servers into wsrep_cluster_address.
Another was 7 database servers, and we just put first three servers (which were in different racks) into wsrep_cluster_address.

There were power failures (all three servers), hardware failures, software bugs on these clusters in four years, and they worked pretty well.


Detecting resource scheduling conflicts with MySQL How to query the 3 tables below to detect resource scheduling conflicts? Conflicts occur when two events with overlapping date/time ranges require common participants.

CREATE TABLE `events` (
  id int(11) AUTO_INCREMENT,
  `date` date,               # date on which the event occurs
  start_minute smallint(6),  # minute of day on which event starts
  end_minute smallint(6),    # minute of day on which event ends
);

CREATE TABLE participants (
  id int(11) AUTO_INCREMENT,
  `name` varchar(255),
);

CREATE TABLE event_participations (
  event int(11),             # refers to events.id
  participant int(11),       # refers to participants.id
  PRIMARY KEY (event,participant)
);

I think the following query would detect overlapping events:

SELECT *
FROM events e CROSS JOIN events e2
WHERE e.date=e2.date AND e.start_minute<e2.end_minute AND e2.start_minute<e.end_minute

However, overlaps do not imply conflicts as the two events may involve mutually exclusive sets of participants. How to query for event-pairs that require the same participants at the same time?

Either like this:

SELECT DISTINCT
'this events and participations overlap',
e.*
FROM events e
JOIN event_participations ep ON e.id = ep.event
WHERE EXISTS (SELECT 1
              FROM events e2
              JOIN event_participations ep2 ON e2.id = ep2.event
              WHERE e.date = e2.date
                AND e2.start_minute < e.end_minute
                AND e2.end_minute > e.start_minute
                AND ep2.participant = ep.participant
             )

or like this:

SELECT DISTINCT
'this events and participations overlap',
e.*
FROM events e
JOIN event_participations ep ON e.id = ep.event
JOIN events e2 ON  e.date = e2.date
               AND e2.start_minute < e.end_minute
               AND e2.end_minute > e.start_minute
JOIN event_participations ep2 ON e2.id = ep2.event
WHERE ep2.participant = ep.participant

The nice thing about EXISTS is, that it stops to search as soon as it hits. The JOIN approach builds the whole data set.


Adding foreign keys constraint on databases used We have a server with about 20 databases, all duplicates of the same original db, and we just noticed the original is missing all the foreign keys constraint for some unknown reason. So of course all the databases on that server are missing them too.
Now, these databases have been used for some time now, and there is data in them. We noticed that in the export done from phpmyadmin, the constraint are at the end, so I did an export from a valid database and I now have a few hundreds lines of ALTER TABLE to add the constraints, the problem being that if I start applying that to our databases, MySQL will reject it because the keys that should have been deleted by the constraint weren't.

Is there any way around that ?
That's a lot of databases and a lot of tables, it's just not possible to go in all of them and delete the data manually, and since those constraints should have been here from the start, I feel that all the data that would prevent those keys from being added could be dropped to get back to the "normal" state, am I wrong ?

I'm not a database administrator, so I have no idea if that's even possible. I've been told that ALTER IGNORE TABLE works like that for UNIQUE, would it work for constraints too ?
Or is there a way to tell MySQL to ignore the errors when adding the constraints and then DELETE all the unmatching entries ?

Thanks a lot,

OK - following my comment, here is what I would do.

Before shutting down the server or individual database.

Create backup table scripts for each table you need to enforce foreign keys on and their parent(s). Call them my_table_bak (or similar).
Run these CREATE TABLE my_table_bak(1..n) scripts on the database (don't definitively delete anything).
(removed during edit)
Prepare an SQL script file (salvage.sql? :-))

Now, the question is are you removing orphan records (i.e. child records that have no corresponding record in the parent table)? Or are you deleting barren parents (i.e. parents with no child records)? Below will do both.

INSERT INTO parent_bak
SELECT * FROM parent
WHERE parent.primary_key NOT IN
(
    SELECT child.foreign_key
    FROM child
); -- backup barren parents

INSERT INTO child_bak
SELECT * FROM child
WHERE child.foreign_key
NOT IN
(
    SELECT parent.primary_key
    FROM parent
); -- backup orphan records

DELETE FROM parent
WHERE parent.primary_key
NOT IN
(
    SELECT child.foreign_key
    FROM child
); -- remove invalid records from your original tables.

DELETE FROM child
WHERE child.foreign_key NOT IN
(
    SELECT parent.primary_key
    FROM parent
); -- remove invalid records from your original tables.

Add your FOREIGN KEY constraints.
Stop all clients connecting to the server or database (so updates can't occur while running the copy into _bak tables (invalid data) and the deletes.
Run:

mysql -h host -u username -ppassword yourdb < salvage.sql

...for each of your servers. For your 20 databases, you could write a script for all 20 and run that.

The time this will take will depend on the size of your databases and the number of tables. Warning - there may be typos in this post - I didn't run the scripts on a server, but you should get the idea.

Test on at least one database before running on your live server.

[EDIT] Forgot - add your FOREIGN KEY constraints at the end of salvage.sql also.