texts:
I am facing the below error :

Login failed for user 'NT AUTHORITY\SYSTEM' :
Reason: Failed to open the explicitly specified database.
Reason: Failed to open the database configured in the login object while revalidating the login on the connection.

Few points here :

Login NT AUTHORITY\SYSTEM has SYSADMIN Role along with Public Role
Default Database is Master for NT AUTHORITY\SYSTEM Login .
Master DB is Online and AutoClose is False
The Database which the login is trying to access is a temporary Database and is in Single user mode for last 15 days failing with above error.
When I check SP_HELPDB it doesnot show the Database where as SYS.Database shows the database in name column ?
When I tried to set MULTI_USER it says DB is already open and can have only one user at a time and database is in single user mode .

I have checked Aaron Bertrands post on login issues and found below :

Please help me what else I am missing ?

Do I need to change the default database to the database which the login is trying to access ?

Please advice .


texts: How to specify a display name in emails from SSRS subscriptions?

texts: How can I specify a display name (e.g. "Reporting Server" <noreply@domain.com>) in the emails sent from SSRS subscriptions?


texts: Is there any option to import specific databases from all databases dump

texts: Is there any option to import only required databases from a all database back up .


texts: How can I monitor running queries in Oracle?

texts:
We use a closed source application but the built-in reporting tools aren't great so we've started using a seperate database reporting application to generate our reports.

I need to get a report copied across to our new reporting application.

I want to run the report in our old application and monitor what queries it is sending to the Oracle database so that I can copy those queries across to our new reporting application.

How can I do this?


texts: What would I look for in SQL Server to determine if a user connected with Microsoft Access?

texts: Is there a way I can check SQL Server to see who is accessing it using Microsoft Access (as opposed to a website)?  I'm using SQL Server 2008R2 with the default logging settings.


texts:
Let's say I have a table Foo with columns ID1, ID2 and a composite primary key defined over ID2, ID1. (I'm currently working with a System Center product that has several tables defined this way with the primary key columns listed in the opposite order they appear in the table definition.)

CREATE TABLE dbo.Foo(
  ID1 int NOT NULL,
  ID2 int NOT NULL,
CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED (ID2, ID1)
);
GO

-- Add a row and update stats so that histogram isn't empty
INSERT INTO Foo (ID1, ID2) VALUES (1,2);
UPDATE STATISTICS dbo.Foo;

The key_ordinal column in sys.index_columns shows the index columns in the same order they were declared in the composite primary key:

SELECT t.name, i.name, c.column_id, c.name, ic.index_column_id, ic.key_ordinal
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.[object_id] = i.[object_id]
JOIN sys.index_columns AS ic
ON ic.[object_id] = i.[object_id]
AND ic.index_id = i.index_id
JOIN sys.columns AS c
ON ic.column_id = c.column_id
AND ic.[object_id] = c.[object_id]
WHERE t.name = 'Foo';

The histogram also shows the statistics in the same order:

DBCC SHOW_STATISTICS ('Foo',PK_Foo);

However, sys.stats_columns shows the columns listed in the inverse order (ID1, ID2).

SELECT s.name, sc.stats_column_id, c.name
FROM sys.stats AS s
JOIN sys.stats_columns AS sc
ON s.stats_id = sc.stats_id
AND s.[object_id] = sc.[object_id]
JOIN sys.columns AS c
ON c.[object_id] = s.[object_id]
AND c.column_id = sc.column_id
JOIN sys.objects AS o
ON o.[object_id] = c.[object_id]
WHERE o.name = 'Foo'
AND s.name = 'PK_Foo';

Books Online says stats_column_id is a "1-based ordinal within set of stats columns," so I was expecting the value 1 to point to the first column in the statistics object.

Is this a bug in sys.stats_columns or a misunderstanding on my part?

I've verified this behaviour occurs on current builds of SQL Server 2005, 2008, 2008 R2, 2012, and 2014.

sys.stats_columns seems to reflect the order within the statistics object in other situations, for example:

CREATE TABLE dbo.Foo2(
  ID1 int NOT NULL,
  ID2 int NOT NULL,
  ID3 int NULL,
  String VARCHAR(10) NULL,
CONSTRAINT [PK_Foo2] PRIMARY KEY CLUSTERED (ID2, ID1)
);

GO

INSERT INTO Foo2 (ID1, ID2, ID3, String) VALUES (1,2,3,'String');

CREATE STATISTICS ST_Test ON Foo2 (ID3, String);
CREATE STATISTICS ST_Test2 ON Foo2 (String, ID3);

DBCC SHOW_STATISTICS ('Foo2',ST_Test);
DBCC SHOW_STATISTICS ('Foo2',ST_Test2);

SELECT s.name, sc.stats_column_id, c.name
FROM sys.stats AS s
JOIN sys.stats_columns AS sc
ON s.stats_id = sc.stats_id
AND s.[object_id] = sc.[object_id]
JOIN sys.columns AS c
ON c.[object_id] = s.[object_id]
AND c.column_id = sc.column_id
JOIN sys.objects AS o
ON o.[object_id] = c.[object_id]
WHERE o.name = 'Foo2'
AND s.name LIKE 'ST_Test%';

Here is another example where sys.stats_columns appears to return the correct data, this time for statistics on an index:

--drop table dbo.Foo3
CREATE TABLE dbo.Foo3(
  ID1 int NOT NULL,
  ID2 int NOT NULL,
  ID3 int NULL,
  String VARCHAR(10) NULL,
CONSTRAINT [PK_Foo3] PRIMARY KEY CLUSTERED (ID2, ID1)
);

GO

INSERT INTO Foo3 (ID1, ID2, ID3, String) VALUES (1,2,3,'String');
UPDATE STATISTICS Foo3;

CREATE INDEX IX_Test ON Foo3 (ID3, String);
CREATE INDEX IX_Test2 ON Foo3 (String, ID3);

DBCC SHOW_STATISTICS ('Foo3',IX_Test);
DBCC SHOW_STATISTICS ('Foo3',IX_Test2);

SELECT s.name, sc.stats_column_id, c.name
FROM sys.stats AS s
JOIN sys.stats_columns AS sc
ON s.stats_id = sc.stats_id
AND s.[object_id] = sc.[object_id]
JOIN sys.columns AS c
ON c.[object_id] = s.[object_id]
AND c.column_id = sc.column_id
JOIN sys.objects AS o
ON o.[object_id] = c.[object_id]
WHERE o.name = 'Foo3'
AND s.name LIKE 'IX_Test%';


texts: MYSQL only Show columns which allow null

texts:
I need to be able to pull the column names which allow a NULL value,  I know that

show columns from TABLE

Will give show me the table properties and whether or not the column allows null values, but is there a way to just return ONLY the columnnames which allow null.

show columns from TABLE where Null = 'YES' doesn't work, but it explains what I need to accomplish.

And of course it's easy to just pull everything and sort it out later on, but if there IS a way to do what I'm asking, I'd like to learn it.


texts:
I would like advice on what would be the best setup for our SQL Server 2005 TempDB.

At the moment our Temp DB consists of a single MDF file of 3 GB and a single log file of 120 MB on the D: drive of a 3 spindle RAID-5 array which is partitioned in to C: and D: drives. The D: drive also holds our application core programs and our reports database.

I would like to move the temp DB to its own drive which is a 2 spindle RAID-1 array with a single partition, drive F:.

I would like to create 4 x 1 GB temp db MDF files when I move it to this drive. I do not have another spare dedicated drive to split up the TempDB MDF and LDF files, so both would have to go on the same drive.

Should I leave the LDF file behind on the current drive or should I move all the files to the new one?


texts: SQL Server 2008 R2 Dirty reads - how non-atomic?

texts:
I am wondering "how dirty" dirty reads can get under a read-uncommited isolation level. I understand that rows that have been updated but not yet committed are visible, but:

Can a row appear as partially updated - that is, some of the columns are updated and some are not?
Can a single column appear partially updated. For example, if you have a varchar(4000) column that was in the process of being completely updated and assuming it actually contains 4000 chars. Can you read say 2k chars from the previous state and 2k chars from its new state?
What about varchar(max) with length >8k?

Update: After some debating, the minimal consensus is that if the column size is >8KB, dirty reads, even within the column itself, are possible.


texts: Can you optimise the choice of array for the Oracle IN clause

texts:
I have a table which holds timestamped streaming data for events, it can be created like:

create table event_data
(
    event_id integer,
    date timestamp,
    ... -- other data corresponding to the stream
);
create index ed_event_id on event_data(event_id);
create index ed_date on event_data(date);

Now this table has a lot of information and I would like to retrieve data in batches of events. To this end, I have constructed a where clause like:

select * from event_data
where event_id in (event_id1, event_id2, ...);

For the purposes of retrieving data in batches like this, does it matter whether the event_ids supplied in the array have any particular order or are part of any particular subset?

E.g. should I supply the first N events, then the next N events, and so on and so forth, according to the event_date column?

Would a random subset in the IN clause perform just as well? Why, and why not?


texts: Can I query a copy of a database without impacting performance of the primary?

texts:
I am working with a partner who has a large (150,000,000 records and growing) MSSQL database. They query and populate the database with proprietary software which I cannot easily influence. I would also like to access the data in the db but the company that provides the software are concerned that my additional querying (I may do a lot, in short bursts) would add too much load to an already straining server and cause problems for them.

What are my options here?

Is it possible to set up a server which provides a read-only copy of the data so I can query that without putting much pressure on the original database?


texts: Script to automate converting a clustered index on primary key (with foreign keys to it) column to non clustered?

texts:
What's a good way to easily convert a clustered index on a primary key column to a nonclustered index? I know how to do this manually, but it's time consuming if there are several tables which have foreign keys to the primary key as they need to be dropped and recreated.

Looking for a script or free tool to automate this.

I am using SQL Server 2012


texts:
I am using PostgreSQL 9.1.16 (and I cannot upgrade) with Hot Standby.

We have a few batches that perform some heavy writes, resulting in a lot of WAL bein produced in a short period of time.

The standby desynchronizes because the pg_xlog becomes full.

I could just increase the FS but I would like to understand.

I found out that with this version of PostgreSQL, the restartpoints are triggered only because of the checkpoint_timeout (5 min) parameter on the standby.

The primary checkpoints a lot (every 5 sec or so)  because of the WAL activity (triggered by checkpoint_segments (10) ). The result is that my standby doesn't recycle its WAL quickly enough and the FS becomes full before the next checkpoint.

Since checkpoints every 5s is "a bit too much", I tried to set checkpoint_segments to 50 on the primary and checkpoint_timeout to 30s in the standby. It's ugly but somehow the checkpoint timing are in synch and the standby doesn't "desync".

My questions are:

In 9.1.16 and before. How did you handle restart point when it was triggered only by checkpoint_timeout?
Can I do something else PostgreSQL wise than this ugly workaround ?
I don't have much experience with pg in real world. my pg_xlog FS is 5G. Is it a normal size or is it way too small ?

PS: Feel free to ask me more info, it's my first post.

PS2: the article I found about checkpoint_timeout on standby http://www.postgresql.org/message-id/AANLkTimpudRqHf8kFfQZxyMHsULYo79qwK5PCXxP21gt@mail.gmail.com


