Hiding all databases in SSMS that a user does not have access to without making them db_owner I am trying to make it so a test user can only see the dbs that he has access to. Currently, the user can only see the one db that he has access to, but he is now the db_owner of that database. I don't feel that a test user should be the db_owner as this gives him access to everything in the database. From the articles that I found online, this seems to be the only way to make it so the user can only see the single db. When I use DENY VIEW ANY DATABASE TO [user] he does not see any db within the server, including the db I have given explicit view privileges to. Is there a way to allow him to only view the single db without giving him db_owner privileges?

The article I am referencing is here.

First of all if you DENY VIEW ANY DATABASE TO [user] it will not "see" even the database where it's a memeber of db_owner role, except for sysadmin, only database owner will "see" the database owned by it.

Database owner is different from just membership in db_owner role, it's a login that has authorization on that database (grant authorization on database::myDB to myLogin), so only one login at a time can own the database, and the corresponding user has all the permissions in that database and can "see" it in OE even when VIEW ANY DATABASE is denied to the corresponding login.

Saying that there is an alternative solution to your problem, it's SQL Server 2012 Contained Database Feature

Starting with SQL Server 20012 you can enable Contained Database Feature at the instance level and then make your databases contained.

This means that there will be a database, not server to authenticate your users, and all these users will "see" only the databases where they were created. They will not "see" nothing at the server level.

If one day you'll need to migrate your databases to another server, you'll not need to transfer server logins to another server, your contained database already contains all its users and they are not "orphaned" because they don't have corresponding logins at all.


Snapshot not picking up new articles in SQL Server 2012 transaction replication when added through script I am trying to add new articles to an existing publication in SQL Server 2012.

I am adding the article to the publication with the syntax

--add the table into the correct publication
sp_addarticle @publication='<publicationname>', @article='CardBookingType_tbl', @source_object='CardBookingType_tbl'

Previously on SQL Server 2008 I used to use the following syntax to subscribe to 1 table only and then run the snapshot. This no longers work on SQL 2012 as you have to subscribe to all articles.

--add the table into the subscription
EXEC sp_addsubscription
        @publication = '<publicationname>',
        @subscriber = '<Servername>',
        @destination_db = '<dbname>'

GO

In SQL Server 2012, if I add the table via the GUI and then run the snapshot agent it picks up the new table fine. But if I add via the script it appears in the gui but the snapshot reports no new items were detected.

Seems like I am missing a piece of code to flag the subscribers as missing a article?

Maybe Brandon's idea here will help:
How to add an article to existing replication

Summary of his suggestions to fix:

execute sp_refreshsubscriptions for pull subscriptions
or in push subscriptions use the sp_addsubscription as you mentioned (worked
for me)

I wrote some code to help identify the "orphaned articles on subscriber's":

--Run from distributor
SELECT  pub.data_source AS Publisher ,
        a.Publisher_db ,
        p.Publication ,
        sub.data_source AS Subscriber ,
        s.Subscriber_db ,
        a.Article
FROM   distribution.dbo.MSarticles as a
LEFT JOIN distribution.dbo.MSpublications AS p ON a.publisher_id = p.publisher_id AND a.publication_id = p.publication_id
JOIN sys.servers AS pub ON p.publisher_id = pub.server_id
LEFT JOIN distribution.dbo.MSsubscriptions AS s ON a.publisher_id = s.publisher_id AND a.publication_id = s.publication_id AND a.article_id = s.article_id
LEFT JOIN sys.servers AS sub ON s.subscriber_id = sub.server_id
WHERE s.subscriber_db IS NULL --Leave off for great documentation, but this will show the "orphaned" subscribers effected by this issue.


How to understand "object-relational"? I can't understand why the document say "PostgreSQL is an object-relational database management system"?Can somebody tell me the reason, or give me some article about this question.

Thanks a lot.

My experience:

I'm a beginner of database(and also beginner of postgresql. :D）.
I implemented a service that provide several restful APIs for app. The logic of APIs all implement in plpgsql function, and app access them through nginx.

I question whether it should be described as "object-relational", really.

That statement refers to PostgreSQL's inheritance features. They are useful, but limited. You can't have foreign keys pointing to a parent table and all children, nor can you have a unique constraint that constrains a parent table and all children. Triggers must be defined on each child, not just the parent. Etc.

Another issue is that Pg's inheritance features don't play well with ORMs. Most ORMs want to map inheritance as decomposition by reference, so that if "A" extends "B", then you'll have a table "A" with all A's fields, and a table "B" with the extra fields and a foreign key back to "A".

PostgreSQL's inheritance doesn't have that foreign key back to A. Instead, each row of "B" has all fields of both "A" and "B". When you query "A", you see the common columns from "B" as well. This confuses ORMs, becaue it means there's no relationship they can see between "A" and "B", so they don't tend to cope with mapping their understanding of inheritance onto PostgreSQL's understanding of inheritance.

That tends to limit use of PostgreSQL inheritance to areas where you're not using an ORM, but areas where inheritance is useful is just where you're most likely to use an ORM.

The inheritance features are quite handy for certain kinds of designs, you just have to accept the limitations. They're a lot faster to use than the typical ORM-style approach of separate tables with foreign keys, for one thing.

Your situation, where you're coding in PL/PgSQL, is one where the inheritance features might prove very useful to you.


Unique constraint across two columns I need to add a constraint with two columns that says if any given value is present in one of the columns, then:
   1) It cannot be duplicated in the same column.
   2) It cannot be duplicated in the other column either.

The constraint we are looking to make is with PrimaryEmail & SecondaryEmail.

This would be invalid:

UserId    PrimaryEmail       SecondaryEmail
231       joe@yahoo.com      Null
424       smo@gmail.com      joey@yahoo.com

because "joe@yahoo.com" is present in the first column and therefore it cannot be present in the second column regardless of what row it's in.

Is it possible to define this type of constraint in SQL Server 2008?

We started by defining a table just for emails, but we've since reverted from that model in favor of two hard columns for many reasons including: query speed, query complexity, and the probability of a user using multiple email accounts actively decreases in order of magnitude after one.

This defines a traditional two column constraint but its on a per row basis between the two columns and doesn't give us what we are after:

CREATE UNIQUE NONCLUSTERED INDEX idx_UniqueEmail_notnull
ON UserProfile (PrimaryEmail, SecondaryEmail)
WHERE PrimaryEmail IS NOT NULL and SecondaryEmail IS NOT NULL;

This isn't possible with the proposed table structure declaratively. You would need triggers to enforce this.

A unique index on both columns, together with a pair of check constraints with scalar UDFs, gets quite close however.

CREATE TABLE UserProfile
  (
     Id             INT PRIMARY KEY,
     PrimaryEmail   VARCHAR(100),
     SecondaryEmail VARCHAR(100)
  )

CREATE UNIQUE INDEX IX1
  ON UserProfile(PrimaryEmail)

CREATE UNIQUE INDEX IX2
  ON UserProfile(SecondaryEmail)

go

CREATE FUNCTION dbo.EmailInUseAsPrimary (@Email VARCHAR(100))
RETURNS BIT
AS
  BEGIN
      RETURN
        (SELECT COUNT(*)
         FROM   UserProfile WITH (READCOMMITTEDLOCK)
         WHERE  PrimaryEmail = @Email)
  END;

go

CREATE FUNCTION dbo.EmailInUseAsSecondary (@Email VARCHAR(100))
RETURNS BIT
AS
  BEGIN
      RETURN
        (SELECT COUNT(*)
         FROM   UserProfile WITH (READCOMMITTEDLOCK)
         WHERE  SecondaryEmail = @Email)
  END;

GO

ALTER TABLE UserProfile
  ADD CHECK ( dbo.EmailInUseAsPrimary(SecondaryEmail) = 0),
      CHECK ( dbo.EmailInUseAsSecondary(PrimaryEmail) = 0)

The reason for READCOMMITTEDLOCK is to avoid problems with snapshot isolation.

One problem with the approach above is that because the constraints are evaluated RBAR it can fail some transactions that ought to succeed.

For the example data

INSERT INTO UserProfile
VALUES (1, 'abc@abc.com', 'def@def.com'),
       (2, 'ghi@ghi.com', 'jkl@jkl.com')

This statement fails

UPDATE UserProfile
SET PrimaryEmail = CASE Id WHEN 1 THEN 'jkl@jkl.com' WHEN 2 THEN 'def@def.com' END,
    SecondaryEmail = CASE Id WHEN 1 THEN 'ghi@ghi.com' WHEN 2 THEN 'abc@abc.com' END

even though at the end of the transaction the constraints would have been met. But maybe it is sufficiently unlikely that you will be performing this kind of update (swapping email addresses between both type and person) that this can be ignored.


Connecting dimension tables in a multi-fact star schema database warehouse? I am new to data warehouse modelling so please bear with me.
In database warehouse modelling, the star schema is typically a fact table with multiple dimensions connected directly to it. However, what if there are two or more fact tables in the schema and some of the dimensions are related? Please see below as an example:

Is it allow to draw a line to relate the dimension tables in star schema database warehouse modelling? For example, assume dim_a is hotel_rooms and dim_d is customers, can I draw the line between the two table in the star schema since a hotel room has many customers staying?
In all the examples I searched online, the star schema is always connected to dimension tables and these tables are not drawn with a line to connect it.

Having multiple fact tables in a model is pretty common and is sometimes referred as a Fact Constellation. The shared dimensions are referred as Conformed Dimensions.
As @bbaird said in the comment there shouldn't be a direct one-to-many relationship between dim_a (hotel_rooms) and dim_d (customers) as this relationship should be modeled through a fact table (booking, reservation, stay, ...).
Relationships between dimensions are mainly used to drill-down from one level to another (for instance over a geographical area or as you modeled it for the time dimension). A model with dimensions linked like that is referred as a Snowflake. It is often also possible to flatten these hierarchical levels into a single dimension which might add complexity to maintain but provide better performance on some RDBMS.


Automation of backups of Large Number of Database In a multi-tenant web application where a database is set up for each tenant, the database can be backed up and restored manually through a fairly conventional approach.

I am considering this type of architecture for an application that could have 5,000-10,000 tenants, and running this type of process for this many tenant databases will be cumbersome.

If I was to implement such a process for a large number of tenant databases:

