texts: OpenRowSet sp_update mystery - Update returns row updated, but does not update row

texts:
/*
SELECT @@VERSION
Microsoft SQL Server 2016 (SP1-CU1) (KB3208177) - 13.0.4411.0 (X64)   Jan  6 2017 14:24:37   Copyright (c)
Microsoft Corporation  Developer Edition (64-bit) on Windows 8.1 Enterprise 6.3 <X64> (Build 9600: )
*/
USE tempdb
-- PHASE 1 - BUILD THE Test Table, insert test values
IF OBJECT_ID('dbo.tbl_Test') IS NOT NULL
    DROP TABLE dbo.tbl_Test
GO
CREATE TABLE dbo.tbl_Test (
    TestTableKey INT IDENTITY(1,1),
    TestTableName VARCHAR(100) UNIQUE,
    Status INT DEFAULT 1,
    DateStamp DATETIME DEFAULT GETDATE(),
    Signature VARCHAR(50) DEFAULT 'SYSADMIN' )

INSERT INTO dbo.tbl_Test
    (TestTableName)
VALUES ('FISHPASTE'),('REDRO'),('PEANUTBUTTER')
GO
IF OBJECT_ID('dbo.sp_Update') IS NOT NULL
    DROP PROCEDURE dbo.sp_Update
GO
CREATE PROCEDURE dbo.sp_Update (
    @TestTableKey INT NULL,
    @TestTableName VARCHAR (100) NULL,
    @Status INT NULL,
    @Signature VARCHAR (50) NULL)
AS
BEGIN
    UPDATE dbo.tbl_Test
    SET TestTableName = ISNULL(@TestTableName,TestTableName),
        Status = ISNULL(@Status,Status),
        Signature = ISNULL(@Signature,Signature),
        DateStamp = GETDATE()
    OUTPUT INSERTED.TestTableKey
    WHERE TestTableKey = @TestTableKey
        OR  TestTableName = @TestTableName
END
GO

SELECT * FROM tbl_Test
--This is the base state of the data.

EXEC dbo.sp_Update NULL,'REDRO',0,NULL
SELECT * FROM tbl_Test
--NOTE at this time Row 2 (as output) Status = 0 and DateStamp is updated.

EXEC ('dbo.sp_Update NULL,''REDRO'',10,NULL')
SELECT * FROM tbl_Test
--NOTE at this time Row 2 (as output) Status = 10 and DateStamp is updated.

