texts: Merge multiple column values into one column in different table?

texts:
I've done this before, but I don't remember how. I need to select all rows with unique values from several columns in one table, and insert them into one column in a different table. For the destination table they need to be on separate rows in the destination column.

Here's an example:

                        Source Table
+-----+------------+------------+------------+------------+
|  id |  column1   |  column2   |  column3   |  column4   |
+-----+------------+------------+------------+------------+
|  1  |   value1   |   value1   |   value1   |   value1   |
+-----+------------+------------+------------+------------+
|  2  |   value2   |   value3   |   value4   |   value5   |
+-----+------------+------------+------------+------------+

 Destination Table
+-----+-----------+
|  id |  column1  |
+-----+-----------+
|  1  |   value1  |
+-----+-----------+
|  2  |   value2  |
+-----+-----------+
|  3  |   value3  |
+-----+-----------+
|  4  |   value4  |
+-----+-----------+
|  5  |   value5  |
+-----+-----------+

I tried this query but of course it didn't work because the columns aren't equal.

INSERT INTO table2 (column1)
SELECT DISTINCT column1, column2, column3, column4
FROM table1


texts: How to setup large database access/cache on LAMP environment?

texts:
My application is getting very slow I guess because of the ever growing database (30+ tables some with 50,000+ rows).

Using CodeIgniter's model tier to call MySQL database, I optimized queries & views and what not but to no avail. My limited DBA skills took me so far, but I need to step up my game and learn how to do it on a larger scale.

I investigated a little deeper and found a possible direction of caching (?) in memcache, sphinx, HandlerSocket, redis. I also thought it might be logical to use a middle tier (compiled C++ ? Java?) between the DB and PHP. Also I am looking into moving my setup to the cloud (beyond regular VPS) so I will have better control over memory and setup.

Can someone please tell me if I am barking up the wrong tree here? I will read and investigate according to your answers and hand it over to a DBA pro, but I would like to understand the methodology and services I should be looking at.

Thanks.


texts: Do I have to pay a license fee for Berkeley DB?

texts: I'm planning to use Berkeley DB in a mobile application. Will I need a license even if the application is distributed freely in the iPhone and Android markets?


texts:
We experienced a problem recently where the disc space ran low and some queries started giving unpredictable results. The root cause was that we'd take too many restore points and filled up the disc.

I'd like to be able to monitor this. My context is a big corporate, highly separated functional roles, so I only have access to the SQL query on the box.

My question is: Is there a way to get disc space remaining using an SQL query on an Oracle database?


texts: Default SQL Server 2008 locking behaviour

texts:
I am using SQL Server 2008.

What is the default lock behaviour with UPDATE?

NOLOCK?
ROWLOCK?
PAGLOCK?

How can I tell what the current level of lock is for a table?

Thanks,
Bruce


texts: how to add loop in date column in sql through query?


texts:
I have several databases I set up using mamp. Recently, my mac died of old age and I got a new one. Fortunately I have a made of bootable backup of my hard drive every day.  Unfortunately, I cannot boot off of it with my new mac - the image is os x tiger and the new mac won't run tiger, only os x lion.

The upshot is, I have several mysql databases on that backup disk that I need to move to my new machine.  Is there a way of doing this using my current setup running mamp under lion, perhaps open the databases on the backup disk, export the databases and then restarting mamp from the lion databases, and importing what was exported, or something?

Thanks for any ideas!


texts: How to treat numbers within strings as numbers when sorting ("A3" sorts before "A10", not after)

texts:
For all of these queries:
SELECT label FROM personal.storage_disks ORDER BY label ASC;
SELECT label FROM personal.storage_disks ORDER BY label COLLATE "C" ASC;
SELECT label FROM personal.storage_disks ORDER BY label COLLATE "POSIX" ASC;
SELECT label FROM personal.storage_disks ORDER BY label COLLATE "default" ASC;

The output is always: DISK 1, DISK 10, DISK 2, DISK 3, [...]
But, I want and expect: DISK 1, DISK 2, DISK 3, [...] DISK 10
I'm out of collations to try now according to SELECT * FROM pg_collation;... unless I'm supposed to use one of the many really weird ones with cryptic names. (I even tried a bunch of those with the same result.)
Please note that I've read the existing seemingly related SE questions as well as many articles on SORT BY, but they didn't help and didn't clear up anything for me.
I'm using PostgreSQL 12.4


texts: MariaDB Galera Arbitrator failing to sync with SSL

texts:
So the scenario is this. I have already setup a 4 node MariaDB Galera cluster (10.5). I'm also adding on top a 5th Galera Arbitrator and everything syncs and connects just fine. However when I enable SSL on the cluster ( after bootstrapping it from scratch ) I'm able to sync the 4 nodes but the Arbitrator for some reason does not sync.
Interesting is that when the nodes are syncing they're outputting "connecting...ssl://172.31.0.4:4567".
However when the arbitrator is trying to sync it's outputting "connecting...tcp://172.31.0.4:4567".
Seems like it's trying over tcp for  some reason.
It's config is this when I start it up
    name:    garb
    address: gcomm://mariadb-galera-0:4567,mariadb-galera-1:4567,mariadb-galera-2:4567,mariadb-galera-3:4567,mariadb-galera-arb:4567
    group:   scluster
    sst:     trivial
    donor:
    options: socket.ssl_key=/etc/ssl/galera/server-key.pem;socket.ssl_cert=/etc/ssl/galera/server-cert.pem;socket.ssl_ca=/etc/ssl/galera/ca-cert.pem;socket.ssl_cipher=AES128-SHA; gcs.fc_limit=9999999; gcs.fc_factor=1.0; gcs.fc_master_slave=yes
    cfg:
    log:

It doesn't give me an ssl or cert error just timing out
INFO: (8bef8261-9d27, 'tcp://0.0.0.0:4567') connection to peer 00000000-0000 with addr tcp://172.31.0.4:4567 timed out, no messages seen in PT3S, socket stats: rtt: 39 rttvar: 19 rto: 200000 lost: 0 last_data_recv: 3500 cwnd: 10 last_queued_since: 3499991400 last_delivered_since: 3499991400 send_queue_length: 0 send_queue_bytes: 0
The pem files are exactly the same that I'm using across all nodes + the arbitrator.
I read in the documentation that I need to specify the cipher otherwise I'll be getting an error. I tried AES128-SHA and AES128-SHA256 and not declaring it at all but still timing out.
Below is the wsrep provider options from the phpmyadmin from one of the nodes. The cipher seems empty but then again why is the arbitrator trying to connect to "tcp://" and not "ssl://" like the others ?

Thank you for helping


texts: SQL injection in Postgres functions vs prepared queries

texts:
In Postgres, are prepared queries and user defined functions equivalent as a mechanism for guarding against SQL injection?
Are there particular advantages in one approach over the other?


texts: PostgreSQL autoincrement up to certain number then reset for sharding

texts:
I've was recently reading an article around how Instgram used to shard their IDs and they use a unique function to generate ids on their servers https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c

But it got me curious around whether if you could just assign a shard to a particular user rather than every item and how performant it would be. Especially in a large scale website.

So would a function similar to the following be a valid approach.

Assuming a users table on a dedicated database purely for users.

User
   - UID
   - name
   - shardId

Where shardId is calculated via a function that starts at 1, and for every new insert it would increment - upto a max of n (say 2048) for this example. Then reset to 1. Almost acting as a round robin to distribute users data among a shards.

Would there be serious performance implications to such a function, especially during high load? As serial columns simply select a nextval from a sequence do they not? It's just an extra step of reseting a sequence once it reaches n. Or am I barking up the wrong tree?


texts: How do I stop MYSQL trying to load a missing InnoDB table?

texts:
Everytime I start MySQL, I receive the error:

Cannot open datafile for read-only: '.\deleteddatabase\deletedtable.ibd' OS error: 103

I can't DROP the table, because it doesn't exist. There's no corresponding .frm file. I presume that somewhere in ibdata1 there's a reference to this old InnoDB file.

How do I stop the error?


texts: Cross-Continent SQL High-Latency Data Synchronization

texts:
How would one go about synchronizing a MySQL database cross-continent, while keeping the distributed application using the DB running fast (<0.2s page loads) ?

My database contains about 2.5GB of data, having almost all 200 tables indexed by auto_increment int key, so some kind of MASTER-MASTER replication seems not quite possible, as duplicate keys might occur.

The hardware my project runs at are located in 2 datacenters in US-West and EU-West (130ms ping latency between each other)

I've thought about using a Master SQL server in EU-West, and having a slave server in both regions, and having the application servers connect directly to the master server for insert/update queries only, and connecting to the slave servers for select queries.

The thing is that when having to execute a few insert/update queries, this may cause really slow page loads for users connected to US-West. Even when running the queries async can cause a slow user experience.

Is there a different way how I can guarantee that users in neither region experience slow page loads due to having to write to a high-distance SQL server, while keeping data synchronized between the 2 regions fast, without having to worry about duplicate keys and synchronization errors ?


texts:
I wondered if anyone else may have tried restoring a copy of an encrypted database to an instance of SQL vNext. The instance I'm running has been installed on a CentOS box, but I'm not having any luck restoring this particular database, because I can't get my certs set up. I'm trying to follow the same practice that has allowed me to accomplish this on other instances/servers, to no avail. I've gone so far as to give full access (chmod 777) to most of the folders and files involved, because the error I'm receiving states that it may be a permissions issue. Everything that should be taking place would be in /var/opt/mssql/data, /var/opt/mssql/secrets, /var/opt/mssql/.

If any of you knowledgeable folks could point me in the right direction, I'd greatly appreciate it. I will post more details/specific error message in the morning.


texts:
I am looking at running the following query within a scheduled job weekly:

Select TOP 10
      s.database_name,
      m.physical_device_name,
      CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date)
        AS VARCHAR(100)) + ' ' + 'Seconds' TimeTaken,s.backup_start_date,
      CASE s.[type]
       WHEN 'D' THEN 'Full'
       WHEN 'I' THEN 'Differential'
       WHEN 'L' THEN 'Transaction Log'
      END AS BackupType,
      s.server_name,
      s.recovery_model
 FROM msdb.dbo.backupset s
 INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
 ORDER BY backup_start_date DESC, backup_finish_date
 GO

Not sure what I am doing wrong but I continue to get an error, I have tried running it from within the @query tag for the sendmail option.


texts: why/how does the number of matched columns influences the way of excecuting a query

texts:
Imagine the following situation:

Table A uses MyISAM and contains 4 fields (text) with a combined FULLTEXT-index.

FULLTEXT fulltext1 | fulltext2 | fulltext3 | fulltext4

Table B uses InnoDB and contains some other fields, where 5 of them are indexed as 'ORD'.

ORD order1 | order2 | order3 | order4 | order5

