Database slowdown at random times using mirroing Our web application suffers DB query timeouts a few times a day at random times. Can you help to work out the cause, e.g. deadlocks, mirroring (disk or network), etc?

We have two identical database servers (and a third witness server) with MSSQL 2008 R2 mirroring in high safety mode with automatic failover. Two DBs are mirrored and each server is principal in one so in the event of a failure, both DBs will run from one server. As far as I can see, the problem only occurs on one of these two databases.

Using performance monitor, I can see the "Log Harden Time (ms)" on the principal server spikes to 212,301.206ms at the exact time a timeout occurred (07:30 today). The average from a 24hr period is 22.712ms.

What else should I be looking at to determine the cause of this slowdown?

Database mirroring involves two copies of a single database that reside on different computers. At any given time, only one copy of the database is available to clients. This copy is known as the principal database. Unlike log shipping which works by applying full transaction log backups to a warm standby database, database mirroring works by transferring and applying a stream of database log records from the principal database to the other copy of the database (the mirror database). Database mirroring applies every database modification that is made on the principal database to the mirror database. This includes data changes as well as changes to the physical and logical structures in the database, such as database files, tables, and indexes.

I know we have two of the three issues listed above, but if it cannot communicate with the mirror it just holds the transactions in the transaction log until they are able to be applied.  It doesn't use any form of locking  or modification of the data since it is being read from the log file.

So look at Log generation rate, network throughput, and I/O throughput, influence the deployment of database mirroring.

References:
http://msdn.microsoft.com/en-us/library/ms189852.aspx


Effective duplication of DELETE ... LIMIT in Pg I have an unusual problem: I need to delete a certain number records from a table, of a particular type; in certain non-compliant DBs it would be easy to accomplish this with DELETE FROM sometable WHERE COND LIMIT X, but in Postgresql this is clearly not an option.

In some cases I do need to delete the last N records; in other cases it doesn't matter as long as they match the overall criteria. Other than primary key and timestamp, the records are identical.

So what is an (elegant) means of duplicating this functionality? It was suggested that I could do a join on ctid, but that doesn't strike me as an especially "nice" solution; is there something cleaner--and more portable--that I can do instead?

It's easy with window functions. Say you want to delete the last @N rows when ordered by some_column. You assign a ROW_NUMBER() with a CTE or a derived table and then DELETE:

WITH del AS
  ( SELECT pk,
           ROW_NUMBER() OVER (ORDER BY some_column DESC) AS rn
    FROM theTable
    WHERE <conditions>
  )
DELETE FROM theTable
USING del
WHERE theTable.pk = del.pk         --- the PRIMARY KEY of the table
  AND del.rn <= @N ;               --- your parameter here

or without the USING, in a more standard way:

WITH del AS
  ( SELECT pk,
           ROW_NUMBER() OVER (ORDER BY some_column DESC) AS rn
    FROM theTable
    WHERE <conditions>
  )
DELETE FROM theTable
WHERE pk IN
      ( SELECT pk FROM del WHERE rn <= @N ) ;

Test at SQLFiddle.com


Declaratively select an entity requiring relations from all (dynamic number of) related entities I am trying to construct a where clause that requires an X number of children to have a relation to a specific parent with a N-to-N relationship. Something similar to "Get all receipts where these products were sold together."
An example scenario is easier to explain:

DECLARE @Nodes TABLE(
  Id INT
);

DECLARE @Arc TABLE(
  Id INT IDENTITY(1,1),
  Source INT, -- FK @Nodes.Id
  Dest INT    -- FK @Nodes.Id
);

INSERT INTO @Nodes (Id) VALUES (1),(2),(3),(4);

INSERT INTO @Arc (Source, Dest) VALUES
   (1, 2)
  ,(1, 3)
  ,(1, 3)
  ,(2, 1)
  ,(2, 3)
  ,(2, 4);

In the SP, the input is preprocessed into a table like this:

DECLARE @InputConnectedNodes TABLE ( Id INT );
INSERT INTO @InputConnectedNodes (Id) VALUES (3),(4); -- not a fixed number of nodes

In this example we want all nodes that have an arc to nodes 3 and 4 (only node 2 in the above example).
Currently we have this:

SELECT DISTINCT Source
    FROM @Arc
    WHERE Dest IN (SELECT Id FROM @InputConnectedNodes);

Then we have some ugly procedural code that loops through the input and (nested) output checking for all relationships. I'd like to replace it with something declarative. The best I came up with so far is this:

SELECT Source
    FROM @Arc
    WHERE Dest IN (SELECT Id FROM @InputConnectedNodes)
    GROUP BY Source
    HAVING COUNT(Source) = (SELECT COUNT(*) FROM @InputConnectedNodes)

But it will fail when nodes have multiple (identical) arcs like (1->3) in my example.

I could also solve this by dynamically generating a where clause, but I'd prefer a fully declarative solution.

I suggest loosening your resolve for a fully declarative solution. There may not be a declarative way to solve your problem, and there are many cases where dynamic SQL is a much better solution in terms of performance - even if it lacks readability.

Dynamic SQL gives the optimizer the opportunity to use different plans optimized for different parameters instead of having to come up with a single plan that solves all permutations well. The latter is quite difficult to achieve.

Your problem is called relational-division. See questions in that tag and:

Divided We Stand: The SQL of Relational Division by Joe Celko; and
How to filter SQL results in a has-many-through relation on Stack Overflow.

Most of the Stack Overflow solutions would work as they are, or with minor modifications for SQL Server. Note how the more complicated queries, with multiple joins or EXISTS subqueries (that would need dynamically produced code for the arbitrary cases) are more efficient than the GROUP BY query.


MySQL Event Help - Update column value for each row every 15 minutes Okay so if I have a login database:

id | email | pass | attempts
----------------------------
 1 |  ""   |  ""  |    4
 2 |  ""   |  ""  |    2

Attempts marks the number of attempts made at logging in, per user. So user 1 has attempted to log in 4 times and user 2 has attempted to log in 2 times.

I was wondering if there was an event that I could add to my login database, which would reset all user attempts to 0? So after the reset, the table would look like this:

id | email | pass | attempts
----------------------------
 1 |  ""   |  ""  |    0
 2 |  ""   |  ""  |    0

Thanks !

Each hour:

CREATE EVENT reset ON SCHEDULE EVERY 1 HOUR DO UPDATE admin set aLoginAttempts = 0


Wrong results from joining two CTEs I've had some help to create a query to solve a problem I had and the solution seemed to be to create two CTE's and then join and query them.

If I run the two CTE's as single queries each table contains the expected data without fail but the join produces less results than it should and duplicates and even rows with three or more duplicates.

;WITH CTE1 AS
(
SELECT FContainerHylla.Name, FContainerPlats.HyllId,
FContainerPlats.x, FContainerPlats.y,
    FContainerPlats.enable, FContainerHylla.Type,
ISNULL((SELECT MIN(fcontainer.id) AS id
FROM FContainer
WHERE (PlatsId = FContainerPlats.id)), 0) AS FcId
FROM FContainerPlats
INNER JOIN FContainerHylla ON FContainerPlats.HyllId =

    FContainerHylla.Id
WHERE (FContainerHylla.Type IN (1, 6, 10))
    ),

    CTE2 AS
    (
SELECT FContainer.id AS SubID, produkter.Produktnamn AS Produktnamn
from FContainer
INNER JOIN Stämplingar ON FContainer.id = Stämplingar.ID
INNER JOIN Tempo ON Stämplingar.temponr = Tempo.temponr
INNER JOIN Produkter ON Tempo.produktnr = Produkter.Produktnr
    )

    SELECT CTE1.*, CTE2.Produktnamn
    FROM CTE1
    INNER JOIN CTE2 ON CTE2.SubID = CTE1.FcId
    ORDER BY CTE1.HyllId, CTE1.x, CTE1.y

CTE1 produces a table which is about 2600 rows long and I've verified that it's correct. CTE2 is about 7000 records long and also correct. The column fcontainer.id is a unique key.

As I said when these two tables are joined I get the wrong results. I had expected to get a something similar to the results from CTE1 but with the field produkter.produktnamn added but which I do but instead of the 2500 results from CTE1 I get something around 1600 rows with duplicates.

Create Table For Fcontainer and Stämplingar:

CREATE TABLE [dbo].[FContainer] (
[id]              NUMERIC (18) IDENTITY (1, 1) NOT NULL,
[PlatsId]         NUMERIC (18) CONSTRAINT [DF_FPall_InStoreage] DEFAULT
((0)) NOT NULL,
[Halv]            BIT          NOT NULL,
[FlaggId]         SMALLINT     NOT NULL,
[Kragar]          SMALLINT     NOT NULL,
[TmpFifo]         NUMERIC (18) NULL,
[PackTempoTypeNr] SMALLINT     NULL,
[Invent]          VARCHAR (50) NULL,
CONSTRAINT [PK_FPall] PRIMARY KEY CLUSTERED ([id] ASC) WITH (FILLFACTOR =
90)
);

CREATE TABLE [dbo].[Stämplingar] (
[Stämplingsnr]   NUMERIC (18) IDENTITY (1, 1) NOT NULL,
[temponr]        NUMERIC (18) NULL,
[Tidpunkt]       DATETIME     NULL,
[antal]          NUMERIC (18) NULL,
[anställd]       CHAR (50)    NULL,
[Bokad]          BIT          CONSTRAINT [DF_Stämplingar_Bokad]
DEFAULT ((0)) NOT NULL,
[Skickad]        BIT          CONSTRAINT [DF_Stämplingar_Skickad]
DEFAULT ((0)) NOT NULL,
[Leveransdatum]  DATETIME     NULL,
[Papp]           NUMERIC (18) NULL,
[daglignr]       NUMERIC (18) NULL,
[ID]             NUMERIC (18) NULL,
[Anställningsnr] NUMERIC (18) NULL,
CONSTRAINT [PK_Stämplingar] PRIMARY KEY NONCLUSTERED ([Stämplingsnr] ASC)
WITH (FILLFACTOR = 90)
);

From help with the comments I thing going with left join seems to be a step in the right way. Now I got about 3100 results instead of 1600. If I just can remove the duplicates I can figure out how to get the produktnamn for those that didn't have it reachable via CTE2.

Some of the relationships might be a bit confusing since they are not very well defined where .id on one table might not be the same thing as .id in another. The query in CTE1 is the original query which I inherited and was trying to expand.

As the discussion in the comments revealed, an fcontainer can be related to many products, due to the 1-to-many relationship between FContainer and Stämplingar.

Try this query, which will choose only one product per fcontainer.

The missing fcontainers are due to the INNER join. Changing it to LEFT join will show all fcontainers, even those not related to any product:

;WITH CTE1 AS
(
    SELECT
        FContainerHylla.Name, FContainerPlats.HyllId,
        FContainerPlats.x, FContainerPlats.y,
        FContainerPlats.enable, FContainerHylla.Type,
        ISNULL( (SELECT MIN(fcontainer.id) AS id
                 FROM FContainer
                 WHERE (PlatsId = FContainerPlats.id)
                ), 0) AS FcId
    FROM FContainerPlats
      INNER JOIN FContainerHylla ON FContainerPlats.HyllId = FContainerHylla.Id
    WHERE (FContainerHylla.Type IN (1, 6, 10))
),

    CTE2 AS
(
    SELECT
        FContainer.id AS SubID, MIN(produkter.Produktnamn) AS Produktnamn
    FROM FContainer
      INNER JOIN Stämplingar ON FContainer.id = Stämplingar.ID
      INNER JOIN Tempo ON Stämplingar.temponr = Tempo.temponr
      INNER JOIN Produkter ON Tempo.produktnr = Produkter.Produktnr
    GROUP BY
         FContainer.id
)

SELECT CTE1.*, CTE2.Produktnamn
FROM CTE1
  LEFT JOIN CTE2 ON CTE2.SubID = CTE1.FcId
ORDER BY CTE1.HyllId, CTE1.x, CTE1.y ;


How does a security group sid differ from a per service Sid? I am learning SQL and trying to understand the concept of a SID.

At the link below it states:

  Microsoft SQL Server uses a security group to set resource access
  control lists (ACLs) instead of using the service account directly.
  Therefore, changing the service account can be done without having to
  repeat the resource ACL process. The security group can be a local
  security group, a domain security group, or a service security
  identifier (SID)

and then goes on to say

  For stand-alone instances of SQL Server on Windows Vista and on
  Windows Server 2008 operating systems, service SIDs are added to the
  service group, and the service SID for SQL Server Engine and SQL
  Server Agent is added as a login to the sysadmin server role.

It uses service group and security group interchangeably. Are they the same? ...and linked to this, if a service SID can be added to a security group (which can also be a SID), these 2 SIDs are different right?

I think so, but struggling with the Microsoft documentation.

thanks.

https://support.microsoft.com/en-us/kb/2620201

A SID is a mechanism that assigns privileges to the service itself, rather than to the account under which the service runs.

For Clustered installations:

It enables you to use a domain account with minimal privileges on the box, which also improves your security because people end up knowing the domain service
account credentials.

For non-clustered installations:

Per-Service SID, it allows you to use Network Service as the service account, improving security by
getting automatic password management, but without the traditional downside of having Network Service accumulate excessive privileges from multiple services.

Refer to :

SQL & SIDs why we need it and what the hell is it?
SIDs and IDs


The SQL Server Network Interface library could not register the Service Principal Name (SPN) Windows return code: 0x21c7, state: 15. Failure to register a SPN
  might cause integrated authentication to use NTLM instead of Kerberos.
  This is an informational message. Further action is only required if
  Kerberos authentication is required by authentication policies and if
  the SPN has not been manually registered.

I am getting above error whenever I restart my SQL service. I changed it from current service account to local account and was able to connect to SSMS from local machine, but if I am using current service account then this error is coming and in SSMS the error is :

  The target principal name is incorrect. Cannot generate SSPI context.

The password is not changed for this service account.

You specifically get error "0x21c7, state: 15" when an attempt is made to register the SPN which already exists, but possibly under different account.

When you run SQL Service under local system account, an SPN is registered under the Computer Object in AD. You can confirm from below:

setspn -l ComputerName

In above results, If you see any SPNs for MSSQLSvc then they need to be dropped with below commands on AD (You need to have Domain Admin permissions for this) so that you can then register SPN under new Service account:

setspn -d MSSQLSvc/FQDN:XXXXXXXXXX ComputerName

Once the above is done, restart SQL Service under the Service Account adn it should successfully register the SPN under the new Service account and you will be able to confirm by running the below command:

setspn -l DomainName\LoginName

Reference: http://www.b-blog.info/en/changing-service-account-for-ms-sql-server-2012-and-the-following-issues.html

You could also use Microsoft Kerberos Configuration Manager for SQL Server to automatically detect and fix such SPN Issues:

https://blogs.msdn.microsoft.com/farukcelik/2013/05/21/new-tool-microsoft-kerberos-configuration-manager-for-sql-server-is-ready-to-resolve-your-kerberosconnectivity-issues/


syntax error at or near "IF" I try to create a sp that checks if a job that didn't started already exists and if not creates a new one. It should return the id of the job.
For now I have the following code:

CREATE OR REPLACE FUNCTION
public.create_category_training_job(category_id integer) RETURNS integer AS
$BODY$
-- check if a job for this category is already queued and not started
DECLARE job_id Integer := (SELECT id FROM category_training_jobs WHERE startdate IS NULL);
-- if a not started job is present return the id it
IF job_id IS NOT NULL THEN
    RETURN job_id;
-- if id is not present create a new job and return its id
ELSE
    (INSERT INTO
        category_training_jobs (category_id, submissiondate, startdate, enddate)
     VALUES
        (category_id, NOW(), NULL, NULL)
     RETURNING id);
END IF;
$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF
COST 100;

For some rease I get "ERROR:  syntax error at or near "IF"
LINE 6: IF job_id IS NOT NULL THEN"

As far as I know IS NOT NULL should be a boolean expression, so it should be a valid expression. Am I missing something?

You are missing a BEGIN and END around your body:

REPLACE FUNCTION create_category_training_job(category_id integer)
  RETURNS integer
AS
$BODY$
-- check if a job for this category is already queued and not started
DECLARE
  job_id Integer := (SELECT id FROM category_training_jobs WHERE startdate IS NULL);
BEGIN --<<<< HERE

  -- if a not started job is present return the id it
  IF job_id IS NOT NULL THEN
      RETURN job_id;
  -- if id is not present create a new job and return its id
  ELSE
      INSERT INTO
          category_training_jobs (category_id, submissiondate, startdate, enddate)
       VALUES
          (category_id, NOW(), NULL, NULL)
       RETURNING id;
  END IF;

END; --<<<< AND HERE
$BODY$
LANGUAGE plpgsql;

The parentheses around your insert are useless noise as well, you should remove them.


Can I use Database Engine Tuning Advisor for Azure? I am trying to use "Database Engine Tuning Advisor" for queries in Azure SQL.

According to https://docs.microsoft.com/en-us/sql/relational-databases/performance/start-and-use-the-database-engine-tuning-advisor it should work with Azure SQL.

However it gives me "Failed to open a new connection".

  ADDITIONAL INFORMATION: Database Engine Tuning Advisor does not
  support Microsoft Azure SQL Database. (DTAClient)

Is it correct? I found a thread  on MSDN forums(asked at December 2016)
that confirmed that  Tuning Advisor worked with Azure in Microsoft® SQL Server® 2016 Service Pack 1 (SP1)

Is it still supported in Sql Server Management Studio v17.3 (Database Engine Tuning Advisor 14.0.1000.169), that I am using?
What should I do to enable it?

I've tried Azure Performance Insights, but it doesn't allow me to get suggestions of indexes for individual queries that I have to analyse.

No. The error message is correct - it does not support Azure SQL. There is no SQL 2016 for "Azure SQL", but there is SQL 2016 on an Azure VM, which is the same as any traditional SQL Server installation (more or less).

That thread was confused, I assume, between SQL Server on an Azure VM and Azure SQL.

Using the Azure Portal you should be able to check out index autotuning.


MongoDB instances and oplog I would like to ask a couple of things about MongoDB and replica sets:

I have different meteor applications running on my private server; each one of them uses a different meteor database (and does not need to access other databases); right now only one mongod instance is running on the server, serving all applications; should I run different mongod instances (one for every application)?

I would like to create a replica-set with oplog; my question is: this is only useful for performance when there are a lot of requests (coming from many different clients) or will improve performance also in every single request? (The different members of the replica set will cooperate to find faster the documents requested by a single client?)

I would like to create a replica-set with oplog

As per MongoDB Documentation sources from MongoDB IN ACTION The Replica sets rely on two basic mechanisms: an oplog and a heartbeat. The oplog enables the replication of data, and the heartbeat monitors health and triggers failover.

At the heart of MongoDB’s replication stands the oplog. The oplog is a capped collection that lives in a database called local on every replicating node and records all changes to the data. Every time a client writes to the primary, an entry with enough information to reproduce the write is automatically added to the primary’s oplog.Once the write is replicated to a given secondary, that secondary’s oplog also stores a record of the write. Each oplog entry is identified with a BSON timestamp, and all secondaries
use the timestamp to keep track of the latest entry they’ve applied.

Applications sometimes query secondary nodes for read scaling. If that’s happening, this kind of failure will cause read failures, So it’s important to design your application with failover in mind.

let’s look more closely at a real oplog and at the operations recorded in it.
First connect with the shell to the primary node and switch to the local database.

amj:PRIMARY> use local
switched to db local

The local database stores all the replica set metadata and the oplog. Naturally, this database isn’t replicated itself. Thus it lives up to its name; data in the local database is supposed to be unique to the local node and therefore shouldn’t be replicated.If you examine the local database, you’ll see a collection called oplog.rs, which is where every replica set stores its oplog. You’ll also see a few system collections. Here’s the complete output:

amj:PRIMARY> show collections
me
oplog.rs
replset.minvalid
slaves
startup_log
system.indexes
system.replset

replset.minvalid contains information for the initial sync of a given replica set member, and system.replset stores the replica set config document. Not all of your mongod servers will have the replset.minvalid collection. me and slaves are used to implement write concerns, and system.indexes is the standard index spec container.

  right now only one mongod instance is running on the server, serving
  all applications; should I run different mongod instances (one for
  every application)

I don't think so it's good idea to run different one mongod instance for one application.

For Your Further Ref Here and Here


Append values to MySQL column and delete old values Engine MySQL 5.6.37

I have a table with the following structure:

CREATE TABLE `multi` (
  `unitid` varchar(16) NOT NULL,
  `value` varchar(21) DEFAULT NULL,
  `date_time` timestamp NULL DEFAULT NULL,
   PRIMARY KEY (`unitid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

When I perform inserts on this table, I would like to append the 'value' column with new values seperated by  a delimiter, and then only keep the latest 3 values at any point.

I am doing it this way using PHP PDO  -

 $sql = "INSERT INTO multi (unitid, value, date_time)
VALUES (?,?,?) ON DUPLICATE KEY UPDATE presence_value= CONCAT(value, ?),
date_time = ?;";
$stmt = $db->prepare($sql);
$stmt->execute([$unit, $value, $time, ','.$value, $time]);

Whenever this php is called, it will keep appending the value column with new values separated by comma.

I then perform a CRON job every few minutes to alter the value column and keep the latest 3 values.

Is there a better way to do this? I would like to eliminate the CRON job altogether. Can I do this using triggers?

First, you can use the VALUES() syntax to update. No need to send parameters twice:

$sql = "
    INSERT INTO multi
        (unitid, value, date_time)
    VALUES
        (?,?,?)
    ON DUPLICATE KEY UPDATE
        presence_value = CONCAT(value, VALUES(value)),
        date_time = VALUES(date_time);
";

To keep only the last three, you can use some string functions:

        presence_value = CONCAT_WS(','                                -- separator
                                   SUBSTRING_INDEX(value, ',', -2),   -- last two
                                   VALUES(value)),                    -- new one

The code becomes:

$sql = "
    INSERT INTO multi
        (unitid, value, date_time)
    VALUES
        (?,?,?)
    ON DUPLICATE KEY UPDATE
        presence_value = CONCAT_WS(','                                -- separator
                                   SUBSTRING_INDEX(value, ',', -2),   -- last two
                                   VALUES(value)),                    -- new one
        date_time = VALUES(date_time);
";
$stmt = $db->prepare($sql);
$stmt->execute([$unit, $value, $time]);

No CRON jobs, no events, no triggers needed.
You need to make sure that that the 3 concatenated values will not exceed the 21 characters combined or increase the size of the column.


Why can't C# SMO see extended properties on a column but Powershell SMO can? I am attempting to read extended properties on tables and columns in a winforms C# application. I am using SQL Server SMO to do so. When I execute the application it does not see the extended properties, but when I read the extended properties using PowerShell, it does see the extended properties.
The C# code:

var x = col.ExtendedProperties.Count;
var NPI = col.ExtendedProperties["NPI"].Value;
bool npi = bool.Parse(NPI.ToString());

The PowerShell code:
Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:COMPUTERNAME
$server.Databases["<db name>"].Tables["<table name>"].Columns["<column name>"].ExtendedProperties | Select Name, Value, State

I have checked and both Visual Studio and PowerShell are using the same version of SMO (11.0.0.0). When I execute the C# code the col.ExtendedProperties.Count = 0, but when I execute the PowerShell code I get:
Name Value    State
---- -----    -----
NPI  False Existing

Does anyone have any ideas as to why this could be happening?
Additional Information
In the C# code I open up a DataReader on a table using:
sourceServer.ConnectionContext.ExecuteReader(<select command>)

to retrieve the data from the table. I then go into a while loop with DataReader and inside that while loop I have:
foreach (Column col in sourceTable.Columns)
{
StringBuilder cleanData = CleanseColumn(col, dr[col.Name].ToString());
sbvalues.Append("'" + cleanData + "', ");
}

When I step through the foreach, the sourceTable variable has its extended property, but the col column variable does not.

I just want to shadow @BradC a little.
I tested his code on Visual Studio 2015 (Update 3) on a Windows 10 Pro x64 machine against an SQL Server 2014 instance and I can confirm you can get Extended Properties of a field without issues.

To replicate I use one of my local databases. I used the following code snip to define some extended properties.

USE MyLocalDb;
GO
EXEC sys.sp_addextendedproperty
@name = N'MS_DescriptionExample',
@value = N'Some EP example.',
@level0type = N'SCHEMA', @level0name = dbo,
@level1type = N'TABLE',  @level1name = tblATableWithAtblPrefix,
@level2type = N'COLUMN', @level2name = Forename;
GO

I created a new console project and added the following references:

Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SqlEnum

All from:

  \%Install Path%\Microsoft SQL Server\130\SDK\Assemblies\

So my whole code, based on @BradC's looks like:

using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace ConsoleApplication7
{
    class Program
    {
        private static Database database;
        static void Main(string[] args)
        {
            Microsoft.SqlServer.Management.Smo.Server server;

            SqlConnection connection = new SqlConnection("Integrated Security=SSPI; Data Source=SIS_DBA");
            Microsoft.SqlServer.Management.Common.ServerConnection serverConnection =
                new Microsoft.SqlServer.Management.Common.ServerConnection(connection);

            server = new Server(serverConnection);

            database = server.Databases["ImmigSql"];

            foreach (Table table in database.Tables)
            {
                Console.WriteLine(" " + table.Name);
                foreach (Column col in table.Columns)
                {
                    Console.WriteLine("  " + col.Name + " " + col.DataType.Name);
                    foreach (var property in col.ExtendedProperties)
                    {
                        Console.WriteLine(" " + property.ToString() + "");
                    }
                }
            }
            Console.ReadLine();
        }
    }
}

Of course you can now access the properties and use their values.

The Count value that wasn't working on your example:


SQL Server local instance user login I am working on an Azure Mobile Apps backend service, which I am trying to connect to a local SQL Server database, as described here. I have followed all the steps, except I am using SQL Server 2016 rather than 2014.

If I follow the Microsoft tutorial exactly, everything works fine. I am not sure what the environment variable is about, as I don't reference it anywhere, but anyway, when I run my app, I get the following output:

  Express server listening on port 3000
  2016-06-19T09:37:39.331Z - error: Error occurred during table initialization ConnectionError: Login failed for user 'azuremobile'.  Reason: The password of the account must be changed.
  at Connection. (C:\Users\George\Source\Repos\backend\node_modules\mssql\lib\tedious.js:378:25)
  at Connection.g (events.js:260:16)
  at emitOne (events.js:77:13)
  at Connection.emit (events.js:169:7)
  at Connection.processLogin7Response (C:\Users\George\Source\Repos\backend\node_modules\tedious\lib\connection.js:672:16)
  at Connection.message (C:\Users\George\Source\Repos\backend\node_modules\tedious\lib\connection.js:1082:21)
  at Connection.dispatchEvent (C:\Users\George\Source\Repos\backend\node_modules\tedious\lib\connection.js:519:45)
  at MessageIO. (C:\Users\George\Source\Repos\backend\node_modules\tedious\lib\connection.js:439:23)
  at emitNone (events.js:67:13)
  at MessageIO.emit (events.js:166:7)

This seems to be because of the setting that is shown below, "User Must change password at next login"?

However, If I change the password, or delete and recreate the login but unchecking that feature. I get this:

  Error occurred during table initialization ConnectionError: Login failed for user 'azuremobile'.

I am possibly getting the password wrong, but I have tried this 6 times now. Is there a way I can check the password or anything else I can do to get my node project to login to my local SQL Server database?

Update:

Here is my UserMapping:

Ahh, I had a typo in the name of my database... There was nothing wrong with the credentials!


Postgres 13 - Grant to Active Directory Groups? Is it possible to grant an AD group in PG 13 without sorting to creating the group in the database?
Similar to how AD groups can be granted directly in SQL Server.
The PG server is on a Windows server that is already joined to the AD domain.
Note: I asking about authorization  and not authentication against AD.

No. All roles have to be created with CREATE ROLE in the database.
The existence of the role in the database is necessary, because it is associated with permissions in the database (authorization). Authorization is not managed through an external authority. What is managed externally is authentication.
You could write or find some software that synchronizes PostgreSQL roles with what is in your Active Directory.


DB2 Express-C on Mac OS X I have followed all the steps described on the Getting Started with DB2 Express v9.7 e-book and installed DB2 Express-C on my Mac. The installation succeeded without a problem.

I did setup OS X according to this blog post: http://programmingzen.com/2009/01/20/installing-db2-on-mac-os-x/

After the installation, I set up the DB2 instance environment as instructed and was able to start DB2 with:
$ db2start

But I was unable to run db2fs. It returns the following error message:

$ db2fs
sh: /Users/ivan/sqllib/java/jdk64/jre/bin/java: No such file or directory DB2JAVIT : RC = 127

What else do I have to setup in order to make it work on my system?

To make it easier for future users to find a working answer:

It appears that one method to fix this problem is to create a symlink from the DB2 location to the Java Home you wish to use. Since I told DB2 to use my user when I installed it (this is for a development copy), the command I used to fix this problem was:

sudo ln -s /Library/Java/JavaVirtualMachines/1.6.0_29-b11-402.jdk/Contents/Home/ ~/sqllib/java/jdk64/jre


Use an array returned from a subquery as argument in WHERE clause with ANY function in outer query A query like this:

SELECT *
FROM person
WHERE person.favorite_number = ANY(
    ARRAY[ 2 , 3 ]
)
;

…runs. The ANY() function tells us if the single value is found within an array of values. So, we find any person with a favorite number of 2, or of 3, but omit people who chose their single favorite number to be 0, 1, 4, 5, or so on.

➥ How to replace that hard-coded array with an array dynamically retrieved?

Imagine a second table event with a column hits of type INTEGER[] (an array of integers). I want to pull the one array from a single row and feed as the argument to ANY( ) of the outer query.

Something like this:

SELECT *
FROM person
WHERE person.favorite_number = ANY(
    SELECT hits FROM event WHERE when = DATE '2018-01-23' ;  -- Returns a single array from a table with `hits` column of type `INTEGER[]`.
)
;

That SELECT hits FROM event WHERE when = DATE '2018-01-23' ; is what stumps me. How do I get a single array resulting from one query to be the argument to a function in the WHERE clause of another outer query?

You're pretty close. The only thing you're missing is unnest() to turn the array into a set-valued function, allowing it to work on the right-hand-side of an ANY.

testdb=# create table person(favorite_number bigint);
CREATE TABLE
testdb=# create table event("when" date, hits bigint[]);
CREATE TABLE
testdb=# insert into event select '2018-01-23', '{2,3}';
INSERT 0 1
testdb=# insert into person select 2;
INSERT 0 1
testdb=# select * from person where favorite_number=ANY(select hit from event, unnest(hits) AS hit where "when"='2018-01-23');
 favorite_number
-----------------
               2
(1 row)


SQL Server: subscriber as publisher and hierarchical replication In Oracle Streams one way replication from one DB to another is a basic block for many replication topologies (N-way, hierarchical, combined and so on), and changes could be captured and applied for the same table at the same time.

But I can't find anywhere in SQL Server documentation whether table (article) could be a source (publisher) and destination (subscriber) simultaneously. Is it possible, for example, to setup bidirectional replication using two pairs of publisher->subscriber transactional replication?

Naturally I am more interested in multi-level hierarchy: for example one table is replicated from root node through intermediate nodes to leaves, second one - from leaves to root, and third one - bidirectionally, and for all tables intermediate nodes could perform DMLs to be replicated too. With Oracle Streams it is easy to achieve, and even more sophisticated configurations are possible, but are they with SQL Server?

UPDATE: It seems it is with use of merge replication and republishing (http://msdn.microsoft.com/en-us/library/ms152553.aspx), but what about transactional replication?

UPDATE: It seems it is with use of merge replication and republishing, what about transactional replication?

The near to same functionality can be achieved using Peer-to-Peer Transactional Replication. This is the only SQL Server replication technology that allows simultaneous writes at multiple nodes.

Peer-to-peer replication is a logical extension to bi-directional transactional replication handling the data layer only. The replication engine is used to replicate like it says "from everyone - to - everyone".

More info can be found at Publication Types for Transactional Replication. Here is a good example of configuring it.

Refer this white paper for a comparison of SQL Server and Oracle features.


Partitioning a database I have a database that's growing larger by the week (as expected).  Key tables are becoming huge and slower to perform queries against.  Whilst the indexes are sufficient, I've read the partitioning will improve the speed.  What I was thinking on doing was creating partitions on these tables via year.  I understand how the partitions would work for previous years.  What I've not managed to find is how do you future proof these partitions?  Do you have to create partitions for years to come?  Or do you create the partition at the end of the current year?

Partitioning will not improve speed. Valid partitioning scenarios include storage management (via. hot-cold filegroups), data archival and ETL fast partition switch load. Not query performance. Here is a more detailed answer at How To Decide if You Should Use Table Partitioning which goes into details.

If you want to improve speed then you need to identify the bottlenecks and address them. You can follow How to analyse SQL Server performance for details. The answers will depend on your findings.

If you want a 'magic bullet' for data growing and ad-hoc analytical workloads problems then I would recommend looking into columnstores. I specifically say ad-hoc analytical because transactional OLTP workloads can and should be addressed with traditional indexes.


SQLVDI and Backup Database event viewer errors We recently started seeing SQLVDI and MSSQLSERVER errors poping up on a number of our machines.  Shortly after event 3197 (I/O is frozen on database x), we start seeing SQLVDI 1 errors (signal abort) and MSSQLSERVER 3041 errors, failure to complete the command BACKUP DATABASE. We run our backups once daily, and looking at our DB backups, they restore fine and are not corrupt.  But batches of these errors are happening throughout the day, with no logs of our maintenance plans attempting to run at those times.  Even stranger, we are seeing these errors occouring on machines that do not have SQL Server 2012 installed.  Any ideas as to what might be causing these errors? or if they can be passed off as nuisance errors?

There's an external backup running via VSS (Volumne Shadow Copy Service), might be a VM snapshot, SAN snapshot or similar. It freezes I/O briefly to quiesce it since the files are open.

Reach out to your Windows admin for what's going on during that timeframe.

See this related post.


Login Problem as SYSDBA ORA-01031 I have the following problem:
I can connect to an Orcale database as normal user or as sysdba:

sqlplus myuser/myPw@myDB

sqlplus "sys/sysPw@myDB as sysdba"

But when another (Windows-User) is logged on, he could only use

sqlplus myuser/myPw@myDB

With

sqlplus "sys/sysPw@myDB as sysdba"

he gets the error:

  SQL*Plus: Release 9.2.0.1.0 - Production on Do Sep 29 12:10:07 2011

  Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

  ERROR: ORA-01031: Nicht ausreichende Berechtigungen.

I even tried full qualified paths to sqlplus with the same result.

Edit:

I see the possibility that different windows users use different (windows-) environment variables, especially different PATH variables.
Further I see the possibility that some windows dlls could have wrong security settings and can not be accessed by one of the users.

But I do not see, how this could make an Oracle user, ( SYS in this case ) behave differently.

2nd Edit:

I checked that the Path variable is a system variable, i.e. both windows logins use the same paths.

Have you checked your ORAPWD file? Maybe the initial originator of the database set entries=1. That would allow 1 user to logon as SYSDBA and the second user would not be able to logon as SYSDBA. Just a thought  ;-)


What is the best to add unique index on nvarchar(max) field on existing table? The situation I find myself in is that I am getting bad data into the database for some reason. I want to preserve data integrity by adding to the User table a UNIQUE INDEX on the UFID field.

Naturally I generated a script to remove any duplicate records, in a non destructive way:

update [user]
set UFID = UFID + '_dup_removal'
where ufid in (
  select ufid
  from [user]
  group by ufid
  having count(ufid) > 1
)

then I found that the field is unable to be used in a UNIQUE INDEX.

So now what is the best way to get to a result where I have the user table and data with the addition of the UNIQUE INDEX.

Note: it should be done in a way that allows easy reversal.

varchar(max) columns cannot be indexed, as you have found out.
You will need to index something else.

It could be a shortened version of the data (only you can tell whether this is acceptable or not) or a hashed version of the data. The odds of a hash collision are very low.

USE tempdb;

IF OBJECT_ID('user') IS NOT NULL
    DROP TABLE [user];

CREATE TABLE [user] (
    UFID varchar(max)
)

INSERT INTO [user] VALUES(1),(2),(3),(4),(4),(4),(3),(5);

WITH ranked_users AS (
    SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY UFID ORDER BY (SELECT NULL))
    FROM [user]
)
update ranked_users
set UFID = UFID + '_dup_removal_' + CAST(RN AS varchar(10))
where RN > 1;

--CREATE UNIQUE INDEX IX_UFID ON [user](UFID) -- FAILS

-- Solution 1: add a computed column with the size trimmed down
ALTER TABLE [user] ADD shortened_ufid AS CAST(UFID AS varchar(900))

CREATE UNIQUE INDEX IX_shortened_UFID ON [user](shortened_ufid)

-- Solution 2: add a computed column with the hashed version of the data
ALTER TABLE [user] ADD hashed_ufid AS CAST(HASHBYTES('SHA1', UFID) AS bigint)

CREATE UNIQUE INDEX IX_hashed_UFID ON [user](hashed_UFID)

SELECT *
FROM [user]
GO

Another option is cleansing the data and then ensure no duplicates can be inserted using a trigger. This is going to be painfully slow, though.

-- solution 3: use a trigger
CREATE TRIGGER TR_no_dupes ON [user]
FOR INSERT, UPDATE
AS
BEGIN

    IF EXISTS (
        SELECT 1
        FROM [user]
        WHERE UFID IN (
            SELECT UFID
            FROM inserted
        )
        GROUP BY UFID
        HAVING COUNT(*) > 1
    )
    ROLLBACK;

END


Is an ER diagram an example of a conceptual, logical or physical schema? I have been reading databases, and I clearly understand the difference between a conceptual, logical and physical schema. An ER diagram is an example of which of these schema's?

When ER Modeling was introduced in the 1970s, the intent was to provide a model that was not biased towards one particular implementation.  People were constructing relational models of a given project, even if the intent were to implement on a hierarchical or network database.

This evolved into what I learned as conceptual modeling, and that's probably what you learned as conceptual modeling as well.  The way I learned it, the conceptual model is useful for analysis of the subject matter from a data centric point of view.  That model captures what the information requirements on the database are going to be. It does NOT capture features of the proposed design.

ER diagrams initially reflected only ER models, and thus were suitable for depicting conceptual models.

When I learned logical modeling, this reflected the first stage of design, as distinct from analysis.  If the final target is a relational database, it makes sense for this model to be relational.  When I first learned this stuff, ER diagrams were not used to depict relational models.  Instead there was a kind of diagram that I'll call a relational schematic.  A relational schematic had somewhat different appearance from an ER diagram, and also somewhat different content.  For example, a relational schematic usually used arrowhead notation while an ER diagram usually used crow's foot notation.

Differences in content were mainly these:  a relational schematic included foreign keys while an ER diagram did not.  And many-to-many relationships were depicted differently.  In the relational schematic, a junction table is needed to hold the two (or more) foreign keys, whenever there's a many-to-many relationship.  In an ER diagram, a many-to-many relationship was depicted with a line with a crow's foot at both ends.

Physical models added details to the logical model, and generally reflected features available in the specific DBMS intended for implementation, as well as certain other considerations.

Over the last 20 years, there have been two broad trends.  The first trend has been to use ER diagrams to depict either relational models or ER models.  The second trend has been to skip over analysis completely and proceed directly to design. Thus, a single model depicts both the understanding of the requirements and the features of the proposed solution.

This blending of analysis and design works pretty well on small scale projects,  because the analysis is pretty trivial anyway.  For large scale projects, it can be a disaster.  You end up with the right solution to the wrong problem.  By the time the mismatch is discovered, the budget has been spent, and the deadline is looming.

There's more detail in a post-relational world, but I'll stop here.


Meta Query - Select the SELECT expression as a string field Sorry, not sure i am in the right place for this question, but anyway:
I was wondering if there is way in PostgreSQL or any other dbms to select the select statement itself? Smthn like this:
SELECT *, $QUERY_EXPRESSION FROM MYTABLE
?

You can use the current_query function:
SELECT *, current_query() FROM generate_series(1, 10) AS n;

 n  |                        current_query
----+-------------------------------------------------------------
  1 | SELECT *, current_query() FROM generate_series(1, 10) AS n;
  2 | SELECT *, current_query() FROM generate_series(1, 10) AS n;
  3 | SELECT *, current_query() FROM generate_series(1, 10) AS n;
  4 | SELECT *, current_query() FROM generate_series(1, 10) AS n;
  5 | SELECT *, current_query() FROM generate_series(1, 10) AS n;
  6 | SELECT *, current_query() FROM generate_series(1, 10) AS n;
  7 | SELECT *, current_query() FROM generate_series(1, 10) AS n;
  8 | SELECT *, current_query() FROM generate_series(1, 10) AS n;
  9 | SELECT *, current_query() FROM generate_series(1, 10) AS n;
 10 | SELECT *, current_query() FROM generate_series(1, 10) AS n;
(10 rows)


SQLcl Oracle to export data to CSV without blank first line and without carriage return character at the end For reasons, I am using the following here-doc approach to export data to CSV.
$ /oracle/sqlcl/bin/sql -s username/password@//host.mybiz.com.my:1521/dwh > arca.csv <<EOF
SET SQLFORMAT CSV
SET FEEDBACK OFF
SET TERMOUT OFF
SET ECHO OFF
SET PAGESIZE 0
SET TRIMSPOOL ON
SET NEWPAGE NONE
SELECT
    *
FROM ORDER.SALES
WHERE ROWNUM < 100;
EOF

This however produces a CSV file with empty first line and with unquoted carriage return at the end, as complained by psql tool when I want to load to Postgres
Below error happens because the CSV file has blank row as first line. The column names are read as data instead, causing type error.
$ psql -c "\COPY raw_data.arca FROM arca.csv CSV HEADER"
ERROR:  invalid input syntax for type date: "FILE_DATE"
CONTEXT:  COPY arca, line 2, column file_date: "FILE_DATE"

If I remove the blank row manually, the following error then occurs because of carriage return character at the end of data
$ psql -c "\COPY raw_data.arca FROM arca.csv CSV HEADER"
ERROR:  unquoted carriage return found in data
HINT:  Use quoted CSV field to represent carriage return.
CONTEXT:  COPY arca, line 101

How do I correctly use sqlcl with here-doc approach to produce CSV file without first blank line, and carriage return ending?

Why don't you just use the UNLOAD TABLE command ?
sql -s scott/tiger@localhost:1521/spatialdb <<EOF
unload table us_cities
EOF

Or even this simple one-liner:
echo "unload table us_cities" | sql -s scott/tiger@localhost:1521/spatialdb

This creates file US_CITIES_DATA_TABLE.csv in your current directory.
Check the help about the UNLOAD command to get details about various options for formatting the output.


Are composite primary keys bad practice? I want to know if composite primary keys are bad practice and if not, in which scenarios is their use beneficial?
My question is based on this article

Note the part about composite primary keys:

Bad Practice No. 6: Composite Primary Keys
This is sort of a controversial point, since many database designers talk nowadays about using an integer ID auto-generated field as the primary key instead of a composite one defined by the combination of two or more fields. This is currently defined as the “best practice” and, personally, I tend to agree with it.

However, this is just a convention and, of course, DBEs allow the definition of composite primary keys, which many designers think are unavoidable. Therefore, as with redundancy, composite primary keys are a design decision.
Beware, though, if your table with a composite primary key is expected to have millions of rows, the index controlling the composite key can grow up to a point where CRUD operation performance is very degraded. In that case, it is a lot better to use a simple integer ID primary key whose index will be compact enough and establish the necessary DBE constraints to maintain uniqueness.

To say that the use of "Composite keys as PRIMARY KEY is bad practice" is utter nonsense!
Composite PRIMARY KEYs are often a very "good thing" and the only way to model natural situations that occur in everyday life! Having said that, there would also be many situations where using a composite PK would be cumbersome and unwieldy and therefore not an optimal choice.
Your question is: "if composite primary keys are bad practice... (answered) and if not, in which scenarios is their use beneficial?".
Below is an example of where composite keys represent a rational/beneficial choice as the PK (indeed the only rational one as I see it - in the fiddle here, there's an extra example of having grades also!
On the plus side for composite keys, think of the classic Databases-101 teaching example of students and courses and the many courses taken by many students!
Create tables course and student:
CREATE TABLE course
(
  course_id SERIAL,
  course_year SMALLINT NOT NULL,
  course_name VARCHAR (100) NOT NULL,
  CONSTRAINT course_pk PRIMARY KEY (course_id)
);

CREATE TABLE student
(
  student_id SERIAL,
  student_name VARCHAR (50),
  CONSTRAINT student_pk PRIMARY KEY (student_id)
);

I'll give you the example in the PostgreSQL dialect (and MySQL) - should work for any server with a bit of tweaking.
Now, you obviously want to keep track of which student is taking which course - so you have what's called a joining table (also called linking, bridging, many-to-many or m-to-n tables). They are also known as associative entities in more technical jargon!
1 course can have many students.
1 student can take many courses.
So, you create a joining table
CREATE TABLE registration
(
  cs_course_id INTEGER NOT NULL,
  cs_student_id INTEGER NOT NULL,

  -- now for FK constraints - have to ensure that the student
  -- actually exists, ditto for the course.

  CREATE CONSTRAINT cs_course_fk  FOREIGN KEY (cs_course_id)
    REFERENCES course  (course_id),
  CREATE CONSTRAINT cs_student_fk FOREIGN KEY (cs_student_id)
    REFERENCES student (student_id)
);

Now, the only way to sensibly give the registration table a PRIMARY KEY is to make that KEY a combination of course and student. That way, you can't get:

a duplicate of student and course combination

a course can only have the same student enrolled once, and

a student can only enroll in the same course one time only

you also have a ready made search KEY on course per student - AKA a covering index,

it is trivial to find courses without students and students who are taking no courses!
-- The db-fiddle example has the PK constraint folded into the CREATE TABLE
-- It can be done either way. I prefer to have everything in the CREATE TABLE statement.

ALTER TABLE registration
ADD CONSTRAINT registration_pk
PRIMARY KEY (cs_course_id, cs_student_id);

Now, you could, if you were finding that searches for student by course were slow, use a UNIQUE INDEX on (sc_student_id, sc_course_id).
ALTER TABLE registration
ADD CONSTRAINT course_student_sc_uq
UNIQUE (cs_student_id, cs_course_id);

There is no silver bullet for adding indexes - they will make INSERTs and UPDATEs slower, but at the great benefit of greatly decreasing SELECT times! It's up to the developer to decide to index given their knowledge and experience, but to say that composite PRIMARY KEYs are always bad is just plain wrong.
In the case of joining tables, they are usually the only PRIMARY KEY that make sense! Joining tables are also very frequently the only way of modelling what happens in business or nature or in virtually every sphere I can think of!
This PK is also of use as a covering index which can help speed up searches. In this case, it would be particularly useful if one were searching regularly on (course_id, student_id) which, one would imagine, can often be the case!
This is just a small example of where a composite PRIMARY KEY can be a very good idea, and the only sane way to model reality! Off the top of my head, I can think of many many more.
An example from my own work!
Consider a flight table containing a flight_id, a list of departure and arrival airports and the relevant times and then also a cabin_crew table with crew members!
The only sane way this can be modelled is to have a flight_crew table with the flight_id and the crew_id as attibutes and the only sane PRIMARY KEY is to use the composite key of the two fields!


Does import sql dump wipe out current data? I have a mysql dump from version 5.5.50. I want to import it into a mysql instance on another machine, running version 5.7.13. I am using the following command to do the import:

mysql -u root -p dbname < /home/dump.sql

I want to be sure that this command will completely overwrite everything on the new mysql instance. That instance already has a database with the same name and some temp data in it.

Or should I manually drop the database on the new instance prior to doing the import to ensure they're completely identical after the import?

Thanks

it is totally dependent on your mysqldump process and which options you have used while dumping. There are options like --add-drop-database,--add-drop-table  etc with mysqldump which results to wipe out your existing database with same name.
see mysql docs for futher details.


Percona Audit Log plugin not auditing account I have setup the Percona-server-5.6.41-89 Audit plugin with the following settings:

audit_log_policy=QUERIES
audit_log_handler=FILE
audit_log_file=/var/log/mysql/perc_audit.txt
audit_log_format=CSV
audit_log_rotate_on_size=1000000000
audit_log_rotations=9
audit_log_include_accounts=someuser@%

Now for some reason, auditing is not being performed against the configured account. I tried SET GLOBAL audit_log_include_accounts="'someuser'@'%'";, but with no success.

How do I need to set up the audit_log_include_accounts parameter so that it contains wildcards in the host part?

The resolution is SET GLOBAL audit_log_include_accounts='someuser@'; - without host part of definition, BUT with @


CREATE FILE encountered operating system error 5 (Access is denied.) I'm trying to execute the following script in SQL Server Management Studio:
USE [master]
GO

CREATE DATABASE [test1] ON PRIMARY (
  NAME = N'test1',
  FILENAME =
    N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\test1.mdf',
  SIZE = 70656KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)
LOG ON (
  NAME = N'test1_log',
  FILENAME =
    N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\test1_log.ldf',
  SIZE = 164672KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

But I'm getting the error:

Msg 5123, Level 16, State 1, Line 2
CREATE FILE encountered operating system error 5 (Access is denied.)
while attempting to open or create the physical file
'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\test1.mdf'.
Msg 1802, Level 16, State 4, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Already have all role permissions for my user, any ideas on what's wrong?

You are getting a permissions error.  The account which is running the SQL Server doesn't have the needed rights on the folder which will contain the database files.

You need to give the account which is running SQL Server (not your account) full control of C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA.


Is it possible to install Developer Edition of SQL Server as the primary development server? Is it possible to install Developer edition of SQL Server on a Windows server environment as development server? Each developer will have a local copies of same SQL Server edition and will connect to this development server for data/object synchronization.

Also, if you can refer a link for a full description of this product.

Indeed this setup is possible. When the developers open Sql Server Management Studio, instead of connecting to a locally installed instance (not necessary given your requested setup, but I would still install it) then they would connect to the shared instance on the development server.

That is how our shop is setup.


mongo shard + maxSize I have a sharded cluster (mongo 3.2). I'm trying to set a Maximum Storage Size for a Given Shard, but it fails.

mongos> show dbs
DB_files  820.835GB
config      0.012GB
mongos> config = db.getSiblingDB("config")
config
mongos> config.shards.updateOne({ "_id": "shard0000"},{$set : { "maxSize": 4194304 }})
2016-12-21T15:47:00.467-0300 TypeError: Property 'updateOne' of object config.shards is not a function

So I tried something else:

mongos> use config
switched to db config
mongos> show collections
shards
actionlog
chunks
mongos
collections
lockpings
settings
version
locks
databases
tags
changelog
mongos> db.shards.updateOne({ "_id": "shard0000"},{$set : { "maxSize": 4194304 }})
2016-12-21T15:50:05.842-0300 TypeError: Property 'updateOne' of object config.shards is not a function

The shading is working, balancing, etc.

mongos> db.shards.find({ "_id": "shard0000"})
{ "_id" : "shard0000", "host" : "mongosh1:27017" }
mongos> sh.status()
--- Sharding Status ---
  sharding version: {
        "_id" : 1,
        "minCompatibleVersion" : 5,
        "currentVersion" : 6,
        "clusterId" : ObjectId("57cf4769b7dc19454fe95604")
}
  shards:
        {  "_id" : "shard0000",  "host" : "mongosh1:27017" }
        {  "_id" : "shard0001",  "host" : "mongosh2:27017" }
  databases:
        {  "_id" : "DB_files",  "primary" : "shard0000",  "partitioned" : true }
                DB_files.fs.chunks
                        shard key: { "files_id" : 1, "n" : 1 }
                        chunks:
                                shard0001       9136
                                shard0000       9136
                        too many chunks to print, use verbose if you want to force print

Any ideas?

Most likely you are using a different shell version than the db version. That is, the output of db.version() (the server version) and version() (the shell version) should be the same.

For example:

mongos> db.version()
3.4.1
mongos> version()
2.6.12
mongos> config = db.getSiblingDB('config')
config
mongos> config.shards.updateOne({_id:'shard01'},{$set:{maxSize:4194304}})
2016-12-22T13:22:56.154+1100 TypeError: Property 'updateOne' of object config.shards is not a function

Note the identical error message to the one you're seeing. This is because updateOne function doesn't exist in early mongo shell. However, using the correct version:

mongos> db.version()
3.4.1
mongos> version()
3.4.1
mongos> config = db.getSiblingDB('config')
config
mongos> config.shards.updateOne({_id:'shard01'},{$set:{maxSize:4194304}})
{
  "acknowledged": true,
  "matchedCount": 1,
  "modifiedCount": 1
}


How to restore replication after server crash We had two databases on one SQL Server 2005 instance with transactional replication between them (3 tables as articles). This server acted both as publisher and distributor.

Then the RAID on the server failed. However we managed to detach and copy almost all .mdf files before the crash.

After the system restored, we reinstalled SQL Server, restored system databases (master, model, msdb) and placed the .mdf files to the same paths. So it started.

The problem appeared with the replication. The Local Publications folder was empty, though the Local Subscriptions folder contained the subscription in question. When I tried to add a new publication I got:

  New Publication Wizard encountered one or more errors while retrieving
  publication names.

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  Invalid object name 'dbo.syspublications'. (Microsoft SQL Server,
  Error: 208)

I decided to try to remove the left subscription (eventually I managed to) and disable publishing and distribution on the server, which gave me:

  SQL Server could not disable publishing and distribution on
  'someserver'.

  Invalid object name 'dbo.syssubscriptions'. Transaction count after
  EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is
  missing. Previous count = 0, current count = 1. Transaction count
  after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION
  statement is missing. Previous count = 0, current count = 1.
  Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
  TRANSACTION statement is missing. Previous count = 0, current count =
  1. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count =
  1. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count =
  1. Changed database context to 'master'. (Microsoft SQL Server, Error: 208)

It seems like some info was not recovered from the system database backups (probably distribution database may be missing something).

Also I tried to follow the answer given in unable to disable publishing and distribution. But to no success. I tried to create syspublications both in master and distribution, but nothing changed.

So, how can I make replication work on this server?

Tried to publish some other database on the server and it worked. So the problem seems to be connected with the published database. How can I clear it up to manage create a new publication over it?

System Tables subfolder of the problem database misses the replication tables, which are created, when a database is published.

In replication monitor I can see those old subscriptions, while they are not available in  Replication folder.

In the end I simply did what the server wanted, i.e. created all the "publication" tables (*dbo.MSpeer_** and *dbo.sys**) in the problematic database (the one that was the publisher), through Script Table As -> CREATE To dialog. Thanks to this server allowed me to disable publishing and distribution. Then I only needed to reconfigure replication.

Hope, next time it crashes (actually I hope it won't), we'll be wise enough to backup tables involved in replication thoroughly.

Additional information by Zane, originally left in a comment:

The tables dbo.MSpeer_* and dbo.sys* can also be created by configuring the distributor/publisher on a different server with SQL Server installed. This will create these tables in the database that you've set for publishing. See -> Tables - >System Tables in SSMS.

Then, generate the schemas for all these tables and then create these tables in the problematic database which will allow you to disable distribution and publishing and reconfigure the distributor.


mysql index on derived table Possible Duplicate:
  Calculate MATCH() AGAINST() scores from UNIFIED QUERY not FOR EACH TABLE

I want to have a FULLTEXT INDEX on data name in the base SELECT that selects data from another SELECT which unions several tables.

SELECT `data` FROM SELECT (
  SELECT `name` as `data` FROM `names`
  UNION SELECT `title` as `data` FROM `titles`
) as `search`

I want data to have a FULLTEXT INDEX

What is your requirement? we cannot index "data" as it is not a column in table, It can defined with multiple columns within one table .

Few things to be considered while using Fulltext.
Its only supported for MyISAM engine,
You can also set few variables "ft_min_word_len" and "ft_max_word_len" to search,
Some stopwords are ignored we can set change values at "ft_stopword_file", and
Its not case sensitive search.


How to rebuild SYSAUX corrupted tablespace? I have database 10g 32-bit windows without backup. I want to migrate full database to 11g, but the SYSAUX tablespace is corrupted.

Can I rebuild the SYSAUX tablespace?

Corruption of the SYSAUX tablespaces is apparently a normal thing. It happens every now and then and can be fixed following these steps:

Find the corrupt block (V$DATABASE_BLOCK_CORRUPTION)

Run the following script to retrieve the blocks that are corrupt:

select * from v$database_block_corruption;

This will output a list of corrupt blocks. In most cases rebuilding the index where the corrupt block is located will fix the corruption.

Rebuild all the indexes (Script)

Run the following script to rebuild all the indexes for the SYSAUX tablespace:

    set lines 90;
    set heading off;
    set feedback off;

    spool runme.sql

    select
    'alter index '||owner||'.'||index_name||' rebuild online tablespace sysaux;'
    from
    dba_indexes
    where tablespace_name = 'SYSAUX';

    spool off

    spool rebuild.lst
    @runme.sql
    spool off

Run DBMS_REPAIR()

Have a look at the official documentation for more details (see references)

Open up a Service Request

If all else fails, you might want to consider opening up a Service Request with Oracle.

Reference:
- sysaux corruption tips (Burleson Consulting)
- Using DBMS_REPAIR to Repair Data Block Corruption (Oracle)
- V$DATABASE_BLOCK_CORRUPTION (Oracle)

  Disclaimer
  In no way affiliated with any of the companies.


What is the best way to transport database fields from one database to another? I have two databases. The table name and fields name are different and field numbers are unequal. I need to transport all fields from one database to another. I can import the database as CSV format.

I can use a PHP script which will accomplish this.

But is there any other way to do this easily without any script.

If you have the following scenario:

On DBServer1, Database db1 contains MyISAM tables only
On DBServer2, Database db2 is the target database
/var/lib/mysql is datadir for both DB Servers
10.2.3.40 is the IP address of DBServer1

You could copy the MyISAM tables straight to the target database

From DBServer2, run these Linux commands

cd /var/lib/mysql/db2
scp IPADDRofServer1:/var/lib/mysql/db1/*.frm /var/lib/mysql/db2/.
scp IPADDRofServer1:/var/lib/mysql/db1/*.MYD /var/lib/mysql/db2/.
scp IPADDRofServer1:/var/lib/mysql/db1/*.MYI /var/lib/mysql/db2/.
chown -R mysql:mysql /var/lib/mysql/db2/.

That's it. No SQL Processing of any kind. The information_schema database on DBServer2 will autodetect the presence of new MyISAM and incorporate them accordingly.

Give it a Try !!!

CAVEAT : THIS DOES NOT WORK InnoDB TABLES AT ALL !!!


How do you configure a 4-node AlwaysOn cluster to have two listeners? Looking to build a 4-node (servers A, B, C & D) AlwaysOn Windows 2016 failover cluster with 2 nodes in the primary datacenter for HA, and 2 other replica nodes in the DR datacenter for disaster/reporting purposes.

Since this is a multisite cluster, is there a way to configure to have two listeners (one for the Production/primary instance, the other for the DR instance) under the one AlwaysOn Availability Group.

Also, would SQL on all four nodes need to run under the same service account?  Or can servers A, B be on one account while servers C, D run under another account.

Is this even feasible/possible?  Thank you in advance for your help!!  Hope everyone is safe & well during this time.

is there a way to configure to have two listeners

Sort of, but it's unusual.  The more normal practice is to have a single AG Listener, with read-only routing configured.  That way everyone connects to the same hostname, but read-only workloads will disconnect and reconnect directly to an available readable replica.

  would SQL on all four nodes need to run under the same service account?

It's recommended, but not required, to use a single Group Managed Service Account for all the nodes.  If the instances have different service accounts, Kerberos won't work when connecting to the AG Listener.


Dependency Preservation and Normal Forms Suppose a set of functional dependencies F holds over a relation R . We find that the dependencies violate the conditions of 2NF and decompose the relation into R1 and R2. After the process what can i say about the set R. In other words are all the dependencies preserved if not how to we find the new set of dependencies.
Do we need to specify the dependency set for each of the decomposed relation?

As an example let us consider the set

R = {a, b, c, d, e, f, g , h , i , j}

F = {AB -> C, A -> DE, B -> F, F -> GH, D -> IJ }

Computing the closure we find AB to be a candidate key of R
this leads to the violation of 2NF by the dependency A -> DE We compute A+ = {A , D , E , I , J }

the Decomposition therefore is R1 = {B , C , F , G ,H}  R2 = { A , D , E , I , J}

After this step i am unsure how to check the new relations for violations , which set of dependencies do i take as a reference for checking for violations.

The way we have constructed R2 ensures no violation of 2NF  but what about the Relation R1?

Given R(A B C D E F G H I J ) and the FD's specified above
We would get three relations after normalizing it till 2NF:
Since the FD: A->DE is not satisfying 2NF
Finding A closure we get (ADEIJ).
Since the FD B-> F also does not satisfy 2NF
Finding B Closure we get (BFGH).
Hence we get the relations:
R1(ADEIJ) and R2(BFGH).
A is the Candidate Key for R1; B is the Candidate Key for R2
What remains from R is C.
So we get the third relation R3 (ABC).

This is a dependency preserving and loss less decomposition.


repmgr: Can I replicate databases from two different masters to a third slave? If I have a master node 1 (with database A) and a master node 2 (with database B) can I use repmgr and have both A and B replicated to a hot standby node 3 ?

[ EDIT: They would all be running Postgres 9.3 ]

PostgreSQL's built-in replication (as used by repmgr) replicates entire PostgreSQL instances - with all databases. The transaction log is shared between all databases.

PostgreSQL can't merge transaction logs from another instance. Nor can it replicate only a subset of databases. You have to replicate exactly one instance to exactly one other instance.

If you want something other than that, there are a variety of other replication options such as Londiste, Slony-I, BDR, etc.


How to restart PostgreSQL server under CentOS 7 I installed PostgreSQL 10.1 under a CentOS 7.3 environment.

The service is started (postmaster.pid file present under /var/lib/pgsql/10/data), but I need to reload configuration or restart the server following a change in pg_hba.conf.

However, trying different commands, I get the following:

pg_ctl reload -D /var/lib/pgsql/10/data
bash: pg_ctl: command not found

service postgresql reload
Redirecting to /bin/systemctl reload postgresql.service
Failed to reload postgresql.service: Unit not found.

I found out that you need to specify the exact name for the PostgreSQL service, which you can find under the list of services, using systemctl (also see this post):

systemctl list-units|grep postgresql
postgresql-10.service                                                                     loaded active running   PostgreSQL 10 database server

Then you can use service:

service postgresql-10.service reload

or

service postgresql-10.service restart

Alternatively, you can use the systemctl command:

/bin/systemctl reload postgresql-10.service

or

/bin/systemctl restart postgresql-10.service


Postgres JSONB - Flatten nested objects and groupings As i've learned, if i need to flatten a JSON contained in a specific field of my postgres db table, i can use one of the following functions/operators
Now, those functions are perfect for a simple for some simple flattening like this one:
{
   "manufacturer":[
      {
         "manufacturer":"Tesla",
         "address":"Deer Creek Road Palo Alto",
         "contact":"support@tesla.com"
      },
      {
         "manufacturer":"BMW",
         "address":"Petuelring 130, 80809 München",
         "contact":"support@bmw.com"
      }
   ]
}

Using the jsonb_to_recordset function the json will be flattened without any problem.
Now, here comes my effective question, i'm not able to address in order to solve this problem effectively...
Suppose that every manufacturer has a nested JSON object that exposes the model and starting price like reported in the following JSON
{
   "cars":[
      {
         "manufacturer":"Tesla",
         "address":"Deer Creek Road Palo Alto",
         "contact":"support@tesla.com",
         "models":[
            {
               "model":"Model S",
               "starting_price":50000
            },
            {
               "model":"Model 3",
               "starting_price":35000
            },
            {
               "model":"Model X",
               "starting_price":70000
            }
         ]
      },
      {
         "manufacturer":"BMW",
         "address":"Petuelring 130, 80809 München",
         "contact":"support@bmw.com",
         "models":[
            {
               "model":"X1",
               "starting_price":35000
            },
            {
               "model":"X2",
               "starting_price":32000
            },
            {
               "model":"X3",
               "starting_price":39000
            },
            {
               "model":"Series 5",
               "starting_price":55000
            },
            {
               "model":"Series 3",
               "starting_price":50000
            }
         ]
      }
   ]
}

How i can create a query that also flattens the models object effectively and outputs many rows as the manufacturer models reported in the models object?

Bonus Question:
In addition to that, it's possible to create a query that outputs something like this in a more compact way grouping rows as shown below?

Any help is appreciated!

You need to unnest twice:
select x.m ->> 'manufacturer' as manufacturer,
       x.m ->> 'address' as address,
       x.m ->> 'contact' as contact,
       m.model ->> 'model' as model,
       (m.model ->> 'starting_price')::int as starting_price
from the_table t
  cross join jsonb_array_elements(t.models -> 'cars') as x(m)
  cross join jsonb_array_elements(x.m -> 'models') as m(model)

Suppressing repeating values is better done when you display the data in your application, not in SQL.
Online example


What is the meaning of "sga_max_size= # internally adjusted" I used following command to get backup of my spfile.

CREATE PFILE='c:/test_init.ora' FROM MEMORY;

it has following lines related to memory.

memory_max_target=12G
memory_target=8G
__java_pool_size=16M
log_buffer=4743168 # log buffer update
__pga_aggregate_target=6G
_pga_max_size=1258280K
processes=400
sga_max_size=2G # internally adjusted
__sga_target=2G
__shared_io_pool_size=0
__shared_pool_size=704M
_smm_max_size=629140
_smm_min_size=1024
_smm_px_max_size=3145728

I wonder about "# internally adjusted" comment. What does it mean?

It means it was adjusted by Oracle, because it was not specified explicitly.
When using Automatic Memory Management, you need to set only the memory_target parameter, the sga/pga parameters are handled internally by the database automatically.


Postgresql indexes in memory I use Postgresql 9.2. I have tables stored on a ramdisk, but now indexes on those relations are located on a regular disk. I want to move them into ram too, but for all I know postgresql loads indexes into memory when it's possible. Am I right? Is there any method to tell postgres don't load indexes into memory.

If you force indexes and tables into RAM by using a ramdisk, all you're likely to be doing is wasting RAM.

PostgreSQL uses the operating system's buffer cache. So when a table/index page is first read, it's cached by the OS, and future reads are returned by the OS cache, not fetched from disk. If the database is bigger than the available memory then the OS will throw oldest, least-used data away and it'll get read from disk next time it's needed.

If you create a ramdisk, you are reducing the amount of memory the OS can use for cache. It'll still cache each block read from the ram disk before returning it to PostgreSQL. If there's not enough space left for efficient memory management of the buffer cache the kernel will do a lot of work trying to free pages for buffered reads, and your ramdisk will actually slow things down.

For writes, the main effect the ramdisk has is to make all disk sync operations effectively no-ops. So it's like running PostgreSQL with fsync=off, where it never tries to flush writes to disk at all. It has the same property of being able to eat your data whenever it feels like it, too.

There's generally not much benefit in running PostgreSQL on a ramdisk vs running it with data durability settings turned off, a very high dirty writeback threshold, etc.

(On a side note, if you create a tablespace on a ramdisk and put anything in it, you're making the whole database cluster - i.e. all databases on that server - at risk of severe data loss. Don't do it. Either initdb the whole database on the ramdisk or, preferably, just tune the server so you don't need to mess about with ramdisks.)


Can sharding help in this scenario? I will have around 600 GB of data in single table with multiple columns in MySQL. Basically this table will store user events from application.
Indexes are there on required columns.

Question: If I shard the data based on some key which will be included  in every  query,
insertion and selection will happen on limited data set. Say I have 3 nodes  and data is redistributed equally on all 3 nodes with each shard of 200 GB.
Now based on shard key in query, I will send it to specific DB only.I think it will improve the search performance. Is that right ?

Constraint : Issue is I have single node. If create three shards(of 200 GB each) on single node , will it help in search performance ?

Can MongoDB(instead of MYSQL) help here with same approach ?

If create three shards(of 200 GB each) on single node, will it help in search performance ?

Short Answer: No.

Info from Comments:

16GB of RAM.

The important query is a "search", but it is rather unbounded.  Hence, we need to assume that the whole table (600GB) needs to be scanned.

So, why not have multiple instances of MySQL, each with a fraction of the data?  They could work in parallel, couldn't they?

Long Answer:

I/O is much more costly (10x?  100x?) than CPU or context switching or looking a cache.  Of the 16GB, you will allocate perhaps 11GB for  innodb_buffer_pool_size, which is the main "cache" in MySQL.  There is no way to fit 600GB into 11GB, so there will be I/O.  If you shard across ~60 separate machines, then you could avoid I/O.  Or if you had 700GB of RAM on the one machine.

So, on a single machine, the parallelism is defeated because of there being only one access to disk, and each parallel thread need to spend most of its time fetching data from disk.  You would see the CPU cores sitting there twiddling their thumbs waiting for I/O.

What if an index could be used?

Why bother with sharding?  With a usable index, the query is likely to be fast because it won't be touching much of the 600GB, hence doing very little I/O.

And if you have lots of different connections, MySQL (with the help of the OS) is happy to farm them out to different CPUs.

Hence, I/O is not a bottleneck; CPU is not a bottleneck?  The sharded system is complex, but for no benefit.

When is Sharding needed?

If you need "read scaling" -- that is, too many clients are accessing MySQL, and the system is overloaded -- use Replication.  Each Slave has a copy of the data.  You can add as many Slaves as needed to handle the load.  Note:  Each Slave needs to be on its own hardware.

If you need "write scaling" -- that is, the writes, not the reads, are the bottleneck -- then you might need multiple servers and do "sharding".

Even with either of those situations, I suggest that there may be ways to improve the performance of the queries / indexes / ETL / etc to tame it.


Failed to attach SQL Express DB, Could not continue scan with NOLOCK We have a remote client that had to rebuild their server and has been having trouble with their SQL Express database since. I cannot remotely connect to this client unfortunately. I had them run "DBCC CHECKDB" and it showed one of the tables to be corrupted. I had them copy and send me the .mdf and .ldf files so that I could hopefully do something to resolve that.

I can't even attach that database though. I get the below errors when attempting to attach it...

  Attach database failed for Server 'GW1\SQLEXPRESS'.

  Could not continue scan with NOLOCK due to data movement.
  Converting
  database 'QAData1' from version 655 to the current version 782.
  ...
  Database 'QAData1' running the upgrade step from version
  690 to version 691. (Microsoft SQL Server, Error: 601)

Any idea how I can get around this error or where I could look to possible get a better error message?

Any tips how I can repair a .mdf file if I can't attach it?

There have been other cases that have shown that this error can happen when trying to attach a corrupted database.

Instead of detaching the database and sending MDF/LDF files, I suggest asking for a proper backup and attempt to restore it. This is a much safer way to copy a database. Also, detaching a database that is suspect or corrupt is especially dangerous, and absolutely the last thing you ever want to do or advise a client to do. Hopefully the client has backups dating back to before the corruption occurred (and this is why we keep more than just the last backup, and why we test our backups frequently to ensure they can be restored).

To avoid all the upgrade steps, which may be what is causing this to fail, another option would be to set up an instance of SQL Server that matches the version of SQL Server that the client has (SQL Server 2008), because attaching it to a newer version makes real, irreversible changes to the database. This will avoid changing the database and skip the upgrade steps, but is unlikely to allow the database to be attached or restored if there is, in fact, corruption.

As an aside, in general, don't use the SSMS UI to attach a database... write a CREATE DATABASE ... FOR ATTACH; command in a query editor window. The UI leaves a lot to be desired.


How to print results in SQLcmd window I'tm trying to show in SQLcmd window the results of these queries:

SELECT SYSDATETIME() AS "date";

PRINT 'EXTRAÍDO POR:'
SELECT SUSER_NAME() AS "Owner"

PRINT 'PASSWORD SETTINGS';
exec xp_loginconfig;

PRINT 'PASSWORD SETTINGS';
select * from sys.sql_logins;

And using this command in SQLcmd:

sqlcmd -S myServer -i inputpath -s, -o outputPath(like C:\log.xls) -e

So the sqlcmd output the file and does not show nothing in sqlcmd screen. How can i print these "Print" commands in the sqlcmd screen? (command promt).

When I run it on SSMS works well, like:

(1 row affected)
OWNER:

(1 row affected)
PASSWORD SETTINGS

(8 rows affected)
PASSWORD SETTINGS

I would like to show in the prompt command this, like in the SSMS.

Thanks!

You are not seeing anything in the command window because you are telling SQLCMD.EXE to direct all output to a file due to using the -o switch. If you want results to go to a file and messages to go to the screen (and not into the file), then you cannot use the -o switch.

Instead, you will need to manually redirect output (i.e. "stdout") to the file using > on the command line, as in > log.xls. However, by default, PRINT messages will also go to "stdout", so you need to send those to "stderr" by using the -r1 switch (so that they will be displayed instead of going into the file). That will leave you with (writing to C:\ is not a good practice, so maybe create a TEMP directory/folder, or something like that):

SQLCMD -S (local) -i script.sql -s, -e -r1 > C:\TEMP\log.xls

You will then see the following in the command window:

  EXTRAÍDO POR:
  PASSWORD SETTINGS
  PASSWORD SETTINGS

The "(18 rows affected)" will still be in the output file. If you want that in the window and not in the file, then you need to:

Add SET NOCOUNT ON; to the top of your SQL script
Add PRINT '(' + CONVERT(VARCHAR(10), @@ROWCOUNT) + ' rows affected)'; after each T-SQL query (i.e. after each SELECT and EXEC in this script).

After doing those 2 steps, you will see the following in the command window:

  (1 rows affected)
  EXTRAÍDO POR:
  (1 rows affected)
  PASSWORD SETTINGS
  (8 rows affected)
  PASSWORD SETTINGS
  (18 rows affected)

ALSO:

You might need to remove the -e switch if sending the original SQL queries in script.sql to the top of the output file causes a problem when opening it in Excel (which I assume you are wanting to do given the .xls file extension).
Depending on your OSs regional setting and the code page associated with the default collation of your login's default database, you might need to change the current code page of the command window so that the Í character displays properly.

Meaning: I am in the US so my default code page for command windows is 437, yet the collation of my login's default database is a Latin1_General_* collation which uses code page 1252. In SSMS, PRINT 'EXTRAÍDO POR:' displays as:

  EXTRAÍDO POR:

in the "Messages" tab. But, in my command prompt window, I see:

  EXTRAIDO POR:

Command prompt shows " I " instead of " Í ". To fix this, I needed to run the following in the command prompt window before running SQLCMD:

chcp 1252

P.S. No, GO is not required in this script.


Keeping my database updated every 30 minutes using Log files I have a situation where I get a full nightly backup and trans log backup every 30minutes starts at 7:00 am ends at 2:00 am. I need to restore them every 30 minutes into a database and replicate this data into another database, so every next 30 minutes I should be able to restore the log files and keep the database in usable state to replicate this data to another database. I think we cannot use the database while restoring log files, but is there any process to deal with this kind of situation?

With log shipping you can do this. However, during the restore, the connection will be on hold. Query resulting is delay. Unfortunately, log shipping in there is no such solution.


Count the number of days in each month between two specified dates I am trying to write an SQL Server formula which give the number of days in each month between two specified dates. E.g:

This looks like a lot of code but I think it steps through and explains how it meets the requirements of counting days in the range, including leaving out days from the next year (in case someone forgets to filter for it first, which the sample usage also handles):

CREATE FUNCTION dbo.ReturnDateRangeByMonth
(
    @start date, @end date
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
  WITH d(d) AS -- recursive to get all the days in the range
  (
    SELECT @start UNION ALL SELECT DATEADD(DAY, 1, d) FROM d
    -- except the last day
    WHERE d < DATEADD(DAY,-1,@end)
    -- and except any days from the next year
      AND d < DATEFROMPARTS(YEAR(@start)+1, 1, 1)
      -- on < 2012 use AND YEAR(d) = YEAR(@start)
  ),
  m(m) AS -- grab all 12 months to pivot on
  (
    SELECT 1 UNION ALL SELECT m + 1 FROM m WHERE m < 12
  )
  SELECT m.m, c = COALESCE(COUNT(d.d),0) FROM m
  LEFT OUTER JOIN d ON m.m = MONTH(d.d) GROUP BY m.m
);

Sample usage:

DECLARE @vaca TABLE (OOW date, RTW date);

INSERT @vaca(OOW,RTW) VALUES('20191222','20200107'),('20190326','20190528'),
('20190328','20190522'),('20190612','20190617'),('20190404','20190418');

;WITH v AS
(
  SELECT OOW, RTW FROM @vaca WHERE OOW >= '20190101' AND OOW < '20200101'
)
SELECT * FROM v CROSS APPLY dbo.ReturnDateRangeByMonth(v.OOW, v.RTW) AS f
  PIVOT (MAX(c) FOR m IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS p
  OPTION (MAXRECURSION 366);

Results:


Hierarchical data: get all nodes from a hierarchy, including parents that don't match the where I have a table similar to this structure:

TaskId:  int PK
AssignedTo: varchar(50)
ParentTaskId: int FK nullable

ParentTaskId references TaskId.

The data in the table looks similar to this:

TaskId     |     AssignedTo     |    ParentTaskId

1                Jim                 NULL
2                Jim                 1
3                Joe                 2
4                John                NULL
...

The crux of my issue is this:  I need to query the table for all tasks assigned to Joe (which would return TaskId 3), but I also need to show the hierarchy for the found task, so I need to return Tasks 1, 2, and 3 from the above table.

I'm lost as to how do pull this data from SQL, either with one query or multiple queries.

Help please!  Using SQL Server 2012.

For future posts, it's much better to provide CREATE TABLE and INSERT statements like:

create table tasks
(TaskId int not null primary key
,AssignedTo char(5) not null
,ParentTaskId int references tasks (taskid));

insert into tasks (TaskId, AssignedTo, ParentTaskId)
values (1, 'Jim', NULL);
insert into tasks (TaskId, AssignedTo, ParentTaskId)
values (2, 'Jim', 1);
insert into tasks (TaskId, AssignedTo, ParentTaskId)
values (3, 'Joe', 2);
insert into tasks (TaskId, AssignedTo, ParentTaskId)
values (4, 'John', NULL);

That said, you can use a recursive common table expression (CTE) as in:

with tree (ancestortaskid) as (
    select taskid from tasks where assignedto = 'Joe'
    union all
    select ParentTaskId from tree t join tasks s
        on t.ancestortaskid = s.taskid
)
select t.ancestortaskid
from tree;

I only had sql-server2017 to test with, but afaik, recursive cte should be availible in 2008 as well


What's causing my postgresql database to freeze? This is my current setup on my home laptop:

OSX El Capitan.
Installed postgresql 9.6 via postgres.app.
Symlinked the database files to an external HD (connected via USB).
Inserting data via the psycopg2 python module in a jupyter notebook.

I have a long list of files to insert to various tables.  I've made sure to turn off logging and there are no indexes assigned to any of the tables.  There's plenty of space on the external HD.

Eventually the upload would hang at some particular file.  I would be able to select from other tables in psql except for the table that file was being uploaded to.  Examined tables pg_prepared_xacts and pg_locks and nothing's there.  Attempting to drop the corrupted table would just sit there.
 However, I wouldn't be able to do a clean shutdown of the server on postgres.app due to the error pg_ctl: server does not shut down, even though trying to access via the psql shows that the server's shut off.  I would try to restart the laptop, and it'd just sort of freeze with all the applications closed.  After doing a hard reboot, postgres.app would complain about not being able to start the server due to some fatal error.

At a complete loss as to what's going on.  Any clues?

Edit: I've tried killing the active COPY process, which reads from STDIN to no avail via:

SELECT pg_cancel_backend(__pid__);
SELECT pg_terminate_backend(__pid__);
kill __pid__

Console.app not showing anything in logs.  When I force a shutdown via postgres.app this is what shows up in the logs:

LOG:  received fast shutdown request
LOG:  aborting any active transactions
FATAL:  terminating autovacuum process due to administrator command
LOG:  autovacuum launcher shutting down
FATAL:  terminating connection due to administrator command
FATAL:  terminating autovacuum process due to administrator command
FATAL:  the database system is shutting down

Turns out the issue was the format of the external HD.  It was in NTFS, which was accessed via Paragon NTFS, and for whatever reason the connection would simply screw up after a few hours.  I reformatted the external HD into Mac OS Extended (Journaled) format and all problems went away.


Track daily view count of youtube videos I am making program that searches youtube for given keyword and need to get links of active videos. Term "active" means that video must have 1000 views per day. Youtube API doesn't give such statistics, only total view count. Now I want to run Java program every day to search for given term and to save data into database. I made database in SQLite and want to ask you if my database model is ok.
So, I have just one table VIDEO. Primary key is "saying key",  based on date. For example: 201108040001, 201108040002, 201108040003, 201108050001, 201108050002, 201108050003.
Second attribute is Address, for example: http://www.youtube.com/watch?v=pqha-F3KYHk&feature=youtube_gdata_player, http://www.youtube.com/watch?v=FAIgWGCYGJU&feature=youtube_gdata_player, etc.
Third attribute is Date.
Forth attrubute is TotalViewCount.
Fifth attribute is SearchTerm.

Now I wonder if this database model is good for getting statistical data. For example, I want to get videos that have more than 1000 view per day. To do this I have to subtract TotalViewCounts from 2 dates, and so for every row in a table.

Here is table DDL:

CREATE TABLE VIDEO (
 VideoID        INTEGER         PRIMARY KEY AUTOINCREMENT NOT NULL,
 Address        VARCHAR( 100 )  NOT NULL,
 Date           DATE            NOT NULL,
 TotalViewCount INT             NOT NULL,
 SearchTerm     VARCHAR( 100 )  NOT NULL
);

Well, the biggest thoughts I have are regarding the application and data collection.  For example, why are you storing the entire address?  You really should only store the "v=" option of the querystring.  That's the part of the web address to tells youtube to go to the video.  If you store the entire address, then you may have one video stored multiple times when new attributes are added to the querystring (such as &feature=youtube_gdata_player changes to &feature=embedded_player or something).  I would just pull of the string between "v=" and the next "&" to get a "VideoId" column.

Secondly, I would include a main table "Video" as such:

CREATE TABLE Video
(
    VideoPK INTEGER PRIMARY KEY IDENTITY,
    VideoId VARCHAR(100),
    PreviousViewCount BIGINT,
    CurrentViewCount BIGINT
)

This would allow you to do the screen scrape from Youtube, get the VideoID ("v=" parameter from the query string), set the PreviousViewCount to the CurrentViewCount and set the CurrentViewCount to whatever you find from the youtube screenscrape.  If you wanted to get fancy, you could add in a "SnapshotDate DATETIME" column to insure that at least 20 hours has passed since the last snapshot.

Then you could run a query against the Video table, something like:

SELECT VideoId
FROM Video
WHERE CurrentViewCount - PreviousViewCount > 1000

That would work out well for your purposes.

If you really wanted to log historical data, I would throw in a second table to do that:

CREATE TABLE VideoHistory
(
    HistoryID INTEGER PRIMARY KEY IDENTITY,
    VideoPK INTEGER
        FOREIGN KEY REFERENCES Video(VideoPK),
    SnapshotDate DATETIME,
    ViewCount BIGINT
)

Then, when you did the screenscrape, add a new entry into the VideoHistory table.

At least, that's how I would approach it.

Edit:  First, I didn't realize this is SQLite, so that syntax won't work for you.
Secondly, I misunderstood the question

Since you want to view all videos that have had a count of 1000 or more for the past N days, I would stick with something like this:

CREATE TABLE VideoHistory
(
    HistoryID INTEGER PRIMARY KEY AUTOINCREMENT,
    VideoId VARCHAR(100),
    SnapshotDate DATETIME,
    ViewCount BIGINT,
)

The query for pulling the data is going to be slow (due to large amounts of data and correlated subqueries).  But a covering index should help out.

Also, I wouldn't put SearchTerm in the table since your search term probably won't change from one video to another.  I would add a secondary table, such as:

CREATE TABLE SearchTerm
(
    SnapshotDate DATETIME PRIMARY KEY,
    SearchTerm VARCHAR(100),
)

It will help remove the duplicate data.