DECLARE @STATEMENT VARCHAR(MAX) = '''SET FMTONLY OFF; EXEC dbo.sp_Update 1, ''''FISHPASTE'''', ''''2'''',''''TESTING'''';'''
DECLARE @SQLStatement VARCHAR(MAX) = REPLACE(REPLACE('
SELECT *
FROM OPENROWSET (
            ''SQLNCLI'',
            ''DRIVER=SQL Server;SERVER=#SERVERNAME#;Trusted_Connection=Yes;DATABASE=#DatabaseName#'',
            ' + CONVERT(VARCHAR(MAX) , @Statement) + '
            )','#SERVERNAME#', CONVERT(VARCHAR(200),SERVERPROPERTY('SERVERNAME'))),'#DatabaseName#',CONVERT(VARCHAR(200),DB_NAME()))
PRINT @SQLStatement
EXEC (@SQLStatement)
SELECT * FROM tbl_Test
/*
Note at this time Row 1 (as output) is UNCHANGED!
How can OUTPUT.INSERTED result the correct row number from NON-UPDATED ROW?
Look at the messages tab in SQL Management Studio for the exact query exeucted, copy it and run it.
It will update the row; but not in openrowset!
*/

/*Clean up*/
DROP PROCEDURE sp_Update
DROP TABLE tbl_Test

Hoping someone can explain why my openrowset execution of my procedure returns the key for the row updated, but the procedure does not update the row returned.

The scenario is used to update a remote database in a t-sql context... with native tools, in a controlled manner. Currently targeting same server for demonstration of failing.


texts: SQL Server 2014 .bak file error - The media family on device 'D:\..' is incorrectly formed

texts:
Worst case scenario - the server crashed and the 2014 SQL Server .bak file had to be recovered from the failed disk. The .bak file I have is over 20gb in size but is unusable when trying to restore from it. I built a new server and it is running SQL Server 2014 developer, just like the old server (the new server backs up all the database to the cloud, local drives, virtual drives, and network drives. I also have a script on a cron job to verify all the backup media files daily now as well).

I was able to restore some of the data but now my customer is asking us for a certain date range that I can't provide.

I have been reading a lot about this and everything indicates that it is a SQL Server version related issue however I think it has something to do with how the .bak file was recovered (maybe ASCII vs Binary file format?).

I know the data is there or the file wouldn't be over 20gb, I just need to get SQL to recognize it as valid backup media.

Any suggestions is greatly appreciated!

RESTORE HEADERONLY
FROM DISK = 'D:\!Recovered Drives From Old Server\ONTMaster Recovery File\ONTMaster.bak'
GO

RESTORE VERIFYONLY
FROM DISK = 'D:\!Recovered Drives From Old Server\ONTMaster Recovery File\ONTMaster.bak'
GO

Here is the error I receive:

  Msg 3241, Level 16, State 0, Line 1
  The media family on device 'D:!Recovered Drives From Old Server\ONTMaster Recovery File\ONTMaster.bak' is incorrectly formed. SQL Server cannot process this media family.
  Msg 3013, Level 16, State 1, Line 1
  RESTORE HEADERONLY is terminating abnormally.
  Msg 3241, Level 16, State 0, Line 5
  The media family on device 'D:!Recovered Drives From Old Server\ONTMaster Recovery File\ONTMaster.bak' is incorrectly formed. SQL Server cannot process this media family.
  Msg 3013, Level 16, State 1, Line 5
  VERIFY DATABASE is terminating abnormally.


texts: Why are CASE-expressions in the list documenting operator precedence?

texts:
In the list here:
https://mariadb.com/kb/en/operator-precedence/
case-expressions are put at the same precedence level as BETWEEN, between the NOT operator and the comparison operators.
However, case-expressions always begin with CASE and end with END, and all subexpressions are also delimited by the CASE keywords. They're like parenthetical expressions, so I don't understand why case-expressions are on this list.
Is there an SQL expression that would be parsed differently if the case-expression precedence was set higher or lower?
To give an example, with 2 + 3 * 4, we get different results when parentheses are used in these 2 ways: 2 + (3 * 4) and (2 + 3) * 4. This question is about how it's impossible to do the same with CASE. One can't substitute the use of + for CASE and show 2 different uses of parentheses such that the result differs between them.
To compare with other RDBMSes, neither SQLite nor PostgreSQL include CASE in their operator precedence lists.


texts: Is it best practice to revoke server permissions to the server "public" role on SQL Server instances?

texts:
We have evaluated the Microsoft "best practice" policies (located at C:\Program Files (x86)\Microsoft SQL Server\number\Tools\Policies\DatabaseEngine\1033) on each of our SQL Server instances (example here). The following issue has been flagged up to us:

This provides a link to a Microsoft page which states the following as being best practice:

  Do not grant server permissions to the server public role.

On reviewing the security, we can see that the public role has server permissions to view any database by default. We have run the following and re-evaluated the policy, but the issue is still flagged up.

REVOKE VIEW ANY DATABASE TO [public] AS [sa];

The only other permissions attributed to this server role are endpoint permissions to be able to connect via TCP/VIA/Named Pipes/Local Machine. If we remove these, surely the role will be superfluous.

QUESTIONS

Should we be concerned about this? Is this something we really should look to address?
What else do we need to do to properly resolve this issue?
Are there any implications for doing this or not doing this?


texts: What Query Browser tools are out there available for Sybase Database?

texts: What are the free and commercial tools available for Sybase Database?


texts: Best way to put date range in a table in mySQL

texts:
I'm designing a system which is basically a booking engine. it uses separate table to store room data and separate table to store it's availability information (available date(s) ). I'm stuck in designing at the availability table.

This is the structure in my rooms table,

room_id
room_type
room_name etc...

This is my current availability table structure

room_id (int)
week_id (int)
date (datetime)
availability (boolean)

is there any better way to achieve this?please help. even the tiniest advice will be greatly appreciated


texts: MySQL Adding New Slave to Master Master Replication Scheme

texts:
I've got two mysql servers, A and B which are currently set up in a circular, MASTER MASTER replication scheme. I need to add third server to the mix, C as a SLAVE to B as pictured below:

A <---> B
        |
        v
        C

I just made an attempt at doing this, but I forgot to take into account the fact that writes that go from A to B will not make it to C without turning on log-slave-updates on B being turned on.

My question: if I turn log-slave-updates on for B, isn't A going to pick up on those log entries again execute them a second time, thereby spawning some sort of black hole that could threaten to swallow the planet?


texts: Resizing and reclaiming free space on Tablespaces

texts:
I would like to reclaim some free space from our tablespaces. I can see that UNDOTBS1 and USERS for example have 30GB and 10GB free.

these are the tablespaces:

| tablespace    | used mb   | free mb   | total mb  | pct. free     |
|------------   |---------  |---------  |---------- |-----------    |
| SYSAUX        | 3268      | 152       | 3420      | 4             |
| UNDOTBS1      | 991       | 31777     | 32768     | 97            |
| USERS         | 186866    | 10766     | 197632    | 5             |
| SYSTEM        | 3360      | 30        | 3390      | 1             |
| INDEXES       | 32920     | 2920      | 35840     | 8             |

I could see on ASKTOM, and other questions that I need to:

PURGE TABLESPACE MyTs
aLTER TABLESPACE MyTs COALESCE

and then:

ALTER DATABASE DATAFILE 'Datafile_here' RESIZE 3g

But I would like to know how much can I reclaim. when i try to redize UNDOTBS1 for example, it says I cannot trim space that is used. but it has more than 30GB free.

On SQL Server we can simply " shrink file" adn it shrinks the free space.


texts: Get count based on 15 minutes of interval

texts:
I have a table containing Call records:

tbl_calls

cl_Id
cl_StartDate
cl_endDate

I am passing two parameters @StartDate and @EndDate to my stored procedure.

My requirement is to get the count of Call Records between every 15 minutes of duration.

For example, if:

@StartDate = '2015-11-16 00:00:00.000',
@EndDate = '2015-11-16 23:59:00.000'

Output should be:

Date                        Count
2015-11-16 00:00:00.000      10(Count of startDate between '2015-11-16 00:00:00.000' AND '2015-11-16 00:15:00.000')
2015-11-16 00:15:00.000       7(Count of startDate between '2015-11-16 00:15:00.000' AND '2015-11-16 00:30:00.000')
2015-11-16 00:30:00.000      50(Count of startDate between '2015-11-16 00:30:00.000' AND '2015-11-16 00:45:00.000')

upto @EndDate

I've tried the following query, however it doesn't work properly.  Is there a better way to do this?

DECLARE @StartDate DATETIME = DATEADD(DAY,-1,GETUTCDATE()),
        @EndDate DATETIME = GETUTCDATE()

SELECT New
FROM
    (SELECT
         (CASE
             WHEN cl_StartTime BETWEEN @StartDate AND
                         DATEADD(MINUTE, 15, @StartDate)
                THEN 1
                ELSE 0
          END) AS New
     FROM
         tbl_Calls WITH (NOLOCK)
     WHERE
         cl_StartTime BETWEEN @StartDate AND @EndDate) AS Inners
GROUP BY
    New

Let me know if you need further details.


texts: Exporting a Database schema and triggers

texts:
I have a small problem at work. Our DBA went on a journey of self-discovery ... I am very happy for him and I hope he finds what he is looking for. But he left us without any information about how the database he created works, the triggers he has, the connections between tables and, frankly, no documentation.

My question is whether it is possible to export all the information from the database (connections between tables, schema, triggers, fields with data type, etc .. :) in a file from TOAD or another application to mount it in some application that would reverse engineer the database to indicate that same information that we need.

I thank you very much for the help you can give me!


texts: Can you use an inserted variable in a default constraint

texts:
Working in SQL Server, let's say I have a database with two tables: events and competitors.

events has the following columns:

[event_id] [event_name] [winner_id] [loser_id]

competitors has the following columns:

[comp_id] [comp_name]

I wanted to add a default constraint to [event_name] to save me time when inserting a new event. Something similar to:

ALTER TABLE [events]
ADD CONSTRAINT DF_event_name
DEFAULT CAST([winner_id] AS varchar(10)) + ' vs ' + CAST([loser_id] AS varchar(10)) FOR [event_name]

Of course, this is a simplified version, it would also need to implement a join statement with competitors ON comp_id = winner_id and comp_id = loser_id.

When I tried setting the above default constraint, I got the response you are probably expecting:

  The name "winner_id" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

So is there any way to work around what I'm trying to do?
Is there any way to automatically set the value of a cell to a string implementing data inserted from the same row? If that's possible, can it also be done using a join statement? My next idea would be a trigger.


texts: Multi-server administration - force poll on target fails, server needs to be registered locally without FQDN

texts:
I'm experimenting with multi=server administration on my SQL lab. Running SQL 2012 Enterprise SP1 on Windows 2008 R2 on a domain.

I've created a domain account for my sql server agent user on each server including the master. I was able to create the Master and set Targets to two other servers. The targets are added via the registered servers (FQDN) on the CMS which is also the master.

When i go to the master, right click > Multi-Server Administration > Manage Target Servers, i see the servers are listed using the short sever name not FQDN. When I click Force Poll, I get an error message:

This operation requires SQL3 server to be a registered server. (SqlManagerUI)

In order to work around this, i have to setup a LOCAL server registration and use the short name SQL3 instead of a FQDN.

This seems rather inconvenient to say the least - why do i have to register each server locally to use this feature? Why is it relying on the short name and not FQDN. These servers are already registered in my CMS which is also the master server. I could see this being a big problem when managing many servers - would i have to locally register a 100 servers??


texts: Why does updating a large VARBINARY break replication (MSSQL_REPL21100)?

texts:
We have replication set up on a VARBINARY(MAX) column that stores image data (yes, yes, I know - don't blame me, I didn't design it). Recently, we started inserting larger images (around 200kb) and every time it breaks the replication with the following message:

  The distribution agent failed to create temporary files in 'C:\Program Files\Microsoft SQL Server\110\COM' directory. System returned errorcode 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL21100)

If I re-initialise the subscription with a new snapshot, everything syncs up fine (including the VARBINARY) and the error goes away. If I then update the VARBINARY column the replication breaks again and we go back to step 1.

The first thing I checked was that the accounts that run the 'SQL Server' and 'SQL Server Agent' services both have full access to the directory - they did.

Next I made sure the server was configured to replicate large VARBINARYs:

-- Set the maximum replication size to unlimited.
EXEC sp_configure 'max text repl size', -1;
GO
RECONFIGURE;
GO

This didn't help (I think it was already set to unlimited). What is going on here and how do I fix it?


texts:
MySQL 5.6 on Debian 9: I have a master replicating to a slave. On the master I ran an alter table ... to add more ranged partitions:
alter table game_action
  reorganize partition pMAX into (
    partition p2021_01 values less than ( unix_timestamp('2021-02-01 00:00:00') ),
    partition p2021_02 values less than ( unix_timestamp('2021-03-01 00:00:00') ),
    partition p2021_03 values less than ( unix_timestamp('2021-04-01 00:00:00') ),
    partition p2021_04 values less than ( unix_timestamp('2021-05-01 00:00:00') ),
    partition p2021_05 values less than ( unix_timestamp('2021-06-01 00:00:00') ),
    partition p2021_06 values less than ( unix_timestamp('2021-07-01 00:00:00') ),
    partition p2021_07 values less than ( unix_timestamp('2021-08-01 00:00:00') ),
    partition p2021_08 values less than ( unix_timestamp('2021-09-01 00:00:00') ),
    partition p2021_09 values less than ( unix_timestamp('2021-10-01 00:00:00') ),
    partition p2021_10 values less than ( unix_timestamp('2021-11-01 00:00:00') ),
    partition p2021_11 values less than ( unix_timestamp('2021-12-01 00:00:00') ),
    partition p2021_12 values less than ( unix_timestamp('2022-01-01 00:00:00') ),
    partition pMAX values less than (maxvalue)
  );

Unfortunately, stuff happened, and I had to kill mysql on the slave, and when it came back, I found the following after replication had caught up:
mysql> select table_name,partition_name,table_rows from information_schema.partitions where partition_name like 'p2021%' and table_name='game_action';
+-------------+----------------+------------+
| table_name  | partition_name | table_rows |
+-------------+----------------+------------+
| game_action | p2021_01       |   40051706 |
| game_action | p2021_02       |          0 |
| game_action | p2021_03       |          0 |
| game_action | p2021_04       |          0 |
| game_action | p2021_05       |          0 |
| game_action | p2021_06       |          0 |
| game_action | p2021_07       |          0 |
| game_action | p2021_08       |          0 |
| game_action | p2021_09       |          0 |
| game_action | p2021_10       |          0 |
| game_action | p2021_11       |          0 |
| game_action | p2021_12       |          0 |
| game_action | p2021_01       |          0 |
| game_action | p2021_02       |          0 |
| game_action | p2021_03       |          0 |
| game_action | p2021_04       |          0 |
| game_action | p2021_05       |          0 |
| game_action | p2021_06       |          0 |
| game_action | p2021_07       |          0 |
| game_action | p2021_08       |          0 |
| game_action | p2021_09       |          0 |
| game_action | p2021_10       |          0 |
| game_action | p2021_11       |          0 |
| game_action | p2021_12       |          0 |
+-------------+----------------+------------+
24 rows in set (0.01 sec)

show create table only shows the 12 partitions I would expect, so perhaps this is only a problem in information_schema? Can I ignore it - or if not, how can I fix it (without dropping partitions)?


texts:
I built a database several years ago in Access to track over 4K pieces of equipment in and out of a shop, where it was going, current status, etc. It's dying. I would like to upgrade it, but unsure if a split Access DB or a SQL Server 2008 back with Access front would be better/sufficient. I'm leaning towards the latter.
At most 35 simultaneous users, but only on rare occasions.
Would like to track (log) changes to items each time data/status changes.
Print reports, etc.
Keep it SIMPLE for users.


texts: Different Indexes with different query duration

texts:
I had the following table that holds phone contacts of users.

CREATE TABLE `contacts_phone` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `playerPhone` varchar(45) NOT NULL,
  `friendPhone` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `by_contact` (`playerPhone`,`friendPhone`),
  KEY `by_id` (`playerPhone`),
  KEY `by_phone` (`friendPhone`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

I built the next query that returns mutual friends of my friends:

SELECT SQL_NO_CACHE phones.*, COUNT(phones.friendPhone) AS mutual_friends
FROM (SELECT cp2.friendPhone
    FROM contacts_phone cp1
    JOIN contacts_phone cp2 ON cp1.friendPhone= cp2.playerPhone
    WHERE cp1.playerPhone = 'PHONE_NUMBER') phones
WHERE phones.friendPhone IN (SELECT friendPhone
        FROM contacts_phone
        WHERE playerPhone = 'PHONE_NUMBER')
GROUP BY phones.friendPhone

Until this point everything was ok :)

As our product grew we have collected 13M rows to this table and had a product requirement to add the users id to this table. So I made the next change:

ALTER TABLE `contacts_phone`
ADD COLUMN `cid` VARCHAR(12) NULL DEFAULT NULL AFTER `id`,
DROP INDEX `by_contact` ,
ADD UNIQUE INDEX `by_contact` (`playerPhone` ASC, `friendPhone` ASC, `cid` ASC),
ADD INDEX `by_cid` (`cid` ASC);

I added the user's id (cid) to the table and filled the missing data. part of the rows didn't have id so I had to use  DEFAULT NULL.

I built a different but pretty similar query but now using cid instead.

SELECT SQL_NO_CACHE phones.*, COUNT(phones.friendPhone) AS mutual_friends
FROM (SELECT cp2.friendPhone
    FROM contacts_phone cp1
    JOIN contacts_phone cp2 ON cp1.friendPhone = cp2.playerPhone
    WHERE cp1.cid = 'USER_ID') phones
WHERE phones.friendPhone IN (SELECT friendPhone
        FROM contacts_phone
        WHERE cid = 'USER_ID')
GROUP BY phones.friendPhone

I expected to get a similar query duration but the result was that when querying with cid the duration is doubled.

What am I missing out? Do I have something wrong in my design?
Could it be because of the nullable index?


texts: How can I write a proper query to JOIN all of these tables without duplicates?

texts:
Let's say I have four tables:

TABLE:    players
COLUMNS:  id, first_name, last_name

TABLE:    passing_stats
COLUMNS:  id, year, passing_yards (several other passing columns)

TABLE:    rushing_stats
COLUMNS:  id, year, rushing_yards (several other rushing columns)

TABLE:    receiving_stats
COLUMNS:  id, year, receiving_yards (several other receiving columns)

Let's say Michael Vick has an id of 100.  I want to get his full name and all of his stats for each year (passing, rushing, and receiving).

I don't want any duplicates, meaning that rushing stats for 2011 should appear in the same row as passing stats for 2011.

What is the most elegant way to write this query?  Thanks.


texts:
I have a contact mechanism between my website's users (always between two users). User A (sender) can send a message to users B (receiver) based on two rules:

User A could send 2 messages at most to user B and he must receive at least one message from user B to be able to send another 2.
User A could send 4 messages at most to everybody in daily period, not more.

And here is my table structure:

-- contact
+----+-----------+------------+--------------------------+------------+
| id | sender_id | receive_id |          message         |  date_time |
+----+-----------+------------+--------------------------+------------+
| 1  | 123       | 456        | Hi, how are you?         | 1492431111 |
| 2  | 123       | 789        | How are you doing?       | 1492431112 |
| 3  | 456       | 789        | Why would you say that?  | 1492431113 |
| 4  | 123       | 456        | Why don't you answer?    | 1492431114 |
| 5  | 789       | 456        | Because the sky is high  | 1492431115 |
| 6  | 123       | 789        | Hello?                   | 1492431116 |
+----+-----------+------------+--------------------------+------------+

And here is my current query:

INSERT INTO contact(sender_id, receive_id, message, date_time )
SELECT ?, ?, ?, unix_timestamp()
FROM dual
WHERE NOT EXISTS(
    SELECT count(*) AS num_day,
    FROM contact
    WHERE user_id = ?
      AND date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 day))
    HAVING num_day > 4
)

