texts:
I'm building a search interface for a web site that currently searches through approximately 2 million pieces of real estate with approximately 60 million attributes (address, city, state, ZIP, beds, baths, assessed value, etc).

The tables are laid out so that there is a LEAD table (2 million rows) with one entry for each piece of real estate and a corresponding LEAD_DETAIL table (60 million rows) that holds name/value pairs for the attributes.

The LEAD table has the following structure:

LEAD_ID BIGINT PRIMARY KEY
LEAD_TYPE_NAME VARCHAR(50)
CREATED_DATE_TIME TIMESTAMP
MODIFIED_DATE_TIME TIMESTAMP
RECEIVED_EMAIL_ID BIGINT
NEW_LEAD SMALLINT
FDOLD_ID INTEGER

And the LEAD_DETAIL table has the following structure:

LEAD_DETAIL_ID BIGINT PRIMARY KEY
LEAD_ID BIGINT
NAME VARCHAR(100)
VALUE_STRING VARCHAR(1000)
VALUE_NUMERIC NUMERIC
VALUE_DATE DATE

The LEAD table has the following indexes (I've tried lots of indexing strategies to try and get this to work):

"lead_pkey" PRIMARY KEY, btree (lead_id)
"lead_created_date_time_idx" btree (created_date_time)
"lead_created_date_time_idx1" btree (created_date_time DESC)
"lead_created_date_time_lead_id_idx" btree (created_date_time DESC, lead_id)
"lead_fdold_id_idx" btree (fdold_id)
"lead_lead_id_created_date_time_idx" btree (lead_id, created_date_time DESC)
"lead_lead_id_lead_type_name_created_date_time_idx" btree (lead_id, lead_type_name, created_date_time DESC)
"lead_modified_date_time_idx" btree (modified_date_time)

And the LEAD_DETAIL table has the following indexes:

"lead_detail_pkey" PRIMARY KEY, btree (lead_detail_id)
"lead_detail_lead_id_name_key" UNIQUE CONSTRAINT, btree (lead_id, name)
"lead_detail_name_value_date_idx" btree (name, value_date)
"lead_detail_name_value_numeric_idx" btree (name, value_numeric)
"lead_detail_name_value_string_idx" btree (name, value_string)
"lead_detail_upper_value_string_idx" btree (name, upper(value_string::text))

Here is the query that I'm generating:

SELECT   lead.lead_id
FROM     lead, lead_detail
WHERE    lead_detail.name = 'State'
AND      UPPER(lead_detail.value_string) = 'FL'
AND      lead.lead_id = lead_detail.lead_id
ORDER BY lead.created_date_time DESC
LIMIT 5000

Now, the distribution of the data is such that currently every lead is in the state of FL, so this is not exactly a highly "selective" query.  More selective queries, e.g. for a small county, are quite fast.

Here is the explain plan on explain.depesz.com

My question is, given that we are not preventing users from issuing non-selective queries, how can we quickly return the top-n results for large results?  I was hoping to use an INDEX on the CREATED_DATE_TIME column in conjunction with an ORDER BY and a LIMIT to produce the results quickly.

I've been working on this for hours and could really use some help.  Thanks in advance!


texts:
Using SQL Server 2012, how can I check for changes in a text column?

I know the text datatype has been deprecated, and get the ever helpful message

  Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

when trying to reference the existing text column in a trigger, so things like joining inserted & deleted tables on the column does not work.  UPDATE() would work, except it is always true (I am guessing the front end just pushes whatever data is in the form field regardless if it changed or not).

Any suggestions on how I can check for changes on the column?


texts: Auto increment primary key inside a number range

texts:
I have following table in SQL Server 2012:

CREATE TABLE [dbo].[xxx](
    [K1] [int] NOT NULL,
    [K2] [int] NOT NULL,
    [F1] [int] NULL,
    [F2] [int] NULL,
    [F3] [int] NULL,
 CONSTRAINT [PK_xxx] PRIMARY KEY CLUSTERED
(
    [K1] ASC,
    [K2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

K1 and K2 are primary keys.

I want to achieve that K2 is automatically incremented inside the number range of K1.

An usual query should look like:

INSERT INTO xxx (K1, K2, F1, F2, F3) values (10, -1, ...)

A trigger should now check the highest K2 inside the number range of K1 (select MAX(K2) from xxx where K1 = i.K1) , and add one to it.

I tried following trigger:

CREATE TRIGGER [dbo].[trg_xxx_INSERT] ON [dbo].[xxx] INSTEAD OF INSERT AS
BEGIN
SET NOCOUNT ON

insert into xxx
 (K1, K2, F1, F2, F3)
select
  i.K1, (select isnull(max(K2),0)+1 from xxx where K1 = i.K1), i.F1, i.F2, i.F3
from
  inserted i;
END

It does work well if an INSERT statement adds single rows.

truncate table xxx
INSERT INTO xxx (K1, K2, F1, F2, F3) values (10, -1, ...)
INSERT INTO xxx (K1, K2, F1, F2, F3) values (10, -1, ...)
INSERT INTO xxx (K1, K2, F1, F2, F3) values (10, -1, ...)

In this case, I will have following rows in the Table xxx:

10, 1
10, 2
10, 3

However, the trigger fails if I want to add more than 1 row:

truncate table xxx
INSERT INTO xxx (K1, K2, F1, F2, F3) values (10, -1, ...), (10, -1, ...), (10, -1, ...)

I guess the subquery is only executed once and not for every i, and therefore I get a primary key violation.

How can I achive that the trigger also works with multiple inserts?


texts: How to create a temporary table using VALUES in PostgreSQL

texts:
I am learning PostgreSQL and trying to figure out how to create a temporary table or a WITH declaration that can be used in place of regular table, for debugging purposes.

I looked at the documentation for CREATE TABLE and it says VALUES can be used as a query but gives no example; the documentation for the VALUES clause linked therein does not have an example either?

So, I wrote a simple test as follows:

DROP TABLE IF EXISTS lookup;
CREATE TEMP TABLE lookup (
  key integer,
  val numeric
) AS
VALUES (0,-99999), (1,100);

But PostgreSQL (9.3) is complaining about

  syntax error at or near "AS"

My questions are:

How can I fix the statement above?
How can I adapt it to be used in a WITH block?

Thanks in advance.


texts: MySQL - Number format - Separated with Comma

texts:
I have a query of MySQL like this-

SELECT avg('total_cost') FROM 'employee'

And then I am getting output like this-

1254589687.26587952

But I like to have a output like this-

1,25,45,89,687.26

So, a comma separator should be added and number should be rounded.

Is there any way in MySQL?

Thanks for helping.


texts: Linked server query fails when using an Alias after a schema change

texts:
Recently we added a new column [NewColumn] in a table [OurTable]. After that, a query from linked server referencing this table failed with error:

  The multi-part identifier "[OurTable].[NewColumn]" could not be bound.

This is the query referencing the table, it was not even referencing the NewColumn.

SELECT 'test' AS dummy FROM [LinkedServer].[Database].[Schema].[OurTable] ot

The fix was to get rid of the alias and the query worked

SELECT 'test' AS dummy FROM [LinkedServer].[Database].[Schema].[Table]

The only change is removing the alias.

I'm lost here, I've checked sp_serveroption for lazy schema validation and it's false.

I wanted to try to create a synonym, but I'm missing the permissions to do so.

EDIT: Found the same problem here


texts: In which tables values will be inserted if I insert values into a view that its result is made out of inner join of 3 tables?

texts:
Ι have the following view:
CREATE OR REPLACE VIEW public.my_view as
SELECT
  col1,
  col11
from
  table_a JOIN table_b on table_a.id=table_b.a_id
  join
   (
     select id,col56 from table_c where col56 > 3
   ) as filtered_c on table_a.id=filtered_c.a_id;

table_a is:
id: SERIAL PK
col1: INTEGER

table_b is:
id: SERIAL PK
col11: INTEGER
a_id FK table_a

table_c is:
id: SERIAL PK
col56 INTEGER
a_id FK table_a

As I saw upon postgresql wiki I can insert values to views that correspond to the appropriate tables. But in my case if I insert:
INSERT INTO my_view VALUES (7,88);

In which table the values will be inserted? As far as I know views are read-only tables.


texts: Querying pg_buffercache: relation "pg_buffercache" does not exist

texts:
I have PostgreSQL 9.4.7 installed in LXC container on Ubuntu 14.04. For monitoring purposes, i try to execute this from my host machine:
psql -qAtX -h *** -p *** -U postgres -c "select count(*) from pg_buffercache where isdirty", so I am expecting to get a pure decimal value for parsing. However, I get relation "pg_buffercache" does not exist error. I have already added pg_buffercache extension (CREATE EXTENSION pg_buffercache;), restarted server afterwards and checked if it is present:

postgres=# \dx
pg_buffercache | 1.0    | public     | examine the shared buffer cache

The strangest thing is, if i execute this query directly in postgresql shell, it works just fine:

postgres=# select count(*) from pg_buffercache where isdirty;
count
-------
 113

What can cause this? Other queries not using pg_buffercache work perfectly.


texts: Unable to create read only user-- postgresql 9.4

texts:
I am using PostgreSQL 9.4, I want to make a user with permanent read only access on public schema.
I have tried many different ways to get this but using

REVOKE ALL on schema public to user1;
GRANT SELECT on all tables in schema PUBLIC to user1;

doesn't make any sense for me, user user1 is not able to change any table but is still able to create new tables in public schema.


texts: mysql data types: define as char but work as int

texts:
I have to create a table including a column named tax. The data values of that column are such as 20k, 15k, and so on. So I used varchar(10) data type for that column. But when I have to do calculation or comparison , (eg) to find the tax more than 15k,   I have to use that as an int (tax>15). Is the correct way? Or is there any better way?
I consider to change the column name as “tax(k)”. But I didn’t because I want to keep the table as the original table. In the original table, the column name is just tax and the data values are 10k, 15k and so on.


texts:
We have user associated to a schema with limited privileges.During run time we are generating symmetric key using same user log-in.since the user has limited privilege, we are not able to create key now.

How to grant permission for creating,reading and executing symmetric key in sql server 2008 for this user ?


texts: How to find the max date stamp in a database?

texts:
I want to find the most recent date in a database.

I could use sys.dm_db_index_usage_stats to find when it was last updated, but there is no value here if it has not been updated since the last server restart

I could use MAX()

use adventureworks
select Max(ModifiedDate)
from HumanResources.Department

But I would need to look at every table in the database, find each datetime (or date, smalldatetime, datetime2) field and run the query multiple times, then decide which was most recent.

I want something like

use <some_db>
select MAX(<FieldThatIsADate>)
from <anytable_in_db>

I have Googled around and am not finding a way to use the datatype in place of a field name. I have not attempted to find a way around naming the specific table.

How can I make this query as portable as realistic?

If I have to build and run a unique query for every table in each database, well that is what I have to do.


texts: Sharding for slave node in master slave replication

texts: Is it possible to configure sharding only for slave nodes in a master slave replication scheme?


texts: Understanding Disk Partition Alignment for Windows Server 2012 and SQL Server 2014

texts:
I am about to setup a SQL Server 2014 on a Windows Server 2012. Starting from best performance on the drive I formatted the drive with 64k allocation unit size.
Now I am stuck using Diskpart to set the perfect offset. As far as I understand I have 63 hidden sectors and to align it with my formatting I can use an offset of 1024k , did I get the right?
Anyway when I list my partition afterwards I get this result:

Using command line wmic partition get BlockSize, StartingOffset, Name, Index

I get a starting offset of 135266304

Some websites/blogs recommend 32K other do recommend 1K for 2012, whom can I trust? Currently I have issues understanding it correctly. May the force (you) enlighten me?


texts:
We manage an internet based SaaS app which has a MySQL DB as its backend DB.
It is a Windows 2008 R2 server running MySQL 5.5/Apache 2.2
I have started using MonYog to monitor the health of the DB Server and get regular (hourly) emails informing me that the number of refused connections is high.
Looking in the logs for the DB I can see constant login attempts as there are 'Access denied' entries in the log.
I suspect someone is trying a brute force hack attempt.

Question is, how can I best secure this server. At present I have port for DB open on firewall and cannot restrict that usage to a 'white list' of client IP addresses as I have developers working on the system who do not have a fixed IP address (working on the road, at home etc). The main app (web site) is on the same server so that connects as a local host only user so I guess that is pretty secure.

The challenge is the remote techs who need direct DB access for coding procedures etc.

Is there something I am missing in being able to lock the server down more and stop these constant hack attempts into the open TCP port of the DB?

Thanks
Mark


texts: Is it bad to have a cyclical relationship?

texts:
I have 5 tables that have ended up with a cyclical relationship and I don't think that is ok.
I School is in one County.  A Distict has many Counties. A Teacher can teach in many Districts.

STUDENTRECORD - RecordId, TeacherId, CountyCode
COUNTY - CountyCode, CountyName, DistrictId
DISTRICT - DistrictId, DistrictName
TEACHER - TeacherId
TEACHERDISTRICT - DistrictId, TeacherId

So the relationship goes from the StudentRecord, teacherid, to the district, to the county and back to the studentrecord.
I have built the database, populated the teacher, district and county tables, but I have not tried to insert a StudentRecord yet.  I just know I have not seen a db design with a circle relationship before and I think there is a reason for that.  I assume I have created a bad design.
Is a cyclical relationship a bad thing in database design?  If so, why?Thoughts on fixing?
EDIT:  I forgot the DistrictId in the COUNTY table, which completes the circle. Sorry for the incomplete question.  Here is a picture:


texts: How big can innodb_doublewrite_file grow?

texts:
When I use the enable innodb_doublewrite_file ...

How big can this file grow ?
How can I calculate the optimal disk size, if it would be used only for this file?


texts: Restore mysql database with some columns content enclosed by " and another not

texts:
We have to restore a database in MySQL but some columns have content enclosed by " and another not like in this sample:

00.168.93.70,null,ES,0,htc,33a48a1515b20691,"htc;HTC One (REL4.4.2)",m7,53e4b0f1e733a4a95e,-Free-2.1.0.6.f,4.4.2,unknown,13,"Internet app",com.app.android.free.debug,null,"HTC One",test@gmail.com,"31.5000,34.7500",APA91bE1E-7-tO1fgbYCs5kbiUapri1zm5Bda5ehyXmTgTOabx5GQHprbYhfB62w0BgdJ7Nbe3qevehFmhCmGXwArSkCr8qpyjoL_WIGq7IA0tsyC4pmpxT0RO9gJ2K3ErfaMQevHviUR4ZJrm_,"en_EN,zh_CN"
unknown,unknown,unknown,0,htc,33a48515b20691,"htc;HTC One (REL4.4.2)",m7,5385a31e733a4a95f,Free-2.1.0.6.f,4.4.2,unknown,0,unknown,com.app.android.free.debug,unknown,"HTC One",test@gmail.com,unknown,APA9zm5Bda5ehyXmTgTOabx5GQHprbYhfB62w0BgdJ7Nbe3qeveAtIxxi0X3mCr8qpyjoL_WIGq7IA0tsyC4pmpxT0RO9gJ2K3ErfaMQevHviUR4ZJrm,

How Can I restore it properly ?


texts: Developing a database for a funds transfers business where (a) people and organizations can (b) send and receive money

texts:
In the business context of relevance, both members and organizations need to have an account for funds. Funds can be transferred

from member to member,
from member to organization,
from organization to organization, and
from organization to member.

Considerations

In order to build a database for such a scenario, I have created the following three tables:

CREATE TABLE Members (
  memberid serial primary key,
  name varchar(50) unique,
  passwd varchar(32),
  account integer
);

CREATE TABLE Organizations (
  organizationid serial primary key,
  name varchar(150) unique,
  administrator integer references Members(memberid),
  account integer
);

CREATE TABLE TransferHistory
  "from" integer, -- foreign key?
  "to" integer, -- foreign key?
  quantity integer
);

I think that the TransferHistory table is necessary to show who/what sent funds to whom/what.

The problem is, since Members and Organizations are different tables, how can I reference them from the TransferHistory table?

For instance, the data involved can show up as follows:

Account      Account      Quantity
-----------  -----------  --------
 1072561733  38574637847       500
38574637847   1072561733       281

That would suggest that accounts need to be recorded in the same table, but accounts are for two different kinds of owners (members and organizations), each of which are retained in their respective table.

I could create a table called Accounts, so now I would have four tables:

CREATE TABLE Members (
  memberid serial primary key,
  name varchar(50) unique,
  passwd varchar(32),
  accountid integer references Accounts(accountid)
);

CREATE TABLE Organizations (
  organizationid serial primary key,
  name varchar(150) unique,
  administrator integer references Members(memberid),
  accountid integer references Accounts(accountid)
);

CREATE TABLE Accounts (
  accountid serial primary key,
  state integer
);

CREATE TABLE TransferHistory (
  "from" integer references Accounts(accountid),
  "to" integer references Accounts(accountid),
  quantity integer
);

...but now I have to ensure that each foreign key from Members and Organizations tables does not point to the same Account row in Accounts table...

...or I could have an Accounts table having two foreign keys, one pointing to Members and another to Organizations (and one foreign key column would have to contain a NULL mark at all times). But now things get a bit confusing with respect to queries. The database design would be, generally speaking, as follows:

CREATE TABLE Members (
  memberid serial primary key,
  name varchar(50) unique,
  passwd varchar(32)
);

CREATE TABLE Organizations (
  organizationid serial primary key,
  name varchar(150) unique,
  administrator integer references Members(memberid)
);

CREATE TABLE Accounts (
  accountid serial primary key,
  member integer references Members(memberid),
  organization integer references Organizations(organizationid),
  state integer
);

CREATE TABLE TransferHistory (
  "from" integer references Accounts(accountid),
  "to" integer references Accounts(accountid),
  quantity integer
);

So, does anybody have a suggestion on how to solve this problem?


texts: SSIS: Trying to configure branching logic in Foreach loop

texts:
Full disclosure: I posted this on SQL Server Central yesterday but got no answers. I hope someone here can assist me.

I'm working on importing flat files into a database. I have used a Foreach loop in an SSIS project in SQL Server Data Tools for SQL 2012. have just started developing these packages so please let me know of any rookie mistakes I need to correct.

If even one of the files has not been updated in the last day, I want the processing to stop and an alert email sent out.

Schematically, the control flow I need is as follows:

[Foreach loop] ---> Failure ----> Send import failure email
|
|
↓
Success
↓
[Proceed with import]

I have tested with setting one of the files to be too old, on purpose, to trigger the failure. What happens now, though, is that I get the Failure email but I also get a SQL error from a task in the Proceed with import section, as if the Success path has also been followed.

In both success and failure cases, I have tried to set Precedence Constraints with "Expression and Constraint" as the Evaluation operation, with a Boolen value set for the expressions. @[User::ProcessFile]= False or @[User::ProcessFile]= True depending on the case.

Could someone explain (or direct me to a reference explaining) how to make sure the SSIS package is following the path I expect it to?

EDIT: Picture of control flow is below.


texts: SQL Server - Change AuditLevel on Linux hosted instance

texts:
just wondering if it's possible to change the AuditLevel on SQL Server 15.0.1800.32 (2019) hosted on Linux to "Failed logins only".

Currently it's set to audit failed and successful logins and I would really like to switch it to failed only.

Many thanks in advance!


texts:
I want to track changes on SQL Server 2012, and save it for 5 days.

I want get the time that each insert/update/delete operation occurred on the SQL Server and save the new insert line(on insert operaion)/ old+new value(for update operation) / removed line(for delete operation).

I've searched a lot, but I can't find a solution for it.


texts: SQL Server : Included columns in an Index: is there something like "too many"?

texts:
We have a table in one of our databases with approximately following structure:

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

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

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

Thanks in advance.


texts:
I'm really confuse with the term instance and database. So, my first question is how database and Instance are related in Oracle, I mean, whether an instance is associated with one/multiple database(s) or a database associated with single/multiple instance(s)?

And, my second question is, one of my friend says we can't have multiple instances in oracle. is it really valid a statement ?


texts: Can db_datareader user block ALTER TABLE statement?

texts:
db_datareader user tries to update and gets an error:

BEGIN TRAN

UPDATE [TestDB].[dbo].[Table_1]
  SET Col1 = 'Test';

   Msg 229, Level 14, State 5, Line 3
The UPDATE permission was denied on the object 'Table_1', database 'TestDB', schema 'dbo'.

User doesn’t close session.

User with enough rights tries to alter the table afterwards and gets blocked:

BEGIN TRAN

ALTER TABLE [TestDB].[dbo].[Table_1]
ADD Col2 INT;

Executing query ..

Is this normal behavior?


texts: What does having the primary key as the last column in a composite secondary index in an InnoDB table do?

texts:
Say I have a 1-to-N relationship (person_id, pet_id).  I have a table where pet_id is the primary key.

I understand that an InnoDB secondary index is essentially a B-tree where the values are the corresponding primary key values for the row.

Now, suppose one person can have thousands of pets and I often want a person's pets in order of pet_id.  Then it would matter if records in the secondary index are sorted by (person_id, pet_id) or just person_id with the pet_id's for that person_id being unsorted.  Guessing the later.

So, if person_id is non-unique, are records physically sorted by (person_id, pet_id) or JUST pet_id?

Thanks


texts: Can I simulate a sequence in pre-2012 SQL Server, with separate identity column, in function style?

texts:
A database on SQL Server 2005/2008/R2 is currently using an identity column in a separate table as primary key source. The reason is that the application, with NHibernate as ORM, needs to know the PK value before it is actually inserted (NHib can work with identity PK columns, but with some serious problems, like pre-INSERTs of invalid rows outside of transactions).

CREATE TABLE MainTable
(
    ID int primary key,
    Name nvarchar(100) not null,
    -- more columns
)

CREATE TABLE IdSrc_MainTable -- one IdSrc_ table for each data table
(
    ID int identity(1,1),
    DummyCol int null
)

CREATE PROCEDURE GetNewId(@TableName nvarchar(100))
AS
(
    DECLARE @newId int
    DECLARE @sql nvarchar(4000)

    -- check if <@TableName> table exists, prevent SQL injection

    -- create this as dynamic SQL due to @TableName variable,
    -- with <@TableName> replaced by the actual table name.

    SET @sql = N'
    BEGIN TRANSACTION GetNewIdTran -- or savepoint in existing tran
    INSERT INTO IdSrc_<@TableName> (DummyCol) VALUES (null)
    SET @newId=SCOPE_IDENTITY()
    ROLLBACK GetNewIdTran' -- with name if inner transaction

    EXEC sp_executesql @sql, N'@newId int OUTPUT', @newId OUTPUT

    RETURN @newId --
)

Usage is like:

DECLARE @newId int

EXEC @newId=GetNewId(N'MainTable')
INSERT INTO MainTable (ID, Name, otherStuff) VALUES (@newId, @name, @otherStuff)

NHibernate uses a custom ID generator which calls the stored procedure.

While this is o.k. for single row inserts, bulk operations can't have an explicit ID/primary key assignment and require a cursor which repeats the above code for each row. A function (as direct replacement of a sequence) with an ID result value fails due to the DML operation, and stored procedure output/returned int results can't be used directly in insert/update statements.

When attempting to pre-fetch many IDs in a GetNewIds procedure, duplicate usages have to be prevented: if somebody else gets a new ID from the IdSrc_ table meanwhile and only a min/max ID range is returned. Long lasting table locking shall be avoided to prevent a possible total deadlock.

It seems to be impossible to return result sets or table variables with IDs for a bulk operation - any attempt with table value functions, table value OUT parameters or INSERT INTO from stored procedure result set fails due to the DML operation in GetNewId(s).

Even if I have a "safe" ID range, using locking and DBCC CHECKIDENT with RESEED, I still can't find a way to insert incremental PK values, like

INSERT INTO MainTable (ID, Name, other) -- already existing, not select into!
    SELECT dbo.IncrementAndGetNext(@IdCounter), Name, Other
    FROM StagingTable. -- won't work

Is there a safe and well performing way to simulate sequences, which allow both pre-fetching and set-based DML operations?

(for a data migration, I once even worked around this using dynamic DBCC CHECKIDENT calls with RESEED, assuming no applications were running then which could steal my new IDs. Luckily, it was an SQL Server 2012, which allowed me sequences to fill in IDs in set-based INSERT/UPDATE - even if the DB schema didn't use them due to downward compatibility)


texts:
I have configured Database mail and alert, test email is being sent successfully to designated recipient who were part of OPERATORS but I have created an alert on EXECUTE PROCEDURE (exec sp_insert_shift_A_data; which is not sending email on Procedure fail error, I have set SEVERITY to "016 Miscellaneous user error" and CHECK on raise alert when message contains and MESSAGE TEXT given "Violation of UNIQUE KEY constraint".

Kindly guide me if there is any mistake in configuration or How it is configured? Why it is not sending email on Procedure fail error while procedure failed to execute?


texts:
I'm on Oracle 11gR2 with a 2 node RAC system.  It's shared fiber storage to an EMC Clariion.

Last friday things went bad..fast.  All of the sudden processes that normally ran fine for years became very, very slow.  I noticed a sudden increase in log_file_sync waits and the LGWR process is listed as a blocker for several processes.  Nothing changed on that Friday that we're aware of.  Also, it appears to be just on one node.

Statspack reports confirm that log_file_sync wait time went from around 1ms to 47ms !
Additionally statspack shows this - meaning some of them are waiting a lot:

                           Total ----------------- % of Waits ------------------
Event                      Waits  <1ms  <2ms  <4ms  <8ms <16ms <32ms  <=1s   >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
log file sync               100K    .0    .3   1.7   9.0  25.4  31.0  32.4    .1

And before it was this:

                           Total ----------------- % of Waits ------------------
Event                      Waits  <1ms  <2ms  <4ms  <8ms <16ms <32ms  <=1s   >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
log file sync              1589K  72.3  20.4   5.4   1.2    .6    .1    .0

What can cause this?  What should I be checking for?


texts: Snapshot Replication where Publisher is SQL Server 2008 and Subscriber is SQL Server 2019 not working

texts:
When we are performing Snapshot replication where Publisher is SQL Server 2008 and Subscriber is SQL Server 2019. We are receiving below mentioned errors.

Error messages: The process could not bulk copy into table '"dbo"."Car"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037) Get help: http://help/MSSQL_REPL20037 metadata query failed Invalid character value for cast specification (Source: MSSQLServer, Error number: 22018) Get help: http://help/22018 Unspecified error

When we change compatability level of subscriber database to 120 then snapshot replication is working successfully.

Is there anyway by which we can perform snapshot replication successfully without changing the compatability level?.


texts: How should I Schedule SQL Server 2008 queries to run every month?

texts:
I need to schedule some queries to run at the beginning of every month (for monthly reports) and I read that creating a JOB in sql server 2008 was the way to go. Unfortunately, I don't have the rights/privileges to use SQL Server Agent at this time. Is there another way of scheduling a few queries to run the first day of every month?

I am running Sql Server 2008 R2 on Windows Server 2008 R2 Standard operating system.


texts:
I would like to play with RAC configuration.

Using virtual area (VMware or VirtualBox):

RAC1 (Windows 2003 Server)
RAC2 (Windows 2003 Server)
Shared storage (Openfiler)
DNS server (for using SCAN feature)

In process of RAC config I will need to insert static IP address, subnet mask, gateway. Is it possible to create RAC configuration in virtual area, not touching work Active Directory network (I mean not adding IP to working office network)?


texts: How to query closest date and display the specific data

texts:
guys, I have a problem displaying data from my query code.
I want to display data from the closest date today. Below are my sample data and my output data that I want to display.

Landing Area data.

|landing_id| id_number| address |
+----------+----------+---------+
|    1     | 00012345 | Ozamiz  |
|    2     | 00012346 | Tudela  |
|    3     | 00012347 | Nailon  |
|    4     | 00012348 | Taboo   |
|    5     | 00012349 | Jimenez |
|    6     | 00012350 | Tangub  |
+----------+----------+---------+

Percentage data.

|percent_id| landing_id | percentage |     date_added     |
+----------+------------+------------+--------------------+
|    1     |     1      |    19      |2018-10-16 21:42:22 |
|    2     |     1      |    44      |2018-10-16 20:43:32 |
|    3     |     5      |    13      |2018-10-15 19:43:49 |
|    4     |     3      |    22      |2018-10-13 15:43:56 |
|    5     |     3      |    54      |2018-10-12 18:44:03 |
+----------+------------+------------+--------------------+

The query code is.

SELECT fish_landing.landing_id,
   percentage.percentage,
   percentage.date_added,
   fish_landing.address
FROM fish_landing
LEFT JOIN percentage ON percentage.landing_id = fish_landing.landing_id
LEFT JOIN (SELECT landing_id, MAX(date_added) AS max_date_added
        FROM percentage
        GROUP BY landing_id) AS dt
ON dt.landing_id = percentage.landing_id AND
 dt.max_date_added = percentage.date_added
ORDER BY fish_landing.landing_id ASC

The output of the query code above is this, where the date_added and the percentage is not exact, because the other landing_id is displayed.

|landing_id| percentage |     date_added     |
+----------+------------+--------------------+
|    1     |    19      |2018-10-16 21:42:22 |
|    1     |    44      |2018-10-16 20:43:32 |
|    2     |            |                    |
|    3     |    54      |2018-10-12 18:44:03 |
|    3     |    22      |2018-10-13 15:43:56 |
|    4     |            |                    |
|    5     |    13      |2018-10-15 19:43:49 |
|    6     |            |                    |
+----------+------------+--------------------+

And the output data that I want to display is the table below, having a latest date_added in every landing_id will be displayed.

|landing_id| percentage |     date_added     |
+----------+------------+--------------------+
|    1     |    19      |2018-10-16 21:42:22 |
|    2     |            |                    |
|    3     |    22      |2018-10-13 15:43:56 |
|    4     |            |                    |
|    5     |    13      |2018-10-15 19:43:49 |
|    6     |            |                    |
+----------+------------+--------------------+

I hope you can help me in my problem.


texts: mariadb automate replication resync using windows task scheduler

texts:
Built Master - to multi-slave for different locations(servers)replication setup.
Need batch file script to be run under windows task scheduler to automate replication status.

if Replication breaks and last error found, hit   some  commands to stable replication status. Also generate logs for every server.


texts: We're running a SQL Server 2000 instance with users tied to Windows accounts. Many of the usernames have spaces in them and that is causing us some problems now. We would like to be able to rename them to get the spaces out, but I'm unable to figure out how this is done. I would love some help on this.


texts: Timestamp Difference - Syntax Error - Condtitional Selects

texts:
Here is what I have right now

SELECT name, admin, reason, unban,
FROM_UNIXTIME(date) AS datetime, HOUR(difference) AS HRemain,
MINUTE(difference) AS MRemain
FROM (SEC_TO_TIME(AVG(TIMESTAMPDIFF(MINUTE, unban, NOW()))) AS difference FROM banned, banned)
WHERE name = '%e'

I get some syntax error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(AVG(TIMESTAMPDIFF(MINUTE, unban, NOW()))) AS difference FROM banned, banned) WH' at line 3

Here is what I am trying to achieve.

Table banned

name   | admin     | reason | unban(TIMESTAMP) | date
Yashas | SomeAdmin | test   | 180              | 60

Required Results:

Player who was banned: Yashas (given)
Administrator who banned: SomeAdmin (need to fetch from the db)
Banned on: January 1st, 1971 (must be obtained from date) (Need the string as datetime) (I decided that I don't need the time, just the date as a string)
Unban Time left: 0 Hours 2 Minutes (I need the hours left as HRemain and minutes left as MRemain)

I need the following:

unban as a date-time string
time remaining for unban from current time. I need just hours and minutes remaining.
and rest of the content of the table as it is including unban

If possible optimize the query for the following:
If unban is set to 0 then it means its a permanent ban and therefore I don't need the time remaining until unban. So in every case where unban is zero, I don't need HRemain, MRemain.

Thanks

EDIT:

CREATE TABLE `banned` (
`name` varchar(21) NOT NULL,
`admin` varchar(21) NOT NULL,
`reason` varchar(81) NOT NULL,
`unban` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

My old query which was working but isn't efficient.

SELECT name, admin, reason, unban, FROM_UNIXTIME(date) AS datetime,
HOUR(SEC_TO_TIME(AVG(TIMESTAMPDIFF(MINUTE, unban, NOW())))) AS HRemain,
MINUTE(SEC_TO_TIME(AVG(TIMESTAMPDIFF(MINUTE, unban, NOW())))) AS MRemain
FROM banned WHERE name = '%e'


texts:
I've created a schema to allow creation of folders for customers.

Customers

Id
Firstname
Lastname
StreetLine1
StreetLine2
City

CustomerFolders

Id
CustomerId
ParentId (NULL)
Name

Now as you can see, you can create folders with a specified name and which can be arranged like a tree structure.

My problem now is this: CustomerFolder has a relation to Customer (which is a good thing) and to its parent (which alone is also a good thing). However, it would be possible to create a folder related to a customer with ID 1 and for the same folder to have a parent folder that relates to a customer with id 2.

How can I prevent this at database level?


texts: MySQL Using Group By, Need to add fields where answer is reliant on another fields data

texts:
I've asked a the same question before, but didn't get an answer and I believe I made the question too confusing to understand.

BEFORE CODE:

SELECT
inventory.WHSE AS `Warehouse`,
inventory.`CODE` AS `Item No.`,
inventory.INV_DESCRIPTION AS Description,
inventory.ONHAND AS `On Hand`,
pricing.BVRTLPRICE01 AS `SF Warehouse`,
pricing.BVRTLPRICE02 AS `LA Warehouse`,
pricing.BVRTLPRICE03 AS `HP Warehouse`,
pricing.BVRTLPRICE04 AS `FOB China/Indonesia`,
pricing.BVRTLPRICE05 AS ECOMM,
inventory.CUBE,
inventory.WEIGHT,
inventory.DIMENSION,
inventory.PROD AS `Status`
FROM
inventory
INNER JOIN pricing ON inventory.`CODE` = pricing.BVSPECPRICEPARTNO
WHERE
inventory.WHSE IN (00, 10, 20, 50, 70) AND
inventory.PROD IN ('A', 'A-70', 'B', 'C', 'S', 'KIT', 'SET')
GROUP BY
inventory.`CODE`,
inventory.WHSE ASC
ORDER BY
`Item No.` ASC,
inventory.WHSE ASC,
inventory.PROD

SQL before and after http://www.glgonline.com/pics/SQL.png

Can someone please help me figure this out? Thanks


texts: In source code, is it more secure to use several MySQL users that have limited access as opposed to using one user that can access everything?

texts:
I have a Perl website back-end that connects to MySQL databases. We have dozens of customers and each customer has their own database. So depending on which customer webpage we're on, the Perl will connect to a specific database using a MySQL user that only has access to that database. For example, MySQL user customer1 only has access to database customer1 and MySQL user customer2 only has access to database customer2, etc.

Is that a common practice to make things more secure, or would it be just as secure to always use the same MySQL user that has access to all databases?


texts: Best way to solve a lot of queries stuck because of no index

texts:
Environment

PostgreSQL 9.4
16 GB RAM  8 Cores
max_iops: 8000

What problem I encountered
Last night our production environment database encountered a large amount of traffic, whcih caused the cpu load to approach 100% and the number of active connections approached more than 600. Most of the connections are querying the same table, and stuck there.
Then I found this table has 800,000 rows and does not have any indexes.

What I have tried
I created a index on this table without concurrently. A few minutes later, there were no more stuck queries.
What I want
Although I have solved this problem, but I think this approach is risky! Because the database server is already busy, the ddl operations may be the last straw to kill the camel. Is there any  safer and more sufficient way to solve this problem?


texts:
The cappedCollection concept works well for most of my projects where cleaning old data without care makes sense.

For another projects, I need a more complex and safe concept. The requirement is nearly the same as logrotate.
The data is appended to the main collection, without compression/compact and no index except a timestamp for simple queries by time. => The focus is on writing and persistent data.

Similar to the logrotate rules, I'd like the main collection not to become too large => capped by size; if possible, capping by timestamp might be a plus.

This sounds like a cappedCollection, but I do not want any data loss when it's capped. The old data should be stored into another db's collection that must be compact:true and a non-capped collection. It's name depends on the current month and makes sure that there will be max 12 "archive" collections per year.

Example:

liveDB.mainCollection_capped grows and starts capping.

Before removing old documents, these are savely moved into archiveDB.compactArchiveCollection201303.

No data is lost and the main collection remains small and fast. Storing the data in another database avoids db locks, e.g. repairDatabase tasks on an archive file will not affect or delay the main collection.

Is there a good practice or how to achieve this - as reliable and automated as possible - without writing all the data transfer for a cronjob which handles the data transfer but should never ever be missed because data is lost if capping starts before old data is copied into the archive.


texts: Measuring SQL execution time in PostgreSQL?

texts:
MySQL has a performance_schema database that allows one to capture SQL statement execution time data in a table (e.g. performance_schema.events_statements_history_long ; useful MySQL link).

I was wondering if a similar set of tools existed in PostgreSQL?


texts: How to efficiently clone database records together with all child elements

texts:
I have an insert-only PostgreSQL database with a Book table, a Page table and an Element table.
Books can have multiple pages and pages can have multiple elements.
The database is insert-only because any time a page is modified, a new version of that page is created in a new record for auditing reasons.

-------------
Book
-------------
id [PK]

-------------
Page
-------------
id [PK]
book_id [FK]
version
created

-------------
Element
-------------
id [PK]
page_id [FK]
version
created

Currently, Element has a Foreign Key pointing to Page.id and Page has a Foreign Key pointing to Book.id.

A requirement is to have independent page drafts (versions) that can contain any number of elements. Every time a new page draft (version) is created, the new draft should be a clone of the previous version and it should contain all elements in the previous version. Elements can be added to or removed from a specific version of the Page, although Elements records are never deleted.

For scalibility reasons, I would avoid cloning all the elements together with the page, since the elements could be a very large number. I would also what to avoid many-to-many relationships between pages and elements.

I was thinking about replacing the Element.page_id FK with an Element.book_id FK so to have a single bucket of elements and pages more loosely coupled with each other. This way, when cloning a page, I wouldn't have to clone all the elements, but I would need a way to associate each element with a particular version of a Page.

Ideally, I should be able to retrieve a page version with all the elements associated to that version with a single SQL query.

I am still in a design/prototyping phase, so any aspect of the databse can potentially be changed to fulfill the requirements.

What could be the best way to associate Pages and Elements according to the requirements above? I hope the requirements are clear, if not, please ask any questions and I'll try to clarify.


texts:
I need a graph database system that has an Erlang driver.

Can you advise which database system to use?


texts:
This might be a little silly, but can't figure out why this insert is not working, I did surround the IP with single / double quotes!

psql -U dbuser hosts -h dbhost -c 'INSERT INTO HOSTS ('type','name') VALUES ('"test"', '"10.100.133.1"')'
Password for user dbusr:
ERROR:  syntax error at or near ".133"
LINE 1: INSERT INTO HOSTS (type,name) VALUES (test, 10.100.133.1)
                                                          ^

Do I need to escape anything?


texts:
I have a question related to tree like structure that I try to shown you in table

id       name           reportsto
2        Akas           345
3        grity          345
4        kethy          2
5        alex           345
6        holy           3
7        dean           4
8        ambrose        3
9        amily          456

Now reportsto contains parent id like when I am login with reportsto id 345 then the contacts that reports to 345 shown and also contacts that reported to other id that id reported to 345 just like parent child relationship. So I want a result like
2,3,4,5,6,7,8 but not id 9. How to achieve that with mysql?

I have a table that contains a lot of data and its like tree structure.


texts:
I was in a meeting with my boss and technical support leader and they asked me for a database model to a very simple application. That application requires: employees information, some location data, and etc, etc, etc.

One of their database table was:

employee

  id
  name
  lastname
  username
  password

That table suppose to save all employers' info but since I was available to make any changes as I wanted I did as follow:

   Person
      id
      lastname
      ocupation// saved as int. Ex: 1 =employee, 2 =contacts, 3 = bank name, etc, etc

   User
      id
      person_id
      username
      password

My approach was very simple and extensible since now my table person was able to handle all person information despite whether if it is employee or a dentist or what ever. But my boss and a work-mate said that my approach was a bad idea and not correct and too complicated it(which is not), but what if sometimes later the costumer wants to add a contact-list? Now i have to create another table to store contacts info and repeat the columns name,  lastname, etc, etc.

I had implemented this before and it worked perfectly.

The question is: Is my approach so bad? Is there any way to make it better?


texts: Configure Always On to use secondary site as DR

texts:
I have two SQL Server 2017 Standard Edition clusters, each in a different datacenter, only a pair of servers each.

I was able to configure and test the Windows Cluster, Quorum for it, AG, including failing over from primary to secondary, and created one listener for each.

How do you think I can replicate to second DC?

I can't upgrade to SQL Server Enterprise to use Distributed AG.


texts: Postgres INNER JOIN same table failing on CartoDB

texts:
I have a table with data which looks like this

+----+------+-------+
| cp | year | count |
+----+------+-------+
| 1  | 2000 | 10000 |
| 1  | 2001 | 9000  |
| 1  | 2002 | 9500  |
| 2  | 2000 | 8000  |
| 2  | 2001 | 7500  |
| 2  | 2002 | 7000  |
+----+------+-------+

Every cp is guaranteed to have the same number of years. What I want is the percentage change between the current year and a reference year. So I'm after this:

+----+------+-------+-----------+--------------+
| cp | year | count | since2000 | since2000pct |
+----+------+-------+-----------+--------------+
| 1  | 2000 | 10000 | 0         | 0            |
| 1  | 2001 | 9000  | -1000     | -0.1         |
| 1  | 2002 | 9500  | -500      | -0.05        |
| 2  | 2000 | 20000 | 0         | 0            |
| 2  | 2001 | 16000 | -4000     | -0.2         |
| 2  | 2002 | 21000 | 1000      | 0.05         |
+----+------+-------+-----------+--------------+

It's been awhile since I've done much with SQL, but this looks like a pretty straightforward inner join. All I should need is the year 2000 count repeated for every row, and the rest is just math. I got it working using SQLFiddler:

http://sqlfiddle.com/#!15/81a94/1/0

SELECT
*
FROM traffic_counts as s1
INNER JOIN
(SELECT cp, count AS count2000 FROM traffic_counts WHERE year = 2000) s2
ON s1.cp = s2.cp;

But when I try to run this on CartoDB (PostgreSQL 9.3.4) I'm getting an error saying that cp is ambiguous. (Fiddler has no problem…) I even tried aliasing the table to s3 inside the subselect and fully qualifying the first "cp", but the error was the same.

Can anyone help?


texts: What is the benefit of using BOOLEAN over TINYINT(1)?

texts:
From MySQL manual, it says:

  BOOL, BOOLEAN

  These types are synonyms for TINYINT(1). A value of zero is considered
  false. Nonzero values are considered true:

I created a BOOLEAN column with 0 as the default value. Then I update the value to 2. Logically, I would expect MySQL to accept either 0 or 1 since it is a boolean. However, MySQL did not issue an error or prevent me from performing the update.

If BOOLEAN works exactly the same as TINYINT(1), does it make any difference whether I use TINYINT(1) or BOOLEAN?


texts:
How do you go about setting an index to be used for a column comparison like WHERE col1 > col2?

col1 and col2 are of DATETIME type.


texts: How to execute Stored Procedure of MS SQL server through Amazon Lambda?

texts:
I have one stored procedure which is too long and occurs more than a thousand times with different variables. Normal execution on Server takes around 3-4 hours to complete. That is why I want to execute that SP in parallel on the elastic server to speed up the processing.

First execution of SP is independent from second one. So, it is possible to run single SP on thousand thread with different variables. Is there any solution to execute SP in parallel or use Amazon lambda to run it through Node.js API services? Are there any other ways I can execute it in faster way except using elastic server?


texts: Monitor delete statements on a specific table on DB2 LUW

texts:
Background

User is claiming that records are deleted from a specific table, but nobody knows why. They are not sure if it's an application error or done by a user.

Question

Is it possible to monitor / catch / trace delete statements executed against a specific table?

My first idea was to create an event monitor, but I can't limit it to a specific table or a specific kind of statement.


texts: Filtered index throwing error - UPDATE failed because the following SET options have incorrect settings: ANSI_WARNINGS

texts:
While executing proc1, I am getting error as below:

  UPDATE failed because the following SET options have incorrect
  settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use
  with indexed views and/or indexes on computed columns and/or filtered
  indexes and/or query notifications and/or XML data type methods and/or
  spatial index operations.

I know this is happening because I am having a filtered index on customer table
and that requires SET ANSI_WARNINGS ON.

Even we need have some ANSI options which need to be taken care while
creating filtered index and stored proc. I've handled those things.

And I've fixed the issue by setting ansi_warning on in proc1 which is I think is the ideal solution.

But my question is, even after setting all required ANSI options in proc2, why I am getting this error? Any idea?

Below is my sample code:

create Customers
(
id int,
name varchar(100),
[Stats] BIT NOT NULL DEFAULT 1,
)
go

CREATE NONCLUSTERED INDEX [IX_Customers_Stats]
  ON [dbo].[Customers]([Stats])
  WHERE [Stats] = 0
go

CREATE PROC proc1
as
BEGIN
SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;

....
...
.. some code .....
....
...

EXEC proc2 @customerId

END
go

createPROCEDURE [dbo].[proc2 ]
    @customerId INT
AS
BEGIN

    SET ANSI_WARNINGS ON;
    SET ANSI_NULLS ON;
    SET ANSI_PADDING ON;
    SET ARITHABORT ON;
    SET CONCAT_NULL_YIELDS_NULL ON;
    SET NUMERIC_ROUNDABORT ON;
    SET QUOTED_IDENTIFIER ON;

    UPDATE dbo.Customers set Stats = 0 where id = @CustomerId

END


texts: Join 2 tables by closest time, PostgreSQL 9.6


texts: selecting where two columns are in a set

texts:
This might be a silly question, and my suspicion is that I can't do this, but is there a construct in SQL that would allow me to do something like the following:

SELECT whatever WHERE col1,col2 IN ((val1, val2), (val1, val2), ...)

I want to select data where two columns are in a set of pairs.

I'd like to avoid using a subquery, if possible.


texts: Create trigger base on time and insert to another table

texts:
How to create a trigger for my case?

A table has columns id, value and datetime.
I want to create a trigger; when data is more than 1 week old move it to another table (historical); data less than 1 week old will be remain in the current table (live table)


texts: Distribution clean up job in transactional replication removed records but not files

texts:
Distribution clean up job ran without errors according to schedule, but I noticed that the snapshot files were not removed even if when created beyond max_disretention period. Records from msrepl_commands and msrepl_transactions were removed, but the files were not.

immediate_sync = 1
max_disretention = 72 hours


texts:
We are running DB2 on our iSeries, and there is a journal + journal receiver created. The current size of the journal receiver is 200MB (found using the DSPJRNRCVA command). Is there a way to change this value?
If so, how? And what is the minimum that it can be changed to?

Thanks!


texts: Oracle: Physically reorganizing rows of a huge table by a different key

texts:
I have a huge table RECORD -- a few billion rows -- which contains data loaded continuously in a lot of batches. I can assign a primary key RECORD_ID from sequence, or use natural composite key BATCH_NUM + RECORD_NUM, which gives me a nice, unfragmented table where a batch with thousands of records only spans over several physical blocks, which is useful for next batch-aware step of processing.

But later I need to group together rows from arbitrary batches based on another field value, some RECORD_KEY. There are hundreds of millions of different RECORD_KEYs spread across rows of the table, therefore if I want to pick 10 rows with the same RECORD_KEY, it will almost certainly require retrieval of 10 physical blocks. Due to the table size this also cannot easily be cached so it is 10 physical reads (plus overhead on index traversal). This is obviously very slow.

Example:

Table RECORD

RECORD_ID is primary key from sequence
BATCH_NUM and RECORD_NUM are unique constraint (and could be a primary key)
RECORD_KEY is an indexed column

Sample data:

| RECORD_ID | BATCH_NUM | RECORD_NUM | RECORD_KEY
|         1 |         1 |          1 |  987654321
|         2 |         1 |          2 |  876543219
|         3 |         1 |          3 |  765432198
|         4 |         2 |          1 |  654321987
|         5 |         2 |          2 |  543219876
...
| 100000006 |   3000003 |          2 |  432198765
| 100000007 |   3000003 |          3 |  876543219
| 100000008 |   3000003 |          4 |  321987654
...
| 200000009 |   6000004 |          3 |  219876543
| 200000010 |   6000004 |          4 |  876543219
| 200000011 |   6000004 |          5 |  198765432
...

This SQL command will be fast because it will only have to retrieve one physical block:

select RECORD_ID from RECORD where BATCH_NUM = 1

This SQL command will be slow because it will have to retrieve three physical blocks -- one for each retrieved row:

select RECORD_ID from RECORD where RECORD_KEY = 876543219

EDIT:

The above is just an example. Typically I would have:

thousands of rows per BATCH_NUM
tens of rows per RECORD_KEY
thousands of rows per lookup, by BATCH_NUM of RECORD_KEY

SQLs look like this, both retrieving around 1000 rows:

select RECORD_ID from RECORD where BATCH_NUM = 123456

select RECORD_ID from RECORD where RECORD_KEY in (
  select COLUMN_VALUE from TABLE(batch_num_tbl) -- 100 values
)

The execution plans look both reasonable and similar -- using the respective index. However while the BATCH_NUM lookup takes under 1 second to execute, RECORD_KEY lookup takes around 20 seconds.

Options which I considered:

Partitioning: I could do of hash partitioning on RECORD_KEY. It would put the records a bit closer to each other, increasing the chance of them being in the same block, and enabling some partition-wise joins. Might help a bit but will not resolve the problem for me.
Index-organized table: Since it requires primary key, I would have to make primary key RECORD_KEY + RECORD_ID. Also the lookup on BATCH_NUM would then become slow.
Re-inserting the rows: A rather ugly solution, where I would regularly select rows ordered by RECORD_KEY, delete them and insert append them, effectively putting them next to each other.

Please note I am not any experienced DBA, just a developer stuck with this non-trivial DB problem.


texts:
I have a school project in which I have to design a database and my theme is Movie database of sorts. I created a basic ER diagram and I will post a picture here so you can tell me where are my mistakes (I know I have a lot of those)...
I also want to add some new entities like "Genre" but I have a M:M relationship between "Movie" and "Genre" and I'm not sure if I can leave a M:M relationship in a ER diagram. Any advice about what am I doing wrong or how to implement "Genre" entity would be helpful.


texts: Why should a SET TRANSACTION command show a long Duration when the CPU is low?

texts:
I got enlisted to troubleshoot a performance issue on a SQL Server 2016 Enterprise Edition at a customer site and have been handed a SQL Profiler trace captured during a period of "poor" performance. One of my observations is that seemingly innocuous statements like SET TRANSACTION ISOLATION register a duration of the order of seconds.

In the image above, the StmtCompleted event reports a duration as well as a CPU of 0milliseconds but the BatchCompleted event reports a duration of 1907ms (I have translated the profiler duration to milliseconds by dividing it by 1000). Why should there be such a drastic gap in the duration reported by  BatchCompleted event and   the StmntCompleted event ? Also the BatchCompleted event reports a CPU of 15ms but not the StmtCompleted. Why should just sending an ACK back to the client need so much CPU or duration?  There are other statements too which report a high duration on the BatchCompleted with almost 0ms reported against the StmtCompleted event. There are sp_reset_connection events which show a CPU=0ms but high durations of the order of seconds. Ditto with BEGIN TRANS, COMMIT TRANS, SCOPE_IDENTITY etc. And to make complicate matters, there are many instances of the same commands which have executed within  acceptable duration of a few milliseconds during the same period. I am at a loss to come up with an explanation. Wondering if the stack exchange community can come up with a plausible explanation  for these.  The customer swears that the CPU usage & Network Usage as reported at the Operating System level were normal but admitted that the disk usage seemed higher than usual. But most of the commands mentioned above should not be affected by disk activity.

The site unfortunately does not do continuous monitoring. They run a profiler trace whenever they feel that the SQL Server is showing "slowness". Wait stats are not captured either and they periodically reboot the machine.

I am going to suggest that the customer snapshot wait stats in addition to a profiler trace. I am hoping that a very high "ASYNC NETWORK IO" wait shows up :).  Should I request for any other type of monitoring ? Any help appreciated.


texts: What happens if an index build is cut half way through?

texts:
Today I ran a create NC index but stopped the execution after running for 1 hour 46 minutes. The main reason being I started to get worried about the disk space getting lower. It consumed roughly 10 gb of space on a rather expensive storage. Indexes of the db is on its dedicated filegroup and I created this one the same.
Even though I stopped the execution, the 10 gb of space was not released to the OS. The index file has not much free space either. My question is; where has my 10 gb gone?
Thank you,


texts: Remove extra email characters to enforce uniqueness

texts:
Gmail ignores . inside an email address e.g abc@gmail.com and a.b.c@gmail.com are same
Gmail also considers same account if account has any number after + symbol. e.g xyz@gmail and xyz+1@gmail.com are same

I want to put constraint for uniqueness on emails which can handle above case in postgres but not successful so far. Simple unique and saving in lower case doesn't work


texts: Retrieve Names of Check Constraints Specific to a Column

texts:
PostgreSQL 12
When I rename a column in a table, I would like to dynamically rename the matching check constraints as well.
I have a hypothetical table:
CREATE TABLE hypothetical_table (
  id SERIAL PRIMARY KEY,
  some_col text,
  some_col_also text
);

There are constraints:
ALTER TABLE hypothetical_table ADD CONSTRAINT some_col_length_>_5 CHECK(char_length(some_col) > 5);

ALTER TABLE hypothetical_table ADD CONSTRAINT some_col_also_length_>_5 CHECK(char_length(some_col_also) > 5);

I do not know ahead of time what constraints a column may have. I do know that they are prefixed with the column name. But, like some_col constraint above, that prefix may match another column's (some_col_also).
When I rename a column in this table, I would like to rename the constraints as well, to match the new column name.
So, if I were to rename some_col, how can I select all of the check constraints associated with that column?
I have tried joining pg_catalog.pg_constraint with pg_catalog.pg_attribute ON con.conkey = pga.attnum (with conkey unnested), but that returns many constraints that are not associated with that column.


texts: Cannot drop distribution database in SQL Server 2012

texts:
I am trying to drop distribution database but cannot as I am getting this error:

  The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000056000 in file 'E:\XXX_Replication\Data\distribution.MDF'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

The underlying drive on which the .mdf file existed was replaced, thus deleting the .mdf files. Replication is not needed on this server. But this is production server and cannot be restarted.. Please help me to delete this database.


texts:
I am designing a C# client to connect to a MySQL DB and am having second thoughts on my design.

The users have credentials in order login in the C# client, so now it is designed like this:
1 - the C# client has its own username/password to connect to the DB.
2 - the DB has a table "Users" that holds names, passwords and access level.
3 - when the user logs in, what happens is the client logs in the DB using its standard user/pass and queries the "Users" table to check if user's credentials match.

So for the DB it is always the same user that connects to it.

But now I need to log activities such as update/delete/insert, linking them to the user responsible.

The question is:
- what are the benefits and drawbacks of my current design compared to each user having actual DB credentials and the C# client using them to connect?


texts: Corrupted tables after restoring base backups

texts:
I have a task of upgrading the current DB in the server from version 8.2.4 to version 12.4.
I am testing it out in the DEV staging server first.
The steps I have done :
1)Copy the base backup for the old cluster into the DEV server. (The format is tar.gz and this base backup is an automated nightly task in our company).
2)Rebuild postgresql 8.2.4 from source in the DEV server. (I only can upgrade to 12.4 after getting a dump from 8.2.4 and restoring it into the 12.4, but am prohibited from doing this in production directly because it will lock tables, thus the reason of using the base backup instead).
3)Copy the tarred base backup into the new postgresql directory (/usr/local/pgsql824).
4)Untar the base backup.
5)Start the postgresql service.
6)Login to the database to check if all DBs are listed.
7)From step num 6, I can see that all the DBs are there, however to confirm the validity of all tables, I ran the command "select count(*) from <table_name>;".
8)From the count command, there were a few tables from some of the databases that were corrupted. This was the output :
paybills=# select count(*) from dbkl_tx_history;
WARNING:  could not write block 7433 of 1663/16598/9302295
DETAIL:  Multiple failures --- write error may be permanent.
ERROR:  xlog flush request 30/B87D6430 is not satisfied --- flushed only to 30/B87678F8
CONTEXT:  writing block 7433 of relation 1663/16598/9302295

