texts: Multi-tenant database using SQL Server 2008?

texts:
I am designing a database using a shared schema containing the "security views" to which all logged tenants will be directed.

I read the msdn article on Multi-tenant Data Architecture. I understand the concept to a degree - unclear how to get the tenant view filter to work.

So, I have a tenant table containing a TenantID, and TenantName. I suspect i would need a mapping table to associate the tenantID with roles set up in SQL Server to which user domain logins will be mapped to?

I have the client authenticate using a DSN-less connection.

Please any guidance and examples on this "Mapping" table, and how this relates to the SUSER_SID() described in the article to make this all work will be greatly appreciated.

Would i need to create database / application roles identical to those indicated in the Mapping table??

Thanks


texts: Running BigQuery on BigTable vs loading from Google Storage

texts:
I have a medium size dataset (a few TBs), that is changing over time that requires BigQuery analysis.
The first attempt to do analysis was composed by:

dumping the DB into a JSON
moving it into a Google Storage bucket
loading the dataset with BigQuery Load
run the query! And it was blazing fast

The big caveat of this, is that once you loaded it, updating one single row, requires rereading (and billing) the entire dataset, which is not acceptable.
At this point I was wondering if, instead of dumping the data into a JSON, I could:

dump it into BigTable
have the BigTable as an external table from BigQuery
and still have all of the same benefits in terms of perf and cost of loading it within Google storage directly

At this point I could update single rows at 0 costs, and hopefully see the change propagated immediately into BigQuery
Are there limitations I should be aware for BigTable+BigQuery integration aside from these https://cloud.google.com/bigquery/external-data-sources?
How slower should I expect it to be? 2x? 1 order of magnitude?


texts:
We have a job, that contains SSIS packages. It is SQL Server 2005. A dba named xyz created those SSIS packages. It was running fine in the when the job kicks in. However, he left, and the company wanted to change all his ownership from all objects, such as databases, jobs, and maintenance plans ownership to sa.

As we did this, lots of errors came up. Every other job runs fine, only those jobs with SSIS package are failing.

Error is :

  Executed as user: Mycompany\sqlagentserviceAccount. Unable to open Step output file.  The step failed.

However, the SSIS package was created by the xyz DBA. For troubleshooting we did change the job ownership to xyz dba name, but it still failed.

Any help on how to change the SSIS packages back to his name, or any other tips, would be appreciated.


texts: I have three schemas Production, HRecourse and Sales,tables in every week create and drop , I have six Users P_User1, P_User2, HR_User1, HR_User2 and S_User1 and S_User2. I have to grant then how i can Select permission to each table in their respective Schemas. how i tackle this scenario with SQL Scripts.


texts: Procedure with "bind varibale" does not return the desired result set

texts:
I have a table with below structure:
create table CUST_MODERN_CHANNEL_BRANCH
(
  customer_Num      NUMBER,
  channel_Code  NUMBER(5)
)

Some example data :
   Customer_Num       Channel_Code
  =============     ==============
       1                 5
       1                 4
       1                 6
       2                 3
       3                 5

I wrote a procedure which you can see the code below :
create or replace procedure query_test(res          out sys_refcursor,
                                       order_by     number,
                                       customer_num number) is
  qry varchar2(1000) := ' select * from cust_modern_channel_branch ';
begin

  if customer_num is not null then
    qry := qry || ' where customer_num = :cust ';
  end if;

  if order_by is not null then
    qry := qry || ' order by :ord ';
  end if;

        --******* Generate Result

  if customer_num is not null then
    if order_by is not null then
      open res for qry
        using customer_num, order_by;
    else
      open res for qry
        using order_by;
    end if;
  end if;
end;

I'm using bind variable in my procedure to protect it from injection and make it better in terms of performance. The problem is that when I TEST my procedure with input parameters customer_num=1 / Order_by=2 the final result is not sorted on the second column and it is like this:
  Customer_Num       Channel_Code
  =============     ==============
       1                 5
       1                 4
       1                 6

But when I write a query with order by, it gives me the output below:
select *
from CUST_MODERN_CHANNEL_BRANCH t
where t.customer_num = 1
order by 2

  Customer_Num       Channel_Code
  =============     ==============
       1                 4
       1                 5
       1                 6

Is that something wrong with my procedure? Or the way I used bind variables?
Thanks in advance


texts:
I'm using MySQL 5.7 on Ubuntu 16.04.
I want to set up a mysql_config_editor login path.  The default file for this information is ~/.mylogin.cnf, but I would prefer to have it in the file ~/.config/mylogin.cnf.
The documentation for mysqli-config-editor says

To specify an alternate login path file name, set the MYSQL_TEST_LOGIN_FILE environment variable.

I tried doing this:
$ MYSQL_TEST_LOGIN_FILE=~/.config/mylogin.cnf

$ echo $MYSQL_TEST_LOGIN_FILE
/home/username/.config/mylogin.cnf

$ mysql_config_editor set --login-path=mysqldump --host=localhost --user=root --password
Enter password:

As you can see, I confirmed that the environment variable set correctly.  However, running the set command above created the file ~/.mylogin.cnf, which I did not want, and did not create the file ~/.config/mylogin.cnf, which I do want.
$ ls ~/.mylogin.cnf
/home/username/.mylogin.cnf

$ ls ~/.config/mylogin.cnf
ls: cannot access '/home/username/.config/mylogin.cnf': No such file or directory

How do I use the MYSQL_TEST_LOGIN_FILE environment variable to change the mysql_config_editor file?


texts:
How to not use the temp tablespace in oracle?

I have something like this:

set timing on;
create table export_table
tablespace tbspace1
noparallel
nomonitoring
as
select  /*+ full(cs) full(soi_aux) parallel(cs, 30) parallel(soi_aux, 30)*/
    distinct
    soi_aux.field1,
    soi_aux.field2
from
    export_table2 cs,
    big_table soi_aux
where
    cs.*** = soi_aux.***
    and soi_aux.****** = cs.***
    and soi_aux.***** = '*****'
    and soi_aux.****** = '*******';

However big_table has 450GB space, my Temp Tablespace has 140GB.

When I execute this, I get this error:

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Is there anyway to avoid write in Temp tablespace, or distribute it for others tablespaces?

Note: I can't change the tablespace size.

EDIT:

 - What is the query plan with and without the hints?

Later I can post the explain plan. Without hints, I go full for export_table2 and indexed big_table, although a single query with índex on big_table it takes always 2 or 3 seconds.

 - How big will the table that you create be?

~950k lines

 - Do you need the distinct?

Yes, I can go for order by+rownum=1 too.

 - How big is the data set before you distinct it?

Big table: 650 millions lines, export_table2 950k

- How selective are the predicates that you've obfuscated?

Both indexed (single)

 - How much of that 140 GB are other sessions using?

0


texts: SQL Union with results in Order of Union

texts:
I'm looking to write a Union between 2 tables where the 1st Union set of results appears before the second. (MSSQL 2014)

http://www.sqlfiddle.com/#!6/17c9e/74

I've noticed this question asked a few times but for whatever reason the accepted answers aren't working for me:

https://stackoverflow.com/questions/4076052/union-query-display-results-in-order-queries-are-written

https://stackoverflow.com/questions/13885261/order-result-sets-in-a-union

The 1st set of results returns:

userID   FirstName   LastName
-----------------------------
4        John        Snow
5        Joffrey     Baratheon
1        Tyrion      Lanister

And the 2nd results return:

userID   FirstName   LastName
-----------------------------
5        Joffrey     Baratheon
3        Daenerys    Targaryen
1        Tyrion      Lanister

Union gets rid of the duplicates which leaves userID 3 which should be appearing very last in my results. Final result:

userID   FirstName   LastName
-----------------------------
4        John        Snow
5        Joffrey     Baratheon
1        Tyrion      Lanister
3        Daenerys    Targaryen

The result I'm getting on SqlFiddle has userID -3 appearing second.

Any help appreciated.


texts:
I've got an auto generated join table (three columns, two of them keys to other tables), which was recently corrupted during an aborted migration. As a result there are around 1 million duplicate rows that need to be removed. I've been reading into how to best do this and I've run into conflicting advice. I've managed to get a list of the duplicates which need to be deleted using this query:

SELECT MIN(id)
FROM my_join_table
WHERE site_id=42    -- The migrations targeted a single site.
GROUP BY content_id
HAVING COUNT(*)>1;

However, when I tried to run a simple delete query with the above as an inner query, the command timed out after several hours.

DELETE FROM my_join_table
WHERE id IN ( <insert above query here> );

So my question is two part:

Would breaking that large (~1 Million ids) set up into smaller delete queries be more efficient?
If so, what's a good candidate for smaller set sizes? From what I can see online, no one suggests breaking up sets of fewer than 100 elements, but I'm hoping that I can get away with 1,000 or even 10,000.


texts: Why does PostgreSQL accept junk when parsing an INTERVAL

texts:
psql (11.8)
Type "help" for help.

public=# select '!@#$%^&*()00:00:00.01<>?<>:'::interval;
  interval
-------------
 00:00:00.01
(1 row)

Seems to take just about anything... I don't see anything in the docs about this...
It's kind of cool but also worrisome... can I rely on say '__00:00:00.01__' ALWAYS working?
And what are the REAL rules - the ones in the docs are not complete apparently...


texts: Just update one column, trigger updates another

texts:
I have logged the slowest queries on my database, and one surprises me, showing many times in the list, and taking often many seconds to execute.

UPDATE books SET last_read='2018-12-31 11:23:45' WHERE book_id='15'

book_id is int identity PK (clustered), last_read is a datetime. The query is written with the 15 in single quotes, thus requiring a conversion, but I cannot imagine this being a big deal, because the conversion would only be done once per query. There are 6 indexes on the table, but the column last_read is not involved or included in any of them. The PK is on book_id and is nothing special.
The table is very central in database, contains 50 columns and 400,000 rows.

The estimated execution plan tells me:

UPDATE: 0%
Clustered Index Update (on the PK constraint): 100%

The table has a trigger FOR DELETE, which I consider irrelevant here, and a trigger AFTER UPDATE, which reads:

CREATE TRIGGER books_last_update
ON books
AFTER UPDATE
AS
    UPDATE books
    SET last_update = GETDATE()
    WHERE book_id IN (SELECT book_id FROM Inserted)
GO

This shows me that the initial query actually performs two queries, one after the other. However, both of the updated columns have no index defined.
I would like to make the initial query perform faster.

Do you see any optimization possible?

I am afraid that, since the trigger is on UPDATE, and it actually updates the column, it may trigger multiple times. Is it possible? If yes, how do I prevent it?


texts:
I'm trying to figure out what MySQL code would do the following.

Count * from 'users' table (eg; 0,7 rows) then within the count get the count of 'money' based on the value of the user. I'm struggling to explain, so I hope that I've gave as much detail, 1 more example below.

I'm trying to achieve this:

You are currently the eg 1st, 2nd (depending on money value). richest person on the website.

database example:

-------------------------------
 id  | username  | money
-------------------------------
 1   | name      | 1000
 2   | name2     | 3000
-------------------------------


texts:
I have a series of rows representing either start (S) or and (E) is a thing

+----+-------+-------+
| Id | Event | Thing |
+----+-------+-------+
|  1 | S     | A     |
|  2 | E     | A     |
|  3 | S     | B     |
|  4 | E     | B     |
|  5 | S     | C     |
|  6 | S     | D     |
|  7 | S     | E     |
+----+-------+-------+

