texts:
Is there a work around to use window functions in update statement

You can populate sample/original table using the code

Create table #table(
key1 Char(5),
date1 Date,
date2 Date
);

Insert into #table
Values ('abc','2019-07-22',NULL);
Insert into #table Values ('abc','2019-07-23',NULL);
Insert into #table Values ('def','2019-07-22',NULL);
Insert into #table Values ('ghi','2019-07-22',NULL);
Insert into #table Values ('ghi','2019-07-23',NULL);

I want to partition and Order by key1 - check for NULL in date2 - leave NULL intact if max(date1) over (partition by key1 order by key1) = date1 and change to max(date1) over (partition by key1 order by key1) if the above condition is not true. This is a data vault concept by the way.

Original:

Required:

I tried the below code:

UPDATE #table
SET date2 = CASE WHEN MAX(date1) OVER (PARTITION BY key1 ORDER BY key1) = date1
  THEN NULL
 ELSE MAX(date1) OVER (PARTITION BY key1 ORDER BY key1) END
WHERE date2 IS NULL ;

I am getting an error message:

  Msg 4108, Level 15, State 1
  Windowed functions can only appear in the SELECT or ORDER BY clauses.


texts: How to use a view to span two tables that are identical

texts:
We have table A that receives data from our shop floor and we currently have it so that the data is moved from table A to table B after 5 years. I want to streamline this but need help creating a view. I can change the stored procedure to move the data after one month, but need to be able to point to a view with the combined data of table A and B. I tried the below but get the following error: "Column name 'PARTNOAUX' in view or function 'Table_Full_' is specified more than once."

CREATE VIEW Table_Full AS
SELECT *
FROM [A].[dbo].[AUX] AS S
Inner Join [B].[dbo].[AUX]AS T
ON S.DATETIMEAUX=T.DATETIMEAUX;


texts: MariaDB running indefinitely on inner join with count

texts:
I am currently experiencing a very, very weird issue with a query in MariaDB. Our server experiences a "504 Gateway Timeout" quite often after a database upgrade, and I was investigating the issue.

After tracking it down and simplifying the query, I've come up with the following query:

SELECT
    COUNT(trace)
from traces
INNER JOIN banner on banner.banner_short = trace

This query does not work. It just runs indefinitely, not returning. This is most probably what causes the gateway timeout, since PHP cannot respond in time.

Now onto the more interesting part. Why does this query not run? If I change COUNT(trace) to * to select all the fields, it works just fine. Also, removing the INNER JOIN solves the issue as well.

How does an unused INNER JOIN in combination with COUNT affect a query like this? Why won't it complete?


texts: Copy database, problem with not existing principal

texts:
I'm trying to copy a database from a server to my laptop, using Management Studio. However, after about an hour of copying, I get the error

Cannot find the principal 'MVGSQL01\itsac', because it does not exist or you do not have permission

I do not have the option of using backups, as I do not have access to the file system on the source server.

I found something while searching on Google, that suggested I should do:

CREATE LOGIN SEC_USER  WITH PASSWORD = 'YourPassword'
Alter User Sec_User with login  = SEC_USER;

but if I change SEC_USER for ITSAC and Sec_User for itsac, I get almost the same error message:

Cannot alter the user 'itsac', because it does not exist or you do not have permission.

Some advice would be appreciated!


texts:
I am writing a query to check if I have got duplicates in a certain column.

SELECT COUNT(thecol) FROM thetable WHERE thecol IS NOT NULL GROUP BY thecol
  HAVING COUNT(*) > 1

That will give me an output like

3
5
6

Which of course is the number of records for each value of thecol where thecol is not unique.

Now how do I only get the sum 14? Should I have the existing query as a subquery?


texts: Performance difference between Clustered and Non Clustered Index

texts:
I was reading Clustered and Non Clustered Indexes.

Clustered Index - It contains Data Pages. That means the complete row
information will be present in the Clustered Index Column.

Non Clustered Index - It only contains the Row Locator information in the
form of Clustered Index column(if availabe) or the File Indentifier +
Page Number + Total Rows in a Page.  This means that the query engine must take an additional step in order to locate the actual data.

Query - How can I check the performance difference with a help of a practical example as we know that the table can have only one Clustered Index and provides sorting at the Clustered Index Column and Non Clustered Index don't provide sorting and can support 999 Non Clustered Indexes in SQL Server 2008 and 249 in SQL Server 2005.


texts: Database design question: Tags with values(duration) linked to them

texts:
I am creating a database that takes in daily inputs such as:

Date
Events in that date (e.g. project foo: 5 hours, bar: 2 hours, etc...)
Rating of the date

There's more but these are probably the only relevant inputs regarding the problem.
The problem is how do I construct my tables so that I have stored somehow the specific duration an event lasts. Currently what I have is:

A users table storing user log in credentials (1)
A rating table to store input date, rating of the date, etc... (2)
A tags table storing unique tags (3)

I will use the tags and ratings to look for possible patterns in the future in case that's relevant.

I don't find it difficult at all if I just had to deal with tags alone related to specific dates, as I would just have to have an unique table of tags and associate the tag_id with a date. But I have no clue how I store the duration of a tag on a specific day efficiently. I could of course only use a single table, accept redundancy with overlapping tags on different days and store tags, events and duration in a single string field. But that doesn't seem efficient to me.
I am very novice when it comes to databases and I appreciate the help.

I am using Python 3.7.2, SQLite and Flask-SQLAlchemy 2.4.0 to construct the tables.

Like so:

(1)

class User(db.Model):
__tablename__ = 'user'

id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True, nullable=False)
email = db.Column(db.String(120), unique=True, nullable=False)
password_hash = db.Column(db.String(128))

(2)

class Rating(db.Model):
__tablename__ = 'rating'

