texts: Insert/ Update Scalability (more records, continually longer lookups --- solution?)

texts:
posted this before but never received a clear answer.

I'm working on an ETL project and am basically feeding JSON records of "tickets" into a MySQL database. (actually come to think of it, may be migrating to SQL server, but I'm not sure that will be a huge difference).

These JSON records are the "current data" for each ticket.

So it will say, for example:

Ticket_Id: 1
Ticket_Subject: Hi
Ticket_Issue: Greeting
Ticket_Status: Open

In the future I might get a record like:

Ticket_Id: 1
Ticket_Subject: Hi
Ticket_Issue: Greeting
Ticket_Status: Closed

These update records are stored in the order in which they are made ... so it's pretty easy to sweep through each day, parse the JSON, and perform an Insert/ Update into a SQL database/ table with Ticket records.

Here's the issue. There's currently 100k tickets in the database. In 6 months there may be 500k tickets.

The ETL process is already fairly slow, and I need to work on optimizing each step. For now, I want to focus on the SQL Insert/ Update.

Currently it's lookup against 100k rows ... soon it will be looking up against 500k ... eventually 2 million ... you get the idea. This is NOT scalable currently.

Luckily, I know that these tickets become "archived"/ uneditable after 30 days of inactivity. They become "closed" permanently and will never be changed or updated again.

Thus, there may be a logical solution that somehow transfers, locks, moves, partitions, ignores .... I simply don't know ... these "closed/ archived" tickets ... so I only have to perform daily lookups against 30k active tickets any given day. Not a million.

This seems like an extremely common issue (virtually ANY large insert/ update conundrum) .... but I have not found many simple ideas online.

I'm wondering what is a simple, maintainable solution to this problem. Even if the "simplest" is sadly two different tables or what have you. I have no problem doing a "union" select statements --- in actuality the 'select' query times are already super fast and not a problem. The problem is the insert/ update/ lookup/ write times.

Any ideas?


texts: Date model for commenting system stored in a key-value NoSQL DB?

texts:
I'm trying to leverage Cassandra's strong support for time series data to build a commenting system.

So far I'm thinking that each row would be a particular topic, and each comment would be a new column, stored in event_time descending order such that I could get the latest replies.

CREATE TABLE topic (
    topic_id text,
    user_id text,
    event_time timestamp,
    data text,
    PRIMARY KEY (topic_id,event_time),
) WITH CLUSTERING ORDER BY (event_time DESC);

However, if I want to get the last 10 comments made by 'John' for example, this seems to be difficult with the current model.

Could anyone give any advice on how I could retrieve 'John's comments efficiently, and if I would have to change the data model?


texts: PostgreSQL Backup and restore, sizes are different

texts:
I Have a Wordpress database in my postgreSQL, in phppgadmin this database is shown with the size of 273 MB.

I've ran sudo -u postgres pg_dump -Fp -f wordpress.sql wordpress to do the database backup, the result is a file with just 18MB.

I've changed the name of the original database to Wordpress2 and Then:

I've created a new database called Wordpress and did a restore sudo -u postgres psql wordpress < wordpress.sql  and the result is a database with size of 19 MB, there is something missing?

Below is a print of phppgadmin, with wordpress as the imported backup and wordpress2 as the original database.

AutoVacuum

postgres=# select setting from pg_settings where name = 'autovacuum';
 setting
---------
 on
(1 row)

Indexes Space

postgres=# select pg_size_pretty(sum(pg_relation_size(indexrelid))) from pg_index;
     pg_size_pretty
    ----------------
     2600 kB
    (1 row)

Database Sizes
Original

postgres=# select pg_size_pretty(pg_database_size('wordpress2'));
 pg_size_pretty
----------------
 273 MB
(1 row)

Restored

postgres=# select pg_size_pretty(pg_database_size('wordpress'));
 pg_size_pretty
----------------
 20 MB
(1 row)

I Copied the original database

CREATE DATABASE newdb WITH TEMPLATE wordpress2 OWNER postgres;

And performed a vacuum full in this "newdb"

vacuumdb -f -d newdb -z -v

Result is the "newdb" decreased from 273MB to 17MB

postgres=# select pg_size_pretty(pg_database_size('newdb'));
 pg_size_pretty
----------------
 17 MB
(1 row)


texts: Split/explode comma delimited string field into SQL query

texts:
I have field id_list='1234,23,56,576,1231,567,122,87876,57553,1216'

and I want to use it to search IN this field:

SELECT *
FROM table1
WHERE id IN (id_list)

id is integer
id_list is varchar/text

But in this way this doesn't work, so I need in some way to split id_list into select query.

What solution should I use here? I'm using the T-SQL Sybase ASA 9 database (SQL Anywhere).
But in this way this doesn't work, so I need in some way to split id_list into select query.

Way I see this, is to create own function with while loop through,
and each element extract based on split by delimiter position search,
then insert elements into temp table which function will return as result.

  COMMENTS TO Sebastian Meine ANSWER AND HIS SOLUTIONS :

  Sybase SQL Anywhere 9

  sa_split_list system procedure not exist here, so it doesn't work
  CAST works great

  Sybase SQL Anywhere 12

  sa_split_list system procedure exist and works great
  CAST works great

  For the Sybase SQL Anywhere 9 I have made sa_split_list system procedure replacement:

CREATE PROCEDURE str_split_list(in str long varchar, in delim char(10) default ',')
RESULT(
  line_num integer,
  row_value long varchar)
BEGIN
  DECLARE str2 long varchar;
  DECLARE position integer;

   CREATE TABLE #str_split_list (
   line_num integer DEFAULT AUTOINCREMENT,
   row_value long varchar null,
   primary key(line_num));

   SET str = TRIM(str)+delim;
   SET position = CHARINDEX(delim, str);

   separaterows:
   WHILE position > 0 loop
       SET str2 = TRIM(LEFT(str, position - 1));
       INSERT INTO #str_split_list (row_value)
       VALUES (str2);
       SET str = RIGHT(str, LENGTH(str) - position);
       SET position = CHARINDEX(delim, str);
    end loop separaterows;

   select * from #str_split_list order by line_num asc;

END

  Execute the same way as sa_split_list:

select * from str_split_list('1234,23,56,576,1231,567,122,87876,57553,1216')


texts: MySQL InnoDB transactions with lot of delete instructions

texts:
We have a process that runs frequently and does the following steps on a mysql database of type innodb.

SET autocommit = 0;
DELETE FROM table WHERE category='something';
-- every 300 rows or whatever is left in case of last round
INSERT IGNORE INTO table (fields) VALUES (row1),(row2),..,(row300);
    -- check affected rows == number of rows given if not, ROLLBACK;
-- at end of all rows
COMMIT;

This process for some reason seems to expand the ibd file into way more than the actual size. Previously we had one huge ibdata1 that went to about 60gb size, but then we dropped everything, changed the my.cnf to store data into separate files for each table, and still the table on which these transactions are happening just blew up again.

Is there some way to see what the ibd file consists of? Or if we could name the transaction to say transaction_2014_06_30 and then at the end of the erase all information related to this transaction from the idb file?

Also, will using a temporary table help in this process, because I've read that temporary tables use a different ibtmp1 file to store data and are actually lot faster in the recent versions of mysql.


texts: In partition definition filegroup name appears twice, what does that mean?

texts:
short question regarding partitions, consider the following partition definition:

CREATE PARTITION SCHEME [MSAppServerPS_ASWcfEventsTable] AS
PARTITION [MSAppServerPF_ASWcfEventsTable] TO ([PRIMARY], [PRIMARY])
GO

From reading about this here I understand, that this will create the partition on the socalled primary filegroup. Unfortunately in the examples on that page I did not see one example that used the same filegroup name twice.

Can someone explain to me what that means?


texts:
I believe DTC is optional but cannot confirm it (can't find it on MSDN)

Why we need DTC (distributed transaction cordinator) when clusting a single instance (active passive scenario)?


texts: Combined or separated tables: File and Message?

texts:
Is this the best approach to design this table in the purpose of sharing the file online between my customer and I, or should I separate Message table and File table or combined them together?

Combined table approach:
FILE table

ID
CustomerID
FilePath  (files'path from both parties: customer and I)
Content   (Message)
Flag      (Read/Unread + ISent/CustomerSent)
Status    (Viewed,Reviewed,Accepted,and etc)
Timestamp (Current DateTime of file changed)
CreationDate (Date of file created)
Dateline  (Set the deadline of an action corresponding with the file)

Pros: 1 single table

Cons: 2 actions sharing the same table, in each record, either "FilePath" or "Content" could be null in case customer wants to communicate by writing a message without having attachment (FilePath) or customer uploads any files without having any message (Content) written.

2 tables approach:

FILE table

ID
CustomerID
Name
Type
Size
Data
Created

Message table

ID
CustomerID
Message
Attachment
Created

Pro: No Null field

Con: They are serving the same purpose in these two tables but one focusing on Message and others on File.

What would you recommend?


texts: Large update and delete based on another table

texts:
I have a very active PostgreSQL 10.4 DB, dealt with through SQLAlchemy, so SQL is not my forte.

Among its tables, there are master with a primary key id and some nonindexed string column value, and slave with a foreign key mid towards master.id. The table master is just a bit short of 100M rows and is very frequently accessed by the app (usually several times per second, but always just a few rows at a time), while slave has roughly double that and is accessed in a similar manner.

I want to do two things:

Delete from slave and master:

delete from slave
    using master
    where
        slave.mid = master.id
        and master.value in ('some', 'set', 'of', 'values')
delete from master
    where value in ('the', 'same', 'set', 'of', 'values')

SQLAlchemy didn't create any cascade delete rules for master, so I cannot just run the latter of the two queries, as it causes a foreign key constraint violation.
Update master:

update master
    set value=<case expression>
    where value in ('a', 'different', 'set', 'of', 'values')

The number of rows to be deleted is estimated to be few millions (maybe a mil or 2 mils; certainly not much more).

I have another constraint that I'd like to keep (but could ditch it if absolutely necessary): a dry run is done the same way as the live execution, except that I call rollback instead of commit. So, for example, doing the update top would end up in an infinite loop if I kept rolling back after each call.

The above codes work nicely on a small DB used to just test if the code works as intended, but they are super-slow on a copy of the production DB. So, my first issue here is the performance.

My second issue would be locking, because the production is hit a lot and it would be nice to be able to do this seamlessly, without causing any downtime and/or timeouts for the app.

This got me to start thinking about doing deletes/updates in batches, but this goes beyond my knowledge of PostgreSQL (or SQL in general). How can I get these to work the way I want them to?

Edit

After some consideration, I altered the slave tables (there is two of them) to delete on cascade, which solved the deletion problem. There is still an issue of efficient updating, most likely in chunks.


texts: Best practice for copying tables from server to server

texts:
Not being a DBA and not having sysadmin privileges to do a backup/restore, would the following be a good solution for copying a set of tables? (I have a linked server from serverA to serverB)

copyTables

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[copyTables]
AS
-- NOCOUNT ON added to prevent extra result sets from interfering
-- with SELECT statements. XACT_ABORT ON to terminate the transaction
-- and rollback if a run-time error occurs.
SET NOCOUNT, XACT_ABORT ON

BEGIN
    DECLARE @serverName varchar(50), @dbName varchar(50), @schemaName varchar(50)

    SET @serverName = QUOTENAME('serverB')
    SET @dbName = QUOTENAME('db')
    SET @schemaName = QUOTENAME('dbo')

    BEGIN TRY
        BEGIN TRANSACTION

        EXEC [dbo].[copyTable] @serverName, @dbName, @schemaName, 'tbl1', 'copyLog'
        EXEC [dbo].[copyTable] @serverName, @dbName, @schemaName, 'tbl2', 'copyLog'
        EXEC [dbo].[copyTable] @serverName, @dbName, @schemaName, 'tbl3', 'copyLog'
        ...

        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        -- Insert error into log table

        ROLLBACK
    END CATCH
END
GO

copyTable

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[copyTable]
    @serverName varchar(50),
    @dbName varchar(50),
    @schemaName varchar(50),
    @tableName varchar(50),
    @logTableName varchar(50)
AS
-- NOCOUNT ON added to prevent extra result sets from interfering
-- with SELECT statements. XACT_ABORT ON to terminate the transaction
-- and rollback if a run-time error occurs.
SET NOCOUNT, XACT_ABORT ON

BEGIN
    DECLARE @localFullName varchar(200), @localShortName varchar(150),
        @foreignFullName varchar(200), @logShortName varchar(150);

    SET @localFullName = QUOTENAME(@dbName) + '.' + QUOTENAME(@schemaName)
        + '.' + QUOTENAME(@tableName);
    SET @localShortName = QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName);
    SET @foreignFullName = QUOTENAME(@serverName) + '.' + QUOTENAME(@dbName)
        + '.' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName);
    SET @logShortName = QUOTENAME(@logTableName) + '.' + QUOTENAME(@schemaName)
        + '.' + QUOTENAME(@tableName);

    IF EXISTS
    (
        SELECT *
        FROM sys.objects
        WHERE object_id = OBJECT_ID(@localShortName)
                AND type in (N'U')
    )
    BEGIN
        DROP TABLE @localShortName;
        SELECT *
            INTO @localFullName
            FROM @foreignFullName;

        INSERT INTO @logShortName (stamp, [message])
        VALUES
            (
                    GETDATE(),
                    'Table ' + @foreignFullName + ' was copied'
            );
    END