I'm trying to get the rows that have no end event.

SELECT [a].[Id] AS [ID a],
       [a].[Event] AS [Event a],
       [b].[Id] AS [ID b],
       [b].[Event] AS [Event b]
  FROM [dbo].[TimeSeries] AS [a]
  LEFT OUTER JOIN [dbo].[TimeSeries] AS [b]
    ON [b].[Thing] = [a].[Thing]
   WHERE [a].[Event] = 'S'
   AND [b].[Event] = 'E'

This returns

+------+---------+------+---------+
| ID a | Event a | ID b | Event b |
+------+---------+------+---------+
|    1 | S       |    2 | E       |
|    3 | S       |    4 | E       |
+------+---------+------+---------+

This is where I'm getting stuck.

I want all the IDs that are neither in the ID a column nor the ID b column.  Then I'm guessing LEFT JOIN to find all the rows that have no end event.

  NB: The real table is an SSIS log with many millions of rows.


texts:
I have a view that looks like this:

CREATE view   reference.Test WITH SCHEMABINDING  as

    SELECT        reference.HighlevelTestId as TestId, Name, IsActive,
                  cast(1 as bit) as IsHighLevelTest
    FROM          reference.HighlevelTest

    UNION ALL

    SELECT        LowLevelTestId  as TestId, Name, IsActive,
                  cast(0 as bit) as IsHighLevelTest
    FROM          reference.LowLevelTest

GO

NOTE: HighLevelTestId and LowLevelTestId are guaranteed to never have the same values (no conflicts).

I have another table that looks like this:

CREATE TABLE [Reference].[TestAddition](
    [TestId] [BigInt] NOT NULL,
    [OtherStuff] [bit] NOT NULL,
    ....
)

I would really like to FK my TestAddition table to my Test view on the TestId column (for referential integrity and ease of use with OData).

Is there any way to do that?  (Any way to change my view to make this work?)


texts: How should a numeric value that is an external key be stored?

texts:
This is something I was pondering recently. trying to determine how to store certain values. The best wide spread example I can think of is Federal Employee Identification Number - more commonly knows as tax id or social security for companies.

For various business / tax / legal reasons we need to store this number for some of our clients.  Now this is a numeric 9 digit field .  However it doesn't change. There are no calculations to be done with it, this is an external reference field that we have no control over.  So should it be stored as char(9) or int(9) ?  What are the benefits of storing one way or another? What's the most efficient way: in terms of space usage(I'm assuming int) , and in terms of performance ( speed of look ups) assuming that we will create a unique index on this column either way?

For the purpose of this exercise DB doesn't matter, but if want to elaborate I use MariaDB cluster ( MySQL)


texts: Securely generate a UNIQUEIDENTIFIER in SQL Server

texts:
I intend to be using a UNIQUEIDENTIFIER as an access key that users can use to access certain data. The key will act as a password in that sense.

I need to generate multiple such identifiers as part of an INSERT...SELECT statement. For architectural reasons I want to generate the identifiers server-side in this case.

How can I generate a securely random UNIQUEIDENTIFIER? Note, that NEWID would not be random enough as it does not promise any security properties at all. I'm looking for the SQL Server equivalent of System.Security.Cryptography.RandomNumberGenerator because I need unguessable IDs. Anything based on CHECKSUM, RAND or GETUTCDATE would also not qualify.


texts: Oracle With Statement and Delete Together

texts:
I am trying to use WITH statement and DELETE together. But I get ORA-00928 error.
Basically my code is like following

WITH TO_DELETE
AS
(
SELECT TableKey
  FROM SOME_TABLE
  WHERE SomeConditions LIKE '%'
)
DELETE FROM TO_DELETE

-- SELECT * FROM TO_DELETE

This code works in SQL Server 2005+ but gives error in Oracle. Any idea how can I solve this problem?

Following workaround exists.

use temp table and drop it.
use following inner select construct.

DELETE FROM
(
SELECT TableKey
FROM SOME_TABLE
WHERE SomeConditions LIKE '%'
)

What I wish to know is there any way using WITH statement?


texts: Replication of a remote MySQL database to local MySQL database

texts:
I'm the developer of my employers corporate private website, a HTML5/PHP/CSS powered site for company reports etc. The site, hosted on a shared hosting package, connects to a remote read-only MySQL DB server for authentication and report data queries. This remote DB is mainly used by a separate windows exe.

The remote DB goes down frequently which of course causes the private website to bomb out on a MySQL connection error. I've thought about replicating the remote DB to the site's local hosted MySQL DB and using the local DB as the site's primary and only DB connection.

Is there a way of synchronising the remote DB to the site's shared hosting DB in a simple and pain free way? Like every hour it updates or something?

Or is there a function where the site's hosting package MySQL DB functionality can cache the remote DB data and use that as a fallback.

Thanks in advance.


texts: How to remove duplicates values within particular ID

texts:
Im struggling with removing duplicate values within certain employee ID. Here is the situation:

ID ||  VALUE1   || VALUE2   || DATE

11 || Position1 || Other1   || 2019-04-04
11 || Position2 || Other3   || 2019-05-04
11 || Position1 || Other1   || 2019-06-04

12 || Position2 || Other3   || 2019-01-04
12 || Position1 || Other1   || 2019-03-04
12 || Position2 || Other3   || 2019-04-04
12 || Position3 || Other1   || 2019-05-04

13 || Position1 || Other3   || 2019-04-04
13 || Position3 || Other1   || 2019-05-04
13 || Position1 || Other3   || 2019-06-04

What I want to do is to remove repeating values for column VALUE1 but only within certain ID and the value which stays is the newest. So the results can look like:

ID ||  VALUE1   || VALUE2   || DATE

11 || Position2 || Other3   || 2019-05-04
11 || Position1 || Other1   || 2019-06-04

12 || Position1 || Other1   || 2019-03-04
12 || Position2 || Other3   || 2019-04-04
12 || Position3 || Other1   || 2019-05-04

13 || Position3 || Other1   || 2019-05-04
13 || Position1 || Other3   || 2019-06-04

I dont know how to achieve that using partition by or distinct. Any advices?


texts: MYSQL Slow SELECT with multiple joins and ORDER BY

texts:
I have this query

SELECT
r0_.id AS id0,
r0_.deleted_at AS deleted_at1,
r0_.updated_at AS updated_at2,
r0_.created_at AS created_at3,
r0_.is_vehicle_offer AS is_vehicle_offer4,
r0_.vehicle_offer_price AS vehicle_offer_price5,
r0_.vehicle_offer_description AS vehicle_offer_description6,
r0_.nr_of_parts_found_mails_sent AS nr_of_parts_found_mails_sent7,
r0_.client_id AS client_id8,
r0_.parent_id AS parent_id9,
r0_.vehicle_id AS vehicle_id10
FROM
requests r0_
INNER JOIN vehicles v1_ ON r0_.vehicle_id = v1_.id
INNER JOIN sibren_brands s2_ ON v1_.sibren_brand_id = s2_.id
INNER JOIN member_sibben_brands_subscriptions m4_ ON s2_.id =m4_.sibren_brand_id
INNER JOIN members m3_ ON m3_.id = m4_.member_id
INNER JOIN member_vehicle_categories m6_ ON m3_.id = m6_.member_id
INNER JOIN vehicle_categories v5_ ON v5_.id = m6_.vehicle_category_id
INNER JOIN member_sibren_brands_filter m8_ ON s2_.id = m8_.sibren_brand_id
INNER JOIN members m7_ ON m7_.id = m8_.member_id
LEFT JOIN request_metadata r9_ ON r0_.id = r9_.request_id AND (r9_.member_id = '210')
WHERE m3_.id = '210' AND v5_.id = s2_.vehicle_category_id AND
m7_.id = '210' AND r0_.updated_at >= '2012-04-19 13:31:26' AND
(r9_.id IS NULL OR r9_.deleted_at IS NULL) AND
r0_.updated_at <= '2012-06-19 13:31:25'
GROUP BY r0_.id
ORDER BY r0_.updated_at DESC
LIMIT 25 OFFSET 0;

Wich takes about 1.5 secs to run.  If I remove the last part GROUP BY .etc.....
than it only takes 0.07 sec.

Here's the result of EXPLAIN

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  m3_ const   PRIMARY PRIMARY 4   const   1   Using index; Using temporary; Using filesort
1   SIMPLE  m7_ const   PRIMARY PRIMARY 4   const   1   Using index
1   SIMPLE  m6_ ref PRIMARY,vehicle_category_id,member_id   PRIMARY 4   const   1   Using where; Using index
1   SIMPLE  v5_ eq_ref  PRIMARY PRIMARY 4   oz_nl.m6_.vehicle_category_id   1   Using index
1   SIMPLE  s2_ ref PRIMARY,vehicle_category_id vehicle_category_id 4   oz_nl.m6_.vehicle_category_id   37  Using index
1   SIMPLE  m4_ eq_ref  PRIMARY,sibren_brand_id,member_id   PRIMARY 8   const,oz_nl.s2_.id  1   Using where; Using index
1   SIMPLE  m8_ eq_ref  PRIMARY,sibren_brand_id,member_id   PRIMARY 8   const,oz_nl.s2_.id  1   Using where; Using index
1   SIMPLE  v1_ ref PRIMARY,sibren_brand_id sibren_brand_id 5   oz_nl.s2_.id    111 Using where; Using index
1   SIMPLE  r0_ ref vehicle_id,updated_at   vehicle_id  4   oz_nl.v1_.id    1   Using where
1   SIMPLE  r9_ eq_ref  request_id,member_id,request_id_2   request_id  8   oz_nl.r0_.id,const  1   Using where

As far as i can see indexes are set OK


texts: Restore Backup Fails - File 'Db' cannot be restored to '{...}.mdf'. Use WITH MOVE to identify a valid location for the file

texts:
I am getting the following errors trying to do a restore using the restore command on a backup file I recieved from a customer.

RESTORE DATABASE SFDB FROM DISK = N'C:\Backup\Backup.bak'

Any thoughts on what I can do to correct this?  It's looking for the mdf, so can I assume that the backup file I have is an incremental backup and not the full backup and that's why it's looking for the mdf?

I am not sure if 1) they have given all the data (the entire db) or 2) I have the right data - and I can do the restore with the right T-SQL commands - I just don't know how.

Help is appreciated.

Msg 5133, Level 16, State 1, Line 2
Directory lookup for the file "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Db.mdf" failed with the operating system error 21(failed to retrieve text for this error. Reason: 15105).
Msg 3156, Level 16, State 3, Line 2
File 'Db' cannot be restored to 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Db.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 2
Directory lookup for the file "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Db_log.LDF" failed with the operating system error 21(failed to retrieve text for this error. Reason: 15105).
Msg 3156, Level 16, State 3, Line 2
File 'DbDev_log' cannot be restored to 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Db_log.LDF'. Use WITH MOVE to identify a valid location for the file.
Msg 3634, Level 16, State 1, Line 2
The operating system returned the error '21(failed to retrieve text for this error. Reason: 15105)' while attempting 'GetVolumeInformation' on 'D:\'.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

RESTORE FileListOnly FROM DISK = N'C:\Backup\Backup.bak' shows me the following...

