SQL query help: Calculating days between observations I'm extremely new to SQL, in fact, I just really started playing around with it.  So I'm not exactly sure how to search for appropriate terms related to the question I have.  I'll do my best.

CONTEXT:

I have a table that I'd like to query using SQL Server.  That table has behavioral data for hundreds of thousands of animals over multiple days (I know! Lots of animals! Rats, actually, and it is data that has been compiled over many many years by many many people).  Here are the columns:

AnimalID (an identifying ID unique to that animal)
DateID (Date of observation-- for example, 20151111)
Behavior1 (# of times observed eating on that DateID-- could be 0, max is 337!)
Behavior2 (# of times observed grooming on that DateID-- could be 0, max is 36)
Behavior3 (# of times observed mating/attempted mating on that DateID-- could be 0, max is 77!)

There are other columns like "gender," "AgeInDays," and several more behavior types.  I don't think it is worth listing them all out.

I'd like to be able to use WHERE and GROUP BY in a query to summarize this dataset.

So far I have been able to write queries that give me the total number of days that each animal does a behavior or the total number of times that an animal has done a behavior across all records for each animal.  For example, I've done this by making WHERE [Behavior1] > 1 and GROUP BY [AnimalID].

This is actually pretty cool!  And I'm stunned at how quickly these queries run!  But I'm stuck on a different type of query.

QUESTION:

I want to write a query that will tell me the number of days (DateID) between the first date an animal (each unique AnimalID) tried to mate (Behavior3) and the second date the same animal tried to mate.  In the same output, I'd like to have the number of days between the SECOND date they tried and the THIRD date they tried.  All while having WHERE [Behavior1] > 1 and GROUP BY [AnimalID].

Ideally the output would have columns like this:

AnimalID
NumDays1 (number of days between first and second attempts for that AnimalID to mate)
NumDays2 (number of days between the second and third attempts for that AnimalID to mate)

Is this even possible to do in a single query?  I know I need to use DATEDIFF...   Any help would be greatly appreciated!

Apologies if I've messed up the way I was supposed to ask this question on this forum.

Thanks!

I think in this case, you best best since you are on SQL 2012 is to use Window Functions and specifically the LEAD function.

Here is an example to try and match your data.

Say I created a table with the columns you have here in the following way and then insert rows into that:

CREATE TABLE #AnimalBehavior
(
    ID int identity(1,1) primary key clustered
    ,AnimalID int
    ,DateID int
    ,Behavior1 int
    ,Behavior2 int
    ,Behavior3 int
);
GO

insert into #AnimalBehavior (AnimalID, DateID, Behavior1, Behavior2, Behavior3)
Values (1, 20150101, 0, 1, 1)
    ,(1, 20150201, 0, 1, 1)
    ,(1, 20150301, 0, 1, 1)
    ,(1, 20150401, 0, 1, 1)
    ,(2, 20150101, 0, 1, 1)
    ,(2, 20150301, 0, 1, 1)
    ,(2, 20150501, 0, 1, 1);
GO

From here I will want to only look at the times where your behavior3 is greater than 0 (so that we know it occurred during that period) and take the difference between the LEAD of the 2nd compared to the initial row, and the LEAD of the 3rd compared to the LEAD of the 2nd.  You can do that in this way:

With ab
as
(
Select AnimalID
, DateID
, LEAD(DateID, 1, 0) over (Partition By AnimalID order by DateID) DateID_1
, Lead(DateID, 2, 0) over (Partition By AnimalID order by DateID) DateID_2
, row_number() over (Partition By AnimalID order by DateID) as rown
from #AnimalBehavior
where Behavior3 > 0
)
Select AnimalID, Convert(date, Convert(varchar(8), DateID))
, DateDiff(dd,  Convert(date, Convert(varchar(8), DateID)),  Convert(date, Convert(varchar(8), DateID_1)))
, DateDiff(dd,  Convert(date, Convert(varchar(8), DateID_1)),  Convert(date, Convert(varchar(8), DateID_2)))
from AB
where rown = 1;

I believe this covers what you are looking for.  But feel free to ask questions if not.


Alternatives for Dblink in oracle? we have an SSIS job that inserts data into a table in server A from data in table which is located in some other server B.

For this,we are making use of dblinks to connect to other database . But, this is causing lot of performance issues and job is failing because it is taking lot of time to execute the query.

So, i would like to know , what are the alternatives for Dblinks. The one thing i got is , using Materialistic views .

BUt i am not sure , how that works ?  Please suggest..

A materialized views is not a replacement for a database link. It would likely create a local materialized view using data pulled accross a database link. What you need to do is tune the query that you are using for your materialized view.

Run the query in sqlplus with timing and autotrace turned on. Look for cartesean joins, full table scans and sorts. There are probably some additional indexes or changes to indexes that you can make that will make the query cheaper to run. You want all of the filtering and sorting to happen on the source side of the link so that you are bringing accross as little data as possible.


Status of objects owned & permissions granted by a (now)-disabled Windows Account When an employee (esp. sysadmin) leaves (ie, their AD account is disabled), what happens to objects they 'owned' (permissions they granted) in SQL Server?

In other words, for SQL Server security, do I have to change the ownership of those objects, grants?

(Currently on SQL Server 2008; anticipating upgrade to 2016 this fall.)

You'll want to change ownership of databases that are owned by the disabled user.  I'd recommend creating a dedicated account, either SQL Server authentication, or Windows Auth if you're not using SQL auth, to own each individual database.

You can identify databases that are owned by the individual in question using this query:

SELECT DatabaseName = d.name
    , PrincipalName = sp.name
FROM sys.databases d
    INNER JOIN sys.server_principals sp ON d.owner_sid = sp.sid
WHERE sp.name = '<DOMAIN\USER>';

Modify ownership on a database using ALTER AUTHORIZATION like this:

ALTER AUTHORIZATION ON DATABASE::<database_name_here>
TO <new_owner_name_here>;

Any SQL Server agent jobs that are configured to run "as" the disabled user will not run.

You can identify SQL Server Agent Job Owners using this query:

SELECT JobName = sj.name
    , OwnerName = sp.name
FROM msdb.dbo.sysjobs sj
    INNER JOIN sys.server_principals sp ON sj.owner_sid = sp.sid
WHERE sp.name = '<DOMAIN\USER>';

Change the job owner using this T-SQL:

EXEC msdb.dbo.sp_update_job @job_name = '<job_name_here>'
    , @owner_login_name = '<new_owner_name_here>';

Any stored procedures or other objects that are owned by the disabled account, where no one else has rights will obviously be problematic.


Does MySQLDump Verify the backup as restorable I have written a PowerShell script to use MySQL dump to perform full backups of a specific database at a specific time.
I am wondering if I need to confirm the backup is restorable via my script or will MySQLDump fail if there is corruption in the database?

The command I am currently using:

.\mysqldump.exe --user=old --password=mate --log-error=$errorLog  --result-file=$backupfile  --databases $database

No - there is no way to check if the text file that is produced by the mysqldump utility will in fact restore correctly. Of course, the backup process can be automated (e.g. as per here). I'm not much of a Windows man, but search engines appear to return a number of scripts which you could use/adapt.

What you CAN do however is to run the CHECK table command on your tables of interest - you mightn't be so interested in slow/never changing reference tables (i.e. names of US states for example). Depending on your scripting ability, if the CHECK process throws an error, you could a) stop the backup and b) page a dba or invoke some other process. An excellent answer on how to automate this workflow is given here.

If you wish to use a "belt and braces" approach, you could use the mysqlcheck utility which:

  mysqlcheck uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE
  TABLE, and OPTIMIZE TABLE in a convenient way for the user.

Also, see here, but be warned,

  If you use the --databases or --all-databases option to process all
  tables in one or more databases, an invocation of mysqlcheck might
  take a long time.

Basically, it can't be put better than as expressed here:

  It is always a good practice to test the whole solution
  backup/restore. So nothing is better than testing a restore, actually
  it should be a periodic procedure.

Like most things in life, dba work is a series of compromises! :-) You have to consider your RPO (Recovery Point Objective) and your RTO (Recovery Time Objective) (see here) and balance those with the time and expense spent in verifying backups. An agreement with the major stakeholders (clients, managers, dbas) on what is an acceptable level of risk vs. expense is what is required.

Remember though, that every extra 9 in your reliability will add an extra 0 to your budget!

"Yae cannae beat the laws o' physics, Jim!" (*)

(*) with apologies to Gene Roddenberry


alter table (mysql) adding column with default value of space (' ') On my Mac, using  5.5.11 MySQL Community Server (GPL)

alter table foobaz  add column (
extendedTitle varchar(3000) DEFAULT NULL,
clasindxIndent int,
spacedash1 enum (' ', '-', '1') default ' ',
plusSubs enum (' ', '+') default ' ');

works perfectly. On my Debian server using 5.1.49-3-log (Debian), it fails with

ERROR 1067 (42000): Invalid default value for 'spacedash1'

I don't understand the error, or why it works on my Mac and not on my server. Any hints or pointers greatly appreciated.

Pat

Either the rules have changed, or somebody missed something in compiler builds.

According to the MySQL Documentation on ENUM

  For example, a column specified as ENUM('one', 'two', 'three') can have any of the values shown here. The index of each value is also shown.

  Value   Index

  NULL    NULL

  ''      0

  'one'   1

  'two'   2

  'three' 3

  An enumeration can have a maximum of 65,535 elements.

  Trailing spaces are automatically deleted from ENUM member values in
  the table definition when a table is created.

You are using MySQL 5.1.49. That build probably errors out on seeing a one-character string with a blank.

MySQL 5.5 probably removes trailing spaces leaving an empty string.


How to select the latest record of each hour in a day? I want to select the latest record of each hour in a given date, based on the datetime column 'reading_on' in my table. What is the best way to do it? I already tried group by HOUR(reading_on) with HAVING("max(HOUR(reading_on))"), but I didn't get the proper result.

Below is my table structure

That's a groupwise maximum.
You may plump for a null-self-join:

SELECT  ir1.*
FROM inverter_readings IR1
LEFT JOIN inverter_readings IR2 ON
DATE(IR1.READING_ON) = DATE(IR2.READING_ON) AND
HOUR(IR1.READING_ON) = HOUR(IR2.READING_ON) AND
IR1.READING_ON  < IR2.READING_ON
WHERE IR2.id IS NULL

sqlfiddle demo.


ORDER BY usage in subquery (derived table) SQL standard allowed or not MariaDB documentation is mentioning that ORDER BY clause in subqueries (derived table) are never allowed by SQL standards.

Meaning the SQL query

SELECT
   field1
 , field2
FROM (
  SELECT
       field1
     , field2
  FROM
   table1
  ORDER BY field2
) alias

is never allowed by SQL standards

  A "table" (and subquery in the FROM clause too) is - according to the
  SQL standard - an unordered set of rows. Rows in a table (or in a
  subquery in the FROM clause) do not come in any specific order. That's
  why the optimizer can ignore the ORDER BY clause that you have
  specified. In fact, the SQL standard does not even allow the ORDER BY
  clause to appear in this subquery

see source

But when i look into the SQL 92 standard. (i know it's old)

4.9  Tables

  ...
  ...
     A table is either a base table, a viewed table, or a derived table.
           A base table is either a persistent base table, a global tempo-
           rary table, a created local temporary table, or a declared local
           temporary table.
  ...
  ...
          A derived table is a table derived directly or indirectly from one
           or more other tables by the evaluation of a <query expression>.
           The values of a derived table are derived from the values of the
           underlying tables when the <query expression> is evaluated.
  ...
  ...

  The simply underlying tables of derived tables and cursors are
           defined in Subclause 7.9, "<query specification>", Subclause 7.10,
           "<query expression>", and Subclause 13.1, "<declare cursor>". A
           viewed table has no simply underlying tables.

They mention cursors let see what they say about cursors and what they are.

4.21 Cursors

  ...
  ...

  A cursor in the open state identifies a table, an ordering of the
           rows of that table, and a position relative to that ordering. If
           the <declare cursor> does not include an <order by clause>, or
           includes an <order by clause> that does not specify the order of
           the rows completely, then the rows of the table have an order that
           is defined only to the extent that the <order by clause> specifies
           an order and is otherwise implementation-dependent.

So they mention here a cursor in open state can be a table is either a base table, a viewed table, or a derived table. if you look how they define what a table is in section 4.9 Tables

If i read this i could be interpreting it as using ORDER BY in derived table (like the SQL query above) is perfect valid by SQL standards and it's up to vendors if they would allow ORDER BY there as a option.
But i would be interpreting it wrong english is not mine mother laungauge, so the question is iám interpreting it correctly?

A cursor is not a table, it refers to a table or a view of that table.  The cursor has an order only to the extent that the query that defines the cursor has an order by.  A subquery within that larger query would not define the order, and the fact that the query is being used for a cursor doesn’t make an order by in subquery legal syntax.

Basically, as with any other query, if you don’t have an order by on the outermost layer, then the optimizer is free to return the results in what it deems is the easiest/quickest way. It may take advantage of indexes or caches or anything else that allows it to produce the correct result, but is not constrained to do so.


SSIS failure flow error. The decision after a sequence container failure does not proceed with the correct flow I have an SSIS sequence container that extracts data from individual source database tables into a Stage layer. However, I need to know when a given calculation crashes in order for the given error to be written to the configuration table, which is provided by the yellow highlighted task in which the procedure is called. The problem is that if a given container falls due to an error, it does not flow through failure precendence constraint into the given task (LogPackageFailed).
I tried setting FaiPackageOnFailure together with FailParentOnFailure to TRUE in the properties, but it didn't help. The proposed solution in the following links does not work either:

http://techblog.elish.net/2010/12/ssis-foreach-loop-container-continue-on.html
https://simonworth.wordpress.com/2009/11/11/ssis-event-handler-variables-propagate/

, which was addressed in a previous similar query.

I would look at the event handlers (Package explorer tab shows this nicely). Something in there might be tamping down the failure or the DWH_STAGE container might have an explicit return status.
Something is off because a minimal reproduction works as expected


Problems Exporting Long Columns to Flat File Using SSIS We have an SSIS package that is going to generate files to be consumed by Google Big Query.  The files will be gzipped .tsv files.

One of the requirements is that the file be UTF-8.  We have set this in the flat file destination so that it's 65001 - UTF-8.  After this, the generated gzip files are consumed to Big Query correctly.

The problem now is that some fields have character lengths of up to 21,000 characters.  The DT_WSTR doesn't allow for this size.

Changing the flat file destination field to DT_NTEXT yields the following error message

  Error: 0xC020802E at 14_3 Data Flow into MyTSV, Flat File Destination MyDestination [12]: The data type for "Flat File Destination MyDestination.Inputs[Flat File Destination Input].Columns[Value]" is DT_NTEXT, which is not supported with ANSI files. Use DT_TEXT instead and convert the data to DT_NTEXT using the data conversion component.

All solutions I have read involve converting back to DT_WSTR or changing the code page back to 1252 which is not an option due to the Big Query code page requirement and the length of the data.  Is there another solution for this problem?

Try clicking the Unicode Checkbox

Try the following 65001 code page and Unicode checkbox, it got rid of the error message for me. Hopefully we're on similar versions of SSDT. My Version is 15.

But first, double-click your flat file destination, and click the Update button...


https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/tuning-SQL-Plus.html

8.1.2 Execution Plan
The Execution Plan shows the SQL optimizer's query execution path. Execution Plan output is generated using EXPLAIN PLAN and DBMS_XPLAN.

Yes, it is possible to have a different actual plan. Example:
create table t1 (c1 number);
insert into t1 select 1 from dual connect by level <= 100000;
insert into t1 values (2);
create index i1 on t1(c1);
variable B1 number
exec :B1 := 2;
set autotrace on
select * from t1 where c1 = :B1;

        C1
----------
         2

Execution Plan
----------------------------------------------------------
Plan hash value: 955340196

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |   933 | 12129 |    59   (2)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| I1   |   933 | 12129 |    59   (2)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C1"=TO_NUMBER(:B1))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Statistics
----------------------------------------------------------
         14  recursive calls
          0  db block gets
         59  consistent gets
          0  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        446  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

But the actual plan was:
SQL> set autotrace off
SQL> select sql_id, sql_text from v$sql where sql_text = 'select * from t1 where c1 = :B1';

SQL_ID        SQL_TEXT
------------- ----------------------------------------
9677k5kzddy78 select * from t1 where c1 = :B1

SQL> select * from table(dbms_xplan.display_cursor('9677k5kzddy78'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID  9677k5kzddy78, child number 0
-------------------------------------
select * from t1 where c1 = :B1

Plan hash value: 1816312439

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| I1   |     1 |    13 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"=:B1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

22 rows selected.


Event replication on slave and Slave_IO_Running:NO I have one master and four slaves. Whenever I created an event on master it gets replicated on all slaves.However, i am running a script on one of the slaves that takes a snapshot of the machine and saves it somewhere else. During taking a snapshot i kept the Slave_IO_Running: NO  in the script and when the snapshot gets completed the script makes Slave_IO_Running: YES and the replication start again.I just want to know that after taking a snapshot at that one slave when the Slave_IO_Running turned to YES by the script, The event I created disappears from that slave but remains there on the other slave and when I delete the event on master it produces error on that slave at which snapshot is being taken.Please just let me know the reason and the effect of Slave_IO_Running: NO on slaves.

For non-LVM, I recommend doing

STOP SLAVE;  -- before taking the backup
START SLAVE;  -- after taking the backup

This both avoids the problem and avoids other issues.

What kind of backup is it?

LVM "snapshot" -- I recommend stopping mysqld completely.
mysqldump (or similar) -- be sure to add the parameter that says to dump stored routines.  (I think that will capture the EVENT.)


~2 Billion row fact table in star schema on Oracle. Advisable? Let me start with: I am not a DBA; I am a backend developer.

It has been suggested that, for a report schema, I implement a star schema where-in the fact table can have ~2B rows before being archived. ~1M rows a day will be added to said table. The information in the report can be no more than 15 minutes stale and page load time must be < 7s

The report to be offered will be a sort of "summary-and-drill-down" where in when a selected summary portion is selected a subset of the rows that made up said summary will be displayed in another section Example:

I am inclined to move to a NoSQL store like Cassandra because I am really concerned about a fact table (and perhaps attribute tables) stretching to 2 Billion rows. Hoping someone here can shed some light on both the use of a star schema for this and how well I can expect Oracle (or any RDBMS, for that matter) to handle selecting and joining through a table with ~2B records.

Thanks

Row count isn't a great indicator of database size. I would not be worried about Oracle scaling to 2 billion rows. Relational databases can practically scale up into the many terabyte range. The ability to scale comes down to data model, hardware, and developer skill, user requirements, and budget. It's feasible to scale an Oracle (or SQL Server) data warehouse up into the 10s to 100s of terabytes.

Using a star schema is the right starting point for a relational data warehouse. There are a number of well thought out techniques for data warehousing outlined in The Data Warehouse Tookit. This will help you design a data warehouse that can scale.

At some point, you'll want to look into features that your vendor offers like partitioning, materialized views, and dimensional hierarchies. You should be able to put that off in your first attempt, though.


Changing numeric type slow down query big time for postgres I have 3 tables, leads, lead_addresses and addresses. Scheme looks like this:

recently we decided that foreign keys would be beneficial for us so we changed data types for lead_id and address_id from numeric to integer in lead_addresses table (in scheme named LeadAddresses). Reason for data type change is because foreign keys cannot be created between id which is integer and lead_id for numeric. Same for address attributes.

All tables mentioned have 10k+ records.

Problem:

We have query which works with numeric types well, but with integer types takes 20s+. What it does, its selects all leads and join addresses (only one address per lead, the last address created).

EXPLAIN ANALYZE
SELECT
  lead.id,
  lead.updated_at,
  lead.state,
  CONCAT(address.street, ', ', address.city, ', ', address.state) as address
FROM leads AS lead
LEFT JOIN lead_addresses lead_address ON (
  lead_address.lead_id = lead.id AND
  lead_address.id = (
     SELECT id FROM lead_addresses WHERE lead_id = lead.id ORDER BY id desc LIMIT 1
  )
)
LEFT JOIN addresses address ON (address.id = lead_address.address_id)
WHERE (lead.state = 1)
ORDER BY "lead"."updated_at"DESC
LIMIT 15000

We have original database which is in production and execution is fast. Here is result of explain: https://pastebin.com/raw/F6XaKNzp

We made exact clone of same database got rid of indexes on LeadAddresses
and changed types from numeric to integer, build indexes again.

Here is result of explain again: https://pastebin.com/raw/LhAyJ4ZQ
this time it takes too long.

I noticed the subplan being executed for each row, on the other hand in old production database its fast since subplan is executed once.

Can anybody give me advice on how to make planner execute subplan only once? how to change datatype of tables correctly?

Here are indexes:

Order of operations to create test db (the one where query is slow):

1) we made clone of database from old production

2) we executed query. Query was fast same as in production database

3) we dropped indexes on join table (LeadAddresses)

4) we changed numeric types to integer

5) we build same indexes again

