Sure it's possible just TIAS.
CREATE TABLE f ( id int );

Then
$ echo 1 | psql -d test -c 'COPY f FROM STDIN'
COPY 1

In fact all of \COPY is just a wrapper around that functionality. From the docs, on COPY

Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.
So that's why i'm asking, if it is possible to pass data using just sql commands and client data pipe, if it's not possible, what is the reason for this command "FROM STDIN"?

Because it's also possible for the server to open a file handle to a local file and read from that file directly. So there are two modes, the server reads from a file, or the server read reads from STDIN. \copy itself just pipes the data over the socket to the server and tells it to read from STDIN.


When using OLEDB, I believe you should choose the Integrated Security option which would have your connection string look like this:

Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;

Since you're using wallet, you have no username and passwords.

This is assuming your wallet is configured properly in the first place, meaning test with sqlplus is positive.

I hope this helps.


Am I violating any NF rule on my database design?

There are few things to consider other than normalization.  For instance, you have a column for AGE.  Are you going to update that every year? How will you know when to do that? The same goes for years of experience.

There are some columns that will probably have multiple values for each applicant: School, Course, etc.

You may also want to check your optionality on those relationships.  Right now an applicant must have a related exam, but an exam does not have to have an applicant associated with it. I'm guessing that's backwards to how things work in real life.  You have similar issues with all the other relationships.

It helps if you read out the relationships after you create them.


No way how to do that. XE version does not support Java Stored Procedures.
You have to upgrade at least to "Standard Edition One" or "Oracle Personal Edition".

See edition comparsion.


Select values of a column into one row - Sql Server

First you have to use CTE to find ROW_NUMBER() which will sort record then use LEFT OUTER JOIN to achive your result

-- declare temp table
DECLARE @t1 TABLE (
  StudentID bigint,
  SolutionDate date,
  SolutionTime time,
  SongID int
)

-- insert the data into table
insert into @t1 values (0824616,'2015-09-20','00:07:00',01)
insert into @t1 values (0824616,'2015-09-20','00:05:00',02)
insert into @t1 values (0824616,'2015-09-21','00:07:40',01)
insert into @t1 values (0824616,'2015-09-21','00:10:00',03)
insert into @t1 values (0824616,'2015-09-23','00:04:30',03)
insert into @t1 values (0824616,'2015-09-23','00:11:30',03)

-- use CTE to find ROW_NUMBER()
 ;WITH CTE AS
(   SELECT
*,ROW_NUMBER() OVER(PARTITION BY SongID ORDER BY SongID,SolutionTime) AS ColumnNumber
FROM @t1
)

-- then JOIN between CTE and Temp table
SELECT diSTINCT
t.StudentID,t.SongID
,t1.SolutionTime AS TimeA,t2.SolutionTime AS TimeB,t3.SolutionTime AS TimeC
FROM @t1 t
LEFT OUTER JOIN CTE t1 ON t.SongID=t1.SongID AND t1.ColumnNumber=1
LEFT OUTER JOIN CTE t2 ON t.SongID=t2.SongID AND t2.ColumnNumber=2
LEFT OUTER JOIN CTE t3 ON t.SongID=t3.SongID AND t3.ColumnNumber=3
ORDER BY t.SongID ASC


Creating database/tables in SequelPro (or MySQL) from External FTP Server

What you do depends heavily on what you're wanting the end result to be, but there is almost certainly not a way to do what you are thinking of... and if you examine the content of the files, the reason for this should become apparent.

The .7z file format is a compression+container format similar to zip or tar+gzip (tgz) that supports one of the highest compression ratio lossless compression formats in common use, LZMA2, among others.

Almost certainly, the file contains a heavily-compressed copy of the output from mysqldump of your database, either with a single file, a file for each schema, or a file for each table, depending on how they're making the backup.  The potential compression ratio can easily exceed 10:1, so you could be dealing with 52GiB or more of raw data in each of those weeklyh files.

MySQL is a database server (RDBMS) and SequelPro is a MySQL client application, so neither of those things would be able to directly do anything natively with the files sitting in the compressed format, and neither of them is going to fetch the file from the ftp server on its own and do anything useful with it.

If you were to download it and then uncompress it, you should be able to restore the backup onto your own local MySQL server and have a queryable copy of your data as it stood when the last backup was made -- though it would likely take many, many hours... then you could examine that data with SequelPro connected to your local MySQL server... but you're going to have to download that file somewhere, then uncompress and extract the contents, and then either review them with your eyeballs (mysqldump backups are typically human-readable, they consist of the DDL necessary to recreate the empty tables, followed by the DML to insert the existing rows int he tables) or load the onto a local MySQL server if you wanted to actually do something useful with what's in the file.

Ultimately, you'll need to download these files... but to where?

You could automate the download of the file from FTP with a cron job on the same machine as your local MySQL development/test server (or on a different machine) and you could even automate the decompression and restoration of that backup onto your local MySQL server, but that wouldn't be "MySQL" itself doing the downloading or decompressing... that would be other processes that might (or not, depending on what hardware and other resources are available) be running on the same machine -- but this is done at arms-length from the MySQL process itself... not "through MySQL."

If that's what you're asking how to do, that's more of a system administration question than a DBA question.  The last step in that process would be piping the extracted data into the mysql command line client for restoration of its content onto a local server... but before you can consider that, you'll need to examine how the files are structured and consider what you're wanting to accomplish with these files.


How to restore everything (including "postgres" role) from pg_dumpall backup

I ended up using grep for this:

egrep -v '^(CREATE|DROP) ROLE postgres;' dump.sql \
| psql -X -v ON_ERROR_STOP=1 -1 ...


I don't think it's our place to make the decision of you needing MDW or not. Only you and your team with your knowledge of your environments can make that decision, but if you're not using it then I would try to find out who installed it, for what reason and if that reason is still valid.

If you do decide to remove it, be aware there are some 'problems' with the uninstall. See this Connect item for all the pain people have suffered over the years with the uninstall.

You've tagged 2008 R2 and 2014, so the version of SQL Server that MDW is installed on will decide how many issues you will encounter when trying to remove MDW. 2012 added 3 stored procedures to help the process but even they don't quite remove everything. This TechNet post covers the problem and this post covers the solution for 2008 and 2012.


how can I update some table rows using data from existing rows?

In general (if "many rows contain empty values" means "they contains NULL"):

UPDATE store_locator_store_lang t1
  JOIN store_locator_store_lang t2 ON t1.id_store = t2.id_store
                                  AND t1.lang_id = 1
                                  AND t2.lang_id = 2
SET t2.partner = COALESCE(t2.partner, t1.partner),
    t2.address = COALESCE(t2.address, t1.address),
    t2.city = COALESCE(t2.city, t1.city),
    t2.occupation = COALESCE(t2.occupation, t1.occupation);

  The empty value seems to be zero-length string and not null.

UPDATE store_locator_store_lang t1
  JOIN store_locator_store_lang t2 ON t1.id_store = t2.id_store
                                  AND t1.lang_id = 1
                                  AND t2.lang_id = 2
SET t2.partner = CASE WHEN t2.partner = ''
                      THEN t1.partner
                      ELSE t2.partner END,
    t2.address = CASE WHEN t2.address = ''
                      THEN t1.address
                      ELSE t2.address END,
    t2.city = CASE WHEN t2.city = ''
                   THEN t1.city
                   ELSE t2.city END,
    t2.occupation = CASE WHEN t2.occupation = ''
                         THEN t1.occupation
                         ELSE t2.occupation END
/* WHERE '' IN (t2.partner, t2.address, t2.city, t2.occupation) */
    ;


How to find what is contributing the most to "ORA-01555: snapshot too old" error

What I know for sure is ... the statement that errored is not the root cause, it's just a victim

Probably, but by no means certainly.
First thing to understand is that Oracle absolutely, totally, guaranteed-edly will not allow a Dirty Read.  Ever.  Each and every query that it runs is Point-in-Time consistent with the moment (System Change Number) that the query started.
To achieve this, Oracle uses Undo to effectively "roll-back", in-memory, any changes made to the data blocks since the query started.  Even if you're table-scanning a billion row table, any records added to the end of the table while the query is running will not appear in your results.
Obviously, Oracle needs the Undo to achieve this and, if those particular Undo entries get removed from the Undo Tablespace, then Bang!  ORA-01555.
Now, as you quite rightly say, lots of stuff could be making changes and "using up" all the Undo space but, almost as likely, is that the query is taking so long to do whatever it's doing that the Undo get aged out naturally.
How long does this query take to run?

Seconds?  OK, that would be a big worry.
Minutes?  This is the most likely scenario and, in the first instance, I would suggest you look into tuning the query, if possible.
Hours?  What did you expect would happen?  :-)


sqlite3 command does not save database after creation?

There are two databases here: the main database, which does not have a file name, and therefore is a temporary in-memory database, and the database named testdb with the file name testdb.db, which gets attached to the main database.

The SQL script does not mention testdb anywhere except in the ATTACH command, so all tables are created in the main database.

You shold drop the ATTACH command, and create the database file directly:

$ sqlite3 testdb.db < testdb.sql

Alternatively, replace the ATTACH with .open testdb.db to make that the main database. (The .open is not an SQL command and must not be terminated with a semicolon.)


SQL Server uses only 1 CPU of 4 available

SQL Server Express is limited to 1 CPU only

