texts: Date alignment and pair matching extraction best done with TSQL or C#?

texts:
For starting with: my title sucks, so help me figure out a new one?

I can't post all the SQL here (over 30k characters for the lot of it), so I stuck it on pastebin.com

The problem:

I get an XML file that I scrape some records from, and I need to extract some data from the records and build another table off it. The records are for an event going off and coming on, and I've included sample data in the pastebin for recreating. Without seeing the data it's kind of hard to explain. I've given all the data I have from my sample import which should be sufficient to build the app off of, but I'm not getting any more information than what is shown in the data.

I'll give you a moment to glance at the data so this makes sense.

So what I need to do is this: For each "off" event, I need to match it to the next "on" event, and I need to have two tables at the end, one table for "historical events" and one table for "current events". However, if I can just get "historical events" built correctly I can figure out how to get "current events" from that.

Business rules:

If two or more "off" events are collected before an "on" event, keep the oldest "off" event. If two or more "on" events are collected before an "off" event, keep the newest "on" event. If there is a complete pair, put them in the historical table. If there is an "off" event and not an "on" event, put it in the current table (so if I wanted to keep inserting/deleting from this table, that's fine too). If there is an "off" event already in the current table I can take it and move it to the historical table for an "on" event being read in (this will need to be implemented later, but if I can get the pairings matched initially I'll be able to go forward for now.

I think that's pretty much it for the logic. My thoughts are to either figure out how to do this in SQL or push it out to an app written in C# and do it with some temporary lists in C# and build what I need using for...next logic. This may be infinitely easier in C#, but I have a feeling SQL can do this job just as easily as C# can, so I needed some help from the dba gurus.

The queries I have already don't work, but that's where I got to with my starting before going home on Friday, and I've been mulling it since then, and building a sample problem that I can post online (and that whole life thing too ya know). The data is live data and exact, except the ID's being anonymized and the text fields changed to something simple to work with.

Here's a spreadsheet showing roughly how I want the data to look at the end and the way it looks now. There is the current data (with a spacer row between each ID for clarity), the data that would be in the historical table (aligned with the ID of the original data for understanding) and the current table (again aligned). I hope this can help clarify the business rules.
https://spreadsheets.google.com/ccc?key=0AuvCdeHuVU5ddHRCNkpuWHBUREpRajlmLU5VX2xsWnc&hl=en&authkey=COq7y50H

So the complete SQL including tabledefs and current (very not-right) queries is on pastebin http://pastebin.com/k2f2CLnQ


texts: Firebird database recovery and protection

texts: I'm sure many companies are running critical Firebird database servers in production 24/7. Other than regular backups, what backup/recovery/protection techniques are available? I am also interested in backup policies for high availability databases.


texts: Giving EXEC (@Variable) a Column name and Concatenation

texts:
I'm trying to writing a stored procedure that will check for orphan data before our customers attempt an upgrade, as orphaned records can cause issues. This is what I have thus far;

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'DetectOrphanDataBeforeUpgrade')
DROP PROCEDURE [dbo].[DetectOrphanDataBeforeUpgrade]
GO

CREATE PROCEDURE [dbo].[DetectOrphanDataBeforeUpgrade]

@TenantId   INT = NULL

AS
BEGIN

DECLARE @OrphanAuditItems NVARCHAR(MAX)
DECLARE @OrphanAuditAnswers NVARCHAR(MAX)
DECLARE @OrphanAuditQuestion NVARCHAR(MAX)

------------------------------------------------------------------------------------------------
/* Throw an error if the TenantId is NULL or Invalid.               */
------------------------------------------------------------------------------------------------
IF @TenantId IS NULL OR @TenantId NOT IN (SELECT Tenants FROM [Application].[dbo].[TenantIdNumber])

BEGIN
    THROW 51000, '@TenantId is invalid because it is NULL or does not exist.', 1;
END

------------------------------------------------------------------------------------------------
/* Checks for Orphan records related to the Audits table                */
------------------------------------------------------------------------------------------------
SET @OrphanAuditItems = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems]
                                                    WHERE FK_Audit NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

SET @OrphanAuditAnswers = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditAnswers]
                                                        WHERE AuditItemId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems])'

SET @OrphanAuditQuestion = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditQuestion]
                                                        WHERE AuditId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

EXEC (@OrphanAuditItems)
EXEC(@OrphanAuditAnswers)
EXEC(@OrphanAuditQuestion)

END;

At the moment, this script is working, however the output is;

I want to return the results on a single line, and have each column with their own name, for example: @OrphanAuditItems result shows as column name: OrphanAuditItems, and so on.

How's the best way to achieve this?

Thanks,
Tom


texts:
I've been gathering information about how addresses in different countries are written. While doing that I've also been working on a database design to hold this world wide information most probably used for making deliveries.

I started off with GeoNames.org data and came up with a first design (the higher table has the lower table as a child) :

Continent
  Country
    City
      Address

With Currency (as a FK) and Language (with a many-to-many table) linked to the Country table. Also a many-to-many Capital table linking a Country and a City.

Soon I realized the US for example uses the state's code in their address. So an extra Province table was inserted between Country and City:

Continent
  Country
    Province
      City
        Address

Now I stumbled upon United Nations Code for Trade and Transport Locations (UN/LOCODE). This organization provides a list with all the cities in each country with some detailed information. This data fits pretty good with my 2nd design. I only renamed the Province table to SubDivision which is in the US the state, in Belgium the province and in France the department. This is very similar to the ISO 3166-2 codes.

Continent
  Country
    SubDevision
      City
        Address

To me, this looks like a good detailed database structure that won't need to be altered in the future. Like I said in the beginning, Currency is a FK in the Country table easy editable or removable. Language has a many-to-many relationship with Country and is also easy editable or removable. My biggest concern is that ISO also has more than 1 subdivision level fear certain countries. For Belgium for example there they have the provinces (provided in the UNLOCODE code list), but ISO (ISO 3166-2:BE) also has 3 regions that are higher than the provinces in Belgium and are pretty much in-between the Country and the Province.

Should additional (optional) tables be added? How this would be implemented is a question to me though.
Another database design I can think off is all loose tables, no FK's, and 1 table linking all required tables together with following columns: AddressID, CityID, SubDiv1ID, SubDiv2ID, ... , CountryID, ContinentID. Not sure if this is a good approach.
Add a different table per Country based on their amount of SubDivisions. This means Continent and Country and Address stay unchanged. City will be Country specific with the US for example having a USState (SubDiv 1) -> City and Belgium BERegion (SubDiv 1) -> BEProvince (SubDiv 2) -> City.
I did a little more searching and found this answer.

  Consider creating lookup tables for city, state and country entities. The city/state/country columns of the address table then consist of FKs pointing to these lookup tables. This allows you to guarantee consistent spellings across all addresses and gives you a place to store additional metadata (e.g., city population) if needed in the future.


texts: Does an index continue to be used after it is dropped (Database Polling investigation)

texts:
The folly of developers who use database polling is shown below.

I have identified a query below which did not have an index in place on a particular column

In addition to polling the database every second with the below query, the developer did not utilise indexes on the CloseDateTime, so a scan of about 50,000 rows was hitting the database constantly. (Keep in mind this was in Development, so the dataset is smaller)

After giving the developer an inkling of why we were experiencing high CPU utilisation on the DB, by showing them a query - they came back with miscellaneous excuses for the cause such as a virus, etc.

I then had to investigate this further, which involved correlating the CPU utilisation using Perfmon, using SentryOne Plan Explorer to look at the impact of the query closer, and then finally - watching what CPU utilisation did when I added a new index that appeared to be missing.

Output of SPBlitzFirst helped me identify the query.

I then investigated it further in Sentry One Plan Explorer. I noticed firstly that there was no Index on 'CloseDateTime' (in the where clause of the inner query)

I tested my hypothesis by creating an index on this column, and watched the CPU utilisation suddenly drop. I then tried to drop this newly created index, expecting to see CPU utilisation increase again, but it did not.

My question is, will the database continue to use this index even after we remove it?

  MemberId,
    Sum(RemovedAmount) as Amount,
    Sum(RemovedQty) as Qty,
    PromotionId,
    CheckNo,
    ProcessedDT
    from vm_config..HospitalityTransaction HT with (NOLOCK)
    where exists (Select * from vm_warehouse..TransHeader TH with (NOLOCK) where AssociatedCheckNo = HT.CheckNo and  CloseDateTime between HT.ProcessedDT -1 and HT.ProcessedDT + 1)
      Group By     MemberId, CheckNo, PromotionId, ProcessedDT


texts: Re-initializing a transactional replication wihch was initially synced from backup

texts:
I have a transactional replication which was initially synced from backup.   Now I need to add a new table which is really big so we have decided to backup and restore a fresh copy of the db to subscriber to re-intializing it.

My question is, in this scenario should I be dropping the subscription, backup restore and then re-add the subscription?  is that the correct way or is there any other way of going about it?

Thanks


texts:
I have the following tables:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `account_data` text COLLATE utf8_unicode_ci,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `twitter_username` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `crypted_password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password_salt` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `persistence_token` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `single_access_token` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `perishable_token` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `login_count` int(11) NOT NULL DEFAULT '0',
  `failed_login_count` int(11) NOT NULL DEFAULT '0',
  `last_request_at` datetime DEFAULT NULL,
  `current_login_at` datetime DEFAULT NULL,
  `last_login_at` datetime DEFAULT NULL,
  `current_login_ip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_login_ip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `is_admin` tinyint(1) DEFAULT '0',
  `referrer_id` int(11) DEFAULT NULL,
  `partner` tinyint(1) DEFAULT '0',
  `subscription_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT 'free',
  `workflow_state` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `persona_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `persona_index` (`persona_id`)
) ENGINE=InnoDB

and the table:

CREATE TABLE `user_actions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `action_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `module` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `data` text COLLATE utf8_unicode_ci,
  `timestamp` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id_index` (`user_id`),
  KEY `action_type_index` (`action_type`),
  KEY `user_action_type_index` (`user_id`,`action_type`),
  KEY `timestamp_index` (`timestamp`),
  KEY `user_id_timestamp_index` (`user_id`,`timestamp`)
) ENGINE=InnoDB

the problem is with the following query:

    SELECT user_actions.*, users.twitter_username, users.email FROM `user_actions`
INNER JOIN users ON (user_actions.user_id=users.id) ORDER BY timestamp DESC LIMIT 0, 30

here is the explain:

user_actions
The table was retrieved with this index: user_id_timestamp_index
You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.
Approximately 76 rows of this table were scanned.
users
This table was retrieved with a full table scan, which is often quite bad for performance, unless you only retrieve a few rows.
The table was retrieved with this index:
No index was used in this part of the query.
A temporary table was created to access this part of the query, which can cause poor performance. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.
MySQL had to do an extra pass to retrieve the rows in sorted order, which is a cause of poor performance but sometimes unavoidable.
You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.
Approximately 3445 rows of this table were scanned.

this query takes a long time to execute, any ideas how to improve?


texts: Shrink transaction log while transaction in progress

texts:
I have a several-GB import into my SQL Server 2005 (Standard Edition) database.

The import is separated into 4 parts, each part in its own transaction.

As you can guess, the transaction log files are quite big. The space on my disk is also limited, but should fit the data. While doing my import the free space on my disk is about 2 gb.