Is there likely to be an excessive performance overhead for such a large number of distinct backups?
How would such a large number of backup processes be automated?
How could one keep track of such a large number of backups and ensure that restore operations got the correct backup and database to restore to?
How could one implement controls to monitor the backup status and integrity of such a large number of databases?

At my previous job I did exactly this. We were in the 500 tenant range, but once you get above 10 or 20 the automation part makes the actual number much less important.

Assuming that your hardware can handle backing up 10,000 databases in a sufficient time, and that all 10,000 aren't having significant enough activity to overwhelm your server with or without backups, the next step is to automate the process in general. The nice thing about this model is that if 10,000 databases end up overwhelming your server, it is much easier to move half of them to a second server and point your applications at the right instance depending on the tenant, than it is to try and rip half of the data out of a single database. Do you have some that are in FULL and some in SIMPLE, or are they all in the same recovery model?

We had priorities. So a table to hold the tenant information (in my implementation there were many more columns, but this should be the basics):

CREATE TABLE dbo.TenantBackups
(
  TenantID      INT PRIMARY KEY,
  RecoveryModel VARCHAR(6),
  Priority      TINYINT
);

And then a history table (again, more to it in the actual system, but for the purposes of the question):

CREATE TABLE dbo.TenantBackupHistory
(
  TenantID   INT,          -- FOREIGN KEY,
  BackupType TINYINT,      -- lookup for FULL, LOG, RESTORE_TEST
  StartTime  DATETIME,
  EndTime    DATETIME,
  Status     NVARCHAR(1000) -- almost always NULL but held true "exceptions"
);

So now the job(s) could run through the tenants, ordered by priority, and perform backups. We actually had separate jobs for priority 1s vs. priority 2-5.

We tested priority 1 tenants nightly by restoring their full backups on a backup server. We also picked 20 random databases from the other priorities each night to test their restores as well. There were alerts etc. configured of course so that failures were immediately known, and if the exception was outside of our normal retry/error handling the Status column would contain information.

We also used SQL Sentry to chain jobs so that we didn't have to worry about scheduling a buffer and trying to predict when the backup jobs would finish. For priority 1 databases we didn't bother to wait for the backups to finish as a whole; we had a background thread that would wait for each individual full backup to complete and then would immediately pick it up off the queue and start the copy/restore test processes. For priority 1 tenants we also used the restored copy as a reporting offload - for any reports that were interested in data before today (which was most reports), they could run off the copy, because the only thing it was missing was today.

For full recovery tenants (not all customers required point-in-time), the log backup jobs could easily check the history table to see if a backup of any kind was already in progress. But of course error handling and retry logic was implemented all around.

I know these are very generic descriptions but I hope they point you in the right direction. Let me know if you'd like me to expand on any details.


SQL 2014 SP2 CU1 - AlwaysOn Cluster trouble synching to one node We have recently move CPQ to SQL2014 AlwaysON cluster
we are seeing some serious problems with the synchronization from The Primary to one of the secondary servers (there are 2 secondary servers)
It seems to be fine for a while but then we start getting messages that Data Movement suspended on one or more databases

We have discovered by running this script we can see the amount of  locks that are being held and what session-id is running holding them

SELECT request_session_id, COUNT (*) num_locks
FROM sys.dm_tran_locks
GROUP BY request_session_id
ORDER BY count (*) DESC;
GO

What we have found is that the session-id is holding a large number of locks (98535 at the moment)

We then found you can run this script

SELECT
[request_session_id] AS [SPID],
[resource_type] AS [LockType],
DB_NAME ([resource_database_id]) AS [DB],
[resource_description] AS [Resource],
[resource_associated_entity_id] AS [ResourceID],
[request_mode] AS [Mode],
[request_status] AS [Status]
FROM sys.dm_tran_locks
WHERE [request_session_id] < 0;
GO

and it will show you what SPID -3 is holding  and all the locks are on one database (500+GB) with locktype of KEY or PAGE etc

Its not long after all the other database in same Availability Group and other AGs start failing.

I don't know how to release  the locks except to stop and remove database from AG but that takes downtime which is a luxury.  Any help or guidance is appreciated

SPID -3 is a deferred transaction or BOL Definition.

  A deferred transaction is one where the transaction could not be recovered fully because of an I/O error or a gross file system problem like an unavailable file.

Check your disk subsystem and run a CHECKDB in your database.

To kill the negative spid, use

