texts: How to get all records where a joined column contains some value (at least once)

texts:
Forgive my horrible description in the title. I have a table called timesheet which has one record per week per employee. There are many rows in timecard_entries for a timesheet. A timesheet can have multiple approvers because each timecard entry is connected to a project. What I need is to get all timecard entries on a timesheet that contains an approver.

Here is my query currently:

SELECT a.timesheetid AS sheet, b.timecardid AS entry, f.ControlsLead AS approver
FROM timesheet a
LEFT JOIN timecard_entries b ON a.timesheetid = b.sheet
LEFT JOIN sub_projects c ON b.subprojectid = c.subprojectid
LEFT JOIN tblCustomerPOItems d ON c.lineid = d.CUSTOMERPOITEMID
LEFT JOIN tblCustomerPOs e ON d.CustomerPO = e.CUSTOMERPOID
LEFT JOIN tblProjects f ON e.ProjectNum = f.PROJECTNOID

This returns:

sheet    entry    approver
1        68       1
1        69       3
1        70       3
1        71       3
1        72       3
1        73       1
1        74       1
2        97       1
2        98       1

So, in this case, if I want records with approver 3 I am looking to get all entries with timesheet = 1 because it contains at least one record with approver = 3

Required output:

sheet    entry    approver
1        68       1
1        69       3
1        70       3
1        71       3
1        72       3
1        73       1
1        74       1

Using the logic from the accepted answer the new query is:

SELECT a.timesheetid AS sheet, b.timecardid AS entry
FROM timesheet a
LEFT JOIN timecard_entries b ON a.timesheetid = b.sheet
WHERE a.timesheetid IN
(
    SELECT z.timesheetid FROM timesheet z
    LEFT JOIN timecard_entries y ON z.timesheetid = y.sheet
    LEFT JOIN sub_projects x ON y.subprojectid = x.subprojectid
    LEFT JOIN tblCustomerPOItems w ON x.lineid = w.CUSTOMERPOITEMID
    LEFT JOIN tblCustomerPOs u ON w.CustomerPO = u.CUSTOMERPOID
    LEFT JOIN tblProjects t ON u.ProjectNum = t.PROJECTNOID
    WHERE t.ControlsLead = 3
)


texts: How can I identify the number of times a View is called in SQL Server?

texts:
How can I identify the number of times a View was called in SQL Server?

I am looking for an answer that is similar to this below which shows how it can be done for stored procedures.

How can I monitor the call count per stored procedure per timespan?


texts:
Is it possible to have an unique index on multiple columns, of which a column with type enum is part of?

Let's say I have 3 columns, which are an unique index, combined:

date
type
companyId

date is just a date, companyId is an integer, but type is an enum, which could be week, day or month. Reason to have this, is because the table contains imported statistics. A company has only one row per date, per type. The index is to prevent data will be imported twice, accidentally.

I tried the following which did not work:

ALTER TABLE `stats` ADD UNIQUE INDEX `unique_index` (`companyId`, `date`, `type`);

The error I received was:

  MySQL said: Duplicate entry '288837-2017-05-15-' for key 'unique_index'

As you can see the key doesn't contain the type, only the companyId and date


texts: Safe method to rename a crashed myisam table

texts:
I have a large MyISAM table which has crashed. Repairing the table will take some time. The table only INSERTed to and SELECTed from, never updated. To allow the application to continue working, albeit with reduced capability, I thought of

renaming the crashed table
creating a new table with the original name
switching processing back on
repairing the backup table
switching off processing
merging the repaired and new data
switching on processing

The other steps in this process do not pose any risk due to the nature of the application.
Is it safe to rename a crashed MyISAM table? How?

I believe that I can't simply do ALTER TABLE...RENAME.... as this always does a row-by-row copy into a new table.

Apparently Peter Zaitsev uses a "tiny script which moves out all MyISAM tables out of MySQL database directory" but doesn't seem to give details of what this script does (presumably stops database first?).


texts: LAST_DDL_TIME Not Changing after compilation of package body

texts:
On Nov 24 th,  we created the package body. Again today we recompiled it, but last DDL date has not changed. Is there any reason for this?

Below is the query I'm using

SELECT object_name, object_type, created, last_ddl_time, status
FROM ALL_OBJECTS
WHERE OBJECT_NAME = 'xxxxxxxxx';


texts: Postgres Backup fails with: pg_dump: could not fsync file: Bad file descriptor

texts:
I am running Postgres 11 on Windows 10 and this should be my last backup before moving to Linux:

"C:\PostgreSQL\pg11\bin\pg_dump.exe" -U postgres -h localhost -p 5432 --verbose --jobs=4 --format=d --blobs --clean --create --if-exists --compress=0 --dbname="DATABASE_NAME" --file="P:\PG11_dump_20190217"

This is the end of the log:

...
pg_dump: dumping contents of table "schema.table"
pg_dump: finished item 255955 TABLE DATA table
pg_dump: finished item 255956 TABLE DATA table
pg_dump: pg_dump: dumping contents of table "schema.table"
dumping contents of table "schema.table"
pg_dump: finished item 255953 TABLE DATA table
pg_dump: finished item 255952 TABLE DATA table
pg_dump: could not fsync file "P:\PG11_dump_20190217/291267.dat": Bad file descriptor
pg_dump: could not fsync file "P:\PG11_dump_20190217/293909.dat": Bad file descriptor
pg_dump: could not fsync file "P:\PG11_dump_20190217/294060.dat": Bad file descriptor
pg_dump: could not fsync file "P:\PG11_dump_20190217/295715.dat": Bad file descriptor
pg_dump: could not fsync file "P:\PG11_dump_20190217/296142.dat": Bad file descriptor

I searched for the pg_dump: could not fsync file: Bad file descriptor error but could not find any help.

Maybe of importance: The database is about 3.6 TB and I am using the PostGIS extension.

Question: How to prevent this error or work around it?

Additional question: Could I do a file level backup and copy the data folder from windows to linux, given an identical Postgres version and both servers stopped?


texts:
I have a CSV file on about 200 MB/1 mill rows, which is updated every night. I need the values from this file available in a MySQL table.

I get this working by using LOAD DATA INFILE filepath REPLACE INTO TABLE tablename.
This is fast and it keep my indexes.
The problem is that values which are no longer in the csv will not be deleted.

Another attempt has been to TRUNCATE the table, and then LOAD DATA INFILE filepath INSERT INTO TABLE tablename afterwards. This seems to be about 25% faster. But by doing this I've had a lot of trouble with indexes being disabled(?) or stopped working. I've been reading on the MySQL docs and SO for some days now, but I can't figure that out.

My next attempt has been similar to the first. I added a column "update", and set every row to update=0 before doing a REPLACE INTO with a SET update=1, and then delete all remaining rows with update=0. This is my current method.

My question is this: Which method is considered better for completely replace a table with info from csv, but keep/refresh indexes. I'd appreciate any advice on the subject.

I am using the MyISAM engine.


texts: Achieving "sequence number", rather than dateTime, for updates; postgres specifically

texts:
So a postgres database (specifically, an AmazonRDS instance fwiw).

It's easy enough to arrange things to get the last updated (or created) time for any record.

That information would be a datetime.

What I want to achieve: imagine an integer, sequenceNumber, which is atomic and global to the database.

Simply, every table would have a sequenceNumber field.

Any time any record (ie, in any table) is updated (or created), it gets the next sequenceNumber.

You know how you have programmer friends who know nothing about databases. Wave. My idiot solution would be, in the entire codebase, every time you create or modify a record, be sure to remember to update sequenceNumber on it. (Of course, just have one global system that gives the next atomic sequenceNumber.)

What's the sound solution for this?

Indeed, is it completely available already in some way I don't know, or?


texts: SSRS Matrix Report - Count for NULL data

texts:
I have a SSRS Matrix Report for data quality in our warehouse. We use this report to display invalid values in specific fields during specific time period.

For example, Missing/Invalid Postcode,
My SP lists all the users with Missing or Invalid postcode in our system. I list this users based on their activities in past 6 months. At Front-end, I am using Matrix report to display the count of Users.

Another Validation field is Missing Birth-date, if in last 6 months there isn't any user without birth-date, my SP returns a row with Validation Field as "Missing Birth-date" but the activity date will be null as there isn't any activity time. (This is the result of my left outer join on Validation Fields Table. Please refer to attached ERD for more details)

If it appears at least once in last 6 months, count becomes 0 for other five months automatically but what I want to do is display 0 as Count for "Missing Birth-date" validation criteria but currently this specific criteria doesn't appear on the report at all.

Does anyone have any idea on How I can achieve this?

Thanks,

Maulin.


texts: Connect to SQL Server behind NAT from remote machine

texts:
The scenario is that this is a SQL Server behind a corporate firewall with your standard setup to allow only minimal outbound traffic and zero inbound.  However, port 80/443 are open, per the usual allowance.

Has anyone tried, or is it possible to

Connect to a remote machine port 80, say using Telnet.
On the local machine, note the bound address and port [1], e.g. netstat
Disconnect from telnet.  Start port forwarder and listen on [1], mapped to SQL Server TCP/IP listener
On remote machine, connect to SQL Server on [1]

What do you think?  I just need to get some testing done and I don't want to move my huge database outside for this one-off.  I am familiar with and have used UDP hole punching for NAT traversal, mainly for push to mobile phones, but am interested to know if this works with TCP as well.

References:

TCP Hole Punching
UDP Hole Punching
TCP Hole Punching #2

I am open to other suggestions.


texts: DB2 equivalent of Oracle DBMS_LOB.SUBSTR etc

texts:
I have to migrate an Oracle stored procedure to DB2 LUW 10.5. It uses DBMS_LOB.SUBSTR() function on a BLOB parameter that receives an array of bytes from the client code.
What is its equivalent in DB2? SUBSTRB seems fitting, but I am not sure.


texts: SQL Server 2014 - Add Node inherits wrong configuration

texts:
I'm setting up a simple 2-node failover cluster with SQL 2014 on Server 2012 R2 in Core mode (no GUI). I have the cluster configured with identical machines, and I have the first node configured and working.

When I go to add the second node however, the wrong configuration information is used. Specifically, the shared and instance binary locations point to the OS drive (I installed them on a different drive on node 1). I tried manually specifying the locations in the configuration file, but setup won't accept these parameters when the install mode is AddNode. Did I do something wrong, or is this a bug?


texts: Composite Primary keys and auto increment?

texts:
i'm developing SaaS app with multi-tenancy, and i've decide to use single DB (MySQL Innodb for now) for client's data. I chose to use composite primary keys like  PK(client_id, id). I have 2 ways here,

1: increment "id" by myself (by trigger or from code)

or

2: Make "id" as auto-increment by mysql.

In first case i will have unique id's for each client, so each client will have id 1, 2, 3 etc..
In second case id's will grow for all clients.
What is the best practice here? My priorities are: performace, security & scaling. Thanks!
Same question at stackoverflow with more answers


