How to overwrite csv file of MySQL result set?

According to the MySQL Documentation on SELECT ... INTO

  The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed. The character_set_filesystem system variable controls the interpretation of the file name.

I suppose the easiest way would be the following:

STEP #1

Change this line in the query

INTO OUTFILE '/var/www/backup/AppandFllups.csv'

to this

INTO OUTFILE '/var/www/backup/AppandFllups.tmp'

STEP #2

Add these lines to the cronjob just after the dump

cd /var/www/backup
mv AppandFllups.tmp AppandFllups.csv

The main idea is to have /var/www/backup/AppandFllups.tmp be nonexistent just before the dump. The output file gets renamed to something you already use elsewhere. So, there is no need to change other processes or scripts.

Give it a Try !!!


sstableloader returns `java.lang.NumberFormatException: For input string: "Filter.db"`

I've fixed this by upgrading Cassandra to the latest version; I was running the last 2.x release and now it's running the latest 3.0.7 version.


Dacpac deployment via sqlpackage, “DropObjectsNotInSource=True” is trying to drop .mdf & .ldf

If your mdf and ldf files are in a non-default location (or have a non-default prefix / name), you need to add that information to the SSDT project.  This will help the schema compare operation (that occurs when you do a publish) to see that the files are in the right place and don't need to be dropped / recreated.

Right-click your project -> Add New Item -> File Group File

You'll get something like this:

/*
Do not change the database path or name variables.
Any sqlcmd variables will be properly substituted during
build and deployment.
*/
ALTER DATABASE [$(DatabaseName)]
    ADD FILE
    (
        NAME = [SqlFile1],
        FILENAME = '$(DefaultDataPath)$(DefaultFilePrefix)_SqlFile1.ndf'
    )

Modify that script so that it points to the desired location of your primary data file (mdf) and your deploy should go much more smoothly.

Note that $(DefaultDataPath) is set by the settings of the target instance:

And $(DefaultFilePrefix) is set by the "TargetDatabaseName" in your publish.xml file.


MySQL 5.1: How to rollback partitioned table

ALTER TABLE .... REMOVE PARTITIONING

http://dev.mysql.com/doc/refman/5.1/en/alter-table-partition-operations.html

Only available from version 5.1.8 onwards.


How to find a specific column name in table design view?

If you start by looking at the table definition in the Object Explorer and expand the columns properties, you can simply type the first letter of the column you are looking for.  If you keep hitting the same letter, SSMS will cycle through all the columns that start with that letter...

if you then right click the column and and choose modify, it will open the designer at the right column...

(using ssms 17.9.1 - but worth a try)


What is the difference between these extracting date method?

As others have pointed out date(created), created::date and cast(created as date) are functionally equivalent.
If you want to be portable, use cast(). I think the :: operator is more common in "Postgres land".
However, date_trunc('day', created) is not equivalent to the other expressions, because it returns a timestamp value, not a date.
Neither of those expressions will make use of an index on created - you would need to create an expression based index with the expression used in your queries. The cast() expression will be rewritten to :: by the query optimizer, while date(...) will not. So if you create an expression based index, using cast() or :: won't make a difference between the indexed expression and the one used in the query.
But if you use date(..) or date_trunc() you will have to use the same expression in your queries to make use of the indx.


To insert a single row

INSERT INTO RR DEFAULT VALUES;

It is possible to insert multiple rows of default values by (ab)using MERGE

MERGE INTO RR
USING (SELECT TOP 1000 *
       FROM   master..spt_values) T
ON 1 = 0
WHEN NOT MATCHED THEN
  INSERT
  DEFAULT VALUES;


Error "ORA-0091:Invalid Character" when executing a PL-SQL statement in Oracle

As mentioned in the comments, your original problem is the unnecessary semicolon ( ; ).

Code Review

By building the query the way you did, you are introducing SQL Injection Vulnerability. (obligatory XKCD reference).  To prevent SQL Injections, use BIND variables.

Most RDBMS allow you to use ? as a place holder.  Oracle supports this and Named place holders (eg :SEARCH_NUM ).  By using a Bind variable, DATE data type will be bound as DATEs, etc.  This removes the necessity for TO_CHAR/TO_NUMBER.

Since your code is really dynamic, I highly advise using the DBMS_SQL package to parse your SQL statement and Bind the variables.  This is especially true if the usage of Binds will be dynamic also. (eg use two variables one time, use 3 another, etc.)

Documentation for DBMS_SQL can be seen here.

Example code:

declare
  qry                  varchar2(5000);
  effdate              date := to_date('5/19/2020', 'mm/dd/yyyy');
  user_id              varchar2(1000) := 'pantea';
  search_number        varchar2(50)   := '8080';
  deposit_filter_query varchar2(5000) := '  deposit_group_code =  -1
                                      and deposit_group_desc =  -1
                                      and currency_desc      = ''-1''
                                      group by customer_num
                                      having sum(dep_avg_balance) > 1'; -- removed semi-colon
  initial_dml           varchar2(5000) := 'insert into dep_filter_cust (col1, col2)' || chr(10) -- alway define the columns in INSERT
             || 'select distinct customer_num , :SEARCH_NUM
                from vmi_customer_deposit_detail v
                  inner join branchlf_user b
                on b.userid = :USER_ID
                  and dep_branch_cod = v.dep_branch_cod
             where effective_date = :EFFDATE' || CHR(10); -- chr(10) === \n; NEWLINE character for visualisation

actual_sql  varchar2(32767);

c   pls_integer;
ret PLS_INTEGER;

begin

  -- build your SQL (I do this in a separate function)
  actual_sql := initial_dml || ' and ' || deposit_filter_query;

  -- debug results
  dbms_output.put_line( actual_sql );

  -- get your cursor
  c := dbms_sql.open_cursor();

  -- parse the SQL
  dbms_sql.parse( c, actual_sql, DBMS_SQL.NATIVE );

  -- bind variables (this can be dynamic if needed)
  -- Oracle can use NAMED Binds
  dbms_sql.bind_variable( c, ':SEARCH_NUM', search_number );
  dbms_sql.bind_variable( c, ':USER_ID', user_id );
  dbms_sql.bind_variable( c, ':EFFDATE', effdate );

  -- execute
  ret := dbms_sql.execute( c );

  -- close the cursor
  dbms_sql.close_cursor( c );
end;
/

If you don't do that, at the very least, use the USING clause of EXECUTE IMMEDIATE.

eg

execute immediate actual_sql using search_number, user_id, effdate;


Diff in Transactional and Non-transactional tables

Transactional table means, if data manipulation done with in transaction then rollback / commit will work.

For Non Transactional table, You need to rollback the changes with manual code. No Impact of rollBack and commit. These tables are useful for performing the statements with high performance.

By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk.

If you are using a transaction-safe storage engine (such as InnoDB, BDB, or NDB Cluster), you can disable autocommit mode with the following statement:

SET AUTOCOMMIT=0;


Uninstalling / upgrading PostgreSQL on OSX

try to use the edb installer to upgrade your postgresql version[1].

If you want to make your life easer, I recommend you backup your databases, uninstall your current postgresql configuration and use the postgres app[2].

For a full list of installers, take a look here[3]

Hope it helps.

References:

http://www.enterprisedb.com/products-services-training/pgdownload#macosx
http://postgresapp.com
https://www.postgresql.org/download/macosx/


Moving a PostgreSQL database to a different AWS RDS instance

You can safely replicate the whole instance using DMS, then remove the unnecessary DBs on both resulting clusters.  After this, you will most possibly have excessive storage on the new instance - I think (but never tried) you can just do another such migration to a fitting (smaller) instance type.


Quorum for 3 nodes AlwaysOn Availability Group

You don't have a choice as to whether or not to have quorum: Always On Availability Groups are built on top of Windows Server Failover Clustering, which requires quorum voting to understand who the primary is for a given AG at a given time.

I think what you might be asking is whether or not you need an old-school quorum drive (like a Q drive on a SAN). These days, Windows Server Failover Clustering (WSFC) can either use a quorum drive witness, OR a file share witness.

Windows Server 2012 R2 includes dynamic witness capabilities: if you have an even number of voters (like if 1 of your 3 cluster nodes goes down), then Windows will automatically add in your witness in order to be a tiebreaker. That's why you want to configure the witness (either a disk or a file share) ahead of time - Windows will automatically manage its voting rights whenever you have node failures or network failures.

I hate telling people to read the manual, but in this case, Books Online's quorum section is actually fantastic. Just make sure to read the appropriate one for your version of Windows, because Win2008R2, 2012, and 2012R2 all have different quorum options.


Errors in the metadata manager. The process type specified for the Effect dimension is not valid since it is not processed

You are trying to process the dimension with a type that needs the dimension to be processed. For instance, you are trying to do a process indexes, but the dimension is not previously processed.

Change the process indexes by a process update or a process complete.


How to determine which `Foos` have no associated `Bars`

There are probably bars that have NULL in foo_id.

select 1 where 55 not in (select 1 union all select null)

The above query returns 0 rows.

The solution is to change the query to use NOT EXISTS instead of NOT IN as follows:

select * from foos f where not exists(select * from bars b where b.foo_id = f.id)

Not only is this safer as you don't have to manually exclude NULLs from the subquery, but also this construct will generally provide better performance. So for the most part, I recommend getting into a habit of doing it this way instead of using NOT IN.


What happens when there's no tempdb space for version store operations?

A transaction may get rolled back if the version store needs space and runs out. It won't wait for space in the version store, and really you wouldn't want it to wait.

Typically the version store is used by transactions with isolation levels that use optimistic locking (Snapshot isolation or read committed snapshot isolation for example). The intent in using these is usually to prevent readers from blocking writers in highly transactional databases, but if a writer has to wait for version store space to be freed it could cause extensive blocking of other writers and could conceivably become endlessly blocked. Instead, these transactions are rolled back.

The following errors arise from running out of tempdb space for the version store:

3959 - Version store is full. New version(s) could not be added. A transaction that needs to access the version store may be rolled back. Please refer to BOL on how to configure tempdb for versioning.
3966 - Transaction is rolled back when accessing version store. It was earlier marked as victim when the version store was shrunk due to insufficient space in tempdb. This transaction was marked as a victim earlier because it may need the row version(s) that have already been removed to make space in tempdb. Retry the transaction
3967 - Insufficient space in tempdb to hold row versions.  Need to shrink the version store to free up some space in tempdb. Transaction (id=%I64d xsn=%I64d spid=%d elapsed_time=%d) has been marked as victim and it will be rolled back if it accesses the version store. If the problem persists, the likely cause is improperly sized tempdb or long-running transactions. Please refer to BOL on how to configure tempdb for versioning.