END


texts:
I've an SSIS task which download a couple of files,update various Database tables and archive the files(i have it split in various packages).
The average time to completion is about 2.30 min.

However, i've recently noticed  on few occassions that the SSIS Task does remain in the  runnning state and unable to complete.

What i would like is to receive an email when the average time to completion is passed  and after fail the package .


texts: Join SQL Server table in OPENQUERY on multiple keys

texts:
I need to join an Oracle linked server table (with 1 billion rows) with an SQL Server table (with 100 rows) on multiple keys:

SELECT * FROM OPENQUERY(EDW, 'SELECT * FROM ORACLE_TABLE') A
INNER JOIN SQL_TABLE B
ON A.KEY1 = B.KEY1 AND A.KEY2 = B.KEY2
INNER JOIN SQL_TABLE C
ON A.KEY3 = C.KEY1 AND A.KEY4 = C.KEY2;

But ORACLE_TABLE is very huge and this query takes too much time to run. Is there a way to pass SQL_TABLE to OPENQUERY and do the join on the Oracle server?


texts: Clustered composite key high fragmentation and fill factor

texts:
I've recently made a table to hold the language preferences of my users as follow:

CREATE TABLE [dbo].[systemUserLangPreference](
  [systemUserID] [int] NOT NULL,
  [langID] [int] NOT NULL,
  [preferredOrder] [int] NOT NULL,
  [createdBy] [int] NOT NULL,
  [createdOn] [datetime] NOT NULL,
  [lastActionBy] [int] NOT NULL,
  [lastActionOn] [datetime] NOT NULL,
CONSTRAINT [PK_systemUserLangPreference] PRIMARY KEY CLUSTERED
([systemUserID] ASC, [langID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

There's less than 1,000 rows in there now so performance isn't much of an issue at this time however when I run an index fragmentation report this index and more of my compound keys always comes up as high fragmentation no matter what I do.

Using

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()

This one index will return 80% avg fragmentation even moment after I rebuild it.  I've tried playing with the fill factor with the following results:

Fill 100%; Fragmentation 75%
Fill 80%; Fragmentation 80%
Fill 50%; Fragmentation 62.5%
Fill 10%; Fragmentation 19.44%
Fill 1%; Fragmentation 1.90%

So it seems the lower I go the better? I'm not sure I'm interpreting this right. Since the table is mostly reads (insert on new user and I guess when they learn a language) wouldn't a higher fill factor be better? Why does it lead to immediate fragmentation and is it a bad thing?


texts: MS Access - Creating a report based on a pass-through query that requires a parameter

texts:
I am attempting to make a report in Access that uses parameters as well as a pass-through query.

I'm open to using a temporary table but the guides I can find using temporary tables all use an Insert query which doesn't work with my pass-through query.

I see a lot of mention of creating a recordset from the pass-though query and then copying the recordset to a temporary table but have not had luck location instructions on how to do that.

Some background. I have a PostgreSQL server that is aggregating multiple databases via a foreign data wrapper. Across those databases I have multiple table names that a reused.

I can do a normal pass-through query using the front end query properties but this doesn't handle parameters for the pass-through query.

I can build a form using ADO objects by setting the record set for the form to equal a record set created from a pass-through query. (Set Me.Recordset = MyRecordset) This allows me to create the query string dynamically so I can use parameters! One step closer but not a report.

Reports do not support setting themselves to a record set the way forms do so I'm back to the drawing board.

Any suggestion on how to accomplish my goal? Should I give up and have Postgres store the results in a table, link that table, and call it a day?


texts: Get last Reoganized OR Rebuild index date

texts:
I am currently working on migrating sqlserver 2008r2 to SQL server 2016.The DBA before me used "IndexOptimize" from https://ola.hallengren.com/downloads.html to create the iIndex solution.
I can see the job is schedlued and run on a monthly basis and logic apply is:
if Fragmentation < 20 then reorganize index
if fragmentation > 40  rebuild index
I am planning on using  the Maintenance plans via SSMS in order to reorganize or Rebuild indexes.
How do i find which one has run "reorganize/rebuild", I  can see fragmentation for some table are above 66. Am right in thinking that rebuild  Index is not taking place?


texts: Date is not sorted in reporting services consecutively and correctly

texts:
I am trying to generate a report using the SSRS,but the date is not being sorted

as I want when running the query.

It is being shown as follow (consecutive months are mixed):

28/05/2014

28/06/2014

29/05/2014

29/06/2014

What I need is to have it as follow (all consecutive days in the same month) :

28/05/2014

29/05/2014

28/06/2014

29/06/2014

Note that I am using this expression to convert the date :

convert(nvarchar(50),dbo.mytable.manufacturyDate,103)


texts: Pentaho Data Integration: 'Execute SQL statement' to create trigger

texts:
I'm trying to create a mysql trigger via an Execute SQL Statement step in my transformation. Below are the commands I'm attempting to run.

DROP TRIGGER IF EXISTS standing_actions_swap_before_insert_row_tr;
DELIMITER $$
CREATE DEFINER=`user`@`%` TRIGGER standing_actions_swap_before_insert_row_tr BEFORE INSERT ON `standing_actions_swap`
FOR EACH ROW
BEGIN
    SET NEW.id = CONCAT(NEW.acad_career, NEW.status, NEW.action);
END
$$
DELIMITER ;

Unfortunately, for whatever reason, while the following works in various other db software suites (e.g. Sequel Pro, Aqua Data Studio), I always get an error in Pentaho Data Integration. I've tried omitting delimiters and checking and unchecking the Execute as a Single Statement box - unfortunately, it still no worky.

Any help is appreciated. Thanks!

Edit:

Here's is the stack trace:

2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : An error occurred, processing will be stopped:
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - Couldn't execute SQL: DELIMITER $$
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - CREATE DEFINER=`user`@`%` TRIGGER standing_actions_swap_before_insert_row_tr BEFORE INSERT ON `standing_actions_swap`
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - FOR EACH ROW
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - BEGIN
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 -     SET NEW.id = CONCAT(NEW.acad_career, NEW.status, NEW.action)
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 -
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - CREATE DEFINER=`user`@`%` TRIGGER standing_actions_swap_before_inse' at line 1
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : Error initializing step [Recreate ac.student_actions_swap before_insert trigger]
2015/05/15 16:13:13 - recreate_standing_actions_swap_trigger - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : Step [Recreate ac.student_actions_swap before_insert trigger.0] failed to initialize!
2015/05/15 16:13:13 - Recreate ac.student_actions_swap before_insert trigger.0 - Finished reading query, closing connection.
2015/05/15 16:13:13 - Spoon - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : recreate_standing_actions_swap_trigger: preparing transformation execution failed
2015/05/15 16:13:13 - Spoon - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : org.pentaho.di.core.exception.KettleException:
2015/05/15 16:13:13 - Spoon - We failed to initialize at least one step.  Execution can not begin!
2015/05/15 16:13:13 - Spoon -
2015/05/15 16:13:13 - Spoon -
2015/05/15 16:13:13 - Spoon -   at org.pentaho.di.trans.Trans.prepareExecution(Trans.java:1149)
2015/05/15 16:13:13 - Spoon -   at org.pentaho.di.ui.spoon.trans.TransGraph$27.run(TransGraph.java:3989)
2015/05/15 16:13:13 - Spoon -   at java.lang.Thread.run(Thread.java:695)
2015/05/15 16:13:13 - recreate_standing_actions_swap_trigger - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : Errors detected!
2015/05/15 16:13:13 - recreate_standing_actions_swap_trigger - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : Errors detected!


texts:
I've got a query with a CTE (getting all children of a parent) followed by a main statement using the "IN"-Operator to filter for the CTE's output.

Roughly looks like this:

WITH cte_table AS (...)

SELECT (stuff)

FROM tables JOIN more tables

WHERE ID IN

(SELECT ID FROM cte_table)

The CTE runs fast by itself. The main query runs fast if fed with the CTE's output manually. Fast means that it takes no noticeable time. But when I combine both it takes almost 20 seconds.

Any ideas for solutions? Or, what is probably more important to me, the reason why it could take that long?


texts: T-SQL Check another table rows if match delete them

texts:
I am creating a script in SSIS where I want table A to check if rows exist in table B. If they exist, they must be deleted.

Table A is an archive table named Customer_archive
Table B is a table that will get updated frequently and is named Customer_Information.

The idea is that once a row is stored in Customer_archive, that row will not appear in Customer_information, even after Customer_information has been updated.

It has to check the entire row and not WHERE = 'something'.

Is it possible to do a WHERE clause that is a combination of 2 columns?


texts: Referencing PL/SQL variables in FOR loop

texts:
I have written a PL/SQL script to find the size of a long column in a table.
Just to make the script generic I am passing the table Name and column name as variables , but I am getting an error saying that table or view does not exist. the details are :

ORA-06550: line 8, column 34:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 8, column 11:
PL/SQL: SQL Statement ignored
ORA-06550: line 9, column 42:
PLS-00364: loop index variable 'J' use is invalid
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored

The Script is :

declare
a number := 0;
x number := 0;
i number := 0;
tablename varchar2(100):= 'FILES';
columnname varchar2(100):= 'FILESIZE';
begin
for  j in (select columnname from tablename) loop
  a:=UTL_RAW.LENGTH (UTL_RAW.CAST_TO_RAW(j.columnname));
    i := i+1;
dbms_output.put_line(i);
  x:= x + a;
end loop;
dbms_output.put_line(x);
end;

The table name is FILES. And the column name is FILESIZES.

Can you suggest what I am doing wrong. And what can I do to find the size of long Column??

Thanks.


texts: Separate stored procedures for inserts and updates?

texts:
I have a table in Microsoft SQL Server. Sometimes I need to update, and sometimes I need to insert. I could write 2 stored procedures:

InsertNewPerson
UpdatePertsonById

But I was thinking to write 1 stored procedure instead (SetPerson) which would do both (if there is an ID, it is an update operation , else insert).

Should I create one stored procedure (only one to maintain) or should I create two different stored procedures?


texts: How to select current and next values after applying filters


texts: Does it ever make sense to use RAM Disk to force RAM allocation for tempdb with SQL Server 2008

texts:
If I give all RAM to SQL Server, it will use it's own pattern to determine what RAM to allocate for what.

If I allocate portion of RAM as RAM Disk and put tempdb into that disk it will force RAM
allocation for tempdb. This could be helpful if there are usage scenarios where SQL Server refuses to allocate RAM for tempdb and uses it for something else.

So I wonder if those usage scenarios exist where it makes sense to force RAM allocation for tempdb by using RAM Disk, thus overriding SQL Server decision for RAM allocation.

I was kind of asking this here: https://stackoverflow.com/questions/10199349/can-sufficient-increase-of-available-ram-eliminate-usage-of-tempdb-when-querying, but didn't get desired and specific response, so I am asking it differently now.


texts: What could cause SQL Server to deny execution of a SP at first, but allow it later with no privileges change?

texts:
A user (Windows login) just complained he was denied the execution of a procedure. I went to check and verified he had the privileges to execute it. I didn't change anything (and right now I'm the only one with admin privileges do to so if needed) and after two unsuccessful attempts he tried to run the SP for the third time and it worked.
I have XE configured to catch error messages and it captured twice the error code 229:

The EXECUTE permission was denied on the object 'storedProcedureName',
database 'databaseName', schema 'schemaName'.

Is there any situation where this behavior is expected?

Microsoft SQL Server 2014 (SP3-CU-GDR) (KB4535288) - 12.0.6372.1 (X64)


texts: How do I check that the database master key encryption is valid?

texts: I have a scenario where I'm restoring a db from one server to another. On the source server, the database master key (DMK) is encrypted with both a password and the service master key (SMK). When I go to restore it to the new server, the row in sys.key_encryptions still says that it's encrypted by the SMK. This isn't true though since the SMKs don't match between the two servers. Is there any programmatic way to verify that the DMK is indeed encrypted with this server's SMK?


texts: How can we implement "A student can enroll in up to 4 units in a semester "

texts:
I have a practice problem on Database implementation and am confused. For this database, I need to store enrollment information on students. Information such as which units a student has enrolled into. The relationship between Units and Students is such that a student can enroll in up to 4 units per semester but a unit can be enrolled into by many students. My problem is, how can I implement this limitation of a student enrolling in up to only 4 units per semester in mysql using either CHECKs or triggers?
If it was one to many relationship between Student and Units that would have been straight forward but here we have a one to four(?) relationship?


texts: PostgreSQL: Insert into JSONB key failing with syntax error at or near "->>"

texts:
Running the following query auto-generated by Eloquent's upsert function is throwing a syntax error and I'm not sure why? I couldn't find a supporting statement that says PostgreSQL supports the following syntax, looking for some expert advice on whether this would work.
insert into "plugin_positions"
("created_at", "positions"->>"test", "slug", "tag", "updated_at")
values
('2021-04-10 17:30:40', 0, 'contact-for-telegram', 'rrss', '2021-04-10 17:30:40');

Here's the query that works (which uses the simple column name, and a valid json value):
insert into "plugin_positions"
("created_at", "positions", "slug", "tag", "updated_at")
values
('2021-04-10 17:30:40', '{"test":0}', 'contact-for-telegram', 'rrss', '2021-04-10 17:30:40');

Does postgresql allow inserting into a table if we specify the column as "positions"->>"test"?


texts: Optimal way to record and retrieve a time-dependent value

texts:
I have a table that has transactions for a bus (boarding riders). Given the route ID and the date, I need to look up in another table what service type it was doing that day. Bus schedules change at most every 6 months or so, with most going years unchanged.

Currently the schedule table is defined like so:

CREATE TABLE [dbo].[Routes](
    [ID] [int] NOT NULL,
    [RouteID] [int] NOT NULL,
    [Type] [varchar](50) NOT NULL,
    [StartDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
));

An example might look like:

ID  RouteID  Type          StartDate
--  -------  ------------  ----------
 1      301  Standard      2015-01-01
 2      301  Discontinued  2016-06-01
 3      302  Standard      2015-01-01
 4      302  ParaTrans     2017-01-01

So, if I have a transaction from 2015-04-20 for RouteID 301, I want to get back "Standard", but if the transaction is from 2018-01-20, it should return "Discontinued". For transactions prior to 2015-01-01, it should return NULL (or "", or anything other than a result that might conflict with a valid answer, i.e., "Standard", "Paratrans", or "Discontinued").

Basically, the table represents that route 301 was a standard route between 2015-01-01 and 2016-05-31 (and thus any transactions during that period should be categorized as "Standard"), then it was Discontinued on 2016-06-01 (through current day, implicitly as there is no later schedule change noted), while 302 was a Standard route from 2015-01-01 through 2016-12-31, then a ParaTrans(it) route after.

Route   Type          Start       End
-----   ----          -----       ---
301
        Standard      2015-01-01  2016-05-31
        Discontinued  2016-06-01  Present
302
        Standard      2015-01-01  2016-12-31
        ParaTrans     2017-01-01  Present

Currently, the query to do this looks like this:

SELECT
    TRANSIT_DAY,
    ROUTE_ID,
    (SELECT TOP (1) Type FROM Routes
     WHERE (RouteID = dbo.DAILY_SALES_DETAIL.ROUTE_ID)
     AND (StartDate <= dbo.DAILY_SALES_DETAIL.TRANSIT_DAY)
     ORDER BY StartDate DESC) AS NCTD_MODE
FROM dbo.DAILY_SALES_DETAIL

Questions

What I'd like to know is: Is this the most effective combination of (a) structure of the Routes table and (b) query to achieve this result? In other words, could a more efficient query be used with the existing structure? Could a change to the route table allow for a more efficient query?

Considerations

The table of transactions is imported from a vendor on a daily basis, and so changing the schema of that table is not trivial and it is preferred to be avoided. More importantly, this lookup is used across a number of tables and databases using transactions or other bus related data sourced form multiple vendors; this is just a single example. We have one vendor (and thus one database) for monetary transactions, another for rider count, and still another for performance, and so on, with the route number and date being the only reliably consistent identifier across them all.

The route table has an index of (RouteID, StartDate). Currently there are 56 rows in the Route table and 26M rows in the transaction table. The route table is comprised of 45 routes, and currently there are no routes with more than 2 rows, or one change. There is no limit to how many changes a single route could have, but I include this stat to show that the number is likely to remain small for the foreseeable future.

I can add any required indexes in order to optimize a suggested query. The question is more about finding the best strategy, assuming all reasonable optimizations are made to the considered strategies, than about finding the best optimization of a particular strategy.

db<>fiddle here


texts: Query All Tables In PostgreSQL Database For Text

texts:
I do not have write privilges on the database so I can not create a proc to run.  I was originally going to use this function

CREATE OR REPLACE FUNCTION search_columns(
    needle text,
    haystack_tables name[] default '{}',
    haystack_schema name[] default '{}'
)
RETURNS table(schemaname text, tablename text, columnname text, rowctid text)
AS $$
begin
  FOR schemaname,tablename,columnname IN
      SELECT c.table_schema,c.table_name,c.column_name
      FROM information_schema.columns c
      JOIN information_schema.tables t ON
        (t.table_name=c.table_name AND t.table_schema=c.table_schema)
      WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
        AND (c.table_schema=ANY(haystack_schema) OR haystack_schema='{}')
        AND t.table_type='BASE TABLE'
  LOOP
    EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L',
       schemaname,
       tablename,
       columnname,
       needle
    ) INTO rowctid;
    IF rowctid is not null THEN
      RETURN NEXT;
    END IF;
 END LOOP;
END;
$$ language plpgsql;

but I need a way to scan all fields in all tables to search for the keywoard '%mxltw%' how can this be done w/o dumping the data and w/o creating a stored proc?


texts: How to get an array as output parameter from stored procedure?

texts:
This is my stored procedure:

CREATE PROCEDURE `getAttritionData`(
 IN `supid` BIGINT,
 IN `from_date` DATE,
 IN `to_date` DATE,
 OUT `attr_rate` FLOAT,
 OUT `TERM_EMP_CNT` INT
)
DETERMINISTIC
BEGIN
 DECLARE i INTEGER DEFAULT 0;
 DECLARE CNT_EMP INTEGER DEFAULT 0;
 DECLARE TOT_EMP INTEGER DEFAULT 0;
 DECLARE AVG_HCOUNT INTEGER DEFAULT 0;

 SELECT count(employee_id) INTO TERM_EMP_CNT FROM employee
  WHERE term_date between from_date and to_date AND app_role=1;
 WHILE from_date<=to_date DO
   SELECT COUNT(EMPLOYEE_ID) INTO CNT_EMP FROM employee
    WHERE sup_id=supid AND doj<from_date
    AND (term_date>from_date OR term_date='0000-00-00') AND app_role=1;
   SET from_date=date_add(from_date, INTERVAL 1 DAY);
   SET i=i+1;
   SET TOT_EMP = TOT_EMP + CNT_EMP;
 END WHILE;
 SET AVG_HCOUNT=(TOT_EMP/i)*365/12;
 SET attr_rate = TERM_EMP_CNT/AVG_HCOUNT;
END

I want attr_rate as an array, where it has 1 key and another value such as:

attr_rate[6-2015]=3.5
attr_rate[7-2015]=6.2
attr_rate[8-2015]=3.3

How do I declare an array in a MySQL stored procedure?


texts: Added .Net assembly to SQL CLR without turning TRUSTWORTHY ON

texts:
I'm trying to add "System.Messaging.dll" using the guidance provided by Solomon Rutzky in his post Assembly deployment with permission UNSAFE or EXTERNAL_ACCESS using asymmetric key but I'm failing at the first hurdle.

The first part of the script is to create a certificate from the assembly;

CREATE CERTIFICATE [MS.NETcer] FROM EXECUTABLE FILE = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll';
GO

However, when I execute this I get the error;

Msg 15208, Level 16, State 1, Line 1
The certificate, asymmetric key, or private key file does not exist or has invalid format.

The account I'm using to execute the command has the 'sysadmin' server role. This is on a SQL Server 2008 instance.

Please does anyone have any ideas regarding why this is failing?

---------- Update 1 -----------

I've taken Solomon's advice and amended my script so it looks like this;

CREATE ASYMMETRIC KEY [Key.System.Messaging] FROM EXECUTABLE FILE = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll';
GO

CREATE LOGIN [CLR.Login.System.Messaging] FROM ASYMMETRIC KEY [Key.System.Messaging];
GO

GRANT UNSAFE ASSEMBLY TO [CLR.Login.System.Messaging];
GO

All good so far. I now run the CREATE ASSEMBLY command to add the assembly to the SQLCLR;

CREATE ASSEMBLY [System.Messaging]
FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
WITH PERMISSION_SET = UNSAFE
GO

but this fails with the error;

CREATE ASSEMBLY for assembly 'System.Messaging' failed because assembly 'System.Windows.Forms' is not authorized for PERMISSION_SET = UNSAFE.  The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.

I've tried creating an asymetric key for 'System.Windows.Forms'

CREATE ASYMMETRIC KEY [Key.System.Windows.Forms] FROM EXECUTABLE FILE = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll';
GO

but this fails with;

Msg 15468, Level 16, State 1, Line 1
An error occurred during the generation of the asymmetric key.

so I tried;

CREATE CERTIFICATE [Cer.System.Windows.Forms] FROM EXECUTABLE FILE = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll';
GO

and this fails with;

Msg 15208, Level 16, State 1, Line 1
The certificate, asymmetric key, or private key file does not exist or has invalid format.

So now I'm stuck again and wondering if its possible to use System.Messaging.dll in the SQL CLR without having to enable the TRUSTWORTHY database property (and also why on earth does System.Messaging.dll need to have a dependency on System.Windows.Forms.dll!)

Any thoughts gratefully received


texts: How do I get a graph of request traffic history in MS SQL Server 2008 R2

texts:
We're having difficulty with an application. One of the potential causes on the table is a regular spike in database traffic overloading the database, causing DB-dependent services to back up.

I would like to get a graph of the number of requests received per second over the last several days, and can't figure out how to get Server Management Studio to give me one. Or any historical performance graph, for that matter. Plenty of performance reports but none of them showing differences over time. Is this something it can't do, or am I just not looking in the right place?


texts: Does keyset/cursor pagination scale query performance better than offset pagination?

texts:
I am seeing behavior where the query that powers my infinite scrolling list seems to be slowing down as the number of rows grow.

Naturally, I used offset pagination because the library I am using just has you tack on an .offset(n).limit(m), and after a few minutes everything just functionally worked, and it seems to be the common approach for pagination.

I am aware of the problem with offset pagination where it can cause duplicate/missing items if the list is updated, but I am mostly concerned with performance here. I read that offset pagination doesn't really take advantage of indexes like keyset/cursor pagination does?

So considering it's an infinite scrolling list and I don't need page numbers, I am wondering if someone could confirm that refactoring to use keyset/cursor pagination would make it so the pagination doesn't grind things to a halt at more rows. Also, I am under the assumption that keyset pagination and cursor pagination are actually different things, and that keyset pagination would be better than cursor pagination for this scenario?


texts: Delete all row from all table from MYSQL DB with some ID clause

texts:
Is it possible to delete all rows from all tables in MYSQL DB with some id = '123'.

Actually i needed this because there is lots of junk data in others table but in main table rows are deleted, i have the IDs.

Now the new logic is implemented in the right way but i have to delete previous rows. Is it possible in a single MYSQL query?

Thanks in advance


texts: Does any RDBMS parallelize or otherwise optimize UNION queries?

texts:
For an example data set with rows fulfilling both subqueries:

SELECT h.batch, li.id as line_item, d.prusr as price, 0 as advertised
FROM schema.lineitem li
INNER JOIN schema.detail d on d.line = li.line and d.item = li.item
INNER JOIN schema.header h on d.batch = h.batch
LEFT JOIN schema.specialoffer sp ON sp.LINE = d.LINE AND sp.ITEM = d.ITEM
WHERE h.status = 'T' AND sp.batch IS NULL

UNION ALL
SELECT h.batch, li.id as line_item, d.prusr as price, 1 as advertised
FROM schema.lineitem li
INNER JOIN schema.detail d on d.line = li.line and d.item = li.item
INNER JOIN schema.header h on d.batch = h.batch
INNER JOIN schema.specialoffer sp ON sp.LINE = d.LINE AND sp.ITEM = d.ITEM
WHERE h.status = 'T'

What RDBMS will attempt to parallelize this type of query? I'd also be interested in any other "under the hood" sorts of optimizations you may know of, but parallelization is the main focus of this question.

References I have found already:

MySQL: No - Can MySQL parallelize UNION subqueries (or anything at all)?
Oracle: Maybe - Does a database parallelize a SQL query which has a union in it?


texts: Why database diagrams are not working when domain account is attached to database owner?

texts:
I was trying to use Database diagrams is SQL Server and got following error:

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

I tried to use this command to solve my problem

ALTER AUTHORIZATION ON DATABASE::mydb TO "domain\username"
GO

but it did not help. When I changed my domain account to sa and ran

ALTER AUTHORIZATION ON DATABASE::mydb TO sa
GO

I got rid of this error. The question is, why domain account is not acceptable for database owner (it seems that only sa annd NT AUTHORITY\SYSTEM are acceptable)?


texts: Recover registered servers in SQL Server Management Studio 2012

texts:
My computer died so it's be reinstalled.

Previously I had SQL Server 2008 R2 installed. On the new system I have SQL Server 2012.

I have the old RegSrvr.xml file (that contains the list of registered SQL Servers).

How do I get the server list from 2008 R2 into 2012?

Update:
When doing  Local Server Groups -> Tasks -> Import and choose the RegSrvr.xml file, click OK it's says "The import was successfull" - but no servers or folders has been added and the RegSrvr.xml file for the 2012 installation has not been modified

Sample from xml file

 <document>
  <docinfo>
    <aliases>
      <alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/Company1/RegisteredServer/stage</alias>
    </aliases>
    <sfc:version DomainVersion="1" />
  </docinfo>
  <data>
    <RegisteredServers:RegisteredServer xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
      <RegisteredServers:Parent>
        <sfc:Reference sml:ref="true">
          <sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/Company1</sml:Uri>
        </sfc:Reference>
      </RegisteredServers:Parent>
      <RegisteredServers:Name type="string">stage.server.dk</RegisteredServers:Name>
      <RegisteredServers:Description type="string" />
      <RegisteredServers:ServerName type="string">stage.server.dk</RegisteredServers:ServerName>
      <RegisteredServers:UseCustomConnectionColor type="boolean">false</RegisteredServers:UseCustomConnectionColor>
      <RegisteredServers:CustomConnectionColorArgb type="int">-986896</RegisteredServers:CustomConnectionColorArgb>
      <RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
      <RegisteredServers:ConnectionStringWithEncryptedPassword type="string">server=stage.server.dk;uid=username;password=AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAegY4QOvPGEGcK2Xdr+jDjQQAAAACAAAAAAADZgAAwAAAABAAAADesdxAGO4GO879z1yS2DZeAAAAAASAAACgAAAAEAAAAGAvgtRJfPRL/puHnREmKu8YAAAAUxRIQVDZHywb7rOYL9MRqG0B2Jvi8kyfFAAAANs53tVPbVux2j56oRU/2oGtmQc9;pooling=false;packet size=4096;multipleactiveresultsets=false</RegisteredServers:ConnectionStringWithEncryptedPassword>
      <RegisteredServers:CredentialPersistenceType type="CredentialPersistenceType">PersistLoginNameAndPassword</RegisteredServers:CredentialPersistenceType>
    </RegisteredServers:RegisteredServer>
  </data>
</document>


texts: Pivot rows to columns in Oracle where number of rows-to-columns can only be determined at runtime

texts:
Using Oracle 18c and given the following table and data:

create table food (type varchar2(20), name varchar2(30));
insert into food values ('fruit', 'apple');
insert into food values ('fruit', 'pear');
insert into food values ('fruit', 'orange');
insert into food values ('veggie', 'peas');
insert into food values ('veggie', 'carrots');
insert into food values ('veggie', 'squash');
insert into food values ('veggie', 'corn');

I want to pivot the results such that, if I query for fruit, I get the columns:

apple pear orange

but if I query for veggie, I get the columns:

peas carrots squash corn

My goal is to create dynamic SQL or PL/SQL such that, given the type, I get certain columns (and the column length can vary as in the two examples above).

How to do this?
That is, need to know how to pivot rows-to-columns where number of columns can be different but finite.

UPDATE - This comes close but the results are in XML format and I just want the name to be columns:

select * from food
  pivot xml (min(type) for name in ( select name from food where type = 'fruit' ));

OUTPUT -

<PivotSet>
  <item>
    <column name = "NAME">apple</column>
    <column name = "MIN(TYPE)">fruit</column>
  </item>
  <item>
    <column name = "NAME">orange</column>
    <column name = "MIN(TYPE)">fruit</column>
  </item>
  <item>
    <column name = "NAME">pear</column>
    <column name = "MIN(TYPE)">fruit</column>
  </item>
</PivotSet>


texts: SQL Server Merge Replication Error: The merge process could not replicate one or more INSERT statements to the 'Subscriber'

texts:
I have a merge replication with a push subscription from the publisher. It is getting error and stopped synchronization. The synchronization started again automatically and get the same error. Data don't get synced. The details of the error are

After getting this error when I have checked replication monitor it displays the following error messages:
Error messages

The merge process could not replicate one or more INSERT statements to the 'Subscriber'. A stored procedure failed to execute. Troubleshoot by using SQL Profiler. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200990)
Get help: http://help/MSSQL_REPL-2147200990