As you can see, only the second rule is implemented in my query. How can I also implement the first rule to the query?


texts: Partial unique constraint spanning multiple tables in postgres

texts:
To enforce partial uniqueness in postgres, it is a well known workaround to create a partial unique index instead of an explicit constraint, like so:

CREATE TABLE possession (
  possession_id serial PRIMARY KEY,
  owner_id integer NOT NULL REFERENCES owner(owner_id),
  special boolean NOT NULL DEFAULT false
);
CREATE UNIQUE INDEX possession_unique_special ON possession(owner_id, special) WHERE special = true;

This would restrict each owner to having no more than one special possession at the database level. It obviously isn't possible to create indices spanning multiple tables, so this method cannot be used to enforce partial uniqueness in a supertype & subtype situation where the columns exist in different tables.

CREATE TABLE possession (
  possession_id serial PRIMARY KEY,
  owner_id integer NOT NULL REFERENCES owner(owner_id)
);

CREATE TABLE toy (
  possession_id integer PRIMARY KEY REFERENCES possession(possession_id),
  special boolean NOT NULL DEFAULT false
);

As you can see, the earlier method does not allow for restricting each owner to no more than one special toy in this example. Assuming each possession must implement exactly one subtype, what is the best way to enforce this constraint in postgres without substantially altering the original tables?


texts: Tools or built-in functionality to alert upon table data changes

texts:
We have the following (unfortunate) scenario: we have a database where on-the-fly changes are being made by developers as quick remedies to dictionary tables (order types, document types, fee distributions, and so forth). This wouldn't be an issue if these particular developers didn't forget to check their changes into TFS, but alas, we face it. This causes an issue where the database project(s) in TFS do not properly reflect what is in the database.

Essentially, I want receive an e-mail or other type of notification (table insert with necessary information) when a change is made to 15-20 tables, what the change was, and who committed the transaction.

What is the best method to monitor this type of behavior? We are willing to invest in tools that allow for this ability. I've checked out RedGate SQL Monitor, but it doesn't seem to have this feature set (or I am missing it).

I've also seen usage of triggers, but we have a large amount of tables that would require monitoring and I don't know the performance degradation that might incur and if we can collect the appropriate data. I do like the trigger approach where it would insert the information into a table ([dbo].[TABLE_UPDATES]) because it would allow us to build an SSRS report to run against and pull data as needed, but I'm not sure I 100% trust this as a solution.

We currently use RedGate DLM Dashboard for monitoring schema changes, and it has proven quite invaluable to receive those updates. We just want to expand it to the data itself.

Disclaimer: I know - terrible practice by developers to apply things directly to a database without checking in their changes, but we are currently having to deal with this hurdle and reign these individuals from wildly shooting at the hip. We are wanting a long-term solution for accountability purposes as our database and company grows.

Thanks in advance.


texts: Proper way to reset/cleanup a connection

texts:
I have a very simple connection pooler in my application. When handing back a connection I need to ensure that no variables are set and no transaction is still on going. I understand that ROLLBACK; DISCARD ALL; should be enough.

In most cases, if the transaction was successful, it will be committed before handing it over to the pooler and at this point, I will have a warning such as WARNING:  there is no transaction in progress.

Is there any way to execute a ROLLBACK only if we are still in a transaction?
Or is there an other preferred method to cleanup a connection?


texts: What is FT BATCH CMPLETE in the cmd column for sysprocesses?

texts:
I was trying to figure out what was causing high cpu usage in our sql server and found two processes that were consuming high CPU.  When I queried sysprocesses to see what the spid was doing, it had FT BATCH CMPLETE in the cmd column.  Does anyone know what this command is?  Nothing came up when I looked at the sql_handle.

Is it related to full text?  I can't figure out what the process is doing, even tracing it comes up with nothing.  Seems like the process is just idle but yet it has high CPU usage.  We are using SQL Server 2012 SP1 on Windows 2012 R2.

Thanks!


texts:
My Environment is SQL Server 2017 Distributed Availability Groups, details as follows:

Configured SQL Distributed AG from DC1 to DC2.

DC1 (Production): TEMPDB1 AND TEMPDB2 (HOSTED ON WSFC1)
DC2 (DR): TEMPDB3 AND TEMPDB4 (HOSTED ON WSFC2)
DC1 and DC2 located in two different Geo location
DC1 is unavailable (shutdown).

Please suggest on following scenarios:

What are the steps/commands need to execute on DC2 to bring as a Production?
Once DC1 is up/running, what are the steps/commands need to execute in DC2 and DC1 to bring DC1 as a Production?

Here are the screenshots of status from 2 DC's

DC1-AG Status before DC1 shutdown:

DC2-AG Status before DC1 shutdown:

DC2-AG Status After DC1 shutdown:

After DC1 shutdown, tried to access DC2-DB1, got blow error.

Changed DC2-AG2 properties as mentioned below (Allow R/W Connections and Readable Secondary=Yes)

After changing properties, I am able to access DB1 but not able to perform any DML operations in DC2-DB1.

This DB should allow for all operations because app is pointing DC2-DB during DC1 shutdown.

Please suggest/advise on this.


texts: Literal does not match format string error in oracle

texts:
I was given a SELECT statement that is running in a Crystal Report to investigate a data problem but when I try to run it against the database I get the error:

  ORA-01861: literal does not match format string
  01861. 00000 -  "literal does not match format string"
  *Cause:    Literals in the input must be the same length as literals in
             the format string (with the exception of leading whitespace).  If the
             "FX" modifier has been toggled on, the literal must match exactly,
             with no extra whitespace.
  *Action:   Correct the format string to match the literal.

I think it has to do with the Date comparisons but I am not sure what is wrong. I know nothing about oracle and since the error message does not specify the line the error is on I am only guessing at what might be the problem. Can anyone point me to what is causing this problem?

   SELECT ACTDET.GROSS_VOLUME,
          ACTDET.NET_VOLUME,
          ACTIVITY.SHIPPER_ID,
          ACTIVITY.ACTIVITY_START_DATE,
          SHIPPER.NAME_1,
          TANK.TANK_PRODUCT_CODE,
          PRODUCT.DESCRIPTION_2,
          SITE.NAME_1,
          SITE.NAME_2
   FROM   G3USER.ACTIVITY ACTIVITY,
          G3USER.ACTDET ACTDET,
          G3USER.SHIPPER SHIPPER,
          G3USER.TANK TANK,
          G3USER.SITE SITE,
          G3USER.PRODUCT PRODUCT
   WHERE ((ACTIVITY.TERMINAL_ID=ACTDET.TERMINAL_ID) AND (ACTIVITY.BOL_NUMBER=ACTDET.BOL_NUMBER))
   AND ((ACTIVITY.TERMINAL_ID=SHIPPER.TERMINAL_ID) AND (ACTIVITY.SHIPPER_ID=SHIPPER.SHIPPER_ID))
   AND ((ACTDET.TERMINAL_ID=TANK.TERMINAL_ID) AND (ACTDET.TANK_ID=TANK.TANK_ID))
   AND (ACTDET.TERMINAL_ID=SITE.TERMINAL_ID)
   AND ((TANK.TERMINAL_ID=PRODUCT.TERMINAL_ID AND (TANK.TANK_PRODUCT_CODE=PRODUCT.PRODUCT_CODE))
   AND (ACTIVITY.ACTIVITY_START_DATE >= '2014-02-05 00:00:00') AND (ACTIVITY.ACTIVITY_START_DATE < '2014-02-06 00:00:00'));


texts: Get count of near points based on 2 unrelated tables

texts:
I have 2 tables, cities and listings and there is not FK relationship or any other field that I can create a relationship based on.
They both have a geography field.
I would like to get list of all cities that have at least 1 listing (using Location property of both tables)
When I run the following query, I get cities with count of 1, multiple times. I simply want to get cities with number of listings near it as the count.
DECLARE @distance FLOAT = 100000;
SELECT c.name, count(l.ListingID) FROM listings l, Cities c
WHERE l.Location.STDistance(c.Location) <= @distance
GROUP BY c.name, l.listingid

I also have a 16x16x16x16 and 256x256x256x256 indexes on both tables for Location field if that matters...
I am using SQL Server 2019
EDIT: indexes on both tables
CREATE SPATIAL INDEX [256_HHHH] ON [dbo].[Listings]
(
    [Location]
)USING  GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 256, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE SPATIAL INDEX [16_HHHH] ON [dbo].[Listings]
(
    [Location]
)USING  GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Actual Execution Plan
https://www.brentozar.com/pastetheplan/?id=rJ2HftM4v


texts: Which oracle should I download for windows 7 x64?

texts:
My os is: windows 7 x64

I tried to download oracle and install it in my computer, but I have downloaded 3 different oracle releases(including oracle 11G), they can't be installed.

They reported such error:

Need os version: 5.1, ..., 6.0
But found: 6.1

I have spent a day to download them, that I don't know which one I should try.

Is there any oracle 10G can run in windows 7 x64? If not, oracle 11G would be fine.


texts: what is difference between uniform extents and mixed extents in Database file storage structure?

texts:
I just went to some resources saying:

uniform extents are owned by single object and mixed extents can be shared by maximum 8 objects.

But I'm unable to grasp their insight meaning, like what is the benefit of this structure? basically, I am new in this field, so if you could provide some intuitive examples and applications, it would be highly appreciated.


texts: Mongo DB Upgrade 3.0 to 3.2 Verification of Upgrade

texts:
We have a few environments with MongoDB 3.0, these are standalone servers.
These servers are already using WiredTiger Storage engine.
I am trying to upgrade them to 4.2.

As per the upgrade documentation you can't go from 3.0 to 4.2 but have to make incremental upgrades from 3.0 to 3.2 and so on to 4.2.

As this is something that is going to be repeated on multiple env's I am trying to automate it where in a script does the upgrade but at certain points in the upgrade gives out diagnostic info that someone can look at and confirm that a step in the upgrade process has been successful.

Doing this on EC2 instances with Amazon Linux 2.

To test this out I did a manual upgrade from 3.0 to 3.2. The processes came up fine, I was able to connect to the db and query a collection and everything "seems" fine.
Across various DB's the total size of the data is approx 2.3TB, and I was surprised that after the upgrade the DB came up in a few seconds.

I was looking at diagnostic commands to figure out if I can verify in the script that the upgrade happened without a hitch. (Other than the mongod process starting correctly).
The only thing I could find was this.
https://docs.mongodb.com/v3.2/reference/command/serverStatus/#asserts
And am not sure if this is good enough.

So TL;DR; Can I programatically verify that the upgrade was successful by looking at any metadata from the DB itself.

Note: a tangential question, YAML is current preferred config file format, as it is supported even with 4.2 any issues continuing with it?


texts: How to delete duplicate records in MySQL, in a table without IDs?

texts:
I need to delete the duplicate records in this table. However, there is no id for each row.
Example Data

product
amount
quantity

table
2000
5

chair
300
25

TV
30000
4

bike
300
25

table
2000
5

chair
300
25

chair
300
25

