texts: Is it possible to export bacpac file from SQL SERVER 2008 (running 10.50.6220)? I can only see option for dacpac.


texts: Output result of DBCC PDW_SHOWEXECUTIONPLAN into a variable

texts:
I would like to output the results of DBCC PDW_SHOWEXECUTIONPLAN into a variable (or table, I'm not fussy!).

I know the PDW syntax is quite different when it comes to outputing results, which means everything I've tried has so far failed. I've seen some suggestions of inserting the results into a table, but I always get syntax errors

create table #1234
(
    sql1 varchar(max)
)
with (distribution = round_robin)

insert into #1234
DBCC PDW_SHOWEXECUTIONPLAN(@did, @spid)

  Parse error at line: 37, column: 2: Incorrect syntax near 'DBCC'.


texts:
I just built a server (SQL 2017) and I can connect to Analysis Services and Integration Services via SSMS without issue using Windows Authentication.

When I try to connect to the Database Engine I get:

  Cannot connect to myserver01.

  Login failed for use 'me'. (Microsoft SQL Server, Error: 18456)

  A connection was successfully established with the server, but then an
  error occurred during the login process. (provider: Shared Memory
  Provider, error: 0 - No process is on the other end of the pipe.)
  (Microsoft SQL Server, Error: 233)

I checked SQL Configuration Manager and everything seems to be running properly. I restarted everything just to be safe, but no changes. I see some answers on here referring to turning on Mixed Mode, though I think that wouldn't be relevant to me as I'm using Windows Authentication.


texts:
I'm using Oracle 11.1.0.6.0 db, on Linux 64 bit server on Amazon cloud.

I'm getting following error when I run the connect command

SQL> connect / as sysdba
ERROR:
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 30: Read-only file system
Additional information: 9925
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 30: Read-only file system
Additional information: 9925

When I run this df -h command I'm not seeing the file system it's just showing as below

$ df -h
Filesystem Size Used Avail Use% Mounted on

I do not understand cause of this issue. Please let me know how to solve this issue


texts: SSISDB Maintenance job cleaning data very slow on SQL Server 2014

texts:
On SQL Server 2014 Microsoft (12.0.2430) SQL Server Enterprise: Core-based Licensing (64-bit) after changing the retention period from 365 to 90 since last 2 days job clean up about 2 GB of data only. I have rebuild & update all stats on SSISDB database still its slow.

Current Size of database is 344GB Approx. what is the best way to clean old records Quickly, To do that do i need down time ?

I have followed below article so far :-
https://www.mssqltips.com/sqlservertip/3307/managing-the-size-of-the-sql-server-ssis-catalog-database/


texts: What is the difference between Oracle clusterware and Real Application Clusters? If we have 2 servers and want to join them into a cluster why there are two separate software products?


texts: How do we determine data_length + index_length as database size in MySQL

texts:
I am calculating my InnoDB database's size with the following syntax:
SELECT table_schema AS "Database",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
GROUP BY table_schema;

As per MySQL documentation, data_length column gives the clustered index length in pages. But I have not created an index on that table. How to calculate exact total space, used space and free space for a table?


texts: Transfer ERD between Databases on SQL Server

texts:
I've a development database on SQL Server 2008. I want to copy the production database back to my development server to get the latest data for testing. Unfortunately the production database is missing the many ERDs I've created to my development database.
If I just restore a backup, I'll loose my ERDs. Is there any way to save or script the ERDs and transfer them to another database if both have the same schema?

Thanx


texts: SQL Server 2019: InitPersistentVersionStore failed after installing CU2

texts:
I have installed SQL Server 2019 CU 2 over a vanilla install of SQL Server 2019 RTM.

Now I am puzzled by the following messages in the error log at startup:

  RecoveryUnit::InitPersistentVersionStore failed with db [1] name
  [master]
  RecoveryUnit::InitPersistentVersionStore failed with db [4] name
  [msdb]

AFAIK the PersistentVersionStore links to the new feature Accelerated Database Recovery however I have not configured it.

Here's a screenshot from the error log:

The error occurs again after a service restart.

Is it automatically enabled with CU2 for system databases? Is there anything I can do about this error in order to resolve it?

I already did a web search with the error message but most surprisingly didn't find anything regarding to it.

Quick update (March 16th):

Opened a Ticket with Microsoft Support (no answer yet)
Installed SQL Server 2019 CU 3: Error persists at service restarts just as with SP 2


texts: Is the foreign key automatically indexed in MySQL?

texts: Do we need to create an index for a foreign key while creating the table in InnoDB of MySQL?


texts: Can Microsoft JDBC Driver 4.0 connect to SQL Server 2019 and perform DML without issue?

texts:
The SQL version compatibility BOL article advises that the minimum compatible JDBC driver version for SQL Server 2019 is 7.4.
Can an older JDBC driver still connect to SQL Server 2019? Or can only the driver versions listed as compatible connect to the specified SQL Server versions?


texts: Refresh SQL Server's service account security group membership

texts:
Consider a SQL Server whose service account is a Managed Service Account in Active Directory. When this MSA is added to a security group, this group membership is not immediately going to take effect if SQL Server is already running as the MSA. (Restarting the SQL Service should have the effect of the MSA "logging on" again, and gaining the access of its new security group membership.)

Is there a way to safely "refresh" the service account's group membership without restarting the SQL Server?


texts: Exclude databases from automatic full backup

texts:
I have 10 databases on my SQL Server. We do an Automatic Full Backup every day at 00.00.

The issue is there are two databases out of the 10 that should not be backed up as these databases are currently not required by the management; but I have no idea how to remove these databases from the Automatic Full Backup.

I am not able to find any script or anything related to the backup. The only thing I was able to find is the Full Backup option on the SQL Agent. Also these backups are done by SQL Server. Please help me out in resolving this issue.

When I went to SQL Server Agent> Jobs> Full Backup I found the following:

I am not able to make it out what exactly is this. Are these backups or something else? The files generated by these jobs are consuming my drive space and these jobs are running on daily basis.


texts: Why do connections to my database fail, but only sometimes?

texts:
I am having some trouble diagnosing the cause of a problem I am having with Microsoft SQL Server 2008 R2.

There is an application written in C#.net that connects to the database.

Occasionally the application will throw this error:

Cannot open database "<database name>" requested by the login. The login failed.
Login failed for user '<user name>'.

I can successfully connect to the database through SQL Server Management Studio by copying and pasting the login information from my connection string in Web.config

Looking at the SQL Server log, this is the error that comes up:

Source      Message

Logon       Login failed for user '<user name>'. Reason: Failed to open the explicitly specified database.
Logon       Error: 18456 Severity: 14 State: 38.

Looking up that error code online doesn't provide any solutions. The recommendation is to run a trace using SQL Server Profiler and wait for the problem to happen.

I ran the trace and noticed that the following happens every time the error occurs (some lines removed for privacy reasons, click to enlarge):

When the last item happens, this error message opens:

I looked in the SQL Server Agent Jobs tree in SQL Server Management Studio and noticed some jobs in there that I created but don't use any more. I removed them and the error still comes up.

The only job left in there is:

syspolicy_purge_history

Which I assume are required to some extent.

Does anyone know how to fix this problem? What steps can I take to find and resolve the issue?

Detailed Version Info:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)   Jun 28 2012 08:36:30   Copyright (c) Microsoft Corporation  Web Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)


texts: Insert trigger function from select query, plus static values pulled from the new.

texts:
We're having an issue writing a trigger function that involves 3 tables:  CustomerProfile, DropdownStats and OrderDetails. A simplified schema of each is as follows...

CustomerProfile table...this table has a row for all of the column names of the OrderDetails table.

CustID ColumnName  IsDropDown
------ ----------  ----------
1      firstcolumn false
1      ...
1      orderid     false
1      format      false
1      department  false
1      ...
1      lastcolumn  false

DropdownStats table...

OrderID ColumnName ColumnValues CountOfColumnValues
------- ---------- ------------ -------------------
1       format     circle       15
1       format     square       23
1       format     triangle     3
2       format     circle       10
3       format     square       5
3       format     triangle     10

OrderDetails table...

firstcolumn ... orderid format department ... lastcolumn
----------- --- ------- ------ ---------- --- ----------
data        dta 1       circle 123        dta data
data        dta 1       square 134        dta data
...
data        dta 2       circle 354        dta data
...

The trigger is written for the CustomerProfile table as an AFTER UPDATE trigger.

If the 4th row in the CustomerProfile table (where columnname = format) transitions the IsDropDown from false to true, then we have a to ADD rows to the DropdownStats table as they are pulled from the OrderDetails table.

The trigger function as written is producing results in the DropdownStats table like below but we want results like the sample above.

DropdownStats table...

OrderID ColumnName ColumnValues CountOfColumnValues
------- ---------- ------------ -------------------
1       format     format       41
2       format     format       10
3       format     format       15

The problem is having the trigger function insert a static column name and then have a separate row for each of the distinct values found for that column and a count of rows that hold those values. Here is one (of several) attempt at this...the problem is the two new.columnname They should not be the same.