texts: Multiple results from a single query into different columns

texts:
I have 2 tables that look something like this:

Table A:

CREATE TEMP TABLE table_a (
  ID_number_X text,
  ID_number_Y int
);
INSERT INTO table_a VALUES
  ('HT59452', 123),
  ('HT59453', 123),
  ('HT59454', 123),
  ('HT59455', 789),
  ('HT59456', 101);

Table B:

CREATE TEMP TABLE table_b (
  Column_1 text,
  ID_number_Y int,
  ID_number_X text
);
INSERT INTO table_b VALUES
  ('foo', 123, null),
  ('bar', 789, null),
  ('baz', 101, null);

I am running the following query to bring ID_number_X across to Table_B:

UPDATE table_b AS B
SET id_number_x = A.id_number_x
FROM table_a AS A
WHERE B.id_number_y = A.id_number_y

This works fine, however, there are cases when there can be multiple results i.e. id_number_y can have more than one id_number_x associated with it.

How can I put those multiple results (when they occur) into additional columns?

So the result I want from the above example tables would be this:

Table B
column_1    id_number_y     id_number_x1   id_number_x2   id_number_x3
foo            123            HT59452        HT59453        HT59454
bar            789            HT59455
baz            101            HT59456

I think there could be up to 5 id_number_x for each id_number_y, so I can happily create the extra columns needed to store these.


texts: Two (almost) identical queries, but only one of them uses the index

texts:
I have a table with two columns, batch_id and study_id. There are indexes on both columns. I want to get a random row back from MySQL.

I originally wrote:

SELECT * FROM alt_join_batch_study ORDER BY RAND() LIMIT 1

but that proved slow. Here's the explain.

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    alt_join_batch_study    ALL    NULL    NULL    NULL    NULL    172235    Using temporary; Using filesort

I was surprised it wasn't using any indexes. I tried this:

SELECT batch_id, study_id FROM alt_join_batch_study ORDER BY RAND() LIMIT 1

and Lo! it was faster. And the explain shows the use of the index.

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    alt_join_batch_study    index    NULL    study_id    4    NULL    172235    Using index; Using temporary; Using filesort

Why wouldn't MySQL find the index when I do SELECT * ...?


texts: Processing occasionally fails with "Unexpected end of file has occurred."

texts:
I've got an Analysis Services database that's used for reporting on email activity. It mainly counts incoming and outgoing messages, who they're to and from, etc.

Fact table: 11,367,910 rows
Address dimension table: 386,015 rows
To-address fact table: 21,303,290 rows (used for many-to-many intermediate measure group)
Date dimension table: 9,132 rows

It's a simple structure, but there's a lot of data in it. The two measure groups have 6 yearly partitions with varying numbers of rows in them. The whole thing takes about 30 minutes to process fully.

But that's not my problem (at least I don't think it is). Seemingly randomly, the scheduled processing task will fail with the error Unexpected end of file has occurred. The following elements are not closed: root, return, ExecuteResponse, soap:Body, soap:Envelope. Line 3707, position 1. The line number can vary, but the error is the same. I've tried hunting around, but I can't find any info on what this indicates. Anybody else run into this error?


texts: How to write sql statement for following scenario?

texts:
So I currently have an sql statement:

Select room_no, category from help_desk where room_no = '133' OR room_no = '120';

and it returns:

How would I write an sql statement that would return it like this


texts: How to configure so the Oracle wallet password can be authenticated with a web based Oracle application?

texts:
Does anyone know how to configure it so that the Oracle wallet password can be authenticated with a web based Oracle application?

I’m trying to make secure LDAP connections with an Oracle web based application with secure ports. This requires that the application’s source code be able to authenticate against the Oracle wallet password. I am using ONLY the HTTP portion of Oracle Fusion 11.1 to serve these Oracle web pages.  The wallet has been created and the certificates are in place and working.

When we used the Oracle 10g HTTP server we would just make an entry in the SSL.conf file;
SSLWalletPassword encrypted or unencrypted Wallet password   .
But when I try it with Oracle Fusion 11.1, the HTTP server won’t start. I tried several versions of the above, but none worked. I also tried to solve the problem by modifying the opmn.xml file and changed the script to;

But that didn’t work any either. When we try to authenticate with secure LDAP ports our web applications fail.

Does anyone have any ideas how to enable this with the HTTP portion of Oracle Fusion 11.1? Thanks.

Kirk

  Edited to add more information per
  here
  ~ Richard

I'm quite certain that I created the wallet correctly and that the certificate are correct. I've done it before, though not with Fusion 11.

I'm really starting to suspect there are network denial or port issues.  I do a Utl_Http.Get_Detailed_Sqlerrm and will get an

ORA-24247: network access denied by access control list (ACL)...

message.

But the system guys say that all necessary ports are opened and access is okay. But I can make an entry in the DADs.conf file to "point" to another schema on another server (though 10G) and I have no "can't open wallet" issues.


texts: Database migration program, that can analyze source and target db, and tell diff

texts:
I'm looking for a program that can help with migration of both database schema and data from a source db, to a target db.
And it has to be able to do it while target db stays live, all the time. So clearing target db, and doing a full migration, is not an option.

We usually use Navicat SQL Server for this, but I'm just wondering, if there is any alternatives out there.

It would also be nice, if the program can make a database diagram, for the SQL servers that does not support the way SSMS does this. (like azure db's)
And unfortunately SSMS isnt up to this task.

What would a working alternative be to using this program?


texts: MySQL: What is SQL mode and why we use it

texts:
If in an application, if we execute sql_mode=mysql4.0 as a first query in my application but using MySQL 5.1 in my application then what will happen?

Will my system use MySQL 4.0 or MySQL 5.1 version features?  What is
SQL mode in a RDBMS?  Why we should use this?
On which basis, we should set its value?


texts:
Can mongodb take advantage of an index partially? I'm not talking about using a "partial index" ( https://docs.mongodb.com/manual/core/index-partial/ ), but using a regular index partially.

For example, let's say I create this index that has two fields:

    eventLog = database.getCollection("event_log", EventItem.class);
    eventLog.createIndex(Indexes.ascending("userId", "eventDate"));

What happens if I try to find elements for a given userId ONLY e.g.:

find({userId:23423})

Will mongodb take advantage of the index that begins with the userId field? Or do I have to create another index to speed up the query, e.g.

    eventLog.createIndex(Indexes.ascending("userId"));


texts:
I have a table with three columns: id, sort, name in PostgreSQL 9.5 database.

I need to select all rows starting from sort=1 and before the first break (ordered by sort).

For example I have:

id | sort | name
1  |   1  | 'ss'
2  |   2  | 'ss'
3  |   3  | 'ss'
4  |   4  | 'ss'
5  |   7  | 'ss'
6  |   8  | 'ss'

I want to select rows with id=1,2,3,4.

As you can see, there is a break in the sort sequence (4-7). So I only need the rows before this break.

How can I do this?


texts:
What is the best method to generate a Custom Auto-Generated Sequence with SQL Server?

Here is my requirement:

My application is hosted on 10 independent servers which are not
interconnected.
Each instance should generate a sequence number, and all these data
will be merged to all databases using SQL Server's merge replication.
The sequence generated should be unique across all the servers.

To achieve this, I have created a function to prefix a server id to the sequence number and used this as a computed column. But when I add data, I am getting the following exception:

  "Maximum stored procedure, function, trigger, or view nesting level
  exceeded (limit 32)."

Here is my table and function. Kindly suggest a better method.

create table Customers
(
    CID int identity not null primary key,
    CustomerName varchar(50)
)

ALTER function [dbo].[NextCustomerNumber]()
returns bigint
as
begin
    declare @lastval bigint
    declare @serverId int
    declare @newval bigint
    set @lastval = (select max(CustomerNumber) from Customers)
    set @serverId= 1 -- Server ID pulls from some settings
    set @newval=CONVERT(bigint, (CAST(@serverId as varchar(2))+CONVERT( varchar(10),(RIGHT(@lastval,(LEN(@lastval)-LEN(@serverId)))+1))))
    return @newval

end

Alter table Customers add CustomerNumber as dbo.NextCustomerNumber()


texts: What is a "partitioned view"? I googled but could not get a satisfactory result.


texts:
So I have this pgbouncer config:

[databases]
my_db = host=10.10.10.10
my_db_with_conn_limit = host=10.10.10.10 dbname=my_db pool_size=55

max_client_conn = 300
default_pool_size = 65
reserve_pool_size = 5
reserve_pool_timeout = 1

And the goal is to limit the number of connections from a specific app - when the db name is database_with_conn_limit, only 55 connections are allowed.

Basically, the apps are identical, but I want one to be limited in connections and the other one grab as many as it wants.

Is this a correct setup?
Or should I specify pool_size for my_db, too, giving it all Postgres has minus 55?


texts: Can mysqldump dump triggers and procedures?

texts:
Is there any way of making a mysqldump which will save all the triggers and procedures from a specified db?

Some time ago I read that mysqldump will also save my triggers, but it doesn't look like it. My second related question is how can I check in a sql file if triggers exists?


texts: How to get database name in a server-level DDL trigger

texts:
I'm creating a Server level trigger that fires after a create table statement, and I want to print the database name and table name.

CREATE TRIGGER LogTempTables
ON ALL SERVER
AFTER CREATE_TABLE
AS
BEGIN
SET NOCOUNT ON;

       DECLARE @EventData XML = EVENTDATA()
       DECLARE @TableName NVARCHAR(50)
               = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(50)')
       DECLARE @Database NVARCHAR(50) = DB_NAME()

       PRINT @Database
       PRINT @TableName
END
GO

If I create a table in database ABC123 it prints:

  master
  TestTable


texts: What is the proper way to design this database?

texts:
So, I'm not a database expert (or really have much experience at all other than my college classes) and I need someone to point me in the right direction on how to design this problem. To be very clear, I'm out of school and I have my degree. This is NOT a homework problem.

I work as a software engineer at a company that manages other company's warehouses. I built a system that tracks our associate's productivity by the hour and I need the information to be stored in a database. Previously I have used Java and simply serialized classes and stored the data in files on our server but the problem is I've had to change so many data types to account for more information we wish to track and I lose the serialized objects after any significant changes. Anyways, I decided to just use a database to store this stuff but really am having a hard time with it's design.

So the information I need stored is as follows...

Associates
Jobs (Associates can work multiple jobs throughout the day and each job has a different goal average they need to reach)
Each Associates' Total Work Hours for a given day
Need to save all this for each separate day (this is the hardest part for me and I have a mental block on how to separate all that by day)

A little more info: The way I designed this in code was like this.

HashMap<LocalDate, HashMap<Employee, DayData>> data = new HashMap<>();

So as you can see each day has a list of employees each with a "DayData" object associated with them.

The DayData object contains total day work hours and a list of WorkJob.

WorkJob stores the name of the job, the unit per hour the associate hit, and the units the employee packaged.

I hope this is clear as mud. I can explain anything further if needed. I really need someone to point me in the right direction with this. Please and thank you.


texts:
I work as full stack developer, but never really spent time on Database designs. I had written code to retrieve data out of DB, but never ever created a schema from scratch. So, I thought of doing some side project to understand how database design involves.

I had decided to create a Resume Builder Application. Here are some of the features that I had thought about:

User can sign up to create his/her own resumes.
User can have multiple resumes. (only applicable for paid users, for free users only one resume)
Resume can be built from the UI (from available components) and user can save it as draft.
Resume can be published via url.
Published resumes stats like visitors count needs to be calculated.
User can delete the resume.
User can upload his/her pic.
User can add description about themselves.

Given all these, I have come up with this schema:

https://ibin.co/3CoJEfeQTuAp.png

Here are the questions I have:

I have a general knowledge on RDBMS, so my schema targets databases like Oracle, MySql etc. But can we choose NoSql for this problem? If so why?
Is my schema correct? Does it work on large data with ease?
I thought of saving the resume layouts (layouts for each resume is decided by the user via say UI) in JSON format. Is it a good practice to use JSON datatypes?
ResumeData table serves to store the user data for each resume. Is it a good to have a separate table for the same?
If the answer for the first question is RDBMS, which database you will prefer? I had decided to use PostgreSql.

Thanks a lot for spending time on answering these questions.


texts: Does it make a difference to add the DEFAULT keyword in CREATE TABLE?

texts:
The MySQL manual says:

CREATE DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

ALTER DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

  The database character set and collation are used as default values for table definitions if the table character set and collation are not specified in CREATE TABLE statements.

Someone made a point in the comments, saying:

  It's not at all clear what difference it makes to specify DEFAULT or to omit it. So if you specify DEFAULT then that charset becomes the default for tables for which you don't specify an explicit charset. But what if you omit DEFAULT? Does that prevent tables from using a different charset from the database? This should really be explained above.

Does it make any difference if the DEFAULT keyword is specified, or are CHARACTER SET and DEFAULT CHARACTER SET just exact synonyms?


texts:
I'm using SQL Server 2005.  I have two tables that contain aggregate information.  The information is constantly being updated, generating almost 5GB of log data a day.  (That's larger than the entire database!)  I'd like to disable logging on these tables, as rolling back is not really necessary.  I would however like to keep logging on the other tables in the database.

