texts:
I have a table with schema along the lines of:

CREATE TABLE table1(
  account VARCHAR(64) NOT NULL,
  json    JSONB       NOT NULL
);

And I'd like to create an index on it that will allow me to run queries such as:

SELECT *
FROM table1
WHERE account = :account
  AND json ... -- Some JSON search expression

So I tried adding an index on this table like this:

CREATE INDEX index1 ON table1 USING GIN (account, json);

And I'm getting:

ERROR: data type character varying has no default operator class for
       access method "gin"
Hint: You must specify an operator class for the index or define a default
      operator class for the data type.

I've tried adding _varchar_ops:

CREATE INDEX index1 ON table1 USING GIN (account _varchar_ops, json);

And then I got:

ERROR: operator class "_varchar_ops" does not accept data type character varying

I realize I can simply add the account as a field of every json document, but I'd like to avoid this duplicity and potential gotcha or room for discrepancy, and just have the account as a column.

How can I solve this and get a combined index on these two fields?


texts: Windows 7/x64 . installing oracle 11.2 32-bit client.  Install proceeds, then at 80% hangs on netca.  I have not had any luck finding a solution.   Any suggestions?


texts: Dynamic column value to columns name mySQL

texts:
I have table like following, i have almost 100+ questions and more than 30,000 students:

StudentId | Questions  | QID| Answer  | View   |
1         | YourGender | 1  | Male    | Gender |
2         | YourGender | 1  | Female  | Gender |
1         | YourAge?   | 2  | 23      | Age    |
2         | YourAge    | 2  | 21      | Age    |

I need table like followings:

StudentId | YourGender  | YourAge?
    1     | Male        | 23
    2     | Female      | 21

My questions are randomly add/modify/del, so I want everything should work dynamically, when questions added or deleted I need not to change anything on my code.


texts: Power BI Scale Out Data model Refresh Control

texts:
What, if anything, controls which server in a PBIRS scale out performs the refresh?
I have 3 servers. One is a Back End server where SQL is installed and contains on premise Power BI Report Server Database. The Back End also has the Reporting Services service installed and is configured to do data model refreshes for reports and dashboards.. The other 2 are Front End servers with only the Reporting Service Service installed and both are doing refreshes of data models for reports and dashboards. These 2 servers point to the database on the Back End server.
In the Reporting Services Configuration Manager on each server the scale out section contains all 3 servers.
What/how is it decided which of the 3 servers will perform the data model refresh?


texts: MySQL Master/Slave. Actual use of Slave Machine

texts:
We have a MySQL server running in a mobile production trailer.  The trailer is out on the road for weeks at a time.  We have cell internet and wifi access is nearly all of our locations.  Our office staff (bookkeeper, HR, etc) are at a brick and mortar office.

There are many times when the office staff need to access information but if the trailer is traveling or after hours then clearly we aren't online.

I was thinking/hoping we could replicate from the Trailer (Master) to the Office (Slave).  This way the office web servers (duplicated of the trailer web server) would be able to access nearly up to date information.  It wouldn't be 100% live but it's better then waiting a week or two for the trailer to come home.

The office would need to be able to write to the database.. BUT these changes CAN be overwritten during replication.  All of the work they do is simply reading data.  The few writes they would perform are inconsequential and would not matter if they were overwritten.

Am I going about this the right way?  Can I simply point my office web server to the Slave MySQL server? Will they be able to write to the slave?

Thanks!


texts: Introducing surrogate key in may-to-many table?

texts:
I have 3 table:

brand: brand_id(pk), name

product: product_id(pk), name

brand_product: brand_product_id(pk), brand_id, product_id

And I have a report table, at the moment it looks like this:

product_report: product_report_id(pk), quantity, brand_id, product_id

My question is, what are the pros and cons of having the brand_product_id column instead of brand_id, product_id.


texts: Elastic search sharding strategy for 3 TB database

texts:
I have an elasticsearch database that supposes to get 3TB in a single index.
The database is well structured. a very fine-tuned JSON that contains. short strings.
in the next year, ill have not more than 750GB, in the future, we will have more and more.
but not in a daily manner, we "batch import" from time to time to the same index.
I am starting with a single node memory of 64GB and 1TB SSD.
Before Im importing the data, I want to know how many shards should I create?
Thanks


texts: Is it possible to limit timeout on Postgres server?

texts:
I set connection and command timeout to 10 minutes in my application (client side).

Than my application runs a simple query: SELECT pg_sleep(65)

On some servers it works fine, but other servers close the connection after 60 seconds.

Could this be some sort of PostgreSQL server configuration which limits timeouts and ignores my client settings?


texts: How to find how many number of transactions are happening in SQL Server/SQL Server database in a day?

texts:
I have to migrate my SQL Server on a particular public cloud, which is charging/licensing on the basis of number of transactions happening on the SQL Server.

I am aware of the Performance Counters for SQL Server:Databases - Transactions/Sec AND Batches/Sec.

Do I simply convert these transactions per second data to hours, or is there some other method to find out total number of transactions going on in SQL Server.


texts: SSIS Script Component - How to modify the Output0Buffer

texts:
I've a script component which accepts records from SQL Azure database table. The script then invokes a web service, which returns the number of failed and success records.

For all the records, I would like to add the Status field which has either "success" or "fail" and this gets output from script component.

I then log those output into a text file.

Problem:
I'm not been able to add status for each input records since the web service call only happens on post execute.

I tried this but still doesn't work:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
         listOfData.Add(new ClockData
         {
             TimeClockID=Row.TimeClockID,
             PersonID=Row.EmployeeCode,
             LocationCode=Row.ClockInServiceContextID,
             ClockInDateTime=Row.ClockInDateTime,
             ClockOutDateTime=Row.ClockOutDateTime

         });
 }

 public override void CreateNewOutputRows()
 {
     MessageBox.Show("Test CreateNewOutputRows");
     MessageBox.Show(listOfData.Count.ToString());
     foreach (var item in listOfData)
     {
         Output0Buffer.AddRow();
         Output0Buffer.EmployeeCode = item.PersonID;
         MessageBox.Show(item.PersonID);
     }
 }


texts: Possible to intercept and alter SQL Queries in the engine before it gets executed?

texts:
I've given up trying to fix a php program (which is of course geared to MySQL) running on SQL Server.

