texts: Heterogeneous queries error using stored procedure

texts:
Stored procedure

ALTER    PROCEDURE Proc_DayShift
AS
insert into #Punching_History SELECT * FROM SERVER2.MARS.DBO.TABLE1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
SET ANSI_WARNINGS OFF
GO

The above stored procedure is showing error as

  Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query
  semantics. Enable these options and then reissue your query.

I added the server1 from server2 using linked server, when I run the query like this

SELECT * FROM SERVER2.MARS.DBO.TABLE1

It's executing and showing results.

When I run the query using stored procedure, it is showing this error message.

How to rectify this issue.  what wrong in my query...?


texts: New table row autopopulated with data from other rows?

texts:
I am in the middle of developing a simulated horse game using PHP and databases. Apparently I have just enough knowledge to get into trouble with my grand ideas! I'm looking for help/tips on my current task, creating new horse stats based on currently registered ones in a database.

I have a horse table containing data similar the following:

CREATE TABLE horse(id int, name text, stat1 int);
INSERT INTO horse VALUES
  (1,'sparky',20),
  (2,'spunky',10);

I want to create a new horse using the average of the horse 1 & 2's values contained in stat1. So horse 3 would have a stat1 value of 15 that would be automatically calculated.

Is this possible using PHP? Or can someone point me in the right direction?


texts:
I'm trying to set up a linked server between two instances we have on our network. I've done this many times before without an issue, but this time I repeatedly get the following error:

OLE DB provider "SQLNCLI10" for linked server "MYLINKEDSERVERNAME" returned message
    "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Procedure tableName_InsertUpdate, Line 57
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked
    server "MYLINKEDSERVERNAME" was unable to begin a distributed transaction.

I've got server 'A', a SQL2008 R2 box and server 'B' a SQL2012 box as part of a cluster.

I've setup the linked server on server A and I can run a simple select * from [linkedserver].dbname.dbo.table which works correctly.

What isn't working for me, is a a trigger on a table on DB 'A' which is activated from an SP on server A attempting to insert into a table on DB 'B'.

Has anyone got any ideas as to why a query would work, but a trigger fired from an SP wouldn't?


texts:
Our first and current approach :

      Current Replication configuration is Active - Active
      and R/W is split on both node . This is asynch-replication.

      Primary node is having RW and Secondary Node is Read only

      Both nodes are having dedicated storage node.

Now our second approach is to have single SAN for both nodes .

   In this approach , Primary node will point to SAN and whatever data being
   written in data directory of SAN , will get reflected in SN.
   This SAN has a redundancy that whenever it gets down,
   other SAN will come in picture .

   This will eliminate the usage of 1 storage drive and hence reducing cost.

Now can anyone tell which is the best approach here with various justified reason.

My Server configuration is :

RHEL 7.2
48 GB RAM
8 CORE CPU
MySQL 5.7 enterprise edition

EDIT

In SAN with data directory shared , only one node can stay alive and other node will be dead. So replication is no longer required.


texts: Is There A Way To Tell How Many Times A Query Has Executed?

texts: I am attempting to determine the number of times a particular query has executed in the course of a day. Is there a simple script that I can run to determine this?


texts: Why get queryplans recompiled every minute

texts:
I have a server running SQL Server Enterprise 2016 SP2 and I noticed that every minute several hundred queryplans get invalidated.
I run sp_BlitzCache @expertmode=1 to check the 'Created At'-column to see when the plan is generated.
Every minute about 500 plans are re-created and this continues the entire day and it are always different plans that get recompiled.

When I use the DMV sys.dm_db_index_usage_stats to check the user_updates I see that there happen between 10.000 and 1.000.000 updates on the indexes.
Some tables have more than 10.000.000 records but most of them have between 100.000 and 2.000.000 records.

As far as I know a queryplan gets invalidated in following circumstances:

Modifications made to table/view definition by ALTER TABLE/ALTER VIEW
Changes made to any indexes used by execution plan
Updates to the statistics used by the execution plan (manual or automatically)
Dropping an index used by queryplan
sp_recompile / with recompile
Large numbers of changes to the table
Adding/dropping a trigger on a table used by the execution plan
Memory pressure

All the queries use KEEP PLAN and KEEPFIXED PLAN, so I think automatic or manual update of the statistics will not invalidate the plan. This is a third-party application so I can not alter the queries.
When I run UPDATE STATISTICS [TableTest] WITH FULLSCAN I don't see a recompilation of the queries that use that table.

Next I used a script by Jonathan Kehayias to check if there was memory pressure but the script only returned RESOURCE_MEMPHYSICAL_HIGH so I do not think this is the problem.

No modification to the table definition, indexes happened nor was the query executed with sp_recompile / with recompile

Is there something else that can cause invalidation of a queryplan?

Update:
After some more research I think the recompiles are triggered by memory pressure on the plancache. This article gives a lot of information about the plan cache internals. This is about SQL Server 2005 but hopefully this still applies to SQL Server 2016.
According to this article you can calculate the pressure limit based on the total amount of visible target memory.
My research advances slow because it is hard to find info about pressure on the plan cache.
I'll keep you posted on the progress


texts:
I have a development machine with a new database I have designed. The server has a login L1 and the database has a user U1. L1 is mapped to M1 and tables have permissions set up for U1. Authentication is SQL Server authentication. I wanted to fully backup the database and restore to the production server so that I could start to use it in production.

I had a problem with user U1: I could not map it to any login on the production server. So I could not use U1 with any login. Is this correct behavior?


texts: Is there a way to reference a column dynamically within a trigger?

texts:
I asked a similar question a few months back and I'm improving upon my solution!

I have an Oracle database where I would like to trigger a procedure to do additional work on a column upon updating of another.

For example, say that if I mark an item on sale, I would like the database to automatically take 20% off the price.  So:

| ID | ITEM                     | PRICE | ONSALE | DISCOUNT1 | DISCOUNT2 |
|----|--------------------------|-------|--------|-----------|-----------|
| 1  | T-Shirt                  | $20   | F      | .15       | .3        |
| 2  | Sweatshirt               | $30   | F      | .25       | .5        |
| 3  | Jeans                    | $40   | F      | .1        | .4        |
| 4  | Fashionably Ripped Jeans | $120  | F      | .05       | .1        |

What if I wanted the trigger to, say, programmatically select DISCOUNT1 or DISCOUNT2, how would I do this?

:NEW.PRICE := :NEW.PRICE -
    (:NEW.PRICE * :NEW.DISCOUNT(do tricky math to get a random number));


texts: MongoDB specifying shard key and sharding strategy

texts: What is the syntax to state that a collection is to be sharded as location based, while using the range based sharding strategy. I know you have to use the the sh.shardCollection() command, but what arguments do I use in it?


texts: Unable to install SQL named instance as it says RS feature exist, even after dropping it

texts:
We are trying to Un-install a named instance from one of our servers.

But, don't know why it did not un-installed the reporting services even after proper selection was made during uni-install.

Please see the below screenshot what I can see:

But below does not show me any such feature to uninstall

Moreover, we can see reporting services as running in sql server configuration manager.

Please help what could be possibly missing here?


texts:
I amb trying to do a full database backup from one server to restore into another one, this is the backup script (creating a dated name for the backup).

declare @DB sysname
declare @DISK sysname
SET @DB = 'DBNAME'
SET @DISK = 'L:\' + @DB + '_'+convert(varchar, getdate(), 112)+'.bak'
BACKUP DATABASE @DB
TO  DISK = @DISK
WITH COMPRESSION, COPY_ONLY, STATS = 5
GO

And this is the restore one (restoring from the previously created file):

declare @DB sysname
declare @DISK sysname
SET @DB = 'DBNAME'
SET @DISK = 'L:\' + @DB + '_'+convert(varchar, getdate(), 112)+'.bak'
-- #commented until needed (dangerous)
SELECT @DISK
--EXEC('ALTER DATABASE '+ @DB +' SET OFFLINE WITH ROLLBACK IMMEDIATE')
--EXEC('DROP DATABASE '+ @DB)
-- #show 1% fractions as it is restoring
--RESTORE DATABASE @DB FROM DISK = @DISK WITH REPLACE, STATS = 1
GO

Both scripts run without error messages, but the developers are saying that they are missing some VIEWs from the restored database. About those views:

Some of them use linked servers, some don't
The linked server in the DEST server has a different name that the one in the source (for those using linked servers).

I saw this about clr, but doensn't seem related.

Any ideas on what might be failing?
UPDATE1: After asking averyone, on of the devs told me that they did a failed update after my restore that deleted the views I was missing, my procedure was fine.
UPDATE2: I accepted Aaron's reply because altough it wasn't the cause in my case, it's nice addon to the backup script.


texts: Recommendations around managing large amounts of data that needs to be aggregated (SUM)

texts:
I am looking for alternatives to redesign a part of our system that needs to perform cumulative SUMs over large data sets of rows based on user and dates.

How the system works

We are running a web (.NET) loyalty program, so every time users performs a certain action (that in turn adds or subtracts points from their account), we are logging that in a transaction log table. That table has about 10 columns, but the (most) important ones are:

Operation (points earned, awarded or spent),
ItemDescription (a description of the action),
ItemAmount (self explanatory),
UserID and
Date.

Every time a user logs in and access their profile, they can see their transaction log history sorted by date, with the aggregated fact that we have a Partial Total column that needs to be calculated via the SUM of each item on the list. This transaction log can be filtered by date range, and has a paging feature.

They also see a cumulative of all the points they have earned/awarded or spent since the beginning of time.

We only run INSERTs and SELECTs on this table, and we are currently (after about 4 years of the system being running) in the 200 million range, and it is only going to get bigger fast. We have over 300k users registered, and we need the response to be fairly quick if possible (obviously there will be a middle layer of code to try to apply optimizations to the data querying). We are running SQL Server 2012 (11.0.7462.6).

Considerations

I am looking for suggestions on alternatives on how to  redesign this thinking ahead, so we don't have to worry about within the next five years or so.

Maybe using a NoSQL product?
What would be the limit to determine that SQL Server is no longer viable?
Maybe we just need to have our indexes optimized better? (which is also a topic of conversation because we need to keep the site up and running about 98% of time and index rebuild and defrag has an impact on performance)

Right now we are performing the partial calculations directly on SQL Server but we are thinking about moving that calculation over to the client side, however we are still very early on the design process to know if this is feasible.

Basically I wanted to ask the (much smarter) collective their thoughts on this matter, and see if anyone can point me on the right direction.

Thanks a lot for your time!


texts: RANK used in 'where' returns invalid column, but exists in results set

texts:
I basically have a table with date, timestamp, DID, coordinates.

I want a query that will return rows with the last coordinate from day X, and the first coord from day X+1, and the coordinates. So it will only return results where there are 2 consecutive dates.

This is what I came up with. Been trying to get this query to work, it's almost perfect, but I just need to add the commented out where conditions and it'll do exactly what I want. But when I uncomment, I get an error "column doesn't exist":