Is it possible to disable logging on certain tables within the database?  If not, can I place the two tables in the same schema, then disable logging on the schema?  Is the only option to move the two tables to a separate database, and disable logging there?

Update:  I guess I'll explain why I really don't need to log the activity on these tables.

The two tables are filled with GPS data, so they get quite large.  The first table is capturing raw locations from six Android tables in the field.  New data from each of the tablets comes in every 5-10 seconds.  That information is then aggregated as locationA, locationB, travelTime.  The goal is to ultimately have the shortest travel times between all locations, based on actual driving data.  The data is only for a small city, and only accurate to four decimal places, so it is manageable.  However, as new raw data comes in, there are slower travel times that need to be updated, and new ones that need to be inserted.

Once the raw data is aggregated, it is purged.  We're not going backwards to longer travel times, so that is why rolling back does not matter so much in these tables.


texts: Streaming replication setup, sane setup?

texts:
Not being a DBA as main profession, I would like to know if the following assumptions are true. I have been searching the net for days and I think I have understand it correctly, but I would like to hear the response from people when faced with my exact configuration.

I want to utilize streaming replication, which I have a working setup for.

I have turned off WAL archiving, since I believe that wal_keep_segments will contain enough backlog to get a slave up to date if necessary (700, you might think this high, I just calculated how much "buffer" I could spare for replication backlog, please tell me if this is the wrong approach).

I have the same configuration on master and slave, which includes the following:

wal_level = hot_standby
hot_standby = on  #(this is ignored on master, as I understand it?)

Now, the main question is the first entry here, I could theoretically set this to minimal on the slave, correct? However, I'm thinking for ease of failover, I should keep it this way?

Finally, I never specify restore_command in my recovery.conf, because as I have understood it, this is for log shipping (when rsyncing the WAL files between the two nodes or similar), or if I were to bring back a slave node from a long disconnect and archive_mode was on. 12 GB of WAL segments should be good for at least a week (unless I do some big restore of a DB), and I assume that all segments that are in pg_xlog will be streamed to the slave upon reconnect if it is behind?

And finally, what would be a good way to surveillance that the replication works as it should? I know about repmgr, but it would be nice to be aware of how the surveillance would consider the slave to be out of date.


texts: In Microsoft SQL server, how can I add/change the default on an existing table column using T-SQL?


texts:
I need to get some stats out of our MySQL-DB and wonder if there is a faster way in doing this.

Basically I need info about user registration for any given month before including seperation by attributes.

e.G:

SELECT count(*) AS c
FROM `users`
WHERE `date_created` LIKE '2015-10%'
  AND `country` = 'DE'

This would return a number for October 2015. How could I alter this statement to return results for all months within one statement?

I would also be interested in adding something like AND confirmed = 1 as separate result.

Is this possible with just one MySQL-Statement?


texts:
We have got a procedure on SQL Server agent jobs and this job started to hang on transaction for many hours with wait stats CXCONSUMER and never complete. The problem is after we noticed that it was hanging we kill the job and started manually during and the job was working fine and completing. But right now neither starting manually or on sql agent job seems never completing and hanging on waits with CXCONSUMER.

According to sp_who2, sp_whoisactive and sql server profiler there are no active locks it is waiting for. It keeps yielding CXCONSUMER and it's processor time and does basically nothing.  Here is the execution plan after 3h -

And the other symptoms is the tempdb is unaccesible through properties when this job runs I get this error "Property SpaceAvailable is not available for Database '[tempdb]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights" despite there are available space on tempdb.

I will appreciate your help in this matter.

Thank you and Best Regards,


texts:
I have a table called assignments. A user will request an assignment from an API endpoint. I need to ensure that an assignment can only be handed to one user. There will be multiple users requesting their next assignment. I have a column called locked_by, although I am not sure that marking this column will ensure that one and only one user can select that row.

What strategies are used in this scenario?

I am using mysql and SQLAlchemy

Edit: Here is a code snippet that does not work like I expect it to. Both queries return the same row.

BEGIN;
SELECT *
FROM task_queue_contents tqc
WHERE tqc.locked_by_id IS NULL
LIMIT 1
FOR UPDATE;

BEGIN;
SELECT *
FROM task_queue_contents tqc
WHERE tqc.locked_by_id IS NULL
LIMIT 1
FOR UPDATE;

I would like to prevent that first row that has been read from being selected by any other user (since only one user may hold a task)

MySQL 2nd Gen 5.7 on Google Cloud Platform


texts:
What I am trying to achieve is run both queries (below) at the same time.
From one table rvsessions I need to get the count for the total amount of sessions(which is achieved by the number of times the rvsessions_user_id appears in the table) and the number of sessions correct (correct = 1 and incorrect = 0)

<?php
$query = "SELECT * , count(*) AS rvsession_user_id FROM rvsessions
GROUP BY rvsession_user_id ORDER BY rvsession_user_id DESC";

$query = "SELECT * , count(*) AS rvsession_correct FROM rvsessions
WHERE rvsession_correct = 1 GROUP BY rvsession_user_id";

$select_all_user_sessions = mysqli_query($connection,$query);
$total_user_session_count = mysqli_num_rows($select_all_user_sessions);

while($row = mysqli_fetch_assoc($select_all_user_sessions)) {

$rvsession_username  = $row['rvsession_username'];
$count_user_sessions = $row['rvsession_user_id'];
$count_sessions_correct = $row['rvsession_correct'];

echo "
";
           echo "$rvsession_username	";
           echo "$count_user_sessions	";
           echo "$count_sessions_correct	";
echo "
";
                       }
?>

Each individual query runs fine and produces the correct results.
When I run them together both counts are not producing the correct results.
When I try to embed one query in the other as a nested sub-query, I get a syntax-error. I am at a loss... What would be the correct syntax for this?


texts:
I have a Postgresql 9.3 with a "mother" table containing items, and a number of "child" tables containing parts of different kinds (e.g. item "AAA" is composed of 1000 parts "P1", 800 parts "P2", 40 parts "P3", ...).

The items table contains ~500k rows, and each row references between 10 and 10000 rows on each part table, meaning each part table can contain several million rows.

Each table has a _id_ primary key, and each "child" table has a foreign key pointing to item._id_ (with an index) and UPDATE/DELETE CASCADE so that all parts are deleted when an item is deleted. Some parts reference each other as well.

SELECT on item and parts tables is quite fast (SELECT * FROM p1 WHERE item_id=? <1000 ms) but DELETE is awfully slow: it took a full 24h just to DELETE FROM item WHERE _id_=?.

I tried to delete sequentially from each parts with DELETE FROM p1 WHERE item_id=?: the first deletion took 3h but the next ones took a few ms only... Can it be some inefficient cache-fetching? In case statistics were a problem, I ran a VACUUM ANALYZE on the whole database (which completed in a couple hours) but deletion still is painfully slow. Items are added at a pace of 10/h so I will eventually run out of disk space in the near future (~100 GB left now).

I ran EXPLAIN ANALYZE DELETE FROM item WHERE _id_=?:

Delete on "item"  (cost=0.42..8.44 rows=1 width=6) (actual time=10375838.942..10375838.942 rows=0 loops=1)
  ->  Index Scan using "item_pkey" on "item"  (cost=0.42..8.44 rows=1 width=6) (actual time=0.043..0.054 rows=1 loops=1)
        Index Cond: ("_id_" = 29878)
Total runtime: 10375838.996 ms"

I'm don't know much about those costs and their meaning; I just see it is using the index, but I can't explain why it takes so long. The deleted items are the old ones, barely accessed (if at all), so I could understand cache miss.

Is there any parameter to tune to speed it up? I can also wait for maintenance time to do some things like dropping indexes, deleting rows, rebuilding indexes, as we can lock down the database (but then, what to do?), but I would prefer to be able to do it live, if possible. If I have to choose, the database should be optimized for fast INSERT and SELECT over fast DELETE.

Here is an excerpt from the table definitions:

TABLE item(
    _id_ serial,
    ...
    CONSTRAINT item_pkey PRIMAY KEY (_id_)
)

TABLE p1(
    _id_ serial,
    item_id integer,
    ...
    CONSTRAINT p1_pkey PRIMARY KEY (_id_)
    CONSTRAINT fk_item FOREIGN KEY (item_id)
        REFERENCES item(_id_)
        ON UPDATE CASCADE
        ON DELETE CASCADE
)
CREATE INDEX idx_p1_item ON p1 USING btree(item_id)

TABLE p2(
    _id_ serial,
    item_id integer,
    p1_id integer,
    ...
    CONSTRAINT p2_pkey PRIMARY KEY (_id_)
    CONSTRAINT fk_item FOREIGN KEY (item_id)
        REFERENCES item(_id_)
        ON UPDATE CASCADE
        ON DELETE CASCADE
    CONSTRAINT fk_p1 FOREIGN KEY (p1_id)
        REFERENCES p1(_id_)
        ON UPDATE CASCADE
        ON DELETE CASCADE
)
CREATE INDEX idx_p2_item ON p2 USING btree(item_id)
CREATE INDEX idx_p2_p1 ON p2 USING btree(p1_id)


texts: MongoDB Zone Sharding without min & max key