texts: logical replication in postgresql - "server closed the connection unexpectedly"

texts:
I'm running two postgresql servers and I'm trying to setup logical replication.

These are extra bits of configuration I added on master in order to make replication work:

wal_level=logical

max_replication_slots=10
max_wal_senders=10

# maximum wait time in milliseconds that the walsender process on the active master
# waits for a status message from the walreceiver process on the standby master.
wal_sender_timeout=600000
#wal_receiver_timeout=180000

These are extra bits of configuration on slave:

wal_level=logical
max_replication_slots=2
max_logical_replication_workers=4

wal_receiver_timeout=1200000

I have appropriate entries in pg_hba to provision access for replication user.

I followed postgresql guide and everything seems to work fine - that is I can see in log tables are replicated one by one. As soon as initial replication is completed I can see changes from master are applied on slave.

However at some random point I see this log on master:

2019-08-21 11:52:37.183,(...),"starting logical decoding for slot "my_slot_name","Streaming transactions committing after D35/AD4F438, reading WAL from D35/A1D85C0.",,,,,,,,"my_slot_name"
2019-08-21 11:52:37.184,(...),"logical decoding found consistent point at D35/A1D85C0","Logical decoding will begin using saved snapshot.",,,,,,,,"my_slot_name"

This is then immediately followed with this log on slave:

2019-08-21 11:52:36.932 [4411] ERROR:  could not send data to WAL stream: server closed the connection unexpectedly
                This probably means the server terminated abnormally
                before or while processing the request.
2019-08-21 11:52:36.977 [14438] LOG:  logical replication apply worker for subscription "my_subscription_name" has started
2019-08-21 11:52:36.987 [27972] LOG:  background worker "logical replication worker" (PID 4411) exited with exit code 1

I have done some reading and found this mailing list thread and based on advice I increased value of wal_sender_timeout on master from 60 seconds to 300 seconds initially. However the problem still seems to persist...

Also each time I try something new I have to reinitialize replication from scratch which takes lots of time, I was wondering if I can point slave on WAL archives from master so it would replay missing WALs..?

I would appreciate any advice for directions how to approach debugging this problem...

Edit: I think I found the way to reinitialize replication:

ALTER SUBSCRIPTION my_subscription_name DISABLE;
ALTER SUBSCRIPTION my_subscription_name ENABLE;
ALTER SUBSCRIPTION my_subscription_name REFRESH PUBLICATION;

The original problem still persists though..

I ended up setting wal_sender_timeout to 20 minutes and this cleared errors away for me. I still need to get to the bottom of this to understand what was causing this behavior.

Edit (month later)

I came across this great presentation, it is pointing at documentation saying:

a block containing an EXCEPTION clause effectively
forms a subtransaction that can be rolled back without
affecting the outer transaction.

They had BEGIN (...) EXCEPTION WHEN (...) TEN (...) END clause used in archiving trigger. There we go, this is what I see in my schema! That means some refactoring in front of me.

And also wonderful reading mentioned in that PDF worth to look at: https://blog.anayrat.info/en/2018/03/10/logical-replication-internals/ and https://www.postgresql.org/message-id/688b0b7f-2f6c-d827-c27b-216a8e3ea700@2ndquadrant.com

So that's what I'm going to explore now and hopefully report back with final answer.


texts: Complex rights structure for users/members and keeping it fast

texts:
We are working on a database that holds various information about 15.000 members in a complex structure. These members are managed/viewed by about 1.000 and we need a system to handled rights for these users. You are only allowed to view the members you have had connection with in the last 3 months or currently have one.
The problem we are facing is that it's very slow to find out which members you are allowed to see, because of the various ways you can have a connection.
These members have parents(~2 per member), a location (1 or more of 500), a principal (1 or more of 100) and a hobby (1 or more of 100). All of these are separate tables.
There is a connection for these members in several tables
[fixedconnection] (~500.000 rows)
memberid, locationid, principalid, startdate, enddate, dayofweek
[fixedconnectionhobby] (~10.000.000 rows)
fixedconnectionid, hobbyid, startdate, enddate, dayofweek
[dailyconnection] (~10.000.000 rows)
memberid, locationid, principalid, hobbyid, date
[parents] (~30.000 rows)
member, parent
Then the actual users also have a rights structure to define what they can see. There are companies and for each company you can select zero, one or multiple locations, principals and or hobbies. If nothing is selected, you can see all.
Each company has departments, where you can select a subset of the chosen locations for the company. If nothing is selected, you can see what the company can see. If something is selected, you can only see those.
Each dapartment has users connected and these users login.
These users have various lists they want to see and select one item to view the details, like:

Show me all the dailyconnections
Show me all the members
Show me all the locations
Show me all the hobbies

We need a way to determine in SQL which records we are allowed to show. At the moment we use a combination of joins and union all to combine all the various options to get a list of ids for a specific type. For example all the member ids we can view. We can use this in the query to view the member list.
Obviously this will make the queries quite slow, even with proper indexing, because you are constantly juggling around milions of connections, just to show a single member.
We tried to use caching and get a single table with all the members you can see, per user, but since there are constant modifications to the members and their connections, it changes often who you can see.
My question: How would you handle a structure like this, to prevent users from seeing members they shouldnt see, and keeping it fast.


texts: Does mongo *really* need a dedicated volume, if I need to back it up?

texts:
TLDR; What's the easiest way to automate a mongodb backup of a 20-gig DB?
We run mongo on the same KVM (VPS) as the web app; the server runs Debian.
We/I don't need/want to backup the entire fs on a daily basis, but definitely the Mongodb database.
I don't really want to put Mongo in a separate volume, it complicates things and increases likelihood of downtime, especially when I need to expand the volume in question. However, using the LVM to create snapshots seems like the easiest (actually, the only) way forward.
I've been told that when oplog is in use hot snapshots won't be corrupt.
I found this guide demonstrating how to set up this backup method, but it's almost 10-years out of date. Is there an easier way?
I have read everything I could find, several times, on the man-pages.
Thanks for reading :)


texts: Is there any way through which can I store my "show slave status" output into any permanent/temporary table. After storing output into table I will took some decisions against their error number.


texts: How to merge data sets without including redundant rows?

texts:
I think this must be a fairly common thing to do, but I haven't been able to find an example. It involves merging data that involves dates/times (or more generally any sequential values) without including redundant records. By redundant I mean records that can be implied by other records.

E.g. if the price yesterday was $1, and the price today is $1, and there were no other price changes between yesterday and today, then the price today can be inferred from the price yesterday without storing a price for both days.

Here's the scenario I'm trying to solve. We have a table of historical prices for items. New prices are imported periodically into the table. The import file can contain dates from any time, we don't know that they are always "after" the data in the history table.

To avoid data bloat I only want to insert records if they give new information. So if a new record can be inferred from an old record then I don't want to insert the new record, and if an old record can be inferred from a new record then I want to remove the old record and insert the new record.

Some concrete examples might help, here are the two problem scenarios:

An incoming record can be inferred from an existing record, so the incoming record is redundant.
E.g.

old record: 2013-04-23 1.00

new record: 2013-04-24 1.00 <-- this is implied by the existing record, don't insert it
An existing record can be inferred from a new record, so the existing record is redundant.
E.g.

new record: 2013-04-23 1.00

old record: 2013-04-24 1.00 <-- this is implied by the new record, delete it
This just shows an example of when a value is not redundant.
E.g.

old record: 2013-04-23 1.00

old record: 2013-04-24 1.20

new record: 2013-04-25 1.00 <-- not redundant, the price changed since it was last 1.00

There is a more detailed example here http://sqlfiddle.com/#!3/2ef87/2

Currently I'm leaning towards a multi-step approach of:

Delete from incoming where there is an existing record that has the same price with an earlier date (scenario 1 above).
Delete from existing where there is an incoming record that has the same price with an earlier date (scenario 2 above).
Insert the remaining incoming records.

There must be a nicer way, maybe using MERGE, but it's doing my head in trying to work out how to do it.

How do I efficiently "merge" the existing and incoming records?
Thanks


texts: How do you remove reported non existing tables from mysql?

texts:
I was trying to export a mysqldump --all-databases and noted errors. A subsequent

mysqlcheck --all-databases

revealed several non existent tables like the following

Error    : Table 'stats.scoring_campaign_countries_99save' doesn't exist
status   : Operation failed

Of course I can't drop a table if it does't exist so how to fix this?

The tables are using MyISAM.


texts: Count number of writes to different tables

texts:
I have recently setup Continuous Archiving for Point-in-Time Recovery in a postgresql DB, but the archive is growing at an unexpectedly high pace (tens of gigabytes per day). The DB is a couple of GB, most of it static. It seems to me that we may have some part of the application which is writing a bit too aggressively to the database, possibly updating large number of rows frequently, causing needless diskload.

Is there some way to either ask the DB, or to analyze the WAL files, to find out which tables most writes (row delete, add or updates) are made to, preferably counted in bytes? That would be a big help in figuring out the source of the disk load.


texts: Is it possible to have one server acting as secondary for two primaries on AG?

texts:
Can I have a server acting as secondary replica for two primary servers on an availability group?

If possible, are there some concerns about performance in an async environment? Are listeners going to work that way?

Thanks!


texts: SQL Query Joins Multiple Tables - Results in Duplicate Rows

texts:
This Question/Answer seems close to mine, but I think I have a different problem.

Here's my query:

SELECT
PL.PlantName AS 'Plant Name',
M.MachineNumber AS 'Line',
P.Type AS 'Record Type',
P.Reason,
P.ActivityDate,
P.OrderNumber,
P.Bundle,
P.Quantity,
P.ItemLength,
P.Pattern,
P.PartOption,
P.InvCoil,
P.Footage,
P.Material,
P.ScrapCode,
P.EmployeeName,
P.LBperFt,
P.DelayCode,
P.DelayReason,
P.Duration,
P.CostPerLb,
P.ListId,
P.ListText
FROM dbo.Production P WITH (NOLOCK)
JOIN dbo.Plants PL WITH (NOLOCK) ON P.PartnerId = PL.PartnerId
JOIN dbo.Machines M WITH (NOLOCK) ON PL.Id = M.Machine_Maintenance_Plant_Maintenance
WHERE ActivityDate >= '2018-04-01' AND ActivityDate <= '2018-04-07' ORDER BY 'Plant Name';

Before I added the third table, the query was fine.  After joining the third table, I'm getting 6 copies of each row returned.


texts: Access 2013: Merging the same table-field with different data

texts: I have six tables with identical fields. the group was tasked to input the data with the same fields and I need to merge all into one dataset--- table and form.


texts: Forcing a LIMIT to MySQL Queries using ProxySQL, QueryRewrite, or some other method

texts:
I have a system that end users will need to query but they are not guaranteed to be well versed in query writing.  We wanted to ensure every SELECT statement ends with a LIMIT 100000.  I have thought of some regex to do this and some tools.  It seems the native Query Rewrite is a good option but we needed to use ProxySQL for other things, thus I wanted to see if anyone knows of a way to force this.