As described in the error message, 3967 indicates that the version store has run out of space in tempdb and a specific transaction has been marked as the victim. This transaction will be rolled back if it hits the version store. If the open transaction does not modify any additional rows, requiring rows to be added to the version store, then it isn't rolled back.

3966 indicates a marked transaction (from 3697) has tried to access the version store and was subsequently rolled back.

3959 indicates that tempdb doesn't have enough space to hold the version store but there hasn't been a transaction marked as a victim as yet (meaning tempdb is out of space but no transactions tried to access the version store). Existing transactions would be unaffected but new transactions that need the version store would be rolled back.


Going from separate databases to shared databases and schemas (Multi Tenant Data Architecture)

I suspect it is how SSMS enumerates tables

However, you don't need DENY. Just don't GRANT.

Best practice would be something like this

Remove membership of the db_datareader and db_datawriter role(I never use them)
Create a specific role (with CREATE ROLE) and GRANT on the view to this.

Then, you don't need to DENY on the table because it won't be checked anyway due to "ownership chaining"

Saying that, I'd also use SCHEMAs so I don't even require GRANT to the view. GRANT once to the SCHEMA. For more, see

Schema design - best practices?
Over use/correct use of schemas?


What happens if the database node's network fails just after commit and before responding to the client

The simple answer is, that a transaction is committed after responding to the client. And it's the client that's sending the commit, the server doesn't decide on its own, when a transaction is finished. (I'm having a hard time finding reliable sources for this, I must admit, but you have my word. It makes sense)

I tested something similar after it wasn't clear to me when a statement is written to the slow-query-log (the manual isn't clear about this). For this I throtteled my network bandwidth in Linux with netem. The result was, that the statement duration in the slow-query-log includes the time until the result is transmitted to the client.
Feel free to do a similar test :)


What is the best way to store user profiles and logins?