SELECT DISTINCT 'KILL ''' + CONVERT(VARCHAR(100),request_owner_guid) + ''';'
FROM   sys.dm_tran_locks
WHERE  request_session_id = -3
AND    database_id = DB_ID('Your_DBName');


Questions on database table and index partitioning in SQL Server I have developed Transact-SQL script to partition the table. To apply the partition, I am creating one non-clustered index for my partition key column and associating it to partition scheme. I have few questions:

My table could have multiple indexes, and when we create index, duplicate data is created. When I partition using a non-clustered index on only one column (i.e. Partition Key Column), will other index data be partitioned too?
How is the partitioning applied in the case of multiple indexes?
Do we need to partition table data and indexes separately?
What are the best practices for partitioning a table in SQL Server?

When you partition an index all its data will be split according your partitions.
The partitioning is applied to each particular index independently others.
From the previous point - yes, if you need a partitioned table with partitioned indexes you have to apply the partitioning to the table and each of the indexes.
Too broad to be answered. You could start with SQL Server Table Partitioning: Resources


Connection pool Error I am getting the below error from my .net application.

Source: xxx.Services.xxx.xxx.xxxx.xxxx.xxxxRequest
    ---
    System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.

I have seen several posts regarding this connection pool error.I have gone through most of it and i understand why it is happening and the possible solutions from on the application side like closing the connections opened by application.
These is what i understood :

Opened connections are not closed, called connection leaking.
Morethan 100 concurrent connections are being used. Increase the max
    pool size in the connection string if we have more than 100
    simultaneous users.
Slow queries or open transactions blocks new
    connections. When the connections are being held open by slow query
    execution/open transactions and instead of being reusing connections
    the new connection are open and eventually reaches the connection
    pool maximum.

How can i monitor this from the database side.I saw some suggestions like use sp_who or sp_who2 to see the existing sessions.When i execute sp_who2 when i see these errors,i see lots of active sessions,but how can i see the connection pool information and the connections which comes under the pool.
Questions:

Is there any way to track the connections opened and relate it to a
connection pool created from database side.

I have noticed that when the issue happens,the queries which normally runs faster ,goes slow.

Is it expected?Because i am suspecting whether the slow queries are
causing the connection pool errors.

Is there any way to track the connections opened and relate it to a connection pool created from database side.

Set the Application Name in your config and you can monitor from the server side in sys.dm_exec_sessions.  You'll get a separate Connection Pool for each Connection String, so if you set the Application Name in the connection strings you can tell what pool each connection belongs to.

  the queries which normally runs faster ,goes slow. Is it expected?

Yes.  Before you start seeing timeouts getting connections, you will have waits of up to 30 seconds waiting for a pooled connection to become available.  So it may be a delay in opening or re-opening a connection, rather than a slow query.

Or it could be an indirect correlation.  Often the code path that leaks connections only happens when you get some other Exception.   For instance blocking or resource exhaustion on the database is causing timeout Exceptions, which, due not using using blocks is causing the leaked connections.  But that's just speculation.

BTW 99.99% of the time the cause is: "Opened connections are not closed, called connection leaking."


Oracle server connection reset by peer socket write error Full disclosure, I'm not a database admin, but if anyone is able to help me, it would be you guys.

Our Oracle 11g database server stopped working, I'm not aware of anything that could have caused it, it was out of the blue. When I try to connect via SQL Developer I get either 'connection reset by peer socket write error' or 'Input/Output error: connection reset.

I've tried restarting it, checked whether the Windows Services are running and went through every item in the configuration that I could have found, but nothing helped. It seems that the listener is unable to start (I've tried adding a new listener with identical setting and also got the socket write error)

Since I've done no changes to the configuration, I'm perplexed what the issue could be. Does anyone has any ideas to what could be the cause of this issue? And if not, can some one post a link to something like the SQL Server's "Repair Installation" which I didn't find?

Any help is appreciated.

EDIT: I was asked for the following:

Output for the lsnrctl status:

    LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 03-JUL-2012 13:30
:53

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12571: TNS:packet writer failure
 TNS-12560: TNS:protocol adapter error
  TNS-00530: Protocol adapter error
   32-bit Windows Error: 54: Unknown error

And according to the lsnrctl start, the listener is already running

The windows error 54 is associated with a port busy or problems with the network communication, try editing the listener.ora file and change the port, then restart the listener service (or if you're able to restart the machine, do so).

It looks like that, for some reason, the port 1521 hungs and there's no longer a TNS Listener service listening there.

P.D. if you didn't configured a TNS_ADMIN folder, the listener.ora file must be in the %ORACLE_HOME%\network\admin folder, where the %ORACLE_HOME% is where the product was installed

The Windows error reference is in: http://msdn.microsoft.com/en-us/library/windows/desktop/ms681382(v=vs.85).aspx


SQL Server : Included columns in an Index: is there something like "too many"? We have a table in one of our databases with approximately following structure:

Field1 int primary key
Field2 int foreign key A
Field3 int foreign key B
Field4 int foreign key C
Field5 ... Field70 diverse data types, for the "payload"

There are indices on the foreign key columns, albeit only with key columns, without any included columns. The index analysis routine keeps suggesting new indices with the same key columns for which there are already indices there, but with many included columns - in one case even all the Field5 to Field70. I am a bit hesitant to create an index with so many included columns - it feels like duplicating the table itself.

Are there some rule-of-thumb limits for the count of included columns in an index, or should I just go forward and replace the existing index (key column only) with an index with the same key column and so many included columns?

Thanks in advance.

Suggested indexes always tend to include as many columns as needed by queries accessing the data, in order to have covering indexes and eliminate the need for lookups.

Whether this is a good thing or not, only you can tell. It highly depends on the shape of your workload. Some queries will highly benefit from covering indexes, some others will barely improve.

Having columns included in your nonclustered indexes will also mean that whenever you change the data in those columns, the changes will have to be written to the index as well. Broad indexes have a cost.

It is also possible that some of those columns are better placed inside the index key rather than included. Some queries might benefit from ordered data in the index.

The only way to know for sure is benchmarking:

Capture a production workload and sync it with a backup
Restore the backup to your test server
Run the workload against the test server and analyze performance indicators and execution times
Apply the changes you identified as sensible to your indexes
Replay the workload
Compare performance
Rinse and repeat

There is no other way to know for sure.


How do I group items separated by a 0 in a column using mysql? I have a table with the following structure:

id,center,shelf_id,occupied,remaining
1,East,1_1_1,1,3
2,East,1_1_2,1,1
3,East,1_1_3,1,0
4,East,1_1_4,1,0
5,East,1_1_5,1,2
6,East,1_2_1,1,1
7,East,1_2_2,1,0
8,East,1_2_3,0,4
9,East,1_2_4,0,4
10,East,1_2_5,0,4
11,East,1_3_1,0,4
12,East,1_3_2,0,4
13,East,1_3_3,0,4
14,East,1_3_4,0,0

I would like to aggregate by remaining where remaining is not 0.

The above example should return a table with a commas delimited shelf_id and remaining with a total of remaining that looks like:

shelf_id
1_1_1, 1_1_2
remaining
3,1
total
4

shelf_id
1_1_5, 1_2_1
remaining
2,1
total
3

shelf_id
1_2_3, 1_2_4, 1_2_5, 1_3_1, 1_3_2, 1_3_3
remaining
4,4,4,4,4,4
total
24

I am using MariaDB 10.2.31.

WITH
cte1 AS
(
SELECT shelf_id, remaining, SUM(remaining = 0) OVER (ORDER BY id) group_num
FROM test
)
SELECT GROUP_CONCAT(shelf_id) shelf_id,
       GROUP_CONCAT(remaining) remaining,
       SUM(remaining) total
FROM cte1
WHERE remaining != 0
GROUP BY group_num;

fiddle


Find Multiple Finished Product code via 1 Raw Material Code On the SQL server in work we have multiple part codes which start with either "M" or "P" codes, once these M or P codes are used throughout the manufacturing process these are then assigned an "A" code of which there can be multiple "A" codes which are finally sold as "R" codes.

Therefore I'm looking to enter a specific M-code for example such as M1655 which links directly to the R-code without having to manually search by each A-code.

For example, the initial search of M1655 results in A02748.  I then need to search for A02748 which then leads on to A02749 then on to R3700, R3856 and R3857

I'm just not sure how I'd go about sorting that, there's a small selection of data below

Resource    PM Name   PM Description
M1655       A02748    HONEY RST SALMON FROZEN
A02748      A02749    HONEY RST SALMON DEFROSTED
A02749      R3770     LOCHMUIR SALMON PASTA
A02749      R3856     CAFE HONEY RST SALMON NICOISE
A02749      R3857     BFY HOT SALMON & SUPERGREEN

Current SQL query details to get this info is:

SELECT pdResc.CalcValResource AS [Consumed Resource], pdPM.PM AS [PM Name], pdBalConsRescDt.Shrinkage,
        pdPM.Description AS [PM Description], fdBasResc.CatCodesCode2 AS [Resource BUSINESS], pdPM.PMSite AS [Site Name],
        fdBasResc_1.CatCodesCode2 AS [PM BUSINESS]
    FROM ERP_STD_CALE.dbo.fdBasResc AS fdBasResc,
        ERP_STD_CALE.dbo.fdBasResc AS fdBasResc_1,
        ERP_STD_CALE.dbo.pdBalConsRescDt AS pdBalConsRescDt,
        ERP_STD_CALE.dbo.pdPM AS pdPM,
        ERP_STD_CALE.dbo.pdResc AS pdResc,
        ERP_STD_CALE.dbo.pdStage AS pdStage
    WHERE pdPM.ObjectID = pdStage.ParentObjectID
        AND pdResc.ParentObjectID = pdStage.ObjectID
        AND pdResc.RescDtlObjectID = pdBalConsRescDt.ObjectID
        AND pdResc.CalcValRescOID = fdBasResc.ObjectID
        AND pdPM.PPRObjectID = fdBasResc_1.ObjectID
        AND (
              ( pdStage.ParentClassID = 10353 )
              AND ( pdStage.CollectionID = 1 )
              AND ( pdResc.ParentClassID = 10354 )
              AND ( pdResc.CollectionID = 1 )
              AND ( pdPM.PMInstType = 0 )
              AND ( pdPM.PMSite = '1000' )
              AND ( pdPM.AvailforSched = 1 )
            )
    ORDER BY fdBasResc.CatCodesCode2, pdResc.CalcValResource;

Does something like this help? It's a recursive CTE.

DECLARE @crap TABLE (Resource VARCHAR(6), PMName VARCHAR(6), PMDawn VARCHAR(50))
INSERT @crap ( Resource, PMName, PMDawn )
SELECT *
FROM (
VALUES('M1655' ,      'A02748',    'HONEY RST SALMON FROZEN'),
      ('A02748',      'A02749',    'HONEY RST SALMON DEFROSTED'),
      ('A02749',      'R3770' ,    'LOCHMUIR SALMON PASTA'),
      ('A02749',      'R3856' ,    'CAFE HONEY RST SALMON NICOISE'),
      ('A02749',      'R3857' ,    'BFY HOT SALMON & SUPERGREEN')
) x (Resource, PMName, PMDawn);

WITH r_cte AS (
        SELECT  c.Resource, c.PMName, 0 AS PMLevel, CONVERT(VARCHAR(900), c.Resource) AS FullPath
        FROM @crap AS c
        WHERE c.Resource = 'M1655'

        UNION ALL

        SELECT c2.Resource, c2.PMName, PMLevel + 1, CONVERT(VARCHAR(900), CONCAT(r.FullPath, '/', c2.Resource))
        FROM r_cte r
        JOIN @crap AS c2
        ON r.PMName = c2.Resource
)
SELECT DISTINCT c.PMName, c.PMDawn
FROM r_cte r
JOIN @crap AS c
ON r.Resource = c.Resource
WHERE c.PMName LIKE 'R%'
OPTION(MAXRECURSION 0);


Do I need to convert varchar(32) and varcha(64) to update from MySQL 5.7 to 8.0 I am upgrading a database from 5.7 to 8.0 in MySQL and ran across this:
How to easily convert utf8 tables to utf8mb4 in MySQL 5.5
I understand converting the schema and tables to utf8mb4 and utf8mb4_unicode_ci but regarding the changing of columns to varchar(191) I was lost.  I tried to follow their disclaimer and read the notes but I cannot see if this is actually needed to upgrade...or why it is even listed as a step.
Also, are there any issues that could arise from converting the tables to utf8mb4 from utf8 and just skipping converting the columns to a different type?

No you can change the character set, because you don't come to the limit that is explained in the article.

The InnoDB storage engine has a maximum index length of 767 bytes, so for utf8 or utf8mb4 columns, you can index a maximum of 255 or 191 character.

So if your utf8 column varchar has a size of more than 191 characters, you will get an error when converting, because tf8mb4 needs 4 bytes per characters and that will exceed the limit.
But as you only have 32 and 64 characters you are wide blow the limit of 191


What is the impact of building Postgres with --disable-thread-safety option? I am a newbie in the database field. Recently I have been trying to install Postgres 9.3.4 on AIX 6.1. When I was building the code with the configure command, I ran into this error:

checking thread safety of required library functions... no
configure: error: thread test program failed
This platform is not thread-safe.  Check the file 'config.log' or compile
and run src/test/thread/thread_test for the exact reason.
Use --disable-thread-safety to disable thread safety.

I am only able to compile successfully after using the -disable-thread-safety option. As such, I would like to know what is the impact of using this option towards the database reliability. What does 'client libraries' mean in terms of using the database with this option?

I would also like to know how to compile the thread_test.c and run it manually. The manual says:
        - run "configure"
        - compile the main source tree
        - compile and run this program

But I haven't been successful in compiling it.

*Edit:
Here are the steps that I took to compile thread_test.c:
1. I first ran configure with the --disable-thread-safety option to get the source tree.
2. Then from the build directory, I ran "gmake" to compile main source tree.
3. When's that's done, I moved into the src/test/thread folder and tried to run "gmake".
I got this error:

`gmake: *** No rule to make target `thread_test.o', needed by `thread_test'.  Stop.

If I run "make", I get many errors like this:

"../../../src/Makefile.global", line 45: make: Dependency line needs colon or double colon operator.

"../../../src/Makefile.global", line 48: make: Dependency line needs colon or double colon operator.

"../../../src/Makefile.global", line 52: make: Dependency line needs colon or double colon operator.

"../../../src/Makefile.global", line 72: make: Dependency line needs colon or double colon operator.

"../../../src/Makefile.global", line 84: make: Dependency line needs colon or double colon operator.

"../../../src/Makefile.global", line 85: make: Dependency line needs colon or double colon operator.

"../../../src/Makefile.global", line 87: make: Dependency line needs colon or double colon operator.

"../../../src/Makefile.global", line 88: make: Dependency line needs colon or double colon operator.

"../../../src/Makefile.global", line 91: make: Dependency line needs colon or double colon operator.

make: Equal sign not found in macro substitution.

I might be doing something wrong here...Can someone be kind enough to point out my mistakes in compiling the src/test/thread/thread_test.c file?

Thanks for all your patience with me. :)

Disabling thread safety will mean that libpq can't be safely used with multi-threaded applications unless the app is very careful to only interact with it using a single thread.

This isn't a concern for the great majority of apps.


Txt format from Bank I'm working on a service that allows users to store data from the bank, but banks give me their account statements in different formats, one of them is the following

  68520157286     0106201809510981PAY TRAN SPEI                          -000000000088640000001321436306018973NON1 CLA24 REF 0000000

I would like to know if someone knows the name of this format and how I can treat it to store it in SQL Server, I receive this format in a txt file, which can contain more than a thousand records. , what strikes me about the format are two things:

First, that at first glance we might think that the information is separated by blank spaces as shown

Secondly, there are many zeros among the quantities, which make it difficult for me to know if they are part of the quantity to be treated or if they are part of a character separation format.

So, my question is to know the name of the format and if it is convenient to treat all that information from SQL to store it or if it should be passed through a previous separation process before trying to put it in the database. Thank you very much everyone for your time.

I worked on a similar project, and we asked the source institution to provide a breakdown of the field types, lengths, and delimiters. We then stored the source files as-is for auditing, and processed them into tables in SQL Server.

Ask the bank for a key to the file layout. Guessing and asking the Internet is setting yourself up for failure.


How to join two tables with different numbers of rows I've already search the net about this but it can't solve my problem.

I have two tables one with 10 rows and other with 9 rows

table 1

RNUM       JOB_ID
---------- ----------
1          AC_ACCOUNT
2          AC_MGR
3          AD_ASST
4          AD_PRES
5          AD_VP
6          FI_ACCOUNT
7          FI_MGR
8          HR_REP
9          IT_PROG
10         MK_MAN

table 2

RNUM       JOB_ID
---------- ----------
1          AC_ACCOUNT
2          AC_MGR
3          AD_ASST
4          AD_PRES
5          AD_VP
6          FI_ACCOUNT
7          FI_MGR
8          HR_REP
9          IT_PROG

I want to join these two tables but I want the output to be like this

RNUM       JOB_ID            RNUM       JOB_ID
---------- ----------        ---------- ----------
1          AC_ACCOUNT        null       null
2          AC_MGR            1          AC_ACCOUNT
3          AD_ASST           2          AC_MGR
4          AD_PRES           3          AD_ASST
5          AD_VP             4          AD_PRES
6          FI_ACCOUNT        5          AD_VP
7          FI_MGR            6          FI_ACCOUNT
8          HR_REP            7          FI_MGR
9          IT_PROG           8          HR_REP
10         MK_MAN            9          IT_PROG

my first code is

select a.*, b.*
from samp a, samp2 b where a.rnum = b.rnum(+)

but the result of my code is

RNUM       JOB_ID                RNUM       JOB_ID
---------- ----------            ---------- ----------
1          AC_ACCOUNT            1          AC_ACCOUNT
2          AC_MGR                2          AC_MGR
3          AD_ASST               3          AD_ASST
4          AD_PRES               4          AD_PRES
5          AD_VP                 5          AD_VP
6          FI_ACCOUNT            6          FI_ACCOUNT
7          FI_MGR                7          FI_MGR
8          HR_REP                8          HR_REP
9          IT_PROG               9          IT_PROG
10         MK_MAN                null       null

I want to connect table 2's number 1 entry(AC_ACCOUNT) to table 1's number 2 entry(AC_MGR) as what you can see in my desired output.

I used rownum as a technique just to have a connection between my two tables.

I think I misunderstood left outer join here, please kindly enlighten me how to achieve my desired output.

When joining you join one key to another x=y but you seem to want to join x=y-1 instead. In your case you want to join 1 with 0 and 2 with 1. This is not a good practice. When joining you always you should join keys with eachother without chaning them.

You should think about what you really want as a result because it is not a logical join to do. But to genereate the result you want you can do like this:

select a.*, b.* from t1 a LEFT OUTER JOIN t2 b on a.rnum-1 = b.rnum

DB Fiddle

This is a good reference image for future use:


Union of data from all tables with name containing a substring I have 500+ tables all containing a column called COLUMNDATA. The table names are like: TABLE_FIRST, TABLE_SECOND, ... ,TABLE_FIVE_HUNDREDTH. What I am trying to get is a column containing all the unique entries from COLUMNDATA across all of these tables. The problem is made a little more challenging because all tables in the DB contain this column, and I am not interested in data from the tables which aren't called TABLE_NUMBER.

I have tried various recipes including UNION and INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'TABLE%' but to no avail.

If anyone has an idea that would be great! Otherwise I'll just stick with the 1000 line script I've got already. For now I have a nasty but gets-the-job-done

CREATE TABLE TAB_NAME
AS
SELECT COLUMNDATA FROM TABLE_FIRST
UNION
SELECT COLUMNDATA FROM TABLE_SECOND
UNION . . .

ad infinitum

Yes, you need a script but it doesn't have to be 1000 lines.

--make a table or global temporary table to put the results in

CREATE TABLE TEMP_COLUMN_DATA
( PK_ID NUMBER(9),
  TABLE_SOURCE VARCHAR2(30),
  TEXT_CONTENT VARCHAR2(4000));

--add the primary key of PK_ID, are contents larger than 4000 characters?  use a CLOB

--create a trigger on the table if the PK_ID is null add a sequence value

DECLARE
v_text  VARCHAR2(4000);
CURSOR tab_col IS
Select TABLE_NAME from all_tab_cols
where COLUMN_NAME = 'COLUMNDATA'
and table_name LIKE 'TABLE%'
--modify the cursor here to exclude any tables you dont want
--and table_name not in('xxxx','xxxxx','xxxxx')
ORDER BY 1;
BEGIN
  FOR THE_TABS IN TAB_COL LOOP
    V_TEXT := 'INSERT INTO TEMP_COLUMN_DATA SELECT NULL,'
    || THE_TABS.TABLE_NAME
    ||','
    || THE_TABS.TABLE_NAME
    || '.COLUMNDATA '
    || ' from '
    || THE_TABS.TABLE_NAME
    || ' order by 2';
    DBMS_OUTPUT.PUT_LINE(V_TEXT);
    EXECUTE_IMMEDIATE(v_text);
  END LOOP;
 END;

--then do a select distinct columndata from temp_column_data


How to script all permissions on a schema SQL management studio allows to create scripts for all db objects, however I so far couldn't find a way to correctly script a schema or user. The permissions of a user on a schema are not included in the script that is created. Did I make something wrong or is MSFT her a bit sloppy ?

Your friend is sys.database_permissions to check/script out permisssions.

Below is the script that I am using when doing a refresh of PROD on DEV or UAT server. I will script out before hand all the permissions and after the restore I will just run the script.

/*
Script DB Level Permissions v2.1
Source: http://www.sqlservercentral.com/scripts/Security/71562/
*/

DECLARE
    @sql VARCHAR(2048)
    ,@sort INT

DECLARE tmp CURSOR FOR

/*********************************************/
/*********   DB CONTEXT STATEMENT    *********/
/*********************************************/
SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],
        1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],
        1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '' AS [-- SQL STATEMENTS --],
        2 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********     DB USER CREATION      *********/
/*********************************************/

SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --],
        3 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ' AS [-- SQL STATEMENTS --],
        4 AS [-- RESULT ORDER HOLDER --]
FROM    sys.database_principals AS rm
WHERE [type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups

UNION

/*********************************************/
/*********    DB ROLE PERMISSIONS    *********/
/*********************************************/
SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
        5 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  'EXEC sp_addrolemember @rolename ='
    + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --],
        6 AS [-- RESULT ORDER HOLDER --]
FROM    sys.database_role_members AS rm
WHERE   USER_NAME(rm.member_principal_id) IN (
                                                --get user names on the database
                                                SELECT [name]
                                                FROM sys.database_principals
                                                WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
                                                and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
                                              )
--ORDER BY rm.role_principal_id ASC

UNION

SELECT '' AS [-- SQL STATEMENTS --],
        7 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********  OBJECT LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
        8 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  CASE
            WHEN perm.state <> 'W' THEN perm.state_desc
            ELSE 'GRANT'
        END
        + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects
        + CASE
                WHEN cl.column_id IS NULL THEN SPACE(0)
                ELSE '(' + QUOTENAME(cl.name) + ')'
          END
        + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
        + CASE
                WHEN perm.state <> 'W' THEN SPACE(0)
                ELSE SPACE(1) + 'WITH GRANT OPTION'
          END
            AS [-- SQL STATEMENTS --],
        9 AS [-- RESULT ORDER HOLDER --]
FROM
    sys.database_permissions AS perm
        INNER JOIN
    sys.objects AS obj
            ON perm.major_id = obj.[object_id]
        INNER JOIN
    sys.database_principals AS usr
            ON perm.grantee_principal_id = usr.principal_id
        LEFT JOIN
    sys.columns AS cl
            ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
--WHERE usr.name = @OldUser
--ORDER BY perm.permission_name ASC, perm.state_desc ASC

UNION

SELECT '' AS [-- SQL STATEMENTS --],
    10 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********    DB LEVEL PERMISSIONS   *********/
/*********************************************/
SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
        11 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  CASE
            WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
            ELSE 'GRANT'
        END
    + SPACE(1) + perm.permission_name --CONNECT, etc
    + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>
    + CASE
            WHEN perm.state <> 'W' THEN SPACE(0)
            ELSE SPACE(1) + 'WITH GRANT OPTION'
      END
        AS [-- SQL STATEMENTS --],
        12 AS [-- RESULT ORDER HOLDER --]
FROM    sys.database_permissions AS perm
    INNER JOIN
    sys.database_principals AS usr
    ON perm.grantee_principal_id = usr.principal_id
--WHERE usr.name = @OldUser

WHERE   [perm].[major_id] = 0
    AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas
    AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group

UNION

SELECT '' AS [-- SQL STATEMENTS --],
        13 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
        14 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT  CASE
            WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
            ELSE 'GRANT'
            END
                + SPACE(1) + perm.permission_name --CONNECT, etc
                + SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>
                + QUOTENAME(SCHEMA_NAME(major_id))
                + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default
                + CASE
                    WHEN perm.state <> 'W' THEN SPACE(0)
                    ELSE SPACE(1) + 'WITH GRANT OPTION'
                    END
            AS [-- SQL STATEMENTS --],
        15 AS [-- RESULT ORDER HOLDER --]
from sys.database_permissions AS perm
    inner join sys.schemas s
        on perm.major_id = s.schema_id
    inner join sys.database_principals dbprin
        on perm.grantee_principal_id = dbprin.principal_id
WHERE class = 3 --class 3 = schema

ORDER BY [-- RESULT ORDER HOLDER --]

OPEN tmp
FETCH NEXT FROM tmp INTO @sql, @sort
WHILE @@FETCH_STATUS = 0
BEGIN
        PRINT @sql
        FETCH NEXT FROM tmp INTO @sql, @sort
END

CLOSE tmp
DEALLOCATE tmp


The 'Database' with 'ID' = 'xxxxxxx' doesn't exist in the collection I have a SSAS Tabular server with 30 database.

I also have a scheduled process to backup all databases daily.

When I run the process which is an SSIS package with script task using AMO, I get this error :

  The 'Database' with 'ID' = 'nameofdatabase' doesn't exist in the
  collection.

The backup process is running under the service account credential and it successfully backs up 27 databases but fails for only 3 of them.

I checked those databases and don't see anything special on them.

I googled the error message and most of the issues are related to deploying or processing the database. I don't see any reason for failing the backup.

What is the problem and how I can resolve this issue?

One thing that could cause this is if you use the Name of the database instead of the ID.

For example, I created a database with with the name Demo1, so the ID defaulted to the same string.

Afterwards I renamed the database, but that doesn't change the ID.

If your AMO script uses the name string instead of the ID string that could cause this error.


What does the DBMS do exactly when i send it a SELECT query regarding the .frm files and database pages? (MySQL) so i was reading about database pages and DBMS in mysql and it got me really confused

so based on their documentation database pages have a part where it points to rows in the page, which have tuples of the database, but how does the DBMS find the correct page when i type something like SELECT name from mytable where id>1? there is no info on this

I Suppose the DBMS looks for the corresponding .frm, but how does it find the correct pages that should read inside that?

i read this article : https://ieeexplore.ieee.org/document/5474822/

which showed the header content of .frm files, but there was nothing in the header that helps find the correct page, then how does the DBMS find it?!

The .frm file contains nothing more than the schema.  It is looked at when opening the file, then info is kept in RAM.

ibdata1 or the .ibd file is the "tablespace" for the data.  It contains meta info about the data, indexes, etc.  In particular, it points to the root nodes for the indexes of the table.  (Note:  The data is stored based on the PRIMARY KEY, so the "data" acts very much like an "index".)

From the "root" node of a B+Tree, you can drill down to an individual row.

From an individual row, it is possible (when doing a range/index/table scan) to easily find the next/previous row in the B+Tree.

The BTrees are organized in 16KB "blocks", pointed to by a tuple that contains (approximately): tablespace number and block number.  Since a tablespace is a file, the block number is easily translated into a byte offset in that file.  The OS has an extra layer of looking up such a logical address to find a physical address.

Within the Block, there may be, say, 100 "records" that are either data rows or index rows or links down the BTree to other blocks ("nodes").  (The "100" is a Rule of Thumb; in reality, it could be as few as 1, to as many as several thousand.)

To find a particular row (other than the "next" or "previous") within a block, some form of search is done.  (I don't know whether this is a linear scan or a binary search.)

SELECT name from mytable where id>1

Parse the command.
"Open" the table -- either cached (cf table_open_cache) or use .frm (or Data Dictionary in MySQL 8.0)
Figure out how to perform the query
Find index on id.  Let's say it is PRIMARY KEY(id)
Locate root of B+Tree for the PK
Drill down until locating the last (or, since a PK is unique, the only) row with id=1, then start at the 'next' record.
Scan forward, fetching name from each row until you hit the end of the table.


Where/how does innodb store tables in files? I read somewhere that InnoDB can store all the tables in a single file or in separate files. Is this a configuration option? What is the default setting? A file for all tables or a file per table? What is the recommended setting?

The setting is innodb_file_per_table, and it's default 0 (ie. use one big file) up to MySQL 5.1, and then in either 5.5 or 5.6 the default changed. Changing this value will not affect tables which already exist.

Even with innodb_file_per_table set to ON you still need the shared tablespace file (ibdata1 file by default).

There is no one recommended setting, it depends on your databases. Some advantages:
- It's easier to reclaim space from the file (you only need to reimport one table)
- It can be faster on ext* filesystems as they use inode level locking, and so splitting the accesses over multiple files will be alleviate this a little

Some disadvantages:
- It takes more space, as reclaimed space can only be used in the same table
- It takes longer to recover from an unclean restart
- It can be a big problem if you have a large number of tables (as you can hit an open files limit)
- I might be wrong with this one, but if the temporary tables aren't in the system tablespace a drop table can take a long time

What problem are you trying to solve?


Oracle: Recover source code/table structures from "utterly wiped out" development database Given an Oracle 11.1 database used for development, running in NOARCHIVELOG mode, where all datafiles (including undo) have been wiped out; and only SYSTEM and SYSAUX datafiles and control file remain (and even then, just as hot backup of disk taken while database was open; not as database/RMAN backup), is there any way to open a "skeletal" version of the database such that source code and table schemas can be retrieved?  No concerns whatsoever for data recovery/integrity/etc; just want to retrieve whatever data dictionary contents that are possible (given these scraps of the original).

UPDATE

In order to start the instance without interfering with an existing running instance (e.g ORCL), before starting anything:

sqlplus / as sysdba
create pfile='/tmp/initORCL2.ora' from spfile='?/dbs/spfileORCL.ora';
exit

Edit /tmp/initORCL2.ora, and modify (add if doesn't exist) the instance_name:

*.instance_name='ORCL2'

Prevent listener registration, e.g:

*.local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=DUMMY)))'

Create the new spfile:

sqlplus / as sysdba
create spfile='?/dbs/spfileORCL2.ora' from pfile='/tmp/initORCL2.ora';
exit

And continue according to the original steps:

export ORACLE_SID=ORCL2
sqlplus / as sysdba

If you don't use spfile, simply copy and edit the initORCL.ora.

/UPDATE

startup nomount

You have lost the UNDO tablespace, so switch to manual undo management:

alter system set undo_management=manual scope=spfile sid='*';

You have lost the redo logs and your remaining datafiles are in an inconsistent state, so you will not be able to open the instance, unless you allow resetlogs corruption:

alter system set "_allow_resetlogs_corruption"=true scope=spfile sid='*';

Mount the database:

startup force mount

You have lost the datafiles, so you will not be able to open the instance, unless you offline drop the datafiles:

select name from v$datafile;

alter database datafile '/database/users01.dbf' offline drop;
alter database datafile '/database/undotbs01.dbf' offline drop;
...

In order to perform open resetlogs, and incomplete media recovery should be performed (even if it can not do anyhting):

recover database until cancel;

At this point you should be able to open the instance:

alter database open resetlogs;

Should be, because depending on the scale of inconsistency of the remaining SYSTEM datafile, the  instance may not open at all, or it may crash immediately or in best case scenario, it may open and remain stable so you can query DBA_SOURCE, SYS.SOURCE$, etc.


mysql 8 datetime default throwing and error when forward engineering I am trying to create a schema by forward engineering a model eer diagram. But its giving an error on the datetime fields when using the default current_timestamp which is allowed in mysql 8 for datetime fields.
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema test
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema test
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8 ;
USE `test` ;

-- -----------------------------------------------------
-- Table `test`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test`.`users` (
  `user_id` CHAR(36) NOT NULL,
  `email` VARCHAR(225) NOT NULL,
  `password` VARCHAR(20) NOT NULL,
  `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME(6) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME(6) NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`)
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

ERROR: Error 1067: Invalid default value for 'created_at'

I don't know why this is. I thought it had something to do with the variables mysql workbench sets when forward engineering. But I can't figure out what it is as it looks like they are set in a normal way.
Is it due to the variables or the workbench? Also why are the variables used? Can I run the script without them or are they necessary? To be frank, I am a bit put off by the use of the work "OLD" in the variables. Is it using version 5.6 defaults? Because I want the all the new settings on mysql 8 to be used by default.

As @akina pointed out in the comments:

Default value datatype and column datatype MUST MATCH STRICTLY ! If you create DATETIME(6) column then you must use CURRENT_TIMESTAMP(6) default value. Test - add (6) to default value for created_at only - and the error message will point you to updated_at (the first syntax error detected)


use function arguments or variable in postgres function I want to set the default value for the column building and floor in my table points in the below function called test(b,f)

Create or replace function test(b text, f text) returns void as
$$
Begin
Alter table points alter COLUMN building set default b;
Alter table points alter COLUMN floor set default f;
End;
$$
language plpgsql;

When I run select test('Shopping Mall','03') it gives me below error:

 column b does not exist

It seems that I cannot use a function arguments b in the alter ... query?

I have found the solution by using function triggers. I didn't dynamically change the default value but I used trigger function to update the column value before insert action. It can achieve the same result.

Create or replace function update_value()
Returns Trigger AS $$
BEGIN
IF NEW.floor ISNULL THEN
    NEW.floor :=(select floor from buildings where my_current_location='yes');
END IF;

IF NEW.building ISNULL THEN
    NEW.building :=(select name from buildings my_current_location='yes');
END IF;
RETURN NEW;
END $$ Language 'plpgsql';

Also you need to add a trigger to the table:

Create triggers update_value_trigger Before Insert On points For each row execute procedure update_value();


Postgresql. Generate sets of random integers With this command I can generate 16384 random integers between 1 AND 200,000.

SELECT generate_series (1,16384),(random()*200000)::int AS id

I want to generate 10 sets of such integers. Each set must have an integer identifier, something like that:

  1 | 135

  1 | 1023

  ... end of first set of 16384 random numbers

  2 | 15672

  2 | 258732

  ... end of second set of 16384 random numbers

Is this possible with an SQL command, or should I write a function for that?

Would this be what you want?

SELECT
   n, generate_series (1,16384), (random()*200000)::int AS id
FROM
   generate_series (1,10) AS x(n) ;

or the similar:

SELECT
    n, i, (random()*200000)::int AS id
FROM
    generate_series (1,10) AS x(n)
  CROSS JOIN
    generate_series (1,16384) AS y(i) ;


Parent/Child table design? Lets say I have a table to hold Tasks and then I have a table to hold Sub Tasks, is it better to break Tasks and Sub Tasks into separate tables or keep them both in the same table with a possible ParentId column.  What if a Parent Task could also be a Sub Task, does this change the design?

If a Parent Task and Subtask are essentially identical, save their rank in a hierarchy, then yes, a single table is appropriate. You can use a nullable foreign key to the primary key in the same table like this:

create table tasks (
  task_id bigserial primary key,
  subject text,
  ...,
  parent_task_id bigint null references tasks(task_id)
);

If your database server does Recursive Common Table Expressions (example PostgreSQL), then use those for querying. Here are some examples.


SSIS ETL or CDC - real time DWH reporting I am currently working on SSIS package for continuous incremental data loads of the tables (40 tables now and expected to increase in near future)  from OLTP system to a Reporting database residing on a different server.

The request is for real time data sync between the two sources.

Should I continue and test it with just regular SSIS -> Staging -> merge route OR should I look into implementing CDC. Will that be faster than plain old ETL considering the number of tables involved?

Have you considered transactional replication? From what I see that would be the best option with the less administrative overhead.

That way you will have the DB in practically real time and can be relatively easily modified to include more tables in the future.

https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication?view=sql-server-2017


Prevent Privileged User from Denying Access to SA I have to set up a system where UserA has almost full control over a database (not the server), except for one table that's used by sa, and so sa will always need access to this table.

So far I have:

-- executing as sa
CREATE SCHEMA [sa_schema];
CREATE TABLE [sa_schema].[table1] ( ... );
GRANT SELECT , REFERENCES ON OBJECT::[sa_schema].[table1] TO PUBLIC

CREATE USER [UserA] WITHOUT LOGIN;
GRANT CONTROL TO [UserA] WITH GRANT OPTION;
DENY ALTER , DELETE , INSERT , TAKE OWNERSHIP , VIEW CHANGE TRACKING , UPDATE
  ON SCHEMA::[sa_schema] TO [UserA];
DENY ALTER ON USER::[sa] TO [UserA];

I thought this would be sufficient, but it turns out that it's not. It's perfectly legal for UserA to run:

-- executing as UserA
CREATE ROLE [Denied]
DENY SELECT ON OBJECT::[sa_schema].[table1] TO [Denied]
ALTER ROLE [Denied] ADD MEMBER [sa]

And now sa can no longer read from this table.

-- executing as sa
SELECT * FROM [sa_schema].[table1]

  Msg 229, Level 14, State 5, Line 2

  The SELECT permission was denied on the object 'table1', database 'mydb', schema 'sa_schema'.

Of course, sa could query the database to find out roles it has been added to and remove itself from them, but that seems like a kludge and a lot of work just to perform a select. I think I could avoid this problem by making sure UserA doesn't have the ability to add sa to any database roles or by ensuring UserA cannot modify the permissions on this object, so no matter what roles sa would be added to there's never going to be a DENY on this object.

Does anybody have any ideas on how to make sure sa will always have access to this table regardless of what other changes UserA makes?

SA is a login just like any other.  You may want to do some research on best practices regarding the SA account.  It does not need to have superpowers.

Members of the sysadmin role, however, cannot be locked out of anything and members of this role bypass security checks.  If SA is a member of sysadmin, there shouldn't be anything you need to do.

A programmatic approach could be to create a SQL Agent job that restores the correct (according to you) memberships for administrator accounts.  Set this to run on a schedule and you're guaranteed to have the permissions restored (unless UserA decided to mess with the job).

However, this is really more of a personnel issue.  The people that have this level of permission on a server should be experienced and trustworthy enough not to mess with the administrative level permissions.  If they don't know what they are doing, they should not be able to do it.  If they know what they are doing and do it any way, you have a different problem.  This may best be handled by a business policy indicating that anyone that messes with administrative level permissions may be subject to disciplinary action.


MonetDB auto-backup mechanic problem I'm trying to set up mechanism that will automatically create database backup.
The idea is to create a script that will be executed by crontab. Seems easy but I'm struggling with the following problems:

I cannot use msqldump, because it needs password input from the user;
I've tried physical copy, but after removing database or dbfarm and restoring them, monetdb and monetdbd couldn't start "restored" database and dbfarm;

Can I bypass above problems and/or are there other solutions?

Create .monetdb file in the home directory as below:

cat << EOF > $HOME/.monetdb
user=monetdb
password=monetdb
EOF

Replace the above content with your monetdb username and password.
Change the file permission to prevent other user from reading it [optional]
Run:

export msqldump=$HOME/.monetdb
echo 'export msqldump=$HOME/.monetdb' >> $HOME/.bashrc

After making above changes, I have ran the below mysqldump command, it doesn't ask user to input password.

mysqldump -d[database-name] > [sql_dump_backup_path]

Source :How to setup mysqldump without password in cronjob


MySQL convert rows to column I am working with 2 tables and need help to produce an output by converting rows to columns, and i need to sum the value first be grouping
Here is the fiddle: https://www.db-fiddle.com/f/kmQjRvvensRTfYsSELxMF2/1
Here is the table:
CREATE TABLE teacher (
TeacherId INT, BranchId VARCHAR(5));
INSERT INTO teacher VALUES
("1121","A"),
("1132","A"),
("1141","A"),
("2120","B"),
("2122","B"),
("2123","B");

CREATE TABLE activities (
ID INT, TeacherID INT,    Hours   INT);

INSERT INTO activities VALUES
(1,1121,2),
(2,1121,1),
(3,1132,1),
(4,1141,NULL),
(5,2120,NULL),
(6,2122,NULL),
(7,2123,2),
(7,2123,2);

My SQL:
    SELECT totalhours hours
             , branchid
             , COUNT(*) total
          FROM
             ( SELECT COALESCE(y.hr,0) totalhours
                    , x.branchid
                    , x.teacherid
                 FROM teacher x
                 JOIN
                    ( SELECT teacherid
                           , SUM(hours) hr
                        FROM activities
                       GROUP
                          BY teacherid
                       ORDER
                          BY hr ASC
                    ) y
                   ON x.teacherid = y.teacherid
             ) a
         GROUP
            BY hours
             , branchid
         ORDER
            BY hours
             , branchid;

Output:
   +---------------+-------------------+--------------------+
   |     hours     |     branchid      |       total        |
   +---------------+-------------------+--------------------+
   |       0       |        A          |         1          |
   |       0       |        B          |         2          |
   |       1       |        A          |         1          |
   |       3       |        A          |         1          |
   |       4       |        B          |         1          |
   +---------------+-------------------+--------------------+

Explanation:
Table teacher consist teacher id and branch id, while table activities consist of id, foreign key teacher id, and hours. Hours indicate duration of each activities made by teacher. Teacher can do more than one activities or may not do any activities. Teachers who not doing any activity will be set to null.
The objective of queries is to produce a table that consist of summary of teachers activity by branch and group by hours.
In the expected output table, 'Hours' is a fixed value to indicate hours from in ascending order starting from 0 to 12. It will still display value even there are no hours value for A and B. A and B columns are branch. The value indicates total number of teachers who are doing activities. So, for row 0, there are 1 teacher for branch A and 2 teachers for branch B who are not doing activities.
Expected output:
   +-----------+------------+------------+
   |   Hours   |     A      |     B      |
   +-----------+------------+------------+
   |     0     |     1      |     2      |
   |     1     |     1      |     0      |
   |     2     |     0      |     0      |
   |     3     |     1      |     0      |
   |     4     |     0      |     1      |
   +-----------+------------+------------+

SELECT hours, SUM(CASE WHEN branchid = 'A'
                       THEN total
                       ELSE 0 END) A, SUM(CASE WHEN branchid = 'B'
                                               THEN total
                                               ELSE 0 END) B
FROM output
GROUP BY hours;

or
SELECT hours, SUM(CASE WHEN branchid = 'A'
                       THEN total
                       ELSE 0 END) A, SUM(CASE WHEN branchid = 'B'
                                               THEN total
                                               ELSE 0 END) B
FROM (SELECT 0 hours UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) hours_table
LEFT JOIN output USING (hours)
GROUP BY hours;

fiddle


Backing up Oracle Database Services I'm currently working on project to clean database services on oracle database.
Analyze first whats not needed and then drop.

I need to prepare backup of services data for rollback purpose.
(toad can export many types of object metadata, but there isn't option to export services metadata)

Someone know way to achieve that?

You can get the required data from DBA_SERVICES (or v$active_services)

You can get the DDL required to create them again using something like this:

select 'DBMS_SERVICE.CREATE_SERVICE(
    SERVICE_NAME => ''' || name || ''',
    NETWORK_NAME => ''' || network_name || ''',
    parameter_array => params
  );' from dba_services;


Fact table design - Level or contribution measures I am trying to come up with a dimensional model for the following:

The source data comes from game machines as meter messages. Basically, the game machine accepts payments and sends a snapshot of the current values of several measures. The message contains over 15 meter types such as the coin-in level (in cents), games played level, games won, games lost and so on. This message is captured on a daily basis. Because of the fact that the snapshots of numeric levels are taken on a daily basis, I thought that we should use a periodic snapshot fact table.

Would it be better to store the levels of the numeric values as measures (ex. the total coin-in value and total games played of the machine as of the snapshot) or should we store the contribution amount for the snapshot period (ex $100 was deposited and 10 games were played in the one day ). Or maybe both levels and contributions (levels would not be additive across the date dimension but contributions would be)??
What is the difference in terms of analysis.

We have not started the requirements gathering yet but it seems that there are a lot requests on trending the coin-in data for example by month per machine.

Thanks,

What is the difference in terms of analysis.

Storing the values that apply to the snapshot period would be the natural fit for most kinds of reporting, including "requests on trending the coin-in data for example by month per machine."

The risk is that if a snapshot goes missing the total on the machine may not match the totals in the cube. Storing both would give you the means to cross-check.


SQL Server - How can varbinary(max) store > 8000 bytes? In SQL Server 2005 and 2008 R2, I'm calling into a stored procedure that has an out parameter defined as varbinary(max). The out parameter returns 10020 bytes according to DATALENGTH.

However, SQL Server errors if I try to define a varbinary with > 8000 bytes such as varbinary(10000). E.g.

The size (10000) given to the type 'varbinary' exceeds the maximum allowed for any data type (8000).

What is happening here? How can SQL Server return more bytes than allowed in the data type? Is SQL Server using some other data type behind the scenes to hold > 8000 bytes?

The maximum size you can specify is 8000.  Anything larger needs to be specified as (MAX)

See https://msdn.microsoft.com/en-us/library/ms188362.aspx for details.


Matching left and right single-quotes used as apostophes I have four columns containing names and want to search these using a LIKE in a Microsoft SQL Server environment.

The complication comes that names may include left and right single-quotes / angled apostrophes (i.e. ‘ and ’, char(145) and char(146) respectively), which should match a straight apostrophe (i.e. ', char(39))

Doing the following is very slow:

SELECT person_id
FROM person
WHERE REPLACE(
          REPLACE(
              person_name,
              CHAR(145),
              CHAR(39)
          ),
          CHAR(146),
          CHAR(39)
      ) LIKE '{USER_INPUT}'

As explained in SQL replace statement too slow on Stack Overflow, this is because the use of REPLACE makes the statement unsargable.

Is there a way that SQL Server can handle situations like this in a better way?

One solution which has been proposed is to have the application generate a 'searchable' value which concatenates all of the fields (person_name, person_surname, person_nickname, etc.) and converts the problematic characters at the point of editing.  This could be effectively indexed and searched.  Storing this data in a separate SQL table/column would require less application rewrite than implementing a full NoSQL solution like Lucene.

The example above is a simplification: the query doesn't literally get built as I explained above and we do implement SQL injection (and other) protections.

The question is how to replace the angled-apostrophes with straight ones in the table data. To clarify:

User supplies O‘Malley - this should match both O‘Malley or O'Malley
User supplies O'Malley - this should match both O‘Malley or O'Malley

We need to replace the SQL data, not the user's input.  We can convert the user input on the way through the application so that if they input angled apostrophes we change them to simple apostrophes before passing in to SQL.  It's the data in SQL we need to standardise.

Unfortunately the data must stay in the database as the correct angled bracket, but when we do the search we need to match them all against straight apostrophes.

The best way to handle your issue (and avoid SQL injection) is to pass in your user input as a variable.  Since you are using a LIKE you can do something like this:

CREATE TABLE #person (person_name nvarchar(50))
INSERT INTO #person VALUES (N'Bob'),(N'Bo''b'),(N'Bo‘b'),(N'Bo’b'),(N'Bo#b'),(N'Bo^b')

DECLARE @user_input nvarchar(50) = 'Bo’b'

SET @user_input = REPLACE(
                        REPLACE(
                                REPLACE(@user_input, N'‘', N''''),
                                N'’', N''''),
                                N'''', N'[‘’'']')

