I have not had much experience switching the myisam_data_pointer_size option from 4 to 6.

Simply put, I think you got it right based on the Documentation. Here is why:

According to the MySQL Documentation 5.5 on myisam_data_pointer_size:

  The default pointer size in bytes, to be used by CREATE TABLE for MyISAM tables when no MAX_ROWS option is specified. This variable cannot be less than 2 or larger than 7. The default value is 6. See Section C.5.2.12, “The table is full”.

The fact that you changed it from 4 shows that the table was created originally in an earlier version of MySQL (default pointer size is 4 in MySQL 4.1).

Looking at the additional MySQL 4.1 reference for “The table is full”:

  You are using a MyISAM table and the space required for the table
  exceeds what is permitted by the internal pointer size. MyISAM creates
  tables to permit up to 4GB by default, but this limit can be changed
  up to the maximum permissible size of 65,536TB (2567 – 1 bytes).

  If you need a MyISAM table that is larger than the default limit and
  your operating system supports large files, the CREATE TABLE statement
  supports AVG_ROW_LENGTH and MAX_ROWS options. See Section 12.1.5,
  “CREATE TABLE Syntax”. The server uses these options to determine how
  large a table to permit.

  If the pointer size is too small for an existing table, you can change
  the options with ALTER TABLE to increase a table's maximum permissible
  size. See Section 12.1.2, “ALTER TABLE Syntax”.

Since you ran the change and MySQL did not keel over in agony, you must have gotten it right.

CAVEAT #1

I noticed the table characteristics you posted

Rows: 1786679 (that's 1,786,679 rows)
Avg_row_length: 2403
Row_format: Dynamic

What disturbs me is the average length. Why? I have easily seen 1 Billion Row MyISAM tables and never had a client switch its pointer size. This may be a good time to reevaluate the table's layout.

Here are some possible ways to shrink the table without touching the pointer size ?

Does the table have a lot of TEXT/BLOB fields ?
Could those TEXT/BLOB field be removed an placed in another table ?
Could the table stand some normalization ?
Do you have INT fields that could be shrunk to TINY, SMALLINT, or MEDIUMINT ?

CAVEAT #2

The next time you get table is full, please do not automatically assume that the table reached its internal limit. Always check the datadir to make sure the disk itself is not full.


MySQL 8.0.15 starts normally but any connection hangs

Everything but the bit about the general log was a distraction. Killing the mysqld daemon, disabling the general log in my.cnf and then restarting the server worked fine. Although I can't find this documented anywhere, apparently mysqld will be completely unresponsive while it is busy prowling through it's general log, and if that log is extremely large, it can take an arbitrarily long time.


Mysql's internal mysql database in replication

Answer to 2) question - Most probably you had some minor version upgrade for your master 5.5 MySQL server. There is a bug for 5.0 - Bug #50876 and according to my experience, the same bug exists in 5.5


Read this post how StackExchange does it:

http://www.brentozar.com/archive/2014/01/how-to-query-the-stackexchange-databases/


A weak entity with many to many relationship with its owner SQL

The FOREIGN KEY is not correct. You must create 2 of them. One the PATIENT table for the FILE_NO field and one on the APPOINTMENT table for the APP_NO field.

PAT_TO_FK FOREIGN KEY APT_PATIENT_FK (FILE_NO) REFERENCES PATIENT(FILE_NO)
APP_TO_FK FOREIGN KEY APT_APPOINTMENT_FK (APP_NO) REFERENCES APPOINTMENT(APP_NO)


Joining table from Linked Server is very slow

Put an index on A.ID

Add a <> so it can avoid taking locks

update A
    set A.ColumnA = B.ColumnB
from #Table A
join [LinkedServer].[DB].[dbo].[Table] B
  on A.ID = B.ID
where A.ColumnA <> B.ColumnB or A.ColumnA is null


How do you find where a stored procedure is used (in other stored procedures)

DBA_DEPENDENCIES view has all the answers to such questions.

select * from DBA_DEPENDENCIES
  where referenced_owner='HR' and referenced_name='STORED_PROCEDURE_41';


Access: search for more than one parameter?

If you'll input "4.6, 4.7", it could construct sql-query, using IN predicate that will give you something like:

where length IN(4.6, 4.7)


Permission error when using sys.dm_exec_input_buffer with @@SPID

Using the condition WHERE req.session_id = <some value> triggers the error condition. Then the line is ignored, and the rest runs correctly. Try executing:

SELECT      event_info
FROM        sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_input_buffer(req.session_id, req.request_id)

without the WHERE clause, and you should get the correct output.

tl;dr Looking for a specific session_id triggers the error.


You can generate a flag column for each group that you need to get:

 CREATE OR REPLACE VIEW vw_messages AS
 SELECT id
      , `from`
      , `to`
      , double_1
      , double_2
      , case when double_1 = double_2
              and `to` = 0
             then 1
        else 0
        end flag_1
      , case when double_1 = 0
              and double_2 = 0
              and `to` = $id
             then 1
        else 0
        end flag_2
      , case when double_1 = 0
              and double_2 = 0
              and `from` = $id
             then 1
        else 0
        end flag_3
 FROM messages

This way you can later get the rows that accomplish either one or any combinations of groups.


How to convert a unix time to PostgreSQL' Timstamp without timezome?

Here are a couple of approaches:

I've simplified your query, as you shouldn't need the TRUNC(), nor the CAST().

SELECT to_timestamp(1395036000) AT TIME ZONE 'UTC';

SELECT timestamp '1970-01-01 00:00:00' + interval '1395036000 second';

For reference, more information can be found at the following links:

First
query
Second
query


How to trim the log file that stores information about job history execution?

The information is not in a file, it is a table named sysjobstepslogs. The column for the logged data is nvarchar(max). I don't think there's any trunc option for this table, so if you output lots and lots of data from a job step, then you will have to live with having the last execution's data in this table being huge.
Note, that the regular sysjobhistory table (the one that the history GUI in SSMS uses) is capped at about 4000 characters and it doesn't include result from SELECT (it only includes output from errors and PRINT).
This raises the question why you have this huge amount of output from some of your jobsteps? Are you doing unnecessary SELECT in the job?
In case you can't trim down the output from the jobstep, then I would suggest that you just don't look at the data using the SSMS GUI since it bombs on that amount of output. This can be considered a bug, so report it to MS if you want. Anyhow, you can do SELECT from the sysjobstepslogs table directly and if SSMS chokes on the amount of data returned, then just do some substring to cap the length.


MAXTRANSFERSIZE setting causes restore to fail

This turned out not to be an error with the backup, but with the restore command that I was using.

RESTORE verifyonly  FROM
DISK = @backupFile
 WITH FILE=1,  STATS=5,  MAXTRANSFERSIZE=4194304

returned the error "Damage to the backup set was detected"
some
whereas

RESTORE verifyonly  FROM
DISK = @backupFile
 WITH FILE=1,  STATS=5

returned "The backup set on file 1 is valid."

Which is ironic given that I was only using the MAXTRANSFERSIZE option to try to speed up the restore, but instead it cost me half a day!

The error only occurs on backups taken from last night onwards.  Earlier backups verify fine whether I specify MAXTRANSFERSIZE or not, which is strange, but I guess I can live with not knowing the reason why that is unless somebody has an insight into that behaviour?


You could rewrite with OR and two EXISTS subqueries and compare performance with your query:

SELECT
    a.*
FROM
    a
WHERE
    EXISTS
    ( SELECT FROM b
      WHERE a.type_id = b.id
        AND b.user_id = 123
        AND a.type = 'dog'
    )
 OR EXISTS
    ( SELECT FROM c
      WHERE a.type_id = c.type_id
        AND c.user_id = 123
        AND a.type = 'cat'
    ) ;

or:

SELECT
    a.*
FROM
    a
WHERE
    a.type = 'dog'
    AND EXISTS
    ( SELECT FROM b
      WHERE a.type_id = b.id
        AND b.user_id = 123
    )
 OR a.type = 'cat'
    AND EXISTS
     ( SELECT FROM c
       WHERE a.type_id = c.type_id
         AND c.user_id = 123
    ) ;

Your query seems correct, assuming that there are unique constraints or indexes on b (user_id, id) or on b (id) - and c (user_id, type_id).

It does however looks somewhat obfuscated - it is not clear without careful reading that it restricts the result to rows that have WHERE a.type_id IN ('dog', 'cat').


How to find the detail cpu high process name which occupying most resource in the SQL Server database?

You can look at sys.dm_exec_query_stats to find queries which are causing the highest total_worker_time.

SELECT      TOP 20
            ST.text,
            QS.*
FROM        sys.dm_exec_query_stats          QS
CROSS APPLY sys.dm_exec_sql_text(sql_handle) ST
ORDER BY    total_worker_time


Creating a Database with the CREATE DATABASE Statement

If you want to create a database like that, then follow the steps listed on the above link.

Your next step should be Step 8: Start the Instance.

You have not created your the database yet, you do not have a controlfile, so you can not mount it. Startup the instance with nomount, then create the database with CREATE DATABASE.


This is a default behavior of SQL server, to understand more you need to invoke isolation level. What you have described above falls in isolation level called "Read Committed". If you want similar behavior like Oracle in SQL server then you need to turn on the Read Committed Snapshot database option, basically this option takes a snapshot of your data before modifications and puts them in tempdb, accordingly you would see last committed record which was snapshot of last committed records. Another isolation level Read Uncommitted will in this situation return data but with a big catch i.e. you would read uncommitted records and there is a fair chance that you could read a dirty record.
There is concept of nolock and readpast as lock hint in SQL server for avoiding locking however it has its own impact.
You may read more about isolation level at Microsoft site at this link.
There are tonnes of article on this subject from many SME, I am listing few of them for your reference:

Brent Ozar --> https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

Kendra Little --> https://littlekendra.com/2016/02/18/how-to-choose-rcsi-snapshot-isolation-levels/

Erik Darling --> https://www.brentozar.com/archive/2018/01/heaps-deletes-optimistic-isolation-levels/

Paul White --> https://sqlperformance.com/2014/07/t-sql-queries/isolation-levels

Robert Sheldon --> https://www.red-gate.com/simple-talk/sql/t-sql-programming/questions-about-t-sql-transaction-isolation-levels-you-were-too-shy-to-ask/

There is a video from Brent Ozar (unable to find now) where he has clearly explained the difference between Oracle and SQL server you have mentioned. For this reason, Oracle used to cost lot more earlier than SQL server per core however now the case/scenario is different. One more thing to add here - In azure, RCSI is default behavior.
I hope above helps. Actually this is less of a question than a very important topic and understanding of SQL server and hard to cover them in one answer however above links will definitely guide you in right direction.


Verify your intended change using SELECT

SELECT
    fullname = dbo.fnConvert_TitleCase(fullname),
    saddy = dbo.fnConvert_TitleCase(saddy)
from
 @Garbage

and then, if you're satisfied, run the update below

update @Garbage set
    fullname = dbo.fnConvert_TitleCase(fullname),
    saddy = dbo.fnConvert_TitleCase(saddy)

That being said, scalar functions don't really perform very well - a better solution would be to create a table value function and then you could use CROSS APPLY


