texts:
OK, before I start, I know you're not supposed to do this...

I have inherited a SQL Server instance (2008 R2 SP2 x64) which has a DB with a very large transaction log (full mode, with no tlog backups), and the log is in a compressed folder (don't ask). I need to sort this out and was thinking my best approach would be to detach the database (no reason why it shouldn't checkpoint) and re-attach the mdf ignoring the ldf so a new one is created in a proper folder structure. I can then implement a decent backup strategy.

I really don't want to backup this tlog as it's huge, and in a compressed folder so who knows how big it really is. Another option was to simply truncate the tlog but that's deprecated... Maybe backup the tlog to /dev/null (whatever the windows equiv is)?

Obviously I'd do a full backup before anything - these occur daily anyway.

I guess what I'm asking is, what is the safest approach here?

EDIT: actually, I've just thought - if I'm doing daily full backups, would the tlog not be useless immediately after the BU anyway?


texts: how to calculate minus value between rows and between time

texts:
I have a table contains id, server_time and value:

id   server_time      value
---  ---------------  -----
100  04/10/2016 0:00  3
101  04/10/2016 0:15  6
102  04/10/2016 0:30  8
103  04/10/2016 0:45  9
104  04/10/2016 1:00  10
105  04/10/2016 1:15  10
106  04/10/2016 1:30  12
107  04/10/2016 1:45  13
108  04/10/2016 2:00  14

This data will be inserted every 15 minutes, and the value is a cumulative value.

I need to output columns min15, hour1 and hour2:

column min15 will be the difference between this row's value and the one 15 minutes ago.
column hour1 will be the difference between this row's value and the one 1 hour ago.
column hour2 will be the difference between this row's value and the one 2 hours ago.

id   server_time      value  min15  hour1  hour2
---  ---------------  -----  -----  -----  -----
100  04/10/2016 0:00  3      3      null   null
101  04/10/2016 0:15  6      3      null   null
102  04/10/2016 0:30  8      2      null   null
103  04/10/2016 0:45  9      1      null   null
104  04/10/2016 1:00  10     1      7      null
105  04/10/2016 1:15  10     0      null   null
106  04/10/2016 1:30  12     2      null   null
107  04/10/2016 1:45  13     1      null   null
108  04/10/2016 2:00  14     1      4      11

Or should I display these columns (min15, hour1, hour2) as separate results?

Any suggestion for the query would be appreciated.


texts: Is there a repository for pgadmin plugins?

texts:
I've been using PostgreSQL with PGAdmin III for a while now and it's been bugging me that there is a plugins menu option that is empty. I've Googled some and found a plugin here and there but I wanted to know if there was a repository I was missing out on?

If you don't know of a repo but are aware of additional plugins please also let me know about them.


texts: Generate backwards compatible CREATE DATABASE script with all objects?

texts:
I'm working on SQL Server 2016, and I want to deploy a database I have created to Sql Server 2012 SP1.

I found I was unable to create a backup in v2016 and restore to v2012. So instead in the v2016 version I:

Right click the DB
Select Tasks > Generate scripts
Script a CREATE statement for all objects

This kinda works... but I have to add a lot of objects manually. The database has tables/views/tvps/sps/scalar functions. Is there a way to script a create statement that is backwards compatible with older SQL Server versions?


texts: Trigger Definer Already Same But User Still Denied

texts:
currently I already have user like this 'app_user'@'10.148.0.0/255.255.240.0'
and I have the trigger with definer like this
CREATE DEFINER=`app_user`@`10.148.0.0/255.255.240.0` TRIGGER usertable_delete_audit

but when apps insert data i got this error
trigger command denied to user 'app_user'@'10.148.0.0/255.255.240.0' for table user table.

i already make sure about the grants this user already full grant.
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON

this impact from user with specific host ? i mean because the user have grant like this 10.148.0.0/255.255.240.0. And the trigger only can give the access from specific ip from server apps ?
my server apps ip 10.148.0.57
i using mariadb version 10.4


texts:
I have database with 170+ tables in mssql 2012. Some of them contain the field "UserID", varchar(9) while others do not. Due to an application redesign, I need to alter all tables in the database to check if the field exists, and if it does, I need to change it to varchar(50). If not, then I need to add it.

Can someone point me how to do this with some kind of batch procedure? I don't have much experience with mssql databases, so detailed explanation would be appreciated.


texts: Type conversion in expression may affect "CardinalityEstimate" in query plan choice?

texts:
I maintain an archive database that stores historical data in partitioned views. The partitioning column is a datetime. Each table under the view stores one month of data.

We constraint the events on each table with a check constraint on the datetime column. This allows the optimizer to limit the tables that are searched for queries that filter on the event datetime column.

The names of the check constraints were generated by SQL Server, so it's hard to know what they do by looking at their name.

I want the constraint names to have the form 'CK_TableName_Partition'.

I can generate a rename script using this query and copying data from from sql_text column. The WHERE clause matches check constraints whose names look like they were generated by SQL Server:

SELECT
  checks.name AS check_name,
  tabs.name AS table_name,
  skemas.name AS schema_name,
  cols.name AS column_name,
  N'
EXECUTE sys.sp_rename
  @objname = N''' + skemas.name + N'.' + checks.name + N''',
  @newname = N''CK_' + tabs.name + N'_Partition'',
  @objtype = ''OBJECT'';' AS sql_text
FROM sys.check_constraints AS checks
INNER JOIN sys.tables AS tabs ON
  tabs.object_id = checks.parent_object_id
INNER JOIN sys.schemas AS skemas ON
  skemas.schema_id = tabs.schema_id
INNER JOIN sys.columns AS cols ON
  tabs.object_id = cols.object_id AND
  cols.column_id = checks.parent_column_id
WHERE checks.name LIKE (
  N'CK__' + SUBSTRING(tabs.name, 1, 9) +
  N'__' + SUBSTRING(cols.name, 1, 5) +
  N'__' + REPLACE(N'xxxxxxxx', N'x', N'[0-9A-F]') COLLATE Latin1_General_BIN2
)
ORDER BY table_name;

The output looks like this:

check_name  table_name  schema_name column_name sql_text
CK__tbAcquisi__Acqui__5C4299A5  tbAcquisitions_201301   Archive AcquisitionDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbAcquisi__Acqui__5C4299A5',  @newname = N'CK_tbAcquisitions_201301_Partition',  @objtype = 'OBJECT';
CK__tbAcquisi__Acqui__76026BA8  tbAcquisitions_201302   Archive AcquisitionDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbAcquisi__Acqui__76026BA8',  @newname = N'CK_tbAcquisitions_201302_Partition',  @objtype = 'OBJECT';
CK__tbAcquisi__Acqui__7D6E8346  tbAcquisitions_201303   Archive AcquisitionDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbAcquisi__Acqui__7D6E8346',  @newname = N'CK_tbAcquisitions_201303_Partition',  @objtype = 'OBJECT';
...
CK__tbRequest__Reque__60132A89  tbRequests_201301   Archive RequestDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbRequest__Reque__60132A89',  @newname = N'CK_tbRequests_201301_Partition',  @objtype = 'OBJECT';
CK__tbRequest__Reque__1392CE8F  tbRequests_201302   Archive RequestDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbRequest__Reque__1392CE8F',  @newname = N'CK_tbRequests_201302_Partition',  @objtype = 'OBJECT';
CK__tbRequest__Reque__1AFEE62D  tbRequests_201303   Archive RequestDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbRequest__Reque__1AFEE62D',  @newname = N'CK_tbRequests_201303_Partition',  @objtype = 'OBJECT';

The result of the query seems to be correct and the server executes it quickly.

But the root node of the execution plan has a warning:

  Type conversion in expression (CONVERT_IMPLICIT(nvarchar(128),[o].[name],0)) may affect "CardinalityEstimate" in query plan choice

What does that mean in this context? Is such a complex filter confusing the optimizer? Is it something I should be worried about?


texts: In trying to setup back up device, net use command throws operating system error 53 when passing in a unc

texts:
So I'm trying to use XP_CMDSHELL to set up my drive for using a backup.  I already have the drive mapped, but per http://blogs.msdn.com/b/varund/archive/2009/06/04/backup-sql-server-database-to-a-share-drive-on-another-server.aspx, I figured I'd need to mount the drive using net use.

But when I do, I get System error 53, 'The network path was not found.'

EXEC XP_CMDSHELL 'net use S: \\sql-backup'

I know this unc is accessible from the server because it is mounted as a network share on the sql server.

Any ideas why the unc is unreachable?


texts: How many users and objects can be in an Oracle Database?

texts:
I wonder if anyone know what can be the biggest existing Oracle databases in the world and how many users (DBA_USERS), objects (DBA_OBJECTS) they can contain?

Is there a scenario when a database contain more than 100 000 users?

For example, as far as I can guess, there can be thousands of users in MySQL database on a webhosting server where each hosting user registered on the hosting website has its own MySQL user. Is there a real-life Oracle databases that are used in a way like this?


texts: Select all data from two tables with partial common data

texts:
I have two tables which have 3 same column names and approx half of the records matching.

I want the output table to combine all the rows from both tables whether the rows match or not.

I Tried to run the Union in PL SQL as

SELECT a.First_Name,a.Last_Name, a.State, a.DOB, a.Age, b.Gender
From table1 a, table 2 b
where
a.First_Name || a.Last_Name || a.State = b.First_Name || b.Last_Name || b.State

UNION

SELECT a.First_Name,a.Last_Name, a.State, a.DOB, a.Age, 'NA' Gender
From table1 a
where
a.First_Name || a.Last_Name || a.State != b.First_Name || b.Last_Name || b.State

UNION

SELECT a.First_Name,a.Last_Name, a.State, 'NA' DOB, 'NA' Age, b.Gender
From table2 b
where
a.First_Name || a.Last_Name || a.State != b.First_Name || b.Last_Name || b.State

However, this returns redundant rows and not the expected data.
Could anyone help me with this.


texts:
I have an script that uses IMPDP over a database link (no expdp used) to import some data from our PROD environment to a DEV environment.
Some tables are rather gigantic and partitioned, and we want just a sample of it like the last 30 days of data. I noticed that even specifying a QUERY parameter, impdp still slowly goes thru every partition displaying several 0 rows imported and taking time to get tot he desired partitions.
Is it possible to specify the partition names inside the INCLUDE parameter (this is where i am listing desired tables, its a subquery of table names on the PROD environment)?
I know there's a TABLES parameter that is usually used for this, but it can be dinamically populated like the INCLUDE one can, from a query?
I imagine i can generate a string in the script prior to calling impdp, but would prefeer a native solutuion using impdp itself.
Bellow is a sample command structure i am using, its not filtered as it is.
impdp user/"pwd"@bd directorySOMEDIR NETWORK_LINK=somelink schemas=someschema logfile=somefile.log remap_tablespace=TBS_BLA:TBS_BLE CONTENT=DATA_ONLY PARALLEL=40 include=TABLE:\"IN \(\'MY_TABLE\' \)\"

If I use TABLES=MY_TABLE:SYS_P01 it works, but I would like to dynamically define this as everyday a new partition is created.
Would be nice if I could specify partition names in the INCLUDE parameter, but not sure its suported.


texts:
I have an interview question, which was asked during my interview. I answered the question, but interviewer was not so convinced with my answer. So, anyone please correct me with my understanding ?

Q. Why Truncate is DDL Where as Delete is DML ? Both do almost same job (removing rows)

