String handling in postgres 8.4 This started as a question about indexes, but then when I got my answer I realized I would have to try and convert the answer into 8.4 syntax (I can't believe that format does not exist).

I thought I was handling the variables correctly, but for some reason I can't get the typing to function properly:

CREATE OR REPLACE FUNCTION add_column(schema_name TEXT, table_name
TEXT,  column_name TEXT, data_type TEXT) RETURNS BOOLEAN AS $BODY$
DECLARE   _tmp text;
BEGIN

   EXECUTE 'SELECT COLUMN_NAME FROM information_schema.columns WHERE
     table_schema='''+|| schema_name+'''
     AND table_name='''+||table_name+'''
     AND column_name='''+||column_name+''''   INTO _tmp;

   IF _tmp IS NOT NULL THEN
     RAISE NOTICE USING MESSAGE = 'Column '+||column_name+' already exists in '+||schema_name+'.'+||table_name;
     RETURN FALSE;   END IF;

   EXECUTE format('ALTER TABLE %I.%I ADD COLUMN %I %s;', schema_name,
 table_name, column_name, data_type);

     RAISE NOTICE USING MESSAGE = 'Column '+||column_name+' added to '+||schema_name+'.'+||table_name;

   RETURN TRUE;
END; $BODY$ LANGUAGE 'plpgsql';

Here is the error message I get:

  ERROR: operator does not exist: text + unknown

Does anyone know how I can get this to work in 8.4?

Your function had a number of problems. Try this revised version:

CREATE OR REPLACE FUNCTION add_column(
  full_name   regclass
 ,column_name text
 ,data_type   regtype)
  RETURNS BOOLEAN AS
$func$
BEGIN

IF EXISTS (
      SELECT 1
      FROM   pg_attribute
      WHERE  attrelid = full_name
      AND    attname = column_name
      AND    attisdropped = FALSE) THEN

   RAISE NOTICE 'Column "%" % already exists in %.'
                ,column_name, data_type, full_name;
   RETURN FALSE;
END IF;

-- For Postgres 9.1+:
EXECUTE format('ALTER TABLE %s ADD COLUMN %I %s;'
                ,full_name, column_name, data_type);

-- For Postgres 8.4-:
-- EXECUTE 'ALTER TABLE '|| full_name
--      || ' ADD COLUMN ' || quote_ident(column_name) || ' ' || data_type;

RAISE NOTICE 'Column "%" % added to %.'
             ,column_name, data_type, full_name;
RETURN TRUE;

END
$func$ LANGUAGE plpgsql;

Tested with PostgreSQL 9.1.7.

You do not need dynamic SQL at all to check the catalog table(s) for existence of the column. Simplify that to a plain EXISTS expression.
It's regularly faster by two orders of magnitude to use the table pg_catalog.pg_attribute instead of the bloated view information_schema.columns.

information_schema is only good for SQL standard compliance and portability of code. Since you are writing 100 % Postgres-specific code in a plpgsql function, neither is relevant here. And the basic layout of pg_attribute is not going to change across major Postgres versions either.
format() was introduced with Postgres 9.1. Use this convenient function in modern Postgres. For version 8.4 and older use the commented alternative in the code.
Do not quote the language name plpgsql at the end! It's an identifier, not a value. Single quotes Will make the command fail when using upper case letters.
Generally, it is essential to know the difference between identifiers and values.
I replaced the two parameters schema_name text, table_name text with fullname regclass. This has a number of advantages and no disadvantage AFAICS.

Shorter code and simpler call.
It's simple to supply the schema name where needed and omit it where not.
Cast to regclass checks for existence of the table immediately and raises an exception if not. Also prevents SQLi effectively.
If you don't provide a schema-qualified name, the search_path is used automatically. If the table name is unambiguous given the current search_path the schema name is not printed either when converted to text.
When (automatically) converted to text, schema and / or table name are automatically double-quoted where needed. Voids the need for quote_ident(), escaping as identifier in format() or adding quotes in messages.

Similarly I use regtype for the data_type. Similar advantages. Read more about object identifier types in the manual.
Simpler RAISE syntax.
The concatenation operator in Postgres (and standard SQL) is ||. + is an extension of the standard in SQL Server that does not work with most other RDBMS.


Is this a step in the right direction for normalization? SQL 'inheritance' and Many-to-Many relationships Context:

So, this is kind of a compound question, but they go together, and definitely wouldn't make sense split up into separate questions.

I've been working on a new database for what will end up being a programmer's blog/portfolio, with the occasional tutorial or demonstration, written in ASP.NET MVC 5 and Entity Framework 6. About halfway into designing the database schema, I noticed almost all of the tables contained the same 5 basic columns: ID, Title, Description, DatePosted, and URLStub. While not necessarily redundant, as most of the data would be unique, except maybe where an article is linked to a Demo, it looks wrong or sloppy.

Models:

This is the model I initially set out making:

I swear I auto-arranged/auto-sized those tables

And this is what I ended up shifting towards, but I still feel like I'm doing something stupid:

Questions:

Is the new model even plausible with entity framework?
Is it an improvement (more normal) over the first one?
Should I have even worried about the first one?
Can anyone see anything inherently stupid with the new model?

Thank you in advance for any input, and also sorry in advance if this is going to "create too much discussion" or is "primarily based on opinion".. I don't really know a better place to ask this, and get knowledgeable answers.

This looks like the party relationship model (see https://stackoverflow.com/questions/4969133/database-design-articles-blog-posts-photos-stories).

I recently went through something similar, combining different content types under a single table with subtables for each unique content type.

I'm not sure about the entity framework, but in the new model you have, you'll have an easier way to link content to other content by having a central place storing the ids/primary keys. It's a common design pattern, so I don't think there's anything "inherently stupid" about it.

I don't think you needed to worry about the first one, but the second one is obviously better thought out.


How do you structure a database that organizes post by location like craigslist? I'm new to databases and web development so I'm just looking for a laymen explanation. No need for specific code, just theory. I understand OneToMany and ManyToMany relationships, so no need to explain that. I'm just wondering how someone would handle posts that are categorized by location the same way craigslist does it, eg. Country > State > City > County.

Let's say you have a PostTable that holds all posts site-wide. Do you have 4 fields in this table dedicated to CountryID, StateID, CityID, CountyID? Or do you have 1 field that lists CountyID and from that ID determine what Country, State, and City it's a part of?

Create a table called states.

Create a table called cities that as a many to one relation ship for a table called states.

Then in your post table you have a many to one to the cities tables.

Then you can use syntax like post.city.state.name to get the name of the state for the post or just post.city.name for the city.


Login failed for user when trying to connect to Azure Postgre database from MSSMS I created a database on Azure but can't log to it with MSSMS. I always have the Error 18456 even if I followed this tutorial.

TITLE: Connect to Server
------------------------------

Cannot connect to coventgarden.postgres.database.azure.com.

------------------------------
ADDITIONAL INFORMATION:

Login failed for user '******'. (Microsoft SQL Server, Error: 18456)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Instead of SSMS use pgAdmin to connect to Azure Postgresql. You can download the software from here.

1.Open the pgAdmin application on your client computer.

2.From the toolbar go to Object, hover over Create, and select Server.

3.In the Create - Server dialog box, on the General tab, enter a unique friendly name for the server, such as mydemoserver.

4.In the Create - Server dialog box, on the Connection tab, fill in the settings table.

5.Select Save.

6.In the Browser pane on the left, expand the Servers node. Select your server, for example, mydemoserver, to connect to it.

7.Expand the server node, and then expand Databases under it. The list should include your existing postgres database and any other databases you've created. You can create multiple databases per server with Azure Database for PostgreSQL.

8.Right-click Databases, select the Create menu, and then select Database.

9.Enter a database name of your choice in the Database field, such as mypgsqldb2.

10.Select the Owner for the database from the list box. Select your server admin login name, such as the example, my admin.

11.Select Save to create a new blank database.

12.In the Browser pane, you can see the database that you created in the list of databases under your server name.

For more details please read this documentation.


MyISAM : Should I be concerned by these warnings? I restored some tables from recent Databases backups. Because Im supporting a legacy system that only uses MyISAM tables in their DBs, for backing up I used a little script that pretty much copies all the database fils (the folders with the *.frm *.MYD and *.MYI).

At first everything seemed ok, I could see and query every table without problem, but just to be sure I did a little check in MySQL Administrator and got this:

Status checked for table fotdb.tabfotlog.
1 client is using or hasn't closed the table properly
------------------------------------------------------------
Status checked for table fotdb.tabfotlog.
Size of indexfile is: 11223040        Should be: 11224064
------------------------------------------------------------
Status checked for table fotdb.tabfotlog.
Size of datafile is: 28854024         Should be: 28854668
------------------------------------------------------------
Status checked for table fotdb.tabfotlog.
Corrupt

After that the table was labeled as corrupt and I couldn't access to it, so I did a little repair and everything went back to normal.

My guts tell me it has to do with the fact that the tables were used and written in while copying (because availability needs I cannot lock the tables while I copy them, and since every table by design is completely independent one from another, it is acceptable to have different point of time for each table).

My question is, should I be particularly worried about this? If it was this easy to fix I think I just can get by with this, but there may be bigger concerns Im not aware of and that's what I want to know. Specially since MyISAM documentation is particularly difficult to find.

Yes, you should be worried, but you can improve your backup process with a few changes.
ANALYSIS OF MESSAGES
Status checked for table fotdb.tabfotlog.
1 client is using or hasn't closed the table properly

MyISAM has a header that contains the number of open file handles on the table. If you make a copy of the table with a nonzero value for the number of open file handles, copying it to another MySQL instance or restoring it to your current MySQL instance will result in the table being marked crashed.
Status checked for table fotdb.tabfotlog.
Size of indexfile is: 11223040        Should be: 11224064
------------------------------------------------------------
Status checked for table fotdb.tabfotlog.
Size of datafile is: 28854024         Should be: 28854668

Running REPAIR TABLE fotdb.tabfotlog; will fix it all of these problems after restoring it, but you may probably lose a few rows in the process.
What accounts the  discrepancy in the size of .MYD and .MYI ?
The MyISAM Storage Engine relies solely on the OS for disk changes. That means the OS is responsible for flushing disk changes to the .MYD and .MYI files. This mechanism becomes much worse if you are talking about using MyISAM in a Windows environment.
SUGGESTION
There was a post made back on March 24, 2014 entitled Does a MyISAM table locks if I copy it while MySQL is running? (READ DISCLAIMER)
I have a post (Shell Scripting included) and so does @BillKarwin (proposes XtraBackup for MyISAM) that you may find helpful on scripting a FLUSH TABLES WITH READ LOCK plus other nuances of MyISAM you need to consider.
EPILOGUE
MyISAM Documentation is actually very easy to find. I keep some links in the myisam tag info. The first link goes to the latest MyISAM Documentation in MySQL 5.6.
UPDATE 2014-05-21 19:07 EDT
It just dawned on me. The posts from @BillKarwin and I were answering you from Mar 24th.


Database Design for Questions and Answers I'm building a website that will host a variety of questionnaires. Each questionnaire has a different number of questions and each question has a different number of answers. I have attempted to design a database to hold the questions and possible answers for each questionnaire, but I end up having separate tables for each question. Is this correct or am I going wrong somewhere?

For example

Table for question x

  Answer  | Answer ID
       1         019
       2         089

I cant have a fixed size table for all questions as I don't have a maximum amount of answers. This obviously means I could end up with hundreds of tables, one for each question.

We have an application at our workplace that does a similar thing. It works by having a table that contains a list of all possible questions like such:

CREATE TABLE QUESTIONS
(
   ID INT NOT NULL PRIMARY KEY,
   SUMMARY NVARCHAR(64) NOT NULL UNIQUE,
   DESCRIPTION NVARCHAR(255) NULL
);

Then you have an ANSWERS and a QUESTIONAIRES table defined using the same structure above. Once you have these two tables you then define a table to hold the list of question/answer possibilties as such:

CREATE TABLE QUESTION_ANSWERS
(
   ID INT NOT NULL PRIMARY KEY,
   QUESTION INT NOT NULL REFERENCES QUESTIONS(ID),
   ANSWER INT NOT NULL REFERENCES ANSWERS(ID)
);

Once you have these you can then create a table to contain the responses as such:

CREATE TABLE RESPONSES
(
   QUESTIONAIRE INT NOT NULL REFERENCES QUESTIONAIRES(ID),
   RESPONSE INT NOT NULL REFERENCES QUESTION_ANSWERS(ID)
);

This will give you maximum flexibility allowing you to add new questions and answers without having to change your database design frequently. It can get a bit complicated if you need to version the questions/answers but this should give you a good foothold to work from.

I hope this helps you.


How to enable Cloud Watch logs for AWS DMS task? I am creating AWS DMS task for migrating data from GCP SQL server to AWS Aurora MySQL. How can I attach log group which I created?
I could able to enable cloud watch logs for the task but when I click on "View CloudWatch logs" its showing as log group doesn't exit. Details in image -

You have to modify your migration task and you can see the Enable CloudWatch Logs like below.


MySQL: How does AES_ENCRYPT turn the password into a key? I am trying to figure out how to create the best secure password for AES_ENCRYPT. Specifically, how long should the password be, and how it translates into the 128 bit key length that AES typically uses.

This really isn't a mysql question, more of a "how does encryption work" question.  If you have some time to kill you might want to watch this series

http://www.youtube.com/watch?v=dlS5_qfxAss&feature=results_main&playnext=1&list=PLDCC9179604F435AB

Here's what I felt was a good explaination of password strength I came across a while back

http://www.reddit.com/r/programming/comments/9pu3k/how_long_would_it_take_to_bruteforce_crack_an_aes/c0dv7sx

Remember though, the theoretical security of your password is moot if it's stored in source code that is not secure (world readable, ending up in a .mysql_history when working from the commandline, etc).


Dynamic Dimension Security I have a cube in SSAS 2012 with about 30 dimensions, one of which is Company.

The Company dimension contains a list of companies, of which there are several hundred in the cube.  Our security model requires that users have visibility only to those Company values to which they have been assigned.

In our SQL Server environment, we control this access through a series of functions that are JOINed in VIEWs.  The functions return a table listing all the Company values, which is used to filter results.

For instance:

CREATE VIEW dbo.FakeView
AS
SELECT d.*
FROM FunctionToGetAccess(SYSTEM_USER) f
INNER JOIN DataTable d
    ON d.CompanyID = f.CompanyID

Is there a way for me to port this logic, or a similar logic, to dimension-level security in SSAS?

A couple of notes:

The Cube will be rebuilt overnight
Security does not have to be current hourly, but should be reprocessed during the cube processing overnight
I would strongly prefer not to have to materialize the list of users and allowed clients into a table or dimension

You need to overcome your scruples and materialize the list of users and allowed clients into a dimension, then apply security to that dimension.  If you have Enterprise Edition you can hide the dimension from the users.


SQL Server Reporting services migration (2008 to 2012) We have a SSRS 2008 (not R2) instance with a few hundred reports, and now, on a new machine we have installed a 2012 instance of SSRS. We need to migrate all the reports, while keeping all their rights and privileges, plus the datasources with credentials (some of them have local data sources). Is there a way to achieve this? The high number of reports does not allow us do it manually one by one. We tried to migrate a report, but as I said, the rights and credentials were lost. Any ideas?

Thank you in advance!

Best is to use  Reporting Services Scripter

  Reporting Services Scripter is a .NET Windows Forms application that enables the scripting and transfer of groups of SSRS catalog items from one server to another or from one SSRS folder to another on the same server. Reporting Services Scripter is flexible and lets you transfer catalog item properties, such as serverside report parameters, Subscriptions (both normal and data-driven), Descriptions, History options, and Execution options (including both report-specific schedule and shared schedule execution options) to other SQL Server instances.

Kevin Kline highlights the functionality here and there is a good article by Dale Kelly for migrating reports using RSScripter at MSSQLTips.

Another way is to use the power of PowerShell. There are many script available like here and here.

There is other utility (which I have not used) called reportsync


Join tables with same keys, second table has multiple values for key I have two tables with shared keys and I'm trying to join them and output the results to one table. The problem I'm having is the second table has multiple values for the shared key.

hosts

host_object_id | hostname  |
1              | America1  |
2              | Another1  |

vars

object_id      | varname   |  varvalue |
1              | type      |  Country  |
1              | location  |  N.A.     |
1              | capital   |  D.C.     |
2              | type      |  Another  |
2              | location  |  Location |
2              | capital   |  Example  |

What I'm trying to get is

Hostname       | Type      |  Location |
America        | Country   |  N.A.     |
Another1       | Another   |  Location |

You have two possibilities:

Two joins. Probably a good alternative if you do a limited search, and don't want all available attributes (i.e.: you ignore capital)

SELECT
    hostname,
    type_var.varvalue AS `type`,
    location_var.varvalue AS location
FROM
    hosts
    JOIN vars AS type_var
        ON (type_var.object_id = hosts.host_object_id) AND  type_var.varname = 'type'
    JOIN vars AS location_var
        ON (location_var.object_id = hosts.host_object_id) AND location_var.varname = 'location' ;

max(case) and GROUP BY. This is a typical approach to unpivot in standard SQL. This is normally faster went you either want all attributes (type, location, capital), or you don't limit your queries with a WHERE that restricts the result significantly. (In your case, without WHERE, I'd choose this approach:

SELECT
    hostname,
    max(case when varname = 'type'     then varvalue end) AS `type`,
    max(case when varname = 'location' then varvalue end) AS location
FROM
    hosts
    JOIN vars
        ON (vars.object_id = hosts.host_object_id)
GROUP BY
    object_id ;

In both cases, you'll get:

hostname | type    | location
:------- | :------ | :-------
America1 | Country | N.A.
Another1 | Another | Location

NOTE 1: This assumes that vars has PRIMARY KEY (object_id, varname). Otherwise, you could have more than one type or location per object_id, and that would give you more than one row per object_id in the first case, and just one in the second, choosing the max among them.

NOTE 2: These queries are standard SQL and should work not only on MySQL, but on most SQL RDBMS.

You can check everything at dbfiddle here


Error 1236 From Master After Restored Replication I've been trying to restore a broken replication caused by power interruption. upon checking the slaveDB, it was already broken so I setup a new replication. And upon supposedly completing the replication, i'm stuck with this: Got fatal error 1236 from master when reading data from binary log:    'Misconfigured master - server id was not set'

We are running Percona Server 5.5.
Here were the procedures i made:

1.) on the Master Server masterDB (IP = 10.0.0.20, server_id = 1),I ran innobackupex to make a hot backup. checked the xtrabackup_binlog_info and noted slavedb-bin.000609    and 113535960 as the binlog file and position.

2.) Restored the backup to the slaveDB (IP = 10.0.0.40), and setup the replication as follows -- CHANGE MASTER TO MASTER_HOST='10.0.0.20', MASTER_USER='user',MASTER_PASSWORD='password',MASTER_LOG_FILE='slavedb-bin.000609',MASTER_LOG_POS=113535960 (as noted from the xtrabackup_binlog_info from the innobackupex backup.

3.) After Starting the Slave, i get the error 1236 above with a misconfigured master, but the master server_id is 1.

