texts:
--- Update ---
Thanks for the comments and the help so far. And I apologize for not specifying the question further. I have updated the question below.
--- Update ---

Currently I am asked to develop a database structure for a pretty big amount of data. I am debating between implementing a graph database instead of a "normal" relation database, and was wondering what the disadvantages are if the data does not necessarily contain any relationships? Could separate, unconnected, nodes be utilized the same way as a table / row in a table?

I am asking this because as of right now there are is no need for relationships but I am trying to future proof the database (with the anticipation of relationships) to expand the abilities of the data. If it is of any help, I am debating between OrientDB/Neo4j or mySQL/postgreSQL.

An example:
Let say we have a database full of stocks. Stock can be bought and sold at pretty much any time / day by anyone (as long as the market is open). Now this database could be a normal relation database: Table 1: IDs | Products | Prices | Sizes | Dates. But could potentially also be organized as a relationship database Node 1: Stock A | Node 2: Stock B.
If I just use the database for storing stock information, it seems to me a normal database will be better. But is this true? Will it negatively affect me working with a relationship database / will I be better off working with a normal database? Are there disadvantage to organize my data in nodes, instead of rows?

A picture to illustrate it all:

The database will mainly be used for products bought and sold similar like stocks, however they also have other information such as locations attached to them. I am trying to anticipate the implementation of features such as recommending products or even the prediction of next products to be bought by somebody.

Most of the queries from the database will be per product.
Pulled from the database: 100 to 1000 times a day.
Pushed to the database: 20.000 a day.

An added question which might reveal a disadvantage:
How easy will it be to go from relation-to-graph or from graph-to-relation. Any lock-in dangerous?

Thanks for all the help, comments have been great so far!
R


texts: Does MySQL InnoDB locks multiple rows with READ_COMMITTED isolation level?

texts:
I am in trouble while resolving LOCK WAIT TIMEOUT EXCEED error with MySQL InnoDB.

I have gone through this article and it says if we use isolation level READ_COMMITTED then my update query should lock only those rows which match the WHERE condition, but this is not working for me as I am getting 54 row locks for that query.

The result of SHOW ENGINE INNODB STATUS; is given below.

---TRANSACTION 8AE162608, ACTIVE 102 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 56 lock struct(s), heap size 6960, 54 row lock(s), undo log entries 135
MySQL thread id 18013536, query id 164766412915 localhost MyDataDb Updating
update stock set quantity = quantity + -1, last_updated_dts='2015-01-19 00:08:23', last_updated_by='vishal' where location = 1 and product_id = '123'
------- TRX HAS BEEN WAITING 98 SEC FOR THIS LOCK TO BE GRANTED:

Why I am getting 54 row locked while my update query condition matches only one row and I am using READ_COMMITED isolation level?

EDIT: All columns used in where condition are indexed.
Please see my table structure here.

