How to update a field in sharded environment in mongodb using C# driver I have successfully done loading in sharded environment. It is working fine. But when I do update in sharded environment, I am getting the error as :

WriteConcern detected an error
 'update { q: { IsTagged: "0" }, u: { $set: { IsTagged: "1" } }, multi: false, upsert: false } does not contain _id or shard key for pattern { JobId: "hashed" }'.
 (Response was { "ok" : 1, "code" : 61, "err" : "update { q: { IsTagged: \"0\" }, u: { $set: { IsTagged: \"1\" } }, multi: false, upsert: false } does not contain _id or shard key for pattern { JobId: \"hashed\" }", "n" : NumberLong(o), "updatedExisting" : false }).

I need to update the value of Istagged from 0 to 1 . But it is showing as read concern error.I can get the point that the error is due to not giving the shard key. The Code which I am using is :

var query = Query.EQ("IsTagged", "0");
var update = Update.Set("IsTagged","1");
collection.Update(query, update);

I am using C# driver to communicate with mongodb. I dont know how to mention shard key in this coding.

All targeted updates (multi: false so targeting a single document) must contain enough information to target a single document in order to be routed correctly (i.e. the shard key, or the _id of the document being targeted).

To understand why, consider what would happen with your attempted update. You have said (by stating multi: false that you only want one document to be updated.

First, because you have not specified the _id value of the document (unique value), if the query is broadcast to all shards, there could be multiple resulting updates (each shard will only update one document but if you have 3 shards, 3 docs could be updated total). Hence that is rejected.

The other option is to route the update operation only to a single shard.  But to do so, you must specify the JobId (your shard key) as part of the criteria for your update.  Then the operation would not need to be broadcast to all shards, it could be routed to a single shard and your original criteria (only update one such document) could be satisfied.

If your intent is to update more than one document, then simply change multi to true.  If not, then you need to be more specific about what single document you want to update.


How to tune a stored procedure that is fast on test data but slow in production If you have a SQL Server 2012 database with test data in development and preproduction phase and the real from the company's server take place only in the production phase. In other words, the real data take place only the the production phase and not in the test and pre production phase.

Production phase take place inside in the company's server.

When you have a stored procedure that takes a lot of time to retrieve the data in the production phase and you need to make a performance tuning.

This specific stored procedure is part of critical function for a webpage. The stored procedure is used daily.

My questions are:

Is it recommended to do a performance tuning in the production phase in relation to real data?
Is it something that I should be aware of? Today, I'm a newbie in performance tuning.
What plugin or tool do you use in order to review the the database and its specific stored procedure's performance?

Thanks!

"Performance" is a much broader topic than is being assumed here. There are quite a few factors that influence performance:

The Data Model: Are you using appropriate data types? Do you have Primary and Foreign Keys defined? Do you have repeating string values (bad) instead of lookup tables? Do you have an appropriate structure or something that is convoluted and/or over-engineered
Number of rows in the table(s): All things being equal, the Query Optimizer can choose to do things differently depending on the overall row-count of the table(s) involved in each query. Hence, testing in environments with radically different data volumes (not just distribution differences) can only test functionality, not performance.
Distribution of data values: This obviously affects statistics associated with indexes, but assuming that the "auto-create statistics" option is enabled (and it is by default), then any column used for filtering and/or sorting can have system-created statistics associated with it.
Indexes: The fact that indexes help find data should be obvious, but incorrectly defined indexes can sometimes hurt. Also, too many indexes can easily hurt DML operations (especially in relation to available hardware and contention).
Up-to-date statistics: Even if you have the same indexes between environments, and the exact same data, if the statistics are out of date then you can still see performance issues if the Query Optimizer doesn't know to use a certain index, or accesses it incorrectly due to obsolete row estimates.
Contention: What other queries are running on the system? Production will naturally have a lot more concurrent usage than a Development / QA / UAT system. More concurrent queries means more locking and less available hardware resources.
Code: There are usually several ways to achieve a goal, but they can have radically different requirements. There are many ways to write queries across the spectrum of horrible to great. And there are ways of structuring operations that are far better or worse than other methods. Often enough, even small changes in query and/or process structure can have major impact, regardless of any of these other factors.
Hardware: RAM (how much exists, how fast is it, and how much is available), CPU (how many cores, how fast are they, how many are allocated to SQL Server, how much is available), and Disk I/O (type of disk: SATA vs SAS vs ?, NAS vs SAN vs Local vs ?, Raid level, SSD vs non-SSD, etc, available throughput). For all of these areas, other processes running on the system can take up RAM, CPU, and I/O, which affects how much of the total amount is available to SQL Server to use. Meaning, tons of CPU does not help if most of it is being used by one or more programs outside of SQL Server.
Configuration: Things like Snapshot Isolation, Async Update Statistics, Auto-growth, Auto-shrink, etc can impact performance. Are they configured the same between Dev / QA / Production environments? Are they configured correctly to begin with?

Generally speaking, even horribly inefficient code and/or structure will appear to work quickly enough in most Development and QA environments. With only 100 - 1000 rows of data, poor choices in data modeling and/or coding will be hidden.

At the very least you should have, in a non-Production environment, the exact same structure and configuration (tables, indexes, database, server, etc) that Production has. At this stage you should be focused on a good, scalable data model as well as good T-SQL coding.

Indexes are a bit trickier as they require a representative data set (volume and distribution) since that will impact the execution plan. This can be done in one or more testing environments (QA, UAT, ?). If it is not possible to get this data into a lower environment due to sheer size, then this will have to be done in Production.

But, even with this, true performance tuning can really only be done in Production since that is "reality" whereas the other environments are not (unless you somehow manage to duplicate not only the hardware, but also the usage / load). So you do your best, educated guess in lower environments but you will only know in the end once it is in Production. For testing code changes, I have found it helpful to create a global temporary stored procedure so that I can do side-by-side comparisons in the ideal environment.


How to execute dependent multiple SQL queries from multiple databases via same PHP web page? We can connect a single PHP page with more than one databases. But what about executing multiple dependent queries that belong to different databases?

mysqli_multi_query(connection,query);

does not seems helpful as it asks for a single shot while my second query uses result of the first. I tried using two connection strings and using them with two queries but this does not seems to be helpful. My code uptil now is here:

   <?php
   //Connection with DB
   $user = "root";
   $pass = "";
   $db1 = "raw_images";
   $db2 = "labelled_images";
     $conn1 = mysqli_connect('localhost',$user,$pass,$db1,true);
   $conn2 = mysqli_connect('localhost',$user,$pass,$db2,true);
   if(!$conn1||!$conn2)
   {
   die(mysqli_error());
   }
*/
//query to display image
   $query1 = "SELECT * FROM raw_images.tbl_raw_image WHERE id IN
    (SELECT id FROM (SELECT id FROM raw_images.tbl_raw_image ORDER BY RAND() LIMIT 1) t)";
   $sth = $conn1->query($query1);
   $result=mysqli_fetch_array($sth);
   $id=$result['id'];
   $image=$result['image'];
   $query2="INSERT INTO `labelled_image`.`tbl_labelled_image` (`id`,`image`, `label`, `id_in_raw_image_table`) VALUES (NULL, '$image', '0', '$id')";
   $rs  = mysqli_query($conn2, $query2);
   echo ''; ?>

When using such approach, first query executes successfully but not the second one. While executing it simply does not inserts values which I retrieved from the first table but only the values which are given directly to the query like label which I pass zero hence it results in the following outcome:

You shouldn't need two connections for this as the databases in your code are on the same MySQL instance and the DB user apparently has grants to use both.

All you have to do is prefix your table with the database name in the SQL statements, as you have already done. You can use the same connection for both. (It doesn't matter which database is specified in the connection parameters, I suspect this parameter can even be empty.)


INSERT AFTER trigger failing with multiple rows I have a trigger (AFTER INSERT) that has been working in single inserts, and I believed it would work for multiples but its not. I researched it and am currently stuck as what I'm reading implies it should work. Any advice on the below would be appreciated: -

ALTER TRIGGER [dbo].[Trig_Tb_Users_Created]
    ON  [dbo].[Tb_Users]
    AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @CreatedBy INT

IF (SELECT COUNT([ID]) FROM [Tb_Users] WHERE [ADUsername] = SUSER_NAME()) = 0
    INSERT INTO [Tb_Users] ([ADUsername]) VALUES (SUSER_NAME());

SELECT @CreatedBy = (SELECT TOP 1 [ID] FROM [Tb_Users] WHERE [ADUsername] = SUSER_NAME());

UPDATE [Tb_Users] SET [CreatedBy] = @CreatedBy WHERE [ID] in (SELECT [ID] FROM inserted);

INSERT INTO [Tb_Permissions] ([LnkUserID]) VALUES ((SELECT [ID] FROM inserted));

END;

I referred to this article (Create DML Triggers) and to my understanding I am following the syntax correctly.

Bu I get the below error message when trying to insert multiple rows in to Tb_Users that refers to this trigger saying: -

Msg 512, Level 16, State 1, Procedure Trig_Tb_Users_Created, Line 28 [Batch Start Line 0]
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

This is in MS SQL Server 13.0.4522.0

Try taking out the VALUES clause in your final insert (See the commented line below which give your error) and replace it with a simple insert (see the final insert below).

Declare @Tb_Permissions table (LnkUserID int)
Declare @Inserted table (ID int)
insert into @Inserted(id) values(1),(2)
--INSERT INTO @Tb_Permissions ([LnkUserID]) VALUES ((SELECT [ID] FROM @Inserted));
INSERT INTO @Tb_Permissions ([LnkUserID]) SELECT [ID] FROM @Inserted;


Tracing 'Invalid Number Error' from millions of rows using a large, complicated query Background
I am working on fixing a large query that I inherited. The query consists of over 200 lines of DML statements.

The search queries the database restricting the data to a single month. Most months work just fine, but there are a few months that generate an ORA-01722: Invalid Number error.

I suspect the problem is one of the following:

a column defined as VARCHAR contains alpha characters but is
being used where a number is required
a column is null and is being used where a number is required

If I can find the row that is causing the problem, I can change the query to handle said data appropriately. However, I don't know how I can isolate that row. I have attempted to break the query into parts and see where null values are being generated, but the entire query is dependent on a number of outer joins. I have been able to isolate the range to few days, but that doesn't allow me to isolate the row in question.

Also, the query has been defined as a stored procedure. At the end of the procedure I have defined an error handler to catch all errors and display a message:

EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20001, error_message || parameter_string_values, TRUE);

However, the procedure raises the Invalid Number error and does not get trapped by the error handler at the bottom.

Questions

Are there any methods or database variables that I can set to help
isolate this error?
Why isn't the ORA-01722: Invalid Number error being trapped by the
error handler?

Your suspect #2 cannot be true because null values do not cause ORA-01722.

Q1: You might try

Decode (Translate (AlphanumericColumn, 'x0123456789', 'x'),
  Null, To_Number (AlphanumericColumn),
  SomeIdentifyingNumericReplacementValueForNonNumericContent
)

on all the alphanumeric columns whereever any one is used as a numeric value and later scan for the identifying numeric replacement value, which might even be null if still identifying, in the output to spot the offending rows.

Q2: It might be that the offending conversion occurs outside the error handling block, particularly in one of the initializers in the declare part before the begin or in another exception handler immediately before your When Others.
If true, you should wrap the existing code into another inner declare-begin-end block while keeping your exception handler at the outermost level.


Mariadb with more than one node each node will have recent 1 year data I am creating a saas which will then have time based data every seconds for example.
I want horizontal scalling when data reach its limit of 1 year.
after 1 year old data should be shift to another server so only 1 year data is available on 1st node
2nd will have 1 year old data and 3rd will have 2 years old data so on.

let me know which db tool I should use to achieve this and how?
or
let me know better solution if any

You may want to look at the MariaDB Spider storage engine which is a sharding engine that allows you to partition the data e.g. by range so that data in a certain range will be stored on a certain backend server.

Let's say you have a table with year column. You can then say that values in a particular year should go to a particular backend server. See some example use cases here.

This will give you node 1 for the first year, node 2 for the second year and so on, and no shifting of data, but that is probably better anyway. You can always rename the servers, if that helps.

If you're storing a new row every second, that means 31,536,000 rows per year, which is probably absolutely fine, though it's always a good idea to make sure the table(s) are optimally designed especially when you're storing a lot of data like this.


Calculation Showing Inaccurate Answer I can not get this calculation to display the answer I need. I am sure it is my lack of SQL Server skills, but this is my conundrum.

In the sample DDL Below, I am wanting to run the query (1111-666)/1111 which broken down is 450/1111 which equates to .4050

When I run each calculation step individually (AnnualSales, DeptSales, Tax) the SUM() is accurate, but when I try to run one equation to get me the ONE result that I need, I keep getting .7889 as my answer.

What in this query should be changed so that my output is .4050?

Declare @Calc Table
(
  employee varchar(100)
  ,annualsales decimal(10,2)
  ,dep1sales decimal(10,2)
  ,dep2sales decimal(10,2)
  ,dep3sales decimal(10,2)
  ,dep4sales decimal(10,2)
  ,dep5sales decimal(10,2)
  ,tax decimal(10,2)
)

Insert Into @Calc (employee, annualsales, dep1sales, dep2sales, dep3sales, dep4sales, dep5sales, tax) Values
('emp1', '1111.00', '26.12', '83.00', '16.55', '21.77', '87.08', NULL)
,('emp1', NULL, '28.07', '11.00', '101.24', '47.03', '76.19', NULL)
,('emp1', NULL, '35.25', '7.00', '33.64', '43.54', '43.52', NULL)

Select
employee
,SUM(ISNULL(annualsales,0)) AnnualSales
,SUM(ISNULL(dep1sales,0)+ISNULL(dep2sales,0)+ISNULL(dep3sales,0)+ISNULL(dep4sales,0)+ISNULL(dep5sales,0)) DeptSales
,SUM(ISNULL(tax,0)) Tax
,SUM((
    (ISNULL(annualsales,0))
    -    (ISNULL(dep1sales,0)+ISNULL(dep2sales,0)+ISNULL(dep3sales,0)+ISNULL(dep4sales,0)    +ISNULL(dep5sales,0))
    +(CAST((ISNULL(tax,0)) AS INT)))
    /NULLIF(ISNULL(annualsales,0),0))
FROM @Calc
GROUP BY employee
ORDER BY employee asc

You should get the correct result if you perform your calculation using the sums for each part instead of performing the calculation on each row and summing the result.

rextester: http://rextester.com/ZGQ51639

Declare @Calc Table
(
  employee varchar(100)
  ,annualsales decimal(10,2)
  ,dep1sales decimal(10,2)
  ,dep2sales decimal(10,2)
  ,dep3sales decimal(10,2)
  ,dep4sales decimal(10,2)
  ,dep5sales decimal(10,2)
  ,tax decimal(10,2)
)

Insert Into @Calc (employee, annualsales, dep1sales, dep2sales, dep3sales, dep4sales, dep5sales, tax) Values
('emp1', '1111.00', '26.12', '83.00', '16.55', '21.77', '87.08', null)
,('emp1', null, '28.07', '11.00', '101.24', '47.03', '76.19', null)
,('emp1', null, '35.25', '7.00', '33.64', '43.54', '43.52', null)
,('emp2', 1954.85, '1120.65', '0', '0', '0', '0', 392.70)

Select
employee
,AnnualSales=sum(isnull(annualsales,0))
,DeptSales  =sum(isnull(dep1sales,0)+isnull(dep2sales,0)+isnull(dep3sales,0)+isnull(dep4sales,0)+isnull(dep5sales,0))
,Tax        =sum(isnull(tax,0))
,[SomeCalc] =(sum(isnull(annualsales,0))
             -(
               sum(isnull(dep1sales,0)+isnull(dep2sales,0)+isnull(dep3sales,0)+isnull(dep4sales,0)+isnull(dep5sales,0))
             +(sum(isnull(tax,0))*.75)
              )
             )
            /nullif(sum(isnull(annualsales,0)),0)
from @Calc
group by employee
order by employee asc


How can I justify making changes to my redo logs? I don't spend a lot of time with my Oracle environment. I'm more of a SQL Server guy. I need to look into the alert logs to check for correlation of an application error. What I found instead was collections of this:
Thread 1 cannot allocate new log, sequence 947
Checkpoint not complete
  Current log# 1 seq# 946 mem# 0: D:\ORACLE\DATA\TRAXTRAI\ONLINELOG\O1_MF_1_G0B8NV7H_.LOG
  Current log# 1 seq# 946 mem# 1: D:\ORACLE\RECOVERY\TRAXTRAI\ONLINELOG\O1_MF_1_G0B8NVLP_.LOG
Thread 1 cannot allocate new log, sequence 947
Private strand flush not complete
  Current log# 1 seq# 946 mem# 0: D:\ORACLE\DATA\TRAXTRAI\ONLINELOG\O1_MF_1_G0B8NV7H_.LOG

These are happening, in my test environment, rather frequently. At least once every 20 minutes for the last several years according to the log. I have read that I can ignore these but I was see more often is that I use this as motivation to play with my redo log settings/framework. It's just that I don't know exactly what I need to do with them. My test environment is not running archive logs which is supposed to play a factor in this. (My production environment is doing this more frequently so I am using this to try and get a handle on my solution approach.) I am sure this has a performance impact and should be looked at.

This is what I know so far...

SELECT name,value
  FROM v$sysstat
  where name in ('redo entries','redo log space requests');

redo entries             30463497
redo log space requests  89

I also have this

select t1.group#, t1.bytes/1024/1024 as "Size(MB)", t2.member, t1.status
from v$log t1, v$logfile t2
where t1.group# = t2.group# ;

GROUP SIZE  MEMBER                                                      STATUS
  3   50    D:\ORACLE\DATA\TRAXTRAI\ONLINELOG\O1_MF_3_G0B8NW8F_.LOG     ACTIVE
  3   50    D:\ORACLE\RECOVERY\TRAXTRAI\ONLINELOG\O1_MF_3_G0B8NWFR_.LOG ACTIVE
  2   50    D:\ORACLE\DATA\TRAXTRAI\ONLINELOG\O1_MF_2_G0B8NVNN_.LOG     CURRENT
  2   50    D:\ORACLE\RECOVERY\TRAXTRAI\ONLINELOG\O1_MF_2_G0B8NVPM_.LOG CURRENT
  1   50    D:\ORACLE\DATA\TRAXTRAI\ONLINELOG\O1_MF_1_G0B8NV7H_.LOG     ACTIVE
  1   50    D:\ORACLE\RECOVERY\TRAXTRAI\ONLINELOG\O1_MF_1_G0B8NVLP_.LOG ACTIVE

50MB seems large enough based on other suggestions but I have also seen people suggest making them smaller or adding new ones. I am still making my way though Managing the Redo Log of the admin guide but I am not confident I will find the answers there.

How can I justify making changes to my redo tables? I know there is likely not enough information to make a suggestion from what little I provide so I basically need to know what else to get. I don't want to make guesses if there is something more I can do.

You don't necessarily need to duplex online redo. But if you have the disk space it should not hurt anything. You should probably add additional log groups. That will give your database more time to archive each online redo log before Oracle needs to use it again. Try adding 3 additional online redo log groups.


how do I configure Oracle Net Listener on a server with two network connections I have a server that I have virtualized and as such it has two network connections, one to simulate a network between several virtual machines and the other to connect to the host physical network. when this server starts, oracle net listener does not start automatically. My understanding of this issue is that there is a conflict in host address configuration in my .ora file seeing as during initial install, net config assistant sets it up with a host address. Can someone advise me on how to correctly configure this Listener to have it startup automatically?

If you have static ips you can configure them all in the listener.ora file:

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))
      (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.1)(PORT=1521))
      (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.2)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc1521))))

