texts: SQL Server : Is it possible to have a linked server link to another linked server?

texts:
Hello to all and thanks ahead of time for taking the time to look at my question!

I'm trying to do a work-around to access a 32bit Advantage Database Server 8.1 database with 64bit SQL Server 2008R2.

Setup:

Windows Server SBS2012
SQL Server 2008R2 64bit
SQL Server 2012 Express 32bit
Sybase Advantage Database Server (ADS) 8.1 32bit (can't change)

What I've done so far is to install a 32bit SQL Server Express on the same machine. I have setup a linked server to the Advantage database on that instance and successfully accessed data. So, I know that much is working fine.

Problem is, when I try to setup a linked server on the 64bit 2008R2 server, it errors out with the following...

  OLE DB provider "SQLNCLI10" for linked server "[--MyLinkedServerName--]" returned message "Login timeout expired".
  OLE DB provider "SQLNCLI10" for linked server "[--MyLinkedServerName--]"
  returned message "A network-related or instance-specific error has
  occurred while establishing a connection to SQL Server. Server is not
  found or not accessible. Check if instance name is correct and if SQL
  Server is configured to allow remote connections. For more information see SQL Server Books Online.".
  Msg 65535, Level 16, State 1, Line 0
  SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

Like I said, I can access the data using the 32bit SQL Server Express, so I know that much of it works. I just can't seem to access it from within the 64bit 2008R2. I know the login info is correct.

So, Is it not possible to do?


texts: How to add External data source into MySQL?

texts: I have two database. One is a FileMaker database and the other one is mysql. I want to use this database in mysql. I created ODBC data base connection so that I can sync both database e.g when I make changes in mysql database then FileMaker database should also be updated. Is this thing possible in mysql? If not then in which open source database this thing is supported?


texts:
So what I am trying to do is to limit the access to a stored procedure so that only another stored procedure may call it. Here is an example:

create procedure Database_Deployment_Proc      -- This procedure can be executed,
                                               -- but "view definition" is restricted
    @databaseName nvarchar(200)
as
begin
    exec Internal_DeployTables      @databaseName   -- Internal_ should be inaccessible
                                                    -- if executed outside Database_Deployment_Proc
    exec Internal_DeployConstraints @databaseName
    exec Internal_DeployIndexes     @databaseName
    exec Internal_DeployProcedures  @databaseName
    exec Internal_DeployFunctions   @databaseName
end

My goal is to deny any access to any database component, with exceptions of when it is called from specific stored procedure. Is it possible?

I understand that this approach is very bad for the query plan, but it will ease a lot of my work for deploying same database to different machines, with different users in different domains (unrelated).

Is it better to keep it in stored procedure or embed it in C# CLR assembly?

Thanks in advance!


texts: Postgresql | Database with OID doesn't exist

texts:
So, how it started, DB started throwing errors about "database with OID 1"

2014-02-07 22:54:46.561 GET >ERROR:  database is not accepting commands to avoid wraparound data loss in database with OID 1
2014-02-07 22:54:46.561 GET >HINT:  Stop the postmaster and use a standalone backend to vacuum that database.
        You might also need to commit or roll back old prepared transactions.

OK, let's find DB with OID = 1:

backend> select oid,datname from pg_database where oid=1
         1: oid (typeid = 26, len = 4, typmod = -1, byval = t)
         2: datname     (typeid = 19, len = 64, typmod = -1, byval = f)
        ----
backend>

result = nothing. I've checked all DB's I've got and all of them have unique OID, none with value of "1".

Configuration

Software:

CentOS 6 x86_64 (always fresh updates), postgresql 9.3.2 / RPM's from postgresql.org. fsync=on, autovacuum=on, rest is default (commented).

Block layers:

SAS Disks (write cache off) -> Dell Perc RAID (RAID 10, battery backed write cache) -> LVM LV for pgsql -> ext4 (defaults).
No errors reported on block level by OS or HW RAID consistency check, no power failures (never occured).

DB with OID=1

By default, when running initdb, postgres creates database with template0 and template1. DB OID for template1 = 1, but in my case for some reason - it's not.


texts:
There are two transactions. One transaction I used BEGIN RO for read-only transaction. While the other one I used BEGIN to allow me adding a new column to my table called dummy_table. This is what I've done :

However, after committing (even though I'm getting error transaction aborted), the column is successfully added.

For testing, I tried to UPDATE one of the row instead, the tx2 able to COMMIT successfully.
May I know what is the cause of the problem? Why is adding column causing the transaction to abort? What kind of improvement can I do?


texts: How to create a new table from other tables that are related to each other?

texts:
I need to create a new table with all columns from other 4 tables.

My idea for creating the table would be to use the structure:

CREATE TABLE IF NOT EXISTS table_name AS SELECT ~select from other tables~

So far, so good. But I can't find a proper way to do the joins without the foreign key / primary key columns in the table repeating.

Is there a way to use something as distinct, but for columns, no for values?

I know I could do a simpler way, which would be to select exactly the fields I need (like select column.field1, column2.field2), but the 4 tables I need to use to create the new one, have several fields, would be a long code, I don't know if it would be the best way, I wanted something more optimized.

I was also wondering if using this select table structure as per the example would there be any way to set a primary key on creation?

I just tried typing the primary key (column) command after the end of select in various ways (as it would be if I was creating the table by normally setting the fields) and was unsuccessful. Creating the table with AS SELECT, can I only define primary and foreign keys with analter table after creation?


texts: Error in PostgreSQL/PostGIS Access to offline bands disabled

texts:
I am using postgis functions i.e. ST_Clip, ST_PixelHeight.

But I am getting this error -

  ERROR: rt_raster_load_offline_data: Access to offline bands disabled

Actually, I have loaded my raster to postgis using

  raster2pgsql

In raster2pgsql I have used -R option for saving only the metadata of the raster and path location to the raster in the database (not the pixels).

How to solve this issue ?


texts: mysqlimport vs Loading the SQL Format Dump

texts:
Isn't mysqlimport rumoured to be much faster than loading the SQL format dump?

We have a database with nearly 60 million entries on the Windows platform, running MariaDB 10.1.21.  We created the tab delimited dump of the database, which was subsequently uploaded to a remote Linux system running MySQL 5.6.49.  It took 16 hours for the mysqlimport operation to complete.
Subsequently my colleague generated the SQL format dump, uploaded it to the same system and loaded it on the same system (using mysql).  This operation took about 17.5 hours to complete.

mysqlimport certainly took less time but didn't quite proceed at the blazing speed (relatively speaking) that we were expecting.  Is there any study that has been done comparing the time taken by the mysqlimport operation versus loading the SQL format dump?


texts: Always On FCI vs Always On Availability Groups

texts:
What is the advantage of Always On Failover Clustering vs Always On Availability Groups?

Basic Always On Failover Clustering provides server level protection, (eg: 2 servers with 1 shared disk space;  if server fails, it can utilize another server on the shared disk space).

Always On Availability Groups provides both disk storage DR and server-level HA Protection. It provides 2 servers with 2 shared space.

So what benefit functionally does Always On Failover Clustering have over Availability Groups?  Looking at the chart comparison I see none. Seems Availability Groups is better.

We utilize SQL Server 2016 Enterprise.
Thanks.

Reviewed documentation:

Failover Clustering and Always On Availability Groups
SQL AlwaysOn


texts: Unable to add new instance on MySQL InnoDB cluster

texts:
I'm setting up new MySQL InnoDB cluster 8.0.16 consisting of three instances:

srv-mysql-01
srv-mysql-02
srv-mysql-03

I run JS-script in mysql shell for build cluster:

mysqlsh --file=/tmp/MakeCluster.js

Script:

var dbPass = "Somepassword"
var clusterName = "cluster"

try {
  print('Setting up InnoDB cluster...\n');
  shell.connect('admin@srv-mysql-01:3306', dbPass)
  var cluster = dba.createCluster(clusterName);
  print('Adding instances to the cluster.');
  cluster.addInstance({user: "admin", host: "srv-mysql-02", password: dbPass})
  print('.');
  cluster.addInstance({user: "admin", host: "srv-mysql-03", password: dbPass})
  print('.\nInstances successfully added to the cluster.');
  print('\nInnoDB cluster deployed successfully.\n');
} catch(e) {
  print('\nThe InnoDB cluster could not be created.\n\nError: ' + e.message + '\n');
}

Cluster has been created successfully but while adding 2-th instance to cluster it give below error:

A new InnoDB cluster will be created on instance 'admin@srv-mysql-01:3306'.

Validating instance at srv-mysql-01:3306...

This instance reports its own address as 192.168.100.201

Instance configuration is suitable.
Creating InnoDB cluster 'cluster' on 'admin@srv-mysql-01:3306'...

Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

Adding instances to the cluster.A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster ...

Validating instance at srv-mysql-02:3306...

This instance reports its own address as 192.168.100.202

Instance configuration is suitable.

The InnoDB cluster could not be created.

Error: Cluster.addInstance: WARNING: Not running locally on the server and can not access its error log.
ERROR:
Group Replication join failed.
ERROR: Error joining instance to cluster: 'srv-mysql-02:3306' - Query failed. MySQL Error (3092): ClassicSession.query: The server is not configured properly to be an active member of the group. Please see more details on error log.. Query: START group_replication: MySQL Error (3092): ClassicSession.query: The server is not configured properly to be an active member of the group. Please see more details on error log.

I'd like to fix this error.

I checked all instances on possible problems using shell-command dba.checkInstanceConfiguration() and didn't look errors.

But there are some errors in MySQL log on 2-th instance:

2019-07-20T23:24:43.085920Z 0 [ERROR] [MY-011526] [Repl] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 77aabf2e-aa76-11e9-9a4d-525400f0e95b:1-2 > Group transactions: 114adc66-ab3f-11e9-a109-525400f0e95b:1-7,

....

2019-07-20T23:24:43.086303Z 0 [ERROR] [MY-011522] [Repl] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'

my.conf

[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

bind-address=192.168.100.202
port=3306

# Replication part
server_id=202
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay-log=srv-mysql-02-relay-bin
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

default_authentication_plugin=mysql_native_password

# Group replication part
transaction_write_set_extraction=XXHASH64
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="192.168.100.202:33061"
loose-group_replication_bootstrap_group=OFF
report_port=3306
report_host=192.168.100.202

[mysql]
default-character-set=utf8

Any help would be appreciated.


texts: How to import CSV file in SQL server 2008?

texts:
I am trying to import a CSV file in SQL Server 2008. BULK INSERT is a way to go but it is applicable for CSV from SQL Server 2014 onwards.

What would be an alternative way to achieve this goal?

Any thoughts/ideas much appreciated.


texts: Installed Percona-Toolkit, but no sign of xtrabackup

texts:
I've installed Percona-Toolkit on my Debian (Wheezy) test Server, using

dpkg -i percona-toolkit.deb
apt-get install --fix-missing -f
dpkg -i percona-toolkit.deb

However, there is no sign of Innobackupex in the /usr/bin folder. Trying to run it simply gives me Command Not Found

I can see all the pt-xxx applications in there suggesting the installation worked ok.

I then tried

apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

as per the documentation, but that resulted in:

. . .
gpgkeys: key 1C4CBDCDCD2EFD2A not found on keyserver
gpg: no valid OpenPGP data found.
gpg: Total number processed: 0

Followed by

apt-get install xtrabackup

But that gave me

Reading package lists... Done
Building dependency tree
Reading state information... Done
E: Unable to locate package xtrabackup

How can I get xtrabackup (Innobackupex) installed as well?


texts:
For a certain project, it seems like the command use <database>; takes an extraordinarily long time (often a minute or more). (This seems to be the case across a couple of different instances on different versions of MySQL (5.3 and 5.5, I think)). I understand that this is caused by the Tab complete functionality, and I am aware that I could just use a -A flag, but I am interested in the cause of this delay.

My first guess was that this has to do with the speed of INFORMATION_SCHEMA, and that the sheer number of columns caused a delay, but at 4.7k columns (580 tables at ~8 columns a table), that seems a bit of a stretch. There are 3.6k indexes, but that does not strike me as a likely cause of the issue.


texts: SQLite - Counting referenced rows in one table using a different, related self-referencing table

texts:
media_subscriptions
+----+--------------------------+
| id | target_metadata_item_id  |
+----+--------------------------+
| 30 | 19039                    |
| 31 | NULL                     |
| 32 | NULL                     |
| 33 | NULL                     |
| 34 | NULL                     |
| 35 | NULL                     |
| 36 | NULL                     |
| 37 | 18976                    |
+----+--------------------------+

metadata_items
+-------+-----------------+-----------+
| id    | guid            | parent_id |
+-------+-----------------+-----------+
| 18976 | id://265        | NULL      |
| 18977 | id://265/1      | 18976     |
| 19039 | id://841        | NULL      |
| 19040 | id://841/1      | 19039     |
| 19041 | id://841/1/1    | 19040     |
| 19224 | id://841/1/2    | 19040     |
| 19966 | id://265/1/1    | 18977     |
| 19967 | id://290        | NULL      |
| 19968 | id://376        | NULL      |
+-------+-----------------+-----------+

I have the above two tables with data. The parent_id column defines how each row in metadata_items relates to any other row, which is also evidential by each row's guid. The parent_id can be nested by a root ID having a child, and that child having a child (if that makes sense). The target_metadata_item_id column from media_subscriptions relates to the id column of the metadata_items table. I want to get a COUNT(*) of any records from the metadata_items table that have an associated record in media_subscriptions based on a search of guid. Ultimately, if there is a matching record, the count should only ever be 1, and 0 if not, but I'm using COUNT(*) just in case there's ever a duplicate record in the media_subscriptions table, and then I'm just checking elsewhere if it returns > 0.

Some examples of what I'm trying to accomplish:

guid='id://841' would return 1 because its root parent id (19039) is referenced by id 30 in media_subscriptions.
guid='id://841/1/1' would return 1 because its root parent id (19039) is referenced by id 30 in media_subscriptions.
guid='id://290' would return 0 because its root parent id (19967) is NOT referenced by ANY column in media_subscriptions.

Here's what I've got right now, which obviously isn't working as I'd intend (take it easy on me, I'm a noob with database administration, and am just starting to learn).

SELECT COUNT(*) FROM `metadata_items` AS `t1`
 LEFT JOIN `metadata_items` AS `t2` ON t1.id = t2.parent_id
 INNER JOIN `media_subscriptions` AS `subscriptions` ON t1.id = subscriptions.target_metadata_item_id
 WHERE t1.guid='id://841'

This correctly returns 1, but if I switch out the last line with WHERE t1.guid='id://265/1/1' it returns 0 where I'd like it to return 1. Obviously I'm doing this very incorrectly, but I'm really not sure how to go about getting where I need to be.


texts: DROP TRIGGER locks up database with Waiting for table metadata lock

texts:
I've tried to read MySQL's documentation on metadata locking to figure out what's going on here, but our scenario just doesn't seem to fit.

We're using MySQL 5.5.35. All tables are InnoDB

First, our app was built 9 years ago and was initially really heavy on the stored procedure side (as in everything was done in stored procedures). All stored procedures have their own script that first drops if it exists, then creates the procedure. When we run our migrations, it will source every one of these scripts, effectively dropping and recreating every procedure we have. Included in this process are 3 triggers.

Whenever we have a specific long-running report executing (select statement joining several tables) at the same time we do a migration, we find that everything locks up on the "drop trigger if exists" statement with a "Waiting for table metadata lock" State. Every subsequent query after that gets the same "Waiting for table metadata lock" state, which instantly fills up our max_connections and our server dies. If I kill the report, it's able to finish processing the triggers and all the previously waiting queries and everything returns to normal.

According to the docs, it would make sense that the drop trigger function would have to wait due to the query because it's a DDL statement and that's precisely the point of the metadata lock. What I don't understand is why all of the subsequent DML statements end up waiting for the lock as well.

Can someone explain to me why this is happening?


texts:
We use SQL Server 2008. When I am watching in SQL Server Profiler I found some queries that take for example 20 seconds. The same query (in a stored procedure) when executed in SQL Server Management Studio takes 2 seconds. SQL Server is under load when I catch those queries with
memory and CPU at the 20 percent.

What I can do?


texts: MySQL Table Crashed Needs Repair, but Doesn't seem to Repair?

texts:
I am getting the error:

Table 'feed_data' is marked as crashed and should be repaired

So I run REPAIR TABLE feed_data (repeatedly) and get the following result:

+----------------+--------+----------+---------------------------------------------------------------+
| Table          | Op     | Msg_type | Msg_text                                                      |
+----------------+--------+----------+---------------------------------------------------------------+
| mydb.feed_data | repair | Error    | Table 'feed_data' is marked as crashed and should be repaired |
| mydb.feed_data | repair | status   | Table is already up to date                                   |
+----------------+--------+----------+---------------------------------------------------------------+
2 rows in set (0.06 sec)

The table still shows as crashed.
Why won't the table repair when I run the repair table command? What else can I do to repair the crashed table?


texts:
I have a Windows Server 2012R2 with SQL Server Express 2014 install that supports a video app.  I am trying to create a backup solution for it using the Task Scheduler to do full and incremental backups.  I have the task scheduler executing a batch file that uses SQLCMD to run a stored procedure that does the backup.  This is the batch file:

@Echo Off

"C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SqlCmd.exe" -S SERVER\INSTANCE -i "C:\Scripts\FullBackup.sql"

The SQL that it runs (in FullBackup.sql) is as follows:

exec spBackupDatabases '\\BackupServer\Backup\Database\Server\', 'F';

The spbackupDatabases stored procedure is created in Master.  When I run this stored procedure with SSMS logged in as myself (a local administrator) it works fine.  When I try and run the same thing, still logged in as myself, from the batch file I get the following error:

C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn>\scripts\FullBackup.cmd
Msg 229, Level 14, State 5, Server SERVER\INSTANCE, Procedure spBackupDatabases, Line 1
The EXECUTE permission was denied on the object 'spBackupDatabases', database 'master', schema 'dbo'.

There is nothing in the SQL Server Logs (even with failed and successful logins turned on) so I'm not even sure what user is being used.  What might I be doing wrong?


texts:
I have been stuck on this for a long time, I tried to follow the instructions located here: https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html. When I run the commands I get this as my output:
2021-01-01T09:15:04.445377Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.22) starting as process 19920
2021-01-01T09:15:04.487135Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-01-01T09:15:04.901676Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-01-01T09:15:05.135403Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060
2021-01-01T09:15:05.240496Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-01-01T09:15:05.242979Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-01-01T09:15:05.325105Z 0 [System] [MY-010931] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: ready for connections. Version: '8.0.22'  socket: ''  port: 3306  MySQL Community Server - GPL.
2021-01-01T09:15:45.594^C297Z 0 [System] [MY
-013105] [Server] C:C:\Program Files\MySQL\MySQL Server 8.0\bin>\Pro
gram Files\MySQL\MC:\Program Files\MySQL\MySQL Server 8.0\bin>ySQL Server 8.0\bin\mysqld.exe: Normal shutdown.
2021-01-01T09:15:46.281364Z 0 [System] [MY-010910] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Shutdown complete (mysqld 8.0.22)  MySQL Community Server - GPL.
mysqld --init-file=C:\Users\user\text.txt --console
2021-01-01T09:15:51.297226Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.22) starting as process 21652
2021-01-01T09:15:51.320019Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-01-01T09:15:51.784233Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-01-01T09:15:52.015722Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060
2021-01-01T09:15:52.118175Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-01-01T09:15:52.126894Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-01-01T09:15:52.205977Z 0 [System] [MY-010931] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: ready for connections. Version: '8.0.22'  socket: ''  port: 3306  MySQL Community Server - GPL.

I am not sure what to do from here because I do not think it is on the tutorial. Any help solving this would be very appreciated. Thank you


texts:
From this SQL schemas, I want to find all users related by contest.id

I try this req :
SELECT u.id, u.firstname, u.lastname, u.avatar FROM contests c
LEFT JOIN trials AS t ON (t.contest_id = c.id)
LEFT JOIN trials_has_users AS tu ON (tu.trial_id = t.id)
LEFT JOIN users AS u ON (u.id = tu.trial_id)
WHERE c.id = '7a101d23-a1d5-457c-821e-14321ae3bb64'
GROUP BY u.id;

but it does not work, u.id, u.firstname, u.lastname, u.avatar is NULL, and I only have one result


texts: IP from a different subnet is registered even after node and resource were removed

texts:
We run two SQL server at primary site in AlwaysOn High Availability Group on subnet 10.10.0.0/24
A third SQL server is running on secondary site in a different subnet 10.10.1.0/24
We have had third server as part of a cluster for some time until faced issue from this question. It was then decided to remove the third server from the cluster and all of the resources that it presented.

Right off the bat DNS registration for 10.10.1.0/24 subnet had to be removed, but it is now being registered every 24 hours. Whenever it is registered, all clients start to use and fail to connect. This occurred even when all three servers were running and syncing properly, and some clients would just drop live connection just to connect to ip on a different subnet which would fail.

Where can I find this 10.10.1.0/24 subet IP address that is still being registered with DNS server? SQL Management studio group listener lists only one IP in correct subnet. Fail-over cluster lists single network IP address too. The third server has since been powered off, but it is irrelevant

Get-ClusterGroup SQLHA | Get-ClusterResource | Where-Object {$_
.ResourceType -match "Network Name*"} | Update-ClusterNetworkNameResource -Verbose

After running this command DNS server is updated, but yet again two records are created.


texts:
I'm in need of some help with the database design of order table of a shopping cart for online food ordering system. I have a design below. Tables about which I'm Concerned  are order_details and order. Basically what is going to happen is user selects foods from restaurant menus and will place order, User details are stored in the user table (which is not shown in the image).

This is my first attempt, I was never good at Database. So please take it easy if you fill that the question is way too low for this site.

And my questions are:

Are the tables good enough?
Do I need to add any more fields?
And one more question I need to store multiple addresses of users(user can have multiple shipping address) how can I handle this?

http://i.stack.imgur.com/HCYBi.jpg

Note The item_price table in the image is not i use. Please ignore that table while answering. Thanks


texts: SQL Server: Backup failed because there is a mismatch in file metadata

texts:
We have a problem with one of our databases that has FileStream enabled. When we try to backup, we get the error

Backup failed because there is a mismatch in file metadata for file 65537

When we do a DBCC, no errors get reported, DBCC CHECKDB ('MyDB', repair_allow_data_loss) also does not return any issues. Users are not experiencing any issues when accessing the database, it is only the back-up that is a problem.
We have tried copying data to other tables, etc. but nothing has helped yet. We have even tried 3rd party repair tools but nothing has delivered results.
This is a very big database and creating a new DB and migrating the data is not really an option (will probably take many days).
Does anybody have any ideas on how to fix this issue or suggestions on what we can try?
I am now investigating undoing the FileStream...
Thanks!


texts:
I have a reporting database (in SQL Server) that takes nightly snapshots of the production data (Oracle) for users to query against for the next day, but now the requirement is getting to be that they want near realtime data (about every 15-30 mins). What is the best way to do this in production without downtime or any partial data getting returned?

I've looked into realtime data mirroring using triggers, but because of licensing we can't alter anything in the Oracle database.

I've seen some information on partition switching, where I do a full refresh into a set of alternate tables then switch the partitions. Is it good practice to be doing that on 20 tables every 15 minutes? A lot of these tables have one-to-many relationships, but I figure if I replace the "many" tables before the "one" tables, reports won't return partial data if they run during this process.

I also want to avoid locking the whole database, because I want the system to be able to scale to many concurrent users, and it seems like if I do that, even if it takes just a second, it might take some time to acquire the lock.

Any strategies or insights are appreciated! Thanks!


texts:
Here is the situation:

DB on Server A had the transaction log shipping configured and uses Server B as secondary. It has been working well for a long time.

However, we recently detected that one of the trn files got corrupted and Server B cannot restore it.

DB on Server A is still healthy, so I would like to create a new full backup into Server B and restart the transaction log shipping.

Since all the timings and other settings were already tweaked and configured, I am trying to avoid starting from scratch.

What step would i need to follow in order to reinitialize the log shipping? (This is a SQL Servert 2008 R2)


texts:
I'm trying to create JSON column in 10.3.16-MariaDB but every time I save the configuration in PHPMyAdmin, the column saves as longtext datatype.

No errors shown :|

Where could be the problem?

System specs: Apache/2.4.39 (Win64) OpenSSL/1.1.1c PHP/7.3.7 and PHPMyAdmin 4.9.0.1 versions


texts: How to programmatically find out where LocalDb is installed

texts:
I'm writing a plugin for Cake Build tool where I need to start a LocalDB instance. But this code will be executed on different machines and LocalDB can be installed in different folders depending on the version of SQL Server installed in the build machine.

I know at least 3 paths possible:

c:\Program Files\Microsoft SQL Server\130\Tools\Binn\SqlLocalDB.exe
C:\Program Files\Microsoft SQL Server\120\Tools\Binn\SqlLocalDB.exe
C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SqlLocalDB.exe

Though I'm not absolutely sure about the last path. And I can't install SQL Server 2016 on my machine, so can't confirm if

c:\Program Files\Microsoft SQL Server\140\Tools\Binn\SqlLocalDB.exe

would the correct guess.

I can iterate through a list of options and pick the one that works.

Are there any other possible folder where SqlLocalDb.exe can be installed?
Or is there a registry value somewhere that I can check?


texts: Unable to shrink transaction log file in SQL Server

texts:
I moved to a new company recently wherein they have 2 databases in a SQL server of size 20-25 GB. I'm unable to shrink the log file of the databases.
Transaction log backup of the databases are set to run every 30 mins from 6 AM-7PM.
They are of size 10 and 2 Gb respectively.
I tried to shrink it around 8-9 PM last night but I was not able to do.

The log_reuse_wait column stated it's waiting on a log_backup.
I tried taking couple of log backups which didn't help.
I ran DBCC OPENTRAN but there are no open transactions.

Also, this morning even after the Tlog backups started at 6:00 AM till now(10:30) it still shows LOG_BACKUP in log_reuse_wait column.
And no OPEN TRAN as well.

When I researched on this I found this article from Paul Randal:
http://www.sqlskills.com/blogs/paul/why-is-log_reuse_wait_desc-saying-log_backup-after-doing-a-log-backup/

It states that if the DB is small and all the content of tlog file is in same VLF it couldn't truncate.
I have some 300-400 VLFs in the Databases.
So that doesn't make sense here as well.

One thing I noticed that every morning at 6 AM the tlog is a little bigger than the other ones.
So I'm thinking to check with the lead DBA to run tlog round the clock.
Other than that, I would like to see why is this happening and Am i missing anything.

I tried to shrink with the below command:

Use DB1
go
DBCC SHRINKFILE(2,10)
GO

Note: It's SQL 2012 instance running 0n 2012 compatibility


texts: Is using multiple unique constrains on a single table considered bad design?

texts:
I was looking at PostgreSQL's INSERT INTO .. ON CONFLICT (..) DO UPDATE .. syntax and realized, you cannot do multiple unique constraint checks with it. I mean, you either refer to a composite unique index by the column names ON CONFLICT (Name, Symbol) (if the unique index is defined for these two columns), or you use the primary key. If you define two separate unique indexes for the columns, you can only check for one.

CREATE TABLE student
    (Id int primary key, Name varchar(50), Symbol varchar(50),
      CONSTRAINT col1_unique UNIQUE (Name),
      CONSTRAINT col2_unique UNIQUE (Symbol)
    );

INSERT INTO student
    (Id, Name, Symbol)
VALUES
    (1, 'John', 'J'),
    (2, 'David', 'D'),
    (3, 'Will', 'W');

INSERT INTO student
    (Id, Name, Symbol)
VALUES
    (4, 'Jeremy', 'J')
   on conflict(Name) DO UPDATE
   set Name = 'Jeremy';

Could throw an error, saying J is a duplicate. However, this example is simply a bad design, because the Symbol should be in another table and be connected to the student table via a one to many relationship. Which is why I am wondering, maybe PostgreSQL's on conflict was designed this way, because you can ALWAYS restructure the tables in a way, where there is only a single unique index. Is it true or there is an another reason?

Example fiddle: http://www.sqlfiddle.com/#!17/9c0ce


texts: Include logical key on each table vs. join

texts:
I'm designing a database that is used to track an inventory item through its lifecycle. I'm currently relating my tables using the previous lifecycle event's ID (for example, a shipment contains the inventory item's ID, the receiving department table stores a shipment ID, the sales table stores the receiving receipt ID, and so on). I've designed the tables in this way to enforce the fact that each of the lifecycle steps must happen in a particular sequence.

If I wanted to check information about a particular inventory item, I'd need to go through and join all the tables that represent that item's lifecycle. Each of my inventory items has a unique serial number, so I'm thinking about including that SN on specific lifecycle event tables in order to avoid having to perform all the joins.

I was wondering, is there a rule or guideline as to when you should use key duplication vs performing a bunch of joins? If I were to go with the joining method, would this be an appropriate place to use a bunch of different views?


texts: does xml element exist, regardless of value?

texts:
EDIT: I demonstrated my ignorance by confusing attribute with element, I updated my question. Sorry. To clarify, I'm looking for the existence of the element code in:

<conditionmessage:code>MYCODE</conditionmessage:code>

I have a bunch of schemas where there is a table that contains an XML column. There are a handful XML indexes on each column. I'm pretty sure that the XML-schemas differs between the SQL-schemas, nevertheless, the same XML-indexes exists in each schema. I suspect copy/paste and I can't seem to find any documentation for the XML-schemas. So I thought of a more pragmatic approach namely to loop through the XML columns and check whether each element exists or not. The tables have been around for a while, so every possible element should be there by now.

Now to the question, what's the best way to check for the existence of an element, regardless of value? Unfortunately, my brain is incompatible with XPATH/XQUERY, and I have to struggle every time to just convince my self that the query does what it is supposed to do. My current approach seems to work, but I'm curious if there is a better way. The cardinality of the smallest table is ~100.000 rows and the largest ~100.000.000 rows:

import ibm_db
import ibm_db_dbi
...
conn = ibm_db_dbi.connect("...")
c1 = conn.cursor()

for schema in [ "S1", "S2", ... ]:
    for attribute in [ "a1", "s2", ...]:
        sql = f"""
            select * from {schema}.ATOM_ENTRY
            WHERE XMLEXISTS('$d//*:{attribute}[fn:matches(text(), $p, "i")]'
                PASSING ENTRY_XML AS "d", CAST('\S*' AS VARCHAR(128)) as "p")
            fetch first 1 rows only
            optimize for 1 rows"""
        c1.execute(sql)
        exists = False
        for row in c1.fetchall():
            exists = True
        print(f"{schema}.ATOM_ENTRY -> {attribute} : {exists}")


texts: Relating ExecutionInstanceGUID to the SSISDB

texts:
The 2012 release of SQL Server Integration Services, SSIS, has delivered an SSISDB catalog which tracks the operations of packages (among other things). The default package execution for solutions using the Project Deployment model will have logging to the SSISDB turned on.

When a package executes, the System::ExecutionInstanceGUID is populated with a value that, if one were using explicit logging (to sys.sysdtslog90/sys.sysssislog) would record all the events for a specific package execution.

What I'd like to know, is how do I tie an ExecutionInstanceGUID to anything in the SSISDB catalog. Alternatively, is an SSIS package executing in the SSISDB privy to the value of its catalog.executions.execution_id

Ultimately, I am trying to use the existing, custom audit table and link it back to the detailed history in the SSISDB catalog but can't seem to find the link.


texts: In PostGIS, how do I swap the lat/log of invalid points, like MySQL ST_SwapXY?

texts:
MySQL has a function ST_SwapXY which swaps the x and y values (long, and lat). A function, like ST_SetSRID, that's useful if you import data incorrectly.

  Accepts an argument in internal geometry format, swaps the X and Y values of each coordinate pair within the geometry, and returns the result.

How do I do this in PostGIS?


texts: Does mysqlnd expose PDO connection data to process/log files?

texts:
So I was trying to figure out how to write an automated backup script for php on a shared webhost to execute using mysqldump, with no root access to core php.ini or mysql config on the remote database server, no decent access above "true" web root (just .htaccess mods) and no automated backup protection other than command line.

Apparently it's possible to run mysqldump with passwords hidden in a defaults file, but only in a specifically named my.cnf with an odd --defaults-group-suffix syntax which only works for mysql (not mysqlnd), and neither can be used with mysqlnd/PDO, which (in true Kafkaesque fashion) "no longer allows you" to set a default config file at the mysql level.

Instead it has to parse_ini_file the same .cnf file, and does not read any default option files. mysqlnd apparently does not take any config files as argument.

Everything I read tells me not to use the "default" connection args as command arguments.

If I parse_ini_file the mysqldump config file my.cnf or HOST.cnf, does PDO simply pipe that to mysqlnd arguments in plaintext as a (linux process) command? (as arguments)
Can I have a single config file with two database connections if I list each separately using mysqldump's default prefix option and then parse_ini_file in PHP to pick the right one? *
Is there any point to hiding the PDO connect args in a config file if they are piped to mysqlnd in plain text? (Given how strenuously it is argued against doing the same in mysqldump shell script?)

*Edited: nope, can't even do that, neither mysqlnd nor mysqldump uses --defaults-group-suffix. Do I have to use a separate .cnf file per-host to share with both mysqlnd (using PHP's parse_ini_file) and mysqldump?


