texts: Pick one single row each time from either table?

texts:
I will run a query and get a list of aID and based on that I will try to match below tables.For each aID I just want one record either from tblGAlert or tblEAlert based on gDateTime and eDateTime depends on which time comes first.

CREATE TABLE IF NOT EXISTS `tblGAlert` (
  `gAlertID` int(11) NOT NULL AUTO_INCREMENT,
  `eID` int(5) NOT NULL,
  `aID` int(5) NOT NULL,
  `gEntryStatus` enum('Do','Ad','Ej') NOT NULL,
  `gDateTime` datetime NOT NULL,
  `gInsertDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `gMessage` varchar(255) NOT NULL,
   PRIMARY KEY (`gAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `tblEAlert` (
  `eAlertID` int(11) NOT NULL AUTO_INCREMENT,
  `eID` int(5) NOT NULL,
  `aID` int(5) NOT NULL,
  `eDateTime` datetime NOT NULL,
  `eInsertDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `eMessage` varchar(255) NOT NULL,
  PRIMARY KEY (`eAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Sample data I say for tblGAlert

1,12,1122,'Do',2013-07-13 14:30:19,2013-07-13 15:30:19,''
2,13,1122,'Ad',2013-07-13 14:35:19,2013-07-13 15:35:19,''
3,13,1122,'Ad',2013-07-13 14:38:19,2013-07-13 15:39:19,''
4,14,1122,'Ej',2013-07-13 14:45:19,2013-07-13 15:55:19,''
5,14,1122,'Ej',2013-07-13 14:50:19,2013-07-13 15:56:19,''

Same data for tblEAlert.

1,1,1122,2013-07-13 14:33:19,2013-07-13 15:35:19,''
2,5,1122,2013-07-13 14:36:19,2013-07-13 15:36:19,''
3,6,1122,2013-07-13 14:37:19,2013-07-13 15:39:19,''
4,7,1122,2013-07-13 14:48:19,2013-07-13 15:55:19,''
5,8,1122,2013-07-13 14:52:19,2013-07-13 15:56:19,''

The output I want to merge them together in such a way arrange by the gDateTime and eDateTime. Any idea how to merge them ?

I have tried below script but the answer I get is not right and it hogs my db server.

SELECT X . *
FROM (

SELECT gAlertID,  'gType' AS PTYPE, gDateTime AS DATE
FROM tblGAlert
WHERE tblGAlert.aID =2494
UNION
SELECT eAlertID,  'eType' AS PTYPE, eDateTime AS DATE
FROM tblEAlert
WHERE tblEAlert.aID =2494
)X
ORDER BY X.`date`


texts: How to Reduce Mysql Size After Dropping Table

texts:
I dropped a corrupted table with large size to reduce the server size.

But the server size still the same!

I entered the MYSQL location in my server and found a .ARN file with the name of the dropped table with 28GB size.

I tried to optimize the table after dropping it, but sure its not exist

Is it safe to remove .ARN file through the command line ?

Or what can I do to free this 28GB?


texts: Why - read but not update ops fail in case of lacking space in tempdb database?

texts:
MSDN online article "Choosing Row Versioning-based Isolation Levels" states:

"tempdb must have enough disk space for the version store. If there are very long-running transactions, all the versions generated by update transactions during the time must be kept in tempdb. If tempdb runs out of space, update operations do not fail, but read operations using row versioning might fail"

Can you explain me why?
taking into account that (quoting the same article):

"When either the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database options are ON, update and delete transactions for a particular database must maintain row versions even when there are no transactions using a row versioning-based isolation level... Because the record versions are stored in tempdb"

that is, update (but not read) operations require space for row versioning should have IMO failed due to lack of space.
And why do read ops fail if they do not require version storage in tempdb ?


texts: Postgres COPY command gets Error: out of memory

texts:
When copying a large amount of data (around 9GB) from CSV file into table, I get following error:

ERROR:  out of memory
 DETAIL:  Cannot enlarge string buffer containing 1073741632 bytes by 349 more bytes.
 CONTEXT:  COPY column_name line 13275136

A server (postgresql 10) has 8GB of memory and database has shared_buffers set to 2GB.

Can somebody suggest a solution, please. Or problem is instead in column_name length ( according to CONTEXT:  COPY column_name line 13275136 message)


texts:
I have a question, I am managing 2 SQL servers

is Master Server (Publisher + Distributor)
is Client Server (Subscriber)

The client is basically owned by a client who is buying data from us.

Both Servers are replicated with each other using Transactional-replication.
Only selected Tables of a single database are replicated from the Master Server to Client Server.

Basically there are several problems i want to discuss.

Problem 1:
When I add or delete any data table and take snapshot of replication then all databases vanish from customer side and after that it rebuilds in 20-30 minutes (I only have limited access so don’t get logs)

Problem 2:
When I reinitialize subscription then it takes a lot of time to completing it and it is showing partially applied scripts of tables in monitoring.

Problem 3:
Publisher to Distributor showing high commands and transactions count but actually on distributor to subscriber commands and transaction counts are more than 100’s of times lower as compare to publisher section.

Problem 4:
Latency is Very high between Publisher-Distributor (around 5+ minutes)(Server-1) and jumps to 7 minutes then automatically less and stay on some seconds but average is really very high. Distributor-Subscriber latency seems fine.

Problem 5:
Log Size of Master DB increases and occupying space around 3.5 GB (Normally 3 GB with 80-87% free space) whether Data File Size also increases and now it’s 4 GB+ (Normally 3.3 – 3.5 GB)


texts: Database backups in Oracle - Export the database or use other tools?

texts:
One of the "practices" that I've seen about being performed by DBA's in my organization is to treat a full database export using tools like exp / expdp as backup.

Would this be a good practice ? What would be the advantages of using RMAN over this approach ?


texts: Is it possble to replicate MySQL server on Azure virtual machines

texts: I have two Windows Server 2012 R2 virtual machines on Azure. I need to replicate the database on each of the windows servers. I have followed this link but I am not getting the connection between the two MySQL servers. It is showing as connecting to master. Is there any other way to replicate MySQL databases on two different windows servers?


texts: Lots of short term blockings on SQL Server 2008-R2 database

texts:
Seeking experts advise on short term blocking which we are seeing for almost a week now.

DB is 15 TB and this Table B where we have lots of blocking - almost 300 in no is 2 TB.

While using sp_whoisactive,  I see queries getting blocked for shorter fraction of 3-4 secs and some in 9-10 secs but because these come and go almost through the day for connections over 2-3 thousand, this causes lots of errors in application.

Troubleshooting done:

Updated stats on NC index, We just have one NC and one C index.
While checking from sentry we can see that these blocked statements
are all insert statements for that table mentioned above.

Below are some metrics:

Wait stats for past 3-4 hours where blocking is till on

PAGELATCH_EX --> 51.1 % Total waits 730 513 153.5
PAGELATCH_SH  --> 23 % Total waits 343 538 769.7
THREADPOOL --> 18 % Total waits 257 222 894.4

BATCHES/sec on Avg: 8612.6
CPU and memory --> well under the threshold as we have 256 logical processors and 612 GB RAM
CHECKPOINT pages/sec --> 1706
Lazy writes --> 0
Log flushes/sec 1844.4
Transactions/sec 9074

results as per david's query: Note@ below was run 15 mins after we did node failover

 wait_type  wait_time_ms_per_sec
PAGELATCH_EX    733024.4
PAGELATCH_SH    328846.4
CPU_USED    7131.4
WRITELOG    6484.4
DBMIRROR_EVENTS_QUEUE   4973.6
DBMIRRORING_CMD 1456.6
OLEDB   990.2
TRACEWRITE  984.2
SOS_SCHEDULER_YIELD 280.6
THREADPOOL  254
ASYNC_NETWORK_IO    45.4
LATCH_EX    9.4
PAGEIOLATCH_EX  7.6
PREEMPTIVE_OS_GETPROCADDRESS    3.4
MSQL_XP 3.2
DBMIRROR_SEND   1.4
PREEMPTIVE_OS_DELETESECURITYCONTEXT 1
PAGELATCH_UP    0.4
PREEMPTIVE_OS_WAITFORSINGLEOBJECT   0.4
PREEMPTIVE_OS_AUTHORIZATIONOPS  0.2
PREEMPTIVE_OS_AUTHENTICATIONOPS 0.2
SOS_RESERVEDMEMBLOCKLIST    0.2
CMEMTHREAD  0.2

Please provide additional troubleshooting suggestions and let me know if there are any further metrics needed.

Edit@ We are using MAXDOP 8 and Cost threshold of par'sm is set default-5

Update: Did not made any changes as such and when we saw drop in connections during the night time, suddenly issue seem to have vanish and no blocking as checked for now. Below is the stats as of now

OLEDB   11883.8
WRITELOG    11732.2
DBMIRROR_EVENTS_QUEUE   4969.8
CPU_USED    4093.75
DBMIRRORING_CMD 1457.2
TRACEWRITE  1080.2
PAGEIOLATCH_SH  333.4
PAGELATCH_EX    237.4
PAGELATCH_SH    31.4
ASYNC_NETWORK_IO    30.2
SOS_SCHEDULER_YIELD 6
LATCH_EX    4.4
DBMIRROR_SEND   1.6
PREEMPTIVE_OS_AUTHENTICATIONOPS 1.4
MSQL_XP 1.2
PREEMPTIVE_OS_GETPROCADDRESS    1.2
PAGELATCH_UP    0.4
PREEMPTIVE_OS_DECRYPTMESSAGE    0.4
SOS_RESERVEDMEMBLOCKLIST    0.4
PREEMPTIVE_OS_DELETESECURITYCONTEXT 0.2
PREEMPTIVE_OS_WAITFORSINGLEOBJECT   0.2
CMEMTHREAD  0.2
PREEMPTIVE_OS_AUTHORIZATIONOPS  0.2
PREEMPTIVE_OS_CRYPTACQUIRECONTEXT   0.2
PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY 0.2

Not sure how and why it went as when seeing today we dont see those many connections and neither the blocking. But thats for sure it will pop up soon

Update@ Its back and looks with same waits


texts: Query to see Index Fragmentation on all databases without using SP or temp tables

texts:
I am trying to compile a index fragmentation report on all databases, in a multi-server environment. The query will be executed via an openquery command, so I would like to make it as simple as possible, without using stored procedures, temporary tables, etc.

I am aware the follow can bring back all indexes on all databases

select * from sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'SAMPLED')

but as soon as we join it to sys.indexes, sys.objects, we restrict out system wide indexes and rather focus on indexes in the currently executing database.

Is there a way around this, so I can get the following output for all databases:

Database Name, Index Name, Index Type, Number of Pages, Percentage of Fragmentation.


texts: My connection to a Mysql DB has been blocked after spamming queries, for how long?

texts:
I am creating a web application that depends on querying a legacy DB without an API.

It's MySql.

Off course the lack of an API eventually got me in trouble. A feedback loop while testing the app fired a bunch of (Probably a few hundred) get requests towards the database, and now i can't connect to it anymore, it simply times out.

I horrified tried to log into to it via the browser and PHPmyAdmin, and to my relief nothing was wrong, but in Status->Processes i can see this process:

Kill    25289014    myusername  localhost   None    Query   0   ---

I reckon the DB killed all incoming connections from my user when i began to spam it from my application.

Do any of you guys know if the "block" will be lifted after a certain amount of hours. It's about 12 hours ago now.

Or if i have to contact the administrator to lift the block, or kill the kill process? My user doesn't seem to have the privilege to kill the process as there is no buttons to do so.

Thanks in Advance.


texts: Trying to recover a database with just .ibd and .frm files, but no idbata1 file

texts:
Trying to restore a WAMP database with just .frm and .ibd files. The uninstall of WAMP left .ibd and .frm files, but no ibdata1 file. I have read several posts here to figure out the process for doing this.

I read this post:  Recover MySQL database from data folder without ibdata1 from ibd files . I also got advice to bring in a ibdata1 file in from a fresh WAMP MySQL installation . (My old WAMP was a 5.6.12, this new one installed 5.6.17). I ran the .frm files through a utility at TwinDB: https://recovery.twindb.com/ which I understand helps get the schema restored.

The instructions are this: to run commands through a MySQL utility after copying .ibd files to the data level folder, then moving the .ibd files back to the database level. I assume the .frm files that were run through TwinDB's utility are to replace the existing .frm files in my database folder. There are eleven .ibd files to run through this process.

My hope when I'm done is that my 5.6.12 installation will be legitimate, and that WAMP's tray menu will recognize my 5.6.12 installation once again. I have several years of archived posts there that I want to re-use again online.

I downloaded the MySQL Utilities (1.5.6) and installed them. I've read here that I should be using mysql.exe. But that file is not in the folder that was installed. I'm stuck. There are 24 .exe files in that folder. The READ ME file gives me no instructions. Which utility am I supposed to use if mysql.exe is not in there? Why didn't the installation of the Utilities create an app that opens up all these tools?

My database is located at C:\wamp\bin\mysql\mysql5.6.12\data\wordpress. I know I need that path to log in. What is my command to log into this server path so I can run my commands and restore these connections between my eleven .ibd files?
My database user is root. There is no password since this is an internal server.

I've read that I need to log in to mysql so I can run these commands to put the database info in the directory with the ibdata file so that everything can "connect" again. The .ibd files have all the data for the internal WordPress site. Their files sizes are clearly indicative that all the data is there.


texts:
We have a three jobs scheduled on production server. Full backup, Differential backup and Transaction Log backup. The first two backup successfully, but the Transaction Log backup is failing. It shows below error.

  Date      1/23/2019 4:00:00 AM

  Log       Job History (JOB_NAME)

  Step ID       1

  Server        SERVER_NAME

  Job Name      (JOB_NAME)

  Step Name     1

  Duration      00:00:01

  Sql Severity  16

  Sql Message ID    3013

  Operator Emailed

  Operator Net sent

  Operator Paged

  Retries Attempted 0

  Message

  Executed as user: NT SERVICE\SQLAgent$MSSQLSERVER_2016. Cannot open
  backup device 'BACKUP_PATH\database_Trans_4.trn'. Operating system
  error 32(The process cannot access the file because it is being used
  by another process.). [SQLSTATE 42000] (Error 3201)  BACKUP LOG is
  terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step
  failed.

I have edited the message to hide the server details for security reasons.

The Backup script used is

Declare @number nchar(2)
set @number=isnull((select max(ID) from database.dbo.Backup_Transactionlog),0)+1

declare @path1 varchar(max)
set @path1=N'E:\DB_Backup\database_TransactionalLog_Backup\database_Trans_' + ltrim(rtrim(@number)) +'.trn'

BACKUP LOG [database]
TO  DISK = @path1
WITH INIT

insert into database.dbo.Backup_Transactionlog
values (@path1,getdate())

After doing lots of research i found a solution. I changed the WITH INIT Option of the backup syntax with WITH FORMAT. But still the backup is failing.

Could any one please provide any solution that would make my day.

Thanks.!!


texts:
I have a Postgres 9.6 database with several schemas, and a regular backup of this database is done with a command like

pg_dump -h localhost -d db -U postgres -Fc -f file.dump

When I restored a backup I got the unexpected result that for one of the schemas the contents were missing entirely, only the tables were there, but empty. This affects all tables in that schema. The other schemas contained all the expected data when restoring from this backup.

To confirm that the data isn't in the backup I used the following command to create a readable text version of the binary database dump:

pg_restore mydb.dump > mydb.sql

I looked for the entry that is supposed to fill in the rows and found something like this:

--
-- Data for Name: some_table; Type: TABLE DATA; Schema: schema1; Owner: postgres
--

COPY schema1.some_table (column1, column2, column3, column4) FROM stdin;
\.

There was no data inserted at all with the COPY command here, and this entry looks similar for every table in this particular schema. When I look at the other schemas, there are the individual rows to be inserted in the COPY command as I would expect.

The schemas all have the same tables, but there is one difference that is possibly connected to the source of the problem. The schema with the missing data in the backup is owned by postgres, while all other schemas are owned by other roles. Each schema is supposed to be owned by a specific role, one role per schema. That the problematic schema is owned by postgres means there was an earlier bug or issue that caused the wrong ownership, but my understanding is that as I'm making the backup as the postgres superuser, the wrong ownership alone can't explain the missing data in the backup.

I'm now looking to figure out what happened here, and why the data is missing. Right now I don't have any explanation of how just this specific data could be missing in the database dump, when the dump itself is successful.

Is the way I'm transforming the binary dump valid, and do my observations on the plaintext dump mean that there is no data for this table in the dump?
Is there any way the ownership could affect the data contained in the dump when performing pg_dump as a role with SUPERUSER privilege?
What are the possible explanations for the missing contents of only one specific schema when dumping a whole database?


texts: Dealing with NULL values and EMPTY strings in UNION of two tables

texts:
I want return records from two database tables when a record differs in at least one of the fields.

I have written a query for it using the UNION operator.  It is working absolutely fine.

However, I want to treat NULL and '' (empty string) as the same value.

For example, all entries have a field Email-ID having value NULL in one table and '' in the other table.

Using the query I have, it is showing these two are different.  I want these two values to be treated the same.  I have around 15 such fields.

How do I do this?


texts:
I have a table structure like below:

CREATE TABLE `sample` (
  `id` int(11) NOT NULL,
  `is_duplicate` tinyint(1) NOT NULL DEFAULT '0',
  `duplicate_with` int(11) NOT NULL,
  `name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `sample` (`id`, `is_duplicate`, `duplicate_with`,`name`) VALUES
(1, 0, 0,'ABC'),
(2, 0, 0,'PQR'),
(3, 1, 1,'XYZ'),
(4, 1, 2,'PPP');

The is_duplicate column is an indicator of whether the row is a duplicate of another row (1) or is a master row (0). If it is a duplicate, the duplicate_with column will hold the ID of the master row of which this one is a duplicate.

Now I want output like below:

3  1  1  XYZ
1  0  0  ABC
4  1  2  PPP
2  0  0  PQR

I want to list rows in such a way that duplicates of the same master row are arranged together and followed by their master row, while the groups themselves should be sorted by master row ID.

In the above example, there are two row groups. The first group includes row 3 and its master row, row 1, which goes after its duplicate. In the same way, the second group consists of rows 4 and 2, row 2 being the master row and following its duplicate.

How can I achieve such a result?


texts: Sql Statement to display multiple true statement

texts:
I have a sql statement that checks for certain special characters in a string and returns that.

SELECT

CASE
WHEN (LEN(DSC1) - CHARINDEX(char(1), DSC1)) <> LEN(DSC1) THEN '[DSC1 - NUL (null)], '
WHEN (LEN(DSC1) - CHARINDEX(char(2), DSC1)) <> LEN(DSC1) THEN '[DSC1 - SOH (start of heading)], '
WHEN (LEN(DSC1) - CHARINDEX(char(3), DSC1)) <> LEN(DSC1) THEN '[DSC1 - STX (start of text)], '

END  [Special Character]

The issue with below is that if the string has more than one special character, it just lists the first one and not the other as I guess the case statement breaks as soon as it finds the first match.

How do write that it lists all that it finds. e.g. if the string under DSC1 has both char(1) and char(2), then it will return

  Special Character

  [DSC1 - NUL (null)], [DSC1 - SOH (start of heading)],


texts: TSQL change column constraint between null and not null

texts:
I am doing a exercise to change a column's constraint between null to not null.
Table name: Production.categoriesTest. Please review my question and give me suggestion
on what I did wrong and how to fix the issue. Thanks!

when table was created, the description column is set to NOT NULL
I used the update clause to set description column to NULL
When using alter table command to set the column description back to NOT NULL, I received an error message.

Error message:

  Msg 515, Level 16, State 2, Line 98
  Cannot insert the value NULL into column 'description', table 'TSQL2012.Production.categoriesTest'; column does not allow nulls. UPDATE fails.

Create table command:

 create table Production.categoriesTest
    categoryid INT not null identity,
    categoryname nvarchar(15) not null,
    description nvarchar(200) not null,
    constraint PK_Categories1 Primary Key (categoryid)

--update clause make all description column becomes 'NULL"
  update Production.categoriesTest
  set description = NULL
  where categoryid = 8;

The description column became 'NULL'

I did insert a value for description column in the able after the column set to Null.


texts: Performance getting rows with max column

texts:
I have an historic table called 'historic', with three columns: object_id, day, value.
For historical reason, day(date) is an integer number (number of day since 01/01/1904)
Example:

object_id | day | value
------------------------
1         |  5   | blue
1         |  7   | red
1         |  8   | green
2         |  4   | blue
2         |  11  | green
3         |  6   | blue

I want to get all value for a max-date before a specified date:
For example, as of 'day <= 7', I want this result:

object_id | day  | value
------------------------
1         |  7   | red
2         |  4   | blue
3         |  6   | blue

I have tried several option:

Subquery

select
*
from historic H1,
  (
  select max(day) as date,
  object_id
  from historic
  where
    day <= :my_date
  group by object_id
  ) H2
where
  H1.day = H2.day
  and
  H1.object_id = H2.object_id
order by h1.object_id

Execution plan

| Id  | Operation               | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                        |     8 |   608 |     7  (29)| 00:00:01 |
|*  1 |  FILTER                 |                        |       |       |            |          |
|   2 |   SORT GROUP BY         |                        |     8 |   608 |     7  (29)| 00:00:01 |
|*  3 |    HASH JOIN            |                        |     8 |   608 |     6  (17)| 00:00:01 |
|*  4 |     INDEX FAST FULL SCAN| HISTORIC_PK            |     4 |   104 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL   | HISTORIC               |     6 |   300 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

   1 - filter("H1"."DAY"=MAX("DAY"))
   3 - access("H1"."OBJECT_ID"="OBJECT_ID")
   4 - filter("DAY"<=7)

Analytic function

SELECT * FROM (
  SELECT
      object_id,
      value,
      day,
      RANK() OVER (PARTITION BY object_id ORDER BY day DESC) d_rank
    FROM historic where day <= 7
  ) where d_rank = 1

Execution plan

------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                     |     4 |   204 |     4  (25)| 00:00:01 |
|*  1 |  VIEW                    |                     |     4 |   204 |     4  (25)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|                     |     4 |   152 |     4  (25)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL     | HISTORIC            |     4 |   152 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

   1 - filter("D_RANK"=1)
   2 - filter(RANK() OVER ( PARTITION BY "OBJECT_ID" ORDER BY INTERNAL_FUNCTION("DAY")
              DESC )<=1)
   3 - filter("DAY"<=7)

But performance is poor. What is the fastest way to get this result ?
(Real table has 100.000.000 rows)


texts: Referencing a value of a prior column in a default expression in xmltable()

texts:
I'm trying to reference a preceding column c1 in a default value of another column c2 in the xmltable() function, but I get an error: column c1 does not exist.

SELECT * FROM xmltable(
    '.' PASSING '<r/>'
    COLUMNS
    c1 Text PATH 'name()',
    c2 Text DEFAULT c1
);

I wonder why, as this precise usage is permitted by the documentation (emphasis's mine):

  If the path expression does not match for a given row but default_expression is specified, the value resulting from evaluating that expression is used. If no DEFAULT clause is given for the column, the field will be set to NULL. It is possible for a default_expression to reference the value of output columns that appear prior to it in the column list, so the default of one column may be based on the value of another column.


texts: Removing Orphan Pull merge replication subscriptions

texts:
I have a merge replication set where I have removed one of the pulled subscriptions. On the subscriber the subscription shows up after deletion. It will not go away. I tried the methods provided on many forums but cannot seen to get this to go away and let me recreate. Any suggestions would be much appreciated

  SQL Server could not create a subscription for Subscriber ''.

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  You must first drop all existing merge publications to add an
  anonymous or local subscription to database '' Changed database
  context to ''. (Microsoft SQL Server, Error: 21258)


texts: Combine Table Hints INDEX and FORCESEEK with Two Joins Not On PK

texts:
See first query below.
Can NOT combine table hint Index and forceseek with two joins and the joins are not on the PK.
How to make the first query compile?

Interesting
-  if just one join or the other then can combine index and forceseek hints
-  if the index is the PK then can combine 2 joins and have both hints

-- compiler fails
-- Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
Select Count(Distinct([docSVsys].[sID]))
From [docSVsys] with (nolock)
Left Join [docSVtext] with (nolock, Index(IX_docSVtext_value_sID), forceseek )
       On [docSVtext].[sID] = [docSVsys].[sID]
Left Join [docMVtext] with (nolock, Index(ix_docMVtext_value_sID), forceseek)
       On [docMVtext].[sID] = [docSVsys].[sID]
where [docSVtext].[value] = 'doug'  or
      [docMVtext].[value] = 'doug'

--  can do one join
Select Count(Distinct([docSVsys].[sID]))
From [docSVsys] with (nolock)
Left Join [docSVtext] with (nolock, Index(IX_docSVtext_value_sID), forceseek )
       On [docSVtext].[sID] = [docSVsys].[sID]
where [docSVtext].[value] = 'doug'

--  can do the other join
Select Count(Distinct([docSVsys].[sID]))
From [docSVsys] with (nolock)
Left Join [docSVtext] with (nolock, Index(IX_docSVtext_value_sID), forceseek )
       On [docSVtext].[sID] = [docSVsys].[sID]
where [docSVtext].[value] = 'doug'

-- if on the PK then can do forceseek on two join
Select Count(Distinct([docSVsys].[sID]))
From [docSVsys] with (nolock, INDEX(PK_docSVsys))
Left Join [docSVtext] with (nolock, Index(PK_docSVtext), forceseek )
    On [docSVtext].[sID] = [docSVsys].[sID]
Left Join [docMVtext]  with (nolock, Index(PK_docMVtext), forceseek)
    On [docMVtext].[sID] = [docSVsys].[sID]
where [docSVtext].[value] = 'doug'
   or [docMVtext].[value] = 'doug'

This does what I want and runs 100 times faster.
This has almost an identical query plan to the answer from ypercube.
But I cannot combine this with some other conditions I need.

Select count(distinct([docSVsys].[sID]))
From [docSVsys] with (nolock)
LEFT HASH JOIN [docSVtext] with (nolock)
  on [docSVtext].[sID] = [docSVsys].[sID]
 and [docSVtext].[value] = 'doug'
LEFT HASH JOIN [docMVtext] with (nolock)
  on [docMVtext].[sID] = [docSVsys].[sID]
 and [docMVtext].[value] = 'doug'
where [docSVtext].[sID] is not null
   or [docMVtext].[sID] is not null

This also uses the proper indexes and is the fastest.
For now I don't have the option to rewrite the format.
The program builds up queries to other tables.
We incorrectly wanted the same format for where as an order by and that got us where we are.

SELECT  count(distinct(c.sID))
from
(   SELECT sv.[sID]
    FROM [docSVtext] AS sv
    WHERE sv.[value] = 'doug'
    UNION ALL
    SELECT mv.[sID]
    FROM [docMVtext] AS mv
    WHERE mv.[value] = 'doug'
) as c

docSVsys
sID Int PK Identity

docMVtext
sID Int PK FK to docSVsys
fieldID Int PK
value string PK
ix  value, sID

docSVtext
sID Int PK FK to docSVsys
fieldID Int PK
value string
ix  value, sID

MV is a table for multi-value text
SV is a table for single-value text
fieldID is for unique fields
This is a search of all SV and MV fields


texts: Our business runs on Windows Server 2012r2, and we're interested in exploring use of SQL Server in Docker containers for Dev and QA support.  I've looked at Microsoft's new images for Windows Server 2016, but see the two Express images (2014 and 2016) have been deprecated in favor of the vNext image.    Has anyone heard what MSFT's plans are for SQL Server support, or if they provide support for Windows Server 2012?


texts:
I have a table that looks roughly like this:

create table people (
    id bigserial primary key,
    name varchar,
    deleted timestamp with time zone,
    parent bigint references people(id)
        on delete restrict
        on update cascade
)

I would like to do something like

delete from people where name = $1
on conflict people_parent_fkey do
update set deleted = now() where name = $1

but I don't think that this is supported.

Is there an easy way to do this?


texts: SELECT in 2 merged 3-Tables relation with IN() clause and a COUNT for each relation

texts:
In a 3-Tables relation Query, I have this following two Queries for each relation:

http://sqlfiddle.com/#!9/0bcc34/3/0

SELECT d.`id`, COUNT(da.`doc_id`)
FROM `docs` d

LEFT JOIN `docs_scod_a` da ON da.`doc_id` = d.`id`
LEFT JOIN `scod_a` a ON a.id = da.`scod_a_id`

WHERE a.`ver_a` IN ('AA', 'AB')

GROUP BY d.`id`;

| id | COUNT(da.`doc_id`) |
|----|--------------------|
|  1 |                  2 |
|  2 |                  1 |
|  3 |                  2 |

http://sqlfiddle.com/#!9/d89a4e/1/0

SELECT d.`id`, COUNT(db.`doc_id`)
FROM `docs` d

LEFT JOIN `docs_scod_b` db ON db.`doc_id` = d.`id`
LEFT JOIN `scod_b` b ON b.id = db.`scod_b_id`

WHERE b.`ver_b` IN ('BA', 'BB')

GROUP BY d.`id`;

| id | COUNT(db.`doc_id`) |
|----|--------------------|
|  1 |                  2 |
|  2 |                  1 |
|  3 |                  2 |

What I want to Do is to merge both Queries into a Single one That will not only use (3-Tables)x2 only, So I've tried the following Query:

http://sqlfiddle.com/#!9/1d2954/2/0

SELECT d.`id`, COUNT(da.`doc_id`), COUNT(db.`doc_id`)
FROM `docs` d

LEFT JOIN `docs_scod_a` da ON da.`doc_id` = d.`id`
LEFT JOIN `scod_a` a ON a.id = da.`scod_a_id`

LEFT JOIN `docs_scod_b` db ON db.`doc_id` = d.`id`
LEFT JOIN `scod_b` b ON b.`id` = db.`scod_b_id`

WHERE a.ver_a IN ('AC', 'AB') AND b.ver_b IN ('BA', 'BB')

GROUP BY d.`id`;

What I expected to get from this Query was the following results:

| id | COUNT(da.`doc_id`) | COUNT(db.`doc_id`) |
|----|--------------------|--------------------|
|  1 |                  2 |                  2 |
|  2 |                  1 |                  1 |
|  3 |                  2 |                  2 |

But Instead of That I got

| id | COUNT(da.`doc_id`) | COUNT(db.`doc_id`) |
|----|--------------------|--------------------|
|  1 |                  4 |                  4 |
|  3 |                  2 |                  2 |

Is the Problm on the COUNT only or Am I using the wrong method to SELECT data from 3-Tables Relation?

So in Berif: How can I select data from 3-Tables Relation, And merge them together, Then COUNT result of each relation,

In the end I'll SUM the count for each row into one, So the final result expection overall after all of this is supposed to be

| id | SUM(COUNT(da.`doc_id`) + COUNT(db.`doc_id`))|
|----|---------------------------------------------|
|  1 |                                           4 |
|  2 |                                           2 |
|  3 |                                           4 |


texts: How to disable TRUNCATE on whole database or for particular table?

texts: Is it possible to disable TRUNCATE operation on the entire PostgreSQL database, or on a specific table?


texts: Cannot set ArithAbort to Off on SQL Server

texts: I have 2 SQL 2012 servers. The settings are exactly the same, but on one I can set ARITHABORT to Off but no matter how I try I can't do the same on the second server. The only difference I could find was that the server I could set it on is an older version of SQL 2012. Has something changed? Or is there something else I'm missing?


texts: How to implement database mirroring using SQL on two geographically separate servers

texts:
I need to implement database mirroring on two geographically separate servers.

I have been tasked with ensuring that there should be a backup of the databases that is not more than 1 day behind the live databases.

This is a link of a sketch of what I currently have installed on the servers and how the servers are connected via a WAN. My question is, given my current configuration.

What do I need in order to make mirroring possible? Essentially, what do I need to purchase or upgrade in order to make mirroring possible. Thanks in advance.


texts: Move all InnoDB databases into one subdirectory (innodb_file_per_table is ON)

texts:
First of all, excuse my English.

I want to run a MySQL 5.6 on ZFS volume, and need to move all InnoDB databases to one subdirectory (which in fact will be a different ZFS filesystem). For example, by default mysql/ directory look like:

mysql/
mysql/my.cnf
mysql/ib_logfile0
mysql/ib_logfile1
mysql/ibdata1
mysql/database1/
mysql/database2/
...

and I want to make it like:

mysql/
mysql/my.cnf
mysql/ib_logfile0
mysql/ib_logfile1
mysql/db/ibdata1
mysql/db/database1/
mysql/db/database2/
...

Is it possible?


texts: Pass additional data in connection for use by PGSQL Trigger

texts:
I would like to add some auditing triggers to PG, to log all changes to tables.  I have found an example trigger ( wiki.postgresql.com - 9.1+ audit trigger ), which gets me started.

What I would like to do is pass along some additional data automatically, such as the user-id or other key, so that the trigger can link the change back to the application user that made it. The basic flow would go like this:

Get a connection for the current request
Run custom SQL to set a context variable (set conn_context = {userid};)
Read / parse that variable inside the trigger
Write the value to a log table along with the other information

When using MS SQL I was able to execute a statement that set the context for the connection by executing SET CONTEXT_INFO ...data....  I am hoping there is a way to do this for PG; I haven't found one yet, but that may just be bad google-fu.


texts:
SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 20 13:08:07 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Enter user-name: system
Enter password:
ERROR:
ORA-28002: the password will expire within 4 days

Every time I login into my database it shows this warning message of password gonna expire in .. days. Now its just 4 days left I don't know what to do to fix this warning.


texts: SQL Server 2016 Restore Failure on DB Using Clustered Shared Volumes (Error 7627)

texts:
We are new to using clustered shared volumes and SQL Server 2016 Enterprise Edition.

When restoring SQL Server 2005 databases with full text search catalogs onto our new 2016 instance, we get the following SQL Server error:

  Msg 7627, Level 16, State 1, Line 2
  Cannot create the full-text catalog in the directory C:\ClusterStorage\Volume2\SQLData\LogBook for the clustered server. Only directories on a disk in the cluster group of the server can be used.

We have restored this same database to a SQL Server 2016 without issue using:

USE [master];
RESTORE DATABASE [MYDATABASE]
FROM DISK = N'\\BACKUPSERVER\BACKUPS\DATABASE\Database_backup_201805070600.bak'
WITH FILE = 1,
     MOVE N'DatabaseData'
     TO N'C:\ClusterStorage\Volume2\SQLData\Database.mdf',
     MOVE N'DatabaseLog'
     TO N'C:\ClusterStorage\Volume1\SQLTranLogs\Database_log.ldf',
     MOVE N'sysft_LogBook'
     TO N'C:\ClusterStorage\Volume2\SQLData\LogBook',
     NOUNLOAD,
     REPLACE,
     STATS = 5;

Have any of you ran into this using clustered shared volume instead of just a plain volume?

Here are some shots of the setup on the cluster using CSVs.

Using the old method on shared volumes our dependencies looked like this example:

Now using clustered shared volumes which are using SMB they look like this:


texts: Performance Metrics be on Own Server or Own Server Instance

texts: We would like to collect SQL Server performance metrics (memory, Cpu, page writes/sec, batches/sec,active sessions), etc. I heard its a rule not to collect Sql server metrics on the live Financial Server itself. Place it somewhere else. Should the monitor metrics be on a Different server instance or be on a totally different server? Should I consider Sql server standard, or anything lower?


texts: Does a computed column break 3NF (third normal form)?

texts:
From my understanding of 3NF, there should not be any transitive dependency.

However, this is what I found from the AdventureWorks2012 database Production.WorkOrder table. Since StockedQty is dependent on OrderQty and ScrappedQty, there is a transitive dependency in this table, am I right?

The DDL for the aforementioned table is as follows:

CREATE TABLE [Production].[WorkOrder](
        [WorkOrderID] [int] IDENTITY(1,1) NOT NULL,
        [ProductID] [int] NOT NULL,
        [OrderQty] [int] NOT NULL,
        [StockedQty]  AS (isnull([OrderQty]-[ScrappedQty],(0))),
        [ScrappedQty] [smallint] NOT NULL,
        [StartDate] [datetime] NOT NULL,
        [EndDate] [datetime] NULL,
        [DueDate] [datetime] NOT NULL,
        [ScrapReasonID] [smallint] NULL,
        [ModifiedDate] [datetime] NOT NULL,
     CONSTRAINT [PK_WorkOrder_WorkOrderID] PRIMARY KEY CLUSTERED
    (
        [WorkOrderID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

My questions are:

Is a computed column in fact transgressing 3NF or is it not, considering that such a column cannot be updated manually by a user?
If it is indeed violating 3NF, then why is the computed column shown in the example above?
Ignoring the factor of whether a computed column infringes 3NF or not, what are the criteria of measure to choose between a computed column over computing when querying?


texts:
I have noticed when changing the data type of a column, the table size can double with a simple ALTER statement (Reclaiming Space After Column Data Type Change).

Why does SQL Server do this and what causes it?

I have seen posts for how to resolve the issue, how to observe the issue, but nothing about why it is happening and what SQL Server is doing under the hood.

The seemingly correct method is to create a new table, put all the data in the new table with the correct data type, drop the old table, and sp_rename the new table (as seen when you script out the change from using the Designer in SSMS). But it is odd that a straight table alter would not provide a warning or error message when it considers the operation one that "requires table re-creation".


texts: Mysl hangs when calling function inside select

texts:
I have a problem with MySQL. I have a query:

select distinct a.continent_code, a.country_code, a.city_code
from t_lst_ip4 a
where a.idncode = (select f_get_ip4_code(in_ip4_desc));

select f_get_ip4_code(in_ip4_desc)
by itself is a function that always returns -1;

it looks like this:

delimiter $$
use `geoloc`$$
drop function if exists `test1`$$
use `geoloc`$$
create function `geoloc`.`test1`
(a int) returns int
begin
    return -1;
end;
$$
delimiter ;

When running this function separately, it works fine and fast.

When calling mentioned SELECT with just '-1' (that function always returns) it works fine.

I guess it has something to do with calling a function inside select?
separately everything works fine.

I guess function executes as many times as there are rows.

So if I have 3M rows, the function will execute 3M time.

I left only 3K entries in the table and now that select runs fast.

Is this how functions are originally executed inside SELECT?


texts: Need to aggregate data from multiple tables with multiple same foreign keys Access

texts:
I need to add coHours and BaseBidHours and then compare to SumofActHours all per CodeID per jobID. See image below.

I only loaded everything in to one query to visualize all the tables. Any thoughts would be awesome, still relatively new to access and DB's in general. I imagine I will need to nest several queries?

Sorry I don't know how to explain this any better.


texts:
I am trying to run the script to update an XML column:

UPDATE DataImpTable
SET serviceData.modify('replace value of (/SMObjInfo/CentralData/SMData/CentralSDItem/ControlData/text())[1] with "9876"')
WHERE identifier=5

<SMObjInfo xmlns="DataService/1.0.0.0" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <CentralData>
    <SMData>
      <CentralSDItem>
        <ControlData>1234</ControlData>
      </CentralSDItem>
    </SMData>
  </CentralData>
</SMObjInfo>

Change value of ControlData to 9876 but the value doesn't seem to change in the XML for the /SMObjInfo/CentralData/SMData/CentralSDItem/ControlData value.

Is it anything to do with typed and untyped XML?


texts: Postgres 9.6 (pgAdmin 4) - How to query daily statistics

texts:
Is there a way I can query through pgAdmin in a way I will get statistics for daily query usage?

I need to find what queries are run against my api as I get huge network traffic. I'm looking for a way I can get top queries for example (top 100). I've tried using various pg statistics, but didn't find anything that provides this feature.


texts: pg_createcluster - Error: cluster configuration already exists

texts:
Wow! I seem to be stuck in the 9th level of pg wrapper command hell...

On Debian, have been trying to delete cluster 'main' (in order to recreate using appropriate locales and other options...)

After a bit of futzing, am getting these responses from wrapper tools:

$ pg_createcluster 9.4 main Error: cluster configuration already
exists

( seems reasonable - it does! )

$ pg_dropcluster 9.4 main Error: Invalid data directory

OK, but which data directory? The 'main' cluster dir is there, intact - correct permissions...

What do these tools read to get these configurations?

How to drop 'main' and start afresh?


texts:
I have simple master/slave replication set up between two servers.  The master has several DB's I wish not to replicate at this time, in fact there is only DB I am trying to replicate.  I have several ignore statements in the .ini file but today replication stopped based of a sql statement that I thought should be ignored.  Ini and error below...can anyone see what I am doing wrong and the cleanest way to move forward past this error?
error
Error 'Table 'registry_mobiledoc.regsitry_temp' doesn't exist' on query. Default database: 'mobiledoc'. Query: 'DELETE FROM registry_mobiledoc.regsitry_temp'
config of slave
[mysqld]
basedir=E:/mysql
datadir=E:/mysql/data
tmpdir=E:/mysql/tmp
log-bin=E:/mysql/mysql_binlogs/binarylogs
port=4928
skip-locking
skip-bdb
lower_case_table_names = 1
query_cache_type = 0
max_connections=300
query_cache_limit=1M
tmp_table_size=1M
max_tmp_tables=150
wait_timeout = 30
interactive_timeout = 30
table_cache = 900
flush_time = 0
log-slow-queries
long_query_time=3
skip-name-resolve
expire_logs_days=14
server-id=3
default_table_type = INNODB
myisam_data_pointer_size=6
If memory = 4GB
key_buffer = 256M
max_allowed_packet = 1M
max_allowed_packet = 32M
sort_buffer_size = 512K
read_buffer_size = 512K
myisam_sort_buffer_size = 512M
query_cache_size = 0
tmp_table_size = 1M
thread_cache_size=250
number of CPU's*2 for thread_concurrency
The following parameter will change based on the number of CPUs. Some times
the hyper threading is enabled which makes sinlge processor look like dual,
so Make sure that this is done properly.
For single processor -> 2
Dual -> 4
Quad -> 8
thread_concurrency=2
innodb_thread_concurrency=2
Innodb parameters
innodb_flush_log_at_trx_commit=0
innodb_log_files_in_group=2
innodb_log_file_size=256M
transaction_isolation = REPEATABLE-READ
read_rnd_buffer_size = 8M
innodb_data_home_dir=D:/eClinicalWorks/mysql/data
innodb_data_file_path = mobiledoc_InnoDb_data1:300M:autoextend
innodb_file_per_table
innodb_additional_mem_pool_size=8M
configure innodb_buffer_pool_size according to available memory
innodb_buffer_pool_size=16G
innodb_log_buffer_size=8M
set-variable=key_buffer=16M
innodb_data_home_dir=E:\mysql\data
master-host=192.168.12.83
master-user=SlaveUser
master-password=P@ssw0rd
master-port=4928
master-connect-retry=15
read-only=1
relay_log_purge
replicate-wild-ignore-table=ecw%.measuretemp
replicate-wild-ignore-table=ecw%.mu_%
replicate-do-mobiledoc
replicate-ignore-db = cur_lmrp
replicate-ignore-db = ecwmaster
replicate-ignore-db = medispan
replicate-ignore-db = mysql
replicate-ignore-db = multum
replicate-ignore-db = registry_mobiledoc
skip-slave-start
slave-skip-errors=1062
[WinMySQLadmin]
Server=D:/eClinicalWorks/mysql/bin/mysqld-nt.exe
user=root
password=test
Server=E:\mysql\mysql\mysqld-nt.exe


texts: Enabling TLS and TLS-MA simultaneously for different clients of a Oracle DB

texts:
We have a requirement where we want to enable TLS-MA for some of the clients connecting to a specific Oracle database while the other clients can continue to use TLS with server certificate.

We are using Oracle 12.c in our environment.
Clients are connecting using the jdbc thin driver

I am an Oracle noob and i am not able to understand the documentation here
Will it work if i create 2 listeners; one with
SSL_CLIENT_AUTHENTICATION=TRUE
and another with
SSL_CLIENT_AUTHENTICATION=FALSE
Any pointer to reference documentation will be great.


texts:
I created some select statements which run without any problem in SQL Server. Then I joined them together with UNION and got this error message>

  Msg 8114, Level 16, State 5, Line 1 Error converting data type
  nvarchar to float. Warning: Null value is eliminated by an aggregate
  or other SET operation.

Does anyone have an idea why? (I googled, and found that it could because I have some NULL values, which can-t be summed up or something, but as I said, the two codes run without error seperately.)

SELECT SUM(try_convert(numeric(38, 5), Rüsten_Ist)) AS Rüsten, 1 AS filtering
FROM   [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100)
       AND (YEAR(BuchDatum) = 2019)
       AND (DATEPART(week, BuchDatum) = 21)
UNION
SELECT SUM(ISNULL(try_convert(numeric(38, 5), Arb__Plan), 0) + ISNULL(try_convert(numeric(38, 5), Masch__Ist), 0) + ISNULL(try_convert(numeric(38, 5), IstArb), 0)) AS B, 2 AS filtering
FROM   [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100)
       AND (YEAR(BuchDatum) = 2019)
       AND (DATEPART(week, BuchDatum) = 21)
UNION
SELECT SUM(try_convert(numeric(38, 5), Restzeit_T)) AS Zukünftliche_Bearbeitungszeiten, 3 AS filtering
FROM   [Test].[dbo].[ZNEJO_DETAIL]
WHERE  (ArbPlatz = 103100)
       AND (YEAR(Endtermin) = 2019)
       AND (DATEPART(week, Endtermin) = 21)
UNION
SELECT SUM(try_convert(numeric(38, 5), Ausschuß)) AS C, 4 AS filtering
FROM   [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100)
       AND (YEAR(BuchDatum) = 2019)
       AND (DATEPART(week, BuchDatum) = 21)
UNION
SELECT SUM(Angebot) AS Angebot, 5 AS filtering
FROM   [Test].[dbo].[CM01_DETAIL]
WHERE  (Woche = '21.2019')
       AND (Arbeitsplatz = '103100')
UNION
-- This is where I seperate the code.
SELECT FORMAT((
          SELECT W21
          FROM [Test].[dbo].[Übersicht_Woche_RAP_103100]
          WHERE id = 2) /
          (SELECT W21
          FROM [Test].[dbo].[Übersicht_Woche_RAP_103100]
          WHERE id = 5),'P0') AS Calculation, 6 AS filtering

The two screenshots are the two results from the code run in two parts. I want to join them together with UNION.

Thanks for all the help


texts: performance issues when concurrent requests

texts:
We are using SQL Server 2008 R2 on a Windows Server 2008 server.

We have some main functions which uses some Stored Procedures which has a lot of sub-queries  inside of it.

I'll take one SP as an example; when running this SP sequentially it takes about 500ms, and we are ok with it (kinda), it has mainly select queries, some on the same table, it also has some temp tables created and filled with another SP.

We are now in the process of testing these main functions under multiple concurrent requests, and we found out right away that the SP's completion time jumps really quickly to seconds and rising...

after using some suggestions, I noticed that more than one spid is associated with multiple S locks, and that some processes are blocking themselves.

other than that, I'm uncertain as to what is making performance so poor.

I'm not aware if this description is too vague, I would love to give more information if needed, in the end I would like to know where you think should I look at in order to solve this problem.

EDIT:

Here is a portion of the execution plan, it doesn't look good...

Should I just add the indexes as specified?

what does all the parallelism sections in some of the queries' execution plan mean?

does this explain the performance problem?


texts:
I have an issue with insert time out. My table has a size of 12 gb and 12 million rows.

Sometimes I get timeout on insert. I have an insert query every 5 sec on average.

I suspect that I have lock escalations on the table when end user performs a select.

What is the best practice to avoid insert time out on large tables?

Is to create two tables one insert second for select ? replication ? SQL AVAILABILITY Groups ? table partitioning ? any other options?

(soon  we are going to upgrade to SQL Server 2016)

regards,
Pavel.


texts: Multiple Agent Jobs Running same SSIS Package

texts:
I have the following doubt, is there a possibility to have faild a Job when are running simultaneously as the following example:

4 SQL Agent Jobs running the same SSIS in different schedules, I've heard that the max number of possible execution is 4, but not sure if that is a parameter that can be changed or not, if so, have you heard about it ?


texts: Using cursor to update if exists and insert if not

texts:
I am trying to write a cursor. I have created a table for capturing login name and time from sys.dm_exec_sessions. Now, I need to write a cursor to update the login time to last login time if the login already exists and insert a row in case login does not exists in my table. I have come up with following, but unfortunately I am getting an error with subquery returning more than one value for it. Any ideas???

declare @log as varchar(200)
declare @log_time as datetime
declare LoginsSize cursor for
        SELECT  login_name, login_time
        FROM sys.dm_exec_sessions
open LoginsSize
            fetch next from LoginsSize into @log, @log_time
            while( @@fetch_status = 0)
        begin
          If (Select Login from [dbo].[LoginsForDBUserList])  = @log

             Begin
             UPDATE  [dbo].[LoginsForDBUserList]
             SET     LastLoginTime = @log_time
             WHERE   [login]= @log
             END

             Else

             Begin
             Insert Into [dbo].[LoginsForDBUserList]
             SELECT  login_name, login_time
             FROM sys.dm_exec_sessions
             END

        fetch next from LoginsSize into @log, @log_time
        close LoginsSize
        deallocate LoginsSize

end


texts: In PostgresQL, how do I make a query match exactly one row or fail?

texts:
I am making an arbitrary DELETE or SELECT query and I know this should effect or return only one row. How can I ensure that the query doesn't execute and I get some signal that something is wrong when more then one row matches?

Using LIMIT and ORDERBY allows the operation to continue, rather than doing a hard abort. Meaning multiple objects may be subject to DELETE with no chance to recover.

Preferably whatever signal results would be distinct from having zero match.


texts:
I'm not a DBA, I'm a DevOps guy trying to manage a MySQL database which is ever growing and desperately needs some archiving. My question is not how to archive. I think I have a pretty good idea and have SO for anything I might not know yet.
I'm trying to figure out "what" to archive. I don't have a clue what our apps need, or how often they access the records. I can tell you our DB has 410 tables in it. Our largest tables has 4 million records in it. I don't have the time to go through each and every one of those, ask how much they get used, or accessed, etc. I'm trying to trim down our development environments database size. It's slow to refresh them, and our RDS bill is massive. I know for certain our development environments do not need all this data. We also run a sanitizing script which rips out PII and other real world sensitive information. I don't think it is necessary to sanitize 8+ years or possibly older of records just for a development environment database.
Goal: Have a smaller developer environment database to A) trim down bill B) reduce time for data refresh.
Question: What are common things you look at when you are making decisions on what to archive, and how much to archive? Are there tools that help you identify good candidates? Are there common queries that you run to help you make this decision?


texts: How do these snapshot isolation level configurations interact on a SQL Server 2005 instance?

texts:
I started troubleshooting a tempdb issue we are having on SQL Server 2005 Enterprise Edition. A developer is getting an error that tempdb is out of space. Technically, the error is:

  Transaction aborted when accessing versioned row in table
  'dbo.inserted' in database 'dbname'. Requested versioned row was not
  found. Your tempdb is probably out of space. Please refer to BOL on
  how to configure tempdb for versioning

I looked at the database configuration in sys.databases and found the following settings:

snapshot_isolation_state: 0

snapshot_isolation_state_desc: OFF

is_read_committed_snapshot_on: 1

I looked up what this meant in BOL and the salient information is as follows:

  snapshot_isolation_state

  State of snapshot-isolation transactions being allowed, as set by the ALLOW_SNAPSHOT_ISOLATION option:

  0 = Snapshot isolation state is OFF (default). Snapshot isolation is disallowed.

  1 = Snapshot isolation state ON. Snapshot isolation is allowed.

  2 = Snapshot isolation state is in transition to OFF state. All transactions have
  their modifications versioned. Cannot start new transactions using
  snapshot isolation. The database remains in the transition to OFF
  state until all transactions that were active when ALTER DATABASE was
  run can be completed.

  3 = Snapshot isolation state is in transition to
  ON state. New transactions have their modifications versioned.
  Transactions cannot use snapshot isolation until the snapshot
  isolation state becomes 1 (ON). The database remains in the transition
  to ON state until all update transactions that were active when ALTER
  DATABASE was run can be completed.

  snapshot_isolation_state_desc

  Description of state of
  snapshot-isolation transactions being allowed, as set by the
  ALLOW_SNAPSHOT_ISOLATION option:

  OFF
  ON
  IN_TRANSITION_TO_ON
  IN_TRANSITION_TO_OFF

  is_read_committed_snapshot_on

  1 = READ_COMMITTED_SNAPSHOT option is ON. Read operations under the
  read-committed isolation level are based on snapshot scans and do not
  acquire locks.

  0 = READ_COMMITTED_SNAPSHOT option is OFF (default). Read operations
  under the read-committed isolation level use share locks.

If I'm understanding this correctly, snapshot_isolation_state is OFF and disallowed. However, the 1 on is_read_committed_snapshot_on indicates that the database is using snapshot isolation. How is this possible if it's disallowed?


texts:
What is the return datatype of this statement :

INSERT INTO sample_table
VALUES (
    #{id},
    #{Name},
    ...
)
ON DUPLICATE KEY UPDATE
    column_id   = VALUES(column_id),
    column_name     = VALUES(column_name)


texts: Repeat an action for X number of PostgreSQL tables

texts:
Note: The solution does not need be optimised for processing speed. It's supposed to be part of a setup phase and won't be run more than once.

General problem

I have X number of tables in a PostgreSQL database with very different names. I can neither be bothered to write the same PostgreSQL commands for each of them, nor can I be sure that the next set of data that I import has the exact same set of tables. Yet I would like to repeat the same action on each of them. How would I go about doing this? Is it even possible?

I will accept an answer that can do this much. However, an answer that does this and also allows me to exclude some tables that I do know the names of would be even better.

Specifics

This question is closely related to another question which details what I want to do with the tables, but both specific and more general answers are appreciated.

Findings

While looking for a solution I found this question, but I don't know if and how I could use those answers. I'm including it here in hopes that someone else might know how to apply them to my question.


texts: Cardinality of one-sided and mutual authentication (entity-relationship modeling)

texts:
I would like to model client computers that are identified by servers that validate their computer certificates. I am stuck at the cardinality of the relationship between a client and a server when the client certificate is validated by the server.

The reason I am not satisfied with a one-to-one (1:1) relationship is that there are configuration options that require the server to be identified as well. If I describe the one-sided authentication as a 1:1 relationship, then how do I describe mutual authentication? Should I model two relationships?


texts: MySQL ON DUPLICATE KEY UPDATE performance issue

texts:
We have a table with over 200k rows. Every row belongs to a category, there are almost 50k rows per category. What is the best way to update all rows of that category? We are using a script like

insert into foo (catgeory_id, family, text)
values ('1', 'test', 'my text'), (50k more rows)
on duplicate key update family = values(family), text = values(text)

Running that solely is relatively fast. Our production server has many databases and when we run the script on the different databases at the same time the whole machine starts swapping and on some point the kernel is killing the mysql process.

Where should we start to optimize? We are running MySQL 5.5.


texts: How do I solidify my SQL Server / T-SQL Knowledge?

texts:
I am a C# developer and not a database admin, but I have been using SQL Server for application developnment for over 4 years now and was really interested in getting a more complete understanding, and filling the gaps in my knowledge.

I read the training kit and passed the 70-461 exam today, learnt a ton of things from that book which I had not known or used before.

My question is, I would like to become proficient in all of what I have learn't, so that I know it like the back of my hand. It seems like I can see the correct answers from a list of choices but writing the T-SQL from scratch is a challenge.

How would I go about doing that? Keeping in mind the developer jobs will always use a small subset of the functions, is there any way I can practice everything, and become better at it instead of forgetting everything I have just learnt?


texts: Error in 'Master Database Restore' in SQL Server 2012 (Because of difference in SQL Server Build)

texts:
I have a backup of a master database that was taken from SQL Server 2012 11.00.2218 build.

Now I have a new SQL Server installation with build 11.00.2100. I tried restoring the master database after running in single-use mode but got the error as follows:

  The backup of the system database on the device E:\master_backup.bak cannot be restored because it was created by a different version of the server (11.00.2218) than this server (11.00.2100).

I also tried installing SQL Server 2012 SP2. But still the same error. Please help


texts:
At one point, test database deployments within my department were handled directly by the database developers. The typical process was that the developer would unit test their changes in the development environment, then:

1) Migrate their changes to the test environment.

2) Send an email to the testers to let them know the changes were available in the test environment.

This was a solid process, which typically took only a few minutes to complete, and was nearly always completed successfully.

However, at one point the head DBA for my company took away all database owner permissions from the database developers on the test environment so that test database deployments are now handled only by DBAs. Over time the process morphed into the following:

1) Database developers complete a 15 page form to list the changes they need to migrate (for a typical deployment, most sections are blank, but the applicable sections are scattered throughout the form)

2) The database developer completes a 1 page script header for any migration scripts involved in the plan

3) The database developer references a deployment project containing all of the objects to be deployed in the plan

4) The database developer sends an email to the department head requesting the deployment

5) The department head approves the plan, scripts and project

6) The department head forwards the email to the primary DBA for the database

7) The DBA either approves the migration, or rejects it (typical rejections are due to a section of the 15 page form or 1 page script template not being filled in, or the 15 page form not being the most recent version of that form)

8) The developer completes a 2 page form email to the testers, letting them know the changes that are scheduled to be migrated

9) The DBA sends an email to the developer and department head letting them know the deployment was complete. Or alternatively, that it could not be completed.

10) The developer replies to the 2 page form email, letting the testers know the deployment was completed.

So, a process that once involved a single person, took less than five minutes to complete and had a near-zero failure rate now involves a minimum of three people and takes a minimum of twenty minutes to complete. In addition, deployments submitted after 2:00 PM often cross over into the next day based on DBA and department head availability.

Along similar lines, the head DBA has published a 50 page standards document. The majority of the document is focused on what he feels things should be named, the use of acronyms, underscores, capitalization and so forth. However, it also includes some time-consuming non-functional requirements. For example, adding database descriptions to all parameters for all objects. Or requiring all constants used in the database to be drawn from a numerically keyed constants table. Which means that execution plans can't be optimized based on the values for the constants. The 50 page standards document also includes a number of prefix/suffix naming conventions that are now considered industry anti-patterns.

The head DBA often refuses to deploy anything that does not conform to the latest version of this document, which changes on average every two months and often reverses previous positions. Ostensibly this is to 'raise the standards used in the department' but in reality quality has fallen off considerably since he implemented these policies. The time that used to be spent improving the existing code base and adding new features has been diverted to renaming database objects and regression testing the changes. The database developers in the department estimate they now spend 40% off their time dealing with standards and processes put into place by the head DBA. All of which have a negative or no impact on the performance and stability of the applications supported by the databases.

Has anyone out there had luck dealing with inefficient DBAs? Or alternatively, have any similar stories they'd like to share?


texts: How to setup snapshot agent for publisher with distributor on different machine?

texts:
Looking at this question here: In which server does the snapshot agent run?

The answer says that the snapshot agent runs on the distributor, but that there is also a job run on the publisher. i.e. security credentials are required for both the publisher and the distributor if these are on separate machines.

So looking at this screenshot:

Is it correct to say that the top section is authentication for the distributor machine? And that the bottom section is authentication for the publisher machine?

I'm confused because I'm getting errors and I don't know where from. Since I had to register the publisher on the distributor first, I could see how the publisher is being connected to from the distributor.

===== EDIT

Indeed. As described above that is the case.


texts:
In SQL 2012 can you replicate sequence objects, and if so do changes to the sequence (including consumption of values) get replicated?

If not is there a good reason why this was not included?


texts: How can I create postgres users for employees that have limited access?

texts:
I want to create postgres users for our staging + production databases that allow the users limited access to the tables.

I want the users to have read only access to ALL tables except messages & credit_cards. I want to give them update access for the access_tokens & I want them to have read access to newly added tables. How can I do this?

Example DB:

users:

id
name

messages:

user_id FK
message
to_user_id FK

credit_cards:

user_id FK
card info...

access_tokens:

token
user_id FK
expired_date

... Other tables

I tried:

grant USAGE on schema public to james;
grant select on all sequences in schema public to james;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO james;

ALTER DEFAULT PRIVILEGES
    FOR ROLE james
    IN SCHEMA public
    GRANT SELECT ON TABLES TO james;

Update 1:

Note that running:

ALTER DEFAULT PRIVILEGES
    FOR ROLE james
    IN SCHEMA public
    GRANT SELECT ON TABLES TO james;

Will throw the following error from postgres (rds):

  ERROR:  must be member of role "james"

However running the grant commands worked. It seems I cannot alter the default privileges for the postgres rds database.


texts: Are there any advantages in SQL Server Always On vs. SQL Server 2008 R2 Mirroring with only two nodes?

texts:
We are currently hosting a SaaS website on two Windows 2008 R2 / SQL Server 2008 R2 Servers. Would upgrading to SQL Server 2012 / Always On give us any advantages with respects to high availability?

Note that while we are looking for high availability, high performance is not really an issue for us at the moment, so being able to run read-only queries on backup database servers is not really important.


texts: Trigram index on all values of a JSONB column

texts:
I have a JSONB column that contains a bunch of key/value pairs, no complicated nested structures or arrays in there. The keys are dynamic, and I don't know them in advance. I'm using PostgreSQL 9.6.

What I'd like to do is to query the values in that JSONB column with a query like

ILIKE '%mysearchterm%'

which is very well supported by a trigram index. I've used them to index regular text columns, and it works very nicely for this kind of query, but I'm not sure how exactly to approach the JSONB column here.

Is there a way to dump all values (not the keys) from a JSONB column into a trigram index?


texts: SQL Server 2012: Full Text Catalog taking up all the disk space

texts:
We have created full text catalog and index for a pretty large table. The column on which the index is being created has a datatype of varchar(max). The problem started as soon as we started populating the catalog. It almost grew to 835GB and Population status stuck to "Processing Notification". I think is status code 9 which corresponds with the "Change tracking" status in the SQL. It gets stuck in this status almost for ever.

Is there a way to deal with this huge catalog size, may be shrink it or move it to a different location.

Is switching off Change Tracking going to improve the situation.

Any help regarding this will be highly appreciated.

UPDATE:

Some more info about the volume of data we are dealing with:

Row Count: 129,953,562 Total: 1327 GB Data 867 GB Index 452 GB Column size 361GB

Although we went to FT instead of LIKE search, but is there a strategy to optimize LIKE search to perform better (may not be as efficient as FT, but somewhat near to that)


texts: Is it possible to log whenever someone decrypts a credit card in MSSQL 2012?

texts:
All of our credit cards are encrypted. The process to decrypt them is to Open the symmetric key and decrypt the data by certificate.

Is it possible to add a trigger on something in the process to ensure that this is logged to a table?

For example: Trigger when the below is executed

OPEN SYMMETRIC KEY KEYNAME
DECRYPTION BY CERTIFICATE CERT_NAME;
GO
SELECT cc_card_number, AS 'Decrypted Credit Card Number'
FROM dbo.clever_lead_master
WHERE lead_id in ('6123009','6756089','6123012', '6605155')

 -- Close the symmetric key
CLOSE SYMMETRIC KEY CC_SYMMETRIC_2019;
GO

e.g. trigger:

INSERT INTO pci_log (event_time, lead_id, [description], who)
(SELECT getdate(), lead_id, 'cc_encrypt', user FROM clever_lead_master
WHERE lead_id in (select lead_id from inserted))


texts: When was a table trigger disabled and re-enabled?

texts:
I need to know if the enable/disable history of a table trigger is natively tracked by SQL Server.

I've reviewed the system views:
 • [sys].[triggers] contains a modify_date field
 • [sys].[trigger_events] focuses on the trigger INSERT/UPDATE/DELETE events

Can you recommend any other sources of information about trigger history?


texts: How can I pg_dump my database while excluding extensions?

texts:
I am trying to migrate my PostgreSQL 9.3 database to heroku.

My database uses the UUID-ossp contrib extension

I am not able to load my db onto Heroku because

ERROR:  permission denied for language c

on

-- Name: uuid_generate_v1(); Type: FUNCTION; Schema: public; Owner: -
--
CREATE FUNCTION uuid_generate_v1() RETURNS uuid
    LANGUAGE c STRICT
    AS '$libdir/uuid-ossp', 'uuid_generate_v1';

I understand I can simply

CREATE EXTENSION "uuid-ossp";

in my database.  But how can i exclude the various UUID functions from my pg_dump in the first place?


texts: Why is supplying the hostname sometimes optional and otherwise not in psql?

texts:
After having used createuser and createdb to create a user and database both called teamcity I wanted to log in. Using both of these commands, I never said anything about which host the database was running on, so I would assume that when using psql it would use the same settings. This is what happens if I do not supply a hostname:
$ psql -U teamcity
psql: FATAL:  Peer authentication failed for user "teamcity"

No password prompt and immediate failing. If I supply -W to force a prompt, the password does not work. I am logged in as a normal unix user, not the postgres user.
Now, if I supply -h localhost everything works as it should:
$ psql -h localhost -U teamcity
Password for user teamcity:
Timing is on.
Line style is unicode.
Border style is 2.
Null display is "[NULL]".
Expanded display is used automatically.
psql (10.11 (Debian 10.11-1.pgdg80+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

localhost teamcity@teamcity=> \q

What can explain this behaviour? I assume some of the commands connect to the postgres using unix sockets and some using TCP, but it should be the same database, no matter, right, so why is the authentication failing in some cases?


texts: SQL Server Insert Into - How to identify the column that is causing truncation error

texts:
I have a stored procedure that inserts 650 fields into a table. The insert is failing with a truncation error.

It's a simple

INSERT INTO
SELECT (a bunch of fields)
FROM (a bunch of tables)

Below is the error message:

  Msg 8152, Level 16, State 14, Procedure DSP_Procedure, Line
  1075 String or binary data would be truncated.

Is there a quick way that I can identify what field is causing the truncation error?

The fact that the select statement  to be inserted into the table has 650 fields makes it difficult to pinpoint which field is causing the truncation error.

I am thinking I can maybe comment out blocks of fields at a time so as to only have the SP insert 100 fields at a time and then run the SP 6 or 7 different times until i can at least narrow down to a group of 100 fields that will contain the field that is causing the truncation error.

Alternatively I am thinking that maybe I can just SELECT INTO a new table and then compare the data lengths in the table vs the data lengths of the target table I am trying to insert into in my SP to see which field contains a longer than expected field length...

I am using SQL Server 2014.

Any easier alternatives?


texts: Best way to loop through mulitple rows for an entity in a table with many entities -- SQL Server

texts:
I have a table in my SQL Server DB that is sturctured as

Entity_ID  varchar(255)
Production_Date date
ProductValue float

The table is indexed by Entity_ID and Production_Date. The dates are one month apart and there are no gaps in the dates once the data starts for each Entity_ID.  But each entity will start a different date.

There can be hundreds of rows for a single Entity_ID. For the first sixty rows for an Entity_ID (sorted ascending by the date) I need to know the ordinal position of the maximum value for each series of dates, starting at the first date and going to sixth date.

By this for the tenth date in the series for a particular entity, I am looking for the position of the maximum values for the first through tenth dates.

I can see how I can do this within a C# program, but I was wondering if a SQL script might be faster.  I have a couple of million entities and I can see grinding through the database could take time.  I am wondering if straight SQL might be faster than dragging all the data from the database into a C# program.

As far as I am thinking about structuring a C# program, I would read the first 60 values for each entity.  Then start walking through the stack of values. For each step along the way, I would keep track of the position of the maximum value as I go along.  For each step from 1 to 60 I could then write back to the database the position of the maximum value up to that step.

I know how to do this in C#, but am struggling to see if this logic (or some other) can be applied in a SQL script.


texts: Failure sending Excel attachments larger than 1.5MB

texts:
I have innumerous reports that I send from SQL Server to people.
But for some reason there's this single one, with 10k+ rows, that I can't send via dbmmail.
I tried top 7000 and it worked but top 8000 still creates the file, I can open it, but I can't send via dbmail. No errors or alerts about SQL attachment size limit.
File is 1.5MB and I don't receive any error message. I changed only the attachment name to see if the code was wrong but it sent the new file (the smaller one).
It's not an attachment size error because I configured it.
Is there a row limit to send Excel files via dbmail?
SELECT * FROM msdb.dbo.sysmail_allitems WHERE sent_status = 'failed' Only says "failed" on these items.
Well code is here for people that wants to send excel files by email with header:
EDIT: I did some tests. I created an .xls file with 7k rows and only 1 column. 15kb file and it was sent.
Then I created a file with 26k rows. 1 column. File was sent. 170KB.
And to my surprise, I ran the query, 9krows (bcp crates a 2mb file) and I just copied into an .xls file. The file was 160KB and I could send it via dbmail.
I'm sure it's a size problem and not row limits.
why is bcp creating such big file?
SELECT * FROM msdb.dbo.sysmail_sentitems says the file was sent, but it wasn't.
SELECT * FROM msdb.dbo.sysmail_unsentitems says "retrying". After some minutes there's 0 rows and still I didn't receive the email.
SQL is configured to send 20MB files.
create TABLE ##tempsss
    (
        ..fields VARCHAR(XX) --it needs to be varchar to be able to use header
    )

insert into  ##tempsss
SELECT
...fields...
FROM some table or view
go
-------------------------------------------------------------
--passo 2 --

exec xp_cmdshell 'bcp "select ''field1'',''field2'',''field3'' UNION ALL select * from table or view" queryout "filepath\filename.xls" -U sa -P password -w  -S servername'
-------------------------------------------------------------

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'dba profile or something',
    @recipients = 'recipients email',
    @subject = 'email subject',
    @file_attachments='path\filename.xls'
-------------------------------------------------------------

drop table ##tempsss
-------------------------------------------------------------


texts: Should I delete the mysql database user id created for my site by the developer?

texts:
I am a newbie when it comes to anything related to databases. I cut ties with my developer who had set up my site and was hosting it too about six months ago. I moved my site to a new host thinking that that was the end of that.

I realised recently that he also had access to the database as he'd created a privileged user id originally(a user by his name kept showing up in my sql processes). Can he still access my database on my new host or is this user showing only because it is the only default user?

I asked my hosting company if this was a concern but they said it was not an issue. I have my doubts though. Can anyone please tell me how to educate myself about this and if this is a concern. I just want to learn enough to be able to run my content based wordpress site.

Recently my site experienced a brute force attack so I want to take all precautionary measures when it comes to security.

If this is a concern will simply deleting this user from the cpanel take care of things?
Please help


texts:
I installed SQL Server 2008 R2 Express Edition.

I opened the command prompt and typed sqlcmd -S server1\SQLExpress -U SqlUserAccount -P SqlPassword to connect to SQL Server.

However, it occasionally shows error messages such as 'login failed for user', or 'could not open a connection to SQL server'.  When I install SQL Server 2008 R2 with Advanced Services, I can logon to SQL Server using SSMS successfully.


texts: database design for optional association and temporal

texts:
I'm designing a simple database and I'm stuck with one part. I have a Task, Sweep and Spray entities.

       +-------+
       | Task  |
       |       |
       +-+--+--+
         |  |
         |  |
+------+-+  |    +--------+
|Spray |    +----+ Sweep  |
|      |         |        |
+------+         +--------+

Each task is done by either a Sweep or Spray or both. Each Spray and Sweep has few properties that are only valid for particular Task (temporal) and may change over time. For example, spray color is yellow for one task and might be green for another task.

How do I design the tables so that I can query the tasks to see which task was done by which spray and sweep with which properties? Like:

task1: spray green  | sweep auto
task2:      -       | sweep manual
task2: spray black  | sweep manual
task2: spray blue   |       -


texts:
Given the following CREATE TABLE statement:

CREATE TABLE [dbo].[Unit]
(
    [id]                SMALLINT       NOT NULL IDENTITY(1,1),
    [name]              VARCHAR(100)   NOT NULL,

    CONSTRAINT [PK_Unit] PRIMARY KEY CLUSTERED ([id]),
    CONSTRAINT [UQ_Unit_Name] UNIQUE ([name])
)

How to I allow the following two strings to co-exist without violating the UNIQUE constraint?

INSERT INTO dbo.Unit (Name) VALUES('e³m³/day')
INSERT INTO dbo.Unit (Name) VALUES('e3m3/day')

Any insight appreciated.

EDIT

Current Collation is Latin1_General_CI_AS

EDIT

Unless someone has a better option, it appears that I may have to use Latin1_General_BIN and manually handle CI in the few places where I need it... will convert this to an answer if no better solutions are offered.


texts: How to delete records without a primary key in a stardard way (i.e. not using ctid)

texts:
Case of use

We have a log table without a PK with two columns (date, event);

You inserted a row. Then you want to undo the inserted record.

Is there a way to delete that row (without deleting other rows with the same data), that doesn't use postgres only capabilities?

I want a solution that works in another database (it doesn't need to be totally standard, it can be only in just one database: SqLite, Oracle, MySql or SQLServer).

Example:

create table the_log(
  date date,
  event_id integer
);

insert into the_log(date, event_id) values ('2019-09-21',1),('2019-09-21',1);

select * from the_log;

My atempts:

delete from the_log where row_number() over ()=1;

delete from the_log limit 1;

with the_log_2 as (select *, row_number() over () as version from prueba_sin_clave)
delete from the_log_2 where version=1;

I supouse that the answer is No. I want to know if I am wrong or in what documentation I read that I am right.


texts: MySql Replicate different tables to different database

texts:
I have Database1 on server1 and Database2,Database3 on server2.
Database1 has two tables T1, T2. I want to replicate T1 on Database2 and T2 on Database3.
How can I accomplish it?

I googled but only found examples where replication was performed to only one DB.


texts: When all you indexes have included all the other columns, do you still have the penalty for a wide clustered index?

texts:
So I hear sometimes that I should not use a clustered index on a wide column, mainly because it is being referenced in all other indexes.

When all my other indexes on this table include all other columns - is there still this (extra) penalty?

(This table has no external references on the clustered index by other tables)

For reference my table looks a bit like this:

PartOfNaturalKey1    int
PartOfNaturalKey2    varchar(10)
PartOfNaturalKey3    varchar(20)
PartOfNaturalKey4    int
Value                varchar(10)
LastChanged          datetime2(7)
Invalid              bit

The Natural Key is unique and will (almost never) change.


texts: Deadlock graph with a lock on an index on a seemingly unrelated table

texts:
I have a deadlock graph from a deadlock where one process is doing a SELECT and one is doing an UPDATE.  This seems like the classic case where the SELECT gets a NCI lock to perform a join and then a CI lock to retrieve all the data by lookup.  And the UPDATE is using the CI lock to perform an update and then needs to lock an NCI because the update results in a status change and the NCI facilitates finding items by status.

The problem is that one of the locks the UPDATE wants is NOT on the table it is updating and I can't find why this is happening.

Here's the SELECT:

SELECT *,
       RIGHT(c.CC_NUMBER, 4) AS CC_LAST_4,
       DATEDIFF(ss, '1970-01-01', plan_started ) plan_started_epoch,
       DATEDIFF(ss, '1970-01-01', plan_expires ) plan_expires_epoch
FROM customers c, accounts a, parent_cos pc, htt_customers_overlay_ultra u
WHERE c.customer_id = a.customer_id
AND   u.customer_id = c.customer_id
AND   a.cos_id=pc.cos_id
AND   u.customer_id = 9300;

Here's the UPDATE:

UPDATE htt_customers_overlay_ultra SET plan_state = 'Active'  WHERE customer_id = 9300;

But according to the deadlock graph, the UPDATE is acquiring a lock on ACCOUNTS.ACCOUNT0, which is the PK (CI) of the ACCOUNTS table.  There are no foreign keys in the overlay table.  There are some default constraints which I don't currently have permission to see.

I've looked at the deadlock graph in SSMS and in SQL Sentry Plan Explorer Pro and am none the wiser.

Here are the execution plans:

For the SELECT

For the UPDATE

I'd like to find out why it is getting this lock, and then the best way to serialize these calls.

Things I am aware of which I have already advised the client which have bearing on the locks taken, but don't explain the seeming unrelated lock which is arising:

Remove * and identify the columns needed and alter the NCIs to become covering - this would potentially make the SELECT use fewer locks

Determine why the system is SELECTing the same data that another process is processing - this would potentially mitigate these two processes running at the same time at all

There is a table scan in the SELECT


texts:
I have approximately 80 Databases with various collations.

I would like to modify the collation of all 80 databases.

I have the following code:

USE [COLLATE_ADM]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PR_COLLATE3] AS
BEGIN
    DECLARE @collate SYSNAME;
    SELECT @collate = 'Latin1_General_CI_AS';
    DECLARE @cmd AS NVARCHAR(4000);
    DECLARE @banco AS VARCHAR(100);
    -- Bancos que serão consultados
    SELECT name INTO #tmp
    FROM master.sys.databases
    WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb');
    --while
    -- Loop pelos bancos
    While (Select count(1) from #tmp) > 0
    BEGIN
        SELECT @banco = min(name)
        FROM #tmp;
        SET @cmd = '    use [' + @banco + ' ]

        insert into collate_adm.dbo.tblCollateScript;

        SELECT ''?'' as Banco,
            ''['' + SCHEMA_NAME(o.[schema_id]) + ''].['' + o.name + ''] -> '' + c.name
            , '' USE [' + @banco + ' ]; ALTER DATABASE '+@banco+' SET SINGLE_USER;
            ALTER DATABASE  '+@banco+ '  COLLATE Latin1_General_CI_AS;
            ALTER DATABASE '+@banco+' SET MULTI_USER;
            ALTER TABLE     ['' + SCHEMA_NAME(o.[schema_id]) + ''].['' + o.name + '']
            ALTER COLUMN ['' + c.name + ''] '' +
            UPPER(t.name) +
            CASE WHEN t.name NOT IN (''ntext'', ''text'')
            THEN ''('' +
                CASE
                WHEN t.name IN (''nchar'', ''nvarchar'') AND c.max_length != -1
                    THEN CAST(c.max_length / 2 AS VARCHAR(10))
                WHEN t.name IN (''nchar'', ''nvarchar'') AND c.max_length = -1
                    THEN ''MAX''
                ELSE CAST(c.max_length AS VARCHAR(10))
                END + '')''
            ELSE ''''
            END + '' COLLATE Latin1_General_CI_AS '' +
            CASE WHEN c.is_nullable = 1
                THEN '' NULL''
                ELSE '' NOT NULL''
            END
        FROM sys.columns c WITH(NOLOCK)
            JOIN sys.objects o WITH(NOLOCK) ON c.[object_id] = o.[object_id]
            JOIN sys.types t WITH(NOLOCK) ON c.system_type_id = t.system_type_id
                    AND c.user_type_id = t.user_type_id
        WHERE t.name IN (''char''
                            , ''varchar''
                            , ''text''
                            , ''nvarchar''
                            , ''ntext''
                            , ''nchar''
                        )
            AND o.[type] = ''U''
        ';

        SET @cmd = REPLACE(REPLACE(@cmd,'?', @banco) , 'XXXcollateXXX', @collate)

        BEGIN TRY
            EXEC sp_executeSQL @cmd -- Executa comando gerado pelo script
        END TRY
        BEGIN CATCH
            INSERT INTO tblCollateScript (rotina, script,Data)
            VALUES ('pr_BuscaCotas', @cmd, GETDATE());
        END CATCH
        DELETE FROM #tmp WHERE name = @banco
    END
    DROP TABLE #tmp
END
GO

This code lists the script to modify all databases (to change table collation, etc.), however I'm facing LOTS of problems, including:

PK is reference of another table.
IX is reference.

So I need to drop EVERYTHING and create again (including functions).

Is there an easy way to do this?


texts:
i have a msde (mssql) 2000 / v8.0 database that has one corrupted table. Microsoft SQL Server Management Studio Express can see the database STRUCTURE, but not the data. i can see the DATA of the tables using "SysTools MDF Viewer 1.0" (working link), but that's just for viewing, not for recovering the data.

the databases are corrupt because of harddrive failure (i know, backup backup.. i'm focusing on getting all the data back for now :( )

UPDATE 1:
   tried the following query:

CREATE DATABASE NewDbName
      ON(NAME=’OldDbName,
            FILENAME='C:\my\path\to\file.mdf')
      LOG ON(NAME='mydatabase_Log',
            FILENAME='C:\my\path\to\file.ldf')
      FOR ATTACH

and got the following result :

Server: Msg 823, Level 24, State 2, Line 1
   I/O error (bad page ID) detected during read at offset 0x00000001354000 in file 'C:\my\path\to\file.mdf'.

Connection Broken

currently, i'm trying to investigate this error msg (with little progress), mainly reading here. haven't tried all that yet

i was wondering if i can't construct the db from the transactional history, but i don't have "a good backup" to transaction from. is it going to work if i try that method using a "empty db" (with just the structure)

really big thanks for giving me suggestions on this! highly appreciated!

Update 2:
    Ran all the (updated) steps and i got to the data (YAY!), i'm seeing it in the SQL query analyzer, i just need to figure out how to export it safely and i'll be able to breathe! THANK YOU VERY MUCH!

Update 3:
   following this recover guide

Update 4: (epilogue)
   First and foremost, thank you Craig for the awesome help, your input was invaluable in solving this crisis.

After following steps 1-4 (with no error messages) i got stuck at DBCC CHECKDB. The process was taking more than 6 hours to complete. With no IO/CPU activity, i decided to stop the operation as it seemed to hang.

After checking with the client, it was revealed that straight data backup is desirable (as triggers  were recoverable by other means ), so i proceeded with Step 5a and copied the data (using
SELECT * INTO mynedatabase..sometable FROM mydatabase..sometable.) to a sane (new and empty) database. From there on all utilities (such as export / backup ) worked as expected.

for backup i used (writing here for reference)

BACKUP DATABASE backup_db TO DISK = 'c:\data_files\working.dat'

this should serve as (yet another) reminder: backup EVERYTHING


texts: Storing a large, sparse matrix in Apache Hbase?

texts:
I'm currently testing out Apache Hbase for our Matrix analytics service. I'm using a managed cluster running on AWS EMR.

The matrices are sparse, and we have 50,000 columns, and up to 10 million rows. The values are integer values

The main operation we'd like to support is random access of any 500 rows and 30 columns. We would like this operation to return in under a second, and ideally under half a second.

Apache Hbase seemed like the ideal option, as it's advertised to support 'real-time' access of large sparse matrices with 'millions of columns and rows'.

The instance I'm running is sizable, with 4 nodes, each with 16 cores and 256GB of memory.

I've tried both 'wide and short' and 'tall and thin' table formats. The 'wide and short' format has 50,000 columns, and is simply the matrix represented in hbase. The 'tall and thin' format has the row key as a composite key, with the format '{row_name};{column_name}' - e.g.  F1S4_160106_001_B0111;100507661. The 'tall and thin' format has a single column containing the value.

For both formats, I've reduced the block size to 8192 bytes and turned on the Bloom Filter, as well as adding SNAPPY compression.

The access pattern for rows and columns is completely random. Any 500 rows and 30 cells can be requested at any time. There is no easy way to group the rows and columms for faster access.

I'm still seeing a latency of up to 5 seconds, which is much too slow. Is this too fast a response time to expect from such a large dataset? Or am I making some basic error? Should I try encoding and compressing the row key?


texts: Unable to open database (Datafile corrupted)

texts:
I am unable to open the database, but i am able to mount it.

When i try to open the database, getting following error

ORA-03113 - end-of-file on communication channel

I am using Oracle 12C.

On further analysis below is the trace log created using adrci

> *** 2016-05-02 09:12:27.769 DDE rules only execution for: ORA 1110
> ----- START Event Driven Actions Dump ----
> ---- END Event Driven Actions Dump ----
> ----- START DDE Actions Dump ----- Executing SYNC actions
> ----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) ----- Successfully dispatched
> ----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) ----- Executing ASYNC actions
> ----- END DDE Actions Dump (total 0 csec) ----- ORA-01110: data file 111: 'V:\DB\CST001.DBF' ORA-01114: IO error
> writing block to file 111 (block # 1) ORA-27041: unable to open file
> OSD-04002: unable to open file O/S-Error: (OS 5) Access is denied.
> Read of datafile 'V:\DB\CST001.DBF' (fno 111) header failed with
> ORA-01208 Rereading datafile 111 header failed with ORA-01208 DDE:
> Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
> ORA-01110: data file 111: 'V:\DB\CST001.DBF' ORA-27091: unable to
> queue I/O ORA-27041: unable to open file OSD-04002: unable to open
> file O/S-Error: (OS 5) Access is denied. ORA-01208: data file is an
> old version - not accessing current version error 1114 detected in
> background process ORA-01114: IO error writing block to file 111
> (block # 1) ORA-01110: data file 111: 'V:\DB\CST001.DBF'
> ORA-27091: unable to queue I/O ORA-27041: unable to open file
> OSD-04002: unable to open file O/S-Error: (OS 5) Access is denied.
> ORA-01208: data file is an old version - not accessing current version
> 2016-05-02 09:12:27.778758 :kjzduptcctx(): Notifying DIAG for crash
> event

There is no permission issue. This is windows 2012 R2 Strandard edition. Database in no archive log mode, we have only backup's taken from expdp. The data is in SSD.


texts: SSRS email subscription with custom file name on the attachment

texts:
I would like to set up an SSRS email subscription that attaches the report in PDF format to the email with a custom file name that changes daily.  I was hopeful that I could to this via a data-driven subscription, but that does not seem possible.  Does anyone have a handy work-around for this limitation?

UPDATE: @swasheck provided a decent work-around, and one that we currently use.  But I am hoping to remove the step of saving to a file-share.


texts: Understanding postgres txid_current and pg_locks

texts:
I am trying to get a list of pg_locks relevant to the current transaction

eg

> BEGIN;
BEGIN
> ALTER TABLE ... ;
ALTER TABLE
> select locktype,virtualxid,transactionid,mode,relation from pg_locks;
  locktype  | virtualxid | transactionid |        mode         | relation
------------+------------+---------------+---------------------+----------
 relation   | [NULL]     |        [NULL] | AccessShareLock     |    11695
 virtualxid | 2/24699    |        [NULL] | ExclusiveLock       |   [NULL]
 relation   | [NULL]     |        [NULL] | AccessExclusiveLock |   801091
(3 rows)

Ok so what I think of as "my" lock here is the last one, the AccessExclusiveLock

But it has no transactionid or virtualxid.

If there is other activity in the db I will see other locks too in this list.

How do I filter out just the locks belonging to my current transaction, if they have null transaction ids?


texts: Have less tables with a lot of data or a number of tables with smaller amounts of data?

texts:
Lets say I have a number of tables that will have a type and status and I want the types/statues to be configurable by a end-user of the application (so I can't use enums here).  There are 2 different ways I am think about doing this:

Have Types and Statuses tables for each table that needs them.
Have one Types and Statuses table that every table that needs them uses

What are the benefits/disadvantages of each of these methods?  Which one would you go with?