In general, if tables relate one-to-one, put both sets of columns in a single table.  (There are exceptions, but I don't see one here.)

On the other hand, "progress" sounds like some type of log?  That is, there might be lots of rows for each user?  Then you must have 2 tables, one with user info, one with 'progress'.

The user table would have a user_id as the PRIMARY KEY and the log table would have an INDEX(user_id, datetime) or something like that.  (I am assuming there is a datetime/timestamp column in the log.)


What is the Difference between Data Transformation vs Transportation?

Transportation is just moving data from one place to another - in ETL, from source system to either staging area, data warehouse or data mart.

Transformation is changing data structure so that it meets data warehouse needs - i.e. star schema, denormalization, aggregation, calculations, data cleansing, data enrichment etc.

Both happen while ETL, but the "T" from "ETL" stands for Transformation.


Changing my answer, as I was incorrect in my previous assertion after testing. I believe this is another way of expressing that the value is either a password or another form of ciphertext (encrypted value) instead of a plaintext value.


Databases in Suspect state after enabling TDE

For the 3 suspect databases can you check the log files referred to in sys.master_files physically exist on disk and have the same access permissions as the other working database log files? Just that one of the error messages you posted said the log file is missing, and unless it's a red herring, that would make their problem separate to your TDE issues and explain the suspect status.

I've noticed this in your log: 04/13/2016 15:18:23,spid61,Unknown,Database SuspectDb was shutdown due to error 9001 in routine 'XdesRMReadWrite::RollbackToLsn'.

This can happen if there are transactions in the log file which cannot be rolled back due to a lack of space, forcing the db into suspect mode. Can you confirm the underlying disk has free space, and also that you have not set and hit a size limit on that database's files (e.g. that auto-grow is enabled and set to grow by a sane fixed amount)?


SQL - use variable as column in SELECT statement and WHERE clause

You need to use dynamic sql to use variables as column names, try:

ALTER PROCEDURE dbo.dayplanv00 (
    @dt nvarchar(255) = '[15-04-2017]'
    ,@fg nvarchar(255)= 'XC 1 IEC'
)
AS
BEGIN
    DECLARE @sql nvarchar(200)= 'Select ' + @dt + ' as dt from RollingPlanWK15 where FNGD = ''' + @fg + ''''
    EXEC sp_executesql @sql
END

Note, are you wanting those default values in there? would make more sense (in my opinion) to have it as a required field on the execution of the proc so you don't accidentally run it with no values and presume its right


How to check if all elements of an array exists in a table?

I recommend to use the application code to insert the array into a Temporary table, including an identity field so you can store every record, even if it is repeated. (the syntaxis is the same as CREATE TABLE but using CREATE TEMPORARY TABLE):

CREATE TEMPORARY TABLE A (
     id BIGINT NOT NULL AUTO_INCREMENT,
     number BIGINT NOT NULL,
     PRIMARY KEY (id)
);

Then make a query to check/determine if the number exists in the large table. Something like this:

--supose the temporary Table is A and the big table is B and the fields you are comparing ara called NUMBER.

SELECT A.id, A.number as ARRAY_NUMBER,
CASE
    WHEN B.number IS NULL THEN 'not found'
    ELSE 'found'
END as FOUND_IN_TABLE
FROM A LETF OUTER JOIN B ON A.number = B.number

good luck!


Reclaiming space from data files in Oracle

If the data files are growing then any file space you reclaim will be reused as the files grow.

Reasons that cause me to think about re sizing are:

after moving a table/partition/index from one table space to another
after truncating a large table (example: a logging table)

Reasons not to re size:

data files have to be empty to re size below the high water mark, 11g offers online reorganization where the files do not have to be empty
from here: a change that demands some preparation:

take a backup
Export the schema objects that are stored in the specific tablespace/datafile you want to resize.
Drop the exported objects.
Resize the datafiles.
Import the objects.
Perform any required maintenance, like grants, recompilation, rebuild indexes

requires downtime
for a growing table the space will be used again anyway


Why do the MySQL bin log files still exist after a purge or flush?

The PURGE BINARY LOGS statement deletes all the binary log files listed in the log index file prior to the specified log file name or timestamp. Deleted log files also are removed from the list recorded in the index file, so that the given log file becomes the first in the list.
I hope you have purged binary logs upto mysql-bin.000019 using command
PURGE BINARY LOGS TO 'mysql-bin.000019';

If you need to purge all logs do like
PURGE BINARY LOGS TO 'mysql-bin.000025';

This will remove binary logs upto mysql-bin.000025.
UPDATE
You can try
RESET MASTER;

RESET MASTER Deletes all binary log files listed in the index file, resets the binary log index file to be empty, and creates a new binary log file
The effects of RESET MASTER differ from those of PURGE BINARY LOGS in 2 key ways:

RESET MASTER removes all binary log files that are listed in the index file, leaving only a single, empty binary log file with a numeric suffix of .000001, whereas the numbering is not reset by PURGE BINARY LOGS.

RESET MASTER was not intended to be used while any replication slaves are running. The behavior of RESET MASTER when used while slaves are running is undefined (and thus unsupported), whereas PURGE BINARY LOGS may be safely used while replication slaves are running.

CAVEAT by RolandoMySQLDBA
If you run RESET MASTER with Slaves connected and running, the IO Thread of each Slave will immediately lose its place. Replication is thus broken and you will have to spend time getting the data on all Slaves sync'd again. If you want to safely delete binary logs from a Master without breaking Replication integrity, here is what you do:

Run SHOW SLAVE STATUS\G on each Slave.
Take note of Relay_Master_Log_File. This is the binary log whose latest statement was successfully executed in the Slave).
From all displays of SHOW SLAVE STATUS\G, determine which Relay_Master_Log_File is the oldest (For example, 'mysql-bin.00123').
You can run PURGE BINARY LOGS TO 'mysql-bin.00123'; None of the Slaves will lose its place.

The overall effect? This will leave behind binary logs on the Master whose statements that have not been executed on all Slaves as of yet.


SQL Server memory benchmarking - how to fill up memory?

The solution I came up with is a slight extension of the forum post I mentioned before (first make sure that your max server memory is set appropriately!):

DECLARE @sometable table (id uniqueidentifier)

insert into @sometable
SELECT NEWID()
FROM sys.columns A
CROSS JOIN sys.columns B
CROSS JOIN sys.columns C

Indeed you can see that SQL server starts to consume memory when running this query, and of course this does not cause SSMS to fail as there is only the usual "rows affected" output


Where Clause - Two columns of Primary table mapped to single column in Sec. table

You can join the rows using the following (as suggested by Akina):

WHERE TableA.Name || '_' || TableA.Company = TableB.Name


Can I convert a VARCHAR field body to unquoted JSON result in SQL Server 2016 CTP 2.X?

You'll need to wait for CTP 3 to use things like OPENJSON and JSON_VALUE. These constructs simply didn't ship in the CTPs up to and including CTP 2.4. That doesn't mean there isn't some way to get the result you want right now, but the amount of time and effort you might spend doing so is probably not worth it, especially if the functionality you need will be available in the next CTP, which should ship soon.


Regarding providing DBA access, you might be better-off creating an Active Directory group and adding the DBAs into that group.  That way, you can provide group-level permissions and give sysadmin to the group rather than individual users, meaning that when a DBA leaves (or a new one gets hired), you just need to adjust Active Directory rights rather than going into each SQL Server and altering logins.  The same applies (though without granting sysadmin!) for other groups of internal users.  Windows authentication is more secure than SQL authentication, and if you can use it, you probably should.

Regarding auditing, you can still have individual-level auditing when users are in a group.  You can use SUSER_NAME() to get the username in whatever process you're going to use to log activity.  Note that somebody could do an EXECUTE AS to switch their user context, but you should be able to log that as well as the IP address from which the query came, so you'd still be able to correlate activity to a person.

The idea of using a jumpstation (in your case, you mentioned a server) is possible.  It would make administration a bit easier, especially if the laptop domain does not have a full trust relationship with the domain upon which the SQL Server instances are located.  It would probably be a bit annoying for the support personnel, honestly, but if you go that route, I'd recommend a virtualized desktop for each IT support team member.  That way, they don't have to deal with server contention issues and you can minimize the pain.  If you do go down that route, then the IT support team members could have domain accounts and you'd create relevant Windows groups the same way as the DBA group.


Type conversion in expression may affect "CardinalityEstimate" in query plan choice?

The result of the query seems to be correct and the server executes it quickly.
But the root node of the execution plan has a warning:

Type conversion in expression (CONVERT_IMPLICIT(nvarchar(128),[o].[name],0)) may affect "CardinalityEstimate" in query plan choice

What does that mean in this context? Is such a complex filter confusing the optimizer? Is it something I should be worried about?

The warning is informational. If your query performed slowly, or you noticed that cardinality estimates were incorrect, the warning would give you information about where to look for a possible cause.
The warning is triggered by the implicit conversion used for the collation change. If using the collation is the easiest way to get correct results, feel free to leave it as it is. Alternatively, if you explain more about why it is needed, someone will advise you.
As an aside, the REPLACE could be replaced with:
REPLICATE(N'[0-9A-F]', 8);

(This answer is a summary of the comments to the question.)


SQL Server Standard Edition Failover Cluster Installing Windows Updates

Yes, that is how we need to roll out patches in the sql cluster with minimum downtime. The downtime will be during the failover part.

Process:

Patch the passive node first.
Move the resources to the passive node.(This will become active now)
Patch the current passive node.(It was originally active).

Before doing this do test it in a Test environment and prepare a full proof plan.

Also refer to this link :

https://blogs.msdn.microsoft.com/john_daskalakis/2014/09/05/how-to-install-a-service-pack-at-a-sql-server-2012-failover-instance-best-practices/


I find no MySQL functionality to achieve what you need, you have to create what you need.

If you use a statement like:

GRANT EXECUTE
ON FUNCTION `yourdatabase`.`yourfunction`
TO 'youruser'@'localhost';

will apply:

  14.7.1.4 GRANT Syntax

  ...

  Important

  MySQL does not automatically revoke any privileges when you drop a
  database or table. However, if you drop a routine, any routine-level
  privileges granted for that routine are revoked.

  ...

One option is to use a statement such as:

GRANT EXECUTE
ON `yourdatabase`.*
TO 'youruser'@'localhost';

of course, considering that possibly will be giving more privileges to the user really wants to give.

Another option is to have two user (internal and external) and two functions (real and mock), the real function that periodically removed and the mock function that does not eliminate. The external user only has the privilege to EXECUTE mock function and internally mock function call real function and will execute with internal user permissions.


How to grant connect and read/write permission to ALL databases?

You indeed have to add the user to the db_datareader and db_datawriter roles for each of your existing databases.

Such a query can be used for existing (and ONLINE) user databases:

DECLARE @user sysname = 'userTest1';
DECLARE @login sysname = 'userTest1';
DECLARE @SQL nvarchar(max) = '';

SELECT @SQL = @SQL + '
    USE ' + QUOTENAME(NAME) + ';

    IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name = ''' + @user + ''')
    BEGIN
        CREATE USER ' + QUOTENAME(@user)
           + ' FOR LOGIN ' + QUOTENAME(@login)
           + ' WITH DEFAULT_SCHEMA=[dbo];
    END
    ELSE
    BEGIN
        EXEC sp_change_users_login ''Update_One'', ''' + @user + ''', ''' + @login + ''';
    END

    EXEC sys.sp_addrolemember ''db_datareader'', ''' + @user + ''';

    EXEC sys.sp_addrolemember ''db_datawriter'', '''+ @user + ''';
'
FROM sys.databases
WHERE database_id > 4 AND state = 0;

--EXEC sp_executesql @sql;
print @sql

This will print the first 4000 chararacters from @sql. Comment out the EXEC line to execute it.
The user is created if it does not exist or the link to the login is updated if the user is already there
It then add the user to the db_datareader and db_datawriter roles
Any other right or role needed can be added to the query

New databases are created from model and will inherit its setting.
The User can be added to the db_datareader and db_datawriter roles on model:

use model
go
CREATE USER [userTest1] FOR LOGIN [userTest1] WITH DEFAULT_SCHEMA=[dbo];
EXEC sys.sp_addrolemember 'db_datareader', 'userTest1';
EXEC sys.sp_addrolemember 'db_datawriter', 'userTest1';

The first query can take care of both model and user database using this WHERE clause:

WHERE (database_id > 4 or name = 'model') AND state = 0;

Note (From Kenneth Fisher): With SQL Server 2014, there are some new permissions CONNECT ANY DATABASE and SELECT ALL USER SECURABLES that would cover the connect and read but unfortunately there is nothing similar yet for write permissions. (MSDN)


Powershell Get-Counter not returning values for SQL counters

The reason your code works when you run it as Administrator is that when you start the powershell console it's not elevated.

There are several options to elevate the script. One nice example from Ben Armstrong lets the script check if it's elevated and if it's not it's started in elevated mode and executes your code: http://blogs.msdn.com/b/virtual_pc_guy/archive/2010/09/23/a-self-elevating-powershell-script.aspx


show detail processlist like at mariadb on maxscale

Only users with PROCESS privilege can see processes other than their own.
From the SHOW PROCESSLIST Statement (MySQL Documentation):

SHOW PROCESSLIST shows which threads are running. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field.

And from Privileges Provided by MySQL (MySQL Documentation):

Enables display of information about the threads executing within the server (that is, information about the statements being executed by sessions). The privilege enables use of SHOW PROCESSLIST or mysqladmin processlist to see threads belonging to other accounts; you can always see your own threads. The PROCESS privilege also enables use of SHOW ENGINE.


Usually PostgreSQL 9.3 is generally faster then 8.3 - but hard to say what is wrong. Possible sources: problems with IO, wrong PostgreSQL configuration - max_connections = 1000 is probably terribly wrong value, default work_mem is usually too small, hitting hw limits (9.1 and higher should to better use more CPU), wrong testing ...

Other problems can be changes in pgBench. Result of pgBench 8.3 should not be exactly compared with results pgBench 9.3. If you would to have a good numbers, then you have to use pgBench 9.3 for PosgreSQL 9.3 and PostgreSQL 8.3. Don't forget - test should run minimally about 20 munutes (better hours).

Second factor - probably pgBench is not important for your production. pgBench is synthetic tests with disputable value. It is good for initial hw and sw testing, but is bad for configuration precision and database tuning. Much more important is speed of your application - you should to test a speed of slow queries or most often queries. 9.3 has lot of new functionality - better monitoring, more comfortable tools, better optimization for some kind of queries, built-in replication, online comfortable physical backup, much smarted vacuum (faster on large datasets), ...

Increasing of default_statistics_target had overhead in query planning - for extremely fast and simple queries it can be significant. On second hand, higher value decrease a probability of wrong estimation and wrong optimization - that is important for usual non trivial queries - planning is little bit slower (it can be visible in synthetic tests) but usage in production is much more robust. Depends on your application (if it use simple or not simple queries) this configuration variable can be important for you or not.


Does MariaDB support native JSON column data type?

JSON support will come to MariaDB 10.2. See the official MariaDB blog post from 28.02.2017. There are some example SQL statements and validation.

  JSON is fast becoming the standard format for data interchange and for
  unstructured data, and MariaDB 10.2 adds a range on JSON supporting
  functions, even though a JSON datatype isn't implemented yet. There
  are some reasons why there isn't a JSON datatype, but one is that
  there are actually not that many advantages to that as JSON is a
  text-based format. This blog post aims to describe JSON and the use
  cases for it, as well as to describe the MariaDB 10.2 JSON functions
  and uses for these, as well as showing some other additions to MariaDB
  10.2 that are useful for JSON processing.


How to rollback when 3 stored procedures are started from one stored procedure

Given only the code shown in the question, and assuming that none of the three sub-procs have any explicit transaction handling, then yes, an error in any of the three sub-procs will be caught and the ROLLBACK in the CATCH block will roll back all of the work.

BUT here are some things to note about transactions (at least in SQL Server):

There is only ever one real transaction (the first one), no matter how many times you call BEGIN TRAN

You can name a transaction (as you have done here) and that name will appear in the logs, but naming only has meaning for the first / outer-most transaction (because again, the first one is the transaction).
Each time you called BEGIN TRAN, whether or not it is named, the transaction counter is incremented by 1.
You can see the current level by doing SELECT @@TRANCOUNT;
Any COMMIT commands issued when @@TRANCOUNT is at 2 or above do nothing more than reduce, one at a time, the transaction counter.
Nothing is ever committed until a COMMIT is issued when the @@TRANCOUNT is at 1
Just in case the information above does not indicate clearly: regardless of the transaction level, there is no actual nesting of transactions.

Save points allow for creating a subset of work within the transaction that can be undone.

Save points are created/marked via the SAVE TRAN {save_point_name} command
Save points mark the beginning of the subset of work that can be undone without rolling back the entire transaction.
Save point names do not need to be unique, but using the same name more than once still creates distinct save points.
Save points can be nested.
Save points cannot be committed.
Save points can be undone via ROLLBACK {save_point_name}. (more on this below)
Rolling back a save point will undo any work that happened after the most recent call to SAVE TRAN {save_point_name}, including any save points created after the one being rolled-back was created (hence the "nesting").
Rolling back a save point has not effect on the transaction count/level
Any work done prior to the initial SAVE TRAN cannot be undone except by issuing a full ROLLBACK of the entire transaction.
Just to be clear: issuing a COMMIT when @@TRANCOUNT is at 2 or above, has no effect on save points (because again, transaction levels above 1 don't exist outside of that counter).

You cannot commit specific named transactions. The transaction "name", if provided along with the COMMIT, is ignored and only exists for readability.
A ROLLBACK issued without a name will always rollback ALL transactions.
A ROLLBACK issued with a name must correspond to either:

The first transaction, assuming it was named:
Assuming no SAVE TRAN has been called with the same transaction name, this will rollback ALL transactions.
A "save point" (described above):
This behavior will "undo" all changed made since the most recent SAVE TRAN {save_point_name} was called.
If the first transaction was a) named and b) has had SAVE TRAN commands issued with its name, then each ROLLBACK of that transaction name will undo each save point until there are none left of that name. After that, a ROLLBACK issued of that name will rollback ALL transactions.
For example, assume the following commands were run in the order shown:

BEGIN TRAN A -- @@TRANCOUNT is now 1
-- DML Query 1
SAVE TRAN A
-- DML Query 2
SAVE TRAN A
-- DML Query 3

BEGIN TRAN B -- @@TRANCOUNT is now 2
SAVE TRAN B
-- DML Query 4

Now, if you issue (each of the following scenarios is independent of each other):

ROLLBACK TRAN B once: It will undo "DML Query 4". @@TRANCOUNT is still 2.
ROLLBACK TRAN B twice: It will undo "DML Query 4" and then error as there is no corresponding save point for "B". @@TRANCOUNT is still 2.
ROLLBACK TRAN A once: It will undo "DML Query 4" and "DML Query 3". @@TRANCOUNT is still 2.
ROLLBACK TRAN A twice: It will undo "DML Query 4", "DML Query 3", and "DML Query 2". @@TRANCOUNT is still 2.
ROLLBACK TRAN A thrice: It will undo "DML Query 4", "DML Query 3", and "DML Query 2". Then it will rollback the entire transaction (all that was left was "DML Query 1"). @@TRANCOUNT is now 0.
COMMIT once: @@TRANCOUNT goes down to 1.
COMMIT once and then ROLLBACK TRAN B once: @@TRANCOUNT goes down to 1. Then it will undo "DML Query 4" (proving that COMMIT didn't do anything) . @@TRANCOUNT is still 1.

Transaction names and save point names:

can have up to 32 characters
are treated as having a binary Collation (not case-sensitive as the documentation currently states), regardless of the Instance-level or Database-level Collations.
For details, please see the Transaction Names section of the following post: What’s in a Name?: Inside the Wacky World of T-SQL Identifiers

A stored procedure is not, in itself, an implicit transaction. Each query if no explicit transaction has been started, is an implicit transaction. This is why explicit transactions around single queries are not necessary unless there can be a programmatic reason to do a ROLLBACK, else any error in the query is an automatic rollback of that query.
When calling a stored procedure, it must exit with the value of @@TRANCOUNT being the same as when it was called. Meaning, you cannot:

Start a BEGIN TRAN in the proc without committing it, expecting to commit in the calling/parent process.
You cannot issue a ROLLBACK if an explicit transaction was started prior to the proc being called as it will return @@TRANCOUNT to 0.

If you exit a stored procedure with a transaction count that is either higher or lower than when it stared, you will get an error similar to:

  Msg 266, Level 16, State 2, Procedure YourProcName, Line 0
  Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = X, current count = Y.

Table Variables, just like regular variables, are not bound by transactions.

Regarding having transaction handling in procs that can either be called independently (and hence need transaction handling) or call from other procs (hence not needing transaction handling): this can be accomplished in a couple different ways.

The way that I have been handling it for several years now that seems to work well is to only BEGIN / COMMIT / ROLLBACK at the outer-most layer. Sub-proc calls just skip the transaction commands. I have outlined below what I put into each proc (well, each one that needs transaction handling).

At the top of each proc, DECLARE @InNestedTransaction BIT;
In place of simple BEGIN TRAN, do:

IF (@@TRANCOUNT = 0)
BEGIN
   SET @InNestedTransaction = 0;
   BEGIN TRAN; -- only start a transaction if not already in one
END;
ELSE
BEGIN
   SET @InNestedTransaction = 1;
END;

In place of simple COMMIT, do:

IF (@@TRANCOUNT > 0 AND @InNestedTransaction = 0)
BEGIN
   COMMIT;
END;

In place of simple ROLLBACK, do:

IF (@@TRANCOUNT > 0 AND @InNestedTransaction = 0)
BEGIN
   ROLLBACK;
END;

This method should work the same regardless of whether the transaction was started within SQL Server or if it was started at the app layer.

For the full template of this Transaction handling within the TRY...CATCH construct, please see my answer to the following DBA.SE question: Are we required to handle Transaction in C# Code as well as in stored procedure.

Moving beyond the "basics", there are some additional nuances of transactions to be aware of:

By default, Transactions are, most of the time, not automatically rolled-back / cancelled when an error occurs. This is usually not a problem as long as you have proper error handling and call ROLLBACK yourself. However, sometimes things get complicated, such as in the case of batch-aborting errors, or when using OPENQUERY (or Linked Servers in general) and an error occurs on the remote system. While most errors can be trapped using TRY...CATCH, there are two that cannot be trapped that way (can't remember which ones at the moment, though--researching). In these cases, you must use SET XACT_ABORT ON to properly rollback the Transaction.

SET XACT_ABORT ON causes SQL Server to immediately roll-back any Transaction (if one is active) and abort the batch if any error occurs. This setting existed prior to SQL Server 2005, which introduced the TRY...CATCH construct. For the most part, TRY...CATCH handles most situations and so mostly obsoletes the need for XACT_ABORT ON. However, when using OPENQUERY (and possibly one other scenario that I can't remember at the moment), then you will still need to use SET XACT_ABORT ON;.
Inside of a Trigger, XACT_ABORT is implicitly set to ON. This causes any error within the Trigger to cancel the entire DML statement that fired the Trigger.
You should always have proper error handling, especially when using Transactions. The TRY...CATCH construct, introduced in SQL Server 2005, provides a means of handling nearly all situations, a welcome improvement over testing for @@ERROR after each statement, which didn't help much with batch-aborting errors.

TRY...CATCH introduced a new "state", however. When not using the TRY...CATCH construct, if you have an active Transaction and an error occurs, then there are several paths that can be taken:

XACT_ABORT OFF and statement-aborting error: Transaction is still active and processing continues with the next statement, if any.
XACT_ABORT OFF and most batch-aborting errors: Transaction is still active and processing continues with the next batch, if any.
XACT_ABORT OFF and certain batch-aborting errors: Transaction is rolled-back and processing continues with the next batch, if any.
XACT_ABORT ON and any error: Transaction is rolled-back and processing continues with the next batch, if any.

HOWEVER, when using TRY...CATCH, batch-aborting errors do not abort the batch, but instead transfer control to the CATCH block. When XACT_ABORT is OFF, the Transaction will still be active the vast majority of the time, and you will need to COMMIT, or most likely, ROLLBACK. But when encountering certain batch-aborting errors (such as with OPENQUERY), or when XACT_ABORT is ON, the Transaction will be in a new state, "uncommitable". In this state you cannot COMMIT, nor can you do any DML operations. All you can do is ROLLBACK and SELECT statements. However, in this "uncomittable" state, the Transaction was rolled-back upon the error occurring, and issuing the ROLLBACK is just a formality, but one that must be done.

A function, XACT_STATE, can be used to determine if a Transaction is active, uncommitable, or doesn't exist. It is recommended (by some, at least) to check this function in the CATCH block to determine if the result is -1 (i.e. uncommitable) instead of testing if @@TRANCOUNT > 0. But with XACT_ABORT ON, that should be the only possible state to be in, so it seems that testing for @@TRANCOUNT > 0 and XACT_STATE() <> 0 are equivalent. On the other hand, when XACT_ABORT is OFF and there is an active Transaction, then it is possible to have a state of either 1 or -1 in the CATCH block, which allows for the possibility of issuing COMMIT instead of ROLLBACK (although, I cannot think of a case for when someone would want to COMMIT if the Transaction is commitable). More information and research on using XACT_STATE() within a CATCH block with XACT_ABORT ON can be found in my answer to the following DBA.SE question: In what cases a transaction can be committed from inside the CATCH block when XACT_ABORT is set to ON?. Please note that there is a minor bug with XACT_STATE() that causes it to falsely return 1 in certain scenarios: XACT_STATE() returns 1 when used in SELECT with some system variables but without FROM clause

Notes about the original code:

You can remove the name given to the transaction as it is not helping any.
You don't need the BEGIN and END around each EXEC call


Why is SQL Server 2008R2 using more memory than the "max server memory" setting?

For SQL Server 2008 R2, "max memory" is for buffer pool only (basically data cache)
Other memory is used for other things.

This is changed in SQL Server 2012


Excel / MS SQL Server: Exception of type Microsoft.Office.PowerPivot.BackEnd.StorageException

Using the profiler I found, that Excel sends an EXEC sys.sp_prepare to return the meta data and compiles the execution plan.

When I executed the prepare statement in SSMS it hangs too, the same when I tried to display the estimated execution plan for the SELECT on the view itself. I let it run for a while and after about 7 minutes it was done compiling the query and showing me the estimated plan (sp_WhoIsActive showed me only about 30 sek CMEMTHREAD plus some minor wait stats for my query).

After this "initial" compiling both - SSMS and Excel - were able to load the data / show execution plans / preparing the statment without any delay.

Reason:

When I executed

SELECT STATS_DATE(s.object_id, s.stats_id) AS last_updated, s.*
  FROM sys.stats AS s
 WHERE s.object_id = OBJECT_ID('dwh.big_table')
 ORDER BY last_updated DESC

I found, that there are 4 auto generated statistics that were updated today, the last one exactly at the moment when the execution plan was finally generated. All updated statistics were on columns that were used in the query / view.

Obviously we had a problem with manually updating the statics (e.g. using Ola Hallengreens Maintenance Solution) after loading the latest data to the DWH and the Auto Update Statistics feature kicked in, when the query was first executed today. And while the statistics were updating, it could not compile an execution plan (e.g. since it does not know how many rows we have for the last x days and how much memory it needs to reserve).

Excel itself seems to have an internal timeout (found no way to configure it) of about 30 seconds regarding the sp_prepare statement and shows the very vague error message (Exception of type Microsoft.Office.PowerPivot.BackEnd.StorageException) when it does not get back the metadata for the query in this time.

Solution:

I moved the statistics update to a new (later) time frame, to ensure that the data was loaded first (before updating the statistics), so that there is no need for the Auto Update Statistics feature to kick in and block the table for several minutes...


mysqldbcompare SELECT command denied to user

You should enable the dbuser to have READ permissions to the proc table.

  The process attempts to perform a SELECT statement on the 'proc'
  table.  The 'proc' table is a system table which retains the
  procedures of all users on that mysql instance.  On shared hosting,
  allowing userA to see the procedures of userB, which would include
  information about table structure, etc., would be considered breach of
  security.All info found here

Or you can use the root user.


How to set up development servers SQL Server

Most of these topics aren't overly difficult but there are so many that I think you would struggle starting from scratch.

Furthermore because you're coming at it from a developer workflow point of view you'll likely discover that this kind of information doesn't get talked about much. You're likely going to have to pay for a consultant to come in, help you set up the dev server with basic backups and maintenance, and otherwise create a workflow for you.

  Does anyone know of a place where I can go to learn about this stuff?

If you were determined to do it yourself you'd search YouTube for videos on SSDT or SQL Server Database Projects which is part of Visual Studio; most of what you want is covered in some way by that (unit testing, source control through TFS or similar, deployments through msbuild, and also even schema compares). Unit testing is also commonly done with a framework called tSQLt. Deployments are sometimes done with Flyway. Database backups and maintenance are usually done with Ola Hallengren scripts or Minion Backup / Reindex.

You'd also take a look at SQL PASS which has a lot of chapters which have online training videos on each of those topics. However most of them will come up via a YouTube search.

Those are all free tools with caveats around Visual Studio of course.

For inexpensive non-tailored training Pluralsight has a video on each of these topics above and other database administration. But if you're not a DBA then you probably don't want to waste time on learning all of these, and you're still going to be stuck with creating your own workflow. There is no "developer workflow" from soup to nuts.

Red Gate does a lot of SQL Server developer software (in particular SQL Compare, SQL Source Control, and SQL Tests). You could search for their training videos for some ideas about how a workflow in these or other tools might look like. Again you can find these on YouTube, though they also publish some free downloadable books as part of their marketing.


Select data from a table to insert/update another similar table

Here's how to update the records that exist in catalog_product_entity_varchar and relate to a record in catalog_product_entity_text:
UPDATE catalog_product_entity_varchar cpev
INNER JOIN catalog_product_entity_text cpet
    ON cpet.attribute_id = cpev.attribute_id
    AND cpet.store_id = cpev.store_id
    AND cpet.entity_id = cpev.entity_id
SET cpev.value = cpet.value
WHERE cpet.attribute_id IN (<myids>)
  AND cpet.value IS NOT NULL;

After you do the update, then you can insert what's missing like this:
INSERT INTO catalog_product_entity_varchar (attribute_id, store_id, entity_id, value)
SELECT cpet.attribute_id, cpet.store_id, cpet.entity_id, cpet.value
FROM catalog_product_entity_text cpet
LEFT JOIN catalog_product_entity_varchar cpev
    ON cpet.attribute_id = cpev.attribute_id
    AND cpet.store_id = cpev.store_id
    AND cpet.entity_id = cpev.entity_id
WHERE cpet.attribute_id IN (<myids>)
  AND cpet.value IS NOT NULL
  AND cpev.attribute_id IS NULL; -- This last predicate ensures we only insert new records (i.e. the join didn't match any records in cpev to this record in cpet, if cpev.attribute_id is null here)


Want to perform a function on a returned table on subset of rows

Below is an example of how you might use the FIRST_VALUE and LAST_VALUE Windows Functions to calculate your Gain_loss column and then use a CASE expression to give you a Classification

SET NOCOUNT ON
declare @Table table (Col1 Char(1), Col2 Char(2), Col3 Char(2), [Year] int, [Year+n] int, Result int)
insert into @Table
    (Col1, Col2, Col3, [Year], [Year+n], Result)
    values
        ('A', 'UK', '1Z', 2015, 2, 30),
        ('A', 'UK', '1Z', 2015, 1, 20),
        ('A', 'UK', '1Z', 2015, 0, 10),
        ('A', 'UK', '1Y', 2015, 2, 30),
        ('A', 'UK', '1Y', 2015, 1, 35),
        ('A', 'UK', '1Y', 2015, 0, 40)

--Use the FIRST_VALUE and LAST_VALUE Window Functions to calculate the Gain_loss
SELECT Col1
    ,Col2
    ,Col3
    ,[YEAR]
    ,[Year+n]
    ,Result
    ,LAST_VALUE(Result) OVER(PARTITION BY Col1, Col2, Col3
                            ORDER by Col1, Col2, Col3, [Year], [Year+n]
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) -
    FIRST_VALUE(Result) OVER(PARTITION BY Col1, Col2, Col3
                            ORDER BY Col1, Col2, Col3, [Year], [Year+n])
    AS Gain_loss
    ,CASE
        WHEN
            LAST_VALUE(Result) OVER(PARTITION BY Col1, Col2, Col3
                            ORDER BY Col1, Col2, Col3, [Year], [Year+n]
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) -
            FIRST_VALUE(Result) OVER(PARTITION BY Col1, Col2, Col3
                            ORDER BY Col1, Col2, Col3, [Year], [Year+n])  > 0 THEN 'Up'
        WHEN
            LAST_VALUE(Result) OVER(PARTITION BY Col1, Col2, Col3
                            ORDER BY Col1, Col2, Col3, [Year], [Year+n]
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) -
            FIRST_VALUE(Result) OVER(PARTITION BY Col1, Col2, Col3
                                ORDER BY Col1, Col2, Col3, [Year], [Year+n]) < 0 THEN 'Down'
        ELSE 'NoChange'
    END AS Classification
FROM @Table
ORDER BY Col1, Col2, Col3 DESC, [YEAR], [Year+n]

That produces the following results (which appears to match your desired results)

| Col1 | Col2 | Col3 | YEAR | Year+n | Result | Gain_loss | Classification |
|------|------|------|------|--------|--------|-----------|----------------|
| A    | UK   | 1Z   | 2015 | 0      | 10     | 20        | Up             |
| A    | UK   | 1Z   | 2015 | 1      | 20     | 20        | Up             |
| A    | UK   | 1Z   | 2015 | 2      | 30     | 20        | Up             |
| A    | UK   | 1Y   | 2015 | 0      | 40     | -10       | Down           |
| A    | UK   | 1Y   | 2015 | 1      | 35     | -10       | Down           |
| A    | UK   | 1Y   | 2015 | 2      | 30     | -10       | Down           |

To learn more about SQL Server Window Functions, check out:

SELECT - OVER Clause (Transact-SQL)

How to Use Microsoft SQL Server 2012's Window Functions


Any problem will all InnoDB and one MyISAM table?

Is there a maintenance or tuning headache here? ABSOLUTELY !!!

Any benefit to it? I'll pretend I didn't read that.

The MyISAM storage engine always performs a full table lock with each DML (INSERT, UPDATE, DELETE). SELECTs block the daylights out of DML Statements.

Any transactions that mix MyISAM and InnoDB tables will cause even InnoDB tables to degenerate to full table locks because of the presence of even just one MyISAM table. I wrote about that in StackOverflow back in March 2011.

If the only MyISAM table is just the log table you mentioned, that is still very bad. The reason? If 50 DB Connections want to write a single row into the log table, there will 50 full tables locks, 50 inserts. Here is what is worse:

50 DB Connections, 1 performs table lock, does INSERT, 49 DB Connections have to wait
49 DB Connections, 1 performs table lock, does INSERT, 48 DB Connections have to wait
48 DB Connections, 1 performs table lock, does INSERT, 47 DB Connections have to wait
...
1 DB Connection, 1 performs table lock, does INSERT

Get the idea ??? This is what awaits you by making the log table MyISAM !!!

If the same log table was InnoDB, 50 DB Connections would perform 50 one line transactions that do not block each other.

Please read these questions for your own benefit on MyISAM and InnoDB

What are the main differences between InnoDB and MyISAM?
Benefits of table level locking
Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?
Is it common practice to mix InnoDB and MyISAM tables on same server?

CAVEAT : To be fair, if a MyISAM has a FULLTEXT, then you have no choice but to live with it. MySQL 5.6 will change that problem as InnoDB will support FULLTEXT indexes.


Trying to figure out when a table was dropped?

Postfactum you not available to see it

If you want to know this in the future - see all about DDL Triggers


"column ... does not exist" error when calling an SQL procedure (new in PostgreSQL 11)

The problem is with the parameter assignment in the call. To fix:
CALL config.sp_add_holiday(holiday_date => '2018-01-01', name => 'New Years Day');

db<>fiddle here
Explanation
Parameter notation is the same for functions and procedures (new in Postgres 11). The manual:

See Section 4.3 for the full details on function and procedure call syntax, including use of named parameters.

Bold emphasis mine.
You have been using = by mistake. That's the plain old "equal" operator, forming a boolean expression. Obviously not what you intended. The expression is evaluated, Postgres looks for a column named "holiday_date" in the calling SQL context and cannot find it. Hence the error message:

ERROR: column "holiday_date" does not exist

Only => (SQL-standard notation ) or := are valid for parameter assignment in the call. Details in the manual. Related:

Functions with variable number of input parameters

There is a history of misunderstandings around the use of ...

... the operators = and := in plpgsql code.
... the use of = as short syntax for DEFAULT in CREATE FUNCTION and now also CREATE PROCEDURE statements.
... the assignment notation => and := in function calls.

Further reading:

The forgotten assignment operator “=” and the commonplace “:=”


Log Shipping Copy Job Fails once in a while

This is not SQL-Server error actually,

This occurs when the LS Copy Job tries to copy the file on destination location(On Shared Network location)
Now, when the location is not available due to temporary connection loss with network, or due to any reason. (when its connected through internet & VPN reasons can be multiple)

So this can be broadly concluded as Network path was not reachable(not available at that time)

Solution: Check that if network location available before copy. If network location reachable then only Init copy.

I hope this answers what you are looking for.


Unable to deploy SSIS package to remote sql server

Have you tried to lauch VS in the same way you are using SSMS?
runas /netonly /user:domain\user "C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\Common7\IDE\devenv.exe"

Eventually you can build your SSIS solution with VS, copy the ispac file to your VM filesystem and thne import the ispac file inside the ssis catalog with the proper wizard.


How to determine which tables use a sequence in SQL Server

A quick test shows that sequence references are tracked via sys.dm_sql_referencing_entities in SQL Server 2014 SP2. I don't have access to SQL Server 2012, but you can use the following query to (have an admin) confirm whether they're tracked in 2012 as well. If so, this might provide a slightly more rigorous way to find the references you are looking for.

-- Find all objects that reference each sequence on the database
SELECT s.object_id AS sequence_object_id,
    s.name AS sequence_name,
    OBJECT_SCHEMA_NAME(o.parent_object_id) + '.'
        + OBJECT_NAME(o.parent_object_id) AS table_name,
    r.*
FROM sys.sequences s
CROSS APPLY sys.dm_sql_referencing_entities(
    OBJECT_SCHEMA_NAME(s.object_id) + '.' + s.name,
    'OBJECT'
) r
JOIN sys.objects o
    ON o.object_id = r.referencing_id


I would like to query a range of criteria on multiple columns in MySQL

Assuming you want to match against 1 1 1 you could do something like this:

set @fast:=1;
set @smart:=1;
set @sharp:=1;

SELECT if(`fast`=@fast,1,0) + if(`smart` =@smart,1,0) + if(`sharp`=@sharp,1,0) as score FROM `table` order by score DESC

That should return the exact matches with score 3, people with 2 matches with score 2 and so on.


I can recommend to use Robocopy, it has the support of multi threaded copy. And it won't take ages to calculate the packages before.

Your syntax would be as follows:

robocopy "C:\SOURCE\Folder\folder" "D:\folder\destination" /mir /MT:XX

I use the mir option, since it will copy everything, meaning doing a perfect mirror. So you don't have to worry if your copy job stucks for any reason. You can easily run the same command again and it will only copy the missing files.
Be careful if you have hidden files in your folder, for example if you have the files placed directly on your Drive root folder. Than you will have problems copying the recycler ;)

MT Parameter
The Command /MT will run your copies with multiple threads. You can choose a number between 1 and 128, default is 8.
Please read : msdn Robocopy

Also one advice is to copy from the machine with more CPU Power since a copy job will alwas run on the CPU where you are currently logged in. In most cases I copy from the destination server, since it is probably not in use so far.


Changing two publications at the same time causes deadlock in distribution database

It has been quite a while since I personally used replication, so I am not current on practical problems. A publisher with multiple subscribers is pretty common. However, Microsoft suggests using multiple Distribution databases when you have multiple Publishers.  The following post has direction:

http://technet.microsoft.com/en-us/library/ms183524.aspx

"However, if multiple Publishers use a single Distributor, consider creating a distribution database for each Publisher. Doing so ensures that the data flowing through each distribution database is distinct."

It sounds like a recommendation to me.


postgresql Cannot login with created users

It's just a case folding problem. You're trying to connect with myUser but due to case folding rules, you created myuser in lowercase, as the \du output shows. Per documentation:

  Quoting an identifier also makes it case-sensitive, whereas unquoted
  names are always folded to lower case.

To create myUser with this exact case, enclose it in double quotes, as in:

CREATE USER "myUser" WITH SUPERUSER PASSWORD 'password';

Then the invocation psql -U myUser ... will work.

Or alternatively, don't recreate the user but always refer to it in lowercase.


TEXT data migrate from Sybase to SQL Server

Stop using text data types -- they're long deprecated. If you need to, use NTEXT instead to preserve Unicode characters. But that's not a great idea.
Do the smart thing and use NVARCHAR(MAX) instead, because that's not deprecated, and will handle your Unicode long text data just fine.


PostgreSQL 9.6 performance problem with Sum() aggregate query

If your table is INSERT-only, there are ways to get your sums (much) faster.

Assuming there is a column with monotonically increasing values (like id or created in your example), create a MATERIALZED VIEW to pre-compute sums older than a (recent) given threshold. And then just add the sum of recent additions to it:

CREATE MATERIALIZED VIEW shares_summed AS
SELECT poolid, miner, SUM(difficulty) AS sum_diff
FROM   shares
GROUP  BY  poolid, miner
ORDER  BY  poolid, miner;  -- optional, but to optimize some more
WHERE  created <  '2018-01-01 0:0';

If there are many combinations of (poolid, miner), add a UNIQUE index on that. Also required to refresh CONCURRENTLY, see below.

Fetching a row from that table cost next to nothing. Then you only add recent additions to it:

SELECT sum(sum_diff) AS total_sum  -- takes care of possible missing rows
FROM  (
   SELECT sum_diff
   FROM   shares_summed
   WHERE  poolid = 'xmr1'
   AND    miner = '4BCeEPhod...'

   UNION ALL
   SELECT SUM(difficulty)
   FROM   shares
   WHERE  poolid = 'xmr1'
   AND    miner  = '4BCeEPhod...'
   AND    created >= '2018-01-01 0:0'
   ) sub;

You only have to adapt the threshold value from time to time and REFRESH the MV. Best with the CONCURRENTLY option, to avoid concurrency issues with your many INSERTs. Consider:

Analyze needed after a refresh materialized view?

Remember the value and adapt queries accordingly. You might store it in another table.

To make it even faster, create a partitioned table and add another partition with pre-computed sums after every month (or whatever). You can easily schedule a monthly cron job to automate this. Then the threshold is always the start of the month or so:

SELECT sum(sum_diff) AS total_sum
FROM  (
   SELECT SUM(difficulty) AS sum_diff
   FROM   shares_summed_master  -- includes all partitions
   WHERE  poolid = 'xmr1'
   AND    miner = '4BCeEPhod...'

   UNION ALL
   SELECT SUM(difficulty)
   FROM   shares
   WHERE  poolid = 'xmr1'
   AND    miner  = '4BCeEPhod...'
   AND    created >= date_trunc('month', now())  -- careful, current time zone affects it
   ) sub;

Make sure there is no overlap, and pre-computed sums are current.

Table partitioning was largely improved and simplified with Postgres 10. Be sure to run the latest point release, some corner case bugs were fixed in Postgres 10.2. The above link to the manual is for version 9.6. Current version here.


How to create a SQL Server user that can restore databases to a database server?

Whilst you can use the dbcreator role, I wouldn't personally. It can drop existing databases, and alter them.

In fact I tend to stay away from fixed server roles completely, in favour of designing my own server roles with appropriate rights.

I would do the following to grant the specific, granular right you need:

GRANT CREATE DATABASE TO [my_db_restore_user];

This should allow them to create databases only. Obviously it's worth thinking about the fact that this login then becomes the owner of that database and can subsequently drop it.


How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL?

If you want to see how many idle connections you have that have an open transaction, you could use:
select *
from pg_stat_activity
where (state = 'idle in transaction')
    and xact_start is not null;

This will provide a list of open connections that are in the idle state, that also have an open transaction.
Beware that the state column is present only in PostgreSQL 9.2 and above.
Having said that, I cannot recreate an open connection in the idle state that has an open transaction.  Perhaps someone else can provide details on how to do that.


Add a proxy account for Operating System (CmdExec) jobs and run the Ola jobs under that.  This way the proxy account can have proper access to only the backup shares, etc. and you don't have to elevate permissions on the SQL Agent account (and inherently any other jobs running under the SQL Agent security context).  Principle of Least Privilege for the win!  :)


Problem processing quotes by T-SQL during Job creation

Your code is nearly there, just change:

'...
WHERE m.mirroring_role_desc <> 'MIRROR'

to

'...
WHERE m.mirroring_role_desc <> ''MIRROR''

Jonathan Fite also mentions this nice QUOTENAME() solution in the comments below:

'...
WHERE m.mirroring_role_desc <> ' + QUOTENAME(MIRROR, '''')


How to create a table with a field 99% INT; 1% STR values?

Apparently you want to store some kind of range where most of the ranges just contain a single element and one of them is open ended.

You did not explain how you want to use those values.

If you only use them for display purposes and never, ever under no circumstance (ever!) have to do any calculations on them, a text column might be enough.

However, I have never encountered a situation where numbers weren't intended to be treated as numbers in the long run.

Depending on how you use the values, I can think of two solutions: either use a range type where most ranges only contain a single element. Whether or not that makes sense depends on the way you use that value. Maybe the 20 single values actually mean a range from 1 to 20 - it's hard to tell from your question

Or use a two columns: one integer and one indicating the "and above". If you only have the choice between "exactly that value" and "that value and above", I would probably use a boolean column that indicates that the value is the lower end of a range.


Why PostgreSQL's service are different between system service and pg_ctl?

If you start the server manually using pg_ctl, systemd doesn't know about it.

It will happily try to start the server, which will fail (because the server is already running), and it cannot stop the server, because it does not know it is running.

So always use systemctl when starting and stopping the service. Reloading with pg_ctl reload is fine.


What is the best way to transport database fields from one database to another?

If you have the following scenario:

On DBServer1, Database db1 contains MyISAM tables only
On DBServer2, Database db2 is the target database
/var/lib/mysql is datadir for both DB Servers
10.2.3.40 is the IP address of DBServer1

You could copy the MyISAM tables straight to the target database

From DBServer2, run these Linux commands

cd /var/lib/mysql/db2
scp IPADDRofServer1:/var/lib/mysql/db1/*.frm /var/lib/mysql/db2/.
scp IPADDRofServer1:/var/lib/mysql/db1/*.MYD /var/lib/mysql/db2/.
scp IPADDRofServer1:/var/lib/mysql/db1/*.MYI /var/lib/mysql/db2/.
chown -R mysql:mysql /var/lib/mysql/db2/.

That's it. No SQL Processing of any kind. The information_schema database on DBServer2 will autodetect the presence of new MyISAM and incorporate them accordingly.

Give it a Try !!!

CAVEAT : THIS DOES NOT WORK InnoDB TABLES AT ALL !!!


Selecting ALL records when condition is met for ALL records only

Something like this would probably be the most conventional approach:
SELECT
  Name,
  Course,
  Grade
FROM
  myTable a
WHERE
  NOT EXISTS
    (
      SELECT
        1
      FROM
        myTable b
      WHERE
        b.name = a.name
          AND b.grade <> 'NA'
    )

Somewhat less conventional but potentially more efficient:
SELECT
 Name,
 Course,
 Grade
FROM
  (
    SELECT
      Name,
      Course,
      Grade,
      SUM
        (
          CASE
            WHEN Grade <> 'NA' THEN 1
            ELSE 0
          END
        )
        OVER
          (
            PARTITION BY
              Name
          ) AS num_not_na
    FROM
      myTable
  ) a
WHERE
  a.num_not_na = 0


Can I implement clustering with just 2 servers and one NAS?

Consider using mirroring, not clustering?

MSDN difference overview, a summary:

mirroring is per database vs per instance
mirroring is 2 instances with own files, clustering is one set of files owned by active node
clustering has stricter hardware requirements


Getting MySQL User Variable in Sequence, While Using ORDER BY RAND()

You need to get your random row-set first and then number the rows.

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE tbl
    (`ID` int)
;

INSERT INTO tbl
    (`ID`)
VALUES
    (1),
    (2),
    (3),
    (4),
    (5),
    (6),
    (7),
    (8),
    (9),
   (10),
   (11),
   (12),
   (13),
   (14),
   (15),
   (16),
   (17),
   (18),
   (19),
   (20)
;

Use LIMIT and ORDER BY RAND() together to get your random row-set:

Query 1:

SELECT *
  FROM tbl
 ORDER BY RAND()
 LIMIT 5;

Results:

| ID |
|----|
| 18 |
| 10 |
| 15 |
| 16 |
|  2 |

Wrap that into an outer query to apply the row numbering:

Query 2:

SELECT ID,@counter:=@counter+1 as counter
FROM (
  SELECT *
    FROM tbl
   ORDER BY RAND()
   LIMIT 5
  )t
INNER JOIN (SELECT @counter:=-1) b;

Results:

| ID | COUNTER |
|----|---------|
| 19 |       0 |
| 10 |       1 |
|  1 |       2 |
|  7 |       3 |
| 17 |       4 |

Running the same query again to show its really random:

Query 3:

SELECT ID,@counter:=@counter+1 as counter
FROM (
  SELECT *
    FROM tbl
   ORDER BY RAND()
   LIMIT 5
  )t
INNER JOIN (SELECT @counter:=-1) b;

Results:

| ID | COUNTER |
|----|---------|
|  4 |       0 |
|  8 |       1 |
| 18 |       2 |
| 14 |       3 |
| 16 |       4 |


The usual mistake is to not include (6) on the default:

DEFAULT CURRENT_TIMESTAMP(6)

See about 3/4 of the way down on https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html .

Also, consider that to set this to:  https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp


It's not possible to save query plans "for all future sessions". Query plans are only ever saved for the current session. And only reused under a number of favorable conditions.

Plans for ad-hoc SQL queries are not saved at all. All queries inside PL/pgSQL functions are treated like prepared statements. And there are more steps than just the query planning.

It's important to note that there have been substantial changes since Postgres 9.1, where generic plans were more generally saved and reused for statements inside PL/pgSQL. This has become a lot smarter with pg 9.2.

The manual (bold emphasis mine):

  Prepared statements only last for the duration of the current database session.

That's not saying anything about query plans, yet:

  When the PREPARE statement is executed, the specified statement is
  parsed, analyzed, and rewritten. When an EXECUTE command is
  subsequently issued, the prepared statement is planned and executed.

And further down:

  If a prepared statement is executed enough times, the server may
  eventually decide to save and re-use a generic plan rather than
  re-planning each time. This will occur immediately if the prepared
  statement has no parameters; otherwise it occurs only if the generic
  plan appears to be not much more expensive than a plan that depends on
  specific parameter values. Typically, a generic plan will be selected
  only if the query's performance is estimated to be fairly insensitive
  to the specific parameter values supplied.

How to tell whether a generic plan is used?

  To examine the query plan PostgreSQL is using for a prepared
  statement, use EXPLAIN. If a generic plan is in use, it will contain
  parameter symbols $n, while a custom plan will have the current actual
  parameter values substituted into it.

A saved plan is always a generic plan, but not vice versa.
Where are prepared statements saved?

  You can see all prepared statements available in the session by
  querying the pg_prepared_statements system view.

Query plans for SQL statements in PL/pgSQL

Those are not normally visible in EXPLAIN output. You can install the additional module auto-explain to write those plans to the log, too. Details here:

Postgres query plan of a UDF invocation written in pgpsql

For details about Plan Caching in PL/pgSQL functions, best read the manual directly.


Define calculated column of BOOLEAN/TINYINT(1) type in a View?

This is currently impossible.

MySQL doesn't have a real BOOLEAN type, (or a real array type.. or a real JSON type). It has an alias for TINYINT.
Any condition returns an integer. This is the fastest datatype for a CPU, and presumably this implementation detail is reflected here. For instance, 'true' IS TRUE and 1=1 both return 1 as an int.
CAST does not provide a TINYINT format.

I would highly suggest migrating to PostgreSQL. It's so much less terrifying... and liberating.


As at MongoDB 4.0, there isn't built-in support for partitioned collections but you can implement a similar pattern:

Your application would always write new entries to collection M0 (current)
You would set up a scheduled task to rotate collection names at the end of the month using renameCollection (renaming M1 to M2 and M0 to M1)
Your scheduled task would download the data from M2 and drop this collection when no longer needed

This approach should minimise storage fragmentation since your M0 collection is recreated every month rather than being long-lived, however it would limit your ability to easily run simple queries against data spanning both M0 and M1.

  I have read about Capped Collections and TTL setting. doesn't seem to fit the bill. There is ranged sharding too; but, we aren't such a huge installation requiring a clustered deployment.

Capped collections have a number of limitations and aren't appropriate for this use case. Capped collection growth is limited by size in bytes or number of documents, but you cannot control document expiry time. You also cannot directly delete documents in a capped collection as they are designed for FIFO (First In, First Out) use cases.

A TTL (Time-To-Live) index is a definite candidate for your use case. Documents have a TTL expiry date based on a number of seconds or a specific clock time. You could use the first type of expiry to smooth out deletions over time (so documents expire after ~62 days) or the second type of expiry to set a stricter date based on the first day of the month when the documents would normally be dropped as part of M2.

Sharding adds a lot of operational complexity if your only use case is removal of archival data. The relevant feature to consider is zone-based sharding which would allow you to associate contiguous ranges of a shard key with specific shards. This is more typical for an archival use case where older data should remain online but move to lower cost/performance storage. For more information see Tiered hardware for varying SLA or SLO in the MongoDB documentation.


Without looking in detail to your configuration, I would bet that your problem is the kernel doing an Out of memory (OOM) kill: killing some of your ndbd, etc. processes. Those are usually logged to /var/log/messages, so you can confirm it easily.

  128MB of ram, 32-bits

I am not 100% sure if you can run an NDB cluster with those spects, but I can tell you that, in practice, you do not want. Here it is a guide on how much memory consumption you can more or less expect from NDB. Remember that NDB preallocates most of its memory at first execution, ant that it is a memory-focused transactional engine. Increase its available ram and/or reduce some of its configuration parameters.


generate new column from another table column, treating nulls are unique

Adding the column:

alter table device_login
  add column person_id int ;

The query is a bit complicated, due to the nulls. First group the not null logins per login and keep each null seprately

with a as
( select min(device_id) as device_id, login_id
  from device_login
  where login_id is not null
  group by login_id
union all
  select device_id, login_id
  from device_login
  where login_id is null
)

then use dense_rank() to assign separate numbers to each group

, b as
( select dl.device_id, a.login_id,
         dense_rank() over (order by a.device_id, a.login_id) as rnk
  from a join device_login as dl
      on a.login_id = dl.login_id
      or a.login_id is null and a.device_id = dl.device_id
)

and finally update the table:

update device_login as dl
set person_id = b.rnk
from b
where b.login_id = dl.login_id
   or b.login_id is null and b.device_id = dl.device_id ;

Tested at SQLfiddle.


Checking if an Username is already in use

mysql_query() returns false only when there is something wrong with your query and it fails to execute. Otherwise, the return value is a resource which evaluates to success. That is your code always echoes "Account Exists". Check for the number of rows the SELECT statement fetched to determine whether the user exists or not.

Your code is also not secure. It is vulnerable to SQL injection. I would highly recommend NOT using mysql_* family of PHP functions. They are deprecated as of PHP 5.5.0 and will be removed in future releases. Consider switching over to mysqli_* family of functions or better use parameterized queries with PDO.

PDO:

$conn = new PDO('mysql:host='.$dbhost.';dbname='.$dbname,
                 $dbusername,
                 $dbpassword);

$username = $_POST["username"];
$password = $_POST["password"];

//Query statement with placeholder
$query = "SELECT Username
          FROM Userdata
          WHERE Userdata.Username = ?";

//Put the parameters in an array
$params = array($username);

//Execute it
try {
    $stmt = $conn->prepare($query);
    $result = $stmt->execute($params);
} catch(PDOException $ex) {
    echo $ex->getMessage());
}

//Now Check for the row count
if($stmt->rowCount === 1) {
    echo "Account Exists";
} else {
    echo "Account does not exist";
}


Rebooting the server has fixed the issue

After rebooting Server A  the correct amount of free space was showing on the affected databases on Server A.
e.g. for the example in the question the affected database that was showing 9% free on Server A and 75% free on Server Z is now also showing  75% free on Server A after rebooting Server A.

We have now shrunk the affected databases on Server A back to having a normal amount of free space and run the imports.
The databases have only grown by the amount of new data that has been added.
e.g. if 3GB deleted and 3.1GB inserted the database has grown by 0.1GB instead of 3.1GB.

For reference Server A had been rebooted 3 weeks before and we are now planning to upgrade to SQL Server 2016 in the next couple of months.


Clustered Index Scan on partitioned table even when there is covering index

Yes it is most likely a row goal issue.

SQL Server assesses that scanning the clustered index (to avoid a sort) will find the first ? matches quicker (at which point the scan would stop) than searching the nonclustered index for matches, sorting those, then returning the top ? matches.

On your version of SQL Server, you can test if setting a row goal is the cause by running the query with OPTION (QUERYTRACEON 4138) to disable row goals.

Related Q & A:

Why is my index not being used in a SELECT TOP?
How (and why) does TOP impact an execution plan?
Why is this query not using my nonclustered index, and how can I make it?


Verify if the client is connecting to the Oracle database using native network encryption

You're confused with my comments,with accepted on server side and required client side sqlplus connection is encrypted and Sql Developer with thin client  is unencrypted but the same Sql Developer with thick client connection is encrypted.(Toad is out of scope I never used at all).In other words accepted on server side is working as expected.
Let me demonstrate for you
 sqlnet.ora on server

    $ cat $TNS_ADMIN/sqlnet.ora
    # sqlnet.ora Network Configuration File: /u01/app/oracle/product/19/network/admin/sqlnet.ora
    # Generated by Oracle configuration tools.

    NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

    SQLNET.ENCRYPTION_SERVER = accepted

    SQLNET.ENCRYPTION_TYPES_SERVER= (AES256)

    SQLNET.CRYPTO_CHECKSUM_SERVER = required

    [CDB2] oracle@hol:~

   Client side Window 10 Pro

sqlnet.ora

# sqlnet.ora Network Configuration File: C:\app\oracle\product\19.3.0\db_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

#SQLNET.ENCRYPTION_CLIENT=REQUIRED   -- commented out
#SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256) -- commented out

    SQL> @network_encryption

NETWORK_SERVICE_BANNER
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
SHA1 Crypto-checksumming service adapter for Linux: Version 19.0.0.0.0 - Production
Crypto-checksumming service for Linux: Version 19.0.0.0.0 - Production
Encryption service for Linux: Version 19.0.0.0.0 - Production

Now I'll uncomment last two lines in sqlnet.ora client side
# sqlnet.ora Network Configuration File: C:\app\oracle\product\19.3.0\db_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SQLNET.ENCRYPTION_CLIENT=REQUIRED

SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)

ADR_BASE = C:\app\oracle\product\19.3.0\db_1\log

SQL> @network_encryption

NETWORK_SERVICE_BANNER
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
AES256 Encryption service adapter for Linux: Version 19.0.0.0.0 - Production
SHA1 Crypto-checksumming service adapter for Linux: Version 19.0.0.0.0 - Production
Crypto-checksumming service for Linux: Version 19.0.0.0.0 - Production
Encryption service for Linux: Version 19.0.0.0.0 - Production.

Now let's test with Sql Developer thin client

It's evident from above image it uses jdbc thin driver.Let's check the sql out put in Sql Developer and as expected there is no AES256  encryption

Now I will switch to thick client,check both images

Hope this clears up


How to avoid Msg 9514, Level 16, State 1, Line 3 error for linked server?

What is probably happening is that SQL Server wants to download the whole remote table to your tempdb before attempting to join to the local table, including the XML column.

Try something like this:

SELECT TA2.ID
FROM [DB].[dbo].[table1] TA2
INNER JOIN (
    SELECT col1, col2, col3, CAST(xmlCol AS varchar(max)) AS xmlCol
    FROM [linkedsrv].[DB].[dbo].[table1]
) AS PA
    ON (TA2.ID = PA.ID)

Or like this:

SELECT TA2.ID
FROM [DB].[dbo].[table1] TA2
INNER JOIN (
    SELECT *
    FROM OPENQUERY(linkedsrv, '
        SELECT col1, col2, col3, CAST(xmlCol AS varchar(max)) AS xmlCol
        FROM [DB].[dbo].[table1]
    ')
) AS PA
    ON (TA2.ID = PA.ID)


You have my sympathies.
This sounds like [yet another] amateur-hour set-up where the "Administrators" think they can treat an Oracle database server just like they do a file server full of Word documents.
The fact that their attempted "Recovery" was done at the disk level instead of reaching for their database backups tells me they that have very little idea what they're doing.
Ask them directly about their "database backups" and see how blankly they look at you.   Based on that, consider your "escape" options.
Moving forward:

Backup the database.  The Data matter, not which server they host it on.
Build a clean Windows server,
install Oracle database,
port the data across,
test everything!

their concern is about reinstalling Oracle ... took over the company ... don't know the license details.

"Danger, Will Robinson!"
Start pointing them, strenuously, in the direction of an Oracle Account Representative.
Running Oracle database without a support contract is bordering on sheer insanity.


general memory requirements for sql server 2008 r2

... was hoping I could get ... a rough rough estimate of what we should be running.

Without more information about your queries and data sizes, it's really difficult to give you any kind of estimate, let alone an accurate estimate.

  Database: sql server 2008 r2 enterprise database

  Windows: Windows 2008 r2 Enterprise 64 bit, pretty sure running on
  VMware.

  Processor: Intel(R) Xeon(R) CPU E7-4860 @ 2.27GHz 2.26 GHz (2
  processors)

  Installed memory: 4GB

Two processors (I'm assuming this is exposed in the VM as 2 cores) may or may not be under-provisioned. The cores assigned to a VM aren't necessarily mapped directly to physical cores (or even allowed to use 100% of a single core when it's needed!), so you may find this is a more flexible resource than memory. Without any more information about your workload or hardware/virtualization configuration, I would say increasing this to 4 would be nice-to-have.

Memory allocation. Oh boy. This is grossly under-provisioned for the workload. Windows itself needs a bare minimum of 2-3 GB to stay happy, and each of the 2 users running BIDS on the box will require at least 500 MB each. And with that, the box is maxed out already, and I didn't even start figuring out how much the database is going to need.

  Majority of users interact with database through asp.net website and Report server website.

You didn't say, but if these are running on the same box, memory requirements for them need to be taken into account as well.

  Finally, we have a fairly involved data warehousing operation that probably brings in 3 million records per day by way of SSIS packages that also run on the server.

Assuming this runs at night when there are no live users on the system, I don't see this as a problem unless it's taking too long to run. This part of things is the least of your worries; live users are more important.

  Our previous requests for additional memory has been denied with the common response that we need to perform more query optimizations.

As I demonstrated above, the current amount of memory that's been provisioned is completely inadequate. At the same time, though, at the other end of the spectrum, it's exceedingly unlikely you'll be able to get enough memory provisioned to be able to keep the entire database in memory at once.

Even though you got a blanket response like that (which, by the way, probably had more to do with how persuasive your justification for additional resources was, and not the actual resource usage itself), it's highly likely the efficiency of the database could be improved. Yet there's no amount of tuning alone that can fix the issues you're experiencing now; the suggestion of that is a complete non-starter to me.

I would take the overall approach that the amount of memory currently provisioned is below the minimum required (which should be corrected ASAP), and additional resources may be required to improve the user experience to a usable level while improvements are made to increase the efficiency of the systems.

Here are a few thoughts (in order of attack):

You will win if you can prove how much performance improves every time you get more resources provisioned. Keep track of performance metrics using Performance Monitor logging (note: the logging part is very important), including website response times if you can. Start doing this now, before doing anything else. When you do finally get to the minimum amount of memory (you aren't going to get 32 GB right away), suddenly you now have evidence that the added memory improved things... which means adding even more would probably help, too! If you don't collect a baseline on the current configuration, you're going to miss the boat when things are bumped up to the minimum recommended level.
Analyze your server's wait statistics. This will tell you what the biggest bottleneck in the system is. You'll probably have PAGEIOLATCH_XX as the most common/highest wait time, which indicates too much I/O is being done to fetch pages from disk. This can be alleviated by adding memory, so the physical I/O's become less frequent as the needed data is already in memory. While this analysis is pretty much a foregone conclusion, the fact you've gathered these stats at all gives you more ammo when justifying the need for resources.
As I mentioned above, the bare minimum requirement for memory is not being met. Collect the set of recommended hardware requirements for all the software you're running, and maybe also grab screenshots of Task Manager. This alone should be enough to justify at least 4-8 GB more, on the spot. If they still refuse, try to convince them to allow you to try it out for a week, and give it back after that (you're collecting performance stats, so you won't need to give it back because mid-week you'll be able to prove how much it's improved the situation). If they still refuse, you're being set up to fail; URLT.
If you can offload some of the workload (in particular, avoid remoting in if at all possible), this will increase the amount of memory available for the database, which is more critical.
You won't be able to fit the entire database in memory at once, which means you need to set SQL Server's max memory setting very carefully to prevent memory over-commit, which kills performance like nothing else. Over-commit is actually even worse than simply not being able to fit all the data in memory. It's highly likely you're in this scenario right now simply because there's just no memory available at all, and it's probable that the max memory setting is set to the default (unlimited).
Since you're running SQL Server Enterprise Edition, and memory is at a premium, I would strongly consider implementing data compression. This will trade off an increase in CPU usage for space-savings of memory (and hence reduced disk accesses, which are comparatively very slow).
Tune the database. It's likely the structures and queries could use improvements as far as indexing and access patterns go. Also, if a lot of data is being frequently scanned and aggregated, creating indexed views, summary tables, or precomputed reports may be very helpful.
This might be a longshot because it probably means more hardware provisioning, but implement a caching solution. The fastest query is the one you never make.

Those are just a few ideas. The bottom line is that tuning alone will not solve the problems here, nor will hardware alone, even though the latter probably will alleviate the majority of the immediate issues. That's really how it goes: throw hardware at the problem in the short-term to put out the fire, and throw tuning at the problem in the long-term to fix the root cause as best you can.


MYSQL Select data from two different tables BUT represent the data in multiple rows

SELECT id, First_Name, Last_Name, 'Email' Name, Email Value
    FROM table1
UNION ALL /* or UNION DISTINCT */
    SELECT tb1.id, tb1.First_Name, tb1.Last_Name, tb2.Name, tb2.Value,
    FROM  table1 tb1
    LEFT JOIN table2 tb2
        ON tb2.Name LIKE '%email%'
       AND tb2.Value LIKE '%@%'
       AND tb2.client_id = tb1.id
/* ORDER BY 1 */

PS. AND tb2.value != '' condition is excess. If false, then AND tb2.Value LIKE '%@%' is false too.


You've either got a slow running query or blocking/deadlocking going on.  To see blocking, you could run this query:

CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
      Login  VARCHAR(255),HostName  VARCHAR(255),
      BlkBy  VARCHAR(255),DBName  VARCHAR(255),
      Command VARCHAR(255),CPUTime INT,
      DiskIO INT,LastBatch VARCHAR(255),
      ProgramName VARCHAR(255),SPID2 INT,
      REQUESTID INT)
