texts: MySQL Bug required remedy : [Warning] Invalid (old?) table or database name '#sql-'

texts:
We have a master & multiple slave replication in our production environment. At first, I found this error in the mysql error log on the master and later on the  slave. As for all the replication, the databases are fine, and slave updates are working well. Noevertheless, this error appears in the mysql error log. I just googled and checked. It's a bug.

Will it have any impact on the databases, as we had multiple db instances which is replicating master & multiple slave architecture?

Error Log :

111116 10:47:57 [Warning] Invalid (old?) table or database name '#sql-3d6f_122a#P#p7'
111116 10:47:57 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p8'
111116 10:47:57 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p9'
111116 10:47:57 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p10'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p1'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p2'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p3'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p4'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p5'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p6'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p7'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p8'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p9'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p10'
111116 10:48:46 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p1'
111116 10:48:46 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p2'
111116 10:48:46 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p3'
111116 10:48:46 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p4'
.....................................................................................;

Our error is in warning state. But as i googled and i can see the tag [error] state also:

[ERROR] Invalid (old?) table or database name 't1#p#p4'
[ERROR] Invalid (old?) table or database name 't1#p#p2'
[ERROR] Invalid (old?) table or database name 't1#p#p2'

I had a multiple Db instances around 70 & thousands of tables. What will be the query to trace out the particular table among multiple instances ?

Did any one faced this bug & solved the issue. I guess soon all my slaves will catch up this problem. What will be the impact in the production environment...?

Will enabling the innodb_lock_monitor cause many of these messages to be printed out!
create table innodb_lock_monitor(a int)engine=innodb; If so we handnt enabled. I had also seen in the innodb engine status. no sign of identity related to error/ warning.
"Do you start MySQL server with database created in previous version?" but i hadnt done it. It is running in the version 5.1.39
No plugins had been upgraded. So what could be root cause and remedy for this error/ bug..?


texts: "Connecting to Integration Service failed..." SQL Server 2016

texts:
Please see below. We have SSIS services installed and running, but it appears we cannot connect to them.

What is the path to get them set up and working?

TITLE: SSIS Execution Properties
------------------------------

Connecting to the Integration Services service on the computer "886319-DB1C" failed with the following error: "The specified service does not exist as an installed service.".

This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance.

For help, click: http://go.microsoft.com/fwlink/?LinkId=506689

------------------------------
ADDITIONAL INFORMATION:

Connecting to the Integration Services service on the computer "886319-DB1C" failed with the following error: "The specified service does not exist as an installed service.".

This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance.

------------------------------
BUTTONS:

OK
------------------------------


texts: Copying datadir directory enough for backing up a database with InnoDB tables?

texts:
I have an innodb table with 80 million rows and it's extremely slow with mysqldump so I'm thinking of copying the datadir directory for a backup / transfer.

I used to copy database with only MyISAM tables in Windows version of MySQL and it worked perfectly. Now that I'm copying a database directory with a mix of InnoDB and MyISAM tables from Linux to Windows, would this approach work as well?

I'm currently doing this right now but I want to know if I'm missing anything? Ain't InnoDB tables data spread across various places such as in ibdata1? I don't have to backup ibdata1 or anything right?


texts: Sql Server, Is "Reorganize Files Before Releasing Unused Space" better or worse?

texts:
I could not find a satisfactory answer. I fully understand that "Shrink" will cause index defragmentation, and that can worsen performance.

But, is not this option to reduce defragmentation?

So, to activate this option, is it better or worse? Please do not increase my confusion. I ask you to give me a clear and simple answer: it is better, or, it is worse.

Then if you want, give me your explanation that I will gladly read and appreciate.


texts:
Let say I have two queries, running in two separate sessions in SSMS:

First session:

UPDATE Person
SET Name='Jonny', Surname='Cage'
WHERE Id=42

Second session:

SELECT Name, Surname
FROM Person WITH(NOLOCK)
WHERE Id > 30

Is it possible that the SELECT statement could read a half-updated row, for instance one with Name = 'Jonny' and Surname = 'Goody'?

Queries are executed almost simultaneously in separate sessions.


texts: PostgreSQL: Save roundtrips / Network performance

texts:
For my work I often have to to large selects loading a lot of data. The connection happens with JDBC driver and high fetch size. This is not a problem when working directly on the server of the database. However when connecting from a different machine, the load times get terrible and it takes >10x times longer to load the data.

Note that the amount of data is actually small (100s of MB, 200k rows) and the network bandwidth can't be the limiting factor. Therefore it's almost certainly latency and hence round trips that cause the bad performance.

Latency issue seem confirmed by checking with wireshark and I see a huge back and forth between the client and server. (note both windows). Similar what was observed in this old topic here.

Are there any server or client side (JDBC) settings that would reduce roundtrips and lower the latency costs?

EDIT:

On same machine:
Remote: 20 min to load data (ping ~120ms)
local: 50 seconds (ping < 1ms)

where local means intranet and db server is in a building about 300 feet away. And on the actual server it's also 50 seconds. Explain analyze says 105ms.

I do understand the issue I simply wasn't aware the impact is that big. I also wonder if it's a windows issue as it seems there are far too many ack after very few data when connecting from remote. I should probably ask about that in a network forum.


texts: Minimum permissions for postgres server dump

texts: Sql request to create a user with the minimum rights necessary for the full backup of the server postgres (without data)


texts:
Would like to set up a key-value store that is sharded across multiple machines.

We are currently using MongoDB, is there a reason why we shouldn't use MongoDB for this purpose?

We also use Redis, however for this use case, we would like to use the hard drive and Redis is in-RAM only.


texts: Why does backup file size become enormous when Agent schedule is changed?

texts:
I have a database that is currently backed up every two hours.  This is a secondary backup, done in addition to the hosting provider's backup strategy.  I created a procedure (stored within master) to perform the following actions on a scheduled basis in SQL Agent:

If the backup file doesn't exist create it and perform a full (copy only) backup
If the backup file does exist, perform a transaction log backup

The code creates a backup file with a weekly date stamp (e.g. 2017_Week37_myDbName.bak), then drops and re-creates a new file each week.

The code is currently scheduled to run every two hours, which for my level of understanding meant I would have a maximum of two hours data loss in the event of failure.  The initial backup file created in step 1 is 230MB, which obviously increases every two hours as the transaction log backup is added.  Each week, the file reaches a size of around 7GB, before being deleted by the procedure.

I then changed the schedule to run every 15 minutes, but noticed that the backup file went over 230GB - a gigantic increase.  There may be a bug (which I cannot figure), but I'm wondering why this would happen?  I thought that the transaction log backup would only contain the transactions backed up since the last backup, so regardless of the frequency, the backup file would always be a similar size before it was deleted.  I should clarify that at no point was the database used any more frequently than normal.  Basically, I am trying to create differential-style backups after the initial full backup.

CREATE PROC [dbo].[BackupMyDB]

    @myDbName nvarchar(50)

AS

    DECLARE @year nvarchar(4) = CAST(YEAR(GETDATE()) AS nvarchar);
    DECLARE @weekNumber nvarchar(2) = CAST(RIGHT('0' + RTRIM(DATEPART(wk, getdate())), 2) as nvarchar);
    DECLARE @backupFileName nvarchar(max) = @year + '_Week' + @weeknumber + '_' + @mydbname; -- '2013_Week37_myDbName'
    DECLARE @backupPhysicalPath nvarchar(100) = N'C:\SqlScheduledBackups\';
    DECLARE @fullPath nvarchar(150) = @backupphysicalpath + @backupFileName;
    DECLARE @backupName nvarchar(200);

    IF NOT EXISTS (SELECT * FROM sys.backup_devices WHERE (name=@backupFileName))
        BEGIN

            -- create backup device and perform full backup
            EXEC master.dbo.sp_addumpdevice  @devtype=N'disk', @logicalname=@backupFileName, @physicalname=@fullpath;
            SET @backupname = @myDbName + '-Full Database Backup';
            BACKUP DATABASE @mydbname TO  @backupFileName WITH COPY_ONLY, COMPRESSION, RETAINDAYS=45, NOFORMAT, NOINIT,  NAME=@backupname, SKIP, NOREWIND, NOUNLOAD, STATS=10;

            -- delete the previous backup file
            DECLARE @previousBackupName nvarchar(200);
            SELECT
                  TOP 1 @previousBackupName = o.name
            FROM
                sys.backup_devices o
            WHERE
                (o.name <> @backupfilename) AND
                (o.name LIKE @year + '_Week_%_' + @mydbname)
            ORDER BY
                o.name ASC;

            IF @previousBackupName IS NOT NULL
                BEGIN
                    EXEC master.dbo.sp_dropdevice @logicalname = @previousBackupName, @delfile='delfile';
                END
        END
    ELSE
        BEGIN
            SET @backupname = @myDbName + '-Transaction Log Backup';
            BACKUP LOG @mydbname TO  @backupFileName WITH COPY_ONLY, COMPRESSION, NOFORMAT, NOINIT,  NAME=@backupname, SKIP, NOREWIND, NOUNLOAD, STATS=10;
        END
GO


texts: Random and constant "Log Growths" Alerting, how to make it stop?

texts:
I've set up alerting on all my database servers and every once in awhile I'll get the following error, sometimes it goes on and on and on, other times it'll be a few times here and there.

The SQL Server performance counter 'Log Growths' (instance '_Total') of object 'SQLServer:Databases' now equals the threshold of 1.00.

Are there any configurations that I can change to stop this?  It only appears to be coming from two servers.


texts: Daily database maintenance using SQL Server 2008 and a stored procedure

texts:
I have created a database maintenance plan using wizard though that link for SQL Server 2005.

I have created a Maintenance Plan for SQL Server 2008 and it works fine.

Now I required to complete the same task using a stored procedure for my desktop application.

I have tried this stored procedure for that but it doesn't works for me.

Stored Procedure Execute Successfully and also i can see the entered values in the table sysdbmaintplans of  msdb the system database but created maintenance plan doesn't shows Under tree(MANAGEMENT=>MAINTENANCE PLAN) of Object Explorer(SQL Server Management Studio).

Please Help...

Below is the code snippet I have tried.

//   sp_add_maintenance_plan [ @plan_name = ] 'plan_name' ,
//@plan_id = 'plan_id' OUTPUT

String planid = "";

string maintenancePlan = "MyMaintenance";

string SQLstr = "DECLARE @plan_id as Varchar(50)";
SQLstr += " EXEC sp_add_maintenance_plan ";
SQLstr += " @plan_name='" + maintenancePlan + "',";
SQLstr += " @plan_id= @plan_id OUTPUT";
SQLstr += " SELECT @plan_id";

SqlConnection cnn = new SqlConnection(MSDBconnStr);
SqlCommand cmd = new SqlCommand(SQLstr, cnn);