I've read online on an old thread that a possible solution would be to go to the next binlog file since it might have been corrupted with the transactions applied.

But how would i go about moving to the next binlog file? Is it the next binlog from the innobackupex xtrabackup_binlog_info?" or should i check the master log and find out the last binlog written before it crashed?

I tried moving past the binlog from what innobackupex gave me, going to slavedb-bin.000610 and POS=0, but it still got the same error.

This is a guess, but please note that the option server-id cannot be activated in a hot way. In my experience, this is a common cause of problems when setting up the replication for the first time, specially because the master accepts the following syntax without complaining:

mysql> SET GLOBAL server_id = 1;
Query OK, 0 rows affected (0.04 sec)

And the server id seems to have taken effect:

mysql> SHOW GLOBAL VARIABLES like 'server\_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.02 sec)

But it doesn't work, (it does not have effect until restart and read from the my.cnf or passed as a parameter). The slave will complain until the master is restarted.

The official documentation is misleading, while it says it is dynamic in reality it is not (or at least it is not when setup for the first time, or setup with the default, etc.). See this bug for details, mentioning the misleading documentation.

In summary, activation of the binary logs and server id setup requires a server restart, so I always recommend to do it on installation time. Change it on the [mysqld]section of your configuration file and restart the service.


Annonymous user has no privileges, but still able to show databases? Output of SHOW GRANTS FOR ''@'localhost';

GRANT USAGE ON *.* TO ''@'localhost'

However, when I log in using my default user account, which is later defaulted to an annonymous account by mySQL as shown below :

Output of SELECT USER(),CURRENT_USER();

+------------------+-------------------+
| USER()           | CURRENT_USER()    |
| dukn@localhost   | @localhost        |
+------------------+-------------------+

I can still do show databases; using @localhost. Why is this so ?

UPDATE:

Output of @@version :  5.5.34-0ubuntu0.13.10.1

Output of show grants while logged in as dukn@localhost : GRANT USAGE ON *.* TO ''@'localhost'

I just ran this in MySQL 5.6.15 on my Windows 8 machine

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

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user(),current_user();
+----------------+----------------+
| user()         | current_user() |
+----------------+----------------+
| ODBC@localhost | @localhost     |
+----------------+----------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.05 sec)

