Cross-Database Impersonation Fail for Service Account

Correct: Impersonation / EXECUTE AS, by default, is quarantined to the initial database. In order to get around this you need to do either set the initial database to TRUSTWORTHY ON (not a great option, so should be viewed as a last resort), or create a Certificate that can be in both the initial DB and the destination DB that can be used to link the desired permissions (and then you don't need Impersonation at all).

I have several examples of doing this across various answers here on DBA.StackExchange. Try these first:

Stored procedure security with execute as, cross database queries, and module signing (this one goes through, step by step, showing how Impersonation, Cross-DB Ownership Chaining, and TRUSTWORTHY work, and then how module signing eliminates the need for all three of those options).
Permissions in triggers when using cross database certificates

Now, given the new information added to the question regarding:

  The project that precipitated this question is an iterative deprecation so I think cert-signing may end up being a rather large administrative overhead as time goes on.

and:

  If it is possible to use this stored proc as a wrapper to execute restricted actions agnostic of who calls it, that's the end-goal. ... I am confident that the SProc cannot be improperly modified, so anything that executes from inside it may be highly permissioned - in the hopes that I don't need to administer perms for the ServiceAccount one-by-one.

the direction to take can be narrowed down. Usually the goal is to be as granular as possible with the permissions, so it sometimes takes some extra steps to get to a least-privileged setup. But that doesn't mean that you can't take fewer steps to be less granular when the situation calls for it.

One of the (several) wonderful aspects of module signing is that you are effectively giving permissions to the code, not to people. So you can control the who can make use of the permissions by properly setting permissions on whatever code (i.e. Stored Procedures, Functions, Triggers, etc) is being signed.

Assuming you want both Server/Instance -level and Database-level permissions, even if you don't end up using most of what will be allowed, you can do the following:

Create a Certificate in [master].
Create a Login from that Certificate.
Add the Certificate-based Login to the sysadmin Fixed Server Role.
Switch to the Stage DB
Re-create the same Certificate in this DB (either by backing it up to a file on disk OR by extracting both the Certificate and the Private Key to variables or a local temporary table as shown in my other answers).
Sign the Stored Procedure with this Certificate via ADD SIGNATURE.

That's it! The Stored Procedure, regardless of who executes it, can do anything it was coded to do. And if that code changes, you will need to do the ADD SIGNATURE again. But the Certificate-based Login cannot be Impersonated, so nobody can use it to get "sa" privileges. And, if you use "encrypt by password" when creating the Certificate and do not give that password to anyone, then nobody will be able to sign other modules with it.

In this setup, the only on-going administration would be re-signing the Stored Procedure if you keep changing it.

Also, regarding your final attempt (i.e. alter authorization on object::spLoadStageToProd to sa;): that doesn't work because "sa" is a Login (Instance / Server -level), not a User (Database-level). So you can only grant permissions on database-level items to principals found in sys.database_principals.


Creating a "database link" from Oracle to SQL Server

The Oracle database server accepts SQL statements that query data stored in several different databases. The Oracle database server with the Heterogeneous Services component processes the SQL statement and passes the appropriate SQL directly to other Oracle databases and through gateways to non-Oracle databases. The Oracle database server then combines the results and returns them to the client.

Database Heterogeneous Connectivity Administrator's Guide

Oracle provides heterogeneous services to query non-Oracle databases like SQL Server. If you need some guidance here are some useful links.

 Connecting to Non-Oracle Data Sources Through Gateways

 How to Set up Third Party Databases Drivers in Oracle SQL Developer

 Heterogeneous Services - Generic Connectivity


Running psql/postgres client in an ephemeral mode in a container

Currently your Dockerfile doesn't contain an execution script so it will finish & exit...

If you want it to always run, you can 'tail -f some file '

But, you can skip it and combine the 2 commands to run the psql command & exit:

docker run -it --link pg_server:pg_server_1 --name pg_client_1 psql -U my_user -h pg_server_1 -p 5432 -d my_db -c "select uncertainty_estimate(' land_cover', manifold_envelope(-81.85, 6.8, -81.01, 7.8, 4326))"

Docker treats the last arguments as a command to run directly.


DB2 will allow you to use a new line character in a quoted identifier:

$ db2 'select ibmreqd "foo
> bar" from sysibm.sysdummy1'

foo
bar
-------
Y

  1 record(s) selected.

The question is, will Dbeaver let you enter a new line character where you want it.


RDS is quickly running out of storage, how can I debug this?

I figured out that one of my replication ‘subscriptions’ was broken on a Subscriber database.  Once, I fixed the subscription, pglogical started replicating successfully, and my storage went up 40GB instantly.


Need some expert eyes to look at my schema

Good start so far. Here are some notes:

a movie can be released on different dates in different cities
please add your primary keys, so we can see them
are images for movies? people? why no order column?
movies schedules are more complex than you've got so far. You will want to calculate show times based on some recurrence rules (see stackoverflow for recurrence posts)
what is 'location' in theatres? the address?

Regarding your questions at the bottom, don't loop. You can almost always do everything using SQL and its set logic. Make sure you understand joins:

http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/


You should activate the general log. Normally, the general is a text file. I have posts that show you how to make the general log a MyISAM table. Then, you could query the general log by datetime using SQL.

Here are my posts how to setup and configure the general log as a MyISAM table

Jun 30, 2011 : How do I output MySQL logs to syslog?
Jan 08, 2012 : How to enable MySQL general log?
Feb 11, 2012 : MySQL general log
Apr 04, 2013 : Stress test MySQL with queries captured with general log in MySQL
Jun 23, 2013 : Analyse MySQL General Query Log in Real-time?
Apr 28, 2014 : MySQL (on Windows) general log too large to rotate properly?

Once you configure it, just run queries like this to see commands run today

SELECT user_host,argument
FROM mysql.general_log
WHERE event_time >= DATE(NOW()) + INTERVAL 0 SECOND;


Migrate data from multiple disparate databases into 1

You can generate a list of T-SQL statements using dynamic SQL, something like this:

DECLARE @srcDB sysname;
DECLARE @newDB sysname;
DECLARE @oldSchema sysname;
DECLARE @newSchema sysname;

SET @srcDB = 'oldDB';
SET @newDB = 'masterDB';
SET @oldSchema = 'dbo';
SET @newSchema = 'newDBSchema';

SELECT N'INSERT INTO ' + QUOTENAME(@newDB) + N'.' + QUOTENAME(@newSchema) + N'.' + QUOTENAME(t.name) + N'(' + STUFF((SELECT N', ' + QUOTENAME(c.name) FROM sys.columns c WHERE c.object_id = t.object_id ORDER BY c.column_id FOR XML PATH(N'')), 1, 2, N'') + N'
) SELECT ' + STUFF((SELECT N', ' + QUOTENAME(c.name) FROM sys.columns c WHERE c.object_id = t.object_id ORDER BY c.column_id FOR XML PATH(N'')), 1, 2, N'') + N'
FROM ' + QUOTENAME(@srcDB) + N'.' + QUOTENAME(@oldSchema) + N'.' + QUOTENAME(t.name)
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE s.name = @oldSchema
ORDER BY t.name;


Things I'd check:

Are any changes happening? Binary log doesn't include SELECT queries, only changes. Compare the timestamp on files like ib_logfile*.
Are replication filters excluding changes being made?
Are sessions making changes while SQL_LOG_BIN=0 is suppressing binary logging, either globally as @RolandoMySQLDBA suggests, or else per session?
Disk is full?
You are looking at binlogs in the wrong directory?


Which major database platforms support TTL?

If you need true TTL functionality, the only real choice I know of is using an object DB.

Mongo does TTL quite well;
Redis fully supports TTL;
LevelDB has an add-on which supports TTL;
MS Azure DocumentDB also has TTL support.


MYSQL REGEXP for text not between parentheses

I hate regex(or dont get them) so:

SELECT id,data FROM table
WHERE CONCAT(REPLACE(SUBSTRING_INDEX(data,'(',1),'(',''),
REPLACE(SUBSTRING_INDEX(data,')',-1),')',''))
LIKE '%search term%';

SQL Fiddle


Find unauthenticated connections to MongoDB

Failing anything better here is a script to parse the log files and find unauthenticated users:

#!/usr/bin/env gawk -f

# Script to parse the MongoDB log file to find any connections that was not authenticated.

BEGIN {
    if (ARGV[1] == "") {
        print "[usage]: ./unauth_connections [mongodb.log]"
        exit 1
    }
}

# Build list of all connections and their IPs
/[[]initandlisten[]] connection accepted/ {
    # Prepend "conn" and remove the leading # from the connection
    # ID so the key is consistent with the log authentication line
    # i.e. turns #5 to conn5
    conn_key = "conn" substr($10, 2)
    # Build the list: conn_key => authenticated
    conn_list[conn_key]
    # Build the list for the IP: conn_key => IP
    ip[conn_key] = $9
}

# Find authenticated connections
/[[]conn[0-9]+[]]  authenticate/ {
    # Remove the brackets from the connection ID so key is
    # consistent with the initial connection log line
    # i.e. turn [conn5] to conn5
    gsub(/[][]/, "", $5)
    # Flag that the connection is authenticated
    conn_list[$5] = 1
}

END {
    # Build a list of unauthenticated users
    for (conn in conn_list) {
       if (!conn_list[conn]) {
          anon[conn] = sprintf("Connection %8s from %20s not authenticated", conn, ip[conn])
       }
    }

    # Sort the list and print in order
    n = asort(anon)
    for (i=1; i<=n; i++) {
        print anon[i]
    }
}


This is nothing to be concerned about. The default trace creates up to 5 rollover files, each with a max size to 20MB, and a incremental number is automatically appended to the file name. The older files are automatically deleted once the max of 5 files is reached.

You can query the trace in T-SQL using fn_trace_gettable, specifying only the base file name log.trc along with the number of files to read or default to read all available trace files.


Session hanging with TM REQUEST and plan handle 0x0000

TM stands for Transaction Manager. The current command being set to TM
Request means that the thread is currently servicing a DTC (Distributed Transaction Coordinator) request, either to enlist in a DTC transaction, or defect from it, or commit it, or so on. Distributed Transactions are not supported for Database Mirroring because it can't guarantee transaction integrity.  Suppress your DTC transactions and this should go away.


SQL script Scope in Microsoft SQL Server Management Studio - runs against active DB or all DB's?

If you execute a  use database statement, subsequent queries will only execute against that db. However: this is only for the current tab.

In SSMS to see the connection context look in the bottom right corner of the current tab. You should see the current database name there.

