texts:
I have inherited a Windows 7 Professional pc with an existing instance of SQL Express 2008. I have local admin rights on the machine. I do not know the SA password. I am unable to create databases or logins, or modify the SA login's password. I would like to grant my domain account SYSAdmin privileges.

I have tried the following, in both situations I right clicked and ran SQL management studio as Administrator, and used Windows authentication to log in to the local SQL express.

I created a local admin account, with administrator privileges and logged in to my PC as that.
I have logged to my PC as a Domain Admin.

I understand that if I log in as an administrator using windows authentication I should have full privileges, what can can be stopping me from getting this access?


texts: Set in dynamic query value of variable declared outside dynamic query

texts:
I have a stored procedure which i simplified this way :

DECLARE @variable1 INT

DECLARE @SQL VARCHAR(MAX)

SET @SQL = '
    DECLARE @variable2 INT

    SET @variable2 = 1
    SET '+CAST(@variable1 AS VARCHAR)+' = @variable2

    SELECT @variable1 as V1, @variable2 as V2
'
EXEC(@SQL)

But this script don't give me anything ! I'm pretty sure it's something related to scope. The thing is that i need to declare the variable outside the dynamic query.

thanks for help !

EDIT :

WHILE LOOP UNTIL SELECT COUNT xxx = 0
BEGIN
    DECLARE @variable1 INT

    DECLARE @SQL VARCHAR(MAX)

    SET @SQL = '
                EXEC STORE PROC WITH PARAMETER @Param1 = @variable1 (first loop @Param1 is null)

                STORE PROC RETURN A VALUE

                SET @variable1 with return value of store proc
                and use it in second loop, third loop...
    '
    EXEC(@SQL)
END


texts: Efficient structure for querying membership in many-to-many relationship where tableA has ONLY the indicated members from tableB?

texts:
I'm working on an application with a PostgreSQL back end. There is a many-to-many relationship between items in one table and tags in another table. The specs require that the application be able to respond to frequent queries that provide a list of tags as parameters and expect in response a list of all items which contain only those tags but not necessarily all of those tags.

Is there an efficient (non-exponential complexity) way to structure my database (or application) to suit this task? At launch, I shall have on the order of 12K items and 1K tags.

E.g.
A user requests a list of all items for tags a, b, and c.
The response contains items with the following tag sets:

{}
{a}
{a,b,c}

The response must not contain items with the following tag sets:

{d}
{a,b,c,d}

The structure of tags table is as follows:

CREATE TABLE tags (
    id integer NOT NULL,
    name text NOT NULL,
    description text,
    revision_id integer
);


texts:
I am doing large INSERT into a local MySQL database

I am at 2,000,000 rows and am really starting to notice the slowdown now. I always heard that MySQL wasn't very scalable

I expect this database to get larger and I have a lot more to insert

would using an Amazon micro instance have any benefit here?

I don't foresee my upload speed being a bottleneck, so my logic is that their distributed processing will make the database quicker.

This turned out to be more of a CPU utilization issue but would amazon's solution be better, perhaps more optimized for its more available processors? With this logic a more expensive instance would have better performance

but with so many variables involved here, does anyone have experience with this to say?


texts: Is there some plugin that would reconnect query tabs to original DB's in SSMS?

texts:
It's a minor thing, but annoying one.

I'm currently working on about 5 different databases, and every time I restore db's from snapshots or run my restore from backup scripts, all of the queries get disconnected.
Sure, I can just rerun them, but the db is set to 'master' by default (yes, writing use mydbx at the beginning of query is useful, but often forgotten, and I usually have two pages of various scripts in each).

Is there some plugin out there that would remember which query was on which db, and reconnect automatically from time to time?


texts: How do you generate a build script for all views

texts:
I would like to create a build script that can run through and recreate all views (and stored procedures too, but one step at a time).

I am trying to create a baseline of all of our user databases (around 50 of them) for our developers to run on their local machines, basically recreate all of the databases from the ground up. I have gotten past all the preliminary steps of recreating all dbs, schemas, roles, tables, indexes, constraints,...yada yada. I have relied heavily on the sp_foreachdb function and it works like a charm. Now I am stuck on stored scripts, like views and procs mostly due to the length of some of the scripts.

Reason I need to recreate the entire database setup is because people are dumb and left cross database joins and dependencies in legacy products for us. (yay!) So how can we expect our developers to pull the database down to their local machines for development and testing if first they need stuff from 3-4 other databases. And it doesn't end there, those databases depend on other things to exist. Ugh, circular dependencies.

If I only had one database I would hook up my handy dandy RedGate tool SQLCompare and pull the stuff into a script that they could run. Problem is the number of user databases we have and the desire to automate it.

If anyone can offer some advice that could help me recreate ALL script objects on ALL Databases, I would gladly accept it. The thought of manually scripting out a single database's scripts 50+ times is daunting to say the least. What am I not thinking of?

Thanks Friends!


texts: How to just keep latest records in SQL server?

texts:
Here is my case,

Table name: EMP_Detail
Records:
ID          Name     Statue     ChangeDate
1111        Apple    Active     2017-01-01
1111        Apple    Active     2017-03-07
1111        Apple    Disabled   2017-04-06
1111        Apple    Active     2017-05-01
2222        Bell     Active     2017-02-05
2222        Bell     Active     2018-01-05
3333        Cisco    Active     2017-03-08

Trying to remove the active and out of date records and keep table like this:

ID          Name     Statue     ChangeDate
1111        Apple    Disabled   2017-04-06
1111        Apple    Active     2017-05-01
2222        Bell     Active     2018-01-05
3333        Cisco    Active     2017-03-08

Thanks a lot!


texts: Give every user 3 default 'bookmark' entries

texts:
I've got a table users and a table bookmarks. The table bookmarks obviously contains pages the user wants easy access too.

But, there's a new requirement now: every user gets 3 unmutable bookmarks. One of the bookmarks (be it an unmutable one or a user-specific one) is marked as homepage. Always.

I'm thinking about 2 solutions, but I'm not sure which one to choose:

1) Add those 3 records for every user and use triggers or application logic to enter those 3 records automatically when a new user is created.

2) Keep those 3 'records' out of the database altogether and fix it in the application (on the 'bookmarks' page). This solution has a great disadvantage: by default, one of the 3 unmutable bookmarks is selected as the user's homepage. The user can change their homepage to one of the other 2 unmutable bookmarks, or a bookmark of their own. I would have no place to store this data, or I would have to insert the 3 unmutables in case the user makes a change. But that sounds hackish.

What would you do? Is inserting 3 records for all users a design flaw?


texts:
I wish to assign a value in my row which should be based on the country as:

Only US: Should be assigned USA
only Other countries: Should be
    assigned International
US + Other countries: Should be assigned
    Global

So for the below table:

    DECLARE @A TABLE
    (
    PolicyID int,
    PolicylocationID int,
    Country VARCHAR(100)
    )
    INSERT INTO @A
    (
    PolicyID,PolicylocationID,Country
    )
    VALUES
    (1,1,'US'),
    (1,2,'US'),
    (1,3,'US'),
    (1,4,'US'),
    (2,1,'NA'),
    (2,2,'PA'),
    (2,3,'CA'),
    (2,4,'ZA'),
    (3,1,'US'),
    (3,2,'NA'),
    --(3,3,'PA'),
    (4,1,'US'),
    --(4,2,'US'),
    (4,3,'US'),
    (5,1,'US'),
    --(5,2,'NA'),
    (5,3,'PA'),
    (6,1,'NA'),
    --(6,2,'PA'),
    (6,3,'CA')

    Select * into #temp
    from @A
Select * from #temp

the output should be:

DECLARE @A TABLE
(
PolicyID int,
PolicylocationID int,
Country VARCHAR(100),
CType VARCHAR(100)
)
INSERT INTO @A
(
PolicyID,PolicylocationID,Country,CType
)
VALUES
(1,1,'US','US'),
(1,2,'US','US'),
(1,3,'US','US'),
(1,4,'US','US'),
(2,1,'NA','Internationl'),
(2,2,'PA','Internationl'),
(2,3,'CA','Internationl'),
(2,4,'ZA','Internationl'),
(3,1,'US','Global'),
(3,2,'NA','Global'),
--(3,3,'PA','Global'),
(4,1,'US','US'),
--(4,2,'US','US'),
(4,3,'US','US'),
(5,1,'US','Global'),
--(5,2,'NA','Global'),
(5,3,'PA','Global'),
(6,1,'NA','Internationl'),
--(6,2,'PA','Internationl'),
(6,3,'CA','Internationl')

SElect * into #temp
from @A

Any ideas on how to achieve this?


texts: DTS error when a parameter is added to an Execute SQL Task

texts:
I have an Execute SQL Task that is (hopefully) going to receive a global variable called startDate, which is set to a Date type. A shortened version of the code looks like this:

DECLARE @startDate DATETIME,
        @endDate DATETIME

SET @startDate = ?
SET @endDate = DATEADD(DAY, 6, @startDate)

INSERT INTO destinationTable
SELECT  *
FROM    sourceTable
WHERE   startDate = @startDate
AND     endDate = @endDate

When I replace the parameter with a date (e.g. '08-01-2012'), the statement parses correctly; however, as soon as the parameter is added back in, the statement errs.

I use DTS very rarely, so I'm basing this code entirely on Microsoft's Using Parameterized Queries. Can anyone enlighten me on how to correctly use a parameter in this situation?

Edit:
The error detail reads "Syntax error or access violation" which, considering the circumstances, I assume to read as a syntax error due to the addition of the "?".


texts: 0x80041008 error when trying to change Log On account on SQL Server services

texts:
I'm trying to do a "Mirroring" homework in SQL Server, but i get 14148 Error when trying to start mirroring. According to some videos this problem can be solved changing SQL Server services account to the same account for both instances. But when i try i receive this error
[Invalid Parameter 0x80041008]

What should i do?


texts: Explanation for important reserved space

texts:
I am analysing a database

I didn't understand why reserved space is more important then used space ?

Any explication ? can I get 400 GO ?


texts: Model data with many to many and overrides

texts:
I'm having some trouble modeling the database for this particular scenario in Postgres:

We have a set of toys that contain several pieces. Each piece is associated with one or more materials. All of these relationships are many to many, because a piece can be placed in many different toys, and a material can be associated with many different pieces.

Here's where I lack the knowledge on implementation. There's another many to many relationship, where a toy can be made by many factories, and a factory can manufacture many types of toys. In the general case, for a particular toy, we want to know what pieces it has, and which materials are associated with those pieces. However, there's a case where the material can be different for a given piece depending on the factory the toy is made in.

In short, there's a "default" case where we know that, for a given toy, we have a set of pieces, and for each of those pieces, we have a set of materials. But, for an arbitrary factory, the material associated with a piece is different.

The furthest I've gotten is putting this information in the piece_material junction table:

