SQL Data Warehouse - Is it possible to switch a whole table to a partition? Probably a stupid question, but I wanted to know if it was possible to switch a whole table into a partition of a larger table?

I'm guessing not, as check constraints aren't available to constrain the table on the partition boundary.

Azure SQL Data Warehouse does not support check constraints so partition switching is a slightly different ballgame.  The partition scheme as described in the table DDL (rather than a partition schema/function pair) must match exactly in both tables.

Code example duplicated from here:

-- Assume we have a file with the values 1 to 100 in it.

-- Create an external table over it; will have all records in
IF NOT EXISTS ( SELECT * FROM sys.schemas WHERE name = 'ext' )
EXEC ( 'CREATE SCHEMA ext' )
GO

-- DROP EXTERNAL TABLE ext.numbers
IF NOT EXISTS ( SELECT * FROM sys.external_tables WHERE object_id = OBJECT_ID('ext.numbers') )
CREATE EXTERNAL TABLE ext.numbers (
    number          INT             NOT NULL
    )
WITH (
    LOCATION = 'numbers.csv',
    DATA_SOURCE = eds_yourDataSource,
    FILE_FORMAT = ff_csv
);
GO

-- Create a partitioned, internal table with the records 1 to 50
IF OBJECT_ID('dbo.numbers') IS NOT NULL DROP TABLE dbo.numbers

CREATE TABLE dbo.numbers
WITH (
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED INDEX ( number ),
    PARTITION ( number RANGE LEFT FOR VALUES ( 50, 100, 150, 200 ) )
    )
AS
SELECT *
FROM ext.numbers
WHERE number Between 1 And 50;
GO

-- DBCC PDW_SHOWPARTITIONSTATS ('dbo.numbers')

-- CTAS the second half of the external table, records 51-100 into an internal one.
-- As check contraints are not available in SQL Data Warehouse, ensure the switch table
-- uses the same scheme as the original table.
IF OBJECT_ID('dbo.numbers_part2') IS NOT NULL DROP TABLE dbo.numbers_part2

CREATE TABLE dbo.numbers_part2
WITH (
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED INDEX ( number ),
    PARTITION ( number RANGE LEFT FOR VALUES ( 50, 100, 150, 200 ) )
    )
AS
SELECT *
FROM ext.numbers
WHERE number > 50
GO

-- Partition switch it into the original table
ALTER TABLE dbo.numbers_part2 SWITCH PARTITION 2 TO dbo.numbers PARTITION 2;

SELECT *
FROM dbo.numbers
ORDER BY 1;

HTH


MySQL Query optimisation advice I'm running the below query and wanted to ask about how i could optimise this query. This query has been in use where i work for a while and i have the feeling that it could be optimised as its run regularly with different parameters for reporting purposes

First run takes around 2 minutes. Subsequent runs take around 30s as the query cache has kicked in i believe.

The tags table has over 15million rows and contains tags for users as well as other tables like companies.

CREATE TABLE `tags` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`type` enum('company','review','job','sector','user') NOT NULL DEFAULT 'company',
`fk_id` int(11) DEFAULT '0',
`tag_key` varchar(24) DEFAULT NULL,
`tag_value` varchar(128) DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `tag_key_value` (`tag_key`,`tag_value`) USING BTREE,
KEY `fk_id_key` (`fk_id`,`tag_key`) USING BTREE,
FULLTEXT KEY `tag_value` (`tag_value`)
) ENGINE=InnoDB AUTO_INCREMENT=15725767 DEFAULT CHARSET=utf8

The users table has around 450k rows.

    CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(30) DEFAULT NULL,
