texts: Comparing two time fields in database with 2 given time fields

texts:
I am trying to compare 2 given time fields with two time fields, StartDateTime and EndDateTime respectively.

From my Resources, I have 5 items of different resource types and I have to do a check whether my StartDateTime and EndDateTime does not fall between the values in my database – If so, the Resource Item is available to be used.

From the sample data above, I wanted to do a query to select ALL resources of the selected resource type, and not show the resource items that are being used:

SELECT a.ResourceID
FROM Resources AS a
LEFT OUTER JOIN Bookings AS b
ON a.ResourceID = b.ResourceID
AND b.Status IS NULL
WHERE a.ResourceTypeID = (SELECT c.ResourceTypeID FROM ResourceType AS c INNER JOIN Resources AS d ON c.ResourceTypeID = d.ResourceTypeID WHERE c.ResourceType = 'Coffee Machines' LIMIT 1)
AND NOT a.ResourceID IN (SELECT ResourceID FROM Bookings WHERE DATE(StartDateTime) = '2017-07-15' AND Status IS NULL AND ((TIME('11:00:00') >= TIME(StartDateTime) OR TIME('11:00:00') <= TIME(EndDateTime)) AND (TIME('12:00:00') >= TIME(StartDateTime) OR TIME('12:00:00') <= TIME(EndDateTime))))

The subquery in line 7 is to query for resourceIDs that are being used, and with the AND NOT Clause, the query will only show the ones that are available. But the subquery logic is wrong... I am getting this result:

My given time fields do not fall between the time fields in the database, yet CM-01 and CM-03 is not shown. Can anyone please help me solve this logic?


texts: What's the owner of SqlServerAgent job for?

texts:
What is the possible use of owner of an SQL Server Agent job, I wonder?

Removed, and left it blank and the job still succeeds


texts: INSERT IGNORE/UPDATE IGNORE equivalency in SQL Server

texts:
I've got the following table structure.

CREATE TABLE dbo.UserOrgUnits
(
    UserOrgUnitId int PRIMARY KEY NOT NULL IDENTITY,
    UserId int NOT NULL,
    OrgUnitId int NOT NULL,
    DateUpdated int NOT NULL,
    UpdatedByUserId int NOT NULL,
    IsActive int NOT NULL,
    VersionNumber int NOT NULL,
    CONSTRAINT FK_UserOrgUnits_Users FOREIGN KEY (UserId)
       REFERENCES Users (UserId)
               ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FK_UserOrgUnits_OrgUnits FOREIGN KEY (OrgUnitId)
       REFERENCES OrgUnits (OrgUnitId)
);
CREATE UNIQUE INDEX UQ_UserOrgUnitsCreated
                 ON dbo.UserOrgUnits (UserId, OrgUnitId, DateUpdated);
CREATE UNIQUE INDEX UQ_UserOrgUnits
                 ON dbo.UserOrgUnits (UserId, OrgUnitId);

And below is an example of the rows in the db.

INSERT INTO UserOrgUnits (UserId, OrgUnitId, DateUpdated, UpdatedByUserId, IsActive,
                          VersionNumber)
                  VALUES (1, 2, 1523639062, 6, 1, 1);
INSERT INTO UserOrgUnits (UserId, OrgUnitId, DateUpdated, UpdatedByUserId, IsActive,
                          VersionNumber)
                  VALUES (2, 2, 1523639062, 6, 1, 1);

The problem I'm experiencing is that I have a number of tables similar to the one above that need to be updated, specifically, I'm trying to update the OrgUnits from one to another. (A move from OrgUnit A->OrgUnit B) so I'm using a stored procedure and creating a transaction. At the beginning of the stored procedure I get the values of the OrgUnitId I need to move as values @from, @to.

But if I try to simply perform an

UPDATE UserOrgUnits SET OrgUnitId = @to,
                        DateUpdated = 1523639511,
                        UpdatedByUserId = 6
                  WHERE OrgUnitId = @from;

I obviously get the following error

Cannot insert duplicate key row in object 'dbo.UserOrgUnits' with unique index 'UQ_UserOrgUnits'. The duplicate key value is (1, 2).

In MySQL or Maria I would simply fire an UPDATE IGNORE but in SQL Server 08 I know that's not possible so in such a situation I thought it would be best to simply to merge the two rows.

i.e.

DECLARE @from INT;
DECLARE @to INT;
DECLARE @now INT = (SELECT dbo.UNIX_TIMESTAMP(GETDATE()));
DECLARE @Updater INT = (SELECT  UserId FROM Users WHERE Username = 'root');
DECLARE @OrigOrgUnit NVARCHAR(MAX) = N'SELECT TOP 1 @from = OrgUnitId FROM OrgUnits
                                        WHERE OrgUnitKey = ' + <incoming orig key>;
DECLARE @NewOrgUnit NVARCHAR(MAX) = N'SELECT TOP 1 @to = OrgUnitId FROM OrgUnits
                                        WHERE OrgUnitKey = ' + <incoming new key>;
EXEC sp_executesql @OrigOrgUnit, N'@from INT OUT', @from OUT;
EXEC sp_executesql @NewOrgUnit, N'@to INT OUT', @to OUT;

MERGE UserOrgUnits AS t
USING (VALUES (@from, @to, @now, @Updater)) AS s([from], [to], [now], [updater])
ON t.OrgUnitId = s.[to] AND t.UserId = s.UserId
WHEN MATCHED THEN
    DELETE
WHEN NOT MATCHED THEN
    UPDATE SET OrgUnitId = s.[to], Updater = s.[updater], DateUpdated = s.[now];

But now I'm getting an error stating An action of type 'UPDATE' is not allowed in the 'WHEN NOT MATCHED' clause of a MERGE statement.

Clearly I'm not using the MERGE statement properly. I was wondering if there was another statement(s) something that would be more appropriate to use? If so what would be the best way to go about doing so?


texts:
ALTER TABLE SWITCH statement failed because column does not have the same ANSI trimming semantics in tables...

Does anyone knows how to fix that on already existing table full of data?

Well, I've found that it is simply possible to recreate same table with same ANSI_PADDING parameter, but the problem is that the table I'm trying to switch into a larger main table is already filled with data. I don't want to spend again 37 hours to reload that partition =(


texts: PostgreSQL how to return data from anonymous code block?

texts:
I have a table mytable (id, "someLongURI", status, "userId") and a working query:

UPDATE          mytable
SET             status = 'IN_WORK'
WHERE           "someLongURI" IN (
    SELECT      "someLongURI"
    FROM        mytable
    WHERE       status = 'UNUSED'
    AND         pg_try_advisory_xact_lock(id)
    ORDER BY    id ASC
    LIMIT       1
    FOR UPDATE
)
RETURNING       id, "someLongURI";

But now I need to check the "userId" parameter and based on this either select existing row or update (and receive updated row). Something like that (in MySQL this would work):

IF NOT EXISTS (
    SELECT          1
    FROM            mytable tbl
    WHERE           tbl."userId" = 123
)
THEN
    UPDATE          mytable tbl
    SET             tbl.status = 'IN_WORK',
                    tbl."userId" = 123
    WHERE           tbl."someLongURI" IN (
        SELECT      "someLongURI"
        FROM        tbl.mytable
        WHERE       tbl.status = 'UNUSED'
            AND     pg_try_advisory_xact_lock(id)
        ORDER BY    id ASC
        LIMIT       1
        FOR UPDATE
    )
    RETURNING   tbl.id, tbl."someLongURI";
ELSE
    SELECT          tbl.id, tbl."someLongURI"
    FROM            mytable tbl
    WHERE           tbl."userId" = 123;
END IF;

I've wrapped it with DO $$ BEGIN {...} END $$; but it didn't work for     me. Last thing I came to:

DO
    RETURNS TABLE (id INT4, "someLongURI" TEXT) AS
$$
DECLARE
    "_userId" INT4;
BEGIN
    "_userId" = 123;

    IF NOT EXISTS (
        SELECT          1
        FROM            mytable tbl
        WHERE           tbl."userId" = "_userId"
    )
    THEN
        UPDATE          mytable tbl
        SET             tbl.status = 'IN_WORK'
                        tbl."userId" = "_userId"
        WHERE           tbl."someLongURI" IN (
            SELECT      "someLongURI"
            FROM        tbl.mytable
            WHERE       tbl.status = 'UNUSED'
                AND     pg_try_advisory_xact_lock(id)
            ORDER BY    id ASC
            LIMIT       1
            FOR UPDATE
        )
        RETURNING       tbl.id, tbl."someLongURI";
    ELSE
        SELECT          tbl.id, tbl."someLongURI"
        FROM            mytable tbl
        WHERE           tbl."userId" = "_userId";
    END IF;
END $$;

Can't understand where I need to add "RETURNS" or some other statements inside this anonymous code block to receive the results of query


texts:
Application server for CRM has SQL native client 2008 version installed. I have installed SQL native client 2012 side by side as need to connect to an sql availability group listener. How do I get the application server to use the new client version? Oledb connection
Thanks


texts:
I am wondering why I am getting error when running below simple IF in MySQL?

IF NOT EXISTS (SELECT employee_attendance_id FROM employee_attendance WHERE employee_number = 10 AND attendance_date_time = '2015-10-11 13:14:16') THEN
BEGIN
    INSERT IGNORE INTO employee_attendance (employee_number, attendance_date_time)  VALUES (10, '2015-10-11 13:14:16');
END;
END IF;

This is the error I am getting:

  [SQL] IF NOT EXISTS (SELECT employee_attendance_id FROM
  employee_attendance WHERE employee_number = 10 AND
  attendance_date_time = '2015-10-11 13:14:16') THEN BEGIN
      INSERT IGNORE INTO employee_attendance (employee_number, attendance_date_time)  VALUES (10, '2015-10-11 13:14:16'); [Err] 1064
  - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
  near 'IF NOT EXISTS (SELECT employee_attendance_id FROM
  employee_attendance WHERE empl' at line 1

Thanks,
Jassim


texts: The best way to rename the tables after development completed

texts:
What is the easiest and most reliable approach to rename the database tables and columns  in SQL Server 2008 r2?
We have finished development and for some reasons, we are required to rename some tables and afew columns.

Is using synonyms the good way of doing that? What are the pitfalls we should be prepared for? Does it help in renaming the columns gracefully as well?

We have a lot of scripts related to these tables and also they are referenced in the application by .net developers.


texts:
I apologize for the long question. I am trying to learn a lot on the fly as I have become the defacto SQL Admin. I have been tasked with moving a production database to a new server. My goal is to take a full backup and restore the database on new server. My question is focused around the concept of shrinking a database.

I have a production database almost 3 terabytes in size. There are 5 tables no longer needed. Combined the 5 tables account for almost 700GB. My thought process was to remove the 5 tables to save myself a considerable amount of space (700gb) before taking a full backup. I am learning real quick it is not as easy as it sounds. I removed the 5 tables using the drop command but then learned deleting tables will not actually free up any space. In order to free up space I must shrink the database. This is where I run into conflicting opinions. I am reading you do not want to shrink a database unless you have to.

Can someone please point me in the right direction. Now that I have deleted the tables should I shrink the database before I perform the full backup? Will shrinking the database have a negative effect?


texts: Need help about normalizing database to 3NF

texts:
I am currently working on a city travel guide application which let users explore places and plan trip.

In the account module, the following functions are to be considered:

User can register by providing primary email and password.
Registered users can add their personal details like they can add full name, secondary email, country and city.
User can setup their profile like they can add profile picture, Display name, places they have visited etc.
When they open own/ other's profile, their profile info (like photo and Display name) and statistics like their 'trip' count, 'likes' count, 'reviews' count are to be shown.