mysql>

Then, I logged into MySQL as redwards@localhost

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

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user(),current_user();
+--------------------+--------------------+
| user()             | current_user()     |
+--------------------+--------------------+
| redwards@localhost | redwards@localhost |
+--------------------+--------------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql>

What is the difference? An anonymous user can still execute SHOW DATABASES; but can only see information_schema (contains metadata only anonymous users can see). Why can the database test be seen? The table mysql.db allows test databases to be visible to everyone (See my question MySQL : Why are there "test" entries in mysql.db? and the answer I posted)


Can I turn auto-shrink off while on production server with people connected? I'm going to turn auto-shrink off on our SQL Server 2008, but I was wondering if I need to wait until the end of the day when no one is on the server? Or, can I just make the change now? Thanks!

I have to admit I expected this to require a lock on the database which would mean you couldn't do it while someone else was in the database.  But just to be certain I did some testing and was able to update the setting while I had an open transaction on a table in the database.

So technically yes you could make the change during the day.  However, just because you can doesn't mean you should.  In general any changes like that should wait until after hours (or a maintenance window).  Unless of course auto_shrink is causing you production issues that you don't feel like you can wait on.

It's definitely a good idea to turn it off as soon as you can.  There are a few edge cases I've heard of where it's helpful but not very many.


What are the pros and cons of this schema for flashcards app? I am planning a flashcards app with the following schema:

user(id, name, email)
flashcard_group(id, user_id, group_name, group_tag)
flashcard(id, group_id, question, solution)
guess(id, user_id, flashcard_id, is_correct)

Some of the things I would like to ultimately calculate are:

The number of times a user guesses a flash card incorrectly and correctly (or a correctness percentage)
All of a user's flashcards
All of the user's flashcard groups
Probably more information about guesses.

Does this seem reasonable? How else could I go about capturing guesses in a logical fashion?

Flashcards are used to test knowledge in a topic.
Each card has a question and a correct answer.
One topic has many cards.
Each card may belong to more then one topic.
User chooses a topic to test in.
User may attempt the same test many times.
Each test has many cards (questions) from the chosen topic.
Each card may be part of many test from the chosen topic.

Note:
UserTopicAttempt is as sequence number (1,2,3 ...) for each (USerID,TopicID).


Restore diff backup only on SQL Server Is there a way to restore ONLY a differential backup on SQL Server?

I have a situation where I only need to view one table that was truncated from an entire database.  And instead of trying to restore the entire massive database in it's entirety, I wanted to try and restore only the differential and check to see the condition of that table.

No, the differential backup is based on the differences since the main.  It doesn't contain a full usable structure.


Make DB tables customizable from an Admin I want to build an commercial product promotional mobile application in which the administrator of the app will be able to customize some texts / pictures or whatever from the database that dynamically populates the application.

  For example:

    Lets assume that the database has a description and a picture for the
    product XYZ. The administrator of the specific instance of the app
    decides to change the description of the product through the provided
    CMS for ONLY this instance of the app.

The application will run on tablets that the users will be able to use inside a store, so the administrator of each instance of the app will be the store owner or someone that works there. All the texts / pictures that appear to the application will be fetched from the database.

The application will support both customers login and administrator login. I mean that if the admin of a specific store changes the content of a customizable DB table, all the users that login from this instance of the app will see the customized content.

How is this possible? I first thought to COPY the table that i want to be customizable every time a ADMIN is registed and have a foreign key pointing to the administrator from admin table. Is this an acceptable solution?

PS. I use mysql.

You don't want to be in the business of constantly dynamically creating a separate table for each store's admin to store their own data in. Instead, use a single table and designate a column to differentiate the admin's customization, organized by store.

You haven't given enough details to get into the minutiae of the implementation, but this approach can facilitate having users only see/edit data from their store with a view, stored procedures, etc. once you associate each employee and inventory item to a specific store.

  Inventory
  ---------
  Image
  Description
  Price
  Store

  Employee
  ---------
  Name
  Store
  Title
  StartDate


innodb_thread_concurrency seems evil as it enlarge table size by big factor Environment: OS: MS Windows 2012 R2, CPU: Xeon 12 logical processors, HDD Block Size: 8 KB, DB engine (MariaDB 10.1.23 Or MariaDB 10.2.6 Or MySQL 5.7.18) with Default Settings.

Schema:

CREATE DATABASE IF NOT EXISTS `z` DEFAULT CHARACTER SET utf8;
CREATE TABLE `test` (
    `testId` BIGINT(20) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`testId`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Use below mysqlslap to make 1 Million records without concurrency (single thread, single connection) :

  mysqlslap -pPutPASS --create-schema=z --number-of-queries=1000000
  --query="insert into test values(null)"

test.ibd size: 36.0 MB (37,748,736 bytes)

Truncate/empty the table and perform concurrency insert as below:

  mysqlslap -pPutPASS --create-schema=z --concurrency=100
  --number-of-queries=1000000 --query="insert into test values(null)" --commit=10000

test.ibd size: 56.0 MB (58,720,256 bytes)

Set innodb_thread_concurrency=12 and repeat step 2:

test.ibd size: 76.0 MB (79,691,776 bytes) and sometimes 72 MB. also I tested with --concurrency=500 and table size became 88.0 MB

the actual optimized size is 28 MB by using:

  OPTIMIZE TABLE z.test;

Update 1:

Above test.ibd size results valid for MariaDB 10.1.23.

For MariaDB 10.2.6 test.ibd size:

36 MB (without concurrency)
88 MB (concurrency insert with innodb_thread_concurrency=0)
92 MB (innodb_thread_concurrency=12)
40 MB (optimized)

For MySQL 5.7.18 test.ibd size:

36 MB (without concurrency)
68 MB (concurrency insert with innodb_thread_concurrency=0)
72 MB (innodb_thread_concurrency=12)
40 MB (optimized)

The difference of 10.2.6 & 5.7.18 from 10.1.23 mostly due to barracuda "innodb_file_format" which seems worst than the old format as above tests shows!

Questions:

Why InnoDB table size increased by big factor when I use concurrent Insertion?
Why innodb_thread_concurrency causing the table size to be tripled and nothing mentioned in official docs regarding that?!
What are the config tweaks that make MariaDB/MySQL benefit from the concurrency performance boost WHILE keeping the table size smallest? I am planning to use innodb_thread_concurrency=0 as explained in Hyperthreading & MySQL InnoDB Thread Concurrency Performance but that's not enough, my project will have billions of records and keeping the table size as small as possible will be big gain for performance and saving of H/W resources.

Update 2:

Until I get an answer for the above questions, I will highlight my project requirements in order to get best configuration:

Production Environment:

OS:  MS Windows 2012 R2

SDD: HPE 1.92TB SATA 6G Mixed, Block Size 4 KB

RAM: 768 GB

CPU: 4 Xeon Processors (Sockets) E7-8890 V3 2.5 GHZ (total cores: 72, total logical processors: 144)

Data: 150 inserts per second, 24X7, from 50 to 150 connections, single-row inserted. Avalibilty of data is 1 year (mean any record older than 1 year will be deleted), no updates on the rows, just insert once, then selecting/querying through web UI), one year data estimated to be 3-4 Billion rows. DB schema consist of one big table and 10 tables holding metadata (ex: country, etc), biggest metadata table will hold maximum of 3000 rows during the whole project life cycle. so the main focus is the big table.

So far I adjusted below config params:

max_connections=501
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 256M
innodb_buffer_pool_size=512G
innodb_log_file_size=1G
max_allowed_packet = 1G
innodb_flush_method=async_unbuffered
innodb_flush_neighbors=0
innodb_io_capacity=400
innodb_io_capacity_max=2000
innodb_log_compressed_pages=off
innodb_write_io_threads = 12
innodb_doublewrite = off
innodb_read_io_threads = 12
skip_name_resolve=ON
# dump/restore buffer pool, faster buffer pool warmup
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup  = ON
innodb_page_size=4k

Please Suggest (with Explanation) further config tweaks I need in order to get the best of InnoDB and MariaDB 10.2.6