A query executing on Subscriber 'XXXXX' failed because the connection was chosen as the victim in a deadlock. Please rerun the merge process if you still see this error after internal retries by the merge process. (Source: MSSQL_REPL, Error number: MSSQL_REPL20245)
Get help: http://help/MSSQL_REPL20245

Cannot issue SAVE TRANSACTION when there is no active transaction. (Source: MSSQLServer, Error number: 628)
Get help: http://help/628

The process was successfully stopped. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200990)
Get help: http://help/MSSQL_REPL-2147200990

I am very new to replication, please let me know a way to get rid of the error.


texts: MSSQL to Postgres Replication and Syncing tools

texts:
Use case:
We use a postgres instance for our application but we need to transfer 100tb of data from a 3rd party db to postgres and maintain any updates to the third-party db. We need a tool that works independently of cloud providers as our application is primarily run in private networks.

Ideal functions:
Monitors transaction logs in MSSQL and copies them over to postgres.

Options that worked:
 - AWS DMS (with additional modification)
 - SpectralCore (fullmigrate/replicator) great tools but need performance improvement and only run on windows.
 - Talend was promising but very complex

We're looking for a tool or company that's solved this problem in the past or an opensource software we can leverage to solve this.


texts: How To Set Variables in MySQL Update Trigger