texts: How does SQL Server reuse reserved pages after deleting a table

texts:
I just want to know:

How SQL Server reuses reserved space after dropping a 1GB table? (call the table T1)
How SQL Server reuses reserved space after truncating a table? (call this table T2)

Both are clustered tables (not heaps).


texts: Disallowing MariaDB query cache except from webserver (JDBC)

texts:
I have an application server (Play Framework), webserver (Caddy), and database server (MariaDB with InnoDB) that are running on a single machine for now.

The database is used in two ways:

client Python code that makes highly specific SELECTs (over an SSH tunnel).
the web/application server, which performs occasional INSERTs and UPDATEs, but mostly SELECTs from large tables each time a page loads.

Is there a simple way to enable (and force) query caching for the webserver but not for the client code? My thought was to set query_cache_type=DEMAND and use SQL_CACHE on the webserver, but it's not ideal. Perhaps I can force JDBC to use SQL_CACHE somehow?

Clarification:
The unfortunate technical issue with adding SQL_CACHE to every query is that the web application queries using Slick and a JDBC backend. The integration with Slick is tight so I can't rewrite the application layer, and I don't know how to force JDBC to append SQL_CACHE.


texts: Restore rman backup on another server with a higher Oracle release

texts:
I would like to know if it is possible to restore a full backup made with rman of an Oracle9i Enterprise Edition Release 9.2.0.6.0 on another server with Oracle9i Enterprise Edition Release 9.2.0.8.0, I have the doubt precisely in the change of versions of 9.2.0.6.0 to 9.2.0.8.0.
Thank you very much


