texts: Verify if user has replication permissions in Postgres

texts:
I am working on streaming changes in my Postgres database using Debezium using the Postgres connector offered by Postgres. However, for the connector to work, the user must have REPLICATION and LOGIN rights. Although, the problem seems to be as simple as checking permissions in pg_roles, it is not.

For example, I have a user with following permissions :

  rolname   | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig |  oid
------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
 test_user  | f        | t          | f             | f           | t           | f              |           -1 | ********    |               | f            |           | 36123

The test_user has rolreplication as false even then I am able to create a replication slot using the user. Can anyone show some light about what exactly is the right method to get all privileges available to the user?

I also tried looking into the groups of which test_user is a member but those groups also do not have the rolreplication as true. Here we are talking about logical_replication and not physical_replication. Am I missing something or is there any other table or query which I should use to get this?

EDIT :

Running select * from pg_replication_slots gives:

slot_name  |  plugin  | slot_type | datoid |   database    | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn  | confirmed_flush_lsn
-----------------------------------------------------------+----------+-----------+--------+---------------+-----------+--------+------------+------+--------------+--------------+---------------------
test_slot  | wal2json | logical   |  60811 | test_database | f         | t      |      21642 |      |      5955602 | 1FD/D00BA2E0 | 1FD/D107C598


texts:
I am having mysql 8.0 installation with replication between 2 servers in master-master configuration. While data is being replicated from server A to server B the replication is stalled from hours. Below is the snippet for show slave status \G on server B. I am not sure on what waiting for handler commit means and whether its the cause of my Replication stalling

        Master_SSL_Cipher:
       Master_SSL_Key:
Seconds_Behind_Master: 49808
        Last_IO_Errno: 0
        Last_IO_Error:
       Last_SQL_Errno: 0
       Last_SQL_Error:Replicate_Ignore_Server_Ids:
     Master_Server_Id: 1
     Master_Info_File: mysql.slave_master_info
            SQL_Delay: 0
  SQL_Remaining_Delay: NULL
  Slave_SQL_Running_State: waiting for handler commit
       Master_Retry_Count: 86400
              Master_Bind:
  Last_IO_Error_Timestamp:
 Last_SQL_Error_Timestamp:
           Master_SSL_Crl:

After executing show processlist; i am getting a process that is waiting from a long time , having a wait time of 50071 and increasing

system user     |                               | NULL | Query       | 50071 | waiting for handler commit

What steps shall i take to bring my replication on track?


texts: TempDB data files don't shrink well on SQL 2008

texts:
So ideally you want to pre-size your TempDB data and log files appropriately so that this isn't a concern, but sometimes a rogue developer runs a crazy huge query on a production server during work hours, causing the TempDB data files to blow up huge.

If TempDB was literally the only thing on the drive, then I could probably just leave it like that, but on some servers I have several SQL instances that all share the same TempDB drive.

So how can I shrink these TempDB data files without restarting the instance?

I normally try:

DBCC SHRINKFILE (name = 'tempdev', size = 5000)

This worked fairly consistently in SQL 2000 and 2005 (assuming the actual tempdb activity had tapered off), but seems to not work very often in 2008 (on my current server, it only worked on 1 of 4 data files, the others continue to remain 2-3x larger).


texts: Does view take advantage of table indexes

texts:
I have a table with a clustered index and 2 non clustered index. Now, in my view I am selecting everything from my table

create view dbo.MyView
as
Select * from MyTable

Now, when I am using this view in my queries may I assume that this view will take advantage of the indexes created on the table. (i.e. work in the same way as Select * from MyTable should work)?
or should I have to use Indexed views

Addition to the question

If view can take advantage of the table index, then using SELECT * FROM MyView Where SomeColumn = @someValue may impact the performance badly (if view has large amount of data , so it will behave like a heap when not indexed).
How to overcome this issue ? other than using NOEXPAND (because I am using Enterprise edition)


texts: Calendar Event table - best practice setup for range queries and individual retrieval

