How to set up triggers in MySQL for copying a field I have an in-house application that I've put together, and I'm VERY new to SQL in general.

I have a table that contains most of our data. Let's pretend it looks like this.

Matrix

|Name       | Description        |
----------------------------------
|Server01   | First Server       |

Now I've added another table and let's pretend it looks like this.

OtherInfo
|Name       | Price              |
----------------------------------
|Server01   | 100                |

I've done an initial copy of data from Matrix to OtherInfo. People will be adding entries to Matrix, and I want the Name from the Matrix to go into the OtherInfo table.

Basically, the Name field should always replicate what is in Matrix. If something gets changed or deleted on Matrix, the same thing should happen on OtherInfo.

How do I set this up?

Create the following four(4) Triggers on the Matrix table

DELIMITER $$
CREATE TRIGGER matrix_ai AFTER INSERT ON Matrix FOR EACH ROW
BEGIN
    INSERT IGNORE INTO OtherInfo (Name) VALUES (NEW.Name);
END; $$
CREATE TRIGGER matrix_bu BEFORE UPDATE ON Matrix FOR EACH ROW
BEGIN
    DECLARE found_key,dummy INT;
    SELECT COUNT(1) INTO found_key FROM Matrix WHERE Name = NEW.Name;
    IF found_key = 1 THEN
        -- Abandon the Trigger of Name already exists
        SELECT data_length INTO dummy FROM information_schema.tables;
    END IF;
END; $$
CREATE TRIGGER matrix_au AFTER UPDATE ON Matrix FOR EACH ROW
BEGIN
    UPDATE OtherInfo SET Name=NEW.Name WHERE Name=OLD.Name;
END; $$
CREATE TRIGGER matrix_ad AFTER DELETE ON Matrix FOR EACH ROW
BEGIN
    DELETE FROM OtherInfo WHERE Name=OLD.Name;
END; $$
DELIMITER ;

What does each trigger do?

matrix_ai INSERTs the new Name into OtherInfo after it is inserted into Matrix
matrix_bu performs a validation to make sure you do not change the old Name into a New Name that already exists in the Matrix table
matrix_au UPDATEs the old Name in OtherInfo to the new Name you updated in Matrix
matrix_ad DELETEs the Name from OtherInfo that you just deleted from Matrix


Load into memory-optimized table gives insufficient memory error I am trying to load a memory-optimized table with rows from a disk-based table. It is just a simple INSERT..SELECT since the table structures are almost the same.

The database is on a dedicated server (not a VM) with 512 Gb RAM. However, I keep on hitting the following error:

  Msg 701, Level 17, State 103, Line 10

  There is insufficient system memory in resource pool 'default' to run this query.

The table has about 66 million rows and about 50 columns. Max server memory is set to the default 2147483647 Mb. Max memory % on the resource pool is set to 100%. The server is running SQL server 2016 SP1.

Things that I've tried so far, with no success:

• Enclosed the INSERT..SELECT in a loop so that it will only insert 10,000 rows at a time. It will run for some iterations and then error out. The number of iterations varies a lot at each run -- it could error out after 10,000 one time, and then go for a million the next.

• Created a dedicated resource pool for the database, instead of using the default (read this somewhere). Same problem.

• Tried using SQL server 2017 RC2. Same problem.

Any help would be greatly appreciated.

Added:

The INSERT..SELECT looks like the following:

INSERT INTO apc.Policy
    (PolicyID, ReportingCode, CarrierCode, -- and some more columns
    ProcessDate, LastUpdateDate)
SELECT
    PolicyID, ReportingCode, CarrierCode, -- and some more columns
    ProcessDate, GETDATE()
FROM Policy (NOLOCK)
WHERE PolicyID BETWEEN @Index * 10000 + 1 AND (@Index + 1) * 10000 -- @Index is the loop index
ORDER BY PolicyID

(This was originally posted in StackOverflow. Closed there. Opened here instead.)

Setting max memory to 2147483647MB is a really bad idea, as it could/would destabilize the operating system.

You've posted your SQL server is the following version:

  Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30

I would strongly recommend patching this server to CU4, because they contain many bug fixes that are specific to In-Memory.

If you are still having problems:

Is compression used for the disk-based table?
After creating a resource pool, and binding the database to it, did you OFFLINE/ONLINE the database to make the binding effective?


Select everything besides the Max(date) for each group I have a table where I would like to pull everything besides the max dates for each groupID.
Is there an easy way to do it?
here is what i am trying to do:
SELECT
groupId,
Types,
MAX(Dates) as date

FROM TableData

Where Dates < Max(dates)

GROUP BY
groupId,
Types

//The table looks as follows:
ID      |   GroupID | Date
1       |     A     | 10-10-2020 -> don't show
2       |     A     | 09-10-2020
3       |     A     | 08-10-2020
4       |     B     | 10-10-2020 -> don't show
5       |     B     | 09-10-2020
6       |     B     | 08-10-2020

//Expected result:
GroupID | Date
  A     | 09-10-2020
  A     | 08-10-2020
  B     | 09-10-2020
  B     | 08-10-2020

This is a classic situation where the use of window functions can come to the rescue!
Something like:
SELECT * FROM
(
  SELECT
    group_id,
    dates,
    ROW_NUMBER () OVER (PARTITION BY group_id
                        ORDER BY dates DESC) AS rn
  FROM table_data
) AS t
WHERE rn != 1
ORDER BY group_id, dates DESC;

Can I urge you not to use a keyword as an alias - use something like my_date or the_date instead - it helps readability and is less prone to problems when debugging!
Also, please include your SQL Server version in your questions.
Finally, you could make things a lot easier for those trying to help you if you provided a fiddle with your tables and data - this allows those answering to test their replies and helps eliminate duplication of effort (include the fiddle data in the question also).


SQL Server 2008 R2 on production -- is it worth to upgrade to SP1/SP2 One of my company's mission-critical database is running SQL Server 2008 R2. SELECT serverproperty('ProductVersion') returns 10.50.1600.1. So, yeah, RTM.

Now, long story short, this database is not yet migrated to our SAN Storage, so we can't have easy snapshot of the system. If we want to make a reversible change, we'll have to backup the database's instance willy-nilly. And of course, this might need some downtime/service interruption, and is thus a hassle.

Currently there seems to be no significant problems, except some slowdowns which we attribute to suboptimal queries. And our Dev/DBA sorely misses OPTION (RECOMPILE), which Microsoft took out from SQL Server 2008 R2 RTM.

So, I would like to know:

Upgrading the server to SP1 or SP2: will it be worth the hassle?
I have been searching for something clear-cut on the benefits of SP1 over RTM, or SP2 over SP1 over RTM.
Should we decide to apply SP1/SP2, will there be critical hotfixes to apply afterwards?
(E.g., something similar to the regression with SQL Server 2012 SP2 could lead to data loss).

Thanks for any help/explanation.

1.Upgrading the server to SP1 or SP2: will it be worth the hassle?
  I have been searching for something clear-cut on the benefits of SP1 over RTM, or SP2 over SP1 over RTM

Yes very much and believe me its not a hassle it will save you from lot of hassles. As already pointed you are running on unsupported version of SQL Server. Let me tell you personal experience, suppose you face any issue which comes eventually as a MS bug and you plan to ask MS to pay you for your loss you cannot because you are running unsupported version. The legal agrrements are little more complicated and I will not talk about it. Also even if you raise a case with MS for some support related activities MS engineere will straight away say its unsupported version first please apply SP2, YES not even SP1 SP1 is also not supported, then only they will proceed further, after you have applied SP2.

There was a critical bug fixed in SQL Server 2012 SP1.

Memory leak for SQLOPTIMIZER
Unexpected start for SQL Server.

List of bugs fixed in SQL Server 2008 R2 Sp1

List of bugs fixed in SQL Server 2008 R2 SP2

I guess above two reasons and links are enough. Are you waiting to face the issue and then apply SP2 ? Please dont. Also the option recompile bug was fixed in later CU and eventually in SP1.

  2.Should we decide to apply SP1/SP2, will there be critical hotfixes to apply afterwards?

As already pointed SQL Server 2008 R2 SP2 is not fully supported. As a general rule after launch of new service pack within one year previous SP becomes unsupported. Following is difference between mainstarem support and extended support taken from here

Think of Mainstream support as “normal”. In other words, mainstream support means Microsoft supports a product with its full offerings including paid incident support, hotfix support, security updates, etc.

When a product enters the Extended Support “phase”, the game changes:

1) We still provide security updates at no charge to all customers

2) You can still call CSS or create a case online per the normal support offerings (pay per incident, Premier, etc)

But…you cannot obtain a non-security hotfix from Microsoft for no charge. In order to obtain a non-security hotfix, you must purchase an Extended Hotfix Support Agreement. Extended Hotfix Support Agreements are available for Premier customers. Contact your Technical Account Manager for more information.

The extended support might involves more cost than normal support and is not available to everyone. If your firm has premier support tie up with MS then you can get extended support eaisly otherwise its costly and bit difficult. This is as per my region MS support/licensing is complex and you should speak to licesning expert to get more information about extended support.

MS says that CU should be only applied if user is facing bug which is fixed in that CU. If not Microsoft recommends not to apply it. SP's are more throughly tested and more reliable as compared to CU. So I would also say unless you face issue dont go for CU.If you want help in deploying SP please refer to This Link(its for SS 2012 but process/steps remains same)


How to truncate all tables with a specific name in all databases I want to delete all tables named error_log in all of my databases that have such a table.

truncate table error_log

I am using SQL Server 2000.

In SQL Server 2000 you can do this using a cursor against sysdatabases and building a check against each db's sysobjects table for the error_log object. If it exists, truncate. Rinse & repeat.

DECLARE @sql NVARCHAR(4000), @db SYSNAME;

DECLARE c CURSOR
  LOCAL READ_ONLY FORWARD_ONLY STATIC
  FOR SELECT name FROM sysdatabases WHERE dbid > 4
  AND CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'Status')) = 'ONLINE';

OPEN c;

FETCH NEXT FROM c INTO @db;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = N'IF EXISTS (SELECT 1 FROM ' + QUOTENAME(@db) +'.dbo.sysobjects
    WHERE name = ''error_log'' AND xtype=''U'')
    TRUNCATE TABLE ' + QUOTENAME(@db) + '.dbo.error_log;';

  EXEC sp_executesql @sql;

  FETCH NEXT FROM c INTO @db;
END

CLOSE c; DEALLOCATE c;

In SQL Server 2005 and up, you can do this without the explicit cursor (and using the more modern catalog views), but in 2000 this method would not be safe - depending on how many databases you have you would risk exceeding 4000 characters, and you can't use a MAX type in SQL Server 2000 (or declare TEXT/NTEXT as a local variable):

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + N'
  IF EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
  +'.sys.tables WHERE name = ''error_log'')
      TRUNCATE TABLE ' + QUOTENAME(name) + '.dbo.error_log;'
  FROM sys.databases
  WHERE database_id > 4
  AND state_desc = 'ONLINE';