It seems like the patter or regex would be something like:
anything that starts with select needs to have a LIMIT on the final line followed by a space and int equal to or less than 100000 then maybe a semi colon.

Has anyone had any luck doing this?


texts: How to compare two SQL Server 2008 configurations

texts:
I have a problem with my application that works on my dev SQL Server 2008 (Developer Edition), but not on a production SQL Server 2008. Is there an easy way to export the settings from the production server and compare it to my server configuration?

What I found out is that I can export Facets in SQL Server Management Studio to XML files and compare them in a diff tool.

Is there any other/better way to export and compare settings of two SQL Server instances?


texts:
I can get a list of each day of last 30 days row by row with this query and it works..
But I don't think that it is the best one since the "WHERE" part is messed up. I draw a graph and I just want to get daily_total_invoice_amount for sum of each day of last 30 days.

SELECT
  sum(b.invoice_amount) daily_total_invoice_amount,
  extract(day from a.order_creation_date)   as day,
  extract(month from a.order_creation_date) as month
FROM orders a
  INNER JOIN order_items b
    ON a.order_id = b.order_id
WHERE
  order_creation_date BETWEEN (CURRENT_DATE - INTERVAL '1 MONTH' + INTERVAL '2 DAY')
AND CURRENT_DATE + INTERVAL '1 DAY'
    GROUP BY month, day
    order by month DESC, day DESC


texts: MySQL uses range and where on UPDATE with the primary key

texts:
I have these simple SELECT and UPDATE requests on MySQL using the primary key, there are about 13 million rows in the table.

When running an EXPLAIN on both, I notice that the UPDATE one does not use the primary key in the same way and is reported as slow in the slow query log:

type is "range" for UPDATE and const for SELECT
extra is "using where" for UPDATE and "NULL" for SELECT

Here is the data:

Thank you for your help


texts: IF EXISTS taking longer than embedded select statement

texts:
When I run the following code it takes 22.5 minutes and does 106million reads. However, if I run just the inner select statement by itself it only takes 15 seconds and does 264k reads. As a side note, the select query returns no records.

Any idea why the IF EXISTS would make it run so much longer and do so many more reads? I also changed the select statement to do SELECT TOP 1 [dlc].[id] and I killed it after 2 minutes.

As a temporary fix I have changed it to do a count( * ) and assign that value to a variable @cnt. Then it does an IF 0 <> @cnt statement. But I thought EXISTS would be better, because if there were records returned in the select statement it would stop performing the scan/seeks once it found at least one record, whereas the count(*) will complete the full query. What am I missing?

IF EXISTS
   (SELECT [dlc].[ID]
   FROM TableDLC [dlc]
   JOIN TableD [d]
   ON [d].[ID] = [dlc].[ID]
   JOIN TableC [c]
   ON [c].[ID] = [d].[ID2]
   WHERE [c].[Name] <> [dlc].[Name])
BEGIN
   <do something>
END


texts:
Is there any way how to know "Who did change the permission on users"

Like:

who give read permission or owner on a database for a user?

Using SQL Server 2014


texts: 2 Different database or 1 Single database

texts:
I have a Windows 7 machine running MySQL database and I have a Linux machine running PostgreSQL database.

Preferably I would like to have only 1 machine housing both database as currently they are co-located in a datacenter which counts the electricty power and space used.

Would it be favorable to run 2 different database (MySQL and PostgreSQL) or convert the MySQL to a PostgreSQL?


texts: How to Drop Database in Single_User Mode

texts:
How do I drop a database which shows DatabaseName (Single User) as its name?

When I try to delete it, I get the following error:

  Alter failed for Database 'DatabaseName'.  (Microsoft.SqlServer.Smo)

  ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5064)

I tried to execute the ALTER below and still have the same issue.

ALTER DATABASE [DatabaseName] SET MULTI_USER WITH NO_WAIT


texts:
How to extract data from spreadsheets or Excel to database using terminal in Ubuntu?

I'm using MySQL in Ubuntu and now I have 3 different Excel files using which I need to create a database in SQL.

Can someone help me with how can I extract that Excel data to MySQL?


texts: What are best practices for managing DB record versioning with cross-reference tables?

texts:
In our system, we need to be able to track the changes to our objects and to be able to generate a history file for each such object.

For the most part, this is straightforward: We can either create an archive for previous versions, or a Delta for each change (we are leaning toward the former). However, our DB also includes cross-reference tables, and we need to preserve these changes as well - if version 2 of an object is not connected to another object where version 1 was, then we need to be able to tell the difference.

This seems to suggest massive duplication of cross-reference data whenever anything in any versionable object changes. That doesn't seem scalable, especially since some objects are expected to change rather frequently. Is there a better way of doing this?


texts: In USER_INDEXES but not in USER_IND_COLUMNS

texts:
I can't believe I don't know this but what type of index (Oracle) would have an entry in the USER_INDEXES view and not in the USER_IND_COLUMNS view?  I see two system entries in USER_INDEXES.  Here is what I am seeing:

  select index_name,table_name from user_indexes where table_name = 'IRX_POLICY'
SQL> /

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
IRX_POLICY_IDX1                IRX_POLICY
IRX_POLICY_PK                  IRX_POLICY
IRX_POLICY_UK1                 IRX_POLICY
IRX_POLICY_UK2                 IRX_POLICY
SYS_IL0016753765C00010$$       IRX_POLICY
SYS_IL0016753765C00013$$       IRX_POLICY

6 rows selected.

SQL> select distinct index_name,table_name from user_ind_columns where table_name='IRX_POLICY';

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
IRX_POLICY_PK                  IRX_POLICY
IRX_POLICY_UK1                 IRX_POLICY
IRX_POLICY_UK2                 IRX_POLICY
IRX_POLICY_IDX1                IRX_POLICY

Why are the two system named indexes (SYS_IL00....) not in the USER_IND_COLUMNS view?  Under what conditions might this occur?


texts: Unable to shutdown immediate database - Oracle

texts:
whenever I want to shutdown the database, I get an error saying datafile header is media corrupt:
SQL> shutdown immediate
ORA-01122: database file 13 failed verification check
ORA-01110: data file 13: '/oradata/obvs/entry_data02.dbf'
ORA-01210: data file header is media corrupt

I do not mind losing those files, just need to be able to shut down the database.
I tried reseaching online, but they all say in order for me to fix them I need to shutdown the database.


texts: COUNT(), GROUP BY and GROUP_CONCAT() together on MySQL

texts:
My question is quite simple. I just want to count events from each client and present them in a comma separated single row.

I have this table:

+----+----------------+
| id | event          |
+----+----------------+
| 22 | a              |
| 23 | bb             |
| 24 | bb             |
| 25 | ccc            |
| 26 | ccc            |
| 27 | ccc            |
+----+----------------+
6 rows in set (0.01 sec)

So far I have this following query:

SELECT COUNT(event) AS total FROM test_table GROUP BY event;

Which gives me the following result:

+--------------+
| count(event) |
+--------------+
|            1 |
|            2 |
|            3 |
+--------------+

I'm trying to use GROUP_CONCAT() in order to show them in a single line, like this, but I tried all different approaches and I didn't get the desired result:

+--------------+
| result       |
+--------------+
| 1, 2, 3      |
+--------------+

Do you have a clue on how to do that?

Thanks in advance!


texts: How to query SSAS Datasource ConnectionString Properties

texts:
I've been asked to audit the Connection strings used by our SSAS OLAP cubes and where they are sourcing data from.

I can view the properties manually and can even script them:

  Provider=SQLNCLI11.1;Data Source=DEV-SERVER;Integrated
  Security=SSPI;Initial Catalog=EnviroDataMart

But I can't figure out how to query them. Can this be done with SQL or MDX?


texts: Select most occurrences of a varchar2 in Oracle

texts:
We need to group data in an Oracle database and must get a ProductNumber and a Description. The Description needs to be the one with the most occurrences inside the table. Both are a varchar2. See below for the raw data:

ProductNumber
Description

ABC
Product ABC (with discount)

ABC
Product ABC

ABC
Product ABC

DEF
Product DEF

Expected  result:

ProductNumber
Description

ABC
Product ABC

DEF
Product DEF

We've tried this but don't find a way to include the Description with most occurences.
select distinct cnt1.ProductNumber
from (select COUNT(*) as total, ProductNumber
      from Inventory
      group by ProductNumber) cnt1,
     (select MAX(total) as maxtotal, ProductNumber
      from (select COUNT(*) as total, ProductNumber
            from Inventory
            group by ProductNumber)
      group by ProductNumber) cnt2
where cnt1.total = cnt2.maxtotal;

Here you could find a fiddle.
Do you know how we could do it?


texts:
We've got this very badly designed logging table that we want to add functionality to. The problem is that it's already a scalability nightmare and we want to fix the design before adding to it, but we only have the nightly upgrade window to do it in.

I've seen a lot of articles about the various bulk copy options with SQL Server, claiming "We could move 80M rows in 10 minutes!" but so far my testing doesn't get anywhere near that, and I'd like suggestions on how to improve on what I'm seeing.

Before the upgrade, there's always a full backup. I'm only interested in the end result and don't want a huge transaction log. I also don't want it to take too long and I don't want to blow out the disk space with transaction logs or temp files.

The table's been out there a while, so in our bigger customer dbs, it's already over 50 million rows. Each row is about 350-400 bytes. The columns are something like this

IdentityColID int, [type] int, [subtype] int,
created datetime, author nvarchar(100), Message nvarchar(max)

The problems with the design are

The primary clustered key is (type, subtype, created, identitycolid), so it's an insert nightmare. Blocksplits all over the place. And even doing a SELECT COUNT(*) takes like 8 minutes.
There aren't good indexes to support the types of queries desired

I wanted to make a new table where the primary clustered index is the IdentityColId and add the indexes to support the type of necessary queries, and then copy the existing data over and drop the old table.

So far, I tried using BCP to get the data out, and importing with

BCP
BULK INSERT
INSERT INTO ... FROM OPENROWSET

The bcp export took about 25 minutes and the imports all took about 1.3 hour - about 1.5 hours. With Recovery Model Simple, the transaction log didn't grow but the cpu consumption was in the 60-65% range most of the time.

I tried just using T-SQL INSERT INTO NewTable SELECT * FROM OldTable, but even with Recovery Model Simple, the transaction log gets to 100 gig.

I tried using SSIS data import packages with the from/to model, and the net time was about an hour 20 minutes.  With Recovery Model Simple, the transaction log stayed small.

Then I tried an SSIS Execute SQLTask package to effectively do the INSERT INTO NewTable... line within SSIS.  That got the execution time down to about 1:15, but no matter what the recovery model, the transaction log ended up around 100 gig, though CPU consumption stays modest.