SELECT  a.timestamp_intersecting_date d1,b.timestamp_intersecting_date d2,
        a."DID",
        a.timestamp_intersecting_max t1, b.timestamp_intersecting_min t2,
        RANK () OVER (
          PARTITION BY a.timestamp_intersecting_date
          ORDER BY a.timestamp_intersecting_max DESC
       ) timestamp_d1_rank ,
        RANK () OVER (
          PARTITION BY b.timestamp_intersecting_date
          ORDER BY b.timestamp_intersecting_max ASC
       ) timestamp_d2_rank,
        a.coords_centroid, b.coords_centroid
FROM
    signals a
INNER JOIN signals b ON (a."DID" = b."DID")
WHERE (b.timestamp_intersecting_date = a.timestamp_intersecting_date + INTERVAL '1 DAY')
AND a."DID" = b."DID"
--AND timestamp_d1_rank = 1
--AND timestamp_d2_rank = 1
ORDER BY a."DID", t1 desc, t2 asc

How to solve this?


texts: mongodump download collections only if changed

texts:
If I get it right if to make mongodump in to directory that already contains previous dumped data it will still download full collection data even if is not different from data containing in the dir Though it could check the metadata. Is there a way to make it not download collection if it has not "changed" in terms I described?

Mongodb version 4.0.4


texts: Postgres/pgloader: setting a delimiter for multiple line text records

texts:
I wish to import a 5 gigabyte text file with pgloader.
The individual records are paragraphs of html. The records include carriage returns and white space. So this not an import where it is one record per line.
Reading the pgloader howto I get the impression I can import these records by using setting lines terminated by to a special character to demark the end of a record. The options is described as:

lines terminated by
Takes a single character as argument, which must be found inside single quotes, and might be given as the printable character itself, the special value \t to denote a tabulation character, or 0x then an hexadecimal value read as the ASCII code for the character.
This character is used to recognize end-of-line condition when reading the CSV data.

So, am I right?  Can I can import these records by using setting lines terminated by to a special character?
UPDATE: I had to get the project done, so I used some perl commands to modify the data to be one record per line. It's an undesired option because I wanted a multi-line text field.
But I still would like a way to do this. Something that allows a custom record-delimiter would probably be the answer.


texts: Can application_name be accessed from a trigger?

texts:
The PostgreSQL documentation lists a number of System Information Functions which provide state from the current connection, such as inet_client_addr() or pg_backend_pid(). I would like to create an audit trail for updates to a table which includes the application_name, but there does not seem to be a function for accessing this.

The application_name is visible from the pg_stat_activity view and is set by clients in the format of

username:progname@hostname

How can this information be accessed from a trigger?


texts: SSIS Execute Sql Task not executed on server

texts:
I have a package that has an Execute Sql Task.  When I run it in SSDT it works fine, the query is executed.  When I deploy it on the server and run the package, the query is not executed and there is no error in the execution report.  Even if I put "BLABLABLA" as the query, there is no fail.  The query is not dynamic.

I've started SQL Profiler while running the package and I can see that the Task goes through the Pre and Post Validation, but the query is never executed.

I've tried running the package on the server with my user with no luck.

Here's a screen shot of my package.

I've disable all other task to isolate the problem.

Thanks for your help


texts: PostgreSQL Serialized Inserts Interleaving Sequence Numbers

texts:
I have multiple processes inserting into a Postgres (10.3) table using the SERIALIZED isolation level.

Another part of our system needs to read these records and be guaranteed that it receives all of them in sequence. For example, in the picture below, the consumer would need to

select * from table where sequanceNum > 2309 limit 5

and then receive sequence numbers 2310, 2311, 2312, 2313 and 2314.

The reading query is using READCOMMITTED isolation level.

What I'm seeing though is that the reading query is only receiving the rows I've highlighted in yellow. Looking at the xmin, I'm guessing that transaction 334250 had begun but not finished, then transactions 334251, 334252 et al started and finished prior to my reading query starting.

My question is, how did they get sequence numbers interleaved in those of 334250? Why weren't those transactions blocked by merrit of all of the writing transactions being serialized?

Any suggestions on how to achieve what I'm after? Which is, a guarantee that different transactions don't generate interleaving sequence numbers? (It's ok if there are gaps.... but they can't interleave).

Thanks very much for your help. I'm losing hair over this one!

PS - I just noticed that 334250 has a non zero xmax. Is that a clue that I'm missing perhaps?


texts: mysqldump changes primary key to unique key?

texts:
While attempting to dump and restore a WordPress database in MySQL, I noticed that mysqldump seems to be changing the PRIMARY KEY on several tables to a UNIQUE KEY instead. For example, this tables:

mysql> describe wp_yoast_seo_meta;
+---------------------+---------------------+------+-----+---------+-------+
| Field               | Type                | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------+-------+
| object_id           | bigint(20) unsigned | NO   | PRI | NULL    |       |
| internal_link_count | int(10) unsigned    | YES  |     | NULL    |       |
| incoming_link_count | int(10) unsigned    | YES  |     | NULL    |       |
+---------------------+---------------------+------+-----+---------+-------+

Is dumped as:

CREATE TABLE `wp_yoast_seo_meta` (
  `object_id` bigint(20) unsigned NOT NULL,
  `internal_link_count` int(10) unsigned DEFAULT NULL,
  `incoming_link_count` int(10) unsigned DEFAULT NULL,
  UNIQUE KEY `object_id` (`object_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Notice the object_id field is used to create a UNIQUE_KEY index, instead of being set as the PRIMARY KEY. I'm not using any special options to mysqldump other than specifying the host name and user name.

How can I get mysqldump to include the primary keys?


texts:
In SQL Server I have a table that is filled often and also gets deleted often.
Because several threads write and erase data from that table I cannot even truncate it because there is always someone accessing it.

Thats why the threads do "delete from" statements.

As result I now have a table that has 6 TB (terabyte) of reserved space and 0 rows.
The reserved space goes down slowly implying the ghost cleanup works.
I know I can rebuild the table or truncate it, but I wonder if there is a solution that I can use to speed up deletion during runtime? Is there a possibility to speed up ghost cleaner somehow?


texts: Does CREATE/DROP VIEW require a SCH-M Lock

texts:
As part of processing an Invoice, our 3rd party ERP creates, queries, then drops a view on one of the ERP Database tables.

We have separate concerns about this, but I'm genuinely curious: do either of the CREATE or DROP statements require a Schema Modification Lock on the Database or the tables that are involved in the view?

If not, are there any other lock types involved in the process that we should be concerned about?


texts: Oracle 12.1 string literal on varchar2 with trailing space

texts:
I'm having a very specific issue with Oracle 12.1 concerning the handling of string literal ending with space in SQL WHERE clause applied on a VARCHAR2(2500) column (named 'NOTES').

As I need Case-and-Accent-Insensitive string comparison, I alter the NLS_COMP and NLS_SORT in a Logon Database trigger:

ALTER SESSION SET NLS_COMP='LINGUISTIC';
ALTER SESSION SET NLS_SORT='BINARY_AI';

Then, if i try this (notice the ' ' space at the end of 'cholera', it is needed as the space is present in the table data, and I can't change it as I only have read-only access to the table):

SELECT NOTES FROM DECCODESIDC WHERE NOTES='cholera ';

So far, so good, it returns the only row matching the criteria ('Choléra ')

But if i create a View based on the table, and I apply the same criteria, it doesn't return anything:

SELECT NOTES FROM (SELECT NOTES FROM DECCODESIDC) WHERE NOTES='cholera ';

I noticed that the explain plan is different between the two queries

Here is the first query explain plan:

Notice the 6 last digits of HEXTORAW : 61 20 00   -> 61='a', 20=' ' *(space)*, 00=end

And the second one:

Notice the 6 last digits of HEXTORAW : 72 61 00   -> 72='r', 61='a', 00=end

As we can see, the HEXTORAW function automatically added by Oracle remove the trailing space in the second query, but not in the first...

I'm aware that string literals are considered as CHAR by Oracle and are subject to space padding, but in this case the string literal is compared against a VARCHAR2 type column... and it doesn't explain why the execution plans are differents ...

Am I missing something or is it a bug in Oracle?

Benoit

ps: the fact is that I don't write such queries by hand, but rely on Entity Framework with Oracle Managed Drivers, so I don't have so much options concerning query rewriting :(

ps2: As a temporary workaround, I added a call to the TRIM oracle function on every VARCHAR2 column in my View, but it is suboptimal in term of performance...


texts:
For our new system we are looking for database server. We considered a lot of pros/cons for MySQL(InnoDB) vs PostgreSQL and the breaking point for us is partitioning. We will choose the one which partitioning fits better our needs.
Our case
The IS is backend for selling tickets. We sell thousands of tickets daily so the table scales quite fast and seat reservations scale even faster.
+--------------+            +-------------------+
| tickets      |    1:N     | seat_reservations |
+--------------+            +-------------------+

We want to use date partitioning for both of these tables and some others but this is the core.
We need to force preserve db integrity via foreign keys or some similar solution. I know that InnoDB doesn't support FK with partitioning.
The questions

Does PostgreSQL support FK and Partitioning? Probably for rules based partitioning.
Is another way how to force preserve DB integrity?
Which SQL server do you recomend? (based on partitioning)

I plan to partition both tables. I want partition even more than that (log,...) but these are the main tables.
We are just building the system so we want to use the newest versions; MySQL 5.6 or PostgreSQL 9.3. The r/w ratio will be about 3:1 (r:w), and the line count will be 700k growing about 100k per month so we have to be able to have more than 7m lines.


texts: Mysql - get sum() result in another column

texts:
I get user_point column's sum like

select sum(user_point) as user_total_point from users;

but I also want to use that sum result for new generated column like

select sum(user_point) as user_total_point, (user_total_point * 100) as hundred_times from users;

-

Error: Query 1 ERROR: Unknown column 'user_total_point' in 'field list'

How can i use user_total_point column's value for another colums?


texts:
My local PostgreSql 9.2 database won't start anymore this morning. I am on Windows 7. Yesterday, I performed a Windows Update before going to bed.

I have checked the log and found:

2014-05-28 10:16:03 CEST LOG:  database system was interrupted while in recovery at 2014-05-28 10:11:41 CEST
2014-05-28 10:16:03 CEST HINT:  This probably means that some data is corrupted and you will have to use the last backup for recovery.
2014-05-28 10:16:03 CEST LOG:  database system was not properly shut down; automatic recovery in progress
2014-05-28 10:16:03 CEST LOG:  redo starts at 0/25544D08
2014-05-28 10:16:03 CEST LOG:  file "pg_clog/0000" doesn't exist, reading as zeroes
2014-05-28 10:16:03 CEST CONTEXT:  xlog redo commit: 2014-04-24 18:48:48.775+02; rels: base/16855/140563 base/16855/140562 base/16855/140561 base/16855/140560 base/16855/140559 base/16855/140558 base/16855/140557 base/16855/140556
2014-05-28 10:16:03 CEST FATAL:  the database system is starting up
2014-05-28 10:16:04 CEST FATAL:  the database system is starting up
2014-05-28 10:16:05 CEST FATAL:  the database system is starting up
2014-05-28 10:16:06 CEST FATAL:  the database system is starting up
2014-05-28 10:16:07 CEST FATAL:  the database system is starting up
2014-05-28 10:16:08 CEST FATAL:  the database system is starting up
2014-05-28 10:16:12 CEST FATAL:  the database system is starting up
2014-05-28 10:16:13 CEST FATAL:  the database system is starting up
2014-05-28 10:16:14 CEST FATAL:  the database system is starting up
2014-05-28 10:16:15 CEST FATAL:  the database system is starting up
2014-05-28 10:16:16 CEST FATAL:  the database system is starting up
2014-05-28 10:16:18 CEST FATAL:  the database system is starting up
2014-05-28 10:16:19 CEST FATAL:  the database system is starting up
2014-05-28 10:16:20 CEST FATAL:  the database system is starting up
2014-05-28 10:16:21 CEST FATAL:  the database system is starting up
2014-05-28 10:16:22 CEST FATAL:  the database system is starting up
2014-05-28 10:16:23 CEST FATAL:  the database system is starting up
2014-05-28 10:16:24 CEST FATAL:  the database system is starting up
2014-05-28 10:16:25 CEST FATAL:  the database system is starting up
2014-05-28 10:16:26 CEST FATAL:  the database system is starting up
2014-05-28 10:16:27 CEST FATAL:  the database system is starting up
2014-05-28 10:16:28 CEST FATAL:  the database system is starting up
2014-05-28 10:16:29 CEST FATAL:  the database system is starting up
2014-05-28 10:16:30 CEST FATAL:  the database system is starting up
2014-05-28 10:16:31 CEST FATAL:  the database system is starting up
2014-05-28 10:16:32 CEST FATAL:  the database system is starting up
2014-05-28 10:16:33 CEST FATAL:  the database system is starting up
2014-05-28 10:16:34 CEST FATAL:  the database system is starting up
2014-05-28 10:16:35 CEST FATAL:  the database system is starting up
2014-05-28 10:16:36 CEST FATAL:  the database system is starting up
2014-05-28 10:16:37 CEST FATAL:  the database system is starting up
2014-05-28 10:16:38 CEST FATAL:  could not access status of transaction 0
2014-05-28 10:16:38 CEST DETAIL:  Could not open file "pg_clog/0000": No such file or directory.
2014-05-28 10:16:38 CEST CONTEXT:  xlog redo zeropage: 4
2014-05-28 10:16:38 CEST LOG:  startup process (PID 8024) exited with exit code 1
2014-05-28 10:16:38 CEST LOG:  aborting startup due to startup process failure

It is not a real issue if I have lost the database content, because I use it for development purposes only.

How should I proceed with this? Should I reinstall PostgreSql 9.2? Is there a command to restore it in a proper state?


texts:
Is it possible to exclude a database from an availability group backup?

My backup jobs looks like this:

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'AVAILABILITY_GROUP_DATABASES'
@URL= xxx,
@Credential = xxx,
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = NULL,
@CheckSum = 'Y',
@LogToTable = 'Y'


texts: Updating MySQL innodb table based on multiple joins

texts:
I have a table with the following (slightly simplified) structure

CREATE TABLE `oak_relation` (
 `o_id` int(10) unsigned NOT NULL,
 `k_id` bigint(20) unsigned NOT NULL,
 `initial` float unsigned NOT NULL,
 `xy` float unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`o_id`,`k_id`),
 KEY `xy` (`xy`)
) ENGINE=InnoDB

There are multiple instances of the same o_id, and multiple instances of the same k_id, but the primary key ensures there are only single instances of a row with an individual o_id & k_id.
Values for initial range from 0 to 1.

I would like to run an update to generate values for "xy".
The update should perform the following operation:
For every row in the table, calulate the xy value;

xy = x * y

where

x = `initial` value / number of rows matching the o_id of this row
y = total number of distinct o_ids / number of rows matching the k_id of the row

My query so far:

UPDATE
    oak_relation AS final
    INNER JOIN
    (
        SELECT
            oak.o_id ,
            oak.k_id ,
            oak.initial,
            link.numLinks,
            COALESCE( ( oak.initial / link.numLinks ), 0 ) AS x
        FROM
            oak_relation AS oak
            LEFT OUTER JOIN
            (
                SELECT
                    o_id ,
                    COALESCE( COUNT( k_id ), 0 ) AS numLinks
                FROM
                    oak_relation
                GROUP BY
                    o_id
            ) AS link ON oak.o_id = link.o_id
    ) AS xjoin

    INNER JOIN
    (
        SELECT
            k_id ,
            COUNT( o_id ) AS numOforK ,
            COALESCE( ( tmp.totalO / COUNT( DISTINCT( o_id ) ) ), 0 ) AS y
        FROM
            oak_relation,
            (
                SELECT
                    COUNT( DISTINCT( o_id ) ) AS totalO
                FROM
                    oak_relation
            ) AS tmp
        GROUP BY
            k_id
        HAVING
            COUNT( o_id ) > 1
    ) AS yjoin

SET
    final.xy = xjoin.x * yjoin.y

WHERE
    final.o_id = xjoin.o_id
    AND
    final.k_id = xjoin.k_id
    AND
    xjoin.k_id = yjoin.k_id

The trouble I am having is my query doesn't seem to update all the rows and my SQL knowledge is a little limited in updates using joins.
I thought perhaps it was just not updating if the xy result was 0 (if initial is 0), but selecting the expected result set shows that some xy = 0 values are being set, also tested by setting default xy value to some arbitrarily large number and examining the result after update. Some rows were definitely being set to 0.

So I'm a little stuck! For the rows where the calculation happens, it appears to work correctly, I just seem to not be selecting the correct set of data to update so it calculates for every row of the table.

Thanks very much in advance for any help or insight you might be able to provide.


texts: Adding subpartitions to existing partitioned table

texts:
In Oracle 11g, is there a way to introduce subpartitions into an existing partitioned table?

I cannot seem to find a combination of EXCHANGE and SPLIT partition that does the right thing.  SPLIT will split a partition into multiple partitions, not introduce subpartitions.  Any suggestions?

I did find an existing post on partitioning an existing non-partitioned table by exchanging to a table with one partition and then using SPLIT, but can't figure out the equivalent process for subpartitions.


texts:
I have a Postgres 10 database which is the model for an MVC app.
On it many (200,000) LOBS get saved, eventually processed, and stored or deleted depending on some conditions.

I have two problems.

The first is about disk space management, i.e. I want that my tablespace occupies only a portion of disk that I'll allow to it, and when the maximum allowed storage is reached, the application has to stop inserting LOBS.

AFAIK there are no ways to set quota on tablespaces, so if the application continues inserting it fills up the whole disk space, not only the one I've chosen to occupy.

So in order to avoid problems I've set up a disk quota at the OS level, but this is a rough solution.

Is there any better way to manage this problem?
The second problem is about unused space release. AFAIK, if I delete some LOBS, the pg_largeobject size is exactly the same, so I have to do a vacuum full to release the unused space of it.
But for doing this how much additional space do I need?

Reading somewhere it seems that I need at list the table size free disk space, so if it's 100 GB, I need to have another 100 GB in order to perform vacuum.

Is it true?

I've got a baffling experience on that. I had deleted 100,000 records, tried to vacuum full, and I got a "no disk space" error. I deleted another 120,000 records chunk (but the table is not empty), retried the vacuum full and this time worked.

Can you help me shedding light on this matter?


texts: Index for date column for range query in Oracle?

texts:
We have a lot:

select count(*) from TBL where XDATE between ? and ? and FLD = ?;

We expect to boost performance by tuning index on XDATE column (which have DATE type).

What type of indexes more suitable for DATE type and between condition?


texts: pg_restore importing single table to a file

texts:
I have a backup in directory format taken with pg_dump.
When I try to export single table from this dump with the command:
pg_restore -Fd mydirectory -t sometable > table.sql

I get the error :
pg_restore: error: one of -d/--dbname and -f/--file must be specified.

When I read the document if no -d parameter passed pg_restore should print the output to stdout.
What I am  missing?


texts: SQL Server Upgrade 2008 -> 2016 / 100 to 130 compatibility slow queries, new CE killing me

texts:
Recently upgraded from SQL Server 2008 to 2016, been running in compatibility mode 100 for 4 months everything was ticking along nicely (fast). After a fair amount of testing and running the migration adviser i decided to flick the "switch" and changed the compatibility level to 130...

This has had a detrimental effect on a quite a few sprocs/queries , some which where running in split seconds are now taking minutes. Also CPU has bumped up due to this.

These queries are written well, also I rebuild indexes and stats nightly..

Compared plans which look the same, with a couple of percentages slightly off, still the same plan though! i think CE isn't getting good row counts.

Also what is weird some times the queries run fine in 130 so i think all is good, but suddenly get an alert and the query is taken ages just by 2 rows being different then I have to add back OPTION (QUERYTRACEON 9481) to get it speedy again.

Are there any other things I can check / do to help get these queries get back to their former glory?

Should i remove all stored procedures plans from cache???


texts:
I have dozens of text reports that come in every day and I need to store them. I cannot just zip them and put them on the hard drive because the users aren't sophisticated enough to figure that out. I need to be able to associate some data with the report so they can look up report ABC for any given date. These files range in size from 11K to 1GB. Mostly they are less than 3 MB and many are less than 1MB.

What is the best way to store these? The options I am looking at are using Filestream or storing it in a varchar(max) field or zipping it and storing it in a varchar(binary) then unzipping it to display. I am open to any other options as well. Space efficiency is a high priority.

Thank you for reading this. I would truly appreciate any help or advice.

** The other post is very interesting and I learned a lot from it - thank you for referencing it.  This is different from the other post in that my files are plain text files -- they are not binary files.


texts: Strange error in trace file for NSAn standby process

texts:
One of the Oracle databases I help manage is the primary in a standby configuration, with two standby databases. I started finding repeated entries in the alert log on the primary for a failure to resolve the TNS connect identifier for log_archive_dest_3 and referencing the trace file for NSA3. That trace file had an error message I'd never seen before, and I can't find any information on it for the life of me: "LOG_ARCHIVE_DEST_2 depends upon me, but my destination index 2 does not match its destination index 1." This showed up several times in the trace file.

I was able to verify that all standbys are up-to-date with the primary and are responsive to tnsping. Can anyone shed some light on this error?


texts:
I am trying to create a database structure that will allow me to store records of league matches across multiple seasons and divisions. I have the following rules that need to be maintained:

A league can contain multiple divisions
Multiple seasons can take place for a league
Multiple leagues can take place in the same season
A team can only play in one division of a league in any given season
A player can only play for one team in a league in any given season (e.g. Player A cannot play for a team in Division 1 and a team in Division 2 in the same season)
However a player can play for different teams in different leagues in the same season (e.g. Player A can play for a team in League 1 and a team in League 2 i the same season)

My initial design is in the picture below but I am not sure whether it will meet all of the constraints. I have chosen to link seasons to divisions rather than leagues as the divisions make up the league rather than link them directly. Any advice to improve the design and to help enforce the rules would be greatly appreciated.


texts: How do I solve an error code 127 when doing a pg_dumpall through a cronjob?

texts:
I have this simple script to backup all my databases in postgres

    pg_dumpall -U pg_user > /tmp/tmp.txt
    DUMP_STATUS=$?
    echo "Dump status: $DUMP_STATUS" > /tmp/status.txt

The dump status is 0 and the tmp.txt file is properly created when the script is executed from the command line.

However, if I execute this script as a cronjob in my crontab, my tmp.txt is empty (the dump of all databases failed) and my status.txt file contains a dump status 127.

A strange behavior I noticed is that pg_dumpall will pipe the information into the file but also print it on the terminal.  I'm thinking this behavior might be the cause of the problem.

Any idea how I can solve this issue?

I am running FreeBSD:

    > uname -mrs
    FreeBSD 9.1-PRERELEASE amd64

    > psql -V
    psql (PostgreSQL) 9.1.7
    contains support for command-line editing


texts: Unable to call sp_start_job from website only

texts:
I have an agent job that I'm trying to use to kick off an SSIS package via a web page. I have configured my web site running in an application pool as user X.

When I invoke sp_start_job as use X from SSMS it successfully starts the job.

When I use my web page to invoke sp_start_job permission is denied.

They user is an administrator locally, and a member of the sysadmin role.

What is preventing successfully invoking sp_start_job from the website?


texts: Is it possible to add index to mysql with multiple column to optimize order by performance?

texts:
# I have a simple table
  create table t1 (c1 int, c2 int);
  # I want to do the following sorting
  select * from t1 order by c2 desc, c1 desc;
  select * from t1 order by c2 asc, c1 desc;
  # add indexs
  CREATE INDEX index_1 ON t1 (c1 DESC, c2 DESC);
  CREATE INDEX index_2 ON t1 (c1 ASC, c2 DESC);
  # then explain
  explain select * from t1 order by c2 desc, c1 desc;
  # the result is
  mysql> explain select * from t1 order by c2 desc, c1 asc;
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | t1    | index | NULL          | index_2 | 10      | NULL |    6 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

My questions are

Are the indexs optimized the performance?
Why Using index; Using filesort show at the same time?


texts:
I need to get data from a remote SQL 2000 server and store it in a new MySQL server. I am using the FreeTSD module for php 5.3 to connect to the SQL 2000 server. I can connect without problems.

Here's my issue; one particular table isn't yielding any result to this query:

SELECT * FROM Item

But this query is successful when I test it SQL Query Analyzer.

I do get results (also remote) when I change it to:

SELECT * FROM Brands

The Item table holds over 500K records and I'm thinking that that might have something to do with it. Could that be the issue and how can I work around that. I have tried this without result:

SELECT TOP 10 * FROM Item

This is the php code to connect:

try {
    $db = new PDO('odbc:Driver=FreeTDS; Server=xxx.xxx.xxx.xxx; Port=1433; Database=xxxxx; UID=xxxxx; PWD=xxxxxx;');
} catch(PDOException $exception) {
    die("Unable to open database.
Error message:

$exception.");
}

And this is the select code:

$query = "SELECT * FROM Item";
$statement = $db->prepare($query);
$statement->execute();
$data = $statement->fetchAll(PDO::FETCH_ASSOC);

echo '
';
var_dump($data);
echo '
';

Which returns an empty array.


texts: SQL actually executed as planned in ORACLE?

texts:
I wonder if SQL is definitely executed as execution plan.

I mean.. is the actual SQL execution process always consistent with execution plan, i.e. does the same SQL always give the same plan?


texts: How do I create a user-defined aggregate function in MySQL (redux)?

texts:
OK, I'm probably going to get banned for asking the same question a second time, but I'm an incredible newbie here and I was told I don't have enough of a reputation to comment on the original question.

I agree with the OP's (Matt Fenwick's) comment to answer 5 in How do I create a user-defined aggregate function? in that it gives a fish for a day, but the real question is learning how to fish.

I need, among other things, a GROUP BY aggregate function like the MIN function, but on call SMALL(t.value, 2) it return the second smallest data element, on call SMALL(t.value, 3) it return the third smallest data element, and so on. (This actually works with LibreOffice Calc's SMALL function, BTW.)

The problem for my particular reason for asking this problem is I would like to convert my grading spreadsheet for a college course to a MySQL database, and I need to drop the two lowest attendance score, the two lowest homework scores, and the two lowest quiz scores for each students to compute their adjusted attendance percentage, adjusted homework average, and adjusted quiz average, among other adjustments to the data.

The tblQuizzes looks something like

mysql> select * from tblQuizzes LIMIT 12;
+------------+---------+-----------+
| StudentKey | QuizKey | QuizGrade |
+------------+---------+-----------+
|          1 |       1 |     0.123 |
|          2 |       1 |     0.456 |
|          3 |       1 |     0.789 |
|          4 |       1 |     0.890 |
|          5 |       1 |     0.123 |
|          6 |       1 |     1.000 |
|          1 |       2 |     0.789 |
|          2 |       2 |     0.123 |
|          3 |       2 |     0.456 |
|          4 |       2 |     0.789 |
|          5 |       2 |     0.123 |
|          6 |       2 |     1.000 |
+------------+---------+-----------+

I would need a query like

SELECT
tblQuizzes.StudentKey,
(SUM(tblQuizzes.QuizGrade) - MIN(tblQuizzes.QuizGrade) - UDF_SMALL(tblQuizzes.QuizGrade, 2))/(COUNT(tblQuizzes.QuizGrade) - 2) AS AdjQuizAverage
FROM
tblQuizzes
GROUP BY
tblQuizzes.StudentKey

to drop the lowest 2 quiz scores and average the remaining quizzes.

So, keeping in mind that I don't just want "a fish for a day" and know how to get the second lowest quiz score, but instead am interested in "learning how to fish" and write a MIN(table.value, offset) GROUP BY aggregate function (in C/C++ or PHP if necessary), can anyone provide some hints/complete answers with links and examples for this "fishing problem"?

Thanks much in advance for any assistance you can provide; I'm sorry if I seem rude in my "demands"/requests, I've just learned that vague requests result in workaround, "fish for a day" replies.


texts: Very high memory usage by Postgres 10.1 on Windows 2008 R2

texts:
On Windows start up, ram usage is around 5% but it gradually keeps increasing and will eventually balloon to where it is using almost all the ram.The DB size is only 4941MB. The server has 8gb total memory and is a Xeon Quad core. The DB is on its own SSD drive. Below are the settings from my postgresql.conf file:

max_connections = 200
shared_buffers = 512MB
work_mem = 6553kB
maintenance_work_mem = 512MB
dynamic_shared_memory_type = windows
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
wal_level = hot_standby
wal_buffers = 16MB
max_wal_size = 2GB
min_wal_size = 1GB
checkpoint_completion_target = 0.7
max_wal_senders = 5
wal_keep_segments = 32
max_replication_slots = 5

Below is a screenshot of Task Manager....

If any other info is required i'll happily provide it.


texts: How to add / create table in the current database in mysql?

texts:
mySQL 5.6.24: Could anyone guide me on that "How to create a base view and table for a specific plugin?"

After uploading the plug-in, I'm getting following error:

  "There has been an error processing your request SQLSTATE[42S02]: Base
  table or view not found: 1146 Table
  'yuvakxkb_mage800.mgjm_manthan_marketplace_seller' doesn't exist,

query was:

SELECT `main_table`.*
FROM `mgjm_manthan_marketplace_seller` AS `main_table`
WHERE (`user_id` = '1')"

How can I create a base view and table for the above error in MySQL?


texts: Access Db `Long Text` Unicode Byte Corruption

texts:
I have a column that is Long Text when I attempt to edit a cell it randomly inserts characters and does other weird things. The column has French HTML text in it. What could be causing this and what could fix it? I assume there might be a byte offset problem but I never dealt with that before. I've googled around and haven't been able to find anything on it.

E.g.,

Se présente en nappes.

When I type a space, , right after the first word Se it inserts an e, e.g.,

Se e présente en nappes.

I don't work with Access very much so this is quite strange to me.

Thanks in advance.


texts: Does galera cluster works in thecloud (Azure IAAS)

texts:
We are moving our infrastructure into the cloud (Microsoft)

Our current MySQL master-Slave environments will be moved as Infrastructure as a service.

At the same time I have been asked to look to move some of our master-slave env into a cluster.
We do not have the budget for MySQL cluster
This is why I'm looking at the Galera Cluster

will Galera Cluster work on the cloud and specially in Microsoft AZURE (IAAS)

Anybody with experience on something similar

Thank you

Regards


texts:
I already have a question on gis.stackexchange.com assuming it may be geoserver the problem but as I go deeper into problem I found that I have a database bottleneck. My SQL server CPU usage is almost 100% when geoserver executes following queries :

SELECT "siteId","Vendor",CAST("SP_GEOMETRY".STSrid as VARCHAR)
  + ':' + "SP_GEOMETRY".STAsText() as "SP_GEOMETRY" FROM "LiveData"."sites"
         WHERE ("SP_GEOMETRY".Filter(
    geometry::STGeomFromText('POLYGON
    ((87.16003722182671 27.034666490477207,
      87.16003722182671 28.328562656235395,
      88.62121886224784 28.328562656235395,
      88.62121886224784 27.034666490477207,
      87.16003722182671 27.034666490477207))', 4326)) = 1
AND ( UPPER("Vendor") LIKE 'A%'  OR  UPPER("Vendor") LIKE 'B%' ))

--

SELECT "Site_ID","Vendor",CAST("sp_geometry".STSrid as VARCHAR)
  + ':' + "sp_geometry".STAsText() as "sp_geometry"
FROM "LiveData"."sites"
WHERE ("sp_geometry".Filter(geometry::STGeomFromText('POLYGON ((
  82.89734190991959 26.99551618775358,
  82.89734190991959 28.367238076413702,
  84.44641417532948 28.367238076413702,
  84.44641417532948 26.99551618775358,
  82.89734190991959 26.99551618775358))', 4326)) = 1
AND ( UPPER("Vendor") LIKE 'A%'  OR
 UPPER("Vendor") LIKE 'B%' ))

Now the problem is as I open multiple pages just 4-5 pages the web pages hangs as it has to retrieve data from database.

I have a view that is made spatial by selecting spatial column SP_GEOMETRY from  base table:

    CREATE view [LiveData].[sites]
      As
     select
     live.Site_ID as siteId,live.Site_Name,live.Vendor,live.Status,live.MI_SQL_REC_NUM,
     live.MI_STYLE,live.MI_PRINX,live.SP_GEOMETRY
     from baseTble.spatialSites live ,LiveData.liveStatus stat
where
live.Site_ID  = stat.siteId and stat.Code in ('x123','y345') and stat.alias = 'Neyman'


texts: Why is a NOT NULL computed column considered nullable in a view?

texts:
I have a table:

CREATE TABLE [dbo].[Realty](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [RankingBonus] [int] NOT NULL,
    [Ranking]  AS ([Id]+[RankingBonus]) PERSISTED NOT NULL
    ....
)

And a view:

CREATE View  [dbo].[FilteredRealty] AS
 SELECT
realty.Id as realtyId,
...
COALESCE(realty.Wgs84X, ruian_cobce.Wgs84X, ruian_obec.Wgs84X) as Wgs84X,
COALESCE(realty.Wgs84Y, ruian_cobce.Wgs84Y, ruian_obec.Wgs84Y) as Wgs84Y,
realty.Ranking,
...
FROM realty
JOIN Category ON realty.CategoryId = Category.Id
LEFT JOIN ruian_cobce ON realty.cobceId = ruian_cobce.cobce_kod
LEFT JOIN ruian_obec ON realty.obecId = ruian_obec.obec_kod
LEFT JOIN okres ON realty.okresId = okres.okres_kod
LEFT JOIN ExternFile ON realty.Id = ExternFile.ForeignId AND ExternFile.IsMain = 1
                     AND ExternFile.ForeignTable = 5
INNER JOIN Person ON realty.OwnerId = Person.Id
WHERE Person.ConfirmStatus = 1

I have a dbml model in C# (LinqToSQL) with the FilteredRealty view in it. The [Ranking] field is recognized as a nullable int and so I have to fix the type in the generated code every time when I change anything in the database. This is very frustrating for me and a lot of manual work.

There are no aggregates used in FilteredRealty (regarding this related question).

Why is the Ranking column of the view considered as nullable if Realty.Ranking is non-nullable?


texts: What is the best solution to implement to replicate a database, with a purging process is in place

texts:
I am planning to implement a solution solution to create a separate database for reporting purposes (read only).  I thought of implementing transactional replication to solve this problem, however I have a data purge process that runs every week.  This means that any data deleted will be replicated too.

I need help to design the best solution, how do I create a separate database instance for reporting.  I want the primary database to keep the data purging in place, but I want a separate db system that holds all the data.

Need ideas!  Please help!


texts: MySQL FullText search on string shorter than 3 chars returns no rows

texts:
I have a MySQL table with some addresses in it. Let's say I have the following 2 rows:

"10 Fake Street"
"101 Fake Street"

I'm trying to use fulltext search with MATCH() AGAINST(). I have set ft_min_word_len to 1, rebooted the server and dropped and then rebuilt the index by running

ALTER TABLE addresses DROP INDEX address_index
CREATE FULLTEXT INDEX address_index ON addresses(street)

I have verified that my ft_min_word_len is indeed set to 1 by running

show global variables like 'ft_min_word_len'

If I include any word in my search that's shorter than 3 characters, I get no results back, unless I append a wildstar to it. For example

SELECT * FROM addresses WHERE MATCH(street) AGAINST('+101' IN BOOLEAN MODE)

or

SELECT * FROM addresses WHERE MATCH(street) AGAINST('+10*' IN BOOLEAN MODE)

both return 1 row "101 Fake Street". Running

SELECT * FROM addresses WHERE MATCH(street) AGAINST('+10' IN BOOLEAN MODE)

returns 0 rows. Why? The only suggestions I can find online all talk about setting min length, but I already verified that mine is set to 1 and rebuilt the index.


texts: Database corruption: QueryStore internal table

texts:
This morning, the following email alert was received:

  DATE/TIME:    2/28/2018 9:26:42 AM

  DESCRIPTION:  Attempt to fetch logical page (1:3948712) in database 9
  failed. It belongs to allocation unit 72057594045857792 not to
  72059184917512192.

  COMMENT:  (None)

  JOB RUN:  SQL Sentry 2.0 Alert Trap

Looking in the event log of the secondary replica there are three occurrences of the same message:

  Source        spid138

  Message Attempt to fetch logical page (1:3948712) in database 9
  failed. It belongs to allocation unit 72057594045857792 not to
  72059184917512192.

Running the following on the secondary replica (2 node synchronous Availability Group):

DBCC TRACEON(3604)
dbcc page (9, 1,3948712,3)
go
DBCC TRACEOff(3604)

Snippet of the results from either replica:

Page @0x00000070DAB8C000

m_pageId = (1:3948712)              m_headerVersion = 1
m_type = 3 m_typeFlagBits = 0x0                m_level = 0
m_flagBits = 0x8200 m_objId (AllocUnitId.idObj) = 129   m_indexId
(AllocUnitId.idInd) = 256  Metadata: AllocUnitId = 72057594046382080
Metadata: PartitionId = 72057594040811520
Metadata: IndexId = 1 Metadata: ObjectId = 197575742
m_prevPage = 0:0)                  m_nextPage = (0:0) pminlen = 0
m_slotCnt = 2                       m_freeCnt = 1634 m_freeData = 6568
m_reservedCnt = 0                   m_lsn = (46041:1506360:18)
m_xactReserved = 0                  m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = -99702035              DB Frag ID = 1

Running the following on the primary replica:

select OBJECT_NAME (197575742)

plan_persist_plan

Questions

Am I right in saying that I have a clustered index corruption of the plan_persist_plan table which is part of Query Store?
Is the best/only fix to run the following:

ALTER DATABASE MyDatabase SET QUERY_STORE CLEAR;

If #2 is the best fix, is there any good way of preserving the data in Query Store that would be deleted?
Does this kind of corruption indicate a problem with the IO subsystem?

Other info

I have QueryStore enabled obviously, it has a capacity of 350MB, is in Read-Write mode currently, flush interval 15 minutes, stats collection hourly, Capture mode ALL, Auto size based cleanup, 5 day stale query threshold.
DB id 9 is a business critical user database
The error details are Error: 605, Severity: 21, State: 3.

I have checked the Windows System Event log as per the guidance. This has yielded only "Informational" events, no errors.

DBCC CHECKTABLE ('sys.plan_persist_plan');

results:

DBCC results for 'sys.plan_persist_plan'.
There are 12562 rows in 240 pages for object "sys.plan_persist_plan".
DBCC execution completed.
If DBCC printed error messages, contact your system administrator.

I cannot establish the correct command to rebuild the index, the following does not work:

ALTER INDEX PK_plan_persist_plan_cidx ON sys.plan_persist_plan REBUILD;


texts: Unable to install 64-bit Oracle 10g Release 2 ODAC 10.2.0.3 for Windows x64 on 64 bit Windows Server 2008 R2

texts:
I need to connect an application to Oracle. In the installation guide for that application it says that it requires 64 bit version of Oracle Data Access Component,more specifically 10.2.0.3 . I have tried installing it but I am continuously getting the error attached in the image when ever I try running the installer. The crash details are given in the image attached. Has some one ever tried installing it? If yes then would it be possible to give some details or point me in the right direction so that I can complete this installation.

Thanks


texts:
I have one MySQL database and I want to migrate it with data to MSSQL. I have tried a few tools including MS SQL server Import and Export wizard, DataLoader, ESF Database migration toolkit, mysqldump with --compatible=mssql.

All of these tools are working, but it is migrating views as tables.

Do you have any experience with the tool that can migrate views as well?


texts:
How do you usually warm up your database ?

I run a similar queries to this for every table in DB:

SELECT * FROM ip_log ORDER BY ID;
SELECT ip, member_id FROM ip_log ORDER BY ip, member_id;

Is that the best way, or there is something cooler ?


texts: Improve split large table algorithm in MySQL

texts:
I have a very large table ~1TB of history data in MySQL 5.6. I want to split the data into many smaller tables per sites. My current idea is to iterate in chunks of 10'000 records and inside this loop iterate through each chunk to all sites. This has taken more than 2 days (stopped).

Is there any way of improving this query?

Code

for min_id,max_id in range(1,max_id_in_table,chunk_size):
    for site_id in [site_id_1,site_id_2,site_id_3]:
        insert into history_table_<site_id> (field_a,field_b,field_c)
        select field_a,field_b,field_c
        from history_table h  where h.site_id = <site_id> AND h.id > <min_id> AND h.id <= <max_id> ;


texts: Is there a standard database layout for repetitive information?

texts:
I was recently tasked with creating a database to store a modest amount of data. I am learning as I go, but my main hold is figuring out the best way to layout the data. The problem is that for each test, there are n number of identical objects being tested and data is recorded for each, but the test id, date, test operator, etc.. are the same for all n objects.

So I see two options. I could have one entry per test but then have data columns named "test_result_1, test_result_2, ... test_result_n" for every result that we will be recording. This would lead to many columns for each test entry and would make searching for a test result difficult since you would have to search over n columns.

The other option is to have one entry per object, which would result in n entries having identical test ids, dates, test operators, and any other information that is specific to the test but not the objects. This would reduce the number of columns per entry and make searching for test results easier, but it would also increase the total amount of data stored and lead to more input required when done manually.

So is there any standard method for handling data of this type? It is a bit repetitive either way and I can see pros and cons to each side. I could see it being something that is considered on a case by case basis, but I figured I would ask if there is something I'm not taking into account that makes one option the standard.


texts:
In Oracle 11g, we are using XMLELEMENT to get records from tables. The problem seems to be, however, that we are calling SELECT XMLELEMENT(...).getClobVal() from DUAL; which is eating our temp tablespace like a sandwich.

I read that we can kill blob records using FREETEMPORARY(blob_ref), but in the format shown above, we have no reference to the blob. Is there any way to get one for it?

Because this is being run from a program and the library we use generates the PL/SQL block for us, it really cannot be restructured as a PL/SQL statement.

The DBCP is the connection pool we use with Tomcat and Spring's JdbcTemplate is what we're using to run the queries. Considering that JdbcTemplate swaps out bind variables for us, we're assuming that it is creating an anonymous block behind the scenes.

Running select * from v$temporary_lobs; reveals that for the current session I still have 4 in the ABSTRACT_LOBS column. This is after running the query by itself (no anonymous block) four times in SQL Developer.


texts: Auto_increment value needs to be set as S1

texts:
Hi I need to set auto increment for a table as S1. I tried by altering the table and also by creating by new table
I am aware how to set auto_increment but when i try to set auto increment as S1 i am unable to do. since i need the primary key values should be S1,S2,S3 as following records. can any one please suggest the data type for that,

 desc test_1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | varchar(10)  | NO   | PRI | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE test_1 AUTO_INCREMENT='S1';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''S1'' at line 1

mysql> create table tbl(ID varchar(100) not null primary key auto_increment,name varchar(100)) auto_increment='s1';
ERROR 1063 (42000): Incorrect column specifier for column 'ID'
mysql> create table tbl(ID text not null primary key auto_increment,name varchar(100)) auto_increment='s1';
ERROR 1063 (42000): Incorrect column specifier for column 'ID'
mysql> create table tbl(ID varchar(100) not null primary key auto_increment,name varchar(100)) auto_increment='s';
ERROR 1063 (42000): Incorrect column specifier for column 'ID'


texts: SQL Server: Always On Availability Group: getting Error: GetAccountToProcess failed: Cannot open database requested by the login. The login failed

texts:
When myself and our SA are testing our AO group, we are running a script that flows data into a table so we can monitor proper data. We are pointing this at our Listener: TestDBAG01, and it flows into our primary DB (TestDB0A1, using just 0A1 from here) and mirrored into our read-only secondary (TestDBOD1 - 0D1 from now on) when I do a select from the same table. So far so good.
So what's the problem?
No matter the way I do a failover, either manual or automatic, the script begins flagging an error IMMEDIATELY:
System.Exception: GetAccountToProcess failed: Cannot open database "Sitewit" requested by the login. The login failed. Login failed for user 'SWService'. at
SiteWit.SessionProcess.GetAccountToProcess() in
C:\Users\chris\Documents\Sitewit\LocalSource\Services\Development\SessionProcessing\SiteWit\SessionProcess.cs:line 1204 at
SiteWit.SessionProcess.ProcessSessions() in
C:\Users\chris\Documents\Sitewit\LocalSource\Services\Development\SessionProcessing\SiteWit\SessionProcess.cs:line 257 at
SiteWit_Session_Processing_Service.SiteWitSessionProcessor._svcTimer_Elapsed(Object sender, ElapsedEventArgs e) in C:\Users\chris\Documents\Sitewit\Local
Source\Services\Development\SessionProcessing\SiteWit Session Processing Service\SiteWitSessionProcessor.cs:line 100

and when I run a EXEC xp_readerrorlog I see:
Login failed for user 'SWService'. Reason: Failed to open the explicitly specified database 'Sitewit'. [CLIENT: 192.168.66.57]

Until I run a orphaned login script:
SET NOCOUNT ON
USE SiteWit
GO
DECLARE @loop INT
DECLARE @USER sysname
IF OBJECT_ID('tempdb..#Orphaned') IS NOT NULL
BEGIN
    DROP TABLE #orphaned
END
CREATE TABLE #Orphaned (UserName sysname, UserSID VARBINARY(85),IDENT INT IDENTITY(1,1))
INSERT INTO #Orphaned
EXEC SP_CHANGE_USERS_LOGIN 'report';
IF(SELECT COUNT(*) FROM #Orphaned) > 0
BEGIN
    SET @loop = 1
    WHILE @loop <= (SELECT MAX(IDENT) FROM #Orphaned)
    BEGIN
        SET @USER = (SELECT UserName FROM #Orphaned WHERE IDENT = @loop)
        IF(SELECT COUNT(*) FROM sys.server_principals WHERE [Name] = @USER) <= 0
        BEGIN
            EXEC SP_ADDLOGIN @USER,'password'
        END
        EXEC SP_CHANGE_USERS_LOGIN 'update_one',@USER,@USER
        PRINT @USER + ' link to DB user reset';
        SET @loop = @loop + 1
    END
END
SET NOCOUNT OFF
--End SQL Account Linking Script

I have been beating my head against a wall for a week about this. I am not sure if it's on the script side or the DB side. This is the first time I have been working with setting up a HA-AOG and am not sure what I can do to resolve this.


texts: Splitting TempDB into multiples files equal to number of CPUs

texts:
The article SQL Server tempdb Best Practices Increase Performance suggests that I should split tempdb into a number of files equal to the number of cores. So for 4 cores you get 4 files.

  By having the larger number of files, you can increase the number of
  physical I/O operations that SQL Server can push to the disk at any
  one time. The more I/O that SQL Server can push down to the disk
  level, the faster the database will run. With standard databases, SQL
  Server can cache a large amount of the data that it needs into memory.
  Because of the high-write nature of the tempdb, the data needs to be
  written to the disk before it can be cached back up into memory.

Though it sounds good in theory, is it really that good as a general optimisation? Is it something that may only apply for specific systems where IO is very high?


texts:
I want to design an ER diagram in order to create a database for a small local business. My problem is that the bussiness sells merchandise to persons and businesses both. How can i differentiate businesses from persons in my ER diagram? Should i create different entities for those?
EDIT: Here's my ER diagram so far. I implemented two subclasses to differentiate between business and person. Not sure if this is the best way to go though and i'm also not sure about cardinalities.


texts: Monitor source SQL code of inserts into table

texts:
I have a table with rows which should not be there, means the data is wrong according to business logic. And there is a lot of possible sources the insert statement of the "bad" row is coming from. I'm not able to repro this it's just happening from time to time. And I wanna know which exact SQL statement and module (eg. procedure/trigger / ad-hoc statement) is the source of that insert. To catch the culprit.

Using Profiler or Extended Events is not the way - because you don't see the data (just statements) and while I'm not able to repro this I cannot correlate the output with the data.

I've tried collect info from sys.dm_exec_input_buffer(@@SPID,0) inside my after triggering on insert (made for logging purposes, collecting every insert in that table). But this is too wide. It's giving just initial SQL statement, let say outer stored procedure, but the insert statement could be invoked in some nested stored procedure or trigger.

I've tried to collet output of sys.dm_exec_connections CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) - see the whole code below. But this is too narrow in opposite - it is giving me statement of my collecting after trigger.

Is there a way to catch something like it?

SELECT
OuterSQL = CAST(T.text as NVARCHAR(255))
,InnerSQL = CAST((SUBSTRING(t.text,(s.statement_start_offset/2) + 1,
          (CASE
             WHEN s.statement_end_offset = -1
               THEN DATALENGTH(t.text)
             ELSE s.statement_end_offset
           END - s.statement_start_offset)/2 + 1)) as NVARCHAR(255))
FROM sys.dm_exec_connections
    CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) T
    INNER JOIN sys.dm_exec_query_stats s ON sys.dm_exec_connections.most_recent_sql_handle = s.sql_handle
WHERE session_id = @@SPID;


texts: Postgres "could not receive data from client: Connection reset by peer."

texts:
I have a cakephproject got a error when the project reload and it seem the error Database connection "Postgres" is missing, or could not be created.

I have checked the error it will show error could not receive data from client: Connection reset by peer


texts: Does SQL Server 2012/2014 Standard support Hot Add memory on a Hyper-V guest?

texts:
In this support article:

https://support.microsoft.com/en-us/kb/956893

The answer to Q7 says:

  Hyper-V Dynamic Memory is fully supported with SQL Server. Only SQL
  Server versions and editions that support Hot Add Memory (Enterprise
  and Datacenter) can see memory that is added by using Hyper-V Dynamic
  Memory. SQL Server 2012 Standard edition also recognizes Hot Add
  memory when running in a virtual environment.

The two sentences seem contradictory. Can anyone confirm if SQL Server Standard supports hot add memory when running on a Hyper-V guest?


texts:
We are planning on migrating our existing SQL 2012 servers from current windows servers to new windows servers. Only difference is we are migrating this current instance which is standalone to Clustered SQL instance. [New windows server has been built and SQL is configured as clustered instance]

Therefore we need to migrate on the same SQL2012 and windows 2012 version.
Total database sizes on the instances are over 20 TB. And yes this is 24*7.

What all measures should i take or please suggest any action plan for this migration that can help me to kick start the migration process in above scenario?


texts:
Is there a way to have mariadb check tables in the background and not lock the tables after a crash? We have a number of fairly large databases and suffered a hardware failure that resulted in most of them checking tables on a restart which unfortunately blocked access to the databases for hours.

I get that a check is a good thing, however of all the databases, only one ended up needing to be fixed.

Ta.
Peter.


texts: How to compare dates on minute level in plsql?

texts:
If I'm correct, then date comparison on seconds level is as simple as

SELECT 1
FROM sample_table
WHERE sysdate <= sample_table_date_field;

but haven't found any simple or stable solution for date comparison on minute level.
If there is, then what would it be?


texts: Can I have 'Query Store' automatically on when I create new databases?

texts:
Query Store is available in SQL Server 2016+ and on Azure SQL DB it is on by default. With an on-premises SQL Server, it has to be started on each database individually.

Is there a way I can have Query Store on by default when new databases are created?


texts: Creating a report that fills in when the user makes a data selection from a combobox?

texts:
I want to make a report that acts as an empty template, but when the user makes a selection from a combo box or pop up window, the template fills with all of that data related to that primary key.

For example, my database stores information about purchases. I want the user to be able to select a vendor from a drop down list and see all the pertinent information such as how much money we spend with them, how long they have been an active vendor, and charts that show trends over time.

How can this be done if the vendor name is a primary key in the table?


texts: Fixing a UTF8 encoded database that was dumped using an incorrect encoding?

texts:
The question is probably a little vague so let me elucidate:

System OS is Ubuntu Linux 14.10 LTS
The Servers default encoding is Latin1
The database default encoding is/was UTF8
The data was dumped by piping mysqldump > out.sql

On re-importation back into the same engine into a database created with:

CREATE DATABASE test DEFAULT CHARACTER SET utf8;

it was re-imported using a simple Source out.sql;

As a result a number of :

  Warning (Code 1300): Invalid utf8 character string: '9CD4BB'

... warnings during importation.

I have tried using -verbose on the MySQL client, but it produces a large quantity of unnecessary information (out.sql is around 845MB)

Settings:

| character_set_client                              | utf8
| character_set_connection                          | utf8
| character_set_database                            | latin1
| character_set_filesystem                          | binary
| character_set_results                             | utf8
| character_set_server                              | latin1
| character_set_system                              | utf8
| character_sets_dir                                | /usr/share/mysql/charsets/
| collation_connection                              | utf8_general_ci
| collation_database                                | latin1_swedish_ci
| collation_server                                  | latin1_swedish_ci

Is it possible to generate  a smaller file containing the SQL statements that generated the warnings?
My assumption here is that during the data dump the data was converted to latin1 and saved as out.sql.  There are characters in latin1 that cannot be represented under utf8.  It is possible to convert them back, or at least a grep that can show me a small amount of the offending text - remembering that I think grep returns the entire line (and sql exports have no hard coded line endings)?
Given that I have lost the original data - any ideas on overall recovery strategy?


texts: How to connect to Oracle database on VM1 from host with 11gClient

texts:
I have a VM running Oracle Linux 6.5, I loaded Oracle server x64bit and created a database. The database functions fine and the listener is up and running for the database. I want to connect to the server (VM1) using SQL Developer from host. But I have no idea how to do this.

Can anyone point me to DETAILED instructions on the easiest way to accomplish this?


texts: Enable Service Broker when deploying solution

texts:
I'm having an issue deploying the latest build of a database solution after creating some service broker objects. I am thinking that it could be related to not having service broker enabled on the database, as it gets created as part of the build, then deployed straight away.

Where can I set the "ENABLE BROKER" option so it get's executed before deploy, but after the database is created by SQLPackage ?


texts: Postgresql ERROR: duplicate key value violates unique constraint when trigger insert

texts:
I've created a function which is called when a trigger is fired while insert. But every time I try to insert the data in the table I get the duplicate key value violates unique constraint

This is my trigger function:

CREATE OR REPLACE FUNCTION trg_new_rows()
  RETURNS trigger AS
$BODY$
BEGIN
IF EXISTS (SELECT 1 FROM organization WHERE organization.name = 'TV' AND organization.country = 'D') THEN
    --RAISE '1';
        INSERT INTO organization VALUES ('TV1','TV1',NULL,'TV1',NULL,NULL);
ELSE
        --RAISE '2';
        INSERT INTO organization VALUES ('Tivoli1','Tivoli1 organization',NULL,'F',NULL,NULL);
        INSERT INTO organization VALUES ('Tivoli2','Tivoli2 organization',NULL,'GB',NULL,NULL);
END IF;
RETURN NULL;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

And this is the trigger

CREATE TRIGGER delaft
  AFTER INSERT
  ON organization
  FOR EACH ROW
  EXECUTE PROCEDURE trg_new_rows();

And then when I try executing this:

INSERT INTO organization VALUES ('TV','Tivoli D organization',NULL,'D',NULL,NULL);

I get this error:

ERROR:  duplicate key value violates unique constraint "organization_pkey"
DETAIL:  Key (abbreviation)=(Tivoli1) already exists.
CONTEXT:  SQL statement "INSERT INTO organization VALUES ('Tivoli1','Tivoli1 organization',NULL,'F',NULL,NULL)"
PL/pgSQL function trg_new_rows() line 8 at SQL statement
SQL statement "INSERT INTO organization VALUES ('Tivoli1','Tivoli1 organization',NULL,'F',NULL,NULL)"
PL/pgSQL function trg_new_rows() line 8 at SQL statement
, Time: 0.000000s

Although when I check the organization table I can't find any abbreviation having the value Tivoli1. Any advice please?

This is the create and insert queries for table organization:

CREATE TABLE Organization
(Abbreviation VARCHAR(12) PRIMARY KEY,
 Name VARCHAR(80) NOT NULL,
 City VARCHAR(35) ,
 Country VARCHAR(4) ,
 Province VARCHAR(35) ,
 Established DATE,
 CONSTRAINT OrgNameUnique UNIQUE (Name));

 INSERT INTO organization VALUES ('EFTA','European Free Trade Association','Geneva','CH','Geneve','1960-01-04');
 INSERT INTO organization VALUES ('EIB','European Investment Bank','Luxembourg','L','Luxembourg','1957-03-25');
 INSERT INTO organization VALUES ('CERN','European Organization for Nuclear Research','Geneva','CH','Geneve','1953-07-01');
 INSERT INTO organization VALUES ('ESA','European Space Agency','Paris','F','Ile de France','1973-07-31');
 INSERT INTO organization VALUES ('EU','European Union','Brussels','B','Brabant','1992-02-07');
 INSERT INTO organization VALUES ('FAO','Food and Agriculture Organization','Rome','I','Lazio','1945-10-16');

I'm using navicat for postgresql.


texts: When performing a cross-database transaction, in which transaction log(s) is the information stored?

texts:
Given the following snippet:

-- error checking omitted for brevity
begin tran

exec database1..my_stored_procedure
exec database2..my_other_stored_procedure

if (@@error <> 0)
  rollback

commit

Into which database's transaction log(s) will the transactional information be inserted?

I would expect both logs would get all the data, since it wouldn't make sense if you attempted to replay database1's transaction log and it only affected that DB. I'd also expect that you would be unable to replay database1's transaction log on a server where database2 wasn't present, and vice versa.

.. but I'm open to corrections!


texts: Creating Linked Server Object to same instance

texts:
I'm trying to create a Linked Server Object back to the same SQL Express 2014 server instance (see below for the context), but I'm hitting the following error...

Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "NewServer".
OLE DB provider "SQLNCLI11" for linked server "NewServer" returned message "Invalid connection string attribute". (Microsoft SQL Server, Error: 7303)

I've used the following configuration...

Linked Server: NewServer
Server Type: Other data source
Provider: Microsoft OLE DB Provider for SQL Server
Product name: {empty}
Data source: LOCALHOST\SQLEXPRESS (have also tried actual server name)
Provider string: SQLNCLI
Security, Login: Using the login's current security context (have also tried using this security context and providing sa/password)

Why am I getting this error, and how do I fix it?
Is it because I'm trying to create a linked server object back to itself?

For context, the reason I'm doing this is due to a client who's UAT and Production environments have a linked server with which we need to interact.
As a result I need to create stored procedures on my local development machine that use the [NewServer].[Database].[dbo].[Table] name... so that the sproc can be stored in the repo and easily deployed via comparison tools.  But it won't let me create/update the sprocs without the valid data source.
I thought the simple solution would be to create the [Database].[dbo].[Table] on my local SQL Express 2014 instance and add a linked server back to itself using the [NewServer] name.
Is the only option to create a new SQL instance?


texts: Inherited Database with Corruption and a Bad Checksum

texts:
We have inherited a corrupted database 'DBName', with no good backup available to us. The corruption apparently happened some months ago.

We have tried a few thing:

DBCC CHECKDB(DBName); --Shows no errors

BACKUP DATABASE DBName TO DISK = 'D:\Backup\DBName.BAK' WITH CHECKSUM

This backup fails with detected error:

Msg 3043, Level 16, State 1, Line 3 BACKUP 'DBName' detected an error
on page (1:188137) in file 'D:\Data\DBName.mdf'. Msg 3013,
Level 16, State 1, Line 3 BACKUP DATABASE is terminating abnormally.

This puts a row into msdb.dbo.suspect_pages with event_type = 'Bad Checksum'

dbcc traceon (3604)
DBCC PAGE('DBName',1,188137,0) WITH TABLERESULTS
dbcc traceoff (3604)

ParentObject    Object  Field   VALUE
BUFFER: BUF @0x0000000E931A54C0 bpage   0x00000007D1BC6000
BUFFER: BUF @0x0000000E931A54C0 bhash   0x0000000000000000
BUFFER: BUF @0x0000000E931A54C0 bpageno (1:188137)
BUFFER: BUF @0x0000000E931A54C0 bdbid   64
BUFFER: BUF @0x0000000E931A54C0 breferences 0
BUFFER: BUF @0x0000000E931A54C0 bcputicks   0
BUFFER: BUF @0x0000000E931A54C0 bsampleCount    0
BUFFER: BUF @0x0000000E931A54C0 bUse1   42902
BUFFER: BUF @0x0000000E931A54C0 bstat   0x809
BUFFER: BUF @0x0000000E931A54C0 blog    0x215a215a
BUFFER: BUF @0x0000000E931A54C0 bnext   0x0000000000000000
PAGE HEADER:    Page @0x00000007D1BC6000    m_pageId    (43690:-1431655766)
PAGE HEADER:    Page @0x00000007D1BC6000    m_headerVersion 170
PAGE HEADER:    Page @0x00000007D1BC6000    m_type  170
PAGE HEADER:    Page @0x00000007D1BC6000    m_typeFlagBits  0xaa
PAGE HEADER:    Page @0x00000007D1BC6000    m_level 170
PAGE HEADER:    Page @0x00000007D1BC6000    m_flagBits  0xaaaa
PAGE HEADER:    Page @0x00000007D1BC6000    m_objId (AllocUnitId.idObj) -1431655766
PAGE HEADER:    Page @0x00000007D1BC6000    m_indexId (AllocUnitId.idInd)   43690
PAGE HEADER:    Page @0x00000007D1BC6000    Metadata: AllocUnitId   -6148914691236560896
PAGE HEADER:    Page @0x00000007D1BC6000    Metadata: PartitionId   0
PAGE HEADER:    Page @0x00000007D1BC6000    Metadata: IndexId   -1
PAGE HEADER:    Page @0x00000007D1BC6000    Metadata: ObjectId  0
PAGE HEADER:    Page @0x00000007D1BC6000    m_prevPage  (43690:-1431655766)
PAGE HEADER:    Page @0x00000007D1BC6000    m_nextPage  (43690:-1431655766)
PAGE HEADER:    Page @0x00000007D1BC6000    pminlen 43690
PAGE HEADER:    Page @0x00000007D1BC6000    m_slotCnt   43690
PAGE HEADER:    Page @0x00000007D1BC6000    m_freeCnt   43690
PAGE HEADER:    Page @0x00000007D1BC6000    m_freeData  43690
PAGE HEADER:    Page @0x00000007D1BC6000    m_reservedCnt   43690
PAGE HEADER:    Page @0x00000007D1BC6000    m_lsn   (-1431655766:-1431655766:43690)
PAGE HEADER:    Page @0x00000007D1BC6000    m_xactReserved  43690
PAGE HEADER:    Page @0x00000007D1BC6000    m_xdesId    (43690:-1431655766)
PAGE HEADER:    Page @0x00000007D1BC6000    m_ghostRecCnt   43690
PAGE HEADER:    Page @0x00000007D1BC6000    m_tornBits  -1431655766
PAGE HEADER:    Page @0x00000007D1BC6000    DB Frag ID  1
PAGE HEADER:    Allocation Status   GAM (1:2)   ALLOCATED
PAGE HEADER:    Allocation Status   SGAM (1:3)  NOT ALLOCATED
PAGE HEADER:    Allocation Status   PFS (1:186024)  0x40 ALLOCATED   0_PCT_FULL
PAGE HEADER:    Allocation Status   DIFF (1:6)  NOT CHANGED
PAGE HEADER:    Allocation Status   ML (1:7)    NOT MIN_LOGGED

It appears that the page has been deallocated, because object ID = 0

The following command does not resolve the error:

DBCC CHECKDB ('DBName', repair_allow_data_loss)

We would appreciate some help in resolving this problem since (as noted above) the backups are all failing.


texts: Deny Delete to An Active Directory Account in Sql Server

texts:
I want to deny an AD account to delete any record in sql server. How can i do that?
DENY DELETE TO [usergorup\username] did not work. Is there another way?


texts: how to retrieve rows of different dates using IN clause

texts:
My table structure:

+-----------------+--------------+------+-----+-------------------+----------------+
| Field           | Type         | Null | Key | Default           | Extra          |
+-----------------+--------------+------+-----+-------------------+----------------+
| id              | int(11)      | NO   | PRI | NULL              | auto_increment |
| user            | varchar(100) | NO   |     | NULL              |                |
| uniq_name       | varchar(100) | NO   |     | NULL              |                |
| info_name       | varchar(100) | NO   |     | NULL              |                |
| delay_time      | int(11)      | NO   |     | NULL              |                |
| track_time      | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+-----------------+--------------+------+-----+-------------------+----------------+

I have to retrieve data from table for past seven days, not summed one. Data for a specific date, for this right now I have to fired same query seven times using like operator:

SELECT COUNT(DISTINCT `user`) FROM `tracks` WHERE `track_time` LIKE '2012-11-12%';

Which is not a good way to do. I am trying to do myself, also discussed on forums , it might be done with IN operator or using date1 < date2 < ....date for past seven days. For this, i am using a function in PHP in which I am supplying a date parameter, but query will take lot of time to display result on web. Please have a look on this situation.


texts: Complex MySQL Query returning almost all the correct rows

texts:
I've created a SQLFiddle with my schema and some test data. The calculated columns aren't working for some reason in SQLFiddle, but they come out fine in my MYSQL Workbench. Assume they work correctly.

I've gotten this far with my query:

SELECT DISTINCT o.orderid,i.invoiceid,i.subinvoicenumber,stockingorderid AS oistockingorderid,

IF(stockingorderid > 0, 0, po.purchaseorderid) AS purchaseorderid,subponumber,po.paidvia,dropshipfee,i.taxstate,

COALESCE(
    (SELECT mfrname
        FROM cs_products.tblstockingorders so
        WHERE so.stockingorderid=oistockingorderid),
    po.mfrname)
AS source,

(i.shipping + i.surcharge +
    (SELECT SUM(additionalshipping * quantity) AS additionalshipping
        FROM cs_products.tblorderitems
        WHERE invoiceid=i.invoiceid))
AS shipping,

CAST(IF(o.paymentmethod=2, CONCAT('Check: ', i.checknumber),o.paymentmethod) AS CHAR) AS invoicepaidvia,

COALESCE(
    (SELECT (SUM(cost * quantity) * IF(so.mfrdiscount > 0, 1 - so.mfrdiscount, 1)) AS cost
        FROM cs_products.tblorderitems oi
        JOIN cs_products.tblallocations a ON oi.orderitemid=a.orderitemid
        JOIN cs_products.tblstockingorders so ON a.stockingorderid=so.stockingorderid
        WHERE a.stockingorderid=oistockingorderid),
    (SELECT (SUM(cost * quantity) * IF(po.mfrdiscount > 0, 1 - po.mfrdiscount, 1)) AS cost
        FROM cs_products.tblorderitems
        WHERE purchaseorderid=po.purchaseorderid))
    AS grosscost,

(SELECT SUM(price * quantity)
    FROM cs_products.tblorderitems
    WHERE invoiceid=i.invoiceid)
AS grossprice

FROM cs_products.tblorders o
    JOIN cs_products.tblinvoices i ON o.orderid=i.orderid
    #ordertype of 0 means the order came from the website
    LEFT JOIN cs_products.tblpurchaseorders po ON o.orderid=po.orderid AND IF(o.ordertype<>0, subinvoicenumber=subponumber, subinvoicenumber=0 AND subponumber>=0)
    LEFT JOIN cs_products.tblallocations a ON a.orderid=o.orderid

This gets me 95% of the way there. As you can see, for OrderId 1287, it shows two records with stockingorderids and none with a purchaseorderid even though a purchase order has been attributed to 1287. What I'm expecting to see for the missing row is something like:

1287|276|0|NULL|194|0||0|0|'Quality Fabricators'|357.53|0|781.43|11917.70

Again, don't worry about the calculated columns, I just want the record to appear. I feel like I'm missing the smallest thing, but I don't know what it is.

Thank you!


texts: Remove duplicates in sequence based on keys

texts:
I am looking to remove duplicates on a row by row comparison based on keys Member and Group and data sorted ASC on Member, Group and StartDate. Just return the records in the select query and not delete in the table.

I am basically looking at a row by row comparison to eliminate the duplicates based on the 2 keys and value of the Status column with the data sorted by Member, Group and Start_date.

Sample data:

Member  Group   Status        Start_date        End Date
1         2     Active       4/12/2011 01:19    NULL
1         2     Active       4/12/2011 02:19    NULL
1         2     Inactive     4/12/2011 03:19    NULL
1         2     Inactive     4/15/2011 04:19    NULL
1         2     Inactive     4/16/2011 05:19    NULL
1         2     Pending      4/16/2011 11:23    NULL
1         2     Active       4/16/2011 11:25    NULL
1         2     Pending      4/16/2011 11:27    NULL
11        22    Inactive     4/16/2011 11:27    NULL

Expected Output:

Member  Group   Status        Start_date        End Date
1         2     Active       4/12/2011 01:19    NULL
1         2     Inactive     4/12/2011 03:19    NULL
1         2     Pending      4/16/2011 11:23    NULL
1         2     Active       4/16/2011 11:25    NULL
1         2     Pending      4/16/2011 11:27    NULL
11        22    Inactive     4/16/2011 11:27    NULL

DDL and DML:

CREATE TABLE `temp1` (
  `MEMBER_ID` int(10) unsigned NOT NULL,
  `GROUPIE_ID` int(10) unsigned NOT NULL,
  `STATUS` varchar(12) CHARACTER SET utf8 NOT NULL,
  `START_DATE` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'Active', '2011-04-12 01:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'Active', '2011-04-12 02:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'InActive', '2011-04-12 03:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'InActive', '2011-04-15 04:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'InActive', '2011-04-16 05:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'Pending', '2011-04-16 06:23:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'Active', '2011-04-16 07:25:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'Pending', '2011-04-16 08:27:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (1,2,'InActive', '2011-04-17 09:27:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (11,22,'Active', '2011-04-12 01:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date )
values (11,23,'InActive', '2011-04-13 02:19:51 PM', NULL);


texts: How to find user who Drop any thing without auditing applied on database

texts: In my scenario, I have a database with no flashback on, Only Archivelogs are active. No Auditing applied on database. 3 days ago some one drop all triggers, views, sequence from a specific user. Now is there any way to find out who did this? Can I go 3 days back without flashback on?


texts: InnoDB Deadlocks Involving Currency Transactions

texts:
We're dealing with InnoDB deadlocks that are affecting our virtual transaction system.

LATEST DETECTED DEADLOCK
150210 20:51:03
*** (1) TRANSACTION:
TRANSACTION 0 4240405649, ACTIVE 0 sec, process no 1250, OS thread id 140378832058112 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 368, 2 row lock(s), undo log entries 1
MySQL thread id 2069114, query id 21027980 localhost 127.0.0.1 dbname Updating
UPDATE users SET points = points + 7000 WHERE id = '47112'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1823 page no 860 n bits 104 index `PRIMARY` of table `dbname`.`users` trx id 0 4240405649 lock_mode X locks rec but not gap waiting

[...]

*** (2) TRANSACTION:
TRANSACTION 0 4240405339, ACTIVE 1 sec, process no 1250, OS thread id 140378835252992 fetching rows, thread declared inside InnoDB 294
mysql tables in use 1, locked 1
683 lock struct(s), heap size 63472, 23093 row lock(s), undo log entries 22383
MySQL thread id 2069041, query id 21009690 localhost 127.0.0.1 dbname Updating
UPDATE users SET session_active = 0 WHERE session_lastclick < 1423618562
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1823 page no 860 n bits 104 index `PRIMARY` of table `dbname`.`users` trx id 0 4240405339 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Essentially, we have a users table that contains a column session_lastclick (which is the timestamp of the user's last click) and another called points which is the user's currency balance.

Cron is actively setting sessions to active/inactive via minutely jobs and a users points balance could be updated by any number of actions in real time.

There are obviously application-level things to resolve. But at a database level, how should the data be segmented out to get rid of these deadlocks? A new table dedicated to user point balances, a separate user session table, or both?

I've to give an answer to the application level. Thanks in advanced.


texts: How to, efficiently, search through many sql dump files of really big size

texts: We have an ads website that the uncompressed(plain .sql file) size of the database is about 30gb. Until now when a user was deleting his classified the classified was completely getting wiped out from the database.We were always taking a full backup of the database every day and thus we have hundreds of this 30gb sql files . Sometimes there are users that asks us to give them some info about a classified that was deleted like 2 months ago(they usually dont know the exact date) and we have to search thought this back up files in order to find it.Is there a tool designed specifically for this task? I dont mean just a text editor that can handle big files , I mean a tool that can actually load the full database and let us do queries on it(maybe just simplified queries that involve only one table) and maybe even let us search many of the backup files at the same time(and no, loading the database in a sql server is not an option too since we need to quickly search thought tens of this backups).


texts:
While using the ceil() in postgresql, I found some strange behavior of it, Please see SQL :

Select
     *,
     ceil( (550*0.5)/100 ),
     ceil( (58*5)/100 ),
FROM
   XYZ

If I focus on the output of ceil functions it is strange :

ceil( (550*0.5)/100 ) Returning 3 ( Which converts 2.75 to 3 )
ceil((58*5)/100 ) Returning 2 ( Which converts 2.9 to 2 )

Why 2nd statement returning 2 if it is closed to the greater integer 3 ?


texts: Query to view failure detail for a given sql agent job step?

texts:
I have a sql agent job step that failed for a given date / time.  Is there an MSDB - TSQL query I can run to view the step failure reason given that :

I know the job name.
I know when the job failed.
I know the step number


texts:
I've a table for save sub tasks data.
some time we change status of sub tasks, and i need both of them id and status in @JsonStatus parameter.
I wrote a stored procedure like below :
Declare @JsonStatus varchar(1000)       = '[{"id":147},{"id":151,"status2":2}]'
        ,@ID int

--TRUE FORMAT IS : '[{"id":147,"status":3},{"id":151,"status":2}]'
        Update dbo.TaskSub
        Set StatusID = JC.StatusID
        From OpenJson(@JsonStatus)
        With(   ID                  bigint          'strict $.id',
                StatusID            tinyint         'strict $.status'
            ) as JC
        Where dbo.TaskSub.id = @ID
            AND dbo.TaskSub.ID = JC.ID

Some time developers not send a parameters but i cant raiserror or etc for them, id and status is mandatory.
I tried use strict statement by read this article
if use select its work well like below :
Select *
From OpenJson(@JsonStatus)
With(   ID                  bigint          'strict $.id',
        StatusID            tinyint         'strict $.status'
    ) as JC

but not worked in update and result is :
(0 rows affected)


texts: how to query multiple junction tables to create 1 record

texts:
In normalizing data for a Contract bridge tournament, it seems like I am ending up with 13 junction tables. Each tournament will have 2 winners, 2 runner ups, and 4 semi-finalists, and a Tournament director, Assistant Tournament director, and maybe 1-2 Substitutes. For the sake of simplicity, I am working with tables as shown here:

I am trying to create a query which will produce the following result.

Knowing only basic SQL, Querying 1 junction table is not a problem, but 2 or more junction tables has me stumped. Is there a better way to structure my data? I will need to do Insert, Update, and Deletes also. Any help or suggestions will be most welcome.


texts: Accessing an external database through pgAdmin installed on my local machine?

texts:
I want to query a database stored in an external location. Up to now, I was using Microsoft Access for querying, but due to limitation of 2 GB output file, I am trying to switch to another DBMS, PostgreSQL.

How do I connect to the external database using pgAdmin, installed on my machine?


texts: Dropping the automatically created NT AUTHORITY and NT SERVICE accounts

texts:
So I recently moved jobs - one piece of code I've spotted in our build scripts for new SQL Server installations is below.

IF EXISTS ( SELECT  *
            FROM    [sys].[syslogins]
            WHERE   [name] = N'NT AUTHORITY\SYSTEM' )
    BEGIN
        DROP LOGIN [NT AUTHORITY\SYSTEM];
    END

IF EXISTS ( SELECT  *
            FROM    [sys].[syslogins]
            WHERE   [name] = N'NT SERVICE\SQLWriter' )
    BEGIN
        DROP LOGIN [NT SERVICE\SQLWriter];
    END

IF EXISTS ( SELECT  *
            FROM    [sys].[syslogins]
            WHERE   [name] = N'NT SERVICE\Winmgmt' )
    BEGIN
        DROP LOGIN [NT SERVICE\Winmgmt];
    END
GO

These accounts are created during the SQL Server installation process by default.

Are dropping the above logins recommended? Are there any side effects this could cause? What are these logins used for?

I read Can I delete NT SERVICE\SQLWriter and NT SERVICE\Winmgmt logins? but it just isn't concrete enough - I can see what they're needed for but very little else. Do they need sysadmin access? etc.

As an example (taken from Configure Windows Service Accounts and Permissions):

  Local System is a very high-privileged built-in account. It has extensive privileges on the local system and acts as the computer on the network. The actual name of the account is NT AUTHORITY\SYSTEM.

How should I read this? Should it be left with these "high" privileges?


texts: How can I achieve conditional page breaks in SSRS 2016

texts: I am using SSRS 2016 and Report Builder. I need the page breakinging with a group to be conditional on the value of a parameter. Does anyone know if this is even possible?