Ans. When we are using Truncate, we are de-allocating the whole space allocated by the data without saving into the undo-table-space. But, in case of Delete, we are putting all the data into undo table-space and then we are deleting all the data.

Please, if anyone knows best answer for the above, please explain.


texts: Firewall Rules, IP address failover cluster

texts:
We are having issues with some applications running and connecting to a failover cluster through a firewall (not Windows firewall). In the firewall settings we configure the IP addresses from the failover cluster.

My question is: To which IP address from the failover cluster do we have to connect to?

The instance IP address (example: server1)
The a node (active) (example: server1a)
The b node (passive)(example: server1b)
the SQL Server listener IP (example: server1-fc)

At the moment we have all of the above configured in the firewall but this is giving performance issues. Why is adding all IP addresses giving performance issues?

This is Basic FCL (not Always On). Is it possible that when we added the instance IP address  (example: server1) in the firewall together with the SQL Server listener (example: server1-fc) it causes the traffic to use/route the wrong IP address?

We started having performance issues when we changed firewall rules. Before the change we had the SQL Server listener configured in the firewall. After the change we added all of the above IP addresses. But why is adding more IP addresses in the firewall causing performance issues?


texts: Ramifications of Deleting Transaction Log Backup

texts:
What are the ramifications of having deleted a backup of the SQL Server transaction log for a database that's in Full Recovery mode?

Caveat: I'm a developer, not a DBA, and we're a "one person" shop.

The saga: We were running out of space on our hard drive, needed to refresh Staging, and didn't have enough room to do so.  I took a transaction log backup (which apparently had not been done in a long time, if ever), tried to Shrink the trans log, but that only reduced the size by about 40GB instead of the 175GB it was taking up.  I then didn't have enough room for the upcoming nightly backup, so - in my endless wisdom - I deleted the TLOG backup I'd just taken.  Restored a copy of Prod to Staging using the 40GB I'd freed up by shrinking the transaction log the first time.  The nightly production backups succeeded and all was well.  Then this morning I took another backup of the Prod transaction log and tried to shrink it again.  This time it worked and free up most of the 135GB that it was taking up.

So... have I potentially ruined the integrity of my Production database?  What if in a few days I encounter a disaster and need to do a restore... will the fact that I deleted the first transaction log backup prevent me from doing that?


texts: How to Remove Scripts Date Comments from Powershell Generate Database Script?

texts:
I am generating Database Generate scripts with Powershell.
What is the option in Powershell to remove Script dates comment lines?

Automated Script-generation with Powershell and SMO

Object:  Table [dbo].[customer]    Script Date: 9/6/2018 4:40:33 PM

$Filepath='E:\MyScriptsDirectory' # local directory to save build-scripts to
$DataSource='MyServer' # server name and instance
$Database='MyDatabase'# the database to copy from
# set "Option Explicit" to catch subtle errors
set-psdebug -strict
$ErrorActionPreference = "stop" # you can opt to stagger on, bleeding, if an error occurs
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$ms='Microsoft.SqlServer'
$v = [System.Reflection.Assembly]::LoadWithPartialName( "$ms.SMO")
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
[System.Reflection.Assembly]::LoadWithPartialName("$ms.SMOExtended") | out-null
   }
$My="$ms.Management.Smo" #
$s = new-object ("$My.Server") $DataSource
if ($s.Version -eq  $null ){Throw "Can't find the instance $Datasource"}
$db= $s.Databases[$Database]
if ($db.name -ne $Database){Throw "Can't find the database '$Database' in $Datasource"};
$transfer = new-object ("$My.Transfer") $db
$CreationScriptOptions = new-object ("$My.ScriptingOptions")
$CreationScriptOptions.ExtendedProperties= $true # yes, we want these
$CreationScriptOptions.DRIAll= $true # and all the constraints
$CreationScriptOptions.Indexes= $true # Yup, these would be nice
$CreationScriptOptions.Triggers= $true # This should be included when scripting a database
$CreationScriptOptions.ScriptBatchTerminator = $true # this only goes to the file
$CreationScriptOptions.IncludeHeaders = $true; # of course
$CreationScriptOptions.ToFileOnly = $true #no need of string output as well
$CreationScriptOptions.IncludeIfNotExists = $true # not necessary but it means the script can be more versatile
$CreationScriptOptions.Filename =  "$($FilePath)\$($Database)_Build.sql";
$transfer = new-object ("$My.Transfer") $s.Databases[$Database]

$transfer.options=$CreationScriptOptions # tell the transfer object of our preferences
$transfer.ScriptTransfer()
"All done"


texts: How to generate a 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, ... series in standard SQL or T-SQL?

texts:
Given two numbers n and m, I want to generate a series of the form

1, 2, ..., (n-1), n, n, (n-1), ... 2, 1

and repeat it m times.

For instance, for n = 3 and m = 4, I want a sequence of the following 24 numbers:

1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1
----------------  ----------------  ----------------  ----------------

I know how to achieve this result in PostgreSQL by either of two methods:

Using the following query, which uses the generate_series function, and a few tricks to guarantee that the order is the right one:

WITH parameters (n, m) AS
(
    VALUES (3, 5)
)
SELECT
    xi
FROM
(
    SELECT
        i, i AS xi
    FROM
        parameters, generate_series(1, parameters.n) AS x(i)
    UNION ALL
    SELECT
        i + parameters.n, parameters.n + 1 - i AS xi
    FROM
        parameters, generate_series(1, parameters.n) AS x(i)
) AS s0
CROSS JOIN
    generate_series (1, (SELECT m FROM parameters)) AS x(j)
ORDER BY
    j, i ;

... or use a function for the same purpose, with adjoint and nested loops:

CREATE FUNCTION generate_up_down_series(
    _elements    /* n */ integer,
    _repetitions /* m */ integer)
RETURNS SETOF integer AS
$BODY$
declare
    j INTEGER ;
    i INTEGER ;
begin
    for j in 1 .. _repetitions loop
        for i in         1 .. _elements loop
              return next i ;
        end loop ;
        for i in reverse _elements .. 1 loop
              return next i ;
        end loop ;
    end loop ;
end ;
$BODY$
LANGUAGE plpgsql IMMUTABLE STRICT ;

How could I possibly do the equivalent in either standard SQL or in Transact-SQL / SQL Server?


texts: Native checksum table says there's a difference, pt-table-checksum does not

texts:
I was setting up a slave and doing some table checksums to check things were ok before getting them live.

I did a native mysql checksum tables on the slaves after syncing slaving to a stopped point and comparing against a preexisting slave.

Two tables differed.  To drill down I ran pt-table-checksum which reported both the old and new slave had no differences and the same row counts.

To get a third opinion I did a select * into outfile ordered by PK and ran an md5sum on the outputs.  Those too both matched.

One noticeable difference is the newer slave is running a slighter newer version (5.5.34 vs 5.5.29); both percona builds.

Is this version difference something to be expected in causing differences in checksums?  If so it seems odd it was just two tables out have a couple dozen.


texts:
Is there a way to display the layouts for all the tables to see their columns? I am trying to find tables with a certain column names.

I am using the web app Web Data Administrator

Example:

table1
- column 1
- column 2
table2
- column 1

is there a way to get a display like that?


texts: Getting error while starting mysql by just typing in mysql terminal

texts:
I am getting an error while starting mysql, by just typing mysql in the  terminal:

The error is:

amtz@amtz-VPCEA23EN:~$ mysql
ERROR 1045 (28000): Access denied for user 'amtz'@'localhost' (using password: NO)


texts: Does SQL Server upgrade to table locks at total locks or session locks?

texts:
I’ve a table that both has mass sequential inserts at the end of the CI and random (very distributed) reads+updates. Naturally, the mass inserts should not block the random access. RCSI is used, so the read-only queries shouldn’t affect the lock count (?) in relation to the sequential insert.
My concern is that, even when limiting the maximum number of locks taken during the insert (eg. inserting in batches), it is possible for one (or more) of the OLTP updates to bypass this limit. If the lock count heuristic is per-session then it is less of potential issue.
Given the answer to the question in the title, then, what is the “best” way to prevent table lock escalation here?
My current approach/thought is to select a row count (eg. arbitrary of 1-4k) during the mass inserts to allow “some slack”, although this feels overall imprecise. While batches are essential a way to deal with replication and such, it would be nice to specify a batch size of 5k rows and move on. (To be fair, quick table locks aren’t really the issue: the intent of the question is more about finding the edge such that table lock escalation doesn’t happen.)
There has been DBA pushback on both 1) disabling row locks (to ensure page locks and thus reduce lock counts) and 2) disabling table lock escalation (with forced page locks to minimize worst-case). Are there any other relevant database properties to consider with respect to lock escalation? (Increasing the lock limit to say, 10k would then allow a much larger “slack” batch size.)
Using with PAGLOCK in the batch inserts results in page-row deadlocks with the current instead-of triggers and generally appears to be a pain to get correct. Although the instead-of triggers themselves are currently on the axing block for various technical complications. This hint also only increases the “slack”. I don’t suppose there is a NOINCRLOCKCOUNT hint that has been overlooked..


texts: Subtract multiple quantities in table2 from table1

texts:
How can I subtract the quantities in table2 from table1?
I'm using SQL Server 2014.
Table1
P_ID     name       quantity
1       carrot        100
2       eggplant      100
3       Avocado       100
4       Cabbage       100
5       peppers       100

Table2
   P_ID      name       quantity
    1       carrot        3
    2       eggplant      12
    5       peppers       7
    2       eggplant      8
    1       carrot        25
    5       peppers       13

The final result should be like this:
Table1
P_ID        name     quantity
1            a         73
2            b         80
3            c         100
4            d         100
5            e         80

How can I do this? Is this even possible?


texts: Phantom SQL Server database backups. How can I find the cause?

texts:
I've noticed from the event log, that something is backing up SQL Server databases, separately to our maintenance plan backups. We can see something running SQL backups at 03:00. Our intended backups only kick off at 04:05.

The backup logs look like this:

  Database backed up. Database: master, creation date(time):
  2015/11/15(09:39:37), pages dumped: 490, first LSN: 743:441:73, last
  LSN: 743:472:1, number of dump devices: 1, device information:
  (FILE=1, TYPE=VIRTUAL_DEVICE:
  {'{9BCE7BA5-E8AB-4459-8655-365988420F35}12'}).

There seems to be no other backup of the database files themselves which might trigger a volume shadow copy (VSS writer) that we can see, so it's a mystery what's causing these.

The worry is of course, that these backups will interfere with the restore process for point-in-time restore as we have no access to these full backups, yet they are presumably resetting the transaction log.

The server is a Virtual Machine and I originally theorised that perhaps something was backing up the entire VM at this time, and perhaps that was causing VSS to create the backups, but apparently there is no machine imaging backup set up on this server - only file based backups of certain folders and the main SQL data folder is not backed up.

Is there anything I can set up which could provide any clues as to what's running these backups?


texts: How to create a dynamic comment to an object?

texts:
I want to embed variable information in a table that is created from a script. The following works:

SELECT( 'created ' || CURRENT_DATE ||' and is in CH1903 LV03');

But it seems I can't use it for creating a comment:

COMMENT ON TABLE public.mytable IS SELECT( 'created ' || CURRENT_DATE ||' and is in CH1903 LV03');
\set mycomment SELECT( 'created ' || CURRENT_DATE ||' and is in CH1903 LV03');
COMMENT ON TABLE public.mytable IS :mycomment;