texts: Avoiding a possible table reference error

texts:
I'm working on a project that's due in 2 weeks and have hit a possibly serious roadblock. The app that I'm working on is some tuition reimbursement management system, whose use cases are dependent on the roles of the employee (user) trying to access it. It's a solo project, in which I was given a two-page document of some high-level requirements, involving the employee, the direct supervisor, the department head, and the benefits coordinator.

After a week of trying to plan it, and work on it, I came up with this: the employees table has a foreign key to itself called manager_id (an employee is a base user, then, iff there are no records such that manager_id equals that employee's employee_id), and a department_roles_id that points to the department_roles table that has a department and a department_role_name. Here's a screenshot of my ERD for this

This approach, however, seems to have a problem. What happens when someone inserts an employee record into employees, with the same department_role_id as another record, but they have two different manager_ids (for example, if one of them was a manager of that department but the other one wasn't)? How to avert this?

NOTE: if it helps any, I'm on Oracle 11g.


texts: Oracle: How can I create a where clause programmatically?

texts:
Suppose I have a table like:
TBL_CONDITIONS
| ID | WHERE_CLAUSE                          |
|----|---------------------------------------|
| 1  | fruit = 'apple'                       |
| 2  | delicious = 'T'                       |
| 3  | fruit = 'apple' AND gluten_free = 'T' |
| 4  | delicious = 'F'                       |

I'd like to be able to select the where clause on this table based on user input.  Say the user selected "3". The stored procedure is written thusly:
BEGIN
    SELECT * FROM SAMPLE WHERE (SELECT WHERE_CLAUSE
                  FROM TBL_CONDITIONS WHERE ID = 3);
END;

Unfortunately, this doesn't work.  Is there a way to run this type of query?  Or another way to accomplish the same task?


texts:
I've initially posted this question on serverfault.com, but because lack of answers decided to post it here...

I'm trying to use bat file/CLI to run SQL script against certain Oracle database with following syntax:

sqlplus -L user_name/database_name@Password @d:\my_bat_file\sql_script.sql

If I run this following error is returned:

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

SP2-0751: Unable to connect to Oracle. Exitig SQL*Plus

At the same time tnsping database_name works fine & corresponding connection configuration strings are present in tnsnames.ora

Moreover if I type commands manually in a following way:

sqlplus /nolog
connect user_name@databasename
Passowrd

I can connect to the database too. Any idea why I'm getting error when trying to use command I mentioned in beginning & how to fix it?

P.S. I'm doing this from Windows Server 2003 SP2 box with Oracle client v10 installed.


texts:
The strangest thing is happening on my SQL Server Management Studio.
When I right-click any table, I get a "VIEW SERVER STATE permission was denied on object 'server', database 'master'. (.Net SqlClient Data Provider)" error, but if I just click OK, then the context menu appears and I keep working just the same.
Again, if I right-click in Columns, Keys, whatever, inside the table, everything works fine.
Any ideas??


texts: Despite STATISTICS is updated, Estimated and Actual Row count is not same

texts:
I am executing a SELECT statement to retrieve 2 columns with filter on datetime column from a table having 27451373 rows.
declare @LastProcessedDate datetime='08-15-2016'

select Bill_Code,CLIENT_ID_6,u2_id
from [ODS_HRP].dbo.EMPLOYEE_BILL_VCHR_CODE_ALLOCATIONS
where time_stamp >= @LastProcessedDate

In execution plan I found big gap between Estimated and Actual Number of rows.
Hence I update statistics 2 time in a week, I updated all statistics of the table again and executed same SELECT statement.
But, it is still same.
Here is the execution plan.

And last stats update date.

Thanks


texts: Check programmatically if SQL Server needs restarting

texts:
I have to check programmatically if the SQL Server service needs restarting after series of changes in configuration.

Is there any indicator (e.g. in the system registry) that allows to obtain this information?


texts: How can I create such a query in MS access so that only customers with more than 3 bookings show up?

texts:
I have two different tables, the "customers" tables, with "customer ID" as the primary field and a few more fields such as Name, Surname etc. (All in text format).

The second table is "Booking details", It has the primary key as "Booking ID" and then customer name, surname etc, (Again, all in text)

So, basically, in the Booking details table, each booking is given a unique Booking ID, and one customer can do several bookings, i want to create a query that sorts out only customer with more than 3 bookings ?


texts: Combine results of Multi-Server Query from Registered Servers

texts:
Is there a way to insert result of a query executed in Registered Servers into a view / sp / table?

Currently I have locally setup a Registered Server on multiple servers to query the master database for each.

I basically want to insert the result in a single container then read it by a job.

It this possible?


texts: Removing sp_execute_external_script Start-Up Lag

texts:
On a freshly-restarted instance of SQL Server, the first time Python is executed using sp_execute_external_script, there's a lag before results are returned. After the initial Python script execution, future script executions executed shortly thereafter return much faster. However, if several minutes elapse before another script execution is attempted, the lag reappears.

I'm guessing that the lag is due to some aspect of the external script environment being started. The delay can't be due to query execution time as it is present even when the script invocation does not involve data access.

EXEC sp_execute_external_script
  @language = N'Python',
  @script = N'print("hello")';

If I'm correct that the lag has to do with script environment start up, is there an option to configure that environment to be pre-started when the SQL Server instance starts so that the lag is removed?


texts: Grant access on Secondary but not on primary in SQL Server logshipping

texts:
I have a strange situation wherein users need permission on log shipped DB  on secondary server but not on primary. The problem is, the login has access on many other DBs on primary server apart from log shipped DB and that needs to be maintained. My environment is SQL Server 2012 EE.

Is it possible to achieve this?

As a workaround what I have tried is granting temporary access on my primary DB just before the backup log-shipping starts and once the backup completes I have added a step to remove it. But the issue with this workaround is, the users can still access the primary log shipped DB when I try to grant them access just before the log backup. Any help would greatly be appreciated.


texts: Can't mysql autocast encoding in query data?

texts:
i am getting error "Illegal mix of collations"

i know either i need to change db tables to utf8 or convert user input to latin1 or ansii

so i want to know, can mysql do this automatically so i won't need to change table encoding and also no need to modify my exiting queries with COLLATE expression.


texts: How do I intentionally fragment a SQL Server Index?

texts:
I would like to create bad indexes conditions intentionally on a SQL Server 2017 test database I have, just to better understand these maintenance scripts? SQL Server Index and Statistics Maintenance

Is there a fast/automatic way to compromise index integrity or increase index fragmentation? Do you know any useful resource I can look at to achieve this?


texts: Prompt user for input using substitution variable

texts:
DECLARE
 localvar char;
BEGIN
 localvar := &ENUMBER;
 IF localvar ='A' then
  dbms_output.put_line('Please proceed to Level1');
 ELSIF localvar='B' or localvar='C' then
  dbms_output.put_line('Please proceed to Level2');
 ELSIF localvar='D' then
  dbms_output.put_line('Please proceed to Level3');
 ELSE
  dbms_output.put_line('Invalid Entry');
 END IF;
END;

I am trying to prompt the user for input using a substitution variable in Oracle, but I am having a problem with this. Any help is appreciated.


texts:
I am not being able to drop my tables using :

drop table <table_name>;

command. It says "the table is referencing primary keys of some other tables as foreign keys."
, and when I tried to drop foreign key constraints using :

alter table <table_name> drop constraint <constraint_name>;

statement. It says: "cannot drop constraint",  and when I tried to drop column itself on which foreign key constraint was applied, using:

alter table <table_name> drop <column_name>;

statement. It says: "cannot drop column owned by SYS table.". Somebody told me, SYS doesn't has the privileges to drop columns especially when foreign keys constraint are present, and he suggested me to make new User account apart from default SYS and customise all the privileges. Making new user is very lengthy process and I read many articles of making a new user already, but it doesn't help , coz, I've done the installation by choosing the option of "install database software only" and then later created and configured Database using DBCA(Database configuration Assistant ). I might have skipped certain steps or didn't look it properly while using DBCA and just kept the default settings.

Creating a new user requires you to use the CREATE USER privilege and the syntax given in the oracle documentation to create user is :

CREATE USER sidney
IDENTIFIED BY out_standing1
DEFAULT TABLESPACE example
QUOTA 10M ON example
TEMPORARY TABLESPACE temp
QUOTA 5M ON system
PROFILE app_user
PASSWORD EXPIRE;

This is the example I copied from the oracle documentation. Now the problem I am facing is with the PROFILE keyword in the above statement. I don't know what value to fill for PROFILE keyword and moreover the documentation suggests :

"Oracle recommends that you use the Database Resource Manager rather SQL profiles to establish database resource limits. The Database Resource Manager offers a more flexible means of managing and tracking resource use."

Now, when I started looking for Database Resource Manager, I came accross various steps such as:

To administer the Resource Manager in Enterprise Manager:

1) Access the Database Home page.

and it says: The Database Home page is the main database management page in Oracle Enterprise Manager Database Control (Database Control).

It asks to confirm certain steps to access the "DataBase Home Page" such as :

1.) Ensure that the dbconsole process is running on the database host computer.