(to add more details it's limited to one socket or four cores, whichever is less)

see http://msdn.microsoft.com/en-ie/library/cc645993.aspx


Tokudb storage engine as mysql-cluster storage engine

I think you are mixing concepts here: Mysql Cluster (assuming you are referring to MySQL NDB Cluster) requires the NDB engine for tables that are part of the cluster. In other words, NDB is the cluster, and MySQL Server, on the SQL layer, requires to use the NDB Cluster storage engine in order to access the data on the cluster.

TokuDB is an on-disk compressed format for MySQL optimized for large datasets. While you may be (or maybe not) able to install this engine in the MySQL NDB Cluster server distribution and use it locally to the node, it will never be able to be used as part of the distributed data, as that requires NDB.

There seems to be support of TokuDB by Galera in MariaDB Cluster 10, providing HA to TokuDB, but not the automatic sharding that NDB provides. I have not tested it, though.

Edit: Tim has told me on the comments that TokuDB is not supported by the Galera protocol in MariaDB (or at least not yet), only MyISAM (partially) and InnoDB, so not an option currently. So probably the only option for HA and TokuDB nowadays is traditional replication.


A follow up on this one.
Fore some reason the Server Firewall was the reason for the slow speed.

I verifyed it serveral times by turning it of and

Funny though, that it only affected one of the clients.

Anywasy, all upp and running now.
Good speed.


multiple similar rows, where only one field has changed

Plan A:  Use SELECT DISTINCT word FROM ... when pulling out.  That is, eliminate dups when fetching.

Plan B:  Have a SET datatype to list 3,6,7.

Plan C:  Use an extra many:many table to map "imposter" to each of the 3 levels.  There would be 3 rows for that word in the new table, but only one row in the current table.

Plan C may be the "proper" way to do it.


Query in SQL SERVER2014 for a report (I need the last ROW of a table)

As with most things SQL-related, there is more than one answer in this case. Here is one solution that might work for you. It relies on a lesser known function called "row_number" (Microsoft documentation here) which, in this case, can help you avoid making multiple passes through temp tables (though that would also work for your needs)

;WITH deliveries_report AS (
    SELECT
        id_order ,
        id_Delivery ,
        itemcode ,
        qty,
        ROW_NUMBER()  OVER (PARTITION BY itemcode ORDER BY id_Delivery DESC) AS row_num
    FROM
        #deliveries
    WHERE
        id_order=9
)

SELECT
    id_order ,
    id_Delivery ,
    itemcode ,
    qty
FROM
    deliveries_report
WHERE
    row_num=1
ORDER BY
    id_Delivery DESC

Just replace #deliveries with the name of your deliveries-table. Good luck!


One possible reason is that you are running in a VMWare environment [and configured Paravirtual SCSI) adapters. See this knowledge base article

  SQL Server reports "Operating system error 1117 (I/O Device Error)" on
  VMware ESX environments that are configured to use PVSCSI adapters

Unfortunately the link to the VMWare knowledgebase in that article is dead but there is an official VMWare KB article here. If this describes your situation you might want to open a support call with VMWare.


MongoDB Benchmark with write-intensive on geo-indexed DB

i have used PostGIS for over a decade now and I can tell you for sure that there is no match for it in the NoSQL world.

how many rows do you have? how large is the thing? Mongo is definitely not going to make you happy. I am pretty sure you did something fishy on the PostgreSQL side to even consider using Mongo. Let us fix it ...


PGpool Promote to master and Slaves wont replicate on failure

Ok so. By design, pgpool does the above.

The follow_master_command in the pgpool.conf file is what you need to use to resolve this issue.

I use the following script (follow_master.sh which i place in the /etc/pgpool-II folder)

#!/bin/sh

################
##
## $1 = node id
## $2 = Old master node id
## $3 = node hostname
##
###############
PGPOOLIP=10.**.**.**
PGUSER=postgres
PGPASS=*************
PGHOME=/var/lib/pgsql/9.3
REMOTE_PGDATA=/var/lib/pgsql/9.3/data

if [ $1 = $2 ]; then
        /usr/bin/pcp_detach_node 10 $PGPOOLIP 9898 $PGUSER $PGPASS $1
else
        sleep 5
        ssh -T postgres@$3 "
        LD_LIBRARY_PATH=$PGHOME/lib:LD_LIBRARH_PATH;
        $PGHOME/bin/pg_ctl -w -D $REMOTE_PGDATA stop"
        /usr/bin/pcp_detach_node 10 $PGPOOLIP 9898 $PGUSER $PGPASS $1
        /usr/bin/pcp_recovery_node 10 $PGPOOLIP 9898 $PGUSER $PGPASS $1
        /usr/bin/pcp_attach_node 10 $PGPOOLIP 9898 $PGUSER $PGPASS $1
fi

I've done testing and it seems to be working fine.

I hope this helps someone in the future.

Thanks

Rob


How can I Use Access to combine tables of information

Use the query wizard to create a new "temporary" table - a 'view' (a query). This you can use then as source for your letter.


Using exists in a query instead of a regular where

I'd suggest you another approach.

COUNT DISTINCT disciplines by employee.

Then use EXISTS to check if one Employee has more than one degree.

select      e.id, e.name
from        Employees e
where exists (select    1
              from      Academics a
              where     a.emp_id = e.id
              having count(distinct a.discipline) > 1);

Or JOIN it with table Employees and show these were NumDegrees > 1

select      e.id, e.name, nd.NumDegrees
from        Employees e
inner join (select    emp_id, count(distinct a.discipline) NumDegrees
            from      Academics a
            group by  a.emp_id) nd
on          e.id = nd.emp_id
where       nd.NumDegrees > 1;


SQL Server 2008 R2 - Capture or be notified when an sql handle is ran

For SQL Server 2008 R2 or earlier, I'd recommend running a SQL Server Profiler session against the server.

On the General tab, in the template dropdown, select "Blank", then on the Events Selection tab, choose "SQL_StmtStarting" from the list of events.  Click the "Column Filters" button, and fill it out similar to this:

When you run this profiler session, the results will only show you rows where the TextData column contains the text you entered.  Alternately, if you have a sql handle you specifically want to track you can enter that in the filter under SqlHandle instead of TextData.

On SQL Server 2012 and above, I'd recommend using Extended Events to do this, however that is out of the scope of this question.


SQL Server 2012 failover cluster node won't start cluster services

I have had not exactly the same but similar issues described here, here and here.

  What can I do to start cluster service on node1 back on?

when a node feels alone in a windows server cluster setting, it loses the quorum.

Then, to start the windows cluster failover service without the quorum, you need to force it to start.

it is very important, because generally you would have something like this:

windows cluster name - the name that all the applications try to connect to: le't's say it is called the_server
but actually the_server does not exist, what exist is node1 and node2 (plus maybe a quorum disk or a shared storage for quorum purposes) - so even if you have only one node left, you need the windows cluster failover service running, so that all applications can find the_server

The way I would do that is using powershell:

Import-Module FailoverClusters

$node = "Always OnSrv02"
Stop-ClusterNode -Name $node
Start-ClusterNode -Name $node -FixQuorum

(Get-ClusterNode $node).NodeWeight = 1

$nodes = Get-ClusterNode -Cluster $node
$nodes | Format-Table -property NodeName, State, NodeWeight

this is described in detail here:

Force a windows server failover cluster to start without a quorum

To force a cluster to start without a quorum:

Start an elevated Windows PowerShell via Run as Administrator.
Import the FailoverClusters module to enable cluster commandlets.
Use Stop-ClusterNode to make sure that the cluster service is stopped.
Use Start-ClusterNode with -FixQuorum to force the cluster service to start.
Use Get-ClusterNode with -Propery NodeWieght = 1 to set the value the guarantees that the node is a voting member of the quorum.
Output the cluster node properties in a readable format.

First, get hold of the failover cluster manager application, let's have a look at what we have got:

This is how it looks like:

Check the servers inside the nodes:

This is the normal way to manage the clustering services.

However, when there is no quorum we need to force the service to start.
For that you need to follow this link.

just don't forget the way to run the powershell:

The way to run this script is command by command
First this:

Import-Module FailoverClusters

Then

$node = "SQLPROD2"
#Stop-ClusterNode -Name $node
Start-ClusterNode -Name $node -FixQuorum

(Get-ClusterNode $node).NodeWeight = 1

$nodes = Get-ClusterNode -Cluster $node
$nodes | Format-Table -property NodeName, State, NodeWeight

and all good - with only one node up and running - just until we add a new node (but no downtime and no application is upset and not connecting to the_server):


query performance gains by removing operator hash match inner join

the following links will provide a good source of knowledge regarding execution plans.

From Execution Plan Basics — Hash Match Confusion I found:

  From
  http://sqlinthewild.co.za/index.php/2007/12/30/execution-plan-operations-joins/

  "The hash join is one of the more expensive join operations, as it
  requires the creation of a hash table to do the join. That said, it’s
  the join that’s best for large, unsorted inputs. It is the most
  memory-intensive of any of the joins

  The hash join first reads one of the inputs and hashes the join column
  and puts the resulting hash and the column values into a hash table
  built up in memory. Then it reads all the rows in the second input,
  hashes those and checks the rows in the resulting hash bucket for the
  joining rows."

  which links to this post:

  http://blogs.msdn.com/b/craigfr/archive/2006/08/10/687630.aspx

Can you explain this execution plan? provides good insights about the execution plan with, not specific to hash match but relevant.

  The constant scans are a way for SQL Server to create a bucket into
  which it's going to place something later in the execution plan. I've
  posted a more thorough explanation of it here. To understand what the
  constant scan is for, you have to look further into the plan. In this
  case, it's the Compute Scalar operators that are being used to
  populate the space created by the constant scan.

  The Compute Scalar operators are being loaded up with NULL and the
  value 1045876, so they're clearly going to be used with the Loop Join
  in an effort to filter the data.

  The really cool part is that this plan is Trivial. It means that it
  went through a minimal optimization process. All the operations are
  leading up to the Merge Interval. This is used to create a minimal set
  of comparison operators for an index seek (details on that here).

In this question:
Can I get SSMS to show me the Actual query costs in the Execution plan pane?
I'm fixing performance issues on a multistatement stored procedure in SQL Server. I want to know which part(s) I should spend time on.

I understand from How do I read Query Cost, and is it always a percentage? that even when SSMS is told to Include Actual Execution Plan, the "Query cost (relative to the batch)" figures is still based on cost estimates, which can be far off actuals

Measuring Query Performance : “Execution Plan Query Cost” vs “Time Taken”
gives good info for when you need to compare the performance of 2 different queries.

In Reading a SQL Server Execution plan you can find great tips for reading the execution plan.

Other questions/answers that I really liked because they are relevant to this subject, and for my personal reference I would like to quote are:

How to optimise T-SQL query using Execution Plan

can sql generate a good plan for this procedure?

Execution Plans Differ for the Same SQL Statement


Restore database with zero size log file

No, there is no way. Restores really is meant to recreate the original database that was backed up. There are tools that allow you to query directly a backup w/o actually restoring it (Idera's SQL Virtual Database) but those are horrendously slow.


PostgreSQL ANALYZE execution time over 24h (still running)

After several hours of research and examining the current situation I think I managed to solve the issue. (Many thanks to fellow user ypercube for the inspiration and for Erwin Brandstetter who in parallel came to the same solution.)

So there were several layers of the problem.

1.) UPGRADE

Upgrading with pg_upgrade 9.3.2 --> 10.5 should be made in two steps. First within the same line (9.3.2 --> 9.3.25) and then to 10.x (10.5 in my case)

I made a direct upgrade and it seems that it was the root cause of the problem.

2.) HASH INDEXES

It seems that hash indexes suffered from some strange errors in postgres which have been corrected already but using indexes of the pre-correction versions leads to errors

3.) FROZEN TASKS

It does make sense to look for postgres processes which are running for non-realistic long time. (See query in the question.) In my case it turned out that the recreation of indexes stuck somehow and several other tasks have been blocked as well.

It is safe to cancel most of them with SELECT pg_cancel_backend(__pid__); where pid is the process ID found in the result set of the before-mentioned query. So I did it. I even stopped the autovacuum processes.