texts:
I have an estimates table, and an estimates_line_items table.  I am trying to update the estimates table whenever one it's child line items changes.  I keep running into a syntax error.  The error isn't very descriptive("You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version").  It appears I am screwing up at setting the variable values.

CREATE TRIGGER Update_estimate_from_line_items
AFTER UPDATE
   ON estimate_line_items FOR EACH ROW

BEGIN

   -- variable declarations
   DECLARE vPrev_amnt INT;
   DECLARE vNew_amnt INT;
   DECLARE nDiff INT;

   SET vPrev_amnt = OLD.price * OLD.quantity;
   SET vNew_amnt = NEW.price * NEW.quantity;
   SET nDiff = new_amnt - prev_amnt;

   -- trigger code
   UPDATE estimates SET
    subtotal = total + nDiff,
    total = subtotal + (tax_rate/100 * subtotal)
   WHERE estimate_id = NEW.estimate_id;

END;

EDIT: I've also tried setting the variables like this, with the same results:  SET vPrev_amnt := (SELECT OLD.price * OLD.quantity);


texts: Why does SQL Server fail to create a procedure referencing a linked server?

texts:
Red Gate Deployment Manager (RGDM) today failed with a "login failed" error even though it had administrative control of the target server:

2013-11-04 17:13:22 +00:00 INFO   Creating [dbo].[GetPublications]
2013-11-04 17:41:29 +00:00 INFO   Command RedGate.Deploy.Agent.Commands.SingleShotDeploymentCommand failed
2013-11-04 17:41:29 +00:00 INFO   RedGate.Deploy.PluginApi.Deployment.ConventionFailedException: Dynamic deployment failed
2013-11-04 17:41:29 +00:00 INFO   Login failed for user 'red_gate_deployment_manager'. ---> RedGate.Deploy.SqlServerDbPackage.Shared.Exceptions.SqlServerInaccessibleException: Dynamic deployment failed
2013-11-04 17:41:29 +00:00 INFO   Login failed for user 'red_gate_deployment_manager'. ---> System.Data.SqlClient.SqlException: Login failed for user 'red_gate_deployment_manager'.