id = db.Column(db.Integer, primary_key=True)
date = db.Column(db.Date, unique=False, nullable=False)
rating_day = db.Column(db.Integer, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
# etc...

(3)

class Tags(db.Model):
__tablename__ = 'tags'

id = db.Column(db.Integer, primary_key=True)
tag = db.Column(db.Integer, unique=True, nullable=False)


texts: enforcing at least one child in a foreign key constraint (or 1:1..n relationship)

texts:
While trying to implement multi-language strings in a postgres DB, I came across "Data Modeling for Multiple Languages (on Vertabelo)". Especially "Approach #4", splitting non-translatable properties from translatable strings seems favorable to me:

there is no need to differentiate between a "favored child" (transation) and other children
no third table per object required
(which really feels clunky and not (performance) efficient - read below)
please refer to the article for all pros & cons

In order to enforce at least one translation (string) per object I tried to implement a 1:1..n relationship by adding a FK on the object table back to the object_string table, what leads to a circular reference of course.

Have a look at the

Model

(using only a single property and string per object. Representative purpose only, actual DB is more complex)

or use the psql dump:

CREATE TABLE language (
    id SERIAL PRIMARY KEY,
    rfc3066 character varying(10) NOT NULL
);
CREATE TABLE object (
    id SERIAL PRIMARY KEY,
    unit_string_id integer NOT NULL,
    property real NOT NULL
);
CREATE TABLE object_string (
    id SERIAL PRIMARY KEY,
    object_id integer NOT NULL,
    language_id integer NOT NULL,
    string varchar(100) NOT NULL
);

ALTER TABLE ONLY object_string
    ADD CONSTRAINT unit_string_language_id_fkey FOREIGN KEY (language_id) REFERENCES language(id) MATCH FULL ON UPDATE CASCADE;

ALTER TABLE ONLY object_string
    ADD CONSTRAINT unit_string_unit_id_fkey FOREIGN KEY (object_id) REFERENCES object(id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY object
    ADD CONSTRAINT unit_unist_string_fkey FOREIGN KEY (unit_string_id) REFERENCES object_string(id) MATCH FULL;

 -- this constraint is not related to the question. just ensure only one translation per string --
ALTER TABLE ONLY object_string
    ADD CONSTRAINT unit_id_language_id_unique UNIQUE (object_id, language_id);

Question

Keep in mind, that I'm going to implement as much as possible on the server side. Is this bad preference?
(==> I plan to use views, i.e. with custom ON INSERT DO INSTEAD
rules, to free the frontend from ideally any checking&logic requirements)
Is this circular reference still that disgusting / problematic these days?
AND if it is, what is more preferable:

create all string columns in the object table and store the default language string there
implement the relationship using a third (cross-reference) table ?
(I intended to avoid this, as it feels redundant, given the targeted approach is able to work elegantly. Btw: How much of a performance penalty could this be?)

Ignoring whether this approach is bad practice or not - out of curiosity:
Is it possible to deal with that at all (without alleviating NOT NULL constraints)
Is it possible to deal with that even elegantly (using rules, triggers, whatever...)

Any experience or comments welcome!


texts: Disable log ERROR: duplicate key value violates unique constraint

texts:
Running Postgresql in a better ask for forgiveness than permission mode during the inserts.

Works great, only problem: The logs are flooded with the following message

duplicate key value violates unique constraint

Is there a possibility to explictly disable this log?


texts:
I'm new to performance problems, but I saw some discussion that SSD disks solve performance problems caused by index fragmentation.
Has anyone experienced this?
I'm thinking of changing my hardware and I'm wandering if that would help


texts:
The MySQL version I'm using is 5.1.46.
The query is

CREATE DEFINER = CURRENT_USER TRIGGER `movimentacao_before_del_tr` BEFORE DELETE ON `movimentacao`
  FOR EACH ROW
 IF (OLD.stMov IN ("E", "C", "R")) THEN
    INSERT INTO `controle` (
      `tabela`,
      `data`,
      `hora`,
      `registro`
    ) VALUES (
      MD5("movimentacao"),
      OLD.dtEmissao,
      CURTIME(),
      CONCAT_WS("|",
        OLD.idMovimentacao,
        OLD.idEcf,
        OLD.numFabricacao,
        OLD.CCF,
        OLD.stMov,
        OLD.tpNota,
        OLD.modoMov,
        OLD.dtMovimentacao,
        OLD.dtEmissao,
        OLD.hrEmissao,
        OLD.numNF,
        OLD.idPessoa,
        OLD.cpfCnpj,
        OLD.rgIe,
        OLD.nomePessoa,
        OLD.codcidade,
        OLD.uf,
        OLD.cidade,
        OLD.bairro,
        OLD.logradouro,
        OLD.numero,
        OLD.complemento,
        OLD.cep,
        OLD.totalBruto,
        OLD.valAcrecimo,
        OLD.valDesconto,
        OLD.valOutrasDespesas,
        OLD.valFrete,
        OLD.valSeguro,
        OLD.bcICMS,
        OLD.valICMS,
        OLD.bcICMSST,
        OLD.valICMSST,
        OLD.valPIS,
        OLD.valCOFINS,
        OLD.totalLiquido,
        OLD.idNatOp,
        OLD.idAidf,
        OLD.especie,
        OLD.serie,
        OLD.docProprio,
        OLD.chaveNFe,
        OLD.motivoCancelamento,
        OLD.hrMovimentacao,
        OLD.IdTransportador,
        OLD.modFrete,
        OLD.qtdeVolumes,
        OLD.especieVolumes,
        OLD.marcaVolumes,
        OLD.pesoL,
        OLD.pesoB,
        OLD.infAdFisco,
        OLD.infCpl,
        OLD.enderecoTransp,
        OLD.cidadeTransp,
        OLD.UfTransp,
        OLD.finNFe,
        OLD.placaVeicTransp,
        OLD.ufVeicTrans,
        OLD.RNTC,
        OLD.idOperador,
        OLD.chk,
        OLD.hash,
        OLD.hash2
      )
    );
  END IF;

The returned is #1064 - Você tem um erro de sintaxe no seu SQL próximo a '' na linha 79. Translation: You have a MySQL syntax error near to '' on line 79.

The Workbench show an erro at the last line: syntax error, unexpected END_OF_INPUT, expecting ';'


texts:
I know there are many blog posts similar to this, and I have read them all and as far as I can tell things are setup correctly, but I still get invalid username or password when kicking off the copy job.
My setup and procedure is below:

Server A hosts source database in Domain A
Server B and C are in a cluster in Domain B.  I am log shipping to both of these servers to prep for eventual migration and adding into an Availability Group.
SQL Server agent accounts are created in AD on both domains as this: Server A:    A\sqlagent - Server B and C:  B\sqlagent
As above shows all 3 servers have the same exact username and password with the exception of the domains being different before each one
Log backups are placed locally on Server A, shared out with full permissions given to the A\sqlagent account.

As far as I am aware, that is all that is needed from a SQL perspective to get cross-domain LS working, however whenever the copy job runs, it fails for "invalid username and password".  We have a VPN tunnel between the 2 domains, however no Trust relationship exists.

I am able to path out to the backup share on Server A from Server B and C using the admin login to manually pull the backups over if I want, so I know the tunnel is properly working.

There is mention of using a local network account Here when doing cross domain log shipping instead of a domain login, but I am not sure how to create and use that.  Is that created as a different type of account in Active Directory? I already have the A\sqlagent and B\sqlagent created in AD.  Does the account need to be created differently to be a local network account?  I don't have much experience with AD and accounts.

Is this something that might be caused by network pass-through security?  unfortunately I am not finding any helpful information on setting up network passthrough security online.  As far as I am aware, this should happen independently of SQL Server, but if anyone has any info about what might be causing this issue, it would be most appreciated.


texts:
I get ORA-01410: Invalid ROWID error when executing the following piece of code:

DECLARE
        CURSOR c1 IS
      SELECT e.empno,
             e.ename,
             e.sal,
             d.dname
      FROM   emp e,
             dept d
      WHERE  e.deptno = d.deptno
             AND e.deptno = 10
      FOR UPDATE OF sal NOWAIT;
    c2 c1%ROWTYPE;
BEGIN
    OPEN c1;

    LOOP
        FETCH c1 INTO c2;

        Exit WHEN c1%NOTFOUND;

        IF c2.dname = 'ACCOUNTING' THEN
          UPDATE emp1
          SET    sal = sal + 1000
          WHERE  CURRENT OF c1;
        END IF;
    END LOOP;
END;
/

Please suggest a way to overcome it.


texts: How to enforce ON DELETE RESTRICT on MySQL?

texts:
This is my SQL code:

-- DROP the database if it exists
DROP DATABASE IF exists labtest;

-- CREATE THE DATABASE
CREATE DATABASE labtest;

-- SHOW DATABASES;
SHOW DATABASES;

-- Use the database
USE labtest;

-- Create Table 1
CREATE TABLE user(
    userid INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50));

-- Create Table 2
CREATE TABLE sale(
    saleID  INT AUTO_INCREMENT PRIMARY KEY,
    userid  INT NOT NULL,
    item VARCHAR(50),
    FOREIGN KEY (userid) REFERENCES user (userid) ON DELETE RESTRICT);

-- Create Table 3

SHOW TABLES;

-- INSERT
INSERT INTO user(name)
VALUES ('A'),
('B'),
('C'),
('D'),
('E');

INSERT INTO sale(userid,item)
VALUES (2,'item1'),
(3,'item2'),
(1,'item3'),
(4,'item4'),
(4,'item5');

-- SELECT (READ)
SELECT saleID,s.userid,name,item
FROM user as u,sale as s
WHERE u.userid = s.userid;

-- DELETE (DELETE)
DELETE FROM user
WHERE name='D';

When I try to delete I don't get an error like I'm supposed to, even though I'm deleting a parent record. How do I impose a restrict action? Adding ON DELETE RESTRICT doesn't help either.


texts: why computed columns slowing down performance all the sudden?

texts:
Users were able to run reports before 10 am.
After that same reports became very slow, sometimes users just didn't have patience to wait.
After some troubleshooting I found the column that was causing the delay. It was computed column that uses function in order to bring the result.

Approximately at the same time I got another complain about slow running report, that was always working fine. After some troubleshooting I found the columns that was causing a delay:

where (Amount - PTD) <> 0

And again, the Amount column is computed column.

So my question is: why all of the sudden computed columns that were always part of the reports started to slow down the performance significantly?
What could really happen approx after 10 am?
And what is the disadvantage if I make those columns persisted?

Thank you

--FUNCTION that bring the column:

ALTER FUNCTION [dbo].[CalcInvoiceAmtPTD]
(@SInvNum INT, @entityGuid uniqueIdentifier) RETURNS MONEY
AS
BEGIN
    DECLARE @Amt MONEY

    declare @toplevel uniqueidentifier
    set @toplevel = (select dbo.gettoplevelentity(@entityguid))

    declare @t table
    (
        Guid uniqueidentifier
    )

    insert into @t select * from dbo.getlinkedentities(@toplevel) where guid is not null
    declare @tbl table (amount money, glacctid int)

    select @amt = isNull(sum(amount), 0) from tblfin_journalpostings jp
        inner join tblfin_journal j on j.transactnum = jp.transactnum
            and (voiderfor is null and voidedby is null)and j.transdescid <> 'I'
        inner join tblfin_glaccounttypes glt on glt.glacctid = jp.glacctid and glt.accounttype = 'p'
        inner join @t t on t.guid = jp.entityguid
    where invoicenum = @SInvNum

    RETURN ISNULL(@Amt  , 0)
END


texts: MySQL 8 (current) W2K16 server fails to start

texts:
Brand new Xeon, 64gb, 2tb RAID 5 W2K16 server.  Installed current MySQL 8, setup as dedicated server and ran through setup just fine.

Was having problems with slow restores from mysqldump.sql file so after reading I sent to adjust the default 8M up to 32M just to try (Percona suggests up to 51G)

innodb_buffer_pool_size=8M

Restart the service and get the following error immediately after it starts:

The MySQL80 Service on Local Computer started and then stopped.  Some services stop automatically if they are not in use by other services or programs.

I did see that it had changed the log on as to Network Service, so per some other stack questions I put it back to Local System Account and enabled can interact with desktop.

No dice.  Nothing I seem to do will get the server to start even though I immediately changed that innodb_buffer_pool_size back to 8M.

UPDATE

It seems it might have been a permissions issue.  I removed all traces of MySQL and reinstalled it.  I examined permissions on all directories and it looks like the configuration did not set the NETWORK SERVICE to have full control of the logs directory if it wasn't in it's default location under Data.


texts:
I currently have a table called sample, the structure of my attendance table is:

create table sample
(
  Student_ID VARCHAR2 (10),
  Late_Attendance number (10) Default 3,
  Attendance_Date Date,
  Attendance_ID VARCHAR2 (10)
);

Sample data:

insert into sample
values ('SCM-026020', default, To_Date ('4/4/2016', 'DD/MM/YYYY'), 'LATE');
insert into sample
values ('SCM-026021', default, To_Date ('4/4/2016', 'DD/MM/YYYY'), 'ABSENT');
insert into sample
values ('SCM-026022', default, To_Date ('4/4/2016', 'DD/MM/YYYY'), 'PRESENT');
insert into sample
values ('SCM-026020', default, To_Date ('5/4/2016', 'DD/MM/YYYY'), 'ABSENT');
insert into sample
values ('SCM-026021', default, To_Date ('5/4/2016', 'DD/MM/YYYY'), 'ABSENT');
insert into sample
values ('SCM-026022', default, To_Date ('5/4/2016', 'DD/MM/YYYY'), 'PRESENT');

This is the SQL I used based on pivot:

select *
from  (
        select student_id, attendance_Date, attendance_id,
        sum (DECODE (Attendance_id, 'ABSENT', 1, 0)) absent,
        SUM (DECODE (Attendance_ID, 'LATE', 1, 0)) LATE,
        SUM (DECODE (Attendance_ID, 'PRESENT', 1, 0)) PRESENT
        from sample
        group by student_id, attendance_id, attendance_date
      )

pivot (
          MAX (Attendance_ID)
          for Attendance_Date IN (To_Date ('4/4/2016', 'DD/MM/YYYY') "4/4/2016",
                                  To_Date ('5/4/2016', 'DD/MM/YYYY') "5/4/2016")
      )

Why I am getting such weird output?

STUDENT_ID     ABSENT       LATE    PRESENT 4/4/2016   5/4/2016
---------- ---------- ---------- ---------- ---------- ----------
SCM-026021          1          0          0 ABSENT     ABSENT
SCM-026022          0          0          1 PRESENT    PRESENT
SCM-026020          1          0          0            ABSENT
SCM-026020          0          1          0 LATE

The Student_ID scm-026020 is appearing twice which should only appear once and the sum such as absent for scm-026021 should be 2 but displaying as 1.

My expected output would be:

STUDENT_ID     ABSENT       LATE    PRESENT 4/4/2016   5/4/2016
---------- ---------- ---------- ---------- ---------- ----------
SCM-026021          2          0          0 ABSENT     ABSENT
SCM-026022          0          0          2 PRESENT    PRESENT
SCM-026020          1          1          0 LATE       ABSENT

There should always be one row per student and day.

If I remove the sum (DECODE (Attendance_id, 'ABSENT', 1, 0)) absent part, it gives the correct output, but I need to put this since I need to calculate at the end the number of attendance days they are late, absent, and present.


texts:
I could see any proper documentation which answer these questions

1.Is MySQL database and cluster supported on AIX operating system?

2.Is MySQL database and cluster free for commercial use in any private enterprise application ?


texts: is it possible to pass the message from sp_add_alert to the job?

texts:
sp_add_alert stored procedure can react to different system messages and execute a job in response. It also can notify a person with the message text by email, pager of net send.

But how do I pass the sysmessage message (corresponding to the event that caused the alert) not to a person but to the job that is executed in response to the alert?

Let's consider this message:

select [description] from sysmessages where msglangid = 1033 and error = 829

This will yeild:

  Database ID %d, Page %S_PGID is marked RestorePending, which may
  indicate disk corruption. To recover from this state, perform a
  restore.

I'd like to receive this message in the job, so the job knows what %d and %S_PGID caused a problem.


texts:
I'm trying to deny all permissions on the dbo schema for a particular user, as I only want that user to access a specific schema. When I try the following (using sa):

DENY CONTROL, ALTER ON SCHEMA :: dbo TO AppUser

I get this message:

  Cannot grant, deny, or revoke permissions to sa, dbo, entity owner,
  information_schema, sys, or yourself.

Is it possible to deny all dbo schema permissions for a user?

EDIT: I've just had a thought - there are a few tables and views in the dbo schema that are owned by public (not my idea and I can't change them). Could this be why I am getting the message? If so, is there any way I can get around this? The purpose of denying access to dbo is to prevent unnecessary access to these tables.

