Availability Group, asynchronous mode, manual/forced failover - handling identity overlap We have a two replica 2017 Standard Availability Group in asynchronous mode.  With a failover (manual/forced, since can't do automatic), we have the potential for overlapping/reused identity values if the secondary is lagging behind the primary.  This is really bad for us...CustomerA seeing CustomerB's data.

EDIT: I'm mostly concerned about a DR/failure failover where the primary goes offline unexpectedly.  A planned failover (patching, etc.) would be easier to manage by waiting for or forcing the secondary to sync up to the primary before taking the primary offline, as suggested below.

Is there a way to automatically run a script as the secondary takes over the primary role, to bump all identity columns (DBCC CHECKIDENT RESEED) to allow for the eventual merging of the old primary's unsynced records?
Since this is Standard, the secondary is inaccessible until it takes over as the primary, so when/how would the script run?
Side question: Since the old primary becomes the secondary (once it's back online) and is at that point inaccessible (Standard), how does one run queries on it to determine the unsynced records?  Back up/restore the old primary as a nonAG db?

Thanks!

Rather than trying to react to the failover on the new primary, I think it would make more sense to update your manual failover procedure to first change the AG to sync mode.

Once the AG indicates that it's synchronized, then do the failover.  This way you won't have data loss.  You can then go back to async mode after the failover is completed.

See Change the Availability Mode of an Availability Replica (SQL Server) for instructions on how to make the change prior to the failover.

Note: a sync mode AG can temporarily shift into async mode if the network gets slow between the two servers

  If primary's session-timeout period is exceeded by a secondary replica, the primary replica temporarily shifts into asynchronous-commit mode for that secondary replica. When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode

But according to the failover docs, a sync mode AG guarantees zero data loss during a manual failover

  This guarantees that every transaction that was committed on a former primary database has also been committed on the new primary database


Corrupt Transaction Log on database I have reason to believe the transaction log file on my database is corrupt.

This database is involved in transaction based replication (both subscription and publishing), and I'm unable to start its log reader agent with an error message which cites "Operating System Error 23".

I essentially just want to do what it takes to be able to restart the log reader agent, and my best idea so far is to somehow clear the log file, detach from it and reattach to a new log file. I'm not sure what the data loss implications of this would be though. Is this the best solution or is there a better way to deal with a corrupt log file? It is not a production database so avoiding data loss is not absolutely necessary (though preferable). There are no backups.

OS error 23 is "Data error (cyclic redundancy check)."
you can check those by running executing on the command prompt net helpmsg 23, this basically means that you have a serious error on your disk.

As the database is running what you need to to is to

DONT PANIC
Find your backups
Script your replication setup
Do a tail-of-the-log backup to have a restore point.
Run DBCC CHECKDB with NO_INFOMSGS, ALL_ERRORMSGS and keep the output
Turn of the SQL Server and copy the data and log files to a safe place -if possible
Run CHKDSK on the log drive.
Restart the SQL Server and run DBCC CHECKDB again if it comes out clean you should be able to restart the replication if not you will have to restore the database.


SQL Server 2008 transaction log growing since SP3 Installed On our production db (~117gb) we quite a few tables being replicated.  Historically our maintenance plan has kept the transaction log fairly small.  Since we installed SP3,  the transaction log is growing pretty quickly (currently ~213gb).

We made sure the install was successful by checking the registry entries,  but not too sure what else to check.  Searching hasn't found any similar problems with SP3 as the culprit.

Not too sure how to look at the transaction log either since DBCC LOG results are cryptic at best.

This is output from version:

Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)
Sep 21 2011 22:45:45
Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit)
   on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Full Recovery Mode = true
Backing up transaction log = true on a daily basis.

This is a production database that has had the transaction log maintained to a small size for many years without a problem... until SP3 came along.

I ran the DBCC OpenTran and found this:

Oldest active transaction: SPID (server process ID): 9s UID (user ID) : -1 Name : tran_sp_MScreate_peer_tables LSN : (365893:25399:1) Start time : Nov 12 2011 8:26:10:927AM SID : 0x01 Replicated Transaction Information: Oldest distributed LSN : (366831:664081:8) Oldest non-distributed LSN : (0:0:0)

Quite a few other people have reported this same long running process after a service pack install... hmmmm.

Not too sure what to do about it. Might consider pausing or killing my replication jobs and see if it goes away. All indications are that since it is a '9s' SPID, the system owns it and it can't be killed.

thoughts on how to proceed?

I'd suggest the 2 are unrelated.

Has anything changed:

in the maintenance plan setting
recovery model of the database (is it now FULL where it was SIMPLE)
did you take log backups before (FULL) in the maintenance plan? Do you still do them?
did you have auto shrink in the maintenance plan? This is bad anyway
have you any open transaction that is preventing a log backup (or shrink)

Also, restore a database from pre-SP3 on another server and compare the sys.databases entries


What is the best database design to keep track of fields with multiple values? I am trying to dynamically build a form with a label (field) and a user entered value (fieldValue) but the form can have several different control, like a combobox, textbox, listbox, checkbox, etc....

Here is my schema right now

Page
* PageId

Field
* FieldId
* FiledTypeId
* FieldName

FieldType
* FieldTypeId
* FieldTypeName (Combobox, Textbox, etc...)

FieldValue
* FieldValueId
* FieldId
* PageId
* FieldValueValue
* CurrentValue (This is a bit because a combobox could have multiple values but I wanna show which one is selected. Similarly, a listbox could have multiple values selected, a textbox would not have any values selected.)

The problem with my design is that every time I have a form using the same fields, I have to add all of the items in the combobox again as it is a new field, specific to a new page AND when I create a new page how will it know that "these" values belong to a combobox, for example.  I've thought of having a 0 to many table to keep track of comboboxes and things with multiple values and then my field value table will only hold one value per field and the CurrentValue column will be gone but I'm not 100% sure if this is correct.

Any suggestions?

Your new design constraint is such that there's a many-to-many relationship between pages and fields, and a many-to-many relationship between page-fields and field values.  That means you'd have to do this:

Page
* Page_Id

Field
* Field_Id
* Field_Type_Id
* Field_Name

Page_Field
* Page_Field_ID
* Page_Id
* Field_Id

Page_Field_FieldValue
* Page_Field_FieldValue_ID
* Page_Field_ID
* FieldValue_Id

FieldValue
* FieldValue_Id
* FieldValue_Value
* Current_Value

"That's really gross" you're thinking.  Yes, it is.  I'd strongly suggest creating views (indexed or materialized if possible) to make retrieval easier.  It doesn't really solve your problem of having to define everything for each new page, but at least you won't be storing the same data repeatedly.

The Field_Type table doesn't change at all.  It's relation to Field is the same.  I did not list it in order to reduce confusion.

I'd also caution about using the FieldValue.Current_Value field as you seem to be suggesting, as that will not really work if your application allows multiple users.  Application state needs to be stored separately from application data, typically.


In DB2, what are the alternative ways of evaluating an index's (or several indexes) benefit? I'm trying to evaluate different approaches to analyzing the benefit of changing the set of indexes that are available. If I pick some representative queries from my workload, I can run EXPLAIN and check if the plan of each changes when I add/drop an index. However, this may be too tedious if I have, say 100 queries. I was wondering what available alternatives (tools, scripts, etc.) exist and what are their pros/cons.

The best way to do this for a DB2 database running on Linux, UNIX or Windows is use the DB2 Design Advisor.

This tool (included with DB2) allows you to specify a workload (which could include hundreds of queries), and it will analyze the workload and the existing database to recommend indexes and/or other physical design changes to optimize the workload.


User Rights - Database Security I am having following scenarios and i am having some issues and some questions.

We have a DB Server on which we have some databases in these 2(two databases) are for my users.
Now i have created 2 windows group login A & B, A is the user which can only read data from 2 DB's, and B is the user which can do a restore to DB's.
Now what i like to know, is this possible to restrict user B to have restore DB rights only on 2 databases(i have many on my server)
Another problem i am facing is when i am restoring DB from user A than all rights assigned to user B from that specific DB are not there, so how can i always keep my user rights same for every DB.

I am using SQL Server 2008. Thanks in Advance.

To move logins and users from the source server, I use the following solution. This script helps completelly transfer logins with user permissions  (see description on the top of the script how t use it and to specify the databases and logins).


Is it a good idea/approach to index a VARCHAR column? We're using PostgreSQL v8.2.3.

There are tables involved: EMPLOYEE and EMAILLIST.

Table 1: EMPLOYEE (column1, column2, email1, email2, column5, column6)
Table 2: EMAILLIST (email)

2 tables are joined in such a way that if either EMPLOYEE.EMAIL1 or EMPLOYEE.EMAIL2 do not have a matching entry, those rows will be returned.

SELECT employee.email1, employee.email2,
        e1.email IS NOT NULL AS email1_matched, e2.email IS NOT NULL AS email2_matched
   FROM employee
   LEFT JOIN emaillist e1 ON e1.email = employee.email1
   LEFT JOIN emaillist e2 ON e2.email = employee.email2
 WHERE e1.email IS NULL OR e2.email IS NULL

Column EMAIL which is varchar(256) of EMAILLIST table is indexed.  Now, the response time is 14 seconds.

Table count statistics: Currently, EMPLOYEE has got 165,018 records & EMAILLIST has got 1,810,228 records, and both tables are expected to grow in future.

Is it a good idea/approach to index a VARCHAR column? This question immediately strike on my mind because of the reason that we've not indexed a VARCHAR column before in our application.  Experts advice/suggestion on this are highly appreciated.
With this current query and index, the response time of 14 seconds is reasonable or  is there any scope for further tuning?  What are other user's real-time experience/opinion based on this kind of table size and response time?

NOTE: My actual requirement/use case is explained in detail here.

There's nothing wrong with indexing a varchar column if you're going to be doing queries based on it. However please keep in mind that there a limits to some indexes and how much they can index in a single field. Example you can't index a column that can contain an unlimited amount of text. However you should be able to do an index on varchar(256) without issue. Try it, and analyze the improvements in your queries performance to see if it helps.


Enforcing constraints "two tables away" I ran into some trouble modeling an electrical schematic in SQL.
The structure I'd like to capture is

  part ←────────── pin
   ↑                ↑
part_inst ←───── pin_inst

where "inst" is short for "instance".

For example, I might have as a part an LM358 op-amp with pins
1OUT, 1IN-, 1IN+, GND, 2IN+, 2IN-, 2OUT, and VCC. I might
then place this part on a schematic, creating a part_inst and 8
pin_insts.

Ignoring data fields, my initial attempt at a schema was

create table parts (
    part_id bigserial primary key
);
create table pins (
    pin_id bigserial primary key,
    part_id bigint not null references parts
);
create table part_insts (
    part_inst_id bigserial primary key,
    part_id bigint not null references parts
);
create table pin_insts (
    pin_inst_id bigserial primary key,
    part_inst_id bigint not null references part_insts,
    pin_id bigint not null references pins
);

The main problem with this schema is that a pin_inst might be tied
to a part_inst with part_id=1 but its pin has part_id=2.

I'd like to avoid this problem on the database level rather than the
application level. So, I modified my primary keys to enforce that.
I marked the changed lines with --.