CREATE TABLE `stock` (
  `location_id` int(10) NOT NULL,
  `product_id` varchar(100) NOT NULL DEFAULT '',
  `quantity` int(6) DEFAULT NULL,
  `is_available` char(1) NOT NULL DEFAULT 'F',
  `created_dts` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_by` varchar(50) NOT NULL DEFAULT '',
  `last_updated_dts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_updated_by` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`location_id`,`product_id`),
  KEY `FK_location` (`product_id`),
  KEY `availibility` (`location_id`,`is_available`,`quantity`),
  KEY `Index_4` (`last_updated_by`),
  CONSTRAINT `FK_stock_2` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


texts: Trace binding variables from UPDATE statement

texts: Is there a way to trace binding variables from UPDATE statement? I tried to look into v$sql_bind_capture but according to documentation this only works for WHERE or HAVING statements.


texts: How to get list of all tables in SQL Server without specifying a db?

texts:
I have a function that allows me to check if a table name is valid, that looks like this:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @tbl

But this has to be executed in the context of the database within which you are checking. I would like to make this dynamic. i.e.

DECLARE @db VARCHAR(250) = @dbString;
DECLARE @tbl VARCHAR(250) = @tblString;
RETURN (
    SELECT TABLE_NAME
    FROM @db.INFORMATION_SCHEMA.TABLES // this is the line!
    WHERE TABLE_NAME = @tbl
)

But I have found that I can't execute dynamic SQL from within a function. This function is called from within an SP.

Alternatively, is it possible to get a return value from an SP when that SP is executed from within an SP?

I have found that this query:

select * from master.sys.databases

gives me a list of databases. Is there anything I can join this to to get a list of tables?

Other solutions that I have found all rely on executing an SP


texts: mysql 5.6 GTID replication 'Got fatal error 1236 from master when reading data from binary log'

texts:
I Checked SHOW SLAVE STATUS   error  is "'Got fatal error 1236 from master when reading data from binary log: ''The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.''

i am suspecting it happens because of  master old binary  log (7 days before) deleted form master as we  are  using  expire_logs_days=7 option and slave still looking  for this old binary  log for  some GTID reference to replicate   with  master.

I tried following  link  solution ,

http://www.mysqlperformanceblog.com/2013/02/08/how-to-createrestore-a-slave-using-gtid-replication-in-mysql-5-6/

but  it still not  working.

Note :using  mysql  5.6.15

Now only  option for me to  re-create  slave   by  taking  mysqldump from master.

Is there any other option if  we can start replication without recreating  new slave.

Thanks in Advance


texts: How to add 1 milliseconds to a datetime string?

texts:
Based on a select, I can return x rows like this:

1   2019-07-23 10:14:04.000
1   2019-07-23 10:14:11.000
2   2019-07-23 10:45:32.000
1   2019-07-23 10:45:33.000

We have all milliseconds with 0.

Is there a way to add 1 by 1 milliseconds, so the select would look like this:

1   2019-07-23 10:14:04.001
1   2019-07-23 10:14:11.002
2   2019-07-23 10:45:32.003
1   2019-07-23 10:45:33.004

I'm trying to create a cursor or even a update with no success.

This is the query to get the results I want:

  select top 10 ModifiedOn
    from [SCHEMA].[dbo].[TABLE]
  where FIELD between '2019-07-23 00:00' and '2019-07-23 23:59'

There are 81k values. The field is DATETIME.


texts:
We have two data centers DC1 and DC2

The distance between two DC's are about 200 miles.
DC1 - has two nodes
DC2 - has 1 node with SQL 2012 Always-On AG and windows 2008R2
Node majority cluster
Two nodes on DC1 are primary.
Failovers are always between the two nodes on DC1.
DC2 is only for backups.

There is intermittent network issue between two DC1 and DC2, whenever there is network glitch causes the databases on DC2 to restart which kills the full backup job that was running.

One of our DBA suggesting to create a file share witness and remove vote to the node on DC2, then change quorum settings from node majority to node and file share majority.

My Question is..

If we create file share witness and change quorum setting will fix our backup issue? I am not clear how the full backup job continue running when there is network glitch and the all the nodes are in cluster
Can any one advise how do i over come from this situation. Network team said they do not have any fix from their end.

I researched on this and found below link which looks similar but i can not correlate with my issue.

SQL Server Always On Node and File Share Majority


texts: What do I have to do in order to be able to receive notifications?

texts:
In the event of a database error in an application, what do I have do in order to be able to receive notifications?

Should I create an alert in SQL Server and use the RAISERROR command within the application?


texts: a SELECT or set-returning version of MERGE/UPSERT?

texts:
I'm learning MERGE or UPSERT (UPDATE + INSERT), which according to wikipeda, has the form:

 MERGE INTO tablename USING table_reference ON (condition)
   WHEN MATCHED THEN
   UPDATE SET column1 = value1 [, column2 = value2 ...]
   WHEN NOT MATCHED THEN
   INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]);

, and:

  If the ON field(s) in the Source matches the ON field(s) in the
  Target, then UPDATE

  If the ON field(s) in the Source does not match the ON field(s) in the
  Target, then INSERT

I was wondering if there is a SELECT version of it (without side effects) that returns the would-be result of the updated target table without actually changing the target table?

Using PostgreSQL 9.5's INSERT INTO ... ON CONFLICT [ conflict_target ] conflict_action statement (pointed out in wikipedia) as an example. Given a target table distributors and an update table distributors_upd as follows:

DROP TABLE IF EXISTS distributors;
DROP TABLE IF EXISTS distributors_upd;

CREATE TABLE distributors (did INTEGER PRIMARY KEY, dname VARCHAR);
INSERT INTO distributors
VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc');

CREATE TABLE distributors_upd (did INTEGER PRIMARY KEY, dname VARCHAR);
INSERT INTO distributors_upd
VALUES (5, 'Gizmo Transglobal*');

Is there an effective query to get the result of a UPSERT without actually changing the target table distributors?

I tried to mimic the effect with:

WITH distributors_updated AS (
INSERT INTO distributors (did, dname)
    (TABLE distributors_upd)
    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname
    RETURNING *
)
--SELECT * FROM distributors;
SELECT * FROM distributors_updated;

Neither one of the last two lines gives the would-be result:

 did |           dname
-----+---------------------------
   6 | Associated Computing, Inc
   5 | Gizmo Transglobal*

The first ... SELECT * FROM distributors shows the original table (by design).

-----+---------------------------
   5 | Gizmo Transglobal
   6 | Associated Computing, Inc
(2 rows)

The second ... SELECT * FROM distributors_updated shows the change rows only.

-----+--------------------
   5 | Gizmo Transglobal*
(1 row)

The content of distributors is changed either way. Is there an effective query returning the "merged"/"unioned" without actually changing the target distributors?


texts: SSIS Derived Column Conditional Time Modification

texts:
I'm using SSIS to transfer data from a MySQL database to a SQL Server one. There's one point that I'm currently stuck on, however. MySQL TIME datatype supports values greater than or equal to 24:00:00, whereas SQL Server TIME caps at 23:59:59. One of the tables I'm transferring has a TIME column that, in MySQL, is 24:00:00. As logically in our business case this is a 'stop time' and thus equivalent to 00:00:00, I'm attempting to find a way to use SSIS to convert to that should that value come up.

Derived columns seem the way to go about doing this, but I don't seem to have a firm grasp on the syntax and very few resources are out there for TIME literals in SSIS conditional expressions. This is what I'm attempting to work with so far:

time == '24:00:00' ? time = '00:00:00' : time = time

Which of course returns only errors as I'm certain that part of that syntax is incorrect. I have (I believe) the logic, just spottily documented syntax is the issue.


texts: SQL Server- Limit to Concurrent table queries?

texts:
In SQL Server, or RDBMS systems in general, is there a limit to the number of concurrent queries that can be made against a table?  How is it that N number of users can access the same physical data at the same time?

Update

Actually, more generally, forgetting about limits, how is it that two users can read from a table at the same time. Can someone explain this from a technical perceptive?  If data is stored in a physical space on a disk, how is it that two users / processes can query that same physical data at the same time?


texts: To maximize DB performance, which commands should be run after loading large amounts of data into SQL Server 2008 via SSIS

texts:
I have written an SSIS package to load test data into an empty database. Some of the tables are very large (~700 million rows). Once the SSIS package has completed are there any commands I should run (as an apprentice DBA!) to maximize the performance of the database?

For instance, I executed EXEC sp_updatestats but it reported that no indexes required updating.

Is there a list of things to do once large amounts of data has been loaded or does SQL Server 2008 just take care of all that for you?


texts: Which full backup will be differential one based on?

texts:
I wanted to perform a differential backup of a DB in SQL Server 2008.
I currently have a .bak file with a full backup and I want to add this new one in it.

Will be the differential backup based on the existing one contained in the .bak file? Or will it be based on another full backup?


texts: With SSD's and in memory tables, does a snowflake schema become preferred over a star?

texts:
With SSD's being very fast (reducing the overhead of joins), while more expensive (increasing the cost of redundancy due to de-normalization), along-with a focus on in memory tables (again reducing the costs of joins), are there situations where snowflake schemas are now preferred over star schemas?

AFAIK snowflake schemas are rarely used in production quality code now


texts: Column doesn't exist when using CASE expression in WHERE clause

texts:
I have this SQL

SELECT
    price_amount,
    price_currency,
    CASE
        WHEN price_currency='USD' THEN price_amount * 3.9024
        WHEN price_currency='EUR' THEN price_amount * 4.413
        WHEN price_currency='ILS' THEN price_amount * 1
        ELSE price_amount * 1
    END AS final_price
FROM items
WHERE final_price BETWEEN 1 AND 100

but I get:

  ERROR:  column "final_price" does not exist
  LINE 1: ...ce_amount * 1 END AS final_price FROM items WHERE final_pric...

It is getting selected using alias, so why is it trying to find it in the table?


texts:
First poster, long time lurker here.
What is the best way to activate application role in a report ?

I've tried different things and so far the only method that works is to embed the call to the application role like so :-

EXEC sp_setapprole 'REPORTZ', 's3cr3t';
select *
from mytable
where ID < 10000

in the Dataset.
It does work... but not to my liking (certainly not in the shape I'd want to roll into production environment).

I'd prefer if I could somehow 'hijack' or 'inject' the application role activation line at runtime either through custom assemblies or probably some sort of 'server hook' in the Reporting Service (which in both cases, I have no idea how)

Much appreciated for your time + kind attention.

YS.


texts: SQL Server Collation for Arabic, Hebrew, English and French

texts:
I have new SQL Server database which need to have support in 3 different languages.

What character sets and collations do I need to choose if I want my database to support the following:

Hebrew
Arabic
English
French


texts: SQL query to return a column per date in date range with sum of contract utilisations (value/duration)

texts:
My question is similar but more elaborate than another already in stackexchange.com  "SQL query to return a column per date in date range?"

I have the following data:

Customer Start_date  Duration_mths  Value
Aaaaa    2014-01-01  4              200
Bbbbb    2014-03-04  12             120
Ccccc    2014-04-01  8              80
AAAAA    2014-01-03  4              100
Ccccc    2014-01-03  12             240
Ddddd    2013-12-05  3              60

What I would like to achieve is the sums of values divided by contract duration (evenly distributed contract utilisation) for each customer for each month if the contract falls betwen start date and end date (start plus dur. in months) :

Customer 2014-Jan   2014-Feb  2014-Mar 2014-Apr   (my comments)
Aaaaa     50         50        50       75   (for Aaaa 2 contracts overlapped in April)
Bbbbb ...  0          0        10       10   (for Bbbb 1 contract started in march)
Ccccc ...  0          0        20       20
Ddddd...  60          0        0         0   (for Dddd 1 contract just ended)

I was looking for the solution in this post: Return a column per date in a range but it is only generating 1 if the booking is active and does not sum.

Thank you very much for all the help.


texts: What's MongoDB's default behaviour when no sharded collection is specified

texts: When we deploy a Sharded Cluster, without having a sharded collection in our database, what is the default behaviour of MongoDB? How does MongoDB, distributes the data across the shards?


texts: Unable to create stored procedure to access excel files in SQL Server?

texts:
I need to create a stored procedure to access Excel files in SQL Server. I have created the below stored procedure. Since I am a sysadmin, the stored procedure is working fine for me.

But for the users who do not have SA privileges they are getting an error.

Could some one assist me on this, how the users can execute the stored procedure with my credentials (SYSADMIN)?

create procedure [dbo].[readexcel]
    (@filepath varchar(max))
as
    declare @Statement varchar(max)
    --set @Statement =
    EXECUTE('select * FROM OPENROWSET(' +
        '''Microsoft.ACE.OLEDB.12.0'',' +
        '''Excel 12.0;Database=' + @filepath +''',' +
        '''SELECT * FROM [Sheet1$]'')');

Error message:

  Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been
  denied. You must access this provider through a linked server


texts: Need to understand the drastic difference in execution plan between two queries

texts:
I have 2 queries.
    select guid=NEWID()
    into #guid

--update statistics #guid with all
--create statistics s1 on #guid(guid)

select * from Party a join #guid b on a.[Party_GUID] = b.guid --first select
select * from Party a join #guid b on a.[Party_GUID] = b.guid or b.guid is null --second select

drop table #guid

There is an index on Party.Party_GUID. The query execution plan for the first select is excellant. For the second select statement is horrible. I need to understand the reason for this. There is only one row in the #guid table and it has a value. So the query optimizer should be creating a similar execution plan for the second query too. Am I expecting too much of the optimizer ? I have tried this on 2008R2 as well as a 2012.

Admittedly this is a contrived reproduction of an issue that I am currently encountering in our system. The developers have coded a stored procedure with a table valued parameter which holds various combination of search values to be applied on a table. E.g the table valued parameter can have 3 nullable fields, GUID, LastName, FirstName and the application can populate it with values ((N'89241068-7068-4728-9CD0-A565FC2BFDEB', null, null), (null, "smith", "john"), (null, null, "jane")). The expectation is that the stored procedure apply it as a filter. e.g
    select * from Party a join @tablevar b on a.Party_GUID = b.guid OR b.guid is null
                                              and (a.LastName = b.LastName or b.LastName is null)
                                             and (a.FirstName = b.FirstName or b.FirstName is null)

One can argue that this is tough query to optimize but it-is-what-it-is currently and am trying to look at avenues to help the optimizer come up with the best query plans. I do understand that some inputs can result in a horrible plan. What I am trying to understand is why the "OR is null" clause degrades the execution plan so much even if there are indexes on the table.

I know there are dbcc traceflags which will help me understand why the optimizer chose a particular plan but i find those hard to comprehend.

Any help appreciated.


texts: Mongo: group query runs for hours then errors

texts:
I have a Mongo database with a collection with 1 billion rows. Each document has three fields, _id, Input and Hash. There are indexes on each of these fields.

I'm attempting to run the following query:

db.Collection.aggregate([ { "$group" : { _id : "$Input", count : { $sum : 1 } } }, { "$sort" : { "count" : -1 } }, { "$match" : { "count" : { "$gt" : 1 }  } }  ], { "allowDiskUse" : true  } )

I've tried to run it a few times and it runs for a few hours and then errors with:

QUERY    [js] uncaught exception: Error: command failed: {
        "ok" : 0,
        "errmsg" : "assertion src/mongo/db/pipeline/value.cpp:1365",
        "code" : 8,
        "codeName" : "UnknownError"
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:18:14
_assertCommandWorked@src/mongo/shell/assert.js:583:17
assert.commandWorked@src/mongo/shell/assert.js:673:16
DB.prototype._runAggregate@src/mongo/shell/db.js:266:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1012:12

What's going on here?

DB version is 4.1.2


texts: what is the efficient way of read mysqlbin log to keep informed about every event in sqldb?

texts:
I want to keep updated about tranformation happening in mysql db and based on each event i want to do some processing and send data to some rest api on other server.

My plan is :

Read mysqlbinlog using mysqlbinlog connector in java and do processing and send updates.

I wanted to know is it efficient and how to control the huge rate of updates in mysqldb. As reading from mysqlbinlogs is single threaded.


texts: Query a list of objects in each database

texts:
I have a list of about 300 Views spread out across several databases.
Some of the tables used in the DDL of the views have been dropped or renamed.
What I want to do is try to query all 300 views in each database.
If a query is successful, I want to output "Query Successful".
If a query fails because the object the view DDL is based on fails, I want to output "Query Failed".
The final output will look something like this
database_name     schema_name     view_name     status
db_1              dbo             view_1        Query Successful
db_2              abc             view_2        Query Failed

I know part of the solution will use the sp_MSforeachdb stored procedure, but I'm not sure what to do after that.
I have a list of all the objects I want to query stored in a table


texts: Anyone Know of Some Desktop Software that Searches an SQL server for a String

texts:
I have a database on a server that's running Microsoft SQL. I would like to find a tool that logs into that server, allows me to search to a string, and returns the tables and fields that contain that string. I"m not a programmer, nor do I know SQL. I saw someone using free desktop software that does this, but I've come to a Google dead-end. Does anyone know a software product that does this?

I don't think monitoring software is what I'm looking for. I might be wrong, but when I look at those products (RedGate, etc), they seem like overkill.


texts: Subquery or multiple left joins, or both?

texts:
I have two tables, for simplicity table1, with id as Primary key, and a field name, and table2 with the same.  Each table has quite a few records, but during a complicated form process, the user is presented with every record from table1, but depending on their choice in the form (done by radio, with id as the value) they may not have every result from table2 listed on the next part of the form.  This is based on a 3rd table, table3, with id as primary key, table1_id, table2_id as records, which lists "invalid combinations" from table1 / table2.

So, I'm trying to generate the sql for showing table2, which needs to NOT have any records that match the choice from table1, that are listed in table3.

Subquery Table3, with a where statement of the choice made on table1, and use that as a NOT NULL Left join on table2?  Every time I try to write the SQL I mess something up.


texts: The SELECT permission was denied - View querying data from a different database

texts:
I created a view SchemaX.VW_VIEW on a DatabaseB. This view is querying data from another database DatabaseA of the same server.

I have granted the SELECT permission on VW_VIEW to a user. However when the user tries to query the view, it says "The SELECT permission was denied on the object 'OriginTable', database 'DatabaseA', schema 'SchemaY'.

SchemaY and SchemaX are both owned by 'dbo' so there shouldn't be any break in the ownership chaining.

How can I fix this issue without granting read access to the underlying tables?


texts: Restoring a mysqldump loses user privileges for routines?

texts:
When I try and restore a mysqldump, the user privileges against some routines are missing. From what I can tell that is happeneing the routines that are in schemas that are loaded after the mysql schema have no user privileges. (eg. Schema "abc" has the correct user privileges for routines but in schema "qrs" they are missing.)

The dump I am using is created by:

mysqldump --allow-keywords --all-databases --single-transaction --events --routines --flush-logs --flush-privileges --hex-blob

When I have run into this issue in the past I would load the dump and then reload just the mysql schema, which then gives the missing users privileges on the routines.

However recently I have started using GTIDs and as a result I can't reload the just the mysql schema from the dump as the GTIDs overlap, as when I try to do so i get this error:

@@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

Is there a way to reload a dump made this way and have all the user permissions be exactly the same after reloading the dump on another server?


texts:
I have a SQL Server 2012 Std cluster that has a lot of writes going on. We'd like to offload some of the reporting to a separate (2012 Std) VM. It can be around 24 hours out of date.

I think log shipping is probably a good approach but I'm concerned I won't be able to compress the tlogs (they'll be big). My question is, can I manually set up log-shipping, essentially leaving the primary untouched and just restore 24 hours worth of transactions on the secondary each day - whilst still being able to read from the secondary during the day?

EDIT: After doing some testing and with the help from users here, it looks like I can just "manually" restore 24 hours' worth of backups each day by going SINGLE_USER then restoring all the tlogs WITH STANDBY, essentially log shipping but without touching the primary at all.


texts: Change MySQL 4.1 Windows to MySQL 4.1 Linux- Problem with the key Sensitive in query

texts:
I need help to resolve this problem.

change the server MySQL 4.1 run on Windows to MySQL 4.1 run on Linux(debian).

This all working, but same query not return the correct data because key sensitive<\h1>.

In windows server i type:

  selete * from acao;

or

  Selete * from ACAO;

this two query return data.

But on Mysql 4.1 on linux is difference.

Is key sensitive<\h1>,
if the table is difine on server "acao",

the query

  selete * from acao;

the query returns data, but if the query is:

  Selete * from ACAO;

I have more than 200 files with this query, so I do not have time to change all of this.

bests,
jgois


texts: Splitting a result row into multiple rows

texts:
I have the following query:

SELECT
       vs.MonthName AS Month,
       vs.Region,
       vs.Category,
       CAST (MIN (a.TotalAmount) AS money) AS ActualAmount,
       MIN (a.TotalCases) AS ActualCases,
       CAST (MIN (ve.BudgetAmount) AS money) AS BudgetAmount,
      MIN (ve.BudgetCases) AS BudgetCases
FROM
    dbo.VarianceSetup AS vs
    LEFT OUTER JOIN dbo.Actuals
       AS a ON vs.MonthNum=a.Month
                AND vs.RegionId=a.SalesRegionId
                AND vs.CategoryId=a.ProductCategoryId
                AND a.Year=2015
    LEFT OUTER JOIN dbo.VarianceReportEntries
       AS ve ON a.Month=ve.Month
             AND a.SalesRegionId=ve.SalesRegionId
             AND a.ProductCategoryId=ve.ProductCategoryId
             AND ve.Year=2015
GROUP BY
        vs.MonthName,
        vs.Region,
        vs.Category;

which gives me the following output.

+---------+-------------+----------+--------------+-------------+-------------+--------------+
|  Month  |   Region    | Category | ActualAmount | ActualCases | BudgetCases | BudgetAmount |
+---------+-------------+----------+--------------+-------------+-------------+--------------+
| January | New England | Cola     | 439365.6000  | 31200       | 0           | 0.0000       |
| January | New England | Juice    | 110051.7600  | 8424        | 0           | 0.0000       |
| January | New England | Water    | 800.0000     | 40          | 0           | 0.0000       |
| January | NY / NJ     | Cola     | 552583.0800  | 40692       | 0           | 0.0000       |
| January | NY / NJ     | Juice    | 139824.3600  | 10764       | 0           | 0.0000       |
| January | NY / NJ     | Water    | NULL         | NULL        | NULL        | NULL         |
+---------+-------------+----------+--------------+-------------+-------------+--------------+

But for some code I am working on, I need each row to be split into four rows and add a string identifier to indicate the type of row, like this:

+---------+-------------+----------+--------------+-------------+
|  Month  |   Region    | Category |     Type     |    Value    |
+---------+-------------+----------+--------------+-------------+
| January | New England | Cola     | ActualAmount | 439365.6000 |
| January | New England | Cola     | ActualCases  | 31200       |
| January | New England | Cola     | BudgetAmount | 0.0000      |
| January | New England | Cola     | BudgetCases  | 0           |
| January | New England | Juice    | ActualAmount | 110051.7600 |
| January | New England | Juice    | ActualCases  | 8424        |
| January | New England | Juice    | BudgetAmount | 0.0000      |
| January | New England | Juice    | BudgetCases  | 0           |
+---------+-------------+----------+--------------+-------------+

How can I accomplish that by tweaking my query -- OR by creating a query based on the View of this query?


texts: How functional dependency imply multi valued dependency?

texts:
I understand following informal definition of multi valued dependency:

  If multi valued dependency is α↠β, then whenever the tuples (a,b,c) and (a,d,e) exist in relation r, the tuples (a,b,e)  and (a,d,c) should also exist in r.

I also understand following formal definition:

  Let R be a relation schema and let α⊆R and β⊆R (subsets). The multivalued dependency α↠β holds on R if, in any legal relation r(R), for all pairs of tuples t1 and t2 in r such that t1[α]=t2[α], there exist tuples t3 and t4 in r such that
  t1[α] = t2[α] = t3[α] = t4 [α]
  t3[β] = t1[β]
  t3[R-β] = t2[R-β]
  t4[β] = t2[β]
  t4[R-β] = t1[R-β]

For example,

  t1 = (a,b,c)
  t2 = (a,d,e)
  t3 = (a,b,e)
  t4 = (a,d,c)
  α = first attribute which is a in all t1,t2,t3,t4
  β = second attribute
  t1[α] = t2[α] = t3[α] = t4[α] = a
  t3[β] = t1[β] = b
  t3[R-β] = t2[R-β] = (a,e)
  t4[β] = t2[β] = d
  t4[R-β] = t1[R-β] = (a,c)

I also understand following definition of functional dependency:

  Given a relation R, a set of attributes X in R is said to functionally determine another set of attributes Y, also in R, (written X → Y) if, and only if, each X value in R is associated with precisely one Y value in R.

Also I came across following points which distinguishes FD and MVD:

FD is equality generating dependency.
MVD is tuple generating dependency.

Now I came across following property of MVDs stated on wikipedia:

  If α→β then α↠β

I dont how both definitions imply above. To be specific, definition of α→β is not tuple generating or in other words, it does not enforce (a,b,e) and (a,d,c) should exist, given (a,b,c) and (a,d,e) exist, right?


texts: Does Oracle support the VALUES expression construct?

texts:
PostgreSQL supports the SQL spec's VALUES expression in what the spec calls a <table value constructor>

SELECT *
FROM (VALUES
  (1, 'one'),
  (2, 'two'),
  (3, 'three')
) AS t(num,letter);
 num | letter
-----+--------
   1 | one
   2 | two
   3 | three
(3 rows)

Trying it on dbfiddle, I can't get it to work in Oracle. I keep getting

  ORA-00903: invalid table name

Does Oracle support the VALUES ( expression [, ...] )? If not what is the alternative method of creating a virtual table from row-literals provided in the query?


texts: SQL Server: How much space needed to restore?

texts:
SQL Server 2016 Std

Our DB is 1TB of MDF, but there are 500GB free space in that MDF.

When we do a restore on a secondary (log shipping target), will the MDF there be 1TB, or ~500GB ?


texts: Mysql sync_binlog = 50 and innodb_flush_log_at_trx_commit = 2 with replication

texts:
I'm looking into sync_binlog = 50 and innodb_flush_log_at_trx_commit = 2 to improve write performance. There is this question, but it doesn't address the specific problems with replication.

I have an application that processes incoming log data with dozens of commits per second, causing a lot of flushing to disk, and losing one second of data is not a problem for the increase in speed it gives. However, The docs state:

  For durability and consistency in a replication setup that uses InnoDB
  with transactions [set both options to 1].

But it doesn't specify the issues with consistency and durability. I would like to know what exactly can go wrong. If you have a hardware crash and the slave becomes live, it may be one second behind the original master. Are there subsequent problems in getting that failed master back online (as slave)? Could there be mismatches in ID's, duplicate rows, etc?

As I said, loosing data is OK, but breaking replication is not.

Edit: BTW, by now, the problem is solved another way. Long story, but an comprehensive schema change made it possible run log processors in parallel. Still slow per process, but many at a time makes it fast.


texts: Select row from one query where opposites are not present in second query

texts:
Hopefully this explanation will clear up what the question is asking for ...

I have 2 tables (I'll populate them a little too):

inbox

---------------|-----------------
| to_USER_id   |   MESSAGE_id   |
---------------|-----------------
|       2      |         1      |
|       3      |         2      |
|       4      |         3      |
---------------------------------

message

---------------|----------------------------------
|     id       |  from_USER_id   |    content    |
--------------------------------------------------
|      1       |        4        |      Hey      |
|      2       |        1        |     Hello     |
|      3       |        2        |   How's it..  |
--------------------------------------------------

Basically, these are the tables that hold data when messages are 'sent' from one user to another. I need to work out a query (MySQL) that will return the message.id, which does not have an inbox.to_USER_id the same as the  message.from_USER_id (Basically, which message has not been replied to)

So, as you may be able to see, user 4 messages user 2. Then user 1 messages user 3. Then user 2 replies to user 4.

How do I query these tables to only return the message.id which has not yet had a reply. So, in this case it will return the row where a message was sent from user 1, which has not got a reply from user 3.

It's quite hard to explain ... But hopefully someone understands


texts:
I am hitting database performance issues, so we rebuilt all our indexes as we haven't been doing a great job maintaining them.
Rebuild ran for about 20 minutes.
Then I found a query to see how my indexes are looking

SELECT
t.NAME 'Table name',
i.NAME 'Index name',
ips.index_type_desc,
ips.alloc_unit_type_desc,
ips.index_depth,
ips.index_level,
ips.avg_fragmentation_in_percent,
ips.fragment_count,
ips.avg_fragment_size_in_pages,
ips.page_count,
ips.avg_page_space_used_in_percent,
ips.record_count,
ips.ghost_record_count,
ips.Version_ghost_record_count,
ips.min_record_size_in_bytes,
ips.max_record_size_in_bytes,
ips.avg_record_size_in_bytes,
ips.forwarded_record_count
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN
sys.tables t ON ips.OBJECT_ID = t.Object_ID
INNER JOIN
sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id
WHERE
AVG_FRAGMENTATION_IN_PERCENT > 0.0
ORDER BY
AVG_FRAGMENTATION_IN_PERCENT, fragment_count

So most of my indexes have an average percentage fragmentation <1
But I have about 10 that are 85%, and another 10 that are 100%

I am no dba, and understand very little about what I am doing here, but surely this is very bad?

Also worth noting that the 100%'s are all primary keys

Am I in a world of trouble, or is my understanding of the stats incorrect?

note: worth mentioning my database is 24gb, and my drive has 46gb free out of 115gb

EDIT: as requested reindex script below

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.['+name+']' AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
print @TableName
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR =     ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

results of original index fragmentation qry attached as requested
results


texts:
Assuming that I have a table, user. How do I calculate the sum of votes and paymentAmt each time they are being updated using trigger? Then, the votes and paymentAmt will be added to the point. I want to do it for every users (1 and 2) everytime the they are updated without selecting the specific user id.May i know how do i automatically update points for each user? My trigger does not update the table even after I alter the datas

1) user_ID | votes | paymentAmt | point (USER)
    1          10        20        30 <-- automatically updated
    2          25        30        55
    3          10        40        50

Trigger

CREATE Trigger upd_pt
AFTER UPDATE ON user
FOR EACH ROW
BEGIN
    UPDATE user
        set point = (select sum(votes) from user) + user.paymentAmt
        where user.user_ID = new.user_ID;
END


texts:
I am currently going through all indexes in our data warehouse as a performance tuning exercise. Several of these indexes are not used in the system stats, and I am considering removing them to reduce overhead during batch.

Some of these does not make sense, due to PK columns being used in the middle of other indexes, etc. However, I was wondering how SQL Server handles multi column indexes (with overlapping column).

Examples:

NONCLUSTERED INDEX Index1 (A, B, C)

NONCLUSTERED INDEX Index2 (A, B)

Would SQL Server be able to use Index1 for the same rows as Index2 and Index2 is not needed? I know the rowID are contained in the leaf level node of the B+tree, however all records for C would be valid in Index1.

Can Index2 be removed without impacting performance?
As far as I have understood, indexes can also help with sorts in execution plan.  Can Index1 be used for these as well?


texts: Join 1 column to multiple columns in another table

texts:
I have 2 tables portmap and devices

Portmap has the following columns.
ID (which is the PK),
networkDeviceID (FK to device table),
port,
timestamp,
description,
connectedDeviceID (FK to device table)

Devices has the following columns.
ID (which is the PK),
modelID (FK to the model table),
hostname,
serialnumber,
lanmac,
wmac

I'm having a mental block on how to get the correct corresponding hostname to display when doing a join. for example:

if row 1 in portmap table cotains the following
networkdeviceID = 2 and ConnecteddeviceID = 1

how do I get it to display the hostname for each IDvalue instead of just giving me the INT vaules

edit

how do I get the hostname from deviceID to show in the networkdeviceID column and in the conntecteddeviceID column?

edit2

maybe this might help you understand what I need.

I would like have the hostname mvl displayed in the networkdeviceID field and mvd displayed in the connectedDeviceID field on row 1. row 2 when then display mvd for the networkdeviceID and mvl for the connectedDeviceID field.

I need it that way so if say a end user decided to change the hostname mvl to say xyz1234 every record throughout the DB wouldn't automatically update to xyz123 instead of having to changing it by hand. or if in the portmap table they changed row2 networkdebiceID to 7 it would automaticlly display the device table hostname of mvdxxxxx

Do you get what I'm trying to do now?


texts: Calculate row value based on previous and actual row values

texts:
Hi everyone and thanks for your help.
I have the following situation: a table called statements that contains fields id(int), stmnt_date(date), debit(double), credit(double) and balance(double)

I want to calculate the balance following these rules:

The first row balance (chronologically) = debit - credit and for the rest of the rows

current row balance = chronologically previous row balance + current row debit - current row credit

As you can see on the picture above the rows are not arranged by date and that's why I used the word chronologically twice to emphasize on the importance of the stmnt_date value.

Thank you very much for your help.


texts: How to find historical sql_id executed by users

texts:
I'm trying to find historical sql_id executed by users within interval between snapshot_id from AWR. I'm using the view DBA_HIST_ACTIVE_SESS_HISTORY,DBA_USERS and DBA_HIST_SNAPSHOTS to try find the username,sql_id,sample_time etc.. but after some tests neither all sql executed was listed in output of the following query

col username for a30;
col BEGIN_INTERVAL_TIME for a30;
col END_INTERVAL_TIME for a30;
COL SAMPLE_TIME FOR A30;
set linesize 2000;
select * from (
select b.BEGIN_INTERVAL_TIME,b.END_INTERVAL_TIME,u.username,a.SESSION_ID,a.SESSION_SERIAL#,
a.INSTANCE_NUMBER,a.sql_id,A.SAMPLE_TIME from DBA_HIST_ACTIVE_SESS_HISTORY a
inner join dba_users u
on u.user_id=a.user_id
inner join dba_hist_snapshot b
on b.snap_id=a.snap_id
where b.snap_id >= 40 and b.snap_id <= 46
order by BEGIN_INTERVAL_TIME desc) where rownum <= 50;

Why is this happening? Is there some other way to do it?


texts: I want to move my SQL server from one domain to another

texts: I currently have two domains in my datacenter. Some time ago, it started to cause some inconveniences that I've decided to move my SQL server from one domain to another (where i have most of my servers). I'm already aware of the fact of changing the accounts SQL service run under. My concern is about services accounts. I have some of the other services (analysis, reporting, etc)  running under service accounts. What happens with these accounts when i disjoin my server from the current domain?


texts: First order by importance then group by section

texts:
In this table scheme http://sqlfiddle.com/#!9/101a9c/9/0

id  section     rev     importance
2   1           b       6
1   1           a       5
3   2           c       9
5   3           e       6
4   3           d       5

i want to order by **importance** then group by **section**

so i tried to use GROUP BY docs.section

SELECT docs.id, docs.section, docs.rev, (
  SELECT SUM(docs_importance.importance)
  FROM docs_importance
  WHERE docs_importance.doc_id = docs.id
) AS importance
FROM docs
GROUP BY docs.section    ---GROUPING
ORDER BY docs.section, importance DESC

but got this result:

id  section     rev     importance
1   1           a       5
3   2           c       9
4   3           d       5

but the result i want is supposed to be

id  section     rev     importance
2   1           b       6
3   2           c       9
5   3           e       6


texts: Count bookings and cancellations per member (select in subquery)

texts:
That will be my very first query using subquery ever but I already have problems.

Let's take a look at my tables:

user
   - id
   - name

user_visit
   - id
   - user_id
   - visit_type_id (BOOKED, CANCELED)
   - visit_starts_at
   - visit_ends_at

type_of_visit
   - id
   - type

And the result of it should be as follows:

['name' => 'John Doe', 'bookings' => 138, 'cancellations' => 30],
['name' => 'Jane Smith', 'bookings' => 47, 'cancellations' => 12],
['name' => 'Fleur V.', 'bookings' => 12, 'cancellations' => 1]

So it should count how many bookings and cancellations had user.

So far I have started with the following query:

SELECT
    u.name AS `name`,
    COUNT(booking.id) AS `bookings`
FROM `user` AS `u`,
      (SELECT
            *
        FROM `user_visit` AS `uv`
        INNER JOIN type_of_visit AS `tov`
            ON uv.visit_type_id = tov.id
        WHERE tov.type = 'BOOKED'
      ) AS `booking`
 GROUP BY u.id

Unfortunately it does not work properly because it was returning only one user, so something went wrong. Anyway you guys have more experience and you probably know how would you handle this, so anyone?


texts:
I have started working in a project which has a big database, around 300GB. For security reasons I can not access database from my local web app. So I need to copy last 100,000 from each table.

To copy from a table to another, I know I can do:

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

But how I can handle connecting to the other database?

One idea I have, it is to create a table, same structure, and use query above to move records and then dump those tables.

Is there a better way?


texts: How to make it impossible for a postgres user to delete databases?

texts:
What yould be the sql statement (that the user postgres will execute) so it will be impossible for the postgres user user1 to delete (drop) databases?

Or can I add a rule into some config file?


texts: how to increase column values by 20 percent using mysql

texts:
How do i increase the values in a column with percentage increment using MySQL,  for example:

ID      price
1       500
2       800
3       450

How do i increase the values in price column by 20%  using one sql query (MySQL)?


texts: Is it possible to configure SQLNet / TNS_ADMIN options *without* sqlnet.ora file?

texts:
Background:

We have an application using OC(C)I via Oracle 11 Instant Client, and we need to set DIAG_SIGHANDLER_ENABLED=FALSE to disable OCI overwriting our own crash handling.

However, this is a sqlnet.ora parameter, and we rely on the customer provided options in sqlnet.ora (and all other TNS_ADMIN stuff) when running the app, so we cannot and do not want to change the sqlnet.ora file at the TNS_ADMIN location, nor do we want to change our app to use a different, custom TNS_ADMIN.

Question:

Is there any way to set sqlnet related(?) options that normally go into sqlnet.ora either programmatically through some OCI interface, or maybe via an environment variable?

There are workarounds of course, we also could just copy everything from an existing TNS_ADMIN, but this question is specifically whether we can set any sqlnet.ora options without touching sqlnet.ora.


texts:
I'm developing a inventory system with optional serial no tracking (which users can turn on for items they select).

in order to keep stock records currently i came up with two options.

Option 01
use single stock table with following schema

dbo.stock{
stock_id,
item_id,
item_qty,      //for serial items this will always be 1
serial_no,
grn_id         //to keep track of GRN
}

e.g. if i have 5 TVs to track using serial no, i'll have 5 rows with serials and quantity as 1.
if i have 10 DVDs with no inventory tracking i'll have 1 row with qty =10

Option 02
use two tables as follows
table 01: keep track of inventory batches

dbo.stock_main{
stock_id,
item_id,
item_qty,      //for serial items this will always be 1
grn_id         //to keep track of GRN
}

table 02: keep the list of serials in this table for particular inventory batch

dbo.stock_serials{
stock_serial_id,
stock_id,
serial_no,
used           //1-used, 0-not used
}

each time serialized item is issued i'll check this table and prompt users to select a not used serial no.

all searches for serialized inventory database/design/model goes to various vendors of barcode systems so i couldnt find much info on web too.

is this the correct way of doing this? or am i missing something or there are smarter ways to address this? thanks.

EDIT
To add more details about my product setup,
Product:
Id: 1,  name: Samsung TV, barcode:6473647

All Samsung TVs have same barcode, but each TV has its unique serial no.


texts: jdbc equivalent to libpq "hostaddr" connection string parameter

texts:
Is there an equivalent to libpq's hostaddr parameter in the JDBC connection string? I want to connect via a local TCP proxy (on 127.0.0.1), but use ssl=verify-full with the CA bundle of the remote Postgres instance.

It doesn't seem to be obvious how to do this in the JDBC driver without some sort of class overloading of the HostVerifier?  Anyone tried this before?


texts:
Recently, one of my customers had a server crash. Unfortunately, the 3th party who was responsible for the backup was 'forgotten' to schedule a backup of the DB (I know, I should have notice that).

After a professional recovery, I have the .mdf and .ldf files back. I was able to attach them back to SQL Server 2005 Express. At first glance, everything looked ok.

However, the most recent changes (say from one month before crash) are missing. Has anyone seen that before?  Does anyone knows a solution for that? Is it possible that it is still in the .ldf?  I think the recovery model was Simple.

As a side note, I'm sure that the data was there since there is a printed version. I'm also sure that it is the most recent .mdf / .ldf that is recovered and sent.

I did already several DBCC checkDB with different parameters (every time on a fresh .mdf/.ldf combination of course). I know there is a consistency error somewhere but it doesn't block me to query the last 20000 rows. So expect to find the last records there (or I am wrong here?). The fix of this consistency error is lower prio since it occurs in the older data region. Worst case scenario, we can remove that part.

So, please help me with the missing data.

Thanks in advance!


texts: Can a Postgresql trigger send a row to an api via a post request

texts:
I have an application where it would be really nice to be able to use an insert trigger on a table to format and send a post request to an external API. This would save me having to write the code to do this work in another application.
I have had a look around and can see some information about using Postgres in a RESTFUL manner, but this seems to be mainly aimed at the database being the server for the api, which in this case isn't the direction I need.
I have thought about using NOTIFICATIONS to trigger an external app and have played with a nodejs app to handle these, which seems to work (as an aside, does anyone know of a way of subscribing to notifications from a golang app?) so that is an option too.
Any pointers appreciated.
Thanks.


texts: What program to use for the best compression ratio for DB2 log files?

texts:
What linux program to use to compress DB2 log files with the best compression ratio?

UPDATE

I thought that these log files would be text type files. But viewing them didn't prove this. I tried to use gzip and zip with up to 88% compression ratio. Which is pretty good.

Still my question is valid. Is there any 'recommended' program to compress DB2 log files?

UPDATE2
- results of my own comparision using maximum/best compression

 5.1M  7zip.mx9.7zip
 7.0M  tarbzip2.bzip2
 8.8M  targzip.tgz
 8.8M  zip.zip

when compressing two files

41M S0045979.LOG
29M S0045980.LOG


texts: score ranking no tie, if tie order by score submit date

texts:
I have a very simple MySQL table where I save highscores. It looks like that:

Id     Name     Score    SubmitDate
So far so good. The question is: How do I get what's a users rank EVEN THEIR SCORE IS TIE, I want them have diff ranking base on the submission date (older submission date have higher ranking)? For example, I have a users Name or Id and want to get his rank.

An Example

Id  Name    Score    SubmitDate
1   Ida     100      2014-08-01 21:37:17
2   Boo     99       2014-08-02 21:37:17
3   Lala    99       2014-08-03 21:37:17
4   Bash    102      2014-08-04 21:37:17
5   Assem   99       2014-08-05 21:37:17

In this very case, got 3 persons have the same score which are 99. Boo rank would be 3, then Lala, then come to Assem because I want to arrange by submit date.

The query should return one row, which contains (only) the required Rank.

Expected Result should be like below:

Id  Name    Score    SubmitDate              Rank
4   Bash    102      2014-08-04 21:37:17     1
1   Ida     100      2014-08-01 21:37:17     2
2   Boo     99       2014-08-02 21:37:17     3
3   Lala    99       2014-08-03 21:37:17     4
5   Assem   99       2014-08-05 21:37:17     5

Any idea how to accomplish this, I saw a lot solution have the same ranking but I do not want this output. Thanks!


texts: postgresql.conf file found in both /usr/share/postgresql and /etc/postgresql/

texts:
While troubleshooting another issue I noticed that in my Ubuntu 14.04 system there are two postgresql directories containing a postgresql.conf file:

/usr/share/postgresql
/etc/postgresql/9.3/main/

With the following directory structure:

$ tree -d /usr/share/postgresql
/usr/share/postgresql
├── 9.3
│   ├── extension
│   ├── man
│   │   ├── man1
│   │   └── man7
│   ├── timezonesets
│   └── tsearch_data
└── 9.4
    └── man
        ├── man1
        └── man7

... and:

$ tree -d /etc/postgresql/9.3/main/
/etc/postgresql/9.3/main/

With the postgresql.conf files residing in the following locations:

/usr/share/postgresql/9.3/postgresql.conf
/etc/postgresql/9.3/main/postgresql.conf

By using SHOW config_file; I have verified which configuration file my server is using:

postgres=# SHOW config_file;
               config_file
------------------------------------------
/etc/postgresql/9.3/main/postgresql.conf
(1 row)

My question is why do I find the postgresql.conf file in two locations and is the above an indication of some unused installation or other unused artifacts I ought to purge?


texts:
I have to create an XML File using sql

 <externalCodes>
  <externalCode>
   <system>CALYPSO</system>
   <extCode>cptname</extCode>
  </externalCode>
 <externalCode>
   <system>CMS-GDP</system>
   <extCode>cptname</extCode>
 </externalCode>
 <externalCode>
   <system>Manual</system>
   <extCode>cptname</extCode>
 </externalCode>

My SQL is as follows

(SELECT system, extCode from(Select 'CALYPSO' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 'extCode') a
FOR XML PATH(''), TYPE, ELEMENTS) AS "externalCodes/externalCode",

(SELECT system, extCode from(Select 'CMS-GDP' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 'extCode') b
FOR XML PATH(''), TYPE, ELEMENTS) AS "externalCodes/externalCode",

(SELECT system, extCode from( Select 'MANUAL' AS 'system', LTRIM(RTRIM(c1.cptname)) AS 'extCode') c
FOR XML PATH(''), TYPE, ELEMENTS) AS "externalCodes/externalCode"

My output comes like below:

    <externalCodes>
      <externalCode>
        <system>CALYPSO</system>
        <extCode>Mon</extCode>
        <system>CMS-GDP</system>
        <extCode>Mon</extCode>
        <system>MANUAL</system>
        <extCode>Mon</extCode>
      </externalCode>
     </externalCodes>

But the expected output is as below

   <externalCodes>
     <externalCode>
       <system>CALYPSO</system>
       <extCode>Mon</extCode>
     </externalCode>
    <externalCode>
       <system>CMS-GDP</system>
       <extCode>Mon</extCode>
     </externalCode>
     <externalCode>
       <system>MANUAL</system>
       <extCode>Mon</extCode>
     </externalCode>
    </externalCodes>


texts: Is there a way to find out who changed the password for a login?

texts:
I am trying to find out who changed the password for a login in SQL Server 2008 R2.

I have already checked default trace - and it does not log that event. The default trace will include these security-related events:

/*
    Audit Add DB user event
    Audit Add login to server role event
    Audit Add Member to DB role event
    Audit Add Role event
    Audit Add login event
    Audit Backup/Restore event
    Audit Change Database owner
    Audit DBCC event
    Audit Database Scope GDR event (Grant, Deny, Revoke)
    Audit Login Change Property event
    Audit Login Failed
    Audit Login GDR event
    Audit Schema Object GDR event
    Audit Schema Object Take Ownership
    Audit Server Starts and Stops
*/

Also, looked into transaction log backup to find that out, but no luck.

Is there any other way to find it out ?

Also, I am aware that a server side trace will help, but unfortunately in our server side trace, we did not include the Audit Login Change Password Event.

Best article that I found is from Aaron Bertrand : Tracking Login Password Changes in SQL Server


texts:
I have a Web forms asp.net site running with a database on a VM (VM1). I also have a MVC 5 Web app that has its own membership database on another VM (VM2). I want to retrieve customer information from VM1 to create a login on VM2.

My thoughts were I could create a backup database(or mirror) for VM1 to run member queries on that to retrieve information. Is this plausible? What methods would you recommend?

One method that I think I am able to obtain, would be creating a webservice on VM1, but it just seems the VM2 would be constantly using this webservice and I really want to obtain the best performance possible. Any help is much appreciated.


texts: Backed up MySql folder… upgraded to newer version and cant seem to recover data

texts:
I copied the entire MySql folder mysql-8.0.13-macos10.14-x86_64 and installed a newer version mysql-8.0.17-macos10.14-x86_64.

I should have researched but thought the installer would handle moving the data over (I only backed up as a precaution).

After installing my older MySQL folder only has 'data' and 'keyring' and the new the obvious. My backed up folder seems to have everything the old MySQL did before the new install.

Is there a way to get the older data?

I've tried copying the data folder to the new install folder, data & keyring.

I'm on Mac. upgraded from MySQL 8.0.13 to 8.0.17

Old MySQL Directory:
/usr/local/mysql-8.0.13-macos10.14-x86_64/

Now after installing the update:
/usr/local/mysql-8.0.17-macos10.14-x86_64/


texts: "Cluster nodes" command showing different status on different Redis Cluster nodes


texts: Difference between OCCI and Instant Client package Oracle

texts:
I am a bit confused. I want to use Oracle Call Interface in C++ to interact with databases. This link: http://oradim.blogspot.co.uk/2009/07/getting-started-with-occi-windows.html says you need such packages:

OCCI 11.1.0.6 (Visual C++9 (VS 2008)[Windows 32-bit])
Instant Client Package - Basic: instantclient-basic-win32-11.1.0.6.0.zip
Instant Client Package - SDK: instantclient-sdk-win32-11.1.0.6.0.zip

The 2nd and 3rd I have downloaded from here: http://www.oracle.com/technetwork/topics/winsoft-085727.html.

My question is basically: What is the number 1 (OCCI 11.1.0.6) item on the list and where can I download it from? I have been looking for solution for this problem, but could not figure it out. Help appreciated. Is the first item separate from 2nd and 3rd items?


texts: SSAS different results between database process-full and dimension process-update

texts:
I have an Analysis services database with several cubes in it.  We have a nightly job that extracts data from our source database to some staging tables, and another that performs a "Process Full" of the entire SSAS database.  After these jobs are complete, one particular cube has a dimension that contains 6 elements, all of which are incorrectly labeled.  (It's supposed to have values "M1 - name1" through "M6 - name6", but instead it gets "M1 - " (code, but no name value) and then 5 " - " (neither code nor name).
I can fix this by doing a "Process Update" of just that dimension, followed by a "Process full" of the cube that uses the dimension; the dimension will then contain all the expected labels.

What I don't understand is why the database-wide "Process Full" is mucking up the dimension in the first place and why a "Process Update" gives me a different result.

I read on a Microsoft SQL BI blog that a ProcessUpdate

  does not discard the dimension storage contents.  It applies the changes in a "smart" manner that preserves the fact data in dependent partitions.

but since these processes are being run in sequence with no changes to the cube and no changes to the underlying data in between, I still don't get how the Update can produce changes to the dimension.

This process is completely repeatable:  any time that I just process-full the cube, the dimension gets mucked up.  Process-update the dimension and then process-full the cube, the dimension is good.

So,
(a) How can the Process-full produce different results than the Process-Update on the dimension when they are both operating on the same data?
(b) Is there a better way to get to my desired cube state than tacking on steps to my nightly job to do the "process-update dim/process-full cube"?

Here are a couple of excerpts from the processing logs, where you can see that the queries are identical but the results are not...

from the Update:

    Processing Dimension Attribute 'Facility' completed. **7 rows have been read.**
            Start time: 5/31/2017 9:11:19 AM; End time: 5/31/2017 9:11:19 AM; Duration: 0:00:00
            SQL queries
                SELECT DISTINCT
 [dbo_Dim_WorkCenter].[Facility] AS [dbo_Dim_WorkCenterFacility0_0],[dbo_Dim_WorkCenter].[Facility id name] AS [dbo_Dim_WorkCenterFacility_x0020_id_x0020_name0_1]
  FROM [dbo].[Dim_WorkCenter] AS [dbo_Dim_WorkCenter]

from the Full:

            Processing Dimension Attribute 'Facility' completed. **18 rows have been read.**
            Start time: 5/31/2017 9:14:56 AM; End time: 5/31/2017 9:14:56 AM; Duration: 0:00:00
            SQL queries
                SELECT DISTINCT
 [dbo_Dim_WorkCenter].[Facility] AS [dbo_Dim_WorkCenterFacility0_0],[dbo_Dim_WorkCenter].[Facility id name] AS [dbo_Dim_WorkCenterFacility_x0020_id_x0020_name0_1]
  FROM [dbo].[Dim_WorkCenter] AS [dbo_Dim_WorkCenter]


texts: How can I insert records 'into null' in Oracle?

texts:
I'm looking for a way to insert records into a 'black hole'; i.e. execute a query for a certain table successfully, but make the records disappear. Something like INSERT INTO NULL, if you will. MySQL has the BLACKHOLE engine, can Oracle do something similar?

The only thing I could come up with was:

creating a view with SELECT NULL FROM DUAL
creating a trigger with INSTEAD OF INSERT and BEGIN NULL; END;

Is this okay?

Obviously, the optimal way is not to execute the insert statement, but I have no control over this - I have to fix this in the DB.

What's the best way to handle this? (the solution with the least impact on performance)


texts: How can I grant a user create/alter index permissions without giving db_ddladmin? I want them to have the minimum permissions possible.


texts: Windows Server 2016 Active Directory-Detached Cluster - Cannot add a Client Access Point

texts:
I am setting up a Windows Server 2016 cluster detached from an active directory (so called Workgroup Cluster, i.e. nodes are not added to an active directory). I cannot add a Client Access Point to the cluster. The error is Unable to determine if the computer 'client_access_point_name' exists in the domain 'WORKGROUP'.

How a client access point can be added to the cluster?

P.S.: I need to add a client access point to use it later in SQL Server Availability Group Listener, to use it for auto-failover between mirrors.


texts:
How to convert timestamp / rowversion to a date and time in SQL Server?

I have several tables where I would like to track the last update, with column names like ModifiedAt, UpdatedAt, UpdatedDate.

Would this be possible if these were with a Rowversion data type or Timestamp data type?

as in

CREATE TABLE dbo.Customer (
    CustomerID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    CustomerName NVARCHAR(200) NOT NULL ,
    CreatedAt DATETIME2 NOT NULL CONSTRAINT df_Customer_CreatedAt DEFAULT GETUTCDATE(),
    UpdatedAt ROWVERSION NOT NULL /* does this work ? */
)


texts: Change column values but keep order in MySQL

texts:
Lets consider I have a table:
CREATE TABLE student (
id int,
 name varchar(128)
);

INSERT INTO student (id,name) VALUES
(1,'John'),
(2,'Bob'),
(3,'Alice'),
(4,'Don'),
(5,'Eve');

I need the 'names' column to exchange places with next one: 1 with 2, 3 with 4 and etc, but keep the order of 'id'. The selected output should be something like this:
id  names
1 | Bob
2 | John
3 | Don
4 | Alice
5 | Eve

Is there an effective way to write query that would give me expected result?


texts: MYSQL Subquery in columns of select statement

texts:
How are subqueries in the column field of select (projection) paired with the result of the main query? in the form:

 SELECT id,email,(SELECT name From Names WHERE Names.id=Users.id) as name
 FROM Users

Is the subquery executed once per row, from the output of SELECT id,email FROM Users,and thus, one should use LIMIT 1 on the subquery (since only 1 row from the subquery can be paired with a row from the main query), or does the subquery run once, and then each result is paired with the corresponding row from SELECT id,email FROM Users, much like the equivalent join: SELECT id,email,name FROM Users JOIN Names ON Users.id=Names.id


texts: "Merge" two rows in a Postgres table, with foreign keys

texts:
I am keeping a database of books I've read, using the following two tables in PostgreSQL:

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name text
);

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title text,
    author_id integer REFERENCES authors(id) ON UPDATE CASCADE ON DELETE CASCADE,
    UNIQUE(title, author_id)
);

Now when going through my list of authors, I found the following two entries:

id | name
----------
 1 | Mark Twain
 2 | Samuel Clemens

What I'd like to do is delete the "Mark Twain" entry, and effectively update all books referencing "Mark Twain" to reference "Samuel Clemens". I know I could do this manually, but I want a solution that works, regardless of which tables are referencing the authors(id)

I thought about doing it like this (within a transaction):

Change Mark Twain id to 2, letting UPDATE CASCADE take care of changing the references.
Delete Mark Twain entry

But this runs into a few problems, mainly:

The first step creates a duplicate primary key
I'm not sure how to reference the right row to delete, once they both have the same ID!
The DELETE CASCADE worries me for the second step

There's also a subtler problem, that can be illustrated with a portion of my (poorly curated) books table:

id | title              | author_id
------------------------------------
 1 | "Huckleberry Finn" | 1
 2 | "Huckleberry Finn" | 2

Here, even if my two-step process succeeded, I would be violating the UNIQUE contstraint on books.

Is there a way to do this, and work around most/all of these issues? Using Postgres 9.4.


texts: How to GRANT the USAGE privilege on SCHEMAS that will be created in the future

texts:
I need a role to access a schema owned by another role, which can be dropped and re-created in the future. For the first part of my requirement, i.e. for an existing schema, the following would do:
GRANT USAGE ON SCHEMA existing_schema TO my_role;

For the second part of the requirement, i.e. for schemas that don't exist yet but could be created in the future, the ALTER DEFAULT PRIVILEGES command, as documented here, looked promising:
ALTER DEFAULT PRIVILEGES FOR ROLE my_role
  GRANT USAGE ON SCHEMAS TO my_role;

but it doesn't work as expected, i.e. after creating a new schema owned by another user:
CREATE SCHEMA new_schema AUTHORIZATION other_role;

and trying to access it as my_role I get:
my_role=> SELECT * FROM new_schema.some_table;
ERROR:  permission denied for schema new_schema

What am I doing wrong here?
PS I am aware of this answer but modifying system databases, such as template1 is not an option for me since I am neither the owner nor the only user of the database cluster in question.


texts:
I have the following data

JoinFlag    ReferenceType   ReferenceValue
A            CARRIER        Sample#######
A            LEASE_NUMBER       17438
A            LEASE_NUMBER       6054
A            STATION            ENERWST
B            LEASE_NUMBER       17704
B            LEASE_NUMBER       17711
B            STATION             RJ
B            STATION             C5

I would like to know how to write a SQL query in TSQL to get the following output

JoinFlag    ReferenceType   ReferenceValue
A            CARRIER        Sample#######
A            LEASE_NUMBER     17438, 6054
A            STATION          ENERWST
B            LEASE_NUMBER     17704, 17711
B            STATION          RJ, C5

The SQL should be generic.

The Query that I using is

SELECT [CustomTicketIgnoreList].JoinFlag,
   STUFF((SELECT ', ' + ThisTable.ReferenceType
            FROM   [CustomTicketIgnoreList] ThisTable
            WHERE  [CustomTicketIgnoreList].JoinFlag = ThisTable.JoinFlag
            AND    ThisTable.ReferenceType <> ''
            FOR XML PATH ('')),1,1,'') AS ConcatenatedSomeField,
    STUFF((SELECT ', ' + ReferenceValue
            FROM   [CustomTicketIgnoreList] ThisTable
            WHERE  [CustomTicketIgnoreList].JoinFlag = ThisTable.JoinFlag
            AND    ThisTable.ReferenceValue <> ''
            FOR XML PATH ('')),1,2,'') AS ConcatenatedSomeField2
FROM [CustomTicketIgnoreList]
GROUP BY JoinFlag

Regards


texts:
I've got a small database with 5 tables: 3 of them are resources (material, skill and tool) and the other 2 are tasks (corrective maintenance and preventive maintenance).
Every table has a primary key with a code, and the task tables has a column for every resource code. My problem is that in some tasks, there is the possibility of having many resources of one type (2 materials, 3 skills or 6 tools). What is a good way of implementing this into an SQLite database? Creating many columns is not a good idea because there is not fixed number of resources that are needed.
Thank you in advance!


texts: Why does insert on duplicate-key error create a share lock?

texts:
In the mysql docs is the following example for locking on insert:

INSERT sets an exclusive lock on the inserted row.  [...] If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock.

This is proceeded with the following example:
CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

Session 1:
START TRANSACTION;
INSERT INTO t1 VALUES(1)

Session 2:
START TRANSACTION;
INSERT INTO t1 VALUES(1)

Session 3:
START TRANSACTION;
INSERT INTO t1 VALUES(1)

Session 1:
ROLLBACK;

The following would happen:

The first operation by session 1 acquires an exclusive lock for the row. The operations by sessions 2 and 3 both result in a duplicate-key error and they both request a shared lock for the row. When session 1 rolls back, it releases its exclusive lock on the row and the queued shared lock requests for sessions 2 and 3 are granted. At this point, sessions 2 and 3 deadlock: Neither can acquire an exclusive lock for the row because of the shared lock held by the other.

This makes me wonder, why does one first require a share lock just to proceed with an exclusive lock? Would it not make sense to change the behavior to:

INSERT sets an exclusive lock on the inserted row. [...] If a duplicate-key error occurs, an exclusive lock on the duplicate index record is set.

If one would handle it like this, then in the above example, there would be no deadlock and either session 2 or session 3 (depending who gets the exclusive lock first) would try to proceed. Assume session 2 would lock first and then fail and rollbacks aswell, then the session 3 would try to insert. Would that not make much more sense?


texts: Is connection string's default database used if queries are run against other databases?

texts:
Let's say I have an app that uses this connection string to connect to a SQL Server using "DB1" as the default database (a.k.a. initial catalog):

Server=myServerAddress; Database=DB1; User Id=myUsername; Password=myPassword;

But all queries executed through this connection target other databases, never DB1. (Example: SELECT * FROM DB2.dbo.Table1)

Can/will DB1 possibly be used for anything, even though it is not referenced in any queries? I'd like to hear about all possibilities no matter how obscure. (For example, could there be differences in options, cursors, table locking, query performance...?)

I would assume not, except that today I saw strange performance issues with DB1 that are making me second guess my assumption. It could be a red herring but regardless I'd like to make sure I fully understand the role of the default database / initial catalog.


texts: Backup from Server1 and restore it to Server2?

texts:
IF we have two Servers installed with SQL Server 2008 instances,
Server1 has three drives C, D and E with 100, 500 and 500 GB respectively
and Server2 has three drives C, D and E with 100, 300 and 300 GB respectively.
Now you have database1 on Server1 with data file 250 GB on D drive and Log file 350GB on E drive, its recovery model is full and replication has been setup
on this database, what is the best way to take backup from Server1 and restore it to Server2?

Problem is server2 has 300GB space but on Server1 has 350GB log file.


texts: Replicating Multiple SQL Servers to Azure and Accessing From Web Apps

texts:
I am setting up quite a large system with multiple servers (MS SQL Server), and Office 365 Access DB, and a custom SharePoint web app for reporting purposes.

The system should work as follows:

Each location has a singular server that stores information pertaining to that business instance. A client application allows insert/update/delete operations through a GUI for the business, and manipulates the server database (running MS SQL Server).

Each location's database is replicated (one way) to the cloud (MS Office 365 which uses Azure), so that all of the different locations information can be accessed from the web.

A custom web app can then query the cloud database and produce reports (mainly charts for analysis). This app will only have read permissions on the data - no modification will happen at this end.

My question is if and how I can replicate the separate servers to the cloud. As far as I understand, Microsoft's web apps platform can tie an application to only a single database.

Does this mean that I must replicate all of the databases to a singular, all-encompassing database on the cloud? Is this possible? For manageability reasons, it would be great to mirror each local database to a matching database on the cloud, and then somehow aggregate these cloud databases into a singular one for the web app to access. Could this be done through sharding / partitioning?

Am I correct in saying that even custom written web-apps can access only one database through this system? If I am incorrect, how hard is it to tie an app into multiple databases?

Sorry, I am fairly inexperienced in this field and will really appreciate any guidance!


texts: What is a secure strategy for managing login/passwords used in a powershell script

texts:
I have several Powershell scripts that need to write data to an Oracle database.  I do not want to hard-code the login credentials.

When connecting to SQL Server I rely on windows authentication and Active Directory, but that is not an option here because the Oracle database is on a separate network (Unix).  The scripts will be executed on a SQL Server via SQL Server Agent.

I am hoping to come up with a strategy that will be secure and will minimize the work involved when it comes time to change the password.


texts:
I've learned that epoch (UNIX timestamp) is a unique independent amount, so I am baffled by output I get from the following queries:

select  extract( epoch from current_timestamp::timestamp with time zone at time zone 'Asia/Tehran');
1588605420.89165

select  extract( epoch from current_timestamp::timestamp with timezone at time zone 'UTC');
1588589296.18219

which there is a deficit of 16200 (4 and a half hours). Why is that when it explicitly declares that current_timestamp is in a specific time zone. Should both epochs (UNIX timestamp) not map to the same amount of seconds?

what I don't get maybe better displayed on contrast with this small python code:

>>> import time
>>> import pytz
>>> import datetime
>>> teh_tz = pytz.timezone('Asia/Tehran')
>>> utc_tz = pytz.timezone('UTC')

>>> datetime.datetime.now(teh_tz)
datetime.datetime(2020, 5, 4, 15, 31, 11, 14869, tzinfo=<DstTzInfo 'Asia/Tehran' +0430+4:30:00 DST>)
>>> datetime.datetime.now(utc_tz)
datetime.datetime(2020, 5, 4, 11, 1, 16, 198437, tzinfo=<UTC>)

>>> datetime.datetime.now(teh_tz).timestamp()
1588590087.590199
>>> datetime.datetime.now(utc_tz).timestamp()
1588590091.901826

As it's seen, different time zones have differences in their current time but all map to the same amount of seconds (UNIX timestamp).
I guess I'm totally lost in here, please save me.


texts: AlwaysOn read only replica slower then prime

texts:
I’m running into an issue. My readOnly replica (on Node B) takes twice as long to execute a query as on my primary (Node A).
But when I fail over to Node B (which becomes my primary) it runs fine, but then it runs slow on the new replica so I don't think its the hardware.

I checked the index fragmentation and they are identical and there is currently nothing running on the servers except my query.
Execution Plans are identical.
The power is set to High Performance.

We are running SQL 2017 on windows 2016 server.

Any suggestions on what else to check?


texts: Basic PostgreSQL 9.x Performance Optimization Strategies for Reads/Analysis

texts:
Postgres comes, out of the box, configured as a general purpose database for both OLTP and OLAP types of loads. My need is to do use Postgres, in this case, purely as an OLAP running aggregate queries over few relatively large tables.

I am looking for instructions how to adjust default configuration parameters relating to buffers and cacheing, including OS settings (Linux) for page sizes, so that the database can handle large sorts, joins, projections and filters more efficiently than out of the box, given the resources on the system.

I have seen several tuning and tweaking best practices but these are still general purposes (addressing WAL, transaction synchronisation, etc.), where I am mostly interested in maximizing read, sort and join performance by adjusting the parameters that related to these.

I have a 4-core server with 64 GB of memory and about 500 GB of SAS-2 drives.

My dataset consists of about 8-10 tables of which some are 100 MB, and some are about 20 GB.

This dataset will be used for read-only analysis with direct querying of the individual tables, or some complex queries joining two or three tables.

Tables should be partitionable by month or year, at least.

Given the hardware I have, and the fact that we are accessing few relatively flat but large tables, could someone please recommend some basic but specific Postgres 9.4 or 9.5 optimizations that would maximize the performance of the read-only queries and the database.


texts: Should I save "document ID" as a String or Number in MongoDB

texts:
So I'm importing about a 100,000 documents into mondoDB. MongoDB already generates an Object hash but the data has a documentID that was used to identify records in the previous database.

The IDs look like '45454343','3434', and so on. Should I keep them as String or Numbers? Is there a general rule when making this decision?


texts: Is it acceptable to have a surrogate/primary key of one table be the primary/foreign key of another table?

texts:
For my purposes I'm trying to apply this to Microsoft SQL, but would like to know if there are restrictions for other DBMS regarding this.

I don't know how to represent UML as ascii characters so if someone has a tool suggestion to do so please add in comments.

Example of the scenario below.  Note that I'm using composite keys.

building
------------------------------------
+ buildingFullName:String(30) <<PK>>
+ buildingId:Int <<SK>> <<PK>>

buildingRoom
------------------------------------
+ buildingId:Int <<PK>>  <<FK>>
+ roomNumber:String(10) <<PK>>
+ roomId:Int <<PK>> <<SK>>

buildingRoomAsset
-------------------------------------
+ roomId:Int <<PK>> <<FK>>
+ roomAsset:String(9) <<PK>> <<SK>>


texts: Newdatabase option is not available in sql server management studio 2014

texts: I connect to analysis server using windows authentication. But I can't create the new database.


texts: Check if two tables have the same structure (are compatible)

texts:
I was wondering if there is an established way to test if two PostgreSQL tables/relations have the same structure. In other words, how to test whether they are compatible with each other in the sense that I can perform set operations such as UNION ALL and EXCEPT on them?

I searched around on DBA.SE and elsewhere, and can only find questions about finding whether the contents of two tables are different, (e.g. Checking whether two tables have identical content in PostgreSQL), or when the compatibility is known (e.g. Compare two tables with same structure, but differing membership numbers). But I'm interested in checking the compatibility of table structure.

I'm using PostgreSQL 10.3, but standard compliant ways are certainly preferable.


texts: Concurrent usage of rdbms, write 4K rows without lockdown

texts:
I am working on a django project using Postgresql. The use case is as follows:

A user needs to upload an excel (containing 2.5-4K rows) 2-3 times daily. There will be 100s of users (somewhere between 1100-1200).

Now, the issue is the app is taking 1.2-1.8 seconds to write the data in the db. During this time, anyone else viewing the webapp is seeing a "server busy" error.

How can I get around this issue, so that:
1. Write speed can improve
2. Other users can use the webapp and maybe in worst case, we can also have concurrent uploading of excel possible?


texts: Dynamic SQL Stored Procedure called by a Function

texts:
I want to be able to dynamically call a function that contains a select statement whose table changes depending on the function parameters.  I know this is not possible to do within a function, but I have read that it is possible within a stored procedure.  I made the following stored procedure to execute a string:

CREATE DEFINER=`uname`@`%` PROCEDURE `execute_Dynamic_Query`(IN dynamicQuery varchar(255), OUT resultValue varchar(255))
BEGIN
    SET @queryString =
        CONCAT(dynamicQuery);
    PREPARE stmt FROM @queryString;
    EXECUTE stmt;
    SET resultValue = @selectedVar;
END

This function seems to work. When I call it, I see the expected value (1 returned row):

CALL control.execute_Dynamic_Query("SELECT date from test order by date desc limit 1;",@TestVar);

However, if I call this from within a function at all, the function suddenly stops working.  For example, this works and returns "1":

CREATE DEFINER=`uname`@`%` FUNCTION `new_function`() RETURNS varchar(255) CHARSET latin1
BEGIN
    RETURN 1;
END

But this returns nothing and the "Message" that I see in Workbench is just "Ok" (with a green check mark).

CREATE DEFINER=`uname`@`%` FUNCTION `new_function`() RETURNS varchar(255) CHARSET latin1
BEGIN
    CALL control.execute_Dynamic_Query("SELECT date from test order by date desc limit 1;",@TestVar);
    RETURN 1;
END

The ultimate goal is to return the the results in a function similar to this:

CREATE DEFINER=`uname`@`%` FUNCTION `new_function`() RETURNS varchar(255) CHARSET latin1
BEGIN
    DECLARE outputVar DATETIME;
    CALL control.execute_Dynamic_Query("SELECT date from test order by date desc limit 1;",@TestVar);
    outputVar = @TestVar;
    RETURN outputVar;
END

I am using MySQL Workbench 6.3 and am disappointed with the unhelpful output that I'm seeing.  Any ideas?


texts: How to create unique index for month and year column?

texts:
Does anyone have any idea how do i create unique index for both month and year column?

As in: JAN 2012 should be an unique index and also to aid in searching and retreiving of results by month and year.

Thanks


texts: SSRS ReportServer_XXX.log is giving incorrect CPU count

texts:
I am currently investigating an issue wherein sometimes the SSRS just suddenly stops accepting requests until we do an IISRESET command. This happens after we added an additional processor on the server. No new report is added on that time. Right now we have 2x Quad Core with HT enabled processors. Information in 'sys.dm_os_sys_info' is giving me the correct cpu_count which is 16. However, while looking at the 'ReportServer_XXX.log', I noticed that it's logging incorrect cpu count.

w3wp!resourceutilities!1!3/22/2014-14:29:16:: i INFO: Reporting Services starting SKU: Standard w3wp!resourceutilities!1!3/22/2014-14:29:16:: i INFO: Evaluation copy: 0 days left w3wp!resourceutilities!1!3/22/2014-14:29:16:: i INFO: CPU throttling turned on w3wp!resourceutilities!1!3/22/2014-14:29:16:: i INFO: Running on 127 physical processors, 255 logical processors

I am not certain if this is the cause but either way, how do I correct this issue? The count is way off.

Also, on our other server, the CPU throttling turned on message is not being logged. Could also be this another reason?

SSRS is running under this environment:

Windows Server 2003 R2 32-bit (PAE enabled)
SQL Server 2005 SP4 (AWE enabled)
2xQuad core with HT processors
64GB memory


texts:
When trying to install SQL Server 2008 R2, I get the following error:

Error installing Microsoft SQL server 2008 R2 with tools! Error code: -2068578303

Does anyone know what this means?


texts: Mixing 2 and 3 node AG on a single WSFC?

texts:
I have a 3 node availability group (Enterprise 2014), it is multi subnet, with two data centers and file share witness in a third data center. There are about 20 "production" database that exist on the Primary, Reporting and DR instances, via an AG with a listener. Currently configured, async with manual failover.  Production is about 1TB, active OLTP.

The application points to the listener, reporting points to Report Server.

We get HA by temporarily changing to sync-commit and failing over.  Reporting is fine with async as is DR (risks understood)

There is a "Reportsdb" that only needs to exist on the reporting database. I have used a 2 node AG with async to have it available on DR. Reportsdb is about 50GB, with a rebuild from scratch time of a few hours.

The DR goal is have reporting on a seperate server as long as any two are up.  With two down, there is no reporting.

Question: If we change to sync and automatic failover; what if any issues will having mixed 2 and 3 node AGs on one WSFC have?


texts: Validation logic for Mobile Number Column in SQL Server

texts:
I am creating a validation script and need help with mobile number validation logic with below details.  Mobile number must be:

10 digits in length.
Cannot begin with a 1 or a 0 (For example:1144684679 or 04468-4679
Cannot contain all the same digit.  For example: 5555555555

I am using PATINDEX for this but could not come up with the proper result.


texts: Retrieve 16 digit card numbers from a string

texts:
I have a large database table, which contains records of users accounts. Back in 2011 a lot of notes were added to the accounts, which contained the full debit card details.

How can I find all 16 digit card numbers in the table? They are all entered differently.


texts:
I have tried to search online on how to interpret these ternary relationships but haven't found any clear answer. Since I don't understand how to interpret it I am having trouble changing it into relations as well. In an example like this:

How exactly would I interpret this? For instance:

The line with an arrow going into Works_in3 from the Employee entity, is that pointing to the Departments entity or the Locations entity? So is it implying that an Employee can only work in one department or is it implying that an employee can only work in one location or does it imply both?


texts: How can I convert integer value from query_hash_s in Azure Log Analytics to a VARBINARY(64) for sys.dm_exec_sql_text?

texts:
SQL Server errors are shown in Azure Log Analytics. Sometimes, the query_hash_s column is populated. But the values provided appear to be BIGINT. How can I use that value to find the actual text of the SQL statement using sys.dm_exec_sql_text? Simply converting the value to a VARBINARY(64) doesn't work.

Example value: 621553112374777528

SELECT CONVERT(VARBINARY(64), 621553112374777528)

results in 0x12000001B81623384333A008, whereas sys.dm_exec_sql_text is looking for a value that looks more like this (this is a different value, shown only to illustrate the length difference):

  0x02000000E5F7E122B77841FB561786311AFC20E00196B0D30000000000000000000000000000000000000000


texts: I need advise on if I should rebuild my index

texts:
I have a SQL Server 2008 Standard server on full mirror (safety). I reorganize all indexes weekly. I am looking into rebuilding a few indexes to improve performance. I ran DBCC SHOWCONTIG on my largest, most accessed table and got the following results.

DBCC SHOWCONTIG scanning 'MYTABLE' table...
Table: 'MYTABLE' (12345678); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 738663
- Extents Scanned..............................: 92661
- Extent Switches..............................: 92720
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.58% [92333:92721]
- Logical Scan Fragmentation ..................: 0.35%
- Extent Scan Fragmentation ...................: 41.58%
- Avg. Bytes Free per Page.....................: 467.9
- Avg. Page Density (full).....................: 94.22%

Would rebuilding the index make sense on this table? Would there be any benefits to doing so? I ask because this entails downtime and I am not sure if it is necessary. Plus, the extra log size due to the index could effect the mirror.


texts: How to calculate running total based on dynamic values?

texts:
I need to calculate the running total in which the target column is changing along with the time. Its something which aws does. Charging based for only what you use. So how do I aggregate sum of payments and charges.

Currently I'm storing the running total in the balance column and that sum is calculated using sum of charges - sum of payments.

Here's what the table looks like

      Invoices
id    tx_type    amount   live
 1     Charge     100      true             // live = true means that the amount will be updated and the meter is ticking
 2     Payment    100      false
 3     Charge     50       true
 4     Charge     75       false
 5     Payment    125      false

UserBalances

id  user_id   balance
1     1         0.0

So the balance at this instant will be 0 but after couple of seconds should be different as meter is still ticking. How do I get the sum of amount field which is ever changing?

PS: I'm using ruby on rails and postgresql

Thanks


texts: Reference outer variables in nested select in bigquery vs mysql

texts:
I run following request in MySQL and it works, but results in an error with bigquery standard SQL.

$sql = <<<EOT
    SELECT
    ads.ad_id,
    (SELECT count(*)
      FROM `PRFXvisits` AS v
      INNER JOIN `PRFXvisit_data` AS vd
      ON vd.visit_id = v.id
      WHERE v.date_time >= @xday AND v.date_time < @xdaytomorrow
      AND ads.id = vd.ad_id
    ) AS clicks
    FROM `PRFXad_reports` AS r
    INNER JOIN `PRFXads` AS ads
    ON r.ad_id = ads.id
    WHERE r.rdate = @xday AND ads.is_archive = 0
    GROUP BY ads.ad_id
    ORDER BY ads.ad_id ASC;
EOT;

Error message:

{
 "error": {
  "errors": [
   {
    "domain": "global",
    "reason": "invalidQuery",
    "message": "WHERE clause expression references ads.id which is neither grouped nor aggregated at [8:15]",
    "locationType": "other",
    "location": "query"
   }
  ],
  "code": 400,
  "message": "WHERE clause expression references ads.id which is neither grouped nor aggregated at [8:15]"
 }
}

If I replace ads.id with e.g. 380 no error is produced.

    $sql = <<<EOT
        SELECT
        ads.ad_id,
        (SELECT count(*)
          FROM `PRFXvisits` AS v
          INNER JOIN `PRFXvisit_data` AS vd
          ON vd.visit_id = v.id
          WHERE v.date_time >= @xday AND v.date_time < @xdaytomorrow
          AND 380 = vd.ad_id
        ) AS clicks
        FROM `PRFXad_reports` AS r
        INNER JOIN `PRFXads` AS ads
        ON r.ad_id = ads.id
        WHERE r.rdate = @xday AND ads.is_archive = 0
        GROUP BY ads.ad_id
        ORDER BY ads.ad_id ASC;
EOT;

Why can't I reference ads.id in the inner select from outer FROM clause in bigquery?

It seems mysql select random row to show ads.id during grouping by ads.ad_id.


texts: Minimum value on most recent date per account MS ACCESS SQL

texts:
I have a MS ACCESS DB table Riskscore with the three fields: CHACCTBS, CHRQDATE, CHSCORE and want to find the lowest CHSCORE on the most recent CHRQDATE per CHACCTBS where it is possible to have multiple CHSCORE values on the same CHRQDATE.  There should be one record returned for each CHACCTBS.  There are about 17,000 records.  Once the query has run, there should be 7008 records returned.

CHACCTBS, CHRQDATE, CHSCORE
101    1/1/2014    781
101    1/1/2014    791
101    12/31/2013   717
221    10/01/2013   512
235    1/2/2014     685
235    10/31/2013   600

The query should return:

101    1/1/2014    781
221    10/01/2013   512
235    1/2/2014     685

Thanks!


texts:
We have planned to upgrade our Ops Mgr from 3.6 to 4.2
Need to know what are all the configuration parameters and other prerequisite I should be aware of??
Should there be any change on the agents ? since we are using server pools.
I went through the official documentation but I feel like didnt get all the information.
Our Ops Mgr is 3.6 and the app db is running with 3.4
Regards,
Kenny


texts: migration from mysql 4.1.0 to mysql5.6.2

texts:
I have been trying to backup data with structure from my old legacy server to my local server for modifications. PhpMyAdmin timed out at the time of export, MySQL Workbench has performed connections between source and target machines as Ok and upto bulk data transfer everything went well, after that point I am getting errors as "Connection using old(pre-4.1.1)authentication protocol refused(client option secure_auth enabled)although I checked disable_auth in Advanced tab.
Error: Error getting row count from source tables.

Any suggestions relating to this would be useful.


texts: SHOW PROCESSLIST: show processes from all sessions

texts:
SHOW PROCESSLIST shows the list of processes for the current user and session only.

Is there a way for the current user to list all their processes for all currently opened sessions?


texts: How to maintain fixed number of rows per foreign key?

texts:
I would like to keep a record of the last N (say, 10) objects a user has viewed. I'm thinking a basic way to store this information might be the following table:

CREATE TABLE history (
  id            SERIAL PRIMARY KEY,
  user_id       integer REFERENCES users (id),
  object_id     integer REFERENCES objects (id),
  view_date     timestamp DEFAULT current_timestamp
);

Since I only want to record the last 10 object views, I never need more than 10 rows for a given user_id. Is there an efficient way to maintain this limit whenever a user views an object and a new entry is created?

Or am I going about this the wrong way? Perhaps I should just limit to 10 results in during SELECT and periodically purge extra entries in some other manner?


texts: SQL 2014 Availability Group Cluster or Listener

texts:
We recently deployed a setup of SQL 2014 availability groups and we are having some difficulty with the failover.

Setup:

Two windows server 2012R2 VMs in a windows cluster with a CNAME pointed at the cluster IP address.
The availability group is setup synchronously.
The listener and the CNO are both in active directory.
The application is using the cluster CNAME to connect to the database.

The problem we are running into is that in the event of a failover the Windows cluster is failing over, and the AG is not.  So we get the primary of the windows cluster pointed at the secondary of the AG, so all the databases are read only.

My question is:

  should we be pointing the CNAME at the listener instead of the Cluster
  IP?  Or more generally what is the proper way to ensure correct
  failover behavior?


texts: Removing secondary replica from SQL Availability Group causes large log file

texts:
Due to an unforeseen hardware error, I had to take our secondary replicas down for maintenance. This was really due to a storage issue and, it was decided at a higher level, to purchase a new storage system for the secondary instance. This is several weeks out from delivery and installation at this point.

This was on a system that also uses FCI with 2 active instances.

I was able to successfully remove the secondary replicas from both Availability Groups and I thought I had done this correctly (they no longer show up in the UI or when I look at the AG Dashboard. However, one one of the AG's, all of my log files are growing. When I query the sys.databases and look at the log_reuse_wait_desc for that instance it says 'AVAILABILITY_REPLICA'

I'd rather not remove the AG as the listener is actively used and would require reconfiguration of numerous web.configs and other configuration entries. But, I also am slightly worried about the log file growth.

Why, on one AG does it not appear to be growing or have the log_reuse_wait_desc of AVAILABILITY_REPLICA but on the other it does?