If you don't know the IPs or Hostname, you can also leave out the HOST part (this is undocumented as far as I know) this will result in listening on any:

(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(PORT = 1521))
)


Replication Blocks Code Deployment We have a database (SQL Server 2008r2) that is being transactionally replicated, namely tables, sprocs, views, indexed views and UDFs only, to another server (SQL Server 2014) as part of a cautious (read: slow) upgrade process. However, we still need to deploy new code or changes to it in the meanwhile.

Whenever we try to deploy new/changed code to it, using DACPAC under Visual Studio, we get a slew of the following errors:

Error SQL72035: [dbo].[SomeTable] is replicated and cannot be modified.

Is there anyway to avoid this? I was thinking if we could script the drop and create of the replication, we could put it in pre and post-deploy script, respectively, but I'm not sure if a) that is a good approach, and b) common practice.

We are facing the same problem - DACPAC does not support replication (as of now).

What we do is - if the deployment involves/affects replicated tables then

manually drop replication.

Script out drop and create replication using GUI or Powershell
Drop the replication

Run your DACPAC to upgrade your system
Create replication (scripted out previously) and kick off the snapshot of articles.

UPDATE :

USING GUI :

USING PowerShell :

Quickly Script Out Replication Redux - by  CHAD MILLER
Using PowerShell to Script out Replication on SQL Server 2005/SQL Server 2008 by Paul Ibison


Can I query a copy of a database without impacting performance of the primary? I am working with a partner who has a large (150,000,000 records and growing) MSSQL database. They query and populate the database with proprietary software which I cannot easily influence. I would also like to access the data in the db but the company that provides the software are concerned that my additional querying (I may do a lot, in short bursts) would add too much load to an already straining server and cause problems for them.

What are my options here?

Is it possible to set up a server which provides a read-only copy of the data so I can query that without putting much pressure on the original database?

You can do something like log shipping. Assuming you are already taking full and log backups on the primary server then using those backups to maintain a mostly-fresh copy of the database elsewhere will not impact the primary server at all (unless the storage for the backups is the same - which is a no-no anyway). You cannot access the database while logs are restored, but assuming you know when you'll be running your query bursts, you can schedule restore/standby operations outside of those windows (just know that the data won't always be in sync with the production copy, depending on the last log restore).

Your reporting copy can also serve as a warm standby to recover data in the event that the vendor or someone else makes a mistake in production - depending on the log shipping delay you can recover that data from before that mistake without recovering the entire database. I've seen this methodology used in several places, especially where a lot of cretins had ad hoc access to the database. :-)