-- @user_input now == Bo[‘’']b

SELECT person_name
FROM #person
WHERE person_name LIKE @user_input

Basically this replaces all of the different 's with a single type (the ') and then puts []'s around all three so that they get used in the LIKE.


How to Install/Configure Oracle Instant Client on Ubuntu Installing the Oracle Instant Client on Ubuntu/Debian based systems is awkward since Oracle is RPM centric regarding its Linux support.

What's the best way to easily install and configure the Instant Client on Ubuntu?

Updated Per Feedback
I never really found a good answer to this that didn't involve a lot of manual steps, so I made a solution.

You can download a script from my oracle_instant_client_for_ubuntu GitHub repo which will convert Oracle rpms into Ubuntu compatible packages and then install them for you.

It now supports multiple versions of the instant client.  Feel free to log issues via GitHub.

Currently, this project only supports 64-bit systems.


Do my metrics justify changing the MAXDOP settings? I recently took over a dual instance virtualized SQL Server with 8 x vCPUs and 8 x physical cores.  This is the only VM on the host.

MAXDOP is at the default setting.

The server has 100 high transaction vendor specific databases with some very nasty reporting queries.

CXPacket waits are at the top of the waits list with 63%.

Signal Waits are averaging 15.50%.

In PerfMon the Processor Queue Length rarely breaches 1.

