SSRS report slow on initial query of the day I am trying to fix an issue where an SSRS report is slow for the initial query of the day. I have tried using the solution described here:

Solving issue with first long starting report on SSRS 2008

...to no avail. I know that it seems to be an SSRS issue, but I am not sure if there is another workaround for this behavior. It uses Microsoft's ServerReport.Render method to render the report and dump it to a PDF.

Clarifications from comments:

I created a script like the one in the blog. I had that script run during off hours prior to the start of the business day. I also manually ran the script line by line in PowerShell and that did seem to alleviate the issue; however, that could have just been a placebo as the slowness was reported again by the QA assigned to this issue.

I have manually run the report and experienced the same level of slowness for the initial report query.

I do have a task that runs just before the start of business every day. I created the scheduler with the following line:

schtasks /create /tn "SSRS Recycle" /ru UserName /rl highest /np /sc daily
    /sd 08/01/2011 /st 02:00 /tr "powershell.exe -noprofile
    -executionpolicy RemoteSigned -file c:scriptsSSRSRecycle.ps1"

Is the path case sensitive? My Scripts file was named Scripts. Also, do I need slashes in the path name?

Per the article you provided, give this a shot for another potential workaround:

On the Reporting Services server, modify the RecycleTime value of the rsreportserver.config file, and time this out so a 'recycle' at the app server level of RS occurs just before the start of the regular business day every day - like an hour or so before any production RS reports would typically be run.
Then setup a small dummy Subscription report to run every day at the same time—ensure it has enough time beforehand though for the RS app 'recycle' to complete. Running this non-production small dummy report as the first and initial report each day after the RS app server 'recycle' completes should ensure RS app server stuff loads up before any production reports are run, and production reports shouldn’t experience the problem.

This way your Reporting Services app server recycle operation happens every day at the usual and designated time per the value you set in the config file, then right after that happens (giving ample time for full completion) the dummy report runs before any production reports run for the day, and then every subsequent report for the next 24 hours runs as expected without needing to wait for the RS app server post 'recycle' load operations to complete. You also could leave out the PowerShell scripted solution altogether as well if this works.


Step by step establish a connection between SQL Server 2014 and Oracle 12c I have two servers (both servers are on Windows OS):

Server_A running Oracle 12c
Server_B running SQL Server 2014

I want these servers to be able to see each other, so I can SELECT anything I want in Server_A from Server_B and vice versa.

I only want the ability to run SELECT queries (not INSERT, UPDATE, DELETE or any DDL queries), and also if possible to EXECUTE procedures.

What options or technologies we have for achieving this issue and which one is better?

Tell me if any software I have to install or any configuration have to be set on each of servers.
I appreciate explaining the instructions step by step.

Partial answer (select SQL Server tables from Oracle):

On the Oracle side, you can create a database link to view data in SQL Server.  Since this is a 5 step process, I've also included some debug steps that I use when diagnosing db links from SQL Server in order to track down where the failure is.

On the Oracle machine:

1) Set up an ODBC connection to SQL Server

start => control panel => Administrative tools => Data Sources (ODBC) => System DSN => Add => SQL Server)

Debug: Press the Test Connection button from the ODBC connection window

2) Create an HS file that points to the ODBC connection

In %oracle_home%\hs\admin, create a new file called init<name>.ora.  A typical HS file usually just contains:

HS_FDS_CONNECT_INFO = <name of the ODBC connection name>
HS_FDS_TRACE_LEVEL = OFF

Debug: change HS_FDS_TRACE_LEVEL = ON, then check for the appropriate trace files in %oracle_home%\hs\trace

3) Add an entry to your listener.ora

If there is already a SID_DESC present in SID_LIST_LISTENER you can add a new one just below it.

SID_LIST_LISTENER =
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=...)
      (ORACLE_HOME=...)
      (PROGRAM=...)
    )
    (SID_DESC=
      (SID_NAME=<name of the ODBC connection>)
      (ORACLE_HOME=<full path for oracleHome>)
      (PROGRAM=dg4odbc)
    )
  )

Debug: Check the listener log

c:> lsnrctl reload

4) Add a TNSEntry

<Name> =
   (DESCRIPTION=
      (ADDRESS=(PROTOCOL=TCP)(HOST=<Host>)(PORT=<port>))
      (CONNECT_DATA= (SID=<ODBC connection name>))
      (HS=OK)
   )

Debug c:\> tnsping <tns entry name>

5) Create the db link

create database link <name> connect to DUMMY identified by " " using '<tns entry name>';

Debug: SQL> select * from dual@<dblinkname>;

pre 10g, we used to be able to simply create database link <name> using '<tns entry>'; but the connect as identified by section became required starting in 11g even though whatever you enter isn't used (since it uses the login credentials for SQL Server in the ODBC connection).

It is a little old, but I found the following tutorial very helpful when I was learning this process: Installing and configuring Oracle Heterogeneous Services for SQLServer.

Once you have the database link working, on the Oracle side you could then create views/synonyms/materialized views (complete refresh only) over the SQL Server tables for easier access.

As for the other way around (how to view data in Oracle from SQL Server), i'm not sure since i'm exclusively an Oracle DBA...


Unit testing of stored procedures I've been considering this for quite a long time now.

The basic question is: how to unit test stored procedures?

I see that I can set up unit tests relatively easily for functions in the classic sense (I mean they get zero or more arguments and return a value).  But if I consider a real-life example of a seemingly simple procedure inserting a row somewhere, with a few triggers doing this and that before or after the insert, even defining the boundaries of a 'unit' is quite difficult.  Should I test only the INSERT itself?  That's fairly straightforward, I think–with relatively low value.  Should I test the result of the whole chain of events?  Apart from the question whether this is a unit test or not, designing a suitable test can be quite a strenuous job with lots of additional question marks arising on the way.

And then comes the problem of constantly changing data.  In the case of an UPDATE affecting more than just a few rows, every potentially affected row must be included somehow in the test cases.  Further difficulties with DELETEs and so on and so on.

So how do you unit test your stored procedures?  Is there a treshold in complexity where it gets completely hopeless?   What resources are needed for maintenance?

EDIT One more small question, based on AlexKuznetsov's answer: Or is there a treshold under which it is completely useless?

We've been doing this for almost five years, and we think that explicitly testing modifications is definitely doable, but it is quite slow.
Besides, we cannot easily run such tests concurrently from several connections, unless we use separate databases. Instead, we should test modfications implicitly - we use them to build up at least some of the test data, and verify that our selects return expected results.

I've written an article entitled Close Those Loopholes: Lessons learned from Unit Testing T-SQL, as well as some blog posts

Regarding your question "Is there a treshold in complexity where it gets completely hopeless?", complex modules need tests much more than simple ones.

To simplify maintenance, we generate expected results, and we store them in separate files - that makes a huge difference.


Add Secondary Node to existing WSFC Cluster I need to add new server in my existing WSFC cluster server setup. I need few details on following :
1. I have Windows Server Failover Cluster setup on 2012 R2 with SQL Server 2012, Can I have my new server with Windows Server 2012 R2 with SQL Server 2016?
2. Can I add it on my existing AlwaysOn Availability Group, which is configured with SQL Server 2012?

I have Windows Server Failover Cluster setup on 2012 R2 with SQL Server 2012, Can I have my new server with Windows Server 2012 R2 with SQL Server 2016?

Yes, you can... however, it will not be readable as a secondary and if you ever fail over to it, the SQL Server 2012 instance will no longer synchronize and your databases will be upgraded. Unless you're planning on doing an upgrade, I wouldn't mix and match versions - it gets you nothing.

  Can I add it on my existing AlwaysOn Availability Group, which is configured with SQL Server 2012?

Yes, but do see above for restrictions and limitations. I would only do this if you're starting a rolling upgrade scenario and plan to finish it fairly quickly. Do not leave multiple versions in the same topology for any appreciable amount of time.


CTE column caused an overflow - Order By only! ;WITH
cte_Date ( DateCode_FK ) AS (
    SELECT  DATEADD( DAY,
                1 - ROW_NUMBER() OVER (
                        ORDER BY so1.object_id ),
                GETDATE() )
    FROM    sys.objects so1
    CROSS APPLY sys.objects so2 )
SELECT  TOP 10 d.DateCode_FK
FROM    cte_Date d
ORDER BY d.DateCode_FK DESC;

Nothing an overly interesting query, but i'm receiving an error message if I run it with the ORDER BY clause:

Msg 517, Level 16, State 1, Line 4
Adding a value to a 'datetime' column caused an overflow.

However, without the ORDER BY clause, it runs just fine.  In addition, if I run the query on other catalogs contained in the same instance on the same server, the query runs fine with or without the ORDER BY clause.
I've taken a look at the configuration options and compatibility levels between the affected catalog and a catalog on which the query runs as expected, but have not found anything that might warrant the difference.  Has anybody else run into a similar issue?  I can work around it for now, but would ideally need to be able to fix the problem, whatever it is.
Potential hint - if you have a relatively large number of objects in a catalog ( > 5000 ), you -may- be able to reproduce the error... This is occurring on my largest catalog and it appears that if I include a TOP in the CTE, the ORDER BY issue goes away.

SQL Server does not guarantee the timing or number of evaluations for scalar expressions. This means that a query that might throw an error depending on the order of operations in an execution plan might (or might not) do so at runtime.

The script uses CROSS APPLY where CROSS JOIN was probably intended, but the point is that the potential number of rows over which the ROW_NUMBER is calculated depends on the size of the sys.objects cross product.

For a database with sufficient objects, an overflow for the DATEADD result is an expected risk. For example, this is reproducible using the AdventureWorks sample database, which has 637 entries in sys.objects. The size of the cross product is 637 * 637 = 405,769; the following throws an overflow error:

SELECT DATEADD(DAY, 1 - 405769, GETDATE());

One might argue that there is no need to materialize the result of an expression for rows that are not returned (and therefore not throw an error), but that is not the ways things work today.

Consider:

The highest ROW_NUMBER will give the lowest value for DateCode_FK in the DATEADD(DAY, 1 - ROW_NUMBER()... expression
The presentation ORDER BY is DateCode_FK DESC
Only the first 10 rows in presentation order are required

If the optimizer contained logic to reason that lower row numbers lead to higher DateCode_FK values, an explicit Sort would not be needed. Only ten rows would need to flow through the execution plan. The ten lowest row numbers are guaranteed to produce the ten highest DateCode_FK values.

Regardless, even where a Sort is present, the argument is that SQL Server should not throw an error because none of the ten rows actually returned are associated with an overflow error. As I said above, "that is not the ways things work today".

An alternative formulation that avoids the error (though it is still not guaranteed to do so - see my opening remark), makes the row numbering deterministic, and uses CROSS JOIN:

WITH cte_Date (DateCode_FK) AS
(
    SELECT TOP (10)
        DATEADD
        (
            DAY,
            1 - ROW_NUMBER() OVER (
                ORDER BY
                    so1.[object_id],
                    so2.[object_id]),
            GETDATE()
        )
    FROM sys.objects AS so1
    CROSS JOIN sys.objects AS so2
    ORDER BY
        ROW_NUMBER() OVER (
            ORDER BY
                so1.[object_id],
                so2.[object_id]) ASC
)
SELECT TOP (10) -- Redundant TOP
    d.DateCode_FK
FROM cte_Date AS d
ORDER BY
    d.DateCode_FK DESC;

Paste the Plan


Arabic characters not displaying in oracle database We have a Oracle database 8.1.7 running for many years on Win-2000. We wanted to move the database on a newer machine running Win 2003. I did the following

I made a cold backup of old database.
Installed the oracle database same ver on the new machine and moved
the data files from the old to the new server.
Imported the data dump taken from export (taken from old server) on
the new server.
Without a full test we shifted live to the new server.
Everything was OK on the new oracle server except for the Arabic
characters. I am getting ?? (questions marks) characters in the
Arabic fields.

I checked the nls_database_parameters on both the servers.

Select * from nls_database_paramters;

The old database had: nls_char & nls_nchar = Ar8mswin1256
The new database had nls_char & nls_nchar = We8iso8859p1

I then did the following in the new server:

Changed the character set (nls_char)in the new database to
AR8mswin1256.
Changed the Nls Nchar set to Ar8mswin1256.

All the above done were by an alter database command as below:

SVRMGR> STARTUP MOUNT;
SVRMGR> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SVRMGR> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SVRMGR> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SVRMGR> ALTER DATABASE OPEN;
SVRMGR> AALTER DATABASE NATIONAL CHARACTER SET ar8mswin1256;
SVRMGR> SHUTDOWN IMMEDIATE;
SVRMGR> STARTUP;

I checked nls_database_parameters in the new server both nls_char, nls_nchar was changed to Ar8mswin1256.

I checked the database through our ERP applicatoin but still Arabic characters displaying in ??.

I then checked the registry of new Oracle server. The value of nls_lang is We8iso8859p1
whereas the registry of the old server had value of nls_lang as Ar8mswin1256. I changed
the value in new server to be American_america.Ar8mswin1256. Checked the database still
arabic characters displaying as ??.

The registry of Client has nls_lang as American_america.Ar8mswin1256.

It is a production database. How can I solve the problem of Arabic characters being displayed as ??? in the new server?

I still have the old server database but it is not live. It doesnt contain updated
data.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER SYSTEM ENABLE RESTRICTED SESSION;

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

ALTER SYSTEM SET AQ_TM_PROCESSES=0;

ALTER DATABASE OPEN;

ALTER DATABASE CHARACTER SET INTERNAL_USE AR8MSWIN1256;

SHUTDOWN IMMEDIATE;

 -- or SHUTDOWN NORMAL;

STARTUP;


Database Files through Linked Servers Is there another way to create a script that I can see all database files with their disk space details across databases and across servers?

I can do it through Registered Servers.

But I want to know if I can do it using 1 instance with linked servers for all the servers. My idea is to generate a script that will read each database for each linked server then when I have all the data, it can now be passed to the SQL Server Job.

Thanks.

Powershell is the way. And it make it easier, just install dbatools on one of your admin servers and use get-dbadiskspace

  Get-DbaDiskSpace -ComputerName server1, server2, server3 -Unit GB

if you have list of servers then you can use

$servers = get-content "D:\serverlist.txt"
foreach ($server in $servers) {
  Get-DbaDiskSpace -ComputerName $server -Unit GB
}

You can even dump the above info using invoke-sqlcmd2 into a central db for reporting .


Migration from SQL Server 2012 Ent to SQL Server 2014 STD We recently migrated from a traditional hosting environment with a back end of SQL Server 2012 ENT to an Azure VM running SQL Server 2014 STD. We did all the cool stuff with BPEs on SSD, etc. The problem is, performance is down.  Specs of the old "server" and the new "VM" are identical (in theory, it is azure...).

Question being - I have run the following scripts on staging DBs in the new environment and seen improvement, but I am generally afraid to run these scripts in production.  Is there any real risk in running the following?  It seems to solve the problem and get SQL Server 2014 to work with the data much better.

EXEC sp_MSforEachTable 'ALTER INDEX ALL ON ? DISABLE'
GO

EXEC sp_MSforEachTable 'ALTER INDEX ALL ON ? REBUILD'
GO

EXEC sp_MSforeachtable @command1='UPDATE STATISTICS ? WITH FULLSCAN';
GO

The scripts go over all of your tables, rebuild all the indexes and update their statistics.
The reasons they CAN improve performance is that they reduce the index fragmentation and force SQL Server to generate new execution plans to queries, that may be more suitable.

I'm not sure, though, why you need to run the first command which disables the indexes..

Before running it in production you need to verify they run well on a test system that is similar to the production system. Also do it during a maintenance window and not during the work day.


Microsoft SQL Server Metadata Corruption we have a bad incident right now.

One of our larger SQL databases has broken metadata.

unfortunately the warnings were ignored and they worked a week on the broken SQL database.

Is it possible to reconstruct this database in a trustworthy way?

What experiences do you had with this problem?

Thank you for your answers.

Before you try any fixes, make sure you have backups.

  unfortunately the warnings were ignored and they worked a week on the broken SQL database.

Use old backups to locate a version that does not have the corruption. You will need the most current corruption free backup as you work through the issue.

Optimally you will take a fresh full backup, restore it to a test server, and try to work through the problem on the test server.

When you make it worse than it was to begin with, restore the backup to your test server again and try again.

Hopefully you have been taking regular backups (daily at least) if so make sure that old backups do not age out of the inventory, before you get the problem solved.

If the data is highly valuable you might want to consider contacting a corruption consultant.


how to disable access to mongo shell to anonymous users? i have created users in mongodb . one user among them is admin. but when i do
$mongo
it is allowing me to connect to mongo shell with out me specifiying user and password. what should i do to prevent access to mongo shell for all with out user login? please help

There is no access control enabled with MongoDB by default, so this is something you'll need to configure separately. Fortunately, it's not too difficult thanks to the well-written MongoDB Manual.
Here is the gist:

Start MongoDB without access control (which I believe you've already done)
Connect to the instance (which you've stated you've already done)
Create an administrator account:
use admin
db.createUser(
  {
    user: "adminny",
    pwd: "superSecretPassword!123",
    roles: [ { role: "dbOwner", db: "admin" } ]
  }
)

Re-start the MongoDB instance with access control.
mongod --auth --port 27017 --dbpath /data/db1

Note the --auth option in the startup.
Authenticate as the admin account:
mongo --port 27017 -u "adminny" -p "superSecretPassword!123" --authenticationDatabase "admin"

Important Note About Roles
There are several roles that are available, and choosing the wrong one can result in unnecessary frustration.

Role
Detail

root
This would be a root-level role, allowing the creation of user accounts, databases, and all administration-level actions one might need to do.

dbAdmin
Provides the ability to perform administrative tasks such as schema-related tasks, indexing, and gathering statistics. This role does not grant privileges for user and role management.

dbOwner
The database owner can perform any administrative action on the database. This role combines the privileges granted by the readWrite, dbAdmin and userAdmin roles.

userAdmin
Provides the ability to create and modify roles and users on the current database. Since the userAdmin role allows users to grant any privilege to any user, including themselves, the role also indirectly provides superuser access to either the database or, if scoped to the admin database, the cluster.

userAdminAnyDatabase
Provides the same access to user administration operations as userAdmin on all databases except local and config.

readWrite
Provides all the privileges of the read role plus ability to modify data on all non-system collections and the system.js collection.

You can read more about each of the Built-In Roles in the MongoDB Manual, which is very approachable.


mysql - sum interval dates I would like to sum the hours for each name, giving a total interval between START and END activities. It would be simple if I could subtract from each record the end of the beginning.  For example, Mary started at 13:00 and worked up to 15:00 and started another activity from 14:00 to 16:00. I would like the result to be 3 (she used 3 hours of their time to perform both activities).

Example data

Name    |    START               |    END                 |
-----------------------------------------------------------
KATE    | 2014-01-01 13:00:00    | 2014-01-01 14:00:00    |
MARY    | 2014-01-01 13:00:00    | 2014-01-01 15:00:00    |
TOM     | 2014-01-01 13:00:00    | 2014-01-01 16:00:00    |
KATE    | 2014-01-01 12:00:00    | 2014-01-02 04:00:00    |
MARY    | 2014-01-01 14:00:00    | 2014-01-01 16:00:00    |
TOM     | 2014-01-01 12:00:00    | 2014-01-01 18:00:00    |
TOM     | 2014-01-01 22:00:00    | 2014-01-02 02:00:00    |

Result

KATE    15 hours
MARY     3 hours
TOM      9 hours

You could merge overlapping intervals and count the hours from there (untested):

select name, min(start), end
from (
    select x.name, x.start, min(y.end) as end
    from t as x
    join t as y
        on x.name = y.name
       and x.start <= y.end
       and not exists (
           select 1
           from t as z
           where y.name = z.name
             and y.end >= z.start
             and y.end < z.end
       )
    where not exists (
        select 1
        from t as u
        where x.name = u.name
          and x.start > u.start
          and x.start <= u.start
    )
    group by x.name, x.start
) as v group by name, end;


Confirmation of SQL statement disappeared Until today after every SQL statement eg. CREATE user t identified by t; I had a confirmation of the execution "User created." Now I have nothing (no confirmation) but if I check if the user exists it is positive:

select username from all_users where username='T';

USERNAME
--------
T

How to turn it on again?

If the feedback on SQL*Plus is set to off then you don't get the confirmation messages.

SQL> set feedback off
SQL> create user c##u1 identified by user1;

set feedback on.

SQL> set feedback on
SQL> drop user c##u1;

User dropped.

SQL> create user c##u1 identified by user1;

User created.

Documentation: SET FEEDBACK


How to update Different Date formats to one specific datetime formate I need advise on conversion of date time, below you may see the example data with images for current and expected output

Input Table

Expected Output

You haven't mentioned an RDBMS, so assuming SQL Server, you need to use CONVERT and a style definition to interpret and convert the string into DATETIME:

DECLARE @Table TABLE (ID INT IDENTITY, Country VARCHAR(255), DateString VARCHAR(25))

INSERT INTO @Table
VALUES ('USA', '02/13/2018'),
    ('Germany', '02.12.2018'),
    ('England', '23/02/2011'),
    ('UAE', '08 Jun 2008')

SELECT *,
    CASE
        WHEN [Country] = 'USA' THEN CONVERT(DATETIME, [DateString], 101)
        WHEN [Country] = 'Germany' THEN CONVERT(DATETIME, [DateString], 103)
        WHEN [Country] = 'England' THEN CONVERT(DATETIME, [DateString], 104)
        ELSE TRY_CAST([DateString] AS DATETIME)
    END AS DateValue
FROM @Table

Results

ID  Country     DateString      DateValue
-------------------------------------------------------
1   USA         02/13/2018      2018-02-13 00:00:00.000
2   Germany     02.12.2018      2018-12-02 00:00:00.000
3   England     23/02/2011      2011-02-23 00:00:00.000
4   UAE         08 Jun 2008     2008-06-08 00:00:00.000

Note that any strings that you can't implicitly identify from the Country will be caught by the TRY_CAST and will return NULL if they cannot be accurately converted. These NULLs might be difficult to handle automatically, and this is where data cleansing is important, if you can clean up data at the source and better control the input formats then you will have less of these issues.

CONVERT Styles


SQL Server 2012 - SA account probe On one of my servers (Win 2012 R2), SQL Server 2012 is constantly under attack with the SA account being hit like 10 times per second with different passwords. I don't have an SA account but still I would like to stop this probing as its probably consuming resources. I keep checking the logs and blocking the offending IPs at firewall level manually, still I would like a better solution. SQL Server runs locally with IIS to serve its websites, I only need to connect to SQL Server remotely for database development with SSMS, so at first I thought setting up a VPN but not sure if this is going to play well with SSMS and other services like FTP. As in interim solution where can I stop SQL Server from being visible to the outside world at the firewall by disabling port 1433, or elsewhere? I can then enable/disable this when developing and perhaps just for my IP. Thanks.

So the IIS on this server is intended to be externally accessible from your internal data network but the SQL Server instance on this same server is not meant to allow for external connections except for only through the web interfaces of your applications, correct?

If so, then is not adding an explicit rule in the Windows Firewall on this server to allow only the private IP address ranges to connect via local network on SQL Server ports not acceptable? I assume you trust your internal IP addresses on this port and don't suspect those as the authentication attempts with malicious intent?

Allow the [localhost IP] IIS apps to authenticate to SQL (may need to check site bindings, etc. and/or setup internal DNS pointers too).

On your external side, you should only be allowing the port 80 or 443 to port forward/NAT to the IIS server on those ports only and not all ports too it I would think -- stop at corporate firewall too I guess and only allow the web app ports to go to that server from the external interface with your firewall appliance proxies, etc.


Make a non-ordinal numeric ID in PostgreSQL If I declare a column to be serial in PostgreSql, it will auto-increment in a ordinal sequence.  The end result may not be contigious due to rollbacks and whatnot, but it's more or less ordinal.

I have a user table where the user id's are set as serial, and are thus incremented starting from 1... but I'd rather they weren't, as it seems like asking for a sequence number attack of some kind.

Is there a relatively easy, relatively performant way to create an automatic artificial ID in PostgreSQL such that the ID's are randomly spaced apart, and you shouldn't expect that ID 987654321 follows ID 987654320?

but I'd rather they weren't, as it seems like asking for a sequence number attack of some kind

Aside from the fact that I think you are giving yourself a false sense of security, you can do what you want by reserving the upper half of the identifier for the 'real' sequence and filling the lower half with random bits:

create sequence seq;

create table tab(
  foo bigint
      default (nextval('seq')<<32)+(random()*(1<<32))::integer
      primary key
);

insert into tab select from generate_series(1,9);

select foo, foo>>32 seq from tab;

        foo | seq
----------: | --:
 4294967296 |   1
 8589934592 |   2
12884901889 |   3
17179869185 |   4
21474836481 |   5
25769803777 |   6
30064771073 |   7
34359738369 |   8
38654705664 |   9

dbfiddle here


NOT IN (together with join) query I have two tables :  Part, OrderLine:

Part (PartNum PK, PartDesc, Price)

OrderLine (OrderNum PK, PartNum PK, QtyOrdered)

And I want to find out the parts that were not ordered. I think I have a good idea, but I may be missing something;  I think most likely on the syntax. Please check:

select Part.PartNum
    ,OrderLine.OrderNum
from OrderLine
inner join Part on OrderLine.PartNum = Part.PartNum
where PartNum not in (
        select distinct (OrderNum)
        from OrderLine
        )

Is this correct?

If you want the parts that were not ordered, then you should start with Part, not OrderLine. When you're thinking about sets, that is the only one that could conceivably have the items you're looking for. Part can be a Superset of ordered parts and OrderLine could easily be a Subset of parts.

SELECT PartName
FROM Part
WHERE PartNum NOT IN (
        SELECT PartNum
        FROM OrderLine
        )

More often than not, comparing integers will be far more efficient than strings, so I've changed the WHERE clause to look for PartNums, not PartNames.


Log shipping monitor primary not seeing recent backups, warnings triggered in error I am receiving the Log shipping Primary Server Alert warning in error

In msdb.dbo.log_shipping_monitor_primary the last backup time is 1215 however on that machine there are .trn files that have been created since

In msdb.dbo.log_shipping_monitor_secondary the destination is updating as I'd expect it to

So the .trn files are being created correctly, transferred correctly, and saved correctly, however SSMS appears to be unaware of this hence the warnings that are being triggered in error

Any ideas on why this might be happening? I set up TLS around 3 months ago and apart from a few little problems it's worked fine, this has only occurred today

edit: Rather than the full output as there are a lot of databases being backed up, I've added below the relevant mismatch, please excuse my formatting I'm not sure exactly how to neaten this up

msdb.dbo.log_shipping_monitor_primary

primary_server  primary_database    backup_threshold threshold_alert    threshold_alert_enabled last_backup_file                        last_backup_date        last_backup_date_utc    history_retention_period
DREAM           ACCDRE              60               14420              1                       e:\tlshipping\ACCDRE_20170209121501.trn 2017-02-09 12:15:02.010 2017-02-09 12:15:02.003 5760

msdb.dbo.log_shipping_monitor_secondary

secondary_server secondary_database primary_server  primary_database    restore_threshold   threshold_alert threshold_alert_enabled last_copied_file                                last_copied_date        last_copied_date_utc    last_restored_file                              last_restored_date      last_restored_date_utc  last_restored_latency   history_retention_period
WHSQLDR          ACCDRE             DREAM           ACCDRE              45                  14420           1                       E:\DreamLogShipping\ACCDRE_20170209163001.trn   2017-02-09 16:32:02.397 2017-02-09 16:32:02.397 E:\DreamLogShipping\ACCDRE_20170209163001.trn   2017-02-09 16:34:11.477 2017-02-09 16:34:11.477 4                       5760

You are correct the last backup time is showing 2017-02-09 12:15:02.010 while last copied and restored are much greater 2017-02-09 16:32:02.397 and 2017-02-09 16:34:11.477. So I believe somehow the catalog is not getting updated. There can be two reasons:

This can be a bug make sure  SQL Server 2008 R2 is patched to SP3
There was old logshipping which was configured and was not removed correctly and completely.

I believe the solution here would be to drop and recreate log shipping.


Interactive INSERT / UPDATE function to implement UPSERT I have a function in PostgreSQL 9.1 that I want client applications to be passing a record object and the function will detect if it's an insert or an update and return the operation done, i.e "1 record successfully added" or " Record with PK [wherever] was successfully updated" or "An error occurred, field X cannot be null" or "any error that it gets".

I want to avoid an expensive CURSOR and an even more expensive EXCEPTION clause. I also want it to be optimized for speed. The function should consider concurrency because there will be more than 50 clients connected to the database at the same time, each updating/inserting (calling the same function). This is what I have currently:

CREATE OR REPLACE FUNCTION netcen.fun_test(myobj netcen.test)
  RETURNS text AS
$BODY$
DECLARE
myoutput text :='Nothing has occured';
BEGIN
    update netcen.test set
    tes=myobj.tes,
    testname=myobj.testname  WHERE testkey=myobj.testkey;
IF FOUND THEN
        myoutput:= 'Record with PK[' || myobj.testkey || '] successfully updated';
        RETURN myoutput;
    END IF;
    BEGIN
        INSERT INTO netcen.test values(myobj.testkey,
    myobj.tes,
    myobj.testname);
    myoutput:= 'Record successfully added';
    EXCEPTION WHEN null_value_not_allowed THEN
        RAISE NOTICE 'something has just occured';
    myoutput:= 'A field was null';
    WHEN not_null_violation THEN
        RAISE NOTICE 'something has just occured';
    myoutput:= 'A field was null';
    WHEN unique_violation THEN
    RAISE NOTICE 'something has just occured';
    myoutput:= 'A field was null';
    END;
    RETURN myoutput;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

I know this function can be simplified to a simple and shorter writable CTE function of SQL language. But those have a race condition. And I don't know how to capture errors and report the operation done as output from it.

Can this function be simplified or complicated but handle all my concerns?

If you want to catch NULL violations as well you have to cover the UPDATE as well, which makes the function a little more expensive.

SET search_path = netcen;

CREATE OR REPLACE FUNCTION fun_test(myobj test, OUT myoutput text)
  RETURNS text AS
$func$
BEGIN
   UPDATE test
   SET    tes = myobj.tes,
          testname = myobj.testname
   WHERE  testkey = myobj.testkey;

   IF FOUND THEN
      myoutput := 'UPDATE successfull. testkey = ' || myobj.testkey;
      RETURN;
   END IF;

   INSERT INTO test
   SELECT (myobj).*;
   myoutput := 'INSERT successfull. testkey = ' || myobj.testkey;

EXCEPTION
WHEN null_value_not_allowed THEN
   RAISE NOTICE 'null_value_not_allowed occurred.';
   myoutput := 'A field was null.';

WHEN not_null_violation THEN
   RAISE NOTICE 'not_null_violation occurred.';
   myoutput:= 'A field was null.';

WHEN unique_violation THEN
   RAISE NOTICE 'unique_violation occurred.';
   myoutput:= 'Duplicate value.';
END
$func$ LANGUAGE plpgsql;

Using an OUT parameter and made some simplifications and clarifications.

I would just let the regular EXCEPTION happen. People are not supposed to enter NULL values for NOT NULL columns. This way, the UPDATE can run outside the EXCEPTION block.


Is it possible to execute an "update" trigger if all columns except one have changed? Assume I have the following table:

CREATE TABLE data (
    key TEXT primary key,
    some_interesting_value TEXT,
    inserted TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    last_seen TIMESTAMP WITH TIME ZONE
);

Then I can create a trigger to set the "updated" column like this:

CREATE TRIGGER set_updated_col
    AFTER UPDATE ON data
    FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.*)
    EXECUTE PROCEDURE set_updated_timestamp();

The last_seen column in this case contains a timestamp which is updated every time a process is run (with some conditions). However, this value is not interesting to track table updates. In my case I have many such tables. They are all contain information about physical network devices, and the "last_seen" value is important for each entity.

The trigger above will set the "updated" timestamp even if only the "last_seen" value is modified.

In my case I want to avoid this.

I could manually name all columns in the WHEN clause, but then, if I add a column to the table, there is a risk that this gets forgotten in he trigger.

Is there a way to "remove" the last_seen column from OLD.*?

As code example:

-- This should NOT trigger a change to `updated`
UPDATE data SET last_seen=NOW() WHERE key='foobar';

-- this SHOULD trigger a change to `updated`
UPDATE data SET some_interesting_value='foo', last_seen=NOW() WHERE key='foobar'

I'm not sure about performance impact, but you can convert row to jasonb, remove field, and compare json objects.
So changing WHEN (OLD.* IS DISTINCT FROM NEW.*) to
when (row_to_json(old)::jsonb - 'last_seen' is distinct from
 row_to_json(new)::jsonb - 'last_seen') will do the job.

Also, if you want to modify NEW.updated , it should be BEFORE UPDATE  trigger, not AFTER UPDATE.


moving data from one db to another using SSIS I am very new to SSIS and need to work out how to setup a transfer from table A database A to table B database B.

table A contains many more fields than I need in table B, so the process needs SQL so I can specify fields to take. (I cant just lift the table from database A and drop it into database B).

If I were doing it in SQL I would just select field1, field2, field3 from table a and update table b with the results.

Can anyone offer me any pointers on how to do this in SSIS? I have had a good google around but can't find a definitive answer.

Many thanks

Create regular data flow with 2 components - OLE DB Source and OLE DB Destination (I assume you are using MS SQL Server, in general, use whatever components your company uses to connect to the DB).

In case of 2 DBs, create 2 connection managers, each pointing to its DB. Point OLE DB Source to first connection manager configured to point to source of data, and OLE DB Destination to second connection manager configured to point to destination DB.

Now point OLE DB Source to the source table in source DB, leave all the fields intact. Connect source and destination components with green arrow originally going out of source component. Now point OLE DB Destination to the destination table in target DB. Double-click destination, go to mappings and make sure they are correct (SSIS tries to map automatically using strick name matching), otherwise (in case names are different) connect source and destination fields manually. That's it, you just don't provide mappings for the fields which cannot be accommodated by destination table.

Alternatively, you can leave out the columns you don't need at source component - double-click it, go to Columns and uncheck columns you don't need.


Static Data Masking does not appear in SSMS 18 preview 6 I installed SSMS version 18 preview 6 and am using it with SQL Server 2017. I want to test the Static Data Masking feature, but the option does not appear.

It was removed from the final version of SSMS 18. Interestingly, no explanation deemed necessary to be given as why it was taken out or will this feature be coming soon.

From SQL Server Management Studio (SSMS) 18.0 released for general availability by Brad Groux:

  Removed static data masking (preview) feature – While this feature was available in previous previews, it did not make it into the GA version of SSMS, unfortunately.


How to find out who is active and locking the oracle database? At work, we are sharing oracle credentials among many developers. Often time team runs heavy adhoc  queries on the database and it is causing lot of trouble to oracle server. We are working to fix this issue permanently by creating readonly replica instance. Mean time I wanted to find out if I can quickly identify who are all currently in session by looking at windows user id, and/or machine names etc, so that we don't need to maintain too many credentials for database.

Also is it great if I can find out which user/query is creating trouble for oracle server. For example one query caused temp space outage in server because we had not limited the resource for the account.

Please try the below query to see if it's helps:

  select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;


How do I make a locale available in Postgresql? I needed to add new locales to our postgresql server (PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit ).
As the locales were not yet installed on Ubuntu, I added them with

sudo locale-gen fr_FR.UTF-8

the locales do show now with

locale -a

I restarted the postgresql server, but the locales aren't visible in postgresql. I cant find themp in select * from pg_collation;

When I try to create the collation manually with create collation fr_FR (LOCALE=‘fr_FR.utf8’), I get this error

ERROR:  could not create locale "‘tr_tr.utf8’": Success

Am I missing a step ?

It looks like a syntax problem. This statement:

create collation fr_FR (LOCALE=‘fr_FR.utf8’)

has Unicode non-ASCII quotes LEFT SINGLE QUOTATION MARK and RIGHT SINGLE QUOTATION MARK instead of the normal ASCII double-quote. These fancy quotes are not delimiters for the SQL syntax, so they get interpreted as being part of the locale's name.

Try with:

create collation fr_FR (LOCALE="fr_FR.utf8");


Repairing buggy query server-side – without changes in the app More external apps are accessing our ERP SW database. As we were adding some columns into our structure, some queries from the external app become buggy because they are not specifying the targeted table in the query and errors out with Ambiguous column name… message.
The app vendor is inaccesible. We cannot repair the query from the app point nor change our structure (it’s for more customers).
Can we do something at the database server level?

You are facing the error Ambiguous column name… because the app is quering your database this way:
SELECT
[Name],
[Salary],
[Name],
[Age]
FROM TABLE1 A
INNER JOIN TABLE2 B
ON A.EmployeeID = B.EmployeeID

As you can see there are two [Name] in the SELECT. You will resolve the issue by recreating the query this way:
SELECT
A.[Name],
[Salary],
B.[Name],
[Age]
FROM TABLE1 A
INNER JOIN TABLE2 B
ON A.EmployeeID = B.EmployeeID

Yes, you are facing this because, as you said, you are adding some columns in your DB. You have 2 choices:

Contact the vendor and make it aware of the Ambiguous column name… error
Recreate the structure of your database and avoid using column names that were already used.


Character set warning in PXC logs I am using PXC 5.6 (Percona-XtraDB-Cluster-56-5.6.41-28.28.1.el7.x86_64) and my log file is flooded with this warning:

  [Warning] Client failed to provide its character set. 'latin1' will be
  used as client character set.

how can I disable this warning or at least set character by default?

It looks as though this is related to a MySQL bug, here is one discussion https://www.percona.com/forums/questions-discussions/mysql-and-percona-server/13590-character-set-warning

Here is the bug report: https://bugs.mysql.com/bug.php?id=72543

A poster on StackOverflow reported that after updating the connector the issue was resolved, might this apply here? https://stackoverflow.com/questions/...ws-server-2012

If you are using HA Proxy, this may also be relevant, and there is a workaround at the end: https://jira.percona.com/browse/PS-1518


SQL Server backup, attach to BAK file? I have a SQL Server database with a full text index. When I created the app that uses it, I made a backup of the database and I ship the .BAK file with the app. As part of the app install it does a restore of this .BAK file. I like this option because it auto-creates all of the tables for me, with the default rows some tables need, and includes the full text index as well. Nice and simple.

Now I need to add a backup/restore feature, and the end-user wants it to work more like an MS Office app than a client/server app.  He thinks of it as a file, and just wants to backup to a file, and then restore whenever he wants... not your typical SQL Server backup, and not automated either, just simple "copy" backup, no appends/backup sets/etc.

I'm thinking of using BACKUP DATABASE TSQL command, and he doesn't mind that it only saves to a folder on the server. Then restore database TSQL command to restore -- what I currently use for my initial install.

I ran in to one problem though, and this is my question. I have a version number that I keep in a row in a table that I use to avoid old apps connecting to new databases and vice-versa.  I'd like to check this version before allowing the restore. I know if it were an .MDF file I could attach to it and check before restoring, but can I do this with a backup (.BAK) file?

Am I missing a better backup/restore method that would work better for my design? For example, should I detach the .MDF file and copy it and the log file, as the backup, and then attach it as the "restore" process? Will that preserve everything the way backup/restore does, including the full text index?

I'm using SQL Server 2008 Express and my app is C# .NET 3.5.

Edit: Backup does not need to happen while other users are in the database, it is fine if exclusive access is required, it is just a small office with a few people that can easily shutdown if needed.

First there's no way using the native tools to mount a backup file and read the data in it.  That said, what you can do is write your version information into the description of the backup using the DESCRIPTION keyword.  Then read this information using the RESTORE DATABASE WITH HEADERONLY to get the information and make sure that they have the correct version.


Can SHOW TABLE STATUS query performance on MySQL be improved ? - Amazon RDS I have a fairly large database on the Amazon Relational Database Service (RDS) running on MySQL 5.1.57.  There are 900+ tables, so that running the "SHOW TABLE STATUS" query can take up to 30 seconds.

We have an integration with the UPS Worldship software connecting through ODBC.  It seems that this software running the SHOW TABLE STATUS; query before every function, such that clicking a button requires a 45 second wait.  We had the same database running prior to the new year on dedicated hardware, and we didn't have this problem.

My questions are these:

Is there a way to improve the performance of this query ?
What might be causing such a problem ?

You may want to disable the variable innodb_stats_on_metadata.

According to the MySQL Documentation on innodb_stats_on_metadata

  When this variable is enabled (which is the default, as before the
  variable was created), InnoDB updates statistics during metadata
  statements such as SHOW TABLE STATUS or SHOW INDEX, or when accessing
  the INFORMATION_SCHEMA tables TABLES or STATISTICS. (These updates are
  similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does
  not update statistics during these operations. Disabling this variable
  can improve access speed for schemas that have a large number of
  tables or indexes. It can also improve the stability of execution
  plans for queries that involve InnoDB tables.

Since you are using Amazon RDS, ask your DB Administrators to disable it. If your DBs are in a Shared Resource, my condolences...

UPDATE 2012-07-25 14:45 EDT

If you can download the RDS CLI, you can adjust innodb_stats_on_metadata in a customized DB Parameter Group. You will have to do the following:

mysqldump data out of RDS Server
destroy DB Server Instance
create DB Server Instance using your custom DB Parameter Group adjusted for innodb_stats_on_metadata
reimport the mysqldump into new DB Instance

Since Amazon RDS does not give away SUPER privilege, that's the only way to change a GLOBAL static option.

UPDATE 2012-07-27 16:34 EDT

Without SUPER Privilege, you can modify a DB Parameter Group for a given MySQL RDS Instance, and launch a reboot with one of the following:

rds-reboot-db-instance <DBInstanceName>
Click Reboot Button in the RDS Management Console


Cross-cutting I18N native support in databases Does a database engine with native cross-cutting internationalization support exist, able to store data like this?

SET LOCALE='en'
SELECT * FROM BOOKS

+------+-----------------------+----------------+
|  ID  | TITLE                 | AUTHOR         |
+------+-----------------------+----------------+
|   1  | The Lord of The Rings | J.R.R. Tolkien |
+------+-----------------------+----------------+

SET LOCALE='it'
SELECT * FROM BOOKS

+------+-------------------------+----------------+
|  ID  | TITLE                   | AUTHOR         |
+------+-------------------------+----------------+
|   1  | Il Signore degli Anelli | J.R.R. Tolkien |
+------+-------------------------+----------------+

i.e. able to store values in a third dimension, such as a locale in this case?

Clarification

In the example above, just TITLE should be affected by SET LOCALE by a special definition in this kind of database (I imagine something like LVARCHAR); so, adding a discriminator column would not be feasable since all the non-localized columns will be duplicated.

With PostgreSQL you can simulate something like using views and environment variables.

create table books_ids(
  book_id serial not null primary key,
  author text not null);

create table books_titles (
  book_id int not null references books_ids,
  lang char(2) not null,
  title text not null,
  isbn text null,
  primary key(book_id, lang));

insert into books_ids (author) VALUES ('J.R.R. Tolkien');
insert into books_titles (book_id, lang, title)
  values (1, 'en', 'The Lord of The Rings'), (1, 'it', 'Il Signore degli Anelli');

create view books as
  select b.book_id as id, t.title, b.author
  from books_ids b
  join books_titles t on (t.book_id = b.book_id and t.lang = current_setting('my.lang'));

-- in English
set my.lang='en'; select * from books;

-- in Italian
set my.lang='it'; select * from books;


Why does BETWEEN use a btree index but "element is contained by" range operator (<@) does not? I've got a table with a utc timestamptz which has a "btree" index on the utc column:
CREATE TABLE foo(utc timestamptz)

CREATE INDEX ix_foo_utc ON foo (utc);

This table contains about 500 million rows of data.
When I filter utc using BETWEEN, the query planner uses the index as expected:
> EXPLAIN ANALYZE
SELECT
   utc
FROM foo
WHERE
    utc BETWEEN '2020-12-01' AND '2031-02-15'
;

QUERY PLAN
Bitmap Heap Scan on foo  (cost=3048368.34..11836322.22 rows=143671392 width=8) (actual time=12447.905..165576.664 rows=150225530 loops=1)
Recheck Cond: ((utc >= '2020-12-01 00:00:00+00'::timestamp with time zone) AND (utc <= '2031-02-15 00:00:00+00'::timestamp with time zone))
Rows Removed by Index Recheck: 543231
Heap Blocks: exact=43537 lossy=1818365
->  Bitmap Index Scan on ix_foo_utc  (cost=0.00..3012450.49 rows=143671392 width=0) (actual time=12436.236..12436.236 rows=150225530 loops=1)
Index Cond: ((utc >= '2020-12-01 00:00:00+00'::timestamp with time zone) AND (utc <= '2031-02-15 00:00:00+00'::timestamp with time zone))
Planning time: 0.127 ms
Execution time: 172335.517 ms

But if I run the same query using a range operator, the index isn't used:
> EXPLAIN ANALYZE
SELECT
   utc
FROM quotation.half_hour_data
WHERE
    utc <@ tstzrange('2020-12-01', '2031-02-15')
;

QUERY PLAN
Gather  (cost=1000.00..9552135.30 rows=2556133 width=8) (actual time=0.179..145303.094 rows=150225530 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Parallel Seq Scan on foo  (cost=0.00..9295522.00 rows=1065055 width=8) (actual time=5.321..117837.452 rows=50075177 loops=3)
"Filter: (utc <@ '[""2020-12-01 00:00:00+00"",""2031-02-15 00:00:00+00"")'::tstzrange)
Rows Removed by Filter: 120333718
Planning time: 0.069 ms
Execution time: 153384.494 ms

I would have expected the query planner to realise that these are doing the same operation (albeit that <@ is right-hand exlusive and BETWEEN is inclusive.)
So why are these query plans so different? (Forget about asking why the sequential scan query completes more quickly??!!)

My Postgres version:
"PostgreSQL 10.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit"

An index can only support operators that belong to its operator class.
SELECT ao.amoplefttype::regtype,
       op.oprname,
       ao.amoprighttype::regtype
FROM pg_opfamily AS of
   JOIN pg_am AS am ON of.opfmethod = am.oid
   JOIN pg_amop AS ao ON of.oid = ao.amopfamily
   JOIN pg_operator AS op ON ao.amopopr = op.oid
WHERE am.amname = 'btree'
  AND ao.amoplefttype = 'timestamptz'::regtype;

       amoplefttype       | oprname |        amoprighttype
--------------------------+---------+-----------------------------
 timestamp with time zone | <       | date
 timestamp with time zone | <=      | date
 timestamp with time zone | =       | date
 timestamp with time zone | >=      | date
 timestamp with time zone | >       | date
 timestamp with time zone | <       | timestamp without time zone
 timestamp with time zone | <=      | timestamp without time zone
 timestamp with time zone | =       | timestamp without time zone
 timestamp with time zone | >=      | timestamp without time zone
 timestamp with time zone | >       | timestamp without time zone
 timestamp with time zone | <       | timestamp with time zone
 timestamp with time zone | <=      | timestamp with time zone
 timestamp with time zone | =       | timestamp with time zone
 timestamp with time zone | >=      | timestamp with time zone
 timestamp with time zone | >       | timestamp with time zone
(15 rows)

There is no <@ operator in there, so a B-tree index cannot support this operator.
GIN indexes can support <@, but not with the constant on the right side.
You will have to rewrite the query and use BETWEEN.
Remark: There is not a fundamental reason why it is like that, it is just how indexes work in PostgreSQL. It is even possible to write an optimizer support function that would do exactly that, but your use case is so exotic that PostgreSQL doesn't want to spend optimizer time and development effort on it.


Structuring change history data in MongoDB / NoSQL I have a Chart on which I need to track changes; the Chart has several properties. A Chart also has a long list of items.

So in C#, my Chart object looks like this:

public class Chart {
    public int Id { get; set; }
    public string Title { get; set; }
    ...
    public List<ChartItem> Items { get; set; }
}

Or in JSON, my Chart looks like this (I wrote this JSON by hand, so it could be wrong):

{
    "Id": 1,
    "Title": "Test Chart",
    "Items": [
        {
            "Id": 222,
            "Title": "Test Item",
            "Price": 112.34
        }
    ]
}

So my question is as follows:

Should I log an entire chart with all the items as a single document, every time there is a change? (Although I read there may be a limit on document size, so this could be a problem.)

OR

Should I log Chart changes as one type of document and Chart Item changes as a separate type of document? This would allow me to store changes to chart items as a single "line item" document without having to save the entire Chart every time there is a change to a single chart item.

OR

Is there a better way?

As per @DANIEL WATROUS Blog here The size of documents and the frequency with which they are changed must factor in to the retention of historical data.

  Should I log an entire chart with all the items as a single document,
  every time there is a change? (Although I read there may be a limit on
  document size, so this could be a problem.)

As per your JSON code

{
    {"Id": 1},
    {"Title": "Test Chart"},
    {"Items": [
        {
            "Id": 222,
            "Title": "Test Item",
            "Price": 112.34
        }
    ]
}}

As MongoDB BOLhere The maximum BSON document size is 16 megabytes.

Each document in MongoDB has a specifically allocated size when it is created. Updates that increase the size of the document must allocate a new document large enough to accommodate the updated document on disk and move the document. This can be an expensive operation to perform, especially at high volume.

  Should I log Chart changes as one type of document and Chart Item
  changes as a separate type of document? This would allow me to store
  changes to chart items as a single "line item" document without having
  to save the entire Chart every time there is a change to a single
  chart item.

There are a few options when it comes to dealing with uniquely identifying items data. One is to calculate a unique value at the time an object is placed in history. This could be a combination of the top level object ID and a sequential version number. Another is to generate a hash when the object is loaded. The problem with the second approach is that queries for specific date objects become more complex.

For your further ref here


How do I copy from one meta_key to another meta_key in same table? I am trying to copy the address to the billing address. Both are located in the wp_usermeta table†. I picture something that looks similar to this:

update wp_thafxavhs9_usermeta(my table)
set billing_address_1(meta_key want copied to) = address(meta_key want copied from)

but I know I'm missing something because they are meta_keys. Also, I am trying to do this for all users and I'm using phpMyadmin

Do I need to do anything special to have it copy for all users? But yeah, that's all I'm trying to do. WooCommerce pulls from the billing_address field to populate the forms, so I figured it would be easy since I already have the address.

When I tried:

update 'wp_thafxavhs9_usermeta'
set billing_address_1 = address

I got the following error message:

  1054 - Unknown column 'billing_address_1' in 'field list'

Clearly the error message says the field billing_address_1 isn't there.   (This I just copied from the database, unless I typed something wrong in my sql...)

I have updated my registration form that new members fill out when joining the site, so it captures all that for new members.

Here is the table schema:

SHOW COLUMNS FROM wp_thafxavhs9_usermeta;

Field        Type               Null        Key     Default     Extra
umeta_id    bigint(20) unsigned NO          PRI     NULL        auto_increment
user_id     bigint(20) unsigned NO          MUL     0
meta_key    varchar(255)        YES         MUL     NULL
meta_value  longtext            YES                 NULL

†: My table names somehow magically changed when we switched over from some crappy shared hosting to wordpress hosting. Not sure how that all happened, but it works.

Based on the table structure what you need is a self-join in your update. To find lots of examples type mysql self join update into your favorite search engine. I think the basic structure you're looking for is:

UPDATE 'wp_thafxavhs9_usermeta' AS 'billing'
INNER JOIN 'wp_thafxavhs9_usermeta' AS 'address' ON
   'billing'.user_id = 'address'.user_id AND 'address'.meta_key = 'address'
SET
   meta_value = 'address'.meta_value
WHERE
   'billing'.meta_key = 'billing_address_1'

Of course don't run that or any SQL you find on the interwebs on production without testing it in a safe environment first.  That might not give you exactly what you want but should give you a solid start.

Also you should keep in mind that this won't keep the columns synced as new users are added. In order to do that you'll need to change your insert/update logic or add a trigger. Personally, I would recommend changing your insert logic over the trigger, but that isn't always practical.


Does it make sense to use OPTION(RECOMPILE) in dynamic SQL? I would like to find out if using OPTION(RECOMPILE) is beneficial when executing dynamic sql queries?
There are a few points to keep in mind:

There will be hundreds (maybe even thousands) of different dynamic queries created.
About 1/4 of them will be used repeatedly with "higher-frequency". Let's say several hundred times a day (that's several hundred executions of 1 specific query per day).
The rest will be used sporadically, might be a few times per day, might be a few times over a week.
Even if a query should be "composed" basically the same way (ie. joining the same tables etc.) as the one before it. There is also the question of input variables that come into play.
At minimum 2 objects (tables, views, joins etc.) will come into play in every query. At most it will be dozens of objects, in average it should be around 10 (this should loosely corelate with the 1/4 "high-frequency" queries)
Purpose is to optimize for speed.

Thank you beforehand for any tips and recommendations.
DB used: MS SQL 2019

No.
Now that I got that out of the way, , there's no need to use query hints until there is a need to use them. In other words, don't proactively use them, only reactively use them after you've measurably determined your query's use case will benefit from them.
Now to the specifics. OPTION (RECOMPILE) (and RECOMPILE hints) is useful in edge case parameter sniffing issues where there's no alternative fix, as  discussed in this Kendra Little article. Until you determine you have an otherwise unsolvable parameter sniffing issue with your Dynamic SQL, then there's no need to use the OPTION (RECOMPILE) query hint. In fact it'll only slow your queries down, possibly by an insignificant amount of time, but perhaps not when you have the same query recompiling over and over at a high frequency.
The reason SQL Server caches query plans is to reduce latency on repeated runs of the same query, when possible, by not having to recompile and re-decide on what execution plan to use to serve your queries. Also your Dynamic SQL will already elicit a recompile every time any change is made to it from the last time it ran or when it's no longer in the plan cache, as cached query plans are only re-usable by the same exact query (even a comment change will generate a new plan) since the query text is hashed as the key to the cached plan.
All of that being said, I just want to reiterate that query hints are not necessarily bad, and don't let my hard no above scare you away. It's just that query hints are meant to be used in more edge case scenarios, not your normal use cases, and improper use of them can actually severely impact performance. OPTION (RECOMPILE) is a good tool to keep in the back of your mind for when you're in a parameter sniffing performance jam as a temporary quick fix, or when there's an actual valid case to use it. But I would definitely not recommend just using it without the testing to back it up, and there's nothing special about Dynamic SQL that automatically makes sense to use RECOMPILE hints with it.


Strategy for standby reporting with different database versions I am trying to come up with solution for restoring database with one full backup file and then with hourly transaction logs that are going to be created each hour thru FTP site.
So, here is a scenario.
I connect to data provider's FTP site, and get a full backup file first and restore database with 24 transaction logs on a daily basis. So, I will do a nightly run (around 12 am) to update all transaction logs from previous day's data.
I learned that there is an option called Standby, so I tried full back up with standby, but it appears that I cannot do this because database needs to be upgraded. I got this error when I tried Restore Full backup with "Standby" mode:

This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY confirms that. Now, as standby option is not an option, what should be the way to read data from the database if we are trying to continue doing the transaction log restore everyday (with 24 files)?
I am coming back to the way of restoring database using NORECOVERY for full backup and then for all other transaction log files (because with RECOVERY mode, I cannot restore additional ones later).
But, I need to access database to read data on a regular basis and to query the data. I am not sure if there is a way to read the data (during work hour) when we try to update database with transaction logs. It is not like we could change from RECOVERY mode during work hours to NORECOVERY mode during nightly batch, correct? Do I have to set up like two separate databases (one for restore & other one to somehow grab data from other database). I am not sure that is possible.
What should I do?
Basically, we need to get the updated data from other company thru FTP. After we get the data, developer needs to use it to create reports.

The easiest options are to either upgrade the source instance to the same version as the target (or otherwise ensure the database engine versions are compatible), or perform a restore from a full backup every day to refresh.
For example, say the source was SQL Server 2008 and the target was SQL Server 2012. Upgrading the source installation from SQL Server 2008 to SQL Server 2012 would help ensure a database version match so STANDBY could be used.
Applying changes to a lower SQL Server version will require a bit of development effort.


Why is a new user able to create tables in PostgreSQL? I followed two tutorials to create a DB with:

a fully privileged user link
a read-only user link

I then got a tip from CJ Estel's tutorial stating that "you may have inherited the ability to create tables even though we never explicitly gave it to our new user". Sure enough, the read-only user is able to create and own tables!

CJ Estel has pointed the root cause very well, namely a template database. But the ability to create tables undermines most tutorials you get from googling "read only user postgres" including one hosted on postgresql.org. Your user has more than read-only privileges!

Why does a new user have this ability? After revoking this privilege, is the database truly read-only for that user?

TL;DR: New users can create tables in the public schema because people complained that it was too hard when they couldn't.

If you dislike the defaults, you should probably create a new template database with the initial configuration that you want. For example, you might:

DROP SCHEMA public;

or

REVOKE ALL ON SCHEMA public FROM public;
GRANT USAGE ON SCHEMA public TO public;

in your template.

If you wish the public user to have no rights on a database, you should additionally:

REVOKE ALL ON DATABASE mydbname FROM public;
GRANT CONNECT ON DATABASE mydbname TO public;

so that the public user cannot create schemas or use temp tables.

Personally, if I was designing this, I'd give users the TEMP right on the database by default, but not CREATE (schemas in database) or CREATE (tables in the public schema). I'd reserve those for the owner.

They're choices that were made a long time ago, though, and it's pretty hard to change them now.

As it is, there are regular complaints that it's too hard to get started with PostgreSQL because you have to create a user account and often want to create a database too. Why don't we just auto-create them and default to 'trust' as the auth-mode to make it easy? Why doesn't the postgres user default to having the password postgres? Why don't we just auto-create users if they exist in the OS? etc.

There are some genuine usability problems for new users - in particular, most people have no idea what peer auth is, or why just running psql after installing PostgreSQL tells them there's no user by the name they're logged in as.

It's also messy that pg_hba.conf is a config file, but users are created at the SQL level. This split confuses users.

Lots of things, though, are compromises between secure defaults and easy defaults where the project isn't ever going to make everybody happy.


Threshold for sort spill and random cardinality estimates I wanted to test tempdb spill warnings so I run the following script on SQL Server 2014:

USE tempdb

IF OBJECT_ID('tempdb..tblTest') IS NOT NULL DROP TABLE tblTest

CREATE TABLE tblTest
(
          c1 INT         PRIMARY KEY CLUSTERED,
          c2 INT        ,
          c3 CHAR (1000)
);

GO
SET NOCOUNT ON;

BEGIN TRANSACTION;

DECLARE @i AS INT;

SET @i = 1;

WHILE @i <= 10000
          BEGIN
                    INSERT  INTO tblTest (c1, c2, c3)
                    VALUES              (@i, @i, 'a');
                    SET @i = @i + 1;
          END

COMMIT TRANSACTION;

GO
UPDATE STATISTICS dbo.tblTest
GO
SET STATISTICS XML ON;
GO
--no tempdb spill (SQL Server 2014)
--in sql server 2012 always different "Estimated number of rows" if you run the whole script several times
SELECT   *
FROM     tblTest
WHERE    c1 <= 5948
ORDER BY c2
OPTION (MAXDOP 1);

GO
SET STATISTICS XML OFF;

SET STATISTICS XML ON;
GO
--no tempdb spill (SQL Server 2014)
SELECT   *
FROM     tblTest
WHERE    c1 <= 5949
ORDER BY c2
OPTION (MAXDOP 1);

GO
SET STATISTICS XML OFF;

(core of the query was based on one material from MS)

1) The first thing I am curious about is what causes the tempdb spill during sort operation at this specific level of c1 column. All the estimates are correct and count of the read page is the same for both queries. So why is the second query spilled? (In another words why memory grant for the later query is so much higher).

2) I have tested this query on SQL Server 2012 and got very interesting behaviour as well. Firstly I was unable to get to the same thresholds, so I run the script repeatedly and noticed that estimated number of rows is always different than it was in the previous run. My question is why is the estimated number of returned rows always different when I repeatedly run the very same script (creating, inserting a updating it's own statistics via full scan)?

The amount of memory needed to perform a sort is not as simple as computing the raw size of the input data. The main sorting algorithm used by SQL Server is a variation on merge sort, which includes extra steps like key normalization to ensure all combinations of data column types can be sorted efficiently. Due to these extra steps, it is not easy to predict the amount of memory that would be needed to avoid a spill at runtime; the memory grant is an estimate. Spilling is part of the design. You should not be overly concerned by small spills in edge cases like this.
SQL Server 2014 includes a new cardinality estimation module, which is used if the context database is compatibility level 120. You use tempdb for your test, which will be 120 level by default. You can get the pre-2014 CE behaviour by changing the compatibility level, or using trace flag 9481. Regarding the different number of estimated rows, the statistics may be sampled unless you use the WITH FULLSCAN option with UPDATE STATISTICS.


Join multiple PostgreSQL tables that have both common and individual columns Processing speed matters not for my needs. What I want to do is meant to be done once and then stay that way. It should be an automated process though, which can be performed by sending SQL commands to the database with psql.exe.

General problem

I have several tables that I want to merge into a single table. Assume that I can't know the names of all the columns (there are A LOT of them). What I do know is that some columns are the same for every table while other columns are individual for each table. If necessary I can specify the columns that are common to all tables, but I can't specify the columns which are common only to a few tables.

You may assume that I know the names of all the tables that I want to merge. However, a solution that doesn't assume that would be superior.

If you can give me an answer based on this then I may be able to proceed to solving the specifics of my problem on my own. However, I'm adding the specifics in case you have knowledge that may help based on that.

Specifics

The tables that I want to merge into one were generated by ogr2ogr (GDAL 2.1.0) from data in the S-57 format. I've already imported the base .000 file and updated with the available .00x files. I'm currently testing with only one .000 file, but I intend to append the import with data from several files. This shouldn't really affect the solution unless I've missed some ogr2ogr option that automatically imports everything into a single table.

I asked a different but related question on the GIS stackexchange.

Problems I've faced

I can't use UNION because the tables don't have the same columns. I also tried FULL OUTER JOIN, but it doesn't work because some columns are defined in multiple tables.

Clarifications

I want to merge several tables into one.
These tables have some columns in common. I can name these if needed.
These tables have some columns that are not in common. There's too many of these for me to name.
The new table should have all the columns, but no duplicate columns.
I want all rows from all the original tables inserted into the new table.

A solution where all the tables to be merged are known is good. A solution for any number of tables with unknown names is better.

The specifics of my problem is that I'm working with the S-57 format and importing to the database using ogr2ogr. These specifics are not necessarily important for the solution, but I included it in the question because someone might know something that I've missed that could have solved this already in the import.

I've tried using UNION and FULL OUTER JOIN, but it did not work.

Community wiki answer:

SELECT * FROM a
NATURAL JOIN b
NATURAL JOIN c
...
NATURAL JOIN z;

...will give you a result, without any prior knowledge of column names.

I got it to work as intended by writing

SELECT * INTO new_table
FROM a NATURAL FULL OUTER JOIN b;

I needed the FULL OUTER part for the rows to actually be inserted into the new table.

This can be done for X number of tables in the following way, as described in this answer:

CREATE TABLE new_table ();
DO $$DECLARE r record;
BEGIN
    FOR r IN SELECT table_name FROM information_schema.tables
         WHERE table_schema = 'public' AND table_type = 'BASE TABLE' AND table_name != 'new_table' AND table_name != 'spatial_ref_sys'
    LOOP
        EXECUTE 'CREATE TEMP TABLE temp_table AS SELECT * FROM ' || quote_ident(r.table_name) || ' NATURAL FULL OUTER JOIN new_table';
        EXECUTE 'DROP TABLE new_table CASCADE';
        EXECUTE 'CREATE TABLE new_table AS SELECT * FROM temp_table';
        EXECUTE 'DROP TABLE temp_table CASCADE';
        EXECUTE 'DROP TABLE ' || quote_ident(r.table_name) || ' CASCADE';
    END LOOP;
END$$;


MySQL is using wrong index This is my table:

- user_id INT (foreign key on `user` with the reference `id`)
- question_id INT (foreign key on `question` with the reference `id`)
- option_id INT (foreign key on `option` with the reference `id`)
- exam_id INT (foreign key on `exam` with the reference `id`)
- order INT

My Index is: user_id, exam_id

My Query:

select * from `user_answer` where `user_id` = '48' and `exam_id` = '1' and `order` > '10' order by `order` desc limit 1;

I think it should use my index but this is the result of EXPLAIN:

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'user_answer', 'range', 'user_answer_exam_id_foreign,user_answer_user_id_exam_id_index', 'user_answer_exam_id_foreign', '4', NULL, '10', 'Using where; Using filesort'

Apperently, It is not using my index. When I use FORCE INDEX:

select * from `user_answer` force index (user_answer_user_id_exam_id_index) where `user_id` = '48' and `exam_id` = '1' and `order` > '10' order by `order` desc limit 1;

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'user_answer', 'ref', 'user_answer_user_id_exam_id_index', 'user_answer_user_id_exam_id_index', '8', 'const,const', '10', 'Using where; Using filesort'

Any idea what's wrong with me or MySQL ?

MySQL will chose the index based on few factors. One of them is the cardinality of the indexed field, and other one in the number of rows to be scanned (on average). In your first example, MySQL decided that it will read less rows if it is used the index on "answer_id".
If you provides the stats of your table, it will be much clearer.
Example of data snapshot when this happens:

user_id answer_id order
48      1         15
48      2         17
48      3         19
48      4         13

Here, if MySQL will see that user (User_id, answer_id) will scan 4 rows, while using (exam_id) will scan only 1 row.


Changing SQL account password in specific database I am trying to change the password for userId in a specific database called LionsDB.
If I am changing the password manually, I would login into SQL Management Studio using that user account and update the password.

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerName
$SQLUser = $srv.Logins | ? {$_.Name -eq "$login"};
$SQLUser.ChangePassword($password);

With powershell, I cant find the specific logins in the LionsDB database. The above code gives me the logins at server level. Is there anyway to get to the userIDs in specific catalog and change password for them?

All password authentication for userid is at the server level for a login.  Therefore the LionsDB database will have users but does not have per database password for those users since the login is the authentication point.

If you have enough rights, you can change the passwords of the logins and use a different password, but that password would be the same for all databases on your server.

If you need a special login for a database, then you should create another login, e.g. LionsDB_George or some such pattern that suits you.  Then you grant that login rights to the LionsDB database.


Postgres: SELECTing bytea data partially with offset and length I have a table containing a bytea column, with an average entry size of 5MB. Is it possible to query from random points in the bytea data (given an offset and length of data required) instead of SELECTing the whole byte string into the memory?

Of course, just use the substring function:

select substring(bytea_contents from offset for length) FROM table;

or get_byte(bytea_contents,offset) to get a single byte as an integer.

See Binary String Functions and Operators in the doc for all functions.


Want to add a "variable" to a table I'm using PostgreSQL 8.4 and I have a table that is cleared out and refilled with new data every so often. I want to be able to store the date that the table was last filled, but I'm not sure where to put it. I'm really a novice at database design, and the only two things I can think of are:

Create a table with the sole purpose of storing the date
Timestamp every row in the table with the same timestamp

There's about 1000 to 2000 rows normally. It's just for a hobby project. Any help will be greatly appreciated.

Timestamping each row doesn't seem to make a lot of sense to me. I would just keep a log of the events that are interesting. Such a log could be reusable for other events too. E.g. (sorry this is SQL Server dialect, you may have to adjust slightly):

CREATE TABLE dbo.EventLog
(
  EventDate SMALLDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  [Description] NVARCHAR(2000)
);

Whenever you reload the table, also call a stored procedure that does this:

INSERT dbo.EventLog([Description])
  SELECT N'Re-populated table x';

If you need to know the date of the last refresh, you can say:

SELECT MAX(EventDate) FROM dbo.EventLog
  WHERE [Description] = N'Re-populated table x';

If all you ever care about is the last time, then before the insert above, you could just:

DELETE dbo.EventLog
  WHERE [Description] = N'Re-populated table x';


Multiple inserts of batch insert over the wire? I am generating about 500 records.

What I want to do is INSERT them all at the same time because:

The server is located elsewhere and I would rather send one big payload over.
This seems like less overhead than sending 500 separate insert queries over the wire.

But because they're batch inserting, I don't want to rollback everything if one insert fails - I want to try to insert the rest in there and record the failure(s).

What's the best way to do this in SQL Server? Is BULK INSERT what I want?

I'm not saying this is the best way, but here is one way.

My solution uses xml which, I think, has a max size of 2gb.  Table Valued Parameters would have been ideal, but they are not supported in remote calls.

Since you are only generating around 500 records, you probably won't run into any size limitations.

On the source instance, create/load a table to play with. This will be the source data to send to the remote process.

DROP TABLE IF EXISTS dbo.customer
CREATE TABLE [dbo].[Customer] (
    [CustomerID] [int] NULL
    ,[Name] [varchar](30) NULL
    ,[RecordCreated] [datetime] NULL
    ,[RecordUpdated] [datetime] NULL
    ,
    ) ON [PRIMARY]
GO

INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (1, N'James', CAST(N'2017-11-01T16:16:21.297' AS DateTime), CAST(N'2017-11-01T16:52:02.427' AS DateTime))
GO
INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (2, N'John', CAST(N'2017-11-01T16:41:52.347' AS DateTime), CAST(N'2017-11-01T16:41:52.347' AS DateTime))
GO
INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (3, N'Sam', CAST(N'2017-11-01T16:50:25.430' AS DateTime), CAST(N'2017-11-01T16:50:25.430' AS DateTime))
GO
INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (1, N'James', CAST(N'2017-11-01T16:16:21.297' AS DateTime), CAST(N'2017-11-01T16:52:02.427' AS DateTime))
GO
INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (2, N'John', CAST(N'2017-11-01T16:41:52.347' AS DateTime), CAST(N'2017-11-01T16:41:52.347' AS DateTime))
GO
INSERT [dbo].[Customer] ([CustomerID], [Name], [RecordCreated], [RecordUpdated]) VALUES (3, N'Sam', CAST(N'2017-11-01T16:50:25.430' AS DateTime), CAST(N'2017-11-01T16:50:25.430' AS DateTime))
GO

Here is some TSQL to run on the source instance, but before you can fully execute it, you'll have to define the receiving stored procedure on the remote server.  I'll get to that.

--Declare XML variable and populate by selecting from a table using FOR XML AUTO
DECLARE @x XML = (
        SELECT *
        FROM Customer
        FOR XML AUTO
            ,TYPE
            ,ROOT('Root')
        );

--You cant ship XML via remote call, so declare a Varchar(max) variable and convert the XML
DECLARE @XVarchar VARCHAR(max)
SET @XVarchar = convert(VARCHAR(max), @x)

--Call the remote stored procedure and pass the xml
--My linked server is called sqlcompare

--NOTE: you cannot execute this until you have defined the
--receiving stored procedure on the remote server.
EXEC sqlcompare.scutility.dbo.[ProcessXmlRows] @xVarchar

Now, on the remote server, create a stored procedure to receive the passed xml.  In the following example, I'm using OPENXML to basically shred the xml and inserting directly into the remote table. You'd probably need a cursor to help you weed out the good and bad rows. Note, that I have also created the customer table on the remote server (this allows me to reference that table in the OPENXML using the WITH clause.

CREATE PROCEDURE [dbo].[ProcessXmlRows] (@Xml xml)
AS
BEGIN
DECLARE @intXMLDocID INT -- xml document handle

--Prepare the XML that was passed into the SP
EXECUTE [master].dbo.sp_xml_preparedocument @hdoc = @intXMLDocID OUTPUT
    ,@xmltext = @xml;

--Using OPENXML, and referencing the existing customer table, insert the rows
insert into Customer
SELECT *
FROM OPENXML(@intXMLDocID, '/Root/Customer') WITH customer

select * from Customer

END
GO

Note, that I'm using with customer on the OPENXML.  From the documentation on OPENXML

  The WITH clause provides a rowset format (and additional mapping
  information as required) by using either SchemaDeclaration or
  specifying an existing TableName.

At this point, you should be able to successfully execute the source T-SQL to call the remote stored procedure and process the data.


6GB Table - 37Mi Rows - 'System.OutOfMemoryException' - SQL 2008 R2 I have a proc, looping through databases( the insert is in a fix database...the select uses the loop ), making inserts (Will hide names for security):

insert into Main_Database..Table(16 Fields)
    select ( there are 16 fields here, DateDiff(s, DtTrabalhoInF8, DtTrabalhoFi) as TempoSegundos
     from  [Database_name].dbo.View as T1 with (nolock)
     where DtTrabalho >= '2015/06/06' and
     not exists(select 1 from PainelControle.dbo.tblLogProjetos T2 Where t2.BancoDados collate SQL_Latin1_General_CP1_CI_AS = t1.BancoDados collate SQL_Latin1_General_CP1_CI_AS and t2.codlog = t1.codlog)

The problem is:

If I run this, it runs in 1 second:

insert into Main_Database..Table(16 Fields)
        select ( there are 16 fields here, DateDiff(s, DtTrabalhoInF8, DtTrabalhoFi) as TempoSegundos
         from  [Database_name].dbo.View as T1 with (nolock)
         where DtTrabalho >= '2015/06/06'

The problem is here:

and
         not exists(select 1 from Database_Name.dbo.tblLogProjetos T2 Where t2.BancoDados collate SQL_Latin1_General_CP1_CI_AS = t1.BancoDados collate SQL_Latin1_General_CP1_CI_AS and t2.codlog = t1.codlog)

This tblLogProjetos has 37Million rows. 6GB ( The simple select 1 from tbllog  trhowing the message) and the job never ends( running the code manually, not inside the job, after 10min it shows me the error ):

  'System.OutOfMemoryException'

I would like ot know how to troubleshoot this. Is this SERVER problem? ( we have 32GB ). Is this MANAGEMENT STUDIO problem:

name                    minimum      maximum       config_value     run_value
max server memory (MB)    16       2147483647        28000           28000

Or is this query problem? One thing that I notice, When I use a query to see unused indexes, every index of this table has 0 reads. How can this be possible?

By the way...tblLogProjetos is a VIEW that uses a linked server to another server. It's just a select ( fields ) from [server5].painelcontrole.dbo.tbllogprojetos

System.OutOfMemory exception is a .NET exception which is basically telling you that the grid control experienced an OOM condition. I had blogged about this in detail in the past.

You could try the recommendations to trim down the result set which is definitely required in the intermediate levels of the procedure. But to circumvent the OOM condition in SSMS, you could either try with output to TEXT or output to a file or use SQLCMD to execute the same T-SQL procedure/command.


Quickly restoring a backup over top of an existing database - error "database in use" I'm working on a set of scripts (in SQL Server Management Studio) to fix certain data inconsistencies in a customer database (SQL Server 2008 R2).

I got a .bak file with the database, and I restored that to my system, to e.g. SampleDatabase. Now I'm working on my scripts, and after a while, I've fixed most of the issues.

So now I'd like to restore that original .bak file again, to be sure all my scripts do run against the original data, and do what they're supposed to do. I close all the Windows in SQL Server Management Studio, open a fresh new query window in my master database, and issue my RESTORE command:

RESTORE DATABASE [SampleDatabase]
FROM DISK = N'c:\tmp\sample.bak'
WITH FILE = 1,
MOVE N'Sample_PRIMARY' TO N'c:\mssql\Sample_PRIMARY.MDF',
MOVE N'Sample_LOG' TO N'C:\mssql\Sample_LOG.LDF',
NOUNLOAD, REPLACE, STATS = 5

Unfortunately, I now get an error message:

  Msg 3101, Level 16, State 1, Line 2
  Exclusive access could not be obtained because the database is in use.
  Msg 3013, Level 16, State 1, Line 2
  RESTORE DATABASE is terminating abnormally.

which seems a bit odd, since I made sure I had closed all the SSMS Windows into that database - why is it still in use?

How can I fix this situation as quickly as possible? The only solution I know that works right now is restarting the MSSQLSERVER service - but that's a rather lengthy and somewhat cumbersome process...

Is there another, more efficient, more streamlined way to get this database "out of use" so I can restore the backup over top of it again to check my scripts?

Variants of the following which uses ROLLBACK IMMEDIATE are common:

:setvar DatabaseName "SampleDatabase"
:setvar BackupDevicePath "D:\Temp\sample.bak"
:setvar DataFilePath "D:\SQLData\I1\"

USE master;
GO

IF (DB_ID('$(DatabaseName)') IS NOT NULL)
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
END
GO

RESTORE DATABASE
    [$(DatabaseName)]
FROM
    DISK = N'$(BackupDevicePath)'
WITH
    FILE = 1
    , MOVE N'$(DatabaseName)_Data' TO N'$(DataFilePath)$(DatabaseName).mdf'
    , MOVE N'$(DatabaseName)_Log' TO N'$(DataFilePath)$(DatabaseName).ldf'
    , REPLACE
    , STATS = 1;

Note SQLCMD mode.


Oracle Trigger errors I have a tables ENV_CONFIG and ENV_CONFIG_HIST where the latter table should get a new record on any insertion to the former. The trigger seems pretty straight forward but I'm getting compilation errors:

line 5  : SQL Statement ignored (Insert ...)
line 13 : Column not allowed here (,new.PARAM_VALUE)

What am I missing?

create or replace TRIGGER TRG_ENV_CONFIG_INSERT
  AFTER INSERT
  ON ENV_CONFIG
  FOR EACH ROW
BEGIN
  INSERT INTO ENV_CONFIG_HIST (
    PARAM_NAME
    ,PARAM_DESCR
    ,PARAM_VALUE
  ) VALUES (
    new.PARAM_NAME
   ,new.PARAM_DESCR
   ,new.PARAM_VALUE
 );
END;

you just need add Colon character (:) before new keyword same as below query:

create or replace TRIGGER TRG_ENV_CONFIG_INSERT
AFTER INSERT
  ON ENV_CONFIG
  FOR EACH ROW
BEGIN
  INSERT INTO ENV_CONFIG_HIST (
    PARAM_NAME
    ,PARAM_DESCR
    ,PARAM_VALUE
  ) VALUES (
    :new.PARAM_NAME
   ,:new.PARAM_DESCR
   ,:new.PARAM_VALUE
 );
END;


MongoDB using too much memory We've been using MongoDB for several weeks now, the overall trend that we've seen has been that mongodb is using way too much memory (much more than the whole size of its dataset + indexes).

I've already read through this question and this question, but none seem to address the issue I've been facing, they're actually explaining what's already explained in the documentation.

The following are the results of htop and show dbs commands.

I know that mongodb uses memory mapped IO, so basically the OS handles caching things in the memory, and mongodb should theoretically let go of its cached memory when another process requests free memory, but from what we've seen, it doesn't.

OOM kicks in an starts killing other important processes e.g. postgres, redis, etc. (As can be seen, to overcome this problem, we've increased the RAM to 183GB which now works but is pretty expensive. mongo's using ~87GBs of ram, nearly 4X of the size of its whole dataset)

So,

Is this much memory usage really expected and normal ? (As per documentation, WiredTiger uses at most ~60% of RAM for its cache, but considering the dataset size, does it even have enough data to be able to take 86GBs of RAM ?)
Even if the memory usage is expected, why won't mongo let go of its allocated memory in case another process starts requesting for more memory ? Various other running processes were being constantly killed by linux oom, including mongodb itself, before we increased the RAM and it made the system totally unstable.

Thanks !

Okay, so after following the clues given by loicmathieu and jstell, and digging it up a little, these are the things I found out about MongoDB using WiredTiger storage engine. I'm putting it here if anyone encountered the same questions.

The memory usage threads that I mentioned, all belonged to 2012-2014, all pre-date WiredTiger and are describing behavior of the original MMAPV1 storage engine which doesn't have a separate cache or support for compression.

The WiredTiger cache settings only controls the size of memory directly used by the WiredTiger storage engine (not the total memory used by mongod). Many other things are potentially taking memory in a MongoDB/WiredTiger configuration, such as the following:

WiredTiger compresses disk storage, but the data in memory are uncompressed.
WiredTiger by default does not fsync the data on each
commit, so the log files are also in RAM which takes its toll on
memory. It's also mentioned that in order to use I/O efficiently,
WiredTiger chunks I/O requests (cache misses) together, that also
seems to take some RAM (In fact dirty pages (pages that has
changed/updated) have a list of updates on them stored in a
Concurrent SkipList).
WiredTiger keeps multiple versions of records in its cache (Multi
Version Concurrency Control, read operations access the last
committed version before their operation).
WiredTiger Keeps checksums of the data in cache.
MongoDB itself consumes memory to handle open connections, aggregations, serverside code and etc.

Considering these facts, relying on show dbs; was not technically correct, since it only shows the compressed size of the datasets.

The following commands can be used in order to get the full dataset size.

db.getSiblingDB('data_server').stats()
# OR
db.stats()

This results is the following:

{
    "db" : "data_server",
    "collections" : 11,
    "objects" : 266565289,
    "avgObjSize" : 224.8413545621088,
    "dataSize" : 59934900658, # 60GBs
    "storageSize" : 22959984640,
    "numExtents" : 0,
    "indexes" : 41,
    "indexSize" : 7757348864, # 7.7GBs
    "ok" : 1
}

So it seems that the actual dataset size + its indexes are taking about 68GBs of that memory.

Considering all these, I guess the memory usage is now pretty expected, good part being it's completely okay to limit the WiredTiger cache size, since it handles I/O operations pretty efficiently (as described above).

There also remains the problem of OOM, to overcome this issue, since we didn't have enough resources to take out mongodb, we lowered the oom_score_adj to prevent OOM from killing important processes for the time being (Meaning we told OOM not to kill our desired processes).


How to handle multiple associative tables? I really need so help regarding an issue I'm having. I'm trying my best to explain the problem, if anything is not clear please tell me. Some help would mean the world to me!

Here's my problem, let's say I have these 4 tables:

**CRIMINAL**
+ ID
+ Name
+ Image

**CAR_CRIMINAL**
+ ID
+ Type
+ Image

**COMPANY_CRIMINAL**
+ ID
+ Name
+ Image

**IMAGE**
+ ID
+ Link

For example, all entities (Criminal, car_criminal, Company_criminal) all have the attribute image. But an image can be assigned to multiple companies and a company can have multiple images so therefor it's a many-to-many relationship right?

That's also applicable to the all other tables. My question is, would I have to create an associative table for each of those many-to-many relationships or is there another way to resolve this problem?

Yes, if you want to reuse an image between multiple entities and a single entity can have multiple images you'll need to make it a many-to-many relationship. And yes, you would want to create multiple associative tables for each many-to-many relationship.

The only way around this would be to create one Entity table that all of the other tables pulled their IDs from, but I think what you currently have is easier to maintain going forward.