Update statistics on sys.objects I am working on a query to give me all the database objects that depend directly or indirectly in any level, to a table called dbo.tblborder, which is heavily depended on.
However, this question in particular is related to the query plan of this query,
because I see warnings in the query plan (in the distinct sort operators)
two types of warnings, one related to spillage to tempDB and the other warning related to conversion of data types and cardinality estimate.
the query and the query plan are further down, after the pictures.
Questions
As I am dealing with system objects, how can I find out which ones I need to update the statistics?
Or otherwise, how to get out of this warning on the query plan?
And regarding the data type conversion, is there anything I can do to avoid this, and the cardinality estimate issue?
Some trace flag maybe?
it is a database of 600GB, I would like to find all the dependencies on a specific table, first level alone shows me 325 objects, but it is not a query I would run everyday. I am interested in clearing those warnings, but it is not a question of life and death.
Information
1st picture of warning on spillage on tempdb:

2nd picture of warning on spillage on tempdb:

3rd warning - related to data type conversion and may affect cardinality estimation:

;WITH Radhe AS (

            SELECT DISTINCT
            s2.object_id,
            Name=SCHEMA_NAME(S2.schema_id) + '.' + S2.Name,
            ObjectType = S2.Type,
            DependsOn = s1.object_id,
            DependsOn_Name=SCHEMA_NAME(S1.schema_id) + '.' + S1.Name,
            0 as Level

            FROM sys.sysdepends DP

            INNER JOIN sys.objects S1
                    ON S1.object_id = DP.DepID

            INNER JOIN sys.objects S2
                    ON S2.object_id = DP.ID

            WHERE S1.object_id = OBJECT_ID('DBO.tblborder')

            UNION ALL

            SELECT
            s2.object_id,
            Name=SCHEMA_NAME(S2.schema_id) + '.' + S2.Name,
            ObjectType = S2.Type,
            DependsOn = s1.object_id,
            DependsOn_Name=s1.Name,
            Level + 1

            FROM sys.sysdepends DP

            INNER JOIN Radhe S1
                       ON S1.object_id = DP.ID

            INNER JOIN sys.objects S2
                       ON S2.object_id = DP.DepID

            WHERE Level < 100
              AND S1.object_id <> S2.object_id
              AND S2.object_id <> OBJECT_ID('DBO.tblborder')

)

SELECT DISTINCT *
FROM Radhe
ORDER BY LEVEL DESC, DependsOn_Name

here is the query plan for this query
After updating the stats this way (from How to update statistics for a database's system tables) :
DECLARE @TSql NVARCHAR(MAX) = ''
SELECT @TSql = @TSql +  'UPDATE STATISTICS sys.' + o.name + ' WITH FULLSCAN;' + CHAR(13) + CHAR(10)
FROM sys.objects o
WHERE o.type in ('S')
ORDER BY o.name

--Verify/test commands.
PRINT @TSql

The warnings related to tempdb spillage are still there, however, they have changed as per the picture below:

Nothing has been said or addressed to the following warning though:

Type conversion in expression
(CONVERT(bigint,[Bocss2].[sys].[sysobjvalues].[value],0)) may affect
"CardinalityEstimate" in query plan choice, Type conversion in
expression (CONVERT(bigint,[Bocss2].[sys].[sysobjvalues].[value],0))
may affect "CardinalityEstimate" in query plan choice

The related Q & A How to update statistics for a database's system tables is very good, but did not seem to have solved my problem here entirely, plus no address to the cardinality estimate warning.

The warnings you're seeing most likely come from the sys.sysdepends view.

If you script it out using

EXEC sys.sp_helptext @objname = N'sys.sysdepends'

The definition has a bunch of converts and other nonsense going on.

CREATE VIEW sys.sysdepends AS
 SELECT
  id = object_id,
  depid = referenced_major_id,
  number = convert(smallint,
   case when objectproperty(object_id, 'isprocedure') = 1 then 1 else column_id end),
  depnumber = convert(smallint, referenced_minor_id),
  status = convert(smallint, is_select_all * 2 + is_updated * 4 + is_selected * 8),
  deptype = class,
  depdbid = convert(smallint, 0),
  depsiteid = convert(smallint, 0),
  selall = is_select_all,
  resultobj = is_updated,
  readobj = is_selected
 FROM sys.sql_dependencies
 WHERE class < 2
 UNION ALL
 SELECT  -- blobtype dependencies
  id = object_id, depid = object_id,
  number = convert(smallint, column_id), depnumber = convert(smallint, type_column_id),
  status = convert(smallint, 0), deptype = sysconv(tinyint, 1),
  depdbid = convert(smallint, 0), depsiteid = convert(smallint, 0),
  selall = sysconv(bit, 0), resultobj = sysconv(bit, 0), readobj = sysconv(bit, 0)
 FROM sys.fulltext_index_columns
 WHERE type_column_id IS NOT NULL

sys.objects, on the other hand, is fairly straightforward.

CREATE VIEW sys.objects AS
 SELECT name,
  object_id,
  principal_id,
  schema_id,
  parent_object_id,
  type,
  type_desc,
  create_date,
  modify_date,
  is_ms_shipped,
  is_published,
  is_schema_published
 FROM sys.objects$

The view definition for sys.sysdepends causes the same warnings when queried on its own.

SELECT *
FROM  sys.sysdepends

In general, if you want to control datatypes and indexes and have some performance tuning ability when referencing system views or tables, your best bet is to dump them into a temp table first.


Changed limits: max_connections: 214 I am using  MySQL version 5.7.15 , ubuntu0.16.04.1. When I start the MySQL, I am getting below log,

2016-11-06T13:44:56.838233Z 0 [Warning] option 'table_open_cache': unsigned value 33554432 adjusted to 524288
2016-11-06T13:44:56.839061Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 1049386)
2016-11-06T13:44:56.839078Z 0 [Warning] Changed limits: max_connections: 214 (requested 800)
2016-11-06T13:44:56.839082Z 0 [Warning] Changed limits: table_open_cache: 400 (requested 524288)

  $ ulimit -Sa | grep "open files"

  open files                      (-n) 1024

In google, It is specified like open_files_limit value need to be increased to have more max_connections.

Will it be enough to set below values on the /etc/mysql/mysql.conf.d/mysqld.cnf and restarting the MySQl server ?

open_files_limit = 2048

max_connections = 800

table_open_cache = 600

Is there any other way to change the values without restarting the MySQL server ?

Is setting the open_files_limit on the MySQL configuration file enough ?

You can always check if a variable is dynamic or not with on http://dev.mysql.com/doc/refman/5.7/en/dynamic-system-variables.html

Or looking at the parameter directly:
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_open_files_limit

If it is dynamic you can:

set global <variable_name> = <value>;

  Just don't forget to change in your my.cnf also otherwise next restart will override it.

If it is not then you have to restart MySQL to make it happen. Oracle is working on getting more and more variable dynamic and you can see the trend going through 5.5 -> 5.6 -> 5.7. Unfortunately though there's always going to be parameters which require restart.


VS2012 setup spoils SQL Server 2012 Data Tools for everybody or just me? On Windows 7 Prof I have installed SQL Server 2012 R2 with, as I am pretty well remember, client tools.
Then, I tried to install Visual Studio 2013 Ultimate but failed.
Then I've installed VS2012 Prof (30-day trial).
Now, on launching SQL Server 2012 (i.e. VS2010 Shell)) Data Tools --> New Project I am getting:

Fig.1.1. On launching SQL Server 2012 (i.e. VS2010 Shell)) Data Tools --> New Project
and on pressing OK button

Fig.1.2.
telling:

"The operation you are attempting requires Microsoft SQL Server Data Tools to be installed"  though I am somewhat sure I'have installed SQL Server Data Tools...

Also, a book on SQL Server 2012  I am reading tells me that in such situation I should see (to choose) "SQL Server Database project" template (which is absent in my case)
When I launch VS2012 --> choose New Project ,  I see SQL Server Database project template

Fig.2.1.
but on pressing OK button, I get

Fig.2.2

"This version of SQL Server Data Tools is not compatible with database runtime components installed on this computer"
Also, I cannot rely on this since I am developing against machines with SQL Server 2012 but without VS2012

After installing Download SSDT for Visual Studio 2010 then Download SSDT for Visual Studio 2012 from Download SQL Server Data Tools - October 2013, everething seems started to work but I am eager to understand whether VS2012 setup damages SQL Server 2012 (i.e. VS2010 Shell) Data Tools for everybody or just me?

I had a similar problem with my installation of VS 2012 (Premium). I installed VS 2012, at the custom installation options screen I chose the option for SSDT, but when actually using it, it was really absent (same error as yours).

My idea is that the default installer only adds the template for the project, but not SSDT itself. I also had to install SSDT separately (in this case only for VS 2012) and it worked (SSDT for VS 2010 is necessary only if you want to open SQL projects in VS 2010, but I guess you know that already).


SQL SSRS and Default Values Stored Procedure I am creating a stored procedure with a corresponding default value.
It is using a default value of 2018 below.

create procedure rpt.FinanceReport
    @AccountYearParam int = 2018
as
select * from FinanceTable where AccountYear = @AccountYearParam

How do I pass the default Value into an SSRS report? So when the user selects nothing for parameter, it will display 2018.

"Using Null values", does not work, since Null will product a nothing.
I want the SSRS report to display all values for 2018, if user selects nothing for parameter.

You could handle nulls inside the stored procedure with an isnull() like this:

create procedure rpt.FinanceReport
    @AccountYearParam int
as

set @AccountYearParam = isnull(@AccountYearParam, 2018)
--
select * from FinanceTable where AccountYear = @AccountYearParam

and if you want to get really fancy, you can use this (it will return the current year):

create procedure rpt.FinanceReport
    @AccountYearParam int
as

set @AccountYearParam = isnull(@AccountYearParam, datepart(year, getdate()))
--
select * from FinanceTable where AccountYear = @AccountYearParam

You can also handle it in the where clause, although this method might be considerably slower:

create procedure rpt.FinanceReport
    @AccountYearParam int
as
--
select * from FinanceTable where AccountYear = isnull(@AccountYearParam, datepart(year, getdate()))


Get the pais_id not associated to fabricante_distribuidor_id and producto_solicitud_id, the opposite from SELECT query I have this query for get all the pais_id given a fabricante_distribuidor_id and producto_solicitud_id, the query works fine.

SELECT DISTINCT nom.pais_id
FROM   negocio.fabricante_producto_solicitud neg
       INNER JOIN nomencladores.pais_fabricante_producto_solicitud nom
         ON ( neg.id = nom.fabricante_producto_solicitud_id )
WHERE  neg.fabricante_distribuidor_id = 1
       AND neg.producto_solicitud_id = 1

But I want to get the opposite, I mean all the pais_id that does not associated to fabricante_distribuidor_id and producto_solicitud_id, how?

NOTE:

I though this will be more easy but I'm getting some problems getting the data I need. Take a look to this piece of my model:

Well based on this (and was my bad, my sincerely apologies) what I need to get is the pais.id, pais.nombre that has not relation with any fabricante_producto_solicitud.fabricante_distribuidor_id having the same input, meaning fabricante_distribuidor_id and producto_solicitud_id, this is the right question!

I've tried this query on my own:

SELECT
    ps.id, ps.nombre
FROM
    nomencladores.pais_fabricante_producto_solicitud nom
LEFT JOIN negocio.fabricante_producto_solicitud neg ON (
    neg. ID = nom.fabricante_producto_solicitud_id
)
LEFT JOIN nomencladores.pais_fabricante_producto_solicitud pfs ON (
    pfs.fabricante_producto_solicitud_id = nom.fabricante_producto_solicitud_id
)
LEFT JOIN nomencladores.pais ps ON (
  ps."id" = pfs.pais_id
)
WHERE
    neg. ID IS NULL
OR NOT (
    neg.fabricante_distribuidor_id = 1
    AND neg.producto_solicitud_id = 1
)

But it's complete wrong since I get repeated results and also I don't get the expected output. Can I have some advice?

NOTE2: The tables on purple belongs to nomencladores schema and the one in orange belongs to negocio schema

Here is some structure & data files to play with, let me know if any have problems downloading

EDIT: Playing with queries

I'm still playing with queries trying to get the result I'm looking for but this, I made based on the ones leave here, is not working, meaning I'm getting all the rows all the time not matter what parameter changes, why?

SELECT
 nomencladores.pais.id as ID,
 nomencladores.pais.nombre as nombre
FROM
 nomencladores.pais
WHERE ID NOT IN(
 SELECT
     nomencladores.pais.id
 FROM
     negocio.fabricante_producto_solicitud
        INNER JOIN nomencladores.pais_fabricante_producto_solicitud ON (negocio.fabricante_producto_solicitud.id = nomencladores.pais_fabricante_producto_solicitud.fabricante_producto_solicitud_id)
        INNER JOIN nomencladores.pais ON (nomencladores.pais_fabricante_producto_solicitud.pais_id = nomencladores.pais.id)
        INNER JOIN nomencladores.fabricante_distribuidor ON (negocio.fabricante_producto_solicitud.fabricante_distribuidor_id = nomencladores.fabricante_distribuidor.id)
 WHERE
     negocio.fabricante_producto_solicitud.producto_solicitud_id = 3
AND
     negocio.fabricante_producto_solicitud.fabricante_distribuidor_id = 37
GROUP BY
     nomencladores.pais.id
)

@Lennart's answer is one way to negate your query, but it only selects países that are associated with other fabricantes_distribuidores. If you want to include países that are not linked with any distribuidor, too, you have to add an outer join (I use a left outer join as per convention):

SELECT DISTINCT nom.pais_id
FROM nomencladores.pais_fabricante_producto_solicitud nom
LEFT JOIN negocio.fabricante_producto_solicitud neg
ON ( neg.id = nom.fabricante_producto_solicitud_id )
WHERE neg.id IS NULL OR
NOT ( neg.fabricante_distribuidor_id = 1 AND neg.producto_solicitud_id = 1 );

Check the differences in results between the original query, Lennart suggestion and mine:

mysql> SELECT * FROM nomencladores.pais_fabricante_producto_solicitud;
+----------------------------------+---------+
| fabricante_producto_solicitud_id | pais_id |
+----------------------------------+---------+
|                                1 |       1 |
|                                2 |       2 |
+----------------------------------+---------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM negocio.fabricante_producto_solicitud;
+----+----------------------------+-----------------------+
| id | fabricante_distribuidor_id | producto_solicitud_id |
+----+----------------------------+-----------------------+
|  1 |                          1 |                     1 |
|  3 |                          3 |                     3 |
+----+----------------------------+-----------------------+
2 rows in set (0.00 sec)

Your query:

mysql> SELECT DISTINCT nom.pais_id
    -> FROM   negocio.fabricante_producto_solicitud neg
    ->        INNER JOIN nomencladores.pais_fabricante_producto_solicitud nom
    ->          ON ( neg.id = nom.fabricante_producto_solicitud_id )
    -> WHERE  neg.fabricante_distribuidor_id = 1
    ->        AND neg.producto_solicitud_id = 1;
+---------+
| pais_id |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

Lennart's:

mysql> SELECT DISTINCT nom.pais_id FROM
    -> negocio.fabricante_producto_solicitud neg
    -> INNER JOIN nomencladores.pais_fabricante_producto_solicitud nom
    -> ON ( neg.id = nom.fabricante_producto_solicitud_id )
    -> WHERE  NOT (neg.fabricante_distribuidor_id = 1 AND
    -> neg.producto_solicitud_id = 1);
Empty set (0.00 sec)

Mine:

mysql> SELECT DISTINCT nom.pais_id
    -> FROM nomencladores.pais_fabricante_producto_solicitud nom
    -> LEFT JOIN negocio.fabricante_producto_solicitud neg
    -> ON ( neg.id = nom.fabricante_producto_solicitud_id )
    -> WHERE neg.id IS NULL OR
    -> NOT ( neg.fabricante_distribuidor_id = 1 AND neg.producto_solicitud_id = 1 );
+---------+
| pais_id |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

EDIT: You should only use LEFT JOIN for the "negative" of distribuidor, other relations should be read with normal JOINs:

SELECT DISTINCT ps.id, ps.nombre
FROM nomencladores.pais_fabricante_producto_solicitud nom
LEFT JOIN negocio.fabricante_producto_solicitud neg
ON ( neg.id = nom.fabricante_producto_solicitud_id )
JOIN pais ps
ON ( nom.pais_id = ps.id )
WHERE neg.id IS NULL OR
NOT ( neg.fabricante_distribuidor_id = 1 AND neg.producto_solicitud_id = 1 );

Example results:

+------+--------+
| id   | nombre |
+------+--------+
|    2 | Perú   |
+------+--------+
1 row in set (0.00 sec)

Again: this will select the id and name of every different country on the database that has a solicitud. If you want to select every possible country (no matter if they have solicitudes or not), you have to move the LEFT JOIN to pais and chain it:

SELECT DISTINCT ps.id, ps.nombre
FROM pais ps
LEFT JOIN nomencladores.pais_fabricante_producto_solicitud nom
ON ( nom.pais_id = ps.id )
LEFT JOIN negocio.fabricante_producto_solicitud neg
ON ( neg.id = nom.fabricante_producto_solicitud_id )
WHERE nom.pais_id IS NULL OR neg.id IS NULL OR
NOT ( neg.fabricante_distribuidor_id = 1 AND neg.producto_solicitud_id = 1 );

On my example, we would get:

+------+---------+
| id   | nombre  |
+------+---------+
|    2 | Perú    |
|    3 | Uruguay |
+------+---------+
2 rows in set (0.00 sec)

Both solutions are technically correct, the confusion on what the definition of "the opposite" is. The first one is countries that have solicitudes but not for that provider, the second is all countries on the database (having solicitudes or not) minus the ones that have solicitudes for that particular provider.


Creating an index in both asc and desc directions Over the past few weeks I've been raging against an old Firebird database. This database is crappy for all sorts of reasons, but one thing I noticed was that every single field of every single table has two indexes; each one with a single segment, one in asc order and one in desc order.

Apart from the wtf'ness of having an index for every field in every table, it got me thinking - is there any advantage for single-segment indexes to having two indexes with the same index segments, but one in desc and one in asc? Is there anything to be gained, or would a modern DBMS simple use the asc index and start from the end and work its way backwards if required?

I'd be surprised to hear of a modern database that can't do reverse order index scans.

SQL Server certainly does and the backward scan is indicated in the execution plan.
Firebird (search for backward in the page text).
MySQL


How does this replication behaviour work in a RDBMS? Consider a 3 node (N1, N2, N3) system
N1 is master (used for writes) and N2, N3 (used for reads) are slaves
Write request comes to update R to R1, N1 writes to disk and crashed without replication
Will system continue to work and Oracle select the another master automatically?
Write request comes to update R to R2 on N2
N1 is back again

Which value will be kept - R1 or R2?

I am using Oracle as RDBMS.

It depends on exactly how the "replication" is set up.

Real Application Cluster

N1,N2,N3 are the three nodes of a RAC cluster.

In a RAC system, all nodes are "masters".  You just choose to do READ-ONLY session on the other two.

DML operation on Node 1 tries to set R to R1
Node 1 crashes
Transparent Application Failover fails the connection from Node 1 to
Node 2 (or Node 3).
The DML is "rolled back and tried again" on Node 2.
DML on Node 2 attempts to change R to R2 (but value is now R1).

Logical Standby

DML operation on Node 1 tries to set R to R1
Node 1 crashes
DML on Node 2 attempts to change R to R2.

DML on Node 2 fails since Logical Standbys can only be opened as READ ONLY
Starting with 18c, You can only do DML on Temp Table.

At some point in time, DBAs set the Logical Standby as the primary.

If the change log was successful in writing, this change might be applied
to the standby database.

Golden Gate

DML operation on Node 1 tries to set R to R1
Node 1 crashes before it completes the COMMIT process.
DML on Node 2 attempts to change R to R2.

Since Node 2 is a separate Database, this DML succeeds.


Detection of contradictions I found some old notes of mine from 2011 and I also re-read the excellent article 10 Cool SQL Optimisations That do not Depend on the Cost Model by @lukas-eder. So I thought I would take my notes for another spin. The scenario is quite simple Fiddle
create table emp (
    emp_no int not null primary key,
    title varchar(10) not null,
    salary int not null,
        check (emp_no > 0),
        check (title in ('BOSS','WORK'))
);

insert into emp with recursive t (n) as (
    values (1)
    union all
    select n+1 from t where n+1 < 1000
) select n, case when mod(n,10) = 0 then 'BOSS' else 'WORK' end,
            case when mod(n,10) = 0 then 110 else 0 end + mod(n,90)
from t;

Now, given the rules:
 (TITLE = BOSS) implies (SALARY > 100)

and
 (TITLE = WORK) implies (SALARY <= 100)

which can be implemented as:
-- (TITLE = BOSS) => (SALARY > 100)
alter table emp add constraint cc1
    CHECK ( (title <> 'BOSS' OR salary > 100) );
-- (TITLE = WORK) => (SALARY <= 100)
alter table emp add constraint cc2
    CHECK ( (title <> 'WORK' OR salary <= 100) );

and the query:
select *
from emp
where title = 'BOSS' and salary <= 100

can the DBMS spot the contradiction, and return an empty result set without even touching the data?
Let A = ( title = ’BOSS’ ), B = ( salary > 100 )

select * from emp where (A ^ ~B)
select * from emp where (A ^ ~B) ^ (~A v B)           # by cc1
select * from emp where (A ^ ~B ^ ~A) v (A ^ ~B ^ B)
select * from emp where (FALSE ^ ~B) v (A ^ FALSE)
select * from emp where (FALSE) v (FALSE)
select * from emp where (FALSE)

I have tried Postgres 13 (see fiddle above)
Seq Scan on emp  (cost=0.00..26.50 rows=2 width=46) (actual time=0.134..0.134 rows=0 loops=1)
  Filter: ((salary <= 100) AND ((title)::text = 'BOSS'::text))
  Rows Removed by Filter: 999