I like to do a full-text-search on tableA by joining tableB on it's foreign id and ordering the results by the indexed columns of tableB.

Query1 - match all 4 fulltext-columns:

SELECT `tableB`.`id`
FROM `tableA`
INNER JOIN `tableB` ON `tableA`.`tableB_id` = `tableB`.`id`

WHERE MATCH (
`tableA`.`fulltext1`, `tableA`.`fulltext2`, `tableA`.`fulltext3`, `tableA`.`fulltext4`
)
AGAINST (
'+search*'
IN BOOLEAN
MODE
)
ORDER BY
`tableB`.`order1` DESC,
`tableB`.`order2` DESC,
`tableB`.`order3` DESC,
`tableB`.`order4` DESC,
`tableB`.`order5` DESC
LIMIT 0,15

takes 1.6565 seconds.

EXPLAIN Query1:

select_type  table   type     possible_keys     key      key_len  ref   rows            Extra
SIMPLE       tableA  fulltext PRIMARY,FULLTEXT  FULLTEXT    0     1                     Using where; Using temporary; Using filesort
SIMPLE       tableB  eq_ref   PRIMARY           PRIMARY     4     db.tableA.tableB_id   1

no index is used, temporary table was necessary. I don't know what "Extra 1" in second row means.

Query2 - match only 3 columns:

SELECT `tableB`.`id`
FROM `tableA`
INNER JOIN `tableB` ON `tableA`.`tableB_id` = `tableB`.`id`

WHERE MATCH (
`tableA`.`fulltext1`, `tableA`.`fulltext2`, `tableA`.`fulltext3`
)
AGAINST (
'+search*'
IN BOOLEAN
MODE
)
ORDER BY
`tableB`.`order1` DESC,
`tableB`.`order2` DESC,
`tableB`.`order3` DESC,
`tableB`.`order4` DESC,
`tableB`.`order5` DESC
LIMIT 0,15

takes 0.0114 seconds.

EXPLAIN Query2:

select_type table   type    possible_keys  key     key_len  ref           rows  Extra
SIMPLE      tableB  index   PRIMARY        ORD     783      NULL          15    Using index
SIMPLE      tableA  eq_ref  PRIMARY        PRIMARY 4        db.tableB.id  1     Using where

The order of the listed tables has changed. For tableB, the index was used, for tableA no temporary table was necessary.

It doesn't matter which fulltext-column I exclude from the query - as long as I match less than 4 of them, I get the duration and explanation of the showed Query2.
Maybe interesting: the amount of rows is equal in both tables, about 180k.

I'd like to know the reason why this behaviour occurs. I mean, it seems like the whole way of excecution would depend on the number of matched columns.

edit: now I'm completely confused. I deleted the fulltext-index. I matched all 4 (ex-)fulltext-columns (Query1). Now it takes 0.1205 seconds. EXPLAIN shows me that no temporary table is needed; but I wonder how I can match faster in Boolean Mode without having a fulltext-index.


texts:
Domain user MyDomain\UserA has a login with a server role of public and is a user of the msdb database with membership in the SQLAgentUserRole.

The user owns a job, Daily Job. When logged in as the user, if I try to view the job properties, I get this error:

  Cannot execute as the database principal because the principal "guest" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Microsoft Server, Error: 15517)

If I right click on Jobs and Select New Job... I get the exact same error.

I have even given the user membership in SQLAgentOperatorRole but get the same error.

In all my searching online, the only similar issue I've found is this question on MSDN, which is unanswered.

Any ideas? I'm at a loss here.


texts: Using Two LEFT JOIN or use AND with single LEFT JOIN?

texts:
Using two LEFT JOINs i.e.

SELECT <some columns>
FROM Table1 AS t1
LEFT JOIN Table2 AS t2 ON <condition1>
LEFT JOIN Table2 AS t3 ON <condition2>

is this same as using AND in single LEFT JOIN? i.e.

SELECT <some columns>
FROM Table1 AS t1
LEFT JOIN Table2 AS t2 ON <condition1>
AND <condition2>

Both are same or different(in general)?


texts: ORA-01033: ORACLE initialization or shutdown in progress using Oracle Express

texts:
I am using Oracle Database 11g Express Edition.  I'm on Windows 7-64Bit

I get this error when trying to access the database:

ORA-01033: ORACLE initialization or shutdown in progress

I set up the express edition more than a year ago, and I've used it primarily for a single database instance that I access through SQL Developer.  I really do not do much as far as DBA administration on this, so I am not familiar with the points of failure.  But I know this will not fix itself by waiting longer.  I've tried rebooting and waiting several days.  I cannot log in using any userId I've created.

I've googled solutions and am running a console as a system administrator.  Here is what happens when I try to make repairs.

>sqlplus /nolog
SQL>connect / as sysdba
ERROR:
ORA-01031: insufficient privileges

Others that have had the same ORA-01033 do not seem to get this error.

I really don't even need the databases.  If there is a way to wipe my data clean and start again, I'd be happy.  But it is not obvious to me how to even do that.

Any suggestions would be appreciated.


texts: Would this INSERT statement work? Errata in textbook?

texts:
I was taking a practice test for Microsoft Certification 70-461 and wanted to confirm if what I saw was an error (this is from the official Microsoft Press book, Ninth printing. I did not see this on existing errata).

  Question:

  You are adding tables to the Hovercraft database. You create
  a table by using the following statement:

  Create Table Hovercraft.Testing
  ( TestIdentifier uniqueidentifier
  DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
  HullId tinyint NOT NULL,
  TestDate date NOT NULL,
  Registration nvarchar(8) NOT NULL,
  Notes nvarchar(max)
  )

  Which of the following statements can you use to insert data into this table? (Each correct answer presents a complete solution. Choose two.)

The choices are:

INSERT INTO Hovercraft.Testing (Notes, HullID, TestDate, Registration,)
 VALUES (N'Hovercraft requires maintenance.', 142, '20120715', N'AAA320')
INSERT INTO Hovercraft.Testing (HullID, TestDate, Registration, Notes)
 VALUES (124, '20120722', N'AAA321', N'Hovercraft in good condition. Next service in six months' time')
INSERT INTO Hovercraft.Testing (HullID, TestDate, Registration, Notes)
 VALUES (247, '20120722', N'ABCD54321', N'Hovercraft in good condition. Next service in three months' time')
INSERT INTO Hovercraft.Testing (Notes, HullID, TestDate, Registration,)
 VALUES (N'Hovercraft requires maintenance.', 842, '20120709', N'AAB121')

The answer key claims the first two are correct. However, shouldn't the additional apostrophe in the word "months" cause an error in the statement?

I apologize in advance if this isn't the right forum to confirm this.


texts: Storing a music playlist, best approach?

texts:
I am working on a web project that creates playlists online based on youtube links.

My question is how do I store a playlist on MYSQL. What I was thinking about doing was having a table with basically all information. something like this

example table

I could query this and get information "WHERE playlist_name=bob" and get every song in bobs playlist. Does this seem like a good route? Is it a problem that the table will eventually get huge? what about storing each playlist in a separate table, so bobs_playlist would be a whole new table. This would end up with MANY tables, is that a problem? are both approaches valid? is one better than the other? I just cant determine the best way to organize the information.


texts: First Response Kit is missing sp_AskBrent

texts: I just downloaded the First Responder kit. Unfortunately, the 'sp_AskBrent' was not included in the download.  How can I get it to complete the kit?


texts:
In my Linux filesystem, there are many ibd files (InnoDB file per table is ON) which are not named after tables. What are these files and what data do they contain? The question has arisen due to space limitations on the server.
4.2G    ./var/lib/mysql/wift/#sql-ib179-1438865579.ibd
4.2G    ./var/lib/mysql/wift/#sql-ib179-1413146901.ibd
4.2G    ./var/lib/mysql/wift/#sql-ib179-1376672335.ibd
4.2G    ./var/lib/mysql/wift/#sql-ib179-1355103119.ibd
4.2G    ./var/lib/mysql/wift/#sql-ib179-1163730678.ibd
128M    ./var/lib/mysql/wift/customers.ibd


texts: How to get distinct day number with window function (Postgres 9.6)

texts:
I am trying to get the distinct day number with a set of data that contains different dates that are not always consecutive. For example, if I have this data:

      date | id
2019-01-01 | 1
2019-01-02 | 2
2019-01-02 | 3
2019-01-04 | 4
2019-01-07 | 5
2019-01-07 | 6

I need to get the day number, where the day_num is the next unique data from the fist, and NOT the count of days SINCE the first .. e.g.

      date | id | day_num
2019-01-01 | 1  |   1
2019-01-02 | 2  |   2
2019-01-02 | 3  |   2
2019-01-04 | 4  |   3
2019-01-07 | 5  |   4
2019-01-07 | 6  |   4

Use case:
1) I need to know how many distinctive dates someone logged time .. so above I could see that the time was logged over 4 days (last day_num value).
2) I need to see which work day each separate date group is .. for example, above I can see that work done on 2019-01-04 was the 3rd time which this person worked.

for brevity, columns for user_id, hours etc, are not shown

In trying to figure this out, I got as far as

  COUNT(1) OVER (
     ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING
       AND CURRENT ROW
  ) day_num,

.. which of course is not grouping by the date. This is PG 9.6 so I dont have frame_exclusion available .. not sure if that would even have helped.


texts: Clarification on SQL Server database replication / Distribution / Performance

texts:
I started reading about the scaling database lately due to my recent engagement, and so far, I find it interesting and have been able to successfully set up transactional database replication with SQL server 2014 instance. However, I discovered that there's an option for connecting to a remote distribution server as well as using the server as local distribution. I don't fully understand the performance implication of either option.
My plan is to make use of a single SQL Server instance to serve as Publisher, Distribution, and Subscription.

Is it advisable to use the local distribution? If yes, what are the possible performance effect on the server?

I created two databases A (with sample data) and B on the same server with Table transactions. Then set up a publisher to publish from database A to database B's transactions table. I noticed that immediately I completed the subscription configuration,every data in database A now appears in database B's transactions table and subsequent insert to DB A's table is now being replicated on the B's transaction table.

I know that my test transactions are pretty small, so it would have been easier to replicate. What happens when there's an existing database with millions of data and over 400,000 and growing daily read/write requests? Will the initial process of copying existing data affect applications read/write operations on the server? What can be advised to mitigate this?

What server specification is recommended to accomplish this with a single SQL server instance? I currently have a server with 16CPU, 128gb RAM configuration.

The main purpose of this is to set up a read-only database for reporting and analytics purposes in order not to affect the write performance during peak hours.
Thanks.


texts: Kimball Cross Reference Table/Bridge Table

texts:
I'm currently struggling to understand how to apply Kimball methodology to a certain scenario and talk of Outriggers/Cross Reference/Bridge tables are starting to get confusing.

Consider the scenario:

We sell software licences.
Our fact is at the level of a single sale.
We have an Customer dimension.
We have a licence dimension.
We have a licence user dimension.

One customer may purchase many licences.
One licence may have many users.

How do I resolve the one-to-many relationships between the sale fact and the licence dimension and also the one-to-many relationship of the licence dimension and the users dimension?

Bridge tables do not seem appropriate and I cannot find any references in the Kimball documentation to XREF tables even though they are discussed on the (now closed) forums.

Multiple licences are sold as one product so we cannot have a 'licence sale' fact.


texts: how to check if a stored procedure has been called from another stored procedure in mysql?

texts:
So here I am using MysqlYog / Phpmyadmin for my database management.
Now I have lots of store procedure written and now I want to delete a store procedure as it don't have any direct use but I can not because I don't know if this procedure is being called from another procedure or not.
So how can i check that.
FYI :
DB- MYSQL
Scripting Language : Php


texts: Does reordering table rows reduce the time for subsequent ordering with the ORDER BY clause?

texts:
Is there any run-time advantage gained by changing the order of a table's rows to match the expected ordering of the ORDER BY that is in a slow select?

(assuming unique alphanumeric index and no auto-increments if these matter)


texts: Postgres 10 Backup: Should I run the backup in the master or in a slave?

texts:
I have been using the continuous-archiving and Point-in-Time Recovery for backing up my postgres 10 DB https://www.postgresql.org/docs/10/continuous-archiving.html, however, the master database is handling a lot of work load and my slaves, that are read only, are not that busy, so, am wondering:

Is there any difference in running the backups in the master database than running them in one of the slaves?

I create two backups daily, so, I'm confident that it might be one of the reasons why the master database is experiencing much more workload than the replica ones.


texts: Automate Backup of SQL Server Database Structure

texts:
I have a data warehouse which isn't huge only about 16Gb in a Microsoft SQL Server Standard Edition 2014 database. However, now that it forms part of our production system I don't like developing on it because it can affect our end users.

Therefore what I would like to do is automate a nightly process to:

Backup the structure of warehouse1
Restore the structure to a warehouse2 on the same server

So in effect I end up with a mirror of warehouse1 database structure in Warehouse2 database. I can then use this database for development.

As all of the data is retrieved from other locations I don't want to back the data up I just want to do the structure.

Does this seem like the most appropriate method of achieving what I want to do or is there an alternative? If this does sound right would you be able to suggest how I would go about achieving this?

Thanks in advance,

Andrew


texts:
Is there a way to check cpu utilization of services running.
Setup is a two node rac 12c and there are more services running in one node than the other.
Is there a way to find the cpu utilization of each services?


texts: User unable to edit a subscription after migration of SSRS from 2008 R2 to 2017

texts:
I performed a migration of our SSRS 2008 R2 instance to a brand new SSRS 2017 instance.  This was accomplished via Microsoft's documented instructions "Migrate a Reporting Service Installation (Native Mode)":  Backup/Restore of the DBs, setup of the RSExecRole in the system DBs with permissions, import the encryption keys for the old server.  Everything is working well and we're slowly turning on reports in the new environment and turning off in the old after testing.

Everything seems to be working except for this one thing:  We give our DOMAIN\Domain Users group access via Site settings.  It's a System User.  And, for a particular folder of reports, we have a user who is explicitly added to that folder by domain account name with permissions more than sufficient to edit subscriptions.  And, this still works fine.  However, when a user who did not have this explicit access/permissions on the old server was added to the folder on the new server in the same fashion, he is not able to edit an existing subscription.  The error is "The permissions granted to user 'DOMAIN\USER' are insufficient for performing this operation"  This is despite the user not only being a member of all the same roles as the existing user but being added to the System Administrators role in Site settings. (!)

The error occurs when the user clicks the "Edit" link for the subscription.  The subscription page begins to open but the error dialog appears and the page is greyed out.  The user can create a subscription (or at least get the page to open).  It doesn't matter whether the subscription is data driven or standard. It doesn't matter if the subscription existed before the migration or was created after.

Given that the user now has all the permissions possible in SSRS, I'm not sure where to look next.  It's baffling to me that the function works in general, it just doesn't work for someone who was given permissions after the migration.

Both the old and new server are running under the same service account.


texts:
I have a SQL Instance that was previously on an old domain within my company and was moved to a new domain prior to my time as DBA. After the move, none of the logins / AD Groups from the old domain were removed. I recently started removing the old domain logins and since then users are unable to connect. The users are part of the new domain and are members of an AD Group that has appropriate permissions.

I'm at a loss as to why the users can't connect and my next move is to nuke the existing AD Group and rebuild it to try again, but I'd rather not do that just on the off chance that's the problem.

As always, any help is greatly appreciated.


texts: Deleting old large global_log files CSV, CMS & FRM without SUPER privilege

texts:
I have an 12GB old log file /var/lib/mysql/mysql/general_log.CSV which I'd like to delete. I checked my.conf and general_log = 0 now but it was enabled for most of 2016.

TRUNCATE table mysql.general_log.

Returns "1142 - DROP command denied to user"

Is it save to delete or truncate the CMS, CSV & FRM files manually or is there some easy way to reclaim this 12GB space without stopping my server? Can I give my database user super privileges via my SSH root user? I dont have the mySQL root password.


texts: How convert database from SQL Server 2012 TO SQL Server 2005

texts:
I have SQL Server 2012 database which I need to use. But I can't install SQL Server 2012 on my PC.

I use SQL Server 2005 only.

How can I change the database SQL Server 2012 To SQL Server 2005 OR 2008

Or help with installing SQL Server 2012


texts: Clustered index compression vs table compression - are they the same thing?

texts:
If I set compression (either page or row) on the clustered index of a table, is that the same as setting compression on the table?

SQL Server provides options for doing both, which suggests that they are different, but I was under the impression that a clustered index and a table were essentially the same thing, and my mental model of how clustered indexes work tells me that compressing the clustered index must also compress the table.


texts: Clarification on usage of Serially Reusable Pragma for Oracle 10g

texts:
I need to make packages for Oracle 10g that can be changed while users are still logged in an using an application that makes database calls through PL/SQL. Unfortunately, anytime a package is updated while users are using it they get the following error:

ORA-04068: existing state of packages has been discarded

This makes it really difficult to make changes that are still compatible with the application that is using these PL/SQL packages.

I read that by using PRAGMA SERIALLY_REUSABLE the ORA-04068 error can be avoided by:

Removal of package variables
Trap ORA-04068 and retry
Separate globals package
Move package variables to a context
Versioning code via Edition-based Redefinition (not available for
10g)

After reading through lots of documentation, I keep stumbling upon the same warning for using the SERIALLY_REUSABLE pragma.

Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are called from SQL statements.

The second half of this warning or other PL/SQL subprograms that are called from SQL statements is difficult to understand and I have not been able to find any examples through the Oracle website, Ask Tom, dba-oracle.com or any other website that provides information about Oracle products.

Question:
What are some examples for invalid usage of packages that implement the SERIALLY_REUSABLE pragma and will I be able to use this with client-side reference cursors?

Example Package 1

CREATE OR REPLACE PACKAGE example_package_1 AS
    PRAGMA SERIALLY_REUSABLE;

    FUNCTION get_database_system_time(
        date_time_format IN VARCHAR
    ) RETURN VARCHAR;

END example_package_1;
/
CREATE OR REPLACE PACKAGE BODY example_package_1 AS
    PRAGMA SERIALLY_REUSABLE;

    --------------------------------------------------------
    -- example 1
    --------------------------------------------------------
    FUNCTION get_database_system_time(
        date_time_format IN VARCHAR
    ) RETURN VARCHAR AS return_value VARCHAR(256);
        formatted_system_time VARCHAR(64);
    BEGIN
        SELECT
            TO_CHAR(sysdate, date_time_format) INTO formatted_system_time
        FROM
            dual
        ;

        RETURN formatted_system_time;
    END get_database_system_time;
END example_package_1;

Example Package 2

CREATE OR REPLACE PACKAGE example_package_2 AS
    PRAGMA SERIALLY_REUSABLE;

    FUNCTION get_time(
        locale IN VARCHAR
    ) RETURN VARCHAR;
END example_package_2;
/
CREATE OR REPLACE PACKAGE BODY example_package_2 AS
    PRAGMA SERIALLY_REUSABLE;
    --------------------------------------------------------
    -- example 2
    --------------------------------------------------------
    FUNCTION get_time(
        locale IN VARCHAR
    ) RETURN VARCHAR AS return_value VARCHAR(256);
        formatted_time VARCHAR(64);
    BEGIN
        IF locale IS NOT NULL THEN
            formatted_time := example_package_1.get_database_system_time('HH24:MI:SS');
        END IF;

        RETURN formatted_time;
    END get_time;
END example_package_2;


texts: Number of locks acquired during query execution

texts:
I execute a statement in SSMS like:

select Ident
from PART_Stamm
where VerketteterArtikelID = 'D9FA5CEC-E3C5-11E6-B088-F079596E5F58'

There I can show the execution plan. This works perfectly. Now I need to also show the number of locks acquired on table PART_Stamm during the execution of the query above. How can I get this information?


texts: Hybrid Synchronous / Asynchronous Replication?

texts:
We have a situation where we would like to have synchronous replication in our main data center, but replicate asynchronously to another region. Our second data center is 3,000 miles away (Site B) and we use it as a warm backup in case we need to failover our primary data center (Site A) due to a data center failure.

We are not storing any financial or transactionally sensitive data. Our data is important, of course, but it is not critical to that degree, it consists primarily of updates to website content from content editors.

We are interested in using either Percona XtraDB or Galera Cluster in Site A mixed with something like MySQL, MariaDB or Percona Server for Site B, but don't know if this is even possible or how we might implement such a solution. Can we have a synchronous replication scheme in Site A with an asynchronous backup to the Site B servers? Would we be able to maintain a multi-master implementation in Site A with replicas in Site B?

If it is possible, I assume it would require multiple technologies. What technologies or configurations are feasible solutions?


texts:
Trying to figure out what went wrong here. Any advice would be appreciated. This server is running a new installation of Percona Server 5.6.19 on CentOS 6.5

16:05:46 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Please help us make Percona Server better by reporting any
bugs at http://bugs.percona.com/