I have done these steps not on one, but two different servers thinking that the first time, the server had a problem with the hardware/disk. However if it even happens the 2nd time, I have 2nd thoughts if this was indeed disk issues or other things. And the 2nd server has just been newly built - it's a VM. Not sure if the disk that is being used by the VM cluster itself is problematic.
How do I investigate the reason, and most importantly resolve the problem? I need to get this done on production server by end of next week, but have no clue how to get this resolved ASAP.


texts: Dynamically convert hstore keys into columns for an unknown set of keys

texts:
I have a database that stores a bunch of custom fields using hstore. In order to merge it into another database that doesn't support hstore, I'd like to split the keys into extra columns.

Users can add new custom fields and so I can't rely on knowledge of the keys ahead of time. Which makes the answer at "Attributes from an hstore-column as separate columns in a view?" not applicable for my problem.

Where a record doesn't have a key present in other records, it should get the same column with a null value.

How do I do this?


texts: Need for reaching data through clustered index with a non-clustered index

texts: I have found that when a table has both clustered and non-clustered indexes (on different columns), the leaf level non-clustered pages, instead of pointing to the data row, point to the node of the clustered index, from where another search is instituted to find the data row. What is the point of this extra level of indirection? If the clustered index has, say, 8 levels, then the indirection from the NCI leaf page to the CI root would have to traverse through these 8 levels to reach the data. Why not store the normal RID in the NCI leaf page so that we can access the data at once without going through the CI index structure?


