texts: Automatically invoke `SET ROLE` when connecting to PostgreSQL

texts:
The short version first:

Is it possible to have a new PostgreSQL database connection automatically invoke SET ROLE with a specific role, whether by configurations on the connecting role (using ALTER ROLE), or an option on the end of a connection URI?

Longer, with context:

I'm setting up a web application to use rotating database credentials (thus, there are a variety of roles in play). However, these credentials are also used for modifications to the database (via Rails migrations), and that means tables become owned by a role that isn't meant to exist in the long-term.

I can modify the rotating credentials so they inherit from a parent role (which doesn't have the ability to log in itself), and then via SET ROLE all database modifications are owned by the parent, rather than the short-term child role. This resolves the ownership issue, but it requires every connection to invoke SET ROLE parent - not really feasible.

Hence, I want some way to ensure that every child connection will always operate within the context of the parent role. Is this possible?


texts: How to scatter/arrange all the tables in database model (workbench)?

texts:
I have around 71 tables and if i open them for creating a model in workbench all overlap as below

If i use the auto layout under arrange section then max i can get is as below

How to auto arrange them without any overlapping ?


texts: Error when trying to install SQL Server 2012 Ent in to a Server Core 2008 VM

texts:
I have a problem that I hope you can help with.

I am studying for my 70-462 exam and in order to do so, must setup virtual labs as follows.

DC: Windows Server 2008 R2 Enterprise (Full Installation) - Domain controller for the lab network, contoso.com
SQL-A: Windows Server 2008 R2 Enterprise (Full Installation) - Domain member for hosting SQL Server
SQL-B: Windows Server 2008 R2 Enterprise  (Full Installation) - Domain member for hosting SQL Server
SQL-C: Windows Server 2008 R2 Enterprise (Full Installation) - Domain member for hosting SQL Server
SQL-D: Windows Server 2008 R2 Enterprise (Full Installation) - Domain member for hosting SQL Server
SQL-CORE: Windows Server 2008 R2 Enterprise (Server Core Installation) - Domain member for hosting SQL Server

I have created all of these VMs and installed the versions of Windows Server listed. I have also created the domain and joined VMs SQL-A, SQL-B and SQL-Core as well as creating a domain user on the Domain Controller called Contoso/Kim_Akers. I am able to log in to all of the VMs with this user.

I have since installed SQL Server 2012 Ent on SQL-A and SQL B, but get the following error when trying to install SQL-Core.

Overall summary
Final result:                  Failed: see details below
Exit code (Decimal):           -2068578304
Exit facility code:            1204
Exit error code:               0
Exit message:                  The Windows account Contoso/Kim_Akers does not exist and cannot be provisioned as a SQL Server system administrator.
Start time:                    2016-07-06 20:02:52
End time:                      2016-07-06 20:04:17
Requested action:              Install

If I run whoami on the SQL-Core VM it comes back as follows:

Consto.Kim_Akers

If I run SConfig, it shows that I am connected to the Contoso domain, so I don’t understand where the error is coming from.

To provide further background on this, the command I am running to install SQL Server 2012 Enterprise on the Server Core 2008 VM is as follows.

Setup.exe /qs /Action=install /Features=SQLEngine,IS,Conn
 /InstanceName=MSSQLServer /SQLSYSADMINACCOUNTS="Contoso\kim_akers"
 /IAcceptSQLServerLicenseTerms

What may be wrong with my setup?


texts: How to convert rows into columns in sql server using pivot or with out pivot

texts:
How to convert rows into columns in sql server?

This is my query output:

I need like..

ServiceName             CLO          IC             AC
---------------------   ---------    -----------    -----------
HeatExchanged           27933.562    133217.4152    146385.4261
MTD                     74.07        57.82          55.17
PerfTube_PressureDrop   3.83 / 0     1.21 / 0       2.63 / 0
Design_TubeOD           0.75         0.625          0.625
Design_NoTubes          7            41             32

Here ServiceName is dynamic value.

I tried:

select QuoteServiceID,HeatExchanged, MTD, PerfTube_PressureDrop,
     Design_TubeOD, Design_NoTubes
from
(
  select QuoteServiceID, HeatExchanged
  from Quotes_Output_Thermal
) d
pivot
(
  max(QuoteServiceID)
  for HeatExchanged in (QuoteServiceID,HeatExchanged, MTD,
       PerfTube_PressureDrop, Design_TubeOD, Design_NoTubes)
) piv;

Here are the DDL commands

CREATE TABLE Quotes_Output_Thermal
(
    ServiceName nvarchar(50) NULL,
    HeatExchanged nvarchar(50) NULL,
    MTD nvarchar(50) NULL,
    PerfTube_PressureDrop nvarchar (50) NULL,
    Design_TubeOD nvarchar (50) NULL,
    Design_NoTubes int NULL
);

INSERT dbo.Quotes_Output_Thermal
(
  ServiceName, HeatExchanged, MTD, PerfTube_PressureDrop,
  Design_TubeOD, Design_NoTubes
)
values('CLO','27933.562',  '74.07', '3.83 / 0', '0.75',  7),
      ('IC', '133217.4152','57.82', '1.21 / 0 ','0.625',41),
      ('AC', '146385.4261','55.17', '2.63 / 0', '0.625',32);

Help me, how can I achieve this?


texts: Creating domain user login from another domain?

texts:
I have two domains named "Domain1" and "Domain2". I have SQL server 2005 in Domain2, and users in Domain1.

How can I create a login in SQL Server 2005 (in Domain2) for the user that is present in Domain1?


texts:
I have configured log shipping in sql server. During logshipping the size of log file increased. And I need to shrink log file. for that I have to change database to simple. when I will change the recovery mode from Full to simple chance of break of log shipping will be maximum as I have read in Microsoft guide.

Is it possible to shrink log file in database when it is being used for log shipping?


texts: Domain Admin on AWS lacking user rights to edit the top 200 using the Management Studio interface

texts:
I setup an application + SQL Server on AWS. Everything works fine my SQL User can read/write to any table.

Now I connected to the SQL Server using my domain admin account using windows authentication in SQL Server. It works fine and I can insert / update / delete rows. But as soon as I try to edit a table using the interface, meaning select "Edit Top 200 Rows" I get the following error:

The requested operation cannot be completed. The computer must be
trusted for delegation and the current user account must be configured to
allow delegation.

In the local Security Policy I have enabled domain Admins and also my exact account to the following and also all admins:

Enable computer and user Accounts to be trusted for delegation

What else am I missing? I use SQL Server 2014 Standart on Windows Server 2012


texts: Need the name value of the enterprise to be the same as it was when it was registered and not the value it currently has

texts:
I will explain the problem with an example: I am designing a specific case of referential integrity in a table. In the logical design there are two tables, enterprise and document. We register the companies and then someone inserts the documents associated with it. The name of the enterprise is variable.

When it comes to recovering the documents, I need the name value of an enterprise to be the same as it was when it was registered and not the value it currently has.

The solution that I thought was to register the company again in each change with the same code, the updated name in this way would have the expected result, but I am not sure if it is the best solution. Can someone make a suggestion?


texts: MySQL using a index referencing a different column

texts:
I have a large table which uses a versioning system in the table. When a record is updated a new record is inserted with the old data and the original record is updated ( so to maintain the same primary key for the original record ).

In the new record there is a reference to the original record.

So to query the last record for a specific customer/user we need to do the following.

select * from  where business_key = 1234 and parent = id

I want add an index on business key does it make sense to make the index a multi-column index?

ALTER TABLE MyTable ADD INDEX NewIndx ( business_key ASC, parent ASC, id ASC )

Will the parent part in the index be used, because it is referencing the id and a constant?

Sample records

id | parent | data
1  | 1      | Last record
2  | 1      | previous record of 1
3  | 3      | Other record


texts: Why would an index covering only half of primary key be useful?

texts:
We have a primitive junction table to store a many-to-many relation between two other tables, their primary keys being bigint:

CREATE TABLE [dbo].[JunctionTable] (
    [ID-1] [bigint] NOT NULL,
    [ID-2] [bigint] NOT NULL,
    CONSTRAINT [PK_JunctionTable] PRIMARY KEY CLUSTERED ([ID-1] ASC, [ID-2] ASC)
)

When the Tuning Advisor was run on the database, it suggested (among many others, obviously) the following index to be added:

CREATE NONCLUSTERED INDEX [_dta_index_JunctionTable_8_296569782__K1] ON [dbo].[JunctionTable]
(
    [ID-1] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

And I wonder: is this index good for anything? AFAICT it is the left half of a clustered primary key and the space reduction coming from omitting a single bigint cannot be that important, I guess. Or, is there any scenario when this index would help? And if not, why would the DTA suggest it? (The Advisor suggested many new indexes which I consider silly, but this one seemed the silliest, so I thought I ask…)


texts: MySQL rows in explain extended keeps increasing while the rows are being deleted continuosly

texts:
I have a table in Mysql with userid, status and other columns. I keep updating the status of the row as and when the job is complete. Periodically these rows are deleted from the table.
JobTable (userid, status, ...)
   userid_status_index (userid, status)

When I run some queries:
   select count(*) from JobTable where userid="1" and status="ready"
   Result: 20

For explain:
   explain extended select count(*) from JobTable force index (userid_status_index) where userid="1" and status="ready"
   rows: 2500

When I do explain, the number of rows keeps increasing (as I keep inserting, but the deleted rows are probably not removed from the index), but when i do count(*) on the actual table, it shows lesser rows.
After few rows, the DiskIOPS increases and things are back to normal. I suppose this is due to some change buffer update. Can I force it to happen more periodically so that the queries are not slow?
Forcing the index also doesn't help.
Also note that the queries are actually slow when it's run, not just the explain is incorrect.
Mysql 5.6


texts: MySQL compare two columns value at the same row

texts:
I'm newbie in SQL, I wonder if its possible to compare the values of two columns in the same row(s) using MYSQL.

e.g. I have a table called "Table" with two columns "data_a" and "data_b" which both are (int)

I want to get "data_a" value bigger than "data_b" value .

data_a   data_b
----------------
10        5
----------------
6         4
----------------
2         8
----------------
1         9


texts: UPDATE performance where no data changes

texts:
If I have an UPDATE statement that does not actually change any data (because the data is already in the updated state). Is there any performance benefit in putting a check in the WHERE clause to prevent the update?

For example would there be any difference in execution speed between UPDATE 1 and UPDATE 2 in the following:

CREATE TABLE MyTable (ID int PRIMARY KEY, Value int);
INSERT INTO MyTable (ID, Value)
VALUES
    (1, 1),
    (2, 2),
    (3, 3);

-- UPDATE 1
UPDATE MyTable
SET
    Value = 2
WHERE
    ID = 2
    AND Value <> 2;
SELECT @@ROWCOUNT;

-- UPDATE 2
UPDATE MyTable
SET
    Value = 2
WHERE
    ID = 2;
SELECT @@ROWCOUNT;

DROP TABLE MyTable;

The reason I ask is that I need the row count to include the unchanged row so I know whether to do an insert if the ID does not exist. As such I used the UPDATE 2 form. If there is a performance benefit to using the UPDATE 1 form, is it possible to get the row count that I need somehow?


texts:
I have a sql server 2005 database server.  I have administrative access(sa access) however I need the password to a specific account on the server.  I can't really reset the password to this account because it would break some applications.

Is there anyway to get the decrypted password from the sql server?


texts: T-SQL: If parameter1 is null, use parameter2

texts:
This is a simple one but I can't seem to figure it out.

I have two parameters, 1 and 2. If one parameter is passed a null value, use the other parameter in there WHERE clause.

Ex:

SELECT ...
 WHERE CASE
    WHEN @Parameter1 IS NULL
    THEN Field2 = @Parameter2
    WHEN @Parameter2 IS NULL
    THEN Field1 = @Parameter1
  END

I know this is something simple or maybe I'm using my logic wrong. Any help or direction is appreciated.


texts: What is the difference between ASYNC_IO_COMPLETION and IO_COMPLETION wait types?

texts: Could someone explain what is the main difference between these two wait types?


texts: Table with identity column in merge replication

texts:
I have a table in SQL Server 2005 and I have an IDENTITY(1,1) property set for an ID column.  Records are inserted in a table at a local server as well as a remote server.  I am using Merge Replication to match the data from local server and remote server.

What happens if I insert the same ID at the local server and the remote server?

Please suggest what am I doing wrong here.


texts: How to get the maximum amount of memory (RAM) consumed by a single query?

texts:
I know this topic is pretty complex and involves a lot of different factors. Let's say I have several similar queries running at the same time. These queries involves only read operation and several ordering and windowing. If I'm not wrong, with EXPLAIN ANALYZE (BUFFERS) I can get the memory used by some operations like ORDER BY. I need to know how a single query impacts in order to calibrate (and kind of predict) the amount of queries I can run at the same time.

Is it possible to get the maximum amount (not the total amount) of memory used by a single query? Should I get it from the EXPLAIN ANALYZE output (which gives me only some memory usage infos) or there's a better way?

I'm using Postgres 9.5 and 9.6 on different Unix-like environments like Red Hat 6.7, Ubuntu 16.0.4 and macOS Sierra.


texts: How to select only one database in a policy in SQL Server?

texts:
I am creating a new policy which shows sproc creation dates. I am based on Stored Procedure facet and appropriate condition is created. Now I want this policy to include only one database in an instance

When I select new policy from the dropdown menu, which is next to every keyword, it shows this

But my point is not to create a new condition for all databases.

My question is, is it possible to to run a policy only for one database in an instance, and if yes, then how?

Thanks for your time!


texts: How to add CYCLE to BIGSERIAL in phpPgAdmin

texts:
I'm trying to apply CYCLE to a BIGSERIAL in phpPgAdmin.

I've seen how to do it with a query, but I'd much rather be able to do it from phpPgAdmin.

Can this be done with phpPgAdmin?  If so, how?  If not, how can CYCLE be applied to a pre-existing BIGSERIAL column?


texts: MySQL Cluster over WAN: Best Architecture Options?

texts:
My company is looking at a multi-data-centre solution for high-availability, fun, and profit.

We have businesses across the planet, with the need for MySQL database clustering solutions that can keep up with high-traffic over high-latency.  Bandwidth isn't really an issue for us.

Could you please recommend a MySQL Database Cluster Architecture that is able to cope with high-latency networks.  For instance, we have one Master at Sydney, AU, and another Master at New York, US - the latency between these two points can be up to a second or more.

We are currently using Percona Server 5.1, and we would like to stay this way, so I will place more value on answers that take this factor into consideration.


texts:
I have a simple mysql table which I use as a server booking system. A user can log in to a server, if he has a valid booking for that server. A sample data might look like this:

+----------+----------------+---------------------+---------------------+
| server   | user           | start               | end                 |
+----------+----------------+---------------------+---------------------+
| server01 | Alice,Bob      | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 |
| server02 | Carlos         | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 |
| server03 | Dan,Erin,Frank | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 |
+----------+----------------+---------------------+---------------------+

To check whether user Carlos has a booking for server02, I use the following SQL query:

SELECT 0, id FROM booking WHERE user = 'Carlos'
AND (( start < now() and end > now() ) AND server = 'server02') ;

But this query does not work if there are multiple (comma-separated) users. How can I check if Alice has booking for server01 ?


texts: Find Max(value) for each department using GROUP BY MYSQL

texts:
I want to print the name of student in each department who has the highest gpa,

Students(stuID: String, stuName: String, gender: String,
        birthdate: Date, enterYear: Year, gpa: Float)
Departments(deptName: String, numPhDs: Integer)
Majors(deptName: String, stuID: String, degreeProgram: String,
        attendYear: Year, attendSemester: String)

My query looks like

SELECT S.StuName, M.DEPTNAME
FROM Students S NATURAL JOIN MAJORS M
WHERE S.GPA=(SELECT MAX(S2.GPA) FROM STUDENTS S2)
GROUP BY DEPTNAME;

I am only getting one student's name and their corresponding department (MATHEMATICS). How can I get the student with the highest GPA for each department? Where did I go wrong on my query?


texts: FILESTREAM shared folder can not be accessed using listener name

texts:
I have AlwaysOn configured in SQL Server 2014 and enabled Filestream in both nodes (node 1 and node2) as shown here:

Enable and Configure FILESTREAM (MSDN Library)

I have named the shared folder name "MYSHAREDSQLFILES".

I can access the folder using below path whenever node 1 is Primary

   \\**node1**\MYSHAREDSQLFILES

So the user must know which node is primary before accessing the folder. I saw in MSDN that filestream is partially supported:

FILESTREAM and FileTable with AlwaysOn Availability Groups (SQL Server)

  In an AlwaysOn availability group, however, the name of the computer is virtualized by using a Virtual Network Name, or VNN. When the computer is the primary replica in an availability group, and databases in the availability group contain FILESTREAM data, then a VNN-scoped share is also created to provide access to the FILESTREAM data. This does not affect Transact-SQL access to FILESTREAM data. However applications that use file system APIs have to use the VNN-scoped share, which has a path in the following format:

    \\<VNN>\<filestream_share_name>

What is VNN here? I know its Virtual Network name but when i try it does not resolve. Is it virtual network from azure?

The link explains how to provide access
Need to make sense of FileTable permissions but did not work for me :(


texts: MySQL5.7 (Windows) how to install from zip file without the Data Directory?

texts:
This has probably been asked before, but I am struggling to find the answer.

Historically I have always installed MySQL (on Windows) from a template based on the mysql-xxx-win64.zip file.
I would basically

1) download the zip file,
2) copy the contents to the new location,
3) add in my template ini file,
5) add in my template mysql directory (with default users)
4) install a Windows Service,
5) start MySQL