Or you could query the current db with:

SELECT DB_NAME() AS [Current Database];

Note: it is possible to have multiple tabs open which can be connected to different databases or servers. If you have multiple tabs open and tiled it can get a bit confusing.

As an added precaution you could disconnect and close other tabs to prevent any accidents.

It is possible that a sql script can contain many sql statements, and could include another use database command which could change the connection part way through the script. You should read the script or search for this before executing scripts supplied by other people.

One last warning: it is also possible to connect to multiple database servers simultaneously using registered servers. However unless you deliberately chose to do this its extremely unlikely you would ever  encounter this even accidentally.


Could not start the SQL Server Agent (SQLEXPRESS)

SQL Server Agent is not available with Express. It does install but that is just the false hope Microsoft puts in for some reason.

If you are seeing the "(EXPRESS)" within SQL Server Configuration Manager it was likely installed at some point in time and an attempt to remove it left some things behind. Express edition can get installed if you happen to have installed Visual Studio 2010 for instance.

Since you see the "(MSSQSERVER)" it would tell me that the default instance is the full version (Standard or higher) of SQL Server. There are a few ways to "break" into the default instance you have on your machine/server. However it depends on your environment as to what method you want to use.


MySQL Statement not displaying some values

Your query is just plain invalid, as you have included values in the column list that are not part of the GROUP BY.  MySQL lets you do this (unfortunately), but the results will be indeterminate.  Consider setting sql_mode=TRADITIONAL in my.cnf (and SET GLOBAL sql_mode=TRADITIONAL)

A valid query would like like this, though I'm not sure it will get you the results that you want.

SELECT `Group Transaction No`,`Receipt No`, `Transaction No`, `Actual Cost`,
`CErr`,`Resource Units`, `Meter Serial No`, `Token`, `Transaction Timestamp`,
ROUND(SUM(`Transaction Amount`),0) as Amount FROM `efulusi_acc`
GROUP BY 1,2,3,4,5,6,7,8,9
ORDER BY 8
LIMIT 5

Also, it's a really bad idea to include spaces in your column names.  It's just plain difficult to work with and can cause a lot of edge case bugs.


There are several issues with the query. FROM and column expression are misplaced, "games_played.*"  is not doing what it looks like, inner join instead of outer join, etc.

Try this one (I assume that (id) is the primary key of games):

select
    games.*,
    count(games_played.*)
        as count,
    count(*) filter (where games_played.player_id = 1) > 0
        as played
from games
    left join games_played
    on games_played.game_id = games.id
group by
    games.id
order by
    games.id ;

Test at dbfiddle.uk


Help wrapping my mind around many-to-many relationships

Something like:

Article                ArticleKeyword         Keyword
---------------        ---------------        ------------
article_id (PK)  <---  article_id (FK)
                       keyword_id (FK)  --->  keyword_id (PK)

There could be zero-to-many ArticleKeyword rows for each row in Article.

There could be zero-to-many ArticleKeyword rows for each row in Keyword.

This equates to a many-to-many relationship between that Article and Keyword tables.

The primary key for ArticleKeyword would be the composite key (article_id, keyword_id) unless you wanted to count multiple occurrences of the same keyword in the same article separately (perhaps noting the position in the article) in which case you may instead want a surrogate key like so:

Article                ArticleKeyword         Keyword
---------------        ---------------        ------------
                       ak_id      (PK)
article_id (PK)  <---  article_id (FK)
                       keyword_id (FK)  --->  keyword_id (PK)
                       kw_location

(or you could add the location properties to the combined key, of course, if those extra columns would never be NULL)


Tips for Optimizing Oracle Warehouse Builder 11.1 with Oracle DB 10.2

Make sure that open_cursors is sufficient. What is the current value for open_cursors and session_cached_cursors? They should be high, start with 500.


You could use either:

HAVING MAX(rnaseq.fold_change) > 4

or the Postgres aggregate function bool_or:

HAVING bool_or( rnaseq.fold_change > 4 )


Execution plan results for sp_executesql suggests adding missing index for already existing index

I've seen the same behavior; check the execution plan and make sure it's using the new index, then ignore the recommendation.


Cannot revoke default privileges from postgresql user

Edited to answer the question related to the \ddp command not the \dp command as @personne3000 pointed out in the comment below.

You probably want to use ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA kpi REVOKE EXECUTE ON FUNCTIONS FROM intranet2;

This is because postgres is the user that was granted the default privilege of execute on the functions in the schema kpi and is granting it to intranet2, as noted by the permissions of intranet2=X/postgres

I created a small example to illustrate what's going on.

pgsql@[local]:5432:pgsql:=# CREATE ROLE bob;
CREATE ROLE
Time: 0.526 ms
pgsql@[local]:5432:pgsql:=# CREATE SCHEMA we_like_bob;
CREATE SCHEMA
Time: 0.608 ms

pgsql@[local]:5432:pgsql:=# ALTER DEFAULT PRIVILEGES FOR ROLE bob IN SCHEMA we_like_bob GRANT EXECUTE ON FUNCTIONS TO pgsql;
ALTER DEFAULT PRIVILEGES
Time: 1.480 ms
pgsql@[local]:5432:pgsql:=# \ddp
             Default access privileges
 Owner |   Schema    |   Type   | Access privileges
-------+-------------+----------+-------------------
 bob   | we_like_bob | function | pgsql=X/bob
(1 row)

User pgsql has the execute privilege, X, granted by bob.

Try to revoke the execute privilege for the role pgsql, as pgsql (a superuser).

pgsql@[local]:5432:pgsql:=# ALTER DEFAULT PRIVILEGES FOR ROLE pgsql IN SCHEMA we_like_bob REVOKE EXECUTE ON FUNCTIONS FROM pgsql;
ALTER DEFAULT PRIVILEGES
Time: 0.176 ms
pgsql@[local]:5432:pgsql:=# \ddp
             Default access privileges
 Owner |   Schema    |   Type   | Access privileges
-------+-------------+----------+-------------------
 bob   | we_like_bob | function | pgsql=X/bob
(1 row)

Didn't work because the command is not removing the bob role's default ability to grant execute on functions to role pgsql, it is removing the pgsql role's ability to revoke execute on functions from pgsql.

If we change it to remove the privilege of execute from the bob role for the role pgsql, then it works.

pgsql@[local]:5432:pgsql:=# ALTER DEFAULT PRIVILEGES FOR ROLE bob IN SCHEMA we_like_bob REVOKE EXECUTE ON FUNCTIONS FROM pgsql;
ALTER DEFAULT PRIVILEGES
Time: 0.644 ms
pgsql@[local]:5432:pgsql:=# \ddp
         Default access privileges
 Owner | Schema | Type | Access privileges
-------+--------+------+-------------------
(0 rows)

pgsql@[local]:5432:pgsql:=#


Strip longest common suffix and concat in Postgres

Given that you have an aggregate that finds the longest common suffix

WITH x AS(
    SELECT left(name,length(name)-length(lcs(name) over ())) AS s
    FROM t WHERE id = 1
)
SELECT string_agg(s,', ') FROM x;


Write concern can be set differently for every DML operation and that overrides the default write concern value. So, if default value is majority, developer can choose w:0 or even w:all for update operation, if he or she wants to do so.


Can I select data inserted in the same uncommited transaction?

Yes.
Everything you did inside the same transaction is visible to later commands inside the same transaction. Just not to other transactions until committed. This is true for all isolation levels except Read uncommitted where "dirty reads" are possible (but that does not affect your question per se).

It's implemented with the MVCC model (Multiversion Concurrency Control) based on TransactionIds determining relative age and visibility for every table row. Every new row version written in the same transaction gets the same xmin and is considered to have happened "at the same time".

There is a corner case for multiple CTEs (Common Table Expression) in the same command. One might think those are executed sequentially, but unless one CTE references the other their sequence is arbitrary. And all of them see the same snapshot from the start of the query. That's why it's disallowed to UPDATE the same row more than once in multiple CTEs of the same query: would be ambiguous.

Example:

Return pre-UPDATE Column Values Using SQL Only - PostgreSQL Version

Advanced example:

Are SELECT type queries the only type that can be nested?


Search efficiency of datetime vs date + indexed smallint (PostgreSQL)

I'm putting down my recommendations for your two questions and additional considerations for your index.

1

I'd keep the timestamp as one field, breaking it into two separate fields for date and a smallint for the hour will reduce the space by 2 bytes per row (aka 43 MB with your 22k row estimation) having to compare two fields for date /time doesn't seem worth the small space gain.

2

22k rows may not be many in comparison to some databases, if you're picking out individual occurrences though then you still want it as fast as possible. finding a specific value from a heap requires a full table scan which can take time, if it's indexed then you do an index seek which means you can find records a lot faster.

Additional

If the data is broken up to different rows by location (eg, '2015/10/13 00:00', 'London', 'sunny') then I'd advise a composite key between the timestamp and location

I don't believe that is the case, it sounds like the data will be stored in one long line, so (datetime timestamp, London (data), Paris(data)), for this just index the timestamp field, as an index would suffice

Hope that helps


Does the SQL-1992 standard restrict naming identifiers to 18 characters?

Unless you're implementing SQL-1992's "Full SQL" (which is the 1992 spec), you should not have identifiers with more than 18 characters.

SQL 1992 defines three levels of compatibility,

Entry SQL includes the statements for defining schemas, data ma- nipulation language, referential integrity, check constraints, and default clause from ISO/IEC 9075:1989, and options for module lan- guage and embedded SQL interfaces to seven different programming languages, as well as direct execution of the data manipulation statements. It also includes features related to deprecated fea- tures from ISO/IEC 9075:1989 (commas and parentheses in parameter lists, the SQLSTATE parameter, and renaming columns in the ), features related to incompatibilities with ISO/IEC 9075:1989 (colons preceding s, WITH CHECK OPTION constraint clarifications), and aids for transitioning from ISO/IEC 9075:1989 to this International Standard (s).  Finally, it contains changes to correct defects found in ISO/IEC 9075:1989 (see Annex F, "Maintenance and interpretation of SQL").
Intermediate SQL includes major new facilities such as statements for changing schemas, dynamic SQL, and isolation levels for SQL- transactions. It also includes multiple-module support and cascade delete on referential actions, as well as numerous functional en- hancements such as row and table expressions, union join, character string operations, table intersection and difference operations, simple domains, the CASE expression, casting between data types, a diagnostics management capability for data administration and more comprehensive error analysis, multiple character repertoires, interval and simplified datetime data types, and variable-length character strings. It also includes a requirement for a flagger facility to aid in writing portable applications.
Full SQL increases orthogonality and includes deferred constraint checking and named constraints. Other technical enhancements in- clude additional user options to define datetime data types, self-referencing updates and deletes, cascade update on referen- tial actions, subqueries in check constraints, scrolled cursors, character translations, a bit string data type, temporary tables, additional referential constraint options, and simple assertions.