Planning Time: 0.312 ms
Execution Time: 0.149 ms

and Db2 11.5.4.0:
Optimized Statement:
-------------------
SELECT
  Q1.EMP_NO AS "EMP_NO",
  Q1.TITLE AS "TITLE",
  Q1.SALARY AS "SALARY"
FROM
  DB2INST1.EMP AS Q1
WHERE
  (Q1.SALARY <= 100) AND
  (Q1.TITLE = 'BOSS')

Access Plan:
-----------
        Total Cost:             51.8267
        Query Degree:           1

      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
     90.2441
     TBSCAN
     (   2)
     51.8267
        4
       |
       999
 TABLE: DB2INST1
       EMP
       Q1

but both fail to do so. Any other DBMS that can spot the contradiction and act upon it? This is of course more of an amusement than a real-world problem, but still.
Edit: The constraint suggested by @federico-razzoli in his answer does not work either:
alter table emp add constraint cc1
    check ((title = 'BOSS' and salary > 100) or
           (title = 'WORK' AND salary <= 100));

and the same query still causes table-access
 Rows
 RETURN
 (   1)
  Cost
   I/O
   |
 90.2441
 TBSCAN
 (   2)
 51.8267
    4
   |
   999

TABLE: DB2INST1
EMP
Q1

Your question is great, but it skips one step. You are assuming that the query planner (or optimiser, depending which terminology you prefer) can keep into account all CHECKs, merging them.
So you have:
check (title in ('BOSS','WORK'))

And:
CHECK ( (title <> 'BOSS' OR salary > 100) )
CHECK ( (title <> 'WORK' OR salary <= 100) )

And you assume that the planner should be able to know that:
CHECK ((title = 'BOSS' and salary > 100) or title = 'WORK' AND salary <= 100)

This could be the case or not.
In general, the question to your answer depends on the technology you are using. MySQL and MariaDB don't take into account CHECKs. Some other technologies probably do. You'll have to test the technologies you are interested in.


Import .bak file in Microsoft SQL Server 2008 Service Pack 3 I have a backup of my database as a TestDatabase.bak file but I am unable to restore it.

When I try to restore I receive the following error message in SSMS:

  RESTORE could not start database 'FieldopsDevDynamic07042013'

The error log shows:

  Database 'FieldopsDevDynamic07042013' cannot be started because some
  of the database functionality is not available in the current edition
  of SQL Server.

How can I restore this database?

Your backup is from a database that is using Enterprise Edition features. You are trying to restore it to an Express Edition server. That will not work.

The message in your error log is telling you exactly that:

  Database 'FieldopsDevDynamic07042013' cannot be started because some
  of the database functionality is not available in the current edition
  of SQL Server.

The error log even tells you which feature is causing the problem:

  Database 'FieldopsDevDynamic07042013' cannot be started in this
  edition of SQL Server because it contains a partition function...

Try restore to an Enterprise or Developer edition server. If it still fails update your question with the result of your attempt.


MySQL Stored Procedure - 0 rows affected I have got two tables i_mast and exp_mast. The structure of both tables are same.

+-----------------+
|id INT(5)        |
|name VARCHAR(30) |
|e_date DATE      |
+-----------------+

When the e_date matches current date i need to insert those rows to exp_mast and then delete those inserted rows from i_mast. I wrote the below stored procedure for this:

CREATE PROCEDURE is_expired()
BEGIN
  DECLARE id INT(5);
  DECLARE name VARCHAR(30);
  DECLARE e_date DATE;
  DECLARE n INT;
  DECLARE i INT DEFAULT 0;
  SET n = (SELECT COUNT(*) AS num FROM i_mast WHERE e_date = DATE_FORMAT(NOW(), CURDATE());
  WHILE i < n DO
  SELECT id FROM i_mast WHERE e_date = CURDATE() LIMIT 1 INTO id;
  SELECT name FROM i_mast WHERE e_date = CURDATE() LIMIT 1 INTO name;
  SELECT e_date FROM i_mast WHERE e_date = CURDATE() LIMIT 1 INTO e_date;
  INSERT INTO exp_mast VALUES(id,name,e_date);
  DELETE from i_mast WHERE e_date = CURDATE() LIMIT 1;
  SET i = i + 1;
  END WHILE;
END;

It help me create routine named is_expired(). I can see it under routines tab in phpmyadmin. After that i tried executing and it gave me this result 0 rows affected by the last statement inside the procedure.. I have got two records that needs to be deleted today from i_mast, while making a copy in exp_mast. But it's not having any effect. Please help me solve this issue.

Something like the following should work for you:

CREATE PROCEDURE is_expired()
BEGIN
  INSERT INTO exp_mast(id,name,e_date)
    SELECT id, name, e_date
    FROM i_mast
    WHERE e_date = CURDATE();

  DELETE from i_mast
  WHERE e_date = CURDATE();
END;


What is this upside-down silver key icon I inherited a SQL Server 2005 3rd party server and database to look into to be able to generate reports and dashboards on.
On one table, I found a new icon: a blue-silver upside-down key, alongside my other primary and foreign keys.
When I right-click it, the context menu gives me the option to "Script Constraint as", but when I try to "Modify" it, it opens the column design form (like primary keys), and yet, it appears in the "Indexes" section, not constraints.
Someone knows what it is called and what it is used for?

It's signifying a unique constraint. In SQL Server, a unique constraint is actually implemented as an index under the covers; this is why it appears under the Indexes node as well (I can't give you a good explanation of why it is not at least listed under the Constraints node, nor why they chose an upside down key instead of just a different color).

You should see a result here to validate that this is actually implemented as an index:

SELECT index_id, type_desc, is_unique_constraint
  FROM sys.indexes
  WHERE name = N'IX_tblCustIndex';

You said that you saw "Script Constraint As..." - if you followed through with that it would have provided you a script something like:

ALTER TABLE [dbo].[tblCustIndex] ADD UNIQUE NONCLUSTERED
(
  [some_column] ASC
) WITH (...a bunch of default options...)

But don't run it because this syntax does not use a name and it will just create a second, redundant unique constraint...

Here is a very simple repro, and proof that I get the upside-down key even with very basic syntax, on 2014:

CREATE TABLE dbo.foo(id INT UNIQUE);

   click to enlarge

Now, you can get a different icon if you create a unique index explicitly (and the only differences you'll see in sys.indexes are the name and the value of is_unique_constraint):

   click to enlarge

For other differences see this Stack Overflow answer.


Execution Plan Warnings cardinality xml data type I have the following statements:

-- 1st
DECLARE @AuditParameters XML = (
    SELECT
        1 AS AccountID,
        2 AS CategoryID,
        3 AS CategoryAttributeID,
        '4' AS SyncBatchGUID
    FOR XML PATH(N'Parameters'), ELEMENTS XSINIL
)

-

-- 2nd
DECLARE @AuditParameters XML = convert(xml, (
    SELECT
        1 AS AccountID,
        2 AS CategoryID,
        3 AS CategoryAttributeID,
        '4' AS SyncBatchGUID
    FOR XML PATH(N'Parameters'), ELEMENTS XSINIL
))

Both statements result in the same execution plan, however, they display the warning message

  Type conversion in expression (CONVERT_IMPLICIT(xml,[Expr1004],0))
  may affect "CardinalityEstimate" in query plan choice.

You may note I have tried to convert the second statement to XML and yet I get the same warning message.

How can I get rid of this warning message on the statement above?

Thank you

Add TYPE to return XML from the inner query rather than a string

DECLARE @AuditParameters XML = (
    SELECT
        1 AS AccountID,
        2 AS CategoryID,
        3 AS CategoryAttributeID,
        '4' AS SyncBatchGUID
    FOR XML PATH(N'Parameters'), ELEMENTS XSINIL, TYPE
)


Converting a composite primary key to composite unique, and adding an auto-incremented PK column Background

I have taken over responsibility for a table that, in the long run, will grow monolithic. The table is a very simple link table with 2 columns, call them col1 and col2, both being foreign keys to their respective tables, and also composite primary keys.

Before this table grows any further, I want to add an id column (NOT NULL AUTO-INCREMENT PRIMARY KEY), and instead let col1, col2 have a composite unique constraint.

The reasons for me wanting to do this lies in the business logic.

Question

Is there anything I should be particularly aware of before doing this? I'm going back and forth as to which order I should do it in.

Any insight appreciated!

Assumptions

The added column will of course increase required storage space. This is not an issue.

Bandwidth and overhead in terms of CRUDing towards the table is also accounted for.

Setup: MySQL / InnoDB

The why

The comments have been deleted, but there were some people asking why I wanted to make this change, and told me that it was a stupid change to make.

I'll answer it to prevent the XY problem (even though I don't think it's necessary in this case), and because the question has remained answerless thus far.

The reason I want to make this change, is because this link table will be linked to, from at least one other table, namely a change log.

Consider the change log table having the columns: id, col1 (FK), col2 (FK), user_id, action, timestamp. Now, if I want to have a changelog, I'm duplicating the entire original link table by virtue of having both col1 and col2 there.

At this point, it's an even trade. Saved the space in the link table, wasted the same amount of space in the change log table.

Now, who's to say I'll never need another table to link to it? That would be a pretty big assumption to make.

That smells a lot like a "many-to-many" relationship table.  Is it?

As for linking from a changelog -- space is not the issue; correctness is.  A change log should have exactly what existed at the time the log was taken.  What if the original table is updated?  If the table in question is changed for any reason, I think you do not want the change reflected in the existing changelog entry.  (Though, perhaps in a new changelog entry.)

If you would like to discuss your particular case further, please provide SHOW CREATE TABLE and a few queries that use the table.

Here's another tip: It is OK to do the following:

id INT UNSIGNED AUTO_INCREMENT,
...
PRIMARY KEY (...),
INDEX(id)

That keeps the original PK clustered, which might be beneficial, yet still allows for an AI key.

Another tip:  Don't say UNIQUE(foo) in place of INDEX(foo) unless there is some reason for doing the uniqueness constraint.  (Example:  INSERT ... ON DUPLICATE KEY ...)

Further discussion about many-to-many.


Count multiple tables as one count I have seen how to do multiple counts for different tables, but never how to combine them all into one.  I have a MySQL DB where I am running the following query:

SELECT characters.name, COUNT(*) AS wiki_unlocks
    FROM wiki_items
    INNER JOIN characters
    ON characters.character_id=wiki_items.character_id
    GROUP BY wiki_items.character_id
    ORDER BY wiki_unlocks DESC
    LIMIT 10;

This is giving me the following which is great:

name          wiki_unlocks
player1       2
player2       1

I want to get a combined count of all of the 'wiki_xxxx' tables.  For example I want 'wiki_items'(above) + 'wiki_armors' + 'wiki_weapons' + ...

Thanks for any help :D

I did not manage to delete my post from the phone. It's probably easiest to do each count as a subselect.

SELECT c.name
     , (select COUNT(i.character_id)
         From wiki_items i
         Where   c.character_id=i.character_id

       ) as  wiki_unlocks
     , (select COUNT(a.character_id)
         From wiki_armors a
         Where   c.character_id=a.character_id

       ) as  wiki_armors
     , (select COUNT(w.character_id)
         From wiki_weapons w
         Where   c.character_id=w.character_id

       ) as  wiki_weapons

FROM characters c


Determine Order Streak For a particular customer, I have data returned by a query that looks like this:

OrderDate   OrderCount
2020-01-12  0
2020-01-13  17
2020-01-14  22
2020-01-15  21
2020-01-16  30
2020-01-17  12
2020-01-18  0
2020-01-20  0
2020-01-21  49
2020-01-22  28
2020-01-23  4

These are the number of orders for each business day for that customer.  How do I efficiently determine their current "Streak" of orders?  For instance, if today was 2020-01-24, the answer would be 3, since they have ordered 3 days without missing a day.

Note that the days may not always be consecutive.  There are some non-business days that aren't returned by the query (ie 2020-01-19 in example).

Can anyone tell me how to do this?  Thanks in advance!

As mustaccio pointed out this is a "gaps and islands" problem:

declare @date date = '2020-01-16';

with ct as
(
    /* First you must detect the gaps, filtering those rows lesser than the selected date */
    select
        OrderDate,
        case when
            datediff(day,
                     coalesce(lag(OrderDate) over (order by OrderDate), OrderDate),
                     OrderDate
                     ) <> 1 then 1 else 0 end rst
    from
        orders
    where
        OrderDate <= @date
)
, ct2 as
  (
      /* Then set an island of each group */
      select
          OrderDate,
          sum(rst) over (order by OrderDate) as grp
      from
          ct
   )
   /* And finally count the affected rows */
   select top 1
       count(OrderDate) streak
   from
       ct2
   group by
       grp
   order by
       max(OrderDate) desc;

| streak |
| -----: |
|      5 |

db<>fiddle here


SQL Server Always Encrypted - Create an external table containing an encrypted column I'm trying to create an external table MyTable on server S2 that references the table MyTable from server S1.
The table MyTable contains a column encrypted using Always Encrypted. I'd like the column to be unencrypted when querying MyTable from S2.
Is it something that can be accomplished? I haven't found anything about it in the documentation (if someone knows where to find it, I'd be happy to just read the doc)... I've tried the naive approach bellow but obviously it doesn't work.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'myEncryptionPassword';
CREATE DATABASE SCOPED CREDENTIAL MyCred
WITH IDENTITY = 'myAdminAccount',
SECRET = 'mySuperStrongPassword';

