configure new linked server from SQL Server 2016 to Oracle 12c I'm having difficulty creating a Linked Server in SQL Server 2016 to talk to a remote Oracle database.

I have installed Oracle Database Client 12c. First I installed InstantClient and then later realizing I needed more functionality installed Runtime. During the setup I accepted C:\Oracle\product\12.1.0\client_1 as the Oracle home directory. For the Oracle Home User, I used the Windows Built-in Account NT AUTHORITY\LOCAL SERVICE.

I used Oracle Net Configuration Assistant to create a tnsnames.ora file with alias = MyAlias and service name = MyServiceName. Net Configuration Assistant was able to test the database connection successfully after I provided credentials for the remote database.

The tnsnames.ora file created by Oracle Net Configuration Assistant was in this folder: C:\Oracle\product\12.1.0\client_1\network\admin

From a Powershell prompt, I ran tnsping MyAlias and got OK (200 msec).

I think (hope!) this means I got the Oracle side correct.

On the Microsoft side, I installed .NET Framework 3.5. Then I downloaded and installed Oracle Data Access Components (ODAC) from here. Specifically I downloaded ODAC122010Xcopy_x64.zip and followed the instructions in the readme file included. The command line options I used during install were:

 .\install.bat all c:\oracle odac true true

The parameters of the above commmand were:

component_name = all (install all Oracle Providers)
oracle_home_path = c:\oracle (should it be the Oracle base or the subfolder?)
oracle_home_name = odac (determines registry key path)
install_dependents = true
machine_wide_configuration = true (this parameter was a guess; I want this to work for all users of this machine)

I then added C:\oracle and C:\oracle\bin to the beginning of the Environment variable PATH. In Powershell this was:

 [Environment]::SetEnvironmentVariable("Path","C:\oracle;C:\oracle\bin;" + $env:Path, [System.EnvironmentVariableTarget]::Machine)

Within SQL Server Management Studio, I can see OraOLEDB.Oracle in the list of Linked Server Providers.

Now trying to create the Linked Server. Here's my config:

For security, I'm trying for now with a fixed security context, using the same credentials that worked when testing the connection with Oracle Net Configuration Assistant.

It doesn't work. Here's the generic error:

  The linked server has been created but failed a connection test. Do
  you want to keep the linked server?

  Cannot initialize the data source object of OLE DB provider
  "OraOLEDB.Oracle" for linked server "MYLINKEDSERVER". OLE DB provider
  "OraOLEDB.Oracle" for linked server "MYLINKEDSERVER" returned message
  "ORA-12154: TNS:could not resolve the connect identifier specified".
  (Microsoft SQL Server, Error: 7303)

What am I missing? What diagnostic tests would be valuable?

Try changing your Data Source and Provider String to MYALIAS.  Looking at the three Oracle Linked Servers I have on my server, that is how I have them setup.


Allocating Filegroups and Storage I have upto 10 databases running on a single SQL Server instance on a single server.

I have read about best practice for storage and concluded the following...

1) Transaction Log on own RAID array (Raid 10) or at least on own physical disk

2) Data files(secondary filegroup) on own disk array (Raid 10 or 5)

3) Indexes on own filegroup (Raid 5)

4) Primary on Raid 1 or 5 (only system dbs)

5) Tempdb on Raid 5 or 10

So each of the above will be on separate physical disks?

Should I then have a separate disk for each secondary filegroup of each database? Or can they reside on the same disk?
 How would this impact on performance?

And similar again with each Primary filegroup - should each one be on its own disk? In this case 10 separate disks for each primary filegroup?

Thanks!

I just tried quickly to find an answer because there is a lot here, but this is a common question. Didn't find quite what I wanted yet so a few thoughts to guide you:

First: It depends is the default answer here.

It really depends on what the performance characteristics are of your system. You should review what your waits and filestats look like and consider where your pain points are and build according to that. There are sizing concerns and performance concerns.

A few thoughts to guide you:

RAID 5 can be fine for data files unless you are doing some heavy writes and performance becomes a concern. So this isn't always good, but a place I feel safe about RAID 5, again your mileage really varies based on your performance needs.
Mirroring or RAID 10 is where I tend to like to go with log files and TempDB.
In some environments, TempDB needs to be isolated onto it's own first because it is a shared resource and used when queries spill over from memory to disk as well.. In some environments, log files need to be isolated and get better performance because of the insert activity.. In some environments, data files need the best IO (heavy read/reporting).
You need to look at Filestats and Waitstats and Perfmon and really see where your pains are.

Using multiple file groups is a great approach/answer for certain workloads. Separating TempDB is usually a best practice though I'd never put TempDB on RAID 5 typically.

And if the environment is pushing no IO? Having no IO waits? I've seen and placed environments all one one big happy set of drives and things have been fine. It really depends on a lot.

I'd spend some time profiling your workload. Look at the PAL tool and run some perfmon scripts on your existing environment. Look at your SQL Server File Statistics. Read about some best practices in SQL Server IO, and make informed decisions and monitor to see how they look as you go.


Oracle 12c SYSTEM user locked out We have a development Oracle 12c database and the SYSTEM user account is locked out for one of the SID's (works fine with another SID). When I try to do a conn SID / as sysdba it keeps connecting me to another SID that's idle. So I cannot even get to the SID that has the locked account.

Any ideas on how to connect to the SID that has the locked SYSTEM account so I can unlock and reset the password?

Thanks.

Ok, figured it out with the comments from my original question. Steps I used to connect to the correct instance and get the account unlocked and password reset.

Connect to the correct SID/instance off the top. Example:

c:\sqlplus SYSTEM/<Password>@<service name> --> this can be found in the tnsnames.ora file
I then verified I was in the correct instance:

SQL>select instance_name, status from v$instance;
From there I was able to unlock the user and reset their password:

SQL>alter user <USERNAME> account UNLOCK;

SQL>alter user <USERNAME> identified by <PASSWORD>;

My problem intially was that I was just trying to connect using sqlplus / as sysdba and it wasn't putting me in the correct instance.

Hopefully this will be helpful to other Oracle devs and rookie DBA's (I'm a rookie in this since we have a dev instance for some client work and it rarely gets touched).


checking the normal form of the current table I have a schema

R1(A,B,C,D)

with the following functional dependencies

A->B
CD->B
A->CD
CD->A

I need to figure out which is the highest normal form in which the following table exists. Here A is the primary key.

2NF- Since B, C and D are fully dependent on A, 2NF holds.

3NF-Since B,C and D are not transitively dependent on A. 3NF holds.

Since on the LHS of functional dependencies, there are only super keys, BCNF holds.

Is my solution fully correct? or am i lacking something? I feel this is not 100% correct. Here CD is also a key attribute so do I not need to check my non key attributes with it?

You are correct, the relation is in 2NF, 3NF, BCNF.

The reason is that the relation has two keys, A and CD.

So the relation is in BCNF (which is a property stronger than 3NF and 2NF) since each determinant of the minimal conver of R1 is a key. Here is one minimal cover:

A → C
A → D
C D → B
C D → A