At the top you're quoting "Entry SQL" which though stricter is a "low" level of SQL conformance with the 1992 spec. This mode was for backwards compatibility with SQL:89 and the spec is pretty explicit

  ... [boring stuff] aids for transitioning from ISO/IEC 9075:1989 to this International Standard (s).

While I couldn't personally dig up the SQL-89 standard, I assume it does have an 18 character limit on identifiers.

The other part is alarming to me, because I believe this shouldn't apply to Intermediate SQL from the spirit of the spec which is defined as,

But none the less, the spec is the letter of the law. Clearly even in Intermediate SQL the <regular identifier> and <delimited identifier body> should not be more than 18 characters.


SQLite Data Types In Context of Disk Space

I searched for "SQLite storage space" and this was the second link:

https://www.sqlite.org/fileformat2.html

All the information you could possibly want there.

You cannot exactly work out how much storage space a given data set will use because it depends on how it was created (deletes tend to cause fragmentation for example, which means more space than would be optimal). It does explain the row format though.

SQLite is not strongly typed though. So getting a row size is out of the question. Also most databases created in the last 30 years don't use a packed row format.


What is the best way to model an invoicing system with different forms of invoice items?

If you want the "keep it simple" approach, you can in fact put both types of "items" in the same table, while maintaining proper integrity constraints. Generally speaking, this is what it would look like:

Put an ItemType column in BillingItem, which is a foreign key to some other table that will hold the two (or more) item types you're dealing with.
Add a nullable foreign key column AppointmentID.
Add constraints to the ItemType table which stipulates which columns (including the AppointmentID foreign key) must or must not be null based on the ItemType.

But I would probably prefer to do something like this (and have in the past):

Create one table with all the shared fields (BillingItem in this case), and a composite primary key with ID and Type.
Create tables for each subtype (appointments, physical goods) which have the columns specific to those goods, and have the same primary key columns as BillingItem.
Add a constraint to each subtype table forcing a specific value for Type ('A', 'P', etc.)
Create a foreign key from the subtype tables' primary keys to the BillingItem table's primary key.
Optional: Create a view that flattens/denormalizes the data by UNIONing multiple queries that fetch all the different subtypes and provide nulls for the columns that don't apply. This is just to simplify querying.

Simplified example:

CREATE TABLE BillingItem (
    ID int NOT NULL,
    Type char(1) NOT NULL,
    Cost money NOT NULL,
    CONSTRAINT PK_BillingItem PRIMARY KEY CLUSTERED (ID, Type),
    CONSTRAINT CK_BillingItem_Type CHECK (Type IN ('A', 'P')) --Or make Type a foreign key to a lookup table
)

CREATE TABLE BillingItem_Appointment (
    ID int NOT NULL,
    Type char(1) NOT NULL DEFAULT 'A',
    Start datetime NOT NULL,
    AppointmentID int NOT NULL,
    CONSTRAINT PK_BillingItem_Appointment PRIMARY KEY CLUSTERED (ID, Type),
    CONSTRAINT CK_BillingItem_Appointment_Type CHECK (Type = 'A'),
    CONSTRAINT FK_BillingItem_Appointment_BillingItem FOREIGN KEY (ID, Type) REFERENCES BillingItem (ID, Type),
    CONSTRAINT FK_BillingItem_Appointment_Appointment FOREIGN KEY (AppointmentID) REFERENCES Appointment (ID)
)

CREATE TABLE BillingItem_Product (
    ID int NOT NULL,
    Type char(1) NOT NULL DEFAULT 'P',
    Name varchar(50) NOT NULL,
    ManufacturerID int NOT NULL,
    CONSTRAINT PK_BillingItem_Product PRIMARY KEY CLUSTERED (ID, Type),
    CONSTRAINT CK_BillingItem_Product_Type CHECK (Type = 'P'),
    CONSTRAINT FK_BillingItem_Product_BillingItem FOREIGN KEY (ID, Type) REFERENCES BillingItem (ID, Type)
)


Oracle database link via ODBC to SQL Server does not recover automatically

Step by step:

There is no way to test a dblink other than select a dummy over it
A network timeout affects only the running transaction that is currently the dblink. There is no recorded state about the dblink, there is no status like ACTIVE or BROKEN. DBLink creation simply instruct the DB about remote host, port, tns, user and password. Hence drop and recreate the dblink does not affect or change the network or ODBC problem. A pratical case is: select sysdate from dual@dblink. A firewall is in place between both databases. Source DB opens the connection, retrieve data and leave connection opened. After 15mins the firewall drop the connection. At minute 20 another user performs the same query. The database will reopen the connection.
The best automatic way should be to query a table; this only if we are experiecing a simple network issue.

Apart from this I understand that you get the connection dropped and you have to way to re-establish it without drop and recreate the database. Metalink (formerly My Oracle Support) suggest to check if HS=OK is correctly set in the tnsnames.ora. Attention: the HS=OK argument needs to be outside of the CONNECT_DATA portion of the entry. Here an example:

MSSQL =
(DESCRIPTION =
      (ADDRESS_LIST =
          (ADDRESS =
              (PROTOCOL = tcp)
              (HOST = 192.168.36.18)
              (PORT = 1522)
             )
        )
   (CONNECT_DATA =
        (SID = MSSQL)
   )
   (HS=OK)
)

Moreover I suggest you to check (and eventually post) logs on both sides. You can also increase the level of HS_FDS_TRACE_LEVEL parameter.
In addition MOS reports some issue with DG4ODBC and suggest to debug them (and fill a bug if necessary) using trace on the ODBC layer.


Are the columns returned from CROSS APPLY with a Table Valued Function evaluated only once?

The way you have phrased the question indicates you fear that the whole TVF will be evaluated multiple times. This is certainly not the case, as with an inline TVF the inner query is effectively pasted into the outer query as a derived table.
What you can possibly be concerned about is whether the computation of a computed column in the TVF is deferred or not. Unfortunately, the XML query plan has no info on this (you would be looking at the relevant COMPUTE SCALAR).
Paul White has a very good blog post explaining this. Often SQL Server will defer the calculation of a Compute Scalar to later in the plan, and this can become an issue with very complex or computationally difficult calculations.
For example, PATINDEX and SUBSTRING (and even REVERSE) are often used in tandem to do string manipulation. A common trick to avoid lots of copy-pasta is to CROSS APPLY (VALUES) of one calculation, then reference it in another CROSS APPLY, similar to how you might do it procedurally.
What I have noticed from query plans is that the optimizer collapses the whole thing together, no nested loops, no constant scans, which is normally the right idea. As I understand it, the execution engine will afterwards pull out various pieces which it thinks need pre-calculating and storing, and some which it will calculate when needed, but sometimes it can get it wrong, especially when dealing with very large strings.
What you can do in such a case instead is to use OUTER APPLY (VALUES), this appears to create an optimization fence, causing a nested loop with a constant scan. Do not do this blindly. Test it, time it, look at CPU time etc.


By using SQL Window Functions, how can I get the lead of the last row in a partition?

You can specify a window that excludes the current group:

SELECT first_value(c) OVER w
FROM (VALUES
      (1,'1','date1'),
      (1,'2','date1'),
      (2,null,'date2')
) AS t(a,b,c)
WINDOW w AS (
   ORDER BY a
   ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP
);

 first_value
-------------
 date2
 date2

(3 rows)


Cannot dump database in Oracle on Docker