I checked the dbconsole process by running the following statement on the command prompt :

C:\oracle_base\product\11.2.0\dbhome_1\BIN>./emctl status dbconsole

It showed a warning on the command prompt saying :

"OC4J configuration issue . C:\oracle_base\product\11.2.0\dbhome_1\oc4j\j2ee\OC4J_DBConsole_rubbalbhusri-PC_orcl not found."

Note : rubbalbhusri-PC is my system-name(the name I defined for my Operating System)

Now, my question is this :

Is "OC4J_DBConsole_rubbalbhusri-PC_orcl" directory created during installation itself ?

Why, I don't have one( "OC4J_DBConsole_rubbalbhusri-PC_orcl" directory ) ?

What should I do to have one( "OC4J_DBConsole_rubbalbhusri-PC_orcl" directory ) ?

Please don't tell me to install the whole "oracle database 11g" again.

Tell me some repair, which solves this problem, if any ?


texts: Which is quicker in SSIS: uploading to a database via SP with TVP param, or using an OLE DB Destination?

texts:
I'm importing a (relatively) malformed CSV into SQL Server via SSIS. So far my workflow is:

Import CSV as one column
Use script component to clean up into 113 properly formed columns

Now I have a choice: I can configure the script component as either a 'transformation' or a 'destination' step. I have chosen 'destination', which involved:

Creating a DataTable
Then iterating through single column rows, splitting, and populating DataTable
Calling SP in SQL Server with DataTable as a param

I have found this works with a sample of the CSV, but have not been successful with the entire CSV. I suspect this is because I'm developing remotely and the 50mb + DataTable upload is causing the package to time out.

So my question is:

Would using the SSIS OLE DB Destination task be more reliable and faster in some way than what I'm currently doing?


texts: On not public server: Host name example.com could not be resolved: Name or service not known

texts:
This is not a duplicate (please read carefully)

Digging in mysql logs of a production server I found a few entries with the error above, all pointing to the same host name. Each entry appears ~30 seconds right after mysql starts. There is also no record of the hostname in mysql.user table

I would not be surprised if mysql was exposed to public internet but it is not. The port is blocked by lfd/iptables and explicitly allowed for a few public/private IP addresses.

MySQL's skip_name_resolve is not enabled, since I need hostname resolution for a few VPN servers.

Where these entries may be coming from? The server is WHM/CPanel, MySQL 5.6.21


texts: Need advice on combining tables or leaving as separate tables

texts:
I tried to find a related question similar to mine, but couldn't. I apologize if this is a repeat.
I have an odd situation and just need advice. I have a company calendar that shows events as well as employee time off (PTO). These are in an event table.
Events Table
id
user_id
text
start_date
end_date

PTO Table
id
user_id
text
reason
length
start_date
end_date
type
status
overall_status

Here's where it gets a bit messy. There are a few identical columns in both tables, such as user_id, start_date, and end_date. If a user submits a request to change the PTO, I would need the make the changes in two tables.
My question is, is it better to combine the tables or update the related Event when a change is made? OR, is it better to find a way to pull data from two tables and combine them into one variable for display on the calendar.
Hope that's not confusing.


texts: query to search a particular word/string in toad/oracle

texts:
I trying to find out a word/string named 'audapro_ind' in oracle database in toad. This word can be present anywhere in column of table, function, procedure etc. in oracle database

I tried few queries like

select name , line,text from dba_source where upper(text) like
upper ('audapro_ind') escape '\'

also

select DISTINCT(name) from user_source where type = 'PROCEDURE'
 AND text_like 'audapro_ind'

But  could not able to find the solution


texts: Postgres plpgsql - Using a variable inside of a dynamic create statement

texts:
Using Postgres pl/pgsql, I'm attempting to create a table using a dynamic EXECUTE command, such as:

 ...
 DECLARE
    tblVar varchar := "myTable";
 BEGIN
 EXECUTE 'CREATE TABLE $1 (
             foo integer NOT NULL,
             bar varchar NOT NULL)'
 USING _tblVar;
 ...

However, I continue to receive the error message

  ERROR:  syntax error at or near "$1"

If I don't use the $1 token and, instead, write the string myTable it works just fine.

Is there a limitation on using dynamic statements for CREATE calls?


texts: Facing Error 'Missing expression' while trying to create a After insert trigger in PL/SQL

texts:
Hi everyone hope you're doing well.
AS you might know by now I'm new to PL/SQL and I'm trying to write a trigger
as you can see below :

CREATE OR REPLACE TRIGGER T_Insert
  AFTER INSERT ON tag

    for each row
      BEGIN

       Select case
          WHEN EXISTS (Select tag , brand_id
                       from my_tags
                       where tag      = :new.tag AND
                       brand_id = :new.brand_id) THEN

                      UPDATE my_tags set count := count +1
         ELSE
            insert into my_tags (sr_no,tag,count,brand_id)
            valuse (:new.sr_no , :new.tag ,1 , :new.brand_id)
            end case AS SS
     from dual;

 END;

When I want to run the code and create the trigger I receive these errors:

ORA_00936 : missing expression. (Line 15) update statement
SQL statement ignored

am I using the Select Case and When Exists The wrong way?


texts:
Have a bit of confusion regarding a normalization from 1NF to BCNF.  The teacher gave us this excercise to illustrate a normalisation all the way up to BCNF.  I think I have found a problem with the given solution and would like to clear this up.

The way I have solved it up to 3NF is as follows:

My understanding is that there are TWO partial dependencies here

The Primary key is made up of (SID, InstructorID)

The Subject is dependent only on the the InstructorID and not on the entire primary key
The Name is dependent only on the SID only and not on the entire primary key

Finally, 3NF where the transitive dependency has now been removed and refactored into it's own table.
No BCNF violations here!!!

QUESTION:

Is my solution correct?

have I correctly performed the 2NF step?


texts: How to read data from multiple Excel files with SQL Server Integration Services(New Senario)

texts:
Hope you're doing well.
I have a scenario here.
As you can see I have a table with two main columns :

What I need is to read each "FilePath" which is "Active" right now and then read the Excel files in that path. So I assume that I need at least 2
"For Each loop Container" inside each other. I've already done some part of the scenario :

Within the "Execute sql task" I'm retrieving "FilePath" which are active from the table and putting them in an "object" variable (My first variable in SSIS) .

Then I have my second variable in SSIS in which I keep the name of each "FilePath" each time the outer loop is being executed.

At the end I have my third variable which will change each time the inner loop in being executed and it carries the Excel files.

The outer loop must execute to the number of active rows in meta data table and the inner loop must execute to the number of file within the path.

The point is I receive no error after executing the package, but the data won't be inserted in my "OLEDB Destination"
I was wondering if you could help me with this issue.


texts: Change the sql table output - Rows to Columns

texts:
I have a result from recursive query that gives me Table1 (SQL Server 2008 R2).
I need to convert the Table1 to Table2 and I don't know the best and simplest approach to do it.
Any suggestions?
Table1

Table2

Regards.


texts: Copying MySQL tables, InnoDB tables not recognized

texts:
I have to set up a database copy on my local computer.
The problem is that that I have given just the raw database table files (.frm, .ibd, .MYD, .MYI).

I created the database by creating a folder in MySQL's data folder and moved all database files there.

The problem is that some tables seem to work correctly the ones with MyISAM storage engine
(.frm, .MYD, .MYI), I can access information in them, see their structure.

My problem is with the tables with InnoDB storage. I cant see their structure in MySQL workbench and when I try to select data from them MySQL spits out an error "Table 'table_name' doesn't exist".

There are two files per one InnoDB table (.frm and .ibd).

To fix this problem I tried to enable innodb_file_per_table, I added to my.ini file in MySQL root folder line innodb_file_per_table = TRUE but this does not solve anything.

I checked working InnoDB tables in my other MySQL databases and there are the same two files per table (.frm and .ibd).

I will include bellow screenshots of what I described for more clarity

The problematic table example:

Ok table example:

Could someone help me out please, Im hoping I was descriptive enough.


texts: Any potential complications from ms sql backup db to disk = 'nul', disk = 'nul', ...?

texts:
Our development team has a tool that notifies us of issues with our DB backups, but they have not yet given us the ability to exclude certain databases from the alerts - DBs that we do not want backups of are alerting us.

The only way I've found to resolve this are backups to the windows NUL device, however that takes much more time than I expected, and seems to still use processor time, and probabaly take RAM and I/O.

In attempting to speed up the process I tried several sets of backup options, collected data about time, and found that the more backup files I allocate as NUL, the faster it went. SQL 2008 R2 capped me at 64 files, i.e.

BACKUP DATABASE <db_name> TO DISK = 'NUL', DISK = 'NUL', ...
64 disk = 'NUL' being a clear winner for fastest option.

I'm worried about the server resources though, and am now wondering about striking a balance between 'speed' and 'not hogging all resources while this is happening'.

Any advice on resources or other ideas would be awesome. Thanks.


texts:
Is there a good reason why I should not turn the column statistics down to 0 on columns that are not used in JOIN, WHERE, GROUP BY, or window functons etc.?

It strikes me that the default statistics target is only there for people who can't be bothered to set the statistics target to either something high for JOIN and WHERE columns, and 0 for everything else. Am I wrong?


texts:
I want to report on failed Sql Agent jobs using Get-SqlAgentJobHistory but excluded jobs that were not invoked by the service account.
I'm running this cmd:

 Get-SqlAgentJobHistory -ServerInstance $SqlServer -OutcomesType Failed -StartRunDate ((Get-Date).AddMinutes(-15))

I want to exclude these:

InstanceID       : 146788
SqlMessageID     : 0
Message          : The job failed.  The Job was ***invoked by User domain\user***.  The last step to run was step 3 (packagename Pkg).
StepID           : 0
StepName         : (Job outcome)

but include these

   InstanceID       : 146789
  SqlMessageID     : 0
Message          : The job failed.  The Job was ***invoked by User domain\serviceaccount***.  The last step to run was step 3 (packagename Pkg).
StepID           : 0
StepName         : (Job outcome)


texts: Concurrent UPDATE statements of single row in small table takes minutes

texts:
I have a very simple table with no indexes (although scan_uuid could have a UNIQUE index if that helps):
create table public.host_scan_complete_log_counts
(
    scan_uuid uuid not null,
    num_shards_completed numeric default 0 not null
);