CREATE EXTERNAL DATA SOURCE RemoteServerRef
WITH
(
    TYPE=RDBMS,
    LOCATION='somewhere.on.the.inter.net',
    DATABASE_NAME='myDatabase',
    CREDENTIAL= MyCred
);

CREATE EXTERNAL TABLE [dbo].[MyTable]
(
    [Id] [uniqueidentifier] NOT NULL,
    [Field2] [int] NOT NULL,
    [Field3] [datetime] NOT NULL,
    [Field4] [decimal](18, 2) NULL,
    [EncryptedField1] [nvarchar](128) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    [EncryptedField2] [int] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
)
WITH
(DATA_SOURCE = RemoteServerRef)

Does anybody know how to do that? As a bonus question, can someone explain to me what the CREATE MASTER KEY ENCRYPTION BY PASSWORD is used for (and does it have anything to do with the first problem)?

Thanks for your help!

First:

  Before creating a database scoped credential, the database must have a
  master key to protect the credential. For more information, see CREATE
  MASTER KEY (Transact-SQL).

CREATE DATABASE SCOPED CREDENTIAL

So the Database Master Key is used to encrypt the credential.

Second:

  I'd like the [AlwaysEncrypted] column to be unencrypted when querying MyTable from Elastic Database Query.

That is not possible.  AlwaysEncrypted is intended to have the data decrypted only on the client.  Even in the case where SQL Server is the client, there's no way to store the encryption key inside SQL Server and use it in the database engine.  There's no fundamental reason why this couldn't be implemented.  But the main use case for AlwaysEncrypted is storing data in such a way that SQL Server (and its administrators) cannot access this data.


Finding or confirming absence of corrupted data in MongoDB Background

I have a directory full of .gz files that were produced by mongodump on a mongod running MongoDB 3.4.x, which I am writing into a different instance running MongoDB 3.4.2 using mongorestore ... --numInsertionWorkersPerCollection 8 --drop --nsFrom ... --nsTo .... All but one are getting loaded properly. They all take between 1 and 3 hours to load the data, and then another 2-4 hours to rebuild indexes. The problematic file hangs before the step below, and I usually kill the process externally after about a day. I've reproduced the scenario 4 or 5 times.

finished restoring database.collection (N documents)

During this time the mongorestore process is still visible from the shell, but does not have a corresponding entry in db.currentOp(), and the size of the database is not increasing.

These errors were returned right before the log line "restoring indexes for collection database.collection from metadata":

2017-03-13T09:30:04.218-0400    demux namespaceHeader: {database collection true 726142452659775357}
2017-03-13T09:30:04.218-0400    demux checksum for namespace database.collection is correct (726142452659775357), 54599167880 bytes
2017-03-13T09:30:04.218-0400    demux finishing (err:corruption found in archive; I/O error reading length or terminator ( gzip: invalid header ))

I have found a couple reports of mongorestore hanging (e.g.), and some documentation of issues with /s in namespaces in older versions (e.g.), but I haven't found a description of quite my situation.

Question

Now I find myself with some indication that a file I have loaded may have been corrupt, or at least had a corrupt header. Issuing realistic queries to the collection in question and its indices returns results that look reasonable. How can I definitively detect or rule out "corruption" of this type? What indeed does "corrupt" mean in this context? Can I expect to find it in the database, or only within the zip file?

Update: At @mustaccio's suggestion I ran gzip -t on the problematic file and it reported "trailing garbage". Is this sufficient to pin the problem reported by MongoDB on a bad input and stop looking for corruption in the database?

demux finishing (err:corruption found in archive; I/O error reading length or terminator ( gzip: invalid header ))

This error message refers to processing of the gzip archive rather than the BSON data within. Your test with gzip -t implies that the contents are likely OK and the error may be due to ignorable padding at the end of the archive (see: gzip complains with trailing garbage ignored).

  All but one are getting loaded properly. That one reproducibly hangs (indefinitely)

If the problem is easily reproducible, I would raise a bug report in the MongoDB Jira TOOLS project including more details such as the specific versions of mongodump and mongorestore and command line parameters used as well as how long you waited. It seems reasonable for mongorestore to have some sort of timeout or error handling for this case rather than hanging indefinitely; I also wouldn't expect mongodump to create files that the same version of mongorestore cannot handle.

  Now I find myself with some indication that a file I have loaded may have been corrupt, or at least had a corrupt header. Issuing realistic queries to the collection in question and its indices returns results that look reasonable.
    How can I definitively detect or rule out "corruption" of this type?  What indeed does "corrupt" mean in this context? Can I expect to find it in the database, or only within the zip file?

It's unlikely that you would be able to restore corrupt BSON documents into MongoDB, but there may be some nuances depending on how/where data is corrupted. In most cases invalid data will trigger an exception/assertion when the server attempts to read the BSON, but valid BSON does not necessarily mean that the field contents are as expected.

To be entirely certain you would have to compare the document counts or checksums of the restored data against the original data (assuming you still have a copy other than the gzip archive).


MySQL 1366 Error on Replication Slave I've got two pairs of servers (each identical hardware and OS) with replication running on both pairs.
Running MySQL 5.5.34 on Windows Server 2008 R2.

I'm trying to track down a problem that has occurred twice now on on of the pairs.

I get a 1366 error "Incorrect integer value '\x00' for column Flag at row 1" for the following call:

UPDATE mytable SET Flag = 0 where Id = 10

When I issue the show slave status command.

On the other pair I don't ever get this error when replicating.

The type of Flag is tinyint(1).

I haven't been able to identify any differences between the two pairs yet.

Ideas?

Update:
I posted an answer to this below.  I believe it has something to do with the serialization of the value when sent from the master to the slave.  The representation of the BIT value when replicated is not in a form that can be applied to a column of type TINYINT.

In this case the stored procedure took a flag value as a BIT and used that to update a TINYINT column.  While we can agree that those datatypes should match, it is the case that on the master a call to that stored procedure works just fine, meaning the BIT parameter's value is properly converted over to the TINYINT column.

An answer:

After looking into this further, I determined that the call:

UPDATE mytable SET Flag = 0 where Id = 10

was being called also in a stored procedure which took a parameter of type BIT and did something like so:

UPDATE mytable SET Flag = theFlagValue where Id = theId;

While this worked fine on the master - the mytable would be updated with the correct flag value for any rows matching the Id - it wouldn't work on the slave.

My thought on this is that it involves how the data is serialized when sent to the slave.  That type mismatch was fine on the master but not on the slave.

I was able to modify that stored procedure to take a TINYINT instead and it works flawlessly now.


Store a revenue based dataset in a relational database I have several revenue based datasets with below structure:

Subject                 2012   2013    2014    2015
online marketing         54      50     60      80
website ads             900     850    320     250
mobile ads               60      80    120     130
video ads                30      40     50      80

there are over 10 tables (example: website_1 / website_2 / website_3). I was planning to change the dataset to below structure so I can join tables and do comparison of data based on year.

Subject                 revenue   year
online marketing        54        2012
online marketing        50        2013
online marketing        60        2014
online marketing        80        2015
website ads             900       2012
....
mobile ads              60        2012
....
video ads
...

I dont think the above structure is efficient.
what would be the best way to store such data in a relational database?

Why do you believe that the second design is not efficient? It would be the design that I would recommend to you. It allows the data set to be scaled to add more subjects and more years without needing to adjust the schema.

To extend the normalization, place the subjects within their own table and reference that from your main table. This will reduce duplicate names in your main table and also reduce the space required to store your data.

Depending on whether you can or not, you could also reduce your table count down to 3 by adding a column to your main tables called website, then create a new table to hold the website list and reference this again from your main table.

You would then end up with a structure similar to this:

create table "websites"
(
  id int not null primary key,
  name varcher(64) not null unique
);
create table "subjects"
(
  id int not null primary key,
  name varcher(100) not null unique
);
create table "revenue"
(
  id int not null primary key,
  website int not null,
  subject int not null,
  year smallint not null,
  value int not null,
  constraint uk_revenue unique (website, subject, year),
  constraint fk_revenue_website foreign key (website) references website (id),
  constraint fk_revenue_subject foreign key (subject) references subject (id)
);

This creates a table for the website, a table for subjects and one for the revenue. The revenue table contains references to the former tables and also creates a unique constraint across the combination of the website, subject and year to prevent duplicate entries.

Naturally, normalization makes query definitions larger (as you now need to join to other tables) but this is trivial really and the ability to scale out to multiple websites, subjects and years easily is in my opinion worth the extra effort of writing a longer query.

After time, should the data set grow to a point whereby querying it becomes very slow, you can either archive off data that you don't need anymore or warehouse it in a denormalized fashion.


Is the data fetched by an explicit cursor immutable in PL/SQL? This seems like an obvious question, but Googling has so far failed me...