key_buffer_size=1073741824
read_buffer_size=1048576
max_used_connections=188
max_threads=2002
thread_count=177
connection_count=176
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 7228203 K  bytes  of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x17e85e60
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f7c813f8d40 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x2c)[0x8bb80c]
/usr/sbin/mysqld(handle_fatal_signal+0x479)[0x6504b9]
/lib64/libpthread.so.0[0x3af3a0f710]
/usr/sbin/mysqld(_ZN10ha_archive10unpack_rowEP11azio_streamPh+0xf5)[0xadb385]
/usr/sbin/mysqld(_ZN10ha_archive8rnd_nextEPh+0x7a)[0xadb8ba]
/usr/sbin/mysqld(_ZN7handler11ha_rnd_nextEPh+0x5d)[0x597c5d]
/usr/sbin/mysqld(_Z13rr_sequentialP11READ_RECORD+0x20)[0x7f5780]
/usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x101)[0x6adbf1]
/usr/sbin/mysqld(_ZN4JOIN4execEv+0x2b8)[0x6ace68]
/usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_P10SQL_I_ListI8st_orderESB_S7_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x275)[0x6f56b5]
/usr/sbin/mysqld(_Z13handle_selectP3THDP13select_resultm+0x165)[0x6f5f15]
/usr/sbin/mysqld[0x559ee0]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x1cec)[0x6cfe3c]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x5c8)[0x6d5078]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xfa7)[0x6d6807]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x162)[0x6a4882]
/usr/sbin/mysqld(handle_one_connection+0x40)[0x6a4970]
/usr/sbin/mysqld(pfs_spawn_thread+0x143)[0xb0dc13]
/lib64/libpthread.so.0[0x3af3a079d1]
/lib64/libc.so.6(clone+0x6d)[0x3af36e8b5d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f7c380009a0): is an invalid pointer
Connection ID (thread ID): 71
Status: NOT_KILLED


texts: For SSL renewal procedure in Db2 LUW , DO i need to create CSR request for each instance residing in same server?

texts: I am trying to renew my SSL certs . Stuck here if we need to have  create a Certificate Signing Request for each instance residing on the same server and send it to CA who will provide us the certificates .


texts: How to select data in a sub query only once?

texts:
I have the following query:

SELECT
  ordno
from
  orders
where
  ordno NOT IN (SELECT ordno FROM invoices)

There are about 25000 records in each table. I have indexed the ordno column.

The query hangs without showing any output.

I imagine that the problem is that the select subquery is called 25000 times for each ordno in orders table.

How can I do this?

SELECT
  ordno
from
  orders
WHERE ordno NOT IN (
--  somehow select ordno from invoice table only once
)


texts: How to obtain SQL feature list from the command line

texts:
Is there a command at the CLI or in PowerShell that will list the components installed for SQL?  I'm looking for something like the Feature List you can get from running the discovery report from Tools in the SQL Server Installation Center program option.

I'm running SQL Server 2012 on the CORE version of Windows Server 2008 R2 Enterprise.  I've searched around the Net, but haven't found anything useful.


texts: Suppressing SELECT output before \gexec?