INSERT INTO #sp_who2 EXEC sp_who2
SELECT      *
FROM        #sp_who2
-- Add any filtering of the results here :
WHERE       DBName <> 'master'
-- Add any sorting of the results here :
ORDER BY    DBName ASC

DROP TABLE #sp_who2

Look for anything in the BlkBy column and you can see what session is blocking your queries.

You didn't mention a deadlock error, which you should see in SSRS logs.  I'd take a close look at those to see the exact error and proceed with that knowledge.  It's down in the log dir of the SSRS install.

SSRS will have timeouts set for it, and you may be hitting those times, but your SSRS log should tell you if it's a application timeout or a deadlock.


In principle, 2-3TB should be possible to do without resorting to a shared-nothing architecture, but Vanilla PostgreSQL still does not have good parallel query functionality.  All you will achieve by replication is the ability to apportion queries out to individual nodes.  I don't believe that PostgreSQL supports federated queries out of the box and I would be very surprised to find that Tableau has direct support for sharding at the client.  I'd guess that PostgreSQL won't perform very well on a data set this big.

There is work in progress on a parallel query facility for PostgreSQL, but AFAIK it's not included in 9.1.  My gut instinct is that some release in the next few years will include this feature but it's not quite there yet.  I don't see evidence of much effort being put into a federated query facility.

