texts: Can we change the database code page after the database creation?

texts:
Can we change below configuration of DB2 Database after DB is created.

 Database territory                                      = US
 Database code page                                      = 1208
 Database code set                                       = UTF-8
 Database country/region code                            = 1

Can some one give me the steps to do.


texts: SQL Server FCI installation with Mount Point permissions issues

texts:
Please help me get past this installation issue.

I am trying to install a SQL Server 2016 (SP1 slipstreamed) Failover Cluster Instance (FCI) on two Windows Server 2012 R2 VMs.

I have 3 cluster disks.

Data drive: E:\
Log mount point: E:\MSSQL\Log\L1
Tempdb mount point: E:\MSSQL\Data\Tempdb1

I first attempted to install directly to these root directories. After failing, I created subdirectories to install to. The service account and myself are local admins to the VMs and I explicitly granted full control on each of the subdirectories. Finally, I tried to ignore the mount points, for now, and just install to a subdirectory on E:. All attempts failed with the below error.

  Detailed results:   Feature:                       Database Engine
  Services   Status:                        Failed: see logs for details
  Reason for failure:            An error occurred during the setup
  process of the feature.   Next Step:                     Use the
  following information to resolve the error, uninstall this feature,
  and then run the setup process again.   Component name:
  SQL Server Database Engine Services Instance Features   Component
  error code:          0x84CF0004   Error description:             While
  updating permission setting for folder 'E:\MSSQL\Data\TempDB1\System
  Volume Information' the permission setting update failed for file
  'E:\MSSQL\Data\TempDB1\System Volume
  Information\ResumeKeyFilter.Store'. The folder permission setting were
  supposed to be set to
  'D:P(A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)(A;OICI;FA;;;S-1-5-80-419818685-2113908795-3893829424-1849583840-1690709397)'.
  Error help link:
  http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=13.0.4001.0&EvtType=0x88792597%400xBB814387&EvtType=0x88792597%400xBB814387

Below is an anonymized version of the config file being used for install.

;SQL Server 2016 Configuration File
[OPTIONS]

ACTION="InstallFailoverCluster"
SUPPRESSPRIVACYSTATEMENTNOTICE="False"
IACCEPTROPENLICENSETERMS="False"
IAcceptSQLServerLicenseTerms="True"
ENU="True"
QUIET="False"
QUIETSIMPLE="True"
UpdateEnabled="True"
USEMICROSOFTUPDATE="False"
FEATURES=SQLENGINE,REPLICATION,FULLTEXT,DQ,CONN,BC,SDK,SNAC_SDK
UpdateSource="MU"
HELP="False"
INDICATEPROGRESS="True"
X86="False"
INSTANCENAME="InstName"
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
INSTANCEID="InstName"
INSTANCEDIR="C:\Program Files\Microsoft SQL Server"
FAILOVERCLUSTERDISKS="Cluster Disk 7"
FAILOVERCLUSTERGROUP="SQL Server (InstName)"
FAILOVERCLUSTERIPADDRESSES="IPv4;10.10.10.17;Cluster Network 2;255.255.255.0"
FAILOVERCLUSTERNETWORKNAME="abc-123-IN"
AGTSVCACCOUNT="DOMAIN\sqlsvc-abc-123"
COMMFABRICPORT="0"
COMMFABRICNETWORKLEVEL="0"
COMMFABRICENCRYPTION="0"
MATRIXCMBRICKCOMMPORT="0"
FILESTREAMLEVEL="0"
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
SQLSVCACCOUNT="DOMAIN\sqlsvc-abc-123"
SQLSVCINSTANTFILEINIT="True"
SQLSYSADMINACCOUNTS="DOMAIN\AdminGroup"
SECURITYMODE="SQL"
SQLTEMPDBFILECOUNT="8"
SQLTEMPDBFILESIZE="8"
SQLTEMPDBFILEGROWTH="64"
SQLTEMPDBLOGFILESIZE="1024"
SQLTEMPDBLOGFILEGROWTH="128"
INSTALLSQLDATADIR="E:"
SQLBACKUPDIR="E:\MSSQL\Backup"
SQLUSERDBDIR="E:\MSSQL\Data"
SQLUSERDBLOGDIR="E:\MSSQL\Log"
SQLTEMPDBDIR="E:\MSSQL\Data"
SQLTEMPDBLOGDIR="E:\MSSQL\Data"
FTSVCACCOUNT="NT Service\MSSQLFDLauncher$InstName"


texts:
I can not start Sql Server agent to create job. Can anyone help me? .
I'm using Windows 7 Ultimate , Sql server 2008 Rc2
See this images:


texts: What units are 'reads' and 'writes' in dm_exec_query_stats

texts:
MSDN describes the various logical / physical read and write columns in dm_exec_query_stats as:

  Number of physical reads performed the last time the plan was executed.

Those columns are all 64 bit ints (bigint), so obviously larger numbers represent more reads / writes.

But what are the units of this column? Database pages? Kilobytes? Just an arbitrary number?

Example: for a reasonably expensive query in my database dm_exec_query_stats.total_logical_reads = 40412.

40412 of what?


texts: Which SQL table statistics are used after they are updated when NORECOMPUTE is turned on?

texts:
Background:
I have a large table whose values are significantly rewritten with a switch ~once a week, but the table's primary key values remain relatively constant. After the switch, statistics are guaranteed to be out of date on the table.
A consequence then is that user queries against this table after the switch are pretty much guaranteed to need to update statistics. This has lead them to timing out. (User queries have a 30 second time limit; these same queries running with up-to-date statistics complete in <1 second.)
To address this, I'm looking to manage statistics manually by (1) calling UPDATE STATISTICS after the  switch, and (2) turning on STATISTICS_NORECOMPUTE. User queries the rest of the week will then potentially use outdated statistics, but that should be fine.
Question:
Let's say I turn STATISTICS_NORECOMPUTE on for this table, have a constant stream of new queries against this table, and I call UPDATE STATISTICS. When will the new statistics be used after the UPDATE STATISTICS call is made? Will they be used by all queries that execute after the statistics transaction is made?
Put another way, I'm considering a SQL query in two phases: when it is submitted, and when it is executed. The query optimizer is responsible for telling a query which statistics to use. Does the optimizer create the query plan when the query is submitted, or when the query finally executes?


texts: Quality of Microsft's SQL Server Training/Certification

texts: I'm working as a SQL Developer in the healthcare industry and I have about 2 years experience.  I'm primarily using SQL Server 2012.  My company will pay for me to get additional training and/or certifications.  My first thought was to look at Microsoft, so I was looking here for possible certs or courses.  I'm wondering what others have done and/or would recommend?


texts: SQL: Dynamic Pivot Column, In the column's row value How to get Top 1 value

texts:
In my following query I need to get the top 1st record's value in pivot row value. So basically I get all parts and if there is a service entry with that part and a revision number I used that revision number from the service entry part table and if not i use the revision number from part table(as you can see I do coalesce).  Now I need to get the revision number from the most recent service entry if it exists based on the input date provided by the user and if that doesnt exist then colese will give revision number from part table. Currently it is not picking up the most recent service entry revision number

------Part Table------
ID    Description  RevisionNumber
123   xyz           100

-------ServiceEntryPart Table-----
ID   ServiceEntryID   PartID   PartRevisionNumber  SystemModuleID
1    20               123       123                 56
2    21               123       124                 56

-------ServiceEntryTable------
ID    SystemID    ServiceDateTime
20    31           1/1/2014
21    32           1/2/2014

So the correct query results  would be  as follows if I run the query on 1/3/2014. You can see that the revision number in the pivot value is the revision number from the service entry part table from the most recent service entry

   PartID  PartDescription  System System2
    123    xyz              124    124

if the part was not used in a service entry (no service entry part table record)then the result would look as follows.So the part record is still in the results but the revision number is the baseline-from part table since this part was not used in a service entry

   PartID  PartDescription  System1 System2
    123    xyz              100     100

Currently I get incorrect revision number and not the most recent one if the part was used in a service entry

   DECLARE @PivotColumnHeaders NVARCHAR(MAX)

SELECT @PivotColumnHeaders =
   COALESCE(
     @PivotColumnHeaders + ',[' +  cast(SystemFullName as Nvarchar) + ']',
     '[' + cast(SystemFullName as varchar)+ ']'
   )
FROM System
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
   SELECT *
   FROM (
select  Part.System as Subsystem, Part.ID as PartNumber, Part.PartDescription,System.SystemFullName,
COALESCE(ServiceEntryPart.PartRevisionNumber,Part.RevisionNumber) AS Revision
 from Part
    left outer join ServiceEntryPart on Part.ID = ServiceEntryPart.PartID
left outer join serviceentry
 on ServiceEntryPart.ServiceEntryID = (select top 1  ID from ServiceEntry where ServiceEntry.ServiceDateTime <= GETDATE() order by ServiceDateTime DESC)
left outer join System on System.PlatformID = Part.platformid

 ) AS PivotData
   PIVOT (
     MAX(Revision)
     FOR SystemFullName IN (
       ' + @PivotColumnHeaders + '
     )
   ) AS PivotTable
'
exec sp_executesql @PivotTableSQL

If I simply want to get  the most recent revision number for a given part from a service entry i can do the following and it gives me the correct but im interested in doing it in a pivot and show every part from the part table

select top 1 ServiceDateTime,ServiceEntry.ID,ServiceEntryPart.PartRevisionNumber from ServiceEntry
inner join  ServiceEntryPart on ServiceEntry.ID = ServiceEntryPart.ServiceEntryID
where PartID = 670220 and ServiceDateTime <= getdate()
order by ServiceDateTime desc


texts: Always on(SQL server 2016) is configured between four standalone servers Server1,Server2,Server3,Server4. These servers contains one availability Group(AG01) which contains 3 databases. How to apply SQL server patch for these servers? Do we need to apply patch for all servers at same day? These servers are production servers.


texts: Get records which met SELECT condition over time and range them by period of time when condition was met

texts:
I have the following task: I want to execute fixed SELECT statement against SQL Server table with some fixed condition and I need to do it after specified period of time multiple times... Let's say N times with 1 minute interval between each execution with final goal to display only records which were returned all N times... Final goal is to check if some specific records persist in select results over time or maybe even order them by time they were returned - i.e. one record was returned in 3 first SELECT executions and interval was set to 1 minute hence I can say it was persistent for 3 minutes, another record was returned 5 times - so I can conclude it persisted / met my condition for 5 minutes.

Nature of my condition/data dictates that records not meet this condition for more than one minute and I want to catch abnormal records which meet my condition for prolonged period of time.

From explanation above it seems that it all involves some cycle, temp table and condition to put results in yet another temp table. But I'm posting it here to check if I maybe missing some simpler approach/solution which will allow to achieve the same result?


texts: How do you select*from with multiple conditions?

texts:
I'm trying to return the records of a table where three columns match up, but i'm getting no results in return. Any suggestions?

SELECT *
FROM Flights
WHERE Departure_IATA_Code = 'STN'
  AND Arrival_IATA_Code = 'DBN'
  AND Departure_Date = 2018-04-16

Create table statements