texts: I am trying to schedule back up in SQL Server 2005 with management option. I am unable to find database name while scheduling a backup with full option. Database name is not over there in selected area. What can be the reason?


texts: SQL - Update certain database records with a loop

texts:
I have a certain IDs in a table that I would like to change a column value from false to true. How can I do a loop that goes through the specific IDs given and changes a column value from false to true?

I’m not very familiar with SQL and the only option that came to mind is through a loop.

This is a sample of my data.

Basically we have a stored procedure to change the “description” of the BetGenerationBetID given from FAILED to SUCCESSFUL example, the below will update the description of BetGenerationBetID 265809596 to SUCCESSFUL

With the above I can only update a record each time. So I want something that I can give all BetGenerationBetIDs that I want to update and changes the description of each ID at once.


texts: Export varbinary to file (image) from multiple rows

texts:
I have a MSSQL 2k8 database, in it I have a table of format below.

Employee Number   |   Segment   |   Data (varbinary(8000))
----------------------------------------------------------
111111            |     1       |    0x01234567...DEF
111111            |     2       |    0x01234567...DEF
111111            |     3       |    0x01234567...DEF

The data (varbinary) column makes up a picture but unfortunately is split in multiple segments by a process I cannot control.

Is there a way to export this data via an SQL script/procedure to a file? I have seem some questions that answer for a varbinary(max) column but I can't for the life of me work out how to stitch these all together into one file.