6) query is still slow :'(

I'll be glad for any advice. Thanks.

I would like to point out that the join condition in the EXPLAIN was still comparing ::numeric with ::integer as can be seen in the EXPLAIN PLAN you provided:

...
Hash Cond: (((lead.id)::numeric = lead_address.lead_id)
...
 Hash Cond: ((address.id)::numeric = lead_address.address_id)
...

So even though you seemed to have dropped, converted and re-created the relevant information, your query was still creating an execution plan based on old information.

Cleaning up the database with VACUUM ANALYZE ... rectified your issues.


Part of statement missing from sys.dm_exec_query_stats I'm trying to debug the individual parts of a large query; I'm using the statement offsets in sys.dm_exec_query_stats to separate them. There is one particularly troublesome statement that for some reason is not present in sys.dm_exec_query_stats; it is, however, present in sys.dm_exec_requests. Is this expected behavior? Can I not get the query stats for that substring because it's currently executing? To be specific, this returns the executing part of the query:
select * from sys.dm_exec_requests where statement_start_offset = 29466

But this does not:
select * from sys.dm_exec_query_stats where statement_start_offset = 29466

This is a production system and installing third-party tools or scripts is something I want to avoid unless absolutely necessary. Just looking to see if someone has thoughts about what could be going on with the DMVs here.

It's difficult to say what might be going wrong with your script, since you didn't show us what you're running. But no matter -- you're probably better off using sp_WhoIsActive, and doing something like this:
EXEC sp_WhoIsActive
    @get_plans = 1,
    @get_full_inner_text = 1,
    @get_outer_command = 1;


Convert string numeric values with comma as decimal separator to NUMERIC(10, 2) I have an SQL table of varchar columns which contain Greek formatted numbers (. as thousand separator and comma as decimal separator)

The classic conversion

CONVERT(numeric(10,2),REPLACE([value],',','.'))

does not work because the . (thousand separator) kills the conversion

E.g try

CONVERT(numeric(10,2),REPLACE('7.000,45',',','.'))

I want to convert such values to numeric(10,2)

Any suggestions of how to handle it?

(If you are using SQL Server 2012 or newer, please see @wBob's answer for a cleaner approach. The approach outlined in my answer below is only required if you are using SQL Server 2008 R2 or older.)

You don't need (or want) the thousands' separator when converting to NUMERIC, regardless if it is comma, period, or space, so just get rid of them first. Then convert the comma into a period / decimal and you are done:

SELECT CONVERT(NUMERIC(10, 2),
               REPLACE(
                       REPLACE('7.000,45', '.', ''),
                       ',', '.'
                      )
              ) AS [Converted];

Returns:

7000.45

For the sake of completeness, I should mention that I also tried:

SET LANGUAGE Greek;
Looking at various format styles for CONVERT, but nothing applies here.
The FORMAT function, but the input type must be a numeric or date/time/datetime value (that and it was introduced in SQL Server 2012, so not applicable to SQL Server 2008 R2 or older).

And nothing else seemed to work. I was hoping to find something more elegant than two REPLACE calls, but so far no such luck.

Also, just to mention, while not a pure T-SQL solution, this can also be accomplished via SQLCLR. And, there is a pre-done function that does this in the SQL# library (that I wrote) named String_TryParseToDecimal. This function is available in the Free version, and works in every version of SQL Server starting with SQL Server 2005:

SELECT SQL#.String_TryParseToDecimal('7.000,45', 'el-GR');

Returns:

7000.45000000000000000000


SQL Server Profiler is available for SQL Server 2008 Dev Edition? I just upgrade from SQL Server 2008 Express with advance services to Dev Edition. I wonder whether SQL Profiler is available for the Dev Edition?

Please let me know how to access that if it is present.

Yes, it is available in the installer for development edition. Without firing it up, I'm not sure if the installer has changed between 2008 and 2008R2 but in 2008 there are two options for installing client tools.

Management Tools (Basic) which installs SSMS, SQLCMD and Powershell support
Management Tools (Complete) adds Profiler, Database Tuning Advisor, Integration Services, Reporting Services.


What is a deterministic method for evaluating a sensible buffer pool size? I'm trying to come up with a sane way to understand if the max server memory (mb) setting is appropriate (either should be lower, or higher, or stay the way it is).  I am aware that max server memory (mb) should always be low enough to leave room for the operating system itself, etc.

The environment I'm looking at has several hundred servers; I need a reliable formula I can use to determine if the present size of the buffer pool is appropriate since RAM is costed per GB allocated to each server.  The entire environment is virtualized, and "physical" RAM allocated to a VM can easily be changed up or down.

I have a particular SQL Server Instance I'm looking at now with a PLE of 1,100,052 seconds, which equates to 12.7 days (the amount of time the server has been up).  The server has a max server memory setting of 2560MB (2.5GB), of which only 1380MB (1.3GB) is actually committed.

I've read several items including one by Jonathan Keheyias (post) and another by Paul Randal (post), and several others.  Jonathan advocates monitoring for a PLE below 300 per 4GB of buffer pool as being too low.  For the SQL Server Instance above, 300 * (2.5 / 4) = 187 results in a really really low target PLE below 300.  This instance has 290GB of SQL Server data (not including log files), and is only used for integration testing.  Assuming the last 12 days is representative of typical usage for this server, I'd say the max server memory (mb) setting could be lowered.

At the other end of the scale, I have another integration-test server with a PLE of 294, that has a max server memory (mb) setting of only 1GB.  This server has only 224MB of SQL Server data not including logs, and is running some BizFlow databases.  This server might benefit from a higher max server memory (mb) setting.

I'm thinking a good starting place for targets that might be assigned too much memory could include looking at:

SELECT
    RamMB = physical_memory_in_bytes / 1048576
    , BufferPoolCommittedMB = bpool_committed * 8192E0 / 1048576
    , BufferPoolCommitTargetMB = bpool_commit_target * 8192E0 / 1048576
    , PercentOfDesiredSizeMB = CONVERT(INT,(CONVERT(DECIMAL(18,2),bpool_committed)
                            / bpool_commit_target) * 100)
FROM sys.dm_os_sys_info;

If BufferPoolCommitTargetMB / BufferPoolCommittedMB is greater than 1, the server is not using the entire buffer pool.  If the machine in question also has a PLE of greater than "x" then it might be a good candidate for a decrease in max server memory (mb).

Since the Buffer Manager:Lazy writes/sec performance counter tracks the number of times SQLOS has written pages out to disk between checkpoints due to memory pressure, this might be another good thing to look at.

DECLARE @WaitTime DATETIME;
SET @WaitTime = '00:00:15';
DECLARE @NumSeconds INT;
SET @NumSeconds = DATEDIFF(SECOND, 0, @WaitTime);
DECLARE @LazyWrites1 BIGINT;
DECLARE @LazyWrites2 BIGINT;

SELECT @LazyWrites1 = cntr_value
FROM sys.dm_os_performance_counters dopc
WHERE (
        dopc.counter_name LIKE 'Lazy writes/sec%' COLLATE SQL_Latin1_General_CP1_CI_AS
    )
    AND dopc.object_name = 'MSSQL$' + CONVERT(VARCHAR(255),
               SERVERPROPERTY('InstanceName')) + ':Buffer Manager';

WAITFOR DELAY @WaitTime;

SELECT @LazyWrites2 = cntr_value
FROM sys.dm_os_performance_counters dopc
WHERE (
        dopc.counter_name LIKE 'Lazy writes/sec%' COLLATE SQL_Latin1_General_CP1_CI_AS
    )
    AND dopc.object_name = 'MSSQL$' + CONVERT(VARCHAR(255),
               SERVERPROPERTY('InstanceName')) + ':Buffer Manager';

SELECT LazyWritesPerSecond = (@LazyWrites2 - @LazyWrites1) / @NumSeconds;

The above code assumes the server is under load during the 15 seconds it takes to run, otherwise it will report 0; which might be a misleading false-negative.

Should I also look at PAGELATCHIO_* wait stats or some other wait type as an indicator of memory pressure, or lack thereof?

My question is, how can I reliably determine a "good" target value for PLE and max server memory (mb)?

As you already know there is No general formula to calculate max server memory you can do some quick maths and reach to a value but still you would need help of Perfmon counters at last to monitor memory usage and change accordingly. I know below general formula and I use it as well. I learned this formula from This Link

For SQL Server 2005 to 2008 R2

Please note from SQL Server 2005 to 2008 R2 max server memory only controls buffer pool. So max server memory configuration is bit tedious here and involves few calculations

Leave 2 G memory straight away for Windows OS.
Of course system would have antivirus running. Please leave 1.5G for Antivirus. Please note Mcafee and SQL Server do not go hand in hand so make sure you leave enough for it.
You can also check perfmon counter Perfmon Process-> Private bytes and Working Set to monitor memory usage by AV and other small applications running on SQL Server box

Consider the memory requirements of the drivers/firmwares.You have to derive it based on memory requirements by drivers installed on the system. RAMMAP tool can help
Consider the NonbPool (aka MTL or MTR) memory requirements by SQL Server.

select  sum(multi_pages_kb)/1024 as multi_pages_mb from  sys.dm_os_memory_clerks

+ Max worker threads * 2MB

+ Memory for direct Windows allocations approximately 0 to 300 MB in most of the cases but you may have to increase it if there are many 3 party components loaded in SQL Server process (Including linked server dll’s, 3rd party backup dll’s etc.)

+ If you are using CLR extensively add some additional memory for CLR.
Consider the memory requirement by jobs (Including replication agents, Log shipping etc. )  and packages that will run on the server. It can very from MB's to GB's according to number of jobs running. For medium sized server you can take it as 250 MB
Make sure there is good enough free space for operating system.

Approximately (100 MB for each GB till 4G) + (50 MB for each additional GB  till 12GB) + (25 MB for each additional GB till your RAM size)
Other memory requirements.

If you have any other memory requirement specific to your environment.

Max server memory=  Total physical memory  – (1+2+3+4+5+6+7)

I have not included memory configuration for SSIS.SSRS,SSAS you would also need to subtract memory required by these services from total physical server memory.

After you have configured above you need to monitor following counters

  SQLServer:Buffer Manager--Page Life Expectancy(PLE):
  SQLServer:Buffer Manager--CheckpointPages/sec:
  SQLServer:Memory Manager--Memory Grants Pending:
  SQLServer:memory Manager--Target Server Memory:
  SQLServer:memory Manager--Total Server memory

For SQL Server 2012/2014.

From SQL Server 2012 onwards setting up max server memory has become easy. Because now max server memory almost accounts for all memory consumption. Max server memory controls SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and CLR memory (basically any “clerk” as found in dm_os_memory_clerks). Memory for thread stacks, heaps, linked server providers other than SQL Server, or any memory allocated by a “non SQL Server” DLL is not controlled by max server memory.

You can allocate 75-80% to SQL Server and then use perfmon counters to monitor memory usage. In SQL Server 2012 few perfmon counters have been deprecated. Buffer manager counter is deprecated you must use memory manager counter

  SQL Server: Memory Manager-- Target Server Memory (KB)
  SQL Server: Memory Manager--Total Server Memory (KB)
  SQL Server: Memory Manager- Free Memory (KB)
  SQL Server: Memory Manager--Database Cache Memory (KB)

On value of PLE, I have used formula by Joanthan and fortunately it has worked for me.


MySQL Internals: SQL Execution Flow This is a descriptive question regarding MySQL SQL flow architecture. I need a flow chart on the complete steps regarding MySQL SQL flow using the following components.
eg the sequence an UPDATE query goes through from memory to disk. And considering a crash at every stage how 'durability' is maintained.

innodb_log_buffer_size
log_buffer
Double write buffer
ib_logfile0, ib_logfile1 (redolog)
ibdata (Single tablespace or multiple tablespace,innodb_file_per_table=1)
binary log (WAL)
general query log (assuming enabled)

I explored and got few information but those are not sufficient and not explained clearly

MySQL Internals

XtraDb-InnoDB Internals

For Example:

First data will be written in buffer pool which contains the page cache of tables that are used recently.
If it is a READ request it is done in foreground like first it checks if the result is available in buffer pool , otherwise it hits the disk
If it is a WRITE request, the change will be written in buffer pool of table.ibd file and then to ib_logfiles .
After innodb_flush_log_at_trx_commit the changes are flushed to disk (table.ibd).

After that I don't know OR I am wrong at the initial 4 steps or missing something. Pardon me as I didn't got any reading material online on this.
At what stage it will be written in binary log (contains both committed and uncommitted transaction) or general log (contains everything including SELECT and unsuccessful queries). No idea.

Note1: Not interested in Optimizer, Parser, Other Storage Engines

Note2: Kindly do not mark this as too broad, A short precise answer covering the 7 components mentioned will be enough and really helpful for many in future

There isn't a clear-cut flow chart online for this, but if you want to understand how it all comes together for InnoDB, one of the best explanations I have seen are at M18 Deep Dive InnoDB Transactions and Write Paths. The slideshow is available at https://mariadb.org/wp-content/uploads/2018/07/Deep-Dive_-InnoDB-Transactions-and-Write-Paths.pdf

That presentation is for MariaDB, but MySQL should be quite similar.

The presentation of overall architecture of MySQL 8.0 at https://www.percona.com/resources/webinars/mysql-80-architecture-and-enhancement is also quite clear.


Create a snapshot for a transaction? PostgreSQL has a feature to create a snapshot for a transaction using:

SET TRANSACTION SNAPSHOT snapshot_id

Does SQL Server have a similar feature?

I have an application program that uses a SQL Server backend.  It invokes two read-only queries Q1 and Q2 using separate connections.  This means they do not execute in the same transaction.  I need to ensure both connections see the database in the same precise state, as would be afforded via the SET TRANSACTION SNAPSHOT statement in PostgreSQL.

How can I ensure both Q1 and Q2 see the database in a consistent state, identical to both connections?

So there is a way to do this using a couple of features that have probably never been used together in the real world: snapshot isolation and bound sessions.

Bound Sessions in particular is an extremely old, obscure and deprecated feature (although like many deprecated features it may never actually be removed).

Anyway the procedure sp_bindsession allows a session to participate in a transaction started by a different session.  And if both sessions are in SNAPSHOT isolation, then they will share a point-in-time consistent view of the database.

So setup:

create database testdb
alter database testdb set allow_snapshot_isolation on
go
use testdb
drop table if exists t
create table t(id int)

Then from one session:

--session 1
use testdb
if @@trancount > 0 rollback

set transaction isolation level snapshot
begin transaction

declare @token varchar(255)
exec sp_getbindtoken @token out
select @token

insert into t(id) values (1)

and from another session, after copying and pasting the bind token:

--session 2
use testdb
go

set transaction isolation level snapshot
declare @token varchar(255) = '7-`L>5B0-1[OgFb4FVTWU]5---/=AM--'

select @@trancount --returns 0

exec sp_bindsession @token

select @@trancount --returns 1

go

select * from t --returns a row

rollback --rolls back the transaction started in the other session

go

select * from t --no rows

But any real-world scenario, you should probably either use Multiple Active Result Sets and run the queries in the same session, or create a Database Snapshot


Stored procedure prepared statement no error and not working Here is my stored procedure:
BEGIN
#Procedure:     insertMetaTagIfNotPresent
#Parameters:    bkID    ID to find or NULL to create automatically
#               vcTag   Tag name to assign, must be unique
#Notes:         Creates metatag table if it doesn't exist
#History:       2021/05/04  Written by Simon Platten

    DECLARE txtSQL TEXT;
    #Create SQL insert string
    SET txtSQL := 'INSERT INTO metatags (';
    #Has a primary key value been supplied?
    IF NOT biPK IS NULL THEN
    #Yes, use it!
      SET txtSQL := CONCAT(txtSQL, '`biPK`,');
    END IF;
    SET txtSQL := CONCAT(txtSQL, '`vcTag`) VALUES (');
    IF NOT biPK IS NULL THEN
        SET txtSQL := CONCAT(txtSQL, biPK, ',');
    END IF;
    SET txtSQL := CONCAT(txtSQL, '\'', vcTag, '\');');
    #Create prepared statment
    SET @SQL := txtSQL;
    PREPARE stmt FROM @SQL;
    DEALLOCATE PREPARE stmt;
    SELECT txtSQL;
END

Its very much a work in progress and not finished.  biPK (parameter 1) is optional and if NULL then it isn't inserted into the query.
I've added SELECT txtSQL so I can see if the final SQL created is valid.  No error occurs and the SQL generated is valid, but no record is inserted into the table.
I have copied the generated insert SQL and pasted into HeidiSQL and it works, however calling the stored procedure doesn't end up with any new record being created.
This is running on MariaDB version 10.5.9.0 on Windows version 10.0.19041.928
What am I missing?
[Edit] modifications, but still doesn't work:
BEGIN
#Procedure:       insertMetaTagIfNotPresent
#Parameters:      _bkID ID to find or NULL to create automatically
#                 _vcTag      Tag name to assign, must be unique
#Notes:           Creates metatag table if it doesn't exist
#History:         2021/05/04  Written by Simon Platten

      DECLARE txtSQL TEXT;
      #Create SQL insert string
      SET txtSQL := "INSERT INTO metatags (";
      #Has a primary key value been supplied?
      IF NOT _biPK IS NULL THEN
      #Yes, use it!
        SET txtSQL := CONCAT(txtSQL, "`biPK`,");
      END IF;
      SET txtSQL := CONCAT(txtSQL, "`vcTag`) VALUES (");
      IF NOT _biPK IS NULL THEN
            SET txtSQL := CONCAT(txtSQL, "'", _biPK, "',");
      END IF;
      SET txtSQL := CONCAT(txtSQL, "'", _vcTag, "');");
      #Create prepared statment
      SET @SQL := txtSQL;
      PREPARE stmt FROM @SQL;
      DEALLOCATE PREPARE stmt;
      SELECT txtSQL;
END

Doh, I looked at numerous examples online, and these were pretty much the same as I used in my example, however I did think I was missing something, then I found another example which proved that I was missing:
EXECUTE stmt;

So the complete prepared statement looks like:
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


Understanding postgres txid_current and pg_locks I am trying to get a list of pg_locks relevant to the current transaction

eg

> BEGIN;
BEGIN
> ALTER TABLE ... ;
ALTER TABLE
> select locktype,virtualxid,transactionid,mode,relation from pg_locks;
  locktype  | virtualxid | transactionid |        mode         | relation
------------+------------+---------------+---------------------+----------
 relation   | [NULL]     |        [NULL] | AccessShareLock     |    11695
 virtualxid | 2/24699    |        [NULL] | ExclusiveLock       |   [NULL]
 relation   | [NULL]     |        [NULL] | AccessExclusiveLock |   801091
(3 rows)

Ok so what I think of as "my" lock here is the last one, the AccessExclusiveLock

But it has no transactionid or virtualxid.

If there is other activity in the db I will see other locks too in this list.

How do I filter out just the locks belonging to my current transaction, if they have null transaction ids?

I found an answer.

There were two parts to this.

Firstly, what is important is virtualtransaction rather than virtualxid. Even though virtualxid and transactionid are null for everything I care about, they all share a virtualtransaction id.

Secondly, although txid_current() returns a transactionid, which seems like it wouldn't help because my locks all have null for that, we can still use it to get what we want.

What happens is when you call txid_current() postgres assigns a lock with the transactionid returned by that function, which we can see if we query pg_locks. This lock will share a virtualtransaction id with everything else in the current transaction.

For example:

> BEGIN;
BEGIN
> SELECT locktype,transactionid,virtualtransaction,mode FROM pg_locks;
  locktype  | transactionid | virtualtransaction |      mode
------------+---------------+--------------------+-----------------
 relation   |        [NULL] | 3/38672            | AccessShareLock
 virtualxid |        [NULL] | 3/38672            | ExclusiveLock
(2 rows)

> SELECT txid_current();
 txid_current
--------------
       355056
(1 row)

> SELECT locktype,transactionid,virtualtransaction,mode FROM pg_locks;
   locktype    | transactionid | virtualtransaction |      mode
---------------+---------------+--------------------+-----------------
 relation      |        [NULL] | 3/38672            | AccessShareLock
 virtualxid    |        [NULL] | 3/38672            | ExclusiveLock
 transactionid |        355056 | 3/38672            | ExclusiveLock
(3 rows)

So now from this we can get the virtualtransaction id shared by everything in the current transaction, and filter on that.

In other words:

SELECT
    *
FROM pg_locks pl
WHERE
    virtualtransaction=(
        SELECT virtualtransaction FROM pg_locks
            WHERE
                transactionid::text = (txid_current() % (2^32)::bigint)::text
                -- compare int to xid, see https://dba.stackexchange.com/a/123183/10371
                AND locktype='transactionid'
        LIMIT 1
    )

...will return only locks relevant to the current transaction.


SQL self join with condition I have table like:

bu_id   bu_name     parent_id
1       DefaultBU   NULL
601     bu1         1
602     bu2         601
603     bu3         NULL
604     bu4         603

I want to retrieve the record which bu_id is 1 and the records which has parent_id as selected records bu_id, like:

for 1 bu_id:

I need resuts like:

bu_id   bu_name     parent_id
1       DefaultBU   NULL
601     bu1         1
602     bu2         601

I have tried with:

SELECT b.bu_id, b.bu_name, b.parent_id
FROM `business_unit` AS a
INNER JOIN `business_unit` AS b
WHERE (
b.parent_id = a.bu_id
)

Resuts:

bu_id   bu_name     parent_id
601     bu1         1
602     bu2         601
604     bu4         603

In SQL Server you can do this with a recursive CTE, but unfortunately this isn't supported by mySQL. Instead, you need a recursive function/sp which calls itself to get the full tree, such as the following:

set @@session.max_sp_recursion_depth=255;

delimiter $$

create procedure get_bu_tree (
   in parent int
)
begin
    create table if not exists temp_bu_tree_ids (id int);
    set @i = (select count(1) from temp_bu_tree_ids);
    if @i = 0 then insert into temp_bu_tree_ids values (parent); set @i = 1; end if;

    insert into temp_bu_tree_ids (id)
    select bu_id
    from bu
    inner join temp_bu_tree_ids as t
    on bu.parent_id = t.id
    where bu_id not in (select id from temp_bu_tree_ids);

    if (select count(1) from temp_bu_tree_ids) > @i
    then
        call get_bu_tree ((select bu_id from bu where parent_id = parent));
    else
        select bu.*
        from bu
        inner join temp_bu_tree_ids as t
        on bu.bu_id = t.id;

        drop table temp_bu_tree_ids;
    end if;
end$$

delimiter ;

call get_bu_tree (1);

drop procedure get_bu_tree;

Note the call get_bu_tree (1); where 1 is the parent id for which you want to retrieve the children. Also note that this creates and drops a table (temp_bu_tree_ids), check for conflicts!


SQL Server 2000 Enterprise Manager - "Navigation to the webpage was canceled" error when trying to design a table While working in SQL Server 2000 Enterprise Manager on a remote server today, I wanted to look at a table's columns, so I found the table, right-clicked on it and selected "Design Table".

Instead of getting the table design view, I'm greeted with a white page with the following informational message:

  Navigation to the webpage was canceled

  What you can try:

  Refresh the page.

If I click "Refresh the page", I get another white screen with the following message:

  This program cannot display the webpage

  Most likely causes:

  You are not connected to the Internet.
  The website is encountering problems.
  There might be a typing error in the address.

I'm not sure who was tinkering around with the server, but I would love to know how to resolve this.

2011/04/19 update:  I checked, and KB961260 hasn't been installed.  The server is Windows 2003 R2, with SQL Server 2000 and Internet Explorer 8 for what it's worth.  I tried dropping security settings in IE to Low for Local and Trusted sites, which did not fix the issue.

We've also discovered that right-clicking on a table and selecting Open Table > Return All Rows gives the same error.  Designing and opening a view gives the same errors as above, but double-clicking on a view to open its properties lets us view the properties as normal.  We can also manage triggers, generate SQL scripts, etc. without issue.

Right-clicking on the first error page and checking its properties gives me this:

  res://ieframe.dll/navcancl.htm#http://%7b464ee255-fdc7-11d2-9743-00105a994f8d%7d/

Right-clicking on the second error page and checking its properties gives me this:

  res://ieframe.dll/dnserrordiagoff_webOC.htm#http://%7b464ee255-fdc7-11d2-9743-00105a994f8d%7d/

Most likely (or oddly?) a known issue after a Windows patch to do with IE zones because of your help settings.


Oracle bitand inconsistent behaviour I'm working on a legacy database using bitmaps for searching. I'm using the bitand function to find bitmaps that match a given bitmask but I'm getting the wrong results: the bitand between two numbers is returning a non-zero value when zero would be expected.

I'm using Oracle 11gR2 and I can reproduce the problem like this:

create table BITAND_TEST (BITMAP binary_double);

-- insert the bitmap of 2^57 in the table
insert into BITAND_TEST values (144115188075855872);

-- bitand(2^57, 2^50) evaluates to 0
select bitand(144115188075855872, 1125899906842624) from dual;

-- but in this statement it returns non-zero
select  *
  from  BITAND_TEST
  where bitand(BITMAP, 1125899906842624) <> 0; -- I'm using 2^50 here

Is this a type casting problem? Which types would you use to store a bitmap?

When you execute

select * from  BITAND_TEST;

you get 1,44115188075856E17.  It is not the same value you entered, you have lost some precision. Try this

select TO_CHAR (BITMAP, '9999999999999999999999') from  BITAND_TEST;

to see the difference. I get

 144115188075855870


Track my own custom metadata on Postgres tables and columns Is there any way to add my own metadata to tables and columns in Postgres, such as key-value pairs of short text?

I am migrating tables, columns, and data from another database system. That other system has properties defined on tables and columns not matched by Postgres. For example, in the other database system each table can be assigned a color for use in graphical display of the database structure such as CUSTOMER_ table being green and INVOICE_ table being red.

So I would like a way to keep track of those property settings within my Postgres database in such a way that my Java app may query for the custom metadata via JDBC.

SET attribute ?

I noticed:

ALTER TABLE … SET ( attribute_option = value [, ... ] )

…and…

ALTER TABLE … ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )

The documentation for ALTER TABLE does not really explain what these attributes do. Can I invent my own attribute names to be used as keys that map to my own metadata values?

Or is there some other approach to having Postgres remember my own custom metadata?

Comment

I suppose one workaround might be embedding key-value pairs as text with the COMMENT property on each table and column. Seems like kind of a hack, and kind of messy as I do often use COMMENT to document business rules as prose. But I suppose it would work. Any better way to remember per-table and per-column attributes?

Extended Properties of Microsoft SQL Server

Apparently MS SQL Server offers an "Extended Properties" feature for such meta-data. Described here on Stack Overflow and here on Microsoft doc.

Perhaps something like that in Postgres?

Additional tables

Of course I could define my own extra tables to store this meta-data. But I was hoping for something tied to the targeted tables and columns to be more obvious to those maintaining this app down road. No point in re-inventing the wheel if Postgres already provides me with a custom meta-data facility of some sort.

Usually the most portable way of doing this is to have your own metadata table, something like:

create table meta(
  table_name text not null,
  column_name text not null,
  attribute_name text not null,
  attribute_value text not null,
  primary key (table_name, column_name, attribute_name)
);

This approach works with any database
Access to metadata is done by standard SQL
Migration and backup is very easy
The attribute_value can be anything, you can declare it as byte[], text, json, jsonb, whatever you want...


Alias using "AS" and without using "AS" What is the difference in accessing a table with alias "AS" and without using "AS". Is there any performance enhancements?

For Example :

SELECT S.ID,S2.Value FROM dbo.Sample AS S
INNER JOIN dbo.Sample2 AS S2 ON
S.ID = S2.ID;

SELECT S.ID,S2.Value FROM dbo.Sample S
INNER JOIN dbo.Sample2 S2 ON
S.ID = S2.ID;

What will be difference between 1 and 2 ?


Unpack your JSON to separate rows using
SELECT json_unpacking.foo,
       values.value,
       ROW_NUMBER() OVER (PARTITION BY foo) value_number
FROM json_unpacking
CROSS JOIN json_each(json_unpacking.bar) values

Then combine it back into needed row structure using grouping and conditional aggregation.


Get results ordered by a specific column in a looping way I have heavy difficulties in getting rows ordered in an special way. Two tables are part in this:

Table 1 - WORDS:

id | char | word | categoryid (<- id of a row in CATEGORY)

Table 2 - CATEGORY:

id | name

Now I like to get a list of 80 WORDS (LIMIT 0,80) randomly ordered by the categoryid. BUT before there is any new chosen row with an already chosen categoryid all other categoryids should have chosen already. It is not easy to explain, see the following example:

Possible result (CATEGORY has 5 rows with 1 < id <= 5):

id    |  char    | word    | categoryid  | name
3        f         fire      3             nouns
6        n         nice      5             adjectives
2        t         three     4             numbers
4        e         europe    1             places
5        s         spanish   2             languages
1        m         man       3             nouns
7        b         bad       5             adjectives
9        t         two       4             numbers
8        a         alaska    1             places
10       t         turkish   2             languages
...

As you can see the order of the categoryid column should loop.

The used words should be randomly chosen and therefore the list should always display different words ordered always differently but in this kind of way.

This task is way to hard for me, I cannot do it in MySQL only, but i really would like to. So if there is anyone genius, please give me advises ;-)