Does it work to shrink the files, while my transactions are in progress? Would it also have an effect?


texts: Incremental extract strategy using SSIS and CDC

texts:
I'm looking for a strategy for extracting data from a SQL Server database that is configured for change data capture (CDC) using SSIS.

Most of the documentation I have seen around using CDC and SSIS are simple scenarios where there is one source table configured with CDS, a conditional split to determine the inserts, updates and deletes and then those corresponding actions.

My current extract consists of queries that join multiple tables together and then feeds that into the transform and load. Changes could happen in any of those tables, not just one.

One approach could be to just look for changes on the base table and do lookups or merges on the other tables. However that would miss changes on a supporting table if there is no corresponding change in the source table.

Another option would be to do individual extracts on all tables involved and then do the joining in the transformation layer. But again, there could be times that one or more tables isn't changed and isn't available for the join.

What I'm leaning towards is one of these 2 options:

Totally separate all tables into their own ETL that then do multiple inserts and updates on the destination table. For example, only process inserts for the source table of the query and process updates for all the supporting tables after the source table has been populated.
Store all previous extracts for a table in a versioned directory in the spirit of a "data lake" and then look up the related record from a previous version.

Is there a strategy that I'm not aware of?

I have used the CDC functions to extract data in the past. My plan was to try to use the SSIS CDC components themselves if possible.

My main reason for looking into this is to increase the frequency of our loads to be on an hourly basis instead of a nightly basis. Using CDC on my biggest tables would reduce the load, and reduce the time needed. It would still be ideal to only pull the data that has changed no matter the size.

We also have the ability to add a read only secondary to our AG to scale out reads similar to what Dave describes in his answer. However, the time taken to pull all the data out would still be the same even though the same load wouldn't be on the source system.


texts: Restore a database by pointing to one or more directories containing backup files and specifying a STOPAT time

texts:
I'm working on prepping/testing migration of our ERP and other auxiliary systems to a new database server. This will involve restoring database backups from the old server onto the new one.

For the sake of convenience, I'd like to be able to use a script/stored procedure to restore a database by simply pointing to one or more directories containing the backup files (combination of full, differential, and log), and optionally specifying a STOPAT time, or omitting it and restoring to the most recent possible time given the backups present. I would also need to be able to specify new file locations (the MOVE option), either explicitly, or more generally "put all the data files here, and all the log files here". The tool should look at all the files in the provided directories, and determine which ones need to be restored to reach the target time.

The GUI restore tool in Management Studio can do all of this, but that obviously can't be included in a script. I'd like to be able to write a general migration script that would be conceptually similar to this:

EXEC master..RestoreDB
    @database='ERPSysDB',
    @source='\\oldserver\Backup\oldserver\ERPSysDB\FULL,\\oldserver\Backup\oldserver\ERPSysDB\DIFF,\\oldserver\Backup\oldserver\ERPSysDB\LOG',
    @datadir='D:\SQLServer\DATA',
    @logdir='L:\SQLServer\DATA';

--Do various cleanup/post processing steps in ERPSysDB here

EXEC master..RestoreDB
    @database='ERPCompanyDB',
    @source='\\oldserver\Backup\oldserver\ERPCompanyDB\FULL,\\oldserver\Backup\oldserver\ERPCompanyDB\DIFF,\\oldserver\Backup\oldserver\ERPCompanyDB\LOG',
    @datadir='D:\SQLServer\DATA',
    @logdir='L:\SQLServer\DATA';

--More post-processing in ERPCompanyDB here

--Now restore a test copy of the database that we don't back up directly on the production server,
--using production database backups instead:
EXEC master..RestoreDB
    @database='ERPCompanyDB',
    @restoreas='ERPCompanyTestDB',
    @source='\\oldserver\Backup\oldserver\ERPCompanyDB\FULL,\\oldserver\Backup\oldserver\ERPCompanyDB\DIFF,\\oldserver\Backup\oldserver\ERPCompanyDB\LOG',
    @stopat='2018-11-01T08:00:00'
    @with='MOVE ''ERPDataFile'' TO ''D:\SQLServer\DATA\ERPCompanyTestDB.mdf'', MOVE ''ERPLogFile'' TO ''L:\SQLServer\DATA\ERPCompanyTestDB.ldf''';

--Additional post-processing, database restores, etc...

Are there any existing tools that provide this functionality? I'm prepared to write my own, but it would be a fairly intricate chunk of code (particularly error/exception handling), so I don't want to reinvent the wheel. I'd prefer this to be usable within T-SQL, i.e. not PowerShell cmdlets, but CLR stored procedures would be fine (and quite possibly the easier/better way to implement this).


texts: Can we pass a varchar/text datatype in to a variable in pgbench?

texts:
--pgbench sql script

[pg93@db1 load_test]$ cat get_email.sql
\setrandom cnt 1 100000
\set v_username 'user_' || :cnt

begin;
select get_user_email(:v_username);
end;

get_user_email() is a function which can get user's email.

--pgbench

[pg93@db1 load_test]$ nohup pgbench -c 4 -T 10 -d proxy -U proxy  -N -n -f get_email.sql  >  get_email.out  2>&1 &

--the get_email.out file says

nohup: ignoring input
client 0 executing \setrandom cnt 1 100000
client 0 executing \set v_username 'user_' || :cnt
invalid input syntax for integer: "'user_'"
invalid input syntax for integer: "'user_'"
set: unsupported operator ||

It's seem that can only use integer datatype.

--data

db2=> select * from users limit 3;
 username |      email
----------+-----------------
 user_6   | user_6@163.com
 user_19  | user_19@163.com
 user_20  | user_20@163.com

get_user_email() is a function which can get user's email.


texts: How can I set maxIdleTimeMS by command line or mongo-shell?

texts:
So, I have deployed my PHP application (using Laravel). However , I've noticed a high connection number in connection pool.

I would like to know how could I to set the maxIdleTimeMS parameter through MongoDB Shell or command line (or even via configuration file)?

Thanks in advance!

UPDATE

  Please, see the important note on below comment from @jmikola


texts:
SQL Server 2000

I've been tasked with cleaning up an SQL database of which I have no history working with so I'm not familiar with the tables within. Short from dropping a table and seeing if anyone hollers, what is the best practice for cleaning up orphan tables? I did not see a "modified date" attribute on the table, only a "created date".

Would the process be the same for SQL Server 2005/8?

Thanks,
Dave


texts:
In SQL Server, version 2008R2 Enterprise, 64 bit, we created a database to track DDL changes.
I was inspired by this solution: https://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/

Then we created a trigger, on each database, like this:

CREATE TRIGGER [CaptureChanges] ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
 AS ....

Where, after the as, the code make a simple insert in the audit database.

At first, it works very well, but when a user, owner of a database, but with no permission on the audit database, tries to alter a stored, SQL Server rises an error for insufficient permission.This is correct, because the user ha not permissions on the audit database.

So I used EXECUTED AS using a user with sysadmin role:

CREATE TRIGGER [CaptureChanges]  ON DATABASE
WITH EXECUTE AS 'useradmin'
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE

But now it does not work because this error:

Msg 916, Level 14, State 1, Procedure CaptureChanges, Line 0 [Batch Start Line 0]
The server principal "useradmin" is not able to access the database "userdatabase" under the current security context.

It seems, when you use EXECUTED AS, you are sandboxed to a single database: http://www.sommarskog.se/grantperm.html#EXECUTE_AS

So, I cannot use EXECUTED AS, because it does not work cross db. I dont like the idea to create the table to track changes in every DB where a user can be dbowner.

I can assign permission to every user to write in the destination table, but then I need to remember this for every new user. Someone will sure forgot this.

I also considered a trace, but it can be easly stopped or it will not restart at server start.

Is the trigger the wrong way? Can I fix this in some way?


texts: What Happens When Identity Range IsExceeded?

texts:
I am using identity columns in my merge replication solution to give people an id they can tell us over the phone (i.e. to support staff). A rowguid column would be too hard for this.

I have noticed that int identity columns are replicated no trouble, and each client subscription has a range of identity values it can use.

i.e. select * from MSmerge_identity_range

range_begin    range_end    next_range_begin    next_range_end
6003           7003         7003                8003

I'm just wondering what happens if you do some kind of bulk insert for instance which inserted 5000 new records into this table. What would happen to the identity column then?


texts: I have a nightly job that monitors database and log file sizes.  Over time the databases grow.  Last night some databases actually shrank in size.  The logs show no SHRINKFILE OR SHRINKDATABASE events.  AUTOSHRINK is OFF.  Is there any other way these databases could have shrank?


texts: Does dropping a partition and running update indexes stop DML execution on the whole table?

texts:
I am looking to drop a partition from a table in the database.
I wanted to know if I run ALTER TABLE DROP PARTITION UPDATE INDEX, would there be any blocking of DML statement executions on the other partitions as the indexes get updated?

Also, I am looking to drop 38 partitions in total in the table. Should I run the update index statement each time I drop a partition, or drop all 38 partitions and then run the update index statement?

P.S. the index is a global one.


texts:
Redshift support export data to CSV by executing this SQL query.

unload ('select * from bhuvi')
to 's3://bhuvi-bucket/folder//user-data.csv'
iam_role 'arn:aws:iam::1111111:role/myredshiftrole'
delimiter '|'  GZIP";

I have different tables and need to export them to different locations. So
I have a stored procedure like below. so I can pass the locations in a variable in the future.

CREATE OR REPLACE PROCEDURE test_variable()
 LANGUAGE plpgsql AS
$$
DECLARE
   sql text;
   s3_loc text;
   role text;
   deli text;
   query text;
BEGIN
   sql:='select * from bhuvi';
   s3_loc:='s3://bhuvi-bucket/folder/';
   role:='arn:aws:iam::1111111:role/myredshiftrole';
   deli:='|';
   query := "unload (sql) to 's3_loc/user-data.csv' iam_role 'role';
   execute query;
END
$$;

but somewhere the syntax is wrong. Im not able to find that. When I call this procedure, im getting this below error.

test=# call test_variable();
ERROR:  column "unload (sql) to 's3_loc/user-data.csv' iam_role 'role' " does not exist
CONTEXT:  SQL statement "SELECT  "unload (sql) to 's3_loc/user-data.csv' iam_role 'role' ""
PL/pgSQL function "test_variable" line 12 at assignment
test=#


texts: Merging two Select queries with different where statements

texts:
I have this query below in which I am trying to get results for the month and year to date (account balance).

SELECT T3.AcctCode, T3.AcctName, SUM(T1.Debit - T1.Credit)* -1 AS 'Balance'
FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode]
INNER JOIN OACT T3 ON T3.[AcctCode] = T2.[FatherNum]
WHERE T0.TaxDate BETWEEN '2014-09-01' AND '2014-09-30'
AND T3.AcctCode LIKE '611%'
GROUP BY T3.AcctCode, T3.AcctName
UNION
SELECT  T3.AcctCode, T3.AcctName, SUM(T1.Debit - T1.Credit)* -1 AS 'YTD Balance'
FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode]
INNER JOIN OACT T3 ON T3.[AcctCode] = T2.[FatherNum]
WHERE T0.TaxDate BETWEEN '2014-01-01' AND '2014-09-30'
AND T3.AcctCode LIKE '611%'
GROUP BY T3.AcctCode, T3.AcctName
ORDER BY T3.AcctCode