I was able to replicate the error.First login without username parameter into docker container(you'll logged in as root user)

C:\>docker exec -it xedbd bash
bash-4.2# expdp system/oracle  dumpfile=test01.dmp directory=DATA_PUMP_DIR logfile=test.log

Export: Release 18.0.0.0.0 - Production on Mon Feb 17 21:22:19 2020
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

UDE-12547: operation generated ORACLE error 12547
ORA-12547: TNS:lost contact

I'll add user parameter and remember the schema is in pdb you need to add connect identifier in expdp

    C:\>docker exec -it -u oracle xedbd bash
    [oracle@89feb4161c93 /]$ expdp system/oracle@xepdb1 dumpfile=test01.dmp directory=DATA_PUMP_DIR logfile=test.log schemas=test

    Export: Release 18.0.0.0.0 - Production on Mon Feb 17 21:24:12 2020
    Version 18.4.0.0.0

    Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

    Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
    Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@xepdb1 dumpfile=test01.dmp directory=DATA_PUMP_DIR logfile=test.log schemas=test
    Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
    Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
    Processing object type SCHEMA_EXPORT/USER
    Processing object type SCHEMA_EXPORT/ROLE_GRANT
    Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
    Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
    Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
    Processing object type SCHEMA_EXPORT/TABLE/TABLE
    . . exported "TEST"."T1"                                 9.978 MB   72834 rows
    Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
    /opt/oracle/admin/XE/dpdump/9ECC9107E3AE0AADE053020011AC6840/test01.dmp
    Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Feb 17 21:24:53
   2020 elapsed 0 00:00:39


SP for Restoring Database and performing other tasks on it

The only way to really backup a database is to create a backup of the database (using BACKUP DATABASE). All the other methods (scripting database schema and content, copying files around, OS file snapshots) are workarounds, not database backups.

So, as I see it, the best way to have a database copied from one place to another is to restore it from a backup (RESTORE DATABASE). There's no other effective and more efficient way as I see it. That's my 2 cents, anyway.

Now, in order to do all that preparation, I'd make different stored procedures (using dynamic sql to build proper names, paths..etc) that will:

restore databases from a specific location;
prepare specific permissions -> maybe different users..etc;
prepare replication objects;

PS: if it's a possibility, make sure to enable backup compression, as it will really help with size and speed.

PS2: taking into considerations your backup file sizes I wouldn't worry too much about the duration of the operation. Should be fast even on a usual developer box.


Mysql Select Subquery where clause subquery equal alias ( AS subquery named )

You can try this

with t1 as (
select id, name,
(select count(tableB.id) from tableB where tableB.id_tableA = tableA.id) as
haveBonA,
(select count(tableC.id) from tableC where tableC.id_tableA = tableA.id) as
haveConA
from tableA
)

select t1.*,
   case
   when t1.haveBonA = t1.haveConA then "Equal"
   else "Not Equal"
   end equalTableBTableC
from t1
where 1=1


Does Express store any statistics to indicate when performance is held up due to being Express?

SQL Server isn't going to tell you specifically that there is a performance limitation due to it being Express Edition.  But in general, if you start collecting and monitoring wait statistics, you'll see whether there are any bottlenecks due to insufficient CPU or memory.  So if you've given SQL Server Express the max number of CPUs and memory, then bottlenecks in these areas generally indicate that it's being limited by the limitations of the edition.  Technically, you could give it faster processors to help overcome the CPU limiation, but I assume that's not an option.

See SQL Server Wait Statistics: Tell me where it hurts to get started understanding what you need to be monitoring.


Why is cross db ownership chaining on by default for system DB

I have continued to search for reasoning behind this.  Everything I find is some version of

  These system databases must have cross-database ownership chaining turned on in order to function properly source

Clearly the system databases; master, tempdb, and msdb are interdependent. Presumably early in SQL development, some microsoft developer made the choice to use cross-database chaining to solve access issues, and it has never been updated.

Another of of those things that is Microsoft recommends not be used, but they are dependent on it.


It's probably easiest to add one correlated sub-query for each count:

SELECT D.*, S1.*, S2.*, S3.*
     , ( SELECT COUNT(1) FROM Group G WHERE G.<key> = ... ) as GroupCount
     , ( SELECT COUNT(1) FROM Client C WHERE C.<key> = ... ) as ClientCound
FROM Districts as D
CROSS JOIN Seasons as S1
CROSS JOIN Sectors as S2
CROSS JOIN Sites as S3

Since I don't know how Group and Client relates to the other tables, I left that as an exercise for the reader ;-)

It's quite unusual to do a cartesian product between 4 tables in a query, but perhaps you have your reason for that. Beware that the cardinality of the result is the product of the cardinality for the individual tables. If the cardinality for the tables is 100, the result will contain 100^4=100000000 rows. For a cardinality of 1000, you end up with 1000^4=1000000000000. If each row is 1 byte, the result in the latter case is 1Tb.


Recommended distributor architecture for transactional replication?

What is the best practice in order to have this scenario done?

It depends on how much data you are replicating i.e.

Are you replicating selected columns/tables, entire database (all tables), etc ?
Are both nodes located in the same domain or different domains ?
Are you replicating in same region or cross region (US-UK, etc) ?

I have implemented T-Rep where I have used same server as publisher and distributor as the data that was needed to replicate was less and also, have implemented separate distribution database on separate server that does all the heavy lifting of publishing the data to subscribers where we had massive data to push down to subscribers.

You have to consider factors like -

time taken to perform snapshot and applying that snapshot to subscribers
Feasibility of re-initializing the articles when a major (e.g. schema) change occurs to the tables that are involved in replication.

should I create 2 distributors?
You can use the same distribution database. Though, for ease of maintenance and better performance [reducing contention - both writing to and reading from the distribution database] I would highly recommend you use separate Distribution databases.

Remember that distribution database is the heart of replication. So it requires proper maintenance, backups, etc. Now if you have just 1 distribution database that supports multiple publishers and a DISASTER happened, then restoring it from a previous backup will impact ALL publishers.

From BOL :

  In many cases, a single distribution database is sufficient. However, if multiple Publishers use a single Distributor, consider creating a distribution database for each Publisher. Doing so ensures that the data flowing through each distribution database is distinct.

Lastly some good references that will help you :

Deep Dive on Initialize from Backup for Transactional Replication

Replicating Non-Clustered Indexes Improves Subscriber Query Performance

Follow the Data in Transactional Replication - Whitepaper

Troubleshooting Transactional Replication

Scaling Out the Distribution Database


Within a PL/SQL procedure, wrap a query or refcursor in HTML table

It is possible to generate xml with dbms_xmlgen, eg:

select dbms_xmlgen.getxml('select * from dual') as xml from dual;

XML
---
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DUMMY>X</DUMMY>
 </ROW>
</ROWSET>

Using getxmltype instead, it is then possible to apply an xslt transform, perhaps like this:

select dbms_xmlgen.getxmltype('select * from dual').transform(XMLType(
'<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html"/>
<xsl:template match="/">

      <xsl:for-each select="/ROWSET/ROW[1]/*">
       <xsl:value-of select="name()"/>
      </xsl:for-each>

     <xsl:for-each select="/ROWSET/*">

       <xsl:for-each select="./*">
        <xsl:value-of select="text()"/>
       </xsl:for-each>

     </xsl:for-each>

  </xsl:template>
</xsl:stylesheet>')).getclobval() as html from dual;

HTML
----

DUMMY

X

nb credit to Tom Kyte for the xslt from his blog. See also this other post for a slightly more advanced version.


Why is add timestamp column with default not null so slow in Oracle 11g?

This is actually improved in Oracle 12c.

From Oracle Magazine's Ask Tom column:
http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53asktom-1999186.html

  In Oracle Database 11g you were able to perform a fast add of a column
  to a table if it had a default value and was defined as NOT NULL.
  (Arup Nanda has written about this at bit.ly/16tQNCh.) However, if you
  attempted to add a column with a default value and that column
  permitted null values, the ADD COLUMN operation could take a
  significant amount of time, generate a large amount of undo and redo,
  and lock the entire table for the duration of the operation. In Oracle
  Database 12c, that time, volume, and locking are no longer part of the
  process.

I know this doesn't help if you're stuck on 11gR2, since you probably want to get that column added before taking an outage to upgrade the DB.

As to why it takes so long only when the data type is TIMESTAMP, I'm at a loss.  Maybe since the TIMESTAMP data type is a few more bytes to store than DATE, or than VARCHAR2 for empty strings, and those extra bytes are causing a cascade of row migrations.  To test that theory, try adding a fixed length CHAR column (in a non-Prod environment): alter table my_table add my_char_col CHAR(200) default 'hello' not null; I expect this would not be instantaneous due to row migrations.

Your best bet on 11gR2 might be either to

Take an outage at an appropriate time that is ok with your stakeholders to add this column

or

Use DBMS_REDEFINITION to add the column as part of an online redefinition.  This should be non-blocking, and even though it may take hours, the users will not even notice it happening (no locking).  If you have a lot of indexes, this becomes a little more difficult.  If you have foreign keys, this becomes even more difficult.  If you have stored procs or views that reference this table, be sure to recompile them after you FINISH the redef. This will have a side-benefit that your table will be reorganized (i.e. defragmented).

or

Maybe add the column without the NOT NULL constraint.  When that finishes, then alter the column to add the NOT NULL constraint. My intuition is that you'll still get locking somewhere in the process, so no promises. Maybe just a crazy idea.

If you want to try the Online Redefinition, then you might find these links helpful:

http://www.oracle-base.com/articles/11g/online-table-redefinition-enhancements-11gr1.php
http://docs.oracle.com/cd/E18283_01/server.112/e17120/tables007.htm


Database design for application with multiple useres

Just to give you some ideas here are some of the ways I have seen this implemented:

all interactions with the database use stored procedures and views to limit access
groups are implemented in Active Directory. Members are added to groups. When a user logs on and is authenticated to the domain by AD the application calls a stored procedure which returns all the groups the user is a member of. The membership is cached in memory for the duration of the session. Behind the stored procedure are tables for groups, users, user => group, group=> permission  (create a unit of work, process, close...)
in the application every time a page or view loads you check what the user is authorized to do.  It leads to some less than elegant code like   IF CAN_READ then ....ELSIF CAN CREATE then....  There are undoubtedly more sophisticated ways to set the permissions.
the database has an active directory group for the administrators and a generic app user with permissions on all the stored procedures. This method means the application enforces the permission structure set in Active Directory not the database.  Others prefer the database to have roles which set user permissions but then you lose your Active Directory Management.


Mongodb - see when a database was dropped

If the drop command ran slowly, then it will be recorded in the logs (by default >100ms), otherwise the only record of it will be in the oplog (assuming you are running a replica set, even a single node replica set) and that is assuming that it did not occur so far in the past that it has "fallen out" of the oplog (which is a capped collection).

NOTE: Before running queries against your oplog, be aware that any such queries will be table scans, and that running such a query will potentially be slow, especially when run on an active replica set with a large oplog.  If you have a secondary available, you may wish to use that for this type of query rather than add load to your primary.

With that said, let's show an example.  We will call the database "foo", drop it and show how you would search the oplog for evidence of the drop.  From the mongo shell your query would look like this:

use local;
db.oplog.rs.find({ns : "foo.$cmd",  "o" : { "dropDatabase" : 1 }})

And the result, if found, would look like this:

{
        "ts" : Timestamp(1412246712, 1),
        "h" : NumberLong("-6606042550253448275"),
        "v" : 2,
        "op" : "c",
        "ns" : "foo.$cmd",
        "o" : {
                "dropDatabase" : 1
        }
}

After that, the only other place to gather such evidence would be the filesystem, since the files are unlinked/deleted when the DB is dropped.  There are plenty of answers on how to do that (and the potential problems) - I've used this one successfully in the past.


Best way to flag a row for future deletion?

Normal guiding principles are to,

Not delete data. It's easy to put a boolean flag on the data deleted and then for it to seem like the data is deleted to users, without it really being deleted. Do you really want the data gone forever?
Not create 1:1 relationships. If it's 1:1 just merge it together.
"Match delete_date with current date and delete them" I would suggest storing the delete_queue_date instead. The date that the data entered the queue. That's what matters. The date the row actually gets deleted is a function of that. Generally you don't store f(x) on the table, you store x.


Providing that you'll always close a job before opening another under the same ID (although personally you should never have two seperate jobs running under the same ID without another unique identifier to distinguish (You've opened 4909 and 4910 twice))

if you join the table on itself distinguishing them between Open and Closed you can get it all in one short statement

select opened.jobOrderId as [Job Order ID],opened.dateadded as [opened date], closeed.dateadded as [closed date]
FROM JobHistory as opened
LEFT JOIN Jobhistory closeed on opened.jobOrderID = closeed.jobOrderID and closeed.status='Close' and closeed.dateadded > opened.dateAdded
WHERE opened.status='OPEN'

If you've got an actual unique identifier for the job I'd recommend joining on that rather than the JobOrderID column and you won't then need do the statement:

and closeed.dateadded > opened.dateAdded

as that is simply there to ensure that you don't close something that was opened in the future

Ste


Single MySQL DB contains both push and pull data. Recommended path to replication?

You may set up a Master-Master-Replication in which both servers are slave of each other and one can write to both servers.

If you always make changes to just one subset of the tables on each of the servers, you do not run into problems concerning auto increment values (you might want to set auto_increment_increment and auto_increment_offset anyhow) or simultaneous writes to the same data on different servers.

But please note that a Master-Master-Replication can result in severe chaos, if one does something wrong.

Actually I do not understand why you do not fill the product tables in the production directly and not in the development database? You then could use one single master for writes and just keep a slave for backups and statistics / analytics, which want current data, but do not need the latest at all time. Makes configuration, maintenance and failure resistance a lot easier.
That is the solution I would prefer in this case.


A connection cannot be made. Ensure that the server is running

After spending lot of time, I found the solution!

As I mentioned, I had to log into Win10 with User10 and at the same time log into Win7, using User7.

Looks like connection was successful when I log into both PC with the same username.

So, I ended up to run SSMS as different user (in each PC) to be able connect to the other one.

On Win10 PC, I created a shortcut to run SSMA as User7, and this way I was able to connect to WIn7 PC and also created the shortcut in Win7 PC to run the SSMS as User10 to be able to connect to Win10 PC.

This link (FIX Connection) also might be helpful, it did not help me though.

This is the link to create shortcut to run as different user


The statement "Schema of unstructured data can be defined at query time" sounds like a little bit of poor choice of words but likely eludes to the point that all data has a structure, even if it hasn't been defined explicitly yet (such as in a relational database management system).
In the context of NoSQL, an unstructured dataset such as JSON may not explicitly define things like the data types of the data itself, but the consuming applications of that data may define the schema of that data explicitly ahead of time such as when defining models to physically represent your data as objects.
For example, TypeScript is a JavaScript framework where you can define class files to store your model definitions that deserialize the JSON data into objects, such as when you receive data back from a restful web service. Furthermore, you can then persist that data into another database system, such as SQLite for example, where the schema is materialized with different (but compatible) data types. TypeScript has the number data type whereas you could store the same data in SQLite as INTEGER or REAL depending on what fits your use case.
The point being, there is no defined schema at the source of the data, but once it's queried and consumed in an application, it is possible to define one, or even multiple schemas that appropriately fit the shape of the data.
Additionally, some applications implicitly define a schema around the data they consume, for example, with an ORM, or with data migration tools. The purpose of an ORM is to literally define concrete models for a particular set of data. While most ORMs usually consume structured data, there are unstructured ORMs as well.


How to get only the first error with psql --single-transaction

You may just ask it to stop on the first error:

$ psql -v ON_ERROR_STOP=on --single-transaction ...


SQL Server Agent logs in as guest on a linked server

Personally I would try using ORIGINAL_LOGIN() instead of USER_NAME().  USER_NAME() returns the database principal or user associated with your login in the database you are connecting to.  In this case you are probably connecting to master in both cases.  If LinkedServerUser doesn't have a specific login in master on SERVER2 then you will see GUEST.

If on the other hand you use ORIGINAL_LOGIN() you will get the server principal that connected, which will probably be LinkedServerUser in both cases.


Lost connection to mysql while restoring dump. Is it safe to resume?

(n.b. I've never used AWS or RDS so there may be some differences). But as a general rule a MySQL DUMP file is just a series of CREATE TABLE and INSERT INTO statements, that when combined create your database.

Ultimately it depends on what options were chosen as to what does and doesn't get included. But I think that by default it includes the --add-drop-table option. So if you open the file (which is basically a text file) you should see:

DROP TABLE tablename;
CREATE TABLE tablename. . .
INSERT INTO tablename. . .

for all of your tables.

Restarting the loading process will simply start from the beginning of the file and carry out the actions accordingly.

(I assume that the import has actually died - its not uncommon (in my experience) to lose connection to MySQL during the import due to the time taken, but the process continues in the background. You can use SHOW PROCESSLIST to check this).


Using like over charindex to search for pattern

The disadvantage is that you need to escape all characters of special significance in the pattern syntax.

Following your previous question it seems the way you do the escaping affects the cardinality estimates.

Testing on SQL Server 2008/2012

SELECT name
/*Copying into a temp table as on 2012 this table is now in the readonly
 resource database*/
INTO #T
FROM   master..spt_values

Then

SELECT *
FROM   #T
WHERE  name LIKE '%\%%' ESCAPE '\'

gives an estimated row count of 625.33/627.575. But

SELECT *
FROM   #T
WHERE  name LIKE '%[%]%'

gives an estimated row count of 1.8563/1.86296.

In fact no name values in that table contain a literal % character so the second one appears to be more accurate in this case.

The response to this Connect Item states

  We do not have support for precise cardinality estimation in the
  presence of user defined escape characters. So we probably get a poor
  estimate and a poor plan. We'll consider addressing this issue in a
  future release.


Execute SQL query after execute TRUNCATE in a table

How about creating a stored procedure that includes your truncate command and the insert statement.

Since that insert statement only makes sense specifically after calling truncate on your fos_user_user table, you could just as well run 'exec .ufn_Truncate_fos_user' or something like that.


It's possible, but not just with backup and restore.  You've got three separate problems to solve:

Problem 1: How do you delete all but one day at the office?

You can do this with delete statements that touch every table in the system, but if you've got complicated relationships or lots of tables, this can be painful.  You have to make sure you only delete tables with changing data, like sales, and not tables that need to be kept, like config tables and customer lists.

If you've got large quantities of data, this can also be painful for the long-running deletes.  Table partitioning can be a good fit for scenarios like this when you've got, say, >100GB of data involved.

Problem 2: How do you get the data from office to home?

You could solve this with backup/restore, but given that this is a solution you want to implement permanently, something like replication might make more sense.  Transactional replication lets you stream the logged transactions to the second machine, and then your data can look different between the primary machine and the secondary machine.  For example, the home machine might have different indexes depending on how much data you're trying to store.

Problem 3: How do you keep adding the historical data at home?

If you use the backup/restore approach, you'll be restoring a new database every day.  The historical database should be under a different name, and then write scripts to insert the data from the restored database into the backup database.  Again, more tables = more problems here.

If you use the transactional replication approach, this will be much easier.

All three problems are pretty large, so consider breaking this work up into more digestible steps.  Short story, though, this isn't a built-in feature.


No, there is no such configuration available.

Of course, you are not the only one concerned about style. You might find some posts by Alexander Kuznetsov helpful in pursuing your overall reliability goals.

One of his posts is: Developing Low-Maintenance Databases

https://www.simple-talk.com/sql/database-administration/developing-low-maintenance-databases/

He has an online book entitled: Defensive Database Programming

https://www.simple-talk.com/books/sql-books/defensive-database-programming/

But SQL Server has no 'strict checks' although Erland Sommarskog has proposed that something should be done about this.  (No traction yet, though.)

http://www.sommarskog.se/strict_checks.html

So, perhaps you can create a process that examines the text of a stored procedure, but it will not be simple.  And you can have rigid code review.


Please try:
(edited 2014-12-18: replaced Left with INNER join):

SELECT
    `pages_users`.`page_id` ,
    COUNT(`pu2`.`page_id`) AS `count`
FROM
    `pages_users`
    INNER JOIN `pages_users` AS `pu2`
        ON `pu2`.`user_id` = `pages_users`.`user_id`
           AND `pu2`.`page_id` = *PAGE_ID*
WHERE
    `pages_users`.`page_id` != *PAGE_ID*
GROUP BY
    `pages_users`.`page_id`
ORDER BY
    `count` DESC
LIMIT 10

or as an alternative

SELECT
    `pages_users`.`page_id` ,
    COUNT(`pu2`.`pu2_page_id`) AS `count`
FROM
    `pages_users`
    INNER JOIN
    (
        SELECT
            `pu2_intern`.`page_id` AS `pu2_page_id`
        FROM
            `pages_users` AS `pu2_intern`
        WHERE
            `pu2_intern`.`page_id` = *PAGE_ID*
    )  AS `pu2`
        ON `pu2`.`user_id` = `pages_users`.`user_id`
WHERE
    `pages_users`.`page_id` != *PAGE_ID*
GROUP BY
    `pages_users`.`page_id`
ORDER BY
    `count` DESC
LIMIT 10

You could also add another index just on the field page_id:

KEY `page` (`page_id`)

I hope it could help you.


How to return script IDs not in policies

select script_id
from scripts s
    left outer join policy_scripts ps on ps.script_id = s.script_id
where ps.policy_id is NULL;


Data into Star Schema's fact or dimension table?

This is a very interesting, but perhaps also a bit too general as a question.

My general opinion is that if the cardinality of a dimension starts to approach that of the fact table, then there's no point in keeping a separate dimension. If you read Kimball carefully you'll see that on many occasions there's an implicit assumption that dimensions are small when compared to the fact table (by a few orders of magnitude at least).

In your case, you mention a proposal may have up to 20 tasks; you're pretty much on the verge of having a dimension that starts to be too large to be efficiently handled.

However, there's another thing you may have missed. What you're trying to achieve is counting proposals on any given state of their lifecycle. Again, if you go to Kimball, the solution would be an accumulating snapshot fact table. But this comes at a cost, which is the need to define beforehand how many milestones of an object's lifecycle it is worth tracking.

There's an alternative, though, which I developed a while back and blogged about last week, which is what I call a Status Change Fact table, where you track events (in your case, tasks) and add rows to mark each state change in the proposal's lifecycle (blog post here). The basic idea is that if a proposal has two tasks, A and B, that took place on days D1 and D2, you insert three rows on your fact table:

date;task;count
D1;A;+1
D2;A;-1
D2;B;+1

This model, combined with a periodic snapshot to count how many of which are in each state at any given time, should allow you to run simple queries and still get meaningful answers to your questions.

I've implemented this approach in a couple projects recently where objects are tracked through a system, with a good degree of customer satisfaction, even though customers had to be educated to what negative counts in the status change table mean.


Does activating delayed durability affect executing statements?

DELAYED_DURABILITY does not change the number of IO and CPU tick necessary to perform transactions in your server.  It can give a throughput boost to your workload, so you will expect to see the duration being reduced for the same transaction. Generally you expect to see an increase of Transactions/sec. This feature let transactions before being flushed to the transaction log file.

In flight transactions will use the old setting and new transactions will use the new 'DELAYED_DURABILITY' setting.  I did confirm using #sqlhelp hashtag, Denny Cherry was kind enough to answer.

Be mindful that:

  You may still have log I/O contention if there is a high degree of
  concurrency, particularly if you fill up the log buffer faster than
  you flush it.

From Paul Randal's blog:

  Delayed durable transaction commits are asynchronous and report a
  commit as successful before the log records for the transaction are
  written to disk. Writing the transaction log entries to disk is
  required for a transaction to be durable. Delayed durable transactions
  become durable when the transaction log entries are flushed to disk.

From Books online:

  Delayed durable transaction commits are asynchronous and report a
  commit as successful before the log records for the transaction are
  written to disk. Writing the transaction log entries to disk is
  required for a transaction to be durable. Delayed durable transactions
  become durable when the transaction log entries are flushed to disk.

I suggest you also be totally aware of the risk of turning on this feature especially for all transactions as you did.  You have the capability of turning this feature on per transaction.

Few resource :

Control Transaction Durability
Paul Randal's post
Delayed durability performance by Aaron Bertrand


Take a "slice" of a SQL Server database: for all tables, get only the rows that join (directly or indirectly) to a specific row

You could certainly script it with a nested stored procedure:

To be called as something like EXEC recursive_shaped_query @StartTable='SomeTable', @KeyColumn='Id', @ValuesSQL='SELECT Id FROM (VALUES (1),(6),(4576),(42)) AS Ids(Id)'
This then runs via ad-hoc SQL SELECT * FROM SomeTable WHERE Id IN (SELECT Id FROM (VALUES (1),(6),(4576),(42)) AS Ids(Id))
It then cursors through sys.foreign_keys to find any tables that reference this table and…
… for each such table found calls itself like EXEC recursive_shaped_query @StartTable='ReferencingTable', @KeyColumn='Id', @ValuesSQL='SELECT Id FROM SomeTable WHERE Id IN (SELECT Id FROM (VALUES (1),(6),(4576),(42)) AS Ids(Id))'

Of course there would be a number of complications to consider:

You will need to decide what to do if your data structure allows for circular links, like a table containing state machine graphs, or places where two entities might refer to each other, otherwise this will loop to infinity (well, until you hit the "procedure calls nested too deep" error).
You may need to deal with foreign keys acting on multiple fields instead of just one.
The SELECT * FROM t1 WHERE Id IN (SELECT Id FROM t2 WHERE Id IN (SELECT Id FROM Tt3 WHERE ... ))) nesting may get silly long and be queries that take an age to run due to table scans if useful indexes are not in place.

Other things to note:

You might find using joins instead of sub-selects produces more efficient queries (though I think the query planner will do a decent job of optimising the two syntax options to be equivalent).
You might prefer the procedure just emits the ad-hoc SQL needed instead of running it, so you can run it later (and rerun it again elsewhere).
If just emitting the ad-hoc SQL and not running it, a recursive CTE over sys.foreign_keys may be noticeably cleaner than a nested stored procedure - in fact doing that might be better anyway: generate the SQL in on go then run it in one go, perhaps explicitly dropping the data to temp tables then ending the current transaction to release locks before pushing the data out to the reader.
This is all of course assuming that you have foreign keys defined everywhere they should be. In too many databases out there some or all of these constraints are missing.

For our systems in [Day Job] I have a set of such queries manually created to pull out nested data for given entities as these procedures are useful for diagnostics, but I've never needed nearly enough of them to warrant trying to create a generator or other automated solution. I've thought about it as a play project, but not actually done it.


I have worked with Oracle Xmltype for the last 4 years, starting with Oracle 10.2.0.4 and now on Oracle 12c. I find it very reliable. We have used it not only for storage, but also XML manipulation. In that regard Oracle has chosen XQuery above XSLT.

The performance is dependent on the way you define the storage of these XMLs. You can store them as CLOB, as object-relational storage or (in 12c) as binary XML. The choice that you make depends on different parameters. CLOB keeps the white spaces in the XML. Object-relational storage is the smallest and binary XML (the default in 12c) is somewhere in between.

Regarding Query speed, I prefer binary xml, but all 3 solutions are very much workable in my experience. I didn't see any performance differencies between CLOB and Xmltype (stored as clob). When the XMLs are stored in a more compressed format, you could even gain speed.


How do I tell which versions support a given DMV column?

Each version of SQL Server potentially adds new columns to the DMVs.

Correct, and since each CU is technically a new version this can happen inside of major version releases.

  Is there a list somewhere that shows which columns are available for a given version of SQL Server?

While this may not be in the spirit of the question asked, there technically is an answer which is to use the system tables. For example, what columns does sys.availability_groups currently have in this version (14.0.3037):

select name FROM sys.all_columns where object_id = object_id('sys.availability_groups') order by column_id asc

Will output:

╔═════════════════════════════════════════════╗
║                    name                     ║
╠═════════════════════════════════════════════╣
║ group_id                                    ║
║ name                                        ║
║ resource_id                                 ║
║ resource_group_id                           ║
║ failure_condition_level                     ║
║ health_check_timeout                        ║
║ automated_backup_preference                 ║
║ automated_backup_preference_desc            ║
║ version                                     ║
║ basic_features                              ║
║ dtc_support                                 ║
║ db_failover                                 ║
║ is_distributed                              ║
║ cluster_type                                ║
║ cluster_type_desc                           ║
║ required_synchronized_secondaries_to_commit ║
║ sequence_number                             ║
╚═════════════════════════════════════════════╝

More in the spirit of the question, Docs is your best source for columns based on version outside of your own queries.


Identity versus Composite values for Primary Keys in Many-To-Many Tables

A Many-to-Many mapping table must have a unique index on the foreign key columns.  And should normally have an additional unique index on the foreign key columns in the reverse order.

So while adding an additional synthetic key doesn't do any great harm, it's just not very useful.


MySQL Explain gives different result for same query, query performance changes

Thanks for confirming that innodb_buffer_pool_size is OK.

Here are some ideas; I don't know which will be best:

Plan A

pricelist:  INDEX(pCurrent, tagged_to)
materials:  INDEX(companyID, article_nr_lev)

Plan B

SELECT STRAIGHT_JOIN ...
    FROM pricelist ...    -- first
    JOIN pricelist_entries ...
    LEFT JOIN materials

Please provide SHOW CREATE TABLE for each table; there could be some more clues there.

More

materials.article_nr_lev = pricelist_entries.peArtnr -- Whenever JOINing, the datatypes must be the same (or 'close').  Those two value are probably close enough -- same collation (mandatory) and both VARCHAR.  But they are of different lengths; I think that is 'close enough'.  However, do you sometimes need 255 characters, and sometimes need only 50?  For various subtle reasons (having to do with the execution of queries), it is better to keep VARCHAR sizes smaller (yet large enough to be safe).  I suspect the 255 could be changed to 50 without harm.

Also you have a lot of 4-byte INT fields that could probably be turned into smaller fields (eg, 2-byte SMALLINT UNSIGNED).  Shrinking table sizes often leads to improved performance, albeit minor.

category int(11) NOT NULL COMMENT '0 - Inget val 1 - Alkov 2 - ... -- Consider using ENUM.  That would avoid remembering the mapping.  (And take 1 byte instead of 4.)

Be careful about CHARCTER SET -- some columns are dec8, some are utf8.  The may be issues with comparisons, conversions, etc.  (I haven't noticed any problems with the query in question.)


Postgres permissions - schema not showing using \dt

You have to put the schema "data" in your search_path if you want the tables from that schema to show up in a \dt run with no arguments.  (If your import worked, then you already have permissions to the schema, unless you changed users)


As said by @ypercube, lag() will help.

select id,
       (d - lag) as lag,
       case when (d - lag) < interval '24 hours' then false else true end as p
from (
      select id,
             (datetime) as d,
             lag(datetime) over (order by id) as lag
      from lagtest
     ) as t;

For the first row no lag will be displayed.


Sql Server 2008 R2: Simple recovery model with transaction log backups

Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?

Even taking a transaction log backup is not supported for databases using the SIMPLE recovery model. This is a restriction of the database engine based on how this recovery model works, and the recovery features it doesn't support, as you mentioned.

A transaction log backup maintenance plan task automatically skips databases in SIMPLE recovery to avoid causing errors.

  Which backup should be done first, the database backup or the transaction log backups? Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.

For the reasons I mentioned above, it won't matter for databases using SIMPLE recovery, as they will be skipped by the transaction log backup task.

For databases in the other two recovery models, a full backup must exist before you start taking transaction log backups (just the first time), or you will get an error -- this is probably what the articles refer to.

Point-in-time recovery ability is normally driven by business need -- in other words, you determine how critical the data is and how much you can afford to lose, then set the appropriate recovery model to meet those needs, and finally create a backup solution.

Even though SIMPLE recovery does not support point-in-time recovery, if an hour of data loss is okay, perhaps a differential backup solution could work for you. (There are far too many variables that go into developing this kind of solution to give you a complete picture with what was provided in the question.)


Microsoft has deprecated the SQL Server Native Client (NCLI) driver, however SQL Server itself uses a wire-protocol, TDS that is independent of the driver used by the client.

NCLI will continue to work for the forseable future, however no new work will be conducted by Microsoft on the NCLI driver.

Pedro Lopez talks about the announcement, and subtle un-deprecation here.  Also included are some sample connection string changes that will help migrate over to the MSOLEDBSQL driver.


Is it possible to commit a transaction that is waiting to RollBack?

The ROLLBACK must finish in order to get the database back to a table state.

(Without knowing the details of the load, I cannot advise on autocommit.)


How to migrate MySQL Cluster (NDB) backup to a different ndb cluster?

Most certainly it is possible to restore a backup on a different
cluster. This is a standard usage of
backups and should be well covered by the documentation of
ndb_restore.


You could approach it as double-entry book keeping. Each user has an "account" for each product, to which you credit and debit transfers. You act as the "bank" in the middle of the network. Each account's balance is the stock, either on-hand for sale or out for work.

The tables would be something like:

Participant
  ParticipantId
  <other columns>

I specifically do not call this "user" since participants need not be people. You will have a participant record, too.

Product
  ProductId
  UnitOfMeasure    -- will be "kilogram" or "piece"/"quantity"

One row for each of the five you mention, sheet through ready circle.

Account
  AccountId
  ParticipantId
  ProductIt

Each participant needs an account per product since there is no direct translation from e.g. sheet to circle, according to your description. Even from circle to rolled circle there may be wastage during handling so sending out 100 units does not mean 100 units are always returned. It's a lot like owning some dollars and some Yen - you can convert each to the other, but the point-in-time exchange rate fluctuates. Each must be held in its own bank account.

You yourself will have an account for each of the five products you deal with. This represents the items sitting on your shelves at any point in time, waiting for further work or to be sold.

Transfer
  TransferId
  FromAccountId
  ToAccountId
  <probably date, time, recipient etc.>
  Quantity

A row here records the movement of a given quantity of a particular product from one recipient to another. You know the participant and the product from the corresponding account. From the product you can determine if the Quantity represents a kilogram or an item.

For ease of processing you'll probably want to duplicate this under each account. This will need another table:

AccountTransaction
  AccountTransactionId
  AccountId
  TransferId
  DebitAmount
  CreditAmount

For each Transfer there will be two rows in AccountTransaction - one each for the FromAccount and the ToAccount; one will be a debit and the other a credit.

Over the years this table may get large and queries sluggish. At intervals (annual accounts?) it may be worth rolling it forward to a new starting balance and archiving previous transactions to a different table.

To answer your questions:

  I am unable to manage how much stock I have given to labours

Sum that Participant's AccountTransactions.

  How much remain at their end (i.e. labours end)

Well, you know how much you've transferred (previous question) but since you've no strong conversion rate or wastage you cannot know how much is actually in their workshop and how much has been scrapped. For that the labourers would have to report scrap and wastage directly.

  How to maintain stock details for selling purpose at it is not managed through one table.

Sum the AccountTransaction tables for your participant ID - these accounts record the units assigned to you and, presumably, sitting in your warehouse.

Apology: it is a while since I last wrote accounting software. Many of the details are a bit fuzzy now. I don't have the capacity to revise all the details and type a fully polished answer. There are a great many online resources available which can explain it more fully. Some finessing may be require around the foreign keys in Transfer and AccountBalance. Hopefully this is sufficiently clear to point you in the right direction. Good luck.


ssis cannot convert between unicode and non-unicode data types even though the source and the destination columns are both unicode

You said that the input column is showing in the advanced editor as non Unicode even though the source data column is Unicode.

Based on your query, I don't think the source data column is Unicode - because the source data column isn't what you think it is.

Gender__C may be an NVARCHAR column - but it's not your source column. Your source column is based on the values returned by your CASE statement - that those are all CHAR/VARCHAR values.

To make the values returned by the CASE statement NCHAR/NVARCHAR, tell SQL that your text literals are to be treated as Unicode values, by putting an N before the opening single quote:

SELECT
   CASE
        WHEN [Gender__c] = N'Female' THEN N'N'
        WHEN [Gender__c] = N'Male' THEN N'M'
        WHEN [Gender__c] = N'Nainen' THEN N'N'
        WHEN [Gender__c] = N'Mies' THEN N'M'
        When [Gender__c] = N'Ei tiedossa' THEN N'Ei tiedossa'
        ELSE NULL
    END as Gender__c
  FROM [database].[table]

[Since Gender__c is an NVARCHAR, I went ahead and made your comparison values NVARCHAR as well].

Try this, and SSIS should recognize that Gender__c in the result set is a DT_WSTR.


error writing input to commandERROR: Invalid username and/or password

I got more information when I tried to rebuild another backup job in EM using the credentials. I got the same error when I tried to use the same Host Credentials I got the same Invalid username and/or password error on the page.

I could not find anything authoritative on the subject but I read a list of prerequisites from this Oracle Community thread. Paraphrased..

  The user needs to be member of the ora_dba group and have local security privilege Log on as a batch job.

In my case the former was true but the latter was false. An admin on the team was "cleaning" up pcs in an OU and moved this server into another with different GPOs applied.

So in the end, I gave the local account, used as Host Credentials, Log on as a batch job rights and the backup jobs continued to work again.


You need dynamic SQL for DDL like this.

IF NOT EXISTS(SELECT 1 FROM sys.periods WHERE object_id = OBJECT_ID('sysjobhistory'))
    DECLARE @sql NVARCHAR(MAX) = N''
    SET @sql += N'
    ALTER TABLE dbo.sysjobhistory
    ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime);'

    EXEC sys.sp_executesql @sql


You can also set it as a default for any user or role (takes effect after a new connection is established):

ALTER ROLE <rolename> SET client_encoding = 'UTF8';


Is there a DMV that shows the aggregate logical reads on a SQL server?

I think that what you might be after you may find in a different DMV:

select * from sys.dm_os_performance_counters

sys.dm_os_performance_counters

But pay attention to these features:

  Counters like Page reads/sec appear to be cumulative. Which just
  blows my poor head. In that case it should be named like 'pages read'.
  So to get actual Page reads/sec you have to do smth like:

  divide the counter value by amount of seconds since startup (you can
  get it by select datediff(ss,create_date, getdate()) as uptime from
  sys.databases where name = 'tempdb').


Plan A:  Client code to construct the query with the variable column name.

Plan B:  Do a similar thing, but using a Stored Routine in which you use CONCAT, PREPARE and EXECUTE.

Meanwhile, FLOAT(m,n) is a bad idea.  It leads to two roundings.  Suggest simply using FLOAT.  Also, FLOAT contains about 7 significant digits, yet many of your float columns seem to expect more.  Perhaps you want DOUBLE (~16 digits) or DECIMAL.


Should I install SQL Server 64 bit or 32 bit

32 bit OS and 64 bit Processor

If the OS is 32-bit, then your applications would have to be 32-bit as well.

  64 bit OS and 32 bit Processor

You cannot install a 64-bit OS on a 32-bit processor, so this isn't relevant

  when to install SQL Server 64 bit package and when to install 32 bit
  package

SQL Server 2008 R2 is no longer a supported platform, and modern, supported versions of SQL Server are only 64-bit, so the question itself is kind of irrelevent - you should not be installing unsupported versions of SQL Server, however, if you are required to:

On a 32-bit OS, you can only install the 32-bit version of SQL Server.
If you have a 64-bit OS (by definition on a 64-bit processor) then you should install the 64-bit version of SQL Server to take full advantage of available resources.


Key lookup operations can be avoided by the use of a covering index.  Full-text-indexes cannot be "included" in a covering index, however, including the TitleTable in the covering index is still useful since SQL Server can find all the details it needs for the query, aside from the full-text-query results, by seeking the covering index.

I've created a simple test-bed to show this in action.

First, we'll create an empty database for our test, since we cannot create full-text catalogs in tempdb:

USE master;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = N'FullTextTest')
BEGIN
    ALTER DATABASE FullTextTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE FullTextTest;