EXEC sp_executesql @sql;


Purging Binary Logs from Master in a Replication Environment Replication Set up

6 Masters and their respective Slave.
All 6 slaves writing to a Master - which has its own Slave.

Now, I want to purge binary logs on the 6 original masters and set expiry to 15 days. Currently, those servers are holding binlogs for over 6 months.

If I just go and purge binary logs on the 6 Masters, can it be a problem for the Slaves?

Replication Topology

M Con 1 is the slave replicating from all 6 slaves (which are acting as masters).

It's ok to purge binlog on M 01 ~ M 06 as long as S 01 ~ S 06 has relayed all the binlog you want to purge, but remember to purge slowly, for example each time purge 20 binlogs (depends on your single binlog file size and the server load)


How to install tds_fdw on AWS RDS for SQL Server I am using AWS RDS Posgres DB. I would like to connect Postgres to my SQL Server 2019, so that I can have sql server tables available in foreign table drop down of postgres for querying. As I know, tds_fdw data wrapper is required. When I tried to create tds_fdw extension through super user account in AWS RDS, I got the following error:
ERROR:  Extension "tds_fdw" is not supported by Amazon RDS
DETAIL:  Installing the extension "tds_fdw" failed, because it is not on the list of extensions supported by Amazon RDS.
HINT:  Amazon RDS allows users with rds_superuser role to install supported extensions. See: SHOW rds.extensions;
SQL state: 22023
Is there any way to install tds_fdw on AWS RDS? If not then, what other ways I can use to connect AWS RDS Postgres to my SQL Server?

If RDS doesn't allow it, then you can't do it on RDS.  The straightforward solution is not to use RDS if it doesn't support the features you need.
But RDS does allow postgres_fdw.  So the convoluted solution is to run your own postgres server (perhaps running it on an EC2 instance) which does supports tds_fdw, and then connect to that from RDS and chain the foreign tables.


MySQL: SELECT the n-th item from an ordered result SELECTing the top n elements from a table is easy:

SELECT id FROM pixels WHERE pixel_id='some_pixel_id' ORDER BY id DESC LIMIT 1000;

Finding the n-th element with a subquery is quite straightforward:

SELECT MIN(id) FROM
    (SELECT id FROM pixels WHERE pixel_id='some_pixel_id' ORDER BY id DESC LIMIT 1000) s;

Assuming that there is a BTREE index on the relevant columns (id), is this approach efficient? Are there any better alternatives?

SELECT id FROM pixels WHERE pixel_id='some_pixel_id' ORDER BY id LIMIT n, 1


Are UDF used by a stored procedure optimised/merged in the SP itself? The issue has sent me crazy for a few days, I may have found my answer, but I need a confirmation and to check for an eventual easy solution.

When a stored procedure uses some user defined functions, are the UDF plans merged in the main plan and everything get optimised or are they called directly every time?

And suppose it doesn't happen, is there a way to force SQL Server to do it for a specific SP?

If I understand the question correctly, it should depend on the type of function. Inline Table-Valued Functions have their definitions substituted into referencing queries, just like what happens with views. This is what is meant by "inline", and why they are not true modules that can be signed, among other differences with the other function types.

Multi-statement Table-Valued Functions and Scalar User-Defined Functions are completely independent modules that are executed separately and have their own plans.

This behavior should have nothing to do with them being referenced in a stored procedure or in an ad hoc query.

Now, whether or not UDFs are called every time, that depends on the type of UDF:

T-SQL UDFs are executed per row and prevent parallel plans
SQLCLR UDFs, if they are marked as IsDeterministic=true; (is false by default), and are not doing any data access, do not prevent parallel plans and should be able to have their return values cached (per query execution, I assume).

All of this is why, whenever possible, it is best to convert T-SQL UDFs into T-SQL Inline TVFs.


Interpreting Page Life Expectancy metric I need advise on interpreting PLE metric of my server which:

is SQL Server 2008 R2
with 4 NUMA nodes
with 128 GB RAM

Daily full backups start at 02:00 AM and takes around 1 hour to complete.

Various metrics for the past 24 hours is as follows:

My questions would be:

What should alert threshold for PLE be?
What might the dips and peaks on the graph indicate?

Thank you

There's no simple figure for thresholds. This is the same as most SQL Server counters. Your system is busy though.

The peaks and such will likely be large queries.

To dig in deeeper you need to understand: interpret PLE as "I'm reading my buffer memory from disk every X seconds, does that sound like a lot?" It's not entirely accurate (buffer < total memory size) but it's a good rule of thumb.

Is 128GB per 23 minutes too much? That's 93MB/s of reads which is a lot to sustain for a busy SAN. But if you had direct attached storage then maybe it isn't.

How low is too low? If your PLE is 0 you're reading faster than the memory can hold. If it's 60 you're reading 128GB a minute. Again that sounds like a lot right. Not much hardware could sustain that.

The thumb guide is 300. Except on your system 300 would mean 25GB of reads per minute or 400MB/s. Also too much except for directly attached SSDs.

Your ultimate threshold is: where users experience pain. Which is likely somewhere near the limits of your sustained storage speed (again remembering SQL is doing more than just reads).

Nobody looks at the threshold for during backups because databases are so large they often exceed memory and need to cycle through the buffer numerous times. Again it's not important unless users are experiencing pain due to it. (And if they were you would increase memory or throttle down backup speed).

How do monitoring applications determine a threshold? They're usually stupid and just pick 300. Or 1000. Some sample at different times of the day and apply a fudge factor and compare against past results - and that gets super complicated if you do backups on a weekly or monthly schedule; after all which past baseline should they compare against?


Cannot connect from computer to server database I'm trying to connect from my computer to a server which has a SQL Server 2008 instance installed. My computer and the server are in the same domain and the name of the server is "SERVER". The instance name is "SZSQL". If I try to connect on the server in SQL Management Studio 2008 I'm able to login using instance SERVER\SZSQL, user sa and the admin password. When I try using these login data in MS Visual Studio 2012 Server-Explorer, I get an error (translation below):

In English:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified).

Like I told you before, the instance name, username and password are correct when I try to login on the server itself. Just the connection from my computer to the server is not working.

Any suggestions? :)

EDIT: The Server is running on Windows Server 2008 Standard

Have you turned on TCP/IP access in the SQL configuration manager?  It's off by default for security reasons and is the first thing you have to turn on for remote DB access.

You will also need to open port 1433 in the firewall and in the case of a non-default instance, port 1434 as well.  For non-default instances, the SQL Server Browser service must also be running.


How to recompile all stored procedures and table valued functions in SQL Server database? In a SQL Server database sp_recompile can be run on a stored procedure to update the execution plan.  I would like to run this on all stored procedures in a database.  Also, I would like to run its equivalent on all table-valued functions but I do not know which sys procedure to run.

Is it possible to do this without manually typing out a sp_recompile line for all of the stored procedure names in SQL Server Management Studio?  Likewise for the table valued functions?

I believe I need to do this because the VM SQL Server has had its memory significantly increased but I am only seeing a marginal increase in execution time. The execute plans show 80+ percent of the run time is on a clustered index seek so I don't think there is much more I can do to optimise the stored procedures.

You can run sp_recompile on everything by using a cursor to produce ad-hoc SQL for each and run it, if you think that will help:

DECLARE C CURSOR FOR (SELECT [name] FROM sys.objects WHERE [type] IN ('P', 'FN', 'IF'));
DECLARE @name SYSNAME;
OPEN C;
FETCH NEXT FROM C INTO @name;
WHILE @@FETCH_STATUS=0 BEGIN
    EXEC sp_recompile @name;
    FETCH NEXT FROM C INTO @name;
END;
CLOSE C;
DEALLOCATE C;