Note: Some of the files have >500 segments but this procedure will not be occuring exceedingly regularly.


texts: PostgreSQL 12.5/ pgAdmin - postgis. Error: column p.proisagg does not exist

texts:
pgAdminIII reports errors related with a column missing after installing postgis in postgres 12.5 and ubuntu 20.04
Error column proisagg does not exist where perhaps you meant pr.proisagg
people reported this error en previous versions and phpadmin
PostgreSQL 11 error: column p.proisagg does not exist
Solution there does not work
Note /usr/share/phpPgAdmin/classes/database/ does not exist in my installation


texts: Oracle Scheduler Job - Exporting of CSV File every 16:01

texts:
I have already executed the code for scheduler. However, I have a problem whenever I execute my scheduler. Can someone help me with this? I'll appreciate all your response.
My procedure code:
create or replace
procedure csv(p_query varchar2) is
 l_theCursor     integer default dbms_sql.open_cursor;
 l_columnValue   varchar2(4000);
 l_status        integer;
 l_descTbl       dbms_sql.desc_tab;
 l_colCnt        number;
  n number := 0;

 f utl_file.file_type;
 l_line varchar2(32767);

 begin
 f := utl_file.fopen('TMP','mycsv.dat','W',32767);

 dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

 for i in 1 .. l_colCnt loop
 dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
 l_line := l_line ||  l_descTbl(i).col_name||',';
 end loop;
 utl_file.put_line(f,l_line);
 l_status := dbms_sql.execute(l_theCursor);

 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
 l_line := null;
 for i in 1 .. l_colCnt loop
 dbms_sql.column_value( l_theCursor, i, l_columnValue );
 l_line := l_line || l_columnValue ||',';
 end loop;
 utl_file.put_line(f,l_line);
 end loop;
 utl_file.fclose(f);
 end;