SQL for Inserting the system date and a fixed time (not the system time) in a column

If you're asking for an expression that returns 6:05am on the current day

trunc(sysdate) + interval '6:05' hour to minute


Performance of primary key IDs with gaps (but in sequential order)

As far as PostgreSQL is concerned, your assumption

  having non-sequential IDs is bad for index performance

is generally not true. There are special use cases, where sequential numbers help somewhat, but not with index performance. In normal operation it does not make any difference whatsoever.

Operations on UUIDs are a somewhat slower than on int or bigint, because the are 16 bytes long (as opposed to 4 / 8 bytes).

Generally, having gaps in the numbering is the rule, not the exception.

For your special case, if you have a small number of instances and control over them, you could set aside separate ranges of a bigint or even just int: sequences with pre-set START value, for instance. Since you don't have mass inserts, there should be plenty of numbers.

For more instances or if you don't have control, I would strongly advice to use UUID instead of any home-grown solution. It's an established, well-tested technique and performs well.


You should be able to get the estimated query plan in SSMS without running the query.

If you do, you'll see that SQL Server is not going to be able to use partition elimination because you have a function on the partitioning column.  Just like using a function on an indexed column prevents SQL Server from using the index, using a function on a partitioning column prevents SQL Server from doing partition elimination.  SQL Server has no idea how to prove which partition function( column ) data might be in so it has to scan all of them.

Assuming trickey is a datetime column, you likely just want to compare it against dates directly rather than using a function

and trickey >= cast( '2019-09-13' as date )
and trickey <  cast( '2019-09-14' as date )


Convert raw or hex value to signed 2's complement via Oracle SQL

Using the expression
result = -(x & mask) + (x & !mask)

where mask=2^(n-1) yields the result you want.  The (x & mask) isolates the sign bit and (x & !mask) is the offset.  When the sign bit is not set, (x & mask) == 0 and (x & !mask) is the result.  When the sign bit is set, -(x & mask) == -2^(n-1) and (x & !mask) is the offset from the most negative number.
Next we convert the hexadecimal string to a raw representation by using HEXTORAW().  The raw representation is cast to an integer with UTL_RAW.CAST_TO_BINARY_INTEGER().
Assuming 16 bit:

select
  x,
  -BITAND(utl_raw.cast_to_binary_integer(hextoraw(x)),32768)
    +BITAND(utl_raw.cast_to_binary_integer(hextoraw(x)),32767)
  from (select '8508' x from dual);

DB Fiddle example
Note:  You probably want to do the utl_raw.cast_to_binary_integer(hextoraw(...)) once instead of twice.


How to setup SQL active/active cluster to achieve Blue / Green instance switching?

What you are looking to do is basically have two instances running that you can redirect clients between.  There's no need to do this within Failover clustering as this won't help you any.  Doing this simply requires that you setup two standalone instances of SQL Server, each on a separate Windows server.  Then setup a CNAME in DNS and use that to point people to the correct SQL Instance.

Now for some of the problems.

The data won't be in sync between the two instances.
DNS takes time to replicate and update on the client machine's local cache, so you'll end up with users connected to both systems.

A better solution would be to take a snapshot of the database before doing the schema change release.  If the release is successful delete the snapshot.  If the release fails and you have to rollback you'll need to restore the database from the snapshot.  This will be pretty quick, but there will be an outage while the rollback happens.

What you are looking for basically won't work.


Can't take a write lock while out of disk space

The problem was that I used folder as a mapped drive on Windows.

I've replaced link to the link with absolute path on the real hdd and problem has gone.

It seems that mongo checks free drive space and compares its changes with created file size.

I don't understand the reason for downvotes...


Can I install an additional oracle database on one node of an oracle cluster (RAC)?

You can install another database on the same server that is already running one instance of a RAC database, yes.


Solving high page load time using SQL Profiler

When you run DBCC DROPCLEANBUFFERS, DBCC FLUSHPROCINDB you require that the data is loaded into RAM and all plans are recompiled.

In a live system, you'd only this double whammy on startup or after heavy maintenance that evicted all data from cache and all plans were invalidated by a statistics update.

Your typical response time would be for the 2nd and subsequent calls.

On a really "cold" system your app in IIS would be unloaded too and would incur a CLR compile overhead


Compress files and directories into zip file and put it into blob field

Does it need to be done in real time?  I would engineer the solution differently.

The client uses a stored procedure to write to a queue table with what needs to be compressed.
A script running on the server periodically checks the queue table and zips up everything that needs to be zipped up, marks it as complete and does whatever else needs to be done.


Granting permissions to a DB user - SQL Server

Reason you are not seeing the permission when you open the window by default it is scoped to Securable scope: Schema' and notdatabase`.

More on secureables.

  Can select permission on the database be visible from somewhere apart
  by executing T-SQL code?

Yes it is possible. From your 2nd screen:

Click Search...
Select radio button All objects of the types....
Click Ok
Select Databases

You should see TestDB in the list at top half of the window. In the bottom half you can see Explicit and Effective permission.

In case you want to use tsql:

EXECUTE AS USER = 'testuser';
USE [DbaDatabase];
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
REVERT;
GO


How to get user-specific settings in PostgreSQL?

Unfortunately pg_shadow does not show roles with no LOGIN privilege.

I find it more useful to use @dezso hint view in the following query:

SELECT coalesce(role.rolname, 'database wide') as role,
       coalesce(db.datname, 'cluster wide') as database,
       setconfig as what_changed
FROM pg_db_role_setting role_setting
LEFT JOIN pg_roles role ON role.oid = role_setting.setrole
LEFT JOIN pg_database db ON db.oid = role_setting.setdatabase;

As always, just add it to psqlrc file (see doc)


SQL query or method to list all sql services running

Powershell would be simplest.

Get-Service "sql*"


Fastest way to extract full table in Postgres

Your current idea of using a range query on the primary key is probably the best bet.  I don't see how partial indexes could help with that.  You would need a series of partial indexes which would in aggregate be a total index, and with BTREE indexes used for range queries that would be pointless.  But, the success of this method is likely to depend on the rows of your table being physically approximately ordered by the primary key value.  You can enforce this ordering by using the CLUSTER the command, but that is itself a very expensive operation to do.

If you want to get PostgreSQL to parallelize this for you, you would have use a dummy query which selects everything:

COPY (SELECT * FROM metrics) TO 'metrics.csv' DELIMITER ',' CSV;

and you will probably also need to dramatically lower the "parallel_tuple_cost setting", perhaps all the way to zero.  But this is not likely to give you an actual improvement, because the bottleneck (if it is not disk IO) will be in converting your data from its internal binary format to the text format that is going to be output by the COPY.  This conversion is always done in the leader process, not the parallel workers.

You can kind of get around this, by writing your queries so the heavy lifting is explicitly done in the query itself:

COPY (SELECT col1::text, col2::text, col3::text, ... FROM metrics) TO 'metrics.csv' DELIMITER ',' CSV;

Now the lead process still needs to scan all that text for things which need to be escaped/quoted, but at least it doesn't need to convert JSONB to text for all the rows by itself.  In my hands, using 7 parallel workers (8 processes including the leader) cuts the time required to COPY...TO in half.  The overhead then becomes the communication from the parallel workers to the lead process.  The way to remove that is to go back your original suggestions of indexed range queries in separate sessions.


oNare's answer shows the principle way to achieve this. (He gets a +1 !!!)
He set the variable @names as follows:
SET @names="'oNare','Another'";