I then have a few hundred nodes each with their own direct connection to the database. As they work, they perform an update on this table like so:
UPDATE host_scan_complete_log_counts
SET num_shards_completed = num_shards_completed + $2
WHERE scan_uuid = $1

Although there is never a deadlock, these UPDATE statements can take several minutes to complete.
Since the table of scan counts is so small, I expected the UPDATE statements to take very small fractions of a second and therefore no queue of UPDATEs to build up. But it seems that isn't the case, possibly due to some locking clashes. Is there some way to improve this situation perhaps with more sophisticated UPDATE SQL?
Here's the output of a query showing the age of the locks that get generated, in case that is useful.
SELECT   l.relation::regclass,
         l.transactionid,
         l.mode,
         l.GRANTED,
         a.query,
         a.query_start,
         age(now(), a.query_start) AS "age",
         a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
ORDER BY a.query_start;

relation
transactionid
mode
granted
query
query_start
age
pid

ExclusiveLock
t
UPDATE host_scan_complete_log_counts SET num_shards_completed = num_shards_completed + $2 WHERE scan_uuid = $1
2021-01-31 22:17:20.921768+00
00:02:24.465192
15624

6826571
ExclusiveLock
t
UPDATE host_scan_complete_log_counts SET num_shards_completed = num_shards_completed + $2 WHERE scan_uuid = $1
2021-01-31 22:17:20.921768+00
00:02:24.465192
15624

host_scan_complete_log_counts

ExclusiveLock
f
UPDATE host_scan_complete_log_counts SET num_shards_completed = num_shards_completed + $2 WHERE scan_uuid = $1
2021-01-31 22:17:20.921768+00
00:02:24.465192
15624

host_scan_complete_log_counts

RowExclusiveLock
t
UPDATE host_scan_complete_log_counts SET num_shards_completed = num_shards_completed + $2 WHERE scan_uuid = $1
2021-01-31 22:17:20.921768+00
00:02:24.465192
15624

host_scan_complete_log_counts

ExclusiveLock
f
UPDATE host_scan_complete_log_counts SET num_shards_completed = num_shards_completed + $2 WHERE scan_uuid = $1
2021-01-31 22:18:03.688236+00
00:01:41.698724
22147

6826937
ExclusiveLock
t
UPDATE host_scan_complete_log_counts SET num_shards_completed = num_shards_completed + $2 WHERE scan_uuid = $1
2021-01-31 22:18:03.688236+00
00:01:41.698724
22147

host_scan_complete_log_counts

RowExclusiveLock
t
UPDATE host_scan_complete_log_counts SET num_shards_completed = num_shards_completed + $2 WHERE scan_uuid = $1
2021-01-31 22:18:03.688236+00
00:01:41.698724
22147

ExclusiveLock
t
UPDATE host_scan_complete_log_counts SET num_shards_completed = num_shards_completed + $2 WHERE scan_uuid = $1
2021-01-31 22:18:03.688236+00
00:01:41.698724
22147

6827769
ShareLock
f
UPDATE host_scan_complete_log_counts SET num_shards_completed = num_shards_completed + $2 WHERE scan_uuid = $1
2021-01-31 22:18:12.969814+00
00:01:32.417146
22179

host_scan_complete_log_counts

RowExclusiveLock
t
UPDATE host_scan_complete_log_counts SET num_shards_completed = num_shards_completed + $2 WHERE scan_uuid = $1
2021-01-31 22:18:12.969814+00
00:01:32.417146
22179

ExclusiveLock
t
UPDATE host_scan_complete_log_counts SET num_shards_completed = num_shards_completed + $2 WHERE scan_uuid = $1
2021-01-31 22:18:12.969814+00
00:01:32.417146
22179

6827071
ExclusiveLock
t
UPDATE host_scan_complete_log_counts SET num_shards_completed = num_shards_completed + $2 WHERE scan_uuid = $1
2021-01-31 22:18:12.969814+00
00:01:32.417146
22179


texts: Cross-product between table column and input values

texts:
I seem to be unable to write an SQL query, which computes the cross-product between a table column and a set of given input values.

Something along the lines of:

WITH {1,2} as Input
Select *
From mTable.column, Input

With mTable.column containing the values 3 and 4, it should return:

1,3
1,4
2,3
2,4

Is there any way to achieve this?


texts: Will this Database design logic work and what should be used?

texts:
A few weeks ago I had a client of mine ask me about how complex it would be to develop a custom CRM application similar to the one being provided by highrisehq.com

What he essentially wants to do is provide external sales reps within a specific industry access to use this CRM application for managing all of their contacts, clients and communication/tasks.

The key difference between highrisehq and this system is that he wants to allow each of these sales reps the ability of "sharing" data with one another. Now what I mean by this is the following:

Lets assume there were two separate CRM account users within the system, each of these accounts have thousands of contacts and hundreds of relationships (linking companies to contacts, contacts/companies to projects, and contacts to messages/tasks/notifications).

Now... what he wants to do is allow both of these sales reps each with their own  independent accounts to be able to share information with one another.

My thought was that the first thing which should happen here is a "share" request be initiated through the system by one user to the other user. Upon this share request being accepted both accounts now have the "ability" of sharing info with either other.

My thought was that once these accounts are linked that user can now show up as a "sharing user" and show up in all areas where you would normally set the view/edit permissions for individual users.

Where things get complicated in my view is how to show the "activity stream" for example because essentially now the system is not just showing all the activity for my account but also all the activity for external accounts for which the user has been granted access.

Furthermore... if a user has lets say 100 accounts he is sharing information with is there anything I can do to ensure the system does not slow to a crawl?

What is the most logical, best and least expensive db logic which should be used to create these relationships so a query on a single account returns data at the same speed as when having to access data from multiple accounts?

Any other key aspects which you guys feel should be considered or are "must have" features?


texts: SQL Server domain name and user name formatting

texts:
I am executing a SQL script in SQL Server Management Studio 2018. In my script I need to specify a user (including the domain - unsure if I need the server name).

So I have created a user sam, set the user type to SQL user without login and set the users role to db_datareader and db_datawriter.

I then execute my script but it gives me the error: User or role 'MHT.sam' does not exist in this database.

But I am almost certain I have added this user to the database (see my images below to double check). Is my user and domain name format correct? What do you think I am doing wrong?

Here's my domain and server:


texts: When do Azure SQL Database index rebuilds occur?

texts:
Azure backups occur every few minutes as per the documentation for Automated backups:

  SQL Database supports self-service for point-in-time restore (PITR) by automatically creating full backup, differential backups, and transaction log backups. Full database backups are created weekly, differential database backups are generally created every 12 hours, and transaction log backups are generally created every 5 - 10 minutes, with the frequency based on the compute size and amount of database activity.

When do index rebuilds occur in Azure SQL? Are they automatic or manually selected in a maintenance window?


texts: Table design question for a `Check all that apply` survey

texts:
I'm looking for advice on a design for a portion of a table.

Background

We're collecting survey data for multiple sites, and one question is a check all that apply. Currently, there are from 2-5 available options based on the site, though that number could increase. When I get the data, I get anywhere from 2-5 columns (based on the number of options available on that survey), each column in the result set responds to one of the options. The data in the columns will all be either NULL or a text value representing that ordinal option for that particular survey. Different sites will also have different options from a set, so there is no guarantee that column 1 is always the same response. There are 13 total possible options to pick, at this point, and no one site has more than 5 available, but it's possible that we'll add more options to pick from, and that the there could be more than 5 options per site in the future.

Sample Data

Survey 1:

Col 1       Col 2     Col 3
A           B
            B
A                     C
A           B         C

Survey 2:

Col 1       Col 2     Col 3
B           D
            D         E
                      E
B

Survey 3:

Col 1       Col 2     Col 3
A           D
            D         F
A                     F
A

The question

How do I best store this data?

Currently

I get the data in a .CSV, and for expediency & urgent deadlines, I've been pasting it into an Excel spreadsheet. (That's why I'm looking to move it to an actual table...) I've created one column for each possible answer, and if the value in CSV.Col1 = Excel.TableHeader, then I put a 1 in that column. This allows me to quickly filter the Excel rows and sum the 1s for a quick total of how many there were. This is probably not ideal for an actual database.

Possibilities Considered

I've considered assigning each of the 13 options a value and summing them to store them in a single column. For Example:

A = 1
B = 2
C = 4
D = 8
So my Survey 1 example from above would store the values of:
3
2
5
6

I've considered leaving it as it is currently with 13 columns of either Boolean, Number, or Text format. Each column would hold [True|False], [1|0], or [A|B|C|D...], as appropriate.
Creating a 1:M dependent table to store a result per row, linked to the master table row for that survey. The child table would have a PK, a Foreign Key back to the parent table, and a text column to hold the response.

The back end

The data will be moved from Excel to Access, with the intent to move it into SQL Server as soon as possible.


texts: Postgres client unable to detect UTF-8 database

texts:
Context

I am deploying a Vagrant box for my development team, and have encountered a problem restoring UTF-8 collated dumps. I don't believe it is related to the now-infamous "UTF8" has no equivalent in "LATIN1" problem, as my issue seems to lie in the client. Additionally, I have successfully restored exactly the same dump files on other Precise boxes (though precise64) with the same client and server packages (9.1). However, I am uncertain if other supporting libraries may not be present in my Vagrant box.

I can not alter the way in which the dump files are created, but I don't believe the problem lies there, anyway.

Problem

I successfully programatically create and restore a UTF-8 (specifically, en_CA.UTF-8) dump by creating a table with:

createdb --lc-collate=en_CA.UTF-8 --lc-ctype=en_CA.UTF-8 -E UTF8 -T template0 ${SCHEMA}