Does this justify setting MAXDOP to 4 or 6?

I would investigate the CXPacket issues first - see what's causing those.  Can you identify a selection of queries that cause those wait types?  Can you then see what is causing the problem through the actual execution plan?  You're looking at gather streams steps, and looking for uneven loads coming in.  Try to identify the source of those - a likely suspect is out-of-date / not helpful index statistics.


SQL to combine records only if data is available I have a table with the following format:

id      description1       description2
123     null               abcd
123     zyxw               abcd
124     null               efgh
124     vuts               efgh
125     null               ijkl
126     null               mnop

Basically, I want SQL to return a single record for 123 and 124 that contains description1, instead of displaying two records for each, one with a null description1. But if there is no description1 in any record of the same id and description2, simply display null. So that the data displayed would be:

id    description1    description2
123   zyxw            abcd
124   vuts            efgh
125   null            ijkl
126   null            mnop

SELECT id,
description1 = MAX(description1),
description2 = MAX(description2)
FROM dbo.MyTable
GROUP BY id
ORDER BY id;


Set PostgreSQL table to LOGGED after data loading I have created an empty UNLOGGED table to faster copy a large amount of data (over 1 Billion rows). Loading the data took around 4 hours.

Now I want to set the table to LOGGED to make it safe for unexpected shutdowns and crashes. This process takes a long time. In fact it takes longer than loading the data. Is this normal, or is there a way to speed it up?