Expected Results
I need to get this result.

product
amount
quantity

table
2000
5

chair
300
25

TV
30000
4

bike
300
25

Script with ID
If there were an id, I could have used:
DELETE p1 FROM products p1
INNER JOIN products p2
WHERE p1.id < p2.id AND p1.product = p2.product;


texts:
I am new to the DBA Part.

I had an 110 GB Database from which I deleted records from a 95GB sized table. Since the SQL Server Stores memory for the future, it still had 110 GB on the disc.

I ran the Shrink Database through GUI, but it was taking a lot of time. So, I cancelled it, but it remained in the greyed state. When shown from sys.sysprocesses, it shows the last batch time some 3 hours ago and is in runnable state. Can I kill the SPID?

To make it worse, Database is in multi-user mode.

Update: After three long hours, the process got rolled back on its own.


texts:
In this query I want to display the columns of partition and subpartition of a table. It works fine if the nom_tabla query get only one table, but when the select give us more than one table it doesn't works.

with nom_tabla as
(
  select table_name from dba_tables
  where table_name like 'TE%'
),
col_part as
(
  SELECT listagg(column_name, ', ' )
    within group (order by column_position) PART_POR
  FROM dba_part_key_columns WHERE NAME in
  (
    select *
    from nom_tabla
  )
  GROUP BY name
  ORDER BY name
),
col_subpart as
(
  SELECT listagg(column_name, ', ' )
    within group (order by column_position) PART_POR
  FROM dba_subpart_key_columns
  WHERE NAME IN
  (
    select * from nom_tabla
  )
  GROUP BY name
  ORDER BY name
)
select
  t.OWNER PROPIETARIO,
  t.TABLE_NAME as TABLA,
  ' - ' as PARTICION,
  NVL (pt.PARTITIONING_TYPE,' - ') AS TIPO_PART,
  ' - ' as SUBPARTICION,
  NVL(pt.SUBPARTITIONING_TYPE, ' - ') AS TIPO_SUBPART,
  NVL(t.tablespace_name,' - ') as TS,
  NVL((select * from col_part),'-') ,
  NVL((select * from col_subpart),'-')
  From dba_tables t, dba_part_tables pt
  where   t.owner not in
  ('SYS','SYSTEM','DBSNMP','SYSCAT','WMSYS','MDSYS','XDB','ORDDATA')
  and     t.owner=pt.owner(+)
  and t.table_name=pt.table_name(+)
  and t.TABLE_NAME in (select * from nom_tabla);

I think that the problem is in this section of the query because it return more than one record:

, NVL((select * from col_part),'-') , NVL((select * from col_subpart),'-')

I don't know how to avoid this problem.


texts:
how do i get rows for each month which contain the store with the highest number of purchases?

my code is:

SELECT DATE_FORMAT(buyDate, '%M') as month, count(id) as numPurchases, store
from purchase, customerpurchases
where purchase.id = customerpurchases.purchaseid
group by month, store;


texts: Find all instances where a user has been explicitly granted permissions on an object

texts:
Is it possible to generate the list of objects a user has been given explicit access to, for a certain user?

I'd like to see a list of all the items a user has explicit permissions for, excluding any permissions inherited through roles etc.


texts:
This is the result set that I get returned using my query:

empname itemssold
JJ      14

But this is my desired result set:

empname itemssold
AA      0
BB      0
CC      0
DD      0
JJ      14
RR      0

It seems to pulls all employees until I add in the where clause to my query which makes me wonder if I am adding it in the wrong place, or am I setting up my query incorrectly?

Below is DDL & My query I use:

Create Table #ShowAll
(
  empname varchar(1000)
  ,empid varchar(100)
)

Create Table #Data
(
  empid varchar(100)
  ,itemssold int
  ,beenverified varchar(100)
)

Insert Into #ShowAll Values
('JJ', 'J1'),
('AA', 'A1'),
('BB', 'B1'),
('CC', 'C1'),
('DD', 'D1'),
('RR', 'R1')

Insert Into #Data Values
('J1','14', 'Yes'),
('A1', '12','No'),
('B1', '13', 'No')

Select sa.empname
    , Da.itemssold
FROM #ShowAll sa
LEFT JOIN #Data da
    ON sa.empid = da.empid
WHERE da.beenverified = 'Yes'


texts: How to know db size by the backup? in DB2

texts:
I want to add validation for free space before restore a db2 database.
I have the *.001 backup file (compressed).

How do I check the estimate db size of that backup?

I can't estimate because the compression ratio is very diverse. db2ckbkp doesn't have that data.


texts: Query over SSRS/RPC:Completed a lot slower than SSMS

texts:
So, as the title says, I have a query that is run by SQL Server Reporting Services, when it ran by SSRS, I can see in SQL Profiler it's executed using RPC Protocol, and takes about 30 seconds. When I execute the same query (I copied it from Profiler to be sure) over SQL Management Studio, it executes instantly (less than 1 second).

Everything is in the same server, SSRS, Management Studio, Profiler and database instance, so I'm also guessing network latency should not be the culprit

The query is common SQL Text and not a Stored Procedure. I also removed the parametrization of the query, and I hard-coded the Id in the query. The same issue persists, slow on SSRS, fast on SSMS. So I don't think it's a problem of parameter sniffing. My query is very simple, a couple joins and I'm filtering the main table by Primary Key. The only "non-standard" feature it uses, is "WITH XMLNAMESPACES" clause, that I use to select a couple XML values using the syntax s.MyXMLColumn.value('(/Details/MyValueHere)

Using SQL Profiler I compared CPU, Read and Duration, only the Duration column has a higher value on the slow execution. I also tried to compare the executions plans using SQL Profiler and in the diagram they are equal, but when comparing the XML there are some differences, that I am not sure if they matter or not. Also the section RunTimeInformation is repeated several times in several places in the plans

Plan for RPC:

<QueryPlan DegreeOfParallelism="0" MemoryGrant="3040" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="640" CompileTime="475" CompileCPU="471" CompileMemory="12832">
    <MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="3040" RequiredMemory="512" DesiredMemory="3040" RequestedMemory="3040" GrantWaitTime="0" GrantedMemory="3040" MaxUsedMemory="24"/>
    <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="104712" EstimatedPagesCached="209424" EstimatedAvailableDegreeOfParallelism="16"/>
    <RelOp NodeId="0" PhysicalOp="Sort" LogicalOp="Distinct Sort" EstimateRows="31.3203" EstimateIO="0.0112613" EstimateCPU="0.000371601" AvgRowSize="28816" EstimatedTotalSubtreeCost="547.917" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
    ...
    ...
    <RunTimeInformation>
        <RunTimeCountersPerThread Thread="0" ActualRows="25" ActualRebinds="25" ActualRewinds="0" ActualEndOfScans="0" ActualExecutions="25"/>
    </RunTimeInformation>
    ...
    ...

Plan for SQL:BatchCompleted

<QueryPlan NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="640" CompileTime="484" CompileCPU="484" CompileMemory="12832">
    <MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="3040"/>
    <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="104712" EstimatedPagesCached="209424" EstimatedAvailableDegreeOfParallelism="16"/>
    <RelOp NodeId="0" PhysicalOp="Sort" LogicalOp="Distinct Sort" EstimateRows="31.3203" EstimateIO="0.0112613" EstimateCPU="0.000371601" AvgRowSize="28816" EstimatedTotalSubtreeCost="547.917" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
    <OutputList>
    ...
    ...
    <RunTimeInformation>
        <RunTimeCountersPerThread Thread="0" ActualRows="25" ActualRebinds="25" ActualRewinds="0" ActualEndOfScans="0" ActualExecutions="25"/>
    </RunTimeInformation>
    ...
    ...

I am no DBA, so I'm unsure where else to look. So the question is, how do I find the cause of performance differences between the same query executed over RPC and SQL Batch?

Edit: Additional things I tried based on answers/comments:

SET ARITHABORT OFF makes no difference


texts: Tokudb 5.7 [Warning] The partition engine is deprecated and will be removed in a future release

texts:
I am running upgrade from Tokudb 5.6.30 to 5.7.17 (Percona distribution) and I am getting this warning

  [Warning] The partition engine, used by table ‘my_table’, is deprecated and will be removed in a future release. Please use native partitioning instead.

Does it mean that I can still use it in 5.7.17 tokudb, but to use native partitioning in a future releases (8.0) I will have to rebuild this table to use innodb. In other words, is Percona or any other distribution going to support partioning in Tokudb beyond 5.7?


texts: Multiple selects ordering desc from single table

texts:
I'm trying to do multiple selects to give multiple ordered columns.  I'm struggling to get the below mysql query to achieve the output I want.

SQLfiddle:  http://www.sqlfiddle.com/#!2/a1002/3

Table of example data table, veh_summary :

veh_id    fac_id   item_id    w_name    stat
   1        1         51      name1      45
   1        1         52      name2      10
   1        1         53      name3      2
   1        2         51      name1      28
   1        2         52      name2      17
   1        2         53      name3      67
   1        3         51      name1      0
   1        3         52      name2      44
   1        3         53      name3      33
   2        1         51      name1      26

Output I want where each multiple select are ordered desc with limit:

item_id_1   w_name_1   stat_1   item_id_2   w_name_2   stat_2
    51        name1      45         53        name3      67
    52        name2      10         51        name1      28

I'm trying this query:

select a.item_id as item_id_1, a.w_name as w_name_1, a.stat as stat_1,
   b.item_id as item_id_2, b.w_name as w_name_2, b.stat as stat_2
from
  (select item_id, w_name, stat from veh_summary
      where veh_id = 1 and fac_id = 1 ) a,
  (select item_id, w_name, stat from veh_summary
      where veh_id = 1 and fac_id = 2 ) b
  order by stat_1 desc, stat_2 desc limit 2

But get this output where the first select is not ordering desc:

item_id_1   w_name_1   stat_1   item_id_2   w_name_2   stat_2
    51        name1       45        53         name3     67
    51        name1       45        51         name1     28

I'm sure this is simple to do, I've also tried the order by limit in the select itself.  I've done a bit of searching through the questions but don't seem to see one that fits what i'm looking for.


texts: Access - SQL Server: cannot generate SSPI context

texts:
I actually understand why this error messages pops up, but I do not know how to implement my solution.

There is a SQL Server network instance I am providing some MS Access forms to interact with, and I myself have no connectivity issues. This is because both in the server and in the database I am actually registered with the same username and password as in my own pc, so the kerberos authentication works brilliantly... until I share the Access file with someone else, then they get this error (because their local users are not registered in the server &/or database)

The thing is, I don't want the process to use client-local user and password to authenticate the connections (or the kerberos service all together), but rather database-local user and passowrd. I originally linked the remote tables to Access using ODBC calls, providing database-local user and password (different from my credentials) and I would like it to be kept that way (or similar enough) so that someone else can open the forms and interact with them using database-local credentials.

