texts: Windows Authentication for SQL Server Linked Servers

texts:
Currently, on our project, we have a situation: there are a SQL Server 2012 instance IA in domain A and MS SQL Server 2012 instance IB in domain B. Domains A and B are trusted. IB configured as the linked server for IA.

Could you help with an idea how can be configured "forwarding" of Windows authentication security context from instance IA to IB and what is the set of requirements to doing this? Is it only way to use Kerberos delegation for linked server or is enough that they are located in trusted domains?

UPDATE:

There is an issue... it is clear that Kerberos delegation can be used for that but what about other ways? This not work for our case in the reason that for Kerberos delegation between different Active Directory Domain controllers will work only in the case when both od Domain Controllers are located on machines with OS Windows 12+ (not our case). Possibly it can be enough that SQL Server instances located in trusted domains. If client application located on the same server as SQL Server instance IA everything is working well without SPN configuration and etc. But what about the case when client application located on other server (different from IA and IB)?


texts: Can adding a columnstore index to a table affect read performance of a query that uses a rowstore index on the same table?

texts:
I'm doing some testing of columnstore indexing on a single table that has about 500 million rows.
The performance gains on aggregate queries have been awesome (a query that previously took about 2 minutes to run now runs in 0 seconds to aggregate the entire table).
But I also noticed another test query that leverages seeking on an existing rowstore index on the same table is now running 4x as slow as it previously did before creating the columnstore index. I can repeatedly demonstrate when dropping the columnstore index the rowstore query runs in 5 seconds, and by adding back in the columnstore index the rowstore query runs in 20 seconds.
I'm keeping an eye on the actual execution plan for the rowstore index query, and it's almost exactly the same in both cases, regardless if the columnstore index exists. (It uses the rowstore index in both cases.)
The rowstore test query is:
SELECT *
INTO #TEMP
FROM Table1 WITH (FORCESEEK)
WHERE IntField1 = 571
    AND DateField1 >= '6/01/2020'

The rowstore index used in this query is: CREATE NONCLUSTERED INDEX IX_Table1_1 ON Table1 (IntField1, DateField1) INCLUDE (IntField2)
The columnstore test query is:
SELECT COUNT(DISTINCT IntField2) AS IntField2_UniqueCount, COUNT(1) AS RowCount
FROM Table1
WHERE IntField1 = 571 -- Some other test columnstore queries also don't use any WHERE predicates on this table
    AND DateField1 >= '1/1/2019'

The columnstore index is: CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Table1_2 ON Table1 (IntField2, IntField1, DateField1)
Here is the execution plan for the rowstore index query before I create the columnstore index:

Here is the execution plan for the rowstore index query after I create the columnstore index:

The only differences I notice between the two plans is the Sort operation's warning goes away after creating the columnstore index, and the Key Lookup and Table Insert (#TEMP) operators take significantly longer.
Here is the Sort operation's info with the warning (before creating the columnstore index):

Here's the Sort operation's info without the warning (after creating the columnstore index):

I would've thought a read query that is specifically leveraging the same rowstore index and execution plan in both cases should have roughly the same performance on every run, regardless of what other indexes exist on that table. What gives here?
Edit: Here's the TIME and IO stats before creating the index:

Here's the stats after creating the columnstore index:


texts: Ensure Mongo sharding rebalance latest chuck

texts:
The shard key i use is :

"_id" : {
        "pdm" : "1990-07-11",
        "hdocid" : "f23b7d7b2fc94339aed19983baddaeb3"
    }

pdm is a day level grouping key.
hdocid is a random hash key.

When I add new shard to the existing mongo, it rebalance the old chuck like "1990-07-10" rather then newer data like "1990-07-11".

The data I insert is from 1990-07-10 -> 1990-07-20 by order.

Thanks.


texts: DBCC CHECKDB - SQL Log Says Completed, SQL Server Agent Says Failed

texts:
I run a nightly Integrity Check (set up via Maintenance Plans) on all of my databases. For the last two nights, the final CHECKDBCC has failed... at least according to the SQL Server Agent Job.

  Progress: 2019-01-25 03:51:01.15     Source: Check Database Integrity      Executing query "USE [CellTestData_QC]  ".: 50% complete  End Progress
  Error: 2019-01-25 05:14:43.79     Code: 0xC002F210     Source: Check Database Integrity Execute SQL Task     Description: Executing the query "DBCC CHECKDB(N'CellTestData_QC')  WITH NO_INFOMSGS..." failed with the following error: "A severe error occurred on the current command.  The results if any should be disca...  The package execution fa...  The step failed.

But if I check the SQL Server Logs, I get two conflicting messages.

First I get:

  01/25/2019 05:00:39,spid105,Unknown,DBCC CHECKDB (CellTestData_QC) WITH no_infomsgs executed by sa terminated abnormally due to error state 6. Elapsed time: 1 hours 9 minutes 38 seconds.

But then I get:

  01/25/2019 05:32:06,spid110,Unknown,CHECKDB for database 'CellTestData_QC' finished without errors on 2019-01-24 23:01:46.507 (local time). This is an informational message only; no user action is required.

So what exactly is going on here? Is my process completing? Or erroring out?

For a bit more context...

We restore [CellTestData_QC] every morning at 5AM (likely why I'm getting the SQL Server Log message of 'terminated abnormally at 5AM).
Additionally, my backups of [CellTestData] and integrity checks of [CellTestData] and [CellTestData_QC] have been taking longer over the last few days - possibly because of issues with our SAN, which is why we're getting the IntegrityCheck of [CellTestData_QC] beginning to run into the restore of [CellTestData_QC]
Integrity checks run every night at 11PM
DB backups (full and partial) run nightly
Transaction Log backups run every 15min
Currently working with 8 databases, that range in size from 4MB to 100GB, with a total of 330GB of databases
I'm running SQL Server Standard 2012 on a 64-bit Windows Server VM (using vSphere) with 64GB RAM, 1TB SAN storage, 175GB NAS storage, 4CPU, 4.9GHz

Any ideas what might be going on here?


texts: Query product and product variants based on properties

texts:
I'm trying to build a filter system for an e-commerce application. The following schema is given:

http://sqlfiddle.com/#!9/18b93

A product has many variants
A variant as has many property_values
A product has many property_values as well
A variant "inherits" the property_values of the related product

I cannot figure out the query to filter out all product variants that have for example property_id 1 = Red, property_id 2 = S and property_id 3 = Cotton. In this case the property_ids 1 and 2 are inherited from the product. The query sould also be flexible enough to be extended with any number of additional filters.

Can this be queried in a performant fasion? Would it make more sense to denormalize the data and keep a "index" table from these values?

Any help is greatly appreciated!


texts: INSERT INTO statement with parameters for tableName, columns and values?

texts:
Is it possible to have an SP that looks something like this:

DECLARE @tableName = null;
DECLARE @cols = null;
DECLARE @values = null;

INSERT INTO @tableName @cols @values;

The resultant query looks like this:

INSERT INTO <table name> (<set of column names>) VALUES (<set of corresponding values>);

My use case is that I'm parsing Excel data into a database, i.e. a single workbook of 'type X' equates to a single row in this database. These workbooks are filled in from a template, so the data format is pretty constant. To parse the data I create a sheet in the workbook called 'DATA' or something, that looks like this:

tableName | attributeName | attributeValue | .. plus some config stuff
People    | Firstname     | Johnny         | etc
People    | Lastname      | Heinz          | etc
People    | Firstname     | Sam            | etc
People    | Lastname      | Smith          | etc

This would equate to two rows in the People table of the database (multiple rows are recognized by repeating attributeNames for a specific table).

The reasons this needs to be dynamic SQL is:

I'd like this to be applicable to any table in the database
I won't know how many cols a particular table has before parsing


texts: Is there a way to make pg_dump ignore errors instead of stopping?

texts: I'm running a pg_dump of my postgres 9.4 DB and occassionally it'll hit an error if some other process has an exclusive lock on a table.  Is there a way for the pg_dump to just ignore that table and proceed with the rest of the database instead of just stopping in its tracks?


texts:
I have a process which runs via Pentaho on a Unix server against MySQL 5.6.19. and using 5.1.32 of the MySQL jdbc driver.  We actually found this driver helped with this error in some cases compared to 5.1.26

The process is an extremely long running heavy load analytical process.  Last night I had a failure, and I can see from the system stats that there was a large amount of "iowait" on the system which started ~15mins before this failure.

The actual error is pasted below.  I've lived with this error over the years and usually it is simply due to the net_write_timeout.  So that has already been set at 86,400 or 24 hours.  For good measure i set net_read_timeout to the same.  I also had a situation where after 8 hours we hit the "wait_timeout" with exactly the same error so that is now also at 86,400.

So I don't really understand the error.  Last packet sent was 15 seconds ago, last packet received was 142ms ago. so this cannot be a timeout?  It must be something else?  Yet googling around for this error every time people say "its the timeout".  I've also set tcpKeepAlive=true for good measure.

The MySQL and Pentaho process are both working on the same local box.  No errors in /var/log/messages or in the MySQL error log.

It's hard to know if this should be posted here, or on the sysadmin site, or elsewhere.

Here's the full error:

org.pentaho.di.core.exception.KettleDatabaseException:
Couldn't get row from result set
Communications link failure

The last packet successfully received from the server was 142 milliseconds ago.  The last packet sent successfully to the server was 14,468,141 milliseconds ago.

        at org.pentaho.di.core.database.Database.getRow(Database.java:2420)
        at org.pentaho.di.core.database.Database.getRow(Database.java:2389)
        at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:145)
        at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
        at java.lang.Thread.run(Thread.java:745)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 142 milliseconds ago.  The last packet sent successfully to the server was 14,468,141 milliseconds ago.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1137)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3697)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3586)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4131)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:925)
        at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:2033)
        at com.mysql.jdbc.RowDataDynamic.nextRecord(RowDataDynamic.java:408)
        at com.mysql.jdbc.RowDataDynamic.next(RowDataDynamic.java:387)
        at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:7008)
        at org.pentaho.di.core.database.Database.getRow(Database.java:2408)
        ... 4 more
Caused by: java.io.EOFException: Can not read response from server. Expected to read 45 bytes, read 4 bytes before connection was unexpectedly lost.
        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3143)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3653)
        ... 12 more


texts:
I have a query which return me two dimension data:

SELECT POINT(value , extract(EPOCH FROM datetime)) AS pseudo_geom
FROM table

gaves me

pseudo_geom
---------------
(18,1439679940)
(29,1439680000)
(16,1439680060)

What I would like to do is to convert these data into a path type to be able to simplificate through ST_Simplify which accept a geometry type.

My goal is actually to reduce the number of points to display on a graph.