The results are as below:

The problem I have is that the Balance and Year to Date balance columns are stacked on on top of the other. How can I modify this query to have 4 columns, i.e: AcctCode, AcctName, Balance, YTD Balance? Using SQL Server 2008


texts: Query output conditional upon values in dataset

texts:
If the number column contains values 5 AND 6, AND the value X exists in the chr column, I would like to exclude rows with 10 in the test column.

But, if the number column contains values 4 AND 5, AND the value X does not exist in the chr column, I would like to exclude all but ONE of the rows with 10 in the test column.

+----+------+-----+--------+
| id | test | chr | number |
+----+------+-----+--------+
|  1 |    7 | C   |      4 |
|  2 |    7 | T   |      5 |
|  3 |    8 | C   |      4 |
|  4 |    8 | T   |      5 |
|  5 |    9 | A   |      4 |
|  6 |    9 | G   |      5 |
|  7 |   10 | T   |      4 |
|  8 |   10 | A   |      5 |
|  9 |   10 | X   |      6 |
| 10 |   14 | T   |      4 |
| 11 |   14 | G   |      5 |
+----+------+-----+--------+

How is it possible to do in MySQL?


texts:
We have to do information assurance remediation on our SQL Server 2012 and came across the following finding: Check to see that all required events are being audited.  So I ran these queries:

select DISTINCT traceid from ::fn_trace_getinfo('0')

Select DISTINCT(eventid) FROM ::fn_trace_geteventinfo('1')

This returned about 34 different eventids that I assume are being used for audit purposes.  Most of the items returned are NOT to be audited and should be replaced with others.  However, I have no idea how to remove eventids and how to add new ones.  For instance I am missing 14,15,112,113,114,118,128,129 and many others.  So my question is how to I remove these audits and add news ones?  A way to script it would be best or at least run it as a query.


texts: Query disk performance in VMware database

texts:
i need help to build a query to retrieve disks performance from VMware database. The query must bring the host LUN or the VM disk, and their respective IO and latency.

I found the view VPXV_HIST_STAT_DAILY with the performance collection, but i did not find a away to connect this view with the disk.

Thanks.