if old.isdropdown <> new.isdropdown then --if there was a change
  if new.isdropdown = true then --and the change was from false to true
    insert into dropdownstats
    (orderid,columnname,columnvalues,countofcolumnvalues)
      select distinct
      orderid
      , new.columnname -- we want this to be the static actual name of the column, repeated for every record inserted (Ex: 'format')
      , new.columnname -- we want a row for each of the values found for the column in the database (Ex: 'circle', 'square', 'triangle', etc.)
      , count(new.columnname) -- the count of the records found for the value (Ex: for 'circle, count was 15)
      from orderdetails
      group by orderid, new.columnname;
  else --the transition was from true to false
    delete from dropdownstats as dd where dd.columnname = new.columnname;
  end if;
end if;

If I just use the query tool and manually create and run the select statement shown below, I get what I want.

select distinct orderid, 'format', format, count(format) from orderdetails group by orderid, format;

But how do we translate the manual query into an equivalent trigger function? How do we distinguish the two "new.columnname" from each other?

The structure of OrderDetails does not change. It is very stable.


texts: Where does SQL Server store symmetric keys?

texts:
I am wondering if symmetric keys created in particular database are being stored in the primary file group?

Or they are stored in some special file group which the user is not able to interact with?

For example, if I am performing a partial backup to a file group (primary or secondary) can I be sure that the backup does not contain the symmetric keys?


texts: Getting related data from an uncertain column

texts:
I am working on a friendship logic, the table friendship has this structure:

`friendship_id` INT NOT NULL ,
  `user1_id` INT NOT NULL ,
  `user2_id` INT NOT NULL ,
  `status` INT NULL ,
  `created` DATETIME NULL ,
  `message` VARCHAR(255) NULL ,

And the profile table.

`profile_id` INT NOT NULL ,
  `user_id` INT NOT NULL ,
  `first_name` VARCHAR(255) NULL ,
  `last_name` VARCHAR(255) NULL ,
  `image_url` VARCHAR(255) NULL ,
  `gender` INT NULL ,
  `city_id` INT NULL ,
  `country_id` INT NULL ,

I am trying to get a list of the the friends of a certain user, but I want related info also, for example their city.

The problem is the user_id could be either in user1_id or user2_id.

I can try with a left join but the results are not ok:

select
        city.name,
            p1.first_name as 'user1_first_name',
            p1.last_name as 'user1_last_name',
                p1.user_id as 'user1_id',
                p1.profile_id as 'user1_profile_id',
                p1.image_url as 'user1_image',
                p2.first_name as 'user2_first_name',
                p2.last_name as 'user2_last_name',
                p2.user_id as 'user2_id',
                p2.profile_id as 'user2_profile_id',
                p2.image_url as 'user2_image'
            from friendship f
            left join profile p1 on p1.user_id = user1_id
            left join profile p2 on p2.user_id = user2_id
            left join city on p1.user_id = 1 and city.city_id = p1.city_id or city.city_id = p2.city_id
            where f.user1_id = 1 or f.user2_id = 1 and f.status = 2

Note: Probably my sql is not the best thing you'll ever see :)

What's the best way to get the information?


texts: Can a transaction log backup create a new database before any full database backup is applied? SQL Server 2017

texts:
We have a large database we restore daily from a single t-log backup from a remote supplier. We are looking to move to 15 minute t-logs, but applying these to the current database on prem it is then very hard to see just what has changed, to update the downstream warehouse tables. A hypothetical solution would be if it were possible to take a t-log as it came in, restore it to a new database, and then be able to compare tables in that with the tables in the full database to then create some sort of UNION view that reflects the current state.
But I am 99.9% sure you cannot restore a t-log if you have not restored a full backup before, hence I am looking at ApexSQL Log to read the t-logs instead.
But if I'm wrong, please let me know! I'd love to be able to restore a single t-log to a new database!


texts: psycopg2 - drawbacks of reusing cursors?

texts:
I am new to Python and the psycopg2 module.

context

Working on a script that select data from MySQL, perform some operations (conversion of data-types and other "transformations"), and finally insert data into PostgreSQL using the psycopg2 module.

main problem

I've read on the official website of psycopg2 that it is better to instantiate new cursors each time it is possible:

  When should I save and re-use a cursor as opposed to creating a new
  one as needed?
      Cursors are lightweight objects and creating lots of them should not pose any kind of problem. But note that cursors used to fetch
  result sets will cache the data and use memory in proportion to the
  result set size. Our suggestion is to almost always create a new
  cursor and dispose old ones as soon as the data is not required
  anymore (call close() on them.) The only exception are tight loops
  where one usually use the same cursor for a whole bunch of INSERTs or
  UPDATEs.

but the nature of my script seems to require the use of only one cursor for a whole bunch of inserts since I've add the check if the dataset is empty before try to insert in psql

for query, dataset in dict.iteritems() :
    if dataset:    # this is the check if value(dataset) is not empty
      try:
        cur_psql.execute( query + dataset )
      except psycopg2.Error as e:
        print "Cannot execute that query", e.pgerror
        cnx_psql.rollback()
        sys.exit( "Rollback! And leaving early this lucky script, find out what is wrong" )
    else:
      print "The dataset for " + query + " is empty, skipping..."

this code insert data in different psql tables at each iteration with

cur_psql.execute( key + value )

question

My doubt is if in my scenario, I will encounter any drawback using the same cursor for all the inserts. I was not able to understand if I am in the category where

  The only exception are tight loops
  where one usually use the same cursor for a whole bunch of INSERTs or
  UPDATEs.


texts: Postgresql Restore - duplicate key value violates unique constraint

texts:
I have a PostgreSQL 9.2 server and want to restore this to PostgreSQL 9.6.

Here is the table.

create table mytable (
ID int,
EID int,
Name varchar(100),
Address varchar(300),
primary key (ID, EID)
)

While restoring this I'm getting this error.

COPY failed for table "mytable ": ERROR:  duplicate key value violates unique constraint "ID_EID_pkey"
DETAIL:  Key (ID, EID)=(18, 2) already exists.

Here is the select query results of the mytable.

select ID, EID from mytable where ID=18;

ID | EID
___|____
18 | 2

(1 row)

select ID, EID from where mytable where EID=2;

ID | EID
___|____
10 | 2
14 | 2
18 | 2
(3 rows)

As per my knowledge, PostgreSQL will create a Primary key on ID column and Unique index on EID.

So the EID is the unique column.

\d+ mytable  --On both servers

     Column      |            Type             | Modifiers | Storage  | Stats target | Description
-----------------+-----------------------------+-----------+----------+--------------+-------------
 ID              | integer                     | not null  | extended |              |
 EID             | integer                     | not null  | plain    |              |
 Name            | character varying(100)      |           | plain    |              |
 Address         | character varying(100)      |           | plain    |              |
Indexes:
    "ID_EID_pkey" PRIMARY KEY, btree (ID, EID)
Has OIDs: no

My Questions,

How the unique column has 3 duplicate values (value 2)?
While restoring why its throwing this error?


texts: Sum currencies excluding duplicatied records

texts:
I have a CRMOffer table containing customerID and other CRMOffer data. And a Sell table containing customerID and sellValue and sellPK.

Sell table has a explicit PK, CRMOffer sadly doesn't have one available.

I must join these tables, so that I can relate CRM offers to actual sells.

If Sell table doesn't have a specific customerID, sell value will be null. But if it does, and there are multiple CRM offers to the same customer ID, the join will result in duplicated SellPK and sellValue.

I must then group by CRMOffer's customerID and sum sell values, but sum only once for each SellPK.

A given sell event may be duplicated, in case CRM made multiple offers to a customer that generated a sell. But there may also be multiple distinct sells from the same customer.

Sorry I think the explanation is kinda confusing. Basically I must sum values of distinct sells being careful to exclude duplications resulted to multiple CRM offers. I can see if a sell is duplicated by its SellPK. I can use count(DISTINCT customerID) to remove duplications from customerID, but I can't find a way to remove duplications from sell value.


texts: Oracle - 'Correctly' configured ACL fails with network access denied by ACL

texts:
I have the exact same ACL privileges (used same sql script) on two separate databases.  It works on one, but is denied on the other.  Both databases are meant to connect to the same resource.  Tried checking all the things in the following url:

https://support.oracle.com/knowledge/Oracle%20Database%20Products/1074843_1.html

at least 4 times but to no avail.  I have tried dropping them and re-adding them (again, with the same script) on both databases but the outcome is the same.  All variables seem to be the same.

Anyone have any experience with this?


texts: How to read all metadata without select permissions to actual data?

texts:
I need to create a user account that can read the metadata for all objects from information_schema

I can access the information_schema with any user but only for tables that the user has select privileges. Is there any role (like Oracle's SELECT_CATALOG_ROLE) that I need?


texts: Monitoring Log-Shipping for Multiple Databases

texts:
We are setting up log-shipping for multiple databases hosted at different server and while process began, I noticed that only one SQL job is being retained at Monitoring Server.

Our plan was to have one monitoring server which will be monitoring all the replication between Production and DR for databases and we can set-up alert in case any of the replication goes bad.

Kindly suggest how to make multiple jobs possible on the monitoring server.


texts: Firebird 3.0 service won't start on Windows 7

texts:
I have Firebird 2.5 classic server running on Windows 7 on port 3059. This service is set to automatically start up and it works fine.

I've installed Firebird 3.0.2 as classic server on port 3050 but service won't automatically start. I can start it manually but I really need it to start automatically.

  Error 1053: "The service did not respond in a timely fashion"

is the error I get in Event Viewer. Windows has only one, administrator user, without password. That's only error.


texts: Cannot view sql server agent in sql server 2005?

texts:
I have installed full setup of SQL Server, and start the service for SQL Agent through SQL Server surface area. But still I can't get SQL Agent tree node in my SQL Server connection treeview.

So please help me what I have to do for getting SQL Agent in treeview.


texts: Is indexing the following columns efficient?

texts:
I have the table: orders with the following columns

(orderNumber, orderDate, shippedDate, status, users_userId)

I indexed orderDate, shippedDate, status for performing similar queries to the following:

SELECT orderNumber FROM orders WHERE status='shipped'
ORDER BY orderDate, shippedDate;

Since there are only about 14 possible statutes for an order which I thought  would speed up the search whenever I need to select range of rows of orders with a specific status.

Also since indexes are sorted,creating an index on the orderDate and shippedDate it allows a query's ORDER BY specification to be met without a separate sorting step.

I just want to know if this sounds correct and if am I really optimizing my query in this way?

Here is the EXPALIN output of my query:

id: 1
select_type: SIMPLE
table: orders
type: ref
possible_keys: composite_index
key: composite_index
key_len: 138
ref: const
rows: 1
filtered: 100.00
Extra: Using where; Using index


texts: MySQL: Is there a way to find out which user called stored procedure?

texts:
Server: MySQL 5.7.14
I am following the Suggestion 1 suggested here https://dba.stackexchange.com/a/109416/126860

I am trying to find out which user called the stored procedure. But since I am also using a definer. The CURRENT_USER(); function returns the "definer" user name.

I have tried using SHOW PROCESSLIST; to find out who is running the stored procedure. but it doesn't show stored procedures as a separate process.

Is there a way to find the actual user who invoked the stored procedure.


texts:
In 2018, does it still hold true that MySQL (InnoDB) CHAR performs better VARCHAR speed-wise?

I've only found a performance comparison explanation that is 7 year old. I've checked the official MySQL 8 CHAR vs VARCHAR but the article speaks about storage only, not about performance. I've also read a notice that VARCHAR causes some problems with table fragmentation (see the very 1st comment to this answer.


texts:
Why would I get an 'incorrect syntax' error with this (on the OUTPUT keyword):

DELETE tb
    FROM dbo.TransactionBatch tb
    OUTPUT Deleted.TransactionID INTO #TransactionIDs
    INNER JOIN @BatchTransactionIDs tid
        ON tb.TransactionID = tid.TransactionID

@BatchTransactionIDs is a User-Defined Table type


texts: Global Temporary Table used in Stored Procedure executed concurrently across sessions

texts:
I use dynamic SQL in a stored procedure and must use a global temp table so the temp table and its data is available outside the instance when sp_executesql happens.

I need the stored procedure to be able to be run multiple times simultaneously. Could I do something like this?

...

BEGIN TRY
WHILE OBJECT_ID('tempdb..##BOM', 'U') IS NOT NULL
BEGIN
WAITFOR DELAY '00:00:01'
END;

...
Here is the general idea behind the stored procedure. (This is not the code I use just a gist of what I'm doing)

CREATE TABLE ##BOM(...);
DECLARE @TBL AS TABLE(...);

INSERT INTO ##BOM
SELECT * FROM EXPLODE_BOM_F(@ORG, @JOB); --all data inserted into temp table for job

SET @SqlQuery = 'SELECT * FROM ##BOM LEFT JOIN STUFF WHERE 1=1 '; --build sql query
IF @Param IS NOT NULL
   SET @SqlQuery = @SqlQuery + ' AND ##BOM.ParamField = @Param' --add to sql query if specific params are passed to SP

INSERT INTO @TBL (Fields, field...) --insert records into table
EXECUTE sp_Executesql @SqlQuery; --from final sql query

SELECT * FROM @TBL;


texts: Will Changing the Max Memory Setting Invalidate Query Plans?

texts: I recently lowered the max memory from the default (unlimited) down to 20 GB. Does this erase the oldest queries in the plan cache?


texts: Detect bottleneck of server via psycopg2

texts:
I have PostgreSQL superuser called "admin" which can connect to a PostgreSQL database.

I am not managing the database server. I have no root-access to this machine.

Sometimes the performance is not good.

Up to now I have no numbers, but I would like to have some.

Is there a (common) way to do small benchmarks on the production db to see how it performs?

I have something like this on my mind: Every ten  minutes some magic sql commands get execute which give me some numbers. I would like to see CPU/MEM/IO usage/performance.

The same queries lead to different performance. I guess the hypervisor hosting the PostgreSQL VM is overloaded from time to time

The current situation is not nice for me, since the customer sees the slow application, and blames me. But it is not me, it is the slow DB.

How can I proof that the DB is slow, and not the application?

(I guess this situation is common. How to do handle this?)


texts: MySQL Master Slave Replication and backup

texts:
I would like to implement the following backup solution as highlgihted on the mysql site:

  To use replication as a backup solution, replicate data from the
  master to a slave, and then back up the data slave. The slave can be
  paused and shut down without affecting the running operation of the
  master, so you can produce an effective snapshot of “live” data that
  would otherwise require the master to be shut down.

I will have the master -> slave replication running every 5 minutes and then perform a full backup of the slave every 30 minutes

I understand that when the slave is backed up this is a database dump which will require all tables to be locked. Therefore the master wont be able to replicate to the salve, will this result in any errors on the master side? What actually happens in this case? Will the replication fall over if master cant replicate to the slave or will it handle gracefully?

I can accept if the master cant replicate when slave is backed up as the replication is quite frequent. i would just like to know what happens if the master is trying to replicate and the slave is locked.


texts:
I am having a problem with syntax.  I have the following statement which updates a field in a table with a value from a field in a different table. I need the statement to update the field with the text in field 142 unless field 142 is null, then update with field 17. Here is the existing code:

--Defendant Address
update xrji_:Usernum
    set DefendantAddress=trim(MemoText)
from eqanswer
where eqanswer.entitynum=xrji_:Usernum.DefendantEntityNum and
                       entityrole='DEFENDANT' and fieldnum=142
;

Columns MemoText, entityrole and fieldnum belong to the eqanswer table.

Let me know if this needs further clarification.
Any suggestions would be appreciated.

DBMS is: Advantage.


texts: Why my query does not return correct data (NORTHWIND)?

texts:
select *
from orders o
WHERE EXISTS
(
  select 1
  from orders o2
  where o2.EmployeeID = o.EmployeeID
    AND YEAR(o2.OrderDate) = 1997
    and MONTH(o2.OrderDate) = 9
    AND DAY(o2.OrderDate) = 5
)

I expect to receive only two records in result instead of 228.
As you can see this is EXISTS on the same table.
Thank you in advance.


texts: Why am I getting a snapshot isolation issue on INSERT?

texts:
Given two tables

Parent

KeyID   GroupID   Name  Active

Child

KeyID   ParentID  Name

Child.ParentID is FKed to Parent.KeyID

We insert both Parent and Child in a single transaction.

If a different Parent row gets updated (eg. Active 1 -> 0) while the transaction is active, the Child INSERT fails with:

  Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Child' directly or indirectly in database 'Test' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

From what I can tell from Why am I getting "Snapshot isolation transaction aborted due to update conflict"? this is probably due to a full scan to verify the foreign key.

Indeed, removing the foreign key does allow the Child INSERT to complete as expected.

With that said, no amount of non-clustered indexes on the foreign key on Child table seem to be helping to resolve this issue, so I'm a bit at a loss of what to do.

We have RCSI turned on for this database and the transaction is running in Snapshot isolation mode.

Additional details

I have discovered this issue manifests when the insert to Child is larger than a given number of rows. At this point the query optimizer switches from a Nested Loops (Left Semi Join) to a Merge Join (Left Semi Join).

Apologies for not including the fact that multiple Child records are inserted for a single Parent record.

Working insert (20 child records):

Failing insert (50 child records):

Insert sproc is roughly this:

CREATE PROCEDURE dbo.[usp_InsertRecords] (
    @journal dbo.ParentType READONLY,
    @journalItems dbo.ChildType READONLY,
    @tenantId INT
) AS
BEGIN
    INSERT INTO dbo.Parent(GroupID, Name, Active, TenantId)
        SELECT GroupID, Name, Active, @tenantId FROM @journal

    DECLARE @JournalId INT = convert(int,scope_identity());

    INSERT INTO dbo.Child(ParentID, Name, TenantId)
        SELECT @JournalId, Name, @tenantId
        FROM @journalItems j2

END
GO

And concurrent update would be something like:

UPDATE dbo.Parent Set Active = 0 WHERE KeyID = 1234 -- row not being inserted


texts: Will a delete then insert within a transaction cascade?

texts:
I had a question about what the expected and actual behaviour for the following scenario is.

The scenario is one where a database table ([table1]) is cleared and reloaded every day. The table has an id column which is reference by several other tables using foreign keys. If I set the foreign key ON DELETE action to CASCADE, obviously this will delete rows in the other tables if I issue a single DELETE FROM [table1] command on this table. However, what would happen if I were to delete all the rows, and then re-insert the same rows with the same IDs under the same transaction? Will this trigger the cascade mid-transaction, or will the foreign-key reconciliation happen once I've called commit?

Obviously, I'm thinking SQL Server here, but I'm wondering if this behaviour is consistent across other DBs as well.

In the event that the cascade is triggered even in the middle of the transaction, what would be the best way to go about managing foreign keys and relationships with a table which is completely cleared and re-loaded every day?


texts: Confusion in Informix when selecting dates with JOIN

texts:
I am trying to run a query in Informix, doing a join in a table of logs where we save the record of insertion of the rows.

Notice that the query below brings two results, with different insertion dates:

select * from log_cipessoa where cgccpf = 1541247000188

Results:

 cgccpf         tppessoa    tp_dml  dt_insercao
 -------------  --------    ------  ---------------------
 1541247000188         1    INSERT  2018-03-06 15:05:42.0
 1541247000188         2    INSERT  2018-03-27 09:54:45.0

Now, I want to bring the rows that were inserted after the 2018-03-21 day, but while doing the join, informix seems to merge the dt_insercao column with the most recent date.

And according to the log above, Informix should only return the second line, where the column tppessoa with the value 2 and dt_insercao 2018-03-27 09:54:45.0

SELECT pe.cgccpf
      ,pe.tppessoa
      ,REPLACE(LTRIM(RTRIM(pe.nomepessoa)), '  ', ' ') AS nomepessoa
      ,lg.dt_insercao
FROM CIPESSOA pe
INNER JOIN LOG_CIPESSOA lg
ON lg.cgccpf = pe.cgccpf
WHERE    lg.tp_dml = 'INSERT'
     AND lg.dt_insercao > '2018-03-21 20:09:32' AND lg.dt_insercao <= '2018-03-31 20:00:00'
     and pe.cgccpf = 1541247000188 --FILTER ONLY FOR DEMONSTRATION

Results:

 cgccpf         tppessoa    nomepessoa      dt_insercao
 -------------  --------    -----------     ---------------------
 1541247000188         1    XPTO LTDA       2018-03-27 09:54:45.0
 1541247000188         2    XPTO LTDA       2018-03-27 09:54:45.0


texts: Flat file manager TextQualifier not working

texts:
I've a CSV text file which contains

col1,col2,col3

"abc,cdef",a,b

When I use the Flat File Source and preview the
a

bc,cdef appears on first column.

But when i run and view the result in data viewer

the first column contains "abc and second column cdef"...

I've a textQualifier set to "

can anyone please help. I'm using SSIS 2008 R2


texts: Variable inside another variable in SQLCMD mode

texts:
Trying to tidy up my SQL script with SQLCMD mode and I ran into an issue:

:setvar db_suffix "some_suffix"
:setvar some_db "some_db_$(db_suffix)"
print 'some_db: $(some_db)'

The output of that is:

some_db: some_db_$(db_suffix)

However, what I expected was:

some_db: some_db_some_suffix

Is there a way to have variable interpolation like this?

(Note that T-SQL functions such as CONCAT() won't work since I'll be using the variable as a database name).


texts: DECODE or CASE STATEMENT for PARAMETER in WHERE CLAUSE

texts:
I am having an issue where I need to make 1 parameter have multiple options, almost like a case statement or decode function.
Here is a snippit of the query below, We were using 2 parameters so he can have either or, or use a wildcard but we want to narrow it down to 1 parameter.

What I am looking for is.. if he where to choose '%' for the &industry parameter, it will show spc.characteristic_code in ('ARLFT','MINE')
other wise he would put either 'MINE' or 'ARLFT' in the &Industry parameter.

If I need to clarify, please advise. Thanks in advance!

  SELECT INV.COUNTRY
    , INV.NAME
    , INV.STATE
    , INV.CUSTOMER_ID
    , INV.CUST_GRP AS DIVISION
    , INV.DISTRICT_CODE
    , INV.SLSPRT
    , SP.CATALOG_DESC
    , SPC.CHARACTERISTIC_CODE
    , SUM(INV.QUANTITY) QTY
    , SUM(INV.SALES) SALES
    , SUM(INV.COST) COSTS
    , SUM(INV.SALES - INV.COST) AS MARGIN
    FROM IFSINFO.HB_INVOICING_ALL INV
        JOIN IFSAPP.SALES_PART_CHARACTERISTIC SPC
            ON (INV.SITE = SPC.CONTRACT)
            AND (INV.SLSPRT = SPC.CATALOG_NO)
        JOIN IFSAPP.SALES_PART SP
            ON (INV.SITE = SP.CONTRACT)
            AND (INV.SLSPRT = SP.CATALOG_NO)
    WHERE (TO_DATE(INV.INVDATE)
        BETWEEN TO_DATE('&Start_Date','mm/dd/yyyy')
        AND TO_DATE('&End_Date','mm/dd/yyyy')
        OR  TO_DATE(INV.INVDATE)
        BETWEEN TO_DATE('&Start_Date2','mm/dd/yyyy')
        AND TO_DATE('&End_Date2','mm/dd/yyyy'))
    AND INV.SITE = '&Site'
    AND INV.CUST_GRP like '&Cust_Grp'
    AND SPC.CHARACTERISTIC_CODE like '&Industry'
    GROUP BY INV.COUNTRY
    , INV.NAME
    , INV.STATE
    , INV.CUSTOMER_ID
    , INV.CUST_GRP
    , INV.DISTRICT_CODE
    , INV.SLSPRT
    , SP.CATALOG_DESC
    , SPC.CHARACTERISTIC_CODE


texts: SQL Server: Can Page Verify be set to Checksum on any database, or must I check compatibility?

texts:
In Microsoft SQL Server, I have some older databases set to "Torn Page Detection" rather than "Checksum" page verify.

Do I need to determine if a given SQL Server database is 'compatible' with the "Checksum" page verify setting, or is it something that can be applied to any SQL database?

Are the databases even aware what their setting is? It is a setting that is handled transparently by SQL Server correct?

I realize that the change will only affect pages changed after the config takes effect.


texts:
I have a table containing a list of numbers. How do I find numbers that are missing from the table?

For instance:

numbers   id
 1         1
 5         2
 3         3
 6         4
 7         5
 8         6
 9         7
 20        8
 ....

How can I find missing numbers?  Like 4 and there are no numbers between 9 and 20.

I have tried nothing, but want to know.


texts: View SQL 2008 sp_cursorexecute Underlying Query and Execution Plan

texts:
I'm performance tuning a Dynamics AX application and see in a SQL trace a long-running, high-I/O query of the form exec sp_cursorexecute 1073742882 ... When I try and run that query in a new SQL Management Studio window, I get an error Could not find prepared statement with handle 1073742882. I'm not sure, but it seems that a cached plan is connection-specific. I have no sp_cursorprepare in my trace; repeating the use case reveales the same prepared handle ID with a new cursor. Since it's a shared environment I'm connecting to, I think I'd have to reset the app server and trace its startup in order to see it.

Is there any way to use the cursor's prepared handle ID to see the underlying query?
Is there any way to correlate a row in dm_exec_cached_plans to this cursor?
Is there any way to see the execution plan via dm_exec_query_plan or some other way?


texts: Upgrade MySQL Master Slave Replication Cluster from 5.7.22 to 5.7.24

texts:
I have a two node MySQL cluster with Master Slave replication.
The MySQL version is 5.7.22 and the OS is CentOS.

I need to upgrade the MySQL version to 5.7.24 due to some security reasons.

However, I need to

keep the existing data
keep the existing user privileges
do with a zero downtime
keep the master slave replication after upgrading

What would be the correct approach to do this with the above requirements.


texts: How to copy table content before deleting to flat file using BCP

texts:
I have created stored procedure where, I delete data in any given time. I want to save the records to a flat file before deleting in case I need to import the data back to table.
I DO NOT want to call from the command line as I have a program that calls the stored procedure to schedule the job.

CREATE PROCEDURE [dbo].[Clean] (
 @DeletionDate date
) AS

DECLARE @id int,
    @intError int

        DELETE FROM
      Errn
     WHERE
    erid IN (
         select
             distinct m.ID
         from
             Member m
         left join
             (select
                  *, row_number() over (PARTITION BY rid order by ceid desc)
           as rn
              from TypeA) x
             on m.ID = x.ID and x.rn = 1
           where
             (
                 (x.ceid is null and m.DateTime < @Deletion)

             );
     ON_ERROR:
     RETURN @intError
    RETURN 0

How can I copy the results of the select statement to a .txt file before deleting the records?


texts: ORA-01503 CREATE CONTROLFILE FAILED... while trying to create a database

texts:
i am trying to create a new database along with another database. The server already has a database running. I used DBCA command to create the new db instance. First i set the new SID and home and then ran the dbca command.. But half way through installation im getting the error

ORA-01503 CREATE CONTROLFILE FAILED

ORA- 01158: database already mounted


texts: SQL Server 2016 SP1 + strategy for long term version retention (snapshots, temporal tables, other?)

texts:
We have a data mart fed from an upstream data warehouse that we generate reports out of for external parties. I have a business requirement to preserve the state of the database for each month end to ensure reproducibility of results in case we are asked to do more analysis on the same time period. Database is <100GB, about 40-50 tables, nothing too crazy.

I know there are some interesting new options in SQL 2016 SP1 to consider. My goals here are less about quick/easy access to prior months and more about integrity/immutability of the data, but any solution that covers the latter while coming out ahead in the former wins the round.

Which approach makes the most sense here?

Traditional backups: Archive to external storage, restore as needed. But this is clearly the most hassle from the DBA side, and the least self-service option. Note: Since I need to keep around all the copies, after while this will be a lot of storage space.
Database Snapshots: Create snapshots and keep indefinitely. But I'm not aware of any solution that lets me safeguard the snapshot itself, from what I've if there is any corruption on the server(s) then your snapshots are hosed and there is no way to reproduce them. But the self-service aspect looks hard to beat. Is there a 3rd party solution I'm missing?
Temporal Tables: The cool new feature of 2016, is anyone actually using this? Refactoring our scheme to support this looks like a heavy lift, so maybe this is off the table.
Based off of @Max Vernon's suggestion...Monthly Restores to Read-Only DBs, maybe using Stretch Database: This sounds pretty good as this will be a lot of cold data....buuut is anyone actually using Stretch Database? Is this something Microsoft will quietly deprecate in a couple of years?
Something else I'm totally missing here?

Update: As of mid-2019, Stretch Database has a pretty high cost to get off the ground, starting at about $1.8K/mo just for compute in Azure (storage extra) so I would say this puts it out of reach for all but the larger use cases.


texts:
I'm having a database which is used heavily. A lot of writing/reading is done simultanosly. Therefore I've decided to use snapshots to reduce/avoid deadlocks.

I'v turned on READ_COMMITTED_SNAPSHOT, but do I need to specify what transaction level to use in each SP aswell?

Thanks a lot!


texts:
We use Ola Hallengren's DatabaseBackup stored procedure to backup a load of SharePoint databases on a SQL Server 2012 instance into Azure blob storage. We have been doing this for quite a while without any issues. However, for the last 6 weeks our DIFFs are randomly being promoted to FULL and we cannot find out why.
This is the output from the agent step

BACKUP DATABASE [Database] TO URL = N'https://strorgage.blob.core.windows.net/server/instance/Database/2020/11/diff/Database_FULL_20201105_200000.bak'
WITH NO_CHECKSUM, COMPRESSION, CREDENTIAL = N'*storeageaccountname*'

If you have a look at the generted URL you will note that the procedures is storing in the DIFF directory, but creating a FULL backup file.
https://strorgage.blob.core.windows.net/server/instance/Database/2020/11/diff/Database_FULL_20201105_200000.bak
                                                                        --^           --^

DatabaseBackup (Ola proc) is from 2019-06-14 so it needs an upgrade to be fair, but it has worked well for over 18 months.
We do not call Ola code directly as we have a small wrapper procedure which builds up the virtual path name for Azure, but essentially this is how we call Ola's code.
This is an issue were for some unknown reason DIFF backups are being promoted to FULL, this causes petabytes of Azure blob backups instead of gigabytes - each day.
EXECUTE dbo.DatabaseBackup
    @Database = @DatabaseName,
    @URL = @BackupPath,
    @Credential = @StorageAccount,
    @BackupType = @backupType,
    @Compress = @Compression,
    @LogToTable = 'Y',
    @ChangeBackupType = 'Y',
    @Updateability = @DatabaseReadOnlyState,
    @DirectoryStructure = NULL,
    @AvailabilityGroupDirectoryStructure = NULL

Do you have any thoughts on that?


texts: How do I Configure Per Database Roles for PostgreSQL?

texts:
The default for PostgreSQL is that roles are created per cluster not per database.  I would like to have per database roles.  I could have sworn I once read about a way to set that up but now I can't find any reference to it.  I think it was an initdb option or a createcluster.conf setting or similar but I've read the initdb documentation thoroughly and can't find anything on this.

Is there a way to set per database roles in PostgreSQL?


texts: Why doesn't join elimination work with sys.query_store_plan?

texts:
The following is a simplification of a performance problem encountered with the Query Store:

CREATE TABLE #tears
(
    plan_id bigint NOT NULL
);

INSERT #tears (plan_id)
VALUES (1);

SELECT
    T.plan_id
FROM #tears AS T
LEFT JOIN sys.query_store_plan AS QSP
    ON QSP.plan_id = T.plan_id;

The plan_id column is documented as being the primary key of sys.query_store_plan, but the execution plan does not use join elimination as would be expected:

No attributes are being projected from the DMV.
The DMV primary key plan_id cannot duplicate rows from the temporary table
A LEFT JOIN is used, so no rows from T can be eliminated.

Execution plan

Why is this, and what can be done to obtain join elimination here?


texts: SQL Login keeps losing access to databases

texts:
We created a SQL Login account for reporting use only (SSRS), we'll call it 'MyReportAcct'.

We've used this account for some time and it is created for every newly installed instance.  It has been working as you'd expect, except on a couple of mirrored instances.  I've not seen this issue on any standalone instances in my environment.

Randomly, reports will stop working and there is nothing being changed based on our internal processes for Change Management, Windows Logs, and SQL Error logs.  Here are two of the error messages we receive:

Upon investigation of the account, it shows up under the Security object folder in SSMS.  Also, we find that the account is associated to all the databases under User Mapping for the account properties.  I've verified that 'MyReportAcct' has public and db_datareader, so on the surface every looks as expected.

The rub comes when I try to alter the account or remove the mappings.  I'm met with:

Cannot alter the user because it does not exists or you do not have
permission (Error 15151).
Cannot drop the user dbo.. (Error 15150)

What I've had to do to fix the issue is delete every instance of 'MyReportAcct' on the server, then recreate it, then add to each database.  This doesn't happen all the time, but often enough that it is irritating.  Further, we shouldn't have to do this at all.

I think that we should change this account to an Windows Account instead of a local SQL Login in every instance.  I have a feeling that this might fix the issue, but I need to prove why or provide a reasonable answer.  I think part of the problem is the mirroring in some way, but I don't know how to prove that either.

Security is a weakness of mine and I'm employing steps to change this, so any insight you can provide would be greatly appreciated.  Please let me know if you need more information.


texts: Automatic index creation for primary vs. foreign keys in Postgresql

texts:
For PostgreSQL 9.4 (http://www.postgresql.org/docs/9.4/static/ddl-constraints.html) it is stated that

  A foreign key must reference columns that either are a primary key or form a unique constraint. This means that the referenced columns always have an index (the one underlying the primary key or unique constraint); so checks on whether a referencing row has a match will be efficient. Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns too. Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on the referencing columns.

and I wondered whether the author(s) of these lines were overlooking one major point: When I have a parent-child relation (e.g. a cart with cart positions) where the child (cart position) marks its relation to the parent (cart) using a foreign key to the parents (cart) primary key when I want to get all children for a given parent I use the non-indexed foreign key column.

So, does the overhead of not generating an index for foreign key columns by default (in contrast to primary key columns) and letting the user experience suboptimal performance on one hand overweigh the overhead of maintenancing an index which, at smaller table sizes, do only minor improvements of query performance on the other hand?

Perhaps someone has more insights in database implementation details and reasonings leading to this decision and/or can point me to reasons why a default index generation for foreign key columns (in Postgresql) could lead to unnoticed serious problems.


texts: How to force Windows to support not supported LC_COLLATE and LC_TYPE in PostgreSQL?

texts:
I have fresh installation of PostgreSQL 9.1.x. Unfortunately, I discovered that OS doesn't support LC_COLLATE = 'pl_PL.utf8' and LC_CTYPE = 'pl_PL.utf8'. I read in PostgreSQL documentation that:

  On all platforms, the collations named default, C, and POSIX are
  available. Additional collations may be available depending on
  operating system support.

Is a way, however to force Windows to support that locale ? I mean a patch, plugin or something.


texts: How do I effectively handle very skewed data? Statistics up to date but don't seem to help

texts:
I have a table, dbo.ClaimBilling, with 130,000 rows. In this table, the column OperatorID is a varchar(max) and is heavily skewed.  125,000 rows are 'user1', and the remaining 5000 rows are split between 6 other values, with 'user2' having a total of 3 records.

There is a non-clustered index on OperatorID, and the clustered index is the primary key, IDClaimBilling.

I have the following query currently:

SELECT DISTINCT IDClaimBilling
FROM dbo.ClaimBilling cb
INNER JOIN dbo.BillingItem bi
    ON cb.IDClaimBilling = bi.ClaimID
WHERE OperatorID = @operator

No matter what value @operator is, the estimate of rows from ClaimBilling is ~4000, which is not close to what any value would return, and it's always a clustered index scan, it doesn't use the operatorID index.  If I remove the join and do

SELECT DISTINCT IDClaimBilling
FROM dbo.ClaimBilling
WHERE OperatorID = @operator

then it does use the OperatorID index, but again the estimates are wrong regardless of the value of @operator, this time always estimating ~18,000 or so.

I did an UPDATE STATISTICS dbo.ClaimBilling WITH FULLSCAN before running the queries.

Why are these estimates so wrong even if the statistics know exactly how many rows there are per value?

I'm declaring and assigning @operator a value in testing.  It was originally part of a procedure and I assumed that was the problem, but it behaves the same when used in an ad hoc statement as well.

The query only runs when the user first logs in, so only maybe a couple times a day per user.


texts: is id and phone number enough to uniquely identify a row in phone numbers

texts:
Was designing a table a customer_phone_numbers table a while back, which only purpose was to allow us to store as many phone numbers as a user wanted to have on file. I originally came to the conclusion that customer_id + phone_number was unique, but then it occurred to me, it is possible for the same user to have 2 extensions at the same number. I am not sure how likely that is though, and it creates another issue.

phone_number ext
555-555-555, NULL
555-555-555, NULL

becomes legal, unless I find a good way to constrain ext against null. What is the best way to normalize and constrain phone numbers?


texts: Can MySQL use an index to calculate SUM?

texts:
The following query is run in a MySQL database (all InnoDB tables, Percona 5.5), and is slow for some parameter values (i.e. for some large customers).  It returns the total size of all files uploaded by a given customer:

SELECT SUM(document.size)
  FROM document
       INNER JOIN post ON post.id = document.id
       INNER JOIN page ON page.id = post.page_id
 WHERE page.customer_id=1234;

I tried denormalization -- added document.customer_id column and index on document(customer_id, size) -- and got much better query times.  But I'd like to avoid denormalization if possible.  I also tried adding just an index on document(size), but couldn't get MySQL to use it.  The documentation states that every index in an InnoDB table also contains references to the primary key, so why did MySQL not use this new index when joining, and save itself the need to load document row data?

The schema looks like:

CREATE TABLE page (
  INT id NOT NULL,
  INT customer_id NOT NULL,
  ...
  PRIMARY KEY (id),
  INDEX ix_page_customer (customer_id)
  FOREIGN KEY (customer_id) REFERENCES customer (id)
)

CREATE TABLE post (
  INT id NOT NULL,
  ...
  PRIMARY KEY (id),
  INDEX ix_post_page (page_id),
  FOREIGN KEY (page_id) REFERENCES page (id)
)

CREATE TABLE document (
  INT id NOT NULL,
  INT size NOT NULL,
  ...
  PRIMARY KEY (id),
  FOREIGN KEY (id) REFERENCES post (id)
)

A customer contains many pages, a page contains many posts.  Posts can be of different types (e.g. message, document).  There are tables for each of these post subtypes, of which document is one.  One of the larger customers has 435K posts (of which 8K are documents) spread over 25K pages.

The query is fairly fast for smaller customers (i.e lower pages*posts), but slow for larger customers.  Here's EXPLAIN output for the query for a large customer:

+----+-------------+-----------+--------+--------------------------+------------------+---------+-----------------------+-------+-------------+
| id | select_type | table     | type   | possible_keys            | key              | key_len | ref                   | rows  | Extra       |
+----+-------------+-----------+--------+--------------------------+------------------+---------+-----------------------+-------+-------------+
|  1 | SIMPLE      | page      | ref    | PRIMARY,ix_page_customer | ix_page_customer | 4       | const                 | 51200 | Using index |
|  1 | SIMPLE      | post      | ref    | PRIMARY,ix_post_page     | ix_post_page     | 4       | database.page.page_id |     2 | Using index |
|  1 | SIMPLE      | document  | eq_ref | PRIMARY                  | PRIMARY          | 4       | database.post.post_id |     1 |             |
+----+-------------+-----------+--------+--------------------------+------------------+---------+-----------------------+-------+-------------+


texts:
First off, I'm using SQL Server 2008 R2.

When I set up my maintenance plan and got to the "Select Report Options" step, I selected to email the report to the Operator I already have set up.  This does not allow me to alter the subject, which I believe Gmail uses when it collapses emails into conversations.

Is there a way to trick Gmail to separating them out based on the database?  Or is there a better way from within SQL Server Management Studio to email the reports out?  I have multiple instances, with their own Operator, each Operator is using the same email address (no_reply@noneofyourbusiness.com), but the name is different....but since the subject is the same, Gmail still collapses them into one big conversation.

Does anyone else have to deal with this issue?


texts: Login failed for user - Error: 18456, Severity: 14, State: 38

texts:
I am trying to run and ADODB command in MS Access 2012 to exec a stored proc in our SQL server 2008R2 database, but I keep getting error 38 and SQL profiler shows the following:

  Error: 18456, Severity: 14, State: 38.
  2015-01-28 12:59:27.34 Logon
  Login failed for user 'NordenDevel'. Reason: Failed to open the explicitly specified database. [CLIENT: 82.71.5.169]

This user (NordenDevel) login works on this database (NordenWeb) in SQL Server Management Studio and in Visual Studio, so the database is working fine and I do not see how it could be a permission issue.

Can anyone suggest what I could do to find out what's wrong?

Many thanks, YAOWSC


texts: PostgreSQL data directory/files portability

texts: I have lost my PostgreSQL 9.1 installation on my Ubuntu machine by update to Ubuntu 14.10. Now I have no (reasonably easy) way to export the data from my PostgreSQL 9.1 database. Is it possible to take the data directory from the Ubuntu machine, put it to a Windows machine and have Windows version of PostgreSQL 9.1 use it so that I can dump it?


texts: Testing data integrity porting from old database to new

texts:
I've created a Web/ASP.NET/MySQL database front end & back end that gets created from the XML logs from a previous Java based XML client-server that is not serving the company very well (the XML database bloats too quickly and the server begins to time out, which halts the production floor).

Any future data will come through the web front end or API calls to the back end, and we'll eventually (after testing) shut down the old client-server database.

We're about to go live for testing with the new database, and I'm worried about data integrity with the imported data.  We ended up with a bunch of duplicate data (about 18%, see SO question about this here) which I've cleared up (or at least come up with a repeatable method to do so).  I've also tested that each of the row ID's that are primary keys actually have data associated with their foreign key children (and their's have the same PK-FK row data association down to the lowest child table, hopefully that made sense).  However, I've only tested that the rows contain data, not that they contain the right data.

My thought is to choose random log files from the old XML database, and make sure that its data is where it should be in the new MySQL version.  Ideally I'd be able to automate this, but it's complex to break down the old logs into their constituent parts to insert into the database, but maybe not so much to simply search for the data contained in them.

I'm relatively stupid when it comes to DBA stuff, and I realize what I'm asking is kind of vague, but what I'm looking for is what would a DBA want to know to make sure they were working with reliable data?  If this can be figured out and tested for (my forte), I'm going to feel a lot better about the data.  The entire company will be relying on this data once it goes live, I need to get this right.


texts: SSMS command to disconnect after query has finished

texts: In SQL Server Management Studio, is there any way (for example a SQLCMD command or something) to get the SQL script window to automatically disconnect as soon as the query has finished executing?


texts: Shredding Query Plan XML For Potential Skewed Estimates - Data Type Conversions

texts:
I'm using the below T-SQL to check for root level estimate skews in execution plans by pulling the estimated rows from the execution plans and comparing to the last rows from the sys.dm_exec_query_stats DMV.  Obviously I can't do this calculation due to the data type mismatch, converting from varchar to bigint won't work in this scenario.  Is there any way around this?

SELECT  DB_NAME(qt.dbid) AS [Database], cp.objtype AS [Cached_Plan_Type],
    cp.usecounts AS [Use_Counts],
    qp.query_plan.value('(//@CardinalityEstimationModelVersion)[1]','INT') AS [CE_Version],
    qp.query_plan.value('(//@EstimateRows)[1]', 'varchar(128)') AS [Estimated_Rows],
    qs.last_rows AS [Last Rows],
    --(qp.query_plan.value('(//@EstimateRows)[1]', 'varchar(128)') - qs.last_rows) AS [Estimate_Skew],
    qs.total_logical_reads / qs.execution_count AS [Avg_Logical_Reads],
    CAST((qs.total_elapsed_time ) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [Average Execution time(s)],
    CAST((qs.total_worker_time) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [CPU Time Average (s)],
    qt.text AS [SQL_Statement],
    qs.query_hash AS [QueryHash],
    qp.query_plan AS [QueryPlan]
FROM sys.dm_exec_cached_plans cp WITH (NOLOCK)
    CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) qp
    CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) qt
    INNER JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = cp.plan_handle
WHERE   qt.text NOT LIKE '%sys.%'
ORDER BY [CPU Time Average (s)] DESC OPTION(RECOMPILE);
GO


texts: How many fields is too much for a primary key?

texts:
I am creating a table to store information about the placement of objects in a grid (game map).

For example, I have a 10x10 grid and I want to store, in the database, that cell (5,5) has a blue box, (3,3) has a red chair and so on.

There can be multiple items in any square, but the same item shouldn't be in the same square twice.

is it acceptable to have this structure? (all columns in primary key)

 (room_id (FK), room_object_id (FK), x_cell, y_cell)(PK)

or will it be better to have this

id(PK), room_id (FK), room_object_id (FK), x_cell, y_cell

I think the first way makes more sense, but I'm worried that increasing the integrity checks whenever something is entered might create a lot of unnecessary overhead.


texts:
I asked a relevant question few months back and got answer with wide variety of options for backup.
But this time I have different scenario and I have got a database with 7 tables and 1 of them has exceeded 20GB (table1) and one another(table6) is 13GB while all other tables are well below 500MB.
In current scenario larger table (table1) is going to get even more data so I have a thought to create another table(table1-1) with same schema and insert all new data in that table. Because mostly there are INSERT calls and very few UPDATE calls are made to table1 and all data inserted are based on ID from table2. (I think its not very important to know though)
All tables are MYISAM and table6 is not going to grow in near future with same pace.
Now I want to ask few questions to gurus and few backup solutions.
First of all.. do you think as I have grown beyond 20GB and creating new table with same schema would help me because mostly there are INSERT calls so it will be light on system to insert in new place compared with inserting in a table which is already grown to 20GB+.
And all these tables are in single database so I want to backup whole database but not 1 big file rather it should be 1 file per table because it will reduce overhead of keeping local copy which now contains table1 all the time. Actually if table1-1 will be used then table1 will only be locally copied only once in a week while others are locally copied daily.
Please suggest me finest way to backup keeping in mind the data size and with minimal (only necessaary)downtime.


texts:
Tempdb is configured with 8 files and we are reducing them to 4.  I read many blogs where SQL Server will allow you to remove excess .ndf's if you run the 4 dbcc drop and free statements, then run the dbcc shrinkfile with the emptyfile clause, then the alter db command with the remove file clause.  When this was attempted, the error

DBCC SHRINKFILE: Page 9:364016 could not be moved because it is a
work table page.
Msg 2555, Level 16, State 1, Line 19 Cannot move all
contents of file "tempdev8" to other places to complete the emptyfile
operation. DBCC execution completed. If DBCC printed error messages,
contact your system administrator.

My question:
Should I attempt to remove the locked temp tables by identifying them, then, re-run the dbcc statements, or, simply run an alter statement, then restart the instance, or restart the instance, then rerun the alter?  I realize the issue that the temp tables are being used by tempdb consistently which is causing the remove file not to work.


texts: Trying to update a table with a customer sales averaging column - Oracle SQL

texts:
Trying to to write a query to add a column to a sales table in order to average sales amounts for individual customers. I have tried quite a bit, ultimately populating the column with a single value over all the rows (not what I need). I have about 75 customers with a total of hundreds of invoices. Coming up blank. Thanks for any advice or help.

ALTER TABLE t1
ADD AVG_AMOUNT NUMBER(9,2);
UPDATE
  SET AVG_AMOUNT =
    (SELECT CAST(AVG(col1)AS FLOAT)
    FROM t1
    GROUP BY col2;

I tried this but get the same value for the whole column.

ALTER TABLE t1
ADD AVG_AMOUNT NUMBER(9,2);
UPDATE t1
SET AVG_AMOUNT =
  (SELECT CAST(AVG(INVOICE_TOTAL)AS FLOAT)
FROM t1
INNER JOIN t2
ON t1.customer_ID=t2.customer_ID
WHERE t1.customer_ID = t2.customer_ID
);


texts: Removing the uses of MySQL temporary tables

texts:
We have a very huge PHP/MySQL application with a lot of legacy code, this application utilized MySQL for most of it's work. It does most of the processing on the database side using temporary tables, or tables that are used temporarily but are not strictly declared as TEMPORARY.

Also this application uses MySQL replication with 3 replicas for each instance, and we use write on those replicas.

In our current setup we use replication filtering to ignore those tables ,We are moving all of our application instances, which is about 15 deployments, to Amazon's RDS, and Amazon RDS does not allow setting "replicat-ignore-tables" param

So now we want to remove the usage of temporary tables, and we are checking the alternatives.

We mainly use them to process joined data from huge tables (billions of rows) and some matrix processing utilizing the use of indexes to process matrix rows.

What would be the best way to replace temporary tables usages using PHP and MySQL?

EDIT
The answer below would solve our first usage, which is direct selects.

Another usage goes as this:

We SELECT a huge amount of data from a huge table into a file using locally located.
We create a TEMPORARY table and load the data in the file into it.
Then we do some calculations and update some rows on that table in every calculation, for example we calculate the height of a building, then we update all rows with that height in the temporary table.
We then run some aggregation calculations on that table, and put the results in a final table.
We drop that table.


texts: Are there any best practices before moving SQL Server to another node (controlled failover)?

texts: Let's say there are multiple jobs running on the SQL Server - we have index maintenance jobs, backups, replication, etc. We would like to perform a switchover using Failover Cluster Manager. Are there any best practices before performing such operation, for example disabling the jobs? Is there anything which could be done to minimize any potential risks?


texts: Which of these database designs is normalized?

texts:
I have two possible database designs.

A) a many-to-many relationship between Branches and FiscalYears via a dedicated join table FiscalYearsBranches

B) two (or more) many-to-many relationships between Branches and FiscalYears via other tables (Documents, Factors, etc.)

See the diagrams below. Which of these designs is better and why?

Design A:  a many-to-many relationship between Branches and FiscalYears via a dedicated join table FiscalYearsBranches

Design B: two (or more) many-to-many relationships between Branches and FiscalYears via other tables (Documents, Factors, etc.)


texts: SQLCMD: "Login failed for user" on SQL Azure, but it works with SSMS

texts:
I have an SQL Azure database, and I'm able to connect to it using SQL Server Management Studio. When connecting to it, it displays

myServer.database.windows.net (SQL Server 12.0.2000.8 - myUser)

However, when I'm using the SQLCMD utility on the same machine, against the same SQL Azure server, with the same credentials, it always answers

Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login failed for user 'myUser'

The syntax I'm using is

sqlcmd -S tcp:myServer.database.windows.net -U myUser@myServer -P "myPassword"

I tried everything I could think of:

Added the port: myServer.database.windows.net,1433
Removed the tcp: protocol
Enclosed the user in double quotes, like the password
Tried the -I and -C command line options
Combinations of the above.

The error is always the same.

Is there some specific flag that has to be activated for SQLCMD to work with SQL Azure? Any other ideas?


texts: How to deal with fragmentation in PostgreSQL?

texts:
What are best practices for dealing with fragmentation in PostgreSQL?

Example:
I have a multi-tenant database whose workload includes on-the-fly aggregation for reports.
CREATE TABLE account (
    id serial PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE widget (
    id bigserial PRIMARY KEY,
    account_id int NOT NULL REFERENCES account (id),
    length numeric NOT NULL,
    weight numeric NOT NULL,
    color text NOT NULL,
    shape text NOT NULL
);

CREATE INDEX ON widget (account_id);

I want to run account-scoped queries on widget, e.g.
SELECT color, count(*), sum(weight) AS weight
FROM widget
WHERE account_id = 42094 AND 3 < length
GROUP BY 1

Suppose there are 5,000 records in account and 100,000,000 records in widget.
Since PostgreSQL lacks clustered indices, an individual account's 20,000 widget records get scattered all over the entire table. With enough records in the table, PostgeSQL will have to read 20,000 pages for a query that under other circumstances would be very fast.

This would seem to be a common sort of situation...how do users deal with this?


texts: Can sp_Blitz, sp_BlitzCache, or sp_BlitzFirst identify what queries caused a major growth operation to occur on a particular log file?

texts:
I'm currently logging the outputs of sp_Blitz, sp_BlitzCache, and sp_BlitzFirst regularly (via SQL jobs).

A particular database's log file grew somewhat exponentially and I believe I can see the grows that occurred from the output from sp_BlitzFirst (via the SizeOnDiskMBgrowth column).

But I was wondering if any of those sp_Blitz... procedures would also be able to tell me the queries that were running that caused the large grows, or at least what queries were running at the time of those grows and then I can do a loose correlation?


texts: Creating a mongoDB backup/dump on remote server via local CMD/Shell

texts:
I hope you can help me.

It is a question that was asked before, but I have tried all solutions, and simply cannot get it right.

I have spend quite some time researching before posting this question. I have looked at the official MongoDB documents and many other blogs.

How can I make a DB backup/dump remotely, as well as restore that dump locally?

This is what I have thus far:

Running this from a new CMD prompt shell:

C:\Users\Admin>mongodump --host 10.13.9.210 -d C:\Program Files\MongoDB\Server\3.0\bin --port 29039 --out /backup/mongodump-2015-11-13

I get this error:

2015-11-13T10:26:41.371+0200    error parsing command line options: expected argument for flag `/o, /out', but got option `/backup/mongodump-2015-11-13'
2015-11-13T10:26:41.371+0200    try 'mongodump --help' for more information

Secondly, how will I be able to restore this backup locally?


texts: How to select a subset of data from a large ER model?

texts: I have a quite large ER model in my database and lots of data. Now I would like to take a subset of this data into a different database for testing purposes. The problem is how to get a subset from all tables so that I don't break referential integrity and get orphanded records from the database? I mean that if I choose 100 records e.g. From the "product" table, how can I easily get all the data from the referenced tables (and from the tables these tables are referenced)? Is there any tool availble for this purpose or just pure SQL?


texts: Restrict access to Edition- or instance-specific databases

texts:
I have the following issue with SQL Server.

On my Windows Sever, SQL Server 2008 Express Edition is running. Now I have installed SQL Server 2008 Web Edition on the same server.

My problem is, when I log in using SQL Server Management Studio of the Web Edition instance, I am able to see, and access, all the databases there, including those created in the Express Edition, which is not what I want.

Is it possible to restrict visibility/accessibility of databases to just those related to the Web Edition when I log in using the Web Edition, and similar for the Express Edition?


texts: Mirroring in SQL Server Standard Edition

texts:
I have setup mirroring of a database in SQL Server Standard Edition.

After this I tried to see if the data has been synchronized or not.

I was not able to check data by taking a database snapshot since database snapshots are not available in SQL Server Standard Edition.

Is there any alternative solutions available to verify whether the data is synchronized or not?


texts: How do you get an average of job history times?

texts:
The following is a script I found online that has most of the elements of what I desire in the result set.  The only thing missing is how I can get an average of the run times over a decided time period, like a week, 10 days, 23 days, a month, etc.

I've done some searching, but what I've tried has failed in some way or another.  Further, my TSQL are weak, but I'm reading publications by Itzik Ben-gan to change this matter.  I'd truly appreciate any help that anyone can provide.

select job_name, run_datetime, run_duration
from
(
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        select DISTINCT
            j.name as job_name,
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
    ) t
) t
order by job_name, run_datetime


texts:
Need the erd of the schema for doctor, patient and appointment database. scenario is given as under:

Clinic Management Entity-Relationship Model

-Doctor has a unique id, name, address, qualification and speciality stored for him. Doctor can have 3 time slots in which he can check patients on each day and specialists are not available daily but twice a week only. however, medical officers are available daily.
-Patient has unique cnic, name, address and has a home phone and mobile number, blood group stored
-Disease data is maintained for more than 300 diseases with disease id, scientific name, common symptoms.
-When patient comes to clinic 5 common diseases out of 300 are noted for each patient in (yes or no) response i.e: bloodpresure, diabetes, uric acid, heart attack, hepatitis.
-Patient can book appointment from many doctors at different time slots. A doctor can give appointment to only 10 patients in a time slot. Appointment is not given for doctor if his time slot reaches full capacity for that particular day.


texts:
Could you tell me the reason why it is mandatory to restore the full backup with NORECOVERY option before restoring the transaction log? otherwise Transaction log is always grayed (Task -> restore -> Transaction log)

Thanks a lot in advanced


texts: How to tell if Azure SQL DW is using a secondary index?

texts:
Using Azure SQL DW, I have created a secondary index on a single column in a table, yet I'm unsure if the index is ever being used by my query. The performance is still slow, but I'm searching about 7 billion rows of data.

My table is essentially:

CREATE TABLE FactBusinessEvent
(
    [EmailAddress] [nvarchar](200) NOT NULL,
    [EventDate] [datetime] NOT NULL,
    [EventDate_key] [int] NOT NULL,
   -- OTHER COLUMNS HERE
)
WITH
(
    DISTRIBUTION = HASH ( [EmailAddress] ),
    CLUSTERED COLUMNSTORE INDEX
);

CREATE INDEX IX_FactBusinessEvent_EmailAddress ON FactBusinessEvent
(
   EmailAddress ASC
);

And my query is:

SELECT * FROM FactBusinessEvent WHERE EmailAddress = 'test@test.com'

Using SSMS 17.6, I can show the estimated query plan and it completely ignores the secondary index, showing a single Get from the table. I can't seem to use hints in SQL DW, so is there anything else to try?

Thanks for any insight.


texts: MySQL MONTH Clause: Want to get Month Name instead of number

texts:
My query is

SELECT MONTH(`hire_date`) AS month FROM `emp_tbl`

I'm getting output in Java fx

if(resultSet.next()){
int month = resultset.getInt('month');           // output is like 11

}

Then I used swtich statement to get month in string. I want output like 'November' instead of 11. I dont want to use switch statment.
Is there is any other solution to get month name as a string instead of a number?

Any other solution besides SWITCH statement?


texts: 1062 Duplicate entry but there are no duplicates?

texts:
I keep getting this error:

  failed to INSERT: [1062] Duplicate entry 'Upping Your Type
  Game-http://jessicahische.is/talkingtype' for key 'PRIMARY'.

The problem is, is that there are no duplicate entries. Which is why I'm confused when receiving that error.

Is there any way to check using InnoDB?

I have tried exporting the table and reading the SQL code and ran a search for any duplicates but there wasn't any.


texts: Is it possible to backup and restore a materialized view with it's data?

texts:
I have some hard to compute materialized views, which I want to be able to backup and restore using the actual stored data, and not by recomputing.

Is it possible in PostgreSQL 9.4?

(the alternative is to create actual tables, but they  are harder to "refresh")


texts: Configure Connection String to Use Target Machine Credentials

texts:
I am trying to develop an SSIS package that will be run on several different machines. I am stuck because I do not understand how to create a connection string that doesn't explicitly point to one machine.
An example connection string is below:
Data Source=myPC;User ID=Admin;Initial Catalog=TestLocal;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;
When I create a new connection in visual studio it asks for the server name...I want the server name to be equal to whatever the server name is where my package is being run. Is this possible?
edit:
The package is being executed as a multiserver sql server agent job. My machine is the master(msx). Should I be looking at making changes to the actual ssis package in visual studio, or focusing on changes in the ssms job? My intuition points me towards editing the package but this is where I have been left scratching my head.
following this guide: https://www.sqlshack.com/parameterizing-database-connection-sql-server-integration-services/ still has the package being called by the user on the target machine, but the package is executed on my machine... headscratcher..
This screenshot below we can see that the job is started on My Machine and is called by Machine2. My thought process here is that now Machine2 should be running this job ON Machine2. You can see that I have included a parameter called ServerName and have set the value to localhost thinking that this would cause the package to be executed on the local machine of the user that called it (in this case Machine2)?..

The result of the run in the image above has the database of Machine1 being updated, when we really want Machine2 to be receiving the update.


texts: Find max sum of daily total within date range


texts:
I'm PIVOTing a table such that the column names in the final table are based on the "organization name" in the source table.  The problem is, the "organization name" is data-driven and could be any set of values; I use dynamic SQL to build up the PIVOT's IN clause.  This means that the value could match another column name I have, like the RecordId I'm pivoting against.  So I have something like this:

.----------------------------------------------------------------------.
| RecordId | OrganizationFoo | OrganizationBar | OrganizationBaz | ... |
|----------|-----------------|-----------------|-----------------|-----|
| 123      | 182             | 76              | 56              | ... |
| 234      | 846             | 0               | 182             | ... |
| 345      | 46              | 2               | 951             | ... |
...

The problem is, this is all data-driven so it would be fine for an organization name to be RecordId, which would cause an invalid SQL query to be generated because of duplicate columns.  Is there a way I can automatically prefix something to every column in the PIVOT output?

If not, the only 3 ways I can think of doing it are:

Select CONCAT('Prefix_', [OrgName]) instead, but this will probably reduce efficiency because [OrgName] is indexed.
Create a duplicate table to the one holding [OrgName], and each time this query is run, clear it and insert the contents of the original table but with [OrgName] having the prefix instead.
Just always store [OrgName] with the prefix in the first place.

Any cleaner/better options?


texts:
My problem is having issue with SQL Server 2014 Database and its size growing and consuming Hard disk is almost full, Only 50GB space is left in 1TB Hard drive, and SQL database log (.ldf) file is consuming 800GB space and continue increasing, We can Shrink the database-log but there are several blogs which says it effects the server performance and data loss, or we can increase the space of the hard disk, Please advise.

Why Does the Transaction Log Keep Growing or Run Out of Space? does not mention the main performance problem of Virtual Log Files.

Any Help or suggestions much appreciated.


texts:
select  to_tsvector('english', 'Ice-cream') @@ to_tsquery('english', 'Ice<->cream');

is True. While:
select  to_tsvector('english', 'iDream  Ice-cream  iScream') @@ to_tsquery('english', 'iDream<->Ice<->cream<->iScream');

is False.
For my understand. Both add same word before and after the match one. It will keep the same answer.


texts: Getting "There is already an object named 'Mytable' in the database" error

texts:
I keep getting the following message:

  Msg 2714, Level 16, State 6, Procedure tr_AuditTableChange, Line 9
  There is already an object named 'Mytable' in the database.

Not sure what I am doing wrong and I can't figure out what this is.

Can somebody please help?


texts: (x IS NOT NULL) vs (NOT x IS NULL) in PostgreSQL

texts:
Why is x IS NOT NULL not equal to NOT x IS NULL?

This code:

CREATE TABLE bug_test (
    id int,
    name text
);

INSERT INTO bug_test
VALUES (1, NULL);

DO $$
DECLARE
    v_bug_test bug_test;
BEGIN
    RAISE NOTICE '%: %', v_bug_test, (v_bug_test IS NULL);
    RAISE NOTICE '%: %', v_bug_test, (v_bug_test IS NOT NULL);
    RAISE NOTICE '%: %', v_bug_test, (NOT v_bug_test IS NULL);

    SELECT *
    INTO v_bug_test
    FROM bug_test
    WHERE id = 1;

    RAISE NOTICE '%: %', v_bug_test, (v_bug_test IS NULL);
    RAISE NOTICE '%: %', v_bug_test, (v_bug_test IS NOT NULL);
    RAISE NOTICE '%: %', v_bug_test, (NOT v_bug_test IS NULL);
END
$$;

DROP TABLE bug_test;

gives following output:

(,): t
(,): f
(,): f
(1,): f
(1,): f ???
(1,): t

while I would expect to get this output:

(,): t
(,): f
(,): f
(1,): f
(1,): t <<<
(1,): t


texts: Using the same sequence twice in a PL/SQL statement

texts:
I wrote the snippet of PL/SQL today:

declare
  first_id number;
  second_id number;
begin
  insert into table (sort_nr, text_id, unit_id) values(...,
  table_seq.nextval, table_seq.nextval) returning text_id, unit_id
  into first_id, second_id;
  dbms_output.put_line(first_id);
  dbms_output.put_line(second_id);
end;

and received a unique constaint violation. After futher examination, I discovered that first_id and second_id had the same value in it.

My question is: Is there any restriction calling the same sequence multiple times in the same statement and receiving a subsequent number? From my point of view it seems like the nextval is invoked only once in the scope of the query and cached.

Just a side note, I cannot change that crappy schema to avoid using the same sequence in two columns.


texts: MySQL Installation error: Unable to update security settings

texts:
I tried to configure MySQL server 5.5 on Windows and this is what I got:

mysql-server-5.5-winx64:56 - Attempting to update security settings.
mysql-server-5.5-winx64:100 - Unable to update security settings. Unable to connect to any of the specified MySQL hosts.
mysql-server-5.5-winx64:62 - Attempting to verify command-line client shortcut.

Any thoughts? I found this article on it, but I'm not really sure what to do. I tried getting random processes to work but it didn't seem to change much.


texts: how to convert a date string like this MM/YYYY to 01-MM-YY

texts:
I have a column and its type is string and stores date something like this 03/1996
I want to convert that to 01-MAR-96?

how can I achieve that in ORACLE


texts: How do we find who used Dedicated Admin Connection

texts:
Is there any way to find who used Dedicated Admin Connection?

Not active connection but the previous one which is already closed?


texts: Does SSAS log when connections are refused due to hard memory limit or total memory limit is reached?

texts:
I have recently set memory limits for SSAS for SSAS 2017 Tabular In-memory. I'd like to know if those limits are being reached. Obviously I took the time to set the limits based on actual usage, not just random, so they may not have been triggered yet. I could set them artificially low but that seems a little dangerous as I am not sure what to look for.
Does SSAS log info to the Windows Event Logs, or the flight recorder, or elsewhere when the memory clean up begins or connections are refused?
I'd like to be alerted about aggresive memory clear up, before getting errors from customers with connection refusals.
There's lots of great content about the limits and how to set them but I haven't found any info on what gets logged when they are reached.


texts: Entity-relationship diagrams and application program functionalities

texts:
It seems that the core functionalities of an application program I'm working on are nothing but associative entities. Hence one-to-many relations sort of produce "metadata" that will only feed (one way or the other) the associative entities for our application functionalities.

Now we have an entity-relationship diagram (ERD) that has lots of one-to-many (more that 10 tables) and just one associative entity. What does it say about that model or the the application?

Is it improvable, i.e., can the application circumvent more functionalities if the ERD is improved to add more associative entities?

Does having very few associative entities means that the application will not be rich in functionalities?

Additional considerations

What I'm wondering is: if the project scope statements lead to an ERD with only one many-to-many relation and a dozen of one-to-many, then would that mean that the project does not solve many problems (functionalities) except only digitizing lots of data?

I think that with less many-to-many, they will just mirror at first (unless we create join queries for other purposes...).

Or simply put: does a high number of many-to-many associations means that the software will be richer in functionalities than with less many-to-many (do not include join queries in the thoughts for this one)?


texts: Postgresql 9.6, create a user with only select permissions for a given database/schema

texts:
I want to create a user that has:

has its default privileges revoked when I create it
can only perform selects against a given database/schema

Is this possible?


texts: Crow's Foot notation and identifying -vs- non-identifying relationships

texts:
Is it possible to distinguish between identifying and non-identifying relationships using the Crow's Foot notation?

Case in point: In table Book, part of the book's Primary Key is a FK to the Author table. In other words I embed the author's identifier in the book's identifier. Also, a book has a FK to a Book_Type (containing values like "hardcover", "papercover") (this is just an example, I understand that I would probably need a "book edition" table for that).

The thing is that this latter FK relationship isn't nearly really as important as the former one (the FK to Book_Type is not part of Book's Primary Key), yet my understanding is that in the Crow's Foot diagram shown below, both these relationships are rendered identically:

Have I misunderstood something or is there a way to signify the identifying versus non-identifying relationship distinction using the Crow's Foot notation?


texts:
System

PostgreSQL 11.5 (Ubuntu 11.5-0ubuntu0.19.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 8.3.0-6ubuntu1) 8.3.0, 64-bit

Case

Does select work in case condition? I need return 1 state from 4 states in case. I try to explain.

Shop has eshop (store_id = 7) and stores (store_id = 1 - 10, others, but not 7).

Eshop and every store have available_count - in stock.

Logic is:

When available_count > 1 where store_id = 7 (eshop) then in stock.

When available_count = 0 where store_id = 7 (eshop) and available_count > 1 where store_id != 7 (at least one value is greater than 1 for all stores, for eshop is 0) then in stock on store.

When available_count <= 1 where store_id = 7 (eshop) or available_count <= 1 where store_id != 7 (at least one value is 1, others are 0, but if all are 0, then not in stock) then on request.

When count_availability = 0 where store_id = 7 (eshop) and count_availability = 0 where store_id != 7 (all values are 0) then not in stock.

I tried

case
when (select ja.available_count where ja.store_id = '7') > 1 then 'IN STOCK'
when (select ja.available_count where ja.store_id = '7') = 0 and (select ja.available_count where ja.store_id != '7') > 1 then 'IN STOCK ON STORE'
when (select ja.available_count where ja.store_id = '7') <= 1 or (select ja.available_count where ja.store_id != '7') <= 1 then 'ON REQUEST'
when (select ja.available_count where ja.store_id = '7') = 0 and (select ja.available_count where ja.store_id != '7') = 0 then 'NOT IN STOCK'
end as "availability",

It returns more than one value for availability, just 'IN STOCK' or '[NULL]' and sometimes seems there is problem with right value.

Is betterr way to do that?

Thank you for any help.


texts: Count in an other table and show only theese records in query which has more than they used in other table

texts:
I have for example 2 tables:

used(us_id, code_id);
codes(code_id, amount)

I want something like this:

If for example I have 5 eggs, and egg code is 1, then if the used table have reached 5 of ID 1 then in a from, the egg won't show up as choseable. (listbox, which get it's source from codes table)

I'm using Mysql server and Access connected by odbc.


texts: Influx:Show field keys based on tag values (or the other way)

texts:
I have an influx database with one tag key (called machine) and multiple field key (called cpu and memory). I want to be able to find all unique tag values for each field key or filled field keys for a given tag value.
Here is an example of database:
SELECT * FROM "myMeasurement"

name: myMeasurement
-------------------
time                   machine     cpu     memory
2020-01-01T00:00:00Z   m1          51
2020-01-01T01:00:00Z   m2                  2048
2020-01-01T00:00:00Z   m1          52
2020-01-01T01:00:00Z   m2                  2054

I would like to retrieve m1 when requesting machine containing cpu values and/or cpu when requesting not empty field keys for machine m1.
Thanks


texts:
I manage a Percona XtraDB 3-node cluster. Recently the MySQL datadir of one of the nodes (not the one from which the cluster has been bootstrapped) started being filled up with gcache.page.00nnnn files. This is not happening in the other two nodes.

What causes the creation of such a large number of Galera cache files?

I stopped the daemon, wiped out the datadir and forced a SST on the node, but that's not really a solution -- I want to find the cause for this behaviour.


texts: Oracle Application Express Listener PDF Report

texts:
We need our reports to be available in PDF format on our Oracle 11g, APEX:4.2.6

Followed this tutorial and selected "Oracle APEX Listener".

But the generated PDF report is empty.

We are using EPG.

Do we have to install/configure anything else other than the mentioned tutorial.


texts: Configure MSLocalDB in a .NET config file

texts:
I have created a .NET Standard C# project and a SQL database in my LocalDB server (LocalDb)\MSSQLLocalDB.
I have tried to configure the connection string in the project web configuration file but in runtime the connection to SQL server is not succeeded.
How can I configure a local DB server in my project config file?

This is the connection string configured in my project web.config:

<connectionStrings>
    <add name="iPark.Database.Properties.Settings.iParkConnectionString"
         connectionString="Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=iPark;User ID=iParkUser;Password=iParkPassword1"
         providerName="System.Data.SqlClient" />
</connectionStrings>

When I try to access the database, the following error occurs:

Relative to the database, I've created the user iParkUser with authentication type "SQL Server Authentication". I can login to the server and the database with the user and its roles are the following:

db_owner
db_reader
db_writer


texts: DB schema for storing the process tree of linux

texts:
I search a db schema to store the process tree of linux every half hour.

Platform: PostgreSQL on Linux.

I am unsure where to split all the data into columns or to use a json column.

Which one is better, and why?


texts: Single slave - multiple master MySQL replication

texts: I need to replicate different MySQL databases from multiple servers into a single slave server. How can this be done? is there a way to define multiple master hosts?


texts: How to report stats on restore via sqlcmd?

texts:
I've inherited a batch file script that calls sqlcmd with parameters. One of the parameters is the path to a SQL file. That file includes a database restore command. The command in the SQL file includes the parameter stats = 1 but nothing is reported in the commandline window during a restore.

Is it possible to use sqlcmd this way to initiate a database restore and have the stats report back and displayed on the commandline as the restore executes? If not, then can you point me to alternative approaches?


texts: Query/Table Hints for the History Table when Using the FOR SYSTEM_TIME Clause

texts:
Probably a pretty quick question, but is there any way to specify Query/Table hints for the History Table when using the FOR SYSTEM_TIME statement when querying temporal tables?  I suspect not, but I wanted to double-check before I throw in the towel on this one.

Here's a dbfiddle that shows a basic breakdown of different ways I'm aware of specifying hints for a query, and the only way I can figure out how to pass query and/or table hints that interact with a history table is to convert the query to a UNION ALL between the live and history tables rather than use the FOR SYSTEM_TIME clause.

When trying to specify a history table hint when using the FOR SYSTEM_TIME clause, I get the following error:

Msg 308 Level 16 State 1 Line X
Index '<<HISTORY TABLE INDEX NAME>>' on table '<<LIVE TABLE NAME>>' (specified in the FROM clause) does not exist.

When trying to specify a query hint pointing to the history table when using the FOR SYSTEM_TIME clause, I get the following error:

Msg 8723 Level 16 State 1 Line X
Cannot execute query. Object '<<HISTORY TABLE>>' is specified in the TABLE HINT clause, but is not used in the query or does not match the alias specified in the query. Table references in the TABLE HINT clause must match the WITH clause.

These errors both make sense on the surface as the history table is not specifically referenced in the query, but is there another query hint, trace flag, etc. that I can use so that I can still use the FOR SYSTEM_TIME clause and specify hints against the history table?


texts: DISTINCT COUNT with GROUP BY query is too slow despite indexes

texts:
I have the following query that counts the number of vessels in each zone for each week:

SELECT zone, DATE_FORMAT(creation_date, '%Y%u') AS date, COUNT(DISTINCT vessel_imo) AS vessel_count FROM vessel_position
WHERE zone IS NOT NULL
AND creation_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY zone, date;

The table has about 40 million rows. The execution plan for this is:

+----+-------------+-----------------+------------+-------+--------------------+------+---------+------+----------+----------+------------------------------------------+
| id | select_type | table           | partitions | type  | possible_keys      | key  | key_len | ref  | rows     | filtered | Extra                                    |
+----+-------------+-----------------+------------+-------+--------------------+------+---------+------+----------+----------+------------------------------------------+
|  1 | SIMPLE      | vessel_position | NULL       | range | creation_date,zone | zone | 5       | NULL | 21190904 |    50.00 | Using where; Using index; Using filesort |
+----+-------------+-----------------+------------+-------+--------------------+------+---------+------+----------+----------+------------------------------------------+

Columns vessel_imo, zone and creation_date each indexed. Primary key is the composite key (vessel_imo, creation_date).

When I look at the query profile, I can see that a large amount of time i spent doing Creating sort index.

Is there anything I can do to improve this query further?