texts: PostgreSQL multi-column unique constraint and NULL values

texts:
I have a table like the following:

create table my_table (
    id   int8 not null,
    id_A int8 not null,
    id_B int8 not null,
    id_C int8 null,
    constraint pk_my_table primary key (id),
    constraint u_constrainte unique (id_A, id_B, id_C)
);

And I want (id_A, id_B, id_C) to be distinct in any situation. So the following two inserts must result in an error:

INSERT INTO my_table VALUES (1, 1, 2, NULL);
INSERT INTO my_table VALUES (2, 1, 2, NULL);

But it doesn't behave as expected because according to the documentation, two NULL values are not compared to each other, so both inserts pass without error.

How can I guarantee my unique constraint even if id_C can be NULL in this case?
Actually, the real question is: can I guarantee this kind of uniqueness in "pure sql" or do I have to implement it on a higher level (java in my case)?


texts: Caching results of query for part of table that almost never change

texts:
In our database we have a table with a very large number of rows. Let's call it Table1. The greatest difficulty for us is a COUNT query to this table with some conditions (see example below):

Query1

SELECT
    count (*)
FROM
    Table1
WHERE
    Table1.field1 = 'something'
    AND Table1.field2 = 'something'
    AND Table1.field3 = 'something'
    ..

In our application this query can occure with any number and any combination of conditions so we cannot use any index to somehow speed up this query so a simple sequence scan is being used (actually parallel sequence scan).

The feature of Table1 is as follows: new lines in it change very often and old ones almost never change (lets assume that the lines that often change is lines that created 'today' and the lines that almost never change is lines that created 'not today').

Because of this feature, the following idea emerged: if in our application occurs the need of execution of a Query1 (see above), we can actually not execute Query1, but instead we can execute 2 queries (see below): one for new rows (Query2) and one for old rows (Query3) and further we can summarized the results of these two queries to get total COUNT result. And to speed up overall performance we can perform Caching the results of query for old rows (Query3). Yes, we should clear this cache when some UPDATE occure on old rows, but it happens very rare.

Query2

SELECT
    count (*)
FROM
    Table1
WHERE
    Table1.field1 = 'something'
    AND Table1.field2 = 'something'
    AND Table1.field3 = 'something'
    ..
    AND Table1.creation_date = 'today'

Query3

SELECT
    count (*)
FROM
    Table1
WHERE
    Table1.field1 = 'something'
    AND Table1.field2 = 'something'
    AND Table1.field3 = 'something'
    ..
    AND Table1.creation_date != 'today'

So the question is:

Is this a good idea and it can speed up performance or it is a bad idea and it is better not even consider this approach?


texts: Restrict server login by user AND machine (within or not within a domain): is it possible?

texts:
I'm trying to setup a SQL Server 2008 R2 instance, restricting access to my database so only specific users on specific machines can login there.

Let's say, we have 3 devs: John, Jill and Steve, with 3 machines: JohnPC, JillPC and StevePC.
John and Jill use their machines inside a domain, but Steve doesn't.
So, the machines "full names" are: MYDOMAIN\JohnPC, MYDOMAIN\JillPC and StevePC, since Steve isn't in MYDOMAIN, but in windows default "WorkGroup".

I know that I can create accounts to restrict access from a machine, as in [MYDOMAIN\JohnPC$].

But in my case, I don't want to allow Jill to login from JohnPC.

I'm using mixed-mode login to server, so I have the 'sa' account enabled (and it's supposed to access only within the server's localhost environment).

I've read this article that shows how to create an account for a computer within a domain, but that doesn't apply to my case [Edit: because it doesn't tell how to restrict user to each computer, and it doesn't tell how to create an account for a computer with no domain]. Searched google about it, and can't find any answer that could remotely fit what I'm trying to do.

So, is this possible to achieve? And if it is, how can I do it?


