texts: SQL Server 2008 R2 - VLDB Backup Strategies

texts:
I have a 200GB database running on SQL Server 2008 R2 Enterprise Edition.  I just recently partitioned it in order to archive 11 months of history, so it was pushing 400GB prior to archiving.

Our company has 2 data centers in order to implment DR solutions.  Most of the databases are at DC1 where there is tape backup infrastructure, where DC2 doesn't have tape backup capability.

This VLDB of 200GB resides at the DC2 location with no tape backup infrastructure.  Our server team stopped doing tape backups about a year ago when the database .bak file exceeded 90GB and became too large to snap back to DC1 to backup to tape.  I keep 2 disk copies of the .bak file, but still am concerned if a recovery is needed that requires going back more than 2 days.

I am looking for solutions.  I thought I could setup a log shipping solution from DC2 to DC1, then each night stop the log shipping and run a backup of the secondary standby, but am concerned it could through the ability to start log shipping into a tiz.  I have been doing a lot of research and see that it could cause a problem, but if a standby can be connected to and read from, you'd think a backup could be done.

The database grows by 30GB a month with a daily average of 1-2GB a day, so the traffic to log ship from DC2 to DC1 should be manageable.  It is a vendor supplied database, so we try very hard not to modify it to support our environment, although, I did just partition it with the vendor's approval.

Anyone have any ideas of how best to save .bak files to tape from a location without tape infrastructure?


texts: Is it possible to match two different Data types in PostgreSQL?

texts:
I want to compare two field values from different tables of PostgreSQL both are of different data types that is

CREATE TABLE IF NOT EXISTS departments(
  _id SERIAL NOT NULL UNIQUE PRIMARY KEY,
  departmentName text,
  active BOOLEAN
);

CREATE TABLE IF NOT EXISTS departments_users(
  _id SERIAL NOT NULL UNIQUE PRIMARY KEY,
  department_id integer[]
);

INSERT INTO departments(_id,departmentName)
VALUES
    ( 1, 'aa' ),
    ( 2, 'bb' ),
    ( 3, 'cc' ),
    ( 4, 'dd' );

INSERT INTO departments_users(_id,department_id)
VALUES
  ( 1, ARRAY[1,2,3] );

in the above 2nd table am using department_id which are all present in departments table but in second table am storing it in array if i want get the data which has the common department id
expected output should be:

_id | departmentName
-----------------------
1   | aa
2   | bb
3   | cc

So is it possible to match 2 different datatypes?


texts: Drop the user from Read only Secondary database Always ON server

texts:
How to drop a user from the secondary database in an AG when it is in read only mode and synchronizing in SQL Server 2012?

The login and user have been deleted from the primary database, and we are trying to recreate the login on primary.  But when we are trying to create the login on primary, it is throwing an error saying it already exists.

We suspect due to the user being present on the secondary it is not allowing it to create on the primary. Hence we need a way to create the login by deleting the user in secondary.

Any ideas on how do we do that?


texts: Large MySQL import stuck at "Waiting for table flush"

texts:
Two weeks ago (!) I started a large import (dump sql file is 540GB). Now the import is stuck at this state:
"show processlist" shows: Command = "Field List", State="Waiting for table flush".
Looking at the record count for a table I selected randomly, I see zero rows, whereas in the source DB there are millions of rows, so the import is clearly not done. The .ibd file for that table is tiny, whereas on the source it's several GB. For other tables this is not the case, i.e. the .ibd files are very large on the target DB.
The server shows heavy swap activity, with the load being around 5.
Following suggestions I got on this site I started the import with these commands:

set autocommit=0;
set unique_checks=0;
set foreign_key_checks=0;

...and ended the import with these commands:

commit;
set unique_checks=1;
set foreign_key_checks=1;

What is going on now? Must I wait?


texts: How to find out SQL Server options updated

texts:
Two days ago, we started having problems on a production server executing stored procedures.
No problems on any other servers, only production.
Our investigations found out that the server connection properties XACT ABORT were set to ON, and this option is ON only on the production server.
Now we want to understand when, and possibly who, changed this option. We are looking at the system tables but till now without any luck.
Is there someone who knows if there is a way?


texts: I am having MySQL database with all my tables as InnoDB with file_per_table config on. But Still I m seeing huge ibdata file ( ~50GB). All tables are having .ibd file as well. This is a machine I have created by taking dump and loading. What is a reason for this.


texts: Where to store administrative (DBA) data?

texts:
I am a SQL Server DBA by trade, investigating MongoDB for a project.  One of the conventions we use is to store configuration-style information in a SQLDBA database on each server (index rebuild thresholds, backup exceptions, etc).

I am wondering if this same technique would be useful here. My question is:

Should/could I use the admin or local database for this information, or should I create a new database to store essentially a single collection and document?
Would this change in a sharded/replica-set environment?

We have a central hub SQL server/database for sure, but we also store some configuration data locally on each server to drive certain processes.  We wouldn't want these to fail because a server couldn't contact the hub.


texts: SQL Server 2016 partitioning with columnstore

texts:
Environment is SQL 2016/SP1/CU1 Enterprise on Windows 2012 R2 Standard, and I'm running tests on using paritioning in combination with clustered columnstore.

For one large table (300 million rows), data was first sorted by a date column, bulk inserted into the partitioned table, and then a clustered columnstore index was created.

The data type for the partitioning column is date, and this column exists in the smaller parent table, and the larger partitioned table.

There are a few joins from the large table to other smaller tables.

When the predicate is:
WHERE parent.datecolumn BETWEEN @date1 and @date2

I see rowgroup elimination, but no partition elimination.

When the predicate is:
WHERE parent.datecolumn BETWEEN @date1 and @date2
  AND partitioned.datecolumn BETWEEN @date1 and @date2

I do not see rowgroup elimination, but I do see partition elimination.

I'm not sure if this is expected behavior, but didn't entirely make sense to me.


texts: MS SQL explicit rowversion column overhead?

texts:
For MS SQL servers, when I declare something like rv rowversion in the table definition, I have access to the rowversion value of the record. Does it have any impact to the size of the table?
If I understand it correctly, the rowversion value is stored for the record independently whether I make the column explicit, or whether it is hidden. So, making the column explicit should not lead to the increase of the size of the record (of the table). Is that assumption correct?


texts: How to design a table with relationships to a group of users or individual users?

texts:
I have a feature in my application that allows setting a set of permissions for groups of users or individual users. Considering that a group contains zero or more users and a user belongs to one or more groups, how should I design my permissions table so I'm able to reference the group id or individual user ids?

A permission set can have both groups and users referenced at the same time, this way if a user is added to a group that is part of a permission set, he/she automatically gets the same permission set.


texts:
Lets say, I have a query,

SELECT * FROM MyTable WHERE MyParam = 0 OR MyColumn = MyParam

Here MyParam is parameter and optional. So, it only check MyColumn = MyParam if MyParam is not 0. But our DBA is saying OR will makes it slow and db will suffers. Another option is,

IF MyParam = 0
SELECT * FROM MyTable WHERE MyColumn = MyParam

The problem with this approach is that we have lot of optional parameters. So, our query become very very big. Another option is CASE.

So what you guys suggest. I am talking about in general whether Oracle or SQL Server.


texts:
SELECT FIRSTNAME,LASTNAME, EMAIL
   FROM CUSTOMERS
   WHERE REGEXP_LIKE(EMAIL,'[A-Z0-9._%-]{1,10}@[a-z0-9._%-]{1,5}\.[net|com]','i');

I know this is not practical or make a ton of sense, but its a constraint I'm working with: email address with upto 10 characters prior to @ followed by upto 5 characters, then a '.' and finally the .net or .com extension. This works for the most part except the 1-10 constraint on the first half does not appear to do what I'd like.
It pulls in pretty much any number characters prior to the '@'.
For example it should accept jreed but not jodywilliamreed as the later is more than 10 characters. Anyone see what I've done here that would cause this problem?
edit: I'm using sql developer with oracle


texts: How do you convert IBM DB2's recursive increment to MySQL version?

texts:
DB2 Queries:

    WITH
    TABLE1 (YEAR_END) as( VALUES
    (2011))
    ,
    TABLE2
    (YEAR_END) AS ( VALUES(2011))
    ,
    TABLE3 (YEARS) AS ( SELECT 0+2008 FROM TABLE1 T1, TABLE2 T2
    union all
    select YEARS+1 from TABLE1 T1, TABLE2 T2, TABLE3 T3
    WHERE  YEARS < 2011
    )

select YEARS from TABLE3

Result:

2008
2009
2010
2011

how to change above DB2 statement to Mysql statement?


texts:
I need to change the order of columns in my table (Oracle and SQL Server). Reason is that some of our software calls select * and insert without specifying columns. Refactoring will be too expensive so I need to solve it on the database level.

My idea is to create a new view which will encapsulate the table and just change the order of columns. Current table will be renamed and new view will have the original name. I found here that encapsulating a table with a view is some kind of database design pattern.

Here are my questions:

Do I lose performance when I encapsulate a table with a view and call the view only?
Do you see any risk with naming the view (there will not be V_ prefix)?
Do you see any other advantage / disadvantage in encapsulating a table with a view?


texts: Treating (A, B) and (B, A) as same pair and getting max row per pair

texts:
Following is my table structure:

"id"           = bigint;
"sender_id"    = bigint, FK;
"receiver_id"  = bigint, FK;
"message"      = character varying;
"sent_ts"      = timestamp with time zone;
"delivered_ts" = timestamp with time zone;
"read_ts"      = timestamp with time zone;
"version_no"   = bigint;
"date_created" = timestamp with time zone;
"date_updated" = timestamp with time zone;

For Eg:
If I've following data:

id | sender_id | receiver_id | message | date_created
1  | 1         | 2           | hi      | 2016-05-21
2  | 1         | 2           | hello   | 2016-05-22
3  | 2         | 1           | :)      | 2016-05-23
4  | 1         | 2           | :D      | 2016-05-24
5  | 2         | 1           | hehehe  | 2016-05-25
6  | 1         | 3           | wassup! | 2016-05-26
7  | 3         | 1           | \m/     | 2016-05-27
8  | 3         | 2           | :D:D    | 2016-05-28

I need to find last message record from whichever user (sender, receiver) pair ie considering (1,2) and (2,1) as a single pair.

So, if I pass 2 as sender_id and receiver_id, I need following output:

id | sender_id | receiver_id | message | date_created
5  | 2         | 1           | hehe    | 2016-05-25
8  | 3         | 2           | :D:D    | 2016-05-28

Following is so far what I've tried:

SELECT sender_id, receiver_id, MAX(date_created) date_created
FROM uconvs
WHERE sender_id = 2 OR receiver_id = 2
GROUP BY sender_id, receiver_id
ORDER BY date_created DESC;

Right now I'm getting following output:

sender_id | receiver_id | date_created
1         | 2           | 2016-05-24
2         | 1           | 2016-05-25
3         | 2           | 2016-05-28

I'm unable to consider (1,2) and (2,1) as a single pair and also unable to extract the message as I've used group by.

Let me know if my question is not clear.
I'm using postgresql 9.4+.