(If relevant, I'll add that my other successful restores (on the precise64 system) did not require any additional locale parameters to createdb.)

Then restore,

pg_restore -d ${SCHEMA} --single-transaction /tmp/${SCHEMA}_-_latest.backup

I have attempted with both postgresql.conf's client_encoding commented out and manually set to client_encoding = 'UTF8'; both behave the same.

My /etc/default/locale contains

LC_ALL=en_CA.UTF-8
LANG=en_CA.UTF-8

And yet, at the psql prompt:

schema=# SELECT * FROM table;
ERROR:  character 0xe28099 of encoding "UTF8" has no equivalent in "LATIN1"

schema=# show server_encoding; show client_encoding;
 server_encoding
-----------------
 UTF8
(1 row)

 client_encoding
-----------------
 LATIN1
(1 row)

The schema is UTF-8, right (so the commented-out client_encoding should use this value)?

schema=# SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = ...
pg_encoding_to_char
---------------------
 UTF8
(1 row)

Setting it manually at the command prompt works:

schema=# set client_encoding to UTF8
SET
schema=# show client_encoding;
 client_encoding
-----------------
 UTF8
(1 row)

schema=# SELECT * FROM table;
<EXPECTED DATA>

What have I done wrong?

EDIT for Craig:

vagrant@precise32:~$ locale
LANG=en_CA.UTF-8
LANGUAGE=
LC_CTYPE="en_CA.UTF-8"
LC_NUMERIC="en_CA.UTF-8"
LC_TIME="en_CA.UTF-8"
LC_COLLATE="en_CA.UTF-8"
LC_MONETARY="en_CA.UTF-8"
LC_MESSAGES="en_CA.UTF-8"
LC_PAPER="en_CA.UTF-8"
LC_NAME="en_CA.UTF-8"
LC_ADDRESS="en_CA.UTF-8"
LC_TELEPHONE="en_CA.UTF-8"
LC_MEASUREMENT="en_CA.UTF-8"
LC_IDENTIFICATION="en_CA.UTF-8"
LC_ALL=


texts: How can I use all the selected values of a column, one by one, in another query?

texts:
Use column values returned from the statement below:

SELECT DISTINCT(column_name) FROM Table1;

into the following query:

SELECT dif_col_1,
       dif_col_2
  FROM Table2
 WHERE key_in_table = (SELECT key_in_table
                         FROM Table3
                        WHERE UPPER(some_column) LIKE '%each_value_from_the_first_query%');


texts: How to add foreign key in a table where column value can come from two different tables?

texts:
I have three tables

create table main_comments (
   id serial primary key,
   comment text not null
);

create table sub_comments (
   id serial primary key,
   main_id integer not null,
   comment text not null ,
   foreign key (main_id) references main_comments(id) on delete cascade
);

create table report (
  id serial primary key ,
  comment_id integer not null ,
  table_type bool ,
  report text not null
);

in report table comment_id column's value can come from main_comments or sub_comments. so avoiding the confusion i add table_type column which tell from where comment_id value comes from like main or sub table. But now i want to add foreign key to report table.


texts: Large hexadecimal PRIMARY KEY - how to index substrings of the PK field?

texts:
I have a requirement for a very large (in terms of the number of rows, not data) database, and need to use a very long, unique, hexadecimal PRIMARY KEY instead of the usual AUTO_INCREMENT integer.
As an example, lets say that the PK is similar to an IPv6 address, say something like 2001:0db8:0000:0000:0000:8a2e:0370:7334.
So, apart from the usual searching by the PK, there will be a need to search by subfields of it - i.e. find all keys, that begin with "2001" (covered by the PK- should be sargeable), or (say) has 0db8 in their second set of four hex characters, and then to reference this 2nd key from other tables.
What is the best way to store such a primary key (assume that I am using MariaDB or MySQL)?
My thoughts are to do one of the following:

one big column,

have 8 columns and make a composite key,

or something else.

From a general database point of view, what would be the best technical solution to server my requirements?
Apart from the purely technical aspects of the question above, I would also be interested in any recommendations that users here might have as to the best database engine to use in this case?
I will only have a couple more tables, that will have FOREIGN KEY references to the long hex PRIMARY KEY. I can do without the DRI (Declarative Referential Integrity) if there are major performance benefits


texts:
I'm trying to insert the below query using SSIS. I'm doing this just because there are some overlapping ID's that are already existing ID's in the destination table. I tried using the following to get the Min and Max IDs for both tables in order to insert only the non existing rows:

/*
--SourceTable
 MinID  MaxID
select 552596452-633166940

--destination table
MinID        MaxID
450485204  596693972
*/

Then I now used that to implement the insert using SSIS Package. I couldn't get this package to run. Is there anything that i'm doing wrong? is there any other SSIS data flow that I could use to work around this? I chose to use SSIS because the record is aroung 30 million rows which will take too long to run on SQL and it's going to also fill up the logspace pretty quicky which will affect the performance of the server.

INSERT INTO dbo.destinationtable
           ([id]
           ,[userId]
           ,[serviceId]
           ,[keyId]
           ,[wappush]
           ,[builturl]
           ,[datestamp]
           ,[pocessingTime]
           ,[UserAgent]
           ,[Referer]
           ,[fallBack]
           ,[Error]
           ,[ClientIP]
           ,[AdvertUrl])

SELECT  id, userId, serviceId, keyId, wappush, builturl, datestamp, pocessingTime,
          UserAgent, Referer, fallBack, Error, ClientIP, AdvertUrl
FROM  sourcetable a (nolock)
WHERE (datestamp < DATEADD(d, 0, DATEDIFF(d, 0, GETDATE() - 60)))
AND id > 596693972 and id <= 633166940
ORDER BY id


texts: Replication To Enterprise & Standard Edition subscribers

texts:
We have a defined and well running transactional replication process.
One publisher, one distributor (on separate servers) and several subscribers (pull). All servers have Enterprise Edition.
Latency checked using a canary table. I use a canary table on all subscribers instead of tracer tokens. Don't see any additional latency. There is a great article by Kendra Little showing that canary tables are very good for this measure, since tracer tokens are too patient.
The replication is not for the whole database, only several tables, but the tables are relatively big and there are a lot of inserts.
I added one more subscriber using Standard Edition. It runs well and has no issues except it shows more Latency issues, not huge but it is alerting much more than all other subscribers.
Questions
How can I handle this issue? Is it possible to improve? What should I check?
If the server is outperformed on inserts, what should I do to improve it?
Information added in response to comments
The Standard Edition server itself is the same as the other subscribers. All servers are created by DevOps using the same configurations. We have 12 CPU cores on all the subscribers. The Standard Edition subscriber usually performs the same as all other subscribers (Enterprise). Only some times it starts to show increasing delay (30min-1hr) and it takes more than hour to recover.
PLE is 55146 (on Enterprise subscriber it is 178775). The main wait stats on the Standard subscriber are:

CXPACKET
PAGEIOLATCH_EX
PAGEIOLATCH_SH
PREEMPTIVE_OS_AUTHENTICATIONOPS
IO_COMPLETION
LOGBUFFER

The latency is between the distributor and the new subscriber. It usually serves ok and shows no latency as for all other subscribers. But once or twice a day it show latency that at its peak gets to 30min-1hr. I find that it happens when there are thousands of updates coming.
The tables are not partitioned. Everything in replicated tables suits Enterprise & Standard.


texts:
I have a database in a high availability group with two nodes and one listener.

Several times a week, I have a problem with switchovers. The message I get is:

  Stopped listening on virtual network name ''. No user action is required.

Any idea?


texts:
I'm a newbie for microsoft technologies and I'm having problems in connectivity part for SQL Server 2014. This is probably an easy issue but I'm stuck.

I've 4 servers. One of them is the Active Directory Domain Controller, one is the application server and the others are being used for SQL Servers. Since it's a connectivity issue, IP's for servers are;
-AD DC : 10.6.0.100 ( Also the DNS Server )
-APP   : 10.6.0.110
-SQL 1 : 10.6.0.120
-SQL 2 : 10.6.0.121

I've successfully created a failover cluster (DBCLUSTER) and set the IP address of the FC as 10.6.0.130 ( which is not an actual server IP, I really don't get this part.. ).

Later, I've created an AlwaysOn Availability Group for sql servers. I've successfully created the AG without listener. I could connect to servers from each other, database syncs without problem. Then, I've created a listener (AG-LISTENER) and set it's ip as 10.6.0.131 ( which is not an actual server IP, again? ) and set it's port to 5525. It gave no problems.

So I wanted to test connectivity. When I wanted to make connections from APP server to directly SQL 1 or SQL 2, I can connect without problem. But when I try to connect to AG-LISTENER, it cannot find it on network. When I check the DNS records. I can see it as it's being hosted on 10.6.0.131.

When I tried to ping to AG-LISTENER from AD-DC, APP, SQL 2 servers, it responds that the destination host is unreachable (it's pinging 10.6.0.131 but the response comes from the IP's of AD-DC, APP and SQL 2 servers). It can connect from SQL 1 server, which is the primary for AG.

I've checked the firewalls, there's no problem. But I think this is an network issue which I have no clue.

PS: Servers are operating on Windows Server 2012 and are not being hosted on Azure.

SOLUTION

Cause of the problem was the network topology of the service provider. It's found that IP's 10.6.0.0/32 could be used by other servers of other customers. So they've allocated us another IP block which only we have access to and it worked like a charm.


texts:
Getting PAGELATCH_UP:tempdb:1(PFS) with so many waiting queue. What can be solution?

We have two hexacore processors. Ideally how much tempdb can we create to handle this issue?


texts: Are SQL Server Database writes slower with Snapshot Isolation?

texts:
I have a lot of deadlocks going on in my system.

I would like to use Snapshot Isolation to fix them, but my DBA has reserves about it.

One of his concerns is that Snapshot Isolation slows down writes.  This is because it has to write to cache and then to the TempDb (row version) and then it can return to the caller.

A "normal" write can just write to the cache and then be done.

Is this how the row versioning works?  Or is it more complex than that?  Does it somehow do these in parallel?

Or are writes slower with Snapshot Isolation?


texts:
While going through the AWR report found that the row lock waits on indexes and buffer busy waits are more (rest of the stats look normal).

Could someone tell me if we can interpret by the below if it (row lock waits & buffer busy waits) is the cause of slowness? Or the stats look ok based on the DB time.

Thank you for your time.

    Can we reduce the row lock waits on indexes and reduce the buffer busy waits by further tuning of IX1_PCWAUDITLOG, IX1_PCWREQRESP & LOBSEG_NEW (image).


texts: Same MySQL query is so slower on ubuntu than Windows

texts:
I have a big select in MySQL 5.7.33. In ubuntu (real server) 20.04 it takes 600s for the answer but 1.2s on Windows about the same on the VM Ubuntu 20.04.
The MySQL configuration for all the os are the same.
Do you have any idea to resolve this problem?
Thanks.
Edited Part:
Explain Select ...
Slow:

Fast:


texts: Deleting rows from multiples tables at once

texts:
I have a question about this piece of code I just wrote. I have a problem with the syntax, the first query executes its purpose, the problem is in the second query which does not.

if(isset($_POST['deletecontr'])){
  $dcquery = $_POST['dni']; // This get the ID of the person who I want to delete.
  $catchempr = ("SELECT `empresa` FROM `contratistas` WHERE dni='$dcquery'"); //This catch the name of the company of that person
  $query = ("DELETE FROM `contratistas` WHERE dni='$dcquery'"); // This works fine
  $query2 = ("DELETE FROM `empleados` WHERE empresa='$catchempr'"); This does not.
  mysqli_query($link,$query);
  mysqli_query($link,$query2);
  echo "La empresa a sido eliminada de la base de datos";

I hope you guys can see something that I'm not seeing.


texts: Is there anybody using the SQL Server feature to create groups of stored procedures differentiated by number?

texts:
The question refers to the number parameter in this msdn documentation

If you don't you can create multiple stored procedures in SQL-Server differentiated by number and drop them with a single drop.

create procedure dbo.stored_proc1 as select 1
go
create procedure dbo.stored_proc1;2 as select 2
go
exec stored_proc1
-- returns 1
go
exec stored_proc1;2
-- returns 2
go
drop stored_proc1
-- drops both
go

I wonder if this feature is used by anybody for something useful or if it is just a historic curiosity.


texts: Is there a UI tool for moving data from two different tables?

texts:
The schemas in the two databases are different. Eg.

Database 1 / users
- id
- name
- phone

Database 2 / users
- id
- first_name
- mobile

Is there a tool that lets me visually create the statements or do the transform / copying the data?

Using MySQL by the way :)