texts:
We are using the Index Maintenance of Ola Hallengren (https://ola.hallengren.com) solution for a couple of years now. While setting up some new SQL servers we are seeing some strange behaviour in the SQL Agent Job.
When an error is raised in the process of trying to ALTER and index or UPDATING a statistics object the job fails the job also fails. But i cannot see why the job failed in the log. I can see errors written in the CommandLog table which we log to with the @LogToTable = 'Y' set in the jobstep, but don't see why the job would fail.

Question: Does the job exit as failed when 1 or more errors have occured in the maintenance time? Or is something else wrong?

Thanks in advance


texts: Badly-formed histogram causes bad estimates on Nested Loop

texts:
We have a query on a SQL Server 2016 SP2 CU12 Enterprise where the Query Optimizer estimates that only 1 row would come out of the Nested Loops Join operator, in reality 108501 rows came back. This caused that the Sort operator spilled to TempDB.
The estimates on the inner (index seek) and outer input (index seek) of the Nested Loops Join are correct.
I added trace flags 2363 (Selectivity Computation) and 3604 (redirect the output to the messages window) and here I found that there was a badly-formed histogram:

Plan for computation:

  CSelCalcExpressionComparedToExpression( QCOL: [Object1].Column1 x_cmpEq QCOL: [Object3].Column18 )

Loaded histogram for column QCOL: [Object1].Column1 from stats with id 1  *** WARNING: badly-formed histogram ***

Loaded histogram for column QCOL: [Object3].Column18 from stats with id 9

Selectivity: 1.07973e-009

Stats collection generated:

  CStCollJoin(ID=4, CARD=1 x_jtLeftSemi)

      CStCollBaseTable(ID=1, CARD=5.01133e+007 TBL: Schema1.Table2 AS TBL: AA)

      CStCollFilter(ID=3, CARD=108210)

          CStCollBaseTable(ID=2, CARD=2.00511e+006 TBL: Schema1.Table1 AS TBL: A)

End selectivity computation

Above is only a part of the output, the full text can be found here
When I updated the badly-formed histogram with fullscan the estimates are correct (without fullscan this issue is not resolved).
But as soon as one record is inserted in the table, the histogram is badly-formed again.
The query plan (with badly-formed histogram) can be found here and here you can find the query plan after update statistics.
No query optimizer fixes are enabled.
When I enable the original cardinality estimator for this query, using trace flag 9481, I'm getting the same query plan as after update statistics.
What can cause the badly-formed histogram?
Is there a way to solve this issue?
I tried the PERSIST_SAMPLE_PERCENT option but it didn't make a difference, the histogram gets also badly-formed.


texts: Maintenance Plan Failing After Database Collation Change

texts:
We currently use maintenance plans to perform index rebuilds each night (we inherited this system).

Yesterday, i amended to the database collation to match the instance level collation (Latin1_General_CI_AS). Ever since changing the database collation, the maintenance plan fails.

If i go into the maintenance plan and select 'View T-SQL', it errors out with the following message:

  TITLE: Microsoft.SqlServer.ConnectionInfo

  An exception occurred while executing a Transact-SQL statement or
  batch.

  ADDITIONAL INFORMATION:

  Cannot resolve collation conflict for column 1 in SELECT statement.
  Could not use view or function 'sys.all_views' because of binding
  errors. (Microsoft SQL Server, Error: 451)

With that said, if i try and rebuild all the indexes just using a T-SQL script, it runs without issue.

Any help would be appreciated.


texts:
I have rocket.chat server connected to LDAP.
When one of users try logging to their account he get logged to someone other account, it's probably because indexes. My logs look like this:

  [34mI20161126-12:07:28.992(1) rocketchat_ldap rocketchat_ldap.js:341 [31mLDAP ➔ Search.error Search by id 31353036 returned 2 records
  [34mI20161126-12:07:28.999(1) rocketchat_ldap rocketchat_ldap.js:780

  [31mLDAPSync ➔ error { [MongoError: E11000 duplicate key error index:
  rocketchat.users.$emails.address_1 dup key: { : "some@one.com" }]
  name: 'MongoError',   message: 'E11000 duplicate key error index:
  rocketchat.users.$emails.address_1 dup key: { : "some@one.com" }',
  driver: true,   index: 0,   code: 11000,   errmsg: 'E11000 duplicate
  key error index: rocketchat.users.$emails.address_1 dup key: { :
  "some@one.com" }' }

Can I rebuild that indexes or something else to resolve this problem?


texts: Looking for a tool for managing complex powershell automation processes with multiple scripts

texts:
Sysadmin here,
I'm wondering what tools you all use to manage large scale automation tasks with powershell. I currently have several powershell scripts that are regularly used, but do not interact or call each other. I'm looking for a GUI interface to help make sense of it, and grab variables as outputs from one script into another.
I can do all this through powershell alone, but wondering if there's a more visual way to set up the complex interactions between scripts.
An example of a process that would need to be completed:

SQL backup taken on hosted client server
Backup uploaded to FTP Site
Backup loaded to data scrubbing server
Backup restored to local SQL Instance
Client sensitive info scrubbed
Backup taken and uploaded to NAS
NAS backup restored to local support environment
Database modified to add users for WinAuth
Various server config on support environment for our software


texts: Strange behavior of TOP 1 clause sql server

texts:
I have a SQL Server database and restored it on another server on a different machine. Now I run following same query on both DBs which produces same below result set.
select person_id, visit_id, verification_code, mobileNo, created_date
from person
order by verification_code, created_date DESC

But when I add TOP 1 clause to the above query, original DB shows the 2nd row (ignores first row)
select TOP 1 person_id, visit_id, verification_code, mobileNo, created_date
from person
order by verification_code, created_date DESC

while restored DB shows first row

I thought it might be due to different collation, but I checked both servers have same collation.


texts: oracle sqlcl and sqlplus - seperate login.sql on linux

texts:
I feel I'm probably missing something obvious here, but I can't see an easy way to have a separate login.sql for sqlplus and sqlcl
This is problematic for me as the errors thrown up by sqlplus for the set commands it doesn't understand interfere with various scripts.
I think I could do it with aliases that change the SQLPATH prior to starting sqlcl, but would prefer a cleaner way if possible.
Is there a separate file name that only sqlcl will look for? Or separate location?


texts: RDS Mysql: Parameter to kill idle transactions

texts:
From time to time I see some long-running idle transactions in my MySQL database. I need to set timeout for such transactions. Therefore, I want to know if there is something like PostgreSQL's idle_in_transaction_session_timeout parameter in amazon RDS too.
Percona seems to have something similar here.


texts: Need Help Finding a Replacement for sys.sysprocesses

texts:
The SQL 2016 Data Migration Assistant (Upgrade Advisor) is saying that I need to stop using old system table references.  Namely, it wants me to stop using sysdatabases and sysprocesses.

I have some code that's logging session information based on data from sysprocesses.  Included in that log is the database ID of the spid (session_id).

I have been searching online and have found only two possible replacements, neither of which work fully due to reasons explained below.

Option 1 (sys.dm_tran_locks) - This option seems to work rather reliably if I link back to sys.dm_exec_sessions but I noticed that it will never return a result if the session_id's database context is master or tempdb.  That means I will not be able to reliably fetch the resource_database_id for any session_id that is using master or tempdb.  I also cannot just assume it since there are at least two possible contexts to choose from.

Option 2 (sys.dm_exec_requests) - This option only works on session_id's that are actively running.  Idle/suspended session_id's do not appear in the list.

If I cannot get the dbid for session_id's that are suspended and/or are using the master database context then I will have a gap in my logs.

Is there any other way to reliably fetch this information without the use of sys.sysprocesses?


texts:
I am using MySQL 5.5. For some reason, I need to change the date of my system to after 2038. Unfortunately, the MySQL service won't start after the year 2038:

root@sd-84962:~# date
Sat Sep 24 18:41:32 CEST 2039
root@sd-84962:~# service mysql restart
stop: Unknown instance:
start: Job failed to start
root@sd-84962:~# date -s "Tue Sep 24 18:37:56 CEST 2015"
Thu Sep 24 18:37:56 CEST 2015
root@sd-84962:~# service mysql restart
stop: Unknown instance:
mysql start/running, process 22123

I do not have any tables in MySQL.
I am running Ubuntu 14.04 on a 64 bits system.

If I start mysqld manually, I get:

root@sd-84962:~# /usr/sbin/mysqld
610924 18:59:16 [ERROR] This MySQL server doesn't support dates later then 2038
610924 18:59:16 [ERROR] Aborting

I am using MySQL 64 bits:

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.5.44, for debian-linux-gnu (x86_64) using readline 6.3

What would be the workaround?


texts: Bi-directional synchronization between local SQL Server 2005 and a SQL Azure using SQL Data Sync

texts:
I need to synchronize an Sql Server 2005 database in an SQL Azure database. In other posts I've read that I can do this using Sql Data Sync but I don't know if SQL Data Sync can make the synchronizations that I need without exceeding their limitations. This is the exact process I need to do:
First: synchronize 1 table from Azure to Sql Server.
Second: Execute some Store procedures in the SQL Server 2005 instance.
And third: Synchronize various tables from Sql Server to Azure sequentially.

Thanks!


texts: How are these tempdb tables automatically created after each SQL Server restart?

texts:
I have a session state management implementation using SQL Server. There is an ASPState database, and ASPStateTempApplications & ASPStateTempSessions tables in the tempdb database.

When I restart the SQL Service, tempdb database recreates, but these tables recreate too. I want to know: how do these tables recreate automatically each time?

I looked for start-up parameters and jobs, but did not find any.


texts: Designing SQL database with two types of users

texts:
So I'm trying to design a Postgresql database with two types of users.

Customers and Brands

A Customer cannot be a brand and a brand cannot be a customer.

Is this the right approach? I feel I may have some challenges when querying data.


texts: Is it possible to generate workload for hammerora (hammerdb for oracle) from AWR tables?

texts:
I'm trying to generate workload from our production databases but rather than running a trace file in production, I would prefer using the AWR tables since we have a license for it.

Is it possible?

Thank you.


texts: Cannot use 'STWithin' as it can't be found for type 'Microsoft.SqlServer.Types.SqlGeography' (SQL Server V14)

texts:
I am currently using the STIntersects method to spatially match a geographic point to a geographic area. I am working with Geography types here not Geometry.
Historic code from the place I work shows that STWithin was previously used to carry out this operation.
From my googling, it appears that STIntersects will return a match if the point lies in or on the boundary of an area but that STWithin will only return true if the point is inside the area.
I want to try both methods, look at the results and fully understand exactly how they affect what we are trying to do with the data.
My problem is that when I try to use STWithin, e.g.
SELECT SourcePoint.STIntersects(TargetArea) FROM Table1

SELECT SourcePoint.STWithin(TargetArea) FROM Table1

...the STIntersect query works but the STWithin query gives:
Could not find method 'STWithin' for type 'Microsoft.SqlServer.Types.SqlGeography' in assembly 'Microsoft.SqlServer.Types'.
I am using SQL Server V14 (2017) on a remote server. I've tried using a local SQL V15 server and I get the same result.
The Microsoft documentation seems to suggest it is available for SQL Server 2017:
https://docs.microsoft.com/en-us/sql/t-sql/spatial-geography/stwithin-geography-data-type?view=sql-server-2017
Can anyone suggest where I might be going wrong here?
Thanks


texts: How do I ensure I always connect to Primary/Secondary node in a MongoDB ReplicaSet?

texts:
I have a 3 node replica set running on 3 different servers. I have finished setting it up and everything works great. Now I want to give my users two connection strings:

Connect to this "xyz" for write
Connect to this "abc" for read only traffic

I am also trying to set up a CNAME for each of the connections, to be able to have the flexibility to change in the future.

Thanks and appreciate your time in advance.


texts: Working on a scenario where two entity types have two kinds of relationship, one of which is ISA-to-ISA

texts:
I am a student of a university, and my teacher thinks my entity-relationship diagram (ERD) is weird. The ERD is about a ramen restaurant.

The order receipt looks like this:

Tofu Ramen         $3
Topping:

Meatball        $1
Kerupuk         $1

Tofu Ramen         $3
Topping: none

You can see, there is detail that has detail, in other word, orderitem has topping.

Let me show the ERD:

*ISA is the generalization.
* minuman=drink

This is the output schema:

The question:

The relation added is the problem. Is it correct or I am wrong? Please correct me if it wrong.

Pardon my English.


texts:
Does PostgreSQL support generated columns? Also know as virtual columns. I am not talking about IDENTITY columns.

I can’t find any information on this remarkable feature but I know that it is available on SQL Server, and in the latest versions of MariaDB & MySQL.

The feature is mentioned in the SQL:2003 standard, and there was some discussion on the PostgreSQL forums around 2006, but I can’t find anything substantial on the matter.

There is some discussion on SO, but it is quite old now, so it may well be out of date.


texts: Compare Two Columns, Remove Duplicate Characters

texts:
Example table:

  ID      CA      CB
  ------  ------  -----
  1       龍      龍 竜 龒
  2       齒      歯 齒
  3       黽      黽

I would like to modify column CB so that it doesn't contain any of the characters in CA.
Like this:

  ID      CA      CB
  ------  ------  -----
  1       龍       竜 龒
  2       齒      歯
  3       黽

(SQLite)


texts: Entity Relationship to One of Several Other Entities

texts:
I'm building a database model that requires one table to have a relationship to only one of several other tables. For example, a Contract may be associated with a Person, a Business, or a Government, but only one of those.

Other than having a foreign key column for each of those and using a check constrain to ensure only one is populated, what is the best approach to this situation?


texts: Querying Cassandra without a partition key

texts:
I read Cassandra's documentation on the internal steps it performs when  querying data. It looks like Cassandra relies on the Partitioner and Replication Strategy to process queries. I am still confused that the Partitioner needs to know the Partition Key. If the query has the Paritition Key, the internal query process looks straightforward. However, if the query expects a result set instead of a deterministic row like below.

SELECT * FROM <table>

In this case, when there is no Primary Key specified in the WHERE clause, how does the Coordinator know which nodes to send the requests to?
If multiple rows are returned, which may be distributed in different nodes, how are these rows aggregated and returned to client?


texts: Should I Avoid Repeated References to Aggregate Columns in a View?

texts:
So I'm wanting to summarize the balance of the accounts in my business application.  My (simplified for our purposes) data model looks like:

--Main account table that holds the single accounts
create table account (
id int primary key,
descr varchar(50))

--This table holds the budgeted amount for the account.  This table also holds the
--'actual' adopted amount.  The difference is that budgeted is what they thought it
--should be, while adopted is what the board accepted.  They can be different
create table account_amount (
id int primary key,
acct_id int foreign key references account (id),
amount money,
type tinyint)

--Each monitary action against an account is contained here.  This is the transactional
--table
create table account_journal (
id int primary key,
acct_id int foreign key references account (id),
amount money)

I want to create a view to summarize the balance for me, so I've got something like this:

create view account_balance as
select a.id, isnull(aaBudgeted.amount,0) as budgeted, isnull(aaAlloted.amount,0) as alloted
, sum(isnull(aj.amount,0)) as journaled_activity,
, isnull(aaAlloted.amount,0) + sum(isnull(aj.amount,0)) as balance
from account a left join acct_amount aaBudgeted on a.id = aaBudgeted.acct_id
and aaBudgeted.type = 2
left join acct_amount aaAlloted on a.id = aaAlloted.acct_id
and aaAlloted.type = 1 and aaAlloted.is_current = 1
left join acct_journal aj on aj.acct_id = a.id
where a.fiscal_year = 2012
group by a.id, aaBudgeted.amount, aaAlloted.amount

My question has to with the:

isnull(aaAlloted.amount,0) + sum(isnull(aj.amount,0)) as balance

line above.  Do I want to be referencing an aggregate column twice like this?  Is SQL Server 2k (don't get me started) smart enough to NOT sum the values twice?  What are my options?


texts: Partial SQL Server database synchronization

texts:
I have 2 SQL Server databases and they have somewhat different schemas.
But there is a table with person attributes.
Name, address, city, email, etc...
And an account key.  on both tables.
Can SQL Server be set up to sync just these attributes.  On this table?
And not the entire database?
And, is there a way to log when data is overwritten from db1 to db2.  or db2 to db1?


texts: Different syntax to add/substract interval

texts:
Till now I used the following syntax to add an interval to a timestamp:

select now() + '5 year';

This worked fine till I tried to subtract the interval which results in a syntax error.

invalid input syntax for type timestamp with time zone: "5 year"
LINE 1: select now() - '5 year'

In the documentation I learned that the syntax actually is:

select now() - interval '5 year'

So my questions are:
Why does select now() + '5 year' work at all?
Does it work only by accident and it might break in a future Postgresql release?


texts: how to implement read-only rows or snapshots of rows

texts:
In my application I have objects with 3 levels of hierarchy. To model this in SQL Server, I have a ParentObject table with an Id column, ChildObject table with an Id and a foreign key ParentObjectId, and a GrandchildObject table with an Id and a foreign key ChildObjectId. A ParentObject may have many ChildObjects and a ChildObject may have many GrandchildObjects.
At any point in time, users would like to take a snapshot of the current state of a specific ParentObject. A snapshot of a ParentObject may not be modified after it has been created but the snapshot ParentObject can be deleted. The ParentObject snapshot needs to retain the entire object hierarchy, i.e. include ParentObject, ChildObjects, GrandchildObjects. There can be multiple snapshots of a particular ParentObject. All snapshot ParentObjects and the current version of ParentObject must be SELECTable. How can this be implemented using SQL Server 2019?
Users do not have direct access to my database. Instead they call a REST API which has its own account to access my database. Since there is only 1 account accessing the database, I don't think Row-Level Security can fulfil my requirements.
One implementation I have considered is to have a [ParentObject].[SnapshotTime] DATETIME2 NULL column and for the 3 object tables to each have a [IsReadOnly] BIT NOT NULL column. When a user wants to snapshot a ParentObject, I copy the ParentObject row to a new row, setting newRow.SnapshotTime = currentTime and newRow.IsReadOnly = 1. I then cascade copy the ChildObjects and GrandchildObjects, setting the new rows's IsReadOnly = 1. When an UPDATE or DELETE is requested, I check whether a row's IsReadOnly = 0 before performing or denying the action.
Is this a sensible approach? Are there any built-in SQL Server features to achieve my aims? Are there better/faster/more efficient approaches?


texts: Alter table add column like another column syntax

texts:
I was looking into MySQL specification, but couldn't find any solution to my problem. I want to clone specific columns in several tables like this:

ALTER TABLE `table` ADD COLUMN `new_column` LIKE `column` AFTER `column`;

Of course this gives me syntax error: right syntax to use near 'LIKE `column` AFTER `column`', what i was expecting. Is there any possible solution to this or i have to specify directly a new column? I know that "cloning" table structure using LIKE operator is possible. Is it also possible in this case?


texts:
I restored a SQL Server 2000 database to 2005 then to 2012. I am not able to see system tables that existed in SQL Server 2000 now.

Why?


texts: Migrate from SQL Server 2014 Failover Cluster to 2019 Availability Group, rollback plan

texts:
we have a 2 node SQL Server 2014 failover cluster with 2 SQL Server instances and about 100 databases to be migrated to an availability Group 2019. What is the faster way to do that considering that we have one weekend for any instance? What's the best rollback strategy considering that restore from a recent version to a older one is not possible?
Any idea?
thanks


texts:
I have a db2 instance, and I need to stop it properly, clean, without any db2stop force. I need this in order to restart it fast in a batch process.

I am completly sure there are not connections or active databases; however, the instance does not stop. Attached, there is an image that shows this behaviour.

What can I do? what is using the instance that produces the SQL1025 error?


texts:
I write a DB LISTENing program something like as shown here (Example 3)==> https://www.postgresql.org/docs/9.1/static/libpq-example.html

I have a problem with pg_notify such that;

NOTIFY channel

==> works fine, C program catches the signal

whereas

SELECT pg_notify('channel','This is a test');

==> Program can't catch anything.

Any idea?
Note: My Version is PostgreSQL 10.5


texts: Delete one in two rows in an ordered result set


texts: Update values in one table based on contents of another

texts:
Software Name: Oracle SQL Developer Version 17.2.0.188

I have three tables: orders, order_details, and products.

I wanted to run a query to update units_in_stock in the products table based on quantity in order_details, where the associated orders row has been marked as shipped, and has a shipping date.

Here's what I tried:

UPDATE Products SET (Products.Unit_In_Stock = Products.Unit_In_Stock - Order_Details.Quantity)
INNER JOIN (
    Orders INNER JOIN Order_Details ON Orders.Order_ID = Order_Details.Order_ID
    ) ON Products.Product_ID = Order_Details.Product_ID
WHERE (
        ((Orders.Shipped_Date) IS NOT NULL)
        AND ((Orders.This_Order_Was_Shipped) = "YES")
        );

When I run this, I get:

Error at Command Line : 1 Column : 45

Line 1, Column 45 is the equal sign in the very first statement that said

Products.Unit_In_Stock = Products.Unit_In_Stock - Order_Details.Quantity

Can you help me get this working?


texts: Having four different tables or having one table with one more field?

texts:
I have a website with 4 translation (That is Arabic with 4 different Persian translation), and I had exported each of these translations to different tables.
Which one of these ways are faster and better:

1- Merging or Importing all of this translation (tables) to one table,
and name it nahj_all and adding a flag field to table to clarify
what translation is it (for example flag with numbers 1, 2, 3 and
4).

OR

2- Importing each translation to different tables and get diffrent
name to them according to translation name (or number), for example
nahj_feiz, nahj_jafari, nahj_dashti, nahj_shahidi

I have these tables in MySql and I want to convert database to sqlite
Which one is better for sqllite?
Which way is better for MySql?


texts: Which MySQL files in InnoDB are made of pages and how are they structred?

texts:
Definition of InnoDB page is here : https://dev.mysql.com/doc/internals/en/innodb-page-structure.html

  InnoDB stores all records inside a fixed-size unit which is commonly called a "page" (though InnoDB sometimes calls it a "block" instead). Currently all pages are the same size, 16KB.
  A page contains records, but it also contains headers and trailers. I'll start this description with a high-altitude view of a page's parts, then I'll describe each part of a page. Finally, I'll show an example. This discussion deals only with the most common format, for the leaf page of a data file.

And based on their documentation, database tables and their tuples are stored in pages, for example a part of each page contains the row data which is the tuples

what i don't understand is :

which files inside the mysql folder are made of these pages? only the .ibd files or..? and do these files contain any meta data at the beginning of them to show info about these pages and their addresses?
when i want to read a particular row, lets say with the PRIMARY id of 1, what are the files that DBMS reads and which parts of them? I'm asking about the DBMS interaction with files, i want to know for example does it first read the .frm file to know the metadata of that table then reads the .ibd file of that table? and then goes down in the B+tree aka that .ibd file and finds the correct page in the clustered Index? or am i wrong?


texts: Combine a running balance SQL statement as a column

texts:
I have two Select Statements which are collecting data from 1 table, one is a totals select statement. Could anyone assist me with combining them into 1 statement? Any assistance is appreciated

Statement 1

SELECT ClientId, ExternalBank, PlacementDate
FROM tblDepositTransactions where ID = 15

Statement 2

SELECT Sum(EffectiveAmount) AS RunningBalance
FROM tblDepositTransactions WHERE ID = 15 GROUP BY ID


texts: SQL Server Version - are hotfixes for previous versions implied?

texts:
tl;dr: If I identify an SQL Server as having build X, and find out that X implies hotfix Y, does it also imply that all hotfixes listed for versions below X are installed? Or that only the hotfix for build X is installed?

I found the following list of Microsoft SQL Server version numbers and their meaning in terms of service packs, hotfixes, product versions, etc.:

SQL Server Version Database @ sqlsecurity.com

My question is best illustrated with an example, an excerpt from that list:

2005 RTM+Q915793        9.00.1502  <-- interested in this
2005 RTM+Q910416        9.00.1500
2005 RTM+Q932557        9.00.1406

In the above list, if I find out that the SQL Server I am connected to is version 1502, does this imply that:

only hot-fix Q915793 is installed?
or that all of hot-fixes Q915793, Q910416 and Q932557 are installed?

ie. does the build imply that all previous hotfixes are installed?

Follow-up bonus question: Is this a fixed pattern for all the versions, or are there exceptions to the rule?


texts: Query Linked Access database on network share via SQL Job Agent

texts:
I have an MS Access database on a shared network location which I've added as a linked server to my SQL Server instance.  I'm authenticated on the network location via the server's main Windows login.

I can run the stored procedure normally via SSMS on the server, which works perfectly.  However, when I try and run the job through SQL Job Agent, it fails, I believe due to the fact that Job Agent runs under the Local Service account.

How can I allow only this one job to run under the Windows account without having to change this for all the other jobs?  And should I even be doing it this way from a security standpoint?


texts:
Please Help...
What are the best RAID configuration for MS SQL server.
High availability, redundancy and recovery are require.
All I need is best performance. cost is not a factor for me but a 500-800  concurrent hits would be there.


texts: Run a stored procedure only the last 3 days in a month

texts: How can I run a stored procedure only the last 3 days of a month using a T-SQL script?


texts: Different Maximum server memory across Availability Replicas

texts:
I have a scenario in SQL Server 2014 AlwaysOn High Availability where other services are running in one of the Secondary read-only replicas.

These services obviously demand additional memory. Is there any good practice or specific reason why SQL Server should have the same memory configuration across different replicas?


texts: Performance of searching for hex strings in large tables (using LIKE, Full-Text Search, etc.)

texts:
I have a table with 40+ million rows in MS SQL Server Express 2019. One of the columns store pure hexadecimal strings (both binary and readable ASCII content). I need to search this table for rows containing a specific hex string.

Normally, I would do this:

SELECT * FROM outputs WHERE outhex LIKE '%74657374%' ORDER BY id DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

Since the results are paginated, it can take less than a second to find the first 10 results. However, when increasing the offset, or searching for strings that only appear 1-2 times in the entire table, it can take more than a minute, at which point my application will time out.

The execution plan for this query is this:

Are there any easy ways to improve the performance of such a search?

I have tried the following:

Create an index on that column. This has no noticeable effect.
I came across this article about slow queries. Initially, I was using parameterized queries in my application, which was much slower than running them in SSMS. I have since moved to the query shown above, but it is still slow.
I tried to enable Multiple Active Result Sets (MARS), but without any improvement in query time.

I also tried using Full-Text Search. This seemed to be the most promising solution as text search is exactly what I need. I created a full-text index and can do a similar query like above, but using the index:

SELECT * FROM transactionoutputs WHERE CONTAINS(outhex,'7465') ORDER BY id desc OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

This returns results almost instantly. However, when the query is longer than a few characters (often 4), it doesn't return anything. Am I doing something wrong or why is it doing that?

The execution plan:

My understanding is that my case is not the ideal use case for FTS, as it is designed to search in readable text and not hexadecimal strings. Is it reasonable to use anyway?

After reading dozens of articles and SO posts, I can not confidently say I know how to improve the performance of such queries for my specific use case, if it is even possible at all. So, is there any easy option to improve this?

Edit: Using this answer, I was able to reduce the query time from 33 seconds to 27 seconds:

SELECT * FROM transactionoutputs WHERE
CHARINDEX('74657374' collate Latin1_General_BIN, outhex collate Latin1_General_BIN) > 0
ORDER BY id DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

When I leave out the ORDER BY and pagination, I can reduce this to 19 seconds. This is not ideal because I need both the ordering and pagination. It still has to scan the entire table


texts:
Server: MySQL 5.1.66-0+squeeze1

My problem:

I have two databases with the same tables. One is for the testing environment and one for production. When I perform a MATCH AGAINST on the prodution table (let's call it prod), I get the correct result. When I do the same on the test table (let's call it test), I get thousand of results, that shouldn't match.

Things get even worse. I copied the table from the production database to the test database. Still everything fine in the table.

My steps trying to resolve the problem:

So I though it might be the table definition. I truncated the prod table and than inserted the data from test (INSERT INTO test SELECT * FROM prod). The same problem.
I repaired the table (even with USE_FRM), same problem
I dropped all indexes and added them again, same problem

Now it is getting really strange:

I copied the data from both tables into one table (giving the new rows new auto incremented IDs), problem solved, but duplicate rows, as expected. I than removed the rows from prod -> same problem!

So it must have something to do with the data. Trying to find the "broken row" I deleted 100 rows in each step, trying to find the one causing the issue. After hundreds of tries, I though, that I have found the one row. But after filling all data into the table again and deleting only this single row, the problem still exists.

So I really need you help as I have absolutely no clue what is going wrong and how I can solve the problem. At the moment it's only in the test table, which wouldn't be that dramatic, but it might also occur in the prod table in the future.

UPDATE:

OK, I think the real problem is, that the MATCH will return everything with a score larger than 0.2 using a query like this:

SELECT  MATCH(col1, col2, col3, col4, col5, col6) AGAINST ("word1, word2") AS score
FROM    table
WHERE   MATCH(col1, col2, col3, col4, col5, col6) AGAINST ("word1, word2")

In my table, the word2 might match exactly the value of col6 and is included in col1. But the query also returns rows that does not have word1 in any cols. Those rows have a score from approx. 0.2 to 0.6 and all rows with both words in any cols have a score from 8 and more.

So filtering this score for values greater than 1 does fix the problem. But how is the MATCH  AGAINST defined? Why does it return rows with a score lower then 1?

UPDATE 2:

In prod I only get a score for three rows (which are the ones, you should expect for the search) of approx. 7.3 and higher, all other rows get a score 0. In test approx. 40% of rows get a score larger than 0 but only the three positive rows get a score larger than 1.

So a possible solution would be using a query like the following (filtering scores lower than 1 out):

SELECT  *
FROM    table
WHERE   MATCH(col1, col2, col3, col4, col5, col6) AGAINST ("word1, word2") > 1

But is this really the right solution for the problem?


texts: In mongodb cluster, Can I shut down one config server and one data server in the shard at the same time

texts: I have one mongodb cluster, including 3 config servers, and one shard (3 data servers in this shard). One data server and one config server are on the same machine. Can I shut both of them down to upgrade the cluster without stop the service?


texts: Mongodb migration and appending data to existing database

texts:
I have two databases in two different machines containing data in same schema. Both have relational mappings in form of ObjectIDs as it is done in NoSQL database. The task at hand is to pick one database from machine A and append that data to another database residing in the machine B. If I simply insert the data there will be ObjectID collisions and it simply won't work.
The only option which I see is to insert the primary key collections first in the database, then insert the collections containing the foreign key mappings and replace the ObjectIDs with the newly created ObjectIDs from the primary key documents. However, it isn't as easy as it sounds because there are multiple collections which are interconnected with ObjectIDs.
Please suggest me the most efficient way in which this can be done. TIA


texts:
I've a backup table with several distinct IDS... I wanna do a BACKUP of those rows, by ID.

The function has to be executed by a limit of account_id ( select dump(21); - Where 21 = number of account_id to be touched by the function )
Each account_id must have a different file name. Examples below:

Example:

COPY
(
SELECT * FROM backup_table WHERE id = 1112 AND status = 1
)
TO '/var/lib/pgsql/1112.sql';

COPY
(
SELECT * FROM backup_table WHERE id = 1113 AND status = 1
)
TO '/var/lib/pgsql/1113.sql';

COPY
(
SELECT * FROM backup_table WHERE id = 1114 AND status = 1
)
TO '/var/lib/pgsql/1114.sql';

Question:

Can I create maybe a function to do that automatically? There are thousands of those IDS and would take ages by doing manually.

UPDATE 1:

Tried to do:

CREATE or REPLACE FUNCTION dump(integer)
RETURNS integer AS $$

declare
   crtRow record;
begin
      FOR crtRow in execute 'select account_id from backup_table WHERE migrated = 1 AND account_id = '|| $1

      LOOP
    COPY
        (SELECT * FROM backup_table WHERE migrated = 1 AND account_id = crtRow.account_id)
    TO '/var/lib/pgsql/backup/%s.sql';
    end loop;
    return integer;
end

$$ language 'plpgsql';

Each account_Id would have a file with its ID
When calling the function, I want to specify the numbers of account_Ids I wanna do the dump

However it isn't working...

UPDATE 2:

CREATE or REPLACE FUNCTION function_1(rows integer)
RETURNS void AS $$

declare
   crtRow record;
begin
      FOR crtRow in EXECUTE 'select DISTINCT(account_id) from backup_table WHERE migrated = 1 AND account_id IN '|| $1

      LOOP
    COPY
        (SELECT * FROM backup_table WHERE migrated = 1 AND account_id = crtRow.account_id)
    TO '/var/lib/pgsql/' || crtrow.account_id || '.csv';
    end loop;
end

$$ language 'plpgsql';

Error:

  ERROR:  syntax error at or near "||" LINE 12:     TO '/var/lib/pgsql/'
  || crtrow.account_id |...

UPDATE 3: amacvar's answer

CREATE or REPLACE FUNCTION function_1(rows integer)
RETURNS void AS $$

begin
execute 'COPY
(
SELECT * FROM backup_table WHERE id = ' || rows || 'AND status = 1
)
TO ''/var/lib/pgsql/'||rows||'.sql''';

end

$$ language 'plpgsql';

The above function based on the amacvar's answer works. The problem is that I have to specify the id number...

Example:

`select function_1(25)` - Where 25 = id

That isn't good for me, as I have thousands of ids and can't specify them manually.

  What I need is:

  The same function above, but with a LIMIT of IDS to be specified when
  the function is called:

EXAMPLE:

select function_1(100) - WHERE 100 = numbers of id to be copied

How can I do that?


texts: Saving into a VARCHAR(MAX) throwing error in some scenarios

texts:
We are noticing an error like this:

InterfaceError: ('HY104', 0, '[Microsoft][SQL Server Native Client 11.0]
Invalid precision value', 10226)

It happens when we insert a certain length of characters along with Unicode content like \u3000 (Ideographic Space). This happens only we are using the Windows MSSQL Driver and the query is parametrized.

The column is a VARCHAR(MAX). So it should support 2GB.

What could be the reason for this?
Why is it that length matters?

If I just insert this Unicode character alone, it won't cause an issue!

I was trying to see this from a theoretical point of view. Switching to NVARCHAR is not an option for us currently, but removing Unicode is.

Here are some more details as per the questions asked by Solomon Rutzky:

Column Collation is SQL_Latin1_General_CP1_CI_AS
String length where I start seeing the error is 4019.
sqlalchemy.connectors.mxodbc, line 86, in error_handler is where I get the initial error. This is from the traceback.
It works fine with single Unicode character.
A 20000 character string without Unicode works fine.
It makes it ASCII from when I try this from this software (Web App). I tested in Dbeaver by exporting the data.


texts: Modeling a graph of different node types and edge types using SQL Graph

texts:
I am looking into using SQL Server 2017's SQL Graph feature to model a graph that has different node types and edge types, but am confused as to how I would model this. In short, a user can create a project that is a collection of connected nodes. There are different types of nodes, where each type of node has some attributes in common (say 5 or so), but has many unique attributes (say 10-25).

Moreover, the nodes are connected by edges, but there are different types of edges that can be used to make the connection, where (like nodes) each type of edge has a few attributes in common, but many more unique attributes.

For simplicity's sake, imagine that there is just one type of edge and only two types of nodes: NodeA and NodeB. My initial thought is that I'd:

Create two node tables
Create one edge table

E.g.:

NodeA (AS NODE)
---------------
ID, PK
ProjectID, FK
NodeA_Attribute1, string
NodeA_Attribute2, string
...

NodeB (AS NODE)
---------------
ID, PK
ProjectID, FK
NodeB_Attribute1, string
NodeB_Attribute2, string
...

Edge (AS EDGE)
--------------

If the user has a project that has two nodes - one NodeA and one NodeB that are connected, then the INSERT statements would be like so:

INSERT INTO NodeA(ID, ProjectID, Attribute1, Attribute2) VALUES(1, 123, 'I'm a NodeA', '...')
INSERT INTO NodeB(ID, ProjectID, Attribute1, Attribute2) VALUES(1, 123, 'I'm a NodeB', '...')
INSERT INTO Edge VALUES((SELECT $node_id FROM NodeA WHERE ID = 1), (SELECT $node_id FROM NodeB WHERE ID = 1))

But here is where I am lost... How do I reconstruct the graph for this particular project? I could query each possible node table, e.g.:

SELECT * FROM NodeA WHERE ProjectID = 123
SELECT * FROM NodeB WHERE ProjectID = 123

But that doesn't seem practical or efficient. My project in reality has ~30 different types of nodes, so running 30 SELECTs (and having to add new SELECTs if a new node type gets added) seems incorrect.

It feels like I'm missing something fundamental here. Is my proposed design incorrect? Are there different ways of querying the SQL Graph that I'm overlooking?

Thanks


texts: Why set `group_concat_max_len` below the maximum?

texts:
MySQL 5.5.28 on Ubuntu 12.04

If the result is longer than group_concat_max_len then the result is gracelessly truncated.

Currently I have a script that tries to check the required length ahead of time and sets group_concat_max_len to be large enough.

But the checking adds extra queries. Is there any downside to just setting group_concat_max_len to the maximum value? The upside is fewer queries.


texts:
Supposing we have a table with four columns (a,b,c,d) of the same data type.

Is it possible to select all distinct values within the data in the columns and return them as a single column or do I have to create a function to achieve this?


texts:
I have two tables:

Table A:
a_id, a_href, a_name,......

Table B:
b_id, b_href, b_name......

Both tables have indexes on their href column. I intend to union these tables and join them on another, something like

Select c_id, u.name FROM TableC
Left join (Select a_href AS href, a_name AS name FROM TableA
           UNION
           SELECT b_href AS href, b_name AS name FROM TableB) AS u ON u.href = c_href

I want to preserve the index on href columns in the union, so my join is fast. Do I have to dynamically create an index for that union, or is there a better way to do this?


texts: Update record with converted epoch timestamp from another column

texts:
I'm trying to convert epoch data in one column (timestamp_epoch) to a legitimate timestamp in another column (timestamp) for every record in a table.  In reading the PostgreSQL Documentation it appears I can do this using the to_timestamp() function, but I get an error indicating this function does not exist.

UPDATE table
SET timestamp = to_timestamp(timestamp_epoch) AT TIME ZONE 'EST'
WHERE timestamp IS NULL;

Server is a PostgreSQL 12.1 and I'm using pgAdmin4 to run the query.  When the query runs, I get an error indicating the function doesn't exist and that I might need to add explicit type casts... for which I'm lost on how to properly do if that is indeed needed.

ERROR:  function to_timestamp(text) does not exist
LINE 2: SET timestamp = to_timestamp(timestamp_epoch)
                        ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 56

Any help on converting the epoch data in one column to a legitimate timestamp in another column is appreciated.

Thanks!


texts: How to insert and or update a variable number of records and prevent concurrency issues

texts:
I currently have the following stored procedure. However I am seeing some race conditions where I end up with incorrect data which I think is caused because the procedure isn't attomic and it can be called multiple times resulting in overlapping data.

CREATE PROCEDURE [dbo].[data_save]
    @user INT ,
    @players VARCHAR(MAX) ,
    @captains VARCHAR(MAX)
AS

-- Clear existing records if any
DELETE FROM dbo.data WHERE user = @user

DECLARE @captainsTable TABLE (captainId INT);
INSERT INTO @captainsTable (captainId)
SELECT DISTINCT TOP 3 item FROM [platform].[SimpleSplit](@captains, ',')

INSERT INTO dbo.data
( user ,
  player ,
  iscaptain
)
SELECT @user
     , item -- this is what is returned from the SimpleSplit
     , CASE WHEN [item] in (select captainId from @captainsTable) THEN 1
         ELSE 0
       END
  FROM [platform].[SimpleSplit](@captains, ',')

I want to be able to execute this concurrently for different users but not for the same user.

The number of records for one user might be 4 at point 1 in time and then in point 2 the save might be executed with 6 records for instance. So just updating the existing records is not an option.

It's not a problem if the last call to the save procedure "wins" and overwirtes the previous ones but I don't want to end up with a combination of data from both. I am using sql server 2008.


texts: Restart event scheduler and alter event when slave being promoted to master

texts:
I am using innnodb cluster. It is good that every time a R/W node failed the R/O node will being promoted to R/W.

However I have to restart scheduler and alter event enabled (originally slave side disabled) manually for the cluster again. It pretty error prone and troublesome. I always have to run the script to manually fill the losing data for the time event is not running.

Is there anyway make it automized so I can be hands off?


texts: SQL equivalent to CouchDB for 2 way replication

texts:
Are there any SQL server variants (i.e MySQL, Postgresql etc) that handle replication as well as couch db does and offers client side replication (2 way)?

I'm in the early stages of my app which is currently using PouchDB / CouchDB as a key point about the app is being able to go offline then replicate when back online. I know the servers themselves can replicate but I'm basically looking to have an offline version of the database available to the client as well (which is what PouchDB / CouchDB achieves).


texts: Disaster Recovery Options for Sql Server

texts:
I wanted to see what suggestions you might have for Disaster Recovery Options based on the following criteria.

Currently running SQL 2012 standard edition
We have 18000 databases (same schema across databases)- majority of databases are less than 2gb-- across 64 instances approximately
Recovery needs to happen within 1 hour (Not sure that this is realistic)
We are building a new data center and building dr from the ground up.

What I have looked into is:

Transactional Replication: Too Much Data Not viable
AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances
Log Shipping is a viable option and the only one I can come up with that would work right now. Might be a management nightmare but with this many databases probably all options will be a nightmare.

Is there anything else that you can think of that I should look into?

Thanks in advance for any help provided.


texts: Access Denied while installing SQL server 2016

texts:
I am getting the below error while installing SQL Server, when I've selected the domain account for SQL server engine startup account and press next.

(11) 2020-02-16 14:58:48 Slp: Sco: Attempting to check if container 'WinNT://DOMAIN' of user account exists
(11) 2020-02-16 14:58:48 Slp: Prompting user if they want to retry this action due to the following failure:
(11) 2020-02-16 14:58:48 Slp: ----------------------------------------
(11) 2020-02-16 14:58:48 Slp: The following is an exception stack listing the exceptions in outermost to innermost order
(11) 2020-02-16 14:58:48 Slp: Inner exceptions are being indented
(11) 2020-02-16 14:58:48 Slp:
(11) 2020-02-16 14:58:48 Slp: Exception type: Microsoft.SqlServer.Configuration.Sco.ScoException
(11) 2020-02-16 14:58:48 Slp:     Message:
(11) 2020-02-16 14:58:48 Slp:         Access is denied.
(11) 2020-02-16 14:58:48 Slp:
(11) 2020-02-16 14:58:48 Slp:     HResult : 0x84bb0001
(11) 2020-02-16 14:58:48 Slp:         FacilityCode : 1211 (4bb)
(11) 2020-02-16 14:58:48 Slp:         ErrorCode : 1 (0001)
(11) 2020-02-16 14:58:48 Slp:     Data:
(11) 2020-02-16 14:58:48 Slp:       WatsonData = Domain
(11) 2020-02-16 14:58:48 Slp:       DisableRetry = true
(11) 2020-02-16 14:58:48 Slp:     Inner exception type: System.UnauthorizedAccessException
(11) 2020-02-16 14:58:48 Slp:         Message:
(11) 2020-02-16 14:58:48 Slp:                 Access is denied.
(11) 2020-02-16 14:58:48 Slp:
(11) 2020-02-16 14:58:48 Slp:         HResult : 0x80070005
(11) 2020-02-16 14:58:48 Slp:         Stack:
(11) 2020-02-16 14:58:48 Slp:                 at System.DirectoryServices.Interop.UnsafeNativeMethods.IAdsContainer.GetObject(String className, String relativeName)
(11) 2020-02-16 14:58:48 Slp:                 at System.DirectoryServices.DirectoryEntries.Find(String name, String schemaClassName)
(11) 2020-02-16 14:58:48 Slp:                 at Microsoft.SqlServer.Configuration.Sco.User.LookupADEntry()

I am installing SQL server 2016 STD version on Windows Server 2016 OS which is joined to a Domain controller.

Note:  I can install by selecting Local System as the startup account. later I can change through the configuration manager.  I can't do this workaround for Cluster installation.

Anybody faced this issue?


texts:
Good day, I created a AlwaysOn HA cluster in Azure, 2 Server 2012s both with SQL 2012  Ent updated and patched on the same subnet.. These are the guides I followed:

Tutorial: AlwaysOn Availability Groups in Windows Azure (GUI)
 Tutorial: Listener Configuration for AlwaysOn Availability Groups in Windows Azure

AZURESQL01 and AZURESQL02 are the nodes and with their powers combined they form CAPTAIN_CLUSTER01. There are 3 instances with 3 AG groups and 3 listeners and I opened the firewall ports.

We have a VPN to our AZURE and I can connect to AZURESQL01 and AZURESQL02 and all the instances over the VPN, but I cannot connect to the cluster or the listeners at all. I can do so from either AZURESQL01 or AZURESQL02, but not from outside or our AZURE network. All I get is  various Login timeout messages.

I have double checked the setup and the ports and the DNS entries, but I cannot connect.

Am I a moron and missing something really silly, or is there some configuration not covered in the installation tutorial that I am missing?


texts: How to set a correct value for wiredTiger concurrentTransactions

texts:
I have a single instance in server with 7 Processor and 15 Gb.
In value default for wiredTigerConcurrentReadTransactions and wiredTigerConcurrentWriteTransactions is 128.

But is value default, how to calculate the ideal value for ConcurrentTransactions ?

[Reference]

https://www.datadoghq.com/blog/monitoring-mongodb-performance-metrics-wiredtiger/

https://docs.mongodb.com/manual/reference/parameters/#param.wiredTigerConcurrentReadTransactions


texts: How to PIVOT data from MSSQL query and Fetch to PHP view

texts:
I have a MSSQL 2005 Database with query content like this :

nopas
code
name
order

00115301
D031
ADAM
1

00130015
D031
ADAM
2

00149998
D026
JAMES
1

00149970
D028
FIKI
1

00142641
D028
FIKI
2

00127700
D028
FIKI
3

For report reason, I need Output a table like :

ADAM
JAMES
FIKI
...(dynamic content based new record)

00115301
00149998
00149970
...

00130015
-
00142641
...

-
-
00127700
...

Can anyone help me??


texts: Identify Queries causing High Average Wait time (SQL Server 2008)

texts:
Recently we started to receive many alerts from SCOM : Alert: SQL 2008 DB Average Wait Time is too high.

When reviewed the graph for this counter, we found that the alert is always ranging between 1,000 (ms)  and  2,500  (ms) .

is there a specific way\query that can identify which queries are causing this locking which lead to the high wait time ?

Thanks
Osama Waly


texts: MongoDB : How to estimate max number of connections supported?

texts:
We have a sharded cluster with 2 replica sets. Each replica set is a 3 member cluster with one primary and 2 secondaries. Each instance is a r3.large (aws-ec2) instance with 15G ram.

mongos's are in app servers and can have minimum 3 and can auto-scale to 10. Is there a way to find out number of max connections supported by sharded cluster. That means number of connections between mongos and cluster. (Like For MySQL max connections are based on size of RAM)

mongo version - 2.6.8


texts:
I'm trying to enable slow_query_log on a Mariadb 10.1.32 server.
When slow_query_log is enabled & flushed, logging is successful (slow queries are recorded and written to the file) for a minute or less, before becoming disabled again. By disabled I mean the slow_query_log variable has been set to OFF.
I can't find any indication in logs (mysql/syslogs) as to what might be disabling the slow_query_log.
Any clues appreciated.
options

| log_output          | FILE                                 |
| slow_query_log_file | /var/log/mysql/mariadb-slow_test.log |

Note slow_query_log_file per my configs is set to /var/log/mysql/mariadb-slow.log; and I had changed to this new 'test' file as part of debugging. I see the same behaviour regardless of file.
We're running the database locally; Debian Jessie.
root@xxxxx:/var/log/mysql# ls -l /var/log/mysql/mariadb-slow_test.log

-rw-rw---- 1 mysql adm 449 May 25 09:40 /var/log/mysql/mariadb-slow_test.log

These configs are set in my.cnf since the last restart:
root@xxxx:/var/log/mysql# cat /etc/mysql/conf.d/mygalera.cnf | grep slow
slow_query_log              = 1
slow_query_log_file         = /var/log/mysql/mariadb-slow.log

Long query time:
MariaDB [(none)]> SELECT @@global.long_query_time\G
*************************** 1. row ***************************
@@global.long_query_time: 10.000000

I pointed the file back to the default (/var/log/mysql/mariadb-slow.log), flush & enable, it logged again until 11:50:
 - -rw-r-----  1 mysql adm   861 May 25 11:47 mariadb-slow.log
 - -rw-r-----  1 mysql adm  2.1K May 25 11:50 mariadb-slow.log

This makes me think there is something scheduled that is disabling it, however event_scheduler is off and nothing in cron.


texts: Determing when a stored procedure was last compiled

texts:
Is there a way to find when a procedure last had an execution plan compiled in Sybase ASE 16?

I don't see a "modified" datetime column in sysobjects, and I haven't been able to find a Sybase equivalent to a queryable plan cache to see if that has a date.


texts: How to efficiently design MongoDB database for Uber-like application?

texts:
I'm designing a MongoDB database for an Uber-like application, but since I'm new to NoSQL concept, I have several doubts.

I have three main collections - users, requests and messages. User can post a ride request, and if other user wants to give him a ride, accepts such a request and now text communication between those two users is started.

So, in SQL world I would have the following tables:

USERS
user_id
username

REQUESTS
request_id
request_details
gps_location
passenger_id
driver_id
timestamp

MESSAGES
message_id
message_body
request_id
sender_id
timestamp

But we're in NoSQL reality right now, and here things tend to look a little bit different. I know that I should forget about SQL mindset, about JOINs, and get familiar with embedded documents.

The question is - what's the proper way of designing the database in this case?

I could of course have such a design:

USERS
{
    "user_id": "001",
    "username": "John"
}

REQUESTS
{
    "request_id": "001",
    "request_details": "Chicago - NYC",
    "gps_location": [21.0,42.0],
    "passenger_username": "John",
    "driver_username": "Claire",
    "timestamp": "30-Mar-2016"
}

But there would be a data redundancy - passenger and driver usernames would exists both in Users and Requests model, and in case of username change, I would need to update all the requests for the given user. Using user_id instead of username could be a solution, but it would create a need for JOIN and in NoSQL we try to avoid it, don't we? And there are still messages left - how can we efficiently associate them with requests? I guess it's not a proper way and I need something else...

So maybe such a design?

{
    "user_id": "001",
    "username": "John",
    "requests": [{
        "request_id": "001",
        "request_details": "Chicago - NYC",
        "gps_location": [21.0,42.0],
        "passenger_username": "John",
        "driver_username": "Claire",
        "timestamp": "30-Mar-2016",
        "messages": [{
            "message_id": "001",
            "message_body": "Hi, how are you?",
            "timestamp": "30-Mar-2016-14-30-00"
        }]
    }]
}

But now several other things come to my mind. I will very often need to search for requests within a given radius and within a certain timespan, e.g. "show me all requests witin 10 miles not older than 24 hours", and for such a request I need to display all its details and username of the user who posted it. Actually, I will more often ask for requests than users (and each user will have more information, there will be not only his id and name, but also google/facebook username, photo, phone number, etc.). Isn't it a problem with this design?

Aren't all these collections (users/requests/messages) too coupled with each other?

What would be the best way to design a database here?


texts: Joining columns with null marks into one row

texts:
I used a sub select to get the data I want. I'm struggling to merge it all into one row now though.

This is an example of the result where its bike but there are loads on them:

bike    bike    null    null    bike    6       6
bike    bike    null    null    bike    null    null
bike    bike    2       2       bike    null    null

Any idea how I can merge or 'join', if you will, these rows together to act as one?

Output would ideally be bike 2 2 6 6.

The large data output above is literally the output of the query I ran.


texts:
I currently have backups that were taken with Percona XtraBackup of all my MySQL databases.

My specific scenario is that I want to restore a month old InnoDB database snapshot to my local machine for testing but I can't seem to find instructions for doing that in the Percona documentation.

Reading around on Google I come to the conclusion that I have to extract the tar.gz file on my machine and "replay" the transaction log.

So my question is what specific steps are needed and what are the caveats for restoring the DB snapshot to my machine.

Finally it seems that using XtraBackup by itself doesn't guarantee that you will be able to restore your data if something goes wrong. For example if the server was to die the backups I have would be useless without the transaction log. So a general phrasing of my question might be what steps need to be taken to ensure that I can restore my data on any machine I wish.

Please keep in mind that my main area of expertise is not database management!

Following instructions that were given to me in an answer, I extracted the backup archive and "prepared" the backup with innobackupex --apply-log . which completed fine. I then stopped mysql, copied ALL the files from the backup dir to the mysql data dir and restarted mysql.

Once I log in though (after resetting the root password), I can see the DB's and their tables (using adminer) but once I select one I'm hit with a table does not exist error. So I'm still at my original question of firstly how to restore to a different machine and secondly how to make sure my backups are usable whenever/wherever I need them.

On a side note, from what I'm reading there should be an .idb file for each table but I don't see anything like that.

It seems that I'm doing everything correctly. My backup script includes

#!/bin/bash
BDIR="/disk2/backup/mysql"

/usr/bin/innobackupex --user=backup --password=xxx --ibbackup=xtrabackup --stream=tar /tmp | gzip -c -9 > $BDIR/`date -u +%Y-%m-%dT%H:%M:%SZ`.tar.gz

which also rsyncs the files off-site.

I download the file, extract (tar -ixvzf), apply the log as mentioned above, stop mysql and copy the files to the datadir (cp), apply permissions, start up and log-in. Browsing to my backup db gives me the error

  140730 16:28:49 [ERROR] Cannot find or open table lead@002dmanager@002dbackup/crm_word_ban from
  the internal data dictionary of InnoDB though the .frm file for the
  table exists. Maybe you have deleted and recreated InnoDB data
  files but have forgotten to delete the corresponding .frm files
  of InnoDB tables, or you have moved .frm files to another database?
  or, the table contains indexes that this version of the engine
  doesn't support.

for all the db tables.

MySQL version on server is 5.1, local version is 5.5


texts: Postgres: compare the dates in rows and find the difference

texts:
I would like to check if a day is missing during the current month in my result set.The table looks like this.

date               balance
2017-06-01          404.66
2017-06-03          392.41
2017-06-07          295.41
2017-06-10          275.41
2017-06-13          155.24
2017-06-15          151.74
2017-06-20           11.15
2017-06-21           -5.15

Since the result set is always within  a month, only the difference in days is needed. I need to check the following two cases.

if the difference between dates is 1, then take the corresponding balance as it is,
if the date difference is bigger than 1, then multiply the number of day difference by the previous balance.

EDIT: For example, the day difference between the first two rows is 2. My desired output is 2*404,66.
And then, the day difference between 2nd and 3rd row is 4. So 4*392.41.

Is there any short and efficient way of doing this?

I need to analyze more than 300.000 data.


texts: Efficiency : Query Over Multiple Colomns vs Query over PK

texts:
What would be better approach or rather in what situation should I select which approach, in below explained situation

    **Table - Wishlist**
    Id PK
    Owner FK
    Recipient FK
    Title
    Description
    Url

Search Rules
Return wishlist item with specific 'Id', if Logged in user's username matches Owner or Recipient.

Approach 1 -
Result = Select * From Wishlist where id == %id% && ( Owner == username || Receipent == username )

This will return only one record if the user is allowed to access it.

Approach 2 -
Result = Select * From Wishlist where id == %id%
if(Result.owner == username || Result.Receipent == username){
  return Result
}

This will return result irrespective of whether the user is an owner or not.
However, we're looping over single column as compared to multiple columns.

Which approach should be preferred and under which condition?

PS - SQL syntax is not accurate, and is only meant as an example.


texts: Should 'FOR system_time ALL' include rows where the start and end times are equal?

texts:
Querying a system-versioned temporal table with FOR system_time ALL appears to omit rows where the start and end times are equal.

For example:

/* Create a simple system-versioned table */
CREATE TABLE [dbo].[svt_test](
    [string] [VARCHAR](30) NOT NULL,
    [validFrom] [DATETIME2](7) GENERATED ALWAYS AS ROW START NOT NULL,
    [validTo]   [DATETIME2](7) GENERATED ALWAYS AS ROW END NOT NULL,
CONSTRAINT [PK_svt_test] PRIMARY KEY CLUSTERED
(
    [string] ASC
),
    PERIOD FOR SYSTEM_TIME ([validFrom], [validTo])
)
WITH
(
    SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[svt_test_history] )
);

/*
Insert and immediately delete a row.
May have to try multiple times to get a row with the same start and end
timestamp.
*/
INSERT INTO dbo.svt_test (string) VALUES ('string');
DELETE dbo.svt_test WHERE string = 'string';

/* 0 rows */
SELECT * FROM dbo.svt_test;
/* 1 row */
SELECT * FROM dbo.svt_test_history;
/*
0 rows.
Should be the "union of rows that belong to the current and the history table"
  per https://msdn.microsoft.com/en-us/library/dn935015.aspx
*/
SELECT * FROM dbo.svt_test FOR system_time ALL;

/*
   CONTAINED IN does not find the row either, even though MSDN says the
   interval is defined as SysStartTime >= start_date_time AND SysEndTime <=
   end_date_time
*/
DECLARE @Start DATETIME2(7);
SELECT @Start = validFrom FROM [dbo].[svt_test_history] WHERE string = 'string';
DECLARE @End DATETIME2(7);
SELECT @End = validTo FROM [dbo].[svt_test_history] WHERE string = 'string';
SELECT * FROM dbo.svt_test FOR system_time CONTAINED IN (@Start, @End);

/* Clean up */
ALTER TABLE dbo.svt_test SET (SYSTEM_VERSIONING = OFF);
DROP TABLE dbo.svt_test;
DROP TABLE dbo.svt_test_history;

I've verified this on 2016 SP1 CU1 (12.0.4416) and 2016 CU3 (13.0.2186). I got the same result with datetime2(0) instead of datetime2(7).

Would anyone happen to know whether this is likely a bug or by design? Alternatively, perhaps I've misunderstood something?


texts: DB client tool -- To get the error message when open toad oracle client

texts:
I have a serious problem with toad oracle client. I got the error message as following when I open toad client (installed and then opened it):

  Error: No valid Oracle clients found. You need at least one 64-bit
  client properly configured

Please do me a favor to give me how to deal with this issue or give me any slim clue.

Download from toad official website with 64-bit like as following picture


texts: IBM DB2 Data Studio Web Console Installer

texts: I would really like to test the Web Console for Data Studio to see if it will do what we need.  However, I absolutely cannot find an installer/download for it.  Can anyone help?


texts:
I would like to know idf it is possible to force System.Date use in an SSAS data source view.
For now, I have a view with a field of type Date and in the data source view it is converted to a System.Datetime.
thank you.


texts:
For the first time since it was set up I need to reboot a read-only MySQL replication slave.

I found this article about downing a slave for maintenance (albeit he's just describing stopping the mysql daemon):

  How to Safetly[sic] Restart Your MySQL Slave Server

In summary the procedure is:

In mysql client:

STOP SLAVE;
FLUSH TABLES;

From OS:

/etc/init.d/mysql stop

I would reboot at this point and then after the system has booted:

In mysql client (the mysql daemon is configured to start on boot):

START SLAVE;

Does this look about right? Is there anything else I should be doing?


texts: Change MySQL default installation directory in OS X

texts:
I install mysql in default installation directory /usr/local/mysql

Is it possible to change mysql installation directory to another path?

I am running OS X 10.10 & MySQL 5.6


texts: SQL - Select everything after a certain row

texts:
After the following query:
SELECT
    my_table.ID,
    my_table.DATETIME,
    my_table.VALUE,
    diagnostics_keys.type as type,
    diagnostics_keys.key,
    diagnostics_keys.unit,
    diagnostics_policies.type as policy,
    pair_device_voucher.device_id,
    pair_device_voucher.voucher_id
FROM
    my_table
    inner join diagnostics_keys on my_table.DIAGNOSTIC_TYPE_ID = diagnostics_keys.ID
    inner join diagnostics_policies on my_table.POLICY = diagnostics_policies.ID
    inner join pair_device_voucher on my_table.DVP_ID = pair_device_voucher.ID
WHERE
    (
        (diagnostics_keys.type = 'AVGKMKWH')
        OR (diagnostics_keys.type = 'STATUS')
    )
    AND (
        DATETIME BETWEEN 1612515739000
        AND 1612516445000
    )
ORDER BY
    DATETIME ASC

I am getting the following results as they are in the image:

How can I adapt the query in order to select only the rows/entries that follow after the condition type= status and value = 40 (the rows that are inside on the red rectangles)?
Or to exclude, if the status = 100/20/whatever, do not select the following entries.
These are two commands for the sample data:
CREATE TABLE my_table(
         ID varchar(255),
         Datetime_ varchar(255),
         Value_ varchar(255),
         Type_ varchar(255),
         Key_ varchar(255),
         Unit_ varchar(255),
         Policy_ varchar(255),
         Device_ varchar(255),
         Voucher_ varchar(255) )

INSERT INTO `my_table`(
                      `ID`,
                      `Datetime_`,
                      `Value_`,
                      `Type_`,
                      `Key_`,
                      `Unit_`,
                      `Policy_`,
                      `Device_`,
                      `Voucher_`)
             VALUES ('1','1612515739000','40','STATUS','203_sts','','periodic','12345','12345'),
('2','1612515745000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345'),
('4','1612515754000','40','STATUS','203_sts','','periodic','12345','12345'),
('3','1612515760000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345'),
('56','1612515775000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345'),
('6','1612515775000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345'),
('7','1612515790000','100','STATUS','203_sts','','periodic','12345','12345'),
('8','1612515805000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345'),
('9','1612515813000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345'),
('10','1612515820000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345'),
('11','1612515835000','40','STATUS','203_sts','','periodic','12345','12345'),
('12','1612515850000','327.67','AVGKMKWH','0_202_avgkmKwh','Km/Kwh
','periodic','12345','12345')

Thank you in advance.


texts: multiple join HOW to get the following result?

texts:
I would like to get the following result. I have tried with the following scripts to get TAB1 and TAB2 but I couldn't show up the result like that.

This is my code for TAB1:

select branch_code,ABS(nvl(sum(cr_bal_lcy - dr_bal_lcy), 0)) as A35
         from gltb_gl_bal_daily
        where gl_code in ('620100000',
                          '620230000',
                          '620400000',
                          '620800000',
                          '620910000',
                          '620930000',
                          '621010000',
                          '621020000',
                          '630000000')
          AND trn_dt = &PM_DATE --entry
        group by branch_code  ;

Resultset:

--------TAB1-------------
id      Amount
-------------------------
1       5791920.396
2       4900
3       103.95
4       4420.265
5       46.2
6       23.1
7       500
8       0.03

and this is script for TAB2:

select branch_code,ABS(nvl(sum(cr_bal_lcy - dr_bal_lcy), 0)) as A35
                 from fcubs1203.gltb_gl_bal_daily
                where gl_code in ('620820000', '630300000')
                  AND trn_dt = &PM_DATE
                  group by branch_code;

Resultset:

--------TAB2-------------
1       593023.5

===> this is the desired result that i would like to show:

--------Result-----------
1       5198896.896  (TAB1.id-TAB2.id)
2       4900
3       103.95
4       4420.265
5       46.2
6       23.1
7       500
8       0.03


texts: Cannot INSERT into database on a shared server

texts:
I have launched an application on a shared server but I cannot INSERT any data. I thought maybe I don't have privileges so I did SHOW GRANTS in my application then die()

The result was [ EDITED ]:

  [Grants for gigfa_11863212@%] => GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES ON `gigfa_11863212_life_journal`.* TO 'gigfa_11863212'@'%'
  [Grants for gigfa_11863212@%] => GRANT USAGE ON *.* TO 'gigfa_11863212'@'%' IDENTIFIED BY PASSWORD '*78839FD7FF9639FB72CCB5E9D8BCD81A7A0A9E3D' WITH MAX_QUERIES_PER_HOUR 80000 MAX_UPDATES_PER_HOUR 60000 MAX_CONNECTIONS_PER_HOUR 20000

Why do I get the following error message when I issue an INSERT statement?

< INSERT command denied to user 'user'@'host' for table 'table' >


texts: problem to join several tables without relation

texts:
My simplified database scheme:

CREATE TABLE `stores` (
  `idStr` int(11) NOT NULL,
  `nameStr` varchar(255) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `channelProvider` (
  `idCP` int(11) NOT NULL,
  `idStr` int(11) NOT NULL,
  `mount` int(11) NOT NULL,
  `dateCP` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `Expenses` (
  `idExp` int(11) NOT NULL,
  `idStr` int(11) NOT NULL,
  `amountExp` int(10) NOT NULL,
  `dateExp` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `products` (
  `idProd` int(11) NOT NULL,
  `idStr` int(11) NOT NULL,
  `nameProd` varchar(255) NOT NULL,
  `priceSale` int(11) NOT NULL,
  `priceBuy` int(11) NOT NULL,
  `qAvailable` int(11) NOT NULL,
  `dateAddProd` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I need to select and get a result as an example:

BENEFIT | Turnover | Total profit of Product | Total profit | Total of Expenses | Total Amount Channel Provider | Month

I tried with JOINS but I don't get good results.
Appreciate any hints!


texts: Why is this SQL update query returning rows?

texts:
I'm running the following update command

UPDATE table
SET [End] = '2018-12-17'
WHERE (ID = '5b010204-c12c-4b38-8d28-affe11684da0')

This will give the following result:

What can cause this on SQL server 14?


texts: Postgres 9.4 importing csv file causes connection abort

texts:
I have very simple database which i would like to import data from a csv file, so far i tested with a file with a few rows, and it worked.

So tried to upload the real data to the database, which causes this error:

Software caused connection abort (0x00002745/10053)

And this error message too:

Copy failed!
Connection to database broken.

Now i have done this before, i have uploaded way more bigger csv files into the database with no problem, my file is roughly about 1.4 Mb.

Note: i'm connected to the database via ssh tunnel, although this should be not a problem.

Why i'm losing the connection? i have checked that i have permission to modify the csv files


texts: Database design: sharing tables between databases?

texts:
PREAMBLE: I'm taking a bit of a risk asking a slightly abstract question in a stack exchange (cough) such as this, but I'm hoping that someone may have come across this type of situation before. This is a question which is also likely to be useful to be others in the future. I can give detailed technological aspects in relation to this question, and the answer need not be "opinion" based. Furthermore, aspects in relation to this question are unlikely to change temporarily. So if you wish to downvote me because of my inexperience, or because you think you are smarter than me go ahead, but don't pretend it is because it is an inherently bad question. /PREAMBLE

I'm working with MySQL, and have developed a database to support a room allocation program. Aspects such as time, rooms, buildings are fixed and never change. However, the actual allocations entirely change every half year. As such the tables related to the allocation of users to rooms is entirely overhauled every 6 months. In order to preserve these records I duplicate the entire database, archive the old one and start off from scratch with the allocation information every time an overhaul is required.

I dislike the redundancy that is inherent to that process. The tables that never change need not be duplicated (and in my opinion, should not be duplicated). Instead it would make more sense for them to be shared between database children, which inherit this information.

Is this even possible with MySQL? No course that I have attended concerning rdmss have ever mentioned such a process. Looking on-line I have seen people state that the sharing of tables between databases would be a design flaw, but I do not see a more elegant solution. Are there better solutions to this design problem?


texts: AlwaysOn Availability Group and workgroup servers

texts:
Has anyone had any luck setting up AGs with servers in a workgroup, neither server in a domain (no domain even exists for this client). I've added DNS suffix to both servers, like recommended. I have both servers using matching local service accounts for SQL, share that is accessible by both servers. When adding the Secondary Replica, I select SQL Authentication instead of Windows. I've blown away the Endpoints and recreated (several times). Nothing in the SQL or Windows logs either.

ServerA.local.workgroup
ServerB.local.workgroup


texts:
I had a big problem which I just happened to solve, but it's a permissions issue I don't quite understand. Unfortunately I'm in a tight bind and no amount of googling in the past week has really helped, so perhaps someone could explain what happened here:

As part of an interface out to our customers we developed a Powershell script that maps a network drive on our web server WEB. The job step is run on a regular basis to validate the connection is active:

Connect Job

Owner: [JobsLogin]

Step 1: Connect Share

Type: PowerShell

Run as: Sql Server Agent Service Account

Command:

$Scripts\Map-Share.ps1 (My configuration values)

Map-Share.ps1

param (
    [string]$DriveLetter,
    [string]$Path,
    [string]$User,
    [string]$Password,
    [string]$Persistent="No"
)
if (!(Test-Path "${DriveLetter}:"))
{
    net use ${DriveLetter}: $Path /u:$User $Password /persistent:$Persistent
}

Then we run BCP to output the file to the mapped site:

declare @cmd varchar(2048)
SET @cmd = 'bcp "exec ' +
    @SPName + ' " queryout "' + @DataFolder + @FileName + '.tmp" ' +
    @BCPFlags
print @cmd

EXEC master..xp_cmdshell @cmd

At some point in the past week this mapping became unavailable and we started receiving the dreaded "BCP cannot open host-file" error message.

After an number of hours mapping network drives, deleting mapped network drives,  running diagnostic command shell, pleading with god and then satan, checking registry values, I started to suspect that the mapped drive was not mapped for the user executing sp_cmdshell.

I ran exec sp_cmdshell "cd W:" and found that it wasn't working. I ran exec sp_cmdshell "net use" and saw that the status of W was "Unavailable". Finally, progress!

And a

exec sp_cmdshell "net use /delete W:"

exec sp_cmdshell "Powershell $Scripts\Map-Share.ps1 (My configuration values)"

later, everything works again!

But now I'm stuck, because I can't figure out how to run these commands in the job as the correct user (I've tried the services account with no success). I could change the Connect share job to run the powershell command but that seems like a hack to me (But I'm not a database guy, so maybe that is right).

Anyway, what is really going on here? What user is my drive getting mapped in the connect share job?


texts: Migrating differential backup restoration

texts:
Good afternoon,

A little back story about what I'm trying to do: I'm migrating a database environment from one SQL server to another. A simple full backup/restore works fine, but there's a possibility that the old environment will still have to be active while I move data to the new environment. Mirroring and log shipping are not options, but there is nothing that is needed to be updated in the database itself for it to work, so I thought I'd run a differential on one and restore it to the migrated database location.

I tried to do this already and found out that I'm already out of the recovery phase after my first full database restoration. I guess my real question is can I do a full backup on my current location database, and a differential on my previous location database and have it work the same as if I restored a full and dif backup from the same location database?

Also, if there are log backups between the times I did a diff backup of the original database, do I need to include them as well?

Please let me know if you need clarification.


texts: How to change the path of temp file,redo log file when restore an rman backup on another machine?

texts:
I am trying to migrate my database (11.2.0.4) A to another machine by using rman backup. At present, in the source machine, the redo log file, temp file is stored under the directory foo (/foo). In the target machine, I would like to store these files under the directory bar (/bar).
In the past practice, what I did is to create foo for these files and move these files to bar manually after the recovery is complete. RMAN is such a powerful tool that I think there must be some an easy way to satisfy my requirement.
So far, by searching the google, I got a useful rman command for temp file:

set newname for tempfile <fileno> to '<path>'

It seems that there is no similar command for redo file.
Before using this command to perform a recovery, I have several questions to ask:

When we take an full backup, the backup did not go with temp tablespace and redo files, but in the past migration I experienced, I found that when I open (alter database open resetlogs)the database after recovery is complete, redo log files and temp files exist. Will rman create this automatically for database? I can`t find the reliable oracle documents for this topic.
To change the path for redo is so boring, I would the finish this task in the rman scripts, is there any command available to rename the redo log file in the rman scripts?


texts: SQL Server Extended Events Sessions on VIEWs

texts:
I'm trying to monitor when Views are being called in SQL Server.

Using Extended Events, I've created a Session and included event "sql_statement_completed".

Then I've added 1x filters on this:

  sqlserver.database_name | equal_i_sql_unicode_string |
  MyDatabaseNameHere

So the session logs all sql statements across that particular database.

However, I'd like to only monitor when Views are being called. Ideally, something to track against ObjectType = 8278.

Is this possible?


texts: Pull multiple fields from table B where a specific value from Table A is not in Table B

texts:
SQL Newbie here. I have a question about pulling multiple fields from a database when one value is not in another table. I have two tables:

"Contact" table - contains all of our contact information such as their email address, contact creation date, department, job, etc. This is considered our master table.
"Event" table - contains information regarding a contact and their email activity such as their email address, email delivery status, email create date, etc.

The db uses the email address as the joining key. I don't have the privileges (due to work restrictions) to add a new PK.

The task is to find all contacts in the Contact table that are not in the Event table, and pull their email address AND their contact creation date. The Event table does not have the contact creation date. When I select only the email address it works fine, but when I try to add in the second field (create date), it gives me an error.

The query I'm using is below. Any help is appreciated!

SELECT e.recipient, c.contactProperty_createdate
FROM Event as e
WHERE NOT EXISTS
    (SELECT c.emailAddress, c.contactProperty_createdate
    FROM Contact as c
    WHERE c.emailAddress = e.recipient);


texts: 1:M relation exclude row if any row in M table does not contain specific value

texts:
Consider I have following table products

group   ads status
X       ad1 ACTIVE
X       ad2 NOT_BUYABLE
X       ad3 PAUSED
Y       ad4 ACTIVE
Y       ad5 SUSPENDED
Y       ad6 PAUSED
Z       ad7 ACTIVE
Z       ad8 OUT_OF_BUDGET
Z       ad9 ARCHIVED

All I want is to filter distinct group

For example, I want to filter the group where

the product status != NOT_BUYABLE
AND the product status = ACTIVE

consider Group X:

ad1 = ACTIVE
ad2 = NOT_BUYABLE
ad3 = PAUSED

This filter would ignore Group X

consider Group Y:

ad4 = ACTIVE
ad5 = SUSPENDED
ad6 = PAUSED

This filter would return Group Y

consider Group Z:

ad7 = ACTIVE
ad8 = OUT_OF_BUDGET
ad9 = ARCHIVED

This filter would return Group Z

so final output should be

group
Y
Z

This means the user only wants to filter product that doesn't have any NOT_BUYABLE status values but has at least 1 ACTIVE status.

when I apply != operator I have to make sure none of the product under that group has those staus

This SQL query is wrong

Select  distinct `group`   from products
where status != 'NOT_BUYABLE' and status = 'ACTIVE';

Let me know what will be the SQL query


texts: Non-English characters are not imported using SSIS or Visual Studio Deploy

texts:
I'm having problems with importing data into a SQL Server 2008 database.

If I execute the following statement in SSMS, it works fine.

INSERT INTO
    [dbo].[ObjectiveRatingTranslation]
(
    [ObjectiveRatingId]
,   [TranslationId]
,   [Abbreviation]
,   [RateName]
,   [Description]
,   [CreatedBy]
,   [CreatedOn]
,   [ModifiedBy]
,   [ModifiedOn]
)
VALUES
(
    3
,   29
,   N'O / T'
,   N'På målet'
,   N'Målet har slutförts i enlighet med detta förväntningar. Uppfyller de slutprodukter som definieras. Det förväntas att de flesta mål bör vara Topplista på denna nivå.'
,   0
,   '2012-03-27T11:49:43.2300000'
,   0
,   '2012-03-27T11:49:43.2300000'
)

If I have this statement in  a script file that is being executed by VS 2010, Database, Deploy.  The data gets imported as

P� m�let | M�let har slutf�rts i enlighet med detta f�rv�ntningar. Uppfyller de slutprodukter som definieras. Det f�rv�ntas att de flesta m�l b�r vara Topplista p� denna niv�.

Same type of problem happens with SSIS on import to. I'm sure there must be some kind of setting that can be changed, but I"m not sure what.


texts: Why does mysql give different result for this summations?


texts: Variable layers of data in a multi-country addresses system

texts:
My diagram
I want to record addresses in my database. The layout I have right now is visible in the diagram below:

Considerations
Continent, Country, Subdivision and City will be pre-filled, Address will be filled in by the user that will allow him four lines to fill in what he wants.
The question is not about the four lines in the Address table but rather the Subdivision table. Not every country has the same division of territory. US has states, Belgium has regions and provinces (two layers) and other might have more or less. Therefore I've created the Subdivision table which has a foreign key (FK) to itself and a FK to the Country.
Let's take a fictional country, it has five regions and six provinces:

Country: X
Region: Y
Province: Z

This would give me a row in the Country table and two rows in the Subdivision table. Subdivision Y would have FK Country filled in and referencing to X. Subdivision Z would have FK Subdivision filled in and referencing to Y.
Questions
I have some questions about the design I came up with because I'm not sure if this is a good approach. It would allow me to get everything higher and lower in the tree but having two FK's in the Subdivision table where only one is used at a time looks to me as a bad design.
The other thing about this is that querying everything might be hard to do. Am I correct?
Is there a better approach to not necessarily recording addresses but rather to variable layers of data?


texts: Postgresql 9.2 won't start after rerunning installer

texts:
All,

I made a fatal mistake and accidentally ran the core installer for postgresql rather than the stackinstaller. I didn't go all the way through the install process but it still set something that is preventing it from starting at all now. This is a production DB and I really need some insight in to what I can do to troubleshoot this problem.

I am running 9.2 on a Windows 2008R2 server.


texts: Copy SQL Server Maintenance Plan between instances on same server

texts:
I have multiple SQL Server 2019 instances on the same server. I've created and exported the first instance's maintenance plan, edited its exported DTSX file to change the instance and plan names to avoid name conflicts, and imported the edited DTSX via SSIS (and DTUTIL) on the same server.
Problem: I see the the both the original and imported plan in SSIS under Stored Packages > MSDB > Maintenance Plans, but NOT under the second instance's Management > Maintenance Plans list in SSMS.


texts: Ge the value of id in master table and supply it to other tables

texts:
I have 5 tables, of which one is master table and other 4 are the tables with have the foreign key of the id of the master table

I have to create a procedure which INSERT's data in all the tables including the Master table, I have to keep the id of Master table incremental.
So how should I get the value of the Master table row and supply the id of it in other tables in that procedure, WITHOUT using SELECT query(to get the id of Master table row) or WITHOUT supplying manually the id of master table in my program.

I want all of it to be handled it on database side.

I am using PostgreSQL

Please help me in it.


texts:
I have a little bit confusion how the json data internally organized (physical storage structure for dynamic schema objects)

db.some_collection.insert({x:1,y:4,z:34})
db.some_collection.insert({p:1,q:5,z:"nothing"})

let us assuming if we created the indexes on 'x' and 'q', then how the json data internally organized/stored.

Looking for vital suggestions


texts: Creating an entity-relationship diagram about an art fairs and galleries business environment

texts:
Scenario description

The charactheristics of the business domain under consideration are:

Each and every art item has a unique identifying number, and also a name and asking price.
Art galleries participate in multiple art fairs.
Each time a gallery participates in an art fair, the database stores the date, the name of the art fair date the gallery participated, the name of the art fair, a count of how many items the gallery brought to the fair, and a count of how many items sold.
For each art gallery, the date and name of of the art fair, together, identify a trip an art fair. However, art fairs involve many vendors selling art, so a date and fair name pairing cannot identify a fair visit unless the gallery identifier is included.

The issue

I am stuck on this problem so far in that I have just two entities, Art Galleries and Art Fair connected by a relationship....

  However, art fairs involve many vendors selling art, so a date and fair name pairing cannot identify a fair visit unless the gallery identifier is included.

I don't think vendors are part of the entity-relationship diagram. The statement is saying a date and fair name cannot identify a fair visit unless the gallery identifier is included.

My ERD just has a box with 'Art Galleries' connected to a diamond with 'participate' connected to a box with 'Art Fair'.


texts: Deleting rows from table where values less than an aggregate value


texts: Which SQL standard is most broadly and completely supported?

texts: I want to concentrate on one of the SQL language specs for learning and reference purposes. Which version of the SQL standard is most broadly supported across DBMS'es so that I can focus my time and energy on it ?


texts: SQL Server 2012 Page Life Expectancy resets to 0 after about 50 days

texts:
I've noticed an odd behavior on a 2-server HA cluster and I was hoping someone could confirm my suspicion, or maybe offer some other explanation... Here is my setup:

A 2-server SQL 2012 SP1 installation
SQL AlwaysOn HA has been enabled for a few databases
CPUs are 2.4GHz, 4 cores
RAM is 34 GB (it is an AWS instance, hence the odd number)
Resource utilization is relatively low - each server has 14+ GB memory free, and SQL is not capped on how much memory to use
Disk access time is fine - rarely going over 15ms/Read or Write
Databases are not big - 1 GB, 1.5 GB, 7.5 GB
SQL server process is using 16 GB Private Bytes, 15 GB Working Set

Overall, no resource issues are noted. Now for the odd part. SQL is not restarted (process has been running for almost 6 months) but it seems that every ~50 days, the Page Life Expectancy counter drops to (almost) 0. Up until that point it climbs steadily, no drops. Here is a perf graph:

When I look at the counter data (I don't have the exact number, just an hourly aggregation) it seems the PLE counter value reached about 4,295,000 sec (roughly 50 days) every time (at least every time I have data for).

My crazy theory is that the PLE number is held as milliseconds as an unsigned long int (which has a limit of 4,294,967,295) and at 49.71 days it resets, either by design, or because of a bug. This would explain the behavior of the two servers and the identical pattern they have. Or it could be something totally different and I am just not making any sense. :)

Has anyone seen anything like that, or can explain this behavior?

P.S. I saw this post, but my case seems slightly different.

P.P.S. This is a repost - I originally posted it here, but was advised the audience here is more appropriate.

Thanks!


texts: Best way to automatically reset sequence/identity once a year

texts:
I need to have a identity/sequence/counter of some sort reset annually. What is the best way of doing this automatically (i.e. I don't want the front end application calling a stored procedure or executing SQL to do this).

If it matters the database will be at least SQL Sever 2012.

I will probably be using an identity column, but if there is a better way to do it with sequences instead, I am certainly open to that.