As I've been asked for an example for the SQL Server version, something like:

WITH NumberedWithinCategories AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY NEWID()) AS rownum
  FROM dbo.Words
)
SELECT TOP (80) *
FROM NumberedWithinCategories AS w
JOIN dbo.Categories AS c
  ON c.ID = w.CategoryID
ORDER BY w.rownum, NEWID();

Of course you'd filter the outer query to the columns you want once you've checked the logic...


percona cluster 5.6 - handshake with remote endpoint failed, but endpoint is not part of config I set up a cluster of 3 database nodes using a percona 5.6 cluster. Everything works, all nodes are replicated. I configured SSL for state transfer. The cluster recently changed its topography, because we had to migrate 2 nodes to new machines. The cluster was completely restarted recently with configuration files that only contain the 3 current cluster nodes node3, node8, nodeA (don't mind the naming). Earlier we also had node1 but this machine left the cluster and does not run any mysql any more.

Now the problem: In the log files of node3 and node8 I find every couple of seconds an error log that looks like this:

2015-04-20 11:06:24 22546 [ERROR] WSREP: handshake with remote endpoint ssl://<PUBLIC.node1.IP>:57953 failed: 1: 'End of file.' ( )

where <PUBLIC.node1.IP> is the address of the old node1, which is by no means any more part of the cluster. I looked through the my.cnf config files of all cluster nodes, but all traces of node1 are gone from it. How does it come the node3 and node8 try to contact the outdated node? Note, that this is after a complete bootstrapping of the cluster starting from nodeA, which does not show the strange error messages in the log.

I found an error description of a very similar error here: https://mariadb.com/kb/en/mariadb/strange-ssl-messages-in-error-log/

There it was suggested, that this may be due to a broken network configuration where nodes could only ping from one node to the other, but not the reverse direction. But the given reason/solution does not fit in my case, since all nodes can ping each other fine. Also, it does not explain at all the fact, that my cluster nodes try to contact a node that is not part of the cluster any more.

Here is the relevant part of the congiguration file (from node8)

### PERCONA CLUSTER STUFF
wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_cluster_address=gcomm://<PUBLIC.nodeA.IP>,<PUBLIC.node3.IP>,<PUBLIC.node8.IP>
#wsrep_cluster_address=gcomm://
wsrep_node_address=<PUBLIC.node8.IP>
wsrep_slave_threads=8
wsrep_sst_method=xtrabackup-v2
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_cluster_name=betdata_cluster
wsrep_provider_options=gcache.size=3G;socket.ssl_cert=/etc/mysql/cert.pem;socket.ssl_key=/etc/mysql/key.pem
wsrep_sst_donor=node3,nodeA
wsrep_node_name=node8
wsrep_sst_auth=sstuser:blahblubb
wsrep_sst_receive_address=<PUBLIC.node8.IP>:14444
query_cache_size=0
query_cache_type=0

Any ideas?

The cluster is working correctly and was started freshly just before I get this error log. I want to understand what is going on. Galera does accept connections fine, also now. All nodes are up and running. Clustercheck says they are OK. The error also is about a node IP address that is not any more part of the cluster. I guess my problems comes from some config that I did not change and don't know about.

I feel a bit stupid, but the answer to that strange behavior was simple:

The old node1 cluster node was trying to connect to the cluster again after a reboot. I forgot to uninstall the mysql installation on that node and instead only stopped the mysql server. That was well as long the system did not restart mysql.

To fix the situation I removed the old config files on node1 and also uninstalled the percona cluster packages.

Everything is peachy now.


How do I create a file from a result set? I'm using Oracle 11g (I think)

I'm trying to create a flat file (CSV or TXT) from a result set but am struggling on where to even start. It seems like I have to create a stored proc and use UTL_FILE. After doing some research, I have two questions:

Where does the file get created? According to this question I need to get access to the Oracle user directory, but where is that on a Windows and Linux environment? I have to test on Windows , and the script will eventually be on a Linux environment.
What would be the basic format of a SQL script to create the aforementioned file, and load data into it from a fairly basic SELECT query?

There's two routes to go through.

Firstly, Server-side. This is where a process on the machine hosting the DB will write the data to its file system using UTL_FILE. A DIRECTORY entry needs to be created in the database by a DBA pointing to a directory on the file system which can be written to by the OS process running the database (generally 'oracle')

Secondly, client-side. This is where a client program (eg SQL*Plus, Java, Python, Excel...) connects to the database, pulls the required data across to the client and writes it to a file on the client's file system.

You can have the client and server on the same machine, or a shared directory/file system that is accessible to both a client and server (one side needs to be able to read/write, the other can just be read).

You need to decide which best fits your situation, including

where you want the file to end up
whether the task will be invoked manually or through a scheduler/automation
how to manage and deploy changes to the script/code that generates the file


What is the MS_PerfDashboard schema in msdb? We just migrated an old reporting server (running on 2005) to 2012 and some of the database performance monitoring jobs have started failing. I looked into them and found that they use a couple of functions in the msdb in a schema called MS_PerfDashboard, but the schema is missing on the new server. I had a quick browse online and found that people use this for performance monitoring, but what I couldn't find is where the functions came from originally. Are they part of an old SQL version? A plugin? Or just a script of functions that someone came up with?

You can find the Performance Dashboad here:
http://www.microsoft.com/en-us/download/details.aspx?id=22602

From memory there is a setup.sql file which should recreate the objects for you.


Datatype for a Persisted Computed Column? Should I Convert/Cast When Creating It? What is the Datatype for a Persisted Computed Column?

(a) Does SQL Server automatically infer it?

(b) Also is better to cast it as a datatype, so SQL does not spend time to find what data type it is?

create table dbo.CustomerTransaction
(
   CustomerTransactionId int primary key identity(1,1),
   CustomerId int,
   Price decimal(10,2),
   Quantity int,
   TotalAmount as CustomerId * Price PERSISTED  -- should I cast as cast(CustomerId* Price as decimal(10,2) ??
)

Information turned into a Community Wiki answer from this question on Stack Overflow: What is the data type of a computed column?

It's determined by the value with the highest datatype precedence.

You can use sql_variant_property to determine what datatype a literal expression is as per my answer here. e.g. 2147483648 is interpreted as numeric(10,0) rather than bigint.

In addition, if you want to force a specific data type instead of relying on data type precedence, you can use CONVERT or CAST in the computation to force it (assuming all potential outcomes are compatible with the type you choose). This can be very useful in cases where, by default, you end up with a wider data type than you intended; the most common use case I've seen is when you end up with an INT instead of, say, a BIT:

Active1 AS CASE WHEN something THEN 1 ELSE 0 END,
Active2 AS CONVERT(BIT, CASE WHEN something THEN 1 ELSE 0 END)

In this case Active1 is an INT (4 bytes) while Active2 is a BIT (1 byte - or less, potentially, if it is adjacent to other BIT columns).


database normalisation on object that is attached to another object that can change I am building an inventory management application for equipment attached to machines. The app is to allow scheduling and reporting on maintenance and service of the equipment. The machines have make/model information and also site location lookups. There is also daily task information that is attached to the equipment which gets marked as completed and this is used to produce reports. My app entities look like the following:

Location
Sub-location (fk to location)
Make
Model (fk to make)
Machine (fk to model, fk to sub location)
Equipment (fk to machine)
Task (fk to equipment, read below for more info on fk's)

I need to produce reports and calendars based on task information and would like to see tasks by make/model and also site location in separate reports.

The problem is that a task may be completed while the equipment is at Location A and then the equipment moves to Location B - the equipment might also be removed from the current machine and attached to a new machine. I need to allow my app to support this reality and give the user good historical reporting and future scheduling.

My current solution is as follows:

On the Task model, I have a fk to both site location and machine which is copied from the equipment and machine when the task is created. When the equipment location or machine is changed, I update all incomplete ie. completed tasks have their data frozen.

The benefit I see to this is that I can keep my queries simple without having to always join tasks to equipment and then machine to get location and machine/model/make info and I can also support equipment moving without having to introduce some history type table and a complicated join based on todays date etc.

Can anyone advise on pros/cons to this approach or a new approach?

I'll also add that this is a MySQL/PHP application where I am using laravel and eloquent ORM.

Since Make and Model is a hierarchical relationship they can exist in the same table.

You'll need a junction table to handle the many to many relationship between machine, equipment, and sub-location.

Your junction table will need to contain four columns; [SLID], [MID], [EID], [datetimestamp].

You can handle tasks in several ways - if one task always follows one piece of equipment then the link between Task and Equipment is fine, however, if the same task can use multiple pieces of equipment on multiple machines then you would want include a task id [TID] field in your junction table.

The junction table will grow quite large so you might want to partition it by month and year, and also experiment with the indexes to make sure that they cover the reporting requirements.

Also, since you're using an ORM I'm going to make an assumption that the idea of using GUIDs as IDs has come up, or will at some point - don't create a clustered index (primary key) on a guid column, if you must use a guid to make it easier for the application to do its thing, then do create an integer id column as your primary keys.

Example ERD - This is what I might start with given the requirements, and modify it from there as needed.


How to troubleshoot orm queries interfering with other applications Well, it's hypothetical scenario but what I'm trying to understand is the path to go from a post-morten log (say, an SQL Server Profiler trace) to identify the code on a ORM situation. To make it not too vague, consider an scenario like that:

SQL Server 2008
Entity Framework as a ORM

So, in that scenario, what is the common path to a DBA (which is also an VB.Net developer) make from the log to diagnosing which code(s) (in this case, Linq queries) are creating the trouble?
In this case, the app is ok but is affecting other apps response time using the same DB/server.

That would be absurdly different from a Java+Hiberate process?

EDIT: I want to understand the path from the trace to the culprit metaquery. If the app have SQL in it, this means that an "Find in Files" session (maybe with some regex, in extreme cases) could reduce the inspect task' targets to some dozen suspects instead of tenths or even hundreds of source files.
Using an ORM, how to get to that stage using an ORM (in this case: EF)?

I would strongly suggest metadata in the connection in order to trace back to the application.  In the connection string, there is an Application Name.  There is also session data which can be used in the form of CONTEXT_INFO

https://stackoverflow.com/questions/323494/sql-server-modifying-the-application-name-property-for-auditing-purposes

Of course all this requires application changes, but it is good for tracing and auditing in general, so baking it in from the beginning is really useful.


Change the Existing Topology of a MySQL Replication Setup I want to be able to edit the topology of an existing replication, is that possible?

For example if I have a star topology, I want to edit it to make it a ring one.
How can I do that?

I'm thinking maybe first I need to reset all masters and servers. If I have the following topology:

    a -> b
    b -> c
    c -> a

and I want to transform it to:

    a -> c
    b -> c

I tried change master but it didn't work well because the old topology still exists.

Based strictly on your question, the topology you want is not possible.  Why?

Here is what you presented (call it Topology#1)

    a -> b
    b -> c
    c -> a

and you want it to be (call it Topology#2)

    a -> c
    b -> c

Since ServerC cannot execute CHANGE MASTER TO with multiple host/port combinations, Topology#2 is mechanically impossible.

I have addressed this as to why IT IS NOT POSSIBLE:

Feb 03, 2012 : One slave, multiple masters MySql
May 15, 2011 : Is Multi Master Single Slave possible in mySQL DB? (My ServerFault Post)

Now, it you want to turn Topology#1 into this (call it Topology#3)

    a -> b
    a -> c

this is quite possible. Here are the steps:

STEP01) On ServerB, run STOP SLAVE;

STEP02) On ServerC, run SHOW SLAVE STATUS\G and check to make sure Exec_Master_Log_Pos is no longer updating.

STEP03) On ServerB, run SHOW SLAVE STATUS\G.

STEP04) Record Relay_Master_Log_File (call it RMLF) and Exec_Master_Log_Pos (call it EMLP) from STEP03 (For the sake of example, let RLMF='mysql-bin.000345' and EMLP=98765)

STEP05) On ServerC, run the following (Using RMLF and EMLP from STEP04):