or you could produce ad-hoc SQL and run that via EXEC, takes less code which might be marginally more efficient:

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'EXEC sp_recompile '''+[name]+''''+CHAR(10) FROM sys.objects WHERE [type] IN ('P', 'FN', 'IF');
EXEC (@sql);

(though I find this form sometimes throws people due to looking set-based but building the string up iteratively, and not being a standard SQL pattern)

Another set of objects that might be a similar concern here is views. You can similarly mark them as needing to be reassessed to make sure stored plans and other meta-data is not stale with sp_refreshview, by small modifications to either the cursor or ad-hoc SQL methods shown above:

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'EXEC sp_refreshview '''+[name]+''''+CHAR(10) FROM sys.objects WHERE [type] IN ('V');
EXEC (@sql);

  The execute plans show 80+ percent of the run time is on a clustered index seek so I don't think there is much more I can do to optimise the stored procedures.

There is sometimes more to optimisation than preferring seeks over scans and so forth, sometimes an index scan is more efficient than many executions of seek operations, and the cost estimates upon which the percent figures you are looking at are calculated are that (estimates) at best (a useful guide but sometimes far from at all accurate).

While "throw more memory at it" can help some database performance issues, at least temporarily, if your bottlenecks are very CPU bound rather than memory and/or IO bound then adding more memory will have very little effect.


CTE vs Function/trigger performance in Postgresql I have a very long CTE query that I have shortened here for readability. But it tries to insert state, county, city, zipcode and street into their own tables and then insert their ID for inserting the address in the property table.

with tmpstate as (
insert
    into
        state ....on
    conflict ( lower(state_code) ) do
    update
    set
        state_code = excluded.state_code returning id,
        state_code ),
tmpcounty as (
insert
    into
        ..... on
    conflict ( lower(county),
    state_id ) do
    update
    set
        county = excluded.county returning id,
        county ),
tmpcity as (
insert
    into
      .....
    values ( 'Sarasota',
    (
    select
        id
    from
        tmpcounty
    limit 1 ),
    (
    select
        id
    from
        tmpstate
    limit 1 ) ) on
    conflict ( lower(city),
    county_id,
    state_id ) do
    update
    set
        city = excluded.city returning id,
        city ),
tmpzipcode as (
...... returning id,
        zipcode ),
tmpstreet as (
  .... ),
tmpproperty as(
insert
    into
        property (full_address,
        street_address,
        street_id,
        city_id,
        state_id,
        zipcode_id,
        county_id,
        property_details )
    values('ssss',
    'ssss',
    (
    select
        id
    from
        tmpstreet
    limit 1),
    (
    select
        id
    from
        tmpcity
    limit 1),
    (
    select
        id
    from
        tmpstate
    limit 1),
    (
    select
        id
    from
        tmpzipcode
    limit 1),
    (
    select
        id
    from
        tmpcounty
    limit 1),
    '{}') returning id)
select
    id
from
    property
limit 1

Would there be any performance gain if I create functions/triggers that insert the street, city, etc... and return their id if they exist instead of this long CTE query?

Running a big statement with its CTEs will be more efficient than running several small ones.

The only advantage of having a PL/pgSQL function for the individual inserts is that plans of statements are cached for the duration of the database session. But you can put your big statement into such a function if you want to make use of that.


Is the overhead of frequent query cache invalidation ever worth it? I'm currently working on a MySQL database where we are seeing a large number of invalidations from the query cache, primarily because of the high number of INSERT, DELETE and UPDATE statements that are being executed on many of the tables.

What I'm trying to determine is whether or not there is any benefit at all to allowing the query cache to be used for SELECT statements that are being run against these tables. Since they get invalidated so quickly, it seems to me the best thing would be to just use SQL_NO_CACHE on SELECT statements with these tables.

Is the overhead of frequent invalidation ever worth it?

Edit: At the request of the user @RolandoMySQLDBA below, here's the info on MyISAM and INNODB.

InnoDB

Data Size: 177.414 GB
Index Size: 114.792 GB
Table Size: 292.205 GB

MyISAM

Data Size: 379.762 GB
Index Size: 80.681 GB
Table Size: 460.443 GB

Additional info:

Version: 5.0.85
query_cache_limit: 1048576
query_cache_min_res_unit: 4096
query_cache_size: 104857600
query_cache_type: ON
query_cache_wlock_invalidate: OFF
innodb_buffer_pool_size: 8841592832
24GB of RAM

You should just disable the query cache with

[mysqld]
query_cache_size = 0

and then restart mysql. Why would I suggest that ???

The Query Cache will always butt heads with InnoDB. It would be nice if InnoDB's MVCC would let queries be served from the query cache if modifications do not affect repeatable reads for other transactions. Unfortunately, InnoDB just does not do that. Apparently, you have a lot of queries that get invalidated rather quickly and are probably not being reused.

For InnoDB under MySQL 4.0, the query cache was disabled for transactions. For MySQL 4.1+, InnoDB plays traffic cop when allowing access to the query cache on a per-table basis.

From the perspective of your question, I would say that the justification of removing the query cache is not so much the overhead, but how InnoDB manages it.

For more information on how InnoDB interacts with the query cache, please read pages 213-215 of the book "High Performance MySQL (Second Edition)".

If all or the majority of your data is MyISAM, you could go with your original idea of using SQL_NO_CACHE.

If you have a mix of InnoDB and MyISAM, you will have to find the right balance for your application based on on how high your cache misses are. In fact, pages 209-210 of the same book point out reasons for cache misses :

  The query is not cacheable, either because it contains a nondeterministic construct (such as CURRENT_DATE) or because its
  result set is too large to store.Both types of uncacheable queries
  increment the Qcache_not_cached status variable.
  The server has never seen the query before, so it never had the chance to cache its result.
  The query's result was previously cached, but the server removed it. This can happen because there wasn't enough memory to keep it, because
  someone instructed the server to remove it, or because it was
  invalidated

and the root causes of high cache misses with few uncacheable queries may be :

  The query cache is not warm yet. That is the server hasn't had a chance to fill the cache with result sets.
  The server is seeing queries it hasn't seen before. If you do not have a lot of repeated queries, this can happen even after the cache
  is warmed up.
  There are a lot of cache invalidations.

UPDATE 2012-09-06 10:10 EDT

Looking your latest updated info, you have query_cache_limit set to 1048576 (1M). This limits any result set to 1M. If you retrieve anything bigger, it will simply not be cached. While you have have query_cache_size set to 104857600 (100M), this only allows for 100 cached results set in a perfect world. If you perform hundreds of queries, fragmentation will come about rather quickly. You also have 4096 (4K) as the minimum size result set. Unfortunately, mysql has no internal mechanism for defragmenting the query cache.

If you must have the query cache and you have so much RAM, you could execute the following:

SET GLOBAL query_cache_size = 0;
SELECT SLEEP(60);
SET GLOBAL query_cache_size = 1024 * 1024 * 1024;

in order to purge the query cache. You lose all cached results, so run these lines during off-peak hours.

I would also assign the following:

query_cache_size = 1G
query_cache_limit = 8M

That leaves 23G of RAM. I would raise the following:

innodb_buffer_pool_size = 12G
key_buffer_size = 4G

That leaves 7G. This should be adequate for OS and DB Connections.

Keep in mind that the key buffer caches only MyISAM index pages, while the InnoDB Buffer Pool caches data and indexes.

One more recommendation: upgrade to MySQL 5.5 so you can configure InnoDB for multiple CPU and multiple threads for read/write I/O.

See my earlier posts on using MySQL 5.5 in conjunction with accessing multiple CPUs for InnoDB

Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Oct 05, 2011 : Query runs a long time in some newer MySQL versions
Sep 20, 2011 : Multi cores and MySQL Performance
Jun 19, 2011 : How do I properly perform a MySQL bake-off?

UPDATE 2012-09-06 14:56 EDT

My method for clearing the query cache is rather extreme because it hoses cached data and forms a completely different segment of RAM. As you did point out in your comment, FLUSH QUERY CACHE (as you suggested) or even RESET QUERY CACHE would be better. For clarification, when I said "no internal mechanism," I meant exactly that. Defragmentation is needed and has to be done manually. It would need to be crontab'd.

If you do DML (INSERTs, UPDATEs, DELETEs) on InnoDB more often than on MyISAM, I would say remove the query cache altogether, which I said in the beginning.


database connection and sessions in an example of social media I am referring to MS SQL connection and sessions. I know that each application should make a connection in order to access the database, and each connection can have multiple sessions. In case of a social media website where there are multiple users each with its own security credentials; does every user have to make a connection to all the databases that the user needs, and for different actions, like deleting a post or uploading a picture, make corresponding sessions? Or is this terminology for an application not a user?
I am new to this terminology and sorry for possible misunderstandings.

Thanks for your time.

Authentication and authorization for public site users is typically done in the application layer. Database access is then done by application services using service credentials for the database connection rather than end-user credentials. The application service serves requests for many end-user sessions (not to be confused with database connections/sessions). This sort of n-tier architecture greatly improves scalability.

In cases where MARS (multiple active result sets) is used, a single database connection may be used to interleave query execution. The same connection credentials are used for each session. Note that MARS is used for specialized data access needs. Typically, there is a one-to-one relationship of client application database connections and sessions.

Connection pooling also is very important for scalability. Pooling avoids the overhead of establishing physical network connection and authorization after the initial connection is made. When a query is executed on a reused pooled connection, the session is reset automatically without an additional round trip. This is why polling is used by default with many SQL Server Client APIs.


how to add trigger in mysql workbench, without UDF, using EER design I have designed a simple database using EER in workbench. However, i want to have a minimum value for the attribute salary (compared with min_value attribute from artist table). Here is the schema:

and here is the equivalent in the EER mysql workbench:

I need to check that the salary of each artist that acts on a movie, has a minimum value (lets say 1000), but how am i suppose to do that? I have checked in the options and all i see is:

-- Trigger DDL Statements
DELIMITER $$

USE `Askisi_3db`$$

Should i use something like:

CREATE TRIGGER min_salary
BEFORE INSERT OR UPDATE OF
salary, min_salary ON acts, artist
FOR EACH ROW
WHEN (new.salary < min_salary)
BEGIN
RAISE_APPLICATION_ERROR (-20004, ‘Violation of Minimum Actor Salary’);
END;
.
run;

The problem is that the attribute is in a N-M relation and has to check keys in other tables. Salary matters only for artists that participate as actors in a movie and not for directors or writers. However not every artist must have a min_salary (may be null for some who participate as directors or writers). Is this feasible only via UDF fucntions or front end design? I would really like a simple and tidy solution :)
Please don't give suggestions about how the schema could be better, all i ask is on this particular design how to implement the check between salary and min_salary

A trigger could be like this:

DELIMITER $$

CREATE TRIGGER
    acts_INSERT_min_salary
BEFORE INSERT ON
    acts
FOR EACH ROW
    BEGIN
        DECLARE msg VARCHAR(255);
        IF NEW.salary <
            ( SELECT min_salary
              FROM artist
              WHERE artist_id = NEW.artist_artist_id
            )
        THEN
            SET msg = 'Violation of Minimum Actor Salary.' ;
            SIGNAL SQLSTATE '45000' SET message_text = msg ;
        END IF ;
    END ;

$$
DELIMITER ;

You will need a similar trigger for the UPDATE operation on acts and another one for the UPDATE operation on artist table:

CREATE TRIGGER
    artist_UPDATE_min_salary
BEFORE UPDATE ON
    artist
FOR EACH ROW
    BEGIN
        DECLARE msg VARCHAR(255);
        IF NEW.min_salary >                             -- this part
            ( SELECT MIN(salary)                        -- is slightly
              FROM acts                                 -- different than
              WHERE artist_artist_id = NEW.artist_id    -- the other
            )
        THEN
            SET msg = 'Violation of Minimum Actor Salary.' ;
            SIGNAL SQLSTATE '45000' SET message_text = msg ;
        END IF ;
    END ;


dbatools start-dbamigration running very slowly I am trying to use the powershell module dbatools to migrate one SQL Server instance to a new instance on a VM. The new VM is on a different physical server than the source. I am not migrating the databases as I already have restored them on the new instance.

I am using the start-dbamigration task, but it seems to be running very slow. I started it last night at 20:00 hours, and as of writing this (the next day at 09:30), it is still running.

SQL Server version is 2014.
I am running dbatools from my personal PC and was wondering if that could have anything to do with the slowness?

The server has a lot of logins, and it seems that much time goes to granting CONNECT access to the different databases (takes around 40 - 50 seconds per login per database).(This task is called Update-SqlPermission in the log)

Anyone have experience with this tool and know if this is normal or not?
Would it be better to install dbatools on one of the servers?

You can look at the code for each Copy command used in the Start-DbaMigration command and try running them each individually (for the objects you want to migrate).

Overall the start command can take some time to run as it does a good bit of extra stuff in the command. Using each Copy command individually is the common method when you are not performing a full migration (including databases).

If you find any particular bugs or slowness you can get further assistance with issues by submitting an issue on our repository here.


Does SQL check each And condition if first condition fails? For a query of below kind does sql process data for each And condition if first And condition fails? If Emp_ID is not 12 for a row then does it check condition of Emp_Name?
So how SQL process this kind of queries?

SELECT [Emp_ID],[Emp_Name],[Emp_Sal] FROM [gyh].[dbo].[Employee_Demo] where Emp_ID='12' And Emp_Name like '%ab%'

What you're talking about is Short Circuit evaluation.

Unlike in CASE statements (except certain scenarios using variables and aggregate functions), where the order is important, you have little control over the order in which the WHERE clause in SQL Server is evaluated (except with some grouping paratheses). So, even if it does short-circuit, SQL Server may choose to evaluate the second clause first, especially if that is referenced as the first column in the index it chooses to use for the query.

SQL Server will still attempt to parse the entire query to make sure it is logically correct, regardless of whether the first AND condition fails.

For example, if I use the AdventureWorks database and look for a City that I know doesn't exist:

USE AdventureWorks;
GO

-- obviously don't use SELECT * in production
SELECT
   *
FROM Person.Address AS a
WHERE City = 'Bournemouth' AND 1/0 = 1

Even though City = 'Bournemouth' will always be FALSE, I still get the error:

Msg 8134, Level 16, State 1, Line 5
Divide by zero error encountered.


Select row with more of one type than another in a separate table I have two tables.

I'm attempting to select customers that have bought more of one type of product than another with at least one of the first type. Let's say for example that I want to get all customers that have purchased more apples than pears but have bought at least one apple.

So far I've come up with the following query:

select customers.id
from customers
where 'apple' in
(
  select type
  from products
  where products.customer_id = customers.id
)
and
(
  select count(p1.id)
  from products p1
  where p1.customer_id = customers.id
  and p1.type = 'apple'
)
>
(
  select count(p2.id)
  from products p2
  where p2.customer_id = customers.id
  and p2.type = 'pear'
)

This query works as is, however is slow, I understand that the subquerys are slowing down the query but haven't been able to find a solution involving joins that is faster for example.

SELECT c.id
      FROM customers as c
      JOIN products p1 ON (c.id = p1.customer_id)
      LEFT OUTER JOIN products p2 ON (
         c.id = p2.customer_id
         AND p2.type = 'pear'
         AND p1.id < p2.id
      )
      WHERE p1.type = 'apple'
      AND p2.id IS NULL

I want to get all customers that have purchased more apples than pears but have bought at least one apple.

SELECT c.id
FROM customers as c
JOIN products p ON c.id = p.customer_id
WHERE p.type IN ('apple', 'pear')
GROUP BY c.id
HAVING SUM(p.type='apple') > SUM(p.type='pear')


SELECT clausebased on other rows values How can I use a SELECT clause based on comparison between values of different rows? For instance, let Table 1 be:

ID   fieldA    fieldB    fieldC
1      A         X         2
2      A         Y         3
3      B         X         3
4      B         X         2
5      C         X         2

for each diffent value of fieldA, I want to get only the rows where combination of fieldC for fieldB=Y is greater then fieldC for fieldB=X. Note:There is a maximum of two rows for each value of fieldA.

ID   fieldA    fieldB    fieldC
1      A         X         2
2      A         Y         3
3      B         X         3
5      C         X         2

I was trying to use OVER clause, but I couldn't make it to work so far...

Thank you for your help!

-- Please try this query

create table #tablea (id int, fieldA char(1), fieldB char(1), fieldc int)
insert into #tablea values
(1,'A','X', 2)
,(2,'A','Y',3)
,(3,'B','X',3)
,(4,'B','X',2)
,(5,'C','X',2)

select t1.*
from #tablea t1
inner join (
    select fieldA, fieldB, max(fieldc) fieldc
    from #tablea
    group by fieldA, fieldB
)t2 on t1.fieldA = t2.fieldA
and t1.fieldB = t2.fieldB
and t1.fieldc = t2.fieldc
Order by Id


Make IN clause behave like AND clause I've asked the same question on stackoverlow, but got no answer, so that's why I'm asking it here (I've read that simple sql questions should be asked at SO, but I have no choice).

Suppose I have 3 tables: posts, post_categories and categories. I'm implementing some kind of page with filters where I can filter posts by categories. User can select multiple categories. When he selects more than one, they should be summed. I can't get it working. What I have now is simple IN() SQL clause:

SELECT posts.id, post_categories.category_id FROM posts
JOIN post_categories ON posts.id = post_categories.post_id
WHERE post_categories.category_id IN (1,2,3) LIMIT 10;

But it is not matching all ids, it is OR and I need AND. What I need here is to find all posts that have categories with id=1 AND id=2 AND id=3, but instead this query returns posts that have at least one category from the IN() list.

IN is like writing = ANY, e.g.

regress=> SELECT 1 = ANY (ARRAY[1, 2, 3]);
 ?column?
----------
 t
(1 row)

A simplistic interpretation of your question would be to say that you're asking for = ALL, but that rarely makes sense:

regress=> SELECT 1 = ALL (ARRAY[1, 2, 3]);
 ?column?
----------
 f
(1 row)

What you really want is to find all posts that have a corresponding post_categories entry for each of a listed set of category_id.

As is usual in relational databases, as soon as you clearly frame the question, the solution starts writing its self.

You could do a multiple join, like Daniel suggests, but that's a pain because it requires dynamic SQL. Or you could use relational set operations.

Instead, I'd possibly use PostgreSQL's array features. This would be easier if you'd provided sample data, but I think you want something like the following, which finds each category that matches the post then does a check to see if all the categories match (the HAVING clause):

SELECT
   posts.id
FROM posts
INNER JOIN post_categories ON posts.id = post_categories.post_id
WHERE post_categories.category_id IN (1,2,3)
GROUP BY posts.id
HAVING array_agg(post_categories.category_id) @> ARRAY[1,2,3]
LIMIT 10

Untested, since you didn't provide sample data in CREATE TABLE and INSERTs form, but that's the general idea. @> means "array-contains"; I suspect it'll be more efficient than using array_agg(category_id ORDER BY category_id) and an equality test against a sorted ARRAY literal, because you avoid the sort on aggregation.

You could leave out the WHERE clause and this would still work, but it might be quite slow, as it'd fail to filter out posts in which none of the categories matched before doing aggregation.

BTW, LIMIT without ORDER BY is usually a bug. The database can return any set of 10 matching results it feels like, in any order.


SSIS Not Executing any Script Task I have a simple SSIS package written in Visual Studio 2015 in a Project Deployment model solution. The package includes a Script Task with code written in C# and performs a very simple task. This script targets .NET Framework version 4.5.2.  I verified this version of .NET is installed on the SQL Server machine.

When I run the package in my local development environment it runs fine and does exactly what I need it to do.

I deployed it to the Integration Services Catalogs in the DEV SQL Instance and setup a SQL Server Agent Job to execute the package.  I executed the job but it failed in a step past the script task. The failure was due to the Script Task not doing it's job. A check of the SSIS Package execution messages showed the Script Task step completed with no errors.  I checked the event logs and found no errors there.  I double checked the C# code.  I modified the script task code to also generate an Information Message (Dts.Events.FireInformation) just to make sure the script task was actually firing.  I changed the SSIS logging in the job to Verbose as well.  I tried several other methods to validate the C# code was being called and executed. I turned antivirus off and re-ran with no change.

I’m convinced that the Script Task is just not executing the C# code in the Script Task.  I took the same basic c# script code and created a console app and successfully ran it on the server so I know the .NET code works on the server. I looked at the access permissions in Component Services and the permissions seem fine.  No errors are being thrown in SSIS or the event logs so it's been difficult to figure out what the problem is.

I’m not sure what else to check.  Does anyone know if what else I can check that might be preventing Script Tasks from being executed by SSIS or the SQL Agent?

--UPDATE--

So after spending over 3 days trying to figure this out I decided to approach this differently.

On a hunch I decided to install VS 2017 and SSDT 2017 to see if the upgraded development environment would make any difference. It works perfectly on the server after upgrading the project to 2017 and rebuilding the script task.

So apparently there was some sort of issue with my development environment or the versioning of the compiled code.

Live and learn I guess!

I spend the day dealing with the same problem in vain, my temporal solution would be to run the SSIS package with a CMD command.

here is my example:

dtexec /ISSERVER "\"\SSISDB\project_____name\Main_Package.dtsx\"" /SERVER server___name /X86 /Par "\"$Project::var___name\"";"\"value___value\""


Form builder schema for SQL database? There are many ways one can envision the solution, from the top of my head:

Two table approach:

Field table with (name, field_name, type, description, meta) attributes
Form table with (name, created_by) [name being foreign-key]

Code-generation approach:

New tables created on the fly

Example of what will come in from the user (e.g.: via an HTTP POST):

{
    "name": "dinner_time",
    "fields": [
        {"field_name": "hungry", "type": "boolean", "description":"", "meta": [""]]},
        {"field_name": "time", "type": "datetime", "description":"time now", "meta": [""]]}
    ],
    "created_by": "AT"
}

How should I enable this use-case, and what should I be extra wary about?

It's doable and quite safe approach is

Store form definitions in form_type table with JSONB and proper indexing. The table should have a marker telling if the form is already SQL-ized with create table
Write a asynchronous processing tool (cron worker) which will update table structure and issue proper CREATE TABLE requests, marking form_typeas deployed to SQL
form submit requests can be issued only after the form table is deployed
you can and should limit permissions / quota for this deployment task

Why async? I would avoid doing this in trigger due to locking issues under high load. Also, easy to DDOS so permissions should be separate for form insert and form create.


Loop over string literals as input to function PostgreSQL 9.4 I have a function called update_total which looks like this:

CREATE OR REPLACE FUNCTION update_total (table_name CHARACTER VARYING, year CHARACTER(4), donor_type CHARACTER VARYING, donor_code CHARACTER(5)) RETURNS VOID AS $$

DECLARE

table_name ALIAS FOR $1;
year ALIAS FOR $2;
donor_type ALIAS FOR $3;
donor_code ALIAS FOR $4;
query_statement TEXT;

BEGIN

    query_statement :=
    '
    UPDATE gha.' || table_name  || '
    SET "' || year || '" = "' || year ||'_total"
    FROM gha.' || table_name || ' "' || year || '",
    (
        SELECT
        donor_type
        , COALESCE(SUM("' || year || '"), 0) AS "' || year ||'_total"
        FROM gha.' || table_name || '
        WHERE donor_type = ''' || donor_type || '''
        GROUP BY donor_type
    ) "new_data"
    WHERE
    gha.' || table_name || '.donor_code = ' || CAST(donor_code AS INT) || ';'
    ;

    EXECUTE query_statement;

END;
$$ LANGUAGE plpgsql;

It updates a row in a table that holds an aggregate value and I call it, for example, like this:

SELECT update_total ('bilateral_oda_dac_1', '1990', 'Multilateral', '20002');

The table it updates is set up so that there are columns for each year in a range, i.e.:

   Column   |       Type
------------+-------------------
 donor_code | smallint
 donor_name | character varying
 donor_type | character varying
 1990       | numeric
 1991       | numeric
 1992       | numeric
...

With this function I can only update the aggregate value in one column at at time, but I would like to be able to update a range of columns at once, something along the lines of:

CREATE FUNCTION update_all_total ()
    RETURNS void LANGUAGE plpgsql AS

$BODY$
BEGIN

FOR i IN 1990 .. 1995
LOOP
   PERFORM update_total ('bilateral_oda_dac_1', i, 'DAC', '20001');
END LOOP;

END;
$BODY$;

(I used this post to get me started: https://stackoverflow.com/questions/11164409/sql-call-function-multiple-times-in-a-loop-postgres-8-3). The code snipped above doesn't work because the function I am using takes a string as input and I'm passing it an integer. How can I set the loop up so that I pass strings to the function that I am calling inside it?

You can use to_char() ,

CREATE FUNCTION update_all_total () RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
FOR i IN 1990 .. 1995
LOOP
   PERFORM update_total ('bilateral_oda_dac_1', to_char(i, '9999'), 'DAC', '20001');
END LOOP;
END;
$BODY$;

or can use cast ::char(4).

CREATE FUNCTION update_all_total () RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
FOR i IN 1990 .. 1995
LOOP
   PERFORM update_total ('bilateral_oda_dac_1', i::char(4), 'DAC', '20001');
END LOOP;
END;
$BODY$;


Problem in using muliple inner join I have four tables :

table  p_d :

id   price
1     12
2     13

table s_d

id   name
1    name1
2    name2

table b_d

id    p_d_id
1       1
2       1

table b_d_s

id     b_d_id    s_d_id
1       1         1
2       1         2

Some info about tables :

for table b_d

column p_d_id reference p_id (id)

for table b_d_s

column b_d_id references b_d(id) , column s_d_id references s_d(id)

Now problem is that, i am unable to link all tables together using inner join .

What i need from table b_d_s:

p_d.price  where s_d_id = 1

I am not able to join table b_d_s  and  table p_d  because there is not any common column between them.

result might be

price
 12

I also tried it at my end ,  but could not join all tables together. i used inner join .

Try this (in an INNER JOIN you don't necessary have to reference previous table):

select p_d.price
 from b_d_s inner join b_d  on b_d_s.b_d_id = b_d.id
            inner join s_d  on b_d_s.s_d_id = s_d.id
            inner join p_d  on b_d.p_d_id   = p_d.id
 where s_d.id = 1


Can Postgres partition table by column values to enable partition pruning I'm using Hive currently and it has partitioned tables.
Let's say this is my data:
Country City DailySale
USA     NY   200.2
USA     SF   190.5
CAN     TR   390.4

The I can define my table

create table SALES_DATA(
Country    STRING,
City       STRING,
DailySale  BIGDECIMAL
) PARTITIONED BY (Country, City)

Then I create catalog structure on HDFS:
SALES_DATA/
          Country=USA/
                      City=NY/
                              data-file-0001
                              data-file-0002
... the rest omitted for brevity...

Then partition pruning can be applied for queries like
SELECT SUM(DailySale) FROM SALES_DATA
WHERE  Country='USA' and City='SF'

Hive engine will prune all partitions except one. Super effective and optimal.
I would like to implement smth similar on Postgres. i'll have immutable tables with aggregated data and would like to apply smth similar for them.
I'm trying to go through it
https://www.postgresql.org/docs/13/ddl-partitioning.html
but don't really follow if it's what I need.

In such a case having an index on country and city will probably perform much better than partitioning.
If you are really set on using partitioning, postgres always uses the CHECK constraints to prune partitions.
A way to approach this is to:

Create a function that based on the "country" & "city" input will create a table that inherits from salesdata (if the table already exists return it)
If the table is newly created add a check constraint on the values of country & city.
Create a trigger that executes before/after insert into salesdata that will run that function and insert into the country & city partition.

When following this procedure and you do a select on SalesData postgres will prune the tables not matching the check constraint.


Special characters in Oracle utl_http request When calling a web service using utl_http, the calls fail when the SOAP envelope contains characters like ¿, ± or º.

IS
     http_req  utl_http.req;
     http_resp utl_http.resp;
     request_env varchar2(32767);
     response_env varchar2(32767);
BEGIN

    request_env:='
<soap:Envelope
     ...
</soap:Envelope>';

    http_req := utl_http.begin_request('http://ws.domain.com/Project.asmx', 'POST', utl_http.HTTP_VERSION_1_1);
    utl_http.set_header(http_req, 'Content-Type', 'text/xml; charset=utf-8');
    utl_http.set_header(http_req, 'Content-Length', length(request_env));
    utl_http.set_header(http_req, 'SOAPAction', '"http://domain.com/WS/ProjectAction"');
    utl_http.write_text(http_req, request_env);

I thought that charset=utf-8 in Content-Type should take care of that, but it still bombs and the characters above have to be replaced to their ASCII multi-characters equivalents beforehand.

This is an NLS report for this database:

NLS_CALENDAR    GREGORIAN
NLS_CHARACTERSET    WE8ISO8859P1
NLS_COMP    BINARY
NLS_CURRENCY    $
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE   AMERICAN
NLS_DUAL_CURRENCY   $
NLS_ISO_CURRENCY    AMERICA
NLS_LANGUAGE    AMERICAN
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CHARACTERSET  AL16UTF16
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS  .,
NLS_SORT    BINARY
NLS_TERRITORY   AMERICA
NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT  HH.MI.SSXFF AM TZR

I am kind of consulting the in-house developers on this issue and before I will try to convince them to try redeclare request_env as NVARCHAR2, wanted to get the community opinion on whether that should be the fix they should attempt.

The problem is length or lengthb functions treat these special characters as 1 byte but write_text function (correctly) considers these as 2 bytes. Hence a mal-formed XML is sent to the server.

To resolve this error please use the following instead of the length / lengthb function.

lv_clob_length_bytes := utl_raw.length(utl_raw.convert(utl_raw.cast_to_raw(lv_request_body),'american_america.al32utf8','ENGLISH_UNITED KINGDOM.WE8ISO8859P15'));

The above casts the XML to raw and then converts it from our characterset to UTF8 before finding its length.


Setting and Changing the Server Collation after installation I have the following scenario: 2 database servers SQL Server 2008 R2 on Windows server 2012 R2, Cluster 2 nodes (with no-shared storage), with different Server Collation settings, planning to implement an AG after upgrade SQL Server to 2014 SP2.

Will that different Server Collation settings have an impact on implementing the AG? Or will it affect the SQL Failover operation if required to be done?

Note: this structure is still in the testing phase.

Based on Sean's comment on the question, it sounds like there is a requirement that the Server / Instance -level Collations be the same. But, even if you could technically set up the Availability Group with differing Server default Collations, you wouldn't want to do that as you could easily experience unpredictable / inconsistent behavior when there is a fail over, such as sorting and/or comparison differences.

You could also have code breaking. For example, if the primary system is case insensitive and you have casing differences in variable names, that code will break if moving to an instance having either a case sensitive or binary Collation. This is due to Server-level Collation affecting variable names  (including cursor variable names).


Identifying elements in SSIS package built in MS Visual Studio 2008 I recognize that these are variables, but I am not sure how to add these elements to the package (or how they are added to begin with), what effect that have or even how to manipulate them. No right click, no left click. I can hover the mouse over the element and the tooltip displays the variable name.
I can add variables via right click > Edit > Parameter mapping...however that does not display the little icon on the various control flow elements.

I am trying to rebuild this SSIS package. I have very little experience with MS Visual Studio/SSIS/SQL. Thanks for any input.

The fx glyph indicates that an Expression exists on the item in reference. The first picture indicates there is a an expression on the Precedent Constraint between two items. Whether they're backed by Variables or hard coded won't be known until you explore.
The blue color I think is that the preceding task must finish (Completion) - failure or success does not matter. The red dashed line coming in from offscreen is the Error/Failure path from a Task.
The Dashed line indicates that there is a Logical OR condition between all the elements that feed into the icon offscreen. A solid line will indicate a Logical AND condition.
Double click the blue precedent constraint and you'll see something like this

You can also right click on anything and select properties. An element like a Precedent Constraint will look like this

More complex tasks will have the Expressions in their own section i.e.

All that said, you should be able to add the package to an existing SSIS project and Visual Studio will upgrade it to the current revision. There can be hiccups with things like script components which are usually resolvable by opening up the script and rebuilding. Other things, like custom third party components, need to be updated to bind to the current version of SQL Server binaries.


Why am I seeing these mongodb 3.6.5 server log messages in huge amounts? Could someone please shed some light on these mongodb 3.6.5 server log messages on the primary node of a 3-member replicaset? rs.status() says all the nodes are up and running but seeing a ton of (I)nformational logs.

  2019-01-16T00:07:24.819+0000 I ACCESS   [conn95] Unauthorized: not
  authorized on admin to execute command { replSetGetStatus: "1", $db:
  "admin", $clusterTime...

Unauthorized: not authorized on admin to execute command { replSetGetStatus: "1", $db: "admin", $clusterTime...

The message indicates there is a user or application trying to run the replSetGetStatus command without having the correct privileges.

This command returns the current state of your replica set from the point of view of the server that processed the command. If  this error is logged frequently, it is likely the result of a monitoring script or agent.

You can track down the source by searching the MongoDB server logs for related connection history. The first few lines for a given connection ID will usually include client metadata (optional, so will vary by driver/application) as well as the username used with authentication enabled.

For example, using a Linux-like command line to find the first entries for conn95:

> grep conn95 mongod.log | head -2
2019-01-15T21:41:19.026+1100 I NETWORK  [conn95] received client metadata from 192.168.1.123:59354 conn95: { driver: { name: "PyMongo", version: "3.6.0" }, os: { type: "Darwin", name: "Darwin", architecture: "x86_64", version: "10.14.2" }, platform: "CPython 2.7.14.final.0", application: { name: "monitoring-agent v1.2.3-dev" } }
2019-01-15T21:41:19.027+1100 I ACCESS   [conn95] Successfully authenticated as principal monitoring-agent on admin

These log lines include the following info:

the connection originated from 192.168.1.123
the client application identified itself as monitoring-agent v1.2.3-dev
the application successfully authenticated as user monitoring-agent via the admin database

If this is an expected connection from a monitoring agent, the likely fix would be to grant a role which includes the required access for replSetGetStatus (for example, the built-in clusterMonitor role):

use admin;
db.grantRolesToUser("monitoring-agent", [{ role: "clusterMonitor", db: "admin" }]);

If the connection is unexpected, you could determine an appropriate action based on the source IP or other details. You may wish to shutdown a monitoring app that is no longer required or remove the associated user account and block connections via your firewall. See MongoDB Security Checklist for some available security measures to consider.


How to compare dates in SQL? In a database table I have made a date attribute, but I have set its type to varchar and not Date. My question is: will I still be able to compare such dates in a SQL Query?

Dates in my DB are stored in this format:

  dd/mm/yyyy hh:mm:ss

I have to do a SQL Query in PHP that looks something like this:

SELECT *
FROM DBtable
WHERE DBname='$name' AND date>='31/01/2015' AND date<='31/09/2015';

'date' looks like this: 07/08/2015 15:15:02

I would appreciate an example of how to do this.

Depending on what Database you are using, you should be able to cast the string to a date and then do your comparison.

i.e. in Sql Server

cast(date as datetime) >= '20150131' ...

That said, I'd typically recommend storing the date as a date if at all possible, and if you do need to use strings, It'd be best to use a more standardized format

i.e.
'YYYY-mm-dd' or even better 'YYYYmmdd' instead of 'mm/dd/YYYY' which, for example, can easily be mixed up with 'dd/mm/YYYY' which is common in many cultures. See this blog post: Bad habits to kick : mis-handling date / range queries


SQL Server 2000 optimzation job hasn't run for a while Our SQL Server 2000 used to run a Sunday morning 3am job of optimizing the database. Somehow it was turned off and hasn't run in a year. We have noticed slow access to the database.

We turned it back on and it will run for about 4 hours and complete. Our issue is when it runs on its scheduled Sunday at 3am it will cause the applications that connect to stop working as long as it runs. Before it was shut off the application would continue to work but I believe since it has been off for a year and the job only ran once it wasn't enough to clean the DB up enough.

Should we run it three times in a row? Or what is the best way to fix this?

thanks in advance!

A lot can have happened in a year to cause performance issues. The fact that your "clean up job" did not run might or might not be the primary reason.

Before you start optimizing performance, the first thing you need to do is finding out what your biggest problem is and then address that. (Independent of the SQL Server version, or really independent of the system you are working with.)

SQL Server 2000 provides a limited set of tools you can use for this investigation. Start by tracing batch and procedure executions to see which of them take the longest and do the most reads. It is not unlikely at all that over the year the data changed in a way that a new index is now required.

If in the end you come to the conclusion that the indexes in place are the best for your workload, you can look at this "clean up job" again.

First you need to figure out (on a per table basis) if a reorganization or a rebuild is required. Reorganization usually is faster but has a smaller improvement impact. after a year of "neglect" you most likely need to execute a rebuild on all tables. See http://technet.microsoft.com/en-us/library/cc966523.aspx for more details, particularly the   DBCC DBREINDEX vs. DBCC INDEXDEFRAG section.

As Shawn mentioned, each table rebuild/reorganize action requires a table lock in SQL Server 2000. To mitigate that there are two options:

Upgrade to a more modern version of SQL Server that supports online index maintenance
Clean up one table at a time and then take a significant break to allow other processes to be worked on. Depending on how many tables you have do one a night, or a few with an hour break in between each. In the following nights address other tables. You could also, each night address the 3 - 5 most fragmented tables. DBCC SHOWCONTIG can show you how fragmented a table is. (See above link for details.)


ssms - how to format T-SQL scripts? as part of the process to add articles to my publication
I use the stored procedure sp_addarticle:

use [Returns]
exec sp_addarticle @publication = N'USReturns', @article = N'tblReturnsContainerTypeDescr', @source_owner = N'dbo', @source_object = N'tblReturnsContainerTypeDescr', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'tblReturnsContainerTypeDescr', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotblReturnsContainerTypeDescr]', @del_cmd = N'CALL [sp_MSdel_dbotblReturnsContainerTypeDescr]', @upd_cmd = N'SCALL [sp_MSupd_dbotblReturnsContainerTypeDescr]'
GO

I have publications with over hundreds of articles.
I save the scripts, but I would like them to be properly formatted.
How can I format my scripts in SSMS?

I wanted to see my scripts in this way:

use [Returns]
exec sp_addarticle
 @publication = N'USReturns',
 @article = N'tblReturnsContainerTypeDescr',
 @source_owner = N'dbo',
 @source_object = N'tblReturnsContainerTypeDescr',
 @type = N'logbased',
 @description = N'',
 @creation_script = N'',
 @pre_creation_cmd = N'drop',
 @schema_option = 0x000000000803509F,
 @identityrangemanagementoption = N'none',
 @destination_table = N'tblReturnsContainerTypeDescr',
 @destination_owner = N'dbo',
 @status = 24,
 @vertical_partition = N'false',
 @ins_cmd = N'CALL [sp_MSins_dbotblReturnsContainerTypeDescr]',
 @del_cmd = N'CALL [sp_MSdel_dbotblReturnsContainerTypeDescr]',
 @upd_cmd = N'SCALL [sp_MSupd_dbotblReturnsContainerTypeDescr]'
GO

what is working for me at the moment,
is copying and pasting the script into word
then following this link:
Special characters you can use with Find and Replace in Word

I can put all the "@" in a new line and other similar tricks:

Is there any way of doing this level of formatting using SSMS?

SSMS doesn't have any built-in formatting functions, but there are several 3rd party tools and web sites that can help, many of them free, and all of them surely better than using Word:

http://www.red-gate.com/products/sql-development/sql-prompt/
http://www.apexsql.com/sql_tools_refactor.aspx
http://www.dpriver.com/pp/sqlformat.htm
http://www.cleancss.com/sql-beautify/
http://www.sql-format.com/
http://poorsql.com/
http://www.freeformatter.com/sql-formatter.html

This is just a brief subset from the first page of a search result. I am sure there are dozens of others, but I don't want to turn this into a shopping list question - the main thrust is that you need to pick a tool that you like, rather than hoping SSMS can do it directly.


Virtual Private Database: Create Policy function/s so that a user can view his own records or records inserted by other users with the same company ID So lets say 100, 200, 400, 600 are company_ids and there are 6 administrators who each have their company_ids. The owner of the table User_Info is Admin who is also the owner of other tables and can view, edit, delete, update any tables, procedures, or anything. Has all rights.
Now Admin has created users MKarki and other such in the User_Info table with respective company_ids.
There are other tables inserted using Admin's account in Oracle 10g like customer, employee, shipment and so on. Each of those table have "Company_ID" as a foreign key.
A user when logs into their own session can insert, update, delete the records from the table customer, employee or any other based on the company_id it belongs to. MKarki cannot select, insert, update, or delete records which has company_id 200 (this one is already taken care of).

Example: So the User_Info table has these columns:

Company_ID F_Name    L_Name    Username    Owner   Level_Of_Security
100        Mala      Karki      MKarki     Admin        5
100        Piya      Karki      PKarki      Admin        5
200        Giya      Khadka     GKhadka     Admin        5

Now customer table looks like this:

CUS_ID  Company_ID  F_Na  L_Na    Owner   Sec_Level
23       100        Hor    Loki    MKarki       4
22       100        Bholu  Kam     PKarki       4
34       200        Golu   Pop     GKhadka      4

Based on the tables above: When MKarki logs into her own Oracle account and executes the Select statement: Select * from Customer. She should be able to view all records that have company_Id 100. (This is what I need to know as how this can be implemented???)

So now if PKarki logs into oracle 10g then she should be able to see both of the customer values for company_ID 100 even if one belongs to MKarki since PKarki is also from same company ID.
Now we need to implement a security policy function for the above business policy.

The environment is Oracle 10g and it is for Virtual Private Database

I am unable to get any rows even after setting the context with package, package body, and procedure. (No rows selected or "Error: Policy predicate has errors" if I tweak the Sys_Context a little bit)

I have also created the context with package like this:

Create or replace Context CompAdmin USING Pac_Comp_Adm;

Create or Replace Package Pac_Comp_Adm IS Procedure Get_Company_ID; END; /

Create or Replace PACKAGE BODY Pac_Comp_Adm
IS PROCEDURE Get_Company_ID IS V_Company_ID Number;
BEGIN
Select  Company_ID INTO V_Company_ID FROM User_Info
Where Company_ID = SYS_CONTEXT('USERENV','SESSION_USER');

DBMS_SESSION.SET_CONTEXT('COMPADMIN','Company_ID', V_Company_ID); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END; /

Create or Replace FUNCTION Helo_Comp_ID ( P_SCHEMA_NAME IN varchar2, P_OBJECT_NAME IN varchar2)
Return varchar2 IS V_Hello Varchar2(300);
BEGIN If User = 'ADMIN'
then V_Hello := '';
Else
V_Hello:='SYS_CONTEXT('||q'['CompAdmin']'||
','||q'['Company_ID']'||')
='||q'['Company_ID']';
END IF;
RETURN V_Hello;
END; /

After this I am setting the policies using the function above as follows:

exec DBMS_RLS.ADD_Policy     ('ADMIN','CUSTOMER','Row_Upper_Sec','ADMIN','Helo_Comp_ID','SELECT,INSERT,UPDATE,DELETE');

According to the question, all you need to do is attach a predicate on the query to customer table as shown below.

select * from customer where company_id in(select company_id from user_info where Admin='admin_user');

Right?
If yes, you have to create a function which should return a predicate as:

company_id in(select company_id from user_info where Admin='admin_user')

Well, I have done a small demo for your requirement. I have created doramail as an ADMIN user who owns the tables user_info, customer and a function called Hello_Comp_ID.

SQL> conn / as sysdba
SQL> create user doramail identified by doramail;

User Created.

SQL> grant connect, resource to doramail;

Grant succeeded.

SQL> conn doramail/doramail

Connected.

SQL> create table user_info(Company_Id number, Admin varchar2(20));

Table created.

SQL> create table customer(company_id number, name varchar2(20));

Table created.

SQL> insert into user_info values(100, 'MKarki');

1 row created.

SQL> insert into user_info values(200,'VKarki');

1 row created.

SQL> insert into user_info values(300,'CKarki');

1 row created.

SQL> insert into customer values(100,'Joe');

1 row created.

SQL> insert into customer values(200,'Jimmy');

1 row created.

SQL> insert into customer values(300,'Cashy');

1 row created.

SQL> insert into customer values(300,'Tim');

1 row created.

SQL> Commit;

Commit complete.

SQL> select * from user_info;

COMPANY_ID ADMIN
---------- --------------------
       100 MKarki
       200 VKarki
       300 CKarki

SQL> select * from customer;

COMPANY_ID NAME
---------- --------------------
       100 Ricky
       100 Joe
       200 Jimmy
       300 Cashy
       300 Tim
SQL> Create or Replace FUNCTION Hello_Comp_ID( P_SCHEMA IN varchar2, P_OBJECT IN varchar2) Return varchar2 IS
  2    V_User Varchar2(20);
  3    V_Predicate Varchar2(200);
  4  BEGIN
  5   V_User := SYS_CONTEXT('USERENV','SESSION_USER');
  6    If V_User = 'DORAMAIL' then
  7      V_Predicate := '';
  8    Else
  9     V_Predicate := 'Company_Id in(select company_id from user_info where admin='||q'[']' || V_User || q'[']'||')';
 10    END IF;
 11    RETURN  V_Predicate;
 12  END;
 13  /

Function created.

SQL> exec DBMS_RLS.ADD_POLICY('Doramail','customer','secure_select','doramail','Hello_Comp_ID','SELECT,INDEX');

PL/SQL procedure successfully completed.

SQL> select * from customer;

COMPANY_ID NAME
---------- --------------------
       100 Ricky
       100 Joe
       200 Jimmy
       300 Cashy
       300 Tim
SQL> conn / as sysdba
Connected.
SQL> create user "MKarki" identified by mkarki;

User created.

SQL> grant connect, resource to "MKarki";

Grant succeeded.

SQL> conn doramail/doramail
Connected.
SQL> grant select on customer to "MKarki";

Grant succeeded.

SQL> conn "MKarki"/mkarki
Connected.
SQL> select * from doramail.customer;

COMPANY_ID NAME
---------- --------------------
       100 Joe
       100 Ricky

Since the user MKarki belongs to company_id 100 he only can see records where company_id is 100.

In your case there might be problem in returned predicate. You can change:

V_Hello:='SYS_CONTEXT('||q'['CompAdmin']'||
','||q'['Company_ID']'||')
='||q'['Company_ID']';

To this:

V_Hello:='SYS_CONTEXT('||q'[']' || CompAdmin || q'[']' ||
','|| q'[']' || Company_ID || q'[']' ||')
='||q'[']' || Company_ID || q'[']';

If you are still having trouble then you can view the predicate being attached to your query using explain plan command. And then you can change the predicate(if there are any logical/syntactical errors in the predicate) in the function.

SQL> explain plan for select * from doramail.customer;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2021122684

--------------------------------------------------------------------------------
| Id  | Operation      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     2 |   100 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |           |     2 |   100 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| CUSTOMER  |     5 |   125 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| USER_INFO |     1 |    25 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - access("COMPANY_ID"="COMPANY_ID")
   3 - filter("ADMIN"='MKarki')

Note
-----
   - dynamic sampling used for this statement (level=2)

20 rows selected.


MySQL how to change a slave to a new master? I have a MySQL active master (lets call it Alpha), a passive master (called Beta), and a slave (called Gamma).

Topology before change:

I am going to switch the active master and the passive master.

I will then need to also change the slave (Gamma) to the new active master (Beta).  I am following the instructions in the book "Effective MySQL: Replication Techniques in Depth", page 97, which states before slave (Gamma) is pointed to the new active master (Beta) I should run:

beta> STOP SLAVE SQL_THREAD;
beta> SHOW SLAVE STATUS\G
beta> SHOW MASTER STATUS\G
beta> START SLAVE SQL_THREAD;

It says use the log position from that SHOW SLAVE STATUS on Beta, and use it to run on Gamma:

gamma> START SLAVE UNTIL MASTER_LOG_FILE='{file}', MASTER_LOG_POS={position}

My question is why is that step necessary.  I am planning to stop all application writes to the current active master (Alpha), wait as long as necessary for Beta and Gamma replication to fully catch up.

Since they are both caught up with master (Alpha), is that command "START SLAVE UNTIL..." really needed?

You're correct.  If the slaves are in sync to the same point on Alpha, START SLAVE UNTIL would serve no purpose.

Effectively, they're having you do is stop the slave on Gamma so that it is at the same point in Alpha's binlog as Beta.  In a live, high traffic condition, that can be slightly trickier to do, so I assume that's their motivation for suggesting that approach.

If Gamma and Beta have identical Relay_Master_Log_File and Exec_Master_Log_Pos values, then they are at the same point relative to Alpha, and you can safely switch Gamma to Beta at Beta's current master log file and position.

However... to state the (presumably) obvious, you can't be doing any writing on Beta (other than via replication) around the time you make the cut, because if you're writing to Beta, then Gamma would miss any events that hadn't replicated from Beta to Gamma via Alpha... but the START SLAVE UNTIL wouldn't help you with that, either.

This means you need to actually verify that all three servers are in agreement in all possible directions, so there's nothing outstanding from Beta back to Alpha.  Alpha's Exec_Master_Log_Pos (SHOW SLAVE STATUS) needs to match Beta's Position (SHOW MASTER STATUS) and both Gamma and Beta need to match Alpha and each other in the opposite direction (and of course the actual names of the binlogs need to match, not just the positions).

If you want to be super safe and paranoid, verify that everything is in agreement, then issue FLUSH TABLES WITH READ LOCK; on both Alpha and Beta (leaving the connections open to hold that global lock, which is yours once the prompt returns), then verify that everything remains in agreement at the same binlog positions with nothing written to either master server's binlog that the other hasn't already played back.  Stop, re-home and restart replication on Gamma, verifying that it is connected and waiting for incoming events.  Finally, verify agreement has persisted in each direction between Alpha and Beta once again, then UNLOCK TABLES; on Alpha and Beta.

Note that above, I  never stopped the slave on Beta.  It's not really necessary if you have a global read lock, since the slave is stalled and binlog coordinates can't advance under that condition, but you could stop it if desired, so long as the coordinates continue to match Alpha.


No mysql database in mysql server - how do I give users permissions? I have a mysql server (5.5.44) running on my local Ubuntu 14 machine.
I can start the interactive shell via

$ mysql

When I ask to see all the databases, there is only one

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+

If I try to make a new database I get an error

mysql> CREATE DATABASE purchases;
ERROR 1044 (42000): Access denied for user 'danielsank'@'localhost' to database 'purchases'

I am puzzled by how to resolve this problem.
I'm used to mysql servers always having a database called mysql containing user access data: passwords, allowed hosts, etc.
My server installation doesn't seem to have this.

How do I give myself permission to create new tables, given that there is no mysql database in the server?

The problem here is that I am invoking mysql as user danielsank, but that user does not have privileges to make new tables, nor to see the mysql database!

The issue becomes clear by invoking mysql as root

$ mysql -u root

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

Now we can see the mysql database, and we can check it for user information

mysql> USE DATABASE mysql;
mysql> SELECT User, Host, Password FROM user;
+------------------+-------------------------+-----------------------------+
| User             | Host                    | Password                    |
+------------------+-------------------------+-----------------------------+
| root             | localhost               |                             |
| root             | <my machine name>       |                             |
| root             | 127.0.0.1               |                             |
| root             | ::1                     |                             |
| debian-sys-maint | localhost               | *<a really long string>     |
| danielsank       | localhost               |                             |
+------------------+-------------------------+-----------------------------+

We can create the new database

mysql> CREATE DATABASE purchases;

and grant permissions to danielsank

mysql> GRANT ALL PRIVILEGES ON purchases . * TO 'danielsank'@'localhost';


Query for existence of objects across two multi-to-one relationships I have three relations. The first is "MyObject", which contains an integer "value" attribute as well as a foreign key to "Config". The relationship is "multiple MyObjects to one Config".

The "Config" relation contains basically just a name. Finally, there is the "ConfigKV" relation, having three attributes: a key, a value, and a foreign key to "Config". Each Config has multiple ConfigKVs.

This is the SQL statement to create the database:

CREATE TABLE "MyObject" (
  "value" INTEGER NOT NULL,
  "cfg" INTEGER NULL,
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  CONSTRAINT "cfg_fk"
    FOREIGN KEY ("cfg")
    REFERENCES "Config" ("id"));

CREATE TABLE "Config" (
  "name" TEXT NOT NULL,
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT);

CREATE TABLE "ConfigKV" (
  "key" TEXT NOT NULL,
  "value" TEXT NOT NULL,
  "cfg" INTEGER NULL,
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  CONSTRAINT "cfg_fk"
    FOREIGN KEY ("cfg")
    REFERENCES "Config" ("id"));

I want to query for all MyObjects that:

has value 42
the associated Config of which has exactly two ConfigKVs associated:

"Foo"->"Bar"
"Fuz"->"Baz"

Aside from the "exactly" part above, I can achieve that with this query:

SELECT * FROM MyObject
INNER JOIN (
  SELECT * from ConfigKV WHERE key == 'foo' AND value == 'bar'
) as SQ1 on SQ1.cfg == MyObject.cfg
INNER JOIN (
  SELECT * from ConfigKV WHERE key == 'fuz' AND value == 'baz'
) as SQ2 on SQ2.cfg == MyObject.cfg
WHERE MyObject.value == 42;

The inner joins neatly eliminate all MyObjects that do not have the required Key-Value mappings associated. However, I have no clue how to exclude MyObjects the config of which has a superset of the specified Key-Value mappings. Any idea here?

Thanks a lot!

You can add this restriction with either a NOT EXISTS subquery or with LEFT JOIN / IS NULL:

SELECT * FROM MyObject
INNER JOIN (
  SELECT * from ConfigKV WHERE key = 'foo' AND value = 'bar'
) as SQ1 on SQ1.cfg = MyObject.cfg
INNER JOIN (
  SELECT * from ConfigKV WHERE key = 'fuz' AND value = 'baz'
) as SQ2 on SQ2.cfg = MyObject.cfg
WHERE MyObject.value = 42
  AND NOT EXISTS
      ( SELECT 1
        FROM ConfigKV
        WHERE ConfigKV.cfg = MyObject.cfg
          AND NOT ( key = 'foo' AND value = 'bar'
                 OR key = 'fuz' AND value = 'baz'
                  )
      ) ;


How can SUSER_SNAME return the localized user name? I need to assign network service user to SQL Server database programmatically, so I am running this script:

USE [MyDB]
GO

DECLARE @user nvarchar(50)
DECLARE @SQLStatement nvarchar(500)
SET @user = SUSER_SNAME(0x010100000000000514000000);
SET @SQLStatement =
N'IF NOT EXISTS(SELECT principal_id FROM sys.database_principals WHERE name = ''' + @user + ''')
  BEGIN
      CREATE USER ' + quotename(@user) + ' FOR LOGIN ' + quotename(@user) + N' WITH DEFAULT_SCHEMA=[dbo]
      ALTER ROLE [db_owner] ADD MEMBER ' + quotename(@user) + '
  END'
EXEC sp_executesql @SQLStatement;
GO

That does not work. The problem is that, when running

PRINT SUSER_SNAME(0x010100000000000514000000)

This is returned:

NT AUTHORITY\NETWORK SERVICE

That is wrong, since my system is in Spanish. When I add that user manually using SQL Server Manager and then I see the users in the database, that user is actually added as:

NT AUTHORITY\Servicio de red

Is there a way to add Network service account independent of language?

Since you already have the SID — a value that does not change based on culture — you might as well use it:

DECLARE @Login sysname,
        @DelimitedLogin NVARCHAR(258),
        @SID VARBINARY(85);

SET @SID = 0x010100000000000514000000;

SELECT @Login = sp.[name]
FROM   sys.server_principals sp
WHERE  sp.[sid] = @SID;

SET @DelimitedLogin =  QUOTENAME(@Login);

IF (NOT EXISTS(
        SELECT *
        FROM   sys.database_principals dp
        WHERE  dp.[sid] = @SID
              )
    AND @Login IS NOT NULL
   )
BEGIN
  EXEC(N'
CREATE USER ' + @DelimitedLogin + N' FOR LOGIN ' + @DelimitedLogin
  + N' WITH DEFAULT_SCHEMA = [dbo];
ALTER ROLE [db_owner] ADD MEMBER ' + @DelimitedLogin + N';
');
END;
GO


MySQL: What happens to the row when skip-errors=1062 is set I found out that I can set skip-errors=1062 in the my.cnf but I need to know what happens to the row when the error happens. Does it get silently dropped? Or is that config option the equivalent to STOP SLAVE;SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

Yes, it is equivalent, provided the slave error you are skipping is 1062 (Duplicate Key)

It would have to appear in the [mysqld] section of my.cnf like this

[mysqld]
slave-skip-errors=1062

All other error numbers will stop the SQL thread and would require

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;

to have replication conitnue

If you have multiple error numbers to skip, it should be a comma-delimited list of error numbers

[mysqld]
slave-skip-errors=1062,1053

For more information, please read the MySQL Documentation on it.

You asked

  Does it get silently dropped?

Yes it does. Just remember that any non-key columns that were supposed to change will not change.


How to create tablespace if it does not exist When I create a new schema in Postgres through executing a script in psql I want to put it in a tablespace. If this tablespace does not exist then I want to create it first. Since the default SQL does not have this option I created a function:

CREATE OR REPLACE FUNCTION make_tablespace(tablespace CHARACTER,
                                           directory CHARACTER,
                                           owner CHARACTER)
  RETURNS void
AS
$$
BEGIN
  IF tablespace = '' THEN
    RAISE EXCEPTION 'No tablespace.';
  END IF;
  PERFORM SPCNAME FROM PG_TABLESPACE WHERE SPCNAME=tablespace;
  IF NOT FOUND THEN
    IF directory = '' THEN
      RAISE EXCEPTION 'No directory.';
    END IF;
    IF owner = '' THEN
      RAISE EXCEPTION 'No owner.';
    END IF;
    EXECUTE 'CREATE TABLESPACE '||tablespace||' OWNER '||owner||' LOCATION '''||directory||''';';
    RAISE NOTICE 'Tablespace % created.', tablespace;
  ELSE
    RAISE NOTICE 'Tablespace % already exists.', tablespace;
  END IF;
END $$ LANGUAGE plpgsql;

Unfortunately when I execute it (select make_tablespace('marco', '/opt/marco', 'marco');) this gives an error:

  ERROR:  CREATE TABLESPACE cannot be executed from a function or multi-command string

I searched the internet and there seems to be a workaround (some years ago) by using the dblink package. I do not want to install this. Is there today another way? I can return the SQL statement as a string but how to execute it then?

The point here is that Postgres functions are almost but not quite like true stored procedures. Postgres functions (unlike stored procedures) run in the context of an outer transaction. Therefore, you cannot execute commands that cannot run in a transaction block, like VACUUM, CREATE DATABASE, ... or CREATE TABLESPACE. The manual is clear about that:

  CREATE TABLESPACE cannot be executed inside a transaction block.

You have to run these commands as singular SQL commands. Postgres currently (up to version 9.6) also doesn't have autonomous transactions, which could be a workaround.

So the only workaround to include the command in a function or transaction is faking an autonomous transaction with dblink, just like you found already.

There are some other minor problems in your code. I suggest:

CREATE OR REPLACE FUNCTION make_tablespace(tablespace text,
                                           directory text,
                                           owner text)
  RETURNS void AS
$func$
BEGIN
   IF tablespace <> '' THEN  -- catches '' *and* NULL
      -- do nothing
   ELSE
      RAISE EXCEPTION 'No tablespace.';
   END IF;

   IF EXISTS (SELECT 1 FROM pg_tablespace WHERE spcname = tablespace) THEN
      RAISE NOTICE 'Tablespace % already exists.', tablespace;
      EXIT;
   END IF;

   IF directory <> '' THEN
   ELSE
      RAISE EXCEPTION 'No directory.';
   END IF;

   IF owner <> '' THEN
   ELSE
      RAISE EXCEPTION 'No owner.';
   END IF;

   PERFORM dblink_connect('myserver');  -- name of foreign server
   PERFORM dblink_exec(format('CREATE TABLESPACE %I OWNER %I LOCATION %L', tablespace, owner, directory));
   RAISE NOTICE 'Tablespace % created.', tablespace;
   PERFORM dblink_disconnect();
END
$func$  LANGUAGE plpgsql;

Major points:

Do not use the data type character. You want text.

Any downsides of using data type “text” for storing strings?

While checking for '', also check for NULL.
Use format() for short, clean dynamic SQL code and properly escape user input to avoid sneaky syntax errors and SQL injection.

Related answer with code examples and more explanation:

Persistent inserts in a UDF even if the function aborts
Does Postgres support nested or autonomous transactions?


Dataguard parameter changed after DB bounce I have a production environment of Oracle 11g R2 patchset 4 primary database configured with 2 standby machines.

Primary database unique_name is DBIDI
1st standby database unique_name is DBIFI
2nd standby database unique_name is DBIGI.

These unique_names are configured in parameter

log_archive_config=DG_CONFIG=(DBIDI,DBIFI,DBIGI)

This parameter is configured through Enterprise Manager with My USER with dba grant. Last night we bounced the primary database. After bounce back, the parameter log_archive_config is changed and DBIGI is removed from this parameter:

log_archive_config=DG_CONFIG=(DBIDI,DBIFI)

Can any one tell what is the reason for this?

The initialization parameters are not supposed to change while bouncing the database except in one case, that is if the modification was only for currently running instance(SCOPE=MEMORY).

The following are the steps to modify initialization parameters using Enterprise Manager.

  To modify initialization parameters for the currently running instance, and also record the modifications in the server parameter file that will persist when the database is restarted, complete the following steps:
  On the Current subpage, in the Value column, enter new values for the initialization parameters.
  Select Apply changes in current running instance(s) mode to SPFile.
  (Optional) In the Comments column, enter text explaining the reasons for the changes.
  Click Apply.
  A confirmation message appears.

Viewing and Modifying Initialization Parameters


SS2012 Export Wizard Access is denied When a user exports to a flat file she gets an access denied error.  The wizard allows her to preview the data so she has access on the db side.  From Windows she can create a new file in the target destination so she seems to have network permissions.  She is logged into her computer with a network username and into SS2012 with a SS login.  Everyone else can run the export wizard to this location successfully.  I am stumped!

T.H.  Everyone has a their own logins.  I love your idea to try someone elses SS login from her machine.  Unfortunately the export was successful from her machine with my SS login and also a success from my machine with her SS login.  I say unfortunately because it now works from her machine with her own SS login.  The issue has resolved itself but I don't know why! :-(


Applying patch oracle 11.2.0.4 on 11.2.0.2 We are going to apply Oracle patch 11.2.0.4 on Oracle 11.2.0.2. We have 11.2.0.4 patch. We are using (DBUA) for this upgrade. As DBUA will check the preupgrade requisites, we want to run preupgrade check manually.

Could you please help where we can find preupgrade check?
How to run this script?

From which ORACLE_HOME should we run this script, as we have two ORACLE_HOME directories?

Using the Pre-Upgrade Information Tool

Important:

The Pre-Upgrade Information Tool must be copied to and must be run from the environment of the database being upgraded.

...

Run the Pre-Upgrade Information Tool:

SQL> @$11g_ORACLE_HOME/rdbms/admin/utlu112i.sql


How can i automate the process of log shipping monitoring I have log shipping configured between my Prod SQL server 2005 and QA SQL server 2005.        Per the requirement from Business we need to provide them with a report at the end of the day for the LOG shipping configured for multiple databases on their respective instances.

If i go manually and use standard reports, i am able to achieve this. But is there a way i can get such a report send via e-mail confirming the status of Log shipping configured amongst various DB;s in an instance.

Thanks
Kapil

The script below will give you most of the information from the standard reports in SSMS when executed on your secondary server.

select ls.primary_server,ls.primary_database,lsd.restore_delay,
DATEDIFF(mi,lms.last_restored_date,getdate()) as time_since_last_restore,
lms.last_copied_date,lms.last_restored_date,lms.last_copied_file,
lms.last_restored_file,
lsd.disconnect_users,ls.backup_source_directory,
ls.backup_destination_directory,ls.monitor_server

from msdb.dbo.log_shipping_secondary ls
join msdb.dbo.log_shipping_secondary_databases lsd
  on lsd.secondary_id=ls.secondary_id
join msdb.dbo.log_shipping_monitor_secondary lms
  on lms.secondary_id=lsd.secondary_id

You can then schedule this to run daily with a SQL Agent job or if you have SSRS you can easily schedule and format the report.

If you go the SQL Agent route you will need to configure Database Mail and use that as your delivery service for the results of the query.


SQL Server xml performance I have a table with 21 columns. The majority are numerical and the rest are nvarchar.

I need to add 4 columns that will contain XML data for each row. The XML data for each row can be anywhere from 200 to 2,000 lines.

The question is:

Does adding the XML typed columns to the same table change the speed of querying that table?
What are the performance benefits when I add the xml typed columns to another table and join both tables when doing queries?
Is it better to encode the XML data to shrink it and decode it in the application?

There are a lot of facets to this, so it really depends.

  Does adding the XML typed columns to the same table change the speed of querying that table?

More data potentially could be read (I/O), but when you say speed of querying, if you mean finding rows based on columns outside of the XML, this should usually be based on a index and so the xml should not be especially involved in any performance differences

xml columns can be stored in row or out of row, with obvious implications for number of rows which can be stored in a page, fragmentation etc...  http://technet.microsoft.com/en-us/library/ms189087(v=sql.105).aspx  Storing out of row should mean that more rows fit per page, so less data is read and discarded when you don't actually have the xml data stored in-row.

  What are the performance benefits when I add the xml typed
  columns to another table and join both tables when doing queries?

Well, this is basically equivalent to storing out of row.  You'll only get an administrative  benefit doing it yourself in terms of being able to manage the other table independently.

  Is it better to encode the XML data to shrink it and decode it in the application?

Perhaps.  If we're assuming that you don't need to query it in the database (xml data type with xml query techniques), then why stored it as xml at all instead of varbinary or varchar?


Making the tables with db generalization Can anyone tell me what will be the best way to make the tables for the following diagram:

I was thinking about this:

videos
------

 - id
 - title

music_videos
------------
 - id
 - video_id
 - artist
 - genre

lessons
-------
 - id
 - video_id
 - teacher

Is it right?

(i'm using MySQL if that makes any difference)

Smells a bit like homework this, but ....

Why do you have an id column on music_videos?
Can one music_videos be multiple videos?
And likewise for lessons?
Those two id columns seems redundant.

Other than that, from the information given, it seems decent enough.

Generally speaking, you'll need to decide on some structure between the data, what belongs to where, what is inherited from others, and then make the tables depending on the cardinalities which arise from those.


How to cope with SQL Server 2012 vs SQL (Azure) Database sql files I'm trying to port my system to SQL Database (Azure instance) from SQL Server 2012. I'm using Visual Studio 2013 and I have my .sqlproj with all the definition of my database inside.

Given that a number of SQL statements are not available in SQL Database (like "ON [PRIMARY]", filegroups, etc.), I should change a huge number of *.index.sql , *.pkey.sql, *.table.sql files. Unfortunately I still need to cope with SQL Server 2012 installations (some customers are still on that infrastructure), so I would like to have instead a simple way to switch between the "SQLAzure" vs. "OLD-2012" syntax.

E.g. something like Compilation Symbols would be useful, or similar tricks.

Anyone has a brilliant idea on how to manage such an issue?

Thank you very much!

cghersi

Rather than maintaining two separate code bases, the easier way to do this is by aiming for the lowest common denominator. Try to only use features in the database that are supported in Azure SQL DB. That way you'll have a single code base that works for both, and you'll have much easier upgrades, too.

For box-product customers that truly need SQL Server features that aren't available in Azure SQL DB, build a separate set of scripts that are deployed after the database is created.

If there are specific features you need in SQL 2012 that aren't available in Azure SQL DB, try posting separate questions about each of those (or list 'em in the question itself) so we can get a better feel for why you really need two separate code bases.