[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near '`'. for query UPDATE wp_posts SET post_author = yyy, post_date =  N'2017-04-16 16:52:44.000', post_date_gmt =  N'2017-04-16 14:52:44.000', post_content =  N'\\ \', `post_content_filtered` =  N'', `post_title` =  N'test \\', `post_excerpt` =  N'', `post_status` =  N'draft', `post_type` =  N'post', `comment_status` =  N'closed', `ping_status` =  N'open', `post_password` =  N'', WHERE ID = xxx made by edit_post, wp_update_post, wp_insert_post, pdo_wpdb->query, pdo_wpdb->_post_query, pdo_wpdb->print_error

Is it possible to create a filter on a specific database only, and remove all the backticks / grave symbols in order to sanitize all incoming queries?


texts: Optimization issue with user defined function

texts:
I have a problem understanding why SQL server decides to call user defined function for every value in the table even though only one row should be fetched. The actual SQL is a lot more complex, but I was able to reduce the problem down to this:

select
    S.GROUPCODE,
    H.ORDERCATEGORY
from
    ORDERLINE L
    join ORDERHDR H on H.ORDERID = L.ORDERID
    join PRODUCT P  on P.PRODUCT = L.PRODUCT
    cross apply dbo.GetGroupCode (P.FACTORY) S
where
    L.ORDERNUMBER = 'XXX/YYY-123456' and
    L.RMPHASE = '0' and
    L.ORDERLINE = '01'

For this query, SQL Server decides to call GetGroupCode function for every single value that exists in PRODUCT Table, even though the estimate and actual number of rows returned from ORDERLINE is 1 (it's the primary key):

Same plan in plan explorer showing the row counts:

Tables:

ORDERLINE: 1.5M rows, primary key: ORDERNUMBER + ORDERLINE + RMPHASE (clustered)
ORDERHDR:  900k rows, primary key: ORDERID (clustered)
PRODUCT:   6655 rows, primary key: PRODUCT (clustered)

The index being used for the scan is:

create unique nonclustered index PRODUCT_FACTORY on PRODUCT (PRODUCT, FACTORY)

The function is actually slightly more complex, but the same thing happens with a dummy multi-statement function like this:

create function GetGroupCode (@FACTORY varchar(4))
returns @t table(
    TYPE        varchar(8),
    GROUPCODE   varchar(30)
)
as begin
    insert into @t (TYPE, GROUPCODE) values ('XX', 'YY')
    return
end

I was able to "fix" the performance by forcing SQL server to fetch the top 1 product, although 1 is max that can ever be found:

select
    S.GROUPCODE,
    H.ORDERCAT
from
    ORDERLINE L
    join ORDERHDR H
        on H.ORDERID = M.ORDERID
    cross apply (select top 1 P.FACTORY from PRODUCT P where P.PRODUCT = L.PRODUCT) P
    cross apply dbo.GetGroupCode (P.FACTORY) S
where
    L.ORDERNUMBER = 'XXX/YYY-123456' and
    L.RMPHASE = '0' and
    L.ORDERLINE = '01'

Then the plan shape also changes to be something I expected it to be originally:

I also though that the index PRODUCT_FACTORY being smaller than the clustered index PRODUCT_PK would have an affect, but even with forcing the query to use PRODUCT_PK, the plan is still the same as original, with 6655 calls to the function.

If I leave out ORDERHDR completely, then the plan starts with nested loop between ORDERLINE and PRODUCT first, and the function is called only once.

I would like to understand what could be the reason for this since all the operations are done using primary keys and how to fix it if it happens in a more complex query that can't be solved this easily.

Edit: Create table statements:

CREATE TABLE dbo.ORDERHDR(
    ORDERID varchar(8) NOT NULL,
    ORDERCATEGORY varchar(2) NULL,
    CONSTRAINT ORDERHDR_PK PRIMARY KEY CLUSTERED (ORDERID)
)

CREATE TABLE dbo.ORDERLINE(
    ORDERNUMBER varchar(16) NOT NULL,
    RMPHASE char(1) NOT NULL,
    ORDERLINE char(2) NOT NULL,
    ORDERID varchar(8) NOT NULL,
    PRODUCT varchar(8) NOT NULL,
    CONSTRAINT ORDERLINE_PK PRIMARY KEY CLUSTERED (ORDERNUMBER,ORDERLINE,RMPHASE)
)

CREATE TABLE dbo.PRODUCT(
    PRODUCT varchar(8) NOT NULL,
    FACTORY varchar(4) NULL,
    CONSTRAINT PRODUCT_PK PRIMARY KEY CLUSTERED (PRODUCT)
)


texts: How to delete stored procedures from a database project and have that reflect on the server?

texts:
I have a Visual Studio database project with several stored procedures. I deleted one of them and then published the project. But the SP was still present on the published database and I had to delete manually.

I would want the VS project to be the point of truth for the database. But at the same time, I'm publishing to a production database that has data.

Is there a way to delete files in a VS database project so that those changes reflect on the published database?


texts: Restoring multiple files to multiple database with pg_restore

texts:
I have multiple dump files and want to restore them with single command. something like this

pg_restore  -h localhost -p 5432 -U postgres
    -d gl_election gl_world gl_tourism
    -c "gl_election" "gl_world" "gl_tourism"

This command doesn't work and I can't find how to do it correctly. It works fine for one file.


texts:
Recently our school district has upgraded several servers from version 2005 to 2016 and from physical servers to VM servers.

Our infrastructure team presented the VM servers for SQL Server installation with a single core.

I am attempting to explain to the head of that department that SQL Server 'hates' being on a single core and he wants to see 'proof'.  None of the data that I have presented has satisfied his need for proof and so he has asked for some articles on why a VM SQL Server won't operate efficiently on a single core.

What articles to you use for this?

Any help for me making my case would be appreciated!


texts: Copying Oracle 11g express Database from one server to another

texts:
Basically, I am a MS-SQL developer,and relatively new to Oracle.

Even after great deal of research, I was unable to find any way of copying entire database from one server instance to another.

In MS-SQL this is usually done through "backup database" and "restore database" sql query. We can even use external tools such as Management studio to create scripts.

But, How this can be done in Oracle 11g express? Any ideas??


texts: Grant rights to create a table with an identity column on another schema

texts:
I have a user in charge of deploying DDL on other schemas than its own.
This user can create a table on those schemas, it can also create sequences on those schemas.

But when I try to create a table with a GENERATED IDENTITY column on those schemas,
the user encounters an ORA-01031: privileges insuffisants

When this user try the same create table on its own schema he can do it properly.

This user was given the following rights :

GRANT CREATE ANY SEQUENCE TO ddl_role;
GRANT CREATE ANY TABLE TO ddl_role;

Here is an example of the query executed :

CREATE TABLE "sch1"."tab1"
   (        "col1" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE)
TABLESPACE "sch1_data";

The following queries work :

CREATE TABLE "sch1"."tab1"
    (        "col1" NUMBER )
TABLESPACE "sch1_data";

CREATE SEQUENCE "sch1"."seq1";

References :
https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1


texts:
I've downloaded the AdventureWorks based In-memory sample from here, and followed all the steps described in the accompanying doc. However, when I try to run the script in SQL Server Management Studio, I get the error message:

  ALTER DATABASE statement not allowed within multi-statement transaction

The error points to line 9, which is:

IF NOT EXISTS (SELECT * FROM sys.data_spaces WHERE type='FX')
    ALTER DATABASE CURRENT ADD FILEGROUP [AdventureWorks2012_mod]
    CONTAINS MEMORY_OPTIMIZED_DATA
GO

Since this is (more or less) official Microsoft documentation, I'm assuming it's something I'm doing wrong, but I can't figure out what it is.


texts: Is it there anyway to restore updated records in Microsoft SQL Server 2008 R2?

texts:
We have an test database configured with our web application. Due to internal web application error the entries were get updated in the database. Is it there any way to recover the updated records?

My problem in brief :

My web application form has five fields such as name, applicant number, date, d.o.b, address. The database also configured with same columns. Now due to internal web application's error the applicant's number has been duplicated and whenever the second number is generated (let me say the data with applicant number 12123 is placed in the database and due to duplication of 12123 the database rows get updated) it gets updated.. sadly we didn't have any backups(no backups at all).

Now we are in need to recover the data of updated data's from the database is it possible to recover updated records..

My database recovery model was preselected as full

Any third-party tools to recover the data? Or any other methods to recover the updated rows from Microsoft SQL Server 2008 R2?


texts: How can I change an existing type from "bigint" to "bigserial"?

texts:
I have a PostgreSQL table with the following structure:

I simply need to change the TYPE of prove_identity_id from bigint to bigserial.  I read the docs but wasn't able to understand how to legally achieve the change without the following error: PostgreSQL said: type "bigserial" does not exist

PostgreSQL 10.1 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version 7.0.0 (clang-700.1.76), 64-bit


texts: why an update doesn't update a row if the where clause exists?

texts:
I'm working with SQL Server 2012 SP2 and I have a problem with a stored procedure.

I have a table where we do a lot of things in a second: we insert and update rows very often.

I have a stored procedure to update a row, and it works fine most of the time, but sometimes it doesn't update a row but @@ERROR is zero. This is the update statement (@isAuto is another parameter):

-- Set new Commisioning Flag value depending on is an auto or manual read.
if (@isAuto = 1)
    set @newCommFlagValue = 20  -- Commissioning Auto
else
    set @newCommFlagValue = 120 -- Manual Commissioning.

[ ... ]

-- Get Code PK to do fast queries. @code is a stored procedure parameter
-- Serial has an unique constraint
set @codeId = (select CodeId from Code where Serial = @code);

-- Get current flag.
SET @currentCommFlag = (SELECT CommissioningFlag
                            FROM Code
                            WHERE CodeId = @codeId);

if (@currentCommFlag is null)
begin
    if (@@TRANCOUNT > 0)
        rollback transaction

    SELECT @message = 'ReadCode ' + @code + ' return -17';
    EXEC master..xp_logevent 60000, @message, informational;

    return -17 -- @code doesn't exist on Code table.
end

[ ... ]

begin try
    if (@helperCodeId is not null)
        UPDATE Code
            SET CommissioningFlag = @newCommFlagValue
                        , Source = @source
                        , UserName = @username
                        , LastChange = CAST(SYSDATETIMEOFFSET() as nvarchar(50))
                        , SentToNextLevel = 0
                        , HelperCodeId = @helperCodeId
        WHERE CodeId = @codeId
    else
        UPDATE Code
            SET CommissioningFlag = @newCommFlagValue
                        , Source = @source
                        , UserName = @username
                        , LastChange = CAST(SYSDATETIMEOFFSET() as nvarchar(50))
                        , SentToNextLevel = 0
        WHERE CodeId = @codeId

    SELECT @ErrorVar = @@ERROR
            , @RowCountVar = @@ROWCOUNT;
end try
begin catch
    SELECT @message = 'ReadCode ' + @code + ' error ' + ERROR_NUMBER() + ' - ' + ERROR_MESSAGE();
    EXEC master..xp_logevent 60000, @message, informational;
end catch
-- If it hasn't been updated...
IF @ErrorVar != 0
BEGIN

    SELECT @message = 'ReadCode ' + @code + ' error update Code';
    EXEC master..xp_logevent 60000, @message, informational;

    SET @code = NULL
    if (@@TRANCOUNT > 0)
        rollback transaction
    RETURN -1
END
else
begin
    if (@@TRANCOUNT > 0)
        commit transaction;

    if @RowCountVar = 0
    begin
        SELECT @message = 'ReadCode ' + @code + ' - no actualizado';
        EXEC master..xp_logevent 60000, @message, informational;

        return -1;
    end
    else
    begin
        set @commFlag = (Select CommissioningFlag from Code where CodeId = @codeId);

        SELECT @message = 'ReadCode ' + @code + ' - flag ' + CAST(@commFlag as varchar(2)) + ' - return 0';
        EXEC master..xp_logevent 60000, @message, informational;

        return 0;
    end
end

There isn't any transaction on the stored procedure. Maybe the table is locked by an insert or another process.

After stored procedure execution I do a select for that row and it has the previous value on CommissioningFlag column.

I'm sure this stored procedure works because it updates nearly 98% of the rows. But there is a 2% that it isn't updated.

XACT_ABORT is not set.

Do you why an Update doesn't update a row but it doesn't return an Error?

I've been investigating what it is happening and I have seen something very interesting:

Row number 5 hasn't been updated. This is the error. My program first update row 1, then row 2, etc. Each update triggers 0.15s. Update between row 4 and row 6 gets 0.30s (more or less). It seems that row 5 is updated in 0.15s without any delay but it isn't stored the change in database.


texts:
I'm trying the following:

INSERT INTO (blob_column) VALUES (HEXTORAW('very big hex here'))

But I'm getting the following error:

  ORA-01704: string literal too long

How can I workaround that? Is there any other syntax to insert a binary without converting from string?


texts: Use of temporary table variable inside a stored procedure

texts:
I am new to oracle, I was trying to create a procedure which consist of temporary table inside. I have tried this code below and getting an error in the declaration of temporary variable.

 DECLARE @TEMPDATA TABLE (TOTALQUANT numeric(18, 0));
    create or replace PROCEDURE ESTIMATION(FROMDATE IN DATE,TODATE IN DATE,USERID IN NUMBER)
    IS
    BEGIN
    SELECT
      SUM(z.QUANTITY)
    FROM x
    INNER JOIN y
    ON x.SURVEYID = y.SURVEYID
    INNER JOIN z
    ON z.SURVEYCRAFTID = y.SURVEYCRAFTID
     WHERE SURVEYDATE BETWEEN FROMDATE AND TODATE
    GROUP BY x.LCID,
      x.PERIODID,
      y.CRAFTID,
      y.GEARID;
     END ESTIMATION;

I am getting a syntax error of 'Partially recognised rule'.


texts: How to allow NULL in foreign keys of a compound primary key

texts:
I want to have NULLs in foreign keys of a compound primary key. This is an example of what I would expect to be valid data.

product_id
variant_id

123-123
ABC

123-123
NULL

456-456
ABC

I cannot figure out why the following SQL in postgres gives NOT NULL violation constraint me when inserting NULL as variant_id.
CREATE TABLE IF NOT EXISTS inventory.price (
  product_id             UUID NOT NULL, -- this has to be always to a valid product
  variant_id             UUID,          -- this could be NULL
  amount                 MONEY NOT NULL,
  created_at             TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  -- Constraints
  CONSTRAINT inventory_price_pkey PRIMARY KEY (product_id, variant_id),
  CONSTRAINT inventory_price_inventory_product_fkey FOREIGN KEY (product_id)
    REFERENCES inventory.product (id) MATCH FULL,
  CONSTRAINT inventory_price_inventory_variant_fkey FOREIGN KEY (variant_id)
    REFERENCES inventory.variant (id) MATCH SIMPLE,
  CONSTRAINT inventory_price_amount_gt_0 CHECK (amount > '0'::money)
);

And the inspection to information_schema confirms the non-nullable constraint.

column_name
column_default
is_nullable
data_type

product_id
NULL
NO
uuid

variant_id
NULL
NO
uuid

amount
NULL
NO
money

created_at
now()
NO
timestamp with time zone


texts:
How do I put current date into the where clause without having to type it in all the time?

Select SALEITEMS.SKU,
       STOCK.SHORTDESC,
       SALEITEMS.SALEPRICEEX,
       SALEITEMS.COSTPRICEEX,
       SUM(((SALEITEMS.SALEPRICEEX - SALEITEMS.COSTPRICEEX) /
             SALEITEMS.SALEPRICEEX) * 100) AS GP
  From SALEITEMS
 Inner Join STOCK On SALEITEMS.SKU = STOCK.SKU
 where SALEITEMS.FP_DATE = '13 Nov 2018'
 group by 1,2,3,4


texts: How is a table locked regarding UPDATE-SELECT queries in PostgreSQL?

texts:
Is it possible to combine these UPDATE queries?

UPDATE myTable1 SET fld1 = myTable2.value
FROM myTable2
WHERE myTable1.id = myTable2.id AND myTable2.style = 45;
UPDATE myTable1 SET fld2 = myTable2.value
FROM myTable2
WHERE myTable1.id = myTable2.id AND myTable2.style = 46;
UPDATE myTable1 SET fld3 = myTable2.value
FROM myTable2
WHERE myTable1.id = myTable2.id AND myTable2.style = 47;

And also, how is the lock on these two tables?


texts: How to reassign all privileges to another role in PostgreSQL?

texts:
On an existing PostgreSQL server let say that I have two users "Alice" & "Bob" and I want to clean up my policy because they actually have the same job and should have exactly the same privileges on every objects of the database.

To do that I will create a new group role "Crew" from which they will both automatically inherit so that in the future, administrators only need to deal with the group role "Crew" and eventually add more users to it.

However because I need to be sure that neither Alice nor Bob will by embarrassed in any way by this change I need to transfer existing  ownerships and privileges of both "Alice" & "Bob" to "Crew" while switching to this new policy.

To perform this I should:

Create the Crew role
Grant Crew to Alice & Bob (with INHERIT)
Transfer Ownership of Alice & Bob to Crew
Copy privileges from Alice & Bob to Crew (and keep more elevated privilege on conflict)

Step 3 is actually easy because there is a built-in command for that:

REASSIGN OWNED BY Alice TO Crew;
REASSIGN OWNED BY Bob TO Crew;

Step 4 however is more complicated because there is no magic command

REASSIGN PRIVILEGES FROM Alice TO Crew; --This don't actually exist too bad for me!

So what is the cleanest way transfer all privileges from "Alice" & "Bob" to "Crew" ?

My question was initially posted on StackOverflow with no answer in ~3 years and was up-voted last week. I required a migration, but StackOverflow admin pointed out that old questions can't be migrated and suggested I should repost here. I'll delete old post on StackOverflow in 1-2 weeks so that any interested user get a chance to notice the new post here. So please don't flag for duplicate on SO before discussing first


texts: KPI's don't transfer on RS.exe SSRS Server Migration

texts:
I'm attempting to do a server migration with SSRS 2016 to Power BI (March 2018 release).  I have been successful at getting my Data Sources, Data Sets, Folders, Reports, settings and subscriptions to come across.  Each of the KPI's that we set up have failed to transfer.  Other than this it's essentially the last thing that needs to transfer.
I'm using the process outlined here;
RS.exe migration process

Has anyone else run into the issue using RS.exe that none of the KPI's transfer?  Is there a workaround or some step that I have to take to get that to work?
I've been looking on Microsofts pages for a bug report but haven't been able to locate anything.

I realize that I can script copy the records out of the Catalog table but I was hoping for something a bit more out of the box.
Also, to clarify, we are using Power BI report server through our Enterprise license with SA.  I have verified that I can create a KPI manually on the new server.


texts: SQL Server Designers, Failed Saves, and Generated Scripts

texts:
I am a big fan of the simple diagramming tool that comes with SSMS, and use it frequently.  When I save changes to the model, I have it configured to automatically generate the change scripts that go along with the save.  I then save (and source control) the resulting change script.  This works great and an important piece of the process my team(s) uses.

What occasionally happens is that a save fails, and I still get the option to save my change script.  I then fix the problem and save again (which results in another change script).

I'm never clear what I need to do at this point to maintain a consistent set of change scripts.  There seems to be overlap between the two scripts (the failed and the successful), but they are not identical.

If I want to continue to use this feature, what should I be doing with the resulting script as soon as I get a failed save of the model?


texts: I'm trying to enable SQL Filestream on Windows Server 2012 and SQL Server 2016 64 bit, Management Studio 2017

texts:
The machine config is Windows Server 2012, 64 Bit SQL Server 2016, and SQL Server Management Studio 2017. When I use SQL Server Configuration Manager to enable filestream, it gives me an error that I do not know how to get around.

  There was an unknown error applying the FILESTREAM settings. check the
  parameters are valid. (0x80041008)


texts: SQL Server Backups - A couple of questions

texts:
We run our weekly backup job at 9pm on Fridays and we are experiencing a couple of issues with regard to disk space (which gets perilously low at times) and performance. We're looking at streamlining/optimising what happens and would appreciate your comments.

Specifically:

The backup process is taking about 4hrs to update statistics during
the backup. Can we safely disable this process to save time?
We're running low on disk space very regularly and wondering if we should re-jig the process. Currently it creates the backup and then deletes the previous backup and this is what is hogging disk space. Can we safely delete the previous one first and then do the backup?

Any other comments or observations would be very welcome
EDIT: Total size of SQL files on the server is around 35GB. One db is about 25GB in size while the other six share make up the other 10 or so GB.


texts: How Can I Increase Performance of This MERGE Statement?

texts:
I have a INSERT INTO/MERGE statement that is part of a stored procedure that needs to be really efficient. The execution plan shows that 90% of the procedure is due to the following statement shown below. The tricky part is I need to insert rows when they're not matched in the target table but also need to insert records when there are updated and have shares left over.

INSERT INTO dbo.#SubscriptionPeriodTemp (UserId, Shares, ProcessedShares, StartDate, EndDate)
SELECT UserId, Shares, ProcessedShares, StartDate, EndDate
FROM (
    MERGE INTO dbo.SubscriptionPeriod AS sp
    USING #ResponderOrderItem AS ob
    ON (ob.UserId = sp.UserId
        AND ob.ContentId = sp.ContentId
        AND sp.Status = @SubscriptionPeriodStatusOpen
        AND sp.EndDate > @StartDate
        AND sp.StartDate < @EndDate)
    WHEN MATCHED THEN
        UPDATE
        SET sp.Status = CASE WHEN @IsRespondingOrderOfBuyType = 1 THEN @SubscriptionPeriodStatusPurchased ELSE @SubscriptionPeriodStatusSold END,
            sp.ProcessedDate = SYSUTCDATETIME()
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (UserId, ContentId, Status, Shares, StartDate, EndDate, CreateDate)
            VALUES (UserId, @ContentId, @SubscriptionPeriodStatusOpen, ob.ProcessedShares, @StartDate, @EndDate, SYSUTCDATETIME())
    OUTPUT $action, inserted.UserId, inserted.Shares, ob.ProcessedShares, inserted.StartDate, inserted.EndDate
) AS Changes (Action, UserId, Shares, ProcessedShares, StartDate, EndDate)
WHERE Action = 'UPDATE'
    AND Shares - ProcessedShares > 0

Is there a more efficient option?

EDIT
I should have been a bit more specific. I'd like to increase the performance of a specific stored procedure in my database and the execution plan when I run the proc shows 90% of the work is being done with this section of the code. with ~30,000 records in the dbo.SubscriptionPeriod table it takes ~20 seconds. This procedure is meant to be a high throughput proc being called 1000s of times per second. I think I have a long way to go :)

Click here for the execution plan.

Click here for the database script (70MB file). Please see line 102,786 in the stored procedure named spAddOrderItem for the area of concern

Click here for the script to run the procedure with the exact data I'm using for testing.


texts: Removing a FILESTREAM windows share after uninstall

texts:
I had to uninstall my local SQL 2017 Dev edition on my home PC (Windows 10) because I changed some drive structure (including where all the system databases lived) a while ago, so I needed to reinstall SQL Server to re-create those system databases (no backups since home Dev instance) and re-set the proper drive paths. I used a configuration file for the re-install process. It seems that when you uninstall SQL Server 2017 it does NOT remove the filestream share (bug?). So when I get to the install step on filestream, it says

Windows share MSSQLSERVER already exists and cannot be created for FILESTREAM file I/O. Specify another share name.

I've tried removing this existing MSSQLSERVER filestream windows share from admin command prompt by doing:

net share MSSQLSERVER /DELETE

But I get an error:

System error 3 has occurred.
The system cannot find the path specified.

So how can I delete this existing old MSSQLSERVER filestream windows share so I can proceed with the SQL 2017 setup and configure the new filestream share as part of the install process (using the same default MSSQLSERVER share name)?


texts: As Innodb is my default engine, all my tables are innodb. But while monitoring with the help of a tool, I found a constant spike in key_write_requests and key_read_requests. These two variables are myisam variables. Will innodb internally use key cache for any process? Is it possible?


texts: Proper drive partitioning in Linux for Oracle

texts:
I have a current production physical server running on RHEL and I'm going to be building a new Oracle Linux VM to take it's place. Below is a listing of the existing physical drive partitions.

Can anyone tell me why the previous DBA would have created all of these and how you might have created these differently if it was your VM that you were going to build?

It just seems to have more partitions than what I've seen in the past.

The other server looks like this:


texts:
Lets assume we have a vary amount of clients, which could manage some stuff on our site. Each client has his own table, which is used to store data. For instance: client_1, client_2, ..., client_N. Each table could contain different amount of records, but there could be clients which could have more than a hundred of thousand records in a table.

The task is to create aggregate search on the site, which will display results from different tables for a certain search keyword. In this case we face with a problem that we need to perform up to N queries to N tables. This is not cool.

The idea is to create an index table which will collect all information from all tables and will be updated by insert/update/delete trigger on a concrete table.

I feel uncomfortable with this "index table" idea, because it could contain a huge amount of records and will be quite big overhead for our database. Is there any other approach how to build an index for horizontal shard?


texts: How do I check how many rows a query will affect before executing it?

texts:
I'm writing an application to interface with my database (SQL Server 2014 SP1 Express), and there are two sections of the application that worry me slightly. The first involves an UPDATE:

UPDATE Jobs
SET JobStatus = 'Running' --there's also an UPDATE where this is set to 'Completed'
WHERE (ExperimentID = @currentID);

And a DELETE:

DELETE from Simulations
WHERE (ExperimentID = @currentID);

These seem like fine statements at a glance, but if there's something wrong with how @currentID is passed from my application to the DB, I could possibly lose all information about which Jobs have been run, and more gravely, all of my simulation data.

How do I protect these queries from deleting/updating more than @n rows? The UPDATE should really only update one row at a time, so I'd like that to be a restriction. There's also a column in the Simulations table that could be used to restrict how many the DELETE should be getting rid of, by doing:

SELECT MAX(CycleCount)+1 AS numRows
FROM Simulations
WHERE ExperimentID = @currentID

So what do you think? How best do I sanitize these queries to prevent them from doing damage in the event that my application fails?

There are three tables in this DB: Jobs, Parameters, and Simulations. ExperimentID is a primary key for both Jobs and Parameters, and they are both the same size (each job has a corresponding set of parameters). There's more detail about Parameters in my post history if you're interested.

The Simulations Table stores results from a simulation at each step of the simulation. So the primary key is then formed not just by ExperimentID (as theres many many steps per simulation), but by the compound of ExperimentID and CycleCount.

I'm trying to account for situations where something gets corrupted in transfer (highly unlikely, but you never know), and situations where a simulation terminates prematurely (loses connection with DB, computer loses power, etc). ExperimentID in both Jobs and Parameters is determined by IDENTITY(1,1). Hope this clears up a bit of what I'm hoping to accomplish here.

@currentID is programmed as an integer. It is pulled initially from the database using:

SELECT MIN(ExperimentID) FROM Jobs WHERE JobStatus = 'ToRun'


texts: How to write database creation scripts when coding software setup?

texts:
A SQL Server database is part of an software application that clients will install.

When the clients are installing the software, the setup process needs to create an empty database.

Do I need to manually start figuring out which tables needs to be created first, and which foreign key needs to be referenced after X table is created? Or is there a tool that creates these scripts in the correct order using the existing database?


texts: Using case and group by in select statement not working

texts:
I'm using a case and group by in a select statement and I would like for the case field name (item number) to not be apart of the group by clause statement. I've calculated the invoice weight as well as the shipping weight. This is what I have thus far. The issue is when the calculation is done with the case and group by on the item number, providing multiple rows however, I would like only one row.

    SELECT TOP 10 S.SOPNUMBE, CONCAT(RTRIM(LTRIM(S.CUSTNAME)) ,' ' ,(RTRIM(LTRIM(S.[ADDRESS1]))) ,' ' ,(RTRIM(LTRIM(S.[ADDRESS3]))))AS CUSTNAME,

            S.[CSTPONBR],S.DOCDATE, CAST(round(Sum(QUANTITY),2) as numeric(12,0))TotalOnInvoice,  (CASE

            WHEN I.ITEMNMBR = 'FG-PD-TJ-PQ-KILOM-FORN'

            THEN  CAST(cast(Sum(QUANTITY * 30 ) as decimal(10,0))as float)

            WHEN I.ITEMNMBR = 'FG-PD-TJ-PQ-20.0K-FORN'

            THEN  CAST(cast(Sum(QUANTITY * 20 ) as decimal(10,2))as float)

            WHEN I.ITEMNMBR = 'FG-PD-TJ-PQ-2.25K-FORN'

            THEN  CAST(cast(Sum(QUANTITY * 2.25 ) as decimal(10,2))as float)

            ELSE CAST(cast(Sum([ITEMSHWT] * Quantity) as decimal(10,0))as float)/100

            END) AS TotalShippingWeight

            FROM [TWCL].[dbo].[SOP30200] S inner JOIN twcl.dbo.SOP30300 P ON S.SOPNUMBE=P.SOPNUMBE inner join [TWCL].[dbo].[IV00101] I on

            P.ITEMNMBR= I.ITEMNMBR WHERE CSTPONBR='1343231 '

            GROUP BY S.SOPNUMBE, S.CUSTNAME, S.[CSTPONBR], S.DOCDATE, S.[ADDRESS3], S.ADDRESS1,I.ITEMNMBR

This is the outcome I'm receiving now

This is the outcome I would like to receive.

New Query

    SELECT TOP 10 S.SOPNUMBE, CONCAT(RTRIM(LTRIM(S.CUSTNAME)) ,' ' ,(RTRIM(LTRIM(S.[ADDRESS1]))) ,' ' ,(RTRIM(LTRIM(S.[ADDRESS3]))))AS CUSTNAME,

            S.[CSTPONBR],S.DOCDATE, CAST(round(Sum(QUANTITY),2) as numeric(12,0))TotalOnInvoice, SUM (CASE

            WHEN I.ITEMNMBR = 'FG-PD-TJ-PQ-KILOM-FORN'

            THEN  CAST(cast(Sum(QUANTITY * 30 ) as decimal(10,0))as float)

            WHEN I.ITEMNMBR = 'FG-PD-TJ-PQ-20.0K-FORN'

            THEN  CAST(cast(Sum(QUANTITY * 20 ) as decimal(10,2))as float)

            WHEN I.ITEMNMBR = 'FG-PD-TJ-PQ-2.25K-FORN'

            THEN  CAST(cast(Sum(QUANTITY * 2.25 ) as decimal(10,2))as float)

            ELSE CAST(cast(Sum([ITEMSHWT] * Quantity) as decimal(10,0))as float)/100

            END) AS TotalShippingWeight

            FROM [TWCL].[dbo].[SOP30200] S inner JOIN twcl.dbo.SOP30300 P ON S.SOPNUMBE=P.SOPNUMBE inner join [TWCL].[dbo].[IV00101] I on

            P.ITEMNMBR= I.ITEMNMBR WHERE CSTPONBR='1343231 '

            GROUP BY S.SOPNUMBE, S.CUSTNAME, S.[CSTPONBR], S.DOCDATE, S.[ADDRESS3], S.ADDRESS1,I.ITEMNMBR

Error message:Cannot perform an aggregate function on an expression containing an aggregate or a subquery.


texts: Reports with recurring events DB Schema in SQL Server

texts:
I'm trying to create a scheduling reporting feature where a user can schedule a report to be received "every 1st Monday of the month" or "every last day of the month" or "every week on Tuesday".

I saw an answer that talks about this somewhat, but I think it's more complicated than I need it.  Calendar Recurring/Repeating Events - Best Storage Method.
 My reports will always be starting immediately (not starting on a specific date) and it won't be rendering to a calendar. All I need to display is what options were chosen so it can be edited (what I mean to say is I don't need to display specific instances of a scheduled report but do need to display the rule.) The rules need to be stored in the DB and I'm at a loss on where to even begin. Any help would be greatly appreciated!


texts: Should I separate frequently updated columns?

texts:
I have a users table that contains users' information and a column named credits that is frequently updated.

On index page I'm showing list of users with basic user information but I don't need credits.
On details page I'm also showing credits with user information.

Two main operations are dense in project. The first operation is the SELECT operation on index page. The second operation is the UPDATE operation on credits column. credits column is not indexed but since it's frequently updated, will it effect the indexed columns on users table? If so, I think I should separate credits column as a table and join it only when required. Doing by this, can I improve SELECT performance on index page?

Also, I'm using MySQL. What kind of table structure should I use for frequently updated columns?


texts:
I have a database that supports a web application with several large tables.  I'm wondering if partitioned tables will help speed up certain queries.  Each of these tables has a colum called client_id.  Data for each client_id is independent from every other client_id.  In other words, web queries will always contain a where clause with a single client_id.  I'm thinking this may be a good column on which to partition my large tables.

After reading up on partitioned tables, I'm still a little unsure as to how best to partition.  For example, a typical table may have 50 million rows distributed more or less evenly across 35 client_ids.  We add new client_ids periodically but in the short term the number of client_ids is relatively fixed.

I was thinking something along these lines:

CREATE TABLE foo (
    id INT NOT NULL PRIMARY KEY,
    ... more column defs here...
    client_id int
)
PARTITION BY KEY(client_id)
PARTITIONS 35;

My question.  Is this an optimal strategy for partitioning these types of tables?  My tests indicate a considerable speedup over indexing on client_id, but can I do better with some other form of partitioning (i.e. hash or range)?


texts: Preventing duplicate data when usual normalization only makes sense for some of the records

texts:
Background

I have two tables, one called issues and the other called status_checks, which both contain (among others) the field comment (VARCHAR).

An issue is raised by a user when something needs to be looked at by the support staff. A status_check on the other hand is something that is done every so often by support staff and can (but doesn't have to) result in an issue being raised.

So, for instance, a status_check might look like this:

+----+---------+---------+--------------+---------+------------+
| id | user_id | date    | satisfactory | comment |  issue_id  |
+----+---------+---------+--------------+---------+------------+
| 1  |    23   | 2014... |      1       | blah... |    NULL    |
+----+---------+---------+--------------+---------+------------+

or like this:

+----+---------+---------+--------------+-------------------+------------+
| id | user_id | date    | satisfactory |      comment      |  issue_id  |
+----+---------+---------+--------------+-------------------+------------+
| 2  |    24   | 2525... |      0       | It all started... |     25     |
+----+---------+---------+--------------+-------------------+------------+

(Typically, an issue will only be raised if it's unsatisfactory (0), but that's not required.)

Then, the issue for that second one would look something like this:

+----+---------+---------+-------------------+---------+--------------+
| id | user_id | date    |      comment      |  status | close_reason |
+----+---------+---------+-------------------+---------+--------------+
| 2  |    24   | 2525... | It all started... |  open   |     NULL     |
+----+---------+---------+-------------------+---------+--------------+

The Problem

So the question is... how can I avoid duplicating the comment field when an issue is raised by a status_check? I could leave that field NULL when an issue is raised, and simply put the comment contents in the issue table (or vice versa)... but then that makes the SQL retrieval logic more complex. Having to optionally join to another table based on a field being possibly null seems like overkill, but I can't think of a better solution.

Normally, one would normalize by putting the comment in an issue and always having an issue_id in the status_checks table, but obviously that doesn't make sense here, since most status_checks are not issues.

Possible solution?

The other way I thought of doing things seems like normalization gone wrong to me, but maybe it's actually better. That solution is to have a comments table:

+----+---------+
| id | comment |
+----+---------+
| .. | ....... |
+----+---------+

Then I would just include a comment_id in both tables, rather than the comment itself. It's still duplicating the data, but it's only duplicating an integer.

Can some other way of normalizing help me here? Or is one of the above the best I'm going to get?

EDIT: Looking at it now, I see that the user_id and date are also both duplicated, so maybe it's natural that the comment be duplicated too? I guess it's only the size of the comment that makes me queasy about duplicating it. The comments-only table looks ugly, since a comment is such a general concept and the comments by themselves would just be floating in there with no context.


texts: Storing historical records and extracting data from a given period

texts:
This a question about database modeling and PostgreSQL. This post partially answers the question but I need advice on more technical stuff: columns and query to extract the data.

I need to store administrative boundaries history within a given territory in order to be able to track boundaries movements back over the passed years.

Here is an example on how boundaries move:

Martin Land area has always existed
Bulwers Land area has always existed too
Those areas were merged together in 2015:

The new area was renamed as Martin-Bulwers
The new area kept Bulwers Land code (Martin Land code becoming deprecated)

Here is the way I store the data:

gid  | code   | name            | change_date
-----+--------+-----------------+-------------
1    | 86001  | Martin Land     | 2000-01-01
2    | 86002  | Bulwers Land    | 2000-01-01
3    | 86002  | Martin-Bulwers  | 2015-01-01

I have set a default change_date to 2000-01-01 for historical situation of an area, i.e. before the first changement occurs.

Then, I have many different cases: areas that have remained unmerged, areas that have merged with others at different dates. This can produce the following example:

CREATE TEMPORARY TABLE foo AS
SELECT gid,code,area,change_date::date FROM ( VALUES
  ( 1,86001,'Martin Land'   ,'2000-01-01' ),
  ( 2,86002,'Bulwers Land'  ,'2000-01-01' ),
  ( 3,86002,'Martin-Bulwers','2015-01-01' ),
  ( 4,86003,'Coveral Land'  ,'2000-01-01' ),
  ( 5,86004,'Big Tom Area'  ,'2000-01-01' ),
  ( 6,86005,'Small Tom Area','2000-01-01' ),
  ( 7,86004,'Tom Land'      ,'2016-01-01' )
) AS t(gid,code,area,change_date);

Then I have difficulties writing a query that could return the list of areas of a given year. I tried to rely on DISTINCT ON() clause but this does not the job I need...

For instance, the following query will return the following table:

SELECT DISTINCT ON (code) code, area, change_date

  FROM myTable WHERE change_date < '2016-01-01'

  ORDER BY code, change_date DESC ;

-- Result:

code   | area            | change_date
-------+-----------------+-------------
86001  | Martin Land     | 2000-01-01
86002  | Martin-Bulwers  | 2015-01-01
86003  | Coveral Land    | 2000-01-01
86004  | Big Tom Area    | 2000-01-01
86005  | Small Tom Area  | 2000-01-01

This is partially right as before 2016-01-01, Big Tom Area and Small Tom Area had not merged together but Martin Land and Bulwers Land had! There were 5 areas in 2014, then 4 areas in 2015 and 3 in 2016.

In fact, I would need the following result:

code   | area            | change_date
-------+-----------------+-------------
86002  | Martin-Bulwers  | 2015-01-01
86003  | Coveral Land    | 2000-01-01
86004  | Big Tom Area    | 2000-01-01
86005  | Small Tom Area  | 2000-01-01

Is there another way to store these informations or to write the query?


texts: SQL Server split mdf into multiple files

texts:
I have a database called example.mdf with a total size of 1GB which suffers from performance issues. I checked the allocated hardware and it is higher than required, I double checked the design and every thing looks normal, when I look at the .mdf files in their physical location (C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\example.mdf) I found that the database is split into multiple files example_1.mdf, example_2.mdf,  example_3.mdf, up to example_7.mdf.

I have another database file on the same SQL Server that has the same issue.

Why does this happen? Does this affect the performance? How can I prevent or stop SQL Server from splitting my .mdf files? Moreover I need to combine back the already split files.


texts: Choosing between MyISAM & InnoDB - two servers with related tables

texts:
I don't want to make a mistake.

I am about to put my website online and I admit I haven't thought about which MySQL engine to choose: MyISAM or InnoDB.

I read here and there that InnoDB should be preferred now. MySQL is version 5.1.63.

Context: Two MySQL servers, serv1 and serv2.

serv1: one table with unique id. Call this table daddy.
serv2: one table child on which id's are the same as daddy's.

I have to update (and only this) my fields contained in daddy with the fields of child, where id's are the same. Indexes are on the columns that are important for the query.

So, to max performance out, should I stick with MyISAM or convert to InnoDB? I can still do it as the database is empty.

In my case, every hundredth of a second is worth a million $ :D (pure joke of course)

May you enlighten me?


texts:
Please help me debug this:

Working:

When I execute:

set serveroutput on

declare
    middle_name     varchar2(30);
begin
    middle_name := '0000000231';

    for x in (
            select distinct tablespace_name
            from all_tables
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_tab_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_indexes
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_ind_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_lobs
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_lob_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            order by 1
        )
    loop
            dbms_output.put_line('nir2');
    end loop;
end;
/

I see:

nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2

PL/SQL procedure successfully completed.

But when I execute:

create or replace package "PKG_PROVISIONING" as
    procedure prc_create_tablespace(
        v_datadir in varchar2
    );
end PKG_PROVISIONING;
/

create or replace package body "PKG_PROVISIONING" as
    procedure prc_create_tablespace(v_datadir in varchar2)
    is
        middle_name     varchar2(30);
    begin
        dbms_output.put_line('nir');
        middle_name := '0000000231';
        for x in (
            select distinct tablespace_name
            from all_tables
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_tab_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_indexes
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_ind_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_lobs
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_lob_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            order by 1
        )
        loop
            dbms_output.put_line('nir2');
        end loop;
    end prc_create_tablespace;
end PKG_PROVISIONING;
/

I see:

exec PKG_PROVISIONING.prc_create_tablespace('test');
nir

PL/SQL procedure successfully completed.

Both are from the same user. this use has DBA privelage.


texts: Identity management in Transaction replication with updatable subscription

texts:
I have migrated my publisher DB to new server yesterday. For migration what I did was

removed subscriptions and publication completely
backed up DB on which publication was configured
restored DB to new publisher server
Ran sp_removedbreplication on newly restored DB to give a clean start
recreated publication and subscribers on my respective servers

I was able to setup replication properly and it came in sync normally. But when user tried to insert new records at subscriber I got primary key violation errors for lot of tables. Primary key is on identity columns for most of my tables and have not for replication true on both publisher and subscriber end. Upon checking the identity check constraint on subscriber I saw that it had old ranges than my current max values. It wasn't getting the new ranges even though it had automatic identity range management set. I had to manually RESEED the identity on subscriber to make it work and had to go through manual process of reseeding it for all tables. My environment is :

Replication type: Transaction replication with updatable subscription with immediate updating

1 publisher -- SQL server 2008 R2 + SP2
1 distributor -- SQL server 2008 R2 + SP2
2 subscribers -- SQL server 2008 R2 + SP2

I have below questions:

What could be the reason for automatic identity management not working?
How to fix this problem permanently? Is there a script available which can check my max values on publisher and subscribers and then RESEED it accordingly?
What is the fastest way to fix this?


texts: How to determine right value for innodb_sync_array_size

texts:
How do I determine the right value for innodb_sync_array_size in MySQL? We have version 5.7 deployed on AWS RDS with 32 vCPUs, and it sees about 26 concurrent threads on average. When I measure waits using:
 show engine innodb mutex, the total number of waits increases by about 143k / hour.
The MySQL documentation does say that a higher number will help with high concurrency and wait situation, but I cannot find any documentation on what to set this number and how to determine whether this will indeed help me.


texts: How to do a while loop to delete rows based on rowcount?

texts:
I have the following scenario:

A table containing more than a million records. I need to create a loop that deletes 10'000 rows per execution (loop) based on the column number of the rows. So if in the end of the execution the column has 500 rows for example, then i need to delete all these rows.

So I imagined that this might work:

declare COUNTER int;

while (select count(*) from Table) >= 10000)
loop
     DELETE FROM (SELECT * FROM STAGE1 WHERE STAGE1.ID <= 10000);
endloop;

It's something like this that I need, but I don't know how to increase the Counter variable in the loop. And I suppose that I need to put an IF THEN ... ELSE... statement inside the WHILE to check the SELECT * FROM STAGE1 WHERE STAGE1.ID <= 10000, and in the ELSE, delete the rest of the rows that I have: i.e the last 500 rows.

EDIT:

I'm trying to make this work:

DECLARE COUNTER INTEGER :=0;
 BEGIN
 DBMS_OUTPUT.PUT_LINE('START');
loop -- keep looping
COUNTER := COUNTER + 1;

  --do the delete 1000in each iteration
  Delete TEST where  rownum < 1000;

  -- exit the loop when there where no more 1000 reccods to delete.
  exit when SQL%rowcount <= 1000;
  commit;
end loop;
  DBMS_OUTPUT.PUT_LINE(COUNTER);
 commit;
END;

I put 10'000 rows in my TEST table. In the end the counter should be 9 and at the end of the loop the table should contain 1000 rows, but in my case the counter is 11  and the table is empty.

Why doesn't it work?


texts:
SQL Server - What is the best method to run the same query over multiple databases on multiple instances? for example we have many application databases, with the same schema that need to have information aggregated.

I currently use a script that relies on linked servers already being setup and a table that identifies which instance and database to loop through.

        /* declare some cursor variables and the dynamic sql string variable */
    DECLARE @currentDB          nvarchar(64)
    DECLARE @connectionstring   nvarchar(256)
    DECLARE @sqlstring          nvarchar(max)

CREATE TABLE #TEMP1
                (
                     currentDB      varchar(32)
                    ,field1         varchar(128)
                    ,field2         smallint
                    ,field3         datetime
                )

/* Build and open the cursor */
DECLARE connectioncursor CURSOR FAST_FORWARD
FOR
    SELECT  currentDB, connectionstring
    FROM    [admin].dbo.DatabaseList
    WHERE   dbtype = 'PROD'
OPEN    connectioncursor
FETCH NEXT FROM connectioncursor INTO @currentDB, @connectionstring

/* Start the loop through db list */
WHILE @@FETCH_STATUS = 0
    BEGIN
        /* Build and set the sql string */
        SET @sqlstring =
            '
                select
                         ''' + @currentDB + '''
                        ,field1, field2, field3
                        from ' + @connectionstring + '.dbo.table1
            '

        /* Insert the results from the iteration and fetch the next db */
        INSERT INTO #TEMP1 exec sp_executesql @sqlstring
        FETCH NEXT FROM connectioncursor INTO @currentDB, @connectionstring
    END

/* Kill the cursor */
CLOSE connectioncursor
DEALLOCATE connectioncursor

select * from #TEMP1  -- some output

Be gentle I'm a TSQL newbie!


texts: How should I configure my postgresql.conf file to backup the database once per hour?

texts:
My goal is to back up the PostgreSQL 10 database on a Ubuntu 18.04 VPS once per hour.

However, I clearly do not understand how to achieve this. Here is what I've done.

I modify the postgresql.conf file in four ways:

wal_level = replica             # minimal, replica, or logical
archive_mode = on               # enables archiving; off, on, or always
archive_command = 'cp %p /test/%f'  # copy the database file to the /test directory
archive_timeout = 3600            # force a logfile segment switch after this
                                # number of seconds; 0 disables

These are the only entries in postgresql.conf that I've changed. AIUI, I'm saying: copy the current database file every hour to /test using the same filename as the database filename.

I then restart PostgreSQL using /etc/init.d/postgresql reload (I've also tried restart).

However, nothing ever copies to /test. Also, the /etc/postgresql/10/main/pg_wal directory does not contain any files.

What am I doing wrong? Maybe other entries in the file need to be changed? Thanks.


texts: Are there any known problems caused by having NLS_LENGTH_SEMANTICS set to CHAR on an Oracle database?

texts: I've always thought that anyone using a database where NLS_LENGTH_SEMANTICS set to BYTE when using a multi-byte character set needed their heads examined, but recently a customer of ours said that they had experienced problems applying Oracle patches on databases where NLS_LENGTH_SEMANTICS is set to CHAR. Is there anything to this? Is it a known problem?


texts: Oracle Enterprise Manager font problem, some text is displayed in Chinese and with squares

texts:
In Oracle Enterprise Manager 11g database control in the following section:

  Setup->Monitoring Templates->"Choose template name and click Edit
  button"->Policies tab

Texts are displayed in Chinese language and squares.

Like this:

  䅵摩琠䙩汥⁄敳瑩湡瑩潮

  䝲慮瑩湧⁓䕌䕃吠䅎夠呁䉌䔠偲楶楬敧

  䑩獫*⁇*牯異⁃潮瑡楮猠䑩獫猠潦⁓楧湩晩捡湴汹⁄楦晥牥湴⁓楺敳

How to solve? I want these words to see in English. Please help.

  I found now that some words contains question marks

Thank you.


texts: Options for syncing from a MASTER table to a COPY table?

texts:
I have a MASTER table and a COPY table in separate databases (both are Oracle 12c).

I want to sync the MASTER table with the COPY table on a weekly basis.

I can think of a few options for syncing from MASTER to COPY:

Sync All: Delete all from COPY and insert all from MASTER.
Period-based: Replace (or add) rows in COPY where sysdate - editdate < 7 in MASTER.
Period-based with redundancy: Replace (or add) rows in COPY where sysdate - editdate < 31 in MASTER.

This applies in situations where rows in previous syncs have failed. The failed rows in MASTER will have been corrected, but we still need to sync them to COPY in future syncs.

Manual flag: Users manually flag rows in MASTER that need to by synced to COPY. The sync process resets the flag in MASTER once the edits have been synced to COPY.
Date comparison: If date in MASTER is newer than date in COPY then sync those rows.

Are there any other options or details that I've missed?


texts: MongoDB: removeShard when dbsToMove is an empty array

texts:
Background:

Two empty shards with no explicit sharding scheme -  shard0000 and shard0001.
mongorestore a single database, all of which defaults to shard0000
Add a shard key to one collection and let the balancer move 1171 chunks to shard0001, leaving 1171 on shard0000. (So far, so good.)
Manually move 2 chunks from shard0000 to shard0001, one of which happens to contain the highest values of the shard key. UPDATE: I had suspected this is where things got tricky, but I repeated the entire procedure described here without this manual move, and got the exact same results.

The result, according to sh.status({"verbose":true}) is both chunks simply having their assigned shard names changed from "shard0000" to "shard0001", including the row at the end of the output with { "key_field" : "highest_value" } -->> { "key_field" : { "$maxKey" : 1 } } on shard0001 Timestamp(1173, 0).
Totals are shard0000=1169 and shard0001=1173, as expected.
The only database listed has shard0000 as its primary.

Next steps:

Attempt to move all of the data back to shard0000 using db.runCommand({removeShard:"shard0001"})

The initial return value, as well as all subsequent reruns of the above command, documented to give the status of the removeShard operation, is

{
        "msg" : "draining ongoing",
        "state" : "ongoing",
        "remaining" : {
                "chunks" : NumberLong(1173),
                "dbs" : NumberLong(0)
        },
        "note" : "you need to drop or movePrimary these databases",
        "dbsToMove" : [ ],  <-- THE PROBLEM
        "ok" : 1
}

Considering that there is only one database, attempt to movePrimary it just in case. As expected, this returns { "ok" : 0, "errmsg" : "it is already the primary" }
sh.status() shows shard0001 as "draining" : true.
The "remaining" count does not change.
Moving back the two manually moved chunks to shard0000 does not affect the results.
There are no outstanding locks. db.locks.find({"state":2}) returns nothing.
The mongos log shows these two relevant operations, then nothing. (The shards' logs have no trace of this, with default verbosity.)

SHARDING [conn5] going to start draining shard: shard0001
SHARDING [conn5] about to log metadata event into changelog: { _id: ..., server: ..., clientAddr: ..., time: ..., what: "removeShard.start", ns: "", details: { shard: "shard0001" } }

No chunks are jumbo. db.chunks.find({shard: "shard0001","jumbo":true}) returns nothing.
db.runCommand({flushRouterConfig:1}) does not affect any of the above.

Question:

Is there really something I need to movePrimary here, despite the empty array for dbsToMove? If so, what is it and where did it come from? Have I made some more basic error in moving all of the data back to one shard?

Disclaimers:

There are several questions on SO about troubles with removeShard (example), most of which do not address specifically the case of dbsToMove: [ ].
The one that does (this one) has no answers, and I am hoping that by providing more details I can solicit one.
The documentation says "nor can you unshard a collection", which is essentially what I'm trying to do, but I wouldn't necessarily conclude from that that I can't drain a shard.
I found this bug ticket, but it doesn't seem directly related to this problem.

MongoDB 3.2.9


texts: Can I be notified of a rollback in my DDL Trigger?

texts:
I'd like DDL changes in SQL Server to trigger an external process (outside of SQL). The problem is, is there a way for me to be notified via my trigger that a rollback occurred?

For instance, with the following:

BEGIN TRAN

CREATE TABLE [dbo].[TestRollbackTable](
    col1 varchar(100) NOT NULL)

ROLLBACK

A DDL trigger on CREATE_TABLE would be called, but nothing seems to be called with the ROLLBACK. And since my trigger is calling an external process, it is not automatically subjected to the rollback. How can I catch the fact that the ROLLBACK negated that action?


texts: Problems splitting and aggregating data in SQL

texts:
Hello I have the following data in csv file:

OrderID, OrderType, Amount, Price, Time
      1,       Buy,     10,     1, 10/01/2018 10:30 AM
      2,       Buy,      2,     1, 10/01/2018 11:30 AM
      3,      Sell,      5,     3, 10/01/2018 01:30 PM
      4,      Sell,      3,     6, 11/01/2018 10:30 AM
      5,       Buy,     13,     2, 11/01/2018 11:30 AM
      6,      Sell,      3,     3, 12/01/2018 10:30 AM
      7,      Sell,     11,     2, 13/01/2018 10:30 AM

What I need to do is I need to aggregate the records, so that I have matching amounts for buy and sell.

So I need to split the buy or the sell in order to have matching amounts.
like this:

ver1: (do nto modify buy records, just sell)

for OrderID=1 I have 10 buy but OrderID=3 (first sell) has just 5.
So what I have to do is search next sell (ID 4) 3, now the sum is still under 10, so I go to the next. If the next is taken at full I will have sum over 10, so I need to split OrderID=6 into two lines, one with the remaining to the 10 (2) and the second with the remaining (3-2=1). Then I look into OrderID=2. Amount is 2, from OrderID=6 I have 1 remaining that I need to use + 1 from (ID 7).

ver2: (keep sell orders and split buy)

In this case OrderID=1 has to be split into 5,3,2 (to be used in OrderID= {3,4,6})
Then OrderID=2 has to be split into two for (OrderID={6,7})
And OrderID=5 is ok to be used in full,s o no modification needed...

Is there a way to do all this in normal SQL or do I need some external program to manipulate the data accordingly?

My data is coming in csv, but I can import it in MySQL.
The program that I use to read the csv with sql is QlikView, but I am looking for general solution for general SQL or MySQL.


texts: DATABASE lacks a quorum of nodes for high availability

texts:
Just after setting up successfully alwayson - availability group,
testing the failover,

I get this error message:

TITLE: Microsoft SQL Server Management Studio

Failed to resume data movement in database 'APCORE', which resides on the availability replica 'MY_SERVER' in the availability group 'AG_TS'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17289.0+((SSMS_Rel_17_4).181117-0805)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&LinkId=20476

ADDITIONAL INFORMATION:

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

Unable to access database 'APCORE' because it lacks a quorum of nodes for high availability. Try the operation again later. (Microsoft SQL Server, Error: 988)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.1000&EvtSrc=MSSQLServer&EvtID=988&LinkId=20476

BUTTONS:

OK

what I have is a Quorum for 3 nodes AlwaysOn Availability Group

my windows server 2016 cluster has a quorum disk set up:

There is a related question here:

Failover Cluster Instance work without quorum

What does that message mean?


texts:
Table dating_users:
 user_id | username
---------+----------
      11 | taimoor
      13 | jacob
      14 | pi
(3 rows)

Table dating_likes:
 like_id | user_id | liked_user
---------+---------+------------
      54 |      11 |         13
      56 |      13 |         11
      57 |      11 |         14
(3 rows)

I want to find likes where a user has liked someone, and that someone has liked them. Here's my query:
select like_id from dating_likes where user_id = 11 and exists(select like_id from dating_likes where liked_user = 11);
Result:
 like_id
---------
      54
      57
(2 rows)

That should have resulted with only the first row, since 54 is a like where the user_id = 11 and there is a like 56 where liked_user = 11. Like 57 should not show up, since it's not mutual - all that's true is that the user_id is 11.


texts: How (and why) does TOP impact an execution plan?

texts:
For a moderately complex query I am trying to optimize, I noticed that removing the TOP n clause changes the execution plan.  I would have guessed that when a query includes TOP n the database engine would run the query ignoring the the TOP clause, and then at the end just shrink that result set down to the n number of rows that was requested.  The graphical execution plan seems to indicate this is the case -- TOP is the "last" step.  But it appears there is more going on.

My question is, how (and why) does a TOP n clause impact the execution plan of a query?

Here is a simplified version of what is going on in my case:

The query is matching rows from two tables, A and B.

Without the TOP clause, the optimizer estimates there will be 19k rows from table A and 46k rows from table B.  The actual number of rows returned is 16k for A and 13k for B.  A hash match is used to join these two results sets for a total of 69 rows (then a sort is applied).  This query happens very quickly.

When I add TOP 1001 the optimizer does not use a hash match; instead it first sorts the results from table A (same estimate/actual of 19k/16k) and does a nested loop against table B.  The estimated number of rows for table B is now 1, and the strange thing is that the TOP n directly affects the estimated number of executions (index seek) against B -- it appears to always be 2n+1, or in my case 2003.  This estimate changes accordingly if I change TOP n.  Of course, since this is a nested join the actual number of executions is 16k (the number of rows from table A) and this slows down the query.

The actual scenario is a bit more complex but this captures the basic idea/behavior.  Both tables are searched using index seeks.  This is SQL Server 2008 R2 Enterprise edition.


texts: Multiple disparate functions within stored procedure

texts:
Developers at my company have this habit of putting multiple semi-related functions within a stored procedure, calling the appropriate one based on a passed-in parameter, e.g.:

if @function = 1

begin
    truncate table holiday_list
end

if @function = 2

begin
    -- huge SQL statement populating the holiday_list table
end

Is this a bad practice? It seems to me that SQL Server would not create a good execution plan, as the code branches into two disparate paths (this is a simple example, though many times there will be 4 or 5 such branches, doing unrelated tasks except for sharing the same base table).


texts: Why doesn't my profile of my procedure seem to account for the duration?

texts:
I'm running 10.3.10-MariaDB-1:10.3.10+maria~bionic locally in Docker and I'm profiling a procedure call like so via adminer:

SET SESSION profiling = 1;
CALL `add_user_to_role_in_unit`;
SHOW PROFILES;

However the duration of call 2.294 s doesn't tally of the duration of the profile: 0.00033988

What am I missing?

Here's a supplementary screencast of my issue: https://media.dev.unee-t.com/2018-12-14/duration.mp4


texts: Many-to-many relationship with attributes

texts:
I'm working on a project where I need a book database. The problem is as follows:

A book can be related to multiple people (authors, editors, traslators, etc.). Then a person may contribute to different books in different roles.

Book1 PersonA (author), PersonB(editor)
Book2 PersonC (author), PersonB(translator), PersonD(editor)
Book3 PersonE (author), PersonA(editor)

I have a BOOKS table (bookID, bookTitle), a PEOPLE table (personID, personName) and a ROLES table (roleID, roleName). Then I need a way to relate these tables and then query them. I thought of a junction table that has the following row structure:

bookId personID roleID

Is this the optimal way of handling this problem (when it comes to insertion, update and deletion of entries on all these tables)? If not, what are the alternatives?

Note: I'm using SQLite3.

PS: I hope my use of the terminology is correct.


texts: PostgreSQL for running Tests (Durability is not important)

texts:
Our Django tests are getting slow, and a lot of time is spend in cursor.execue().
There are roughly two ways now: Rewrite the tests to mock the ORM, or make PostgreSQL faster.
This question is about "make PostgreSQL faster".
What can I do to improve PostgreSQL speed if D (Durability) of ACID is not important. Everything could be kept in RAM.
Constraint: This question is about what you can do without modifying the Django/Python/SQL code.


texts:
I have a table with below data.

columnA  columnB
ONE      value 1
ONE      value 2
ONE      value 3
TWO      value 1
TWO      value 2
THREE    value 1

Need help in creating a query which gives below

COLUMN X   COLUMN Y
ONE         value 1, value 2, value 3
TWO         value 1, value 2
THREE       value 1


texts: Combine multiple row's records in one row with MS Access Query?

texts:
I have a table (name- tbl2) with 21 column but I want to combine multiple rows records in a row by using a unique reference number as below:

Table Field and Records Type

Expected Result:

Please help me to resolve this issue.


texts: Migrating Pure PHP Site with Associated Database To New Server

texts:
First, let me say I'm not a DBA or network admin, but have moved large scale CMS websites from host to host. But, I have never moved a pure PHP-coded database driven website from one host to another.

What I have done is moved the entire site including the level above 'html' with the include folder with config.php, database.php, main.php etc. I have imported database(s) and have proper credentials for access.

But, when I try to load a file from the site (ex. accounts/index.html) I just get partial code returned with no css styles or anything that controls the site.

Could someone point me in a direction to be able to start to sync this site back? Like I said, getting raw output with viewing files and can't even get CSS to load site colours and styles.

I'm a newb when it comes to this aspect of things and appreciate any guidance.


texts: Why does my SSIS package produce different results when executed from a maintence plan

texts:
I have a SSIS package that picks up a file in one network location transforms the data and drops off files ready for import in another network location. The package reads a file and writes a new file or overwrites an existing file in another location. The package works as expected from visual studio and when executed manually (double click in explorer). When run from a maintenance plan or scheduled through the SQL server agent job menu the file is always blank. The process will overwrite existing files and create files when no file exists. I believe this indicates it is not a permissions issue.

Running MSSQL Server 2008r2
SQL Server Agent is running as NT AUTHORITY\System with privileges provided to the computer$ on the network share.

Any ideas what is going on and why?


texts:
I would like to move a SQL Server database from a shared configuration with the web-server to it's own dedicated box. My current budget will allow me to put 4 disks together in an array with a single hot spare. I'd like to stretch to 8+ drives but the costs are a little out of my budget right now (and probably somewhat overkill).

So my question is, what would be the optimum configuration for SQL Server 2012 when limited to 4 disks? The database is around 29 GB and is growing around 250-500 MB per month. The database will typically deliver 80% reads to 20% inserts/updates/deletes.

I understand from researching this subject that my options are as follows:

RAID 5 with all functions on a single volume.
Dual RAID 1 with OS, Backups and system dbs on one volume and database + log files on the other.
RAID 10 with all function on a single volume.

I'm looking for a solution that will give me reasonable performance but won't obliterate the array if a single drive fails (which I understand is quite common with SSDs).

Current Hardware ------------------

HP ProLiant DL360 G7 1 x Xeon E5640 / 2.66 GHz - RAM 12 GB - 2 x 300GB Pluggable SAS SFF 10,000 rpm disks in RAID 1.


texts: prepare statement not getting deallocated

texts:
I am completely new to MySql and trying to use PREPARE STATEMENT, but for unknown reasons previous working query is getting cached.

SP Code :

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_filter`(
        p_membership_status varchar(10),
        p_country_iso_code varchar(2),
        p_subdivision_1_iso_code    char(5),
        p_districtname  varchar(100),

        p_mobile_req tinyint,
        p_most_ordered_items_req tinyint,
        p_emailid_req tinyint,
        p_foreign_members_req tinyint
    )
BEGIN

    /* if we dont set below variables to null, problems persists */
    set @whereclause = null;
    set @query = null;

    if(p_mobile_req = 1) then
        begin
            set @selectcolumn = 'mobile';
            if(p_country_iso_code is not null) then
            begin
                set @whereclause = concat('country_iso_code = ', "'" , p_country_iso_code , "'");
            end;
            end if;
        end;
    end if;

    if(@whereclause is not null) then
    begin
        set @query = concat('select ', @selectcolumn, ' from member where ', @whereclause);
        PREPARE stmt1 FROM @query;
        EXECUTE stmt1;
        DROP PREPARE stmt1;
    end;
    end if;
END

For example, if I call above sp like

call sp_filter(null, 'in', null, null, 1, 0, 0, 0);
call sp_filter(null, null, null, null, 1, 0, 0, 0);
// result of this 2nd call will present result of 1st call as if second parameter is now 'in' instead of null

I tried countless times, but of no use. So, I set both variables to null at the start and problem was solved. No idea why ?

What could be the reason behind this behavior ?


texts: What could be the cause for my high disk IO latency (from sys.dm_io_virtual_file_stats DMV)?

texts:
These are the results from querying the sys.dm_io_virtual_file_stats DMV. (I add some calculated fields for clarity).

I'm finding certain basic operations in Database 7 are bottlenecking core operations I have running in Database 6.

For example, I have a table in Database 7 that isn't used anywhere, and has a lot of rows (about 300 million). In Database 6, a bulk insert of hundreds of thousands of records is happening every hour throughout the entire day.
If I run a simple SELECT COUNT(1) FROM Database7.dbo.UnusedTableInDatabase it takes about 10 minutes to return, and it appears to eat up a lot of the server resources, IO in particular. My continuously running bulk insert job on Database 6 crawls to almost a halt and a backlog starts to build up.

One thing I know is Database 7 has it's data and log files on the same logical drive.
Database 6 is appropriately setup so that it's data and log files are on separate drives.

Where should I start digging next to try to resolve these issues?

UPDATE: My server is hosted on AWS with the Provisioned IOPS SSD (io1) drive. It sounds like I'm supposed to be seeing up to 64,000 IOPS per volume, but I'm seeing about 2,000 IOPS right now, whereas another server I have on AWS with the General Purpose SSD (gp2) drive is giving me about 6,000 IOPS.


texts: Oracle Best-Buyer-Customer is Wholesale or Retail buyer

texts:
I have data warehouse with Fact table Orders, and CustomerDimension, and TimeDimention, and BookDimension.

CREATE TABLE DIM_CUSTOMER(
    DIMCUSTID NUMBER(6) PRIMARY KEY,
    CustId    NUMBER(4)   NOT NULL,
    L_Name    VARCHAR(30) NOT NULL,
    F_Name    VARCHAR(30) NOT NULL,
    City      VARCHAR(20) NOT NULL,
    Country   VARCHAR(20) NOT NULL
)

CREATE TABLE TDIM_TIME(
    TIMEID   DATE PRIMARY KEY,
    DAY      NUMBER(2) NOT NULL,
    MONTH    NUMBER(2) NOT NULL,
    QUARTER  NUMBER(2) NOT NULL,
    SEMESTER NUMBER(2) NOT NULL,
    YEAR     NUMBER(5) NOT NULL
)

CREATE TABLE TDIM_BOOK(
    ISBN        NUMBER  PRIMARY KEY,
    TITLE       VARCHAR(150) NOT NULL,
    EDITIONNO   NUMBER(2) NOT NULL,
    PRICE       NUMBER(5) NOT NULL,
    AUTHORS     VARCHAR(400) NOT NULL,
    PUB_NAME    VARCHAR(50) NOT NULL,
    CITY        VARCHAR(30) NOT NULL,
    COUNTRY     VARCHAR(30) NOT NULL
)

CREATE TABLE TFACT_ORDER(
    FACTORDERID NUMBER(9) PRIMARY KEY,
    DIMCUSTID   NUMBER(6) NOT NULL,
    ISBN        NUMBER    NOT NULL,
    TIMEID      DATE      NOT NULL,
    QUANTITY    NUMBER(3) NOT NULL,
    PRICE       NUMBER(6) NOT NULL,
    CONSTRAINT FK_TDIMBOOK FOREIGN KEY (ISBN)
    REFERENCES hwu.TDIM_BOOK(ISBN),
    CONSTRAINT FK_TDIMCUST FOREIGN KEY (DIMCUSTID)
    REFERENCES hwu.TDIM_CUSTOMER(DIMCUSTID),
    CONSTRAINT FK_TDIMTIME FOREIGN KEY (TIMEID)
    REFERENCES hwu.TDIM_TIME(TIMEID)
)

I need to find if my Best-Customer (Who had buy the most quantity of Books) had orders a lot of orders with low price (Retail Buyer), or he had order a few orders with high price (Wholesale Buyer).

How can I achieve this in Oracle?

Edit:

Lots of orders : 500 order in year

Few orders: 10 order in year

High price: 50000$ or more; for each order of him

Low price: 100$ or lower; for each order of him


texts: Join query returning 0 rows - impossible where

texts:
My query is performed from a php web request using a PDO prepared statement.

My desired output is to list the top 10 brands which have the same transaction id as the 'selected' brand :brand by order of frequency.

I initially started with the first query below on a single table with small sample data consisting of 2 columns (txn_id, brand).

My goal/intention now is to make this happen with the actual data, which is split into 2 tables, 1_txns and 2_products,, which have a common field (sku).

Query #1: Queries 1 table, returns desired results. (top 10 brands which share a transaction id with :brand

SELECT brand
FROM transactions
WHERE
id IN (SELECT id FROM transactions WHERE brand = :brand)
AND brand <> :brand
GROUP BY brand
ORDER BY COUNT(*) DESC
LIMIT 10;

I tried to modify my existing query to use the actual data, and the result returned 0 rows. I used EXPLAIN, and an Impossible WHERE was noted. Obviously I'm by no means an expert, and this is actually my first go at handling joins, and subqueries for filters, with MySQL. If I can get a grip on things like this, then I will look to move some of my excel->access solutions to web based. Many Thanks in Advance

SELECT `2_products`.`brand`
FROM `2_products`
RIGHT JOIN `1_txns`
ON `2_products`.`sku`=`1_txns`.`sku`
WHERE `1_txns`.`txn_id` IN (SELECT `1_txns`.`txn_id`
                      FROM `1_txns`
                      WHERE `2_products`.`brand` = :brand)
AND `2_products`.`brand` <> :brand
GROUP BY `2_products`.`brand`
ORDER BY COUNT(*) DESC
LIMIT 10;

EDIT: for Derek Downey

Plain english.  this query is supposed to return a list of the top 10 brands that are purchased with the selected brand.
The data is as follows:
1_txns

(txn_id, sku, salesamount, various other columns like qty etc)
1, 12345, 5
1, 12346, 10
2, 12345, 5
2, 12347, 10
3, 12345, 15
3, 12346, 30
4, 12346, 20
4, 12347, 10

2_products

(sku, brand, various other columns)
12345, Nike
12346, Adidas
12347, Reebok

The desired output is:

If Nike is the selected brand
Adidas
Reebok

If Adidas is the selected brand
Nike
Reebok

If Reebok is the selected brand
Something that will deal with a tie and still list both in alphabetical order


texts: Sort by ID when ID < 1000, otherwise sort by name

texts:
I have a table of Ids and Names. Where the Id is less than 1000, I want to order by Id. Otherwise sort by Names. For example, if I have:
Id   Name
2    A
1    B
3    C
1111 Z
2222 Y
3333 X

The result should be (first three rows sorted by Id, last three rows sorted by Name):
Id   Name
1    B
2    A
3    C
3333 A
1111 B
2222 C

I came up with this:
SELECT *
FROM table1
ORDER BY
  CASE Id
    WHEN Id < 1000 THEN
        Id
    ELSE
        Name
  END
  ASC
;

Which isn't really working - for the following table:
Id   Name
1    M
2    P
3    A1
4    C
5    A2
6    T
1009 A2
1011 A1
1010 A3

I get the following output:
1    M
3    A1
1011 A1
5    A2
1009 A2
1010 A3
4    C
2    P
6    T

What's happening here? And what's the correct query I'm after?


texts:
I am using SQL Server 2012 and have a db with around 2 million rows. I need to figure out how to calculate a rolling sum over a 30 day range and return any results over a set amount (amt_pur). These are transaction orders so the id number (ID_NO) is the same but transaction date(TD) varies by minutes to many years at times. So if a customer ordered 10 items within any 30 day period I need those results. I tried something like this and many googled variations:

select id_no, td,
sum(amt_pur) as amtpur from db1 where td between td and dateadd(day,30,td)
group by id_no, td, amt_pur having sum(amt_pur)>10
order by amtpur desc

This does not get me rolling type calculation results. I have a specific ID I can verify this with so I know I'm not getting the right ones. Please help!


texts:
We've a item table with a delimiter that contain its parent's tree

item,Level,parent,parent_tree,cost
a,0,null,a|,0
b,1,a,a|b,10
c,2,b,a|b|c,10
d,2,b,a|b|d,10
e,1,a,a|e,10
f,2,e,a|e|f,10

We currently using

select sum(cost) from items where parent_tree like 'a|%'

In order to get all the sub-trees costings without complex joining of tables, however, performances of the full-text search has slowed down significantly.

Is there anyway to create a index for delimiter values? such that our trees could increase up to 20 levels


texts: How to implement High Availability for all nodes in a sharded database?

texts:
I'm doing some research into implementing sharding for one of my large (~2 TB) production databases.
Currently the database has HADR via a multi-replica AlwaysOn Availability Group.
Since sharding a database requires it to be broken out into multiple smaller pieces across multiple nodes, is it possible to leverage AlwaysOn Availability Groups with the sharded database?...would I need to create a failover cluster that duplicates every node in my sharded database to multiple secondary replicas to be able to do so?
Is there a better way to implement High Availability for a sharded database? (I know sharding inherently has a level of High Availability, because if one node goes down the rest of the database is still accessible, but I need a solution that is fully High Availability so that no part of the database is ever inaccessible.)


texts: Reasons why batch generates more than one query plan?

texts:
I know that a single batch can have more than one query plan but want to learn some more about the cases when more than one execution plans are generated. Is it because of a batch length or the use of some statements? Is there maximum limit for query plans for a single batch?

Thanks


texts: How to connect to SQL Server using sqlcmd on Linux?

texts:
I have set up the Microsoft ODBC Driver 13 for SQL Server on an Ubuntu 16.04 machine. I am now trying to test a database connection, that includes both the server name and an instance name.

The following both work fine from a Windows machine:

sqlcmd -S "SERVERNAME\INSTANCENAME" -U User -P pwd -d DatabaseName -Q "SELECT TOP 5 Id FROM dbo.MyTable;"
sqlcmd -S SERVERNAME\INSTANCENAME -U User -P pwd -d DatabaseName -Q "SELECT TOP 5 Id FROM dbo.MyTable;"

However none of them work on Linux - they all return the error message below. The error implies the server cannot be found:

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : MAX_PROVS: Error Locating Server/Instance Specified [xFFFFFFFF]. .
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

I have also tried escaping the backslash with the same error (and with single and double quotes).

sqlcmd -S "SERVERNAME\\INSTANCENAME" -U User -P pwd -d DatabaseName -Q "SELECT TOP 5 Id FROM dbo.MyTable;"
sqlcmd -S SERVERNAME\\INSTANCENAME -U User -P pwd -d DatabaseName -Q "SELECT TOP 5 Id FROM dbo.MyTable;"

The driver is working however as I can connect to a different server with no instance name:

sqlcmd -S SERVERNAME -U User -P pwd -d DatabaseName -Q "SELECT TOP 5 Id FROM dbo.MyTable;"

I am fairly certain it is due to the backslash, but cannot find a way to get around this.


texts: How to merge consecutive record pairs over a table?

texts:
I have a table where I need to merge consecutive record pairs (when ordered by id, data and from). Note that for pairs being merged, the first row's prevfrom is NULL and the second row's nextto is NULL.

id  data from        to          prevfrom    nextto
1   a    2015-01-01  2015-01-05  NULL        2015-01-10
1   a    2015-01-10  2015-01-14  2015-01-05  NULL
1   a    2015-01-15  2015-01-20  NULL        2015-01-22
1   a    2015-01-30  2015-02-04  2015-01-25  NULL
2   c    2015-01-01  2015-01-05  NULL        2015-01-10
2   c    2015-01-05  2015-01-10  2015-01-01  NULL

So in this case, I would want to merge rows 1&2, 3&4, and 5&6, and discard helper columns prevfrom and nextto. As a result of the merge, each row would have from value of the first record in the pair, and to value of the second record in the pair. Like so:

id  data from        to
1   a    2015-01-01  2015-01-14
1   a    2015-01-15  2015-02-04
2   c    2015-01-01  2015-01-10

I can do this by doing a SELECT and a JOIN inside a WHILE loop until I run out of record pairs (pseudo code):

WHILE table exists DO:
BEGIN
  INSERT INTO #temp
    SELECT TOP 2 * FROM table ORDER BY [id],[data],[from]  --get a pair
  INSERT INTO result            --join #temp with itself to merge rows
    SELECT t1.[id],t1.[data],t1.[from], t2.[to]
    FROM #temp AS t1
    JOIN #temp AS t2
      ON t1.[from]<t2.[from]    --later record joined on earlier record
  DELETE FROM table             --update original table
    IF record exists IN #temp
  TRUNCATE TABLE #temp          --empty temp table for next insertion
END

However I feel that doing an ORDER BY in every iteration is inefficient. Is there a simpler way to do it?


texts: What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?

texts: I have an indexed column that stores an MD5 hash.  Thus, the column will always store a 32-character value.  For whatever reason, this was created as a varchar rather than a char.  Is it worth the trouble of migrating the database to convert it to a char?  This is in MySQL 5.0 with InnoDB.


texts: Error 1046 Mariadb: No database selected

texts:
GRANT ALL ON my-database.* TO my-user@10.0.0.1 IDENTIFIED BY 'password';
ERROR 1046 (3D000): No database selected

Ok, I have created a database with a "-" on the name (did the same thing on the user)... then when I try to set the grants on it the database wont work.

If I use the base the error is different

MariaDB [(none)]> use my-base
Database changed
MariaDB [my-base]> GRANT ALL ON 'my-base'.* TO 'my-user'@'10.0.0.1' IDENTIFIED BY 'password';
ERROR 1064 (42000): 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 ''my-base'.* TO 'my-user'@'10.0.0.1' IDENTIFIED BY 'password'' at line 1
MariaDB [my-base]>


texts: Where can I find the reason why TDE encryption Scan aborted?

texts:
When encrypting large database > 10TB . I had to issue --alter database  set encryption on several times , but the encryption starts and fails with the same message in the error log. where can i find why the encryption scan failed ?

Error Log entry

  Database encryption scan for database  was aborted. Reissue
  ALTER DB to resume the scan. This is message in the sql server error
  log.

The  Database compatibility level is 130

To be clear when I run the alter db <dbname> set encryption on command the encryption starts progresses by small percent and fails immediately. currently it is at 0.4716863 percent . when it fails it fails with encryption_state = 2 and percent_complete=0

SQL Server version

Microsoft SQL Server 2016 (SP2-CU7-GDR) (KB4505222) - 13.0.5366.0 (X64) Jun 15 2019 08:22:25 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: )

Thanks


texts: How can I create a TRIGGER in MySQL from this Oracle syntax?

texts:
I'm teaching myself RDMSs through a text book. I'm used to using MySQL so I'm sticking to it, but the book uses Oracle.

I'm trying to create a trigger in MySQL with the Oracle command

CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDER
AFTER INSERT OR UPDATE OF P_ONHAND ON PRODUCT
BEGIN
    UPDATE ON PRODUCT
        SET P_REORDER = 1
        WHERE P_ONHAND <= P_MIN;
END;
/

When I run the above, I get

  Error code 1064, SQL state 42000: 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 'TRIGGER TRG_PRODUCT_REORDER AFTER INSERT OR UPDATE OF P_ONHAND ON PRODUCT'

Basically in the PRODUCT table, if an INSERT or UPDATE is made, and the quantity on-hand (P_ONHAND) is less than the product minimum (P_MIN), a flag should be raised on P_REORDER.

I'm not sure how to go about putting this into MySQL syntax without separating the INSERT OR UPDATE, which would break the requirement of the command. Is this even possible in single TRIGGER with MySQL?

This is the best I can think of after going through the MySQL manual

delimiter //
DROP TRIGGER TRG_PRODUCT_REORDER;
CREATE TRIGGER TRG_PRODUCT_REORDER
AFTER INSERT OR UPDATE ON PRODUCT
FOR EACH ROW
    BEGIN
        IF NEW.P_ONHAND < P_MIN THEN
            SET P_REORDER = 1;
        END IF;
    END; //
delimiter ;

But I still get the error

  Error code 1064, SQL state 42000: 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
  'CREATE TRIGGER TRG_PRODUCT_REORDER AFTER INSERT OR UPDATE ON PRODUCT FOR EACH R' at line 2'

Any help would be appreciated.


texts: SQL Server Agent maintains thousands of sleeping connections

texts:
In a recent monitoring session I noticed that one of our main MSSQL 2014 databases had an increasing number of open connections.

It started with more than 1000 open connections, and now after two weeks it has increased to more than 2600. One would think that this would be improper connection handling in one of our applications, but out of the 2610 open connections, 2600 belong to SQLSERVERAGENT. When inspecting the result of sp_who2, almost all SQL Server Agent connections are program name 'SQLAgent - Temporary Worker', sleeping and 'awaiting command'.

I've searched for threads on improper behavior on the SQL Server Agent side, but almost all results talk about applications not closing their db connections properly. It seems to me that the jobs performed by the SQL Server Agent are not correctly being closed?


texts: Which TLogs are causing large block change in SAN snap?

texts:
The environment is SQL Server 2014 Enterprise Edition, NetApp storage managed by CommVault Simpana. We have 100 dbs setup on an instance, all TLogs are on the same Volume.

The SAN Admin has setup TLog backups 4 times a day with a full snap of the TLog backup volume once a night.  The retention period is 14 days so at any time the TLog backup volume has 14 snaps of the TLogs.

Recently the TLog backup volume came close to running out of space because the full snaps were increasing at a rapid pace. We attributed this to end-of-year data processing, causing a lot of block change. This up-tick wasn't expected to last and it didn't.

The SAN Admin said he has no way of knowing which TLog files were responsible for the increase in block change and in turn the larger snaps.

I know there is a disconnect between files and blocks, but I would think you could connect the dots through Simpana, NetApp or PowerShell to find out which TLogs were causing the large block change.

Any suggestions would be appreciated.

Thanks