4.) MEMORY HANDLING

When after all of this I finally thought I was able to delete and create the new indexes I faced the next problem. After about one minute all maintenance queries exited with an error message:

ERROR: out of memory
DETAIL: Failed on request of size 22355968.
SQL state: 53200

It seems that the memory handling changed between 9.3 and 10. I had to reduce the amount of maintenance_mem in the config:

maintenance_work_mem = 64MB     # min 1MB

It was 512MB before and although the server has 32GB of RAM it was still not working with that.

5.) RECREATING INDEXES

After all it was possible to recreate indexes (drop old ones and create new ones). It would have been easier with a proper script but I had to do it manually. Don't forget that creating and dropping indexes locks the table so in productive environment (like mine) you should do that CONCURRENTLY.

Edit:

I also realized that using hash indexes in my specific case was not really meaningful so I decided to change them to btree at the recreation.

6.) ANALYZE

After recreating the indexes it is necessary to run an analyze on the affected tables (or the whole DB). After all the above actions it will run surprisingly quick even in a huge DB like mine.

The indexes are again being used and the performance is perfect again. So this is a happy end in my first StackExchange post. :-)


Always On Listener Behavior - DBs removed from group

How large are your transaction log files? I'd be prepared to try going with leaving them disconnected, provided no reindexing, bulk ops etc go on during the outage, and provided you can be 100% confident it will only be 6 hours.

In my experience OLTP transaction log growth is normally modest, and provided your t-logs are a reasonable size, you should be ok.

Difficult to test though as normal OLTP transactional throughput is tricky to simulate, although profiling for replay could be a way of measuring it.


Is it possible to have the transaction log file write to a disk on a remote machine?

It is supported to have database files on network share, but Its inviting a disaster as any network blip might corrupt your database.

  Microsoft recommends that you store database files either on Local Disk or on Storage Area Networks (SANs).

You have to tune your data warehouse loading process. There are many parameters that can be tuned to have minimally logged operations and fast loading of data in sql server. e.g.

Trace Flag 610 - controls minimally logged inserts into indexed tables.
I have listed few items in this answer.

Also, if you want to be proactive, then create an alert for Transaction log full, so you get notified when the log is about to get filled up.

Read up on :

The Data Loading Performance Guide
Load 1TB in less than 1 hour


It's critical that you do not enable RCSI on a database without the application vendor and your own developers certifying that they support it. The reason is that the behaviour of all your read queries will change, but not actually break in such a way that replaying your workload will detect. Read section 3 at https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/.

To answer your specific questions:

Switching on will be near-instantaneous (assuming only one DB connection).
Yes, switching off is just as painless.
See above.
Cleanup happens by itself. Section 2 of the post above has some things to consider.


Can't create database on volume mounted as folder (Error Msg 5123 & 1802)