What do I need to do to get the above output? I don't know pl/sql much, but if it's the optimal solution that would also do.

Thanks in advance.


texts: Expected function to return very few rows, but returns all rows

texts:
I am creating a Postgres/postgis function that is suppose to cut an input-geometry according to a 'hard-coded' table's geometry. The hard-coded table is called j.borders  and its geometry attribute is called geom. If the input-geometry intersects several geometries (rows) from j.borders, j.borders geometry is collected into one geometry (using st_union), which is then used to cut the input-geometry using st_difference. If the input-geometry doesn't intersects  j.borders geometry, the input-geometry is returned un-modified.
The function I have created behaves as i want it to, however:
I dont understand why it behaves like this
the function in question:
CREATE OR REPLACE FUNCTION public.clip_geom(
    IN in_geom geometry,
    OUT out_geom geometry)
-- can be called like SELECT public.clip_geom(geom) FROM a_table

  RETURNS geometry AS
$BODY$
BEGIN

    SELECT
    st_multi( -- st_multi makes sure the geometry is always the same "type"
    COALESCE(
    ST_DIFFERENCE(in_geom,st_union(brdr.geom)), -- cutting the geometry
    in_geom -- not cutting the geometry
    ))
    INTO out_geom
    FROM j.borders brdr
    WHERE ST_INTERSECTS(in_geom, brdr.geom); -- if the two geometries intersects

END;
$BODY$
  LANGUAGE plpgsql;

If you give this function a table of 5 rows with geometry and 2 of them intersects the border-geometry,  it then returns all 5 rows, where 2 have been clipped to the border.
the select statement is followed by a where-clause which states the input-geometry and border-geometry have to intersect. If the two geometries do not intersect, nothing is returned, correct?
but why does the function then return the unmodified geometry? Does it have something to do with the COALESCE?


texts: Searching from Database having billion records, which database should i use

texts:
We are having 1 billion phone numbers and we have to search them against numbers which we send for promotion to filter out which are not promotional registered. That is we have lets say 1 million record which we need to search against 1 billion database records.

What would be best and optimized way to search these records and which database should we use?

We are currently using SQL Server and in a batch of 10000 searches it gives result in 20 minutes.

We want real time searching.


texts: Custom error code class and numbers for Postgres stored functions

texts:
I'm writing a collection of stored functions in Postgres 11.5, and want to RAISE EXCEPTION when various pre-conditions are not met. For example, a null or empty string with a string is required, or an integer parameter that is out of range, etc.

I can RAISE EXCEPTION and provide details, hints, and a message...but what range should I use for the error code? I've checked the docs, but didn't find any guidance here:

https://www.postgresql.org/docs/11/errcodes-appendix.html

I searched on StackOverflow, found a similar question from years ago...but not a clear answer.

Is there some block or prefix that's safe or conventional to use for custom error codes coming back from stored functions/procedures?


texts: Run MongoDB under a Windows Managed Service Account

texts:
In Windows environment can I run MongoDB under a Windows Managed Service Account? the main reason is there is a company policy about PWD expiration each 60 days, and we want to use Managed Service Account as this allow password updates transparent for the services using this type of account
Best regards


texts:
I am very new to sql create function

i wanted to check data column value at table

the purpose is to return full table where data is null , <0 , >1

the problem is i am trying make data is dynamic so the table column more than 10 or more than  that ( can 50 column)

my sql syntax is

CREATE FUNCTION Checkdatavalue
(
     @tables varchar(50)
)
RETURNS table
AS
BEGIN
     DECLARE @colId int, @sql nvarchar(max),@colName nvarchar(400), @ssql nvarchar(MAX)

        DECLARE oCur CURSOR FOR
            SELECT [name] AS colName, colId
               FROM syscolumns
            WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = @tables )
            and xtype = '56'

            SET @ssql = ''

            OPEN oCur

            FETCH NEXT FROM oCur INTO @colName, @colId
            WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @ssql = @ssql + ' [' +@colname + '] is null or [' +@colname + '] < 0 or [' +@colname + ']>1 or'
                FETCH NEXT FROM oCur INTO @colName, @colId
            END

            SET @ssql =  left (@ssql,len(@ssql)-3)
            set @sql = 'select * from '+@tables+' where ' + @ssql
            --select   @sql

           close ocur;
        deallocate ocur;
            return @sql
END
GO

the @sql statement like this

 select * from tablename where  [column1] is null or
 [column1] < 0 or [column1]>1 or [column2] is null
 or [column2] < 0 or [column2]>1 or etc...

there is following error like

A RETURN statement with a return value cannot be used in this context
Incorrect syntax near 'BEGIN'.


texts: How to determine how much storage space a column takes in Oracle?

texts:
How to determine how much storage space on the disk a column takes in a table in Oracle?

I'm running Oracle 11gR2 11.2.0.3.


texts:
I migrated two applications to the new server. One of the them works without any issues but the other ran into back end sql permission problems like

  The EXECUTE permission was denied on the object
  ‘aspnet_CheckSchemaVersion’

I could find figure why one application would work and the other would not. They both run under the same application pool but uses different database. I didnont have to do anything for the one that to work, for the other I had to grant permission on [Network Service] user for each table, stored procedure which the application access.

Well I have resolved the problem now by making the two databases securables for Network Service. I dont know why I had to do this for one application while the other does not need it. My question is

What is SQL Server Securable in SQL Server Management Studio? You basically access them when you click on user -> Properties then go to second page on the left.

What is it used for? What is its proper use?

From Microsoft

  Securables

Lists the securables on which specific permissions have been granted or denied to  this principal.


texts: Is it a good idea to move high-volume and highly-accessed tables to a separate database?

texts:
Ours is a web-based application (built on multi-tenant architecture) running PostgreSQL v9.1.3.  There are about 450 tables in our application, out of which 2-3 tables, specific to a module in the application, have huge volume of records stored compared to other remaining tables and are heavily accessed (both read and write operations) by application Users.

To give a picture/statistics on the volume of records available, currently there are 8 million records in one table and 3 million records in another table.  We're expecting a immediate increase/traffic in the volume of transaction (again both read and write transactions) to these tables in the near future, since we're coming up with some exciting features within this specific module.

My question with requirements are,

because we're expecting a huge traffic to this specific module, we don't want to let down the Users accessing other modules in the application to be affected because of any performance issues that it may cause, though.
separating/isolating heavily accessed tables is one solution that came off the top of my head.  Is it a good idea to separate/move to a different database? What are the pros and cons of this approach?
any solution, comment, approach, suggestion are welcome and appreciated.


texts: mysql dummy where check to utilize multi column index

texts:
I have multi-column index on col1,col2. Both columns are unsigned int with values 1 or greater. I want WHERE col2=100. I write WHERE col1>0 AND col2=100. The query should now use my index.

Does this work and what are the ramifications. How does it get optimized etc.


texts:
The source table structure is as defined in the image. I have fetched the data grouped by VesselID, with max function applied on cargodate(Date), as shown in the next image.

Now I need balance of the row fetched using group by. Please help.


texts: MySQL Client out of memory when running a simple select query from MSSQL ODBC

texts:
Table has a total of 1,120,000 rows and 20 columns. When I run a select query on mysql directly like:
select * from <tablename> where UpdatedOn > '2020-10-05'

it gives the results which is about 50+ rows.
But when I run the same from MSSQL using ODBC Connection it throws "MySQL Client Out of Memory" every single time.

The server has 8 GB RAM and no one else is using the server
Running Select top gives the results without the error
Running select with a single integer column like select id from ... throws a "MySQL Client out of memory"

What could be going wrong? I haven't been able to the issue.


texts: Fact table Primary Key: Surrogate Key or multi-column Natural Key?

texts:
What's the best practice when choosing a fact table's Primary Key (PK for brevity): Should we have an auto-incremented Surrogate Key (SK), or should we use Natural Key's (NKs) columns?

The advantage I see in using a SK is INSERT performance, which is very relevant when we have almost a million rows inserted daily, which is my case. I'm expecting almost 20M rows per day, 200M rows per month, and the table must sustain data for decades to come without needing to be split.

But query performance should be better if we have a NK as PK, and the order of the PK is well designed.

Of course we can create non-clustered indexes for queries, but that eats storage and makes INSERT even slower.

Considering that INSERT and DELETE (yes, we have that too, a lot, because data is reprocessed and new files versions must be "updated") performance is very relevant, almost as much as query performance, what's best?

Other issue to consider is that when rows are deleted a SK count doesn't decrease. With 200M rows per month, I'll have 2,4G rows per year, which int datatype won't hold, so I'll need to use bigint for PK...


texts: How to fix "The OLE DB provider "MSOLAP" has not been registered"?

texts:
Here's the code I'm using to register a linked server to SSAS:

USE [master];
GO

EXEC master.dbo.sp_addlinkedserver
    @server = N'ServerAlias',
    @srvproduct = N'',
    @provider= N'MSOLAP',
    @datasrc= N'ActualServerName',
    @catalog = N'DatabaseName';

When I attempt to run an MDX query using OPENQUERY, or if I use the Test Connection command in Management Studio after running the above code, I get the error:

  Msg 7403, Level 16, State 1, Line 1
  The OLE DB provider "MSOLAP" has not been registered.

This is pretty strange as this provider is installed as part of SQL Server.

It also fails on all other instances on the same box -- 5 total instances of 2008 R2 RTM and 2008 R2 SP1. I don't remember exactly, but 1 or 2 of the instances on the problem box may have been upgraded from 2008 to 2008 R2 at some point.
It succeeded on a couple of other boxes using 2008 R2, including one box that has about a dozen total instances of 2005, 2008 R2 and 2012. I tried both 32-bit and 64-bit with success.

I looked at this Microsoft KB article which describes the exact issue; however, there isn't a resolution that's applicable. Every other instance of this error I found on the internet was due to someone incorrectly defining the linked server, which also isn't applicable.

This is a development server (thankfully), so pretty much all options are on the table. Would a Repair fix this? It feels like something is awry in the registry.

(I'm asking for direction first, as I can't perform any maintenance myself due to lack of permissions.)


texts: Very high I/O on database during a backup maintenance plan

texts:
I've recently been having some hellacious problems with a specific maintenance plan.  I found that part of the problem was that I had Shrink and Rebuild Index in my nightly plans, those have since been removed.  All that is left is Tlog backups, Integrity check, and database backups in that order.

One of my database developers came in this morning and logged in as usual and found that our ServiceDesk software wouldn't load via webpage.  Upon looking in Spotlight, the Max I/O Wait on the ServiceDesk database was almost 55k ms.  I went in SSMS and cancelled the job manually and everything returned to "normal" and we were able to log in.

Now to my quesions....

Is this normal?
If it is normal, should I start my backups much earlier?
Should I break up the maintenance plan into smaller chunks and then stagger?

I'm an accidental DBA who is on the homegrown path of being a production DBA, so I'm relatively new.  I'm finding a passion for this stuff as late and heard this community rocks, so anything you can suggest I'd appreciate greatly. Also, if I've missed anything or you need more detail, I'll be happy to clarify.


texts: SQL Database, how to set up users saving favorite songs