/

My batch procedure code:
BEGIN
  dbms_scheduler.create_job (
  job_name        => 'WORKFLOW_REPORT',
  job_type        => 'PLSQL_BLOCK',
  job_action      => q'{csv(p_query=>'select * from reports_sample');}',
  start_date      => SYSTIMESTAMP,
  repeat_interval => 'freq=daily; byhour=16; byminute=1; bysecond=0;',
  enabled         => TRUE);
END;


texts: Schema design for products with different sizes?

texts:
I have My Products Table With Many to many relations with variants table

------------------------------
| ID | variant name          |
|----------------------------|
| 1  | XL                    |
| 2  | small                 |
| 3  | custom                |
------------------------------

I need if I user Choose custom he should add

data for custom
-------------------------------
| length | shoulder_width | chest_size | hip_size | waist_size | sleeves_length| sleeves_width | trouser_length
|---------------------------------------------------------------------------------------------------------------------------|
| null   | null           | null       | null     | null       | null          | null           | null
|---------------------------------------------------------------------------------------------------------------------------|

every user when adding product and choose custom will add all of them or tow or three anyone he like

so how I will make database so every user when adding a custom variant can enable and disable what he likes and how in user area will add data for variant and save it any idea how to make this without Complicate everything


texts: Vendor Wants to Run MSDB job every 5 minutes for Business Application