cnn.Open();
planid = (string)cmd.ExecuteScalar();
cnn.Close();

//   sp_add_maintenance_plan_db [ @plan_id = ] 'plan_id' ,
//[ @db_name = ] 'database_name'

string maintenancePlanDataBase = "MyDataBase";
string SQLstr1 = " EXEC sp_add_maintenance_plan_db ";
SQLstr1 += " @plan_id='" + planid + "',";
SQLstr1 += " @db_name='" + maintenancePlanDataBase + "'";

SqlConnection cnn1 = new SqlConnection(MSDBconnStr);
SqlCommand cmd1 = new SqlCommand(SQLstr1, cnn1);

cnn1.Open();
cmd1.ExecuteNonQuery();
cnn1.Close();


texts: Stored procedure working when run manually, not running in SQL Agent Job

texts:
When I execute a Stored Procedure through a SQL Agent Job, I get the following error message:

  Executed as user: NT AUTHORITY\SYSTEM.
  XML parsing: line 10, character
   33, unexpected end of input [SQLSTATE 42000] (Error 9400).
  The step failed.
  Sql Severity 16, Sql Message ID 9400

However, when I run it manually, it is successful.

This is the procedure which I am executing:

decalre @URL VARCHAR(max)
 set @URL='http://www.spa.gov.sa/english/rss.xml'

 declare  @xmlT TABLE ( yourXML XML )
DECLARE @Response nvarchar(max)
DECLARE @XML xml
DECLARE @Obj int
DECLARE @Result int
DECLARE @HTTPStatus int
DECLARE @ErrorMsg nvarchar(MAX)

EXEC @Result = sp_OACreate 'MSXML2.XMLHttp', @Obj OUT

EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false
EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
EXEC @Result = sp_OAMethod @Obj, send, NULL, ''
EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT

INSERT @xmlT ( yourXML )
EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--, @Response OUT

    INSERT into Tlb(discp , tit ,   datee,linkk)
    SELECT   N.C.value('description[1]', 'nvarchar(max)') discp,   N.C.value('title[1]', 'varchar(999)') tit,
    N.C.value('pubDate[1]', 'varchar(99)') datee,N.C.value('link[1]', 'varchar(999)') linkk
    FROM @xmlT CROSS APPLY yourXML.nodes('//channel/item') N(C)


texts:
I have a table called visits that is structured like so:

+----+--------------+-------------+
| id | name         | visitor_id  |
|----+--------------+-------------+
| 1  | event1       | 1           |
| 2  | event2       | 2           |
+----+--------------+-------------+

I have a table called params that is structured like so:

+----+--------------+---------------+---------------+
| id | a_key        | a_value       | visit_id      |
|----+--------------+---------------+---------------+
| 1  | key1         | value1        | 1             |
| 2  | key2         | value2        | 1             |
| 3  | key3         | value3        | 1             |
| 4  | key1         | value1        | 2             |
| 5  | key2         | value2        | 2             |
+----+--------------+---------------+---------------+

This query looks for visits with params that are 'key1' and 'value1'.

select *
from visits
right join params as p on p.visit_id = visits.id
where a_key = 'key1' and a_value = 'value1'

I need to write a query that will give me all the visits that have params 'key1', 'value1' and also 'key3', 'value3'. In this case it would only return visit 1.

What is the most performant way to achieve this?

I thought about joining the params on again, but that doesn't seem like the best solution.

Here it is in a fiddle:

http://sqlfiddle.com/#!15/06773/5


texts:
I am planning to use Cassandra in memory tables for my particular work load. THe problem is I am worried if the node fails then what will happen to the data in memory that was not flushed to disk ?

Would I loose that data, or not ?


texts:
SQL Buffer pool is consuming more memory in sql server 2017. Is there any way to reduce it??
or How can we find what is consuming more? We are receiving alert for low buffer cache hit ratio.
MEMORYCLERK_SQLBUFFERPOOL    90365.62 MB
One particular database alone consuming 87GB buffer pool. Total memory allocated to this instance is 100GB


texts: Select multiple ranges in a WHERE clause

texts:
I am trying to find a way to query a tables in my database. The table is called districts which contains a boroid and area column. Basically the community table looks like:

boroid  |  area
--------+---------------
101     | 41641860.1953
104     | 49300667.7004
106     | 38700249.8776
112     | 77981905.0100
210     | 174703337.756
301     | 132531300.780
306     | 131678770.100
303     | 246427540.049
302     | 167440618.596
402     | 171605041.803

I am trying to find all community districts within the ranges 101-108, 301-303 and only 402. Can someone help me achieve this? Below is my query:

SELECT boroid from districts where ('[101-108]','[301-303]','[402]');


texts: What is the best choice for MySQL high availability (zero downtime)?

texts:
As I know there are many solutions for MySQL HA, but most of them are congenitally not zero downtime solution, like MHA, AWS RDS...

That said, if zero downtime is "must have", the only choice is MySQL cluster/MariaDB Galera ?


texts: TSQL: Find Queries causing too many SQL Compilations and SQL ReCompilations seperately

texts:
I want to find out what is causing the high SQL Compilations (not re-compilations) I am seeing in performance monitor counters.
Here is my take on it:
If I am seeing a lot of SQl compilations, then it means that the queries on our system are not getting cached for following reasons:

Many adhoc queries

Running queries which SQl doesn’t cache e.g. :
UPDATE table1
SET col1= 'String longer than 8000 characters .....'
WHERE key_column = some int

Plans are timing out and being removed from the cache because: Cache is running out of space or plans are not being used long enough.