create table parts (
    part_id bigserial primary key
);
create table pins (
    pin_id bigserial,                                          --
    part_id bigint not null references parts,
    primary key (pin_id, part_id)                              --
);
create table part_insts (
    part_inst_id bigserial,                                    --
    part_id bigint not null references parts,
    primary key (part_inst_id, part_id)                        --
);
create table pin_insts (
    pin_inst_id bigserial primary key,
    part_inst_id bigint not null,                              --
    pin_id bigint not null,                                    --
    part_id bigint not null references parts,                  --
    foreign key (part_inst_id, part_id) references part_insts, --
    foreign key (pin_id, part_id) references pins              --
);

My gripe with this method is that it pollutes the primary keys:
Everywhere I refer to a part_inst, I need to keep track of both the
part_inst_id and the part_id. Is there another way I can go about enforcing the constraint
pin_inst.part_inst.part_id = pin_inst.pin.part_id without being overly verbose?

Minimal solution
One radical solution might be to remove pin_inst completely:
  part ←────────── pin
    ↑               ↑
part_inst ←───── pin_inst

There is nothing in your question to suggest you actually need the redundant table. For pins associated to a part_inst, look at the pins of the associated part.
That would simplify the code to:
create table part (    -- using singular terms for table names
    part_id bigserial primary key
);
create table pin (
    pin_id bigserial primary key,
    part_id bigint not null references part
);
create table part_inst (
    part_inst_id bigserial primary key,
    part_id bigint not null references part
);

But your comment made clear that we won't get away with that ...
Alternative if pin_inst is needed
Including part_id like you did is the simplest solution with foreign key constraints. You cannot reference a table “two tables away” with foreign key constraints.
But you can at least make do without "polluting" the primary keys. Add UNIQUE constraints.
create table part (
    part_id bigserial primary key
);
create table pin (
    pin_id bigserial primary key,
    part_id bigint not null references part,
    unique(part_id, pin_id)         -- note sequence of columns
);
create table part_inst (
    part_inst_id bigserial primary key,
    part_id bigint not null references part,
    unique(part_id, part_inst_id)
);
create table pin_inst (
    pin_inst_id bigserial primary key,
    part_inst_id bigint not null,
    pin_id bigint not null,
    part_id bigint not,
    foreign key (part_id, pin_id) references pin,
    foreign key (part_id, part_inst_id) references part_inst
);
I put part_id first in the unique constraints. That is irrelevant for the referential integrity, but it matters for performance. The primary keys already implement indexes for the pk columns. It's better to have the other column first in the multicolumn indexes implementing the unique constraints. Details under these related questions:

Working of indexes in PostgreSQL
Is a composite index also good for queries on the first field?
Many to Many and Weak Entities

Related questions on SO:

Can a multi-field UNIQUE constraint include fields from more than one table?
Foreign key constraint with some column values residing in other tables

Alternative with triggers
You could resort to triggers functions, which are more flexible, but a bit more complicated and error prone and a bit less strict. The benefit: you could do without part_inst.part_id and pin.part_id ...


Maximum possible memory usage: 23.6G (785% of installed RAM) I have a virtualized 32bits Server for Mysql (RAM : 115 Go).

I am trying to optimize with mysqltuner and I get this :