Does LocalDB support temporary tables? Are SQL Server temporary tables (prefixed with #) supported in LocalDB instances?

I can't answer for all versions, but for SQL Server 2012 up until  SQL Server 2017 I am certain that they are supported

LocalDB has the same programmability features as SQL Server Express.

  SQL Server Express LocalDB, a lightweight version of Express that has
  all of its programmability features, yet runs in user mode and has a
  fast, zero-configuration installation and a short list of
  prerequisites.

Source

And then,  building on the previous point, for the SQL Server express 2012 T-SQL Syntax

  Express supports the same T-SQL language elements you find in any
  edition of SQL Server. Not only can you issue data manipulation
  language queries against the database, but you can also run data
  definition language statements to create such objects as views,
  triggers, cursors and stored procedures

Source

Testing (SQL Server 2017)

USE testdb
GO
CREATE TABLE #temp (id int , value nvarchar(255));

INSERT INTO #temp( id ,value)
SELECT 5, 'bla';

SELECT * FROM #temp;

result

id  value
5   bla

The temporary table also works when changing the db's compatibility mode to 100 (2008).


Omit table name and dot in SELECT query When I perform this query:

SELECT `tablename`.* FROM `something`.`tablename`

I get a table with column names that contain dots:

tablename.c1 | tablename.c2 | tablename.c3
------------------------------------------
     a       |       1      |     2
     b       |       1      |     3

I don't want this, I just want the column names c1, c2 and c3, I can solve this by writing the following query:

SELECT `tablename`.`c1` as `c1`,
       `tablename`.`c2` as `c2`,
       `tablename`.`c3` as `c3` FROM `something`.`tablename`

I however have many columns which makes it a very long query. How can I rename the columns from the first query or how can I get this right from the start?

(p.s. the query I'm using contains multiple table references that is why I specify the table name tablename.*)

You will have to do it the way you proposed in your question.

In cases of many columns, you can get the code generated for you. I find string manipulation in Excel really useful for generating SQL code.


SQLyog and MariaDB engine I am quite new in MariaDB and I use SQLyog Community Edition as the frontend GUI. Whenever I create a new table, on the Engine dropdown, there is no XtraDB option. Is there any additional steps I am missing? As I've read, XtraDB is the InnoDB improved. How can I set my table to have that engine?

Here is my SHOW ENGINES result:

MariaDB [(none)]> SHOW ENGINES;

    +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
    | Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
    +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
    | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO   | NO         |
    | CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
    | PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
    | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)             | NO           | NO   | NO         |
    | MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO   | NO         |
    | MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
    | ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
    | FEDERATED          | YES     | FederatedX pluggable storage engine                                        | YES          | NO   | YES        |
    | InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
    | Aria               | YES     | Crash-safe tables with MyISAM heritage                                     | NO           | NO   | NO         |
    +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+

By default, XtraDB is used as the InnoDB replacement.

If you login to MariaDB and run SHOW ENGINES;, look for the InnoDB.

According to the Documentation for SHOW ENGINES;:

  Note that both MySQL's InnoDB and Percona's XtraDB replacement are labeled as InnoDB. However, if XtraDB is in use, it will be specified in the COMMENT field. See XtraDB and InnoDB. The same applies to FederatedX.

Based on your SHOW ENGINES; output, you are using XtraDB now.

In case you need to revert back to the original InnoDB, see Using InnoDB Instead of XtraDB. It discusses flipping the plugins and restart MariaDB.

As for XtraDB, you can read the Documentation.

I can tell you one thing that I wish MySQL's InnoDB had: MORE INFORMATION_SCHEMA

Check out the 5.5 and 5.6 INFORMATION_SCHEMA extensions for Percona and see how they compare to those in MariaDB.

To see which parts of XtraDB can be tuned and monitored (which InnoDB cannot do), look at this Pictorial Representation of InnoDB (originated by Vadim Tkachenko).


DBCC sqlperf(waitstats) contains signal wait time longer than last time waitstats was cleared I'm trying to troubleshoot some performance problems on our SQL2000 production server.
I ran DBCC SQLPERF(waitstats) and noticed the stats hasn't been cleared in a long time.  So I cleared them and did some refreshing and noticed after only a few minutes, OLEDB reported a signal wait time in the order of hundreds of millions of milliseconds.  How is this possible?

The signal wait time is aggregated across all cores. Any system with more than one core can have total signal waits longer than the last time you clear the wait stats.


SQL Server 2008 query planner failing after index drop & recreate Recently we ran a script to our production DB that would dynamically drop and recreate hundreds of indexes as filtered indexes. While this script had run perfectly in all other previous tests, now after this one the SQL Server query plan cache is behaving oddly.

Execution planner reveals that wrong indexes are being used wrongly that return millions of rows, when only a few should match. When the indexes are correct, the execution plan shows that SQL Server resolves to use index Scan as opposed to index Seek providing much less than optimal results. With tablehints like WITH(INDEX(indexname)) or WITH(FORCESEEK) in the proper places, this can be corrected. INNER LOOP JOIN also fixes some of these.

However, the problem is that even when these new, filtered indexes are dropped and recreated as they were before, the query plan remains the same. Query plan cache has been cleared, DB has been restored to a different environment, statistics have been updated and obviously the indexes have been rebuilt so they're not fragmented.

This is currently a critical issue that nobody has any idea how to fix. While we can force SQL Server to use the correct plans, it's simply not a solution to the multitude of software that would have to be updated with it, and obviously a DB where you need to manually point out how to handle queries is not an option.

So any help would be greatly appreciated.

Edit:
We managed to fix one query by dropping the indexes, recreating them again as filtered, then running an UPDATE STATISTICS tablename WITH FULLSCAN. This fixed some of the problem and two joins were working correctly. After this we had to do a separate alter to a multi-column index that was in no way involved in the original index script, to include one of the columns used in the join. These two changes together enabled the query planner to resolve to use the correct indexes with Seek rather than Scan.

The theory right now is that due to an earlier crash, the DB was apparently restored by deleting the underlying DB and then creating a new one from backup, rather than just using REPLACE as before. This would have somehow disconnected masterdb's metadata such as execution plans, all cache and whatnot from the DB resulting in a new massive database with no existing plans to handle queries. This, grouped with an apparently failed statistics update on the newly created indexes would have produced a scenario where SQL Server had no idea how to resolve the various queries it was being bombarded with.

I'm not convinced however that this will still be enough, since some of the behavior such as having to alter the multi-column index, and the fact that no statistics update was ever necessary in any of the prior testing environments in the past 2 months of testing, seems to let on that there's something else that has gone wrong.

Well, the issue is now resolved:

While it would seem logical to use filtered indexes (NOT NULL), to reduce database size and as so many sources on the web say, increase performance, the reality it seems is something else entirely.

In layman's terms, SQL Server query planner resolves even your basic inner joins without making any assumptions as to the content of the columns. Even though NULL values do not form a join, they must be included in the column index in order for query planner to use it, unless otherwise specified with predicates such as WHERE joinCol_ID IS NOT NULL. Basically, SQL Server does not use filtered indexes for joins at all, unless the queries themselves are modified to account for the filter value. Instead, it will create new statistics on these columns and / or use a clustered index scan or other indexes including the column, whichever it deems most effective. Using filtered indexes on foreign keys is therefore an absolutely horrid idea.

We still have no idea how months worth of testing this in multiple other environments never produced the same results outside of this one, single DB, but this is the way it's supposed to work. Apparently something that as far as we know is not related to cache, statistics or configurations, caused the production DB to behave differently and correctly detect and use the filtered indexes, while all of the testing environments simply used the old indexes (seeing as the indexes were dropped and recreated with the same name, this seems a valid theory even if there is no real proof).

So the lesson of the story: The web is filled with examples of how underused filtered indexes are, how awesome they can be. But this serious downside never popped up except as a nagging thought in the back of my head saying "if these are so great, then why aren't NULL values filtered out of indexes by default, since they only take up space and only serve a purpose in special circumstances"? Well, now I know why. :)


How to copy all firewall rules and apply them on a new server? I often install new SQL Server Servers. Be them production, test or development.

There is one thing I still have not automated and it is time consuming: the firewall settings.

I know how to Configure Windows Firewall to Work with SQL Server but I want to automate it.

I want to be able to copy all settings from a firewall in an already existing server my_good_server_already_running and apply all those settings to my_new_server.

How can I get this done?

well, how to Enable these Windows Firewall rules using powershell? gives me an idea.

From Use PowerShell to List Firewall Rules I got the following script that allows me to list all the firewall rules that I am interested in:

Get-NetFirewallRule | Where { $_.Enabled –eq ‘True’ –and $_.Direction –eq ‘Inbound’ }

Get-NetFirewallRule | Where { $_.Enabled –eq ‘True’ –and $_.Direction –eq ‘Outbound’ }

How do I get these rules and make them a script to apply on my_new_server?

I tried Managing the Windows Firewall with PowerShell but it doesn't say how to make the applying script out of the list either.

I am delighted to say that I have used the link provided by Scott Hodgin in the comments,
and it worked fine, although the order is not the same, and the original server was involved in clustering and the new one is not.....

I recommend this script, for those who need to deal with database server installations, and consequently firewalls.

Powershell scripts to export and import firewall rules

This is a partial picture of the firewall on the original server:

This is a partial picture of the firewall on the new server:


Is Neo4j 2 replication architecture a master-slave or multi-master? In the new docs of Neo4j (23.1. Architecture) it says that:

  When running Neo4j in HA mode there is always a single master and zero
  or more slaves. Compared to other master-slave replication setups
  Neo4j HA can handle write requests on all machines so there is no need
  to redirect those to the master specifically.

And in wikipedia about Multi-master replication

  Multi-master replication is a method of database replication which
  allows data to be stored by a group of computers, and updated by any
  member of the group. All members are responsive to client data
  queries. The multi-master replication system is responsible for
  propagating the data modifications made by each member to the rest of
  the group, and resolving any conflicts that might arise between
  concurrent changes made by different members. Multi-master replication
  can be contrasted with master-slave replication, in which a single
  member of the group is designated as the "master" for a given piece of
  data and is the only node allowed to modify that data item. Other
  members wishing to modify the data item must first contact the master
  node. Allowing only a single master makes it easier to achieve
  consistency among the members of the group, but is less flexible than
  multi-master replication.

Is that means that is a multi-master architecture? What is the difference if not. Thanks

Neo4j is a master-slave cluster.

While you can write to slaves for safety, it will take locks on the master and affect your whole cluster's performance.


Reasons for not Globally enabling Trace Flag 2453 - Cardinality on Table Variables I've been doing some reading on the improvements that Trace Flag 2453 can give on the performance of Table Variables by maintaining statistics and was wondering what would be the reasons you would advise to not turn this on globally.

I understand there will be some overhead of recompiles if the number of rows being used substantially differs - but to me, this would appear similar to any other query, and be preferred behaviour if it is caching better query plans.

Is there other reasons why this would not be preferred behaviour to generate statistics on table variables, similar to temp tables?

Primarily I'm thinking about replacing a few places where we use XML to bring in data sets and use Table-Valued-Parameters instead. We do use temp tables for the most part.

The main reasons not to enable TF 2453 globally are:

You have queries that deliberately used table variables for the one-row guess.
You absolutely must avoid recompilations as much as possible.

In many cases, you will still find that using other solutions (temporary tables, recompile hints) are better overall. Trace flag 2453 benefits a relatively small class of queries for which a table variable is the absolute best choice, but table cardinality is important without needing full statistics.

Somewhat related reading:

Interleaved execution for multi-statement table valued functions
Public Preview of Table Variable Deferred Compilation in Azure SQL Database
What's New in SQL Server 2019: Faster Table Variables (And New Parameter Sniffing Issues)


SELECT describable_id
FROM table
WHERE (property_id, value) IN ((6, 'Red'), (4, '150'))
GROUP BY describable_id
HAVING COUNT(DISTINCT property_id, value) = 2

For strongly 2 pairs

SELECT DISTINCT t1.describable_id
FROM table t1
JOIN table t2 ON t1.describable_id = t2.describable_id
WHERE t1.property_id = 6
  AND t1.value = 'Red'
  AND t1.property_id = 4
  AND t1.value = '150'

If (property_id, value) is unique (by index!) you may remove DISTINCT from both queries.


Tempdb size strategy I've recently started my role as a DBA and my initial task has been to take ownership and redeploy a database provided by a 3rd party to help us conform to GDPR legislation.

As a consequence, I've not really been able to look at much beyond the project itself. I'm now at the stage where my database is ready to go live, although there are a few caveats.

My existing code for some aspects creates large temporary tables (70m+ rows) and I know my dev database has grown over this project.

My question is for this and subsequent projects working with large datasets, in instances of temporary storage, should I use the temp database or create staging tables within the database I migrate to and drop them?

The reason I ask this is my live tempdb currently is less than 1gb in size  whereas dev reached around 30 and should I continue with temporary tables, I would want to grow tempdb in advance to prevent waits on autogrowth.

Based on the additional details provided, I would recommend using staging tables versus temporary tables.

The benefits are:

If there is an error, you can view the data as it was mid process for troubleshooting more easily. You can also more easily control the existence/deletion data (in regards to GDPR) rather than it being in the sometimes nebulous tempdb.
You can simplify the code by utilizing persistent tables and just truncating them after each successful processing.
You won't be thrashing your tempdb and possibly affecting other things relying on the tempdb at the same time.
As Jonathon mentioned, it gives you a more stable tempdb size that isn't inflated just because of a 1x a week process.
The schema more clearly illustrates how the data is used, instead of doing a lot of data processing under the veil of tempdb. This is more of a personal preference, but I think it would be more clear for future maintenance / maintainers.
I can't think of any huge downsides to this approach, unless you have way better disks for tempdb and will suffer by using the drives for a normal database.

Downsides provided by David Browne:

"The downsides are that writing to user databases generates more IO, as Tempdb is allowed to not flush tables and log records to guarantee recoverability. And this is exacerbated if your database is in Full recovery, and further if using an AG"

If you go this route, a common choice is to also segregate the data to a separate staging database altogether, which might help mitigate some of the negatives as this wouldn't have to be in an AG or in Full recovery mode necessarily.


Storing reports with a lot of rows in relational database (Oracle) I am setting up a relational database (Oracle) to store some reports with the following specifications:

Each report has the same columns (say 20 columns of strings or numbers). I expect the columns to be rarely changed but they might change.
Each report has a lot of rows (~200k).
The report generation frequency is like once every few days.

I have this schema in mind where report_id and row_id is the composite primary key:

+---------------+---------------+-----+
|    Column     |     Type      |     |
+---------------+---------------+-----+
| report_id     | uuid (string) | KEY |
| row_id        | number        | KEY |
| data_column_1 | string        |     |
| data_column_2 | number        |     |
| ...           | ...           |     |
+---------------+---------------+-----+

To retrieve a report, I will do something like

SELECT * FROM report_rows WHERE report_id = 'SOME-UUID';

Is this an appropriate design? Will performance suffer when there are more reports? If so, is there a better schema / other database to store data like this?

Thanks.

Some things to consider are;

How is the data populated, in one operation or over a period of time ?
How you access the data, e.g. how does a user know that report_id 32645 is the one they want ?
Will you need the data in a different order ?
Will one reports data ever be correlated with another reports data ?
Will data for a report_id ever need to be removed ? If so how do you identify it ?
If you use generic column names be prepared to confuse some people.

This sort of solution CAN work but like any solution it has some limitations (some of which can be seen from the above questions).
Without knowing what sort of reports are stored its very hard to guess but in my experience reports tend to change quite frequently based on a customers needs.

They will see some data and that will lead them on the path to refining and expanding what they want to see - and that's a good thing.

Ideally you store the data in a way that reflects how it sits in the organisation (as you would with any modelling) and the reporting solution sits above that pulling out the information in whatever way it wants.
This is what a data warehouse/reporting database is designed for.


How can I connect Microsoft SQL Server to MySQL I have a SQL Server running on a local server that is connected to a POS (Point of Sale) system.
I want to create an online application to enable online sales of the products in the POS system.  I'm using MySQL for this application's database.  I would like to connect the MySQL instance to the SQL Server instance to avoid the need to enter duplicate data into each system.  Ideally, changes made to the SQL Server database should be automatically reflected in the MySQL database.
Is there a way to do that with a Linked Server in SQL Server?  Or should I use some other method?  There are around 10,000 products in the SQL Server instance, and we sell around 5,000 each day.

Yes, Linked server and OPENQUERY is the right approach.
However, 5k of sales per day is already a good website. If you are already on MSSQL why don't you use MSSQL for your website? Because you are using WordPress? There are solutions for that:

Project Nami: It has a few years now so it's quite stable and keeps baing developed. You can also deploy it to Azure quite simply. It might be a good alternative if you are already on MSSQL and if you want to stay there.
Ziper: I've used it in the past, try to give it a go.
Skyvia: It says it's free up to 5k per month so it really looks like your scenario. You can use it for free now and pay later if you have more than 5k of records per day.

Or if MSSQL and MySQL is what you want and you are good in usingg Microsoft technologies you can use SSIS to update data from MySQL to MSSQL and vice versa.


Add a mongodb read only replica for reporting purpose I have a MongoDB DB X sharded in 2 replica set. Now I want to add a replica (which will be only read operations) to reduce load on mongodb for reporting purpose.
How is possible to achieve this as my collections are sharded to 2 different replica sets?

Sharding a Mongo DB should not have any impact on your readable secondaries.  Each shard will have a replica set just as if it wasn't sharded.  The 'read only' methods to access those would be the same way.

https://docs.mongodb.com/manual/core/read-preference-mechanics/
From the link:

Read Preference and Shards

For sharded clusters that have replica set shards, mongos applies the read preference when reading from the shards. Server selection is governed by the read preference and replication.localPingThresholdMs settings.

You're probably going to look for the 'secondary preferred' option:
https://docs.mongodb.com/manual/reference/read-preference/#secondaryPreferred

"In most situations, operations read from secondary members, but in situations where the set consists of a single primary (and no other members), the read operation will use the set’s primary.
When the read preference includes a tag set, the client attempts to find a secondary member that matches the specified tag set and directs reads to a random secondary from among the nearest group. If no secondaries have matching tags, the client ignores tags and reads from the primary.
Read operations using the secondaryPreferred mode may return stale data."


Measuring MongoDB I/O and CPU performance I am new to MongoDB and I'm experiencing this NoSQL db through a series of very simple queries, with an Oracle exported data set.

Aside measuring the execution time with primitives like db.system.profile.find(), I would like to measure I/O performance and system calls. Is there a simple, relevant, way to do this? What would be the best approach?

Also, I have noticed that the size of the collections are way much bigger than in Oracle. For example, an Oracle table (average row length x number of rows) translates to a 5x time bigger collection in MongoDB. Why? Are there any reasons for this?

Thanks for your insights.

Cheers

There are several tools that measure system performance like newrelic,MMS,cacti, ganglia...

Regarding the size you need to share a sample record from Oracle and a sample document from Mongo.

The obvious reasons are:

long field names: A collection in Mongo with N documents stores the field names N times. If the field names are too long you are wasting space.
powerof2 allocation strategy (only for MMAP engine): Is now the default record allocation strategy for MMAPv1. With the power of 2 sizes allocation strategy, each record has a size in bytes that is a power of 2 (e.g. 32, 64, 128, 256, 512 ... 2MB). If your document size is 33 then mongo will allocate 64 and you are wasting half your storage.
Compression (only for MMAP engine): Oracle is using compression while MMAP engine not


Finding out who changed the owner of a database when performing a restore A coworker who worked on restoring a backup changed the database owner from his name to 'sa' (rightly so) last night. While I had seen the his name in a SQL Server Management Studio window, I did not take a screenshot. He is now claiming that he did not restore that database.

I am an admin on the server and sysadmin on the database server. Is there a way to check who changed the owner of the database?

I'm not aware of a way to find out who changed a db owner without some additional logging in place such as Auditing, but you can definitely tell who restored a database by checking the restorehistory table in the msdb database:

SELECT  *
FROM    msdb.dbo.restorehistory

If your coworker restored the database using impersonation they may be telling the truth as the database would be restored by the account being impersonated and this account would end up as the database owner.  The impersonated user would show up as the user_name in the restorehistory table, so it'd be the first place I'd recommend you look.

Maybe someone else has a trick up their sleeve, though.


Rebooting a MySQL Replication Slave For the first time since it was set up I need to reboot a read-only MySQL replication slave.

I found this article about downing a slave for maintenance (albeit he's just describing stopping the mysql daemon):

  How to Safetly[sic] Restart Your MySQL Slave Server

In summary the procedure is:

In mysql client:

STOP SLAVE;
FLUSH TABLES;

From OS:

/etc/init.d/mysql stop

I would reboot at this point and then after the system has booted:

In mysql client (the mysql daemon is configured to start on boot):

START SLAVE;

Does this look about right? Is there anything else I should be doing?

This looks right. The slave will pick up where it left off when it starts back up.

I will note that unless you supply the --skip-slave-start option, the slave should start automatically.


How to catch all the errors happening during the execution of an SQL file containing multiple INSERT INTO statements to insert multiple rows? I've got a sql file TW_popul.sql containing multiple INSERT statement:

INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID)
VALUES (32353357, 8.67, 0, 'com.transferwise.fx.payment.fee.RequestFee', 29639547);

INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID)
VALUES (32358776, 1.84, 0, 'com.transferwise.fx.payment.fee.RequestFee', 29643922);

INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID)
VALUES (32374786, 11.26, 0, 'com.transferwise.fx.payment.fee.RequestFee', 29656743);

INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID)
VALUES (32374911, 1.49, 0, 'com.transferwise.fx.payment.fee.RequestFee', 29656849);

Some of those insert statements a breaking constraint rules and throwing errors(ex. "ERROR at line 1: ORA-00001: unique constraint (TW.BT_BANK_TRANSACTION_ID_PK) violated" ).

1 row created.

1 row created.

1 row created.

INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID)
*
ERROR at line 1:
ORA-00001: unique constraint (TW.CF_FEE_ID_PK) violated

1 row created.

1 row created.

How do I collect all the rows generating an error (in an Error_table for example)?

I've read about TRY... CATCH but i'm not sure it applies to catching multiple errors.

Maybe a better approach would be to verify if the record already exists and would violate the constraint, and only then try to insert it. You would have to script a bit around this file.

Another possibility is to insert everything to a temp table first, and then do the insert to table conversion_fee in batch, selectively with a WHERE/IN/EXISTS clause, rather than on a row by row basis.
The temp table should simply be a copy of conversion_fee, without data and the constraints.

Then it should not be too difficult to idenfity the duplicate rows, for example by doing a JOIN between conversion_fee and the temp table.

With a text editor, you can easily replace the table name in your source file to something else (the name of a temp table). Import your file to that temp table, and then compare with conversion_fee.

I can't provide full code right now, since the table structure is not known, nor is the constraint. But this is an idea.


Change all datatypes in a table I'm migrating a database and i need to change the "container", i don't exactly how to name it. I give an example to keep it clear :

old base:

VARCHAR2(20 BYTE)

new base:

VARCHAR2(20 CHAR)

I can do it individually with alter...modify query but it takes so much time to prepare the queries cause the tables have a hundred or more columns. Is there any scripting way or a query that changes all the byte containers with char ?