texts:
I have a database structure that is similar to the following structure.

User collection

Sub collection0, with userId
Sub collection1, with userId
Sub collection2, with userId

and so on

Example:

User: {_id, firstName, lastName, ...}
Blog: {_id, userId, ...}
Post: {_id, userId, ...}

The term sub collection is not appropriate I guess. In the above example, in one zone, I like to have documents related to only one user. For example, If there is an user X, then in a zone named X, there must be only one user document in users collection, which is X's document, the blogs & posts collections in zone X must only have X's blogs & posts. This use case is outlined here.

Based on the above documentation, I've setup zones. For shard tag range, min & max keys must be provided. In my use case(user id, blog/post ids), it is not possible to define range. Also for user collection, there is no range at all, only one user must in one zone.

I am able to think of hacks like, defining non overlap-able ranges in the application, which I tried & works too. But, is there a better way to do this?

Thank you!


texts: Foreign key reference to column that is no primary key

texts:
I know, foreign keys should uniquely identify rows and therefore reference a primary key.

However, consider my example: There are 4 pre-defined categories, and for some combinations of values for these categories, there is a hierarchy of modules. The hierarchical structure is implemented by a parent module column.

My approach would be to implement it in a single table with these columns:

category 1 (e.g., region)
category 2 (e.g., kind of school)
category 3 (e.g., class)
category 4 (e.g., subject)
module title
parent module title
and a primary key consisting of all categories + the module title.

I would want the parent module title to reference the module title column. Now because a foreign key needs to reference the whole primary key, I would need to insert columns like parent category 1, parent category 2, parent category 3, parent category 4. But the parent module always has the same values as the child modules in all 4 categories, so these columns would be redundant.

It would be ideal if I could write something like this in my table declaration:

--- fictitious SQL! ---

create table modules (
  ...,
  module_title varchar(50),
  foreign key parent_module_title varchar(50)
    references modules(module_title) where
      modules.category1 = category1 and modules.category2 = category2 and
      modules.category3 = category3 and modules.category4 = category4
  )

Or something like:

--- fictitious SQL! ---

  ...
  parent_module_title varchar(50)
    check (exists
    (select * from modules where modules.module_title = parent_module_title))
  ...

These are both in fictitious wishfulSQL (the latter throws cannot use subquery in check constraint), but is there a way of achieving my aim? I'm using Postgres if that matters.


texts: What is the persistent_version_store table?

texts:
What is the purpose of the MS Shipped table: sys.persistent_version_store?

Why do I want to know

Primarily curiosity, partly because it may relate to an issue waiting to bite us...

We're running a SaaS instance of MS Dynamics 365 for Finance and Operations.  This has very limited tools for monitoring performance.  Whilst trying to find something of meaning I discovered that this table had an insanely high row count (~75,000,000).  That seems worrying. It's possible that this is OK, but MS have included it on their analysis tools, meaning that this eclipses all other information on the graph (since it has a linear scale) making the graph pointless.  I want to understand what this table is so I can determine if it's something to be investigated, or if it's just bad design to have included it on the monitoring tools.

Why not read the documentation

Since it's an MS table it should be documented somewhere.  However, searching persistent_version_store gives 1 result, and it's in Chinese.  Searching for Persistence Version Store does better; but it's unclear whether this is the same thing / there's not much documentation on what it is; only how to fix issues where it's reached its maximum size.


texts: What are the compression options for storing large amounts of text in Postgres?

texts:
I need to store large amounts of text in Postgres, mainly command logs and the output of long running commands and need to know if there are some settings that could help compress the data transparently with a tool like zlib, or some other native space saving methods.

The data is primarily read only data, so writing is not a problem.


texts:
I have an 1:m association.

symbols table has many rows in company_key_statistics table. company_key_statistics has column createdAt which is timestamp indicating when row has been created.
I need to join latest symbols with company_key_statistics, but I need only latest company_key_statistics.
For example I need to get ORCL and MSFT symbols and only their latest company_key_statistics.

So far I have tried this.

SELECT `symbols`.`id`,
       `symbols`.`symbol`,
       `statistics`.
       `marketCapitalization`
FROM   `symbols`
       LEFT JOIN (SELECT `s`.`companyId`,
                         `s`.`marketCapitalization`
                  FROM   `company_key_statistics` AS `s`
                  WHERE  `s`.`companyId` = `symbols`.`id`
                  ORDER by `createdAt`
                  DESC LIMIT 1) AS `statistics`
ON     `symbols`.`id` = `statistics`.`companyId`
WHERE  `symbols`.`symbol` IN ('ORCL', 'SNAP');

But unfortunately I found that I can't use columns from parent query in JOIN subqueries.

How can I achieve to this?


texts: PostgreSQL last_vacuum is null for some tables


texts:
I am looking for MySQL High Availability with huge transactions. I prefer to go with InnoDB Engine.
I prefer to use MySQL Master - Slave Replication. However, this option provides us Manual fail over of Application from Master to Slave.
QUESTIONS

What could be the maximum difference time to have Sync between Master and Slave ?
Is there any option of having High Availability other than this approach ?


texts: SQL Server 2008 R2 Most Efficient Full Text Search Field Type

texts:
In SQL Server 2008 R2, what is the most efficient column type to store data enabling full text search?

I can currently see two options, nvarchar and varbinary.

Which one performs better and why?

EDIT

If, for example, at the moment all I need is text, but in the future it might change, will there be a performance hit if i use varbinary?


texts:
I did upgrade the MySQL server from 5.5 to 5.6
I am trying to take dump for a table like this
 mysqldump -uroot -p dba admin > D:\admin.sql

But it gets following error

mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1':
You have an er  ror in your SQL syntax; check the manual that
corresponds to your MySQL server v  ersion for the right syntax to use
near 'OPTION SQL_QUOTE_SHOW_CREATE=1' at line  1 (1064)

edit:
mysqldump verison is 5.5.30.


texts: Trying to create a collation on Postgres 9.6; a nonsensical error message

texts:
I'm running Postgres 9.6.1 on a Debian-based Docker image. My default locale is en_US.utf8. I'm trying to create a collation of type ja_JP.utf8. I have the Japanese locale installed as demonstrated by the command locale -a:

$ locale -a
C
C.UTF-8
en_US.utf8
ja_JP.utf8
POSIX

However, when trying to create the Japanese collation in psql, I get the following, nonsensical error message:

# create collation ja_JP (LOCALE="ja_JP.utf8");
ERROR:  could not create locale "ja_JP.utf8": Success

Googling a bit finds me a bug report from over two years back: https://www.postgresql.org/message-id/20140525154511.GA21233%40alap3.anarazel.de

It looks the same, and judging from my symptoms, it isn't fixed yet! Is there any way to work around this bug?


texts:
There are two tables: contract (with primary key contractid) and contractupdate (with field contractid referring to a contract).

contractupdate also has DATE field startdate and TINYINT field day.

I want the list of contract IDs ordered by the value of field day in the latest (having maximal startdate) contract update which refers to this contract.

MySQL.


texts:
I'm trying to restore a db dump from PostgreSQL 9.2 to PostgrSQL 8.4
(from separate machines, both are running Windows 7).

I followed the instructions in this answer:
Use pg_restore to restore from a newer version of PostgreSQL.

However,the screen keeps writing

invalid command \N  invalid command \N
invalid command \N  invalid command \N
invalid command \N  invalid command \N

How can I resolve this issue?


texts:
We have a DB link between 2 Oracle 11.2 databases.

On the production system, we noticed that when an UPDATE statement of a remote table fails in an PL/SQL procedure (started as a scheduled job), the error message is lost.

Example code:

UPDATE remote_table@our_db_link SET ...
exception
when others then
  senderror('Error: '|| sqlerrm || chr(10) || dbms_utility.format_error_backtrace || chr(10) || sys.DBMS_UTILITY.format_call_stack);

The senderror procedure saves the supplied text to a log file.

There we see:

Error: ORA-01536: space quota exceeded for tablespace 'TS1'
ORA-02063: preceding line from OUR_DB_LINK
ORA-06512: at "X_TEST.X_PACKAGE", line 64

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x89b0bca0       114  package body X_TEST.X_PACKAGE
0x7d5b27e8         1  anonymous block

This is what we get on the test system. On the production, the root cause is missing, so the text is:

Error: ORA-02063: preceding line from OUR_DB_LINK
ORA-06512: at "X_TEST.X_PACKAGE", line 64

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x89b0bca0       114  package body X_TEST.X_PACKAGE
0x7d5b27e8         1  anonymous block

The DB version is 11.2.0.4.

Any idea what could cause this?


texts: Why can't I use newid() in a user-defined table-valued function?

texts:
I recently found out that SQL Server 2005 doesn't allow the use of newid() within user-defined functions. Why is this?

I have an alternate solution that suits my needs, so I'm not looking for ways to get around this. I'm curious why the designers would make this decision.


texts: PostgreSQL: Using schema and table names from other tables?

texts:
Consider the situation where I have all my schema names in one table and all my table names in another table.

Is it possible to do something like in the following (pseudo-code)?

SELECT value FROM (SELECT schema_name FROM schemas).(SELECT table_name FROM tables)

Or do I have to break up the query into three SELECT's?


texts: What is Required to use Oracle Performance Manager to monitor SQL Server?

texts:
I am working with a customer who has several SQL Server and Oracle DBMS server instances.  My customer would like to setup up performance data collection, monitoring and alerting for all of their databases.  My strength is in SQL Server and I know lots of products/tools for doing this there, but there seems to be very few options for one tool or product that can do both Oracle and SQL Server at the same time.

One option that has been suggested is Oracle Performance Manager, which apparently can also monitor SQL Server.  I have been trying to read up on it, but there's a lot of doc out there and I am not very familiar with it's implicit context (the Oracle Product suite(s)) so I am having trouble filtering it down to the parts that I need right now to make a recommendation to my client.

Here are the things that I am trying to figure out:

Does Oracle Performance Manager fulfill any/all of these Performance functions for Oracle databases?:

a. Performance Data Collection (ie., retained in a database)

b. Performance Monitoring (live views of performance)

c. Performance Alerting (live notification of exceeding configurable thresholds)

d. Performance Reporting (this would be nice but not necessary, I know how to write a report from data tables, even in Oracle)
Which can Oracle Performance Manager fulfill for SQL Server databases?
What specific Oracle Product(s) does my customer need to have or purchase in order to get these functions?
Are there any other requirements or restrictions that we should know about?

Any help on this is much appreciated.


texts: A query that lists all mapped users for a given login

texts:
When looking at the properties of a particular login, it's possible to see a list of users mapped to that login:

I profiled SQL Server Management Studio (SSMS) and I see that SSMS connects to every database one at a time and retrieves information from sys.database_permissions

Is it possible to write a single query that retrieves the user mapping information shown above or am I forced to use a cursor or sp_MSforeachdb or something like that?


texts: Permission to view execution report in SSIS Catalog

texts:
We are currently using SSIS 2012. Is there any way for a user to view execution reports under SSIS Catalog without being ssis_admin or sysadmin?

This is for production environment and we don't want people to manipulate SSIS Catalog projects.

Thanks!


texts: MySQL slow importing on hyper-v virtual server compared to host