END
CREATE DATABASE FullTextTest;
ALTER DATABASE FullTextTest SET RECOVERY SIMPLE;
BACKUP DATABASE FullTextTest TO DISK = 'NUL:';
GO

Here, we'll create a mock-up of your table.  You say in the question that all mentioned columns have an associated non-clustered index, so we'll define those too:

USE FullTextTest;
GO

CREATE FULLTEXT CATALOG ftc
WITH ACCENT_SENSITIVITY = OFF
AS DEFAULT
AUTHORIZATION dbo;

CREATE TABLE dbo.TitleTable
(
    PK_ID int NOT NULL
        CONSTRAINT PK_TitleTable
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , FKID1 int NOT NULL
    , FKID2 int NOT NULL
    , Title nvarchar(100) NOT NULL
);

CREATE NONCLUSTERED INDEX IX_TitleTable_FKID1
ON dbo.TitleTable (FKID1);

CREATE NONCLUSTERED INDEX IX_TitleTable_FKID2
ON dbo.TitleTable (FKID2);

CREATE NONCLUSTERED INDEX IX_TitleTable_Title
ON dbo.TitleTable (Title);

Here's the full-text index:

CREATE FULLTEXT INDEX ON dbo.TitleTable(Title)
KEY INDEX PK_TitleTable
ON ftc
WITH (CHANGE_TRACKING = AUTO, STOPLIST SYSTEM);