I have designed database tables for account module as follows:

 1. User_Account_details(user_id, primary_email, secondary_email, password)
 2. User_Personal_details(user_id, first_name, last_name, country, city)
 3. User_Profile(user_id, profile_picture, display_name)
 4. User_Statistics(user_id, trips_count, likes_count, reviews_count)

There are 3 more tables for cities and countries so that when user add country , then the app retrieve it from database and according cities are displayed in suggestions for city textbox.

 1. country(country_id, country_name)
 2. city(city_id, city_name)
 3. country_city_association(country_id, city_id)

Can anyone tell me the database tables are properly normalized to 3NF or not? And if yes, one more confusion: Should I normalize tables upto 3NF or BCNF? sorry, I am a newbie in database-design. plz help.


texts: SSAS Cube - Create measure based on the a dimension attribute

texts:
We have an analysis service cube contains:

1 Measure = All_Calls_Count

2 dimensions = Month  &  Call Status

The All_Calls_Count reflects the count of rows on the fact table as shown :

Is it possible to create another measure that count all rows with only Call_Status = Rejected ? How ?

The below picture shows the desired output when browsing the cube :


texts: Query SQL Server Alert System Mail Session Settings

texts:
I'm looking for a way to query the Mail session settings under Agent > Alert System > Mail Session. Similar to how sp_MSgetalertinfo returns information about the Fail-safe operator, I'd like to determine if the Enable mail profile is checked, and what the Mail system and Mail profile are set to. I understand these may only be contained in registry settings.

The reason why I want to query for these is because we have them enabled... but twice now they have been disabled by someone, at some point in time, and I've only caught it because I got the dreaded "[476] Database Mail is not enabled for agent notifications. Cannot send e-mail to" error in the agent log. This is a third part DB which support has full access to. I'm not sure if they accidentally (or purposely) disabled it, or if it could be disabled by some other system means that isn't done by a user.

In order to not make this an XY question, I'm open to suggestions on how to audit these changes with a server audit specification.


texts: Create user on Azure elastic database and deny view to other databases i same setting

texts:
I have just shifted to Azure and I'm trying to set up some databases.

Previously I have been able to create users that can only see their own database with the following script. But on Azure this is giving me quite the headache.

declare @login_name sysname
declare @db_name sysname
declare @password sysname
declare @sql nvarchar(max)

SET @login_name = 'Username'
SET @password ='Password'
SET @db_name = 'Database'

USE MASTER