One of the procedures I want to deploy refers to the distribution database on a linked server. The definition looks like this:

CREATE PROCEDURE dbo.GetPublications
AS
BEGIN
  SELECT publisher_db, publication, description
  FROM DISTRIBUTOR.distribution.dbo.MSpublications;
END;

In SSMS I connected to the target server as red_gate_deployment_manager and tried to create the procedure manually. I received a similar error:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'red_gate_deployment_manager'.

A colleague explained that RGDM fails to create the procedure because it has no permission to connect to the referenced linked server.

In our environment, all linked servers impersonate the local login. Our solution therefore is to grant administrative control to RGDM on the linked server.

We worked around the issue, but I still didn't understand it.

Inconsistent Behavior?

Why does SQL Server raise an error at creation time rather than at execution time?

It's confusing because the behavior feels inconsistent. I can't think of a good reason for it to behave this way, especially when in other situations the error is deferred until execution time.

When I create a stored procedure that references a missing stored procedure:

CREATE PROCEDURE dbo.ExecuteMissingProcedure
AS
BEGIN
  EXECUTE dbo.MissingProcedure;
END;

SQL Server prints a warning instead of raising an error:

The module 'ExecuteMissingProcedure' depends on the missing object 'dbo.MissingProcedure'. The module will still be created; however, it cannot run successfully until the object exists.

However, when you try to execute it:

EXECUTE dbo.ExecuteMissingProcedure;

SQL Server raises a hard error:

Msg 2812, Level 16, State 62, Procedure ExecuteMissingProcedure, Line 4
Could not find stored procedure 'dbo.MissingProcedure'.


texts: Smaller client results in a very slow query

texts:
I am testing a query on PostgreSQL 9.6 on a very small tenant and a big one (within the same database, schema, tables). The query is super slow on a small client, which is quite surprising as I would expect otherwise.
The query plans:
Small - https://explain.depesz.com/s/mnlD
Large - https://explain.depesz.com/s/JB85
The noticeable difference between the queries is that the smaller one uses a Nested loop over  a Hash join and I did find that a query planner might do that when the tables are "small".
I am not suggesting that there is a problem with the planner, the query is far from perfect but clearly, if a big client is queried x times quicker I would assume that the smaller one should not be a problem. How could I rectify this?
I tried running vacuum analyze on all involved tables but it did not help.
I am aware there have been three new major versions of PostgreSQL but 9.6 is officially still supported so it does not explain or justify the issue.


texts: MySQL Workbench 8.0 data filling using foreign key

texts:
As you see in the picture below, I have some kind of information like this. The main information in Regions_other will be the following (in short):

NA      AMS
LATAM   AMS
APAC    APJ

As I will be using AMS/APJ format I created a Regions table to use them and Regions other as maybe I will need after these infos.

Question is the following, for example when I start to fill in Regions_other is there any mean to fill them using the info in Regions. For example, I am talking about Data Validation in Excel, when you say that in the cell there can be only the values from list X. As the table Regions is filled, wouldn't I be able to use them for filling the second table?


texts: Creating a row for each value in a comma-separated string

texts:
I'm currently trying to create a View from a Table that does the following:

Table A
╔═════════════╦═══════════════╗
║ PART NUMBER ║   FILE NAME   ║
╠═════════════╬═══════════════╣
║  PN0001-01  ║      FOO      ║
║  PN0002-01  ║      BAR      ║
║  PN0003-01  ║    FOO,BAR    ║
║  PN0004-01  ║  BAR,FOO,FOB  ║
╚═════════════╩═══════════════╝

To create

View B
╔═════════════╦═══════════════╗
║ PART NUMBER ║   FILE NAME   ║
╠═════════════╬═══════════════╣
║  PN0001-01  ║      FOO      ║
║  PN0002-01  ║      BAR      ║
║  PN0003-01  ║      FOO      ║
║  PN0003-01  ║      BAR      ║
║  PN0004-01  ║      BAR      ║
║  PN0004-01  ║      FOO      ║
║  PN0004-01  ║      FOB      ║
╚═════════════╩═══════════════╝

So, essentially, I need to take records from Table A and insert them into View B, where if the record from A has a LIKE '[,]' property, then that field is split into multiple records in B, depending on the number of times the ',' is present, with the text between each ',' being given its own record in View B.


texts: PostgreSQL: Do regclass/oid columns persist across backups?

texts:
I have a large table (audit) in which I store relation ID of the schema and table.
This is conveniently populated by trigger using TG_RELID.
It is also convenient because I can index it and the index would be smaller than if I were to store "schema"(text) and "table"(text) columns separately and index them.
Also, I can then do queries using WHERE relation_id = 'myschema:mytable'::regclass and it just works.