I've got a database with around 47,000 words in it which I'll use to fill the dbo.TitleTable table:

INSERT INTO dbo.TitleTable (FKID1, FKID2, Title)
SELECT wl.WordRow, wl.WordRow, wl.Word
FROM WordsDB.dbo.WordList wl;

Here's the query from your question:

DECLARE @ID_PARAM1 int;
DECLARE @ID_PARAM2 int;
DECLARE @SINGLE_WORD_PARAM nvarchar(100);
SET @ID_PARAM1 = 46777;
SET @ID_PARAM2 = 46777
SET @SINGLE_WORD_PARAM = N'"' + 'it' + N'"';
SELECT T2.Title
FROM TitleTable T1
    INNER JOIN TitleTable T2 ON T2.FKID1 = T1.FKID1
WHERE T1.FKID2 = @ID_PARAM1
    AND T2.FKID2 = @ID_PARAM2
    AND CONTAINS(T1.Title, @SINGLE_WORD_PARAM);

At this point, if we run the query, we see the following plan:

As expected, there is an index seek on the IX_TitleTable_FKID2 non-clustered index, with an associated key-lookup against the table itself for the Title column.

If we add a compound index on both FKID2 and FKID1, we'd expect a different plan, which is what we get:

CREATE INDEX IX_TitleTable_FTS_Cover_NoInclude
ON dbo.TitleTable (FKID2, FKID1);

