texts: How to pass a table of values to a stored procedure from SSIS?

texts:
I am really new to SSIS, so I apologize for the "dumb" question. I know open ended "Best Practices" questions are seriously frowned upon, so I will try to be as specific as I can.

I have a basic SSIS package, which moves data from one SQL Database to another SQL Database. There are few data transformations and one filter going on, in the Data Flow task section. I've been trying to come up with a good way to write my data to it's destination, but it can't go straight into the "Final" table (as I would with the Data Flow Destination tasks). I would prefer to pass it as a variable to a stored procedure. I see two options, both of which I don't like:

I could use the OLE DB Command and loop through my data line by line, running the stored procedure for each one.
I could write the data to a temp table and call the stored procedure from the Control Flow section.

For option 1, it seems like really bad form to have to loop through each data row. Is there no way to call the stored procedure and pass it a table?

I have someone here at work telling me not to use a temp table, so Option 2 seems off the table. I think if I had justification for using Option 2, as a "best practice", I might be able to convince them to let me do the data load that way.

Am I missing something? Is there another way to tackle this problem? If you could include a reason WHY with your answer, it would greatly be appreciated, since I'm trying to understand the rhyme and reason.

EDIT: To clarify, I'm trying to find a good way to pass a table-valued parameter.


texts: Install galera cluster without remove existing mariadb

texts:
I have to setup Galera cluster in one of my production servers.

OS: Ubuntu 14.04
Existing mysql: maridb 10.1

While installing this cluster, its asking to remove the existing mariadb-server-10.1

How can I install this without remove anything?


texts: Integration Services Catalogs - Version Description

texts: In Integration Services Catalogs I can see what versions of a package/project are available and if necessary revert to an older version. On this dialog I see a field "description" however I found no way yet to put a description into this field. How can it be edited/filled ?


texts: SQL Server Windows NT 64 bit Sudden High CPU Spike

texts:
I am facing a strange behavior of my SQL Server 2016 web edition. SQL Server Windows NT 64 bit suddenly jump to use 90% of the CPU and then it goes down after 5 sec, then the spike comes again after 3 to 5 mins (normal usage is just aroud 2% to 5% of cpu)

My server specs

Windows Server 2012 R2 Standard Evaluation (9600 build)
MS SQL Server 2016 Web
Intel Xeon E3-1245v5
64GB DDR4 ECC (2400 MHz)
3x480 GB SSD (Micron)

Server is up to date with latest updates installed and MS SQL Server is on default settings.
How can I find what is causing this. Please help me. Thanks

Please check the screenshots


texts: Selecting multiple values from a column and put them into separate columns

texts:
I have a table like this:

MainUser | PartyUser
--------------------
George   | Tom
Michael  | Tim
Michael  | James
Michael  | Monica
Jim      | Nick
Jim      | Connie

And I need to get something like this:

MainUser | PartyUser1 | PartyUser2 | PartyUser3 | PartyUser4 ...
----------------------------------------------------------------
George   | Tom        |            |            |
Michael  | Tim        | James      | Monica     |
Jim      | Nick       | Connie     |            |

Can someone help to find a way doing this in sql?
I'm having a hard time figuring this out, so any help would be greatly appreciated.


texts: ALTER COLUMN NOT NULL, not enough space in the log file

texts:
I am trying to run the following DDL
ALTER TABLE [dbo].[table_name] ALTER COLUMN [Value] [float] NOT NULL

This generates a large amount of log file data, and there is not enough space for it on the server.
Is it possible to somehow reduce the information being logged?
I'm using Microsoft SQL Server 2016 (SP1-CU15) (KB4495257)


texts: How to stop Firebird SuperClassic (run as application) in Win 7?

texts:
I run Firebird 2.5.3 SuperClassic as application in Windows 7 64-bit with this command:

fb_inet_server -a -m

Now, how can I stop/shutdown it from command prompt? I know that I could right-click its icon and choose Shutdown, but this is not what I'm looking for.

Thanks in advance.


texts: How to select functions that belong in a given extension in PostgreSQL?

texts:
I am trying to compose a SELECT from pg_catalog that pulls function name, its schema name and the name of extension that created this function.

To pull function list I use (a simplified version)

SELECT pg_proc.proname, pg_namespace.nspname AS proschema
FROM pg_proc
    INNER JOIN pg_namespace ON (pg_namespace.oid = pg_proc.pronamespace)

To select extensions I can run

SELECT pg_extension.extname, pg_namespace.nspname as extschema
FROM pg_catalog.pg_extension
    INNER JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = pg_extension.extnamespace)

Now I am looking for a link between extensions and functions they create when "CREATE EXTENSION". The SELECT should ideally produce a list of extension_name, extension_schema, foo_name, foo_schema


texts: RDS MSSQL in-place upgrade how to update master db compatibility level when rdsa account is db owner

texts:
Background

My workplace infrastructure team ran a typical in-place upgrade of an RDS MSSQL instance to update the version from 2012 to 2017.
After the upgrade, while logged in as the "master user", I was able to update all user and system databases to 2017 compatibility level except for master and rdsadmin. (I'm not worried about the latter.)
Keeping the master database at 2012 compatibility level concerns me.

Is it recommended that I also update the compatibility level of the master database to 2017? I presume that leaving it at the 2012 level means that I'd be sacrificing some functionality. (I opened a github issue with Microsoft's documentation for clarification.)
(Related question but not quite close enough to my scenario.)
If that is a yes, here is my problem
I cannot change the compatibility level of master. The "master user" assigned to this RDS instance is not the db owner for the master database. The db owner of the master database is rdsa, which as you're probably aware, is a disabled AWS sysadmin user that I cannot utilize.
Troubleshooting

I tried running

ALTER DATABASE master
SET COMPATIBILITY_LEVEL = 140;

But I got this error
User does not have permission to alter database 'master', the database does not exist, or the database is not in a state that allows access checks.

This makes sense. The "master user" that I am logged in as is not the db_owner of the master database.

Next, I asked the infrastructure team to reset the Master User password.
According to these links, resetting the password should reset anything wrong with the db_owner role:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.CommonDBATasks.ResetPassword.html
https://forums.aws.amazon.com/thread.jspa?messageID=939730
https://forums.aws.amazon.com/thread.jspa?messageID=706615

Unfortunately, this didn't change the db_owner of master. It was still rdsa. I rebooted the instance just in case and got the same results.

Then I tried adding the "master user" account as dbo in the master database.

USE [master]
GO

CREATE USER [mymasteruser] FOR LOGIN [mymasteruser]
GO

USE [master]
GO

ALTER ROLE [db_owner] ADD MEMBER [mymasteruser]
GO

That didn't work either, as expected.
User does not have permission to perform this action.

Help
Are there other steps on the configuration side that I can try? That side of things is a bit of a black box for me so any insight is appreciated.
My next step is to contact AWS support.


texts: Oracle: guaranteeing unmodified database state with queries?

texts:
If the first word of a SQL statement is either WITH or SELECT, is the statement guaranteed not to change the state of the database?  i.e. is this loop "safe"?

read SQL string
if string begins with SELECT or WITH
    process string
else
    error, do not execute

If the answer is no, what string(s) would allow data to be modified?

(note that this is a very specific question and not a request for general advice regarding database security.)


texts: How to update multiple rows without using JSON

texts:
I have a situation. In this example I want to update multiple notification definitions. Incoming data is very dynamic array object by user controlled. Sometimes new notifications added or removed.
My way of doing this is delete all unused notification definitions before insert new values. notifications.new_notification_definition procedure If given values are already exists in database update if not insert it. In this way I can update multiple rows. But I have a problem. It doesn't work. I don't know why sometimes new values are not inserted. Sometimes it does... Please help me what is best way to update multiple rows ? I have more problems like this.

DELETE FROM nd USING `notification_definitions` AS nd
WHERE
    `origin`     = 'table'       AND
    `type`       = 'date'        AND
    `table_name` = 'table_13' AND
    NOT EXISTS (
       SELECT NULL FROM $table WHERE `notification_definition_id` = nd.`id`
    );

UPDATE `notification_definitions` SET `is_active` = 'no' WHERE `table_name` = 'table_13';

CALL `notifications.new_notification_definition`('table_13', ?, ?, 123, ?, ?);
CALL `notifications.new_notification_definition`('table_13', ?, ?, 2, ?, ?);
CALL `notifications.new_notification_definition`('table_13', ?, ?, 7, ?, ?);
CALL `notifications.new_notification_definition`('table_13', ?, ?, 30, ?, ?);

Procedure notifications.new_notification_definition

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `notifications.new_notification_definition`(
IN `in_table_name`          VARCHAR(100),
IN `in_primary_field_label` VARCHAR(250),
IN `in_field_label`         VARCHAR(250),
IN `in_days`                INT(11) UNSIGNED,
IN `in_color`               VARCHAR(20),
IN `in_message`             VARCHAR(250)
)
BEGIN

-- Select old definition
SELECT `id` INTO @id FROM `notification_definitions`
WHERE
    `origin`              = "table"                  AND
    `type`                = "date"                   AND
    `table_name`          = `in_table_name`          AND
    `primary_field_label` = `in_primary_field_label` AND
    `field_label`         = `in_field_label`         AND
    `days`                = `in_days`
LIMIT 1;

IF @id IS NULL THEN
INSERT `notification_definitions` SET
    `origin`              = "table",
    `type`                = "date",
    `table_name`          = `in_table_name`,
    `primary_field_label` = `in_primary_field_label`,
    `field_label`         = `in_field_label`,
    `days`                = `in_days`,
    `color`               = `in_color`,
    `message`             = `in_message`,
    `created_at`          = NOW(),
    `updated_at`          = NOW();
ELSE
UPDATE `notification_definitions` SET
    `color`      = `in_color`,
    `message`    = `in_message`,
    `is_active`  = 'yes',
    `updated_at` = NOW()
WHERE id = @id
LIMIT 1;
END IF;

END ;;
DELIMITER ;

EDIT, Fixed problem

While I was posting this question at night I was tired and didn't think well. Now I have a new question and still remaining main question too. Problem was here
SELECT id INTO @id

After I changed
SET @id = (SELECT id query) it solved, I don't know why? My guess is @id is never NULL after set. Last value is still used even row wasn't found. Can you explain to me why ?

I changed my strategy to update multiple rows.
First Update old rows field is_active = 'no' and INSERT ON DUPLICATE KEY UPDATE new values with is_active = 'yes' as James suggested. And lastly DELETE rows did not updated. Is there any better way to update multiple rows ?


texts:
Version: PostgreSQL 10.3

PostgreSQL supports outputting JSON results. This seems to be a golden match for my application demands.

Now, before turning my application code upside down for benchmarks and tests, I did some queries with some EXPLAIN ANALYZE and almost didn't cost anything extra.

Simple example:

explain analyze
    select id, name, phonenumber, email
    from contacts
        order by name
        limit 27
        offset 2;
                       QUERY PLAN
══════════════════════════════════════════════════════════════
 Limit  (cost=62.61..62.68 rows=27 width=61) \
        (actual time=0.223..0.226 rows=27 loops=1)...

Complete explain for the plain query here.

explain analyze
    select json_agg(contacts) as contacts
    from(
        select id, name, phonenumber, email
            from contacts
                order by name
                limit 27
                offset 2
        ) as contacts;
                       QUERY PLAN
══════════════════════════════════════════════════════════════
 Aggregate  (cost=63.02..63.03 rows=1 width=32) \
            (actual time=0.272..0.272 rows=1 loops=1)...

