texts: How to migrate Unicode UTF-8 CLOB data from Oracle to SQL Server 2017 UTF-?

texts:
I am currently assisting in a migration effort of this application from Oracle 12c to SQL Server 2017. Initially I just performed table inserts using openquery to Oracle. I discovered that the tables which contained CLOB data could not be migrated using a single table to table insert query otherwise we would end up with dirty data. I am able to do the inserts by dynamically generating a single insert statement per row and running thousands of lines of insert statements. The problem is I've now come across a table which contains over 300k records with CLOB data in them. The one record at a time insert is taking an extremely long time and may at this rate run over 24hrs which is unacceptable.

What is my best option for migrating such a large amount of CLOB data from Oracle? Should I use the bulk tools like BCP, BULK INSERT or OPENROWSET?

Edit/Update: I have since learned that my troubles are most likely due to the UTF-8 encoding at the source Oracle system. Both methods I've used for migrating both CLOB and BLOB data have resulted in mismatched rows or missing rows.

UTF-8 has some known issues with SQL Server. The ones in particular I'm dealing with, Linked Server OPENQUERY and SSIS Project deployments, are both fixed in SQL Server 2019.


texts: How to set constraints on UDT in SQL Server 2005 - 2012

texts:
I'm creating my UDT:

CREATE TYPE [dbo].[Code]
FROM VARCHAR(20) NOT NULL

As RULES and DEFAULTS are considered deprecated, what is the recommended way to set constraint on my type to make sure that columns defined from this type contain only specific characters?


texts: pg_database_size() is small,but pg_dump is very big

texts:
I faced a strange question.
when I use the psql command " \l+ db_name" or following sql,

cattle_dev=# \l+ cattle_dev
                                                    List of databases
    Name    |   Owner    | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace | Description
------------+------------+----------+-------------+-------------+-------------------+---------+------------+-------------
 cattle_dev | cattle_dev | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |                   | 1738 MB | pg_default |
(1 row)

cattle_dev=# select pg_size_pretty(pg_database_size('cattle_dev'));
 pg_size_pretty
----------------
 1738 MB
(1 row)

cattle_dev=#

it tells that the database is small.
But,if i use the pg_dump to backup this database,the result is too large,it's almost 18GB.

[enterprisedb@ppasdev 20170605012443]$ ll -h cattle_dev_20170605012443.dmp
-rw-rw-r--. 1 enterprisedb enterprisedb 18G Jun  5 01:34 cattle_dev_20170605012443.dmp
[enterprisedb@ppasdev 20170605012443]$

The problem is why the pg_dump result is too large,but the pg_database_size is small?

any help would be appreciated.thanks.


texts:
I'm currently working on a project which bulk import data from flat files (csv) about 18 different files each linking to a specific table through some stored procedure.

I followed the steps as  advised in Data Loading Performance guide.

The database is in BulkLogged recovery mode to minimize the logging, when executing the stored procedure below on a file containing 600000 rows I get an error

  Msg 9002, Level 17, State 4, Procedure SP_Import__DeclarationClearanceHistory_FromCSV, Line 34
  The transaction log for database  is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

(for testing purposes I do a full backup before starting the import).

Looking at the log_reuse_wait_desc I see the following:

  log_reuse_wait_desc CHECKPOINT. All other import get imported successfully.

Any input in solving this would be welcomed.

PROCEDURE [dbo].[SP_Import_DeclarationClearanceHistory_FromCSV]
    @FilePath [nvarchar](1000)
AS
BEGIN
    -- Creating a Temproary Table for importing the data from csv file.
    DBCC TRACEON(610)

    CREATE TABLE #DeclarationClearanceHistory
    (
          [ItemID] [int] IDENTITY(1, 1) NOT NULL ,
          [CMSDeclarationID] [bigint] NOT NULL ,
          [StatusCode] [nvarchar](10) NOT NULL ,
          [SubStatus] [nvarchar](10) NULL ,
          [DepartmentCode] [nvarchar](10) NULL ,
          [StartDate] [datetime] NULL ,
          [EndDate] [datetime] NULL ,
          PRIMARY KEY CLUSTERED ( [ItemID] ASC )
            WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                   IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
                   ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
        )
    ON  [PRIMARY]

    -- Inserting all the from csv to temproary table using BULK INSERT
    EXEC ('BULK INSERT #DeclarationClearanceHistory
    FROM ''' + @FilePath + '''
    WITH ( FIELDTERMINATOR = ''<,>'', ROWTERMINATOR =''\n'', FIRSTROW = 2, KEEPIDENTITY, CODEPAGE = ''ACP'', ORDER = ''ITEMID ASC'' );') ;

    -- By using MERGE statement, inserting the record if not present and updating if exist.
    MERGE dbo.DeclarationClearanceHistory AS TargetTable                            -- Inserting or Updating the table.
        USING #DeclarationClearanceHistory AS SourceTable                           -- Records from the temproary table (records from csv file).
        ON ( TargetTable.ItemID = SourceTable.ItemID )      -- Defining condition to decide which records are alredy present
        WHEN NOT MATCHED BY TARGET
            THEN INSERT (
                          ItemID ,
                          CMSDeclarationID ,
                          StatusCode ,
                          SubStatus ,
                          DepartmentCode ,
                          StartDate ,
                          EndDate
                        )
               VALUES   ( SourceTable.ItemID ,
                          SourceTable.CMSDeclarationID ,
                          SourceTable.StatusCode ,
                          SourceTable.SubStatus ,
                          SourceTable.DepartmentCode ,
                          SourceTable.StartDate ,
                          SourceTable.EndDate
                        )
        WHEN MATCHED -- If  matched then UPDATE
            THEN UPDATE
               SET      TargetTable.ItemID = SourceTable.ItemID ,
                        TargetTable.CMSDeclarationID = SourceTable.CMSDeclarationID ,
                        TargetTable.StatusCode = SourceTable.StatusCode ,
                        TargetTable.SubStatus = SourceTable.SubStatus ,
                        TargetTable.DepartmentCode = SourceTable.DepartmentCode ,
                        TargetTable.StartDate = SourceTable.StartDate ,
                        TargetTable.EndDate = SourceTable.EndDate ;
DBCC TRACEOFF(610)
END


texts: MySQL:- Fetch records from 31 to 60 days

texts:
I have a table with a field called date_created with TIMESTAMP format now I have a requirement to fetch records between 0-30,31-60,61-90 like this.
I able to fetch record between months like

SELECT  received_quantity , delivery_qty , unit_cost
FROM    tbl_inward_list
WHERE   date_created BETWEEN NOW() - INTERVAL 30 DAY AND NOW()

also

SELECT  received_quantity , delivery_qty , unit_cost
FROM    tbl_inward_list
WHERE   date_created BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 2 MONTH ) AND DATE_SUB( CURDATE() ,INTERVAL 1 MONTH )

But I want a query between 31 to 60 also 61 to 90 like this way.


texts: Do I need to perform any tasks after Postgres goes into recovery mode?

texts:
For starters, I do not have any sort of automated failover in place.

After two scenarios, I'm unsure of the state of the database and any required actions, if any, to take:

My master server disappears off the face of the planet and I touch my_trigger_file, converting my standby into a master server.
My master server crashes, then restarts in recovery mode

My assumption is that the server in question in each of those scenarios will first be in recovery mode, will then finish recovering, and will finally be ready as a master server.

In scenario 2 my assumption means things are back to normal, while in scenario 1 my assumption means the standby has no idea it was ever a standby and is now simply a master.

Are these assumptions correct, or are there other actions that need to take place in either or both scenarios after recovery finishes (aside from creating a new slave in the case of scenario 1)?

Also, is there a notable technical difference between the state of a slave server after becoming a master vs a master server undergoing recovery after a crash, or as far as Postgres is concerned, are they both essentially master servers that just recovered from something?

I'm using Postgres 9.2 with asynchronous streaming replication.


texts: Joining Three Tables Using Composite Key

texts:
I know there are a few posts on this topic, but can't seem to find one that is similar to mine, so hopefully someone can provide some insight!!

I have three tables: acct_rec, income_rec, and advance_billing. These tables house invoice data. acct_rec is the main table that has every invoice and some data around it. income_rec houses only invoices where we received income. advance_billing houses invoices that have been billed to the client.

So the tricky part is that sometimes an invoice will only exist in either income_rec or advance_billing. Sometimes an invoice will be in both tables.

The columns they all have in common are ar_inv_nbr, ar_seq_nbr, and ar_type. income_rec and acct_rec have two additonal fields that need to be joined on: job_number and job_comp_number.

These are the columns of interest I'm looking to retrieve:

acct_rec
inv_amount
inv_advance_amt
inv_date
post_period
ar_inv_nbr
ar_inv_seq
ar_type

income_rec
ar_inv_nbr
ar_inv_seq
ar_type
rec_amt

advance_billing
hours
quantity
line_total
ar_inv_nbr
ar_inv_seq
ar_type

So for example, if an invoice is only in income_rec I'd like to retrieve that row from income_rec along with the data for that invoice from acct_Rec. There should then be NULL's for all the fields that are in advance_billing.

And if an invoice is in advance_billing but not in income_rec, I'd like to accomplish the same: return all data from advance_billing but NULL's for income rec data.

If an invoice is in both tables, then return the data from both tables. if there are multiple rows for the invoice in the tables (which there can be) group all rows to create one row.

I'm including the DDL for the tables below:

CREATE TABLE [dbo].[ADVANCE_BILLING](
[AB_ID] [int] NOT NULL,
[SEQ_NBR] [smallint] NOT NULL,
[AB_FLAG] [smallint] NOT NULL,
[JOB_NUMBER] [int] NOT NULL,
[JOB_COMPONENT_NBR] [smallint] NOT NULL,
[FNC_CODE] [varchar](6) NOT NULL,
[FNC_TYPE] [varchar](1) NULL,
[ADV_BILL_NET_AMT] [decimal](14, 2) NULL,
[COMMISSION_PCT] [decimal](9, 3) NULL,
[AR_INV_NBR] [int] NULL,
[AR_INV_SEQ] [smallint] NULL,
[AR_TYPE] [varchar](3) NULL,
[AR_INV_VOID] [smallint] NULL,
[CREATE_DATE] [datetime] NULL DEFAULT (getdate()),
[LINE_TOTAL [decimal](14,2)

CREATE TABLE [dbo].[ACCT_REC](
[AR_INV_NBR] [int] NOT NULL,
[AR_INV_SEQ] [smallint] NOT NULL,
[AR_TYPE] [varchar](3) NOT NULL,
[CL_CODE] [varchar](6) NOT NULL,
[DIV_CODE] [varchar](6) NULL,
[PRD_CODE] [varchar](6) NULL,
[OFFICE_CODE] [varchar](4) NULL,
[AR_INV_DATE] [datetime] NULL,
[AR_DESCRIPTION] [varchar](40) NULL,
[AR_INV_AMOUNT] [decimal](15, 2) NULL,
[AR_POST_PERIOD] [varchar](6) NULL,
[AR_COS_AMT] [decimal](14, 2) NULL,
[AR_STATE_AMT] [decimal](14, 2) NULL,
[AR_COUNTY_AMT] [decimal](14, 2) NULL,
[AR_CITY_AMT] [decimal](14, 2) NULL,
[AR_ADVANCE_AMT] [decimal](15, 2) NULL

CREATE TABLE [dbo].[INCOME_REC](
[AB_ID] [int] NOT NULL,
[SEQ_NBR] [smallint] NOT NULL,
[JOB_NUMBER] [int] NOT NULL,
[JOB_COMPONENT_NBR] [smallint] NOT NULL,
[AR_INV_NBR] [int] NULL,
[AR_INV_SEQ] [smallint] NULL,
[AR_TYPE] [varchar](3) NULL,
[AB_FLAG] [smallint] NULL,
[CREATE_DATE] [datetime] NULL
[INCOME_REC] ADD [REC_AMT] [decimal](14, 2) NULL

I hope this wasn't too long winded, I just wanted to provide as much detail as possible with regards to my question!

If anyone has any insight, that would be awesome! I've tried a billion different join combinations and cannot get this to provide the right results!!

Thanks!!


texts:
I want to understand how safe is the rename command:

ALTER INDEX old_name RENAME TO old_name;

The index was created by command(for the foreign key column):

CREATE INDEX old_name ON table_t1 (user_id);

Does it mean simple update in the postgres system tables or do much more operations. I try to find it in the docs but unsuccess.


texts: Is it possible to speed up this PARTITION BY query?

texts:
I've got a timeserie table. Each datapoint has also a statut field which
indicates its providing source. I'd like to request last available datapoint
as of date T but there could be several results, each with a different statut.

Therefore, I'm using a "statut preference list" to retrieve datapoint in
accordance with providers priority.

Here's the query : (in this example, my preferred ordered statuts would be
10 then 9 then 1)

DECLARE @MyStatuts TABLE (StatutOrder tinyint, StatutValue tinyint)
INSERT INTO @MyStatuts VALUES(1, 10)
INSERT INTO @MyStatuts VALUES(2, 9)
INSERT INTO @MyStatuts VALUES(3, 1)

SELECT TOP 1 Id, Date, Statut, Value
FROM (
    SELECT data.Id, data.Date, data.Statut, data.Value, s.StatutOrder,
           MIN(s.StatutOrder)
             OVER(PARTITION BY data.Date) As MinStatutOrder
    FROM [DATA].[TimeSerie] as data
      JOIN @MyStatuts as s on data.Statut = s.StatutValue
    WHERE Id = 'timeserieid' AND Date <= '24/04/2014'
    ) AS T
WHERE T.StatutOrder = T.MinStatutOrder
ORDER BY T.Date DESC

Do you have any ideas how I can speed that up ? I already used Index on [TimeSerie] table...

Here's table code :

CREATE TABLE [DATA].[TimeSerie](
[Id] [varchar](25) NOT NULL,
[Date] [date] NOT NULL,
[Statut] [tinyint] NOT NULL,
[Value] [decimal](19, 8) NOT NULL,
CONSTRAINT [PK_Price] PRIMARY KEY CLUSTERED
(
[Id] ASC,
[Date] ASC,
[Statut] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Link to the execution plan


texts: PostgreSQL 10 - single big query in UDFs or multiple smaller ones?

texts:
Many times, I'm able to write a PL/PGSQL UDF in two ways:

As a big query usually using WITH clauses and UNION (i.e. more declarative but also more complex to write/read)
As multiple smaller queries (i.e. more procedural but also easier to write/read)

I'm aware that PG query planner will plan each smaller query in UDF individually, and I'm afraid that might have an impact on overall performance. What do you guys prefer?

Thank you.


texts:
Postgres 9.6.8, a table with 6GB including a jsonb field that is up to 15MB.  The query planner is not using any indices btree or gin when querying that table.  When I add the same indices to a local/dev table (not as big), indices are used.
api=> \d api.commands
                            Table "api.commands"
        Column        |            Type             | Collation | Nullable | Default
----------------------+-----------------------------+-----------+----------+---------
 id                   | uuid                        |           | not null |
 error                | boolean                     |           |          | false
 command              | jsonb                       |           |          |
 program_id           | uuid                        |           |          |
 account_id           | uuid                        |           |          |
Indexes:
    "commands_pkey" PRIMARY KEY, btree (id)
    "idx_cmd_account" btree (account_id)
    "idx_cmd_program" btree (program_id)
    "ix_cmd_created" btree (created)
    "idx_cmd_gin" gin (command jsonb_path_ops)
    "idx_cmd_type" btree ((((command -> 'command'::text) -> 'meta'::text) ->> 'type'::text))

Given this table with listed indices, I'm trying to query for a specific "type" of command e.g.
select * from api.commands where command->'command'->'meta'->>'type' = 'create'

query planner returns
api=> explain select * from api.commands where command->'command'->'meta'->>'type' = 'create';
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Seq Scan on commands  (cost=0.00..63589.26 rows=604 width=739)
   Filter: ((((command -> 'command'::text) -> 'meta'::text) ->> 'type'::text) = 'create'::text)
(2 rows)

I've also tried to use the GIN index with
api=> explain select * from api.commands where command->'command'->'meta' @> '{ "type": "create" }';
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Seq Scan on command_registry  (cost=0.00..63287.23 rows=121 width=456) (actual time=50.802..333627.363 rows=2 loops=1)
   Filter: (((command -> 'command'::text) -> 'meta'::text) @> '{"type": "create"}'::jsonb)
   Rows Removed by Filter: 120921
   Buffers: shared hit=218390 read=131857
 Planning time: 115.912 ms
 Execution time: 333627.427 ms
(6 rows)

I tried to disable seqscan, and that just increases the cost.  Further, even if I don't include the large JSONB field in the query no index is used:
api=> explain select * from api.commands where program_id = '620D00ED-5368-4803-928E-30D454F4AFD3';
                              QUERY PLAN
-----------------------------------------------------------------------
 Seq Scan on commands  (cost=0.00..62683.16 rows=34 width=739)
   Filter: (program_id = '620d00ed-5368-4803-928e-30d454f4afd3'::uuid)
(2 rows)

All of the above queries are using the index on a local database with less data.  I tried vacuuming the big database to no avail.
Can anyone explain what's going on?

Update
I resized my database (running in RDS) to match the production sized database and the planner is finding the btree indices.   Still not the GIN index, and I've updated the plan to include the execution & buffer usage.


texts: When using DAC, receiving the "[…] the maximum number of '1' dedicated administrator connections already exists" message in error log

texts:
I am about to escalate this to MS, but I thought I would try here first and see if anyone has any ideas as to how to prevent this faux error message...
When connecting to SQL Server (2016 and 2017 latest builds) with a dedicated administrator connection (DAC) via a PowerShell, I get the following error message:

Date      4/2/2019 1:59:13 PM
Log       SQL Server (Current - 4/2/2019 1:59:00 PM)
Source        Logon
Message
Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]

The query does run successfully. Numerous connections string management iterations have been attempted, this one is the most robust so far.
A solution exists on Stack Exchange that involves killing the spid before closing the connection, but that also throws a nuisance message into the SQL Server error log, so no joy there.
Examining sys.dm_exec_sessions reveals nothing of interest, no connections remain open using this technique.  The below PowerShell has a dummy query in it, I can't talk about why we are connecting this way because it is proprietary, but it is 100% necessary, it is a very quick connection, and I need to do it once every 10 minutes.
This error is a total nuisance/noise. DAC query runs and works as expected. Error fires every time even with a fresh restart on a quiesced system.  There are NO other DACs - if there is it would throw a very heinous error message at the command prompt.
Interestingly, when using sqlcmd for this, it does not throw the error message.
#begin powershell script
$SqlServerName = "server\instance"

$DbQuery = "
INSERT INTO  master.dbo.sometable(value1,value2) values ('test14','testtest14');"

function Get-SqlConnection
{
    param (
        [String] $SqlServerName
    )
    $sqlConnection = $null

    try
    {
        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $sqlConnection.ConnectionString = "data source=admin:$SqlServerName;Integrated Security=True; pooling=false"
        $sqlConnection.Open()
    }
    catch
    {
        if ($sqlConnection)
        {
            [void] $sqlConnection.Dispose()
        }
        throw
    }
    $sqlConnection
}

try
{
    $sqlCommand = New-Object System.Data.SqlClient.SqlCommand
    $sqlConnection = Get-SqlConnection -SqlServerName $SqlServerName
    $sqlCommand.Connection = $SqlConnection
    $sqlCommand.CommandText = $dbQuery
    [void] $sqlCommand.ExecuteNonQuery()
}
finally
{
    if ($sqlCommand)
    {
        [void] $sqlCommand.Dispose()
    }

    if ($sqlConnection)
    {
        [void] $sqlConnection.Dispose()
    }
}


texts: Most efficient way of bulk deleting rows from postgres

texts:
I'm wondering what the most efficient way would be to delete large numbers of rows from PostgreSQL, this process would be part of a recurring task every day to bulk import data (a delta of insertions + deletions) into a table. There could be thousands, potentially millions of rows to delete.

I have a file of primary keys, one per line. The two options I was thinking of were along the lines of the below, but I don't know/understand enough of the internals of PostgreSQL to make an informed decision which would be best.

Execute a DELETE query for each row in the file, with a simple WHERE on primary key (or group the deletes in batches of n using an IN() clause)
Import the primary keys into a temporary table using the COPY command and then deleting from the main table using a join

Any suggestions will be much appreciated!


texts:
Hi I am working on one project and want to create a view. I am using temp table in my query. can any one let me know how we can create such view ?
Following is my query, I tried using WITH..AS , but it gives due to insert query.
create temporary table temp_testtbl as
select id,dte,name,hrs_total
FROM dttbl.dts limit 0;

insert into temp_testtbl
select id,dte,name,hrs_total
FROM dttbl.dts
group by id,dte,name,hrs_total;

SELECT i.dte,
    idm.empl_id,
    1 AS entity_id,
    sum(i.hrs_total) AS myhrs
   FROM temp_testtbl i
   JOIN schema1.mytable idm ON idm.src_id = i.iex_id::text AND idm.src_type_id = 123 AND idm.end_dte IS NULL
   JOIN schema1.mytable idm2 ON idm2.empl_id = idm.empl_id AND idm2.src_type_id = 345 AND idm2.end_dte IS NULL
  GROUP BY i.dte, idm.empl_id limit 100;


texts: Redo log member and control files at Oracle DB Standard Edition

texts:
I am planing to dive into the oracle database world. Since the oracle database is such a complex machine, I want to inform myself as good as possible, before I start. I have some questions.

How many redo log members for each group can I define and how many control files can I use by using the standard edition (one)?

I've read, that I can have only two archive log destinations defined by LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST in the standard edition (one). Now I am wondering, if there is any limitation by multiplexing redo log members and control files by using the standard edition (one) ?

I couldn't find this information except, that there can be 8 control files at all.


texts: Update rows in a table with an index column starting from 0 with consecutive numbers

texts:
I have a few tables in a Oracle db with some "rule": The INDEX_COLUMN must start from 0 and must be consecutive numbers and the column NRCRT must start from 1 and also consecutive numbers. I've made a script to identify the rows who do not follow this rule but I don't know how to modify those rows (ids). The script to identify the rows is:

select ID,NRCRT,H_ID,INDEX_COLUMN from ANEXE_H_ROWS where H_ID in (
select anexa.id from (
    select ANEXA_ROWS.H_ID as id, ANEXA_ROWS.INDEX_COLUMN as idx
        from chest CH
        inner join investig INV on INV.FK_ID_CHEST = CH.ID
        inner join tip_investig TIP_INV on TIP_INV.ID = INV.FK_TIP_INVESTIG
        inner join ANEXE_H ANEXA on ANEXA.FK_ID_INVESTIG = INV.ID
        inner join ANEXE_H_ROWS ANEXA_ROWS on ANEXA_ROWS.H_ID = ANEXA.ID
    ) anexa
group by anexa.id having max(anexa.idx)+1<>count(*) or min(anexa.idx)<>0
)
order by H_ID,INDEX_COLUMN

and let's say it returns:

ID      NRCRT   H_ID    INDEX_COLUMN
2517    8       1136    7
2518    9       1136    8
2519    10      1136    9
2520    11      1136    10
2521    12      1136    11
2522    13      1136    12
2523    14      1136    13

3000    2       1137    1
3001    3       1137    2
3002    4       1137    3

How can I modify the script to alter the table to return:

ID      NRCRT   H_ID    INDEX_COLUMN
2517    1       1136    0
2518    2       1136    1
2519    3       1136    2
2520    4       1136    3
2521    5       1136    4
2522    6       1136    5
2523    7       1136    6

3000    1       1137    0
3001    2       1137    1
3002    3       1137    2


texts: Single table of all chat conversations or separate tables?

texts:
I'm planning to make a chat application. The problem is that there can be billions of records, since there will be lots of chat messages.
I have come up with the idea to make separate tables for each conversation, and that would make millions of tables. Or should I add them to a single table? Which makes tens of billions of records in a single table?
I'm also thinking about distributing each conversation between different servers.
Which of these would be an appropriate solution? Please feel free to guide if I'm doing this wrong! I can consider different database methodologies as well (like NoSQL).


texts: Approaches for deleting unnecessary records from tables

texts:
We have a database which stores the temperatures of 20 thermometer every 7 seconds. We want to delete all records in the way that every minute holds just one tempereature instead of 8 which are older than 3 months. I was planning to do it as follows as discussed in that question here:

Select the (relatively few) surviving rows into a temporary table.
Truncate the table.
Re-insert the survivors.

But all devices are working without interruption and inserting values into the DB so I can not Truncate and rename the temp_tables since the system should be on all the time. Or can I? If not, do I have to do it by DELETE VACUUM?

By the way we delete all unnecessary records which are older than 3 months just once, afterwards we have to repeat this process every month for the 4.th month previous (I hope that part was clear). If we repeat this process at the beginning of 05.2013, we have to delete the unnecessary records of the month 01.2013. How can I automatize this process? Should I better write a .bat file for scheduled task or is there any better approaches?


texts: Schema Design - would you split this table?

texts:
Table A

- auto-incrementing integer primary key
- 3 dates
- 17 booleans
- 7 varchars (less than 40 bytes in these combined varchars in 99% of rows)
- 5 foreign keys

Table B

- auto-incrementing integer primary key
- 1 date
- 2 boolean fields
- 3 varchars (1 unique UUID string in each row, other 2 varchars empty 99% of the time)
- 3 foreign keys

Table A has 25 million rows - SELECT COUNT(*) takes approx 70 seconds
Table B has 20 million rows - SELECT COUNT(*) takes approx 6-7 seconds

I only included Table B to illustrate my assuption, which is that the 900% more time it takes to count Table A, despite having only 25% more rows than Table B, is that Table A has 33 columns, compared with only 10 columns in Table B. I realize 33 columns isn't a "big" table, but it might be bigger than we need, since we only use a few of those columns in many operations.

We aren't experiencing any serious issues, aside from counting taking too long to be useful within the span of a web request (e.g., for pagination in a Django admin list view).

However, we are about to increase the size of Table A, from 25 million, by about 1-2 million rows per month, because of a new feature, which adds to our fears about performance in general.

We're considering the following options:

Split Table A

We'd put most of Table A's columns into a new child table (Table A Meta) with a 1-to-1 relation to Table A. Only 4-6 most commonly used columns would remain in Table A, and presumably this would help make read queries and counting quicker at the expense of needing a separate query (or JOIN) to get the additional details for a given row, as well as a small amount of additional write overhead.

Wait for trouble, then partition

The other option is to ignore the fact that SELECT COUNT(*) is basically unusable, since other things are functioning properly (querying via indexed fields, etc.), and continue using Table A even past 50, 75 million rows, etc., eventually partitioning at some point in the future.

I realize these options aren't mutually exclusive, but I'd like to get some general feedback on those options and whether you would bother to do the first, etc.

Execution

Here is the execution plan for the longer COUNT mentioned:

db=# EXPLAIN ANALYZE SELECT COUNT(*) FROM "message";
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1236008.12..1236008.14 rows=1 width=8) (actual time=79357.273..79357.293 rows=1 loops=1)
   ->  Seq Scan on message  (cost=0.00..1173727.10 rows=24912410 width=0) (actual time=1.460..65375.321 rows=24926666 loops=1)
 Planning time: 0.543 ms
 Execution time: 79357.350 ms
(4 rows)


texts: Creating indexes with t-sql scrips vs rebuild indexes in maintenance plan

texts: I’m using SQL Server 2008 and I am running several (15) scripts each day to bulk insert to tables. Each script drops the table at the beginning. I create indexes in the scripts. Some of the scripts reference the tables that were created with previous scripts. I want to improve the bulk insert operations but also want to keep the indexes to improve query performances. Does it make sense to create the indexes using maintenance plan rather than creating them in the script? Will it improve the bulk insert performance if I create the indexes in the maintenance plan at the end of all scripts run? Thanks.


texts: Returning the column names used in Query

texts:
I am working with a tool that connects to PostgreSQL, which returns the result of a query in a multi-dimensional array, but, I need the name of the columns used in the query, is there any way to return this information too, automatically, without having to modify it existing queries?

I have the possibility to concatenate codes in the existing queries, but not to change them.

an idea, would be to return the name of the columns in the first row of the query result, this row I would eliminate within the program....

Assuming I have the following query:

SELECT
   A.USER_ID    AS ID
   A.USER_NAME  AS NAME
   A.AGE
FROM USERS A

the result could be something like:

1º row -> | ID | NAME | AGE |
2º row -> | 2  | JOHN | 25  |
3º row -> ....

of course, this is just an idea ...


texts: where can I find the DNS name associated to the listener of an Availability Group?

texts:
I have an availability group that has a listener.

The listener is called SQLPROD as you can see on the picture below.
It uses port 1433

When I run the following script:

ALTER AVAILABILITY GROUP [SQLPROD_AG]
    ADD LISTENER 'SQLPROD' (WITH IP (('200.1.1.199', '255.255.254.0')));

I get this error message (when it already exists):

  Msg 19477, Level 16, State 48, Line 4 The availability group
  'SQLPROD_AG' already has a listener with DNS name
  'SQLPROD_AG_SQLPROD'.  Availability groups can have only one listener.
  Use the existing listener, or drop the existing listener and create a
  new one.

using the Always On Availability Groups Catalog Views I haven't found anything revealing:

select * from sys.dm_hadr_auto_page_repair
select * from sys.dm_hadr_cluster_networks
select * from sys.dm_hadr_availability_group_states
select * from sys.dm_hadr_database_replica_cluster_states
select * from sys.dm_hadr_availability_replica_cluster_nodes
select * from sys.dm_hadr_database_replica_states
select * from sys.dm_hadr_availability_replica_cluster_states
select * from sys.dm_hadr_instance_node_map
select * from sys.dm_hadr_availability_replica_states
select * from sys.dm_hadr_name_id_map
select * from sys.dm_hadr_cluster
select * from sys.dm_tcp_listener_states
select * from sys.dm_hadr_cluster_members

select * from sys.availability_databases_cluster
select * from sys.availability_groups_cluster
select * from sys.availability_group_listener_ip_addresses
select * from sys.availability_read_only_routing_lists
select * from sys.availability_group_listeners
select * from sys.availability_replicas
select * from sys.availability_groups

All seems to be fine:

when I use powershell, the following commands:

Import-Module FailoverClusters
cls
Get-ClusterResource

Where can I find where the name SQLPROD_AG_SQLPROD is?
Can I change this name?


texts: How does sql server FCI respond to failure?

texts:
Taking windows role default settings - (n-1) failovers ok within 6 hrs , where n=# of nodes, if nodes = 2, 1 failover within 6 hrs is allowed.

Taking resource default settings - Within 15mins period, 1 restart is allowed on the same node, else failover, RetryPeriodOnFailure is 1 hr

Taking a example of a simple 2 node cluster (node1 & Node2), single sqlFCI (sql), say sql is on node 1 at 9AM.

9AM - sql fails - attempts to restart on Node1 - fails - failsover to Node2 - attempts to restart on Node2 - fails - stays failed.

Now where I am confused is, https://support.microsoft.com/en-us/help/947712/failover-cluster-resource-recovery-behavior-in-windows-server-2008 , "If there is no intervention and the resource remains in the failed state for 60 minutes, Windows Server tries to bring the resource online again. "

So, according to above, after 60mins, say sometime after 10AM - Does sql try a cycle again :

Attempts sql restart on Node2 - fails - (but cannot failover as 6hrs have not passed)

11AM- Attempts sql restart on Node2 - fails - (but cannot failover as 6hrs have not passed)

12PM - Attempts sql restart on Node2 - fails - (but cannot failover as 6hrs have not passed)

1PM - Attempts sql restart on Node2 - fails - (but cannot failover as 6hrs have not passed)

2PM - Attempts sql restart on Node2 - fails - (but cannot failover as 6hrs have not passed)

3PM - Attempts sql restart on Node2 - fails - failsover to node1 as 6 hrs have passed, tries coming online there , and so on.. is this how it happens? Thanks.


texts:
I have two tables:

Person
------
PersonID (PK)
GroupID
DeletedDate

PersonData
----------
PersonID (PK)
FieldID (PK)
Value

The fields are custom-created; hence the schema. I am trying to run a histogram-type query to discover the range of values for a given FieldID together with a count of the times each value occurs. Here is the query:

SELECT pd.Value, count(*)
FROM Person p
INNER JOIN PersonData pd ON p.PersonID=cd.PersonID
WHERE p.DeletedDate IS NULL AND p.GroupID=‘cAPHSWovx9d2yaN’
GROUP BY pd.Value

We use GUIDs for the GroupIDs.

Performance is fine for small groups but degrades rapidly. The group in question has 5 million records in table Person and an order of magnitude beyond that in PersonData. The above query takes an hour to run. It would be acceptable to get it down to a few minutes.

I created an index on PersonData on (PersonID, FieldID, Value) in that order. MySql generally chooses not to use it when I run explain. (Actual results of explain vary based on FieldID.) I also have an index on Person on (GroupID, DeletedDate).

I have two questions:

On the explain I always get a “Using where; Using index; Using temporary; Using filesort” on the Person table. Why does MySql need to sort the Person table before joining to PersonData?
Why does MySql generally decline to use the (PersonID, FieldID, Value) index? Is there a better index I could use? I assumed I needed the PersonID in the index to help with the join.

Edit Here is the relevant portion of the explain:

id    select_type    table    type    key              key_len    ref          rows       Extra
1     SIMPLE         p        ref     deleted_person   67         const,const  3476105    Using where; Using index; Using temporary; Using filesort
1     SIMPLE         pd       ref     person_field_val 62         p.PersonID   10         Using index

Edit 2 Forgot the GROUP BY in the query above.


texts: Can CDC or Change Tracking be against a read only secondary?

texts:
We will be deploying SQL Server 2012 Availability Groups with read only secondaries and would like to execute our SSIS extracts against the secondary while utilizing CDC or change tracking (the decision hasn't been made as to which will be used at this time).

I am hoping that the underlying implementations of CDC and CT would allow for their functionality to exist and be consumed on the secondary but have not yet been able to find anything stating this one way or the other.

Does anyone have any experience (and better yet official documentation) that points to the viability of this solution (or not)?

We are leaning toward Change Tracking at this point because we do not have a hard requirement for historical data but I would like to be able to identify any issues/risks for either implementation.


texts: How to get exception context for a manually raised exception in PL/pgSQL?

texts:
In Postgres, we get the "stack trace" of exceptions using this code:

EXCEPTION WHEN others THEN
    GET STACKED DIAGNOSTICS v_error_stack = PG_EXCEPTION_CONTEXT;

This works fine for "natural" exceptions, but if we raise an exception using

RAISE EXCEPTION 'This is an error!';

...then there is no stack trace. According to a mailing list entry, this might be intentional, although I can't for the life of me figure out why. It makes me want to figure out another way to throw an exception other than using RAISE. Am I just missing something obvious? Does anyone have a trick for this? Is there an exception I can get Postgres to throw that would contain a string of my choosing, so that I would get not only my string in the error message, but the full stack trace as well?

Here's a full example:

CREATE OR REPLACE FUNCTION error_test() RETURNS json AS $$
DECLARE
    v_error_stack text;
BEGIN

    -- Comment this out to see how a "normal" exception will give you the stack trace
    RAISE EXCEPTION 'This exception will not get a stack trace';

    -- This will give a divide by zero error, complete with stack trace
    SELECT 1/0;

-- In case of any exception, wrap it in error object and send it back as json
EXCEPTION WHEN others THEN

    -- If the exception we're catching is one that Postgres threw,
    -- like a divide by zero error, then this will get the full
    -- stack trace of the place where the exception was thrown.
    -- However, since we are catching an exception we raised manually
    -- using RAISE EXCEPTION, there is no context/stack trace!
    GET STACKED DIAGNOSTICS v_error_stack = PG_EXCEPTION_CONTEXT;

    RAISE WARNING 'The stack trace of the error is: "%"', v_error_stack;

    return to_json(v_error_stack);
END;
$$ LANGUAGE plpgsql;


texts:
I've been using MySQL Workbench as my go-to management application for quite some time. However, recently (namely, in my most recent install), it will every so often lose the saved connections I've put in. It doesn't happen every time, but sometimes, when I open it up, there won't be any saved connections and I have to re-enter them.

Does anyone know of a way to fix this? The only thing I've been able to find are the release notes, which claim to have fixed this issue over a year ago (ironically, I never had an issue with that until recently).

I'm currently running Lubuntu 11.04 64bit.


texts: Given the functional dependencies of a relation is there more then one way to normalize it?

texts:
I"m studying normalization and have been reading through descriptions and examples online. One Concept that isn't clear to me is if the process can be automated at any point. I understand that a system can't tell you if you schema makes any real-world sense, but it seems likely to me that if you feed a program some arguments say, the functional dependencies, it would be able to do some steps for you.

Say you have a relationship R(A,B,C,D)

and you tell your program that A,B->C and A->D and that the primary key is (A,B)

Now if my understanding serves me correctly it should be possible for a program to identify that A->D violates 2NF because D depends on A and A is a subset of the primary key. That the proper decomposition would be R1(A,B,C) and R2(A,D).

The main book i'm learning from and my instructor indicate these normalization's are done all by hand. Is this correct or can some parts be automated?


texts: How do I send a database to someone else?

texts: I am working with a more experienced database admin and he wants me to send him a database that I created.  However, I cannot figure out where the database is stored on disk.  I am using Microsoft SSMS.


texts: WMI error: 0x800700a4 - The @wmi_query could not be executed in the @wmi_namespace provided

texts:
When trying to create a WMI alert in SQL Server, I received this error on 7 of the ~50 instances I was applying the change to:

SQLServerAgent Error: WMI error: 0x800700a4.
Msg 14511, Level 16, State 1, Procedure sp_verify_alert, Line 304 [Batch Start Line 557]
The @wmi_query could not be executed in the @wmi_namespace provided. Verify that an event class selected in the query exists in the namespace and that the query has the correct syntax.

The 7 instances that got this error were all SQL 2008 R2 and SQL 2012, a mix of Developer and Standard editions. I did not get the error on SQL 2014 or 2016 instances.

I was able to solve the issue in a couple, but not all 7 of the cases. See my answer below.


texts:
We have a MySQL 5.0 server running all tables as MyISAM. We have two slaves that, in the last month, we have upgraded to MySQL 5.5. Their tables are also still MyISAM.

My original plan was to upgrade the 5.0 master to 5.5 and then convert to InnoDB. Now I'm wondering, would it make more sense to start before the upgrade? I've been wary to since I've heard that InnoDB under 5.5 is much faster than InnoDB under 5.0. Is this unfounded, and should I start now?


texts:
I have created a maintenance plan to take a full backup of all my databases every Sundays and a differential backup every day. Also, a log backup is triggered every hour.

However, there is an issue with this schema. If I add a new database in the middle of the week, the differential and log backup jobs will fail because they are lacking a full backup image.

I therefore need to be able to take a full backup anytime either the differential or log backup job fails.

I could for example add a new step to take a full backup in case the previous one fails. However, this full backup job will run on all my databases but I would like to only take the one that have failed.

Any way to ask the SQL server to take a full backup of new databases? It can right after the database creation or in case a of failure of a differential or log backup job.


texts:
I've got a small script which allows me to select a database, and a number of tables, then output them via mysqldump.

Currently this does not include the USE 'db_name' statement in the output file, something which I need to be included.

Here's an example the dump command I'm using:-

mysqldump -u root db_name tbl_1 tbl_2 tbl_3 > output.sql

I found this question here, which describes the same issue I'm having, however the provided answer does not work for me. The USE 'db_name' statement is simply not written to the outputted SQL file.

Here's everything from one of my dumped SQL files, upto where it starts the DROP/CREATE table commands:

-- MySQL dump 10.13  Distrib 5.5.16, for Win64 (x86)
--
-- Host: localhost    Database: db_name
-- ------------------------------------------------------
-- Server version   5.5.16-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

If anyone knows a solution to this, or why the solution in the other question isn't working for me, I'd really appreciate it.

Thanks!


texts: One table or duplicate table with same properties but different dependencies

texts:
Disclaimer: I'm new to database modeling so I apologize in advance if the question is unclear or causes any confusion.

I am using .NET Entity Framework and have the following tables:

An Order can have multiple Items, but Item itself is not existence dependent on Order. That said, should I have two separate tables for Item, e.g. InventoryItem and OrderItem? If not, how should I manage the relationship?

Any help is appreciated!


texts:
In an Oracle DB have about 200M rows (about 20GB data) that I would like to insert into another table by

insert into foo select * from bar where some_unimportant=condition;

How do I do that the fastest way if I can always drop the foo table and redo the insert in case of any problem? Like, I don't need to be able to do a rollback.
Since I can drop the foo table, I have complete control over its creation also, if there are some good options to use to speed up the insert.

Of course, after the inserts, I wish the table to behave as an ordinary table (support undo by using rollback, etc.)

UPDATE: The table was created as an IOT, so I guess that changes things. Should I run

 alter index foo_pk nologging;

also?


texts: Number of active connections and remaining connections

texts:
I would like to get statistics about the peak number of connections over a period of time.

I know the pg_stat_activity view, like select count(*) from pg_stat_activity, but I think this method is not very smart.

Are there other views or tables that can provide the information I need?


texts: Problem processing quotes by T-SQL during Job creation

texts:
This is my first post ever in this website, which I found very helpful and instructive.

I need to execute a job with this step (which checks the mirroring state and send the result to me via Database Mail)

DECLARE @tab char(1) = CHAR(9);

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Mail',
    @recipients = 'xxxxxxxx@xxxxx.com',
    @query = 'SET NOCOUNT ON;

SELECT CONVERT(NVARCHAR,d.name), ISNULL(m.mirroring_state_desc, "")
FROM sys.databases d
LEFT JOIN sys.database_mirroring m ON m.database_id = d.database_id
WHERE m.mirroring_role_desc <> 'MIRROR'
'
    , @subject = 'Mirroring state'
    , @query_result_width = 80
    , @query_result_separator = @tab
    , @exclude_query_output = 0
    , @query_no_truncate = 0
    , @query_result_no_padding = 1
    , @attach_query_result_as_file = 1
    , @query_attachment_filename = 'CSV_Extract.csv';

The problem I have is that when I save the step, and try to execute the job it gives me a syntax error, because apparently T-SQL doesn't process the quotes with the condition WHERE I put in there, the 'MIRROR' parameter.

How can i rewrite this code to make sure the parameter 'MIRROR' in the WHERE condition will be processed? (I managed to get it to work by deleting that line, but it sent me a result of all databases, even tempdb)


texts: PostgreSQL DB freezes with large inserts

texts:
I am developing a web application with an SQLAlchemy ORM linked with PostgreSQL database backend. I have this usecase where a client uploads a file of about 20 MB containing large data. What I am trying to do is,run a series of select, insert and update commands on about a million entries parsed from the uploaded file against the database.

I am on Ubuntu 12.0 with a 8 GB RAM, and whenever I perform this massive insert update operation, my memory hits about 90%  usage and freezes at about half way through the inserts and updates.

During this period, nothing gets inserted or updated. Nothing happens at all and unless I kill this process, it takes a toll on my system's performance.

I have 3 tables. Two contain data and the other one is a junction table containing the foreign keys of the first two tables, and a column specifying the number of rows inserted in the first two tables. I do NOT have indexes specified, and Autocommit is off.

I tried looking up bulk insertions and suggestions that said

  "COPY is better than INSERT" and "take your DB offline and insert" and
  deferred Foreign key processings.

Please let me know if there are any ways to chunk my inserts so that I can serve other requests while the DB is occupied with writes. Else, my whole web application is frozen with this specific upload operation.
Thanks in advance.


texts: Optimize query collecting all related items

texts:
The following table is my menu master table that has an unlimited menu depth. Every menu can have a submenu and a sub-submenu and so on.

The problem I am facing is getting all items that have the same top-level parent.

One solution is to loop while parent is not null and second solution is to create a view with a parent id and all sub menu ids with comma separated column.

Then one select command will give the parent id. My table is as follows:

CategoryID  | ParentID
1           | null
2           | null
3           | 1
4           | 1
5           | 2
6           | 2
7           | 3

Output should be

CategoryID  | AllRelatedCategory
1           | 1,3,4,7
2           | 2,5,6

My current code is as follows. Problem is that it's working only with 3 levels of the hierarchy.

;with cte as
(
select a.CategoryID col1, convert(varchar, a.CategoryID) col2, a.ParentID
from [dbo].[TB_CATEGORY_MASTER] a where a.ParentID is null

union
select a.CategoryID col1, (convert(varchar, isnull(b.CategoryID,''))) col2, a.ParentID
from [dbo].[TB_CATEGORY_MASTER] a
left join [dbo].[TB_CATEGORY_MASTER] b on a.CategoryID=b.ParentID

union
select a.CategoryID col1, (convert(varchar, isnull(c.CategoryID,''))) col2, a.ParentID
from [dbo].[TB_CATEGORY_MASTER] a
left join [dbo].[TB_CATEGORY_MASTER] b on a.CategoryID=b.ParentID
left join [dbo].[TB_CATEGORY_MASTER] c on b.CategoryID=c.ParentID
)

select distinct c.col1,
  STUFF(
         (SELECT distinct ', ' + c1.col2
          FROM cte c1
          where c.col1 = c1.col1
          FOR XML PATH ('')), 1, 1, '') col2
from cte c
where c.ParentID is null


texts: How do I split a long PL/pgSQL line of code over multiple lines?

texts:
Is there a way to split a long line of PL/pgSQL code over multiple lines? My context is a trigger function where I log inserts into a table as per:

INSERT INTO insert_log (log_time, description)
VALUES (
    now()
    , 'A description. Made up of 3 semi long sentences. That I want to split, in the code, not in the log table, over 3 lines for readability.'
);


texts: Mysql query to get all different conversations for and to same user

texts:
This a simple chat table

id| fromUserId| toUserId| message

Let's take dummy data John (45), Jane(46) and Mary(47). Say John chats with both Jane and Mary at the same time.

John (45) started the conversation with Jane (46) so data looks like this:

id| fromUserId| toUserId| message
1 | 45        | 46      | wassup?

In the second context, Mary (47) started conversation with John (45)

id| fromUserId| toUserId| message
2 | 47        | 45      | wassup?

How can I display the two different conversations to John so that he can click on it and open the specific conversations in a modal with all messages? I have the modal part. The problem is to limit the display of the conversations in the header. It should look like this:

Live Chat With Jane | 1 New
Live Chat With Mary | 1 New

This is the code I use:

SELECT * FROM ve_chat c WHERE c.isActive='1'"
. "AND (c.fromUserId='$loginid_session' OR c.toUserId='$loginid_session') "
. "GROUP BY (c.fromUserId='$loginid_session' OR
c.toUserId='$loginid_session')";

But no matter how many different users started a conversation with John or how many conversations John started with different users, it only returns one user and all the messages are forwarded to that user. In case you are interested here's the usage:

         <?php
            $chatboxesQ="SELECT * FROM ve_chat c WHERE c.isActive='1'"
            . "AND (c.fromUserId='$loginid_session' OR c.toUserId='$loginid_session') "
            . "GROUP BY (c.fromUserId='$loginid_session' OR c.toUserId='$loginid_session')";
            $chatresultse=  mysqli_query($db,$chatboxesQ);?>
            <?php if(mysqli_num_rows($chatresultse)==0):?>

          No chats yet.
 Start a conversation.

    <?php elseif(mysqli_num_rows($chatresultse)>0):?>
         <?php   while($chatboxes=  mysqli_fetch_array($chatresultse)){;?>

          <?php
          $userchatboxesQ=mysqli_query($db,"SELECT * FROM ve_users u
          JOIN ve_chat c ON u.UserID=c.fromUserId
          OR u.UserID=c.toUserId WHERE u.UserID <> $loginid_session");
          $chatresultuseres= mysqli_fetch_assoc($userchatboxesQ);?>
          <?php

          $checkuserbox=mysqli_query($db,"SELECT * FROM ve_photos p WHERE
          p.UserID={$chatresultuseres['UserID']} AND IsPrimaryPhoto='1'
          ORDER BY InsertDate LIMIT 1");
          $rowboxes = mysqli_fetch_array($checkuserbox,MYSQLI_ASSOC);

          $countTotalmsgs="SELECT COUNT(*) FROM ve_chat c WHERE
          c.isActive='1' AND (c.fromUserId='$loginid_session' OR
          c.toUserId='$loginid_session')";
          $resultmsg=  mysqli_query($db, $countTotalmsgs);
          $rowChat= mysqli_fetch_array($resultmsg);?>
         <?php if(mysqli_num_rows($checkuserbox)==0):?>

         <?php elseif(mysqli_num_rows($checkuserbox)>0):?>
         <img class="streamphotos"src="core/<?=$rowboxes['PhotoPath'];?>" alt="profilename">
         <?php endif;?>
        Chat  with <?=$chatresultuseres['ProfileName'];?> | <?=$rowChat[0];?> Msg (s)

            <?php } ;?>
                 <?php endif;?>


texts: Expressing some queries without employing JOIN or OUTER JOIN

texts:
Assume the following relations schemas:

S(A,B)
T(B,C)

Rewrite the following expressions without using join or outer join operators explicitly:

S LEFT OUTER JOIN T
S FULL OUTER JOIN T

How do I do these two questions? I've tried using a where clause but I don't think it is correct. I know the first one has to use a union but how do I go about writing the queries?


texts: Foreign key without referenced columns specification

texts:
I noticed in the SQL Server documentation that the list of referenced columns is not a required parameter of a foreign key constraint:

<column_constraint> ::=
    [ CONSTRAINT constraint_name ]
    {     { PRIMARY KEY | UNIQUE }
            (...)

      | [ FOREIGN KEY ]
            REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ]
            [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
            [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
            [ NOT FOR REPLICATION ]

      | CHECK (...)
    }

If I omit the ( ref_column ) part, it seems to reference the primary key of the referenced_table_name. That is most convenient. However, I cannot find any specification of this behavior so I am cautious to use it.

Does anybody know whether it is specified anywhere?


texts:
How can I RESTORE a database through SQL Server Management Studio by connecting "remotely" to it?

If I know the "IP address" of the target machine what else do I need to know?

If the port number is a requirement, how can I find it?

I only know the IP address of the target machine. I want to know on which port the SQL Server runs. I am given the task to connect to the SQL Server instance and take a backup.


texts: Why my Oracle 12C is allowing me to a connection creation without passing network security and integrity parameters

texts:
My Integrity settings Oracle Net Manager are:
-- Integrity for - Server
Checksum level - REQUIRED
Selected Method : SHA1

-- Integrity for- CLIENT
Checksum level - REQUIRED
Selected Method : SHA1

-- Encryption for - Server
Encryption Type - REQUIRED
Seed: "encryption seed"
Algorithm - AES256

-- Encryption for - CLIENT
Encryption Type - REQUIRED
Seed: "encryption seed"
Algorithm - AES256

--

Now my test program 1: With security parameters : Works FINE

    Properties prop = new Properties();

    prop.put("oracle.net.encryption_client", "REJECTED");
    prop.put("oracle.net.encryption_types_client", "AES192");
    prop.put("oracle.net.crypto_checksum_client", "REJECTED");
    prop.put("oracle.net.crypto_checksum_types_client", "SHA1");

    OracleDataSource dataSource = new OracleDataSource();
    dataSource.setServerName("192.168.42.191");
    dataSource.setPortNumber(1521);
    dataSource.setDriverType("thin");
    dataSource.setDatabaseName("orcl");
    dataSource.setUser("system");
    dataSource.setPassword("dkpune");
    dataSource.setConnectionProperties(prop);
    m_cOrclConnection = dataSource.getConnection();

--
Now my test program 1: Without security parameters: Works FINE

Properties prop = new Properties();

OracleDataSource dataSource = new OracleDataSource();
dataSource.setServerName("192.168.42.191");
dataSource.setPortNumber(1521);
dataSource.setDriverType("thin");
dataSource.setDatabaseName("orcl");
dataSource.setUser("system");
dataSource.setPassword("dkpune");
dataSource.setConnectionProperties(prop);
m_cOrclConnection = dataSource.getConnection();

I am not able to understand the difference, why my test stub is returning valid connection instance in both of the cases.

the connection creation in 2nd case should throw an exception. Correct me if i am wrong.


texts: How can I run a T-SQL Script using SQL Credentials?

texts: I need to develop a T-SQL insert script for use on a remote SQL 2005 Express instance but I would like the script to include the necessary SQL credentials so all the user has to do is execute the script.  What is the easiest way to accomplish this?


texts: Perform mysql limited select that wraps when it reaches the end of table

texts:
I want to grab the next 2 rows from a table after a given id. This code works nicely however if the rows being selected right at the end of the database I need the select to wrap around so for instance in a table of rows 0 to N I will at some point want to pull out rows N and 0 and then a bit later pull out 0 and 1. Is it possible to make a MYSQL select wrap around like this?

SELECT `promo_id`
FROM `promotions`
WHERE (`expiry_date` >= '2017-03-21' AND `promo_id` > (SELECT `promo_id`  FROM `promoted_questions` WHERE `promo_id` = 8))
ORDER BY `promo_id` ASC
LIMIT 2

UPDATE for clarity:

At the moment my code just returns up to the end of the table rather than wrapping back around to get the first rows again. Here's an example of what I want:

ID
1
2
3
4
5

If I run the code above with the starting ID as 2 then get IDs 3 and 4 back - perfect. If I run it with the starting ID as 4 then all I get back is 5, not so perfect as I was 5 and 1. Likewise, if I use starting ID 5 then I want 1 and 2 back from the database.


texts: How do I limit the resources that pg_dump uses?

texts:
Is it possible to limit the system resources used up by pg_dump?

I have a fairly resource intensive server that uses a PostgreSQL database. and it is running on FreeBSD 10.2. The server has 8 GB of memory.

I have a backup run with the following command.

pg_dump -h <address> -U <user> -Fc <database> -f <location>

As the backup is being completed the associated postgres process keeps rising in memory usage. It gets to the point of 2.2 GB for that process alone. At this point memory is all but used up and FreeBSD starts killing processes.

I've tried to change how the backup is done. For example, I tried using multiple jobs (and thus changed the format to directory).

pg_dump -j 4 -h <address> -U <user> -Fd <database> -f <location>

No differences. Eventually the memory usage will cap and FreeBSD will kill the database backup (among other processes).

I've also tried tuning some system variables. There was Managing Kernel Resources and also Resource Consumption. These have affected the running postgres processes that are used up by the server, but it had no impact on the process pg_dump uses. It still rose to 2.2 GB before FreeBSD started killing processes.

I've read through the pg_dump manual and haven't really seen anything that will help besides the multiple jobs.

At this point I'm not really sure what to do. Is there a way to cap the resources that pg_dump is allowed to use? I don't mind if the backup is slower. Just don't run the system out of resources where the OS has to start killing processes.


texts: PostgreSQL insert into table (not origin) based on a condition on fields on different tables

texts:
Lets say you have 4 different tables:

table_1 is a table holding the original data:

table_1

id1  |  id2  |  score1  |  score2  |  name_1         |  name_2
-----+-------+----------+----------+-----------------+---------
123  | 89898 | 0.5      | 0.8      |Jack             |Joe
129  | 89999 | 0.55     | 0.3      |Chris            |Don
150  | 90098 | 0.8      | 0.1      |Susan            |Anna
170  | 99898 | 0.7      | 0.14     |Ben              |Lisa

table_2 is a table where we would like to insert fields from table_1 (id1, id2, score1,score2) plus a comment string based on a condition on table_3 and table_4 (where both fields name_1 and name_2 are empty)

table_2 (the result table) should look like:

id1  |  id2  |  score1  |  score2  |  comment_string
-----+-------+----------+----------+-----------------
129  | 89999 | 0.55     | 0.3      | 'removed_because_no_value_in_fields_name_1_and_name_2'

table_3 and table_4 are tables which are holding data of two colomns from table_1 and some rows that have been modified previously. The modified fields are empty. The amount of rows is the same like in the origin table table_1.

table_3:

name_1  |
--------+
Jack
[empty]
Susan
Ben

table_4:

name_2  |
--------+
Joe
[empty]
Anna
Lisa

Note:
[empty] means that the field is empty. ids are not representing primary keys. The result table (table_2) should only contain the fields id1, id2, score1,score2 plus a comment string if the fields with the same row number in table_3 and table_4 are empty.

How to achieve this using PostgresSQL 9.1?


texts: Large differences between estimated and actual row counts but stats are OK

texts:
I'm having a problem with a Function which is taking 10 times longer to run than it used to and high CPU usage. And I'm puzzled by what I'm seeing in the execution plan with some index seek operators showing large discrepancies between actual and estimated row counts. For example an actual row count of 61 and an estimated row count of 269,445.

When I looked at the stats for the index associated with the highest-cost operator in the plan, its sampled rows count was about 10th of the actual rows so I ran a Full Scan. However that's made no difference. Now sampled rows and actual rows are the same but I'm still seeing large discrepancies between actual and estimated row counts for the same index. I was wondering if the high estimate was causing the plan to request a much higher memory grant than was needed however I can't understand where this high estimate is coming from so I was hoping someone might point me in the right direction.

Gordon.


texts: Select with a preffered where condition, but if missing use another

texts:
I have the following select in SAP HANA DB:

SELECT name, text, lang FROM texts
  WHERE name IN ('name1', 'name2', 'name2')
    AND lang IN ( 'S', 'E' )
  INTO TABLE @DATA(lt_texts).

It will select multiple lines for a given name if translations exist for different languages.

How do I say that I want texts with language = 'S', but if it doesn't exist, then select ones with lang = 'E'. I want it in a single request to the DB and no processing on application level.

Thank you in advance


texts: How to resolve TNS error in Oracle when using an ODBC DSN

texts:
I have come across this error when I was trying to insert data from front end (using JSP) to back end (Oracle 10g):

java.sql.SQLException: [Oracle][ODBC][Ora]ORA-12154:
                       TNS:could not resolve the connect identifier specified

Steps followed:

went to Microsoft ODBC
User DSN
Enter DNS ,TNS SERVICE NAME, USERID
When I click on "Test connection", the result was "Connection Successfull"

When I run my frontend JSP which has a text box and a button, I get the error mentioned above when I click on the button.


texts: When does SQL Server warn about an Excessive Memory Grant?

texts:
What are the conditions that produce an "Excessive Grant" execution plan warning?

  The query memory grant detected "ExcessiveGrant", which may impact the reliability. Grant size: Initial 5128 KB, Final 5128 KB, Used 16 KB.

SSMS

Plan Explorer

Showplan xml

<Warnings>
    <MemoryGrantWarning GrantWarningKind="Excessive Grant"
        RequestedMemory="5128" GrantedMemory="5128" MaxUsedMemory="16" />
</Warnings>


texts:
We have a slow overnight process which I was asked to look at with the top waits being PAGEIOLATCH_SH and WRITELOG

I have been collecting information from sys.dm_io_virtual_file_stats and can see that I have extremely high readIOStalls but with a low latency of 13ms.   The same is also true for the log file in that it has a large amount of writeIOStalls but with a latency of 1ms

I also collected some memory information and PLE was in the thousands on average with this being a 32GB system I would expect that apart from in one 30min period where it drops right down to 30 before climbing sharply again, at this time lazy write/sec increases to 50 also before reducing to 0.

My question is I would have expected high IO stalls to have associated high latency values.  With this not being the case is the issue memory related or could it be a blocking issue, which direction do I go with this?


texts: Database sharding, denormalization and synchronization

texts:
In sharding, it is advisable to follow a share nothing, shard everything approach. Essentially, this translates to: data that are to be used together are to be stored together (i.e. in a single shard). This also fits in nicely with the split your data at the application level philosophy.

But it is not always practical to completely denormalize the data. Consider for example an eCommerce site. All data corresponding to an user should ideally be kept together. However, some data, like product availability will need to be synchronized throughout. In some cases, like the aforementioned one, this sync needs to happen real time. As an example, suppose a user has added products to their wishlist. In principle, all data about the wishlist items should be kept together with the rest of the user's data. However, multiple users can wishlist the same item, and other users can also buy the item, thus affecting its availability. Thus the information needs to be shared and sync'd in some way. The complexity and performance hit that would come from multiple joins is also well known.

To make the issue even thornier, it is to be expected that in the above example, or in any case requiring proper ACID transactions, the sync has to be practically real time. It might be acceptable if the number of likes is updated with a delay but it would be bad business for an eCommerce site to show delayed product availability data!

What's the best approach in cases like this?

One preliminary line of thought was to use a push/notification mechanism for such fields as availability, transaction status, etc. But I am really keen to hear from the experts. Obviously, I've read about eBay's architecture on High Scalabilitybut it doesn't go into this level of implementation detail.


texts: Is there any database system that allows to have different schemas for different users but the same content for all users?


texts: SQL Server - Ignoring object dependencies to copy a DB (despite some column referenced in a stored procedure no longer existing)

texts:
First of all apologies for the novel but there's a lot to explain...

We have over 100 databases for our product, 1 per server, each of which has variations on other ones (ideally they should all be exactly the same, but this is where over 20 years of product development gets you). For reasons I cannot say my task is to write a script which imports ALL data for a specific client from their DB. There can be many clients in a single DB so we have to detect if customerID is exists on each table and filter on that column if it does. I am using linked servers, a SQL Server Agent job, stored procedures, a cursor for all the tables and dynamic SQL to get as far as importing all of the data using select * into. The job simply drops / recreates the DB on the test server, sets compatibility to 80, alters the DB to match the settings of the source DB, creates the tables and then the stored procedures / triggers / functions / etc. This works perfectly up to creating the stored procedures, triggers, functions etc.

Basically there is one table, on the live server we're running this against in our tests, which used to have columns 'insertedpersonid' and 'updatedpersonid', but for reasons unknown these can now only be found on one server to my knowledge (and there may well be other tables where this is an issue on other servers, so simply creating the columns on the servers which don't have them is not an option). There is a stored procedure which references these columns, whether or not they exist, so when I try to create the stored procedure from a DB which does not have these columns the script just kinda falls over. I'm using try catch around every stored procedure to get it to go past the erroneous stored procedure, but I'm not happy with that as the erroneous stored procedure is not created and therefore the DB is not an exact copy of live. I could also be missing other errors in this way.

I have done a lot of searching on this topic, but cannot find any way to ignore object dependencies. Does anyone know of a way to do this?

Also if you can think of any better ways to do any or all of this then please feel free to say so. Note that this will be used by several teams around the world through a web interface (select the server > select the client > click import), so it can't be a simple backup / restore onto another server (firstly not all teams have access to the server and if we import 10 clients then in some cases the DB size will exceed 300GB, which simply won't fit onto the server we're importing to).

Lastly I am afraid I cannot give any detail on what version of SQL Server we're using as there are over 100 servers, some of which are on different versions of Windows Server. We're not happy that the testing server may not be on the same OS or SQL Server version, but it's the way it has to be. In the ideal world we'd have 1 server per SQL Server version per OS.

Thanks in advance.
Regards,
Richard


texts: Simple way to export and import data in SQL Server

texts:
I have two SQL Server instances, each with a database.

They are not on the same network and not connected to the Internet and the only way to get data between them is with a thumbdrive.

I want to export a table from one of the computers, put the exported data on a thumbdrive, then import the data into the other computer.

How do I do that?

The data has NULLS so I am afraid a flat file transfer will change the data.  That is, a NULL is converted to an empty string.

The other solutions I have found require using linked servers or otherwise keeping the data in the databases.

In DB2 this is easy because I run a EXPORT command that exports to a ".idx" file and then IMPORT command that imports the data.  This command also defines the table.

This data is only 23,000 rows.


texts:
I'm going to dump a considerable big datatabase (7gb) from a MySQL 5.1 instance running on Windows into a .sql script and import it into a MySQL 5.5 instance, also on Windows (another machine). I'd like to be able to monitor the process of both operations, since I believe they are going to take some time to execute.

Apparentely, this feature has not yet made into the target MySQL versions (http://stackoverflow.com/questions/8806435/how-to-implement-a-mysql-dump-restore-progress-bar). I also checked the solution suggested here, but it seems a lot of work (and also inserting more variables to this sensitive operation) to do something that should be simple.

By the time, ins't there any simple solution to monitor, somehow, the progress of these operations?


texts: Select Statement to generate result, for group by per second and link with Table

texts:
I tried to search in existing queries but didn't find any related thread.
I would like to run a query in user table (for example), and like to get output result on per second basis. My query gives me zero (0) result if no second found.

Here is my query:

select
            call_start = convert(varchar(50),dateadd(SS,datediff(SS,'09/29/2014',[session_datetime]) + 1,0),108),
            rows = count(*)
  from [User_Table]
      where [session_datetime] >= ('09/29/2014' + DATEADD(mi, -3, CONVERT(varchar(50), GETDATE(),108)))
    and [session_datetime]<= ('09/29/2014' + DATEADD(mi, -1, CONVERT(varchar(50), GETDATE(),108)))
     group by convert(varchar(50),dateadd(SS,datediff(SS,'09/29/2014',[session_datetime]) + 1,0),108)

and current output/result of the query is like:

15:10:06    1
15:10:07    1
15:10:10    1

Here you can see that for 15:10:08 and 09 there is no entry shown, because no record exists in the database. How can I bring these entries as well with zero entry, like this:

15:10:06    1
15:10:07    1
15:10:08    0
15:10:09    0
15:10:10    1


texts:
I need to design tables for a laboratory test.

I will have Lab test name, and its component to be saved. The component can be changed with time. I need to design a UI for update.  Also each component's data with name, min, max value etc. needs to be saved in tables.

I already have Person table for storing PersonID there.

How can I design this more efficiently, Any help. Thanks.


texts:
We use the tool etckeeper to keep track of the configuration of servers.

I would like to dump the configuration of pglogical to files for this purpose.

I tried this:

db@host:~$ pg_dump -t 'pglogical.replication_set'

But the output looks empty:

--
-- PostgreSQL database dump
--

-- Dumped from database version 11.5
-- Dumped by pg_dump version 11.5

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- PostgreSQL database dump complete
--

How can I dump the configuration of pglogical?

Use Case:

dump it hourly and store it in git to see what was changed.
compare two hosts to see if the configuration is equal.


texts: Oracle - MUST_RENAME_THIS_LOGFILE in logfile name

texts:
I'm creating a data guard using hot backup from the primary server.
In my restore I've used the following commands to restore:

run{
set newname for database to '+DADOS';
restore database;
switch datafile all;
switch tempfile all;
recover database;
}

After successful restore and recovery my redo logfile names has the name below:

select member from v$logfile;

+DADOS/MUST_RENAME_THIS_LOGFILE_1.4294967295.4294967295
+FRA/MOOTDB/ONLINELOG/group_1.287.1040583755
+DADOS/MUST_RENAME_THIS_LOGFILE_2.4294967295.4294967295
+DADOS/MUST_RENAME_THIS_LOGFILE_3.4294967295.4294967295
+FRA/MOOTDB/ONLINELOG/group_3.259.1036639239
+DADOS/MUST_RENAME_THIS_LOGFILE_4.4294967295.4294967295
+FRA/MOOTDB/ONLINELOG/group_2.288.1040583761
+DADOS/MUST_RENAME_THIS_LOGFILE_5.4294967295.4294967295
+FRA/MOOTDB/ONLINELOG/group_5.295.1042634101
+DADOS/MUST_RENAME_THIS_LOGFILE_6.4294967295.4294967295
+FRA/MOOTDB/ONLINELOG/group_6.297.1042634113

Why they have the "MUST_RENAME_THIS_LOGFILE_"? isn't automatic?

In my standby I'm using the db_log_file_name_convert and log_file_name_convert

log_file_name_convert = +DATA, +DADOS
db_file_name_convert = +DATA, +DADOS

Other issue is that some tempfiles come just with the DG name (see last line below).

select name from v$datafile union all select name from v$tempfile;

+DADOS/MOOTSTB/DATAFILE/system.266.1042731967
+DADOS/MOOTSTB/DATAFILE/sysaux.292.1042731967
+DADOS/MOOTSTB/A249914E10CA3494E0532900A8C0EAA4/DATAFILE/system.278.1042732081
+DADOS/MOOTSTB/A249914E10CA3494E0532900A8C0EAA4/DATAFILE/sysaux.296.1042732081
+DADOS/MOOTSTB/DATAFILE/users.309.1042731967
+DADOS/MOOTSTB/A249914E10CA3494E0532900A8C0EAA4/DATAFILE/undotbs1.320.1042732081
+DADOS/MOOTSTB/DATAFILE/undotbs2.283.1042731967
+DADOS/MOOTSTB/A249E748BB047773E0532900A8C02088/DATAFILE/system.257.1042731811
+DADOS/MOOTSTB/A249E748BB047773E0532900A8C02088/DATAFILE/sysaux.284.1042731809
+DADOS/MOOTSTB/A249E748BB047773E0532900A8C02088/DATAFILE/undotbs1.289.1042731811
+DADOS/MOOTSTB/A249E748BB047773E0532900A8C02088/DATAFILE/users.267.1042731811
+DADOS/MOOTSTB/A249E748BB047773E0532900A8C02088/DATAFILE/soe.311.1042731809
+DADOS/MOOTSTB/A249E748BB047773E0532900A8C02088/DATAFILE/teste.308.1042731811
+DADOS/MOOTSTB/A249E748BB047773E0532900A8C02088/DATAFILE/undo_2.304.1042731811
+DADOS/MOOTSTB/DATAFILE/undotbs1.302.1042731967
+DADOS/MOOTSTB/TEMPFILE/temp.319.1042736311
+DADOS/MOOTSTB/A249914E10CA3494E0532900A8C0EAA4/TEMPFILE/temp.318.1042736323
+DADOS  <<<<<< is a tempfile


texts: What Open Ports are Necessary for SQL Server Log Shipping to Secondary on the Internet

texts:
Our secondary server will be on the other end of an always on IPSEC tunnel. We need to instruct our customer's firewall/VPN/Server team (they are responsible for the primary).

What I've gathered so far, is that I need for the log files to be copied to the secondary, I need CIFS ports opened:

ports 135,137,138,139,445

Per this question, I need to also make sure the SQL Server is NOT set to dynamic:

https://stackoverflow.com/a/10540007/1192939

and it seems, all that's required beyond that would be for 1433 to be available:

http://sqlmag.com/sql-server/sql-server-tcp-and-udp-ports

Am I leaving out anything?


texts: How do I prevent my Windows Server from being automatically rebooted by SQL Server updates?

texts:
Some SQL Server updates have happened outside the maintenance window, which have caused the server to be rebooted. How can I stop this from happening again in future?
Below is what I can see when looking at the Windows Update Settings:


texts: how to use mysql to delete only the first ten table records?

texts:
I'm currently using php and mysql together.  What I want to try is if I have ten "stable" records for the database and in the same .php file I have some text boxes so I can type in then what I typed will be saved into database but each time I goes to the page only the ten records will be deleted and recreated and the ones I entered will NOT be deleted.  Is it possible?  I know sounds weird but I'm reading the book and there's such task.  Well the task didn't mention clearly if all should be deleted or just the ten "stable" records.

Am I not making much of a sense here?

There are like quite lots scripts for what I have already done so I didn't paste the codes here and wonder if I can get a concept first.


texts: Does MongoDB produce a single file like sqlite does for an individual database? Can it?

texts: Does MongoDB store it's data like SQLite does at all?  A single file.  From what I could see it is using "wiredTiger," but I can't find a specific file for my database I created.


texts: Correct way to write this view? Total sales/purchases/profits by day

texts:
I'm currently trying to create a view that lists a sum of sales, purchases and profits done by each person per day. This data comes from 3 tables:

transaction:
idtransaction (pk)
user_iduser (fk)
item_iditem (fk)
total_price (bigint)
time(datetime)
transaction_type (enum: 'buy', 'sell')

profit:
idprofit (pk)
transaction_idtransaction_bought (fk)
transaction_idtransaction_sold (fk)
quantity_profit (int)
profit_unit (bigint)

calendar:
days (date)

Calendar table is a simple table created from a stored procedure. It consists on every date between 2000-2050. Profits are already calculated from the application side, the profits table simply stores the results.
 Note that different users MAY participate in the same 'profit', (e.x user 200 buys an item and user 201 sells it. Again this is handled by the application side). In this case you'd increment the value of sales and profit to user 201 and the value of purchases to user 200.

I'm trying to create a view that outputs this (a snapshot of sums for each day for each user)

date      userid  total_bought  total_sold  total_profit  profit_margin
01-01-2016   200   150000       2000000      50000         0.20
02-01-2016   200   512          14515        456           0.05
03-01-2016   200   150000       1000000      5000          0.14
04-01-2016   200   512          12515        4556          0.07

and so on (do the same for every user and date, so in total the number of rows should be users x dates)

However my implementation is outputting wrong data.

create view v_history_details as

select calendar.days as hday,
users.iduser as user_id,
sum(t1.price_total) as total_buy
sum(profit.profit_unit*profit.quantity_profit) as total_profit,
(sum(total_profit)/sum(total_buy)) * 100 AS margin

from
calendar

JOIN transaction t1 ON date(t1.time) = calendar.days
JOIN transaction t2 ON date(t2.time) = calendar.days

JOIN users on t1.iduser = users.iduser

where t1.transaction_type = 'Buy'
and t2.transaction_type = 'Sell'

GROUP BY calendar.days

order by calendar.days ASC

The issue comes from joining with the transaction table twice. I need a way to split the purchases and sales data but at the same time I also need them in the same view. I'm kind of new to all this

I'd really appreciate some guidance on how to structure this view here.


texts: Why I am seeing NULL value in a table where the schema defines as NOT NULL field?

texts:
So I am checking a friend's project. I got a table where every field is defined will NOT NULL constraint but I am still finding so many fields are NULL. I cannot understand what might be the reason to happen this kind of phenomenon?

Because as I have understanding if you pass NULL value in a field which is defined as NOT NULL than it does not let you submit that entry.

Thank you.

P.S. This is a very big, perfectly working pharmacy management system.


texts: Getting only 4 rows per idea from ideas table

texts:
i have a table of ideas that stores responses from evaluators and stores as one row per evalautor
For ex : Evalautor 1 evaluated idea A then in table record gets added as
IDEA A, COMMENTS , EVALUATOR ID
as single idea have 4 evaluator response and i need to show data in this format
IDEA ID ||. EVALUATOR 1 || EVALUATOR 2|| EVALUATOR 3 || EVALUATOR 4
1 --           Comments --      Comments  --    Comments   --      Comments
Need assistance in creating logic to get information in format given above.
Query i am running :
SELECT * from Toy_ideas GROUP BY evaluator id
but getting results as
Ideaid -- comments -- evaluator id


texts:
I have a column with about 100 records in datetime (for example 2004-08-09 12:20:41:000) and I would like to convert all records or update same time just date only with format mm-dd-yyyy.

Thank you.


texts:
I have recently deployed some SSIS packages. I made a job to run them one after another. However, I am now allowed to use MSDTC. So I chained the jobs together like I have done before, but now I am faced with an error message about DCOM. All Internet research points to insufficient permissions on the MSDTC component, but I am worried this is going the wrong way.

Any idea how I can "reset" the need for MSDTC for this package?


texts: Once the work_mem , temp_buffer or maintenence_work_mem is full where does the PostgreSQL writes to the disk, what is the location of temporary files by default


texts:
Do you know what the best practices would be regarding SSAS and having real-time reports.

I am not sure if we should be pulling data off our live database or using SSIS to move data periodically to a Data warehouse database before re-processing the cube.

Any advice on what the best approach is would be greatly appreciated.


texts: Handling case-insensitive queries with case-sensitive data

texts:
What are some practices for designing a table, or queries, when it comes to case-sensitive data and case-insensitive queries?

For example, I have a Products table that holds household goods, and presumably because I intend to display this data on a client, I would like to have the original names.

If one of the products is called "Vacuum Cleaner" and someone's doing a search through the database for vacuums, I wouldn't want a term like "vacuum" to not find this particular vacuum.

Currently I am using Postgres so I could probably get away with the ILIKE operator, but if a database system didn't support this kind of operator, what would be some alternatives?


texts: Massive random reads on a huge mongodb database but no cache misses?

texts:
I do some performance testing for MonogDB. I have lots of RAM (2TB) and a database which is completely in RAM with 250GB, so for sure all indexes are too (actually there is only one on _id since I ycsb tests).

But what can be the reason that I do see almost no cache misses? I would expect plenty since I have ycsb setup to randomly access the db with a recorccount of 1 000 000 ...  This can not fit into the cache even with L4 = 600 MB.....


texts: mysqldump 5.5.25 use swap instead of memory

texts:
mysql version 5.5.25, while use mysqldump to dump big tables, it eat lots of swap while there is lots of free memory.

# free -m
             total       used       free     shared    buffers     cached
Mem:         32177      31427        749          0        208      10747
-/+ buffers/cache:      20471      11705
Swap:        16002      12952       3049

# cat /proc/sys/vm/swappiness
5

#[mysqldump]
quick
max_allowed_packet = 16M


texts: How can I restrict the version of the target DB instance in a SSIS package?

texts:
How can I restrict an SSIS Package to only execute on SQL Server 2012?

Is there a built-in way to do this (metadata?), or do I need to do a query?

(Possibly belongs in a new question):

I can get the version using SERVERPROPERTY('ProductVersion'), but how do I verify that it is like '11.%'?


texts: PostgreSQL and SQLite on one server - possible problems/conflicts?

texts:
So, I want to install SQLite on a server where I already got an essential PostgreSQL database.
Could this lead to problems or can I do that without any thoughts?


texts:
Any idea what's wrong with the query

SELECT DISTINCT Table1.Description
FROM Table1
ORDER BY Table1.Description DESC;

I'm running this on MsAccess on a table where all fields are Memo. There are no indexes, primary or anything like that just a bunch of Memos. I'm getting the error Reserved error (-1060); there is no message for this error.

If you think my query is OK please let me know because I just had a problem with MDAC being corrupted and had to reinstall so it could be related to that.

Edit: OK I narrowed it down so you guys can try to repro:

Create a blank database (table Table1 and ID field will be there by default) in MS Access.
Add a field called Field1 of type Memo
Add some random data and
Try to run this query: SELECT DISTINCT Field1 FROM Table1 ORDER BY Field1

If you SELECT * it will work but won't be uniquer because it will consider the ID field.

If you don't get an error please let me know because that would mean my database engine is messed up.


texts:
I have a school assignment to create a lost and found schema.

An admin can log a found item (someone turned it into the lost and found office)
A user can report a lost item
A user can claim a found item

Would the below schema work or should I somehow merge lost/claim into one table?


texts:
I'm using SQL Server 2005 and I need to save every SQL agent job in single 'create in' *.sql files. The problem is that I got about 20 SQL Server with 15 (up to 51) SQL jobs.

How can I export these jobs easy in single files?

If I mark all jobs they will be saved in a single file. It would take to long to manually export every single job to a file.

Did someone have an idea?


texts:
I have a local installation of Oracle
Before i can connect directly via SqlPlus
but now when i try to connect i have this error :

  ERROR:
  ORA-01034: ORACLE not available
  ORA-27101: shared memory realm does not exist
  Linux-x86_64 Error: 2: No such file or directory
  Additional information: 3640
  Additional information: 684464879
  Process ID: 0
  Session ID: 0 Serial number: 0

Can you tell me what is the cause of this Error ?
how can i solve it ?


texts: Index with high fragmentation percentage

texts: I have a very large table with enormous percentage of index fragmentation( almost 98 % !). Obviously, it needs index rebuild. Main problem is my SQL Server is in Standard edition, thats why I cannot perform online index rebuild. The table has very high production priority and I cant afford to get it offline. I want to try index reorganize several times, as it can be done online. My question is: do you think several reorganizes will be efficient way in order to low the fragmentation percentage ?


texts: Running reports across multiple MySQL databases with the same schema

texts:
Every client of our online services has its own MySQL database. All databases have exactly same schema, however, a number of databases is constantly changing as we archive db for inactive client's as well as new db gets created after every signup.

Is there a way to run analytics in this kind of environment? There seems to be no way to run query across multiple databases. The same applies to ETL solutions that we have investigated.

Merging databases and updating app code seems like a major effort. Create merged db for reporting purposes using db triggers might be the way to go.

Currently, if we need to run some reports across db, we use PHP script to execute query on every single client db which is very inefficient.

Any recommendations?


texts: Sync MySQL schemas between different databases

texts:
I have a couple of databases that have the exact same structure than my "master" database, but after a couple of months, the developers did small changes (like, add another column, change a datatype to fix an error, etc) and they forgot to replicate it overall databases.
After some research, I found out that mysqldiff could generate the queries to update the databases left behind. But since I am using MySQL 8, it doesn't work (ERROR: Query failed. 1146 (42S02): Table 'mysql.proc' doesn't exist), and the project (mysql-utilities) looks like got abandoned (the last commit is from 2016)...
Does anyone know a free alternative for mysqldiff? I just need to sync the databases, not the data. I am asking because most questions that I looked up where a little bit old.


texts: How to tell from the OS whether Oracle instance is a standby, assuming no DB access?

texts:
One can easily check via SELECT database_role FROM v$database; whether your Oracle database is a primary or standby database.  Is it possible to make the same determination without database access, but only having (Unix) host access?

Oracle DBAs are very used to doing things like ps -ef | grep pmon or ps -ef | grep tnslsnr to quickly check if things are running, and these commands require no DB access.  They can even be run from an unprivileged (non-root) Unix user who doesn't own Oracle.  So thinking along those lines, I have 90% of it solved with things like ps -eo args | grep mrp0, but that falls short.  It only works if the standby is mounted and doing managed recovery.  It doesn't work if (i) the standby is simply mounted without starting MRP, or (ii) if the standby has been opened read-only.

So to recap, here are the parameters:

Goal is to determine whether a running DB instance is a Primary or Standby.
No access to the DB (e.g.: via SQL*Plus, JDBC, etc), but you have server access.
Therefore can only use Unix tools (ps, lsof, /proc, awk, ...)
Nice if can be done from unprivileged (non-root) Unix user.
Triple nice if can be done from any unprivileged Unix user (i.e. not the Oracle owner).

Note, I'm running Oracle 11gR2 & 12c on AIX, but I'll gladly take any *nix solution.

Note2, I don't care about the quasi-solution where as root you simply su to the Oracle owner and sqlplus / as sysdba. I want to do this without a database connection. No V$ views allowed, no X$ tables allowed.


texts: How can I tell how many Cores SQL Server, at peak, it is using?

texts:
I've seen this question here (and elsewhere), but it doesn't really answer the question I'm trying to answer.

How can I tell how many Cores SQL Server is actually using?

I already know how many cores/CPUs are physically assigned to SQL Server. However, I want to know how many of those cores are, at peak, REALLY being used (ideally, I'd love to know percentiles, utilization %s per core, etc., but I'll take what I can get).

We'd like to migrate this instance to another machine and if we can reduce the number of cores assigned that could free up some core licensing for us. However, I don't want to "downsize" only to find out that our new SQL box falls over because it really needed those extra cores. We will be doing load testing, but the more info. we have around this, the better.

EDIT: I ended up just setting up some dashboard software which makes it easy to see/break out CPU usage (could have used PerfMon).


texts: Getting a list of active objects that use cursors but don't explicitly close/deallocate them

texts:
Sometimes our developers will write a query that uses cursors, but doesn't close them out explicitly. I'm trying to generate a list of active objects in my production database that use cursors but don't explicitly close/deallocate them. To do this I've written a simple statement that does the job, but is extremely slow:

select distinct name,
            definition
from   SYS.SQL_MODULES
   inner join SYS.OBJECTS O
           on SQL_MODULES.OBJECT_ID = O.OBJECT_ID
where  SQL_MODULES.DEFINITION like '%open%'
   and SQL_MODULES.DEFINITION like '%declare % cursor%'
   and ( SQL_MODULES.DEFINITION not like '%close%'
          or SQL_MODULES.DEFINITION not like '%deallocate%' )

Currently this takes something like 3 minutes to run. Is there a better way to get the information I'm looking for?


texts:
I have a table in PostgreSQL with a timestamp and a value.
I would like to interpolate the missing values under "lat".
The value under "lat" are tidalheights above a datum.
For the purpose it is ok to interpolate the missing value linear between the two known values.
What is the best method to do so in PostgreSQL?

Edit 20200825
I have solved this problem in a different way using the QGIS fieldcalculator. Problem with this method: it takes a long time and it the process runs client-side and I would like to run it directly on the server.
In steps, my workflow was:

The interval between the recorded "lat" values is 10 minutes. I calculated the increment per minute between two recorded values and stored this in an extra column called "tidal_step" at the recorded "lat"-value.  (i stored the timestamp also as an "epoch" in a column)

In QGIS:
tidal_step =
-- the lat value @ the epoch, 10 minutes or 600000 miliseconds from the current epoch:
(attribute(get_feature('werkset','epoch',("epoch"+'600000')),'lat') -
-- the lat value @ the current
attribute(get_feature('werkset','epoch',"epoch"),'lat'))
/10

for the first two values from the example image that results in:
(4.95 - 5.07) /10 = -0.012

I determined the amount of minutes of the "lat"-value to be interpolated, past the last recorded instance where a "lat" value was recorded and stored this in a column: "min_past_rec"

In QGIS:
left(
right("timestamp",8) --this takes the timestamp and goes 8 charakters from the right
,1) -- this takes the string from the previous right( and goes 1 character left

for the first value in the example:
2019-01-01 00:15:15 returns: '5'
This is 5 minutes past the last recorded value.

I interpolated the missing values by adding the ("min_past_rec" * "tidal_step") to the last recorded "lat" value and stored this in in column called "lat_interpolated"

In QGIS
CASE
WHEN "lat" = NULL
THEN
-- minutes pas the last recorded instance:
("min_past_rec" *
-- the "tidal_step" at the last recorded "lat"-value:
(attribute(get_feature('werkset','epoch',
("epoch" - --the epoch of the "lat" value to be interpolated minus:

left(right("timestamp",8),1) * 600000 -- = the amount of minutes after the last recorded instance.
+ left(right("timestamp",6),2) * 1000) --  and the amount of seconds after the last recorded instance.
),'tidal_step')) +

-- the last recorded "lat"-value

(attribute(get_feature('werkset','epoch',("epoch" - left(right("timestamp",8),1) * 600000 + left(right("timestamp",6),2) * 1000)),'lat'))

With data from the example:
2019-01-01 00:17:33:
"lat_interpolated" = "min_past_rec" * "tidal_step" + "lat" =
7*-0.012 + 4.95 = 4.866

delete obsolete columns from database

Which statements/script should I use in PostgreSQL to perform the same task?


texts: MongoDB - Store date and optional time as a field

texts:
I'm developing a web service using MEAN stack + Mongoose, and one of the things I need to implement is storing a date as an object's field. But the thing is, the time should be optional, e.g. the user should be able to save an object filling in only the date without time.

How can I achieve this result? And how can I search on this field later?


texts:
Given the following code, from the answer to this question, how can I adapt it for paging?  I've read that Common Table Expression is a good way to do paging, but since I implemented CTE to solve a problem that was already over my head, I really have no idea where to start in merging the two solutions.

;with cte as
(
  SELECT DISTINCT d.AppId,
    d.AppName,
    d.AppType,
    Tags = STUFF((SELECT ', ' + t.TagName
                  FROM AppTags t
                  where d.AppID = t.AppID
                  FOR XML PATH (''))
                  , 1, 1, '')
  FROM AppDetails d
  WHERE d.AppID = '1'
)
select *
from cte
where tags like '%test1%'