LogicalName PhysicalName    Type    FileGroupName   Size    MaxSize FileId  CreateLSN   DropLSN UniqueId    ReadOnlyLSN ReadWriteLSN    BackupSizeInBytes   SourceBlockSize FileGroupId LogGroupGUID    DifferentialBaseLSN DifferentialBaseGUID    IsReadOnly  IsPresent   TDEThumbprint
Db  D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Db.mdf  D   PRIMARY 29622272    35184372080640  1   0   0   02925462-83CC-4222-8966-53229FA25B1C    0   0   29032448    512 1   NULL    269000000146900238  735C7F0E-F63D-4AA1-AC48-505A084AC00B    0   1   NULL
Db_log  D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Db_log.LDF  L   NULL    40239104    2199023255552   2   0   0   71038B04-CBC9-4E4A-93AD-4E1268859CB2    0   0   0   512 0   NULL    0   00000000-0000-0000-0000-000000000000    0   1   NULL
DbDev_filestream    D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Db  S   PRIMARY_FILESTREAM  4691012 0   65537   18000000011800001   0   627C9AAA-97BD-4991-9C8C-90C400226A9F    0   0   4784128 512 2   NULL    269000000146900238  735C7F0E-F63D-4AA1-AC48-505A084AC00B    0   1   NULL


texts: Select data from another database instance on the same server in sql server

texts:
I have a query shown below:

select count(*) as Count, datepart(yyyy, [LogDate]) as [Year]
from ViewAssociate..Auth_Log
where ActionCode = 12
group by datepart(yyyy, [LogDate])
order by [Year]

This query is part of a stored procedure based on ReadAssociate database. It's trying to get data from ViewAssociate db. The user trying to run the stored proc doesn't have access to the ViewAssociate db so the query throws an error.

My question is can I modify the query somehow and provide it a username password to run so I can get data from the ViewAssociate Db. Someone suggested Execute As but that doesn't seem to work.

execute as login = 'viewassociate' I get this error:

  Cannot execute as the server principal because the principal "viewassociate" does not exist, this type of principal cannot be impersonated, or you do not have permission.

That viewassociate is a sql login which is db_owner of ViewAssociate database.


texts: Uniqueness constraint in union of two columns' values

texts:
Using postgresql 9.5, assume I have a table:

CREATE TABLE t (
 id INT,
 primary TEXT,
 secondary TEXT
)

I want to guarantee that the union of values in primary and secondary does not contain duplicates. Is this doable via index/checks or do I have to go to a trigger?

For example,

INSERT INTO t(id, primary, secondary) VALUES(1, 'a', 'b');
INSERT INTO t(id, primary, secondary) VALUES(1, 'x', 'y');
INSERT INTO t(id, primary, secondary) VALUES(1, 'a', 'z');
INSERT INTO t(id, primary, secondary) VALUES(1, 'z', 'a');

The latter two INSERTs should fail.


texts: Best way to display number of overspent projects

texts:
I have the first view displayed as:

CREATE VIEW Projects AS
    SELECT DepartmentName,
        COUNT(*)NumberOfProjects,
        OfficeNumber,
        Phone
    FROM   DEPARTMENT AS D JOIN PROJECT AS P
            ON D.DepartmentName = P.Department

    GROUP BY Department;

However, I want to display a "NumberOfOverBudgetProjects" for each department in this view.
I created a view which lists which projects are going over their hourly budget:

CREATE VIEW Budgets AS
    SELECT Department,
        MaxHours AS ProjectMaxHours, SUM(HoursWorked) AS TotalHoursWorked,
        SUM(HoursWorked) - MaxHours AS Balance
    FROM   PROJECT AS P JOIN ASSIGNMENT AS A
        ON P.ProjectID = A.ProjectID
    GROUP BY P.ProjectID

;

Marketing department should list 2 projects being over budget and Accounting and Finance should both list 0.

Any ideas how I can incorporate a column of "OverBudgetProjects" in the original view? Or by creating a new view?


texts: Undesirable Nest Loop vs. Hash Join in PostgreSQL 9.6

texts:
I've a trouble with PostgreSQL 9.6 query planning. My query looks like this:

SET role plain_user;

SELECT properties.*
FROM properties
JOIN entries_properties
  ON properties.id = entries_properties.property_id
JOIN structures
  ON structures.id = entries_properties.entry_id
WHERE structures."STRUKTURBERICHT" != ''
  AND properties."COMPOSITION" LIKE 'Mo%'
  AND (
    properties."NAME" LIKE '%VASP-ase-preopt%'
    OR properties."CALCULATOR_ID" IN (7,22,25)
  )
AND properties."TYPE_ID" IN (6)

I have Row-Level Security enabled for the above-used tables.

with set enable_nestloop = True, the query planner runs
Nested Loop joining with a total running time of about 37 seconds: https://explain.depesz.com/s/59BR
with set enable_nestloop = False, the Hash Join method is used and the query time is about 0.3 sec:  https://explain.depesz.com/s/PG8E

I did VACUUM ANALYZE before running the queries, but it didn't help.

I know that it is not a good practice to set enable_nestloop = False, and any other similar options for the planner. But how could I "convince" the planner to use hash joins without disabling nested loops?

Rewriting the query is an option.

If I run the same query under a role that bypasses RLS, then it is executed very fast. The row-level security policy looks like this:

CREATE POLICY properties_select
ON properties
FOR SELECT
USING (
  (
    properties.ouid = get_current_user_id()
    AND properties.ur
  )
  OR (
    properties.ogid in (select get_current_groups_id())
    AND properties.gr
  )
  OR properties.ar
);

Any ideas or suggestions would be greatly appreciated.


texts:
Context:
We’re building a web application to manage business-data. Each “client” of the application has a separate DB (this wont change), and each DB is based off a model (so they are all identical in structure). RDBM is MySQL.

Problem:
When we make modifications to the model DB, we propagate those changes to all client DBs. This is fine in most cases, EXCEPT when we add a new field that is also a not-null FK. In this case the DB upgrade fails.

What we’ve explored:

We could “impose” that all new FK fields can be nullable, however
this potentially defies the business logic that the DB must respect.
We could “select” a default value from the existing data in each
client DB. However, this is potentially absurd: Think of adding a NotNull
FK from Invoice table to Client table… which client should be
selected?

Is there a way to make these types of upgrades without making (crazy) default selections or breaking the business logic of the DB?


texts: Column order in a DynamoDB table browsed through RazorSQL

texts:
I use RazorSQL to navigate in a DynamoDB database. What's the rationale behind the column order?