My question is if this is "safe" to do in terms of backup/restore (will the schema.table have the same regclass/oid in target server and it did in the source server?

Any other issue I should be aware of?


texts: How to create month ranges without using a specific date in Postgresql

texts:
What's the best way to create month ranges without using specific dates?

For example, today is April the 4th and I want to calculate the sales rate of the past 12 months, that I can run every month without updating the date.

I was thinking of working with current_date, but is there a way to create a more efficient query?

select
datetrunc('month',date)
,count(id) filter (where status='sold' and (date between (datetrunc('month','current_date - interval '1 months'))
and datetrunc('month', current_date) - Interval '1 days')) / count(id) filter
(where date between (datetrunc('month','current_date - interval '1 months')) and
datetrunc('month', current_date) - Interval '1 days') as Mar2020_Sales_Rate
,..... as Feb2020_Sales_Rate
,.
,.
,..... as Mar2019_Sales_Rate
from sales
group by 1;


texts: I would like to know the working principle of DBCC regarding the tempdb. I want to know why it uses tempdb for what purpose. I didn't find any advanced article about that so maybe someone can shortly explain or at least share links for the article about that.


texts: is the only way to create a user for an azure database, to use SQL query?

texts:
Azure databases are contained databases (right?) then I need to use this to create a user:
CREATE USER UserName WITH PASSWORD = 'hardPass';
ALTER ROLE [db_owner] ADD MEMBER UserName ;

is there a way to do this via portal? I found the oficial documentation saying that the only way to add a user is using a query, but maybe the doc is not updated.
Thanks.


texts: What are the pros/cons of Oracle's Grid Control compared to DB Control?

texts:
Database control is a web based tool for managing a single Oracle database that is installed on the same box as the database itself.  Grid Control is a web based tool for managing multiple Oracle databases that can be installed on a separate server and has its own database.

We are currently using the 10g version of Grid Control and are considering the pros and cons of upgrading to the 11g version of Grid Control vs. abandoning it for DB Control.  As such we would like to quantify what features/benefits we would loose and what features/benefits we would gain with each route.  We are interested in the obvious as well as the obscure/smaller/intrinsic/potential.


texts: Can't get rid of filesort on joined query sorting on second table


texts: What's the best strategy for integrity checks on large dbs? DBCC CHECKDB (DB Name) would probably take over 24 hours and the largest table, being 532GB, would probably take over 17 hours.


texts: Why is this delete operation slower after a foreign key relationship has been added?

texts:
Imagine the following table about cat owners.

drop table if exists  owners cascade;
create table owners(
    id bigint primary key generated always as identity ,
    name text not null
)
;

insert into owners(name)
select random()::text from generate_series(1,20000);
--insert 200,000 owners records

When I delete some of the owners records it is pretty fast:

delete  from owners
where id %10 = 0;

20000 rows affected in 85 ms

Now I add a table called 'cats' that refers to owners:

drop table if exists cats;
create table cats(
    id serial primary key ,
    name varchar(20000) not null,
    owner_id int not null references owners(id)
);

--insert 1bn cats records
insert into cats(name, owner_id)
select
       random()::text,
       owners.id
from generate_series(1,10), owners;

Lets delete some owners, but first we have to delete the cats that these owners 'own':

--delete the records in cats so we don't get a foreign key constraint violation
delete  from cats
where owner_id %10 = 1;

---now we do the same delete on owners as we did before
delete  from owners
where id %10 = 1;

2000 rows affected in 25 s 828 ms

Why is the second delete from owners ~5000 times slower than when we didn't have the cats table?


texts: DBMS_METADATA.GET_DDL not displaying complete DDL for big tables

texts:
While generating the DDL for a schema, big tables' DDL are getting truncated. After displaying close to 110 rows I am getting an ellipsis (...).

I am using SQL developer to run:

SELECT dbms_metadata.get_ddl('TABLE',TABLE_NAME,OWNER)
FROM DBA_TABLES
WHERE OWNER = 'XYZ'


texts:
I am using following command line to install SQL Server 2008 R2 but its not installing. Also, I am not getting any error or log in summary.txt.

  "D:\SQLEXPR32_x86_ENU (1).exe" /q /ACTION= Install /FEATURES=SQL
  /INSTANCENAME=nvt /SECURITYMODE=SQL /sapwd=abc
  /IACCEPTSQLSERVERLICENSETERMS


texts: I can't successfully install postgreSQL. 'Problem running post-install step' error message pops up

texts:
I have successfully installed postgreSQL previously, but I am struggling to install it in one particular machine (Windows 8.1 Pro). Everytime I try to install it I get the following message:

Warning
Problem running post-install step. Installation may not complete correctly. The database cluster initialisation failed.

After the above message pops up the installation completes, but there's no postgreSQL service at all on the services list in the task manager.

I know that an older version of postgreSQL used to run without problems in this machine. Maybe uninstallation was not done properly? Has anyone seen this message before?


texts:
i am new to JDBC and trying following code. Could not find the error in it

try{
        Connection con = ConnectionProvider.getCon();
        stmt=con.prepareStatement("insert into create_request values((select count(reqno) from create_request)+1,?,?,?,?,?");
        stmt.setString(1,obj_Leaverequest.getUser_name());
        stmt.setString(2,obj_Leaverequest.getLeave_Type());
        stmt.setInt(3,obj_Leaverequest.getLeave_Units());
        stmt.setString(4,obj_Leaverequest.getLeave_Reason());
        stmt.setString(5,"pending");
        count=stmt.executeUpdate();
        if(count>0){
            status=true;
        }
    }

Getters are working fine, i could print their values . i guess i am wrong with the query.Please point out my mistake. Thanks in advance!!


texts: Ubuntu cannot open shared library mysqlx.so

texts:
MySQL Server version:

Ubuntu: 5.7.23-0ubuntu0.18.04.1-log (Ubuntu)

MySQL installed using apt-get.

When installing a plugin with the following command:

mysql> INSTALL PLUGIN mysqlx SONAME 'mysqlx.so';

...I receive the following error message:

  ERROR 1126 (HY000): Can't open shared library '/usr/lib/mysql/plugin/mysqlx.so' (errno: 2 /usr/lib/mysql/plugin/mysqlx.so: cannot open shared object file: No such file or directory)

What can't Ubuntu locate/find the mysqlx.so library?


texts: Recommended memory configuration for many SQL Server instances per server

texts:
I am currently managing a server cluster for a SaaS system. We currently have 6 SQL servers running MSSQL 2008 R2. Each server is a quad core virtual machine with 16GB of RAM provisioned on a server rack. We populate each server with the maximum of 50 instances allowed by SQL Server. Each instance will be accessed sporadically by a maximum of 10 clients simultaneously, and each database will in general only be around 100MB to 500MB in size.

Currently, each instance was installed without setting memory limits, but what we have found is that the first instances tend to use too much memory, leaving later instances (in starting order) with barely 200MB to work with, and the server OS with less than 1% physical memory available. This seems to cause excessive disk swapping, and latency issues.

What is the recommended way to split memory allocation in this case? Is there a formula to determine approximately how much memory one instance needs as a minimum from the number of clients and database size? Could I set a maximum of 300MB per instance and be done with it?

2018-12-13 Final solution

I have finally figured out how to configure everything where it seems very stable and is completely symmetric across all instance, and since it took some digging and doing I thought I should share the solution I came to. This is probably not for everyone, but it seems to be the best solution to me.

The key is in this API: https://docs.microsoft.com/en-us/windows/desktop/api/memoryapi/nf-memoryapi-setprocessworkingsetsizeex

This allows you to set a hard limit on the minimum and maximum working set size on a per process basis. This seems to be the cleanest to me, as it allows me to tell SQL server to use as much memory as it wants WITHIN this working set size, and it seems to optimize adequately by itself instead of running at capacity and trying to hog more memory all the time. Each instance will swap more overall, but since the usage is sporadic for each instance it seems to work just fine, and if it ever becomes a problem I can provision a few swap partitions off an SSD in the server rack as swap space for each server. I set the working set size to 300MB for each instance through a powershell script when each instance launches, and the minimal OS always has 1GB to work with, and everyone seems happy enough.


texts: Open a Oracle 7.3.x database in Oracle 11

texts:
Is there any way to open or convert an old Oracle 7.x database into Oracle 11g?
Thanks in advance!


texts: Analysis Services Component set to 'false'?

texts:
Our database server (Microsoft SQL Server 2008 R2) undergoes audit and they are requesting to set the value
of Security\BuiltinAdminAreServerAdmins to false from default value of true

Now, since it is a production server, my worry is the effect of taking this action, can anyone tell me the effect when I set it to false?

These are the steps to go on said item.

Open the SQL server management studio
Connect to the  "Microsoft Analysis Server"
Right click on the "Microsoft Analysis Server" then select properties
Tick the box  for  "Show Advanced (All) Properties"
Scroll down till you find  [Security \ BuiltinAdminsAreServerAdmins]  and check the current value.
If the value set to true then stand on the true and change it to false.

Thank you in advance. Please use the following image as a reference.


texts: Querying sums of grouped consecutive rows in PostgreSQL 9

texts:
I have data about people traveling in different countries like this:

country | begintimestamp      | distance

Germany | 2015-01-01 00:00:00 | 100
Germany | 2015-01-01 01:12:13 | 30
France  | 2015-01-01 02:13:14 | 40
France  | 2015-01-01 03:14:15 | 20
Spain   | 2015-01-01 04:15:16 | 10
France  | 2015-01-01 05:16:17 | 30
France  | 2015-01-01 05:17:18 | 5
Germany | 2015-01-01 06:18:19 | 3

What I need is to be able to receive a result like this - the distance of consecutive rows summed with the earliest begintimestamp:

country | begintimestamp      | distance

Germany | 2015-01-01 00:00:00 | 130  // 100+30, the distance of two first rows summed.
France  | 2015-01-01 02:13:14 | 60   // 40+20
Spain   | 2015-01-01 04:15:16 | 10   //
France  | 2015-01-01 05:16:17 | 35   // 30+5
Germany | 2015-01-01 06:18:19 | 3

I've tried to play around with PG window functions but have not been able to come up with anything that would lead me closer to the result.


texts: Collation conflict ... Could not use dbo.sysdac_instances

texts:
After a migration of databases to a new 2014 server with default collation Latin1_General_CI_AS, but where most of the databases have a collation of Latin1_General_BIN, an attempt to import an Excel spreadsheet is throwing the following error:

I've tracked sysdac_instances down to msdb where it is a system view and querying it directly gives the same error. I'm hoping someone will be able to point me in the direction of a straighforward-ish solution to this problem?

Curiously though, running the import via SSMS 2008 works.


texts:
I am running Oracle 10g. I am writing a script (a delta script over a created database) that will be executed in SQL*Plus. It is needed to fix a bug.

I need to know if a row exist in a table, and if it does not, insert it. So I start like:

BEGIN
    DECLARE
        COL_EXIST NUMBER(10);
    BEGIN
        -- Reset ET_SEVERITY_LEVEL
        UPDATE ET_SEVERITY_LEVEL
        SET ID_SEVERITY_LEVEL = 5
        WHERE CODE='FATAL';

        UPDATE ET_SEVERITY_LEVEL
        SET ID_SEVERITY_LEVEL = 4
        WHERE CODE='ERROR';

        UPDATE ET_SEVERITY_LEVEL
        SET ID_SEVERITY_LEVEL = 3
        WHERE CODE='WARNING';

        UPDATE ET_SEVERITY_LEVEL
        SET ID_SEVERITY_LEVEL = 2
        WHERE CODE='INFO';

        -- Insert ET_SEVERITY_LEVEL register DEBUG
        SELECT COUNT(*) INTO COL_EXIST
        FROM ET_SEVERITY_LEVEL
        WHERE CODE like 'DEBUG';

        IF COL_EXIST = 0 THEN
            EXECUTE IMMEDIATE 'INSERT INTO ET_SEVERITY_LEVEL(ID_SEVERITY_LEVEL, CODE, DESCRIPTION) VALUES (1, "DEBUG", "Debugging")';
        END IF;
    END;
END;
/

But I get the following error:

ORA-00984: column not allowed here
ORA-06512: in line 30
00984. 00000 -  "column not allowed here"
*Cause:
*Action:

How could I do this? Is there a way to write a script to query tables and DML at the same time?


texts: Why does Mongodb production notes have no recommendations about the disk block size? AFAIU, disk block size would have a considerable impact on a throughput intensive application. Does disk block size have any impact on mongodb performance in practice?


texts: Connecting to a SQL Server database from a Flash program

texts:
I currently have the ability to utilize Microsoft SQL Server 2012. I am developing a project with Adobe Flash Builder 4.7.

If I link my database with Adobe Flash Builder is there any additional steps I must take in order to make the database live, or as long as my computer is running will this database be accessible from any device that is utilizing it?

In other words is this a LAN only system or does it automatically make itself available for the programs I link to it?


texts: designing data model similar to Power Point Presentation slides

texts:
I am trying to come up with a data model that would adequately describe business rules, similar to power point presentations with one exception. The structure of the slides is predefined - each slide has a slide style. I need to store the contents the user inputed for that style (which image he/she chose, what is the String in heading 1 or 2...)

By slide style I mean that some slides have 1 heading, some 2, some 3 others, some have 1 image in it, others 8...some have a background video, some solid color, others backgorund image..

The order of slides in a presentation is also important. Users can add slides to presentation, remove them, replace them. One slide style can be repeated multiple times in a project. If you want to print the presentation, it gets printed in the correct order.

How would you construct such data model?

I've created users and projects table, but I am having problems with how to represent and slide_style. slide_style has so many different options and I see no elegant way of doing this. How would you do this?

slide_style and project are in M:N relationship, so I will create another table project_slides and link them together, but how to represent so many different styles is the real issue here.

CREATE TABLE `users` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(256) DEFAULT NULL,
  `password` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

CREATE TABLE `projects` (
  `project_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`project_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `projects_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


texts: Feasibility of Partitioning existing tables with huge data in Oracle

texts:
We have a set of 24 tables that take up about 1tb+ data which is why DBA suggested to use partitioning to better handle usage.

The only issue being the partitioning of the existing tables is something they are not advising as data transfer from current to temp table will take a lot of time(dbms_redefinition), which is why they are suggesting to use a 2 table approach where old table will be static and new table will be partitioned, and select will be a union of the 2 tables as required.

I just wanted to explore if there is any other way of partitioning the existing tables without requiring significant down time


texts:
what are the best practices in Postgres for globally converting UUIDs into IDs. I'm currently using both UUIDs and IDs in my schema. The intent is for UUIDs to be exposed to the public (for URLs et.) and IDs for internal systems.

I've looked at the following methods but am unsure of the best practices.

a custom function that does a UUID/ID lookup on the target table
using a BEFORE trigger that does the conversion and keeps all postgres functions clean

Can someone provide some wisdom/suggestions here?

Thanks!


texts: Does indexed column with millions of different values make sense?

texts:
I noticed that one of my queries was pretty slow, taking about two seconds to execute.

This is the slow query:

SELECT EVENT_DATETIME, EVENT_EVENTTYPE FROM EVENT WHERE (EVENT_ID = :1  AND EVENT_RESULT = :2  )

The index used was created like this:

CREATE INDEX "INDEX_EVENT_DATETIME" ON "EVENT" ("EVENT_DATETIME", "EVENT_ID")

The execution plan shows an index skip scan because obviously the EVENT_DATETIME column is not in the where clause.

So at first I thought I would create a second concatenated index on the table for the columns (EVENT_ID, EVENT_RESULT) but then I noticed that the column EVENT_DATETIME has 18,702,145 distinct values. Of a total of 18,722,706 rows.

There are 4 different queries happening regularly on this table. All of them except one have the EVENT_DATETIME column in the where clause but all of them have EVENT_ID in the where clause.

So my assumption is that the index as it is currently being used doesn't make the queries more efficient.

Is it correct that a concatenated index on the columns (EVENT_ID, EVENT_DATETIME) would be more useful?


texts: How can I see if the data in a SQL Server table is page-compressed?

texts:
This is a follow-on question to a question which I asked yesterday: Can I bulk insert into an empty page-compressed table and get full compression? The answer to that question (paraphrased from Randi Vertongen's excellent answer) is yes, but it requires that the bulk insert take a table-level lock; otherwise, the bulk insert takes a row-level lock and performs only row data compression. This raises the question: how can I tell afterward what compression is applied?

Here are the steps to create row-compressed data in a theoretically-page-compressed table:
1. Create a table with DATA_COMPRESSION=PAGE and do not use sp_tableoption to turn on the "table lock on bulk load" option for this table.
2. Use bcp to bulk insert the data from a flat file into the new table, but without specifying the -h TABLOCK option to lock the table.

The result is a table in which the data is compressed on the row level (smaller than an uncompressed table but larger than a page-compressed table), but inspecting the sys.allocation_units catalog table shows the data compression as page.

The question

When the table's data allocation is for page compression as in this scenario, what can I do to find out if the data in that table is page-compressed?


texts: SQL Server: Join all tables from a table listing their names

texts:
I have a table that contains a list of metrics and their IDs in the format:

L6Metrics
| ID   | Name            |
--------------------------
| 734  | L6A_Velocity    |
| 736  | L6B_Velocity    |
| 738  | L6A_Target_Rate |
...

Each of these metrics has its own corresponding table containing timestamped historical data. If the metric has ID X, then its historical data's table name is History_tX. These history tables have the format:

History_t734
| DateTime                       | Value |
-----------------------------------------
| 2018-05-04 19:17:41.972 -05:00 | 33.9  |
| 2018-05-04 19:17:51.972 -05:00 | 36.3  |
| 2018-05-04 19:18:01.972 -05:00 | 35.1  |
...

Assuming that the timestamps across all of the history tables align, how can I use the L6Metrics table to join all of the history tables on DateTime to get the following:

| DateTime                       | History_t734 | History_t736 | ...
----------------------------------------------------------------
| 2018-05-04 19:17:41.972 -05:00 | 33.9         | 34.9         |
| 2018-05-04 19:17:51.972 -05:00 | 36.3         | 37.4         |
| 2018-05-04 19:18:01.972 -05:00 | 35.1         | 36.5         |
...

There are 75 rows in L6Metrics (representing 75 history tables) so there would be 76 columns in my result. I have the ability to modify L6Metrics if needed.


texts: How do I find an object that doesn't seem to exist in the objects table?

texts:
I have an object called cot_ntn_pi_v. I was told this was a synonym.  It doesn't appear in the all_synonyms table. It looks like a view or table but I can't find it in the all objects table. I can select from it, but I can't drop it as it 'doesn't exists' and I can't create a new table with the same name as 'the name is already used by another object.'

Am I going mad or doing something really stupid?


texts: postgresql - Disallow getting list of databases

texts: How can I disallow getting list of databases on the PostgreSQL server? That can be done (for example) using \l in psql.


texts:
I have a column in my table that I need to change the data type from VARCHAR to DOUBLE. Now I cannot convert it to DOUBLE data type easily because the values on that column have numbers and strings.

> 0 41  14:16:57
  ALTER TABLE `ControlAutomation`.`constantcomponentstabular`
     DROP COLUMN `String`,
     CHANGE COLUMN `ConstantValue` `ConstantValue` DOUBLE NULL DEFAULT NULL

> Error Code: 1265. Data truncated for column 'ConstantValue' at row 217966 1.891 sec

I know what is causing this issue, its because there are strings on the column that I want to change to DOUBLE.

Here is the solution that I have in mind using Python

Query the table.
Check whether the value can be converted to float.
If not, UPDATE value of that column to 0.

This process takes a long time, My question is how to best change those values to any number so I will be able to ALTER the table.

I am using MySQL 5.6.32 and InnoDB as storage engine.


texts: How to find the number of tuples that are vacuum-eligible?

texts:
I am trying to find the number of vacuum-eligible tuples in one of our postgres tables. This is the output of vacuum verbose on that table

INFO:  "act_msgs": found 0 removable, 2217083 nonremovable row     versions in 314492 out of 314492 pages
DETAIL:  2185476 dead row versions cannot be removed yet.
There were 62133 unused item pointers.

Before running the vacuum the live and dead tuple counts were n_live_tup => 2204051 and n_dead_tup => 12370. After running the vacuum they are n_live_tup => 2217220 and n_dead_tup => 9. So it looks like if vacuum cannot remove the actually deleted tuples it adds those tuples to live_tup.

Now, we know that we have a long running transaction opened by the application and that is causing the auto vacuum not able to clean up the dead tuples. The application would do frequent inserts and deletes. How do i find the number of tuples that were actually deleted and would be vacuumed when that transaction is closed?


texts:
I created a maintenance plan for my SQL Server 2008 R2 using SQL Server Management Studio (SSMS).

However, I am unable to export/convert it to a script, in case I need to redo it on another server.

Is there a way?

If no, will there be a way with a newer version of SQL Server?


texts: how to force postgresql to filll work_mem?

texts:
i'm currently investigating a situation where my PostgreSQL 9.3 instance was killed by the Linux OOM Killer and went to recovery mode. The machine is EC2 Server with 16 GB of RAM and has rather few connections and is used for Business Analsys purposes only.

My assumption is that the reason for the out of memory situation is on one hand a very huge statement but also the fact that work_mem variable is set to 2GB while at the same time 100 connections are allowed.

And here are my questions:

is my assumption correct
if so, how can i force postgres to fill the session memory in a controlled manner to make the situation repeatable

I hope you guys can help me soon, thanks in advance and have a good week.


texts:
I have two staff lists:

List A:

StaffID   Supervisor
====================
0001234         NULL
0001235      0001234
0001237      0001234
0001239      0001237

and

List B:

StaffID   Supervisor
====================
0001234         NULL
0001235      0001234
0001238      0001235
0001239      0001235

And I need the following output:

StaffID   SupervisorA   SupervisorB
===================================
0001234          NULL          NULL
0001235       0001234       0001234
0001237       0001234          NULL
0001238          NULL       0001235
0001239       0001237       0001235

Notice that the staff IDs from List A and List B have been merged into a single, non-repeating list, and that I have merged the two supervisor details which may not match between the two lists.

Query doesn't have to be nice. I have ~8000 records in each list and I will run it nice. I'm happy to do some minor Excel manipulation afterwards if necessary.

I wanted to do a full join, but the Access query designer properties window on the join allows me to choose ALL from table A and matching from table B, ALL from table B and matching from table A, or only those which match in both A and B.

I'm sure this is very simple to do, but I very rarely use MS Access.

I have two queries so far which don't give me what I want. I wanted to combine them in the full outer join, since Access won't give me an option, but not sure how:

SELECT ListA.*, ListB.*
FROM ListA LEFT JOIN ListB ON ListA.[StaffID] = ListB.StaffID;

and

SELECT ListA.*, ListB.*
FROM ListB LEFT JOIN ListA ON ListB.[StaffID] = ListA.StaffID;


texts: Excluding rows from a query on a combination of mulitple conditions

texts:
I want to drop rows from a query result based on the combination of two columns e.g.

My table after join is

-- JOIN of person and flag table based on person_id

Person.Person_id  Flag.Query_id  Flag.Person_id  Flag.query_flag
1                 10             1               IGNORED
1                 20             1               FAVORITE
2                 20             2               FAVORITE
2                 10             2               IGNORED
3                 10             3               FAVORITE

Now, I want remove/drop all persons who have "ignored" the query 10.

So remove rows where query_id = 10 and flag = 'IGNORED'.

The resulting table should be:

Person.Person_id  Flag.Query_id  Flag.Person_id  Flag.query_flag
1                 20             1               FAVORITE
2                 20             2               FAVORITE
3                 10             3               FAVORITE

So I want to drop rows from the result based on the combination of two columns.

I know that I can select a row based on the combination of two columns as:

SELECT *
FROM  -- query with joins here
WHERE Flag.Query_id = 10 and Flag.query_flag = 'IGNORED'

I tried doing something like:

 SELECT *
 FROM  -- query with joins here
 WHERE NOT (Flag.Query_id = 10 AND Flag.query_flag = 'IGNORED')

I see here that NOT is applied to the columns individually and not to a combination of conditions.

How can I achieve my result without a subquery?


texts: Best High Availability Solution for SQL Server 2014

texts:
Please we are about to implement High availability for for SQL server 2014 on our new infrastructure but i need to know which is best between SQL Failover Clustering and Always On High Availability Group. We needed to consider below as part of our requirement ;
1. Automatic Failover
2. Ensure that if storage hosting SQL database fails or get corrupted all data are moved to another Storage and still work perfectly with Automatic Failover
3. Needs to ensure that secondary(passive) node can used for reporting

I will be very happy if i can get assisted with above queries as well as advantages/Disadvantages of both Always on High availability and SQL Failover Clustering


texts: Better way to manage data integrity, without having too many composite FK's

texts:
I have following tables:

class_table (class_id = Auto Incremented Primary Key (AIPK) of this table)
user_table (class_id, user_id (AIPK of this table))
course_table(class_id, course_id (AIPK of this table))

enrollment_table(class_id, user_id, course_id, enrollment_id (AIPK))

Now to maintain data integrity and make sure that both user_id and course_id belong to same class_id, I will have to create 2 composite Foreign Key's (FK) on enrollment_table e.g.

(class_id, user_id) to -> user_table
(class_id, course_id) to -> course_table

With this kind of schema design, if there are more tables involved then number of composite FK's are increasing. So I wanted to know if there is any better way of modeling this kind of schema and avoiding too many composite FK's.


texts: If I assign a NVL constraint to a nullable column will it display an error or it will retrieve the records?

texts:
I have a database in which some tables have to be joined to fetch the data. The problem is that in one table the column is nullable (nullable is set to yes or no constraint given for that column) and the select query is given as NVL(column name,0).

So will the command retrieve it normally or give an error?

Example:

Select  eid,ename ,nvl(sal,0),nvl(comm,0)
    from employee,sal
   where sal>1500
         AND
         comm=500

In the above query sal column is nullable but I am passing the query as nvl(sal,0) so will it retrieve the records or pass an error? If an error then how do you suggest I change the query?

I have already tried and getting neither the output nor any error in sql developer.

When tried in Plsql it gives PLS-00103: Encountered the symbol "OPEN" when expecting one of the following:

:= . ( @ % ;

Update:-

 cursor c_alerts is
select function_code               as function_code,
       nvl(latitude,0.0)           as latitude,
       nvl(longitude,0.0)          as longitude,
       nvl(gps_timestamp,0)        as gps_timestamp,
       nvl(violation_maxspeed, 0)  as violation_maxspeed,
       nvl(violation_start_time, 0)as violation_startspeed,
       nvl(violation_stop_time, 0) as violation_stopspeed,
       nvl(duration_sec, 0)        as duration_sec,
       cs_rec_insert_time          as rec_insert_time,
       nvl(location1, 'not found ')         as location1
  from t_vmu_alerts_hist
 where vmu_id = lv_vmu_id
   and gps_timestamp between lv_from_date and lv_to_date
union
select function_code               as function_code,
       nvl(latitude, 0.0)          as latitude,
       nvl(longitude, 0.0)         as longitude,
       gps                         as gps_timestamp,
       nvl(violation_maxspeed, 0)  as violation_maxspeed,
       nvl(violation_startspeed, 0)as violation_startspeed,
       nvl(violation_stopspeed, 0) as violation_stopspeed,
       nvl(duration_sec, 0)        as duration_sec,
       rec_insert_time             as rec_insert_time,
       loc_info                    as location1
  from p_error_vmu_alerts
 where vmu_id = lv_vmu_id
   and gps_timestamp between lv_from_date and lv_to_date
union
select function_code               as function_code,
       nvl(latitude, 0.0)          as latitude,
       nvl(longitude, 0.0)         as longitude,
       gps_timestamp               as gps_timestamp,
       nvl(violation_maxspeed, 0)  as violation_maxspeed,
       nvl(violation_startspeed, 0)as violation_startspeed,
       nvl(violation_stopspeed, 0) as violation_stopspeed,
       nvl(duration_sec, 0)        as duration_sec,
       rec_insert_time             as rec_insert_time,
       loc_info                    as location1
  from p_vmu_alerts
 where vmu_id = lv_vmu_id
   and gps_timestamp between lv_from_date and lv_to_date;

t_alert
    open c_alerts();
    FETCH c_alerts BULK COLLECT
      INTO t_alert;
    CLOSE c_alerts;
    LOG_ERROR := 13;


texts:
How do i filter, in a Mondrian MDX Query, just the FACTS that have ocurred in a Range of Months over last 3 years?

I need to make it dynamic so i can use parameters latter, so i need the following:

Month Range over last 3 years, Where StartMonth and EndMonth is given by user by parameters.

[2002].[January],  [2002].[February], [2002].[March]
[2003].[January],  [2003].[February], [2003].[March]
[2004].[January],  [2004].[February], [2004].[March]

Time dim have Year, Half Year, Quarter, Month, Week and Day level.

I have tryed this, but i get an error Mondrian Error:Cannot deduce type of set

WITH set [MesesFiltrados] as
DESCENDANTS(
    FILTER(LastPeriods (3,
        StrToMember("[Data].[" || TRIM(Str(Int(YEAR(NOW())))) || "]")
    ),
      [Data].[Mes].currentmember in ([Data].Mes.[${StartMonth}]:[Data].Mes.[${StartMonth}])
  )
)

Select {[Escritorio]} on columns,
{
  MesesFiltrados
}
on  rows

From
Desempenho

Tks


texts:
I faced this problem after importing the DB, recently i had to setup the fresh one so i used the already taken backup of files & db after importing db this following error shows,

General Error
SQL ERROR [ mysqli ]

Can't find FULLTEXT index matching the column list [1191]

SQL

SELECT  f.forum_id, f.forum_name, t.*,
        MATCH (t.topic_title) AGAINST ('some topic') AS score,
        fp.post_text AS first_post_text, lp.post_text AS last_post_text,
        fpu.user_avatar AS fp_avatar,
        fpu.user_avatar_type AS fp_avatar_type,
        fpu.user_avatar_width AS fp_avatar_width,
        fpu.user_avatar_height AS fp_avatar_height,
        lpu.user_avatar AS lp_avatar,
        lpu.user_avatar_type AS lp_avatar_type,
        lpu.user_avatar_width AS lp_avatar_width,
        lpu.user_avatar_height AS lp_avatar_height
    FROM  (phpbb_topics t)
    LEFT JOIN  phpbb_forums f  ON (f.forum_id = t.forum_id)
    LEFT JOIN  phpbb_posts fp  ON (fp.post_id = t.topic_first_post_id)
    LEFT JOIN  phpbb_users fpu  ON (fpu.user_id = t.topic_poster)
    LEFT JOIN  phpbb_posts lp  ON (lp.post_id = t.topic_last_post_id)
    LEFT JOIN  phpbb_users lpu  ON (lpu.user_id = t.topic_last_poster_id)
    WHERE  MATCH (t.topic_title) AGAINST ('some topic') >= 0.1
      AND  t.topic_status <> 2
      AND  t.topic_visibility = 1
      AND  t.topic_time > (UNIX_TIMESTAMP() - 315360000)
      AND  t.topic_id <> 8
      AND  f.similar_topics_ignore = 0
    LIMIT  5

BACKTRACE

FILE: (not given by php)
LINE: (not given by php)
CALL: msg_handler()

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 994
CALL: trigger_error()

FILE: [ROOT]/phpbb/db/driver/mysqli.php
LINE: 193
CALL: phpbb\db\driver\driver->sql_error()

FILE: [ROOT]/phpbb/db/driver/mysql_base.php
LINE: 45
CALL: phpbb\db\driver\mysqli->sql_query()

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 270
CALL: phpbb\db\driver\mysql_base->_sql_query_limit()

FILE: [ROOT]/phpbb/db/driver/factory.php
LINE: 321
CALL: phpbb\db\driver\driver->sql_query_limit()

FILE: [ROOT]/ext/vse/similartopics/core/similar_topics.php
LINE: 210
CALL: phpbb\db\driver\factory->sql_query_limit()

FILE: [ROOT]/ext/vse/similartopics/event/listener.php
LINE: 63
CALL: vse\similartopics\core\similar_topics->display_similar_topics()

FILE: [ROOT]/vendor/symfony/event-dispatcher/EventDispatcher.php
LINE: 184
CALL: vse\similartopics\event\listener->display_similar_topics()

FILE: [ROOT]/vendor/symfony/event-dispatcher/EventDispatcher.php
LINE: 46
CALL: Symfony\Component\EventDispatcher\EventDispatcher->doDispatch()

FILE: [ROOT]/phpbb/event/dispatcher.php
LINE: 62
CALL: Symfony\Component\EventDispatcher\EventDispatcher->dispatch()

FILE: [ROOT]/phpbb/event/dispatcher.php
LINE: 46
CALL: phpbb\event\dispatcher->dispatch()

FILE: [ROOT]/viewtopic.php
LINE: 2370
CALL: phpbb\event\dispatcher->trigger_event()


texts: Why does linked server need delegation where as file access require no delegation settings?

texts:
I have a ssis package deployed to ssis catalog in ServerA. It is configured to be run via sql agent job step.
The job step is set to Run As proxy user that is pointing to a domain user credential. Example: domainname\user1
Effectievly the ssis package is getting run in context of the above user.
The ssis package access a file on shared drive located on ServerB and dumps the data into a sql table on ServerC.
The sql table on ServerC is access via linked server configured on ServerA using the same windows account and impersonate is check marked. Delegation is not configured.
There is an error when dumping data to sql table via linked server since I have not configured delegation. My question is that - why does linked server need delegation for Windows domain user where as accessing a shared network file using the same account doesn't need any delegation?


texts: Why isn't mixed-mode replication default on MySQL

texts:
It is well known that mixed-mode replication was default from MySQL 5.1.12 to MySQL 5.1.28. However, this was reverted, and at the current moment, statement-based replication is default.

In the announcement that statement-based replication was default, Oracle claimed that this was only because mixed mode replication constituted a behavior change which may catch some users unaware. (See also Bug 39812.) However, MySQL 5.1 has many behavior changes, and it because the benefits of mixed-base replication so clear (I'm sure every DBA has run into a case where replication failed because a user submitted a query which didn't replicate properly and caused their databases to diverge), this doesn't seem like a particularly strong reason.

There are some mutterings that mixed replication "can provide the best of both worlds, but needs testing", but we're already on MySQL 5.1.61, and one would hope all of the bugs would have been ironed out by now. So that leaves us just rather confused. Why isn't mixed mode replication default?


texts:
I'm working on a issue with heavily fragmented indexes on a large production DB. I've pretty much identified the indexes that are heavily fragmented, including those that are not really being used. I plan to rebuild some and remove others. So my next step is to devise a before and after time test.

One of the symptoms of this is SSRS reports taking about an hour to render. I'm new to Report Server. I can see that a report is being embedded in the ASPX page using a ReportViewer control with the ServerPort ReportPath and ReportServerUrl properties set. My problem is trying to figure out how to time the display of the report from start to finish in the code-behind. I can write the start time to a file in the Page_Load but I can't figure out how to record the end time... Pre-Render could just hang and I'm not sure if this is the only page lifecycle event I can tap into to record this. Should I use a Windows Service, and if so, how would I trigger/record the start and end times that way?

I'd really appreciate some feedback on whether this is possible via the display page's code-behind.


texts: How to use an integer pivot in SQL Server?

texts:
I want to get multiples rows using a pivot:

FIXED  |    0    1    2    3    4
---- ---- ---- ---- ---- ---- ----
First  |    D1   C1   SP   A0   1B

I did it:

DECLARE @zero int = 0
DECLARE @one int = 1
DEClARE @two int = 2
DECLARE @three int = 3
DECLARe @four int = 4
DECLARE @col NVARCHAR(MAX) = '[' + CONVERT(varchar(MAX), @zero) + '], [' + CONVERT(varchar(MAX), @one) + '], [' + CONVERT(varchar(MAX), @two) + '], [' + CONVERT(varchar(MAX), @three) + '], [' + CONVERT(varchar(MAX), @four) + ']'
DECLARE @sql NVARCHAR(MAX)

 SET @sql = 'SELECT * FROM
            (
                SELECT Principal.Fixed, Code.Alpha, Code.Position
                FROM       Principal
                INNER JOIN Code ON Principal.Id = Code.PrincipalId
            ) SOURCE
            PIVOT
            (
                MAX(SOURCE.Alpha)
                FOR SOURCE.Position in ('+@col+')
            ) PIVOTABLE'

EXEC sp_executesql @sql

And the output always is NULL:

FIXED  |    0    1    2    3    4
---- ---- ---- ---- ---- ---- ----
First  |   NULL NULL NULL NULL NULL

What did I do wrong?


texts: SCAN verification error during 11gr2 installation

texts:
Hi All,
I am getting the following warning when installing RAC DB (11.2.0.4). Does any one has idea what could be the reason? I have tried to search from Metalink but no help. Can I proceed with the warning? What will be the impact if I ignore the warning and proceed?

Thanks in advance...


texts:
I have the environment that I explain below and I need to encrypt the data that is sent between my two nodes, I have been reading but I can not find the right solution, I explain:
I have an Oracle Database 12c in which I have Advanced Security implemented through the netmgr but the encryption between the databases does not work, when putting a capture of the packages with tcpdump is shown in plain text when I use dblink, these dblinks are they use to load information from different sources (Oracle, PostgreSQL and SQL Server ... the latter using Oracle Gateway). How can I encrypt the communication between the source and the destination and vice versa to use the dblinks? Thank you very much.