`title` int(1) NOT NULL,
`firstname` varchar(50) NOT NULL,
`surname` varchar(50) NOT NULL,
`email` varchar(255) NOT NULL,
`password` varchar(255) DEFAULT NULL,
`university` int(11) NOT NULL DEFAULT '0',
`university_start_year` varchar(20) NOT NULL,
`university_end_year` varchar(20) NOT NULL,
`degree_discipline` varchar(3) DEFAULT NULL,
`signup_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`newsletter_subscription` int(1) NOT NULL DEFAULT '1',
...
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `university` (`university`) USING BTREE,
KEY `university_start_year` (`university_start_year`) USING BTREE,
KEY `university_end_year` (`university_end_year`) USING BTREE,
KEY `degree_discipline` (`degree_discipline`) USING BTREE,
..
KEY `email` (`email`),
FULLTEXT KEY `firstname` (`firstname`,`surname`,`comments`,`email`)
) ENGINE=InnoDB AUTO_INCREMENT=1188075 DEFAULT CHARSET=utf8

I don't have the ability to change the table structure or indexes so any input on the optimising the query would be greatly appreciated. I thought that maybe using sub queries or optimising how the joins work would be the first point to address but i'm a bit lost of how i would tackle this.

SELECT u.firstname, u.email
FROM users AS u
LEFT JOIN tags as tags ON tags.fk_id = u.id AND tags.type = "user" AND tags.tag_key = "option"
LEFT JOIN tags as tags6 ON tags6.fk_id = u.id AND tags6.type = "user" AND tags6.tag_key = "role"
LEFT JOIN tags as tags7 ON tags7.fk_id = u.id AND tags7.type = "user" AND tags7.tag_key = "sector"
WHERE (user_type IN ("1")) AND
(tags.tag_key = "option" AND tags.tag_value IN ("1", "2", "8")) AND
((degree_discipline IN ("ACC", "ACT", "ACF", "INS", "CBN", "INV", "RBN", "BNK", "ECO", "FIN", "MAT"))
OR (tags6.tag_key = "role" AND tags6.tag_value IN ("ACC", "ACT", "ACF", "INS", "CBN", "INV", "RBN", "BNK", "ECO", "FIN", "MAT"))
OR (tags7.tag_key = "sector" AND tags7.tag_value IN ("ACC", "ACT", "ACF", "INS", "CBN", "INV", "RBN", "BNK", "ECO", "FIN", "MAT"))) AND
(u.signup_date >= "2012-09-01" ) AND
newsletter_subscription = "1"
GROUP BY u.email

Explain

If there's any other information that i can provide that would be helpful let me know.

Server Environment

These queries are being run on a replication slave for reporting purposes. This replication server has hourly backups run on it via mysqldump which could be affecting the caches but thats only a guess at this point.

EAV (key-value) schema is clumsy and hard to optimize.

Please provide SHOW CREATE TABLE for each table.

What table(s) are newsletter_subscription and degree_discipline in?

These should help:

tags:   INDEX(fk_id, type, tag_key)
users:  INDEX(newsletter_subscription, user_type, signup_date)

The Query cache, if invoked, will take only milliseconds, not 30s.  It sounds like other I/O caching kicked in.

Also, try this...  Try 3 queries, each with one of the OR clauses.  While doing it, see if you can simplify each.  For example, when fetching tag6, there is no need to join to tag7.  If they add it up to less than 2 minutes (or 30 sec), we can discuss using UNION to finish out the query.

A few more hints...

( SELECT u.firstname, u.email
    FROM users AS u
    LEFT JOIN tags as tags ON tags.fk_id = u.id
                          AND tags.type = "user"
                          AND tags.tag_key = "option"
    LEFT JOIN tags as tags6 ON tags6.fk_id = u.id
             AND tags6.type = "user" AND tags6.tag_key = "role"
    LEFT JOIN tags as tags7 ON tags7.fk_id = u.id
              AND tags7.type = "user" AND tags7.tag_key = "sector"
    WHERE (user_type IN ("1"))
      AND (tags.tag_key = "option" AND tags.tag_value IN ("1", "2", "8"))
      AND ((degree_discipline IN ("ACC", "ACT", "ACF", "INS", "CBN", "INV", "RBN", "BNK", "ECO", "FIN", "MAT"))
) UNION DISTINCT
( SELECT ... same ...
    FROM ... same ...
    WHERE ... second part of OR ...
) UNION DISTINCT
( SELECT ... 3rd ...
) GROUP BY u.email


PostgreSQL partial index unused when created on a table with existing data In PostgreSQL 9.3, I am attempting to create an efficient index on a rarely-used (0.00001% of total records) boolean column. To that end, I discovered this post on SO: https://stackoverflow.com/a/12026593/808921

I am attempting to make use of the "partial index" feature of PostgreSQL that Erwin Brandstetter recommends. I have a table with a couple million records already, and I would like to add the index to that table, like so:

CREATE INDEX schema_defs_deprovision ON schema_defs (deprovision)
WHERE deprovision = 0;

(the vast majority of the records will have deprovision = 1)

The problem is that when I try to use this index with the simplest of queries, PostgreSQL acts as if it isn't there:

explain select * from schema_defs where deprovision = 0;

Seq Scan on schema_defs (cost=0.00..1.05 rows=1 width=278)
Filter: (deprovision = 0)

The really strange thing is that I have found that if this index is created before there is data in the table, then it does actually work fine. Don't believe me? Here are some SQL Fiddle entries that prove it:

Partial Index Created After Inserts (Index not working)

Partial Index Created Before Inserts (Index working properly)

In both of those, just expand the "View Execution Plan" link to see what I'm talking about.

So, my question is - what do I have to do in order to get PostgreSQL to start using a partial index on a table that had data in it before the index was created?

BTW I'm also the developer of SQL Fiddle and this question is related to a new development effort I'm making for it.

Run ANALYZE after the index has been added. And make sure the column deprovision has statistics. How to verify?

Basic statistics in pg_class:

SELECT relname, relkind, reltuples, relpages
FROM   pg_class
WHERE  oid = 'schema_defs'::regclass;

Data histograms per column in pg_stats (pg_statistics):

SELECT attname, inherited, n_distinct
     , array_to_string(most_common_vals, E'\n') AS most_common_vals
FROM   pg_stats
WHERE  tablename = 'schema_defs'
AND    attname = 'deprovision';

The manual:

  The PostgreSQL query planner relies on statistical information about
  the contents of tables in order to generate good plans for queries.
  These statistics are gathered by the ANALYZE command, which can be
  invoked by itself or as an optional step in VACUUM. It is important to
  have reasonably accurate statistics, otherwise poor choices of plans
  might degrade database performance.

  The autovacuum daemon, if enabled, will automatically issue ANALYZE
  commands whenever the content of a table has changed sufficiently.
  However, administrators might prefer to rely on manually-scheduled
  ANALYZE operations, particularly if it is known that update activity
  on a table will not affect the statistics of "interesting" columns.
  The daemon schedules ANALYZE strictly as a function of the number of
  rows inserted or updated; it has no knowledge of whether that will
  lead to meaningful statistical changes.

In your case, analyzing just the one column would do the job:

ANALYZE table_name (deprovision);

While being at it, it makes no sense to have the index on the column deprovision. Given the predicate WHERE deprovision = 0 it does not carry additional information. You might as well use a constant expression:

CREATE INDEX schema_defs_deprovision ON schema_defs ((true))
WHERE deprovision = 0;

Just a proof of concept. This would not be any more useful. In this special case you wouldn't need an index column at all, but you must provide at least one column or expression. So use the primary key (since it does not change and is indexed anyway, you don't introduce more restrictions / overhead costs) or any other small column (<= 8 bytes) that might be useful for queries.

CREATE INDEX schema_defs_deprovision ON schema_defs (id)
WHERE deprovision = 0;

sqlfiddle.com

The demo fiddles are misleading.

  Partial Index Created Before Inserts (Index working properly)

Your demo table only has 4 rows. Postgres should not be using the index. A similar problem, just the other way round. Postgres does not have statistics on the table immediately after creating it - until the first run of ANALYZE. Then it knows there are only 4 rows and won't touch the index any more.
So why does it work properly on your second demo? The manual:

  For efficiency reasons, reltuples and relpages are not updated
  on-the-fly, and so they usually contain somewhat out-of-date values.
  They are updated by VACUUM, ANALYZE, and a few DDL commands such as
  CREATE INDEX.

Bold emphasis mine. If you create the index after inserting rows, those basic statistics in pg_class are updated. But only those, not the detailed statistics in pg_statistic:

  Entries in pg_statistic are updated by the ANALYZE and VACUUM ANALYZE
  commands, and are always approximate even when freshly updated.

To make Postgres use the partial index (in particular in it's original form that is not useful for anything else) you also need the data histogram in pg_statistic informing the query planner that deprovision = 0 is actually a rare case, so it will pay to use the index.

Autovacuum takes care of this. It schedules VACUUM and ANALYZE automatically. But there is a time frame (depends on settings and load) between writes to the table and the next ANALYZE run. If you run queries immediately after table creation or changes to the table, those latest changes are not reflected in the statistics, yet. Never mind if that does not change statistics in a relevant way. If it does, for instance after a large INSERT or immediately after table creation, run ANALYZE manually to get proper query plans.

Note that temporary tables are not covered by autovacuum at all. You always need to run ANALYZE manually on those if you need it:

Are regular VACUUM ANALYZE still recommended under 9.1?

I don't know how you configured autovacuum and whether / when you run ANALYZE manually. But I noticed in the past that sqlfiddle can be misleading due to missing / outdated statistics.

I would be very interested how ANALYZE is handled behind the curtains on sqlfiddle. It may be best not to do anything special, but some info would be welcome. Maybe one basic webpage per available RDBMS version?

Demo

I created an SQL Fiddle to demonstrate the effects of CREATE INDEX and ANALYZE on the various statistics.

The effects show (at least) on the first run for me. May not be reproducible on later runs, you would have to create a new schema and run again.

First we see neither basic statistics in pg_class:

relname      reltuples  relpages
schema_defs  0          0

Nor any entries for deprovision in pg_statistics at all (no result).
Postgres has no idea what's in the table and defaults to using the index - which is a bad choice!

After CREATE INDEX, we see basic statistics, but still no data histogram in pg_statistics.

After ANALYZE we see both.

With proper statistics, Postgres now uses a sequential scan (good choice, even if there is an index - it would be more expensive for so few rows).


Calling TVFs on another Instance We have a database on a SQL Server 2014 instance. We have another on a 2008R2 instance. Both on the same server.

I have created a linked server on the 2008R2 instance to the 2014 one.
However, I am only able to see tables & views.

Is it possible to call stored procedures and functions in the 2014 instance using the linked server?

If not what is my best/simplest solution?

You can call procedures easily:

EXEC LinkedServer.Database.dbo.Procedure;

TVFs will be a different issue:

  Msg 4122, Level 16, State 1
  Remote table-valued function calls are not allowed.

And no, you can't fool SQL Server by creating a synonym so that it doesn't detect a 4-part name; it will just return a different error, e.g.:

  Msg 7357, Level 16, State 2
  Cannot process the object ""db"."dbo"."synonym"". The OLE DB provider "SQLNCLI11" for linked server "LinkedServer" indicates that either the object has no columns or the current user does not have permissions on that object.

To work around this, you can either put the call to the TVF inside a remote procedure and call it like above, or do something like this:

EXEC LinkedServer.Database.sys.sp_executesql N'SELECT ... FROM dbo.TVF();';

If you need more direct interaction (e.g. cross applying the TVF against local tables), you might have to perform that on the remote server using a linked server in the other direction for the table data:

-- instead of
SELECT ... FROM dbo.foo AS t CROSS APPLY LinkedServer.db.dbo.tvf(t.id);

-- you would do:
EXEC LinkedServer.db.sys.sp_executesql N'SELECT ...
  FROM ReverseLinkedServer.db.dbo.foo AS t CROSS APPLY dbo.tvf(t.id);';


Unknown database corruption: Data error in cyclic redundancy check I have a server that is throwing an error message when I try to backup a database. The error is:

10 percent processed.
20 percent processed.
Msg 3271, Level 16, State 1, Line 1
A nonrecoverable I/O error occurred on file "R:\data\MyDB.mdf:"
         23(Data error (cyclic redundancy check).).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

I thought it was a database corruption, so I executed:

DBCC CHECKDB([MyDB]) WITH NO_INFOMSGS;

and no errors were found.

What could be happening?
What are your suggestions regarding this datafile?

If you are experiencing corruption, the first thing I would do is make sure you have a valid backup from a previous run on standby.  Restore that somewhere (if possible) so you have access to the data.

You will need to run a DBCC CHECKDB to see what exactly is going on.  If DBCC CHECKDB comes back with no problems, delete your backup file and try re-creating it again from scratch (it may be a corrupted backup file).

If DBCC CHECKDB shows errors, you can try running a repair_fast or a repair_rebuild on the database, but more than likely you will need to run a repair with the repair_allow_data_loss option.  As the option says, you may lose data.  If the problem lies with an index, just drop / recreate the index.  If the problem lies with a data page, then you will need to correct it with the repair_allow_data_loss option and deal with the loss.

After you correct the error, ensure the database page_verify option is set to CHECKSUM.

ALTER DATABASE [MyDB] SET PAGE_VERIFY CHECKSUM

To find the lost data, you could try to take a new full backup and try using a data comparison tool against the old backup and the new backup to identify records that may have been lost.  This will be difficult and time consuming.  There may be a better way using the pages displayed in DBCC CHECKDB when the repair is run but someone else would have to fill in that part as I'm not too familiar with this.


Force SQL Server to parse "DDMMYYYY" I'm developing ETL from a file where dates are expressed in the format "DDMMYYYY", example "31012016".

I'd like to grab this string and pass it directly to the SQL text where data will be inserted. I don't want to parse it into C# DateTime to then format it to "YYYY-MM-DD" so that SQL Server parses it once more.

As I do currently, the date string is parsed once by .Net, then formated by .Net and printed in the SQL code, then parsed again by SQL Server. I want to grab the string from the flat file and place it directly into SQL code without any conversion.

SQL Server recognizes "YYYYMMDD", but it fails for "DDMMYYYY" with message:

  Conversion failed when converting date and/or time from character string.

Example:

DECLARE @datevar date = PARSE('31012016' as date using 'pt-BR');
SELECT @datevar;

Is there any way to make it work?

I tried PARSE('31012016' as date using 'pt-BR') but raised exception. TRY_PARSE returns null. Also tried CAST and CONVERT (Transact-SQL) but there doesn't seem to exist this style.

When a date is represented as 8 numerals without any delimiters, SQL Server will always try to interpret it as YYYYMMDD. That format has deliberately been chosen as locale- or culture-independent (being also one of ISO 8601 standard date formats, as a matter of fact), and there is no way to have the server interpret it as DDMMYYYY or in any other way.

So, either rearrange the date parts of your DDMMYYYY string to make it YYYYMMDD:

CONVERT(
  date,
  SUBSTRING(@datestring, 5, 4) + SUBSTRING(@datestring, 3, 2) + SUBSTRING(@datestring, 1, 2)
)

or insert delimiters to make it match one of the known delimited DMY formats, for instance DD/MM/YYYY:

CONVERT(
  date,
  STUFF(STUFF(@datestring, 5, 0, '/'), 3, 0, '/'),
  103  -- DD/MM/YYYY
)


Bit column vs relationships? Two examples:

Table users, each user may have or not have some of ~10 possible permissions (like "can login, can post, can edit, can delete" etc).

Table articles, each article may have or not have some of ~10 possible attributes (like "for children", "18+", "less than 10 minutes to read", "long read").

What is the canonical \ more efficient \ fastest way to store it in the database?

I can think of two ways:

Option 1:
Bit-like kind of column, have column "permissions"\"attributes" and store a value which can be bit-interpreted like "0101100010", i-th bit is the flag for i-th attribute\permission

Option 2:
Relationship. Create table user_permissions, put 10 values with their IDs there, then create table user_permissions_map and keep this many to many (M:N) relationship in this table.

I'm a bit afraid of option 2 because it looks like it will require additional queries\lookups in permission_map table each time I need to check user's permission. When with option 1 it's just a column belonging to the user, makes it much simpler to check the permissions.

The bit-like field will make it harder to find all users or articles with a certain property. For users/permissions, that's not much of a problem (unless you want a list of e.g. all administrators). For articles, I can imagine that you want to filter on certain attributes (e.g. only non-18+ articles). You also have the option to store those fields (option 3) as single bit columns; then, no extra table is needed.

Another consideration: what happens when you want to add an additional permission/attribute which is by default set to '1'/'true'? For the bitmask field (option 1), this can be done by adding the right power of 2 to all values in column permissions; for option 2, you need a mass INSERT in user_permissions_map; adding an extra bit column with ADD COLUMN and DEFAULT true (option 3) is clear and easy.


Materialized views in oracle How are materialized views implemented in oracle? I've created a materialized view in oracle. What I now see is a quite normal table with a job. Are materialized views translated into tables & jobs in oracle?

A materialized view in Oracle is a combination of a structure to hold the data (a table), a job that refreshes the data (a job), and a process that figures out how to refresh the data based on the specified query.  This process would generally involve the creation and maintenance of materialized view logs on the base table to track changes so that the materialized view can be refreshed incrementally though that is technically optional.  It is also common to organize multiple materialized views into refresh groups so that the materialized views themselves are transactionally consistent with each other.

The Materialized View Concepts and Architecture chapter of the Advanced Replication manual is a good place to start reading up about this sort of thing.


How can I fix the Oracle syntax of this update by the result of a select in Oracle 10g? I have a problem with an update statement, that I developed under 11g, when running under 10g.

I reduced the problem to:

Create table test (
   col1 number(10)
);

insert into test values (1);

update test set col1 = (select 1  from DUAL ) + 1;

select * from test;

When executing on Oracle 10, I get the following error:

update test set col1 = (select 1  from DUAL ) + 1
Error at line 1
ORA-00933: SQL-Befehl wurde nicht korrekt beendet

Any ideas, how I can modify the statement to run under 10g?
I want to use a single statement.

The documentation for the Update statement allows this syntax even in 10g, so perhaps this is a bug.  I looked for one on My Oracle Support, but couldn't find one.  Perhaps you should open a SR with Oracle to see what they say.

As a workaround if you re-arrange the statement to have the literal first it will work on both versions.

update test set col1 = 1 + (select 1 from DUAL );

Another workaround is to wrap the function in to_number.  I don't see why either should be necessary.  Updating a column to the results of a compound expression that contains a scalar subquery expression added to a value is supported by the syntax and should work.

Similar behavior found here also with no resolution.


Replicating contained SQL databases to customer in near real-time We host a number of customer SQL Server databases that we need to replicate to external customer sites in near real-time. The main constraint we have is that, for security reasons, our databases are partially contained to ensure customer A can not see customer B's database.
Depending on how big the customer is, the databases range from 5GB to 200GB in size. There are usually up to 5GB of changes on any one day.
Other constraints are that our SQL servers are secured behind firewalls and DMZs. We don't want to expose them directly to customers. A proxy we host in our DMZ may be a workaround. In addition, Availability Groups, Mirroring and Log shipping have version compatibility constraints that would lead to tight coupling which would be undesirable with a single customer, let alone across multiple customers that have potentially a wide variety of SQL versions. We are running SQL Server 2019 Enterprise which AFAIK means customers would need to be running SQL Server 2019 for Availability Groups, Mirroring or Log shipping compatibility.
Options I've considered so far

Change Data Capture (CDC). SQL Server 2019 does not support CDC with contained databases.
Table replication. SQL Server 2019 does not support table replication with contained databases.
Triggers. Triggers are synchronous and can have unwanted side effects such as slowing
down transactions and failing transactions if replication fails.
3rd party software HVR Software and Qlik replication. These both rely on initially enabling CDC to add "supplemental logging" to the log stream. They do not actually rely on CDC for replication but need the additional metadata in the SQL log files that requires enabling CDC. Due to CDC not being supported with contained databases this rules out these options too.
Sending a copy of the complete database (in compressed BACPAC format via SFTP) to the customer on a schedule. The databases are then restored on the customer site. This complete process takes approximately 45 minutes on a fast internet connection so we only schedule this overnight at present. It can't satisfy the near real-time requirement.
Replicating each customer database to an Azure SQL database and opening this up for the customer to access. Is this even viable, can customers subscribe to changes from Azure SQL and how real-time would the data be? What costs are involved?

Has anyone dealt with such a scenario and how did you manage to solve it? What other options are there for replicating databases?

Interesting problem to solve. I don't have a lot of familiarity with contained databases but after some quick research I understand their general purpose and the challenges you're facing.
The few ideas that come to mind are possibly looking into Log Shipping or Database Mirroring. You might be able to schedule more frequent data synchronizations with one of these methodologies than routinely sending a BACPAC file.
Finally, it looks like that Contained Databases do support Availability Groups but I'm not sure if that'll be of much help since AlwaysOn Availability Groups require a Windows Failover Cluster to be setup between the servers of the Availability Group, which I'm assuming is not conducive to your business scenario.

After some more brainstorming, research, and re-reading what you wrote, there may be a way to leverage a cloud provider like Azure, as you mentioned. This DBA.StackExchange answer gives a few options for moving data into an Azure instance. Additionally it reminded me of SSIS which you should be able to leverage to synchronize data from your instance within your DMZ to an external server (whether that's in Azure or on-prem).


query to find all dblink() Besides manually searching for all the dblink() used in triggers/functions/stored procedures is there a way to query for this information?

Example: There is a dblink inside of a trigger, can I select something from the postgres schema that would identify this?

I'm thinking this is a manual effort but asking just to make sure I'm not missing something.

NOTE: I have some logging where I am also looking for dblink()

I would draw a text backup and search it with vim or any tool of your choice.

As far as plpgsql functions are concerned (there is one behind every trigger), you can query the system catalog pg_proc:

SELECT *
FROM   pg_catalog.pg_proc
WHERE  prosrc ILIKE '%dblink%';


Database name not found but I can connect using IBM Data Studio. What happened? In IBM Data Studio, I can create a connection to a remote IBM DB2 LUW database named DBEMP and browse the schema and data.

Here is the steps to create the database connection:

From IBM Data Studio, In Database Adminstration Explorer, select: New -> New Connection to Database.
Select DB2 for Linux, UNIX, and Windows in the dialog.
In General properties:

Database: DBEMP
Host: 10.221.5.111
Port Number: 50001
User Name: user1
Password: *
Click OK.

But when I tried to connect to the remote database server (AIX v7) via telnet command:

telnet 10.221.5.111

Note: I login using root user.

And then I run this command:

/db2/V10.1/bin/db2 connect to DBEMP

I got this error:

  SQL1013N  The database alias name or database name "DBEMP" could not
  be found. SQLSTATE=42705

Then I also run this command:

/db2/V10.1/bin/db2 list database directory

The result list doesn't have database named "DBEMP".

So what happened anyway?

If there is no database name exist, why IBM Data Studio can connect to it?

Ahh....That has to do with profiles. Root was not set up by default to have DB2 libraries set up in its profile, so it doesn't recognize the DB2 executables. This is not to say root can't do what you are asking, you would just have to browse to the /instance/bin folder to find the executables you are after. Of course, then you have to worry about whether or not root has permission to connect....

But anyway, you might find it easier to sudo over to the id you want to use to connect (possibly even the instance) sudo - myid. For sure the instance will have DB2 set up in its profile. Your other ID you may have to set it up. Here is an answer I provided before where I showed someone how to set up DB2 for their profile.

I would recommend that you not give root DB2 access through its profile.


How to identify and remove the SQL table row which contains UTF 8 BOM characters I recently identified that some of the rows in our table contains UTF 8 BOM (Byte Order Marking) characters (ï»¿).. Is there a way to identify those rows and removing the BOM characters from those rows?

Balu, the answer you posted will truncate the value to 30 characters because the no length was specified for the CAST and CONVERT declarations of the varchar and nvarchar data types.

Consider using STUFF instead:

--nvarchar example
UPDATE dbo.YourTable
SET UnicodeColumn = STUFF(UnicodeColumn, 1, 1, '')
WHERE CAST(LEFT(UnicodeColumn, 1) AS binary(2)) IN(0xFFFE, 0xFEFF);

--varchar example
UPDATE dbo.YourTable
SET NonUnicodeColumn = STUFF(NonUnicodeColumn, 1, 2, '')
WHERE CAST(LEFT(NonUnicodeColumn, 2) AS binary(2)) IN(0xFFFE, 0xFEFF);


Combining Explain and Union Morning all,
I'm trying to quickly compare multiple queries that do similar tasks and I'm wondering if there's a quicker way of lining up the EXPLAIN outputs. To me, this example query should work:

EXPLAIN SELECT count(*) AS thetotal, date(datetimefield) AS theday, key_id FROM dbname.tablename WHERE datetimefield >= "2015-12-12 00:00:00" AND datetimefield <= "2015-12-12 23:59:59" GROUP BY key_id UNION
EXPLAIN SELECT count(*) AS thetotal, date(datetimefield) AS theday, key_id FROM dbname.tablename WHERE DATE(datetimefield) = "2015-12-12" GROUP BY key_id, theday;

and output something like the following:

but as you probably know it gives a "Syntax error, unexpected describe" message. Is there any reason why it shouldn't, or a way to achieve a similar result? At the moment I'm running EXPLAIN on the two similar queries, exporting them to csv, opening both in a spreadsheet editor and pasting them one row after the other.

Thanks in advance for any assistance you can give me.

As @jkavalik said in a comment, according to the MySQL docs UNION can only be used to combine the outputs of SELECTs, which EXPLAIN is not.
However, you can line up the raw outputs of each EXPLAIN in a text file using the MySQL commands in a shell. Run this once for each EXPLAIN statement:
mysql -u $USERNAME -p$PASSWORD -Bse "[EXPLAIN STATEMENT GOES HERE]" >> explains.csv
From the MySQL docs:

-B: Print results using tab as the column separator
-s: Silent mode. Produce less output.
-e statement: Execute the statement and quit.

This will give you a tab delimited row and >> will append to the file specified at the end, creating it if it is not already there.


transactional replication-snapshot agent failure ... can anyone help me out configured transactional replication ..source acting as publisher and distributor,target server acting as subscriber.. but I got error for snapshot agent at 93 % saying that in below screenshot

Looks like you timed out.
Add this to your snapshot agent job step:
-QueryTimeOut 3600
The default is 1800 seconds which is 30 minutes.  Try giving it 1 hour or more.
http://msdn.microsoft.com/en-us/library/ms146939.aspx
http://msdn.microsoft.com/en-us/library/ms151326.aspx


SQL Server: What is the difference when backing up via .bak, BACPAC or just copying .mdf files? What is the difference between a .bak file as created when backing up a database, vs just copying the .mdf files and restoring as mentioned over here, vs creating a BACPAC file.

In this case, the logs themselves are not important, and the .bak file is far larger than the actual database. (a 20GB backup on a 2GB database).

For this backup strategy (copying database files themselves) I would detach the database on a daily basis, copy the mdf files across to an external media and then reattach the database. There would be 3 backups of each database (with older backups deleted as newer backups are created):

db_daily_backup
db_weekly_backup
db_monthly_backup

I realise that MS SQL provides it's own backup mechanism, that is almost certainly superior to what I've outlined. However the bloat caused on even differential backups seems too much for what we need and the amount of space we have available.

the .bak file is far larger than the actual database.

The problem might be that you are just appending the backups to one single file. The default is to appended to the device. Take your backups with WITH COMPRESSION, INIT - compress your backup and all backup sets should be overwritten.

A .bacpac file is not a substitute for a full backup.. Also, using MDF/LDF files as backup is not a good habit.


SSRS email subscription with custom file name on the attachment I would like to set up an SSRS email subscription that attaches the report in PDF format to the email with a custom file name that changes daily.  I was hopeful that I could to this via a data-driven subscription, but that does not seem possible.  Does anyone have a handy work-around for this limitation?

UPDATE: @swasheck provided a decent work-around, and one that we currently use.  But I am hoping to remove the step of saving to a file-share.

This is the first way I could think of to make this happen:

Create a data-driven subscription for the report
Specify Windows File Share as the delivery method (this would imply that you had a file share location available)
Connect to a data source
Write a query to generate the name
Specify that name as the report name in the location of your file share
Have a process by which the end-users are emailed this report - perhaps a SQL Agent job?

As a matter of personal exploration, I wonder if it'd be possible to modify the SQL Agent job that the subscription creates such that you could add a step to send the file via email.


Cleanup of AG databases on secondaries nodes Consider a scenario where you have an AG running across 3 nodes and your backups are being backed up locally.
Each node have all the default jobs deployed and scheduled to run at same time. As the databases are part of the AG, backups are only being taken on the PRIMARY NODE.
After failover to a different node, the backups stored in the previous PRIMARY are not being deleted.

I thought that this could be happening since the default for the cleanup is AFTER_BACKUP and since no backups were happening on that node, the cleanup was not happening. I tried changing this to 'BEFORE_BACKUP' to no avail.

Am I missing anything obvious or is there anything I can do ?

Command running on every node

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

Thank you

The current design is that the stored procedure will decide if the database should be backed up. Only if the database should be backed up, it will go into the code that does the work (creates sub-directories, backup, verify, and cleanup).

It could make sense that if you are running with @CleanupMode = 'BEFORE_BACKUP', then it should delete backups, even if the database should not be backed up.

Could you create an issue for this on GitHub.

https://github.com/olahallengren/sql-server-maintenance-solution/issues


mongodump with readpref nearest I have a mongodb cluster where shards exist across multi geographically locations and each of those locations have replica members for each shard. I have setup our DR datacenter. Which also has a mongos for creating backups. I want to use the mongodump command to dump the database onto the file system of the mongos instance before sending it off to long term archive. The issue is that mongodump command on sharded clusters sets read pref to secondary so it may dump data from an active production secondary and not the one I have setup for this processes.

  When running mongodump against a mongos instance where the sharded
  cluster consists of replica sets, the read preference of the operation
  will prefer reads from secondary members of the set.
  http://docs.mongodb.org/manual/reference/program/mongodump/

I am looking for a way to change the readpref to nearest when running the mongodump command. Or if there is another command that will allow for the same sort of backup as mongodb. (http://docs.mongodb.org/manual/core/read-preference/)

The usual reason for sharding is that your workload has exceeded the resources of a single server, so the expectation is that you would not be running mongodump via mongos for a full sharded cluster backup. Backups are instead done by stopping the balancer and then backing up a config server as well as a mongod from each shard.

As at MongoDB 3.0, mongodump does not provide an option to specify read preferences or tags. If you have specific secondaries in your DR data centre for backup purposes, you can use these mongod nodes in your backup procedure.

Recommended backup strategies are included in the MongoDB manual:

Stop the balancer and backup all components of the sharded cluster with mongodump
Stop the balancer and backup all components of the sharded cluster with file system snapshots

Backups with filesystem snapshots are faster to complete (and to restore) because they include all data and indexes. Backups using mongodump export the data and index definitions, but indexes will have to be rebuilt as part of the mongorestore procedure.

FYI, there is an open feature request you can watch/upvote in the MongoDB issue tracker: TOOLS-630: Allow specifying readPreference (including tags).


What are the pitfalls when storing data retrieved from an api I can get some financial data from our vendor via their API. In this case API is too strong a word. Basically we can get only an array of orders for a given time range. What we paid them for the product, what they charges us for shipping, etc.

To report on and analyze this data I want to store it in a table. I have some concerns:

What if i load 1 Jan - 31 Jan today, and then next week they change some data in that range? With thousands of products sold daily combing over that to look for necessary updates could be intense.
How should I manage adding new data? I'm thinking to get new data mid-month for the previous month. This vendor's practices are not the best, I'm concerned they'll add some things one Monday that they forgot to enter the previous Friday. I think if I get today's data every day the likelihood of changed data in a past period rises.

Appreciate any advice from folks who've faced a similar issue. Maybe people do this all the time and I'm worried over nothing. Just would like to get some advice before I proceed.

Advice:

Normalize data stored.
Consider carefully and figure out your write model.  For orders, can you overwrite?  do you need to mark superceded and append?

The above are generic data entry pieces of advice, but regarding automated application to application interfaces, you have the issue of erroneous data entry.  To deal with this it is probably better to accept orders etc. but have the invoicing done by humans, who can cross check with the real world.  Also this keeps people in control of your books.  So I would add to that:

Separate automated entry from homan review and approval.


Login lacks connect endpoint permission in SQL Server - Error 18456, State: 149 I have a fresh database I've setup, which I can connect to and perform operations like normal via the SQL Server Management Studio. I have two accounts I created for my application to use, and both can log in via SSMS. My program is running java and using the 'mssql-jdbc-6.4.0.jre8.jar' driver, and when I attempt to connect I can see the following error on the database log:

  Error: 18456, Severity: 14, Sate: 149
  Login failed for user 'application_read_only_user'. Reason:
  Login-based server access validation failed with an infrastructure
  error. Login lacks connect endpoint permission. [CLIENT: 127.0.0.1]

I have looked at several sources trying to solve this issue. The official Microsoft documentation does not seem to list state 149, nor do any results I've found online.

I have not modified my endpoints from the original settings, so I only have the following items:

Dedicated Admin Connection
TSQL Local Machine
TSQL Named Pipes
TSQL Default TCP
TSQL Default VIA

I have tried setting the SQL Server services to run as Local System.

There are a lot of similar questions on this site, and stack overflow, but none of them have been able to help. They all either have a different state, or do not list a state.

Analysis

If you have a search for the Error 18456 you will eventually find the following blog post from Aaron Bertrand:

Troubleshooting Error 18456 (SQL Blog / Aaron)

Which lists the following for states 146...149:

  These states replace states 11 and 12 above, but only in SQL Server 2016 or better. The goal was to make the actual underlying issue easier for the sysadmin to diagnose between SQL auth and Windows auth logins, and between connect and endpoint permissions (all without giving any further info to the user trying to log in). For more details, see the latter part of this post.

The this post link in the above quote references the article "Why do I get the infrastructure error for login failures?" over on CSS SQL Server Engineers blog/site.

The article cites these two possible reasons as the cause for the "Login-based server access validation failed with an infrastructure error":

  Does the login have the SERVER class permission named CONNECT SQL for this server instance?
  Does the login have the ENDPOINT class permission named CONNECT for the specific endpoint on which the client application established the connection?

Because SQL Server 2016+ has new error messages for Level 146...149 errors, you have been presented with the specific error message Login lacks connect endpoint permission. (Level 149), which leads you to the second option from above.

Have a look at your current enpoint permissions with the following script:

SELECT * FROM sys.server_permissions AS sp2
    JOIN sys.server_principals AS sp
        ON sp2.grantee_principal_id = sp.principal_id
    LEFT OUTER JOIN sys.endpoints AS e
        on sp2.major_id = e.endpoint_id
WHERE sp2.permission_name = 'CONNECT'
AND sp2.class_desc = 'ENDPOINT'

You should have at least one entry for the combination of GRANT, public and TSQL Default TCP

Solution

Check the CONNECT permissions on the ENDPOINT for the SQL Server Login for TCP or simply grant the permission to the account:

GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO public;

(replace public with your <SQL Server Login> if you only want to assign that permission to a specific account)

To see a list of endpoints run:

select * from sys.endpoints

Further Reading / Reference List

Troubleshooting Error 18456 (SQL Blog / Aaron)
Why do I get the infrastructure error for login failures? (CSS SQL Server Engineers)
Endpoints Catalog Views (Transact-SQL) (Microsoft Docs)
GRANT Endpoint Permissions (Transact-SQL) (Microsoft Docs)


ListServ Database: Statistics Table Design Question I've got a little bit of a design question going on here.  I have my own ListServ implementation written in Python, that works with a Postfix+Dovecot system for handling mail, and a PostgreSQL database backend for most of the functionality for determining the following:

What lists exist on the listserv (the lists table in the diagram below)
Who has ever been seen on the listserv as a member of any list. (the members table in the diagram below)
Who is a current member of which lists, and: (the listserv_membership table in the diagram below, linking individual 'members' to the individual 'list' they are a member of by listserv ID and member ID)

Can they send to the list? (hidden field in the table)
Can they receive messages sent to the list? (hidden field in the table)

ListServ statistics - Message counts for each day, based off the current date's timestamp, for each individual list that has had activity. (the listserv_stats table below)

Currently, the structure of the DB looks like this (Note that I have blanked out all the non-relevant fields in tables I'm not going to be focusing on, and leaving the primary keys in place; the relevant table is marked with three white asterisks next to it, and this diagram is created with DataGrip):

Please don't call me out about diagonal arrows in the diagram - this one was done as a quick and dirty diagram, while I have a good ERD done by hand in Visio, which is unfortunately not available on the computer I'm on now

The specific table I'm going to ask about is the listserv_stats table here, not the rest of the design of the database.

Now, initially, I was only worried about tracking statistics over a day-to-day basis and 'all time' very rough daily average based on how many days the listserv has had activity.  I am now a lot more concerned about other statistics, such as monthly averages, month-to-date average, year-to-date average, and yearly averages, so I'm considering a redesign here to better accommodate such searches.

The current table CREATE statement is this:

CREATE TABLE listserv_stats
(
    lsid INTEGER NOT NULL,
    datestamp DATE DEFAULT now() NOT NULL,
    msg_count INTEGER NOT NULL,
    CONSTRAINT listserv_stats_lsid_date_pk PRIMARY KEY (lsid, datestamp),
    CONSTRAINT listserv_stats_lists_lsid_fk FOREIGN KEY (lsid) REFERENCES lists (lsid)
);
COMMENT ON COLUMN listserv_stats.lsid IS 'ListServ ID';
COMMENT ON COLUMN listserv_stats.datestamp IS 'DateStamp';
COMMENT ON COLUMN listserv_stats.msg_count IS 'Message Count';

What I'm thinking of doing is expanding the table to replace the datestamp column as follows.  This would allow me to better handle month-to-date, monthly historic, year-to-date, and yearly historic values, in my opinion, in my Python code (which can reconstruct the individual values into datestamps):

CREATE TABLE listserv_stats
(
    lsid INTEGER NOT NULL,
    year INTEGER NOT NULL,
    month INTEGER NOT NULL,
    day INTEGER NOT NULL,
    msg_count INTEGER NOT NULL,
    CONSTRAINT listserv_stats_lsid_date_pk PRIMARY KEY (lsid, year, month, day),
    CONSTRAINT listserv_stats_lists_lsid_fk FOREIGN KEY (lsid) REFERENCES lists (lsid)
);
COMMENT ON COLUMN listserv_stats.lsid IS 'ListServ ID';
COMMENT ON COLUMN listserv_stats.year IS 'Date: Year';
COMMENT ON COLUMN listserv_stats.month IS 'Date: Month';
COMMENT ON COLUMN listserv_stats.day IS 'Date: Day';
COMMENT ON COLUMN listserv_stats.msg_count IS 'Message Count';

From this, I can restructure the existing predefined routines in the DB to allow for my Python code to call "New Date Record" and "Update Date Record" functions by passing the entire date as values.

My question is, is this design change a good idea, or can I achieve everything with the original "datestamp" table I already have in place?  And should the original design be kept, I will have to figure out how to structure the routines to provide the data for those specific date ranges from the system (I'll post on SO for that, most likely).

So, in the chat room for the site, I've been told to leave my table structure alone, and to use views or specific queries to get out the data I need from just the timestamp.

Given this, I've decided to leave the table alone, and started looking into the date interaction functions with PostgreSQL (and MSSQL where another group I trust will like to use the functions) and have begun incorporating such specific checks into the predefined routines which I've designed for this system.

The only thing I left out from my question was that my code base in Python only interacts with the database via the predefined routines and functions I've written, so views are less necessary if I can get the database functions configured right to correctly process the specific date ranges on my radar.

Thanks to all who commented in chat about this to me directly, it's helped me work to restructure my initial design for the functions, and do a lot of stuff database side instead of Python-side.


How do I add a directory, or bulk, of users to Microsoft SQL Server permissions I have a front end program in Visual Basic that is using SQL Server as a "back end". The only way I can get permission for login to use the SQL server is by adding each name or computer individually and this will take forever to update and maintain.

My question is how do I do this at the group level? I found two similar questions that are essentially asking the same thing and they can be found at the following links:

https://stackoverflow.com/questions/5029014/how-to-add-active-directory-user-group-as-login-in-sql-server
Microsoft SQL Server v13 bulk add users from active directory

My problem is that these do not work for me. I get all the way to the end and when I click Ok nothing happens. Please reference the picture I attached to the post.

Do I need to add the directories in some other place before I can use the add button or am I missing something else?

The following picture is what I am trying to select.

SOLUTION UPDATE:

To finish the set up you must type the name of the directory into the check names box and click check names to confirm it. This seems intuitive but it is an easy step to miss. Please reference the pictures below, also my folder was named users, yours may be something else:

The first picture is before clicking the check names button

This picture is after clicking the check names button


User Permissions Messed Up following a Backup -> Restore Operation I had to move several SQL Server 2008 databases to our new db server so I backed them all up (to .bak files), copied these files over to the new box and restored them (all done using SQL Management Studio).

All went OK but now I find I can't login to any of the databases using the SQL Server account that continues to work on the old RDBMS. My windows-authenticated login still works fine incidentally.

I had this idea the users and permissions would all be seamlessly duplicated onto the new database server but it seems something has gone wrong somewhere. I would appreciate comments/suggests/offers of help ;-)

I see you already found a solution to your problem, one thing I noticed in your original question was that you still had access to the old server.

The following question on SO had a similar problem and includes links to a Microsoft article with a script to generate the user permissions.

https://stackoverflow.com/questions/461385/restoring-a-backup-to-a-different-server-user-permissions

(Resource listed for that question http://support.microsoft.com/kb/918992)

It sounds like changing the server setting from Windows authentication to Mixed mode authentication fixed your problem, but just in case it didn't completely resolve the problem I thought this might be useful.


Checking column stats in Oracle 12 In Oracle I can do:

SELECT *
 FROM ALL_TAB_STATS_HISTORY;

and figure out last time stats was run for various tables.
Similarly, I can get the stats information columns by doing:

SELECT * FROM ALL_TAB_COL_STATISTICS WHERE OWNER = 'CURAM' ORDER BY TABLE_NAME

What about for index tables?  Does Oracle also store stats for index tables? And how do I check the last time they were run?

ALL_TAB_COLUMNS

  LAST_ANALYZED

  Date on which this column was most recently analyzed

ALL_INDEXES

  LAST_ANALYZED

  Date on which this index was most recently analyzed


Create a view that summarize vehilce owned from two tables cars and bikes I have 3 tables owner, car and bikes where car and bike are dependent on owner.

owner(_name,_ssn)

cars(_ownername,_ownerSSN, model)

bikes(_ownername,_ownerSSN, model)

I want to create a view that shows how many cars and bike I own
in the following way

vehicle_owned (_name, _ssn , vehicleamount)

where vehicle amount is car and bikes summarized.

I've tried using count with join on the tables but nothing seems to work

As far as you should not provide information enough about your table schema, I've improvised this one:

create table _owner(_name text, _ssn text);
create table _cars(_ownername text, _owner_ssn text, _model text);
create table _bikes(_ownername text, _owner_ssn text, _model text);
insert into _owner values
  ('me' ,'1'),
  ('you','2');
insert into _cars values
  ('me', '1','Ferrari Modena 360'),
  ('me', '1','Porsche Macan'),
  ('you','2','peugeot 205');
insert into _bikes values
  ('me', '1','Specialized Tarmac'),
  ('me', '1','Pinarello Dogma'),
  ('you','2','Scott Silence');

You can use COUNT and GROUP BY to get your desired result:

select o._name, o._ssn,
       ((select count(*) from _bikes where _bikes._owner_ssn = o._ssn)
       +
       (select count(*) from _cars where _cars._owner_ssn = o._ssn)) as total_cars_and_bikes
from _owner o
;

+-------+------+----------------------+
| _name | _ssn | total_cars_and_bikes |
+-------+------+----------------------+
| me    | 1    | 4                    |
+-------+------+----------------------+
| you   | 2    | 2                    |
+-------+------+----------------------+

Check it here: http://rextester.com/UZMF16252


Efficient way to store geographical routes in MongoDB I need to store a taxi route into MongoDB, however I'm not sure what is the best way to do this.

Since the route is only needed for the taxi order, the first idea I had is to store it in the order, this would make the retrieval quite fast, since I won't need to join the order collection with the route collection. As well as that, the route can be stored in a simplified way for example:

{
   order_id: 1,
   ...
   route: [
        [ 1567767312, 58.542454, 110.15454, 45 ], //timestamp, lat, long, speed
        ...
        [ 1567768312, 59.448488, 10.484684, 20 ]

   ]
}

If say the measurement is done every 2 seconds, an average 15 min ride would be 450 points, which is not a lot.

However, I read that MongoDB does not like when documents are very different in sizes and since the route can be different all the time, this might cause issues as the order number grows.

The other obvious approach was to have a separate collection to store each reading a separate document:

{
    timestamp: "2019-09-06T10:49:38+00:00",
    coordinates: [ 58.45464, 128.45465 ],
    order_number: 1
}

With indexing, this should not be much slower in terms of fetching or writing than the method above. However, this will occupy way more space and the collection might grow really fast.

Just as clarifications. The route is only used for displaying it on the map when somebody opens the order, no need for geo queries.

The right way to store a taxiroute in a MongoDB is to use a GeoJSON object and a LINESTRING

{ type: "LineString", coordinates: [ [ 40, 5 ], [ 41, 6 ] ] }

Using this method you can make use of Spatial Queries like $geointersects and $geonear, and Spatial Indexes. All of that said, you're probably better off migrating to a database that focuses more on this kind of stuff, like PostgreSQL.


log ship from older version to newer one As per below thread from SQL Server: Can I log ship from SQL Server 2014 --> 2016?

  Always log ship UP as it's a basic supported upgrade process. you can't log ship DOWN. The question you might be concerned with is how do you roll back and not lose data if you need to go back to 2014 as you can't restore 2016 DBs to 2014

We need to set this up for our environment to log ship from older version to newer one, i.e. from 2008R2 to 2016 and getting following error:

Error: This backup cannot be restored using WITH STANDBY because a database
upgrade is needed. Reissue the RESTORE without WITH STANDBY.

Would you please advise whether this can be done at all? If so, what we are supposed to do to get it working?

As the error message mentions, you can't use WITH STANDBY when restoring to a newer version of SQL Server.  The database upgrade process has to run in that case, and it can't do that in standby mode because the database is essentially read-only.  This is documented in RESTORE Statements - Arguments (Transact-SQL):

  STANDBY is also not allowed when a database upgrade is necessary.

You can use NORECOVERY rather than STANDBY for the restores instead.  You won't be able to use the new server as a readable copy of the database, which might be a dealbreaker.  But it supports the scenario of migrating to the newer server (if that's what your end goal is).

I would double check your configuration. Specifically, check that you selected "No recovery mode" for the "Database state when restoring backups" option on the Restore tab for the target (secondary) database.  It's step 17 in Configure Log Shipping (SQL Server) - Using SQL Server Management Studio

  On the Restore tab, under Database state when restoring backups, choose the No recovery mode or Standby mode option.

You can review the settings by right-clicking the database, choosing properties, and then looking at the "transaction log shipping" tab.

Clarifying a few questions and statements from the comments:

  It seems that at the time of database open on 2016, it actually applies/restores transaction log files. Please confirm!"

No, that's not how it works. The transaction logs are applied during the RESTORE LOG operation. So the deleted source transaction logs aren't a problem. If you're getting that error about missing transaction logs, then it sounds like some of the restores failed, or they were restored out of order, etc.

  However, when we check the timestamp of data and log files of the configured databases on 2016, it shows the timestamp when this was configured

You can't use Windows/NTFS timestamps as a valid indication of the SQL Server data files being used, and thus it can't be used as evidence that the transaction logs are not being applied when you restore them.


SQL 2012: Executed generated DB script in SQL 2008 R2 I generated a script (using the wizard from SSMS 2012) to produce a SQL 2008 R2 compatible database script. I ran this script in a SSMS 2008 R2 instance and i got a bunch (30) of these possibly "warning" errors:

  The module 'spMoney_Increase' depends on the missing object
  'dbo.spMoney_Decrease'. The module will still be created; however, it
  cannot run successfully until the object exists.

Do i need to be concerned about these errors? Note that the errors did not break the overall execution and i know trying to put 2012 DB into a 2008 instance is not recommended but you know how client request goes...

-------- Description of Process -------

In SSMS 2012 - Server A

Right clicked on MY_DB > Tasks > Generate Scripts
In the Set Scripting Options > Advanced > I chose the below setup

Then Ok > Next > Finish

In SSMS 2008 R2 - Server B
I created folder locations on the C drive that mimicked Server A so i didn't need to change the DIR paths in the script:

  CREATE DATABASE [MY_DB] ON  PRIMARY ( NAME = N'MY_DB', FILENAME =
  N'C:\DBS\MY_DB.mdf' , SIZE = 16384KB , MAXSIZE = UNLIMITED, FILEGROWTH
  = 1024KB )

  LOG ON  ( NAME = N'MY_DB_log', FILENAME = N'C:\DBS\MY_DB_log.ldf' ,
  SIZE = 1280KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO

Executed the script and got the "warning" error above.

This is a warning message that comes up due to a missing dependency (it also means that the SQL dependency tree may not be correctly set, and so you won't have correct entries in sys.sql_expression_dependencies which can be useful down the road). This is just a warning however, and so long as the dependent object is created you will have no issues with running your code.

You can also repush the items you get warnings on, and that will fix the dependency list, but again, not critical.


Security implications disabling promotion of distributed transaction for linked server I've a a linked server and I need to run the following statement:

INSERT INTO...EXEC linkedserver.sp @parameter

Both servers SQL Server 2008R2 SP1. Once I run it I get this error:

  Msg 7391, Level 16, State 2, Line 6 The operation could not be
  performed because OLE DB provider "SQLNCLI10" for linked server
  "MY.LINKED.SERVER" was unable to begin a distributed
  transaction.

After searching for the error I saw that lot recommend running:

EXEC master.dbo.sp_serveroption
     @server = N'[mylinkedserver]',
     @optname = N'remote proc transaction promotion',
     @optvalue = N'false'

Is there any security implications on this action I should be aware of?

Other option is to use advanced properties of the linked server object on main server:

When you do INSERT INTO...EXEC locally, sql server will honor it, but when you use Linked server, sql server has to initiate a local transaction and promote / esclate to a distrubuted transaction.

From --> How to create an autonomous transaction in SQL Server 2008

  'remote proc transaction promotion' is a new option on SQL Server 2008, which allows you to control whether or not you want to enlist remote stored procedure call in a distributed transaction. When this option is off  (FALSE), the local transaction will not be promoted to distributed transaction. This is how we are able to separate outer and inner transactions in a "autonomous transaction" fashion.

As far as I am aware, there is no security implication.

Also, refer to sp_serveroption for more details.


Call Back In Union All Within the With-As-Table Itself ALTER PROCEDURE [dbo].[A]
AS
BEGIN
WITH SplitSting1
AS(
Select id, NameString, emp
  ,LEFT(NameString,CHARINDEX(',',NameString)-1) As Head
  ,RIGHT(NameString,LEN(NameString)-CHARINDEX(',',NameString))  AS Remainder
From [dbo].[Table_1]
WHERE NameString IS NOT NULL AND CHARINDEX(',',NameString)>0
Union ALL
Select id, NameString, emp
    ,LEFT(Remainder,CHARINDEX(',',Remainder)-1)
    ,RIGHT(Remainder,LEN(Remainder)-CHARINDEX(',',Remainder))
From SplitSting1
WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)>0
)
Select * from SplitSting1 order by id option (maxrecursion 0);
END

The first image is the table I query.
The second image is the output I run my stored procedure.

Can anyone assist to explain how the code is running to generate the output seem like split the NameString/Remainder Column into Head Column. How is the algorithm work?

This is a Recursive Common Table Expression or Recursive CTE.

References:
Official documentation for SQL Server
(they are supported by other databases too)
A tutorial and another one, both from a very quick Google search so you might find better with more effort now you know the right terms to search for.


SQL Server 2000: Can't DROP VIEW? I have SQL Server 2000. I want to clean it by dropping obsolete and unused objects. One of the views causes me an error. I try to give more information but I still have no clue what the cause is.

Background: I found that one view can be drop and I know for sure no one uses it. And it links to detach (unused) database. I decided to drop it. Before I drop I check the view to make sure.

CREATE View vw_obsolete_view
AS
SELECT [very long],
,
,
...
FROM [unused_database].dbo.vw_obsolete_view

DROP VIEW vw_obsolete_view

Now, View was dropped. It seem to be OK. However, I got a call from user that Application (ADP) got an error message. It states that view vw_obsolete_view is missing.

Since this view is not my coding and the my Application is call only my objects. So I have no idea why this cause an error. It took me an hour to figure out. I check all dependent objects. Nothing links to this view at all. I checked all ADP VBA code. Nothing relates to this view also.

I can't find what wrong with this. But I know that the problem is from dropping view because I was the only one who changes the database. So I make a new view with the same name.

CREATE View vw_obsolete_view
AS
SELECT 1 AS total

The error at the end user is disappear. End User is happy but I still can't drop view. I still have no clue.

Sidenote: I can't upgrade SQL Server right now.

Run SQL Profiler to identify who, where etc. You have various columns with hostname, ntusername etc that will allow you to see usage.

For example: is if just one client or user who notices? Are they running a different ADP?

The view call can be embedded in some Form properties too as the RecordSource for that form, or as the source for some control. It may not be in VBA. You'd have to write some VBA to iterate through the forms, controls and reports testing assorted properties.


For SQL Server, how to fix simultaneous parallel table updates? I have to update all records (add Guids) on two (indexed) empty columns of 150 tables, each table with around 50k records (using a script to create 40k updates at once in c# and post it to the server) and exactly 4 existing columns.

On my local machine (16GB RAM, 500GB Samsung 850, SQL Server 2014, core i5) when I try to run 10 tables in parallel it takes a total of 13 minutes, while if I run 5 the process finishes in mere 1.7 minutes.

I do understand that something is busy on the disk level, but I need some help in how to quantify this huge difference in timings.

Is there a exact SQL Server DB view that I can check this discrepancy? Is there an exact way to figure out for a given hardware how many table updates can I run in parallel?? (the real test server has more RAM and 10k rpm disks).

Can anyone point to something that I can improve on the SQL Server to improve the timings for the running 10 tables in parallel?

I already tried increasing the Auto Growth size to 100MB from 10MB which improves the Disk Queue length (from around 5 to 0.1) but it does not actually decrease the total time that much.

I have asked the exact same question on stackoverflow, but not getting any helpful answers so far, so some or any insight/help would be immensely helpful. :)

Given the code in your answer, you would most likely improve performance by doing the following two changes:

Start the query batch with BEGIN TRAN and end the batch with COMMIT TRAN:
Decrease the number of updates per batch to less than 5000 to avoid lock escalation (which generally occurs at 5000 locks). Try 4500.

Doing those two things should decrease the massive amount of tran log writes and lock / unlock operations that you are currently generating by doing individual DML statements.

Example:

conn.Open();
using (SqlCommand cmd = new SqlCommand(
      @"BEGIN TRAN;
        UPDATE [TestTable] SET Column5 = 'some unique value' WHERE ID = 1;
        UPDATE [TestTable] SET Column5 = 'some unique value' WHERE ID = 2;
        ...
        UPDATE [TestTable] SET Column5 = 'some unique value' WHERE ID = 4500;
        COMMIT TRAN;
        ", conn));

UPDATE

The Question is a bit sparse on the details. The example code is only shown in an answer.

One area of confusion is that the description mentions updating two columns, yet the example code only shows a single column being updated. My answer above was based on the code, hence it only shows a single column. If there really are two columns to update, then both columns should be updated in the same UPDATE statement:

conn.Open();
using (SqlCommand cmd = new SqlCommand(
      @"BEGIN TRAN;
        UPDATE [TestTable]
        SET    Column5 = 'some unique value',
               ColumnN = 'some unique value'
        WHERE  ID = 1;
        UPDATE [TestTable]
               SET Column5 = 'some unique value',
               SET ColumnN = 'some unique value'
        WHERE  ID = 2;
        ...
        UPDATE [TestTable]
               SET Column5 = 'some unique value',
               SET ColumnN = 'some unique value'
        WHERE  ID = 4500;
        COMMIT TRAN;
        ", conn));

Another issue that is unclear is where is the "unique" data coming from? The Question mentions that the unique values are GUIDs. Are these being generated in the app layer? Are they coming from another data source that the app layer knows about and the database does not? This is important because, depending on the answers to these questions, it might make sense to ask:

Can the GUIDs be generated in SQL Server instead?
If yes to #1, then is there any reason to generate this code from app code instead of doing a simple batch loop in T-SQL?

If "yes" to #1 but the code, for whatever reason, needs to be generated in .NET, then you can use NEWID() and generate UPDATE statements that work on ranges of rows, in which case you do not need the BEGIN TRAN / 'COMMIT` since each statement can handle all 4500 rows in one shot:

conn.Open();
using (SqlCommand cmd = new SqlCommand(
      @"UPDATE [TestTable]
        SET    Column5 = NEWID(),
               ColumnN = NEWID()
        WHERE  ID BETWEEN 1 and 4500;
        ", conn));

If "yes" to #1 and there is no real reason for these UPDATEs to be generated in .NET, then you can just do the following:

DECLARE @BatchSize INT = 4500, -- this could be an input param for a stored procedure
        @RowsAffected INT = 1, -- needed to enter loop
        @StartingID INT = 1; -- initial value

WHILE (@RowsAffected > 0)
BEGIN
  UPDATE TOP (@BatchSize) tbl
  SET    tbl.Column5 = NEWID(),
         tbl.ColumnN = NEWID()
  FROM   [TestTable] tbl
  WHERE  tbl.ID BETWEEN @StartingID AND (@StartingID + @BatchSize - 1);

  SET @RowsAffected = @@ROWCOUNT;
  SET @StartingID += @BatchSize;
END;

The code above only works if the ID values are not sparse, or at least if the values do not have gaps larger than @BatchSize, such that there is at least 1 row updated in each iteration. This code also assumes that the ID field is the Clustered Index. These assumptions seem reasonable given the provided example code.

However, if the ID values do have large gaps, or if the ID field is not the Clustered Index, then you can just test for rows that do not already have a value:

DECLARE @BatchSize INT = 4500, -- this could be an input param for a stored procedure
        @RowsAffected INT = 1; -- needed to enter loop

WHILE (@RowsAffected > 0)
BEGIN
  UPDATE TOP (@BatchSize) tbl
  SET    tbl.Column5 = NEWID(),
         tbl.ColumnN = NEWID()
  FROM   [TestTable] tbl
  WHERE  tbl.Col1 IS NULL;

  SET @RowsAffected = @@ROWCOUNT;
END;

BUT, if "no" to #1 and the values are coming from .NET for a good reason, such as the unique values per each ID already exist in another source, then you can still speed this up (beyond my initial suggestion) by supplying a derived table:

conn.Open();
using (SqlCommand cmd = new SqlCommand(
      @"BEGIN TRAN;

        UPDATE tbl
        SET    tbl.Column5 = tmp.Col1,
               tbl.ColumnN = tmp.Col2
        FROM   [TestTable] tbl
        INNER JOIN (VALUES
          (1, 'some unique value A', 'some unique value B'),
          (2, 'some unique value C', 'some unique value D'),
          ...
          (1000, 'some unique value N1', 'some unique value N2')
                   ) tmp (ID, Col1, Col2)
                ON tmp.ID = tbl.ID;

        UPDATE tbl
        SET    tbl.Column5 = tmp.Col1,
               tbl.ColumnN = tmp.Col2
        FROM   [TestTable] tbl
        INNER JOIN (VALUES
          (1001, 'some unique value A2', 'some unique value B2'),
          (1002, 'some unique value C2', 'some unique value D2'),
          ...
          (2000, 'some unique value N3', 'some unique value N4')
                   ) tmp (ID, Col1, Col2)
                ON tmp.ID = tbl.ID;

        COMMIT TRAN;
        ", conn));

I believe the limit on the number of rows that can be joined via VALUES is 1000, so I grouped two sets together in an explicit transaction. You could test with up to 4 sets of these UPDATEs to do 4000 per transaction and keep below the limit of 5000 locks.


How to limit work_mem on PostgreSQL As an analyst I love having the ability to turn up the work_mem on occasion when  a big sort or index build is in the offing.

As a DBA, the idea of random users being able to request an arbitrarily large amount of system resources at run-time is a little scary. I confirmed that a basically permission-less fresh-created user, could request as much work_mem as they wished. It would presumably take only a few greedy users at once to starve out other users, which is scary.

Is there any way to limit the work_mem a user can request?

According to the PostgreSQL Wiki this sort of thing isn't possible.

  PostgreSQL has no facilities to limit what resources a particular
  user, query, or database consumes, or correspondingly to set
  priorities such that one user/query/database gets more resources than
  others. It's necessary to use operating system facilities to achieve
  what limited prioritization is possible.

As suggested in the text above, you could try using operating system facilities to achieve limits such as that. For example, ulimit can be used to create process-specific resource limits.


How to find out ALL the users that can connect to SSAS? I have an user called mycompany\aives that is not listed in the list of administrators in the SSAS, not belong to any of the AD (active directory) groups listed there,

however,

he can connect to the cubes in this server (called SASBI01).

if you see , however, the second picture, mycompany\aives is listed as one of the administrators on the box (SASBI01)

Where does it say that the administrators on the box can (or not) connect to the cubes?

How can I find out (preferably via script), a list of users that can connect to the cubes and their permissions?

Check the server property BuiltinAdminsAreServerAdmins. Till SQL Server 2014 it defaults to true. Not sure if it will be  changed in 2016 (hopefully).

Link: https://msdn.microsoft.com/en-us/library/ms174561(v=sql.120).aspx


Database model with Users, Resources, Permissions I am fairly new to the DB design. I need to handle what a user can do on a specific resource.

One of the main queries I need to drive is:

  Get all the cars a user has write permission to.

I think if I only had the one resource it would be easier, but I will have plenty of resources and so I am trying to design something via a ResourceType model, so that I don't have to create a lot tables as the number of resources grow.

I saw this question: Discretionary Access Control (DAC) model

For what I can tell that is the closest thing to what I am trying to accomplish because it differentiates between roles approach and discretionary. I do not have roles and won't have roles for now. When a user creates a resource (like Car, Location, etc...) the server assigns the "owner" permission for that user. The owner can in turn grant permissions to other users, like read, write...

I have created the following tables:

    CREATE TABLE ResourceType
    (
       Id (PK)
       Name
       resourceId
       ...
    )

    CREATE TABLE Car
    (
       Id (PK)
       model
       resourceTypeId (FK to car ResourceType)
       ...
    )

    CREATE TABLE User
    (
       Id (PK)
       Name
       ...
    )

    CREATE TABLE Permission
    (
       Id (PK)
       canRead
       canWrite
       ...
    )

then I created a junction table:

    CREATE TABLE ResourceTypeToUserPermission
    (
       Id (PK)
       permissionId (FK to Permission)
       resourceTypeId (FK to ResourceType)
       userId (FK to User)
    )

I was hoping to use that junction table to get what I needed with inner joins and where clauses on userId = x...

I am obviously not doing this right and I am wondering how I can modify things correctly so that for any resource I will have in the DB  I will be able to get all resources of a given type with a certain type of permission for a given userId.

Any help would be much appreciated.

-- User USR exists.
--
user {USR}
  PK {USR}

-- Resource type RTY exists.
--
res_typ {RTY}
     PK {RTY}

-- Resource RSC, of resource type RTY,
-- owned by user USR, is named RSC_NAME.
--
resource {RSC, RTY, USR, RSC_NAME}
      PK {RSC}

      SK {RSC, RTY}

      FK1 {RTY} REFERENCES res_typ
      FK2 {USR} REFERENCES user

-- User USR was granted permission PER to
-- resource RSC by owner of that resource.
--
res_perm {USR, RSC, PER}
      PK {USR, RSC, PER}

FK1 {USR} REFERENCES user
FK2 {RSC} REFERENCES resource

CHECK (PER in ('read', 'write'))

Now the resource subtypes. The proper way to implement constraints for subtypes would be to use assertions (CREATE ASSERTION), but it is still not available in major DBs. I will use FKs instead, and as all other substitute methods it is not perfect. People argue a lot, here and on the SO, what is better. I would encourage you to check other methods too.

-- Car RSC, resource type 'C', ... car specific.
--
car {RSC, RTY, ... }
 PK {RSC}

 FK {RSC, RTY} REFERENCES resource

 CHECK (RTY = 'C')

-- Location RSC, resource type 'L', ... location specific.
--
location {RSC, RTY, ... }
      PK {RSC}

      FK {RSC, RTY} REFERENCES resource

      CHECK (RTY = 'L')

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key


ORA-01722 Error when running stored procedure I am trying to run a stored procedure however when I run it with an a valid Number it gives me an ORA-01722 error. I checked to make sure that the columns in the where clause where the parameter is used is of NUMBER type. I checked to make sure that the record exists. Note: When I take out c.charges-c.payment the query runs fine.

create procedure patient_InfoNew(vpatientID NUMBER) IS
x VARCHAR2(300);
BEGIN
 select a.firstname || a.lastname || ' ' ||  a.ssn || ' ' || a.streetname || ' ' || a.phonenumber || ' ' || b.servicedate || ' ' ||  b.servicetype || ' ' || c.charges - c.payment || ' ' || e.name || ' ' || e.insnumber into x
 from patient a, patientaccount b, patient_info c, patientinsurance d, insurance e
 where a.id = vpatientID  AND e.id = d.insuranceid AND a.id = b.id AND a.id = c.id AND a.id = d.id
AND b.servicedate =
   (Select MAX(servicedate)
    from patientaccount
    where id = vpatientID);

DBMS_OUTPUT.PUT_LINE(x);

EXCEPTION
    WHEN     VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('Error '||SQLCODE||
SUBSTR(SQLERRM,1,80));
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Divide by zero');
    WHEN OTHERS THEN
        IF SQL%NOTFOUND THEN
               DBMS_OUTPUT.PUT_LINE('No such record was found');
        END IF;
        DBMS_OUTPUT.PUT_LINE('Error '||SQLCODE ||
                        SUBSTR(SQLERRM,1,80));

END;

This is the error I get below:

No such record was found
Error -1722ORA-01722: invalid number
PL/SQL procedure successfully completed.

You are getting that error because you have a row in patient_info where either charges or payment (or both) are NULL. To just make the error go away, you could replace

' ' || c.charges - c.payment || ' '

with

' ' || (c.charges - c.payment) || ' '

but I suppose you would want to handle the case where charges or payment are NULL in some meaningful way, something like

' ' || (nvl(c.charges, 0) - nvl(c.payment, 0)) || ' '

otherwise when any of charges, payment are null you'll get an empty string instead of a number (since any number - NULL is NULL and NULL is converted to an empty string when concatenated with other strings) and you probably don't want that.


getting insert queries from pgAdmin 4 I am using pgAdmin 4 and am looking to download query results as INSERT statements rather than as a CSV (the only option I can find). I am used to doing this with MySQL Workbench, but maybe I have been spoiled over there?

Is it possible in pgAdmin 4 to download query results as INSERT statements?

If you need this for moving data among databases, as you indicate in your comments, I'd suggest using psql and its \copy command.

This will definitely work, as it writes the files onto the local machine (the one the client is running on, eg. your laptop), and if you can connect using pgAdmin 4, you can also connect using psql.

The process would look like

psql -h dbhost -U whatever -d yourdb
psql> \copy table_name TO ~/table_name.csv WITH (FORMAT csv)
psql> \q

psql -h other-dbhost -U whatever -d your_other_db
psql> \copy other_table FROM ~/table_name.csv WITH (FORMAT csv)

The file location syntax assumes you are on Linux.


How to `vacuumlo` an RDS PostgreSQL database? As you may have noticed, as a fully managed data base as a service product, AWS Relational Data base Service (RDS) restricts the execution of userland commands.

I was staging a production database, and got my pg_largeobject table bloated to 40% of the whole persistency virtual device capacity.

How can I run vacuumlo (beautifully well explained in other DBA.SE question here) on a RDS instance running PostgreSQL database?

Find here (in the GitHub mirror of the official PostgreSQL repo) sources of vacuumlo for the PostgreSQL data base version I was using at that very moment.

You can imagine the rest: I just mimic what the program is performing, also simply described here.

1. Temporary table construction.

Prepare the temporary table of object references or OIDs.

1.1. Temporary lob table as copy of full lob table.

=> SET search_path = pg_catalog;
[...]
=> CREATE TABLE vacuum_lo_removeme AS \
        SELECT oid AS lo FROM pg_largeobject_metadata;
[...]
=> ANALYZE vacuum_lo_removeme;
[...]
=> _

1.2. Limit temporary lob table.

Perform the query that returns you all your data base columns typed OID:

=> SELECT
     s.nspname, c.relname, a.attname FROM pg_class c, pg_attribute a
     , pg_namespace s, pg_type t
   WHERE
     a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = c.oid
     AND a.atttypid = t.oid AND c.relnamespace = s.oid
     AND t.typname in ('oid', 'lo') AND c.relkind in ('r','m')
     AND s.nspname !~ '^pg_';

Next you have to execute this query for all results fetched by the earlier query (note ${VARIABLE} is something you should substitute yourself according to your mileage) in order to remove from the temporary table all OIDs of objects actually in use:

=> DELETE FROM vacuum_lo_removeme WHERE \
        lo IN (SELECT ${column} FROM ${SCHEMA}.${TABLE});

In my case it was only two tables totallying five columns, and actually both tables empty, so naturally the five DELETE queries did not do a thing. If you have a bigger OID enabled subschema you might need to automate this somehow.

2. Large objects unlinking.

Finally the program declares a cursor that iterates the remaining lo cells of the temporary table, purging them with a lo_unlink function call.

2.A. Do not do it this way.

I should have automated that with a PLPGSQL stored procedure, but since I suck at that kind of tasks, I just issued this one liner:

$ echo 'SELECT COUNT(*) FROM vacuum_lo_removeme;' | \
        $MY_AUTOMATABLE_PSQL_MILEAGE
  count
---------
 1117233
(1 row)

Then this other that iterates selecting the first orphan OID in the temporary table of orphan OIDs then unlinking it and removing it from the table:

$ for i in {1..1117000}; do \
        export oid=$(echo 'SELECT * FROM vacuum_lo_removeme LIMIT 1' | \
        $MY_AUTOMATABLE_PSQL_MILEAGE | grep -v 'lo\|\-\-\-\-\|row\|^$' | \
        sed s/\ //g) && \
        echo "SELECT lo_unlink($oid); \
              DELETE FROM vacuum_lo_removeme WHERE lo = $oid" | \
              $MY_AUTOMATABLE_PSQL_MILEAGE; \
        done
 lo_unlink
-----------
         1
(1 row)

DELETE 1
 lo_unlink
-----------
         1
(1 row)

DELETE 1
 lo_unlink
-----------
         1
(1 row)

DELETE 1
 lo_unlink
-----------
         1
(1 row)

DELETE 1
ERROR:  must be owner of large object 18448
DELETE 1
ERROR:  must be owner of large object 18449
DELETE 1
ERROR:  must be owner of large object 18450
DELETE 1
ERROR:  must be owner of large object 18451
[...]
 lo_unlink
-----------
         1
(1 row)

DELETE 1
[...]

I knew it was suboptimized as hell, but I let it slowly removing those orphan records. When not being DBA at all those one liners can be easier to forge than working some meaningful idiomatic PLPGSQL.

But this was too slow to leave it like this.

2.B. Do this better than 2.A (though not a silver bullet yet).

You will be able to speed up large object unlinking with something simple such as:

=> CREATE OR REPLACE FUNCTION unlink_orphan_los() RETURNS VOID AS $$
DECLARE
  iterator integer := 0;
  largeoid OID;
  myportal CURSOR FOR SELECT lo FROM vacuum_lo_removeme;
BEGIN
  OPEN myportal;
  LOOP
    FETCH myportal INTO largeoid;
    EXIT WHEN NOT FOUND;
    PERFORM lo_unlink(largeoid);
    DELETE FROM vacuum_lo_removeme WHERE lo = largeoid;
    iterator := iterator + 1;
    RAISE NOTICE '(%) removed lo %?', iterator, largeoid;
    IF iterator = 100 THEN EXIT; END IF;
  END LOOP;
END;$$LANGUAGE plpgsql;

NOTE it is not required to unlink large objects 100 at a time, not even a particular number x of them at a time, but unlinking 100 at a time is the safest base applicable to all AWS instance sizes' default memory configuration. If you use an excessively large number for this, you risk function failure because of insufficiently assigned memory; how you can do will depend on the amount of objects to unlink and their size, on instance type and size, and on degree of manual further configuration applied.

Which is somewhat easy to forge for a non DBA person, then calling it with something like

$ for i in {0..$WHATEVER}; do echo 'SELECT unlink_orphan_los()' | \
        $YOUR_AUTOMATABLE_PSQL_MILEAGE

Where ${WHATEVER} is a constructed constant depending on the temporary large objects' table size and the number of locks per transaction your configuration is allowing (I am using RDS defaults but iterating from bash I guess I don't even have to get to know which is the largest number of lo_unlinks the RDBMS is allowing with current max_locks_per_transaction.

3. VACUUM large object tables.

Hinted by this thread in the postgres mailing list I understood I should VACUUM pg_largeobject after unlinking the objects.

I am not sure which of these are the minimum required set, or the proper time for their execution, but some of them might be of help, and none should cause any damage: I ran VACUUM ANALYZE VERBOSE pg_largeobject_metadata; VACUUM ANALYZE VERBOSE pg_largeobject; VACUUM FULL ANALYZE VERBOSE pg_largeobject_metadata; VACUUM FULL ANALYZE VERBOSE pg_largeobject; several times while the micro instance was unlinking the objects (took a really large time, alas), firstly when about 1/4 objects already got unlinked, and some little storage was given back to OS, secondly when about 1/3 objects already got unlinked, and some another little storage was given back to OS, thirdly when about 3/5 objects already got unlinked the instance underwent a massive storage giveback to OS:

That massive storage giveback was what I was looking for. After running the query for largest tables found at the official home page, with less than 3/4 total objects unlinked, the objects table got shrunk to less than 3GiB, far from the initial and even more bloated 20GiB.

NOTE automating fast VACUUM ANALYZE iteration on tables has the same long term effects (of cleaning the table from using excess disk storage) as executing a single VACUUM FULL, but without acquiring an exclusive lock.


Database Tuning Advisor error: ...% of consumed workload has syntax errors Hi I've generated a trace file using SQL Server Profiler and save that trace as a table in SQL server DB. The trace file workload was generated by executing a query directly from SQL Serve Management Studio. So there were no syntax errors in the query.

Now I'm using SQL Server Database Tuning Advisor to see analysis recommendations. To achieve it specified the table name as a workload source and selected all interested databases. when database Tuning Advisor completes the analysis the "Process" tab appears and displays the following message for the "Consuming Workload" in the "Action" column.

TITLE: DTAEngine

"50% of consumed workload has syntax errors. Check tuning log for more information."

I'm noticing three issues described below:

1) Before starting analysis I selected the "Save Session Log" checkbox. But the Tuning Log on the Process tab is empty;

2) I have only two types of reports generated after the analysis completes: the "Index Detail Report (current)" and the "Index Detail Report (recommended);

3) No Recommendation were generated.

The reason for the issues above was that I selected the "Showplan Statistics Profile" option under the "Performance" category on the Event Selection tab in SQL Server Profiler. Once I unchecked it and generated a new trace file workload, all the issues listed above had been resolved.


FDW, replication and CREATE SERVER - will it work? We have two servers (master, slave) and we want to use the FDW. The two databases to be connected with the FDW are both on the master (and the slave of course).  When we are to CREATE SERVER we would run something like:

CREATE SERVER server_db2 FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'master', dbname 'db2', port '5432');

The question is this:  Does the slave create the server_db2 and connects to the master's IP address?  What will happen if I break the relationship and the slave is promoted to master? I suppose it will be looking for the master to connect right?

Would connecting via 127.0.0.1 and setting the method to something other than trust be a safe option?

In general the behavior of the postgres_fdw is unclear to me in a replicated environment.

You cannot really use postgres_fdw to connect from a replica server to the master, because you cannot create the foreign server on the replica, it is read only.

You'd have to run the create on the master, then rely on it replicating to the read-replica.

Even if you were to do this, it would make no sense. There is no point connecting from a replica to the master, because the replica has the same data as the master. There is no purpose to doing this.


How to prepare a date and double for a mysql replace statement? I have a MySql table with three columns:

CU VARCHAR(3)
DA DATETIME
VA DOUBLE

I need to execute several REPLACE into mytable (CU, DA, VA) values("ABC", mydate, myval) statements from Java. mydate is a Date and myval is a double.

My question is, assuming I have a connection to my database, how do I format and include mydate and myval properly in this string statement?

Here is the output of the NOW() function

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2012-04-24 22:07:47 |
+---------------------+
1 row in set (0.03 sec)

mysql>

This is the format to enter mydate. For a double, just append .0 to any integer. No need for numbers to to have quotes:

REPLACE into mytable (CU, DA, VA) values('ABC', '2012-04-24 22:07:47', 23.0);


Database model: doubly linked list I am not sure if I get this right. I'm designing a database model for use in an application, and I'm stuck at a doubly linked list (model first; there is no code yet).

There's an order. An order has an owner who gives it to someone, an acceptor. The acceptor can decide to divide the order into suborders. He will then become the owner of these suborders and give them to other acceptors. An order can have infinite Suborders, which can also have infinite Suborders and so on. Typical hierarchy. But the (sub-) orders have to be in order (no pun intended), and it would be nice if they could refer to the predecessor and the successor. Predecessor to the first suborder would either be null oder the parent order, successor to the last order either null or the parent order.

What is the best practice in such a case? I feel that the following is a bit... too much clutter (suborders would be a collection).

I'm not sure I understand your question right, but:

If I understand right, this is a tree that has children ordered. Also the creation of children is monotone, that is no children are deleted.
In these cases, in relational databases I used to have:

Id
parent's Id
child order (first child is 1, second 2, etc.)
child count (count of my children, only needed if you want "last child")
acceptor, ...

Key's are ( Id ) & ( parent's ID ; child order ).