This has worked fine for years (5.5 and earlier). But now I am starting to test 5.7, and I find that the zip file no longer contains a data directory?
If I try and create one by copying over from an existing database, I get an error.

Looking at the documentation I see that I am now supposed to initialize the data directory. But when I try this I get an error:

c:\MySQL\bin\mysqld.exe --initialize --user=mysql
mysqld: Can't change dir to 'C:\MySQL\DATA\' (Errcode: 2 - No such file or directory)

If I create the Directory it seems to work, but there is still no MySQL directory, so when I run mysql_upgrade or try and (start service then) login, it fails.

2016-05-16T09:03:16.222540Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist.

I found this post which says the password shouold be in the error log, but it isn't.

So, how am I supposed to install MySQL if they don't give us the data directory (and therefore mysql user etc. tables) anymore?

(and before someone suggests using the msi file - that's not an option as they are blocked on our network, and require me to jump through numerous hoops to even download it, let alone run it).

If I do: Copy MySQL Directory >> initialize >> install service >> start service, it seems to be ok, until I look in the error log and see that virtually every table has the wrong structure. But I can't run mysql_upgrade as I have no password, and mysql@localhost is access denied.

UPDATE: 2016-05-18

So I got hold of the MSI file, and (tried) to install using that. Eventually, I managed to get a copy of MySQL 5.7 installed and running. So now I need to install a second copy. The MSI installer fails at this point as I 'already have a copy installed', with no option that I can see to install another copy.

So I copied the MySQL Directory to a new folder, removed the DATA directory, updated the ini file, and ran c:\Mysql\...\mysqld.exe --initialize.

That re-created the DATA directory for me.
I then set it up as a Service c:\Mysql\....\mysqld.exe --install MySQL57-2 --defaults-file=c:\MySQL\...\my.ini

I got the temporary password from the error log, and tried to log in, only to be told ERROR 1130 (HY000): Host '::1' is not allowed to connect to this MySQL server.

So I stopped the service, started Mysqld using --skip-grant-tables, logged in, added a new user, logged out, restarted the service. But still can't log in.

If it's going to be this much hassle every time I need to install a new Database, I think I'll just stick to 5.5.


texts:
Unable to start mongod daemon after upgrading OS from Ubuntu 16 to 18
root@dummy2:~# mongod --version
    db version v4.0.4
    git version: f288a3bdf201007f3693c58e140056adf8b04839
    OpenSSL version: OpenSSL 1.1.1  11 Sep 2018
    allocator: tcmalloc
    modules: none
    build environment:
        distmod: ubuntu1804
        distarch: x86_64
        target_arch: x86_64

root@dummy2:~#
cat /etc/*-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=18.04
DISTRIB_CODENAME=bionic
DISTRIB_DESCRIPTION="Ubuntu 18.04.4 LTS"
NAME="Ubuntu"
VERSION="18.04.4 LTS (Bionic Beaver)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 18.04.4 LTS"
VERSION_ID="18.04"

root@dummy2:~# /usr/bin/mongod --config /etc/mongo/db8303/mongodb.conf &
/usr/lib/x86_64-linux-gnu/libcurl.so.4: version `CURL_OPENSSL_3' not found (required by /usr/bin/mongod)

The difference that I'm seeing before and after OS upgrade on libcurl version:
before:
root@dummy2:~# ls -lrt /usr/lib/x86_64-linux-gnu/libcurl*
/usr/lib/x86_64-linux-gnu/libcurl.so.4 -> libcurl.so.4.4.0
/usr/lib/x86_64-linux-gnu/libcurl.so.3 -> libcurl.so.4
/usr/lib/x86_64-linux-gnu/libcurl-gnutls.so.4 -> libcurl-gnutls.so.4.4.0
/usr/lib/x86_64-linux-gnu/libcurl-gnutls.so.3 -> libcurl-gnutls.so.4
/usr/lib/x86_64-linux-gnu/libcurl.so.4.4.0
/usr/lib/x86_64-linux-gnu/libcurl-gnutls.so.4.4.0

After:
root@dummy2:~# ls -lrt /usr/lib/x86_64-linux-gnu/libcurl
/usr/lib/x86_64-linux-gnu/libcurl.so.4 -> libcurl.so.4.5.0
/usr/lib/x86_64-linux-gnu/libcurl-gnutls.so.4 -> libcurl-gnutls.so.4.5.0
/usr/lib/x86_64-linux-gnu/libcurl-gnutls.so.3 -> libcurl-gnutls.so.4
/usr/lib/x86_64-linux-gnu/libcurl.so.4.5.0
/usr/lib/x86_64-linux-gnu/libcurl-gnutls.so.4.5.0

Did anyone face similar issues ?
How to fix this issue ?


texts: Understanding `null` with Oracle Function's IF-Statement

texts:
Given the following Oracle function:

create or replace FUNCTION nullFunction(a NUMBER) RETURN NUMBER IS
  varX NUMBER;
  BEGIN
  select 1 into varX from dual where exists(select 1 from dual where a = 1);
    IF (varX = 1) THEN RETURN 1;
                  ELSE RETURN 0;
    END IF;
  END;

Then, I called it from a Oracle SQL Developer's worksheet:

select nullFunction(3) from dual returns (null).

Next, I modified my function to:

create or replace FUNCTION nullFunction(a NUMBER) RETURN NUMBER IS
  varX NUMBER;
  BEGIN
  select 1 into varX from dual where exists(select 1 from dual where a = 1);
    IF (varX is NULL) THEN RETURN 0;
                      ELSE RETURN 1;
    END IF;
  END;

But, I received the same output when calling it with a value of 3 - null.

Why does it return NULL? I would've expected it to have returned 0 since varX, which I'm assuming to be equal to null, as part of the else.


texts: Setting up a mirror: can't find remote server

texts:
I have troubles setting up a mirror, more precisely: i cant connect to the mirror :(

Here's my troubleshooting log :)

2 virtual Windows 2k12 Servers running in Microsoft Hyper-V, on a Windows 10 machine with SQL Server 2k12,
Neither are part of a domain and the firewalls are off
Machine A has a SQL database,
Machine B has a full backup of Machine A's database, restored with NO RECOVERY,
Both machines/SQL servers can be reached over the network; pinging works, tested database connectivity using SMSS on a third machine.
I followed the steps in this post when using the wizard didn't work out SQL Server Mirroring can't connect to mirror database. The accounts have sysadmin rights and enpoints are associated with the certifactes,
I'm sure the ports are opened, as shown by netstat -an  and further evidenced by using telnet to connect to the host/port combination,
Endpoints are in started state as shown in the mirroring endpoints table, also recycling the endpoints (i.e. stopping and restarting them) does not seem to have an effect on the issue
GRANTing CONNECT TO PUBLIC on the Endpoint isn't working either,

Still, running ALTER DATABASE [xxx] SET PARTNER = 'TCP:\\....:7023' results in the well known:

Msg 1418, Level 16, State 1, Line 1
The server network address "tcp://m.....:7023" can not
be reached or does not exist. Check the network address name and
that the ports for the local and remote endpoints are operational.

Any other insights that can apply here?

I'll post my own answer to this as well to make it obvious for other readers.

What I did wrong and this should have been very obvious, but apparently I missed this. The command: ALTER DATABASE [xxx] SET PARTNER = 'TCP:....:7023' Should have been run first on the mirror and second on the principal.

So in the end, my networking was, indeed, fine, but a blatant mishap when initiating mirroring on my part.


texts: mysqldump --no-data and --single-transaction

texts:
I am using mysqldump to take snapshots of the structure of a MySQL 5.0.77 database. I don't want the data, so I use the --no-data option.

If I use --single-transaction am I needlessly locking tables?


texts: Unable to access OEM 11g after upgrading to Firefox ver 37.0.1

texts:
I upgraded my browser (Firefox) to the latest version 37.0.1. Thereafter, OEM 11g is not accessible. Here are the errors:

**Secure Connection Failed**
The connection to bdporagrid.bdpinternational.com:7799 was interrupted while the page was loading.
The page you are trying to view cannot be shown because the authenticity of the received data could not be verified.
    Please contact the website owners to inform them of this problem.

I did some research and tried many workaround but still unable to get it loaded. I have no problem with Chrome or IE loading the OEM11g webpage.

The URL is:https://xxxxx.yyyy.com:7799/em/console/targets

Workaround attempted:

Deleted the cert8.db in Firefox profile and then relaunch Firefox. --Failed to resolve
Delete or Distrust the OEM11g server certificate under Firefox --> Options --> Certificates tab --> View Certificates.
Relaunch firefox--Failed to resolve
I exported the OEM11g server certificate using IE. Then, I imported it into Firefox. Same error. --Failed to resolve.

Not sure if anyone has a workaround or solution. I am sure ver 37.0.1 has changed the https behavior.


texts: MySQL: How think about Null insert to foreign key column

texts:
I'm designing table relationship. I have an one question.

Parent: A table

Child: B table

Table: A
PK: id

---

Table: B
PK: id
Column: A_id(NOT NULL), desk, chair...
Option: CONSTRAINT `fk_B_A_id` FOREIGN KEY (`A_id`) REFERENCES `A` (`id`)
        ON DELETE NO ACTION ON UPDATE CASCADE

My case is, B table sometimes has A_id, sometimes doesn't have A_id.

So I'm trying to insert 0 for A_id when B table doesn't need the A_id value. But A table doesn't have id 0. As a result error occurs.

I'm thinking two solutions:

A_id can have null.
Dummy data (A.id=0) insert to A table when A table create.

If you have other idea Please tell me.

Thank you.


texts: List foreign keys dependencies that refer to a given table

texts:
RDBMS: PostgreSQL 9.6

I desire to list the foreign keys for a given table and schema. I am about 90 % there but I also desire to know what options were used to make the fkey. Example on delete restrict or on delete update

create or replace function it.table_foreign_keys_getlist(_schema text
                                                       , _table text)
                           returns TABLE(primary_key_ns text
                                       , primary_key_table text
                                       , foreign_key_ns text
                                       , foreign_key_table text
                                        )
     language sql
 as $$
 SELECT
     n1.nspname::TEXT  AS primary_key_ns,
     c1.relname::TEXT  AS primary_key_table,
     n2.nspname::TEXT  AS foreign_key_ns,
     c2.relname::TEXT  AS foreign_key_table
 FROM pg_catalog.pg_constraint c
 JOIN ONLY pg_catalog.pg_class c1     ON c1.oid = c.confrelid
 JOIN ONLY pg_catalog.pg_class c2     ON c2.oid = c.conrelid
 JOIN ONLY pg_catalog.pg_namespace n1 ON n1.oid = c1.relnamespace
 JOIN ONLY pg_catalog.pg_namespace n2 ON n2.oid = c2.relnamespace
 WHERE c1.relkind = 'r'
   AND c.contype  = 'f'
   AND CASE WHEN _schema = '' THEN TRUE ELSE n1.nspname = _schema END
   AND CASE WHEN _table  = '' THEN TRUE ELSE c1.relname = _table  END
 ORDER BY 1,2,3,4;
 $$;


texts: SQL query to find all parent records where all child records have a given value (not just some)

texts:
An Event has many Participants. A Participant has a field of "status".

I need to find all Events except the following ones: Events where every one of its participants has a status of 'present'.

I can find all Events where some of its Participants have a status of 'present' with the following SQL:

SELECT events.id, participants.status as status, count(*)
FROM `events` INNER JOIN `participants`
ON `participants`.`event_id` = `events`.`id`
WHERE (`participants`.`status` != 'present')
GROUP BY participants.event_id HAVING count(*) > 0

This almost works. The problem is that if one of the participant rows has a status of something other like "away", the event will still get fetched, because the sibling records are of a status equal to "present".

I need to ensure that I am filtering out every Event record with all Participants of a status of "present".

I am using PostgreSQL, so no non-PostgreSQL specific solutions please.

Thanks.


texts: generating scripts - Incorrect syntax near 'GO'

texts:
this is a partial view of my script to generate the drop index for a particular index

   ,[DropIndexScript] = 'DROP INDEX ' + QUOTENAME(SI.name) +
   + CHAR(13) + ' ON '  +
    QUOTENAME(Schema_name(T.Schema_id)) +'.'+ QUOTENAME(T.name) + CHAR(10) + CHAR(13) + 'GO' + CHAR(13)

FROM sys.indexes I
INNER JOIN (
              SELECT  Object_id
                     ,Schema_id
                     ,NAME
                     FROM sys.tables

              UNION ALL

              SELECT  Object_id
                     ,Schema_id
                     ,NAME
                     FROM sys.views

            ) T

           ON T.Object_id = I.Object_id

 INNER JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid

the question is:

when I use this:

+ CHAR(10) + CHAR(13) + 'GO' + CHAR(13)

It works - generates this script:

DROP INDEX [IDX_ProdImages_GetProductListingPageDenormalisedData]
 ON [dbo].[ProductImages]

GO

when I don't use the + CHAR(10) + CHAR(13) +

  Msg 102, Level 15, State 1, Line 38289 Incorrect syntax near 'GO'.

is there any other way to get this done?


texts:
I am having tempdb contention (I guess, at least) on one of the servers (SQL Server 2008 R2).

Both the blocker and the waiters (10 of them) has PAGELATCH_EX wait type on database tempdb and all the sessions' (blocker and the waiters) waitresource is "2:1:122".

I checked the SQL codes and saw that table variables are created in most of the sessions and I suspect that might be the culprit.

Any suggestions on how to diagnose further and alleviate this problem is appreciated.


texts:
Pretend that our collection has 1M document
Now if i do aggregation like this:

db.users.aggregate({
    $group: {
        _id : "$gender",
        avgAge : { $avg : "$age" }
    }
})

then how it will prepare the data for me?  Say i have a index on the id but not on the gender so is it gonna read all the 1M documents? if so then how does it scale?


texts: What is "Index Fragmentation Percent" a percentage of?

texts:
I am familiar with the concept of index fragmentation and how to fix it, but I'm not really sure of one specific item.

When you look at index fragmentation, it's always reported in terms of Percentage.

What is that Percentage a percent of?


texts:
I'm trying to export a database content to a dump file, so I'm doing the following:

[root@serverId TEST]# ps -ef|grep pmon|grep -v grep
oracle   25371     1  0 Sep29 ?        00:02:22 ora_pmon_SIDPA

[root@serverId TEST]# echo $ORACLE_HOME
/home/oracle/product/10.2.0

[root@serverId TEST]# echo $ORACLE_SID
SIDPA

[root@serverId TEST]# echo $PATH
/home/oracle/product/10.2.0/bin:/home/oracle/product/10.2.0//bin::/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/cca/CCA_SRV:/root/bin

[root@serverId TEST]# exp userId@//serverId:1521/SIDPA FILE=dumpuserId.dmp LOG=dumpuserId.LOG OWNER=userId

Export: Release 10.2.0.3.0 - Production on Wed Oct 21 09:10:56 2015

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Password:

EXP-00056: ORACLE error 12154 encountered
ORA-12154: TNS:could not resolve the connect identifier specified
EXP-00000: Export terminated unsuccessfully

[root@serverId TEST]#

As you can see I already set the env vars $ORACLE_HOME and $ORACLE_SID as far as I understand correctly. I also set the PATH, so I think it should work but unfortunately, I does not.

Any comments about what to try will be welcomed.

Thanks in advance!


texts: Eliminating OIDs while upgrading PostgreSQL from 9.4 to 12

texts:
I'm currently running PostgreSQL 9.4 in my production environment, but we're in the process of doing an OS upgrade on the server so I thought it might be time to upgrade our PostgreSQL version to the latest (12) so that I could potentially make use of some of the new features available. However, several of the tables in the various databases were built quite a while ago (as in, pre-2008, and quite possibly pre-2000 in at least some cases). These databases contain many tables using OIDs (WITH OIDS=TRUE in the definition).

Additionally, I've run a search through a majority of my code base to look for references to OID columns in any table and found a couple of instances where there are queries that explicitly call on the OID columns. Luckily, there aren't too many of these cases, and a majority of them are referencing system tables (i.e., SELECT oid FROM pg_namespace, ...FROM pg_class).

Running the pg_upgrade on the data dumped from our 9.4 database balks at this point in the process and flatly states that it won't continue until the database is free of OIDs. I understand that they've been slowly phasing these out over the past few releases, and that 12 has made them basically irrelevant, but the suggestion from pg_upgrade is to drop the OID columns entirely before proceeding. I consider that to be "overkill" for a mostly irrelevant column.

At this point - and I readily admit that I haven't thought this all the way through yet - I'm wondering if it's sufficient to simply "flip" the WITH OIDS switch in the affected table definitions to FALSE to be able to proceed with the upgrade? Will the existing OID columns remain a part of the database structure if this is flipped (I would think they would, but automated upgrades can do some funny things)?

I realize that, in an ideal situation, I would want to eventually eliminate OIDs from the database completely. However, being a one-man IT department, that's going to have to go "on the list" for later evaluation. For now, I'd simply like to get the database up-and-running on the latest PostgreSQL version while I have the opportunity to do so without immediately affecting operations.


texts: looking for a lighter alternative to cognos express

texts:
I have a client that has about 10 staff. They want to add business intelligence services to an existing MySql database.

It is a database containing purchasing and readership data for publications over the past 30+ years. They are an Audit Bureau, and thus, on a yearly basis, they audit the numbers that publishers report so that these numbers are they sent to advertisers and can be used to justify the cost of ads in their publications.

I need a small- to mid-sized OLAP cube option. Cognos Express is the first one I'm trialing. It feels more like a mid-sized one than a smaller end one so I'm wondering if anyone knows of any alternatives.


texts: Could not find a log backup file that could be applied to secondary database

texts: Was getting this error when trying to do a Log Shipping Restore, even though I had a bunch of TRN files that were sequential. Seems that the filename of the log file are required to be in the following format [database_name]_[utc_time_stamp].trn. Any other filename format will result in SQL Server not being able to find the file.


texts: Moving a large table from Localhost to remote server

texts:
Postgres 11, Windows 10, pgAdmin 4

I have a large post-gis enabled table (29million rows, 20GB + a 5GB spatial index) stored on my local machine (localhost).

I want to move this table to an AWS hosted server instance (also Postgres). The table has 2 columns, and id column and a geometry column.

Attempt 1: Using dblink, the transfer doesn't work:

CREATE TABLE destschema.dest_table AS
SELECT fid, wkb_geometry FROM dblink('host=localhost
                     user=postgres
                     password=password
                     dbname=sourcedb',
                     'select fid, wkb_geometry
                    from sourceschema.sourcetable') as linktable(
                    fid VARCHAR,
                    wkb_geometry geometry)

Error is: 'FATAL: password authentication failed for user "postgres"'

Even though the localhost connection credentials are correct. Why does it throw this error? I have previously had success using dblink to transfer tables from one remote cloud server to another remote cloud server. It just seems to not like moving a table from localhost to a remote server.

Attempt 2: using pg_dump to create a .backup file of the table (works ok), then upload it to the destination server using psql:

psql -U your_user_name your_db_name < your_dump_file

This method took 12 hours to insert 5million rows, so I cancelled the process. I don't want to have to wait 72 hours to move this table...

Attempt 3: Using PgAdmin's interface to backup the table to a .backup file and then restoring that .backup file to the destination database. However, after waiting 6 hours it was still uploading... I stop the process.

Attempt 4: Eventually I got around this issue by purchasing DataGrip (by Jetbrains) and exporting the 29mil rows to 3 CSV files, then importing the data back into the destination db. All the while using the DataGrip GUI. The exporting phase took around 8 mins in total, the uploading took 2.5 hours in total. Then I had to manually add a spatial index to the new table.

What is the quickest, simplest way of moving a large table from localhost to remote? Surely I've missed a trick or two...


texts:
I'm writing a series of plpythonu functions which share a few common bits of code. I'd like to package the shared code into a separate module for ease of maintenance. Is there an easy way to call my custom module from plpythonu?

I can't guarantee access to locations in Python's path so dropping it in a folder on the path may not work.

These are all part of a PostgreSQL extension so ideally I'd install the helper Python module along with the other extension files.


texts: Missing Database Mail Configuration Wizard in SQL Server 2008 R2

texts:
I was able to verify that Database Mail is installed and running but when I expand the management folder there is no Database Mail Configuration Wizard.

The login is a sysadmin.  I am very green to using SQL but I have to fight my way through this first project before I can step back and actually learn it.


texts: Cannot deploy SSIS packages after upgrade to SQL Server 2016 SP2 Build 5201.2

texts:
We recently upgraded from SQL 2016 SP1 build 4466 to SP2 build 5201 (CU2 GDR) on an AG.

On one server an analyst was deploying SSIS using Visual Studio Enterprise 2015 (version 14.0.25431.01 Update 3 and MS. NET framework 4.7.02558).

Since the SQL upgrade they get this error message:

  Failed to deploy project. For more information , query the operation_messages view for the operation identifier '777'. (Microsoft SQL Server, Error: 27203)

Querying the operations_messages table in the SSISDB database for 777 I get this message:

  Failed to deploy the project. Fix the problems and try again later.:Cannot use SAVE TRANSACTION within a distributed transaction.

That message appeared as fixed in SP2 CU1 in this KB https://support.microsoft.com/en-us/help/4092554/fix-cannot-use-save-transaction-within-a-distributed-transaction-when

The analyst says she gets the same error if she uses Management Studio and the IS Deployment Wizard.


texts: MySQL: How to write a recursive function to show menu tree?

texts:
I try to build a menu tree inside the SQL with this function:

DROP FUNCTION IF EXISTS hello_world;
DELIMITER $$
CREATE FUNCTION hello_world(_typeId INT, _typeText TEXT)
  RETURNS TEXT
BEGIN
    DECLARE _parentId INT;
    DECLARE _typeContent TEXT;

    SELECT parent_type_id, title INTO _parentId, _typeContent
    FROM fcom_product_type WHERE id = _typeId;

    RETURN CONCAT( IF(_parentId IS NOT NULL,' / ', 'not found'), _typeContent);
END;
$$
DELIMITER ;

If I want call my function again in case it finds a "parent", MySQL says the follow error to me.

DROP FUNCTION IF EXISTS hello_world;
DELIMITER $$
CREATE FUNCTION hello_world(_typeId INT, _typeText TEXT)
  RETURNS TEXT
BEGIN
    DECLARE _parentId INT;
    DECLARE _typeContent TEXT;

    SELECT parent_type_id, title INTO _parentId, _typeContent
    FROM fcom_product_type WHERE id = _typeId;

    RETURN CONCAT(
      IF(_parentId IS NOT NULL,
         hello_world(_parentId, CONCAT(_typeContent, ' / ') ),
         'not found'),
      _typeContent, ' - ', _parantId);
END;
$$
DELIMITER ;

Error: Recursive stored functions and triggers are not allowed.


texts: Why my database mirror break down after changing file group settings from RESTRICTED_USER to MULTI_USER?

texts:
My environment is the following:
VMWare 5.5 vitalized server MS Windows Server 2008R2 Enterprise domain and SQL Server 2008 R2 Enterprise.
Centralized storage with fibre-channel connection.

I have partitions in my SQL Server DB. I have 2 file groups: one with live data (FG1), second with historical data (HDG).

The second file group is read-only. Each month I make movement in partitions - I add new data (from previous month) to historical data. This process is automatic.

We moved our database to a new server. Initially, I had to do the process manually. During this operation my mirror breaks down (after operation 3 - see process flow bellow) with the following error:

ON PRINCIPAL SERVER:

ROW 0 in LOG:

Date        15.6.2015 20:54:11
Log     SQL Server (Current - 16.6.2015 07:55:00)

Source      spid84

Message
Setting database option MULTI_USER to ON for database MYDB.

ROW 1 in LOG:

Date        15.6.2015 20:54:11
Log     SQL Server (Current - 16.6.2015 07:55:00)

Source      spid18s

Message
Error: 1453, Severity: 16, State: 1.

ROW 2 in LOG:

Date        15.6.2015 20:54:11
Log     SQL Server (Current - 16.6.2015 07:55:00)

Source      spid18s

Message
'TCP://10.201.27.154:5022', the remote mirroring partner for database 'MYDB', encountered error 823, status 3, severity 24. Database mirroring has been suspended.  Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance.

REMARK: I executed this operation on the old server many times automatically and I never experience such error.

ON MIRROR SERVER:

ROW 1 in LOG:

Date        15.6.2015 20:54:11
Log     SQL Server (Archive #3 - 15.6.2015 21:33:00)

Source      spid17s

Message
Error: 823, Severity: 24, State: 3.

ROW 2 in LOG:

Date        15.6.2015 20:54:11
Log     SQL Server (Archive #3 - 15.6.2015 21:33:00)

Source      spid17s

Message
The operating system returned error 5(Access is denied.) to SQL Server during a write at offset 0000000000000000 in file 'e:\Databases\MYDB_HISTRICAL.ndf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

MY PROCESS IS FOLLOWING:

1. I make several backups of the Database (Full, File Group and TLog backups).

2. I set DB to RESTRICTED_USER (to allow removing read-only of historical file group flag by script).

2a. I remove READ-ONLY flag of my Historical File Group.

3. I set DB to MULTI_USER to allow normal operation of our software.

4. I update partitions so the data is moved the the historical file group.

5. I repeat steps 2, 2a and 3 in so I can set historical file group READ ONLY again.

6. I make backups again.

Does anyone have idea why I receive that error?

EDIT: We receive the same issue during the different phase of the procedure. This is the only situation in which mirror breaks down so I suppose the problem is inside the procedure, but I can not figure out why!


texts: Solution verification for functional dependency using armstrong axioms

texts:
I have the following functional dependencies which includes all attributes of the relation:
{AB -> C, BC -> AD, D -> E, CF -> B}
CF supposedly is a candidate key. The task is: Show that CF is a candidate key given the functional dependencies listed
ACF -> AB (Augmentation)
AB  -> C  (Follows directly form functional dependencies)
BC  -> BAD (Augmentation)
BC  -> D (Decomposition)
D   -> E (Follows directly from functional dependencies)
This came up as part of an algorithm, which has no name in my script. In line 10 the candidate key {CF} was discovered and I wanted to verify that it is indeed a candidate key.

LEFT = {F}
NOWHERE = {}
MIXED = {ABCD}
RIGHT = {E}
2^MIXED = {{}, {A}, {B}, {C}, {D},
{AB}, {AC}, {AD}, {BC}, {BD}, {CD},
{ABC}, {ABD}, {ACD}, {BCD}, {ABCD}}
LEFT ∪ NOWHERE = {F}
{F}G+ (Closure) = {F}
2^MIXED = {{A}, {B}, {C}, {D},
{AB}, {AC}, {AD}, {BC}, {BD}, {CD},
{ABC}, {ABD}, {ACD}, {BCD}, {ABCD}}
{AF}G+ = {AF}
2^MIXED = {{B}, {C}, {D},
{AB}, {AC}, {AD}, {BC}, {BD}, {CD},
{ABC}, {ABD}, {ACD}, {BCD}, {ABCD}}
{BF}G+ = {BF}
2 MIXED = {{C}, {D},
{AB}, {AC}, {AD}, {BC}, {BD}, {CD},
{ABC}, {ABD}, {ACD}, {BCD}, {ABCD}}
{CF}G+ = {CFBADE} Candidate key!
2 MIXED = {{D},
{AB}, {AD}, {BD},
{ABD}}
{DF}G+ = {DFE}
2 MIXED = {{AB}, {AD}, {BD},
{ABD}}
{ABF}G+ = {ABFCDE} Candidate key!
2 MIXED = {{AD}, {BD}}
{ADF}G+ = {ADFE}
2 MIXED = {{BD}}
{BDF}G+ = {BDFE}
2 MIXED = {}

I also have no idea why the author choose the description 2^mixed for the combinations of all the attributes


texts: Unrecognised system process saturating CPU

texts:
We have switched to our backup DC today as primary site Bandwidth has gone.

We are getting issues with the most powerful DB server; it is spawning the following process for several db's on the server. One database is simply a small scratch db that is used for maintenance tools, so not replicated or mirrored.

Does anyone know or recognize the following code?

() select table_id, item_guid, oplsn_fseqno, oplsn_bOffset, oplsn_slotid
from [<dbname>].[sys].[filetable_updates_2105058535] with (readpast)
order by table_id


texts: How to design table to allow marking records as related to each other?

texts:
My web app's main MySQL table has tens of thousands of rows. Pretend it has 2 columns: ID, Content.
I'm trying to design a new feature that allows rows to be marked as related to each other.
Obviously I could add a 3rd column: Related_IDs. It could either be a JSON array or could be a simple CSV string.
But that feels wrong. I'd need to update the Related_IDs field of each row of a group of N rows to be a comma-separated list of IDs of all of the rows in the group except itself. Every time any one of those records becomes unrelated, I'd need to iterate through the group and clean up that field for each.
What is a smarter approach?


texts: Is my mssql 2008 standard suitable for web edition 2014

texts:
A web edition seems enough for me when I read this.

I thought it had a 2gb size limit before? I have three databases our current db and am not doing anything fancy. Just some server agent jobs from http://ola.hallengren.com
This is our current db:

  Microsoft SQL Server 2008 (SP3) - 10.0.5538.0 (X64)   Apr  3 2015 14:50:02   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (VM)

output of exec sp_spaceused
db1:

database_size 1355.81 MB
unallocated space 264.66 MB
reserved 758112 KB
data 647008 KB
index_size 105536 KB
unused 5568 KB

db2:

database_size 3141.25 MB
unallocated space 146.16 MB
reserved 1955672 KB
data 1725656 KB
index_size 216800 KB
unused 13216 KB

db3: (can be removed I hope) and seems similar to db2

it all runs on an xeon e5-2650 v3 @2.3ghz (4 cpu's) and 8gb ram. So I thought that a D2 on azure will perhaps be similar and a web edition of sql 2014.
(west Europe)
https://azure.microsoft.com/en-us/pricing/details/virtual-machines/#Windows
or is it recommended to move away from running a sql server on a vm and towards hosted sql. https://azure.microsoft.com/en-us/pricing/details/sql-database/


texts: No Primary Key in some sql server tables

texts:
I have downloaded WorldWideImporters Database as a standard db (Microsoft Sample Database for SQL Server). In this db, some of the tables don't have primary keys but clustered indexes can be seen. Can anybody tell me why some tables are working without PK? What is the reason?

Any comments would be appreciated!


texts: Execute a script with SQLPlus containing html entity mappings

texts:
When executing a script with SQL PLus, I was prompted to enter value for lt, gt, and apos.

Here are some code examples in this script.

v_qry := replace(v_qry, '&lt;', '<');
v_qry := replace(v_qry, '&gt;', '>');
v_qry := replace(v_qry, '&apos;', '''') ;

How to execute this script without being prompted to enter values?

Thank you
Frank


texts: Right Join vs Left Join - My joins are broken and I don't understand why

texts:
http://sqlfiddle.com/#!3/ef71e/1

I rather drastically cut down the fiddle, but I think the intent shines through. [Def] is a [DefaultClassification] table, and [Cls] is a [Classification] table that has some old records that need to be added in. The DefaultClassification table will be used in the future to spawn new groups of records into yet another table, and we're "unbreaking" an existing bit of logic/data with this process.

I did my best to distill the entire thing down to the barest essentials, but I've got another similar process that runs right after this one, so I'm looking to learn best practices and why this query is broken.

When I wrote this I intended it to run in a 2008+ environment, scripted or manually in SSMS. I don't know that it won't be scripted, but it might. Right now it's entirely run by hand. The rollback and select at the top above the print messages at the end are because it doesn't work right, so this lets me validate it before committing. I would prefer a valid script that I don't have to muck with, however

The specific issue I have is on line 62 of that revision one link http://sqlfiddle.com/#!3/ef71e/1 and it looks like this:

  RIGHT OUTER JOIN [Def] tcd

if I flip that RIGHT to a LEFT it does the insert but doesn't detect duplicates (to prevent insertion) and if I do the RIGHT it detects duplicates but doesn't handle insertions.

What did I break? And Why?

And I'm told the use of the @@ERROR was bad juju, but I don't know why, so bonus points (a 100 rep bounty) for that explanation too.


texts: MySQL Slave fails to flush privileges as Master gets flushed

texts:
0
down vote
favorite
I run into the issue with my slave mysql databases. When I flush the master server it fails to flush slave databases.

What should be considering when adding a new user to MySQL.


texts: Case style of tables and field (Naming Conventions)

texts:
What is best practice for naming dbs, tables (collections) and fields among DBMSs?

I mean casing style, for example AaaBaa,aaa-bbb, aaa_bbb, AAA-BBB, aaaBbb, ...

I think if one exists it should be applicable to all DBMSs  (MySQL, MongoDB, Oracle, ...), shouldn't it?

Currently I thing following is most appropriate:

Columns/Fields/Tables/Collections/Databases:  aaaBbbCcc.

Could distinct case style for these object be useful at all?


texts:
I just installed SQL server 2016 on a new 2012r2 vm. During the SQL installation I added the domain admin group and my domain account to the SA role. After the installation, I can connect using SSMS. The problem is no one in the domain admin group can connect. The group and my account are members of the local admin group on the server and we're only using RDP to connect to the db locally. The error I see in the log is "Login failed for user. Reason: token-based server access validation failed with an infrastructure error. Login lacks Connect SQL permission.[CLIENT:]"

When I check the effective permissions for the domain admin group I get the following error: Cannot execute as the server principal because the principal "domain\Domain Admins" does not exist, this type of principal cannot be impersonated, or you do not have permission.

I'm a fairly new to SQL server...any help would be appreciated. Thanks!


texts: Why doesn't MySQL installation on an Ubuntu linux 16.04 OS require user to specify LD_LIBRARY

texts:
Why doesn't MySQL installation on ubuntu 16.04's linux require the user to specify LD_LIBRARY_PATH to resolve its runtime dependencies?

I tried googling to find the answer to this question, but I found nothing.


texts: What is the optimal data type for an MD5 field?

texts:
We are designing a system that is known to be read-heavy (on the order of tens of thousands of reads per minute).

There is a table names that serves as a sort of central registry. Each row has a text field representation and a unique key that is an MD5 hash of that representation.1 This table currently has tens of millions of records and is expected to grow into the billions over the lifetime of the application.
There are dozens of other tables (of highly varying schemas and record counts) that make reference to the names table. Any given record in one of these tables is guaranteed to have a name_key, which is functionally a foreign key to the names table.

1: Incidentally, as you might expect, records in this table are immutable once written.

For any given table other than the names table, the most common query will follow this pattern:

SELECT list, of, fields
FROM table
WHERE name_key IN (md5a, md5b, md5c...);

I would like to optimize for read performance. I suspect that my first stop should be to minimize the size of the indices (though I wouldn't mind being proven wrong there).

The Question:
What is/are the optimal data types for the key and name_key columns?
Is there a reason to use hex(32) over bit(128)? BTREE or GIN?


texts: Can I grant privileges to a Postgres user on all schemas belonging to a database with one command or script?

texts: Let's say I have 20 different schemas on a Postgres database. I create a new user theUser and now I want to grant only the SELECT and INSERT privileges for this user on all schemas. Is there an easy way/script to do that?


texts: How to change default engine in WAMP to InnoDB

texts: Can someone tell me how to change my default storage engine to InnoDB using wamp? Currently it is MyISAM.


texts: Can set unique constraint in table have data not unique SQL Server?

texts:
I have a table with has many rows, I want to set unique constraint for this table, but the column that must be unique, has data is not unique. Is any way to set unique constraint for this table for this column or unique constraint for any insert or update next time?

I can't delete data because is real


texts: How to select distinct for one column and any in another column?

texts:
I need to query an SQL database to find all distinct values of one column and I need an arbitrary value from another column.  For example, consider the following table with two columns, key and value:

key     value
===     =====
one     test
one     another
one     value
two     goes
two     here
two     also
three   example

I wish to get back one sample row, chosen arbitrarily, from each distinct key, perhaps getting these three rows:

key     value
===     =====
one     test
two     goes
three   example

How can I formulate such a query in SQL?


texts: How to set an IP for automatic failover with repmgr can been accessed from application?

texts:
OS: CentOS
DB: PostgreSQL

By repmgr, set replication and automatic failover on 2 database servers successfully.

 ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------
 1  | node1 | standby |   running | node2    | default  | 100      | 2        | host=node1 user=repmgr dbname=repmgr connect_timeout=2
 2  | node2 | primary | * running |          | default  | 100      | 2        | host=node2 user=repmgr dbname=repmgr connect_timeout=2

The nodes IPs:

node1: 192.168.0.1
node2: 192.168.0.2

Set database connection IP with 192.168.0.1. But when node1 failed, it can't switch to 192.168.0.2 automaticlly. Is there a way to fix it?


texts: How to show the Join with Crows Foot Notation

texts:
I could be barking up the wrong tree, but I'm trying to illustrate not only what the relationship is between entities, example, Customers shop in many stores, but also how to join the two entities. For example, two tables:

Customer Data

CustomerID | Name
11112      | Monkeyhead
11113      | Monkeybutt

Customer to Store data

CustomerID | StoreID | Lifetime Purchase
11112      | ABC     | $23
11112      | DEF     | $45

I know that it's a many to one relationship between the two entities, Customer Entity and Customer to Store Entity; but how do I illustrate that it's joined via the CustomerID columns?

Is crows foot notation the right one to use? If not, which is?


texts:
I am PHP programmer with basic-intermediate knowledge on databases. I am working on a project where I need to verify a given phone number's presence in India's Do not call database. Following is the structure of the table,

Phone Number | Flag 1 | Flag 2 | Flag 3 | Flag 4 | Flag 5 | Flag 6 | Flag 7

here each Flag represents a category.

I would be provided a phone number and a category, or maybe phone number and multiple categories.

What would be the best way to optimize the table structure? Which will be best to use? Mysql or Oracle?

Should the table structure be normalized? How should the query be designed?

Response time is a key factor for the solution. The database has about 150 Million records.


texts: Update main and second table with a before update trigger for message logging

texts:
I currently have the following testing schema:
CREATE TABLE userMessage (
    message_id BIGSERIAL PRIMARY KEY,
    creator_id BIGINT NOT NULL REFERENCES "User" (id),
    created_at BIGINT NOT NULL,
    modified_at BIGINT NOT NULL,
    content TEXT NOT NULL
);

CREATE TABLE userMessageLog (
    message_id BIGINT PRIMARY KEY,
    modified_by BIGINT NOT NULL REFERENCES "User"(id),
    modified_at BIGINT NOT NULL,
    content TEXT NOT NULL,

    foreign key (message_id) references userMessage (message_id)
);

Where who created the message, when and it's actual content. Then another table where I keep track of who modified (can be another user) the message, when and the previous content.
I also created the following trigger for when the message is first created:
CREATE OR REPLACE FUNCTION new_message() RETURNS trigger AS $$
    BEGIN
        INSERT INTO userMessageLog(message_id, modified_by, modified_at, content) VALUES (NEW.message_id, NEW.creator_id, NEW.created_at, NEW.content);
        RETURN NEW;
    END
    $$
    LANGUAGE plpgsql;

CREATE TRIGGER new_message AFTER INSERT ON userMessage FOR EACH ROW EXECUTE PROCEDURE new_message();

Which works as expected. As soon as something is inserted on userMessage a new log entry will appear on userMessageLog.
My question is, when the user or another user edits that message, how can I create a trigger and modify the content, modified_at columns from userMessage table and add a new entry to the userMessageLog? Is there a more better approach to this schema/trigger design?
My only approach resulted in a failed attempt of creating a trigger on BEFORE UPDATE for userMessage which would make the entry on userMessageLogand then UPDATE the userMessage table, but that would result in recursive calls because of the trigger and I also have no way of letting it know who modified it for modified_by and when it was modified for modified_at column.


texts: Two Databases on same server handle date differently

texts:
I have two databases on the same server, one is a backup of the other from a few weeks ago.

I have a third party application that has started misbehaving.  It logs the LastLoginDate of a particular user when they log on using the following command.

Update DBO.theTable Set LastLoginDateTime = '2015/10/27 15:14:34'
Where UserName = 'theUser'

Now in the live database this works fine, yet when I run the same command on the Backup database I get the following error

  The conversion of a varchar data type to a datetime data type resulted
  in an out-of-range value.

This looks like a simple date format error, but I can't find out how to rectify it.  From what I understand the language of the database is set at the server level, so this should be the same.  Also, the same user at the server level is being used, so again, the Language of that user should not come into play.

As mentioned above, it's a third-party app so I can;t change the underlying SQL to be a different (more universal) date format, so I'll have to handle it at the database level.

Does anyone have any ideas on what I should be checking beyond the above to fix this?

UPDATE

I have worked out a fix to this problem but would still appreciate advice on why the databases are behaving inconsistently.  The two databases share a User 'AdminUser'.  There are also user accounts set at the server level such as 'AppUser'.  'AppUser' has experienced no problems while I have been investigating this issue.  They are set to use British English language

Yet when they log on to the database the database context switches to US English (as captured using SQL Server Profiler)

As a result of this I have switch the AdminUser to have their default Language set to 'English', and now it works as expected

So when I execute the query posted at the top I get the result

  (1 row(s) affected)

While the application is now working as expected, it surely can;t be right to have some users set to 'British English', and some set to (US) 'English' to achieve the same behaviour!  Any ideas are welcomed.


texts:
I need to delete the SQL Server 2005 logs, but can't figure out how to do it. I'm talking about the logs that are found in Management -> SQL Server Logs in SSMS object explorer. I can't seem to detete them from there, so I assume there is another way or a command to do it.

Also, I noticed that at least some log entries can be found in Windows Event Log (the ones about failed logins, for example). Will those get deleted too if I find a way to clear the SQL Server logs? Or should I clear the event log separately?

I looked for an answer but people seem to ask mostly about clearing transaction logs which is not what I need.


texts:
I have a few tables and I am facing this issue most likely with the table "item".
When I remove restaurant_id and category_id, it works fine.

CREATE TABLE IF NOT EXISTS restaurant
(
    id              UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    title           VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS restaurant_gallery
(
    id              SERIAL,
    restaurant_id   UUID NOT NULL REFERENCES restaurant,
    image           VARCHAR NOT NULL,
    PRIMARY KEY (id, restaurant_id)
);

CREATE TABLE IF NOT EXISTS category
(
    id              SERIAL NOT NULL PRIMARY KEY,
    restaurant      UUID NOT NULL,
    label           VARCHAR(25) NOT NULL,

    UNIQUE (restaurant, label)
);

CREATE TABLE IF NOT EXISTS item
(
    id              SERIAL NOT NULL PRIMARY KEY,
    restaurant_id   UUID NOT NULL REFERENCES restaurant (id),
    category_id     SERIAL NOT NULL REFERENCES category (id),
    name            VARCHAR(25) NOT NULL,
    UNIQUE (id, restaurant_id)
);

CREATE TABLE IF NOT EXISTS item_gallery
(
    id              SERIAL,
    item_id         INT NOT NULL REFERENCES item,
    image           VARCHAR NOT NULL,
    PRIMARY KEY (id, item_id)
);

  Error:

pq: 1 columns must reference exactly 1 columns in referenced table (found 2)
Error: pq: 1 columns must reference exactly 1 columns in referenced table (found 2)
Failed running "sql"

Any help is highly appreciated!


texts:
(also posted on MSDN forums http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/79058c06-d3c5-4073-8571-c29d1f43f7ae)

Hi guys,

We have a set of 5 online auction systems running on Windows Azure & SQL Azure. Each system consists of a single web worker and one or more web roles. Each system is using ASP.NET MVC 3 and Entity Framework, Repository Pattern and StructureMap.

The worker role is responsible for housekeeping and runs two groups of processes. One group is run every ten seconds, the other every second. Each process will likely run a database query or stored procedure. These are scheduled with Quartz.net

The web role serves the public interface and back office. Among other basic crud functionality, both of these provide screens which, when open, will repeatedly call controller methods which will result in execution of stored procedure read-only queries. The frequency of repetition is about 2-3 seconds per client. A typical use case would be 5 back office windows open, and 25 end user windows open – all hitting the system repeatedly.

For a long time we have been experiencing intermittent SQL timeout errors. Three of the most common ones are:

System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

The only predictable scenario is during an auction where a specific controller -> sproc starts to timeout during the event (presumably due to load). All other times the errors appear to be completely random and come in singles, two’s, and three’s etc. even during periods of user inactivity. For example the system will go 18 hours without an error and then could be 5 – 10 errors from different housekeeping methods, or perhaps a user logged on and viewed their account.

Other info:

I have tried to run the affected queries/sprocs on SQL Azure using both local SSMS and Azure web-based query tool – all seem to execute quickly, 1 second max. Query plans not showing anything too suspicious although I am by no means a SQL query performance expert, or any other kind of expert for that matter J

We have wrapped all affected areas in Azure SQL Transient Fault Handling Blocks – but as is discussed here http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/7a50985d-92c2-472f-9464-a6591efec4b3, they do not catch timeouts, and according to “Valery M” this is for good reason.

We are not storing any session information in the database, although asp.net membership information is stored in the database.

We use 1 “SQL Azure server instance” which hosts all 5 databases, two for staging and three for production. All 5 systems are generally active at the same time although it is unlikely that more than one will be in a state of live load use at any given time.
All web roles, worker roles and the SQL Azure server reside in the same Azure Geographical Region.

Any thoughts on where we should be looking? Would it help giving each system it's own SQL Azure server? ... Failing a solution by ourselves - is it possible to get Microsoft to open a support ticket and take a look under the hood at what’s going on in with our application – how does one go about this?

Thanks in advance.

Ilan


texts: Database design: Dividing multiple identical tables, good or bad?

texts:
I am very new at SQL and databases in general. I only use them for the occasional homework so I haven't even tried to master them.

I have seats at a theater, the seats are divided into 4 main areas (A, B, C, D). Each area has the same number of rows and the same number of seats per row.

In my database, I'd like to have Row + SeatNumber as a compound primary key, and to have one table for each area.

Now, I don't yet know how I'll do my selects, but what I want to ask: If I do it this way, will my selects be doable ? I want to, for example, select an exact position within the theater (where I know the area, row and seat number).

Would the 4 tables be a hindrance ? Could you give an example of how such a "select" might look ?

P.S. This is my first time at the site, if the question does not belong here, please direct me to a more suitable site within stack exchange.


texts: Control SQL Server pace of db shrink to split into multiple files

texts:
(Manually moving this from StackOverflow, as it really belongs here.)

We have a production SQL Server Enterprise database that has a few TB of data in it. We have a need to split the data into multiple files. Tests in pre-Production environments have gone well with the exception that during the shrink the performance of the database is degraded. We would like to get ahead of potential issues with shrink/split of files in the production system, since we have large customers with SLA's that limit our ability to make changes that significantly affect performance.

Does the SQL Server shrink/split functionality have the ability to limit how much overhead the shrink/split incurs during the transition to multiple files? Or, do we have paths that enable the same limiting ability. Alternately, can an approach be taken that gradually splits/shrinks the files during given maintenance windows?


texts: How to create a summary report that contains other reports?

texts:
I have 4 reports that I run for 8 different environments. Each report creates a graph from some usage tables. It's very simple SQL that creates a very simple graph.

The guy who used to do these manually took the results of each one of these reports and pasted them into a powerpoint doc.

I have been able to get each one of these reports into report server so you just need need to click on the report and see the graph. I can set up subscriptions to send out these results with no problem either.

The problem is that I am sure the managers don't want 32 graphs coming their way. They want only one doc to see all the graphs.

Is there a way to put all these graphs on one report?


texts: Weekly and Biweekly Std Deviation for total rowcount by grouping parameteres

texts:
I am trying to calculate standard deviation according to 3 grouping columns System, Type, T_Type.

The query should calculate weekly and biweekly deviation for row count according to above grouped parameters.

In the grouping parameters each system has all T_Type but it can have different Type values.
Find below the sample data. Tables contains dated rowcount for grouped values each day.


texts: Copy Database from Production to Development

texts:
I need to copy database from production to development instance in SQL Server 2012 as I don't want to mess up the production server database.

Can someone suggest how I can do this ?


texts: Should database files be on different disk(s) than OS?

texts:
My very short overview

SQL Server databases are installed as virtual machines on VMWare, there are plugged a few disk areas with the same (or very similair) parameters
versions are SQL Server 2005/2008 R2 Standard installed on Win2003/2008 Datacenter
Databases files are saved on another disk as is WinOS installed

Is this logic about good practicies, or are there other important, significant arguments for or against, why we should do this (or not do this)?


texts:
The following is a simple version of a delete I need to perform.

delete from table_name
where order_date < (getdate()-60)
go

This is deleting a large number of blobs and to clean up the table I need to reduce the number of days in small chunks to avoid TEMPDB filling up.

Is there an easy way of getting this to run in passes each time reducing the number of days by 1 starting at say 60 days and ending at 30 days?

Any help or pointers in the right direction would be great.


texts:
I have to make 25 modification historic tables from 26 tables. I need a design for each table that could grow more than 75,000,000 of rows.

I use InnoDB for 25 tables and 1 with MyISAM. I have one design in mind but I want to see other choices vs what I got.

My company uses Birt as BIT(business intelligence tools) and uses historic logs a lot.

Legend:

OTPK: Original Table Primary Key.
HTNAME: Historic Table Name.
IDX: It has an index.

My design tables:

ChangeInfo:

ChangeID INT(3) (PK)
ChangeName VARCHAR(75)

HTNAME_LOG:

OTPK_Id` INT (PK)
ChangeTime DATETIME (PK)
ChaValue VARCHAR(255) (Varchar value of the OTPK)
IntValue BIGINT(15) (Int value of the OTPK)
ChangeID INT(3) (PK) (Change Info PK)
DataType BIT(1) IDX (If true, integer(IntValue), varchar(ChaValue). This is for reading queries)

PD: All the datatypes depend of the Original Table.

Any suggestion will be appreciated.


texts:
I have a classical statement-based replication between the master and the slave. Triggers were created on the master and replicated on the slave as well.

Assuming that I have, say an ON UPDATE trigger, is the following correct?

  MySQL will only write to the binary log statements that were not the
  result of the trigger. Data consistency between the master and the
  slave is therefore ensured by the fact that the same triggers are
  present on both.

The relevance of this pertains to incremental updates (or accumulutors), eg.

CREATE TRIGGER vesion_increment AFTER UPDATE ON table
FOR EACH ROW
BEGIN
  UPDATE table SET version=version+1;
END

In this case, the UPDATE table SET version=version+1 will not be in the binary log and hence not replicated but taken care of by the same trigger on the slave. Is that correct?


texts: When sys.dm_db_index_usage_stats is cleared in Azure SQL Database

texts:
In my solution I need to check when there where some last change done in table/index to determine data freshness.

I'm using Azure SQL Database and the best option (and maybe the only one without additional tracking/triggers etc) was to check last_user_update in :

sys.dm_db_index_usage_stats

I know that in SQL Server this data is cached and not stored in physical table, so information from there are lost when SQL Server is restarted.

But I'm not sure how does it behave for Azure SQL DB, and could not find that info in documentation.

I have noticed that in one of my Azure SQL DB's after few days of data not being updated - that is indexes did not changed (not sure exactly for how many days - trying to look it up at the moment) - last_update_date was empty. While I still have rows for some objects, as they where queried and last_user_scan had value, tables that were not used at all are not present at all.

I have some theories to check if for example Scaling DB or health events are clearing up that DMV, but maybe someone have some idea or reference to documentation on that behavior in Azure SQL DB?

For solution to that problem, I'll "materialize" this DMV with procedure and schedule it in ADF, but I would like to understand logic of this "clearing cache" for that DMV in Azure SQL, to know when to expect loosing information from it.


texts: Issues installing patches on SQLserver 2008

texts:
There are multiple issues with a server I manage, we cannot start management studio on it and when we try to install patches to SQL Server the setup never shows up.

I run the patch as an administrator, it shows me that some files are being extracted to a temp folder, then a CMD window opens for a split second and closes immediately after.

I have looked around a bit to find a solution but can't quite decide where to start from.

Here is a link of someone saying he ran caspol.exe to fix a somewhat similar issue

What's caspol anyways? Having never used it I don't really want to create more issues by not knowing what it should be used for.0

Here is another link saying I need to add registry keys for my installation to begin:

Here is my server's select @@version query result :

  Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)   Mar 29
  2009 10:27:29   Copyright (c) 1988-2008 Microsoft Corporation
  Standard Edition on Windows NT 6.0  (Build 6002: Service Pack 2)
  (VM)

The server is a windows server 2008 32-bit

The patch setup creates the following log when it fails :

03/17/2016 13:58:13.308 ======================================================================
03/17/2016 13:58:13.309 Setup launched
03/17/2016 13:58:13.310 Attempting to determine media source
03/17/2016 13:58:13.311 Media source value not specified on command line argument.
03/17/2016 13:58:13.390 Setup is launched from media directly so default the value to the current folder.
03/17/2016 13:58:13.391 Media source: g:\e89cdf338f18c1307f\
03/17/2016 13:58:13.391 Attempt to determine media layout based on file 'g:\e89cdf338f18c1307f\mediainfo.xml'.
03/17/2016 13:58:13.433 The folder 'g:\e89cdf338f18c1307f\' does not contain a valid media info file 'mediainfo.xml'.
03/17/2016 13:58:13.434 Setup closed with exit code: 0x84C4001E
03/17/2016 13:58:13.484 ======================================================================

It might be good to add that the windows search is not working, as you may observe in the following image :

Maybe the setup is trying to look for files but fails as it cannot search for them ?

If this was your server, what would be the first step of troubleshooting you'd go through ?


texts:
What I want to do is to set DATE_REACT a value when the first condition is satisfied, and a different value when the first condition is not satisfied. The problem is that the first condition is executed always, even when it shouldn't. What I am doing wrong?

UPDATE BILLS
SET STATE = 2,
DATE_REACT =
CASE
WHEN (DATE_EM <= CURTIME() <= ADDTIME(DATE_EM, '00:02:00'))
THEN ADDTIME(CURTIME(), '00:13:00')
ELSE ADDTIME(DATE_REACT, '00:13:00')
END
WHERE STATE = 1;


texts: What is the effect of replacing indexes with filtered (non-null value) indexes?

texts:
Our project runs a very large, very complicated database. So about a month ago, we noticed that the space used by indexed columns containing null values was getting too large. As a response to that, I wrote as script that would dynamically search through all single-column indexes containing more than 1% of null values, then drop and recreate those indexes as filtered indexes on the condition that the value was NOT NULL. This would drop and recreate hundreds of indexes throughout the database and typically free up almost 15% of the space used by the whole DB.

Now I have two questions about this:

A) What are the downsides of using filtered indexes in this fashion? I would assume that it would only improve performance, but are there any performance risks involved?

B) We received errors ('can't drop index XYZ beause it does not exist or you do not have permission') on dropping and recreating the indexes, even though when checked afterwards, everything had gone exactly as expected. How can this happen?

Thanks for any help!

Edit: In response to @Thomas Kejser

Hi and thanks, but it turns out this was a disaster. At the time we didn't understand several things such as:

During a query, SQLOS makes index plans prior to determining that it cannot use NULL values for joining table columns. IE, you truly do need to have a WHERE clause filter fitting the index for each and every filtered index used in the query, or the index will not be used at all.
Dropping and creating indexes and redundantly updating their statistics yet again afterwards still may not be enough to produce the updated plans, which we assumed they would. It appears in some cases only a high enough workload will force SQL Server to reassess the plans.
There are some exotics to the functionality of the execution planner that are difficult to determine by common sense and logic alone. With thousands of code-behind -generated variations of different queries even, seemingly useless indexes can help in some statistics and query plans that end up being used in critical queries.

In the end, these changes were reverted. So filtered indexes are a powerful tool, but you need to truly understand exactly what data is being fetched from those columns. Where normal indexes aside from the space issues are rather easy to apply, filtered indexes represent very customized solutions. They are certainly not a replacement for a regular index, rather an extension to them in those special circumstances they're required.


texts: Executing a stored procedure on remote database

texts:
I have 2 databases on the same server. DB-A and DB-B. DB-B has a user that has read and write access to database B. I would like to grant the user in DB-B execute permissions to 2 stored procedures in database A. I was able to do this when the databases were on seperate servers by setting up a linked server, and mapping a linked login. I am having trouble with this when the databases are on the same server. Can someone please tell me how I can accomplish this task.

Thanks,
James


texts: Transpose rows data into columns in MYSQL

texts: Please help me with the scrip that can change table1 into table2 given in the post. I wish to change the rows data into columns


texts: What is the purpose of table partitioning

texts:
I was advised by a senior DBA that you should never use Partitioning, instead use schema to group data. I am talking on large databases. I am not convinced with this, please advice

An offline application deployed in 350+ locations was converted into an Online Application Single Database (10 Million rows).

They created schema's for 350+ locations and created tables inside these respective schemas.

When I was asked to look into the issues, I wondered why this setup and was because they were advised by Senior DBA to go like this (My Concerns were, How to perform search operations across all schema, how to manage any changes in table 350+ is huge, if it is a single table with partitioning things will become easy)

When I consulted the senior DBA regarding this and why he didnt propose Table Partitioning Instead of different schema, "He said partitioning is logical" and will not work, if you split it into schema the performance will be better because database operations will be limited to that schema.

These statements confused me and thus seeking your views and experiences


texts:
I have a table with following structure

I would like to identify for each child the root structure, like so:

How can I write a query to get this result?
I tried with the following but not getting desired result.
SELECT
 CHILD,
 CONNECT_BY_ROOT PARENT,
 LEVEL-1 "PATH",
 SYS_CONNECT_BY_PATH(PARENT, '/') "PATH"
FROM FGB CONNECT BY PRIOR PARENT=CHILD


texts: Which component of SQL Server architecture handles the user login requests?

texts:
I was asked this question recently and I couldn't find the answer or any detailed explanation on the internet regarding this. Can anyone please explain or provide a reference link that explains about the component of SQL Server architecture that handles the user login requests?

Thanks in advance!!


texts:
as the header line suggests: Using an ALTER VIEW statement on a VIEW that has an index on it will drop this (all?) indexes from the VIEW without warning. I would prefer the ALTER VIEW statement to fail, informing me to drop the index(es) first.

Is there a setting in SQL SERVER to change this behaviour? Or is it changed in a version after SQL 2012 (SP3)?


texts: SQL Server 2008 - Cannot create index on Indexed View

texts:
I am attempting to create an index on a view. The view is contained in a different schema than the underlying table it's based on. When I attempt to create the view, I get the following error:

Msg 1938, Level 16, State 1, Line 1
Index cannot be created on view 'vwCdSimThk' because the underlying object 'CDSIM_Thk' has a different owner.

What I find confusing about this is that I have created this exact same view and index on at least 4 different SQL Server servers (each containing the same database schema) without getting an error like this. But on this one particular SQL Sever, I'm getting the error. Just to be clear, on all the other servers where this works, the view and underlying table it's based on are in different schema's.

Can anyone shed some light on this? Is there a server setting that needs to be changed to allow this?


texts:
I have some instances were some of the BI teams can create their own schduled jobs in the sql agent.

However i also have a couple house keeping jobs i don't want them to interfere with. Can I create a role and specify only people with this role can update the house keeping jobs?

If so how?


texts: can't update user password on Mongodb 4.0.5 (Use of SCRAM-SHA-256 requires undigested passwords)

texts:
I'm trying to update a password from a user, using changeUserPassword(), and I just receive the following message:

  Error: Updating user failed: Use of SCRAM-SHA-256 requires undigested passwords

> db
mydb
> db.getUsers()
[
    {
        "_id"  : "mydb.mongo",
        "user" : "mongo",
        "db"   : "mydb",
        "roles" : [
            {
            "role" : "dbOwner",
            "db" : "mydb"
            }
        ],
        "mechanisms" : [
            "SCRAM-SHA-1",
            "SCRAM-SHA-256"
        ]
    }
]

 > db.changeUserPassword("mongo", "mongo_n3w_p4ss");
2019-05-22T20:58:10.128-0500 E QUERY    [thread1] Error: Updating user failed: Use of SCRAM-SHA-256 requires undigested passwords :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
DB.prototype.updateUser@src/mongo/shell/db.js:1487:15
DB.prototype.changeUserPassword@src/mongo/shell/db.js:1491:9
@(shell):1:1
> db.version()
4.0.5

I also tried updateUser(), but no use.

What am I missing and what is the meaning of "requiring undigested password"?


texts: How to fix postgres stuck in recovery mode?

texts:
I have Jira and Confluence installed in a server. We didn't have any free disk space one day, and these services didn't started correctly, so after deleting some files we now have some free space available. The problem now, is that postgres seems not to start correctly. It's in this recovery mode, but it can't get out of it.

valter@web:~$ ps -ef | grep post
postgres 14580     1  0 Jan03 ?        00:01:16 /usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main -c config_file=/etc/postgresql/9.3/main/postgresql.conf
postgres 14581 14580  0 Jan03 ?        00:00:00 postgres: startup process   recovering 000000010000000A0000000D

Any idea what is going on here ?

valter@web:~$ sudo ls -ltha /var/lib/postgresql/9.3/main/pg_clog/
total 3,9M

drwx------ 15 postgres postgres 4,0K Jan  3 12:41 ..
drwx------  2 postgres postgres 4,0K Jan  3 12:34 .
-rw-------  1 postgres postgres  48K Jan  3 12:41 000E
-rw-------  1 postgres postgres 256K Jan  3 12:30 000D
-rw-------  1 postgres postgres 256K Nov 15 09:39 000C
-rw-------  1 postgres postgres 256K Okt 13 23:07 000B
-rw-------  1 postgres postgres 256K Sep 11 23:11 000A
-rw-------  1 postgres postgres 256K Aug  8 10:45 0009
-rw-------  1 postgres postgres 256K Jul  3  2015 0008
-rw-------  1 postgres postgres 256K Mai 26  2015 0007
-rw-------  1 postgres postgres 256K Apr 17  2015 0006
-rw-------  1 postgres postgres 256K Mär  9  2015 0005
-rw-------  1 postgres postgres 256K Jan 30  2015 0004
-rw-------  1 postgres postgres 256K Dez 19  2014 0003
-rw-------  1 postgres postgres 256K Nov  9  2014 0002
-rw-------  1 postgres postgres 256K Sep 29  2014 0001
-rw-------  1 postgres postgres 256K Aug 22  2014 0000

EDIT 1:

valter@web:~/9.3/main/pg_xlog$ ls -ltha
total 97M
drwx------ 15 postgres postgres 4.0K Jan  3 12:41 ..
-rw-------  1 postgres postgres  16M Jan  1 12:16 000000010000000A0000000D
-rw-------  1 postgres postgres  16M Dec 23 00:00 000000010000000A0000000C
drwx------  3 postgres postgres 4.0K Dec 21 23:45 .
-rw-------  1 postgres postgres  16M Dec 21 23:36 000000010000000A00000011
-rw-------  1 postgres postgres  16M Dec 21 19:00 000000010000000A00000010
-rw-------  1 postgres postgres  16M Dec 21 14:25 000000010000000A0000000F
-rw-------  1 postgres postgres  16M Dec 21 09:55 000000010000000A0000000E
drwx------  2 postgres postgres 4.0K Jul 10  2014 archive_status

Server log file:

    valter@web:~/9.3/main/pg_xlog$ tail -f /var/log/postgresql/postgresql-9.3-main.log
    ...
    2016-01-05 17:23:08 CET FATAL:  the database system is starting up
    2016-01-05 17:23:09 CET FATAL:  the database system is starting up
    2016-01-05 17:23:10 CET FATAL:  the database system is starting up
    2016-01-05 17:23:11 CET FATAL:  the database system is starting up
    ...


texts: 32GB RAM needed to work with 200.000.000 rows in MongoDB?

texts:
I've been playing with a large dataset, consisting of a few tens of billions of rows of small ascii strings. I want to count the number of occurrences of each string in the dataset, and i suspect the unique number of strings is about 1 billion. I've been upserting them into a collection in a MongoDB 4.0 database on a three 32GB RAM machines in a replica set, with the WiredTiger storage engine. Things go pretty ok, until i hit the magical number of 200.000.000 rows. After that number, the insert speed starts to grind to a halt. I bulk upsert a chunk of 5000 strings at a time, and some operations take 1 second, but once in a while, an operation takes as much as 40 seconds or more. Replication lag also starts to shoot up.

Examining db.stats(), i see that the only index, _id, takes about 8GB of memory; storage size is about 6GB; wired tiger cache size default is half of ram, so about 15GB. All of these add up to almost 29GB. I'm guessing that the oplog also takes a few GB as well.

Am i running out of RAM? I was expecting to be able to load the whole dataset in a shot. I will probably convert the 3 member replica set to 3 shards, as the data is mostly perisable, and i will reduce the wired tiger cache size parameter, but i am curious if i am right about the RAM and the number of documents.


texts:
I'm using a real Mongo instance for my integration tests. On every test, I call mongorestore my_db_state --drop because I need a clean db to start the next test.
On my tiny db (with only one test user account) it takes 180 ms to run mongorestore my_db_state (without --drop) and 455 ms with --drop.
I don't understand why it's taking so long to drop the db, but how can I speed this up?
I may have a bunch of different connections on the mongo server because I never close them on my Flask server, but they probably should be closed after the test is over...just not sure if this is an issue to care about.
Is there a faster way to restore a snapshot to its original state for testing purposes?


texts: My version of SQL Developer (4.0.2.15) has white text on a light blue background (see below). I have been unable to find in the preferences to change this so that it is more readable. Does anybody known how to make the text a difference colour.


texts: Database Restore Failed Due to Incompatible Version of SQL Server

texts:
I am working on different two SQL Server, I need to transfer database from one server to another but problem is the servers have different versions.

Below is image of error message.

Using SELECT @@Version I have checked database version like below,

First Database Version (Source Database):

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4260.0 (X64)
Jul 11 2012 15:47:13   Copyright (c) Microsoft Corporation  Web Edition (64-bit)
on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Second Database Version (Destination Database):

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)
Jul  9 2008 14:17:44   Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit)
on Windows NT 6.0 <X64> (Build 6001: Service Pack 1) (VM)

How can I do this?


texts: SQL Server Agent Jobs and Availability Groups

texts:
I'm looking for best practice in dealing with scheduled SQL Server Agent jobs in SQL Server 2012 availability groups. Maybe I missed something, however at the current state I feel that SQL Server Agent is not really integrated with this great SQL2012 feature.

How can I make a scheduled SQL agent job aware of a node switch?  For example I have a job running on the primary node which loads data each hour. Now if the primary goes down, how can I activate the job on the secondary which now becomes primary?

If I schedule the job always on the secondary it fails because then the secondary is read-only.


texts: How to setup encryption on a column that joins two tables in two different databases

texts:
What is the best method to encrypt a column that exists in two tables (BUID) in two different database on a 2012 server, at the end should be able to join on the encrypted columns between two tables across databases.

use testdb1

create table table1
(
    BUID number(10),
    ACCOUNT_BALANCE Money
)

use testdb2

Create table table2
(
    BUID number(10),
    CUSTOMER_FNAME varchar2(30),
    CUSTOMER_LNAME varchar2(30)
)

Basically want to encrypt the BUID in both tables and be able to crate a view  that joins across two databases.

create view vw_cust_balance
as
    select
        a.buid ,
        a.account_balance
        b.customer_fname,
        b.customer_lname
    from
        testdb1..table1 a
    inner join
        testdb2..table2 b on a.buid = b.buid

Can someone point me to a resource or example that resolves situation above?