texts:
I'm setting up a web page and database for SQL practice where a bunch of songs will be saved in one database table, and there will be a table of 'users' (all just be testing however...) of the site. I wanted to make a feature of allowing users to build a list of songs they like from those in the database. But I'm unsure how best to keep track of the liked songs of each user.

As far as I know there isn't an array like attribute to add to the users table to hold their linked songs list so the only things I can think to do are:

Create a table for each user upon account registration to hold their liked songs.
Create a mass table holding all songs liked by users, with an attribute of the liking user and then to display their liked songs, pull all from that table that have their name.

Are either of these sufficient? Is there some better way to this?


texts: SQL Server Agent - Will Job Run on Specific Future Date?

texts:
I have a number of SQL Server Agent jobs will different types of schedules (Daily, Weekly, Monthly).  Each of these jobs will represent an event.  For example there will be an FTP job that will transfer a file from a source and destination location every day at 7:00AM. I am trying to build a web interface into this schedule so that I can not only view job history (this can be queried rather easily using the msdb..sysjobhistory table) but also view future scheduled runs.

Therefore, I am looking to pass in a job_id and date into a stored procedures and determine if that SQL Server Agent job will run on that day. For example, If I have a job that runs every second tuesday of every month, I want the stored procedure to return 1 if I pass in 12/9/2013 (2nd tuesday of december) and -1 if I pass 12/10/2013.

I know i can get the next_schedule_run date and time from msdb.dbo.sysjobschedules but need a way to extend this.


texts:
I have a huge view -the view gathers information of players.
In a specific part the view assigns agents for players, those players are selected from table 'S' and are being assigned using a case statement.
I want to assign specific agents according to a aggregated value called total_deposit in table T.
so basically i want to say case make the agent name Andy when users from table S have max(total_deposit)within the conditions below (from table T) that is the code i tried:

        CASE
        WHEN s.Username = gs.username THEN gs.agent
    when exists (select t.username from temptest t
            where t.username = s.username
            group by t.username
             having max(t.total_deposit) >= 5000 or
                (    max(t.total_deposit) >= 1000 AND max(t.total_deposit) < 5000 )or
                      ( max(t.total_deposit) >= 500 AND max(t.total_deposit) < 1000) or
                      ( max(t.total_deposit) >= 250 AND max(t.total_deposit) < 500  )
            ) and s.Country in('Austria', 'Luxemburg', 'Switzerland', 'Lichtenstein')

                         then 'Andy'
else 'Joy'

It didn't work because if exists needs to follow with an expression..
how can i do this? thanks


texts: SSRS not sending scheduled email to specific contact

texts:
I have a specific contact for my SSRS subscriptions that does not receive any mail from SSRS.
her email is an organization email, everyone else including me are getting emails but only that specific user doesn't.

I checked ssrs logs and i see it was sent successfully:

  notification!WindowsService_0!1aa0!12/05/2019-14:15:23::
   i INFO: Notification af94f76f-d236-4ffb-b4e7-8450d206f654 completed.
    Success: True, Status: Mail sent to contact1@organization.com;contact2@organization.com;,
  DeliveryExtension: Report Server Email, Report: Notables Report (Euro),
   Attempt 0

This is what i tried so far:

recreating the subscription
recreating reports
creating a simple report with no attached files
checking all her mail folders and junk mail
restarting SSRS services
IT team checked the mail server shes not receiving the mails they say it doesn't even go through the mailing server.
She does get regular mails
sending mail from the same server via SQL works.
when sending email to a group she belongs too she still doesn't get mail.

it happens with all reports in that server
Any suggestions?

Thanks


texts: Not able to start mysqld as windows service as well as from command line in windows 8

texts:
I recently downloaded mysql-5.7.9-winx64.zip and tried to run mysqld from command line but it does not starts gives me following error.

ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

I have updated system path variable to have "D:\MySQL\mysql-5.7.9-winx64\bin"
and added mysql as system service

"D:\MySQL\mysql-5.7.9-winx64\bin" --install

It's still does not start when I restart my system.

Also when I tries to run from services.msc ==> MySQL ==> Start It gives me following error:

MySQL Services on Local Computer started and then stopped. Some services stops automatically if they are not in use by other service or programs.


texts: Getting Error ORA-00902 - Invalid Identifier when creating a foreign key

texts:
I'm using the following script to create tables:

DROP TABLE Vehicle_type;
CREATE TABLE Vehicle_type
(
Vehicle_type_id INT PRIMARY KEY,
Model VARCHAR2(15),
Make VARCHAR2(15)
);

DROP TABLE Vehicle;
CREATE TABLE Vehicle
(
Registration VARCHAR2(7) PRIMARY KEY,
FOREIGN KEY (Vehicle_type_id) REFERENCES Vehicle_type(Vehicle_type_id),
GVW INT,
Year INT,
Body VARCHAR2(15)
);

Which creates the Vehicle_type table just fine, but when it gets to the foreign key in the Vehicle table I get the error ORA-00904: "VEHICLE_TYPE_ID": invalid identifier.

It's like the Vehicle_type_id column doesn't exist, but it definitely does because it shows up in SQLDeveloper.


texts: Cannot remove char 0x0000 from NVARCHAR column

texts:
So, I know all about the Replace function and a bug with char(0).

I have a column (NVARCHAR(128)) that has some NCHAR(0x0000) characters from a bad import.

I'm using SQL Server 2008 R2.

The collation for the column is: SQL_Latin1_General_CP1_CI_AS.

I have tried all the stuff online that I can possibly find, and NOTHING will get the stinking char(0) characters out of the column.

Here's my latest attempt, with BAFFLING (bug in sql server?) results.

I have a function that loops through each char and replaces 0x0000 with a specific char.