>>  MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.41-0+wheezy1-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 6G (Tables: 293)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 3

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 23m 4s (55K q [39.949 qps], 8K conn, TX: 72M, RX: 9M)
[--] Reads / Writes: 75% / 25%
[--] Total buffers: 2.7G global + 35.6M per thread (600 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 23.6G (785% of installed RAM)
[OK] Slow queries: 0% (35/55K)
[OK] Highest usage of available connections: 8% (53/600)
[OK] Key buffer size / total MyISAM indexes: 1.5G/1.1G
[OK] Key buffer hit rate: 99.6% (85M cached / 327K reads)
[!!] Query cache efficiency: 10.5% (2K cached / 22K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (43 temp sorts / 8K sorts)
[!!] Joins performed without indexes: 68
[OK] Temporary tables created on disk: 19% (645 on disk / 3K total)
[OK] Thread cache hit rate: 97% (202 created / 8K connections)
[OK] Table cache hit rate: 34% (542 open / 1K opened)
[OK] Open file limit used: 28% (843/3K)
[OK] Table locks acquired immediately: 97% (47K immediate / 48K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_limit (> 5M, or use smaller result sets)
    join_buffer_size (> 20.0M, or always use indexes with joins)

my.cnf :

key_buffer              = 1500M
key_buffer_size         = 1500M
tmp_table_size          = 800M
max_heap_table_size     = 800M
sort_buffer_size        = 15M
join_buffer_size        = 20M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
max_connections        = 600
table_cache            = 1200
open_files_limit       = 2400
#thread_concurrency     = 10
query_cache_limit       = 5M
query_cache_size        = 500M

Does it mean that I can use only 23,6 Go of the RAM ? Do you think it would be important to migrate to a 64bits container ? The Mysql performance are not so good that I could imagine with a such server...

I wish the creator of that tool would toss that line.  It unnecessarily scares people.  And the number it gives is both too small and too large.
But, in your case, it there is a real danger.  If you had just 3 queries running that were nasty enough, they could be asking for 2.4GB -- more than your RAM!!  This is because tmp_table_size and max_heap_table_size are set to 800M.  Change them to, say, 20M.  Queries will still work, but they may spill to disk more often.  More importantly, you want swap or crash.
Fragmented tables -- bogus.  Virtually all tables are fragmented.  OPTIMIZE TABLE is almost never worth doing.
But...
key_buffer_size of 1.5G in 2GB of RAM is really bad. 300M is about right if you are using only MyISAM.  The key_buffer is for MyISAM indexes.  You need to leave room for data, which is cached by the Operating System.
Since the Query cache hit rate is so low and the size is dangerously high, you are better off turning it off and using the RAM for other stuff.  query_cache_type = OFF and query_cache_size = 0.
Since you have a "tiny" amount if RAM (2GB), you should probably lower max_connections. Look at the STATUS value of Max_used_connections to see the high water mark since last restart.
Edit

Upgrade to a 64-bit MySQL
Configure key_buffer_size and innodb_buffer_pool_size in my.cnf to the amount of RAM you have.  Guidelines:  http://mysql.rjweb.org/doc.php/memory
See how things are going; come back to us for more help (and/or abuse) if needed.


Naming question for functions: implicit or explicit naming of grouping in function name Requesting help with naming TSQL functions.

Existing function names (see below for table and relationship descriptions)

I currently have functions named as follows:

CustomerYearsGetByYearRangeOffset: For each CustomerID, return all of their CustomerYears records, over a specified begin/end range
CustomerYearsGetLatestByYearRangeOffset: For each CustomerID, return their latest CustomerYears record, over a specified begin/end range
ObjectRevisionIDsGetByYearRangeOffset: For each ObjectID, return all of their ObjectRevisionIDs records, over a specified begin/end year range
ObjectRevisionIDsGetLatestByYearRangeOffset: For each ObjectID, return their latest ObjectRevisionIDs record, over a specified begin/end year range

Note: All accept a caller-specified begin/end year range offset (relative to the current year). For example, begin/end year offsets -1, 0 would indicate the year one-year prior and the current year.

Two new functions

The two new functions will implement more sophisticated logic, as follows:

For each CustomerID, return their latest ObjectRevisionIDs records tied to their latest CustomerYears record, over a specified begin/end year range
For each CustomerID, return all their ObjectRevisionIDs records tied to their latest CustomerYears record, over a specified begin/end year range

What to name the new functions?

Some drafts:

ObjectRevisionIDsInLatestYearGetLatestByYearRangeOffset
ObjectRevisionIDsInLatestYearGetByYearRangeOffset

Other ideas?

Am I thinking about this wrong? For example, abandon the explicit naming approach above and name according to the higher level need it meets?

I see myself calling these new functions frequently, so I prefer to get something that seems "right" each time I use it. ;o)

Thanks for any assistance. Usually when I get this far in writing a question the answer presents itself, but not this time.

Background: tables and relationships

To keep things simple for this question, the database contains four tables (in reality many more tables and fields):

Customers (CustomerID)
CustomerYears (CustomerID, Year)
Objects (ObjectID, CustomerID)
ObjectRevisions (ObjectRevisionID, ObjectID, CustomerID, RevisionNumber, Year)

In a nutshell: each year the Customer participates, they get a new record in CustomerYears. In each year, they will have one or more Objects, each of which can have one or more revisions.

For example:

Customers (CustomerID)
1
2

CustomerYears (CustomerID, Year)
1|2011
1|2012
2|2012

Objects (ObjectID, CustomerID)
A|1
B|1
C|2

ObjectRevisions (ObjectRevisionID, ObjectID, CustomerID, RevisionNumber, Year)
1|A|1|1|2011
2|A|1|2|2011
3|B|1|1|2011
4|A|1|1|2012
5|B|1|1|2012
6|B|1|2|2012
7|C|2|1|2012
8|C|2|2|2012

Where are these functions being called? Are they being called often? How complex is the logic, does it even warrant creating a new database object?

They are very verbose, and the new ones match that style so I guess they're ok. I would prefer to start with Get then I know right away its more of a 'getter' than a subroutine.

I would also pick more terse names, though I have a weakness for clutter free code.


How can I calculate time spent at work in a single column? I would like to calculate the amount of time spent working from rows of data that contain a single date/time field.

The first occurrence on a given day can be considered the start time, with the next occurrence being the end time, alternating back and forth on any given day.  So if I start at 9:00am and go on lunch at 12:00pm, return from lunch at 12:30pm, then leave for the day at 5:00pm, my data would look like:

2013-03-07 09:00:00
2013-03-07 12:00:00
2013-03-07 12:30:00
2013-03-07 17:00:00

Here is a further sample (in my locale we use . as the date separator):

22. 2. 2013 9:09:00
22. 2. 2013 13:28:09
25. 2. 2013 9:43:16
25. 2. 2013 15:15:19
25. 2. 2013 19:30:00
25. 2. 2013 21:30:00
26. 2. 2013 8:27:54
26. 2. 2013 16:31:51
27. 2. 2013 8:52:59
27. 2. 2013 15:08:35
28. 2. 2013 8:37:54
28. 2. 2013 16:37:08
28. 2. 2013 18:30:00
28. 2. 2013 20:30:00

I would like to know the total time spent at work for any given day, and would also like to be able to tally the time spent over, for instance, any given week or month.

DECLARE @d TABLE(dt DATETIME);

INSERT @d VALUES
('2013-02-22 09:09:00'),
('2013-02-22 13:28:09'),
('2013-02-25 09:43:16'),
('2013-02-25 15:15:19'),
('2013-02-25 19:30:00'),
('2013-02-25 21:30:00'),
('2013-02-26 08:27:54'),
('2013-02-26 16:31:51'),
('2013-02-27 08:52:59'),
('2013-02-27 15:08:35'),
('2013-02-28 08:37:54'),
('2013-02-28 16:37:08'),
('2013-02-28 18:30:00'),
('2013-02-28 20:30:00');

;WITH x AS
(
  SELECT
   e = dt,
   r = ROW_NUMBER() OVER (ORDER BY dt),
   p = LAG(dt, 1) OVER (ORDER BY dt),
   m = DATEDIFF(MINUTE, LAG(dt, 1) OVER (ORDER BY dt), dt)
 FROM @d
)
SELECT
  start = p,
  [end] = e,
  [minutes_elapsed] = m,
  [time] = CONVERT(TIME(0), DATEADD(MINUTE, m, 0))
 FROM x
 WHERE r % 2 = 0
 ORDER BY e;

If you need to do this on older versions (2005 -> 2008 R2):

;WITH cte1 AS
(
  SELECT
   e = dt,
   r = ROW_NUMBER() OVER (ORDER BY dt)
  FROM @d
),
cte2 AS
(
  SELECT
    x.r,
    [start] = x.e,
    [end] = y.e,
    m = DATEDIFF(MINUTE, x.e, y.e)
  FROM cte1 AS x INNER JOIN cte1 AS y
  ON x.r = y.r - 1
  WHERE y.r % 2 = 0
)
SELECT
  [start],
  [end],
  minutes_elapsed = m,
  [time] = CONVERT(CHAR(8), DATEADD(MINUTE, m, 0), 108)
 FROM cte2
 ORDER BY [start];

Suggested schema for recording time in/time out is pretty simple:

CREATE TABLE dbo.Shifts
(
  EmployeeID INT NOT NULL, -- probably Foreign Key, right?
  ClockIn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ClockOut DATETIME NULL
);

Now your query is:

SELECT
  EmployeeID,
  ClockIn,
  ClockOut,
  ShiftLength = CONVERT(TIME(0), DATEADD(MINUTE,
    DATEDIFF(MINUTE, ClockIn,
      COALESCE(ClockOut, CURRENT_TIMESTAMP) -- to capture shift in progress
    ), 0))
FROM dbo.Shifts
WHERE ...


What are we Doing when we Connect to SQL Server? My apologies if this is simple, or off-topic for this site:
  I just wanted to know what goes on in the background when one connects to
SQL Server, say, within Management Studio. Just what is it we are connecting to,
is it a virtual machine, or some server somewhere?
Thanks for any explanations, refs. etc.

Usually, you are making a TCP connection to a remote host.

SSMS would ask your DNS server for the IP address associated with the hostname of the server, then initiate a TCP connection to that address on remote port 1433.

SSMS and MS SQL Server would then begin a conversation using the binary Tabular Data Stream protocol (TDS).

You type a query. SSMS encodes this query into TDS. SQL Server gets it, parses it, runs it, returns some data (encoded in TDS), back to SSMS, which decodes it and displays it on screen.

However, you can also connect to SQL Server installed on the same OS as SSMS, or running in a virtual machine on the same box.


Does PostgreSQL support multi-threaded replication? Is PostgreSQL replication single-threaded ? Are there any tools to achieve multi-threaded replication ?

I'm asking this since Mariadb support this and currently I'm learning both these dbs.

The standard streaming replication on PostgreSQL is single threaded and there is no way to change this.  However the question is why would you want to?

PostgreSQL's streaming replication works through the write ahead log which is kinda of like a set of instructions "change block 3525 to this", "change block 2424 to this", etc.  This makes the replication process very fast as it not re-executing SQL, its simply doing the same data file updates that the master has done.  Typically the slowness with replication on PostgreSQL is IO bound rather than CPU bound so executing the recovery in parallel will actually make it slower not faster.  This is because the effect of interleaving the IO requests will potentially make sequential IO random.  Of course modern SAS drives should be able to reorder the requests for data back into sequential order.

I suspect the question came about because of the statement shipping type of replication MySQL and MariaDB can do, in this case yes of course it makes perfect sense to interleave the statements as there will be a much higher CPU cost and running this on multi-core hardware will offer a great performance improvement.


Is non-dynamic scalar subquery evaluted for each row? Is a scalar subquery that's independent on parent query (does not refer to the parent table) evaluated only once, or for each row, regardless?

SELECT /* ... */
FROM
    t1
WHERE
    parent = (
        -- is this sq evaluated once?
        SELECT
            t2.id
        FROM t2
        WHERE t2.id = 10
    )

or using a CTE

WITH parent_id AS (
    SELECT
        t2.id
    FROM t2
    WHERE t2.id = 10
)
SELECT /* ... */
WHERE
    parent = (
        SELECT p.id FROM parent_id p -- is this sq evaluated once?
    )

If the answer is that the subquery/CTE is evaluated for each row, would joining the CTE on each row improve the performance?

WITH parent_id AS (
    SELECT
        t2.id
    FROM t2
    WHERE t2.id = 10
)
SELECT /* ... */
FROM
    t1, parent_id
WHERE
    parent = parent_id.id

Also, is there a way to figure out from EXPLAIN ANALYZE how many times an expression was evaluated?

Note: the examples are arbitrary and do not reflect real scenario, thus ignore the fact it doesn't make sense to do a subquery to find out an id which is already known (10).

It is evaluated once.

EXPLAIN ANALYZE will display lines like these:

...
InitPlan 1 (returns $0)
    ->  Index Scan using t2_pkey on t2 (cost=0.29..5.31 rows=1 width=4) (actual time=0.024..0.025 rows=1 loops=1)
          Index Cond: (id = 10)
...

loops=1 tells you the subplan has only been evaluated once. The manual:

  In some query plans, it is possible for a subplan node to be executed
  more than once. For example, the inner index scan will be executed
  once per outer row in the above nested-loop plan. In such cases, the
  loops value reports the total number of executions of the node, [...]

With a correlated subquery like:

SELECT *, (SELECT id FROM t2 WHERE id = t1.parent_id) AS t2_id
FROM   t1;

(or something more useful) you would see loops=n,  where n is the number of rows t1.


Can't find process that is creating backups We have an instance of SQL Server 2008 installed on a customer server. The customer's IT department is in charge of the various backups of the machine.

The SQL Server log shows there is a full backup every day at 7 PM but we can't find evidence of any plan scheduled in SQL Server.

The technicians we contacted couldn't tell us if there was some sort of automatic backup, all they told is that the entire machine is being backup up.

Using a script I found in this forum thread I found out that the physical device name is a GUID and that this means that this is an external backup process:

USE [msdb]
GO
SELECT
    [bs].[database_name],
    [bs].[backup_start_date],
    [bs].[backup_finish_date],
    [bs].Server_name,
    [bs].user_name AS [BackupCreator] ,
    [bmf].physical_device_name
FROM msdb..backupset bs
INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [bs].[backup_start_date] DESC

This is a row from the above query:

  db_name 2015-09-16 19:01:23.000   2015-09-16 19:01:28.000SERVER_NAME  NT AUTHORITY\SYSTEM{424F084A-F35D-4A66-8FC7-072268A89A77} 5

Moreover the backup start and finish date spans only for 5 seconds, so I guess it's clear it's not a job of sql server.

This is a line from the log:

  2015-09-03 19:02:30.71 Backup
  Database backed up.
  Database: db_name, creation date(time): 2012/10/12(20:52:11), pages dumped: 3290021,
  first LSN: 276028:152755:172, last LSN: 276028:152827:1, number of dump devices: 1,
  device information:
  (FILE=1, TYPE=VIRTUAL_DEVICE: {'{95380B0A-D50B-408F-B95F-1AB8975BA7F8}5'}).
  This is an informational message only. No user action is required.

So, since they can't help us what can I do to track down the process responsible of the backups? We need this because we want to coordinate the backup in an ordered manner, and do the transaction log backups too in order to keep the log at a reasonable size (now we do shrink the log every week, and this is not the good way to go).

2015-09-03 19:02:30.71 Backup      Database backed up. Database:
  db_name, creation date(time): 2012/10/12(20:52:11), pages dumped:
  3290021, first LSN: 276028:152755:172, last LSN: 276028:152827:1,
  number of dump devices: 1, device information: (FILE=1,
  TYPE=VIRTUAL_DEVICE: {'{95380B0A-D50B-408F-B95F-1AB8975BA7F8}5'}).
  This is an informational message only. No user action is required.

The fact that VIRTUAL_DEVICE comes into the backup message clearly says that this is not backup scheduled by T-SQL Job or SQL Server so you wont find it anywhere in SQL Server. You either have some third party tool or windows inbuilt backup mechanism running which is taking this backup.

Now if you really want to find out the source of backup consult windows team or storage team and you would get a fair idea. If all the stakeholders say they do not have idea about the source of backup ( which is highly unlikely) you can stop such backup from happening by disabling SQL Server VSS writer from Services.msc.

Such Third party backups rely on SQL Server VSS writer services to connect to database and take backup.

Before proceeding above I would suggest you to talk to backup/windows admin if they are not aware about any such backup you can go ahead and disable this service. Otherwise you must not and you should consult more regarding this with them.

Please read Information Shedding Light on VSS Backups


Load clob from "insert into" output file In the process of moving data from live to the dev system (completely isolated), I have been given a series of "insert into" files for the affected tables.

One of these tables contains a clob that houses stupidly massive sql queries, so the file contains stupidly massive amounts of text that cannot be inserted via the insert statement...

So, the question is, how can I import the data for this table?

Since they are sql insert files you can only import them using the sqlplus utility. I'd say a dumpfile in this case is the best solution.


How to know if logreader agent already exists? I'm creating a script to recreate a database for test purposes, and the database has some publications.  In order to create or not the logreader agent I need to know if the current database already has one, or destroy the existing agent prior to recreate the database. I know there is some class in SMO can do that, but I need to achieve the same with T-SQL.  Someone can help?

Below T-SQL will give you the log reader agent. You can use the agent name or job_id to drop/delete the log reader agent.

    SELECT SERVER
    ,[command]
    ,sj.job_id
    ,[NAME]
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobsteps sjs ON sjs.job_id = sj.job_id
    AND subsystem = 'logreader'


Store FOR XML result in xml variable (using WITH statement) I am creating xml with a SQL query that works like this: First a CTE, then the SELECT statement that creates the XML.

That works. However I would like to store the output in a XML variable (a table variable is also ok if that's easier). But I can't seem to get it to work (see second snippet). Any suggestions?

The XML generation that works:

;WITH cte
       AS (SELECT ...
          )
  SELECT ...
  FOR XML PATH ('root')

This was my attempt to store it in a table variable, but I couldn't get it to work:

DECLARE @myXml TABLE(
    x xml
);
;WITH cte
       AS (SELECT ...
          )
  INSERT INTO @myXml SELECT ...
  FOR XML PATH ('root')

This is the resulting error message:

  Meldung 6819, Ebene 16, Status 1, Zeile 240
  Die FOR XML-Klausel ist in einer INSERT-Anweisung nicht zulässig.

(I can't use FOR XML in an insert statement.)

You put the assignment after the CTE declaration(s).

declare @X xml;

with C as
(
  select 1 as X, 2 as Y
)
select @X =
  (
  select *
  from C
  for xml path('root')
  );

If you want the XML to end up in a table variable it would look like this.

declare @T table (X xml);

with C as
(
  select 1 as X, 2 as Y
)
insert into @T(X)
select (
       select *
       from C
       for xml path('root')
       );


Changing SQL Server service account My team foundation server is running its databases locally and the person who originally installed the system decided to just use LocalSystem as the service account for SQL Server instead of actually planning the accounts out.

I am trying to create a backup plan (via the TFS power tools Backup Plan extension in the TFS admin console) and it is requiring a domain account in order to complete the plan.

My plan is to quiesce tfs, go into SQL Server Configuration Manager, change the service accounts to a plain 'no rights other than domain user' account, start the mssql services and thne unquiesce tfs.

Are there any problems or concerns I should be aware of before doing this? Will simply changing the LocalSystem account to a domain account cause any problems?

So I finally got the outage to do this yesterday evening and to summarize:

I opened an admin prompt, changed to the 'C:\Program Files\Microsoft
Team Foundation Server 2010\Tools' directory on the application tier
and quiesced TFS (TfsServiceControl.exe quiesce)
Changed all of the databases from LocalSystem to my service (domain)
accounts from inside sql server configuration manager.
Rebooted (I had windows update nagging me for a reboot)
Ensured all of the sql services started
Unquiesced TFS (TfsServiceControl.exe unquiesce)

The only problem I had is somehow inheritance on the Users folder where profiles are stored had some issue that prevented my service account from working. Once I fixed that everything worked just fine. I'm not sure if the profile issue was related to the account change.


ODBC Driver "Oracle in OraClient11g_home1" required but can't seem to get it on Windows 10 Not sure if this was the right StackExchange for this. My job recently upgraded their machines to Windows 10 and I thought setting them up would be a breeze, but I can't for the love of me figure out how to get the correct ODBC drivers on it. I've tried every download I could find and it isn't giving me the right driver.

Not sure where to get the drivers I need. I thought it'd be simple but I just can't find it anywhere after hours of searching. I don't know enough about database connections to know if I can just utilize one of these drivers and somehow translate the configuration from before onto them (old driver just had main entries for Data Source Name, Description, TNS Service Name, and User ID - that's all I enter to make the connection work.)

Never mind. I was looking in the 32bit drivers without realizing. But the problem I have now is that it's a 64bit driver and the Excel sheet that utilizes the connection is looking for the 32bit one.


Pivot with multiple dynamic rows How do i get this?

to look like this:

my create and insert code :

Create Table Countries
(
    Country nvarchar(50),
    Town nvarchar(50),
    City nvarchar(50)
)
GO

Insert into Countries values ('USA','New Jersy','New York')
Insert into Countries values ('USA','houst','Houston')
Insert into Countries values ('USA','Dally','Dallas')

Insert into Countries values ('India','hyder','Hyderabad')
Insert into Countries values ('India','Bang','Bangalore')
Insert into Countries values ('India','Del','New Delhi')
Insert into Countries values ('India','townin','India Town')

Insert into Countries values ('UK','chelsea','London')
Insert into Countries values ('UK','sunderland','Birmingham')
Insert into Countries values ('UK','United','Manchester')

Current pivot code:

Select Country, town1, City1, town2, City2, town3, City3, town4, City4, town5, City5
From
(
Select Country, City, 'City'+ cast(row_number() over(partition by Country order by Country) as varchar(10)) ColumnSequence,
Town, 'Town'+ cast(row_number() over(partition by Country order by Country) as varchar(10)) ColumnSequence2
from Countries
) Temp
pivot
(
max(City)
for ColumnSequence in (City1, City2, City3, City4, City5)
) Piv
pivot
(
max(Town)
for ColumnSequence2 in (town1, town2, town3, town4, town5)
) Piv
group by Country, town1, City1, town2, City2, town3, City3, town4, City4,
town5, City5

Simply get MAX value of Towns and Cities grouped by Country.

SELECT Country,
       MAX(town1) Town1, MAX(City1) City1,
       MAX(town2) Town2, MAX(City2) City2,
       MAX(town3) Town3, MAX(City3) City3,
       MAX(town4) Town4, MAX(City4) City4,
       MAX(town5) Town5, MAX(City5) City5
FROM (
      SELECT Country,
             City,
             'City' + CAST(ROW_NUMBER() OVER (PARTITION BY Country ORDER BY Country) AS varchar(10)) ColumnSequence,
             Town,
             'Town'+ CAST(ROW_NUMBER() OVER (PARTITION BY Country ORDER BY Country) AS varchar(10)) ColumnSequence2
      FROM Countries
     ) Temp
PIVOT (MAX(City) FOR ColumnSequence IN (City1, City2, City3, City4, City5)) Piv
PIVOT (MAX(Town) FOR ColumnSequence2 IN (town1, town2, town3, town4, town5)) Piv
GROUP BY Country
GO

Country | Town1     | City1     | Town2      | City2      | Town3  | City3      | Town4  | City4      | Town5 | City5
:------ | :-------- | :-------- | :--------- | :--------- | :----- | :--------- | :----- | :--------- | :---- | :----
India   | hyder     | Hyderabad | Bang       | Bangalore  | Del    | New Delhi  | townin | India Town | null  | null
UK      | chelsea   | London    | sunderland | Birmingham | United | Manchester | null   | null       | null  | null
USA     | New Jersy | New York  | houst      | Houston    | Dally  | Dallas     | null   | null       | null  | null

Warning: Null value is eliminated by an aggregate or other SET operation.

dbfiddle here


Best datatype to store betting odds? I have a lot of soccer betting odds (hundreds per match and thousands of matches), for example:

<_10Bet_Home_Home>8.00</_10Bet_Home_Home>
<_10Bet_Home_Away>1.39</_10Bet_Home_Away>
<_10Bet_Home_Draw>4.50</_10Bet_Home_Draw>
<Bet_At_Home_Home>7.50</Bet_At_Home_Home>
<Bet_At_Home_Away>1.40</Bet_At_Home_Away>
<Bet_At_Home_Draw>4.30</Bet_At_Home_Draw>
<Bet365_Home>7.50</Bet365_Home>
<Bet365_Away>1.40</Bet365_Away>
<Bet365_Draw>4.50</Bet365_Draw>
<Bwin_Home>7.00</Bwin_Home>
<Bwin_Away>1.45</Bwin_Away>
<Bwin_Draw>4.25</Bwin_Draw>
<MyBet_Home>7.50</MyBet_Home>
<MyBet_Away>1.43</MyBet_Away>
<MyBet_Draw>4.50</MyBet_Draw>
<NordicBet_Home>7.85</NordicBet_Home>
<NordicBet_Away>1.40</NordicBet_Away>
<NordicBet_Draw>4.50</NordicBet_Draw>
....

What is the best datatype to store them? I need to occupy less storage as possible but also be efficient with sums, means etc.

They don't have a theoretical max value but I don't think they'll go above 100... Decimal(4,2) unsigned?

I know the question may be silly but I'm looking for a confirm from database experts...

I would use Decimal(4,2) if you are confident that 99.99 is the max value.  Decimal(4,2) will consume 2 bytes while decimal(5,2) will consume 3 bytes but give you a safety net if the value were to exceed 99.99; it's up to you how confident your data set will stay under 100.


Aggregating two queries and returning a COUNT of some permutations Hopefully the title can make sense to somebody, so I can get somebody working with a database (BigQuery in my case) to look into into.

Given two quarters, I need to find out the # of orders placed by each consumer for each of the two. I need also to count how many consumer I have in Q3 vs Q4 and do some calculation on the consumer retention.

I wish I could obtain the majority of this numbers with just one query, thus avoiding a number of queries.

I'll paste below a query in the attempt to explain what I need

  SELECT T1.email , T2.email
  FROM
  (
          SELECT email, 1 AS KEY
          FROM [reporting.order]
          WHERE 1 = 1
          AND timestampOrdered > TIMESTAMP_TO_USEC(TIMESTAMP("2014-07-01 05:00:00"))
          AND timestampOrdered < TIMESTAMP_TO_USEC(TIMESTAMP("2014-10-01 05:00:00"))
          AND orderStatus IN ('Delivered', 'Collected', 'PaymentCaptured')
          AND name NOT LIKE '%test%'
          AND restaurantId NOT LIKE '%test%'
          GROUP BY email
  ) T1
  JOIN (
          SELECT email, 1 AS KEY
          FROM [reporting.order]
          WHERE 1 = 1
          AND timestampOrdered > TIMESTAMP_TO_USEC(TIMESTAMP("2014-10-01 05:00:00"))
          AND timestampOrdered < TIMESTAMP_TO_USEC(TIMESTAMP("2015-01-01 05:00:00"))
          AND orderStatus IN ('Delivered', 'Collected', 'PaymentCaptured')
          AND name NOT LIKE '%test%'
          AND restaurantId NOT LIKE '%test%'
          GROUP BY email
  ) T2
  ON T1.KEY = T2.KEY

By adding an outer SELECT, with the above results I was able to count how many emails are in Q4 that were in Q3 (SELECT COUNT(CASE WHEN (T1.Email = T2.Email) THEN 1 ELSE NULL END) AS InPrevQ FROM) but anything more than this.

I thought to use the WINDOW function, but I'm struggling to understand its usage.

You are probably over-thinking it.  What if you were to consider your "base" query as something along the lines of the following:

SELECT  CASE
            WHEN TimestampOrdered >= TIMESTAMP_TO_USEC( TIMESTAMP( "2014-07-01 05:00:00" ) )
             AND TimestampOrdered < TIMESTAMP_TO_USEC( TIMESTAMP( "2014-10-01 05:00:00" ) )
              THEN Email
        END AS orderedbyQ3,
        CASE
            WHEN TimestampOrdered >= TIMESTAMP_TO_USEC( TIMESTAMP( "2014-10-01 05:00:00" ) )
             AND TimestampOrdered < TIMESTAMP_TO_USEC( TIMESTAMP( "2015-01-01 05:00:00" ) )
              THEN Email
        END AS orderedbyQ4
FROM    [reporting.order]
WHERE   timestampOrdered >= TIMESTAMP_TO_USEC( TIMESTAMP( "2014-07-01 05:00:00" ) )
    AND timestampOrdered < TIMESTAMP_TO_USEC( TIMESTAMP( "2015-01-01 05:00:00" ) )
    AND orderStatus IN ( 'Delivered', 'Collected', 'PaymentCaptured' )
    AND name NOT LIKE '%test%'
    AND restaurantId NOT LIKE '%test%'
GROUP BY email;

You could then rather easily get distinct email address counts and order counts, or whatever else you needed to derive:

SELECT  COUNT( DISTINCT OrderedByQ3 ) AS RestaurantsQ3,
        COUNT( OrderedByQ3 ) AS OrdersQ3,
        COUNT( DISTINCT OrderedByQ4 ) AS RestaurantsQ4,
        COUNT( OrderedByQ4 ) AS OrdersQ4
FROM (  SELECT  CASE
                    WHEN TimestampOrdered >= TIMESTAMP_TO_USEC( TIMESTAMP( "2014-07-01 05:00:00" ) )
                     AND TimestampOrdered < TIMESTAMP_TO_USEC( TIMESTAMP( "2014-10-01 05:00:00" ) )
                      THEN Email
                END AS OrderedByQ3,
                CASE
                    WHEN TimestampOrdered >= TIMESTAMP_TO_USEC( TIMESTAMP( "2014-10-01 05:00:00" ) )
                     AND TimestampOrdered < TIMESTAMP_TO_USEC( TIMESTAMP( "2015-01-01 05:00:00" ) )
                      THEN Email
                END AS OrderedByQ4
        FROM    [reporting.order]
        WHERE   timestampOrdered >= TIMESTAMP_TO_USEC( TIMESTAMP( "2014-07-01 05:00:00" ) )
            AND timestampOrdered < TIMESTAMP_TO_USEC( TIMESTAMP( "2015-01-01 05:00:00" ) )
            AND orderStatus IN ( 'Delivered', 'Collected', 'PaymentCaptured' )
            AND name NOT LIKE '%test%'
            AND restaurantId NOT LIKE '%test%'
        GROUP BY email ) t;

Sorry if the syntax isn't perfect, I don't have access to a BigQuery DB.


Is there any other way to replace cursor for the following scenario Table IA

Id Status  Details
1   open    aaa
2   close   bbb
3   open    ccc
4   open    ddd
5   open    eeee
6   open    ffff
7   open    gggg
8   close   iii
9   close   hhh
10  open    jjj
primary key ID

Table snapshot_IA

Id  Status  Details
1   open    aaa
2   close   bbb
3   open    ccc
4   open    ddd
5   open    eeee
4   open    ffff
5   open    sdsd
4   open    sdsdd

IA table data changes daily
Snapshot table does not have primary key
snapshot table should have new rows from table IA
snapshot table should get rows from IA table if IA table ID present in snapshot table but status is open in the snapshot table

eg.

select T1.id1,T1.status,T1.details from
IA T1,
snapshot_IA T2
where
T1.id1 = T2.ID1
and T2.status <> 'close'

One way of doing it is using cursor.

Can you suggest any other efficient way than this?

Insert into IA
 select T1.id1,T1.status,T1.details from
IA T1 where not exists (
Select 1 from Snapshot_IA WHERE
T1.id1 = T2.ID1
and T2.status = 'close')


sp_dts_getfolder isn't in my MSDB database, any ideas why? We're currently moving our applications from a SQL 2005 to a SQL 2008 R2 database and everything has gone well(ish) but now one of our applications calls a stored proc called sp_dts_getfolder which is meant to be in the MSDB database.

But it doesn't seem to be there at all, did we not install something or is this no longer a valid stored procedure in 2008 R2? What replaced it?

Why I think it can be something about the maintenance plans is because on
msdn it states:

  If the account that the SQL Server Agent runs under on the target
  server (TSX) is not a member of the sysadmin fixed server role on the
  master server (MSX), to import a distributed maintenance plan, the
  account must be granted the following permission on the master server.

  .......

  If this permission is not
  granted, the following error will appear in the SQL Server Agent log
  on the target server when the multiserver SQL Server Agent Job that
  runs the maintenance plan is refreshed by the target server:

  "Failed to import Maintenance Plan  from <
  NameOfMasterServer>:
  Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: While trying to
  find a folder on SQL an OLE DB error was encountered with error code
  0x80040E09 (The EXECUTE permission was denied on the object
  'sp_dts_getfolder', database 'msdb', schema 'dbo'.)"

  After these permissions are changed, each existing distributed
  maintenance plan must be reloaded on the target server. Make a small
  change to each plan and save. This will force the target server to
  reload the maintenance plan from the master server. By default, the
  target server will update jobs from the master server every 60
  seconds. There will be a minimum delay of 120 seconds after you save
  the maintenance plan before it appears and can be run on the target
  server.

Here is the link to read about it

You should also looked at this kb. This is a fix from Microsoft regarding this problem.

After some investigation I found that going from sql 2005 to sql 2008 there are some changes in the system store procedures

  Now the twist in the story is, since SSIS 2005 has grown up from DTS,
  the system tables and system stored procedures use a naming convention
  like "dts" in its name as you can see in first column of table below.
  With SSIS 2008, the SSIS team has standardize the naming convention
  and uses "ssis" in its name as you can see in the second column of the
  table below. So if you are using these system tables or system stored
  procedure in your code and upgrading to SSIS 2008, your code will
  break unless you change your code to accommodate this new naming
  convention.

So the [msdb].[dbo].[sp_dts_getfolder] is actually changed to [msdb].[dbo].[sp_ssis_getfolder].

You can find all of the other procedure changed and some more information in the article Best Practice #17 - Names of system tables and procedures have changed between SSIS 2005 and SSIS 2008


How to list all users that have grants over a package I'm looking for some query to list all users that have grants over a package.

For example, user Schema_A has been granted to execute package: B.MyPackage.

I'm querying views or tables like: role_tab_privs, role_sys_privs,sys.dba_sys_privs, dba_role_privs... but I can't find what I'm looking.

If the privilege has been granted directly or to a role, the grant will appear in DBA_TAB_PRIVS

SELECT grantee, privilege
  FROM dba_tab_privs
 WHERE owner = 'B'
   AND table_name = 'MYPACKAGE'
   AND privilege = 'EXECUTE'

If the grantee is a role, you would then need to look at dba_role_privs to see what users (or roles) have been granted that role and follow the chain if you have roles granted to other roles.  If you need to account for users that have grants because of the (very dangerous) ANY grants (i.e. EXECUTE ANY PROCEDURE), that would require a separate query.

If you want to get more sophisticated than simply doing a straight query against dba_tab_privs, though, you are probably better off using on of Pete Finnigan's scripts like the who_has_priv.sql (or who_has_priv_procedure.sql).  Pete is probably the leading expert on Oracle security so these are much more likely to account for every possible corner case than anything I would attempt to cobble together.


Estimating bandwidth required for streaming replication I have an existing postgresql server that I would like to replicate to another location using streaming replication.  How can I best estimate the amount of bandwidth that will be required to keep the databases reasonably synchronized, using the actual workload I have now?  I've read through the pg_stat_* list but nothing jumps out at me for an overall transaction rate or amount of data written (except for pg_stat_bgwriter, which has totals since the server was last started).  Would the number of buffers (which I believe are 8kB each) written according to the checkpoint log be in the correct ballpark?

Well, very roughly speaking, at steady-state (i.e. after you have a standby server initialized with a basebackup and in-sync with the primary), the amount of bandwidth needed to keep a standby in-sync will be roughly your primary's WAL volume throughput.

Now, what is your primary's WAL volume throughput? Basically, how many 16 MiB WAL files your primary server produces per unit of time. You can either poke around in your primary's pg_xlog directory and see how many new files are being churned through. Or here's a nifty shell command using psql, credit of depesz that you can use:

(echo "ibase=16" psql -qAtX -c "select pg_xlogfile_name(pg_current_xlog_location())" | cut -b 9-16,23-24) | bc

The output of that command will give you a decimal count of what number WAL file you are on. Run that command again in, say, 10 minutes. Subtract the first number from the second, and that gives you how many 16 MiB WAL segments would need to be synchronized between the primary and the standby in that period of time. That will answer your question about:

  ... the amount of bandwidth that will be required to keep the databases reasonably synchronized

But! A few caveats:

The initial step of copying over a base backup to bring a new standby online will need a LOT of bandwidth (since you will effectively need to copy over your entire DATADIR to bring up the new server), and then the standup will have work to do to catch up to the primary's current position.  Effectively, this means you'll need plenty of additional bandwidth on top of your steady-state needs to be able to reliably initialize a new standby server in a reasonable time.
The volume of WAL produced by the primary will be influenced by settings such as wal_level, which must be cranked up to hot_standby when you actually want to bring a standby server up
Useful settings to enable once you're using streaming replication such as hot_standby_feedback will consume further bandwidth.
You will likely find that your WAL throughput will vary considerably depending on how busy your database is, autovacuuming, checkpoints, DDL, etc. You'll want to be able to absorb this additional WAL volume to keep your standby in-sync.


Join with comma separated values in SQL Server I have a table [CourseMaster] LIKE

CourseId    CourseName
-----------------------
  01          ABC
  02          DEF
  03          GHI
  04          JKL
  05          MNO
  06          PQR
  07          STU

And I have another table [StudentMaster] for Student Details LIKE

ROLLNO  NAME    ADDRESS          Course
------------------------------------------------
12345   RAM     RAM ADDRESS      01,02,06
25695   HARI    HARI ADDRESS     02,06
89685   JEFF    JEFF ADDRESS     03,05,06,07
47896   DAISY   DAISY ADDRESS    03

Here I want to fetch the Student details with CourseName(Not CourseId).

If the values in Course is not comma separated than it would be very simple query to fetch the details with join.

As of my knowledge I can run two queries for the same result what I want, One query for fetching the details of student from [StudentMaster] to the front end. And other one for only fetching the CourseName from [CourseMaster] by corresponding CourseId through a loop.

But the fact I want the result by only one query rather than write two queries for this small task.

I guess it is 100% possible. And my expected result will look like:

ROLLNO  NAME    ADDRESS         Course
-------------------------------------------
12345   RAM     RAM ADDRESS     ABC,DEF,PQR
25695   HARI    HARI ADDRESS    DEF,PQR
89685   JEFF    JEFF ADDRESS    GHI,MNO,PQR,STU
47896   DAISY   DAISY ADDRESS   GHI

Thank you and any valuable suggestion will be highly appreciate.

You really should have a junction table for the courses a student is taking, rather than jamming comma-separated values into a single tuple. If you think this is the last problem you'll have because of this sub-optimal design, you're in for a big surprise. You really should have the owners of this project go read up on normalization - yes it's painful to change your schema, but so is constantly dealing with the limitations of leaving it like it is.

Anyway, with that said, you need a split function. Since your comma-separated values are numeric, you can get away with a variation on my XML function; there are several others to choose from in this blog post.

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       VARCHAR(MAX),
   @Delimiter  CHAR(1) = ','
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN
   (
      SELECT Item = y.i.value('(./text())[1]', 'varchar(8000)')
      FROM
      (
        SELECT x = CONVERT(XML, ''
          + REPLACE(@List, @Delimiter, '')
          + '').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );

Now, your query is:

;WITH x AS
(
  SELECT s.ROLLNO, s.Name, s.Address, c.CourseId, c.CourseName
  FROM dbo.StudentMaster AS s
  CROSS APPLY dbo.SplitStrings_XML(s.Course, default) AS f
  INNER JOIN dbo.CourseMaster AS c
  ON f.item = c.CourseId
)
SELECT ROLLNO, Name, Address, STUFF((
  SELECT ',' + CourseName FROM x AS x2
  WHERE x2.ROLLNO = x.ROLLNO
  ORDER BY CourseId FOR XML PATH,
  TYPE).value(N'.[1]',N'varchar(max)'), 1, 1, '')
FROM x
GROUP BY ROLLNO, Name, Address;

Again, this is a complicated solution, and because of your inferior database structure, the next query you have to perform will be equally convoluted and cumbersome. There's a reason this type of design is argued against in just about every blog, essay or book about the topic...


How to use GROUP BY in an efficient way without losing attributes? I am working with PostgreSQL.

My idea is to group players who appear more than 3 times in table-a.

table-a = <player_id, year_world, c, d, e>

With the first subquery I get player_id and the number of times that player played a world cup.
And with the NATURAL JOIN I recover the year or other attributes in the tuples that player appears.

The following query works but I want to optimize it:

SELECT player_id , year_world
FROM    (SELECT player_id , count(player_id ) player_id_count
        FROM <table-a>
        GROUP BY player_id
        HAVING count(player_id ) > 3) playersMoreThree
NATURAL JOIN <table-a>

I would like to know if it is possible not to use the NATURAL JOIN to obtain the other attributes of the table. Because when I put the rest of the attributes I want to project into SELECT and in theGROUP BY I get an empty table.

  Keep in mind that the attribute that is added to the SELECT of the subquery of the GROUP BY has to be determined by the attribute that we are grouping. That is, for each player_id value, I can always have the same value of the new attribute, if for a same value of player_id I have different values of the new attribute, this is not determined. For add an attribute that is not correlated by what group I use NATURAL JOIN or another solution.

The following query without NATURAL JOIN does not work for me:

SELECT player_id, count(player_id ) player_id_count, year_world, c, d, e
FROM <table-a>
GROUP BY player_id, year_world, c, d, e
HAVING count(player_id ) > 3

What would be the most efficient way in DML?

You can use a window function:

SELECT player_id , ... FROM (
    SELECT player_id, year_world, ...
         , COUNT(*) over (PARTITION BY player_id) AS player_id_count
    FROM <table-a>
) AS T
WHERE player_id_count > 3

over (partition by player_id) defines the window where count(*) should be applied. In this case, we want the window to be all rows for the player in question. This means that you will get the count for player_id on all rows with player_id.


Replicate some tables without transferring the entire log I have a mysql database that contains some tables with private information, and some tables with public information.

I would like to replicate only the tables containing public information from one database to another, making sure that NO confidential information ever gets stored on the slave.

I know I can use the replicate-do-table to specify that only some tables are replicated, but my understanding is that the entire bin log is transferred to the slave.

Is there a way to ensure that only the public information is transferred to the slave?

I am very hesitant to add another copy of the database to an existing server - I just don't think the existing server has the available capacity, in either RAM or CPU.

The only way to filter on one DB server is to run multiple instances of MySQL on one DB Server.

SERVER DB1

Port 3306 would be your normal database instance for your application

Port 3307 would be a slave of Port 3306

There are a few things you need to do with the MySQL instance running on DB1

Enable the following in my.cnf for

log-bin=mysql-bin
log-slave-updates
replicate-do-table=db1.public_tb1
replicate-do-table=db2.public_tb2
...
replicate-do-table=dbn.public_tbn

As an option, convert all tables in DB1 3307 to the BLACKHOLE storage engine.

That way, DB1 3307 only has binlogs with information. No actual data.

SERVER DB2

Setup MySQL instance and make it Slave from the 3307 instance of DB1. Why is that good ?

Because the binlogs in the DB1 3307 instance should only contain the public info. Thus, all slaves from DB1 3307 will only see public info.

CAVEAT

Please see my other posts on how to use BLACKHOLE tables in replication

Apr 18, 2013 : Single slave - multiple master MySQL replication
Feb 03, 2012 : One slave, multiple masters MySql
Jun 01, 2011 : What can we do in MySQL 5.0 Replication to address bandwidth concerns?
May 16, 2011 : Is Multi Master Single Slave possible in mySQL DB?
Mar 11, 2011 : MySQL in star topology


SQL ISO Standard for group concatenation Is there an operator in the latest standard SQL:2011 (or earlier) that will handle group concatenation the likes of which we currently have to resort to these tricks for?

GROUP_CONCAT in MySQL
FOR XML in SQL Server
LISTAGG in Oracle
string_agg in PostgreSQL

Here's a recent question on this site for which various answers were given to different RDBMS.

Since this is a small question for the heavyweight site DBA.SE, please find two sub-parts to the question:

Is there a publicly accessible draft of SQL:2011?
Which part of the SQL standard (any revision) states that CONCAT NULL should YIELD NULL? I can't seem to track it down in the volume of text in the standard.

The wiscorp.com page SQL Standards has several older revisions and drafts of SQL:20nn (zip):

The 7IWD2-02-Foundation-2011-12.pdf, with a date of 2011-12-21 has at page 289:

  If at least one of S1 and S2 is the null value, then the result of the <concatenation> is the null value.


How to check the status of SQL Server? I need to check the status of SQL Server. When I say status its not the status for each database, but rather for the whole SQL Server.

I need to know what are the queries running in the server and no. of connections, which query is taking too long.

Can anyone please help me with this..

You'd have more options here:

Perfmon (or ResourceMonitor on W7+) for general resource information;
Activity Monitor in Management Studio;
Inside SQL Server:

Glenn Berry's DMV queries;
Adam Machanic's stored procedure sp_WhoIsActive.

There are also many other external monitoring tools (some free, some paid) that you can use, but these ones are free and worth mentioning as a first step in your work.


SQL getting IDs from one table with multiple entries in another table In my database I have two tables: Persons and PersonAttributes.
One Person may have several Attributes associated with it.

What I would like to do is get a list of all Persons(/IDs) that have at least the specified attributes associated with them in the PersonAttributes table.

For one attribute this can easily be done with just one WHERE statement.
My problem is that I want to do this for multiple attributes.

The only solution I could come up with is doing a SELECT for each attribute and then JOINing them.
While I can build such a query programmatically it seems pretty complex and I was hoping that there may be an easier solution.

For better understanding here is a SQLFiddle including my solution for 3 attributes.

EDIT: Changed the SQLFiddle link. Query now looks like this:

SELECT Persons.ID
FROM Persons
  JOIN (SELECT * FROM PersonAttributes WHERE PersonAttributes.Attr = 'b') t1
    ON Persons.ID = t1.ID
  JOIN (SELECT * FROM PersonAttributes WHERE PersonAttributes.Attr = 'c') t2
    ON t1.ID = t2.ID
  JOIN (SELECT * FROM PersonAttributes WHERE PersonAttributes.Attr = 'd') t3
    ON t2.ID = t3.ID;

Apparently, your PersonAttributes table is designed using the EAV model. This model has the advantage of being easily extensible: attributes are stored as rows, and adding new rows is easy. However, querying this kind of table is more difficult than those designed the traditional way (attributes stored as columns).

Your solution is quite illustrative of how much more trouble it can be to accomplish a fairly simple task with an EAV-modelled table. It is actually one of the common ways to solve a problem like yours, although I would suggest you try rewriting it without using derived tables – like this:

SELECT p.ID
FROM Persons AS p
  JOIN PersonAttributes AS paB ON p.ID = paB.ID
  JOIN PersonAttributes AS paC ON p.ID = paC.ID
  JOIN PersonAttributes AS paD ON p.ID = paD.ID
WHERE paB.Attr = 'b'
  AND paC.Attr = 'c'
  AND paD.Attr = 'd';

The performance will likely remain the same as with your syntax, but without making the query any faster this rewrite will at least make it more concise and arguably more readable.

That being said, there is another method, fairly common as well, that you could employ, which might offer better performance as the number of the attributes increases. It uses grouping and aggregation:

SELECT
  ID
FROM
  PersonAttributes
WHERE
  Attr IN ('b', 'c', 'd')
GROUP BY
  ID
HAVING
  COUNT(*) = 3
;

By this method, all rows that have any of the specified attributes are retrieved and grouped by ID. In order to determine the groups (persons) having all three attributes, a HAVING filter is introduced to compare the number of rows* in each group to the total number of attributes in the IN list.

The method can be slightly generalised if you can afford storing the attributes to search for in a (temporary) table. Here is what it would look like in that case:

SELECT
  pa.ID
FROM
  PersonAttributes AS pa
  INNER JOIN QueriedAttributes AS qa ON pa.Attr = qa.Attr
GROUP BY
  pa.ID
HAVING
  COUNT(*) = (SELECT COUNT(*) FROM QueriedAttributes)
;

No WHERE clause here – it is replaced by the join to the table of queried attributes, and the total number of attributes required to match is derived from the same table instead of being hard-coded.

This kind of problem is commonly known as relational division. It is discussed in detail in this article by Joe Celko:

Divided We Stand: The SQL of Relational Division

*This particular implementation of the grouping method assumes there is always one row per attribute per person, so COUNT(*) works correctly. If attributes of the same kind may, or will later be allowed to, repeat per person, use COUNT(DISTINCT Attr) instead.


pt-table-checksum with slaves using non-standard ports Trying to run pt-table-checksum to detect slave drift in MySQL replication. My slaves are not using the standard TCP listening port however, and so when I run the tool I get an error:

Cannot connect to P=3306,h=,p=...

It still checksums the master's tables, but without being able to connect to the slaves it's not very useful. I can't figure out how to specify alternate ports for the slaves when running the command. My current command looks like

pt-table-checksum -h  -P 3388 -p

The issue is that the replicas don't report their ports to the master.  By default pt-table-checksum tries to connect by filling unknowns with defaults; if the unknowns aren't using defaults, this will fail.  In this case, there are two options:

1) Use the --recursion-method option to change from inspecting SHOW PROCESSLIST to inspecting SHOW SLAVE HOSTS, and configure slaves to report their host.  I don't recommend this.

2) I recommend, instead, that you use --recursion-method=dsn and create a table full of DSN (data source name) instructions that tell the tool how to connect to each replica.  If this table is foo.bar, then you will use --recursion-method=dsn=D=foo,t=bar.

This is only available in the 2.0 series of the toolkit, but you should be using that anyway, because pt-table-checksum is hugely improved in 2.0.


How can I improve this DB query? it takes too much time The query is this:

$today = $date->getTimestamp();

SELECT t.*, u.screen_name, u.profile_image_url
  FROM table AS t
  LEFT JOIN users AS u
  ON t.user_id=u.id_str
  WHERE UNIX_TIMESTAMP(t.created_at) > {$today}
  ORDER BY id DESC
  LIMIT 12;

Even though you didn't post table definitions in your question so I can't tell for sure what is missed, the following indexes will be beneficial  :

Index on table (user_id, created_at)
Index on users(id_str) or even users(id_str,screen_name,profile_image_url)
I guess you already have an index on table(id)

UPDATE

Change WHERE :
from WHERE UNIX_TIMESTAMP(t.created_at) > {$today}  to WHERE t.created_at > FROM_UNIXTIME({$today}), this way optimizer will use index that includes create_at column more efficiently.


encoding format of multilingual text while saving to database I have website that save content in various languages. just to display that i have enter to database.

Suppose i copy a French text in sql server table column it will display correct on my website, but when i try to save it through my website to table then it does not save text in same encoding as before insert method.

My question is regarding sql server text encoding format that it use to save such type of multilingual text??

i want convert my text encoding from website to sql server encoded text.

Is there issue of Collation. Database behind a Multilingual User Interface

One suggestion would be to use nvarchar(n) type column. You can then store UCS2 data in that column. The width of the column can be decided based on the data.


How to change MSSQL Management Studio theme? Different from:
How to apply a theme to SQL Server Management Studio 2008?

What I want is to change the theme not colour scheme of the editor.

I want it to look as close as possible as Dark in 2015.

Install the 2010 Color Theme Editor in VS 2010.
Find the files somehwere in C:\Users\xxxxx\AppData\Local\Microsoft\VisualStudio\xx.x\Extensions\ .. for me, it was in \shvn3avp.com for the current version of the plugin.
Copy those files to a new folder: C:\Program Files (x86)\Microsoft SQL Server\xxx\Tools\Binn\ManagementStudio\Extensions\VSTheme
Replace extension.vsixmanifest contents with:

<?xml version="1.0" encoding="utf-8"?>
<Vsix xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0.0" xmlns="http://schemas.microsoft.com/developer/vsx-schema/2010">
<Identifier Id="46b3817c-b0c3-42d1-8cea-d373f781b21e">
  <Name>Visual Studio Color Theme Editor</Name>
  <Author>Microsoft</Author>
  <Version>1.3</Version>
  <Description xml:space="preserve">Allows users to customize the color palette used for menus, toolbars, tabs, title bars, and other environment colors.</Description>
  <Locale>1033</Locale>
  <Icon>ThemeManagerIcon.png</Icon>
  <PreviewImage>ThemeManagerPreview.png</PreviewImage>
  <InstalledByMsi>true</InstalledByMsi>
  <SupportedProducts>
    <IsolatedShell Version="1.0">ssms</IsolatedShell>
    <VisualStudio Version="10.0">
      <Edition>Ultimate</Edition>
      <Edition>Premium</Edition>
      <Edition>Pro</Edition>
      <Edition>IntegratedShell</Edition>
    </VisualStudio>
  </SupportedProducts>
  <SupportedFrameworkRuntimeEdition MinVersion="3.0" MaxVersion="6.0" />
  <SystemComponent>true</SystemComponent>
</Identifier>
<References />
<Content>
  <VsPackage>ThemeManagerPackage.pkgdef</VsPackage>
</Content>

Import this setting:
http://studiostyl.es/schemes/vs2012-dark-theme-for-vs2010
On the Customize Color dialog that opens on the Theme menu provided by the extension import his theme

Result:

For 2015 and SQL Management Studio 17RC1

Install the extension on VS2015 instead. Follow the above steps.

The folder on my computer was C:\Users\xxxxxx\AppData\Local\Microsoft\VisualStudio\14.0\Extensions\owbugenu.gif

The xml is:

<!-- language: lang-xml -->
<?xml version="1.0" encoding="utf-8"?>
<Vsix xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0.0" xmlns="http://schemas.microsoft.com/developer/vsx-schema/2010">
  <Identifier Id="B8CED2B3-FF48-4204-88E9-036CA39B83C8">
    <Name>Visual Studio 2015 Color Theme Editor</Name>
    <Author>Microsoft</Author>
    <Version>2.0</Version>
    <Description xml:space="preserve">Allows users to create and edit Visual Studio themes.  Themes can customize colors used for menus, toolbars, tabs, titlebars, the text editor, and other environment colors.</Description>
    <Locale>1033</Locale>
    <InstalledByMsi>true</InstalledByMsi>
    <SupportedProducts>
      <IsolatedShell Version="1.0">ssms</IsolatedShell>
    </SupportedProducts>
    <SupportedFrameworkRuntimeEdition MinVersion="4.0" MaxVersion="4.0" />
    <SystemComponent>true</SystemComponent>
  </Identifier>
  <References />
  <Content>
    <VsPackage>ColorThemeEditor.pkgdef</VsPackage>
    <VsPackage>Colors\Red.pkgdef</VsPackage>
    <VsPackage>Colors\Purple.pkgdef</VsPackage>
    <VsPackage>Colors\Green.pkgdef</VsPackage>
    <VsPackage>Colors\Tan.pkgdef</VsPackage>
    <VsPackage>Colors\DarkWithLightEditor.pkgdef</VsPackage>
    <VsPackage>Colors\LightWithDarkEditor.pkgdef</VsPackage>
    <VsPackage>Colors\SolarizedDark.pkgdef</VsPackage>
    <VsPackage>Colors\SolarizedLight.pkgdef</VsPackage>
    <MefComponent>ColorThemeEditor.dll</MefComponent>
    <VsPackage>Colors\ThemeNames.pkgdef</VsPackage>
    <VsPackage>PkgDefAssemblyCodeBase.pkgdef</VsPackage>
  </Content>
</Vsix>


Error while changing the authentication - mongoDB I had database level authentication in mongoDB, I had tried to change the password for existing user. It is throwing not authorized error.

Steps i followed to change the password

use wallet
db.auth("username","password")
db.changeUserPassword("username","newpassword");

Error

Changing password failed: not authorized for update on wallet.system.users at src/mongo/shell/db.js:186

Any suggestion will be grateful.

You need to login and use ADMIN database to perform the operation.


what is the fast way to prepare test db? slimming mysql db My idea is this:

get mysql dump file from production (GBs)
prepare set of requests based on access logs and run them
record all accessed records in mysql
remove everything else in my db
finally: have lightweight db and recorded requests as tests.

I have a problem with points 3+4. I wonder if such a feature even exists for MySQL, with the possibility to delete, and drop everything that was not accessed (including indexes rebuild).

Of course, having a lightweight DB is needed to easily auto-generate test fixtures, and this is the main purpose.

MySQL doesn't contain any built-in way of marking rows when they're accessed. You could conceivably use triggers to record updates, inserts, and deletes, but not selects.

It's conceivable that somebody could adapt the alpha-quality MySQL proxy server, or the MySQL relay, to build a recording proxy. But that would be a lot of work, and it would yield the queries.


SQL Server 2008 Database Restore Error - Cannot open backup device 15105 I am trying to restore from a backup and getting the following error

  Msg 3201, Level 16, State 2, Line 1
  Cannot open backup device 'Z:\FULL_BACKUP.bak'. Operating system error 3(failed to retrieve text for this error. Reason: 15105).
  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

The code that I used in SQL Server is as follows (I also tried using the dialog screens and got the same result)

RESTORE DATABASE DEX
FROM DISK = 'Z:\FULL_BACKUP.bak'
WITH MOVE 'Decri' TO 'E:\Test\DEX.mdf',
MOVE 'Decri_log' TO 'E:\Test\DEX.LDF'

It may be worth noting that the "FULL_BACKUP" is created by the same SQL server, without any issues (so I assume that the server has permissions).

Drive Z is a network path, for which there is 'full access' to all domain users.

The full backup is 200Gb

Drive E (local) has 500Gb free space

Any help would be appreciated.

OS Error code 3 is ERROR_PATH_NOT_FOUND: The system cannot find the path specified. With a path starting with Z: my pshyhic powers tell me it is a mapped drive. Drive mapping is session scoped and I'm convinced your service session does not see Z:. Mapping a drive to be seen by a service is, for all practical purposes, impossible.

Use a valid path. Use UNC names like \\server\share. Consider the security implication of impersonation and delegation when accessing remote shares.


How to reference primary key when inserting in MySQL I have a database containing the tables authors and books.

I don't know how to correctly assign the foreign key in the books table. I read the information provided in this link and have constructed my DDL queries as follows:

CREATE TABLE if not exists authors(
-> author_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(30)
);

CREATE TABLE if not exists books(
-> book_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> title VARCHAR(255),
-> genre VARCHAR(30),
-> FOREIGN KEY(author_id) REFERENCES authors(author_id)
);

Is this the correct way to create a foreign key relationship between my authors and books tables?

You need to declare the column itself, so the books table should look like:

CREATE TABLE if not exists books
( book_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
, title VARCHAR(255) NOT NULL
, genre VARCHAR(30) NOT NULL
, author_id INT UNSIGNED NOT NULL

,    FOREIGN KEY(author_id) REFERENCES authors(author_id)
);

I added NOT NULL on columns title and genre since I believe they are mandatory in most situations.

I would say that you also need something that uniquely identifies an author in real life, let's assume name does:

CREATE TABLE if not exists authors
( author_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
, name VARCHAR(30) NOT NULL UNIQUE
);

Now you can insert a new book like:

INSERT INTO books (title, genre, author_id)
SELECT 'The Shining', 'Horror', author_id
FROM authors
WHERE name = 'Stephen King';


How can we ensure security and integrity of data stored in MongoDB? Since MongoDB lacks support of transactions how one can ensure consistency of data stored in MongoDB database?

You are mixing concepts here. Transactions are related to atomicity and guarantee that multiple operations are either executed as a whole or not at all.
Consistency, however is (simplified) the guarantee that after each successful transaction or operation applied, the database is in a a valid, usable state.
An example

Note This example is simplified for the sake of understandability.

Often people say that you can not use MongoDB for financial operations such as account balancing, since it lacks transactions. They could not be farer from the truth.
Take a simple example of two users, each with an account:
{
  _id:"act1",
  owner: "Foo Bar"
},
{
  _id: "act2",
  owner: "Bar Baz"
}

And now, we have a collection we call "transfers". Each of our accounts get $100.00 from an outside source:
db.transfers.insert({from:"Scroogey", to:"act1", amount: 100.00, date: new ISODate()})
db.transfers.insert({from:"Scroogey", to:"act2", amount: 100.00 , date: new ISODate()}})

Now, how do we get the balance of our two accounts? Say Foo Bar wants to check his balance. We have to do an aggregation for that:
db.transfers.aggregate([
  { $match:{ $or:[ { from:"act1" },{ to:"act1" } ]}},
  { $sort:{ date:1 }},
  { $group:{
    _id:"act1",
    balance:{
      $sum:{
        $cond:[
          { $eq: [ "$to", "act1" ] },
          "$amount",
          {$multiply:["$amount",-1]}
        ]}
      }
    }
  }
])

Which gives us the correct value when run against transfers:
{ "_id" : "act1", "balance" : 100 }

So far, so good. So how does this relate to atomicity and consistency?

In MongoDB, a write operation is atomic on the level of a single document, even if the operation modifies multiple embedded documents within a single document.

So if we insert a document into the transfers collection, we achieve what we want: atomic transfers from and to accounts. Say Foo Bar wants to send Bar Baz $20.25:
db.transfers.insert({from:"act1",to:"act2", amount: 20.25, date: new ISODate()})

Atomic and consistent. Now, if we run our aggregation again, we get the correct result:
{ "_id" : "act1", "balance" : 79.75 }

If Bar Baz checks his account
db.transfers.aggregate([
  { $match:{ $or:[ { from:"act2" },{ to:"act2" } ]}},
  { $sort:{ date:1 }},
  { $group:{
    _id:"act2",
    balance:{
      $sum:{
        $cond:[
          { $eq: [ "$to", "act2" ] },
          "$amount",
          {$multiply:["$amount",-1]}
        ]}
      }
    }
  }
])

he sees the correct value, too:
{ "_id" : "act2", "balance" : 120.25 }

We have an atomic system to do account balancing without transactions! And still we have a log of all transfers from and to an account which you need anyway. We just use it differently.
There is another way to do it
There is a way to achieve multi-document atomicity from the applications point of view, called Two-Phase-Commits.
Basically, it is an extension of the above. You'd have a transaction collection, in which pending operations are documented. Only if all operations are done successful, the transaction is marked as committed. There are various rollback scenarios. This is definetly more work than doing it like documented above, and should only be considered in case the data model can not be designed for the according use case. That would be the case when dealing with legacy systems, for example, in which you want to add features which would rely on transactions.
Basically you move the transaction logic from the DBMS to the application. This is not necessarily as disadvantage, since control over the various stages of a transaction which is supposed to be committed is much more granular.
Conclusion
As we have seen, there are not transactions. With careful data modeling and a bit of knowledge about the inner workings and tools provided by MongoDB, there is hardly any use case that can not be implemented with MongoDB. It might not be the best tool for any use case, however, and you should choose your persistence system carefully.
If you really need transactions, use the Two-Phase-Commit approach.


How to check other table for value during insert In PostgreSQL is it possible to check another table's data to see if there is data that fits a certain condition during an INSERT statement?

I have attempted to use WHERE NOT EXISTS as I want to not go on with the INSERT if I don't find the data I'm looking for in another table but I get the following error:

Database Model

Query

INSERT INTO car_model(model_name, car_make, date_released)
            VALUES("Classic Car", "Car Brand", "2000-01-01")
WHERE NOT EXISTS (
   SELECT * FROM car_make
   WHERE company_name = car_make AND date_released >= date_incorperated)

Error:

ERROR:  syntax error at or near "WHERE"
LINE 3: WHERE NOT EXISTS (SELECT * FROM car_make WHERE company_name ...
        ^

This would work:

INSERT INTO car_model(model_name, car_make, date_released)
SELECT *
FROM  (SELECT 'Classic Car', 'Car Brand', '2000-01-01'::date)
  AS i(model_name, car_make, date_released)
WHERE  NOT EXISTS (
   SELECT FROM car_make m
   WHERE  m.company_name = i.car_make
   AND    m.date_incorperated <= i.date_released
   );

Or you can still keep the VALUES expression to insert multiple rows at once:

INSERT INTO car_model(model_name, car_make, date_released)
SELECT *
FROM  (
   VALUES
   ('Classic Car', 'Car Brand', '2000-01-01'::date)
    -- more rows?
   ) AS i(model_name, car_make, date_released)
WHERE  NOT EXISTS (
   SELECT FROM car_make m
   WHERE  m.company_name = i.car_make
   AND    m.date_incorperated <= i.date_released
   );

Note how it may now be necessary to add explicit type casts. See:

Casting NULL type when updating multiple rows

You had a couple of issues:

A VALUES expression does not take a WHERE clause. Needs to be a SELECT for the purpose.
Values are enclosed in single quotes (''). Double quotes ("") are for identifiers.
To refer to input values, you need to form a derived table and attach column aliases first - with a subquery (like I did) or a CTE.

Related:

How do I insert a row which contains a foreign key?
Select rows which are not present in other table


Enterprise Manager Database Control on host with an underscore in it's name I know it sounds awkward, but Enterprise Manager is refusing to be set up because of an underscore in the server name:

C:\>emca -deconfig dbcontrol db -repos drop

STARTED EMCA um 30.05.2012 13:21:49
EM-Konfigurationsassistent, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved. Alle Rechte vorbehalten.

...

SCHWERWIEGEND:  Ungültiger Host-Name vm_scotty11.xxnet.de. Host-Name darf keinen Unterstrich ('_') enthalten.
Weitere Einzelheiten finden Sie in der Log-Datei in c:\app\Administrator\cfgtoollogs\emca\PRJ\emca_2012_05_30_13_21_49.log.
Die Konfiguration konnte nicht abgeschlossen werden. Weitere Einzelheiten finden Sie in der Log-Datei in c:\app\Administrator\cfgtoollogs\emca\PRJ\emca_2012_05_30_13_21_49.log.

Has someone worked around this issue or can provide a documentation link which explains why this happens?

I know that there must have been warning messages upon installation of OS and DB but I still need to see it written in the docs in order to convince and make the point that the host name has to be changed.

This is happening because an underscore in a hostname is not a valid character according to the RFC.

  "The Internet standards for protocols mandate that component hostname
  labels may contain only the ASCII letters 'a' through 'z' (in a
  case-insensitive manner), the digits '0' through '9', and the hyphen
  ('-')."


Change Name of Extended Event Session I've just recently started working with Extended Events and am loving it.  However, I ran into a small annoyance and am wondering if there's a way around it.

I had a very simple ExEvent session that was named "DatabaseFooActivity".  Once I got comfortable with that, I expanded it so it was looking at more than just Database "Foo", meaning that the session name is no longer accurate.  Now, when I right-click the session, there's no rename option and when I go into it's properties, the Session Name field is grayed out.

In short, can you change the name of an Extended Event session after it's been created, without dropping and recreating it?

No, there is no way to rename an Extended Events session without dropping and recreating it.  The NAME property is not part of the ALTER EVENT SESSION syntax, nor is it supported by sp_rename.  Simply use SSMS to script and recreate the session easily.  You don't lose anything by doing this.


Is it possible to backup stored routines and functions with Innobackupex? I am running a few restore tests on my Dev and I've noticed that the stored routines are not being restored with the innobackupx. Is that something achievable ? Am I doing something wrong ?

I am only using innodb tables so it will be good to restore the db using only innobackupx.

Thank you.

Stored procedures are stored in database mysql. Innobackupex copies it by default. Make sure database mysql is restored with other databases.


New field based on sum field I'm trying to create a new field based in another (sum field)

SELECT sum(price) AS old_price,
   old_price+100 AS total_price
FROM sales

This returns Unknown column 'old_price' in 'field list'

sum is just a example, my real issue is to use with a select inside this field

What am I doing wrong?

Thanks.

--edit
I've used variables to reuse the alias, updated code:

SELECT @old_price := sum(price) AS old_price,
   @old_price+100 AS total_price
FROM sales

If you think that it is not a good practice, tell me.

Yes, columns in the select-list cannot reference other columns by alias. This is standard SQL.

MySQL does not guarantee the order of evaluation of the select-list from left to right, so it would not be a recommended practice to use variables like you show.

Another workaround is to do computations and create column aliases in a derived table, and then reference them in the outer query. This is standard SQL and is reliable.

SELECT old_price, old_price + 100 AS total_price
FROM (
    SELECT sum(price) AS old_price FROM sales
) AS t;


PostgreSQL:- How to continue execution even INSERT INTO SELECT FROM fails and how to work with refcursor as out param with other output params In this post i am asking 2 questions (with my limited knowledge of PostgreSQL and PLPGSQL)

I have a function inside which there is a query that inserts records to a table using INSERT INTO SELECT FROM. This query is failing the whole function when there is no record returned from the SELECT. Just like SQL Server I don’t want this query to fail, even if there is no record, I want the execution to be continued. What should I do to achieve such functionality.
I have a refcursor to be returned as output however along with it i have few more params (2 or 3) to be returned as output such as bigint or char type. This refcursor would be getting data from a dynamic SQL which is getting formed inside function. So is my function inputs and outputs are in order and whether this will work?

e.g.
CREATE OR REPLACE FUNCTION public.xyz(input1 INT, out1 OUT BIGINT, P_refcursor out REFCURSOR) RETURNS RECORD
AS
$body$
BEGIN

<some code>

/*This step fails the whole function and nothing executes after that*/
INSERT INTO tbl
SELECT * FROM
<joins>
WEHRE <condition>;

<some code>

SELECT aaa INTO out1 FROM <somefunction> ;

/*refcursor to be returned to the calling mechanism*/
open p_refcursor for EXECUTE sql_stmt;

exception
        when others then
          Raise Notice '% - % ', SQLSTATE, SQLERRM;
END;
$body$
Language PLPGSQL VOLATILE;

EDIT-1: Below could be answer to Q2... Answer to Q1 is still pending...
I figured out that there could be different options to handle situation mentioned in Q2.

Write these singular (non-cursor) params into some table and then access from outside the function to know about last execution value
Include these other params as one of the column in REFCURSOR and get their values in the output when you access it
What i did: Removed all the out params, converted the RETURN type of function as SETOF RECORD (my output was dynamic hence couldn't convert it to TABLE) and included the other singular out params (non-cursor) in the RECORD when forming my dynamic query. I was then able to access these out params when I called the function with field list (as my Return Type was SETOF RECORD). The downside of this would be these out param values would be repeated for all rows of the output and you wont have them when dynamic query returns no rows.

Honestly there are so many things wrong with this question, it's pretty hard to know where to start. Your function code is not valid and won't compile due to multiple issues like no variable declaration and almost certainly some record / type mismatches, as well as any return method. Since you don't include the full code, we can't tell if the message your getting back is from where you think it is (I don't think it is, but can't be 100% sure).
It sounds like both things are doable, but you're going to have to clean up the code and provide more info (or smaller test cases) to get real help. The one extra thing I was thinking to share was, if you want to use the refcursor approach, you can; here is an example of one way to do that using a stored procedure (would also work with a function), because these examples are hard to find: https://github.com/xzilla/pagila/commit/52c0ef17609c6d0a3a0e72d6de10ad9101314ffa (there are some other functions in this project as well that might server as good examples. HTH.


Exporting from Oracle enterprise edition to standard edition reaches schemas limit So, I'm trying to export an oracle database(10.2) from EE to SE over a networklink.
It all works fine except that I'm hitting a limit. The database has 1335 schemas and it seems that I can't transfers these in one go. Not with the schemas parameter at least.
Currently I'm doing a full=n with schemas=(the schemas to export) as I'm not really sure what a full=y exclude=schemas:() will transfer. Seeing that I want a clean database.

I tried using cmdline impdp and a par file. But got the same error in both:

When trying 1000 schemas:
ORA-39001: invalid argument value
ORA-39071: Value for SCHEMAS is badly formed.

When trying 250:
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-39097: Data Pump job encountered unexpected error -6502

And when using 25 schemas it works fine.

The syntax is exactly the same, being built dynamicly.
As we can see it appears that the SCHEMAS variable has a length wich oracle can't handle.

Has anyone of you tried the same thing? Would you recommend going the easy way with full=y? Are you confident it doesn't transfer EE history or data.

Update:
An example of EE data imported when using the full=y on SE

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39083: Object type PROCACT_SYSTEM failed to create with error:
ORA-00001: unique constraint (SYS.WRI$_ALERT_THRESHOLD_PK) violated
Failing sql is:
BEGIN
dbms_server_alert.set_threshold(9000,dbms_server_alert.operator_GE,'85',dbms_server_alert.operator_GE,'97',1,1,NULL,dbms_server_alert.object_type_TABLESPACE,'');COMMIT; END;

Which is AWR which is an enterprise feature.

Another is

ORA-29357: object AUTO_TASK_CONSUMER_GROUP already exists
Failing sql is:
BEGIN
dbms_resource_manager.create_consumer_group('AUTO_TASK_CONSUMER_GROUP','System maintenance task consumer group','ROUND-ROBIN');COMMIT; END;

which I believe is the resource manager and in it's turn enterprise feature.

Apparently a full=y is allowed.
Converting An Enterprise Edition Database To Standard Edition (Doc ID 139642.1)

The only way to properly convert from an Enterprise Edition back to a
Standard Edition is through an Export/Import operation. This way you will
get rid of all Enterprise Edition specific Data Dictionary objects, as the
SYS schema objects are not exported. The Standard Edition EXP utility is
preferred to be used to export the data.

So apparently all the EE DD objects are not transfered.
Followed by dropping or excluding while exporting the EE schemas.