Then, his algorithm worked perfectly.
SUGGESTION
Instead of putting single quotes in @names, do it without the single quotes.
Do the delimiting before processing the query with this
SET @names='oNare,Another';
SET @names=CONCAT('''',REPLACE(REPLACE(@names,'''',''''''),',',''','''),'''');

What does @names look like now ???
mysql> SELECT @names;
+-------------------+
| @names            |
+-------------------+
| 'oNare','Another' |
+-------------------+
1 row in set (0.00 sec)

mysql>

I loaded his sample data and tried it out
mysql> SET @names='oNare,Another';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @names=CONCAT('''',REPLACE(REPLACE(@namelist,'''',''''''),',',''','''),'''');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @names;
+-------------------+
| @names            |
+-------------------+
| 'oNare','Another' |
+-------------------+
1 row in set (0.01 sec)

mysql> SET @query=CONCAT("SELECT
    ">                             fName,
    ">                             lName
    ">                         FROM user
    ">                         WHERE fName IN (",@names,")");
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM @query;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt;
+---------+-------+
| fName   | lName |
+---------+-------+
| oNare   | Test  |
| Another | Test  |
+---------+-------+
2 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)


Does innodb_data_reads include reads from the buffer pool?

Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests is very small, so the caching for reads is very effective.  The former is actual I/O; the latter includes cached reads.

Innodb_pages_read + Innodb_pages_written may be the number of 16KB blocks read + written.  In looking around on various machines, it seems that either number can be larger.

Indexes are included.  Non-leaf-node pages of BTrees are included.

I don't know what Innodb_data_* are.

Although many operations are "read-modify-write", the "read" is often cached, but the "write" needs to be flushed for persistence and syncing.

Bug #65030: "The Innodb_buffer_pool_pages_flushed status variable was incorrectly set to twice the value it should be. Its value should never exceed the value of Innodb_pages_written." (fixed in 5.5.25)

Do you want to monitor physical reads/writes?  Or logical reads/writes?


Postgresql - Get Oid and relation name for relations created by the query

I got the solution.

I have the relid of the CTE. (in the example ss1, ss2 etc.)

From the relid I can get RangeTblEntry for each CTE using

RangeTblEntry *rt1 = ((RangeTblEntry *) list_nth(root->parse->rtable, (relid)-1));

For a CTE RangeTblEntry has a field ctename, which stores the name it's a CTE.

You can use rt1->ctename to get the CTE name.


SQL Server 2014 SP3 DB emailer TLS 1.2 support

This hotfix worked for me:

https://support.microsoft.com/en-gb/help/3154520/support-for-tls-system-default-versions-included-in-the-net-framework

emails from dbmailer are using TLS1.2 now


Might sound silly but first thing, check your recycle bin. If the secondary file was small/empty it may still be there.

IF it is, shut off sql server, go to recycle bin, right click the file and choose restore. Then restart sql server and see if it works.

IF you did not drop the secondary file from the filegroup then the database expects there to be more files, so the error "inaccessible files" makes sense.


SQL minimum permissions to manage any job

Unfortunately, the SQLAgentOperatorRole msdb role is the most privileged role for "managing" only jobs on a SQL server, and it only gives the users the ability to disable/enable jobs/schedules not owned by the user.

The only supported way for giving full access to all jobs AND manage them using SQL Server Management Studio is to add the users to the sysadmin server role.

The reason for this is the way the sp_update_job* procedures are written, they are actually checking role membership for access.  Excerpt from sp_update_job is below.  Note the comments and the explicit check for @x_owner_sid <> SUSER_SID() along with NOT being in sysadmin.

  -- Check permissions beyond what's checked by the sysjobs_view
  -- SQLAgentReader and SQLAgentOperator roles that can see all jobs
  -- cannot modify jobs they do not own
  IF ( (@x_owner_sid <> SUSER_SID())                  -- does not own the job
      AND (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)   -- is not sysadmin
      AND (@enable_only_used <> 1 OR ISNULL(IS_MEMBER(N'SQLAgentOperatorRole'), 0) <> 1))
  BEGIN
   RAISERROR(14525, -1, -1);
   RETURN(1) -- Failure
  END

  -- Check job category, only sysadmin can modify mutli-server jobs
  IF (EXISTS (SELECT * FROM msdb.dbo.syscategories WHERE (category_class = 1) -- Job
                                                     AND (category_type = 2) -- Multi-Server
                                                     AND (category_id = @x_category_id)
                                                     AND (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1))) -- is not sysadmin
  BEGIN
     RAISERROR(14396, -1, -1);
     RETURN(1) -- Failure
  END

One alternative/workaround if you can live without SSMS management, is to utilize certificates for access to the underlying sp_update_job* procs, and then create wrapper procedures (which is why you cannot use SSMS still).  For a full example, see Uwe Ricken db Berater GmbH response in this MSDN post.


Unfortunately, not yet, Bob =( https://youtrack.jetbrains.com/issue/DBE-5373


Bulk SQL INSERT into Azure SQL Database using spark causes blocking/contention?

You could be hitting the Resource Governance limits for the Azure SQL DB.   Query the sys.dm_db_resource_stats while running the insert and you will see more detail on what is happening.
Possible workarounds could be to load into TempDB first of all and then process into your main DB (TempDB has higher resource governor limits) or scale up for the bulk load then scale back down afterwards.   Also consider partitioning of the table you are inserting into and consider the tips here


How can I automate copying DB2 table data between remote Servers?

I think you may have answered your own question. I would use command line shell scripts. Seeing as you are on Linux, you have several to choose from (sh, ksh, and perl to name a few).

Another thing that could make things easier is if you catalog the remote server and database locally. Then you can connect to it and work with it without your script having to worry about ssh'ing over to another box.


Returning Result Based on 2 conditions from one Table

This is a quick solution that uses union all.

The first query finds the matching rows between Table1 and Table2 where Table1.Type = 'Test 1' and Table1.Id = Table2.[Test 1]

The second query finds the matching rows between Table1 and Table2 where Table1.Type = 'Test 2' and Table1.Id = Table2.[Test 2]

--demo setup
Declare @Table1 table (Number int, ID int, Type varchar(10))
INSERT INTO @Table1
  (Number, Id, Type)
VALUES
  (1234, 1111, 'Test 1'),
  (1234, 2222, 'Test 1'),
  (1234, 3333, 'Test 2'),
  (1234, 4444, 'Test 2');

Declare @Table2 table (Number int, [Test 1] varchar(10), [Test 2] varchar(10), ID varchar(20))
INSERT INTO @Table2
  (Number, [Test 1], [Test 2], ID)
VALUES
  (1234, '1111', '', 'a1v1b0000006lmMAAQ'),
  (1234, '2222', '', 'a1v1b0000006lmNAAQ'),
  (1234, '', '3333', 'a1vf1000000qvTYAAY'),
  (1234, '', '4444', 'a1vf1000000qvTiAAI');

--solution using union all
SELECT t1.Number
    ,t1.ID
    ,t1.Type
    ,t2.ID
FROM @Table1 t1
JOIN @Table2 t2
    ON t1.Type = 'Test 1'
        AND t2.[Test 1] = t1.ID

UNION ALL

SELECT t1.Number
    ,t1.ID
    ,t1.Type
    ,t2.ID
FROM @Table1 t1
JOIN @Table2 t2
    ON t1.Type = 'Test 2'
        AND t2.[Test 2] = t1.ID

| Number | ID   | Type   | ID                 |
|--------|------|--------|--------------------|
| 1234   | 1111 | Test 1 | a1v1b0000006lmMAAQ |
| 1234   | 2222 | Test 1 | a1v1b0000006lmNAAQ |
| 1234   | 3333 | Test 2 | a1vf1000000qvTYAAY |
| 1234   | 4444 | Test 2 | a1vf1000000qvTiAAI |


Initials separated by periods (e.g. J.E.F. Smith)

You could probably write a function which looped over characters of the string to update any length string, but I'm guessing here you have a fairly limited range (i.e. you're unlikely to have records with more than four initials). If that's true you can do something like the following:

-- Setup example table
create table people (initials varchar(1024));

insert into people (initials)
values
  ('JEF'),
  ('J.EF'),
  ('JE.F.'),
  ('J E F'),
  ('J.E F  F');

-- Remove all spaces
update people
set initials = replace(initials, ' ', '');

-- Remove all periods
update people
set initials = replace(initials, '.', '');

-- Add periods after each letter
update people
set initials =
  case
    when length(initials) = 1 then initials
    when length(initials) = 2 then concat(substring(initials, 1, 1), '.', substring(initials, 2, 1))
    when length(initials) = 3 then concat(substring(initials, 1, 1), '.', substring(initials, 2, 1), '.', substring(initials, 3, 1))
    when length(initials) = 4 then concat(substring(initials, 1, 1), '.', substring(initials, 2, 1), '.', substring(initials, 3, 1), '.', substring(initials, 4, 1))
    else initials
  end;

SQL Fiddle example: http://sqlfiddle.com/#!9/7fc7e/1

Edit: Note that I've used three separate update statements here to make it easier to understand, you could collapse this down to one or two statements if running this over a large table, to avoid multiple updates.


SQL Server 2012 - Can't use AlwaysOn HA. What other options do I have?

Update: as Paul White mentions in a comment, it has been announced that SQL 2016 will support two-node alwayson groups via the limited "Basic AlwaysOn Group" feature. As Windows 2012 Standard edition includes clustering there will be no need for "enterprise" licensing to use a simple AlwaysOn pair. As SQL2016 is not yet released this does not immediately affect the asker of this question, things may change further between now and RTM, and Valien is already using SQL2012 currently, the answer is still correct as of now but there will soon be a more-or-less direct replacement for simple mirroring in SQL Standard Edition.

But before SQL 2016 is released...

Mirroring being dropped in future versions is an annoyance to many who can't use AO instead (mainly due to the enterprise edition requirements), but if you need to use it you'll find it present and supported as far as SQL2014 (in MS release terminology deprecated means "we intend to remove it at some point, so be prepared to switch sooner rather than later" not "stop using it right now or you are in an unsupported configuration"). As 2016 is not released yet the final feature set can not be confirmed but the length of 2014's support window gives you some time to play with before you have to switch to some other method so you could go with mirroring now but make sure management know that you need to put some time into researching future options.

Having said that implementing a fresh solution with a deprecated feature (what-ever definition of "deprecated") is not generally recommended.

If your intention is to prove disaster recovery then a good backup solution may be all you need: set your DB to full recovery, take full backups regularly and log backups even more regularly in between, make sure copies of these backups are kept off the main server and tested regularly.

The next step up would be to automate the testing of the backups: regularly restore on the second box the last full backup and the subsequent log backups. Or to achieve the same thing, implement log shipping. In either case have some automated solution scan the destination copy to make sure that it is running OK and has data you would expect (if your log backup is every hour then at any given time any change in the source DB made a little more than an hour ago should be present in the destination, if the application doesn't already have something you can easily check (such as a full audit trail with regular enough activity) then have a table with a single datetime column set a scheduled task to drop CURRENT_TIMESTAMP in there once per minute), have this drop you a mail/sms/other if it detects a problem (either database not available, or data in the secondary looks too old). This way you don't have to restore from scratch when something catastrophic happens: you should be at most one log restore away from having a relatively up-to-date database on the second box. This isn't as smooth as the quick switch-over possibility provided by mirroring or AO but it may be sufficient for your needs. Do consult with your users to make sure this is in fact sufficient - get them to specify "maximum downtime" and "maximum data loss in a DR situation" times, i.e. "service must be restored inside a two hours, we must not lose data from more than an hour before the outage" or some such (this can vary widely: some of the low volume systems we manage have a pleasantly lenient 24 hour loss window, if the business needs something in the few-minutes-or-less range then you probably need to convince them to stump up for relevant licensing so you can use AO (or just use mirroring for now but let them know the mid/long tern need to change due to the deprecation of the feature)).

Of course be aware that the backups are held off-site as well as on-site: while the most common need for DR is hardware failure within a single server your data could be rendered inaccessible by a number of things that would make a local second server unavailable too: complete power-out or network failure, fire, and so on. And make sure that your off-site backups are tested. It might feel inefficient to push the backups off-site then draw them back down for testing (instead of testing the original local copies) but this protects you from network transmission errors or otherwise undetected disk errors on the off-site store. If your second box is already off-site this dynamic changes somewhat.


More than 4000 chars gives string literal too long error on oracle

Alex Poole is correct, the call is in an SQL context which can only handle 4000 characters.  You could solve this by calling the method in a block like this:

Begin
   UPDATE_LETTER_BODY('long string',201);
End;
/

Other options include using another method to call Update_Letter_Body, creating a method to be called repeatedly to add text over 4000 characters, or using multiple input parameters.


Availability Group - how much data was lost after forced failover

Go to SSMS
Object Explorer
Expand Always On High Availability
Expand Availability Groups
Right click your AG
Select Show Dashboard

In the dashboard, above the list of AG databases and their status, there is Add/Remove Columns button
Pick Estimated Data Loss (time) and add this column to dashboard
You will be able to see the estimated data loss time for each DB
You can check this dashboard right before you are going to do forced failover, to get the idea of how much data can be lost


MS Access 2007/2010 Compact & Repair not working

Open that database in 2010, and do this:

Click the External Data ribbon group
Click the Access item
Use the Browse button to locate the database from which you want to import the database
Be SURE to select the first option (i.e. the "Import tables, blah blah" option)
Click OK
In the Import Objects dialog, select the tables you want to import. Be sure to review the settings in the Options button to see if you need any of those (chances are you do not).
Click OK, and Access will import the selected objects to your new database

Now, close out the database and make a copy of it. Reopen the new database, and Compact it.

If you're still have troubles after that, then there is a good chance that you have troubles with the installation of Access/Office or Windows on your machine. Be sure that you've fully updated Office and Windows, and try again.

If you're still having troubles compacting, then you may need read this MSDN forum thread.


Returning setof derived from for loop in plpgsql

This code:

return query select format('select userid from %s.users', r);

returns the text of queries to the caller instead of executing them and returning their results. You want a three-steps operation: build-execute-return.

These steps are easier to figure out if the code is broken into two lines (assuming query_text is a text variable declared above):

query_text := format('select userid from %I.users', r);
return query execute query_text;

Note: I've also changed %s to %I, the specifier dedicated to identifiers. If the schema identifier required quoting, it would happen automatically, whereas with %s the query would fail.


Since mysql schedules queries for execution in this manner:

Write requests (like UPDATEs and DELETEs) take priority over read requests (SELECTs)
Server performs writes FIFO manner (in the order received)

What are the benefits ?

Deadlocks can never occur with MyISAM. The MySQL server can thus manage all contention, explicit (LOCK TABLEs) or implcit (Any DML).

As long as a MyISAM table has no deleted or updated records, concurrent inserts can freely occur with impunity. That would, indeed, include INSERTs on a table that has an explicit read lock.

For any table with gaps, running OPTIMIZE TABLE would remove those gaps and allow concurrent inserts once again.

For more information, please read "MySQL 5.0 Certification Study Guide" pages 408-412 Section 29.2.


mysqldump takes forever to dump tables from a remote server

I ran into the same issue and regarding the "why" this happens, my theory is that it's due to network overhead of opening/closing connections.
My theory is that mysqldump needs to open and closes a lot of connections and that's what's slowing it down.

I ran it on the machine itself and it took 10 seconds.
From another machine, through a VPN, it took like 10 minutes.
From another machine on the same network (connected to the same switch), it took 20 seconds.

It's just a theory but it fits those observations.

Of course to work around that issue, the ssh trick works.


Copying Partitioned table data to another table on different SQL Server

BCP works fine.

First, you create the target table.
Then export data with the select and filter you need.
Import data with BCP will fit the correct partitions.

I did this before. I was on SQL 2014, but it will be fine on 2012.


After you enable the CDC on a database 2 jobs are created for that database:

cdc.DBName_capture (which will start the change data capture collection agent)
cdc.DBName_cleanup (which will clean up the change tables periodically)

the default retention is 3 days (data older than 3 days is removed)
the default schedule for this job is daily at 02:00 AM

The default retention period (how many days worth of data is kept before removal) and the threshold value (how many rows at once to clear in one execution) can be found in the table msdb.dbo.cdc_jobs, so any configuration for the cleanup can start here. Also, changing the schedule of the cleanup job is a trivial thing to do, as it's not different than any other job in this aspect.

So what you need to do is go to all the cleanup jobs of your CDC enabled databases and tune and schedule them as needed.

References:

Cleanup Job
Change Data Capture - What is it and how do I use it?
Tuning the Performance of Change Data Capture in SQL Server 2008


One-directional database replication without delete on target database?

Yes you can choose to not replicate delete statement.
Try this:


SSMS 18.4 Error opening database properties dialog box Azure SQL DB Managed Instance

Azure support pointed me to this article which worked to cure the problem. Apparently the issue was multiple old entries in the msdb backup sets. All left over from when I was experimenting with Azure SQL MI Migration Assistant. Interestingly, the database where the Properties dialog always worked also has multiple old entries in the backup sets. So it's still a head scratcher!


Trouble backing up remote database from local server

As is the command is trying to execute on your LOCAL instance and not REMOTE. You need to have it execute through the linked server itself. You can build the query as a dynamic statement and then simply execute the query against sp_executesql on the linked server. So something like this should:

DECLARE @qry varchar(2000)
SET @qry = 'BACKUP DATABASE [remotedb] TO  DISK = N''C:\Temp\remotedb.bak'''
EXEC REMOTE.master.dbo.sp_executesql @qry


If A is next to B, how to automatically assert that B is next to A

In your proposed PackageNextTo table, "ID (PK)" appears to be a surrogate key, presumably an autonumber. While I'm not a surrogate key advocate myself, I know folk who are who avoid them in a relationship tables (junction tables, linking tables, whatever). You would certainly need to enforce the candidate key (compound) of (PackageID, NextToPackageID) anyhow and the presence of the ID seems to have confused at least one person answering here. Therefore, I suggest you omit it.

To ensure the 'next to' relationship are stored uni-directional, add a CHECK constraint (or Validation Rule) e.g.

CREATE TABLE PackageNextTo
(
 PackageID CHAR(1) NOT NULL
    REFERENCES Packages (PackageID)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
 NextToPackageID CHAR(1) NOT NULL
    REFERENCES Packages (PackageID)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
 CHECK (PackageID < NextToPackageID),
 UNIQUE (PackageID, NextToPackageID)
);

To show all 'next to' relationships as bi-directional, expand them using a VIEW (or create a soted Query object in your usual way):

CREATE VIEW AdjacentPackages
AS
SELECT PackageID AS PackageID_1,
       NextToPackageID AS PackageID_2
  FROM PackageNextTo
UNION
SELECT NextToPackageID AS PackageID_1,
       PackageID AS PackageID_2
  FROM PackageNextTo;

Note that CHECK and CREATE VIEW require ANSI-92 Query Mode.


You confuse what with index  means.  Use of an index in no way directly drives the order of the results.  A table does not have an inherent order.  Even a table with a clustered index may not return results in the order of clustered index.  Now a sort will use an index but the index alone does not force the sort.  In your case the index made the sort efficient and I suspect if you look at the query plan the sort used the index.


Does the 'reads' column in SQL Profiler mean the same as IOPS?

One "read" in Profiler simply equates to reading one 8kb page.

This does not equate to IOPS, nor does it necessarily mean that data was read from disk. When performing a read, SQL Server will read data from disk only if that page does not already exist in the buffer cache. All data passes through the buffer cache before being used to satisfy your query.

Therefore, you can think of this number as not having anything to do with disk IO, but instead being a measure of the amount of data read from memory.


Two different databases on the same RAC cluster

We just did the same thing.

install software in new machines and configure different cluster
copy tnsnames.ora, orapwSID files to new machines and prepare pfile
you may need to add SID to /etc/oratab file on new machines
shutdown database on old machines
dismount ASM diskgroups on old machines
present LUNs to new machines
mount diskgroups on new machines
start database on new machines
configure database, instances, services using srvctl on new machines

Now you can unconfigure and remove old machines from old cluster. Of course Oracle version on new machines has to be the same as on old machines. Or it is possible to do upgrade right away on new machines.

You should test procedure of course. There are quite a few possible problems on the way.

The idea is that Oracle DB does not store anything in "cluster". All the data is in datafiles, controlfiles, redo logs and spfile. Which is stored on ASM can can be mounted on another server.


How to identify cross-database queries in SQL Server

I would set up an Extended Events session to capture lock acquired events and stream the events with the API.

First of all the session:

CREATE EVENT SESSION crossdb ON SERVER
ADD EVENT sqlserver.lock_acquired(
    SET collect_database_name=(1),collect_resource_description=(1)
    ACTION(
        sqlserver.request_id,sqlserver.session_id,
        sqlserver.sql_text,sqlserver.tsql_frame
    )
    WHERE (
            [package0].[equal_boolean]([sqlserver].[is_system], (0)) -- user SPID
        AND [package0].[equal_uint64]([resource_type], (5)) -- OBJECT
        AND [package0].[not_equal_uint64]([database_id], (32767))  -- resourcedb
        AND [package0].[greater_than_uint64]([database_id], (4)) -- user database
        AND [package0].[greater_than_equal_int64]([object_id], (245575913)) -- user object
        AND (
               [mode] = (1) -- SCH-S
            OR [mode] = (6) -- IS
            OR [mode] = (8) -- IX
            OR [mode] = (3) -- S
            OR [mode] = (5) -- X
        )
        AND [database_name] <> N'distribution'
    )
)
WITH (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=OFF
)
GO

Then, you could group the events on transaction_id and tsql_frame. The groups that touch more than one database_id are the ones that issue cross-database statements. You can then extract the statement from the sql_text action, using the offsetStart and offsetEnd from tsql_frame.

I have a similar example on my blog here: https://spaghettidba.com/2015/04/20/tracking-table-usage-and-identifying-unused-objects/


Error when upgrading SQL Server from 2012 (sp3) to 2014

Run this

EXEC  master.dbo.xp_instance_regread
 N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory'

to see what your default backup directory is set to.

This can also be viewed in Management Studio by right clicking on your instance, selecting Properties and going to the Database Settings tab. It will be near the bottom under Database default locations. If your default backup directory is set to 'H:\MSSQL11_MSSQLSERVER_Backup_Dir', changing it will help.


Identify column name information from table schema

In MySQL and Postgres you can use
MySQL INFORMATION_SCHEMA.COLUMNS
Postgres INFORMATION_SCHEMA.COLUMNS
SELECT COLUMN_NAME, table_name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_schema = 'db_name'
  AND column_name LIKE 'day' OR column_name LIKE 'business_day'


How do we implement SQL Server IF EXISTS (SELECT) in MySQL 5.6?

Assuming Options has a unique key on (OptionCatagory, OptionName), you can use a fake UPSERT/MERGE. More details can be found on MySQL's documentation:
INSERT ... ON DUPLICATE KEY UPDATE syntax.

INSERT INTO Options
(OptionCatagory, OptionName, OptionValue)
VALUES( 'General', 'HVRCustomPortNumber', @custportnum)
ON DUPLICATE KEY UPDATE
OptionValue = @custportnum;

The last part can also be written:

ON DUPLICATE KEY UPDATE
OptionValue = VALUES( OptionValue );


How should I shift values to one place back in SqLite?

Assuming your ID column is correct and sequential, you can join the table to itself on the ID column = ID column + 1.
Psuedo-Example:
SELECT T1.id, T1.f2 AS f1, T1.f3 AS f2, etc, T2.f1 AS f15
FROM YourTable AS T1
INNER JOIN YourTable AS T2
ON T1.id = T2.id + 1

Then with that dataset you can put it in a CTE (https://sqlite.org/lang_with.html) and then do an update statement on your table. I.e. you can update YourTable with the values from the CTE, e.g. SET YourTable.f15 = CTE.f15
Psuedo-Example:
UPDATE YourTable
SET f1 = (SELECT f1
              FROM YourCte
              WHERE YourTable.id = YourCte.id)
 WHERE EXISTS (SELECT id
                  FROM YourCte
                  WHERE YourTable.id = YourCte.id)

Just fill in the rest of the columns in the SELECT of the CTE, and the UPDATE statement.
Your last record in the table will need to be manually fixed assuming it's last value is just missing.


Can Select but not Insert from SQL Server to MySQL

Found out a solution for these, they said that the BINLOG_FORMAT must be "ROW" but then I'm having a doubt or difficulty to edit in my.cnf because I don't know what will happen if I changed the binlog format from mixed to row.

Try it. If MySQL is going to eat your data, it's not going to care about that option -- it'll just plow forward regardless of your configuration. BINLOG_FORMAT says explicitly

  If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used. It is possible to change the logging format to STATEMENT, but doing so at runtime leads very rapidly to errors because InnoDB can no longer perform inserts.

That means you have two options, either use the default isolation level of REPEATABLE READ or set the option.

On a side note, MySQL Isolation Levels are pretty borked and probably don't do what you want anyway


Does anyone deployed a succesful SSAS on docker?

Windocks offers Windows SQL Server containes, and plans to release support for SSAS and SSIS early in 2020 (ie., Jan).


Where is the database SERVICE NAME defined and what is the proper method for changing it?

Understanding Database Services

  The service name defaults to the global database name, a name
  comprising the database name (DB_NAME initialization parameter) and
  domain name (DB_DOMAIN initialization parameter).

You can find service information in ALL_SERVICES.

You can manage services with DBMS_SERVICE.

In an Oracle Restart or clustered environment, you can manage services with srvctl, which uses DBMS_SERVICE in the background.

No, you should not use the service_names parameter. That should be left to maintained by the above.

The default services should not be used by clients, and you should always create your own service(s) with the required properties.

Default Service Connections

  Your Oracle RAC database includes an Oracle database service
  identified by DB_UNIQUE_NAME, if set, or DB_NAME or PDB_NAME, if not.
  This default service is always available on all instances in an Oracle
  RAC environment, unless an instance is in restricted mode. You cannot
  alter this service or its properties. Additionally, the database
  supports the following two internal services:

  SYS$BACKGROUND is used by the background processes only

  SYS$USERS is the default service for user sessions that are not
  associated with any application service

  All of these services are used for internal management. You cannot
  stop or disable any of these internal services to do planned outages
  or to failover to Oracle Data Guard. Do not use these services for
  client connections.

The above is about RAC from the RAC documentation, but it is true for single instance environments as well.


Optional oneToOne relations and criteria field

I'd put person_id as second field of Report and get rid of notifier_type. Then create a relation persons (persona?) with id field, then make foreign key from Report.person_id to persons.id.

This relation persons (persona?) would have all of fields from both your legal- and naturalPerson relations, plus one additional - person_type. You would have NULLs when a field would be not applicable for given type of person (namely legal_Statut would be NULL for natural person and birthday would be NULL for legal person).

I would then exclude headquarters_address and home_address and make additional relation addresses with person_id field - again, foreign key to persons.id. You would then have a possibility of assigning multiple addresses to persons, mark them as current or previous, whatever you wish. That's what I would do.

EDIT: Visualization


Delete duplicate records and update the mapping table

You first need to create the mapping from the old map_id to the new one, this can be done using distinct on() and a self join:
select m1.map_id old_map_id, m2.new_map_id
from store_table_map m1
  join (
    select distinct on (store_id, item_id) store_id, item_id, map_id as new_map_id
    from store_table_map
    order by store_id, item_id, updated_date desc
  ) m2 on (m1.store_id, m1.item_id) = (m2.store_id, m2.item_id)

The above returns:
old_map_id | new_map_id
-----------+-----------
         1 |          2
         2 |          2
         3 |          4
         4 |          4

This can be used in an UPDATE statement:
update item_price_table_map ip
  set store_table_map_id = t.new_map_id
from (
  select m1.map_id old_map_id, m2.new_map_id
  from store_table_map m1
    join (
      select distinct on (store_id, item_id) store_id, item_id, map_id as new_map_id
      from store_table_map
      order by store_id, item_id, updated_date desc
    ) m2 on (m1.store_id, m1.item_id) = (m2.store_id, m2.item_id)
) t
where t.old_map_id = ip.store_table_map_id
  and ip.store_table_map_id <> t.new_map_id;

After that you can remove the rows with the now unused map_ids
delete from store_table_map stm
where not exists (select *
                  from item_price_table_map ip
                  where ip.store_table_map_id = stm.map_id);

(I could only test this on Postgres 12, but I think it should work on 9.2 too)


Help me define the tables in this scenario

My answer assumes that PRG_CODE is the primary key of both tables..  (or PRG_CODE + STATE is pkey of WEB_GK_STATE .. but PRG_CODE for WEB_GK)

My suggestion would be to redefine the PK of your WB_GK_STATE table..

WEB_GK_STATE
    WEB_GK_STATE_ID INTEGER PRIMARY KEY
    PRG_CODE    Varchar2(5)
    STATE       Varchar2(2)

Then alter your other table's PK as well.

WEB_GK
    WEB_GK_STATE_ID    INTEGER REFERENCES WEB_GK_STATE (WEB_GK_STATE_ID)
    TEXT        Varchar2(4000)

But that's only one possible solution.

Naturally, there would be several steps required to achieve this - this is simply what the end version of the tables would look like..

The advantage is that the insert/select from WEB_GK_STATE would be pretty much unaltered (assuming you have a trigger to give the WEB_GK_STATE_ID a valid value). Your stored procedure would have to be altered (minimally), and any SQL in the code that looked at the two tables would need to be reviewed and have it's JOIN condition (if there is one) altered.. I don't think that would be much work, though.

EDIT

Given further information, instead of changing your existing two tables, I would add a third..

WEB_GK_STATE_OVERRIDE
    PRG_CODE    Varchar2(5)
    STATE       Varchar2(2)
    TEXT        VARCHAR2(4000)

You would then need to edit your MAIN.F_GET_GK_TEXT function to first look at the WEB_GK_STATE_OVERRIDE function and see if an entry exists for PRG_CODE/STATE .. if so, return the TEXT value .. if not, return the TEXT value recorded in the WEB_GK table.

But your existing design and this one will be fragile.. They will work, but it's very much a bandaid instead of an actual good design that will be flexible in the long run.


Postgres: Missing data after Binary Copy - Is VACUUM FULL required?

To answer myself.

It was somehow related to  https://www.postgresql.org/message-id/flat/111D0E27-A8F3-4A84-A4E0-B0FB703863DF%40s24.com#111D0E27-A8F3-4A84-A4E0-B0FB703863DF@s24.com i think.

The OS changed from Debian7 to Debian8, and so the dynamically linked libc6 changed.
Had to do a REINDEX on all my Tables with Indexed String Columns.


The warning on mouse over should be from the auto complete feature, it's my guess. I've met that a few times. Especially with newly created tables in a session.
Try refreshing the cache for Intellisense in Management Studio and this should fix it.


Remember that S: is relative to the server, not necessarily where you're running SSMS from. I've seen plenty of people query the location of files and find they're not actually accessible from where SSMS is running. If S: is part of a user profile, then it could be associated with the service account even.


Performing SQL counts with and without a WHERE clause from the same table

You can do the following for SQL Server:
SELECT DeviceOS                  AS DeviceOS
     , COUNT(*)                  AS TotalCount
     , SUM(IIF(Bytes < 0, 1, 0)) AS BadCount
FROM (
         VALUES ('Roku', 10000)
              , ('AppleTV', -50000)
              , ('SamsungTV', -100000)
              , ('Roku', -100000)
              , ('AppleTV', 30000)
              , ('Roku', -90000)
              , ('AppleTV', -20000)
              , ('AppleTV', -10000)
              , ('SamsungTV', -100000)
     ) AS t1 (DeviceOS, Bytes)
GROUP BY DeviceOS

We are grouping by the DeviceOS column and getting the Name and the Count.
The only tricky part is using SUM(IIF(Bytes < 0, 1, 0)) AS BadCount to get the bad count.
What this does is it checks if the byte size is less than 0 (meaning it is negative):

if it is, then +1 is added to the sum
if it is not, then +0 is added to the sum


Log Autogrowths bigger than log size, but no shrinks to explain how

The most likely explanations I can think of are:

the database has been restored from a backup or snapshot

in the case of a restore from backup, the log file would be reverted to whatever size it was when the backup was taken
in the case of a restore from a snapshot, the log file is discarded and replaced with a tiny, 2-vlf log file

the monitoring is missing shrink events, or duplicating growth events due to a bug

the default trace event "Log File Auto Shrink" will only capture automatic shrinks.  If someone uses DBCC SHRINKFILE, it will show up as an Audit DBCC Event event.  So make sure you're capturing both

Check to see if restores are regularly occurring (for instance, if this is a test or reporting database).  Otherwise, you might need to review the code for the monitoring queries, and make sure there aren't missing or duplicated events.

One way to try and gather more information would be to use DBCC SQLPERF('LOGSPACE') to determine if the size of the log is ever changing (thanks to Paul Randal for suggesting this).  You could add this to your existing every-15-minutes check, by dumping the output into a temp table (and from there you could insert it into your logging tables):

CREATE TABLE #log_space
(
    [DatabaseName] sysname NOT NULL,
    LogSizeMb float NOT NULL,
    LogSpaceUsedPct float NOT NULL,
    [Status] int NOT NULL
);

INSERT INTO #log_space
EXEC ('DBCC SQLPERF(LOGSPACE);');

This will help you track down whether the file ever actually grows.  If it does, now you'll know at what point in the day it's shrinking again.


Materialized views use older dbms_job instead of scheduler?

dbms_scheduler, while being much more powerful/flexible than dbms_job, is not a simple/complete replacement for it. dbms_job is still widely used as you have discovered and is unlikely to go away


SQL Server replication - set PollingInterval in sp_addmergepushsubscription_agent

You need to set the PollingInterval Merge Agent parameter in the replication agent profile after you create your subscription and your subscription agent.

Have a look at Work with Replication Agent Profiles.


Index help for bad Query on fairly big table (2m)

If the subquery in that update consistently uses those two predicate values, a filtered index should help a lot.  Something like this (which Erik Darling kindly provided as a comment):

CREATE INDEX IX_ntID_ResponseWindow_Includes ON dbo.Notifications (ntID, ResponseWindow)
INCLUDE (NotificationType, Status)
WHERE (Status = 'Done' AND NotificationType = 'Shift');

This will let the subquery find the relevant group of IDs that match Status and NotificationType (as they are the only rows in the index) and avoid sorting the data (since it's already ordered by ntID as the leading column in the index).

It will still have to check the ResponseWindow value on each row.  In the worst case, if there are no rows that meet the date requirement for the query (or if the first match has a very high ntID), most or all of the index will be read.
  Which makes the effectiveness of this approach limited depending on data distribution.


Finding row with Max value with inner join and sub-query

One way to accomplish what you're looking for is by using the ROWNUM pseudocolumn. Note that you need to define your ORDER BY in an inline view or by some other method to avoid a common trap that people fall into. Here's one way to do it:

SELECT *
FROM
(
    Select      S.Student_ID, S.Student_Name, SUM(A.Score) as "TOTAL_SCORE"
    from        Student S
    inner join  Attempt A
    on          S.Student_ID = A.Student_ID
    group by    S.Student_ID, S.Student_Name
    order by    TOTAL_SCORE desc
) t
WHERE ROWNUM = 1;

As of Oracle 12c you can use the extended ORDER BY syntax to return just the first row. Here's an example:

Select      S.Student_ID, S.Student_Name, SUM(A.Score) as "TOTAL_SCORE"
from        Student S
inner join  Attempt A
on          S.Student_ID = A.Student_ID
group by    S.Student_ID, S.Student_Name
order by    TOTAL_SCORE desc
FETCH FIRST 1 ROWS ONLY;


This sounds to me like a network problem, restart the virtual machine and try again. The whole vm might be needing just a restart.


Simplifying query by adding Computed Column, performance impact?

Moving the calculation to the view won't change performance - it's still done at runtime. So all you've changed is the complexity of the outer query.

In order to change performance you would need to add the computed column to the table, and persist it or index it. This means the work of the calculation is done at insert/update time instead of query time. (Persisting only works in 2005+.)

Now, other factors are at play too. For example, do you have any evidence that this calculation actually affects performance? Maybe the query performs the same without it? Also using the view may cause other performance issues, for example if it involves additional columns, calculations or tables that this query doesn't actually need.


This means you have logged in correctly to SQL Server, but the database in the connection string (or default database from CREATE LOGIN) is one of

not there
unavailable (eg autoclosed or recovering)
no login-user mapping (eg CREATE USER as not been run)


Why does START TRANSACTION WITH CONSISTENT SNAPSHOT not work with SERIALIZABLE isolation level?

The WITH CONSISTENT SNAPSHOT modifier starts a consistent read for
  storage engines that are capable of it.

  consistent read A read operation that uses snapshot information to
  present query results based on a point in time, regardless of changes
  performed by other transactions running at the same time.

With SERIALIZABLE isolation level you don't need to use a snapshot because all necessary data is already locked by your transaction.

SERIALIZABLE isolation level should be more ACID compliant than REPEATABLE READ because the second one allows non-locking reads which may lead to a violation of the I (Isolation) rule of ACID.


Get top 1 row value from third table while joining 3 tables mssql

You can use CROSS APPLY to SELECT just the TOP 1 image from each particular header. APPLY is similar to a function you can create "on the go" that links columns or expressions from outside to it's filters or joins.

SELECT
    -- Header columns:
    HT.O_ID,
    HT.Type,
    HT.Total,

    -- Columns from the CROSS APPLY result
    I.Image
FROM
    HEADER_TABLE HT
    CROSS APPLY (
        SELECT TOP 1 -- Just retrieve 1 row (for each HT row)
            IT.IMAGE
        FROM
            LINE_ITEM_TABLE LIT
            INNER JOIN IMAGE_TABLE IT ON IT.ITEM_ID = LIT.ITEM_ID
        WHERE
            LIT.O_ID = HT.O_ID AND  -- Link the outmost header "HT" record to it's lines "LIT"
            IT.SECTION = 'Retail') AS I

You can add an ORDER BY inside the CROSS APPLY to determine which image will get selected. You can also change the CROSS APPLY to OUTER APPLY if you want header rows to display even when there is no matching record coming from the APPLY operator (the IMAGE column will be NULL).


Will SYSTEM_USER return the unique user id for users authenticated by Active Directory

A call to system_user will return the AD user's name in your case.  For instance, if you have a user set up as YourDomain\YourUser1:

select system_user

-- this returns "YourDomain\YourUser1"

If YourDomain\YourUser1 is part of a AD group (i.e. YourDomain\YourGroup1), then it would still have the same output as above (returning "YourDomain\YourUser1").


Specify different log file locations with Hallengren scripts

Assuming you have separate jobs to backup each database, your best bet is to edit that manually.

Unfortunately, the way these tokens are implemented, you can only get the database name when it's raised in an alert.

All of the tokens that start with "A-" can't be called the way general information tokens can.

Token   Description
(A-DBN) Database name. If the job is run by an alert, the database name value automatically replaces this token in the job step.
(A-SVR) Server name. If the job is run by an alert, the server name value automatically replaces this token in the job step.
(A-ERR) Error number. If the job is run by an alert, the error number value automatically replaces this token in the job step.
(A-SEV) Error severity. If the job is run by an alert, the error severity value automatically replaces this token in the job step.
(A-MSG) Message text. If the job is run by an alert, the message text value automatically replaces this token in the job step.

If they could, I'm sure they'd end up in Ola's code here:

  BEGIN
    SET @TokenServer = '$' + '(SRVR)'
    SET @TokenJobID = '$' + '(JOBID)'
    SET @TokenStepID = '$' + '(STEPID)'
    SET @TokenDate = '$' + '(STRTDT)'
    SET @TokenTime = '$' + '(STRTTM)'
  END

If you try to tack A-DBN on to the string, the job will fail.

$(ESCAPE_SQUOTE(SQLLOGDIR))\DatabaseBackup_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM))_$(ESCAPE_SQUOTE(A-DBN)).txt

  Message
  Unable to start execution of step 1 (reason: Variable A-DBN not found).  The step failed.

Hope this helps!


Difference between MariaDB Server and Maria DB Enterprise?

MariaDB comes in a few different forms:

MariaDB is free and includes: Access all the features of MariaDB Server

MariaDB Enterprise Subscription includes a number of other features and is priced per server.

To look at the high-level features available see: https://mariadb.com/pricing


Schema for stores and groups of stores and companies

I would create a table store_group that represents the hierarchy of the groups using and adjacency model:

store_group (
   id integer primary key
   name text not null, -- maybe even unique?
   ... other attributes
   parent_group_id integer references store_group --- null for the "root"
)

If a group can be associated with only a single store group, make that an attribute of the store table:

store (
  id integer primary key,
  group_id integer references store_group,
  ... other attributes ...
)

If group_id is null, the store does not belong to a group.

If a store can belong to multiple groups, use a m:n relation:

store (
  id integer primary key,
  ... other attributes ...
);

store_assignment (
  store_id integer not null references store,
  group_id integer not null references store_group,
  primary key (store_id, group_id)
);

And you obviously need a m:n relation between product and store:

store_product (
  product_id integer not null references product,
  store_id integer not null references store,
  primary key (product_id, store_id)
);


Your question is a little generic / open ended but here's the best I can answer your questions:

"Transaction Processor" is dedicated to tracking the transactions that occur in the primary database on one server so that it can keep distributed databases in sync with the primary database via the "Data Processor".

It can exist on the primary server or any of the other servers, but usually lives on the primary.

The "Data Processor" is the distributor of the data changes from the primary database to the distributed secondary databases. It's job is to actually move the transactions (or data) from the primary source server and sync it to the distributed secondary servers. It also can live on any of the databases (or its own database) and is usually chosen based on performance reasons.

DDBMS stands for Distributed Database Management System. So it is the very concept / architecture you're asking about. It is composed of multiple databases across multiple servers (usually one server being the primary and the other servers being secondaries). The job of DDBMS is to keep multiple copies of the same database across multiple servers in sync with each other.


This isn't how a trigger works. You should fire the TRIGGER before the update, then you just use SET on the NEW record.

SET new.dif_area = abs(100 - (OLD.area_pol / (OLD.area_ofi * 100)));

But even better, just drop this entirely and use a VIEW.

CREATE VIEW sch_cap.tbl_cap AS
  SELECT *, abs(100 - (area_pol / (area_ofi * 100))) AS dif_area
  FROM tbl_cap;


Refactoring Series of UNIONs in single table (SQL/SYBASE)

If you subtract 1 from FieldNum and divide the result by 3 using integral division, you will get the following results for FieldNum values of 1 till 6:

FieldNum | (FieldNum - 1) / 3
---------+-------------------
    1    |         0
    2    |         0
    3    |         0
    4    |         1
    5    |         1
    6    |         1

And if you replace / with % (the modulo operator), the results will be like below:

FieldNum | (FieldNum - 1) % 3
---------+-------------------
    1    |         0
    2    |         1
    3    |         2
    4    |         0
    5    |         1
    6    |         2

Now, in case the idea isn't yet clear, the (FieldNum - 1) / 3 expression can be used as a grouping criterion in addition to EntityID (for some reason called EntityNum in your query). And the results of (FieldNum - 1) % 3 could be used to determine particular rows (would-be columns) within the subgroups defined by (FieldNum - 1) / 3.

Finally, if my attempts at verbal description hasn't really succeeded, here's a query that uses the above two expressions to produce the results you are after:

SELECT
  EntityID,
  MAX(CASE (FieldNum - 1) % 3 WHEN 0 THEN Memotext END) AS Date,
  MAX(CASE (FieldNum - 1) % 3 WHEN 1 THEN Memotext END) AS Name1,
  MAX(CASE (FieldNum - 1) % 3 WHEN 2 THEN Memotext END) AS Name2
FROM EQ
WHERE FieldNum BETWEEN 1 AND 6
GROUP BY
  EntityID,
  (FieldNum - 1) / 3
;

And here you can find a "live" SQL Fiddle demonstration. To be honest, the demo uses SQL Server to run the query, but I would expect Sybase to produce the same results. (I'm assuming that FieldNum is an integer-typed column and that Sybase, like SQL Server, automatically applies integral division when both operands of / are integers.)


Shadow backups are taken by VSS backing up all the C: drive  lead into baking up the database and then lead into the I/O frozen message seen in the SQL server log.

The database backup when it is done normally via maintenance plan, via SQL Management Studio or via user  SQL query  does not generate I/O frozen message .


Difference between FreeProcCache and FreeSystemCache

What caches does FREESYSTEMCACHE wipe that FREEPROCCACHE doesn't?

There are many system caches available in SQL Server. I am referring to SQL 2008R2 (as I tested on it).

Below query will return all the caches available :

--- ONLY for Educational purpose. Don't attempt to run on PROD !!

select 'DBCC freesystemcache ('+''''+name+''''+')'  from   sys.dm_os_memory_clerks group by name
-- 155 rows

so for e.g. you want to clear all caches you will run

DBCC FREESYSTEMCACHE ('ALL')

for specific caches to be cleared you can run

DBCC FREESYSTEMCACHE ('userdatabase') -- cleans cache for specific user database
DBCC FREESYSTEMCACHE ('tempdb')  -- clears cache for tempdb
DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables') -- clears all the temp table and variables
.. and many more (see above 155 rows returned from sql server 2008R2)

Using FREEPROCCACHE, you wont be able to clear all above mentioned at that detailed level.

FREEPROCCACHE ==> from BOL

  Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool.

So essentially, you might think of it as only related to Plan cache with the ability to remove specific plans from the cache with the plan handle and or pool name.

Fun With DBCC FREEPROCCACHE is a blog from Glenn Berry


Error converting data type nvarchar to float. Oracle Linked Server

It is in fact a configuration issue in the Oracle Client

Set NLS_LANG to AMERICAN_AMERICA.WE8MSWIN1252 and no more conversion error.

UPDATE

Why? Because 0.6 in French would be read as a character value whereas in English it remains numeric.  0,6 in French is 0.6 in English.

Defining NLS_LANG as American means that 0.6 stays numeric thus allowing the cast as float to occur without error.

Restart required.


ORA-01503 CREATE CONTROLFILE FAILED... while trying to create a database

First of all, you could check the newly created init/spfile (under; linux: $OB/dbs - or - win: $OB\database) if everything is set correctly (SID and Service Name, Datafile location...)

I also would advise you to check all your env. vars, the error says “Database already mounted” - it seems it’s trying to create a .ctl for your already-running database.

You can try this to start fresh;

Linux

copy your actual .profile in .profile_new
modify .profile_new as per your need ($OH, $ORACLE_SID,...)
./profile new
check you’re all set: env
start dbca again

Windows

open cmd as Admin
set ORACLE_SID=[your new SID]
set ORACLE_HOME=[your new $OH]
run dbca

Last thing, i would take a look at the oraInventory/oraInst.lok file, to check if there’s the old $OH set - if yes, move the file away (just to backup it) and restart the dbca.

Hope one of this solutions helps.


How to define destination for an append query Microsoft Access

If MainTable has more that one colum (I guess it does), then you need to specify it like that:

INSERT INTO MainTable(ColumnName)

Also you have to specify the columns in the SELECT STATEMENT:

SELECT Column1, Column2
FROM TABLE2....


Foreign Keys don't automatically generate in the child referenced tables unless you use the CASCADE specifier in the declaration of the foreign key. See Why foreign key doesn't update? for more information on how to use CASCADE with foreign keys and alternative solutions.


MySQL - can't get the result of non-matching records

So I think what your looking for is this:

I used this to find the abandoned the abandoned records:

SELECT tpd.id, tpi.name, td.id, td.documentName
FROM tbl_personnel_documents AS tpd
LEFT JOIN tbl_personnel_info AS tpi ON tpi.id = tpd.personnel_id
RIGHT JOIN tbl_documents AS td ON td.id = tpd.document_id
;

Returns

TPD_ID, TPI_NAME, TD_ID,     TD_DOCNAME
-------------------------------------------
   '1',  'Kevin',   '1',    'PASSPORT'
   '2',  'Kevin',   '2',    'AUSTRALIAN VISA'
  NULL,     NULL,   '3',    'US VISA'

The reason you will not get the result

name  | documentName  |
----------------------
Kevin   US Visa

Is because there is no value linking them.

Let me know if this is what you are looking for.

😁

EDIT 1:

If you want to filter them out you can use an INNER JOIN on your joins.

That will strip out all values that don't have a linking value, based on the query in your SQL Fiddle:

SELECT tpd.id, tpi.name, td.id, td.documentName
FROM tbl_personnel_documents AS tpd
INNER JOIN tbl_personnel_info AS tpi ON tpi.id = tpd.personnel_id
INNER JOIN tbl_documents AS td ON td.id = tpd.document_id
;

If you want to see only the missing records you can use:

SELECT tpd.id, tpi.name, td.id, td.documentName
FROM tbl_personnel_documents AS tpd
LEFT JOIN tbl_personnel_info AS tpi ON tpi.id = tpd.personnel_id
RIGHT JOIN tbl_documents AS td ON td.id = tpd.document_id
WHERE tpd.id IS NULL;


Can send test database mail but job emails failing

When you use Send Test Email, SSMS is using your credentials. When SQL Agent attempts to connect to the mail profile, it's using the SQL Agent service account credentials. That would be a major difference, and it wouldn't have anything to do with time of day...just credentials.
You can test it by scheduling a job that has a task that will fail (i.e. Select * from northwind) and let the SQL Agent run it, and find out if it will send the notification. That will at least eliminate the service account permission possibility...


Yes/No columns in logical database design

For a logical database design you don’t need to specify TINYINT or CHAR — that’s a physical consideration. Some DBMSes such as Postgres have a BOOLEAN data-type which would be preferred, but again, a physical design.

In your logical model you could assume a binary-valued datatype with Yes and No as the two values (or a ternary-valued type if NULL should also be allowed).

You have two questions actually; for the other, yes, I would keep the flag in the same relation.


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

I found a support.oracle.com Doc ID 1519958.1 that describes the issue.
For solution it says: Use the relatively simple work around of linking to a view that is based upon the table, instead of directly to the table.
Apparently this issue has been around since 2012.


How to properly replicate and archive at once

Let me explain multiple things.  Then you can figure out which combination fits best.

Technically the Primary and the Replica should have identical data.  However...
You can, via a temporary setting, say that specific statements are not to be replicated.  These could be anything -- INSERT, Partition commands, DROPs, etc.
It sounds like you want the data to be moved from fast media to slow media.  This would require some form of copy.  And that would probably block all operations (maybe even SELECTs) from the table while it is happening.
It may be possible to direct that certain PARTITIONs are in a different filesystem path.  If so, that secondary path could be the other drive.
"Transportable tablespaces" allows for an individual PARTITION to be disconnected from a table and turned into an independent table.  That could them be copied to another device, backed up, deleted, etc.  (Do not do it via filesystem operations.)
It is probably wise to switch to PARTITION BY RANGE(TO_DAYS(...)) with daily partitions.  This would facilitate such movement and eventually deleting after 60 days.
PARTITIONing applies to a table, so there is no need for doing it with all tables.  (Perhaps you are referring to "sharding", which involves having separate, independent, instances of MySQL with each one having part of the data.
If you have not already set up partitioning or replication, there will be a one-time cost (in downtime) to set up either.  After that, ongoing maintenance will be 'cheap'.
More on this use of partitioning:  http://mysql.rjweb.org/doc.php/partitionmaint


update the timestamp on column when data is filled in another

For my preference, a trigger is the wrong tool for the job here. Trying to outguess the application in terms of whether it really meant to update a certain value or not is a losing game. Let the business logic do what it is told and if it updates value "a", then you as the administrator require that it also update "a_timestamp" part and parcel of the same transaction.

By way of example: if you're limited in your ability to modify the app code but you can require a json payload, you can use a model like the following (full dbfiddle here).

Given a table of the form...

create table foo (
    id serial primary key,
    a int,
    b int,
    c int,
    a_tstz timestamptz not null default now(),
    b_tstz timestamptz not null default now(),
    c_tstz timestamptz not null default now()
);
insert into foo (a,b,c)
values
(1,2,3),
(4,5,6);

...and a proc of the form...

create procedure bar (payload jsonb)
language sql
as $$
    with j as (
       select payload as p
    ), cte as (
        select
            (p->>'id')::int as id,
            case when p ? 'a' then (p->>'a')::int else f.a end as a,
            case when p ? 'b' then (p->>'b')::int else f.b end as b,
            case when p ? 'c' then (p->>'c')::int else f.c end as c,
            case when p ? 'a' then now()::timestamptz else f.a_tstz end as a_tstz,
            case when p ? 'b' then now()::timestamptz else f.b_tstz end as b_tstz,
            case when p ? 'c' then now()::timestamptz else f.c_tstz end as c_tstz
        from foo f
        join j on (j.p->>'id')::int = f.id
    )
    update foo as f set
        a = c.a,
        b = c.b,
        c = c.c,
        a_tstz = c.a_tstz,
        b_tstz = c.b_tstz,
        c_tstz = c.c_tstz
    from cte as c
    where c.id = f.id;
$$

...you can call bar('{"id":1,"a":1,"c":null}'::jsonb);. This will demonstrate to you that attributes which are specified in the json payload are updated alongside their paired timestamps while unspecified attributes are not updated. This is true even if specified attributes

Are updated to the same value as they were before
Are moved to an unknown NULL state

Updates that occur outside usage of the procedure with a valid jsonb payload will still require that you update the paired timestamp "manually". The proc demonstrates only that you can infer which timestamps need to be updated. It's worth noting that this approach as-written here likely has a bunch of edge case bugs you'd want to QA for. Off the top of my head...

malformed payload - no id supplied, a supplied multiple times, invalid data supplied, etc...
multiple payload - I only tested a single valid payload. If you supply multiple payloads at the same time what do you want to happen.
upsert logic - the proc as given only updates values. How will you detect no update has happened and insert as needed?

Possibly also worth noting is that requiring yourself to update the data/timestamp pairs via the procedural code allows you to do out-of-band updates to data in the table that don't futz the timestamp without disabling the trigger. While you may say now "I always want the timestamp updated.", in my experience it's a questionable choice to paint yourself into a corner like that. To put it another way: "It's not a bug, it's a feature."


SQL Server stored procedure producing data related to months between two dates

I would approach this by first creating a permanent dates table I can use to help create the desired output.  Then I would create a pivot table using dynamic T-SQL to allow for varying column names in the output.

Setup the test-bed in TEMPDB:

USE tempdb;

IF EXISTS (SELECT 1 FROM sys.objects o WHERE o.name = 'DatesTable')
BEGIN
    DROP TABLE dbo.DatesTable;
END

CREATE TABLE dbo.DatesTable
(
    dDate DATETIME
        CONSTRAINT PK_DatesTable
        PRIMARY KEY CLUSTERED
    , iDay TINYINT
    , iMonth TINYINT
    , iYear SMALLINT
);

INSERT INTO DatesTable (dDate, iDay, iMonth, iYear)
SELECT TOP(90000) DatesList.dDate, DATEPART(DAY, DatesList.dDate), DATEPART(MONTH, DatesList.dDate), DATEPART(YEAR, DatesList.dDate)
FROM (
    SELECT dDate = DATEADD(DAY, -1, ROW_NUMBER() OVER (ORDER BY o.object_id))
    FROM sys.objects o, sys.objects o1, sys.objects o2
    ) DatesList;

IF EXISTS (SELECT 1 FROM sys.objects o WHERE o.name = 't')
BEGIN
    DROP TABLE dbo.t;
END

CREATE TABLE dbo.t
(
    PRJ_ID INT NOT NULL
    , [START_DATE] DATETIME NOT NULL
    , END_DATE DATETIME NOT NULL
);

INSERT INTO dbo.t (PRJ_ID, START_DATE, END_DATE)
VALUES (1, '2015-08-01', '2015-08-03')
    , (2, '2015-07-01', '2015-08-03')
    , (3, '2015-10-01', '2015-10-03');

Here's the good bit:

DECLARE @PivotStmt NVARCHAR(MAX);
DECLARE @PivotCols NVARCHAR(MAX);

DECLARE @StartDate  DATETIME,
        @EndDate    DATETIME;

SELECT   @StartDate = '20150501'
        ,@EndDate   = '20151101';

SET @PivotCols = '';

/* Here we are dynamically creating the month-columns for the pivot */
SELECT @PivotCols = @PivotCols + CASE WHEN @PivotCols = '' THEN '' ELSE ', ' END + '[' + STUFF(CONVERT(VARCHAR(30), CONVERT(DATETIME, CONVERT(VARCHAR(4), dt.iYear) + '-' + CONVERT(VARCHAR(2), dt.iMonth) + '-01 00:00:00'), 106),1,3,'') + ']'
FROM dbo.DatesTable dt
WHERE dt.dDate >= (SELECT MIN(t.START_DATE) FROM dbo.t)
    AND dt.dDate <= (SELECT MAX(t.START_DATE) FROM dbo.t)
GROUP BY dt.iMonth
    , dt.iYear
ORDER BY dt.iYear
    , dt.iMonth;

SET @PivotStmt = 'SELECT PRJ_ID
    , ' + @PivotCols + '
FROM
(
    SELECT t.PRJ_ID
        , MonthYear = STUFF(CONVERT(VARCHAR(30), CONVERT(DATETIME, CONVERT(VARCHAR(4), dt.iYear) + ''-'' + CONVERT(VARCHAR(2), dt.iMonth) + ''-01 00:00:00''), 106),1,3,'''')
        , [Yes] = ''YES''
    FROM dbo.t
        INNER JOIN DatesTable dt ON t.START_DATE <= dt.dDate AND t.END_DATE >= dt.dDate
    GROUP BY t.PRJ_ID
        , dt.iMonth
        , dt.iYear
) SourceTable
PIVOT (
    MIN([Yes])
    FOR MonthYear IN (' + @PivotCols + ')
) as pvt
ORDER BY PRJ_ID;';

/* print out the generated pivot statement for debugging */
PRINT (@PivotStmt);
/* execute the dynamically created pivot statement */
EXEC (@PivotStmt);

My example code above returns the following result set:

Notice, Sept 2015 is included in the output even though none of the data in dbo.t contains projects in September 2015.


Handling time zones in data mart/warehouse

Firstly...

Separating Datime/Time into a Date dimension and a Time dimension is definitely the way to go.

To manage multiple time zones you need to duplicate the DateKey and the TimeKey so that you have the following:

LocalDateKey
LocalTimeKey
UtcDateKey
UtcTimeKey

You say...

  The problem I am having with all that is that 11:00 PM on Tuesday,
  December 31, 2013 in UTC is Wednesday, January 1st, 2014 in all time
  zones that are after UTC+2.

By having the 4 columns I've listed above you, will be able to join the fact table to the Date and/or Time dimension Using Table Aliases (in Kimball terminology these aliased dimension tables are known as "Role Playing Dimensions"), so you would have something like the following:

/*
    Assumes the following:
        - [DateLongName] has the format of this example "Tuesday, December 31, 2013"
        - [TimeShortName] has the format of this example "11:00 PM"
        - Both [DateLongName] & [TimeShortName] are strings
*/
select
    -- Returns a string matching this example  "11:00 PM Tuesday, December 31, 2013"
    localTime.TimeShortName + ' ' + localDate.DateLongName
    ,utcTime.TimeShortName + ' ' + utcDate.DateLongName
    ,f.*
from
    FactTableName  AS f

    -- Local Date and Local Time joins
    inner join dbo.Date  AS localDate
        on localDate.DateKey = f.LocalDateKey

    inner join dbo.Time  AS localTime
        on localTime.TimeKey = f.LocalTimeKey

    -- Utc Date and Utc Time joins
    inner join dbo.Date  AS utcDate
        on utcDate.DateKey = f.UtcDateKey

    inner join dbo.Time  AS utcTime
        on utcTime.TimeKey = f.UtcTimeKey

In closing...

As you're building a data mart, and not an OLTP database, the generation of the Local and Utc times should be performed in your ETL, NOT in any client side applications for the following reasons (apart from localisation of the UTC time to the report reader's perspective):

Having the calculation reside in any queries places an extra performance burden on them, multiplied by the number of times you have to run said query for any reports you have (this matters when reading millions of rows)
Extra burden of ensuring the calculation is maintained correctly in each query (especially when you take daylight savings time into account)
Prevent range scanning of any indexes the column is part of, as you'll be performing a calculation on the column which forces queries to perform index scans instead of seeks (which are typically more expensive as each data page is needed to be read); this is known as being non-sargable.

Edit due to comments: This applies if you push the conversion down into the actual query.

Using the concept of having the additional UTC dates and times available, there is nothing stopping you from taking this concept and extending it by calling this StandardisedDateKey, or CorporateHQDateKey, where instead of a UTC date table you standardise based on some other business agreed standard
Having the two separate column types (Local and UTC), allows for side-by-side comparison across geographic distance. Think -> someone in Australia enters a record that is timestamped with both Local and UTC, someone in New York reads the report with the Local (Australia) date and time and the New York representation of the UTC date and time, thereby seeing that something their Australian counterpart did during the middle of the day (Australia time) happened in the middle of the night their time (New York time). This comparison of time is indispensable in multi-national businesses.


Missing rows under read committed isolation

A scenario where rows committed before the start of the SELECT query can be missed is when an index key value is updated. Consider this query:
SELECT * FROM dbo.YourTable;

The execution plan will likely perform an ordered clustered scan to return all columns. If a key value is updated and committed during the scan and the new value is less than the ordered scan point at the time of the update, the row will not be returned.
With this query:
SELECT * FROM dbo.YourTable WHERE id = @id;

The row will not be missed if id is the primary key and a singleton seek is used. But in the case of a seek/scan of non-unique index where the key value is updated and committed during the scan, the row may or may not be returned depending on the scan point that the time of the update.


In-Memory is for making your heavily accessed table as memory optimized.
For a memory optimized table, all data is stored in memory and hence unlike disk based tables, pages don't need to be brought into the buffer pool or cache.
In order to create a memory optimized table, you need to create a database with a filestream filegroup (along with the use of CONTAINS MEMORY_OPTIMIZED_DATA)

-- enable for in-memory OLTP - change file path as needed
ALTER DATABASE imoltp ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE imoltp ADD FILE (name='imoltp_mod1', filename='c:\data\imoltp_mod1') TO FILEGROUP imoltp_mod

GO

Now for creating memory optimized tables you have to create table with WITH (MEMORY_OPTIMIZED=ON)

Getting Started with SQL Server 2014 In-Memory OLTP will give you all the detailed steps to get you started.

For more details, check this BOL article : In-Memory OLTP (In-Memory Optimization)

Update:

@cat you have to follow 3 step process

Determining if a Table or Stored Procedure Should Be Ported to In-Memory OLTP ,
Determining if a disk based Table or Stored Procedure Should Be Ported to In-Memory OLTP -- informs you about which tables in your database will benefit if ported to use In-Memory OLTP and finally
After you identify a table that you would like to port to use In-Memory OLTP, you can use the memory optimization advisor to help you migrate the disk-based database table to In-Memory OLTP.


Keeping an up to date copy of a SQL Server database on another server

Just to clarify what I mean by having an an update to date copy we don't mind if the database is only updated or synchronized once a day.

Depending on the database size, I would use backup with compression and restore. You can easily automate this process.

Just take full backup with compression once every night, upload it to FTP (probably secure) and have a process on your end to download it and restore the database (overwriting the previous copy).

There are other methods like using SSIS - if you want selected tables only or using BCP OUT and BCP IN - provided you have same schema on your side.

Dont get into the hassle of implementing replication as making schema changes is a big headache + you have to generate snapshot and depending on the size of your database and network, it will have a performance impact on the source server.


I tried to produce the same ordering and having some trouble
but here are two different queries you can start with:

SELECT
    booki_id,bet_id,bettype,
    group_concat(odds order by bet) betodds,line
FROM
    bettable
GROUP BY
    booki_id,bet_id,bettype,line
;

SELECT * FROM
(
    SELECT
        booki_id,bet_id,bettype,
        group_concat(odds order by bet) betodds,line
    FROM
        bettable
    GROUP BY
        booki_id,bet_id,bettype,line
) AA
ORDER BY betodds
;

I also created sample data to test it with, and here it is:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.12 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

MySQL (Current test) :: use test
Database changed
MySQL (Current test) :: DROP TABLE IF EXISTS bettable;
Query OK, 0 rows affected (0.03 sec)

MySQL (Current test) :: CREATE TABLE bettable
    -> (
    ->     id       INT NOT NULL AUTO_INCREMENT,
    ->     booki_id INT,
    ->     bet_id   INT,
    ->     bet      INT,
    ->     bettype  VARCHAR(10),
    ->     line     FLOAT,
    ->     odds     FLOAT,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

MySQL (Current test) :: INSERT INTO bettable (booki_id,bet_id,bet,bettype,line,odds) VALUES
    -> (123,321,1,'3way',0.0,2.3),
    -> (123,321,2,'3way',0.0,3.4),
    -> (123,322,1,'3way',0.0,1.1),
    -> (123,322,2,'3way',0.0,7.4),
    -> (123,323,1,'3way',0.0,1.3),
    -> (123,323,2,'3way',0.0,9.4);
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

MySQL (Current test) :: SELECT
    ->     booki_id,bet_id,bettype,
    ->     group_concat(odds order by bet) betodds,line
    -> FROM
    ->     bettable
    -> GROUP BY
    ->     booki_id,bet_id,bettype,line
    -> ;
+----------+--------+---------+---------+------+
| booki_id | bet_id | bettype | betodds | line |
+----------+--------+---------+---------+------+
|      123 |    321 | 3way    | 2.3,3.4 |    0 |
|      123 |    322 | 3way    | 1.1,7.4 |    0 |
|      123 |    323 | 3way    | 1.3,9.4 |    0 |
+----------+--------+---------+---------+------+
3 rows in set (0.01 sec)

MySQL (Current test) :: SELECT * FROM
    -> (
    ->     SELECT
    ->         booki_id,bet_id,bettype,
    ->         group_concat(odds order by bet) betodds,line
    ->     FROM
    ->         bettable
    ->     GROUP BY
    ->         booki_id,bet_id,bettype,line
    -> ) AA
    -> ORDER BY betodds
    -> ;
+----------+--------+---------+---------+------+
| booki_id | bet_id | bettype | betodds | line |
+----------+--------+---------+---------+------+
|      123 |    322 | 3way    | 1.1,7.4 |    0 |
|      123 |    323 | 3way    | 1.3,9.4 |    0 |
|      123 |    321 | 3way    | 2.3,3.4 |    0 |
+----------+--------+---------+---------+------+
3 rows in set (0.02 sec)

MySQL (Current test) ::

You will have to play with the order of the whole rows from here.

Give it a Try !!!


The problem is that I don't have the rights to write to the registry that causes the issue.

(It would be nice if sql would throw an error not just close the box without saving).

I found the registry key to edit here:
http://kushagrarakesh.blogspot.com/2011_10_01_archive.html


Is there an elegant way to send query results directly to a physical printer?

Execute the query using POWERSHELL and OUT-PRINTER

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance .   | Out-Printer

  The Out-Printer cmdlet sends output to the default printer or to an
  alternate printer, if one is specified.


change MySQL commands to PostgreSQL in a bash script

I think I've cracked it...with a LOT of Googling and chopping of code!

DBS="$(sudo -u postgres psql -l -t | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d')"

and;

sudo -u postgres $PGDUMP -Fc --verbose $db | $GZIP > $FILE

# get all database listing
DBS="$(sudo -u postgres psql -l -t | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d')"

# start to dump database one by one
for db in $DBS
do
        DUMP="yes";
        if [ "$IGNOREDB" != "" ]; then
                for i in $IGNOREDB # Store all value of $IGNOREDB ON i
                do
                        if [ "$db" == "$i" ]; then # If result of $DBS(db) is equal to $IGNOREDB(i) then
                                DUMP="NO";         # SET value of DUMP to "no"
                                #echo "$i database is being ignored!";
                        fi
                done
        fi

        if [ "$DUMP" == "yes" ]; then # If value of DUMP is "yes" then backup database
                FILE="$BACKUPDIR/$NOW-$db.sql.gz";
                 echo "BACKING UP $db";
                      sudo -u postgres $PGDUMP -Fc --verbose $db | $GZIP > $FILE
        fi
done


add a file to your database and set it's filegroup to primary.


How to model a user/group relationship where users are assigned an unique number within the group?

After your comments, I think you need a simple many-to-many table with an additional UNIQUE constraint:

TABLE UserGroups
  GroupID
  UserID
  UserNumber
  PRIMARY KEY (GroupID, UserID)
  UNIQUE (GroupID, UserNumber)
  FOREIGN KEY (GroupID)
    REFERENCES Groups (GroupID)
  FOREIGN KEY (UserID)
    REFERENCES Users (UserID)


Connection issue to Mysql from Apache server

put the new socket location under the [client] section (as well as [mysqld] like:

[client]
socket=/NEW_FOLDER/mysql/mysql.sock


How to Search a Database of Package Deals for Specific Items with the Fewest Unwanted Extra Items

I don't think anyone has come up with a complete answer for your question. This sounds like a complex math question which does not belong on the "Database Administrators" site.

With that said, Joe Celko has an article that might help you. Bin Packing Problems: The SQL