Problem

I believe currently SET LOGGED rewrites the table using the WAL (essentially doing the whole operation), and rewrites the indexes.

So I found a thread about this on the lists

    A new relfilenode is filled with the data - the old one, including the init fork, gets removed by the normal mechanics of rewriting rels.

  There was a long thread about it on -hackers. Doing it without a rewrite and without loosing transactional semantics is really rather hard. And having the capability of doing it with a rewrite is better than not
  having it at all.

You can see the patch that added the SET (LOGGED|UNLOGGED). The implementation hasn't changed much, though there was a plan to fix it that acknowledged the problems

  this design lead us to performance problems with large relations because we need to rewrite the entire content of the relation twice, one into a new heap and other into the WAL, so this project will change the current desing of the mechanism of change an unlogged table to logged without the need to rewrite the entire heap, but just by removing the init forks and if the wal_level != minimal we'll write the contents to the WAL too.

But it seems no more work was done. Looking at the code you can see it,

case AT_SetLogged:        /* SET LOGGED */
    ATSimplePermissions(rel, ATT_TABLE);
    tab->chgPersistence = ATPrepChangePersistence(rel, true);
    /* force rewrite if necessary; see comment in ATRewriteTables */
    if (tab->chgPersistence)
    {
        tab->rewrite |= AT_REWRITE_ALTER_PERSISTENCE;
        tab->newrelpersistence = RELPERSISTENCE_PERMANENT;
    }