STOP SLAVE;
CHANGE MASTER TO
master_log_file='mysql-bin.000345',
master_log_pos=98765;`
START SLAVE;

STEP07) On ServerB, run START SLAVE;

STEP08) On ServerA, run STOP SLAVE;

Going forward, have all INSERTs, UPDATEs, and DELETEs occur on ServerA only.


RMAN Not restoring data from restore set I'll start by saying it has been YEARS since I restored a database with RMAN. That said, I'm trying to restore data provided by a partner company for us to import into our database.

Our database isn't Oracle, but we have an Oracle instance set up for the transition. I was successfully able to restore the initial dataset to an empty database, but we needed a few more data tables that were included in the latest backup set. I would prefer not to delete the database, DBS, but will if this isn't resolved soon.

I have two backup pieces in my catalog, one for the control file and the other for the datafiles. Both have the same tag. When I try to restore the database to that tag, I get the results below.

RMAN> list backupset 14;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14      Full    18.87G     DISK        00:00:00     10-AUG-17
        BP Key: 31   Status: AVAILABLE  Compressed: NO  Tag: COLD_20170810
        Piece Name: C:\APP\ORACLE\ADMIN\DBS_20170810_0GSBHQCU_1_1.BK
  List of Datafiles in backup set 14
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full XXXXXXXXXXX 10-AUG-17 C:\APP\ORACLE\ORADATA\DBS\SYSTEM01.DBF
  2       Full XXXXXXXXXXX 10-AUG-17 C:\APP\ORACLE\ORADATA\DBS\SYSAUX01.DBF
  3       Full XXXXXXXXXXX 10-AUG-17 C:\APP\ORACLE\ORADATA\DBS\UNDOTBSP01.DBF
  4       Full XXXXXXXXXXX 10-AUG-17 C:\APP\ORACLE\ORADATA\DBS\USERS01.DBF
  5       Full XXXXXXXXXXX 10-AUG-17 C:\APP\ORACLE\ORADATA\DBS\DATABIG.DBF

RMAN> restore database from tag 'COLD_20170810';

Starting restore at 14-AUG-17
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/14/2017 16:00:59
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

RMAN>

I set up the run file below:

RUN {
    SET NEWNAME FOR DATAFILE 1 TO "C:\APP\ORACLE\ORADATA\DBS\SYSTEM01.DBF";
    SET NEWNAME FOR DATAFILE 2 TO "C:\APP\ORACLE\ORADATA\DBS\SYSAUX01.DBF";
    SET NEWNAME FOR DATAFILE 3 TO "C:\APP\ORACLE\ORADATA\DBS\UNDOTBSP01.DBF";
    SET NEWNAME FOR DATAFILE 4 TO "C:\APP\ORACLE\ORADATA\DBS\USERS01.DBF";
    SET NEWNAME FOR DATAFILE 5 TO "C:\APP\ORACLE\ORADATA\DBS\DATABIG.DBF";

    #SQL "ALTER DATABASE RENAME FILE ''G:\ORADATA\DBS\redo01.log'' TO ''C:\app\Oracle\ORADATA\DBS\redo01.log'' ";
    #SQL "ALTER DATABASE RENAME FILE ''G:\ORADATA\DBS\redo02.log'' TO ''C:\app\Oracle\ORADATA\DBS\redo02.log'' ";
    #SQL "ALTER DATABASE RENAME FILE ''G:\ORADATA\DBS\redo03.log'' TO ''C:\app\Oracle\ORADATA\DBS\redo03.log'' ";

    set until time "to_date('10-AUG-2017 00:00:00','DD-MON-YYYY HH24:MI:SS')";

    RESTORE DATABASE FROM TAG 'COLD_20170810';
    SWITCH DATAFILE ALL;
    RECOVER DATABASE;
}

And I get the same results.

Any guidance will be appreciated.

It sounds to me that you want to restore a newer backup of the previous incarnation, but the start of the current incarnation is before the time this backup was taken.

Check the output of list incarnation;. If the reset time of the current incarnation is before the time this backup was taken, you will need to reset incarnation to X;, where X is the incarnation key.


Why do async_network_io wait types occur? Last week something strange happened on our database. All of a sudden, the application blocked for our users who were not able to save new entities etc. After looking at the Activity Monitor of the SQL Server (2008 with compatibility mode 2005) I saw the following three entries:

After some time, the users got a connection timeout. When I killed the process 64, they could save normally again.

The problem is that the entities they tried to save during the block were inserted into the DB more than once (up to 3 times) even though there is code which should prevent this from happening (number column which has to be unique but without a constraint... the check happens in the code).

We use Entity Framework 6.0.

Does anyone of you know why and when these ASYNC_NETWORK_IO wait types occur and how to avoid them?
And what exactly do they mean?

ASYNC_NETWORK_IO somehow indicates that the client application isn't processing results as fast as SQL Server feeds them. This could be caused by an issue with the client application or with the network connection between the server and the client application.

Please refer to a post by Thomas LaRock

  The ASYNC_NETWORK_IO wait indicates that one of two scenarios are
  happening. The first scenario is that the session (i.e., SPID) is
  waiting for the client application to process the result set and send
  a signal back to SQL Server that it is ready to process more data. The
  second is that there may be a network performance issue.

or this post by Joe Sack

  As you may already be aware, the ASYNC_NETWORK_IO (seen in SQL 2005)
  and NETWORKIO (seen in SQL 2000) wait types are associated with either
  a calling application that is not processing results quickly enough
  from SQL Server or is associated with a network performance issue.

Since you are using entity framework this post by Brent Ozar  may be useful too

  Looking at the wait stats for these queries, I saw there was a lot of
  ASYNC_NETWORK_IO — often 1000+ milliseconds. That didn’t make any
  sense either! How can a query with so little CPU time and so few reads
  take so long to complete? It’s not like the application was asking for
  millions of rows and couldn’t consume the results fast enough.


What is making MySQL stay in strict mode New Mac

El Capitan 10.11.2

mysql-5.7.10-osx10.10-x86_64.dmg

I have to work with a database that has default dates "0000-00-00" but MySQL is in strict mode and chokes on them.

I can't find a way to switch off strict mode. I have
  [mysqld]
  sql-mode="";
in ~/my.cnf, but this makes no difference. I wonder if something is resetting it further up the food chain but I have no idea where to look. I have tried /usr/local/mysql/my.cnf. I have tried with the files called .my.cnf. The OS won't let me create /usr/my.cnf (an Apple security feature apparently).

Where else might I look; what else might I try?

Also some websites say sql_mode (underscore, not hyphen); which is correct?

Thanks

It turns out that the problem was ending the lines in my.cnf with semicolons. Leave them off, and /etc/my.cnf is read and stops strict mode.


Prevent gaps while using Identity/sequence object in sql server I have a table and I am trying to insert values in it. I want to prevent gaps in the primary key column. For primary column I have tried using Identity / sequence objects. I know that this is potential drawback of using Identity or sequence object; but is there any work around? I am open to using some other technique to prevent gaps.

Here is the table structure and sample code:

CREATE SEQUENCE Service_Ticket_Seq
 AS INTEGER
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 100
 CYCLE;

 CREATE TABLE Meats
(ticket_seq INTEGER NOT NULL primary key default (NEXT VALUE FOR Service_Ticket_Seq) ,
 meat_type VARCHAR(15) NOT NULL)

In order to create auto numbers without gaps we can use QUEUE object of MS SQL Server.

So the idea is that Queue supports concurrency and roll back of transactions. We use SEQUENCE object to generate continuous set of numbers and send them to QUEUE. After that we can receive them back from that Queue concurrently and if one of client's requests fails then our auto number stays in queue for the next available client.

So this way we achieve concurrency and unique stream of auto-numbers without gaps.

The only potential issue is that sometimes our auto numbers are going to be in the wrong time order, but it should be ok.


Delete statement not responding to Kill session command Four days ago a user ran the command below on a table with 400,000,000 rows. It's still running and the log file is increasing in size.

delete from [table-name]

This table does have a foreign key constraint which is not enabled for checking and I know that no rows exist in the other table.

The database is running with "Is Read Committed Snapshot On" enabled and in Simple recovery mode.

After this had run for a few hours I issued a kill session command, because we were running out of disk space for the log file. I added another log file to allow the system to continue to function.

The log file is continuing to grow and when I run kill session with statusonly it returns this message:

SPID 123: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

I am at a loss as to what to do about this query to get it to rollback and also just understanding what is going on, can anyone suggest what I can look at?

I am at a loss as to what to do about this query to get it to rollback and also just understanding what is going on, can anyone suggest what I can look at?

DELETE FROM [Some400MRowTable]

is expensive.  Every row you delete gets logged.  And when you killed the session that huge transaction has to rollback, which is even more expensive.  So normally you just wait, and eventually it rolls back.  Your alternative is to restore from a backup.

Note that this is one of the reasons Accelerated database recovery was added in Azure SQL Database and SQL Server 2019, which provides "Instantaneous transaction rollback" where rollback cost is not a function of the number of changes made by a transaction.


What does this SQLCMD statement do? I am new to SqlCmd. Can anyone give a short explanation of the following command?

SQLCMD -S VMLIVE-DATA\SQLSERVER -Q
"RESTORE DATABASE GIS_ENG FROM DISK = N'\\10.143.251.101\ClientData\Backup\DatabaseBackups\DB_ENG.bak'
WITH  FILE = 1,
MOVE N'DB_ENG' TO N'\\10.153.201.11\E$\SQLSERVER\Data\DB_ENG.mdf',
MOVE N'DB_ENG_log' TO N'\\10.153.201.11\E$\SQLSERVER\Data\DB_ENG.ldf',
NOUNLOAD,  REPLACE,  STATS = 10"

Sure it is saying to use SQLCMD.EXE connecting to the server instance VMLIVE-DATA\SQLSERVER, to run the script that will restore the GIS_ENG from a backup of the DB_ENG database, replacing the existing GIS_ENG if there is one.

The bulk of the text is the script -Q"RESTORE  ...  STATS=10".

The SQLCMD and RESTORE DATABASE parameters are define in the SQL Server documentation.

SQLCMD.EXE - http://technet.microsoft.com/en-us/library/ms162773.aspx

RESTORE DATABASE - http://technet.microsoft.com/en-us/library/ms186858.aspx

By default SQLCMD uses Trusted Connections (describe in the SQLCMD link) so it requires this to be run by a login with rights to restore a database.


Oracle XE: Transfer Database from Linux to Windows our professor gave us a linux VM with a Oracle XE 11g Database.

I want to transfer this Database to my Windows installation because I dont't want to boot up the VM for every exercise.
Everything should be transferred. Users, Permissions, Procedures, Tables, Data, and so on.

How can I do this.

I already tried to use the "Backup Database" and "Restore Database" buttons but I don't know where the backup is placed and where I have to put the backup files on my windows machine.

Thanks...

You should be able to solve this with a full export and import of the database.

Run a full export of the database on the linux VM
Copy the dump file to your Windows machine
Do a full import to the database on the Windows machine.

I believe Oracle reccomends the use of Oracle Data Pump:  expdp and impdp on Oracle 11g and up, rather than the traditional imp and exp tools.

Oracle Data Pump is well documented: http://docs.oracle.com/cd/E11882_01/server.112/e16536/part_dp.htm


Get data from 3 tables using UNION ALL I have these tables to which I am doing a query to get data from all the tables
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=e8d847e118b372071e42c685e9e5ad72
In the sample, there is not a lot of data but I am looking for a way to paginate the data results of the UNION ALL query.
select
    C.EXID as CAMERA_ID,
    U.EMAIL,
    TRIM(CONCAT(U.FIRSTNAME, ' ', U.LASTNAME)),
    ARC.TITLE,
    ARC.EXID,
    ARC.CREATED_AT,
    ARC.FROM_DATE,
    ARC.TO_DATE,
    null as EMBED_CODE,
    ARC.FILE_NAME,
    ARC.FRAMES,
    ARC.URL,
    ARC.PUBLIC,
    ARC.STATUS,
    ARC.TYPE,
    null as EXTRA
from
    PUBLIC.ARCHIVES as ARC
left join USERS U on
    ARC.REQUESTED_BY = U.ID
left join CAMERAS C on
    ARC.CAMERA_ID = C.ID
union all
select
    C.EXID as CAMERA_ID,
    U.EMAIL,
    TRIM(CONCAT(U.FIRSTNAME, ' ', U.LASTNAME)),
    TL.TITLE,
    TL.EXID,
    TL.INSERTED_AT,
    TL.FROM_DATETIME,
    TL.TO_DATETIME,
    null as EMBED_CODE,
    null as FILE_NAME,
    null as FRAMES,
    null as URL,
    null as PUBLIC,
    TL.STATUS,
    null as type,
    TL.EXTRA
from
    PUBLIC.TIMELAPSES as TL
left join USERS U on
    TL.USER_ID = U.ID
left join CAMERAS C on
    TL.CAMERA_ID = C.ID
union all
select
    C.EXID as CAMERA_ID,
    U.EMAIL,
    TRIM(CONCAT(U.FIRSTNAME, ' ', U.LASTNAME)),
    COMP.EXID,
    COMP.NAME,
    COMP.INSERTED_AT,
    COMP.BEFORE_DATE,
    COMP.AFTER_DATE,
    COMP.EMBED_CODE,
    null as FILE_NAME,
    null as FRAMES,
    null as URL,
    COMP.PUBLIC,
    COMP.STATUS,
    null as type,
    null as EXTRA
from
    PUBLIC.COMPARES as COMP
left join USERS U on
    COMP.REQUESTED_BY = U.ID
left join CAMERAS C on
    COMP.CAMERA_ID = C.ID;

I'm afraid that there's no way that you can avoid using a UNION [ALL] in this case. Your timelapses, compares and archives tables are connected to users and to cameras but not to each other, so in order to link them, we have no choice but to use a UNION.
I played around a wee bit (with a subset of the fields) and I came up with this which may help (all the code below is in a fiddle here):
WITH core AS (
  SELECT
    'Archives' AS tab_name, a.id, a.exid, LEFT(a.title, 10) AS title, a.camera_id, a.requested_by
  FROM archives a
  UNION ALL
  SELECT
    'Compares', c.id, c.exid, RIGHT(name, 10), c.camera_id, c.requested_by
  FROM compares c
  UNION ALL
  SELECT 'Timelapse', t.id, t.exid, t.title, t.camera_id, t.user_id
  FROM timelapses t)
SELECT * FROM core;

Result:
tab_name    id  exid    title   camera_id   requested_by
Archives    1   concre-r6ys     Concrete P  1   1
Archives    2   201809-27xn     2018-09-26  1   1
Archives    3   201902-htu9     2019-02-01  4   1
...
...
...
Archives    9   spark-kyzrq     Sparks Bui  1   1
Archives    10  201611-luw8     2016-11-06  2   4
Compares    1   compa-ylxwctg   o 29th Oct  1   2
Compares    2   test-hroqe      test        2   3
...
...

So, we can see that in the combined data, we have a way of getting at the source table name - by selecting it as a TEXT string at the beginning.
You can add in users info (like email, firstname, lastname) and cameras by JOINING to this "core" - see fiddle.
You can search now by doing something like this (again, see fiddle):
...
... core CTE query
...
)
SELECT * FROM core
WHERE title LIKE '%Pond%';

Result:
tab_name    tab_id         exid     title      camera_id    requested_by
Timelapse        1  longp-mvxla     Long Ponds         1               2

So, now we know that the photo with the title "Long Ponds" is in the Timelapse table and it's id in that table is 1.
A few words of advice:

I would consolidate my (photograph) tables - this will make matters considerably easier. There should be some combination of fields that is UNIQUE - and you can keep the tab_name field (and use it to enforce uniqueness).

you have some field names (location, type...) as quoted identifiers. This should be avoided - use t_type... something with an underscore - which remains legible but isn't an SQL keyword.
If you decide to consolidate, you can have NULLs where there isn't a corresponding field in the other tables.

a note on performance: As you can see from the EXPLAIN (ANALYZE, BUFFERS) at the end of the fiddle, your UNION plan has more than twice as many operations as my more_info table - this is because I perform the JOIN on users and cameras only once for all the photos whereas you do it three times.
However you decide to do it - via sub-queries or CTEs, only doing it once is the better option. You could wrap your original UNIONs (without JOINs) in a SELECT and JOIN on that if you prefer... YMMV...

I know that this probably isn't the outcome you hoped for, but I hope this has helped a little - best of luck with your project - agus  slán go fóill a chara!


Select rows with repeat data within certain timeframe As a follow-up question to my previous question on difficult row categorisation, I have another problem which I'm sure will be easily solvable. After this I plan on reading a good book or two on databases and SQL but for the time being I need to overcome this brick wall.

The table in question has the following layout:

TaskID             -  -  -  1. On-site
DateStart         |         2. Return-to-base
DateEnd           |         3. Chargeable
Type -  -  -  -  -          4. Complaint
Reference                   5. Rebook
Requester
Approver
Cost
...

I want to select all "repeat" TaskID. TaskID is a unique ID but a "repeat" is classed as any two tasks that share the same Reference and where the DateEnd of each task is within a 30 day period, so, I'm thinking something along the lines of:

SELECT BOTH TaskID, Reference
FROM   Tasks
WHERE  Row1.Reference = Row2.Reference AND ABS(Row1.DateEnd - Row2.DateEnd) <= 30;

Obviously the above isn't valid but hopefully it illustrates how I want to select the data. I'm guessing it will involve a self-join but I have no clue where to start... having the query return both TaskIDs with the reference is ideal but if it only returns one TaskID then that's no problem.

I'm version of PostgreSQL that I'm using is 9.1.3, if that helps.

Thanks for your help.

how about this:

 SELECT t1.task_id, t1.reference
   FROM tasks t1
   JOIN tasks t2
        ON t1.reference = t2.reference
           AND abs(t1.end_date - t2.end_date) < 30;

Note this assumes that end_date is a date, and not a timestamp.  If a timestamp cast to date first.


DB Schema for Timesheet Hi I want to create a web based resource tracking system which will be used by my manager using php and html. The db i am using is MySql. I have already created tables for employee, project etc. I have to create table to store the weekly resource availability. For eg. an employee A is working in 2 projects this week, and the ratio is like 0.4 and 0.6 (combining both we get 1.00 so he is engaged in project this week completely). Next week he is working on 1 project and his availability for that project is 0.7(so the manager can put him on another project also). I have to store these information for next 20 weeks. I am not getting any logic to implement this idea in DB. The below screenshot is how my db looks like now. The dates are hard coded.

Also next week i need to delete the previous weeks information and add 21st week's information for an employee. I completely stuck with this. I saw this link database schema for timesheet but it is some what complex which deals with hours also. I just need the information on weekly basis. Can somebody help me to do this schema ?

I think this could help you.

I create this structure:

Employee
- EmpID (PK)
- EmpName
- TKID

Domain
- DomID (PK)
- DomName

Project
- ProjectID (PK)
- ProjectName
- ProjectStart
- ProjectEnd
- DomID (Asuming your projects are for Domains)

ProjectType
- ProTypeID (PK)
- ProTypeName

RTS
- ProjectID (PK)
- EmpID (PK)
- ProTypeID (PK)
- Active (PK)
- DateStart (PK)
- DateEnd

I fill the tables with the information you post on the screenshot (Only the RTS information is from 2015-06-01 to 2015-06-09).

Employees:

Domains:

Projects:

ProjectTypes:

RTS:

Query:

        SELECT
        E.EmpID,
        E.EmpName,
        E.TKID,
        D.DomName,
        P.ProjectName,
        PT.ProTypeName,
        ROUND(IFNULL((COUNT(DISTINCT CASE WHEN RTS.DateStart BETWEEN '2015-06-01' AND '2015-06-05' && RTS.Active IS TRUE THEN RTS.DateStart END)/5),0.0),1) AS '2015_06_[01-05]',
        ROUND(IFNULL((COUNT(DISTINCT CASE WHEN RTS.DateStart BETWEEN '2015-06-08' AND '2015-06-13' && RTS.Active IS TRUE THEN RTS.DateStart END)/5),0.0),1) AS '2015_06_[08-12]'
    FROM Employe as E
    LEFT JOIN RTS on (RTS.EmpID = E.EmpID)
    LEFT JOIN Project as P ON (P.ProjectID = RTS.ProjectID)
    LEFT JOIN Domain as D ON (D.DomID = P.DomID)
    LEFT JOIN ProjectType as PT ON (PT.ProTypeID = RTS.ProTypeID)
    GROUP BY E.EmpID;

Result:

The only problem with this is that you have to put the week date parameters you want to calculate. You can make a Stored Procedure that could read eStartDate and eEndDate variables and calculate the weeks between those dates, and all you have to do is make a Dynamic Query that concatenate every week between the parameters.

Example:

CALL SP_RTS('2015-06-01','2015-06-31',1);

Between 2015-06-01 and 2015-06-31 exists 4 weeks monday-friday, you'll need to concatenate 4 columns with those dates. I could help you but I dont know if my post if what are you looking for, if it is, let me know.


MySQL - trying to combine `GROUP_CONCAT` with `COUNT()` I am trying to combine GROUP_CONCAT with COUNT(), and failing.

I have the following data:

CREATE TABLE `tab1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `team` varchar(45) DEFAULT NULL,
  `name` varchar(45) DEFAULT NULL,
  `item` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `test`.`tab1` (`team`, `name`, `item`) VALUES ('team1', 'bob', 'car');