Creating SQL Server Data files at the root of a volume is problematic, due to permissions issues. I always recommend creating a folder on the root of the volume, then placing data files inside that folder (this is also Microsoft's recommendation). Setting & verifying permissions at the root of the drive does not always work the same as setting permissions at the folder level.

When mounting the volume as a lettered drive, this means your data files would never go directly in D:\ but instead in D:\SomeFolder\. In your case, you are using a mount point where UserDb is the root of the volume. I'd suggest creating an additional folder inside your mount point: D:\...\Data\UserDb\SomeFolder\. Then, set permissions to make sure the SQL Server service account has full control for the SomeFolder directory.

Note that this guidance from Microsoft is still true:

  Do not install SQL Server to the root directory of a mount point, always specify a subdirectory for all files. This has to do with how permissions are granted. If you must put files in the root of the mount point you must manually manage the ACLs/permissions.

And also this more explicit advice for database files specifically:

  Warning: SQL Server does not support use of mount volume / mount point root directories for SQL Server databases.

Placing files directly in the root of the mount point means you'll have to manage permissions directly on every file to ensure they are correct. In the case of creating a new database (either from backup or from model), this can be problematic. As a result, it is best practice to never put files in the root of the mount point or drive. You can make it work, but it's not supported and not a good idea.


Database structures for similar profiles

I have limited to 10 user_values for this example. Replace the two instances of "1" with the user_id of the user that you are matching profiles to.

SELECT
    user_id,
    @score :=
        (user_values.value1 = cur_user_values.value1) +
        (user_values.value2 = cur_user_values.value2) +
        (user_values.value3 = cur_user_values.value3) +
        (user_values.value4 = cur_user_values.value4) +
        (user_values.value5 = cur_user_values.value5) +
        (user_values.value6 = cur_user_values.value6) +
        (user_values.value7 = cur_user_values.value7) +
        (user_values.value8 = cur_user_values.value8) +
        (user_values.value9 = cur_user_values.value9) +
        (user_values.value10 = cur_user_values.value10)
        AS score
FROM
    user_values JOIN (
        SELECT
            value1,
            value2,
            value3,
            value4,
            value5,
            value6,
            value7,
            value8,
            value9,
            value10
        FROM
            user_values
        WHERE
            user_values.user_id = 1
    ) cur_user_values
WHERE
    user_id != 1
ORDER BY
    score DESC;

This results in a response like so (I was testing with only 5 users):

In my case, score will be a number between 0 and 10. In your case it will be a number between 0 and 40.


How to run insert statement as much times as select statement gives unique values?

To do this you can use insert ... select:

insert into banks(ID, NR, MONEY)
select
   gen_id(gen_name, 1),
   ID,
   3000
from CLIENTS

If you have a trigger on CLIENTS that generates the ID on insert, or - Firebird 3 - ID is an identity column, then you can simplify this to:

insert into banks(NR, MONEY)
select
   ID,
   3000
from CLIENTS


Tools to monitor Oracle Database Performance on Standard Edition

Note: I've never used this, so please edit this answer if you have.

Lab128

  Lab128 provides additional savings by implementing ASH functions and
  not requiring the purchase of ODP.

Pros:

Response Time Analysis
RAC support
Agentless
Only $299 for a named user connecting to two instances and only $499 for a named user connecting to an unlimited number of instances.

Cons:

Interface isn't very polished.
Lack of alerts.
One writer/maintainer.
No web interface.
Not server based.


Is it safe to delete the innodb schema on a new Amazon RDS instance?

There is a reason you cannot touch the innodb database.

This is where Amazon stores the system tablespace file ibdata1

If you run

SHOW GLOBAL VARIABLES LIKE 'innodb_data_home_dir';

you might see something like this

/rdsdbdata/db/innodb

It will point to this folder as the base of InnoDB.

If you run

SHOW GLOBAL VARIABLES LIKE 'innodb_log_group_home_dir';

you might see something like this

/rdsdbdata/log/innodb

This is where ib_logfile0 and ib_logfile1 live.

If you run

use innodb
show tables;

you will see nothing because there are no .frm files in it.

Even though the innodb database contains no storage engine data files (.frm or .ibd), you cannot drop the innodb database because the folder /rdsdbdata/db/innodb is not empty.


SQL Trigger to Insert a row in Table B is the record is inserted in Table A

Just use your "source"  as supplier for it.
Because you are using variables , which holds only one value , this is way is working fine with one statement.
MERGE and trigger statements can work with multiple rows at once, not just one , so just use the special INSERTED table inside the MERGE statement; just replace the your MERGE statement with next MERGE statement, inside your trigger code
Here are some samples : example1 ,
example2
MERGE Site AS [target]
USING (SELECT [i].MV2ExternalKey,i.SiteID,i.siteName,@DeleteIndicator as DeleteIndicator FROM INSERTED AS [i]) AS source
ON ([target].Name = [source].MV2ExternalKey)
WHEN MATCHED THEN
UPDATE  SET
         Name = source.SITEID,
         Description = source.SITENAME,
         DeleteIndicator = source.DeleteIndicator

WHEN NOT MATCHED THEN
INSERT (
       [Name]
      ,[Description]
      ,[ExternalKey]
      ,[DeleteIndicator])
VALUES( source.siteID,source.siteName,source.MV2ExternalKey,source.DeleteIndicator)

so , the trigger code will become:
CREATE trigger [dbo].[InventSiteAfterInsert] on [dbo].[InventOperationalSiteStaging]
After Insert,Update
AS

MERGE Site AS [target]
    USING (SELECT [i].MV2ExternalKey,i.SiteID,i.siteName, 0 as DeleteIndicator FROM INSERTED AS [i]) AS source
    ON ([target].Name = [source].MV2ExternalKey)
    WHEN MATCHED THEN
    UPDATE  SET
             Name = source.SITEID,
             Description = source.SITENAME,
             DeleteIndicator = source.DeleteIndicator

    WHEN NOT MATCHED THEN
    INSERT ([Name],[Description],[ExternalKey],[DeleteIndicator])
    VALUES( source.siteID, source.siteName, source.MV2ExternalKey, source.DeleteIndicator);

As @Charlieface mentioned in comment section, in case of an update with the same values, some people, will not perform update.
USING (SELECT [i].MV2ExternalKey,i.SiteID,i.siteName, 0 as DeleteIndicator FROM INSERTED AS [i]) AS source

will become
USING (SELECT [i].MV2ExternalKey,i.SiteID,i.siteName, 0 as DeleteIndicator FROM INSERTED AS [i]
EXCEPT
SELECT d.MV2ExternalKey,d.SiteID,d.siteName, 0 as DeleteIndicator FROM DELETED AS d) AS source

ps: there are some bugs on merge statement, so be careful
link1, link2


Efficiently querying a very large MySQL table with C#

Workbench tacks on a LIMIT clause.  And, since you have the perfect index for such, it stops short.

How many rows are you expecting?  What will you do with that many rows?

It is probably possible for a C# client to configure the connection show that the rows will be delivered as they are found, thereby showing you some output before it timesout.  Such is rarely used because delivering giant resultsets is rarely practical.


Why should I apply a CU for SP2 that was released after the release of SP3?

If you plan to remain on SP2 for now, then you can install any SP2 CUs that you determine are needed.  To specifically answer why you should install an SP2 CU at this point, that is somewhat of a murky answer.  You don't really need to install a CU unless it has a fix you need, so if you don't have any issues that the CUs fix, then you might consider just not installing them.

If you upgrade to SP3, then you can start installing SP3 CUs.

As to why the SP2 CUs in question even exist, Microsoft continues to release CUs and security updates for older service pack levels for some time so that you are not forced to update to the latest SP immediately in order to be able to install the fixes they addresses.

CUs generally have fixes for issues that affect a limited number or customers, so it's not critical to upgrade every time a CU is released.  You certainly don't want to upgrade production systems as soon as a CU is released unless there is something in it that addresses an issue on your system.

More importantly, though, are the security updates, and it is advisable to get those installed and tested in non-production environments and then to production sooner rather than later.  If there is a data breach, you want to be able to say that all security updates are installed, even if the breach was unrelated to those updates.

You can check how long a given service pack will be supported/updated at Search Product Lifecycle, where you'll see the following:

SQL Server 2014 Service Pack 1 has a support end date of 10/10/2017 (i.e., you should not be running SP1 as you've missed all security updates since 10/10/2017)

SQL Server 2014 Service Pack 2 has a support end date of 1/14/2020 (i.e., you need to plan to upgrade to SP3 soon because security updates for SP2 will not be released after 1/14/2020).

SQL Server 2014 Service Pack 3 has no support end date published.


How to ensure a field is populated before use in a trigger

Community wiki answer:

You could use a computed column for BedId. No need to be actually stored.

Would something like Bedid AS CONCAT(WardId, '-', Id) work for you?


How to restore SQL Server 2008 R2 backup to SQL Server 2008

Your issue isn't with the 32-bit vs. 64-bit, but it's that you are trying to restore a database on an older version.

  The database was backed up on a server running version 10.50.2600.
  That version is incompatible with this server, which is running
  version 10.00.4064.

This means that the source DB is 2008 R2, and destination DB version is 2008 (not R2).

The Best way to do this is to Generate the script of Database and execute it on other machine.

More Reading

Raj


How to use 'Bind Variable' with 'With clause' in a Dynamic query using PLSQL

You got it wrong, I'm afraid. This is what your query evaluates to:
with binds as
  (select :bind1 as amount1,
          :bind2 as amount2
   from dual)
select t.c_num
  from z_test_a t,
       binds b               --> this is "B"
  where 1 = 1
intersect
select c_num
  from z_test_b
  where amount = b.amount1   --> you can't reference "B" here

Instead of intersect, you could:

use :bind1 in z_test_b, i.e.
intersect
  select c_num
  from z_test_b
  where amount = :bind1

or join z_test_b with z_test_a and binds (but I presume that former suggestion is simpler because of possible where clause; currently, it is useless (where 1 = 1), but I presume that it is here just for illustration. Something like this:
-- CTE you already have; remove WHERE from it
with binds as
  (select :bind1 as amount1,
          :bind2 as amount2
   from dual)
select t.c_num
  from z_test_a t,
       binds b
                      --> WHERE's not here any more; it is moved to join

-- JOIN
, z_test_b z
where z.amount = b.amount1       --> moved here


MySQL Not Recovered After Running Out of Space

you could try to repair the table (or tables) with:

mysql -u root -p -e "repair table <tablename>"


Use cases for hstore vs json datatypes in postgresql

Taken from http://www.depesz.com/2013/03/30/waiting-for-9-3-add-new-json-processing-functions-and-parser-api/ per dezso's comment:

  Given current limitations for both datatypes, I would say that they
  should be used in different scenarios:

  if you want indexable searches, and you’re fine with “no-nesting”
  limitation: use hstore. if you need nested, complicated structures,
  and don’t care about indexing – use json. and if you need the best
  from both world – you’ll have to wait to see what, and when, the
  future will bring.


Cannot disable CDC - sp_cdc_disable_db_internal gives error 8152: 'String or binary data would be truncated'?

I got around this by manually dropping the objects the SP was attempting to drop. In the end, the SP ran but required a manual commit afterwards. No more CDC. I was also able to enable CDC successfully.


Find all values not present for other values of other column

If your pairs are unique, as in the example you gave, you want the values of b that appear only once, and this would be a solution:

SELECT a,b
FROM tbl1
WHERE b in (
  SELECT b
  FROM tbl1
  GROUP BY b
  HAVING (count(b)) = 1
  )

You can see it in this fiddle http://sqlfiddle.com/#!15/c82d3/3


What should my SPN entries look like for each SQL instance?

If you're only using TCP/IP to connect to your instances, you only need the ports specified.  The Instance Names are used when connecting to the SQL Instances via the Named Pipes protocols.  Sadly the MS article doesn't come right out say which format is required for which protocol, but it is derived from (many tests in my environment) and the following MS article sentance:

  For named pipes and shared memory connections, an SPN in the format MSSQLSvc/FQDN:instancename is used for a named instance and MSSQLSvc/FQDN is used for the default instance.

Regarding FQDNs vs NETBIOS names, I will recommend FQDNs as they aren't as prone to problems if you run up against random DNS server issues.

Lifted from my blog post on the matter, formats should look as follows:

The source reference from MS can be found here.

Now to make your Network Admin's Day (e.g OU configuration that allows for Self Registering SPNs)

Your Network Admin can create an OU on the domain which contains all your SQL Server Service accounts that can be configured in such a manner that the Service Account can create an SPN for itself and itself alone.  The method is mainly following Ryan Reis's blog, but has some slight tweaks so that over-grants are not performed.

This process describes the creation of an OU on the domain that allows accounts within it to self-register their own SPNs:

As an account with elevated rights on the domain, open ADSI Edit (adsiedit from the command prompt)
Right-Click on ADSI Edit -> Connect to...
Connect to Default naming context
Navigate to/Create the OU container holding the service accounts you wish to grant SPN rights to
Right-Click on the OU -> Properties
Click on the Security Tab
Click the Advanced button
Highlight SELF and click Edit... or if the SELF special user does not show up in the list of Group or User names, click Add... and enter SELF for the object name
Click the Properties tab
Select Descendant User objects from the drop down list next to Apply to:
Note: This is the slight adjustment to the steps outlined in Ryan's blog post for the reasons better outlined by this ServerFault/StackExchange post.
Check the Allow box next to the following:

Read servicePrincipalName
Write servicePrincipalName

Click Ok (on permissions entry window)
Click Ok (on Advanced Security Setings window)
Click Ok (on OU properties window)
Add service accounts running SQL Server services to the OU
(Optionally) Restart SQL Server Services running under said account(s)
Enjoy Treats

After following the above steps, the OU container in question is now configured such that any account added to it will be able to register and delete SPNs for itself and itself alone.  This is exactly the right amount of permissions as these accounts will be unable to trample over SPNs registered by other accounts.

The purpose of restarting SQL Server in step 16 is to ensure the SPNs are registered as expected.  SQL will try to remove any registered SPNs on shutdown and add them on Startup, so the restart is really only needed if no SPNs currently exist for said SQL Server service.

The final note on this approach is that if you are running SQL Server in a traditional Failover Clustered Instance (FCI) configuration, it is NOT Recommended to add this instance's service account to this OU, per KB 2443457.

I really need to post Part 2 of my Kerberos series...


Database backups in Oracle - Export the database or use other tools?

The advantage of RMAN is PITR - point in time recovery. You can take an RMAN backup of the DBFs and an RMAN backup of the archived redo logs and recover your database to any point in time up to the time of the most recent archived redo log backup. The disadvantage of this approach is that it is very coarse-grained - you can only recover at the level of the tablespace.

The advantage of exp/expdp is that you have a consistent copy of the database that you can just import into a freshly created blank database. However, you cannot roll it forward - it is at this point a completely separate, independent database with no logical relationship to its original. But it is easy to recover just a single table, or even a few rows from an export.

A good approach would be regular RMAN full and incremental datafile backups, continuous backups of archived redo logs (e.g. as soon as one archivelog backup completes, start the next one straight away so you are "streaming" the logs to tape) then educate your users in the use of exp/imp so they can perform their own "backups" (in the case of wanting an actual copy of the data) and flashback (so they can do their own recoveries in the case of DML going wrong).

Remember - a backup is intended for the DBA to recover from catastrophic failure of the hardware. It is not for the benefit of end users (or you will spend your whole time doing restores to a test system and copying a few rows back to the original!).


There are two modes of authentication on SQL Server as you can see below:

Windows Authentication - If only this is enabled then, you can only create domain account(windows account) and login will have format like --> Domain_Name\Login_Name
SQL Server and Windows Authentication - Also called mixed more, if this is enabled then you can either have windows authenticated(domain account) or SQL login.

The difference between above two is - You need to explicitly provide password for the SQL authentication whereas in the Windows authentication if you have already logged in with same domain account then, no need to provide password and other policies are taken care at DC(Domain Controller or Active Directory) level.

In case, you have logged in to windows using account A(for example) and want to use account B, you can do so by doing CTRL+SHIFT+Right Click and choose "Run as different user" for the application and you may input your ID(with domain i.e. Domain_Name\Login_Name) and password for account B as below:

For creating a new windows authentication account and other relevant details, you may refer below link:

https://www.tutorialgateway.org/create-windows-login-in-sql-server/

I hope above helps.


INSERT INTO merchant_seq VALUES (NULL);
SET @last_ID = (SELECT MAX(merchant_id) FROM merchant_seq);
SET NEW.merchant_id = CONCAT('MC', LPAD(@last_ID, 10, '0'));


Assuming the column is an int, and not a varchar column, you could do this:

datepart(year, c.cko_date + 25567)

25567 is the number of days between 1900-01-01 and 1970-01-01; essentially this adjusts for the discrepancy between unix and SQL Server datetime.

The SQL Server datetime is designed around 1900-01-01 being the "epoch".  It may help to think of SQL Server dates as an decimal value representing the quanta of time until or since January 1st, 1900.  Times are the decimal portion such that midnight is 0.0, noon is 0.5, 6pm is 0.75.  The valid range of values that can be stored in a datetime column is 1753-01-01 00:00:00 to 9999-01-01 23:59:59.997.  Numerically, this corresponds to the range starting at -53690.0000000 and ending at 2958463.99999998.

Unix uses 1970-01-01 00:00:00 as its epoch.  By convention, the epoch starts in Universal Coordinated Time, or UTC.  For North America, the Central Daylight Time timezone is 5:00 hours behind UTC.  Therefore, to accurately convert a value from a Unix epoch time value into a datetime value in CDT requires adjusting for that 5-hour difference.  Therefore, a slightly more accurate example of converting from a Unix-time expressed in a number of days would be:

--declare an integer representing the number of days since 1970-01-01
DECLARE @days_since_unix_epoch int = 17674; --2018-05-23
--show the datetime value in UTC
SELECT UTC = CONVERT(datetime, @days_since_unix_epoch + 25567)
--show the datetime value in North American CDT
   , CDT = CONVERT(datetime, @days_since_unix_epoch + 25567) - CONVERT(datetime, '05:00:00');

The results:

╔═════════════════════════╦═════════════════════════╗
║           UTC           ║           CDT           ║
╠═════════════════════════╬═════════════════════════╣
║ 2018-05-23 00:00:00.000 ║ 2018-05-22 19:00:00.000 ║
╚═════════════════════════╩═════════════════════════╝

While we're technical, typically the Unix epoch is stored as the number of milliseconds since 1970-01-01 00:00:00, not the number of days.  The number of milliseconds can be quite difficult to deal with if extreme accuracy is a concern since there have been a number of adjustments to time since 1970 in order to deal with leap-seconds.  According to the wikipedia article I just linked to, there have been 26 one-second additions to time since 1972; to accurately deal with dates between 1970 and today, you need to appropriately add between 0 and 26 seconds.  I am uncertain if SQL Server's datetime structure accurately understands those adjustments.

Note also that SQL Server's datetime structure has a resolution of 3 milliseconds.  Consider the following:

SELECT [1ms] = CONVERT(datetime, '2018-05-22 00:00:00.001')
     , [2ms] = CONVERT(datetime, '2018-05-22 00:00:00.002')
     , [3ms] = CONVERT(datetime, '2018-05-22 00:00:00.003')

The output:

╔═════════════════════════╦═════════════════════════╦═════════════════════════╗
║           1ms           ║           2ms           ║           3ms           ║
╠═════════════════════════╬═════════════════════════╬═════════════════════════╣
║ 2018-05-22 00:00:00.000 ║ 2018-05-22 00:00:00.003 ║ 2018-05-22 00:00:00.003 ║
╚═════════════════════════╩═════════════════════════╩═════════════════════════╝

If you need better resolution, you should consider using the datetime2 data type since it supports microsecond-precision, and a potentially much larger range of dates and times:

  Date range: 0001-01-01 through 9999-12-31
  Time range: 00:00:00 through 23:59:59.9999999


Problem
When a Master is also a Slave, you need to have log-slave-updates.
From what you described, each Master does not have log-slave-updates configured.
Solution
STEP 01) STOP SLAVE; on Slave1
STEP 02) STOP SLAVE; on Slave2
STEP 03) STOP SLAVE; on Master1
STEP 04) STOP SLAVE; on Master2
STEP 05) On Master1, add this to /etc/my.cnf
[mysqld]
log-slave-updates

