Using Sync Orchestrator to Syncronize two SQL CE 3.5 Databases

I was able to solve the error by changing the provisioning.  Instead of calling the above code for each of the two databases, I created a single function as shown below:

    public void SetupScope() {
        if (ProvidersCollection["Local"] != null && ProvidersCollection["Remote"] != null) {
            var localConnection = (SqlCeConnection)((SqlCeSyncProvider)ProvidersCollection["Local"]).Connection;
            var remoteConnection = (SqlCeConnection)((SqlCeSyncProvider)ProvidersCollection["Remote"]).Connection;

            var localScopeDescription = new DbSyncScopeDescription("LocalPerson");
            var localPersonTableDescription = SqlCeSyncDescriptionBuilder.GetDescriptionForTable("Person", remoteConnection);
            localScopeDescription.Tables.Add(localPersonTableDescription);

            var localDatabaseConfiguration = new SqlCeSyncScopeProvisioning(localConnection);
            try {
                localDatabaseConfiguration.PopulateFromScopeDescription(localScopeDescription);
                localDatabaseConfiguration.ObjectPrefix = "Sync";
                localDatabaseConfiguration.SetCreateTableDefault(DbSyncCreationOption.CreateOrUseExisting);
                localDatabaseConfiguration.Apply();
            } catch (Exception ex) {
                Console.WriteLine(string.Format("{0}", ex.Message));
            }

            var remoteScopeDescription = new DbSyncScopeDescription("RemotePerson");
            var remotePersonTableDescription = SqlCeSyncDescriptionBuilder.GetDescriptionForTable("Person", localConnection);
            remoteScopeDescription.Tables.Add(remotePersonTableDescription);

            var remoteDatabaseConfiguration = new SqlCeSyncScopeProvisioning(remoteConnection);
            try {
                remoteDatabaseConfiguration.PopulateFromScopeDescription(remoteScopeDescription);
                remoteDatabaseConfiguration.ObjectPrefix = "Sync";
                remoteDatabaseConfiguration.SetCreateTableDefault(DbSyncCreationOption.CreateOrUseExisting);
                remoteDatabaseConfiguration.Apply();
            } catch (Exception ex) {
                Console.WriteLine(string.Format("{0}", ex.Message));
            }
        }
    }

The difference here is that for each of the two databases, I now reference the connection of the other database when setting up my table descriptions.


OPTION 3:

I would prefer this option in order to design the model based on the question.

I have also split up ReservationRoom into another table, because there is a possibility that single user may be able to reserve multiple rooms, and every room may have different price on different dates.

Room
====
RoomId
Name
Description

RoomRate
========
RoomRateId
RoomId      /*FK to Room table*/
StartDate
EndDate
Price       /*price per room*/

Reservation
===========
ReservationId
UserId      /*FK to user table*/
etc...

ReservationRoom
===============
ReservationId   /*FK to Reservation table*/
RoomId      /*FK to Room table*/
FromDate    /*Reservation From Date*/
ToDate      /*Reservation To Date*/
Price

I don't see any issues writing queries for the above proposed schema. Please take care of indexes and unique keys, which is beyond the scope of this question, but I thought it's worth to mention!

In order to filter the available rooms, you need to be dependent on ReservationRoom table. (Edited)

I think, this design provides you flexibility. Please highlight, if you notice any flaws in this design?


Adding id column of table to a hstore column postgres

Usually in SQL double quotes used to specify the identifiers like tables, columns, functions and other DB objects which name contains "non-conventional" characters or have to be case-sensitive. For example it is correct PostgreSQL statement:

create table "This is#my...%TABLE" ("And&Field" int);

(It is not recommended to use such names however because it may cause a lot of problems)

So double quoted literals meanings the DB objects and in the your statement "id" meanings the id field.

On the other hand to specify the string literal in the standard SQL you have to use single quotes. For example:

select 'foo' as "bar field";

Here 'foo' is a text constant and "bar field" is a column alias where double quotes is required because of the space character inside it.

According to the above you have to change your statement to

update new_table set hstore_col = hstore_col || hstore('id',id::text) where id = 1


Build JSON object from one-to-many relationship data in a single query?

If containers can be empty, the currently accepted solution does not work for you. It has to be an outer join to preserve rows without match - to get equivalent results to the correlated subqueries you are using in your fiddle:

select *, array(select thing_id from container_thing where container_id = container.id) as "thingIds"
from container

1.

SELECT to_json(sub) AS container_with_things
FROM  (
   SELECT c.*, json_agg(thing_id) AS "thingIds"
   FROM   container c
   LEFT   JOIN container_thing ct ON  ct.container_id = c.id
   WHERE  c.id IN (<list of container ids>)
   GROUP  BY c.id
   ) sub;

2.

With more than a few rows per container (you mentioned 20) it's typically faster to aggregate before you join:

SELECT to_json(sub) AS container_with_things
FROM  (
   SELECT c.*, ct."thingIds"
   FROM   container c
   LEFT   JOIN (
      SELECT container_id AS id, json_agg(thing_id) AS "thingIds"
      FROM   container_thing
      WHERE  container_id IN (<list of container ids>) -- repeat condition
      GROUP  BY 1
      ) ct USING (id)
   WHERE  c.id IN (<list of container ids>)
   ) sub;

3.

Or you can combine the ARRAY constructor you found with LEFT JOIN LATERAL:

SELECT to_json(sub) AS container_with_things
FROM  (
   SELECT c.*, ct."thingIds"
   FROM   container c
   LEFT   JOIN LATERAL (
      SELECT ARRAY (
         SELECT thing_id
         FROM   container_thing
         WHERE  container_id = c.id
         -- ORDER  BY thing_id  -- optional order for deterministic results
         ) AS "thingIds"
      ) ct ON true
   WHERE  c.id IN (<list of container ids>)
   ) sub;

What is the difference between LATERAL and a subquery in PostgreSQL?

Might be faster, yet.