However, the key lookup for the Title column is still there.  What if we add an INCLUDE clause to our index above?

CREATE INDEX IX_TitleTable_FTS_Cover
ON dbo.TitleTable (FKID2, FKID1)
INCLUDE (Title);

Success!  No key-lookup operation required.  The cost of the query has also dropped from 0.016 to 0.013, so that's a win.


Passing value of datatype interval in parametrized query

TLDR: Skip to chapter "Superior query" below.
You didn't disclose the module you are working with, but the problem is obviously one of type casting. Looks like your parameter is passed as typed value, and I assume text or varchar. There is no implicit type cast for text -> interval:

SELECT castsource::regtype, casttarget::regtype, castcontext
FROM   pg_cast
WHERE  casttarget = 'interval'::regtype;

castsource             | casttarget | castcontext
:--------------------- | :--------- | :----------
time without time zone | interval   | i
interval               | interval   | i

db<>fiddle here
If my assumptions are correct you should see an error message like:

ERROR:  operator does not exist: interval < text

I am pretty confident your undisclosed module has ways to pass a different data type or an untyped string literal. Postgres does offer this functionality.
You also state:

I also tried $2::interval

This is odd, because an explicit type cast should also work.
Demo

-- interval typed value
SELECT current_timestamp - timestamp '2018-05-04 18:40' < interval '6 days'; -- works

| ?column? |
| :------- |
| f        |

--untyped string literal
SELECT current_timestamp - timestamp '2018-05-04 18:40' < '6 days'; -- works

| ?column? |
| :------- |
| f        |

-- text typed value
SELECT current_timestamp - timestamp '2018-05-04 18:40' < text '6 days'; -- fails!

ERROR:  operator does not exist: interval < text