| piece_id  | material_id  | factory_id |
|-----------|--------------|------------|
| 1         | 1            | <null>     |
| 1         | 2            | <null>     |
| 1         | 3            | 1          |

But this obviosuly doesn't work because I don't have information indicating which material is being replaced in a particular factory. I'm looking for a query that will get associated materials with a piece, and only the overrides if there are some present (i.e. in the last row of the above example, if it's overriding material 2, that would only get materials 1 and 3 back).


texts: How to open a sharded MongoDB (without replica set) environment on my dev machine?

texts:
I have only found this tutorial here which seems to be most recent, other tutorials it seems are outdated and don't work anymore.

I want to create a sharded, non-replicated cluster on my dev machine and I couldn't find how to do it in Mongo 3.4


texts: Can't restore master/slave replication from Percona backup (slave server)

texts:
A slave is restored from a master backup created with Percona xtrabackup
On the master binlog is set to expire in 14 days and this backup is less than 24 hours old
my.cnf (master)
expire_logs_days   = 14

restore script (slave)
service mysql stop

rm -r /var/lib/mysql/*

# backup is unzipped into /var/lib/mysql
xtrabackup --prepare --target-dir=/var/lib/mysql
chown -R mysql.mysql /var/lib/mysql

service mysql start

# get gtid
line=$(head -n 1 "/var/lib/mysql/xtrabackup_binlog_info")
gtid=$(echo $line | awk -F '[ ,]' '{print $3}')

mysql -u root -pPASS -e "reset master; SET GLOBAL gtid_purged='$gtid'; CHANGE MASTER TO MASTER_HOST='$master_host', MASTER_USER='repl', MASTER_PASSWORD='$master_pass', MASTER_AUTO_POSITION=1; start slave"

mysql -u root -pPASS -e "show slave status\G;"

Status
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: server.com
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: rep-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: mydb
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 0
              Relay_Log_Space: 154
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. The GTID sets and the missing purged transactions are too long to print in this message. For more information, please see the master's error log or the manual for GTID_SUBTRACT.'
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: fe51e8df-b7c4-11e9-be21-4061862b8d34
             Master_Info_File: /mnt/HC_Volume_8355755/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 210211 10:50:32
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: 3ccc2392-45ed-11e7-bc98-4061862b8d34:1-35942477,
94bb04d3-6c4e-11eb-8631-96000080ccbb:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:

/var/lib/mysql/xtrabackup_binlog_info
mysql-bin.000025    217141189   3ccc2392-45ed-11e7-bc98-4061862b8d34:1-35942477,
fe51e8df-b7c4-11e9-be21-4061862b8d34:1-25059775

The gtid value extracted from the file: 3ccc2392-45ed-11e7-bc98-4061862b8d34:1-35942477
update
Have also tried to fetch the second uid in /var/lib/mysql/xtrabackup_binlog_info by doing
gtid=$(tail -n 1 "$mysql_dir/xtrabackup_binlog_info")

Nothing works.. Same error


texts: How to Insert a static value into a column in MS Access during a query

texts:
I am currently switching data sources for one of my databases, now the old data used to include a column called 'Counter' every row had a 1 in it, this 1 was then used in excel formulas and summary pages as well as pivot tables, So that I don't have to go through the process of finding all these formulas and fixing them I would like for MS Access to insert a '1' Value into this column when the append is completed however I don't know how to go about it, or if Access can do it.

Help appreciated.


texts: Data mismatch in View2 [SELECT * from View1] with View1, how to fix?

texts:
The problem I encounter can be oversimplied as
CREATE View#2 AS SELECT * FROM View#1;
SELECT * FROM View#2;
SELECT * FROM View#1;

Then View#1 and View#2 return different results. This challenges logic but it's not a logic issue. Both View#1 and View#2 are created years ago in a years-old production database.
A simple test in the same schema,
CREATE View#3 AS SELECT * FROM View#1;
SELECT * FROM View#3;
SELECT * FROM View#1;

can not repilcate the problem.
It's a production database. I haven't fix by replacing the view. Just in the hope that I can get help to look further into the cause and verify the integerity of the database at some level. (maybe inspecting stuff in INFORMATION_SCHEMA etc. I am totaly new to such issue and haven't used Postgres for years due to project change. Some checklist points will be appreciated)
--- prev post with detail -----------------------------
For the details, I think the following session speak for all.
-- trying to audit utlog weighed stat
with t as (
select '2020-07-01 00:00:00'::timestamp t0, '2020--07-02 0:0:0'::timestamp t1
)
--select * from t;
select *
-- from utlog.cache_stats_per_shift_per_reason_weighed_stats
-- from utlog.stats_per_shift_filtered_per_reason
from utlog.stats_per_shift_filtered    (#View2)
-- from utlog.stats_per_shift_filtered_b0206 (#View1)
-- from utlog.stats_per_shift
cross join t
where wline = 'F02'  and wts >= t.t0 and wts < t.t1 and wsft ='D'
-- limit 100 (the above condition returns only 5 results)
;

The Result for #View2
wts                 | wsft | wspan  | wstate | wline | rcodes
--------------------+------+--------+--------+-------+-------
2020-07-01 08:00:00 | D    |      0 | S00    | F02   | {PDCB}
2020-07-01 09:50:01 | D    | 12.533 | S00    | F02   | {PDCB}
2020-07-01 11:35:46 | D    | 12.217 | S00    | F02   | {CDSO}
2020-07-01 13:22:58 | D    |   5.15 | S00    | F02   | {PDCB}
2020-07-01 14:57:38 | D    |    6.8 | S00    | F02   | {PDCB}

INDEX | COLUMN_NAME | DATA_TYPE
------+-------------+------------
1     | wts         | timestamptz
3     | wsft        | varchar
4     | wspan       | float8
5     | wstate      | varchar
6     | wline       | varchar
7     | rcodes      | text[]

Same query, the Result for #View1
wts                 | wsft | wspan | wstate | wline | rcodes
--------------------+------+-------+--------+-------+-------
2020-07-01 08:00:00 | D    |     5 | S00    | F02   | {PDCB}
2020-07-01 09:50:01 | D    |    13 | S00    | F02   | {PDCB}
2020-07-01 11:35:46 | D    |    12 | S00    | F02   | {CDSO}
2020-07-01 13:22:58 | D    |     5 | S00    | F02   | {PDCB}
2020-07-01 14:57:38 | D    |     7 | S00    | F02   | {PDCB}

INDEX | COLUMN_NAME | DATA_TYPE
------+-------------+------------
1     | wts         | timestamptz
3     | wsft        | varchar
4     | wspan       | float8
5     | wstate      | varchar
6     | wline       | varchar
7     | rcodes      | varchar[]

Reuslts in wspan column is inaccurate while both type are float8. Most weird thing is the 5 to 0 change. for Row 1.
Definition of View2 (SQLWorkbench/J generated schema):
(The _b0206 is just a version of stats_per_shift_filtered from past revisions. The original CREATE statement must be AS SELECT * FROM ...._b0206 )
CREATE OR REPLACE VIEW utlog.stats_per_shift_filtered (#View2)
(
wts,
wdate,
wsft,
wspan,
wstate,
wline,
rcodes
)
AS
SELECT stats_per_shift_filtered_u0206.wts,
    stats_per_shift_filtered_u0206.wsft::character varying AS wsft,
    stats_per_shift_filtered_u0206.wspan,
    stats_per_shift_filtered_u0206.wstate,
    stats_per_shift_filtered_u0206.wline,
    stats_per_shift_filtered_u0206.rcodes
FROM utlog.stats_per_shift_filtered_u0206;  (as #View1 in this post)


texts: Optimization for query n increase performance

texts:
I am executing the following query:

select count(inspection_result) from table where (inspection_time between "2015-06-28" and "2015-07-04") and inspection_result= 1 and part_type = "39000048" and location = "1"
union all
select count(inspection_result) from table where (inspection_time between "2015-06-28" and "2015-07-04") and inspection_result= 0 and part_type = "39000048" and location = "1"
union all
select count(defect_1_result) from table where (inspection_time between "2015-06-28" and "2015-07-04") and defect_1_result= 0 and part_type = "39000048" and location = "1"
union all
select count(defect_2_value_1_result) from table where (inspection_time between "2015-06-28" and "2015-07-04") and defect_2_value_1_result= 0 and part_type = "39000048" and location = "1"
union all
select count(defect_2_value_2_result) from table where (inspection_time between "2015-06-28" and "2015-07-04") and defect_2_value_2_result= 0 and part_type = "39000048" and location = "1"
union all
select count(defect_3_result) from table where (inspection_time between "2015-06-28" and "2015-07-04") and defect_3_result= 0 and part_type = "39000048" and location = "1"
union all
select count(defect_4_value_1_result) table where (inspection_time between "2015-06-28" and "2015-07-04") and defect_4_value_1_result= 0 and part_type = "39000048" and location = "1"
union all
select count(defect_4_value_2_result) from table where (inspection_time between "2015-06-28" and "2015-07-04") and defect_4_value_2_result= 0 and part_type = "39000048" and location = "1"
union all
select count(defect_5_result) from tabele where (inspection_time between "2015-06-28" and "2015-07-04") and defect_5_result= 0 and part_type = "39000048" and location = "1"

I have done partitions according to the start of month, that is, there are 12 partitions and the columns which are present in the column are indexed.

I have presently 1 million rows of data, each row is 128 bytes.
I need to optimize the query and increase the performance. Can anyone help?


texts: How can a datapump export have its resource usage limited on windows?

texts:
Doing an export of a number of users causes high CPU and I/O usage. I can mitigate the effects of the CPU usage using Resource Manager and setting the export session to a low priority group, but how can I limit the disk I/O? I am not running the operation in parallel and have already read Oracle's Datapump Performance documentation. Is there anything else I can do?

This is on Windows 2008 R2 x64.


texts: Why are query results shortened in pgAdmin 1.18.1?

texts:
I've recently installed pgAdmin 1.18.1 and noticed a strange thing:

Long json query results are shortened to 256 symbols and then ' (...)' is added.

Could someone help me disable this shortening?


texts:
I've heard that some queries like DELETE, or changing the structure of a table can decrease the speed of the search in a table.

First: is this true , or not?

Second: is there any way to increase it's speed again? (sth like Defragment maybe!)


texts: Installing Postgresql parray_gin extension on mac, include not found

texts:
I am trying to install parray_gin in PostgreSQL 10 on macOS, but I can't get it to build.

I'm running pgxn install parray_gin. This first gave me a missing stdio.h error, so I installed the xcode command line tools, and now it's complaining about

/Library/PostgreSQL/10/include/postgresql/server/utils/pg_locale.h:19:10:
fatal error: 'unicode/ucol.h' file not found

After some Googling it appeared I needed to install an extra library, which I did through brew install icu4c

This printed:

This formula is keg-only, which means it was not symlinked into /usr/local,
because macOS provides libicucore.dylib (but nothing else).

If you need to have this software first in your PATH run:
    echo 'export PATH="/usr/local/opt/icu4c/bin:$PATH"' >> ~/.bash_profile
    echo 'export PATH="/usr/local/opt/icu4c/sbin:$PATH"' >> ~/.bash_profile

For compilers to find this software you may need to set:
    LDFLAGS:  -L/usr/local/opt/icu4c/lib
    CPPFLAGS: -I/usr/local/opt/icu4c/include

But it didn't change a thing and I have no idea how to pass /usr/local/opt/icu4c/include to pgxn


texts: 3 additional varchar(32) keys and also updating them -- too bad for performance?

texts:
I have a table in mind which basicaly would look like this:

table1
--------
id      bigserial autoincrement primary key,
token1  varchar(32) unique not null,
token2  varchar(32) unique not null,
token3  varchar(32) unique not null
[........some other data ......]

My client web app is build is such a way that I can't always use "id" to retrieve a record. Namely, I need to be able to retrieve a record by one of the "tokens" instead:

 select * from table1 where id = 1;
 select * from table1 where token1 = 'fdsafd';
 select * from table1 where token2 = 'ghgfdhg';
 select * from table1 where token3 = 'rewqrw';

Also, my client web app may need to update the "token(s)" also, different ones at different times, if a certain condition ocurrs. Not too often.

I estimate that the table would grow, at most, at 100.000 records per day, evenly distributed during a day; with around 20k updates per day.

Questions:

1) Will using these 3 tokens, of 32 characters long, as oppossed to using only "id",

and, also

2) Will updating them these

worsen the performance significantly?

3) Ways to improve it? All 3 tokens must remain. Should I create a separate table2 for tokens, to which I'd insert tokens only, to avoid having to update them in "table1"?


texts: NLS_NUMERIC_CHARACTERS in SQL loader control file

texts:
I am using a Control File to import data from a CSV file into a table, using sqlldr to import the data, where a large number of fields are numeric values with integer and decimal part separated by dots.
The Control File works just fine because I cast the number to the input my table expects by using

"TO_NUMBER(:MyVariable, '9999999999D999999', 'NLS_NUMERIC_CHARACTERS = ''.,''')"

Although the control file works just fine I would like to know: Is it possible to use:

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ","

In this way I don't mind about differences between character used in NLS and the one used to create the file, nor i mind about the actual number of decimal significative number.


texts: Restoring the superuser account on the postgres server

texts:
I have a complete curious situation with a postgres dbms. I have installed the database and due to a misconfiguration of my installation scripts I made the superuser role to be a regular user. Now I have a completeley fresh installation of postgres with only the template database and a single user 'postgres' that can do pretty much nothing. How can I make postgres user superuser again?

I tried to reinstall the server under ubuntu 12.04 with aptitude reinstall postgresql-9.1 but the role seems to be unaffected. Most probably because the reinstallation does not affect the files where the roles are recorded.


texts: How do I import updates to unique data in MySQL

texts:
I have a reasonably large MySQL database that is an import of CSV values from a public data repository.  It has come to my attention lately that they occasionally update (ie. correct) data, usually from records published within the last few months.  And, when I update my data, I only pull the last few months (not the entire database again).

I'm trying to figure out a way to import the recent data on a regular basis where it will OVERWRITE the current data with the new data if it conflicts.  I have previously been doing this using the IGNORE command, to avoid conflicts, but that obviously keeps the original values.

I'd also prefer not to simply overwrite the entire database each month as it is a large file.  So importing is a pain at over a gig of data.

So, is there a command like IGNORE that I can use that will have the opposite effect? One that will replace all columns that match unique keys (and thus cause a conflict) with the data from the NEWER import. Or a similar strategy?


texts: Which database is more suitable for executing user submitted queries?

texts:
I am in charge of creating a custom reporting tool for the company's clients. The goal is to let the client's in-house/contractor developers to query our database via a web-based query tool.

There are two main concerns here: Having users only read-only access to the database -- with no ability to run admin functions via queries, and having every query being async and have a timeout to prevent un/intentional DDoS attacks.

I can choose from PostgreSQL, MongoDB and MySQL, but I'm not expert in any of these. Therefore my question is which of these databases offers:

a) more control over each individual query

b) more sophisticated user-based permissions and access control system

c) and a better built-in performance overload handling in case of many small queries or few huge queries that can potentially crash the database

I understand that I'm mixing NoSQL and RDBMS here, but I'm happy to go for either of these if I can build a more reliable solution in the end.

Also scaling and performance come as lower priorities and can be totally excluded from the comparison.


texts: Multi-Tenant shared Users Tables: Need an advice

texts:
Am writing multiple Multi-Tenant applications with separate database per each application (I've decided to go with shared database different schema approach). Each application have Staffs Table for tenants' staffs and Customers for, well customers.

The problem am having is all my applications share the two tables and so there are going to be duplicates if each database is going to be completely independent.

I have thought of having a common database with Staffs and Customers table and have a different app (like Oauth2 server) handling the registration and login. And then have stub table Users in each multi-tenant apps with columns id and role(customer/staff) forming Primary key and gets their data from remote Staffs and Customers respectively.

Am not sure this is a good approach so and if there are better ways to handle that.

If am not clear let me know in comments.


texts: I have created a user and want to grant the user read only privileges on all database schemas. Can I do that using a single grant statement for all 20 schemas.


texts: What can cause higher CPU time and duration for a given set of queries in trace(s) ran on two separate environments?

texts:
I'm troubleshooting a performance issue in a SQL Server DR environment for a customer. They are running queries that consistently take longer in their environment than our QA environment. After analyzing traces that were performed in both environments with the same parameters/filters and with the same version of SQL Server (2016 SP2) and the exact same database, we observed that both environment were picking the same execution plan(s) for the queries in question, and the number of reads/writes were close in both environments, however the total duration of the process in question and the CPU time logged in the trace were significantly higher in the customer environment. Duration of all processes in our QA environment was around 18 seconds, the customer was over 80 seconds, our CPU time was close to 10 seconds, theirs was also over 80 seconds. Also worth mentioning, both environments are currently configured to MAXDOP 1.
The customer has less memory (~100GB vs 120GB), and slower disks (10k HHD vs SSD) than our QA environment, but but more CPUs. Both environments are dedicated to this activity and should have little/no external load that wouldn't match. I don't have all the details on CPU architecture they are using, waiting for some of that information now. The customer has confirmed they have excluded SQL Server and the data/log files from their virus scanning. Obviously there could be a ton of issues in the hardware configuration.
I'm currently waiting to see a recent snapshot of their wait stats and system DMVs, the data we originally received, didn't appear to have any major CPU, memory or Disk latency pressure. I recently asked them to check to see if the windows power setting was in performance or balanced mode, however I'm not certain that would have the impact we're seeing or not if the CPUs were being throttled.
My question is, what factors can affect CPU time and ultimately total duration? Is CPU time, as shown in a sql trace, based primarily on the speed of the processors or are their other factors I should be taking in to consideration. The fact that both are generating the same query plans and all other things being as close as possible to equal, makes me think it's related to the hardware SQL is installed on.


texts: How to update MySQL data within intranet from MySQL data in cloud?

texts:
I am currently using a MySQL DB located within the intranet. Only the people within the LAN can access the information.

I'm trying to re-create the MySQL DB in a cloud provider. This can be accessed by other people from a different location.

When people update the MySQL DB in the cloud, is there a way to synchronize the information to the MySQL DB located within the intranet and vice versa?

I was thinking of using MySQL Job Event, like exporting data to an insert/update script file then dumping the file onto a local intranet folder. The intranet MySQL will also have a Job Event checking for this file if there's a new dump.

I am not sure if this is the right way to do such a task.

Any helpful tips are really appreciated.


texts: updating millions of rows in multiple tables sql server 2005

texts:
I have a situation where I am going to need to update millions of rows in 11 different tables. I only have to update 1 column in each of the tables but I was wondering what would be the most efficient way to do this? One thought was to drop the indexes on the tables temporarily and run update statements in batches of 100,000 on 1 table at a time. We are planning to take the database offline to prevent potential locking issues. I would like to know what the fastest way to update all of these tables would be. Our database is Microsoft SQL Server 2005. Below are the counts of rows from the 11 tables that need updated.

Table 1 206,108

Table 2 560,859

Table 3 690,946

Table 4 1,106,173

Table 5 3,159,963

Table 6 3,250,619

Table 7 4,046,048

Table 8 5,638,351

Table 9 8,880,814

Table 10 18,060,095

Table 11 76,294,745


texts: How can I fix "Login failed for user sa" error in SQL Server?

texts:
I can access my databases through SQL Server Management Studio (SSMS) by using my Windows account no problem.
I have two issues though:

I want to enable sa and then try to log in using sa and the password. I get this error:

I enabled the SQL Server authentication:

I tried to create a new user, but that user cannot connect either:

How can I fix this?


texts:
In a little C# tool, I use an a mdf file to save my data.

The file became inconsistent, which isn't really a problem as there only a few changes since my last backup.

Anyway, I would still like to take a look at my last transaction to see what went wrong.

But I can't find a free tool to do so.

ApexSQL requires an SQL Server connection.
SysTool SQL Analyzer only allows for the first 50 rows, I have about 250.
::fn_dump again only on the SQL Server.

I have a running SQL Server available but can't attach the mdf file due to its corrupted state.

Is there a (free) way to view the log file? Doesn't need to be pretty, doesn't need special filters or search functions just plain and simple viewing.


texts: Does Mongo have disappearing/phantom writes

texts:
I have heard that Mongo has something like phantom writes.  For example, if I have a web request with the following db commands in a single unit of work:

a) update comments in article
b) update article attributes
c) update users comment_field

Questions:

Is it possible that only a and c execute, and b fails or never gets to the database?
Is it possible that only a and b execute, and c fails or never gets to the database?

Where can I read some more in regards to this, or is it just a myth?


texts: Should I use tempdb or memory optimized table variable?

texts:
I would like to change some of my stored procs to use memory optimized tables.
Before I do this, could you help me to clarify some things?
parallel execution
Here I can read, the tempdb doesn’t support it for insert-operation (What's the difference between a temp table and table variable in SQL Server?).
But what about with data retrieving, like select?
memory underrun
With memory optimized tables could it be possible, that I get an error, if the server is less of RAM?
In this thread (https://www.brentozar.com/archive/2014/06/temp-tables-table-variables-memory-optimized-table-variables/) I can read about this.
performance boost
Memory optimized tables do the boost, or not (What's the difference between a temp table and table variable in SQL Server?)?
This thread (https://www.brentozar.com/archive/2014/04/memory-ssd-tempdb-temp-table-sql-2014/) describes, that tempdb (starting with SQL 2014) can also reduce disk IO.
Should I use tempdb or memory optimized table variables?
I'm using Microsoft SQL 2014 (12.0.5000.0) Enterprise 64 bit. The SQL queries are a bit complex: e.g. there is a query, that uses 2 views and each of these views depending on other views.
The result of the whole query is not big (a couple thousands of rows).
Some of the tables, from which the views are depending, have 40 - 50k rows.
I alraedy realized, if rewrite a view into a table valued function, it increases the performance. Especially if the original view is embedded in another view, and used more time.
But what about the 3 points above? For me the links are somehow conflicting.


texts: Public Role Access For All Users in SQL Server

texts:
If I'm not mistaken this comes configured like this by default. Our new DBA stripped this permission and broke ownership chaining.

In my mind "public" means "everyone" and hence the permission and role assignment makes sense. Is it OK to remove this configuration? Is it normal? Does it really improve security or is this against how Microsoft thinks the security model should work?


texts: SQL Server 2012 extended event session to expose the network protocol

texts:
I am using SQL Server 2012 (11.0.5058.0) extended events, and wish to know the network protocol used by each connection (TCP/IP, shared memory etc).

Event session created for the login event via: -

CREATE EVENT SESSION [Login] ON SERVER
ADD EVENT sqlserver.login(
    ACTION(
sqlserver.client_app_name
,sqlserver.client_connection_id
,sqlserver.client_hostname
,sqlserver.client_pid
...
...

I have added all available actions for the login event (SELECT * FROM sys.dm_xe_objects WHERE package_guid = '655FD93F-3364-40D5-B2BA-330F7FFB6491' AND object_type = 'action' ORDER BY name) but none appear to give the network protocol.

It may be of course that Login is not the correct event to give this information, but I can't see a connection event (or similar) within the XE DMV.

To confirm, I want an extended event session to expose the net_transport information that is returned by dm_exec_connections: -

SELECT net_transport FROM sys.dm_exec_connections


texts:
I have 2 SQL Servers at production site Windows Server Failover Cluster and 1 SQL Server at DR site. I will add the DR node to existing Windows cluster and configure AOAG.

Should I go for File share witness or Disk Witness for Quorum Configuration for this AOAG set up .Please let me know where to place the file server share in which site.


texts: How to give permissions to 90+ SQL Servers without tweaking in each of them?

texts:
There are over 90+ MS SQL Servers (SQL Server 2005, 2008 and 2012). All in the same Windows domain (but physically all over the world).

How to give a domain user (administrative, or other) access/permissions to its instances and databases without tweaking (creating, configuring) users/accounts and permissions for such user (or group) in each of 90+ servers, (and even more) their instances and databases?

Update (in respnce to @JonSeigel comment):

I have the list of MS Windows server-machines (domain names, ip-addresses) in domain without any access to either machines, SQL and App Servers in domain (supposedly, SQL and app Servers use default or common conventions names).

So, I should request the access in the form of exhaustive detailed description of the smallest privileges/permissions  of what I need.

The usage of BUILTIN\Administrators group provided such easy access to SQL Servers of earlier versions -  2005 and 2000. This would have been the most convenient for me. But it is not the case with MS SQL Servers 2008 and 2012.

I need to describe what I need with the smallest necessary privileges necessary for administering (monitoring, tuning, optimizing) MS SQL Servers + app servers that use MS SQL Servers (sometimes they are not even on the same machine).

Never been in such a situation before that I should describe what I need without even having read access to what I need


texts: How to replicate from SQL Server 2000 to SQL Server 2008 R2?

texts:
I have SQL Server 2000 as publisher and SQL Server 20008 R2 as subscriber.

I want to replicate data from SQL Server 2000 to SQL Server 2008 R2 on a different machine.

Primary key fields are not defined on some tables so transaction replication is not a choice.

I think merge replication is not a choice because the Subscriber SQL Server version is higher than the publisher.

What can I do?


texts: SQL Server - Remove all logins that they are asossiated with orphan users

texts:
I have an SQL Server 2008R2 that had lots of databases.

The procedure was that a logins were created and users were mapped to databases.

Most of these databases have been removed from the server now but it ended up having LOTS of logins that are associated to users mapped to non existing databases.

Is there a batch way to get rid of the these logins and their assosiated users?

Thank you


texts: setup transaction replication in cross domain

texts:
I'm trying to setup transaction replication between two domains under the same active directory.  The publisher server is in a DR site domain and the remote distribution server is in the main site domain with the subscriber. Here are the machine names

Publisher Server : publisher1.drsite.mycompany.com
Distribution Server : distributor1.mainsite.mycompany.com
Subscriber Server : subscriber1.mainsite.mycompany.com

When I try to add publisher1.drsite.mycompany.com to the remote distributor, it will not accept the FQDN and gives me the error below under Replication.Utilities

SQL Server replication requires the actual server name to make a connection to the server.  Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, "publisher1"

Does anyone experience the same problem and know how to solve it?


texts: Postgresql ignoring a substring in the middle of a string

texts:
I may have phrased the title of this question badly. I have a documents table that has a filename, path, and file_content_type columns. I want to search against the filename column to determine if there are records with a similar name.

The problem is that a revision suffix (R00, R01, ...) is always appended to the filename column. What our business rules consider to be the same file will have a different names because the version suffix will be different.

We must search for the entire filename, including the extension, but ignore the revision suffix. And I'm not sure how to go about this.

I thought about negating the version suffix, but its dynamic. Using fuzzy search or trigram doesn't seem like a sound choice either, nor using ILIKE or SIMILAR TO.

Here are some examples of what we consider to be the same file, and a different file.

Same file; only difference is the version suffix: R00 vs R01:

LLE-MET-AP-0000-PLA-COB-R00.pdf
LLE-MET-AP-0000-PLA-COB-R01.pdf

Different files; paper number is different 0000 vs 0001

MET-AP-0000-PLA-COB-R00.plt
MET-AP-0001-PLA-COB-R00.plt

Different files; extensions are different:

LLE-0000-PLA-COB-R00.dwg
LLE-0000-PLA-COB-R00.plt

There's one more complication. There may be cases where the revision suffix can take different forms:

With a leading zero: R01, R02 ...
Without a leading zero: R1, R2 ...
Different separators, and lettercase: XXX-R01, XXX_R01, XXX_r1, etc...

A possible solution is to use a regular expression:

SELECT *
FROM documents
WHERE (filename ~* 'LLE-0000-PLA-COB([.-_ ]{0,}[0-9]{1,2})')

But this has a major disadvantage. I must preprocess the search string to remove any version suffix and then use it in the search. If a record has the filename value of LLE-0000-PLA-COB-R01 I have to remove the -R01 before doing a search, and this is not ideal.

I would simple like to say:

  Find me any files that have this name LLE-0000-PLA-COB-R01.jpg, but ignore the R01 in your search.

What other choices do I have to approach this problem?


texts: SSRS 2017 Subscription - Permissions granted to user '' are insufficient for performing this operation

texts:
I've seen lots of issues relating to 'permissions....insufficient for performing this operation' which can arise in a variety of places when related to SSRS - deploying reports, running reports etc....

But I can't find anyone who has precisely the same issue as me.

I have created a subscription with 'me' as owner - I am an admin with high level permissions.
This works fine and I can send the report.

But when I 'try' to change the 'owner' of the subscription to another user and apply it, I get the message:

"Permissions granted to user '' are insufficient for performing this operation"

Now, this is the EXACT message.  No user is specified in the error, even though I have entered the DOMAIN/USERID in the Owner field of the subscription. (I would have at least expected to see "...user mydomain\thisUser are insufficient..." etc.  But no.  Just two apostrophes are shown.

So.......This is presumably a permissions issue.  But what?

Initially this user is not an admin like me, but I have elevated her permissions temporarily to match mine wherever possible (with the intention of getting it to work and then slowly tightening the permissions again to discover the preceise one that causes the issue.)

So on the SSRS server, I have granted this Other user:

Site settings of 'System Administrator' AND 'System User'
ALL Roles - Browser, Content Manager, My Reports, Publisher, Report Builder and Report_Reader
I have gone even further and created a new Role called 'Everything', assigned EVERY task to this role, and granted this role to my user

In SQL Server I have even made the Other user a sysadmin in the instance!

As far as I can think, I have now given this user every possible permission adn high-level access that I can.

YET....if I change the Owner of an existing (WORKING!) subscription from OurDomain\MyUsername to OurDomain\OtherUsername I STILL get the same error message:

"Permissions granted to user '' are insufficient for performing this operation"

What other permission could 'I' have that I have not granted to this other user....?

I am REALLY stumped with this!


texts:
Pretty straightforward question:

Let's say I have a specific query that I know is regularly used, can I tell MySQL to always cache it "automatically" ... i.e. once the cached query results are flushed the mysqld immediately runs that query again to have the cached data ready for the next user?

Or the other way round: can I tell MySQL to not cache specific queries?
What about other caching options, not necessarily related to queries?

I searched through the web on this but can't really find anything related.


texts: What are the SET options that affect Plan Reuse and how to get their values in T-SQL?

texts:
I'm troubleshooting the case when a stored procedure runs great from the management studio at any time, but the same stored procedure can run terribly badly in one of the webservers, even for the same parameters. There could be a few things causing this, including blocking, etc.
I would like to leave out the possibility that 2 different plans were created with different SET options, and at least one of those plans was optimized using a combination of parameters that produced a bad plan.
Quoting Benjamin Nevarez:

"in general, query optimization is an expensive operation, and in
order to avoid this optimization cost, the plan cache will try to keep
the generated execution plans in memory so they can be reused.
However, if a new connection running the same stored procedure has
different SET options, it may generate a new plan instead...."

In order to tackle this I would like a T-SQL query that would show me all the values that have been set for the session.
Is this possible?

the following SET options will affect the reuse of execution plans:
(some of them)
DATEFORMAT
LANGUAGE
NUMERIC_ROUNDABORT
FORCEPLAN


texts:
I am getting below error in db2 when executing the sql query-

  SQL20448N  "05/09/2016" cannot be interpreted using format string
  "YYYY-MM-DD" for the TIMESTAMP_FORMAT function.

here is my sql qyery-

SELECT DLR_CD,
       FIN ,
       YEAR(TO_DATE(CURRENT_DATE ,'YYYY-MM-DD'))-YEAR(TO_DATE(CRTNG_DTE,'YYYY-MM-DD'))
       AS AGE
FROM ASPECT.RO_CAR_PARK_EXTRACT WHERE YEAR = PARM_YEAR

what could be the reason for this?

Database - DB2 v9.7.200.358
Platform-  home basic 7


texts: Does restarting the SQL Services (on the machine) clear the server cache (for things like query plans and statistics)?

texts: Does the server cache get wiped (similarly to when you restart the SQL instance / machine) when you only restart the SQL Services themselves?


texts: Performance of large queries on low bandwidth connections

texts:
I've been running some measurements on bandwidth needs and the effects of latency on query performance against Postgres 8.3 (waiting for management to approve upgrade) on Windows.  SELECTs of various quantities of rows and some INSERTs with bandwidth of 25 Mbps, 5 Mbps, and 1 Mbps, with latency of 50,40,30,20,10.

For selects of 1, 3, and 250 rows the performance was basically the same with each bandwidth, and the same effects of latency.  However, for a select of 7100 rows, the 1 Mbps situation had identical performance across each latency value.  Additionally, while 25 and 5 Mbps performance was linearly correlated to difference in rows (28x rows, 28x time), the 1 Mbps queries are at about 44x.

So, I was curious if Postgres has some built in throttling that would mask the effects of latency.  This may be more of a networking question, but I don't think so.  Seems to be specific behavior to larger queries on slower connections.


texts: Increase TempDB Files, Shrink VLF's with FCI

texts: I am planning to resize and increase the number of tempDB files and shrink the VLF's on an FCI. In this case, the tempdb files are stored on the network drive, rather than the local disk. I couldn't find anything that addresses situation, but I need to know the impact the increase would have on a FCI. Thanks!


texts:
I have the following query that takes minutes to complete:

SELECT DISTINCT Designation, Model, (
    SELECT COUNT( Equipment.EquipmentID )
    FROM Equipment
    INNER JOIN EquipmentDesignation_Vw ON Equipment.EquipmentID = EquipmentDesignation_Vw.EquipmentID
    LEFT JOIN EquipmentIssuance_Vw eqpi on Equipment.EquipmentSerial = eqpi.EquipmentSerial
    WHERE Equipment.ItemID = i.ItemID
    AND EquipmentDesignation_Vw.Designation = ed.Designation
        AND EquipmentDesignation_Vw.ArmoryID IN 2
    AND ((eqpi.IssueLocationID is NULL) OR (eqpi.IssueLocationID is not NULL AND eqpi.ReturnLocationID is not NULL ))
) AS Total
FROM Item i
INNER JOIN ItemSubCategory isc ON i.ItemSubCategoryID = isc.ItemSubCategoryID
LEFT JOIN Equipment e ON e.ItemID = i.ItemID
LEFT JOIN EquipmentDesignation_Vw ed ON e.EquipmentID = ed.EquipmentID
LEFT JOIN EquipmentIssuance_Vw ei on ei.EquipmentSerial = e.EquipmentSerial
WHERE ed.Designation = 'Issue'
    AND ed.ArmoryID IN 2
    AND i.ItemCategoryID = 7

This is because of the nested SELECT COUNT subquery. Without it, it runs blazing fast.

I made sure to add indexes on all the columns used to perform the JOINS.

If I extract the COUNT subquery to its own query, it is also really fast:

SELECT i.ItemID, COUNT( Equipment.EquipmentID )
FROM Equipment
INNER JOIN Item i on i.ItemID = Equipment.ItemID
INNER JOIN EquipmentDesignation_Vw ON Equipment.EquipmentID = EquipmentDesignation_Vw.EquipmentID
LEFT JOIN EquipmentIssuance_Vw eqpi on Equipment.EquipmentSerial = eqpi.EquipmentSerial
WHERE Equipment.ItemID = i.ItemID AND EquipmentDesignation_Vw.Designation = 'Issue'
AND EquipmentDesignation_Vw.ArmoryID IN 2
AND ((eqpi.IssueLocationID is NULL) OR (eqpi.IssueLocationID is not NULL AND eqpi.ReturnLocationID is not NULL ))
GROUP BY i.ItemID

I tried to remove the subquery and calling COUNT directly in the SELECT, but to get the same results as the original query, I need to add the AND ((eqpi.IssueLocationID is NULL) OR (eqpi.IssueLocationID is not NULL AND eqpi.ReturnLocationID is not NULL )) condition to the main query. By doing so, I am not sure that I will not affect the original results:

SELECT DISTINCT Designation, Model, COUNT( e.EquipmentID )
FROM Item i
INNER JOIN ItemSubCategory isc ON i.ItemSubCategoryID = isc.ItemSubCategoryID
LEFT JOIN Equipment e ON e.ItemID = i.ItemID
LEFT JOIN EquipmentDesignation_Vw ed ON e.EquipmentID = ed.EquipmentID
LEFT JOIN EquipmentIssuance_Vw ei on ei.EquipmentSerial = e.EquipmentSerial
WHERE ed.Designation =  'Issue'
AND ed.ArmoryID  IN 2
AND ((ei.IssueLocationID is NULL) OR (ei.IssueLocationID is not NULL AND ei.ReturnLocationID is not NULL ))
GROUP BY i.ItemID

This is quite fast as well, and seems to return the same results as the original query but I am not certain about this.

So is there a way to get rid of this subquery, or at least to make it more performant?

This is the output of EXPLAIN EXTENDED for the original query

Thank you. I am not a DBA, I spent hours on this and feel a bit stuck here.


texts: Is there an easy way to clear the logfile of postgresql? the logfile is 6044 MB, can you use cp /dev/null file-name or do you mess up the database?


texts: Oracle prevent CPU usage per process/session

texts:
I have an Oracle instance to which several applications from different machines connect (http servers). Normally Oracle CPU usage is ~5-10% even on high server loads. But from time to time, one of http servers impose high (100%) CPU load on Oracle, which causes other oracle clients (in the case other http servers) timeout and do not work properly.

Is it possible to limit CPU usage on a per process or per session basis? Or is it possible to limit CPU usage for clients connected to oracle from a special Machine/IP?


texts: Error 1041 "Out Of Memory" after pointing tmpdir to new disk space

texts:
I want to index a very large column in a very large table. The default /tmp does not have enough disk space and is throwing no space left on device error.
I have a much larger filesystem partitioned where I can point innodb_tmpdir to.

I created a tmp dir in this partition: /mnt/partition/tmp
Changed the permissions of this directory to 777 chmod -R 777 /mnt/partition/tmp
Then I set the innodb_tmpdir variable to point to this directory via mysql client: SET VARIABLE innodb_tmpdir='/mnt/partition/tmp';
The variable is set correctly by executing: SHOW VARIABLES LIKE 'innodb_tmpdir';

The issue arises when I try to create an index with the following basic command structure:
create index a on b (c, d, e, f);

The following error is instantly thrown:

ERROR 1041 (HY000): Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

What is with this error? Do I need to set some permissions/configs for my partition?
NOTE If I do not set innodb_tmpdir and keep everything to default, the index command runs without this issue. (obviously it crashes halfway through due to the limited disk space which is the actual reason why I am changing tmpdir
my.cnf
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=8
wait_timeout=604800
interactive_timeout=604800
local_infile=ON
binlog_expire_logs_seconds=600
skip-log-bin

swap size: 8.39GB. There is 7GB of memory on the server.
MYSQL Version 8 on Ubuntu 18.04. Machine is fully dedicated to MySQL.
After executing the create index command on new innodb_tmpdir error.log file is showing:
[ERROR] [MY-012792] [InnoDB] Cannot create temporary merge file


texts: Migrate/Export DB from Oracle 11g Enterprise to Oracle 11g Express

texts:
So I'm a DBA newbie and I've got an Oracle Enterprise server running in production with the following information under All Reports -> Data Dictionary Reports -> Version Banner, under "Your_Database_Settings".

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

I would like to export/clone the database I have running on the server and import it into a new Oracle 11g Express installation to run some sandboxed tests.

Can someone help me with detailed instructions on how to make that happen?


texts: Postgresql: invalid record length at 29/E4624520 but replication looks fine

texts:
What is this error? I see that replication is working fine, but I am not so sure if the data is being corrupted or not.

Can I safely ignore this error?


texts: Deleting expired SQL server database backup file on data domain using PowerShell

texts:
I am backing up the database on data domain however I'll have to delete an expired backup using following command. It's an emc databoost agent app for Microsoft sql server.

I am trying to convert the command into powershell so I can run the command to delete expired backups from multiple clients in a loop. That way I can avoid running the command on each individual client.

I created following script and $clientname is hardcoded, but I would like to pass multiple server names as $clientname in loop.

$FileExe = "C:\..\bin\ddbmexptool.exe"
$dbtype = "mssql"
$dduser = "DDBOOST_USER=dduser"
$ddhostpath = "DEVICE_PATH=DDHOSTMSSQL"
$devicehostname = "DEVICE_HOST=ddhost.domain.com"
$clientname = "CLIENT=server1.domain.com"

& $FileExe -n $dbtype -a $dduser -a $ddhostpath -a $ddhostpath -a $devicehostname -a $clientname -b "6 weeks ago" -e "2 weeks ago"


texts: Pick the most non-default value from a selection of values

texts:
Given the following tables:

CREATE TABLE FeeTestClient (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name VARCHAR(16))
INSERT INTO FeeTestClient (Name)
VALUES ('Test'), ('Test 2'), ('Test 3')

CREATE TABLE FeeTest (FeeId INT IDENTITY(1,1) NOT NULL PRIMARY KEY, ClientId INT, Fee INT, Val VARCHAR(16), Val2 VARCHAR(16))
INSERT INTO FeeTest (ClientId, Fee, Val, Val2)
VALUES (1, 15, 'Default', 'Default'),
       (1, 10, 'Default', 'asdf'),
       (2, 15, 'Default', 'Default'),
       (2, 20, 'Default', 'qwer'),
       (2, 10, 'zxcv', 'asdf'),
       (3, 20, 'Default', 'Default')

My goal is to select all FeeTestClient elements, and pick the fee that is least default. The rules on a default fee are pretty simple: if Val2 is 'Default', then Val cannot be anything except 'Default', and for each fee, we want the first one where Val is not 'Default', or the first one where Val2 is not 'Default', otherwise we are guaranteed a Val = 'Default' AND Val2 = 'Default' match.

The customer will only ever have one item that matches 'Default'/'Default', one item that matches 'Default'/____ and one item that matches ____/____. (Though the last two rows may not exist.) If they have a ____/____ then they'll always have a 'Default'/____, every customer will have a 'Default'/'Default'. They can never have a ____/'Default' — this is an invalid state on the application, and they can never have more than one of the same x/y, this is enforced by a UNIQUE constraint on the table.

It is possible (in the database) for the customer to have a 'Default'/a, and a 'Default'/b, but that is considered an invalid state in the application, and there is a test for that. (The user must delete one of the two.)

This is similar to a previous question of mine (Select all records, join with table A if join exists, table B if not), but much less pleasant. Because they're INT (actually FLOAT in the database, but the same issue applies) they're being aggregated together like I don't want.

I want to get a result of:

Id  Name    (No column name)
1   Test    10
2   Test 2  10
3   Test 3  20

I've tried:

SELECT Id, Name, COALESCE(f1.Fee, f2.Fee, f3.Fee)
FROM FeeTestClient
LEFT OUTER JOIN FeeTest AS f1 ON f1.ClientId = Id AND f1.Val <> 'Default' AND f1.Val2 <> 'Default'
LEFT OUTER JOIN FeeTest AS f2 ON f2.ClientId = Id AND f2.Val = 'Default' AND f2.Val2 <> 'Default'
LEFT OUTER JOIN FeeTest AS f3 ON f3.ClientId = Id AND f3.Val = 'Default' AND f3.Val2 = 'Default'

Which is ungodly slow on the live data-set, but returns the proper result (roughly 15 seconds, running the basic select on the data, without this selection of joins, is 7, plan is here), and I've also tried (by suggestion of Joe Obbish):

SELECT Id, Name, MAX(COALESCE(CASE WHEN Val <> 'Default' AND Val2 <> 'Default' THEN Fee END, CASE WHEN Val = 'Default' AND Val2 <> 'Default' THEN Fee END, CASE WHEN Val = 'Default' AND Val2 = 'Default' THEN Fee END))
FROM FeeTestClient
LEFT OUTER JOIN FeeTest ON FeeTest.ClientId = Id
GROUP BY Id, Name

Which is just as slow (plan is here), and produces the wrong output (though if you COALESCE the MAXs then it works correctly). (Maybe worse.)

I'm at a loss, and writing these queries has been very painful, so any advice on building the desired output is appreciated.

I've provided the actual plans, but they differ very heavily from the test ones (this MCVE is to demonstrate the result I want, answers are not obligated or even expected to make any performance claims), it seems. Please disregard the extra joins shown in the plans above - they are unimportant to the example.

As far as distribution, in the live data-set, 1.5% only have a 'Default'/'Default', 44.9% have 'Default'/___ and 'Default'/'Default', and 53.6% have all three.


texts:
I'm currently investigating the different options there are setting up a DR site for our main data centre. In an ideal world, we would have fewer, larger databases, however due to architectural constraints with the product, we have about 3000+ small (50MB to 300MB) databases.

I’ve had some experience with replication and I know that I can script setting up replication on each database, but my main concern is the performance overhead and the maintainability of this as a solution.

Before I go ahead and spend a lot of time testing this, has anyone else had any experience / advice on replication of multiple databases on this scale?

Thanks everyone for your comments and feedback. This is the first question I have asked on this forum so I’m having to edit my original question to respond to your comments and expand on the question…

In the main data centre we have a SQL Cluster with SQL2008R2 Standard connected to a SAN.

Regarding our RPO and RTO, the product is not a critical service, we would need to recover to a point in the last 24 hours and the recovery time needs to be within 24 hours of failure at the main data centre for all databases.

There are about 5 key databases which are used by all the other databases, however all other databases are independent of each other.

All databases have their recovery model is set to “Simple”, where full backups are made weekly and differential backups are made daily. Based off our RPO and RTO, log shipping does definitely sound like a viable solution.


texts: Is there a way to put comments in a data pump export parameter file?


texts: Why am I getting permission denied on an ownership change?

texts:
My current user is pronto

mediapop_staging=> select current_user;
 current_user
--------------
 pronto
(1 row)

This user a standard super user created on AWS RDS.

mediapop_staging=> \du pronto
                        List of roles
 Role name |          Attributes           |    Member of
-----------+-------------------------------+-----------------
 pronto    | Create role, Create DB       +| {rds_superuser}
           | Password valid until infinity |

But I'm getting this:

mediapop_staging=> REASSIGN OWNED BY pronto TO mediapop_staging;
ERROR:  permission denied to reassign objects

Why? How can I resolve it?


texts:
I'm wondering if/how an SQL Server would be impacted by an Access DB that has ODBC Read-Only linked tables from that SQL Server on it.

If a form event triggers a query on one of those linked tables,  is that query hitting a local version of that linked table, or is it going to impact the overall performance of the SQL Server? I have about 30 users simultaneously using an Access Front End, linked to an Access Back End, and have added functionality that draws on several of our SQL Server's tables; I just can't allow that many users to be hitting the server with query on top of query. It would bog down the SQL Server immensely.

If you have any ideas on how to best proceed, let me know!

Thanks,


texts: Does Service pack have all the changes in a cumulative update

texts:
I have always thought that all the changes in the CU's are rolled up into the next services pack.  I recently was looking at changes in SQL 2016 SP2, and SQL 2016 CU15 for SP1.

I was surprised to see that none of the KB in SP1CU15 was mentioned in the SP2.  What am I missing?


texts: SQL Server - Basic Availability Groups used for Publisher, Distributor and Subscriber databases?

texts:
I see recently support has been added so that the Distributer can be hosted with AlwaysOn AG.

https://blogs.msdn.microsoft.com/sql_server_team/replication-enhancement-distribution-database-in-availability-group/
https://docs.microsoft.com/en-us/sql/relational-databases/replication/configure-distribution-availability-group?view=sql-server-2017

Can the Publisher, Distributor and Subscriber databases be hosted in a High Availability configuration using Basic Availability Groups?

Given that basic availability groups only support a single database, if BAG can support this, I assume it would require three sets/clusters of two nodes ~ 2 publishers, 2 distributors and 2 subscribers.


texts: SQL Server - how transactions and transaction log work (simplified)

texts:
I have a theory on how transactions work, but I would like if someone could verify it or correct some points if possible. Let's consider we have a database with full recovery model.

Now, everything that happens to the database is logged into the transaction log first, written to disk after a checkpoint is reached.

If I am inside an explicit transaction, all changes to database are written to log first, but during checkpoint uncommitted transactions are not propagated to disk.

If I decide the rollback the transaction, the rollback is simply done by deleting all the transaction log entries.

If I commit, after next checkpoint changes are written to disk.

Transaction log contains instructions on how to redo the operation, not how to undo it, which is why restore to point in time is only possible from baseline backup forwards, not backwards.

Its deducible from some operations and their redo instructions on how to undo it (but not for all). Thats how commercially available tools work to e.g. recover data in full recovery model databases using transaction log.

All are my points at least somewhat valid? Could someone please comment? This is my third and hopefully final question on my quest to discover how transactions and transaction logs work in SQL Server. Thanks.

EDIT:

Simple test seems to disprove my theory, as SQL Server seems to write into primary data file even if inside a transaction that insert 1000000 rows (nothing else goes on with the database). Im confused and sad:)


texts: ER model between video, tagbundle and tag

texts:
I have the following situation:
There is a video platform (like youtube). Each video is described by a number of video tags. How to model the situation?
I realized it as follows:

Each video, is described by a 'tagbundle' and each 'tagbundle' can describe 1..N videos..But what about the relationsship between 'tagbundle' and 'tag'..Each 'tag' can be contained in 1..N 'tagbundles' but each 'tagbundle' can also contain 1..N 'tags'..how do I resolve this?


texts: How do I grant all privileges to local db2admin with DB2 9.7 on Windows?

texts:
I have as my developer station a laptop with OS Windows XP Professional Edition, Service Pack 3.

I have downloaded and installed IBM DB2 UDB 9.7 fix pack 4, of the Express-C edition.

I have a local Windows account called db2admin that I am using as my local database administrator for my local install of DB2 (developer purposes only).

I can run the following without issues when running the Command Window:

db2 attach to db2 user db2admin using xxxxxxxxxx

That allows me to attach to my instance called DB2.

I can run my create database commands.

I then attempt to connect to the database to grant all privileges for my db2admin account in DB2.

db2 CONNECT TO MYDB;
SET SCHEMA DB2ADMIN;
db2 GRANT DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE TO USER DB2ADMIN;
CONNECT RESET;

However when I run that, it DB2 tells me that my actual windows user account (synprgcma) does not have authority to grant authority to user db2admin.

So if I change the second script to the following:

db2 CONNECT TO MYDB USER db2admin USING xxxxxxxx;
SET SCHEMA DB2ADMIN;
db2 GRANT DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE TO USER DB2ADMIN;
CONNECT RESET;

Then DB2 tells me that db2admin cannot revoke or grant authority to itself (actually it tells me that an id cannot revoke or grant authority to itself).

So I am stumped. I did not have this problem with my previous install of DB2 (9.5, and I don't remember which fix pack I was at).

How do I grant the necessary authorities to the local admin account?  I believe I need this in order to run a bind command that I need to do next:

db2 CONNECT TO MYDB;
db2 bind @db2cli.lst blocking all grant public sqlerror continue CLIPKG 20;
db2 CONNECT RESET;

Any help would be appreciated.

  Edit: I've found some of the following links related to this. Still not sure yet how to get it working, but at least I've found some
  documentation.

  http://www.db2teamblog.com/2009/06/whats-new-in-db2-97.html
  http://lpetr.org/blog/archives/simplify-the-db2-9-7-security-model
  http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0000958.html
  http://www.ibm.com/developerworks/forums/thread.jspa?messageID=14288459
  http://publib.boulder.ibm.com/infocenter/tivihelp/v4r1/index.jsp?topic=%2Fcom.ibm.tpc_V422.doc%2Ffqz0_t_preparing_to_install_db2_windows.html
  http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.sec.doc%2Fdoc%2Fc0055206.html
  http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.sec.doc%2Fdoc%2Fc0005479.html


texts: Same query, same workstation, different execution plans

texts:
I ran the same query on the same workstation against two different databases that yielded completely different execution plans.  The structure and indexes are the same. The only difference is the data.  The strange thing is the data in “database1” is much less than the data in “database2” and the performance is worse (database2 takes ½ the time as database1).  You’ll notice an obvious design flaw in the ItemProperties table and we plan on changing the structure of this poorly designed table, however before that takes place I’m looking for optimization ideas.  Here is the info for both databases.

Database1:
[Actual Execution Plan] https://www.brentozar.com/pastetheplan/?id=HyVv5-PE-

Database2:
[Actual Execution Plan] https://www.brentozar.com/pastetheplan/?id=rJrzjbDVW


texts:
I'm trying to update a table with records in another table. My problem is that when there are duplicates returned the values of the first record get used to update.

  update  table1
    set
        columna = table2.expr
    from
        table1
        inner join
        table2
        on table1.column1 = table2.column1

If I

select *  from
        table1
        inner join
        table2
        on table1.column1 = table2.column1

I get

column1 expr
5       0
5       123

So expr is being updated to 0 when I want it to update it with 123

How do I change the update query to select the correct expr to use?
In SQL Server 2008.


texts: How do I deal with FK constraints when importing data using DTS Import/Export Wizard?

texts:
I am trying to use the SQL Server Import and Export Wizard to copy data from my production db to my dev db but when I do it fails with the error "The INSERT statment conflicted with the FOREIGN KEY constraint" i have over 40 tables with lots of FK constraints, is there some easy way to deal with this without having to write a drop constraint/add constrat script?

Edit:
 I just found out that in Web edition of SQL Server, which is what I am running, DTS will not let you save packages.


texts:
I have installed MySQL 5.7 community edition installed.
Can we use Percona xtradb backup tool for backup DB for banking site databases for OLTP?


texts: Made changes to my.cnf and mysqld wont restart - no backup

texts:
I thought I'd be clever and update my.cnf file to add ft_min_word_len=2 and didn't back it up first. I will never make that mistake again. Luckily, our project is still in production and no harm done.

I have no clue as to what I have done wrong in that file, as I cannot see any error logs (/var/log/mysqld.log). MySQL was running fine before I touched the config file - well, I assume it was.

By running the following command and restarting mysqld (service mysqld restart):

mv /etc/my.cnf /etc/my.cnf.backup

I got it up and running again - so that proves the config file is corrupt somehow. As soon as I put it back and restart the service it fails to start again.

This is probably not much help but this is my terminal screen showing the my.cnf file edited:

[mysqld]
local-infile=1
atadir=/var/lib/mysql
ft_min_word_len=2
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

innodb_buffer_pool_size=2M
innodb_additional_mem_pool_size=500K
innodb_log_buffer_size=500K
innodb_thread_concurrency=2

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_buffer_pool_size=2M
innodb_additional_mem_pool_size=500K
innodb_log_buffer_size=500K
innodb_thread_concurrency=2

I can't see from this is what could have gone so wrong. I have tried removing the line I added, obviously.

What are my options here? How can I restore the original config file?


texts: Splitting or Distributing very large data on PostgresQL 9.6

texts:
I have a dedicated server with 10x1TB. I can do RAID and make them as single one. But this gave me 10TB data. What will happen if PostgreSQL data exceeds 10TB? Which will exceed soon (I have a special software that requires TBs of database and writes/reads always)

Is there any method/feature in PostgreSQL or pgpool-II to add more servers or hdds when needed. What should I do to prevent such scenario?

Also, I have GlusterFS infrastructure, Should I deploy my database in that? Or is there any filesystem exactly for this?

Note: I know there is replicate. But my situation is splitting very large database on multiple disks or servers or distribute on filesystems like GlusterFS. Like where server is full I add more and it continues to write...


texts: Postgresql WAL Archiving, How to do it properly?

texts:
Basically I have one (for now) mission critical postgresql database that I will need up to the minute recovery for, I've never implemented a working WAL archival before so I would like to know what is the best way to do this.

The machine we are going to backup to is remote, and we have ssh keys set up, so I was going to have the WAL's rsync'd over. Is that wise with that archive_command option?

A Step by Step guide with examples on how to set these things up would be amazing as I couldn't find one I understood (I'm not a DBA)


texts: Is my theory on INSERT deadlocks correct?

texts:
I originally had this (as three separate PG queries, from my applications):
INSERT INTO table1 (col1) VALUES ($1) ON CONFLICT DO NOTHING;
INSERT INTO table2 (col1) VALUES ($1) ON CONFLICT DO NOTHING;
INSERT INTO table3 (col1, col2, col3) VALUES
(
    (SELECT id FROM table1 WHERE blabla = $1),
    (SELECT id FROM table2 WHERE blabla = $2),
$3);

After finally learning about transactions (or at least I thought that I had finally learned about them...), I wanted to group three INSERTs into one transaction because they are deeply related. The third INSERT expects those other tables to be populated before it runs, and the two first INSERTs are meaningless without the third, "main" one. So I naively did this:
BEGIN;
    INSERT INTO table1 (col1) VALUES ($1) ON CONFLICT DO NOTHING;
    INSERT INTO table2 (col1) VALUES ($1) ON CONFLICT DO NOTHING;
    INSERT INTO table3 (col1, col2, col3) VALUES
    (
        (SELECT id FROM table1 WHERE blabla = $1),
        (SELECT id FROM table2 WHERE blabla = $2),
    $3);
COMMIT;

Sadly, it now started logging a bunch of this when running the third INSERT:
pg_query_params(): Query failed: ERROR:  current transaction is aborted, commands ignored until end of transaction block
pg_query_params(): Query failed: ERROR:  deadlock detected
DETAIL:  Process 6276 waits for RowExclusiveLock on relation 939516 of database 53142; blocked by process 7508.
Process 7508 waits for AccessExclusiveLock on relation 939490 of database 53142; blocked by process 6276.

This confused me fundamentally, but then I thought that maybe, the transaction block causes the third INSERT to not "see" the previous INSERTs because they have not yet been committed? Is that it?
I have now changed it into:
BEGIN;
    INSERT INTO table1 (col1) VALUES ($1) ON CONFLICT DO NOTHING;
    INSERT INTO table2 (col1) VALUES ($1) ON CONFLICT DO NOTHING;
COMMIT;
INSERT INTO table3 (col1, col2, col3) VALUES
(
    (SELECT id FROM table1 WHERE blabla = $1),
    (SELECT id FROM table2 WHERE blabla = $2),
$3);

So far, it has not logged any deadlocks. It appears as if my theory is correct, but I wish to hear if this is a false conclusion by me.
Also, if this is the case, I guess there is no way to "group" all three INSERTs? Since I have to commit after the first two for the third to be possible?
Also, I'm confused as to why, in the second code example, the third INSERT's internal SELECTs don't just return "null" values since that's what normally happens when the values don't exist in the tables. I assume it "knows" that it's inside a transaction with relevant changes having been made? That's got to be it?


texts: PL/SQL for Updating 1 Column in Table A Where PK = a row in table B

texts:
Using an oracle DB, 11g r2, I need to write a pl/sql which updates one column in table A where the PK exists in table B, with commits ever ~50,00 rows.

3200000 rows are involved in the update, and table A has ~70 million rows total.

What is the most efficient way to do this?

Table A (has ~70 million rows, and ~100 columns. Only one needs to be updated):

CREATE TABLE ORD
   (
    ORDER_KEY NUMBER NOT NULL,

    /* ~95 other columns */

    ORDER_TYPE" NUMBER(10,0) /* col needing update */
   )