I'd like the end result to be one new table, so the suggestion from some of the articles I've read to parallelize into multiple result tables doesn't seem a profitable path.  But so far, I just can't seem to approach  those stats from the articles I've read.

Anyone have any suggestions on how I can goose this a bit?


texts: Event Store Summing Number of Expired Items using SQL


texts: Getting "Conversion failed when converting the nvarchar value to data type int."

texts:
I have a SQL Server table pairing zipcodes and lat/long geometry, such as below

ID  ZIP5    geom
1   356HH   0xE610000001044E00....FFFFFFFF0000000003ID
2   35677   0xE6100000010404000000068....000003

The problem I'm having is that I cannot seem to convert from one type to the other due to the ZIP5 field having characters.

What I have tried is an approach wherein I pull only records that are fully integer-friendly:

SELECT
    zip,
    coordinates
FROM (
    SELECT
        CONVERT(int, [ZIP5]) AS zip,
        CONVERT(varchar(max), geom) AS coordinates
    FROM
        [SpatialData].[dbo].[zip5]
    WHERE
        ISNUMERIC([ZIP5]) = 1
) AS t1
WHERE
    zip >= 85000 AND
    zip < 86000

I assumed that since the subquery returned an INT type, that there would be absolutely no problem doing normal comparison operators in the outer WHERE. I get the following error when executing this query:

  Msg 245, Level 16, State 1, Line 1
  Conversion failed when converting the nvarchar value '356HH' to data type int.

Omitting the outer WHERE, I get a full result set with 100% integers in the ZIP5 column.  Using the outer WHERE, it seems to look past the previous CONVERT and fail on conversion.

As a few extra notes:

The nature of this query is OK with not getting the rows that have letters.
It would be perfectly OK as well for the HH (or any other double char pair) to be replaced with numerical zeroes (tried REPLACE and STUFF to no avail, however)
I've tried TRY/CATCH but it does not appear to work when within a WHERE statement --  when more broadly catching around the entire query, the try/catch will end up returning an empty set.

What approach would be the best to address without making a temp table--table variable?


texts: Does Azure SQL Managed Instance offer SQL Server 2016?

texts:
I want to create a SQL Server Managed Instance in Azure that is SQL Server 2016 so I can backup and restore databases across on prem and Azure - i.e. in case we want to back out of Azure, we can do a backup and restore rather than create scripts procedure.

I see the @@VERSION of my current managed instance is Microsoft SQL Azure (RTM) - 12.0.2000.8

If I try to restore a database from Azure to a 2016 server, I get the message '15.00.2000. That version is incompatible with this server, which is running version 13.00.5492' - so Azure is SQL Server 2019. Can it be 2016? Struggling to find clear answers when it comes to Azure Managed Instance.


texts: How can I import latitude and longitude data into a database using Mysql Workbench?

texts:
I am currently trying to get some latitude and longitude data to import into a database using the data import wizard in mysql workbench.  the problem is that the floating point values that get imported are modified by mysql workbench, or whatever query it's preforming, so they are useless.  Example:

40.8152 turns into 40.0000.

I cannot figure out why this is happening.  I have the columns set to DECIMAL(6,4), but they still get modified when imported.

Anybody have any ideas as to what might be causing this and how i can get these to import correctly?

Edit:

I have also tried FLOAT, FLOAT(6,4), DOUBLE and DOUBLE(6,4) with the same results.  It either removes the trailing decimal values completely or sets them to 0.


texts:
I am trying to resolve a problem for a customer. They have a job which calls a stored proc on Server A, which calls a stored proc on Server B, via a linked server called RefreshDB. The linked server references a DNS alias, pointing to Server B.

Recently we migrated all the databases on Server B to ServerC. All is working fine, but when we updated the DNS alias from referencing Server B to Server C, the job on Server A started failing. Basically, the stored procedure fails saying it cannot locate RefreshDB in sys.servers. RefreshDB is the linked server, which does exist in sys.servers. It references the DNS alias.

If I ping that alias from Server A, I get Server C, which is correct. If I change the linked server to reference the actual server name, Server C, instead of the alias, it works. If I change the linked server to point to our test server, Server Test, it works. The problem is with the reference to the live server, Server C.

I have tried deleting RefreshDatabases linked server, and creating a test one called TestLink. TestLink is configured to point to Server C. When I try and run the job it fails saying cannot find RefreshDatabases! But why? I have dropped it! I even used sp_dropservers 'RefreshDatabases'.

If I update TestLink to point to another server, it works. It's just when we reference Server A that we get a failure referencing RefreshDatabases. There is some sort of cached setting that is causing problems here, but I just don't know where! Any thoughts?


texts:
Is it possible to apply a condition on an output.inserted statement?

DECLARE @tblA TABLE (foo INT)
DECLARE @tblOut TABLE (bar INT)

INSERT INTO @tblA
OUTPUT inserted.foo INTO @tblOut(bar) --WHERE foo = 1
VALUES (1),(2),(3)


texts: What is the possibility that mirroring a database will damage, corrupt, or erase either the primary or target database?

texts:
The last time my company had tried to mirror some databases, everything worked great until one morning they came in and the primary database in one of the mirroring schemes was just blank.  They had no errors or anything to point them to a reason of why this happened.  Since then, the company has not tried mirroring since it is not great for high availability if it just kills the primary database in the mirror setup.  The mirror was on SQL-Server 2005 (most likely).  I don't have any other information other than that because of how long ago this problem occurred.

The current setup that we are going to possibly use for mirroring will consist of SQL servers 2008, 2008 r2, 2012.  Do problems like the one mentioned happen frequently or has Microsoft improved their logic enough to rule out these possibilities for the most part?  Do problems with erasing and corruption happen more frequently when the servers are on different version of SQL Server?  Has anyone else ever had a similar problem with a nailed down cause?

Any insight or expertise would be appreciated since I cannot locate anyone who has had a similar problem yet.


texts:
We are running MySQL 5.1 on Windows Server 2008 R2.

We have been doing some diagnostics on our database of late and have found some disturbing artifacts which we can't explain. We added some code to log when we had queries that took a long time (> 2000ms). The results were surprising (and possibly an explanation for our deadlocks).

Occasionally queries, that normally take very little time (<10ms), are taking from 4 to 13 seconds. To be clear, these are queries that are running constantly (several times a second) and not suffering from these query time spikes.

We have gone through our indexes looking for any obvious mistakes and haven't had much luck.

Update

The people table:

| people | CREATE TABLE `people` (
`people_id` bigint(20) NOT NULL AUTO_INCREMENT,
`company_id` bigint(20) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`temp_password` varchar(10) DEFAULT NULL,
`reset_password_hash` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`phone` varchar(32) DEFAULT NULL,
`mobile` varchar(32) DEFAULT NULL,
`iphone_device_id` varchar(160) DEFAULT NULL,
`iphone_device_time` datetime DEFAULT NULL,
`last_checkin` datetime DEFAULT NULL,
`location_lat` double DEFAULT NULL,
`location_long` double DEFAULT NULL,
`gps_strength` smallint(6) DEFAULT NULL,
`picture_blob_id` bigint(20) DEFAULT NULL,
`authority` int(11) NOT NULL DEFAULT '0',
`active` tinyint(1) NOT NULL DEFAULT '1',
`date_created` datetime NOT NULL,
`last_login` datetime NOT NULL,
`panic_mode` tinyint(1) NOT NULL DEFAULT '0',
`battery_level` double DEFAULT NULL,
`battery_state` varchar(32) DEFAULT NULL,
PRIMARY KEY (`people_id`),
KEY `email` (`email`),
KEY `company_id` (`company_id`),
KEY `iphone_device_id` (`iphone_device_id`),
KEY `picture_blob_id` (`picture_blob_id`),
CONSTRAINT `people_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `people_ibfk_2` FOREIGN KEY (`picture_blob_id`) REFERENCES `blobs` (`blob_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4658 DEFAULT CHARSET=utf8 |

Indexes:

+--------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name         | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| people |          0 | PRIMARY          |            1 | people_id        | A         |        3502 |     NULL | NULL   |      | BTREE      |         |
| people |          1 | email            |            1 | email            | A         |        3502 |     NULL | NULL   | YES  | BTREE      |         |
| people |          1 | company_id       |            1 | company_id       | A         |        3502 |     NULL | NULL   |      | BTREE      |         |
| people |          1 | iphone_device_id |            1 | iphone_device_id | A         |        3502 |     NULL | NULL   | YES  | BTREE      |         |
| people |          1 | picture_blob_id  |            1 | picture_blob_id  | A         |        3502 |     NULL | NULL   | YES  | BTREE      |         |
+--------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+

we have ~5000 rows in the table on the server that is giving us trouble.


texts:
I have 2 tables table1 contains the columns of id, status,created_by,created_date,name and table2 is a log table which contains id, updated_by,updated_date. Table2 contains multiple records of same id. I'm trying to get the resultant query which support to get the selected columns from both tables by join operation. but when I tried i'm getting the duplicate records associated with that id. I want only one latest record for that id. My query...

select  t.created_by,t.created_date,tl.updated_by,tl.updated_date
    from  test_name t
    join (
        SELECT  updated_by,updated_date,id
            from  test_log
            order by  updated_date desc
            limit  1
         ) tl  ON t.id=tl.id
    where  state = 'active';


texts: Having trouble with interface table structures in MSSQL

texts:
Hi I'm currently working on a project that involves a third party database and application. So far we are able to successfully TEST and interface data between our databases. However we are having trouble when we are extracting a large set of data (ex 100000 rows and 10 columns per row) and suddenly it stopped at the middle of transaction for whatever reason(ex blackouts, force exit or etc..), missing or duplication of data is happening in this type of scenario.

Can you please give us a suggestions to handle these types of scenarios.

Here's our current interface structure

OurDB -> Interface DB -> 3rdParty DB
OurDB: We are extracting records from OurDB (with bitcolumn as
false) to the InterfaceDb
InterfaceDB: After inserting records from OurDB, we will update OurDB
bitcolumn as true
3rdPartyDB: They will extract and delete all records from InterfaceDB
(they assume that all records is for extraction)


texts: User can't stop Oracle listener (TNS-01190)

texts:
I have Oracle 10g installed on RedHat Enterprise Linux 5.

I have 2 users that use Oracle:

uid=501(user1) gid=502(group1) groupes=500(oinstall),501(dba),502(group1)

and

uid=500(oracle) gid=500(oinstall) groupes=500(oinstall),501(dba),502(group1)

When a user tries to stop a listener that was started by the other user, I get this error:

TNS-01190: The user is not authorized to execute the requested listener command

How can I allow both users to stop the listener, regardless of who started it?


texts: Transactional Replication with Always On via Sql Server 2014

texts:
My Scenario is given below...i want to configure transactional replication with alwayson and i know this is possible but my question is "I have two publisher sql server(SQL01_REPLICATION) is running on 32 bit operating system and one subscriber(64 bit) which is work as a primary for alwaysOn".so this is possible or not ?

SQL01_REPLICATION(32 bit os) as a PUBLISHER(sql server 2014 and window server 2008(32bit)).
SQL02_REPLICATION(32 bit os) as a PUBLISHER(sql server 2014 and window server 2008(32bit))..
SQL03_AG(64 bit os) as a PRIMARY REPLICA FOR AG AND SUBSCRIBER FOR                                REPLICATION(sql server 2014 and window server 2012(64bit))..
SQL04_AG(64 bit os) as a SECONDARY REPLICA FOR AG(sql server 2014 and window server 2012(64bit))..

According to above description of my existing server...i have a two publisher with 32 bit OS for replication and two server with 64 bit OS for AlwayOn Availability group.
my question is according to above description can i implement transactional replication with AlwaysOn.

Please advise me which technology is suitable for my current scenario.


texts:
Two questions:

I have 11.2.0.3 database. I need to upgrade to a higher version either 11.2.0.4 or 12.1.0.2. Which one I should upgrade it to? Im not going to upgrade on the same server. I will have to do it in a new server. So how can I proceed with the upgrade? Export/import? DBCA? Manual upgrade? What is the procedure to do this?
Same as above question except that this is another database but 10g. Which version I should upgrade it to and how to?

Added info::

I will be moving from OEL 5 to 6 or above.

Downtime I will get over an weekend.

No change in character set or licensing features.

Database size is approx 50 GB.

This is only development instance and not prod.

Should I install 12c database and then import everything from 11g to 12c database or run DBUA?

Best.!


texts: Try to define appropiate memory size for this postgres database

texts:
I have a site with around 150.000 daily page views using a 180Mb postgres database size running on a 800 mb ram db on Heroku.

This site does not do many updates, queries are mostly select that are cached on the page. SELECT count(*) FROM pg_stat_activity; always show 30 connections to the database (that should correspond to at least one connection active per worker).

By looking into new_relic I see queries to the database seem to be around 10ms but I have the impression that even If the 800 mb may be ram. Those queries could eventually run noticeable faster.

Is there any relationship in this scenario to giving more ram to process the queries faster or it doesn't matter since the db size is below 200mb?


texts: We have a number of PostgreSQL 9.0 servers. We use binary replication to have a host standby instance of those. The only problem is that is someone drops the master, with or without intentions, this will cascade to the replicas as well. I'm looking at the possible ways to avoid this. One possible option in seems to be Point in Time Recovery. I'm just wondering what could be a good design for this. Any ideas? Let's assume the master is compromised and we lose everything we have there. How can we avoid losing the replica or at least have a way to bring it back if it's dropped?


texts: SQL Memory Usage Cumulative for each Database

texts:
The following query shows how to find Current view of Buffer Memory used by each database.
How do I find Average/Cumulative Memory buffer usage of each database since the last SQL server restart, not just a current time snapshot?

We have 5 microservice databases on one server, no cross database joins, do not relate. Stored procedures are called from appropriate db. We are trying to separate into different servers, and find baseline Ram and core processor requirements, not sure best way to do this

Thanks,

Determine SQL Memory usage by database

-- Note: querying sys.dm_os_buffer_descriptors
-- requires the VIEW_SERVER_STATE permission.

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Database Pages';

;WITH src AS
(
SELECT
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[db_name] = CASE [database_id] WHEN 32767
THEN 'Resource DB'
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3),
db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;


texts: Microsoft Distributed Transactions from Informix with Linux

texts:
I need to create an extraction job from an Informix database, the requirement is that I need to enable transactions in SSIS. All of the MSDTC settings of the target machine and the SQL Server database are working, and the RPC service is listening on port 135, both firewall are disabled and no antivirus is installed.

The Informix server is on a Linux Red Hat 6.5, where the SELinux is permissive, the IpTables disabled. I also ran the cdotnet.sql and coledbp.sql scripts as directed by an IBM article to configure linked servers in SQL Server - and it worked.

The connection between the development machine and Informix is through an ODBC connection, which is also successful in querying the tables.

But when the job is started the following error occurs in SSIS:

Error: 0xC001402C in 1-Load CTLANCTO: SSIS Runtime Failed to Enroll OLE DB Connection in a Distributed Transaction with Error 0x8004D01B "The transaction manager is not available."
Error: 0xC0202009 in 1-Load CTLANCT: SSIS Error Code DTS_E_OLEDBERROR. OLE DB error. Error code: 0x8004D01B.
Error: 0xC020801C in Load CTLANCTO On Stage, OLE DB Source [16]: SIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The call of the AcquireConnection method for the "OLEDB informix" connection manager with error code 0xC0202009 failed. Error messages may have been posted earlier with more information about why the call failed for the AcquireConnection method.
Error: 0xC004701A in Load CTLANCTO On Stage, SSIS.Pipeline: OLE DB Source Fails in Pre-Run Phase with Error Code 0xC020801C.
Information: 0x4004300B in Load CTLANCTO On Stage, SSIS.Pipeline: "Stage" recorded 0 lines.
Information: 0x40043009 in Load CTLANCTO On Stage, SSIS.Pipeline: The cleaning phase is starting.
Task failed: Load CTLANCTO On Stage
Information: 0x4001100C in Load CTLANCTO On Stage: Overriding the current distributed transaction.
Information: 0x4001100C in 1-Load CTLANCT: Overriding the current distributed transaction.
SSIS Package "C: \ Projects \ ETL \ Daily LoadDRE \ Daily LoadDRE \ 1-Load CTLANCTO.dtsx" Finished: Failed.
The program '[796] DtsDebugHost.exe: DTS' was closed with the code 0 (0x0).

Although I have little knowledge on Linux, I tried to connect the development machine with the Informix server on port 135 via telnet, and the result is::

Connecting to x.x.x.x ... Could not open host connection in port
to 135: Connection failed

Then by running the rcpinfo command, it has the following:

program version netid     address                service    owner
    100000    4    tcp6      ::.0.111               portmapper superuser
    100000    3    tcp6      ::.0.111               portmapper superuser
    100000    4    udp6      ::.0.111               portmapper superuser
    100000    3    udp6      ::.0.111               portmapper superuser
    100000    4    tcp       0.0.0.0.0.111          portmapper superuser
    100000    3    tcp       0.0.0.0.0.111          portmapper superuser
    100000    2    tcp       0.0.0.0.0.111          portmapper superuser
    100000    4    udp       0.0.0.0.0.111          portmapper superuser
    100000    3    udp       0.0.0.0.0.111          portmapper superuser
    100000    2    udp       0.0.0.0.0.111          portmapper superuser
    100000    4    local     /var/run/rpcbind.sock  portmapper superuser
    100000    3    local     /var/run/rpcbind.sock  portmapper superuser
    100024    1    udp       0.0.0.0.173.232        status     29
    100024    1    tcp       0.0.0.0.132.42         status     29
    100024    1    udp6      ::.223.153             status     29
    100024    1    tcp6      ::.169.187             status     29

Does this mean that the RPC service in Informix is running on port 111? Is it necessary to change to 135?

The important detail is that when I disable transactions option in SSIS, extraction successfully occurs.

Test thousands of solutions over the internet and nothing worked, my last hope is here.


texts: Would it be better to do a sepparate query instead of a left JOIN?

texts:
Only a couple of rows is returned when not using LEFT since there isn't always data in the associated table - recipe_preparation. This is one of a couple of tables with the same "issue".

SELECT r.id, r.name,
    rp.explanation preparation,
    g.name
FROM recipes r
    LEFT JOIN recipes_preparation rp ON r.id = rp.fk_recipes_id AND rp.in_use = 1
    JOIN glassware g ON r.fk_glassware_id = g.id

The reason to why the preparation details is in a sepparate table is because of the ability to suggest/submit a better explanation. This is so the user can add their own/select a preffered one, and connect it to their account..
The AND rp.in_use is for the public one.

Anyway; I've read somewhere that using LEFT is more expensive, and that it should be avoided if possible. I could query recipes and only JOIN glasses. Then, in my application, I could just do a new query on each recipes to check if there's any data to retrieve.

What would be the better solution?
and as I mentioned before; this is just one of several other tables with this "issue"..

UPDATE:
Here is a EER diagram that I'm working with:

I've added a couple of more tables after this screenshoot was taken, and the in_use column in recipes_preparation (not indexed)..
Maybe this could tell a little more...

UPDATE #2:
I looked into the EXPLAIN statment, and this is the result I got - which didn't tell me much at first.

Looks like it starts with table g(glasses)? And it looks like it uses ALL as the join type - which is not good since that is a full table scan according to the MySQL Refrence..
Also; "MySQL found no index to use for executing the query more efficiently" more than? - the PK? - or non at all?


texts: Understanding "bitmap heap scan" and "bitmap index scan"

texts:
I'll try to explain my misunderstandings by the following example.

I didn't understand fundamentals of the Bitmap Heap Scan Node. Consider the query SELECT customerid, username FROM customers WHERE customerid < 1000 AND username <'user100'; the plan of which is this:

Bitmap Heap Scan on customers  (cost=25.76..61.62 rows=10 width=13) (actual time=0.077..0.077 rows=2 loops=1)
  Recheck Cond: (((username)::text < 'user100'::text) AND (customerid < 1000))
  ->  BitmapAnd  (cost=25.76..25.76 rows=10 width=0) (actual time=0.073..0.073 rows=0 loops=1)
        ->  Bitmap Index Scan on ix_cust_username  (cost=0.00..5.75 rows=200 width=0) (actual time=0.006..0.006 rows=2 loops=1)
              Index Cond: ((username)::text < 'user100'::text)
        ->  Bitmap Index Scan on customers_pkey  (cost=0.00..19.75 rows=1000 width=0) (actual time=0.065..0.065 rows=999 loops=1)
              Index Cond: (customerid < 1000)

My understanding of this node:

As explained there, the bitmap heap scan reads table blocks in sequential order, so it doesn't produce random-table-access overhead which happens as doing just Index Scan.

After Index Scan has been done, PostgreSQL doesn't know how to fetch the rows optimally, to avoid unneccessary heap blocks reads (or hits if there is a hot cache). So to figure it out it generates the structure (Bitmap Index Scan) called bitmap which in my case is being generated by generating two bitmaps of the indexes and performing BITWISE AND. Since the bitmap has been generated it now can read the table optimally in a sequential order, avoiding unnecessary heap I/O-operations.

That's the place where a lot of questions come.

QUESTION: We have just a bitmap. How does PostgreSQL knows by just a bitmap anything about rows' physical order? Or generates the bitmap so that any element of it can be mapped to the pointer to a page easily? If so, that explains everything, but it's just my guessing.

So, can we say simply that the bitmap heap scan -> bitmap index scan is like a sequential scan but only of the appropriate part of the table?


texts: PostgreSQL 10.6: Alter parent table will block ALL children?

texts: I want to add a column to near 74.000 child tables. These tables continually have insertions and reads from all the services in the company, in almost realtime. If I add a column to the parent table, it will block the 74.000 tables, or will be one-to-one?


texts: How to UPDATE a SQLITE Column with an INNER JOIN on TWO FIELDS

texts:
How can I translate this query to SQLite:

UPDATE Table_1
INNER JOIN Table_2 ON (Table_1.Field1 = Table_2.Field1)
AND(Table_1.Field2 = Table_2.Field2)
SET Table_1.Field3 = Table_2.Field3

This is what I have tried.

UPDATE Table_1
SET Field3 = (SELECT Field3
            FROM Table2
            WHERE (Field1 = Table_2.Field1)
            AND (Field2 = Table_2.Field2))
WHERE Field1 IN (SELECT Table_2.Field1
            FROM Table2
            WHERE (Field1 = Table_2.Field1)
            AND (Field2 = Table_2.Field2))
AND Field2 IN (SELECT Table_2.Field2
            FROM Table2
            WHERE (Field1 = Table_2.Field1)
            AND (Field2 = Table_2.Field2))

The query works but I get wrong results.


texts: sqlplus: ORA-01017 logon denied from one host but OK from another

texts:
I have a strange behavior for remote sqlplus login to Oracle 11.2 that I cannot explain:

1) I have an Oracle 11.2 server running on a virtual host, say host1
2) I set up another VM, say host2 (CentOS 6.5 x86_64) with oracle-instantclient 11.2.0.4 64bit, built up from a minimal install ISO
3) I also downloaded a CentOS 6.5 x86_64 VM image from the net (a desktop install with everything already installed), say host3, on which I installed the same Oracle instantclient