The only thing which goes near capturing cache inserts in profiler is Stored Procedures->SP:CacheInserts but it only looks after stored procedure cache.
So I tried the following to get adhoc queries:
SELECT [cp].[refcounts] -- when Refcounts becomes 0, plan is excluded from cache.
    , [cp].[usecounts]
    , [cp].[objtype]
    , st.[dbid]
    , st.[objectid]
    , st.[text]
    , [qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st
CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp ;

I thought queries that caused the compiles should be the ones with objtype= Adhoc but this could also relate to re-compilations. Now I have to run profiler, capture queries causing re-compilations and then exculde it from the above list.
Am I going in the right direction?
Is there a single query can I can use to achive just SQL compilations without too much work?
Resources which helped me in achiving the above knowledge:
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/954b4fba-3774-42e3-86e7-e5172abe0c83
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=143946
http://technet.microsoft.com/en-nz/library/cc966425(en-us).aspx
http://www.sqlservercentral.com/Forums/Topic914951-360-1.aspx
Any help is really appreciated.


texts: How can I find a view column's base table column?

texts:
I program, administer, and support an ERP application. One of my more common tasks is to understand where data originates, finding the link between a particular report field and its data entry field in the application. To do this, I have to manually examine the DDL SQL of one or more views in order to understand the table columns, in order to match one view's table columns with another view's table columns. This manual task might require examination of four or five abstraction layers.

Is there a faster way to examine the metadata to find how a view column is calculated from its base table column? Is there a way to examine Oracle's execution plan to see the rewritten query, skipping the manual inspection of the intermediate views?


texts:
I have table called result:

CREATE TABLE result
    (id_graduate                    NUMBER NOT NULL,
    id_sml                         NUMBER NOT NULL,
    last_update                    DATE NOT NULL,
    id_kt                          NUMBER NOT NULL,
    value_kt                       NUMBER NOT NULL,
    id_res                         NUMBER NOT NULL)
/
ALTER TABLE result
ADD CONSTRAINT pk_id_res PRIMARY KEY (id_res)

And an index:

CREATE UNIQUE INDEX x_pk_id_res ON result
  (
    id_res                          ASC
  )
  STORAGE   (
    INITIAL     65536
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
NOPARALLEL
LOGGING
/

When I run SQL query #1, Oracle uses index X_PK_ID_RES

select ss.*
from V_JOURNAL ss, result r
where TO_NUMBER(ss.ID_SML||ss.ID_KT||ss.ID_GRADUATE)=r.id_res

But, when I run SQL query #2, Oracle does not use index X_PK_ID_RES. Instead it uses an ACCESS FULL TABLE access path.

select ss.*,r.*
from V_JOURNAL ss, result r
where TO_NUMBER(ss.ID_SML||ss.ID_KT||ss.ID_GRADUATE)=r.id_res

So, I added a new column id_res2:

ALTER TABLE RESULT  ADD (  ID_RES2 NUMBER );

And I created a new index:

CREATE UNIQUE INDEX x_id_res2 ON result
  (
    id_res2                          ASC
  )
  STORAGE   (
    INITIAL     65536
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
NOPARALLEL
LOGGING

When I run SQL query #3, Oracle uses index X_ID_RES2.

select ss.*
from V_JOURNAL ss, result r
where TO_NUMBER(ss.ID_SML||ss.ID_KT||ss.ID_GRADUATE)=r.id_res2

And, when I run SQL query #4, Oracle uses index X_ID_RES2 too.

select ss.*,r.*
from V_JOURNAL ss, result r
where TO_NUMBER(ss.ID_SML||ss.ID_KT||ss.ID_GRADUATE)=r.id_res2

Why did Oracle use an index in SQL #4, but did not use one in SQL #2?


texts: Does LIMIT 1 still have effect on a unique column

texts:
I know that adding LIMIT 1 when searching for only one record makes a lot of difference when it comes to performance. But does it still have any effect when we search a column with a unique constraint?

Let us say I store email records with a id (int) primary key column and the email field is marked as unique. Do I still need to explicitly call LIMIT 1 in my queries to speed up the returning of a result set? Or does the MySQL engine understand that as soon as one record is found the result can be returned since the field is marked as unique and is LIMIT 1 obsolete?

MySQL table definition:

CREATE TABLE Email
(
    `id` int NOT NULL,
    `email` varchar(255) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE (`email`)
)

Does it in this case make a difference whether I do:

SELECT * WHERE `email`="john.doe@example.com";

Or

SELECT * WHERE `email`="john.doe@example.com" LIMIT 1;


texts: MySQL: What field types for large numbers?

texts:
I need to store some high numbers, some with decimal points (2 digits).

965.630,5       INT
47.320.500      INT
-62.188,0       INT
32,8%           FLOAT
225.165,7       DOUBLE
715.100,7  DOUBLE
6.929.850,0 DOUBLE

I am not sure what types are right. I don't get the difference between DOUBLE and FLOAT. And when do I use DECIMAL/NUMERIC? Hope someone can help. Please don't link to MySQL docs. I don't understand what they wrote :-(


texts: array_length if array is empty behaviour

texts:
Please explain to me this behavior.

This SQL finds elements where ids (type is array::bigint) is empty.

SELECT * FROM rises WHERE ids = '{}'
-- finds, e.g., 9 rows

This SQL finds no rows:

SELECT * FROM rises WHERE array_length(ids, 1) = 0
--finds always 0 rows

But this SQL can find non-empty arrays

SELECT * FROM rises WHERE array_length(ids, 1) > 0
--finds, e.g., 15 rows

Initialization:

CREATE TABLE rises(
    id bigserial,
    d1 bigint DEFAULT 0,
    ids bigint[] DEFAULT '{}',
    PRIMARY KEY (id));

Why array_length can find non-empty arrays but it doesn't work for empty arrays?


texts: How to convert SQL Server .MDF database to Mac OS PostgreSQL database on Mac OS X?

texts:
I need to import a SQL Server database which is in the .mdf format into a PostgreSQL database. My system is Mac OS X.

How to do it in the easiest way?


texts: Using postgres_fdw to connect from one read replica to another

texts:
I have two postgres database servers in AWS, A and B. These databases have different tables. Both databases also have their own read replica.

My goal is a simple solution for doing cross database joins from A replica to B replica. Querying from master is not an option, as this feature is meant for the analyst on my team, not for an application.

I set up postgres_fdw on A's master following the instructions by doing CREATE SERVER, CREATE USER MAPPING, and CREATE FOREIGN TABLEs, and I was able to easily query from A master to B replica. However, these server and foreign table definitions did NOT replicate to A replica. I am unable to run these create commands on the replica as it is read-only.

Is it possible to use postgres_fdw from A replica to B replica? If not, is there another solution for this problem?


texts: Why does CURRENT_LOAD_SOURCE() return "Batch No. 1" instead of the actual source file name?

texts:
I'm loading some data into a table like this:

COPY table1 (
    col1,
    col2,
    file_name AS CURRENT_LOAD_SOURCE()
)
FROM LOCAL :src_file
REJECTED DATA :rejected_file
EXCEPTIONS :exceptions_file
SKIP 1;

It works, except file_name gets populated with the string Batch No. 1 instead of the actual base name of :src_file.

The doc for CURRENT_LOAD_SOURCE() doesn't explain why this happening.

Is this a bug? How do I get the actual name of the source file?


texts:
I have one table named as temp_stock_reco. I have created one procedure for update two columns in that table.

CREATE OR REPLACE PROCEDURE TEST_RECPT_0705
is
CURSOR RECEIPT IS
select sr_cd,to_char(trunc(transdate,'mm'),'YYYYMM')Month,
sum(quantity)Receipt from transdetail
where transtype IN('STKREC','RP','RECADJ','SRET') and trunc(transdate,'mm')
between '01-April-2013' and '31-March-2014'
group by trunc(transdate,'mm'),sr_cd;
CURSOR ISSUE IS
select sr_cd,to_char(trunc(transdate,'mm'),'YYYYMM') Month,sum(quantity) Issue
from transdetail
where transtype IN('SINV','IP','STA','ISSADJ','PURRET')
and trunc(transdate,'mm') between '01-April-2013' and '31-March-2014'
group by trunc(transdate,'mm'),sr_cd;
BEGIN
OPEN RECEIPT;
LOOP
UPDATE temp_stock_reco SET RECEIPT = I.RECEIPT
WHERE SR_CD = I.SR_CD
AND YYYYMM = I.MONTH;
OPEN ISSUE;
LOOP
UPDATE temp_stock_reco SET ISSUE = J.ISSUE
WHERE SR_CD = J.SR_CD
AND YYYYMM = J.MONTH;
END LOOP;
CLOSE ISSUE;
END LOOP;
CLOSE RECEIPT;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||'
-ERROR- '||SQLERRM);
END;

i want to reduce this one a single by using decode function.Kindly help me to do this one


texts: Most efficient way of updating table from another - Postgres

texts:
I need to populate a foreign key column based on the contents of another table, and both methods I've tried are prohibitively slow.

I have the below tables (some columns omitted):

              Table "public.interface_events"
           Column           |  Type   |     Modifiers
----------------------------+---------+--------------------
 device_id                  | integer | not null
 interface_id               | integer |

Foreign-key constraints:
    "interface_events_device_id_fkey" FOREIGN KEY (device_id) REFERENCES device(id) ON UPDATE CASCADE ON DELETE RESTRICT
    "interface_events_interface_id_fkey" FOREIGN KEY (interface_id) REFERENCES interface(id) ON UPDATE CASCADE ON DELETE RESTRICT

                                       Table "public.device"
        Column        |         Type          |                      Modifiers
----------------------+-----------------------+-----------------------------------------------------
 id                   | integer               | not null default nextval('device_id_seq'::regclass)
 interface_id         | integer               | not null

Indexes:
    "device_pkey" PRIMARY KEY, btree (id)
    "device_interface_id_idx" btree (interface_id)

Foreign-key constraints:
    "device_interface_id_fkey" FOREIGN KEY (interface_id) REFERENCES interface(id) ON UPDATE CASCADE ON DELETE RESTRICT

Column interface_id in table interface_events needs updating from table device, so currently I have:

UPDATE interface_events SET interface_id = device.interface_id
FROM device
WHERE device.id = interface_events.device_id

I should point out that the relationship between device and interface_events is one to many, so there may be thousands of events per device. Is the UPDATE FROM method suitable in this situation? I'd earlier tried:

UPDATE interface_events SET interface_id = (SELECT device.interface_id FROM device WHERE device.id = interface_events.device_id);

But that took forever also.

There are 30 million rows in table interface_events, 32,000 in table device.

The update running currently (using the FROM syntax) has been active for 21 hours, although this is on a low spec test machine (2 x 3G cores, 3G RAM, spindle disk - it's disk I/O that's killing it, constant 99%).

It occures to me as I write I should have applied the foreign key constaint after the update, since I know it's integrity will be ok thanks to the column I'm copying from already being constrained, so I guess this question is more about the best query to use?


texts:
We have the following setup:

Ec2 instance running a jboss server with mariadb (1.1.7) datasources setup to connect to an rds instance running MySQL 5.6.21.

We would like to turn on SSL on for this connection so took the following steps:

Add require ssl to the MySQL users.
Verify that this has worked by first running:

mysql -h HOSTNAME -u USER -pPASS

and getting the expected

ERROR 1045 (28000): Access denied for user 'User'@'10.xxx.xxx.xxx' (using password: YES)

then trying

mysql -h HOSTNAME -u USER -pPASS --ssl_ca=rds-combined-ca-bundle.pem

this connects no problems.

So, great, it appears that ssl has been setup just fine. Now time to modify the -ds.xml files that define our application's connection.

From searching I found that you must add ?useSSL=true; to the end of the url defined in your datasource.

We tried running this and got access denied when the server tried to open those connections. Thinking it was because we had not specified the key, we then added that to the java cacerts file. No change. So instead we tried to add ?useSSL=true;serverSslCert=/tmp/rds-combined-ca-bundle.pem; still no change, getting a stack trace on connection.

Caused by: org.mariadb.jdbc.internal.common.QueryException: Could not connect: Access denied for user 'User'@'10.xxx.xxx.xxx' (using password: YES)

I've tried various other url parameters and certificate stores but still no change. Can anybody suggest anything?

Cheers


texts: Clearing plan cache improves performance - bad plans in cache?

texts:
I have run into this situation a number of times on a VM with SQL Server Express 12.0.6205.1 (SQL Server 2014 SP3 CU1)
Using SSMS, I'll run a stored procedure a few times.  It consistently takes, say 4 seconds to run. ("Wait time on server replies" in "Client Statistics").  Then I clear the plan cache:
DBCC FREEPROCCACHE

Now the same stored procedure will consistently take 400 ms to run.
This VM is under VERY light load.  It's just me poking at it every few days.
This VM is a copy of a production server that is having performance problems.  So it seems like maybe bad plans are getting stuck in the plan cache?  I would like the calls to be consistently fast, obviously.  What is the problem and how to I fix it?


texts: How to model when you could have many (thousands of) attributes to a table (product)?

texts:
We have a main table, let's say, Product. It has several general attributes like 'Product Name', Manufacturer ID, etc. As of now, all these attributes are implemented in the data model as columns to the Product.

Now we have a new requirement where there could be thousands of attributes. Color, size, weight, dimensions, style, etc. Each of these attributes have pre-defined values. Like color can only be ('green', 'yellow', 'red'). The value set is defined for each attribute. But there could be thousands of attributes and each attribute could have thousands of pre-defined values.

So, for this new feature, we defined these attributes as 'row values' instead of 'columns'.

Something like this:

Product Table

ProductID   Name    Date_Created
1           Candy     some date
2           Shirt     some date
3           Pants     some date

Attribute Table

AttributeID   Name
1             Color
2             Dimension
3             Weight

AttributeValue Table

AttributeValueID   AttributeID    Value
1                  1              Green
2                  1              Yellow
3                  1              Red
4                  2              11x11
5                  2              12x12
6                  2              13x13
7                  3              1 lb
8                  3              2 lb
9                  3              3 lb

Now to create association between Product Table and Attribute table, we have an association table called 'ProductAttributes'

ProductAttributes Table

ProductAttributeId      ProductID      AttributeID      AttributeValueID
1                           1              1                   1
2                           1              2                   4
3                           1              3                   9
4                           2              1                   2
5                           2              3                   8
6                           3              3                   8

Does this model make sense? Could you suggest any improvements? As of now this ProductAttributes Table is expected to grow to 200 Million as there are a more than 100K attributes.

Other points:
1. A product could have more than one attribute
2. A product could have more than one of the same attribute. A shirt is both 'brown' and 'green'
3. All Attributes and its Values have to be pre-defined (loaded from text file)
4. Once the data is loaded (once every few months), there is heavy SELECTing. So the model needs to be read-optimized.


texts: UPDATE with CAST(meta->>"$.field") failing due to "null"

texts:
This issue is affecting MySQL 5.7.25.

So, in a migration I'm moving legacy fields from the table into a JSON column. This part of the process works fine. But, the rollback is failing miserably! At this point, I'm considering moving the JSON field logic out of SQL into PHP.

You can reproduce the issues as follows.

DB Setup

CREATE TABLE `test_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `meta` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test_table` VALUES
(null, '{"test_dt": "2019-03-28 15:43:59", "test_enum": "foo"}'),
(null, '{"test_dt": "2019-03-28 15:44:15", "test_enum": null}'),
(null, '{"test_dt": null, "test_enum": "bar"}');

ALTER TABLE `test_table`
  ADD COLUMN `test_enum` ENUM('foo', 'bar') NOT NULL DEFAULT 'foo',
  ADD COLUMN `test_dt` DATETIME NULL;

The Problem

Is there a workaround for the following query? I would prefer just 1 update query if possible...

-- copy from meta back into legacy fields
UPDATE `test_table` SET
  `test_enum` = `meta`->>"$.test_enum",
  `test_dt` = CAST(`meta`->>"$.test_dt" AS DATETIME);

Row 2 will cause an error: Data truncated for column 'test_enum'. This seems to be because both meta->>"$.test_enum" and JSON_EXTRACT(meta, "$.test_enum") are returning "null" instead of NULL.

Now try again without the enum field update:

-- copy from meta back into legacy fields
UPDATE `test_table` SET
  `test_dt` = CAST(`meta`->>"$.test_dt" AS DATETIME);

Row 3 now causes an error: Incorrect datetime value: 'null'

I know I can work around the errors by breaking the query into two like:

UPDATE test_table
SET test_enum = `meta`->>"$.test_enum"
WHERE JSON_TYPE(JSON_EXTRACT(`meta`, "$.test_enum")) != 'NULL';

UPDATE test_table
SET test_dt = `meta`->>"$.test_dt"
WHERE JSON_TYPE(JSON_EXTRACT(`meta`, "$.test_dt")) != 'NULL';

Confusion

SELECT-ing the data via CAST works fine, even though the ENUM-related column is invalid due to the "null" value. Why doesn't JSON_EXTRACT() return NULL properly?

SELECT
  `meta`->>"$.test_enum" AS `test_enum`,
  CAST(`meta`->>"$.test_dt" AS DATETIME) AS `test_dt`
  FROM test_table

"id","test_enum","test_dt"
1,"foo","2019-03-28 15:43:59"
2,"null","2019-03-28 15:44:15"
3,"bar",NULL

Right now, I'm just wondering why MySQL acts this way with its NULL handling in both JSON and the CAST/CONVERT methods. I feel like I'm missing something obvious.


texts:
How can I add a total row to this, so that my query will run then display a total column for each row returned?

 set @bodytext = cast( (
select td = '' + loggedOnUserName + '	' + locatoinloggedOnFrom + '	 '
from (
          Select * from #info Where loggedOnUserName = @loggedOnUserName
      ) as d
for xml path( 'tr' ), type ) as varchar(max) )

set @bodytext
= '
'
+ '
loggedOnUserName	locatoinloggedOnFrom
'
+ replace( replace( @bodytext, '&lt;', '<' ), '&gt;', '>' )
+ '
loggedOnUserName	locatoinloggedOnFrom
'
+ '
'


texts:
I created container orcl2 database with pluggable database pdborcl2. When I try to connect

SQL> connect system@pdborcl2

I get this error:

  ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

My tnsnames.ora look as follows:

ORCL2 =  (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = orcl2.localhost)))
PDBORCL2 = (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdborcl2.localhost)))