Say I have a view which delineates updates that I need to apply and, to this end, I have an SP that opens the view as a cursor, iterates through each row and makes the appropriate change. The thing is, the updates made by the SP will alter the contents of the view (e.g., once the inconsistency has been dealt with, the row will disappear from the view).

Will this be reflected in the open cursor (in the SP), or is the data that it fetches fixed when it is first opened?

What I've read suggests that the cursor data is immutable once it's been opened -- which is good -- however, I've yet to find a straight answer.

Oracle will provide a read-consistent view of the data.  Assuming that you're using the default transaction isolation level of read committed, the data that a query will return is fixed at the point in time that the cursor is opened (whether the cursor is implicit or explicit is irrelevant).

The only exception to this is that PL/SQL that is called by the query (i.e. stored functions) will see the data as it exists at the point in time that the function is called not at the point that the cursor was opened.


Google datastore modeling help I have a very simple requirement. I have an application where users log in, add/remove/view friends, and can send/receive (also view the message you sent and received) a message (message is more like an email, not a real time chat) from a friend. When they receive the message, the application needs to mark the message as 'read'. The messages must also come ordered by newest to oldest.

My thoughts:

KINDS:

USER

username <String> (ID)
password <String>
friends <Comma Separated String of usernames> (will contain a max of 100 friends).

MESSAGE

msg_id <String concating [from+to+timestamp]> (ID)
from <String> (indexed)
to <String> (indexed)
timestamp (indexed)
msg <String>
hasRead <boolean>

1) User would login using their username and password

2) User could get friends by getting their USER entity based on their username

3) User could add/remove friends by getting the entity of user1 and user2 and either adding or removing friend via transaction to make sure they are consistent.

4) User could get all the message they have sent by using indexing the the 'from' attribute (limit of 10 message per request). The same could be done to view all the messages they have received by using the 'to' attribute. When the message has been seen for the first time I would go to the message (1 get) and update the entity (1 write + (4 writes x 3) = 13 writes to update the entity).

My major concern - If a user gets 10 messages, this will require 10 get requests plus if all 10 messages are new I will need to update each entity and if all of them are new that is (10 x 14) 140 writes. Then if I get another 10 message for this user the same process and this could all add up very quickly.

I then thought of creating an entity to store all the sent/received messages in a string for a user inside of a single entity:

MESSAGE
user_entitynum1 <String> (ID)
messages <String looking like this: msg1_fromuser2_unread_timestamp, msg2_touser8_read_timestamp, msg3_touser5_unread_timestamp, etc>

This way I could store all the message (under 1mb) inside of this one entity, but I would have to keep track of which entity each user is at (if the user exceeds the 1mb limit I will have to create a new entity).  This proved to also not be too efficient because if I send a message to perform 2 gets to see which message entity I am currently at and which message entity they are currently at. From there I must now use another 2 reads to get those entities and another 4 writes to update them both (considering I do not need to create another entity if it is full).

I was looking for any ideas to accomplish what I need in a more efficient way. I have nothing implemented yet so I  am open to ANY ideas. My main concern is for this to be efficient,

Cheers!

As per my understanding, your business rules (requirements) are as follows.

Each User can have one or many User Friends.
Each User Friend must belongs to one User.
Each User can send one or many Messages.
Each Message is sent by one User.

By following the above mentioned business rules, your database design may looks like as follows,

USER
{
 user_id
}

PASSWORD
{
password_id,
user_id
}

USER_FRIENDS
{
userfriend_id,
user_id,
added_by,
removed_by
}

MESSAGE
{
message_id,
message,
sent_by,
recieved_by,
is_read
}

If you need to track the user history, add the required data fields with domains such as timestamp, date, etc...at the appropriate entities (message history, login history, friends login, add/remove history). For calculating storage space and setting limit for users, you can done that using programing. I hope this will give you an idea.


Replacing the MS-Acccess frontend for accessing linked tables with SQL Server? Is it possible to use SQL Server / SQL Server Management Studio to do queries across linked tables the way that you would do them in MS-Access?

The MS-Access front-end is frustrating and time consuming to say the least, which has led me to search for alternatives.  I started using SQuirreL SQL, and was quite pleased with it, but it doesn't have the linked table functionality so you're somewhat limited on the queries that can be run.

Also, can you provide me with suggestion for a tutorial or book on how to set something like that up?

SQL Server Management Studio allows you to create multiple versions of queries where you can see the SQL text and results simultaneously.

You can use dotted-notation to refer to tables in separate databases on the same server like:

SELECT * FROM database.schema.table

If you need to query data from multiple servers, you can create linked servers in SQL Server that provide all the capabilities of an Access front-end.  This allows you to use 4-part dotted-notation like:

SELECT * FROM server.database.schema.table

For example, this code creates 2 Databases on the local SQL Server, with 2 tables that contain a single record each.  The SELECT at the end joins data from the 2 tables into a single output:

CREATE DATABASE Test1 ON PRIMARY
    (NAME = 'Test1_Data'
        , FILENAME = 'C:\Temp\Test1_data.mdf'
        , SIZE = 10MB
        , MAXSIZE = UNLIMITED
        , FILEGROWTH = 10MB
    )
LOG ON
    (NAME = 'Test1_Log'
        , FILENAME = 'C:\Temp\Test1_log.mdf'
        , SIZE = 10MB
        , MAXSIZE = UNLIMITED
        , FILEGROWTH = 10MB
);

CREATE DATABASE Test2 ON PRIMARY
    (NAME = 'Test2_Data'
        , FILENAME = 'C:\Temp\Test2_data.mdf'
        , SIZE = 10MB
        , MAXSIZE = UNLIMITED
        , FILEGROWTH = 10MB
    )
LOG ON
    (NAME = 'Test2_Log'
        , FILENAME = 'C:\Temp\Test2_log.mdf'
        , SIZE = 10MB
        , MAXSIZE = UNLIMITED
        , FILEGROWTH = 10MB
);
GO

USE Test1;
GO
CREATE TABLE TestTable1
(
    ID INT NOT NULL PRIMARY KEY CONSTRAINT PK_TestTable1 IDENTITY(1,1)
    , TestText nvarchar(255)
);

INSERT INTO TestTable1 (TestText) VALUES ('This is table 1 in database 1');

USE Test2;
GO
CREATE TABLE TestTable2
(
    ID INT NOT NULL PRIMARY KEY CONSTRAINT PK_TestTable2 IDENTITY(1,1)
    , TestText nvarchar(255)
);

INSERT INTO TestTable2 (TestText) VALUES ('This is table 2 in database 2');

SELECT * FROM Test1.dbo.TestTable1
UNION ALL
SELECT * FROM Test2.dbo.TestTable2;

The output is:

ID  TestText
1   This is table 1 in database 1
1   This is table 2 in database 2


Replicaton - The current transaction cannot be committed and cannot be rolled back to a savepoint After creating a replication publication, I go ahead and create a subscription using the script below:

-----------------BEGIN: Script to be run at Publisher 'MY_PUBLISHER_SERVER'--------------
use [MY_PUBLICATION_DB]
exec sp_addsubscription
 @publication = N'MY_PUBLICATION',
 @subscriber = N'MY_SUBSCRIBER_SERVER',
 @destination_db = N'ORCA_Repl_Sub',
 @subscription_type = N'Push',
 @sync_type = N'automatic',
 @article = N'all',
 @update_mode = N'read only',
 @subscriber_type = 0

exec sp_addpushsubscription_agent
 @publication = N'MY_PUBLICATION',
 @subscriber = N'MY_SUBSCRIBER_SERVER',
 @subscriber_db = N'ORCA_Repl_Sub',
 @job_login = N'MY_COMPANY\sqlrpl',
 @job_password = N'MYP455w0rd',
 @subscriber_security_mode = 1,
 @frequency_type = 64,
 @frequency_interval = 0,
 @frequency_relative_interval = 0,
 @frequency_recurrence_factor = 0,
 @frequency_subday = 0,
 @frequency_subday_interval = 0,
 @active_start_time_of_day = 0,
 @active_end_time_of_day = 235959,
 @active_start_date = 20190423,
 @active_end_date = 99991231,
 @enabled_for_syncmgr = N'False',
 @dts_package_location = N'Distributor'
GO
--------------END: Script to be run at Publisher 'MY_PUBLISHER_SERVER'-----------------

to my surprise I get all the following errors when running the second procedure:

Msg 50000, Level 16, State 1, Procedure msdb.dbo.sp_add_jobstep_internal, Line 255 [Batch Start Line 14]
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3931, Level 16, State 1, Procedure sys.sp_MSadd_repl_job_unsafe, Line 376 [Batch Start Line 14]
The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

Basically Replication Scripts Stopped Working..

How could be a solution for this?
where to find more information about these errors?

I re-connected to sql-server using the credentials of my replication agent - which is a sysadmin on my publication server, and db_owner on my subscription database at my subscription server.

1 - right click on the ssms icon

2 - on the SSMS option above, press shift + right click and choose the Run as a different user

3 - add the credentials of the different user - in my case it was my replication agent account

and it all worked fine after that, as you can see on the picture below:


PostgreSQL 9.5 won't start after Windows 10 fall update I've installed Windows 10 Fall update (1709) and now my PostgreSQL 9.5 server won't start. It worked yesterday before the update and I haven't made any changes to the configuration.

I've checked event viewer and found following error messages:

2017-10-19 11:32:32 CEST LOG:  invalid value for parameter "lc_monetary": "Czech_Czech Republic.1250"
2017-10-19 11:32:32 CEST LOG:  invalid value for parameter "lc_numeric": "Czech_Czech Republic.1250"
2017-10-19 11:32:32 CEST LOG:  invalid value for parameter "lc_time": "Czech_Czech Republic.1250"
2017-10-19 11:32:32 CEST FATAL:  configuration file "C:/Program Files/PostgreSQL/9.5/data/postgresql.conf" contains errors

It seems like Microsoft changed locale name with Fall update, I couldn't find any list of available locale names so I decided to install Postgres 10 and it confirmed my suspicion, the postgresql.conf in Postgres 10 now reads:

# These settings are initialized by initdb, but they can be changed.
lc_messages = 'Czech_Czechia.1250'          # locale for system error message
                # strings
lc_monetary = 'Czech_Czechia.1250'          # locale for monetary formatting
lc_numeric = 'Czech_Czechia.1250'           # locale for number formatting
lc_time = 'Czech_Czechia.1250'              # locale for time formatting

I changed the config values for PostgreSQL 9.5 server to 'Czech_Czechia.1250' and it started ok, but the problem is that now I can't connect to any database, pgAdmin III gives me following error:

Is there a way to get the data back? I can't create dump or run pg_upgrade since the databases now have invalid locale and I can't connect to them. Maybe there is a way to change the locale of the database manually? In theory, it shouldn't cause any problems since its only different name for the same encoding.

Thanks to suggestion from Daniel Vérité I was able to fix it without dumping the entire db server. My understanding of locales on Windows is very limited but from what I've learned it seems like Microsoft changed region name of czech locale from "Czech Republic" to "Czechia" during Fall Creators update (presumably so it conforms to ISO 3166-1).