set @sql = 'CREATE LOGIN ' + QUOTENAME(@login_name)  + 'WITH PASSWORD = ''' + REPLACE(@password, '''', '''''')
execute( @sql )

set @sql = 'DENY VIEW ANY DATABASE TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Set the user to use the database created
set @sql = 'ALTER LOGIN ' + QUOTENAME(@login_name)  +
    ' WITH DEFAULT_DATABASE = ' + QUOTENAME(@db_name)
execute( @sql )

set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
    'EXEC sp_changedbowner ' + QUOTENAME(@login_name)
execute( @sql )

Everything I find online tells me just to

Create the login first
Then connect to the database I want to assign
the login to
Then create a user and assign roles to this user

One thing is that this is quite a lot of connecting and disconnecting when I'm creating 10-20 databases at a time assigning users to everyone of them. Another thing is that I want make sure that when the users login to their database, they can only see their own database and not all other databases on that server.

What can I do, is there no way to create login & user, assign roles and limit view at the same time?


texts: The difference between filtering a specific column in 'On Clause ' of a join statement and 'Where clause'

texts:
I have two tables with these structures:

Table1(Date ,Code ,Name)
Table2(Date ,Code)

I need to filter a specific date for both tables. For instance I need data only for '10/28/2019' from each table.I want to know whether there are differences between these two queries in terms of the final result and query performance. In the First query I'm filtering dates in ON clause and in the second query I'm filtering dates in the where clause.
Will there be any condition that these two queries produce different result set?
Thanks in advance.

query1 :

      Select "Desired Columns"
      From Table1 T1 Inner Join Table2 T2
      On T1.Date = "The specific date" and
         T2.Date = "The specific date" and
         T1.Code = T2.Code

query2:

      Select "Desired Columns"
      From Table1 T1 Inner Join Table2 T2
      On T1.Code = T2.Code
      where T1.date ="The specific date" and T2.Date = "The specific date"


texts: SQL Server High Availability restore copy_only backups from secondary replica problem

texts:
I've implemented the Ola Hallengren backup solution across our 12-instance farm with AlwaysOn High Availability. Typical setup of weekly full backups, daily differential backups and 4-hourly transaction log backups.

To leverage performance on the Primary, I've offloaded these backups to the secondary.  This makes the Full backups copy_only on the secondary. In theory the Diff backups shouldn't occur, but the job just runs on the primary with the Diff backups also marked as copy_only.  Log backups run on the Secondary.

Thought I'd better test the restore process.  Restored a copy_only Full backup WITH RESTORE and MOVE to a test database and that was fine.  Deleted the test database and restored the copy_only Full backup WITH NORESTORE and MOVE to a test database followed by a restore of the next copy_only Differential backup. This failed with

"the differential backup cannot be restored because the database has not been restored to correct earlier state."

Looking at sys.database_files it shows differential_base_time of 2017-02-15 and a differential_base_LSN of 467000002068600193.  I do not have this file!

Using RESTORE HEADERONLY my copy_only Full backup has a

  FirstLSN = 743000000530200000,

  LastLSN = 743000000569700000,

  CheckpointLSN = 743000000530200000,   DatabaseBackupLSN =
  467000002068600000

My copy_only Differential backup has

  FirstLSN = 788000000036000000

  LastLSN = 792000000047100000,

  CheckpointLSN = 792000000040600000,   DatabaseBackupLSN =
  467000002068600000

  DifferentialBaseLSN = 467000002068600000

Irrespective of the potential disaster of not having a Full backup covering LSN 467000002068600193 (or 467000002068600000...), I expected to see some connecting chain between LastLSN of my copy_only Full backup and the FirstLSN of my copy_only Differential backup.  This doesn't seem to be there.  Can someone explain the gap in my understanding please?

And can someone recommend my least painful way of getting my backup strategy back in line?

If offloading backups to a secondary replica makes all Full backups copy_only with no ability to capture the DifferentialBaseLSN and does not allow Differential backups at all (seemingly), can someone also explain any significant benefit of such offloading?


texts:
My Postgresql server is using 100% cpu. I am looking to figure out whether individual queries are slow or I am simply hammering the server with too many requests to begin with.

Is there some sort of profiler I could attach that would let me know:

What queries trigger the most of the CPU time, across multiple connections?
For the above queries, what is the average runtime per query?


texts: what happens If I remove mysql-bin.index file?

texts:
Because of the file storage full, we are removing mysql bin log files.
Accidentally, we removed mysql-bin.index file.
What happens if I remove mysql-bin.index file?
These below are my DBMS info. Thank you.
MariaDB 10.4
Single Instance
engine: InnoDB Engine


texts: In SQL Server, is there way to have a field where an auto-number is appended to the end of whatever value the field is set to?

texts:
Sorry if that's not overly clear.

In SQL Server, can I have a field which I can place a string into, and then SQL Server appends an auto-number onto the end?

So for example, say I have column A.

If I set row 1 in column A to "Foo", the final value will be "Foo1"
Then if I set row 2 in column A to "Bar", the final value is "Bar2"

Is this possible?


texts: Is Reorganizing Indexes required after ShrinkDatabase or can I just reogranize index with fragmentation more than 10%?

texts: I am a new DBA to a SQL 2005 production Report server. My predecessor created a Maintenance Plan with 4 tasks: CHECKDB; SHRINKDATABASE (N'DB1',10,TRUNCATEONLY); it seems to be running a REORGANIZE on all Indexes for all Tables and Views and "Compact Large Objects" is checked; and then it UPDATE STATISTICS for all tables. There is also a separate job that runs after the Maintenance Plan that if the fragmentation of the same DB1 Indexes is more than 10% they are reorganized. It seems redundant. If they were just reorganized why would there need to be a second job? Also wouldn’t the second job be more efficient since it doesn't use up resources for Indexes less than 10%?


texts: How to mention declared integer in where condition using dynamic sql?

texts:
I tried with 1,2 and 3 quotes and with/without conversion, but I am getting

Error: Conversion failed when converting the varchar value

Can someone please help me?
with conversion
            left join xxx.xxx.PITM'+@Datemm_yyyy+' pitm
                on nep.s_pitm_c=pitm.pitm_c
                and  cast(year(s_tran_d) as nchar)='''+@yyyy+'''
                and cast(month(s_tran_d) as nchar) ='''+@mm+'''

            leftjoin xxx.xxx.PITM'+@Datemm_yyyy1+' pitm1
                on nep.s_pitm_c=pitm1.pitm_c
                and cast(year(dateadd(m,-1,s_tran_d)) as nchar) =''+ @yyyy1 +''
                and cast(month(s_tran_d)-1 as nchar)= '''+@mm1 +'''

without conversion
        inner join xxx.xx.PITM'+@Datemm_yyyy+' pitm on nep.s_pitm_c=pitm.pitm_c
        and  year(s_tran_d) =''+@yyyy+'' and month(s_tran_d)  =''+@mm+''

        inner join xxx.xx.PITM'+@Datemm_yyyy1+' pitm1 on nep.s_pitm_c=pitm1.pitm_c
        and year(dateadd(m,-1,s_tran_d))  =''+ @yyyy1 +'' and month(s_tran_d)-1= ''+@mm1 +''

        I also tried by keeping 1 or 2 or 3 single quotes.

When I put 2 quotes I am getting

Error: Conversion failed when converting the varchar value '+@yyyy+' to data type int.

Full code:
Declare @sql nvarchar(max)
Declare @StartDate Date;
Declare @EndDate Date;
Declare @Datemm_yyyy Char(7);
Declare @StartDate1 Date;
Declare @EndDate1 Date;
Declare @Datemm_yyyy1 Char(7);
declare @yyyy int;
declare @yyyy1 int;
declare @mm int;
declare @mm1 int;

Set @StartDate='20050501'
Set @EndDate=EOMonth(@StartDate);
Set @Datemm_yyyy=Replace(right(Convert(Varchar(10),@StartDate,105),7),'-','_')
Set @StartDate1='20050401'
Set @EndDate1=EOMonth(@StartDate1);
Set @Datemm_yyyy1=Replace(right(Convert(Varchar(10),@StartDate1,105),7),'-','_')
set @yyyy=year(@StartDate)
set @mm=month(@StartDate)
set @yyyy1=year(@StartDate1)
set @mm1=month(@StartDate1)

print @StartDate
print @EndDate
print @Datemm_yyyy
print @StartDate
print @EndDate
print @Datemm_yyyy
print @yyyy
print @yyyy
print @mm
print @mm1

While @StartDate<'20181101'
Begin
    Set @sql='

insert into ##loan_account_details_focis

select
nep.s_pitm_c,
nep.s_tran_d,
pitm.LOAN_CNSN_STRT_D loancategory_start_date,
pitm1.LOAN_CNSN_STRT_D lastmonth_loan_catgory_start,
pitm.TFPR_D transfer_pricing_date

from
xxx.DBO.RK_NEP_PROBABLE_POOL nep
left join xxx.xx.PITM'+@Datemm_yyyy+' pitm on nep.s_pitm_c=pitm.pitm_c
 and  cast(year(s_tran_d) as nchar)=@yyyy
       and cast(month(s_tran_d) as nchar) =@mm

leftjoin xxx.xx.PITM'+@Datemm_yyyy1+' pitm1 on nep.s_pitm_c=pitm1.pitm_c
and cast(year(dateadd(m,-1,s_tran_d)) as nchar)  =@yyyy1 and cast(month(s_tran_d)-1 as nchar)= @mm1
'
    exec (@sql)
    print @sql
    Set @StartDate=DateAdd(Month,1,@StartDate)
    Set @EndDate=EOMonth(@StartDate);
    Set @Datemm_yyyy=Replace(right(Convert(Varchar(10),@StartDate,105),7),'-','_')

    Set @StartDate1=DateAdd(Month,1,@StartDate1)
    Set @EndDate1=EOMonth(@StartDate1);
    Set @Datemm_yyyy1=Replace(right(Convert(Varchar(10),@StartDate1,105),7),'-','_')
    set @yyyy=year(@StartDate)
    set @yyyy1=year(@StartDate1)
    set @mm=month(@StartDate)
    set @mm1=month(@StartDate1)
    --print @startDate
End
;


texts: Determining if a relation is in third normal form 3NF

texts:
I have a ralation R(A,B,C) with the functional dependencies as follows

A->B
A->C
B->C

with A being the candidate key, is this relation in 3NF? well because by analyzing the first two dependencies it seems that both non key attributes  B and C are not transitively dependent on A but does the third functional dependency B->C not make C transitively dependent on A?


texts:
I have an application that accesses my SQL Server using a username\password. However our lead developer has access to this password and I suspect that he may be utilizing this outside of our terms of agreement. My wish is to audit for anytime this username (appaccount) accesses our database and any commands that are issued.

Luckily this application uses purely stored procedures with passed in parameters when it accesses the database so anytime the account runs a T-SQL statement it has to be from our developer in question.

My ideal output would be something like this:

Datetime | Username | Action Performed

11:23am | appaccount | "Select * from claimstable"|

11:26am | appaccount | "update table ...(skip change control process)"|


texts: Convert a lookup value to foreign key when a query is run

texts:
Currently we have a legacy system running queries on a Postgres server to update/insert records. One of the obstacles we are having is that we have to convert text values to the correct foreign key for a lookup table. Please see below for a better explanation:

Query:

INSERT INTO public.people(name, state)
VALUES ('John', 'CA');

Tables:

public.people

CREATE TABLE people
(
  name text,
  state smallint,
  CONSTRAINT state_key FOREIGN KEY (state)
      REFERENCES states (states_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

CREATE INDEX fki_state_key
  ON people
  USING btree
  (state);

public.states

CREATE TABLE states
(
  states_id smallint,
  name text,
  CONSTRAINT state_unique UNIQUE (states_id)
)
WITH (
  OIDS=FALSE
);

What is the best way of converting 'CA' to the associated ID in the public.states table? The particular interface we are using to talk to Postgres is called Postgrest (https://github.com/begriffs/postgrest) and would require additional SELECT statements instead of a subqueries. We want minimize the amount of calls as much as possible.

Also, on the legacy system that is using that interface it does not use a lookup table, which makes it difficult to determine which fields need to be converted for a lookup table.

Is there a native way in Postgres to handle these types of conversions? If you need additional information please let me know.

"PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit"


texts: SQL Server automatic transaction log truncation

texts:
Have looked through msdn but cant find answers to these:

Any help much appreciated:

In simple recovery mode:

Is there any TSQL or alternative functionality that will provide a notification either written to the event logs or a message or an alert , stating that the Microsoft automatic transaction log truncation is happening i.e when a checkpoint is reached sql server truncates.
(According to this: http://technet.microsoft.com/en-us/library/ms189085(v=sql.105).aspx)
Is there anyway to find out how long it takes for automatic truncation to happen /how long it takes for the transaction log to be truncated?
Does the SQL Server condition performance alerts happen in real time or in intervals or say 5 mins?

Thanks all!


texts: Generate permutations and combination for name column

texts:
I have the following sample data for permutations and combination.

create table tbltest
(
    name varchar(50),
    addres varchar(100)
);

insert into tbltest values('Sam Mak John','Street 1 HNo 101 USA');
insert into tbltest values('Donatella Nobatti','HNo 101 UK');
insert into tbltest values('Sam Buca','Main Road B Block UAE');
insert into tbltest values('Juan Soponatime','Hight Street CA');
insert into tbltest values('Aaron Spacemuseum','HNo A10 100 feet Road A Block ');

I want to generate permutations and combinations for the name column in the table tbltest and store into temp table.

For an example expected result:

name
----------------
John Mak Sam
John Sam Mak
Mak John Sam
Mak Sam John
Sam John Mak
Sam Mak John
....
....

Tried with following query: Source

Note: Following query works as expected for single record but when I tried on table using cursor its running more than 35 min and still keeps running.

DECLARE @inputStr VARCHAR(MAX)= 'Sam Mak John';
DECLARE @ValueStr VARCHAR(100);
DECLARE @Count INT, @Loop INT= 1, @totalSum INT;
DECLARE @Query1 VARCHAR(1000), @Query2 VARCHAR(1000), @Query3 VARCHAR(1000), @Query4 VARCHAR(1000), @Query5 VARCHAR(1000), @Query6 VARCHAR(1000), @Query VARCHAR(4000), @Combination VARCHAR(1000);

--Temporary table to capture all the words separately
CREATE TABLE #tmpvalues
(intIndex INT IDENTITY(1, 1),
 intProc  INT,
 subStr   VARCHAR(100)
);

--Temporary table to store all the possible combinations
CREATE TABLE #tmpCombinations
(subCombStr VARCHAR(1000)
);

--get the sub-strings(words) from input statement into a temp table
WHILE LEN(@inputStr) > 0
    BEGIN
        SET @ValueStr = LEFT(@inputStr, ISNULL(NULLIF(CHARINDEX(' ', @inputStr) - 1, -1), LEN(@inputStr)));
        SET @inputStr = SUBSTRING(@inputStr, ISNULL(NULLIF(CHARINDEX(' ', @inputStr), 0), LEN(@inputStr)) + 1, LEN(@inputStr));
        INSERT INTO #tmpvalues
        VALUES
        (@Loop,
         @ValueStr
        );
        SET @Loop = @Loop + 1;
    END;
SELECT @Count = MAX(intINDEX)
FROM #tmpvalues;
SET @Loop = 1;

--Set an integer values for each words
--This will be used to filter the combinations in which any two words are repating
DECLARE @tempIntAdd INT;--Addition factor
SET @tempIntAdd = @Loop * @Count;
WHILE @Loop <= (@Count - 1)
    BEGIN
        DECLARE @tempIntProc INT;
        SELECT @tempIntProc = intProc
        FROM #tmpvalues
        WHERE intIndex = @Loop;
        UPDATE #tmpvalues
          SET
              intProc = @tempIntProc + @tempIntAdd
        WHERE intIndex = @Loop + 1;
        SET @Loop = @Loop + 1;
        SET @tempIntAdd = @tempIntAdd * 2;
    END;
--
SET @Loop = 1;
SET @Query1 = 'INSERT INTO #tmpCombinations SELECT DISTINCT ';
SET @Query2 = 'ALL_COMBINATIONS FROM';
SET @Query3 = ' ';
SET @Query4 = ' WHERE';
SET @Query5 = '(';
SET @Query6 = ')';

-- Generate the dynamic query to get permutations and combination of individual words
WHILE @Loop <= @Count
    BEGIN
        SELECT @ValueStr = subStr
        FROM #tmpvalues
        WHERE intIndex = @Loop;
        SET @Query1 = @Query1 + 'T' + CAST(@Loop AS VARCHAR) + '.subStr ';
        IF(@Loop < @Count)
            SET @Query1 = @Query1 + '+ '' '' + ';
        SET @Query3 = @Query3 + '#tmpvalues ' + 'T' + CAST(@Loop AS VARCHAR);
        IF(@Loop < @Count)
            SET @Query3 = @Query3 + ', ';
        SET @Query5 = @Query5 + 'T' + CAST(@Loop AS VARCHAR) + '.intProc';
        IF(@Loop < @Count)
            SET @Query5 = @Query5 + ' + ';
        SET @Loop = @Loop + 1;
    END;
SELECT @totalSum = SUM(intProc)
FROM #tmpvalues;

--Create final query
SET @Query = @Query1 + @Query2 + @Query3 + @Query4 + @Query5 + @Query6 + ' =' + CAST(@totalSum AS VARCHAR);

--Execute the dynamic Query
EXECUTE (@Query);

SELECT subCombStr from  #tmpCombinations


texts: Why do database host services increment id by > 1 unit?

texts:
I have worked with various databases and notice that an id column (primary key) with auto_increment usually increases by a seemingly random, but static, value like 3, 5, or 10.

So instead of the id counting up by one for each row: 1,2,3,4, etc. the id might count up by 5 like 1,6,11,16, etc.

Why is this practice in place?

My guess is that it can serve to obscure information to outside users.  For example my user id  might show in the URL on a website, and if the id on the users table counts up by 1, I'd be able to gauge the number of users that have signed up for a specific service.  However I am curious to know if that is the reason or if other factors are involved.


texts: What are the low-level storage requirements?

texts:
Typically, DB engine fulfils some request and any modifications must stay persistent (you call it durability). How is the persistence is ensured/assumed? Which requirements are imposed on the HW/OS?

Particularly,

When does the server does the flush? Can it happen that OS flush command propagates into some intermediate buffer and does not propagate down to the HDD plates? Today, many storage manufacturers and interfaces support out-of-order write buffers. What are implications?: Should you take any action to counteract it? Does ram buffer on disk matter? Are any UPS (and which) required to support the RAM buffer?

What should I guarantee if I have my own "DB" - storing my data in plain files? I assume that DB does the best. What is that best so that I could do the same to guarantee that data is not lost? Is there a simple procedure? Are there any courses that teach the safe storage?


texts:
We're currently using Postgres 9.4.14 hosting Zabbix 2.4 database. For a while now, the following message started to happens:

< 2017-11-23 15:15:20.037 -03 >HINT:  Close open transactions soon to avoid wraparound problems.
< 2017-11-23 15:15:20.048 -03 >WARNING:  oldest xmin is far in the past
< 2017-11-23 15:15:20.048 -03 >HINT:  Close open transactions soon to avoid wraparound problems.
< 2017-11-23 15:15:20.059 -03 >WARNING:  oldest xmin is far in the past

We restarted the datase and after startup the same message start to be happen:

< 2017-11-22 10:25:04.262 -03 >LOG:  database system was shut down at 2017-11-22 10:00:04 -03
< 2017-11-22 10:25:04.387 -03 >LOG:  MultiXact member wraparound protections are now enabled
< 2017-11-22 10:25:04.389 -03 >LOG:  autovacuum launcher started
< 2017-11-22 10:25:04.389 -03 >LOG:  database system is ready to accept connections
< 2017-11-22 10:25:15.910 -03 >WARNING:  there is no transaction in progress
...
< 2017-11-22 10:25:20.259 -03 >WARNING:  oldest xmin is far in the past
< 2017-11-22 10:25:20.259 -03 >HINT:  Close open transactions soon to avoid wraparound problems.

Vacuum daemon is running:

postgres@bchlnx361:pg_log $ ps -fea | grep vacuum
postgres   409   403  0 Nov22 ?        00:00:02 postgres: autovacuum launcher process

and there are my postgresql.conf settings..

maintenance_work_mem = 2280MB
autovacuum_work_mem = -1
autovacuum = on
log_autovacuum_min_duration = 0
autovacuum_max_workers = 2
autovacuum_naptime = 60
autovacuum_vacuum_threshold = 5000
autovacuum_analyze_threshold = 50000
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.05
autovacuum_freeze_max_age = 1000000000
autovacuum_multixact_freeze_max_age = 400000000
autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = -1

But tables are'nt being vacuumed, when I've checked on pg_stat_user_tables, all rows has last_Autovacuum with null values, and some tables really need to be vacuumed:

zabbix_db=# select tuple_count, dead_tuple_count from pgstattuple('public.triggers');
 tuple_count | dead_tuple_count
-------------+------------------
       73657 |          5161183
(1 row)

Someone could help as?

Regards,


texts: MySQL - Master/Slave Replication with different server specs

texts:
I have a Master/Slave replication (using MySql 5.1 / InnoDB).

Currently both my master & slave are running on the same type of server (32 GB RAM, 2 x Intel Xeon 5520 Quad Core 2.26 GHz (8 cores)) and have the same type of configuration (in terms of innodb_pool_size etc.)

I am considering upgrading my master to a stronger machine (48GB RAM, 2 x Intel Xeon 5640 Six Core 2.26 GHz (12 cores)).

My question is: Is it possible to upgrade just the master (increase his buffer_bool_size etc.) and leave the slave at his current setup?


texts: Understanding the load running on my SQL server

texts:
We have a project this year to move lot of physical SQL Servers, starting with medium critical servers onto Virtual machines.
Generally on physical boxes we have a large count of logical processors, for example 80. Now when moving to VM we get 64 vCores/vCPU.
My understanding is: it's ok to be on low vCores/vCPU in VM till the time their speed is faster. I mean clock speed compared to one on physical.
This bought up an interesting debate with my colleague on how above behaves differently between servers where the majority of load is either single threaded queries vs parallel ones.
Following are a few questions:

How true is above?

Is there a way we can determine even approximately the servers where the queries are running serially vs parallel?  I mean a way to say that my SQL Server has 70% queries which just run single threaded or parallel?


texts:
I already success install oracle-xe on my ubuntu 10-04, tried to configure the port and password sudo /etc/init.d/oracle-xe configure, and then restart my computer and go to http://127.0.0.1:8080/apex and input system as the username and my password admin but it said that Invalid Login Credentials.

The problem here I cannot login, try to re-install the oracle but same problem occur. Anyone can help me with this things? I'm very appreciated your help, thanks...

Update

I found that after I re-install again oracle-xe and set the password same with my system password it worked.

Thank you so much for everyone that has already read my problem. Maybe this problem sound funny, but it really happen to me. Please note that when you try to downvote someone make sure you give comment. I'm really disappointed with this thing happen to me, even I'm newbie I know the rules around here.

Question:
Why I got '-1' here? Please add a comment if I do something wrong, I ask because I don't know the solution and I tried to googling before and cannot find it. Because there is no one that help me here so I solve my own problem. Do you think that I'm here just to find point? No I'm here because I know you can help me to solve my problem. Please read this one before criticize someone


texts:
Doing a staging run setting up a new xtrabackup restore script I'm running into a somewhat cryptic error I can't readily google a solution for.

I'm working with an incremental backup design.  During the initial copy back of the last full the an attempt to apply changes to the first incremental is failing with

xtrabackup: error: failed to read metadata from
/var/tmp/mysql_backups/inc/day1/xtrabackup_checkpoints

The cited file is present and world readable.

It's contents are

backup_type = incremental
from_lsn = 12:4121088784
to_lsn = 12:4121089203
last_lsn = 12:4121089203

Thoughts on what to check from here?

Edit

I think I found my problem.  My testing server install was an ubuntu mysql default; not percona.

https://bugs.launchpad.net/percona-xtrabackup/+bug/655584/comments/3


texts:
Consider the following table:
CREATE TABLE `multiqueue` (
    `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `CustomerID` BIGINT(20) NOT NULL,
    `Volume` INT(11) NOT NULL,
    `Content` MEDIUMTEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `PublishedTS` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`ID`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

This table serves as a multi-queue, meaning that it aggregates the queues of requests coming from multiple customers (denoted by CustomerID), each request having a certain Volume of work.
How to write a query that will select top N rows from the table, interleaving the rows from different customers?
If customer 1 sends 100 requests, each of volume 1000, then customer 2 sends 20 requests, each of volume 300, I would like that the query doesn't force any of the customers to starve on responses while my program is busy handling the requests of another customer. It should take 1 request of customer 1 and 3-4 requests of customer 2 in the first fetch, process them, then take 1 more request of customer 1 and 3-4 requests of customer 2, and so on.
What I have tried so far:
SET @runtot := 0;
SELECT q1.id1, q1.customerId1, q1.volume1, q1.content1, (@runtot := @runtot + q1.volume1) AS rt
FROM (
  SELECT ID AS id1, CustomerID AS customerId1, Content AS content1
  FROM multiqueue
  ORDER BY id1
) AS q1
WHERE @runtot < 2000

As described here, the code above limits the number of items selected with a running total of some field in the rows selected. In the scenario above, customer 2 would starve when the above query is in use.
The database in use is MariaDB (version 10.4.13, but I can upgrade to the latest if needed), though a solution for MySQL should also work.


texts: UPDATE ... FROM ... syntax for iSeries for a MS SQL Server developer

texts:
When I wanted to update a table to the values from another table in SQL Server I would use following syntax:

UPDATE t1
SET t1.a = t2.a
FROM t1
INNER JOIN t2 ON t2.b = t1.b

What would be iSeries 7 syntax for that?


texts:
I had asked a previous question "Proper technique for storing users event data" and the correct answer in my opinion was to create a database partition. Now from what I have read on it there are different ways to partition, but for this question we are going to assume we are doing a horizontal key partition using a date field on a RDBMS such as MySQL... (if you have objection or argument to this, by all means contribute).

The base question is how do you know how many partitions to create?

I understand this is a pretty open question being that it would also heavily rely on what hardware you are running this on, yet either way there should be some guidelines that would point to where the better performance would be, or a proper way of doing so, or even how would you judge something like this? Most all of the documentation I have found use terms such as "large", "big", "a lot"... what do these terms mean when it comes to access speed, or row count, efficiency vs storage or required hardware. Is it something where from trial and error or observed performance where if things start getting a little rough you would just add a partition or two?

I'm very interested in opinions and contradictions to this seemingly common hurdle in large DB schemes.

Thanks


texts: Read committed snapshot:Finding lock on table

texts:
IF update command is run on a table and commit is pending then another user cannot be able to update the table.

Is there any query to get the user or PC which caused lock on the table.


texts: Useful formats for the results of a database "audit"?

texts: My company has tons of disparate databases - Oracle, SQL Server, and (shudder) MS Access - lying around, and the CIO wants to bring in a third party to track them all down and document them. But, rather than having them just give us a big ole ERD, my manager (who is writing the project's requirements) wants to specify that the findings be given to us in some useful format. We were thinking Visio, but that's just a big ERD that's zoom-able. Any suggestions?


texts: Merging two two nested JSONs in Postgresql


texts:
I am creating a script where I have to fix orphan user (app service account) when db gets refreshed from Devl --> Test --> Prod. Since my company sells this software, it is essentially huge number of servers and I alone cannot manage it. So, I decided to create a script and give just that permission to application support people so that they can run that script which should fix orphan user.

This is the script

CREATE PROCEDURE [dbo].[usp_FixPermissionsAfterDBRefresh]
AS
BEGIN

    DECLARE @dbname VARCHAR(50)
    DECLARE @sqlstring NVARCHAR(100)

    DECLARE db_cursor CURSOR FOR
    SELECT name
    FROM sys.databases
    WHERE name NOT in ('master','msdb','model','tempdb')

    OPEN db_cursor
    FETCH NEXT from db_cursor into @dbname

    WHILE @@FETCH_STATUS = 0
    BEGIN

            SET @sqlstring = N'USE ' + @dbname
            EXECUTE sp_executesql @sqlstring
            print @sqlstring
            --EXEC sp_change_users_login 'Auto_Fix', 'SomeUser'
            IF EXISTS (SELECT dp.name  As Orphan_Users
                        FROM sys.database_principals dp
                        left join sys.server_principals sp
                        ON dp.sid=sp.sid
                        WHERE sp.name IS NULL
                        AND dp.type='S' AND
                        dp.name NOT IN ('guest','INFORMATION_SCHEMA','sys'))
            BEGIN
            ALTER USER SomeUser WITH LOGIN = SomeUser
            ALTER ROLE [db_backupoperator] ADD MEMBER [SomeUser]
            ALTER ROLE [db_owner] ADD MEMBER [SomeUser]
            END

        FETCH NEXT FROM db_cursor INTO @dbname
    END

    CLOSE db_cursor
    DEALLOCATE db_cursor
END
GO

If I run this script from any database except the one that got restored, the script exits successfully but won't fix orphan user and grant permissions. However, if I run this script from the newly refreshed DB then the script works just fine and fixes orphan user and grants necessary permissions. I have searched online but couldn't really get this script working. I will be setting this up as an agent job and grant permissions to application support who will be performing db refresh.

Please advise on what I am doing wrong.


texts: Accuracy of mysqldbcompare vs other tools like pt-table-checksum

texts:
I was curious to hear what everyone's experiences are with using mysqldbcompare vs pt-table-checksum( from Percona Toolkit ) to find the differences between two MySQL databases.

I have heard that pt-table-checksum is pretty dead-on accurate in figuring out if two DBs are different, but it looks like mysqldbcompare is able to show the actual row differences better and more clearly in its output.

However, how do we know whether mysqldbcompare is as accurate as pt-table-checksum or is it more/less ?

Thanks
IS


texts: I am very new to PHP & Mysql. I am currently working on database structure of website. I have been a keen follower of this website. I have found that many posts have discouraged use of multiple tables. Now, suppose i have a product that has about 10 pictures associated with it and about 50 reviews. Now, should I have a separate tables for pictures and reviews or should i let all of them stay in the same table ?


texts: SSIS PL/SQL task hangs with message "Multiple-step OLE DB operation generated errors."

texts:
I've configured a Microsoft SQL Server 2008 R2 SSIS package with an Execute SQL Task to run a PL/SQL query on an Oracle 10.2 database.

The Execute SQL Task (named "SQL Update MY_STATUS flag") is using an OLE DB Connection Manager with a "Native OLE DB\Oracle Provider for OLE DB" Provider. All other task settings are default values.

The query is simple:

UPDATE MY_TABLE
SET MY_STATUS = 1
WHERE MY_STATUS = 0

(Note: This query succeeds when run in SQL Developer with a semicolon terminator. Here the terminator is omitted, because if a semicolon is included, an invalid character error is generated by SSIS.)

When I start debugging, the task begins execution and then hangs indefinitely, the package does not complete execution.

The debug output window shows this message:

  Warning: 0x0 at SQL Update MY_STATUS flag: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if
  available. No work was done.

(The Task behaves the same with the query configured as Direct Input, or as a Variable.)

What does this "Multiple-step" warning mean in this context? What must I do to allow the Execute SQL Task to succeed?


texts: How to save Outlook attachment in a drive with a new name and overwrite if it already exists?

texts:
I am using below VBA script to download attachments from email and save it to my windows directory.
I need help to modify this script - 1) to save rename the attachment before saving it 2) Overwrite the file if it already exists. Kindly help.
Public Sub SaveAttachmentsToDisk(MItem As Outlook.MailItem)
Dim oAttachment As Outlook.Attachment
Dim sSaveFolder As String
sSaveFolder = "F:\RBL Attachments\"
For Each oAttachment In MItem.Attachments
oAttachment.SaveAsFile sSaveFolder & oAttachment.DisplayName
Next
End Sub


texts: MySQL Read lock and query performance analyses

texts:
I've a table with the following structure.

CREATE TABLE `wallet_details` (
  `id` varchar(32) NOT NULL,
  `status` varchar(16) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`(3))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

It contains around 30 Million rows.

I need to run the following query on it:

select * from my_table where `status` = "UNASSIGNED" limit 1 for update

Following is the explain output of it:

mysql> explain select * from my_table where `status` = "UNASSIGNED" limit 1 for update;
+----+-------------+----------------+------------+------+---------------+------------+---------+-------+----------+----------+-------------+
| id | select_type | table          | partitions | type | possible_keys | key        | key_len | ref   | rows     | filtered | Extra       |
+----+-------------+----------------+------------+------+---------------+------------+---------+-------+----------+----------+-------------+
|  1 | SIMPLE      | my_table       | NULL       | ref  | idx_status    | idx_status | 11      | const | 12898668 |   100.00 | Using where |
+----+-------------+----------------+------------+------+---------------+------------+---------+-------+----------+----------+-------------+
1 row in set, 1 warning (0.09 sec)

The above query is not scaling very much and I wanted to understand how the above works, at which stage is the query behaving undesirably/heavily, and how can I improve its performance.

Few of my questions are:

Would the query run through all of the 12 million rows before fetching the final row (see query has limit 1 condition)?
On how many rows would the read lock be on?


texts: How to add 100 to all strings starting with a digit in an attribute

texts:
I have a table doc_properties
with columns

id (int)
doc_property_category (int)
prop_id (int)
value (string)

I want to increment all "number-value"-attributes (besides values starting with a number there are also values like "luxury-binding")in column value with 100.

For example in table doc_properties, the column value "0100s" should become "0200s", "1500s" should become "1600s", "1900s" should become "2000s" etc. The alphanumeric digit ends always with 's' and it starts always with 4 digits eg. 0100s, 0200s, 0300s, 1700s, 1800s, 2000s, 2100s.

What I'm looking for is a SQL that:

first check if the value starts with a digit, if so
remove trailing "s" from the string like in "1900s" => "1900"
convert into integer and add 100
convert back into string and add "s" so the result is "2000s".

Is this possible with MySQL only?

My attempt:

CREATE PROCEDURE ROWPERROW()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
SELECT COUNT(*) FROM doc_properties INTO n;
SET i=0;
WHILE i<n DO
  UPDATE doc_properties SET value=value + 100 WHERE concat('',value * 1) = value;
  SET i = i + 1;
END WHILE;
End;
;;

I'm missing step 1 and the conversion to integer and back to string.

Can I do the conversion with:

SELECT field,CONVERT(SUBSTRING_INDEX(value,'s',-1),UNSIGNED INTEGER) AS num
FROM doc_properties;

If so how to integrate it into my loop?


texts:
I was trialing Apex source control - trial ended. Uninstalled Apex (well I thought I did). Today I deleted the Apex database. Now I get

Msg 208, Level 16, State 1, Procedure ApexSQL_SourceControl_DDLTrigger, Line 72 [Batch Start Line 7]
Invalid object name 'ApexSQL.ApexSQL_SourceControl.Objects'.

Can not find any triggers at all on the server - how can I get rid of this error. Won't let me alter any procedures.


texts:
I have a table as

id  diary
1   breakfast
2   walk
3   start
4   office works
5   office projects
6   end
7   taxi
8   start
9   preparing for meeting
10  doing the meeting
11  end
12  night

I want to CONCAT rows between start and end to product table:

id  type      diary
1             breakfast
2             walk
3   concated  office works, office projects
7             taxi
8   concated  preparing for meeting, doing the meeting
12            night

I think I need to use GROUP_CONCAT within a IF STATEMENT, but have no idea how to implement it.


texts: How to optimize the my.cnf of a MySQL server that mixes InnoDB and MyISAM tablespaces?

texts:
I have a database that, because of some issues, I needed to convert some tables from MyISAM to InnoDB.

I basically did this:

set sql_log_bin = 0;

set sql_mode = 'STRICT_ALL_TABLES';

ALTER TABLE  `table1` ENGINE = INNODB;

ALTER TABLE  `table2` ENGINE = INNODB;

ALTER TABLE  `table3` ENGINE = INNODB;

ALTER TABLE  `table4` ENGINE = INNODB;

ALTER TABLE  `table5` ENGINE = INNODB;

ALTER TABLE  `table6` ENGINE = INNODB;

It worked right, but as far as I know now I should reconfigure the my.cnf and then restart the mysql server, correct?

How should I tune the my.cnf file if the server contains both MyISAM and InnoDB tables?


texts: Calculation is not correct during in the left join table

texts:
At the moment My head is bang on the wall as I have used few of dba.stackexchanged.com example but none of them is working.

AS I'm trying to get SKU that it sold on each Country, as I have 5 country table that it sold.

When I get results and it seems working fine, but the calculation seems wrong

One of the SKU in UK "H-ROOT-M012R-B" in return at QTY_sold_in_uk is '15552' which this calculation are wrong, the correct one is '8' which QTY has sold not '15552'

Here is a code I wrote this https://www.db-fiddle.com/f/u7PxDXy8kzSHV5n8VHC3rY/1

but I keep getting when I use this
https://www.db-fiddle.com/f/qENxYhrqhMHvUMFzYoK4ho/3

When I decided to look around and explore in the StackOverflow and I have used this link pages but results of numbers is keeping return same as the wrong one

multiple sum query with joins

second link multiple sum query with joins

MySQL Count rows from another table for each record in table

I have used inner, left and right join or union seems that calculation is never right.
Here is DB Fiddle I has worked on it
Example code

I know this code is a long one but please bear with me,

    SELECT
    ti.sku,
    ti.quantity,
    -- UK
    COALESCE((suo_uk.sku), 'Not Sold') AS Unit_in_uk,
    COALESCE(sum(suo_uk.units_ordered), 0) AS QTY_sold_in_uk,

    -- De
    COALESCE((suo_de.sku), 'Not Sold') AS Unit_in_de,
    COALESCE(sum(suo_de.units_ordered), 0) AS QTY_sold_in_de,

    -- Fr
    COALESCE((suo_fr.sku), 'Not Sold') AS Unit_in_fr,
    COALESCE(sum(suo_fr.units_ordered), 0) AS QTY_sold_in_fr,

    -- ES
    COALESCE((suo_es.sku), 'Not Sold') AS Unit_in_es,
    COALESCE(sum(suo_es.units_ordered), 0) AS QTY_sold_in_es,

    -- It
    COALESCE((suo_it.sku), 'Not Sold') AS Unit_in_it,
    COALESCE(sum(suo_it.units_ordered), 0) AS QTY_sold_in_it
FROM
    tbl_inventory AS ti
        LEFT JOIN
    tbl_sku_units_order AS suo_uk ON suo_uk.sku = ti.sku
        LEFT JOIN
    tbl_sku_units_order_de AS suo_de ON suo_de.sku = ti.sku
        LEFT JOIN
    tbl_sku_units_order_fr AS suo_fr ON suo_fr.sku = ti.sku
        LEFT JOIN
    tbl_sku_units_order_es AS suo_es ON suo_es.sku = ti.sku
        LEFT JOIN
    tbl_sku_units_order_it AS suo_it ON suo_it.sku = ti.sku

-- where ti.sku=" H-ROOT-M012R-B"
GROUP BY ti.sku

Please, Advice and your advice would be greatly appreciated.


texts: MariaDB - "ERROR 1205 ... Lock wait timeout exceeded; ..." when doing multiple parallel UPDATEs affecting different rows

texts:
We are using MariaDB 10.1.19 hosting a legacy database. We have an lpr table (on InnoDB) where we want to move from a text column gate to a normalized foreign key gate_id. Our code is doing the UPDATE in parallel, like this:

UPDATE lpr SET gate_id=1 WHERE gate_id IS null AND gate LIKE '%[1]%'
UPDATE lpr SET gate_id=2 WHERE gate_id IS null AND gate LIKE '%[2]%'
UPDATE lpr SET gate_id=3 WHERE gate_id IS null AND gate LIKE '%[3]%'
UPDATE lpr SET gate_id=4 WHERE gate_id IS null AND gate LIKE '%[4]%'
...

since the rows affected by all the UPDATEs are disjoint, and the table is on InnoDB, we wouldn't expect lock contention, but we get the error:

  ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

for all the UPDATEs. Increasing innodb_lock_wait_timeout to ~100 doesn't change anything, as well as setting FOREIGN_KEY_CHECKS to 0.

The gate column never contains more than one [VALUE] string.

If we do a single UPDATE by hand, no such error occurs.

Doing a SHOW FULL PROCESSLIST doesn't show any lock during the UPDATEs.

What are we doing wrong? Is there a table-level lock even if using InnoDB?
Or do the UPDATEs lock more rows than those strictly selected by the WHERE clause?


texts: How does MySQL evict pages from the buffer pool?

texts:
I am trying to understand the internals of MySQL. I do know that MySQL's buffer pool management has an old/young page list as shows here. And I do know about the flushing of old dirty pages, such as explained here and how to customize it here. I am also aware that MySQL uses a LRU algorithm for page eviction as here.

However, is there a buffer pool daemon/thread that evicts read only pages (not dirty pages) that were brought to the buffer pool by say a select statement? Are there ways to customize it (for example, when I pass 95% of the bufferpool capacity, start evicting read pages or flush dirty pages). In other words, what triggers in the LRU algorithm evict pages from the buffer pool (e.g. time a page is on buffer pool, percentage of buffer pool filled, need to evict pages for new pages to load), are these adjustable?


texts: Database size increases after creating a partition

texts:
I have just partitioned an existing table.

The database size was 5GB with 14 million records prior to the partition operation.  After the partition operation moved rows to the new FileGroup, I noticed the database size increased to 8GB.

Can someone explain why that happened?


texts: Report Builder CTP 2.3 Installation DLL Error

texts:
I was attempting to install Report Builder CTP 2.3 and cannot get it to install on any of three tested systems.

First system was Windows Server 2012 R2 Datacenter - MS Azure SQL 2016 preview image with SSDT 2016 Preview installed and Report Server SQL 2016 setup and running. This isn't server software, but all components for SQL 2016 preview and the SSRS service are present...

Second system was Windows 7 Ultimate, and third was Windows 8.1. All result in the exact same error message:

Has anyone successfully installed Report Builder CTP 2.3 and if so how did you accomplish this please?


texts: MongoDB / WiredTiger: reduce storage size after deleting properties from documents

texts:
I use MongoDB 3.4 with WiredTiger storage engine on a replica set of 4 nodes.

I shrinked a lot a documents by removing properties that takes most of the space, but the documentation says that this operation doesn't reduce the storage size (only the data size), and I can confirm it's true.

So I tried using the compact command: some space was freed but the storage size is still way bigger than data size. Is it because it only moves documents but does not reduce already allocated space per documents?

Do I need to delete and recreate all documents to really reduce the storage size?


texts: PLPGSQL: Cannot use parameters inside a function query

texts:
I'm trying to create a function that returns all events with the corresponding week number, and the day of week the event is occurring using crosstab and series generator.
I have tested that the actual query works inside the funcition if I use literal values, e.g. 2020 and 3 (March month number) in place of the variables.
Here is the function query I'm trying to use:
CREATE OR REPLACE FUNCTION get_month_events(
yr int,
mth int,
OUT week int,
OUT sun int, OUT mon int, OUT tue int, OUT wed int,
OUT thu int, OUT fri int, OUT sat int
)

RETURNS SETOF RECORD AS
$$
BEGIN
RETURN QUERY
SELECT * FROM crosstab('
SELECT
extract(week from starts) as week,
extract(dow from starts) as dow,
count(*)
FROM events
WHERE extract(month from starts) = mth
AND extract(year from starts) = yr
GROUP BY week, dow
ORDER BY week, dow',
'SELECT * FROM generate_series(0,6) AS dow'
)
AS (
week int,
sun int, mon int, tue int, wed int, thu int, fri int, sat int
) ORDER BY week;
END;
$$
LANGUAGE plpgsql;

When I try to call the function in a query, for example
SELECT * FROM get_month_events(2019, 8);

I'm getting this error:
ERROR:  column "mth" does not exist
LINE 7: WHERE extract(month from starts) = mth
                                           ^
QUERY:
SELECT
extract(week from starts) as week,
extract(dow from starts) as dow,
count(*)
FROM events
WHERE extract(month from starts) = mth
AND extract(year from starts) = yr
GROUP BY week, dow
ORDER BY week, dow
CONTEXT:  PL/pgSQL function get_month_events(integer,integer) line 3 at RETURN QUERY

Postgres does not recognize the parameter name inside the function query. How can I get it to reach the variable value?
It seems like it is just a stupid mistake I haven't spotted but so far I haven't been able to figure out why it does not let me access the variable.


texts: MySQL lock on MEMORY storage engine, when does it apply?

texts:
I want to use MySQL MEMORY storage engine to handle my PHP session variables (actually I already implemented it but since all my tests are being done by a single user, me, I am unable to notice this).

As it says here:
http://dev.mysql.com/doc/refman/5.7/en/memory-storage-engine.html

  MEMORY performance is constrained by contention resulting from single-thread execution and table lock overhead when processing updates. This limits scalability when load increases, particularly for statement mixes that include writes.

I don't understand this statement. It makes me think that when you insert/update/replace the table locks itself automatically and entirely so you will not be able to select anything while the write is being done. Is this correct? Can this be disabled? I need to write and read quite frequently and at the same time and I am not planning on doing transactions on this table:

CREATE TABLE `sessions` (
  `id` varchar(255) NOT NULL,
  `data` varchar(64000) NULL,
  `expires` int(11) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`)
)ENGINE=MEMORY;


texts: How to perform multiple selects without using for loop

texts:
I am querying for ids using a for-loop. So multiple selects are being used. Is there a better way to do this to have a single select statement for all ids?

for id in aoi_ids:
    sql = """SELECT id, date, col3 FROM table1 WHERE id=%s"""
    params = [id]

    return helper_func_to_execute_query(sql, params)

I know once way is to use another table. Any other faster way to do it?


texts:
I'm managing a MariaDB server and I noticed that an application run a DELETE query in a table each time the entity was saved and then it will insert the new rows referenced that entity. Sometimes happen that some rows are kept on the table and after the insert the row results duplicated.
The table was missing a PK so i added an auto increment one, but the problem persist.
I can't modify the application so i can't add a transaction to the DELETE/INSERT operation.
How can i avoid this type of duplication?

Edit 2019-06-06

Table:

CREATE TABLE `ipr` (
  `id` int(19) DEFAULT NULL,
  `prid` int(19) DEFAULT NULL,
  `seq` int(4) DEFAULT NULL,
  `qty` decimal(11,2) DEFAULT NULL,
  `price` decimal(11,2) DEFAULT NULL,
  `comm` varchar(250) DEFAULT NULL,
  `descr` mediumtext DEFAULT NULL,
  `chapter_id` varchar(19) DEFAULT NULL,
  `lineitem_id` int(11) DEFAULT NULL,
  `custom_aipk` int(19) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`custom_aipk`),
  KEY `ipr_id_idx` (`id`),
  KEY `ipr_productid_idx` (`prid`),
  KEY `lineitem_id` (`lineitem_id`),
  KEY `idx_chapter` (`chapter_id`(10))
) ENGINE=InnoDB AUTO_INCREMENT=1261 DEFAULT CHARSET=utf8

Query from mysql.general_log:

DELETE FROM ipr WHERE id='123456';

INSERT INTO ipr (id, prid, seq, qty, price, comm, descr, chapter_id, lineitem_id)
         VALUES ('123456','648',1,'1.000',0,'Comment','','891',651);
[...]
INSERT INTO ipr (id, prid, seq, qty, price, comm, descr, chapter_id, lineitem_id)
         VALUES ('123456','698',10,'1.000',0,'Comment 3','','897',692);

Edit 2019-06-07

The database schema of this application don't use any foreign key and i forgot to say that ipr.id should be (but is not) a reference to the main entity on another table.

The relation should be myentitytbl.id -> reference many on -> ipr.id.
So i cannot follow jynus suggestion.

At the moment I'm not searching a workaround to prevent row duplication, I badly explained myself.The problem that I'm concerning is that MariaDB when receive a DELETE query doesn't delete all rows.

For example if i have 15 rows in ipr table with id = 213 when the application run DELETE FROM ipv WHERE id = 213; only 5 rows got deleted and the other 10 don't get deleted, then when the application run all the INSERT i will have $newrow+10 that don't get deleted by the previous statement.


texts: Where does SSMS Last database backup date come from?

texts: The backup date for several of my databases do not appear in msdb.dbo.back upset yet the correct date appears in SSMS when I select a db name, right click, properties, there in the general tab is the correct date. Where does SQL Server get this date?


texts:
MongoDB version 3.6

I got the duplicate key message when I executed this. What's the actual problem?

mongos> db.setup.findAndModify({
... query : {"_id" : ObjectId("5b3c7abbea95705803084cad")},
... update : {$set : { "test" : "xxxx" }},
... upsert : true
... })
2018-07-06T10:13:22.749+0000 E QUERY    [thread1] Error: findAndModifyFailed failed: {
    "ok" : 0,
    "errmsg" : "E11000 duplicate key error collection: testdb.setup index: name dup key: { : null }",
    "code" : 11000,
    "codeName" : "DuplicateKey",
    "$clusterTime" : {
        "clusterTime" : Timestamp(1530872002, 603),
        "signature" : {
            "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
            "keyId" : NumberLong(0)
        }
    },
    "operationTime" : Timestamp(1530872002, 602)
} :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
DBCollection.prototype.findAndModify@src/mongo/shell/collection.js:724:1
@(shell):1:1
mongos>


texts:
SQL server created a lot of SQLDump.txt files in log folder I'm using SQL server 2017, but I have two databases in My SQL server. I find out my problem isn't about to shrink Ldf file I checked it out. I researched about Error Logs, SQL log, and transaction log. I don't know how to find out which of them are my issues?

  Microsoft SQL Server 2017 (RTM-GDR) (KB4494351) - 14.0.2014.14 (X64)
  Apr  5 2019 09:18:51   Copyright (C) 2017 Microsoft Corporation
  Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0
   (Build 14393: ) (Hypervisor)


texts:
My situation:

Table: User { Id, Name, Stone, Gold, Wood }

I have "write" threads:

miningThread (every minute)

UPDATE User SET Stone = @calculatedValue WHERE Id=@id
 UPDATE User SET Wood = @calculatedValue WHERE Id=@id

tradingThread (every minute)

UPDATE User SET gold = @calculatedValue WHERE Id=@id

constructionThread (every minute)

UPDATE User SET Wood = @calculatedValue WHERE Id=@id
 UPDATE User SET Stone= @calculatedValue WHERE Id=@id

And have "write" requests from users:

SellResource

UPDATE User SET Stone(Wood,Gold) = @calculatedValue WHERE Id=@id

(calculatedValue is calculated by C# bussiness logic code)

In this case, I have a lot "lost update" problems if i set read_commited_snapshot isolation level.
But if i set serializable or snapshot level, all work fine

Question

I was looking at an "isolation levels comparison" table and saw that serializable and snapshot isolation solve all problems with concurrent transactions. However, serializable is very slow.

Can I use snapshot isolation for all write transactions? I don't
want chaos in my tables. My business logic is hard and always changing.
Does snapshot isolation have flaws?
What isolation level is better for readonly transactions?


texts: Cardinality , Parallel Hint alternative for MS-SQL

texts:
UPDATE MARK M SET ARCHIVE_FLAG = 'N' WHERE EXISTS
(SELECT /*+ cardinality(S1, 10) parallel(S1,8)*/ 1 FROM SHFASG S, SHIFT S1
WHERE S.ID = S1.ID AND M.ID = S.MARKID AND ARCHIVE_FLAG <> 'Y');

This is the oracle query that I have, I want to create similar query for my MS SQL DB, Any help please


texts:
How can I get values of array in my jsonb type column named 'body' in order to use the value to join another table.
example:-

SELECT U.*
from masters."USERS" as U
    left join masters."ROLE" R on U.body->'role' = R.id

Below is my column data.

{
    "live": "0",
    "role": [
        "1"
    ],
    "desig": "",
    "email": "chandan@logibricks.in",
    "empCode": "",
    "mobileNo": "",
    "password": "$2a$10$qDENcVR/ZDFIsp/blXK7h.h4xsmI18/LArvbqmtNfXxZ6jYO/s5YG",
    "userName": "chandan",
    "full_name": "Chandan ",
    "department": "",
    "updated_by": "rhlsoni",
    "is_access_results": "1",
    "updated_timestamp": 1550556834,
    "reporting_manager_id": "",
    "is_access_testscripts": "1",
    "is_access_workingpapers": "1"
}


texts: convert legacy showplan text into graphical format

texts:
Does anyone know of a way to convert legacy showplan text into a graphical format?

In other words, can I take the StmtText returned from SET SHOWPLAN_TEXT ON and convert that to show me a graphical execution plan? Would I need to parse it into XML, and the .sqlplan format in order to see the graph?

Eric:, here is the example, run against AdventureWorks:

SET SHOWPLAN_TEXT ON
GO

SELECT c.CustomerID, soh.ShipDate
FROM Sales.SalesOrderDetail sod
    INNER JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
    INNER JOIN Sales.Customer c ON c.CustomerID = soh.CustomerID
WHERE sod.SpecialOfferID = 2
AND soh.ShipDate between '2003-01-01 00:00:00.000' and '2004-01-01 00:00:00.000'
AND c.CustomerType = 'I'

And you will see this StmtText returned:

StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Hash Match(Inner Join, HASH:([soh].[CustomerID])=([c].[CustomerID]))
       |--Hash Match(Inner Join, HASH:([sod].[SalesOrderID])=([soh].[SalesOrderID]))
       |    |--Index Seek(OBJECT:([AdventureWorks].[Sales].[SalesOrderDetail].[ix_nc_SpecialOfferID_inc_SalesOrderID] AS [sod]), SEEK:([sod].[SpecialOfferID]=(2)) ORDERED FORWARD)
       |    |--Index Seek(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[ix_nc_ShipDate_inc_SalesOrderID_CustomerID] AS [soh]), SEEK:([soh].[ShipDate] >= '2003-01-01 00:00:00.000' AND [soh].[ShipDate] <= '2004-01-01 00:00:00.000') ORDERED FORWARD)
       |--Index Seek(OBJECT:([AdventureWorks].[Sales].[Customer].[ix_nc_CustomerType] AS [c]), SEEK:([c].[CustomerType]=N'I') ORDERED FORWARD)


texts: PostgreSQL: Multiple Inheritance tables (one child to two parents)

texts:
If I have a base table and 2 or more tables that inherit from it, child1 and child2, I can insert a record into a child table and it shows up in base as you would expect.
However what I want to know is, is it possible to have a record that is present in both child1 and child2?

As an example say base is vehicle, child1 is boat and child2 is car, And I want to insert an amphibious car (effectively both a boat and a car).

I know I could do this the normal way, by having relationships but in theory it could be neat if it's possible to do as described.


texts:
I am about to design an enterprise solution for a large scale application in which a windows service, MVC, and many other projects will use a database, so I am thinking about load balancing and best practices to organize my database.

I have two options :

use different schemas (I know schema is much like a folder)
use different databases

From the point view of database design which is better, and what's the best practice?


texts: Cannot overwrite file: /var/lib/mysql/ibdata1 when using Percona xtrabackup

texts:
I am receiving an error when I am trying to restore from a percona backup to my local mysql server.

  Cannot overwrite file: /var/lib/mysql/ibdata1 at /usr/bin/innobackupex
  line 2128.

sudo innobackupex --copy-back --force-non-empty-directories /home/fazlan/Desktop/2015-05-25_09-59-31/

I had also shut down the mysql service prior to executing this command using

sudo service mysql stop

How do I fix this error?

I am using Ubuntu 14.04.


texts:
I have been looking pretty hard for the documentation regarding the RingBufferTarget

<RingBufferTarget truncated="0" eventsPerSec="1370714" processingTime="7" totalEventsProcessed="9595" eventCount="600" droppedCount="0" memoryUsed="523832">

Do you know what the following attributes mean or have you found the documentation?

truncated
eventsPerSec
processingTime
totalEventsProcessed
eventCount
droppedCount
memoryUsed


texts: Replicating only changes made by a specific database user

texts:
I am performing parallel testing.  We have upgraded the legacy batch jobs to a new framework which we need to test against the existing batch jobs.

In the existing setup, I have web application which creates or updates records based on user input and these batch jobs will process the records.

The plan is to have 2 databases, primary and secondary, with the existing batch job connected to the primary database server and the new batch job connected to the secondary database server.

The web application will populate data to both primary and secondary databases.

I would like to perform replication for all the tables in the database. However, I would only like to replicate any DML changes done by the specific database account used by the web application.

Is that possible?


texts: How to handle user with no password in postgresql

texts:
I installed postgresql but it's asking me for passwords that I never had and never entered. There is a postgres ubuntu user and a database user named moodle. Neither of these were created with a password. Now that my system is asking me for those passwords, what should I do? Why is this process not easy?

$ psql -Umoodle -hlocalhost moodle < /var/www/vcmoodle/moodle/kth-install/moodle_fresh_postgres.sql
Password for user moodle:
psql: fe_sendauth: no password supplied
postgres@dac-VPCSA2Z9E:~$ sudo psql -Umoodle -hlocalhost moodle < /var/www/vcmoodle/moodle/kth-install/moodle_fresh_postgres.sql
[sudo] password for postgres:
postgres@dac-VPCSA2Z9E:~$ sudo -u moodle psql moodle
sudo: unknown user: moodle
sudo: unable to initialize policy plugin
postgres@dac-VPCSA2Z9E:~$ sudo -u postgres psql moodle
postgres is not in the sudoers file.  This incident will be reported.
postgres@dac-VPCSA2Z9E:~$ sudo -u dac psql moodle
[sudo] password for postgres:
Sorry, try again.
[sudo] password for postgres:
sudo: 1 incorrect password attempt
postgres@dac-VPCSA2Z9E:~$ sudo -u postgres psql
postgres is not in the sudoers file.  This incident will be reported.
postgres@dac-VPCSA2Z9E:~$


texts: Background images in SSRS report resizing on export to PowerPoint

texts:
We are experiencing an issue with SSRS 2016, when we export a report with a background image to PowerPoint.  The image displays properly in the designer (VS 2015), and in preview as well; it also displays properly if exported to PDF or Word.  When exported to PowerPoint, the image is reduced to roughly 1/3 of the intended size.  We have attempted changing the DpiX and DpiY device information settings, to no avail.  We then tried enlarging the background image to 3x the screen size; this worked for the PowerPoint export, but now the image extends well beyond the view in the designer, preview, and other export formats.

We formerly used Aspose for this one critical report; however, the verion we currently have of Aspose does not work with SSRS 2016.


texts: Postgres soft delete partition, what to do with Foreign keys?

texts:
I have a table A that has a couple of other tables (B, C) attached to it (many rows in B and C for one row in A).

I'm having performance problems with A, so I thought I would partition it using a date_deleted column (NULL/NOT NULL) into 2 tables A_active and A_deleted.

Data flow is as follows:

Records are created at a rate 10k per day, inserted into A_active
After 2 months records are moved to A_deleted
Records are never un-deleted
Nothing is ever hard deleted, currently there are 1.3M rows

I have written a full script to split my A into A_active and A_deleted, create triggers for inserts and updates, but I'm at a loss about the foreign keys.

Should I create B_active and B_deleted? The triggers will get super complex.

Should I create 2 FKs from B? One to A_active, one to A_deleted? Triggers will also be very complex.

Should I scrape this idea altogether? I kind of like the idea of having a dedicated table for historic records. It will simplify my selects.


texts:
Payments

PaymentRef
Product
PaymentAmount
MerchantRef

P01
ABC
100
MR01

P02
ABC
200
MR02

P03
XYZ
200
MR03

Refunds

ReundRef
Product
PaymentAmount
MerchantRef

R01
ABC
-10
MR04

R02
ABC
-100
MR05

These tables used to be joined on MerchantRef, resulting in a list of payments and refunds. This worked because only single refunds against a product were allowed. Multiple refunds are now allowed, meaning MerchantRef must change for each refund (payment provider requirement) therefore breaking the join. If I change the join use Product, I end up with duplicated rows.
Is there method or some trickery that will allow me to join on Product at all? While Product can exist multiple times, it always refers to the same thing. I don't think there's a way to proceed as we have no way of knowing which refund refers to which payment. The only approach I can think of to SUM() PaymentAmount for all matching Product records, and do the same for refunds, and do it based on total.
SQL isn't my forte at all, but am I right in thinking my only solution here is having a reference between the tables that never changes?
I'm trying to keep changes to a minimum, I never would have started with this design, but here we are.


texts: Declare Variable to use as placeholder text for tablenames, not using declare table type

texts:
Below, is a sample excerpt of a reoccurring code I run monthly.  Each run, a new table is created based on the Month. For example, the created table name isYEAR2016..report_Nov2016, but for next month the tablename will end in _Dec2016.  Instead of find&replace to change all table names(10 occurrences), I want to have one declare variable at the beginning of my code so that I only have to change the table name once and it gets reflected throughout the code.

declare @table varchar(30)
set @table ='YEAR2016..report_Nov2016'
select  recipient_ID, recipient_Name, recipient_DOB,program_code
into @table
from YEAR2016..claim_table

Separate batches

alter table @table
add Medicaid char(1)
GO
update @table set Medicaid =''
GO
update @table set Medicaid ='Y' where program_code ='MK A'

From what I understand, declare @table table () should be used, but that won't accomplish my intention, as a table needs to be created in the database.  I'm also aware,with declare table type, into @table won't work and needs to be Insert into @table.

Is it possible in TSQL to do what I'm envisioning?


texts: connection pooling, transactions, nested transaction and rollback

texts:
I've got strange behavior on production.

First user request thru application failed, I've got SQLServerException with "Lock request time out period exceeded." After that following requests are successfully executed. After some time I've closed client application and all requests disappeared. I know that requests were successfully executed because I have JDBC logging on my client application.

Is there any other possible scenario other than nested transactions and rollback?

Connection pooling uses same spid (session) when it's possible so that must have been the case here.

Every request is just one stored procedure which has explicit transaction defined. I'm not sure how this could happened because transaction would either be committed or rollback because of this block.

begin try

  begin tran

  set lock_timeout x

  ...

  commit tran

end try

begin catch

  if @@trancount > 0

    rollback tran

  ...

end catch

Also, would it be wise to add on start just after "begin try" rollback tran if @@trancount > 0? That way I would prevent future issues like this.

Note, yes, I know that SQL Server doesn't have nested transactions and I don't use them. I'm mentioning them because I don't see other way that this could happened. Thanks!

EDIT:

I believe I have more conclusions about this.

BEGIN TRY
  BEGIN TRAN
      SET LOCK_TIMEOUT 10000
      UPDATE tbl ...
  COMMIT TRAN
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRAN
  INSERT INTO dbo.ErrorLog ...
END CATCH

I think that my update tbl went into lock timeout. After that it went to catch block and I believe that rollback tran went into timeout also. Is this possible? Imagine database maintenance job (index rebuilds) and synchronous mirroring running in parallel with this query.

That would match my application logs because I see 20 seconds (10 on update and 10 on rollback) difference between call to stored procedure and sql exception timeout.

I also don't have record in dbo.ErrorLog for that entry.

It must have been lock timeout on rollback because I wouldn't get such exception in application logs if it happened in try block because it's contained by catch so it must have been raised in catch.


texts: MySQL MyISAM Table Locking and Delayed Inserts for Webserver Access Logs

texts:
We will install a webserver that writes access logs to a MySQL table. Access logs of course will get really really big, so this will be a separate MySQL instance from the regular data and we will more librally delete the binlogs.

We will also have to delete older items from the table which means delete commands and optimize commands that will make the inserts wait.

Is there a way to have delayed inserts that are in a preserved order and that will occasionally tolerate many items in the delayed insert queue?

If the inserts will get mixed up then it is not a show stopper. We can use timestamps and milliseconds for sorting.

Also, can we prevent wasteful growth of index files? Perhaps we can optimize the indexes similar to optimizing the data files? Would requiring the indexes to be fixed width prevent data rotation waste problems?


texts:
I am trying to backup our postgres database (8.4.17), which is about 25GB in size.

pg_dump database_name > database_db_dump_2014-05-05.sql

unfortunately, the backup stops after about 600MB in, and does not continue. It always stops when trying to dump a specific table (fb_crawler_event). I was able to successfully dump all the other tables using the --table swtich. I have already stopped any other process that could interact with the database.

server log after a restart of the DB (looks ok to me):

2014-05-05 14:34:46 CEST LOG:  all server processes terminated; reinitializing
2014-05-05 14:34:46 CEST LOG:  database system was interrupted; last known up at 2014-05-05 14:32:50 CEST
2014-05-05 14:34:46 CEST LOG:  database system was not properly shut down; automatic recovery in progress
2014-05-05 14:34:46 CEST LOG:  record with zero length at 1A1/AD6A78C0
2014-05-05 14:34:46 CEST LOG:  redo is not required
2014-05-05 14:34:47 CEST LOG:  database system is ready to accept connections
2014-05-05 14:34:47 CEST LOG:  autovacuum launcher started

verbose output from pg_dump (nothing suspicious, but the obvious table, which doesn't let the database continue to dump)

... lots of lines ...
pg_dump: restoring data for table "django_site"
pg_dump: dumping contents of table django_site
pg_dump: restoring data for table "fb_crawler_event"
pg_dump: dumping contents of table fb_crawler_event

And then it just halts.

output from the pg_locks table, which seems to be huge to me (294 entries all together):

  locktype  | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |      mode       | granted
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+-----------------+---------
 relation   |    16384 |     2674 |      |       |            |               |         |       |          | 1/23               | 19526 | AccessShareLock | t
 relation   |    16384 |    27367 |      |       |            |               |         |       |          | 1/23               | 19526 | AccessShareLock | t
 relation   |    16384 |   695092 |      |       |            |               |         |       |          | 1/23               | 19526 | AccessShareLock | t
 relation   |    16384 |     2675 |      |       |            |               |         |       |          | 2/7                | 18960 | AccessShareLock | t
 relation   |        0 |     2671 |      |       |            |               |         |       |          | 1/23               | 19526 | AccessShareLock | t
 virtualxid |          |          |      |       | 2/7        |               |         |       |          | 2/7                | 18960 | ExclusiveLock   | t
...

I am no expert in postgres, so I am wondering if the database is corrupt?

How can I further debug this anomaly?


texts: What this mysql instance storing on RAM memory apart from Innodb buffer pool: Linux - Mysql 5.6.2 High memory usage

texts:
We have a mysql 5.6.2 installed on one of our clients machines.

Innodb buffer pool   : 3 GB
Max connections is   : 200
Max used connections : 100
Performace schema    : Enabled
Load                 : READ Intensive
Data                 : Mostly Innodb
Key buffers and additional buffer pool are nominal.
In memory limit for temp table is 1 GB (`tmp_table` & `max_heap_table`).

We have observed it has occupied around 15GB resident memory on RAM, which seems surprising.

This is when we read from a view with multiple simultaneous connections.
When I checked in smaps for this process, I could see 3 GB innodb buffer
pool size and along with that there are 160 memory maps each of 60 MB ( ~ 9 GB of RAM).

Can someone please explain what these memory maps could be?


texts: How Do I Query this Data in Access Without Creating a Circular Reference

texts:
I have two tables called Events and EventSeasons. Events define the main type of events that there can be, and EventSeasons stores the number of times that the events have occurred yearly. I'm trying to query them so that I can figure out which ones are ongoing (between a range of dates), but my problem is that I also need to retroactively mark past seasons of the same event type as active. The next diagram shows what the output should be:You can see in EventSeason that IsActive column for item 2003 is true. Therefore in Events the Subactive column for the item 1001 (Summer) is True. Finally back in EventSeasons the Retroactive column for all 1001 items is set to true.

I have actually achieved this in Excel, but now that I'm trying to do it in access I feel like I'm going to create a circular reference. What I did in excel was to assign true to IsActive in EventSeasons if today's date is between range, then I assign true to Subactive in Events if there are instances of the current EventID marked as active in EventSeason. I then finally assign RetroActive to true if Subactive is true Or IsActive is true.


texts: Design SQL Server 2008 new install ... which collation?

texts:
A customer has a SQL Server 2005 instance, collation SQL_Latin1_General_CP1_CI_AS, with 8 databases ... for a new application, we have been asked to install a SQL Server 2008 with collation SQL_Latin1_General_BIN.

In order to maintain a single database server, and then also by addressing the previous db migration to the new version, which instance collation should be adopted?

Thanks a lot

Bye,
Francesco


texts: Run two SQL Agent Jobs Simultaneously (not sequentially) as the next step

texts: At the completion of Step 1, the job currently goes to step 2 - an osql command to start a Sql Agent job on another server.  However at the completion of step 1, I would also like to start a concurrent SQL Agent Job on the same server as the step 1 SQL Agent job.   Right now, the jobs are cascaded but I would really like "job 2" and "Job 3" to run at the same time.  Any ideas on how to make steps non- sequential?


texts: Register XML schema using dbms_xmlschema in Oracle 11g

texts:
I have a xsd file located at C:\temp\test.xsd

I need to register this schema to create a XMLType table for Object Relational XML Storage.

How do I register the schema for relational storage?

I do not seem to find any example on internet for registering the schema for relational storage.

Any help would be highly appreciated.


texts: Anybody using HierarchyId in production? Is it reliable?

texts:
Is anybody using HierarchyId in real production with tables of reasonable size, more than a few thousand rows? Is it reliable/performant? So far I have not found anyone not affiliated with the vendor recommend it, and Paul Nielsen advises against it here.

What is your experience with using HierarchyId in actual production systems?

Which criteria have you used when you were choosing HierarchyId over its alternatives?


texts:
This is my log - if I run the procedure locally it will still perform as it should and generate the message, but when it is run as a SQL Job it fails.  (For security I have changed the server name and database names)

12/07/2015 14:30:00,Send DBMail,Error,1,Hellfire,Send DBMail,Run Stored Procedure,,Executed as user: NT AUTHORITY\SYSTEM.
Mail queued. [SQLSTATE 01000] (Message 0)
Mail queued. [SQLSTATE 01000] (Message 0)
Mail queued. [SQLSTATE 01000] (Message 0)
Mail queued. [SQLSTATE 01000] (Message 0)
1234 [SQLSTATE 01000] (Message 0)
5678 [SQLSTATE 01000] (Message 0)
11111 [SQLSTATE 01000] (Message 0)
erqe [SQLSTATE 01000] (Message 0)
ssssss [SQLSTATE 01000] (Message 0)
ffff [SQLSTATE 01000] (Message 0)
Subquery returned more than 1 value. This is not permitted when the subquery follows =<c/> !=<c/> <<c/> <= <c/> ><c/> >= or when the subquery is used as an expression.
The step failed.,00:05:10,16,0,,,,0

EDIT
The user account NT Authority\System does not seem to be the issue as this account has no problem sending other messages using the same settings.  It is just this one procedure that errors out (will provide procedure code shortly)

EDIT # 2
This is the sql syntax I am using (may not make much sense as again I changed sensative data to random garbage details).

Set @fromDate = '12/01/2015'
Set @toDate = '12/05/2015'

Declare @query varchar(MAX), @name varchar(50), @toDate datetime, @fromDate datetime, @FQN varchar(500)
Create Table #problemtable (name varchar(50))
Create Table #PD (name varchar(50), LQ int)
    INSERT INTO #Datttta (nameName)
    SELECT DISTINCT(emp)
    FROM server.database.dbo.one
    ORDER BY emp ASC
        DECLARE C1 CURSOR FOR
            SELECT name FROM #Datttta
            OPEN C1
            FETCH NEXT FROM C1 INTO @name
            WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @FQN = (SELECT fqn FROM fulldatabasemapping WHERE rciaem = @name)
                SET @Query =    'update #Datttta '
                            + 'set onetwothree = '
                            + '(select count(*) from '+@FQN+' '
                            + 'where terminationcategory in (select '
                            + 'termcat from '
                            + 'server.database.dbo.twotwo'
                            + 'where emp in '
                            + '(''' +@name+ ''')) '
                            + 'and CAST(termdate As Date) between ''' + CONVERT(VARCHAR(10),@fromDate,101) + ''' '
                            + 'and ''' + CONVERT(VARCHAR(10),@toDate,101) + ''' '
                            + 'and managersignoff is null) '
                        Begin Try
                            exec (@query)
                        End Try
                        Begin Catch
                            insert into #problemtable
                            (name) select @name
                        End Catch

                    FETCH NEXT FROM C1 INTO @name
            END
            CLOSE C1
            DEALLOCATE C1

--Send mail piece is here


texts: How can I authenticate mongodb server identity via mongodb shell?

texts:
I am trying to deploy a mongodb instance in my environment. I have settled a ssl connection so it is possible to authenticate clients (using a certificate they enter and agreed root ca by both the server and the client). Is there a way to authenticate the server identity too? (I could not find any reference to this issue)

I would like to use the mongo shell (without any side programming)

Thank you


texts:
I have this stored procedure:

DELIMITER $$
CREATE PROCEDURE updateTrendContentStats(IN mongoid VARCHAR(255),IN numericid INT,IN points INT,IN ctype INT,IN activity INT,IN created INT)
 BEGIN
   DECLARE row_count INT(1) default 0;
   SELECT count(id) into row_count FROM trendContentMaster WHERE pid=numericid;
   IF row_count = 0 THEN
     INSERT INTO tMaster(tci,pid,tct,tcd,tcf,tcm,tcf,s)
       VALUES(mongoid,numericid,ctype,points,points,points,points,1);
   ELSE
     UPDATE tMaster
       SET tcd = tcd + points,tcw = tcw + points,tcm = tcm + points,tcf = tcf + points
       WHERE pid=numericid;
   END IF;
   INSERT INTO tTransaction(tci,pid,tct,act,acp,cd)
     VALUES(mongoid,numericid,ctype,activity,points,created);
 END $$
DELIMITER

Which works fine, except it updates tMaster field twice. For example existing value of tcd, tcw, tcm, tcf is 30 and points value is 20 rather than 50 it updates value to 70.

What am I doing wrong in the increment operation?


texts: MS SQL server set variable to where clause

texts:
I am new in MS SQL server and oracle database
I have the following query:

 SELECT * from TBL1 where adc in ('21','24');

but I need something like this:

DECLARE @sqlq nvarchar(500)
    Set @sqlq = '21','23';--(how can I write this)
    SELECT* from OPENQUERY([CBSCSDB], 'SELECT * from TBL1 where adc in (@sqlq)');

problem is how can I set value @sqlq and put it in the select statement in where clause.


texts: Extract part of string based on nth instance of character

texts:
I am trying to find a way to extract part of the strings below. I need everything between the 6th and 7th \ character.

\\fileServerA\d$\LiteSpeed\Wednesday\ServerA\Tlog\DBA1_TLOG_20110504_0333.SLS
\\fileServerA\d$\LiteSpeed\Wednesday\ServerB\Tlog\model_TLOG_20120321_1038.SLS
\\fileServerA\d$\LiteSpeed\Saturday\ServerA\TLog\DBA_2_TLOG_20120811_1538.SLS
\\fileServerA\d$\LiteSpeed\Friday\ServerB\Tlog\DB3_TLOG_20120914_2330.BAK
\\fileServerA\d$\LiteSpeed\Wednesday\ServerC\Tlog\DB44_TLOG_20120815_1445.BAK

Desired results:

ServerA
ServerB
ServerA
ServerB
ServerC


texts: One MySQL database or many SQL Server databases

texts:
At the moment I have an in-house Access database, data stored by our online survey provider, and data stored by our project management provider, both in databases. What I want is an in-house database where I have all data harmonized. Now I see two options for us (since we are money limited)

Set up a MySQL-DB with all data in one database or
Set up many SQL Server Express databases

I have worked with a SQL Server database but I have not the experience to decide what is better and why. So it would be great if someone could help me to decide that and tell me why.


texts:
Trying to create a table with the script below. Every time I execute it I get the following error and can't work out why?!

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ,
  CONSTRAINT cat_id
    FOREIGN KEY ()
    REFERENCES waitron.`catego' at line 7 **

CREATE  TABLE IF NOT EXISTS `waitron`.`dishes` (
      `dish_id` INT NOT NULL AUTO_INCREMENT ,
      `dish_name` VARCHAR(90) NULL ,
      `dish_desc` VARCHAR(200) NULL ,
      `price` DECIMAL(19,4) NULL ,
      PRIMARY KEY (`dish_id`) ,
      INDEX `cat_id` () ,
      CONSTRAINT `cat_id`
        FOREIGN KEY ()
        REFERENCES `waitron`.`categories` ()
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;


texts:
I have a table defined as follows, but I'm not sure how to cluster the table:

CREATE TABLE [Security].[UserGroups] (
  [UserId]  INT NOT NULL,
  [GroupId] INT NOT NULL,
  [IsAdmin] BIT NOT NULL,

  CONSTRAINT PK_UserGroups PRIMARY KEY CLUSTERED (UserId, GroupId),

  CONSTRAINT FK_UserGroups_User FOREIGN KEY (UserId) REFERENCES [Security].Users(Id),
  CONSTRAINT FK_UserGroups_Group FOREIGN KEY (GroupId) REFERENCES [Security].Groups](Id),

  INDEX [IX_UserGroups_GroupId] (GroupId)
  );

As far as I am aware a clustered index should be:

unique
static
narrow
ever-increasing

My Primary Key meets the first three of those, but isn't ever-increasing.

Is it worth changing this table?

I could add an extra IDENTITY column for the CI (and if so, do I make it the PK too), or not cluster it at all and use a heap. Both of which seem bad, but so does the leaving the CI as is.


texts: Transaction log bloat when writing to table on replicated database with parameterized filter

texts:
We are observing abnormally large log writes whenever we update a table that is used for parameterized filter in a merge replication. There are a number of join filters as well.

When executing a stored procedure that simply deletes some rows and inserts new rows into the table, it can take anywhere between few seconds to few minutes. During that time, 200-300 MB worth of data is written to the transaction log.

At first, I assumed it was because we had precomputed partitions on and modifying the data on the table would cause the partitions to be re-calculated. As a test, I tried turning off the feature to precompute partition (but keep the partition groups AKA "optimize synchronization"). That didn't help. I turned off all optimization altogether. That still didn't help.

So it doesn't seem that precomputed partition is the reason behind large amount of log write whenever updating that table. I'm stumped now. What else could be being written and how can we avoid this?


texts: SQL profiler how to filter with EventClass column

texts: I need to filter Profiler events by EventClass like RPC:completed, but I cannot find any filter for EventClass. Any idea how to do that?