Is dynamic SQL the only option?


texts: Input to be printed in different output format

texts:
I have a value (e.g. ORACLE) and I would like to print each letter on an individual line using an Oracle query.

Input value:

ORACLE

Output:

O
R
A
C
L
E


texts: Transactional replication subscriber vs AG secondary replica for reporting

texts:
I am trying to recommend my client a SQL server upgrade (from 2005-> 2014) which will allow the users to create record snapshot (via SSRS) from a read-only data source.  Data latency should be kept to a minimum.

My understanding for transactional replication is a subscriber database schema can be a subset of the publisher, and can have different indexes; but I don't think an AG secondary replica can have a different schema from primary, even though a secondary replica can have different indexes?

I am also thinking about having the SSRS Report DB/ReportTempDB and operate independently rather than get replicated/synchronized.  I think it is OK to have the secondary replica/subscriber to communicate to the SSRS Report DB/ReportTempDB via SSRS, right?

Note that for my client the DR is NOT a main concern.


texts: Finding repeating sets of data by foreign key

texts:
I have a table, ResultItem:

CREATE TABLE ResultItem (
    IDResultItem INT PRIMARY KEY IDENTITY(1,1),
    ItemValue VARCHAR(20) NOT NULL,
    ResultID INT FOREIGN KEY REFERENCES Result(IDResult)
)

I need to find all the most recent records in Result that have the same group of values in ResultItem in a previous Result, and concatenate the ItemValues in another column; for example:

INSERT INTO ResultItem
    (ItemValue, ResultID)
VALUES
    ('a', 1),
    ('b', 1),
    ('a', 2),
    ('b', 2),
    ('c', 3),
    ('d', 3),
    ('c', 4),
    ('d', 4),
    ('a', 5),
    ('d', 5),
    ('a', 6),
    ('a', 7) ;

the query should return:

| 2 | 'ab' |
| 4 | 'cd' |
| 7 | 'a'  |

as 1 and 2 are the same, 3 and 4 are the same, and 6 and 7 are the same.  5 has not yet been repeated so I do not want it.  How would I go about doing this?


texts: Distinct on two columns, how to rid from 'duplicates'

texts:
I have a table with messages, that have 'msg_from' and 'msg_to' fields.

when I want to select all current chats and last message from each of them I use the following query:

select distinct on (msg_from, msg_to) array[msg_from, msg_to]
as participants from mockdata_messages
order by msg_from, msg_to DESC

example: https://www.db-fiddle.com/f/n1Bttz4i9Cd5RVA9qCpNkH/1

This works fine in Postgres 12

The problem is that I get 'duplicates' in response. I mean, 'participants' values like: [2, 5] and [5, 2], which obviously refers to the same chat.

I got exactly simillar result when I tried to use 'group by' with two columns.
How can I get arround this problem? What approaches are well to use for storing chat messages in DB?


texts: MySQL: Delete a record from all tables in one shot

texts:
I have a MySQL database, with one table that has a primary key, f_id that is referenced in multiple table as a foreign key.

I want to delete all records where f_id is a specific value from all referenced tables in a single batch.

I am attempting to delete a specific f_id record in the primary table, however I cannot since there are records present in the referenced tables containing that f_id value.

How can I delete all related records at once without having to hand-write a query for each and every related table?


texts: What does "Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 64" in the SQL Server Log mean?

texts:
Every once in a while, I see the error

  Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 64

in the SQL Server log. Oddly this seems to get thrown at off hours where there is no job or backup running at all.

This is a physical install of SQL Server with 128GB RAM. Here are the version details:

  Microsoft SQL Server 2014 - 12.0.4213.0 (X64) Jun 9 2015 12:06:16 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3  (Build 9600: )

Looking for some guidance.


texts: Procedure permission issues with a SQL state query

texts:
We have a login/user with DBO to a single database, and now we're adding a query that's providing some basic monitoring information requiring permissions extending somewhat to the sysadmin side.

Basically, the query needs to be able to run (read only) the following commands:

DBCC SQLPERF(logspace)
EXEC master..xp_fixeddrives
dbo.sysfiles
sys.master_files
tempdb.sys.database_files
tempdb.sys.dm_db_file_space_usage
sys.dm_exec_requests
sys.dm_tran_locks
sys.partitions
sys.indexes
sys.dm_os_waiting_tasks
sys.dm_os_tasks
sys.dm_exec_sessions
sys.dm_exec_sql_text

Most of that stuff entails permissions like VIEW SERVER STATE, VIEW ANY DEFINITION, etc, all of which can be used to grant the permissions required without going to the sysadmin server role. But we only want to limit these permissions to this single query.

So what we're looking for is options. My very last preferred option is to add the various permissions to the login/user, since then it would have access to more than it needs (various sub-permissions of the sysadmin server role), so I tried creating a procedure with the EXECUTE AS clause, only apparently because of the dynamic SQL inside (we need to output the results of DBCC SQLPERF into a table variable), the ownership chaining gets difficult. Even regardless of that, it seems it's quite tricky to allow a procedure to execute with borderline sysadmin credentials.

I've browsed more about this subject than I can understand, and I still can't get this to work. Basically, can you guys tell me how to do this without creating security holes.

Just a batch, that runs this one query, can only be altered by the sysadmin, and can be read / executed by anyone given the permissions to run it? Thanks!

EDIT:

I'm currently examining the possibility of creating a proxy logins and / or signing the procedure with a certificate. Seems quite the interesting an option.


texts: Best practice for "recycling" rows on PostgreSQL (UPDATE vs INSERT + DELETE)

texts:
I searched responses on "UPDATE vs INSERT + DELETE" in general terms and the consensus is UPDATE is faster, as expected.
My question is centered on a somewhat niche use case. Let's say you have a table of fairly high-frequency ephemeral data. For example, modelling notifications on a social network, those are created very often and you always see the latest N.
Would it make sense to keep the latest N (say 20) and when one comes in, the Nth row is UPDATED into the new one (ID included). Rather than inserting a new one and then trimming old rows (maybe in bulk on an interval)?
Any thoughts?


texts:
We have 4 geographic regions where data is getting replicated and expected to be real time

Mountain View(main source of ~160 GB), Atlanta, Netherlands, Singapore

We have 2 set of servers App server and reporting servers for all these 4 geographic regions

We are mainlining 2 different publications from Mountain View(where 70% of them are same articles)

Due to few issues replication broke and we did the initialization which took ~48 hrs  due to different geographic regions and entire business was down for 2 days.

I told them to replicate the articles from the local app servers to report servers so that it will be fast, but they are not ready for this as in case of failure both app and report servers will not work.

I am thinking for the better plan to achieve this .How about implementing HADR along with replication .Please suggest with few better ideas. Will the Always On work better for different geography too ?


texts:
I am using a db.currentOp() to check the current connection in Mongodb.
How can I get the total number of all record and how can I get all the "client" IP in a one column?
Thanks