Postgres somehow loads locale by combination of language name and region name, so databases created before this change cannot be connected to since the combination of language and region is no longer valid.

So to fix it, I downloaded Locale Builder 2.0 from Microsoft, and then:

Created new locale based on Czech locale (cs-CZ), kept the same locale name (cs-CZ)
Changed region name to "Czech Republic" (from "Czechia")
Created installer by selecting "Build" -> "Build Locale Installer"
Installed the newly created locale by running .msi file created in previous step
Restarted the PostgreSQL server, this time it started without problem

Now I can run PostgreSQL 9.5 with 'Czech_Czech Republic.1250' and PostgreSQL 10 with 'Czech_Czechia.1250' and they both work.

I'm not sure whether it's ok to create the locale with same locale name (cs-CZ), but it doesn't seem to cause any problems, maybe someone can elaborate more.


Create DB2 on networked drive but managing from Windows I'm relatively new to DB world and DB2 is very new. Below is what I'm trying to achieve:

Install DB2 express C and IBM data studio on windows (with Fixed IP)
create/share/host DB2 on a shared/networked drive used within an organisation having its own fixed IP. its not a server. its netgear NAS.

I want this database to be shared in my organisation.

I know its relatively easy if I host it on my own windows pc (ie Localhost) but I would want to host on a network drive.

Is it possible? If so, where can I find relevant information?

As Lennart says, you cannot access the data without the engine. You must install the engine on an OS somewhere. Can this be a central server? Most definitely. That is how the businesses I have worked for have set up there databases, regardless of whether they were DB2 LUW, DB2 for i, or SQL Server.

So you will want to pick the OS your DB2 installs on. IMHO, I'd stay away from Windows. That pushes you to Linux or AIX. My experience was with AIX and Windows. But I have heard good things about DB2 on Linux.

Anyway....you will need to install DB2 on a central server. Data Studio is great for access and/or some management. The DB2 Express-C will be great for you to play around with most things locally before moving them to the server. Keep in mind, not every feature is available on Express-C.


MAX and MIN for each car model I have a problem with a query. I have a db with car and their spec, every model has a different car related to that model with its price. I want to create a query that gives me the min and max price for each models.

something like this:

+--------+----------+
| model  | price    |
+--------+----------+
| golf   | 4000     |
| golf   | 6000     |
| golf   | 10000    |
| panda  | 3000     |
| panda  | 5000     |
| panda  | 7000     |
+--------+----------+

the query give me this:

+--------+----------+
| model  | price    |
+--------+----------+
| golf   | 4000     |
| golf   | 10000    |
| panda  | 3000     |
| panda  | 7000     |
+--------+----------+

Can you help me?

You can simple use UNION ALL:

SELECT model, MIN(price) price
FROM YourTable
GROUP BY model
UNION ALL
SELECT model, MAX(price) price
FROM YourTable
GROUP BY model;


Alternative to Dynamic SQL inside trigger? My database has the following tables:

    TAG
    ----------------------
    | tag_id | tag_name  |
    ----------------------

    TAG_VALUE: Stores values associated to each tag
    ----------------------------------------
    | tag_id | insertion_timestamp | value |
    ----------------------------------------

    ALARM: Defines alarms for each tag
    -------------------------------------
    | alarm_id | tag_id | function_name |
    -------------------------------------

    ALARM ACTIVATION: Stores information regarding each time the alarms were triggered
    -----------------------------------------------------
    | alarm_id | activation_timestamp | activation_value|
    -----------------------------------------------------

So, everytime a new value is inserted into TAG_VALUE, I need to check if that new value triggers any alarm associated to it's tag. Since there is no common criteria between alarms, I'm just storing a function name, which will be used later on to determine if the alarm should be triggered or not

TAG_VALUE has the following AFTER_INSERT trigger:

CREATE DEFINER=`root`@`localhost` TRIGGER `mydb`.`tag_value_AFTER_INSERT` AFTER INSERT ON `tag_value` FOR EACH ROW
BEGIN
call sp_alarm_handler(NEW.tag_value, NEW.tag_id);
END

And sp_alarm_handler is coded as follows:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_alarm_handler`(IN tag_value VARCHAR(255), IN value_tag_id INT(11))
    BEGIN

    DECLARE exit_loop BOOLEAN;
    DECLARE v_alarm_id INT(11);
    DECLARE function_name VARCHAR(255);
    DECLARE value_triggers_alarm TINYINT(1);

    DECLARE custom_alarm_cur CURSOR FOR
    Select alarm_id, function_name From vw_custom_alarms where tag_id = value_tag_id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;

    OPEN custom_alarm_cur;

    custom_alarm_lp: LOOP

        FETCH custom_alarm_cur into v_alarm_id, function_name;

        IF exit_loop THEN
        leave custom_alarm_lp;
        END IF;

        #************************************************
        #*********HERE'S THE DYNAMIC SQL PIECE'**********
        #************************************************
        set @query = CONCAT('Select ',function_name,'(',tag_value,')', 'into @value_triggers_alarm');
        PREPARE stmt FROM @query;
        Execute stmt;

        IF fn_is_alarm_active(v_alarm_id) = 0 THEN

            If @value_triggers_alarm = 1 THEN
                    INSERT INTO alarm_activation(alarm_id, activation_timestamp, activation_value)
                    VALUES (v_alarm_id, NOW(), tag_value);
            end if;

        ELSE IF @value_triggers_alarm = 0 THEN
            call sp_deactivate_alarm(v_alarm_id);

            END IF;
        END IF;

    END LOOP custom_alarm_lp;
    Close custom_alarm_cur;

    END

This gives me an ERROR 1336: 1336: Dynamic SQL is not allowed in stored function or trigger
Is there any workaround in my case?

If the list things to check is constant, then spell them out rather than constructing the tests. (not viable)

But, presumably that is not the case...

Plan A: Replace the INSERT that needs to call the trigger with a PROCEDURE call.  The routine contains the INSERT plus the rest of the code you presented.

Plan B: As with Plan A, but do it in application code.

Note:  Either A or B would be wrapped in BEGIN...COMMIT to make it 'atomic'.

Bottom Line:  Triggers can't do everything.

Enforcement of A (I think this will prevent other from bypassing the stored proc.)

CREATE USER special@... ...  -- a new user
GRANT INSERT ON db.tbl TO special@...  -- let it get in
The stored proc would be SECURITY special@...  -- to run proc as "special" so it can do the INSERTs
Remove INSERT PRIVILEGE for that table from others.  (This gets messy, or maybe unnecessary.)  (It may help if the table is in a separate database that no one currently has access to.)
(My gut says there is one more step. I'll leave it as a 'exercise for the reader'.)


Large Number of ALTER TABLE Statements I am managing a database in SQL Server 2008.  Across about 15 tables, I have 1600 VARCHAR columns that need to be changed to NVARCHAR.

I was able to generate all the appropriate ALTER TABLE statements with a query, but now I am faced with the problem of running them.

My approach has been to just paste the 1600 ALTER TABLE statements and try to run them, however this seems to be too big to run practically. If I step away from my desk for too long, I am disconnected from SQL Server and the process stops (no columns are changed).

Is there a smarter way to run this?

Realizing that this is a complete redesign of your intent but why not script out the change for each table?  Ie create a new table, copy the data over, re-create all indexes, security etc.

The easiest way to do this would be to use the designer, make your changes, don't hit save, but instead right click on the background of the designer and request "generate change script".  Copy that out and you can then make changes to it as needed.  That way you only have 15 scripts to run.

I did this recently for a particularly large table (~700gb), using the script to create the new table and using an SSIS package to copy the data.  Then I ran the remainder of the script to generate the indexes etc.  This can be surprisingly fast depending on your needs.

Now if you don't want to do it that way you do have a couple of other options.

You could put GO statements and use PRINT to print a status between each ALTER.  Then when you run your script you can tell where it failed.  It will only roll back the last statement you ran and you can run from that point forward.

Last option I can think of is to remote to the server itself (not considered a best practice) and run the script there.  That way even if you lose your connection to the server the script will continue to run regardless.


failed logon trigger blocking access into SQL Server instance I wanted to create a trigger to record all the names of logins(who logon into system) in ServerLogonRecords table(all the columns are nullable) using LOGON trigger. I get information using eventdata() system function and convert the result into nvarchar type.

 create trigger tr_recorder
 on all server
 for logon
 as
 begin
 declare @var  nvarchar(70)
 set @var = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(70)')
 insert into ServerLogonRecords values(@var)
 end

Now I can't even login using my privileged account because all logins fail.
I want to know why do logins fail (is my trigger doing something wrong?). And any suggestions how to fix this in order to be able to login again into the instance will be very appreciated.

Thanks.

And any suggestions how to fix this in order to be able to login again
  into the instance will be very appreciated.

You should disable your trigger:

disable trigger tr_recorder on all server;

You should be able to do it without problem if you log in as sysadmin because if your trigger is exactly as you posted, it will be executed without errors by sysadmin.

Or you should do it using DAC, i.e. you should connect to server using -A, in case your trigger is different and even sysadmin cannot login now.

Here you can find step to step instruction with screenshots:
SQL Server: Disable Logon Trigger Using DAC to Resolve Login Problem.

  I want to know why do logins fail (is my trigger doing something
  wrong?).

Your problem can be the following: your trigger tries to insert into a table in master database but usually users don't have any permission in master. Unless the login is sysadmin or is mapped to master explicitely, it is guest in master and has no permission on any user table in master.

To fix the issue you can use execute as clause in your logon trigger, this way the trigger will be executed with the permissions of the login that you put in execute as clause.

One osservation: if all you wanted to do is to record every successful login to server you can do it by changing login auditing:

This way you'll get all the successful logins in SQL Server errorlog.


WordPress database error Table './databasename/wep_options' is marked as crashed and should be repaired for query Please help me my mysql is keep crashing again and again and generating following error i am out of any clue what will be the reason for this issue

  WordPress database error Table './databasename/wep_options' is marked as crashed and should be repaired for query SELECT option_value FROM wep_options WHERE option_name = '_otgs_twig_cache_disabled' LIMIT 1 made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/RIVIERA/404.php'), get_header, locate_template, load_template, require_once('/themes/RIVIERA/header.php'), do_action('icl_language_selector'), WP_Hook->do_action, WP_Hook->apply_filters, call_user_func_array, WPML_LS_Actions->callback, WPML_LS_Public_API->render, WPML_LS_Render->render, WPML_LS_Template->get_html, WPML_Templates_Factory->get_view, WPML_Templates_Factory->maybe_init_twig, WPML_Templates_Factory->is_caching_enabled, get_option, referer: http://www.jjweem.com/A/?C-1-348.Html

I think you are using MyISAM Engine

Don't use MyISAM for any case. It's becoming deprecated.

To get rid of this problem first repair your table.

Do the following:

REPAIR table wep_options;

Depending on the table size, this command takes time.

If your table is big and you can stop temporary mysql, I think myisamchk command is faster than repair command.

Check out end of this topic:
[https://mariadb.com/kb/en/mariadb/myisamchk/#repairing-tables][1]

After that To avoid repeating crash convert all tables to InnoDB Engine.

Example:

ALTER TABLE your_tables ENGINE=InnoDB;