texts:
This seems like a generic problem that should have been solved already, but I can't find anything about this. In general this question is - given a table where data is read by a date range, what is the best, most efficient setup?
We have a calendar event table that will quickly grow to millions of records.
The schema is something like:
CREATE TABLE [dbo].[CalendarEvent](
[Id] [uniqueidentifier] NOT NULL,
[DtStart] [datetime] NULL,
[DtEnd] [datetime] NULL,
[Created] [datetime] NULL,
[LastModified] [datetime] NULL,
[CalendarEventType] [nvarchar](255) NULL,
[CalendarId] [uniqueidentifier] NULL
PRIMARY KEY CLUSTERED
(
    [Id] ASC
    )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Forget about recurring events, etc. as that doesn't bear on our problem.
Most queries will be of the type:
select * from CalendarEvent where CalendarId = 'b5d6338f-805f-4717-9c0a-4600f95ac515' AND dtStart > '01/01/2020' AND dtStart < '10/22/2020'
Notice no joins, etc.
But we will also have some that select for individual events, and include joins:
select * from CalendarEvent ce join tags t on ce.Id = t.CalendarEventId where Id = '17606330-5486-496a-a91c-f5d0e123bfff'
Questions and ideas:

Should we keep the Id as the PK, but make the start date the clustered index?
Should we just make an index on dtStart?
Should we partition by month?
Should we denormalize a little and break duplicate the dtStart data by include year and month columns that we can index and use in our range queries?

In general, when you do your querying on a table by date range, what is the best setup for this type of table?
Note: If you think this question could be improved to help more people, make it more generic and widely applicable, such as removing references to a Calendar Event table specifically, and making this just about date range querying in any type of table, please help me do that.


texts: Oracle OLEDB Connections hang in SQL Server: Can I restart Instance?

texts:
There is a killed/rolled back query on this instance that was originally attempting to execute a SELECT on a remote Oracle Server via OLEDB. This query was running for over two days before it finally got cancelled.

SPID 69: transaction rollback in progress.
Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

Using cports I have found all open connections to the 1521 port on the remote server. In cports you can close the connection.

After trying to close the connection, the killed/rollback process is still preventing other processes from taking a schema lock on one of the tables.

How can I determine if the database, upon restarting, won't go into RECOVERY PENDING?

Could I kill the processes using Task Manager, without also killing SQL Server?

EDIT

Here is the query anonymized:

   INSERT  INTO sometable
            ( field1 ,
              field2 ,
              field3 ,
              ...
            )
            SELECT  some_fields
            FROM    someothertable f ( NOLOCK )
                    INNER JOIN OPENQUERY([MYORACLE_LINKED_SERVER],


texts:
Questions:

Is there a way, using SQL, to identify how many views are in the database per user?
How can you authorize/deny users access to delete views created by another user?


texts: SQL Server - Automatically full backup for new databases which are auto-created with EntityFramework-Migrations

texts:
I have an ASP.NET MVC Application with a Multi-Tenant architecture (using SQL-Server). I'm using EntityFramework DB-Migrations which automatically generates a database per customer.
In SQL-Server I have set up 3 maintenance plans: full, diff and transaction backups. The full-backup happens only once a day.
Now if a new customer-database is auto-generated, the next transaction/differential-backups will fail because there isn't any full-backup available.
I have found following thread with a similar problem: full-backup-on-database-creation
But in this case a full-backup is created for all databases - which could lead to a longer run. I just want to do a full-backup for the new database (which is empty and should be finished within seconds).
I have tried this by extracting the code from the maintenance plan:
ON ALL SERVER
FOR CREATE_DATABASE
AS

SET NOCOUNT ON;
    DECLARE @EventData XML = EVENTDATA();
    DECLARE @db sysname = @EventData.value(N'(/EVENT_INSTANCE/DatabaseName)[1]', N'sysname');
    DECLARE @SubDir nvarchar(1024) =  N'PATH TO BACKUP-LOCATION' + @db;
    DECLARE @BackupName nvarchar(1024) = @db + N'_backup_initial';
    DECLARE @BakFile nvarchar(1024) = @SubDir + N'\' + @BackupName + N'.bak';
    DECLARE @ErrMessage nvarchar(1024) = N'Verify failed. Backup information for database ' + @db + ' not found';
    declare @backupSetId as int

    EXECUTE master.dbo.xp_create_subdir @SubDir

    exec('BACKUP DATABASE ' + @db + ' TO  DISK = ''' + @BakFile + ''' WITH NOFORMAT, NOINIT,  NAME = ''' + @BackupName + ''', SKIP, REWIND, NOUNLOAD, COMPRESSION, ENCRYPTION(ALGORITHM = AES_256, SERVER CERTIFICATE = [BackupCert]),  STATS = 10, CHECKSUM, CONTINUE_AFTER_ERROR')

    select @backupSetId = position from msdb..backupset where database_name=@db and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@db )
    if @backupSetId is null begin raiserror(@ErrMessage, 16, 1) end
    --RESTORE VERIFYONLY FROM  DISK = @BakFile WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

GO

Executing the backup-script manually is working, but within the trigger it fails with an error message:

cannot perform a backup or restore operation within a transaction

Does anyone know how to get this working?


texts:
I have a MS-SQL 2014 SP1 - two node AlwaysOn AG (One Primary and an read-only secondary)  with about 10 databases sizing 20-30GB in total. I have some queries regarding scheduling index rebuild and stats gathering.

I have planned to schedule it on weekly basis through maintenance plans. Usually on sunday nights where there will be no load at all on the primary.

Should I run the job in primary or in replica? If I run it only on secondary then what benefits do I get? or vice versa? (Meaning should I run on both the nodes)

Is corruption likely to happen if I use SP1 and index rebuild via maintenance plans?
As mentioned here


texts: Select sales from different stores and group by product

texts:
I have three tables (SalesTable, StoresTable and ProductsTable) and the structure is the following:

--StoresTable
StoreId Name
1       Store 1
2       Store 2

--ProductsTable
Id    Description
1     Product 1
2     Product 1

--SalesTable
Store    Product    Qty
Store 1  Product 1  20
Store 1  Product 1  30
Store 1  Product 2  10
Store 2  Product 1  30
Store 2  Product 2  10
Store 1  Product 2  5

What I want is to display a report to show the sum of qty sales from each product and group by store. Something like this:

--Expected Result according above table data
Store     Product     Sales
Store 1   Product 1   50
Store 1   Product 2   15
Store 2   Product 1   30
Store 2   Product 2   10

At the moment I have the following query:

SELECT ST.Name, sum(S.Qty) as Sales, P.Description
FROM SalesTable S
INNER JOIN StoresTable ST ON S.Store = ST.Name
INNER JOIN ProductsTable P ON S.Product= ST.Description
group by ST.Name, Sales, P.Description

This query returns something like this (according with my real data info):

Store    Sales  PRODUCT
STORE 1  12     PRODUCT 1
STORE 1  3      PRODUCT 1
STORE 1  24     PRODUCT 2
STORE 1  48     PRODUCT 2
STORE 1  132    PRODUCT 2

As you can see the problem is that is duplicating products, I think that maybe is a problem with my joins. What I'm doing wrong?


texts: Tool for identifying stored procs with deprecated syntax?

texts: I have to move a DB running in compatibility mode 80 (SQL Server 2000) on a SQL Server 2005 to a SQL Server 2012. How can I scan it for stored procedures that might break due to deprecated or discontinued syntax? I realize that external (VB or Java) programs might also need some changes. I want to


texts: How does a subquery use the main query columns?

texts:
I'm really confused by this SQL here, it's used in our app but I have no idea how it works. I put it in from examples I found online and it seems to have no issues.

What it's doing is grabbing the latest timestamp of a set. We couldn't use ORDER BY as there had to be multiple groupings. The actual query is much larger and this is just a cut down version showing the fundamental selection.

select * from table1 as main
WHERE NOT (EXISTS (select * from table1 sub
WHERE sub.datetime > main.datetime));

(Live online version: http://sqlfiddle.com/#!17/290cc/2)

What I think should be happening in my mind:

Two identical tables selected, the constraint in the subquery is saying:

  Only select the rows from the subquery which have a date greater than any other row in the main query.

In this case:

Subquery id:1 has only a smaller than or equal to date value from all rows in the main query so it doesn't get selected.
Subquery id:2 has a date which is larger than id:1 from the main query selection, so id:2 gets selected once. It is not larger than anything else.
Subquery id:3 has a date which is larger than both id:1 and id:2 from the main query selection, so it gets selected twice.

So the return from the subquery should be (2, 3, 3). So the main query should do a selection which not exists in that set, which should return id:1, but it returns id:3.

Where is my misunderstanding?


texts: Looking to find a way to determine replicated objects from subscriber

texts:
I am trying to create a process to script out items that have been added to subscriber databases in transactional or snapshot replication - sometimes we find users have added a view or proc to a database for example.

I know that from the publisher end you can check to see if an object "is Published" but is there something comparable from the Subscriber end - something like an "is subscribed"?

If we have to script out every single object we can, but it would be much less time consuming if I could filter out all of the objects from the publication.

Poking around I don't see such a thing


texts: Audit succesfull logons , loging every 2 seconds

texts:
I created an audit to audit only succesfull logons. But the problem is, I did it in a test environment and it's loging a connection every second

Did I do something wrong ? I only selected SUCCESFULL LOGIN GROUP in the specification.
also is there a way to know how many audits for each file created?
and, is there a way to reduce the ammount  of fields being audited?
EDIT: damm 2 weeks and I didnt find an answer for this.


texts: Why intended integrity constraint cannot be enforced?

texts:
What does the following integrity constraint try to enforce? (integrity constraint is primary key and check in this case but I couldn't understand what I should really write).

Explain why the intended integrity constraint cannot be enforced?

CREATE TABLE Sailors
( sid INTEGER,
  sname CHAR(10),
  rating INTEGER,
  age REAL,
  PRIMARY KEY (sid),
  CHECK
    ( (SELECT COUNT (S.sid) FROM Sailors S)
    + (SELECT COUNT (B.bid) FROM Boats B) < 100 )
) ;

We are studying SQL at school.

Sabancı bilgisayara selamlar @yguney


texts: How to create Buffer Pool in Database dedicated only for ONE BIG table?

texts:
I have table TICKET with 400K records in database (DB2).

I wish to create one huge buffer pool which will be dedicated only to this one big table for faster response. What are the steps to do it?

Also at the moment I have one Buffer Pool which coovers whole Table space with all the tables (about 200) in database! what will happen then with that my specific table in that old firstly created buffer pool? should that table stay in first buffer pool or how to remove from that buffer pool?? Also are there some risks for this action???

Thank you


texts:
I have a rather complex query in mysql 5.1.73-community. It involves joining tables and view at the same time.

Here is the query:

VIEW v_summary AS (
select (--some 35 columns fro tabel A B C D F)
from (((((ht_counters_sales_items_b a
       left join ht_counters_sales_b b
         on ((a.tcsi_tcs_id = b.tcs_id)))
      left join ht_counters_trans_b c
        on ((b.tcs_no = c.tct_ref_no)))
     left join **VIEW_counters_activities** d
       on ((c.tct_tca_id = d.tca_id)))
    left join m_items e
      on ((e.mi_id = a.tcsi_mi_id)))
   left join m_items_categories f
     on ((f.mic_id = e.mi_mic_id)))
where ((c.tct_voided = '0')
       and (a.tcsi_voided = '0'))
group by a.tcsi_id)$$

Table A : index (tcsi_id) PRIMARY

Table B : index (tcs_id) PRIMARY

Table C : index (tct_id) PRIMARY

Table E : index (mi_id) PRIMARY, (mi_code,mi_id),(mi_name,mi_id),
(mi_mic_id,mi_id),(mi_mu_id,mi_id),(mi_mu_id,mi_mic_id,mi_id),(mi_id,mi_mu_id,mi_mic_id)

Tabel F : index (mic_id) PRIMARY, (mic_mu_id),(mic_type),(mic_lft,mic_rgt,miclvl),(mic_name,mic_id)

Another view that is involved in the above view is as follows :

VIEW **VIEW_counters_activities** AS(SELECT
  SUM((CASE g.tct_ref_journal WHEN 'CREDIT' THEN (g.tct_ref_value - g.tct_ref_voided) WHEN 'DEBIT' THEN ((0 - g.tct_ref_value) + g.tct_ref_voided) ELSE 0 END)) AS tca_close_cash,
  a.tca_id            AS tca_id,
  a.tca_open_date     AS tca_date,
  a.tca_open_date     AS tca_open_date,
  a.tca_open_date     AS tca_open_time,
  a.tca_close_date    AS tca_close_date,
  a.tca_close_date    AS tca_close_time,
  a.tca_mc_id         AS tca_mc_id,
  a.tca_mst_id        AS tca_mst_id,
  a.tca_state         AS tca_state,
  a.tca_user_id       AS tca_user_id,
  a.tca_open_cash     AS tca_open_cash,
  a.tca_mutation_cash AS tca_mutation_cash,
  a.tca_controller    AS tca_controller,
  a.tca_coordinator   AS tca_coordinator,
  b.mhr_scd_name      AS tca_mhr_scd_name,
  c.fullname          AS tca_user_name,
  d.fullname          AS tca_controller_name,
  e.fullname          AS tca_coordinator_name,
  f.mc_name           AS tca_mc_name,
  f.mc_ip_address     AS tca_mc_ip_address,
  f.mc_mcp_type       AS tca_mc_mcp_type,
  f.mc_mcp_name       AS tca_mc_mcp_name,
  f.mc_ml_id          AS tca_mc_ml_id,
  f.mc_ml_type        AS tca_mc_ml_type,
  f.mc_ml_name        AS tca_mc_ml_name,
  h.mpr_name          AS tca_receipt_printer_name,
  h.mpr_ip_address    AS tca_receipt_printer_address,
  i.mpr_name          AS tca_report_printer_name,
  i.mpr_ip_address    AS tca_report_printer_address,
  j.mst_name          AS tca_mst_name
FROM (((((((((ht_counters_activities_b a
           LEFT JOIN m_hr_schedules b
             ON ((a.tca_mhr_scd_id = b.mhr_scd_id)))
          LEFT JOIN v_users c
            ON ((a.tca_user_id = c.id)))
         LEFT JOIN v_users d
           ON ((a.tca_controller = d.id)))
        LEFT JOIN v_users e
          ON ((a.tca_coordinator = e.id)))
       LEFT JOIN v_counters f
         ON ((a.tca_mc_id = f.mc_id)))
      LEFT JOIN ht_counters_trans_b g
        ON (((a.tca_id = g.tct_tca_id)
             AND (g.tct_ref_mpm_type = 'CASH')
             AND (g.tct_voided = 0))))
     LEFT JOIN m_printers h
       ON ((f.mc_receipt_printer = h.mpr_id)))
    LEFT JOIN m_printers i
      ON ((f.mc_report_printer = i.mpr_id)))
   LEFT JOIN m_sales_types j
     ON ((a.tca_mst_id = j.mst_id)))
GROUP BY a.tca_id))

As i am writing this, the query SELECT * FROM v_summary WHERE MONTH(trx_date) = 7 has run for 70 minutes and still counting.

How can i make it faster ?

All of the tables are INNODB.

MySQL settings:

bulk_insert_buffer_size 8388608
innodb_buffer_pool_size 2147483648
innodb_log_buffer_size  16777216
join_buffer_size    131072
key_buffer_size 57671680
myisam_sort_buffer_size 130023424
net_buffer_length   16384
preload_buffer_size 32768
read_buffer_size    65536
read_rnd_buffer_size    262144
sort_buffer_size    262144
sql_buffer_result   OFF
max_heap_table_size 64M
tmp_table_size 124M
SET PROFILING = ON


texts: Oracle 11.2.0.4.0 32 bits full client installation

texts:
I need to install an Oracle Client on a Windows 2008 Server R2.
The client must be 32 bits since the application that it is going to use it only works under 32 bits.

I´m getting crazy knowing what specific packages I need to download and install. I can´t find what components I have to install to simply install the client and the ODBC drivers. I cannot believe that Oracle is so bad at this. Why cannot Oracle have a simple installer where you install everithing you need?, instead of having to donwload multiple packages, merging them and running console installers.

I´ve donwloaded and installed:

ODAC112040Xcopy_32bit
instantclient-basic-nt-11.2.0.4.0
instantclient-odbc-nt-11.2.0.4.0

And I still cannot get the freaking ODBC driver to appear on the list of availables ODBC drivers on ODBC connections.

Can someone please tell me or point me out what do I have to install to accomplish this?.

Thanks.


texts: Search all columns in list of tables for particular string

texts:
I have a list of market data tables in one of our environments and need to search every column in the list for a certain string. Can anyone point me in the right direction.

1> select @@version
2> go

  Adaptive Server Enterprise/15.0.3/EBF 16558 ESD#1/P/Solaris AMD64/OS 5.10/

Thanks


texts: Best way to store different types of data in a single column, which should be query friendly - MySql

texts:
I'm working on a project in which user can create their own web services from UI by simply selecting number of fields (Max 200) and defining datatype for each field.
I'm considering following table structure for the same :

ServiceMaster
+-----------+-------------+-------------+
| ServiceId | ServiceName | FieldsCount |
+-----------+-------------+-------------+
| 1         | Service1    | 2           |
+-----------+-------------+-------------+
| 2         | Service3    | 3           |
+-----------+-------------+-------------+

ServiceDetails
+------------------+-----------+--------+---------+-------+-------------+
| ServiceDetailsId | ServiceId | Field1 | Field2  | Field3| Field...200 |
+------------------+-----------+--------+---------+-------+-------------+
| 1                | 1         | 5      | Active  | NULL  | NULL ...    |
+------------------+-----------+--------+---------+-------+-------------+
| 2                | 2         | High   | 9.0     | 7     | NULL ...    |
+------------------+-----------+--------+---------+-------+-------------+
| 3                | 1         | 2      | Running | 7     | NULL ...    |
+------------------+-----------+--------+---------+-------+-------------+

I'm not adding DataTypeDetails table here for clean and simple code, but yes I have one table to hold datatype for fields.
If you see above table structure, I can define Varchar or any string datatype for all Fields, but if I do that then while applying filters on table I have to CAST fields data with it's original datatype. Like, 9.0 (Float) or 7 (Int) etc.
I think this will slowdown query if I will use TYPECASTING for all columns to apply filters. This able may have Billions of records or more.
So is there any alternate way to achieve the same with better performance.


texts: How to restore a database from SQL Server 2000 to SQL Server 2012

texts:
I've got a databe in SQL Server 2000 and older than it! And it's very old version. I can't restore to SQL Server 2012. Is that because of old version? How can I restore it? Is there any script or something like that?

Thanks for your advice.


texts: Data is not getting replicated from primary to standby in SAP ASE-ASE replication

texts:
2 SAP ASE servers are configured to replicate using SAP RS on a testdb with logical connection as a warm standby. In my case, all the threads and rep agents are running fine but data changes on PDS.testdb are not getting replicated to RDS.testdb.
On Replication Server:
1> admin logical_status
2> go
 Logical Connection Name
         Active Connection Name
         Active Conn State
         Standby Connection Name
         Standby Conn State
         Controller RS
         Operation in Progress
         State of Operation in Progress
         Spid
 ---------------------------------------------------------------------------
 [102] manvith6605t1.testdb
         [103] MANVITH6605T1.testdb
         Active/
         [104] APMW2K8R2641.testdb
         Active/Waiting for Enable Marker
         [16777317] SAMPLE_RS
         None
         None

1> admin who_is_down
2> go
 Spid Name            State             Info
 ---- --------------- ----------   ----------

On primary: deleted some rows of data.
1> select count(*) from mytable
2> go

 -----------
          24

(1 row affected)

On standby:
1> select count(*) from mytable
2> go

 -----------
          64

(1 row affected)

Feel free to ask for any clarifications.


texts: Does SQL Server guarantee accurate data storage? If so, how?

texts:
This question is specific to SQL Server, but other DB replies welcome.

Read below for some history. My short question is, how does SQL Server guarantee that when I request "ABCDE" to be written to the database, that "ABCDE" is written, not "ABECD"? It has been suggested that data be re-read and compared to guarantee the same results, with the obvious performance penalty.

The company I work for is launch a high-volume product for us (~35,000 pieces each day) for which we will collect data, stored in SQL Server.

Previous, lower-volume products had data stored at the line, locally in SQL Server Express databases, then periodically copied to the server (SQL Server Standard) using cross-server transactions. On occasion, these desktop databases became corrupted, often requiring restoration.

Our new product's process will write to the server directly, bypassing local / desktop storage.

Our management is carrying forward this "desktop corruption" experience into the new product.

Other than appeals to authority (Microsoft would not be in the database business if it didn't store data accurately) or anecdotal experience as evidence (in my five years managing SQL Server databases, have never seen a server [Standard / Enterprise] database become corrupted), is there any Microsoft documentation referencing SQL Server "guarantees"?

This is such a basic truth that is rarely questioned, especially with the standard players (DB2, Oracle, SQL Server).

Thanks!


texts: Why is FCI not considered to be a DR solution?

texts:
FCI is said to be an HA solution.
If one node crashes, it spins up the 2nd node so why is it said to not be a DR solution? There was a disaster at node 1 and node 2 picked up from there. Isn't that isaster recovery?
Further lets assume that the disk is replicated, then can we say that it is a DR solution? Here if node or disk crashes, we have the other node or disk to take over. Isn't this disaster recovery?


texts: powershell to get sql server memory counters and show value

texts:
I am writing a powershell script to capture the following SQL server counters:

SQL Server: Memory Manager: Total Server Memory (KB)
SQL Server: Memory Manager: Target Server Memory (KB)

My machine has 3 instances of SQL servers, so I want this script to capture all counters dynamically and report the value for 1 sample only. i tried writing the following:
Get-counter -List *SQL*Memory* | Select paths, counter | format-list # doesn't display full list

Get-counter -List *SQL*Memory* | Select paths, counter | where {_.counter -like "*server memory*"} |format-list # displays nothing

eventually I want to run this across multiple servers with -computername parameter and hence I want it to capture dynamically.
Can anyone please help me in finding what is missing?
Following is the exact script that I am running:
Function checkTransactionsPerSecond([string] $Hostname )
{
    (Get-Counter -ListSet "*Databases").Counter | Where {$_ -like "*\Transactions/sec"} #this returns nothing
#   $listofmetrics = (Get-Counter -ListSet "*Databases").Counter | Where {$_ -like "*\Transactions/sec"}
#   $listofmetrics | Get-Counter
}

clear

foreach ($Hostname in Get-Content "D:\TEMP\machines.txt")
{
    Write-Host $Hostname
    checkTransactionsPerSecond($Hostname)
}

thanks in advance


texts: Preventing full backups for unmodified/slightly modified databases

texts:
Is there a way to either:

ensure that unmodified databases are skipped during SQL backups, or even better
choose between DIFF or FULL backup depending on the percent database changed since last full backup?

We are using MS SQL Server 2008 R2 + Ola Hallengren's maintenance scripts, ~500 databases - total data files 500 x 2GB, and only about 5% databases get modified each week - but the backup procedure transfers all data files on each full backup.

Background:

Our MS SQL 2008 R2 server is configured to make daily DIFF backups and weekly FULL backups, using the "widely known" Ola Hallengren's maintenance scripts. The system is configured for multi tenancy, and databases are also physically split not only by customers, but also by months. Since the system tracks these databases very well, this makes the system a bit easier to maintain; each customer*month datafile can be located wherever you choose, individual files are smaller in size and easier to manage, and most of the r/w queries for a single customer only go into the last month's db, making them more performant (smaller indexes, less locking, etc.). Recovery model is set to SIMPLE, but losing data is not such an issue for us since these measurements are collected from distributed devices (which also have their local storages) and can re-transmit data automatically.

But the problem is that the backup procedure is becoming bizarrely slow, because each backup copies even the old, unmodified (well, presumably unmodified) backups over and over again. Normally, only the databases for the last month are being modified at any time, so we would only like to backup these weekly. I checked that old databases don't get accidentally modified by index optimization or whatever: e.g. running fn_dblog on old databases only shows some entries seemingly added by the backup procedure, like "CommitDifferentialBase" or "InvalidateDiffMaps".

Update

Using the link from @spaghettidba's answer, I slightly modified Paul Randal's stored procedure to simply return the percentage (to make it slightly easier to get the return value):

-- use at the end of 'sp_SQLskillsDIFForFULL'
return ROUND ((CONVERT (FLOAT, @DIFFtotal) / CONVERT (FLOAT, @sizeTotal)) * 100, 2);

And then I added this check inside Ola Hallengren's DatabaseBackup stored proc:

-- get the percent of changed extents since last full backup
EXEC @CurrentDbChangedPercent = sp_SQLskillsDIFForFULL @CurrentDatabaseName
IF @AutoSelectDiffOrFullOnChange = 1 AND @DiffFullChangePercentThreshold > 0
BEGIN
  IF @CurrentDbChangedPercent <= @DiffFullChangePercentThreshold
    BEGIN
      SET @CurrentBackupType = 'DIFF'
    END
  ELSE
    BEGIN
      SET @CurrentBackupType = 'FULL'
    END
END

As Mr. Hallengren pointed out, DBCC PAGE command used in Paul's proc is not really supported/documented, and this is the main reason this isn't currently a feature of his SQL Server Maintenance Solution. Few people except Paul would be able to parse this data properly.

There is a request to get a supported DMV for this on Microsoft Connect, dating from 2009:

DMV to check the percentage of a database changed since the last full backup


texts: Does SQL server optimize or pre-parse stored procedures?

texts:
Despite what DBAs will tell you ("use stored procedures because the server will optimize them thus they are faster than ad-hoc queries"), I have heard that SQL server does not actually optimize stored procedures.

So, does SQL Server optimize stored procedures? Does it even parse them beforehand, or does it just store them internally as a sql script?

I did not ask about a specific SQL Server version because I assume history is relevant in a meaningful understanding.

Note that my question is NOT, "Are ad-hoc queries as fast as stored procedures?" There are too many other factors involved in answering this.


texts: find if the column of the PostgreSQL table is empty with minimum cost

texts:
I need to find if there are any values written to the column but with minimum cost.
There is a table with ~1B rows and about 100 columns with data. I need to find columns that have no data at all (all nulls) and delete them. If I query like "SELECT * FROM my_table WHERE my_column IS NOT NULL LIMIT 1" then it costs me 1-2 minutes and I'm looking for the faster solution.
As far as I know (but not sure), if there are no data at all for the column, there is some property in the database saying that the chunk or the table has no data for this column, at least this happens when adding a new column to the existing table, so the database doesn't update all existing rows if the default value of a new column is NULL. I wonder if there is a fast way to get a result based on this info.
P.S. I use timescaleDB extension and the PK is a timestamp, if this changes anything


texts:
I discovered that my phpmyadmin cannot login with my root password. So I updated the password as follow:

# mysql
mysql> UPDATE user set authentication_string=password('secret') where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

After doing this I tried to connect with my new root password:

# sudo -u www-data mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I don't understand why I am still denied.

mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select USER(),CURRENT_USER();
+----------------+----------------+
| USER()         | CURRENT_USER() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.00 sec)

I tried with:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';

and it worked, but I have absolutely have no idea of why any other methods do not work.

What is bizarre is that before I could connect in root with # mysql. Now I have to do # mysql -uroot -p. What did I changed?


texts: Killed Backup in Rollback State for Days

texts:
I killed a backup job of an 80 MB database which has been rolling back for days...

I tried to take the database into single user mode, but that too hung in rollback state.

I tried killing all sessions connected to the database, but still cannot drop it.  I used this script to do the kill:

DECLARE @kill varchar(8000) = ''; SELECT @kill = @kill + 'kill ' +
    CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id = db_id('fpddev92')
exec(@kill)

The task's most recent wait type is CMEMTHREAD.  I cannot restore the database with a different name since there is an application using it that has a hard-coded database name within the app.

I am allowed to drop and restore the database if need be.  How do I solve this issue???  It is suspended.  No blocking.

I'm afraid if I restart the service it will simply pickup the rollback upon startup.

Any help is greatly appreciated!  I have never seen anything like this in my life...


texts: Prevent Modification of dbo objects while allowing other schemas to be modified

texts:
I have a vendor solution that uses a sql server (2008r2) database. We are allowed to do anything we want in our own schema, but cannot modify dbo objects without vendor permission. Any customization we make is in our own schema (cust).  We have full control of this server and have always allowed sysadmin rights to any developer. Lately, I've noticed these rules have not been followed, so I want to see if I can set up permissions to stop this in the first place.

Here is what I would like to accomplish:

Tables: read/write to all, alter cust schema only, view design

Views/Sprocs/Functions: view any definition, create or alter cust schema only

I created a new user (dev) and applied the following:

deny alter on schema::dbo to dev
grant alter on schema::custom to dev
grant view definition to dev

Will this accomplish what I want, or am I missing something else?

Solution (thanks to AMtwo)

use master
go
create login dev with password = 'test', check_expiration = off, check_policy = off
grant control server to dev

use CustomerDB
go
if not exists(select * from sys.database_principals where name = 'dev') begin
    create user dev for login dev
    grant select, insert, update, delete, execute, alter on schema::custom to dev
    grant view definition to dev
    deny alter on schema::dbo to dev
    -- deny other schemas here
end`


texts: SQL Server Full-Text Index crawl: Is it done or not?

texts:
I am on SQL Server 2012. I have one table with one full-text index. That index was dropped and recreated around 8am due to a change in the datatype of the indexed column from TEXT to VARCHAR(MAX). These are the relevant entries in the log:

  2017-01-06 08:02:52.65 spid25s     Informational: Full-text Full
  population initialized for table or indexed view
  '[Helpdesk].[dbo].[TicketPosts]' (table or indexed view ID
  '581577110', database ID '8'). Population sub-tasks: 4.

  2017-01-06 10:56:47.98 spid9s      Informational: Full-text Full
  population completed for table or indexed view
  '[Helpdesk].[dbo].[TicketPosts]' (table or indexed view ID
  '581577110', database ID '8'). Number of documents processed: 7016145.
  Number of documents failed: 0. Number of documents that will be
  retried: 0.

  2017-01-06 11:24:42.11 spid20s     Informational: Full-text Auto
  population initialized for table or indexed view
  '[Helpdesk].[dbo].[TicketPosts]' (table or indexed view ID
  '581577110', database ID '8'). Population sub-tasks: 1.

  2017-01-06 11:24:43.12 spid9s      Informational: Full-text Auto
  population completed for table or indexed view
  '[Helpdesk].[dbo].[TicketPosts]' (table or indexed view ID
  '581577110', database ID '8'). Number of documents processed: 0.
  Number of documents failed: 0. Number of documents that will be
  retried: 0.

Between 11am and 11:24am, the TableFulltextItemCount OBJECTPROPERTY and the TableFulltextDocsProcessed OBJECTPROPERTY were equal (and were the same as the documents processed shown in the log for the first crawl).

However, during that time the TableFulltextPopulateStatus OBJECTPROPERTY returned 1 (Full Population In Progress), sys.dm_fts_index_population.completion_type_description was 'NONE', and sys.fulltext_indexes.crawl_end_date was null.

Why did the DMVs think that the crawl was still in progress long after the log says it was done and the processed count equals the item count? What was it doing during that time?

No change was made to the data between the first and second crawl, so why did the second crawl occur?


texts: Can a critical section cause deadlock in SQL Server?

texts: We get the next ID for our tables from a separate table with two columns, TABLE_NAME and NEXT_ID. We have a stored procedure which reads the NEXT_ID and then adds an increment to the NEXT_ID. The work is done within a transaction and the transaction contains a critical section. The critical section was added when some processes where reading the same NEXT_ID and causing key violations. Now the critical section is appearing in dead lock graphs. Can the critical section cause/help cause deadlock?


texts: Connect to SQL Server on Linux over the network

texts:
I have setup Microsoft SQL Server on an Ubuntu VM.

I know how to connect to SQL Server on Windows using SSMS -> but unfortunately I can not install SSMS on Ubuntu.

Which leads me to the question at hand of how would I do this from the terminal using sqlcommand?


texts:
Is the difference between the SCN of primary and secondary a good indicator of RPO?

We are planning for the below logic to get the RPO.

Get the current SCN from secondary.
Get the time stamp of the above SCN(as A).
Get the current SCN from primary.
Get the time stamp of the above SCN(as B).
Calculate the RPO as the difference of B-A.

Is this logic valid?


texts:
I have an SQL Server 2016 v13.0.5101.9 hosting Sharepoint databases. The weekly DBCC CHECKDB job discovered database corruption (or inconsistency): Msg 8928,  and Msg 8965 popping up.

I narrowed it down to the Docstreams table which occupies 99% of the database space, so that doesn't help much: the table is around 100GB, I suspect mostly in the "Content" column of type  varbinary(max).

Within that table I narrowed down the corruption to 8 pages. The REPAIR ALLOW_DATA_LOSS option of DBCC CHECKTABLE which was suggested is not really appealing, considering the possible consequences in Sharepoint. So far, there are no complaints about the corruption from the users, but I suspect that is just a matter of time.

What I'm trying to do is to identify corrupt rows and have the rows (=sharepoint content) removed in a controlled way using sharepoint. So there I go using DBCC PAGE (parameters) which I found is supposed to have four main sections: Buffer, Page Header, Data, and Offset Table  ... but it show only the first 2 sections, the third is supposed to show the data I want to see.

So the question is: Is there a(nother) way to identify the rows that are affected by the corrupt pages that allows me to point to the sharepoint content?

Note 1: this is a new database of which the contents were migrated from a filesystem by an external vendor last week. There is no indication of any faulty disk, but somehow this inconsistency must have crept in.

Note 2: I have copied the database to an non-prod SQL Server in order to investigate the issue. I have already removed the unique constraint and thus related clustered index. The problem lies in the table itself. In other words: recreating the (clustered) index doesn't work.

Note 3: I was thinking about doing a select * into newtable, but as I'm writing this, it crashed with:

  Msg 824, Level 24, State 2, Line 67
  SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xd843cba2; actual: 0x908acba5). .... and actually pointing to the pages found in the DBCC.


texts: Possible to do OPENQUERY without named pipes?

texts:
When I try to do OPENQUERY I get an error about not being able to open named pipe, which makes sense because that's not an option (and isn't going to be an option any time soon) for the way the network is currently setup between the client machine and the server.

Does this mean that I won't be able to use OPENQUERY, or can it be configured to work without named pipes?


texts: Use filters instead of queries in MS Access?

texts:
I realize many things could be done in Access like instead of using queries for filtering things, it is posible to use filters.
I'm not sure if the program makes SQL with VBA queries under the hood or the inner working is different, which in the case of the second:
What is better of the two?
Is it wrong to use filters and even making forms that make use of filters instead of making queries for filtering results?


texts: Should I use bigint or varchar(20) as primary key, performance-wise, for Postgres?

texts:
I have 18 character long IDs I get from an API, and I currently store them as varchar(20). They are used as primary and foreign keys. Is it better in my case, to use bigint instead of a string type, performance-wise?

Since JavaScript cannot deal with bigints, they are converted by default to string, but I could set columns to be bigint, if that will help.


texts: Oprhaned entries in msdb..sysjobactivity

texts:
In this question:
https://stackoverflow.com/questions/18445825/how-to-know-status-of-currently-running-jobs

This answer was proposed:

SELECT sj.name
   , sja.*
FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
WHERE sja.start_execution_date IS NOT NULL
   AND sja.stop_execution_date IS NULL

However for one of my servers this has an entry dating back 10 months ago. Looking in SQL Agent Job Activity Monitor shows no running jobs. Looking in msdb..sysjobs shows a mere 3 jobs. Can anyone explain this phenomenon?

The orphaned row has NULLs for queued_date, stop_execution_date, job_history_id and next_scheduled_date. Rest of the values look reasonable.


texts:
If I have a simple table with 2 columns and a composite primary key consisting of both columns, how do I rename one of the columns but only if it doesn't cause a primary conflict?

Here's a simplified example.

Take the following table:

User  | Role
------|----------------
Joe   | User
Joe   | Administrator
Brian | User
Anna  | Administrator
Anna  | Admin

Suppose I want to write the following query:

UPDATE UserRoles Set Role = 'Admin' WHERE Role = 'Administrator'

Without using cursors, how do I change my SQL Update statement to avoid renaming Anna (because that user already has the Admin role), but still rename Joe?

Is that even possible using set based SQL or is using a cursor the only way?

Thanks.


texts: Is it a good idea to index timestamp in postgresql?

texts:
Here is my table definition

-- Table: public.my_table

-- DROP TABLE public.my_table;

CREATE TABLE public.my_table
(
    my_row_id text COLLATE pg_catalog."default" NOT NULL,
    my_item_id text COLLATE pg_catalog."default" NOT NULL,
    my_timestamp timestamp with time zone NOT NULL DEFAULT now(),
    --  ...... some other data
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.my_table
    OWNER to my_db;

The cardinality of the table is about 10-20 million rows

Here is the query I want to speed up

select my_row_id, my_item_id
from my_table
where my_timestamp > timestamp '2018-09-13 00:00:00'
order by my_timestamp desc

I am tempted to just index the timestamp, but I worry that since the precision of timestamp is millisecond, this means the the cardinality of the index itself will be almost as large as the cardinality of the table (eg. the number of rows will be equal to the number of index entry)

Should I have custom index that index with the precision of a day?

Like store an extra column text 2018-08-17 from timestamp with timezone 2018-08-17 07:06:43.508778

And index the text instead?


texts: Optimize Query: Group By while tracking Count

texts:
Is there a better way to write this query? The query is taking too long (aprox 4sec)  The box table has about 8k records per day.

I've tried different Indexes but none seem to improve it so I'm thinking it's my query.

SELECT
boxType.id, sum(box.numBoxes) as totalBoxes,
count(DISTINCT box.employeeId) as totalEmployees
FROM box
INNER JOIN user as u ON u.id = box.userId
INNER JOIN jobBox as jobBox ON box.jobBoxId = jobBox.id
INNER JOIN boxType as bType ON bType.id = jobBox.boxTypeId
WHERE (u.clientId=?) AND (box.createdOn >= ?)
GROUP BY boxType.id

-- box
CREATE TABLE `box` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) NOT NULL,
`employeeId` int(11) DEFAULT NULL,
`numBoxes` float NOT NULL,
`createdOn` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`jobBoxId` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `User id_idx` (`userId`),
KEY `JobBoxFK_idx` (`jobBoxId`),
KEY `FK_5pomtj2u3uh6xq3qf7vmokc5k` (`employeeId`),
KEY `IDX_CreatedOn` (`createdOn`,`jobBoxId`),
CONSTRAINT `UserFK_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)   ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `packFK_1` FOREIGN KEY (`jobBoxId`) REFERENCES `jobBox`   (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `EmployeeFK_1` FOREIGN KEY (`employeeId`) REFERENCES   `employee` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='.';

-- jobBox
CREATE TABLE `jobBox` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`jobId` int(11) DEFAULT NULL,
`boxTypeId` int(11) DEFAULT NULL,
`createdOn` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `JobBoxFK_idx` (`jobId`),
KEY `BoxTypeID_idx1` (`boxTypeId`),
CONSTRAINT `jobBox_ibfk_1` FOREIGN KEY (`boxTypeId`) REFERENCES    `boxType` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `jobBox_ibfk_2` FOREIGN KEY (`jobId`) REFERENCES `job`  (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1937 DEFAULT CHARSET=utf8;

-- boxType
CREATE TABLE `boxType` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;

-- user
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`clientId` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`),
KEY `clients id_idx` (`clientId`),
CONSTRAINT `clients id` FOREIGN KEY (`clientId`) REFERENCES `client`   (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=utf8 COMMENT='  ';

Explain statement results

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'box', NULL, 'range', 'EmployeeFK_1,packFK_1,IDX_CreatedOn', 'IDX_CreatedOn', '5', NULL, '33034', '100.00', 'Using index condition; Using temporary; Using filesort'
'1', 'SIMPLE', 'u', NULL, 'eq_ref', 'PRIMARY,id_UNIQUE,clients id_idx', 'PRIMARY', '4', 'box.userId', '1', '14.04', 'Using where'
'1', 'SIMPLE', 'jobBox', NULL, 'eq_ref', 'PRIMARY,jobBox_ibfk_1', 'PRIMARY', '4', 'box.jobBoxId', '1', '100.00', 'Using where'
'1', 'SIMPLE', 'boxType', NULL, 'eq_ref', 'PRIMARY,UnitTypesFK_idx', 'PRIMARY', '4', 'jobBox.boxTypeId', '1', '100.00', NULL

Any thoughts are appreciated, thank you.


texts: Cannot connect database as sys as sysdba using sqlplus

texts:
I am new to oracle server 18c (currently taking online course). My problem is sql*plus where I tried to use sqlplus then sys as sysdba (it did not work) and the error appeared below.

However, if I use sqlplus / as sysdba (it worked, and I don't know why)

I have tried this command: lsnrctl start, but again not working. In addition, I have checked my listener.ora and tnsnames.oraca

I have attached my environment set up in .bash_profile

Please help me this. Thank you


texts: Subtracting from multiple CASE statements

texts:
I'm trying to get the net sales from the query below in SQL Server 2008.

We identify a Sale as Type 1 and a Credit as Type 8.

What I'd like to do is get the Sum of the Sales and Subtract the Sum of the Credits to get the NetSales, but I haven't had any luck.

I'm not really sure if I should be using the CASE statement to get the NetSales. I'd like to see the below from the query, or even with just the NetSales showing:

And here is the query:

SELECT TOP (100) PERCENT IM.IM_PROD_CODE, IV.IV_CUKEY, IM.IM_KEY, IM.IM_DESCR,
   SUM(CASE WHEN IV.IV_TYPE = '1' THEN SH.SH_QTY * SH.SH_PRICE END) AS Sales,
 SUM(CASE WHEN IV.IV_TYPE = '8' THEN SH.SH_QTY * PK1.SH.SH_PRICE END) AS Credits

FROM IV INNER JOIN
  SH ON IV_ENTITY_ID = SH.SH_IVENTITY_ID AND IV.IV_NUMBER = SH.SH_IVNUMBER INNER JOIN
   IM ON SH.SH_ITEM = IM.IM_KEY
WHERE
   (IV.IV_TYPE = 1 OR IV.IV_TYPE = 8) AND (IM.IM_PROD_CODE = 'ESY') AND   (SH.SH_DATE >= CONVERT(DATETIME, '2015-11-01 00:00:00', 102))

GROUP BY
   IV.IV_CUKEY, IM.IM_PROD_CODE, IM.IM_KEY, IM.IM_DESCR, IV.IV_TYPE
HAVING
   (IM.IM_KEY = 'A-05.000.007.01_LM')


texts: How to install MySQL server on Mac (OS X 10.6.8) from command line?  If I donwload the .dmg file, is it possible to install it from command line?


texts: How to avoid IX deadlock in check constraint

texts:
I have following situation, I have a table with a primary key, and a constraint that I cannot have rows with specific requirements. For demonstration purposes, here I have constraint that doesn't allow to insert duplicate values in N column. In real case, it checks several columns using foreign keys to other tables and with additional filters, so I cannot put simple unique constraint.
So here is example
create table dbo.T1 (
    Id int not null identity (1,1),
    N int not null
)

alter table dbo.T1
add primary key (Id);

go

create function [dbo].[fn_CheckN](@id int, @n int)
returns int
as
begin
    if exists (select * from dbo.T1 t where t.n = @n and t.Id != @id)
        return 0

    return 1
end

go

alter table [dbo].T1 with nocheck add  constraint [CK_T1_Valid] check  (([dbo].[fn_CheckN]([Id],[N]) = 1))
go

alter table [dbo].T1 check constraint [CK_T1_Valid]
go

When I run concurrently
insert into dbo.T1 (N)
values (@i)

I get this deadlock S -> X, X -> S on primary key. And that I kind of understand why. Deadlock xml:
https://pastebin.com/hceR3sum
My first attempt to fix this was to grab S lock first
begin tran
declare @lock int = (select top(1) 1 from dbo.T1 with (tablock, holdlock))

insert into dbo.T1 (N)
values (@i)

commit

But it failed with this deadlock S -> IX, IX -> S. Can somebody explain what is going on? Deadlock xml: https://pastebin.com/mLXJb59C.
And I fixed it with locking entire table with X lock. Is it ok? Is there a better approach?
begin tran
declare @lock int = (select top(1) 1 from dbo.T1 with (tablockx, holdlock))

insert into dbo.T1 (N)
values (@i)

commit

If I put index on N column I get this deadlock https://pastebin.com/KJGmLDhH. The real requirement is pretty much the same, the simplest case there are 4 columns with accountIds and enabled flag, and when something happens to the record I have to check that account id is unique among all enabled records. or if it's disabled that I have nothing to check. Something like that.
I use C# to run queries simultaneously
class Program
{
    private const string connectionString = "Server=.;Database=Performance;Trusted_Connection=True;MultipleActiveResultSets=True; Max Pool Size=3000";

    static async Task Main(string[] args)
    {
        await ClearAsync();
        await Task.WhenAll(Enumerable.Range(0, 100000).Select(async i => await InsertAsync(i)).ToArray());
        Console.WriteLine("Done");
        Console.ReadKey();
    }

    public static async Task InsertAsync(int i)
    {
        using var connection = new SqlConnection(connectionString);
        using var cmd = new SqlCommand(@"
insert into dbo.T1 (N)
values (@i)
", connection);
        await connection.OpenAsync();
        cmd.Parameters.Add("@i", SqlDbType.Int).Value = i;
        await cmd.ExecuteNonQueryAsync();
    }

    public static async Task ClearAsync()
    {
        using var connection = new SqlConnection(connectionString);
        using var cmd = new SqlCommand("delete from dbo.T1", connection);
        await connection.OpenAsync();
        await cmd.ExecuteNonQueryAsync();
    }
}

I use Microsoft SQL Server Express (64-bit) 13.0.1601.5


texts:
I have an SSRS report which shows the 'last backup time' next to each server in the list. I am getting this information by using:

SELECT TOP 1 @@SERVERNAME as serverName,
sdb.Name AS DatabaseName,
MAX(backup_finish_date) AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
WHERE bus.type = 'D'AND bus.database_name NOT IN ('master', 'model', 'msdb', 'tempdb')
GROUP BY sdb.Name
ORDER BY LastBackUpTime DESC

The problem here is that if for example 7 out of 8 database backups fail in a job, that job is flagged up as 'failed', but the report will show that the last backup time was in that batch of failed backups, which it was, as it is selecting the one backup of 8 which succeeded.

Prior to this I was finding the last run time of the specific job from a table which I inserted the sysjobs and sysjobservers information into, as most of our backup jobs are hallengren jobs named identically. (see below)

SELECT serverName
         , MAX(CLastRunDateTime) AS CLastRunDateTime
    FROM  jobs
    WHERE name = 'DatabaseBackup - USER_DATABASES - FULL'

The problem with this is that there are some servers which have a non hallengren named job, so it involves 'fiddling' to insert the correct job name. There are also some servers which do not have agent and have backups taken manually, so job name cannot be used to pick up the last backup time.

How can I get a backup time at server level with as little 'fiddling' as possible. I understand the problems, as some hallengren jobs exclude certain databases, so it cant be based on 'when were all backups taken on this server' - it has to be on the one hand job specific, on the other hand not job specific.
Maybe something like, when the job name is 'X', 'Y', 'Z', then find it this way, when job name DOES NOT EXIST, then use max backup for last database? But that seems to involve the fiddling around that I don't want.

I'm not sure. Wondering if anyone has a good way of doing this.


texts: Does dm_exec_procedure_stats.total_worker_time include signal waits?

texts:
Does sys.dm_exec_procedure_stats.total_worker_time include signal waits or only time spent by procedure on scheduler?

The reason of question - when number of cores was increased on the server signal waits decreased as expected, but total_worker_time/execution_count has dropped too.


texts:
I am using MariaDB 10.0 multi-source replication for a specific use case.
For security reasons, I would like to prevent ALTER commands on master to replicate (such as CREATE, ALTER, DROP...) whatever user run these commands (even root) but of course let SELECT, INSERT, UPDATE, DELETE commands to replicate....
I do not want to use SET SQL_LOG_BIN=0|1 on client side. In fact, I never want to replicate schema modification.
In practice, I wish I could revoke alter permissions to my replication user (who currently has REPLICATION SLAVE permission)
Is there a way to achieve this?
EDIT 2018-02-19
Since my requirements seems nonsense for some readers, I give some additional information about this use case.
I created one (or more) MariaDB Proxy database(s) with tables using BLACKHOLE Storage Engine. So data is not stored on this proxy server, but binlogs are.
I have other MariaDB servers running the same database schema but with INNODB storage engine that replicates data from proxy server(s) using MariaDB Multi-source Replication.
On the proxy server, I can safely recreate, for example, a table schema with a CREATE OR REPLACE TABLE mytable (id int) ENGINE=BLACKHOLE statement as there is no data stored in it.
But this kind of statement MUST NOT run as is on the "slaves" (which are not real slaves as you noticed) as they must remain in their original storage engine or any other option they may have at the table level.
I can do this by issuing a SET SQL_LOG_BIN=0 before executing my command, but I was looking for a way to make sure that I will not break the slaves in case I forget to do it.


texts: BCP stops after the last "1000 rows sent to SQL Server. Total sent: ..."

texts:
I ran SQL Server 2005's bulk copy program BCP like this:

bcp mydb.dbo.mytbl in myfile.blk -c -S mysvr -U mylogin -P mypass

And it ran and produced output like so:

Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
...
1000 rows sent to SQL Server. Total sent: 55000

But then it stopped. The cursor did not return to prompt, I haven't received the "... rows copied." message.

I tried querying the target table and I can already see the rows that I wanted to import.

Shall I terminate the console? Will it rollback?


texts: ORA-12560: TNS:protocol adapter error in Windows 10

texts: Listener and service are up and running. In SQL Plus, if I connect with no login, and execute 'conn sys/pwd@host:port/sid as sysdba', connection is established successfully. But if I connect / as sysdba or with username  and password , it always throws ORA-12560: TNS:protocol adapter error. What are the possible reasons for this ?


texts: Can't delete Oracle Datafiles after DROP TABLESPACE INCLUDING CONTENT AND DATAFILES

texts:
Just sharing this as a lesson learned...

On Windows, when using DROP TABLESPACE INCLUDING CONTENT AND DATAFILES, the actual files on the harddrive don't get removed. Sometimes (not always) after a reboot, they get deleted. And sometimes (most of the time not) you can delete them manually.

Most of the time tough, they just stay there, and Oracle keeps a lock on them, preventing you from deleting them. Why is this? What can you do about it?

So, what I've been doing to delete these files is the following...

Once I've made sure that the datafile is no longer known by Oracle, and there is no I/O going on on the datafile, I find and close all open Handles.

To do this, I use Process Explorer (run as administrator) I go to 'find', 'Handle or DLL', and then search for the filename I want to delete. Most of the time there are two handles open to the specific file. Sometimes 4, and 2 close after about 20 seconds. If more handles are open, I would double check there is no I/O, and Oracle has no knowlege of this file. Now, just right click the handles and close them. You can now delete the file.

I've been using this method for years, and never had a problem... Yet, I have no clue why those handles stay open, and what any possible side effects might be.


texts: TSQL query for viewing start/end of all SQL Agent jobs yesterday

texts:
See this related query: What's a good tool for visualizing SQL Agent jobs?

I'm not after a visual tool however, since I am looking to build my own visualisation.  This is my starting point, but you'll agree it's sorely lacking.

select TOP(1000)
       h.run_date, min(h.run_time), j.name, j.description, max(h.run_duration) run_duration
from msdb..sysjobhistory h
join msdb..sysjobs j on j.job_id = h.job_id
group by h.run_date, j.name, j.description, h.instance_id, j.job_id
having max(h.run_duration) > 1000  -- << -- 10 minutes encoded in HHMMSS (digits)
order by h.run_date desc, 2 desc

The crux is that there does not seem to be a key to the sysjobhistory table to identify it as a particular "instance" of a sysjob.  For example, a job that started at 12:30 with 4 steps gets inserted into sysjobhistory 4 times with different instance_ids (unique).  They all tie to the sysjob.job_id value, but that's the schedule, not the invocation of the job.  Is there another table in which I should look?

This is probably fairly simple, since SSMS is able to do it easily.


texts: How to synchronize two MySql tables using command line

texts:
I have two tables in the same database (MySql) where one has been populated and there are data been added to it frequently.

I want to have the data added to the first table to be present in the other (synchronized) using a MySql command in console (without any other methods such as triggers, events etc.)

Is this possible and if so what is the command?


texts: Can we reduce row lock contention on an index

texts:
We have few queries which check their status into a table every 5 seconds and update their status..
Below is how the query looks like(all table columns are in update query)
update table1
set
Name='somename'
DetailMessage='Error'
LastUpdate=getdate()
where id=14

Recently this table has run into blocking issues and above update query is lead blocker.When  i ran Sp_Blitzindex, it showed no missing indexes,bur row lock contention of 284 minutes..
Below are few steps that i did so far..
1.Ensured Foreign keys are indexed(they are also Primary key)
2.Create indexes for select statements which can benefit from new index,so that lock contention on this index is reduced
3.I also reduced Fillfactor to 10, earlier each page used to store 93 rows, now it stores only 7 rows per page(still not able to give myself a 100% logical explanation on  how this change would help... )
Any suggestions further..Let me know if you need any further details
Below is the schema of table(changed column names) and table has only 350 rows and queries update this table at frequency of 20/30 queries every 5 seconds...
create table dbo.table1
(
[ID] [int] NOT NULL,
[Name] [varchar](500) NULL,
[DetailMessage] [nvarchar](max) NOT NULL ,
[LastUpdate] [datetime] NOT NULL
)

PS: This is a third party query and we can't modify source database with changes like changing isolation level...I can add indexes only
SQl version:SQl 2012


texts: Can I upgrade MariaDB replication infrastructure gradually?

texts:
I have 2 masters and 1 slave (replicating form both masters).
M1- MariaDB 10.4.11
M2- MariaDB 10.4.16
S1- MariaDB 10.4.12
I want to upgrade this LIVE system to 10.5 with possibly 0 downtime. The idea is this:

upgrade slave S1 (this is used mainly for backups, not queried at all)
switch all traffic to M2, stop M1 and upgrade M1
turn on M1 and sync it from M2 - at this stage I will have active master M2 running old 10.4 MariaDB, but both standby master M1 and slave S1 are already on 10.5
switch all traffic to M1, stop M2 and upgrade M2
sync M2 from M1 and keep it as standby master

I guess my main question is - Is MariaDB 10.5 <-> 10.4 replication compatible? Any particular issues I should look for?
Thanks!


texts: Most efficient way to store a large, changing number of properties

texts:
I have an entity with a large number of boolean properties.

It currently has ~100 properties
It will periodically get new properties
The number of properties will grow to 400+
There will be ~200,000 records
The records will be updated en masse every few days
Reports will generated daily from the records

Should I just have a big-ol' table with a bunch of columns?  I'm a bit of a noob when it comes to how well MySQL can handle tables that large that frequently change the number of columns.  Would this have horrible performance?

I don't much want to have a bunch of tables, one for each property, like IsPropertyA.  Sounds like a million joins for any query.  But is this the better way to do it?

EDIT

Here's an example of the first few properties I'd be keeping in the entity PageOfText.  There are only 4 shown here, but there will be 400+ eventually.  Each boolean would be metadata about a block of text.

create table PageOfText (
hasExpletives BOOLEAN,
hasEmailAddresses BOOLEAN,
isAboutCats BOOLEAN,
isTechnicalWriting BOOLEAN
....
)


texts: Getting pg_dump and pg_restore right for postgresl (9.3.10)

texts:
I'm to transfer a postgresql 9.3.10 DB (~55GB) from Azure VM to an AWS EC2. Being a novice, this question is about getting the process right. I've seen this post, and need to go a bit beyond that:

Is dumping the live production database on a busy server perfectly safe? Docs explain how pg_dump is non-blocking, so I'm inclined to think it is. In your experience, does pg_dump always go off without a hitch on production, or are there exceptions? My other (unpleasant) option is to shut down the server, creating a "hard" maintenance window.
The docs mention using --format to control dump format. c (custom) looks promising, but I found the description vague in terms of how to use it. Should I simply try the pg_dump command like so:  pg_dump -U postgres -Fc db_name > db.dump? Any other practically useful flags would be of interest as well.
A simple guide I've read recommends wrapping the whole restore into a single transaction: psql -1 restored_database < backup_file. That gives a binary outcome by failing in entirety if there are restoration errors. i) What could cause restoration errors? and ii) the restoration command I originally planned on using is pg_restore --verbose --clean --no-acl --no-owner -U myuser -d mydb latest.dump. That looks right?


texts:
I have a very high transaction database
I have multiple sequences to unique generate numbers for different outlets.

One of the outlets started receiving errors because i have a unique constraint on a table to prevent duplicates.
The sequence that generates the numbers rolled back more than 10 numbers.

I can afford for this to happen again because it stops sales and I currently have over 70 sequences and going to added more as the months go by.

Has anyone ever experienced this before and how can i stop this from happening in the future ?

any help would be appreciated .. thanks


texts: How to find the latest SQL statements within the database?

texts:
I like to get the latest executed statements within my database, along with performance indicators.

As such, I like to know, which SQL statements were most CPU/DISK intensive.


texts: plpgsql - stored procedure Comma separated values in parameters

texts:
Im writing a stored procedure to get database and table names in variables and then I'll push these values into IN clause.

CREATE OR REPLACE PROCEDURE bhuvi(db varchar(100), tbls varchar(100))
 LANGUAGE plpgsql
 AS $$
 DECLARE
   rec RECORD;
    v timestamp;
   tables_s varchar(100);

 BEGIN
 select '''' + REPLACE(tbls, ',', ''',''') + '''' into tables_s;
    FOR rec IN select table_schema, table_name from information_schema.tables where table_catalog in (db) and table_type='BASE TABLE' and table_schema not in ('pg_catalog','information_schema')  and table_name in (tables_s)
   LOOP
   select now() into v;
     RAISE INFO ' % printing... schema = % and table = %',v, rec.table_schema, rec.table_name;
 END LOOP;
 END;
 $$;

If I call this procedure, its just showing CALL. No results.

But in my FOR LOOP, if I remove the and table_name in (tables_s), Im able to print all table names and schemas.

But something wrong with the multiple table names in the variable. Without that its working.

Can someone help me to figure it out what was wrong in my script?


texts: Query: Most recent record for unique combination of two fields in MS Access 2010

texts:
I'm trying to run a query on some traffic volume data but I'm having some trouble.
I would like to pull out the most recent data for each section of road, but this is proving more complicated than expected.

Here is an example of the data I am working with:

S_ID | DATE      | DIRECTION | AADT
1001 | 9/16/2013 | North     | 700
1001 | 9/16/2013 | South     | 600
1001 | 1/30/2011 | NULL      | 1093
1001 | 2/31/2009 | NULL      | 1091
1211 | 1/17/2012 | NULL      | 945
1235 | 1/23/2014 | North     | 500
1235 | 6/19/2014 | South     | 350
1235 | 5/10/2011 | South     | 354
1235 | 5/10/2011 | North     | 399
1301 | 9/16/2013 | East      | 700
1301 | 4/16/2012 | West      | 600
1301 | 1/30/2014 | NULL      | 1093

I am interested in the returning the most recent AADT (Annual Average Daily Traffic) value for each SectionID and direction.
In the data above, I would want the values below returned:

S_ID | DATE      | DIRECTION | AADT
1001 | 9/16/2013 | North     | 700
1001 | 9/16/2013 | South     | 600
1211 | 1/17/2012 | NULL      | 945
1235 | 1/23/2014 | North     | 500
1235 | 6/19/2014 | South     | 350
1301 | 1/30/2014 | NULL      | 1093

I have the following code:

SELECT  a.*
INTO    recent_data
FROM    primarytrafficcountdata a
INNER
JOIN    (
        SELECT  section_id,
                direction,
                MAX(date) AS maxdate
        FROM    primarytrafficcountdata
        WHERE   YEAR(date) <= 2014
        AND     YEAR(date) >= 2011
        GROUP BY
                section_id,
                direction
        ) groupeda
ON      (a.section_id = groupeda.section_id
AND      a.direction = groupeda.direction
AND      a.date = groupeda.maxdate)
OR      (a.section_id = groupeda.section_id
AND      a.direction IS NULL and groupeda.direction IS NULL
AND      a.date = groupeda.maxdate)
;

But this returns:

S_ID | DATE      | DIRECTION | AADT
1001 | 9/16/2013 | North     | 700
1001 | 9/16/2013 | South     | 600
1001 | 1/30/2011 | NULL      | 1093
1211 | 1/17/2012 | NULL      | 945
1235 | 1/23/2014 | North     | 500
1235 | 6/19/2014 | South     | 350
1301 | 9/16/2013 | East      | 700
1301 | 4/16/2012 | West      | 600
1301 | 1/30/2014 | NULL      | 1093

This is the most recent dates, which I want, but I want to eliminate the North/South or East/West directions and only use the NULL directions when the NULL direction is more recent or vice-versa when the North/South or East/West are the most recent records.

Does anyone know how I would do this?

I am using Microsoft Access 2010.


texts: How to improve query to include 2 critical pieces of employee data

texts:
I’m going to preface by saying that I’m fairly green with SQL.

My code below works, however I am missing two vital pieces of my employee population: (1) those employees who have been invoiced but had no corresponding payroll deduction, and (2) those employees who experienced payroll deductions but were not invoiced. The code, as it is now, only performs the arithmetic on employees who have corresponding data in both the Employee_Deductions and Employee_Invoices tables. I've been reading up on CASE, but am not certain if that's the path I should go down. I greatly appreciate any constructive critiques and suggestions on how to arrive at my desired result. Thank you.

SELECT
    PayrollpersonID,
    PayrollDeductionYear,
    January,
    February,
    March,
    April,
    May,
    June,
    July,
    August,
    September,
    October,
    November,
    December,
    Total
FROM
    (SELECT
        PayrollpersonID,
        PayrollDeductionAmount-i.invoice as Variance,
        PayrollDeductionMonth,
        PayrollDeductionYear,
        Total = sum(PayrollDeductionAmount -i.invoice) over (partition by PayrollpersonID)
    FROM
        Employee_Deductions as d,
        (SELECT
        InvoicepersonID,
        sum(isnull(v.InvoiceAmount,0.00)) as invoice,
        InvoiceProcessedMonth,
        InvoiceProcessedYear
        FROM
            Employee_Invoices as v
        WHERE InvoiceProcessedYear = '2015'
        GROUP BY InvoicepersonID, InvoiceProcessedMonth, InvoiceProcessedYear) as i
    WHERE
        d.PayrollpersonID = i.InvoicepersonID and
        d.PayrollDeductionMonth = i.InvoiceProcessedMonth and
        d.PayrollDeductionYear = i.InvoiceProcessedYear and
        d.PayrollDeductionYear = '2015' and
        d.PayrollDeductionAmount > 0)
SOURCE PIVOT
(
SUM(Variance) FOR PayrollDeductionMonth in (January, February, March, April, May, June,
                                            July, August, September, October, November, December)
)
AS pivottable
ORDER BY PayrollpersonID;

Table Schema:

The [Employee_Deductions] table has the following columns:

PayrollDeductionYear nvarchar,
PayrollDeductionMonth nvarchar,
PayrollpersonID nvarchar,
LastName nvarchar,
FirstName nvarchar,
PayrollDeductionAmount money

The [Employee_Invoices] table has the following columns:

InvoiceProcessedYear nvarchar,
InvoiceProcessedMonth nvarchar,
InvoicepersonID nvarchar,
EmployeeLastName nvarchar,
EmployeeFirstName nvarchar,
EmployeeEmail nvarchar,
OrganizationID nvarchar,
OrgName nvarchar,
OrgAddress1 nvarchar,
OrgAddress2 nvarchar,
OrgCity nvarchar,
OrgState nvarchar,
OrgZip nvarchar,
OrgEIN nvarchar,
InvoiceAmount money

Result Table:


texts: Many-To-Many relationship between Students and heights (Is it necessary?)

texts:
So I'm trying to design a database for my local school doctor. (This is somewhat analogous to my real problem)
Currently, a student is scheduled for a health check every half year, where the students height is measured.
As it can be seen, one student therefore may have multiple heights, which is signified by a join table, i.e. Student_Height.
To me, this seems like the best way to associate multiple heights to One student. The only downsite is though, that since we may have 1000 students, it is pretty certain that the same height will be measured multiple times - Is this bad practice, and is it overkill to have an extra table for this?
As I see it, this issue could alternatively be solved in two ways:

Pre-populate the height table with heights (i.e. 1, 1.1, 1.2, 1.3, ... 2.9,3.0 ... 180.0 cm).
Put height as a column in the student table, and let it be a Varchar(Max) - Meaning, it could be a comma-separated string of heights. (I don't like this - since it limits/complicates my PowerBi possibilities)

Please let me hear your thoughts on this. Are my thoughts above justified?


texts: Distinguish between unknown and non-present foreign keys

texts:
I'm in the situation of importing data from multiple other applications.
The data is stored in multiple tables that contain foreign keys.
Some of these applications don't provide all the needed data - the application doesn't give me an ID I could put in the foreign key column (either because that application doesn't manage that data or the API doesn't provide access to that field).
I've used NULL if one value is not known. In cases where the value is nothing, I've used 0 or an empty string, for example an empty string for a title if something is untitled.
However, this doesn't work with foreign keys. When using NULL I don't know if the relation is currently unknown or if there is no relation. Using a special value (e.g. 0) also doesn't work because that would violate the foreign key constraint.
I want to find a method to distinguish between the data that (a) applies for a given foreign key column but is currently unknown, and (b) the data that simply does not apply for such column and therefore will never be known.
I can distinguish the two cases (foreign key exists or not) when I get the data from the applications, but I don't know how to write that information to the database without losing that information.
I'm currently using MySQL, but I maybe switch to PostgreSQL (for other reasons).
Example
Let's say I have among others the tables music_album and file:
music_album(album_id, title, cover_id, ...)
file(file_id, ...)

music_album.cover_id is a foreign key to file.file_id.
Now the problem is as following:

application A gives me the title of the album, etc. including the cover. If the album doesn't have a cover the application will also give me that information
application B gives me the title, etc. but not the cover

If I simply stored a NULL for an album without cover from application A and also store all albums from application B with cover_id=NULL I later don't know if the album doesn't have a cover or that the application didn't give me the information about the cover.


texts:
I'm experiencing a very strange behavior on SQL Server 2012. I have a database with several views, two of them are CustomerView and ShipToAddressView.

Problem is: Suddenly these views got renamed to CustomerViewView and ShipToAddressViewView! If I drop and create the views with the correct names, it succeeds, but after a few minutes the rename happens again.

I have no idea why this is happening; this is what I've checked:

no other users are renaming those views;
there are no jobs in SQL Server Agent renaming those views;
there are no triggers renaming those views.

What else could it be?


texts:
Refer to this question on SO because it's similar.

I'm working on a way to delete a range of dates from all tables with date values in a database (purpose for building mini-devs).  I built a script that allows the user to pass in begin, end and database values, but am running into issues with dynamic sql.  For instance, when removing records between certain dates, I get an operand clash.  The below shows an over simplified example of this:

DECLARE @b DATE, @e DATE, @cmd NVARCHAR(MAX)
SET @b = '2012-07-01'
SET @e = '2014-01-25'

SET @cmd = 'DELETE FROM DateTable
WHERE ValueDate BETWEEN ' + CONVERT(VARCHAR,@b,121) + ' AND ' + CONVERT(VARCHAR,@E,121)

EXEC sp_executesql @cmd

The error is Operand type clash: date is incompatible with int.  I tried doing an inner CAST within the dynamic sql since the error indicates that the ValueDate becomes and INT:

-- Only the WHERE clause changed:
WHERE ValueDate BETWEEN CAST(' + CONVERT(VARCHAR,@b,121) + ' AS DATE) AND CAST(' + CONVERT(VARCHAR,@E,121) + ' AS DATE)'

The error this time is Explicit conversion from data type int to date is not allowed.  Finally I tried playing with some other formats (from MSDN).  In a nutshell, I want to do exactly what the SO user was trying to accomplish, but be able to do it across multiple tables (I even searched for a script as I doubt I'm the first person to want this).


texts:
I know that instead of cursor we can have the tables updated with different queries but I wanted to try using cursor so as to have a knowledge on cursor
This was what I was trying I have inserted a new column which has null values instead of null I wanted to update that column based on the title column here what I used the query but I am unable to update the table can someone suggest a solution for this.

Declare @Title varchar(max)
Declare @Department varchar(max)

Declare Department_Cursor Cursor
For
    Select Title, Department
    from Sample_table

Open Department_Cursor
Fetch Next From Department_Cursor Into @Title, @Department

While @@fetch_status = 0
Begin
     Update sample_table
     Set @Department = 'Production'
     where @Title = '%Production%'

     Fetch Next From Department_Cursor Into @Title, @Department
End

Close Department_Cursor
Deallocate Department_Cursor


texts: Dynamic Data Masking Issue when Concatenating Fields

texts:
You can reproduce the issue here:
CREATE TABLE [dbo].[EmployeeDataMasking](
    [RowId] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeId] [int] NULL,
    [LastName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
    [FirstName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
 CONSTRAINT [PK_EmployeeDataMasking] PRIMARY KEY CLUSTERED
(
    [RowId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
) ON [PRIMARY]
GO

Insert Into dbo.EmployeeDataMasking (EmployeeId, LastName, FirstName)
VALUES( 1,'Smithsonian','Daniel'),( 2,'Templeton','Ronald')

Select
    EmployeeId,
    LastName,
    FirstName,
    LastName + ', ' + FirstName
From dbo.EmployeeDataMasking

Notice the LastName and FirstName fields are partially masked (as expected). However, the combined name field contains the default mask. I don't know if this is considered a bug. However, I would think the combined field would retain the mask of the two fields it comprises. At least that's what I would prefer, since I don't know how to provide a mask for the combined field.


texts: I have a table with both user_id and vote. They are stored together so each user can only give one vote. However it's also problematic because admins can simply read who voted what. Is there a way to make it so that only the system(minus all humans) and the user knows what was voted by h(im|er)self?


texts: Simplifying/Improving inner join (self joining a table)

texts:
I have table which has the following schema:

cid         name        type         notnull     dflt_value  pk
----------  ----------  -----------  ----------  ----------  ----------
0           DatasetID   VARCHAR(15)  0                       1
1           QuestionID  VARCHAR(20)  0                       2
2           PersonID    INTEGER      0                       3
3           answer      text         0                       0

And I have constructed a query that joins several answer rows into one, uses some of them, (age in this case) as filters:

SELECT i.answer AS graduation_year, j.answer AS education, k.answer AS country FROM

(SELECT * FROM answer
WHERE QuestionID = 'year'
AND answer <> ''
AND DatasetID = 'test') i

inner join answer j
    on i.PersonID = j.PersonID
    AND i.DatasetID = j.DatasetID
    AND j.QuestionID = 'education'

inner join answer k
    on i.PersonID = k.PersonID
    AND i.datasetID = k.datasetID
    AND k.QuestionID = 'country'

inner join answer m
    on i.PersonID = m.PersonID
    AND i.datasetID = m.datasetID
    AND m.QuestionID = 'age'
    AND CAST(m.answer as NUMERIC) > 25;

The output is following:

graduation_year  education   country
---------------  ----------  ----------
1995             Bachelors   Finland
1980             PhD eng.    Austria

I'm wondering if the same query can be accomplished in a more efficient way in terms of query time, or if it can be simplified. I'm currently using sqlite3 as the test database, but if there's a way to simplify the query in for example MySQL, that would also be an acceptable solution.

For reference, the answer table contains these values (where datasetID is 'test'), i.e. the results of the query SELECT * FROM answer WHERE datasetID = 'test';

DatasetID   QuestionID  PersonID    answer
----------  ----------  ----------  ----------
test        year        12345       1992
test        country     12345       Austria
test        age         12345       24
test        education   12345       M. Sc.
test        other       12345       boring...
test        year        123456      1995
test        country     123456      Finland
test        age         123456      26
test        education   123456      Bachelors
test        other       123456      meh
test        year        1234567     1980
test        country     1234567     Austria
test        age         1234567     35
test        education   1234567     PhD eng.
test        other       1234567     interesting


texts: Independent serial for each foreign key value

texts:
I have a table shared by many different users called players and a table called users.

Each player has the primary key id (serial) and a foreign key called owner_id which matches to users.id.

In order for users to update their players in my database (through my API) they must specify the same id as appears in the players table (the ids are visible to them!).

Because it's shared, the player ids that one user creates might look like:

1
2
12
56

(with the gaps being the id's used by players created by other users).

How can I create a new column called local_id which will behave like an independent serial on each owner_id value?

So each user would see the local_id as perfectly incrementing every time they create a new player?

1
2
3
4

Thanks for your help.


texts: Why does NULL ordering impact a btree scan in PostgreSQL?

texts:
In this answer jjanes says that NULL ORDERING contrary to the index declaration ordering can force a quick scan. Turns out he's right. I want to know why that is though. If you a btree with nulls last, or first why can't you skip over them?

CREATE TABLE foo AS
  SELECT x::int
  FROM generate_series(1,1e6) AS gs(x);

CREATE INDEX ON foo(x DESC NULLS FIRST);

ANALYZE foo;

With NULLS LAST,

SELECT * FROM foo WHERE x BETWEEN 100 AND 5000 ORDER BY x DESC NULLS LAST;

                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=414.95..426.19 rows=4494 width=4) (actual time=2.660..3.147 rows=4901 loops=1)
   Sort Key: x DESC NULLS LAST
   Sort Method: quicksort  Memory: 422kB
   ->  Index Only Scan using foo_x_idx on foo  (cost=0.42..142.31 rows=4494 width=4) (actual time=0.031..1.522 rows=4901 loops=1)
         Index Cond: ((x >= 100) AND (x <= 5000))
         Heap Fetches: 0
 Planning time: 0.265 ms
 Execution time: 3.633 ms
(8 rows)

Without NULLS LAST,

SELECT * FROM foo WHERE x BETWEEN 100 AND 5000 ORDER BY x DESC;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using foo_x_idx on foo  (cost=0.42..142.31 rows=4494 width=4) (actual time=0.040..1.611 rows=4901 loops=1)
   Index Cond: ((x >= 100) AND (x <= 5000))
   Heap Fetches: 0
 Planning time: 0.192 ms
 Execution time: 2.021 ms
(5 rows)

It does seem NULL ordering contrary to index declaration forces quickscan rather than index-only. Though I have no idea why.

It even happens if the column is NOT NULL,

ALTER TABLE foo ALTER COLUMN x SET NOT NULL;
VACUUM ANALYZE foo;
SELECT * FROM foo WHERE x BETWEEN 100 AND 5000 ORDER BY x DESC NULLS LAST;
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=477.64..490.43 rows=5113 width=4) (actual time=2.681..3.173 rows=4901 loops=1)
   Sort Key: x DESC NULLS LAST
   Sort Method: quicksort  Memory: 422kB
   ->  Index Only Scan using foo_x_idx on foo  (cost=0.42..162.69 rows=5113 width=4) (actual time=0.029..1.506 rows=4901 loops=1)
         Index Cond: ((x >= 100) AND (x <= 5000))
         Heap Fetches: 0
 Planning time: 0.225 ms
 Execution time: 3.662 ms
(8 rows)


texts:
As far as I understand, a one-to-one relationship between two tables means that each row in one table matches exactly one row in the other.

There a a number of reasons why this is useful, such as virtually adding columns to a table without actually altering it.

The above description would suggest that the relationship is symmetrical: a row in either table is matched by a row in the other.

This is readily done if the primary key is also a foreign key to the other:

CREATE TABLE stuff (
    id INT PRIMARY KEY REFERENCES more(id),
    data VARCHAR(255)
);

CREATE TABLE more (
    id INT PRIMARY KEY REFERENCES stuff(id),
    data VARCHAR(255)
);

I can see a logistic problem here: how can you add a row to one table when there isn’t a row in the other table to match?

A more relaxed version is a one-to-zero-or-one relationship, which I prefer to call a one-to-maybe relationship. This is easily implemented if one table references the other, but not the other way round:

CREATE TABLE stuff (
    id INT PRIMARY KEY, --  does not reference the more table
    data VARCHAR(255)
);

The relationship is still between primary keys, but only in one direction. This is logistically easier, and can be used to implement optional columns without getting into a fight about the use of NULLs.

The question is:

Does a one-to-one relationship really refer to the first case, where two tables match in both directions?
How would you go about adding a row in that case?


texts: how to dump a single stored procedure from a database

texts:
I need to create a dump file that contains a single stored procedure from a database, not all routines.

I've tried something like this:

mysqldump -u root -pssmart --skip-triggers --no-create-info --no-data \
    --no-create-db --skip-opt test_fshop SP_ITEM_SALES_REPORT -R \
    > test_fshop_routines.sql

Here test_fshop is my DB name and SP_ITEM_SALES is my SP. I want my SP as .sql file.


texts: Combine multiple queries that each require a row of input values

texts:
I'm looking for a way to combine multiple queries that each need two values.

For example, I can look up a patient's age and weight as follows:

select age
     , weight
  from patients
  join visits on patientID = patientNum
 where visits.patientNum = 1;

+------+--------+
| age  | weight |
+------+--------+
|   15 |   3.64 |
|   41 |      5 |
|  114 |   6.98 |

The problem is that I need to perform a query on each row to get their percentile; for example, taking the first row from above:

select age
     , percentile
     , abs(weight-3.64) as value
  from Weights
 where age = 15
 order by value
 limit 1;

+-----+------------+-----------------------+
| age | percentile | val                   |
+-----+------------+-----------------------+
|  15 |         39 | 0.0052000141143797585 |
+-----+------------+-----------------------+

The desired output would be:

+-----+------------+-----------------------+
| age | percentile | val                   |
+-----+------------+-----------------------+
|  15 |         39 | 0.0052000141143797585 |
|  41 |         83 | 0.0099000930786132810 |
| 114 |         90 | 0.0121998596191410510 |
+-----+------------+-----------------------+

I could trivially preform three queries for this example, but I need a way to do this all at once.


texts: Copy DBF files from production to development environment

texts:
I'm not an expert. I'm a Software Developer but got trapped in doing this DBA work.

We have a development environment and we would like to copy the DBF files from the production environment to the development environment to get the latest version of the production database.

I'm wondering if this will work?


texts: SQL Server - Overlapping NC Indexes, Drop Question

texts:
I'm doing some index cleanup/tuning on a SQL '14 DB and ran across a 6MM row, 16 field table with a sound-looking clustered index and two nonclustered, non-unique indexes having the following definitions:

IX_1 ON [Tbl1] ([field1])
IX_2 ON [Tbl1] ([field1], [field2]) INCLUDE ([field3], [field4])

The read/write data for the two indexes shows IX_1 currently with 5 million Seeks/Scans against it vs. 10k Seeks/Scans against IX_2, with equal updates for each.  My index tuning sense says to drop IX_1 without a second thought as it's fully covered by IX_2.  But the the drastic preference of the optimizer to use IX_1 spooks me a little.  Is there any reason to think twice about dropping IX_1 in a case like this?  Thanks.


texts:
Is it possible to change the user password in mysql in an interactive mode like it one can do in PostgreSQL for example using \password command?
My goal is not to provide the new password together with the command like it is described in mysql documentation about how to change the user password.


texts: Importing MySQL tables from other database in live site with mysqldump can cause trouble?

texts:
Scenario: I want to replicate MySQL tables from one database to other database.

Possible best solution: May be to use MySQL Replication feature.
Current solution on what I'm working as workaround (mysqldump) because can't spend time to learn about Replication in current deadline.

So currently I'm using command like this:

mysqldump -u user1 -ppassword1 --single-transaction SourceDb TblName | mysql -u user2 -ppassword2 DestinationDB

Based on some tests, it seems to be working fine.
While running above command, I run ab command with 1000 requests on destination site and tried accessing the site from browser also.

My concern is for destination live site on which we are importing data with whole table (which will internally drop existing table and create new one with new data).

Can I be sure that live site won't break while this process or is there any risk factor?
If yes then can that be resolved?


texts:
We have two servers. One we just compressed, but I took over this little project and this guy says some tables are not good candidates. As with many others, performance is -critical-.
Is it accurate to say that some tables shouldn't be compressed? If so, what is a general threshold to look for?
I ran his script to check for each objects reads/writes, but since this looks like a history, not what's occurred during a time frame, this might not be an accurate way to gauge that.


texts: upgrading Oracle forms and reports 6i to 12c

texts:
We are planning a migration of an Oracle Forms and report 6i application to 12c, and at the same time we will upgrade to the latest version of Oracle (from 10g to 12c).

I would like to know the detailed steps that we can follow, or from where to start?
Is there anything that we must consider before doing this?
Any problems that we might encounter?
How much time will it take?
Taking in consideration that we have Over 900 .fmb/fmx files and Over 700 .rep/rdf files.

Any information whatsoever would be helpful !


texts: Normalised database with one-to-one relationship

texts:
Is it bad practice to use a 1-1 relationship between two logically distinct tables: the user details and the user configuration settings? Each configuration record would have a single unique user ID, but they seemed separate enough to warrant two tables rather than one big one. I ask because the WikiBook for my school course said that I should only use 1-Many relationships.

In the User table I will be storing details such as their username, id and authorisation token for an external system. In the Config table I will be storing settings such as whether to show a welcome message, which are changeable through the settings page.


texts: Store table names to reference other tables

texts:
I'm designing a webapp where users can add widgets to their pages. Right now I have only two types of widgets that are different in nature, so they have different attributes, but both of them are used to sell products. I'm expecting to have other widgets implemented in the future. The users have the option of adding one or both of them to their pages, and I have to keep record of what widgets the users add. Also, for each product sold, I have to generate and store a receipt of the financial transaction. Since every sale is the same regardless of which widgets it comes from, I'd rather have only one Sales table for storing them (instead of one table for each widget type, eg: Widget1Sales, Widget2Sales, etc). And since there may be other widgets developed, I prefer not to make a sparse Widget table with columns for the attributes of all widgets.

So far I came up with the following design:

So for each widget the user add to his/her page, I have to make two queries: one to retrieve the specific widget table name and another (possibly a dynamic query) to get the widget info.The same goes for the products: for each product sold for any given widget I have to make a query to retrieve the specific product table name, and another to get the product info. This arrangement seems to work, but storing table names in another table sound just plain wrong to me... Is there any other way I could implement this logic? I've considered implementing some sort of class table inheritance, but I can't seem to envision how I could go about this in that specific use case.


texts:
Long story short; I have an HBase database that divided into a huge number of small regions. I'm trying to merge them into larger regions, as the huge number of regions is drastically slowing performance.

I'm looking for a simple way to automate the merges; I thought the simplest way would be trying to use the REST API to automate searching for neighboring regions and attempt online merges.

I'm having trouble finding examples of how to do this, though. Is there a proper REST endpoint that can do this?

If it makes a difference, there is a Cloudera 5x interface to the cluster.