texts:
We have a 3rd party vendor trying to integrate 2 different applications where both DBs reside on our SQL Server instance with 150+ other DBs, and they want to create a MSDB job to "synch" the 2 different applications every 5 minutes (at first they wanted to run it every minute).

My initial hunch is that they should instead do this somehow in the Application tier with a Windows scheduled job, or perhaps even a dreaded trigger (which we typically resort to in situations like this).

I prefer to keep MSDB jobs reserved for DBA tasks as much as possible to reduce the clutter there, and have also run into slow querying of MSDB when viewing job histories with super active jobs like this (which also drown and drop out important job histories of more important things like backup histories).  But then again, maybe my preferences are wrong and I need to make some space for the Application tier in MSDB and roll up my sleeves and fix problems of job histories taking forever to load when I need to retain a lot more history entries to capture the important stuff like backups (or purge the hyper active job entries).

Another issue I have is that now I need to give this vendor "sysadmin" rights instead of only "dbo" rights on only their DBs when they perform their upgrades via their GUI and hope they don't blow up the instance where my mission critical DBs are (one of the downsides of consolidation).

I guess I can put them on another "isolated" instance where we put all the vendors that don't play nice, but then we need reconfigure the Applications to point to the new SQL instance (sigh unfortunately not trivial in this case).