EDIT: I think I got confused with the public role - the actual owner of the tables and views I mentioned is dbo, but the public role has SELECT, INSERT, UPDATE etc permissions. I've tried reproducing the message on a new database which has a table with public role permissions as in the other database, but this does not show the message - it is successful and the denied user can no longer see those tables. So to answer my question, yes it is possible, but I'm not sure why it isn't working for me.


texts: Should every table in my database include store id?

texts: I am designing single database for multiple stores. Every store may have different products and stock. should i have to include store_id in every table in database. my database looks like this


texts: PostgreSQL: How to backup only One Schema from a database and restore it to other schema having same name of database?

texts: I have a database named "A" which has three schemas "B","C" and "D". I want to take backup of schema "C"  and restore the data to Schema "B"? not sure how to do this as I am new to Postgres. pls help with commands.


texts: Is there an easy way to update connection strings to a database moved to a different instance

texts:
I have created a new instance of SQL Server 2008 r2 and exported my database to the new instance.  Then I realized that the connection string for all of my datasets still point to the original database.  Is there an easy way to update the connection string for each of the datasets?

This also brings a question on deployment.  Each customer that purchases the software would have a different connection string.  Can you point me to some documentation that would explain how to handle this scenario?


texts:
I am curious, why does SQL Server save the values in msdb.dbo.sysschedules for date and times as int instead of datetime.  I assume the reason dates back to something in SQL Server 2000.

Was it a storage capacity issue, performance, or other?


texts:
Beforehand the table names and columns are in Spanish, my native language is Spanish.
(table names have been now edited to English)

I've got a database to manage the tickets sales of a trip that is made by a bus, the problem is that when I related the tables I didn't reason that when setting a trip to a bus the seats of this will be the amount of tickets of the trip, and for each trip this tickets will be different. Also the seat state has to change from available to unavailable when someone buys the ticket of that seat in each trip.

Here's the db model - only the tables involved in this problem :

EDIT: I renamed the table names to English language for be easy to read.

The only way that I imagine to do this is to create another table where I will insert the number tickets of a trip depending on the number of seats that a bus has, and when showing the available stalls in the app I'll set unavailable the seats of the bus where the number of seat is equal to the number of ticket that is already bought. But I don't feel this way leads to a normalized database.


texts: SQL Developer code 'Advanced Format' versus 'Custom Format'

texts:
Preferences -> Code Editor -> Format -> Advanced Format
versus
Preferences -> Code Editor -> Format -> Advanced Format -> Custom Format

As I understand, both are alternative options to (re)format code in SQL Developer, where Advanced Format bases on .xml and Custom Format bases on .prg.

It seems, that the Advanced Format is the default, which is used when pushing Ctrl+F7.

How can I deactivate Advanced Format and activate Custom Format now?


texts:
I've a table called foo which stores the child and parent information of some entity called bar, with level information as well. If there is no parent of bar, then in the parent_id column it will store its own id.

id  parent_id
1   1
2   2
3   3
4   1
5   2
6   4

So in my example, the bar entities with an id of 1 and 2 are at level 0 (no parent) and have children. The bar with an id of 4 has a child but it also has a parent, so its not at level 0.

I am trying to write a mysql query which will return all the bars that have children and are at level 0 (no parent).

I am able to write the query below, which shows the bars that have children:

select * from foo where id!=parent_id;

And output of above query is :

id parent_id
4   1
5   2
6   4

But in above query, parent_id 4 is not at level 0 and I need a query that produces the output below:

id parent_id
1   1
2   2

Let me know if you need any additional information from my side.

Edit :- Thanks Evan for pointing out my mistake, actually I want ids "that have children and are at level 0". Also I've updated the correct O/P.


texts:
I have been trying to start SQL Server 2017 and been getting
 'Not enough storage is available to complete this operation. '

Full Error:

  TDSSNIClient initialization failed with error 0xe, status code 0x1.
  Reason: Error: 19101, Severity: -1, State: 0. (Params:). The error is
  printed in terse mode because there was error during formatting.
  Tracing, ETW, notifications etc are skipped.  Not enough storage is
  available to complete this operation.

on the System Event:

  The SQL Server (MSSQLSERVER) service terminated with the following
  service-specific error:  Not enough storage is available to complete
  this operation.

I do have enough space on all the drives, any help is appreciated.


texts:
I have an ETL process (in SSIS) that extracts data from the database and writes the data to a predefined text file. Currently, I create the directories and files manually to satisfy this process.

To add a new variable to this process, I run a usp that adds a new row to a table, which is read in to the ETL process and determines which packages to run.

To remove the manual creation of the directories and files mentioned above, and to remove the possibility of human error, I want to create the new directory and file when a new row is added via the usp.

In short; I check to see if the subdirectory exists using xp_dirtree, and then use xp_create_subdir to create the new subdirectory. What I want to do now is create an empty text file, in the new subdirectory, that will have data written to it during the ETL process.

The options I think I have are the following:

Use xp_cmdshell; I recognise that there is a stigma attached to this, but I am not adverse to using it if it is the most optimal way. This is disabled by default, but I can reconfigure on and off within the usp.
Use fso; I'ved used fso within .NET scripts, but have no experience of using it within SQL Server. I can't seem to find any resources that dictate how to create a text file, only read and write.
Use SSIS; within the ETL project I have a flat file connection manager, where the connection string is dynamically set, based on the name of the variable. I'm unsure if the text file will automatically be created if it does not exists? I have manually created them to avoid any chance of failure. If the text file is automatically created, this could be an option.

I've purposely left BCP out of the options above, as I don't have a result set to copy, and therefore presume this is not a viable option? Unless I can "copy" an empty dataset to create the text file?

Can anybody provide any useful resource for how to accomplish this? Or can anybody provide any pointers on how best to achieve this? Is xp_cmdshell the best way? I want to create an empty text file when I create the new subdirectory.

Thanks in advance for any help offered.


texts: Executing SSIS Package from a stored procedure with different user privileges

texts:
I'm having issues with allowing my users to execute SSIS Packages in a reasonable manner due to varying levels of privilege required.
The scenario: we've created a data warehouse, with two different SSIS packages responsible for loading it with data, one is to be run automatically (via a SQL Agent job, and is working fine), and another that must be run on-demand by users once upstream data is finalised and cleansed etc.
This package performs very privileged operations including backing up the database at the start of the run (to be sure, to be sure), dropping and recreating calculated tables etc.
I've written a stored procedure to execute this job via the [SSISDB].[catalog].[create_execution] and [SSISDB].[catalog].[start_execution] stored procedures.... this works fine when run under my account (I'm a sysadmin).
The stored procedure failed when run by a normal user due the higher level of permissions required in SSISDB and MSDB to enqueue the execution, and the package itself failed because it is running under their (lowly) security context.
What I've tried:
I attempted to resolve the issue using 'Execute As' in the stored procedure, however this failed due to the cross-database chaining issues, Trustworthy flag etc.
I've also attempted to solve the issue by having Agent jobs to run the package, and just running the agent job from the stored procedure, however I rapidly entered a world of pain involving:

The inability to set execution permissions on a per-job basis
The hope to configure this access via a central Server Role to cater for changing staff over time, and jobs can only have a single user as owner
The dark world of Proxy accounts, Credentials in combination with sql-auth logins etc

Plans C and D
The only options I can think of remaining to me are to create a dedicated SQL Server Login with elevated permissions, and trust the users not to pass the credentials around/lose auditability of who scheduled the import (how this problem is solved in other areas of the organisation), or custom build a web front end purely to allow the users to authenticate as their 'Server Role' account, and then let the web app run the stored procedure under a second (privileged) connection.
So....
Is there any advice out there on how to:

have an SSIS package perform privileged operations
executed by a lowly privileged user (using an AD windows account)
preferably where the access to run the job is managed via a central Server Role (I don't have an easy ability to create a new windows group for them)
and where any new, intermediate/proxy accounts are SQL Server Auth accounts (again, very limited ability to make changes to the AD)

I understand there's a lot of moving parts here (and some feel like spinning blades) so let me know if there's any other info you feel I've missed.
Cheers,
Tim
Edit....
So today I created a dedicated SQL Server login with ssis_admin permissions, created three SQL Server Agent jobs owned by that user, and updated the stored procedure my end users call to execute as that user.  This failed due to the inability to call create execution as a SQL Server login, it requires a windows account.
I updated the users stored procedure to execute as the Windows account SQL Server is running as (an AD service account), granted it ssis_admin and it fails with the error

The current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again.

This isn't going anywhere fast :(


texts: How to restore SQL Server database from Azure Blob?

texts:
I created a database (IaaS) from Azure marketplace (Windows server 2012 + SQL Server Web 2014). I Selected automatic backups to blob storage. Now I'm trying to do a test restore operation.

I imported my storage account to Management Studio (on VM). By right-clicking on bak file in blob and than going for Import Data-tier Application i started a wizard:

Having followed the wizard, I chose the proper destinations for DATA and LOGS (the same directory where other databases are).

The wizard finished with an error:

Error said:

  The range specified is invalid for the current size of the resource. (Microsoft.SqlServer.StorageClient)
  The remote server returned an error: (416) The range specified is invalid for the current size of the resource.. (System)

I also tried a different approach:

I created an empty test database and tried to execute a Resore task on it. I chose Device and than URL for source. It allowed me to automatically connect to my Azure Storage account and select a bak file from blob.

As soon as I selected the bak file I got an error message:

Please note that all of the bak files have 0 Bytes size (first screen). This may have to do something with the failure.

All of the tasks I was performing form Management Studio installed automatically on the VM. Blob storage and SQL Server all came in a package form Azure marketplace.

I'm using SQL Server 2014 Web edition.

What am I doing wrong? How can I restore the database?


texts: MongoDB Replica Set & Data Sync behavior

texts:
I have a replica set with 3 nodes X, Y & A (where A is an arbiter). Currently X is assigned a higher priority than Y. When X goes down, Y becomes the PRIMARY. When X is back in action, the data sync happens, Y continues to be the PRIMARY and X acts as SECONDARY.

My requirement is as follows:

When X is back in action, I want X to automatically become the PRIMARY (i.e. without any manual intervention), once the X has synced all the data from Y.

I understand that I shouldn't be really worried about which node is primary and which one is secondary, but in my case the performance of Y node is really bad (due to the network latency) and would like to keep X as PRIMARY as much as possible.

Is there any settings / configuration in MongoDB that I can use to achieve this behavior? The version I'm using currently is 3.4.3

Thanks.


texts: Alert and notification option in job properties

texts:
During SQL Agent Job configuration I see the options to define ALERT and NOTIFICATION.
I see that notification is what I'd expect, a notification of a job failure for example.
But I do not understand the requirement to create a notification within the job configuration. For example there I can configure to notify me about general Sql Server events or the Memory Manager\Max Workspace Memory Counter to fall under a limit.

  Are these general server health and performance monitoring options
  that are completely independant of the job I am actually configuring?

I am staring at the screen wondering whether I am just stupid or this is a design failure of Microsoft?


texts: Where mysql writes its log seqeunce numbers and last checkpoint at?

texts:
I'm working on developing mysql log collecting system.
To do that, I have to collect log sequence numbers from mysql LSN and last checkpoint location.
But I have no idea where should
Which log file should i use to get them?


texts: Why do we need partitions in PostgresSQL? (or any other RDBMS)

texts:
So, I am trying to teach myself partitions in PostgreSQL. I understand that a database can become slow when a table hits millions+ of lines and indexes don't fit into memory anymore.

One thing that strikes me is how partitions with sub-partitions resemble a lot to some kind of manually managed indexes. What is wrong with regular indexes? Why can't them partition themselves?

I imagine this must be hard, judging by the fact that this problem seems to have been around for a while. Maybe the DB cannot know how we plan to access our data? If so, why isn't there some simple way to tell the DB how we want to access our data?

Why do we have to do the whole table inheritance dance when partitionning is such a common case?

EDIT: There seem to be some work in progress to support a nicer syntax, Cf. https://wiki.postgresql.org/wiki/Table_partitioning


texts: Why slaveDelay is not respected by mongoDB replica?

texts:
I've seen related article below:

Mongodb replica config slaveDelay but data still sync immediately

My secondary node is set to be delayed by 3600 seconds:

...
"members" : [
    {
        "_id" : 0,
        "host" : "primary.mongo.com:27017",
        "arbiterOnly" : false,
        "buildIndexes" : true,
        "hidden" : false,
        "priority" : 1,
        "tags" : {

        },
        "slaveDelay" : NumberLong(0),
        "votes" : 1
    },
    {
        "_id" : 1,
        "host" : "secondary.mongo.com:27017",
        "arbiterOnly" : false,
        "buildIndexes" : true,
        "hidden" : true,
        "priority" : 0,
        "tags" : {

        },
        "slaveDelay" : NumberLong(3600),
        "votes" : 0
    }
],
...

Slave reports that:

devrepl:SECONDARY> rs.printSlaveReplicationInfo()
source: secondary.mongo.com:27017
    syncedTo: Sat Nov 10 2018 09:28:54 GMT-0500 (EST)
    0 secs (0 hrs) behind the primary

I cannot see the delay in the report and in action. Why is this happening? When delay is applied? All data are synced now.


texts: I do not see SQL Server instance name under roles section of Fail over cluster manager screen! How to setup?

texts: I have created 2 Virtual machines in azure and configured Windows Cluster using fail over feature. I have enabled the "AlwaysOn" feature in SQL Server Configuration Manager. But I do not anything under Roles section of Failover cluster manager window as shown below. In many tutorials I see SQL Server instance names under roles. How it comes? do we really need them?


texts: Linked server connection issue with SQL Server and Lotus Notes

texts:
I am trying to established connection between SQL Server and Lotus notes database (.nsf) file using Linked Server.

But, while creating linked server connection i m facing some issue. Please check the image for same issue.

Any idea how to resolve this.


texts: Does SQL Server care about the location of accounts in Active Directory?

texts:
Some maintenance is being worked on in our Active Directory here and my supervisor asked me if SQL Server cared about the location of security and service accounts.

I told him I didn't think it did but I wasn't 100% sure.

So, for service accounts and security groups used to control access to the database, does SQL Server care where they are in Active Directory?


texts: Troubleshooting Failed SQL login from WMI service

texts:
Every 5 minutes I have a couple of failed login attempts from a Biztalk server to the SQL server.

  Login failed for user 'DOMAIN\SERVER01$'. Reason: Token-based
  server access validation failed with an infrastructure error. Check
  for previous errors

I traced the failed logins to back to a service on the Biztalk server. The failed PIDs were pointing to a wmiprvse.exe that was using a LOCAL SERVICE.

I know I could either grant the Biztalk server access to the SQL server or I could change that service to run with a different account BUT I'd like to know what is using the WMI service before doing this.

How can you tell what is using the WMI service?


texts: Removing duplicate rows and changing related foreign keys in PostgreSQL

texts:
I have found all duplicate rows and now want to delete them after I update related foreign keys in other tables so they point to the correct row.

How do I get a list of foreign keys related to my table?

I have seen a number of old posts with heavy queries and wonder if there is an easier way nowadays?

Update: I have added an answer with a working query and would be happy if there is something more simple.


texts: SQL enforce value of a column if other column is NOT NULL

texts:
I'm having a hard time to explain this so let me illustrate it with an exemple.

I have a table for comments and it looks like so:

CREATE TABLE comments (
    id INT UNSIGNED AUTO_INCREMENT,
    parent_id INT UNSIGNED,
    article_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    ip VARCHAR(255) NOT NULL,
    submitted_at TIMESTAMP DEFAULT NOW(),
    comment TEXT,
    PRIMARY KEY (id),
    CONSTRAINT fk_com_parent
        FOREIGN KEY (parent_id)
        REFERENCES comments (id)
    CONSTRAINT fk_com_article
        FOREIGN KEY (article_id)
        REFERENCES articles (id)
    CONSTRAINT fk_com_user
        FOREIGN KEY (user_id)
        REFERENCES users (id)
);

Now it makes sense that if parent_id is set, child and parent should share the same article_id.

Answers to a comment must be on the same article. So is there a way to contraint this?


texts: What is a good strategy for updating lot of rows in postgersql?

texts:
I have an inventory table which contains item_id and the quantity remaining of the item (And also some other meta data). I have an administrator update the inventory by uploading a CSV file which contains the item_id and the quantity remaining.

Run `update for each row in the CSV file. If my CSV file contains 1 million rows, I will end up sending 1 million update statements from my application server to the database server.
Construct 1 million update queries and send them in a batch (JDBC allows batched statements)

At first glance approach number 2 looks like a better solution. But then can 1 million statements be batched? What happens if one of the statement fails for some reason?


texts: How to set up simple database replication using WAMP server?

texts:
Just need a little help here about replication. I have an intermediate knowledge in Mysql. And I can't find any simple solutions on web how to establish a database replication in Mysql. All I need to do is to create a back up from my master database to my localhost.
For example if I have a master database on 192.145.114.12 and a database 'myorg_site' and in my localhost I have database 'bk_myorg_site'. Any changes in the table data in myorg_site will change as well in bk_myorg_site. Can you provide me a simple instruction for that? Starting from mysql configuration? That's all guys I hope you can help me. Thanks.


texts:
I am looking at the data kept in the various SQL Replication tables.  I am trying to understand what information is available and what it means.
In this example I have push replication on the database AdventureWorks2014 in a SQL 2017 Enterprise edition instance.  The subscription is on the same instance.
The table sysarticles has the column status and it is showing a value of 57, I don't understand how that is possible, nor what it might mean

status    tinyint
The bitmask of the article options and status, which can be the bitwise logical OR result of one or more of these values:
1 = Article is active.
8 = Include the column name in INSERT statements.
16 = Use parameterized statements.
24 = Both include the column name in INSERT statements and use parameterized statements.
64 = Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
For example, an active article using parameterized statements would have a value of 17 in this column. A value of 0 means that the article is inactive and no additional properties are defined.

This query
SELECT TOP (1000) [artid]
      ,[del_cmd]
      ,[dest_table]
      ,[filter]
      ,[ins_cmd]
      ,[name]
      ,[pubid]
      ,[status]
      ,[type]
      ,[upd_cmd]
      ,[schema_option]
  FROM [AdventureWorks2014].[dbo].[sysarticles]

Gives these results

There is one clue (emphases mine)

The bitmask of the article options and status, which can be the bitwise logical OR result of one or more of these values:

Which leads to sp_addarticle Additionally I have included the value of schema_option in the results the value in my example is 0x000000000803509F I am not sure how/if this could create the 57

schema_option binary(8)
A bitmask of the schema generation options for the article, which control what parts of the article schema are scripted out for delivery to the Subscriber. For more information about schema options, see sp_addarticle (Transact-SQL).

I can understand 17, but how do you get 57?  1+8+16+24=49
If you subtract one (57-1=56) Fifty Six is a factor of Eight, (7*8=56) but it is hard to imagine where any potential values could be reported twice.


texts: Mysql Connection Pooling Similar To PostgreSQL's PgBouncer

texts:
I'm looking for a pooling solution for mysql that is independent of the language. I am using PGBouncer as an example because it

Resides on the server the database is on
Just change the connection string to use the pooling connector
No modification to the code required

Is there something similiar in MySQL where I can using pooling by installing a 3rd party connector and just connect through that port?


texts: How to generate the E-R Diagram of Wordpress using mySQL Workbench

texts:
I am trying to generate the E-R Diagram of Wordpress using the 'Reverse Engineer' feature. There is a slight obstacle though.
The diagram does not show the relationships between the entities. Is this the way it's supposed to work or am I missing something?


texts: Performance penalty for using thousands of tables in MySQL

texts:
Background
I'm using WordPress multisite in which each user will have a separate blog. For each blog, additional 15 tables are created. The website will be used by thousands of users. That means that there will be over 15,000 tables which will grow with each user.
Questions

Is there a performance penalty for having this many tables on a single database?
How significant it is compared to a single WordPress installation?


texts: COMMIT after EXPLAIN ANALYZE of a read-only query

texts: In PostgreSQL, what effect could produce issuing a COMMIT after executing an EXPLAIN ANALYZE with a read-only query?


texts: SQL Server Transactional Replication and Security Setup

texts:
I am new to SQL Server replication. I was able to setup transactional replication successfully, but I feel like I am missing some important concepts regarding security and accounts. I have a few basic questions:

1 - Location of Snapshot.

Currently my distribution server and publication is on the same machine so my path is local and I guess it works. Besides distribution server does publisher also need access to "Snapshot" directory? Say what if my distribution server was on a different PC within LAN (I can use UNC) or maybe on a completely different remote location, then how would publisher access the snapshot?

2 - Publisher Security

What is the role of the Snapshot Agent here, what resources does it need access to? is it asking for the access to the physical "SnapShot" directory?

And what about Log Reader Agent, which resource does this user needs access to?

3 - Subscription Security

"Agent Process Account", what is the role of this user? what resources does this user needs access to at subscriber?


texts:
Suppose you have three tables, t1, t2, and t3 with the following relational fields.

t1:

id (PK)

t2: for good business reasons, t2 has a composite primary key on {t2.id, t1.id}

id
t1.id (FK)

t3:

id (PK)
t1.id
t2.id

The key relationship t3 has is with t2. So, t3 has a composite foreign key constraint on {t1.id, t2.id}.

The question: must t1.id in t3 also be a foreign key? Why or why not?

My initial instinct is that of course, this additional FK is required. t1.id is the PK in t1, it must be a FK in t3. However I now believe it is not necessary because it does not add any information about the relationships between the three tables.

If t2 had a simple primary key, of course there would be no reason to include t1.id in t3. From a relational standpoint, it is therefore sufficient for t3 to reference t2 via the composite FK.


texts: Function/procedure to use dblink to fetch remote data and insert to multiple local tables

texts:
Am trying to fetch data from remote db and insert into three local tables. Currently I have a query which is successfully getting the data into one table.

But now I was thinking of creating a function to do the whole procedure including inserting the data into the three tables. I guess am stuck here...


texts: Can SQL Server 2016 R Services be used to connect to Hadoop data?

texts:
I've only been experimenting with SQL Server 2016 recently. So, please correct me if my assumptions are incorrect:
From some research on SQL Server R Services, I see that RxHDFSConnect and RxHDFSFileSystem functions help load data from Hadoop directly into a SQL Server 2016 database.

Can these functions still work without a Polybase connection to Hadoop?
If yes, why use the Polybase connection at all?


texts:
So, I'm running some unit tests against some InnoDB tables in MySQL.

Each unit test is wrapped in a transaction:

setUp:

SET autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;

tearDown:

ROLLBACK;

Each test does one or more queries: SELECT, INSERT, UPDATE etc.

The first time I run the tests, they all pass. But if I run them again immediately, I start getting failures. If I wait a while and then run the test a third time, the failures go away.

Any insights to what might be causing this and how I could prevent it?


texts: Does index need to cover all columns selected?


texts: Misunderstanding statement in MySQL documentation

texts:
I can't understand this statement in Optimizing Data Size:

  Declare columns with identical information in different tables with
  identical data types, to speed up joins based on the corresponding
  columns.

Can anyone describe this statement with example?


texts: what are the different ways to specify the values in PostgreSQL?

texts:
I have a question regarding the value initialization

In query tool:

Insert into tablename(field1, field2, field3..)
values (value1, value2, value3..);

In my JavaScript code, I am using:

db.query({ text: 'INSERT INTO tablename(field1, field2, field3)
    VALUES ($1, $2, $3)', values: [value 1 , value 2 , value 3'] }).then(...)

is this a good practice?

select * from tablename where _id="+[value]+" AND field1 = "+[value]+";

I have listed 3 query above. As per my knowledge 1 & 2 are right as I tried them in many of my projects. Can someone clarify is the option 3 is also a correct method? Does it match the standards?


texts: How does the PostgreSQL query planner estimate the aggregated rows count?

texts:
During an SQL optimization session I ran into the following problem (I won't post the full query and its plan as they are quite large):

The plan:

    ->__SOME_OTHER_NODES__
          ->  Nested Loop Left Join  (cost=1266.92..20967.78 rows=1 width=8) (actual time=178.240..17254.507 rows=64 loops=1)
                Join Filter: ((col1 = col5) AND (col2 = col6))
                Rows Removed by Join Filter: 6081

                -> __A_NODE__

                            Estimated                                   Actual
                ----------------------------------------------|-----------------------------------------------|
                                                              |                                               |
                ->  GroupAggregate  (cost=0.42..19701.14 rows=1 width=40) (actual time=171.277..269.533 rows=96 loops=64)
                      Group Key: col1, col2
                      ->  Subquery Scan on sub  (cost=0.42..19700.72 rows=55 width=40) (actual time=171.271..267.186 rows=8895 loops=64)
                            Filter: ((col1 = 1) AND (col2 = 4))
                            Rows Removed by Filter: 229732
                            ->  WindowAgg  (cost=0.42..16120.38 rows=238689 width=28) (actual time=0.136..234.920 rows=238627 loops=64)
                                  ->  Index Only Scan using idx1 on tbl1 dpa  (cost=0.42..10749.88 rows=238689 width=28) (actual time=0.009..55.740 rows=238627 loops=64)
                                        Index Cond: ((col3 = 2) AND (date < '2016-02-16 23:59:00'::timestamp without time zone))
                                        Heap Fetches: 25344

As you can see, the planner erroneously estimated 1 rows in the bulk GroupAggregate node therefore it chose NestedLoopJoin instead of Merge/Hash Join with Materialize. And actually it performs the aggregation 96 times which is very expensive.

So, I think the issues is with poor statistics. How does the planner estimate the rows count for an aggregate node, so I could fix that?

I couldn't find any info about it in the documentation. The only join node was explained.


texts: SQl server 2008R2 Performance issue after SP3 update

texts:
We have recently applied SP3 KB2979597 and Security Update Patch KB4057113 on Sql server 2008R2 but after that our couple stored procedures runs slow, it's taking almost 4 to 5 hours, but normally it was running under 5 minutes.
Any one has idea that SP update causing any issue?
We have applied earlier same SP and security patch on test server and we didn't have any issue.
Any quick guidance really appreciate.
Thanks for your help!


texts: SQL Server Audit Specification - filter DML by all but one user

texts:
I'm setting up a database audit.  I'd like to capture any DML change that is issued by database user other than the service account related to the application.

In short, this is to prove to our external auditors that no users are tampering with data outside of the application.

This following script sets up a DML audit that is global to all principles.

CREATE DATABASE AUDIT SPECIFICATION [OurDatabaseAuditSpec]
FOR SERVER AUDIT [OurAudit]
ADD (DELETE ON OBJECT::[dbo].[SensitiveData] BY [public]),
ADD (INSERT ON OBJECT::[dbo].[SensitiveData] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[SensitiveData] BY [public])
WITH (STATE = ON)
GO

How do I go about modifying this audit specification to capture everything EXCEPT what is being done by the application service account?

Am I limited to specifying each principle individually or can I specify an exception?  Reading through BOL it seems I can only list principles to audit - not principles to ignore.


texts: Can a weak entity have relationship with multiple strong entities?

texts:
I ran into a situation where a weak entity happens to have attributes which are primary keys in two different entities.

My situation is like:

Strong entities:

--Course
-Course_Num(P.K)
-Title
-Credits

--Semester
-Semester_id(P.K)
-StartDate
-EndDate

Now a weak entity CoursesOffered has attributes as follows

--CoursesOffered
  -Course_Num
  -Semester_id
  -Instructor_Name

So my question is, can this weak entity have identifying relationship with above two strong entities, since the courses offered will be depending on the rows of two strong entities. Please help I am stuck if this would be correct.


texts: Are these two ERDs the same? Do they represent the same thing? [Weak entities and multi-valued attributes]

texts:
What I want to create is a table in a database for employees in a company.
Each employee has a unique identifier ID and a name. Each employee also has a list of skills, the skills are represented as a string (i.e. "Cashier", "Manager", etc.).
Multiple employees can have the same skill, for example, John is a Cashier and a Manager and Bob is also a Cashier.
I thought of two ways to represent this using an ERD:

Using a multi valued attribute connected to the Employee entity:

Using a weak entity with the following cardinalities:

What I'm asking is, are these two implementations the same, cardinality and all?


texts:
I experiencing issues while vacuuming a DB table:

database=# vacuum (verbose) table_name;
INFO:  vacuuming "public.table_name"
ERROR:  uncommitted xmin 415285800 from before xid cutoff 462314292
needs to be frozen

Can someone explain what does this error mean?

Is there any solution for this problem avoiding a vacuum using "zero_damaged_pages" and "ignore_system_index" settings?

Thank you for any replies.


texts:
I have:

SQL Server 2008R2 database server in my LAN and under my control (call it LOCALSQL)
remote SQL Server, on which I have much less control (call it REMOTESQL), for which I only have IP, username, password
Linux-Apache-PHP server, on my LAN, used to access LOCALSQL (call it PHPSERVER)

Up to now, PHPSERVER accesses LOCALSQL; I can only access REMOTESQL from a test Windows machine with SQL Server Management Studio, with the necessary firewall permission.

Now, I would like to use sp_addlinkedserver to have PHPSERVER execute query involving tables from both servers.
What should I do for the firewall? Should I allow connections:

a) between LOCALSQL and REMOTESQL

b) between PHPSERVER and REMOTESQL

c) both of the above


texts: Replication consistently fails to bring new rows to subscriber

texts:
Short description of the problem:

Initial synchronisation to the subscriber works fine.
Subsequent synchronisations fail to download new rows.
Re-initialising the subscription "fixes" the issue and the subscriber gets the new rows.
GOTO step 2

More details:

We've set up a new merge replication, on SQL Server 2008 R2 (on both the publisher and subscriber). Most tables use a filter for which rows they're going to replicate, similar to this example:

SELECT <published_columns> FROM [dbo].[Table1]
WHERE table1_id in (select table1_id from [dbo].[fn_GetTable1IDsForUser] (SUSER_SNAME()))

The fn_GetTable1IDsForUser function brings the relevant Table1 IDs for each subscriber based on pre-set criteria. Running the above query on the publisher (after replacing <published_columns> with *) returns the expected rows correctly.

Each synchronisation that is not done immediately after a (re)initialisation of the subscription seems to have succeeded, as it produces no error message, however no new rows are downloaded to the subscriber. Reinitialising the subscription allows the new rows to be downloaded, but only for the first synchronisation.

If I try to validate the subscription (in Management Studio; it only seems to work if I do it for all subscriptions, trying to do it for one has no effect), the next synchronisation produces the following error message:

  Data validation failed for one or more articles. When troubleshooting, check the output log files for any errors that may be preventing data from being synchronized properly. Note that when error compensation or delete tracking functionalities are disabled for an article, non-convergence can occur.

I have followed the advice in this article: http://technet.microsoft.com/en-us/library/ms152532%28v=sql.105%29.aspx
However none of the suggested solutions do anything.

  The table is filtered, and there are no changes to deliver to a given Subscriber.

This basically suggests to test the filtering query, which is working correctly, as I've described above.

  One or more agents are not running or are failing with an error.

There are no errors, except when I ask for the subscription to be validated. There are no conflicts either.

  A transactional subscription was initialized without a snapshot, and changes have occurred on the Publisher since the publication was created.
  Replication of stored procedure execution for a transactional publication produces different results at the Subscriber.
  The INSERT stored procedure used by a transactional article includes a condition that is not met.

This is a merge replication, not transactional.

  Data is being deleted by a user, a replication script, or another application.

Nothing acts on the data immediately after synchronisation.

  Data is being deleted by a trigger, or a trigger includes a ROLLBACK statement.

There are no triggers except for the ones set by replication itself (MSMerge_*).

In the end we've not been able to figure out why this happens. We'd welcome any suggestions.


texts:
How do we purge events older than a day in Extended Events in SQL Server 2012. Stopping and Starting the session is not helping.
Target : asynchronous_file_target
Trying to create a job which will perform this operation daily.


texts: Migrate SQL Server backup to MySQL for a Wordpress site

texts:
I have a client with an old .Net site that used SQL Server. For lots of reasons they decided to switch to Wordpress. They had a bunch of news articles on that old site that they want turned into posts or custom post types on their new Wordpress site (the standard post is probably easier?)

I reached out to their hosting company and got a .bak file of their database and through online research it seems that MySQL workbench is the best route to go as far as getting this file into a MySQL DB. This is where I'm getting stuck. I don't deal with databases a ton besides backing up Wordpress MySQL databases to move them during local to production development or changing url structures.

Do I need to upload this file to a server somewhere like my personal production site's server to make this migration happen?

My plan was copy the Wordpress site over to my localhost and migrate the SQL Server backup file to my local MAMP server somehow as a test to see if it works correctly. I see this question was sort've asked before but there wasn't a ton of dialogue on that so was hoping anyone with new ideas could help me out since I gave a little more context on my goal. I'm on a mac by the way.

There are 1745 articles with categories and subcategories that won't match the current configuration in the Wordpress database. I asked my server side engineer here at work and he said a possible option is to export the database to CSV and use a CSV to Wordpress plugin to import it. Does this sound like a possibly easier solution?


texts: Under what circumstances would these 3 queries return different number of results?

texts:
Hello I have the following question:
Query 1:

SELECT b.id,
    (
    SELECT SUM(cb.invoice_amount) AS sum
    FROM payment_lines cb
    WHERE cb.batch_id = b.id
    ) AS batch_total_invoice_amount
FROM batches b;

Query 2:

SELECT b.id, SUM(cb.invoice_amount)
FROM batches b
 LEFT JOIN payment_lines cb ON b.id = cb.batch_id
GROUP BY b.id
ORDR BY b.id desc;

Query 3:

SELECT batch_id, sum(invoice_amount)
FROM payment_lines
GROUP BY batch_id
ORDER BY batch_id desc;


texts: Error during sp_refreshsqlmodule after altering table type

texts:
I am trying to add a column to a user-defined table type.
I have done some research and here is what I have now:

  -- First, rename existing table type to something else
EXEC sp_rename 'TT_MY_TABLE_TYPE', 'TT_MY_TABLE_TYPE_1'

-- Create the new table type
CREATE TYPE [dbo].[TT_MY_TABLE_TYPE] AS TABLE(
    [MY_FIELD] [varchar](20) NULL
)
GO

-- Do a refresh of the SP/views so that the SP/views will refer to the new table type
-- Save the list of dependencies to a temporary table
SELECT  'sp_refreshsqlmodule ' + quotename('MySchemaName.' +object_name(referencing_id), '''') AS SQL_CMD
INTO    #TEMPSQL
FROM    sys.sql_expression_dependencies
WHERE   referenced_class_desc = 'TYPE' and referenced_entity_name =  'TT_MY_TABLE_TYPE';

DECLARE @sql NVARCHAR(1000)
-- Do a loop for the list of dependencies, use dynamic SQL to execute the SQL commands
DECLARE c_Cur CURSOR FOR
    SELECT  SQL_CMD
    FROM    #TEMPSQL

OPEN c_Cur
FETCH NEXT FROM c_Cur INTO @sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC SP_EXECUTESQL @statement = @sql
    FETCH NEXT FROM c_Cur INTO @sql
END

CLOSE c_Cur
DEALLOCATE c_Cur

-- Drop the old table type
DROP TYPE TT_MY_TABLE_TYPE_1

DROP TABLE #TEMPSQL

Unfortunately, while refreshing stored/procedures (their dependencies) to reflect the new type I am getting the following error:

Procedure sp_refreshsqlmodule_internal, Line 85 [Batch Start Line 0]
Operand type clash: TT_MY_TABLE_TYPE is incompatible with TT_MY_TABLE_TYPE_1.

I expect that the error is something else since all I did was adding one column to the new type.

Could you help me identify, what is the root cause of this problem?


texts: When installing a service pack on one instance will this hinder the running of the other instances on a standalone SQL Server?

texts:
I have a SQL Server 2016 with an instance A and an instance B. Both have SP1 installed, no reporting server.

Can I assume that, when I install SP2 on instance B only, that, while installing, the running of instance A will not be hindered ? (no restarting of the SQL Server service)

Since sqlbrowser is a shared feature, it will also be updated, will this hinder any remote access to instance A (fix port) for the time of the install?


texts: After ID is updated, get a value from another table

texts:
I have a roads table:

+---------+---------+
| ROAD_ID | MEASURE |
+---------+---------+
|       1 |    1000 |
|       2 |     100 |
|       3 |     500 |
|       4 |    9000 |
+---------+---------+

And I have a related linear referencing table called projects:

+------------+---------+--------------+------------+
| PROJECT_ID | ROAD_ID | FROM_MEASURE | TO_MEASURE |
+------------+---------+--------------+------------+
|        101 |       1 |            0 |         50 |
|        102 |       1 |          100 |        500 |
+------------+---------+--------------+------------+
|        103 |       2 |            0 |        100 |
+------------+---------+--------------+------------+
|        104 |       3 |          100 |        200 |
|        105 |       3 |          400 |        500 |
+------------+---------+--------------+------------+

Users manually enter projects into application's attribute editing environment (more here).

The workflow for entering new projects is:

The user creates a blank new record.
The FROM_MEASURE has a default value of 0.
The user enters the ROAD_ID manually.
The user enters the TO_MEASURE manually.

You can't tell from the sample table, but 9 times out of 10, projects pertain to an entire road (not just a portion of the road). An example is PROJECT #103.

Rather than make the user manually enter the TO_MEASURE when imputing new projects, would it be possible to get these values automatically once the ROAD_ID is entered?

Example:

The user creates a blank new record.
The FROM_MEASURE has a default value of 0.

+------------+---------+--------------+------------+
| PROJECT_ID | ROAD_ID | FROM_MEASURE | TO_MEASURE |
+------------+---------+--------------+------------+
|        106 |         |            0 |            |
+------------+---------+--------------+------------+

The user enters the ROAD_ID manually.
The TO_MEASURE is automatically retrieved:

+------------+---------+--------------+------------+
| PROJECT_ID | ROAD_ID | FROM_MEASURE | TO_MEASURE |
+------------+---------+--------------+------------+
|        106 |       4 |            0 |      9000  | <-- Magic!
+------------+---------+--------------+------------+

After ROAD_ID is updated, automatically get the related road's MEASURE value as the TO_MEASURE.


texts:
Here is the code for my stored procedure, lots of things going on and lots of parameters being passed. I'm knew to sql and I'm sure there's a far better way of performing these functions but need some help! Thanks in advance! :)

ALTER PROCEDURE [dbo].[spCompleteRepair]

@unit_id INT,
@BasicHB NVARCHAR,
@AdvHB NVARCHAR,
@CCRepair BIT,
@CCReplace BIT,
@Fan NVARCHAR,
@RE BIT,
@notes NVARCHAR,
@bin_num NVARCHAR,
@loc_num NVARCHAR,
@repairserial NVARCHAR

AS

BEGIN

INSERT INTO RepairDetails
    (unit_id, BasicHB, AdvHB, CCRepair, CCReplace, Fan, RE, notes, completion_date)
VALUES
    (@unit_id, @BasicHB, @AdvHB, @CCRepair, @CCReplace, @Fan, @RE, @notes, GETDATE())

UPDATE Units
SET bin_num=@bin_num, loc_num=@loc_num, repair_serial=@repairserial, unit_status=1
WHERE unit_id=@unit_id

UPDATE OutBins
SET filled=1
WHERE bin_num=@bin_num AND loc_num=@loc_num

UPDATE Orders
SET Orders.order_status = 1
FROM Orders
--check to see if there are units for the order
WHERE EXISTS (
        SELECT *
        FROM Units
        WHERE Units.order_id = Orders.order_id
        )
--make sure there are NO units that are not status = 1
    AND NOT EXISTS (
        SELECT *
        FROM Units
        WHERE Units.order_id = Orders.order_id
            AND Units.unit_status <> 1)

END

CODE TO CALL PROCEDURE:

 using (SqlCommand command = new SqlCommand("spCompleteRepair", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@order_id", txtorder_id.Text);
                    command.Parameters.AddWithValue("@unit_id", txtunit_id.Text);
                    command.Parameters.AddWithValue("@BasicHB", ddlBasicHB.Text);
                    command.Parameters.AddWithValue("@AdvHB", ddlAdvHB.Text);
                    command.Parameters.AddWithValue("@CCRepair", ddlCCRepair.Text);
                    command.Parameters.AddWithValue("@CCReplace", ddlCCReplace.Text);
                    command.Parameters.AddWithValue("@Fan", ddlFan.Text);
                    command.Parameters.AddWithValue("@RE", ddlRE.Text);
                    command.Parameters.AddWithValue("@notes", txtNotes.Text);
                    command.Parameters.AddWithValue("@repairserial", txtRepairSerial.Text);
                    command.Parameters.AddWithValue("@bin_num", ddl_binnum.Text);
                    command.Parameters.AddWithValue("@loc_num", ddl_locnum.Text);
                    command.Connection.Open();
                    command.ExecuteNonQuery();
                }


texts: query doesnt use column dedicated index after a certain amount of rows

texts:
I have the following table:

create schema test;
CREATE TABLE test.foo (
    info_datetime timestamp NOT NULL,
    asset_id int4 NOT NULL,
    price float8 NULL,
    CONSTRAINT minute_prices_pkey PRIMARY KEY (info_datetime, asset_id)
);
CREATE INDEX foo_asset_id_idx ON test.foo USING btree (asset_id);

when i run this with a small amount of rows the query uses the dedicated index:

explain
select *
from test.foo mp
where mp.asset_id = 1

Bitmap Heap Scan on foo mp  (cost=4.21..14.37 rows=8 width=20) (actual time=0.005..0.006 rows=0 loops=1)
  Recheck Cond: (asset_id = 1)
  Buffers: shared hit=1
  ->  Bitmap Index Scan on foo_asset_id_idx  (cost=0.00..4.21 rows=8 width=0) (actual time=0.003..0.003 rows=0 loops=1)
        Index Cond: (asset_id = 1)
        Buffers: shared hit=1
Planning Time: 0.077 ms
Execution Time: 0.026 ms

however when I insert about 3000 rows, the same query doesn't use the index

Seq Scan on foo mp  (cost=0.00..42.56 rows=1332 width=20) (actual time=0.012..0.435 rows=1332 loops=1)
  Filter: (asset_id = 1)
  Rows Removed by Filter: 873
  Buffers: shared hit=15
Planning Time: 0.074 ms
Execution Time: 0.707 ms


texts: How to check if a combination of columns exists in another table in SQL Server?

texts:
This is how I would express my query in MySQL or Oracle:

SELECT t1.c1, t2.c1, t1.c2, t2.c2, ...
FROM t1, t2
WHERE (t1.c1, t2.c1) NOT IN (SELECT c1, c2 FROM t3)

How can I express the same query in SQL Server?

I figured I could concatenate them as strings but it cannot be the best way to do it.

I could not find a way to do it nicely in SQL Server.

I cannot join them once more because each table contains millions of records.

Example tables so that it is easier to imagine the problem:

t1:

c1 c2 c3
--------
1  x  A
2  y  B
3  z  C

t2:

c1 c2 c3
--------
1  m  D
2  n  E
3  t  F
4  v  G

t3:

c1 c2 c4
--------
1  2  aa
1  4  bb
2  3  cc


texts: Encountering "Snapshot too old" error during execution of expdp command

texts:
As am facing an issue while performing expdp command in my production db.(Oracle 11g in windows enviornment)

  cmd> expdp 'sys/123@PROD as sysdba' DUMPFILE=BACKUP_02082017_BACKUP.dmp LOGFILE=BakupLog_02082017_BACKUP.log SCHEMAS=A1,B2,C3,D4.. exclude=statistics consistent=y

It was taking more than 1 day to export the database sized 7GB. But my issue is that the exporting have error and shows error message

  ORA-31693: Table data object "owner"."PASSWORD_HISTORY" failed to load/unload and is being skipped due to error:
  ORA-02354: error in exporting/importing data
  ORA-01555: snapshot too old: rollback segment number 19 with name "_SYSSMU19_255734752$" too small

As i set my retention policy to 16500 from default 900. Even though, same error was occurring.

As i am planning to increase the retention policy up to 10 hrs ie, 36000. Is it viable? I am confused that do my undo table space is capable for this or not?

Providing some more details:

>  show parameter undo_%;

 NAME                                             TYPE        VALUE
-------------------------------------------------- ----------- --------
undo_management                                   string     AUTO
undo_retention                                    integer    16500
undo_tablespace                                   string     UNDOTBS1

>  select file_name,tablespace_name,trunc(bytes/1024/1024) mb, trunc(maxbytes/1024/1024) mm
        FROM dba_data_files
        where tablespace_name = 'UNDOTBS1';

FILE_NAME                                  TABLESPACE_NAME    MB MM
--------------------------------------------------------------------
C:\APP\ADMIN\ORADATA\PROD\UNDOTBS01.DBF   UNDOTBS1          5630 32767

>Size of undo with current undo_retention :
     Actual Undo size[MBytes]:5630
     UNDO retention[Sec]:16500
     Needed Undo Size[MBytes]:909.433359

I am stuck with this issue. Anyone please advice how i deal with this error?

Thanks in advance.


texts: MySQL Sharding tables/servers or other methods to reduce table sizes?

texts:
I'm currently working with a table exceeding 12 million rows (at about 3 GB when exported with mysqldump) and am curious how huge a table can realistically get without any serious performance impact. The table is growing about 100,000-200,000 rows a day or so.

Should I be starting to think a little about sharding this data across multiple tables or mysql instances now before the data gets much larger? Currently the server this is running on has 1 GB of ram (though it'll soon be moving to a machine with 3/4 GB).

Has anyone got any tips/recommended reading that would push me in the right direction, or is this something I don't need to worry about yet?

Thanks :)


texts: Error while installing Oracle 11g in Ubuntu 18.04

texts:
Also getting a runfixup.sh script which is also giving error

root@szh-server:/tmp/CVU_11.2.0.1.0_zuman# ./runfixup.sh
Response file being used is :./fixup.response
Enable file being used is :./fixup.enable
Log file location: ./orarun.log
./orarun.sh: 186: [: true: unexpected operator
./orarun.sh: 848: [: unexpected operator
./orarun.sh: 864: [: unexpected operator
./orarun.sh: 882: [: unexpected operator
./orarun.sh: 903: [: true: unexpected operator
./orarun.sh: 1052: [: unexpected operator
./orarun.sh: 1057: [: unexpected operator
./orarun.sh: 1075: [: unexpected operator
./orarun.sh: 1085: [: unexpected operator
./orarun.sh: 1115: [: unexpected operator
./orarun.sh: 1143: [: unexpected operator
./orarun.sh: 1189: [: unexpected operator
./orarun.sh: 139: [: unexpected operator
./orarun.sh: 139: [: unexpected operator
./orarun.sh: 1228: [: unexpected operator
./orarun.sh: 1284: [: unexpected operator
./orarun.sh: 1342: [: unexpected operator
./orarun.sh: 1426: [: unexpected operator
./orarun.sh: 1451: [: unexpected operator

I'm getting the error from the script which is supposed to fix the installation errors. -_-

Any solution for this?


texts: Mysql - optimal indexing for query using IN operator

texts:
I have this query:

SELECT *
FROM table1
WHERE foo IN (1,2,3,4) AND bar = 123;

I have 3 indices:

ALTER TABLE table1 ADD INDEX IDX_foo (foo);

ALTER TABLE table1 ADD INDEX IDX_bar (bar);

ALTER TABLE table1 ADD INDEX IDX_foo_bar (foo, bar);

When I try EXPLAIN with the query, IDX_bar is chosen which I don't understand why. So my questions are:

Why is IDX_bar chosen but not IDX_foo or IDX_foo_bar?
Does MySQL choose index differently depend on the data at the time?
Should I only have IDX_bar and remove the other two?


texts: No SA password. No SQL Server Management Studio. No OS authentication

texts:
I have this problem.

I need to do some administrative tasks on a MS SQL Database using SA account.

OS authentication is not set.
SQL Server Manager Manager Studio is not installed.
I have no other admin database account.
I do have access to a Windows admin account
The only tools installed on the database server are as shown:

How can I activate OS authentication so I can log into the database an reset the SA password ?


texts: Clustered Index Update on Nonclustered Columns

texts:
Using: SQL Server 2008 R2

I am currently stepping through a query execution plan, and have come across an instance of a clustered index update on a table.  The issue here is that the columns that are being updated are NOT part of the clustered index.

Table:

<table>
    id INT IDENTITY(1,1) -- Clustered Index
    , name VARCHAR(20) -- Nonclustered Index
    , status CHAR(1)
    , quantity INT
    , price FLOAT

Update statement:

UPDATE a
SET a.status = @status
    , a.quantity = @quantity
    , a.price = @price
FROM <table> a
WHERE a.name = @name

The execution plan shows an Eager Spool at 36% cost, a Clustered Index Update at 55% cost, and an Index Seek on the name index at 9%, among Compute Scalar and Top items at 0% cost.

Why is the plan showing a clustered index update?  What could I do to prevent this, and prevent the eager spool?


texts: Extracting string after and before a character '-' or '<' using Sql Query

texts:
I have table with one column 'UDF_WorkLocation_6' which contain string.Please Review following table.
UDF_WorkLocation_6
MUMBAI - Maharastra<6>
AHMEDABAD - Gujarat<1>
MUMBAI - Maharastra<6>
CHANDIGARH - Chandigarh<34>
NULL
DELHI - Delhi<42>
NULL
INDORE - Madhya Pradesh<56>

Here it is column name 'UDF_WorkLocation_6' which contain string in table such as 'INDORE - Madhya Pradesh<56>'.
I want split string from '-' then show output into two columns,In First Column getting string before '-' like 'INDORE' from 'INDORE - Madhya Pradesh<56>' string & second column getting string  after '-' and also remove charter after '<'this like Madhya Pradesh from 'INDORE - Madhya Pradesh<56>'.
So output in table like,
**Output Table**

 Column1    Column2
 MUMBAI      Maharastra
 AHMEDABAD   Gujarat
 MUMBAI      Maharastra
 CHANDIGARH  Chandigarh
 NULL        NULL
 DELHI       Delhi
 NULL        NULL

I have tried to prepared query for split string.
For instant, string is 'MUMBAI - Maharastra<6> ' and got output for second column like 'Maharastra<6>' using below sql query
SELECT LTRIM(SUBSTRING(AccountMasterUDF.UDF_WorkLocation_6, CHARINDEX('-',
        AccountMasterUDF.UDF_WorkLocation_6) + 2, 100))

But i also want two remove charters after '<' this and query for getting string 'MUMBAI' in first column.
Can anyone please help me to prepared query without split function?


texts: NoSQL- automatically cache and update references to other documents as embedded

texts:
To avoid having to look up referenced documents, one practice is to embed them (e.g. https://docs.mongodb.com/manual/tutorial/model-embedded-one-to-many-relationships-between-documents/).

Is there a way to have those embedded documents actually be separate and be referenced yet be "automatically embedded" like by some sort of functionality which automatically stores their data in the document which is referencing them - almost like an automatic "embedding cache" of sorts.

I'm not referring to them being automatically retrieved by reference, but an actual copy being stored and updated automatically.

I know this could be built manually, but I'm wondering what may exist already which can do this, preferably something inherent to MongoDB, but it doesn't have to be.

While I'm asking this about MongoDB in particular, if someone knows of something like this for other NoSQL databases, I'd be interested to hear about that as well as an answer.


texts: Data center from scratch which way to go

texts:
My budget for a data center (hardware,OS licences, SQL Licences WithOut CALS ) is around $50K~$60K.

I have needs for around 200 users and my apps works only works in Microsoft environment (Window Server and SQL Server).

I've talked to a lot of "so called" experts and every one had different vision for my data center, as much I talking with vendors I am more puzzled how I should design my data center, perhaps this is because I did not meet yet any SQL Server expert who knows what to do.

My living area is so poor whit IT Specialist.

My app is not mission critical and We can afford down time up to 1 working day (8 hours).
I now have 1 main database (80GB-DATA and 20GB of SQL Server log) for writing transaction
That database is transactional replicated by SQL Server to second physical SQL Server web servers from there reading large reports.

I have around 200 users which over WEB server communicating with my SQLs

Here is what I considered so far to do.

First scenario

3X Physical servers (no storage, no SAN, no Virtualization) one server will act as Web Server other two is going to be SQL server whit 2012 standard edition each server will have 96GB ram and Intel Xeon 6C Processor 2Hz. In this scenario I can afford  IBM enterprise SSD discs I can have at TWO SQL servers 8 X 250 GB ssd SATA disc. First server will replicate database to other.
Second scenario

2X physical servers one IBM or HP storage with 10~14 pieces of 600GB 10K SAS disc in storage. All of that virtualized by vmware and running on one SQL Standard Server. without SQL replication ,but whit 200GB of ram in SQL-os.

Personally I do not have experience with virtualization and storage systems. I more like SQL Server replication.

Here is some questions which I seek to have direction which way to go.

Should I avoid virtualization for machines which is dedicated to running SQL Server ?

Is SQL Server 2012 ready for production? I know that this is perhaps silly question but one of IT guys with plenty of certificates say that they always running production instances on second to last Microsoft platform, I am really confused how is He strong in claiming that they not going to instal and configure MS Servers (OS, SQL Server) on 2012 generation.

Do I need SSDs beside plenty of RAM. Unfortunately I can't do much in redesigns queries!

Here is what I have captured during 3 hour monday peek time.

Reporting Database (replication subscriber)

Replication

This is transactional DB (replication publisher)
Trasaction DB


texts:
I have several Oracle servers that exhibit a behavior I do not understand.  I have identified a two node RAC in which one node exhibits the behavior, but the other node does not.  RAC is not involved in the issue, but it dramatically reduces the differences between the servers experiencing the different behavior.

Why is the following output different?
Node 2 (expected behavior):

SQL> select 'a' from dual;

'
-
a

SQL>  select 'abc' from dual;

'AB
---
abc

Node 1 (unexpected behavior):

SQL> select 'a' from dual;

'A'
---
a

SQL>  select 'abc' from dual;

'ABC'
---------
abc

These nodes are connecting to different instances of the same database.  I have done a show all on both and verified that the sqlplus settings are identical.


texts:
I have a question regarding MySQL locking and transaction management.
 My Question is ..does mysql takes lock on the the tuple/table on which I am doing select/update in a transaction ?


texts: Why index REBUILD does not reduce index fragmentation?

texts: I used ALTER INDEX REBUILD to remove index fragmentation. In some cases, REBUILD does not seem to remove this fragmentation. What are the reasons why REBUILD does not remove fragmentation?


texts: How to get number of locks acquired by my MySQL Query?

texts:
How to get number of locks acquired by my MySQL update query?

Is it possible using MySQL Command Prompt or MySQL Workbench?

Example query : update employees set store_id = 0 where store_id = 1;


texts: Is it somehow possible to predict / foresee when SQL Server will trigger auto-growth?

texts:
I found numerous questions / answers about SQL-Server auto-growth - almost all indicating that one should try to avoid auto-growth if possible.

However auto-growth is available with MS SQL Server and sometimes required.

Is there any way how one could predict when SQL Server will trigger it's next auto-growth event or is this calculation completely internal to SQL Server?

(E.g. is there some kind of "fill-level" in settings which SQL Server compares to the current database usage and then triggers auto-growth when this fill-level will be reached?)

BR
Mark


texts: How to link other tables (entities) to parent and child categories?

texts:
I am not a developer by any means, but I have a fairly good understanding of how relational databases work and the various types. I am trying to map a database design diagram (using Visio) and need to understand how I would represent the correct relationships in the correct way for a table which needs to reference Parent/Child categories.

As an example: A member/profile table linking to a product parent category table (reference table containing 3 different product types) which also has 2 layers of sub categories for each parent as a tree like structure that drills down into more specific products. I understand the Parent/Child relationships but would I need to link the member/profile table to the Parent Category table (which inherently links to the child categories), or would I need to link the member/profile table to all three tables individually (parent and two child tables).

What I am trying to represent within the diagram, to ensure this is correct is that this database will be used to drive a front end website and when a user selects a Parent Category type from a drop down list, depending on the category chosen, it would then present the appropriate drop down lists for the associated child categories, allowing the specific Product Types and Child categories products to be associated with the member.

I hope this makes sense and I have explained this in a way that someone will understand. I just need to know how to represent this correctly within Visio.


texts: Why are SELECT queries in sleeping status holding open transactions

texts:
Using sp_WhoIsActive, I see that I have a lot of processes in a "sleeping" status, several for hours. I am not sure of the cause and am still troubleshooting (timeout? bad code with uncommitted transaction?).

Using the query below, I noticed that all of these open transactions are SELECT queries in a "sleeping" status. Why would a SELECT open a transaction? Could these be in the middle of a stored procedure somewhere, and the [TEXT] field is only showing the last statement in the procedure that was executed? There is no wait_info on any of the open transactions.

 SELECT [TEXT] as SQLcode
 FROM SYS.SYSPROCESSES SP
 CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.[SQL_HANDLE])AS DEST WHERE OPEN_TRAN=1


texts: Check the time that PostgreSQL is taking to automatically create existing indexes when you do bulk insert using copy command from Spark

texts:
I am doing a bulk insert from the spark to the postgres table. Amount of data that I am ingesting is huge. The number of records is around 120-130 million
I am first saving the records as multiple csv files on distributed storage location i.e. S3 bucket in my use case. Now I am using multiple copy command to copy the data in the PostgreSQL table. The actual PostgreSQL table has four indexes on it.
The copy command takes around 8 hours to save the data. I created a similar table without indexes and the data got saved in around 28-30 minutes. Based on searches that I have done on internet on multiple sites, they mentioned that indexes can slow down the performance and that is definitely seen based on the time difference that I have specified above.
Now the actual question is how I can identify which index creation is taking more time. Is there any utility,  query or command that shows the time taken to create the indexes on the table when we are doing bulk inserts.
I am using the below query to see the number of multiple copy commands are running on the PostgreSQL instance:
SELECT * FROM pg_stat_activity
where usename = 'xyz' and application_name ='PostgreSQL JDBC Driver'

Is there something like this query or any tool or command that I can use to see amount of time t is taking to create the indexes. Also how much time is taken by each index?
Any idea, guidance or suggestion are welcome. I am not that familiar with PostgresQL.


texts: Unable to delete SQL Server 2008 R2 login

texts:
I had created a SQL Server login some days back. Now I need to delete the same. So I went into SQL Server Management Studio => Security => Logins and selected the login that I want to delete. It displays an informational message which says

  Deleting server logins does not delete the database users associated
  with the logins. To complete the process, delete the users in each
  database. It may be necessary to first transfer the ownership of
  schemas to new users.

Ignoring this message, when I confirm the delete process I am getting a Fatal Error which says

  Drop failed for Login 'iis_login'.  (Microsoft.SqlServer.Smo)

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  This login is the owner of 1 job(s). You must delete or reassign these
  jobs before the login can be dropped. (Microsoft SQL Server, Error:
  15170)

How do I delete the login when there are jobs (according to the message above) owned by the login.

regards,
Nirvan.


texts: How to index a composite primary key with non-sequential inserts?

texts:
I have a composite primary key, comprising of a datetime, a location id and a 2 character code. This is the key that uniquely identifies each row of my table.

I know that the usage of this table will insert a week of data from a year ago, then a week of data from this year. Therefore, it's likely the table will get fragmented, as the primary key jumps back and forth.

I would like to know what sort of indexing strategies can be employed in this scenario. My gut feeling is that the default clustered index is a bad idea and I should replace that with a non-clustered index, which would make rebuilds easier.


texts:
Currently running two VMs with fresh installs of Ubuntu 14.04 x64 and Mongo 3 Stable. Each VM has the same specs with 8 cores and 56GBs of RAM. Our processing VM is doing great under heavy loads, however our production VM seems to have Mongo crashing once every few hours and can't pinpoint why or how.

My config for the production Mongo instance looks like this

Here is the last three logs outputted. The first two links are cut near the bottom as they were too big, and the last is a full log top to bottom (as it was a short lived run).

https://gist.github.com/dustinblackman/e32778344ec5f21af0d8
https://gist.github.com/dustinblackman/acb0a3aef77114275de4
https://gist.github.com/dustinblackman/908d63e7bd386aae6bc7

The applications that are communicating with this instance are using the latest PHP, Python, and NodeJS drivers.

Does anything in the logs stand out to anyone? All that I see is that the only proper operation that happens before a crash is auth (and I've tried disabling auth, but no deal). Any help is appreciated. Thanks!


texts:
In my db the entities are:

designed_object: a generic designed object
designed_object_param: a parameter related to the designed object with the type (param_type), the default value (param_value), the maximum and minimum value (param_max/min_value)
real_object: an instance of the designed object
real_object_param: the actual parameters of the instance (param_value)

They are related as follow (foreign key):

designed_object - real_object: 1..n
designed_object - designed_object_param: 1..n
real_object - real_object_param: 1..n
designed_object_param - real_object_param: 1..1

The problem is that there are two paths from real_object_param to designed_object:

real_object_param -> real_object -> designed_object
real_object_param -> designed_object_param -> designed_object

With this design, it is possible that, starting from the same real_object_param, the two paths point to two different rows if, for some bug, the real_object_param refers a wrong designed_object_param.
I know that this can be avoided through a trigger.
Is there any better design?

Thanks.


texts: How to use BCP Utility queryout in a network path with crededential (login required) for SQL Server

texts:
I'm having a hard time figuring out on how can I export my selected table in a network path that requires login credentials.
'bcp "select ''StudentName'' UNION ALL SELECT StudentName AS StudentName from [School].[dbo].[ClassRoom]" "queryout \\IPAddress\d$\Log\StudentLog_'+@fileTimeStamp+'_01'+'.'+@fileExtension+'" -c -t, -T -S' + @@servername

Running this gives me an error of

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to open BCP host data-file
NULL

Where should i put in the script the authentication for my network path?


texts: Why can't I view the SQL Server database created by a web application?

texts:
I have an ASP.NET MVC web application deployed to IIS on a Windows Server 2012 machine.  When the application first starts, it will create the database and tables it needs if necessary through Entity Framework.  This all works fine, and now I want to do some administration to the database through SQL Server Management Studio.  However, when I open up that tool I only see the System Databases.  If I remove "User Instance=true" from the connection string and let the application create the database again, I can the see the database.

Why couldn't I view the database created by the web application when User Instance=true was in the connection string?  If I am viewing the server instance through Management Studio with an account that has all roles, I would expect it to be able to see any and all databases.

Some details that may help:

The application uses the following connection string in Web.config:

<add name="DefaultConnection" connectionString="Data Source=.\SqlExpress;
  Initial Catalog=Foo.Dashboard;  Integrated Security=SSPI;User Instance=true"
  providerName="System.Data.SqlClient" />

In IIS, the application pool is set to run with the identity LocalService and the Load User Profile setting is True.  On SQL Server Express, we added a login for the Local Service account and gave it the dbcreator role.  When the application started up, it was then able to create the database and tables, and everything appears to be functioning.

I then connect to the server with remote desktop and open SQL Server Management Studio. To connect to the database, I enter .\SQLEXPRESS as the server name and authenticate with Windows Authentication. I then connect and only see the system databases. I’ve added all the possible database roles to my login, and I see that I have the VIEW ANY DATABASE permission in the effective tab.


texts:
On my Ubuntu 20 server, I installed PostgreSQL 13 using the apt manager. I read some articles on performance tuning of PostgreSQL [1] and thought of increasing the WAL segment size (from default of 16MB). I see the following instruction, however, I don't know where to run this.
initdb -D ./data --wal-segment=1024
I guess the documentation refers to installing from PostgreSQL source code, which I don't intend to do. How do I go about change the WAL segment size?
[1] https://software.intel.com/content/dam/develop/external/us/en/documents/Open-Source-Database-Tuning-Guide-on-3rd-Generation-Intel-Xeon-Scalable-Processors.pdf

Following Daniel's answer, I did the following steps
$ pg_lsclusters
$ sudo pg_dropcluster --stop 13 main
$ sudo pg_createcluster 13 main -- --wal-segsize=256
$ sudo pg_ctlcluster 13 main start

You can verify the size of the WAL segments as
# du -hcs /var/lib/postgresql/13/main/pg_wal/*


texts: How to trunc double precision number without rounding?

texts:
I have '9.999999999'::double precision number and I want to convert it to decimal(8,2) without any rounding. The result I am expecting is 9.99. So far I tried:

select trunc('9.999'::double precision::numeric(8,3), 2)

and it works but I have to know number of precision digits for double precision value because if there will be more 9 then ::numeric(8,3) will automatically round it to 10.000.

What I can do about it?