LINE 2: ... current_timestamp - timestamp '2018-05-04 18:40' < text '6 ...
^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

-- text typed value, with explicit cast
SELECT current_timestamp - timestamp '2018-05-04 18:40' < ('6 days'::text::interval); -- works

| ?column? |
| :------- |
| f        |

The same is true for prepared statements:

PREPARE fooplan_typed_interval(interval) AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1;

EXECUTE fooplan_typed_interval('6 days');

| ?column? |
| :------- |
| f        |

PREPARE fooplan_untyped AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1;

EXECUTE fooplan_untyped('6 days');

| ?column? |
| :------- |
| f        |

PREPARE fooplan_typed_text(text) AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1;

EXECUTE fooplan_typed_text('6 days');  -- only this one fails!

ERROR:  operator does not exist: interval < text

LINE 3: ...ELECT current_timestamp - timestamp '2018-05-04 18:40' < $1;
^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

PREPARE fooplan_typed_text(text) AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1::interval;

EXECUTE fooplan_typed_text('6 days');

| ?column? |
| :------- |
| f        |

db<>fiddle here
Superior query
All this aside, neither of your queries can use an index (not "sargable").
Use something like this instead!
server_pg_module:query("select name from new_table
where creation_date > localtimestamp - interval '1 day' * $1", [6])

You can multiply an interval with integer.
Or if you figure out the problem with passing typed parameters:
server_pg_module:query("select name from new_table
where creation_date > localtimestamp - $1::interval", ['6 days'])

Using localtimestamp to point out that the "current time" depends on your current time zone setting with the type timestamp. Details:

How to get the difference in days between 2 timestamps in PostgreSQL
Ignoring time zones altogether in Rails and PostgreSQL


Magic number in optimal redo size query in Oracle 9i

First sentence in the blog post:

  The Oracle Database Performance Tuning Guide recommends switching logs
  at most once every twenty minutes.

Official source:

Sizing Redo Log Files

  It is not possible to provide a specific size recommendation for redo
  log files, but redo log files in the range of a hundred megabytes to a
  few gigabytes are considered reasonable. Size your online redo log
  files according to the amount of redo your system generates. A rough
  guide is to switch logs at most once every twenty minutes.


Need to Send a formatted HTML Email via Database Mail in Sql Server 2008 R2

Here how can you create HTML body part of your mail.

Step 1:

 DECLARE @Body NVARCHAR(MAX),
    @TableHead VARCHAR(1000),
    @TableTail VARCHAR(1000)

SET @TableTail = '
' ;
SET @TableHead = '' + '' + '' + '' + 'Report generated on : '
    + CONVERT(VARCHAR(50), GETDATE(), 106)
    + '

'
    + '
 PO Date	'
    + 'PO ID	'
    + 'SAP Ref	'
    + 'GRN	'
    + 'DealerID	'
    + 'Dealer Name	'
    + 'Status
' ;

SET @Body = ( SELECT    td = CONVERT(VARCHAR(10), P.Date, 120), '',
                        td = P.ID, '',
                        td = P.ID2, '',
                        td = G.ID, '',
                        td = D.ID,'',
                        td = D.Name,'',
                        td = CASE WHEN G.SubmittedDate IS NULL THEN 'New'
                                  ELSE 'Dealer Submitted'
                             END, ''
              FROM      I_CancelledGRN I
                        INNER JOIN TxnGRN G ON G.ID = I.ID
                        INNER JOIN Distributor D ON D.UID = G.DistributorUID
                        INNER JOIN POTxn P ON P.SiteUID = G.POTxn_SiteUID
                                              AND P.UID = G.POTxnUID
              WHERE IsCancelled IS NULL
            FOR   XML RAW('tr'),
                  ELEMENTS
            )

SELECT  @Body = @TableHead + ISNULL(@Body, '') + @TableTail

Step 2: create database email profile

Step 3: Send Email

EXEC sp_send_dbmail
  @profile_name='DatabaseEmailProfile',
  @copy_recipients ='aasc@stackexchange.com',
  @recipients='aa.sc@outlook.com',
  @subject='Query Result',
  @body=@Body ,
  @body_format = 'HTML' ;


SQL Server Management studio slow connection or timeout when using Windows Authentication

Try running a trace with SQL Profiler while you, and then your coworker, connect to the server.
Select RPC, SQL Statement & PreConnect - Starting/Completed.
Select Save Results To Table option, then compare the 2 tables to find the bottleneck.

Or, since you're connecting by IP, it could be doing a Reverse DNS lookup. If so, add a entry in your hosts file.


What are the consequences of not specifying NOT NULL in PostgreSQL for fields which can't be null?

What happens when a new programmer arrives and has to write an app against that db? They don't know that field x has to be NOT NULL.

Another programme might assume that all field x's are NOT NULL for performing counts say, but some now are NULL because of the new programme, leading to inconsistent and difficult to trace errors.

IMHO it is always best to enforce data integrity rules as near to the data as possible, i.e. in the database. That way, new apps and/or programmers can't mess up your data.

Programmers, applications, languages and frameworks come and go. Data and databases tend to persist. The database is your last line of defence against inconsistent, potentially erroneous data.

Make maximum use of your database's integrity constraint enforcement mechanisms, even at the expense of performance. A slow system that produces correct results is infinitely superior to a fast one that gets things wrong!


SQL Server 2008 Maintenance Plan Failure

You have "SET ANSI_WARNINGS  OFF" when running the code as T_SQL

When running the job this setting must be different.


Patching strategy when Windows and SQL Server patches not applied for over 3 years

IMHO I'd recommend breaking up the updates into major SPs and OS vs SQL. That way if anything breaks you can rollback more easily and pinpoint issues. For example, roll out Server 2012 SP1 let it mature for a week or two, then do a SQL SP wait a week or two, etc...

Your methodology sounds good for patching the secondary node, failover, patch primary node, and then balance the cluster.


Where does the magic column "name" come from?

NAME is actually a function. It's a quirk of Postgres that a function with one argument e.g. function(arg) can also be called as arg.function. From the docs:

  The equivalence between functional
  notation and attribute notation makes
  it possible to use functions on
  composite types to emulate "computed
  fields".

NAME is an internal type for object names, and this function is casting its argument to that type and returning it.


Obtaining gratis copy of the SQL specification?

Here they are:

SQL:2008 draft
ISO/IEC 9075:1992 2nd Draft

But I'd rather read read a book:

SQL-99 Complete, Really
Joe Celko's SQL for Smarties


Not able to access stored procedure from my application

The error you provided is generated by your driver. See this question, which points you to a blog entry giving an answer. To sum it up, you have two options:

The GRANT statement you tried
adding noAccessToProcedureBodies=true to your database connection string (which is also suggested by the error message)

Since you are using a shared hosting server it is likely that you don't have privileges on mysql.proc and you don't have root access to the server, so you can't GRANT them. Therefore you should try the second approach.


How do I know an index is used for any queries?

Be careful - if you have usage statistics recently reset or you have an index which is so rarely used its usage statistics was cleared long ago - this solution is useless for your scenario and can cause problems.

Make use of sys.dm_db_index_usage_stats

SELECT
   OBJECT_NAME(object_id) TableName,
   (SELECT name FROM sys.indexes Indices WHERE Indices.object_id =
       IndexStats.object_id and Indices.index_id = IndexStats.index_id) IndexName,
   *
FROM sys.dm_db_index_usage_stats IndexStats;

This outputs all indices that recently done any actions to - scans, lookups, seeks and updates. This won't list indices for some idle tables which are not accessed for weeks.

With this output you can look at each index recent usage. If you see that it only has updates then it wasn't used for anything else recently. This likely indicates it can be deleted.


How to estimate the I/O operations performed by MySQL queries?

Community Wiki answer generated from comments on the question by Raymond Nijland

Use EXPLAIN. This way you can see if an query MAY need disk IO. You need to avoid in the column extra "Using temporary" or ""Using temporary; Using filesort (note the filesort is a misleading name: if the result set fits in memory, quicksort is run in memory)".

This is most likely to be caused by subqueries/unions/order by/group by/... If your result is big and a MyISAM disk-based temporary table is created, and you need to sort the result, you are sorting the result set based on IO reads and IO writes with quicksort algorithm.

In Internal Temporary Table Use in MySQL you can read when MySQL needs to create a disk-based MyISAM table. Maybe you can use avg_row_length * rows (though note that the rows value from explain is not exact with InnoDB engine) to check if the result fits in the heap. See SHOW TABLE STATUS Syntax.

  In general is InnoDB or MyISAM better for avoiding I/O requests?

InnoDB will buffer table data and index data, whereas MyISAM only buffers index keys. An I/O to the table data is needed when explain column extra doesn't say "Using index".

If both are using indexes: With InnoDB, if the buffer is hot it can load data from memory. If indexes need to come from disk there a formula that you can use to calculate needed IO reads for selects, inserts, and updates. From Estimating Query Performance:

  For small tables, you can usually find a row in one disk seek (because the index is probably cached). For bigger tables, you can estimate that, using B-tree indexes, you need this many seeks to find a row:

log(rows) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1

InnoDB indexes are larger because it stores data off the PRIMARY / UNIQUE key into an KEY index. This is faster and needs even less IO seeks but you can compress InnoDB data or indexes.


DISCLAIMER : Never Learned Relational Algebra but it looks interesting

From the schema given and your question, this is what the SQL should be:

SELECT
    emp_mgr.person_name
FROM
    manages emp_mgr
    INNER JOIN employee emp ON emp_mgr.person_name  = emp.person_name
    INNER JOIN employee mgr ON emp_mgr.manager_name = mgr.person_name
WHERE
    emp.street = mgr.street AND
    emp.city = mgr.city
;

Here is another query that only uses JOINs, no WHERE clause:

SELECT
    emp.person_name
FROM
    (SELECT A.person_name,B.street,B.city FROM manages A
    INNER JOIN employee B ON A.person_name = B.person_name) emp
    NATURAL JOIN
    (SELECT A.manager_name,B.street,B.city FROM manages A
    INNER JOIN employee B ON A.manager_name = B.person_name) mgr
;

The first query gets all employees who are managed and their managers in the form of a Cartesian Product. Then, it looks for a common street and city.

The second query collects personnel records (name,street,city) of employees and their managers and performs a NATURAL JOIN between the employess and their managers using (street,city).

If you can transalate both queries back to Relational Algebra, I think you will have what you are looking for. I believe the second may be of better help.


Adding a new data set in SSRS with nested stored procedures

I was able to add the data set by putting it into the XML manually, the GUI refused to save the change.