I use the same environment and tnsnames.ora file on both host2 and host3, something like:

MONSTER=
(DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))
    (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CRM)))

Issue:
On host2, I cannot get rid of the ORA-01017 error:

host2> sqlplus sys/<passwd>@monster as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 1 12:09:51 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

BUT, on host3, everything is OK:

host3> sqlplus sys/<passwd>@monster as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 1 06:21:59 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release ...
SQL>

Has someone an idea why connecting from one host is OK while connecting from the other one is KO?

NB: Needless to say this is not an issue of case sensitivity, or bad orapwd, or remote_login_passwordfile not set to EXCLUSIVE, since it works from host3.

Thanks


texts: PostgreSQL limiting number of rows for a certain condition


texts: Sum of previous n number of columns based on some category

texts:
I have following input for which I need to calculate the sum of values for previous x number of weeks for each category.

If the x is 3 the output would look like this:

Note that the last value is 49 because it added only last two week's values to the current week since x=3.

I am looking to write the SQL as a stored procedure and need some help on appropriate ways of doing this.

With the help from @sp_BlitzErik I tried to use LAG but couldn't quite get where I need to. Here is my query:

SELECT category
    ,year
    ,week
    ,value
    ,(
        LAG(value, 1, 0) OVER (
            ORDER BY category
                ,year
                ,week
            ) + LAG(value, 2, 0) OVER (
            ORDER BY category
                ,year
                ,week
            ) + value
        ) AS cumulative_value
FROM valuedata

And the output is not quite correct yet:


texts:
has anyone a sql script that allow us copy the last full backup files from azure blob container to an ftp server or simply to the machine from which we run the script (sql server machine)?

Thanks in advance


texts: Preserve CASE WHEN output as new column in MySQL

texts:
I am new to MySQL and this question may look silly, but:
I have a dogs table and I categorize their size by their weight:
SELECT *,
    CASE
        WHEN weight < 15 THEN 'Small'
        WHEN weight < 40 THEN 'Medium'
        ELSE 'Big'
    END AS Size
FROM dogs;

This outputs my table with a new column Size at the end.
And I would like to permanently append that column to this table so it's visible when I use SELECT * FROM dogs;


texts: SQL Server Command Line install - Default database directories

texts:
Is there any way using the Command Line install method, I can specify a custom location for the system databases?

I want them to be in D:\SqlServer\Data

or is this only way to do this to install them in the default directory and then move them after?


texts:
I know newer versions of MS Access allow multiple selections in lookup fields.

For example, if I'm collecting information on my students, I can have a field called Race with lookup values that include White, Black, Hispanic, Asian, etc.  and then set that to allow multiple selection.

In term of db design, is it better to use this feature of Access.... OR to have a field for each Race category and then True/False values for each one?  And maybe even have this in a separate table?

--edit

I'm asking my question about the table, not the form. I realize the front-end display is not identical to the back-end strucure. Is it better to have a bunch of true/false fields in the table OR just one lookup field that allows multiples? (knowing the form is the best place to define the actual lookup values)


texts:
How to get number of PK used as FK?

Table 1 has a column ID (PK).
Table 2 & 3 has a column ID (FK - refers Table 1 PK).

How to get Value 1 (PK) has used in table 2 & 3 or not without referring table 2 or 3 ?


texts: Using table variable instead of temp table makes query execution slow

texts:
I have a table with historical data about cars AutoData with combined clustered key Cas (DateTime) + GCom (Car ID). One record contains various indicators, like fuel level, vehicle state etc.

Intervals between individual records for one car in AutoData table are irregular, sometimes it is 120 seconds, sometimes few seconds, sometimes hours etc. I need to normalize the records for viewing, so that it shows one record per every 30 seconds.

I have the following script:

DECLARE @GCom int = 2563,
    @Od DateTime2(0) = '20170210',
    @Do DateTime2(0) = '20170224'

--Create a table with intervals by 30 seconds
declare @temp Table ([cas] datetime2(0))
INSERT @temp([cas])
SELECT d
FROM
(
  SELECT
      d = DATEADD(SECOND, (rn - 1)*30, @Od)
  FROM
  (
      SELECT TOP (DATEDIFF(MINUTE, @Od, @Do)*2)
          rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
      FROM
          sys.all_objects AS s1
      CROSS JOIN
          sys.all_objects AS s2
      ORDER BY
          s1.[object_id]
  ) AS x
) AS y;

--Create temp table
CREATE TABLE #AutoData (
    [Cas] [datetime2](0) NOT NULL PRIMARY KEY,
    [IDProvozniRezim] [tinyint] NOT NULL,
    [IDRidic] [smallint] NULL,
    [Stav] [tinyint] NOT NULL,
    [Klicek] [bit] NOT NULL,
    [Alarm] [bit] NOT NULL,
    [MAlarm] [tinyint] NOT NULL,
    [DAlarm] [bit] NOT NULL,
    [Bypass] [bit] NOT NULL,
    [Lat] [real] NULL,
    [Lon] [real] NULL,
    [ObjemAktualni] [real] NOT NULL,
    [RychlostMaxV1] [real] NOT NULL,
    [RychlostV2] [real] NOT NULL,
    [Otacky] [smallint] NOT NULL,
    [Nadspotreba] [real] NOT NULL,
    [Vzdalenost] [real] NOT NULL,
    [Motor] [smallint] NOT NULL
)

--Populate the temp table selecting only relevant AutoData records
INSERT INTO #AutoData
SELECT [Cas]
      ,[IDProvozniRezim]
      ,[IDRidic]
      ,[Stav]
      ,[Klicek]
      ,[Alarm]
      ,[MAlarm]
      ,[DAlarm]
      ,[Bypass]
      ,[Lat]
      ,[Lon]
      ,[ObjemAktualni]
      ,[RychlostMaxV1]
      ,[RychlostV2]
      ,[Otacky]
      ,[Nadspotreba]
      ,[Vzdalenost]
      ,[Motor]
FROM AutoData a
WHERE a.GCom = @GCom AND a.cas BETWEEN @Od AND @do

--Select final data
SELECT t.cas, ad.malarm, ad.IDProvoznirezim, ad.Otacky, ad.motor, ad.objemAktualni, ad.Nadspotreba
FROM @temp t
OUTER APPLY (
SELECT TOP 1 stav, malarm, otacky,motor, objemAktualni, Nadspotreba, IDProvoznirezim  FROM #AutoData a
                     WHERE DATEDIFF(SECOND, a.cas, t.cas)<=CASE WHEN Motor>120 THEN Motor ELSE 120 END
                     AND DATEDIFF(SECOND,  a.cas, t.cas)>-30
                     ORDER BY CASE WHEN DATEDIFF(SECOND, a.cas, t.cas)>0 THEN DATEDIFF(SECOND, a.cas, t.cas) ELSE (DATEDIFF(SECOND, a.cas, t.cas)*-1) +120 END
) ad

DROP TABLE #AutoData

At the first I have tried to write the script with only one table variable @temp placing the condition WHERE a.GCom = @GCom AND a.cas BETWEEN @Od AND @do in the last select. The script took 39 seconds to execute.

When I have used #AutoData temp table to preload data subset in a temp table like it is shown in the script above, it dropped to 5 seconds.

Then I have tried to use a table variable @AutoData instead of #AutoData - but it took again much longer - 22 seconds.

@temp table has 40320 records and #AutoData table has 1904 records for this example. But suprisingly just using #temp table instead of @temp variable made the execution slow again.

I was suprised to see such differences using or not-using temp table/variable. Appearently SQL Server could not by itself optimize the insides of the OUTER APPLY clause.

But why there is such a big difference using table variables vs. temp tables?
Is there any other way to know, what to use and not just trying it?

Execution plan with temp table #AutoData:

https://www.brentozar.com/pastetheplan/?id=B1y2x2Zcg

Execution plan with variable @AutoData:

https://www.brentozar.com/pastetheplan/?id=r1rAZnbqx


texts: Add multiples of rows till they satisfy an arbitrary sum then return the rows that do

texts:
Am not sure if this is possible. Let's say I have an arbitrary figure like 200, I have a table with two columns (both ints). Lets call the columns col_a and col_b.

I need to perform one query such that if col_a * col_b for the first row does not equal to my arbitrary figure (in this case 200), keep whatever value the first row equates to and go to second row, do the same while adding the results to the value we got from the first row, repeat this till the total equates to our arbitrary value.

In an example. Lets use our arbitrary figure of 200 say we have row 1 (col_a*colb) = 20, row 2 (col_a*col_b) = 70, row 3 (col_a*colb) = 110, row 4 (col_a*col_b) = 30.

My query should return row 1, row 2 and row 3. How is this possible in sql? I am using mysql.


texts: Setting shared_buffers in postgresql.conf does not seem to take effect

texts:
We're on CentOS release 6.6, PostgreSQL version 8.4.20. (Yes, this is not bleeding edge.)

In postgresql.conf, we have:

shared_buffers = 4096MB

kernel shm values are set nice and high:

[root@green data]# sysctl -a | grep shm
kernel.shmmax = 15922077696
kernel.shmall = 3887226
kernel.shmmni = 4096
kernel.shm_rmid_forced = 0

We have plenty of memory:

[root@green data]# free
             total       used       free     shared    buffers     cached
Mem:      31097812   30474972     622840    2873672    1961088   20565360
-/+ buffers/cache:    7948524   23149288
Swap:      1959920      93852    1866068