You can use a bit of dynamic SQL.  Assuming that you want to modify every VARCHAR2 column in one table

DECLARE
  l_sql_stmt varchar2(1000);
BEGIN
  FOR c IN (SELECT *
              FROM user_tab_columns
             WHERE data_type = 'VARCHAR2'
               AND table_name = <<table name>>
               AND char_used = 'B')
  LOOP
    l_sql_stmt :=
       'ALTER TABLE <<table name>> MODIFY( ' ||
          c.column_name || ' VARCHAR2( ' || c.char_length || ' CHAR) )';
    EXECUTE IMMEDIATE l_sql_stmt;
  END LOOP;
END;


Selecting distinct values by column value I have the following table:

+-----------------------------+-----------+
| keyword                     | data_type |
+-----------------------------+-----------+
| red                         | ctr       |
| blue                        | ctr       |
| purple                      | ctr       |
| orange                      | ctr       |
| red                         | var       |
| blue                        | var       |
| black                       | var       |
| yellow                      | var       |
+-----------------------------+-----------+

I'd like to be able to select only those which are distinct by data_type.

For example:
If I checked by data_type: var, 'black' and 'yellow' would be returned.

If I checked by data_type: ctr, 'purple' and 'orange' would be returned.

I'd normally provide an example query of what I've done, thus far, but I don't know where to start with this one - help is much appreciated.

Pretty straight forward:

SELECT * FROM table
WHERE data_type = 'var'
AND keyword NOT IN (SELECT keyword FROM table WHERE data_type <> 'var');


Capture SQL Server queries without third-party tooling and without using deprecated features? Is it possible to capture all queries sent to an MS SQL Server, without third-party tooling and without using deprecated features?

I'm looking for something similar to the "general query log" in MySQL.

Here's an example using a third-party tool:

https://blog.devart.com/capturing-sql-server-trace-data.html

Here's an alternative using deprecated features:

https://docs.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler?view=sql-server-2017

Is there a non-deprecated, native solution?

You could use Extended Events to capture this data. However, depending on how much traffic your server gets this could become a lot of data very quickly and could result in performance issues.

I would look at limiting the events that you track to something like sqlserver.rpc_completed or sqlserver.sql_statement_completed, these only capture what was completed. Erin Stellato wrote a great piece on useing XEvent Profiler to capture queries in SQL Server.

From SSMS, you would go to Management > Extended Events > Session and either use the New Session Wizard or New Session to start building your session to track data. The Microsoft Docs go into a lot of detail on how to set this up.

Here is an example of something I implemented recently to capture queries on a specific server, you can add filters to remove queries that you don't want to see - like some server names or application names that execute queries as well:

CREATE EVENT SESSION [Track Queries] ON SERVER
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)
    WHERE (NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_hostname],N'%name%')
            AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[client_hostname],N'name')
            AND NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_app_name],N'%name%')
            AND [sqlserver].[server_principal_name]<>N'<username>')),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)
    WHERE (NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_hostname],N'%<name>%')
            AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[client_hostname],N'name')
            AND NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_app_name],N'%name%')
            AND [sqlserver].[server_principal_name]<>N'<username>'))
ADD TARGET package0.event_file(SET filename=N'D:\XE\TrackQueries.xel',max_file_size=(5120))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

This captures the details of what has been executed and puts it into a file for easy querying and analysis.


Mysql - optimal indexing for query using IN operator I have this query:

SELECT *
FROM table1
WHERE foo IN (1,2,3,4) AND bar = 123;

I have 3 indices:

ALTER TABLE table1 ADD INDEX IDX_foo (foo);

ALTER TABLE table1 ADD INDEX IDX_bar (bar);

ALTER TABLE table1 ADD INDEX IDX_foo_bar (foo, bar);

When I try EXPLAIN with the query, IDX_bar is chosen which I don't understand why. So my questions are:

Why is IDX_bar chosen but not IDX_foo or IDX_foo_bar?
Does MySQL choose index differently depend on the data at the time?
Should I only have IDX_bar and remove the other two?

The best index in case above seems to be

ALTER TABLE table1 ADD INDEX IDX_bar_foo (bar,foo);

  Why is IDX_bar chosen but not IDX_foo or IDX_foo_bar?

What index will be used and will some index be used at all, is dependent by table statistic (and query text, of course).

Maybe in Your case the index IDX_bar is more selective than another 2 indexes.

  Does MySQL choose index differently depend on the data at the time?

That's right. Change data changes statistic and so index choose can change too.

  Should I only have IDX_bar and remove the other two?

For optimizing the query You show - yes (if data stat will not change radically). Or remove all 3 indexes and create index shown above.


MDW performance_counter_instances table running out of identity values My MDW database's performance_counter_instances table has run out of identity values.

Has anyone ever encountered this?

Is it safe to change the data type of the identity field performance_counter_id from INT to BIGINT without breaking anything?

Bonus Question - What is the impact of this field running out of identity values?

I can't find anything useful about this issue online.

I have converted the data type to BIGINT using this script and now all of the collection_set_2_upload jobs are failing with the following error message:

  LKU - Lookup into performance_counter_instances to obtain performance_counter_id for all counter paths that get inserted.Outputs[Lookup Match Output].Columns[performance_counter_id] and reference column named "performance_counter_id" have incompatible data types.

Please let me know if this is something that can be fixed or if I will have to just eventually recreate the MDW database again.  This happens about once every 6 months in my environment.  For now I've reseeded the identity to -2147483647 to buy some time.

DBCC CHECKIDENT ('snapshots.performance_counter_instances', RESEED, -2147483647)

When you exhaust the upper bound of INT you will receive, for every new insert:

Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.

Outside of the MDW use case

Converting to BIGINT is far safer (IMHO) than what some people do - go back to 0 and fill in the gaps, or go to -2 billion and just delay dealing with it until you use the same number of values one more time. If 2 billion rows isn't enough, neither is 4 billion. If you use compression changing the data type for values < 2 billion will actually save you some space.

However you will come across issues if this is part of a primary key constraint, referenced by foreign keys, has other constraints, etc. You'll need to perform some extra work in addition to just changing the data type.

For MDW specifically

The problem with the solution above, for MDW specifically, is that the data collector package (SSIS Packages/Data Collector/PerfCountersUpload) includes some logic that relies on the underlying data type to be INT. So, the collector jobs fail because of this mismatch.

This should buy you some time:

DBCC CHECKIDENT('snapshots.performance_counter_instances', RESEED, -2147483648);

And between now and when you start approaching 0 again (you should set up some kind of monitoring to check the max value and send some kind of alert when you get close), go in and clean up and make sure there are no positive values.

Then, when you start approaching 2 billion again, clean out all the negative values, and reseed.

Is this annoying? Absolutely. I think this might be one of several reasons why you don't see a very large MDW adoption.


How to Get one Single statement of two table value in SQL I wanted to retrieve two table values in one single statement such as,
(Example Bank statements..)
Table 1: Invoice
SELECT ClientNo, InvoiceDate, InvoiceNo, TotalDue
FROM Invoice_TBL
WHERE ClientNo = 1
ORDER BY InvoiceDate ASC

Table 2: Payment
SELECT ClientNo, PaymentDate, TotalAmountPaid
FROM Payment_TBL
WHERE ClientNo = 1
ORDER BY PaymentDate ASC

The Question is how can i merge two table values and retrieve data from client wise date ascending order
I want the result like bellow screenshot

i'm new to SQL database. Thank you

It's not exactly the wanted output but pretty close:

Query for versions 2012+:

; WITH invoices_payments AS
  ( SELECT StatementDate = InvoiceDate, InvoiceNo, TotalDue, TotalPaid = 0.0,
           ID = InvoiceID, Type = 0
    FROM Invoice_TBL
    WHERE ClientNo = 1

    UNION ALL

    SELECT PaymentDate, '', 0.0, TotalAmountPaid,
           ID = PaymentID, Type = 1
    FROM Payment_TBL
    WHERE ClientNo = 1
  )
SELECT StatementDate, InvoiceNo, TotalDue, TotalPaid,
       TotalBalance = SUM(TotalDue - TotalPaid)
                        OVER (ORDER BY StatementDate, Type, ID
                              ROWS UNBOUNDED PRECEDING)
FROM invoices_payments
ORDER BY StatementDate, Type, ID ;

Test at SQLfiddle. I've assumed that you have a primary key (used InvoiceID and PaymentID) in the tables and I used it to make the ordering 100% determinate (so where you have more than one invoices or payments in the same day, you always get the same order in the results.)

For versions before 2012, there are various ways, none as efficient as the OVER (ORDER BY ... ROWS ...) of 2012. You can check this blog post by Aaron Bertrand about various approaches:
Best approaches for running totals – updated for SQL Server 2012. The more efficient (for 2008R2 versions and older) are - as the blog post tests show - the cursor and the CLR approaches.

Following is one way using a self-join:

; WITH invoices_payments AS
  ( SELECT StatementDate = InvoiceDate, InvoiceNo, TotalDue, TotalPaid = 0.0,
           ID = InvoiceID, Type = 0
    FROM Invoice_TBL
    WHERE ClientNo = 1

    UNION ALL

    SELECT PaymentDate, '', 0.0, TotalAmountPaid,
           ID = PaymentID, Type = 1
    FROM Payment_TBL
    WHERE ClientNo = 1
  )
SELECT a.StatementDate, a.InvoiceNo, a.TotalDue, a.TotalPaid,
       TotalBalance = SUM(b.TotalDue - b.TotalPaid)
FROM invoices_payments AS a
  JOIN invoices_payments AS b
    ON b.StatementDate < a.StatementDate
    OR b.StatementDate = a.StatementDate AND b.Type < a.Type
    OR b.StatementDate = a.StatementDate AND b.Type = a.Type AND b.ID <= a.ID
GROUP BY a.StatementDate, a.Type, a.ID, a.InvoiceNo, a.TotalDue, a.TotalPaid
ORDER BY a.StatementDate, a.Type, a.ID ;