The vendor already pushed back on my concerns talking about how bad triggers are.  So, I "googled" on this a bit and came up empty.  Has anyone seen any link out there "authoritative looking" that this is a bad idea and I can refer them to it?  Or should I embrace their approach?

I don't believe I've ever posted in a sql forum before asking for help, so hopefully my inquiry is properly framed.

EDIT:  We're running SQL Server 2008 Enterprise R2 x64 SP1  (Thanks for pointing out that I forgot to mention version!).  Hmmm, hopefully they don't need to change their MSDB upgrade scripts when we go to a newer version.

Thanks for your time!
Rich


texts: SQL Server collation mismatch reducing the amount of data visible to an application?

texts:
One of our servers died recently, it was rebuilt with a wrong collation and so it looks as follows:

SQL Server - SQL_Latin1_General_CP1_CI_AS
master db -  SQL_Latin1_General_CP1_CI_AS
model db -   SQL_Latin1_General_CP1_CI_AI
msdb db - SQL_Latin1_General_CP1_CI_AI
temp db - SQL_Latin1_General_CP1_CI_AS
every user db - SQL_Latin1_General_CP1_CI_AI

The databases are accessible, my question is: will the data visible to users via an app be limited because of the mismatching collation? Some users have said that they think they have lost a lot of data even though the database was restored from a very recent backup.

It is SQL Server 2008 R2 with SP3 on Windows Server 2012 R2 (compatible only due to service packs)

Thank you


texts: Oracle Custom Sequence of a partitioned group (Insert trigger)

texts:
Please help, need an insert trigger to insert the group_ID (next sequential number) of a partitioned group.  Much like a dense_rank of repeating group of names.
When a new record is created, if the name exists, then the group_id will be this name's group_id.  If the name does not exist, then the group_id will be auto generated to a new number that hasn't been used.

Not sure what this is called.

Create table T1 (
Group_Name Varchar2(50)
,Group_ID Number
);
commit;

Insert into T1 (Group_Name, Group_ID) Values ('ABC', 1 );

Insert into T1 (Group_Name, Group_ID) Values ('ABC', 1 );

Insert into T1 (Group_Name, Group_ID) Values ('ABC', 1 );

Insert into T1 (Group_Name, Group_ID) Values ('EFG', 2 );

Insert into T1 (Group_Name, Group_ID) Values ('EFG', 2 );

Insert into T1 (Group_Name, Group_ID) Values ('HIJ', 3 );

Insert into T1 (Group_Name, Group_ID) Values ('HIJ', 3 );
COMMIT;

SELECT * FROM T1;

GROUP_NAME  GROUP_ID
----------  ---------
ABC         1
ABC         1
ABC         1
EFG         2
EFG         2
HIJ         3
HIJ         3

Need An insert trigger to insert the GROUP_ID based on this logic:

if the inserted value for GROUP_NAME already exists in T1, then GROUP_ID gets its already defined GROUP_ID for GROUP_NAME
if GROUP_NAME does not exist in T1, insert the next sequential number for a this new "Grouping" for GROUP_ID.

Insert examples and what GROUP_ID should be are below:

INSERT INTO T1 (GROUP_NM) VALUES('ABC');

*** Since 'ABC' is found in the table with Group_ID of 1, the insert trigger, should evaluate and update the table of GROUP_ID to 1.

INSERT INTO T1 (GROUP_NM) VALUES('PEP');

*** Since 'PEP' group name inserted is not found in the table T1, the trigger should auto generate the next value of 4 for the GROUP_ID.


texts:
I have the following table

CREATE TABLE words (
  id bigint(20) NOT NULL,
  `text` int(11) NOT NULL,
  w1 int(11) NOT NULL,
  w2 int(11) NOT NULL,
  w3 int(11) NOT NULL,
  w4 int(11) NOT NULL,
  w5 int(11) NOT NULL,
  w6 int(11) NOT NULL,
  w7 int(11) NOT NULL,
  w8 int(11) NOT NULL,
  w9 int(11) NOT NULL,
  PRIMARY KEY (id),
  KEY w1 (w1),
  KEY w2 (w2),
  KEY w3 (w3),
  KEY w4 (w4)
) ENGINE=MyISAM DEFAULT CHARSET=ascii;

that contains 2,124,377,011 rows.

Why this simple query

SELECT w1 FROM words WHERE w2=3112

takes 50 sec on Intel Xeon E3-1230 v2 at 3.30 Ghz Server with 7200 RPM HDD?

Is this normal or am I missing something?

UPDATE: It returns 60243 rows, the execution plain is

+----+-------------+-------+------+---------------+------+---------+-------+-------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows  | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+-------+-------+
|  1 | SIMPLE      | words | ref  | w2            | w2   | 4       | const | 31132 |       |
+----+-------------+-------+------+---------------+------+---------+-------+-------+-------+


texts: How can I change the target server type for a trace template?

texts:
I've got a .tdf SQL Server Profiler trace template someone wants me to run but the template is targeting SS 2008 R2. While my SSMS is 2008 R2 the server I need to trace is SS 2005. When attempting to trace the server the server type is locked (generated from the actual server) so I can't just select the template I need while it's marked as a different SS version..

How can I change which server type the trade template is targeting? I've tried File > Templates > Edit Templates... but there doesn't seem to be an option to change this. Is it possible to change the target server version or does the whole trace have to be made again from scratch?


texts: I believe that PostgreSQL can log slow or unsuccessful queries. Probably I can set also PostgreSQL to log all queries executed. Contrarily, I am interested to know if there is a way that a malicious attacker can get access to all the queries successfully executed on the PostgreSQL server, if I have disabled logging as much as possible. Is this possible? Or once a query has been successfully executed (might be a SELECT or UPDATE query) I can be 100% sure that the DB server has no memory of successfully executed queries and therefore no one else can get access to this information. I am using PostgreSQL 9.3.


texts: Missing Transaction log in chain, Possible to skip

texts:
We have had one of our clients SQL Servers crash and need to restore it to the most recent possible time. We have a full backup from Monday and transaction logs that run every hour up until this morning at 02:01, however we are missing one transaction log from a database so we are only able to restore this database to Wednesday.

Is it possible to skip a transaction log and continue recovering the database to the most recent date with the remaining logs?

EDIT: there were no changes to the database done at the time we are missing, so the transaction log would have nothing in it


texts: Migrate schema updates onto existing schema

texts:
So we have three development environments from a programming point of view.
Each environment has its own postgres database.

Let's say a table gets an extra column in our development environment, we need to update the acceptance environment as well when the code is merged back. Other changes can be column renames etc.

This is needed because we are still developing the app quite heavily, but it is still important that the production environment can keep up on a database level as well.

So the question is: how can I propagate schema updates to a different database without loss of data.


texts:
I am trying to import from 8 files and insert into a single table in db2. I am running 8 .sql files that will do the task, but only one of them actually seem to be running and inserting into table.

Is there a way, I can make 8 inserts happen in parallel into the same table so that it completes faster ?


texts: interpreting how much data has been lost by analyzing dbcc output

texts:
We had a SAN outage and this corrupted several databases with no backups.

We ran this to do a repair:

EXEC sp_resetstatus 'test'

ALTER DATABASE test SET EMERGENCY

DBCC CheckDB ('test')

ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('test', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE test SET MULTI_USER

It ran for approximately 4 hour and generated a huge output:

http://pastebin.com/qXf0DXd4  (very slightly truncated)

Here's a preview:

Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:6769) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
        The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:6770) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
        The error has been repaired.
Msg 8909, Level 16, State 1, Line 1

1. is this enough info to interpret how much data lose there was?
2. if that's not enough info, can i use the logs to determine how much and WHICH data was lost?


texts: Automating Redshift data dump to a PostgreSQL DB every hour

texts: GIS analyst with minimal experience of this back-end stuff but a big task at hand. I have a redshift database that changes multiple times a day. I want to use this "live" data to make web maps and I have a Postgres DB where all my web-mapping data is stored. I want to continuously dump data from the ever-changing Redshift DB to my maps. What is the simplest way to do this? I have experience with SQL so if that's the route it would be helpful.


texts: Import errors importing CSV to SQL Server table

texts:
I am using SQL Server 2014 Management Studio to import a very large .CSV file into a table.

So far I have been trying to accomplish this by right-clicking on the database and selecting Import Data, then using the wizard to import from a flat file.

The problem I am having is that every time I try to import I either run into truncation errors (some of my fields exceed nvarchar(max)) or that there are missing fields on some rows.

How can I set the import to just ignore a failed row, instead of failing the entire operation. I've looked everywhere in the options but cannot find where to set this.


texts: How to get Kerberos working against a named SQL Server instance in Power BI Report Server?

texts:
I have a Power BI Report Server working perfectly connecting to SQL Server machines with a default instance only. I can also connect to SSAS named instances with the proper SPNs and by allowing Kerberos constrained delegation from the Power BI service account to the MSOLAPSVC.3 service (SQL Server Browser). I believe I have all proper SPNs set up properly as I can run linked servers and query just about anything from anything. Kerberos Configuration Manager shows green check marks for everything.

My only problem is connecting to named instances of the SQL Server engine (not SSAS). I get the error below, which you would normally get if SPNs are not registered properly.

  DESCRIPTION:  Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
  Reason: Could not find a login matching the name provided. [CLIENT:
  x.x.x.x]

Is there a difference in how named instances must be connected to from Power BI (and perhaps SSRS as well)? I am trying to do a DirectQuery against a remote named instance of SQL Server in a .pbix file in my environment.


texts: How to serialize on SQL server metadata?

texts:
I tried to find an answer in Kalen Delaney's SQL Server Concurrency but there is no mention of metadata.

The problem is how to ensure that only one of these reads and index creates executes from one connection before the other connection does the same .

if not exists (select * from sysindexes with (XLOCK) where name = 'IND_audittrail_date_time')
CREATE CLUSTERED INDEX IND_audittrail_date_time ON audittrail(date_time)

-- because we get

  java.sql.SQLException: The operation failed because an index or
  statistics with name 'IND_audittrail_date_time' already exists on
  table 'audittrail'.

Query:

if not exists (select * from sysindexes with (XLOCK) where name = 'IND_audittrail_date_time')