Yet the value for shared_buffers reported by pg_settings is only 512MB, not the 4GB set in postgresql.conf:

postgres=# select name, setting, min_val, max_val, context from
pg_settings where name='shared_buffers';
      name      | setting | min_val |  max_val   |  context
----------------+---------+---------+------------+------------
 shared_buffers | 524288  | 16      | 1073741823 | postmaster

Yes, we've done a full restart, and SHOW config_file confirms that I've edited the right postgresql.conf.

My very great thanks to anyone who can provide insight into this mystery.


texts: How to know which DMVs relate to each other?

texts:
Is there any way to know which DMVs/DMFs are related to each other?

Some of them are well known or obvious, like sys.dm_exec_connections JOIN sys.dm_exec_sessions ON session_id = session_id, or the DMVs for missing indexes.

However, for example, I would like to know what other DMVs sys.dm_exec_sessions joins with on session_id.   Session_id does not exist in sys.columns.  Also, the queries here do not list anything for DMVs.

A long, long time ago Microsoft published a large .pdf poster with all of the system views for SQL Server 2005, so I'm looking for something like the for DMVs/DMFs in SQL Server 2017.


texts: Validate Primary Key and Index Selection

texts:
I have a table that will store transaction data from store sales registers, I have read quite a bit on index and key choice and below is what I have concluded is the best option but I am new to this level of table design so advice would be greatly appreciated.

The table will be queried mainly on looking for a specific transaction (TrxDate, StoreNo, RegNo and TrxNo) or summarising data by TrxDate/ StoreNo.

Do my indecies look correct? Should the UX_POS_eCommTrx_TrxDate_StoreNo_RegNo_TrxNo_SeqNo index be a primary key? Is it worth adding another index just to cover looking for specific transactions (same a UX but without SeqNo)?

This is going to be a key table as the database grows and I really want to avoid having to change things round once the table has grown significantly.

CREATE TABLE sfs.tbl_POS_eCommTrx
(TrxDate int NOT NULL,
    StoreNo smallint NOT NULL,
    RegNo smallint NOT NULL,
    TrxNo smallint NOT NULL,
    SeqNo tinyint NOT NULL,
    SKN int NOT NULL,
    Pcs smallint NOT NULL,
    Retail decimal(38,4) NOT NULL,
    CorrTend numeric(1,0) NOT NULL,
    OrderID int,
    UID int NOT NULL identity(1,1),
    DateStamp datetime Default(getdate())
)

CREATE UNIQUE INDEX UX_POS_eCommTrx_TrxDate_StoreNo_RegNo_TrxNo_SeqNo
    ON sfs.tbl_POS_eCommTrx (TrxDate,StoreNo,RegNo,TrxNo,SeqNo)

CREATE INDEX IX_POS_eCommTrx_TrxDate_StoreNo
    ON sfs.tbl_POS_eCommTrx (TrxDate,StoreNo)

Thanks in advance.


texts: Is there a way to run parameterized update statements using only DB2 commandline and bash?

texts:
[DB2 LUW 10.5] I need to update a few hundred thousand rows in a DB2 table. Individual update statements will take longer to execute compared to precompiled approach. I cannot use perl or any such scripting tools with ODBC/JDBC/CLI and PreparedStatement.executeUpdate etc. due to certain limitations.

Is there a way to accomplish this using the db2 commandline tool and procedural SQL? My tool set is restricted to bash, SQL and DB2 Command line interface.


texts: MongoDB replicaset number of connections too high

texts:
I'm using a 3 node replicaset with 2 data nodes(version 2.6.1 and 2.6.3) and 1 arbiter node. I get frequent alerts on number of connections >1000, whereas total databases are about 400 and also I'm using connection pooling with nodeJS.

With another standalone instance where number of databases are much more than that in first scenario I get number of connections are mostly less than 400.

How many connections can I expect normaly(so that I can decide a threshold)?


texts: Attempting to tune a Sharepoint site using SQL Server Waits and Queues

texts:
I'm pretty new to this performance tuning with Waits and Queues thing - fascinating, but also not always all that intuitive...

Right now, a customer of mine has a SQL Server 2008 64-bit Enterprise Edition, currently with 16 GB of RAM assigned to it, running on a physical server with 64 GB of RAM (there are other and changing SQL Server instances on the same machine, too).

This app he's running is a Sharepoint 2010 solution, which performs - well - OK for a Sharepoint site - most of the time. Except for searches, which are terribly slow.

Now from a SQL Server perspective, I've been watching the wait statistics for a few days, and the top three wait types are:

1) CXPACKET at around 49%
2) SOS_SCHEDULER_YIELD at around 10.5%
3) OLEDB at around 9.5%

This seems to be quite consistent - no big changes over time.

PAGEIOLATCH_SH comes in as seventh - 2.5% of total wait times, average resource wait time is 14ms.
ASYNC_IO_COMPLETION comes in eight - less than 2% of total wait times, but high average resource wait time of 38 seconds (yes, seconds - not milliseconds!)

The signal wait time is extremely low, so that doesn't seem to indicate CPU pressure. So what's causing this pattern - and what can we do to (a) find more relevant info, or (b) find a solution to speed things up?

Any thoughts? Insights? Ideas? I'm pretty much open to anything - what we cannot change is the basic architecture (separate SQL Server instances, being moved between physical servers - and a Unix-based SAN which cannot guarantee separation of data and log onto separate, physical disks)

Again: this is a SharePoint site - so as far as I know, trying to solve things with indexing or restructuring the database is somewhere between hard and impossible.


texts: Is there a tool or library to extract data from Mysql database files without having the database engine available?

texts:
I have a bunch of *.frm, *.myd and *.myi files, which are a MySQL database store.

I need to extract the data kept in these files, but I do not have a MySQL database engine at my disposal.

Is there a tool or library, which can do this?

Please, do not suggest installing the database engine (be it MySQL or MariaDB or other MySQL forks). My question is specific about extracting the data without the full blown database engine. In essence, I am looking for an embedded database engine, which can be distributed with my code and which demands no installation.

I am not looking into doing complex queries, I just need to fetch all the data from one or two particular tables.

Thanks.

EDIT

I am aware of http://dev.mysql.com/doc/refman/5.0/en/libmysqld.html, but I do not understand how to download it. What is the licensing for commercial usage? Should I download and install the full blown database to get hold on the libmysqld files (headers, lib + dll)? Even though I do not need the full database? In short, it creates more questions than answers. I am still looking for an advice.


texts: Create user to monitor database activity on Postgres

texts:
Postgres 9.3
Debian 7

I want to create a monitor user called monitor that the only permission is to run queries to monitor all the database activity.
I created the user and it can select from pg_stat_activity, the problem is that it only sees it's own activity and I want this user to see all users activity.

Is there any special privilege to do that?


texts: SQL Server 2008 R2 Drops Connections when Anti-Virus Scan is running

texts:
I have a SQL Server 2008 R2 Enterprise (SP2) cluster (2 nodes Windows Server 2008 R2 SP1, NetApp Shared storage).

There are 5 instances all running on this cluster in an active passive configuration and we have found that occasionally during the time that the weekly antivirus scan runs, one of the instances starts to drop connections and becomes inaccessible for about an hour (the scan runs for 2 hours), all the other instances are fine.

In the error log, we see the following messages when the server is inaccessible:

  TCP connection closed but a child process of SQL Server may be holding
  a duplicate of the connection's socket.  Consider enabling the
  TcpAbortiveClose SQL Server registry setting and restarting SQL
  Server. If the problem persists contact Technical Support.

  Error: 26073 Severity: 16 State: 1.

They are also accompanied by the following error (although not all the time):

  Process 0:0:0 (0x15540) Worker 0x0000000E721041A0 appears to be
  non-yielding on Scheduler 20. Thread creation time: 13025544955370.
  Approx Thread CPU Used: kernel 0 ms user 0 ms. Process Utilization
  0%. System Idle 91%. Interval: 119402 ms.

During this time there are also some warnings with IO operations taking longer than 15 seconds to complete.

What I'd like to know is:

We have excluded all the locations recommended by Microsoft from scanning, so why is this only happening during the scan, can these issues just be caused by the extra load of the scan, they're pretty powerful boxes so I wouldn't have thought that the extra load would cause a problem, and the time at which this runs, the server is not busy, also if it is caused by the scan why does the problem only stay for 1 hour rather than the full 2?