STEP 06) On Master2, add this to /etc/my.cnf
[mysqld]
log-slave-updates

STEP 07) On Master1, Run service mysql restart --skip-slave-start
STEP 08) On Master2, Run service mysql restart --skip-slave-start
STEP 09) START SLAVE; on Slave1
STEP 10) START SLAVE; on Slave2
STEP 11) START SLAVE; on Master1
STEP 12) START SLAVE; on Master2
That's it. Everything should replicate properly from here.
Give it a Try !!!
I have discussed this before

Dec 05, 2012 : MySQL Slave Relay Logging but not logging Binary Log
May 29, 2012 : mysqld-multi with first DB as Slave and second DB as Master
May 07, 2012 : Setting Circular Replication in mysql


How can you view the IP addresses of active MongoDB Atlas connections?

If I understand correctly, this is about how to view ip addresses that got connected to your mongoDB Atlas cluster, I would say you can see number of connections established to a cluster by just logging into your Atlas mongoDB cloud dashboard (Atlas login). But if you want details on connections established, then you can download logs for that particular cluster as a file, You can navigate to it as given below, but you might only see this option for paid versions, Technically db.currentOp(true) should work, but for some reason it didn't, not sure why, maybe unsupported !! (mongoDB-unsupported-commands) :


Performance gain by moving a large table from a database and referencing with a synonym

You don't have to move it to it's own database. You may be able to get a performance improvement by simply moving it to it's own filegroup within the existing database.  The filegroup could then contain one or more files that are on a separate disk(s) from the ones the rest of the database is on.


Trying to recover/import/transfer database from MySQL data dir

I managed to find a workaround for this but I am not very happy about it.
I installed my previuos setup which was EasyPHP and copied my old MySQL *data* dir into the EasyPHP *MySQL* data directory.

This time around the database was able to read the .ibd / .frm files correctly.(even though these files did not change at all)

I then used phpMyAdmin to export the database(this time around all tables/data was stored in the sql file)

I then disabled EasyPHP enviroment and started up my new enviroment and used phpMyAdmin->import with the new sql file which loaded the data correctly.

Overall I think its pretty poor that MySQL cant simply output some type of notice stating that the .ini enviroment variables are not set corresponding to the .ibd / .frm files rather than just output incorrect information and leave the user in the dark with all sorts of angles to think about. After googling around it seems many people seem to believe that their data is corrupt when that's really not the case.


I am trying to run this code and I get an error at line 11