texts:
This answer is great for when I needed to reown 150 tables, each with 40 tables, but displays the 6000 rows from SELECT before running the 6000 ALTER statements.
Thus, as stated in the Subject, is there any way to suppress the SELECT output?
sides=> SELECT format(
sides(>   'ALTER TABLE %I.%I.%I OWNER TO sides_owner;',
sides(>   table_catalog,
sides(>   table_schema,
sides(>   table_name
sides(> )
sides-> FROM information_schema.tables
sides-> WHERE table_schema = 'strans';
                                       format
---------------------------------------------------------------------
 ALTER TABLE sides.strans.foo OWNER TO sides_owner;
 ALTER TABLE sides.strans.blarg_p2020_02 OWNER TO sides_owner;
 ALTER TABLE sides.strans.blarg_p2020_03 OWNER TO sides_owner;
 ALTER TABLE sides.strans.blarg_p2020_04 OWNER TO sides_owner;
 ALTER TABLE sides.strans.blarg_error_p2019_01 OWNER TO sides_owner;
 etc
 etc
 etc


texts: How to write a script to push the server time to out?

texts:
Background:

I am programming a database in Ms-Access and using SQL-server as the Back-end. But since my database will be a multi-user platform I need to make sure that whenever a user is posting data into the back-end that their computer time is the same as the SQL-server time to ensure that record locking is being effectively used and the data is being timestamped properly.

Extra gibberish:

I only had a major concern for this when I saw a user in my company with his computer time set in the future!!! He was exactly 12 years ahead of us but had no idea! Hence this I have been searching for a solution to keep everyone on the same page.

Question:

Is it possible to write a script that will push the SQL-server time out on to networked client computers?


texts: Parallel option performing worse on cluster

texts:
I'm trying out oracles parallel option on a cluster and surprisingly I am getting worse results with the parallel option. I was expecting some improvement with the parallel option but certainly not worse results. I am wondering why this is the case, and if there is anything wrong with the way I am using the parallel option on my cluster.

I am using a degree of 4 when the number of CPU's I have is 8. I have tried adding parallel to the cluster directly ALTER CLUSTER cluster PARALLEL 4 as well as at the statement on both the index /*+ PARALLEL_INDEX(clust_index, 4) */and tables /*+ PARALLEL(4) */,

Here is my trace from the parallel:

Without parallel:


texts: Does PostgreSQL support online schema modification (DDL)?

texts:
Recent versions of MySQL (using InnoDB) support many alter table queries without exclusive locking the table.

Does PostgreSQL support this to any level? That is, can I add/drop columns from a table while I concurrently read/write rows to it (even if performance is degraded)?


texts: Feasible to have thousands of users in Postgres?

texts:
We are creating SAAS where we will at most have 50.000 customers. We are considering creating a user in the Postgres database for each customer. We will map each user that logs into our service to a user in the database in order to be very sure that they only have access to their own data. We also want to implement an audit trail directly in the database by this solutions, which utilizes triggers. If each customer has its own database user, then it would be very easy to see who did what, even if two customers would share the same data.

Will we be running into some unexpected problems because we have 50.000 users in our database? Performance-wise or administration-wise. Maybe connection pooling would be more difficult, but I do not really know whether we would need it.


texts: Partitioning not working need primary key?

texts:
We are trying some partitioning as below.

ALTER TABLE tblreceipt PARTITION BY RANGE (MONTH(receiptDate))
(
PARTITION p1 VALUES LESS THAN (TO_DAYS('2012-10-20')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2012-10-21')),
PARTITION p3 VALUES LESS THAN (TO_DAYS('2012-10-22'))
);

but end up with this Error Code: 1503
A PRIMARY KEY must include all columns in the table's partitioning function

I have done the changes now.

ALTER TABLE tblreceipt PARTITION BY RANGE (receiptDate)
(
PARTITION p1 VALUES LESS THAN '2012-10-20',
PARTITION p2 VALUES LESS THAN '2012-10-21',
PARTITION p3 VALUES LESS THAN '2012-10-22'
);

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2012-10-20',
PARTITION p2 VALUES LESS THAN '2012-10-21',
PARTITION p3 VALUES ' at line 3


texts: Looking for example design of using an SQL database as a queue for throttling web service requests of multiple users

texts:
I'm designing a system for multiple users to queue their requests to a web service which takes requests such as creating new entries, changing entries, requesting data.  Many of the web service calls we will be using take hierarchical data as parameters (heirarchies designed to match c# object structures) while others such as a request for the list of allowable entry types, require no parameters.   The queue is necessary because the web service has a 5 second or so throttling requirement, and only one web service login will be used by multiple users.

xml Data returned from the web service will eventually come back, be parsed by our own server app, and inserted to a response table of some sort so the user app can know the request was received and also get any data that are returned.

basic picture is

user app (multiple users) --> service request queue <--> server app <-->web service

and responses coming back similarly

user app (multiple users) <-- service response queue <--> server app <-->web service

I'm creating the user app and server app myself, and just trying to get a handle on how to design the service request queue and response queues which will be using sql server and trying to figure out just how many tables these queues will have since requests and responses come in hierarchical form (different hierarchy types are returned as well depending on the request), non hierarchical form both.

Objects like these are specified by the external web service API.  Users are sending different types of requests in different related forms of this object structure, which is going to be stored in the queue system/queues, then picked up by the server app for dequeuing to meet a throttle requirement for the web service.  The server app might also cancel irrelevant requests, such as if too much time has elapsed and it suspects a faulty or non-relevant request, or such as if the user app is not synchronized with the data stream from the web service.

Anyone have any simple example multiple user sql server queues used for this purpose?  Is there any good book on this topic? or web tutorial perhaps? seems necessary for throttling multiple users of any web service...


texts: Informix DB export to Microsoft SQL Server 2016

texts:
I have an Informix Database and a Microsoft SQL Server 2016.
My task is to dump the whole Informix Database and export it to the Microsoft SQL Server Database.
I installed the Informix SDK 4.5. I also have installed the ODBC drivers 32bit/64bit. The connection is working fine.

I linked the Informix Server within the Microsoft SQL Server Management Studio, but i was not able to get table information, so i found a workaround in IBMs Documentation.

Now I wantet to try an export with Import and Export Data (64-bit). I chose the data source as IBM Informix OLE DB Provider

Data Destination is SQL Server Native Client 11.0

As a test export i choose some tables. The table Mapping looks fine for me

The next step shows the Type Conversion with an error, but i dont know why.

Then I Run immediatly

[Source Information]
Source Location : sospos@sosposdbtest
Source Provider : Ifxoledbc.4
Cannot locate the mapping file to map the provider types to SSIS types

After that I get errors and warnings.

Warning:

Could not connect source component. Warning 0x80202066: Source - auswahl_hoefken [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

Error:

Error 0xc02020e8: Source 27 - antrzusatz [157]: Opening a rowset fo ""sospos":"sosgx"."antrzusatz"" failed. Check that the object exists in the database.

I get this Warnings for several tables..

I think there is a problem with the table mapping or something like that, but I'm not sure..

Any ideas?


texts: Error: FATAL: no pg_hba.conf entry for host - PostgreSQL - pg_hba.conf is empty

texts:
I upgraded the PostgreSQL database from version 10 to 11. As I tried to access the database from a client pgadmin4 console, I got the below error:

I checked the two file postgresql.conf and pg_hba.conf in the system. However, when I tried to open the pg_hba.conf file, it comes up blank. I followed the steps for the upgrade (for Ubuntu users)in the link below:

https://stackoverflow.com/questions/24379373/how-to-upgrade-postgresql-from-version-9-6-to-version-10-1-without-losing-data

I know this error might get resolved if I allow privileges in the pg_hba.conf file. But that file does not show any data in it. I am a beginner in setting up database servers and could have unknowingly skipped something which I cannot figure out. Can anyone please help me with this?

Adding the image for pg_hba.conf file:


texts: Row level security in SQL Server 2016 with a normalised schema

texts:
SQL Server 2016 supports row level security, which is great. However all of the samples assume that the username is inside the table you wish to apply security to (see example below- from MSDN https://msdn.microsoft.com/en-us/library/dn765131.aspx).

CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
GO

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales
WITH (STATE = ON);

If I had a more normalised schema, my username would be in a separate table, using an ID as a foreign key. In this situation, how would I use row level security to check for a user name? Can I add a join to the filter predicate?


texts: Oracle Creating ER Diagrams and Data Dictionaries

texts: I am new to Oracle Databases and would like to develop a data dictionary and ER Diagrams for our existing databases. Do you have any tips, scripts, tools for doing so?


texts: SQL Server 2008 upgrade to SQL Server 2012 on a cluster

texts: I have a Windows 2008R2 2 node cluster with SQL Server 2008 clustered. How do I prevent the cluster from failing over while I am applying the SQL Server 2012 to the alternate node first? Any pointers on upgrading the SQL Server cluster to 2012?


texts: posgres instance is listening to old port even after changing to new port number

texts:
My machine is having posgres server 11th version,for testing purpose i installed another posgres instance(12 version )in same machine.i changed port number of this new instance to 5433 but when i try to start its referring to old port number.
both the data folders are different.


texts: How to list LOGIN + ROLE in SQL Server 2008?

texts:
I'm looking for an answer but I can't find it. Just a lot of long scripts.

I need to list Login | Role (sysadmin, dbcreator, etc ) but I can't find a table to join.

I have this for now:

SELECT
    name AS Login_Name, type_desc AS Account_Type
FROM
    sys.server_principals
WHERE
    TYPE IN ('U', 'S', 'G')
    AND name NOT LIKE '%##%'
ORDER BY
    name, type_desc

I need another column with the role.

With table should I search for it?

Thanks.

EDIT:

I see that sys.syslogins has some information with binary.

Is it possible to use a CASE here? ( Programming is not my best )


texts: Merge 2 tables and fetch data one by one

texts:
I have two tables: product & offers.

products has (prod_id, category_id, prod_name, brand, price, status)
offers has (offer_id, prod_id, offer_type, discount_amt, validity)

I want to display products on a page, 20 products at a time.

The problem that I want to display all the products from the offers table arranged by descending offer_id and then display products from the products table except those in the offers table within that 20 limit.

How can I achieve that?


texts:
I'm new to SQL and Im trying to create a simple database, but I'm having problems with second one, I'm using Oracle Database express 11 and when I try to run this code I get error: Missing right parenthesis

CREATE table "CURSOS" (
    "Nombre_curso" VARCHAR2(20) UNIQUE,
    "Cod_curso"  VARCHAR2(10),
    "Apellido2"  VARCHAR2(15),
    "NIF_Prof"   VARCHAR2(9),
    "Max_al"     INT (2),
    "Fecha_ini"  DATE,
    "Fecha_fin"  DATE,
    "Horas"      INt(6) NOT NULL,
    constraint  "CURSOS_PK" primary key ("Cod_curso"),
    constraint  "CURSOS_CK" CHECK (Fecha_ini<Fecha_fin),
    constraint  "CURSOS_FK" FOREIGN KEY (Nif_Prof) REFERENCES PROFESORES,
)


texts:
I have a peculiar issue with some database connections: I've got this (test) script on 2 different servers:

$link = mysql_connect($db_server, $db_user, $db_passwd);
if (!$link) {
    die('Could not connect: ' .mysql_errno().":". mysql_error());
} else {
    echo 'Connected successfully';
}
mysql_close($link);
exit;

On one server, there's no issue at all. On the other server, the connection is refused from time to time, with the following message:

Could not connect: 2002:Operation not permitted

Obviously, both hosts aren't blocked by the db server firewall (but I've explicitly whitelisted the problematic IP address anyway)

Most forums indicate it might be a problem with the lock file, mysql-server not running, or any global issues, but it seems like this specific remote host is blocked, all other websites and remote hosts connect perfectly.

I've attempted to set the global max_connect_errors to 10.000 , but that didn't help either. I do believe it has to do with connection errors from the offending server. It has some slow query issues already, and with the site being visited more often, the issue comes back more frequently. After (exactly) ten minutes, the remote host is unblocked again, and everything works as it should.

I've also tried restarting mysqld, rebooting the server.
But I'm running out of options. Any thoughts on how to prevent this, or at least resolve it when the issue occurs?


texts:
Just to give a quick DDL to make my question easier to understand:

------------------ creating tables ------------------
create table if not exists users(
    user_id bigserial primary key,
    first_name text,
    last_name text,
    user_name text
);
create table if not exists managers(
    manager_id bigserial primary key,
    user_id bigint references users(user_id) not null,
    permissions text[]
);
create table if not exists transactions(
    transaction_id bigserial primary key,
    user_id bigint references users(user_id) not null,
    amount numeric,
    is_approved boolean,
    manager_id bigint references managers(manager_id) null
);
------------------ inserting data ------------------
insert into users(first_name, last_name, user_name)
values ('first_name','last_name','ohyea'),
  ('manager','manager','manager');

insert into managers(user_id)
values (2);
insert into transactions(user_id, amount, is_approved, manager_id)
values (1,10,true,null),
  (1,1000,true,1);

Now I am basically going to get manager's name using this way:

select
  t.transaction_id,
  t.user_id,
  u.user_name,
  t.amount,
  t.is_approved,
  mu.user_name as manager_name
from transactions t
    join users u on u.user_id = t.user_id
    left join managers m
        join users mu on mu.user_id = m.user_id
    on m.manager_id = t.manager_id;

The part that I am interested in is this:

    left join managers m
        join users mu on mu.user_id = m.user_id
    on m.manager_id = t.manager_id

What is this to PostgreSQL? A suquery? Like how does it interpret this and what's the difference between doing something like this, and this:

left join (
    select m.manager_id, u.user_name
    from managers m
        join users u on u.user_id = m.user_id
) mu on mu.manager_id = t.manager_id

EDIT:

After a lot of testing, turns out both approaches are absolutely, 100%, 1:1 identical. Same execution plan, same execution times (with small millisecond variations), the only benefit you gain by using the first approach is just cleaner code and easier to follow in bigger functions with a lot of subquery joins.


texts: Enabling TDE on SQL Server AOAG 2014 Databases - Guidance

texts:
I am completely new to Transparent Data encryption and please help me on below questions related to enabling the TDE on existing PRODUCTION Databases.

Primary Question:

What are the things which I should keep in mind before enabling the TDE on PRODUCTION servers ?

NOTES

I have SQL Server 2014 AOAG Databases PROD site. The number of DBs is close to 10.
I read about DEK , SMK , Certificate creation process and backup of Key's and certificates.

Questions:

Now the Databases are TDE free ,what will be the new changes or effects which I will be seeing post enablement of TDE in my PROD site.
What is the performance impact on my existing PRODUCTION database accessibility.
Will there be any performance impact at all ? What is the range of impact after enabling TDE on my DBs ?
What will be the effect on the AOAG component Failover part ? Will I face issues while the Databases are with TDE enabled ?
What will be the affect while Backing up or Restoring the Databases to different SQL Server ?
If I enable TDE on Primary Replica and all secondaries will I be able to add the DBs to AOAG 2014 using the Wizard.

Please guide me , I was not completely sure of the consequences of this change handling , please help me know the disadvantages related to existing situation and post enabling TDE and performance related issues and points.

Many thanks.


texts: Is it good practice to refer to a table with different names in a database schema?

texts:
We have a database currently running with a table named a certain way (let's say thing for the sake of example), and references to that table named the same way (FK would be named thing_id for instance).
We also used to have this thing concept in the code at some point, but then it was decided for various reasons to rename it (let's say to stuff), which was done over time. Now, the code no longer mentions thing except in SQL queries.
We unfortunately can't easily rename the table or columns since they're being heavily used, and we were wondering how to deal with new tables referring to this concept of thing/stuff.

Some of the team think that the schema should be the most up-to-date as possible, and that we should use the new naming (stuff) everywhere new (in join table names and FK names referring to thing)
Others think that the database schema should always stay consistent, and thus we should keep using the old naming (thing) anywhere, and handle the renaming in the code itself

We're trying to understand what are best practices here and what is advised to do in this situation where the DB schema and the code naming diverge?


texts: Linked Server from SQL Server to SSAS not working - what is missing?

texts:
I have a SQL Server 2012 Standard edition sp1 that has ssas also installed.

From this server I create a linked server to a ssas server called BIREPLON1, but when I try to access it, I get the error message shown below.

the funny thing is that when I go through GUI, by clicking Server Objects\linked servers\BIREPLON1 all seem to be ok (as you can see below on picture number 4).

But when I run the simple select:

SELECT CATALOG_NAME
FROM OPENQUERY([BIREPLON1], 'SET FMTONLY OFF;SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS') as Radhe

I get this error message:

  Msg 7303, Level 16, State 1, Line 1
  Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "BIREPLON1".

Have I missed out on something?


texts:
I want to determine if a resultset exists very quickly. At the moment, I'm doing a count - this is taking roughly 55ms, which is unfeasable.The table has ~100k records - I don't care if it has 2, 5, 100k rows that fit a query; I care if it has 0 or 1. Maybe 2 in certain situations.

Is there a way to do this? Would limiting a count using ROWCOUNT (so it only counts about the first 2 rows it finds) speed up the count at all?


texts:
I have seen that there are similar questions like this but I tried all of them and my problem still exist. I want to backup a db.

I created a shared folder on NAS
I can reach this shared folder from windows explorer on DB server like \\10.1.1.x\yedek_db. I can create and delete files in this folder.
I checked all the services related to SQL Server. Previously they were running using network service account and I changed it to domain administrator (which has full access to the NAS folders)  and restarted the services.
I executed the following command on command line

net use H: \\10.1.1.x\yedek_db

to map the NAS folder to H:. After this I can reach that folder by typing h:.
I created a backup device on SQL Server which is named NAS_YEDEK_DB. The drop and create script is below.

IF  EXISTS (SELECT name FROM master.dbo.sysdevices WHERE name = N'NAS_YEDEK_DB')
EXEC master.dbo.sp_dropdevice @logicalname = N'NAS_YEDEK_DB'
GO

EXEC master.dbo.sp_addumpdevice  @devtype = N'disk', @logicalname = N'NAS_YEDEK_DB', @physicalname = N'H:\'
GO

I try to backup using this backup device by the following code:

BACKUP DATABASE evrak
TO NAS_YEDEK_DB
WITH DESCRIPTION = 'Test DB Backup';

But I get this error :

  Msg 3201, Level 16, State 1, Line 1
  Cannot open backup device 'NAS_YEDEK_DB(H:\)'. Operating system error 3(failed to retrieve text for this error. Reason: 15105).
  Msg 3013, Level 16, State 1, Line 1
  BACKUP DATABASE is terminating abnormally.

I will be very happy if I can solve this problem.


texts: How to make Event (like a Party) Sources mutually exclusive?

texts:
I'm designing an entity-relationship diagram (ERD) for a marketplace of events. An event can be created by a user, in which case the Event table must have owner_id or we pull it from an API in which case the Event table must have api_id.

However, it can only be one way or the other. So, an event can be created by a user or pulled from API, never both.

How do I express owner_id and api_id as mutually-exclusive in ER diagram and how will it translate in tables?

This is the ERD I have developed so far:


texts:
We are running SCCM 2012 r2 Reporting services and the SQL Server log file has got out of hand due to the backup not being set up properly and I'd like to confirm something before I continue.

I have set up our backup solution to do full backups of the DB and an incremental that is set to truncate the log on a regular schedule now but from what I've read, shrinking your log file is a bad idea due to performance issues, but it seems like this is the sort of case where it seems appropriate as we're sitting around 220GB for the log file.

Firstly, should I definitely be attempting to shrink the log file and what is the best way to do it? Is it simply by using DBCC SHRINKFILE?

Secondly, I believe I should be setting a reasonable limit on the log file size after shrinking but I'm unsure how to also come up with this limit. Is it just a size I'm happy with it taking up in-between truncation backups or is there a best practice.

As you may tell I'm not that familiar with SQL Server so I apologise if I've worded anything incorrectly. Thanks in advance.


texts:
I have migrated my single MariaDB setup to a 3 node Galera cluster. I am currently in staging and most of normal operations run fine.

I have one issue though: if I do a mass update of one column of one table (500K rows) then the new value is available on the node where I ran the SQL UPDATE immediately after the transaction is done while it takes several seconds to propagate to the other nodes.

I thought Galera cluster was 100% synchronous and thus suprised to see that as it means that users could see different data...

My conf is:

[mysqld]
binlog_format=ROW
default_storage_engine=InnoDB
innodb_flush_log_at_trx_commit=0
innodb_autoinc_lock_mode=2
innodb_doublewrite=1
query_cache_size=0
wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_provider_options="gcache.size=300M; gcache.page_size=300M"
wsrep_cluster_name="xxxxx"
wsrep_cluster_address="gcomm://192.168.0.100,192.168.0.101,192.168.0.102"
wsrep_node_address="192.168.0.102"
wsrep_sst_auth="sst:yyyyyy"
wsrep_sst_method=xtrabackup-v2
wsrep_on=ON

Is this really expected or is there something to tweak?

Thanks!


texts: syspolicy_purge_history 3rd step runs forever on a cluster node?

texts:
I have a SQL Server cluster.  On one of the nodes, the syspolicy_purge_history job runs forever on step 3.

I did stop and restart the job, however, again it is running forever at step 3.

How can I resolve this issue?


texts: What is the purpose of the NOLOGGING clause on Oracle database TABLES and INDEXES

texts:
I was wandering what is the practical purpose of using the NOLOGGING clause on tables and indexes.

For what I know from the Oracle documentation, we can prevent the database from generating redo logs only when INSERTING in direct-path mode. There is no possibility to avoid redo log generation when UPDATING or DELETING.

So, we usually write

INSERT /*+ APPEND NOLOGGING */ INTO TABLE ... ;
commit;

in order to prevent redo log generation.

I totally don't get what is the practical advantage of using the NOLOGGING clause on TABLE or INDEX creation neither to use the NOLOGGING clause on TABLESPACES creation in order to set the default on the objects that will be created on that tablespace.

Can anyone describe a practical scenario where setting this clause on database objects may give advantages?


texts: Fastest way to create local copy of live InnoDB MySQL database?

texts:
We have a MySQL database (InnoDB) on our live Ubuntu 12.04 server which has grown quite large (25gb+).

Every month or so we need to copy this database to our development machines to work on locally.

Previously we have exported the database with MySQL Workbench, downloaded the .sql file to our development machines, and then imported it, again with MySQL Workbench. The problem is now the database is so large, we can't do the restore quick enough for it to finish over the weekend! So on Monday morning we have a half-imported database to work with.

What do other people do in this situation?

The only solution I can think of so far, is doing the restore to an unused machine, then copying the data to all our development machines (5 of them). Would this work?


texts:
I've been trying to COUNT(*) a table with 150,000 rows that has a Primary key. It tool about 5 minutes, so I figured out this is an indexing problem.

Citing the PostgreSQL manual:

  REINDEX is similar to a drop and
  recreate of the index in that the
  index contents are rebuilt from
  scratch. However, the locking
  considerations are rather different.
  REINDEX locks out writes but not reads
  of the index's parent table. It also
  takes an exclusive lock on the
  specific index being processed, which
  will block reads that attempt to use
  that index (...) The subsequent CREATE INDEX
  locks out writes but not reads; since
  the index is not there, no read will
  attempt to use it, meaning that there
  will be no blocking but reads might be
  forced into expensive sequential
  scans.

From your own experience, can you tell:

is REINDEXING dangerous? Can it harm the data consistency?
Can it take a lot of time?
Is it a probable solution to my scenario?

Update:

The solution that worked for us was recreating the same index with a different name, then deleting the old index.

The index creation is very fast, and we've reduced the index size from 650 MB to 8 MB. Using a COUNT(*) with between takes only 3 seconds.


texts: Clustered Index Maintenance vs. Nonclustered Index Maintenance

texts:
There is a plethora of information available detailing why an IDENTITY field should be use as both the primary key and clustered index of a table for most situations; yet I'm having difficulty deciding if my particular situation is an exception.

I have a table with around 250 columns—I don't want to start a normalization debate—and around 100M rows. The table is page compressed. On peak days, around 1M rows are entered into the table sequentially with 0 contention from other connections. The combination of a char(2) NOT NULL field and an int NOT NULL field is guaranteed to be unique and never changing. There are only about 10 unique values for the char(2) field.

My argument for an IDENTITY column being used as the clustered index is solely performance based. Since inserts will happen sequentially, I shouldn't haven't to worry about latch contention; thus the inserts should be faster since there will be no need to search the index. On the other hand, a composite key of 6 bytes is rather small; and I'll end up making it the primary key anyway (with the char(2) field first). Furthermore, there will be a few other tables that are bulk loaded based on the new rows in this table (likely via a filtered index). If I use an IDENTITY column, I'll likely use that column as the clustered index and primary key of these other tables (without the IDENTITY property) and not bring over the "natural" composite key.

Does making a nonclustered index on the composite key nullify the expected speed boost of using an IDENTITY column as the clustered index since this index will have to be searched when the inserts are done?

Edit

Based on the comments, I changed the title and am asking an amended question whose answer can be used to answer the original question above. How does maintenance of a unique clustered index compare to maintenance of a unique nonclustered index in regards to inserts? Does a non-sequential clustered index suffer similarly to a non-sequential nonclustered index?


texts: Dumping mssql db from one server and restoring on another on Mac/Linux

texts:
Using either MacOS or Linux, my objective is to copy a database from one MSSQL database server to another.  The source database server is remote, while the target database server (which will be restored from the source) is running on port 1433 on a local docker container.
Unlike for postgres (pg_dump utility is well-documented), I have not found much documentation online about how to practically use command line tools like sqlcmd (which are avaiable after brew install mssql-tools), to connect to, copy and restore a database on SQL server. Are there any suggestions for a basic template of commands to use for copying databases and restoring them using the terminal in MacOS/Linux?


texts: Page life expectancy very low on certain time?

texts:
Everyday we receive an alert on a specific period (03:00 AM - 04:00 AM) indicating that Page life expectancy is too low :

SCOM :Alert: SQL DB 2008 Engine Page Life Expectancy is too low

We have reviewed if any Memory intensive operation is running on this period but nothing was found.

We assume that the cause is a query run through a windows service that cause pages life in the buffer cache to reach a very low value (8 - 22 seconds)

what is a good way to troubleshoot this issue ?

Thanks
Osama Waly


texts: SQL Server - Uniqueness Of The Transaction ID

texts:
For auditing purposes, I need to get the unique transaction id from inside the current transaction. I will log the mapping between this transaction id and the application specific session id in a table. Then when looking at the transaction logs, I can find out which application session is responsible for a change in a row.

But it seems that transaction id is not unique for the database and is reset to 0 after a server restart. Querying sys.dm_tran_current_transaction proves so.

So, how can I get such a unique id for a transaction in the transaction logs? Or should I use SQL server audit? Is there a way to query the audit id from inside the transaction?


texts: Restarting SQL Server if shrinking database files

texts:
If frequently shrinking database files is it smart to restart SQL Server/ or service ?
I currently work in a company (Mainly analytics databases , using SIMPLE Recovery Model)where we have to shrink the data files sometime to reclaim  space on the disk (Although i did say to my manager shrinking data file is not a smart think to do. Shrinking the log are fine).
I suggested to my manager that we restart the server as we have been shrinking the data file a lot this month.
Can you share your thought and expertise on the subject.


texts: MongoDB secondary node does not become primary when other nodes are stopped

texts:
I want to cluster MongoDB by using a replica set. I have created a three-node replicaset (rs0,rs1,rs2), and run the DBs in parallel with no issues. But when I shut down any two instance (one primary and secondary), the remaining secondary member is not made the primary, instead it remains as a secondary.

Step1:

rs0: primary
rs1: secondary
rs2: secondary

Every thing is ok. When

Step2:

rs0: stopped
rs1: stopped
rs2: secondary  (This is not promoted to primary)

Why?


texts: How is powershell commonly used in SQL server?

texts: I am wondering how is powershell used in SQL Server? I heard people saying that powershell makes a DBA's life easier. But I dont know how is powershell commonly used in SQL server. Can anyone provide me insights or links as to how powershell is being used in SQL Server?


texts: What exactly is INTERVAL 1 HOUR checking?

texts:
MariaDB 10.1.x

Let's say I have a query like:

select FOO from db.BLAH where STAMPFOO > NOW() - INTERVAL 1 HOUR

If I run that query at 12:15 PM, will I only get records that have a date value greater than 11:15 AM?  Or greater than 11:00 AM?


texts: Is there a way to use my Access accdb or accde file (Created on the desktop) on an ipad?

texts:
It would need to be able to read [AND write]***redacted. I only need it to read info. I can store the database either on the network or on the cloud (Microsoft Azure). Is there an app or another way to be able to use my split accdb or accde file on the ipad?

Thanks.


texts: Indexes in a big table. (About 400 mil of rows)

texts:
I have a little deal. I have this table in a SQL Server 2012 Standard edition database. The table contains about 400 mil of rows.

CREATE TABLE [dbo].[prodeje]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [partner] [tinyint] NOT NULL,
    [id_paragon] [varchar](20) NOT NULL,
    [typ_pohybu] [varchar](2) NULL,
    [store] [varchar](20) NULL,
    [datetime] [datetime] NOT NULL,
    [date] [date] NULL,
    [id_prod] [varchar](50) NOT NULL,
    [customer] [varchar](50) NULL,
    [price_per_unit] [decimal](18, 2) NULL,
    [stock_price] [decimal](18, 2) NULL,
    [pc] [decimal](18, 2) NULL,
    [amount] [decimal](16, 4) NULL,
    [action] [varchar](50) NULL,
    [action_cupon] [varchar](50) NULL,
    [bonus_gratis] [varchar](50) NULL,
    [fin_sleva] [decimal](18, 2) NULL,
    [exclude] [tinyint] NULL
) ON [PRIMARY]

It's a table with sales. So most of the query will include column partner, store, date, id_prod, price per unit * amount and distinct count on column id_paragon for baskets. Column id_prod is foreign key for table with products. Column exclude is column with information if the row is good. All query will contains in where clause exclude=0.

And I am thinking about indexes. Because it's more usually use part of the year, so my first think was that i will do clustered index on column date. And in query i query first only data what i need. So I will query on few mil of rows and it will be quick. And index for id_prod. But I think that is not good way to do the indexes. So I have questions for you, what indexes will you do on this table.

If you need more info write a comment. I will add what you want for think about it.

Thanks


texts: Need to grant select permission on View but not in table which is in a different database

texts:
I have a table in database A and a view using that table in database B. I want To give select privilege to a user on that view but I do not want to give any access to table.

Can anyone help me on that issue?


texts: look for missing datetime records in database

texts:
I have a python script that scrapes a webpage every minute of every day and saves the results in a postgres database.

As I browse through the database, I've noticed there are minutes here and there that are missing from the database. This may be because of my server's downtime or because of the website downtime (but the reason for the gaps is not relevant to my question here).

I'm trying to figure out the best way to catalog what minutes are missing from the database.

Right now I have a python script that creates a list of every minute of every day and use that list to iterate through the datetime column in my database table, something like this:

def pd(start, end, delta):
    c = start
    datelist = []
    while c < end:
        datelist.append(c)
        c += delta
    return datelist

Is there a way to do this directly using SQL?


texts: Storing metadata of various data types in a MySQL database

texts:
I am currently designing a MySQL database that will store a variety of documents in different digital formats (mostly docs, spreadsheets, and pdfs). Each document will have several metadata values associated with it (a receipt might have metadata fields for date, store, and price, while a recipe might have metadata entries for various types of ingredients, serving size, and meal type). In order to be clear, the metadata I am storing is not about the table or database itself but is information about each type of document being stored and will have a different value associated with the metadata type for each document. I don't want to create a new table for each type of metadata being stored, but rather allow the users of the database to create additional metadata fields for each type of document as needed.

The problem I'm running into is the metadata's data type will be different depending on what the field is (for example, a business name field would be a varchar, while a dollar amount field would be a decimal type). I want to be able to search based on this data (all receipts with a date issued value before January 2nd for example), so I can't just store everything as a varchar. Currently I only see two ways to design this database:

Create a separate table for storing each data type (ex. a text_metadata table, decimal_metadata table, and date_metadata table) and another table keeping track of the field name and what type it is.
Have a single table for storing the values of all metadata, but have a different column for each data type and only use the column specific to that metadata field to store the actual data.

Both of these solutions seem really clunky. Does anyone have a more elegant approach to storing these types of metadata values?


texts:
There is a cool program out there called DBEdit2, which can be used for SQL Server. Here is where you can get it free:

http://dbedit2.sourceforge.net/

The problem is that I can't make it work! It gives me the following error (conf info crossed out):

This is what I put in the login details:

And this is what I get if I click details:

Any ideas at all? Can anyone else make this program work?

I've made sure that I have my SQL Server set to allow both SQL Server and Windows authentication mode.

I've ensured that the server is running and that I have browser running.

I don't know.


texts: how to delete all rows with empty field via function/trigger combo in Postgres v 9.3?

texts:
In PostgreSQL 9.3 I am trying to write a trigger function that will be called after updates to table test2 and that will delete rows of test2 where field "email" is empty. Here's my attempt:

CREATE OR REPLACE FUNCTION clean_emp() RETURNS trigger AS
$$
BEGIN
DELETE FROM test2 WHERE email=null;
return test2;
END;
$$
LANGUAGE plpgsql VOLATILE;

Then I set up the trigger like so:

CREATE TRIGGER trig_empty2
AFTER UPDATE OF name ON test2 FOR EACH ROW EXECUTE PROCEDURE clean_emp();

This gives me an error when I then do an update on table test2:

update test2 set name = name || 'fesgses';
ERROR:  column "test2" does not exist

And here's my data table for completeness:

  name  |    email
--------+-------------
 hi     | hiemail
 shoe   |
 shoe2  |
 cathy  |

If anyone can give me some pointers on where I'm going wrong, I'd really appreciate it. Thank you.


texts: Is creating a joining/bridging table the most practical and efficient way of normalizing numerous M:M relationships in a database?

texts:
Let me start with an example:

Table users:

ID | Name
---------
1, Kirk
2, John

Table class:

ID | Class
----------
1, MATH
2, FIN

Now, based on what I studied so far, in order to properly normalize this database, I'd create another table, a joining/bridging table:

Table class_enrollment:

UID | CID
1     1
1     2
2     1
2     2

Well, it works fine in these kinds of examples.

But, what if my database has 35 or 50 M:M relationships? Is it really best to create yet another 35/50 joining tables?


texts: How to find givien index by name in PostgreSQL

texts:
I know how to find index by name in MS SQL Server just by search for %name%
But where I can do the same in PGAdmin4 ?


texts: Choosing a PK: VARCHAR(2) or SMALLINT(2)?

texts:
Today we were discussing the following:

Brazil has 27 states and each one has its own abbreviation (just like USA).
So we've got RJ for Rio de Janeiro, SP for São Paulo, MG for Minas Gerais and so on.

One of our programmers proposed that we should use those abbreviations (RJ, SP, MG, etc.) as PK on the States table that we are planning to add to a new project.

Extrapolating the use of our database, I countered his argument saying that if we were to - someday - expand our services to other countries, we would have a problem with repeated abbreviations, for example: in the USA there is SC for South Carolina and on Brazil we've got SC for Santa Catarina; the same happens for MT, PA and MA. Counting on this we've agreed that there should be a ID column as PK IDENTITY.

Now, supposing that we do not expand our services to other countries and stay only in Brazil I started considering the idea of using a VARCHAR(2) column as PK. In this scenario it doesn't sound like a totally bad idea.
Is it? Why? In which cases this could be applied? Should memory be considered in order to choose from one to another?


texts: I need to create a collate view with couch db but I dont want to use the current records, I want to use records of a specific revision. For example, lets imagine that I have an invoice with an array of products ids (and theirs revision), I want to return the products with the description and prices as was at the invoice creation.


texts: MongoDB 4.2 using change streams with read concern majority disabled in a PSA deployment

texts:
I currently run a PSA (Primary, Secondary, Arbiter) deployment and thous only have two databearing nodes.
In case of a databearing node going down, read concern majority prevented the change stream to be read out of.
In MongoDB 4.2 it is now possible to disable the read concern majority for the replicaset and still use the change stream (See https://docs.mongodb.com/manual/release-notes/4.2/#change-stream).

I disabled read concern majority in my deployment and the change stream works as expected.
If I shut down the secondary node the change stream can't be read out of again.

Is this the expected behaviour? I would have assumed that with 4.2 a majority is not necessary anymore to read the change stream.
If that is the case, is there any way to read the change stream in a PSA deployment with only a minority of data bearing nodes?


texts:
I have a column with default value bound to UW_ZeroDefault and I am trying get rid of it.

As I understand it, this default has been created by using CREATE DEFAULT then the column has been bound to this default by using sp_bindefault.

Usually to remove this default, you would use sp_unbinddefault, but since there is a bug in SQL Server 2008 (see KB article), I am getting an error:

  Invalid object name ' '.

I cannot make any changes to the SQL Server installation, so I am looking for some workarround how to drop this thing.

Summary of the discusion below:

SOLUTION 1:

As suggested by @gbn, if you can get your database back to the SQL Server 2000, do it and use sp_unbindefault there. That should work.

But if you can't move it (that's my case)...

SOLUTION 2:

Make a copy of the table(s) and use CONSTRAINT to set a default instead of binding default. This includes dropping all kind of constraints, FKs, etc. and recreating them afterwards. It is a bad solution, but I couldn't find a better.


texts: How to export an ER diagram from an Oracle DB?

texts:
I am developing this application with an Oracle database. I haven't been documenting the data model, but now an auditor wants to have an entity relationship diagram of my database. There are many tables and creating the ERD in visio manually is out of the question.

I know Toad has Database > Export ER Diagram option, but the result is not exactly presentable because of ugly layout and difficult to edit. Do you know an alternative way which can create a presentable ER diagram? Preferably it will be nice if the resulting diagram is editable and also if the tool is free.


texts: MYSQL Database PIVOT and wants in specific format


texts: How to keep updates (diffs) of some entity in the database

texts:
What is the best way to keep updates (diffs) of the some entity in the database? At StackOverflow we can edit questions and answers. And then we can look at any revision of the question or answer we want. For example:  revisions of some random question. Maybe someone knows how it realized in StackOverflow?

To be clear in my case I have some entity (article) with some fields (name, description, content). Many users can edit the same article. I want to keep history of the article updates (something like version control) and I want to keep only diffs, not the whole content of the updated article. By the way I use PostgreSQL, but can migrate to any other database.

Here is some requirements. You don't need to fully satisfy them. But if you do it will be much better. Nevertheless any answer is much appreciated. So I want to have ability:

to keep only diffs to not waste my space for no purpose.
to fetch any revision (version) of some article. But fetching last revision of the article must be really quick. Fetching speed of other revisions is not so important.
to fetch any diff (and list of diffs) of some article. Article can have changes in fields: header, description or content (like StackOverflow have changes in header and content), so it must be taken into account.


texts: Mixing Clustered Instance and Non-Clustered Instance

texts:
I'm currently managing a 2 node SQL Server Failover Cluster in Active/Passive setup. I plan on adding a new SQL Server instance which doesn't have to be on the Failover Cluster. The only documentation I found is about installing additional Failover Cluster Instances. I wonder if there's any set back when mixing them

EDIT: The new instance is for application testing purposes. It's not related to those in the cluster.


texts:
I'm converting from using an Excel spreadsheet to an app with a MySQL database in a pool league I manage.

I understand about eliminating/minimizing redundancy to avoid data corruption. So in a spreadsheet where I have 3 columns for Bonus1, Bonus2 & BonusT, where BonusT=Bonus1+Bonus2, in the table I would just have Bonus1 & Bonus2 and if I want to use BonusT i just calculate on the fly.

But suppose the calculated field is a lot more difficult to calculate, involving data from many other records. For example I'd like to keep track of a player's number of games played in the league because I calculate the change in handicap after every match and do it differently depending on how many matches he has played.

I could add a record to the user_meta table (this is WordPress) for his number of games, and increment it when adding a new match. Or, I could do some sort of nested query to get this info every time.

I'm just wondering how complex the subqueries (or other methods I'm new to this) have to get before "cheating" normal form is justified and becomes appropriate?


texts: INNER JOIN Giving time out on large database

texts:
Getting time out on this script

UPDATE
    uk_data AS ud
  INNER JOIN
    uk_pc AS up
        ON ud.cat10 = up.WardCode
SET
    ud.cat8 = up.Latitude,
    ud.cat9 = up.Longitude;

uk_pc Table has 1,755,213 entries and uk_data has 24,510 entries.

Is there any other way to do this where I don't get time out?

I want to update uk_data cat8 and cat9 with uk_pc Latitude and Longitude.

Table1:  uk_pc
    Latitude
    Longitude
    WardCode

Table2:  uk_data
    cat8
    cat9
    cat10

in both tables WardCode and cat10 have same value.

Table Definitions

Table1 uk_data

CREATE TABLE IF NOT EXISTS `uk_data` (
  `slno` int(100) NOT NULL AUTO_INCREMENT,
  `comp_name` varchar(150) DEFAULT NULL,
  `comp_no` varchar(50) DEFAULT NULL,
  `comp_street` varchar(100) DEFAULT NULL,
  `comp_area` varchar(100) DEFAULT NULL,
  `comp_post_code` varchar(50) DEFAULT NULL,
  `comp_phone` varchar(100) DEFAULT NULL,
  `comp_phone1` varchar(100) DEFAULT NULL,
  `cat1` varchar(100) DEFAULT NULL,
  `cat2` varchar(100) DEFAULT NULL,
  `cat3` varchar(100) DEFAULT NULL,
  `cat4` varchar(100) DEFAULT NULL,
  `cat5` varchar(100) DEFAULT NULL,
  `cat6` varchar(100) DEFAULT NULL,
  `cat7` varchar(100) DEFAULT NULL,
  `cat8` varchar(100) DEFAULT NULL,
  `cat9` varchar(100) DEFAULT NULL,
  `cat10` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`slno`),
  UNIQUE KEY `Phone` (`comp_phone`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=31717 ;

Table2(uk_pc)

    DROP TABLE IF EXISTS `uk_pc`;
CREATE TABLE `uk_pc` (
  `slno` int(11) NOT NULL AUTO_INCREMENT,
  `Postcode` varchar(25) DEFAULT NULL,
  `Latitude
Latitude` decimal(9,6) DEFAULT NULL,
  `Longitude` decimal(9,6) DEFAULT NULL,
  `Easting` varchar(25) DEFAULT NULL,
  `Northing` varchar(25) DEFAULT NULL,
  `GridRef` varchar(25) DEFAULT NULL,
  `County` varchar(25) DEFAULT NULL,
  `District` varchar(25) DEFAULT NULL,
  `Ward` varchar(25) DEFAULT NULL,
  `DistrictCode` varchar(25) DEFAULT NULL,
  `WardCode` varchar(15) DEFAULT NULL,
  `Country` varchar(25) DEFAULT NULL,
  `CountyCode` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`slno`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


texts: High availability PostgreSQL 9.3 server on Windows

texts:
I'm trying to set up PostgreSQL 9.3 server in HA mode on 2 Windows servers. Final solution should be deployed to Microsoft azure network, so there are few limitations. Since Microsoft is doing maintenance on their machines without any warning, I find it difficult to setup such system. If you have any idea how to do it, I'll be glad to hear it.

I already tried to setup Windows Cluster with shared data disk (ISCSI), as suggested on several sites. I found out that, due to heavy NFS caching, failover works only if there are no transactions for few minutes. If there are transactions, after several failovers database became corrupted and service failed to start on both machines.

I can always try to setup this on linux machines using pgpool, but I prefer if this could be done on windows because application is written for IIS. I've done this with Microsoft SQL server on 3 machines without much difficulties, but I couldn't find similar solution for PostgreSQL.

Thanks in advance!
Kind regards,
Ivan


texts:
I have setup sp_whoisactive as a report in SS Reporting Services so people less familiar with SSMS can run it and see what is happening when needed. I have it working good and BUT I can't seem to figure out how to show the "sql text" column as a hyperlink in SSRS as it's shown in SSMS. Anyone have any ideas?
Thanks!

fyi, we ended up doing this:


texts: Is it possible to alter normal column to virtual column in Oracle?

texts:
ALTER TABLE TAB_NAME ADD TEST_COL VARCHAR2(255);
ALTER TABLE TAB_NAME MODIFY TEST_COL GENERATED ALWAYS as ('some_exp') VIRTUAL;

Second script gives an error:

Error report -
ORA-54026: Real column cannot have an expression
54026. 0000 -  "Real column cannot have an expression"
*Cause:    Attempted to alter a real column to have an expression.
*Action:   This is not valid, change the DDL.


texts:
We are in the middle of migration, a full backup has been taken and copied to the destination. On Friday, we plan to do a differential backup, but someone has by mistake taken another full backup (did not use copy_only).

We want to prevent this in the future. We can think of DENYing (as follows), but we don't know the specific login. So, how do we make sure no-one can do a full backup?

DENY BACKUP DATABASE TO [LoginName]


texts:
I'm attempting to improve performance of a stored procedure.  When I run the SP, it finishes almost instantly, as if something were cached.  I was told to use the following two lines of SQL before executing the SP in SSMS:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

When I run the SP with the two lines of code above the SP finishes in around 8 seconds. However, is this really giving me the real execution time (as in if I run this from an application)?  How do I know?


texts: MySql : Grant privileges for specific session

texts:
We have the user with all privileges. And while testing/grabbing information, playing with selects, how to prevent accidentally dropping table, delete and etc?

One of the solutions is to create specific user with permissions only to read. But it requires creating it on each DB and let's say you have a hundred of it or you don't have permission to create the user.

Is there equivalent SQL_SAFE_UPDATES, that will cover ALL DDL and modifying data for the specific session/connection?


texts: Oracle 19c Installer Can't Select National Character Set

texts:
I am trying to install a new 19c instance of Oracle database in order to migrate a 12c database there. The 12c database uses a database character set of WE8ISO8859P1 and a national character set of AL16UTF16, both configured on installation.

I have found during the 19c installation, however, that there is no option to set the national character set. How can I be sure that this is configured to the correct AL16UTF16?


texts: Index help for bad Query on fairly big table (2m)

texts:
I am looking for some assistance please. I have a query that is running against a fairly big table (2 million records).
I have been trying to get the indexes working efficiently. There are a few other queries against this table, but this is by far the most frequent one. I am seriously struggling to get it to execute in under 1 second, and often see it running in 3 to 5 seconds using profiler.
It might be as fast as can be, but I would appreciate some input to confirm/deny that please.
Just a note: Dev wont change the query or schema at all. Optimizations can be made in the database only, and no schema changes.
The Table:
CREATE TABLE [dbo].[Notifications](
[ntID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[NotificationID] [int] NOT NULL,
[NotificationType] [nvarchar](50) NOT NULL,
[UserName] [nvarchar](50) NULL,
[CreatedBy] [nvarchar](50) NULL,
[CreatedOn] [datetime] NULL,
[Status] [nvarchar](50) NOT NULL,
[Result] [nvarchar](50) NULL,
[Extension] [nvarchar](50) NULL,
[ShiftRate] [nvarchar](255) NULL,
[ResponseMinutes] [int] NULL,
[ResponseWindow] [datetime] NULL,
[caNotificationID] [int] NULL,
[AwardedBy] [nvarchar](50) NULL,
[AwardedOn] [datetime] NULL,
[CancelledBy] [nvarchar](50) NULL,
[CancelledOn] [datetime] NULL,
[CancelledReasonID] [int] NULL,
[CancelledReasonText] [nvarchar](255) NULL,
[AwardingDate] [datetime] NULL,
[ScheduledLaunchDate] [datetime] NULL,
[CustomMessage] [nvarchar](160) NULL,
[SystemName] [nvarchar](4000) NULL,
[AutoClose] [bit] NOT NULL,
 CONSTRAINT [PK_ESP_Notifications_ntID] PRIMARY KEY CLUSTERED
(
    [ntID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ESP_Notifications] ADD  DEFAULT ((0)) FOR [AutoClose]
GO

Table Data snapshot:

The Query:
Update Notifications
  set Status = 'Awarding' OUTPUT deleted.*
where ntID = (
    select top(1) ntID
    from Notifications
    where NotificationType = 'Shift'
      and (Status = 'Done')
      and ResponseWindow < '2019-02-04 10:40:03'
    order by ntID)

Attempted Indexes :
CREATE INDEX [IX_Notifications_Status_NotificationType_ResponseWindow_ntID]
ON [dbo].[Notifications](
   [Status] ASC,[NotificationType] ASC,[ResponseWindow] DESC,[ntID] DESC
)

CREATE INDEX [IX_Notifications_Status_ScheduledLaunchDate_ntID]
ON [dbo].[Notifications](
    [ScheduledLaunchDate] DESC,[Status] ASC,[ntID] ASC
)

CREATE INDEX [IX_Notifications_NotificationType_caNotificationID_NotificationID_ntID]
ON [dbo].[Notifications](
   [NotificationType] DESC, [caNotificationID] DESC, [NotificationID] DESC, [ntID] DESC
);

NotificationType contains 3 different types, of which 70% are type 'Shift'
Status has 10 types, but the 'In Flight' records are only about 100 to 200, split over 4 Status's
Thank you for your assistance.


texts: How do I create a file from a result set?

texts:
I'm using Oracle 11g (I think)

I'm trying to create a flat file (CSV or TXT) from a result set but am struggling on where to even start. It seems like I have to create a stored proc and use UTL_FILE. After doing some research, I have two questions:

Where does the file get created? According to this question I need to get access to the Oracle user directory, but where is that on a Windows and Linux environment? I have to test on Windows , and the script will eventually be on a Linux environment.
What would be the basic format of a SQL script to create the aforementioned file, and load data into it from a fairly basic SELECT query?


texts: Why is query using Clustered Index when it shouldn't?

texts:
Let us presume I have a table named Category in a SQL Server 2005 database.  Category has category_id (bigint, identity) as its primary key and name (nvarchar(50)).  There is obviously a clustered index on category_id, and I have also added a non-clustered index on name.  If I run the query

SELECT *
FROM Category
WHERE [name] like '%zz%'

and look at the Execution Plan via Management Studio 2005, it says it is using a Clustered Index scan.  The clustered index is on category_id, not on name.

Why is it saying that?


texts: Ola Hallengren Index Maintenance with compressed indexes

texts: If the Ola Hallengren index maintenance solution is installed onto a server which already has compression applied to several of the indexes, will the script respect the current compression level when rebuilding the corresponding index?