INSERT INTO `test`.`tab1` (`team`, `name`, `item`) VALUES ('team1', 'bob', 'car');
INSERT INTO `test`.`tab1` (`team`, `name`, `item`) VALUES ('team1', 'bob', 'phone');
INSERT INTO `test`.`tab1` (`team`, `name`, `item`) VALUES ('team1', 'john', 'car');
INSERT INTO `test`.`tab1` (`team`, `name`, `item`) VALUES ('team1', 'john', 'phone');
INSERT INTO `test`.`tab1` (`team`, `name`, `item`) VALUES ('team2', 'mark', 'car');
INSERT INTO `test`.`tab1` (`team`, `name`, `item`) VALUES ('team2', 'phil', 'phone');

This gives me:

1   | team1 | bob   | car
2   | team1 | bob   | car
3   | team1 | bob   | phone
4   | team1 | john  | car
5   | team1 | john  | phone
6   | team2 | mark  | car
7   | team2 | phil  | phone

I am trying to get to:

team1   | bob   | car(2), phone(1)
team1   | john  | car(1), phone(1)
team2   | mark  | car(1)
team2   | phil  | phone(1)

The closest I have managed is:

SELECT
A.team,
A.name,
GROUP_CONCAT(DISTINCT CONCAT(A.item, "(", B.count, ")") SEPARATOR ' , ') AS groupings
FROM tab1 A
JOIN (
    SELECT team, name, item, count(item) AS count
    FROM tab1
    GROUP BY team, name, item
    ) B