Complete explain for the JSON query here.

I'm quite new in this (SQL performance) and it seems too good to be true. Are the following conclusions correct?:

Looking to the total execution time, the impact to output JSON is relatively low for the given example
I found that the "excluded" time for JSON aggregation is direct proportional to the size of the result set. (By increasing the limit option). However, the same proportional consequence will be on the client side if the result set will be parsed there.
With more complex queries like joins, the relative (%) overhead for JSON aggregation will be lower.


texts: Is it ok to have huge page life expectancy?

texts:
Recently I have buffer issue for my SSIS package.
When i started to monitor metrics in perfmon i noticed the PLE is nearly 500000.
Is it ok to have this huge PLE.


texts: MongoDB all replica set members are "OTHER"?

texts:
I am setting up a MongoDB replica set using EC2's image: https://aws.amazon.com/marketplace/pp/B00CO7AVMY/ref=dtl_recsim_B007IBMJPI_B00CO7AVMY_3

MongoDB version 2.6.5

I have managed to set up replicaset by not enabling "auth=true" in my config file.

I went ahead to add "auth=true" back into my config file and restarted mongodb on all 3 of my instances.

sudo service mongod restart

After restarting, I got into mongo shell and it is displaying

myReplicaSetName:OTHER>

I am unable to run rs.status() because I don't have a PRIMARY.

 "errmsg" : "not authorized on admin to execute command { replSetGetStatus: 1.0 }"


texts: This is a follow-up question to a previous thread I had posted. We're in the process of migrating to a new character set. Now, I am a programmer and we have DBA's who will be performing the work but I'd like to get a little bit more information about the process. Our current character set is WE8ISO8859P1 and uses BYTE character semantics. However, we are migrating to AL32UTF8. A new database was created containing the AL32UTF8 character set using BYTE semantics. The DBA ran a script on some tables that checked to see if there would be any issues (I believe it was CSALTER). After running the script, it said that some of the rows in our table would be truncated. That table contains a VARCHAR2(BYTE) column that's set to 4000 BYTES. The rows that resulted with potential issues all contain special characters. My question is, how can we export/import the tables in our database without having information truncated? Why is it saying that it would be truncated? My first thought was that since we changed the character set, it may be storing some of the characters from the old database table different than when it's stored into the new database with the character set using up more storage.


texts: Grouping on an expression (and possible HAVE)

texts:
I have the following query, which I rewrote as a 'simple' example below.

The expression CONVERT(int, RTRIM(' ' + UPPER(CONVERT(char, a+b))))/c is just a weird example of a complex function.

I am questioning myself:
 Why do I have to put this complex expression 3 times in my query?

DROP TABLE #TEMP
CREATE TABLE #TEMP (a int, b int, c int)

INSERT INTO #TEMP VALUES( 1,1,1)
INSERT INTO #TEMP VALUES( 1,1,2)
INSERT INTO #TEMP VALUES( 1,1,3)
INSERT INTO #TEMP VALUES( 1,2,1)
INSERT INTO #TEMP VALUES( 1,2,2)
INSERT INTO #TEMP VALUES( 1,2,3)
INSERT INTO #TEMP VALUES( 2,1,1)
INSERT INTO #TEMP VALUES( 2,1,2)
INSERT INTO #TEMP VALUES( 2,1,3)
INSERT INTO #TEMP VALUES( 2,2,1)
INSERT INTO #TEMP VALUES( 2,2,2)
INSERT INTO #TEMP VALUES( 2,2,3)

SELECT
    a,
    b,
    CONVERT(int, RTRIM(' ' + UPPER(CONVERT(char, a+b))))/c as d
from #TEMP
GROUP BY a, b, CONVERT(int, RTRIM(' ' + UPPER(CONVERT(char, a+b))))/c
HAVING CONVERT(int, RTRIM(' ' + UPPER(CONVERT(char, a+b))))/c >= 0
ORDER BY 3 DESC

The question is mainly for using in SQL Server, but answers for other database management systems are welcome too.


texts:
I have a backup of database version SQL Server 2008 R2. While restoring it to SQL Server 2012 I am getting error:

  msg 3183 RESTORE detected an error on page (6100:1886858449) in database ... .

While restoring the same file on another instance of 2008 R2, it is being restored successfully.

I performed DBCC CHECKDB(DB NAME) and then taken the manual backup and using this backup file, I restored the file. It got restored successfully.

Why does this happen? Am I right while doing this or is something missing?


texts:
We are doing a planned failover and switch back from the primary to DR with mirroring. We do not have a separate monitor instance.

Please help me with the following
1. Is it possible to switch back without the testing changes made in DR. OR fallback will have to include the changes tested from DR?
2. Does taking a backup prior to DR test impact log backup/copy/restore process

Many thanks for your helpful responses.


texts: "show create table" output with out "show"?

texts:
What

I'd like to be able to get the SQL statement by 'show create table' as something I could do something with along the lines of

insert into mytable (myTextColumn) (show create table blah)

I've been able to find some stored procedures that do this for other brands of SQL than I use.  I realize all the required information is in information_schema and I could use that to build the create statement myself.  It just seems like this is a problem that's been solved in other flavors and there's likely already a stored proc out there to do what I want w/o reinventing it myself for mysql.

Why

I'm trying to set some dynamic schema introspection as part of documentation.  The idea is I'd like to have the doc pages illustrate a current representation of the schema in the various environment it may exist in (dev, qa, prod).

The "obvious" thing to do is just query the dbs and "show create table blah".  However doing that requires select access on the table.  I don't really care to create an account with global read access to every table in every DB.

I'm looking to either have a restricted script on a cron that runs/collects the output of show create table; or have a mysql event that constructs the output I want in an event.  Either way this output would ultimately be persisted to a documentation db w/ just the schemas stored so the documentation app can pull this information without its db account having select access to the actual tables.

I'm tempted to lean toward an internal event that populates this just so there's not an account out there with wide read access.

Why the why

I'm explaining my ultimate goal in case someone can point out a different implementation path or solution that matches my requirements (or convince me my requirements are off to begin with).


texts: Postgres partition search in reverse order of partitions

texts:
Our production system is on postgres 9, with a partition table. In one of the select queries, we cannot pass the partition key for the select, but we want the data from the latest partition, is it possible to make the postgres search from the last partition backwards?

EXPLAIN
SELECT *
FROM user.user
WHERE username = 123456
ORDER BY start_time DESC
LIMIT 1;

This searches from the earliest partition to the most recently created partition even though I've ordered my partition key in descending order. Is there a way to make postgres look from the recent most partition and look backward?

DDL of table:

create table IF NOT EXISTS user.user
(
    id                      uuid      default journey.uuid_generate_v4() not null,
    username                varchar(255),
    email_id                varchar(255),
    start_time              timestamp not null,
    end_time                timestamp not null
);

create table if not exists user.user_201301
(CHECK ( start_time >= DATE '2013-01-01' AND start_time < DATE '2013-02-01')) INHERITS (user.user);
ALTER TABLE if exists user.user_201301 ADD PRIMARY KEY (id);
....

We've partition tables until 202101 and will continue.

On the above select query, the explain looks at all the partitions from 201301 till 202101, how do I make it look from 202101 backwards to 201301, this will make my query easier.


texts: trying to merge chunks to trigger better balancing after 50% of the data was deleted by the developers

texts:
Trying to merge chunks using the following command:

         db.adminCommand
         ( {
           mergeChunks: "HTMLDumps.HTMLRepository",
           bounds: [ {   "ShardMapId" : 2, "DomainId" : 62 },
           {  "ShardMapId" : 2, "DomainId" : 162 } ]
         } )

getting the following error when trying to run the above command to try to merge any of the available consecutive chunks available on a shard:

             {
             "ok" : 0,
             "errmsg" : "Failed to commit chunk merge :: caused by ::
             DuplicateKey: chunk operation commit failed: version
             32|6||5ba8d864bba4ff264edf0bd9 doesn't exist in
             namespace: HTMLDumps.HTMLRepository. Unable to save
             chunk ops. Command: { applyOps: [ { op: \"u\", b: false,
             ns: \"config.chunks\", o: { _id: \"HTM
             Dumps.HTMLRepository-ShardMapId_2.0DomainId_62.0\", ns:
             \"HTMLDumps.HTMLRepository\", min: { ShardMapId: 2.0,
             DomainId: 62.0 }, max: { ShardMapId: 2, DomainId: 162 },
             shard: \"shard0000\", lastmod: Timestamp(32, 6),
             lastmodEpoch: ObjectId('5ba8d864bba4ff264edf0bd9') },
             o2: { _id: \"HTMLDumps.HTMLRepository-
             ShardMapId_2.0DomainId_62.0\" } }, { op: \"d\", ns:
             \"config.chunks\", o: { _id: \"HTMLDumps.HTMLRepository-
             ShardMapId_2DomainId_109\" } } ], preCondition: [ { ns:
             \"config.chunks\", q: { query: { ns:
             \"HTMLDumps.HTMLRepository\", min: { ShardMapId: 2.0,
             DomainId: 62.0 }, max: { ShardMapId: 2, DomainId: 109 }
             }, orderby: { lastmod: -1 } }, res: { lastmodEpoch:
             ObjectId('5ba8d864bba4ff264edf0bd9'), shard:
             \"shard0000\" } }, { ns: \"config.chunks\", q: { query:
             { ns: \"HTMLDumps.HTMLRepository\", min: { ShardMapId:
             2, DomainId: 109 }, max: { ShardMapId: 2, DomainId: 162
             } }, orderby: { lastmod: -1 } }, res: { lastmodEpoch:
             ObjectId('5ba8d864bba4ff264edf0bd9'), shard:
             \"shard0000\" } } ], writeConcern: { w: 0, wtimeout: 0 }
             }. Result: { applied: 1, code: 11000, codeName:
             \"DuplicateKey\", errmsg: \"E11000 duplicate key error
             collection: config.chunks index: ns_1_min_1 dup key: { :
             \"HTMLDumps.HTMLRepository\", : { ShardMapId: 2.0,
             DomainId: 62.0 } }\", results: [ false ], ok: 0.0,
             operationTime: Timestamp(1554112692, 1), $gleStats: {
             lastOpTime: { ts: Timestamp(1554112692, 1), t: 13 },
             electionId: ObjectId('7fffffff000000000000000d') },
             $clusterTime: { clusterTime: Timestamp(1554112692, 1),
             signature: { hash: BinData(0,
             0000000000000000000000000000000000000000), keyId: 0 } }
             } :: caused by :: E11000 duplicate key error collection:
             config.chunks index: ns_1_min_1 dup key: { :
             \"HTMLDumps.HTMLRepository\", : { ShardMapId: 2.0,
             DomainId: 62.0 } }",
             "code" : 11000,
             "codeName" : "DuplicateKey",
             "operationTime" : Timestamp(1554112687, 1),
             "$clusterTime" : {
             "clusterTime" : Timestamp(1554112687, 1),
             "signature" : {
                "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                "keyId" : NumberLong(0)
               }
              }
             }

This is happening regardless of which chunks i select. Main reason for me to try to do this is to achieve true data balancing and not just chunk numbers. Recently developers deleted 90% of the data from these chunks that caused the distribution to get to 90% 10% state from 60/40 earlier. I hope to merge/remove empty chunks to ensure balancing of the data to reach as close to 60/40 as possible.


texts: SHOW tables returns empty set, but MYI.Crypt files are there