ALTER FUNCTION dbo.ReplaceCharZero
(
  @testString NVARCHAR(MAX),
  @charToReplaceWith NCHAR(1) = ' '
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE
        @i INT = 1 ,
        @fixedString NVARCHAR(MAX) = ''
    WHILE @i <= LEN(@testString)
        BEGIN
            IF SUBSTRING(@testString, @i, 1) = CHAR(0x00)
                BEGIN
                    --PRINT 'Found' + CAST(@i AS VARCHAR)
                    SET @fixedString = @fixedString + @charToReplaceWith
                END
            ELSE
                BEGIN
                    --PRINT 'NOT Found' + CAST(@i AS VARCHAR)
                    SET @fixedString = @fixedString
                        + SUBSTRING(@testString, @i, 1)
                END
            SET @i = @i + 1
        END
    RETURN @fixedString
END

And here's what I do to test:

BEGIN TRAN

    DECLARE @ShortDescription NVARCHAR(128), @SupplierId INT, @Language CHAR(2)

    SELECT TOP 1 @ShortDescription = ShortDescription,
                 @SupplierId = SupplierID,
                 @Language = Language
    FROM   Supplier_Multilingual
    WHERE  ShortDescription LIKE '%' +  CHAR(0x00) + '%'

    SET @ShortDescription =  REPLACE(dbo.ReplaceCharZero(@ShortDescription, ' '), '-', ' ')

    UPDATE dbo.Supplier_MultiLingual
    SET    ShortDescription = NULL
    WHERE  SupplierID = @SupplierId
    AND    Language = @Language

    UPDATE dbo.Supplier_MultiLingual
    SET    ShortDescription = dbo.ReplaceCharZero(@ShortDescription, '')
    WHERE  SupplierID = @SupplierId
    AND    Language = @Language

    SELECT *
    FROM   Supplier_Multilingual
    WHERE  SupplierId = @SupplierId
    AND    Language = @Language
    AND    ShortDescription LIKE '%' +  CHAR(0x00) + '%'

ROLLBACK TRAN

In my test, I grab the column as a variable, I run my function on it to strip out the 0x0000, then I update the original column with a NULL, then I update it to my fixed variable, and then I run a query to see if 0x0000 chars still exist, which they do.


texts: Is a composite index also good for queries on the first field?

texts:
Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index?

Additionally, I created an index on A, but Postgres still uses the composite index for queries on only A. If the previous answer is positive, I guess it doesn't really matter, but why does it select the composite index by default, if the single A index is available?


texts: Why to put unstructured data in NoSQL DBs?

texts: The statement that NoSQL databases are better for unstructured data because of their schemaless nature can be found everywhere. Frankly, I don't understand that. A human voice recording is often be used as an example for unstructured data. I can store it in a relational database with datatype BLOB, in a well defined schema. Can somebody explain?


texts: I'm not exactly sure when it happened, but I've started noticing that some of the hosts in my Enterprise Manager grid have a target named UTILITY_DSN. Does anyone know what this is? The one I noticed today appeared after reinstalling the agent. It shows as a datatbase instance, but with a non-existent Oracle Home.


texts: When installing Oracle, what's the quickest way to check rpm packages and install what is  missing?

texts:
When installing Oracle, there is a step which is to install all necessary packages. I use the following command to test whether a package is installed and install them if they are missing:

rpm -qa | grep binutils

I know I can verify all packages in this way, but I want to know whether there is a better and quicker way to do this task.

Thanks.


texts:
I have this query and works. But It seems ugly to me that it needs some optimization since I repeat the same actions over and over again.
There must be a better way to merge all of these

SELECT *
FROM
  (SELECT COUNT(*) AS a
   FROM order_item_histories
   WHERE order_status_id IN (5, 4, 15)) AS e,
  (SELECT COUNT(*) AS b
   FROM order_item_histories
   WHERE order_status_id IN (6)) AS f,
  (SELECT COUNT(*) AS c
   FROM order_item_histories
   WHERE order_status_id IN (4, 10, 9, 12, 7)) AS g,
  (SELECT COUNT(*) AS d
   FROM order_item_histories
   WHERE order_status_id IN (11, 16, 17, 13)) AS h


texts: B-tree node split strategy in SQL Server for monotonically increasing value

texts:
Consider a B-tree index on a value that will always increase monotonically, e.g. a column of type IDENTITY. With a conventional B-tree implementation, whenever a node is full, it will be split 50%/50% and we end up with a B-tree in which (almost) all nodes will be only 50% full.

I know that Oracle discovers when a value is ever-increasing and in these cases Oracle performs a 90%/10% split instead. That way, (almost) all nodes will be 90% full and a far better page utilization is obtained for these, quite common, cases.

I have not been able to find documentation for a similar feature in SQL Server. However, I have performed two experiments in which I inserted N random integers, and N consecutive integers in an index, respectively. The former case used far more pages the latter.

Does SQL Server provide a similar functionality? If so: can you point me to some documentation on this feature?

UPDATE:
It seems, by the experiments provided below, that leaf nodes are kept un-splitted and internal nodes are split 50%/50%. That makes B-trees on increasing keys more compact than on random keys. However, the 90%/10%-approach by Oracle is even better, and I still look for some official documentation that can verify the behavior seen in experiments.


texts:
I am writing a trigger that needs to raise an exception if a condition doesn't hold. That condition that should result in a raise is that the current row being modified has a field enrolled that is null, and the primary key of the row being modified does not appear in one of 6 other tables.

(I know that this is a terrible design, but this is what I've inherited, and I don't have much experience with the more advanced features of our DBMS, postgresql).

I am comfortable with my trigger, but I am having trouble writing the procedure that the trigger should execute: This is what I have so far, which admittedly is not much:

CREATE FUNCTION ensure_consistent_enrolled()
RETURNS trigger AS $$
BEGIN
  RAISE EXCEPTION 'User not enrolled plan'
  USING HINT = 'Ensure user is enrolled in a plan'
END;
$$ LANGUAGE plpgsql;

What I am trying to achieve in the stored procedure is something like:

enrolled_in_plan = OLD.automatic_payments || !(OLD.id in one of 6 tables)

if <> enrolled_in_plan
  raise...

end


texts: Best Data compression technique in Oracle

texts:
We are trying to create a Archival Strategy for Oracle Databases. Archiving old Records to Compressed Oracle tables has come out as a winner.

We have noticed 55% byte saving for Tables with only Text and 25% byte saving for Tables with Attachments (PDF, Word, Excel).

Please suggest

If anyone has faced problems with Compressed Tables.
If we have better options in Oracle to archive data. We need to refer this archived data on daily bases.

Performance is not a big concern.

We are looking for Oracle 11g Solution only.


texts: Can't install Microsoft SQL Server 2016 Release Candidate 3 (RC3) on Windows Server 2008 R2

texts:
I am attempting to install Microsoft SQL Server 2016 Release Candidate 3 (RC3) on Windows Server 2008 R2.

I have removed all other versions of SQL Server, and removed VS 2015 Community and all it's SQL Server components.

When I run setup from the SQLServer2016RC3-x64-ENU.iso, (downloaded from Microsoft, and successfully installed on Windows 10, with VS 2015 Community installed), I get a dialogue with the following message:

  Microsoft SQL Server 2016 Release Candidate 3 (RC3) Setup:
  Setup limited to Reporting Services for SharePoint
  This server is running Windows Server 2008 R2. You can continue with setup to install or upgrade Reporting
  Services functionality in SharePoint:
  0 Reporting Services - SharePoint
  0 Reporting Services Add-in for SharePoint Products

The server does not have SharePoint installed.  Why is this happening, and how can I install Microsoft SQL Server 2016 Release Candidate 3 on this server?


texts:
I have set up mysql replication with single slave.
Whenever i am adding data to master database, it is automatically copied to slave database.
But if I add data to slave database it is  not available in master database. why?

If it is wrong to add slave database, how I can tell to the slave database to redirect writes to master? is it possible?

My application is designed as normal application not for replicated database. Should I  change my application code for working with replicated database?


texts: Defaulting a column to the 1st of next month

texts:
I'm trying to find the most elegant means of defaulting a column's value to the 1st of next month. The best I've been able to come up with is this:

ALTER TABLE Foo ADD
Bar datetimeoffset(0) NOT NULL DEFAULT(DATEFROMPARTS(DATEPART(year, DATEADD(month, 1, GETDATE())), DATEPART(month, DATEADD(month, 1, GETDATE())), 1))

Whilst this works, it feels really clunky because I need to calculate DATEADD(month, 1, GETDATE()) twice and because I need to do the DATEFROMPARTS dance.

Is there a simpler way to achieve my goal?


texts:
Is it possible to assign column level security in SQL Server directly on a table, without using a view?

For example, for table 'tbl', if I want column 1 to be restricted for user 1, is it possible for the user to query a select * from tbl with the output being every other column except the restricted columns?

My understanding is it is not possible and would throw an error stating that a column is restricted.

Is there any other way?


texts: SQL Server Massive delete causing huge/full transaction log

texts:
Application has a stored procedure that executes a massive delete (millions of records) all at one time which causes a huge transaction log.  CANNOT modify stored proc (to break up delete into batches) - vendor restrictions.
SQL Server 2019 / Simple Recovery Model
Does anyone have an alternate approach to manage the transaction log growth when this stored proc runs?


texts:
trying to start mongo config server :

mongod --configsvr --dbpath /data/config-1 --port 27019 --logpath /data/config-1.log --fork --journal

gives:

F CONTROL  [main] Failed global initialization: BadValue: nojournal is not allowed when configsvr is specified

Can anyone advise on how to resolve this one?


texts:
I have a problem, which I expect is very common, but I am not sure what to search for, as it can be explained in many different ways.

I am designing a database structure for managing invoices, products, prices, currencies, etc.

I am able to manage prices and names of the products (these are expected to change every now and then).

On invoices, products are added by quantity, with discount, vat, unicorns, squirrels, and so on, and then the total of the invoice is calculated.

Now, when a price is updated in the products table, the invoice is not supposed to change. It needs to stay the same forever.

When thinking about this, to me it seems like the product list is a list of "class type" objects, which describe what a product looks like. Or we could call it a "template" or "base" or "abstract", depending on what abstraction we think of.

The products that are added to the invoice are "instances", "implementations", "children", "versioned records" or whatever we'd call them.

How would I structure this in the database? I could split them into two different tables "AbstractProduct" and "Product", but they would have most (possibly all) of their columns in common. In that regard it would seem appropriate to keep them in the same table. But then I would need an extra column to signify whether it is "class" or "instance". That is fine too. This could also be a foreignkey to the class it was created from (or NULL for classes).

I have tried different approaches, but all seems to have annoying caveats.

Is this a known pattern? What is it called? Are there best practices for solving this issue?


texts:
Background

Various databases have custom implementations for auditing:

SQL Server Audit
Oracle Audit
PostgreSQL Audit Trigger
MySQL Enterprise Audit

Problem

A number of problems exist:

There is no consistency (different commands, different log formats, etc.)
There does not appear to be an ANSI SQL standard
Some of the audits log to files, rather than a table or tables

Question

What ANSI SQL table auditing mechanism is:

database agnostic; and
tracks changes that can be queried using SQL (i.e., an audit table, not a log file)?

If no ANSI SQL standard for audit tables exists, then what alternatives exist for database-agnostic auditing (e.g., create a custom audit table) of CRUD operations against all tables in a schema?


texts: Can insert on tables with FK impact operations on other tables that have FKs to the same table?

texts:
Let's assume that we have the following tables on an InnoDB database:

user (id_user, name)
log (id_log, id_user, info)
message (id_message, id_user_from, id_user_to, message)

The id_user column on log and id_user_from and id_user_to on message tables are FK to user(id_user).

As I understand it, when we make an insert on log table mysql will create a shared lock on the related record on user, if exists. If another transaction try to get a shared lock, it will works fine. If  another transaction try to get a exclusive lock, it will have to wait until the first transaction finishes.

My question is: If another transaction try to make an insert or update on message referencing the same record on user it will be impacted by the insert on log? I don't think so, because this transaction will get a shared lock on the record too, but I read on some old articles (from 10 years ago) that this can be an issue. If it is a case, isolation level has some impact on this? Again, I don't think so.


texts: How to convert a Top 1 subquery using outer table alias to Oracle?

texts:
I have the following SQL Server query

select
    (select top 1 b2 from  BB b where b.b1 = a.a1 order by b2) calc,
    a1,
    a2
from AA a
where a2 = 2;

which I can rewrite using analytic functions

select
    (select b2 from
    (select
        row_number() over (order by b2) lfd,
     b2 from  BB b where b.b1 = a.a1
    ) as t where lfd = 1
    ) calc,
    a1,
    a2
from AA a
where a2 = 2;

but when I convert this to oracle

create table AA ( a1 NUMBER(10), a2 NUMBER(10) );
insert into AA values ( 1, 1);
insert into AA values ( 1, 2);
insert into AA values ( 1, 3);
insert into AA values ( 2, 2);

create table BB ( b1 NUMBER(10), b2 NUMBER(10) );
insert into BB values ( 1, 1);
insert into BB values ( 2, 4);
insert into BB values ( 2, 5);

select * from AA;
select * from BB;

select
    (select b2 from
    (select
        row_number() over (order by b2) lfd,
     b2 from  BB b where b.b1 = a.a1
    )  where lfd = 1
    ) calc,
    a1,
    a2
from AA a
where a2 = 2;

I get the following error

Error at line 5
ORA-00904: "A"."A1": invalid column name


texts: SSISDB Start and Stop time for each Package

texts:
I am trying to query the SSISDB Project Catalog to review the Start and Stop time for each SSIS package.
Is there any native query to conduct this?

I am checking [internal].[executions],  SSISDB.catalog.operation_messages, etc

Running this query does not work, if I have a Master Package calling Child Packages.

select start_time,end_time,*
from catalog.executions cc
order by cc.start_time desc

So for each package, I need to know Start and Stop time.


texts: Mixed Editions in a 3 Node SQL Server Always on Availability Group

texts:
I could not find any solid documentation anywhere, checking any of you have seen/built an AG that looks like below. Appreciate your responses.

SQL SERVER 2016

Primary - Enterprise edition
Secondary 1 - Enterprise edition - with Sync Replication
Secondary 2 - Standard Edition with Async Replication

Thank you


texts: Manually delete AUTOBACKUP of controlfile

texts:
My boss has decided to change retention policy to a 31 days period instead of redundancy of 4 copies.
One of the side effects is that autobackups get obsolete after 31 days.
This is the configuration of RMAN:
RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name DBTEST are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 31 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

I have been trying to delete copies of controlfile older than 5 days but it always includes copies of controlfile in Level 0 and Level 1 backups not only autobackups.
If I try with DELETE COPY OF CONTROLFILE LIKE '%F' I do not get any file.
Is there any way to delete AutoBackup files which are stored on +DGDATA/<dbname>/AUTOBACKUP/<date>/?


texts: How to report on sparse areas of sparse fact table

texts:
A source system tracks student attendance for a school district by reporting absence events. Attendance on any particular day can be determined by examining three datasets: school calendar, student enrollment, and absence.

On any given school day, the number of enrolled students in attendance is usually much larger than the number that are absent, so this approach reduces the number of records stored to track attendance significantly.

I am trying to determine the proper way to represent daily attendance in a dimensional model. The most obvious way is to create a factless table with a grain per school day per student, and an attendance dimension that has values for both attendance and absence reasons. This is quite straightforward to work with OLAP, but the downside is the size of the fact table.

For example, for 30,000 students and 188 school days means that there are approximately 0.5 million records per year (if this doesn't seem large enough to be an issue, then consider an example in which attendance must be reported on per period rather than per day). Contrast this to a fact table that records only absences and the number is considerably smaller. However, if I do this, then I am not sure how to build cubes that aggregate daily attendance facts.

The specific OLAP technology being used is SQL Server Analysis Services 2008 R2. Any thoughts?


texts: Is the speed of a PostgreSQL SELECT adversely affected by too many indexes on the table?

texts:
I have read that when having a lot of indexes on a database It can seriously hurt the performance but in the PostgreSQL doc I can't find anything about it.

I have a very big table with something like 100 columns and a billion rows and often I have to do a lot of searches in a lot of different fields.

Does the performance of the PostgreSQL table will drop if I add a lot of indexes (maybe 10 unique column indexes and 5 to 7 three column indexes)?

EDIT: With performance drop I mean SELECT performance; the database will only be updated once per month so UPDATE and INSERT speed is not an issue.


texts:
Building a database on Access 2007 and having trouble selecting the primary key. The tables consists of a student table and another table where the students are checked in. I would have liked to make the Student ID the primary key, but the check in staff doesn't request that on their paper-based forms. I also thought about the making the email the key, This would mean that they would only have to use their school provided emails

Please respond, perhaps a concatenated key would work?

NOTE: The information requested from students on the form when they sign in is there Name, Email, Date, Phone


texts: SQL Agent doesn't have perms to run a PowerShell script

texts:
I recently upgraded SQL Server from Windows 2008 R2 & SQL Server 2008 R2 to Windows 2012 R2 & SQL Server 2014 SP1. One of the sql agent jobs fails to run. The job runs a PowerShell script to script sql agent jobs to disk. The job fails with an error:

  SSPI handshake failed with error code 0x8009030c

If I RDP to the server and run the PS script in ISE with the SQL Server Agent service creds, it works.

Any ideas?

Here's the PS script:

param([String]$ServerName)

$OutputFolder = "\\backupfolder\sqlbackups\sqlservername\SQLAgentJobs"

#Load the SQL Server SMO Assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

#Create a new SqlConnection object
$objSQLConnection = New-Object System.Data.SqlClient.SqlConnection

Try
{
    $objSQLConnection.ConnectionString = "Server=$ServerName;Integrated Security=SSPI;"
    $objSQLConnection.Open() | Out-Null
    $objSQLConnection.Close()
}
Catch
{
    $errText =  $Error[0].ToString()
    Write-Host $errText
}

$DoesFolderExist = Test-Path $OutputFolder
$null = if (!$DoesFolderExist){MKDIR "$OutputFolder"}

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerName

foreach($job in $srv.JobServer.Jobs)
{
    $job_name = $job.Name
    $job.Script() | out-file "$OutputFolder\$job_name.sql"
}


texts: MySQL : perform a big data migration between tables

texts:
I want to migrate data between 2 InnoDB tables.

Currently I'm running this query:

INSERT INTO table_a SELECT * FROM table_b;

If the dataset grows, what's the best way to avoid CPU overload?

Thanks


texts: PostgreSQL 9.3: Primary key violated by a trigger INSERT

texts:
My problem

Consider a table t with many frequent updates from users, from which only the last few are relevant.

In order to keep the table size reasonable, whenever a new row is inserted old rows from the same user_id are deleted. In order to keep an archive, the row is also written to t_history.

Both t and t_history have the same schema, in which id is a bigserial with a primary key constraint.

Implementation

Stored procedure

CREATE FUNCTION update_t_history()
RETURNS trigger
AS
$$
declare
BEGIN
    -- Insert the row to the t_history table. `id` is autoincremented
    INSERT INTO t_history (a, b, c, ...)
    VALUES (NEW.a, NEW.b, NEW.c, ...);

    -- Delete old rows from the t table, keep the newest 10
    DELETE FROM t WHERE id IN (
                  SELECT id FROM t
                  WHERE user_id = NEW.user_id
                  ORDER BY id DESC
                  OFFSET 9);
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

Corresponding insertion trigger:

CREATE TRIGGER t_insertion_trigger
AFTER INSERT ON t
FOR EACH ROW
EXECUTE PROCEDURE update_t_history();

The error

The trigger works well, but when I run a few dozen insertions in a single transaction, I get the following error:

BEGIN
ERROR:  duplicate key value violates unique constraint "t_history_pkey"
DETAIL:  Key (id)=(196) already exists.

Updates

The id field in both tables (from \d+ t):

id|bigint|not null default nextval('t_id_seq'::regclass)
"t_pkey" PRIMARY KEY, btree (id)

PostgreSQL version is 9.3.

Any idea why the stored procedure breaks the primary key constraint in transactions?


texts:
I have two tables. One of them is big, long with more than 500 columns (most of those are tinyint) and 360000 rows. It contains info about photos. Second one is small with just three int columns (surrogate primary key and two foreign keys) and one decimal. It has just 45000 rows and contains info about what people are on those photos. It's so small it doesn't even have indexes. What I have trouble with is selecting photos within clause that have no people marked on them.
Query with clause within just first big table takes acceptable few seconds. Then I make something like that

SELECT photos.* FROM photos
 LEFT OUTER JOIN
 photos_people ON photos.photo_id = photos_people.photo_id
WHERE photos_people.photo_people_id is NULL AND ... LIMIT 150;

Now it suddenly takes literally minutes. What am I doing wrong? Is there a way to speed up query?


texts: Intellisense not working for one connection but works for others

texts:
I can connect to multiple database servers. Intellisense works on all of the connections except one. It used to work on this connection also until recently.

I did the following:

Enable Intellisense (on the toolbar and edit > intellisense)
Disable and then enable intellisense
Install SP3
Reboot SQL (and PC)


texts: Does a DBA need to know how to program in a system language besides SQL?

texts:
To what extent does a Database Administrator need to know system or application level programming languages (for example .NET or PHP) besides "just SQL"?

For the purposes of this question, no specific version of the SQL standard is considered for this answer (SQL ANSI 86, SQL ISO 87, SQL:2008) as the question is in regards to desktop or server languages outside the realm of SQL.


texts: Design rules to make database easier to maintain

texts:
I'm designing a small system that includes an API and a Database.  On the API side I'm familiar with versioning the functions (Endpoints) to allow older clients to continue working.

I'm looking for advice to achieve the same effect for the database.  Is there a way to "version" the database tables (or schemas), so to speak, to allow older applications to continue working?  In this case the that will be the older versions of the API service that I'm maintaining to support older clients.

One thought that occurred to me is "create a versioned view for every table", so for example if the table is called account I would have a view called account_v1.

I obviously don't have a problem with adding new tables or even adding columns to tables (as long as they have default values or allow Null)

The biggest problem comes when splitting a table into multiple tables or when dropping columns.

A simple example would be taking a user's email address out of the user table because in version 2 you want to allow the user to have multiple email addresses.  I'm quite new to views and triggers but I think you can set it up so that the older application still sees the email address in the table, and writes the email address to the new separate table.

I'm finding it terribly inconvenient to maintain views for every table, but this may be because I'm new to it and it will get easier?  Or are there perhaps some other rules or methods that makes it easier to upgrade and maintain older versions so that older version of the application doesn't break?  Is there an easy way to generate the views and triggers automatically because the initial View would basically mirror the table at that time.  Later when the table is modified, I would then have to hand-fix the view(s) associated with that table to maintain a consistent view.  And it seems to me that View version N-2 should refer to View version N-1 as it's base, with View version N-1 referring to View version N.  View version N refers initially to the raw table but when the table is changed that view will be re-written.

FWIW I'm using Postgres 9.5


texts:
I'm trying to backup a drupal site database but I'm having some issues. When I ran the following command:

mysqldump -uroot -p  drupaSite > drupaSite.sql

I get the following error:

mysqldump: Got error: 1017: Can't find file: 'drupal_install_test' (errno: 2) when using LOCK TABLES

if I tried to query the table I get the same error:

mysql> select * from drupal_install_test;
ERROR 1017 (HY000): Can't find file: 'drupal_install_test' (errno: 2)

I check the status of the table in the database:

show table status from drupaSite;

I get the following output:

| drupal_install_test     | NULL   |    NULL | NULL       |   NULL |           NULL |        NULL |               NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL                | NULL            |     NULL | NULL           | Can't find file: 'drupal_install_test' (errno: 2) |

I ran the following query:

SELECT * FROM information_schema.tables WHERE table_name='drupal_install_test'\G

I get the following output:

*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: drupaSite
     TABLE_NAME: drupal_install_test
     TABLE_TYPE: BASE TABLE
         ENGINE: NULL
        VERSION: NULL
     ROW_FORMAT: NULL
     TABLE_ROWS: NULL
 AVG_ROW_LENGTH: NULL
    DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
   INDEX_LENGTH: NULL
      DATA_FREE: NULL
 AUTO_INCREMENT: NULL
    CREATE_TIME: NULL
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: NULL
       CHECKSUM: NULL
 CREATE_OPTIONS: NULL
  TABLE_COMMENT: Can't find file: 'drupal_install_test' (errno: 2)

I ran the following query:

CHECKSUM TABLE drupal_install_test;

I got the following output:

+-------------------------------+----------+
| Table                         | Checksum |
+-------------------------------+----------+
| drupaSite.drupal_install_test |     NULL |
+-------------------------------+----------+
1 row in set, 1 warning (4.34 sec)

I ran the following query:

CHECK TABLE drupal_install_test;

and I get the following output:

   +-------------------------------+-------+----------+---------------------------------------------------+
    | Table                         | Op    | Msg_type | Msg_text                                          |
    +-------------------------------+-------+----------+---------------------------------------------------+
    | drupaSite.drupal_install_test | check | Error    | Can't find file: 'drupal_install_test' (errno: 2) |
    | drupaSite.drupal_install_test | check | error    | Corrupt                                           |
    +-------------------------------+-------+----------+---------------------------------------------------+

2 rows in set (0.02 sec)

My question for you guys is how can I fix this in a way I can backup the database and restore it in another server. The site is working just fine I need to migrate the server. I would really appreciate your help guys.


texts:
Is there a way to issue a postgres update query that just does nothing if the row it wants to update is locked?  Or perhaps waits a very short time before giving up, just for this query?

I have a table with an updated_at field which is used for Russian doll caching.  Russian doll caching basically uses updated_at data in cache keys so you can invalid the cache by simply modifying the updated_at field. So for example if you have 2 tables:

books:
 id: int
 name: string
 updated_at: datetime

pages:
 id: int
 book_id: int
 text: text
 updated_at: datetime

You can now cache a book based on books.id and books.updated_at.  If a page changes you change the updated_at for the parent book and that book's cache is invalidated.

The issue is if a large number of pages for the same book are being modified at the same time, you end up with a flood of queries to update updated_at for the same book record.  As result, there is a ton of blocking while each update queries waits for a lock.  We actually don't need all the updates to happen since it is just being used to invalid that cache.  So if only one actual update happens per sec we are fine.

Any thoughts on how to achieve this?


texts:
Is it possible in SQL Server 2016 Enterprise to add a new database to an Always On availability group and have it enabled for DTC support without recreating the AG?

Our Availability Group is already set for DTC support on the databases already existing but we are creating a new database and it needs to have DTC support as well. Can this be done with some kind of Alter statement or would we have to take an outage and totally recreate the AG group when we add each database that needs DTC?


texts:
I have a flat file with this  datetime format 5/24/2017 05:12:44.243 PM. I have been struggling to convert this format to something like this 2017-05-24 17:12:44.243. I tried using this derived column expression

 (DT_DBTIMESTAMP)(SUBSTRING(EventDate,FINDSTRING(EventDate,"/",2) + 1,4) +
   "-" + SUBSTRING(EventDate,1,FINDSTRING(EventDate,"/",1) - 1) +
   "-" + SUBSTRING(EventDate,FINDSTRING(EventDate,"/",1) +
           1,FINDSTRING(EventDate,"/",2) - FINDSTRING(EventDate,"/",1) - 1) +
   " " + SUBSTRING(EventDate,FINDSTRING(EventDate," ",1) + 1,8))

where "EventDate" is the column name

but I am getting the result in this format "2017-05-22 05:21:00.000" also this is not converting the 12 hour to 24 hour. I am new to SSIS development and been struggling on this really long. Please help me on this, any help will be really appreciated.


texts: Can I do a standard restore/recover using image file backups?

texts: We have a database backed up with incrementally updated image file backups.  I understand the typical use case would be to switch to the copies, but would a point in time recovery using restore/recover also work?  I'm considering this route because the storage for space for the copies is slower.


texts: Mysql Cluster Exceeding MaxBufferedEpochs

texts:
I have an mysql cluster which have 4 api nodes, 2 management nodes and 4 data nodes. Today, I was having problems while trying to connect database and all queries was hanging at "Opening tables" state. After inspecting logs i have found these erros on logs:

Api Node Erros:

2015-08-20 19:44:14 15540 [Note] NDB Schema dist: Data node: 5 failed, subscriber bitmask 00
2015-08-20 19:44:14 15540 [Note] NDB Schema dist: Data node: 6 failed, subscriber bitmask 00
2015-08-20 19:44:14 15540 [Note] NDB Schema dist: Data node: 7 failed, subscriber bitmask 00
2015-08-20 19:44:14 15540 [Note] NDB Schema dist: Data node: 8 failed, subscriber bitmask 00
2015-08-20 19:44:14 15540 [Note] NDB Schema dist: cluster failure at epoch 3313124/17.
2015-08-20 19:44:14 15540 [Note] NDB Binlog: ndb tables initially read only on reconnect.
2015-08-20 19:44:14 15540 [ERROR] /opt/mysql/server-5.6/bin/mysqld: Got temporary error 4028 'Node failure caused abort of transaction' from NDBCLUSTER
2015-08-20 19:44:14 15540 [ERROR] /opt/mysql/server-5.6/bin/mysqld: Sort aborted: Got temporary error 4028 'Node failure caused abort of transaction' from NDBCLUSTER
2015-08-20 19:44:14 15540 [ERROR] Got error 4010 when reading table './database_name/table'
2015-08-20 19:44:14 15540 [Note] NDB Binlog: cluster failure for ./database_name/table_name at epoch 3313124/17.

mysql> show processlists;

Id  User    Host    db  Command Time    State   Info
1   system user     NULL    Daemon  1497    Waiting for ndbcluster to start NULL

Data Node Erros:

2015-08-20 19:44:14 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 14229759227592721 nodes: 0000000000000000000000000000040000000000000000000000000000001a00
2015-08-20 19:44:14 [ndbd] WARNING -- ACK wo/ gcp record (gci: 3313124/17) ref: 0fa2000b from: 0fa2000b
2015-08-20 19:44:14 [ndbd] WARNING -- ACK wo/ gcp record (gci: 3313124/17) ref: 0fa2000c from: 0fa2000c
2015-08-20 19:44:14 [ndbd] WARNING -- ACK wo/ gcp record (gci: 3313124/17) ref: 0fa2008a from: 0fa2008a

Management Node Erros:

2015-08-20 19:44:14 [MgmtSrvr] INFO     -- Node 5: Disconnecting lagging nodes '0000000000000000000000000000000000000000000000000000000000000200',
2015-08-20 19:44:14 [MgmtSrvr] WARNING  -- Node 5: Disconnecting node 9 because it has exceeded MaxBufferedEpochs (100 > 100), epoch 3313119/4

Detailed logs and configurations

Data node config:

https://gist.github.com/sdemircan/730fa49fcc14b4376c42

Api node config:

https://gist.github.com/sdemircan/f9d230d32700b86564fd

Management node config:

https://gist.github.com/sdemircan/d6fbd54799daaae01bf2

Api Node logs:

https://gist.github.com/sdemircan/2d62b1c92176de9de9d3

Data Node logs:

https://gist.github.com/sdemircan/d0c97b82457a9c33deaa

Data Node logs:

https://gist.github.com/sdemircan/3faa1e41367bc7655210

Management Node logs:

https://gist.github.com/sdemircan/a026ac57757fafdafaa9

What might bring MaxBufferedEpochs to upper limit?


texts:
Currently, I have setup log shipping from production server to DR server. Now, the doubt is that whenever my production server goes down or lets say data center crashes, I will switch it to DR site. On DR, I will change the mode of database from "Restoring/No Recovery" to "Recovery" mode.
But, now the problem is that whenever my data center recovers lets say it recovers in 2 hours, then I want to switch again to my production server. So, when it gets switched to PROD server then how can I change database mode to "Restoring" state on DR site to able to start log shipping again?


texts:
I have been using sp_whoisactive and this is very helpful in finding details of running sessions and blocking.

I wanted to check whether there is any option to schedule a job based on this procedure and alert me only when there is active blocking on database and blocking is held for more than configurable number of minutes.

Also, alert should be triggered only when it reaches more than configurable number of blocking on database.

I used the details given at http://www.sqlserver-dba.com/2017/01/how-to-monitor-blocked-processes-with-sql-alert-and-email-sp_whoisactive-report.html however it sends alert on every run and doesn't seem to check regarding blocking. Sends email whether there is only one query running or multiple queries running, whether it is with blocking or without blocking. Not sure, if I am missing anything.


texts:
On one of our production server log files, the following message is being observed on daily basis. What does it mean? Is it a serious issue?

  SQL Server has encountered 1 occurrence(s) of cachestore flush for the
  'SQL Plans' cachestore (part of plan cache) due to some database
  maintenance or reconfigure operations.


texts: Prevent compilation of a stored procedure to a linked server

texts:
I have a stored procedure, that queries a view on a linked server.

This works fine on prod, but our development server has some firewall restrictions, that prevent the compilation. This is no problem datawise (since it is not very important on dev), but I can't even ALTER the procedure on the development server, because the SQL server tries to compile it / check the syntax / used column names etc. when I alter the procedure (and I receive a timeout).

Is there a way (option, trace flag etc.) that I could use to prevent this syntax check and compile the procedure on the dev server (similar as when a table used in a procedure did not exists, where the SQL Server only throws a warning)?


texts:
I've got a SSRS report which is used for a wallboard to display basic stats and information to the department via a Raspberry Pi attached to a creen

This has the AutoRefresh Option set to 60s however since patching today where we turned on Snapshot Isolation to remove a lot of blocking that was occuring every morning and mid afternoon when the daily reports ran the auto refresh option seems to be failing.

I've tried re-deploying the report and even created a completely new report that just puts up the date& time to keep track of it refreshing, it sometimes works for a few minutes but then just stops refreshing

The tests I ran included a refresh every 10 seconds to see if it was timing out the execution running over 60 seconds

Out of the 5 separate executions I have just ran, one ran for 542 seconds before no longer refreshing, one ran for 339 one for 164 and two of them failed to refresh at all

Previous this would run until the wifi connection was lost on the Pi and the page errors while refreshing (NOTE all testing has been done locally on wired connection no network drops)

previous tests before that set have been done across different browses in / out incognito mode and all produce the same results

Any idea why this is happening, and potential fix that doesn't involve us stopping everything on the server again to turn off Snapshot issolation


texts: Optimizing numeric range (interval) searches in SQL Server

texts:
This question is similar to Optimizing IP Range Search? but that one is restricted to SQL Server 2000.

Suppose I have 10 million ranges provisionally stored in a table structured and populated as below.

CREATE TABLE MyTable
(
Id        INT IDENTITY PRIMARY KEY,
RangeFrom INT NOT NULL,
RangeTo   INT NOT NULL,
CHECK (RangeTo > RangeFrom),
INDEX IX1 (RangeFrom,RangeTo),
INDEX IX2 (RangeTo,RangeFrom)
);

WITH RandomNumbers
     AS (SELECT TOP 10000000 ABS(CRYPT_GEN_RANDOM(4)%100000000) AS Num
         FROM   sys.all_objects o1,
                sys.all_objects o2,
                sys.all_objects o3,
                sys.all_objects o4)
INSERT INTO MyTable
            (RangeFrom,
             RangeTo)
SELECT Num,
       Num + 1 + CRYPT_GEN_RANDOM(1)
FROM   RandomNumbers

I need to know all ranges containing the value 50,000,000. I try the following query

SELECT *
FROM MyTable
WHERE 50000000 BETWEEN RangeFrom AND RangeTo

SQL Server shows that there were 10,951 logical reads and nearly 5 million rows were read to return the 12 matching ones.

Can I improve on this performance? Any restructuring of the table or additional indexes is fine.


texts:
Okay... I have a table that has customers:

-- Individual Table
* ID (Internal Unique ID)
* IndividualID (External Unique Individual Identifier)
* Last Name
* First Name
* Birth Date
* SSN
* ...

The issue is that sometimes a person gets multiple Individual ID's. Say the person doesn't provide a SSN for one Encounter, the last name changes, typo in birthday, etc. So you end up with the same person in the individual table multiple times:

1, Frost, Jack, 1/1/2000, 000-00-0008
2, Frost, Jack, 1/1/2000, 000-00-0003
3, Doe,   Jane, 1/1/2000, 000-00-0005
4, Doe,   Janet, 1/1/2000, 000-00-0005
5, Frost, Janet, 1/1/2000, 000-00-0005

Those are just some examples. The basic idea is that I need to find individuals that are potential matches, so that the right person can merge the individuals into a single account.

The particular query I'm currently on is on SS2008-SP1, but I have other queries on SS2005 through SS2012. Is there any way I can improve this?

Initially I had a single select statement (instead of 2 temp tables, 5 inserts and a select statement), but the "This or This or This or..." took many minutes and this takes ~10 seconds. Population of Customers is ~144k (Select count(*) from Data)

Current I'm using a simple attempt to try and match four parts: Last Name, First Name, DOB, SSN. If 3 or 4 of them match on different individuals, the need to be inspected closer to determine if they really are the same person.

IF object_id('tempdb..#DATA') IS NOT NULL
    DROP TABLE #DATA;
GO

CREATE TABLE #DATA (
      EXTID VARCHAR(30) NOT NULL
    , LNAME VARCHAR(30) NULL
    , FNAME VARCHAR(30) NULL
    , SSN VARCHAR(11) NULL
    , DOB VARCHAR(8) NULL
    )
GO

INSERT INTO #DATA
SELECT
      EXTID = D1.EXTERNALID
    , LNAME = D1.LASTNAME
    , FNAME = D1.FIRSTNAME
    , SSN = CASE WHEN D1.SSN = '000-00-0000' THEN NULL ELSE D1.SSN END
    , DOB = convert(VARCHAR, D1.DOB, 112)
FROM Data D1
WHERE Type = 1 and STATUS = 1
GO

SELECT D1.*, [Splitter] = 'MATCH', D2.*
FROM #Demo D1, #Demo D2 WHERE D1.ID > D2.ID
    AND (   D1.LNAME = D2.LNAME
        AND D1.FNAME = D2.FNAME
        AND D1.SSN   = D2.SSN
        AND D1.DOB   = D2.DOB)
        UNION
SELECT D1.*, 'LName', D2.*
FROM #Demo D1, #Demo D2 WHERE D1.ID > D2.ID
    AND (   D1.LNAME <> D2.LNAME
        AND D1.FNAME = D2.FNAME
        AND D1.SSN   = D2.SSN
        AND D1.DOB   = D2.DOB)
        UNION
SELECT D1.*, 'FName', D2.*
FROM #Demo D1, #Demo D2 WHERE D1.ID > D2.ID
    AND (   D1.LNAME = D2.LNAME
        AND D1.FNAME <> D2.FNAME
        AND D1.SSN   = D2.SSN
        AND D1.DOB   = D2.DOB)
        UNION
SELECT D1.*, 'SSN  ', D2.*
FROM #Demo D1, #Demo D2 WHERE D1.ID > D2.ID
    AND (   D1.LNAME = D2.LNAME
        AND D1.FNAME = D2.FNAME
        AND D1.SSN   <> D2.SSN
        AND D1.DOB   = D2.DOB)
        UNION
SELECT D1.*, 'DOB  ', D2.*
FROM #Demo D1, #Demo D2 WHERE D1.ID > D2.ID
    AND (   D1.LNAME = D2.LNAME
        AND D1.FNAME = D2.FNAME
        AND D1.SSN   = D2.SSN
        AND D1.DOB   <> D2.DOB);

Edit to add Distinct Counts:

LName   FName   SSN DOB Count
36737   14539   115073  34284   144044

Edit: Cleaned up a bit to get rid of second temp table. Poking around the Estimated Execution plan, the above query - broken into 5 parts - uses hash map inner joins and takes about 10 seconds. My initial query, and other variations seem to use loop joins and is still chugging along at 10+ minutes.


texts: How to represent the following ternary relationship

texts:
The following image shows that each department can be
associated with several employees and locations, and each location can be associated
with several departments and employees; however, each employee is associated with a single department and location.

Question:-
what if I want to depict that an employee can work at multiple departments but only at a single location?
i.e., I want to represent a many to many relationship between employees and departments and between departments and locations
but a many to one relationship between Employees and Locations


texts: Postgres command failed: No space left on device

texts:
I'm running a command to create a new column on a fairly large table (about 315 million rows). This used more disk space than I expected and it has completely filled up my SAN. The command failed with the ERROR:  could not extend file "pg_tblspc/14342 etc.": No space left on device

My questions are, when a command fails like this, are the results of the command not immediately discarded? Why when no actual changes have been made to the table, is the tablespace still full?

And finally, what is the best way to free up space again when postgres will not allow to me execute any other commands e.g. VACUUM?


texts: Pull Access schema into a SQL Server table

texts:
We had an assembly that used to pull a list of tables and their columns from an Access database and place the listing into a SQL Server table for later use.  For various reasons the assembly is no longer working and reworking it is not an option at this time.

Is there a way to pull a listing of table names and columns from a linked server Access database?  If not, is it possible to pull this information from an SSIS package?  The output I need is really simple:

Customer    First Name
Customer    Last Name
Customer    Email
Product     Product ID
Product     Product Name
...


texts: Where can I get the installation software for Oracle 8i DB?

texts:
I have some backup files for an old Oracle 8i data that was created years ago. I need to be able to view this data and export it to a csv file.

The backup directory includes .dbf, .ctl , .arc and a few other file formats.

My initial thought is to obtain an installation of this DB so I can try to restore it. Is there anyway I can obtain it? (Any OS would be fine)

Any suggestions on how to do this would be great.


texts: SQL Server SELECT column not in Group By clause?

texts:
As you can see my query, this is the working one

SELECT m.MerchandiseId, m.MerchandiseName, createdAt =  MAX(i.CreatedAt) FROM Inbox i
INNER JOIN [User] u
ON i.FromUserId = u.UserId
LEFT OUTER JOIN Merchandise m
ON u.MerchandiseId = m.MerchandiseId  OR i.ToMerchantId = m.MerchandiseId
WHERE i.ToCompanyId = 10 OR i.FromCompanyId = 10
GROUP BY m.MerchandiseId, m.MerchandiseName

What if i want to SELECT one more field as i.InboxMessage with the Last record of each group?

SELECT m.MerchandiseId, m.MerchandiseName, i.InboxMessage, createdAt =  MAX(i.CreatedAt) FROM Inbox i
INNER JOIN [User] u
ON i.FromUserId = u.UserId
LEFT OUTER JOIN Merchandise m
ON u.MerchandiseId = m.MerchandiseId  OR i.ToMerchantId = m.MerchandiseId
WHERE i.ToCompanyId = 10 OR i.FromCompanyId = 10
GROUP BY m.MerchandiseId, m.MerchandiseName

I've tried some ways that I found in stackoverflow, but I still not manage to do it. Thousand of thanks if anyone would help on this.


texts: How do you remove a mirrored database SQL Server 2012

texts:
I have mirrored databases (synchronized), how do I delete such a database correctly? I tried

use master
alter database test0224aEd62 set single_user with rollback immediate
drop database test0224aEd62

by get error

  Msg 1468, Level 16, State 1, Line 2 The operation cannot be performed
  on database "test0224aEd62" because it is involved in a database
  mirroring session or an availability group. Some operations are not
  allowed on a database that is participating in a database mirroring
  session or in an availability group. Msg 5069, Level 16, State 1, Line
  2 ALTER DATABASE statement failed. Msg 3752, Level 16, State 1, Line 3
  The database 'test0224aEd62' is currently joined to an availability
  group.  Before you can drop the database, you need to remove it from
  the availatility group.


texts:
I'm sure this has been asked before, but since the question can be formulated in many different ways it's difficult to find the proper answer.
I have an orders table with a varchar field for the order number, which is formatted with 4-digits year, a dash (-), and a progressive numeric value. For example it may contain the following values:
SELECT number FROM orders ORDER BY number LIMIT 10;
  number
----------
 1999-13
 2019-11
 2020-1
 2020-10
 2020-100
 2020-12
 2020-2
 2020-21
 2020-3
 2021-1

I need to sort that field by year and then by the progressive number, with this expected result:
  number
----------
 1999-13
 2019-11
 2020-1
 2020-2
 2020-3
 2020-10
 2020-12
 2020-21
 2020-100
 2021-1

A DB fiddle is here.
My questions are:

Which is the simplest way to achieve this with an ORDER subclause?
How to add an efficient index using this custom sort without having to modify the table?

I would like to keep at least the first answer as database-agnostic as possible (that's why I haven't included db specific tags), but if different best answers are possible for different DBMS/versions, let's assume PostgreSQL 12.


texts: ENCRYPTBYKEY produces duplicate values when a WHERE clause is used

texts:
Below is my code:

UPDATE MyTable
SET EncryptedCol = ENCRYPTBYKEY(KEY_GUID('MyKey'), '0')
WHERE MyFlag = 0x0 -- This causes every row to have the same encrypted value

Without the WHERE clause every row has a unique encrypted value (this is desired).  When the WHERE clause is added all of the rows being set to the encrypted value of '0' are identical.

How can I ensure a unique encrypted value for every row when using a where clause?


texts: Only 'master' inside the system databases in Azure Managed instance

texts:
During our POC we saw the system databases(model,msdb and tempdb) lying outside the system databases group.

Any specific reason behind it?


texts: I'm finding very limited reading on how a large xml column affects table performance in sql server 2014/6.  For reference, it is a 36gig table and minus one xml column, it is 36megs.  Some queries call for the column (usually single row requests), most do not.  Is there a significant performance benefit to breaking that xml column out to its own linked table?


texts:
I'm trying using RMAN to create and restore backups. I'm getting an error stack trace when trying to restore to one of the backups that I have made. The error is

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/01/2016 11:52:10
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 30 and starting SCN of 2586064415

My backup RMAN script is as follows, and is run through an application where the parameters are filled in correctly.:

configure channel device type disk clear;
run {{
 allocate channel ch1 type Disk FORMAT '{0}\\BCKUP_%U' ;
 backup incremental level {1} database plus archivelog tag '{2}' ;
 backup spfile FORMAT '{0}\\SPFILE_%U' tag '{2}';
 backup current controlfile FORMAT '{0}\\CTRL_%U' tag '{2}';
}}

My restore script is:

run{
shutdown immediate
STARTUP FORCE NOMOUNT;
RESTORE CONTROLFILE FROM '...path to control file backup...';
ALTER DATABASE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}

I can restore to the most recent backup just fine, but when I try to take another step back to one made prior I receive that error. Is there something in my script for either creating the backup or restoring that needs to change?

Files Created by backup:

Side Note: I run ALTER DATABASE OPEN RESETLOGS after I get the error and everything is working fine and dandy. The problem though, is that the application that will eventually do the restore will see the error message stack and classify it as a failed restore.

Parameters:

{0}: \\192.168.xxx.xxx\c$\backups\20160201_104531_INCREMENTAL\ (depending on backup type its INCREMENTAL or ONLINE_FULL)
{1}: 0/1 (depending on level)
{2}: INC_20160201_104531 (depending on backup type it's INC or FULL)
The control file backup loc: \\192.168.xxx.xxx\C$\BACKUPS\20160201_104814_INCREMENTAL\CTRL_0IQSQVKP_1_1


texts: SQL Server 2014 Database vs. Schema Permissions

texts:
Two Schemas:
 dbo
 test

I'd like to grant create table to the test schema for a windows group; but not have them be able to create a table in the dbo schema.

I have tried:

GRANT CONTROL, ALTER ON SCHEMA :: test TO [ad\windows_group];

GRANT CREATE TABLE TO [ad\windows_group];

DENY ALTER ON SCHEMA :: DBO TO [ad\windows_group];

GRANT VIEW DEFINITION ON SCHEMA :: DBO TO [ad\windows_group]

The members of the windows_group can still create a table on the dbo schema; but cannot drop it.

I've seen multiple references saying that DENYing the ALTER schema permission should block the creation of the table; but I think I'm missing something.

Thanks


texts: Trigger is failing with no errors at all, why?

texts:
I need to UPDATE territory_id column at pdone.reps table by using and filtering results from veeva_new.userterritory so based on the answer I got here I am trying to build my own and this is how it looks like:

USE `veeva_new`;

DELIMITER $$

DROP TRIGGER IF EXISTS veeva_new.userterritory_ai$$
USE `veeva_new`$$
    CREATE DEFINER=`root`@`%` TRIGGER `veeva_new`.`userterritory_ai` AFTER INSERT ON `userterritory` FOR EACH ROW
    BEGIN
    SET @TerritoryVerification=(select reps.veeva_rep_id from pdone.reps where reps.veeva_rep_id=NEW.userid);

    IF (@TerritoryVerification>0) THEN
        SET @TerritoryLastMod=(SELECT reps.lastSyncAt FROM pdone.reps WHERE reps.veeva_rep_id=NEW.userid LIMIT 0,1);

        IF (NEW.lastmodifieddate > @TerritoryLastMod) THEN
            UPDATE pdone.reps
            SET reps.territories_id = NEW.territoryid, reps.lastSyncAt = NEW.lastmodifieddate, reps.updatedAt=now()
            WHERE reps.veeva_rep_id=NEW.userid;
        END IF;
    END IF;
    END$$
DELIMITER ;

But it's not working since insertion on veeva_new DB just stop with no errors, I just see this in general.log:

 11 Query COMMIT
           11 Query SELECT sync_time FROM _sync_history WHERE object_name='userterritory' ORDER BY sync_time DESC LIMIT 1
150703 12:48:42    11 Query START TRANSACTION
           11 Query INSERT INTO _sync_history (object_name, sync_time) VALUES('userterritory', '2015-07-03 17:18:26')
           11 Query INSERT INTO userterritory (id,lastmodifieddate,userid,territoryid) VALUES('04S80000000uZi9EAE','2009-08-24 17:56:49','00580000001sYARAA2','04T800000008yHOEAY'),('04S80000000uoFIEAY','2009-09-24 19:20:02','00580000002Ka2yAAC','04T800000008yHOEAY')$
           11 Query SET @UserVerification=(select reps.veeva_rep_id from pdone.reps where reps.veeva_rep_id=NEW.userid)
           11 Query ROLLBACK
           11 Quit

This is the schema for veeva_new.userterritory table:

CREATE TABLE `userterritory` (
  `id` varchar(18) NOT NULL,
  `userid` varchar(18) DEFAULT NULL,
  `territoryid` varchar(18) DEFAULT NULL,
  `lastmodifieddate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

reps.territories_id has a constraint against reps.territories and this is the schema for reps.territories table:

CREATE TABLE `territories` (
  `veeva_territory_id` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `district` int(11) DEFAULT NULL,
  `inactive` tinyint(1) NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`veeva_territory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

What is wrong on my trigger?


texts: SELECT DISTINCT ON subquery uses inefficient plan

texts:
I've got a table progresses (contains on the order of hundreds of thousands of records currently):

    Column     |            Type             |                        Modifiers
---------------+-----------------------------+---------------------------------------------------------
 id            | integer                     | not null default nextval('progresses_id_seq'::regclass)
 lesson_id     | integer                     |
 user_id       | integer                     |
 created_at    | timestamp without time zone |
 deleted_at    | timestamp without time zone |
Indexes:
    "progresses_pkey" PRIMARY KEY, btree (id)
    "index_progresses_on_deleted_at" btree (deleted_at)
    "index_progresses_on_lesson_id" btree (lesson_id)
    "index_progresses_on_user_id" btree (user_id)

and a view v_latest_progresses which queries for the most recent progress by user_id and lesson_id:

SELECT DISTINCT ON (progresses.user_id, progresses.lesson_id)
  progresses.id AS progress_id,
  progresses.lesson_id,
  progresses.user_id,
  progresses.created_at,
  progresses.deleted_at
 FROM progresses
WHERE progresses.deleted_at IS NULL
ORDER BY progresses.user_id, progresses.lesson_id, progresses.created_at DESC;

A user can have many progresses for any given lesson, but we often want to query for a set of the most recently created progresses for a given set of users or lessons (or a combination of the two).

The view v_latest_progresses does this nicely and is even performant when I specify a set of user_ids:

# EXPLAIN SELECT "v_latest_progresses".* FROM "v_latest_progresses" WHERE "v_latest_progresses"."user_id" IN ([the same list of ids given by the subquery in the second example below]);
                                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=526.68..528.66 rows=36 width=57)
   ->  Sort  (cost=526.68..527.34 rows=265 width=57)
         Sort Key: progresses.user_id, progresses.lesson_id, progresses.created_at
         ->  Index Scan using index_progresses_on_user_id on progresses  (cost=0.47..516.01 rows=265 width=57)
               Index Cond: (user_id = ANY ('{ [the above list of user ids] }'::integer[]))
               Filter: (deleted_at IS NULL)
(6 rows)

However if I try to do the same query replacing the set of user_ids with a subquery, it becomes very inefficient:

# EXPLAIN SELECT "v_latest_progresses".* FROM "v_latest_progresses" WHERE "v_latest_progresses"."user_id" IN (SELECT "users"."id" FROM "users" WHERE "users"."company_id"=44);
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Merge Semi Join  (cost=69879.08..72636.12 rows=19984 width=57)
   Merge Cond: (progresses.user_id = users.id)
   ->  Unique  (cost=69843.45..72100.80 rows=39969 width=57)
         ->  Sort  (cost=69843.45..70595.90 rows=300980 width=57)
               Sort Key: progresses.user_id, progresses.lesson_id, progresses.created_at
               ->  Seq Scan on progresses  (cost=0.00..31136.31 rows=300980 width=57)
                     Filter: (deleted_at IS NULL)
   ->  Sort  (cost=35.63..35.66 rows=10 width=4)
         Sort Key: users.id
         ->  Index Scan using index_users_on_company_id on users  (cost=0.42..35.46 rows=10 width=4)
               Index Cond: (company_id = 44)
(11 rows)

What I'm trying to figure out is why PostgreSQL wants to perform the DISTINCT query on the entire progresses table before it filters by the subquery in the second example.

Would anyone have any advice as to how to improve this query?


texts:
VPS (Contabo) : 10 cores (Xeon E5 2.2GHz), 60 GB RAM, SSD, MS SQL Web Edition (configured to use 10 CPUs), Windows Server 2016
When running SQL takes ~32GB of RAM,

When I execute SQL a stored procedure (CPU~19%, Disk 1MB read, 05.MB write) it is slower than on my laptop (i7,16 GB ram,SSD and SQL Express....).

What is wrong? How to improve performances on my VPS?


texts: Name for table with metric_name and metric_value columns

texts:
A typical database table might look something like this:

date-------geography---clicks---cost---conversions
___________________________________________________
1/1/2010---Kansas------56-------12-----1

When transposed, it looks like this.

date-----geography---metric_name---metric_value
____________________________________________
1/1/2010-Kansas------clicks--------56
1/1/2010-Kansas------cost----------12
1/1/2010-Kansas------conversions---1

The transposed table works better for some use cases where the number of metrics being collected changes frequently.

Is there a standard name for a table structured like that in the transposed table?


texts: SQL developer is free or do you require license for oracle database to use the sql or pl/sql developer? Also, Is it secure to connect to the oracle database since it connects to the db over a remote port right?


texts: Data Modeling Problem - Schema to store varying number of data points

texts: I need a schema to store varying number of data points. There are different data types. Each data type can have varying number of data points. For example: Data Type 1 will have 3 data points, Data Type 2 will have 4 data points, Data Type 3 will have 5 data point, etc. Each one of the data point name should be configurable. I need feedback on the schema I came up with. I need to flatten this data but I couldn't figure out how to do that and also have the data point names configurable.


texts: Simple recovery mode with mirroring - is that possible?

texts:
(The usual 'I'm not a real DBA, but I need to handle this somehow' disclamer here)

We have a database with Full recovery model that doesn't really need a Full recovery model... only it does.

Some context first: the database in question contains some data used by the nightly job scheduler (e.g. server connection parameters, queue state etc.). This data doesn't really warrant for the point-in-time recovery or even, for that matter, for backups, because it's much easier to re-create the whole database from scratch. At the same time it's a production database with mirroring enabled, so it can stay online and available if we switch to DR server for any reason.

So we are in a Catch-22 situation: either we switch to Simple and lose the automatic failover setup (which is kind of necessary for us to have), or we keep Full and take regular log backups that we don't actually need, otherwise transaction log file will keep growing (with data that, again, we don't actually need).

The only solution I could come up with is scheduling an automatic job that discards all transaction log data, but I've been wondering if there is a saner way to handle this.


texts: Are these two queries technically equal to each other?

texts:
Ran it in my query browser. I was am getting same results for both. But still need a confirmation.

SELECT
    cs.RSCode
    , CustomerName
    , em.EmailId
FROM
    Customer_STN cs
    JOIN EmployeeMaster em ON cs.RSCode = em.UserName
    LEFT OUTER JOIN Confirmed_Attendance ca ON cs.RSCode = ca.rsCode
WHERE
    isConfirmed=1
AND
    dateOfConfirmation = '2012-08-25'

Second query:

SELECT
    cs.RSCode
    , CustomerName
    , em.EmailId
FROM
    Customer_STN cs
JOIN
    EmployeeMaster em ON cs.RSCode = em.UserName
WHERE
    RSCode NOT IN (
        SELECT
             rsCode
        FROM
             Confirmed_Attendance
        WHERE
             dateOfConfirmation = '2012-08-25'
        AND
             isConfirmed = 1
)

EDIT: Added the WHERE part as Rob noticed. Not so similar any more.


texts: Postgresql Rename Table without Updating View Definitions

texts:
I've found out today that renaming tables in Postgresql also renames the VIEW definitions for Views that use the table I renamed automatically? Is there any way to turn this off?

So I rename the table with:

ALTER TABLE xyz RENAME TO abc;

And my VIEW defition for the sake of example is:

CREATE VIEW foo AS SELECT * FROM xyz;

Next thing I know after I rename the table, the definition for foo has changed to point to table abc.

I'm using Postgresql 8.4

--Updated: Dec 30, 2014 at 12:41pm--

Here is a test case to illustrate what I mean:

CREATE TABLE tmp_test_a (num integer);
CREATE VIEW v_tmp_test_a AS SELECT * FROM tmp_test_a;

-- Now look at what the VIEW is using (now is using tmp_test_a, which is what it should be using):
SELECT pg_get_viewdef('v_tmp_test_a'::regclass, false);

-- Now create a second table, and swap them.
CREATE TABLE tmp_test_b (num integer);
ALTER TABLE tmp_test_a RENAME TO tmp_test_c;
ALTER TABLE tmp_test_b RENAME TO tmp_test_a;
ALTER TABLE tmp_test_c RENAME TO tmp_test_b;

-- Now look at what the VIEW is using again (now is using tmp_test_b with an alias of tmp_test_a)
SELECT pg_get_viewdef('v_tmp_test_a'::regclass, false);

-- Cleanup
DROP VIEW v_tmp_test_a;
DROP TABLE tmp_test_a;
DROP TABLE tmp_test_b;


texts: Replicating into a Percona XtraDB Cluster

texts:
We have a 2 node (plus an arbiter) Percona XtraDB cluster. It is used for data indexing/archiving. It has it's own database called archive but it is also the replication slave for a database called primary. This comes from a standard master->slave Percona Server pair.

One of the clustered servers segfaulted (reason still being investigated...). This node was, specifically, the one that was doing replication. On restarting it, it deleted the contents of the MySQL data directory (/var/lib/mysql/) and did an SST from the other node. This went fine, but it now has a partially incomplete dataset on the primary database and no master binlog position information.

How should replication be restored?

It is not feasible to export the primary database as a mysql dump since it is circa 1TB in size when looking at the raw innodb files
If we export the primary DB with xtrabackup and prepare it as a whole on the clustered node, it is assumed it will get deleted as soon as the node starts SST
It is difficult but not infeasible to allow the node to do SST and then restore an xtrabackup of primary DB table by table... but it will take a while given 500+ tables

So, the question is... What is the correct way to set up replication into a multi-master cluster?