GROUP BY team, name

But this gives me

team1   | bob   | car(1) , phone(1) , car(2) , phone(2)
team1   | john  | phone(1) , car(1) , car(2) , phone(2)
team2   | mark  | car(1) , car(2)
team2   | phil  | phone(1) , phone(2)

I've tried every possible combination of GROUP BY I can think of, but it still doesn't work. What have I missed please?

You've already found your answer, but here are the reasons:

The reason you're getting duplicates is because you're joining tab1 with itself without criteria on the join, which is simply resulting in duplicates. The query can be pretty easily accomplished without the join, looking like this:

SELECT A.team, A.name, GROUP_CONCAT(A.count SEPARATOR ' , ')
FROM
   (SELECT team, name, CONCAT(item, '(', count(item), ')') AS count
    FROM tab1
    GROUP BY team, name, item) A
GROUP BY A.team, A.name;


How to split XML array into separate rows (while upholding consistency) I am working on the database dump of this exact stack exchange section. While I am working on it I have encountered one issue that I am currently unable to solve.

In the XML File Posts.xml the contents look like this

There are of course multiple rows, but that's how one looks like. There's already a Tags.xml file given in the dump, which makes it even more obvious that the "Tags" attribute in that picture is in fact supposed to be its separate table (many to many).

So right now I am trying to figure out a way how to extract the tags. Here's what I tried to do:

CREATE TABLE #TestingIdea (
Id int PRIMARY KEY IDENTITY (1,1),
PostId int NULL,
Tag nvarchar (MAX) NULL
)
GO

↑ The table I created to test out my code. I have already filled it with the Tags and PostIds

SELECT  T1.PostId,
        S.SplitTag
FROM (
    SELECT  T.PostId,
            cast('<X>'+ REPLACE(T.Tag,'>','</X><X>') + '</X>' as XML) AS NewTag
    FROM #TestingIdea AS T
    ) AS T1
CROSS APPLY (
    SELECT tData.value('.','nvarchar(30)') SplitTag
    FROM T1.NewTag.nodes('X') AS T(tData)
    ) AS S
GO

Yet this code returns this error

XML parsing: line 1, character 37, illegal qualified name character

After googling this error (including here), whatever people had (like extra " marks or different CHAR sets) I didn't have. So I am kind of stuck. Maybe I missed something extremely obvious from previous answers I found T_T In any case I appreciate any help and advice on how to tackle this. It's the last table I have yet to normalize.

Small Sample Data From the XML File https://pastebin.com/AW0Z8Be2
For anyone interested in the program I use to view XML files (so it's much easier to read like in that picture above). It's called FOXE XML Reader (Free XML Editor - First Object)

Does something like this satisfy the resultset?

Table & Data

CREATE TABLE #TestingIdea (
Id int PRIMARY KEY IDENTITY (1,1),
PostId int NULL,
Tag nvarchar (MAX) NULL
)

INSERT INTO #TestingIdea(PostId,Tag)
VALUES(1,'<mysql><innodb><myisam>')

GO

Query

SELECT PostId, RIGHT(value,len(value)-1) as SplitTag
FROM #TestingIdea
CROSS APPLY string_split(tag,'>')
WHERE value != ''

Result

PostId  SplitTag
1   mysql
1   innodb
1   myisam