I can normally connect to orcl2 with:

SQL> connect system@orcl2


texts: Simple Mode - Is using checkpoints to truncate the transaction log a bad practice?

texts:
I have a database with simple recovery mode. For that database I have a job running every morning. This job imports data and executes plenty stored procedures that inserts data, updates records, delete records, makes calculations... The job takes approximately 30 minutes and makes the transaction log be 10GB big. To avoid growth of the log file during the job I set the log size to 11GB. With that amount it is never autogrowing. Nevertheless it is blocking 11GB of disk space.

I just read that putting checkpoints truncates the log. So my idea is to put checkpoints at several location in my stored procedures in order to be able to minimize the size of the log (from 11GB to lot less: 3GB for instance).

Question: is it dangerous to do that take into consideration once again that I will never have to make recoveries?


texts:
Using Azure SQL Server, on a managed instance (i.e. a single server running multiple databases, where the db in question is one of them).

I've created a trigger on INSERT to table A, that performs an insert of the same data (with some massaging) to table B.

When checking the sys.triggers table, it seems that the trigger is there and is not disabled. When performing a single manual insert into the table for testing purposes, that trigger works. When looking at the audit logs for that database, that insert is indeed there (i.e. the insert created by the trigger).

When an application is doing the insert, however, the trigger does not fire. I.E. no rows are inserted the table B, and the audit logs are not showing inserts created by the trigger (i.e. the inserts to table B), but are showing the inserts to table A.

Not sure how to debug this further - suggestions for further explorations / solutions are welcome.


texts: SQL Server: Does the sysadmin role encompass all the other roles?

texts:
When creating a new login on SQL Server you have to explicitly add server roles to that user. You have the option of checking the 'sysadmin' role along with all the other roles. Is there any point to this?

I.e. if you add the 'sysadmin' role to a user surely that encompasses all the other roles?


texts: Finding the last command issued by a blocking process

texts:
I've developed an interface from ERP SAP Business One to warehouse management, and am facing problems concerning database locks. My program includes direct write operations into the ERP database (a user defined field is filled during asynchronous event processing after creating or changing a sales order). It is suspected to be responsible for blocking situations occurring quite often each day.

When a blocking situation occurs, sp_who2 gives me information about blocking and blocked processes, every time pointing to an spid used by the BPM management software as the "root blocking process". The status of this spid is always shown as "sleeping".

Can a "sleeping" process cause a database lock for other processes, e.g. when transactions weren't committed?
How can I find out the last command of the sleeping process?


texts: Huge Data Migration in Oracle and space concerns

texts:
I am trying to do Insert append as part of data migration from SchemaA.table1 to SchemaB.table2 in the same Oracle database. Due to refactoring of Table2, there will be some form of data transformation occurring as part of this migration. i.e. I can't just do some kind of partition exchange here.

Due to the huge size of data, this may take anywhere from 10 hours to 30 hours. I don't want to fail the migration at the very end because of insufficient tablespace. Is there a way in Oracle to predetermine the space requirements based on the size of the data in table1 so that I can fail right way asking the user to increase the table space?

Alternatively, is there an efficient way to remove the data from table1 as I move them to table2 ? Assume that user would have already backed up the source data from schema A. This way, I guess I don't need to worry about space issues??


texts: SQL Server 2005 on Windows 7 along with SQL Server 2008 R2 Express edition

texts:
I've installed SQL Server 2008 R2 Express edition on my Win 7 notebook. Using since an Express edition this doesn't have SQL Server profiler enabled. Thus it's hard for me to debug my SQL Server 2005 applications.

I have a license for SQL Server 2005 (32 bit) and when I tried to install the client tools it gave a "known compatibility" error message. Is it possible to install SQL Server 2005 client tools (for 32 bit) only in the same machine?

Below is list of software on my machine

Windows 7
SQL Server 2008 R2 Express edition
Visual Studio 2010

Can any one advise.

thanks a zillion


texts: Switching between databases with dynamic SQL

texts:
I have a process which involves executing various commands between multiple databases - however, when I use dynamic SQL to change DB with 'use @var', then it doesn't actually change the database.

Executing this in [test_db]:

declare @currentDB varchar(max)
declare @sql varchar(max)

set @currentDB =  DB_NAME()
set @sql = 'use  [' + @currentDB +']'

use master

exec(@sql)

select  DB_NAME()

Returns [Master] as the current database name - if I put use [test_db] as a command, rather than dynamically, then it returns the correct name.

Is there a way to do this which will correctly switch between databases?


texts: Receiving error while creating stored procedure to insert Excel files into a table

texts:
I was wondering if you could tell me what is wrong with my query?

The parameter @TableName is an input parameter.

Why do I have to declare it again?

CREATE PROCEDURE [dbo].[OpenExcel]
    @FileName nvarchar(2000),
    @TableName varchar(2000)