texts:
Something really weird happened to my VPS last week. Suddenly, MySQL stopped working. My VPS has CentOS 6 and uses Parallels Plesk. After this, Parallels Plesk stopped working, and trying to start MySQL via SSH just gave me an error
Mysql daemon failed to start.

I added this skip-grant-tables to my.cnf and I was able to start MySQL and access it... in MySQL I can see the database names. I selected the database using use [database-name] and I can select it, but when I use show tables it shows an empty set.

At this point I thought all the info is gone and that's it. But, outside MySQL, when I go to /var/lib/mysql, I see folders with the database names, and inside those folders, files with the table names MYD.crypt or MYI.Crypt.

At this point, all I want is to be able to create a dump file of a certain database. Is there a way to make MySQL use these files so I can recover that data and be able to export it?

Thank you.


texts: Database growing like crazy - what do I do?

texts:
I have an Epicor 9 ERP database that I recently was put in charge of (new job). Server is running Server 2008 R2 with SQL Server 2008 R2 64bit. The main production database started at 16gb 7 months ago (go-live), and has grown steadily to 80gb+. They did schedule a maintenance job to Shrink all DB's each Sunday night, limit 50mb, free space 10%.

At it's current rate of growth the server's drive space will be maxed in less than 90 days, and I'll be forced to either upgrade the server or move the DB to a SAN. Overall system performance is terrible, system is just plain slow.

When I run the "disk usage by top tables" report I see the following:

Number of records: 115,117,737
Reserved (KB): 83,609,584
Data (KB): 81,867,248
Indexes (KB): 52,263,296
Unused (KB) 1,742,336

I am waiting to hear back from Epicor regarding proper care and feeding of their system, but the Indexing seems to be using an insane amount of space. As stated, I only recently took over this system and I'm trying to figure it out as quickly as I can.

Any help would be appreciated.

EDIT: Warning to anyone who reads this. Dropped the duplicate indexes as described in the answers here completed hosed by test Epicor 9 database. E9 apparently uses indexes in a non-standard way. It has a Progress layer which optimizes the indexes for Progress and 4GL (aka ABL) code. I can't state definitively that this caused the problem, but it seems the most likely culprit. Attempting to log into E9 Test gives a "SQL Scripts need to run" error and the database appears to be stuck in an ALTER state.


texts: How to check if the current connection is in a transaction?

texts:
I need my program code to ensure that certain part of logic is being executed within a transaction.

What query would tell me the current transaction ID  / other information that would allow me to determine if I am in a transaction from within the transaction?

BEGIN;
-- How to check if I am in a transaction?
COMMIT;


texts: CopyDatabaseWizard.exe issue on SQL 2014

texts:
I have four servers running Server 2008r2 SP3, of which three were running SQL Server Express 2008r2 and one was running SQL Server Standard 2008r2. After much ado I got these all upgraded to be running SQL Server Standard 2014 SP2.
These servers have .NET 4.6.2 installed and Powershell 2.0 installed.

Unfortunately, now I'm getting this issue in the Application Log on each of these four servers:

Event ID 63:
SideBySide (Application Log)
Activation context generation failed for "c:\program files (x86)\microsoft sql server\120\Tools\Binn\managementstudio\CopyDatabaseWizard.exe".
Error in manifest or policy file "c:\program files (x86)\microsoft sql server\120\Tools\Binn\managementstudio\CopyDatabaseWizard.exe" on line 8.
The value "1.0" of attribute "version" in element "assemblyIdentity" is invalid.

All I can find are some vague technet posts suggesting to use a hex editor to edit the assembly, but that apparently leads to another issue. Editing the exe shouldn't be necessary IMO.

Related technet posts:

https://social.technet.microsoft.com/Forums/office/en-US/43ff2fe9-3112-4c0e-ba57-5a2d85a018ab/exception-has-been-thrown-by-the-target-on-an-invocation-during-installation-of-sql-2014-ctp2?forum=sqlsetupandupgrade
https://social.msdn.microsoft.com/Forums/silverlight/en-US/fa5901bc-a93e-444d-8208-8f2f5584ed94/sidebyside-63?forum=sqlgetstarted
https://connect.microsoft.com/SQLServer/feedback/details/1764660/sidebyside-error

Has anyone run across this or know how to fix this issue? Or is this issue safe to ignore?


texts: How to restore a file group from file group backup?

texts:
I want to restore a specific file group from file group backup. I found examples how to restore it from full backup, but I want to do it from file group backup. Every time when I try, all of the files in the file group stuck in 'Restoring' state and cannot be accessed any more.
The way I'm trying is:

Creating Backups:

BACKUP DATABASE myDataBase FILEGROUP = 'secondaryFilegroup' TO DISK = N'D:\DBBackups\secondaryFilegroupBackup.bak' WITH INIT
BACKUP LOG myDataBase TO DISK = N'D:\DBBackups\tlog.trn';

Restoring:

RESTORE DATABASE myDataBase FILEGROUP = 'secondaryFilegroup' FROM DISK = N'D:\DBBackups\secondaryFilegroupBackup.bak'
RESTORE LOG myDataBase FROM DISK = N'D:\DBBackups\tlog.trn';

  Error: The log in this backup set terminates at LSN 4323000025691200001, which is too early to apply to the database. A more recent log backup that includes LSN 4323000025741600001 can be restored.

Can someone explain me what are the correct steps?

UPDATE
It looks like you misunderstood me. Sorry for my bad explanation and my late response. My task is to create a database with a file group with 3 files. Then I have to create a table in this file group. I have to make backup of the file group, then delete some rows and then have to restore the file group and the table must be as the beginning.
So i create a File group named "secondaryFilegroup" and a table in it named "testTable" with 1000 rows. Then I deleted 100 rows(so at the table remain 900) and restored the file group (so I expect to have again 1000 rows). But with this solution that you gave me I still have 900 rows.
My solution is based on answer from @Scott Hodgin.
PS: Im not sure is this even possible.

BACKUP DATABASE myDatabase FILEGROUP = 'secondaryFilegroup' TO DISK = N'E:\DBBackups\secondaryFilegroupBackup.bak' WITH INIT
BACKUP LOG myDatabase TO DISK = N'E:\DBBackups\tlog.trn' WITH INIT;

DELETE TOP(100) FROM testTable;

use master;

RESTORE DATABASE myDatabase FILEGROUP = 'secondaryFilegroup' FROM DISK = N'E:\DBBackups\secondaryFilegroupBackup.bak' WITH norecovery

RESTORE LOG myDatabase
FROM DISK = N'E:\DBBackups\tlog.trn'
WITH recovery;

BACKUP LOG myDatabase TO DISK = N'E:\DBBackups\ttomov_test\SZH\tlog2.trn' WITH INIT;

RESTORE LOG myDatabase
FROM DISK = N'E:\DBBackups\ttomov_test\SZH\tlog2.trn'
WITH recovery;


texts: Insert to particular location in Oracle DB table?

texts:
Suppose I have a table containing the following data:

Name | Things
-------------
Foo  |   5
Bar  |   3
Baz  |   8

If I want to insert a row, so that the final state of the table is:

Name | Things
-------------
Foo  |   5
Qux  |   6
Bar  |   3
Baz  |   8

Is this possible?

I understand we don't typically rely on the order of rows in a table, but I've inherited some code that does precisely that. If I can insert to a location, I can avoid a significant refactor.


texts:
MYSQL patching.

I took all database backups before starting patching.
If we patch mysql 5.6.30 to 5.6.32 and if we want to rollback to 5.6.30 due to some issues. Can we do that? if so should we have to rollback entire server ?Or is there any way just to roll back mysql?


texts: When will autovacuum work in PostgreSQL?

texts:
I tested the autovacuum in PostgreSQL. So I create a table company and insert 100 records. Then I checked the table size like,

ganapathy=> \dt+ company
                     List of relations
 Schema |  Name   | Type  |   Owner   | Size  | Description
--------+---------+-------+-----------+-------+-------------
 public | company | table | ganapathy | 54 kB |

After that I update all the records in a table like,

ganapathy=> update company set address = 'California' where id between 1 and 100;

Then I checked the table size, It gives the size like this,

ganapathy=> \dt+ company
                     List of relations
 Schema |  Name   | Type  |   Owner   | Size  | Description
--------+---------+-------+-----------+-------+-------------
 public | company | table | ganapathy | 72 kB |

So checked the configuration file of postgresql.conf, In that the autovacuum is already enabled,

autovacuum = on

But the autovacuum has not worked. So after two days I checked the table size again,
But even though the table size has not changed.

ganapathy=> \dt+ company
                     List of relations
 Schema |  Name   | Type  |   Owner   | Size  | Description
--------+---------+-------+-----------+-------+-------------
 public | company | table | ganapathy | 72 kB |

So I want to know,

  Why its not working for me?
  When the autovacuum process will work in PostgreSQL?

Please help me.

Thanks in Advance...!


texts: Common entities with differing attributes across multiple databases

texts:
We have two systems each with their own database. Each system has Parties (Customers and Vendors) and each of these can have one or more Sites (addresses) for different functions (delivery, holding, ...).  Some of the Customers and Vendors appear in both systems. Both systems need to keep track of customer and vendor Licences. A Licence belongs to a Party and applies to a specific Site. So it would make sense to hold the common data (Parties, Sites, Licences) in a shared database to simplify maintenance etc.
However, each system has different attributes for Customer, Vendors and Sites that make sense only in that system.
For example system A holds routing information against each customer that is not relevant to system B. Further, the routing codes are foreign keys to a Routes table that would only exist in A. So it would not make sense to store a Customer's routing info in the common database. (There are many other examples.) So it looks like I will need to maintain entities in all three databases (A, B and Common), and keep the entities in sync - which will kind of cancel out the gains I hoped to make.
So my question is: has anyone come up with a satisfactory way to deal with this kind of thing? Or should I just combine the two systems?


texts: Postgresql - DB listing few DBs twice in the instance

texts:
when I do \l+ in the postgres instance, I can see a few database twice.

Something like

 foo            | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 584 MB  | pg_default |

 foo            | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 584 MB  | pg_default |

 bar            | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 594 MB  | pg_default |

 bar            | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 594 MB  | pg_default |

I am able to connect to both databases "foo" and "bar" without issues. Locally and using connection string.

Will there be any issues with having the databases listed more than once?
How can I remove the duplicate entry from the postgres server?

Please suggest.


texts:
I would like to select and insert data from an old table to the new table:

CREATE TABLE test2 LIKE test1
INSERT INTO test2 SELECT timestamp, datenum FROM test1 WHERE datenum>=23 and datenum<=1000000 ON DUPLICATE KEY UPDATE datenum=VALUES(datenum)

Also I have and id column in the test2 and test1, but I would like to start the id from 1. If I use the query above I'm receiving the following error

ERROR 1136: Column count doesn't match value count at row 1

INSERT INTO test2 SELECT * FROM test1 WHERE datenum>=23 and datenum<=1000000 ON DUPLICATE KEY UPDATE datenum=VALUES(datenum)

In this case the id start from 3239.


texts: Shared and IX locks causing deadlock (Sql server)

texts:
In my environment, I have 2 tables - Issue and IssuePropertyValues (which has foreign key reference to the Issue table).
Now, we insert values into these 2 tables using the below 2 queries:

INSERT INTO [Issue] ([IssueId], [AssetId]) VALUES (?, ?)
INSERT INTO [IssuePropertyValues] SELECT TOP 1 AssetIssueId, ?, ? FROM [Issue] WHERE [AssetId] = ? AND [IssueId] = ? "