What is the TcpAbortiveClose registry setting and what are the implications of this setting, I know how to set it from the Microsoft KB article(http://support.microsoft.com/kb/2491214), but I can't find any documentation on this setting anywhere on what it does or whether it is a good idea.


texts:
To begin with, I have to admit that I don't have great experience in designing database schemas. Being influenced by OOP , I find that by dividing the info we have about a object into different entities, we achieve better info organization.

For example , let's say we have a basketball player. For each player we have some general info like their name , their age , the position they play etc. We also have some info about their performance during his whole career (eg total points scored , total blocks etc). It is obvious that the relation between a player and their lifetime stats is 1:1.

Let's see some designing options for this relation now:

The most simple option would be to include the player's general info with their stats in a single table. But this seems to unorganized to me ; let's just imagine that we have more than 50 columns for the stats.
The second option would be to create a table player_stats and have as primary key the player_id and referencing through it the primary key of the players table. I think that is called One-To-One Bidirectional relationship.

The third option would be to make put a foreign key in the player referncing his stats. That would be a One-To-One Undirectional relationship.

The fourth option is to make a join table combining both info. Placing unique constraints ensures that there will be no duplicates of a player or of a stat.

So let's summarize. Is it a good idea to break information to different tables? And if yes, what design should be preffered in One-To-One relationships and when ?


texts: Database performance for determining entities assigned to users

texts:
I am interested in insight in improving performance in the following instance:

I am currently working on an application that has a hierarchical entity structure where, depending on a user’s level within the organization, they would have an assignment to specific entity at a specific level – and thereby have access to all their children (in this case buildings within districts within regions throughout the U.S.). Most of the super users will have upward of 20,000 individual entities in their portfolio, some with as many as 40,000.

Instead of going into great detail, let’s suffice to say that a good bit of logic is needed to determine all the entities a user has access to. This logic is currently handled using a table function that is used in 95% of the stored procedures. The average stored procedure takes no more than 1 - 2 seconds to run. BUT, in an ASP.Net page that makes calls to 10+ different stored procedures, the performance hit quickly snowballs into 20+ seconds.

As an alternative, we were thinking of only calling this table function once (upon log in) and storing the results in a table (after clearing out any previous values for the same user). We would then have all the stored procedures reference this new table instead of the table function. A test revealed that a page which took 15 seconds to load could render in less than 3 seconds when selecting from the new table.

For example:

User logs in
system deletes all entities for the user in the table
system inserts all the user’s entities then
system sends them on their merry way and no longer runs the table function for that current session

Our concern is that, with the potential for hundreds of users consistently logging in and out of the application, deleting from and inserting into this table so often could result in a significant performance hit itself due to row level locking. Has anyone else used a SQL Table in such a manner? If so, should we be concerned with low performance due to the constant inserting and deleting from a single table.


texts: When Execution Plan Cost Differs From IOPs and Time

texts:
Generally, the query execution plan's overall cost aligns with the statistical IOPs.  However, I've run into a situation where this isn't the case; one plan, which uses no throttling of CPU, shows it's less expensive by 6%, yet the more expensive plan (which uses OPTION (MAXDOP 1)) has significantly fewer reads and requires less time.  I tested the order of the query which came first and last and I continue to obtain the same results (even when running them independently).

  The no maxdop option query is 6% faster by query execution plan, but has significantly more reads and takes more seconds regardless of whether it is run first or last over the MAXDOP 1 query.

In a situation where the query execution plan shows that one plan is smaller than the other overall, but the statistics show a lot more reads on the more expensive one and it takes a lot less time, what's the best way to determine which is more effective?

Update

Just some numbers:

Plan one (no MAXDOP): 6% less cost by execution plan

Plan two OPTION (MAXDOP 1): Per Stats IO, fewer scans, logical reads,WorkTables involved; less time by 2 seconds overall.


texts: Does mysql or mariadb have the concept of SQL Server's "included columns" for indexes?

texts: In SQL Server you can create an index and have additional columns included on that index that can help performance in certain circumstances. Is that same ability available for mysql or mariadb under a different name, perhaps? I was unable to find anything using the term "included columns".


texts: SSIS/SSDT Censor Characters in Specific Positions With Flat File

texts:
I'm developing an ETL process via SSIS/SSDT, which works with Fixed-Width Flat Files. It has 3 record types, and one of them I need to censor specific characters at a certain position. Does anyone know of any documentation that covers steps relating to replacing characters in a specific record type, at a certain position? I've scoured various resource, but I can't find anything definitive.

I essentially need to replace characters in one of record types with "*" where applicable. I'm hoping someone may be able to provide some guidance and/or documentation that covers this step. As I cannot insert this data into the database and then apply an update query. So alternate solutions aren't an option. Also don't want to use an archaic method like Excel to update the flat files before running it through the ETL process. This one step is a new one for me.

I'm using SSIS/SSDT to do the ETL process. If you know of any quirks relating to my scenario that vary from the normal process (flat file, fixed width, etc.), that'd be helpful.

Any assistance anyone provides is greatly appreciated. Thank you for your time!


texts: How to return a record from function, executed by INSERT/UPDATE rule (trigger)?

texts:
I'm having some issue when I'm trying to join two tables in a single view (for migrating code we want to split one big record into two records). I'm using Postgres 9.4!
Here is an SQL fiddle.

The fiddle doesn't work for some reason (not really sure if advanced stuff is supported). So basically I have a custom function that first create the gi record, cast the primary ID from the gi record in a variable and then I use this to query some external table and use the result to finally create a joe record.

Now I need to support the following syntax:

INSERT INTO gi_joe(weapon_type,weapon_fire_power,first_name,last_name,city
                  ,country,created_at,updated_at)
VALUES (3, 500, 'G.I', 'JOE', 'New York', 'USA', NOW(),NOW()) RETURNING id;

This is a typical insert that is done in our application layer (it inserts a record and then uses the returning id). I'm not able to run this query because I get the following error:

ERROR: cannot perform INSERT RETURNING on relation "gi_joe"
SQL state: 0A000
Hint: You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.

How can I support the query with the RETURNING set from a custom function. Or is there a way to do everything in one INSERT (with an optional RETURNING field). Please note that my example is simplified.

Table setup:

CREATE TABLE gi
(
  id serial NOT NULL,
  weapon_type_id integer,
  fire_power integer,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  CONSTRAINT gi_pkey PRIMARY KEY (id)
);

CREATE TABLE joe
(
  id serial NOT NULL,
  first_name character varying,
  last_name character varying,
  city character varying,
  country character varying,
  gi_id integer,
  external_id integer,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  CONSTRAINT joe_pkey PRIMARY KEY (id)
);

CREATE TABLE external
(
  id serial NOT NULL,
  joe_id integer,
  CONSTRAINT external_pkey PRIMARY KEY (id)
);

View:

CREATE OR REPLACE VIEW gi_joe AS (
  SELECT  gi.id AS id,
    gi.weapon_type_id as weapon_type,
    gi.fire_power as weapon_fire_power,
    joe.first_name as first_name,
    joe.last_name as last_name,
    joe.city as city,
    joe.country as country,
    joe.external_id as external_id,
    gi.created_at as created_at,
    gi.updated_at as updated_at
  FROM gi LEFT OUTER JOIN joe ON gi.id = joe.gi_id
);

Function used in rule:

CREATE OR REPLACE FUNCTION gi_joe_insert(new_gi_joe gi_joe) RETURNS integer AS $$
  DECLARE temp_gi_id integer;
  DECLARE temp_joe_id integer;
  BEGIN
    INSERT INTO gi(weapon_type_id, fire_power, created_at, updated_at)
      VALUES (new_gi_joe.weapon_type, new_gi_joe.weapon_fire_power, new_gi_joe.created_at, new_gi_joe.updated_at)
      RETURNING id INTO temp_gi_id;

    SELECT joe_id INTO temp_joe_id FROM external WHERE id = new_gi_joe.external_id LIMIT 1;

    INSERT INTO joe(first_name, last_name, city, country, gi_id, external_id,
                    created_at,updated_at)
      VALUES (NEW.first_name, new_gi_joe.last_name, new_gi_joe.city, new_gi_joe.country, temp_gi_id,
            temp_joe_id, new_gi_joe.created_at, new_gi_joe.updated_at);
  RETURN temp_gi_id;
  END;
$$ LANGUAGE plpgsql;

Rule, insert with the returning id:

CREATE OR REPLACE RULE custom_insert_gi_joe AS ON INSERT TO gi_joe DO INSTEAD (
  SELECT gi_joe_insert(NEW) AS id;
);

INSERT INTO gi_joe(weapon_type,weapon_fire_power,first_name,last_name,city
                  ,country,created_at,updated_at)
VALUES (3, 500, 'G.I', 'JOE', 'New York', 'USA', NOW(),NOW()) RETURNING id;


texts: What are the practical impacts of the sys.dm_exec_query_stats warning in the documentation?

texts:
The documentation for sys.dm_exec_query_stats states the following:

  An initial query of sys.dm_exec_query_stats might produce inaccurate
  results if there is a workload currently executing on the server. More
  accurate results may be determined by rerunning the query.

I sometimes query that DMV during an active workload and would prefer accurate results. I do not know how to apply the above warning in practice. Should I always query the DMV twice and use the second result set because that will be more accurate? That feels a bit far-fetched. Do I need to be aware of the ways in which the DMV can be inaccurate so I can factor that into my analysis? If so, what kind of inaccuracies can appear: missing rows, outdated values, inconsistent rows, or something else?

What are best practices when using sys.dm_exec_query_stats during an active workload?


texts: Within a given database: what objects cant have the same name?

texts:
In a given database what objects (Table, View, Index, Constraint, Trigger etc) can or can't have the same name?

So a table or view can have the same name if they are in a different schema.
Is this true of other database objects?

For example, can an index have the same name of another if it is in another schema or for another table?

What are the rules for uniqueness around identifiers?
(Not what we should do or naming conventions, but rather what is valid to do)


texts: Drop or truncate a temporary table during LOAD DATA?

texts:
I'm using LOAD DATA to batch csv content into a TEMPORARY table, with deactivated auto-commit. Within the same transaction, I then INSERT... ON DUPLICATE KEY UPDATE the content from temp table into the production table. After that, I COMMIT the transaction.

I have several files, and each of them runs the transaction described above.

Question: should I DROP and recreate the temporary table before each commit? Because I don't need the data in the temporary table anymore, plus I need a fresh temporary table for each file import.
Or should I better simply TRUNCATE the temporary table?


texts: Can't grant role permissions under sysadmin login

texts:
I'm trying to execute this script from Management Studio:

GRANT ALTER ON ROLE::[AdaptTemplate] TO [AdaptAdmin] AS [AdminSapr]

Execution fails with this message:

  Msg 15151, Level 16, State 1, Line 1 Cannot find the role
  'AdaptTemplate', because it does not exist or you do not have
  permission.

There are existing roles AdaptTemplate, AdaptAdmin and user AdminSapr in database.
User AdminSapr is a member of db_owner. My current login (which I'm trying to execute script from) is a member of sysadmin server role.

What am I doing wrong?


texts:
I am trying to report on data in e-email messaging system and wanted to have SQL tally counts by month/year.

SELECT YEAR(crdate) AS y,
       MONTH(crdate) AS m,
       COUNT(*) AS tally
FROM   MESSAGE
WHERE  YEAR(crdate) = 2012
       AND SUBJECT <> 'Welcome' --exclude default messages
GROUP BY
       YEAR(crdate),
       MONTH(crdate)

This is what I have but its limiting in that I need to manually change years. Can this be upgraded to output for all dates in the table in this month/year format?

Removing the year would clump all the months over may years together and distort the data. I am looking to get the counts from that particular month/year.


texts: How can I prefer a specific MongoDB replica set member be elected as primary?

texts:
I have a replica set with 5 members. replica-1 is a primary and other replicas are secondary:

replica-1 (primary)
replica-2 (secondary)
replica-3 (secondary)
replica-4 (secondary)
replica-5 (secondary)

If the primary replica (replica-1) goes down one of the secondaries will switch to primary. After fixing any issues I will bring up replica-1. Then I need replica-1 to become the primary again.

How do I achieve this? I have tried using the following command but any of the secondaries may be elected:

rs.stepDown()

How can I force replica-1 to be primary?


texts: Disk space for non-persisted In-Memory tables

texts:
I have a database on a SQL Server 2014 SP2, CU5 Enterprise edition.

The database has a memory optimized filegroup. There are two tables in it, both are set to DURABILITY = SCHEMA_ONLY and have a low bucket count (e.g. 512).

I don't remember what the initial disk space of this file was, but some time ago it allocated 4 GB and has since grown to ~7 GB. When I look in the filestream files with a hex editor, it seems that they contain data from a (partitioned) datawarehouse table in the same database, but this could be due to instant file initialisation.

Is there a way to shrink the filegroup?
Why does the in-memory filegroup take up so much space?
How can I verify, if (and which) other tables are using the space, since SELECT * FROM sys.indexes AS i WHERE i.data_space_id = 8 -- = the in_memory filegroup does not return anything


texts:
I need to confirm that actions by privileged users are being audited. As far as I can tell from views such as dba_stmt_audit_opts, the statements by the privileged users I want to be audited are being audited indiscriminately, so that seems good.

I know that NOAUDIT can be used to stop auditing a statement for a specific user. How would I check whether someone in the past has done this for a privileged user?