texts:
Database dump file:

6 MB size
14k rows
10 tables

Server specs (Ubuntu 20.04, virtual (hyper-v)):

OS: Ubuntu 20.04.1 LTS
MySQL version: Ver 8.0.21-0ubuntu0.20.04.4 for Linux on x86_64
((Ubuntu))
4 core, 2 GB RAM
100 GB storage

Host MySQL

Windows server
MySQL version: 8.0.17 - MySQL Community Server - GPL
HDD Storage
8 GB RAM

Importing dump to the database on the host machine took 1-3 seconds, importing on the guest server 5-6 minutes.
iotop reports maximum write speed 5 MB/s while importing.
Refreshing database on phpmyadmin, i can see all row is imported after 1 minutes, the rest of the time is just altering table:
ALTER TABLE `table`
   MODIFY `ID` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

Import command:
mysql -u user -p db < /home/user/dump.sql

tried:

mysql-server reinstall on guest machine
disable unique checks with UNIQUE_CHECKS=0;
disable foreign key checks with FOREIGN_KEY_CHECKS=0;


texts:
I have Survey results given to me in a massive table. (300+ columns)

A list of questions is provided in a table, and the Survey results in another.

Questions.

+-----+-----+---------------------------------+
| idx | QID |              QDesc              |
+-----+-----+---------------------------------+
|   1 | Q1  | Are you alive?                  |
|   2 | Q2  | Do you like pizza?              |
|   3 | Q3  | Do you like pineapple on pizza? |
+-----+-----+---------------------------------+

The answers from each person surveyed are entered on 1 line, each question/answer in it's own column.

+-----+----+-----+-----+
| idx | Q1 | Q2  | Q3  |
+-----+----+-----+-----+
|   1 |  1 | Yes | No  |
|   2 |  0 | No  | No  |
|   3 |  1 | Yes | Yes |
+-----+----+-----+-----+

I want to create a view on this which will be used in Excel Pivot tables. So I want the quest text in each row. Something like:

+-----+----------------+----------+--------------------+----------+---------------------------------+----------+
| idx |     Q1Desc     | Q1Result |       Q2Desc       | Q2Result |             Q3Desc              | Q3Result |
+-----+----------------+----------+--------------------+----------+---------------------------------+----------+
|   1 | Are you alive? |        1 | Do you like pizza? | Yes      | Do you like pineapple on pizza? | No       |
|   2 | Are you alive? |        0 | Do you like pizza? | No       | Do you like pineapple on pizza? | No       |
|   3 | Are you alive? |        1 | Do you like pizza? | Yes      | Do you like pineapple on pizza? | Yes      |
+-----+----------------+----------+--------------------+----------+---------------------------------+----------+

I feel like the answer should be a unpivot/pivot, but the thought is making my head explode. Over time i'll be loading more results and the questions and result columns could be added to, so i want it to be dynamic.

Is there a simple way to do this?


texts: Keep transaction log size under control during batch processing

texts:
tl;dr

I think my verbosity has obscured the real question I'm asking here, so I apologise for that. My main issue is that the checkpoint command appeared to be working for at least 800 iterations of the loop. The log size remained static at around 1GB during that period. Then, sometime overnight the checkpoint evidently failed to free the space within the log file and it consumed the drive. No database backups where scheduled. I'm at a loss as to what caused this behaviour.

Is there anything I can do to ensure that the log space is freed on completion of each loop iteration under both FULL and SIMPLE recovery modes?

I am trying to alter the schema (and make some data changes) on an extremely large table without causing any disruption to applications and services that require this table and also to keep the transaction log at a manageable size.

So I have decided to process the table data within a loop, either taking a log backup or issuing a checkpoint statement depending on the recovery model at the end of each iteration. Each loop migrates some data into a new table (both old and new tables are UNION ALLd behind a view to ensure availability while the data is only partially migrated).

However the transaction log is blowing out on the development server under SIMPLE recovery mode, but in a very odd way. I monitored the log for the first ~800 iterations of the loop (out of a total of ~3000) before going home and the log remained at a size consistent with the number of records it was processing per iteration and so the method appeared to be working. However, on arrival the next morning, I noticed that the log had exploded and consumed the entire disk with only 400 iterations to go.

I do not understand why the process appeared to work initially, and then failed at some seemingly arbitrary point. I would have expected the log to grow steadily if my logic was wrong, but this did not happen.

I truncated the log so it started at ~500KB. It grew to around 1.2GB after the first loop and stayed at approximately that size for the first few hundred iterations. Based on that it seemed as though the method was sound.

My script is summarised with the following pseudoish code:

select distinct [fk] into #tmp from [huge_table];

while exists (select 1 from #tmp) begin
    select top (1) @id = [fk] from #tmp;

    -- Actual query is more complicated...
    delete [huge_table]
    output deleted.* into [new_table]
      from [huge_table] a
     where a.[fk] = @id;

    -- Keep log down
    if @recovery = N'SIMPLE'
        checkpoint;
    else begin
        set @logpath = '\\path\to\backups\log_' + cast(@c as varchar) + '.trn';
        backup log 'database_name' to disk = @logpath;
        set @c = @c + 1;
    end

    delete top (1) #tmp;
end

My choices at this point appear to be either a) fix the problem somehow, or b) assume that the same degenerate behaviour will not occur under FULL recovery mode in Production. I'm naturally repulsed by option B, but not sure how to go about option A.

How can I ensure that the size of the transaction log behaves itself. Is there something I am missing?

My only idea currently is to append to the end of the loop something like:

if @logsize > 10GB (or some other arbitrary number)
    dbcc shrinkfile (N'database_log_file', 0, truncateonly);

But, aside from being awful in and of itself, I can't think of any way to translate this approach to a database under FULL recovery mode, so it's really not an option for me at this point.

EDIT: Also, I checked the log_reuse_wait_desc column after the failure but the result for the database in question was NOTHING, so that provided no enlightenment. I was able to do a log truncation via dbcc shrinkfile after the fact, so the log was not in any state that prevented truncation.


texts:
I have following code.

select class.name as classname, class.hours as hours, teachers.first_name
as teacherFirstName, teachers.last_name as teacherLastName
from teachers,class
left join class_teachers cl on class.id = cl.class_ref
left join class_teachers cl1 on teachers.id = cl1.teachers_ref

I got following error.

#1054 - Unknown column 'teachers.id' in 'on clause

When I interchanged class and teachers the error was:

#1054 - Unknown column 'class.id' in 'on clause

Do you guys have an idea whats the problem?

edit

CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `hours` int(3) NOT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `class_teachers` (
  `class_ref` int(11) NOT NULL,
  `teachers_ref` int(11) NOT NULL
)