Reading Metadata Access, Isolation Levels, and Lock Hints
I found this

  SQL Server does not guarantee that lock hints will be honored in
  queries that access metadata through catalog views, compatibility
  views, information schema views, metadata-emitting built-in functions

Is there an accepted way how this is done? Thank you.


texts:
I have seen the following query in the PostgreSQL documentation. Unfortunately I don't understand the join criteria.

create temp table tmp_stat_user_tables as select * from pg_stat_user_tables;

select * from pg_stat_user_tables n
join tmp_stat_user_tables t
on n.relid=t.relid
and (n.seq_scan,n.idx_scan,n.n_tup_ins,n.n_tup_upd,n.n_tup_del) <>
(t.seq_scan,t.idx_scan,t.n_tup_ins,t.n_tup_upd,t.n_tup_del);

How I need to understand the JOIN clause?

What the tables are for and what the first query is doing are clear for me.


texts: Oracle 12c - Installing SW with an Active Directory Account on RHEL7

texts:
My client insists on installing Oracle using an Active Directory Account as the SW owner instead of creating a local oracle service account on RHEL7.

I'm opposed, but willing to be flexible to meet their security and UID concerns.

Has anyone done this successfully?  Any Caveats?


texts:
I have posted a similar question before, but this one is a little modified.

I have a table with 4 columns as shown below.

ITEMCHARGE     CPT4       EFF_DATE    CPT4MOD
326.8         29075       20101204    GO
326.8         29075       20110104    GO
326.8         29075       20110204    GO
326.8         29075       20110406    GO
352.69        29075       20090611    GO
352.69        29075       20090917    GO
352.69        29075       20100614    GO
352.69        29075       20100722    GO

Now, if CPT4 and CPT4MOD match, I need to keep the rows with distinct ITEMCHARGE (with latest EFF_DATE). The output should be like below:

    ITEMCHARGE  CPT4    EFF_DATE    CPT4MOD
       326.8    29075   20110406    GO
       352.69   29075   20100722    GO

There are 2 distinct ITEMCHARGE for the same CPT4 and CPT4MOD. So we took the latest EFF_DATE row for both of them. It would be very helpful If I can get a linq (with lamda) and SQL version for this.


texts: SSAS Deployment Wizard - Issue with multiple connection strings

texts:
I've observed an issue with the SSAS Deployment Wizard.

I have two environments DEV and PROD.
A few weeks ago when I deployed anything on PROD I had one connection string only there to PROD (similar to this screen):

But today I've observed that the SSAS Deployment Wizard window looks like this:

Multiple connection strings to DEV and PROD.

Deployment went successfully but when I've tried to process the tables in the SSAS Management Studio I get an error like below:

Any ideas how to solve this issue ?
Is there any method to keep only one connection string to PROD in the DeploymentWizard instead of multiple to DEV and PROD ?


texts: Can I use mirroring to make a copy of my Database?

texts:
We are moving a database off of a older physical server and putting it onto a Virtual Machine (SQL 2005 to 2014 I hope).  I need to copy a single database from the physical to virtual with as little downtime as possible. Originally I was just going to do a full backup and then restore on the new machine, but that will take almost an hour on this database.

My question is: If I can get the new virtual machine setup before the downtime, would it be effective to setup a database mirror from the old Physical box to the new Virtual box?  Then when the downtime came I would cut the mirror (database should be in sync) and realias the server to be the same name as the old physical database server?

I have never had to use mirroring so I was not sure if this was a "Smart" way to do it. If it would work the only downtime would be the name change.


texts: SQL SERVER 2016 side by side with previous versions

texts:
I know that SQL SERVER 2016 can co-exist with previous versions on the same server. I wonder however if there are any gotchas that I should be aware of?
We have a test server that already has  2008r2, 2012 and 2014 instances on it. Is there any chance that there are anything on the existing instances that will behave differently if we install 2016 also on the same server?


texts:
I have two servers that need to be synced. One is online and has a public IP address. The other is internal and has no public IP address - nor is there a way to get it a public IP so that's off the table.

I'd like to set up replication between the two servers through MySQL but as I understand it, the MySQL slave needs to be able to connect to the master (i.e. the master doesn't just simply push data out) and that's a problem since there's no public IP or interface to connect to.

Is there any way to replicate and simply tell the master to push data to the slave (which it can access)? Alternatively, is there some other solution? I have considered a mysqldump periodically but the data is going to grow very quickly resulting in too-large dump files.


texts:
Why the MySQL is saying that can use about 16GB of RAM even if my server has only about 2GB?

# ./mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.34-cll
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 457M (Tables: 1361)
[--] Data in MRG_MYISAM tables: 53M (Tables: 36)
[--] Data in InnoDB tables: 792M (Tables: 1133)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 175

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 54s (2K q [37.537 qps], 325 conn, TX: 1M, RX: 354K)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 1.7G global + 18.6M per thread (800 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 16.2G (874% of installed RAM)
[OK] Slow queries: 0% (9/2K)
[OK] Highest usage of available connections: 1% (13/800)
[OK] Key buffer size / total MyISAM indexes: 128.0M/78.9M
[!!] Key buffer hit rate: 73.9% (1K cached / 297 reads)
[OK] Query cache efficiency: 42.4% (388 cached / 915 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 148 sorts)
[OK] Temporary tables created on disk: 21% (114 on disk / 537 total)
[OK] Thread cache hit rate: 94% (17 created / 325 connections)
[OK] Table cache hit rate: 99% (2K open / 2K opened)
[OK] Open file limit used: 1% (2K/200K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[OK] InnoDB data size / buffer pool: 792.7M/1.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries

And buffer related settings

mysql> SHOW VARIABLES LIKE '%buffer%';
+------------------------------+------------+
| Variable_name                | Value      |
+------------------------------+------------+
| bulk_insert_buffer_size      | 8388608    |
| innodb_buffer_pool_instances | 1          |
| innodb_buffer_pool_size      | 1073741824 |
| innodb_change_buffering      | all        |
| innodb_log_buffer_size       | 8388608    |
| join_buffer_size             | 16777216   |
| key_buffer_size              | 134217728  |
| myisam_sort_buffer_size      | 8388608    |
| net_buffer_length            | 16384      |
| preload_buffer_size          | 32768      |
| read_buffer_size             | 131072     |
| read_rnd_buffer_size         | 262144     |
| sort_buffer_size             | 2097152    |
| sql_buffer_result            | OFF        |
+------------------------------+------------+
14 rows in set (0.00 sec)


texts: Documenting where stored procs are called from

texts:
I've been wishing lately that we documented what all places stored procedures are called from. It would have to be pretty painless to do though... i.e. doing it in excel wouldn't be very good.

Seems like this might be a common desire (probably even a requirement for some companies) so I'm wondering if there are any tools/applications/add ins out there already that facilitate this?

EDIT: To be clear, I'm not really looking for tools that help me identify what needs to be documented (although I would still love to hear about any you think I may be unaware of), but rather a tool that helps me do the documenting.


texts: How can I check the status of transactional replication?

texts:
How can I check if there are commands waiting for being executed in the subscribers and find if there is any error occurring in my publication.

I want to be able to do this programmatically so the replication monitors is not an option.

The objective is to give some feedback to a C# program, so that the program can adapt his behaviour, depending on the possible problems.


texts: Postgres client copy (\copy) command doesn't have access to a temporary table?

texts:
I'm generating a list of SQL commands to export some data that I ultimately run using psql -f. The queries all get the same subset of data, so I thought I'd factor the qualifications out and put a list of eligible user ids in a temporary tables like so

create temporary table tmp_export_users as (select id from users where ...)

then refer back to that in my \copy commands like

\copy (select ... from table where user_id in (select id from tmp_export_users)) TO 'filename.csv' WITH CSV HEADER

Those are all in the same file, one per line, and running them -f I get the error that the copy commands can't see the temporary table, so I'm guessing that the client copy command must not actually use the same postgres session as psql.

Is that correct? Is there a way to change that behavior?


texts: Global command to destroy all disconnected data files in Oracle 11g

texts:
Is there a global command to delete/purge all disconnected .bdf files in an Oracle 11g R2 database?

Here is the scenario:

Every user-defined user and user-defined tablespace was deleted from an Oracle 11g R2 database by successfully executing the following commands for each and every user-defined user and tablespace:

DROP USER username CASCADE;
DROP TABLESPACE tablespacename INCLUDING CONTENTS AND DATAFILES;

However, the folder containing the .dbf files corresponding with all the dropped tablespaces still contains all the same .dbf files, each with the same original file size.  It is thus as if the data has simply been detached from the database without destroying the underlying datafiles.

I hesitate to simply delete all of the corresponding .dbf files out of fear of side-effects.  So is there a global command in Oracle 11g R2 that will enable an administrator to safely delete all unused data files that were once connected with the Oracle 11g R2 instance?

I am using SQL Developer and SQLPlus, so any command or utility either of these tools could work.


texts: How to speed up full text query on a table with 4 million rows? (MariaDB)

texts:
I have an InnoDB full-text table that serves the Ajax-powered search box at the top of my website.  I generate it with a daily script that pulls data from a dozen entity tables on the site and amalgamates them all into one FT table for searching.
To give users the best experience (IMHO) I take whatever their input is, clean certain characters out of it (all full-text modifiers, for example), and then prepend every term with + and append them all with *.
So a search for "stack overflow" becomes +stack* +overflow*
The column that I'm searching on the FT table is small, with a typical character length of 30 characters.  Event names, people's names, geographical locations, that sort of thing.  Not huge passages of text.
It works, but queries take on the order of 1 second to be returned.
EDIT: just after posting I've rebuilt the index and it's down to 0.4 seconds now - but I'd still like to improve it, if possible.
How could I change that to 0.1 seconds, or is that a pipe dream?
My server is a dual Xeon with 16 cores/32 threads and 128GB of memory.  I serve a million pages or so each month, and rarely see server load above 1-2, with plenty of spare memory.
I wonder if I can somehow force this table to reside permanently in memory (rebuilding it after a server reboot or MySQL restart only takes 30 seconds or so), and if that would help?  Or maybe MySQL is already holding it in memory - how can I check?
I'm happy with the query itself, I don't think there's much that I can improve about it, but I know very little about how to maximize server potential through configuration.
FWIW SELECT VERSION() gives me 10.3.20-MariaDB-log.


texts: pgAdmin 4 v2.1 download - could not verify .sig file

texts:
I downloaded the installer and .sig file for the latest pgAdmin 4 v2.1 from their website, given the recent malware/security issues I thought best to verify the installer using Kleopatra (part of Gpg4win) before doing anything, but got this message:

I have emailed the maintainer as listed on the download page but yet to hear back from him. Does anyone know if this is a cause for concern? I will update this post if/when I hear back from the maintainer.