How to Store Text Reports in a Database I have dozens of text reports that come in every day and I need to store them. I cannot just zip them and put them on the hard drive because the users aren't sophisticated enough to figure that out. I need to be able to associate some data with the report so they can look up report ABC for any given date. These files range in size from 11K to 1GB. Mostly they are less than 3 MB and many are less than 1MB.

What is the best way to store these? The options I am looking at are using Filestream or storing it in a varchar(max) field or zipping it and storing it in a varchar(binary) then unzipping it to display. I am open to any other options as well. Space efficiency is a high priority.

Thank you for reading this. I would truly appreciate any help or advice.

** The other post is very interesting and I learned a lot from it - thank you for referencing it.  This is different from the other post in that my files are plain text files -- they are not binary files.

Okay, I think that I'm beginning to understand the nuance of your question.
Given that space efficiency is a high priority, I think that you can strike the varchar(max) option from your list. Since your minimum file size is 11K, nothing is going to fit on a single page. All the files will be stored as BLOBs. SQL Server won't compress that (aside from compressing the backup), so you're not going to save any space with that approach.
Next, I'm going to make the assumption that processing time can be traded for space efficiency. In that case, I would recommend that you compress the files yourself before storing them and decompress before displaying them. This goes for any solution you end up going with. You should end up with better compression than letting the file system handle it (which applies if you use a Filestream).
So I think that your question boils down to Filestream (and possibly FileTable) or VARBINARY(MAX). The standard answer provided by Microsoft is:

When to Use FILESTREAM
In SQL Server, BLOBs can be standard
varbinary(max) data that stores the data in tables, or FILESTREAM
varbinary(max) objects that store the data in the file system. The
size and use of the data determines whether you should use database
storage or file system storage. If the following conditions are true,
you should consider using FILESTREAM:

Objects that are being stored are, on average, larger than 1 MB.
Fast read access is important.
You are developing applications that use a middle tier for application logic.

For smaller objects, storing varbinary(max) BLOBs in the
database often provides better streaming performance.

I think that you will need to reassess your figures using compressed file sizes, and then make a decision based off of that.


Azure TempDB and Log files I'm setting up an Azure D11 SQL Server to be used for a data warehouse. I can only attach 4 data disks and have done so, now I need to determine how to configure them and split the DB files. I'm trying to adapt MS guidance to my specific situation.

Traditional Design:

OS Disk/Azure Temp Disk
1 data disk - TempDB
1 data disk - Log
2 data disk - Data

I'm expecting their guidelines are for general/potentially OLTP servers. I have a data warehouse in simple recovery mode with a single nightly ETL process. I'm not particularly fluent in log file management, but my inclination is the log and TempDB can go on a single data disk, leaving three disks available to stripe and maximize IO for the nightly ETL and, more importantly, day time reporting.

Is it reasonable to put the log and tempdb on a single disk and stripe three for the data or does the log file really need to be on a separate disk?

Azure IaaS is a different animal than traditional on-premise servers (virtual with a SAN or purely physical).  You will get at best 500 IOPS per drive attached.

General recommendation is to use storage pools to stripe multiple drives to increase throughput.

With only four drives available on a D series, I would do the following.

Put TempDB on the D:\, it's an SSD.  Don't put anything else there, make sure that a startup script will check that drive for any folders that are necessary and fix permissions if required.
Stripe the four disks you have into a single driver and put both your data and log files on there.

If you had 8 disks available to attach then I would recommend doing two drives of four disks each and putting log on one and data on the other.


Share centralized queries for multiple databases OK, here's a challenge for you guys.

I am tasked with setting up reporting for two companies being run as a single company.  For reasons I won't get into, they are implicitly split but (for our purposes) are run identically.  They each have two software implementations for which I have access to the databases.

What is being done currently is we have a suite of queries that output to reports in various formats, and the logic is replicated between the two companies, making for 4 sets of queries.

This graphic represents the DB summary, and for each of them there are reports.  We are using MS SQL Server.

We may add additional software in the future, turning 4 suites of queries into 6, 8, etc.  I need to ensure that the query logic has no variation between the two companies in order to minimize the risk of discrepancy and for ease of development.

So here's the question:

Is it possible to share queries somehow between companies without using Dynamic SQL?

Some other ideas:

Use readily available tools to compare two databases after a change is deployed (you can automate this perhaps, depending on your existing deployment process), and synchronize them
Automate the deployment of changes such that changes to procedures always occur against both databases (again this depends on what your current deployment process entails)
Implement dynamic SQL carefully such that data is pulled from the right database depending on the user connecting.
Use a central database with views or synonyms under different schemas, each set pointing to the two different databases. Construct the calls to procedures dynamically, or leave off the schema prefix and set the default schema per user (you can double up on the security by granting database A's user access to B's schema and vice versa).


Iterating through a record set and selecting certain records I am tasked with automating our item return process at work. Our policy for returns is to take the average price of the most recently purchased items desired to be returned.

We are effectively calculating a weighted average unit price of the most recently purchased items, excluding items from the average price once we have hit the desired return quantity.

I currently have a table in Microsoft SQL Server that contains the purchase history of all items ($, units, and purchase date) that have been asked to be returned, and the desired quantity to be returned.

I'll add a specific example; 18 units of Item number 138799528 are to be returned (NOTE that the table is already ordered by ItemNumber, CreatedDateTime descending). In order to determine the return price, we need the query to start with the most recently purchased item (line 137) and select records until the sum of the POQuantity is equal to or greater than the desired return quantity. In this case, the proper item selection would be rows 137,138 and 139, while excluding the rest of the purchases of Item 138799528. The query continues until it has iterated through all items in the record set.

I am attempting to iterate over this table and select the appropriate records into a another table with which to calculate the average. I have attempted using a cursor but my DBA has insisted to avoid cursors at all costs. Below is the cursor logic:

    DECLARE ReturnCursor CURSOR

        FOR SELECT
                T.ItemNumber
                ,-(T.ReturnQuantity)
                ,T.POQuantity
                ,T.PurchasePrice

            FROM #tableforcalc T
            ORDER BY T.ItemNumber, T.CreatedDateTime DESC           --Added order by so following logic works when looping through records

    OPEN ReturnCursor

        SET @Counter=0
        SET @Item2=''           --Needed to set Item2 = ''

        FETCH NEXT FROM ReturnCursor
            INTO @Item, @ReturnQty, @POQty, @PurchPrice

    WHILE @@FETCH_STATUS=0
    IF @Item2!=@Item
            BEGIN

                SET @Counter+=@POQty ;
                SET @Item2=@Item;

                SELECT

                 @Item AS ID
                ,@POQty AS qty
                ,@PurchPrice AS price
                ,@ReturnQty AS RQ

                --INTO #Temp1               --  Put selected variables into a temp table

            END

        ELSE                --this else condition is essentially  IF @Item2=@Item

            BEGIN

                IF (@Counter<@ReturnQty AND @Counter!=0)

                    BEGIN

                        SET @Counter+= @POQty;
                        SELECT
                            @Item AS ID
                           ,@POQty AS qty
                           ,@PurchPrice AS price
                           ,@ReturnQty AS RQ

                        --INTO #Temp2
                    END

                ELSE

                    BEGIN

                        SET @Counter=0;

                    END

I was wondering if there is a way to solve this problem using set operations in SQL? I am open to all suggestions.

As I understand it you want to find records where running total of POQuantity is less than the ReturnQuantity values.

Windowing functions are ideal to help with this problem!  See these resources for further info:

https://www.brentozar.com/sql-syntax-examples/window-function-examples-sql-server/
https://www.red-gate.com/simple-talk/sql/learn-sql-server/window-functions-in-sql-server/
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql

Ive mocked up a quick example from your data to demonstrate this:

DECLARE @Data TABLE(
    ItemNumber            INT
    ,POQuantity            DECIMAL(20, 2)
    ,PurchasePrice        DECIMAL(20, 2)
    ,CreatedDateTime    DATETIME2(3)
    ,ReturnQuantity        DECIMAL(20, 2)
)

INSERT INTO @Data
VALUES
    (138799528, 4, 518.40, '2017-11-02 09:43:18', -18)
    ,(138799528, 8, 518.40, '2017-06-06 15:15:00', -18)
    ,(138799528, 12, 480.00, '2017-01-26 15:52:33', -18)
    ,(138799528, 8, 450.00, '2016-11-14 11:36:42', -18)
    ,(100, 3, 100.00, '2017-11-02 09:43:18', -4)
    ,(100, 7, 150.00, '2017-10-02 19:30:12', -4)
    ;

WITH CalculateRunningTotals AS (
    SELECT
        ItemNumber
        ,POQuantity
        ,PurchasePrice
        ,CreatedDateTime
        ,ABS(ReturnQuantity)    AS ReturnQuantity    /* Make it a positive number for easier calculations later */
        ,SUM(POQuantity) OVER (PARTITION BY ItemNumber ORDER BY CreatedDateTime DESC)    AS RunningTotalQuantity
    FROM
        @Data
), FindApplicableData AS (
    /* We need to consider the previous running total to make sure
       we get "at least" the total POQuantity.  Otherwise we miss the
       last row needed.
    */
    SELECT
        *

        ,CASE
            WHEN RunningTotalQuantity <= ReturnQuantity THEN POQuantity
            ELSE ReturnQuantity - (RunningTotalQuantity - POQuantity)
        END AS POQuantityForAverages    /* Calculate how many "POQuantity" of this row are needed to make up the ReturnQuantity */
        ,RunningTotalQuantity - POQuantity AS PreviousRunningTotalQuantity

    FROM
        CalculateRunningTotals
    WHERE
        ABS(ReturnQuantity) >= (RunningTotalQuantity - POQuantity)    /* Compare to previous running total - remove this to see why you need it ;-) */
)
SELECT
    ItemNumber
    /* Calculate the average price - its not a simple AVG as we need to include the quantities sold at various prices */
    ,SUM(POQuantityForAverages)                                                                            AS TotalQuantities
    ,CAST(SUM(POQuantityForAverages * PurchasePrice)    AS DECIMAL(20, 2))                                 AS TotalPrice
    ,CAST(SUM(POQuantityForAverages * PurchasePrice) / SUM(POQuantityForAverages)    AS DECIMAL(20, 2))    AS AvgPrice
FROM
    FindApplicableData
GROUP BY
    ItemNumber