CREATE TABLE `Flights` (
 `Flight_Number` varchar(50) NOT NULL DEFAULT '',
 `Aircraft_ID` int(7) NOT NULL DEFAULT '0',
 `Departure_IATA_Code` varchar(3) NOT NULL DEFAULT '',
 `Departure_Date` date NOT NULL,
 `Arrival_IATA_Code` varchar(3) NOT NULL DEFAULT '',
 `Cost` int(7) NOT NULL,
 `Departure_Time` time NOT NULL,
 `Flight_Duration` time NOT NULL,
 PRIMARY KEY (`Flight_Number`,`Aircraft_ID`,`Departure_IATA_Code`,`Departure_Date`,`Arrival_IATA_Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


texts: Custom unique constraint, only enforced if one column has a specific value

texts:
Is it possible to have a custom unique constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter).
If type is 'true', then I want the combination of type and subset to be unique. Otherwise, there is no constraint. I'm using PostgreSQL 8.4 on Debian.


texts: getting oracle error ora-00103 when I try to execute a package in oracle 10g

texts:
I have to delete and repopulate tables on a remote database.To access the remote tables I have created synonyms. my package is as follows

create or replace
PACKAGE eco_push IS
PROCEDURE waters_data_push;
END eco_push;

Package body is as follows

create or replace
PACKAGE BODY eco_push is

procedure waters_data_push
   IS

 --DELETING FROM WATERS_SITE

DELETE FROM watersitesyn;  -------------   error line (from)

-- Insert into table WATERS_SITE
     INSERT INTO watersitesyn
            (SITE_ID,SITE_NAME,
            CLI_NAME,ECOTRAC_NO,
            APP_REF,SEQ_NO,
            ACTIV_CODE,LOT_PLAN,
            FACIL_LOC,LAT,
            LON
            )

    SELECT DISTINCT
           st.ID,
           st.NAME,
           cli.NAME,
           cli.ECOTRACKNO,
          /*app.APPROVALREF,*/  CASE WHEN to_date(appst.VALIDTO)IS NOT NULL THEN (select app.APPROVALREF from APPROVALSITE appst, APPROVAL app where  SYSDATE BETWEEN to_date(appst.VALIDFROM) and to_date(appst.VALIDTO)and appst.site_id = st.ID)
                                     WHEN to_date(appst.VALIDTO) IS NULL THEN (select app.APPROVALREF from APPROVALSITE appst, APPROVAL app where  SYSDATE >to_date(appst.VALIDFROM) and appst.site_id = st.ID)
                                END,
           app.APPROVALSEQ,
           luact.CODE,
           CONCAT(lp.LOT,lp.PLAN),
           st.LOCATION,
           geop.LATITUDE,
           geop.LONGITUDE
     FROM  SITE st,
           CLIENT cli,
           APPROVAL app,
           APPROVALSITE appst,
           LU_ACTIVITY luact,
           LOTPLAN lp,
           GEOPOINT geop
     WHERE
          sysdate  between  to_date(appst.VALIDFROM) and to_date(appst.VALIDTO)
          AND  geop.ID = st.GEOPOINT_ID
          AND  lp.SITE_ID = st.ID
          AND  appst.APPROVAL_ID = app.ID;

......

I am getting the following error

Error: PLS-00103: Encountered the symbol "FROM" when expecting one of the following:     constant exception <an identifier>    <a double-quoted delimited-identifier> table long double ref    char time timestamp interval date binary national character    nchar

How to solve the error?

Thanks in advance


texts: Decrypting Previously Encrypted Data Returns NULL

texts:
I'm having trouble decrypting data that has previously been encrypted and stored in a table.

After opening my symmetric key and encrypting the data using EncyptByKey(), I get NULL values when attempting to decrypt using DecryptByKeyAutoCert().

Below is what I use for my setup and attempt at decryption:

Create Keys and Cert

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'master';

CREATE CERTIFICATE cert_dbKeys
   ENCRYPTION BY PASSWORD = 'abcd'
   WITH SUBJECT = 'Database encryption key',
   EXPIRY_DATE = '20201031';

CREATE SYMMETRIC KEY key_dbKeys
   WITH ALGORITHM = AES_128
   ENCRYPTION BY CERTIFICATE cert_dbKeys;

Encrypt Data

OPEN SYMMETRIC KEY key_dbKeys
DECRYPTION BY CERTIFICATE cert_dbKeys WITH PASSWORD = 'abcd'

(retrieve and store selected data with)
EncryptByKey(Key_GUID('key_dbKeys'), @clean_data);

CLOSE SYMMETRIC KEY key_dbKeys;

Decrypt Data

SELECT DecryptByKeyAutoCert(cert_id('cert_dbKeys'), N'abcd', table.data) FROM table

As a result of this, I get a column of nulls.

If possible, please assist in helping me determine as to why I am retrieving NULL values instead of the original data or if there is a better way to carry out this task.


texts: Alternative to TEMPORARY tables capable of re-opening in a query?

texts:
I use TEMPORARY table for calculation of temporary data, but TEMPORARY tables in mysql has a big limitation, which made it useless for me. A table cannot be re-opened in a query. This mean I cannot JOIN the table with itself, and add sub-query. It is almost impossible to process data without JOIN and sub-query.

Thus, I must give up TEMPORARY table. A possible alternative is to create and delete a permanent table on each script run. However, I think it is not good from database administration point of view to DELETE and DROP tables.

Is there any practical alternative to TEMPORARY tables with possibility of re-opening the table?


texts: Too many Partitions, removing older years

texts:
This is a follow-on to this question a few years ago Splitting Existing Partitions in SQL 2014 where now there are partition years for 2011 all the way to 2021.
I need to archive partitions 2011 to 2015 by moving the data from these partitions to a new table/database, and then archive this database, and remove the partitions for 2011 to 2015 from the original Log table.
I'm successfully moving the data out to a Log_Archive table with the following statement.
ALTER TABLE dbo.Log
    SWITCH PARTITION $PARTITION.pfYearlyPartition_Log('20120101')
    TO dbo.Log_Archive PARTITION $PARTITION.pfYearlyPartition_Log_Archive('20120101');

What I'm not sure of is how I now remove that partition from the Log table, as it's still there but with 0 rows within it. Can someone advise? You can see below I've successfully moved the data from Log to Log_Archive but I'm left with 0 rows in the original Log table that I want to remove.

I'm hoping I don't need to re-partition the whole table as it's very large.


texts: Insert missing rows to subscriber of Transactional Replication

texts:
I have established SQL Server Replication between SQL Server 2005 and 2008 R2. SQL Server 2005 is the publisher while SQL Server 2008 R2 is the subscriber.  The replicated database is about 40GB.

My replication was working most of time OK for few years. A few days ago I saw that I have missing rows inside few tables. When I check manually I see which rows I have missing. When I check replication monitor I see no errors and all looks nice. My trace tokens said that there is no latency.

But I know that there is no rows.

Now I want to manually transfer these rows from publisher to subscriber.

I do not want to reinitialize my replication because when I do that all my statistic go away and my users become to suffer with performance when start using fresh database.

Other thing that my publisher is very poor with performance and reinitialization took a long time to finish and I do not have enough time to stop my app during that process.

Is there an easy way to force the publisher to resend missing data to subscriber beside reinitializing the whole replication again?  Is there a way to keep my statistic and indexes on subscriber database after reinitializing that, so I do not lose my performance?


texts: How to join these two tables without Foreign Key?

texts:
I want to generate monthly report and the total in a year based on position(jawatan) that has been applied.
Table 1 : tpermohonan (mohon_id pemohon_id mohon_tarikh mohon_online)

mohon_id
pemohon_id
mohon_tarikh
mohon_tkhluput

1
2
2018-12-23
2019-12-23

Table 2 : kod_jawatan (jawatan_id jawatan_kod jawatan_nama)

jawatan_id
jawatan_kod
jawatan_nama

1
A0001
accountant

My desired output is :

Bil
Jawatan(Position)
Jan
Feb
March
Apr
May
Jun
July
Aug
Sep
Oct
Nov
Dec
Total


texts: Application connection pooling load balancing the transactions on 18c RAC

texts:
Application connecting a 2 node 18c RAC.
JVMs are using connection pool and transactions are routed to the nodes via the connections in the pool. So the initial load across the nodes are distributed by SCAN listener. But the question is how does the existing connections in the pool distributes the next slot of transactions evenly across the 2 nodes, so that they are equally balanced.


texts:
SELECT
 RegionManager,
 [Face-to-Face],
 [Phone],
 [Face-to-Face]+[Phone] as[Grand Total]
FROM
(select
 ed.FRST_NM + ' ' + ed.LAST_NM AS RegionManager
 ,ACTVTY_TYP
 ,CALL_ID
 --,Month(CALL_DT) callmonth
FROM CODS_EDW.dbo.TERR_HIER th
LEFT JOIN CODS_EDW.dbo.[EMP_ROSTR] er ON er.[GEOG_ID] = th.GEOG_CD
LEFT JOIN CODS_EDW.dbo.EMP_DIM ed ON ed.EMPL_ID = er.EMPL_ID
LEFT JOIN CODS_EDW.dbo.[CALL] c
ON  c.TERR_CD = GEOG_CD + ' ' + GEOG_NM
WHERE th.SF_ID = 4
AND th.GEOG_LVL = 3
) source
PIVOT
(
    COUNT(CALL_ID)
    FOR ACTVTY_TYP
    IN ([Face-to-Face],[Phone] )
) AS pvtMonth
order by RegionManager

Using pivot i got result like this:

RegionManager   Face-to-Face    Phone   Grand Total
Craig Irwin     73  6   79
Holly Murphy    27  124 151
Julie Brown     37  28  65
Kevin Borth     52  35  87
Linda Krueger   46  23  69
Robert Bell     66  21  87

I want the output like below.(The columns might change dynamically, needs to calculate Sum by row wise)

Row Labels  Face-to-Face    Phone   Grand Total
Craig Irwin     73  6   79
Holly Murphy    27  124 151
Julie Brown     37  28  65
Kevin Borth     52  35  87
Linda Krueger   46  23  69
Robert Bell     66  21  87
Grand Total     301 237 538


texts: How to generate a unique barcode without using identity or sequence in SQL Server 2012

texts:
We're currently building an application where customers need to have 1 unique barcode. Each time a new customer has been added to the system, a new unique barcode needs to be generated for that customer.

We're using 12 digits for this barcode, but the first 6 digits are fixed, leaving me with only 6 digits left to generate a unique number.

At first I wanted to do this based on the primary key of the table customer, which is an identity column. And this would have worked prior to SQL Server 2012 because apparently, whenever the SQL Server 2012 service gets (forcefully) restarted, the identity column jumps by 1000 or 10000 (depending on the datatype of your column), which would skyrocket the amount of digits being used, risking to exceed the 6 digit limit. Same with the Sequence functionality.

Can anyone point me out how you would do it? I've been thinking of storing a number myself in a column with a unique constraint and add 1 to it each time a customer has been added but this makes me feel very uncomfortable.

Thanks for reading this far, any advice is appreciated.

Zeep


texts: Why is this explicit cast causing problems only with a Linked Server?

texts:
I am querying data from a linked server through a view on the origin server.
The view has to include a couple of standardized columns, such as Created, Modified and Deleted, but in this case the table on the source server doesn't have any suitable info. The columns are therefore explicitly cast to their respective types. I updated the view, changing a column from

NULL AS Modified

to

CAST(NULL as DateTime) as Modified

However, after performing this update, the view is triggering the following error message:

  Msg 7341, Level 16, State 2, Line 3
  Cannot get the current row value of column "(user generated expression).Expr1002" from OLE DB provider "SQLNCLI11" for linked server "".

We have done this "explicit cast"-change generally across the origin server without worries, and I suspect the issue might be related to the version of the servers involved. We don't really need to apply this cast, but it feels cleaner. Right now I'm just curious as to why this is happening.

Server Version (origin):

  Microsoft SQL Server 2012 - 11.0.5058.0 (X64)   May 14 2014 18:34:29   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

Server Version (linked):

  Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)   Jun 17 2011 00:54:03   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

Edit
I just realized I made a mistake by not posting all the columns in question, and I must apologize for leaving out an important detail. I don't know how I didn't notice this sooner. The question still remains, though.

The erroneous cast does not happen with the cast to DateTime, but with a column being cast to UniqueIdentifier.

This is the culprit:

CAST(NULL AS UniqueIdentifier) AS [GUID]

UniqueIdentifiers are supported on SQL Server 2008 R2, and as mentioned in the comments, the query performed by the view runs fine on the linked server.


texts:
I am reading a book on SQL 2008 internals and troubleshooting by Christian Bolton, Brent Ozar and a old book written by late Ken Henderson and I am getting confused and curious about SQL server's dynamic memory manager.

Can someone confirm my understanding of SQL Server’s dynamic memory management is correct?

In SQL Server 2005/2008/2008R2 x64

a)
Max amount of memory taken by locks is limited to 60% of the buffer pool memory (max server memory). By disabling lock escalation and doing a huge insert I can see that SQL Server can take up to 60% of buffer pool memory before throwing error number 1204 (out of memory for locks)

b) Plan Cache size is limited to

 a) Number of plans in cache -- 160K

 b) Plan Cache upper limit: 75% of server memory from 0-4GB + 10% of server memory from 4Gb-64GB + 5% of server memory > 64GB.

Once this threshold is reached SQL server will start evicting execution plans from the cache until SQL server stop seeing internal plan cache pressure

c) Workspace memory is limited to 75% of max server memory and a single query can not take more than 25% of  the (available 75% max workspace memory) unless overridden by resource governor.

My question is

Is there a limit on max memory taken by database page cache before lazy writer start evicting pages out of memory? How does it controlled? By looking at free pages count in the buffer…**

How sql server controls memory taken by "transaction log cache" before flushing log to the disk?

Thanks in advance.


texts: Remove collation from all database columns

texts:
I have a SQL Server database where the collation is set on it. However I am running into problems as the collation on some columns in the database varies from the database collation.

I would like to remove collation from the columns and use the collation set on the database.

I am working with SQL Server 2012.

How can I do this? Can a script do this?


texts: finding keywords in text using full text search in PostgreSQL

texts:
I'd like to determine if a text input contains any keywords from a known list, but also show which ones are found.

E.g with Keywords: ['fox', 'jump', 'dog', 'foo', 'bar']

'the quick brown foxes jumped over the lazy dogs'

yields

['fox','jump','dog']

I appreciate that I can do boolean matching, but I can't work out if I can return the matched keywords, as opposed to a highlighted input snippet.

I'd like to use FTS so i can benefit from finding some synonyms etc.

Is this possible in Postgres?


texts: SQL query optimization in Prestashop e-Commerce solution

texts:
I'm working on a layered navigation module within Prestashop e-Commerce solution.

I'm trying to optimize a SQL query which creates a temp table during execution.  I think this is creating a bottleneck in MySQL.

This is the query:

SELECT p.*, p.id_category_default, pl.available_later, pl.description_short,
pl.link_rewrite, pl.name, i.id_image, il.legend, m.name manufacturer_name,
DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL 30 DAY)) > 0 AS new
FROM `ps_category_product` cp
LEFT JOIN ps_category c ON (c.id_category = cp.id_category)
LEFT JOIN `ps_product` p ON p.`id_product` = cp.`id_product`
LEFT JOIN ps_product_lang pl ON (pl.id_product = p.id_product)
LEFT JOIN ps_image i ON (i.id_product = p.id_product AND i.cover = 1)
LEFT JOIN ps_image_lang il ON (i.id_image = il.id_image AND il.id_lang = 2)
LEFT JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
WHERE p.`active` = 1
    AND c.nleft >= 2
    AND c.nright <= 29
    AND c.active = 1
    AND pl.id_lang = 2
AND p.id_product IN (74, 78, 130, 146, 168, 169,
178, 195, ..., 297, 302, 1986, 1987, 1988, 1993, 1999, 2000, 2001)
GROUP BY p.id_product
ORDER BY p.date_upd desc LIMIT 0,48

I know the temp table is created because of the GROUP BY but I don't know how to remove it.  If the same process could be done with PHP instead of SQL, that could be a solution.


texts: Recovering data from SQLite .db File Data

texts:
To give a bit of background, I was looking for the occurrence of specific text from within multiple SQLite DB files.  I initially tried a simple grep of the text on the multiple files and grep found matches in one of the .db files.

# grep "mytext" *.db
Binary file sqlite.db matches

So I opened the sqlite.db file using a hex (note HEX) editor and searched for the same text.  It was found in multiple spots and with the HEX editor, and as I can see text strings, I was able to actually see the text for which I was looking.

So I then opened the .db file using SQLite.  The file had about three dozen tables.  So I first tried (from within SQLite):

sqlite> .output sqlite.dbdump.sql
sqlite> .dump

...then...

# grep "mytext" sqlite.dbdump.sql

...no results were found...weird.

I went a step further and tried:

# echo ".tables" | sqlite3 sqlite.db | while read tablez
# do
#  echo "select * from $tablez" | sqlite3 sqlite.db
# done | grep -i "mytext"

...no results were found...weird again.

Can anyone shed any light on where this matching text may be within the SQLite DB file?  Are their hidden tables that my .dump and table selects may not be searching?

Since posting this, I believe those are deleted records. I now need to find the best way to attempt to recover instead of vacuuming.


texts: KNN search two arrays with 2000 elements

texts:
What I did:

installed postgresql 9.3.1
installed postgis 2.1.2
I tried to find the distance by selecting column input_variable_1 with value 50.

I got the following result.

select input_variable_1 <-> 50  as distance  from test_ata limit 10;
 distance
       48
        2
       17
       29
       44
       37
       45
       17
       49
        2
(10 rows)

what i want:

input: column which stores array values say 2000 elements in a single record.

I came across cube extension and postGIS there i can get distance between two points by using tree and distance concepts for lower dimentsions upto 100.

How to find the distance between two arrays having elements atleast 2000 in postgresql?
which tree is best for 2000 dimension indexing in postgresql?
finally i want to know how to use KNN search in postgresql?


texts: How to get a count of how often a name/string appears

texts:
I have a database of devices owned by users, I want see how many users own more than 2 devices, but it would suffice to just get the total count.
Real query:
Select tblAssetUserRelations.Username As 'Besitzer',
  tblAssets.AssetName As 'Name'
From tblAssets
  Inner Join tblAssetUserRelations On
      tblAssets.AssetID = tblAssetUserRelations.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where (tsysAssetTypes.AssetTypename = 'Windows' Or
    tsysAssetTypes.AssetTypename = 'Apple Mac')
Group By tblAssetUserRelations.Username,
  tblAssets.AssetName
Order By 'Besitzer',
  'Name'

As an more simple example we can use those tables:
Username         DeviceID
-----------------------------
user1            1
user1            2
user2            3
user3            4
user4            5
user4            6

DeviceID         DeviceName
-----------------------------
1                NB001
2                DEV001
3                NB002
4                DEV002
5                NB003
6                DEV003

Expected Result:
Username         Count
user1            2
user2            1
user3            1
user4            2

Any idea how to get the expected result?
EDIT: wrong naming of column


texts: Shrink Transaction Log While Using AlwaysOn Availability Group 2014

texts:
I'm running a SQL Server 2014 Availability Group with a single secondary in asynchronous mode.

My logs in all the Availability Group databases keep growing and the built-in shrink tool only takes them down a notch.

Can anyone recommend a set of troubleshooting steps to find out why the logs keep growing or how to automate log shrink in Availability Group databases?


texts: Exporting results of a query in SQL developer without having to execute the query twice

texts: Is there anyway to export the results of a query in Oracle SQL Developer without having to execute the query twice? Running a SQL query in worksheet, then Right click on Result Set window -> Export Data -> Text will run the query a second time.


texts: Function without parameters, and calling a function inside

texts:
I'm new with PostgreSQL and feeling quite overwhelmed..
What I'm trying to achieve is something like this:
Wanted outcome

year
descrip
percent

2019
Word word word
0.10

2018
Word word sad
0.80

2017
Word empty word
0.15

etc
Word word word
0.41

Where percent is function_A divided by function_B** (function_A / function_B).
I already have the functions

function_A has two parameters : year (extracted from status_date) and code. Returns the number of orders defined by the previous
parameters.
function_B has one parameter: year (extracted from status_date).
Returns the total number of orders by year.

Function
This is what, wrongly, I did so far - including code from nbk's original answer:
The function shouldn't have any parameters.
CREATE OR REPLACE FUNCTION summary_orders()
RETURNS TABLE (retyear numeric, order_desc text, percentage bigint) AS $$
DECLARE
    retyear numeric DEFAULT 0;
    descrip text DEFAULT 0;
    percent bigint DEFAULT 0;
BEGIN
    RETURN
        QUERY SELECT CAST(EXTRACT(year FROM created_dt) as numeric),
                order_desc as text,
                ROUND(function_A(inyear,incode))::decimal / function_B(inyear) as percent
        FROM orders o
        WHERE EXTRACT(YEAR FROM o.creation) = inyear
        AND o.order_code = incode
        AND o.descrip
        GROUP BY EXTRACT(year FROM o.creation), o.code
        ORDER BY retyear ASC;
END;
$$
LANGUAGE plpgsql;

To call the function I expect to use:
SELECT * from summary_code()

My data looks something like this:
'orders' Table
CREATE TABLE orders (
    id INT NOT NULL,
    code INT NOT NULL,
    service_id INT NOT NULL,
    descript CHARACTER VARYING(50),
    creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status_date TIMESTAMP,
    created_user INT
    );

Records
INSERT INTO orders
(id, code, service_id, descript, creation, status_date, created_user)
VALUES
(100,2394,558151,'Requested not today','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Fulfilled blue squit','2018-06-16 11:12','2018-06-18 14:08',1),
(100,2394,558151,'Requested blue kit','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2395,558152,'Requested pony','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2396,558153,'Requested sauron','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Requested I'm tired','2019-06-16 11:12','2019-06-18 14:08',1);

I'm sorry if this comes as a little cheeky, but I don't know where to start.


texts: incompatibility of max_allowed_packet in mysql

texts:
I have a client and I want to dump a row such as :

$ mysqldump -uroot -p pahlevan_azad mp_posts --where="id='655'"  --max-allowed-packet=1024 > dump.sql
Warning: option 'max_allowed_packet': unsigned value 1024 adjusted to 4096
Enter password:

Because I have the following value in server :

SHOW VARIABLES LIKE 'max_allowed_packet';
max_allowed_packet  1024

And I don't have any permission to change it.I only have a phpmyadmin.

What do you recommend?
How can I set max_allowed_packet such as server?

Update:
even I tested with 1K, But I got the above error.


texts: Implementing a unique primary key for distributed databases

texts:
I've got an issue to find a way to create a global primary key to a given relation (namely "Patient")

In a distributed database there are three local sub-systems: surgery department, emergency department and some other department. In each department, the "Patient" table has a local primary key that is different to the others sub-systems.

I read about global and local indexing in distributed DB. Can it give a solution to that? Or is there any other technique to solve it?

Thanks in advance,


texts: Solving high page load time using SQL Profiler

texts:
I'm looking into a performance of a login page taking 10-13 seconds to load. I'm using a DEMO database to test the performance and tweak to see any improvement. I'm using SQL Profiler to capture what is going on during the login and see what I can change. However, I'm noticing a few oddities.

If I store to table the "duration" column in profiler is [n]*1000
compared to what is on the profiler GUI. (ie. GUI Windows shows
"exec proc1" and duration 50, but in the SQL trace table "exec
proc1" shows 50000.
If I add up all the duration values they are
    not even 1/10th of the time the page takes to load. (I know profiler
    will slow down the page load, but even without profiler a "fresh"
    page load is 10 seconds+ on average and profiler duration (ms) add
    up to roughly 1.1 second.
Also in between each run I am running CHECKPOINT, DBCC
DROPCLEANBUFFERS, DBCC FLUSHPROCINDB () to ensure each test is
"fresh".

Is there anything I'm doing wrong that would make these discrepancies occur? Is there anything you would recommend to change in my approach to solving this problem?


texts: Managing Transaction Log Size using a Maintenance Plan

texts: Are there any best practices for configuring a Maintenance Plan in SQL Server 2008?  Currently, I'm removing database backups and transaction logs greater than 40 hours old, then backing them up.  Problem I've seen is that the transaction log is still very large.  Should I be including a Shrink Database Plan task?


texts:
In SQL Server I've always found it a pain to get the max rows for a dataset, I'm looking for a list of the methods to retrieve the max rows with some guidance on performance and maintainability.

Sample Table:

DECLARE @Test TABLE (ID INT IDENTITY(1,1), name VARCHAR(50),
                     dateOfBirth DATETIME, TaxNumber varchar(10))

INSERT INTO @Test (name, dateOfBirth, TaxNumber)
SELECT 'Fred', convert(datetime, '25/01/1976', 103), '123' UNION ALL
SELECT 'Bob', convert(datetime, '03/03/1976', 103), '234'  UNION ALL
SELECT 'Jane', convert(datetime, '13/06/1996', 103), '345' UNION ALL
SELECT 'Fred', convert(datetime, '14/02/1982', 103), '456' UNION ALL
SELECT 'Bob', convert(datetime, '25/10/1983', 103), '567' UNION ALL
SELECT 'Jane', convert(datetime, '12/04/1995', 103), '678' UNION ALL
SELECT 'Fred', convert(datetime, '03/03/1976', 103), '789'

select * from @Test

Gives:

ID          name      dateOfBirth             TaxNumber
----------- --------- ----------------------- ----------
1           Fred      1976-01-25 00:00:00.000 123
2           Bob       1976-03-03 00:00:00.000 234
3           Jane      1996-06-13 00:00:00.000 345
4           Fred      1982-02-14 00:00:00.000 456
5           Bob       1983-10-25 00:00:00.000 567
6           Jane      1995-04-12 00:00:00.000 678
7           Fred      1976-03-03 00:00:00.000 789

If I want to retrieve the oldest person (group by name) full details, what methods can I use?

Desired output:

ID          name      dateOfBirth             TaxNumber
----------- --------- ----------------------- ----------
1           Fred      1976-01-25 00:00:00.000 123
2           Bob       1976-03-03 00:00:00.000 234
6           Jane      1995-04-12 00:00:00.000 678


texts: SSMS Oracle import using OpenQuery over LinkedServer (Oracle Provider for OLEDB) - XML Parsing: unable to switch encoding

texts:
I am trying to migrate the data from an OracleDB to an SQL Server Express database. I'm doing this by openquerying a linked server (data provider based on Oracle Provider for OLEDB). Everything works fine for the tables that contain simple data types. But when I get to the table that contains an XML column it returns an error:

Msg 9402, Level 16, State 1, Line 24
XML parsing: line 1, character 60, unable to switch the encoding

For that I do...

INSERT INTO [datab].[dbo].[tr] (ID, CREATED, UPDATED, COMPANY, INTERNAL_MESSAGE, EXCHANGE_MESSAGE)
SELECT * FROM OPENQUERY(DATAB_ORACLE,
'SELECT id, CAST(created AS DATE) AS created, CAST(updated AS DATE) AS updated, company, XMLSerialize(DOCUMENT internal_message AS CLOB) AS internal_message, exchange_message FROM datab.tra')

I have no clue as to what encoding the Oracle column has, neither for the MSSQL column (probably default).

Any suggestions?

LE: Please note that a solution relying on Oracle CONVERT() function (with the corresponding encoding type) doesn't seem to work.


texts: MySQL cluster: Failover between data node

texts:
Today, I do some expirements for understanding MySQL cluster better.

MySQL cluster works fine:

ndb_mgm> show

Connected to Management Server at: localhost:1186

Cluster Configuration

[ndbd(NDB)]2 node(s)

id=2@192.168.56.2  (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0, *)

id=3@192.168.56.3  (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0)

[ndb_mgmd(MGM)]1 node(s)

id=1@192.168.56.1  (mysql-5.6.19 ndb-7.3.6)

[mysqld(API)]1 node(s)

id=4@192.168.56.4  (mysql-5.6.19 ndb-7.3.6)

Case 1:
I turn off management node by killall ndb_mgmd then kill a data node by killall ndbd. I see remain data node is turned off although I don't do anything with it. There is no live data node so sql node is impossible to get or change data.

Case 2:
I keep management node live. I kill a data node by killall ndbd. I see the remain data node is sitll living so sql node still works well.

I have a question. Should I keep the management node for controlling data nodes ? I don't read the mysql reference manual mention the similar case.

Thank you in advance.


texts:
We have a new server which we are migrating too and want to see the potential benefits of the hardware.  So the idea is that we capture a workload and replay this against our new box.

My question is however in regards to seeing the performance benefits.  I will be running some perf counters but ideally we want to see how much quicker individual queries are.

I will have this information in the initial workload capture, however when I replay this on my new server do I simultaneously need to run a trace to capture the new workload information also.  In effect running SQL profiler twice at the same time for replaying and capturing?


texts: Get latest status of an item in the database

texts:
I have this table:

ID|Item-FK |timestamp|status
=============================
1 | 123    | ...     | OK
2 | 123    | ...     | FAILED
...

I want to query get all items which last status was FAILED.

If an item failed yesterday, and is OK today, I don't want to see it.


texts: Is `count(*)` ever guaranteed to return the same result in a transaction at any transaction level?

texts:
If I create a table.

CREATE TABLE foo AS
SELECT CASE WHEN random() > 0.5 THEN x END AS x
FROM generate_series(1,10) AS x;

And, then I run the following in a transaction

BEGIN;
  SELECT count(*)
  FROM foo
  WHERE x IS NOT NULL;

  --time

  SELECT count(*)
  FROM foo
  WHERE x IS NOT NULL;
END;

Under what transaction level is my result guaranteed to remain the same in the transaction?


texts: Inconsistent information from different DMVs

texts:
I've a server with 12 GB of physical RAM. I just lowered its 'Max Server Memory (MB)' setting from 10 GB to 8 GB.

If I look at the processes' committed memory in Perfmon, I see:

select *
from sys.dm_os_performance_counters
where [object_name] like 'SQLServer:Memory Manager%';
go

Target Server Memory (KB):  8388608
Total Server Memory (KB):   8388608

Showing that SQL Server has already relased the additional 2 GB of memory from the Buffer Pool.

However, if I look at the sys.dm_os_sys_info:

select [bpool_committed]
     , [bpool_commit_target]
     , [bpool_visible]
from sys.dm_os_sys_info;
go

I get inconsistent results:

bpool_committed     = 1048576
bpool_commit_target = 1048576
bpool_visible       = 1048576

Shouldn't sys.dm_os_sys_info reflect the information on the performance counters?

I'm running Microsoft SQL Server 2005 SP1 on Windows Server 2003.


texts: One MySQL table for all data vs Several Different Engines MySQL Tables

texts:
I did quite good research and went through some information on this regard.
But I am not satisfied yet, so preferred to ask directly about it.

I'm designing the schema of an application database, which is assumed to have tins of millions of records.

Some of these are frequently updated, and some are very rarely updated.
Hence, I decided to spread data on different tables according to the nature, and JOIN them in queries.

So, is this better or.. going with the one table (no JOINs)?

Assume it is a Rent-A-Car management system..

Here are the tables:

DESC cars
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| id              | bigint(20) unsigned | NO   | PRI | NULL    | ai    |
| brand           | varchar(40)         | NO   |     | NULL    |       |
| engine_capacity | tinyint(4)          | NO   |     | NULL    |       |
| model           | tinyint(4)          | NO   |     | NULL    |       |
| serialnumber    | bigint(20)          | NO   | UNI | NULL    |       |
+-----------------+---------------------+------+-----+---------+-------+

AND

DESC cars_data;
+---------------+----------------------+------+-----+---------+-------+
| Field         | Type                 | Null | Key | Default | Extra |
+---------------+----------------------+------+-----+---------+-------+
| carid         | bigint(20) unsigned  | NO   | PRI | NULL    | ai    |
| car_status    | enum('1','2','3')    | NO   | MUL | 1       |       |
| base_location | point                | NO   | MUL | NULL    |       |
| driver_id     | bigint(20) unsigned  | NO   | MUL | NULL    |       |
+---------------+----------------------+------+-----+---------+-------+

Both those tables, are assumed to be very rarely updated, thus I used for them MyISAM for next reasons:

Need to store Point data and use Spatial Index on them.
Saving disk space (since MyISAM tables are smaller in size).
Easier to maintain (as per my experience, is that right?).

Here is the very frequently updated table:

DESC cars_extra;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| carid         | bigint(20) unsigned | NO   | PRI | NULL    | ai    |
| odometer_km   | tinyint(6) unsigned | NO   |     | NULL    |       |
| last_maint    | bigint(20) unsigned | NO   |     | NULL    |       |
| last_update   | bigint(20) unsigned | NO   |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+

I used for last table InnoDB for one single main reason:

Avoid table-level locks during updates.

Advises, and general notes would be more than appreciated :-)

Also, are joins usually a con or pros (surly with the use of proper indexed keys).


texts: One time data conversion in Oracle tables with excel data

texts:
First of all I have very basic knowledge in back end

I need to do an one time data conversion in an Oracle table with a excel sheet data.

Excel sheet have data in below format

OldData NewData
12130   12130
12810   12810
17330   17331

So what I need to do is , I have to compare old data with one of the column in an Oracle table(say a colum called Modifydata in masterdaataTable) and if any column value matches with OldData value in excel , then replace that value with Newdata value from Excel sheet.

I have

OldData NewData
12130   12130
12810   12810
17330   17331

Note:

I have tried using external tables. Since I don't have the privileges to create directory and all, it didn't work out.
I have tried "'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\foldername\spreadsheetname.xls;'," logic also.But seems this will work only for MS SQL DB.


texts: Installing CU21 on SQL Server 2017 Enterprise

texts:
Please give me advise if you have any, related to my case. Problem appears on stage of CU21 installation. Update process stops services, installing updates, but after installation completes and services going up it's keep trying to get service up and after several attempts gives below error and installation stops:
(01) 2020-08-31 04:00:46 Slp:     Message:
(01) 2020-08-31 04:00:46 Slp:         The NT service 'MSSQLSERVER' could not be started.
(01) 2020-08-31 04:00:46 Slp:     HResult : 0x84b20001
(01) 2020-08-31 04:00:46 Slp:         FacilityCode : 1202 (4b2)
(01) 2020-08-31 04:00:46 Slp:         ErrorCode : 1 (0001)

When I'm checking version, it's indicate that server updated to CU21. But I'm afraid of any problems in future. Additionally I've checked it with System Configuration Checker and it didn't showed me any error. Could you please tell me any way to check installation status or can it be there any problems if I'll use sql server in this case?


texts: What would cause an orphaned ##MS_PolicyEventProcessingLogin##?

texts:
This morning I noticed my SQL Log was filling with the following message:

  The activated proc '[dbo].[sp_syspolicy_events_reader]' running on queue 'msdb.dbo.syspolicy_event_queue' output the following:
  'Cannot execute as the database principal because the principal "##MS_PolicyEventProcessingLogin##" does not exist, this type of principal cannot be impersonated, or you do not have permission.'

Running the following EXEC sp_change_users_login 'report' revealed that the login had in fact been orphaned.

I was able to fix it by running the following as recommended in this MSDN post.

EXEC sp_change_users_login
    'Auto_Fix', '##MS_PolicyEventProcessingLogin##',
    NULL, 'fakepassword'

But the question remains: What in the world would have caused this principal to have been orphaned in the first place?  Googling and researching reveal that others have had this problem but I have yet to find a description of cause.  Nothing of note that I am aware of occurred the moment the error began appearing.

We moved the whole server to a SAN storage model last summer, we restored everything (including msdb) during that move but that was months ago. It was only something recent that made the symptom manifest as it doesn't appear in the log earlier than a couple weeks ago.


texts:
I have a table Chron containing the following columns:

ID | timestamp | ...

Some timestamps have been erroneously changed to a specific date D. I want to change them to the timestamp of the nearest rows (in terms of ID) which have not been changed. My query is:

UPDATE Chron as C1
SET C1.timestamp = (
                    SELECT C2.timestamp
                    FROM Chron as C2
                    WHERE C2.timestamp != D
                    AND NOT EXISTS (
                        SELECT *
                        FROM Chron as C3
                        WHERE ABS(C2.id - C1.id) >
                              ABS(C3.id - C1.id)
                    )
)
WHERE C1.timestamp = D

The problem is that my table contains more than 300K rows and this query would take a lot of time to run. How can I optimize it? I know that it is not necessary to compare a given row C1 with the whole table, but just the neighbourhood is sufficient, but the optimal size of the neighbourhood is not always the same.


texts: What is the most efficient way to concatenate strings in SQL Server?

texts:
I have this code:

DECLARE @MyTable AS TABLE
(
   [Month] INT,
   Salary INT
);

INSERT INTO @MyTable VALUES (1,2000), (1,3100);

SELECT [Month], Salary FROM @MyTable;

Output:

I want to concat the Salary (grouping by month) so that it will be NVARCHAR like this: '2000,3100'

How would I do this efficiently?


texts: oracle rman connecting to a database in not mount state

texts:
When connected to oracle database through rman directly from the terminal, everything works fine, but when the same connection string is executed through a shell script rman connects to the database in a no mount state, not sure why, using oralce 11g, oracle Linux, and the connection  string is :

rman target / catalog (userName)/(password)@//(IPADDRESS):(PORT)/SYNDEV


texts: What's the best design for a logical structure that contains different properties of various types for different entity categories? (MySQL or NoSQL)

texts:
I'm designing a MySQL database that needs to contain a list of products. Those products, based on their category, will need to have a list of various properties.

Let's say all products in category "displays" will need to have a property "name" (varchar), and "description" (text), whereas the products in category "keyboards" will need to have properties "cord length" (float) and "keyboard highlighting" (boolean).

I came up with a following structure:

Table product:

id_product INT [PK]
category ENUM('displays','keyboards', ...)

Table property:

id_property INT [PK]
name VARCHAR(50)

Table value:

id_value INT [PK]
id_product INT [FK]
id_property INT [FK]
type ENUM('varchar','boolean', ...)

Table value_varchar

id_value INT [FK]
value VARCHAR(255)

Table value_boolean

id_value INT [FK]
value BOOLEAN

As you can see, the structure contains two basic tables product and category and a many-to-many relation in value table, but it doesn't keep the property values in value table (because they can be of different types). Instead, the values are kept in additional type-specific tables value_varchar, value_boolean and so on (there should be more such tables, like value_text etc.).

Such structure seems suboptimal to me, as if I want to retrieve all values for a particular product, I would need to retrieve the value types at first and then run additional queries to retrieve the values from the tables related to those types.  However, I can't come up with anything better right now as far as MySQL is concerned. Maybe NoSQL is an answer to such a construct? If so, which one of NoSQL engines I should look into? I have no experience with NoSQL.

Thanks in advance for helping me out.


texts: mysqldump: dump several tables from db. what will be locked?

texts:
I have active database with dozens of tables. Among them several are unused (no queries/insertions are performing over them for a while).
I want to dump that inactive tables. I would suggest that it will be harmless to use mysqldump -u.. -h.. -p... mydb tbl1 tbl2 > dump.sql and nothing except that tables will be locked?


texts: Google Datastore model User and Messages efficently

texts: I have two kinds: USER and MESSAGES. A user can send/receive many messages to/from another user. Thinking in a relational way the relationship would suggest a user can have many messages, and a message can only be created by one user. So if user A sends a message to User B, both user A should be able to see the message they sent, and user B should be able to see the message they received. I was thinking first of storing all sent/received messages inside of each user object, but since there is a 1mb limit for entities this is not an option as some users can retrieve many more messages than this. Second I was thinking of creating two kinds, USER and MESSAGES.The messages kind would have a to and from property both which would be indexed so that I can get all the messages a user has sent and received. As a result each message would be its own entity and this is where it is problematic. Lets say a User is deleted, I know have to delete all the message sent and received by this user. If a user has sent though sands of messages, the cost of removing each message will be very expensive. I was wondering what would be an efficient way to model this. I am be to Google Datatore and would be open to any ideas as I have nothing implemented yet.


texts: How to write DAX measure that only shows at specified subtotals and grand totals?

texts:
I have a measure that I only want to show/calculate at the subtotal for a particular category and at the grand total.  For example, with
DATATABLE (
    "Cat1", STRING,
    "Cat2", STRING,
    "Cat3", STRING,
    "Val1", DECIMAL,
    "Val2", DECIMAL,
    "Val3", DECIMAL,
    {
        { "A", "X", "Q", 10, 20, 30 },
        { "A", "X", "R", 10, 20, 30 },
        { "A", "Y", "E", 10, 20, 30 },
        { "B", "H", "Q", 10, 20, 30 },
        { "B", "K", "T", 10, 20, 30 },
        { "B", "H", "P", 10, 20, 30 },
    }
)

If I wanted the measure to target "Cat1" with SUMX on "Val1" and I have subtotals on "Cat1" and "Cat2" for "Val1" and "Val2" then the expected output would be
DATATABLE (
    "Cat1", STRING,
    "Cat2", STRING,
    "Val1", DECIMAL,
    "Val2", DECIMAL,
    {
        { "A",     , 30,  60},
        { "A", "X" ,   ,  40},
        { "A", "Y" ,   ,  20},
        { "B",     , 30,  60},
        { "B", "H" ,   ,  40},
        { "B", "K" ,   ,  20},
        {    ,     , 60, 120}
    }
)

The actual problem is DAX in an Excel Power Pivot and the subtotals formulas are more complicated but outermost function of the measure is SUMX.  I currently have the measure calculating but it shows for all subtotal groupings of the pivot groupings, not just the grouping for "Cat1" and the grand total.


texts: Rman not replacing logs with same name on networked drive

texts:
Historically, rman saved logs by replacing files of the same name so that the most recent set was always there, and logs from backup of older than the most recent set simply went away.

Rman is successfully saving the log files to the mapped network drive location, but it only saves the first go round of the assorted backup type running through the Windows Scheduler.

If I move the first set of log files to an archive directory, rman stores up another full set. Using full backups, Incremental Level 0 and Incremental level 1 backups.

Previously, when rman was saving to a location on the server of the target database, it would always simply replace one set of backup logs with a newer version of the same type, since they (of the same type) would all have the same name.

Can one place the date (_d%) or some other data into the name so that the names will never be duplicated? That is not what I really want, but it would be far better than not getting logs, or having to remember to archive logs each rotaion to get new logs saved.

When I have tried placing name switches in rman logs, I found it just did not save the logs. so I must not have been doing it right, if it is possible. If someone could help me learn how to order rman log names, i would appreciate it.

None of this has any effect on the actual backups, they are working great.

Thank you....


texts: MongoDB background index builds block secondary

texts:
We recently experienced a disruptive incident in a MongoDB 3.2 cluster related to index creation. Scenario:

Several indexes were created on the primary using background: true.
Indexes were automatically created on the secondary by MongoDB after finishing on the primary, also in background.
This eventually led to several background index operations running in parallel on the secondary. So far, everything is happening more or less as expected.
When 4 indexer threads ended up running in parallel, all read operations on the secondary appeared to block (edit: to clarify, this even includes read operations to databases on which no indexes were being built, which according to the docs shouldn't even happen for foreground builds). We perform some reads with the secondaryPreferred option, and these reads ended up hanging completely (see below).
We immediately aborted further index creation as well as the background indexer thread still running on the primary. However, read actions on the secondary only started running again after all background index threads had finished. All in all, we were completely unable to read from the secondary for 20 minutes.

What could explain this, specifically the fact that read operations on the secondary eventually blocked completely? The fact that index creation can significantly impact performance is well known and we were taking this into account.

However, nothing in the documentation that we've been able to find explains the secondary hanging completely until index creation is completed. We were also unable to find anything suspicious in the logs on either the primary or the secondary: log entries simply showed the creation and progress of the individual index builds but nothing that would explain the blocking of read operations.

The situation caused some major issues for us as we perform certain reads with the ReadPreference.secondaryPreferred option of the Java driver. Rather than fall back to reading from the primary, these operations ended up hanging with no timeout while waiting for the secondary and consequently quickly building up a large queue of running (but hanging) requests on our application servers.

If anyone could shine some light on this situation it would be greatly appreciated.

Technical details:

MongoDB version: 3.2.16 (not the very newest, but nothing in the .17/.18 changelogs sounds like it might affect this)
Engine: WiredTiger
Resources: Primary and secondary server VMs each configured with 6 CPUs and 128 GB memory, 1 GBit link between the two.
Dataset: approx. 2 TB total of which ~100 GB is particularly active.


texts:
I am getting all of the following error first:

  ORA-12162: TNS:net service name is incorrectly specified

When I export the ORACLE_SID , I can run any command and I get the following error:

  connected to an idle instance

Where is the ALERTLOG file in 12C?

I can't see any file in ORACLE_HOME/log folder. I am using ubuntu 14.04 BTW.


texts: Filter records that have a certain status

texts:
I am using mysql and I have two tables, product and revision.

Joining them gives me the following table:

SELECT *
FROM product AS p
INNER JOIN revisions AS r ON p.revision_id = r.id
ORDER BY p.id;

| id | name     | prices | revision_id | id | revision_status |
|----|----------|--------|-------------|----|-----------------|
| 1  | Produkt1 | 10     | 1           | 1  | 1               |
| 2  | Produkt1 | 4      | 2           | 2  | 0               |
| 3  | Produkt1 | 2      | 3           | 3  | null            |
| 4  | Product2 | 42     | 4           | 4  | 1               |
| 5  | Produkt2 | 43     | 5           | 5  | null            |
| 6  | Produkt2 | 78     | 6           | 6  | 0               |
| 7  | Produkt3 | 1      | 7           | 7  | 1               |
| 8  | Produkt3 | 3      | 8           | 8  | null            |
| 9  | Produkt3 | 4      | 9           | 9  | null            |
| 10 | Produkt4 | 33     | 10          | 10 | 1               |

A Product has a unique ID that identifies a product. Due to price changes each product can have several different prices. Each product has a revision id.

The revision table has a revision_status and can have three different values: 1 for approved price, 0 for closed revision and null for to be revised.

As a final result I would like to get all records that have revision_status = 1 and also a revision_status = null.

Final result

| id | name     | prices | revision_id | id | revision_status |
|----|----------|--------|-------------|----|-----------------|
| 1  | Produkt1 | 10     | 1           | 1  | 1               |
| 3  | Produkt1 | 2      | 3           | 3  | null            |
| 4  | Product2 | 42     | 4           | 4  | 1               |
| 5  | Produkt2 | 43     | 5           | 5  | null            |
| 7  | Produkt3 | 1      | 7           | 7  | 1               |
| 8  | Produkt3 | 3      | 8           | 8  | null            |
| 9  | Produkt3 | 4      | 9           | 9  | null            |

I tried the following:

SELECT *
FROM product AS p
INNER JOIN revisions AS r ON p.revision_id = r.id
WHERE p.name IN (
  SELECT p.name
  FROM product AS p
  INNER JOIN revisions AS r ON p.revision_id = r.id
  GROUP BY p.name
  HAVING COUNT(r.revision_status) > 1)
ORDER BY p.id;

However, this also gives me revisions back that have a revision_status = 0

I created this sqlfiddle as a minimum viable example.

1 Product can have 1 Revision. Initially a product has revision_status of null. After it gets checked the status is set either to 1 - approved -  or to 0 - not approved. In the application I have a logic, that only 1 product - with the same name -  can have revision_status 1. I am looking for a way to show all revisions that have revision_status 1 and null and display the 1ns always on top of the nulls. (Like shown in the result table).


texts:
I need to rename some tables in my DB - SQL Server 2005, with this:

EXEC sp_rename 'dbo.TableName', 'Tmp_TableName', 'OBJECT'

but I can't do this - I get this exception -

  Cannot rename the table because it is published for replication

This DB doesn't have replication - it was removed 5 years ago - we've just tried it and removed it after a week. So I suppose that it should be a flag somewhere.

Please help me remove this flag somehow.


texts: Database as a service (DbaaS) over dimensions--performance, scalability, & reliability?

texts:
MySql Database as a service:

Amazon
Xeround

Another eg. PostgreSQL Database-as-a-Service.
You can get the list of similar products here.

Does anyone have details on their performance, reliability, and scalability of these DbaaS? Reading literature on these products make them sound too good to be true. And my inner cynical sense tells me to question the claims.


texts:
I have a table games

+------------------+----+
|      title       | id |
+------------------+----+
| Need for Speed   |  1 |
| Far Cry          |  2 |
| Assassin's Creed |  3 |
+------------------+----+

and I have a table games_played

+-----------+---------+
| player_id | game_id |
+-----------+---------+
|         1 |       1 |
|         1 |       2 |
|         2 |       3 |
|         2 |       1 |
|         3 |       2 |
|         3 |       1 |
+-----------+---------+

What I need here is select all the games that player_id 1 haven't played and also the number of times the game was totally played.

+------------------+----+--------+--------+
|      title       | id |  count | played |
+------------------+----+--------+--------+
| Assassin's Creed |  3 |    1   |   f    |
+------------------+----+--------+--------+

Here is the query I wrote

select * from games, count("games_played.*"),
exists(select games_played.player_id from games_played where
games_played.player_id = 1 and game_id = games.id) as played,
inner join games_played
on games_played.game_id = games.id
where played = false;

This is throwing and error column played does not exist.

Can someone help me here ?


texts: SQL Server sys.query_store_runtime_stats max_dop field reporting wrong values?

texts:
I have been trying to use the Query Store in SQL Server as an easy way to identify queries running in parallel and the degree of parallelism. However, when I look at the max_dop field in sys.query_store_runtime_stats, I am getting some values that are way beyond the number of CPU's the instance has. I have looked at another instance and also found the erroneous information.
Since one instance is running SQL Server 2017 and the other is running 2019, it doesn't seem to be related to a particular version.
Has anyone else seen similar problems? Am I misinterpreting the column definition?


texts: Mysql (win) Is it possible to get databases from multiple drives in the same time?

texts:
My question would be, is there any way to read the mysql data folder from different drives in the same time (Windows) ?
For example:
I have 1TB database from the drive C:, and another 1TB from the drive D:.
So when I start mysql it will simply see both databases from both drives.


texts:
Let's suppose that a table foo became eligible for autovacuum analyze, e.g. I have inserted a number of rows into foo that exceeds autovacuum_analyze_scale_factor * number of rows + autovacuum_analyze_threshold effective threshold. I can't figure out which are the conditions that should be satisfied in order to have autovacuum performing the analyze on foo.
Will autovacuum analyze run for table foo only if there are no transactions? Or it can run even during active transactions with the conditions that: (1) no transaction is reading a previous version of table foo and (2) the autovacuum analyze can gain SHARE UPDATE EXCLUSIVE lock on table foo?
These questions are related to particular case. I peform a significant number of INSERTs into foo in one transaction and then some UPDATEs on foo in a second transaction. I need the autovacuum analyze to run before the 2nd transaction in order to have an up to date statistics of foo for my query planner better estimates. Are there a way to guarantee that the autovacuum analyze will run before the 2nd transaction? Maybe to sleep (I'm running both transactions from Java app) couple of milliseconds between transactions?


texts:
We are using the following query to merge new data between tables.

-- Merge descriptions from Sync_CheckPointDescriptions table with temporary table #temp
    MERGE CheckPointDescriptions AS TARGET
    USING (
        SELECT dbo.Sync_CheckPointsPerOrder.CheckPointNL As [Text], dbo.CheckPointsPerOrder.CheckPointID, '2CECA4E4-0322-4DD6-B7ED-5C41D34B6136' AS LanguageID
        FROM dbo.Sync_CheckPointsPerOrder
        INNER JOIN dbo.CheckPointsPerOrder
        ON dbo.Sync_CheckPointsPerOrder.CheckPointID = dbo.CheckPointsPerOrder.OldCheckPointID
        WHERE datalength(dbo.Sync_CheckPointsPerOrder.CheckPointNL) > 0 AND (OrderStatus <> 'HISTORIE' OR OrderStatus IS NULL)
        UNION
        SELECT dbo.Sync_CheckPointsPerOrder.CheckPointEN As [Text], dbo.CheckPointsPerOrder.CheckPointID, 'EB9B8B2C-B956-44AB-A843-E645DB1FF487' AS LanguageID
        FROM dbo.Sync_CheckPointsPerOrder
        INNER JOIN dbo.CheckPointsPerOrder
        ON dbo.Sync_CheckPointsPerOrder.CheckPointID = dbo.CheckPointsPerOrder.OldCheckPointID
        WHERE datalength(dbo.Sync_CheckPointsPerOrder.CheckPointEN) > 0 AND (OrderStatus <> 'HISTORIE' OR OrderStatus IS NULL)
        UNION
        SELECT dbo.Sync_CheckPointsPerOrder.CheckPointPL As [Text], dbo.CheckPointsPerOrder.CheckPointID, 'F2973A81-A3C6-418B-ABC2-163F4541BE94' AS LanguageID
        FROM dbo.Sync_CheckPointsPerOrder
        INNER JOIN dbo.CheckPointsPerOrder
        ON dbo.Sync_CheckPointsPerOrder.CheckPointID = dbo.CheckPointsPerOrder.OldCheckPointID
        WHERE datalength(dbo.Sync_CheckPointsPerOrder.CheckPointPL) > 0 AND (OrderStatus <> 'HISTORIE' OR OrderStatus IS NULL)
    ) AS SOURCE
    ON (
        TARGET.[Text] = SOURCE.[Text] AND
        TARGET.CheckPointID = SOURCE.CheckPointID AND
        TARGET.LanguageID = SOURCE.LanguageID
    )
    ----When no records are matched, insert
    ----the incoming records from source
    ----table to target table
    WHEN NOT MATCHED BY TARGET THEN
    INSERT ([Text],
            CheckPointID,
            LanguageID)
    VALUES (SOURCE.[Text],
            SOURCE.CheckPointID,
            SOURCE.LanguageID);

Is there a way to make this more efficient? The statement is taking 7 seconds to finish. The target table has 2.5 million records and the source table has 70.000 records.
Is this statement locking the table for the full 7 seconds?
After adding the new records I have to run sp_updatestats or else the my application will timeout after 30 seconds. Is there a way to prevent this?

Execution plan XML


texts: passing --single-transaction in mysqldump command line tool

texts:
I'm using MySQL GUI tool to backup my innodb database and I use the option '--single-transaction' to get a consistent copy as backup.

I have however, noticed that if I backup using command line mysqldump tool, the backup process speeds up noticeably. I am wondering if is possible to mention --single-transaction in command line mysqldump utility?


texts: Create a read-write user and a read-only user

texts:
I'm trying to do what I think is a common setup for a PostgreSQL database on a RDS instance. I want to have:

Either on public schema, or on a custom schema I don't especially care
A read-write user
A read-only user
The read-write user should be able to create a table in the schema
The read-only user should be able to read from this new table automatically, i.e. without an additional GRANT

I tried to follow this AWS blog post and this answer but it seems I'm hitting a wall.

I've tried the following to no avail, postgres user is the user created by RDS that has the role rds_superuser:

-- Making sure roles can't do anything on the database without explicit grant
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE my_database FROM PUBLIC;
CREATE SCHEMA my_schema;

-- Read-write
CREATE ROLE read_write;

GRANT CONNECT ON DATABASE my_database TO read_write;
GRANT USAGE, CREATE ON SCHEMA my_schema TO read_write;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA my_schema TO read_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO read_write;

GRANT USAGE ON ALL SEQUENCES IN SCHEMA my_schema TO read_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT USAGE ON SEQUENCES TO read_write;

-- Read-only
CREATE ROLE read_only;

GRANT CONNECT ON DATABASE my_database TO read_only;
GRANT USAGE ON SCHEMA my_schema TO read_only;

GRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO read_only;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT SELECT ON TABLES TO read_only;

-- Users
CREATE USER read_write_user WITH PASSWORD 'password_1';
GRANT read_write TO my_database;

CREATE USER read_only_user WITH PASSWORD 'password_2';
GRANT read_only TO my_database_read_only;

When I connect to the database with user read_write_user, I can create a new table my_table, but if I connect with user read_only_user, I can't access it, I get the following error: permission denied for table my_table.

I connected back with the user postgres and saw that I didn't have rights to grant access to this table to read_only role:

GRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO read_only;

leads to this error: no privileges were granted for "my_table".

So if I understand correctly postgres user doesn't have enough rights to grant access to objects created by read_write_user and the ALTER DEFAULT PRIVILEGES didn't do what I thought it would do.

I tried to add the following queries on a new database before revoking/granting anything:

GRANT ALL ON DATABASE my_database TO postgres;
GRANT ALL ON SCHEMA my_schema TO postgres;

and it didn't help.

I have the feeling that this is linked to the fact the rds_superuser is not a real superuser but I don't know how to go forward, is there a way to do what I'm trying to do?


texts:
I'm wondering if it is possible to update an aggregated table retroactively in case of one of the dimension changes.

A simplified example to be more clear :

I have a data warehouse with the details of transactions done by the company. Two important data are the date the transaction was created, and the status of this transaction.
For reporting and analysis purpose I want to aggregate this into a smaller table. Let's say, aggregating by day and by status :

aggregateId creation_date       status  total
1           2016-10-01          1       15
2           2016-10-01          2       93

But sometimes, it can happen that the status of a transaction changes, several days after it was created. For instance one of the transactions made on 2016-10-01 with status 1 has now status = 2 and another has now status = 3. The aggregated table should now look like :

aggregateId creation_date       status  total
1           2016-10-01          1       13
2           2016-10-01          2       94
3           2016-10-01          3       1

What could be the methods to achieve this ?

Recomputing all the aggregated table is not a solution : in the real life the table is quite big, with many other dimensions and it takes already a long time to compute one day of data.
Identifying the aggregated line that need to be changed (here #1 and #2), updating them, and then adding the new ones can be quite complex and hard to maintain (for instance when we add a new column in the aggregated table).
maybe slowly changing dimension can help but I'm not sure to understand how
I'm using SQLServer, maybe SSIS can help ?


texts: Oracle 11G R2 install using separate instances or multiple schemas in a single instance

texts:
I am in the middle of a server upgrade, and have finally come to the conclusion that I need a very important question answered. I would like to inform everyone that I am a novice to Oracle Administration but am learning quickly and still have a lot to learn.

The company I work for has many customers that use their own web portal which is connected to it's own database schema. We have isolated 3 customers to a single database instance and if the instance goes down then generally those 3 customers go down as well.

My goal for this server upgrade is to prevent any interruption from one customers issues from another customers issues. I want complete isolation, which means installing separate database instances per customer. However, I am not sure if this is a great way of handling it.

So let me ask:

Is it best to isolate the schemas to their own instances if I want to avoid having multiple customers crash, instead of a single client?
Would Automatic Memory Management be beneficial or would Automatic Shared Memory Management be beneficial for each database instance installed on the same server?

What I understand is that ASMM would be great for having multiple schemas in a single database instance.

Currently we have memory issues because SGA and PGA were set manually and when one schema runs out of memory we would have to bounce the database instance, killing each customers connections. I want to avoid this no matter what route I take, ASMM or AMM.

PLease let me know which is best and how to best accomplish this task.


texts:
I have the following replication topology:

  DB1 (MySQL 5.5) -> DB2 (MySQL 5.6,  explicit_defaults_for_timestamp = 1) -> DB3 (MySQL 5.6,  explicit_defaults_for_timestamp = 1)

- "date" field:

`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

- DB3 replication error:

[ERROR] Slave SQL: Error 'Column 'date' cannot be null' on query. Default database: 'test'. Query: 'INSERT INTO test_log VALUES (null,'12345',12345,'test','saved')', Error_code: 1048

The reason why DB3 is failing is explained here:

  No TIMESTAMP column is assigned the DEFAULT CURRENT_TIMESTAMP or ON
  UPDATE CURRENT_TIMESTAMP attributes automatically. Those attributes
  must be explicitly specified.

I would like to understand why DB2 is working fine, I guess that's because it's replicating from MySQL 5.5 but what settings are responsible for this?

Update Wed  1 Oct 09:34:03 BST 2014:

Table definition match on all three servers:

mysql> SHOW CREATE TABLE test_log\G
*************************** 1. row ***************************
       Table: feedback_log
Create Table: CREATE TABLE `feedback_log` (
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `order_ref` varchar(32) NOT NULL,
  `id` int(11) NOT NULL,
  `version` varchar(12) NOT NULL,
  `event` varchar(60) NOT NULL,
  KEY `order_ref` (`order_ref`),
  KEY `id` (`id`),
  KEY `version` (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

SQL_MODE shouldn't be the case here:

  DB1: None
  DB2, DB3: NO_ENGINE_SUBSTITUTION

Summary:

I can't run this query manually on both slaves (DB1, DB2) but it's replicated successfully on DB2:

mysql [5.6.20-68.0-log]> INSERT INTO test_log VALUES (null,'12345',12345,'test','saved')';
ERROR 1048 (23000): Column 'date' cannot be null

Another quick test showing this behaviour:

DB1

mysql [5.5.39-log]> CREATE TABLE t1 (date TIMESTAMP);
Query OK, 0 rows affected (0.20 sec)

mysql [5.5.39-log]> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

DB2

mysql [5.6.20-68.0-log]> SELECT @@explicit_defaults_for_timestamp;
+-----------------------------------+
| @@explicit_defaults_for_timestamp |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql [5.6.20-68.0-log]> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

DB3

mysql [5.6.20-68.0-log]> SELECT @@explicit_defaults_for_timestamp;
+-----------------------------------+
| @@explicit_defaults_for_timestamp |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.04 sec)

mysql [5.6.20-68.0-log]> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `date` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


texts: Trigger to cascade updates to a second table

texts:
In my PostgreSQL 9.6 database, I am trying to update 2 fields in table2 with a trigger when table1 is modified.

Function:

CREATE OR REPLACE FUNCTION schema.maj_tbl2()
RETURNS trigger AS
$BODY$

begin
UPDATE schema.table2
SET (contributor, code_tranche) = (user, table1.code_tranche) from schema.table1
where NEW.gid = table2.gid;
return NEW;
END;

$BODY$

Trigger:

CREATE TRIGGER trg_maj_table2
BEFORE UPDATE
ON schema.table1
FOR EACH ROW
EXECUTE PROCEDURE schema.maj_tbl2();

The 2 fields in table2 are modified the same way but for the entire table with the sames values. I can't update just the row in table2 concerned by the update in table1. I feel something is wrong with the where condition but can't fix it.


texts:
I have this code:

insert into [dbo].[NGC_Agent_Intervals]
SELECT
    AgentID,
    date,
    [Hour] = DATEPART(HOUR, RSRange),
    hour_quarter = CONCAT(DATEPART(MINUTE, RSRange) ,'-', DATEPART(MINUTE, RERange)),
    DepartmentID,
    [DepartmentName],
    [AgentState],
    [AgentStateReason],
    ( select AgentStateReasonDescription from RealRanges) as AgentStateReasonDescription,
    [ExtensionID],
    [WorkstationID],

    Duration = sum(CASE
      WHEN RSRange <= aStart and aend >= RERange THEN DATEDIFF(SECOND, aStart, DATEADD(MINUTE, 15, RSRange))
      when  RSRange <= aStart and aend < RERange THEN DATEDIFF(SECOND, aStart, aEnd)
      WHEN RERange >= aEnd THEN DATEDIFF(SECOND, RSRange, aEnd)
      ELSE DATEDIFF(SECOND, RSRange, RERange)
    END)
 FROM RealRanges

 WHERE DATEDIFF(SECOND, RSRange, aEnd) > 0
   AND AgentState NOT IN ('logout', 'LOGIN')

 GROUP BY duration,RealRanges.AgentID, RealRanges.date, RealRanges.hour,
  DepartmentID,[DepartmentName], [AgentState], [AgentStateReason],
  [ExtensionID],[WorkstationID], RSRange, RealRanges.RERange

I run this code after using a cte to split the original rows from a table to a few rows by hour quarters.
What I want to do is to sum the duration value, if a few columns are matching in a few rows.

That's the original table values and I want to sum the duration:

if i dont sum the duration it looks like this:

It should look like this after the sum:

The only problem is that I want to group by only for the following columns:

AgentID  Date  hour  Hour_Quarter  AgentState  AgentStatereason

and exclude the other columns so even if the extensionid is different (for example) I still get the sum from 2 columns.
I tried doing that with a sub-query (in the code above) but I get an error:

So what am I missing?


texts: Is MAX(rowversion) optimized for the table?

texts:
When trying MAX(rv) for the table--the rv rowversion is defined there--I have noticed that the operation is very fast even for big tables. Is there an index implemented implicitly for the rowversion column? Or is the MAX() function optimized for the rowversion value? In other words, is the maximum value for the rowersion of the table stored elsewhere?
It is SQL Server 2014 if it matters.


texts:
I am looking for the best and simplest way for accessing an Oracle db from an SQL Server db. Both are on separate physical servers. I am looking for a one-way access in read-only mode. SQL Server Database would be used by one system for now but would scale to be used by multiple applications in the future.

I currently have three options in mind:

Option 1: Directly accessing Materialised views

Every hour or so, data would be stored into Materialised views in Oracle in order to prevent impacting original DB performance.
SQL Server DB would access the views on-demand, using Linked servers

Option 2: Copy data to SQL Server DB

Every hour or so, replicated tables into SQL Server DB using CDC mechanisms.
(Not so sure how that'll work).

Option 3: Replicate Oracle tables using SQL Server Publishing

Setup the Oracle DB as the distributor.
Setup the SQL Server as the publisher (snapshot mode).

Thank you for your advices.


texts: Unexpected result from query based on old column value

texts:
I need to update a column and based on the old value, update a second column.

After googling I came up with THIS (*nr 4) solution: copy the table to have the old value at hand.

The table looks like

   id | upd | a1 | a2 .. | b1 | b2 | b3 |
   1  |     | 1  | 1     | 1  | 1  | 1  |

My query looks like:

UPDATE table t1, table t2 SET
    t1.b1  = GREATEST( 5, t1.a1),
    t1.upd = IF(t1.b1 <> t2.b1 ,1 , t1.upd)
WHERE t1.id = t2.id

Based on this query I would expect the table to look like this:

   id | upd | a1 | a2 .. | b1 | b2 | b3 |
   1  | 1*  | 1  | 1     | 5* | 1  | 1  |
*= updated

Only, it isn't. It executes the first line correctly (updating b1 to 5), but not the second one, setting the upd field.

The mysql manual states:

The second assignment in the following statement sets col2 to the current (updated) col1 value,
not the original col1 value. The result is that col1 and col2 have the same value.
 This behavior differs from standard SQL.
UPDATE t1 SET col1 = col1 + 1, col2 = col1;

So I would have expected the second line (update upd) to work as well.

If I add (as a test) these two lines

t1.b2=t1.b1,
t1.b3=t2.b1,

before the t1.upd = line, b2 and b3 still show 1, where I expected b2 to be 5.

Where am I going wrong in my logic (and understanding of MySQL :-)?

NOTE Sorry, I forget to mention: I connot use Triggers. Otherwis it would have been less difficult


texts: Can I see the RAID group name for my SQL Server Database files?

texts: Is it possible to see the RAID group name that my database (and log) files are sitting on, in one of the DMVs (or some other system table) ?


texts: MySQL fails to start on arch Linux. Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

texts:
MySQL fails to start on arch Linux.

mysqld[3440]: [Note] /usr/bin/mysqld (mysqld 10.1.22-MariaDB) starting as process 3440 ...
mysqld[3440]: [Note] InnoDB: Using mutexes to ref count buffer pool pages
mysqld[3440]: [Note] InnoDB: The InnoDB memory heap is disabled
mysqld[3440]: [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
mysqld[3440]: [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
mysqld[3440]: [Note] InnoDB: Compressed tables use zlib 1.2.11
mysqld[3440]: [Note] InnoDB: Using Linux native AIO
mysqld[3440]: [Note] InnoDB: Using SSE crc32 instructions
mysqld[3440]: [Note] InnoDB: Initializing buffer pool, size = 128.0M
mysqld[3440]: [Note] InnoDB: Completed initialization of buffer pool
mysqld[3440]: [Note] InnoDB: Highest supported file format is Barracuda.
mysqld[3440]: [Note] InnoDB: The log sequence numbers 0 and 0 in ibdata files do not match the log sequence number 1600719 in the ib_logfiles!
mysqld[3440]: [Note] InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
mysqld[3440]: [Note] InnoDB: 128 rollback segment(s) are active.
mysqld[3440]: [Note] InnoDB: Waiting for purge to start
mysqld[3440]: [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.35-80.0 started; log sequence number 1600719
mysqld[3440]: [Note] InnoDB: Dumping buffer pool(s) not yet started
mysqld[3440]: [Note] Plugin 'FEEDBACK' is disabled.
mysqld[3440]: [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
mysqld[3440]: [Note] Recovering after a crash using mysql-bin
mysqld[3440]: [Note] Starting crash recovery...
mysqld[3440]: [Note] Crash recovery finished.
mysqld[3440]: [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_slave_pos' doesn't exist
mysqld[3440]: [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
mysqld[3440]: [Note] Server socket created on IP: '::'.
mysqld[3440]: [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

I have reinstalled the packages but I still end up with the same problems.


texts: Cleaning up the environment on old cluster after migrating to newer SQL Server cluster

texts:
We migrated many instances of SQL Server 2008/R2 on Windows 2008R2 and 2012 on Windows 2012 to newer Windows 2016 with SQL Server 2014 and 2016 versions.

The old clusters were configured with either 2, 3, or 4 nodes.

Now to begin with cleaning up and decoming the Windows 2008R2 and 2012 servers, we would need to:

For 2 node clusters, uninstall SQL Server when there is no activity.
For the with 3/4 node clusters, we have one node active since application jobs are still running (just the SQL Server agent jobs running ssis packages which are already connecting to the new cluster). The migration of these jobs will take time, however. Hence I am looking for a way to break the cluster or uninstall SQL Server from the non-active nodes that will ensure the jobs continue to run on the active node.

What is the safest way to clean up the SQL Server clusters and make sure they do not break in a scenario where a single node of the 3-4 nodes needs to remain active?

Appreciate if there is any checklist one follows and can share while bringing down the SQL Server cluster.


texts: Creating a table for indexing another table

texts:
We have a table with 350m rows and 12 columns, and around 1m rows adding everyday. We have crappy hardware and we cannot add an index on one of the 12 columns that we really really need to. (severe lack of use case analysis)

We have tried adding an index on a less busy and less crowded (14m rows) table via percona toolkit , but it didn't work either , failing in 12 hours , due to hardware sloppiness. So we are not gonna try it on the 350m table at all.

I guess the only solution left is creating another table , just for indexing the column on the 350m-row table.

Is this convenient ? is there any other solution you may suggest ?

We have this problem with many tables and a solid solution would make my life easier.


texts:
A client wants to migrate from sql 2008 to sql 2017 and add availability groups. All enterprise. All vm's.

Question: (without using any words starting with Az~ smile) should I set up the ag's first or last?

My instinct is to (side by side) migrate as is. Then once live and stable, separately, add always-on ag's.


texts: Inherit INNER JOIN fields in MySql SELECT QUERY

texts:
I want to know how I can join one field of an INNER JOIN table in a SELECT QUERY.

Here's what i got so far:
In this query my result would be NameA, NameB and idC, but i want to join tableA and tableC reference as shown in the image schema.

SELECT table_A.idC, table_A.nameA, table_B.nameB
FROM table_Ref
INNER JOIN table_A ON table_Ref.idA=tableA.idA
INNER JOIN table_B ON table_Ref.idB=tableB.idB;

My wanted result would be nameA,NameB, and NameC using the idC field in table_A to know which nameC belongs to that idC.

I hope you can help me or give me a reference of this topic :)!


texts: In SQLITE, how do you create a new table into its own file?

texts:
I am trying to do key value pairing, where performance is the top priority. I understand that the smaller a sqlite file is, the better the read and write speeds are, and so I am going to create about 64 files, having every explicit key-value combination.
Hence I would like to create a command that will create a new table inside its own file. How do I do this?
Thanks.


texts: Identify top logins\users consuming resources

texts: Is there a way to identify top database users who are actually consuming high resources with their executed queries ?


texts: Problem with creating SSAS Roles using Powershell

texts:
I'm using powershell to (mass) delete and create roles in SSAS. Deleting is done like this :

$rolecount = $db.Roles.count
    for ($i = 0; $i -lt $rolecount; $i++) {
        Write-host ($db.Roles.item(0).name)
        $db.Roles.item(0).Drop([Microsoft.AnalysisServices.DropOptions]::AlterOrDeleteDependents)
    }
$db.Update()

and adding like this :

        if (!$db.Roles.Contains($sRole))
        {
                $db.roles.add($sRole) > $null
                $role = $db.Roles.GetByName($sRole)
                $role.update()
        }
        else
        {
            Write-host "[$((get-date).ToString('HH:mm:ss'))] -> Role '$sRole' already exists !"
        }

with $db being an instance of the "Microsoft.AnalysisServices.Database" class. When the role is added permissions etc. are added too in a similiar fashion. 98% of the time that works fine, but ever so often i get the following error message back from SSAS : the object (database role) with the name [insert role name here, its always a different one] already exists in [insert database name here] (i translated the error message, actual wording in english could be different). The script output shows that the same role has been deleted moments ago with no errors. I even put a debug output into the script that counted the number of roles after deletion, it was 0. Has anyone experienced this behavior before ? i've tried for ages to find a way to reliably reproduce the error in order to fix it.


texts: How do I do data entry with Foreign Key integer IDs?

texts:
This seems like a basic question to me but I'm having trouble finding practical solutions.

I have two tables, one for video game consoles, and another for the games themselves.

Consoles
+ -- + --------- +
| ID | Name      |
+ -- + --------- +
| 0  | Console X |
+ -- + --------- +
| 1  | Console Y |
+ -- + --------- +

Games
+ -- + ------ + ------- +
| ID | Name   | Console |
+ -- + ------ + ------- +
| 0  | Game X | ?       |
+ -- + ------ + ------- +
| 1  | Game Y | ?       |
+ -- + ------ + ------- +
| 2  | Game Z | ?       |
+ -- + ------ + ------- +

My question is how do I do the data entry from scratch for the Game.Console column here? As a human, it'd be easy to write the Console.Name there, but obviously that's not recommended to use as a Foreign Key ID. But if I use the integer Console.ID as the Foreign Key, for 700 games and 30 consoles, it seems like I'd have to manually cross-check the table as I enter in all those numbers.

How do I do the task of data entry for an association like this? Is there a better solution than manually entering numbers? I'd think if I was using an IDE there might be some feature for a drop-down of foreign rows to fill in a foreign key value, but I haven't found anything.

Additional Info: I'm not expecting user input in this application, just my own personal data, so I'm not worried about validation, just how to alleviate tedium in the initial data entry. Entering cross-referenced integers seems like the job for a computer or tool.


texts: Which is faster, SUM(CASE) or CTE PIVOT?

texts:
There are two types of ways to perform a PIVOT. Before SQL Server 2005, when PIVOT was introduced, most people did this:

SELECT RateID
              SUM(CASE WHEN RateItemTypeID = 1 THEN UnitPrice ELSE 0 END),
              SUM(CASE WHEN RateItemTypeID = 2 THEN UnitPrice ELSE 0 END),
              SUM(CASE WHEN RateItemTypeID = 3 THEN UnitPrice ELSE 0 END)
              FROM rate_item WHERE _WhereClause_
              GROUP BY RateID

Later, when 2005 introduced PIVOT it became this:

   SELECT RateID, [1], [2], [3]
          FROM PertinentRates -- PertinentRates is a CTE with WHERE clause applied
          PIVOT (SUM(UnitPrice) FOR RateItemTypeID IN ([1], [2], [3])) PVT)

Across SQL Server 2005, 2008 R2, 2012 and 2014 (the versions of SQL Server I've worked with that implement PIVOT), in my experience, it has always been faster than SUM(CASE) or in a few cases equally fast. Are there examples where PIVOT is slower?

I can't give the DDL because it is an example from my work. But the table is pretty simple. In the PIVOT example it is drawing from a CTE whereas the SUM(CASE) is drawing directly from the table. But the SUM(CASE) performs the same drawing from the CTE.

In my work example, the PIVOT comes back in 10 seconds while the SUM(CASE) comes back in 14. Clearly it must be doing something different under the covers. The plans are the same, 50% of total each. PIVOT converted to SUM(CASE) in the query analyzer. Yet SUM(CASE) never comes back in less than 13 seconds, and PIVOT never comes back in over 11 seconds.

I've tried running them back and forth, it doesn't matter the order they are run in. If I run them both from a cold cache they both take longer, but PIVOT is still faster, 12 vs 17 seconds. Can't reproduce on a second server, but that one is considerably better; it's 5 seconds each there with minor variations. PIVOT is a little better, but percentage-wise it doesn't have the same edge as on the first server.

The IO stats, like the query plan, is identical between the two. That is odd, I had kind of expected to see different IO stats, even though I've never looked at them for this particular example.