SQL Fiddle. (Extending @a_horse's fiddle.)

Note that the result for empty containers is subtly different in the three queries above:

"thingIds":[null]
"thingIds":null
"thingIds":[]

4.

In Postgres 9.5 (since you are using it) you could also work with  jsonb and its functionality and one less subquery:

SELECT jsonb_set(to_jsonb(c), '{thingIds}', "thingIds") AS container_with_things
FROM   container c
LEFT   JOIN (
   SELECT container_id AS id, jsonb_agg(thing_id) AS "thingIds"
   FROM   container_thing
   WHERE  container_id IN (<list of container ids>) -- repeat condition
   GROUP  BY 1
   ) ct USING (id)
WHERE  c.id IN (<list of container ids>);

Alternatively:

SELECT to_jsonb(c) || jsonb_build_object('thingIds', "thingIds") AS container_with_things
FROM   ...


There is no such functionality in the mysql. If you want to trace the changes made to the data you have to collect historical data on your own. Say you create the trigger BEFORE UPDATE that insert all the data from the updated rows into the historical table. Later you can compare the present state of data with the historical states and trace all the changes been made.


Fail to copy all PostgreSQL databases between servers although there is no error message

The dump-file had a size of 0 Bytes indicating that it was empty. Repeating the dumping process delivered a non-empty file with which the import process with psql worked fine.


Oracle: Can a tablespace stay in backup mode forever?

When we put the tablespace in backup mode Oracle copies whole changed data blocks into the redo stream in order to make the backup safe from fractured block. Copying whole data block instead of only the change may degrade the performance. Oracle strongly recommends to put the tablespace in backup mode only when required.

What Happens When A Tablespace/Database Is Kept In Begin Backup Mode (Doc ID 469950.1) (MOS Account needed)

The following is the excerpt from the Oracle Documentation.

  Caution:

  If you fail to take the tablespace out of backup mode, then Oracle Database continues to write copies of data blocks in this tablespace to the online redo logs, causing performance problems. Also, you receive an ORA-01149 error if you try to shut down the database with the tablespaces still in backup mode.

Making User-Managed Backups of Online Tablespaces and Data Files


MySQL Server Config, Small Timeframe, Extreme Usage-Scenario

Hopefully, I'm missing or misinterpreting something, because I don't see an explicit SELECT ... LOCK IN SHARE MODE as a viable way to approach this.

Using SELECT ... LOCK IN SHARE MODE explicitly sets an IS lock ("intention shared") on the row(s) matched, but other threads can also set IS locks on the same row(s) at the same time, with no feedback from the server indicating whether anyone else already has such a lock on the row.

So, let's say two threads set an IS lock on the row, then both of them issue an UPDATE.

One unlucky thread sees this:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

It's a deadlock because two threads are asking for mutually-exclusive things.  Thread #1 is asking for its IS lock to be escalated to an X (exclusive) lock so it can update the row... but this can't be permitted because thread #2 already has an IS lock, which prevents #1 from getting its X lock ... and, thread #2 is also trying to escalate to an X lock, which is being blocked by thread #1's IS lock.

You might assume that the first thread to request the lock would be the one that gets to update the row, but that's not the case, either.  My testing shows that in this case the winning thread is the one whose UPDATE is noticed first, not the one that gets an IS lock first.  This is sensible, since that thread has done slightly more work than the other, and, after all, both threads had previously only expressed the intention to lock the row exclusively.

But... with all of that said, it seems to me that there's a much simpler approach to this, that would be far easier on your server.

If visitors to your site are trying to claim one specific timeslot, identified by a unique timeslot_id, then I'm not sure why this approach wouldn't work:

START TRANSACTION;
UPDATE timeslot_guest_map SET guest_id = ? WHERE timeslot_id = ? AND guest_id IS NULL;
SELECT ROW_COUNT();
COMMIT;

If ROW_COUNT() returns '1' then you got the time slot.  If ROW_COUNT() returns 0 then sorry, someone else got there first.  In this setup, InnoDB implicitly handles the row locking, and thread #2 will block until thread #1 commits, at which time guest_id will no longer be null on that row so it will not be updated.

On the other hand, if the "timeslots" are actually identical, such as might be the case when 150 "general admission" seats were available in an auditorium, the approach could be like this:

START TRANSACTION;
UPDATE timeslot SET user_id = ? WHERE user_id IS NULL LIMIT 1;
SELECT ROW_COUNT();
COMMIT;

Here, thread #1 implicitly gets an X lock on the first available row, updates it, and commits.  Thread #2 has to wait for an implicit IS lock on the first row, because of the X held by thread #1... but when thread #1 commits, thread #2 gets its IS lock so it can examine that row, and finds out that the row no longer matches... so the next available row gets matched, locked, and updated, if there is one.

The workload involved in either of these scenarios sounds to me like it should be trivial for MySQL, assuming you take care to properly commit (or rollback) your transactions.  Otherwise, you'll end up with a backlog of threads waiting on locks held by abandoned transactions.

As @Valor suggested, you're far more likely to have problems providing your web server with the resources it will need in order to handle the concurrent connections... as was likely the case here and here, to cite a couple of recent examples of MySQL being the victim of memory exhaustion on the server, not the perpetrator.


How do I migrate varbinary data to Netezza?

Best is to test it as Netezza does not support varbinary. So it has to be converted to a supported varchar(16000) or VARCHAR(64000), VARCHAR(n), NVARCHAR(16000), NVARCHAR(n).

Test your migration plan before hand.


No. A differential never truncates the log. Please consider your recovery needs and choose a recovery model that fits those requirements.

  There’s still a widely held misconception that when properly in the
  FULL or BULK_LOGGED recovery models that full or differential backups
  can truncate the log. No. It NEVER happens.

http://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/

I'd recommend easing up on the Differentials and start with scheduling t-log backups ever 15 minutes.

More information:

Once you get your t-logs backed up appropriately then you'll want to leave the shrinking alone. Whatever it grows to (usually within that 15 minutes) is usually the growth profile of what it actually needs. To shrink the log file only to let it grow again will cause unnecessary overhead and may impact performance.

If you want to consolidate your VLFs then you need to find an outage window when you can shrink the log file and then grow it back to it's preferred size using a standard increment (use growth increments of 8000MB)


You dont really need a loop and there is no need to use the undocumented sp_MSforeachdb as there is a better alternative to it - written by Aaron Bertrand.

You can use below tsql - Run in SSMS by pressing CTRL+T:

set nocount on

select 'if OBJECT_ID(''dbo.SDE_server_config'') is not null'+char(10)+'begin '+char(10)+' UPDATE ' + quotename(name) + '.dbo.SDE_server_config SET num_prop_value=0
        WHERE prop_name=CONNECTIONS;' + char(10) + 'end '+char(10)+ 'else print ''there is nothing to update ...!;'''
from sys.databases
where database_id > 4
    and state_desc = 'ONLINE'

You can put above select statement in a variable and do and EXEC and save it in a .sql file and then use sqlcmd to just directly call that .sql file and run it on multiple servers.


Is replication from SQL Server 2008 to PostgreSql possible?

Only ORACLE and IBM-DB2 are officially supported as subscribers. Refer here and here for more details.

Also, from sql server 2012, this is being depreciated as well.

Heterogeneous replication to non-SQL Server subscribers is deprecated. Oracle Publishing is deprecated. To move data, create solutions using change data capture and SSIS.
 Caution :
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Below is a blog post that I found that might be useful to you:  Replicating from MS SQL Server to PostgreSQL


SSIS Packages get stuck in SQL Agent jobs

I think you should debug SSIS package in visual studio, if you have solution file.

I got this situation couple of month before, when I execute the package they stuck on the data flow.
I had resolved my problem by removing the transaction script from the package. Because I importing bulk of data and transaction create kind of deadlock situation....

Hope this will work for you


Mongo Create a user as admin for any database raise an error

The '...AnyDatabase' roles should be added to the admin database for the user:

use admin
db.createUser({ user: "mongoadmin" , pwd: "mongoadmin", roles: ["userAdminAnyDatabase", "dbAdminAnyDatabase", "readWriteAnyDatabase"]})

Any database:

Database User Roles
Database Administration Roles

Admin database:

Cluster Administration Roles
Backup and Restoration Roles
All-Database Roles

Extra: To give different roles on different database and admin:

db.createUser({user:"replSetManager",pwd:"password",roles:[{role:"clusterManager",db:"admin"},{role:"dbOwner", db:"adminsblog"},{role:"readWrite", db:"departmentblog"},{role:"read", db:"otherblog"}]})


Fill Factor: Will the fill factor change automatically after it detects the changes in the index pages?

So, now, essentially the fill factor of the database is not 0 as there are two pages which are half full

You are correct, but this is actually caused by the fill factor value. You cannot say that now fill factor is not 100 (assuming your case) that would be an incorrect statement because fill factor was 100 this page split event was forced. Suppose you have index page which was almost full and new row needs to be accommodated now this would cause page split and move half rows to new page there by causing fragmentation. So what caused this: it was precisely the 100 fill factor value. That is why you must be cautious with what fill factor value you choose

A low, nonzero fill-factor value may reduce the requirement to split pages as the index grows, the index will require more storage space and can decrease read performance

Also note that whatever fill factor value you use you CANNOT avoid page split even if you have ever increasing columns. There are 2 types page splits good and bad. The bad one is when data is inserted in middle creating a gap.

Some information about Full Factor in Paul's Myth Series about Fill Factor

Read more about fill factor

  Will this reflect when we run sp_configure the next time ?So when we restart the SQL Server and check the current value of the fill factor using sp_configure or any other option, does it reflect the current calculated value? Or will it just remain with the same value?

No after restart the value of fill factor in sp_configure will not change even if database has many page splits and many pages are half filled. This is called as fragmentation.


When creating a data model a good place to start is to have an entity type for each noun in the description of your problem.  The attributes of these entity types will be the values you wish to store about those nouns and verbs in the description will become relationships in your data model.

In the database the entity types become tables, the attributes are columns and relationships end up as foreign keys.  Of course there are a lot of subtlties but this should get you going.

Copying your description above into these terms you will have:

Table: LabTests
    Name

Table: Components
    Name
    MinValue
    MaxValue

Since you say "component can be changed with time" you will need a table to link the two together

Table: TestComponents
    TestName
    ComponentName
    FromDate
    ToDate

I've included FromDate and ToDate so you can tell how a test has changed over time.  If you are only interested in what a test looks like right now you can remove these columns.

You mention PersonID, so we'll have a table to put that into.

Table: People
    PersonID

What do you do to that person?  You don't say, but I should think you take a sample from them and apply tests to that sample.  OK, let's have tables for those.

Table: Samples
    SampleID
    TakenFromPersonID
    TakenDateTime

You will need to know from whom the sample was collected.  This is the TakenFromPersonID.  You will need to know when it was collected.  Since it is likely that several samples can be collected in one day this will have to be measured to the minute, or whatever is appropriate.  I've added SampleID so you have a unique, anonymous values which can be used to track sample through the lab, used on bar codes and things like that.  You don't say but I think you may want to have a SampleMaterial column to distinguish between, say, blood and urine samples because some tests will be applicable to one but not the other.

Now we have the sample in the lab we need to know what tests to perform.  I will assume you do not perform every test on every sample.  You need a table to hold this information.

Table: OrderedTest
    SampleID
    TestName

Perhaps you will need to record who ordered the test and when.  You can add to this table as required.

Knowing the TestName we can use TestComponents to find out what is to be measured.

Then we need somewhere to hold the actual measurements.  This will depend on the component and the sample.

Table: Results
    SampleID
    ComponentName
    MeasuredValue
    MeasuredAtDateTime

I'm assuming that a particular component will only be measured once even if it occurs in two tests.  For example, if Test A consists of blood sugar and vitamin A, and Test B consists of blood sugar and iron level, and both Test A and Test B were ordered, then blood sugar would only be measured once from each sample.

A way to check you model is to list the questions you want to ask of it.  Can your model answer those questions fully and unambiguously?  If not you need to talk to your users about how things work in the real world and add this new understanding to the model.

Finally, don't forget normalisation.  As you add more attributes into the model ensure you are adding them to the correct table.  You may have to add new tables and revise the relationships between existing tables.  Don't worry, this is normal in database design.

I hope I've given you some good pointers on how to proceed.  Concentrate on what people do and what they do it to.  This will be a good start.  Lab software is field in itself and can be complicated.  There are a number of commercial packages in existance.


selecting same column twice for different value

Assuming you always have pair , the following should give you desired result:

SELECT a.event_id ,a.pair,a.versus,
a.score as score1, b.score as score2
FROM table_name a
INNER JOIN table_name b on
(a.event_id = b.event_id and b.pair =a.versus and b.versus=a.pair and a.pair<b.pair)

Side notes.
1. It makes sense to have a surrogate primary key column in the table.
2. The query would look much cleared if you had one more column which serves as pair identifier  (ie. both 1-11 and 11-1 would share the same pair_identifier).
3. a.pair<b.pair added to avoid duplicates in resultset


Avoiding performance hit from GROUP BY during FULLTEXT search?

You could try looking at tmp_table_size variable and increasing it. The group by must do a file sort using a temporary table. It's probable the temp table exceeds the size allowed and is dumped to disk for the final sort processing (very slow).

If you do:

SHOW VARIABLES LIKE 'CREATED_TMP%'

And look at the ratio of Created_tmp_tables vs created_tmp_disk_tables, this should tell you if the value is too small.

see also: http://dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html


The first problem is that you don't have an END IF;.

The next problem (assuming that you aren't doing this and just didn't include it in the question) is that when you have a compound statement, you have to change the statement delimiter from ; to something else so that the client will send the entire CREATE TRIGGER statement to the server as a single statement.

Finally, the column "first_name" probably needs to be qualified with NEW (assume you're referencing a column in wp_usermeta called "first_name".

DELIMITER $$
DROP TRIGGER IF EXISTS update_customers $$
CREATE TRIGGER update_customers AFTER INSERT ON wp_usermeta
FOR EACH ROW
BEGIN
  IF NEW.wp_metakey = NEW.first_name THEN
    INSERT INTO test_customer(first_name) values (NEW.wp_meta_value);
  END IF;
END $$
DELIMITER ;


Executing a stored procedure which selects and inserts into tables in SQL Server

No, you don't need to grant explicit permission on Table1 and Table2, that's one of the objective of embedding code in stored procedure and that's where encapsulation feature comes into effect.

Please check below link from Microsoft:

Managing Permissions with Stored Procedures in SQL Server

  Stored Procedure Execution

  Stored procedures take advantage of ownership chaining to provide access to data so that users do not need to have explicit permission to access database objects. An ownership chain exists when objects that access each other sequentially are owned by the same user. For example, a stored procedure can call other stored procedures, or a stored procedure can access multiple tables. If all objects in the chain of execution have the same owner, then SQL Server only checks the EXECUTE permission for the caller, not the caller's permissions on other objects. Therefore you need to grant only EXECUTE permissions on stored procedures; you can revoke or deny all permissions on the underlying tables.

Use the code below to grant execute permission:

USE database_name
GO
GRANT EXECUTE ON USP_NAME
    TO User_name;
GO

Hope above helps.


Getting ratio of reads to writes on SQL Server

This will be incomplete, but it will give you a good idea of the ratios you're looking for. You can use sys.dm_db_index_usage_stats to retreive the access from various indexes on the system. If you combine the seeks, scans, and lookups you'll get a good idea of the reads. Something like this:

SELECT  ddius.user_updates,
        ddius.user_lookups + ddius.user_scans + ddius.user_seeks AS user_reads
FROM    sys.dm_db_index_usage_stats AS ddius

This data is cumulative, so you'll need to capture the previous day in order to find the differences between yesterday & today. As I say, this won't give you a perfect measure, but it will move you in the right direction.


Is an Oracle 10g instance dependent on the windows account authentication?

Oracle on Windows creates a service for database instances, called OracleService%ORACLE_SID%. By default, this service uses the Local System account so a password change of the installation user will not affect the database.

It is however possible and recommended to change the service user from Local System to a standard Windows account. If you performed this step, and you wish to change the password of that account, you must update the password in the service configuration.

Managing Oracle Home User

  You can change the password using Windows tools. However, when you
  change the password for this Windows User Account, you must also
  update the password for all Oracle services running under the Windows
  User Account.


What is the difference between terminating an Oracle sql statement with semicolon or with a slash on the next line?

There is no difference for a single SQL statement.  The '/' character on its own line tells SQL*Plus to execute the command in the buffer.  You can use the semicolon at the end of most SQL statements as a shorthand for the '/'.  If you want to execute a PL/SQL block or to execute a handful of SQL statements like CREATE TYPE, however, you need to use the '/'

SQL> begin
  2    null;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> create type foo as object (
  2    col1 number,
  3    col2 number );
  4  /

Type created.

Different tools may have slightly different conventions for how you execute multiple SQL and PL/SQL statements in a script so be aware that this is SQL*Plus specific.


Frequency distributions segmented by a field

For the first challenge, are you looking for something like this?

SELECT wgt, SUM(Case when household_size=1 then 1 else 0 end) AS SIZE1
    , SUM(Case when household_size=2 then 1 else 0 end) AS SIZE2
    , SUM(Case when household_size=3 then 1 else 0 end) AS SIZE3
    , SUM(Case when household_size=4 then 1 else 0 end) AS SIZE4
FROM tablename
GROUP BY wgt

Now for the fancier challenge, where @column will be your sp parameter:

DECLARE @column VARCHAR(100);
SET @column = 'household_size';
DECLARE @main_SQL VARCHAR(4000);

SET @main_SQL =
    'DECLARE dynamic_cursor CURSOR FOR
    SELECT DISTINCT ' + @column + '
    FROM tablename
    ORDER BY ' + @column + '

    --Now we will be using a cursor to iterate over
    DECLARE @value INT;
    OPEN dynamic_cursor
    FETCH NEXT FROM dynamic_cursor INTO @value

    DECLARE @sql VARCHAR(2000);
    SET @sql = ''SELECT wgt'';
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = @sql + '', SUM(Case when ' + @column + '='' + CAST(@value AS VARCHAR) + '' then 1 else 0 end) AS SIZE'' + CAST(@value AS VARCHAR)
        FETCH NEXT FROM dynamic_cursor INTO @value
    END
    SET @sql = @sql + '' FROM tablename GROUP BY wgt''

    close dynamic_cursor
    DEALLOCATE dynamic_cursor

    EXEC(@sql) '

EXEC(@main_SQL)

This part selectes the distinct values of the tablename's column received into a cursor

DECLARE dynamic_cursor CURSOR FOR
SELECT DISTINCT ' + @column + '
FROM tablename
ORDER BY ' + @column + '

Next we will iterate over the distinct values to dynamically build up a query that will look just like the one at the top of my post, so here is the basis for it:
start the select:

SET @sql = ''SELECT wgt''

the iterative part which dynamically adds the successive , SUM(Case when household_size=n then 1 else 0 end) AS SIZEn to the select (note that @value will be the distinct values that we got selected into the cursor):

SET @sql = @sql + '', SUM(Case when ' + @column + '='' + CAST(@value AS VARCHAR) + '' then 1 else 0 end) AS SIZE'' + CAST(@value AS VARCHAR)

finish the select:

SET @sql = @sql + '' FROM tablename GROUP BY wgt''


How to check if a record is linked with two or more values in a left join

GROUP BY Name
HAVING COUNT(*) >= 2

You may need to do something like this:

WHERE ...
  AND ( SELECT COUNT(*)
           FROM tbl
           WHERE Name = ...
           GROUP BY Name
           HAVING COUNT(*) >= 2 )

For checking for exactly a certain list:

           WHERE Category IN ('clothes', 'hats')
           GROUP BY Name, Category
           HAVING COUNT(*) = 2

(There can't be more than 2 after the GROUP BY; this confirms that both are included.)

           WHERE Category IN ('clothes', 'hats', 'scarves')
           ...
           HAVING COUNT(*) >= 2

would check for any 2 of those 3 (or all 3).


Can we use the multiple databases system concept in a single web application?

I don't see why not.  You would just create two data sources and potentially two data access layers if you want to take it that far.  At least for .Net/C#/VB.Net this wouldn't be a problem.
I'm not sure I understand the benefit in this case but if you feel you have a good reason to separate the database, there's no reason it can't be done.


How to restore mysql DB from only myi files?

No, it's not possible - you need the MYD file, too.  The MYI file is only the indexes and does not contain any data.  It is like trying to restore the contents of the book High Performance MySQL from only the index pages.


There are a least three major reasons for why you should upgrade to a more recent version, preferably to the current version.

As mentioned by @a_horse_with_no_name the autovacuum mechanism has been improved in many places since version 8.2.
You don't see frequent DELETEs, so the table bloat most probably comes from UPDATEs as @Milen commented. A new feature has been introduced with version 8.3, that counters this problem at its root: Heap-Only Tuples, I quote the release notes:

  Heap-Only Tuples (HOT) accelerate space reuse for most UPDATEs and
  DELETEs (Pavan Deolasee, with ideas from many others)

  UPDATEs and DELETEs leave dead tuples behind, as do failed INSERTs.
  Previously only VACUUM could reclaim space taken by dead tuples. With
  HOT dead tuple space can be automatically reclaimed at the time of
  INSERT or UPDATE if no changes are made to indexed columns. This
  allows for more consistent performance. Also, HOT avoids adding
  duplicate index entries.

Emphasis mine.
PostgreSQL 8.2 has reached end of life in Dec. 2011. No more security updates. Upgrade to a more recent version as soon as possible.

To find out whether there are, in fact, a lot of UPDATEs launched, use the configuration parameter log_statement and check your database log. Maybe a misconfigured app or a trigger launches a lot more UPDATEs than you think?

If you SET the parameter in a session it only changes for this session:

SET log_statement = 'all';

So, rather change it in your postgresql.conf:

log_statement = all

and reload. For instance with pg_ctl reload. But don't forget to change it back (and reload) or your log files may grow huge. Then check the log files to see what's actually going on.


"Access is denied" when connecting SSMS to Integration Services

Long shot perhaps, but worth checking file

\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini

or the equivalent on your setup. You may have to manually edit it with the instance name. Otherwise SSIS connections might be looking for an msdb of a default SQL instance that doesn't exist.


Scripts to disable constraints and indexes for data warehouse load

For a few things that you may have not considered:

If an index starts as disabled you'll end up rebuilding it after your script finishes.
If an index starts with page compression you'll rebuild it without compression.

For performance:

Since no other process is accessing the table you could rebuild with the ONLINE = OFF option for possibly a small performance boost due to less locking.
Depending on the size of tempdb and your indexes you could use the SORT_IN_TEMPDB = ON option for a performance boost.
You're updating statistics on all tables, even if that table hasn't been modified or had very few rows changed. You could try to be more restrictive in your updates and only look at tables that were modified since the last stats updated or were modified at all.
It looks like you're running all of these queries in a single session. Have you considered splitting up the work among multiple sessions?


Check current memory_limit of Replication Server version 15.7.1

Have you tried using the 'admin config' command to display current configuration settings?

admin config, memory_limit


Why is MySQL not using the index with the higher cardinality?

Cardinality

Even with high cardinality, the tipping point used by the MySQL Query Optimizer is either the key distribution or the storage engine.

Back on November 13, 2012, I discussed how lopsided keys can make the Query Optimizer choose different indexes (sometime not choose and index at all) : Must an index cover all selected columns for it to be used for ORDER BY?

In that post, I loaded a table call mf (Male Female) and stored a gender M or F. I inserted 37 Ms and 3Fs. I then ran explain plans on both Male and Female values crossed with MyISAM and InnoDB. Those choices of keys and storage engines yielded some interesting results.

Your Query

What you need to do is examine how distributed (or lopsided) your keys are.

You need to run the following two queries

SELECT IFNULL(source,'Total'),COUNT(1) RowCount
FROM tbl GROUP BY source WITH ROLLUP;

SELECT IFNULL(state,'Total'),COUNT(1) RowCount
FROM tbl GROUP BY state WITH ROLLUP;

This will give you a count of each value along with a total row count.

In my earlier post, I said I use a 5% rule-of-thumb. Look at the rowcounts for each value. If a rowcount for any particular source or state exceed 5% of the table's rowcount, the Query Optimizer throws one index under the bus and tries another index. In rare cases, it may just a do a full table scan.

If any key with a low rowcount does not yield a good index choice, you may need to recompute the index statistics for the MyISAM table (especially if the MyISAM table experienced lots of INSERTs, UPDATEs, and DELETEs, thus making the index statistics stale). Just run this:

ANALYZE TABLE tbl;

or to defragment the MyISAM table and recompute the index statistics, run

OPTIMIZE TABLE tbl;

Give it a Try !!!

UPDATE 2014-04-12 18:06 EDT

I like the answer posted by Alexandros (+1 for you).

I would like to update and expand my explanation by means of his answer.

You mentioned the following cardinalities

state has 22
source has 1122

That being the case, you need to run the following

ALTER TABLE tbl DROP INDEX state;
ALTER TABLE tbl ADD INDEX state_source_index (state,source);

Do not reverse the order of the columns. You should always index lower cardinality columns first.

OK, creating that index will make life better for your query. Or, will it ???

After creating the index, please run this query

SELECT
    IF(ISNULL(state)=1,'Total',
    CONCAT('Total Sources for ',state)) Statistic,RowCount
FROM
(
    SELECT state,source COUNT(1) RowCount
    FROM tbl GROUP BY state,source WITH ROLLUP
) A;

This will give you a count of all (state,source) key combinations in the table and subtotals per state. Any RowCount of any key combination that exceeds 5% of the table will most likely result in a table scan.

UPDATE 2014-04-15 16:43 EDT

Your last comment was

  I am learning a lot from your answer. Thank you. What strategy would you suggest when when the key and multi-column key are lopsided? I don't understand why I should index lower cardinality columns first?

The order of the columns will help depending on the type of query you need.

If you run queries like these

SELECT ... FROM tbl ORDER BY state,source;
SELECT ... FROM tbl WHERE state='NY' ORDER BY source;
SELECT source,COUNT(1) RowCount FROM tbl WHERE state='NY' GROUP BY source;

Then, the index should be (state,source)

If you run queries like these

SELECT ... FROM tbl ORDER BY source,state;
SELECT ... FROM tbl WHERE source='blog' ORDER BY state;
SELECT state,COUNT(1) RowCount FROM tbl WHERE source='blog' GROUP BY state;

Then, the index should be (source,state).

I usually line of columns with lower cardinality first to make index scans a little smoother for queries where the lower cardinality is involved in the ORDER BY or GROUP BY along with other columns.

If you are not comfortable with the index being (state,source), you coudl just make both

ALTER TABLE tbl ADD INDEX state_source_index (state,source);
ALTER TABLE tbl ADD INDEX source_state_index (source,state);

Run your EXPLAIN plans on your queries and see which index get used more often.

Whatever key combination are lopsided and whatever order you have the columns in the compound index, evaluating the range and order of data is faster if columns being used are indexed in groups.

UPDATE 2014-04-16 12:17 EDT

If each (source,state) key combination needs to be ordered by date, you need to incorporate the date column in the index as the last column.

ALTER TABLE tbl ADD INDEX state_source_date_index (state,source,date);
ALTER TABLE tbl ADD INDEX source_state_date_index (source,state,date);

That way the ORDER BY does not have to trigger a sort.


User Rights - Can alter Sp's but not tables

I presume your users are creating reports or data extracts.  You can grant permissions to a particular schema, so you could create a separate schema for your users and have the stored procedures reside in that schema.  Configure the security on the rest of the objects so the users only have read permission.

You can also do something similar by creating a separate database and either require explicit reference to the tables or create synonyms within the reporting DB.


What is the best and most performant MySQL cloud solution?

I'd like to chime in on what has been said in the question and in the comments.

XEROUND

Exactly one year ago today, I wrote an answer about the Xeround DB Service : Database as a service (DbaaS) over dimensions--performance, scalability, & reliability?

Granted, you have the following:

Ability to create a Cloud Database and immediately put data into it without any strings attached
Access to a Whitepaper on the Xeround Storage Engine (Overview) from Feb 2012
There is a good FAQ section provided
There is an API for it

I just instantiated a Xeround Free Version Sevice and ran SHOW ENGINES; and got this

mysql> show variables like '%innodb%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| have_innodb           | NO    |
| ignore_builtin_innodb | OFF   |
+-----------------------+-------+
2 rows in set (0.01 sec)

mysql> show engines;
+---------+---------+-----------------------------------------------------------+--------------+------+------------+
| Engine  | Support | Comment                                                   | Transactions | XA   | Savepoints |
+---------+---------+-----------------------------------------------------------+--------------+------+------------+
| Xeround | DEFAULT | Xeround MySQL storage engine                              | YES          | NO   | NO         |
| MyISAM  | YES     | Default engine as of MySQL 3.23 with great performance    | NO           | NO   | NO         |
| MEMORY  | YES     | Hash based, stored in memory, useful for temporary tables | NO           | NO   | NO         |
+---------+---------+-----------------------------------------------------------+--------------+------+------------+
3 rows in set (0.01 sec)

mysql>

No InnoDB ??? Not much has changed.

How about variables for the Storage Engine?

mysql> show variables like 'xeround%';
+------------------------------------+-------------------------------------------+
| Variable_name                      | Value                                     |
+------------------------------------+-------------------------------------------+
| xeround_alloc_pages                | 1                                         |
| xeround_alloc_stats                | 0                                         |
| xeround_conditional_filtering      | off                                       |
| xeround_conf_dir                   | etc                                       |
| xeround_database_schema_size_limit | 2                                         |
| xeround_database_size_limit        | 10                                        |
| xeround_enable_client_socket_check | ON                                        |
| xeround_index_stat_is_approximate  | ON                                        |
| xeround_instance_id                | 35644                                     |
| xeround_maintenance_mode           | OFF                                       |
| xeround_query_row_cache            | 0                                         |
| xeround_record_count_adjust        | 12                                        |
| xeround_records_in_range_mode      | off                                       |
| xeround_schema_id                  | 115                                       |
| xeround_throughput_credit          | 100                                       |
| xeround_throughput_credit_seconds  | 10                                        |
| xeround_transaction_memory_limit   | 128                                       |
| xeround_transactional_ddl          | OFF                                       |
| xeround_xdapc_ip                   |                                           |
| xeround_xdapc_socket               | /opt/xeround/sys_35412/xdapc/xdapc_socket |
+------------------------------------+-------------------------------------------+
20 rows in set (0.01 sec)

mysql>

Looking at the API, I do not believe you can tune anything. You are basically at the mercy of the Storage Engine.

I have also tried loading data into it and using the INFORMATION_SCHEMA database to report back to me space usage. For the XEROUND Storage Engine, I get back nothing. MyISAM is OK.

There are still other limitations with Xeround (Oct 12, 2012) you have to read and consider.

Amazon RDS

While the service bears the hallmarks of a well-trusted brand name, the service must still be evaluated in terms of two things:

Cost
Configuration
Performance

While cost is at one's discretion, I will discuss the other aspects

CONFIGURATION

You have no access to a my.cnf
Any variables to be configured beyond default requires an API call to the DB Parameters Group followed by a restart of the RDS Instance.
There are two things you cannot change no matter what Memory Class you choose

max_connections
innodb_log_file_size

See my post from Aug 02, 2012 : Local database vs Amazon RDS

PERFORMANCE

This goes hand-in-hand with CONFIGURATION. Any tuning aspects you wish to make are subject to the limitations and functionality of the RDS API. Not all Performance enhancements for InnoDB are possible because of Amazon's policies meant to provide a share-everything environment for normal RDS users (unless you can come up with more $$$ for more IOPs and things of this nature).

EPILOGUE

Amazon EC2 may be your best option if your want more flexibility/autonomy for

configuring MySQL
voluntarily paying

RAM
Disk
IOPs

tuning InnoDB

Given the times you just reported in the question, nothing short of due diligence is required for tuning (possibly rearchitecting) your app for Cloud Usage.


Is it possible to upsert into a self-referencing table with a single statement in PostgreSQL?

Prepare the values to insert with a recursive CTE that pre-calculates the ids and defines an order. Then insert it in that order:

WITH RECURSIVE a_sort AS (
      /* get all entries without a parent (rank 1) */
      SELECT nextval('a_id_seq')::integer AS id,
             title,
             NULL::integer AS parentid,
             parenttitle,
             1 AS rank
      FROM a_source
      WHERE parenttitle IS NULL
   UNION ALL
      /* recursively get all the immediate children and increase rank */
      SELECT nextval('a_id_seq')::integer,
             src.title,
             a_sort.id,
             a_sort.title,
             a_sort.rank + 1
      FROM a_source AS src
         JOIN a_sort ON a_sort.title = src.parenttitle
)
INSERT INTO a
SELECT id,
       title,
       parentid
FROM a_sort
ORDER BY rank;

The beauty of the solution is that this uses the sequence behind a.id (a_id_seq in the example), so the sequence automatically has the correct value after we are done.

This solution assumes that the data in a_source are correct, i.e., do not contain cycles.


How do I check three conditions on a select with one query?

You can use Conditional ORDER BY and then use LIMIT 1 to restrict result-set to one row. This approach depends on a MySQL feature where it implicitly typecasts the result of a conditional expression, i.e., boolean to int (0/1)

SELECT * FROM emails
WHERE primary_key=?
ORDER BY
  user_id = ? DESC,  -- if user_id matches, it will be 1 and come first
  client_id = ? DESC
  -- does not matter if user_id matches,
  -- but if it does not, then client_id matches will come first
LIMIT 1


In SQL, is it composite or compound keys?

Both composite key and compound key describe a candidate key with more than one attribute. According to the Relational Database Dictionary (C.J.Date) they mean the same thing.

In ER modelling the term "compound key" also has a more specific meaning. It means a key whose constituent attributes are references to keys in other entities - i.e. a compound key forms an identifying relationship. For most purposes, this isn't an especially useful or important concept so the terms composite/compound are often treated as interchangeable. It's probably best to stick to "composite key" unless you are referring specifically to the ER modelling concept of a compound key.


It is possible but you will have two SQL server in place with only half licenses needed.
More appropriate would be to use an evalutation license (180 days) and the upgrade it to the proper full licence.
To do this use the proper DVD/ISO, Launch SQL Server "setup.exe", select maintenance tab and select edition upgrade.


Does Postgres read through entire WAL when starting replication from later point?

I think I found the explanation by looking at open file descriptors while running through some scenarios and waiting for the replication slot to start sending anything. It appears that Postgres does indeed parse a potentially large block of WAL before the replication slot send its first message.
Transactions spanning across several WAL files exacerbate the problem. If the slot's restart/flushed LSN is in the middle of a long transaction, and you tell Postgres you want to resume from that point, it will first read all the WAL files that encompass that transaction (potentially reading far back from where you left off, and far ahead of the requested starting point).
In addition to Postgres reading more WAL files than it does if you only have small transactions, the files which are part of a long transaction seem to take order(s) of magnitude longer to process.
Below are some excerpts from my test log, which include the output from a process monitoring osxfs's file descriptors.
Here the requested starting LSN is in the middle of a long transaction (ending at 0/213AF688), which appears to be why these files take a long time to process, and also why Postgres reads that far ahead before it send the first message (0/1892F2A8)
00:03:52.839   DB current LSN: 0/389866A8, test_slot_1 flushed currently to: 0/179F4948
00:03:52.993   Replication slot test_slot_1 opened with starting LSN: 0/18875A48
00:03:53.160   000000010000000000000017 is open
00:04:06.363   000000010000000000000018 is open
00:04:31.311   000000010000000000000019 is open
00:04:49.656   00000001000000000000001A is open
00:05:01.984   00000001000000000000001B is open
00:05:14.989   00000001000000000000001C is open
00:05:26.570   00000001000000000000001D is open
00:05:38.112   00000001000000000000001E is open
00:05:51.576   00000001000000000000001F is open
00:06:05.497   000000010000000000000020 is open
00:06:26.949   000000010000000000000021 is open
00:06:32.220   Received first message after PT2M39.227S

These WAL files, on the other hand, are full of single statement transactions transactions, Postgres doesn't take too long to go through them. It also doesn't seem to read any further ahead than the requested starting point.
23:30:40.633   DB current LSN: 0/389865C0, test_slot_3 flushed currently to: 0/26321EF0
23:30:40.757   Replication slot test_slot_3 opened with starting LSN: 0/37321EB8
23:30:40.847   000000010000000000000026 is open
23:30:41.522   000000010000000000000027 is open
23:30:42.525   000000010000000000000028 is open
23:30:43.395   000000010000000000000029 is open
23:30:44.278   00000001000000000000002A is open
23:30:45.271   00000001000000000000002B is open
23:30:46.143   00000001000000000000002C is open
23:30:47.137   00000001000000000000002D is open
23:30:47.764   00000001000000000000002E is open
23:30:47.891   000000010000000000000031 is open
23:30:48.023   000000010000000000000033 is open
23:30:48.151   000000010000000000000035 is open
23:30:48.279   000000010000000000000037 is open
23:30:48.289   Received first message after PT7.532S

What remains unclear is why there is such a big difference in how fast Postgres can get through the files it needs to, depending on whether they're part of a single long transaction, or full of smaller ones.
I also wish it was possible to control this behavior with some slot option, I get the feeling it is being overly cautious for my specific use case.


Error AWR Operation failed: CATPROC not valid when creating database with dbca

Try to rerun CATPROC on the database while connected as sys:

@?/rdbms/admin/catproc.sql

The recompile all invalid objects:

@?/rdbms/admin/utlrp.sql


Is MySQL smart enough to not do a full table scan for LIKE or NOT LIKE when there are other conditions?

Given NOT LIKE '%...' cannot use an index and id can, provided a index (include primary key) begins with id, then it will be used.

EXPLAIN [EXTENDED] {query} will show what indexes the query planner plans to use for the query.


Error on simple Select statement: Subquery returned more than 1 value

That particular error happens when you are using a subquery in such a way that only one row can be used but more than one row is returned.  For example using a subquery to return a column value.

Since this error only occurs when you have subqueries it is very likely that your tableName is actually a view.  You can easily check this by running the following command.

SELECT OBJECTPROPERTYEX(OBJECT_ID('dbname.schema.tableName'),'BaseType')

If it returns a V then it's a view.  You can then get the text of the view by using the following:

EXEC sp_helptext TableName


PostgreSQL leaves dead data in the table; the space can be reused, but the files won't shrink (significantly).

The official method to reclaim space is VACUUM (FULL), but that rewrites the whole table, which will be unavailable for any access during that time. There are extensions called pg_squeeze and pg_repack which do the same thing with less disruption "behind the scenes".

All these methods have in common that they will require enough free space to create a copy of the table, so you probably won't get around increasing the storage space anyway.

Now for the good news:

If you run a plain VACUUM on the table, which is not disruptive, the wasted space can be reused. So your next attempt to load the data won't increase the size of the table.


What's the (Big O) computational complexity of a PostgreSQL recursive common table expression?

OK, I'm going to propose a solution.  Using the excellent article "A Gentle Introduction to Algorithm Complexity Analysis" as a guide, I believe the worst-case complexity of the example in my question (above) is as follows.

Given this recursive CTE:

WITH RECURSIVE nodes(id, src, path, tgt) AS (
    -- Anchor member:
    SELECT id, s, concat(s, '->', t), t,
    array[id] as all_parent_ids
    FROM tree WHERE s = 'a'
UNION ALL
    -- Recursive member:
    SELECT t.id, t.s, concat(path, '->', t), t.t, all_parent_ids||t.id FROM tree t
    JOIN nodes n ON n.tgt = t.s
    AND t.id <> ALL (all_parent_ids)
)
-- Invocation:
SELECT path FROM nodes;

At the Anchor member (query definition), the algorithm selects each row from the table; therefore, at this step the maximum number of iterations (i) and the maximum size (n) is the number of rows in table; i < n, if a starting point within the table is specified.
The Recursive member selects each row from the table, starting from the position specified in the anchor member, so the maximum number of iterations here once again is: i ≤ n.

So, with the recursive CTE above I believe that the overall complexity is Θ(n).


Adding a new slave slowing down previous replicas?

Your new slave server can possibly be slow in some respect and not able to process the queries in the binary log as quickly as the other servers.

You should also look at the possibility that you may have run a very heavy query that caused the new slave to lag as well as the other slaves.

Regarding the new slave, you may have a performance problem that needs to be resolved. Possibilities could include:

Slow hard disks (you mentioned you are buying new ones)
Bad my.cnf configuration. Flushes too often to disk? flush method isn't O_DIRECT?
Long running queries from master server - consider using row-based replication if you feel it helps your overall system performance.
Caches on the new slave aren't warm for running the queries from the master - do you use INSERT INTO ... SELECT .. FROM statements? or non-deterministic insert statements like ones with sub-queries?
Raid card with battery - do the other servers have it and the slave doesn't?


Using mariabackup/xtrabackup vs block level snapshots

To answer my own question, GCP (and AWS) snapshots are mostly consistent, but in a very write heavy database, you would want to lock the tables for the duration of the snapshot backup. I believe there is actually an system level option to lock the drive itself for the snapshot, but that is not necessary for my use-case.

I ended up using both methods to backup. I scheduled an incremental snapshot to happen every 2 hours. Then I also do a mysql dump every night. For the mysqldump, it is done with the --single-transaction flag which will give you a consistent copy of the database. There is a momentary lock on all tables while a binary log point is found. But this is usually very short and not noticeable.

Binary logs are turned on and also included in the mysql dump. So this allows both methods to be used to do a point in time recovery. It is definitely possible that the snapshot image could create inconsistent copies, but it seems to not be an issue and innodb is smart enough to recover from most minor inconsistency issues.

The snapshots take very little storage space because they are only storing the delta with each snap. The mysqldump files do not, but if they become a problem, there are other backup options to store only a delta between each mysql dump.


Oracle How to Generate Multiple Ranges Based on a Result Set

Solved this with help from https://stackoverflow.com/questions/3664903/how-can-i-return-multiple-identical-rows-based-on-a-quantity-field-in-the-row-it

The resulting answer with the original table looks like this:

with qtys as (
  select 3 as qty from dual union all
  select 10 as qty from dual union all
  select 9 as qty from dual union all
  select 7 as qty from dual union all
  select 9 as qty from dual union all
  select 3 as qty from dual
)
, results as (
  select n qty
  from qtys x
  join (
    select rownum n
    from dual
    connect by level <= (
      select max(qty)
      from qtys
    )
  ) y on y.n <= x.qty
)
select max(qty) qty
from (
  select count(1)
  , qty
  from results
  group by qty
  having count(1) = (
    select count(1)
    from qtys
  )
);

I'm certain I can condense it a little, but it produces the desired result.

EDIT

I suppose looking closer at this taking the minimum would have produced the same, desired result:

with qtys as (
  select 3 as qty from dual union all
  select 10 as qty from dual union all
  select 9 as qty from dual union all
  select 7 as qty from dual union all
  select 9 as qty from dual union all
  select 3 as qty from dual
)
select min(qty) qty
from qtys;


Calculate hours based on a course schedule in mysql

Firstly, join the two tables on this condition:

attendance.room_id = schedule.room_id
AND schedule.`start` < attendance.`out`
AND attendance.`in` < schedule.`end`

The first predicate matches the rooms, obviously, and the other two determine if the ranges intersect. Any two ranges intersect if the beginning of each is less than the end of the other.

Secondly, take the time difference between GREATEST(attendance.`in`, schedule.`start`) and LEAST(attendance.`out`, schedule.`end`).

Finally, just aggregate the time differences per user.

So, the complete query might look like this:

SELECT
  a.user_id,
  SUM(TIMESTAMPDIFF(MINUTE,
                    GREATEST(a.`in`, s.`start`),
                    LEAST(a.`out`, s.`end`))) AS total_attendance
FROM
  attendance AS a
  INNER JOIN schedule AS s
    ON a.room_id = s.room_id
    AND s.`start` < a.`out`
    AND a.`in` < s.`end`
GROUP BY
  a.user_id
;

The result of this query is in minutes, as determined by the first argument of the TIMESTAMPDIFF function. Other than simply changing the argument to return the value in different units, you can also return it as time using the SEC_TO_TIME function like this:

SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, ...))) AS total_attendance

Specific formatting, like e.g. 3 hours 37 minutes, may be achieved using MySQL too, but normally it is better to use the presentation level for that.


Fire trigger after the complete transaction commit

You can get a behaviour similar to what you're asking by using the LISTEN / NOTIFY mechanism of PostgreSQL. You can use the payload of the NOTIFY to pass values to your external process.
From the documentation:

The NOTIFY command sends a notification event together with an optional "payload" string to each client application that has previously executed LISTEN channel for the specified channel name in the current database. Notifications are visible to all users.
[...]
NOTIFY interacts with SQL transactions in some important ways. Firstly, if a NOTIFY is executed inside a transaction, the notify events are not delivered until and unless the transaction is committed. This is appropriate, since if the transaction is aborted, all the commands within it have had no effect, including NOTIFY.

(emphasis mine)
The difference between this and a trigger is: the external script will not be started by the database. It needs to be a process that is programmed using a language that allows for connections to the database and some kind of callback system, and that can be started, and be listening to all notifications from the database. When a notification (message) is received, it needs to act accordingly. Your process will behave somehow like a daemon or service. I know you can do this using a program written in C, C++ or C#, but I'm not sure it can be done with a shell script.
See also Asynchronous Notification.


How to Parse a query in Azure Data Studio?

There is no key mapped but you can map it.

Go to:
File -> Preferences -> Keyboard Shortcuts
or
Ctrl+K Ctrl+S

Use the search feature or scroll down to "Parse Query" and setup a key binding


Why doesn't FIND_IN_SET return on NULL values

Well, NULL is not a string, not even an empty one. When you compare to NULL special operators are of need, like column IS NULL or the NULL-safe comparator <=>.

Usually this is solved with a query like

SELECT * FROM test
WHERE
  NOT FIND_IN_SET('test', result)
  OR result IS NULL;

I for one think, that the FIND_IN_SET() function shouldn't exist at all. It makes it seem like having comma separated values in a column is absolutely normal and quite common. But it isn't. You shouldn't store the values this way. You have a lot of disadvantages (see this post for a probably incomplete list) and you should really look into database normalization. Like Bill says in the linked post, comma separated values violate the very first normal form already.


Error connecting to 10.0.17-MariaDB-1~trusty

I started getting this "Lost connection to MySQL server at 'reading authorization packet'", and after reading https://bugs.mysql.com/bug.php?id=28359 I found out that the client machine (debian 64bits) network interface was in 100Mbps (it is a 1Gb interface and was working in 1Gb, not sure why it fell back to 100Mbps).

I could "normal ping" and nc, without problems, but a "ping -f -s 10000" from the client to the server would show 1% lost packets.

I disconnected its RJ45, connected it back "pumping" the connector back and forth (it's an old cable/machine, maybe some oxidation had settled in), and changed the switch port, and it auto-negotiated back to 1Gbps.

Then the "ping -f -s 10000" ran ok, and the mysql errors stopped.

So you might check for the connection between client/server. The "normal ping" (small packets may be ok) and nc (may be getting retransmissions, transparent to you) may be working, but a "ping -f -s 10000" can show how it responds under more load. It seems the "Server greeting" phase of the MySql auth does not like dropped packets.

Best regards,


Removing/overwriting sensitive data located in already deleted records in ibdata files

Switching to innodb_file_per_table is not enough because ibdata1 would still be the same size.

Any tables that were inside ibdatat1 and you ran OPTIMIZE TABLE against, it would simply manifest the table and its indexes to a .ibd file. You would still have the footprint of the table inside ibdata1. You are much better off restructuring InnoDB so that no data and index pages reside in ibdata1

Think about it: With innodb_file_per_table off, what goes inside ibdata1 besides data and indexes?

Data dictionary
Double Write Buffer (Background write to prevent reliance on OS caching)
Insert Buffer (Managing changes to non-unique secondary indexes)
Rollback Segments
Undo Space
Pictorial Representation

Some of these may eventually overwrite old data and index pages, but how long is eventually? Would it not be better to completely compress ibdata1? Basically, it would entail:

doing a mysqldump of all databases
deleting ibdata1
making sure innodb_data_file_path is the default (ibdata1:10M:autoextend) in /etc/my.cnf
starting up mysqld to let it recreate ibdata1
reloading the mysqldump.

Please see my past links on how to do so...

Oct 29, 2010 : My Original Post in StackOverflow
Apr 01, 2012 : Is innodb_file_per_table advisable?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Feb 03, 2012 : Scheduled optimization of tables in MySQL InnoDB
Nov 26, 2011 : ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full


Oracle expdp puts INDEXES to LOGGING mode

This is caused by transform=DISABLE_ARCHIVE_LOGGING:Y. Because of that parameter, the database creates indexes with NOLOGGING property to avoid generating redo, and at the end, it changes the index to the original value, which is in this case LOGGING.

But for indexes on GTTs, specifying NOLOGGING or LOGGING is not supported.
Data Pump manages to create the index without specifying NOLOGGING (because that would not make sense), but at the end, it still tries to change its property to LOGGING.

This bug was reported more than 2 years ago, and it is said to be fixed in 12.2.

  Bug 18366521 : IMPDP ENDS WITH ERROR USING TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

I have just tried this in a 12.2 instance, and it is indeed fixed.

A workaround is to handle the GTTs seperately, without specifying transform=DISABLE_ARCHIVE_LOGGING:Y.


restoring SQL Server from backup into a database with different database name?

I found this link to be particularly helpful. I'll summarize below:
you are probably trying to restore the database on another existing active database. You can run the following query (albeit with your details filled in) to restore your database:

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\BackupAdventureworks.bak'
WITH REPLACE


Updating records in MS Access linked table will activate triggers?

Yes. The Access frontend will not suppress the SQL procedures.


Any Reason to have AlwaysOn Failover Clustering in Azure Cloud?

If you are using Azure VMs to host your SQL server, in my personal experience there is one major factor for building an AlwaysOn FCI - the failover process happens much more quickly than the migration of an entire VM.

From what I've seen, you're looking at a difference of tens of seconds - minutes for a VM failover, to 1-2 seconds for an AlwaysOn FCI failover running in synchronous commit mode.

In the organisation I work in, that difference is critical, and an extra 60 seconds of down time can cost the company a significant amount of business and money.

I think it's also important to understand what causes these kind of issues - what will you do if an entire locally redundant system goes down, and immediate failover isn't possible? This actually happened to us, after which we realised that both halves of our two node in AlwaysOn FCI  were actually running on the same rack! (Eek) sufficed to say that this is no longer the case. We now replicate between two completely separate regions.

As for Azure SQL databases, this comes down to DR-scale events usually - entire data centre outages etc.

In summary, don't put all your eggs in one basket, and don't assume the word 'cloud' means that you don't need to consider DR and HA.


What's the most efficient way to safely convert from Datetime2 back to Datetime

You should use the TRY_CONVERT function Docs
It will return null if value is incorrect
SELECT try_convert(datetime,'1700-01-01')


Strategy for standby reporting with different database versions

The easiest options are to either upgrade the source instance to the same version as the target (or otherwise ensure the database engine versions are compatible), or perform a restore from a full backup every day to refresh.
For example, say the source was SQL Server 2008 and the target was SQL Server 2012. Upgrading the source installation from SQL Server 2008 to SQL Server 2012 would help ensure a database version match so STANDBY could be used.
Applying changes to a lower SQL Server version will require a bit of development effort.


Run job once a minute, every day, except during a certain time

I don't think you can work with that single job to get that exact schedule.  You might want to consider having another job that disables/enables the job for the period of time that you don't want it to run.  Another solution would be to add another conditional logic to your existing job and do a check on the current day of the week and what time it is, and decide whether or not to execute your job's logic.

A few alternatives here, but I think you'll have to get creative with your relatively complex schedule.


You should definitely (in my opinion) not have 142 tables - it'll be a complete mess to name, index and maintain, and you'll generate yourself a lot of extra work if you some day add another category, if you need to move ads from one category to another, etc.

Storing JSON blobs in the database will kill performance when you're performing searches, so I wouldn't go with that either.

A good relational table design (yes, with joins) is really the best approach when you're using a relational database system. For instance, one table with categories (12 records), joined to one table with items (142 records), joined to a table of ads.


Getting Only What Changed from SQL Server 2005

I would change the process to be more like the following.  Not the best solution, but there aren't that many good options to begin with.

Import Data Process:
a) - Drop yesterday's table
b) - rename current table to *_yesterday
c) - Import from system to current

Create view in Store's SQL Server that will return only rows that are new/different between current and _yesterday's tables. Something like the below.

SELECT Current.*
FROM Current
LEFT OUTER JOIN Yesterday ON Yesterday.PK = Current.PK (or list each field out seperately)
WHERE Yesterday.PK IS NULL (so the new ones)
OR ISNULL(Yesterday.Field1, '') <> ISNULL(Current.Field1, '')
<repeat>

2) - Move data to HQ
a) - Use view created to just pull across only the new records (possibly into staging table).  Then use merge? (is HQ a higher version of SQL?) to push the newly updated data into HQ's copy of the data.


Search for Arabic text ignoring diacritics, alef hamza differences, and kashida in SQL Server and Oracle

Not sure but based on my understanding, this is related to the language/character set.

I use Oracle database and I do store arabic values within the database.

The character set I use is UTF8, I have also tried storing the different examples provided for the same word and I see it working fine.

I am sure SQL Server also supports such character set.


Is SELECT * ok in a Trigger. Or am I asking for trouble?

Typically, it is considered "lazy" programming.

Given that you're specifically inserting two values into your TestAudit table here, I'd be careful to make sure your select is also getting exactly two values. Because if, for some reason, that Test table has or ever gets a third column, this trigger will fail.

Not directly related to your question but if you're setting up an audit table,  I'd also add some additional columns to your TestAudit table to...

track the action you are auditing (delete in this case, vs inserts or updates)
date/time column to track when the audit event occurred
user ID column to track who carried out the action you are auditing.

So that results in a query like:

INSERT dbo.TestAudit([ID], [Person], [AuditAction], [ChangedOn], [ChangedBy])
SELECT [ID], [Person],
   'Delete', -- or a 'D' or a numeric lookup to an audit actions table...
   GetDate(), -- or SYSDATETIME() for greater precision
   SYSTEM_USER -- or some other value for WHO made the deletion
FROM deleted

That way you're getting the exact columns you need and you're auditing what/when/why/who the audit event is about.


MySQL - include a default value in LOAD XML INFILE statement

When one needs to do something more complex that is available in the syntax, it is often useful to 'stage' the data through a temp table:

Perhaps:

CREATE TEMPORARY TABLE tmp ( ... DEFAULT 'myfile' ... );
LOAD XML ...;
INSERT INTO tab1 SELECT * FROM tmp;

or:

CREATE TEMPORARY TABLE tmp LIKE tab1;
LOAD XML ...;
UPDATE tmp SET DEFAULT = 'myfile';
INSERT INTO tab1 SELECT * FROM tmp;


how to add loop in date column in sql through query?


Question about new behavior of current connection combobox in SQL Server 2012

Press Ctrl-U (focus the combobox) and then Alt+DownArrow - this will make the combobox drop down.


How to examine PostgreSQL server's SSL certificate?

It looks like OpenSSL's s_client tool added Postgres support using the -starttls in 1.1.1, so you can now use the full power of OpenSSL's command line tools without additional helper scripts:

openssl s_client -starttls postgres -connect my.postgres.host:5432 # etc...

References:

Git commit
s_client manpage


How to become an administrator for SQL Server

Since you have made sure that you have SQL Server installed on your machine all is left is to make sure that you follow the following steps to create a database.

1) Go to Server Explorer right click Data Connections and Select Create New Sql Server Database...

2) This will open a pop-up window enter your MachineName\SQLEXPRESS in Server Name section or select a server name from the Drop down list.

3) Enter the New Database Name.. .

And you are good to go.


Writing a large update/delete script that doesn't provoke locks

@Jacob - I would say we've all had an unpleasant experience at some point in our lives, so don't beat yourself up.  It's admirable that you're seeking guidance and learning from it.  So hats off to you.

Some key things to target might be the impacted rowcounts.

If you're inserting into @idsToDelete, ideally you have a recordset around 1000 rows or less.  Higher rowcounts (thousands+) will cause your queries to slow significantly.  I've done a side by side inserting into #temptable vs. @temptable with 1000+ rows, or using a CTE and you will find time and time again that the #temptable or CTE with higher recordsets will outperform @temptable significantly.
Deleting from a table where you are sure you're deleting more than 1 row (by ID or something), should include a WITH (ROWLOCK) hint.  This means that when scanning a table for a specified recordset to delete, it will not lock the entire tables and cause this massive blocking you're experiencing.

I suspect that a combination of the slowed performance against the table parameter with significant rowcounts, along with deleting potentially thousands of rows from each related table without that lock hint, you would find things crawl and show persistent blocking patterns.

Regarding the transaction, I would keep the transaction to deleting around the delete statements only, or consider utilizing/wrapping the tsql into a stored procedure.  It depends on the usage, but typically I recommend steering away from cursors, for performance and best practices.

I can provide an example too if that's helpful.  :)

And if you're ever still unsure, if you're able to find a second set of eyes  - that's saved a few too!

Cheers!

-skibunnysqldiva


An event has run for certain amount of time. How to find the time for how long this event has run in mysql?

Maybe you can create an evenlog-table. Then inside your event :

BEGIN
  SET @starttime = now();
  ....
  insert into event_log (name, starttime, endtime) values ('myevent', @starttime, now());
END

Like this you'll have a log of all your events over a longer period to compare.


Send a trigger to an application upon a specific SQL Server server usage pattern

There's a few gotchas in here: you've said that the queries will be short-lived, that you're going to have (relatively) high volume, that you're going to capture parts of the queries, and that you want to communicate back to the app tier.  This means you want a non-blocking, high-performance solution, and there's nothing built-in that will make this easy.  I'm going to just talk theoretically about something you could build, but it ain't gonna be easy.

Part 1: capturing the query data as it happens.  For this, SQL Server's Extended Events are probably the way to go.  Think of it as debug points in the SQL Server engine code where you can insert your own code.  As you learn about it, you'll find that you can gather all kinds of cool information about queries, but keep in mind that this is blocking code, and the more information you want to gather, the slower your queries go.  You won't be able to push data outside of SQL Server in this part - you're going to have to either write the data to an XML file, or put it in the ring buffer.  The ring buffer makes the most sense here since you'll need to query it in near-real-time.

Part 2: getting the data from the SQL Server's ring buffer to the client.  You could build something to push the data from SQL Server to the overlay tier, but this screams danger to me.  It couldn't be single-threaded, because you could hang on trying to send a notification to a client that's running slow, and miss everybody else's notifications.  Instead, what I think you want to do is have the overlay app detect on the client side when it needs to check the ring buffer, and then do that via a query.  You don't want all the clients continuously polling the ring buffer - performance nightmare there.


SSIS best practice to allow easily swappable/ secure/ accessible Database Connections?

When the packages are deploy to and executed from the SSIS Catalog variables from an Environment can be used to store credentials such as those you mentioned, with these then used by the connection managers of packages.  Once the value of an environment variable is changed, this is propagated to all packages using this variable.  For instance, if you had a variable called "Server" that's used as the ServerName property of a connection manager, and you updated it to hold production server name instead of the test one all packages with this would now be pointed to the prod server.  Additionally, environments can also be created for test, development, prod, etc., with the referenced environment on the package then updated to point to a different environment.  The variables can also be marked as sensitive to protect information such as passwords.  If you haven't installed the catalog (SSISDB), I'd recommend doing so.  You can learn more about the SSIS Catalog here.  A high level overview of configuring a connection manager to reference an environment variable is below.

Right click the package (or project) and select Configure.  Then on the References pane press Add and choose the environment you created.
Still on the Configure page, go back to the Parameters pane, then the Connection Managers tab.  For the connection manager that will be configured, select the property that will be set from an environment variable by clicking the ellipsis next to the property and select the appropriate environment variables on the next window that is displayed.
When running a package that references an environment from SQL Agent make sure to update the job step that calls the package with an environment reference.  This can be done on the Job Step by going to the Configuration tab, checking the Environment check-box and selecting the referenced environment.


SQL view, how to append results from tableB with the results from tableA

with cte1(Name, Year) as ( select Name, null as year from tablea),
cte2(Name, Year) as (select NULL as Name, year from tableb)
Select Name, Year from CTE1
UNION ALL
select Name, Year from CTE2

This should work, didn't read the question properly last answer :)


Job fails overnight intermittently because of a windows authentication failure to log on to SQL Server. How do I troubleshoot?

There might be something useful in the SQL Server ERRORLOG file but probably not.  It is probably a network timeout between the SQL Server and the domain controller.


64k blocks are usually best.  Log writes should actually max out at about 60k, but at 64k you are aligned with the size of the physical block on the disk.


Plenty of good reasons to move to InnoDB.

MyISAM has no crash recovery mechanism. A power break may render your tables useless. They can be corrupted (I've seen this happen plenty of times) and you may be able to salvage data -- or you may not be able to salvage data (and I've witnessed this as well multiple times). With InnoDB you have crash recovery mechanism.
MyISAM has no "hot backup", with the possible exception of file system/lvm/disk snapshots, which do require you to flush all tables to disk. You say your databases are extremely small, so this may not appeal to you too much; but the time it takes to flush your entire database to disk can be quite long for larger datasets. You have multiple ways of hot-backing up an InnoDB database.
MyISAM has poor index-write performance. Because of its lack of crash recovery mechanism, any write to an idnex page is immediately flushed to disk. I once consulted to a company where they had this MyISAM table being written to intensively -- it was so hard on the disk they had to put it on its very own server. Changed to InnoDB and Voila! All was relaxed: the transaction log takes care of all that for you.
MyISAM is very poor on highly concurrent apps. You can't write to the same table from two connections at the same time. This quickly leads to a bottleneck on heavy traffic sites, for example.
Likewise, once you do get a write lock on a table, no one else is then able to read from it (with a minor exception for specific INSERT types). With InnoDB you can have concurrent writes and non blocking reads.
MyISAM is hardly under any type of development. I don't recall any blog post by Oracle or anyone else saying "we've just improved foobar on MyISAM". InnoDB is under intensive development. You're likely to get new features and bugs fixes in InnoDB; not in MyISAM.

The above off the top of my head. I'm sure plenty more reasons will pop up once I submit; but these provide the general feeling for the disadvantages of MyISAM.

But - you said your databases are extremely small. Why then would it be so time consuming to migrate to InnoDB?


define/& is a simple text substitution mechanism that does not know anything about sql. And sql and the Oracle Database server do not know anything about the text processing mechanism. So the text that results from the 'define/&'substitutuin must be valid sql statement. Otherwise you get an error.

 CREATE TABLE P747||_MU as Select percent from Source_Table

is not a valid sql statement.

 CREATE TABLE P747_MU as Select percent from Source_Table

is  the valid statement, an so

Define PROJECT = 'P747';
Create table &&Project._MU as Select
percent
from Source_Table;

Avariable name like &&Project must be followed by a whitespace or and end of file or by a dot (.). The dot is removed after variable substitution.

Define PROJECT = 'P747';
Create table &&Project_MU as Select
percent
from Source_Table;

will not work, because the variable is &&Project_MU and not &&Project.

you mix up simple text substituion in the Toad editor ( 'define/&)  with sql string functions (to_char, ||). That are different things.


I don't think it's possible. PostreSQL session variables are meant for
mostly scripts to run in the same session. If you need persisting variables across user, or a place to store state -- PostgreSQL can do that too, just use a table.

From there you can limit access to the table through Pg access permissions or getter-setter functions.

Here is an example of such a scheme, https://www.depesz.com/2009/08/20/getting-session-variables-without-touching-postgresql-conf/


Error when running job using SSIS package

So the SSIS service has been installed, but is it actually running?  Also, make sure that the domain and username are the same as those you used to setup the service.  You need to check the permissions settings for the database and server you are trying to configure.


From the documentation about CREATE FUNCTION:

  result_rows
  A positive number giving the estimated number of rows that the planner should expect the function to return. This is only allowed when the function is declared to return a set. The default assumption is 1000 rows."

ROWS 0 is not allowed even in those functions (should be positive).

Since the function is not returning a set, you need to remove that ROWS n.


Is there a way of securing ProxySQL's own SQLite3 database?

I have yet to find a better answer for now other than the SQLite3 DB ProxySQL uses is unencrypted and there are no parameters to change this. It is strange since you can encrypt many other things, but data at rest seems unaddressed.
However one may use HDD encryption to reduce the chances of it being breached. Also, keep user passwords hashed in case your server security is broken, and your encryption keys encrypted.
Will update this answer if I find additional information.


Convert to 2 decimals and keep leading zero

You don't need both cast and round.
For example:
declare @val decimal(9,2) = '849.909839'
select @val [val]
, cast('849.909839' as decimal(9,2))*-1.00 [cast_first]
, cast(convert(float,'849.909839') *-1.00 as decimal(9,2)) [cast_last]

If you run this, you can see the problem. It's in the else clause. you should do a cast after you multiply by -1.00.


Help solving a Clustering Issue: [sqsrvres] ODBC sqldriverconnect failed

Please check if changing the time-out value for the SQL Server Resource in the Cluster Administrator helps.


What is the real difference between vacuum and vacuum analyze on Postgresql?

tl;dr running vacuum analyze is sufficient

Because vacuum analyze is complete superset of vacuum. If you run vacuum analyze you don't need to run vacuum separately. See the discussion on the mailing list archive.

Analyze is an additional maintenance operation next to vacuum. It is supposed to keep the statistics up to date on the table.

  Vacuuming isn't the only periodic maintenance your database needs. You
  also need to analyze the database so that the query planner has table
  statistics it can use when deciding how to execute a query. Simply
  put: Make sure you're running ANALYZE frequently enough, preferably
  via autovacuum. And increase the default_statistics_target (in
  postgresql.conf) to 100.

More info: https://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT


What is the optimal setting for innodb_buffer_pool_instances for a mid level AWS RDS server with 6gb buffer pool?

Short Answer:  Not a problem.

Long Answer:

The "instances" feature is new (as of 5.5.4) and exists to speed up certain things when the system is running at peak load.  But even then, it does not help the performance more than a few percent.  For lightly loaded servers, the improvement is probably not measurable.

What about "8"?  I have yet to hear of anyone getting in trouble when each instance is less than 1GB.  I suspect it is "advice", not "a hard and fast rule" to make each instance at least 1GB.  In fact, glancing at a few dozen servers, I see that almost half of them violate that.  I see one machine with 10 instances for only 2GB of buffer_pool.

I would trust RDS to provide reasonable settings.  If their settings are bad, they will be bad for all their clients.  Imagine how much hassling RDS would get!


Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' for SSIS

You could be dealing with a delegation/impersonation problem as DimUser suggested.

If your SSIS is just fetching data from one DB server and delivers it to the other the solution is much easier and should be to set up an SQL Server Agent Proxy.

If you are executing the SSIS Package from Visual Studio or SSMS by hand the package will try your Windows credentials to log on to the SQL Server. If your account has the correct rights it will succeed.

If you set up an SQL Server Agent Job to execute that package the service account running SQL Server Agent executes this package. He does not have your Windows credentials so he will try to use anonymous login resulting in this error message.

To enable SQL Server Agent to execute a package using a different account you have to set up three things:

Create a credential
Set up a proxy account
Configure the SQL Server Agent step to use the proxy account

Create a credential
Connect to the SQL Server that should execute your package. Right-click Security and select "New Credential...". Enter a descriptive name (Credential name), select the Windows account you intend to use (Identity) and enter the password. Click OK.

For initial testing, you can use your own Windows account. For production use, I would suggest creating a dedicated AD Service Account (with minimal permissions).

Set up a proxy account
Expand the SQL Server Agent folder. Open Proxies and right click on "SSIS Package Execution" selecting "New Proxy...". Enter a descriptive proxy name and use the credential you created earlier.

Configure the SQL Server Agent step to use the proxy account
Open your Agent Job, select the properties of your step executing the SSIS Package. Now you can select your proxy account in the "Run as:" drop-down list.

Additional setup:
If your package is deployed to the SSIS Catalog you need to grant the Windows login you used for the credential the "SSIS_Admin" role on SSISDB as well.

For that, you need to create the account as a regular Windows login in SQL Server (Public) and map the user to SSISDB using the SSIS_Admin role.


SSIS OLE DB Source Editor Data Access Mode: "SQL command" vs "Table or view"

I will make a small experiment. i will use SQL profiler to see what is going in the background while using an OLEDB Source in the two cases:

I have a Table named dbo.Table_1 that contains 3 columns (ID,name,department)

I used SQL profiler to Tune the database containing this Table and i used the 2 access mode, below the results:

Table or View - selecting only ID column

The profiler shows that the following command is executed

SELECT * FROM [dbo].[Table_1]

Even if you only select one column, the OLEDB Source reads all data then filters columns after reading them all.

SQL COMMAND

The profiler shows that the following command is executed

SELECT [ID] FROM [dbo].[Table_1]

Recently I published an article that contains more details, you can check it on the following link:

SQLShack - SSIS OLE DB Source: SQL Command vs Table or View


Is it possible to query for the job schedule description?

You can use sp_help_jobschedule which returns the schedule_description by calling another system proc, sp_get_schedule_description.

exec sp_help_jobschedule @job_name = 'Your Job Name', @include_description = 1

It'd probably be faster to roll this in a cursor instead of breaking apart that system proc which


Temporary Resolving State on Synchronous HA, is there a way around this?

It's difficult to answer the first part of your question as it seems to be related to the network and/or cluster configuration and not related to the availability mode. You should take a good luck at your cluster logs to see what is the root cause.

1.    Checking  cluster status
a.  Login to failover cluster manager console
b.  Click on the cluster name
c.  Check the summary details for errors
d.  Click on networks under the cluster name
e.  Check the summary and network connections tab at bottom of the window for errors
f.  Since this appears to be a 2 node cluster w/file share witness supporting AlwaysON, make sure each node and the FS has a vote in the cluster.

2.    Checking for file share witness errors
a.  Confirm the share is online and available to the network

3.    Obtaining cluster logs
a.  Login to a cluster node as an Administrator
b.  Examine both the System and Application event logs.
c.  Use powershell commandlet:  get-clusterlog  -uselocaltime ***this will save a cluster.txt file to C:\Windows\Cluster\Reports examine the log

As to your second question regarding patching, I assume that the nodes and the FS each have a vote.  If the secondary node is getting patched (windows patching or SQL SP/CU), the AG on the primary node will be available provided quorum is maintained between the primary node and the file share witness. If quorum is not maintained, the AG will shutdown  in order to protect the databases. The Availability mode is not a factor.


How to optimized mysql query having large dataset

First, if email_id and delivery_id together are a unique key, please add a primary -composite- key on both tables for (email_id, delivery_id)
Second, the concat is not necessary and will prevent the previous key from being used. Try:

SELECT ol.*
FROM open_log ol
JOIN sent_log sl
ON (ol.email_id, ol.delivery_id) = (sl.email_id, sl.delivery_id)


My SQL Replication to an online database server

You can use Tungsten for this, but only master->slave replication. Set up your local machine as a master, and connect tungsten to it. It will then run the statements as a normal client on the remote machine.


Vertical Elastic Query is not identifying Object

It looks permissions/authentication related to me.
Assuming that the 2 databases are on the same logical server, then your checklist of things to validate:
There is a login in the master database named
There is a user in the "carDB" database named  mapped to the login
The SalesLT.Addresstest table exists in carDB
The user  in carDB has SELECT permissions on the SalesLT.Addresstest table
Test the above by connecting to carDB as the user and selecting from SalesLT.addresstest
There is a MASTER KEY created in the database that you are querying from
There is a CREDENTIAL in the database that you are querying from whose identity matches
the LOGIN that was created - same name and password.
The EXTERNAL DATA SOURCE is created in the database you are querying from
The EXTERNAL TABLE is created in the database you are querying from and matches the schema of SalesLT.Addresstest


How to Import all the files in a directory with a single command using mysqlimport?

Do not use --tab option, use general syntax mysqldump [options] db_name tbl_name ... > dump.sql instead. You'll get an ordinary sql-script that can be executed via CLI-client mysql such way:

mysql -u dbauser -p < /path/to/dump.sql

Anyway to import different dumpfiles you have to use some shell script that build the list of files in the given directory and then proceed that list one by one like that:

#!/bin/sh
for file in /path/to/*.txt
do
   mysqlimport database $file
done
####


Need SQL Server VLAN advice.. is it best practice to have all SQL Servers under 1 VLAN/network zone?

Is it a best practice to have all SQL Servers in 1 or 2 VLANs within your businesses network?

This is enterprise and personal preference. Some enterprises setup areas for specific things, some don't. You might do it to ease certain administration items and others harder, but overall there isn't anything you specifically gain or lose from doing so.

  Also on firewalls between the SQL Servers & application servers - this is not recommended right, As there will be a bottleneck from the firewall?

This comes down to security requirements. Most enterprises have this in place, but again it's entirely dependent upon your requirements. If you have no requirements you may or may not choose to do this based on the security measures you'd like to enforce. Not everyone specifically wants all servers to be able to talk to all other servers on a network.


How and what is the most efficient way to join two tables, retaining a particular field from both?

It's not entirely clear what you are after but I think a FULL outer join would help:

SELECT
    COALESCE(a.Component, b.Component) AS Component
  , COALESCE(a.data1, 0) AS data1
  , COALESCE(a.data2, 0) AS data2
  , COALESCE(b.data3, 0) AS data3
  , COALESCE(b.data4, 0) AS data4
FROM
    table1 AS a
  FULL JOIN
    table2 AS b
      ON b.Component = a.Component ;

If Component is not UNIQUE on one (or both) of the tables, then you could aggreagte and then join:

WITH a AS
  ( SELECT
        Component
      , COUNT(*) AS cnt
      , SUM(data1) AS sum_data1
      , SUM(data2) AS sum_data2
      -- ...
      , AVG(data1) AS avg_data1
      -- ...
    GROUP BY
        Component
    FROM
        table1
  )
  , b AS
  ( SELECT
        Component
      , COUNT(*) AS cnt
      , SUM(data3) AS sum_data3
      -- ...
      , AVG(data3) AS avg_data3
      -- ...
    GROUP BY
        Component
    FROM
        table2
  )
    SELECT
        COALESCE(a.Component, b.Component) AS Component
      , COALESCE(a.sum_data1,0) AS sum_data1
      -- ...
      , COALESCE(b.sum_data3,0) AS sum_data3
      -- ...
    FROM
        a FULL JOIN b
            ON b.Component = a.Component ;


I believe something is missing in the configuration done for DB2 Link.

the connection string should looks like this First.

Provider=DB2OLEDB; User ID=db2user; Password=mypassword; Initial Catalog=mydatabase;
Network Transport Library=TCP; Host CCSID=37;PC Code Page=1208;
Network Address=111.111.111.333; Network Port=446; Package Collection=MSDB2COL;
Process Binary as Character=False; Connect Timeout=15; Units of Work=RUW;
DBMS Platform=DB2/AS400;Defer Prepare=False;Rowset Cache Size=0; Max Pool Size=100;
Persist Security Info=True;Connection Pooling=True;Derive Parameters=False;

Please add missing parameters, so system will not set Automatic/rando parameters, that should solve the issue.


SQL Server service shutdown due to 'bad?' SSL cert

There are applications out there that change the ACLs on the machine keyset container folder. When this occurs, legitimate keyset users loose access to these keysets. See KB 278381: Default permissions for the MachineKeys folders for the correct permission set.

If you want absolute confirmation, see BUG: You cannot enable encryption by using a certificate when SQL Server 2005 is running under the Network Service account for a 'workaround' (it involves compiling a C++ app that will reset the ACLs properly...). But you can simply set the ACLs according to mentioned KB278381 and see if the problem dissapear.

More related KBs:

Peer-to-Peer Framework APIs return a "PEER_E_NO_KEY_ACCESS" error message
How to troubleshoot the Data Protection API (DPAPI)
Error message when you use Service Broker or database mirroring to connect to an instance of SQL Server 2005: "Connection handshake failed"

I am yet to find which application changes the ACLs. My money is on some antivirus apps. Don't ask me how I know about all this random pieces of disparate infos...