Order key has an index.

Table B (contains the id's of the rows from table a which need the update):

CREATE TABLE GR_ORD (ORDER_KEY NUMBER NOT NULL)


texts: How do I convert the sum of minutes into Hours:Minutes format

texts:
How do I add the value of two money fields that contain a number of minutes and return a string formatted as hours:Minutes?

The query I have so far:

SELECT (
  SELECT ISNULL(SUM(action.service_time), 0) AS Expr1
  FROM dbo.act_reg AS action
  INNER JOIN dbo.act_type ON action.act_type_id = dbo.act_type.act_type_id
  WHERE (dbo.act_type.act_type_n IN ('Travel Time'))
  AND (action.incident_id = inc.incident_id)
) AS [Travel Time], (
  SELECT ISNULL(SUM(action.service_time), 0) AS Expr1
  FROM dbo.act_reg AS action
  INNER JOIN dbo.act_type AS act_type_6 ON action.act_type_id = act_type_6.act_type_id
  WHERE (act_type_6.act_type_n NOT IN ('Travel Time', 'Site Departure'))
  AND (action.incident_id = inc.incident_id)
) AS [Total Productive Time]
FROM dbo.incident AS inc
INNER JOIN dbo.assyst_usr ON inc.ass_usr_id = dbo.assyst_usr.assyst_usr_id
INNER JOIN dbo.serv_dept ON dbo.assyst_usr.serv_dept_id = dbo.serv_dept.serv_dept_id

Expected Result:

Travel Time     Total Productive Time              Total Service Time
60.00           17.00                              1:17
2520.00         10240.00                           212:40

Description:

'Travel Time' and 'Total Productive Time' are retrieving perfectly, please help me to get the 'Total Service Time' in (HH:Min) format.


texts: Replacement for Pragma Restrict references

texts:
Up to Oracle 11.1 you could mark a package as only reading from the database by adding
PRAGMA RESTRICT_REFERENCES( DEFAULT, WNDS);

This has now been deprecated.  I would like to enforce read only on some of the packages.

it would help ensure that junior developers don't add new functionality that writes 'just because'
it makes granting execute on read only packages to report users cleaner

Is there any substitute that can enforce that a package only reads from the database?


texts: Cursor doesn't print text when I tell it to

texts:
I have used WAITFOR combined with a cursor in the following script:

create table orders(orderId int primary key,productId int,productName varchar(20));

insert into orders values(1,11,'book');
insert into orders values(2,13,'flower');
insert into orders values(3,24,'microwave');
insert into orders values(4,7,'food');
insert into orders values(5,46,'clothes');

declare cur cursor for
select * from orders;
open cur;
fetch next from cur;
while(@@fetch_status=0)
begin
    waitfor delay '00:00:01';
    fetch next from cur;
end;
close cur;
deallocate cur;

I'm looking to display one record from the cursor per the specified interval of 1 second. For example: display the first row, then wait a second; display the second row, then wait a second; and so on. The cursor above does not do that. Say orders has 5 rows. The WHILE block waits 5 seconds and then displays all records together.

Is this the cursor's fault?  How can I modify this script to work as intended?


texts:
According to this  answer, it's not possible to have a fully secure database encryption with pgcrypto.

Now, 2 years later (in postgresql 9.3)  is there any new extension or new method to have a secure database encryption, or doing this on the application server is still the only way to be sure that someone with database access cannot decrypt the data?


texts: How can i encrypt or hash a password inside a trigger using sha-256 so that i can store the hashed value in another table using that trigger? Also how can I decrypt it??


texts:
I've got a table which contains object_id, object_name, derived_from_object_id

What function(s) do I need to look at to be able to provide a total count of derivations based on a provided object_id?

Example

1    object1    0
2    object2    1
3    object3    1
4    object4    0
5    object5    3
6    object6    2
7    object7    3
8    object8    5

There is no defined maximum number of derivations from an object or the children. So if I wanted a total count from objectid 1 the result would be 7.

I've thought about writing a stored procedure which simply returned a count for each object id, it would then call itself for each id that there was a child for. Whilst I think this would work I think it's completely the wrong direction to go.


texts: Select TOP N Rows missing from SSMS temporal table

texts:
I am using temporal tables within my database and when I right click on my table  in Management Studio 2017 (v17.4 14.0.17213.0) I do not see the Select Top 1000 Rows in the context menu (there is no problem with non temporal tables)

Any ideas how to get this context menu back? I have a feeling it is to do with the version of SQL Server I am running (SQL 13.1.4001.0 Express Edition)


texts: BACKUP AND RESTORE FileGroup (PRIMARY) - WHY after restore I still see Other FileGroups?

texts:
/********************************** CREATE DATABASE PiecemealDB *************************************/
use [master]
go
if exists (select * from sys.databases where database_id = DB_ID('PiecemealDB'))
    drop database [PiecemealDB]
go
CREATE DATABASE [PiecemealDB]
    CONTAINMENT = NONE
    ON  PRIMARY
( NAME = N'PiecemealDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PiecemealDB.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB )
    LOG ON
( NAME = N'PiecemealDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PiecemealDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
    COLLATE Latin1_General_100_CI_AS
GO

USE PiecemealDB
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY')
ALTER DATABASE [PiecemealDB] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
-----------------------------------------------------------------------------------------------------
use PiecemealDB
go
/* Creating table in PRIMARY FG & init data*/
if OBJECT_ID('dbo.MainFG') is not null
    drop table dbo.MainFG;
go
create table dbo.MainFG (Col1 int identity(1,1),Col2 varchar(100)) ON [PRIMARY];
go

insert into dbo.MainFG(Col2)values ('Primary FG')
go 100
/*******************************ADDING NEW FileGroup [ReadOnlyFG] ***********************************/
USE [master]
GO
ALTER DATABASE [PiecemealDB] ADD FILEGROUP [ReadOnlyFG]
GO
ALTER DATABASE [PiecemealDB] ADD FILE
    ( NAME = N'ReadOnlyDF_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ReadOnlyDF_Data.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) TO FILEGROUP [ReadOnlyFG]
GO
/******************************* create table under the new filegroup [ReadOnlyFG] ******************/
use PiecemealDB
go
if OBJECT_ID('dbo.ReadOnlyFG') is not null
    drop table dbo.ReadOnlyFG;
go
create table dbo.ReadOnlyFG (Col1 int identity(1,1),Col2 varchar(100)) ON [ReadOnlyFG];
go
/*************** Insert data into table under FileGroup [ReadOnlyFG] *******************************/
use PiecemealDB
go
insert into dbo.ReadOnlyFG(Col2)values ('ReadOnlyFG FG')
go 100
/********************************** SET ReadOnlyFG FileGroup to READ ONLY **************************/
USE [PiecemealDB]
GO
ALTER DATABASE [PiecemealDB] MODIFY FILEGROUP [ReadOnlyFG] READONLY
GO
/********************************** BACKUP PRIMARY FILEGROUP **************************************/
BACKUP DATABASE [PiecemealDB]
    FILEGROUP = N'PRIMARY' TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\PiecemealDB.bak'
    WITH NOFORMAT, NOINIT,  NAME = N'PiecemealDB-Full Filegroup Backup',
    SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO
/********************************** RESTORE FILEGROUP PRIMARY  TO [PiecemealDB_NEW] **********************************/
use [master]
go

RESTORE DATABASE [PiecemealDB_NEW] READ_WRITE_FILEGROUPS
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\PiecemealDB.bak' WITH  FILE = 2, RECOVERY, PARTIAL,
MOVE N'PiecemealDB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PiecemealDB_NEW.mdf',
MOVE N'PiecemealDB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PiecemealDB_NEW_1.ldf',
NOUNLOAD,  REPLACE,  STATS = 10
GO

** After you exec this batch,Go to PiecemealDB_NEW properties >> Filegroup TAB :

Question: How can I drop/delete the Other Filegroup [ReadOnlyFG].
1. Is there any way to restore only the PRIMARY filegroup , without the ReadOnlyFG ?.
2. If not, How can I get rid of the ReadOnlyFG after the restore ?.

10X


texts:
I am struggling with the database design for a students / courses administration tool.

Students can book different courses, e.g. course A, B, C...

To complete a course you have to meet some specific requirements like a minimum number of general lessons plus course specific lessons. For each course you have different figures.

  e.g. course A --> min. general lessons 10, course specific lessons 4

  e.g. course B --> min. general lessons 10, course specific lessons 2

Now my problem: You can book e.g. A and B together then you will get a credit of one time 'min. general lessons 10, though you have only:

  course AB --> min. general lessons 10, course specific lessons 4 (course A) plus course specific lessons 2 (course B)

Any ideas for a proper table layout?


texts: create a login or user with sa permissions or existing ones escalated to sa

texts: If someone in SQL Server, creates a login or user with sa permissions or existing login or user permission escalated to sa. I need to get the details in to the table who are escalated to sa permission and logins or users with sa permissions recently created on a daily basis


texts: Where clause from OUTER APPLY using LEFT function

texts:
What would be a good alternative to improve my where clause that currently uses the LEFT function?

Should I create a persisted computed column and an index to it?

SELECT --columns--
FROM DesvioDesenhoSubstitutivoProposto DSP
LEFT JOIN DesvioDesenhoSubstitutivo DS ON DS.idSubstituicaoDesenho = DSP.idSubstituicaoDesenho OUTER APPLY
  (SELECT TOP 1 D.Dendis
   FROM Interfaces.dbo.SSOLQ068 D
   WHERE DS.codigoDesenhoSubstitutivo = LEFT(D.NDISCOMPLFAB, LEN(D.NDISCOMPLFAB)-1)
   GROUP BY D.Dendis) AS DESEN

This is the current execution plan for this part:

PS: query has been simplified for posting.


texts: Does CPU utilization affect the cost of foreign NUMA access?

texts:
Scenario
Let's assume I have a SQL Server with 4 sockets with each 1 NUMA node. Each socket has 4 physical cores. There is 512 GB of memory total so each NUMA node has 128 GB of RAM.
A key table is loaded into the first NUMA node.
Question
Let's assume we have a lot of traffic reading from that table. If all physical cores of the socket that owns the NUMA node have 100 percent CPU utilization, does that negatively influence the cost of non-local NUMA access coming from other sockets? Or on the other hand is the cost of non-local NUMA access is irrespective of how busy that socket is?
I hope my question makes sense. Please let me know if it doesn't I will try to clarify.
Background
We had a database issue in our production server last week and some of our business processed appeared more impacted than others. We had queries with few logical reads taking more than 1 minute. We looked at overall CPU utilization which was around 60 percent. We did not look at socket specific CPU metrics. I/O metrics were average.


texts: How to index range condition and ordering by a different colum?

texts:
I use PostgreSQL and i have a table like this:
create table transactions (
  uuid uuid,
  user_id integer,
  created_at timestamp,
  amount numeric
);
create index idx_transactions_user_id_created_at on transactions (user_id, created_at);

I'd like to get the user's "biggest transactions" for the past N days with a query like this:
select *
from transactions
where user_id=1234
  and created_at >= current_timestamp - interval '7 days'
order by amount desc
limit 10;

The user can choose the number of days and the limit he is interested in.
The query plan is like this:
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Limit  (cost=9.54..9.55 rows=2 width=60)
   ->  Sort  (cost=9.54..9.55 rows=2 width=60)
         Sort Key: amount DESC
         ->  Bitmap Heap Scan on transactions  (cost=4.18..9.53 rows=2 width=60)
               Recheck Cond: ((user_id = 1234) AND (created_at >= (CURRENT_TIMESTAMP - '7 days'::interval)))
               ->  Bitmap Index Scan on idx_transactions_user_id_created_at  (cost=0.00..4.17 rows=2 width=0)
                     Index Cond: ((user_id = 1234) AND (created_at >= (CURRENT_TIMESTAMP - '7 days'::interval)))
(7 rows)

So postgres gets all rows which match the user_id and the created_at range, than sorts all that and returns the top N.
Is there's a better way to index this? So it scales nicely when there are millions of users and millions of transactions per user?
So far my query is not too slow, but i just wonder if it is theoretically possible to index this table in such a way that postgres will know exactly which 10 row to read from the disk for arbitrary created_at condition?


texts: mysql innodb buffer pool instances always 1

texts:
I set in my.ini

innodb_buffer_pool_instances=8

However, no matter how I change the innodb buffer pool size. I check the
innodb_buffer_pool_instances in mysql workbench, it always equal to 1. I cannot find a way to change the instances number. What is wrong? I am using Mysql 5.7.12.


texts: Restore DB from SQL Server 2008 R2 to 2014 Express

texts: I'm trying to restore a database backup made in SQL Server 2008 R2 to the 2014 Express version. Is this even possible? If I choose restore from device and select my backup file, I see no database in the drop down.


texts: Segregating email ids of each customer (if more than one) by creating the new lines/records for that customer

texts:
My customer Database contains more than one email id in “Email” Field due to which my emails getting bounced. I want to have only one email id in “Email” Field but at the same time, I don’t want to lose any other email id also.

I want that if there are more than one email id in “Email” field then new lines (equivalent to total no. of email ids) to be created for that customer with one email id only in “Email” field and data in the rest of the fields should be copied as it is for that particular customer.

Following is the script.

CREATE TABLE mytable (
    NAME VARCHAR(26) NOT NULL PRIMARY KEY
    ,City VARCHAR(10) NOT NULL
    ,EMail VARCHAR(79) NOT NULL
    ,Contact VARCHAR(33) NOT NULL
    );

INSERT INTO mytable
(NAME, City, EMail, Contact)
VALUES
('EFG', 'Chennai', 'eminent5sales@gmail.com;eminentyusuf@gmail.com', 'Mr. Y'),
('ABC', 'AHMEDABAD', 'priyalcorporation@gmail.com', 'Mr. N'),
('XYZ', 'Ahemadabad', 'Dhruv_Patel@milacron.co;mayur_a_patel@milacron.com;Bharat_R_Parekh@milacron.com', 'Mr. J');

Expected Output

+------+------------+------------------------------------------------+---------+
| Name |    City    |                     E-Mail                     | Contact |
+------+------------+------------------------------------------------+---------+
| EFG  | Chennai    | eminent5sales@gmail.com;eminentyusuf@gmail.com | Mr. Y   |
| EFG  | Chennai    | eminentyusuf@gmail.com                         | Mr. Y   |
| ABC  | AHMEDABAD  | priyalcorporation@gmail.com                    | Mr. N   |
| XYZ  | Ahemadabad | Dhruv_Patel@milacron.co                        | Mr. J   |
| XYZ  | Ahemadabad | mayur_a_patel@milacron.com                     | Mr. J   |
| XYZ  | Ahemadabad | Bharat_R_Parekh@milacron.com                   | Mr. J   |


texts:
A logon trigger was added and dropped from our production database. I have full backup of master after the trigger was added, and before it was dropped. I am trying to recover from the backup but I cannot see it under the server_triggers of that backup. Is there a way to recover logon trigger from a DB backup?

The backup is from 11PM yesterday night. Trigger was added yesterday morning and dropped today morning.


texts: Best way to implement Hierarchical data in SQL Server with multiple parents

texts:
How can I implement Hierarchical data where a child has two or more parents?

Something like this

Parent ID       Child ID
1               2
1               3
3,2             4
4,1             5


texts: My MongoDB Atlas Operations graph has doubled in the last few hours. How do I go about debugging this?

texts:
I noticed this graph in my MongoDB Atlas dashboard:

I'm not sure what is causing the increased Disk IOPS and Operations counts... Can I use the oplog to figure out what is causing this massive increase in operations?