I just don't know how to get around this. Help please :'(

Last-minute updated before posting: after this error pops up the user is prompted with the connection dialog where they can edit the username and password provided to make the connection. So the problem is not actually a blocking wall, but rather an uncomfortable detail.

The question remains though: is there a way to make this the default behavior?


texts:
I have a table with similar structure to the below, and a field has a NULL value in it.  When I query the table this record is not returned, it is omitted.  How should the query be altered so that this record is returned?

Declare @Fiesty Table
(
  numberfour varchar(100)
  ,valid varchar(20)
)

Insert Into @Fiesty (numberfour, valid) Values
('1ACRELBS', NULL), ('4ACRELBS', 'Green'), ('17ACRELBS', 'White')

Select * FROM @Fiesty WHERE valid NOT IN ('Green', 'Blue', 'White')

My expected returned result would be where numberfour = 1ACRELBS


texts: What does Oracle use for handling a client connection (process or thread)?

texts:
SQL Server 2016 and MySQL 5.7 use thread-based connection handling.

PostgreSQL 9.5 uses one operating system process for each individual connection.

According to Oracle 12c documentation it's not clear if a thread-based or a process-based model is used. So how does Oracle handle user connections?


texts: Does the target for Double-Take replication require an Oracle license?

texts: Where would Vision Solution's Double-Take product fall with regard to Oracle Licensing?  It is both a backup and a replication of the database.  Oracle's Software Investment Guide has a section on Backup, Standby, Failover, and Remote Mirroring.  My concern is that this product would fall under remote mirroring and the server would need to be licensed.


texts: See direct (instead of indirect) info about rented book in my "actors_directors" talbe dropdown list - Access365

texts:
I'm building a database of "actors_directors" who are renting "books" from my library - a kind of exercise to learn database basics :-)

This is how my "actors_directors" table looks like. They are the customers who are renting the books from my library. Index field is a primary key in this table.

There are books listed in different table called "books" and similarly "Index" field is a primary key in this table.

Finally I have all the instances of the book being rented in my third table "checkouts". Please ignore the last three columns there. Similarly "Index" field is a primary key in this table.
"ForeignKeyCustomer" column refers to Index column in "actors_directors" table and its one-to-many relationship.
"ForeignKeyBook" column refers to Index column in "books" table and its one-to-many relationship.

These are relationship I've set for my tables.

Now after I unfold the pluses in the "actors_directors" table like it's shown in the next picture, there are not exactly the info I wanted to see.
What I see is the number of the book, some specific actor rented, while I'd like to see the title of this book.

Instead of info about "ForeignKeyBook" - the book's number, I'd like to see it's Title in this place. But the info about the title is in different table. Can U help me with that?

I don't want to see the book's number, because it tells me nothing.

I want to see the book's title, that is in different table, but also the "CheckOutDate" and "RentPeriod" and "RentFee" from this "checkouts" table, that's related/linked in my current situation (this screenshot).

Thanks in advance my friends.


texts: Check which condition has met - Sql Server

texts:
In a simple scalar function, I got several "IFs" separated each of them with a unique condition.

Is there any way that I can check which condition was satisfied without having to change the function to log my conditions?

IF ( SOME CONDITION) RETURN 1;

IF ( SOME OTHER CONDITION ) RETURN 1;

What I need to do is check if the condition met was the first or the second.

EDIT:
I know this looks terrible but I also need to get the SQL code that made the condition work. I have no idea how this is possible but I'm hoping any solution.


texts: Full text search with json field in SQLite

texts:
Is it possible to have full text search in SQLite along using json?

In SQLite json is stored as TEXT (JSON1).
Full text search requires creation of virtual table (full text) but I don't know how to connect those two extensions so that full text search wouldn't search in json field names.

WRITING (ID, FK_PERSON_ID, BOOK_NAME, TEXT_JSON)
PEOPLE (PERSON_ID, NAME)

additionally TEXT_JSON for certain person contains following json objects:

-- WRITING.ID = 1, WRITING.FK_PERSON_ID = 1, BOOK_NAME = "NAME_1"
{
  "chapter1": "Title1",
  "text1": "This is sample sentence with word text",
  "text2": "This is sample sentence with word text"
}
-- WRITING.ID = 2, WRITING.FK_PERSON_ID = 1, BOOK_NAME = "NAME_101"
{
  "chapter1": "Title2",
  "text1": "This is sample sentence without"
}

(the structure of json object can differ)

How should I setup virtual table for full text search to search single person writings BOOK_NAME and values of all TEXT_JSON attributes? Searching word text in writings of PERSON_ID = 1 would return only WRITING.ID = 1.


texts: Master-master database replication on 5 servers

texts: My current project requires immediate replication between 5 databases. Servers are physically distributed across the globe. Currently we use Redis with one master server installed on another 6th server. All database writes on any of 5 servers are performed to this 6th master server, and other 5 are slaves of it. This approach has a lot of flaws and i'm looking for solution to replace it. Any suggestions please?


texts: PostgreSQL: PANIC: heap2_redo: unknown op code 32

texts:
I was doing migrations while the server was still accessing the slave DB. I have a master / slave setup. After I restarted the server, I realized I couldn't connect to the SLAVE anymore. I went into the SLAVE and tried restarting Postgres, this is what it returned:

$ sudo /etc/init.d/postgresql start
 * Starting PostgreSQL 9.3 database server
 * The PostgreSQL server failed to start. Please check the log output:
2014-12-16 22:14:20 UTC LOG:  database system was interrupted while in recovery at log time 2014-12-16 20:57:20 UTC
2014-12-16 22:14:20 UTC HINT:  If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.
2014-12-16 22:14:20 UTC LOG:  entering standby mode
2014-12-16 22:14:20 UTC LOG:  redo starts at 0/38620168
2014-12-16 22:14:20 UTC LOG:  consistent recovery state reached at 0/38680BF0
2014-12-16 22:14:20 UTC LOG:  database system is ready to accept read only connections
2014-12-16 22:14:20 UTC PANIC:  heap2_redo: unknown op code 32
2014-12-16 22:14:20 UTC CONTEXT:  xlog redo UNKNOWN
2014-12-16 22:14:21 UTC LOG:  startup process (PID 18357) was terminated by signal 6: Aborted
2014-12-16 22:14:21 UTC LOG:  terminating any other active server processes

Any idea on what could be causing this and what I can do to fix this? I'm going into a PANIC frenzy.


texts: mysql select latest per group using 2 different tables along with self join

texts:
I have a query SQL Fiddle which contains 2 tables

  tbl_data

CREATE TABLE tbl_data (
  id int(10) unsigned NOT NULL,
  type varchar(32) NOT NULL DEFAULT '',
  title varchar(255) NOT NULL DEFAULT '',
  uid int(11) NOT NULL DEFAULT '0',
  status int(11) NOT NULL DEFAULT '1',
  created int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB;

  tbl_slot_booking

CREATE TABLE IF NOT EXISTS tbl_slot_booking (
    bundle varchar(128) NOT NULL DEFAULT '',
    entity_id int(10) unsigned NOT NULL,
    field_slot_target_id int(10) unsigned
) ENGINE=InnoDB;

  Requirement:

  I want to list all the distinct slots sorted by latest booking added.
  Currently I am able to show the results as follows

  Following is the query and result output

SELECT
    td.id AS booking_id,
    td.title AS booking_title,
  node_slot.id AS slot_id,
    node_slot.title AS slot_title,
    FROM_UNIXTIME(td.created) AS booking_time
    FROM
    tbl_data td
    LEFT JOIN
    tbl_slot_booking tsb ON td.id = tsb.entity_id
    LEFT JOIN
    tbl_data node_slot ON tsb.field_slot_target_id = node_slot.id
WHERE
    td.type = 'slot_booking'
#GROUP BY (field_slot_target_id)
ORDER BY td.created DESC;

  Issue:

  The output is showing duplicate result if I am not using group by clause.
  If I use group by clause, the oldest date is retrieved from the records.

EDIT

Although I am able to successfully sort the content by booking time field_data.created using MAX(FROM_UNIXTIME(field_data.created)), however the other fields field_data.id, field_data.title are still showing oldest result in each row.


texts: How can I run this SQL query across all of my databases at once?

texts:
I have a query to let me know basic info on backups. I have several SQL servers that I'd like to be able to query at once. Is it possible to run the following query across several servers at one time?

SELECT
   A.[Server],
   A.database_name,
   A.last_db_backup_date,
   B.backup_type,
   B.backup_start_date,
   B.expiration_date,
   B.backup_size,
   B.logical_device_name,
   B.physical_device_name,
   B.backupset_name,
   B.description
FROM
   (
   SELECT
       CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
       msdb.dbo.backupset.database_name,
       MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
   FROM    msdb.dbo.backupmediafamily
       INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
   GROUP BY
       msdb.dbo.backupset.database_name
   ) AS A

   LEFT JOIN

   (
   SELECT
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   msdb.dbo.backupset.backup_size,
   msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupmediafamily.physical_device_name,
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description,
   CASE msdb..backupset.type
       WHEN 'D' THEN 'Full Database'
       WHEN 'L' THEN 'Log'
       WHEN 'I' THEN 'Differential'
       WHEN 'F' THEN 'File Level'
       WHEN 'G' THEN 'File Level Differential'
       WHEN 'P' THEN 'Partial'
       WHEN 'Q' THEN 'Differential partial'
   END AS backup_type
FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
   ) AS B
   ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
ORDER BY
   A.database_name


texts:
I have a SQL Server 2012 database with a number of Agent jobs.  The jobs run without a problem but the job history is always empty.  Why?

I also note that running a job manually (right click and "Start Job at Step...") will run the job but the window showing progress of the job never closes.  It is as if the job steps complete but the job itself never completes.

All tasks have been executed as a sysadmin so permissions should not be an issue.

Running the jobs via sp_start_job results in the jobs completing but still no job history.

I've done all the usual checking/changing of jobhistory_max_rows_per_job etc.


texts:
I have edited the question.
I was trying to run the sh.splitAt

mongos> sh.splitAt("test.zp",{"city": "1"})it worked.

But the problem is at sharding chunks.

when i have run the command sh.status().
It shows like this

databases:
{  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
{  "_id" : "test",  "partitioned" : true,  "primary" : "shard0000" }
test.zp
shard key: { "city" : 1 }
chunks:shard0000  34
too many chunks to print, use verbose if you want to force print.

i have three more shards but with different ip's.why sharding is not applied to the secondary shards. I have shards like

    {  "_id" : "shard0000", }
{  "_id" : "shard0001", }
{  "_id" : "shard0002", }
{  "_id" : "shard0003", }

How  chunks will be added to "shard0001" and remaining shards.I have added all shards to the test db. and my data is 1.2GB.


texts: How to make the SQL query more efficient?

texts:
I use mysql under Linux. My table is about 8000000 rows. The table has index(advertiser_id,activity_date),(activity_date,advertiser_id,activity_type).
The table is looks as following:

activity_date        advertiser_id activity_type message
2014-06-01 00:00:16  80149         11            Raised max bid
2014-06-01 00:00:16  80149         12            Set Bid Management
2014-06-01 00:01:49  80149         11            Raised max bid
2014-06-01 00:01:49  80149         12            Set Bid Management
2014-06-01 00:03:17  37            12            Lowered daily budget
2014-06-01 00:03:17  59            11            Lowered max bid
2014-06-01 00:03:17  59            14            Raised daily budget

Each row is an activity. I try to write SQL for each time one aid of activity_type:11(UPDATE_AD_BID), count the all other actions of this aid within one day. if one aid have a activity_type:11(UPDATE_AD_BID) at 2014-03-14 16:21:02, I need all the activity of this aid from 2014-03-13 16:21:02 to 2014-03-14 16:21:02. The SQL query I wrote is as following

SELECT bid_change.advertiser_id,bid_change.activity_date,
    CASE bid_change.activity_type WHEN 11 THEN IF(bid_change.message Like '%Raised%', 1, -1) ELSE 0 END as bid,
    sum(CASE change_type.activity_type WHEN 11 THEN 1 ELSE 0 END) as UPDATE_AD_BID,
    sum(CASE change_type.activity_type WHEN 12 THEN 1 ELSE 0 END) as UPDATE_AD_BUDGET,
    sum(CASE change_type.activity_type WHEN 14 THEN 1 ELSE 0 END) as CREATIVE,
FROM (select * from post_funnel) as bid_change
LEFT JOIN (select * from post_funnel) as change_type
ON bid_change.advertiser_id = change_type.advertiser_id
AND change_type.activity_date BETWEEN bid_change.activity_date - INTERVAL 1 DAY AND bid_change.activity_date -INTERVAL 1 SECOND
WHERE bid_change.activity_type = 11
GROUP BY bid_change.advertiser_id, bid_change.activity_date;

The SQL work fine with small test data set. But when I use it with large data set. It took forever to run. Is there anyone I can make the SQL more efficient? or I need to look for a big data solution with Hadoop or so?

update:
output should be as following

activity_date advertiser_id bid  UPDATE_AD_BID UPDATE_AD_BUDGET CREATIVE
2014-06-01 00:00:16  80149  1    0             0                0
2014-06-01 00:01:49  80149  1    1             1                0
2014-06-01 00:03:17  59     -1   0             0                0


texts: Inserting INTO XML Column shows nothing at all

texts:
I am trying to populate xml in table by,

Declare @docy xml = N'<elements>..... 6000 character file </elements>'

SET IDENTITY_INSERT TableName ON

INSERT TableName(Id, ID_A, ID_B, Xml) VALUES (3, 0, 0, @docy)

SET IDENTITY_INSERT TableName OFF

but now all I can see is a record with id = 3, ID_A = 0 and ID_B=0 but xml = empty.....

It worked yesterday, I came in morning and it was empty as well, so I started again and now it doesn't work.

Here's the table if you want,

USE [DBNAME]
GO

DROP TABLE [dbo].[TableName]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TableName](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ID_A] [int] NULL,
    [ID_B] [int] NULL,
    [Xml] [xml] NULL,
 CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
 CONSTRAINT [IX_TableName] UNIQUE NONCLUSTERED
(
    [ID_A] ASC,
    [ID_B] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO


texts: Multiple user roles with different attributes

texts:
I need to design a database that has multiple users, with multiple roles. For instance, a user can be an admin, company, employee, or customer. Each user can have any number of these roles. Right now I have tables that look like this:

Right now, it seems as though when a user logs in, we will have to return from the "What role(s) is/are the user?" query in order to get the information out of the correct table (ex. admins, employees, customers, or companies). I would like to be able to query one time and grab all of a users information. Is this possible, if so how could it be done?

To sum up the problem:
Notice that their are two independent sections (not connected by any lines to the other section). I need a way to connect those.


texts: What's a process for determining table links?

texts:
I need to make several queries to get information out of our Oracle 12c database. I've used oracle SQL Developer to determine primary and foreign key relationships, but how do I link tables when there are no foreign-primary keys defined?

I've thought of using helper tables in creating links, but again, I don't know what helper tables to pick.


texts: BCP Errors: String data, right truncation; Unexpected EOF

texts:
OK... I give up.  I have been struggling with trying to import a semicolon-separated file (field1;field2) with BCP for the past two days, but I only have been getting these errors:

Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]String data, right truncation
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unexpected EOF encountered in BCP data-file

A notepad++ screenshot of my input files is below:

Please note that some fields can have whitespace before the semicolon.

I don't need to import all fields, so I created a format file:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR=";" />
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="4" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR=";" />
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="24" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="14" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="4" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="4" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR=";" />
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="120" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="17" xsi:type="CharTerm" TERMINATOR=";" />
  <FIELD ID="19" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="128" COLLATION="Latin1_General_CI_AS"/>
</RECORD>
 <ROW>
  <COLUMN SOURCE="6" NAME="Municipio" xsi:type="SQLNUMERIC" PRECISION="18" SCALE="0"/>
  <COLUMN SOURCE="17" NAME="Placa" xsi:type="SQLNCHAR"/>
  <COLUMN SOURCE="3" NAME="Categoria" xsi:type="SQLNCHAR"/>
 <COLUMN SOURCE="8" NAME="Taxa" xsi:type="SQLNCHAR"/>
  <COLUMN SOURCE="1" NAME="Autenticacao" xsi:type="SQLNCHAR"/>
  <COLUMN SOURCE="2" NAME="Chassi" xsi:type="SQLNCHAR"/>
  <COLUMN SOURCE="4" NAME="Cor" xsi:type="SQLINT"/>
  <COLUMN SOURCE="5" NAME="MarcaModelo" xsi:type="SQLNUMERIC" PRECISION="18" SCALE="0"/>
  <COLUMN SOURCE="7" NAME="TipoServico" xsi:type="SQLINT"/>
  <COLUMN SOURCE="19" NAME="Destino" xsi:type="SQLNUMERIC" PRECISION="18" SCALE="0"/>
  <COLUMN SOURCE="9" NAME="TipoVeiculo" xsi:type="SQLNCHAR"/>
 </ROW>
</BCPFORMAT>

Please also note that fields are not ordered.

My command line is

 bcp [dbname].dbo.dbTable in input.txt -f format.fmt -T

I am truly and hopelessly lost... If anyone can point me in the right direction, I would really appreciate it.

BULK INSERT fails as well.

Thanks in advance,


texts:
I think I just found the coolest thing every with SQL Server SMO and PowerShell. I was asked to get a script together to find the owner of every object in an instance of SQL Server. I found a script on Technet/MSDN SQL Server library but thought it would be a lot easier to get it with PowerShell and be able to hit all the instances on the network at once.

Well in playing around I came across this method within the Database class: Discover()

So if you use this code:

#Load SMO
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
$srv = New-Object 'Microsoft.SqlServer.Management.Smo.Server' "your instance name"

$db = $srv.Databases

$db.Discover()

When you run that last line you are going to see your console start scrolling text like it is on drugs, if you connect to an instance with a lot of databases on it.

Well I went to do  $db | gm  and it pretty much shows you every TypeName available within SMO objects.

Now my question. I can execute $db.Discover() | Select Name, Owner and it will start spitting out everything with a name and an owner value.

How could I also get the object type of the object being returned (like if it is a trigger, schema, table, etc)? I can't seem to figure that out so far.

EDIT

So far I find that it seems to only "discovery" things on the database level. Apparently somewhere in my console cache I had set  $db to something like $db = ($srv.Databases).Item("aDatabase"), cause this is the only way you can call the method. Still looking for a way to pull out Object Name, type, and Owner though.


texts: Migrating SSRS 2008 to 2016 - ReportSync cannot see Data Sources

texts:
I am trying to migrate our SSRS 2008R2 server to a fresh install of SSRS 2016.

I am using ReportSync and have managed to get connect to both servers OK. I created a test folder with a test report on the 2008 server and have migrated that fine to the 2016 server.

The problem is arising when I am trying to migrate the Data Sources folder.

As you can see from the screen shot, there is not plus symbol next to this folder, suggesting it is empty, yet when I browse the folder using the Reports Manager in Internet Explorer, I can see the Data Sources there. Furthermore, we have a Data Source in the Datasets folder (don't ask!) and when I expand that folder out in ReportSync, I cannot see this Data Source.

The credentials I have used in the ReportSync tool are the same as I am browsing the webpage with and this is a domain account. The account is in the BUILTIN\Administrators group which has "Content Manager" role on the Data Sources folder


texts: Index Maintenance for DW- DB ( ETL process )

texts:
I'm planning to set up a t-sql job to perform index maintenance in a DW DB (Stores data through an ETL process) currently overnight and eventually will need to be run more frequently as the business grows.

The question is, which considerations should be taken in order to have an efficient Index Maintenance for an DW DB that usually loads data from an ETL process.

Currently I have in mind to implement the MP used by Ola Hallengren

T-SQL Code Below:

EXECUTE dbo.IndexOptimize
@Databases = 'StackOverflow2010',
@FragmentationLow = NULL,
@FragmentationMedium  = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 70,
@UpdateStatistics = 'ALL'

If you have any additional comments or suggestions will be very appreciated


texts: Testing query speed in two different RDBMS

texts:
I'm planning to compare the speed of queries in PostgreSQL and SQL server (on the same datasets). I was planning to run the same queries multiple times in both databases and use average response time in miliseconds as a rough estimate.

This seems a little too basic. Is there a better way to compare a speed of a query in two different database systems running on the same hardware? Any tools that I can use?


texts: How can I drop all triggers in a single database?

texts: I have a database with 104 triggers, is there a way to delete all the triggers with a single command from a single database called 'system_db_audits?


texts: Db2 high availability Instance configuration db2haicu fails

texts:
Hi I Configured db2 HADR between two servers and the servers are running in HADR Primary and Standby are syncing up

I tried to configure the instance configuration utility db2haicu on both the servers

when i tried to run the db2haicu i get the Following log Errors

2014-07-20-21.56.27.547634+330 E5186198E907          LEVEL: Error
PID     : 21580                TID : 47378715596352  PROC : db2haicu
INSTANCE: db2inst1             NODE : 000
HOSTNAME: testServer213.int.in.nmc.domain.se
FUNCTION: <0>, <0>, <0>, probe:2467
RETCODE : ECF=0x90000544=-1879046844=ECF_SQLHA_CREATE_CLUSTER_FAILED
  Create cluster failed
DATA #1 : String, 61 bytes
libsqlha: sqlhaCreateDomain() call error from wrapper libra ry
DATA #2 : String, 394 bytes
Line # : 15864---2632-044 The domain cannot be created due to the following errors   that were detected while harvesting information from the target nodes:
testServer265: 2610-441 Permission is denied to access the resource class specified in this command.
Network Identity UNAUTHENT requires 's' permission for the resource class IBM.PeerDomain on node testServer265.int.in.nmc.domain.se.
; FFDC ID:
DATA #3 : signed integer, 4 bytes

21


texts: Encrypted Query locking server from time to time

texts:
We have a system that runs without problems for days, but from time to time we see that all the queries do not run at all, and at the same time the SQL server is consuming too much CPU

So when we execute the following query to get all the running queries on the server:

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

We see the following running query:

TEXT    session_id  status      command     cpu_time    total_elapsed_time
NULL    26          background  INSERT      494039      1362613

Executing sp_who2:

SPID    Status      Login   HostName    BlkBy   DBName  Command CPUTime DiskIO  LastBatch       ProgramName SPID    REQUESTID
26      BACKGROUND  sa        .           .     msdb    SELECT  504257  4152076 04/26 12:16:13              26      0

So, how can I find what proccess, job, task, or whatever is executing this query and what exactly it is doing?

Tks in advance.


texts: SQL Server Always On Node and File Share Majority

texts:
I am looking for some guidance on architecting our SQL Server 2016 SP1 Always On Availability Groups HADR solution on Windows Server 2012 R2. We have a primary site A with a primary replica and a secondary replica and a disaster recovery (DR) site B with a secondary replica and a file share witness. Our goal is that if the primary replica server 1 at site A is down, the Always On Availability Group (AG) fails over to the secondary replica server 2 at site A, and if both servers at site A are down, the AG fails over to site B.

We are attempting to do a Node and File Share Majority configuration per https://technet.microsoft.com/en-us/library/cc731739(v=ws.11).aspx and this diagram:

This diagram shows that when one node and the "disk" / file share witness are in communication the cluster runs, but in our testing of this situation, the cluster fails due to a loss of quorum of the WSFC. The automatic AG failover works if we test a failure of one server at a time by disabling the NIC in vmWare since SQL Server 2016 supports two automatic failover target replicas. However, it does not work if we fail both servers at site A at the same time to simulate a point-to-point network failure or a site power failure.

The following approach with manual intervention to force the quorum will work but it is not automatic, which is what we want in the ideal situation:

$node = "SQLServerC"
Stop-ClusterNode –Name $node
Start-ClusterNode –Name $node –FixQuorum

ALTER AVAILABILITY GROUP SQLServerAO FORCE_FAILOVER_ALLOW_DATA_LOSS;

$node = "SQLServerC"
Stop-ClusterNode –Name $node
Start-ClusterNode –Name $node

I appreciate any suggestions you can offer and thank you in advance!


texts: Is there any ways to retrieve datatable in row field in mysql database

texts:
I want to retrieve data table in row field has fetch from database. Simply, one to many relationship in result set from stored procedure.

Table 'car'

Id  Car
1   BMW
2   Mercedes
3   Audi

Available in many color

Table 'CarColor'

Id CarId    Color
1   1     Metalic White
2   1     Acrylic White
3   2     Glossy Red
4   2     Glossy White
5   3     Frosty Black
6   3     Frosty White

Consequently, I want result set from stored procedure like

Id  Car       CarColor
1   BMW      'CarColor Datatable' (i.e. Datatable of CarColor table relevant row)
2   Mercedes 'CarColor Datatable' (i.e.  Datatable of CarColor table relevant row)
3   Audi     'CarColor Datatable' (i.e.  Datatable of CarColor table relevant row)


texts: COUNT beginning of, mid of & end per month for many years

texts:
In my last post PostgreSQL: How to get a weekly average over a year including 'gap' weeks per animal from years of data? with the fiddle http://sqlfiddle.com/#!17/0bff3/1
I got a weekly count. But it was difficult to graph.

I decided to go a different route. doing the month is 3 parts (beginning, middle and end)
My new objective: (and I won't graph this)

|animal|start             |peak      |end        |
|dog   |beginning of may  |mid of may|end of june|
|cat   |beginning of march|end of may|mid of june|

I was thinking of using a case like structure to assign a part of the month... something like the following...

SELECT animal, count(*),
min(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as start,
max(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as end
    FROM brachyopa_cleaned bc
    AND concat(from_month,'/',from_day) not in ('12/31','01/01','1/1') -- dates where month and day are unknown
GROUP BY animal
ORDER BY aminal

... and then find per species per month section the amount of occurrences to find the month section (there are in fact 12x3 'mm-dd' sections in a year) with the highest count.

Can someone give me some pointers how to do this?

[EDIT1: Changed the SELECT in what I am using so far, now I need somehow to find the peak, the 10 days period where there is the max count]
[EDIT2: rewrote the ...per specie per month... section]


texts:
I have a stored procedure that takes collection of objects. (TABLE OF MyCustomType). Inside the procedure I'm trying to join this parameter with real tables.

For example, something like

create or replace TYPE MYTYPE AS OBJECT (....);
CREATE OR REPLACE TYPE LIST_OF_MYTYPE AS TABLE OF MYTYPE;

CREATE PROCEDURE FOO(my_table LIST_OF_MYTYPE ,....) AS
  CURSOR cur_read_data IS
  SELECT a.col1, b.col2, b.col3
  FROM
  TABLE(FOO.my_table) a
  INNER JOIN existing_table b ON (b.existing_table_id = a.existing_table_id)
  --b.existing_table_id - primary key supported by unique index
  ORDER BY a.existing_table_id
  ;

BEGIN
     FOR record_info in cur_read_data
     LOOP
        ......
     END LOOP;
END;

It works, but I have a performance issue. Collection parameter passed to the procedure doesn't have many elements; even if it has just one element, execution plan involves full table scan of existing_table.

In case of 1 element, changing INNER JOIN existing_table b ON (b.existing_table_id = a.existing_table_id) to INNER JOIN existing_table b ON (b.existing_table_id =FOO.my_table(1).existing_table_id ) makes a huge difference - the query uses "INDEX UNIQUE SCAN" as I expected it for the initial query. I have tried even query hints (ordered, leading) with no results...

Even though the number of elements in collection in my application is between 1 and 5, and it's possible to write 5 different versions of one procedure, I wonder if it's possible to make it work as expected.

For testing, I also did

CURSOR cur_read_data IS
  SELECT a.col1, b.col2, b.col3
  FROM
   (
     SELECT 1 as existing_table_id, 'test 1' as col1 FROM DUAL
      UNION
     SELECT 2 as existing_table_id, 'test 2' as col1 FROM DUAL
      UNION
     SELECT 3 as existing_table_id, 'test 3' as col1 FROM DUAL
   )
  a
  INNER JOIN existing_table b ON (b.existing_table_id = a.existing_table_id)
  --b.existing_table_id - primary key supported by unique index
  ORDER BY a.existing_table_id

That way it also works as expected, INDEX_UNIQUE_SCAN, not full scan...

Thanks for your answers.

UPDATE
Quite surprisingly, but rewriting it to

CURSOR cur_read_data IS
  WITH CTE1 AS (SELECT * FROM TABLE(FOO.my_table))
  SELECT a.col1, b.col2, b.col3
  FROM
  CTE1 a
  INNER JOIN existing_table b ON (b.existing_table_id = a.existing_table_id
  AND b.existing_table_id IN (SELECT existing_table_id FROM CTE1))
  ORDER BY a.existing_table_id

reduced cost about 30 times (1200 original version vs 38 with WITH) which is
 acceptable, but I still have no idea why that helped...

Another update

Analyzing V$SQLSTATS reveals that for the first version of the cursor it avoids DISK READS at any cost (0), DIRECT WRITES is also 0; using WITH somehow changes execution plan resulting in huge improvement in CPU TIME which outweighs increased DISK READS (1)...


texts: pg_restore: [archiver] did not find magic string in file header: please check the source URL and ensure it is publicly accessible

texts:
I have been trying to push a dump file from my local Postgrel DB (which I uploaded into my Google Drive and is accessible to public) into my Heroku remote DB with the following URL:

heroku pg:backups:restore 'https://drive.google.com/open?id=dump_id_link_here' DATABASE_URL

I am already logged in into my Heroku app from the terminal on which I run the command.
but I got the same error twice. I have been searching online and found threads such as pg_restore: [archiver] did not find magic string in file header but I could not help link between the two, since I am very new two Postgrel. I Hope you guys point me out towards the issues. Very much appreciated.

Starting restore of https://drive.google.com/open?id=dump_id_link_here to postgresql-symmetrical-52186... done

Stop a running restore with heroku pg:backups:cancel.

Restoring... !
▸    An error occurred and the backup did not finish.
▸
▸    waiting for restore to complete
▸    pg_restore finished with errors
▸    waiting for download to complete
▸    download finished with errors
▸    please check the source URL and ensure it is publicly accessible
▸    Run heroku pg:backups:info r002 for more details.

=== Backup r002
Database:         BACKUP
Started at:       2019-09-14 21:14:26 +0000
Finished at:      2019-09-14 21:14:27 +0000
Status:           Failed
Type:             Manual
Backup Size:      0.00B (0% compression)

=== Backup Logs
2019-09-14 21:14:27 +0000 pg_restore: [archiver] did not find magic string in file header
2019-09-14 21:14:27 +0000 waiting for restore to complete
2019-09-14 21:14:27 +0000 pg_restore finished with errors
2019-09-14 21:14:27 +0000 waiting for download to complete
2019-09-14 21:14:27 +0000 download finished with errors
2019-09-14 21:14:27 +0000 please check the source URL and ensure it is publicly accessible


texts:
i have two joined table in my postgres db

tb_items and tb_colors

id_item | group |id colors
--------------------------
1        a         1
2        b         1
3        b         2
4        c         4
5        c         5

id_color | color| subcolor
---------------------------------
1          black        var1
2          red          type1
3          red          type2
4          blue         aaa1
5          blue         xxx3
6          red          hex1

that mean in my query i have

id_item | group | color | subcolor
----------------------------------
1         a       black    var1
2         b       red      type1
3         b       red      type2
4         c       blue     aa1
5         c       red      hex1

basically what i want is renaming color name based on numers of color item in a groups  or

 id_item | group | color
----------------------------------
 1         a       black
 2         b       red
 3         b       red-1
 4         c       blue
 5         c       red

i dont' really know whato to do..


texts: Should login id and password be stored in separate table

texts:
I am working on my academic project. I have a table for employee records which have employee id, joining date, designation and other fields. I am trying to implement a scheme in which when record of employee is added first time in DB, a user name and temporary password will be generated so that New employee can use it to login in DB to see his details.
I need to ask if it is good practice to save login id and password in employee record table? Or should I store them separately, but then I think I can not generate temporary password and id at time of inserting employee data in DB.
Or is there any other way to implement this scheme?

Edit: I understand that I should never save clear text password in DB and should use salt/hash. However I am looking for a way that above task can be done in one action, i.e If I make a separate table for user id and password then how can I am able to enter employee details in employee table and generate user id / password for user and store in from separate table in one action.


texts: MongoDB page faults even when all data is in resident in RAM

texts:
I have made a sample app that emulates what my real one does. It simply iterates over all documents of a specific collection over and over.

I start mongo (as a windows service) and mongostat shows almost no resident memory.

insert  query update delete getmore command flushes mapped  vsize    res faults  locked db idx miss %     qr|qw   ar|aw  netIn netOut  conn       time
*0     *0     *0     *0       0     1|0       0      6.03g  6.12g    71m      0 local:0.0%          0       0|0     0|0    62b     3k     3   09:35:11

I use the touch command on the single collection I have (which is about 2GB in size) and mongostat shows:

insert  query update delete getmore command flushes mapped  vsize    res faults          locked db idx miss %     qr|qw   ar|aw  netIn netOut  conn       time
*0     *0     *0     *0       0     5|0       0      6.03g  6.12g   2.1g 427937 HamsterSchool:0.0%          0       0|0     0|0   408b     4k     3   09:39:59
*0     *0     *0     *0       0     1|0       0      6.03g  6.12g   2.1g      0         local:0.0%          0       0|0     0|0    62b     3k     3   09:40:00

Which seems as though the entire collection is now resident in memory, but when I run the sample app, no matter how long, there are always a lot of page faults:

insert  query update delete getmore command flushes mapped  vsize    res faults          locked db idx miss %     qr|qw   ar|aw  netIn netOut  conn       time
*0     68     *0     *0       0     1|0       0      6.03g  6.12g   2.1g  35616 HamsterSchool:0.0%          0       0|0     1|0     6k    74m     4   09:43:15
*0     68     *0     *0       0     4|0       0      6.03g  6.12g   2.1g  36068 HamsterSchool:0.0%          0       0|0     0|0     6k    75m     5   09:43:16

So, why are there any page faults at all if everything was already "touched"?. It's important to note that mongo runs on a 32GB RAM 64bit windows machine and 27GB of RAM is free at all times.


texts:
Is it possible to count even deleted rows in a SQL Server table?

Let's say we have a table Contacts.
I'll add 5 new contacts, and hit count on it. It should return value of 5.
Now, when I delete a contact and run the count it will return 4.

Since I'm working on an app who generates a unique number (Count-year-NP) from the count, it's crucial for me that it's universal.

I don't just count the database, my query is:

SELECT COUNT(*) FROM some_table WHERE YEAR(now) = YEAR(GETDATE())

It counts data where year is current.

Is there any way using stored procedure or simillar, just to keep the count universal for deleted and inserted rows?

I was thinking of marking rows as deleted instead of deleting them, but  I want to be able to delete the values.


texts: How to give a user "create any database" permission in SQL Server?

texts:
Is it different from the dbcreator permission?

Can anybody explain the process?


texts: SQL Query in suspended state causing high CPU usage

texts:
Environment detail: SQL Server 2008 R2. I have applications running on JBoss that access the DB using JDBC driver.

Issue: My online DB server was experiencing high CPU usage.

Using the following query I was able to determine there are 2 queries (Select command) in suspended status (wait type - CXPACKET) for nearly 10 minutes and going on. I believe this is the cause of high CPU usage.

 USE MASTER GO

 SELECT scheduler_id ,
        runnable_tasks_count ,
        pending_disk_io_count
 FROM   sys.dm_os_schedulers
 WHERE  scheduler_id < 255

 SELECT qs.percent_complete ,
        qs.session_id ,
        scheduler_id ,
        blocking_session_id ,
        qs.status ,
        command ,
        wait_time ,
        wait_type ,
        last_wait_type ,
        wait_resource ,
        ST.text ,
        host_name ,
        program_name /*
 ,SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
     (    (CASE statement_end_offset
           WHEN -1 THEN DATALENGTH(st.text)
           ELSE QS.statement_end_offset END - QS.statement_start_offset  )/2 ) + 1) AS statement_text   */   --, qp.*
 FROM   sys.dm_exec_requests qs
        LEFT JOIN sys.dm_exec_sessions es ON ( qs.session_id = es.session_id )
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS ST
 --     CROSS APPLY sys.dm_exec_query_plan(QS.sql_handle) as qp where qs.session_id = 50 and qs.session_id != @@SPID

 SELECT CASE WHEN Requests.sql_handle IS NULL THEN ' '
             ELSE SUBSTRING(Statements.text,
                            ( Requests.statement_start_offset + 2 ) / 2,
                            ( CASE WHEN Requests.statement_end_offset = -1
                                   THEN LEN(CONVERT(NVARCHAR(MAX), Statements.text))
                                        * 2
                                   ELSE Requests.statement_end_offset
                              END - Requests.statement_start_offset ) / 2)
        END AS StatementText ,
        QueryPlans.query_plan AS QueryPlan ,
        Statements.text AS Batch_Text ,
        Sessions.session_id ,
        Sessions.Login_Name ,
        Sessions.Host_Name ,
        Sessions.Program_Name ,
        Sessions.Client_Interface_Name ,
        Requests.wait_time ,
        Requests.cpu_time ,
        Requests.total_elapsed_time ,
        Requests.reads ,
        Requests.writes ,
        Requests.logical_reads ,
        Requests.row_count ,
        Requests.granted_query_memory * 8 / 1024 AS Granted_Query_Memory_MB ,
        LEN(Statements.text) AS Batch_Text_Length ,
        Requests.statement_start_offset / 2 AS Statement_Start_Offset ,
        CASE WHEN Requests.statement_end_offset = -1
             THEN LEN(CONVERT(NVARCHAR(MAX), Statements.text)) * 2
             ELSE Requests.statement_end_offset
        END / 2 AS Statement_End_Position ,
        ( CASE WHEN Requests.statement_end_offset = -1
               THEN LEN(CONVERT(NVARCHAR(MAX), Statements.text)) * 2
               ELSE Requests.statement_end_offset
          END - Requests.statement_start_offset ) / 2 AS Statement_Text_Length
 FROM   sys.dm_exec_session Sessions
        INNER JOIN sys.dm_exec_requests Requests ON Sessions.session_id = Requests.session_id
        CROSS APPLY sys.dm_exec_sql_text(sql_handle) Statements
        CROSS APPLY sys.dm_exec_query_plan(plan_handle) QueryPlans
 WHERE  Sessions.session_id != @@SPID

 SELECT *
 FROM   sys.dm_exec_requests
 WHERE  blocking_session_id != 0

Any suggestions on how I can kill these queries in suspended state?


texts:
Is there any way to temporarily enforce columnar uniqueness on items currently being inserted?  For instance, I'm splitting payments and charges to their own tables from a previously unified "transactions" table.  Of course, the new tables have their own autoincremented ids and won't be cueing off the previously existing "transaction" ids so I was going to create a unique key for each transaction (based on an MD5 of the previous transaction_id and transaction_title) and use it as the new title.  Basically the pseudocode of the PHP/SQL would go something like this:

$old_payments = getOldPayments();  //DON'T WORRY ABOUT THIS, IT WORKS

foreach($old_payments as $payment){
    $unique_md5_info = md5($payment['transaction_id']."_".$payment['transaction_title']);
    $query = sprintf("INSERT INTO payments (title, amount, due_date) VALUES('%s','%s','%s') WHERE title <> '%s'",$unique_md5_info,$payment['amount'],$payment['due_date'],$unique_md5_info);
}

Obviously, in a perfect world we'd only need to run this once and be done.  However there are groups on multiple servers that won't be moving over at the same time so, in order to accommodate the staggered move, we'll have to run the query on different dates while being sure not to import any duplicate transactions.

Anyway, I'm probably seriously overthinking this issue, but hopefully someone has a reasonable solution.

Best!


texts: Large number of row deletes fails with 'out of log space'

texts:
I know this is a common question, but I'm at a loss how to fix it.

Table has an indexed date column so I'm using that to delete one day at a time, oldest first. Within the loop that does each day there's another loop that deletes the TOP 1000 rows at a time, the inner, 1000 row, loop is wrapped in BEGIN/COMMIT TRAN block and the outer, daily, loop has a CHECKPOINT statement at the end. As far as I can tell this should mean that each 1000 rows is deleted in a transaction and after each day a CHECKPOINT is issued.

The statement fails with:  The transaction log for database 'database' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Checking sys.databases I can see the reason is 'CHECKPOINT' (which is why I added the CHECKPOINT statement to the query). The database is using the SIMPLE recovery model, so as far as I can see this shouldn't be happening. It's also rolling back everything when it fails, because I can see the oldest record's date isn't changing.

I've checked there are no long running transactions locking the log, but I'm wondering if it might be because the db used to be log-shipped (it was moved here from a different server and is no longer). Maybe the transactions are marked for replication, but I don't know how to tell.

Does anyone have any suggestions?

My statement logic looks like this:

@NoOfDays = -10 (for example, with 10 days data to delete)
while @NoOfDays < 0
Begin
    While (1=1)
    Begin
        Begin Tran
        Delete 1000 rows that are older than @NoOfDays
        End Tran
        If @@Rowcount < 1000 Break
    End
    Checkpoint
    @NoOfDays += 1
End


texts:
I'm building a new logging system for our company. I'm wondering about the best way to store about 50-100GB data each year.

I'm looking at both MySQL and MongoDB. I'm considering MongoDB because it looks easy to expand to lots of data and it works pretty fast. I'm considering MySQL because I've used it before.

We have a log database today in MySQL which uses InnoDB and foreign keys. It has over 200GB data. It's not working well and the database crashes whenever I try to remove anything from it. I'm not the one who built it, I'm just trying to fix the problem.

I hope people out there know a good way to log this amount of data. I just need a reliable way to store and a simple way to query. I'm open to other options.

The logs are generated on separate servers, gathered to a central log server and imported. I'm building an API to access them when we need to query them.


texts: How to retrive deletes Mysql table data back?

texts:
I accidently delete data from mysql but i want it back.
I am fedora operating system is use for installing Mysql.

Please help ASAP because data is very critical


texts: INSERT INTO selected columns creates new column from JOIN

texts:
I have an INSERT INTO ... SELECT.. query that uses a JOIN on two tables. The results of the query should take a subset of the information and insert it into an existing table. That part works, but the query also creates additional columns in the existing tables to (what appears to be) accommodation for the JOIN... ON statements. Simplified example follows:

TableA

UID   Text_GUID
1     abc123
2     abc345
3     abc456
4     abc678

TableB

Text_GUID   Col_A   Col_B   Col_C   Col_D
abc123       100     200     300     400
abc345       111     211     311     411
abc456       122     222     322     422
abc678       133     233     333     433

Execute the following query:

INSERT INTO [TargetTable] (UID, Col_A, Col_B, Col_C, Col_D)
SELECT A.UID, B.Col_A, B.Col_B, B.Col_C, B.Col_D
FROM [TableA] AS A
INNER JOIN [TableB] AS B
ON A.Text_GUID = B.Text_GUID;

I expect this:

TargetTable

UID   Col_A   Col_B   Col_C   Col_D
1       100     200     300     400
2       111     211     311     411
3       122     222     322     422
4       133     233     333     433

I get this:

TargetTable

UID   Col_A   Col_B   Col_C   Col_D   Text_GUID
1       100     200     300     400     NULL
2       111     211     311     411     NULL
3       122     222     322     422     NULL
4       133     233     333     433     NULL

A new column is being created with the column name of the JOIN. Each row value for the new column is inserted with a NULL. I don't get it. First of all I don't want the new column created, second I have no idea why the new column is populated with NULL (not that it matters if it isn't created, but I don;t understand why it isn't populated with the value of the JOIN... ON statement if it is created.

My understanding was that by specifying the target column names with the appropriate SELECT I should only INSERT into the appropriate columns.

When I do this with a statement that has more than one criteria for the JOIN, two columns (or more) get created. For example, if the JOIN... ON statement was something like the following:

ON A.Text_GUID = B.Text_GUID AND A.Another_Col = B.Another_Col

I would end up with:

TargetTable

UID   Col_A   Col_B   Col_C   Col_D   Text_GUID   Another_Col
1       100     200     300     400     NULL        NULL
2       111     211     311     411     NULL        NULL
3       122     222     322     422     NULL        NULL
4       133     233     333     433     NULL        NULL

This particular query runs against anything from 150 million rows to 8 billion rows, so if I can avoid it I do not want to put everything into a temporary table first as this is part of a migration from a rather poorly designed structure to a far more normalized one (where ID's are int's instead of text GUIDs for one thing).

Server is MS SQL Server 2008.

Any and all help would be very much appreciated. Thanks.