AS
    BEGIN
        DECLARE @Provider nvarchar(2000)
            SET @Provider = 'Microsoft.ACE.OLEDB.12.0'

        DECLARE @etValue nvarchar(4000)
            SET @RetValue = 'select *
                             from openrowset(''' + @Provider +
                             ''',''Excel 8.0;Database=' +
                             @FileName + ';hdr=no' + ''',
                             ''select " from [Sheet1$]'' )'

        DELETE FROM @TableName

        INSERT INTO @TableName

        EXEC(@RetValue)

    END

An image of the stored procedure

The error message I receive is:

  Msg 1087, Level 16, State 1, Procedure OpenExcel, Line 26
  Must declare the table variable "@TableName".

Well I'm trying to pass the Excel path (@FileName) and table name (@TableName; in which the data should be inserted) to the stored procedure.

How can I achieve that?

I have different tables with different structures. Please help me with this issue.


texts: Any drawbacks to referencing multiple tables from a single column?

texts:
I'm working on a system with the following entities:

Projects
Sections
Activities

There is one important constraint that needs to be enforced:

Activities are always part of one project. Activities can also be part of one section, but only if that section is part of the activity's project.

I came up with the following solution:

One FK is a reference from activities.projects_id to projects.id.

The other (composite) FK is a reference from activities.projects_id, activities.sections_id to sections.projects_id, sections.id. The column activities.sections_id is NULLable.

The table sections has a composite PK of id, projects_id.

The solution seems to do exactly what I want. I assume that the constraint on activities.project_id is satisfied when either projects.id or sections.projects_id has a value? Any light on this?

Are there any drawbacks to this solution? Any improvements or alternatives I'd need to consider?


texts: SQL Performance Large Variable Varchar into Small Column

texts:
Are there any performance issues placing larger varchar variable into small column?
I did not see any performance difference in the execution plan. Just want to validate.

Example,

create table dbo.Product
(
    ProductId int identity(1,1),
    ProductName varchar(25),
    ProductDescription varchar(255),

    constraint pk_ProductId Primary Key (ProductId)
)

Case 1 Large varchar(4000)

declare @ProductNameVar varchar(4000),@ProductDescriptionVar varchar(4000)

set @ProductNameVar = 'Table'
set @ProductDescriptionVar ='manufactured , oak table, round edges..'

INSERT INTO dbo.Product(ProductName, ProductDescription)
VALUES (@ProductNameVar, @ProductDescriptionVar)

Case 2 Small Varchars

declare @ProductNameVar varchar(25),@ProductDescriptionVar varchar(255)

set @ProductNameVar = 'Table'
set @ProductDescriptionVar ='manufactured , oak table, round edges..'

INSERT INTO dbo.Product(ProductName, ProductDescription)
VALUES (@ProductNameVar, @ProductDescriptionVar)


texts: Options for Index Defragmentations For Critical Tables

texts:
I wonder if this is a suitable solution for index defragmentations.
scenario is like this;

There is a table named 'Events' which is very critical and has 4 indexes which are not done maintenance before and fragmentation rates are around 95. Also table has 450 million rows. There are very much transactions in a minutes and Board can not tolerate to cuts for long time like 30 minute or higher.

I want to create a new table named 'Events_2' and create same indexes and statistics after all process done, I want to drop 'Events' table, and rename 'Events_2' to 'Events'.

Is it a solution for this situation or Index rebuild or Reorganize is the only solution?

Thanks For Reply.


texts: I dropped a view 20 minutes ago.  Can I recover it? If yes, how?


texts: After log shipping, I activate failover with a RESTORE. What happens when the next log shipping job runs?

texts:
I'm using log shipping for the first time. I've got it working between my primary and secondary server, on the secondary server the database is Restoring... which I understand basically means it is waiting to be activated.

I will run RESTORE DATABASE MyDatabase WITH RECOVERY to activate it, but then what happens next time the job runs to do log shipping? Will it fail? Should I disable the job manually? Will it overwrite my database and set it back to Restoring... ?

Thanks


texts:
I'm running MySQL 5.1.41 (innoDB) on Ubuntu 12.10.

I noticed that the MySQL performance will be better immediately after I restart MySQL.
This smells like something bad, so I'm wondering what could cause this?

SHOW PROCESSLIST shows usually around 5 connections (each in Sleep) before I restart.

By "better performance" I mean a significant difference. A tasks takes 10 seconds that usually takes 2 seconds.


texts: Getting null block_st.text when querying for blocks

texts:
We are using the blocking query from this article which queries sys.dm_tran_locks.

http://simplesqlserver.com/2013/05/21/blocking-capturing-and-monitoring/

The issue we've ran into is that often block_st.text will be null.  We are trying to find out the reasons that this could occur.


texts: How to script data in deterministic order

texts:
When I script data in my database in SSMS Tasks->Generate Scripts and then select data-only it turns out that the insert statements are generated in different order each time when even a smallest change occurs. This makes it impossible for the developers to run a diff program with the previous scripted data and see the data changes: all the tables every time are reshuffled around even if no rows were inserted/deleted so that a diff program can't really show the few data changes in coherent manner.

Is there a way to script data in a database so that they return roughly the same order if no major data additions/deletions that might affect the order happen?


texts: Updating an existing SQL Database with thousands of records

texts:
I am having some trouble thinking of how to update an existing table. There are currently 1500 items in it and the structure of the table is like this:
Old:

ID
Manager ID
Manager Name
Director
Regional Director
State Manager
Head Manager

22
"tom123"
"Tom Hawkins"
"Jerry Lewis"
"Mary Brauhm"
"Anna Michaelson"
"Jack Edwards"

New:

ID
Manager ID
Manager Name
Director
Regional Director
State Manager
Head Manager

22
"tom123"
"Tom Hawkins"
"Dennis Martin"
"Mary Brauhm"
"Nicholas Andrew"
"Jack Edwards"

Say a state manager has left the company and now all items need to be updated. But while we're at it, we also want to correct and update the managers who are no longer with the company.
I know that we could update where stateDirector = "Anna Michaelson", but what if not all people formerly under Anna are assigned under the New Director? We have all these details in a seperate(current) file(excel, csv).
I know the structure of the table itself could be improved (also, been trying to think of this improvement), but what is the correct approach to updating all these if multiple IDs (employees) can exist under a manager.


texts:
Scenario: In production we have the same functional DB at different physical sites. The column order on a table don't match each other so I am writing a script to amend this (a 3rd party application requirement).

Action: To do this I copied the table to a test server, made the changes required in the GUI and then used the save to script functionality. The generated script essentially creates a new table with the proper order, copies the data into there, deletes the original table and then renames the new table to the old/proper name.

Query: The generated table definition includes in part column definitions such as:

STAGE3 float(53) NULL

Using the more common Script Table as> Create to new query window the same column is generated as:

[STAGE3] [float] NULL

1) Why is there a difference?

2) Does it matter?

Environment:
Server SQL2000
SSMS 2008, Script for server version set to SQL Server 2000
Production destinations SQL2000, SQL2008, SQL2008R2


texts:
I have a table that I want to convert to memory optimized.
I have 160 million rows in my table. According to this topic my table will need:

(24B(header size) + 8B(IndexPointer) + 30B(dataRowSize)) * 160mln = 9460.45 MB of memory to store the table and 4B*160mln = 610.35 MB memory to store the index.
Total memory need is about 10GB.

I gave 16,000MB memory to my SQL Server, and I think this might be enough, but when I try to populate my table with data I am getting "out of memory"

I use the following script to change my disk based table to memory-optimized:

USE [master]
GO

ALTER DATABASE [BGD] ADD FILEGROUP [memory_optimized_filegroup_0] CONTAINS MEMORY_OPTIMIZED_DATA
GO

ALTER DATABASE [BGD] ADD FILE ( NAME = N'memory_optimized_file_122569704', FILENAME = N'E:\Artash\LocalBGD\Data\memory_optimized_file_122569704' ) TO FILEGROUP [memory_optimized_filegroup_0]
GO

USE [BGD]
GO

EXEC dbo.sp_rename @objname = N'[dbo].[household_mem]', @newname = N'household_mem_old', @objtype = N'OBJECT'
GO

USE [BGD]
GO

SET ANSI_NULLS ON
GO

CREATE TABLE [dbo].[household_mem]
(
    [location2id] [SMALLINT] NULL,
    [location3id] [SMALLINT] NULL,
    [location4id] [SMALLINT] NULL,
    [location5id] [SMALLINT] NULL,
    [location6id] [INT] NULL,
    [location7id] [INT] NULL,
    [location8id] [INT] NULL,
    [location9id] [INT] NULL,
    [povertyscore] [SMALLINT] NULL,
    [householdid] [INT] IDENTITY(1,1) NOT NULL,

 CONSTRAINT [household_mem_primaryKey]  PRIMARY KEY NONCLUSTERED
(
    [householdid] ASC
)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )

GO

SET IDENTITY_INSERT [BGD].[dbo].[household_mem] ON

GO

INSERT INTO [BGD].[dbo].[household_mem] ([location2id], [location3id], [location4id], [location5id], [location6id], [location7id], [location8id], [location9id], [povertyscore], [householdid]) SELECT [location2id], [location3id], [location4id], [location5id], [location6id], [location7id], [location8id], [location9id], [povertyscore], [householdid] FROM [BGD].[dbo].[household_mem_old]

GO

SET IDENTITY_INSERT [BGD].[dbo].[household_mem] OFF

GO

Caution: Changing any part of an object name could break scripts and stored procedures.
The statement has been terminated.
Msg 701, Level 17, State 109, Line 47
There is insufficient system memory in resource pool 'default' to run this query.

I have also tried to add a resource pool for my database but still get the same error.


texts: Problems on filegroup size -- SQL Server 2005

texts:
consider FG_january_2011 file group contains 100000 records. and FG_january_2012 file group contains 2000 records.

update Table_Name set date=dateadd(yyyy,+1,date) where Year(date)=2011

if i execute this query, what will happen.?? my thought is FG_january_2011 file group 100000 records will move to FG_january_2012 file group. and records too incremented as 102000.

Is it right things?? else what???


texts: System DSN for SQL Server to connect through ODBC with for a specific port

texts:
Earlier we were using default port number on the SQL Server and import of registry was working fine when configuring the clients to connect through ODBC. Recently we moved to new server where the SQL Server port is not the default one. We have around 300 client where this change needs to be made. Due to this we are looking for the best way to reconfigure the SYSTEM DSN's. The registry file content is as below,

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\ODBC Data Sources]
"SQLtest"="SQL Server"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\SQLtest]
"Driver"="C:\\WINDOWS\\system32\\SQLSRV32.dll"
"Description"="SQLtest"
"Server"="BLR002\\MAINT"
"Database"="Maint"
"LastUser"="sa"

Please let me know how to include the specific port number as well into the registry or what is the alternative method that can be used.


texts: Dynamic SQL is not allowed error for creating function in MySQL

texts:
According to this question and answer of Angelo I create a MySQL function as below:

DELIMITER $$
CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT, reqdTable VARCHAR(50)) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
            DECLARE _id INT;
            DECLARE _parent INT;
            DECLARE _next INT;
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;
            SET _parent = @id;
            SET _id = -1;
            SET @table_name = reqdTable;
            IF @id IS NULL THEN
                RETURN NULL;
            END IF;
            LOOP

                SET @sql_text = concat('SELECT MIN(id) INTO @id FROM ', @table_name,' WHERE parent_id = _parent AND id > _id;');
                PREPARE stmt FROM @sql_text;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;

                IF @id IS NOT NULL OR _parent = @start_with THEN
                  SET @level = @level + 1;
                  RETURN @id;
                END IF;
                SET @level := @level - 1;

                SET @sql_text = concat('SELECT id, parent_id INTO _id, _parent FROM ', @table_name,' WHERE id = _parent;');
                PREPARE stmt FROM @sql_text;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;

            END LOOP;
END

But It has following Error:

 #1336 - Dynamic SQL is not allowed in stored function or trigger

What should I do?


texts:
I have latest version of sp_blitzindex.  Would like to store results in table. Runs no output, anyone doing this already?

Running with parameters below:

EXEC dbo.sp_BlitzIndex @databasename = 'ngprod',
    @OutputDatabaseName = 'dbaadmin',
    @OutputSchemaName = 'dbo',
    @OutputTableName ='BlitzIndexStats'

Table structure is below:

USE [DBAAdmin]
GO

/****** Object:  Table [dbo].[BlitzIndexStats]    Script Date: 6/7/2017 9:08:21 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BlitzIndexStats](
    [Priority] [tinyint] NULL,
    [Finding] [varchar](255) NULL,
    [DatabaseName] [nvarchar](128) NULL,
    [Details] [varchar](2000) NULL,
    [Definition] [varchar](4000) NULL,
    [SecretColumns] [varchar](512) NULL,
    [Usage] [varchar](255) NULL,
    [Size] [varchar](255) NULL,
    [CaptureDate] [datetime] NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[BlitzIndexStats] ADD  DEFAULT (sysdatetime()) FOR [CaptureDate]
GO


texts: New SQL Server with slow Index Spool (Eager Spool) and old server used index

texts:
So yesterday I posted this question regarding that we have upgraded from SQL Server 2008 to SQL Server 2017 and that we excperience some performance problems.

I got the answer to try updating statistics which worked for one database, but not the others. Should I set compability level from 2008 to 2017?  At the current time, compatibility level has been left at 100 (SQL Server 2008).

Old server was a physical server, the new server is a VM (Hyper-V).

After the upgrade the execution plan looked different from the old one
and it seems to use Index Spool (Eager Spool) instead of using index. Which means that execution time went from 3 seconds on the old server to 1 minute 50 seconds on the new server.

The only thing I notice that is different from the database that does not have Eager Spool with same query is that the table rows are less, it's 13.000.000 rows on the non-working vs 70.000 rows on the working with correct execution plan.

The plans below are anonymized but I hope there's enough information.

Here is the plan with Eager Spool vs The other databas with Index

I have tried

Updating statistics
Changing cost threshold for parallelism and max degree of parallelism accordingly.
Double check indexes if they're as before.
I've looked at READ_COMMITTED_SNAPSHOT.
I've checked the NUMA configuration.

Another problem is that wait types are high

LCK_M_IS,
CXPACKET,
LCK_M_X,
ASYNC_IO_COMPLETION,
LCK_M_IX,
PAGELATCH_EX,
BACKBUFFER,
TRACEWRITE.

I am starting to run out of ideas.

Max memory is set to leave 4GB for OS, I noticed yesterday that power plan on the host machine was set to balanced but changed it to high perf, but still same problem.

On received advice to update statistics:
Seems to have worked on one of the databases, however I still have a problem on all the other databases.

The old server was a physical server with 8 cores and 32GB RAM.  The new server is a VM running on Hyper-V with 4 cores and 64GB Ram.


texts: store log of update,insert operation of postgresql user

texts:
I have to create postgresql user in postgresql 9.0 and give grant on my database xxx and whose all log of insert and update operation should be stored in one external log file so that i can have all information about his operations performed on my database.
How i can achieved this in my postgresql 9.0.
please suggest me any way.


texts:
I have a table with approx. 200 million rows (approx. 0.5 TB) and I want to drop it, but it is taking a really long time.

It's been running for 2 days now. I suspect the rollback function to be the reason for this.

Is there a way to avoid the rollback function, so that I can speed this process up? I just want to get rid of the table, because the data in there are of no value any more.


texts: How to tell what's failing at making backups?

texts:
I have an instance of SQL server that is regularly getting errors like this:

02/25/2013 23:22:38,spid138,Unknown,BackupVirtualDeviceFile::SendFileInfoBegin:
failure on backup device '{E0EA0FA7-D8FA-401B-8B4D-7C479F32A35D}21'.
Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).

and this:

02/25/2013 23:22:38,Backup,Unknown,BACKUP failed to complete the command BACKUP DATABASE master.
Check the backup application log for detailed messages.

According to our backup administrator, our Data Protector backups are A) running at a different time and B) running successfully. If that's the case, then I've got some rogue service attempting backups and failing.

Either way, I need figure out what's causing the errors. So, is there any way to track or log what entities are trying to make backups?


texts: How I can find out the foreign wrapper that a foreign table belongs to?

texts:
I have found that the following query lists all my foreign tables in my PostgreSQL database:

select * from information_schema."tables" where table_type='FOREIGN TABLE';

Now I want to find out the foreign wrapper connection that a database is referred into. In this answer the following query lists all my foreign wrappers:

SELECT fdw.fdwname AS "Name",
  pg_catalog.pg_get_userbyid(fdw.fdwowner) AS "Owner",
  fdw.fdwhandler::pg_catalog.regproc AS "Handler",
  fdw.fdwvalidator::pg_catalog.regproc AS "Validator"
FROM pg_catalog.pg_foreign_data_wrapper fdw
ORDER BY 1;

Now what I want is to find out how to combine this information in order to find out in which foreign wrapper a foreign table belongs into. Do you have any idea how to query that?


texts: Is it possible to limit the maximum disk usage for a user

texts: Is it possible to set a limit on the amount of physical disk space a user (or group of users) can use within a single database? For example is there some method of associating a schema with a filegroup and then setting a maximum size for the filegroup? The actual database would have other tables on it and would therefore want to have other filegroups which were unlimited.


texts: IBM DB2 9.7 and latest 10 version compatibility

texts: At some point projects need to move from IBM DB/2 9.7 to some 10 version. Are there any significant compatibility issues that should be taken into account? We ofcourse use tables in schemas, but then also triggers and some stored procedures.


texts: How do I connect my MS Access database to the LAN?

texts:
I wrote a database program in MS Access a couple of years ago, and it is used to keep track of some personal stuffs. I now want to expand this personal project and allow several computers to have access to this database. Assuming that all I am interested in connecting to are my home computers, i.e. all connected to the same router (same LAN), what are the basic steps I need to do this?

Also, I am very new to database, but I happened to know how to write VBA on MS Access. Is there any tutorial that can help on what I do?

Thanks!


texts: Looking for a simpler alternative to a recursive query

texts:
The actual query is more involved, but the problem I'm facing can be distilled to this:

A query to filter a rowset of monotonically increasing integers so that - in the final result set, row(n+1).value >= row(n).value + 5.

For the actual problem I need to solve, the rowset count is in the 1000s.

A few examples to clarify:

if rows are: 1,2,3,4,5 : then query should return: 1
if rows are: 1,5,7,10,11,12,13 : then query should return: 1,7,12
if rows are: 6,8,11,16,20,23: then query should return: 6,11,16,23
if rows are: 6,8,12,16,20,23: then query should return: 6,12,20

I've managed to get the required results with the following query, but it seems overly complicated. Uncomment the different "..with t(k).." to try them out.

I'm looking for any simplifications or alternative approaches to get the same results.

with recursive r(n, pri) as (
    with t(k) as (values (1),(2),(3),(4),(5))   -- the data we want to filter
    -- with t(k) as (values (1),(5),(7),(10),(11),(12),(13))
    -- with t(k) as (values (6),(8),(11),(16),(20),(23))
    -- with t(k) as (values (6),(8),(12),(16),(20),(23))
    select min(k), 1::bigint from t             -- bootstrap for recursive processing. 1 here represents rank().
    UNION
    select k, (rank() over(order by k)) rr      -- rank() is required just to filter out the rows we dont want from the final result set, and no other reason
    from r, t
    where t.k >= r.n+5 and r.pri = 1            -- capture the rows we want, AND unfortunately a bunch of rows we dont want
)
select n from r where pri = 1;


texts: Find an object cause to invalid package in Oracle

texts: When DB admin drops then create a table, some packages became invalid. I can easily recompile them. But how to know exactly what is the table(dependant object) that is the cause that  a package to became invalid?


texts: How can I (re-)create the Entity Relationship Diagram (ERD) of an existing hidden Oracle database?

texts:
More precisely, I wan't to re-create the database model / schematics / the Entity Relationship Diagram (ERD) showing all the links between the tables of this existing hidden Oracle Database.

In my job, I use a proprietary application interface (PAI) to manage users and their activities.
This PAI hides the underlying database and any connection to it.
This means that I cannot use standard SQL management interfaces to access this database and its data.

My job requires that I produce User reports.
To do so, I have to extract these reports by using a specific menu of this PAI.
In this menu I can only execute an existing SELECT statement or POST a new SELECT statement in one TextBox.
Unfortunately, the data extracted by the existing SELECT statements is not appropriate to the business requirements.
Furthermore the documents provided by the editor of this PAI contain an extremely light and partial series of schematics of the underlying database.

So, What would you suggest I do to have a better vision of all these data links between the database tables ?

Once I have a better vision of these links, I will be able to write SELECT statements that are extracting more appropriate data for my employer's business.

Extra information :
The PAI prohibits the execution of the following statements : insert, update, dll & dml, functions or procedures.

I can execute the following SELECT Statements :

  SELECT * FROM all_tables      --> results in 800 tables
  SELECT * FROM cols
  SELECT * from tabs
  SELECT * FROM all_views
  SELECT * FROM user_tables
  SELECT a.owner||'.'||a.table_name "Referenced Table",b.owner||'.'||b.table_name "Referenced by", b.constraint_name
  "Foreign Key" from all_constraints a, all_constraints b where
  b.constraint_type in('P','R') and a.constraint_name =
  b.r_constraint_name


texts:
I get the above error message when i run the following perl script from the cmd, that is supposed  to do some transaction in my database.

#!/usr/bin/perl.

use warnings;
use strict;

my ( $pid1, $pid2, $pid3, $i );    #Declaration of local variables
$| = 1;

if ( ($pid1 = fork()) && ($pid2 = fork()) && ($pid3 = fork()) ) {       #Starts child processes
   print( "I have to wait for my kids.\n" );        #The main process execute this code
   my $straggler = wait();              #The main process waits for all the children to finish
   print( "Finally $straggler finished, now I can go.\n" );
}
elsif ( $pid1 && $pid2 && defined($pid3)) {         #Is executed by the third child
   sleep( 1 );                      #Wait 1 second
   print( "Start moving money between account 1 - 2" );
   sleep(2);
   for ($i=0; $i < 1000; $i++) {            #Start transaction repeatedly
    system("sqlcmd -S DESKTOP-U368A1B  -Q tranKonto(1,2,100,1)"); #Moves 100 from account 1 to account 2
   }
   print( "End moving money between accounts trans 1 - 2 " );
   exit();
}
else {
   die( "Forking problems: " );
}

The transaction looks like this:

PROCEDURE [dbo].[tranKonto] @konto1 INT,  @konto2 INT, @belopp INT, @isoLevel INT
AS
BEGIN TRANSACTION tranKonto

    IF(@isoLevel=0)
        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    ELSE IF (@isoLevel = 1)
        SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    ELSE IF (@isoLevel=2)
        SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    ELSE IF(@isoLevel=3)
        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    BEGIN
        UPDATE Konto SET belopp=belopp-@belopp WHERE kontonr=@konto1
        UPDATE Konto SET belopp=belopp+@belopp WHERE kontonr=@konto2
        EXEC kontoLog_SP @konto1,  @konto2 , @belopp , @isoLevel;
    END

COMMIT TRANSACTION tranKonto
RETURN

I get the following error message when running the script:

  Msg 102, Level 15, State 1, Server DESKTOP-U368A1B, Line 1
  Incorrect syntax near '1'.

I would really appreciate if somebody could help me out. I have tried to google a solution but havn't had any luck.


texts: How can I get the last second of the last month as a default parameter value?

texts:
I am trying to get the last second of the last month as a default value for a parameter. I can get the last day of the last month from this query on social.msdn.microsoft.com

  last month last day expression: DateValue(DateAdd("D",-1,DateAdd("D",-(Day(Now)-1),Now)))

The about would return 12am of the last day of the month. Starting with that I made one change so that I can get the last second of the last month.

=DateValue(DateAdd("S",-1,DateAdd("D",-(Day(Now)-1),Now)))

That does not work though as the time is still set to midnight.

How do I get the last second of the last month as a datetime value?

This has no affect on underlying queries that deal with report data. This is purely about showing an end user the start and end dates in a report header. "9/1/2018 00:00:00 to 9/30/2018 11:59:59"   "9/1/2018 00:00:00 to 10/1/2018 00:00:00". The former example to my end users is clearer that the data is in the month where as seeing number for the next month (like in the second example) is ... unsettling


texts:
I have a SELECT with calculated columns and Im doing some filtering on it.

The issue Im having with ROI where Im calculating average for the date range, which is incorrect as ROI needs to be calculated from actual revenue and costs (ie. ROI = (revenue-costs/costs)*100 )

So the question is, how I can calculate the correct ROI?

SELECT
                 c.campaign_name,
                 c.traffic_source_name,
                 cvr.custom_variable,
                 SUM(cvr.visits) as visits,
                 SUM(cvr.clicks) as clicks,
                 AVG(cvr.ctr) as ctr,
                 SUM(cvr.conversions) as conversions,
                 SUM(cvr.cost) as cost,
                 SUM(cvr.revenue) as revenue,
                 SUM(cvr.profit) as profit,
                 ROUND(AVG(cvr.roi), 2) as roi,
                 AVG(cvr.ap) as ap
                 FROM custom_variable_report as cvr
                 INNER JOIN campaigns as c
                 ON c.campaign_id = cvr.campaign_id AND c.date = cvr.date
                 WHERE cvr.campaign_id = 'b881eb19-8a47-4a60-9373-b0981006733a'
                 AND (cvr.date BETWEEN '2017-03-03' AND '2017-03-09')
                 GROUP BY cvr.custom_variable
                 HAVING ABS(profit) > 2.51*1
                 AND roi < -25
                 ORDER BY cvr.profit ASC

In this case if I have 2 data points: -20% and 100% the average is: 40% which is incorrect as ROI needs to be reflected from the revenue and costs instead for each row.


texts:
Is it possible to restore SQL Transaction Logs to a MS SQL database every hour without having to restore the full backup first?

Can you set database to No Recovery, apply transaction Logs, then set to Recover. Do that every hour? is that possible?

Reason for question is that the vendor is sending a full database backup each morning and instead of the large backup being sent they rather send T-Logs throughout the day.


texts: Ideas for intro SQL presentation / training

texts: At my work, I've been asked to teach a short half-hour to an hour course about "select and joins". I'm also thinking about discussing unions, sub queries and temp tables in this course. Are there any core concepts that relate to the topic of "select and joins" that I could include to expand the length of said course?


texts: SQL Server stored procedure working when run manually, not running from SQL Server Agent

texts:
I have a procedure that runs fine using the execute command in SSMS, however putting the same command in a job gives the following error.

  line 9, character 9, unexpected end of input

The code takes a very long XML string in UTF-8 encoding and puts it into a single nvarchar(max) cell. Then puts this string into a XML cell in a different table, allowing me to query the individual parts of the XML code using the nodes function. I cannot put the data directly into a nvarchar cell due to encoding differences.

I can't reproduce the string here as it is very very long.

I'm just looking for ideas really as to where it might be going wrong.

Here is what I know so far:

The procedure runs without issue when executed manually
I have checked permission issues, and that doesn't seem to be the problem. The agent runs under my own account and I am a sysadmin on the database
I split the procedure into seperate parts to locate exactly where the problem is occuring. Once again the seperate procedures run fine when executed manually but an error occurs when run through SQL Server agent.
When the query is run seperately through SQL Server Agent it gives a slightly different error. This leads me to believe it is an encoding issue. However I am getting the XML from a webpage and I can't change the encoding on the webpage.

  line 1, character 38, unable to switch the encoding

I know this is a long shot since you can't replicate the issue but if anyone could give an idea as to where to start looking for an answer, it would be greatly appreciated.

EDIT 1: The code for obtaining the XML is as follows:

Select @url = 'http://....'

EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win, 'Open',NULL,'GET',@url,'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win,'Send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

INSERT #TextData(HTML)
EXEC @hr=sp_OAGetProperty @win,'ResponseText'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OADestroy @win
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win


texts: If a query reads more pages than the InnoDB buffer can hold, are all the previous pages evicted?

texts: If I have some (hot) pages in the InnoDB buffer, and I perform a query which reads an amount of data larger than the buffer itself, will the hot pages be necessarily evicted?


texts:
Suppose I have the following two tables in my Oracle database:

create table a (num number);
create table b (val varchar2(30);

and in my PL/SQL program I have declared a nested collection as follows:

declare
        -- type for inner collection; shadows table b
        type inner_tab_t is table of varchar2(30);

        -- record type for outer table
        type outer_tab_rec is record
        (
            num  number,
            vals inner_tab_t
        );

        -- type for outer table
        type outer_tab_t is table of outer_tab_rec;

        -- actual inner_tab_t
        inner_tab inner_tab_t;

        -- actual outer_tab_t
        outer_tab outer_tab_t;

I am wondering whether it is possible to use a FORALL statement to insert the data from outer_tab into both tables a and b?

In other words, given the following assignments:

begin
        inner_tab := inner_tab_t('one', 'two', 'three');

        outer_tab := outer_tab_t();

        outer_tab.extend;

        outer_tab(outer_tab.last).num := 1;
        outer_tab(outer_tab.last).vals := inner_tab;

I'd like to do something like this (obviously doesn't work):

 forall ind in outer_tab.first .. outer_tab.last
            insert into a (num)
                values(outer_tab(ind).num)
                forall ind2 in outer_tab(ind).vals.first .. outer_tab(ind).vals.last
                    insert into b (val)
                        values (outer_tab(ind).vals(ind2);

Is there a convenient way of handling such a situation?


texts: Versioning: Is this technology used in DBMS other than spatial?

texts:
ESRI's spatial database management systems, called geodatabases (more), use a technology called versioning.

  A version represents a snapshot in time of the entire geodatabase and
  contains all the datasets in the geodatabase.

  Versions are not separate copies of the geodatabase. Instead, versions
  and the transactions that take place within them are tracked in system
  tables. This isolates a user's work across multiple edit sessions,
  allowing users to edit without locking features in the production
  version or immediately impacting other users and without having to
  make copies of the data.

http://help.arcgis.com/en/geodatabase/10.0/sdk/arcsde/concepts/versioning/basicprinciples/state.htm

  When you register a dataset (a feature class, feature dataset, or
  table) as versioned, two delta tables are created: the A (or adds)
  table, which records insertions and updates, and the D (or deletes)
  table, which stores deletions. Each time you update or delete a record
  in the dataset, rows are added to one or both of these tables. A
  versioned dataset, therefore, consists of the original table (referred
  to as the base or business table) plus any changes in the delta
  tables. The geodatabase keeps track of which version you were
  connected to when you made the edits that populated the delta tables.
  When you query or display a dataset in a version, ArcGIS assembles the
  relevant rows from the original table and the delta tables to present
  a seamless view of the data for that version.

To be honest, I find the documentation to be rather vague; it doesn't tell me much about how the technology actually works, or what part of traditional database theory it is based on.

I don't imagine many DBA SE community members would have experience with ESRI's versioning technology. So I won't ask something like 'how does it work?'.

Instead, I'm wondering, are there any technologies in the non-spatial database world that are similar to ESRI versioning?


texts: How to split a string on every 2nd delimiter?

texts:
I have curves consisting of X/Y points (an array?) stored in a string with the same delimiter. There is 1 curve per row. Below is a simple example:

0,0,1,1,2,1.9,2.9,2.8,3.6,3.5

I want to extract each curve into a #table so I can edit them and ultimately put them back.

X,Y
0,0
1,1
2,1.9
2.9,2.8
3.6,3.5

A curve could have anywhere from a dozen to hundreds of values, and the points may not be ordered.

What is the cleanest/simplest way of doing this?


texts: Once a foreign key is set to Null after deleting the related parent record, can you then amend the foreign key to a new value?

texts: My question is say you ran a daycare and had a table for the children and another for their related school (with the FK in the child table), if the school of some of the children, for some reason, closed down, and you had the On Delete option set to SET NULL, would this make sense and also, could you then update the related foreign key to their new school or how would you address this?


texts: Optimizing UDF - Is Using DUAL Expensive

texts:
I am new to Oracle technology (primarily versed in SQL Server) and I am having trouble with the performance of a UDF.

The purpose of the UDF is to take some inputs for an "event", and look up an "expected time for event b to occur". The function returns a single DATE value.

The first thing that occurs is a SELECT ... INTO [variable] FROM DUAL; a variable. Finally a lookup occurs in a reference table (only ~30k rows), and that is stored in the return variable.

It took 8 hours to process on 800k rows. I can select all of the columns without using the function in less than 40 seconds.

My main question is: Is the use of DUAL costing me?

We are on an Oracle 11g RAC.


texts: Galera - single database backup & restore

texts:
I have a MariaDB Galera cluster. One of its nodes is dedicated for the backups (pc.weight=0 and the MaxScale SQL proxy doesn't know it exists).

I'm using mariabackup --backup --galera-info --target-dir=/path/to/my/dir to do the daily backups. So this backs up everything.

I want to be able to do 2 kinds of restore:

full restore in case of disaster (e.g. data center explosion)
single database restore in case of data loss (e.g. human error/hacking)

The case 1 is easy, I just take the backup I have and use mariabackup --prepareand mariabackup --copy-back on the new freshly installed Galera nodes.

But how can I achieve case 2?

I know I could do a mariabackup --prepare and then for each table of the database I want to restore:

discard the table space of table X
copy back the file(s) of table X from my backup
import the table space

The problem is that this process is too low-level for Galera/wsrep to notice it and the changes are of course not replicated to the other nodes. So I would need to repeat this process on all the nodes.

I could also:

take a logical backup (mysqldump)
drop the database on one of the Galera nodes
re-create the database
import the dump I just did

This would be replicated to all the nodes. But it's highly inefficient and I would need to setup a temporary MariaDB server to run the mysqldump on the backed up data.

There must be a better way, right? How are you doing it?

Your answers are much appreciated!


texts: Which Azure service for use as data warehouse/analysis store?

texts:
We currently have a database running in SQL Server 2012 Standard.

We need to export data periodically (a "data dump") of a particular set of data to "a location to be decided" where the client's own analytical software (QlikView) can read the data, and create reports.

If we opt for Azure to hold this data, what is the appropriate Azure service to use please?  Also, should the export be arranged purely in SQL (usually we'd write a quick .Net EXE to pull out and save data exports)?

Thanks.


texts: Check if there are similar rows in a table

texts:
As a Rookie in SQL I have a problem creating a query.

Here is the problem : I would like to check on a table if there are lines that share the same DATES.

For example I would like to know if in TABLE A, there are cases like this :

ID | LIB | START_DATE | END_DATE
01 | A1  | 01-01-2016 | 05-03-2016
02 | A2  | 03-03-2016 | 05-06-2016

Those lines are SURVEYS and I would like to know if an admin created a survey that is starting during an other survey. I did some searches on AUTO JOIN but this wasn't really what I was looking for.

My case is a bit more complicated but I will find out myself if you explain  how to proceed on this simple case.


texts: What free database software support sequences

texts: I have a requirement that a database must support sequences. I know oracle and PostgreSQL support it. Are there any other free database software that support database sequences? it would be great if it had a .NET driver too


texts: Can a rollback/commit occur, independent, for each iteration inside a cursor?

texts:
We have this ecosystem of accounting solutions and, basically, it got two tables:

dbo.AccountFluxRecord
(
   Id,
   TotalCredits,
   TotalDebits,
   CreditMinusDebit,
   Application (the application used to insert the record)
)

dbo.AccountFluxRecordDetail
(
   Id,
   AccountFluxId,       (FK to above table)
   Type,                (Credit or Debit)
   Value,
   CreditBankAccountId, (nullable FK)
   DebtBankAccountId    (nullable FK)
)

We have a few applications using it and all use a stored procedure to insert new records.

SP_InsertAccountFluxRecord

We pass the list os details to the SP as a custom user table type.
It makes lots of validations to ensure all records have at least one credit and one debit and the sum of credits minus debts to be zero.
It also validates if BankAccountId is valid for each kind of operation.

It's working flawlessly, for a couple of years now, in hundreds of customers, inserting many millions of records each week.
We host the database for most of our customers but some of them insist their data must be hosted in house.
We are Ok with that since we can get a open channel for maintenance.

Today we got an issue with one of those customers self-hosting their database.

We have found a dozen "bad" records.
Those records are missing the debt detail, that is impossible since that particular application creates an AccountFluxRecord
from a pair of debit/credit details, one is the mirror of the other.
It cannot even accept a user input without a valid pair of bank accounts but we found the DebtBankAccountId field to be null in those bad records.

We keep a log of all operations, the log reflects the bad records are inserted as they are, there are no deletes or updates over them.
Obs: We do use snapshot transaction mode to avoid some deadlock problems we got in the past.

TLDR
I got a dozen invalid records, inserted by an SP made to block invalid records to be inserted at all, as if all application and Stored Procedure validation where bypassed.
Below there's a snippet showing how validations are handled inside the SP.

IF (@SumDetailDebit <> @SumDetailCredit)
BEGIN
    set @Mesage = @ErrorPrefix + N'TOTAL DEBIT IS DIFFERENT FROM CREDIT!';
    THROW 50000, @Mesage ,1
END

Question: Inside an SP there is a cursor inserting a detail record for each iteration, after making some validations.
Can one insert be commited and other be backrolled even if we are not using transaction control inside the loop?


texts: Running a task without waiting for the results in Postgres 11 on RDS

texts:
I've got a question about spawning stand-alone threads on Postgres. Here's what I'd ideally like:

Have a postgres-side solution to automatically launch tasks on a schedule in their own threads.

Something like the (imaginary) example below:

   CALL_IN_NEW_THREAD do_db_maintenance();

I understand that this doesn't exist, but I don't know what is possible or preferable. I'm in design phase, so my question isn't about SQL syntax, etc. I'm trying to figure out if there is a way to "detach" a query from the original process so that my connection can close down without waiting for the task to finish. Or, even better, if there is a way to auto-start routine tasks on Postgres itself without a client kicking it off or responding to a NOTIFY.

First up, the setup:

We've got a number of routine tasks to run periodically. Imagine refreshing materialized views, integrating posted changes, DB admin chores, etc.
The results of a query/task are written back to the database, so there's no need for the client to collect them.
Imagine that we've got a task_definition table that makes it simple to figure out which tasks are pending and should be run.
Our deployment environment is Postgres 11.x on RDS, so pg_cron is not available. In fact, the command-line isn't available.
We'll be on Postgres 11.x until RDS certifies PG 12. So, I'm looking for a PG-11 based solution for the foreseeable future, likely the better part of the next year.

What I am hoping to do goes like this:

Have an external program log into Postgres, scan the task_definition table, find the jobs that should be started, and start them.
"Start them" in distinct threads.
And then log out.

So, the client just kicks off the work and gets out. Is this a possibility in Postgres without a shared library or anything else that's impossible on RDS?

If it's not possible, I can always have my external program log in, find pending tasks, and launch them in distinct threads from the client. (Be kind and assume that I've handled the locking correctly and am not double-launching any of the tasks.) It seems a bit silly to have the client connection hanging around for no real purpose, but it's not the end of the world. On the upside, it might make it easier to notice if I screw something up and have a task that takes too long.

I've looked around at various solutions for scheduled tasks in Postgres, it seems to be a common requirement. A few notes on solutions I've seen:

Triggers
Sure, that makes sense...they work as a kind of signal. But then you've tied up the process that's running the trigger until the task is done. (There doesn't seem to be a way to detach a procedure call.) So, this doesn't feel like a great help. I guess I could have a utility table where the trigger serves no other purpose than to run a method, but I don't see what that offers over just running the method in a query directly.

pg_cron
Not available on RDS.

PgAgent
Not available on RDS.

cron
Not available on RDS. But you could set up an EC2 instance, etc. to run cron.

Your language here
Use your language of choice to connect, run, wait, repeat. I'm guessing that this is what I'll end up having to do.

NOTIFY/LISTEN
Cool! But it still requires "Your language here" to do the listening, so I don't think it changes anything for me.

dblink
This sounds promising, but I'm not sure it's what I'm after. dblink_send_query supports one async query per connection, and it sounds like the connection has to be sustained. It also doesn't appear from the docs that you can dblink back to the same database. We don't have a second DB running now, but will at some point...so dblink is something we would consider.


texts:
I am using Mongodb 3.6.3 on Centos 7.
I am running the below command:
sudo mongod --storageEngine wiredTiger --dbpath /data --bind_ip_all  --logpath /var/log/mongodb/mongod.log --fork
and everything will work just fine. Except, binding all is not secure enough.
I used to received a logging error when I tried --bind_ip 127.0.0.1,privateIP but discovered that the error would disappear when I did bind_ip [127.0.0.1,privateIP].
However, when I tried to connect to mongo locally it would fail and through a my site, it would sit there until it timed out. I checked sudo netstat -plnt | egrep mongodand then nothing showed up, which although it successfully forked the mongod process, it did not bind to any of the specified ips.
For reference, when I tried the same command on on the --bind_ip_all version it returned tcp        0      0 0.0.0.0:27017           0.0.0.0:*               LISTEN      1645/mongod.
My question is, why did --bind_ip ips fail to bind to the specified ips?
Side Note - when binding ips, should I bind my elastic ip or my private one?


texts: Does Azure SQL Data Sync support MySQL or SQLExpress?

texts:
I did see that it supports SQL Server (2005 and later) but does it support syncing between a SQLExpress server or MySQL server and an Azure Server?

I'm trying to use it with a SQLExpress server but it isn't connecting. Is this because it's not supported?

The error I'm getting when trying to connect with SQL Data Sync 2.0 on my server computer is: Unable to connect to the Database. Please check if service account has connectivity, credentials, and permissions to connect.

I've opened up port 1433 in the firewall and can connect to the server via SSMS. However, the SQL Data Sync 2.0 application won't connect.

I'll be trying this with a MySQL server later as well so I just wanted to know if it's supported or not as well.


texts: How should I structure my time based index?

texts:
So I have this table in SQL Server 2008.  Let's just say it's called TimeSheets.

Now, that table has two columns I am concerned with.

TimeSheetDate and UserCustomerKey.

TimeSheetDate is a DateTime column and UserCustomerKey is a GUID.

UserCustomerKey is the primary ID of a join table for Users and Customers.

Typically, the primary application is going to query TimeSheets by the TimeSheetDate and UserCustomerKey.  And since we're dealing with time sheets, we usually need the most recent time sheets for reports.

So I created an index like:

TimeSheetDate (DESC)
UserCustomerKey (ASC)

Since we normally care about descending time sheet searches (most recent time sheets from today), this index makes sense.

However, there may be applications that JUST search by TimeSheetDate and not care about the UserCustomerKey.

1)  So, do I need to also create a single index for TimeSheetDate or will the above index also include it?

2)  Do I need to create another index that has UserCustomerKey BEFORE TimeSheetDate or does it matter?

I don't want to waste resources if I don't have to.

Thanks.


texts: Blocking task report is showing false positive

texts:
Here is the code I am using to send email alerts. It seems to send more false +ves, can anyone guide me how to resolve this ?

DECLARE @Waiting INT;
DECLARE @Message NVARCHAR(MAX);

SELECT  @Waiting = COUNT( s.session_id )
FROM    sys.dm_exec_sessions s
LEFT OUTER JOIN sys.dm_exec_connections c ON ( s.session_id = c.session_id )
LEFT OUTER JOIN sys.dm_exec_requests r ON ( s.session_id = r.session_id )
LEFT OUTER JOIN sys.dm_os_tasks t ON ( r.session_id = t.session_id AND  r.request_id = t.request_id )
LEFT OUTER JOIN (
                    -- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
                    -- waiting for several different threads.  This will cause that thread to show up in multiple rows
                    -- in our grid, which we don't want.  Use ROW_NUMBER to select the longest wait for each thread,
                    -- and use it as representative of the other wait relationships this thread is involved in.
                    SELECT  *
                            ,ROW_NUMBER() OVER ( PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC ) AS row_num
                    FROM    sys.dm_os_waiting_tasks ) w ON ( t.task_address = w.waiting_task_address )
                                                            AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON ( r.session_id = r2.blocking_session_id )
WHERE   ISNULL( t.task_state, N'' ) <> ''
        AND ISNULL( DB_NAME( r.database_id ), N'' ) = 'MyDB'
        AND ISNULL( w.wait_duration_ms, 0 ) > CASE
                                                    WHEN FORMAT( GETDATE(), 'HH:mm' ) >= FORMAT( CAST('07:00AM' AS TIME), N'hh\.mm' )
                                                        AND  FORMAT( GETDATE(), 'HH:mm' ) < FORMAT( CAST('05:30PM' AS TIME), N'hh\.mm' ) THEN 10000
                                                    ELSE 20000
                                                END ---  this will change the wait time to 20s during offpeak hours.
        AND s.session_id <> ISNULL( w.blocking_session_id, 0 )
        AND is_user_process = 1;    ---- added on 2019-04-02 @8.40AM to neglect the parallel queries.threads blocking themselves

IF ( @Waiting > 30 )
    --send email


texts: T-SQL query to find Primary Management Server for SCOM Agents

texts:
I am trying to replicate the function of a script which uses PowerShell to get info about SCOM agents. I can get everything I am trying to do for the moment except get the Primary Management Server for each agent, so the info that is returned by the PrimaryManagementServerName property when you run the Get-SCOMAgent cmdlet.

I've searched the tables/views etc in the OperationsManager database but have hit a wall, has anyone else tried to do this via T-SQL and been successful? I also tried running a trace on my login while using the SCOM console and selecting the Managed Agents view, but the console isn't passing through my credentials.