Another option: SQL Server

Unless you're married to Postgres you might find that SQL Server provides a cost-effective option to Greenplum for a 2-3TB data set.  It is licenced by the socket rather than the core, so a loaded 2-4 socket Xeon or Opteron box gives good bang for buck as a platform.  For a smaller user base I believe you can still licence Enterprise Edition by CAL as well.

A couple of 24/25 disk arrays on a high-end RAID controller will do sequential reads fast enough to saturate a PCIe-x8 slot (2GB/sec).  A simple table scan query with SQL Server will handle data at this rate without using much CPU (obviously depending on the actual computation), so you have some headroom to add controllers and arrays if you want faster I/O.

SQL Server also comes with a passably good set of B.I. tooling, including an OLAP server.  Tableau is not cheap, around £1,800/seat last time I looked. Depending on the number of users you may find that the 'good-enough' tooling that comes with SQL Server could offset the costs of the DB licence anyway.  Most third party reporting tools also play nicely with SQL Server.

Disclaimer: I'm not any sort of die-hard Microsoftie but I have done a lot of B.I. work with SQL Server and Oracle over the past decade or so.  SQL Server is actually quite a good B.I. platform.


How to restrict column creation for existing tables?

The way to prevent table modification is to change the ownership to a different role.  Tables can be altered only by the owner and superusers - so change the ownership to a non-superuser.  I find it practical to keep a database ownership role for solely this reason (which might make life easier elsewhere, too, like for example when setting default privileges).