db.currentOp()
{
        "inprog" : [
                {
                        "desc" : "WT RecordStoreThread: local.oplog.rs",
                        "threadId" : "1068",
                        "active" : true,
                        "opid" : 77300,
                        "secs_running" : 712,
                        "microsecs_running" : NumberLong(712734301),
                        "op" : "none",
                        "ns" : "local.oplog.rs",
                        "query" : {

                        },
                        "numYields" : 0,
                        "locks" : {

                        },
                        "waitingForLock" : false,
                        "lockStats" : {
                                "Global" : {
                                        "acquireCount" : {
                                                "r" : NumberLong(1),
                                                "w" : NumberLong(1)
                                        }
                                },
                                "Database" : {
                                        "acquireCount" : {
                                                "w" : NumberLong(1)
                                        }
                                },
                                "oplog" : {
                                        "acquireCount" : {
                                                "w" : NumberLong(1)
                                        }
                                }
                        }
                },
            {
                    "desc" : "conn52",
                    "threadId" : "4692",
                    "connectionId" : 52,
                    "client" : "10.10.50.218:60909",
                    "active" : true,
                    "opid" : 101277,
                    "secs_running" : 2,
                    "microsecs_running" : NumberLong(2883686),
                    "op" : "getmore",
                    "ns" : "local.oplog.rs",
                    "query" : {
                            "getMore" : NumberLong("15696192304"),
                            "collection" : "oplog.rs",
                            "maxTimeMS" : NumberLong(5000),
                            "term" : NumberLong(117),
                            "lastKnownCommittedOpTime" : {
                                    "ts" : Timestamp(1501842736, 18),
                                    "t" : NumberLong(117)
                            }
                    },
                    "planSummary" : "COLLSCAN",
                    "numYields" : 0,
                    "locks" : {

                    },
                    "waitingForLock" : false,
                    "lockStats" : {
                            "Global" : {
                                    "acquireCount" : {
                                            "r" : NumberLong(2)
                                    }
                            },
                            "Database" : {
                                    "acquireCount" : {
                                            "r" : NumberLong(1)
                                    }
                            },
                            "oplog" : {
                                    "acquireCount" : {
                                            "r" : NumberLong(1)
                                    }
                            }
                    }
            },
....
...
..


texts: nested set - Get ID from last sub entry with MySQL

texts:
I need the last Child item ID from this nested set table
So i would need ID 4,10,11,16 or "rgt" values in this Example
What i found is just the query below.

THis is what the table looks like:

+----+---------+----------------+-----+-----+
| id | root_id | LongName       | lft | rgt |
+----+---------+----------------+-----+-----+
|  1 |       1 | Clothes        |   1 |   8 |
|  2 |       1 | Boys           |   2 |   3 |
|  3 |       1 | Girls          |   4 |   5 |
|  4 |       1 | Gender neutral |   6 |   7 |
|  5 |       5 | Toys           |   1 |  14 |
|  6 |       5 | Boys           |   2 |   3 |
|  7 |       5 | Girls          |   4 |   5 |
|  8 |       5 | Gender neutral |   6 |   7 |
|  9 |       5 | Shoes          |   8 |  11 |
| 10 |       5 | Toys_TEST      |   9 |  10 |
| 11 |       5 | Buggys         |  12 |  13 |
| 12 |      12 | Maternity Wear |   1 |  10 |
| 13 |      12 | Trousers       |   2 |   3 |
| 14 |      12 | Nursing Bra    |   4 |   5 |
| 15 |      12 | Dresses        |   6 |   9 |
| 16 |      12 | Skirts         |   7 |   8 |
+----+---------+----------------+-----+-----+

Table Structure:

CREATE TABLE `nest` (
  `id` bigint(11) NOT NULL,
  `root_id` bigint(11) NOT NULL,
  `LongName` varchar(255) NOT NULL,
  `lft` bigint(11) NOT NULL,
  `rgt` bigint(11) NOT NULL
)

Table Data:

INSERT INTO `nest` (`id`, `root_id`, `LongName`, `lft`, `rgt`) VALUES
(1, 1, 'Clothes', 1, 8),
(2, 1, 'Boys', 2, 3),
(3, 1, 'Girls', 4, 5),
(4, 1, 'Gender neutral', 6, 7),
(5, 5, 'Toys', 1, 14),
(6, 5, 'Boys', 2, 3),
(7, 5, 'Girls', 4, 5),
(8, 5, 'Gender neutral', 6, 7),
(9, 5, 'Shoes', 8, 11),
(10, 5, 'Toys_TEST', 9, 10),
(11, 5, 'Buggys', 12, 13),
(12, 12, 'Maternity Wear', 1, 10),
(13, 12, 'Trousers', 2, 3),
(14, 12, 'Nursing Bra', 4, 5),
(15, 12, 'Dresses', 6, 9),
(16, 12, 'Skirts', 7, 8);

my query so far:

select root_id, max(rgt) as max_rgt
from (
    select * from nesttable where rgt - lft = 1
) as TT
group by root_id

with the result:

+---------+---------+
| root_id | max_rgt |
+---------+---------+
|       1 |       7 |
|       5 |      13 |
|      12 |       8 |
+---------+---------+

the Result should look like:

+---------+---------+
| root_id | max_rgt |
+---------+---------+
|       1 |       7 |
|       5 |      10 |
|       5 |      13 |
|      12 |       8 |
+---------+---------+

the best thing would be if i get the IDs from the last children.
Something like this would be perfect:

+----+
| id |
+----+
|  4 |
| 10 |
| 11 |
| 16 |
+----+

From this table i need the id 4,10,11,16 or rgt values of this child but i just got rgt values of ID 4,11,16 with this Query.


texts: Is it okay to have a fill factor of 100 on the clustered index that is on an ID column?

texts:
So I have a table with a clustered index on an ID column (which is an auto-increment column) in SQL Server.

Is it okay to have a fill factor of 100 on this since the data will always get written with the next latest ID? Would it matter if the other columns on this table get updated a lot for the existing IDs?

Also, how would deletes impact the fragmentation of the table?


texts: AWS RDS postrgres massive disk use, small tables

texts:
I can't figure out why our AWS postgres server has consumed all of it's space. We just had to up the storage space allocated to it, but can't find any hint from postgres that it's using that much space.

Amazon says that we've eaten up about 60GB in the last 2 weeks. Postgres says our whole DB is barely over 5GB, the DB operations are predominantly INSERT and SELECT.

How can I track down and reclaim our storage?

Here's the output of some size commands in PSQL

select pg_size_pretty(pg_database_size('my_db'));
-- 3730 MB

SELECT     pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 20;

Gives

1540 MB
1286 MB
235 MB
191 MB

Curiously the autovacuumer has never triggered on any of the tables (I'm assuming this is because we rarely delete a row?)
The number of dead rows is quite low relative to table size (these tables have row counts in the  hundreds of thousands or millions)

SELECT
  vacuum_count, autovacuum_count, n_dead_tup
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC limit 5;

0   0   161
0   0   119
1   0   76
0   0   25
0   0   11

Predominantly the operations on the database are INSERT and SELECT, with some things getting UPDATES, and very rarely a DELETE.
We use a lot of JSONB.

UPDATE

\l+ shows same as the other queries: 3730 MB


texts: Postgres how to update first row in the child table for all parent entries

texts:
I have a table parent table workflow and child table workflow_task having one to many mapping.

workflow`

workID
workTitle

1800
vpInv1231

1801
vpInv1231

workflow_task

id
task_Type
workflow_id(fk)

1
null
1800

2
null
1800

Is there any way I can update all the first row in the child table with taskType as 'POC' and rest(from second to so on) as 'Appr'?.


texts: DB2 PURGE (old Online Backups and Archive logs) - some of the archive logs are not removed

texts:
DB is in Archive Mode.
We are creating full Online Backups every night at 23:00.
We set automatic removal of old online backups and archive logs.

AUTO_DEL_REC_OBJ ON
NUM_DB_BACKUPS   2
REC_HIS_RETENTN  2

In backup folder we have two online backups and that is ok:

Backup1 22.02 23:00
Backup2 23.02 23:00

In the archive logs folder we have these logs:

from S0000047.LOG to S0000057.LOG (from 22.02 23:00 to the 23.02 22:41)
from S0000058.LOG to S0000062.LOG (from 23.02 23:00 to this morning 24.02)

Those are OK logs of course.
But I noticed that we have also archive logs which should not be there (logs after the backup of 21.02 which is already deleted and all those transactions are already contained within the backup of 22.02). But we have archive logs

from S0000037.LOG to S0000046.LOG (from 22.02 01:41 to the 22.02 22:42)

So those are unnecessary logs.
On some other system (server) for some other customer we have also enabled this purging but there we have regular situation - only backups and archive logs after those backups are kept, there are no previous archive logs, but on this system it seems like deletion of all archive logs is late to 1 day.

Why is that I really think this is not regular situation (as I said also based on experience on some other server)


texts: How to fully recreate database in MS SQL server 2016 from exported data

texts:
Recently I get many consistency errors very often in my ~40Gb database. Of course I use DBCC CHECKDB, even with REPAIR_ALLOW_DATA_LOSS (when it asks), but then again I get errors, even after read-only operations.

Database itself is stored on SSD drive which is ok, so I suspect there are some hidden errors in database file.

So, the full cycle: I have MSSQL 2016 on local PC, with only 1 user attached. Every day I get data (~1.5M records) in custom format, backup base and insert data to the base (non-bulk inserts, indexes on). During that operation no one touches the base. Later from time to time I make high load queries (different statistics). And often I get consistency errors, check sum errors, index errors (don't have exact output stored in the moment). Index errors can be corrected by rebuilding indexes; others can only be fixed with DBCC CHECKDB using the REPAIR_ALLOW_DATA_LOSS option. After that, it works again - for a little while.

I don't believe the problem is with my SSD drive. I have tried different drives on this PC over the past few years, with no change in behavior.

I suspect that, even after running DBCC CHECKDB successfully, there are some hidden errors in file structure which lead to new errors later. So I want extract only the data to linear file and then import it. Standard Export wizard exports hundreds of lines and then stopped without error.

Is there a correct and robust way to export the whole DB to text/binary file(s) (but not mssql) and then recreate database without data losses from those file(s)? I tried backup-restore, but it looks like database consistency problems persist through restoration.


texts:
I'm currently going through the process of learning about database normalization & having a crack at creating my first relational database. It was going well until I hit the following problem.

In a non-normalised form my justifications table would like like this:

| justification_id | user_id | question | response | action | category | category_id | submitted_at |
-----------------------------------------------------------------------------------------------------

My research & thought process led me to the following:

justification_category

| justification_id | category | category_id |

justification_response

| justification_id | question | response |

justification_action

| justification_id | user_id | action | submitted_at |

How does a justification work?

When a user performs an action on a category ( the current categories being tickets, orders, accounts ) a justification is required by the user to state why they are performing that action. I.e. account deletion

An action can have multiple questions attached resulting in multiple responses. For example: Deleting an account would ask the questions: Have you followed our internal steps? Why do you need to perform this action? etc. This is why I proposed the justification_response table because it will not store any NULL fields.

The justification_action table will log the action performed, the user_id ( this is the submitter of the action ) & the time of the action.

Questions

I currently have 3 potential categories where a justification is required on an action, they're tickets, orders & accounts. With this in mind would it be a better approach to have 3 separate tables to describe each category? Would this approach meet 2NF?

I feel I am far away from a 2NF relation. This is because I'm constantly re-using the justification_id as either a FK or a PK. How far away am I?


texts: Join is causing other column data to change?

texts:
I'll lead off with saying that I am relatively new to databases and SQL and I am trying to make an addition to a View in SQL Server that a previous co-worker created.

I am trying to join the Customer_Snacks table so that I can get the Snack Quantity for each customer's order. The issue I'm having is when I do a LEFT OUTER JOIN which, as far as I can tell, should work, some of the other columns data is drastically changed.

I managed to get it to work the way I would like it to by using a subquery but it is DREADFULLY slow. The query takes over a minute to run and in return, the application that is making the call for that view times out.

Here is what I currently have that works (I know, it's messy):

SELECT
    cl.CustomerID,
    cl.FirstName + ' ' + c.LastName as CustomerDisplay,
    cl.InvoiceID,
    cl.MealPlanID,
    mp.DeliveryDate1 as DeliveryDate,
    SUM(CASE WHEN cll.Name = 'Meal' THEN cll.Quantity ELSE 0 END) as RegularQuantity,
    SUM(CASE WHEN cll.Name = 'Meal' THEN cll.Quantity * Amount ELSE 0 END) as RegularTotal,
    SUM(CASE WHEN cll.Name = 'EXTRAPROTEINMEAL' THEN cll.Quantity ELSE 0 END) as ProteinQuantity,
    SUM(CASE WHEN cll.Name = 'EXTRAPROTEINMEAL' THEN cll.Quantity * Amount ELSE 0 END) as ProteinTotal,
    SUM(CASE WHEN cll.Name = 'TAX' THEN Amount ELSE 0 END) as TotalTax,
    SUM(CASE WHEN cll.Name = 'SNACKS' THEN Amount ELSE 0 END) AS SnackTotal,
    -- Here is my subquery that I would like to integrate into this view with a join so that it's much faster
    (SELECT SUM(cs.Quantity) FROM Fustomer_Snacks as cs WHERE cs.CustomerID = cl.CustomerID and cs.DeliveryDate = mp.DeliveryDate1) as SnackQuantity,
    ------------------------------------------------------------------
    CAST(SUM((CASE WHEN cll.Name = 'home' or cll.Name = 'work' THEN 1 ELSE 0 END)) as bit) as IsDelivery,
    SUM(CASE WHEN (cll.Name = 'home' or cll.Name = 'work' or cll.Name = 'pickup') and cll.Quantity > 0 THEN cll.Quantity * Amount else 0 END) as DeliveryCharge,
    cl.Balance as PrePromoTotal,
    ISNULL(cl.Balance * (pc.Discount * .01), 0) * -1 as PromoDiscount,
    cl.FinalBalance - ISNULL((cl.FinalBalance * (pc.Discount * .01)),0) Total
FROM
    Customer_Ledger as cl
INNER JOIN
    Customer_Ledger_LineItems as cll
        on cl.ID = cll.LedgerID
INNER JOIN
    MealPlan as mp
        on cl.MealPlanID = mp.ID
INNER JOIN
    Customer as c
        on cl.CustomerId = c.Id
LEFT OUTER JOIN
    PromoCode as pc
        on cl.BalancePromoId = pc.Id
GROUP BY
    cl.CustomerID,
    c.FirstName,
    c.LastName,
    MealPlanID,
    InvoiceID,
    mp.DeliveryDate1,
    cl.Balance,
    cl.FinalBalance,
    pc.Discount

Like I said, the way I currently have it, it works but it's way too slow. As soon as I try a LEFT OUTER JOIN it's a lot faster but other columns like RegularQuantity become inflated with other numbers (and I'm not sure how that's even happening).

If anyone can guide me in the right direction, that would be great!


texts: Getting bounds in result with width_bucket

texts:
I'm trying to do a query using width_bucket and having a column in the results with the bounds of my buckets. Is there a simple way to do this?
My query:
SELECT width_bucket(
            (EXTRACT(epoch FROM S.end - S.start) / 60)::integer,
            array[0, 15, 30, 45, 60, 75, 90, 1000]
       ) AS buckets,
       count(*)
FROM shipments_site S
WHERE S.deleted IS NULL
GROUP BY buckets
ORDER BY buckets

Result:
buckets | count
1       | 20
2       | 6
3       | 22
4       | 25
5       | 10
6       | 11
7       | 6

What I'm trying to get:
buckets | count    | interval
1       | 20       | [0, 15]
2       | 6        | [15, 30]
3       | 22       | [30, 45]
4       | 25       | [45, 60]
5       | 10       | [60, 75]
6       | 11       | [75, 90]
7       | 6        | [90, 1000]


texts: How can we select data from another table during an insert?

texts:
I'm trying to insert data into a table using a few columns that are hard-coded or are static values and then the rest of the datat selected from another table. I've tried the following query:
insert into wtdocversion
values (( select id from wtdocmaster),'col2','col3');

But this generates an error:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


texts:
I'm building a package to execute a procedure with parameters from a configuration table.

I've built two Execute SQL Task boxes. One populates SSIS variables from the configuration table. The second executes the procedure with those variables.

I'm facing the following behaviour. Integer variables have a 0 as a default value and strings an empty string. Even after getting NULL from the configuration table those variables keep their values.

How can I change this behavior to apply SQL Server logic of NULL as a default "value", or just to enable NULL that they get from configuration table to override their default value?


texts: pg_timezone_names view is empty - can be recreated?

texts: For some strange reason pg_timezone_names view is empty. Is there a way to recreate it?


texts: Sqlite - Limit Characters for different parts of one String with Check

texts:
I have to make sure that any inserted Email has the format: "X@Y.Z"

X and Y may only contain letters and numbers and Z can only have Letters.

I tried something like this, but i cant figure out how to limit the Characters for X,Y and Z separately.

Create Table User(
Mail varchar check(Mail NOT GLOB '*[^A-Za-z0-9@.]*' AND Mail LIKE '%_@%_._%'));


texts: How to sync logins and jobs when using an availability group?

texts:
So I am configuring availability group which will sync the databases from primary replica to the secondary replicas.
How to sync the jobs and logins so that when there is failover to secondary then the logins and jobs can continue working on the secondary?


texts: Better execution plan if parameter is stored in local variable

texts:
I have two stored procedures. This one is incredibly fast (~ 2 seconds)

CREATE PROCEDURE [schema].[Test_fast]
   @week date
AS
BEGIN

    declare @myweek date = @week

    select distinct serial
    from [schema].[tEventlog]         as e
    join [schema].tEventlogSourceName as s on s.ID = e.FKSourceName
    where s.SourceName = 'source_name'
        and (e.EventCode = 1 or e.EventCode = 9)
        and cast(@myweek as datetime2(3)) <= [Date]
        and [Date] < dateadd(day, 7, cast(@myweek as datetime2(3)))
END

And this one runs slow (~ 2 Hours):

create PROCEDURE [schema].[Test_slow]
   @week date
AS
BEGIN

    select distinct serial
    from [schema].[tEventlog]         as e
    join [schema].tEventlogSourceName as s on s.ID = e.FKSourceName
    where s.SourceName = 'source_name'
        and (e.EventCode = 1 or e.EventCode = 9)
        and cast(@week as datetime2(3)) <= [Date]
        and [Date] < dateadd(day, 7, cast(@week as datetime2(3)))
END

The only real difference is the line (using the local variable @myweek):

declare @myweek date = @week

Here are the execution plans. The first plan is from [schema].[Test_fast] and the second is from [schema].[Test_slow]:

My question is: Why does SQL Server 2012 get a much better execution plan (faster) when I take the parameter and store it in a local variable, and then use this local variable. Is there something broken with the statistics or the indexes? (I also wonder why the second execution plan is not using any kind of parallel execution).

UPDATE:

I give the 2 SPs the same Parameter and started them in the same time (nearly 2s time diff) the is not auto update of statistic in this DB.

example:

EXEC    [schema].[Test_fast]
        @week = '2016-02-08'

EXEC    [schema].[Test_slow]
        @week = '2016-02-08'

Here is the Execution Plan:

https://gist.github.com/anonymous/6e404f896d9613c2061a#file-sp_execution_plan-sqlplan

An additional update of the index does also have no effect.


texts: Tsql Query speed slow due to Or within where clause, causing index scan instead of seek

texts:
I have been working on making this query work more efficiently.

I found that the number of Ors within the where clause are the biggest problem within this query.  This query sits in a stored procedure.

I am to the point where the only options I can think of is. :

Creating 16 different queries for all the different inbound parameter possibilities.
Creating a dynamic sql query but I don't believe this will be any faster
Reverting back to string sql, but I don't ever like to do this as these do not perform as quickly as stored procedures.

I am sure others have ran into this issue before.  The query performance isn't terrible to begin with at roughly a second or less, but there are instances where it is hit numerous times causing a lag of up to 5 or 6 seconds.

Query below. :

DECLARE @PERSON_ID AS INT
DECLARE @ITEM_ID AS INT
DECLARE @ITEM_VERSION AS INT
DECLARE @ITEM_SUB_NAME AS VARCHAR(250)
DECLARE @ITEM_SUB_SUB_NAME AS VARCHAR(250)

--DEFAULTS
SET @PERSON_ID = 0
SET @ITEM_ID = 0
SET @ITEM_VERSION = 1
SET @ITEM_SUB_NAME = NULL
SET @ITEM_SUB_SUB_NAME = NULL

    SELECT ID, PERSON_ID,
           ISNULL(ITEM_VERSION, 1) AS ITEM_VERSION,
           ISNULL(ITEM_SUB_NAME, '') AS 'ITEM_SUB_NAME',
           ISNULL(ITEM_SUB_SUB_NAME, '') AS 'ITEM_SUB_SUB_NAME',
           ISNULL(ITEM_DATE, '1/1/1900') AS 'ITEM_DATE',
    FROM PERSON_TBL s WITH (NOLOCK)
    WHERE    ( PERSON_ID = @PERSON_ID OR @PERSON_ID = 0 )
    AND ( ITEM_VERSION = @ITEM_VERSION OR ( @ITEM_VERSION = 1 AND ITEM_VERSION IS NULL ))
    AND ( EMPLOYEE_ID = @EMPLOYEE_ID OR @EMPLOYEE_ID = 0 )
    AND ( ITEM_SUB_NAME = @ITEM_SUB_NAME OR @ITEM_SUB_NAME IS NULL )
    AND ( ITEM_SUB_SUB_NAME = @ITEM_SUB_SUB_NAME OR @ITEM_SUB_SUB_NAME IS NULL )
    ORDER BY PERSON_ID, ITEM_SUB_NAME


texts: Multiple Filters for Databases on SQL Server Server Level Audit

texts:
I am attempting to create a Server Level Audit to track Database Level events in SQL Server 2014.  I know I can create them at the database level but I want the audits all databases at the server level if this is possible.

I need to filter the Server Level Audit for two databases and only for users who are not the two application users but my filter is not working correctly.  Either I am building the filter wrong or it is not possible to filter multiple database and multiple users.  My Google-Fu is failing me in finding examples with multiple filters:

Any assistance or ideas are appreciated.

CREATE SERVER AUDIT [PCIAudit]
TO FILE
(   FILEPATH = N'D:\SQLAuditLogs\'
    ,MAXSIZE = 2048 MB
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 0
    ,ON_FAILURE = CONTINUE
)
WHERE ([database_name] = 'DataBase1' OR [database_name] = 'DataBase2' AND [server_principal_id] <> 277 OR [server_principal_id] <> 278 AND NOT [statement] like 'ALTER INDEX%REBUILD%' AND NOT [statement] like 'ALTER INDEX%REORGANIZE%')
GO

ALTER SERVER AUDIT [PCIAudit]
WITH (STATE = ON);
GO

USE [master]

GO

CREATE SERVER AUDIT SPECIFICATION [DDLAudit]
FOR SERVER AUDIT [PCIAudit]
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (DATABASE_OBJECT_ACCESS_GROUP)
WITH (STATE = ON)
GO


texts: How to get MySQL to iterate a subquery in an aggregate select?

texts:
Because the vocabulary describing what I'd like to do can be so disparate, I haven't had any luck figuring out why MySQL 5.6 is doing what it does.

Consider the following two simple queries:

#1
select sum(amount) as amt,
    (sum(amount) * (0.000+coalesce((select gas from gas_prices gp where gp.yw=yearweek(gl.stamp,0)),3.50))) as cost
from gas_log gl
where date_format(gl.stamp,'%X')=2013;

#2
select sum(amount) as amt,
    (sum(amount) * (0.000+coalesce((select gas from gas_prices gp where gp.yw=yearweek(gl.stamp,0)),3.50))) as cost
from gas_log gl
where date_format(gl.stamp,'%X')=2013 group by yearweek(gl.stamp,0);

The second query is identical to the first, with a simple group_by to get the weekly totals instead of the yearly totals. Both queries have near-identical explain outputs:

+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | PRIMARY            | gl    | ALL  | NULL          | NULL | NULL    | NULL | 7428 | Using where; Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | gp    | ALL  | yw            | NULL | NULL    | NULL |   52 | Using where                                  |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+

+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | gl    | ALL  | NULL          | NULL | NULL    | NULL | 7428 | Using where |
|  2 | DEPENDENT SUBQUERY | gp    | ALL  | yw            | NULL | NULL    | NULL |   52 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+

gas_log contains a timestamp of when gas was pumped and how much was pumped. gas_prices looks like this:

10:11:09> select * from gas_prices limit 2;
+------------+--------+-------+--------+
| date       | yw     | gas   | diesel |
+------------+--------+-------+--------+
| 2013-01-07 | 201301 | 3.235 |  3.870 |
| 2013-01-14 | 201302 | 3.265 |  3.834 |
+------------+--------+-------+--------+

For the first query, MySQL only executes the subquery once and uses that value (found by matching the first row retrieved from gas_log against its corresponding week in gas_prices) to multiply against the sum of all gallons logged in the gas log, whereas in the second query it does what I'm actually after: execute the subquery for each of the 52 grouped weeks in 2013, matching each week's gas price accordingly.

Using with rollup on the group by provides an interesting result; instead of using the first gas price in the timerange, it uses the last one! Thus, it still provides an incorrect cost total for any timerange spanning more than week.

Is there a way to rewrite the query to get MySQL to present one total for a given timerange, yet still match each gas_log row to its corresponding price in gas_prices?


texts: How to improve performance on PostgreSQL when using multiple concurrent processes?

texts:
Seeing query performance which is not satisfactory on our Python application, which runs several processes that use SQLAlchemy core to access a PostgreSQL 9.2 database.  We may have around 100 - 200 or more concurrent processes executing queries against the database.

We have no connection pooling systems like pgpoolII or pgbounce currently installed.

I have tested a query which can take in the order of 1-10 seconds from our application, which when run over psql will take at most 20 ms.

We believe this issue is related to concurrent connections particularly because of the behavior described above and because we see these performance hits when accessing different tables in the system.  However, we are not sure of this and could very readily accept alternate suggestions or solutions.

What is the usual way of handling concurrent connections from multiple processes against a single database server, in a way that will enable queries to execute quickly?


texts: Column definition for a SQL Server equivalent to Access BOOLEAN type

texts:
The BOOLEAN datatype in Access (ie, Jet/ACE) returns a -1 for True and 0 for False; the field is always required (i.e., it cannot be set to NULL).  The SQL Server BIT type returns 1 for True and 0 for False and also allows NULLs.

I'm looking for a T-SQL column definition using a combination of smallint, check constraints, and NOT NULL to replicate the functionality of the Jet/ACE BOOLEAN datatype.  In other words, the column would only accept the values 0 and -1.


texts:
When I try to dump a whole database using
mysqldump -htest.com -uabc -p --column-statistics=0 abc > abc.sql

The following error occured.
mysqldump: Got error: 1066: Not unique table/alias: 'temp1' when using LOCK TABLES

I have no problem in dumping other database in the same host, only this database is having problem.
What is the cause and how I can fix it?


texts: mongodb removeShard issue with config.system.session

texts:
I have a staging mongodb 4.0.19 sharded cluster with 2 replica set shards . I want to remove one shard. I followed the documentation: https://docs.mongodb.com/v4.0/tutorial/remove-shards-from-cluster/index.html and used removeShard command.
But one chunk cannot be moved: the config.system.sessions chunk. I have the following error:
2020-07-30T09:21:07.330+0200 W SHARDING [conn21] Chunk move failed :: caused by :: OperationFailed: Data transfer error: migrate failed: InvalidUUID: Cannot create collection config.system.sessions because we already have an identically named collection with UUID b788097c-5ba1-464c-9064-0b7faed5c196, which differs from the donor's UUID 22588e86-20a2-4cdf-a70e-803fbd7b3e5a. Manually drop the collection on this shard if it contains data from a previous incarnation of config.system.sessions
This prevents draining chunks to finish. Is there any way to drop that collection or to force removeShard command to finish ?


texts: Delete/remove orphaned users and their schema from server/database

texts:
I'm new to the role of being a SQL Server DBA for SQL Server 2008 R2.

I need help on how to find out orphaned users in SQL Server and how to delete/remove the orphaned users and their corresponding schema from the database.

Please provide a step by step solution.


texts: How to replicate schema changes from local database to a remote?

texts:
I was working with some big database and I want to replicate local changes from a local database (I just added some columns to a few tables) to a remote database on other server.

The thing is that I can't backup data and create new schema and after that put in all data again cause the data is huge like 10.GB, I think there should be a better way of doing this.

So what is the properly way of doing this?

Note:
When I was doing changes at MS SQL Server Management Studio using the design view the IDE don't give me any sql log with changes.


texts: Efficiently retrieve big amount of data based on date range

texts:
Table EVENTS

TimeStamp  |O.Name   |Location  |Source     |
2008-11-12 |Pretorian|Európe    |England    |
2009-12-24 |Lex Rosa |Európe    |Italy      |
2010-01-01 |Nasdaq   |USA       |Whasington |
2010-02-12 |Plank    |Australia |Western Aus|
2010-03-11 |Pretorian|Európe    |England    |
2011-05-01 |Pretorian|Európe    |England    |

The above table is a representation of the table where different events are getting recorded. Events are created in fast time periods, so every minute there is an event created, therefore the table containing the data is really big.

I have a query to select the rows that match some kind of criteria taken from a different table and different database. This is how the 2nd table looks like.

Table TAGS

ID| Location| Source| Tagname
01| EU      | ENG   | Pretorian
02| EU      | IT    | Lex Rosa
03| USA     | WA    | Nasdaq
04| AUS     | WA    | Plank

The idea is that whenever I choose a date range e.g 2009-01-01 and 2011-06-06 I should get these rows:

2010-03-11 |Pretorian|Európe    |England    |
2011-05-01 |Pretorian|Európe    |England    |

For this I have the following query, but the execution time is really slow:

DECLARE @MaxTS Datetime

SELECT @MaxTS = MAX(TimeStamp)
FROM LinkedServer.EVENTS
WHERE O.Name IN
(SELECT Tagname COLLATE DATABASE_DEFAULT FROM MyDatabase.TAGS
WHERE LOCATION = 'EU' AND SOURCE = 'ENG')

IF @MaxTS > '2010-01-01'
BEGIN
  SELECT * FROM LinkedServer.EVENTS
  WHERE O.Name IN
                 (SELECT Tagname COLLATE DATABASE_DEFAULT FROM MyDatabase.TAGS
                  WHERE LOCATION = 'EU' AND SOURCE ='ENG')
END
ELSE
   PRINT 'Date out of range'

In this query I first look up the max date for the entry where location is EU and Source is ENG, and after I look up all the dates till the selected date if this is smaller than the MaxDate. Even though I get the results I am looking for, I am more curious if there is any way I could make this perform better. If you have any suggestions please let me know. Thank you!

EDIT: stored procedure used

ALTER PROCEDURE [dbo].[MyProc]
@StartDate  NVARCHAR(19),
@EndDate    NVARCHAR(19)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
DECLARE @TSQL nvarchar(4000);
DECLARE @MaxDateTS nvarchar(1500);

    SELECT @MaxDateTS = MAX(TimeStamp)
    FROM LinkedServer.Events
        WHERE O.Name IN
    (SELECT Tagname COLLATE DATABASE_DEFAULT FROM MyDatabase.TAGS       WHERE LOCATION = 'EU' AND SOURCE = 'ENG');

IF @MaxDateTS > @StartDate
BEGIN
    SELECT * FROM LinkedServer.Events
               WHERE O.Name IN
    (SELECT Tagname COLLATE DATABASE_DEFAULT FROM MyDatabase.TAGS       WHERE LOCATION = 'EU' AND SOURCE = 'ENG') AND TIMESTAMP >=  @StartDate  AND TIMESTAMP <  @EndDate
END
ELSE
PRINT 'Date out of range'

SET @TSQL = '(' + @MaxDateTS + ') ORDER BY TimeStamp';

PRINT(@TSQL);

EXEC (@TSQL);
END


texts: Database Design for Maintaining Supplier Invoice

texts:
I am not sure whether it is right to ask such question here. However, It comes to my mind this would be the best place to start. My needs are quite simple. As a distributor, I want to maintain all invoices that come from my suppliers. So I stored the invoices in like below:

  +----+------+------------+-----------+---------+---------+---------+--------+
  | id | spid |  transdate | transtype | transno |  debit  |  credit | userid |
  +----+------+------------+-----------+---------+---------+---------+--------+
  |  1 |   2  | 2017-04-04 |  Invoice  |   MI09  | 2500.00 |    0.00 |    2   |
  +----+------+------------+-----------+---------+---------+---------+--------+
  |  2 |   2  | 2017-04-05 |    Pay    |   B098  |    0.00 | 1000.00 |    2   |
  +----+------+------------+-----------+---------+---------+---------+--------+
  |  3 |   2  | 2017-04-07 |  Invoice  |   MI89  | 5000.00 |    0.00 |    2   |
  +----+------+------------+-----------+---------+---------+---------+--------+
  |  4 |   2  | 2017-04-12 |    Pay    |   BI09  |    0.00 | 4000.00 |    2   |
  +----+------+------------+-----------+---------+---------+---------+--------+

In this table I stored the invoices from all my suppliers. But to make things simple, I only showed one supplier with id=2. The problem here is: if I keep storing invoices and my payment in this way, it will soon be too heavy for my table when I have thousands of invoices. I would like to make a printout every month also. So I want to manage the closing and opening of this transaction every month, but I have no idea and google does not help me much.
My question is: How do I manage the closing and opening of this transaction? Do I need another field/table for that? What would that be? Please help.

http://sqlfiddle.com/#!9/eed9b0


texts: Upgrade/Transition Path from MySQL Community 5.6.31 to Enterprise

texts: Full disclosure. I have never upgraded a database. I have a MySQL Community that is not the latest @5.6.31 ( latest is 8 ?) to the paid, Enterprise version. We will ask Tech Support once purchase of Enterprise is fully approved... but really trying to find a clear upgrade path online has proven difficult. I'm a novice, so feel free to be very details in replies.


texts: Why does minimal logging appear to result in a smaller table size?

texts:
I'm testing using the publicly available AdventureworksDW2016CTP3 database.

If I insert the first 100k rows from FactResellerSalesXL_PageCompressed into a page compressed heap without minimal logging I get a table size of 12552 KB. Doing a rebuild of the table brings down the size to 6024 KB. If I do the same insert with minimal logging I get a table size of 5832 KB. Why is the initial table size over 50% smaller when minimal logging is used?

Below is code to reproduce the issue. I'm using SQL Server 2016 SP1:

  Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Home 6.3  (Build 14393: )

No trace flags are enabled.

The actual execution plans are effectively the same. I've verified that minimal logging occurs for the second insert but not the first. Changing the order of inserts does not make a difference. Clearing the buffer cache and plan cache also does not make a difference.

Table creation script:

-- create page compressed heap copy of FactResellerSalesXL_PageCompressed
SELECT * INTO FactResellerSalesXL_PageCompressed_Heap
FROM FactResellerSalesXL_PageCompressed
WHERE 1 = 0;
ALTER TABLE FactResellerSalesXL_PageCompressed_Heap REBUILD WITH (DATA_COMPRESSION = PAGE);

Code for the first insert:

-- insert 100k rows without minimal logging
INSERT INTO FactResellerSalesXL_PageCompressed_Heap
SELECT TOP 100000 *
FROM FactResellerSalesXL_PageCompressed
ORDER BY SalesOrderNumber, SalesOrderLineNumber
OPTION (MAXDOP 1);

-- check space
EXEC sp_spaceused FactResellerSalesXL_PageCompressed_Heap;

Result:

╔═════════════════════════════════════════╦════════╦══════════╦══════════╦════════════╦════════╗
║                  name                   ║  rows  ║ reserved ║   data   ║ index_size ║ unused ║
╠═════════════════════════════════════════╬════════╬══════════╬══════════╬════════════╬════════╣
║ FactResellerSalesXL_PageCompressed_Heap ║ 100000 ║ 12808 KB ║ 12496 KB ║ 8 KB       ║ 304 KB ║
╚═════════════════════════════════════════╩════════╩══════════╩══════════╩════════════╩════════╝

Code for the second insert:

TRUNCATE TABLE FactResellerSalesXL_PageCompressed_Heap;

-- insert 100k rows with minimal logging
INSERT INTO FactResellerSalesXL_PageCompressed_Heap WITH (TABLOCK)
SELECT TOP 100000 *
FROM FactResellerSalesXL_PageCompressed
ORDER BY SalesOrderNumber, SalesOrderLineNumber
OPTION (MAXDOP 1);

-- check space
EXEC sp_spaceused FactResellerSalesXL_PageCompressed_Heap;

Result:

╔═════════════════════════════════════════╦════════╦══════════╦═════════╦════════════╦════════╗
║                  name                   ║  rows  ║ reserved ║  data   ║ index_size ║ unused ║
╠═════════════════════════════════════════╬════════╬══════════╬═════════╬════════════╬════════╣
║ FactResellerSalesXL_PageCompressed_Heap ║ 100000 ║ 5832 KB  ║ 5776 KB ║ 8 KB       ║ 48 KB  ║
╚═════════════════════════════════════════╩════════╩══════════╩═════════╩════════════╩════════╝


texts: Generate metadata file from PostgreSQL server

texts:
Is there a way that I can make a metadata-sheet that lists all the available tables from a postgresql-database, so users can see documentation of the tables?

I know that there are descriptions, column names, number of rows and several more information that could do as metadata for the specific database. But when i have something like 36 tables I'd like to make a documentation of, then I am looking for at way to just generate a table of the metadata of the tables.


texts: Creating a single recid stream for multiple tables

texts:
I'd like to have a single reservoir of RECIDs to apply across multiple tables.

Obviously I can do that by creating a table with a single auto-increment field and inserting every time I need a new RECID.

But that produces a lot of 8-byte+ records that have no real purpose.

A little resident function to delete each record when produced would keep the table pruned back, but I'm wondering whether there's a cleaner way to get to the same goal.


texts: Msg 142, Level 15, State 2 -- Can't figure out what I have wrong

texts:
I've looked at other threads for the error I'm receiving, but I don't see where I'm making the mistake and don't see any extra commas, which seems to be the main cause of issues. I'd be very appreciative if someone could point out where I'm making my error.

CREATE TABLE Accounts.AdminUsers ( admID             INT              NOT NULL    IDENTITY(1,1)
                          , admFN            VARCHAR(35)      NULL
                          , admLN            VARCHAR(35)      NULL
                          , admUserName  VARCHAR(64)      NOT NULL
                          , admPass      VARCHAR(128)     NOT NULL
                          , admPassIV        VARCHAR(32)      NOT NULL
                          , admPassResetDate DATETIME2    NULL
                          , admEEID      VARCHAR(30)      NULL
                          , admEmail         VARCHAR(255)     NULL
                          , admAcctEnabled   BIT              NOT NULL
                          , admHier      HIERARCHYID      NOT NULL
                          , admNumLogins     SMALLINT         NULL
                          , admLastLogin     DATETIME2    NULL
                          , admCreateTS  DATETIME2    NOT NULL
                          , admTID       TINYINT          NOT NULL
                          , CONSTRAINT PK_Accounts_AdminUsers
                            PRIMARY KEY CLUSTERED (admID)
                          , CONSTRAINT DF_Accounts_AdminUsers_admAcctEnabled
                            DEFAULT (1)
                          , CONSTRAINT DF_Accounts_AdminUsers_admCreateTS
                            DEFAULT (CURRENT_TIMESTAMP)
                          , CONSTRAINT FK_Accounts_AdminUsers_AdminUserTypes
                            FOREIGN KEY (admTID) REFERENCES Accounts.AdminUserTypes(admTID)
                          );


texts: When to rebuild and when to reorganize indexes

texts:
I have been trying to gather information about rebuilding and reorganizing indexes. From stackoverflow page (How Often should the indexes be re-build in sql-server DB?) i got this query :

SELECT
    t.NAME 'Table name',
    i.NAME 'Index name',
    ips.index_type_desc,
    ips.alloc_unit_type_desc,
    ips.index_depth,
    ips.index_level,
    ips.avg_fragmentation_in_percent,
    ips.fragment_count,
    ips.avg_fragment_size_in_pages,
    ips.page_count,
    ips.avg_page_space_used_in_percent,
    ips.record_count,
    ips.ghost_record_count,
    ips.Version_ghost_record_count,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.avg_record_size_in_bytes,
    ips.forwarded_record_count
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN
    sys.tables t ON ips.OBJECT_ID = t.Object_ID
INNER JOIN
    sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id
WHERE
    AVG_FRAGMENTATION_IN_PERCENT > 0.0
ORDER BY
    AVG_FRAGMENTATION_IN_PERCENT, fragment_count

The problem is that when i rebuilt indexes the avg_fragmentation_in_percent increased instead of decreasing. Any pointers?? If this is the normal behavior then what am i missing here??

Previously the avg_fragmentation_in_percent was 30 and after rebuild it has increased to 66.


texts: MS-Access vba sql update where not working

texts:
I am having trouble with an MS-Access VBA sql update command.  I have a form which has a combobox of GroupMembershipIDs, and a button.  I am trying to write code which will iterate through the combobox and update the 'Member' field (Yes/No) for all the selected records.

Here is my code:

Private Sub btnRemoveFromGroup_Click()

Dim stDocName As String
Dim VarItem As Variant
Dim sqlcmd As String

For Each VarItem In Me.List12.ItemsSelected
GroupMembershipID = Me.List12.Column(0, VarItem)
MsgBox GroupMembershipID
'That is OK

sqlcmd = "UPDATE 1_tbl_Memberships SET [1_tbl_Memberships].Member =
No WHERE ([1_tbl_Memberships].GroupMembershipID = GroupMembershipID)"
'Something not right here

DoCmd.SetWarnings True
MsgBox sqlcmd
'This looks OK

DoCmd.RunSQL sqlcmd

Next VarItem
DoCmd.SetWarnings False
Me.List12.Requery

End Sub

I have put in a couple of message boxes to help me troubleshoot. The error I get is that the 'where' clause does not seem to be working. When I run this code, I am warned that I am about to update xxx number of records (ie all of them), as if the where criteria is being ignored.

Could someone point me in the right direction? - Where am I going wrong!

Thanks in advance,

Richard.


texts: How to determine if users have the default password in Oracle 12c?

texts:
I know that I can query the "dba_users_with_defpwd" table, but I've seen reports that for some accounts (mainly SYS and SYSTEM) even if you provide a new non-trivial password during install, they will be shown as having the default password.

Is there any way to verify the users with default passowrds other than manually comparing the "sys.users$" and "sys.defalut_pwd$" tables?

NOTE: I don't have direct access to the environment, I just receive the user tables to check for compliance with the company's rules.


texts: Why can't the transaction log shrink during a backup?

texts:
I have a large database in Simple recovery model (vendor requirement) and it took 3 days to back it up to the new hosting company's server.

The morning of the third day, the DB went offline because the transaction log had filled up its drive.

My understanding was that SQL Server would just start recycling the space. What's more interesting is that I don't think the database started working again until I gave it a bit more space - it stayed busted after the backup was complete. I didn't take the time to properly debug, though, so I don't know exactly why it was stuck.

Question: Why can't SQL Server in Simple Recovery re-cycle its transaction log while it is backing up?


texts: Why does InnoDB corrupt tables upon recovery?

texts:
When setting

innodb_fast_shutdown=0

MySQL drops all incomplete transactions before shutting down/crash. The default value is 1, which is understandable to preserve incomplete transactions.

However, in the recovery mode (e.g., innodb_force_recovery = 6), MySQL treats all incomplete transactions as committed. This obviously damages the table as the transactions are incomplete.

What is the purpose of preserving incomplete transactions when it damages the whole table?

If it is necessary in some cases, why doesn't innodb_force_recovery have an option to simply drop all incomplete transactions (when they cannot be committed) like innodb_fast_shutdown=0 to save the table? Or am I missing something?


texts: Security and Performance implications of "View Server State"

texts:
This question points out that "View Server State" permission is required for various DMV's (dynamic management views), but I can't find anything about who you do and do not want to grant the permission to.

Now of course I understand "least permissions", and why you wouldn't want to just grant it to anybody, but I can't find any guideance on how to evaluate whether it SHOULD be granted or not.

So, my question: What are the security and performance implications of granting a user "View Server State" permission.  What can they do that they maybe shouldn't be allowed to do...

Update: one implication is that the user will be able to use DMV's to look at queries.  If the queries or query parameters can contain confidential information that the user wouldn't otherwise be able to see, allowing VIEW SERVER STATE would allow them to do so (i.e. dob = or ssn =).


texts: How can I get performance using PostgreSQL CTE recursive?

texts:
I did a tree structure using id and parent_id in the same table. For query's I'm using CTE provide by PostgreSQL, but it's spend so much time to do the joins with recursive results. For example, by the time I have 100 records on sadt_lot table, and this query is spend 8 seconds to return the results. Someone have a better idea to do it?

Example: Listing all sadt's grouped by root sadt_lot's

EXPLAIN ANALYZE WITH RECURSIVE downlots as (
        SELECT sl1.sadt_lot_id, 0 AS level, sl1.sadt_lot_id as root_id
        FROM sadt_lot sl1
        WHERE sl1.parent_lot_id IS NULL
    UNION
        SELECT sl2.sadt_lot_id, d.level + 1, d.sadt_lot_id as root_id
        FROM sadt_lot sl2
        INNER JOIN downlots d ON d.sadt_lot_id = sl2.parent_lot_id
)
SELECT sl.sadt_lot_id, array_agg(s.sadt_id)
FROM sadt_lot sl
LEFT JOIN sadt s ON s.sadt_lot_id = any(SELECT sadt_lot_id FROM downlots WHERE root_id = sl.sadt_lot_id)
WHERE sl.parent_lot_id IS NULL
group by sl.sadt_lot_id
ORDEr By sl.sadt_lot_id

Query Plan

GroupAggregate  (cost=42.53..15077.74 rows=1 width=36) (actual time=104.090..8436.505 rows=90 loops=1)
  Group Key: sl.sadt_lot_id
  CTE downlots
    ->  Recursive Union  (cost=0.00..42.39 rows=101 width=12) (actual time=0.006..0.104 rows=95 loops=1)
          ->  Seq Scan on sadt_lot sl1  (cost=0.00..2.94 rows=1 width=12) (actual time=0.005..0.019 rows=90 loops=1)
                Filter: (parent_lot_id IS NULL)
                Rows Removed by Filter: 5
          ->  Hash Join  (cost=0.33..3.74 rows=10 width=12) (actual time=0.027..0.028 rows=2 loops=2)
                Hash Cond: (sl2.parent_lot_id = d.sadt_lot_id)
                ->  Seq Scan on sadt_lot sl2  (cost=0.00..2.94 rows=94 width=8) (actual time=0.002..0.008 rows=95 loops=2)
                ->  Hash  (cost=0.20..0.20 rows=10 width=8) (actual time=0.010..0.010 rows=48 loops=2)
                      Buckets: 1024  Batches: 1  Memory Usage: 9kB
                      ->  WorkTable Scan on downlots d  (cost=0.00..0.20 rows=10 width=8) (actual time=0.001..0.004 rows=48 loops=2)
  ->  Nested Loop Left Join  (cost=0.14..15004.14 rows=6242 width=8) (actual time=8.234..8434.229 rows=11345 loops=1)
        Join Filter: (SubPlan 2)
        Rows Removed by Join Filter: 1112125
        ->  Index Only Scan using sadt_lot_sadt_lot_id_parent_lot_id_idx on sadt_lot sl  (cost=0.14..12.86 rows=1 width=4) (actual time=0.011..0.252 rows=90 loops=1)
              Index Cond: (parent_lot_id IS NULL)
              Heap Fetches: 90
        ->  Seq Scan on sadt s  (cost=0.00..635.83 rows=12483 width=8) (actual time=0.002..1.785 rows=12483 loops=90)
        SubPlan 2
          ->  CTE Scan on downlots  (cost=0.00..2.27 rows=1 width=4) (actual time=0.003..0.007 rows=1 loops=1123470)
                Filter: (root_id = sl.sadt_lot_id)
                Rows Removed by Filter: 94
Planning time: 0.203 ms
Execution time: 8436.598 ms


texts: insert with multiple row return through select

texts:
I want to insert multiple rows return from select statement with different uid,
lets select statement return (1,2,4,5);

INSERT INTO `notifications`( `Text`, `uid`, `read_status`)
VALUES ("abc",(select uid from users where status=1),0)

How can I insert this from one insert query?

Output

 Text,    uid,    read_status
 abc,     1,    0
 abc,     2,    0
 abc,     4,    0
 abc,     5,    0


texts: When and Why should I not call a stored procedure from a trigger?

texts:
This answer
says that "you should never call stored procedures from within triggers", however, it seems to be assuming some circumstances and needs that might not be applicable to every database.  What if I don't need to roll data back?  What if my data set is fairly small?

Specifically, I'm building a database and application, part of which will deal with inventory management, and I've made a stored procedure as follows:

drop procedure if exists quantityupdate;
DELIMITER //
create PROCEDURE quantityupdate()
begin
    DECLARE idcounter INT;
    DECLARE newq FLOAT;
    DECLARE partcount FLOAT;
    DECLARE used FLOAT;
    DECLARE added FLOAT;
    set idcounter = 1;
    set partcount = (SELECT COUNT(*) FROM parts);
    while idcounter < partcount DO
        SET used = (SELECT sum(Quantity) FROM `partusage` WHERE partID = idcounter AND SoftDelete = 0);
        SET added = (SELECT sum(Quantity) FROM `purchases` WHERE partID = idcounter AND SoftDelete = 0);
        SET newq = added - used;
        UPDATE `parts` SET `QuantityOnHand` = if (newq IS NOT NULL, (SELECT newq),QuantityOnHand) WHERE partID = idcounter;
        set idcounter = idcounter + 1;
    end while;
end //
delimiter ;

I'm considering calling this stored procedure whenever a record is INSERTed into the purchases and partusage tables, which will happen only 50 times per day or so.  Is there a reason that I shouldn't call that from a trigger like that?


texts: Performance of a=0 and b=0 and ... z=0 vs a+b+c+d=0

texts:
This is a simple question that I can't seem to find the answer for.
In terms of performance, If I have a WHERE clause such as a=0 and b=0 and ... z=0, Would I gain any performance if I replaced that condition with a+b+...+z=0?
In other words, is there any performance gain by replacing the following
Select *
From MyTable
Where A=0 and B=0 and C=0 and D=0...

With
Select *
From MyTable
Where A+B+C+D=0...

I know it can depend on indexes, but for this purpose, let's just say no indexes exist.  Does the arithmetic operator (+) perform better than an "OR" or "AND" Logical Operator?
I'm under the impression that the addition performs better than multiple conditions with ANDs or ORs.
Test results
On a Table of 4.2 million rows
Returning rows Where A=0 B=0 and C=0 -> 351748 Rows
The addition (A+B+C=0) took 5 seconds while the Logical conditions A=0 and B=0 and C=0 took 11 seconds.
On the other hand
Returning rows Where A<>0 B<>0 or C<>0 -> 3829750 Rows 58 seconds
Returning rows Where F65+ F67+f64<>0 -> 3829750 Rows 57 seconds
For the OR, it seems that there is no significant difference.
I agree with gbn:

If A is -1 and B is 1, A+B=0 but A=0 and B= 0 is false

and with AMtwo:

ABS(A)+ABS(B)+ABS(C)+ABS(D)... Even if you expect only positive values, if the column accepts negative values, you should assume that you might encounter one

The results are very impressive, as I thought, It seems that the addition is much quicker than the logical operators.
A = Float, B = Money and C = Float.  The query used is as shown.  In my case, all are positive numbers.  No indexes.  It is just logical in my mind that addition would be quicker than Logical conditions!


texts: Scheduler Current Tasks - Is 92 too high?

texts:
When I run the following query on my Azure SQL database:

SELECT
    scheduler_id,
    cpu_id,
    current_tasks_count,
    work_queue_count,
    runnable_tasks_count,
    pending_disk_io_count
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE scheduler_id < 255 OPTION (RECOMPILE);

I am currently seeing values of 0, 0, 92, 0, 0, 0.

My performance tier is currently S1 (20 DTUs). I have been under 30% DTU utilization the entire day. Yet, in spite of this, 92 tasks seems like an awful lot of tasks waiting to be executed or executing. Also, 92 for current tasks isn't a one-time reading. I have been seeing this value (between 90 and 95) for the past half-hour. My current DTU percentage is about 15% (with 92 current tasks).


texts: Identical query slower when searching for `T` rather than `F`

texts:
I have a legacy database that uses char(1) with 'T' and 'F' for boolean values. The only values in the field are T and F, of the following queries, the first is instantaneous, the second takes 30 seconds.

select top 50 * from vw_Movement
where MOVTypeId = 20 and MOVRequiresResolution = 'T' and MOVIsResolved = 'F'

select top 50 * from vw_Movement
where MOVTypeId = 20 and MOVRequiresResolution = 'T' and MOVIsResolved = 'T'

The execution plans are very similar but the slower one uses a different index.

I fix the issue by changing the sql to:

select top 50 * from vw_Movement
where MOVTypeId = 20 and MOVRequiresResolution = 'T' and MOVIsResolved <> 'F'

This gets the db to use the same index as the first query and executes instantly, but I'd prefer to understand what's actually happening. I'm pretty green at reading execution plans, but some pointers as to what's might be going on would be appreciated.

From reading around, it seems that the expected number of records meeting the criteria would impact performance, the table in question has many more F than T:

movisresolved
------------- -----------
T             70054
F             2734444

What's happening here?

EDIT:

The index that's fast for T:

CREATE NONCLUSTERED INDEX [ix_comms_composite3] ON [dbo].[tb_MOVMovement]
(
    [MOVTypeId] ASC,
    [MOVIsResolved] ASC,
    [MOVRequiresResolution] ASC,
    [MOVSupplierId] ASC,
    [MOVArrangementId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
       SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
       ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
      ) ON [PRIMARY]

After a bit of prompting from the comments, this only occurs when run against the view, not on the table directly ie:

select top 50 * from tb_MOVMovement
where MOVTypeId = 20 and MOVRequiresResolution = 'T' and MOVIsResolved = 'F'

select top 50 * from tb_MOVMovement
where MOVTypeId = 20 and MOVRequiresResolution = 'T' and MOVIsResolved = 'T'

Are both equally quick, view definition here: http://pastebin.com/raw.php?i=FER0bqwh

Table script: http://pastebin.com/nSpSah7b
Poor Index: http://pastebin.com/V7qEpqsY
Execution Plans: https://drive.google.com/file/d/0B5O3tLIEkg56bWhXcU5KcldPa2s/edit?pli=1


texts: SQL Server fails with stack overflow on simple query

texts:
"The query processor ran out of stack space during query optimization."

errorCode=8621 sqlState=S0001
query :
DELETE FROM SOMETABLE WHERE somecolumn is null

it fails with error above, i googled for solution, but only find many people having such problem with table with large amount of foreign keys, this table having only 180 external and 5 internal. Another point about this table - it has two self-references. I mean two foreign keys that point on table itself, can it be cause of problem?
I having this problem on customer PC with:

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)

But on my PC the same query on same table works fine, my version :

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64)


texts:
I need to add a constraint with two columns that says if any given value is present in one of the columns, then:
   1) It cannot be duplicated in the same column.
   2) It cannot be duplicated in the other column either.

The constraint we are looking to make is with PrimaryEmail & SecondaryEmail.

This would be invalid:

UserId    PrimaryEmail       SecondaryEmail
231       joe@yahoo.com      Null
424       smo@gmail.com      joey@yahoo.com

because "joe@yahoo.com" is present in the first column and therefore it cannot be present in the second column regardless of what row it's in.

Is it possible to define this type of constraint in SQL Server 2008?

We started by defining a table just for emails, but we've since reverted from that model in favor of two hard columns for many reasons including: query speed, query complexity, and the probability of a user using multiple email accounts actively decreases in order of magnitude after one.

This defines a traditional two column constraint but its on a per row basis between the two columns and doesn't give us what we are after:

CREATE UNIQUE NONCLUSTERED INDEX idx_UniqueEmail_notnull
ON UserProfile (PrimaryEmail, SecondaryEmail)
WHERE PrimaryEmail IS NOT NULL and SecondaryEmail IS NOT NULL;


texts: store sql handle from xml to table for later use

texts:
SQL Server 2014

I have an application that provides the xml for blocking reports.  The xml contains a sqlhandle that I'm wanting to store in a separate database for later consumption.

The sql handle is stored in the table using the following code

INSERT INTO BlockingSqlHandle(BlockID, SQLServerID, Line, StmStart, StmEnd, sqlhandle)
SELECT f.BlockID,
       f.SQLServerID,
       f.frame.value('data(/frame/@line)[1]','INT') line,
       f.frame.value('data(/frame/@stmtstart)[1]','INT') stmtstart,
       f.frame.value('data(/frame/@stmtend)[1]','INT') stmtend,
       f.frame.value('data(/frame/@sqlhandle)[1]','VARCHAR(1000)') sqlhandle
  FROM f;

However when I try to pull the sqlhandle out for use in sys.dm_exec_sql_text() I get an error "The handle that was passed to dm_exec_sql_text was invalid."
Or no value is returned.

If I pull the handle from the xml and put it into the query manually it returns just fine.

What is the proper way to store a varbinary value that comes from a file so that I can pull the value out and use it with dm_exec_sql_text?


texts: Trying to install Audit_log Plugin on MySQL

texts:
I'm trying to install the Percona Audit_log plugin for my instance of MySQL.

I ran it previously so I know it works ok with MySQL (I'm sure it says so in the documentation anyway).

I have MySQL 5.5.47, so I downloaded the files for Percona server 5.5.47-37.7 and extracted the audit_log.so file.

I put that file into my plugin directory on the server (debian), but when I try to install it I get:

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

Error Code: 1126.
Cant open shared library '/usr/lib/mysql/plugin/audit_log.so'
(errno: 13 /usr/lib/mysql/plugin/audit_log.so: undefined symbol: my_pthread_fastmutex_init)

I checked the permissions are the same as all the other files in that directory. Does anyone know if I need a different version for MySQL 5.5.47, I assumed the Percona versions matched the MySQL versions, or any ideas on it why it wont install?


texts:
I have a table of raw data as timestamp with timezone, as well as other extraneous columns. I want to convert each timestamp to the interval difference between it and the earliest (smallest) timestamp value in the data set. Then I will call extract(epoch) on that interval to get its length in seconds in double precision. I have set extra_float_digits = 1 so I have 6 decimal place precision in the seconds (so microsecond precision).

I then want to multiply the return from extract(epoch) by 1 million to get the number of microseconds in double precision, which I want to then convert to bigint and save as a column in the table time_pyramid. Along with each record I want to store a 0 and a 1 with it to indicate a layer number and a count number, so time_pyramid will have 3 columns. My query looks like this:

INSERT INTO
  TIME_PYRAMID
SELECT
  0,
  CAST (time_converted AS bigint) AS "time",
  1
FROM (
  SELECT
    EXTRACT(EPOCH FROM ("time" - MIN("time"))) * 1000000 AS time_converted
  FROM
    TIME_RAW
  GROUP BY
    time
  ) x;

However, I get all 0's for my time value, so clearly something is not working. Do I need to reformulate the sub-query, or use another sub-query within the first sub-query? Am I trying to do too much work in those command statements? I haven't been able to get insight from other similar-looking questions, because they incorporate joins, whereas I am just processing and inserting from one single table to another. I don't have any WHERE clause or GROUP BY, and I could group by time, but it shouldn't eliminate any duplicates and would just be for the purpose of using "time" in an aggregate function. On the other hand,

select max("time") - min("time") from time_raw;

gives me an answer of "20:23:33.159866", which I can use to get an integer. So I know that the aggregate functions apply to this data (as it clearly has maximum and minimum timestamp values), I just don't know how to properly formulate the query to get the data I want.


texts: How to check if several tables exist in database, and how to show it in Mysql

texts:
I would like to check if several tables exist in a database, and return a easy to understand return, I know you can check if a single table exists by running

show tables like "foo";

but is there something that could check more than one, and could return something that is not a list of tables that exists since there are more than a dozen that needs to be checked.

Perhaps counting the found tables?