I am asking as the import tool uses the same order (which means if I import a CSV file it needs to be in the same ordering as the RazorSQL's column order):

I use RazorSQL 6.3.14 with Windows 7 SP1 x64 Ultimate.


texts: sp_BlitzIndex v 4.5 (2016-11-15) failing with collation error

texts:
Posting this in case anyone else runs into this issue... (fix below)
After updating to SQL 2016 SP1, I also updated to the latest version of the sp_Blitz procs. Unfortunately, sp_BlitzIndex now blows up in the 'Gathering Computed Column Info.' section around line 1586.

Here is the error text:

Gathering Computed Column Info.
Failure populating temp tables.
Msg 2787, Level 16, State 1, Procedure sp_BlitzIndex, Line 1620 [Batch Start Line 4]

Invalid format specification:
    '%.%' THEN 1 ELSE 0 END AS is_function,
    'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) +
    ' ADD ' + QUOTENAME(c.name) + ' AS ' + cc.definition  +
    CASE WHEN is_persisted = 1 THEN ' PERSISTED' ELSE '' END + ';' AS [column_definition]
    FROM    [Production-db].sys.computed_columns AS cc
    JOIN    [Production-db].sys.columns AS c
    ON      cc.object_id = c.object_id
    AND cc.column_id = c.column_id
    JOIN    [Production-db].sys.tables AS t
    ON      t.object_id = cc.object_id
    JOIN    [Production-db].sys.schemas AS s
    ON      s.schema_id = t.schema_id
    OPTION (RECOMPILE);'.

Msg 50000, Level 16, State 1, Procedure sp_BlitzIndex, Line 1624 [Batch Start Line 4]

Production-db database failed to process.
Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_100_CI_AS_KS_WS_SC" in add operator occurring in SELECT statement column 11.

The solution was to add a COLLATE statement to line 1599 (line 1605 in the delivered script file):

CASE WHEN is_persisted = 1 THEN '' PERSISTED'' ELSE '''' END + '';''  collate SQL_Latin1_General_CP1_CI_AS AS [column_definition]


texts: What indexes would I create for a query like this?

texts:
Here's a query I've got:

SELECT "balance_transactions"."fee"
FROM "balance_transactions"
JOIN charges ON balance_transactions.source = charges.balance_id
WHERE "balance_transactions"."account_id" = 123
AND (balance_transactions.type = 'charge'
AND charges.refunded = false
AND charges.invoice IS NOT NULL)
AND ("balance_transactions"."created" BETWEEN '2013-12-29' AND '2014-01-28');

What's hurting my brain is figuring out what I need to index to optimize a query like this.

Would I create individual indexes on each column in that WHERE statement? Or a single index that encompasses multiple columns? Or something else?

I happen to be using Postgres.

Here's the output of EXPLAIN ANALYZE:

 Nested Loop  (cost=40.44..9294.43 rows=744 width=4) (actual time=0.791..32.687 rows=1131 loops=1)
   ->  Bitmap Heap Scan on balance_transactions  (cost=40.02..2266.08 rows=1023 width=22) (actual time=0.737..1.006 rows=1201 loops=1)
         Recheck Cond: ((account_id = 79) AND ((type)::text = 'charge'::text) AND (created >= '2013-12-29 00:00:00'::timestamp without time zone) AND (created <= '2014-01-28 00:00:00'::timestamp without time zone))
         ->  Bitmap Index Scan on index_balance_transactions_account_type_created  (cost=0.00..39.77 rows=1023 width=0) (actual time=0.716..0.716 rows=1201 loops=1)
               Index Cond: ((account_id = 79) AND ((type)::text = 'charge'::text) AND (created >= '2013-12-29 00:00:00'::timestamp without time zone) AND (created <= '2014-01-28 00:00:00'::timestamp without time zone))
   ->  Index Scan using index_stripe_charges_on_stripe_id on charges  (cost=0.42..6.86 rows=1 width=18) (actual time=0.025..0.025 rows=1 loops=1201)
         Index Cond: ((balance_id)::text = (balance_transactions.source)::text)
         Filter: ((NOT refunded) AND (invoice IS NOT NULL))
         Rows Removed by Filter: 0
 Total runtime: 32.924 ms
(10 rows)


texts: MySQL - Select when last entry before timestamp has specific value

texts:
I can find rows in a table where the last row has a specific value. However, I'm having a hard time finding them when I need to limit the max, or min, timestamp.

Let's say I have table 'people'.

id   name   value   date
a    last   jones   2018-04-03 01:00:00
a    last   smith   2018-04-03 03:03:03
a    last   johns   2018-04-04 02:02:02
b    last   johns   2018-04-03 12:12:12
b    last   smith   2018-04-04 03:03:03
c    last   smith   2018-04-03 11:11:11
c    last   johns   2018-04-03 17:17:17
c    last   jones   2018-04-05 01:01:01

I want to get the id's where the last row has name='last' and value='johns'

SELECT  distinct a.id
FROM    people a
left JOIN people b
   ON (a.id = b.id and a.name=b.name and a.date < b.date)
where b.date is null
  and a.name = 'last'
  and a.value = 'johns';

This gives me

id
--
a

Great. However, now I want to limit my search where date < '2018-04-04 00:00:00', which should return

id
--
b
c

I've tried adding a.date < and b.date < in the LEFT JOIN ON, tried adding a.date < to the WHERE but I'm not getting the expected results. Thanks!


texts: How, if possible, can an .sql file be executed against an oracle database from a bash script

texts:
Is it possible to execute an sql file (a text file containing several SQL statements) against an oracle database from a bash shell command line (e.g. no graphical GUI and no interactive clients)

Something like:
sqlplus -f queries.sql ...

The command is expected to execute all the statements in the queries.sql file and exit with an appropriate exit code (e.g. 0 if all queries executed correctly, nonzero otherwise)


texts: Create one row for every user in a Postgres table

texts:
I have a bunch of rows of users in a users table, for example:

| id |  name |
|----|-------|
|  1 | Chris |
|  2 | Max   |
|  3 | Steve |

For each one of these users, I'd like to create a row in another table called user_flags:

| user_id |  flag  |
|---------|--------|
|       1 | myflag |
|       2 | myflag |
|       3 | myflag |

Currently what I'm doing is downloading users in batches of ~100 at a time, and then doing a bulk INSERT query for each batch. However I think that there should be a quicker way.

The goals:

Insert the flag rows as quickly as possible
Long running queries are fine, as long as there's no table locking anywhere
These are real-time queries, so indexes cannot be dropped
Should be able to comfortably run over millions of user rows

Is this possible? I feel like with some smart functions it could/should be.


texts: Substring without the first n characters

texts:
I'm developing a SQL Server 2016 stored procedure and I want to get the last characters of a varchar(38) column.

I know there will always be at least 18 characters and I don't know the exact length of the column, because it is variable.

I think I can get the length of the column and do a subtract to use SUBSTRING, but I can't do that because I'm doing this:

set @externalCodes = (
    select Serial, AggregationLevel
      from ExternalCode where ProductionOrderId = @productionOrderId
    for json path

I'm generating a JSON and I don't know how to get the length of each Serial column inside a select.

My question is: how can I get a substring from a string without the first 18 characters without knowing its length?

One solution could be:

SUBSTRING(Serial, 18, 38)

And it always return the substring from 18 to the end of the string even if the string doesn't have a length of 38.


texts: Trigger function does not exist, but I am pretty sure it does

texts:
I have a procedure:

create or replace procedure pro_update_last_read(in input_sensor_id integer, in read_time timestamp)
as
$$
begin
    update sensor
    set last_read = read_time
    where sensor_id = input_sensor_id;
end;
$$ language plpgsql;

And a trigger that calls it:

create trigger tri_last_read
    after insert or update of report_time
    on report
execute procedure pro_update_last_read(sensor_id, report_time);

However, when creating the trigger, I get the error message:

 [42883] ERROR: function pro_update_last_read() does not exist

Why is this error happening?


texts: Function that returns table with an additional column

texts:
I want to write a PostgreSQL function that returns a table, plus an additional column. Is there a way to do this without manually specifying RETURNS TABLE (col1 type, col2 type, ...)?

For example, consider the following function:

CREATE FUNCTION get_users_with_most_videos_since_time(ts TIMESTAMPTZ)
  RETURNS SETOF "user" AS $$
    SELECT
      u.*,
      count(v.id) AS vids_since

    FROM "user" AS u
    INNER JOIN "video" AS v ON v.creator_id = u.id
    WHERE v.created_at > ts

    GROUP BY u.id
    ORDER BY vids_since DESC;
$$ LANGUAGE SQL;

This fails with the error:

ERROR:  return type mismatch in function declared to return "user"
DETAIL:  Final statement returns too many columns.

Fair enough, we're including that vids_since column, which doesn't exist in the "user" table.

So to fix this I'd want to change it to something like:

CREATE FUNCTION get_users_with_most_videos_since_time(ts TIMESTAMPTZ)
  RETURNS
    TABLE (<<< all columns from table "user" >>>, vids_since BIGINT)
  AS $$
    ...

Is there a way to do this without having to re-copy the entire schema of the "user" table here?


texts: How can I pivot this data using a cte or the pivot syntax?

texts:
Let's say I have these two tables in an MSSQL-Database:

Is there any way to create a query that result in a table like this?

There is no case where there are multiple Leader/Assistant in the TEAMS-Table. Both of these only occur once per project.


texts: SQL Server FCI won't start after OS Upgrade

texts:
I have a two node sql server 2008 r2 cluster that we just did an OS upgrade from windows 2008 r2 to windows 2012 r2, the OS upgrade was done in place so the SQL Server Installation stayed in tack.  The cluster role won't come back online saying it's couldn't find any IP address that this instance depends on.

Everything comes on line except SQL Server resource and looking in the sql server log file it shows that error sql server is starting up as I can see master and model get started up.

From the sql log:

  Could not find any IP address that this SQL Server instance depends upon.  Make sure that the cluster service is running, that the dependency relationship between SQL Server and Network Name resources is correct, and that the IP addresses on which this SQL Server instance depends are available.  Error code: 0x5.

Are there additional steps that need to be taken with Windows Server 2012 r2 that need to be done to get SQL Server 2008 r2 to be able to run in a failover cluster?


texts: One table with a changing variable or different tables?

texts:
I have a problem where all the data except one column, which is known to have 4 different values, will be a variable.

Other than that column everything else will be of same type(only column names are same but data will be different)

For smaller applications I hope it is OK to just add the flag and make it one table.

I will be working with data containing around 100,000 rows and increasing everyday at the below ratios. If split they will be like

Type 1 - 60,000
Type 2 - 20,000
Type 3 - 10,000
Type 4 - 10,000

I want a better way to implement especially on MySQL but information about Oracle is much appreciated as I want to gain knowledge in that too.

What happens if I add this to a single table and make the type as index so the burden for where conditions will be low?

How comparable is that to having 4 different tables?

Personally, I don't like the idea of 4 tables but I am looking for an answer in performance perspective.


texts: How to perform builk update within mysql stored procedure

texts:
I have been trying to perform a bulk update using a single update query but unable to do so within the stored procedure.
Following is the bulk update query that run flawlessly

UPDATE `batch_requests_detail`
SET `processing_status` = 5
WHERE `batch_requests_detail_id`  IN (1,2,3);

Where as when I use it within the stored procedure it didn't work

CALL Update_Batch_Request_Detail_Bulk('1,2,3',10);

CREATE DEFINER=`root`@`%` PROCEDURE `Update_Batch_Request_Detail_Bulk`(
IN _batch_request_detail_ids VARCHAR(5000),
IN _processing_status INT
)
BEGIN
    UPDATE `batch_requests_detail`
    SET `processing_status` = _processing_status
    WHERE `batch_requests_detail_id`  IN (_batch_request_detail_ids);
    END$$

DELIMITER ;


texts:
I want to trim dot (.) from a column value. Just say string is "Mr." and I need to trim the dot from the string. I am expecting "Mr"

Lets say,

I have two fields in my table

 id   |  value
 1       Mr.
 2       Ms.
 11      Mr.
 12      Ms.

But while running below query, not getting as i expected.

 select trim(value, '.') from mytable

 value
 (text)
 Mr
 Ms
 Mr.
 Ms.

And noticed, for id 1 & 2 values is coming as expected. but for id 11 & 12 still the dot is coming. I really could not understand what is the issue.

(Using Postgresql 9.1)


texts: Create/Delete file using SQL Server 2008

texts:
I have an Excel file in C:\testing.xlsx. I am exporting data from my database table to Excel using [OPENROWSET (Transact-SQL)]1 method. Now I want to

clear or delete all rows in my Excel file by executing some query in SQL Server
delete or create new Excel file in specified path (ie C:\) from SQL Server

Can I do this? If, please suggest me how to do that?


texts: Why does MySQL forbid LOCK TABLES in stored procedures?

texts: I have read through several sections of the MySQL reference manual, and while it states repeatedly that LOCK TABLES cannot be used in a stored procedure, I have never found an explanation for the reasoning behind that.


texts:
I have a table 'member' and want to be able to search the table and order results by relevance. I have a single string to search on and want to search multiple fields. Example, given_name and family_name (like the Facebook member search).

The fields in in the member table are strings so I think a full test search will not be the best option.

Table Member

+----+------------+-------------+----------------+
| id | given_name | family_name | mutual_friends |
+----+------------+-------------+----------------+
|  1 | Levi       | Putna       | 4              |
|  1 | Levi       | Levi        | 1              |
|  2 | Leviother  | Somename    | 4              |
|  3 | Boo        | Somelevi    | 4              |
|  4 | Lala       | Smith       | 4              |
|  5 | Assem      | Jones       | 4              |
+----+------------+-------------+----------------+

I have been trying to build a query that creates a score based on matching some criteria and then ordering by the score. I am using a case to do this.

For this example we will use the search query of 'levi'

SELECT id, given_name, family_name,
CASE
    WHEN given_name = 'levi' THEN score + 5
    WHEN given_name like 'levi%' THEN score + 3
    WHEN family_name = 'levi' THEN score + 4
    WHEN family_name like 'levi%' THEN score + 3
    WHEN given_name like '%levi%' THEN score + 1
    WHEN family_name like '%levi%' THEN score + 1
END AS score
FROM (select *, 0 AS score FROM member) AS member_search

ORDER BY score DESC

This seems like a bad way to do this and will only score based on the first match in the case. How would I need to rewrite this query so that it could continue to add to the score based on multiple matches of case? Is their a better way to manage this type of query?


texts: Variable problems in PostgreSQL with language plpython3u

texts:
I am setting up a function where recursive action is relevant. The function basically maps two floats  according to a rule and then returns a string. The code below is stripped for the actual function, but it should serve as a relevant code example (full code available). The original code (python 2.7) works fine, so I am thinking that there is something about the language, plpython3u, used in PostgreSQL which messes it up.
I imagine this is related to local variables and global variables. I would have thought the variables remained local, but it does not look that way. Is there a way to work around this?
Running the function I get an error message indicating that a variable has already been
ERROR:  UnboundLocalError: local variable 'square' referenced before assignment
CONTEXT:  Traceback (most recent call last):
  PL/Python function "qdgc_get_recursivestring", line 3, in <module>
    square ='ABC'+square
PL/Python function "qdgc_get_recursivestring"
SQL state: 38000

Function:
CREATE OR REPLACE FUNCTION public.qdgc_get_recursivestring(
    lon_value double precision,
    lat_value double precision,
    depthlevel integer,
    square text)
    RETURNS text
    LANGUAGE 'plpython3u'
    COST 100
    VOLATILE PARALLEL UNSAFE

AS $BODY$

square ='ABC'+square

depthlevel = depthlevel -1

if depthlevel<1:
    return square
else:
    return qdgc_get_recursivestring(lon_value,lat_value,depthlevel,square)

$BODY$;


texts: Bandwidth-efficient, fail-safe database resync with long backlogs

texts:
I'm looking for a DB system that allows slaves to resync even if there exists a long backlog. In addition these resyncs must be as bandwidth-efficent as possible as the nodes are connected over GSM/GPRS. A slave might be offline for several hours. All nodes run Linux.

I already looked at MySQL but it does not seem to fit the purpose as slaves might go out of sync and need to be reinitialized completely.

Please let me know in case the question is too vague.


texts: What to check before attempting a read-scale replica log backup?

texts:
We have a read-scale replica that we take our backups from. The network connection is not the best and sometimes we lose sync. It comes back in a few minutes, and for our purposes we can live with it. However, during that time-frame, sometimes the log backups fail. I read this which finally explained why that might be happening:

http://www.centinosystems.com/blog/sql/designing-for-offloaded-backups-in-alwayson-availability-groups/

Basically it sounds as if LSNs get too far apart and then backups will fail. So I think it would be a good idea to check those LSNs with sys.dm_hadr_database_replica_states and take appropriate action. My question is what LSNs should I compare to delay a backup until it will not fail? There are 9 of them, and I have trouble going from the blog post to the DMV.

My goal is to delay the backup until it will not fail, so if this is not the best approach I am open to something better.

Thanks.


texts: PostgreSQL: Using transactions for all queries so that SET run time variables will always be LOCAL? (context: Row Level Security)

texts:
I need to set run time variables to identify app users on a postgres DB for row level security.
For example:
  SET app.user_id = $1;

app.user_id will be referanced as follow:
  CREATE POLICY folder_read ON "folder"
  FOR SELECT
  USING(
    "id" = ANY(
        ARRAY(SELECT folder_id
          FROM folder_user
          WHERE folder_user.user_id =
            current_setting('app.user_id')::bigint)
        )
    );

I'm using typeorm for data access and currently as far as I understand there is no api that supply insurance of the connection used for queries from the connection pool.
The only solution I found is to use transactions.
Is that a reasonable solution?
will it harm the performance fatally?
related question already asked here:
Row level security with a single DB user and connection pooling


texts: Can I setup several mariadb clusters, each for a schema

texts:
I'm planning to use MariaDB Cluster, as a way to sync several databases from different peripherals (raspberry pis).
The peripherals don't share data, but all data should be shared with the main server.

Is there a way to keep the same database, but create a cluster with 2 nodes for each schema ?

Thanks


texts: Dedicated SQL Server instance for SQL Agent

texts:
I have an environment that has dozens of SQL Server instances all running SQL agent jobs for their own maintenance as well as other processes (ETL, etc.). I am considering a change to consolidate the non-maintenance jobs to a separate SQL Server instance(s). I'm thinking that having the SQL Agent job processing setup on centralized servers would be easier to manage and coordinate jobs' schedules. I would also plan on using one of these SQL Agent only instances as the distributor for replication purposes.

Besides licensing concerns what downside would there be to move to such a configuration?


texts:
Postgres version 9.3, hosted on Amazon RDS.
I have a messages table with 10G of data.
I connected the database using psql on the EC2.
Then used

\COPY (select * from messages) TO '/tmp/messages.csv' WITH (FORMAT CSV, FORCE_QUOTE *)

to copy all messages into a file on the EC2.

Then I try to copy the file back to the table:

\COPY messages FROM '/tmp/messages.csv' WITH (FORMAT CSV)

I always got this error after around 5 minutes. This messages.csv file is around 10G.

connection not open
The connection to the server was lost. Attempting reset: Succeeded.

I tried a dumb solution, to reduce the input data size, by spliting the file into multiple smaller trunks. ie 400MB each.

split -l 1000000 messages.csv messages_

This creates multiple smaller files with 400MB each. This works fine.

But is there any configurations I can change to keep the connections alive for large file?

I tried to set tcp_keepalives_idle=7200 and tcp_keepalives_interval=7200 and tcp_keepalives_count=5, but the connection still lost after 10-15 minutes.


texts: Start Sql Agent Service with non-administrator priviliges

texts:
I'm trying to start SQL Server Agent service with domain account that is not part of administrator group. I found this https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms143504(v=sql.110). Steps I did:

Granted access in Local Security Policy

Log on as a service
Replace a process-level token
Bypass traverse checking
Adjust memory quotas for a process

Granted full control in regedit: HKLM\Software\Microsoft\Microsoft SQL Server
Edited NTFS permissions:

Instid\MSSQL\binn
Instid\MSSQL\Log
110\com
110\shared
110\shared\Errordumps

I have not just granted NTFS permission on ServerName\EventLog. What folder is it? Do I need to do something else?
I can not change log on account from configuration manager because there are not all services are listed, only Browser service.


texts:
I have a table that saves the needed information to send notifications to users. now the UID columns in this table is almost unique because mostly each user subscribes with one device.
but it is possible to have multiple rows with same UID so unique index is out of picture and because of almost unique nature of data i don't think usual index be useful either as it's going to take almost same time to search index as whole table.
Currently the solution that comes to my mind is saving primary code of each row of notifications table in a column in Users table in comma separated format.


texts:
Why did PostgreSQL choose an elephant as it's logo? It wasn't clear from logo wiki.

UPDATE: Now wiki updated :) Thanks!


texts: PostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK

texts:
I'd like to limit a PostgreSQL 9.2 tstzrange to valid dates at both ends. No NULLs nor 'infinity'.

Various revisions of this SQL isn't constraining '-/+infinity' input:

CREATE TABLE bill
(
  id serial NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  period tstzrange NOT NULL,
  -- other columns
  CONSTRAINT bill_pk PRIMARY KEY (id),
  CONSTRAINT bill_period_lower_not_null_ck CHECK (lower(period) IS NOT NULL),
  CONSTRAINT bill_period_upper_not_null_ck CHECK (upper(period) IS NOT NULL),
  CONSTRAINT bill_period_lower_not_infinity_ck CHECK (lower_inf(period) IS FALSE),
  CONSTRAINT bill_period_upper_not_infinity_ck CHECK (upper_inf(period) IS FALSE)
);

INSERT INTO bill (period) VALUES (tstzrange (NULL, NULL));
INSERT INTO bill (period) VALUES (tstzrange ('2000-05-10', '2015-02-22');
INSERT INTO bill (period) VALUES (tstzrange ('2000-05-10', now());
INSERT INTO bill (period) VALUES (tstzrange ('infinity', now());
INSERT INTO bill (period) VALUES (tstzrange ('-infinity', now());
INSERT INTO bill (period) VALUES (tstzrange (now(), 'infinity');

psql (9.2.3)
Type "help" for help.

test_1=> SELECT id, period FROM bill;
 id |                           period
----+------------------------------------------------------------
  1 | ["2000-05-10 00:00:00+01","2015-02-22 00:00:00+00")
  2 | ["2000-05-10 00:00:00+01","2013-10-16 13:20:01.793803+01")
  4 | [-infinity,"2013-10-16 13:20:18.993038+01")
  5 | ["2013-10-16 13:21:14.208279+01",infinity)
(4 rows)

Changing the *_inf(period) IS FALSE checks to TRUE rejects valid dates.

Range functions in the manual

Thoughts?


texts: PostgreSQL database re-design considerations

texts:
We have a 2TB PostgreSQL database, which contains two groups of tables that account for about 90% of the storage used. These two groups can be loosely described as 2 big tables partitioned, the first group has this table as parent:

CREATE TABLE avl_historico (
    fecha timestamp with time zone NOT NULL,
    latitud double precision DEFAULT 0 NOT NULL,
    longitud double precision DEFAULT 0 NOT NULL,
    altitud double precision DEFAULT 0 NOT NULL,
    velocidad double precision DEFAULT 0 NOT NULL,
    cog double precision DEFAULT 0 NOT NULL,
    nsat integer DEFAULT 0 NOT NULL,
    tipo character(1),
    utc_hora time without time zone,
    fix_fecha date,
    imei bigint NOT NULL,
    registro timestamp with time zone,
    input1 integer DEFAULT 0,
    input2 integer DEFAULT 0,
    input3 integer DEFAULT 0,
    input4 integer DEFAULT 0,
    hdop double precision,
    adc double precision DEFAULT (-99),
    ignicion integer DEFAULT 1,
    adc2 double precision,
    power integer,
    driverid integer,
    ibutton2 integer,
    ibutton3 integer,
    ibutton4 integer,
    trailerid integer,
    adc3 double precision,
    adc4 double precision,
    horometro bigint,
    odometro bigint,
    panico integer DEFAULT 0,
    bateria double precision,
    bateriaint double precision
);

It's children are built like this:

CREATE TABLE avl_historico_354898046636089 (
    CONSTRAINT reports_avl_historico_only_354898046636089 CHECK ((imei = 354898046636089::bigint))
)
INHERITS (avl_historico);

The second "group" has this parent table:

CREATE TABLE historico (
    imei bigint NOT NULL,
    utc timestamp without time zone NOT NULL,
    clase integer NOT NULL,
    valor double precision[],
    registro timestamp with time zone DEFAULT now(),
    power integer DEFAULT (-1),
    voltaje double precision DEFAULT 0.0
);

And it's children:

CREATE TABLE historico_354676056027609 (
)
INHERITS (historico);

There are about 15.000 tables in each of these groups, and each table get's a new record inserted roughly about 40 secs. We only keep online the last 12 months, and older data gets pg_dump'd and archived for later retrieval if necessary.

We guess there might be redesign considerations that may improve storage needs, speed and perhaps even management, but are clueless on what to do, what to look for, or what to search within our data.


texts: MySQL IDB File grows huge despite having little data

texts:
We have recently migrated to a new database server with mysql 8 (from 5.7 previously). We now have a table that stores an int, a datetime and a mediumtext column that grows unproportionally huge. So much so that the server's complete hard drive space was depleted.

The problem is the table only contains about 100 rows of data and the idb file after an optimize table is 7 Mb. after a week running the table grew to over 700GB.

The table does receive a lot of deletes+inserts. We had the same table on the old server for years without issue.

i tried

innodb_purge_threads = 32
innodb_max_purge_lag = 2000000

But the table continues to grow at about 20Mb per Minute without any obvious reason. Am I missing any special new mysql feature?

Or will I need to have my apps run an optimize table every couple minutes now. It seems the database should be handling this issue by itself instead of needing to rely on such hacks from applications.

Thanks in advance for any pointers!

The MySQL Server is Version 8.0.18 running on Windows 2019 with 32GB of RAM. Nothing else runs on this server and the database server is an upgrade from an older version that had a third of this specs. The table structure is identical and ran for years without issue on the old server.

Below is the table structure:

The following is the Show Create Table
CREATE TABLE `automationjobstate` (
  `AutomationJobID` int(10) unsigned NOT NULL,
  `StateTimestamp` datetime NOT NULL,
  `StateData` mediumtext,
  PRIMARY KEY (`AutomationJobID`),
  CONSTRAINT `automationjobstate_ibfk_1` FOREIGN KEY (`AutomationJobID`) REFERENCES `automationjobs` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

How do I attach the results from SHOW VARIABLES? It says its to many characters.


texts: Help with this query, joining three tables in mysql

texts:
Can you help me with this query:
I have three tables

first, main table

page_id | title
1 aaa
2 bbb
3 ccc
4 ddd

revision tbl:

id | rev_page | rev_text_id
1 1 1
2 2 2
3 3 3
4 4 4
5 3 5
6 3 6
7 4 7

text table:

old_id | old_text
1 some_text_blob
2 some_text_blob
3 some_text_blob
4 some_text_blob
5 some_text_blob
6 some_text_blob
7 some_text_blob

I used this query

SELECT c.old_id,c.old_text
FROM text c
INNER JOIN revision b ON c.old_id=b.rev_text_id
INNER JOIN page a ON a.page_id=b.rev_page
WHERE a.page_id=3

and I get

3 some_text_blob
5 some_text_blob
6 some_text_blob

I need only the last one,
6 some_text_blob.


texts: Query running slow on script and producing error 500 when run in phpmyadmin

texts:
I have a query that seems to take a good 30 seconds to execute even when the query produces no results. There are 4000 lines within the database and queries that produce all results run within fractions of a second. Could a greater mind please tell me where i am going wrong as wherever i have asked there is no error in it.

SELECT ShipAddress1, CustomLabel, Quantity, PhService, UniqueID, FullName, ShipPostCode
FROM internetorders WHERE ShipAddress1 IN (SELECT ShipAddress1 FROM internetorders
WHERE Complete = '0' AND LabelCreated = '0' GROUP BY ShipAddress1 HAVING Count(*) > 1)
AND Complete = '0' AND LabelCreated = '0' ORDER BY ShipAddress1

When running in my script the query returns the result but slowly, when running in phpmyadmin i get error 500.

Also this query is obviously returning all results with a duplicate record in ship address one. How could i have it return all results that are same in ship address 1 and post code only?

Thanks in advance.


texts: sp_add_jobstep add more than 4000 Character in @command parameter

texts:
sp_add_jobstep is well documented here.  I understand parameter @command is type NVARCHAR(MAX).

I am creating a SQL Agent Job using a Stored Procedure.  Passing the value for @command parameter via a variable.  I am getting truncated at 4000 character.

My variable is also declared as NVARCHAR(MAX)

Any workaround to add more than 4000 characters in @command parameter?


texts: Chronological list of statements from Oracle trace

texts:
I'm using a trace in Oracle (11.2) to elucidate the behavior of a client application, and would like to view a list of all of the SQL statements that it submits, in the order in which they were submitted. Toward this end, I enable the trace using:

EXECUTE DBMS_SYSTEM.SET_EV(<sid>, <serial#>, 10046, 12, '')

This produces a trace file with the information I'm seeking, though in a format that is difficult for human consumption. Accordingly, I format the raw trace using:

tkprof raw.trc formatted.out explain=user/password aggregate=no sys=no waits=yes record=formatted.rec

This facilitates readability, but also obscures the real-world sequence of events in both of the output files (formatted.out, formatted.rec).

Specifically, the client appropriately holds open the cursors for popular statements and re-executes them on subsequent invocations. The default (and only?) behavior of tkprof is to:

In the main output file, combine the stats for all executions
In the record file, only list the statement when initially parsed

(The latter of which assumes I'm lucky enough to catch the parse event in the first place, which usually requires flushing the shared pool before enabling the trace, but I digress.)

For example, if the client submits statements like the following and holds the cursors open:

SELECT column1 FROM table1 WHERE column2 = :value;
INSERT INTO table1 SET column1 = :value1, column2 = :value2;
SELECT column1 FROM table1 WHERE column2 = :value;

The output record file will only show two statements:

SELECT column1 FROM table1 WHERE column2 = :value;
INSERT INTO table1 SET column1 = :value1, column2 = :value2;

There is no evidence, as far as I can tell, that the first statement was executed a second time.

Similarly, the main trace file will only show reports for two statements, though the first one will indicate that the SELECT statement was executed/fetched twice (only a partial excerpt follows):

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          4          6           0
Fetch        2      0.00       0.00          0          0          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.00       0.00          0          4          6           2

It's not clear from this formatted output which statements were executed in which order - only that one of them was executed twice at some point.

As far as I can see, I need to read the raw trace file and laboriously follow the trail of cursor numbers (some of which get reused for different statements over time) to understand the actual sequence of events, as they occurred in the real world.

My question is:

Is there some tkprof option, or tkprof alternative, that will extract from a raw trace file a list of all SQL statements in the
order that they were executed, even in cases where the same cursor is
held open and executed multiple times?

Ideally, this output would include the bind variable values for each execution, or at least the SQL ID or cursor ID, so I could go back and find them in the raw trace.

Thanks in advance.


texts: Linking SQL Server tables to a PostgreSQL Server

texts: I have a SQL Server 2005 instance and also a PostgreSQL 9.3 running on the same network, I would like to know if its possible to somehow create or have 'views' on a database within the PostgreSQL server that show data coming from SQL Server.


texts: Why does MySQL + Windows Server = crashed tables periodically?

texts:
Windows Server 2008 R2 x64
MySQL Server 5.6.14 x64

Is anyone else having an issue in where some tables are marked as crashed on a Microsoft Windows server? We have a couple of Windows servers (not by choice) and a half a dozen Linux servers running MySQL. For some reason, the tables on the Windows servers get marked "crashed" every so often. We never have these problems on our Linux (Ubuntu) servers.

2014-01-23 10:11:42 1868 [ERROR] MySQL: Table '.\elite_prod\ffcont' is marked as crashed and should be repaired
2014-01-23 10:11:42 1868 [ERROR] MySQL: Table '.\elite_prod\bldr' is marked as crashed and should be repaired
2014-01-23 10:11:43 1868 [ERROR] MySQL: Table '.\elite_prod\frtfwd' is marked as crashed and should be repaired
2014-01-23 10:11:44 1868 [ERROR] MySQL: Table '.\elite_prod\histry' is marked as crashed and should be repaired

It seems we have to deal with this issue every couple of weeks or so. Very frustrating.


texts:
I use the following to load data into postgres
\copy tab FROM /tmp/file DELIMITER E'\t' QUOTE E'\b' NULL AS '\N' CSV

Usually I select the data from a source db
echo "select * from tab" | mysql --batch --quick --skip-column-names .... > /tmp/file

It generates a file with tab delimiter fields. It works well for the most part. Up until I try to import multi line text column.
The error comes on:
ERROR:  unquoted carriage return found in data

Any recommendations how to overcome this?


texts: Fastest way to alter column type in a 2-billion+ records innodb table

texts:
I have a MySQL table with 4 columns.  The 1st column record_id is of type int, primary key, and auto-incremented.  Now that I have added more than 2 billion records to this table I am finding that I have exceeded the range of my auto-incremented field. I need to modify record_id to a "bigint" to accommodate more records.  What would be the fastest way to do this?

Is there a way to do this in-situ without dumping the tables?


texts: Copying the database from SQL Server to PostgreSQL

texts: I currently have SQL Server 2005 and I want to make a copy of the entire database into PostgreSQL. I don't want to migrate to new PostgreSQL but just a entire copy. I almost have more than 1 TB of data in SQL Server. Is there any tool that can simplify this copying process? Also, I assume compression techniques have become much more advance in recent years so how much storage I should expect in PostgreSQL?


texts:
I have a pretty simple select statement,

select column 4 from table_x where column1=:1 and column2=:2 and column3=:3

Columns 1,2 and 3 make up the primary key on the table.

For some reason, rather then using the primary key index, oracle is using a nonclustered index that only contains 1 of the columns.

The only thing that I can find for why the optimizer is ignoring the primary key index, is that the bind variables used in the query are not defined the same way as the columns.  The bind variables are all defined as varchar(32), the columns are nchar, number, and nchar.  Is this mismatch enough to prevent the optimizer from using the primary key index?

Stats have been updated on the table and all the indexes.


texts: Multiple conditional statement checking SQL

texts:
I have a table with different columns. One of them is Message. I want to check multiple patterns using like in this case.

For a start position here is a simple patterns:

DECLARE @pattern VARCHAR(50) = '%|35=[A,D]|%';

For matching this pattern, I use this query:

SELECT Logid 'LogId', 'Message' Message
FROM Messages
WHERE LogId >= 1000 AND
    (m.Message like @pattern);

And it is an example of Message content:

|8=FIX.4.4|9=70|35=A|34=1|10=008|

Now I want to check a complex pattern, which is:

DECLARE @pattern VARCHAR(50) = '%|35=[A,D]|% %|34=[1]|%';

It means that we need to have a message with %|35=[A,D]|% AND %|34=[1]|%. I need to match with a complex pattern with n parts which there is a space between conditions. The previous example is a condition with to statement.

So, first of all, I need to create a list of conditions by splitting pattern and check all of the conditions and make a simple AND between results. I would be glad if I can do that. The last point is that the pattern is an input of a stored procedure.


texts: Azure mysql user connections with proxy sql

texts:
I’ve looked for a couple of days, and can’t find a definitive answer.  I have an application running on a kubernetes cluster that wants to connect to an Azure Database for MySQL server via proxysql. The Azure service requires user names in the format user@AzureDBName.
My application generates usernames based on the origin host. (user@OriginHostIP)
I’ve read that proxysql can accept requests from user a, and relay them to MySQL with user b.  I’ve read through the proxysql documentation, and can’t find an explicit example.  Has anyone done this? Thanks.


texts: Trouble understanding INITRANS in oracle

texts:
I need to calculate a tablespace size and part of the calculation requires a value for INITRANS which I don't fully understand and I wasn't sure if I should just use the default value 2.

My business rules specify that data is inserted from up to 50 different locations so I am to assume some concurrency, is this an indication that I should use an INITRANS of 50 or am I misunderstanding what INITRANS is?


texts: Microsoft Access constrain view by date in outer query

texts:
I'm working on a query in Microsoft access and it does not appear to allow me to filter the inline view by a date that exists in the table outside of the inline view.    A simplified version that exibits the problem is below

select a.orderid, a.ocustomerid
from orders AS a
         INNER join (
SELECT b.orderid, b.ocustomerid
FROM orders AS b
WHERE b.opromisedshipdate = a.opromisedshipdate
) AS c ON c.orderid = a.orderid

Access returns an error looking for the parameter a.opromisedshipdate.

Any idea how I can achieve this filter?

In the real world query I'm doing this to provide aggregated order information (the inline view) next to non aggregated order information but I need the aggregated information to only go up to the date that is a part of the current order.

Thanks in advance.


texts:
We see records in a table that violate primary key and unique key constraints. What could have caused it? How can we track the problem (what needs to be logged/recorded to understand the situation better)?

We are surveilling over a few hundred (if not thousands) of Postgres (version 9.5.1) databases in production. Every now and then, in one database or the other, some tables get rows that violate primary key constraint.

select ctid, id

(where id has a primary key constraint) returns

(0,29);"fb2f529b-87b3-46ff-90ca-e70096c7a08a"
(0,28);"fb2f529b-87b3-46ff-90ca-e70096c7a08a"
(0,26);"fb2f529b-87b3-46ff-90ca-e70096c7a08a"
(0,24);"fb2f529b-87b3-46ff-90ca-e70096c7a08a"

deleting the rows using the ctid works nicely, while accessing the data using the id results in weird errors (due to the violated constraint).

The rows might be inserted using different concurrent sessions. The select is run in a separate database connection. The database is used heavily over JavaBeans. Backing and restoring of the content of the DB could have taken place (in weird ways).

The id have the following type/constraints

CREATE TABLE tbl1
(
  id uuid NOT NULL,
  att1 character varying(255) NOT NULL,

  CONSTRAINT pk_fesert PRIMARY KEY (id)
)

I cannot post complete tables / records as there personally identifiable information there. The particular table receives hardly ever update traffic except when exceptions happen (certain components in JEE application fail in the distributed environment).

It could be a bug in postgres, but we do not even know how to even start to track it. Among numerous installation of postgres this happens at most a few times per year. Could this be related to backup-restore? Is it possible to pause constraints in postgres in some way that are not bound to a specific transaction?
Under which circumstances can happen that multiple rows are inserted into a table that may violate constraints?

OS: Windows Server (2016), runs in a virtualization environment (VMware ESXi 6.0).

Update:

One a different occasion after a power failure we ended up with a database of inconsistent state. I am gathering the logs now, but we see is that a table, that is supposed to have a record does not have any. Inserting a record (the record that was there prior to incident) into the table throws a primary key constraint violation. There are a few dozen of foreign key constraints that are currently violated. Comments?


texts:
I have a table in PostgreSQL 11 with two varchar columns like this:

 A | B
---+---
 m | n
 x | y

Now I want to add a column C which should contain the contents of A and B (concat(A, B)):

 A | B | C
---+---+---
 m | n | mn
 x | y | xy

After that I want to drop A and B so that I effectively replace the two original columns.

How can I set the value of C to the concatenated values of A and B?


texts: Centralize Oracle RMAN scripts in one server

texts:
I want to centralize all my Oracle's DB backup shell scripts in one server, and all of them are in different servers, architectures and DB versions. For this purpose I'm going to use the same server where the Recovery Catalog is installed, a rhel6 64bit with 11.2.0.3 DB, and schedule the backup shell scripts via crontab.

This is easily done executing, from the Recovery Catalog server, something like RMAN TARGET SYS@RemoteDBNetIdentifier CATALOG catalogowner@rmanCatalog

Problem is that, according with the compatibility matrix, RMAN client version must match DB target connected version. http://docs.oracle.com/cd/E11882_01/backup.112/e10643/compat003.htm#i634479

$ rman target sys@db_prod_1 catalog rman@rcat

Recovery Manager : Release 11.2.0.3.0 - Production on Jue Ago 22 13:27:52 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Contraseña de la base de datos destino:
el paquete PL/SQL SYS.DBMS_BACKUP_RESTORE versión 11.02.00.01 de la base de datos TARGET no es actual
el paquete PL/SQL SYS.DBMS_RCVMAN versión 11.02.00.01 de la base de datos TARGET no es actual
conectado a la base de datos destino: DB_PROD_1 (DBID=943768957)
Contraseña de la base de datos del catálogo de recuperación:
conectado a la base de datos del catálogo de recuperación

RMAN> exit

But I don't want to install every single Oracle DB version in the recovery catalog server.
So I tried to copy JUST the rman executable from the Oracle DB installations in the others servers to the Recovery Catalog server, but it didn't worked

$ scp oracle@SRV_PROD_1:/usr/oracle/product/11.2.0/bin/rman /usr/oracle/product/rman_bin/11.2.0.1/
rman                                    100%   14MB 209.6KB/s   01:06

$ cd product/rman_bin/11.2.0.1/
$ ./rman target sys@db_prod_1 catalog rman@rcat

Recovery Manager : Release 11.2.0.1.0 - Production on Jue Ago 22 13:51:27 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: fallo al inicializar el paquete del gestor de recuperación interno
RMAN-03000: fallo al inicializar el componente compilador del gestor de recuperación
RMAN-06035: versión incorrecta de recover.bsq; se esperaba 11.2.0.1 y se ha encontrado 11.2.0.3

It seems that I'd also need the recover.bsq file (which is in $ORACLE_HOME/ADMIN), but I want to be sure that I wont miss more files or face new problems when we'll have to perform a disaster recovery or something in the future.

What can I do?

How can I only install the different versions of RMAN client?
There's another method to centralize all the backups work (I think that EM Cloud Control can achieve this, I didn't read about it, just figuring out, but we dont want to rely on a GUI web-based application to manage our backup strategy.)

Regards


texts: Conversion Failed From nvarchar to int even when all values in result set are int

texts:
Take the following SQL sample:

Declare @Table Table (Value int)

Insert Into @Table
Select intValueAsVarcharType
From OtherTable
Where Category = 'Type1'

The OtherTable.intValueAsVarcharType contains a list of nvarchar values, all the records for Category = Type1 are castable to ints. Values for other categories cannot all be casted to ints.

When we run the select (without the insert) we get a list of values that are all numeric ints, as expected.

When we run it with the insert we get the following error:

Conversion failed when converting the nvarchar value 'aaa' to data type int.

Another interesting thing is that if we change the where clause to the below:

Where Category = 'Type1'
and intValueAsVarcharType not like '%[^0-9]%'

The select statement returns the exact same result set, but this time the insert also works.

I can guess that this is likely caused by the way SQL optimises the query, and that it probably checks all that the values can be casted to int before it filters the result set.

What I'm not sure about is:

Why exactly does adding the second where condition allow it to work when it doesn't change the result set? It seems to have the same execution plan as the statement without it.
What is the best way to resolve this issue properly? Adding the second where clause seems to work but it feels "hacky" and I don't know if it will continue to work under all circumstances


texts: Getting multiple out when using NOT EQUAL operator

texts:
How to display name of the department managers (fname, lname) who have no dependent?

Tried this:

Select DISTINCT EMPLOYEE.Fname,EMPLOYEE.Lname
from EMPLOYEE
INNER JOIN DEPENDENT ON (EMPLOYEE.Ssn != DEPENDENT.Essn);


texts: Creating a table from the existing table along with the constraints in oracle

texts:
I have table emp and I want to create a duplicate table from the emp table. I tried the below query, which is copying all the table columns, data and the not null constraints.

create table emp_backup as select * from emp.

Is there any way to duplicate a table along with all constraints?

Here is the base table:

create table emp
(
empno number Primary key,
ename varchar2(50),
deptno number,
constraint emp_deptno_fk foreign key(deptno) references dept(deptno)
)

I need to create a table emp_backup from the emp along with emp_deptno_fk constraint.


texts:
I'm having trouble figuring out exactly how to place good boundaries for when and where to use lookup tables in a database. Most sources I've looked at say that I can never have too many but, at some point, it seems like the database would be broken down into so many pieces that, while it may be efficient, it is no longer manageable. Here's a thrown together example of what I'm working with:

Let's say I have a table called Employees:

ID  LName   FName   Gender  Position
1   Doe     John    Male    Manager
2   Doe     Jane    Female  Sales
3   Smith   John    Male    Sales

Pretend for a moment that the data is more complex and contains hundreds of rows. The most obvious thing I see that could be moved to a lookup table would be Position. I could create a table called Positions and stick the foreign keys from the Positions table into the Employees table in the Position column.

ID  Position
1   Manager
2   Sales

But how far can I continue to break the information down into smaller lookup tables before it becomes unmanageable? I could create a Gender table and have a 1 correspond to Male and a 2 correspond to Female in a separate lookup table. I could even put LNames and FNames into tables. All "John" entries are replaced with a foreign key of 1 that points to the FName table that says an ID of 1 corresponds to John. If you go down this rabbit hole too far like this, though, your Employees table is then reduced to a mess of foreign keys:

ID  LName   FName   Gender  Position
1   1       1       1       1
2   1       2       2       2
3   2       1       1       2

While this might or might not be more efficient for a server to process, this is certainly unreadable to a normal person who may be trying to maintain it and makes it more difficult for an application developer trying to access it. So, my real question is how far is too far? Are there "best practices" for this sort of thing or a good set of guidelines somewhere? I can't find any information online that really nails down a good, useable set of guidelines for this particular issue I'm having. Database design is old hat to me but GOOD database design is very new so overly technical answers may be over my head. Any help would be appreciated!


texts: Disable Binary Logging During MySQL Dump

texts:
I do apologize if this has been asked before but my search skills were not able to turn up a definitive answer.

SITUATION:
I have two Windows Server 2012 R2 servers running WAMP 3.06 (which contains MySQL 5.7.14).  They are setup for replication with a master-slave configuration using binary logging.  I have a PowerShell script that makes a dump of the database each night.  The PowerShell code looks like this...

# Variables
. (Join-Path $PSSCriptRoot MySQL_Vars.ps1)

# Backup the database
cmd /C " `"$mysqlDump`" $mysqlRoutineFlags -u $mysqlUser -p`"$mysqlPass`" $mysqlDB > $mysqlBkp"

QUESTION:
How could I do this so that when the MySQL dump occurs, it is not written to the binary logs?

Thank you in advance for your help!


texts: Why can't I drop this Oracle datafile - even though it seems empty?

texts:
This system is Oracle 11.2.0.3.0, using ASM.  We had an issue where there seemed to be a corruption issue of some kind - every time a row tried to get inserted into the table, we'd get the following:

ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 23: '<filepath>'
ORA-15081: failed to submit an I/O operation to a disk

Luckily, this is a test system and there happened to be only one table on data file 23, according to dba_extents.  I had the team drop the table and recreate it in another tablespace for the moment.

However, now I'd like to drop the datafile that was reporting the issue.
select count(*) from Dba_Extents where file_id = 23; reports 0 - so there seem to be no extents using that data file.  However, ALTER TABLESPACE tablespaceName DROP DATAFILE '<filepath>'; doesn't work.  I get the following:

Error report -
SQL Error: ORA-03262: the file is non-empty
03262. 00000 -  "the file is non-empty"
*Cause:    Trying to drop a non-empty datafile
*Action:   Cannot drop a non empty datafile

How can I drop this file?


texts: How to import data into Oracle 11g r2 daily

texts:
I need to batch load several log data files (comma delimited but they don't have a .csv extension in the file name) once each day into an Oracle database. I'm sure this is a very common use case, but I can't seem to find a good solution.

Using an external table or sql loader seems to be the way to go, but in my case the filename is not fixed. So, I need to get a list of all the files in the directors and then import them dynamically. I'm guessing some sort of PL/SQL might be required or is there a easier way to do this. Can someone help me out with a solution to this?


texts:
Good morning all
I must migrate this ssis package from SQL 2008 to SQL 2019
I'm trying to see how I can find the physical path of these packages
thanks


texts: How can I use with differential backup of SQL Server

texts:
It's said differential backup could be restored only after it's base. It sounds like I just divide the one step full restore into two steps.

And I can't see the benefit here.

How can i use with differential backup of SQL Server?

BTW, I'm looking for a solution to restore my data from the primary database to the standby one only with the differential data everyday. It's quite appreciated for any suggestion.


texts:
Based on the PostgreSQL Documentation it seems possible to use VALUES list as argument in Insert/Select/Update.
Is it possible to use it as argument in DELETE something like:
delete from some_table where (col1, col2) in values(......);
??


texts: Why did the MySQL Storage Engine Change on Restore in one scenario?

texts:
In my production database life, there was one tragic incident when the MySQL engine type was accidentally changed from InnoDB to MyISAM on restoring a recent backup. The result was that all referential integrity were lost.

What could have caused this incident? My production database is MySQL Server 5.1.


texts:
I am working on a data governance enhancement at my work.  My current task is to use ssms classify data feature.  However, I want to customize the "sensitivity level" for my work (government) we have hipaa, cjis, NFIRS, NFPA, CISSP commercial classification and a few others related to the legal process.  Does anyone know how to customize the list?

Currently,  SSMS has: public, confidential, confidential - GDPR, highly confidential, highly confidential - GDPR, and [n/a].

Thanks for your help


texts:
We are using DBCC CHECKDB on a VLDB 1TB in size.  Previously when on our 2008 platform the check ran successfully on a drive that was 150GB in size for TempDB, but since moving to SQL 2012 and AlwaysOn the check is now running out of space on a 200GB drive.

The DBCC CHECKDB with ESTIMATEONLY is returning a value of 1, so I think we are suffering from what some have said is a bug in that command.

Does CHECKDB work differently between SQL versions?

P.S. I know a work around is to break up the check into batches but we are wanting to know why this has become an issue now.


texts: Extended Event doesn't capture every rpc_completed

texts:
I have a long running Extended Event session that captures rpc_completed events filtered for a few procs. It has found some that have run. But others it doesn't capture them when I know they have been executed. The reason I know this is because I look in the cache and find them called there.

I'm using SQL Server 2016. I'm filtering on object_name as the field name, equal_i_sql_unicode_string as the operator and the value minus the schema name. I've also modified the session to add procs that I know are used regularly and it captures those fine.

Why does it overlook and not capture some?


texts: Separation of Duties - Can a DBA patch or Upgrade a SQL Server 2008 R2 DB without TDE Encryption keys?

texts: Are there any considerations I should have in mind when I patch or upgrade a TDE encrypted database? In the context of separation of duties, does the person executing the patch or upgrade process need access to the Keys or certificates of a TDE encrypted database or can the database be patched without the keys. The answer is important because we are a legal shop and would like an application administrator to encrypt the database and outsource the db maintenance functions to a dba to patch and upgrade the database without having access to the “application data”. Could you throw some light on this please?


texts: Estimate transaction log size for single transaction

texts:
Today I have tried to execute an update operation on 50M+ rows, something like:

UPDATE [table] SET [Column] += 1;

after 30min of waiting I have seen that the log has started to filling itself, after another 30min the entire transaction rolled-back due to the log being full.
DB was in the simple recovery mode but this is meaningless as this was done as a single transaction. SQL Server on it's own couldn't predict that it will use the entire log size (lets say that it was locked size for this matter), but my question to you my dear experienced DBA's - could you predict that size? Is there any way to calculate such an estimation?

  UPDATE:

Yes, I understand the process, I know I can batch, I know how to make it performing well also. My real question is how big the log size is going to be?
How would you calculate update on a simple table like:

CREATE TABLE [TABL] ([ID] int identity(1,1) PRIMARY KEY CLUSTERED, [X] int not null);

INSERT INTO [TABL] VALUES(1)
GO 1000

UPDATE [TABL]
SET [X] = 2;

On my test box, the update part of the operation was 6 pages large = 48KB.
When I repeated the same for 10k rows I received 144 pages of transaction log.
For 100k rows it was 1314 pages, for 1M it was 13970 pages. This shows that we can concider this as linear function (on the we have extra pages which has to be there anyway no matter did we update anything or not --> 2-3 pages).

Going back to the begeining I know that I could run a single batch on 5% of my total operation, check the page chenge content (as blogged by Paul Randal):

    DECLARE @Extent_ID              INT;
    DECLARE @Size_Total             BIGINT = 0;
    DECLARE @File_ID                INT;
    DECLARE @File_Size_Pages        INT;

    DECLARE @Log_Page_ID            INT;
    DECLARE @Log_Total              BIGINT = 0;
    DECLARE @Log_Total_Changed      BIGINT;
    DECLARE @DBCC_PAGE_String_Log   VARCHAR (200);

    DECLARE [files] CURSOR FOR

        SELECT [file_id], [size]
        FROM master.sys.master_files
        WHERE [type_desc] = N'ROWS'
        AND [state_desc] = N'ONLINE'
        AND [database_id] = DB_ID(@DB_Name);

    OPEN files;

    FETCH NEXT FROM [files] INTO @File_ID, @File_Size_Pages;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Extent_ID = 0;
        SET @Size_Total += @File_Size_Pages / 8;    --| Convert size in pages to to extents

        WHILE (@Extent_ID < @File_Size_Pages)
        BEGIN

             SELECT @Log_Page_ID = @Extent_ID + 7;

             SELECT @DBCC_PAGE_String_Log = 'DBCC PAGE ([' + @DB_Name + '], ' + CAST (@File_ID AS VARCHAR) + ', ' + CAST (@Log_Page_ID AS VARCHAR) + ', 3) WITH TABLERESULTS, NO_INFOMSGS';

             TRUNCATE TABLE [msdb].[dbo].[DBCC_Page_Check];
             INSERT INTO [msdb].[dbo].[DBCC_Page_Check] EXEC (@DBCC_PAGE_String_Log);

             SELECT @Log_Total_Changed = SUM ([msdb].[dbo].[Extent_Change_Checks] ([Field]))
             FROM [msdb].[dbo].[DBCC_Page_Check]
             WHERE [VALUE] = '    MIN_LOGGED'
             AND [ParentObject] LIKE 'ML_MAP%';

             SET @Log_Total += @Log_Total_Changed;

             SET @Extent_ID += 511232;
      END
----------------------------------------------------------------------------------------------------------------------------

        FETCH NEXT FROM [files] INTO @File_ID, @File_Size_Pages;
    END;

    DROP TABLE [msdb].[dbo].[DBCC_Page_Check];
    CLOSE [files];
    DEALLOCATE [files];

but this still requires to make a change (even with rollback) before we could estimate anything at all. So again - is there anyone who knows how to estimate it better than the way above - up front?


texts:
According to the oracle docs, during shutdown, 3 steps are performed:

Close the database.
Unmount the database.
Shut down the instance.

Is it possible to make only first 2 steps?


texts: Extended properties are not copied using SMO

texts:
I'm using the following code to copy a database from server to localhost using SMO. The code works well except the fact the extended properties are not copied.
    //Set Source SQL Server Instance Information
    Server sourceServer = new Server(sourceServerName);

    //Set Destination SQL Server Instance Information
    Server destinationServer = new Server(destinationServerName);

    try
    {
        //Using windows authentication
        sourceServer.ConnectionContext.LoginSecure = true;
        #region Optional - Using SQL Server authentication
        #endregion\
        sourceServer.ConnectionContext.Connect();

        //Using windows authentication
        destinationServer.ConnectionContext.LoginSecure = true;
        #region Optional - Using SQL Server authentication
        #endregion
        destinationServer.ConnectionContext.Connect();

        InitServer(sourceServer);
        InitServer(destinationServer);

        //Set Source Database Name [Database to Copy]
        Microsoft.SqlServer.Management.Smo.Database databaseSource = sourceServer.Databases[databaseName];
        //Set Destination Database Name
        Microsoft.SqlServer.Management.Smo.Database databaseDestination = new Microsoft.SqlServer.Management.Smo.Database(destinationServer, databaseName);

        databaseDestination.Create();

        //Set Transfer Class Source Database
        Transfer transferDatabase = new Transfer(databaseSource);

        //transferDatabase.CopyAllUsers = true;
        transferDatabase.CopyAllObjects = false;
        transferDatabase.CopyAllSchemas = true;
        transferDatabase.CopyAllUserDefinedDataTypes = true;
        transferDatabase.CopyAllTables = true;
        transferDatabase.CopyData = true;
        transferDatabase.CopyAllStoredProcedures = true;
        transferDatabase.DestinationServer = destinationServer.Name;
        transferDatabase.DestinationDatabase = databaseDestination.Name;
        transferDatabase.TemporaryPackageDirectory = @"C:\DATA\";

        transferDatabase.Options.WithDependencies = true;
        transferDatabase.Options.DriAll = true;
        transferDatabase.DropDestinationObjectsFirst = true;
        transferDatabase.Options.ContinueScriptingOnError = true;

        transferDatabase.TransferData();

    }
    catch (Exception ex)
    {
        log.Error(ex.Message);
    }
    finally
    {
        if (sourceServer.ConnectionContext.IsOpen)
        {
            sourceServer.ConnectionContext.Disconnect();
        }

        if (destinationServer.ConnectionContext.IsOpen)
        {
            destinationServer.ConnectionContext.Disconnect();
        }
    }


texts: SQL Server Express in a shared hosting environment

texts:
I have created an ASP.NET project in Visual Studio. The database is a SQL Server Express database TestDB.mdf. I uploaded this with .ldf file to the standard App_Data directory but the connection string doesn't seem to work. How can I use this SQL Server Express database in a shared hosting environment?

My Connection String is:

Data Source=.\\SQLEXPRESS;
Initial Catalog=C:\\inetpub\\dir1\\dir2\\docs\\App_Data\\Test.mdf;
Integrated Security=SSPI;
Connect Timeout=120";

It gives always the same error:

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)


texts: Two Inner join on the same field to get same data

texts:
I'm developing a Report in SSRS for MS CRM 2011.

There is a table named StringMapBase which contains Option Set we have declared in different entities.
This table has Value and AttributeValue column.
Every option Set I have created in any entity, has the Value(which is the name of that) and AttributeValue(which is the name I have choosed for that option set) in the StringMapBase table.
If I named an Option Set, I should read the the name from StringMapBase.
Like this :

    select
SalesOrder.new_Order_Code,
orderreceiptstep.new_NextFollow,
orderreceiptstep.new_realization,
StringMapBase.Value,
orderreceiptstep.AccountIdName,
orderreceiptstep.TotalReceivableAmount,
orderreceiptstep.name,

orderreceiptstep.DueDate,
orderreceiptstep.ReceiptStepTypeIdName,
StringMapBase.Value,

from orderreceiptstep
inner join StringMapBase on (orderreceiptstep.OrderReceiptStepStatus=StringMapBase.AttributeValue and StringMapBase.LangId=1065)

It gives me the name of one Options Set.
Now, The problem is that I have another Option set on the same entity that should get the name of that.
It means I need another Inner Join Like above,  Like this :

    inner join StringMapBase on (orderreceiptstep.OrderReceiptStepPrioriy=StringMapBase.AttributeValue and StringMapBase.LangId=1065)

In the first  I Used orderreceiptstep.OrderReceiptStepStatus of orderreceiptstep table and in the second I used orderreceiptstep.OrderReceiptStepPrioriy in the Inner Join clause.

I want to Have These two joins on the same field, Like this :

        select
SalesOrder.new_Order_Code,
orderreceiptstep.new_NextFollow,
orderreceiptstep.new_realization,
StringMapBase.Value,
orderreceiptstep.AccountIdName,
orderreceiptstep.TotalReceivableAmount,
orderreceiptstep.name,

orderreceiptstep.DueDate,
orderreceiptstep.ReceiptStepTypeIdName,
StringMapBase.Value,

from orderreceiptstep
inner join StringMapBase on (orderreceiptstep.OrderReceiptStepStatus=StringMapBase.AttributeValue and StringMapBase.LangId=1065)
    inner join StringMapBase on (orderreceiptstep.OrderReceiptStepPrioriy=StringMapBase.AttributeValue and StringMapBase.LangId=1065)

This means StringMapValue in the first Should return something, and in the second Inner Join must return another thing.
How Can I do that?


texts: Select N preceding rows when column match is true


texts: PostgreSQL pg_dump custom file input not dumping data

texts:
When I'm using custom format (-Fc) in pg_dump, the file is smaller (42kb), and when I restore it, it only restores the tables and columns, not the data.

But when I try to use plain text (-Fp), the file is larger (73kb), and when I try to restore it, all data are included (table, column, rows, etc).

Also, when I try to use a Database Manager (dBeaver to be exact), and I try to backup, I can see the command, and it's the same command that I use in pg_dump. But the dump file is larger (73kb) and when I tried to restore using that dump, all data are included.

Any ideas on what seems to be the problem? As of now, I'm using plain-text as the format, but I want to use the custom one.

I'm using PostgreSQL 11.2.

This is full command that I was using in pg_dump. And this is the exact same command that dBeaver is using to dump the database.

pg_dump.exe --format=c -n public --verbose --host=localhost --port=5432 --username=postgres -f db.backup postgres_db