I suspect I will get down-voted for this Answer, but I think these things need to be said.  (And, no, I don't know the specific reasons for the strange sizes.)

Two simplified replies:

InnoDB plays a lot of games in its attempts to trade off speed and space.
Benchmarks rarely reflect anything in the real world.

Some factors (including possible explanations):

When there are multiple inserts into one table, InnoDB takes effort to minimize the impact of one insert on the other -- this may involve premature block splits, duplication of rows awaiting commits, MVCC, etc.
AUTO_INCREMENT is a bit tricky.  Your benchmark is relatively simpleminded.  In real life, there might be "burned" ids, leading to gaps in the values.  This is (I assume) a deliberate efficiency measure -- gain speed, but waste ids.  Note that there are various AI modes (see innodb_autoinc_lock_mode).
BTrees are inherently sloppy.  When a block is "split", it becomes two half-full blocks.  In the single-threaded benchmark this is not a big issue since there is no contention for the 'last' block.  And InnoDB has some optimizations for such.
BTrees are even more sloppy in the presence of MVCC -- since multiple copies of a row live for some time.
BTree blocks (in InnoDB) are not necessarily recombined promptly.
Allocation in in pages, extents, segments.  See here .  This can lead to megabytes of un-recouped space.  Furthermore, there is no 'visibility' into how much space is wasted at page/extent/segment level.
Concurrency of 100 (especially with only 12 processors) is asking for a lot of thrashing of locks in InnoDB.  Oracle, even with 5.7, does not brag above about 64.  I find that 100 leads to InnoDB stumbling over itself, and not necessarily finishing the benchmark any sooner than with a smaller number.  That is, excessive currency can lead to terrible latency without necessarily helping throughput.
10K rows may lead to an excessive use of the undo buffer (etc), thereby actually be slower than committing sooner.
Your "project will have billions of records" -- but how fast will they be inserted, and from how many different connections, and will they be batched or single-row inserted, etc.?
My take on high speed ingestion .

More (after Update 2)

innodb_buffer_pool_instances = 16
innodb_doublewrite = off  -- this may be safe since the drive and the page_size are the same.
SSDs make 150 individual inserts/sec not a problem
innodb_page_size=4k -- be aware that all tables will have to live with this.  It limits rows to under 2KB.  BTrees will be a little deeper, but I don't see that as a problem.
With the "delete after a year", I strongly recommend using PARTITION BY RANGE(TO_DAYS(...)) with 14 (monthly) or 55 (weekly) partitions.  This will make the deletes have essentially no impact.  A DELETE, even daily, would probably cause hiccups.  More discussion here .
How fast did the mysqlslap run (inserts/sec)?  Probably a lot faster than 150.  If your 150 is reasonably steady, then you will not experience concurrency=100, possibly rarely =10.  I don't see a problem ingesting your data.
If you need to 'normalize' (ex: country, etc) the data as you load it, see my ingestion link.
What are the SELECTs like?  They will be competing with the INSERTs.  I suspect 200 selects/sec, if they are point queries, could coexist with the inserts.  If you expect 1000, we need to think further.
With partitioning, if the excessive size persists, here is a fix...  REORGANIZE or REBUILD, not OPTIMIZE, a weekly partition after it is fully built.  That way, the excessive disk usage can be freed.  Be sure to use innodb_file_per_table before creating the table.


Facing error while executing function inside the package in PL/SQL Hi everyone hope you're doing well.
I'm new to oracle and I'm reading about the concept of 'Package' and 'Package body'.
I have a function as you can see below
CREATE OR REPLACE Function Show_description(i_Course_No
   Course.Course_No%Type)
 Return Varchar2 AS
V_Description Varchar2(50);

 Begin

     Select description
     into v_Description
     From Course
     Where Course_No = I_Course_No;
        Return V_Description;

  Exception
     When No_Data_Found Then
        Return('The Course Is not in the DataBase');
     When Others Then
        Return('Error in running Show_Description');

  End;

and I have a package containing only this function :
CREATE OR REPLACE PACKAGE MY_First_Package is

   function Show_description(i_Course_No Course.Course_No%Type) Return
   Varchar2;

   end ;

and I have a package body as you can see here :
create or replace package body my_first_package AS

  Function Show_description(i_Course_No Course.Course_No%Type)
  Return Varchar2 AS
  V_Description Varchar2(50);

  Begin
    Select description
    into v_Description
    From Course
    Where Course_No = I_Course_No;
        Return V_Description;

    Exception
      When No_Data_Found Then
          Return('The Course Is not in the DataBase');
      When Others Then
          Return('Error in running Show_Description');

    End;
       End  my_first_package;

and everything seems fine without any error . When I want to execute this package as :
exec my_first_package.Show_description(12);

I face an error which is : ORA-00900 :Invalid sql statement
what is the problem??
Thanks in advance

Well, yes - but not necessarily. As you wanted to use EXEC (which is SQL*Plus), then have a look how you could have done it; first, a package with a function that returns department name for a certain department number.

SQL> create or replace package my_first_package as
  2    function show_Description(par_deptno in varchar2) return varchar2;
  3  end;
  4  /

Package created.

SQL> create or replace package body my_first_package as
  2    function show_Description(par_deptno in varchar2)
  3      return varchar2
  4    is
  5      retval dept.dname%type;
  6    begin
  7      select dname
  8        into retval
  9        from dept
 10        where deptno = par_deptno;
 11      return retval;
 12    exception
 13      when no_data_found then
 14        return null;
 15    end;
 16  end;
 17  /

Package body created.

Now, how to use EXEC: declare a variable first, and then ...

SQL> var l_ret varchar2(20);
SQL> exec :l_ret := my_first_package.show_description(10);

PL/SQL procedure successfully completed.

SQL> print l_ret;

L_RET
-------------------------------------------------------------
ACCOUNTING

SQL>

If you'd want to use that function in PL/SQL, then you could do the same - declare a variable and return the function value into it, e.g.

declare
  l_ret varchar2(20);
begin
  some code here
  l_ret := my_first_package.show_description(10);
  ...
end;

Or, you could use it "directly" in, for example, WHERE clause:

begin
  select ...
  into ...
  from ...
  where description = my_first_package.show_description(10)
    and ...
end;


SQL Server: Log Shipping Read Only When log shipping between a primary database and a secondary database, does the secondary database have to be set to read-only?

By read-only, I am not talking about the idea of only reading from the database. I am talking about the database having a specific status of Read-Only or Standby/Read-Only.

I cannot find anything that states that this is required in the Log Shipping Overview and other related pages. This is causing an issue as I am unable to add any indices to a read-only database.

You can't change the secondary database in any way.
This is normal - the secondary database is supposed to be a backup of the main database, so it's not possible to add stuff that's not in the main database. That's why it's in read-only mode.

It's fine to use the secondary database for reporting, to reduce the load on the primary server (see Using Secondary Servers for Query Processing).
But if you need any special indices for that, you have to add them in the primary database and wait until they are log-shipped to the secondary database.

As jchelad already said in his answer, one disadvantage of using the secondary database for reporting is that users are disconnected whenever a log backup is restored.
It's possible to change this, so that restoring is paused until nobody is connected to the database.

Quote from the above link:

  There are two options for configuration when you place the secondary
  database in standby mode:

  You can choose to have database users disconnected when transaction log backups are being restored. If you choose this option, users will
  be disconnected from the database each time the log shipping restore
  job attempts to restore a transaction log to the secondary database.
  Disconnection will happen on the schedule you set for the restore job.
  You can choose not to disconnect users. In this case, the restore job cannot restore transaction log backups to the secondary database
  if there are users connected to that database. Transaction log backups
  will accumulate until there are no user connections to the database.

But I wouldn't recommend using the second option.
We are using logshipping at work, and we are using the secondary database for reporting.

We decided to choose the first option (users are disconnected when a backup is restored) because having a backup is more important than being able to run queries all the time.

We didn't like the second option (only restore if no one is connected to the database) because in case the main server dies, we don't want to find out that the secondary database wasn't restored in the last few hours because the salespeople have been running queries all day.

We "educated" our reporting users that there is a short time every 15 minutes where they can't run queries, and they accept that.
(the alternative would be to run the queries on a copy of the main database from yesterday evening, but they prefer data from today, so they have to live with the "15 minute break")


Why is there no predicate applied to the Index Scans? We're having problems with some queries which are similar to this:

SELECT COUNT('A')  FROM  [dbo].[OINV] T0
INNER  JOIN [dbo].[OCRD] T2  ON  T2.[CardCode] = T0.[CardCode]
WHERE T0.[CardCode] = (@P2)  OR  T2.[FatherCard] = (@P3)

Query Plan

The indexes that it's hitting are defined as:

NONCLUSTERED INDEX [OCRD_FATHER] ON [dbo].[OCRD]
(
    [FatherCard] ASC
) INCLUDE (CardCode)

NONCLUSTERED INDEX [OINV_CUSTOMER] ON [dbo].[OINV]
(
    [CardCode] ASC
)

They're currently taking 1-2 seconds to run, and returns a count of 0 (which is what we're expecting).

I'm incredibly surprised that it's not filtering the NonClustered Indexes before it feeds into the Hash Match - it's feeding every single row.
These are vendor-software queries, so unfortunately there's no way for us to rewrite them.

Why is this, and is there any way to change it to filter before it does the Hash Match without rewriting the query?

Example Data Setup

CREATE TABLE [dbo].[OCRD]
  (
     [FatherCard] NVARCHAR(50),
     [CardCode]   NVARCHAR(50)
  );

INSERT INTO [dbo].[OCRD]
SELECT TOP (2076000) NEWID(),
                     NEWID()
FROM   master..spt_values v1,
       master..spt_values v2,
       master..spt_values v3

CREATE TABLE [dbo].[OINV]
  (
     [CardCode] NVARCHAR(50)
  )

INSERT INTO [dbo].[OINV]
SELECT TOP (5175460) NEWID()
FROM   master..spt_values v1,
       master..spt_values v2,
       master..spt_values v3

CREATE NONCLUSTERED INDEX [OCRD_FATHER]
  ON [dbo].[OCRD] ( [FatherCard] ASC )
  INCLUDE (CardCode)

CREATE NONCLUSTERED INDEX [OINV_CUSTOMER]
  ON [dbo].[OINV] ( [CardCode] ASC )

is there any way to change it to filter before it does the Hash Match without rewriting the query?

In Enterprise Edition, an indexed view can be used. For example:

CREATE VIEW dbo.OINV_OCRD
WITH SCHEMABINDING
AS
SELECT
    INV.CardCode,
    CRD.FatherCard,
    COUNT_BIG(*) AS cnt
FROM dbo.OINV AS INV
JOIN dbo.OCRD AS CRD
    ON CRD.CardCode = INV.CardCode
GROUP BY
    INV.CardCode,
    CRD.FatherCard;
GO
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.OINV_OCRD (CardCode, FatherCard);
CREATE NONCLUSTERED INDEX i ON dbo.OINV_OCRD (FatherCard) INCLUDE (cnt);

Then a query like:

DECLARE
    @P2 nvarchar(50) = N'D20B5DD1-C729-4B7A-A276-950CE7DCF128',
    @P3 nvarchar(50) = N'6A0DBEAB-FECB-40DB-86C5-AAFA612DA691';

SELECT COUNT_BIG(*)
FROM [dbo].[OINV] T0
JOIN [dbo].[OCRD] T2
    ON T2.[CardCode] = T0.[CardCode]
WHERE
    T0.[CardCode] = (@P2)
    OR T2.[FatherCard] = (@P3);

Produces an execution plan using index intersection on the indexed view:

Table 'OINV_OCRD'.
    Scan count 2,
    logical reads 8, physical reads 0, read-ahead reads 0,
    lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

All the usual caveats around indexed view usage apply. Automatic indexed view matching is only available in Enterprise Edition (or equivalent). Using COUNT('A') does not affect the core mechanism, though a Compute Scalar is added to convert bigint to integer.

For more details around the indexed view matching feature see my answer to a related question.

If you add tables to the query, the indexed view matching is likely to continue to work (assuming the match is valid) since the optimizer is capable of matching parts of a query to one or more indexed views. This answer can only address the details actually presented in the question.

If the vendor queries follow a set pattern, you could force the indexed view plan to be used via a Plan Guide.


MYSQL Triggers syntax I need some help with my trigger statement, as I would like to only insert the new row if a column value does not already exist, or if it does exist update a corresponding column. Currently I have a trigger to insert the new row data then another trigger to update it.

TRIGGER `status_insert` BEFORE INSERT ON  `member_status_likes`
FOR EACH
ROW INSERT INTO member_status_like_count( status_id, status_likes )
VALUES (
NEW.status_id, 1
)

 TRIGGER `status_like_increment` AFTER INSERT ON  `member_status_likes`
FOR EACH
ROW UPDATE member_status_like_count SET status_likes = ( status_likes +1 ) WHERE status_id = NEW.status_id

I guess you want to either insert a new row with the counter status_likes = 1 or incresase it by 1 when a row with status_id already exists. If there is a UNIQUE constraint on member_status_like_count (status_id), you can use INSERT ... ON DUPLICATE KEY UPDATE:

CREATE TRIGGER member_status_likes__AFTER_INSERT__UPSERT__like_count
AFTER INSERT ON member_status_likes
FOR EACH ROW
  INSERT INTO member_status_like_count
      (status_id, status_likes)
  VALUES
      (NEW.status_id, 1)
  ON DUPLICATE KEY UPDATE
      status_likes = status_likes + 1 ;