CREATE TABLE `teachers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
)

class_teachers is an intermediary table. This database should make possible that a class should have multiple teachers. For example PE class can have separate teachers for boys and girls. The query should show list of class with teachers names, class name and number of class hours

class table

id  name     hours
1   science     3
2   math        5

teachers table

id  first_name  last_name
1   Ronald      Manlapao
2   hello       goodbye
3   elise       elise
4   me          me

class_teachers table

class_ref   teachers_ref
2                   2
1                   1
1                   2

I tried the following:

select c.name as classname, c.hours as hours, t.first_name as teacherFirstName,
t.last_name as teacherLastName
from teachers t, class c
left join class_teachers cl on (c.id = cl.class_ref and t.id = cl.teachers_ref)

But still the got same error.

problem solved using:

select class.name as classname,
       class.hours as hours,
       teachers.first_name as teacherFirstName,
       teachers.last_name as teacherLastName
from class_teachers cl
left join class on class.id = cl.class_ref
left join teachers on teachers.id = cl.teachers_ref

edit - moved to separate answer by OP

updated class_teachers table to class_teachers_section to include section. the idea is a class can include different sections with different teachers. for example a grade 1 PE class can include all sections (lets pretend 3 sections) ... teacher for boys will be different for girls

select class.name as classname,
   class.hours as hours,
   teachers.first_name as teacherFirstName,
   teachers.last_name as teacherLastName,
   section.name as sectionName
 from class_teachers_section cls
 left join class on class.id = cls.class_ref
 left join teachers on teachers.id = cls.teachers_ref
 left join section on section.id = cls.section_ref
 where section.name = 'grade 1'


texts:
I am trying to import data into postgres. Data fields are comma separated, strings may contain commas, in which case those are escaped:

Data structure:

create table v (a varchar);

Data file:

bus
'Gat\,\\e\'way_MQB'

Import command (run under Linux from bash)

cat data.csv | psql -d database -c "copy v (a) from stdin with delimiter ',' escape '\\' CSV header"

Error:

ERROR:  extra data after last expected column

I've been fighting and google against this problem for quite a few hours and do not understand what I am doing wrong?


texts: Table that is a relation of one field to every other similar field

texts:
So, I have two tables, tblOwners and tblNonOwners. These tables are filled with users. I need a third table that keeps track of if any one user has 'seen' any other user from the other.

What I though is having a tblSeen that has three records, the userID who is using the app, and the profileID that the user has seen/not seen, and a boolean fldSeen as 0 or 1.

How would I populate these tables automatically? I could create records for each user that signs up, but how do I create a record for every single relationship?


texts: Why do my RDS read IOPS drop after a certain period of time?

texts:
I am trying to add non-null constraints on several columns in a table with about 70,000,000 rows. Each statement is just ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;, and each statement takes about 4.5 minutes to complete.
During this time, the read IOPS spike to about 8,000, and stay there until the statement completes. However, after executing several of these statements, the IOPS drop to 2,000 and are capped there. At this point each statement takes four times as long. If I wait a while, I can get another 30 minutes or so at 8,000 IOPS.
At first I thought this was the burst credits being used and then regenerated, but

The burst balance never drops below 98% during this entire time.
According to the documentation, I should have a balance of about 2,970, not 8,000.

The database details are

Postgres 11.8 on AWS RDS.
Instance size is db.t3.large.
990 GB of GP storage.
No provisioned IOPS.

My questions are

What is going on here? What is the significance of the 8,000 and 2,000 numbers? I can't find documentation about this anywhere.
Why isn't burst balance being used here?
Is 4.5 minutes to add the constraint on a 70 million-row table reasonable? And in general, is there a more efficient way I can be doing this? I still have over 60 columns to alter.

Read IOPS

Write IOPS

Burst balance

CPU

Memory


texts:
Error while installing Oracle 11g:

Please find the StackTrace of this error.

ID: oracle.install.driver.oui.OUISetupDriver:13
oracle.cluster.verification.PreReqNotSupportedException: Reference data is not available for verifying prerequisites on this operating system distribution
    at oracle.cluster.verification.constraints.XmlTaskFactory.getTasks(XmlTaskFactory.java:248)
    at oracle.ops.verification.framework.util.VerificationUtil.isPreReqSupported(VerificationUtil.java:4538)
    at oracle.ops.verification.framework.util.VerificationUtil.isPreReqSupported(VerificationUtil.java:4487)
    at oracle.cluster.verification.ClusterVerification.isPreReqSupported(ClusterVerification.java:6382)
    at oracle.install.driver.oui.OUISetupDriver.verifyEnvironment(OUISetupDriver.java:299)
    at oracle.install.driver.oui.OUISetupDriver.load(OUISetupDriver.java:422)
    at oracle.install.ivw.db.driver.DBSetupDriver.load(DBSetupDriver.java:161)
    at oracle.install.commons.base.driver.common.Installer.run(Installer.java:216)
    at oracle.install.ivw.db.driver.DBInstaller.run(DBInstaller.java:129)
    at oracle.install.commons.util.Application.startup(Application.java:869)
    at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:164)
    at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:181)
    at oracle.install.commons.base.driver.common.Installer.startup(Installer.java:265)
    at oracle.install.ivw.db.driver.DBInstaller.startup(DBInstaller.java:117)
    at oracle.install.ivw.db.driver.DBInstaller.main(DBInstaller.java:148)

ID: oracle.install.commons.util.exception.DefaultErrorAdvisor:16
oracle.cluster.verification.PreReqNotSupportedException: Reference data is not available for verifying prerequisites on this operating system distribution
    at oracle.cluster.verification.constraints.XmlTaskFactory.getTasks(XmlTaskFactory.java:248)
    at oracle.ops.verification.framework.util.VerificationUtil.isPreReqSupported(VerificationUtil.java:4538)
    at oracle.ops.verification.framework.util.VerificationUtil.isPreReqSupported(VerificationUtil.java:4487)
    at oracle.cluster.verification.ClusterVerification.isPreReqSupported(ClusterVerification.java:6382)
    at oracle.install.driver.oui.OUISetupDriver.verifyEnvironment(OUISetupDriver.java:299)
    at oracle.install.driver.oui.OUISetupDriver.load(OUISetupDriver.java:422)
    at oracle.install.ivw.db.driver.DBSetupDriver.load(DBSetupDriver.java:161)
    at oracle.install.commons.base.driver.common.Installer.run(Installer.java:216)
    at oracle.install.ivw.db.driver.DBInstaller.run(DBInstaller.java:129)
    at oracle.install.commons.util.Application.startup(Application.java:869)
    at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:164)
    at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:181)
    at oracle.install.commons.base.driver.common.Installer.startup(Installer.java:265)
    at oracle.install.ivw.db.driver.DBInstaller.startup(DBInstaller.java:117)
    at oracle.install.ivw.db.driver.DBInstaller.main(DBInstaller.java:148)


texts:
Hi I'm trying to figure out how to design a database for bus time schedule.

There are several lines, each line have two routes (forward and back), each route have several stations (stops) and each station may belong to several routes.

The main goal of the database is to easily select the next arrival time for a specific bus station with a specific line.

I googled and came up with next database design:

lines (id, name, ...)

routes (id, name, line_id, ...)

stations (id, location)

route_station (id, route_id, station_id)

times (id, station_id, line_id, time_forward, time_back)

So, for instance

times Table
id  station_id   line_id   time_forward  time_back
1       10          100         9:15         9:18
2       10          100         9:30         9:33
3       10          100         9:45         9:48
4       10          100         10:00        10:03
5       10          100         10:15        10:18
6       10          100         10:30        10:33
7       10          100         10:45        10:48

8       20          100         9:20         null
9       20          100         9:35         null
10      20          100         9:50         null
11      20          100         10:05        null
...
45      40          200         9:55         null
46      40          200         9:09         null
...

In the first 7 rows, the line with line_id=100 stops at different times in the station with station_id=10 (The null in time_back indicates that there is no backward route that passes by station_id=20).

My questions are:

I'm not sure if this design is right at all. I'm no looking for performance. I just want to know if I will have problems with this design in future time (For example: is easy to insert a new line station? Is easy to select the next arrival time for a bus station with specific line station?
How I deal with different time frequencies? Because is different for weekdays and weekends. If my design is ok, then the example above will work fine with arrival times in weekdays but no for weekends. I need a second table or add a column in times table indicating the type of the day?

Thanks.

P.S. I've taken a look at this design, but I think is too much for my system.

P.S.2 I've read about using NoSQL databases may fits better for this kind of systems; but my knowledge is limited to relational databases.


texts: How to import a.csv file into a postgresql database using \copy?

texts:
I am trying to import a .csv file into a postgresql database. I am using pgadmin III for that on a win7 machine.

My query looks like that:

\COPY pop_grid(GRID_ID, POP_TOT, YEAR, METHD_CL, CNTR_CODE, DATA_SRC) from 'C:\...\popgrid.csv' DELIMITERS ',' CSV;

The data inside the .csv looks like that:

1kmN5142E2862   2   2006    D   IS  AIT
1kmN5141E2862   13  2006    D   IS  AIT

My table:

CREATE TABLE pop_grid
(
    GRID_ID      text            PRIMARY KEY,
    POP_TOT      int         NOT NULL,
    YEAR         date        NOT NULL,
    METHD_CL     varchar(2)      NOT NULL,
    CNTR_CODE    varchar(32)     NOT NULL,
    DATA_SRC     varchar(4)      NOT NULL
);

Why do I get this error?

ERROR: Missing data for column "pop_tot"
CONTEXT: COPY pop_grid, Row 1: "1kmN5142E2862;2;2006;D;IS;AIT"

There is nothing missing?


texts: Restore from copy of SQL Server backup file produces invalid data error

texts:
We have an instance of SQL Server 2008 R2 SP2 sitting in a DMZ.  When I back up one of its databases to a drive local to that server, then run a RESTORE VERIFYONLY against the backup file, it declares the backup set valid.

If I subsequently robocopy the backup file to a mapped drive, pointing to a server located in our domain, then run a RESTORE VERIFYONLY (or a full RESTORE DATABASE, for that matter) against the copied backup file, while connected to a SQL Server instance which is also in the domain (running SQL Server 2008 R2 SP3), the subsequent RESTORE returns the following error message:

    Msg 3203, Level 16, State 1, Line 1
    Read on "<Backup file path>" failed: 13(The data is invalid.)
    Msg 3013, Level 16, State 1, Line 1
    VERIFY DATABASE is terminating abnormally.

This happens consistently and is repeatable.  Anyone have any ideas as to what might be corrupting the copied backup file?  Does robocopy feature any validation flags that might be useful?  What's the best way to troubleshoot this?


texts: Is it possible for a weak entity, already related to a strong entity, to get related to another weak entity in an ER diagram?

texts:
I have a problem statement (briefed),

Database for a Fitness Club -
Client's Attributes: Name, Address, Ph. No.
Membership Plan's Attributes: Beg. date, Sub. Fee, Monthly Fee, Duration
Trainer's Attributes: ID, Name, Salary
Relationship-Types: Client is 'Enrolled In' a Mem. Plan, and Client 'Meets' Trainers
Enrolled In: Client has total participation while Mem. Plan does not.
Meets: Client & Trainer, both have total participation.

By Meets, it means, a Client entity attends a session guided by a Trainer entity.
So, Client & Mem. Plan are weak entity types & Trainer is a strong entity type.
When converted to an ER diagram, Client & Mem. Plan are related to each other, and Client is related to Trainer.
Is it a possible to have such an ER diagram or is there another way to rectify the same?
Edit: This ER diagram represents what I think it should be (without partial keys) and consider arrows in diagram as straight lines connecting two blocks/shapes.

Feel free to correct me if I'm wrong anywhere.
I also have an entity 'Workout Session', which has a relation with Trainer. A Workout Session is conducted by one Trainer only, but the problem states to keep data of only Trainers which a Client meets, not the Sessions he/she attends. But since that wasn't related to the question I needed to ask, so I didn't add that part.
I think there is no single attribute of either Client or Membership Plan which I can use to distinguish their respective entities uniquely, so I think Client and Membership Plan are weak entity types.
I have provided as much details as I can as I can't disclose the complete statement due some reasons.


texts: Database design that handles data growth with time

texts:
I have a big table that stores video rental data with the following columns:

  id, title, language, duration, owner, remarks, closing_date_for_loan

Assuming every day there are thousands of data inserts to this table, then within a year, I could have a million rows of data. The search on the data record involves range query on several columns and will always contain a WHERE closing_date_for_loan > NOW() condition.

To maintain this table, I could perform a query to transfer data with closing_date_for_loan < NOW() out of this big table periodically so that the table won't get too big causing excessive query times. I am looking for a more automated way of handling such data growth similar to how a log rotation works. Any ideas?

Additional Note:

I have tested a few composite index and the query time can range from a few seconds to 50s if the row gets to 5 million. Range queries can be hard to optimize, so I am looking for other ways like keeping the table to a manageable size.


texts: MySQL: Point in Time query using InnoDB log sequence number?

texts:
Is it possible to do a point in time query based on a log sequence number?

For example, I would like to do something similar to Oracle's "as of" query

select foo from bar as of scn 123923455;

which returns a read-consisten result as of that timestamp or system change number.

My application is verifying that the state of two independent database servers is consistent as of a certain operation or point in time.  The data is being replicated via Golden Gate with a Java adaptor, and I intend to store periodic "checkpoints" saying "The data should be consistent as of Oracle SCN x and Innodb log sequence number y."  I should be able to then run a program to verify this is the case.


texts:
I want to select an increment counter group by medec field please how to do this and i would like the output will be as below

SELECT @n := @n + 1 as n, Nb, medec, prest, Nompatient, ftt, Categorie FROM (SELECT @n := 0, m.nom as medec, s.id as Nb, ft.id as ftt, sv.libelle , m.nom , pr.libelle as prest, p.nom as Nompatient, ct.libelle as Categorie from services sv, medecins m, sejours s, sejourprestations sjp, prestations pr, patients p, categoriepatients ct, factures ft WHERE sv.id = s.service_id
AND s.medecin_id = m.id
AND s.patient_id = p.id
AND s.categorie_patient_id = ct.id
AND sjp.sejour_id = s.id
AND sjp.prestation_id = pr.id
AND s.facture_id=ft.id
AND day(s.created_at) = day(CURRENT_DATE)) AS ishmael GROUP BY n ORDER BY medec


texts:
I am wanting to add insert into my uspInsertUserRoles procedure:

CREATE PROCEDURE [dbo].[uspInsertUserRoles]
    @RoleID INT,
    @UserID INT
    AS
    BEGIN
        INSERT INTO tblUserRoles(RoleID, UserID)
        VALUES (@RoleID,@ UserID)
    END
GO

Table:

CREATE TABLE [dbo].[tblUserRoles](
    [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [int] NOT NULL,
    [RoleID] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [UserRoleID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblUserRoles]  WITH CHECK ADD FOREIGN KEY([RoleID])
REFERENCES [dbo].[tblRoles] ([RoleID])
GO

ALTER TABLE [dbo].[tblUserRoles]  WITH CHECK ADD FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUsers] ([UserID])
GO

This is my code:

Private Sub btnInsertUserRole_Click(sender As Object, e As EventArgs) Handles btnInsertUserRole.Click
        sqlCon = New SqlConnection(strConn)

        Using (sqlCon)
            Dim sqlComm As New SqlCommand()
            Dim msg As String = "EXEC sp_addrolemember '" & storedRoleDesc & "','" & storedUserName & "'"
            MessageBox.Show(msg)
            sqlComm.Connection = sqlCon
            sqlComm.CommandText = "uspInsertUserRoles"
            sqlComm.CommandType = CommandType.StoredProcedure

            sqlComm.Parameters.AddWithValue("UserID", storedUserID)
            sqlComm.Parameters.AddWithValue("RoleID", storedRoleID)

            sqlCon.Open()
            'sqlComm.ExecuteNonQuery()

            Dim rowsAffected As Integer = sqlComm.ExecuteNonQuery()
            MessageBox.Show(rowsAffected)
            If rowsAffected > 0 Then
                MessageBox.Show("Record has been inserted successfully")
            End If

Gives me error:

  System.Data.SqlClient.SqlException: 'The INSERT statement conflicted with the FOREIGN KEY constraint "FK__tblUserRo__UserI__51300E55". The conflict occurred in database "THISONE", table "dbo.tblUsers", column 'UserID'.
      The statement has been terminated.'

My tblUsers:

CREATE TABLE tblUsers(
        UserID INT IDENTITY(1,1),
        FirstName varchar(50) NOT NULL,
        LastName varchar(50) NOT NULL,
        Age int NOT NULL,
        UserName varchar(50) NOT NULL,
        Password varchar(50) NOT NULL,
        PRIMARY KEY (UserID),
        UNIQUE(UserName)
        )


texts: Simple Update Join much slower than it should be (MYSQL)

texts:
This is a simple Update Join that updates only about 100 rows:
Update A INNER JOIN B using(id) SET A.active = 1 WHERE A.date > '2020'

This takes about 30 seconds to run, despite the fact that:

This query updates the same 100 rows and takes milliseconds to run:
Update A SET active = 1 WHERE date > '2020'

The join condition is fast, this query does the same join and takes less than a second
SELECT * FROM A INNER JOIN B using(id) WHERE A.date > '2020'

The field active not part of any index

Table A has an index on (id, date), and table B has an index on id.

I tried putting the where condition in the join (using on date > '2020') but it didn't help. I'm absolutely stumped why this takes so long. Any help is appreciated.


texts: How to use a MySQL database both remotely and locally

texts:
I cannot find the best solution for my problem.

I made a C++ application which runs on a Kiosk under Ubuntu that needs to store and retrieve data from a MySQL database and these same data need to be accessed from a remote web application.

These are the requirements:

The kiosk is connected to Internet, but we cannot assume that internet is always available
The kiosk need to always access to database because the users can access the kiosk services only after the login (user data are stored in the database)
the remote web application needs to insert or modify data stored in the database

At the moment, I'm using a local MySQL database installed in the Kiosk with PhpMyAdmin and the application directly access the local data. Then, I used cron to upload the database once a day, then I import the database on my server in order to be accessed by the remote web application.

This is really a bad solution, so I would like to find another one.
What do you suggest?

I would like to have a database on my server and let the remote web application directly use it and receive updates from the Kiosk.


texts: In memory table vs temporary table - benefits if temdb in already on ram disk

texts:
In the docs, we have example of how temporary tables can be replaced with in-memory tables and because a memory-optimized table:

is stored only in memory, and has no component on disk
involves no IO activity
involves no tempdb utilization or contention

they said that:

  Memory-optimization results in speed increases that are often 10 times
  faster or more.

I am wondering if our system is already using ram for tempdb database, which of the benefits will still apply? I guess they are testing temdb on hard drive or ssd and doubt getting such great results.

More details. One of the system mostly used table variable function is the one calculating access to specific entity. Then, the result is joined to various table to get the request data. In order to optimize the latter joins, I am storing the result of this function in temporary table and the results are nice.

But for really large volumes (for example when the function returns 1-2 millions of entities) the execution of the function itself is slow (which is again normal since we are inserting so many rows in table variable).

So, I am thinking to rewrite this function as stored procedure and insert the entities in memory table, hoping that the CRUD operation with the table will 10 times faster or more.


texts: What is the optimum way to store, update, and analyze integer data for 100+ attributes?

texts:
I need to store the count of all individual characters (A-Z, a-z, 0-9, spaces, punctuation marks, all symbols...) that occur in different texts which will be frequently changing.

I will need to update all counts and analyze all of the counts frequently.

Is one table with close to 100 integer columns (one for the count of each character) the most optimum way to store and retrieve this data?


texts: Check if an index exists by comparing columns

texts:
We are currently using ALL_IND_COLUMNS to check if an index exists by its name or the columns it is using. The problem is that we can only check if the index exists with columns in a specific order.

We need to check if a table has an index with any name, but with Column1, Column2 and Column3 in any order. How can we do this?


texts: How does partitioning and the lack of a primary key affect ON DUPLICATE KEY UPDATE

texts:
Consider a table with the following columns:

phone BIGINT NOT NULL PRIMARY KEY,
name VARCHAR(64),
state VARCHAR(64)

We would like to partition the table on state, however that would require dropping the primary key constraint on phone and replacing that constraint with a UNIQUE constraint. We make use of INSERT ... ON DUPLICATE KEY UPDATE and I would like to know how it would affect the system. Surprisingly little is written on the subject from reliable sources, and I'm sure that there are hidden gotchas which will not be immediately apparent to my naive eye.

What problems might we encounter with a partitioned table lacking a primary key, especially in regards to INSERT ... ON DUPLICATE KEY UPDATE queries?

The database in question in running MySQL 5.5 on Amazon RDS. The table in question has upward of 20 million rows and is often used in JOINs with other similarly-sized tables.


texts: Need to upgrade SQL Server from 2012 to 2017 on a Server 2008 box

texts:
I have been asked to upgrade SQL Server from 2012 to 2017, it is currently installed on a server with Windows 2008 OS.
My thinking from what I've researched is this:

Take a snapshot of the server
Upgrade OS first from 2008 > 2012R2
Run SQL Server data migration assistant to make sure nothing is incompatible with new SQL version
Take a snapshot of the server
Backup all DBS, script out all jobs/logins/linked servers, etc using PowerShell
Upgrade SQL Server in place to 2017

From this thread I have found that 2017 should be compatible with 2012 OS.
And here it says I should be able to upgrade SQL Server directly from 2008 SP4 to 2017.
The only thing I'm not sure about is the initial OS upgrade, will this cause any issues with the current SQL install?  Am I doing this in the correct order?
If anyone has done this kind of upgrade I would be grateful if you could advise me if I'm going about this the correct way or if there's a better solution I'm not seeing.


texts: DISTINCT on one column and return TOP rows

texts:
How do you query for three unique customers with the largest Purchase_Cost?

I want to apply the DISTINCT only on Customer_Name, but the query below applies the distinct on all three columns. How should I modify the query to obtain the desired output?

SELECT DISTINCT TOP 3 customer_name, order_no, Purchase_Cost
FROM PurchaseTable
ORDER BY Purchase_Cost


texts: SnapManager point in time restore problem

texts:
When using a clone-backup command in SnapManager (SMSQL), we are not able to restore the most recent logs, because they are not included in the snapshot.  Does anyone have any ideas to help get those logs into the snapshot to be restored?

We often restore the previous night's backup.  This is done with -restoreLastBackup 0 and -TransLogsToApply 0 parameters.

We desire the ability to restore the most recent transaction logs, which are taken every 15 minutes.   This seems to be something that is promised in the product.  And let's face it, it would be a pretty terrible product if you couldn't restore your most recent data.   So something is amiss.

In order to restore those logs, we simply remove the TransLogsToApply parameter.  So the command is as follows:

clone-backup
-Server ProdServer01
-Database MyDatabase
-RestoreLastBackup 0
-TargetServerInstance TestServer01

So the command begins, and mounts a snapshot on TestServer01.   But the problem is that the most recent logs are not in that snapshot.  So some nasty errors are produced:

Applying transaction log backup - 62...

[C:\Program Files\NetApp\SnapManager for SQL Server\SnapMgrMountPoint\MPDisk001\SMSQL_SnapInfo\VDISK_M&N\LogBackup\LOGINST_ProdServer01\LOGDB__MyDatabase\11-05-2013_14.55.56__MyDatabase.TRB]

Retrying to obtain new log backup path (2)...

[C:\Program Files\NetApp\SnapManager for SQL Server\SnapMgrMountPoint\MPDisk001\SMSQL_SnapInfo\VDISK_M&N\LogBackup\LOGINST_ProdServer01\LOGDB__MyDatabase\11-05-2013_14.55.56__MyDatabase.TRB]

Failed to find the specified log backup file using new log backup path.
Error Code: 0xc00408c4
Unable to locate the specified transaction log backup dump file to restore.


texts: How to quote a SQL*Plus identifier in a bash script?

texts:
I get "ORA-00904: "MEMORY_TARGET": invalid identifier" no matter how I try to quote the identifier in the following command:
MEMORY_TARGET=$(su - orat01 -c " echo 'select VALUE from V\$PARAMETER where name = 'memory_target';'| sqlplus / as sysdba ")

Any ideas on how to do this would be greatly appreciated.


texts: Availability Group with log backups to NUL

texts: We currently have a database in SIMPLE recovery and are planing on setting up an Availability Group for reporting purposes (Enterprise SQL 2016).  My understanding is that this will require moving the database to FULL recovery.  We have no need of the log backups and no interest in storing them so I was thinking about setting up the log backups to NUL (bit bucket).  Nothing I've read has said I'll have any problems with this but I wanted to make sure I'm not missing anything.


texts: Unique index for subquery with RANK() function

texts:
I have the following tables:

CREATE TABLE Revision (
    RevisionId INT PRIMARY KEY IDENTITY,
    UserName NVARCHAR(256) NOT NULL,
    DateTime DATETIME NOT NULL DEFAULT GETDATE()
)

CREATE TABLE MyEntity (
    MyEntityId INT NOT NULL,
    RevisionId INT NOT NULL FOREIGN KEY REFERENCES Revision (RevisionId),
    Deleted BIT NOT NULL DEFAULT 0,
    Name NVARCHAR(256) NOT NULL,
    Body NVARCHAR(MAX) NOT NULL,
    PRIMARY KEY (MyEntityId, RevisionId)
)

That is, the MyEntity table has all changes to an entity. When an entity is created, modified, or deleted, a new record is inserted into MyEntity and into Revision, so that the entire history is tracked.

I would like a view to have the latest version of each entity:

CREATE VIEW MyEntityLatest WITH SCHEMABINDING AS
SELECT
    Latest.MyEntityId,
    Latest.Name,
    Latest.Body
FROM dbo.MyEntity
INNER JOIN (
    SELECT
        MyEntityId,
        RevisionId,
        Name,
        Body,
        RANK() OVER (PARTITION BY MyEntityId ORDER BY RevisionId DESC) AS RevisionIdDesc
    FROM MyEntity
    WHERE Deleted = 0
) AS Latest
    ON dbo.MyEntity.MyEntityId = Latest.MyEntityId
    AND dbo.MyEntity.RevisionId = Latest.RevisionId
WHERE Latest.RevisionIdDesc = 1

However, I would like to create an unique index (and hence constraint) such that the Name is unique for the latest revision only. I cannot create an index on the view, because of the subquery in the view.

How can I accomplish this?


texts: How to increase ETL performance in Informatica for Netezza as a source and SQL Server as a target?

texts:
What settings or configuration on the Informatica server, in the Informatica software itself, or on the database servers can be changed to increase Informatica ETL throughput?  What are some benchmarks we can set to troubleshoot performance?  We are specifically using Netezza as a source and SQL Server as a target.

Please exclude multi-threading and Informatica partitioning from this question.

This we've done in the past:

restart servers every so often
remove indexes on target tables in SQL Server before ETL load
increase commit level


texts: Why does MySQL slow down in loading data?

texts:
We have a really large file (26GB) that we need to upload to MySQL. In order to do so we split it into nine 3GB files.

What's confusing is that the first file took only 2 minutes to load, whereas the second file took 16 minutes and the third file took 40 minutes.

We're loading these files using load data local infile syntax, without any indexes or keys. The table engine is MyISAM.

Does anyone know why MySQL slows down on each successive load?


texts: How can I model this problem in a graph database?

texts:
I have a project that I'm working on where I extract data from PDFs and map/visualize the relationships between the extracted pieces.

Here's an example of my problem:

file: 11425646.pdf
  author: bob
  company: abc co
  date: 1/1/2011
  mentioned_users: [alice,sue,mike,sally]
  images: [1958.jpg,535.jpg,35735.jpg]

file: 15421484.pdf
  author: betty
  company: ionga
  date: 2/15/2011
  mentioned_users: [john,alex,george]
  images: [819.jpg,9841.jpg,78.jpg]

file: 11975748.pdf
  author: micah
  company: zoobi
  date: 9/26/2011
  mentioned_users: [alice,chris,joe]
  images: [526.jpg,5835.jpg,355.jpg]

How can I model this in a graph database like Neo4j?

I would like to be able to be given one piece of data (like a person's name) and find all related (images, co-mentions, authors, etc.) at up to 10 depth. Here's what I'm thinking for the structure, but I'm not sure if it's a good approach: (this isn't any kind of actual syntax)

[file: 11425646.pdf date:1/1/2011] -written_by-> bob
[file: 11425646.pdf date:1/1/2011] -from_company-> abc co
[file: 11425646.pdf date:1/1/2011] -mentions-> alice
[file: 11425646.pdf date:1/1/2011] -mentions-> sue
[file: 11425646.pdf date:1/1/2011] -mentions-> mike
[file: 11425646.pdf date:1/1/2011] -mentions-> sally
[file: 11425646.pdf date:1/1/2011] -has_image-> 1958.jpg
[file: 11425646.pdf date:1/1/2011] -has_image-> 535.jpg
[file: 11425646.pdf date:1/1/2011] -has_image-> 35735.jpg

Is this the right way to structure this data in a graph database?


texts: Using On-Premise SQL Data Warehouse Data for OLTP applications

texts:
Quick Information: Our EDW group has created On-Premise SQL Data Warehouse by collecting information from several systems. These SQL DW databases are made up of:

SQL Version: SQL Server 2016 (SP2)
Partitioned by: Year
Total Database Size: 10TB
For HA: Utilizing Always On (1-Primary & 1-Secondary)
Environment: VMWare
Change Tracking Enable on Couples of tables under EDW

EDW developers have created Views and StoredProcedures for end user (i.e. BI Analytics, Application...) to consume the data, this way, whatever changes or modification EDW group is doing to underlying objects on their end is not directly affecting end user. Application Developer (i.e. OLTP App) group who are consuming these Views/Storedprocedures, so they are not allowed to make changes to these EDW objects nor are allow to create adh-hoc queries from OLTP application, as it is also very critical for EDW group to get their load times done under certain hours. We are looking at these Views and Stored procedures, to see if we can tune them for performance.

So far whatever I have read online I see that, it is not a good practice to utilized EDW for OLTP load as, OLTP application are chatty and they mostly need is recent data and not so much of historical data.

Question: What are your all suggestions on, how can we utilize our current EDW data for these OLTP applications, so that, we are not sacrificing Load Times for Performance and vice-versa?


texts: MSSQL 2014 "The provided statistics stream is corrupt" Error 9105

texts:
I have a database that prevent to see user properties due to this error.
I've begin investigating and the problem comes from sys.asymmetric_keys and sys.symmetric_keys system views inside the database.
Both views uses the sys.sysobjvalues table that contains the following statistics:

Stats Name | Column Name
_WA_Sys_00000002_0000003C   objid
_WA_Sys_00000003_0000003C   subobjid
_WA_Sys_00000004_0000003C   valnum
_WA_Sys_00000005_0000003C   value
clst    valclass, objid, subobjid, valnum

The failure appears on the column "value"
DBCC SHOW_STATISTICS ("sys.sysobjvalues",_WA_Sys_00000005_0000003C) that returns:

Name
_WA_Sys_00000005_0000003C
(1 row(s) affected)

All density | Avg.Length | Columns
0,004830 8 |    7,204199      |      value
(1 row(s) affected)

Range_Hi_KEY
Msg 9105, Level 16, State 25, Line 43
The provided statistics stream is corrupt.

Connected using a DAC connection I've tried to delete it (permissions error) and update it without success. The [UPDATE STATISTICS sys.sysobjvalues WITH fullscan, columns] command fails with the "The provided statistics stream is corrupt " error but  [UPDATE STAT.. WITH fullscan, index] works but the error continues.

DBCC CheckDB returns no errors for this table (sys.sysobjvalues).

The server version is: Microsoft SQL Server 2014 (SP2-CU1) (KB3178925)(Hypervisor)

Any idea on how could I repair this DB without a Restore (All my available restores have the error on it)

Many Thanks


texts: Are "Always On Failover Cluster Instances" and "SQL Server Failover Clustering" the same thing?

texts:
I'm reading documentations of SQL Server. On this page at https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/windows-server-failover-clustering-wsfc-with-sql-server, it says:

Both Always On Availability Groups and Always On Failover Cluster
Instances use WSFC as a platform technology, registering components as
WSFC cluster resources.

I'm a little bit confused. In previous version of SQL Server (like SQL Server 2008) there is a technology called SQL Server Failover Clustering, is it the same thing as Always On Failover Cluster Instances? Hope someone can clarify on this. Thanks in advance.


texts: Difference between database dump (export) and database backup?

texts: What is the drawback using only EXP or EXPDP command for a database dump (and considering it as a backup) instead of taking backups using BACKUP DATABASE command (via RMAN)? What is a difference between database dump (export) and database backup?


texts:
Given:
postgres=# \d foo
                Table "public.foo"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           | not null |
 b      | text    |           | not null |
Indexes:
    "foo_pkey" PRIMARY KEY, btree (a)

postgres=# \d bar
                Table "public.bar"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           | not null |
 b      | text    |           | not null |
Indexes:
    "bar_pkey" PRIMARY KEY, btree (a)

postgres=# select * from foo;
 a |  b
---+-----
 1 | one
(1 row)

postgres=# select * from bar;
 a |  b
---+-----
 2 | two
(1 row)

I then join'd using the following JOIN syntax:
postgres=# select * from foo, bar;
 a |  b  | a |  b
---+-----+---+-----
 1 | one | 2 | two
(1 row)

Then, I compared it to full outer join:
postgres=# select * from foo full outer join bar using (a);
 a |  b  |  b
---+-----+-----
 1 | one |
 2 |     | two
(2 rows)

and cross join:
postgres=# select * from foo cross join bar;
 a |  b  | a |  b
---+-----+---+-----
 1 | one | 2 | two
(1 row)

Is it always true that the from a, b, c will produce a cross join?


texts: How do I combine multiple lines for the same package? (SQL)

texts:
I am doing an assignment and I am close to completing it. However the package balance appears on multiple lines and I would like to combine the balances for each package type.

IF OBJECT_ID('vwPackageRevenue') IS NOT NULL
    DROP VIEW vwPackageRevenue
GO

CREATE VIEW vwPackageRevenue
AS
SELECT (dbo.packageWH.PNAME) AS 'Package Name'
    ,(COSTPERSON * COUNT(GFirstName)) AS 'Total Revenue'
FROM dbo.packageWH
JOIN dbo.reservationWH ON dbo.packageWH.P_ID = dbo.reservationWH.P_ID
JOIN guestWH ON dbo.reservationWH.RNUM = dbo.guestWH.RNUM
GROUP BY dbo.packageWH.COSTPERSON
    ,dbo.packageWH.PNAME
    ,dbo.guestWH.GFIRSTNAME
GO

SELECT *
FROM vwPackageRevenue

Any ideas how to complete this?


texts: SQL Server pivot filter underlying tables by aliases specified in view

texts:
I have table structure as follows:

CREATE TABLE [dbo].[Test] (
   [ID] [int] IDENTITY(1, 1) NOT NULL
   , [ProductID] [int] NOT NULL
   , [CountryID] [int] NOT NULL
   , CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TestData] (
   [ID] [int] IDENTITY(1, 1) NOT NULL
   , [TestID] [int] NOT NULL
   , [TimeSeries] [int] NOT NULL
   , [Values] [float] NULL
   , CONSTRAINT [PK_TestData] PRIMARY KEY CLUSTERED ([ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TestData]
   WITH CHECK ADD CONSTRAINT [FK_Test_TestData] FOREIGN KEY ([TestID]) REFERENCES [dbo].[Test]([ID])
GO

ALTER TABLE [dbo].[TestData] CHECK CONSTRAINT [FK_Test_TestData]
GO

Some test data:

INSERT INTO dbo.Test (ProductID, CountryID)
VALUES (1, 1), (1, 2), (1, 3), (1, 4)
    , (2, 1);

INSERT INTO dbo.TestData (TestID, TimeSeries, [Values])
VALUES (1, 2013, 0.5), (1, 2014, 1.9)
    , (2, 2013, 0.78), (2, 2014, 9.7)
    , (3, 2012, 0.77), (3, 2013, 5.6), (3, 2014, 1.54)
    , (4, 2011, 0.81), (4, 2014, 6.7);

And I've created Pivoted view out of it:

CREATE VIEW dbo.vw_PivotedTest
AS
SELECT ProductID, CountryID, [2011], [2012], [2013], [2014]
FROM (
    SELECT T.ProductID, T.CountryID, TD.TimeSeries, TD.[Values]
    FROM dbo.Test AS T
    LEFT JOIN dbo.TestData AS TD
        ON TD.TestID = T.ID
    ) AS S
PIVOT (
    AVG([Values])
    FOR TimeSeries IN ([2011], [2012], [2013], [2014])
    ) AS P;

And I'd query it like that:

SELECT ProductID, CountryID, [2011]
FROM dbo.vw_PivotedTest;

Now comes the question: is it possible for SQL Server to pick only rows from dbo.TestData, where TimeSeries value is only 2011 instead of scanning everything?

I gave a look at execution plan and it seems to be scanning whole table and doesn't filter these results out.


texts:
I want dump a database, but, without DROP DATABASE, and CREATE DATABASE instructions, in .backup / .sql (generate file).

Actually I use this command:

pg_dump --format=c --no-privileges --no-owner --file=/tmp/`hostname`-`date +%d%m%Y`.backup --username=admin --host=127.0.0.1 database_name

But, in top lines of file, I have this:

DROP DATABASE...
CREATE DATABASE...

So, I my case, I want duplicate this database, and, if I use this script with pg_restore I drop the other database (I don't want that).