CREATE TABLE IF NOT EXISTS `tblproduct` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `code` varchar(255) NOT NULL,
  `image` text NOT NULL,
  `price` double(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_code` (`code`)
)  <<<<------- put semi-colon here.

Your problem here is with the last line of your CREATE TABLE -  it should be ); and not just the bracket i.e. ) on its own! It's the sort of error that's very difficult to spot if you're trying to concentrate on the code - it's driven me mad a couple of times, I just couldn't see it! p.s. welcome to the forum!


plpgsql - stored procedure Comma separated values in parameters

The best solution is to not pass a single varchar parameter but an array:

CREATE OR REPLACE PROCEDURE bhuvi(p_catalogs text[], p_tbls text[])
 LANGUAGE plpgsql
 AS $$
 DECLARE
   rec RECORD;
    v timestamp;
 BEGIN
    FOR rec IN select table_schema, table_name
               from information_schema.tables
               where table_catalog = any (p_catalogs)
               and table_type='BASE TABLE'
               and table_schema not in ('pg_catalog','information_schema')
               and table_name = any (p_tbls)
   LOOP
     select now() into v;
     RAISE INFO ' % printing... schema = % and table = %',v, rec.table_schema, rec.table_name;
 END LOOP;
 END;
$$;

Then you can call it e.g. like this:

call bhuvi(array['schema_1',schema_2'], array['table_1', 'table_2']);

If you can't change the calling code to deal with array, convert the comma separated strings to arrays

CREATE OR REPLACE PROCEDURE bhuvi(p_catalogs text, p_tbls text)
 LANGUAGE plpgsql
 AS $$
 DECLARE
   rec RECORD;
    v timestamp;
 BEGIN
    FOR rec IN select table_schema, table_name
               from information_schema.tables
               where table_catalog = any (string_to_array(p_catalogs.','))
               and table_type='BASE TABLE'
               and table_schema not in ('pg_catalog','information_schema')
               and table_name = any (string_to_array(p_tbls, ','))
   LOOP
     select now() into v;
     RAISE INFO ' % printing... schema = % and table = %',v, rec.table_schema, rec.table_name;
 END LOOP;
 END;
$$;


COUNT and SUM at the same time after removing duplicate rows

Select A.status,count (A.record_id),sum(A.hits)
from
(Select distinct status,record_id,hits from YourTable) A
group by A.status


How to use SUBSTRING using REGEXP in MySQL

This would need to use the LOCATE and SUBSTRING syntax to get the information out of the string.
The basic locate syntax you would need is explained here.
LOCATE (search str, str, [position])

search str = A string which will be searched for.
str =A string which is going to be searched.
position (optional)= Position from where (within the second argument) the searching will start .

While the substring function you need is explained here
SUBSTRING(str, pos, len)

str=  A string.
pos=  Starting position.
len=  Length in characters.

The easier way of viewing this is to think of substring as the following
SUBSTRING(str FROM pos FOR len)
The sytax I used to get the second word is below, I took advantage of the spaces that are constantly around the second word you are trying to extract.
declare @String varchar(50) ='Lorem D9801 ipsum dolor sit amet'

SUBSTRING
(
@String,
LOCATE(' ', @String),
LOCATE(' ', @String, (LOCATE(' ', @String) + 1)) - LOCATE(' ', @String)
)


Getting timeouts and backup errors after mirroring and log shipping is setup

If your server has only  4GB of RAM, AWE isn't going to help you much.  AWE is designed for when you have greater than 4GB of RAM you need to use.

Timeouts are harder to define.  The timeout period itself is likely defined in your application, so it's not inherently SQL Server that's causing the timeouts.  However, what you're running into are queries that take to long to complete.  To troubleshoot this, I would first start by reviewing the wait stats to identify specifically what is causing things to drag in your SQL Server.  Some waits to look for:

BACKUPTHREAD means your queries are waiting on backup operations to complete.  Other backup waits could indicate problems.
Memory pressure could be any number of waits, but look for things like RESOURCE_SEMAPHORE or IO waits.  Also, check the trend on your Page Life Expectancy in Perfmon (SQL Server: Buffer Manager -> Page Life Expectancy).  If this continually trends low, your pages are cycling out of memory to quickly.

Another thing you can do is check how long your log shipping backups are taking to complete:

SELECT
    database_name
    ,backup_finish_date
    ,datediff(ss,backup_start_date,backup_finish_date) SecondsToBackup
FROM
    msdb.dbo.backupset
WHERE
    type='l'
ORDER BY
    backup_finish_date desc;

As for resolution, if your log shipping is happening all at the same time, you might want to try and stagger that out.  If it isn't necessary to log ship every 5 minutes, I'd try and go with your 15 minutes and stagger each database off by 5 minutes so that they're running separately.

The other thing is to consider a hardware upgrade.  The box your describing sounds fairly out of date, considering there are laptops you can purchase now that have more horse power than what you're describing.  If you can move the server to a virtual machine, you'll be able to immediately upgrade to better resources, which could resolve these issues for you.


Found the answer- it's actually a bug on 2014 RTM, please see:
https://connect.microsoft.com/SQLServer/feedback/details/850260/sql-2014-sp-send-dbmail-64kb-query-result-limit

The bug is fixed in SQL Server 2014 CU4, see KB3004195.


Set up common database server for vb.net application

First you'll need to nominate a computer that's going to act as the database server (obviously this will need to be one that all machines running the application can connect to over the LAN and that is always going to be running when the application is in use). We'll call this machine "server"

On "server" download and install SQL Server Express from here: https://www.microsoft.com/en-us/download/details.aspx?id=42299 - you'll want the version that is called "ExpressAndTools" and make sure you get the appropriate 32/64bit version depending on your hardware and OS (installing with default settings should work well enough for your purposes).

Next you'll also need to download and install SQL Server Management Studio on the computer that currently has the application/database on (it's from the same link as above and is just called "MgmtStudio"). Once that's installed open it up and should pop a dialog asking for details of where to conenct. Enter "(localDB)\V11.0" as the Server name, select "Windows Authentication" and click on "Connect". You should then see an entry in the left hand column called something like "(localDB)\V11.0 (SQL Server V11.0 - domain\username)", expand that entry out and you should see a folder called "Databases", expand that and you should see an entry matching the name of your application's database. Right-click on that Select Tasks -> Backup. In the backup dialog make sure it's set to Backup type: FULL with the destination being "Disk" and click on "Add" to chose a folder/filename for the back up and then click Ok to start the backup.

Once that's completed you'll need to copy the back up file to the "Server" computer, then open Management Studio on there and when it asks you for connection details use "localhost\sqlexpress" with Windows Authentication. Once your connected expand the node labelled "localhost\sqlexpress" in the left hand column, right-click on "Databases" and select "Restore Database", in the resulting dialog change the "source" radio button to "Device", click on "..." and then click on "Add" and browse to the backup file from earlier. Once that's selected it should populate the rest of the restore fields for you and you can just click "Ok".

Once that's finished you'll need to update the "App.config" (or "Web.config" if it's an ASP.NET application) to reflect the new database connection string. You should be able to find a line similar to thi:

<add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\aspnet-WebApplication3-20161207104129.mdf;Initial Catalog=aspnet-WebApplication3-20161207104129;Integrated Security=True" providerName="System.Data.SqlClient" />

change that to point to the SQL Express server so it's something like this (obviously fill out your actual details!):

<add name="DefaultConnection" connectionString="Data Source=nameofserverhere\sqlexpress;Initial Catalog=nameofdatabasehere;Integrated Security=True" providerName="System.Data.SqlClient" />

This should then mean that when the application looks for it's database it will look to the one on the "server" not try and attach the mdf file.

It's been a while since I did this to be honest so hopefully I haven't missed any steps!


Creating a staging table when the target table has autoincrement

What I ended up doing was creating a staging table without using LIKE TARGET, from there I created a few queries to see what values were in the staging table vs the target table.  I then inserted those values into the target table, and dropped the staging table.

Thanks to @SqlZim for the help in tuning the query.


Even if you are using the newest version of SSMS, and SQL server 2008, that does not automaically upgrade any databases that you have attached to it. I can see SQL Server 2000 databases in SSMS for SQL Server 2008. Any database that is not upgraded cannot use SQL Server code from the newer version. If you are in compatibility mode, you may not be able to use all functionality of the newer version. I would suggest you connect to the database you want to use new features in and see what version the database is:

Select @@version

Note - do not upgrade the database to SQL Server 2008 unless the production databse has been upgraded or you will write code that can't be moved to production. Upgrading a database to a new version is delicate and time-consuming process requiring much testing. Many companies take years to upgrade to new versions as a result.


Restore partial data from single file of SQL Server Full Strip Backup

There is no way to recover a transactionally-consistent database from only half of the backup media set.

The problem is that, while a full backup contains the data file, there is also a portion of the transaction log, at the end of the backup file, to ensure the restored database is transactionally consistent.

Even if you did manage to recreate portions of the MDF file from this backup, you would still be missing the transaction log that rolls back in-flight transactions and rolls forward completed transactions that took place after the backup started.

Making matters worse, if the striped backup is written out as interleaved files (which I believe it is), you'd have one out of every two 8KB data pages, and a corrupted log at the end. There's no functional way of establishing any consistency with this kind of recovery.


IN SQL Server 2014 and earlier, the replication jobs will not continue to work after failover if you put the Distribution Database in an AG.

If you upgrade to SQL Server 2016 or 2017, this becomes a supported configuration:

  SQL Server 2017 CU6 and SQL Server 2016 SP2-CU3 introduces support for
  replication distribution database in an AG

Set up replication distribution database in Always On availability group


Should I be concered by large SERIAL values?

TLDR: If you're going to have more than 231 possible values change the column type to BIGSERIAL.

Explanation:

The SERIAL type is a signed 32-bit integer and is designed for values less than 231 (NOTE: that's 231, not 232 as they're signed integers). Here's the snippet from the PostgreSQL docs:

  The type names serial and serial4 are equivalent: both create integer columns. The type names bigserial and serial8 work the same way, except that they create a bigint column. bigserial should be used if you anticipate the use of more than 231 identifiers over the lifetime of the table. The type names smallserial and serial2 also work the same way, except that they create a smallint column.

The behavior of how it handles overflowing past 231 isn't mentioned in the docs but it clearly states that you shouldn't use it if you expect to have values greater than that.

SERIAL columns are sequence numbers. The server keeps track of the previous value (the prior max) and each time a value is requested the value is incremented. This incrementation can happen regardless of whether the id is actually permanently saved. If the transaction that fetched the id is rolled back then the id will be "lost" and your sequence will have holes in it (eg. gaps of unused ids). There's nothing wrong with that and if you're simply using them as unique ids then having gaps should cause no issue.

Sequences are implemented like this to be efficient. The server only needs to keep track of one value (the prior max) to uniquely generating ids, it can cache them across multiple connections to speed up sequence generation, and by allowing gaps it never needs to transactionally lock the sequence object when it's generating sequences. This makes them very efficient and concurrent.

Just switch your database table to use BIGSERIAL.


Should a locked query increment its CPU and IO counts?

The answer here is "it depends" - While actually blocked? A session should not use any resources. It is in a state basically saying "I sure would like to do my work, hey SQL Server may I have a lock on such and such a resource"  so while that resource is locked - the session is basically not able to do anything except wait for resource availability. Once that has been cleared it can then start running or join the runnable queue waiting for its scheduler time.

Now you could still see IO and CPU increasing on a session with many queries or steps that is blocked in different spots. So you could look and see it is currently blocked, but see the CPU increased or IO - that would be because it got past a block did some work for a statement but was then blocked again.

I'll often diagnose blocking with a tool that shows query duration and CPU/IO metrics for a session but maybe missed tracking blocks if an execution of a query is higher than normal duration wise but remains within its normal limits for CPU and IO. It isn't only blocking that will present that way but it often is - so it becomes a tool to say "hey let's look and see if we see any blocking".

I might also suggest you have a look at sp_whoisactive. A great script which will be much more informative than SP_Who2.


SSIS 2008R2 - Use Package Config Value in Expression

Yes you can. In you expression you can use: "+@[User::(variable name)]+"

More info: https://anothersqlgeek.wordpress.com/2013/03/28/ssis-dynamic-connections-part-1/


Two thoughts come to mind when someone says "My log file is too big".  First and simplest is that something is running against your database that requires that much space.  Unless it happens to be a one off query someone wrote if you shrink your log file it's just going to grow right back.  And slow your queries down while it's doing it.  The solution here is to find out if there was a large ad-hoc query run that grew your log file.  If you don't find any evidence of that then I would shrink the log to a reasonable size and see if it grows back.  If it does grow back then leave it alone and add yourself some disk space.  That's just how big it has to be.  If you restrict it to a smaller size it's going to cause queries that need the extra space to fail.

The second and scarier thought is that your database is in FULL or BULK LOGGED recovery mode and you haven't been taking regular transaction log backups.  If this is the case then your log file will just grow and grow.  The solution here is to set up regular transaction log backups.  Once these are in place shrink the log file to a reasonable size.  If it grows again (hopefully not as large), then again that is just the size it has to be.


The Database Session is automatically Getting Killed

I would hope you have some session pooling going on, or at least one database connection per thread.  This is not clear from your description.  In a multi-threaded design, I would not close connections in the threads unless they make their own connections (which I would consider a bad design).

I would get the process running with one thread, then add an additional thread.  Once you are comfortable you have that multi-threading gives you the results you want, you can scale up.

You could turn on auditing on the Oracle side to see what is really happening.

You didn't say how long the queries take to run.  If they take minutes, to run you need to ensure you have keep-alive on the network connections.  Otherwise, the network connection may get dropped.

EDIT:  Check the resource limits applicable to the userid you are connecting with.  These might get your connections closed.  You may also be getting killed off due to exceeding resource limits.  I would expect the reason for disconnect to be in the SQL Error text for the connection.

Given how long these queries are taking, you may want to investigate optimizing the queries.  Unless you have lots of memory and processors on the database server you may find that the queries run faster when run serially.  You may be forcing data out of the buffer pool with this many long queries running in parallel.


With regard to your questions, please read the documentation completely, as it addresses all of your concerns.

In terms of looking for 000000010000000000000018 when it doesn't exist .. the recovery process just parses all WAL files until it encounters "EOF" .. which usually happens when it tries to read from the next WAL file and it doesn't exist..  So it uses that case as a termination point for the recovery process..  The important bit here is that it reached a checkpoint in the recovery process as stated with consistent recovery state reached at 0/17000160.  This means that your recovery is reliable.

Continuous Archiving Documentation Section 24.3.5. - Timelines

Timelines are a way for Postgres to handle "alternate universes" so to speak...  For example, if you have a situation where your replication gets messed up in some way and suddenly you have slave promoted into master status (while your master never went down) ... some machines are writing to the master still while some are writing to the slave..  But it's ok, because the writes to the current master will all happen on timeline 1 and the writes to the slave will all happen on timeline 2 .. so you can, theoretically, still merge all the changes together (although I'm sure it wouldn't be easy)..  I imagine it's similar to a branch in a version control system.  Except there's no real "trunk".

For the postgres gurus out there - if I have anything incorrect, please correct me.  :)


I had ran into this problem and solved changing pg_hba.conf in this line

local    all            postgres        password
# IPv4 local connections:
host    all             all             127.0.0.1/32            password
# IPv6 local connections:
host    all             all             ::1/128                 password

changed the word ident to password.
It worked for me!


Can we prevent table from update directly but allow update using trigger from another table?

create trigger has a definer = {user} option for this.

You need to ensure that this defined user has update access to the stocks.stock however the other users only have select table access.

GRANT can control access at a table level.


sp_whoisactive @get_locks parameter xml error

I can't fully answer your question as to which object is causing the issue, but I can offer you some troubleshooting, and possible workarounds.

Note that I can't distribute a "fixed" script to you because of its licensing, but you're free to try the edits locally.

Troubleshooting

If you ctrl+f through sp_WhoIsActive for @get_locks = 1, you'll hit this code section:

A bit further down here, you'll see that a rather complicated select inserts into a table called #locks:

If you scroll down to line 1670 or so (assuming you have line numbers enabled in SSMS), you'll reach the end of the SELECT INTO that populates it. You could try adding a simple SELECT * FROM #locks; here to see what ends up in the table. This could at least potentially point you to the correct object.

Workarounds

One workaround, without changing any code might be to run the procedure like this:

EXEC sp_WhoIsActive
    @get_task_info = 2,
    @get_additional_info = 1;

The intent of these parameters is documented here.

If that doesn't run without error, you're stuck doing a ctrl+f for FETCH NEXT FROM locks_cursor. This will bring you to the section of code where your error pops up.

Within this block of cursor code, you'll find a dynamic query, a catch block, and an update with nested REPLACE blocks to clean out bad characters (except yours, it appears). You'd have to add an additional nested REPLACE across all of them with NCHAR(0x0001) here I believe, in order to fix it. I haven't tested this, so YMMV.

Hope this helps!


You probably misunderstood the recovery model concept. From BOL Doc

  A recovery model is a database property that controls how transactions
  are logged, whether the transaction log requires (and allows) backing
  up, and what kinds of restore operations are available

Recovery model controls the logging and there by how much data you can recover. Only certain transactions which would be minimally logged would be minimally logged all other transactions would be fully logged in bulk logged recovery model. So it depends on how much data you can loose keeping that in mind choose recovery model. You should read Backup under BL recovery model.

If you are performing a bulk logged operation and dont really care about point in time recovery during the period the BL operation runs go ahead change recovery model to bulk logged. But read considerations before changing to BL recovery Model

Risk.

If you perform operation that are minimally logged then you loose point in time recovery for the time period this transaction is running. After this transaction finishes and other transaction starts which is fully logged you can perform point in time recovery. You must read Understanding Logging and recovery in SQL Server


You can use a foreign data wrappers in Postgres to make Oracle tables appear like local tables in Postgres.

Then you can use the Oracle tables in a query running in Postgres and join them to the "real"  Postgres tables in a single query.

A list of foreign data wrappers (not included with Postgres itself) is available in the Postgres Wiki


Inserting data into a table with one value as clob and blob

In SQL, the limit is 4,000 characters.  Using straight SQL like that, without a bind
variable, you'll be limited to 4,000 characters.
Steps to insert lob values :

1) Create a table and name it TBL_CLOB with 2 fields:

   id_int = integer;
    clob_txt =clob;

2) Create a stored procedure and name it P_CLOB  with the following
code:

    (P_ID_INT in int,P_CLOB_TXT in varchar2)as

    begin
    insert into TBL_CLOB values(P_ID_INT,P_CLOB_TXT);
    end;

3) Test inserting up to 32000. Use SQL Plus and enter some starts in
the CLOB field:

    SQL>  exec p_clob(1,rpad('*',32000,'*'));

    SQL> commit;

    SQL>  exec p_clob(2,rpad('*',19872,'*'));

    SQL> commit;

4) Retrieve the 2 records you just inserted and count the number of
characters in the CLOB fields:

    SQL> select id_int,dbms_lob.getlength(clob_txt) from tbl_clob;

5) You should get something like this:

        ID_INT DBMS_LOB.GETLENGTH(CLOB_TXT)
    ---------- ----------------------------
             1                        32000
             2                        19872

Makes sense ?


Update all values for given key nested in JSON array of objects

First cte unnest all elements of the array, second one update each element and then simply update the original table building the array again.

with ct as
(
    select id, jsonb_array_elements(data) dt
    from   t
)
, ct2 as
(
  select id, jsonb_set(dt, '{updatedAt}', '"1571150000"', false) dt2
  from   ct
)
update t
set    data = (select jsonb_agg(dt2) from ct2 where ct2.id = t.id);

select * from t;

id | data
-: | :-----------------------------------------------------------------------------------------------------------------------------------
 1 | [{"id": "1", "status": "test status1", "updatedAt": "1571150000"}, {"id": "2", "status": "test status2", "updatedAt": "1571150000"}]

db<>fiddle here


I believe this is due to all the ORs in your WHERE predicate on the Record.Tag field. It's causing a bad cardinality estimate. That is a rather long predicate and set of ORs, could you try breaking it into multiple smaller queries for different sets of Tags and the. UNION ALL the results of each query back together? It may help with the cardinality estimate.
You could also then change the ORs for a range when you break it up to make it less verbose at least, such as:
WHERE Record.Tag >= 10400 AND < 14006


Finding the last time a table was updated

You might be able to get an idea from

SELECT last_user_update
FROM   sys.dm_db_index_usage_stats us
       JOIN sys.tables t
         ON t.object_id = us.object_id
WHERE  database_id = db_id()
       AND t.object_id = object_id('dbo.YourTable')

but the data there is not persisted across service restarts and might not be accurate for your requirements (e.g. running DELETE FROM T WHERE 1=0 will update the time even though no rows were actually deleted)


Linked servers on SQL Server 2008 R2 through linked tables in MS-Access?

You should be able to get away with this by creating views in SQL Server that are effectively wrappers for tables in the linked server. You will of course have to deal with the extra layer of security configuration for the linked server, thus ensuring that the Access users are connecting to the external data with the proper credentials (whether it be something per-user, or just a global set of credentials that's used for everybody accessing the FoxPro data).

Keep a close eye on performance, since Access is going to be generating queries meant for SQL Server, and it tends to be a bit chatty to begin with. SQL Server is then going to generate queries for fetching the data from FoxPro. With that many layers of query generation, performance could be a crapshoot for large tables, and it'll be pretty difficult to diagnose or improve it.

If the FoxPro data is mostly static historic data, then I'd recommend bringing it into SQL Server. If there's still read/write activity happening, then you'll have to either settle for the linked server approach, or periodically refreshing the data in SQL Server via SSIS or some other ETL.


I found a better index. I think I was reading the query plan results, which caused me to determine the wrong index order.. Now I'm reading it from bottom to top, and it gives me this index:

create index index1 on lineorder(lo_partkey,lo_suppkey,lo_orderdate);

However, is there anyway of making it even better??


when can AG primary replica be a different host to WFC primary?

This is easiest to explain if you think about having multiple Availability Groups in your environment rather than just one. Each AG listener would have its own name and IP, and it could be failed over independently to either physical node.

In that environment, there are a few different parts:

The physical nodes - each of which has its own IP address
Each Availability Group listener - each of which has its own virtual name and address, and fails over back and forth between the nodes depending on who owns that particular Availability Group at that moment in time
The cluster's management point - which also has its own virtual name and address, and can be different than the AG listener.

See, one of the nodes has to own the cluster. This becomes more and more important as your cluster grows out - for example, to multiple physical nodes rather than just two. That cluster manager keeps control of the cluster's state, which nodes own which moving parts, and so on.

All Windows clusters - even a simple 2-node, 1-AG setup - have this separate name and IP address for management. Most of the time, it doesn't matter who owns the cluster, but in a multi-node cluster (say, 3 nodes in your primary data center and 2 nodes in your disaster recovery data center) where you want to get down to last-man-standing as things fail, then it does start to matter.


MySQL: Adding an IF condition to trigger?

You forgot END //

DROP TRIGGER IF EXISTS `init_uuid_company`//
CREATE TRIGGER `init_uuid_company` BEFORE INSERT ON `company`
 FOR EACH ROW BEGIN
  IF(NEW.uuid IS NULL OR NEW.uuid = '') THEN
   SET NEW.uuid = UUID();
  END IF;
 END //


grant select on all databases except one mysql

Run the output of the following query:

SELECT CONCAT("GRANT SELECT ON ",SCHEMA_NAME,".* TO 'test_user'@'localhost';")
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME NOT LIKE 'mysql';


Mongo - findAndModify has to be executed twice to take effect

Find the doc..

The 'findAndModify' command modifies and returns a single document. By default, the returned document does not include the modifications made on the update. To return the document with the modifications made on the update, use the new option.

About new:

Optional. When true, returns the modified document rather than the original. The findAndModify method ignores the new option for remove operations. The default is false. |

https://docs.mongodb.com/manual/reference/command/findAndModify/
So I should have done:
db.campaign.findAndModify({
    query: {
        code: "CK", 'siteCampaigns.siteCode': "IT"
    },
    update: {
        "$set": {
            'siteCampaigns.$.endDate': ISODate("2020-08-30T00:00:00Z")
        }
    },
    upsert:false,
    new: true
})


Try replacing double-quotes " by single-quotes ', and use a double back-slash. \\

UPDATE tbl
set col_name = replace(col_name, 'Equipe d\\''Olivier', 'Equipe d''Olivier');

db<>fiddle here


Nested query not recognize external table

The reason for the error is explained in MySQL documentation in part E.4. Restrictions on Subqueries as:

Subqueries in the FROM clause cannot be correlated subqueries. They are materialized in whole (evaluated to produce a result set) during query execution, so they cannot be evaluated per row of the outer query.

Changing query a bit like the following seems to solve your problem:
SELECT t.CodC, c.NameC, t.Amount, t.Year, t.Week,
    IF(t.Week!=@week OR t.Year!=@year, @rank:=1, @rank:=@rank+1) AS rank,
    @week:=t.Week, @year:=t.Year
FROM
    (SELECT CodC, Sum(Imports) As Amount, Year, Week
        FROM Weekly
        WHERE Year=2014
        GROUP BY CodC, Year, Week
        ORDER BY Year DESC, Week DESC, Amount DESC) t
    JOIN (SELECT @rank:=0, @week:=-1, @year:=-1) t2 ON 1
    LEFT JOIN Companies c ON c.CodC=t.CodC

It basically adds an artificial rank column by checking year and week values on the ordered rows.
Edit
Previous query is given to order by weekly sum because of misunderstanding. Following query will give what you want
SELECT t2.*,
    IF(t2.Week!=@week OR t2.Year!=@year, @rank:=1, @rank:=@rank+1) AS rank,
    @week:=t2.Week, @year:=t2.Year
FROM
    (SELECT t1.*, SUM(w2.Imports) AS cnt
    FROM
        (SELECT w1.CodC, c.NameC, w1.Week, w1.Year
        FROM Weekly w1
            LEFT JOIN Companies c on c.CodC=w1.CodC
            ORDER BY w1.Year ASC, w1.Week ASC) t1
            LEFT JOIN Weekly w2 ON t1.CodC=w2.CodC AND w2.Year=t1.Year AND w2.Week<=t1.Week
            GROUP BY t1.CodC, t1.Week, t1.Year
            ORDER BY t1.Year DESC, t1.Week DESC, cnt DESC
            )t2
    JOIN (SELECT @rank:=0, @week:=-1, @year:=-1) t3 ON 1

Fiddle


how to take output from second table by joining in SQL

Try Something like if you wish to use joins :

 select name from table1 t1 inner join table2 t1 on t1.id=t2.id where t2.status = 'closed';

or

select name from table1 t1, table2 t2 where t1.id=t2.id and t2.status = 'closed';


Choosing the right blocksize for Oracle Database tablespaces with lobs

LOBs are managed in chunks (groups of blocks), not blocks, so there's not really anything to gain with this. Oracle assumes that an 8k block size is "universally good" for everything. By increasing the block size, you could potentially wind up wasting storage space that can't be efficiently allocated for smaller files because the chunk size is too large.
See here for a running discussion: https://asktom.oracle.com/pls/apex/asktom.search?tag=oracle-block-size
Final analysis:

That said, worrying about block size is like fiddling while Rome burns -
many (most?) apps have low hanging fruit which will give a better ROI on your time than tuning the block size.
8k is still best block size to be using.


Result set of SELECT statement in LOOP in MySQL Stored Procedure

CURSORs are rarely worth using.  Try to get rid of them.  In your example:

SELECT b.*
    FROM table2 AS b
    JOIN table1 AS a  ON b.c1 = a.field1
                     AND b.c2 = a.field2

Anyway, when you execute multiple SELECTs inside a Stored Proc, you get "multiple resultsets".  Some interfaces either can't handle such, or need different coding.

And you had an error:  The cursor SELECT fetched 2 columns but the FETCH expected 4.

Coding questions should be addressed to stackoverflow.com


Sql Server Dynamic partition with page compressed index

The answer to your question, as asked, is to rebuild the new partition after the partition split.  The split should be to an empty partition (so long as you split the rightmost partition befor you have any data to the right of the new boundary value), so this is not expensive.
eg
declare @newBoundary int = 20200501;
declare @newPartitionId int =  $partition.Fnc_Prt_Fact_Sales(@newBoundary);

alter partition function Fnc_Prt_Fact_Sales() split range (@newBoundary);

alter index ix_fact_sales on Fact_Sales
rebuild partition = @newPartitionId
with (   data_compression = page on partitions (@newPartitionId) )

But the easier, and more common approach is to set the index's default compression to PAGE, instead of specifying that only enumerated partitions are PAGE compressed.
 CREATE  CLUSTERED INDEX [Ix_Fact_Sales] ON [dbo].[Fact_Sales]
(
    [Date_Id] ASC,
    [City_Id] ASC,
    [Store_Id] ASC
) WITH (
   DATA_COMPRESSION = PAGE --ON PARTITIONS (1,2,3,4)
)  ON Prt_Scheme_Fact_Sales(Date_Id)
GO


db_datareader, db_datawriter and db_owner permissions

According to Database-Level Roles:

  Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.

A member of this Database level role can do whatever he wants on the database. This also include SELECT, INSERT, UPDATE and DELETE data.

You still have to make sure that you are not giving too much rights to a user when you make him a menber of db_owner...

You can connect with this user on your database and run this query to see effective Database level permissions using sys.fn_my_permissions:

SELECT *
FROM sys.fn_my_permissions(null, 'database')
--WHERE permission_name IN ('SELECT', 'UPDATE', 'DELETE', 'INSERT')
ORDER BY permission_name

Output with only db_owner includes following rows:

entity_name subentity_name  permission_name
...         ...             ...
database                    DELETE
database                    INSERT
database                    SELECT
database                    UPDATE
...         ...             ...


Best Database specifications for my IOT solution

You have two question, let's start with the easiest.

Math: rows / hours is:  300 Hz * 3600 s / hour = 1.08 Million rows

Which database can handle ~1 Million rows / hour?

Just about all of them.

You just need to use the correct technique in order to achieve those numbers.
Single row insert then commit can potential kill performance.

This blog describes the different Oracle method to insert 30 seconds worth of data (10000 rows).
The fastest method is done in 6ms.

https://geraldonit.com/2012/03/31/loading-data-fast-regular-insert-vs-bulk-insert/

MySQL can insert 3 seconds worth of data (1000 rows) in 10ms.

http://brian.pontarelli.com/2011/06/21/jdbc-batch-vs-multi-row-inserts/

Don't pick your RDBMS based off of those posts.  The point of those blogs is to show you that a performance difference exists based on HOW you insert the data.

What you really need to look for in an RDBMS is to see if it supports partitioning (and still fits in your budget ).

Partitioning allows you to break up the data into smaller chunks.  You can then toss those smaller chunks away ( truncate ) in milliseconds when you no longer need them.

Can I use a Database Queue instead of MQTT

You could try.

The only one I'm familiar with uses actual tables under-the-hood.  That means you'll need to commit after every row is added in order for you clients to see the data.
As mentioned before, this could be bad for performance.

Personally, I'd stick with the MQTT method for the Live Stream of data.  You got it to work.  No sense in trying other methods.

You'll need to write a subscriber to the MQTT so that it can buffer the inserts using one of the methods mentioned in the blogs.


How to store a sequence of events related to a transaction

Why not just have a transactions table:

CREATE TABLE dbo.Transactions(TransactionID INT IDENTITY(1,1));

When you're about to start a new transaction (a set of imports and exports), do this:

DECLARE @tID INT;
INSERT dbo.Transactions DEFAULT VALUES;
SELECT @tID = SCOPE_IDENTITY();

Now in your import and export tables, add a column that holds the transaction id, and store @tID there for every action in this sequence.

Or use a separate table to store Transaction + Event combinations, if events can be recycled. For example:

CREATE TABLE dbo.TransactionEvents
(
  TransactionID INT,   -- foreign key
  EventTypeID TINYINT, -- 1 = import, 2 = export
  EventID INT,         -- implicit foreign key to import/export table
  ... other columns ...
);

You can have a lookup table for EventTypes if you want, e.g.:

CREATE TABLE dbo.EventTypes
(
  EventTypeID TINYINT PRIMARY KEY,
  Name SYSNAME UNIQUE
);
INSERT dbo.EventTypes VALUES(1,N'Import'),(2,N'Export');

You might also consider not having separate import and export tables, but one events table with a type of import or export. Then the foreign key here can be explicit.

And please ditch the tbl prefixes. All they do is make you type three extra characters every single time.


Is the environment variable LOCAL set? this will cause sqlplus to connect to a TNS alias instead of directly to the local database instance.

Your other problem with starting it into mount, you are missing a parameter to oradim -STARTTYPE srvc.  All of the options can be seen by typing oradim /?

to start just the service so that you can manually start to mount use

oradim -start -sid dantest3 -starttype srvc

At this point the database will not be registered with the listener so you will still need to connect without using the listener.


Reclaiming space from a large partitioned database

I recently had a similar project where we implemented data compression on a partitioned table and reduced our space usage, resulting in the need to shrink down the files.  We did the following to manage this while keeping the database online:

Off hours DBCC SHRINKFILE executions.  We would start the shrink file off production hours and cancel it before the next day's worth of production started.  This would go on for several weeks, but progress on the shrink was not lost (as pages are getting moved towards the "front" of the file and aren't rolled back).  This did cause fragmentation, but the fragmentation was acceptable to our business in order to keep the database online during file cleanup.
Rebuilding individual partitions for the indexes.  If your table is partitioned, my view is that you should be doing this anyway because it minimizes the impact of the rebuild.  While we were doing file shrinks, we only did weekly index maintenance (we usually do daily)  in order to work with/around the DBCC SHRINKFILE.  Once we had reduced the filesize, we went back to daily index rebuilds.  In order to rebuild the individually fragmented partitions, we leveraged Ola Hallengren's scripts, but the essential command is ALTER INDEX foo ON bar REBUILD PARTITION=n.  You would run this command for each partition (n) that you wanted to rebuild.

This was a multi-week process for us, but we reduced a 1.5 TB file down to ~500 GB and did not interrupt the database service to do it.


Best way to implement an insert-only table to prevent updating huge number of fields in SQL Server

So if you are using an ORM I'd suggest that in this particular case you point the actual operation to a stored procedure instead of allowing the ORM to create the insert/update/delete statements (although this is preferable in general).  If you're using Entity Framework, this is fairly simple.  If the ORM doesn't support using your own SQL for create/update/delete, don't use it.
So our requirements are:

For a given date, a room has one price.
Prices for a room/date can be changed

This leaves two options:

Create an audit table to store changes resulting from updates, set up our stored procedure to enforce the audit rules
Use the table to store the current a past values, set up our stored procedure to insert new rows only when necessary

The first option works, but if you need a lot of concurrency or need to query past prices as valid values, the second option works better.  It begins like this:
CREATE TABLE Room
(
  RoomId  INT NOT NULL
  /* Other stuff */
 ,CONSTRAINT PK_Room PRIMARY KEY (RoomId)
)
;

CREATE TABLE RoomPrice
(
  RoomId      INT           NOT NULL
 ,StartDt     DATE          NOT NULL
 ,Price       DECIMAL(9,2)  NOT NULL
 ,UpdateDtm   DATETIME2(2)  NOT NULL
 ,CONSTRAINT FK_RoomPrice_Charged_For_Room FOREIGN KEY (RoomId) REFERENCES Room (RoomId)
 ,CONSTRAINT PK_RoomPrice PRIMARY KEY (RoomId, UpdateDtm)
 ,CONSTRAINT CK_RoomPrice_Price_Is_GT_Zero CHECK (Price > 0.00)
)
;

Now the tricky part, which is ensuring everything behaves how we expect it should.  This is actually embedded in the table design, but it's not obvious without some explanation.  The rules are:

When a new row is inserted, it is the most recent version.  That price applies to all dates on or after the StartDate.
When a price changes, if the new StartDate is greater than or equal to all prior StartDates and greater than or equal to the current date1, the insert is valid.
When a price changes, if the new StartDate is greater than or equal to the current date but not greater than all prior StartDates, the insert is still valid, but all rows with a StartDate greater than the new StartDate are now invalid.

This is all handled by the following query.  To get a price for room @room as of date @date and point in time @dtm, we would use the following query (which should be wrapped in a stored procedure):
SELECT
  RP.Price
FROM
  RoomPrice RP
WHERE
  RP.RoomId = @room
    AND RP.UpdateDtm =
      (
        SELECT
          MAX(UpdateDtm)
        FROM
          RoomPrice
        WHERE
          RoomId = RP.RoomId
            AND UpdateDtm <= @dtm
            AND StartDt <= @date
      )

To set/update a price we would create a procedure like this (h/t to Erland Sommarskog's excellent blog post on error handling):
CREATE PROCEDURE RoomPrice_Set
  @RoomId     INT
 ,@StartDt    DATE
 ,@Price      DECIMAL(9,2)
AS
  SET NOCOUNT, XACT_ABORT ON;

  /* Need to put some time zone logic into this */
  IF @StartDt < SYSDATETIME() THROW 50001, 'Date must be greater than or equal to current date',1;
  ELSE
    BEGIN TRY
      BEGIN TRANSACTION
      INSERT INTO RoomPrice (RoomId,StartDt,Price,UpdateDtm)
      SELECT
        i.RoomId
       ,i.StartDt
       ,i.Price
       ,SYSDATETIME()
      FROM
        (VALUES (@RoomId, @StartDt, @Price)) AS i(RoomId, StartDt, Price)
      LEFT JOIN
        RoomPrice RP
          ON RP.RoomId = i.RoomId
              AND RP.UpdateDtm =
                (
                  SELECT
                    MAX(UpdateDtm)
                  FROM
                    RoomPrice
                  WHERE
                    RoomId = i.RoomId
                      AND StartDt <= i.StartDt
                )
      WHERE
        i.Price <> ISNULL(RP.Price,-1)

      COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
      IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
      ;THROW
    END CATCH
GO

Fiddle here: http://sqlfiddle.com/#!18/c50573/8
1This should be the only scenario, it would be very frowned upon if prices changed after the fact.