Do covering indexes in PostgreSQL help JOIN columns? I have a whole lot of tables that look vaguely like this:

CREATE TABLE table1(id INTEGER PRIMARY KEY, t1c1 INTEGER, t1c2 INTEGER);
CREATE TABLE table2(id INTEGER PRIMARY KEY, t1 INTEGER REFERENCES table1(id), t2c1 INTEGER);

And I do a whole lot of joins where I'm trying to filter on the joined-in table to get stuff from the first table, like this:

SELECT t1c1
FROM table1
JOIN table2 ON table2.t1 = table1.id
WHERE t2c1 = 42;

When I go to write indexes for a table I'd look at the columns that get used in the WHERE clause and build out indexes to satisfy them. So for this query I'd wind up writing an index like this:

CREATE INDEX ON table2 (t2c1);

And this index is at least eligible for use in that query.

My question is that if I write an index like this:

CREATE INDEX ON table2 (t2c1, t1);

Will the index be used as a covering index to help the JOIN in the above query? Should I change my index writing strategy to cover foreign key columns?

Will the index be used as a covering index to help the JOIN in the above query?

It depends. Postgres has "index-only" scans as index access method, there are no "covering indexes" per se - up to Postgres 10.
Starting with Postgres 11 true covering indexes with INCLUDE columns are available. Blog entry by Michael Paquier introducing the feature:

https://paquier.xyz/postgresql-2/postgres-11-covering-indexes/

Related answer with code example:

Does a query with a primary key and foreign keys run faster than a query with just primary keys?

That said, the index CREATE INDEX ON table2 (t2c1, t1); makes perfect sense for the query you demonstrate. It can be used for an index-only scan if additional preconditions are met, or it can be used in a bitmap index scan or a plain index scan. Related:

Index usage on a temporary table
Is a composite index also good for queries on the first field?

JOIN conditions and WHERE conditions are almost completely equivalent in Postgres. They certainly can use indexes in the same way. You can rewrite your query:
SELECT table1.t1c1
FROM   table1
JOIN   table2 ON table2.t1 = table1.id
WHERE  table2.t2c1 = 42;

With this equivalent:
SELECT table1.t1c1
FROM   table1 CROSS JOIN table2
WHERE  table2.t1 = table1.id
AND    table2.t2c1 = 42;

The first form is obviously preferable, though. Easier to read.
Why "almost" equivalent? (Makes no difference for the simple query at hand.)

Why does this implicit join get planned differently than an explicit join?

Related:

Are implicit joins as efficient as explicit joins in Postgres?
What does [FROM x, y] mean in Postgres?


Run SQL Agent Job 3 times in a Row How do I execute SQL Agent job 3 times in a row?

We are creating Data Warehouse Project. It ETLs data from Flatfile Legacy Folder, and creates Dim and Fact tables.

SSIS Project Environment Variable (FlatfileFolder) set: to March 2018

Import ETL Flatfiles into Staging Database for March 2018
Create Customer Dimension Tables
Create Product Dimension Tables
Create SalesTransaction Fact Tables

Want to execute this 3 times in a row, changing Project Environment Variable (FlatfileFolder) to April 2018.
Run 4 jobs again,
then change Project Environment Variable (FlatfileFolder) to May 2018, and run 4 job steps again. (Theoretically, should have been designed to extract all history files and load at once, however did not design this, just inheriting it.)

This stack link below talks about endless loop, only want to run it 3 times, and change environment variable.

Running job in endless loop

Options:

(a) write 12 Step SQL Agent Job,

(b) Or somehow after Step 4, on success go to back to Step 1? How to make this only run 3 times?

Eventually may want to execute job 5-10 times in row for later months.
Trying to conduct with SQL environment, prefer to refrain from C# or Powershell unless required.

We are processing data.

Thanks,

I think I would solve it this way:

Create a driving stored procedure that identifies if work needs to be done.  That could mean checking a table for the next folder month to process.  This driving table would be pre-populated with the number of folders (months) that need to be processed.  You might want to include a method to determine the order of the folders to process so that you process them in the correct order.
Set up a WHILE LOOP to check IF work is to be done. Use the custom stored procedure sp_sp_start_job_wait to invoke the job that has the 4 steps.  This procedure will start the job and wait for it to complete.
Upon successful completion of the job, mark as complete or delete the folder row for the month you just processed.
The WHILE LOOP re-iterates to check for more work to be done.

To start the entire process, simply invoke the driving stored procedure.

You could also eliminate the need for the SQL Agent jobs altogether and simply invoke the SSIS packages directly from the driving stored procedure I described earlier using built-in stored procedures; e.g. ssisdb.catalog.create_execution, ssisdb.catalog.start_execution.  There is a good blog post describing that process How To Execute an Integration Services (SSIS) Package from a SQL Server Stored Procedure.  Summarizing the main points from that post:

create procedure dbo.execute_ssis_package_sample
 @output_execution_id bigint output
as
begin
 declare @execution_id bigint
 exec ssisdb.catalog.create_execution
  @folder_name = 'mssqltips'
 ,@project_name = 'exec-ssis-stored-proc-ssis-sample'
 ,@package_name = 'Sample.dtsx'
 ,@execution_id = @execution_id output
 exec ssisdb.catalog.start_execution @execution_id
 set @output_execution_id = @execution_id
end

The stored procedure ssisdb.catalog.create_execution (new in SSIS
2012) is called to create an instance of an SSIS package execution.
You pass the folder, project and package to the
ssisdb.catalog.create_execution stored procedure.
ssisdb.catalog.create_execution returns an output parameter named
@execution_id; you need this value to start executing the SSIS
package as well as to check the status of package execution.
The ssisdb.catalog.start_execution stored procedure (new in SSIS
2012) is called to start the actual execution of the package.
The SSIS package execution is asynchronous; i.e. when you call the
ssisdb.catalog.start_execution stored procedure you are not waiting
for the package to complete.

I highlighted the fact that the default behavior of ssisdb.catalog.start_execution is to run asynchronous, but there is a workaround for that as documented in this post by Tim Mitchell.  See the section Execute Packages Synchronously. Quoting from that post:

  This one is a relatively easy fix. Although the default behavior is to
  execute [catalog].[start_execution] asynchronously, there is a system
  parameter that lets you override this behavior on a per-execution
  basis. The parameter name is SYNCHRONIZED, and it expects a value of
  either 0 (not synchronized, the default behavior) or 1 (synchronized).
  When overriding this value and setting it to 1, the call to
  [catalog].[start_execution] will not return until the package has
  completed its execution. This means that [catalog].[start_execution]
  will run as long as the package is still running. The syntax to add
  this parameter is shown below.

-- Create the execution object
DECLARE @execution_id BIGINT
EXEC [SSISDB].[catalog].[create_execution]
    @package_name = N'Package1.dtsx'
    , @project_name = N'Project1'
    , @folder_name = N'Folder1'
    , @use32bitruntime = False
    , @reference_id = NULL
    , @execution_id = @execution_id OUTPUT

-- System parameters
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
    @execution_id
    , @object_type = 50                     -- System parameter
    , @parameter_name = N'SYNCHRONIZED'
    , @parameter_value = 1

-- Execute the package
EXEC [SSISDB].[catalog].[start_execution] @execution_id

  As shown, parameter values (including system, project, and package
  parameters) are added after [catalog].[create_execution] and before
  [catalog].[start_execution]. In the above example I set the
  SYNCHRONIZED system parameter to 1, forcing
  [catalog].[start_execution] to wait until the package completes before
  it returns.

  This override solves one problem but leaves another: Even though
  [catalog].[start_execution] now runs synchronously, it does not report
  the status of the package execution. Most workflows need to know the
  status of the package execution, not just that the execution has
  finished. Fortunately, there is a workaround for that as well.

Force a Script Failure if the Package Fails

  This workaround requires more than a simple execution parameter, so it
  has more moving parts. In a nutshell, here is the design pattern for
  forcing the T-SQL script to fail in the event that the package it has
  called fails:

  Create the execution
  Set the SYNCHRONIZED parameter to 1
  Execute the package
  Check the execution log for this execution, and force the T-SQL    script to fail if the package failed

  The previous script already took care of steps 1-3, so we just need to
  add the final step. This can be as simple or as complicated as needed,
  but in my example I just check [catalog].[executions] and retrieve the
  [status] value for that execution; if the value does not equal 7 (the
  status ID for Success), the script will generate a failure using
  RAISERROR.

-- Create the execution object
DECLARE @execution_id BIGINT
EXEC [SSISDB].[catalog].[create_execution]
    @package_name = N'Package2.dtsx'
    , @project_name = N'Project1'
    , @folder_name = N'Folder1'
    , @use32bitruntime = False
    , @reference_id = NULL
    , @execution_id = @execution_id OUTPUT

-- System parameters
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
    @execution_id
    , @object_type = 50                     -- System parameter
    , @parameter_name = N'SYNCHRONIZED'
    , @parameter_value = 1

-- Execute the package
EXEC [SSISDB].[catalog].[start_execution] @execution_id

-- Check package status, and fail script if the package failed
IF 7 <> (SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id = @execution_id)
RAISERROR('The package failed. Check the SSIS catalog logs for more information', 16, 1)


Finding delete or update records I have had three incidents in recent months where records in a table that have either been deleted or values updated to zero  across a whole table. We have a team of four people   who have the permission and who are responsible for updating the database who could have done this. Disappointingly no one has admitted to carrying out the changes.

Going forward I would like to be able to have a record of these transactions. I was wondering what do other people use to track these changes? Do they use software that tracks changes or do you create stored procedures or trace files? If anyone has this set up at their facility I would like to know what they use. The trace files do have the information I am after such as login name machine number and the sql statement so it will give me the information if I set them up in advance.

I have copies of the database and the transaction logs when these changes took place. Is there anything I can do with these old files to help track down the culprit? Thanks in advance to anyone who replies. We are using SQL server 2005.

fn_dblog is the way to look backwards as the other commentators have said.

Only allowing the users to modify the data through stored procedures is a great way to prevent this happening in the first place, as you can add logic to prevent users from mass modifying records they shouldn't, or ensuring that they have to provide correct values for updates. This may mean that you'd need to make application changes depending on how they're currently accessing the data. Which may or may not be possible for you.

If you can't do that, then a quick and simple way with SQL 2005 is going to be using a trigger to do some DML auditing at the table level. (SQL Server 2008 onwards have auditing tools built in).

A simple solution for your issue might be:

drop table audit_test
go
create table audit
(
uname varchar(50),
[date] datetime,
what nvarchar(4000),
host varchar(50),
)
go