As you can see, this no longer needs a cursor.  This would allow you to create this as a view to make your subsequent querying much easier.

Hope this helps you.


Adding warning with sp_add_alert doesn't work with "Greater Than" and SQL Server 2014 We upgraded our SQL Server from 2008 R2 to 2014.

I want to add a custom warning.

USE [msdb]
GO

EXEC msdb.dbo.sp_add_alert @name = N'MAN more than 1000',
                           @enabled = 1,
                           @delay_between_responses = 0,
                           @include_event_description_in = 1,
                           @performance_condition = N'Replication Dist.|Dist:Delivered     Cmds/sec|MyFancyReplication|>|1000',
                           @job_id = N'00000000-0000-0000-0000-000000000000'
GO

This has worked with SQL Server 2008 R2.

When I want to add this Warning, but all I get is this error (it's translated, because my original is German)

  A performance condition must be formatted as:
  ‘object_name|counter_name|instance_name|comparator( or < or =)|numeric value'.

Did you recognize the missing > in the error message?

Why can't I add my custom warning?

Best Regards

Björn

after research, I had found the answer. The Problem is the @performance_condition, my example will not work. But if I add a "SQLServer:" at the beginning of the Condition, like this:

N'SQLServer:Replication Dist.|Dist:Delivered Cmds/sec|MyFancyReplication|>|1000'

This is the Key to add custom warnings to Sql Server 2014. I believe this is a bug with the new SqlServer2k14.

Regards
Björn


First you do something like this,

SELECT id, jsonb_agg(jsonb_build_array(lat,lon)) AS j
FROM foo
GROUP BY id;

That aggregates the values into an JSON Array. You get

 id |          j
----+----------------------
  2 | [[56, 67], [58, 64]]
  1 | [[34, 45], [45, 56]]

From there you need to build an JSON Object..

SELECT jsonb_object_agg(id,j)
FROM (
  SELECT id, jsonb_agg(jsonb_build_array(lat,lon)) AS j
  FROM foo
  GROUP BY id
) AS t;


How to Properly shutdown and disassemble SQL Database Replication While testing SQL Server Replication we setup Replication on our ERP Test DB from SQL Server Enterprise to SQL Server Standard.

We have since got replication up-and-running on our Live database and we no longer need replication on the Test database...

We would like to PROPERLY remove the replication to free up resources and delete the Test Replicated DB.

I  have read online about issues with not properly 'disassembling' or shutting down Replication but I have not been able to find a step by step on how to do it.

I just have one test DB that is replicating between these servers that I need to shutdown and remove completely.

Specifics:

  SQL 2012, 1 DB, Transactional Replication.

To properly clean up replication, follow below steps :

Open SSMS --> Local Publications --> Expand Publication -->

Use sp_removedbreplication to remove replication bit set on database.

Another Method is script our the DROP or disable the components by right click on publication and then run that script using SSMS.


Object Reference Not Set ... Debugging Stored Procedure in Visual Studio I'm working on updating a stored procedure that is called to update a user. This stored procedure needs to also create a description of what was updated for auditing purposes. However when debugging in Visual Studio I get!

All of my input values are valid, I leave none null, and my update_* procedures without the action_taken value are working. I'm seemingly writing something incorrectly, but I haven't figured out what.

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_user`(
in requestor_id BIGINT(20),
in user_id BIGINT(20),
in department_id BIGINT(20),
in location_id BIGINT(20),
in shift_id BIGINT(20),
in first_name VARCHAR(45),
in last_name VARCHAR(45),
in email VARCHAR(45),
in phone VARCHAR(45),
in security_stamp LONGTEXT
)
BEGIN
    DECLARE queried_username VARCHAR(45);
    DECLARE action_taken VARCHAR(255);
    SET action_taken = '';

    SELECT
        users.username
    INTO
        queried_username
    FROM users
    WHERE users.id=user_id
    LIMIT 1;

    IF (queried_username IS NULL) THEN
    BEGIN
    END;

IF (department_id != users.department_id) THEN
    SET action_taken = CONCAT(action_taken, '[Department Updated]');
END IF;
IF (location_id != users.location_id) THEN
    SET action_taken = CONCAT(action_taken, '[Location Updated]');
END IF;
IF (shift_id != users.shift_id) THEN
    SET action_taken = CONCAT(action_taken, '[Shift Updated]');
END IF;
IF (first_name != users.first_name) THEN
    SET action_taken = CONCAT(action_taken, '[First Name Updated]');
END IF;
IF (last_name != users.last_name) THEN
    SET action_taken = CONCAT(action_taken, '[Last Name Updated]');
END IF;
IF (email != users.email) THEN
    SET action_taken = CONCAT(action_taken, '[Email Updated]');
END IF;
IF (phone != users.phone) THEN
    SET action_taken = CONCAT(action_taken, '[Phone Updated]');
END IF;

    BEGIN
        UPDATE users SET
            users.department_id=department_id,
            users.location_id=location_id,
            users.shift_id=shift_id,
            users.first_name=first_name,
            users.last_name=last_name,
            users.email=email,
            users.phone=phone,
            users.modified=NOW(),
            users.security_stamp=security_stamp
        WHERE users.id=user_id;

        CALL update_audit_log(
            requestor_id,
            user_id,
            null,
            department_id,
            location_id,
            shift_id,
            null,
            "User Administration",
            CONCAT(
                'Modified user (id="',
                user_id,
                '", username="',
                queried_username,
                '") | Action= ',
                action_taken
            )
        );
    END;
    END IF;
END

Any help is very much appreciated!

Do you have DELIMITER statements around the CREATE...?

Does VS have any way to debug Stored Routines?  (I suspect not.)

The failure to do it this way could cause trouble later:

START TRANSACTION;
SELECT ... from users ... FOR UPDATE;
various SETs
UPDATE ...;
COMMIT;

Another consideration...  An "audit trail" may be better achieved via a TRIGGER wherein you can compare NEW. to OLD..


How to set up tns listeners for Multiple Oracle Installations on One machine So I have two oracle installations on a development/testing machine (10g and 11g, respectively).

The platform is RHEL4.

Now I need to have both these oracle up and have tns listeners for them running.

How do I do that?

I've tried having separate listener.ora files in 10g's home dir and 11g's home dir, and try to start them up separately (under different users, oracer10 for 10g, oracer11 for 11g).

However, as soon as one of the tns listener is started, the other wont start and complain something like "TNS-01106: Listener using listener name LISTENER11 has already been started".

I do have different name for the two listeners. (LISTENER10 and LISTENER11)

Thanks,

Here are the listener.ora files

For 10g:

SID_LIST_LISTENER10 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /home/oracer/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CER)
      (ORACLE_HOME = /home/oracer/oracle/product/10.2.0/db_1)
    )
  )

LISTENER10 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
     (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = SAP_1)(PORT = 1521))
      )
    )
  )

For 11g:

SID_LIST_LISTENER11 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /home/oracer11/app/oracer11/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CID)
      (ORACLE_HOME = /home/oracer11/app/oracer11/product/11.2.0/dbhome_1)
    )

  )

LISTENER11 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
     (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = SAP_1)(PORT = 1522))
      )
    )
  )

You need to have different keys for the IPC entries in the two configurations, e.g. KEY = EXTPROC10 and KEY = EXTPROC11. IPC uses memory and semaphores etc. that may be shared across the machine, so you need to provide a way for the instances to be differentiated.

Also make sure you're starting the listeners explicitly, e.g. lsnrctl start listener10, and you have the environment set properly for your two accounts, including LD_LIBRARY_PATH pointing to the right ORACLE_HOME.


Using "Generate Scripts" with EMERGENCY/SINGLE USER mode I am trying to create an INSERT script to salvage data from a single table in a corrupt database.

I've succesfully put the database online and put it in EMERGENCY / SINGLE USER mode (following this http://www.sqlskills.com/blogs/paul/disaster-recovery-101-hack-attach-a-damaged-database/
)

This allow SELECTs against the data. However, when trying to use the "Generate Scripts" tool in SSMS, I get an error because there already is a connection to the database. I assume the script tool is attempting to create a seconday (sql server local?) connection/open the database to do its work, but my SSMS session is blocking.

Is it possible to ensure that the script tool is using same connection as the already established? Or drop the connection, but keep the database in EMERGENCY mode?

You can drop your connection and the database will stay in EMERGENCY/SINGLE user mode.  And the object manager in SSMS does use it's own connection.  I'm not sure if the script generator tool uses yet another connection or not.  However there is no way to guarantee that your generate scripts tool connection will be the one that takes up the one connection once you have dropped it.  The best bet is to generate your script on an alternate database (where ever you are moving your data to should work).  Then copy that script to the query window that is connected to the database and run it.

EDIT:

Ok I did a little testing.  If you drop your query connection and open up a connection in Object Explorer you can script an INSERT statement (assuming that is what you want).

In Object Explorer find your table, then right click on it.  Go to Script Table As -> INSERT To -> Clipboard

Make sure you use clipboard.  If you do new query window it will fail.  Then disconnect Object Explorer and open a query window to your database and paste.

Otherwise I'll need more information about what type of INSERT statement you are trying to create.

EDIT2:  Based on comments

Assuming that you have your destination database (where you are trying to move the data to) on the same instance (and if it isn't make one at least temporarily) then use the following piece of code.

INSERT INTO DestinationDB.dbo.DestinationTable
SELECT * FROM SourceTable

If you have an identity column then you will have to do the following

SET IDENTITY_INSERT DestinationDB.dbo.DestinationTable ON

INSERT INTO DestinationDB.dbo.DestinationTable
(Field List)
SELECT [Field List] FROM SourceTable

SET IDENTITY_INSERT DestinationDB.dbo.DestinationTable OFF


How to make PostgreSQL default_tablespace work properly? My question is, "Why isn't database 'screwy' created in tablespace 'screwy'?

I made the following script, with it's output following, to show the problem I am having :

#!/bin/bash
#
export OWNER=yourself
export OBJECT=screwy
export OTHER=not${OBJECT}
export PWD='seecret'
#
sudo -u postgres psql -tc "DROP DATABASE IF EXISTS ${OWNER};"
sudo -u postgres psql -tc "DROP DATABASE IF EXISTS ${OBJECT};"
sudo -u postgres psql -tc "DROP DATABASE IF EXISTS ${OTHER};"
sudo -u postgres psql -tc "DROP TABLESPACE IF EXISTS ${OBJECT};"
sudo -u postgres psql -tc "DROP ROLE IF EXISTS ${OWNER};"
#
str=$(cat /etc/lsb-release | grep DESC)
echo "Linux version is : ${str#*=}"
echo "Postgres version is :"
sudo -u postgres psql -qtc "SELECT version();"
#
echo "CREATE ROLE ${OWNER} WITH CREATEDB LOGIN PASSWORD '${PWD}'"
sudo -u postgres psql -tc "CREATE ROLE ${OWNER} WITH CREATEDB LOGIN PASSWORD '${PWD}';"
echo "CREATE TABLESPACE ${OBJECT} OWNER ${OWNER} LOCATION '/home/${OWNER}/data';"
sudo -u postgres psql -tc "CREATE TABLESPACE ${OBJECT} OWNER ${OWNER} LOCATION '/home/${OWNER}/data';"
sudo -u postgres psql -tc "SELECT 'Tablespace \"' || spcname || '\" is owned by ' || usename \
                             FROM pg_catalog.pg_tablespace, pg_catalog.pg_user \
                            WHERE spcname='${OBJECT}' AND usesysid = spcowner;"
echo "CREATE DATABASE ${OWNER} TABLESPACE ${OBJECT};"
sudo -u postgres psql -tc "CREATE DATABASE ${OWNER} TABLESPACE ${OBJECT};"
#
echo "ALTER ROLE ${OWNER} SET DEFAULT_TABLESPACE TO ${OBJECT};"
sudo -u postgres psql -tc "ALTER ROLE ${OWNER} SET DEFAULT_TABLESPACE TO ${OBJECT};"
echo "GRANT CREATE ON TABLESPACE ${OBJECT} TO ${OWNER};"
sudo -u postgres psql -tc "GRANT CREATE ON TABLESPACE ${OBJECT} TO ${OWNER};"
#
echo "## As ${OWNER}"
echo "*:*:*:yourself:${PWD}" | cat > ~/.pgpass
chmod 600 ~/.pgpass
#
echo "SHOW DEFAULT_TABLESPACE;"
psql -tc "SHOW DEFAULT_TABLESPACE;"
#
echo "CREATE DATABASE ${OBJECT};"
psql -tc "CREATE DATABASE ${OBJECT};"
#
echo "CREATE DATABASE ${OTHER};"
psql -tc "CREATE DATABASE ${OTHER} TABLESPACE ${OBJECT};"
#
echo "## As postgres"
sudo -u postgres psql -qtc "SELECT 'Database \"' || d.datname || '\" is in tablespace \"' || t.spcname || '\" in disk directory \"' || pg_tablespace_location(t.oid) || '\".' FROM pg_database d \
                         LEFT JOIN pg_catalog.pg_tablespace t \
                                ON t.oid = d.dattablespace \
                             WHERE datistemplate = false \
                              AND datname IN ('${OTHER}', '${OBJECT}', '${OWNER}');"

When I execute it, it looks like this :

DROP DATABASE
DROP DATABASE
DROP DATABASE
DROP TABLESPACE
DROP ROLE
Linux version is : "Ubuntu 14.04 LTS"
Postgres version is :
 PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-16ubuntu6) 4.8.2, 64-bit

CREATE ROLE yourself WITH CREATEDB LOGIN PASSWORD 'seecret'
CREATE ROLE
CREATE TABLESPACE screwy OWNER yourself LOCATION '/home/yourself/data';
CREATE TABLESPACE
 Tablespace "screwy" is owned by yourself

CREATE DATABASE yourself TABLESPACE screwy;
CREATE DATABASE
ALTER ROLE yourself SET DEFAULT_TABLESPACE TO screwy;
ALTER ROLE
GRANT CREATE ON TABLESPACE screwy TO yourself;
GRANT
##  As yourself
SHOW DEFAULT_TABLESPACE;
 screwy

CREATE DATABASE screwy;
CREATE DATABASE
CREATE DATABASE notscrewy TABLESPACE screwy;
CREATE DATABASE
## As postgres
 Database "yourself" is in tablespace "screwy" in disk directory "/home/yourself/data".
 Database "screwy" is in tablespace "pg_default" in disk directory "".
 Database "notscrewy" is in tablespace "screwy" in disk directory "/home/yourself/data".

My question is, "Why isn't database 'screwy' created in tablespace 'screwy'?

Per the PostgreSQL tablespaces documentation:

  If a database is created without specifying a tablespace for it, it
  uses the same tablespace as the template database it is copied from.

Per the CREATE DATABASE documentation:

  By default, the new database will be created by cloning the standard
  system database template1.

You could either alter the template1 database's tablespace to screwy, or create a new template database that has a tablespace of screwy, then specify that new database as a template when creating your screwy database.


MySQL INSERT ... SELECT Performance I know that this query will lock table2:

UPDATE table1... SELECT .. FROM table2

How about this query ?

INSERT INTO table1... SELECT .. FROM table2

Does this query also create a lock on table2?

You just asked

  Does:

INSERT INTO table1... SELECT .. FROM table2

  Also create a lock on table2?

Yes, it does create a lock on table2.

I wrote about this behavior back on Aug 08, 2014 (See my answer to MySQL consistent nonlocking reads vs. INSERT ... SELECT) In my old post, I mentioned from the MySQL Documentation:

  By default, InnoDB uses stronger locks and the SELECT part acts like READ COMMITTED, where each consistent read, even within the same transaction, sets and reads its own fresh snapshot


Does a composite primary key break the first normal form? Does having a composite/compound key (using multiple columns/attributes) to form the primary key break first normal form? (I am assuming that this is handled by the DBMS, and so the attribute containing composite key does not exist in the table/relation itself, and so this still complies with the first normal form, but is this correct?) thanks.

No, this is quite normal (pun intended). The criteria is

First Normal Form:
Tables are said to be in first normal form when:

The table has a primary key.
No single attribute (column) has multiple values.
The non-key attributes (columns) depend on the primary key.

None of these requirements for 1NF is violated by the design you propose.
For instance a Junction Table created to normalize a many-to-many relationship will always have a composite Primary Key, with each component being also a Foreign Key into one of the joined tables.


SQL Server Query Store on isolated volume Is there a way to isolate Query Store data to a separate volume? I have a thousand or so databases that we’ve gotten pretty good at predicting growth for. I’m unsure of how much storage Query Store will need, but to be valuable across various scheduled workflows, I need 30 days. I know I can specify storage limits for QS, but wondering if I can just add a large volume of cheaper storage and dump Query Store there?

No, as of 2018/04/25, you can't choose the filegroup where Query Store data is placed. In the databases where you enable Query Store, the data goes into the default filegroup.

It was a highly upvoted Connect item, so other folks share your pain - so I would expect that to be an enhancement added in the future.

Thanks to @klitzkrieg, here are the current feedback items:

Option to store query store data in a filegroup other than PRIMARY
Create central repository for Query Store


Oracle EM 13c automatically logged in as dbsnmp I created a custom super administrator user in Oracle EM 13C.

When this super administrator is accessing a portable container (PDB) it is always logged in as dbsnmp. I cannot find any option to set custom own credentials.

When I use the sysman account on EM 13C, I get asked for credentials to connect to database.

Why it does not ask the custom super administrator user for credentials? Only the sysman? Am I missing some privileges in the super administrator user?

Found the answer myself.

Login with the custom super administrator account

Setup -> Security -> Preferred Credential

Select "Database Instance"

Click on "Manage Preferred Credentials" button above table.

Clear dbsnmp credentials under "Target Preferred Credentials".

Next time you access the database instance you will be asked to choose the credentials to use.


Monthly Date Dimension I am doing my first ssis and sql project for uni being new to both. I am creating a date dimension but only month and year no days. I have seen loads of examples online for date dim but including days. Is there a way to create monthly date dim as deadline is approaching and still a lot to do. All help much appreciated.

Cheers

Aidan

Without more specific requirements, this is probably where I'd start:

CREATE TABLE dbo.MonthDimension
(
  StartDate date NOT NULL,
  EndDate   AS (CONVERT(date,     DATEADD(DAY,-1,DATEADD(MONTH,1,StartDate))))
               PERSISTED,
  NumDays   AS (CONVERT(tinyint,  DATEDIFF(DAY,StartDate,DATEADD(MONTH,1,StartDate)))),
  Y         AS (CONVERT(smallint, DATEPART(YEAR,StartDate))),
  M         AS (CONVERT(tinyint,  DATEPART(MONTH,StartDate))),
  Q         AS (CONVERT(tinyint,  DATEPART(QUARTER,StartDate))),
  MonthName AS (CONVERT(varchar(20), DATENAME(MONTH,StartDate))),
  CONSTRAINT PK_MonthDimension PRIMARY KEY (StartDate, EndDate)
);

Then to populate it, just substitute your desired start and end dates:

DECLARE @start date = '20000101',
        @end   date = '20991231';

;WITH x(n) AS
(
  SELECT 0 UNION ALL
  SELECT n+1 FROM x
  WHERE n < DATEDIFF(MONTH, @start, @end)
)
INSERT dbo.MonthDimension(StartDate)
  SELECT DATEADD(MONTH,n,@start) FROM x
  OPTION (MAXRECURSION 32767);

Partial table data:

StartDate   EndDate     NumDays  Y      M   Q   MonthName
----------  ----------  -------  ----   --  --  ---------
2000-01-01  2000-01-31  31       2000   1   1   January
2000-02-01  2000-02-29  29       2000   2   1   February
2000-03-01  2000-03-31  31       2000   3   1   March
2000-04-01  2000-04-30  30       2000   4   2   April
2000-05-01  2000-05-31  31       2000   5   2   May
2000-06-01  2000-06-30  30       2000   6   2   June
2000-07-01  2000-07-31  31       2000   7   3   July
2000-08-01  2000-08-31  31       2000   8   3   August
2000-09-01  2000-09-30  30       2000   9   3   September
2000-10-01  2000-10-31  31       2000   10  4   October
2000-11-01  2000-11-30  30       2000   11  4   November
2000-12-01  2000-12-31  31       2000   12  4   December
2001-01-01  2001-01-31  31       2001   1   1   January
2001-02-01  2001-02-28  28       2001   2   1   February
2001-03-01  2001-03-31  31       2001   3   1   March

... 1,170 rows in the middle ...

2098-10-01  2098-10-31  31       2098   10  4   October
2098-11-01  2098-11-30  30       2098   11  4   November
2098-12-01  2098-12-31  31       2098   12  4   December
2099-01-01  2099-01-31  31       2099   1   1   January
2099-02-01  2099-02-28  28       2099   2   1   February
2099-03-01  2099-03-31  31       2099   3   1   March
2099-04-01  2099-04-30  30       2099   4   2   April
2099-05-01  2099-05-31  31       2099   5   2   May
2099-06-01  2099-06-30  30       2099   6   2   June
2099-07-01  2099-07-31  31       2099   7   3   July
2099-08-01  2099-08-31  31       2099   8   3   August
2099-09-01  2099-09-30  30       2099   9   3   September
2099-10-01  2099-10-31  31       2099   10  4   October
2099-11-01  2099-11-30  30       2099   11  4   November
2099-12-01  2099-12-31  31       2099   12  4   December


How to store multiple data in a cell inside MySQL? I want to store multiple dates in a row. Each row has a unique id and each corresponding id can have multiple action dates. The number of dates may differ for each row. I have multiple ID's and each ID has multiple dates.

ID   Date1        Date2        Date3        ...          ...         ...
1    2018-05-19   2018-04-09   2018-08-09
2    2016-01-30   2017-05-04   2016-06-07   2020-05-17   2021-06-23  ...
3    2001-05-06
4    2018-11-05   2013-06-09

What is the best way to store multiple dates for a given id?

Unless each date is a specific property (start date, end date, due date, date created, ...) then storing them as columns is probably bad form.

It is likely that you would be better storing the dates in separate rows like so:

ItemDates
===========
ID     (PK)
ItemId
Date

so:

ID   Item   Date
 1   1      2018-05-19
 2   1      2018-04-09
 3   1      2018-08-09
 4   2      2016-01-30
 5   2      2017-05-04
 6   2      2016-06-07
 7   2      2020-05-17
 8   2      2021-06-23
 9   3      2001-05-06
10   4      2018-11-05
11   4      2013-06-09

Or if the dates will be unique per item, you can do without the surrogate key (ID) and declare ItemId,Date to be the primary key.

If you are not careful this could turn into a property bag (the Entity/Attribute/Value or EAV pattern, which is often considered an anti-pattern) though. For more specific advice for your use case, you should update the question with a bit more information about what the date values represent in the system that you are modelling.


PLSQL : DBMS Jobs - parallelization? I am looping over a cursor and executing a stored procedure; how do I parallelize this process through dbms.jobs?

for rec in select column from table1
loop
execute stored_procedure( rec.column );
end loop;

The pl/sql package DBMS_PARALLEL_EXECUTE can be used to parallelize pl/sql. Examples can be found in the manual. You can use the first example (Chunk by ROWID):

define a procedure

create process_chunk(start_id rowid, end_id in row_id)
...
begin
...
for rec in select column from table1 where rowid between start_id and end_id
  loop
  stored_procedure( rec.column );
  end loop;
...
end;
/

and then (following the example) write a job that does the following

...
begin
...
--create a task
DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask');
-- Chunk the table by ROWID in chunks of 100 rows
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('mytask', USER, 'TABLE1', true, 100);
-- execute with parallel level 10
DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', 'call process_chunk(:start_id,:end_id)',
  DBMS_SQL.NATIVE,  parallel_level => 10);

--and implement code for error handling and removing the task according to the example.
...
end;


Difference in Backup File Size I'm using Ola Hallengren's backup script.
I've added a network share location on the sqlcmd so that when the full backup job runs, the backup file will be saved in two different locations: 1 on the Z drive of the VM, and another one on the network share location. Here’s what the sqlcmd looks like:(please note that I've changed the file path of the network share...)

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d LSDBA_DB -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'Z:\Backups, \\, @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 192, @CheckSum = 'Y', @LogToTable = 'Y'" –b

After the job runs successfully, I now see that the backup file (.bak) is saved on both places. However, I've observed that there's a difference in backup file size between the one saved on the Z: drive and the one saved on the Network share.

This is the same case for the rest of the backup files (the one being saved on the network share are larger size). My thinking is that it should be the same size.

Any ideas why the size difference between the two backup files?

Thanks for the help.

I don't think you're doing what you want to do. It looks like you're striping your backups across two paths, which means your backup is evenly split between the two locations.

I suspect that your actual backup size is not 3.5MB, but rather 7MB. The difference in size is most likely due to the local storage being quicker.

Per Ola Hallengren's documentation:

  If you specify multiple directories, then the backup files are striped
  evenly across the directories.

I would strongly suggest modifying this script to only write to one path, and then use an automated method to copy this backup set to another location.


What does SSRS technically do when it expires the cache? Our data is loaded daily.  My report can be run, using a parameter, for any number of sites (100 is the total number of sites) and I want it to cache a temporary copy of the report and expire it on a schedule - a schedule that is triggered at the end of our daily load.  This is simple.

Where it gets complicated is I have a cache refresh plan to pre-load the cache for all 100 sites.  I need to make sure the cache expires before this plan runs - a plan that is tied to a schedule I want to trigger at the end of our load.

If I trigger the jobs, in order, at the end of the load, they are effectively triggered together, so I would like to understand what happens on the system level when SSRS expires a cache so that I can ensure the refresh occurs after the expiration.  Is there a value in one of the ReportServer tables that is changed or a row deleted?

For what it's worth, this is what I am seeing in my testing (the cache is successfully pre-loaded), but I would like to understand the technical architecture to give myself a better comfort level before using this in production.

I see that it's an old thread but you can essentially have different Shared Schedules to expire and refresh the cache executed in that order to solve the problem.


How to set a filesystem size limit for DB2 database? Is it possible? I'm looking for a way to limit a DB2 database size (in disk)  at creation time. But I couldn't find it.

At SQL server we can do by this way:

Create Database MyDatabase
on (Name='MyDatabase_Data',
    Filename='c:\db\BdUnisanta_Data.mdf',
    Size= 20MB,
    FileGrowth = 10%,
    Maxsize=100MB)
log on
    (Name = 'MyDatabase_log',
     Filename = 'c:\db\MyDatabase_Log.ldf',
     Size = 5MB,
     FileGrowth = 5%,
     MAXSIZE = UNLIMITED
     )

What would be the equivalent form in DB2? Is it possible?

Yes.  Tablespaces in DB2 have a MAXSIZE attribute that you can use.  You can specify these size attributes in the CREATE DATABASE statement (or when creating additional tablespaces), or use ALTER TABLESPACE to set them later.

create database mydb
   automatic storage yes
   on C:\mydb
   user tablespace managed by automatic storage
      initialsize 20 M increasesize 10 percent maxsize 100 M

You could specify similar options for the system catalog tablespace and the default system temporary tablespace, but I would not recommend doing so.


How is the Database Encryption Key for SQL Server TDE linked to the certificate? The MSDN article on SQL Server TDE states "Create a database encryption key and protect it by the certificate". What does protect mean? The database encryption key is not created from the certificate?

"Create a database encryption key and protect it by the certificate". What does protect mean?

It means that TDE uses the Database Encryption Key (DEK) to encrypt/decrypt the database BUT the Database Encryption Key REQUIRES the existence of the server certificate in order to do this.  Thus, the Database Encryption Key is 'protected' by its requirement that the server certificate exist.

This 'requirement' is quite obvious if you back up a TDE database and try to restore or attach it to another Sql Server instance that does not have that server certificate.  You'll get an error similar to this:

My answer to a related question here provides some additional information about TDE requirements.


Does SELECT remove dead rows like VACUUM does? I was fiddling with VACUUM and noticed some unexpected behavior where SELECTing rows from a table seems to reduce the work VACUUM has to do afterwards.

Test Data

Note: autovacuum is disabled

CREATE TABLE numbers (num bigint);
ALTER TABLE numbers SET (
  autovacuum_enabled = 'f',
  toast.autovacuum_enabled = 'f'
);

INSERT INTO numbers SELECT generate_series(1, 5000);

Trial 1

Now we run an update on all the rows,

UPDATE numbers SET num = 0;

And when we run VACUUM (VERBOSE) numbers; we get,

INFO:  vacuuming "public.numbers"
INFO:  "numbers": removed 5000 row versions in 23 pages
INFO:  "numbers": found 5000 removable, 5000 nonremovable row versions in 45 out of 45 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 6585
There were 0 unused item pointers.

Trial 2

Now we issue another UPDATE, but this time we add a SELECT afterward,

UPDATE numbers SET num = 1;
SELECT * FROM numbers;

And when we run VACUUM (VERBOSE) numbers; we get,

INFO:  vacuuming "public.numbers"
INFO:  "numbers": removed 56 row versions in 22 pages
INFO:  "numbers": found 56 removable, 5000 nonremovable row versions in 45 out of 45 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 6586
There were 56 unused item pointers.

What exactly is happening here? Why does the second version I run, after the SELECT remove dead tuples from the pages it visits, quite like VACUUM does?

I am running Postgres 11.3 on macOS 10.14.5.

From this post on /r/PostgreSQL to an answer by Laurenz Albe it seems that Heap Only Tuples (HOT) updates may be responsible. From the description of HOT updates in src/backend/access/heap/README.HOT

  Effectively, space reclamation happens during tuple retrieval when the
  page is nearly full (<10% free) and a buffer cleanup lock can be
  acquired.  This means that UPDATE, DELETE, and SELECT can trigger space
  reclamation, but often not during INSERT ... VALUES because it does
  not retrieve a row.

The quote is not in the original answer, but the rest is a quote,

  To support or refute this theory, run the following query:

SELECT n_tup_upd, n_tup_hot_upd
FROM pg_stat_user_tables
WHERE schemaname = 'public' AND relname = 'TABLE_NAME';

  If n_tup_hot_upd is greater than zero, we have got a case.