These 2 statements are invoked from the java layer using batch update as shown:

issueStmt.executeBatch();
issuePropertiesStmt.executeBatch();

Now when more than 10000 values are being inserted, SQL server is throwing deadlock errors:

  java.sql.BatchUpdateException: Transaction (Process ID 506) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

When I studied the deadlock behavior using DBCC TRACEON(1204,-1), I learnt that
the deadlock is for a page lock resource. What I see is that one of the stmts is having a shared lock, and another one an IX(Intent exclusive) lock. But I am not able to understand why this should be causing a deadlock. I understand shared and IX locks are not compatible with each other, but if a deadlock has to happen , it should throw even when few records are inserted.Why is it throwing when 10,000 plus records are inserted?

The DBCC trace snippet is as below

owner-list
2011-12-16 04:02:09.40 spid32s          owner id=process1b1c8ce08 mode=IX spid=372
INSERT INTO [IssuePropertyValues] SELECT TOP 1 AssetIssueId,  @P0 ,  @P1  FROM [Issue] WHERE [AssetId] =  @P2  AND [IssueId] =  @P3

waiter id=process1b1c8c748 mode=S requestType=convert
pagelock fileid=5 pageid=3506256 dbid=7 objectname=Issue mode=IX

Thanks for the explanation. Yes, I shall implement the solution you suggested. I realised that fetching the id using select clause is the culprit.

But I m still not really clear on the cause of the deadlock. What I understand is, when the first insert statement to the issue table executes, it will have an IX lock on the table. Meanwhile, when the second insert statement to IssuePropertyValue table tries to execute, it will try acquiring a shared lock on the Issue table(because of the select clause). But it cannot get the lock as the table is already having an IX lock. But why this should be a deadlock? Once the IX lock is released, second statement should be able to get the Shared lock. Am i correct in my understanding?

It's only the second statement that is waiting on the first stmt to release the lock, but the first statement is not waiting on the second statement . So, ideally its not a deadlock. Does it have something to do with the executeBatch() called from java layer?


texts: DB2 Setting up table access based on LDAP groups

texts: I have a DB2 database installed and set up. My users can access any table that I grant SELECT to the PUBLIC group. However, it doesn't appear to acknowledge the groups the user is in in LDAP. The user belongs to the groups Development and ITDev. I have added both those groups to a table with SELECT permissions, but the user gets a message that their id does not have select access to that table. What do I need to change?