create trigger ddlcheck on tbl_example
for update, delete
as
declare @tbltmp table(eventtype nvarchar(30),para smallint, strsql nvarchar(4000))
insert into @tbltmp exec ('dbcc inputbuffer('+@@spid+')')
insert into audit_test select SUSER_NAME(), GETDATE(),  strsql ,  HOST_NAME() from @tbltmp

This will fire any time a query attempts to update the table, or delete from the table. It uses DBCC inputbuffer ( http://msdn.microsoft.com/en-us/library/ms187730(v=sql.90).aspx ) to get the issued command. This gives you a table populated with all the update and delete statements issued against a particular table. Plus it records who issued statement, and where and when it was.

Now this could be a lot of logging data on a busy table, so this could be restricted to just catch 'bad' queries (say those that update/delete 1000+ rows) by altering the trigger to:

create trigger ddlcheck on tbl_example
for update, delete
as
declare @cnt integer
select @cnt=count(1) from deleted
if @cnt>1000
begin
  declare @tbltmp table(eventtype nvarchar(30),para smallint, strsql nvarchar(4000))
  insert into @tbltmp exec ('dbcc inputbuffer('+@@spid+')')
  insert into audit_test select SUSER_NAME(), GETDATE(),  strsql ,  HOST_NAME() from @tbltmp
end

This logs less data, but the trigger still needs to 'evaluate' for every operation, so may have a performance impact which will need to be measured and tested. This will also miss the actions if the user submits lots of individual statements, ie;

won't catch:

delete from tbl_example where id=1
delete from tbl_example where id=2
.....
delete from tbl_exampe where id=1000

will catch

delete from tbl_example where id>0 and id<1001

Hope this is of some help for the future.


t-sql or powershell to return currently-configured snapshot folder for transactional replication SQL 2012 I'm working on a script to iterate through all the snapshot folders and do some basic find and replace of the ANSI_PADDING OFF bug and replace it with SET ANSI_PADDING ON. I'd like to be able to dynamically point to any publisher and get a list of currently configured snapshot folders by publication. I can then filter and dedupe if needed.

I know I can query the distribution database for the default folder but how do I return the currently configured one, if say, someone changed the snapshot folder via the Replication Monitor GUI?

The snapshot folder is configured per Distributor.  The stored procedure sp_helpdistributor returns a column directory which is the snapshot folder for a specified distributor.


Can SQL Server 2016 validate JSON on insert? Currently SQL Server 2016 CTP 3 and later releases support NVARCHAR(max) fields with JSON content. However since the type NVARCHAR(max) is totally free-form, there is no constraint on these fields.

Can I declare a constraint that prevents insertion of a non-null value that is not valid JSON?

Add this constraint:

ALTER TABLE [dbo].[Resource]
 ADD CONSTRAINT [RESOURCE_ExtJSON_VALID]
   CHECK ( ISJSON( ExtJSON )> 0 )


Postgres Update Limit I'm curious if Postgres has anything in place where I could limit the amount of rows kept for a ID.

For example, say I have a users table and login_coordinates table. Every time the user logs in, the most current coordinates get fed to a column in the users table, as well as inserting into the login_coordinates table.

I only want to keep the last 10 records, and deleting the 11th(oldest) record in the login_coordinates table for all users.

Users

   user_id            | current_coordinates        |
----------------------+----------------------------+
    1                 | aaaa.bbbbb, aaaaa.bbbbbb   |
    2                 | zzzz.xxxxxx, xxxxx.xxxcxx  |
    3                 | dddd.xxxxxx, xxxxx.xxxcxx  |

Login Coordinates

  coordinates_id      | old_login_coordinates      |        user_id           |
----------------------+----------------------------+--------------------------+
    1                 | aaaa.bbbbb, aaaaa.bbbbbb   |     1                    |
    2                 | xxxxx.xxxxxx, xxxxx.xxxcxx |     1                    |
    3                 | xxxxx.xxxxxx, xxxxx.xxxcxx |     1                    |

Is there anything that would limit the records to 10 coordinate_id per user, always deleting the oldest records?

I'm using PostgreSQL 9.5.

as well as inserting into the login_coordinates table

I don't see the benefit of redundant storage. Just write to the table login_coordinates. Easy enough to access with only 10 rows per user. Don't update the user row as well.

Basically, what @Ziggy already suggested, with more flesh. Based on this table:

CREATE TABLE login_coordinates (
  login_coordinates_id serial PRIMARY KEY
, user_id integer NOT NULL  -- REFERENCES users
, login_at timestamptz NOT NULL DEFAULT now()
, coordinates point NOT NULL
);

Using OFFSET 9 LIMIT 1 to pick nr. 10:

DELETE FROM login_coordinates lc
WHERE  login_at <= (
   SELECT login_at
   FROM   login_coordinates
   WHERE  user_id = lc.user_id
   ORDER  BY login_at DESC
   OFFSET 9  -- only found if at least 10 rows present
   LIMIT  1
   )
AND    user_id = 1;

INSERT INTO login_coordinates (user_id, coordinates)
VALUES (1, '(1, 2)');


What is the idiomatic solution in SQL Server for reserving a block of ids for use in a bulk insert? I have a table with an identity column and I want to reserve a block of ids which I can use for bulk inserting, whilst allowing inserts to still happen into that table.

Note this is part of a bulk insert of several tables, where those other tables relate to these ids via an FK. Therefore I need to block them out so I can prepare the relationships beforehand.

I've found a solution which works by taking a lock on the table in a transaction and then does the reseeding (which is pretty fast). But it looks a bit hacky to me - is there a generally accepted pattern for doing this?

create table dbo.test
(
    id bigint not null primary key identity(1,1),
    SomeColumn nvarchar(100) not null
)

Here's the code to block out (make room for) some ids:

declare @numRowsToMakeRoomFor int = 100

BEGIN TRANSACTION;

        SELECT  MAX(Id) FROM dbo.test WITH (  XLOCK, TABLOCK ) -- will exclusively lock the table whilst this tran is in progress,
        --another instance of this query will not be able to pass this line until this instance commits

        --get the next id in the block to reserve
        DECLARE @firstId BIGINT = (SELECT IDENT_CURRENT( 'dbo.test' )  +1);

        --calculate the block range
        DECLARE @lastId BIGINT = @firstId + (@numRowsToMakeRoomFor -1);

        --reseed the table
        DBCC CHECKIDENT ('dbo.test',RESEED, @lastId);

COMMIT TRANSACTION;

select @firstId;

My code is batch processing blocks of data in chunks of about 1000. I have about a billion rows to insert in total. Everything is working fine - the database isn't the bottle neck, the batch processing itself is computationally expensive and requires me to add a couple of servers to run in parallel, so I need to accommodate more than one process "batch inserting" at the same time.

You can use procedure (introduced in SQL Server 2012):
sp_sequence_get_range

To use it you need to create a SEQUENCE object and use it as a default value instead of IDENTITY column.

There is an example:

CREATE SCHEMA Test ;
GO

CREATE SEQUENCE Test.RangeSeq
    AS int
    START WITH 1
    INCREMENT BY 1
    CACHE 10
;

CREATE TABLE Test.ProcessEvents
(
    EventID int PRIMARY KEY CLUSTERED
        DEFAULT (NEXT VALUE FOR Test.RangeSeq),
    EventTime datetime NOT NULL DEFAULT (getdate()),
    EventCode nvarchar(5) NOT NULL,
    Description nvarchar(300) NULL
) ;

DECLARE
   @range_first_value_output sql_variant ;

EXEC sp_sequence_get_range
@sequence_name = N'Test.RangeSeq'
, @range_size = 4
, @range_first_value = @range_first_value_output OUTPUT ;

SELECT @range_first_value_output;

Documentation: sp_sequence_get_range


Identifying which values do NOT match a table row I would like to be able to easily check which unique identifiers do not exist in a table, of those supplied in a query.

To better explain, here's what I would do now, to check which IDs of the list "1, 2, 3, 4" do not exist in a table:

SELECT * FROM dbo."TABLE" WHERE "ID" IN ('1','2','3','4'), let's say the table contains no row with ID 2.
Dump the results into Excel
Run a VLOOKUP on the original list that searches for each list value in the result list.
Any VLOOKUP that results in an #N/A is on a value that did not occur in the table.

I'm thinking there's got to be a better way to do this. I'm looking, ideally, for something like

  List to check -> Query on table to check -> Members of list not in table

Use EXCEPT:

SELECT * FROM
  (values (1),(2),(3),(4)) as T(ID)
EXCEPT
SELECT ID
FROM [TABLE];

See SqlFiddle.

The values constructor will only work on SQL Server 2008 or later. For 2005, use

SELECT 'value'
UNION SELECT 'value'

as detailed in this SO answer.


Use quoted schema in an insert statement executed thorough PreparedStatement in Java I created an unquoted table my_table under a quoted schema "Quoted_User".

When executing the statement

INSERT INTO "Quoted_User".my_table (custom_id) VALUES (DEFAULT)

using the Intellij Oracle browser it works, but when I'm performing the same statement through a PreparedStatement in Java Oracle is complaining that the schema is not existing. Here's the prepared statement Java line:

statement = connection.prepareStatement(insertSQL, new String[]{custom_id});

and here is insertSQL as shown in the inspector:

INSERT INTO \"Quoted_User\".my_table (custom_id) VALUES (DEFAULT)

It's like Oracle is removing the quotes, and I can say this because I noticed the uppercase letters in the error message which are always returned when the object is considered to be case insensitive.

Error returned by Oracle:

java.sql.SQLSyntaxErrorException: ORA-04043: object "QUOTED_USER" does not exist

There should be a way to use quoted schema names with prepared statements I guess.

Thanks for your hints,

Mattia

Are you connecting to the database using the credentials of "Quoted_User"? In this case I see no reason to specify the schema name in your SQL statements. On the contrary, do you have the required privileges on the "Quoted_User" schema?

By the way, using mixed case identifiers is really NOT a good idea.


MySQL Workbench connect to MS Access I have a .mdb file on my computer that I would like to convert into a MySQL Schema and I have found that there is a migration tool within Workbench. However it doesn't seem to allow you to connect to a .mdb instead it looks for a server. How should I either link to my file or host my file easily as I have not done this before.

You can use MySQL Workbench to connect and migrate your schema/data from MS Access to mySQL.
First you need to configure an ODBC Data Source:

Click Start, and then click Control Panel.
In the Control Panel, double-click Administrative Tools.
In the Administrative Tools dialog box, double-click Data Sources (ODBC). The ODBC Data Source Administrator dialog box appears.
Click User DSN, System DSN, or File DSN, depending on the type of data source you want to add. Click Add.
Select the driver that you want to use, and then click Finish or Next.
Follow the instructions and enter the required connection information in any dialog boxes that follow.

Back on MySQL Workbench: Use the menu Database > Migrate and select the ODBC data source you've created to use as source for your job.


How should I model a "communication preferences" field which may require different supporting info for each row? I am building a system which sends information to other systems.  Each client system has its own communication requirements.  For example, a client system might require:

An HTTP request
An HTTPS request
An HTTPS request with some form of authentication
An email
A fax
A physical letter in the mail
A carrier pigeon
Other methods we have yet to discover

Each of these methods will likely require ancillary information.  For example, a fax requires a fax number, while an HTTP request requires a URL.

Currently, we have a simple "client" table that looks something like this:

id           NUMBER
name         VARCHAR
description  VARCHAR

What is the best way to model each client's communication requirements in our database?

The simplest way would be to add a number of nullable columns to this table that store the information in the client's row:

id              NUMBER
name            VARCHAR
description     VARCHAR
comm_pref       VARCHAR  // would store things like 'email', 'fax', 'http', etc.
http_address    VARCHAR
https_address   VARCHAR
https_username  VARCHAR
https_password  VARCHAR
https_api_key   VARCHAR
email_address   VARCHAR
fax_number      VARCHAR
mailing_address VARCHAR
pigeon_name     VARCHAR

But not only does this look ugly, it doesn't scale well if new communication methods are added in the future.

Is there a good way to model this kind of data?

It depends on how much you care. For instance, there is a totally standardized method of doing this with URI. That will support HTTP/HTTPS/Email, and even with maps physical addresses. You can usually store them in the very database with very little work..

scheme:[//[user[:password]@]host[:port]][/path][?query][#fragment]

You can see from that, a lot is optional. They only require

a scheme
a colon (:)
a host

There are lots of schemes specified. However some of your stuff is just goofy. It doesn't serialize requests, or address non-unique things like pigeons. What would that even look like? Globally addressable without a centralized authority (in this case a Global Pigeon Addressing Committee) is very difficult. You have to have a method of resolving conflicts (consensus).

Shy of that, you'll have to normalize the data and figure out what you really need to support. There is no database that is going to provide infinite abstractions without assumptions of use. You may as well just store your stuff in bytes.

Before you go down the EAV route, check to see if your database support
 binary JSON.


Deleting Data Takes A Long Time I want to delete data from a table that contains millions of rows.

As a test I tried to delete 1 row but it takes over 2mins.

DELETE TOP (1)
    FROM [geneva.reports.model].PriceDiffs
    WHERE UserID = 384166

There is no index on UserID but there are 3 non-clustered indexes. I tries disabling them but they failed due to lock timeout.

The actual query plan is uploaded to Paste the Plan.

I can think of two possible explanations for why you experience poor performance with that query.

The first relates to blocking. Have you checked the wait events while the query is running? If it is blocked by another session and waiting for a lock then you don't have a performance problem. Instead, you have a concurrency problem.

The second relates to the table and index structure on the table that you are deleting from. The PriceDiffs table has 194807000 rows. If you don't have an index that can seek on the UserID column it could take a long time to find the single row to delete. There's also a scan on the PriceDifferenceRecords table which I assume is there to enforce a constraint or a foreign key, but that table only has 15843 rows.

You might be wondering why the query takes two minutes to run when it has a relatively low estimated subtree cost of 0.0569578. This is probably due to the row goal introduced to the query by your use of TOP 1. SQL Server scans the IX_PriceDiffs_PriceDate index to find a matching row but the filter is implemented as predicate instead of a seek predicate. I assume this is because UserID is not a key column. Thus, SQL Server scans the index in order until it finds a row where UserID = 384166. If you are really lucky the first matching row could be in the first page of the index. If you are really unlucky the first matching row could be in the last page of the index, so your DELETE query would scan the entire table to delete just one row.

It's worth noting that deleting just one row from a table often isn't a good way to test performance when you need to delete lots of rows. For a starting point, consider creating an index on UserId and deleting in batches.


How to solve "Can't connect to local MySQL server through socket" error? I kept getting this following error every time I tried to restart MySql. It was working fine before I changed the default data directory.

  ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib                                                                                /mysql/mysql.sock' (111)

I know there are other solutions posted by others but none of them worked for me.

Check the folowing

First check if another instance is runnig in the same machine

ps ax | grep mysql
Then, check if your file my.cnf (probably in /etc/mysql/ folder) is correctly configured with

[mysqld]
datadir = /var/lib/mysql/
socket = /var/lib/mysql/mysql.sock

Next, you have changed the default data directory, did you gave the right permissions to the new directory now

 chown mysql:mysql -R /var/lib/mysql/*
 chmod 755 -R /var/lib/mysql/*

After changing the permission restart the service and check

service mysql restart


Mongo - findAndModify has to be executed twice to take effect I have a collection campaign like this:
{
        "_id" : ObjectId("5ec12c501bd23866ef315a85"),
        "schemaVersion" : "1",
        "internalVersion" : NumberLong(56),
        "code" : "CK",
        "siteCampaigns" : [
                {
                        "_id" : ObjectId("5f3689274d42720edb5be328"),
                        "siteCode" : "IT",
                        "startDate" : ISODate("2020-02-10T06:00:00Z"),
                        "endDate" : ISODate("2020-02-20T06:00:00Z"),
                        "status" : "SUBSCRIBED"
                },
                {
                        "_id" : ObjectId("5f3689274d42720edb5be327"),
                        "siteCode" : "ES",
                        "startDate" : ISODate("2020-02-10T06:00:00Z"),
                        "endDate" : ISODate("2020-02-20T06:00:00Z"),
                        "status" : "UNSUBSCRIBED"
                },
                ...
            ]
}

Now I want to find and modify the element in the siteCampaigns.
I use this query:
db.campaign.findAndModify({
    query: {
        code: "CK", 'siteCampaigns.siteCode': "IT"
    },
    update: {
        "$set": {
            'siteCampaigns.$.endDate': ISODate("2020-08-30T00:00:00Z")
        }
    },
    upsert:false
})

I execute it for the first time, I find the document, no error, but the field is not modified; when I repeat it, it finds the same document, now updated.
I always have to execute it twice to take effect. Why? I use mongo shell.

Find the doc..

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

About new:

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

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


Grant User permission to view XE results via sys.fn_xe_file_target_read_file I need to grant a user (MyDomain\JohnSmith) permission to view one particular XE session results using sys.fn_xe_file_target_read_file

I thought the best way to do this would be to encapsulate the logic within a stored procedure in a database which John Smith uses called MyDb and then grant him access to the stored procedure.

I have solved this so far but it means using impersonation of the dbo user within the stored procedure and setting IS_TRUSTWORTHY on which some Googling suggests is a less secure method and that signing a stored procedure is the most secure way to achieve this.

Going the the signing method, I have hit some problems as below:

First of all, I create a certificate in the database:

USE MyDb
GO

CREATE CERTIFICATE [CodeSigningCertificate]
ENCRYPTION BY PASSWORD = 'SuperSecretPassword'
WITH EXPIRY_DATE = '2099-01-01',
SUBJECT = 'Code Signing Cert'

then I create my stored procedure which has the logic

CREATE PROCEDURE MySchema.MyProc
AS
BEGIN

    DECLARE @TraceFilePath NVARCHAR(256) = 'Path/To/My/File*.xel'

    SELECT  CONVERT(XML,event_data).value('(/event/@timestamp)[1]', 'NVARCHAR(MAX)' ) AS [TimeStamp],
            CONVERT(XML,event_data).value('(/event/action[@name="database_name"]/value)[1]', 'NVARCHAR(MAX)' ) AS [Database_name],
            CONVERT(XML,event_data).value('(/event/data[@name="statement"]/value)[1]', 'NVARCHAR(MAX)' ) AS [Statement],
            CONVERT(XML,event_data).value('(/event/action[@name="sql_text"]/value)[1]', 'NVARCHAR(MAX)' ) AS SQL_Text,
            CONVERT(XML,event_data).value('(/event/action[@name="username"]/value)[1]', 'NVARCHAR(MAX)' ) AS Username,
            CONVERT(XML,event_data).value('(/event/action[@name="client_hostname"]/value)[1]', 'NVARCHAR(MAX)' ) AS Client_Hostname,
            CONVERT(XML,event_data).value('(/event/action[@name="client_app_name"]/value)[1]', 'NVARCHAR(MAX)' ) AS Client_app_name
    INTO    #Results
    FROM    sys.fn_xe_file_target_read_file (@TraceFilePath, NULL, NULL, NULL )

    SELECT  *
    FROM    #Results
    WHERE   Statement LIKE '%some value%'
    ORDER BY SQL_Text
END

Next, I sign the stored procedure

ADD SIGNATURE TO MySchema.MyProc
BY CERTIFICATE [CodeSigningCertificate]
WITH PASSWORD = 'SuperSecretPassword';

Then I create a user which uses the certificate

CREATE USER [CodeSigningUser] FROM CERTIFICATE [CodeSigningCertificate];

At this point, I need to grant the underlying privilege that the is required to run sys.fn_xe_file_target_read_file which is GRANT VIEW SERVER STATE

GRANT VIEW SERVER STATE TO [CodeSigningUser]

but when I try to do this, I get

"Msg 4621, Level 16, State 10, Line 44
Permissions at the server scope can only be granted when the current database is master"

which makes perfect sense as it is a server level permission that I am trying to grant to a database level principal

I have tried creating a login for the certificate (as I can grant VIEW SERVER STATE to a login) :

CREATE LOGIN [CodeSigningLogin] FROM CERTIFICATE [CodeSigningCertificate];

but I get the error

Msg 15151, Level 16, State 1, Line 40
Cannot find the certificate 'CodeSigningCertificate', because it does not exist or you do not have permission.

Which, again makes sense as the certificate is in a user database

I can create the certificate in the master database which allows me to create the login from the certificate but then I can't sign the stored procedure using that certificate as the certificate resides in a different database to the stored procedure.

The only way I can think of to accomplish this is to create the Stored Procedure in the master database and then create the certificate, also in the master database, create the login and assign VIEW SERVER STATE to the login.

Is there a way I can keep my stored procedure in MyDb and have a user MyDomain\JohnSmith execute it to be able to see the XE Session Results?

I have managed to do this now thanks to this article the missing piece of the puzzle was to copy the certificate to the master database

DECLARE @Cert NVARCHAR(4000) =
         CONVERT(NVARCHAR(4000),
                 CERTENCODED(CERT_ID(N'CodeSigningCertificate')),
                 1);

EXEC (N'USE [master];
CREATE CERTIFICATE [CodeSigningCertificate]
FROM BINARY = ' + @Cert);


Relational Model Design - Entity with one attribute I am trying to design a database relational model for an assignment. First, I design the ER diagram, and then the relational model with tables (schemas).

Here's my question: Is it possible for an entity to have only one attribute?

Yes, but.

If an entity only has one attribute, then that attribute must serve as an identifier for the entity.  Typically, if only one attribute is found for an entity in the given data stream, then another attribute is "invented" for purposes of identifying.  It's often called "id", it's often an integer, and it's often populated using the autonumber feature of the database system.

When you are doing analysis, it's important to distinguish between the features that were given to you in the problem statement, and the features you added in order to make sense out of the problem.


Printing comments or statements when executing script to a output file using TOAD/ORACLE Coming from a SQL SSMS environment, I would like to duplicate their Print Command which allows you to print commands or variables while executing a query. I am looking for this feature to help identify particular sections of the script during the execution.

For instance we have a .sql script that runs in the evening that checks for discrepancies on particular inventory numbers. These inventory numbers have lets say a store_id of 1, 2, 3, 4, 5.

The script has a select statement attached to each of these store_id. I would like to be able to place a PRINT statement for the select statement of Store_ID 1 so I can see the output.txt file and see the heading 'Store_id 1" then the inventory numbers, then see heading "Store_id 2" etc...

in MSSQL SSMS it would like something like this

PRINT 'Searching Inventory Discrepancies at Store 1'
Select * from table1 where store_id = '1' etc...

PRINT 'Searching Inventory Discrepancies at Store 2'
Select * from table1 where store_id = '2' etc...

If I need to clarify please advise, thank you!

The syntax below will allow you to place comments either before or after your query, so that the comments display on the output.txt file:

BEGIN
    DBMS_OUTPUT.PUT_LINE('TEXT GOES HERE ');
END;
/

You should also issue SET SERVEROUTPUT ON to ensure the PUT_LINE command will work.

Here are some commands you can use while using DBMS_OUTPUT.PUT_LINE to control the formatting of the output:

SET UNDERLINE OFF
SET LINESIZE 32767
SET PAGES 10000
SET TRIMSPOOL ON
SET TRIMOUT ON
SET FEEDBACK OFF
SET HEADING ON
SET NEWPAGE 0
SET HEADSEP OFF
SET WRAP OFF
SET TERMOUT OFF

In my case, I had to use particular settings to allow my output to stop word wrapping and causing the formatting to get thrown off.

SET LINESIZE 32767
SET HEADING ON
SET HEADSEP OFF
SET WRAP OFF


Do Availability Groups support Active/Passive? I am looking into the licensing of SQL Server 2012 when using Availability Groups with 2 servers.
It is not clear to me if Availability Groups are only available as Active/Active and not Active/Passive.
For reference I found a couple paragraphs in the SQL Server 2012 Licensing Reference Guide but it doesn't mention passive servers with respect to Always On.

Licensing SQL Server for High Availability SQL Server software can be
configured so that if one server fails, its processing will be picked
up, recovered and continued by another server. All editions of SQL
Server 2012 provide basic high availability features including backup
log shipping, database mirroring and two-node failover clustering.
Advanced (AlwaysOn) high availability features in SQL Server 2012
Enterprise Edition include enhanced support for multiple, active
(readable) secondary servers and support for multi-site failover
clustering. Log shipping and database mirroring take place at the
database level, whereas failover clustering takes place at the SQL
Server instance level.
AlwaysOn Availability Groups New for the SQL Server 2012 Enterprise
Edition, AlwaysOn Availability Groups enable customers to configure
multiple databases that will failover as a unit, with support for up
to four active secondary servers and two synchronous secondary
servers. The ability to use secondary servers for more than just
passive failover support can improve the performance of primary,
reporting and backup workloads due to better balancing of workloads
across instances, helping to provide better return on hardware
investment. Note: When secondary servers are actively used to support
these additional workload scenarios—that is, when the servers used for
failover purposes are no longer truly passive—they must be fully
licensed accordingly.

If you are talking about AlwaysON Availability Group in that case as long as secondary replica(s) are used strictly for Disaster Recovery or StandBy database then you do not need to license those replica in terms of SQL Server Licensing.

Few weeks ago, I had similar question related to AlwaysON availability group and here is the official answer I got from our MS Licensing Rep.

"When the secondary server does any work at all, like taking requests or running backups off of it, or any of the readable features of AlwaysOn are enabled then it must be fully licensed.

If its sole purpose of replica is for fail-over and none of the readable features of AlwaysOnare enabled, then the fail-over right provided below will cover that server from a licensing standpoint and a second license is not required."

In terms AlwaysON FCSI (failover clustering) licensing stays the same. You only license active node. If you use log shipping for DR you do not license your DR server.

If you are in North America region, you can call Microsoft 1-800-426-9400, Monday through Friday, 6:00 A.M. to 6:00 P.M. (Pacific Time)   to confirm this.


How can I disconnect clients from MySQL? I need an efficient way to disconnect all clients with a given username from MySQL.  I thought about changing the users password but I think that is only checked when the connection is made.

Ideas?

You could use "SQL to SQL" method below (just pass in extra connection options to mysql client as needed):

shell> mysql -NBe "SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE user = 'some_username';" | mysql -vv

Note: This works with MySQL 5.1 and 5.5. This would have to be implemented differently for older MySQL versions as information_schema does not have the processlist table.

Options used:

-N means that you do not want to get column names back.
-B puts it into batch mode, so that you do not get MySQL's table layout.
-e executes the following statement.
-v controls the verbosity, could be used up to three times.

Explanation of how it works:

First the KILL statements are generated along with IDs.

shell> mysql -NBe "SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE user = 'some_username';"

Sample output:

KILL 1061;
KILL 1059;
KILL 1057;

Then those statements are executed.

shell> mysql -NBe "SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE user = 'some_username';" | mysql -vv

Sample output:

--------------
KILL 1061
--------------

Query OK, 0 rows affected

--------------
KILL 1059
--------------

Query OK, 0 rows affected

--------------
KILL 1057
--------------

Query OK, 0 rows affected


How to get records from two Dates in MySql if stored in String Format? If you store your dates in string format in mySql.How to get the Records

You take the wrong way.
You should store the date in date format, not in string


One record, multiple updates in one query (MySQL) I run into a problem for which I didn't find an answer anywhere: If I have a table in which one specific value(here amount) from multiple records can add into a target record(from the same table), can I do it in a single query? This query below works, just that it only works for the first match(?!).

UPDATE test AS t1
LEFT JOIN test AS t2 ON t1.target = t2.obj_id
SET t2.amount = t2.amount + t1.amount, t1.amount = 0
WHERE t1.amount > 0 AND strcmp(t1.target, "null") <> 0;

I might be missing something here, but maybe it only works for the first match because it sets t1.amount = 0, which means that record is excluded from a repeated invocation of the same command (whose WHERE clause requires t1.amount > 0)?


Tracing failed login attempts in Oracle I have some users who are logging in with a wrong password and their accounts get locked.

Now I need to find the source of the failed login attempt. The machine, username etc.

Is it possible to find this?

I did try

select ntimestamp#, userid, userhost, spare1, comment$text from sys.aud$ where returncode=1017 order by 1;

and also,

select OS_USERNAME,USERNAME,USERHOST,to_char(timestamp,'MM-DD-YYYY HH24:MI:SS'), returncode
from dba_audit_trail
where returncode > 0

Both the above queries show up results but I'm not sure if the results are the ones which actually gets locked.

Server - RHEL
DB - Oracle 12c

You are on the right way.
Column RETURNCODE in DBA_AUDIT_TRAIL or RETURN_CODE in UNIFIED_AUDIT_TRAIL view.

http://docs.oracle.com/database/121/REFRN/GUID-A9993FAC-12D3-4725-A37D-938CC32D74CC.htm#REFRN23023

  This view is populated only in an Oracle Database where unified
  auditing is not enabled. When unified auditing is enabled in Oracle
  Database, the audit records are populated in the new audit trail and
  can be viewed from UNIFIED_AUDIT_TRAIL.

Oracle documentation is always a very good source of information.


This is a simple task for a ranking function:

WITH cte AS
 (
   SELECT  prodMPEntity,
       prodMPLiquid,
       prodMPNormMonth,
       prodMPProdDate,
       ROW_NUMBER() -- assign a rank based on your description
       OVER (PARTITION BY prodMPEntity
             ORDER BY prodMPLiquid DESC, prodMPProdDate) AS rn
       FROM    dbo.prodMPRunningTotals
       WHERE        (prodMPNormMonth <= 6)
 )
SELECT   prodMPEntity,
    prodMPLiquid,
    prodMPNormMonth,
    prodMPProdDate
FROM cte
WHERE rn = 1 -- return the row which is ranked 1st


ORA-00227: corrupt block detected in control file I am getting the following error while starting the sqlplus (I am using Oracle XE 11.2.0)

ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0

So, I did the following..

SQLPLUS> CONN AS sysdba

and then..

SQLPLUS> alter database mount

I am getting the following error for the above command.

alter database mount
*
ERROR at line 1:
ORA-00227: corrupt block detected in control file: (block 3, # blocks 1)
ORA-00202: control file: 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF'

I have the following files along with CONTROL.DBF in the same folder.

CONTROL.DBF
SYSAUX.DBF
SYSTEM.DBF
TEMP.DBF
UNDOTBS1.DBF
USERS.DBF

I also tried disk check on the C:\ drive, and Windows said it repaired the CONTROL.DBF file but still, I am getting the same error.

Thanks in advance. Hope you will reply as soon as possible.

Move control.dbf to control.dbf.damaged.20140706

connect / as sysdba
show parameter control

Verify here you have had only a single control file and not two or three of them.
If you have had more than one control file, then this instruction is not for you.

STARTUP FORCE NOMOUNT;

CREATE CONTROLFILE DATABASE "XE" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\log_file_1' SIZE 50M,
GROUP 2 'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\log_file_2' SIZE 50M
DATAFILE
'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF',
'C:\ORACLEXE\ORADATA\XE\UNDO.DBF',
'C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF',
'C:\ORACLEXE\ORADATA\XE\USERS.DBF'
CHARACTER SET AL32UTF8
;
ALTER DATABASE OPEN;


Agent Xps getting disabled automatically In one of SQL Server 2012 server, agent Xps is getting disabled automatically. There is no set pattern for the same and the issue started all of a sudden. Through profiler it was found that application named SQL AGent- Enable\disable Agent Xps is disabling the XPs. And the later is enabled by SQL Agent-Initial BootProbe.

I had a similar problem that I just resolved. Turned out, a new group policy had been applied to our server. This group policy disallowed domain users access to the D:, E:, & F: drives. Our SQL Server Agent was configured to write out its error log to the E: drive. As the Log On account for the SQL Server Agent service was a domain based account, SQL Server Agent was now unable to write to its log file. It died immediately at service start up. It was very frustrating in that we saw nothing in the Windows Event Viewer other than that the service started up and then stopped.