Checking that comment,

 * There are two reasons for requiring a rewrite when changing
 * persistence: on one hand, we need to ensure that the buffers
 * belonging to each of the two relations are marked with or without
 * BM_PERMANENT properly.  On the other hand, since rewriting creates
 * and assigns a new relfilenode, we automatically create or drop an
 * init fork for the relation as appropriate.

So you can see, that a rewrite is still required. I guess.

Potential solution

You may be better of copying all of the data into the table on the same transactions that creates the table. Which disables the rewrite, and is mentioned in the docs.

  In minimal level, WAL-logging of some bulk operations can be safely skipped, which can make those operations much faster (see Section 14.4.7). Operations in which this optimization can be applied include:

    COPY into tables that were created or truncated in the same transaction

This would skip the WAL write and heap rewrite.


rman cloning rman-20207 error I am trying to clone a 9i database (C) from a target 9i database (T).  On running the rman script, i get the rman-20207 error : UNTIL TIME is before RESETLOGS time.  When i do a list backup of database, no backup was display.
Just yesterday, the target 9i database was refreshed from production (P), using rman.
Does the refresh of T using rman, removed all previous backups of T?

No, it does not remove backups. They are just missing from the controlfile, and you can catalog them.
When the T database was refreshed yesterday from P, a new incarnation of T started. But your UNTIL TIME clause refers to a point of time before that happened, when the previous incarnation was active.
After restoring the controlfile on C, mount the database, then you can use the following command to view the incarnations:

RMAN> list incarnation;

You will also see the times when they started. Revert to the previous incarnation, e.g:

RMAN> reset database to incarnation 1;

Catalog the backups:

RMAN> catalog start with '/path/to/the/backups';

Then continue with restore and recover as you normally would.


Database Design - Employee Clocking In and Out System I want to develop Employee Clocking In and Out System (website).

I am concerned two things:

If staff forgot to 'Clock Out' from yesterday and they have 'Clock In' today, it should flag to the manager.
Staff may work over time, eg: Clock In Monday 11:00 AM To Tuesday 01:30 AM (after Midnight). I dont want system think thay staff have forgot to clock Out..

Staff may clock In and Clock Out multiple times a day.

How to solve this issue and the database design?

staff table:

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(50)  | NO   |     | NULL    |                |
| password    | varchar(50)  | NO   |     | NULL    |                |
| hourly_rate | decimal(6,2) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

clocking table:

+----------------+----------+------+-----+---------+----------------+
| Field          | Type     | Null | Key | Default | Extra          |
+----------------+----------+------+-----+---------+----------------+
| id             | int(11)  | NO   | PRI | NULL    | auto_increment |
| staff_id       | int(11)  | NO   |     | NULL    |                |
| clock_in_date  | datetime | NO   |     | NULL    |                |
| clock_out_date | datetime | NO   |     | NULL    |                |
+----------------+----------+------+-----+---------+----------------+

SQL:

CREATE TABLE IF NOT EXISTS `clocking` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `staff_id` int(11) NOT NULL,
  `clock_in_date` datetime NOT NULL,
  `clock_out_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `staffs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `hourly_rate` decimal(6,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

One thing you might want to consider changing is making clocking.clock_out_date nullable.  Until the employee clocks out there isn't a reasonable value to use here, so NULL is the best choice.

You can tell the difference between someone who is still working (theoretically) and someone who forgot to clock out by checking their previous clocking record when they clock in.  If the previous record has a NULL clock_out_date then they obviously forgot to clock out.

You could also create a report that checks for employees who haven't clocked out for more than (some configurable number of hours) since they last clocked in.  You should be able to get a reasonable number for the longest expected shift.  It may be once in a while that you report an employee to management for not clocking out when they are actually still working, but that should be rare and the manager will know it - or find it out quickly enough, so the few false positives you might generate aren't serious.

One other thing you might want to think about is not keeping the hourly rate right on your staff table.  It might be better to keep this in a child table which includes date ranges since you want to be able to let a wage go up or down over time and it would be good to (a) have a history and (b) be able to enter future-dated changes to relieve time pressure for data maintenance.


Is that possible to group result on 3 months period basis? I am trying to group results by 3 months period starting by the current month as shown below:

row1      15 -- This should contain November, September and October
row2      25 -- This should contain August, July and June
row3      5  -- This should contain May, April and March
row4      2  -- This is should contain February and Janvier

I have no idea about how to accomplish this. Any help please?

So far I can group by month:

SELECT MONTH(date), MONTHNAME(date) as month, COUNT(*)
FROM table_name
WHERE MONTH(date) < NOW()
GROUP BY MONTH(date)
ORDER BY MONTH(date) DESC

For the following I am assuming you already have a numbers table, If you don't have a numbers table, then I'd recommend you make one then, but if you don't want to then you can create a number list on the fly

With this numbers table you can then define your quarters:

SELECT  DATE_ADD(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -Number - 1 QUARTER) AS QuarterStart,
        DATE_ADD(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -Number QUARTER) AS QuarterEnd
FROM    Numbers

This gives (When run at the time of answering in November 2013):

QUARTERSTART    QUARTEREND
2013-09-01      2013-12-01
2013-06-01      2013-09-01
2013-03-01      2013-06-01

If the same query was run next month the result would be:

QUARTERSTART    QUARTEREND
2013-10-01      2014-01-01
2013-07-01      2013-10-01
2013-04-01      2013-07-01

You can then join your table data to this:

SELECT  DATE_ADD(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -n.Number - 1 QUARTER) AS QuarterStart,
        DATE_ADD(DATE_ADD(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -n.Number QUARTER), INTERVAL -1 DAY) AS QuarterEnd,
        COUNT(t.ID) AS `Count`
FROM    Numbers n
        INNER JOIN Table_Name t
            ON t.DateField >= DATE_ADD(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -n.Number - 1 QUARTER)
            AND t.DateField < DATE_ADD(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -n.Number QUARTER)
GROUP BY n.Number;

Example on SQL Fiddle

N.B. In the select I have deducted one day from the quarter end field as visually the last day of the quarter appears more relevant, but for the join I have kept it as the first day of the next quarter, but used the less than operator

Of course if you also want to count empty quarters you can change the join to a LEFT JOIN, but you will probably then want to limit the number of rows you are returning by adding a Where clause.


Rich text: VARCHAR, TEXT or BLOB in MySQL Question: In my web application (php/MySQL) I will be saving rich text. This may be anything from a couple words to several pages length essay. I’m sure I could get away with using VARCHAR, TEXT or BLOB to store markdown or HTML from a rich text editor like TinyMCE. Which should I use?

My findings so far: In all my searching this week I’ve not found anything that tried to be a definitive source as to which would be best in general. I think VARCHAR is a standard and TEXT is not. BLOB is for binary data and so is overkill. TEXT may be deprecated in some DBMS. One major concern I have revolves around my ignorance as to how VARCHAR is saved to the database. I.e., if I max it out (or leave it undefined) will it waste storage or memory if there is only a few words in it? Or as a modern DB does MySQL not store blank data as reserved space? My research has indicated that the performance difference is very minor in modern systems between VARCHAR and TEXT queries. Also, I do not need to do many text searches in the content. I’m not planning on any, however, the ability to do so would be a benefit, long term.

Tentative Conclusion: I think to be best and standards compliant (low priority) I should make a VARCHAR type with no limit. Then just insert the markdown, HTML or textile data in there. I assume not specifying a limit would max out the data capacity on the server.

Would VARCHAR be the best standard to employ? Or have I gone completely off the rails in my research?

It would depend on the size of the data you want to save in that field. You should know that there is a limit for VARCHAR, and that limit is in bytes, so number of characters that can be stored will depend on the character set you use. For example, if you use UTF 8, you will be able to store less amount of data.

To sum it up, there is no "best" solution, but if your data fits in VARCHAR, then use it :). If not, you will have to use TEXT or BLOB, or you will have to zip the data before saving it (Check compress)


SQL Server: what is the reason to favor customized database roles over fixed ones? This may be a bit vague, but I was pondering how much DBA's use fixed database roles controlling security of databases. So the question is, is there a reason to favor customized database roles over fixed database roles (db_datareader etc.)?

Fixed database roles provide permissions to the whole database. Custom roles come into play when you don't want to give users permissions to all of the database, just a portion of it.

For example, db_datareader provides SELECT permission to every table, view, etc in that database. For compliance reasons there may be some tables or views that HR staff should only be able to SELECT from and other staff shouldn't. That is usually where custom roles come into play.


How to flush SQL Server database from RAM to hard disk? As part of the nightly maintenance plan, my working MS SQL Server 2012 databases are backup-ed with options of simple recovery and full backup. Transaction log is not backup-ed. Also, at nights the MSSQLSERVER service or SQL Server is restarted

Checking the the properties of corresponding files in operating system (Windows Server 2008), I observe that "Modified" time of .mdf files correspond to the time of maintenance plan (making backup) run but .log files modification time is one-two weeks earlier.

Why is such difference and why the operating system fails to update modification time?

So, what is the time and reason of .mdf file modification time?
Is it restart of SQL Server or the time of backup?

How can I estimate when a database was last written to a physical disk, that is how recent is .mdf file of a corresponding database?
How can I force SQL Server to flush the database to hard disk in order to have most actualized .mdf file in case of seeing problems in SQL Server functioning?

The mdf and ldf files display the last date and time the SQL Server opened the file in question.  This is most likely to be the time SQL Server was last restarted.

There is no reason to force SQL Server to flush to disk, it does that automatically during its checkpoint mechanism.

Even if the entire machine crashed during the middle of a write-to-disk operation, when SQL Server is restarted it will run through the log file, rolling forward and backward any operations that were not fully committed to the .mdf file.  This is one of the primary tenets of an atomic, consistent, isolated and durable database server.

If you want to manually force a checkpoint operation, you can execute the following command in SQL Server Management Studio, or SQLCMD, etc:

CHECKPOINT

For further information on the CHECKPOINT command, see http://technet.microsoft.com/en-us/library/ms188748.aspx

Regarding your statement at the beginning of your question that you backup the database and not the log file, if your data is business-critical you should enable full recovery on the database, and ensure your log file is backed up several times a day (at least).  Backing up the log file assures that you can restore the database to a given point-in-time (most likely the point at which you last performed a log backup).  Depending on your business requirements for recovery point and recovery time, you may want to backup the log file as often as every 5 minutes!

For further information on how to correctly implement business-critical backup for SQL Server see http://technet.microsoft.com/en-us/library/hh393536.aspx


(POSTGRES) ON CONFLICT ... WHERE condition doesn't seem to be working We're trying to populate a new table for a new feature with information from an old table, but we are going to be running this query as a script multiple times because we want to catch any user updated data.

Table 1 Schema.

table1
id | email | first_name | last_name | job | phone | user_id | institution_id | institution_type | completion_state | created_at | updated_at | mobile_phone | deleted_at
----+-------+------------+-----------+-----+-------+---------+----------------+------------------+------------------+------------+------------+--------------+------------

Table 2 Schema.

table2
 id | contact_id | status | data | created_at | updated_at | source_updated_at
----+------------+--------+------+------------+------------+-------------------

This is my query

INSERT INTO table2 AS iic (contact_id, data, created_at, updated_at, source_updated_at)
SELECT
  cc.id,
  (
  SELECT row_to_json(_) FROM (
    SELECT
      cc.email,
      cc.first_name,
      cc.last_name,
      cc.job,
      cc.phone,
      cc.user_id,
      cc.institution_id,
      cc.institution_type,
      cc.completion_state,
      cc.updated_at,
      cc.mobile_phone,
      inst.type,
      inst.name,
      inst.description,
      inst.assets_under_management,
      inst.phone,
      add.id,
      add.street,
      add.street2,
      add.city,
      add.state,
      add.country,
      add.zip_code
      ) AS _
    ) AS data,
  current_timestamp,
  current_timestamp,
  cc.updated_at
FROM table1 cc
LEFT JOIN institutions inst
  ON cc.institution_id = inst.id
LEFT JOIN addresses add
  ON add.addressable_id = inst.id
ON CONFLICT (contact_id) DO
  UPDATE SET status = 'updated',
             source_updated_at = excluded.updated_at
  WHERE iic.source_updated_at != excluded.updated_at;

So, we're copying Table 1 information into Table 2. The first time we run it, everything copies over perfectly, and the status column is null. If I then change the updated date for a single row on table1, I expect that the

ON CONFLICT ... UPDATE

would trigger and update table2 ONLY where the updated_at row has changed. Instead, it changes all status on table2 to updated.

I suspect that the WHERE clause is not row specific and applies to a larger set, in which case this condition will always return true. However, if someone can shed a light on how I can modify my query to return what I am looking for that would be great.

Please let me know if any of this requires clarification.

Cheers,

postgres v.9.6.11

With the help of @a_horse_with_no_name, I was able to find the error in my thinking. There is a great example in the PostgresSQL docs that really clarified it for me:

  Insert or update new distributors as appropriate. Assumes a unique index has been defined that constrains values appearing in the did column. Note that the special excluded table is used to reference values originally proposed for insertion:

INSERT INTO distributors (did, dname)
    VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;

My original thought was that the EXCLUDED table used the same column names as the table I was trying to insert from. In actuality, it references the column names of the table in which I am trying to insert into, but the values are what were originally proposed for insertion.

So, the end of my query should have looked like this:

...
ON CONFLICT (contact_id) DO
  UPDATE SET status = 'updated',
             source_updated_at = EXCLUDED.source_updated_at
  WHERE iic.source_updated_at != EXCLUDED.source_updated_at;

Thank you for being my rubber duck, and I hope this helps someone else in the future!


Rebuild or Reorganize indexes in Sql Server 2016 My database is around 8 Tb and it has 34.ndf files & tables are portioned i'm using ola hallengren script for index maintenance but job is running for more than 2 days to finish the job. is there any other index maintenance script which can bring down maintenance time?

You can consider to change the fillfactor of some of your indexes:

see Specify Fill Factor for an Index

The fact is the solution you use is already optimized, it does not consider small indexes(having only few pages) and does not rebuild all of the indexes but only those that need to. Maybe you just use 100% FF everywhere and your keys are smth like guids?

I think this article can help youin it: How to choose a good index fill factor


Schema unusable after deleting another database I just get a problem with a customer's SQLServer. I'm reading articles related to schema, owners and permissions, but I can't get it out.

I have two DB : DB1 and DB2.
I have a schema, which has the same name of it's owner (user1, which has "user1" as default schema).
This schema exists on both databases, user1 is mapped as owner on both databases.

So, some of my tables are in this format : user1.table1, user1.table2, dbo.table3...

DB1 is not used anymore : I turned it offline. Just after that, the tables [user1].[tableXX] from DB2 were not usable... Turning back DB1 online solved the problem.

How can this happens? How can I turn DB1 offline without blocking DB2?

Thanks in advance!

It looks like you'll need to change the user's default database, as it appears this is currently set to DB1, which no longer exists.

exec sp_defaultdb @loginame='user1', @defdb='DB2'

OR

ALTER LOGIN <login_name> WITH DEFAULT_DATABASE = <default_database>