I tried with the path function and playing around with point(), string_agg() and grouping my data, with no result...

What could be the easiest and shortest way to do this?


texts: SSRS make a conditional format on the column

texts:
In a SSRS matrix report I'm trying to make a conditional format on the 'Total' column
By checking one of the columns for a specific value,

For example:
In case in one of the Groups (Toys, Clothing, Food), the revenue for a certain day is zero then I want to display the total's values in red
See – 05/01/2015 and 'Clothing' then the 'Total' of this day will be in red.

Any idea how to do it

Thanks for your respond


texts: Calculate MATCH() AGAINST() scores from UNIFIED QUERY not FOR EACH TABLE

texts:
I am trying to have a score for entire section of SELECT statements

SELECT *,MATCH(`result`) AGAINST('keyword') as `score` FROM `table1` WHERE MATCH(`result`) AGAINST('keyword')
UNION
SELECT *,MATCH(`content`) AGAINST('keyword') as `score` FROM `table2` WHERE MATCH(`content`) AGAINST('keyword')
UNION
SELECT *,MATCH(`text`) AGAINST('keyword') as `score` FROM `table3` WHERE MATCH(`text`) AGAINST('keyword')

In such case scores are per table + they are not ordered by relevance

But I tried this method, which is working but is not worth of production

SELECT * FROM (
    SELECT *,MATCH(`result`) AGAINST('keyword') as `score` FROM `table1` WHERE MATCH(`result`) AGAINST('keyword')
    UNION
    SELECT *,MATCH(`content`) AGAINST('keyword') as `score` FROM `table2` WHERE MATCH(`content`) AGAINST('keyword')
    UNION
    SELECT *,MATCH(`text`) AGAINST('keyword') as `score` FROM `table3` WHERE MATCH(`text`) AGAINST('keyword')
) as `combined` ORDER BY `score` DESC

Above code is disliked because scores there are per table, they are joined and ordered. A bad approach.