In this case:

Previous sibling is ( parent's Id ; child order-1 )
Next sibling is ( parent's Id ; child order+1 )
First child is ( Id ; 1 )
Last child is ( Id ; child count )
Parent id ( parent's Id )

I hope I helped.


What is a source table, and a target table? I've seen alot of talk about source tables, and target tables on the internet, but no definitions of what these things are...from what I can infer it seems to mean something about copying data from an old table with an old schema into a new table with a new schema...is this what it means?

Essentially the source table is the place where the data is coming from and the target table is the place where the data is going to or the table that an action is taking place on. They do not have to be in different schema's or have a different structure to each other and in fact they could actually be the same table.


In regards to GS1 barcode - what size DB field are used? In regards to GS1 barcode - what size DB field is used or is the entire GS1 typically not stored in database, but rather processed in the application layer and stored in the database as a collection of the component parts?

The length of the GS1 in its entirety is the subject of this question

As the maximum number of characters in a single symbol is 48 and the Technical Spec for aggregators (http://www.gs1.org/sites/default/files/docs/gs1-source/TSD_v1_1_Technical_Implementation_Guide_for_Aggregators.pdf) indicates the file can come in as UTF-8. UTF-16, or UTF-32, the safest bet would be

nvarchar(48)


Can't Delete Database My problem is that I want to delete a DB from our production server. It is a test DB that needed to be there for some reason, so the mdf and ldf are on LUNs that are insecure and oftentime lost connection to the server, not like our production LUNs that are OK.

The problem is that I can't delete the database. It throws:

  El sistema operativo ha devuelto el error 1117(failed to retrieve text for this error. Reason: 15105) en SQL Server durante leer en el desplazamiento 0x00000687a10000 del archivo 'T:\XXX_XXXXX_xx.mdf'

We are using SQL Server 2008 [10.0.5500.0].

It won't let me go into DB settings or read from tables. I can't manually delete the files either. I think a server restart should help. Today is Tuesday and we got a schechuled restart on Saturday 04.

Have any of you encontered this issue?

That just means it can't find that file at the moment. Either wait for when the drive to be available, or, as Martin suggested, try setting the database offline: ALTER DATABASE FOO SET OFFLINE

If that works, you should be able to follow it up with a DROP: DROP DATABASE FOO.

Then delete the files at your leisure.


Executions/min and CPI ms/sec inside Activity Monitor is too high We have executions/min is too high, around 20,000 to 30,000 for a query. Does this means the query is executing 20,000 per minute? I don't think we have that much load on server that a single query runs 20,000 per minute.
Also, I am always confused with CPU ms/sec. Does this means total CPU used per second? If yes how come we have lot of queries with 1000+ CPU ms/sec as we have limited CPU on server.

One possible way to run a query very many times is to use a scalar user defined function as that will execute once per row in the query.
If you have multiple CPU cores each one can support 1000ms / second which is why total CPU appear strange and go over what seems possible


Transaction Log Backup Cleaning If you back up your transaction log every hour through a maintenance plan, why does your log file continue to grow? I would think the system would delete or shrink the log after the backup. Is this something I want to do?

Because the size of it is not big enough to handle the size of the transactions every hour.

or

Something is preventing the log from getting cleared after the log backup completes. Check the log_reuse_wait_desc column in master.sys.databases to see why it isn't clearing out.

Consider backing up the transaction logs more frequently than hourly. I recommend every 1-5 minutes.