texts: Problem with enabling System Audit (Firebird's Trace and Audit Services)

texts:
I would like to enable System Audit on my Firebird 2.5.9.27139 (x64) database (Windows 10), to inspect all queries performed on it.
I've already read some docs about what the configuration file should look like, but most likely I have some trivial problem with setting up all the paths and entries.
I would be very grateful if someone could have a glance at my settings and point out what should I change.
My database is located in: C:\KSBAZA\KS-APW\WAPTEKA.FDB
I would like to store all logs and trace config file in the directory: C:\DB_trace
In the main Firebird directory, I uncommented and set the line of firebird.conf file to:
AuditTraceConfigFile = C:\DB_trace\apw_trace.conf
Content of C:\DB_trace\apw_trace.conf:
<database WAPTEKA.FDB>
  enable true
  log_filename C:\DB_trace\audit_trace_database.log
  max_log_size 0
  include_filter %(INSERT|UPDATE|DELETE)%
  log_connection true
  connection_id 0
  log_transaction true
  log_statement_prepare true
  log_statement_free true
  log_statement_start true
  log_statement_finish true
  log_procedure_start true
  log_procedure_finish true
  log_trigger_start true
  log_trigger_finish true
  log_errors true
  log_warnings true
  log_initfini true
  log_sweep true
  time_threshold 0
  max_sql_length 65536
</database>

<services>
  enabled true
  log_filename C:\DB_trace\audit_trace_services.log
  max_log_size 0
  log_services true
  log_errors true
  log_warnings true
  log_initfini true
</services>

After saving the changes to the firebird.conf file, I completely rebooted the machine (the editing was performed while the Firebird service was down.)
Moreover, I tried to place the apw_trace.conf file in the same dir as the firebird.conf one (now AuditTraceConfigFile = apw_trace.conf), but without any result - they're no log files created in the C:\DB_trace dir (should I touch these empty *.log files?).
Thank you in advance for your help.


texts: Write a slow query to test slow query logging?

texts:
Is there a simple query that would take > 2 sec so that I can test the slow query logger?

I am looking for something like a generic recursive or iterative statement.


texts: Availability Group configuration & licencing

texts:
I am about to setup a 3 node AV group for SQL Server 2012 my configuration will be -

London-Srv1 - PRIMARY
Core Database  (OLTP)
set to READ_WRITE

London-Srv2 - SECONDARY PASSIVE synchronous
Core Database   (OLTP)
automatic failover
no connections allowed (for quick writes from PRIMARY)

Cambridge-Srv1 - PASSIVE asynchronous
Core Database  (OLTP)
no automatic failover
READ_ONLY connections

A Reporting DB is currently restored nightly from core and then heavily indexed. My original intention was to move it to the London-Srv2 and leave it outside the availability group. But I moved the READ_ONLY connections to the Cambridge server as read my synchronous server should not allow connections to make the writes as fast as possible. I would also have to licence both REPLICA nodes, which isn't an option at that cost!

IF I configure READ_ONLY routing to Cambridge, does SQL Server detect which are read_only queries automatically or does every connection string have to specify that they are read only?

Will this perform OK with a decent link between data centres or should I licence the Srv2 in London and keep all READ_ONLY connections accessing here, along with reporting database ?

For a bit of background, its a bookings website, so not huge volumes of data and losing a minute or 2 of data is not mission critical like financial orgs


texts: Does the table constraint CHECK gets executed on deletions?

texts:
I guess not but if someone has an idea perhaps could give me a hand.

I'm trying to restrict the deletion of a row of a table under some circumstances that requires to query against other tables.

An example

Each worker (table worker) references the physical person (table person).
The check aims to allows the existence of workers that will work with his actual_hapiness_level (from table worker) >= base_hapiness_level (from table person), so all the underlying persons are happy.

There is an important exception, is you hire a worker that acts a clown, everybody is happy and therefore the former restrictions doesn't apply. Try to follow the example running one statement at the time, reading the comments.

And the end you will get inconsesty from a "business" point of view.

create table person (
    id serial primary key,
    base_hapiness_level int,
    name text
);
create table worker (
    id serial primary key,
    person_id int,
    actual_hapiness_level int,
    acts_as_clown bool default false,
    CONSTRAINT person_id_fkey FOREIGN KEY (person_id)
          REFERENCES person (id) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE OR REPLACE FUNCTION is_worker_happy_according_to_his_personal_values (bigint,bigint) RETURNS boolean AS $BODY$
DECLARE
    BEGIN
    RAISE NOTICE 'Performing things_are_okey(person_id= %,base_hapiness_level= %)', $1, $2;
    RETURN ($2 >= (select p.base_hapiness_level from person p where p.id = $1));
    END;
$BODY$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION is_there_a_clown_in_there_to_handle_the_unhappiness () RETURNS boolean AS $BODY$
DECLARE
    BEGIN
    RAISE NOTICE 'Performing is_there_a_clown_in_there_to_handle_the_unhappiness';
    RETURN EXISTS (select * from worker where acts_as_clown is true);
    END;
$BODY$ LANGUAGE plpgsql IMMUTABLE;

ALTER TABLE worker ADD CONSTRAINT person_can_work check (
    is_worker_happy_according_to_his_personal_values(person_id,actual_hapiness_level) OR
    (NOT is_worker_happy_according_to_his_personal_values(person_id,actual_hapiness_level) AND is_there_a_clown_in_there_to_handle_the_unhappiness())
);

-- create 3 persons
insert into person (base_hapiness_level,name) values (50,'robert');
insert into person (base_hapiness_level,name) values (10,'cris');
insert into person (base_hapiness_level,name) values (0,'the_clown');

-- insertion 1 is okey, as there is no clow required to the hapiness level of robert
insert into worker (actual_hapiness_level,person_id) values (50,(select id from person where name = 'robert'));

-- insertion 2 goes wrong, as there is no clown to handle the unhapiness of crish
insert into worker (actual_hapiness_level,person_id) values (1,(select id from person where name = 'cris'));

-- let's hire a clown.....
insert into worker (actual_hapiness_level,person_id,acts_as_clown) values (100,(select id from person where name = 'the_clown'),true);

-- try to insert cris again (with the clown is alright)
insert into worker (actual_hapiness_level,person_id) values (1,(select id from person where name = 'cris'));
-- and works ;)

-- but if we kick the clown, now there is no one to handle the unhapinness what a mess!! what about the check :(!!!
delete from worker where person_id = (select id from person where name = 'the_clown')

As you can realize, the check is not beign performing on the deletion of the clown and that is a problem in this example domain model


texts: Unable to output data from tables in pgAdmin4 due to missing oid operator

texts:
I am currently running Postgresql 10.6 locally which I interface with using PgAdmin 4.12, up until today everything was running fine. However today I ran the following query in the pgAdmin query editor:

SELECT * FROM test_table LIMIT 100

and got the following error:

ERROR:  operator does not exist: - oid at character 125
HINT:  No operator matches the given name and argument type. You might need to add an explicit type cast.
STATEMENT:  SELECT at.attname, at.attnum, ty.typname
        FROM pg_attribute at LEFT JOIN pg_type ty ON (ty.oid = at.atttypid)
        WHERE attrelid=-1519044407::oid AND attnum = ANY (
            (SELECT con.conkey FROM pg_class rel LEFT OUTER JOIN pg_constraint con ON con.conrelid=rel.oid
            AND con.contype='p' WHERE rel.relkind IN ('r','s','t', 'p') AND rel.oid = -1519044407::oid)::oid[])

The strange thing is when I run the same command on tables which were created yesterday, the data is output into the Pgadmin data output window successfully. I also tried running the same command with psql:

psql -U postgres -d geodata -c 'SELECT * FROM test_table LIMIT 100'

which was also successful. I am able to create tables in Pgadmin just not output them directly. Any new table I create and then output ends with the top error, the only difference is the oid changes. I have uninstalled Pgadmin and made sure to delete all leftover folders and reinstalled with no change.

Does anyone have any ideas what the problem is? Is the issue coming from pgadmin or is my Postgresql server corrupted in some way?


texts: SQL Server Agent Alert does not pick up some of Events from Application Log written by SQL Server Audit

texts:
My end goal is to be emailed when new object (schema, table, procedure etc.) is created / altered / dropped at one of my databases on SQL Server

I created a server-level audit, that writes to Windows application log, and a database audit specification at my database, that audits two below event classes:

DATABASE_OBJECT_CHANGE_GROUP
SCHEMA_OBJECT_CHANGE_GROUP

Then I tested it, by creating a table, procedure, altering schema, etc.
All those changes were recorded by audit into Application Log successfully, with Event ID = 33205

Next step, I created an Alert at SQL Server Agent, which is supposed to be raised on error number 33205. But this Alert is not working, it is not triggered when objects are changed at my database, and email is not sent

QUESTION: -- Could you help me to understand why alert is not triggered
and not sending emails ?

p.s. I have other alerts based on particular error numbers in place, such as "Login Failed" or "Availability Group"-related which work perfectly fine, and shooting emails)

p.p.s. scripted audit / specification / alert below:

CREATE SERVER AUDIT [DB_Schema_And_Object_Changes_To_App_Log]
    TO APPLICATION_LOG
WITH
    (QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = '55c92648-fc06-47b7-b48d-3a613ba3b8aa')

ALTER SERVER AUDIT [DB_Schema_And_Object_Changes_To_App_Log]
WITH (STATE = ON)

CREATE DATABASE AUDIT SPECIFICATION [DB_Schema_And_Object_Changes_To_App_Log_Spec]
FOR SERVER AUDIT [DB_Schema_And_Object_Changes_To_App_Log]
    ADD (DATABASE_OBJECT_CHANGE_GROUP),
    ADD (SCHEMA_OBJECT_CHANGE_GROUP)
    WITH (STATE = ON)

EXEC msdb.dbo.sp_add_alert @name=N'DB or Schema Object Change',
    @message_id=33205,
    @severity=0,
    @enabled=1,
    @delay_between_responses=0,
    @include_event_description_in=1,
    @job_id=N'00000000-0000-0000-0000-000000000000'

EXEC msdb.dbo.sp_add_notification
   @alert_name=N'DB or Schema Object Change',
   @operator_name=N'DB-Notifications', @notification_method = 1


texts: Is there any string compaction option for PostgreSQL?

texts:
I have a table like this:

CREATE TABLE BLOG(
   CATEGORY CHAR(32)
);

Category is a column, which stored enum values.

For example, I have 1 millions of rows and 10 unique values of Category.

Let's populate the table with sample data:

CREATE TABLE TMP_STRINGS(
   id SERIAL PRIMARY KEY,
   CATEGORY CHAR(50)
);

insert into TMP_STRINGS values (1, 'category1'), (2, 'category2'), (3, 'category3'), (4, 'category4'), (5, 'category5'), (6, 'category6'), (7, 'category7'), (8, 'category8'), (9, 'category9');

insert into blog(category)
SELECT category FROM (SELECT floor(random() * 9 + 1)::int as n from generate_series(1,1000000)) AS T INNER JOIN tmp_strings ON T.n = tmp_strings.id;

This database is 42 MB:

SELECT pg_size_pretty(pg_total_relation_size('"blog"'));

A row of database is 44 byte:

SELECT octet_length(t.*::text) FROM blog AS t WHERE category='category1' LIMIT 1;

Categories can be encoded with smallint, like:

category1 -- 1
category2 -- 2
category3 -- 3
category4 -- 4
category5 -- 5
category6 -- 6
category7 -- 7
category8 -- 8
category9 -- 9

Is there any way to do this automatically? I mean some option or module for String interning (String pooling).


texts: Should we favor multiple INSERTs or multiple UPDATEs

texts:
I always have an impression that INSERT would be beneficial for performance (let's forget storage for now) rather than UPDATES, in all the DB that I know. Especially in Postgres, UPDATES involves transaction and may result in locks

Imagine we are building a shopping cart. For each item I have a table with these fields [row_id, product_id, quantity].

Should I model data for UPDATE (I update quantity column multiple times when user add/remove from cart)

[row_id = 1, product_id = A, quantity = 3]

or to model for multiple INSERTS (I insert a line for each time user clicks, and remove all when they remove from cart) like an event stream

[row_id = 1, product_id = A, quantity = 1] // user clicks add to cart
[row_id = 2, product_id = A, quantity = 1] // user clicks add to cart
[row_id = 3, product_id = A, quantity = 1] // user clicks add to cart
[row_id = 4, product_id = A, quantity = -1] // user clicks remove from cart


texts: Need to create combinations of Parents/Children/Dogs, but can't get past 2nd level of CTE

texts:
I've hit a brick wall and please ask help.

I'm building a table up for (in this example) a ticketing system
where e.g. Ticket 1 will allow
Parent: Either parent Peter or Sally,
Child: to come without a child or with a child (either Sam, Pete or John),
Dog: and either without a dog or with one of the dogs (DogMatix,Snowy,Bliksem)

The table layout is that the Parent/Child/Dog is a parameter, and I can get (with CTE) to the Parent/Child relationships to all show, but not the next level showing the dogs too

declare @Ticket table
(
TID int
)

declare @Param table
(PID int IDENTITY(1,1) not null,
TID int,
ParamType varchar(20),
Val  varchar(40)
)

Insert into @Ticket(TID) Select(1)
Insert into @Ticket(TID) Select(2)

Insert into @Param(TID,ParamType,Val) values(1,'Parent','ParentPeter')
Insert into @Param(TID,ParamType,Val) values(1,'Parent','ParentSally')
Insert into @Param(TID,ParamType,Val) values(1,'Child','ChildSam')
Insert into @Param(TID,ParamType,Val) values(1,'Child','ChildPete')
Insert into @Param(TID,ParamType,Val) values(1,'Child','ChildJohn')
Insert into @Param(TID,ParamType,Val) values(1,'Dog','DogDogMatix')
Insert into @Param(TID,ParamType,Val) values(1,'Dog','DogSnowy')
Insert into @Param(TID,ParamType,Val) values(1,'Dog','DogBliksem')

Insert into @Param(TID,ParamType,Val) values(2,'Parent','ParentParker')
Insert into @Param(TID,ParamType,Val) values(2,'Parent','ParentScarlett')
Insert into @Param(TID,ParamType,Val) values(2,'Child','ChildBrett')
Insert into @Param(TID,ParamType,Val) values(2,'Child','ChildBritney')
Insert into @Param(TID,ParamType,Val) values(2,'Child','ChildBronwyn')
Insert into @Param(TID,ParamType,Val) values(2,'Dog','DogRover')
Insert into @Param(TID,ParamType,Val) values(2,'Dog','Dog2')
Insert into @Param(TID,ParamType,Val) values(2,'Dog','Dog3')

;with cte  as (
Select TID,Val as 'Parent'
from @Param p
where  p.ParamType = 'Parent'
 )
Select cte.TID,cte.Parent,p.Val as 'Child'
from cte
left outer join @Param p on p.TID = cte.TID and  p.ParamType = 'Child'

--The required results will be (for ticket 1)
--TID       Parent          Child           Dog
--1     ParentPeter     ChildSam        DogDogMatix
--1     ParentPeter     ChildSam        DogSnowy
--1     ParentPeter     ChildSam        DogBliksem
--1     ParentPeter     ChildPete       DogDogMatix
--1     ParentPeter     ChildPete       DogSnowy
--1     ParentPeter     ChildPete       DogBliksem
--1     ParentPeter     ChildJohn       DogDogMatix
--1     ParentPeter     ChildJohn       DogSnowy
--1     ParentPeter     ChildJohn       DogBliksem
--1     ParentPeter     null            null
--1     ParentPeter     ChildSam        null
--1     ParentPeter     ChildPete       null
--1     ParentPeter     ChildJohn       null

--1     ParentSally     ChildSam        DogDogMatix
--1     ParentSally     ChildSam        DogSnowy
--1     ParentSally     ChildSam        DogBliksem
--1     ParentSally     ChildPete       DogDogMatix
--1     ParentSally     ChildPete       DogSnowy
--1     ParentSally     ChildPete       DogBliksem
--1     ParentSally     ChildJohn       DogDogMatix
--1     ParentSally     ChildJohn       DogSnowy
--1     ParentSally     ChildJohn       DogBliksem
--1     ParentSally     null            null
--1     ParentSally     ChildSam        null
--1     ParentSally     ChildPete       null
--1     ParentSally     ChildJohn       null


texts:
What is the difference in accessing a table with alias "AS" and without using "AS". Is there any performance enhancements?

For Example :

SELECT S.ID,S2.Value FROM dbo.Sample AS S
INNER JOIN dbo.Sample2 AS S2 ON
S.ID = S2.ID;

SELECT S.ID,S2.Value FROM dbo.Sample S
INNER JOIN dbo.Sample2 S2 ON
S.ID = S2.ID;

What will be difference between 1 and 2 ?


texts: DB Design - Assigning users to multiple sites with different roles

texts:
I am working on a DB design that lets me manage roles and permissions for each user per site. These are my business rules:

A user can manage multiple sites
A user can only have one role per site
A site can have multiple users

I already have two designs that I came up with, I just need validation which one would work better against my business rules.

First is using a ternary table site_user_role that consists of a relationship between site, user and role table. But this already breaks the rule of a user having only one role per site because those 3 primary keys would be unique and I can create a combination of two roles for a user within 1 site. Any complications with this design?

The second idea is having two binary tables. The site_user and site_user_role table. This arrangement I am quite sure I will not duplicate a role for a user on a site. But Is it weird to have a primary key aside from the Foreign keys on the site_user binary table? Any complications with this design?


texts: SQL Server Profiler - How to filter trace to only display inserts, updates and deletes to one database?

texts: How do I limit a SQL Server Profiler to trace only inserts, updates and deletes to a specific database?


texts: Getting DEADLOCKS frequently on Versioned TABLE

texts:
I am having trouble to understand the increase in DEADLOCKS for Update transactions on Versioned TABLES. The problem, was not present earlier and then we enabled the versioning and after a while we started to receive many DEADLOCK exceptions.

Here is the TABLE:

CREATE TABLE `sample_table` (
  `id` bigint(20) unsigned NOT NULL WITHOUT SYSTEM VERSIONING,
  `amount` bigint(20) NOT NULL DEFAULT 0,
  `release_date` date DEFAULT NULL,
  `details` VARCHAR(64) DEAFULT NULL,
  `changed_by_user_id` int(10) unsigned DEFAULT NULL,
  `last_changed_at` timestamp NOT NULL DEFAULT current_timestamp() WITHOUT SYSTEM VERSIONING,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME LIMIT 100000
(PARTITION `p0` HISTORY ENGINE = InnoDB,
 PARTITION `p1` HISTORY ENGINE = InnoDB,
 PARTITION `p2` HISTORY ENGINE = InnoDB,
 PARTITION `p3` HISTORY ENGINE = InnoDB,
 PARTITION `p4` HISTORY ENGINE = InnoDB,
 PARTITION `pcur` CURRENT ENGINE = InnoDB)

I read documents on MariaDB/SQL still cannot pin point on whats causing the deadlock. Here is the log

  Deadlock Info : update sample_table set changed_by_user_id=8127,details='sample' where id=815977
  *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  RECORD LOCKS space id 10235669 page no 94 n bits 192 index PRIMARY of table ebs.sample_table /* Partition p0 */ trx id 1903402610 lock_mode X locks rec but not gap waiting

I am suspecting that its due to HISTORY using INNODB and its causing deadlocks (self)?


texts: How to copy auto-increment id value from one table to another?

texts:
I have a database with two tables, A and B.

Table A contains the title of a book and has an auto-increment column, book_id, that I declared as primary key.

In table B, I want to retain the price of the book having the same book_id value. In this case, the book_id column will be constrained as a foreign key and I dont want to auto-increment it.

How do I do it using PHP?


texts: SQL Server - Temporary vs. Physical Tables

texts:
A movement is afoot in my place of employ to move away from using #temp tables and instead to use permanent physical tables with SPIDs.  Whenever one would have previously INSERTed INTO a #temp table, now an INSERT INTO dbo.MyPermanentTable (SPID, ...) VALUES (@@SPID, ...) is required - together with a bunch of DELETE FROM dbo.MyPermanentTable WHERE SPID = @@SPID statements at the beginning of e.g. a stored procedure.  Additionally, it goes without saying that anywhere that these 'permanent tables for storing temporary data' are used, one has to be careful to include a WHERE SPID = @@SPID.

The logic behind the move towards this practice is that it'll improve overall performance of the server on which the queries are running (by reducing I/O and contention in tempdb).  I'm not keen on this approach for a number of reasons - it's ugly, potentially dangerous and seems like it may well harm the performance of those queries that use the new scheme.

Does anyone have any experience with this or similar approaches to eliminating #temp tables?


texts:
I have a table GEMS_PROJECTS:

du_proj_id
du_id
Proj_id
proj_desc
isactive
transuser
trandate

A second table GEMS_DEPARTMENT:

du_proj_id
dept_id
de_dec
isactive
transuser
trandate

du_proj_id from second table refers to du_id from first table and I need update second table isactive field.

Note duid getting from user to pass first table and second table.


texts: Stored Procedure with Table Value Parameter

texts:
I'm attempting to write a stored procedure that will have two parameters.

ComputerName | nvarchar
Monitor_Serial | Table-valued parameter (TVP)

I'm using a TVP because some computers have an unknown number of monitors.

I would like my stored procedure to take each serial number in the Monitor_Serial and insert a new line in my table with the associated ComputerName (exactly how a foreach loop would work in programing).

How would I do this? When I was doing some research I saw the use of a cursor mentioned a few times but I'm not sure if this is what I should be using. Any suggestions would be greatly appreciated.


texts:
If you execute something like the following query on Postgres:

SELECT * FROM pg_timezone_names() WHERE name LIKE '%Rome%';

you get back a bunch of "weird" timezones:

postgres=# SELECT * FROM pg_timezone_names() WHERE name LIKE '%Rome%';
       name        | abbrev | utc_offset | is_dst
-------------------+--------+------------+--------
 Europe/Rome       | CET    | 01:00:00   | f
 posix/Europe/Rome | CET    | 01:00:00   | f
(2 rows)

I'm wondering why posix/ exists, what they stand for (they are not IANA official timezones right?) and when they are used. They look like a blend of POSIX standard and Olson DB, but posix should have -1 as utc_offset, right? I'm considering using the pg_timezone_names() function for giving the users the chance of choosing their own timezone, but I can't understand the meaning of this specific type of timezone in Postgres.


texts: How get the sum of two tables value using inner join

texts:
There are there tables the below image.

My question is retrieve from each customers and get the sum of Invoice total due, Invoice Total balance in invoice table and return cheque total due and return cheque total balance in return cheque table...

I wanted the answer like below image from the above 3 table using inner join

Please help me thank you.


texts: Import Data From csv Into Postgresql WITHOUT COPY

texts:
I am writing a function for a python environment which needs to import data from a csv file into an existing table. The scope in which the function can execute does not allow for a db SUPERUSER to run the function and so the COPY function is out of the question.

What other options are efficient?

The files range from 100,000 to 1,000,000 rows.

This question has also been posted here


texts: What is wrong with my RESTORE procedure?

texts:
I'm trying to use Toad Freeware (my SSMS is totally broken) for the following restore command:

RESTORE DATABASE AdventureWorks2016
FROM DISK = 'C:\Database\MSSQL\Samples\AdventureWorks2016CTP3.bak'

The response is:

Lookup Error - SQL Server Database Error: Directory lookup for the file "D:\Program Files\Microsoft SQL Server\MSSQL13.DSMAIN\MSSQL\Data\AdventureWorks2016CTP3_Data.mdf" failed with the operating system error 21(The device is not ready.).

I don't even have a D drive. I suspect the original database files were stored on a D drive, but what do I do now?


texts:
I have a server in the USA that should connect to one of our servers in Europe through service broker.

The server in Europe is an Availability group, whose listener is called sqlst.

when I run the following command in the USA server:

 declare @sql varchar(1008)
 set @sql = 'powershell.exe -command Test-NetConnection sqlst -port 4022'

 EXEC xp_cmdshell @sql

I get a wonderful message saying that all the connectivity is fine as you can see on the picture below:
The Ip addresses have been masked to protect the innocent.

However,

when I go to my USA Server in the correct database, and I run the following query after I have been told that the broker has not worked:

-- Error messages in the queue
SELECT *
FROM sys.transmission_queue

where service_contract_name = 'ORCACOLAContract'
order by enqueue_time desc

I get the following reason as why it has failed:

DNS lookup failed with error: '11001(No such host is known.)'.

The service account in the Europe Server has been added to Kerberos and all linked servers work fine.

Looking at the sql error log I don't see any related error messages.

I am not sure this is related to service broker and alwayson

I have done all these - linked server working fine.

I have no access outside of sql server in the USA machine.
Inside SQL Server I am sysadmin.

As I said above
when I run the following I get a good response:

declare @sql varchar(1008)
 set @sql = 'powershell.exe -command Test-NetConnection sqlst -port 4022'
 EXEC xp_cmdshell @sql

When I replace the server name by its IP Address and run it:

 declare @sql varchar(1008)
 set @sql = 'powershell.exe -command Test-NetConnection 200.1.0.xxx -port 4022'
 EXEC xp_cmdshell @sql

I also get a positive return.

However, when I run nslookup using the IP address of sqlst - 200.1.0.xxx
I get the below error:

declare @sql varchar(1008)
 set @sql = 'powershell.exe -command $ns = (nslookup.exe 200.1.0.xxx )[-4..-3]
$ns'
 EXEC xp_cmdshell @sql

Basically that says, domain controller cannot find the sqlst ip address

I have no way to run ipconfig/flushdns but I have run the following command instead:

 declare @sql varchar(1008)
 set @sql = 'powershell.exe -command Clear-DnsClientCache'
 EXEC xp_cmdshell @sql


texts: SQL Server 2008 R2 Management Studio : "Evaluation period has expired"

texts:
I've spent several hours trying to fix this issue so far with no success. I had installed SQL Server 2008 Evaluation Edition. Out of fear it might expire & create problems for me I installed SQL Server 2008 R2 Express Edition & migrated all my databases to Express. It worked fine for several months but now I have problems when I try to launch Management Studio:

I have tried the fixes in http://support.microsoft.com/kb/971268 but they didn't help.

I am not able to understand even after uninstalling the SQL Server 2008 Evaluation Edition why I am getting error with the Express Edition.

I even installed new instance of SQL Server 2008 R2 Express Edition, uninstalled SSMS 2008 Express & re-installed it still nothing seems to work for me.

Upgrade screen of installation:

Present status of my system

I have two instances of sql server 2008 r2 express edition
SSMS 2008 express edition

I am not sure what else to do.


texts: SQL Agent Powershell step not using specified proxy

texts:
I'm trying to execute a powershell script, and use a proxy to do. The job runs correctly, but using whoami within the script to log the executing user indicates that the script is not being run by the user I (think I) specified in the proxy.

I have a credential, with the correct username and password for the windows server:

I have a proxy set up to use that credential:

The user [Domain]\MSSQLService02:

Is specified as a principle for this proxy
Has its own login
Is the job owner

I'm using the proxy to Run As in the job step:

However, as mentioned, logging within the script indicates that [Domain]\MSSQLService02 is the user executing the script.

I don't know if I'm misunderstanding the use of proxies, I'm missing a step, or something is misconfigured.


texts: exceeding 'max_updates' resource when importing large database

texts:
I need to import a large database on my shared server.I'm using the following statement in a shell to import my database

mysql -u USERNAME -p PASSWORD --default-character-set=utf8 DATABASE_NAME < NAME_OF-FILE

Unfortunately, I receive the following error

User 'has exceeded the 'max_updates' resource (current value: 36000).

From my understanding max_updates is the number of updates that an account can issue per hour

My host doesn't want to temporarily increase this value.

Can someone recommend a better strategy of perhaps importing the database in smaller chunks to avoid triggering this limit.Perhaps there is a switch or option in mysql that would allow me to do this.


texts: IIS takes longer to run queries than SMSS

texts:
I'm completely stumped on this one.
We're running a pretty usual intranet app here, classic ASP frontend and SQL Server 2005 backend.
Occasionally, once/twice a week, a certain stored proc takes ~50x longer to run when I run it in the Management Studio. This slowness lasts the entire day and it inexcplicably goes away the next day.

So essentially what's happening is this: once/twice a week, when I fire up the intranet page that runs the stored procedure and displays the results, it takes roughly 115secs to run.
I then fire up SQL Server Management Studio and run the same query, which this time around takes only 3-4secs to run.

The next day, the intranet page is taking as long as SSMS to run the very same stored procedure, with nothing having been changed in the interim (not to my knowledge anyway).

Other details:

the IIS connects to SQL Server 2005 using Provider=sqloledb as the driver
the slowdown lasts the entire day, regardless of the number of active users
I can replicate the slowness by running the same query from Excel, using the same driver/connection string
I've ran a trace on the SQL server and it turns out that on those particularly slow days, the duration of the stored proc is actually longer when executed via IIS than direct query

As such, I was wondering whether any of you has encountered any similar behaviour before, if you've fond a solution to it and what would your advice be in tackling/solving it?

Thanks for your help,

Please find Trace results in .csv format below:

EventClass,TextData,ApplicationName,CPU,Reads,Writes,Duration (s),ClientProcessID,SPID,StartTime,EndTime
12,exec spStubbornProc,Internet Information Services,"106,016","7,059,999",1,115.80,5476,85,18/04/2013 08:17:15,18/04/2013 08:19:11
12,exec dbo.spStubbornProc,Microsoft SQL Server Management Studio - Query,"3,141","146,051",0,3.40,5876,84,18/04/2013 08:20:45,18/04/2013 08:20:48
12,exec dbo.spStubbornProc,Microsoft SQL Server Management Studio - Query,"2,563","147,387",0,2.93,5876,84,18/04/2013 08:21:04,18/04/2013 08:21:07
12,exec spStubbornProc,Internet Information Services,"103,156","7,083,365",7,118.73,5476,80,18/04/2013 09:39:41,18/04/2013 09:41:40
12,exec dbo.spStubbornProc,Microsoft SQL Server Management Studio - Query,"2,406","175,029",1,2.57,5876,84,18/04/2013 10:08:58,18/04/2013 10:09:01
12,exec spStubbornProc,Internet Information Services,"112,218","7,103,267",7,114.72,5476,75,18/04/2013 14:06:26,18/04/2013 14:08:21
12,exec spStubbornProc,Internet Information Services,"93,515","7,101,229",3,93.60,6092,60,18/04/2013 19:37:02,18/04/2013 19:38:35
12,exec spStubbornProc,Internet Information Services,"2,500","148,775",1,3.42,5160,75,19/04/2013 09:16:01,19/04/2013 09:16:04


texts: How to do safety mirroring innodb table on mysql?

texts:
I have one Mysql InnoDb table with rush trafik on row update. The table is also frequently used for heavy select query (especially group by for summary report).  As I know this heavy select query will interfere or reduce the update peformance. So i have an idea to mirror this table, so the heavy select query will do in this mirror table (delay 15 menit still accepted).
Fyi, this table have size approximately 10 GB, I have used mysql event (scheduled every 15 minute) to copy this table to another with "insert into select" query. Actually it need more than 60 second for copy process, but the update query still impacted when this event running.
So Is there any best/common practise for mirroring table mysql with minimum impact to currently query (especially update) process on the master table?
Note : I want to do on the same server


texts: Why does MySQL wait for a table lock for a validation query?

texts:
Why are simple validation queries waiting for a table lock?
In our Hibernate setup we are using a validation query to check that a connection is still usable within the pool. We use a simple SELECT validation-query; statement for this purpose.
Recently during a busy day we noticed that our Aurora MySQL 5.6 cluster shows quite a few of these validation queries in the process list, which were waiting for a table lock. A similar thing also happened for isolation level settings queries, as shown below.
I did not expect that queries like those require a table at all, so why lock it? I was surprised that we have seen locks for this kind of queries very frequently. While there were longer running SELECT statements on other tables, I was wondering what the mechanism in the background is and if there is anything we could do to avoid waits. There was a very high load and I assume that the validation queries are sent before each query, effectively doubling the number of queries. Should we remove the validation query?
We only use InnoDB tables, no MyISAM or other engines are used.
Locking queries
Below is an example from the InnoDB monitor output. I did not expect the validation query and the isolation level query to require table locks.
`---TRANSACTION 3273445961, not started
MySQL thread id 338466, OS thread handle 0x2ab1bbf86700, query id 610723618 10.12.2.123 db-user Waiting for table level lock
SELECT 'validation-query'`

I was also wondering how this query could possibly wait for a table level lock:
---TRANSACTION 3273446385, not started
MySQL thread id 338823, OS thread handle 0x2ab1e2617700, query id 610723620 10.12.2.123 db-user Waiting for table level lock
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

Profiling the validation query
I was profiling the validation query and it does spend time opening and closing tables. I suppose this is normal, also for string constants? But why are they waiting for a table lock when there is only a simple SELECT and no UPDATE, INSERT or DELETE involved?
root@local> SHOW PROFILES;
+----------+------------+--------------------------------------+
| Query_ID | Duration   | Query                                |
+----------+------------+--------------------------------------+
|        1 | 0.00036050 | SELECT 'validation-query' |
+----------+------------+--------------------------------------+
1 row in set, 1 warning (0.000 sec)

root@local> SHOW PROFILE FOR QUERY 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000081 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000012 |
| init                 | 0.000019 |
| optimizing           | 0.000012 |
| executing            | 0.000015 |
| end                  | 0.000008 |
| query end            | 0.000007 |
| closing tables       | 0.000007 |
| freeing items        | 0.000111 |
| cleaning up          | 0.000080 |
+----------------------+----------+
11 rows in set, 1 warning (0.001 sec)


texts:
I have a database called layers. in it there are two schemas 1. how 2. base_layers

each schema has its own login name and password and both are not superusers. I want the ability to query across both schemas from within the schema logins(not using postgres superuser)

above I am running this query from the base_layers schema login and it gives me this permission denied error. if i run this query through the postgres connection it works... what is the proper way to be granting usage from schema to schema without making each login name a superuser


texts: Does Hasura (GraphQL) support multiple databases?

texts:
Hi I am setting up Hasura, the normal setup looks like this:

docker run -d --net=host \
-e HASURA_GRAPHQL_DATABASE_URL=postgres://username:password@hostname:port/dbname \
-e HASURA_GRAPHQL_ENABLE_CONSOLE=true \
hasura/graphql-engine:latest

I couldn't find in the docs a way to use multiple databases within the same instance, is it even possible?


texts: Error using triggers with SQL Server Profiler Tables

texts:
After noticing 100's of sort_warning errors in a recent analysis of the default trace output, I am trying get the text of the queries causing those warnings.

SQL Server Profiler has an event called Sort Warnings, but it doesn't have the TextData column.

Using Extended Events is not possible for 2008 R2, because the sort_warning event isn't available in Extended Events for 2008 R2.

I thought about placing a trigger on the table I'm using to store profiler's results to capture the output from DBCC INPUTBUFFER.

-- table to store the results of dbcc inputbuffer
CREATE TABLE Trace_Results(EventType NVARCHAR(30), Parameters INT, EventInfo NVARCHAR(255))

CREATE TRIGGER [dbo].[Trace_Trigger]
  ON [dbo].Trace_Table
  AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL VARCHAR(MAX)

    SELECT @SQL = 'DBCC INPUTBUFFER(' + CONVERT(VARCHAR(10), ins.SPID) + ')'
    FROM   inserted ins

    --PRINT @SQL

  INSERT INTO Trace_Results (EventType, Parameters, EventInfo)
    EXEC(@SQL)

END

I can start the trace from profiler without any errors, but when I run a query that causes a sort warning, I get the following error:

Which causes the trace to stop.

I also just tried inserting directly into the Trace_Table table with the following command

INSERT INTO trace_table (eventclass) values(1)

, but nothing shows up in the Trace_Results table.

If I place the same trigger on any other user table in my database, the results from DBCC INPUTBUFFER are stored as expected in Trace_Results.

What would prevent a trigger on a table created in SQL Server Profiler from inserting data into another table?

UPDATE 24/01

Trying @Kin's solution, but still getting NULL in the SQLText columns

Query used to cause sort warnings

-- Declare table variable
DECLARE @T1 TABLE (Col1 int, Col2 varchar(8000))
-- Insert data to table variable
INSERT INTO @T1
SELECT * FROM dbo.TestTable

-- Return data sorted on Col2 column
SELECT * FROM @T1
ORDER BY Col2 ASC

Screen shot of sort_warnings_log table


texts: Question on conditional join or other way to create view based on two tables in Postgresql

texts:
I am trying to create a view that combines these two materialized views with following rules.

if all_products.id and there is matching id (market_id not relevant) in visible_products then only return visible_products rows
if all_products.id and there is no matching id in visible_products (market_id is not relevant in match) then return rows from all_products

I am not able to figure it out with joins or union. Can this be solved with a case statement? If both tables have rows then inner join will give me the correct result and if visible_products have no matching rows a left join will work but I need a join or other solution that will work with both cases.

Both all_products and visible_products are materialzied views that I can change / add columns to if needed.

Example a

all_products
id, market_id
1, 100
1, 101
1, 102

visible_products
id, market_id
1, 100

result product_view
1,100

Example b

all_products
id, market_id
2, 103
2, 104
2, 105

visible_products
id, market_id

result product_view
2, 103
2, 104
2, 105

Any help is greatly appreciated

Jonas


texts: Show cassandra help documentation in terminal instead of browser

texts: I've just started playing around with Cassandra through the cqlsh cli. When using the help command for the topics the documentation opens in the browser. Is there a way to show the documentation in the terminal and not open the browser for this? I can't find any descriptions on how to do it and my google searches doesn't turn up anything. I'm using Apache cassandra cqlsh 5.0.1.


texts: I want to log only ddl commands to my postgresql .wal files. So, i configured my postgresql.conf file to log those sql queries but it did not work. It cathes every query that works on my postgresql server. How can i do it? I just want to log only ddl commands.


texts:
I have a project I am working on and I need to extract the most recent date from each each equipment number.  I can't get MAX() to function with this.  Please see the following code. The idea is that it goes through the table and pulls each row and then compares the event_date for the most recent date for each eq_num.

SELECT e.event_id,
    l.location,
    e.event_date,
    c.name,
    q.eq_type,
    e.pickup_date,
    e.eq_to_c AS eq_num,
    e.eq_from_c,
    CASE
        WHEN l.location = '' OR l.location IS NULL AND e.eq_to_c != e.eq_from_c THEN 'Out'
        WHEN e.eq_from_c = '' THEN l.location
        WHEN e.eq_from_c IS NULL OR e.eq_from_c = '' THEN l.location
        WHEN e.eq_to_c = e.eq_from_c THEN 'Returned'
        WHEN e.eq_to_c != e.eq_from_c THEN l.location
        WHEN l.location = '' OR l.location IS NULL AND e.eq_to_c = e.eq_from_c THEN 'Returned'
    END AS eq_status,
    CASE
        WHEN CAST(e.event_date AS DATE) < CAST(e.pickup_date AS DATE) THEN e.pickup_date
        WHEN CAST(e.pickup_date AS DATE) < CAST(e.event_date AS DATE) THEN e.event_date
        WHEN CAST(e.event_date AS DATE) = CAST(e.pickup_date AS DATE) THEN e.event_date
    END AS event_date
    FROM events e
        LEFT JOIN location l ON (e.location_id = l.id)
        LEFT JOIN clients c ON (e.event_client_id = c.id)
        LEFT JOIN equip_type q ON (e.req_eq_type_id = q.eq_type_id)
        ORDER BY eq_num, event_date DESC


texts: How to know spid which is filling tempdb from temp table name in sybase

texts:
I received alert that tempdb is going to be full and temp table name aslo provided.
Now I want to know how to check which spid is filling tempdb from temp tablename.


texts: BIT columns all "1" after a phpMyAdmin export/import

texts:
I have to import data from a MySQL database using phpMyAdmin because that's the only interface my hosting provider supports.

I have exported my database from my local machine using phpMyAdmin. After that I imported the script file to my host. All of data in the columns that is BIT type are changed to '1'. Hosting database version is 5.5.29.

Is it a phpMyAdmin problem, or MySQL version problem? How can I fix this?


texts:
I have created a partitioned table with 1 partition. below is the script for the same:

CREATE TABLE public.ms (
id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
a int8 NOT NULL,
b int4 NOT NULL,
c varchar(2000) NULL,
d int2 NOT NULL,
e bool NOT NULL DEFAULT false,
f timestamp NOT NULL DEFAULT timezone('UTC'::text, CURRENT_TIMESTAMP),
g jsonb NULL,
h int2 NOT NULL DEFAULT 1,
i bool NOT NULL DEFAULT true,
j int2 NULL,
CONSTRAINT me_pkey PRIMARY KEY (id)
)PARTITION BY RANGE (id) ;

CREATE TABLE ms3_0  PARTITION OF public.msg
FOR VALUES FROM (1) to (1000);

Now, when I'm running insert statement for this via below loop, I'm getting below error message:

do $$
begin
for i in 1..1000
loop
INSERT INTO public.ms
(a, b, c, d, e, f,  h, i , j)
VALUES(2, 0, '', 0, false, '2019-01-28 23:59:51', 1, true, 0);
end loop;
end;$$

SQL Error [23514]: ERROR: no partition of relation "ms" found for row
  Detail: Partition key of the failing row contains (id) = (1001).

However, till id '1000', partition has been created already.  After that, I dropped the existing table and recreated the same table and partition again. Now, I started inserting records manually via a single 'insert' statement. Then, I could insert into them.

After, this, I have created 2nd partition like below:

CREATE TABLE ms3_1  PARTITION OF public.ms
FOR VALUES FROM (1001) to (2000);

And, again executed the same loop for 1000 entries and got below error:

SQL Error [23514]: ERROR: no partition of relation "ms" found for row
  Detail: Partition key of the failing row contains (id) = (1000)

However, when I insert in the batch of 100 , like below: I could insert  that:

do $$
begin
for i in 0..100
loop
INSERT INTO public.ms
(a, b, c, d, e, f,  h, i, j)
VALUES(2, 0, '', 0, false, '2019-01-28 23:59:51', 1, true, 0);
end loop;
end;$$

From the above whole scenario , I have a few queries:

In the first case, why it wasn't inserting data (1000 values) into first partition when partition for first 1000 values was  created already?
After inserting 3-4 records into first partition and then After creating again another partition (ms3_1) with 1000 records, when I try to perform bulk insert of 1000 records into the same ms table, why I'm not able to make that?

Any help/reason for this will be appreciated here. Since, it will help me to configure partition as per my business requirement.


texts:
I executed this to check to see if I have write permissions on a folder and it fails (returns -1). I am a system admin and SQL admin.  I have full access to this directory but the R Script says I do not.  If I change the mode of the script to check for read or execute permissions, it succeeds (returns 0).  What else can I check to fix this?

EXECUTE sp_execute_external_script
       @language = N'R'
      ,@script = N'OutputDataSet <- data.frame(file.access("C:/Program Files/Microsoft SQL Server/MSSQL13.MSSQLSERVER/R_SERVICES/library/", 2));'
WITH RESULT SETS (([DefaultLibraryName] VARCHAR(MAX) NOT NULL));
GO


texts: How do I keep a 'running total' of purchases?

texts:
Say I have an append-only table that records purchases at a store:
create table purchases(
    customer_id int,
    amount numeric(10, 2) -- (in dollars)
);

There's a lot of other queries that want to know the total dollars spent by a customer, so I have a view like this:
create view total_amount_purchased_view
as
    select customer_id,
           sum(amount) as total_amount_purchased
      from purchases
  group by customer_id;

The problem is, the purchases table can be very large (much larger than the number of customers), so queries using the view are slow. I can use a materialized view instead, but I would have to refresh the entire view every single purchase (because postgresql does not support single-row refresh of materialized views, you have to recalculate the entire thing. Not sure about other db's).
So, naturally, this leads me to thinking that I should have a table called total_amount_purchased containing the same rows as the view above. I would keep it up-to-date with an insert trigger on the purchases table, which would update a single row.
-- initialize
create table total_amount_purchased_table
as
    select customer_id, sum(amount) as total_amount_purchased
      from purchases
  group by customer_id;

-- keep it up to date
create or replace function fn_update_total_purchase() returns trigger as $$
begin
    update total_amount_purchased_table
       set total_amount_purchased = total_amount_purchased_table + new.amount
     where customer_id = new.customer_id;
    -- (OR, insert a new row for a new customer, code not shown)
    return new;
end; $$ language plpgsql;

create trigger tr_update_total_purchase
after insert on purchases for each row
    execute procedure fn_update_total_purchase();

Question 1: Is there a better solution to this?
Question 2: If this is an ok solution, is there any way to enforce that the total_amount_purchased is correct? (The definition of 'Correct' being that it is equal to sum(amount) of purchases? Can I restricts that the only the trigger tr_update_total_purchase can make changes to total_amount_purchased_table?


texts: In SSMS we are giving hostname\instance name to connect to SQL Instance, can someone please explain me how it is getting connected, is it using any files or protocols or what exactly is happening in back ground so that it gets connected?


texts: Is filtering on int better than filtering on date

texts:
I have a system where most of our table has the current datetime as primary key and we filter most of the data based on the datetime.

The question, if I move this datetime to another new table and have an auto id in the new table. for eg.

Dates
id datetime
1  11/6/2019
2  11/7/2019
3  11/4/2019
4  11/8/2019

Now all the other tables will have id instead of datetime and filtering will be based on the id.

Will this improve performance or filter on a datetime is equal to filter on int?

I am using SQL Server 2017.


texts:
Okay so I have a report that does a this week vs last week comparison and our customer noticed that their data was "funky". Upon further investigation we found it was not doing weeks correctly according to the ISO standards. I ran this script as a test case.

SET DATEFIRST 1
SELECT DATEPART(WEEK, '3/26/13')
    , DATEPART(WEEK, '3/27/12')
    , DATEPART(WEEK, '3/20/12')
    , DATEPART(WEEK, '1/2/12')
SELECT DATEPART(ISO_WEEK, '3/26/13')
    , DATEPART(ISO_WEEK, '3/27/12')
    , DATEPART(ISO_WEEK, '3/20/12')
    , DATEPART(ISO_WEEK, '1/2/12')

When run I got these results.

I thought this was peculiar and so I did some more digging and found that SQL Server counts January 1st as the first week of the year where ISO counts the first Sunday in January as the first week of the year.

The question then ends up being two fold. Question 1 why is this? Question 2 is there any way to change this so I'm not having to modify all of my code to use ISO_Week everywhere?


texts: WAMP server performance tuning for MySQL

texts:
We have a Drupal site with nearly 2000 pages running on WAMP server. The pages also contain images, and flash videos embedded into them. So, they are taking some time to load under normal settings.

Nearly 200-300 users are expected to use the system concurrently (the number is expected to grow later).

The dev server (on our company intranet) has 16 GB RAM, and runs Windows7 - 64 bit OS. I can see that in wamp/bin/mysql/mysql5.5.16/ has files

my.ini
my-huge.ini
my-innodb-heavy-4G.ini

I had a look into both of them, and I am not sure what is the purpose of each one of them and which one is used by my the WAMP server currently. Also, I needed to know the difference in using my-huge.ini and my-innodb-heavy-4G.ini. I have also configured Apache SOLR for indexing the search (I don't know if this is relevant).

P.S : Drupal stores pages (along with the link to images and videos) into the database and images and other media are stored in folder under the site root.


texts: Performance issues on large CSV import with before INSERT trigger

texts:
I am doing import csv file using load into file. The files are big and contains minimum 500K+ lines of records and table extend day by day. Each line is just a phone number and nothing else. My table size has now grown for records more than 200+ Mil. I am also using a trigger to update a few items before INSERT. A bulk import takes a very long time naturally because of the trigger.

To import records query

load data local infile '/tmp/file.csv' IGNORE into table contacts FIELDS TERMINATED BY ',' (contact) set phonebook_id = 1

Trigger

CREATE TRIGGER `contacts_before_insert`
BEFORE INSERT ON `contacts`
FOR EACH ROW
BEGIN

   DECLARE vTimezone varchar(4);

   SELECT IF((timezone = '' OR count(timezone) = 0), 'ERR', timezone)
     INTO vTimeZone
     FROM timezones
    WHERE area_code = (SELECT LEFT(NEW.contact , 3));

   SET NEW.timezone = vTimezone;

timezone table structure

  CREATE TABLE `timezones` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `state` varchar(45) NOT NULL,
    `area_code` varchar(3) NOT NULL,
    `timezone` varchar(4) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=327 DEFAULT CHARSET=latin1

Contacts table structure

 CREATE TABLE `contacts` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `phonebook_id` int(11) NOT NULL,
   `campaign_id` int(11) DEFAULT NULL,
   `contact` varchar(45) NOT NULL,
   `status` tinyint(1) unsigned NOT NULL DEFAULT '1',
   `admin` tinyint(1) unsigned NOT NULL DEFAULT '0',
   `call_status` tinyint(1) unsigned NOT NULL DEFAULT '1',
   `active_call` tinyint(1) unsigned NOT NULL DEFAULT '0',
   `test_call` tinyint(1) unsigned NOT NULL DEFAULT '0',
   `v_node_name` varchar(45) DEFAULT NULL,
   `timezone` varchar(4) DEFAULT NULL,
   `updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created_date` timestamp NULL DEFAULT NULL,
    `contact_name` varchar(45) DEFAULT NULL,
     PRIMARY KEY (`id`),
     UNIQUE KEY `phonebook_id` (`phonebook_id`,`contact`),
     KEY `idx_001` (`active_call`,`campaign_id`,`test_call`),
     KEY `idx_002` (`phonebook_id`,`call_status`,`test_call`,`campaign_id`,`active_call`,`dnc_contact`),
     KEY `idx_003` (`phonebook_id`,`test_call`,`call_status`,`dnc_contact`),
     KEY `FK_campaign_id_idx` (`campaign_id`),
     KEY `idx_testcall` (`test_call`),
     KEY `idx_004` (`campaign_id`,`call_status`,`test_call`),
     KEY `idx_active_call` (`active_call`)
 ) ENGINE=InnoDB AUTO_INCREMENT=136600117 DEFAULT CHARSET=latin1

My question is: if I break the CSV file into smaller pieces, say 2 Meg each or something like that, is that going to speed up the INSERT? Or, should I create some sort of stored procedure which runs after the import and updates the data in contacts table where my contacts are being imported. Not to mention, I absolutely want to avoid row/table locking because at the same time the table has heavy SELECT/UPDATES.

What would be the best practice here? How to speed up large CSV imports avoiding table locking etc .. ?


texts: SQL Server Management Studio DAC connection error

texts:
I'm trying to connect to SQL Server 2012 via DAC using Management Studio. I type admin: before my server name, yet I keep getting the same error:

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The remote computer refused the network connection.) (Microsoft SQL Server, Error: 1225)

What I've checked:

I can login with no problems when using regular connection(non DAC)
I can also login when forcing tcp: in connection string
user is a sysadmin (sa)
I am logging in from a local server (physical sql machine)
SQL Server browser is enabled
Remote connections are enabled and firewall was disabled
TcpDynamicPorts in regedit was set to 59036
I've executed this:

exec sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
exec sp_configure 'remote admin connections', 1
RECONFIGURE WITH OVERRIDE

So far, nothing has helped me.


texts: postgres query performance: view vs function

texts:
I have a scenario where I need to run a payroll report. The report calculates the payroll amount, grouped by staff member, for a specific date range.

For example, when running the report for 2016-11-01 to 2016-11-30, I would see the following result:

Staff Id    Total
------------------
1           123.00
2           439.22

I'm using the following query for the above report:

select
    user_id as staff_id,
    sum(amount) as total
from transaction
where
    business_id = <business_id> and
    type = 'staff' and
    kind = 'commission' and
    created_at between <start_date> and <end_date>
group by
    user_id;

I'm trying to determine the best way to optimize the performance of this query given the following requirements:

Results will vary based on the business_id, start_date and end_date
Data should always be fresh

It appears both views and functions would do the job, but I'm not 100% on which is the best approach given the requirements.

Sidenote: it would be great to cache the data based on the parameters mentioned above, but it seems like there isn't a great solution on the database side. Correct me if I'm wrong!

Additional information:

I'm running Postgres 9.6
I have indexes on the business_id, type, kind, user_id and created_at columns in the transaction table. These are all single column, btree indexes.


texts:
Recently i am facing some slow issue for my web application. And i have check my visitor report and it shows normal traffic rate as usual. And then i check my db login auditing and find below login failed logs.

Previously I have enabled remote access (Ms SQL Server > Connection > Allow remote access to this server) for personal purpose. But I have disabled remote access option at the moment but not sure whether it helps.

Does it means someone trying to hack my db? Anything else i can do in order to prevent this?

EDIT

I am getting this kind of errors in random time with each time consists of 100-500 failed attempts(last for 1-2 minutes). Random time for today as below:

01/19/2014 18:41:29
01/19/2014 16:59:44
01/19/2014 15:50:56
01/19/2014 14:59:43
01/19/2014 07:02:33
01/19/2014 05:34:45
01/19/2014 01:25:49

Eg data:

01/19/2014 18:41:30,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 7.
01/19/2014 18:41:29,Logon,Unknown,Login failed for user 'hbv7'. Reason: Could not find a login matching the name provided. [CLIENT: 61.138.99.12]
01/19/2014 18:41:29,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 5.
01/19/2014 18:41:29,Logon,Unknown,Login failed for user 'sa'. Reason: An error occurred while evaluating the password. [CLIENT: 61.138.99.12]
01/19/2014 18:41:29,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 7.
01/19/2014 18:41:28,Logon,Unknown,Login failed for user 'hbv7'. Reason: Could not find a login matching the name provided. [CLIENT: 61.138.99.12]

Most of the errors are login failed for user 'sa'