So I tried to MATCH() AGAINST() for data in TOP LEVEL SELECT as well this. (DIDN'T WORK)

SELECT *,MATCH(`data`) AGAINST('keyword') as `good_score` FROM (
        SELECT *,`result` as `data`,MATCH(`result`) AGAINST('keyword') as `score` FROM `table1` WHERE MATCH(`result`) AGAINST('keyword')
        UNION
        SELECT *,`content` as `data`,MATCH(`content`) AGAINST('keyword') as `score` FROM `table2` WHERE MATCH(`content`) AGAINST('keyword')
        UNION
        SELECT *,`text` as `data`,MATCH(`text`) AGAINST('keyword') as `score` FROM `table3` WHERE MATCH(`text`) AGAINST('keyword')
    ) as `combined` ORDER BY `good_score` DESC

Above statement is perfect for me, but it does not work because data column is created on-the-fly and it does not support to have a FULLTEXT INDEX.

My question is how to proceed to get my engine working.

Can you somehow make data a FULLTEXT
Is there any way to make it work other than IN BOOLEAN MODE that does not support scores
Is there an approach to this entire topic that would make it work? Creating a temporary table does not solves this, 50% rule of MATCH() AGAINST() makes a query return 0 results, but there are many
Maybe there is a something little that I miss?
Creating a VIEW does not work either, MySQL does not support INDEX-es on VIEWs.
Maybe it is a good idea to use IN BOOLEAN MODE and create scoring manually?

I have been working for this issue for more than two days. So I kindly ask for some help.
Thanks.


texts: How to select not exists data in SSIS package

texts:
i dived into SSIS because of curiosity, what i want to do is inserting "Not Exists" Data to postgresql. i want to select "Not Exists" data from  Postgresql which is joined to the data selected from Mssql, and insert into Postgresql. Below is the Data Flow Task.

Sorry for my English, i hope you got i what i want to do.


texts:
Could you please advise why this query returns 'NULL' on BASH shell? Basically I need to script it so could you please also advise what's the best practices to execute queries in BASH script?

MySQL CLI

mysql> SELECT CONCAT(mail_name,"@",name) AS email_address,accounts.password
     > FROM mail
     > LEFT JOIN domains ON domains.id=mail.dom_id
     > LEFT JOIN accounts ON accounts.id=mail.account_id LIMIT 2;
+-------------------------------------+----------+
| email_address                       | password |
+-------------------------------------+----------+
| an@example1.com                     | password1|
| an@example2.com                     | password2|
+-------------------------------------+----------+
2 rows in set (0.00 sec)

BASH Shell

# mysql test -e "SELECT CONCAT(mail_name,"@",name) AS email_address,accounts.password FROM mail LEFT JOIN domains ON domains.id=mail.dom_id LEFT JOIN accounts ON accounts.id=mail.account_id LIMIT 2;"
+---------------+----------+
| email_address | password |
+---------------+----------+
| NULL          | password1|
| NULL          | password2|    |
+---------------+----------+


texts: Compute duration of an event based on consecutive values in another column

texts:
I need to compute the sum of durations of multiple events based on consecutive values in another column.
             ts             | w
----------------------------+---
 2020-07-27 15:40:04.045+00 | t
 2020-07-27 15:41:04.045+00 | t
 2020-07-27 15:41:14.045+00 | f
 2020-07-27 15:42:14.045+00 | t
 2020-07-27 15:43:14.045+00 | t

The event is considered being active as long as the column w has a consecutive value of true.
The duration of the first event would be 60 seconds. '2020-07-27 15:41:04.045+00' - 2020-07-27 15:40:04.045+00. The second event has the same duration. The sum of both would be 120 seconds.
What would be the best/most performant approach to computing these values? The longest time range we'll be looking at will probably be half a year involving about 30 million rows.
I wrote a custom aggregate function that computes the duration but it takes about 16 seconds for only 1.5 million rows.
 Aggregate  (cost=444090.03..444090.04 rows=1 width=4) (actual time=16290.826..16290.828 rows=1 loops=1)
   ->  Seq Scan on discriminator0  (cost=0.00..57289.03 rows=1547203 width=9) (actual time=0.016..1723.178 rows=1547229 loops=1)
 Planning Time: 0.196 ms
 JIT:
   Functions: 3
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 0.889 ms, Inlining 0.000 ms, Optimization 0.508 ms, Emission 6.472 ms, Total 7.870 ms
 Execution Time: 16291.836 ms

I'm new to SQL and basically just got this working through trial and error, so I'm sure there is room for improvement. Maybe even a whole different approach. Here's a fiddle with the aggregate function.
I'm not sure if i should include the code because it's quite long


texts:
I would like to enable the MySQL General Log to view all the (slow) queries. I'm using XAMPP as server and have no idea how to enable it. I can't find a good guide on the internet.

Can someone help me please?

Thanks!


texts:
I would like to have your opinion and of course an explanation on the following.

My configuration :

mongodb version 3.4.4
1 replicaset with three identical servers( 4 cpu / 32 Go RAM under linux ubuntu v16)
1 primary and two secondaries.
No delay in replication every thing is up ta date.
The engine is WiredTigger.

During insertion or update time I have the following:

Primary : vsize : 3.73 G/ res : 3.02 G
1st secondary  : vsize : 3.28 G / res : 2.63 G
2nd secondary : vsize : 5.66 G / res : 5.02G

I am wondering why is there this difference between the vsize/res of the 2nd secondary and the 1st one?

I was guessing that I should see some kind of equality between them because they both support the same informations from the primary.

Any help or information will bee appreciated.


texts: Transaction isolation level/design for write-only ledger

texts:
This is a simplified example of a problem I've been working on. Say I have the following database schema:

Table: Deposits
+--------+------------+--------+---------+
|   ID   |    Date    | Amount | User ID |
+--------+------------+--------+---------+
| c1f... | 1589993488 |   40.0 | 6c7...  |
| bfe... | 1589994420 |   30.0 | 744...  |
+--------+------------+--------+---------+

Table: Withdrawals
+--------+------------+--------+---------+
|   ID   |    Date    | Amount | User ID |
+--------+------------+--------+---------+
| ad4... | 1589995414 |   20.0 | 6c7...  |
| e9b... | 1589996417 |   20.0 | 6c7...  |
+--------+------------+--------+---------+

And I'm writing a function that performs a withdrawal for a User. It:

Sums the deposits (SELECT amount FROM deposits WHERE user_id = ...)
Sums the withdrawals (SELECT amount FROM withdrawals WHERE user_id = ...)
If the difference is greater than the requested amount, INSERTs a new Withdrawal

We're using MySQL 8 and the default isolation level of REPEATABLE READ.

This function may be running as a lambda, so memory locks are not an option, and we don't have distributed locking (ie. a Redid-based lock) available.

A Caveat

There are existing admin operations run at the REPEATABLE READ level to create/delete these entities on-demand, by ID.

My Questions:

Am I correct in understanding that I need to use SERIALIZABLE as the isolation level for this transaction?
Will the SERIALIZABLE range lock prevent the REPEATABLE READ transaction from inserting new rows into the Withdrawals, or removing rows from the Deposits?


texts:
I have the following table:

                                     Table "public.employee_employee"
     Column      |         Type          |                           Modifiers
-----------------+-----------------------+----------------------------------------------------------------
 id              | integer               | not null default nextval('employee_employee_id_seq'::regclass)
 name            | text                  | not null
 slug            | character varying(50) | not null
 title           | text                  | not null
 base            | numeric(10,2)         |
 gross           | numeric(10,2)         |
 overtime        | numeric(10,2)         |
 benefits        | numeric(10,2)         |
 total           | numeric(10,2)         |
 other           | numeric(10,2)         |
 year            | smallint              | not null
 jurisdiction_id | integer               | not null
 notes           | text                  |
Indexes:
    "employee_employee_pkey" PRIMARY KEY, btree (id)
    "employee_employee_jurisdiction_id" btree (jurisdiction_id)
    "employee_employee_slug" btree (slug)
    "employee_employee_slug_like" btree (slug varchar_pattern_ops)
    "employee_name_title_idx" gin (to_tsvector('english'::regconfig, (name || ' '::text) || title))
Check constraints:
    "employee_employee_year_check" CHECK (year >= 0)
Foreign-key constraints:
    "jurisdiction_id_refs_id_9e093e72" FOREIGN KEY (jurisdiction_id) REFERENCES jurisdiction_jurisdiction(id) DEFERRABLE INITIALLY DEFERRED

Here's a simplified sample of some mock data for the id, jurisdiction_id, year, and total columns:

1 | 1 | 2014 | 100.00
2 | 1 | 2012 | 105.00
3 | 1 | 2011 | 110.00
4 | 2 | 2013 | 115.00
5 | 2 | 2012 | 120.00
6 | 2 | 2011 | 125.00
7 | 3 | 2012 | 130.00
8 | 3 | 2011 | 135.00
9 | 4 | 2011 | 140.00

Or, presented another way, each unique jurisdiction_id has the following year values:

1: 2014, 2012, 2011
2: 2013, 2012, 2011
3: 2012, 2011
4: 2011

I'd like to "rank" these values the following way:

(1, 2014), (2, 2013), (3, 2012), (4, 2011) would be ranked first.
(1, 2012), (2, 2012), (3, 2011) would be ranked second.
(1, 2011), (2, 2011) would be ranked third.

Then, within each rank, the rows would be sorted by total DESC.

Here's the "SELECT (id, jurisdiction_id, year, total) ..." output I'm looking for (with line breaks between each rank):

9 | 4 | 2011 | 140.00
7 | 3 | 2012 | 130.00
4 | 2 | 2013 | 115.00
1 | 1 | 2014 | 100.00

8 | 3 | 2011 | 135.00
5 | 2 | 2012 | 120.00
2 | 1 | 2012 | 105.00

6 | 2 | 2011 | 125.00
3 | 1 | 2011 | 110.00

I have to think this is doable in PostgreSQL, but I don't even know the correct "term" for what I'm trying to do, so googling around has been a bit tough.


texts:
We are doing e-commerce shopping site in PHP.

We designed database as below image.

we are planning to delete "category_Details" table and include "category_id & category_name" columns in "products_information" table as this going reduce one table. Is this good idea or is it better to have separate columns for "categories"? We may end up with 15 rows under "category_name" column.
We are planning to merge as we have to use multiple joint queries to fetch information?

In image designer_details table contains all sellers/vendors who sell their products.


texts: Configure sp_send_dbmail to send emails ONLY to certain recipients

texts:
For our environment test we need to configure sp_send_dbmail to send emails only to certain recipients. Let's say a stored procedure executes the following code:

EXEC msdb.dbo.sp_send_dbmail
    @recipients='developer@myorganization.com'; 'realuser@myorganization.com',
    @body = @myBody,
    @subject = @mySubject,
    @profile_name =@myProfileName

I need the email to be sent only to developer@myorganization.com, and not to realuser@myorganization.com. Is there a way that i can configure this?
Changing realusers email address won't do it for me.


texts: Enforcing existence of records while avoiding circular references

texts:
I'm having issues designing a satisfactory solution for the following problem.

There are two main entities:

Customer
User

A customer can have many users associated to it. A user can be associated with many customers. Definitely a many-to-many relation for which a junction table should exist.

A customer must have at least one admin user. This is absolutely critical.

For several reasons it would be nice to enforce that the combination of customer & corresponding admin account exists in the junction table. For example: when listing the customer's users, it would be nice to not have to manually include the admin user.

In the following design it would be possible for the combination of customer & corresponding admin to not exist in the junction table:

(ignore the erroneously placed crow's foot at 'User')

Is there a good way to solve this problem?

The solutions I came up with so far involved introducing circular references, which I think are undesirable. Another option might be to go with this design and use a VIEW for the user listing. Feels like a workaround.

Is there a flaw in my design or is this a limitation of relational databases?


texts: PL/SQL Can't grant privileges while logged as system

texts:
I can't grant the execute privilege on DBMS_LOB (I need it to operate on files), even when I'm logged as system. What can I do? Granting DBA ROLE doesn't help at all.

Error starting at line 7 in command:
grant execute on sys.DBMS_LOB TO PGADMIN
Error report:
SQL Error: ORA-01031:
01031. 00000 -  "insufficient privileges"

I've been racking my brain about it for few hours. I hope someone can provide a solution that will work in my case.


texts: How do i know a table size and row counts for each month, for the past six months?

texts: Using sql server can i get the row count and size of the table as well as the database size for the past six months?


texts: How do you CREATE TABLE AS SELECT (CTAS) with a composite type?

texts:
PostgreSQL supports CREATE TABLE AS SELECT (CTAS). It also supports composite types that can represent whole rows.

Here is an example of a CTAS,

CREATE TABLE foo AS
SELECT * FROM ( VALUES (1), (2) );

Here is a few examples of SELECTing a row.

SELECT (1,2);
SELECT ROW(1,2);
SELECT t FROM ( VALUES (1,2), (2,100) ) AS t;
SELECT x FROM ( VALUES ((1,2)), ((2,100)) ) AS f(x);
SELECT (x,y) FROM ( VALUES (1,2), (2,100) ) AS t(x,y);

However, none of them seem to work with CTAS.

CREATE TABLE foo AS SELECT f FROM ( VALUES (1,2), (2,100) ) AS f(x,y);
ERROR:  column "f" has pseudo-type record

CREATE TABLE foo AS SELECT (x,y) FROM ( VALUES (1,2), (2,100) ) AS f(x,y);
ERROR:  column "row" has pseudo-type record

I get that this is a typing-error, but what syntax does CTAS want?


texts: Will SQL Server go offline if it loses network connectivity to SAN where master and msdb system databases reside?

texts:
I have a setup where 3 servers combined into Availability Group
All 3 servers have local directly attached SSD drives, and user database files are hosted on these drives

But the system databases (master and msdb) of each server in AG, are hosted on a SAN device that is accessed over the network

Did not move those to local SSD drives yet
Questions:
In a hypothetical situation where network connection between any of the servers and the SAN device is lost (bad cable, bad NIC, some temporary network glitch etc.),

Will SQL Server service on that server go offline or stop working properly immediately ?

Or it continues to work for some time if master and msdb were cached in RAM before network went down ?


texts:
Quite new to db admin here and I have a quick question.

I am collaborating with another colleague off site and they would like me to send them a dump of our (1.5GB sql db) database via email as they need to do some testing. The database contains personal info, and I am wondering if there is a safer way to do this?

We are unable to provide remote access to the db. Rather than zip the .sql file and email it, in order to ensure privacy/confidentiality etc what would be the best way to send this db?

Any help appreciated.


texts: SQL Record Transfer from one db to another instance having same table structure

texts:
I want to move data from server db table to local db table but need to make sure that the Ids of records won't change while copying from main Server db table.
like

The issue I am having is when I move this to local, it merges the next to last record of the table and starts incrementing from number 20.
I want the Ids to remain concurrent or the same in both Local and Production Db.
Any possible Solutions?


texts: Counting occurrence a record based on consecutive days

texts:
I have a query to display record of stationary issued according to date.

SELECT date,stationary_id FROM stationary_issued

How do i check if a stationary was issued in consecutive days.I have tried group by and sum row.

Current table

date       stationary_id
2019-04-01 2
2019-04-01 3
2019-04-01 4
2019-04-01 5
2019-04-02 3
2019-04-02 2
2019-04-03 3

Expected

stationary_id consecutive_occurrence
3             3
2             2

I have tried grouping date and stationary while counting stationary_id but the result is not correct


texts:
I'm trying to setup transaction replication using a static IP.

I have 2 servers: one has a static IP but other one does not. In this case can I do replication. I am able to connect to the static IP SQL Server from the other server. I also created an alias for the static IP.

I set most of it up, but at the end I got an error on the subscriber:

And if I click on Start then this error appears:


texts: Creating my first Elastic Database Job on Azure

texts:
On https://docs.microsoft.com/en-us/azure/sql-database/sql-database-job-automation-overview it says that there is a new replacement for SQL Agent. However, I cannot get it to work.

I had problems with accepting the "Beta software" terms, but my manager accepted the terms on his account, and I could carry on.

On the portal I get this error message:

Deployment failed
Deployment to resource group 'MyRG' failed.
Additional details from the underlying API that might be helpful: At least one resource deployment operation failed. Please list deployment operations for details. Please see https://aka.ms/arm-debug for usage details.

I have also tried the Powershell interface, and here I get stuck on

New-AzSqlElasticJobAgent : No tenant found in the context.  Please ensure that the credentials you provided are authorized to access an Azure subscription, then run Connect-AzAccount to login.
At line:4 char:28
+ $JobAgent = $JobDatabase | New-AzSqlElasticJobAgent -Name $AgentName
+                            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : CloseError: (:) [New-AzSqlElasticJobAgent], ArgumentException
    + FullyQualifiedErrorId : Microsoft.Azure.Commands.Sql.ElasticJobs.Cmdlet.NewAzureSqlElasticJobAgent

It is probably something simple, but I cannot figure out what...


texts: Free universe tool monitoring all database

texts:
Is there any free tool to monitor most popular databases like Oracle, MySQL, MSSQL, PostgreSQL. I want to monitor the real time performance of all the above mentioned databases with a single tool like DBEAVER. But DBEAVER does not have real time monitoring facility.
Please suggest.
Thanks in advance.

Regards
Mahendra


texts: I am little confused about the ephemeral disk on LS series Azure VM.I want to suggest  L32s_v2 series vm for a customer due to high iops and throughput that the nvme disks provide but there is a catch. if vm is stopped from portal all the data on nvme disk will be erased. this is a serious risk because i cant use it for sql server database files


texts: Why are tiny Innodb row updates causing so much disk IO?

texts:
This is a followup question to my earlier How can I determine which Innodb table is being written?

Using the accepted answer from the above-referenced question I was able to determine which (two) of a hundred Innodb tables were being constantly updated on my server. Using some trial and error, I was able to determine which specific rows in those tables were being updated.

The rows are essentially counters that are updating and updates are happening about 1/s (I ran a query that counted 54 updates in 60 seconds). The rows are tiny with 4 columns: BIGINT, VARCHAR, LONGTEXT, VARCHAR. The value in the LONGTEXT column of the row in question is just a 10-character string and that is the column that is being updated.

I ran a query that calculated the change in Innodb_data_written over 60 seconds and ran it several times. It ranged from 4.5 to 5.5 MB over 60 seconds. When I turned off the process that is responsible for the 1-character row updates, the change in Innodb_data_written over 60 seconds went down to .03 MB, so it definitely seems to be these tiny row updates that are responsible for the ~ 5MB per minute of Innodb disk IO.

The innodb_buffer_pool_size is ample and the Innodb buffer use percentage is around 30%.

So, the question is, how are a couple of 1/s row updates that are only changing 1 character in a tiny row generating ~ 5MB per minute of Innodb disk IO?


texts: trying to turn XML into heirarchical SQL tables, can anyone provide a good explanation of two parts of this code example?

texts:
I am trying to learn how to turn XML into heirarchical SQL tables.

I have found an old code snippet from a Microsoft forum which is basically what I am trying to do, but I was wondering if someone could help figure out what is going on line by line on this code, particularly after the xml has been loaded onto @XML

--I understand this part, just making the tables

DECLARE @Books TABLE (BookID int identity(1,1),BookTitle varchar(50),BookLanguage varchar(20),BookPrice decimal(18,2))
DECLARE @Topics TABLE (TopicID int identity(1,1),BookID int,TopicTitile varchar(50),Page int)

--I understand this part, defining the @xml variable to be the xml below.. just a usual xml...

DECLARE @xml XML
SET @xml = '
<bookstore>
    <name>My Bookstore</name>

    <location>New York</location>

    <book>
        <title lang=&quot;eng&quot;>Harry Potter</title>
        <price>29.99</price>
        <tableOfContents>
            <topic>
                <title>Harry Potter Topic 1</title>
                <page>2</page>
            </topic>
            <topic>
                <title>Harry Potter Topic 2</title>
                <page>5</page>
            </topic>
        </tableOfContents>
    </book>
    <book>
        <title lang=&quot;eng&quot;>Learning XML</title>
        <price>39.95</price>
        <tableOfContents>
            <topic>
                <title>Learning XML Topic 1</title>
                <page>1</page>
            </topic>
            <topic>
                <title>Learning XML Topic 2</title>
                <page>2</page>
            </topic>
        </tableOfContents>
    </book>
</bookstore>'

--what is going on below here?  I am familiar with inserting data into tables,
--but what kind of insert is this where you are selecting some things and then doing a
--from @xml.nodes also, what is that T(c) at the end?  and do we always have to put
--a [1] after each xml element to denote we are referring to the first one we encounter?

INSERT INTO @Books
SELECT T.c.value('title[1]','varchar(50)') AS 'BookTitle',
    T.c.value('(title/@lang)[1]','varchar(20)') AS 'BookLanguage',
    T.c.value('price[1]','decimal(18,2)') AS 'BookPrice'
FROM @xml.nodes('/bookstore/book') T(c)

--what is going on here as well?  what is n(x) ?
--could you explain this line by line-ish as well? I ran this on
--SQL Server Management Studio and noticed that both of the 'topic titles' for each
--book got inserted.  Where in the code did those get put into the table?

INSERT INTO @Topics
SELECT b.BookID,n.x.value('title[1]','varchar(50)') AS 'TopicTitile',
    n.x.value('page[1]','int') AS 'TopicPage'
FROM @Books b
cross apply @xml.nodes('/bookstore/book/tableOfContents/topic[../../title=sql:column("b.BookTitle")]') n(x)

--below here is just regular sql selects so this makes sense.

SELECT BookID,BookTitle,BookLanguage,BookPrice FROM @Books
SELECT TopicID,BookID,TopicTitile,Page FROM @Topics

The forum I was referring to and trying to learn from with the old post is:

http://social.msdn.microsoft.com/Forums/en/sqlxml/thread/7216ccc9-c1d7-418d-95a2-ec3a96de2c27


texts:
As given at http://cherry.world.edoors.com/COBFKUqnUdBY one can obtain buffer hit ratio by following sql query:

SELECT round ((P2.variable_value / P1.variable_value),4),
P2.variable_value, P1.variable_value
FROM information_schema.GLOBAL_STATUS P1,
information_schema.GLOBAL_STATUS P2
WHERE P1. variable_name = 'innodb_buffer_pool_read_requests'
AND P2. variable_name = 'innodb_buffer_pool_reads';

QUESTIONS

From what period are buffer hit ratio given by that query? From start of database engine till now?
Is there possibility to obtain buffer hit ratio from given period of time? (for example: last 10 minutes)


texts: PgPool fails to auth in master_slave mode

texts:
We have two PostgreSQL servers running in master-slave streaming replication mode, behind pgpool.

When we activate master_slave mode in pgpool.conf, we get following error:

do_md5 failed in slot 1

When we add a host with the md5 method in pool_hba.conf, we're getting this error:

md5 token is invalid

Without master_slave mode, pgpool connects successfully. Where to dig?


texts: I have an application that stores its timestamps in Oracle using UTC (Coordinated Universal Time). Another application reads these timestamps from the database, but there is no convenient way to convert the timestamps to another timezone automatically (from within that application). Is it possible to adjust session settings in Oracle to return timezone adjusted timestamps for SELECT queries?


texts: Using SQL Server 2012 Standard with Bitlocker

texts:
We're trying to use Bitlocker to secure an SQL Server 2012 database. We don't have any issue getting bitlocker to work... the issue we have is that once the drive is locked, SQL Server can no longer read the data.

We'd love to use TDE, obviously, but since that's limited to the Enterprise edition, that's a no-go. We have TPM activated on the server. This is in a Windows Domain and we use Windows Authentication... but once the drive is encrypted, we cannot access the DB with anything.

What am I missing here?


texts: Dynamic JSON path filtering in PostgreSQL 9.6

texts:
I have a somewhat odd JSONB structure in my database to accomodate different possible overrides to common fields. The tricky thing is that I want to query based on those overrides. Here's a sample of my data:

{
    "title": "bar",
    "_override": {
        "o1": {
            "title": "baz"
        },
        "o2": {
            "title": "foo"
        }
    }
},
{
    "title": "baz",
    "_override": {
        "o2": {
            "title": "bar"
        }
    }
}

And this is the query I managed to get:

select distinct
  id,
  metadata,
  metadata->'_override'->jsonb_object_keys(metadata->'_override')->>'title' as title
from
  public.brands
cross join
  jsonb_each(metadata->'_override')
where
  title = 'foo'

Unfortunately, I'm getting ERROR: column "title" does not exist, even though title is a valid alias. I've tried adding the json_object_keys call to the WHERE clause, but then `ERROR: set-returning functions are not allowed in WHERE".

I feel that I am missing something in this approach. Maybe I should re-structure the data somehow or there's an easy way to traverse dynamic JSONB paths? I couldn't find anything about wildcards, only this complex query that accounts for multiple nesting levels: https://obartunov.livejournal.com/193870.html

EDIT: Based on feedback on the comments, here's the approach using a CTE.

with CurrentOverride as
(
  select distinct
     uuid,
     metadata,
     jsonb_object_keys(metadata->'_override') as override
  from
     brands
  cross join
     jsonb_each(metadata->'_override')
)
select
  uuid,
  metadata
from
  CurrentOverride
where
  metadata->'_override'->override->>'title' = 'foo'

But still not sure if it is the best approach.


texts: Insert in to table without duplicate records TSQL

texts:
Team,
Need some assistance with TSQL
table1
ID |Name| pin
1    a     101
2    b     201
3    c     301
4    d     401

table 2
ID|Name|pin
3   c   301
4   d   401
5   e   501
6   f   601

I want to insert to table1 from table2 only those values which are not duplicates (for all columns in table 2).
Final output in table1
ID | Name | pin
1    a      101
2    b      201
3    c      301
4    d      401
5    e      501
6    f      601

Appreciate your response. Thanks


texts: Defragmentation of Indexes - Higher value after defragmentaion

texts: I am trying to defragment indexes on SQL Server 2008 using ALTER INDEX--REBUILD. After defragmentation some indexes have fragmentation percentage value greater than its before defragmentation value. Is it possible or an issue ?


texts:
Need to build a service with an internal wallet function.
Here is a simple use-case diagram:

I have made a DB model

But I don't like it for the next reasons

No guarantee there will be only 1 sub-type record.
No guarantee there will be at least 1 sub-type record.
No clear way to display the detailed historical list of entries.

So, the question is - how to organize the DB model right? I am ready to accept even the most radical solutions, like anchor modeling or something. Just want to know the ideal accounting DB model. My favorite DB is PostgreSQL, but in general, it doesn't matter.
Here is my accounting_entry main entity implementation - https://gist.github.com/dilame/dcb55112833d0ad6579a08fd573592da
All the other entities supposed to be sub-type of accounting_entry as shown in the ER diagram.


texts: Storing common data instead of multiple joins

texts:
I'm wondering if it's a data modeling strategy to store data in a retrieval table (not sure if that's a thing).

I have 5-6 joins going through 10,000s of records that is only going to get more expensive. When a user performs an action (finalizing payroll for example) I was thinking of storing all the very important and commonly retrieved table data into a separate table called Tablename_History.

If I can simply access this new retrieval by id and retrieve ONE record with all the important information, wouldn't that be a better option?

I obviously know of 3 issues with this strategy:

Data is not live.
Duplication of data.
Adding new columns within the history table would be difficult to set defaults and I might have to do some versioning.

That being said, am I missing something?


texts: Reducing table scans using group by and window functions

texts:
There is a code that I'm trying to improve that looks like this(simple example):

SELECT    DISTINCT a.col_a
         ,COALESCE(b1.col_c, b2.col_c, b3.col_c)
FROM      tab_a a
LEFT JOIN tab_b b1
          ON a.col_a = b1.col_a
             AND b1.col_b = 'blabla1'
LEFT JOIN tab_b b2
          ON a.col_a = b2.col_a
             AND b2.col_b = 'blabla2'
LEFT JOIN tab_b b3
          ON a.col_a = b3.col_a
             AND b3.col_b = 'blabla3';

You can use the following script to recreate those tables

CREATE TABLE tab_a(col_a int)
CREATE TABLE tab_b(col_a INT, col_b VARCHAR(10), col_c INT)

INSERT INTO dbo.tab_a ( col_a ) VALUES ( 1 ), ( 2 ), ( 3 );

INSERT INTO dbo.tab_b ( col_a
                       ,col_b
                       ,col_c )
VALUES ( 1, 'blabla1', 1 )
      ,( 1, 'blabla2', 3 )
      ,( 1, 'blabla2', 5 )
      ,( 2, 'blabla2', NULL )
      ,( 2, 'blabla3', 5 );

How can I change it to 1 join + maybe window function and how to rewrite coalesce part. Just to explain, current plan shows 3 tab_b scans, I want to reduce it to 1.


texts: How can I store 12 character long number in MS Access 2016, if the field cannot be empty?


texts: insert row if table is empty, else update row (mysql)

texts:
i have 2 tables "table1" and "table2" both have TIMESTAMP cols for starttime and endtime. i want something like

if (table2 is empty)
insert into table2 (table2.starttime, table2.endtime) value (table1.starttime, table1.endtime (last row))
else if (TIMESTAMPDIFF(SECOND,table2.endtime(last row),table1.starttime(lastrow))<10)
update table2.endtime (last row) value (table1.endtime)
else
insert into table2 (table2.starttime, table2.endtime) value (table1.starttime, table1.endtime (last row))

i have tried different aproches but i am unable to get it working specially IF-ELSE and IS NULL check.
i can get last row as (SELECT endtime from table1 order by endtime DESC limit 1)so kindly ignore that part.
MySql version: 5.5.38


texts: Feasible to have thousands of foreign data wrappers in Postgres?

texts:
We are creating enterprise-grade SaaS where we will have many 1,000s of customers. We are considering creating a database in the Postgres server for every customer to ensure enterprise-grade security/access.

Each of these customer databases will have some access to a few tables in a root database via foreign data wrappers using the postgres_fdw extension. Also highly probable that we'll have a few triggers on the local tables that are referencing the foreign tables in the root db (I'm not sure how the root database syncs with the databases which could be a scaling issue).

Will we find any unexpected issues if we have 1,000s of databases using postgres_fdw to connect to a root database in the same postgres instance?


texts: What is the MS_PerfDashboard schema in msdb?

texts: We just migrated an old reporting server (running on 2005) to 2012 and some of the database performance monitoring jobs have started failing. I looked into them and found that they use a couple of functions in the msdb in a schema called MS_PerfDashboard, but the schema is missing on the new server. I had a quick browse online and found that people use this for performance monitoring, but what I couldn't find is where the functions came from originally. Are they part of an old SQL version? A plugin? Or just a script of functions that someone came up with?


texts: Please anyone suggest best approach for incremental load of all the tables in a database to a data warehouse , I have already developed SSIS package to do that using CDC, but enabling CDC for 100 database with 360 table will cause any performance degrade??


texts:
I have a list of things, some of which are duplicates.

create table things (
  id int primary key,
  title text not null,
  author text not null,
  duplicate_of int null references things(id)
);

I'd like to mark duplicates as such (can't delete them). The first item (defined by the lowest id) should never be marked as a duplicate. Its duplicates should have their duplicate_of value set to its id.

This is what I have so far:

update things a set duplicate_of = (
  select
    id
  from things b
  where
    b.title = a.title
    and b.author = a.author
    and b.id < a.id
  order by
    id asc
  limit 1
  );

Is there a more efficient way to accomplish this?


texts: Using Ola Hallengren's full backup but database option is for simple?

texts:
Each database on the server is set for simple recovery. I'm using Ola's script with  'full' in the script.

I tried looking on Google, but it seems backup and recovery are almost synonymous. Is there a difference? What am I actually backing up Full or simple?


texts:
I am working on an Azure Mobile Apps backend service, which I am trying to connect to a local SQL Server database, as described here. I have followed all the steps, except I am using SQL Server 2016 rather than 2014.

If I follow the Microsoft tutorial exactly, everything works fine. I am not sure what the environment variable is about, as I don't reference it anywhere, but anyway, when I run my app, I get the following output:

  Express server listening on port 3000
  2016-06-19T09:37:39.331Z - error: Error occurred during table initialization ConnectionError: Login failed for user 'azuremobile'.  Reason: The password of the account must be changed.
  at Connection. (C:\Users\George\Source\Repos\backend\node_modules\mssql\lib\tedious.js:378:25)
  at Connection.g (events.js:260:16)
  at emitOne (events.js:77:13)
  at Connection.emit (events.js:169:7)
  at Connection.processLogin7Response (C:\Users\George\Source\Repos\backend\node_modules\tedious\lib\connection.js:672:16)
  at Connection.message (C:\Users\George\Source\Repos\backend\node_modules\tedious\lib\connection.js:1082:21)
  at Connection.dispatchEvent (C:\Users\George\Source\Repos\backend\node_modules\tedious\lib\connection.js:519:45)
  at MessageIO. (C:\Users\George\Source\Repos\backend\node_modules\tedious\lib\connection.js:439:23)
  at emitNone (events.js:67:13)
  at MessageIO.emit (events.js:166:7)

This seems to be because of the setting that is shown below, "User Must change password at next login"?

However, If I change the password, or delete and recreate the login but unchecking that feature. I get this:

  Error occurred during table initialization ConnectionError: Login failed for user 'azuremobile'.

I am possibly getting the password wrong, but I have tried this 6 times now. Is there a way I can check the password or anything else I can do to get my node project to login to my local SQL Server database?

Update:

Here is my UserMapping:


texts: Does Express Profiler have a major effect on performance of SQL Server Express

texts:
I'm wondering does running Express Profiler against an SQL Express Server have a major impact on performance.  The versions of SQL Express I'm looking to run against are on 2008 and 2013.

This is the express profiler I'm using. http://expressprofiler.codeplex.com/


texts: Indexing for a table with many large subsets

texts:
In SqlServer 2017 we have a table with millions of rows. The table has a few dozen columns. One of the columns is a varchar(50) with a well defined range of allowed values, the value of this column is guaranteed to be one string from a list of about 4,000 strings. The only queries we care to optimize against this table all exclusively deal with the rows that have just one of those values, but they need to retrieve all of the columns of those rows. Does it make sense to put an index on that column and if so which type of index should be used?

Example Schema:

+---------------------------------------+
| Id   | Category | ... 20 other columns|
+------+----------+---------------------+
| 1    | Food     |      .....          |
+------+----------+---------------------+
| 2    | Lumber   |      .....          |
+------+----------+---------------------+

Example Query: select * from table where Category = 'food';

So, in this example, the Category column contains one string from a list of about 4000. I've considered a clustered index, but this column is not unique. I'd add a non-clustered index, but the query calls for all columns to be returned so it has to go from the index back to the main table to retrieve all of the data anyway, right? Then do we have to use a full table index or is there a better option?


texts: How to get SQL insert and/or update to not lock entire table on MS SQL Server

texts:
Very much a newbie on DB work, so appreciate your patience with a basic question.  I'm running SQL Server 2014 on my local machine, and I have a small table and a basic client application to test different approaches with.  I'm getting what appears to be a table lock during both INSERT INTO and UPDATE statements.  The client is an ASP.NET application with the following code:

OleDbConnection cn = new OleDbConnection("Provider=SQLNCLI11; server=localhost\\SQLEXPRESS; Database=<my db>; user id=<my uid>; password=<my pwd>");
cn.Open();
OleDbTransaction tn = cn.BeginTransaction();
OleDbCommand cmd = new OleDbCommand("INSERT INTO LAYOUTSv2 (LAYOUTS_name_t, LAYOUTS_enabled_b, LAYOUTS_data_m) VALUES ('name', '-1', 'data')", cn, tn);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT SCOPE_IDENTITY()";
int newkey = Decimal.ToInt32((decimal)cmd.ExecuteScalar());
Console.WriteLine("Created index " + newkey);
Thread.Sleep(15000);
tn.Commit();
tn = cn.BeginTransaction();
cmd.CommandText = "UDPATE LAYOUTSv2 SET LAYOUTS_enabled_b='-3' WHERE LAYOUTS_key='" + newkey + "'";
cmd.Transaction = tn;
cmd.ExecuteNonQuery();
Console.WriteLine("updated row");
Thread.Sleep(15000);
tn.Rollback();
cn.Close();

I run this code, then from the management studio I run SELECT * FROM LAYOUTSv2.  During both cases when the client thread is paused (i.e. prior to commit/rollback) the SELECT query hangs until the commit/rollback occurs.

The table has field LAYOUTS_key assigned as the primary key.  In the properties window it shows that it is unique and clustered, with page locks and row locks both allowed.  The lock escalation setting for the table is Disable...I've tried both the other available settings of Table and AUTO with no changes.  I've tried SELECT ... WITH (NOLOCK) and that returns a result immediately, but as is well-cautioned here and other places it's not what I should be doing.  I've tried putting the ROWLOCK hint on both the INSERT and UPDATE statements, but nothing has changed.

The behavior I'm looking for is this:  prior to commit of an INSERT, queries from other threads read all rows except the one that's being INSERTed.  Prior to commit of an UPDATE queries from other threads read the starting version of the row being UPDATEed.  Is there any way I can do this?  If I need to provide other information to clarify my use case please let me know.  Thanks.


texts: ADO connection suddenly starts failing Intermittently

texts:
I have an Access 2010 front end connecting to a SQL Server 2014 back end. I've got SSMS 2014 installed on my machine, and everything was working fine. (Running Win7 64-bit with 64-bit Office.)

Long story short, in trying to trouble shoot an end user problem, I attempted to install AccessDatabaseEngine_x64.exe from Microsoft. (I ended up getting it from downloads.com because the MS link wouldn't download it - I should have taken that as a clue.)

Installing that didn't help the issue, so I uninstalled and rebooted my machine. Now, when my Access application is attempting to connect to the SQL Server via this code:

Dim ConfigRS As New ADODB.Recordset
Dim SQLString As String
SQLString = "SELECT <fields> " & _
            "FROM <table> " & _
           "WHERE <condition>"
ConfigRS.Open Source:=SQLString, ActiveConnection:=CurrentProject.Connection

I usually get this error (server name, starts with 'c', blacked out), though sometimes it works just fine:

I can browse the linked tables from within Access, I can select from the tables in SSMS with no problem, I just cannot open an ADODB query via code.

The only thing that's changed today is the installation and uninstallation of AccessDatabaseEngine.

Does anyone know of specific files I need remove to finish removing all traces of AccessDatabaseEngine?
Would it be easier to reinstall SSMS to ensure all the proper drivers are in place?


texts: Is a partial restore of data from backup possible?

texts:
Apologies if I haven't used the correct terminology, I am not a DBA, but right now, I am all that is here!

Situation is last week, a database server was misbehaving (it was about 6 years old). The hosting company built a new server, and moved all the current data across to the new server, which is running well.

However, somehow, the new server is missing 36 hours of data. The hosting company has provided us with a backup that contains this data, amongst other things.

As some of the tables have auto generated keys (type integer) is there any way of restoring just a set of data from a date range back into my live database? I obviously can't just cut and paste, as all the integrity of the data will be lost.

Thoughts?

Windows Server and Microsoft SQL Server 2008.


texts: How to identify columns order in a table

texts:
I need to list columns from a table in the table definition order:

select * from syscolumns
where id = object_id('MyTable')
--order by colid

By examining syscolumns tables two columns look relevant: colid and colorder. The MSDN article on syscolumns says:

colid    | smallint | Column or parameter ID.
colorder | smallint | Identified for informational purposes only.
                    | Not supported. Future compatibility is not guaranteed.

I tried to run

select * from syscolumns where colorder <> colid

which yielded no rows, and that makes me think that these columns has the same values most of the time.

It does look that the safest bet is to use colid. However I would be curious to know: is there a difference between these two columns, and if there is, what is this difference?

Also the MSDN article, does not confirm, that colid reflects the order of the table definition. While this is reasonable to assume that this is the case, could you please let me know, if you are sure that it's the case, how you know that this is?


texts: I'd like to work around instances where a database were changed to simple and then reverted back to full or bulk logged recovery models. This is for my backup maintenance plan, as obviously any following log/differential backups will fail. Are there any DMOs available which monitor recovery model changes?


texts:
We have a query, which is trying to update a row, placing a user at the end of a queue.

To do this, it needs to grab the MAX(initialPositon) and set this row's initialPosition to MAX(initialPosition)+1

Due to the mysql restrictions on updating the same table you are reading from, we've designed this using a join

It's important this query is atomic, because it could be hit under heavy concurrency and we don't want the position to be duplicated

Here's the query:

UPDATE ListingQueue LQ
            JOIN (
                SELECT
                    (MAX(initialPosition) + 1) AS maxpos,
                    listingid
                FROM
                    ListingQueue
                WHERE
                    ListingQueue.listingid = 945877
                    FOR UPDATE
            ) AS LQ2 ON LQ2.listingid = LQ.listingid
            SET LQ.initialPosition = LQ2.maxpos,
                LQ.stage = 'waitingroom',
                LQ.stageLastUpdate = NOW()
            WHERE
                LQ.token = '6HMQ2JPcMTxUcJquw9SFH9ZQ76@CRbBf6p'
            AND LQ.listingid = 945877

This works fine, under normal conditions. When we hit it with concurrency, we get deadlocks:

**Deadlock found when trying to get lock; try restarting transaction**

Any suggestions on how to avoid this lock?

Table Structures:

  CREATE TABLE `ListingQueue` (
 `token` varchar(100) NOT NULL,
`listingid` int(11) unsigned NOT NULL,
`initialPosition` int(11) unsigned DEFAULT NULL,
 `stage` enum('waitingroom','inqueue','eventpage','frontofqueue') DEFAULT     'waitingroom',
 `stageLastUpdate` datetime DEFAULT NULL,
 `enteredQueue` datetime DEFAULT NULL,
 PRIMARY KEY (`token`,`listingid`),
 KEY `currentPos` (`listingid`,`initialPosition`,`stage`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


texts:
I am using SQL Server 2012 and I am investigating transactional replication and had a question on SQL Versions that can be used.

The Distribution and Publisher will be on SQL2012 Standard Edition, the database is around 200GB in size.

Is it possible to use SQL Express as the subscriber in this instance as long as I create it as a Push Subscriber?

My worries/question is that SQL Server Express has a 10GB database size limit and I am not sure if this applies to replication or not.

I hope this makes sense

Cheers
David


texts:
I have used these queries to access transactions from an SQL Server database

Use Action
Go
Select * from fn_dblog(null,null)

This query works but only loads 7 lines when one table in database holds over 100,000 records
Next I used this query to recover history

SELECT  * FROM fn_dump_dblog(NULL,NULL,'DISK',1
,'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Action.bak'
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,   NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,   NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,   NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,   NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,   NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL)

This query worked without error but only recovered 37 lines. If I restore the backup there are more than 100,000 records in one table. I would expect to see hundreds or thousands of lines when I run these queries.

The only logical answer would be that the transactions in the file are limited. But the file size for the transaction log is 1.5 GB. Is there any command I can run to check the number of transactions in the log file?

Can anyone explain how I can make these queries work to recover every transaction in the transaction log?


texts: Should a Fact table contain a column with a single unique value?

texts:
In order to simplify reporting, I am wondering if for each of the Fact tables we have, whether I can include a column that states the name of the Fact table, albeit in a more user friendly format

e.g.

FACT_SALES
Source     ID      Name
Sales      1       Bob
Sales      2       Terry
....

FACT_TESTING
Source              ID      Type
Product Testing     23      Adhoc
Product_Testing     29      ...
...

If it should not, then what is an appropriate way of adding this information without requiring any action by the end user in terms of them having to add some sort of coding to show this?


texts: How soon after updating expire_logs_days param and restarting sql will old binlogs get deleted?

texts:
MySQL 5.1.x | InnoDB | Windows

My mysql data directory is starting to fill up with bin logs.

I currently have the following settings configured in my windows mysql server:

[mysqld]
log-bin
server-id=1
binlog-do-db=foodb1
binlog-do-db=foodb2
expire_logs_days=25

I'm planning to change the expire_logs_days setting to expire_logs_days=10 and bounce the mysql service.  How soon after making this change can I expect the old bin logs to get clear out.

Is this only done as part of a nightly scheduled task? Or should this be immediate?


texts: How to view an encrypted view or stored procedure

texts:
I am working on a third party database.

When I try to view the definition of a view by right clicking, CREATE TO and then to NEW QUERY EDIT WINDOW, I am getting an error:

  This property may not exist for this object or may not be retrievable
  due to insufficient access rights. The text is encrypted.


texts: Massive Transaction Logs - what are my options?

texts:
I have a fairly crazy scenario where I am processing ~250 million inserts and 250 million deletes for a table that contains data that is only relevant for the moments that it is in the database.  Once we get new data for that key, we delete the old and insert the new.

I am using an azure database, and we have been running with very few issues for a few years now.  The problem is this:  Microsoft has recently started charging their PIT backups.  While I'm not opposed to them charging fair prices,  it seems that I never really thought about HOW MUCH data we are storing for PIT recover for this table. Last month we had just over 8 TB in transnational logs.  That means our PIT storage fees were ~$2000.   I am running a small company and for us that is debilitating.   Microsoft has been helpful and offered us some refunds to get us through, however the base problem still exists.

My question is this:  is there anything I can do to lower my logging?  From my research, I'm not sure Simple Recovery mode will solve this issue, and I don't think Azure supports simple recovery either.

I am proficient is SQL (enough to get by and optimize things occasionally), but by no means an expert. Is there anything I can do.  is it possible to not log a table. Is it possible to not log a Sproc?  Do I have options to reduce my Logging on azure?

As a last resort, is there another technology that would work for storing the data in this one table ( this would cause a large re-write, but for $2K a month I would consider it).

I'm a little desperate here! Any and all suggestions are welcome.


texts: Replication Lag on Postgres AWS RDS Service

texts:
We have a single master/single streaming replica Postgres 9.3 db on AWS. The load is not terribly high - this is a development/staging environment. (The production shows similar metrics). Point is the "ReplicaLag" shown in Cloudwatch oscillates wildly during the day between 0 and 200 seconds. I've changed max_wal_senders from 5 to 10 with no change.

Any suggestions for diagnosing this?

(This is a t2.small master and t2.small replica, however the production instance is large, and exhibits the same issue. CPU is < 2%, connections < 60, iops only about 5-15 count/second).


texts:
Given E(ABCDE) ABC are candidate keys

Normalize into 2NF and 3NF

As far as 2NF is concerned the solution is quite easy:

E1(BD), E2(DE), E3(ABC)

But with regard to 3NF I think I'm wrong if I say that nothing should be done.

Maybe the 3NF schema is:

E1(ABC), E2(BD)

Is it correct?

Thanks a lot

So according to @OliverAsmus the 3NF result should be:

E1(ABC), E2(BD)

But if what I wrote is correct, am I right in thinking that the 3NF (in this particular case) doesn't preserve all the attributes?
E doesn't depend upon any key so I got rid of it...

Is that correct? Thanks


texts:
In an effort to scale some existing code, we are removing logic from CASE statements and putting it in dimension/lookup tables.  Thus instead of CASE WHEN 'F' THEN 'Female' WHEN 'M' THEN 'Male' is now handled by a gender dimension.  (A very simplistic example)

But some of our CASE statements use LIKE operators, including comparison strings like 'ABC%DEF' and '%ABC%'.

I am looking for suggestions on the best way to handle this.  Our fact tables have over a billion rows, and growing, so efficiency is key.  We are Oracle 11gr2, if that is applicable.


texts: Best practice for MySQL CASE with multiple WHEN conditions vs a single OR condition

texts:
Is there a preferred (or more performant) way of writing a CASE with multiple WHEN conditions that generate the same value?
For example:
SELECT
  CASE
    WHEN 1 > 0 AND 2 > 0 THEN 1
    WHEN 0 < 1 AND 0 < 2 THEN 1
    ELSE 0
  END AS multiple_WHEN,
  CASE
    WHEN (1 > 0 AND 2 > 0) OR (0 < 1 AND 0 < 2) THEN 1
    ELSE 0
  END AS single_OR

Would the remainder of the WHEN conditions need to be evaluated before reaching the end of the CASE, and would an OR condition act any differently?


texts:
I succeeded in making a backup through maintenance plan.

All I need now is to find how to delete a folder (containing old backup) through the same maintenance plan.


texts: How does MySQL optimizer compute join cost when indexed?

texts:
So, The standard formula in theory is

C(R1.R2) = C(R1) + T(R1)*C(R2) where,
C(R1.R2) - Cost of joining two relations (tables) R1 and R2
C(R1) - Cost of reading relation R1
C(R2) - Cost of reading relation R2
T(R1) - Total number of rows in R1

It seems like this is the formula for Nested Loop Join, when for every row in relation R1 and the loop checks for matching row in relation R2. But since MySQL builds index for FK, It need not do NLJ right? What happens and how is the cost calculated?


texts: Can STRING_AGG be used multiple times with different delimiters?

texts:
I was doing some string aggregation and noticed what looked like a bug to me. STRING_AGG was used twice in the same query with different separator arguments. However, both produced the same results (the "first" separator is used in both cases). It seems to happen only in some circumstances. Is this a bug or is this documented behavior?

First, the setup:

CREATE TABLE #Data
  ([Group] INT
 , Member  CHAR(1));

INSERT INTO #Data
VALUES (1, 'a'), (1, 'b')
     , (2, 'c'), (2, 'd');

From a table vs. VALUES constructor. Without WITHIN GROUP ORDER BY, everything is fine. With it, the bug appears

SELECT Commas = STRING_AGG(Member, ', ') --WITHIN GROUP(ORDER BY Member)
     , Colons = STRING_AGG(Member, '::') --WITHIN GROUP(ORDER BY Member)
FROM #Data;

SELECT Commas = STRING_AGG(Member, ', ') --WITHIN GROUP(ORDER BY Member)
     , Colons = STRING_AGG(Member, '::') --WITHIN GROUP(ORDER BY Member)
FROM (VALUES (1, 'a'), (1, 'b')
           , (2, 'c'), (2, 'd')) [v] ([Group], Member);

Adding in a GROUP BY adds a sort and the error is there for both cases now:

SELECT [Group]
     , Commas = STRING_AGG(Member, ', ')
     , Colons = STRING_AGG(Member, '::')
FROM #Data
GROUP BY [Group];

SELECT [Group]
     , Commas = STRING_AGG(Member, ', ')
     , Colons = STRING_AGG(Member, '::')
FROM (VALUES (1, 'a'), (1, 'b')
           , (2, 'c'), (2, 'd')) [v] ([Group], Member)
GROUP BY [Group];

Add it's definitely broken... like deep down. Combine both expressions in a HAVING and you get nothing back. Use either one alone, and you get data.

SELECT [Group]
     , Colons = STRING_AGG(Member, '::')
     , Commas = STRING_AGG(Member, ', ')
FROM #Data
GROUP BY [Group]
HAVING STRING_AGG(Member, ', ') LIKE '%, %'
   AND STRING_AGG(Member, '::') LIKE '%::%';

Switching the order of the columns switches the separator used.

I used these trace flags for investigating, but I can't find anything about "optimizing" away the second aggregate.

OPTION (QUERYTRACEON 3604 -- Output info to client
      , QUERYTRACEON 8619 -- Show applied optimization rules
      , QUERYTRACEON 8606 -- Show logical query trees
      , QUERYTRACEON 8607 -- Show physical query tree
      , RECOMPILE);


texts: Recommended Postgresql index type to be used with not equal () where condition

texts:
I need to query two huge tables (more than milion records). Besides other conditions, there is also a not equal (<>) where condition on a columns pair of varchar type. It has to exclude one in a million.

Values in the column are unique, same in both tables, so I have to exclude from joining rows from two tables which have identical values in that particular column.

Is a standard b-tree type index suitable, or would some other index type be more suitable for a not equal (<>) where condition?

What about pg-strom extension? Could pg-strom (utilizing GPU) enhance speed of a not equal comparison in the where clause?


texts: When restoring logs in log shipping, when does the restoring stop

texts:
We are using LS for reporting purpose. LS backup runs on primary every 15 minutes. On Secondary nightly restore via LS restore job is done daily at 10 PM.

While the nightly restore job is running, additional log backups are being taken and delivered.

Will the restore job restore the logs through the last one present when the job started? Or, will it keep restoring as long as there's another log to restore.

Let's say our log backups are taken at XX:05, XX:20, XX:35, and XX:50. Our restore job usually runs to, say, 6:00 AM. At that point, does it represent our primary database as it was at 9:50 PM (the last log taken before the restore job started), or as it was at 5:50 AM (because after it restored the 5:50 log, it didn't find another, so it quit)?

Naturally, our users want to know for their queries; whether the data reflects 10PM or 6AM can make a big difference.


texts: In nzsql, how can I determine the path of the current sql file being executed?

texts:
How can I run a script with nzsql that calls other scripts using \i with paths specified relative to the path of the calling script?

The solution must work whether nzsql is being run interactively or non-interactively.

For example suppose run.sql, gather_data.sql, and generate_report.sql are all in /home/my_user/sql/generate_report.

-- run.sql
\set caller_path <magic_here>
\set gather_data_sql :caller_path / gather_data.sql
\set generate_report_sql :caller_path / generate_report.sql
\i :gather_data_sql
\i :generate_report_sql

If I could fill in <magic_here> I would be able to call

\i /home/my_user/sql/generate_report/run.sql

no matter what the current working directory was of nzsql.

I want to accomplish this without having to specify absolute paths or paths relative to cwd for scripts called by other scripts, but so far nzsql has thwarted my every attempt.  Is there something I'm missing?

Oracle has an sqlplus feature @@ that automatically does this for you.

Even better would be a way to change the working directory, but still there needs to be a way to determine the path of the calling script.

The only solution I have found involves processing the history file with shell commands to find the last \i and creating and calling a temporary script with \set caller_path ..., however this does not work if nzsql is being run non-interactively because commands are not saved to the history file in that case.


texts: MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined?

texts:
When reading online about innodb_log_file_size, it says

  The combined size of the log files must be less than 4GB

So what happens if it needs to be larger?

In books and online, it says to set the innodb_log_file_size to at least an hour's
worth of writes.

Currently I have 2x 1GB log files that when calculating Innodb_os_log_written for 1 hour, needs at least 10GB.  2GB of log files can only do 20 minutes of writes on our peak load.

tia!


texts: Select Mysql Table based on other table field value

texts:
I wonder if its possible to make query like this one:

SELECT cs.platform,
     (SELECT COUNT(*) FROM general_list as loc WHERE loc.platform=cs.platform) as indexed,
     (SELECT COUNT(*) FROM cs.table     as loc WHERE loc.platform=cs.platform) as sandbox
FROM (
    SELECT platform, table
    FROM states
    WHERE visible=1
) as cs

For each iteration in the main select, I need to take the table name from cs.table to info from such table.

Is there anyway to accomplish this?


texts: Mongo DB Memory Utilisation freezes app needs restart

texts:
I am running a project using Mongo DB and Django, I have 2 separate instance for my Django project and database each 16GB ram t2xlarge ubuntu ec2 instances.
I am facing issue that within a day or two usage memory on DB server goes above 6GB and on app server it remains below 1GB but freezes application and need to restart machines to recover.
Any suggestions and help would be really appreciated.


texts: Select and update specific mysql data in single query

texts:
I am trying to first select data from a row which contains certain characters. I would then like to update and append new data into another row of those items that were selected. This is my current solution I came up with when trying to combine the two tasks into a single query.

SELECT *
FROM `parts`UPDATE  `parts` SET
`keywords` = CONCAT(`keywords` ,  'Phillips' )
WHERE INSTR(`name`, 'PHI') > 0


texts: Is there a limit to how many backup files I can restore from for a single database on RDS SQL Server?

texts:
The AWS documentation for using native restore specifies that restoring from multiple files is allowed:

  To avoid errors while restoring multiple files, make sure that all the
  backup files have the same prefix, and that no other files use that
  prefix.

  The following example shows multiple-file restore.

  exec msdb.dbo.rds_restore_database @restore_db_name='database_name',
  @s3_arn_to_restore_from='arn:aws:s3:::bucket_name/bakup_file_*'

How many files can I restore from?


texts: List distinct column values where those rows share other column values

texts:
I'm trying to write a query to detect possibly-invalid data in a PostgreSQL table. We have a table of city names like this:

# `city_names`
id    | name     |   language | dialect | city_id
------------------------------------------------
01    | London   |   A        | A1      | 1
02    | London   |   A        | A2      | 1
03    | London   |   B        | B1      | 2
04    | London   |   B        | B2      | 3

In our domain:

It's fine that rows 01 and 02 both map "London" to city 1; the dialects don't happen to differ
It's fine that row 03 maps "London" to city 2; in that language, the name may refer to a different city
It's suspicious that row 04 maps "London" to city 3, because we already have a mapping to city 2 in the same language

I want to write a query that selects only rows 03 and 04 so that a human can decide whether one of them points to the wrong city.

I can solve this problem procedurally, but I'm having trouble doing it in SQL. For example, if I GROUP BY language and name, I lose the city_id values from the individual rows.

Basically my goal is: "If there's more than one city_id for the same name and language, list those city_ids."

How can I do this?


texts: How do I generate a date series in PostgreSQL without hard coding the dates?

texts:
I want to generate a series of months. The start should be the first month of the current year, the stop is the current date with an interval of 1 month.

I used the function generate_series (see below) and it gives me the desired result, but I don't want the starting date to be hard coded. It should change depending the year it is in.

select to_char(generate_series('2018-1-1'::date,
                now()::date,
                '1 month'), 'YYYYmm') as dat

Output:

201801
201802
201803
201804
201805
201806
201807
201808
201809
201810

Any ideas on how I can solve this?


texts: Software Uses SQLite and Has Frequent DB Lock Errors. Zero Access to Code. Anything we can do?

texts:
I've written an API for a client that accepts orders from clients and converts them into print jobs that go to an HTTP bridge into the company's RIP software. On the backend, the bridge is reading and writing job data to an SQLite database. It stores the job in SQLite and then sends it to the RIP. The RIP communicates back to the bridge when the status changes. It updates the record in SQLite, and through a series of subscriptions and webhooks sends the status update back to the API.

Neither the bridge nor the RIP automatically cleans up after itself, and the bridge imposes a limit of 5,000 tracked jobs at a time. So if we try to send job #5001 while it's storing 5,000 additional jobs, it will fail.

So the solution, at least in theory, would be to clean up jobs as they complete. Which is what we're doing.  When jobs complete my API enqueues a cleanup task, which is run from a separate process and requests the bridge remove that job.

The problem arises here. When we request cleanups the bridge starts failing job submissions due to DB lock errors. We're not deleting active jobs so there should be zero conflict between what is currently being INSERTed or UPDATed and what is being DELETEd as a result of our removal request.  I can only assume SQLite is locking the entire database for the duration of the delete instead of locking only the rows in use.  Would this make sense?

From the outside of the black box that is this software, is there anything I can do to affect this locking behavior in SQLite?

If not I essentially have to work around it by putting a buffer between everything our API does and the bridge that switches between a print mode and a cleanup mode but never does both synchronously. This will work but will slow production, and ultimately requires me to build a hack around a limitation in a $200,000 software product that just can't handle our volume.


texts: Why does this seek on BIGINT col have extra constant scan, compute scalar, and nested loops operators?

texts:
When I look at the actual exection plan of some of my queries I notice that literal constants used in a WHERE clause show up as a nested chain of calculate scalar and constant scan.

To reproduce this, I use the following table

CREATE TABLE Table1 (
    [col1] [bigint] NOT NULL,
    [col2] [varchar](50) NULL,
    [col3] [char](200) NULL
)
CREATE NONCLUSTERED INDEX IX_Table1 ON Table1 (col1 ASC)

With some data in it:

INSERT INTO Table1(col1) VALUES (1),(2),(3),
                               (-9223372036854775808),
                               (9223372036854775807),
                               (2147483647),(-2147483648)

When I run the following (nonsense) query:

SELECT a.col1, a.col2
  FROM Table1 a, Table1 b
  WHERE b.col1 > 2147483648

I see that it will do a Nested Loop drawing in the result of Index Seek and a scalar calculation (from a constant).

Note that the literal is larger than maxint. It does help to write CAST(2147483648 as BIGINT). Any idea why MSSQL is defrering that to the execution plan and is there a shorter way to avoid it than using the cast? Does it affect bound parameters to prepared statements (from jtds JDBC) as well?

The scalar calculation is not always done (seems to be index seek specific). And sometimes the query analyser does not show it graphically but as col1 < scalar(expr1000) in the predicate properties.

I have seen this with MS SSMS 2016 (13.0.16100.1) and SQL Server 2014 Expres Edition 64bit on Windows 7, but I guess it is a general behavior.


texts: Transactional Replication: can create 52 publication, but creating 53rd leads to problems

texts:
My apologies in advance for a long post, but I was really trying to make sure I do my due diligence before turning to the community for help, so here it goes.

I am working on a proof of concept with the purpose of using transactional replication to maintain a "reporting" copy of our databases.  The setup will include a "Publisher" server and a separate "Subscriber" server.  The replication will be a "Pull" transactional replication with a distributor running on the Subscriber server. Once again: Publisher => pull => (Distributor + Subscriber)

We expect to have a lot of databases on each of our servers, so, in order to mimic this scenario, I restored 100 databases (about 1 GB each) on the publisher.  All of the databases are identical.

I wrote a script, which enables me to setup replication in an automated fashion (since obviously I don't want to do it manually dozens of times).  The script performs the following steps:

Enable replication for a database (if not enabled already):
Create logreader agent for a database
Create publication for a database (if doesn't exist)
Create snapshot agent for a database (don't run it yet)
Add articles to the publication
Run snapshot agent job (and wait for it to finish)
Create subscription database (if it doesn't exist)
Add subscription in the subscription database (if it doesn't exist)
Create distribution agent

I am able to successfully setup replication for about 52 of my databases and everything is running fine.  When I get to database #53 things start to go wrong.  The snapshot creation is successful, but the logreader and the distribution agents fail.  Moving on to database #54 everything is now completely broken.  The snapshot does not get created and none of the agents are running.  I have carefully reviewed replication monitor and SQL Agent jobs looking for error messages, but I didn't find anything helpful.  The replication monitor simply says: "Agent Shutdown.  For more information see the SQL Server Agent job history for job ‘……’".  In the details section it says: "The replication agent encountered a failure.  See the previous job step history message or Replication Monitor for more information".  Next I go to the SQL Agent Jobs and review the output.   In the job history the failed step says: "Message : The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information".  In turn, the previous step has no info at all.   Just the word “Message” with nothing after that.  I have also reviewed the windows application log looking for any errors, but it simply repeats the same messages I see in the replication monitor and agent job history.  I added “–output” parameter to the agent command (in the SQL Agent Job step properties) hoping that the output file would provide me with more information, but no output file gets created, which leads me to believe that the agents don’t even start.

I was thinking that I might have hit some hardware limit, so I checked the disk space and there is plenty of it available.  I have also checked RAM and it's not even 50% used.

Finally, what really gets me is that I am able to run all of the agents manually from the command line.  For example, I can copy paste parameters from the SQL Agent snapshot job and run snapshot.exe from the command line with these parameters – the snapshot gets created successfully.  The same goes for logreader and distribution agents.

I am completely confused here and appreciate any pointers in the right direction.

Final note (and maybe the only silver lining): The problem is fully re-producible.  I can successfully setup my 50+ subscriptions and then the issues will always start around subscription number 52-53.

Thank you!


texts: DB2 10.1 Client throws SQL0552N running a script

texts:
I am trying to run a sql script on a remote database using DB2 10.1 on AIX 6.1.  I have granted my admin user (Adm101) on the database server SECADM. But when i run my script on the remote server with the db2 client (Client101) I get:

SQL0552N "Client101" does not have the privilege to perform operation "CREATE TABLE". SQLSTATE=42502

Adm101 is in listed in the database but Client101 is not.  How can I create a table on the remote client?


texts: Optimize query without rewriting it - vendor app

texts:
Here is a query that uses a lot of CPU on my SQL Server (8 cores):

DELETE TOP(1) rd
FROM
  (SELECT TOP 99.9999 PERCENT *
   FROM APM_IisBb_Request_Details
   ORDER BY RequestDate ASC) rd
JOIN APM_IisBb_Request r ON rd.RequestID = r.ID
WHERE r.SiteID = 33355

The query plan is here.

Looks like some purge process from a vendor app. Is there any way to improve it without query modification?

It is generated by a binary app and I can't change the query for now, so I am looking for some indexes maybe or optimizer hints.

It's running on SQL Server Standard Edition 2012.

Table 'APM_IisBb_Request_Details'. Scan count 1, logical reads 122719, physical reads 0,
    read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 3193604, physical reads 0,
    read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'APM_IisBb_Request'. Scan count 1, logical reads 2002, physical reads 0,
    read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


texts:
I'm trying to figure out, how I can hide those rows in my report where Total Allocation and Total Cost IN BOTH COLUMNS TOGETHER are 0 in SSRS 2008.

For example:

Total Allocation    Total Actual Cost
0                     0                <---- hide
100,00                0                <---- don't hide
0                     50,0000          <---- don't hide

Here is the screenshot:

Thank you


texts:
Due to a database-restore, collation has been changed. If I try to

alter database <xyz> collate <xyz>

I get an error, that no exclusive lock to database to fulfil this operation can be gotten.
Since sql-server is part of a virtual-machine, I'm quite sure, that no*one* has any database-access at this time but me. I thing that one ore few processes prevent me altering the collation having access to db, but I can't determine which it could be.
I've deactivated all possible services having something in common with the sql-server, but error stays the same. ( Running Processes: MSSMS and Report Server )

I know I have successfully changed collation on another db in the past - easyway - via contextmenu/properties/collation, but this also has no success.

Reading several articles of changing collation of each object by one via cursor operation is not what i want to do.

Any hints would be appreciated!


texts:
I have a simple table like this :

CREATE TABLE gateway_text (
  id         BIGSERIAL                                  NOT NULL PRIMARY KEY,
  text       TEXT                                       NOT NULL,
  hash       char(40)                                   UNIQUE,
  created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now()  NOT NULL
);

CREATE RULE "gateway_text_on_duplicate_ignore" AS ON INSERT TO "gateway_text"
  WHERE EXISTS(SELECT 1 FROM gateway_text WHERE hash=NEW.hash)
  DO INSTEAD SELECT * FROM gateway_text WHERE hash=NEW.hash;

SQL Fiddle

As you can see, the rule is for when the first select is passed (the hash is already in database) so the first insert, is not the one DO INSTEAD supposed to run for.
But the problem is, every time I run insert, I get the select result, even for the first insert of a hash.
In my case this is OK, very OK actually, but I don't know if this is correct or this is a bug? can I simply accept this as normal and relay on this?


texts: Database design for Time Sheet adopting different weekend

texts:
My Requirement
I need to design a database that handles multiple time sheet formats for their staff\contractor working with different clients. Each of them may have a different week end say

Client 1 ==> Saturday & Sunday
Client 2 ==> Friday & Saturday
Client 3 ==> Sunday & Monday
Client 4 ==> Saturday & Sunday
Client 5 ==> Saturday & Sunday
Client .. ......................................................

My Query

How should I design the DB to implement this?

I will be having a Timesheet table.

I will be also having a Holiday table to include the public holidays for different clients.

Where can I design the table to include the different week ends?
Please suggest.

Update

I need to implement the below - to restrict or alert weekend day - while allowing option to add the time sheet data. The weekend days are different (targeting different time sheet format) for each staff as per the client they are working with.

So as per your suggestion Joel, including the table WeekDay to my design, the below way is the option you recommend to implement different weekends?

Timesheet        ==> TimesheetID,EmpID,SubmittedDate, ClientID ...
Timesheet_Details ==> Timesheet_Details_ID,TimesheetID,Date,WeekDayID,IsHoliday
Client            ==> ClientID,TimesheetformatID,..
...
...
...
Holiday           ==> HolidayID,HolidayDescription,Date,ClientID ...
WeekDay           ==> WeekDay_ID,DayName,DayNumber,ClientID...


texts:
We are implementing a software that allows to send "virtual" money between user internally. We have started our database with a single entry as follow (simplified table). We only one record one transaction, where the user initiated the transaction (send money) will be recorded. So it will always be a debit.

TransactionLog
------------------
TransactionLogID
UserIDFrom
UserIDTo
CurrencyID
CurrencyAmount
Remarks
TransactionDateTime

Then a requirement changes that say we need a double entry accounting schema in our database. Then i have make a new table as follow.

TransactionLogDoubleEntry
----------------------------
TransactionLogDoubleEntryID
TransactionLogID
UserID
CounterPartyUserID
CreditOrDebit
CurrencyID
CurrencyAmount
Remarks
TransactionDateTime

My question:
1) Am i doing this in a correct direction that i add TransactionLogDoubleEntry table with credit/debit it means it already become a double entry accounting schema?

2) Should i remain the table TransactionLog? Or using TransactionLogDoubleEntry is sufficient enough?

3) If i were to remain TransactionLog table, is it necessary that i duplicate the currencyID, currencyAmount, remarks, TransactionDateTime in TransactionLogDoubleEntry? Or is that enough to get the corresponding value from TransactionLog table.


texts: Compare only day / month / year portion of a timestamp field

texts:
I am creating a view for a certain table, where rows have timestamp fields (planned_ts). The view needs to be sorted according to these timestamps, but since the application is only interested by the date and not the time, the result look sometimes random, especially when sorting over multiple fields.

The question I have is, if I have this query (for example) :

SELECT *
  FROM tblFoo
 WHERE ...
 ORDER BY planned_ts ASC NULLS LAST, priority ASC

(NOTE: where priority is an integer field, lower means more higher priority.)

How can I change the order clause, so it does not consider the time, as if every timestamps were at 00:00:00.000? I am aware that I can use functions in the sort clause, but I'm not sure how to do this in PostgreSQL.


texts: Number of databases on a server. Should I try to consolidate these objects together?

texts:
Background

Hi, as a BI developer I create various scripts for a middle-size company (200-300 employees). These scripts are used to manipulate / aggregate / clean data from different sources and usually the result of these operations is stored into different tables (to perform extractions, analysis, whatever).

Problem

My boss is a great guy but he likes to create a new database and SQL Server Agent job (on the same server) each time a new business case pops in. For instance we're managing 10+ databases but a lot of them contain only a few tables. Same issue with the SQL Server Agent jobs, most of them perform only one step / action.

Personally I prefer a clean environment and to group tables / jobs together as much as possible (i.e.: storing all our derived tables into one DB rather 2-3 different DBs).

However before I engage any action / audit I would like to hear about your feedback. I mean, from your point of view, what's the best practice? Is it ok to have 10+ DBs / 50+ jobs when we could divide these numbers by 2 or 3?

Thanks,

Edit

I notice the question has been put on hold. Sorry for the inconvenience, I'll try to rephrase my question:
I run a SQL Server containing 10+ databases (same company). Some DBs, for no particular reason (legal or whatever), contains only a couple of tables. I feel like I could consolidate these databases.
Is it the right way to proceed? Or it's ok to create a new DB whenever you want?

PS: maybe the answer to this question is truly "opinion based". I just can't figure out what is the best way to proceed. In a way that's an answer too.


texts: SSRS: Make tables start from the top of the new page

texts:
I am trying to create a report with multiple tables. The report will be exported to Excel though. I want to have 2 tables in one page so I "Add a page break after" to every second tables. However, SSRS will leave blank space on the top of the page, even though I moved tables close to each other.
this is my design
And this is the result(second page), I want to remove the blank space on the top of the page.

Thank you so much for reading this!!