texts:
I have few (~20) names of products corresponding to an integer id that needs to be joined on products while exporting products data (using COPY (<query>) TO). The burden is that these names do not rest in DB, rather in Redis (rails's translations fyi). Therefore, I'll be collecting these names before each export. Now, having these names outside the DB, how can I build the query to join them?

I've tried to join these names on-the-fly:

SELECT
...
LEFT JOIN (
    SELECT unnest('{1,2,3}'::int[]) AS id,
           unnest($${"name1", "name2", "name3"}$$::text[]) AS name
) AS product_names ON product_names.id = products.type_id
...

But I've got busted by the amount of products rows (~500k) and lacking indices for that structure. Is there some (better) way to join these data on-fly? I guess one cannot index these on-fly data though.

Which is a good way to solve that situation? I can think of creating temporary table like:

SELECT ... INTO TEMPORARY ...;

but is it wise to have that stay on for long transaction, while exporting (~30 mins)? Or can I omit transactions while using temporary tables and how? Or am I just troubling myself with needless problems while the best solution is to just store those names as a regular table, only truncating it before each export to have actual names?

-- postgres 9.3.10


texts: function json_populate_record(..., text) does not exist

texts:
I went by the example from the docu which went fine:

select * from json_populate_record(null::x, '{"a":1,"b":2}')

But my self-constructed JSON simplified similar to this did not work :-(

-- p_some_num of type int
select * from json_populate_record( null:my_record_type, '{"a":'||p_some_num||'',"b":2}' )

resulting in:

ERROR: function json_populate_record(my_record_type, text) does not exist


texts:
im not sure if this would be considered as a question or not. I would delete it if not counted for. But just wanted to know, where can I get Oracle 12.1.0.1 software binaries.

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-linux-download-2240591.html

gives me 12.1.0.2 but what about 12.1.0.1 software?

Kindly help


texts: Joining timestamped records with the most-recent-prior records from another timestamped table

texts:
Say I have 2 tables:

Table: sales
  timestamp   timestamp without time zone
  amount      numeric

Table: prices
  timestamp   timestamp without time zone
  price       numeric

How can I get a result set that tells me, for each record in sales, the last price from before that sale took place?

Once I have that, I can multiply amount by the most-recent-prior price to get the total sale price.

(I did not design these tables, so I don't have the option of storing the price on each sale.)

I'm using PostgreSQL 9.3.

Edit: I can write something that works using nested subqueries that require O(n^2) space and/or time, but the sales table has millions of rows, so I'm hoping there's a better way to do it. Maybe some clever use of window functions or the Postgres-specific lead or lag?


texts: Postgresql: Refreshing materialized view fails with "No space left on device" and a huge spike in disk usage

texts:
I have a materialized view that refreshes every five minutes. The SQL aggregates the data among many tables with over 800k rows in each.
However, the when using the "REFRESH MATERIALIZED VIEW CONCURRENTLY tableName", the query runs for about an hour and then complains:
ERROR:  could not write block 39760692 of temporary file: No space left on device
It should be noted that this 39760692 changes every time I execute the query.
The disk size is about 960 GB and the database size is about 30 GB. So the disk has a free space of about 930 GB.
I noticed that when running the refresh query, there is a huge spike in the disk usage of about 12GB per minute and then finally the query errors out with no space error when it hits the 960 GB mark. Immediately, the disk usage is back to 30GB from the abnormal growth.
I even tried the REFRESH MATERIALIZED VIEW tableName (without concurrently) and seeing the same behavior.
I'm not sure what can be done here to diagnose the problem.


texts: SQL Server to MySQL sync, using Workbench, FreeTDS, and iODBC on 10.9

texts:
I've been struggling to get the MySQL Workbench to behave properly to complete the following:

With Mavericks (OS X 10.9), connect to a SQL Server and create a migration job to a MySQL server.

The main issue seems to be with ODBC drivers. iODBC was deprecated with 10.8, and the header files removed complete in 10.9 (Some discussion on this here. More in-depth discussion here).

Despite my best efforts, this is the farthest I have gotten in Workbench, when trying to Test the connection to a SQL Server on a local subnet:

  The ODBC driver that was selected for the source connection was not found by the ODBC manager.
  Ensure that you have the proper ODBC driver installed and retry.

  Error Text: Error("('00000', '[00000] [iODBC][Driver Manager]dlopen({FreeTDS}, 6): image not found (0) (SQLDriverConnect)')"): error calling Python module function DbMssqlRE.connect

Here is what I have done up to this point:

installed FreeTDS .92.XX from their site (homebrew install of FreeTDS wasn't acceptable, as it uses .91. Apparently .92 or greater is required, according to the Docs I have been following on MySQL, located here
Installed the latest iODBC files I could find on iodbc.org (seemingly forgotten since 10.6)
Using homebrew, installed unixODBC, just in case that provided some alternative, which did not seem to be of any use either,
installed ODBC Administrator App, v 1.1.0 from Apple

Has anyone successfully connected to a SQL Server database as a source, using the Workbench on Mac OS 10.9?

About to give up and opt for the Windows app, because this iODBC stuff is becoming pretty frustrating 5+ hours in :(

Edits

It seems that PuTTY is showing simple telnet and ssh commands over ports 3606 and 22 are not accepted either.

The firewall is completely open on Windows, from what I can tell. Private, Public, and Corporate settings allow for all inbound and outbound traffic.

root and users with a wildcard host are not working either :-/


texts: Can I tell mariadb which query plan to use for a given query?

texts: I have a query which gets different query plans over time and one of them takes less then a minute to execute, the other one takes days. Can I tell the db to use a given plan (taken from explain or using inline instructions) so that the good plan is always used or at least the catastrophical one gets always avoided? The good plan has only ref joins while the bad one also has index and eq_ref and a different ordering.


texts: SQL Server 2012 memory consumption outside the buffer pool

texts:
I've got an instance of SQL Server 2012 SP2 Enterprise Edition consuming ~20GB of memory higher than the max. memory limit. The instance is limited to 65GB but the physical memory in use from the below query shows 86GB

SELECT (physical_memory_in_use_kb/1024)/1024 AS [PhysicalMemInUseGB]
FROM sys.dm_os_process_memory;
GO

The server is physical with 2 NUMA nodes. Is there a way that can I find out what is consuming the memory outside of the buffer pool (I'm assuming that is what's happening)?

Here's the output of DBCC MEMORYSTATUS:-

And here'e the set memory limit:-

Thanks in advance.

UPDATE:- I've run the query that Aaron suggested

SELECT TOP (20) * FROM sys.dm_os_memory_clerks ORDER BY pages_kb DESC

Here's the output:-

The SUM of pages_kb comes to ~60GB

UPDATE 2:- Full output of DBCC MEMORYSTATUS is here:- http://pastebin.com/nGn6kXEc

UPDATE 3:- Output of Shanky's scripts in excel file here:- http://jmp.sh/LKRlH4K

UPDATE 4:- Screenshot of the output of:-

SELECT (physical_memory_in_use_kb/1024)/1024 AS [PhysicalMemInUseGB]
FROM sys.dm_os_process_memory;
GO

So this seems to indicate that SQL Server is using more than the 65GB set.


texts: How to solve Parameter Sniffing problem in a query with 'in' operator

texts:
I have a parameter sniffing problem in this Azure SQL Server query with in operator:

    Select Col1, Col2, ..., Coln from T1 Join T2
    On T1.Id = T2.T1Id
    Where T2.Col2 in ('uniqueIdentifier_1','uniqueIdentifier_2',..., 'uniqueIdentifier_m')

I have around 30-40 unique-identifiers in where clause. And I can not replace them because they are primary keys from another table in another Azure SQL Database. As you can see there is a range of different unique-identifiers and it is difficult to tune it.
Would you please guide me to solve a parameter sniff problem?


texts: Can't rely on INFORMATION_SCHEMA.view_definition?

texts:
I've built application that compares objects in the databases and generates scripts to replicate objects in the different DB. What I've noticed while I was using this to retrieve view code
select * from INFORMATION_SCHEMA.VIEWS where TABLE_NAME = 'V_SC_ALL_CUSTOMER_DOCUMENTS'

is that VIEW_DEFINITION cuts view DDL short. Then I've learned to use sys.sql_modules to get full definition.
I ran these
sp_help 'INFORMATION_SCHEMA.VIEWS'
sp_help 'sys.sql_modules'

and found out that INFORMATION_SCHEMA.VIEWS.VIEW_DEFINITION nvarchar size = 8000 and sys.sql_modules.DEFINITION nvarchar size = -1. I believe nvarchar(max).
What is the point then to have VIEW_DEFINITION if you can't rely on it? And are there other types of objects that I should retrieve in sql_modules rather than from their corresponding tables, like Procedures, triggers, etc?


texts:
I've been looking into this most of the day and I'm still not getting far so I thought I'd ask on here.

Basically its a lottery system and what I need to do is check whether 4 of the 6 balls I have selected are already taken in the database.

The table I'm using to test looks like this

CREATE TABLE [dbo].[Lines](
   [Id] [int] IDENTITY(1,1) NOT NULL,
   [Ball1] [int] NOT NULL DEFAULT ((0)),
   [Ball2] [int] NOT NULL DEFAULT ((0)),
   [Ball3] [int] NOT NULL DEFAULT ((0)),
   [Ball4] [int] NOT NULL DEFAULT ((0)),
   [Ball5] [int] NOT NULL DEFAULT ((0)),
   [Ball6] [int] NOT NULL DEFAULT ((0)))

I have a query that works but its slow, too slow for our requirements. We have estimated around a 20mil record count in this table. So far with just 5mill in our test db its taking ~2 seconds to run the query even with indexing. I see in the plan that this is due to a table scan but I cant figure out how to get around this.

The query I'm testing with is this.

DECLARE @matchCount INT = 4

DECLARE @ball1 INT = 2
DECLARE @ball2 INT = 11
DECLARE @ball3 INT = 16
DECLARE @ball4 INT = 28
DECLARE @ball5 INT = 36
DECLARE @ball6 INT = 39

SELECT
       count(*)
FROM Lines
WHERE
       CASE WHEN [Ball1] IN (@ball1, @ball2, @ball3, @ball4, @ball5, @ball6) THEN 1 ELSE 0 END +
       CASE WHEN [Ball2] IN (@ball1, @ball2, @ball3, @ball4, @ball5, @ball6) THEN 1 ELSE 0 END +
       CASE WHEN [Ball3] IN (@ball1, @ball2, @ball3, @ball4, @ball5, @ball6) THEN 1 ELSE 0 END +
       CASE WHEN [Ball4] IN (@ball1, @ball2, @ball3, @ball4, @ball5, @ball6) THEN 1 ELSE 0 END +
       CASE WHEN [Ball5] IN (@ball1, @ball2, @ball3, @ball4, @ball5, @ball6) THEN 1 ELSE 0 END +
       CASE WHEN [Ball6] IN (@ball1, @ball2, @ball3, @ball4, @ball5, @ball6) THEN 1 ELSE 0 END >= @matchCount

Is there a better way to achieve what I am try to do? or do I have to throw more hardware at it?

This is the execution plan from the above query.

Thanks in advance!


texts:
I have designed my database model with entities and they have relations along with normalization. Now I want to implement my design in my database engine. Before implementing I am puzzled with the order of table creation.

Which pattern should I follow?

the tables with no foreign keys first
the tables with foreign keys

Or would it be better to follow this order:

tables without any foreign keys
tables which are connected with that table

I am new to database design. So forgive me if i am asking this awkward question.

I am implementing my design via mysqlworkbench 8.x


texts: Partition an unpartitioned primary key index to become locally partitioned by primary key

texts:
I tried:

alter index clave_primaria_de_b local;

and I read:

ALTER INDEX

But I don't know how to do it.


texts:
I've got a data warehouse (oracle) where I need to set a column to the same value for all 700 million rows.

I don't have admin access, or access to an admin, so this needs to be accomplished with basic sql and no temp table creates.

Further complicating matters is if I try to just do a simple update where 1=1, it runs out of redo space.

The way I have it running right now is looping such as this:

loop
  update mytable set mycolumn = '1' where mycolumn is null and rownum < 50000;
  commit;
end loop

but I know this is probably naive and there must be a quicker and more elegant solution.


texts: SQL Server differential backup LSN values

texts:
I'm looking at restoring databases on SQL Server and I'm not understanding something related to the LSNs. Here is a screenshot from the Restore Database dialog in Management Studio for a sample database I created.

You'll notice that there are two differential backups. If each differential backup contains the data for the transactions of all the previous differential backups, why would the FirstLSN of the second differential backup (22000000041800004) be higher than the FirstLSN of the first differential backup (22000000039800037)? If it covers the same transactions why wouldn't the LSNs overlap?

If you look at Microsoft's documentation on this it states

  For data and differential backups, the first LSN identifies the earliest log record that is needed to perform recovery with this backup.

So if I am going to recover the database using the second differential backup, wouldn't it stand to reason that the second differential backup's FirstLSN would start where the full backup left off (22000000038800001)? Restoring the second differential would apply all the transactions that occurred after the full backup was created wouldn't it?

I'm not understanding how the system accounts for the LSN gap between the full backup and the second differential.


texts: Handling exceptions in stored procedures called using insert-exec blocks

texts:
I have a stored procedure that is called in an insert-exec block:

insert into @t
    exec('test')

How can I handle exceptions generated in the stored procedure and still continue processing?

The following code illustrates the problem.  What I want to do is return 0 or -1 depending on the success or failure of the internal exec() call:

alter procedure test -- or create
as
begin try
    declare @retval int;
    -- This code assumes that PrintMax exists already so this generates an error
    exec('create procedure PrintMax as begin print ''hello world'' end;')
    set @retval = 0;
    select @retval;
    return(@retval);
end try
begin catch
    -- if @@TRANCOUNT > 0 commit;
    print ERROR_MESSAGE();
    set @retval = -1;
    select @retval;
    return(@retval);
end catch;
go

declare @t table (i int);

insert into @t
    exec('test');

select *
from @t;

My problem is the return(-1).  The success path is fine.

If I leave out the try/catch block in the stored procedure, then the error is raised and the insert fails.  However, what I want to do is to handle the error and return a nice value.

The code as is returns the message:

Msg 3930, Level 16, State 1, Line 6
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

This is perhaps the worst error message I've encountered.  It seems to really mean "You did not handle an error in a nested transaction."

If I put in the if @@TRANCOUNT > 0, then I get the message:

Msg 3916, Level 16, State 0, Procedure gordontest, Line 7
Cannot use the COMMIT statement within an INSERT-EXEC statement unless BEGIN TRANSACTION is used first.

I've tried playing around with begin/commit transaction statements, but nothing seems to work.

So, how can I have my stored procedure handle errors without aborting the overall transaction?

Edit in response to Martin:

The actual calling code is:

        declare @RetvalTable table (retval int);

        set @retval = -1;

        insert into @RetvalTable
            exec('

declare @retval int;
exec @retval = '+@query+';
select @retval'
                    );

        select @retval = retval from @RetvalTable;

Where @query is the stored procedure call.  The goal is to get the return value from the stored procedure.  If this is possible without an insert (or, more specifically, without starting a transaction), that would be great.

I cannot modify the stored procedures in general to store the value in a table, because there are too many of them.  One of them is failing, and I can modify that.  My current best solution is something like:

if (@StoredProcedure = 'sp_rep__post') -- causing me a problem
begin
    exec @retval = sp_rep__post;
end;
else
begin
    -- the code I'm using now
end;


texts: Want to remove global_name from DB links (Oracle 11g)

texts:
I am trying to call remote function via DB LINKS.

However, My global name contains the string ON and I believe it causes an error.

The Error I have is ...

SQL> var tmp varchar2(2);
SQL> execute :tmp := IF_TRX_SKY.CREATEWITH@SKYLBS_LINK2.I-ON.NET('a', 1, 2, 't', 't', 'k', 'm', 'r', 'u', 's', 's', 't', 'k', '00', sysdate, 1);
BEGIN :tmp := IF_TRX_SKY.CREATEWITH@SKYLBS_LINK2.I-ON.NET('a', 1, 2, 't', 't', 'k', 'm', 'r', 'u', 's', 's', 't', 'k', '00', sysdate, 1); END;

                                                   *
ERROR at line 1:
ORA-06550: line 1, column 52:
PLS-00103: Encountered the symbol "ON" when expecting one of the following:
( - + case mod new null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
continue avg count current max min prior sql stddev sum
variance execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string literal with character set specification>
<an alternatively-quoted SQ

I assume that if I removed .I-ON.NET part, it would work.

How can I remove/escape this? Oracle documentation states that GLOBAL_NAME is required for replication. Then how can I call this remote function without removing the GLOBAL_NAME?

Here is my create statement to create DB LINKS

SQL> CREATE PUBLIC DATABASE LINK SKYLBS_LINK2
  2  CONNECT TO ICE
  3  IDENTIFIED BY ICE1234
  4  USING 'ORCL';

Database link created.

And this is setting what I have

NAME                             TYPE        VALUE
---------------------------- ----------- ----------------
global_names                    boolean      FALSE

FYI, I can not change config files or reboot database, since I don't have permissions.

Summary:

How to remove GLOBAL_NAME from DB LINKS which I never write in my create statement.
How to execute remote function which contains a forbidden string


texts: Insert and select the row autoincremented id

texts:
What I want to do is insert a row and than select its auto incremented id number, In a multhi-threaded enviroment.

I know how to do it with a Stored Procedure.

Will this line of code work:

BEGIN TRANSACTION;
INSERT INTO table (r1) VALUES (...);
SELECT id FROM table ORDER BY id DESC LIMIT 1;
COMMIT;

And are there any other options?


texts: Overcoming the SQL Server Express Edition limitation

texts: Microsoft SQL Server 2014 Express edition has a database size limit to 10GB. Now, is that just for a single instance or the overall size that the edition will allow? Or does that means that I can have as many databases using the edition provided each database is less than 10GB?


texts:
how to write a case expression with Postgres?  I have this

case
  when rc.stat = 0 Then unpaid
  when rc.stat = 1 Then paid
  when rc.stat = 2 Then dispute
  else rc.stat
end

but when I go to run the statement I get an error of

Error: column "unpaid" does not exist

I have also tried double quotes and brackets around the text values, but all of those options give me errors.  Just to note, the values unpaid, paid, dispute are NOT fields in the database, but text values I want to write.


texts:
select top 10 percent *
from sys.databases
order by database_id

I want to get the same 10 percent result using offset but this query works wrong

offset 0 rows
fetch next (select cast((count(*) * 10/100) as int ) from sys.databases) rows only


texts:
I am trying to understand different memory components in sql,so far my research lead me understand below ,If SO DBAs can confirm my understanding/add something if i am missing,i will be really gratefull

Total components which use memory in SQL can be found through below query,were each  distinct clerk means a component.

select * from sys.dm_os_memory_clerks

SQL dynamically calculates buffer pool memory +memory used for storing plans using max memory settings.This is the part which confuses me .so i am taking one scenario to explain in detail

Test case:
Suppose my SQL is having  64 GB ram and i have capped max server setting to 58 GB,then below are the events that occur

Buffer Pool plus query plans  will be able to use maximum of 58 GB ram
Remaining 6 GB of RAM will be used to for OS and other SQL components

So my questions are like below

Since sql server calculates number of locks during startup,will the memory used by locks fall into 6 GB category ?
Suppose i have executed a query which requires more  memory ,will this memory be allocated from 6 Gb or from buffer pool (58 Gb ) memory or will this be spilled to disk ?
There are different memory clerks all shown above in screenshot,will they fall in 6 GB category ?
In 64 Bit systems,does Sql still calculate memtoleave option or with the exception of few memory clerks ,everything falls under
58 GB category ?
Further largest consumer of these clerks would be buffer pool and
query plans. If other clerks face memory pressure or need more
memory,memory allocation will come through buffer pool clerk in
terms of stolen pages if they pages flushed to disk to accomodate
this request.

Below are total references i had

1.SQL Server Max Memory not limiting use of RAM

--links in question too

2.http://blogs.msdn.com/b/sqljourney/archive/2012/12/17/an-in-depth-look-at-sql-server-memory-part-1.aspx
3.Slavos msdn blog


texts:
I am developing a billing system software. For this I have created a database it contains many tables database and this is in local system
all transactions data will be stored in local systems database.

I would like to provide data backup tables in server.
Whenever the person(billing s/w user) wants he/she can upload the data to server(only newly added data should be uploded if existing data is there).

If local system data is currepted or get deleted by some reason it can be downloaded  from the database  server.
This all features should be done by using billing software.
How to do this.


texts: Can I use attribute name to identify XML element?

texts:
Rather than using the element index COLUMN[1]/@value below, is it possible to retrieve the column value by specifying an attribute name such as: COLUMN[@name=”Prod_ID”]/@value?

When I tried, I got this error:

  Msg 2389, Level 16, State 1, Line 41
  XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

My code:

DECLARE @xml AS XML = '<DATA>
  <ROW RowNumber="1">
    <COLUMN Name="ProdID" Value="1234" />
    <COLUMN Name="AppID" Value="20144" />
    <COLUMN Name="ConsentDesc" Value="My Description" />
    <COLUMN Name="ConsentVer" Value="" />
    <COLUMN Name="ConsentAcceptance" Value="6789" />
    <COLUMN Name="ConsentType" Value="TermsAndConditions" />
    <COLUMN Name="CreatedDate" Value="8/22/2014 3:04:29 PM" />
  </ROW>
</DATA>'

SELECT
   m.c.value('@RowNumber', 'varchar(50)') AS RowNumber ,
   m.c.value('COLUMN[1]/@Value', 'varchar(50)') AS [ProdID] ,
   m.c.value('COLUMN[2]/@Value', 'varchar(50)') AS [AppID] ,
   m.c.value('COLUMN[3]/@Value', 'varchar(50)') AS [ConsentDesc.] ,
   m.c.value('COLUMN[4]/@Value', 'varchar(50)') AS [ConsentVer.] ,
   m.c.value('COLUMN[5]/@Value', 'varchar(50)') AS [ConsentAcceptance] ,
   m.c.value('COLUMN[6]/@Value', 'varchar(50)') AS [ConsentType] ,
   m.c.value('COLUMN[7]/@Value', 'varchar(50)') AS [CreatedDate]
FROM
   @xml.nodes('DATA/ROW') AS m ( c )


texts: Representing multiple attributes for students

texts:
I have a data structure that has group of students.
Each student has an array of attributes - that is, he has an array of strings that represent attributes

How do I plan my data structure? Have a table of students and another table of classes in each grade has a pointer to his students

I need to save all to database - on android device.

Or something else ?


texts: Can I check if a Mirror database was synchronized when the principal died?

texts:
having 2 machines, one being the principal and the second one as a mirror in case of failure of the 1st one. without witness but in High Safety mode
Is any way to check if the mirror DB was synchronised when the Principal has failed?
I need this because I'm concerned if:

1-the mirror goes down
2-the Principal does a lot of work (while mirror is down)
3-the mirror come up, and starts synchronising, while synchronizing the principal goes down

in this situation, can I detect the mirror is not up to date without a witness??
So...is there a way to check if the Mirror database is up to date while the Principal is down?
Edit:
I tried to use a Event Notification, to keep track of mirroring changes, but theses notifications are asynchronous, so there is a gap I cant cover.
Is any other way?


texts: How to populate another column with a sequence value generated for an identity column?

texts:
I have a table that has a column that is determined by a sequence using the identity column thing, so after a record is inserted (or during, I'm not sure how it works behind the scenes, only that it does) it gets a number one higher than the previous record.

I'm writing a program where there is a condition that leads to a record having that number also be in another column. Is there a way to do this without just making the record and then modifying it immediately afterwards?

Thanks!

Edit: My code:

String insertion = "INSERT INTO DANIEL.UNIQUE_PHYSICIAN "
                    + "(SOURCE_ID, ROOT_ID, SOURCE_TABLE, CONFIDENCE_IS_SAME,"
                    + " IS_ROOT_PHYS, FIRST_NAME, LAST_NAME, POSTAL_CODE) VALUES "
                    + "(" + p.getPhysicianID() + ", " + Integer.toString(root_id) + ", STI.PHYSICIAN, 1, 0, " + p.getFirstName()
                    + ", " + p.getLastName() + ", " + p.getPostalCode() + ");";

and the table:

CREATE TABLE UNIQUE_PHYSICIAN
(
  SOURCE_ID NUMBER
, ROOT_ID NUMBER
, SOURCE_TABLE VARCHAR2(20 BYTE)
, CONFIDENCE_IS_SAME FLOAT(126)
, IS_ROOT_PHYS CHAR(1 BYTE) NOT NULL
, UNIQUE_ID NUMBER NOT NULL
, FIRST_NAME VARCHAR2(20 BYTE)
, LAST_NAME VARCHAR2(20 BYTE)
, POSTAL_CODE VARCHAR2(20 BYTE)
, CONSTRAINT UNIQUE_PHYSICIAN_PK PRIMARY KEY
  (
    UNIQUE_ID
  )
  USING INDEX
  (
      CREATE UNIQUE INDEX UNIQUE_PHYSICIAN_PK ON UNIQUE_PHYSICIAN (UNIQUE_ID ASC)
      LOGGING
      TABLESPACE DATAL03
      PCTFREE 10
      INITRANS 2
      STORAGE
      (
        INITIAL 65536
        MINEXTENTS 1
        MAXEXTENTS UNLIMITED
        BUFFER_POOL DEFAULT
      )
      NOPARALLEL
  )
  ENABLE
)
LOGGING
TABLESPACE DATAL03
PCTFREE 10
INITRANS 1
STORAGE
(
  INITIAL 65536
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED
  BUFFER_POOL DEFAULT
)
NOCOMPRESS
NOPARALLEL;

In this instance, I want to set ROOT_ID to be equal to the UNIQUE_ID assigned to that record.


texts: How do install and configure Grid Infastructure on Windows>

texts:
I'm studying for the 1Z0-062 exam with John Watson's exam guide and I'm trying to install Grid Infrastructure alongside my Dbhome.

The book seems to run an installer that displays a configuration command to run at the end of the grid installation, but I haven't been able to find one.

On the Oracle site, whenever I try to find a Windows download for the Grid Infrastructure, I'm simply redirected to the OUI for the 12c Database. Problem is, the OUI won't prompt for any command to run after the installation, and when I try to run it myself it gives out this error:
https://puu.sh/vOGBB/2b89dd17cb.PNG

This is the command in question: Grid_home/perl/bin/perl -I Grid_home/perl/lib -I Grid_home/crs/install
Grid_home/crs/install/roothas.pl

What am I doing wrong?


texts:
Here's what i need to do:

Users will upload CSV files into SQL Server 2012 blob column
Each night, i would like to take each file and import the data into table
I will have 2 web sites using the same database. One is internal and one is in the DMZ
If i want my 2 sites to see the files, the easy way would be to store the files in the database. It will be also more easy to take backup of everything
If i store the files on a shared folder, i will have to open a port in the DMZ to see this shared folder inside our network. I don't know if its a good idea. Also, the backup plan will be harder to make (backup the database and backup the files separatly and try to synchronize them)

Is it possible to use a stored procedure to read the file inside the blob column, loop on each line and insert the data in a table?

Should i export the file to a temporary system file
Read the file and do the importation

or can i

Read the file directly from the blob colum and do the importation

other proposition?


texts: How do I resolve Postgresql error, "could not determine which collation to use for string comparison"?

texts:
When ever I run this SQL script I get:

  ERROR:  could not determine which collation to use for string comparison

This is what is on line 34:

CREATE TEMPORARY TABLE weight_options AS
  SELECT
    io.lot_id
    , io.vin
    , map.fkey_style AS chrome_styleid
    , SUM( CASE WHEN co.code = io.code THEN 1 ELSE 0 END ) AS exact_option
    , SUM( CASE WHEN length(io.code) = 2 AND co.code LIKE io.code || '%' THEN 1 ELSE 0 END ) AS appx_option
    , (
      SUM( CASE WHEN co.code = io.code THEN 2 ELSE 0 END )
      + SUM( CASE WHEN length(io.code) = 2 AND co.code LIKE io.code || '%' THEN 1 ELSE 0 END )
    ) AS option_weight
  FROM inventory.options AS io
  -- options *must* have same index as inventory.vehicles
  JOIN chrome_vinmatch_best_match AS vd
    USING ( vin )
  JOIN chrome_vinmatch.style AS map
    ON ( vd.chrome_patternid = map.fkey_pattern )
  JOIN chrome_nvd.options AS co
    ON ( io.code = co.code AND map.fkey_style = co.fkey_style AND io.code IS NOT NULL )
  GROUP BY io.lot_id, io.vin, map.fkey_style
;

I just did a migration from an install of 8.4.11 to an install of 9.1.3 using pg_dump.


texts: Optimizer logical trees: Order of the different tree types?

texts:
Based on observing output from Traceflags 8605, 8606 and 8607 (and comments from i-one), I have this as the order in which the logical trees are generated.

Converted
Input
Simplified
Join-Collapsed
Before Project Normalization
After Project Normalization
Output

Outside of the ordering of Converted and Simplified, I have not however found independent verification that my assessment is correct.
Is this the correct order?


texts: Designing a database which can track stock movements

texts:
I work for a manufacturing company which sells machines to customers which have a control unit installed in them. We need to keep track of the locations of all the Machines after they are sold to the Customers. We need to keep track of all the Control Units whether they are assigned to the Machines, they are sent to Customers as spares, they have being returned back to the Supplier for repair or in stock.
I am trying to setup tables to record of the history of the Locations of Machines which can be assigned to a Customer but then the same machine can be moved/reassigned to another Customer. The Machine has a Serial Number.
Also need to record the history of the Locations of a Control Unit which can be assigned to a Machine which can then be moved/reassigned to another Machine. A Control Unit can also be sent to a Customer as a backup unit in case of failure or it can be sent to the manufacturer for repair. After the Control Unit comes back from the Manufacturers it is put back in stock and at some stage it can be reassigned to another Machine. The Control Unit also has a Serial Number.

I have attached an ER Diagram of what I think might be a suitable solution.
The ERD diagram shows the link to MachineID, CustomerID and ManufacturerID in the TBL_ControlUnitLocations table. The problem I see is that when you select which link you want, the other two will need to be made NULL. When you view the history on a particular Control Unit you will end up with two fields always being blank.

One option is not to show the MachineID, CustomerID and ManufacturerID fields on the main form and only show the Location field. Is it possible to have another form which pops up and gives you the option to choice which one of the MachineID, CustomerID and ManufacturerID fields you wish to link to and then assign the link to the Location field and then close the popup form. So all you see then is the Location field.

How would you implemented the main form and the popup form to record the association?


texts: optimal table design mysql with primay key and varchar value

texts:
I have a table:

id (PRIMARY KEY AUTO_INCREMENT)
value (UNIQUE KEY)

which currently holds over 50 millions rows (and growing fast) and is hit hard:

process 1 queries this table (>200/sec) with a value to get it's id and insert it of not there
process 2 queries this table (>400/sec) with an id to get it's value.

So both processes are hitting the table and the queries are slowing down.
I considered partitioning but when on id the problem arises that process 2 queries need to check all partitions and when on value the same problems comes the other way around.

What would be an optimal table design for this?
Currently I use MyISAM since I get a higher SELECT performance than InnoDB but if someone tells me to change it, no problem.

thx,
Martin


texts:
I am running MySQL 5.7 (Percona) on CentOS 7.5.x, and when I set the serser-id the config file, MySQL is somehow changing the server-id value to '4294967295'. This is happening on all my PXC nodes. On the slaves, it is resetting to =0. Is this behavior normal? The MySQL config file is located here:

/etc/percona-xtradb-cluster.conf.d/mysqld.cnf

and the config file looks something like this:

...
userstat
#5.7 add setting
table_open_cache = 4096
innodb_open_files = 10240
open_files_limit = 10240
connect_timeout = 60
thread_cache_size = 4096
sql_mode = NO_ENGINE_SUBSTITUTION
query_cache_size = 0
slave_pending_jobs_size_max = 32M
range_optimizer_max_mem_size = 8M
log_timestamps = SYSTEM
server-id = 1015111

I have installed and managed MySQL on Ubuntu and never experienced this. How can i stop this from happening?


texts: Database Stuck 'In Recovery' with command XTP_DB_RECOVERY

texts:
We recently converted a table to memory optimized data. Our backups have all ballooned (3x300GB files to 3x600GB files for Full, 3x50GB to 3x250GB for Diffs), and startup has been getting progressively slower.

In an attempt to stave off these issues we converted the table in question to SCHEMA_ONLY durability, but now the database will not leave 'In Recovery' status.

The errorlog originally was updating on recovery status every 20 seconds, predicting analysis would take ~9days to complete, after roughly an hour however, the updates ceased.

SP_WHO2 shows only one process accessing the DB in question with the command XTP_DB_RECOVERY, however SELECT * FROM sys.dm_db_xtp_checkpoint_files returns no rows.

Do I have any recourse? Or way to see the estimated time remaining for this XTP_DB_RECOVERY command?


texts: Informix JDBC login fails, local login succeeds

texts:
I'm troubleshooting a "sudden" issue on our somewhat old Informix server (11.50.UC9GE). After years of running without issue (and connecting w/o issue), suddenly today we can't connect to the server remotely.

When I use dbaccess locally on the server and choose a connection method that prompts for username / password, the credentials work fine and I'm able to establish a connection. (note I can also intentionally enter invalid credentials and it will fail). This leads me to believe that the basic credentials I have are fine / unchanged.

However, when we try to connect to the server remotely (via a JDBC driver in DB Visualizer), we get this error:

An error occurred while establishing the connection:

Long Message:
Incorrect password or user com.informix.asf.IfxASFRemoteException: informix@ip-10-15-45-226.ec2.internal[MacBook-Pro.local] is not known on the database server.

Details:
   Type: java.sql.SQLException
   Error Code: -951
   SQL State: IX000

Up until today, this has always worked for us - any thoughts on what to check?


texts:
I am currently looking at how to most efficiently delete rows from a table in bulk. The database I'm using is MySQL with innodb.

The table I want to delete from can be any size, but will usually be ~10gb, and has a primary key (int) and indexed hash. This hash is non-unique, and I do drop it before proceeding with the deletes. The table has no other indexes.

I have a second table containing only the id's I wish to delete. The number of id's to delete can be anything from none, to everything.

The problem is I can't decide on a strategy on how to delete these rows quickly. I am aware of 2, which serve opposite sides of the extreme.

Option 1: The standard DELETE FROM table_data WHERE id IN table_todelete. This is good if there are very few deletions.

Option 2: Create a new table. Insert rows from table_data where id NOT in table_todelete. Drop table_data and rename the newly created table. This is obviously better when there are a large number of deletes.

I do not know enough about the performance implications of select, insert, delete, to know how fast they are compared to each other.
My question is, since my data isn't skewed towards always having many or few deletes, which approach is better in terms of performance? Is there some sort of theoretical threshold at which I can swap between the two approaches? If there is a third option I might've not considered, please let me know.

Thanks.


texts: Inserts very slow near the end of dataset

texts:
I'm trying to insert Whois Records ( domain name and relevant info) into MongoDB after parsing them from CSV files. I currently have a collection for each tLD such as .com, .org etc.

During the initial insertion of the records( such as the quick insertions of the lest populated and obscure tLDs and initial .com etc insertion ), I get decent insert speeds of about 10k-15k inserts per second.

However this slows to a crawl, part way through. Since MongoDB uses a B-tree for indexing primaries, shouldnt the performance slowdown due to database size be minimal ?

Extra Information if relevant: MongoDB 3.4, WiredTiger, Java 1.8, primary index is the domain name itself


texts: SQL Server report based on stored procedure not showing columns

texts:
I'm working in SQL Server 2008 R2. Created a stored procedure (EXEC dynamic SQL with CASE THEN ELSE ) on server with parameters works ok. But Report Builder on insert table is not showing columns for arrange!

I found WORKAROUND http://forums.yessoftware.com/posts.php?post_id=115829:

But there is a easy way?

simpified code

ALTER PROCEDURE [dbo].[VetCertTest] @Store char(5), @Date char(8)
AS
BEGIN
DECLARE @Log char(7) = 'MOS_DIS'
DECLARE @sqlQuery VARCHAR(8000) = 'SELECT
  rtiersS.alias store
, rfampr.lb as type
FROM
 presta_ru01.rul
, presta_ru01.rtiers rtiersS
, presta_ru01.pporte
, presta_ru01.rub
, presta_ru01.rfampr
WHERE
    rfampr.rdoss_code = ''''' + @Log + '''''
AND rfampr.code = rub.rfampr_code
AND rub.code = rul.rub_code
AND rtiersS.code = pporte.rtiers_code_dest
AND dhr_mvt >= to_date(''''' + @Date + ''''',''''DDMMYYYY'''')
AND dhr_mvt < to_date(''''' + @Date + ''''',''''DDMMYYYY'''')+1
' + CASE WHEN @Store = 'MA000' THEN '' ELSE 'AND rtiersS.alias = ''''' + @Store + '''''' END + '
ORDER BY rtiersS.alias, rtiersS.nom1, rfampr.lb'
DECLARE @finalQuery VARCHAR(8000) = 'SELECT * FROM OPENQUERY(WMSFELRU,' + '''' + @sqlQuery + '''' + ')'
EXEC(@finalQuery)
END

solved - add defaults to parameters and Report Builder can detect columns

Its a Report Builder bug - it not ask for parameters when refresh fields

IF @Store IS NULL SET @Store = 'MA000'
IF @Date IS NULL SET @Date = '19092014'


texts: Least recently used replacement strategy - where is pin count stored?

texts:
I am reading a book on databases and a chapter in it talks about buffer manager and it's replacement strategies.

The two popular replacement startegies mentioned are least recently used - LRU and clock replacement. I am interested in details about LRU. LRU is implemented via queue. When a pin count is set to 0, page in memory is added to queue. When a replecement is needed, memory page from the start of the queue is used.

The book does not describe how pin count (and dirty bit) for a page is stored. Is it stored inside the queue entries? Is it stored as part of the header in buffer pool frames?

Moreover, what happens if a page is accessed while being in the queue? Is it deleted from the queue immediately?


texts: help in designing a database for medical clinic keeping record of patient visit

texts:
I am making software for patients record keeping in a clinic . I have written a peewee model for setting up tables to store record of each patient visit and patient contact and a relative table that store name of parent or husband (in case female patient is married)

import peewee
from app.datastore.core import db as _db

class Patient(peewee.Model):
    """ Patinets table """

    name = peewee.CharField(max_length=90)
    townCity = peewee.CharField(max_length=200)
    contactnumber = peewee.CharField(max_length=20)
    age = peewee.IntegerField(default=0)
    gender = peewee.CharField(max_length=9)
    email = peewee.CharField(max_length=1024, null=True)
    postalAddress = peewee.TextField()

    def __repr__(self):
        return "<Patient Id: {}>".format(self.id)

    class Meta:
        database = _db

class Relative(peewee.Model):
    """ Visit attendence """

    patient = peewee.ForeignKeyField(Patient,
        related_name="patient")
    relation = peewee.CharField(max_length=10)
    relativeName = peewee.CharField(max_length=90)

    class Meta:
        database = _db

# the below table continues to increment on each visit of new or old patient by frontdesk.

class PatientAttendOnVisit(peewee.Model):
    """ Attendence or visit record """
    patient = peewee.ForeignKeyField(Patient,
        related_name="patient")
    dateTimeofvisit = peewee.DateTimeField()
    attendtype = peewee.CharField(max_length=12) # walkin or appointment
    department = peewee.CharField(max_length=9) # medicine or dental

    class Meta:
        database = _db

# the below table continues to increment on each patient visit by doctor

class PatientCheckup(peewee.Model):
    """
    Patient Checkup table stores what patient reports on each visit
    to doctor like symptoms, diagnosis and presciption
    """
    patient = peewee.ForeignKeyField(Patient,
        related_name="patient")
    chiefcomplaint = peewee.TextField()
    symptoms = peewee.TextField()
    diagnosis = peewee.TextField()
    treatment = peewee.TextField()
    presciption = peewee.TextField()
    dateTimeofvisit = peewee.ForeignKeyField(PatientAttendOnVisit)

    class Meta:
        database = _db

I am very new to database driven application with no knowledge of db design
please help me design the best database and schema ?


texts: I have upgraded mongodb to 4.4 from 4.2 in our dev server(RHEL 8). But unable to downgrade it from version 4.4 to 4.2 (Tried after changing package /etc/yum.repos.d/mongodb.repo). Getting error message, "Package mongodb-org-4.4.4-1.el8.x86_64 is already installed."


texts: multi-master to single-slave replication at table level with PostgreSQL or MySQL

texts:
Here is my scenario

Master1 hosting DB1
Master2 hosting DB2
...
MasterN hosting DBN

replicate to:

Slave1 hosting DB1,DB2... DBN

I've read similar questions and they recommend to start different instances at Slave1 and simply do MasterN-Slave1(instanceN) replication, as instructed here:

https://stackoverflow.com/questions/7544241/single-slave-multiple-master-mysql-replication

That would be piece of cake, but running different instances might be a waste of resources.

I really want to achieve this with an single DBMS instance at Slave1, and if possible with PostgreSQL; but can try with MySQL if there is a solution for this.

Any help is appreciated.


texts: SQL Server 2019 using Linked server to Oracle 19c (19.9) using Oracle 19c client on Windows - DBCOLUMNFLAGS_ISNULLABLE error

texts:
Having issues selected data for some Oracle tables over the linked server from SQL Server:
select type, count(*) from RMTDB..JOB1308.HOST_INTERFACE group by type order by 1 desc;

Msg 7356, Level 16, State 1, Line 13
The OLE DB provider "OraOLEDB.Oracle" for linked server "RMTDB" supplied inconsistent metadata for a column. The column "CREATE_TIME" (compile-time ordinal 3) of object ""JOB1308"."HOST_INTERFACE"" was reported to have a "DBCOLUMNFLAGS_ISNULLABLE" of 0 at compile time and 32 at run time.

Open query version of it works:
select * from OPENQUERY( RMTDB, 'select type, count(*) from HOST_INTERFACE group by type order by 1 desc')

result
TYPE      COUNT(*)
10          10450
8            6543
6            21

Anyone run into this?


texts:
Where does mysql store data? I found out (by using mysql>SELECT @@datadir ) that it's in var/lib/mysql - but that can't be it. I have a quite big database (4 GB) called 'bot', but all the files in the 'bot' subdirectory (var/lib/mysql/bot) have only 280KB. Where is the rest?

One more thing - 99% of the database's size is a text column in one of the tables - I know that mysql stores it in separate files, but does it create one file per record?


texts: Is there any global variable for PostgreSQL replication has configured or not

texts:
Is there any unique identifier or any global variable which can tell us if  replication is configured in current PostgreSQL server. Other than this query

select pg_is_in_recovery();

The above query results whether the server is in recovery or not,
If 't' we consider it as a slave and we can judge it is a part of replication but

If it is 'f' how can we determine the corresponding PostgreSQL server is a part of replication.

Because even a server which is not a part of replication will also return 'f' as a result of the above query.

You can say, why can't I run a query like

select * from pg_stat_replication;

which will return the list of slaves connected to it. Then we can determine it is a master or not.

Am facing a scenario like "if the master is having only one slave, after some time that slave went down, then how can we determine replication is configured or not in that server"


texts: how can I install Microsoft SQL Server 2005 Express Edition on Windows 8.1

texts: I can't install Microsoft SQL Server 2005 Express Edition on Windows 8.1.


texts:
We've been experimenting with Polybase on a SQL Server 2019 (CU2) box, using SQL Server external data sources and the performance has not been good - in most cases a 1400% increase.  In each case all of the tables/views we're querying are from external tables pointing at the same external data source. We've tried both running the query broken out on the local box, and using the same query as a view pulled in as an external table. We've also scripted every statistic from the remote server onto the external tables with no change.  You can see the performance difference below, using a sample query.

The servers are setup identical resource wise: 32GBs of RAM, 8 vCPU, SSD disks, and no other running queries.  I've tried against two different remote servers, one running SQL Server 2016 with the latest SP/CU, and a separate 2019 box running CU2.  The servers are VMs running on the same host, and we've ruled out any type of host contention.

Sample Query:

SELECT
     StockItem_StockNumber, BlanktypeId, NameHTML, BackgroundStrainName, IsExact, IsConditional
     ,ROW_NUMBER() Over(Partition By StockItem_StockNumber, BlanktypeId Order By pt.Name, p.Name, gptr.Text) as row_num
     ,pt.Name as Level1, p.Name as Level2, gptr.Text as Level3, MGIReference_JNumber
     ,gptr.Type as Level3Type
FROM
     StockItemBlanktypes sig
     INNER JOIN Blanktypes g on g.BlanktypeId = sig.Blanktype_BlanktypeId
     INNER JOIN BlanktypeStockTerms gpt on gpt.Blanktype_BlanktypeId = g.BlanktypeId
     INNER JOIN StocktypeTerms p on p.StocktypeTermId = gpt.StocktypeTerm_StocktypeTermId
     INNER JOIN BlanktypeStockTermReferences gptr on gptr.BlanktypeStockTerm_BlanktypeStockTermId = gpt.BlanktypeStockTermId
     INNER JOIN StockTermClosures ptc on ptc.ChildStockTerm_StocktypeTermId = p.StocktypeTermId
     INNER JOIN StocktypeTerms pt on pt.StocktypeTermId = ptc.ParentStockTerm_StocktypeTermId
WHERE
     ptc.ParentHeaderKey = 3

Running on the remote 2016/2019 box directly:

 SQL Server Execution Times:
   CPU time = 3486 ms,  elapsed time = 5035 ms.

Running on the 2019 box with Polybase and with PUSHDOWN OFF:

 SQL Server Execution Times:
   CPU time = 15016 ms,  elapsed time = 92113 ms.

Running on the 2019 box with Polybase and with PUSHDOWN ON:

 SQL Server Execution Times:
   CPU time = 3875 ms,  elapsed time = 74149 ms.

Polybase Execution Plan (they look the same regardless of PUSHDOWN option):

sp_whoisactive (wait info) shortly after starting Polybase query:

sp_whoisactive (wait info) further into the Polybase query:

Using a Linked Server instead of Polybase:

 SQL Server Execution Times:
   CPU time = 3032 ms,  elapsed time = 9316 ms.

That's roughly a 1400% increase in the time it takes the query to run.  What's confusing is that Microsoft is pushing Polybase as an ETL replacement, but there is no way that's possible with this kind of performance.

Are other people seeing similar performance using Polybase with connections from SQL Server to SQL Server?  And, does anyone know what Polybase internal operations could be causing this kind of slowness?

Thank you.

Update on 2/23/2020:

Not that it has any implications to the query performance, but I discovered today that Polybase queries are not honoring MAXDOP settings (either instance wide, or query hints) and the set statistics CPU time is not reporting accurately.

I was able to look at the execution plan through XML, and this is the only section that is informative.  The only information I could find on PREEMPTIVE_SHAREDMEM_GETDATA is its a wait type when a thread is waiting for a call to the GetData method of a COM object to complete.

Second update on 2/23/2020:

I created a single large table and I ran a "select *" both locally and over Polybase.  Those runs have almost identical run times. I opened a support case with Microsoft and I'll report back if we make any headway.

Update on 2/24/2020:

After Kevin's response (below) I made a few tweaks to the original post for clarity, and running the queries/DMVs he referenced comes back with these results:


texts:
Could you please help me with this subject.
We are using date filter since many time now and discovering a behaviour.

I made an exemple here : http://sqlfiddle.com/#!9/8f01f4/3

As you can see the join query returns 'a' and 'b' data.
When just adding a left join I loose 'b' data.

Could you please explain why this behaviour ?

If I change the where like that :  a.jma = '2020-05-18' OR a.jma = 20200518
it works. But I would like to understand to not reproduce this "bug".

Thanks

The codes copied from the fiddle:

CREATE TABLE `a` (
  `clef` INT(11) NOT NULL AUTO_INCREMENT,
  `jma` DATE NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`clef`),
  UNIQUE KEY `jma` (`jma`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1
;

INSERT INTO a VALUES (1,20200518);

CREATE TABLE `b` (
  `clef` INT(11) NOT NULL AUTO_INCREMENT,
  `jma` DATE NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`clef`),
  UNIQUE KEY `jma` (`jma`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1 ;

INSERT INTO b VALUES (1,20200518);

SELECT a.clef , b.clef
FROM a AS a
JOIN b AS b ON a.jma = b.jma
WHERE a.jma = '20200518';

SELECT a.clef , b.clef
FROM a AS a
LEFT JOIN b AS b ON a.jma = b.jma
WHERE a.jma = '20200518' ;


texts: How to measure the number of database writes on SQL Server 2008 R2

texts:
How would I measure the number of database writes on SQL Server 2008 R2

For example, how many updated rows and how many new rows in a database over a period of time (1 day, 1 week or 1 month)?


texts:
I recently checked this in my SQL server instances:

SQL Server 2012 EE:

-- get current SQL Server name\instance name
  SELECT @@SERVERNAME
-- get current machine name and instance name
  SELECT SERVERPROPERTY('MachineName'), SERVERPROPERTY ('InstanceName')

And the results show NULL value for instance name for some of the instances.

I did go through Instance name NULL

Sample output for one of my servers for SELECT SERVERPROPERTY('MachineName'), SERVERPROPERTY ('InstanceName')

was

'Class024.dev.com', Null

Should I change the Instance name? Is it necessary or does it returns NULL if the instance is the default one?


texts: Postgres 10 - Targeting schema's function on a multiple schema Database

texts:
I'm running PostgresSQL 10, and I have several schemas on my DB with multiple functions. I've created a schemaless script with all the functions on it (I've removed the schema prefix), with this, everytime i create a new schema, I ran the migration and create all the functions as well.
This was necessary/requested for a better data separation between customers. All the schemas are twins in terms of structure.
All was fine until I figured that SchemaA was calling a function from public. Even if I call:
SchemaA.myFunction(p_param1:= 'A', p_param2:= 'B').
If this "myFunction" calls another from the inside, it will target public schema by default.
The only way I made it work, was using an input parameter called p_user_schema myFunction(p_param1, p_param2, p_user_schema) and add the following statement as the first line of myFunction body.
EXECUTE FORMAT('SET search_path TO %L', p_user_schema);
I've 147 functions, I will need to adapt each of these, does anyone know a better way to target the callers schema, by callers I mean the prefix schema used on the main call.


texts: Why would SQL Server stop excluding partitions appropriately?

texts:
I have a Fact Table that will run with an appropriate plan during our morning processing. Occasionally, later in the day, if I go to query it, it will have a really strange plan.

When querying a table partitioned on date, you usually see a seek predicate like so:

But for some reason, it turns into this:

Yes, I'm aware that there are two different tables shown above. I pulled the good example from a similarly configured table, and it appropriately displays the seek predicate I would expect.

Both of these are Clustered Index Seek operators. Updating stats doesn't seem to help, but some part of Ola's maintenance scripts seem to fix it each morning.

Has anybody seen this before? Any tips on how to restore the good plan?


texts:
This question has probably been asked and answered before, and if so, I would appreciate if that could be pointed out to me.

I have googled this point quite a bit, but I want to be very sure as to what I am doing before performing this task:

I need to move my current postresql 9.1 database to a new server. This new server will be taking over on that account and the other "turned off".

I need to do this live without, as much as possible, disturbing the users or having to shut down operation.

I also need to move without any possibility of data-loss (anything being written during the move).

Like I said above, if this is already answered/laid out anywhere, I would much appreciate being pointed to it.

Note:

I am currently running on Gentoo Linux and will be moving to a Ubuntu server.


texts: Return unique records based on shared column in 2 tables

texts:
I'm very new to SQL and currently using SQL Server 2008 R2. I'm fairly certain that I need to use a join but not sure if that's possible. The goal is to return just the customer's email address based on their last visit to my store. Poking around, this is the query I started off with:

SELECT DISTINCT account
FROM   [main].[dbo].[visits]
WHERE  time IS NOT NULL
AND    date = '2017-05-25'

This correctly returned a list of customer account numbers for a particular day from table visits. Now I need to find the customer's email, but that is stored in a different table, custlist.

This is where I got lost. I tried some examples found on StackExchange but couldn't quite get the join logic down.

Any help is much appreciated, thank you!


texts: Modify owners in all tables. How to run a selected query?

texts:
I need to write a script which modifies all table owners in a Cloud SQL Postgres db. So far I have gotten this far:
SELECT format(
        'ALTER TABLE %I.%I.%I OWNER TO %I \gexec;',
        table_catalog,
        table_schema,
        table_name,
        {new_user}
        )
        FROM information_schema.tables
        WHERE table_schema = 'mySchema';
        ''')

It returns the right commands like:ALTER TABLE schema_version OWNER TO ali;

ALTER TABLE .. OWNER TO ..;
ALTER TABLE .. OWNER TO ..;

But how do I run these now? My code is below. Ask you kindly to provide any suggestions!
import sqlalchemy
    """ Connect to the PostgreSQL database server """
    conn = None
    try:
        # connect to the PostgreSQL server
        print('Connecting to the PostgreSQL database...')
        db_url = 'postgresql+psycopg2://{}:{}@{}:{}/{}'.format(
        username, password, host, port, database)
        engine = sqlalchemy.create_engine(db_url)
        conn = engine.connect()
    # create a cursor
        cur = conn.cursor()
            # execute a statement
        print('Reassigning the owners:')
        cur.execute(
        '''
        SELECT format(
        'ALTER TABLE %I.%I.%I OWNER TO %I \gexec;',
        table_catalog,
        table_schema,
        table_name,
        {new_user}
        )
        FROM information_schema.tables
        WHERE table_schema = 'mySchema';
        ''')
        # commit the changes to the DB
        con.commit()
        print("The owner has been altered successfully")


texts: SQL Server 2012: set a column to only use a single case?

texts: Is there a way to tell SQL Server 2012 to store both A and a as A (or both A and a as a) for some given column such that every insert statement does not need to have UPPER() in it?


texts:
As the title says, I'm wondering what is this command and why am I seeing a large amount of this query on my database? It's currently 13.30% of my total queries. I've tried googling this but there's not really any documentation on the subject.

Here's a part of the report done by mysqlreport:

__ Questions ___________________________________________________________
Total          17.43M     7.5/s
  DMS           8.58M     3.7/s  %Total:  49.23
  QC Hits       3.91M     1.7/s           22.42
  Com_          2.54M     1.1/s           14.54
  COM_QUIT      2.48M     1.1/s           14.21
  -Unknown     68.28k     0.0/s            0.39
Slow 10 s          17     0.0/s            0.00  %DMS:   0.00  Log:  ON
DMS             8.58M     3.7/s           49.23
  SELECT        6.05M     2.6/s           34.71         70.51
  INSERT        1.36M     0.6/s            7.80         15.85
  UPDATE        1.15M     0.5/s            6.60         13.40
  DELETE       20.35k     0.0/s            0.12          0.24
  REPLACE          61     0.0/s            0.00          0.00
Com_            2.54M     1.1/s           14.54
  set_option    2.32M     1.0/s           13.30
  show_proces  83.16k     0.0/s            0.48
  create_tabl  60.20k     0.0/s            0.35


texts: Slow-running query on clustered MySQL environment

texts:
I've got a very simple query, directly equivalent to

SELECT * FROM sometable t WHERE somecol = 'somevalue' ORDER BY createdon DESC LIMIT 0,20000;

When I run this query on my non-clustered development machine, it runs in 0.07 of a second.

When I run the same query on one of our clustered load-balanced servers, it takes upwards of half an hour to complete.  The data on both servers is exactly the same.

Why is this occurring?

Here is some more info on the setup:

The total number of rows in the table is ~14k, so it doesn't get anywhere near the LIMIT.
MySQL version is MySQL Server: 5.1.56-ndb-7.1.15a-cluster-gpl
Ubuntu 11.04 (GNU/Linux 2.6.38-8-server x86_64).
Storage Engine NDBCLUSTER
InnoDB on my local server

The table schema is as follows, where {X} is InnoDB on my local machine or NDBCluster on the production servers.

CREATE TABLE `sometable` (
    `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `UserId` bigint(20) unsigned NOT NULL,
    `Cookie` varchar(255) DEFAULT NULL,
    `somecol` varchar(30) DEFAULT NULL,
    `IpAddress` varchar(255) DEFAULT NULL,
    `SomeCollection` text,
    `someothercolumn` decimal(6,2) NOT NULL,
    `someothercolumn2` decimal(6,2) DEFAULT NULL,
    `Result` tinyint(4) NOT NULL,
    `Version` tinyint(4) NOT NULL,
    `Source` varchar(255) DEFAULT NULL,
    `CreatedOn` datetime NOT NULL,
    PRIMARY KEY (`Id`),
    KEY `CreatedOnIndex` (`CreatedOn`),
    KEY `SomeColIndex` (`somecol`),
    KEY `ResultIndex` (`Result`),
    KEY `SomeCol2Index` (`someothercolumn2`)
) ENGINE={X} AUTO_INCREMENT=97043 DEFAULT CHARSET=latin1;

The query returns 11,154 records, which is the correct amount for the query.

Regarding latency between the data nodes, this is what a typical ping request returns:

64 bytes from 192.168.3.45: icmp_req=1 ttl=64 time=0.479 ms


texts: What is the default 'window' an aggregate function is applied to?

texts:
The SQL Server documentation on the OVER clause HERE, mentions that the OVER clause:

  Determines the partitioning and ordering of a rowset before the associated window function is applied

By this I assume that aggregate functions are 'window functions'? While I understand what a window is (or at least how it works) when using the OVER clause, what is the default window for an aggregation function when no OVER clause is used?

i.e. for the SQL statement:

SELECT
  someCol,
  someCol2,
  AVG(someCol3) [avg]
FROM
  tbl
GROUP BY
  someCol,
  someCol2

What is the Window that the SUM function is being applied to?

Is it the grouped table prior to the SUM function being applied? In this case is it correct to assume that all window functions are the last operations to be applied to a result set? Since FROM / WHERE / GROUP BY / HAVING / SELECT / ORDER BY must all have been worked out to produce the final window on which to apply aggregation functions?

====================

In response to the answer below by Max Vernon:

If I write this query:

SELECT
    CP.iYear,
    AVG(CP.mUpgradeCost) OVER(PARTITION BY iYear) AS [avg]
FROM ProForma.dbo.CapitalProject CP

I get the results you refer to, with a row for each year in CapitalProject, and since there are many repeated years I get the same average for each year. I.e.

+-------+------------+
| iYear | avg        |
+-------+------------+
| 0     | 18636.3636 |
+-------+------------+
| 0     | 18636.3636 |
+-------+------------+
| 1     | 46741.5151 |
+-------+------------+
| 1     | 46741.5151 |
+-------+------------+

I then changed the SQL to group on years so that I got a single line per year. the SQL is:

SELECT
    CP.iYear,
    AVG(AVG(CP.mUpgradeCost)) OVER(PARTITION BY iYear) AS [avg]
FROM ProForma.dbo.CapitalProject CP
GROUP BY CP.iYear

HOWEVER, when I altered the function to this:

SELECT
    CP.iYear,
    SUM(AVG(CP.mUpgradeCost)) OVER(PARTITION BY iYear) AS [avg]
FROM ProForma.dbo.CapitalProject CP
GROUP BY CP.iYear

I was expecting the averages for each row over the partitioned table to be added together. But they didn't. Why is that?

Clearly the window that the SUM is being applied to is not a nested query as i thought it might be


texts:
I want to Calculate opening and Closing between two table.
Below is Data and expected output
 Create table #tbl_Receivable (Rec_ID int,Inv_ID int ,Customer_ID int,Rec_Date date,Rec_Amount varchar(50),Inv_type varchar(50),item_Weight int);
  Create table #tbl_Shop_InvM (Inv_ID int ,Customer_ID int,E_Date date,Rec_Amount varchar(50),Inv_type varchar(50),item_Weight int);
   Create table #tbl_Customer ( Customer_ID int ,Customer_Name varchar(50));

 insert into #tbl_Customer values (1,'Akhter'), (2,'Hussian');

INSERT INTO #tbl_Shop_InvM VALUES
(211,1,'2020-03-06',5000,'Payable',0),
(222,1,'2020-03-08',8000,'Payable',0),
(223,2,'2020-03-08',2000,'Payable',0),
(224,1,'2020-03-09',5500,'Payable',0),
(225,2,'2020-03-10',15000,'Payable',500)

INSERT INTO #tbl_Receivable VALUES
   (112,211,1,'2020-03-07',2000,'Received',0),
(115,222,1,'2020-03-09',4000,'Received',0),
(116,222,1,'2020-03-09',2000,'Received',0),
(117,211,1,'2020-03-12',1000,'Received',0),
(118,225,2,'2020-03-11',15000,'Received',0);

I want Below ouput in which Date is giving from 06-03-2020 to 12-03-2020 ,then opending will be 0,because there is not invoice before 06-03-2020 ,
if Date is giving from 12-06-2020 to 12-06-2020 ,then opening will be 09-03-2020 balance 10500

Desire Output


texts: Calculating the maximum seen so far for each point in time

texts:
What I'm trying to calculate is a bit tricky to describe, so please bear with me...

I have a table with product numbers, and the price of each product each week over a period of 4 years. Price can go up or down between the weeks. For each date, I'm trying to calculate the maximum price up to that point in time for each product.

In the sample table below, the third column (Price) is the data I have; the fourth column (Maximum Price PIT) is what I'm trying to calculate. - EDIT: to be clear, this column does not exist yet!

For example, on 15/01/2012 for product 001, the maximum price seen up to that point in time was 50, even though in the next week it goes up to 60 (which is the maximum over the whole period).

I think that I might need to use some kind of loop, but I really don't know where to start. I have a pretty good grip of the basics of SQL, but I get a bit fuzzy when it gets to window functions and further. I've googled the problem quite a bit, but haven't been able to find any pointers.

Can anyone help? Let me know if you need me to clarify what I'm asking for!

Product | Date  | Price | Max Price PIT (this column is what I want to calculate)
001   01/01/2012    25  25
001   08/01/2012    50  50
001   15/01/2012    35  50
001   22/01/2012    60  60
001   29/01/2012    50  60
001   05/02/2012    15  60
002   01/01/2012    18  18
002   08/01/2012    7   18
002   15/01/2012    10  18
002   22/01/2012    20  20
002   29/01/2012    30  30
002   05/02/2012    25  30

Thank you!

p.s. the table is 19391864 rows.


texts: Understanding JOINs and why the syntax works the way it does

texts:
Heyo,

I'm confused as to how this works:

UPDATE original
SET price=(SELECT price FROM new WHERE new.id=original.id)
WHERE id IN (SELECT id FROM new);

What it does is updating an original table with items with the prices of another table. The second table (my update table) has two columns, ID (which matches the corresponding ID of the first table) and a column for the new price.

As far as I understand, MySQL collects all columns that have a corresponding entry in the second table in the third row. And then it somehow magically updates the price column with a price column of the joint values created from the tables new and original. How does it determine which value goes where? How does it make sure nothing goes wrong if there is an ID used twice in the table with the new values?

Some clarification would be nice. Thanks in advance,
Yorrd

EDIT:

My question is, how does MySQL determine which row to match with which new value from the second table. Even if they are unique, how is being made sure that they're not mixed up accidentally?


texts: Database size reduced after backup on PostgreSQL 8.3 and restore in PostgreSQL 9.4

texts:
I did a pg_dump on a JIRA database that I was hosting in a PostgreSQL 8.3 server. Size of the database after vacuum full was 217132652 (approximately 207 MB).

Then I restored that JIRA database on a PostgreSQL 9.4 server with the following command:

$ psql -X -v ON_ERROR_STOP=1 -d jira2 -U jira -h localhost < jiradb2017_03_12.sql

I assume the restore would exit on any error since I used ON_ERROR_STOP=1, but the SQL script finished correctly (despite some warnings not related to data restore).

I ended up with a database with a size of 158019348 (approximately 151 MB).

So, what's the story here? Can I just assume database was restored successfully and PostgreSQL optimized its storage (somewhere between 8.3 and 9.4 versions) engine and is using space more efficiently?