texts: Think I have the right indexes and query, but MySQL Query is still slow

texts:
table_1

id | column_1 | import_date

Indexes: Mutli-column key (multi_column) of import_date and column_1 in addition to an individual index for each column.

table_2

id | column_1 | column_2

Indexes: Multi-column key (multi_column) for column_1 and column_2 in addition to an individual index for each column.

i.e. both tables have two composite indexes (of the same name in this question) as well as separate indexes.

Query

I've tried flipping the query around and playing with using/forcing different indexes but it didn't seem to help.

SELECT table_1.import_date, count(*) AS count
FROM table_1
FORCE INDEX (import_date)
JOIN table_2 USE INDEX (multi_column) ON table_1.column_1 = table_2.column_1 AND column_2 = "Some String"
WHERE table_1.import_date >= '2016-05-26'
GROUP BY table_1.import_date
LIMIT 365

I tried adding in the LIMIT and a where clause to check where the date was greater than the same date last year just to try and prevent a large number of results and filter but neither seemed to improve the performance of the query, both tables have a large record set.

EXPLAIN

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  table_1 NULL    range   multi_column,import_date    import_date 6   NULL    1932171 100.00  Using index condition; Using where
1   SIMPLE  table_2 NULL    ref multi_column    multi_column    126 table_1.column_1,const  117 100.00  Using index

Sorry for obfuscated table and column names, trying to keep it simple.

Original Explain without force/use:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  table_1 NULL    index   multi_column,column_1,import_date   import_date 69  NULL    365 100.00  Using where; Using index
1   SIMPLE  table_2 NULL    ref multi_column,column_1,column_2  multi_column    126 table_1.column_1,const  117 100.00  Using index

Row counts for each table are 3,937,236 and 4,503,551 respectively.

The query currently takes around 80s, how long should it take, I'm not sure but I imagined with indexes then hopefully under a second.

Running locally on my development machine just now, Mac OS X El Capitan, 2.2 GHz i7 16gb ram.

MySQL version 5.7.15

Both tables are using InnoDB.

This query seems to take it down to roughly 20 seconds:

select import_date, count(*) as count
from table_1
where exists (select * from table_2 where table_1.column_1 = table_2.column_1 and column_2 = 'Some String')
group by import_date


texts: Problems migrate database from 4 to 5.5 mysql

texts:
I have a server with 4.0 mysql version innodb engine.

I need to migrate to other server with 5.5 version (linux) ok

I dump the full database using mysqldump, when i try to restore on the 5.5 server it has a problem, because the file generate in the 4.0 set the variable on the sql scritp TYPE=INNODB and mysql 5.5 accepts only ENGINE=INNODB format on the time table creation and it abort the process.

I search on google, call any friends and nothing! the sql file has 16 gigabytes. Is not possible edit with an file editor.

error exemple:

CREATE TABLE tabadi (
nAdicao char(3) default '0',
nSeqAdic char(3) NOT NULL default '',
cFabricante varchar(60) NOT NULL default '',
vDescDi varchar(15) NOT NULL default '',
xPed int(6) NOT NULL default '0',
nItemPed int(6) NOT NULL default '0',
nDI varchar(12) NOT NULL default ''
) TYPE=InnoDB

  ERROR 1064 (42000) at line 19: 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 'TYPE=InnoDB' at line 9

Ps: I did try to change TYPE TO ENGINE and its solved the problem.

Can anybody help me? Thanks a lot.


texts: Netezza: What schema contains all the system tables / views?

texts:
Is there a schema I can look in or a query that I can run to see all the Netezza system or metadata tables?

I know about _v_table, _v_view, _v_relation_column, and _v_groupusers.
I know there are many others and I would like the definitive list.
Documentation on each would be nice too, but I will settle for just a list.

I looked at How list of netezza system tables/views? but I am looking for a list of system tables. The author of that question is looking to pull specific metadata about the tables on his system.


texts:
I want to make a FTS on my text column on Postgres, the problem is that I want to search words with "_" between letters, like "the_event" but the default parser split those words and identify "_" like a blank space. If I debug the parser I get the following:

postgres=> SELECT ts_parse('default', 'the_event');
 ts_parse
-----------
 (1,the)
 (12,_)
 (1,event)
(3 rows)

More specifically, I want to create a FTS parser that doesn't split a word with certain characters. How can I create a parser on Postgres?


texts: Partly rotated table with pivot/dynamic SQL without aggregation

texts:
Using MS SQL 2008, I have created a table that I want to display in horizontal direction instead of vertical direction. Because I do not now how many query results I will get returned I think I have to use the pivot function in combination with dynamic SQL

I already saw some examples with dynamic SQL and pivot, but I could not figure out what my query should look like in order to generate the correct output.
Most Pivot examples includes aggregation (AVG, MAX/MIN, Count) functions, but this is not what I want/need. Each row is uniq.

The issue is that the first 3 columns should remain in their original position while the rest should be moved to new (to be generated) columns in the horizontal position.

The starting table looks as below, with all columns as varchar

Article number,
Article Description,
Gross price,
OEM Customer name,
OEM Article number (alternative OEM article number),
OEM Article description (alternative OEM article description),
OEM price

Using some abbreviations the columnnames could be as below.

Art_nr; Art_descr; Gross_price; OEM_Cust, OEM_Art_nr;OEM_Art_descr;OEM_price

1234,cup,100,Jones,101234,cup_a,95
1234,cup,100,Daniels,NULL,cup_b,90
1234,cup,100,Williams,NULL,cup_c,85
3456,plate,200,Jones,103456,plate_a,190
3456,plate,200,Daniels,NULL,plate_b,180
3456,plate,200,Williams,NULL,plate_c,170
987,fork,5,Jones,10987,fork_a,4
987,fork,5,Daniels,NULL,fork_b,3
987,fork,5,Williams,NULL,fork_c,2
rest of rows....(more articles but also more customers)

Code to create example table:

CREATE TABLE My_Table(
Item_No VARCHAR(50),
Item_Desc VARCHAR(50),
Item_Price VARCHAR(50),
Cust_Name VARCHAR(50),
Cust_Item_No VARCHAR (50),
Cust_Item_Desc VARCHAR (50),
Cust_Item_Price VARCHAR (50)
)

INSERT INTO My_Table
(Item_No, Item_Desc, Item_Price, Cust_Name, Cust_Item_No, Cust_Item_Desc, Cust_Item_Price)
VALUES
('1234','cup','100','Jones','101234','cup_a','95'),
('1234','cup','100','Daniels',NULL,'cup_b','90'),
('1234','cup','100','Williams',NULL,'cup_c','85'),
('3456','plate','200','Jones','103456','plate_a','190'),
('3456','plate','200','Daniels',NULL,'plate_b','180'),
('3456','plate','200','Williams',NULL,'plate_c','170'),
('987','fork','5','Jones','10987','fork_a','4'),
('987','fork','5','Daniels',NULL,'fork_b','3'),
('987','fork','5','Williams',NULL,'fork_c','2');

This would be the desired table:

Art_nr; Art_descr; Gross_price;OEM_Cust_1,OEM_1_Art_nr;OEM_1_Art_descr;OEM_1_price;OEM_Cust_2, OEM_2_Art_nr;OEM_2_Art_descr;OEM_2_price;OEM_Cust_3, OEM_3_Art_nr;OEM_3_Art_descr;OEM_3_price
1234,cup,100,Jones,101234,cup_a,95,Daniels,NULL,cup_b,90,Williams,NULL,cup_c,85
3456,plate,200,Jones,103456,plate_a,190,Daniels,NULL,plate_b,180,Williams,NULL,plate_c,170
987,fork,5,Jones,10987,fork_a,4,Daniels,NULL,fork_b,3,Williams,NULL,fork_c,2

So far I have come up with this query:

SELECT Item_No, Item_Desc, Item_Price, Cust_Name_1, Cust_Item_No_1, Cust_Item_Desc_1, Cust_Item_Price_1, Cust_Name_2, Cust_Item_No_2, Cust_Item_Desc_2, Cust_Item_Price_2, Cust_Name_3, Cust_Item_No_3, Cust_Item_Desc_3, Cust_Item_Price_3
FROM
    (select Item_No, Item_Desc, Item_Price, Cust_Name, Cust_Item_No, Cust_Item_Desc, Cust_Item_Price
     FROM My_Table) AS Temp_Table
PIVOT
(
  MAX (Cust_Name)
  FOR Cust_Name in(Cust_Name_1, Cust_Item_No_1, Cust_Item_Desc_1, Cust_Item_Price_1, Cust_Name_2, Cust_Item_No_2, Cust_Item_Desc_2, Cust_Item_Price_2, Cust_Name_3, Cust_Item_No_3, Cust_Item_Desc_3, Cust_Item_Price_3)
) AS PivotTable;

The query above is not (yet) dynamical but only valid for the example given, but will function for me as "proof of concept". Unfortunately the results in the new columns are all NULL so the query is not working.

Who would be so kind to show me the query to get to the requested result or give me some clues to improve my own query?


texts:
I have a temp table #termin which contains 3 rows

When I execute the following query

SELECT t.termin, ttw.tourid, twt.va_nummer_int
  FROM #term AS t
 INNER JOIN plinfo.t_touren_werbeflaechentermine AS ttw
     ON ttw.termin = t.termin
  INNER JOIN wtv.t_werbeflaechentermine AS twt
     ON twt.jahr   = t.jahr
    AND twt.termin = t.termin
    AND twt.ID_Wt  = ttw.id_wt
    GROUP BY t.termin, ttw.tourid, twt.va_nummer_int

I'll get the following execution plan:

Each table is joined using a matching index. Both tables are partioned by ps_termin(termin).

For the first table (t_touren_werbeflaechentermine) it does an partition elimination and read only a subset of row, while for the second table (t_werbeflaechentermine) it scans the whole index (jahr, termin, id_wt include (va_nummer_int)).

So my question: why does it an index scan (and no seek) and why does it not eliminate the partitions for the second table.

PS: when using WITH (FORCESEEK) on the second table, it switchs both tables in the execution plan and does the full index scan on the first one...

PPS: Execution plan can be found here


texts:
We are using SQL Server 2016 Enterprise Ed with AlwaysOn Availability Group.  If we want to encrypt our database backups, how do we go about creating the encyrption keys?  For example, do I create the same Certificate used on both primary and secondary?  I am thinking it would be the same cert since the backups could be happening on any of the two replicas.  Anyone have any guidance on this?

I am new to encryption so this is all new to me.  Thanks for any help!