Explaining what the OR operator does in the following code I ran into the following SQL code which (successfully) outputs a list of parents and the age of their youngest child:
SELECT parents.name AS name, MIN(children.age) AS age FROM people children
INNER JOIN people parents ON (parents.id = children.fatherId OR parents.id = children.motherId)
GROUP BY parents.id

The code self joins a table named "people" on itself. I just wanted to ask how does the OR operator work here? I know OR as a logical operator but here it seems it does something else. It takes two arguments and just joins on both of them. What does it have to do with logical OR?

Another way you can look at it, which might help you conceptualize, is the OR of an ON clause is like using a UNION ALL with the query repeated and only leveraging one predicate of each OR case like so:
-- Gets the fathers
SELECT parents.name AS name, MIN(children.age) AS age FROM people children
INNER JOIN people parents ON parents.id = children.fatherId
GROUP BY parents.id

UNION ALL

-- Gets the mothers
SELECT parents.name AS name, MIN(children.age) AS age FROM people children
INNER JOIN people parents ON parents.id = children.motherId
GROUP BY parents.id


how to write queries properly inside of a postgres RLS policy I'm trying to do a single implicit JOIN inside of an RLS expression. I get a syntax error and have a "hacky" solution using a subquery, but would love to know if there is a better way.

So assuming two tables, projects and objects:

CREATE TABLE app_pub.projects (
  id uuid DEFAULT public.uuid_generate_v4 () NOT NULL,
  owner_id uuid
);

CREATE TABLE app_pub.objects (
  id uuid DEFAULT public.uuid_generate_v4 () NOT NULL,
  project_id uuid
);

The ownership is expressed on the project.owner_id, but the policy of interest will check against objects.project_id. The procedure app_pub.get_current_role_ids () returns an array of role ids for access.

Specifically this query's boolean result tells us if the user has access:

SELECT (p.owner_id = ANY (app_pub.get_current_role_ids ()))
    FROM app_pub.projects p, objects o
    WHERE (p.id = o.project_id)

I'd like to write a policy like so:

CREATE POLICY authenticated_can_select_on_objects
ON app_pub.objects
FOR SELECT
TO authenticated
USING (
SELECT (p.owner_id = ANY (app_pub.get_current_role_ids ()))
    FROM app_pub.projects p
    WHERE (p.id = project_id)
);

But this obviously fails due to syntax error, hence my question...

So I do have my "hacky" solution is to use a subquery and do TRUE IN (subquery):

CREATE POLICY authenticated_can_select_on_objects
ON app_pub.objects
FOR SELECT
TO authenticated
USING (
    (TRUE IN (
        SELECT (p.owner_id = ANY (app_pub.get_current_role_ids ()))
            FROM app_pub.projects p
            WHERE (p.id = project_id)
        )
    )
);

And YaY "it works!", with this TRUE IN (subquery) which feels wrong. I know I could write a procedure and use that, but was hoping to do something inline in hopes the query planner would be happier.

So, with all that context:

is this "hacky" solution the only/best way?
can I do a clean select or join like my first attempt was trying to accomplish?

The USING clause of the CREATE POLICY command expects an expression inside the parentheses. An SQL query, by itself, is not an expression, but you can make it into one by writing it as a scalar subquery. So you just need another pair of parentheses:

CREATE POLICY ..
USING (
  (SELECT p.owner_id = ANY (app_pub.get_current_role_ids ())
   FROM app_pub.projects p
   WHERE p.id = project_id)
);


How to match similar records using multiple columns Given a GUID for an event, say logins, how might I search for similar or dissimilar events, utilizing the other columns data?

For example:
Find me similar logins to [GUID] {Server:"s123", State="NY", Status="503", etc...}

OR

Find me logins the opposite of that example given?

I feel there must been a name for this, but Full-Text, or "%Like%" syntax I do not believe would be sufficient.

Is indexing the data with solr or elasticsearch the solution?

The simplistic method is to count the number of columns that match:

select
 n.id as needle_id,
 h.id as haystack_id,
 case when n.name = h.name then 1 else 0 end
 + case when n.phone = h.phone then 1 else 0 end as relevance
from
 needles n
join
 haystack h
on
 n.name = h.name
 or n.phone = h.phone
order by
 relevance desc;

where "name" and "phone" are columns in your tables. You can use the same table as both the needle and haystack if you want (to find duplicates in a table).

http://blog.databasepatterns.com/2014/12/fuzzy-record-matching-in-sql-p1.html

The more correct and much more complicated method is to use Probabilistic Record Linkage. There's an R package for it (recordlinkage)


Selecting Only One Record with Most Recent Date Using Table Join I have a list of 463 CARRIER_CD.  I am attempting to retrieve the most current BILL_DT for each of the 463 CARRIER_CD.

I have uploaded the 463 unique CARRIER_CD to a database named edw_sbx_bt the table name is jjenkins_carriers.

The BILL_DT is in a database named edw_ar_fl and table named ARTBASE.  I wrote the query below in hopes of returning a single row of data including CARRIER_CD, BILL_DT, and BILL_AMT for the most recent BILL_DT associated with each of the unique CARRIER_CD.  However, the query returns 408+ million rows.  Any help would be appreciated.

SELECT edw_ar_fl.ARTBASE.CARRIER_CD,
    edw_ar_fl.ARTBASE.BILL_DT,
    edw_ar_fl.ARTBASE.BILL_AMT
FROM   edw_ar_fl.ARTBASE
       JOIN edw_sbx_bt.jjenkins_carriers
         ON edw_ar_fl.ARTBASE.CARRIER_CD = edw_sbx_bt.jjenkins_carriers.CARRIER_CD
AND edw_ar_fl.ARTBASE.BILL_DT =
     (select max(edw_ar_fl.ARTBASE.BILL_DT)
      where edw_ar_fl.ARTBASE.CARRIER_CD = edw_sbx_bt.jjenkins_carriers.CARRIER_CD)

The simplest way to select the last row (by bill_dt) in each group of rows with the same carrier_cd in Teradata would probably be using QUALIFY:

QUALIFY 1 = ROW_NUMBER() OVER (PARTITION BY carrier_cd ORDER BY bill_dt DESC)

If you're sure there are no ties (i.e. there's only one row with bill_dt = MAX(bill_dt) for each carrier_cd), you could calculate the max date for each carrier you're interested in:

sel carrier_cd, max(bill_dt) as max_bill_dt
from ARTBASE join CARRIERS on ARTBASE.carrier_cd = CARRIERS.carrier_cd
group by 1

...and then join it back to the original table:

sel ARTBASE.carrier_cd, ARTBASE.bill_dt, ARTBASE.bill_amt
from ARTBASE
join ( the above select here ) CARRIERS_STAT
  on ARTBASE.bill_dt = CARRIERS_STAT.max_bill_dt

In general I try to avoid the correlated subqueries, especially in a join condition. I'm not even sure how it works at all in the example you provided.


PostgreSQL: Index Trigger / Hook? For Synonyms Is it possible, in PostgreSQL, to add a trigger to modify data prior to it going into a full text index?

PostgreSQL requires using files to add custom dictionaries for things like stop words, synonyms, etc. However, on most managed database hosting file access is not allowed. What would be best is if I could use a table (such as a spaced single or double column) instead of a file dictionary to supply the list of synonyms, stop words, etc. but after a lot of searching it does not appear that is possible.

Another path I tried to look into was to populate a table with synonyms then export it to the file system, however PostgreSQL requires a full path to the file. On managed hosting you cannot provide a full path if you do not know the the paths to begin with.

So now I am down to seeing if there is some way I can "hook" into the pre-index process and modify the data myself prior to it being added to an index. Note that I thought about using a trigger and modifying the data directly but that would also modify the data going into the stored row's field - something I don't want to do. What I am looking for is a way to leave the original data / field intact and being entered into a field while modifying the information prior to it being indexed. I thought about adding an additional column to the table with the modified data, however that can get very messy and may require a lot of extra columns for larger data sets. Another option is using materialized views or inherited/child tables but, once again, would be a messy and difficult to manage outcome.

TLDR: I am asking if there is any viable way to define stop words, synonyms, etc. without having to use files (so that it can be used on managed systems that do not provide file access)?

It's all preprocessing. PostgreSQL can do it's thing. You can do yours.

CREATE INDEX ON myTable
  USING gin ( myTransform(x) );

SELECT *
FROM myTable
WHERE 'foo & bar' @@ myTransform(x);

Exercise left to the OP,

CREATE FUNCTION myTransform ( x string )
RETURNS tsvector AS $$
  BEGIN
    -- modify x
    RETURN x::tsvector;
  END;
$$ LANGUAGE plpgsql
IMMUTABLE;


Composite Primary Key on partitioned tables, and Foreign Keys We have a database where several tables are partitioned. The primary key (PK for brevity) for these tables is of the form

transaction_date, id

Where transaction_date is the partitioning key, and id is a unique integer column. If we were not partitioning the table, then we would only use id as the PK.

We have several other tables which need foreign keys (FKs) to reference these partitioned tables. I cannot decide whether to use composite FKs, referencing both columns of the PK, or single column FKs, referencing the id column supported by a unique index.

Are there any advantages/disadvantages of either approach? So far the only factors I can think of (and I may even be wrong on these) are:

Single column FK: Reduces app development complexity.
Composite FK: Allows partition elimination when joining on FK columns.

ALL unique constraints involved in a partitioning scenario MUST have the partition column defined as part of the base index definition (subset of the index key) - (not merely an included column). In other words, you will not be able to create a unique constraint on just the ID column in this scenario.

Set up a partition function and scheme.

CREATE PARTITION FUNCTION [PF_dbo_Test] (DATE) AS RANGE RIGHT
FOR VALUES ('2018-01-01','2018-02-01','2081-03-01')
GO

CREATE PARTITION SCHEME [PS_dbo_Test] AS PARTITION [PF_dbo_test]
TO ([PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY])

Create a table with a PRIMARY KEY that references the partition scheme.

IF OBJECT_ID('[dbo].[Test]') IS NOT NULL
    DROP TABLE [dbo].[Test]
GO

CREATE TABLE [dbo].[Test] (
    [TransactionDate] DATE
    ,[ID] INT CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([TransactionDate] ASC)
    ) ON PS_dbo_Test (TransactionDate)

Now, try to create a unique constraint on the partitioned table without specifying the partition column.

CREATE UNIQUE NONCLUSTERED INDEX [UX_Test] ON [dbo].[Test] ([ID] ASC)
    WITH (
            PAD_INDEX = OFF
            ,STATISTICS_NORECOMPUTE = OFF
            ,SORT_IN_TEMPDB = OFF
            ,IGNORE_DUP_KEY = OFF
            ,DROP_EXISTING = OFF
            ,ONLINE = OFF
            ,ALLOW_ROW_LOCKS = ON
            ,ALLOW_PAGE_LOCKS = ON
            )

  Msg 1908, Level 16, State 1, Line 17 Column 'TransactionDate' is
  partitioning column of the index 'UX_Test'. Partition columns for a
  unique index must be a subset of the index key.

So, when you ask the question:

  I cannot decide whether to use composite FKs, referencing both columns
  of the PK, or single column FKs, referencing the id column supported
  by a unique index.

I'm afraid SQL Server has decided for you. You will have to define your FK's with both TransactionDate AND ID.


mongodb compact does nothing - no entry in mongodb.log and nothing in db.currentOp() We have space problems. The actual data size is only 5 GB, but disk size is 15 GB because of deleted documents. MongoDB (3.0.6) runs in docker container where we can't extend volume.

# tail -f mongod.log
2016-04-26T14:43:59.631+0000 I STORAGE  [FileAllocator] allocating new datafile /data/work/mongodb/data/opWNFbyNlGQ6xShT.11, filling with zeroes...
2016-04-26T14:43:59.632+0000 I STORAGE  [FileAllocator] FileAllocator: posix_fallocate failed: errno:28 No space left on device falling back
2016-04-26T14:43:59.632+0000 I STORAGE  [FileAllocator] error: failed to allocate new file: /data/work/mongodb/data/opWNFbyNlGQ6xShT.11 size: 2146435072 failure creating new datafile; lseek failed for fd 40 with errno: errno:2 No s^C

disk

/dev/vdh                            16G   15G  553M  97% /data/a6a186a3-dc98-4f47-83f3-96e953c18b69

repairDatabase doesn't work because no scratch space

> db.runCommand( { repairDatabase: 1 } );
{
        "ok" : 0,
        "errmsg" : "Cannot repair database opWNFbyNlGQ6xShT having size: 14975762432 (bytes) because free disk space is: 578928640 (bytes)",
        "code" : 14031
}

So I tried

> db.runCommand ( { compact: 'accounts', paddingFactor: 1.1 } )
{ "ok" : 1 }

I don't see nothing in progress

> db.currentOp()
{ "inprog" : [ ] }
> db.currentOp()
{ "inprog" : [ ] }
> db.currentOp()
{ "inprog" : [ ] }

There is no entry in mongodb.log about the compact operation

  You may view the intermediate progress either by viewing the mongod
  log file or by running the db.currentOp() in another shell instance.

compact

Why compact fails?

is our only solution to do dump/drop/restore?

Since you are using the MMAP storage engine there are several factors to be aware of:

The compact command only defragments data files & indexes in MMAP; it does not release unused space to the operating system. Running compact can still be useful to reduce fragmentation and encourage free space reuse, but will not help if your disk space is critically low.
The compact command in MMAP requires additional disk space (up to 2GB) during the compact operation.
As at MongoDB 3.2, compact is a blocking command (database-level lock for the collection being compacted).

The approach to reclaiming preallocated space in MMAPv1 is rebuilding the database either through a resync (recommended for replica sets) or a repair (the only option for standalone deployments).

  repairDatabase doesn't work because no scratch space

You can use the command line option --repairpath or the config file option storage.repairPath to provide extra storage (i.e. a second volume) for working space during the repair process:

mongod --repair --repairpath /path/to/extraspace --dbpath ...

After the repair process completes, the rebuilt data files will reside in the dbpath and the repairpath should be empty.

WiredTiger Storage Engine

If you have the option of upgrading to the WiredTiger storage engine in future, it has many improvements over MMAP:

Data files and indexes are compressed by default (with configurable compression options that can be set globally or per collection).
The compact command can release storage to the O/S (MongoDB 3.2.3+).
Minimal additional space should be required in order to run the compact command.


Create spfile from pfile I'm having a problem in creating spfile from pfile.I think I don't have permission to write on Oracle Home. I'm using Windows 8

SQL> startup nomount pfile='initlittle2.ora.txt';
ORACLE instance started.

Total System Global Area  644468736 bytes
Fixed Size                  1376520 bytes
Variable Size             192941816 bytes
Database Buffers          444596224 bytes
Redo Buffers                5554176 bytes
SQL> create spfile from pfile;
create spfile from pfile
*
ERROR at line 1:
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file
'A:\ORACLEBASE\PRODUCT\11.2.0\OLTP2\DATABASE\INITLITTLE2.ORA'

Any workaround for my problem?

It appears that you created your pfile with a double extension .ora.txt (Windows Notepad sometimes does this).

You can try to:

rename the pfile removing the .txt extension, or
specify the full path to your parameter file in the CREATE statement, e.g. CREATE SPFILE FROM PFILE = 'c:\yourpath\initlittle2.ora.txt';


Setting prompt for MariaDB 10.2 in my.cnf? I'd like to set the default prompt on MariaDB across all our database servers for everyone. I assume this would be best done in my.cnf. I can set the prompt in a session, but when I try to add something like prompt=name[/d]>/_ in my.cnf, MariaDB fails to start an complains about it.

What is the proper way to do this?

across all our database servers for everyone

This is a function of the client. You can't set it on the server. MySQL ships with a client, and a server. The client determines the prompt. There is nothing you can do on the server to fix that. The client could be something totally different that has for its prompt

L33tHAX\###->#

You can see the code here construct_prompt(). Nothing there hits the server. That said, it gets set here in the main

default_prompt = my_strdup(getenv("MYSQL_PS1") ?
                 getenv("MYSQL_PS1") :
                 "\\N [\\d]> ",MYF(MY_WME));
current_prompt = my_strdup(default_prompt,MYF(MY_WME));

So it would seem you could set it through the MYSQL_PS1 environmental variable.


Amazon RDS remove read-only and allow defining table structures I have set up an Amazon RDS Mysql instance and it is successful.
I am trying to create some tables but I am getting this error:

#1290 - The MySQL server is running with the --read-only option so it cannot execute this statement

I have tried to set global to read-only = 0 but my user does not have privileges.
I have tried googling for a solution to no avail. (I am pretty new to RDS)

Update
I do not have a create read replica option on my actions

In my case, I ran into this problem on one of my stacks—but not the first time the stack was built. It seems I was referencing an AWS::RDS::DBInstance's DBInstance.Endpoint.Address, but that instance was actually the master member of an Aurora RDS database cluster.

For some reason, accessing the instance directly sometimes caused the --read-only error. I updated my template to output an AWS::RDS::DBCluster's DBCluster.Endpoint.Address instead (so my app would point at the cluster endpoint, and not the instance directly), and everything worked fine.

It was puzzling to me why this happened sometimes when I built a stack and not others... but the lesson is to make sure you use the cluster endpoint if you have a multiple-member Aurora/RDS cluster, and not just a single instance.


How to find the current Log Sequence Number (LSN) of a database that is in Restoring state? I removed one of my databases from the availability group on my secondary replica in my AlwaysOn group for an experiment. I want to add it back, but I cannot do so because the databases needs to be rolled forward to match what is on the primary (there was a transaction log backup taken on the primary, I guess that clears the log on the primary and that makes it impossible to join the database back to the availability group on the secondary without restoring from the transaction log).

The database is now in the "Restoring" state. I have the transaction backup files on the Primary replica and I can copy over the files needed to perform a transaction backup restore on the secondary to roll it forward. I tried a couple of files and I get the error below

I understand that this means my log back up is older than the current state of the database. The current state of the database seems to have the last LSN to be the second number in the error message.
Question: How can I query the database to find out the second number LSN 956952100000057? Is this error message the only place where I can see it and Follow up: Is trial and error of the only way of figuring out which log backup file the correct one?

If you restore the log backup with "HeaderOnly", you will have the first and last LSN include in this backup.

You want to find the log backup that includes the LSN mentioned in your error message (where the DB is at).

Now if you want to find this LSN, you will have to query the sys.master_files DMV and look at the redo_start_lsn column.

Select redo_start_lsn from sys.master_files where database_id=DB_ID('yourDB') and type_desc = 'LOG'


Why do string functions on SQL based database servers start at position 1 instead of 0? This has always bothered me. It seems that string functions in SQL based servers always start at position 1 (at least this is the case with MySQL, SQL Server, Oracle and Postgres). For example the following query would be used to select the first letter of a column called first_name in the names database:

SELECT SUBSTRING(first_name,1,1) FROM names;

Why does the position for string functions not start at 0 as is the norm in nearly every programming language?

I'm looking for more than just this is the ANSI standard. Why is the standard?

EDIT: Okay, so 0 is not the "norm in nearly every programming language" as has been pointed out below. 1 is also used.

Considering that there is no zeroeth position in a string outside of computers, shouldn't the question really be: why are strings 0-based in some of the more common programming languages? (I am not sure about the statement of "nearly every programming language" since there are many more languages than most people are aware of)

Strings in C and other languages are simply an array of characters (i.e. char[]) that are null-terminated. This is why you can reference individual characters by using the index notation (i.e. stringVariable[index]). Variables are an address to a location in memory. The index is the offset to the starting address of the array. So when thinking in terms of strings being an array, it makes sense enough to interact with them in a 0-based fashion because it is at least consistent even if sometimes a little awkward.

Why is this different in SQL? I would guess that it has to do with SQL being more about physical storage than memory allocation. While some RDBMS's do support arrays (such as PostgreSQL), that is not standard. SQL is also a high-level declarative language that hides the operational specifics of what the query engine is really doing, and so the concepts of addresses and pointers just isn't there. Hence it doesn't really make sense to think in terms of 0-based indexes when working with SQL.

As another poster notes, the source of zero-based indexing is addressing. The first address in any data block ends in zero (whether it occupies the last digit in physical memory or not). And it's not just computers - the address of the first house on your neighborhood block likely is a number like 300 - not 301.

When programming iterative functions where moduli are used (to make something happen every 5 iterations, etc.) it's handy - and faster - to work with zero-based arrays.

Please also see:

Why are zero-based arrays the norm?
Zero-based numbering


Oracle Database 12c: Unable to resize system tablespace Problem:

My java application is returning ORA-01691: unable to extend lob segment errors for every insert

Cause:

After running:

SELECT *
FROM dba_tablespace_usage_metrics
ORDER BY used_percent desc;

I found that the SYSTEM datafile is 99.81% full (and is only 4MB for some reason).

Solution Attempts:

I turned autoextend on with

ALTER DATABASE
DATAFILE '/home/user/oracle/orcl/system01.dbf'
AUTOEXTEND ON NEXT 2048M
MAXSIZE UNLIMITED;

...which executed fine, but did not increase the size.
I then tried to manually increase the size with:

ALTER DATABASE
DATAFILE '/home/user/oracle/orcl/system01.dbf'
RESIZE 2048M;

...which gave an error that the new size I was trying to use would end up truncating data. That makes no sense of course since the current size is 4MB. I also tried pasting in 2GB as bytes instead of "2048M" because I thought it may not understand "2048M". That gave the same error though.

What is the actual, working way to get rid of the ORA-01691: unable to extend lob segment error?

DBA_TABLESPACE_USAGE_METRICS shows units in database blocks, not bytes. 4 MB is not enough for a SYSTEM tablespace. Most likely what you saw was 4M blocks, which is 32 GB with a 8K blocksize tablespace, and that is the maximum size of a datafile in a 8K smallfile tablespace. That is why your attempts had no effect.

You can add a new datafile to the tablespace, for example:

alter tablespace system add datafile '/home/user/oracle/orcl/system02.dbf' size 10m autoextend on next 10M maxsize unlimited;


SQL Server 2016: sp_send_dbmail, Failed to initialize sqlcmd library with error number -2147467259 I have a quite frustrating problem and I am not sure I have done this correctly.
I don't have a lot of experience with T-SQL but here is what I am trying to do:

IF a query returns values THEN send an email, otherwise do nothing.
I am going to schedule the query to be run as a job every 5 minutes or so to check if the query has returned results (and if so send an email as per the script).

Below is the script which is work in progress and taken from here:

https://serverfault.com/questions/165597/send-sql-server-job-alert-only-when-a-query-has-rows-to-return

So I understand that the script won't do exactly what I want but since I get stuck when trying to run it I want to solve that before modifying it further.

create procedure [dbo].[sp_alert_nav_jobqueue_test]
as

Begin

declare @alerttriggered AS nvarchar(max);

set @alerttriggered = 'SELECT "ID", "Description", "Start Date_Time", "Status", "E-Mail Sent", "Job Queue Category Code"
FROM [MyDatabaseTEST].[dbo].[TEST My Company $Job Queue Log Entry]
WHERE cast("Start Date_Time" as date) = cast(getdate() as date) AND "Status" != 0 AND "E-Mail Sent" >0'

IF (@alerttriggered IS NOT NULL)
begin

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Primary SMTP Profile',
    @recipients = 'randomemail@address.com',
    @query = 'SELECT "ID", "Description", "Start Date_Time", "Status", "E-Mail Sent", "Job Queue Category Code"
FROM [MyDatabaseTEST].[dbo].[TEST My Company $Job Queue Log Entry]
WHERE cast("Start Date_Time" as date) = cast(getdate() as date) AND "Status" != 0 AND "E-Mail Sent" >0' ,
      @subject = 'Alert from TEST NAV Job Queue Entry ',
       @Body = 'Asdf' ,
    @attach_query_result_as_file = 0 ;

End
else
begin

      EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Primary SMTP Profile',
       @recipients = 'randomemail@address.com',
            @BODY = 'No data returned ',
            @subject = 'Merchant Email'

End
End;

Now if I try and execute it via:

EXEC [dbo].[sp_alert_nav_jobqueue_test]
GO

I get this error:

Msg 22050, Level 16, State 1, Line 0
Failed to initialize sqlcmd library with error number -2147467259.

Now I am trying to execute it via my user and I've created an AD-group which I am a member of under the "msdb"-datbase and "Security" > "Users" which have "DatabaseMailUserRole" and "db_owner" under "Membership" and also have "Alter","Control" and "Execute" for the stored procedure I created.

When I am running SQL Profiler to further troubleshoot I see this:

exec sp_executesql N'INSERT sysmail_query_transfer(uid, text_data) VALUES((@P1), (@P2))',N'@P1 nchar(72),@P2 ntext',N'29E86B54-AC35-4944-B700-EBD9BED57A22                                    ',N'Msg 241, Level 16, State 1, Server MyServer, Line 1
Conversion failed when converting date and/or time from character string.
'

If I comment out the "WHERE"-clause, modify the "SELECT"-statement to "TOP 1" and set "@attach_query_result_as_file = 0" to "1" I can successfully send an email. This leads me to believe that there is something wrong with my "WHERE"-clause but what?

Guessing it is that the value that gets returned from the "WHERE"-clause is not of a "varchar" type?

Really appreciate any and all help

The problem is that you are a) using double-quotes instead square-brackets when specifying column names, and b) somehow the session setting of QUOTED_IDENTIFIER is OFF.

You can alter the stored procedure, BUT only after specifying the proper setting first. The setting is stored along with the Stored Procedure definition. You can see the setting (the uses_quoted_identifier column) by running the following:

SELECT * FROM sys.sql_modules WHERE [object_id] = OBJECT_ID(N'sp_alert_nav_jobqueue_test');

Hence, do the following and then re-run the query above to see that the setting did change:

SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE [dbo].[sp_alert_nav_jobqueue_test]
AS
...

You should also change the double-quotes to be square-brackets. Even if it starts working with the correct session setting, square brackets always work and so are by far the better choice. You can see the difference in behavior below.

SET QUOTED_IDENTIFIER ON;
SELECT cast("Start Date_Time" as date) FROM (SELECT GETDATE() AS [Start Date_Time]) tmp;
-- works

SET QUOTED_IDENTIFIER OFF;
SELECT cast("Start Date_Time" as date) FROM (SELECT GETDATE() AS [Start Date_Time]) tmp;
-- error:
/*
Msg 241, Level 16, State 1, Line 210
Conversion failed when converting date and/or time from character string.
*/

SET QUOTED_IDENTIFIER ON;
SELECT cast([Start Date_Time] as date) FROM (SELECT GETDATE() AS [Start Date_Time]) tmp;
-- works

SET QUOTED_IDENTIFIER OFF;
SELECT cast([Start Date_Time] as date) FROM (SELECT GETDATE() AS [Start Date_Time]) tmp;
-- works

Side note: It is also best to not name stored procedures starting with sp_ as that has special meaning in SQL Server. It causes SQL Server to first look in [master] for the stored procedure and then, if not found, it will check the current DB. Meaning: it is a performance hit unless it is in [master] and intended to execute this way.


Estimation of appropriate innodb buffer pool size I'm reading this thread How large should be mysql innodb_buffer_pool_size?

And perform the querys suggested by RolandoMySQLDBA and I get 307 RIBPS and 264G. That's mean that I need 307GB of RAM?

Currently I have a innodb_buffer_pool_size value of 2097152000 but the server has 128GB of RAM and according to MySQL Workbench Server Status the InnoDB BufferUsage is at 100%

This is wrong rigth?
I mean, I must to increase the innodb_buffer_pool_size?

MySQL version is 5.5.29

Bad!  The innodb_buffer_pool_size should never be set bigger than the amount of RAM you have.  An general rule is to set it to 70% of available RAM.  For 128GB of RAM, 100G is probably fine, assuming you don't have a lot of other applications running on it.

The buffer_pool is a cache.  If the data that you actively use is only 20G, then about 20G will work fine, almost as good as 100G.  And you don't need to increase your RAM.

If you are repeatedly reading all of your 307G of data and indexes, then you do need 350GB of RAM.  But probably you could make some simple changes to avoid such repeated reading, thereby getting down to a civilized "working set", such as 20G.  (No, there is no simple way to estimate the "working set size".)

Bottom line:  Don't get more RAM.  Set the buffer_pool to 100G.  Fix any slow queries.

More discussion.


Which database to use? Hey there. I am looking for a free to use for commercial website database that should have these design features, in the given order:

Auto-Sharding (getting more free space by adding more server nodes)
high read performance
data larger than available memory (so, persistent of course)
New inserts should be available to read/update in real time.

Features that would be very handy, but not necessary:

In-Place-Updates like in MongoDB because the columns 1 to 7 never change in size. And the column 0 never changes at all.
Handy would be something where I can query slave nodes too. Not just masters. To achieve more concurrent operations (especially reads).

Non-relational, relational, does not matter.

I am going to have some billions of entries that look like this:

{
    0: (64 or 32, not decided yet) byte string,
    1: 64 bit integer,
    2: 64 bit integer,
    3: 64 bit integer,
    4: 64 bit integer,
    5: 64 bit integer,
    6: 64 bit integer,
    7: 64 bit integer
}

The only key/index will be "0" (primary index in SQL, or the key in NoSQL databases).

Queries will look something like these:

SELECT * WHERE 0='string'
UPDATE 3=int, 6=int, 7=int WHERE 0='string'
DELETE WHERE 0='string'
INSERT {0: 'string', 1=int, 2=int, 3=int, 4=int, 5=int, 6=int, 7=int}

No other queries (like range, etc).

For every 1,000 operations there will be:

600 reads (not sequential)
200 updates (not sequential)
150 inserts (bulk if possible)
50 deletes (bulk if possible)

Thank you for your answers!

A few db's that come close:

1:  Postgres-XC, meets most areas except that sharding takes some design effort

2:  VoltDB, meets most areas except for data larger than memory

The key question though is why do you need the autosharding?  Anything automatic adds complexity and if you are big enough to require sharding, you can probably afford to have the experts look carefully at how best to do this (i.e. via Postgres-XC).

Honestly, I think the best option for most people in your position is to start with PostgreSQL and plan on scaling up and out with bigger servers and Postgres-XC when you get to the point that you need this.  But there are a lot of big systems out there that run on PostgreSQL, including Skype and Instagram, so it certainly can do web scale even without sharding.


Does MySQL or MariaDB support double literals (double precision)? MySQL seems to support double precision

  The FLOAT and DOUBLE types represent approximate numeric data values. MySQL uses four bytes for single-precision values and eight bytes for double-precision values.

Is there a method to declare that a literal is a double precision and not a float, decimal or anything else? I don't see this mentioned in the docs on Numeric Literals. In PostgreSQL, I could coerce a literal to a double precision with

SELECT 1::double precision,
  double precision '1',
  CAST(1 AS double precision);

Though not in the docs explicitly on Numeric Literals. You can make a double precision literal with scientific notation (using e0) which seems to work,

MariaDB [test]> CREATE TABLE foo AS SELECT 1.0e0 AS "se_decimal", 1e0 AS "se_nodecimal", 1.0 AS "decimal";
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [test]> SHOW CREATE TABLE foo;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| foo   | CREATE TABLE `foo` (
  `se_decimal` double NOT NULL,
  `se_nodecimal` double NOT NULL,
  `decimal` decimal(2,1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

This is actually supported by the SQL Spec, which shifts the literal from <exact numeric literal> to <approximate numeric literal> when it sees the e or E. From SQL 2011n spec,

<approximate numeric literal> ::=
  <mantissa> E <exponent>

This is what the docs on Numeric Literals presumably refer to when it says,

  Approximate-value numeric literals are represented in scientific notation with a mantissa and exponent. Either or both parts may be signed. Examples: 1.2E3, 1.2E-3, -1.2E3, -1.2E-3.

Though the docs don't say the type, it's double (see above).

See also,

How does the SQL Spec specify literals with a decimal should be handled?


This is relatively easy to do, you just need to add some analytic values to your original data set to get it:

with grps as (
select t1.*
     , trunc((row_number() over (partition by Center_Code, PaperCode order by Roll_No)-1)/4) grp_id
     , roll_no - row_number() over (partition by Center_Code, PaperCode order by Roll_No) rng_id
  from your_data t1
), rngs as (
select center_code
     , papercode
     , to_char(min(roll_no))||'-'||to_char(max(roll_no)) roll_nos
     , min(roll_no) ord
     , count(*) students
     , grp_id
  from grps
 group by center_code, papercode, grp_id, rng_id
)
select center_code
     , papercode
     , LISTAGG(roll_nos, ', ') WITHIN GROUP (ORDER BY ord) roll_nos
     , sum(students) students
  from rngs
  group by center_code, papercode, grp_id;

In the first subfactored query (grps) I'm calculating a grouping id (grp_id) to put the roll_nos into groups of 4, and range id (rng_id) so that each group of 4 roll_nos can be grouped into contiguous ranges.

In the second subfactored query (rngs) the sub ranges of roll_nos are consolidated and counted and an ordering column (ord) is generated.

In the final output query the sub ranges are accumulated with listagg and the final group size is calculated.


Find source of spids that show no hostname I have a busy SQL 2012 server where connections to one particular database (using the same SQL login) don't show a "hostname", and I need to find out which box (of several) these connections are coming from.

I've tried all the normal built-in and 3rd-party tools (master.sys.processes, sys.dm_exec_sessions, sp_who2, sp_WhoIsActive, the SSMS Activity Monitor, even SQL Profiler and Idera Diagnostic Manager), none of them can give me the hostname of the server/client behind these connections, presumably because they all use the same underlying system tables/views.

I'd be fine with an IP address, too, but don't see any way to determine that from within SQL either.

In case this is any help:

sys.dm_exec_sessions shows the client_interface_name is ODBC
Nothing captured for program_name either
The user is a SQL account that happens to be a SYSADMIN (yes, I know...)

I found a related question here (albeit from the other side), but that went unanswered.

At this point I'm tempted to disable the user so I can capture some failed logins in the SQL errorlog, but I don't imagine the application team will be happy with that.

Looks like I was just asking the question wrong, I found the answer under: How to get the client IP address from SQL Server 2008 itself?:

As Aaron mentioned in the comments, I had to use sys.dm_exec_connections to get the source IP. Here is the final version of the query I used:

SELECT s.session_id, s.login_name, DB_NAME(s.database_id) as database_name,
    s.host_name, s.program_name, c.client_net_address, client_tcp_port,
    c.net_transport, c.auth_scheme, s.login_time
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id
WHERE s.login_name = 'MYSTERY_USER'
ORDER BY s.session_id


What are the sp_send_dbmail return values? I have an email failing with function return value of '101' and @@error value of '0' (no rows are added to sysmail_allitems).

Where can I find documentation on return codes for this function?

Example code showing how I obtain the above values:

exec @result = msdb.dbo.sp_send_dbmail
    @profile_name = 'OBFUSCATED',
    @recipients = @DL,
    @subject = 'OBFUSCATED',
    @body = @emailBody,
    @body_format='html',
    @query = @reportQuery,
    @exclude_query_output = 1,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = @filename,
    @query_result_separator = @temp,
    @query_result_header = 1,
    @mailitem_id = @mailitem_id
    ;

    set @temp = @@ERROR;

Error code 101 is because your query result attachment is larger than the "MaxFileSize" Database Mail configuration option.
Investigation
My first attempt at figuring this out was to run sp_helpttext on the sp_send_dbmail procedure to look at the implementation:
EXEC sp_helptext 'sp_send_dbmail';

The main body of that procedure returns a bunch of different status codes, from 0 up to 21.  It doesn't mention 101.  However, it calls into a few other stored procedures, like sysmail_verify_profile_sp, so I glanced in that one:
EXEC sp_helptext 'sysmail_verify_profile_sp';

But that only returns codes from 0 to 4.
Later the proc calls into sysmail_verify_addressparams_sp, let's check it out:
EXEC sp_helptext 'sysmail_verify_addressparams_sp';

Shucks, that only returns 0 or 1.
Next up is sp_RunMailQuery:
EXEC sp_helptext 'sp_RunMailQuery';

It calls sysmail_help_configure_value_sp and sp_isprohibited, both of which only return 0 or 1.
Black Box
At the end of sp_RunMailQuery, a system extended stored procedure is called: xp_sysmail_format_query.
We can't grab the source code for this, but I had a hunch from earlier in the proc:
--Get the maximum file size allowed for attachments from sysmailconfig.
EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'MaxFileSize',
                                            @parameter_value = @fileSizeStr OUTPUT

I noticed that you are using the attachment parameter, and the query results + max file size both get passed into the extended stored procedure as well.
Repro City
I set my Database Mail maximum file size to 10 bytes, and then ran:
DECLARE @result AS INT;

exec @result = msdb.dbo.sp_send_dbmail
    @profile_name = 'DBMail',
    @recipients = 'myemailaddress@something.com',
    @subject = 'This is the subject',
    @body = 'This is the body',
    @body_format='html',
    @query = 'SELECT * FROM sys.messages;',
    @exclude_query_output = 1,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'big-file.csv',
    @query_result_separator = '===',
    @query_result_header = 1;

SELECT @result;

Note: the "sys.messages" table has a bunch of string data that I knew would be more than 10 bytes.
And sure enough, I got 101 as the return code:

Bugz
As CR241 helpfully pointed out, the documentation says that @@ERROR should contain the error id number that corresponds to the appropriate error message in the sys.messages table:

The error code for the statement that failed is stored in the @@ERROR variable. - sp_send_dbmail - Return Code Values

Instead it's 0 in my testing (which you noted in your original question).  This seems like a bug to me.


I can't connect to an instance of SSMS from other servers SSMS I can log in to the server and connect to the instance of SSMS in the server but I can't connect to the same instance from other servers using ssms. how can I fix this?

Start by making sure that your Firewall isn't blocking incoming connections to port 1433 (if this is a default instance installation). Check both the Windows Firewall and also the settings on your network firewall ( if you have one configured ).

Also make sure that remote connections are enabled to the SQL server (in SSMS, right click the instance -> Properties -> Connections -> Allow Remote Connections to this server)


Dynamic column value to columns name mySQL I have table like following, i have almost 100+ questions and more than 30,000 students:

StudentId | Questions  | QID| Answer  | View   |
1         | YourGender | 1  | Male    | Gender |
2         | YourGender | 1  | Female  | Gender |
1         | YourAge?   | 2  | 23      | Age    |
2         | YourAge    | 2  | 21      | Age    |

I need table like followings:

StudentId | YourGender  | YourAge?
    1     | Male        | 23
    2     | Female      | 21

My questions are randomly add/modify/del, so I want everything should work dynamically, when questions added or deleted I need not to change anything on my code.

You can solve it on this way:

Select all distinct StudentId, and join its with two subqueries, one with all answers about Gender and another with all answers about Age, both grouped by StudentId.

select    distinct q.StudentId,
          g.Answer as YourGender,
          a.Answer as YourAge
from      questions q
left join (
           select   StudentId, Answer
           from     questions
           where    View = 'Gender'
           group by StudentId
          ) g
on         g.StudentId = q.StudentId
left join (
           select   StudentId, Answer
           from     questions
           where    View = 'Age'
           group by StudentId
          ) a
on         a.StudentId = q.StudentId
;

This is the result:

| StudentId | YourGender | YourAge |
|-----------|------------|---------|
| 1         | Male       | 23      |
| 2         | Female     | 21      |

Can check it here: http://rextester.com/XPNAT23300


Need help phrasing regular expression to find particular T-SQL pattern I know this is a Database Forum, but since the text I am trying to find is SQL-specific, I am hoping that somebody can point me in the right direction.  I need to search code to find places where the INSERT queries are written without explicitly specifying table column names.  For example, I need to find things like this:

INSERT tablename SELECT col1, col2 FROM anothertable

INSERT INTO tablename VALUES (1), (2)

In case you wonder, when the column names are not explicitly specified after the table name, the SQL Server will implicitely assume that all of the columns are listed.  As a result, if you add a new column to the table, your code will break.

The regex pattern I envision would look for the following:

INSERT followed by any characters, followed by either SELECT or VALUES where there is no occurrence of closing parenthesis ")" before the SELECT/VALUES.

I was thinking about alternative way to phrase it, but I am not sure it's easier:

INSERT or INSERT INT followed by space/tab/cr/lf, followed by any number of characters, followed by space/tab/cr/lf, NOT followed by open parenthesis "("

I appreciate any pointers.

The "magic" regexp to do what you want is:

^[\s\t]*INSERT( INTO|)[\s\t\r\n]*[A-Za-z0-9_."\[\]]*[\s\t\r\n]*(SELECT|VALUES)\s

I tested some scenarios in "The Regex Coach" (a free software to test regexp). I made sure it also works for tables like [dbo].[tablename] or "tablename" etc.


Same column present on two or more foreign key constraints A simple schema leading to my question is the following:

CREATE TABLE A (
    AID INT AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE A_COPY (
    AID INT,
    CID INT,
    PRIMARY KEY (AID, CID),
    FOREIGN KEY (AID) REFERENCES A(AID) ON DELETE CASCADE
);

CREATE TABLE R (
    AID INT,
    CID INT,
    EXTRANUM INT,
    PRIMARY KEY (AID, CID, EXTRANUM),
    FOREIGN KEY (AID) REFERENCES A(AID) ON DELETE NO ACTION,
    FOREIGN KEY (AID, CID) REFERENCES A_COPY(AID, CID) ON DELETE CASCADE
);

This SQL Fiddle contains sample data and queries along with the schema shown above.

Here, as you can see, we build up on the A table by extending it (say that A_COPY is a "weak entity" in an Entity Relationship diagram) and then use it in some relation R. The question is, that here (table R) we use two different foreign key constraints where one column is the same in both constraints. That might, or might not be meaningful.

So, one thing is whether this would be meaningful. I believe it would, since I might (for some reason) want to opt for different foreign key referential actions when deleting from table A or A_COPY respectively, as shown here.

The second question is how do these statements get implemented in MySQL with InnoDB tables. Generally, the question is: what is the chosen rule for foreign key constraints on the same table with two or more identical columns part of the respective foreign key constraints (e.g. here AID is used identically in two foreign key constraints on the same table R)?

I'm not entirely clear on what the standard says about this, but clearly, there is a conflict between:

  a) FOREIGN KEY (AID) REFERENCES A(AID) ON DELETE NO ACTION

and

  b) FOREIGN KEY (AID, CID) REFERENCES A_COPY(AID, CID) ON DELETE CASCADE

The final result may differ if a) is evaluated before b) or the other way around. There is a similar question in standard-behaviour-for-mix-of-on-delete-cascade-and-on-delete-restrict-const


What features of Oracle make it an attractive choice for small projects? Given Oracle's licensing handling[a] (and, to a lesser extent, cost) I have always been wondering what the deciding factors would be for choosing Oracle over PostgreSQL or MySQL.

My company is nearly always choosing Oracle (XE where possible), even for small projects where there is just a single simple Windows server box running the database without any dedicated DB administration. (Note that small does not mean the data will always fit the rather small size constraints of Oracle XE.)

I have always questioned this choice, but it has the benefit that we at least are exposed to only one database product.

Still, given a new project, where you need a RDBMS, but the project and scope of the database is pretty small, based on which unique features of Oracle running on simple Windows server boxes (without too much dedicated administration) would you choose Oracle over another RDBMS?

Additional Context: A lot of our database deployments run at customer sites in a, lets call it, "low-administration" mode. That is, the database is set up once. There is some initial testing on its correct behavior and performance on site. After this, the database just run's on its on. No regular administration done. Only if somethings broken will a technician (not a dedicated DBA) check the database, trying to figure out what's up. Backup is mostly done as offline backup. In some projects, the customers don't even care there's a RDBMS involved. They just see their app as a black box that works (or not).

[a] : Where I work, it took several project managers repeatedly months to get proper licensing for small projects as the local Oracle representatives are simply not very interested in selling their product if the revenue is smallish.

I can only attempt to answer for Oracle and postgres. After using Oracle exclusively for years, and postgres only for the last two years or so, I love postgres. There are so many small ways it is more convenient to use than Oracle, and it shares many of the crucial benefits (such as MVCC). It is easier to administer, reliable, has excellent documentation, and of course, it is free.

However postgres is no match for Oracle in some areas, such as:

RAC - as far as I know there is no better clustering technology for any database
RMAN - far superior to postgres's basic backup and recovery features, especially with block change tracking and incremental backups (which you can apply to other backups to keep an up to date full backup)
Oracle support - exists. postgres support? Not so much
many other features such as IOTs, 'Secure Files' and compression technology that have no analogue in the postgres world

Interestingly almost all af these features are absent or crippled in XE. I think I'd choose postgres over XE, all else being equal, but...

...none of this addresses the two biggest reasons for choosing Oracle:

You are already using Oracle and you have a huge investment in it (database independence is a silly myth)
Your developers and DBAs know Oracle and use its features to the full (why wouldn't they as database independence is a silly myth anyway?)

EDIT:

The one situation where I'd choose postgres over XE every time is if security is a concern. If your database or any of it's applications are exposed to the public internet I am not sure if XE is a good idea at all.


Should I create a new table for store information of an associated object with object need to create I'm not sure if topic can get you understand what I want to explain.
Follow guide in my old question, now I have these tables and its columns:

Course: name,...
Topic: name, course_id
Question: content, topic_id
GeneralExam: name, description, semester, number_question (total number of questions of general exam), duration, used (boolean), user_id, course_id
GeneralQuestion: general_exam_id, question_id

To sum up association:

Course - Topic: one-to-many
Topic - Question: one-to-many
Course - GeneralExam: one-to-many
Question - GeneralExam: many-to-many, join table: GeneralQuestion

Now, When creating new General Exam (GE) for a course, I want to specific a number of questions for each topic of that course, so that I can get questions for that GE ( I want to do(specific) it on form, I had a form for create new GE now). So, should I create new table to store a number of questions of each topic of course when creating new GE? If I should, how table I should design?

I image table I should design will like this (I can't get name of table now):

NameOfTable: general_exam_id, course_id, topic_id, number_question

Is this table good?

If the number of questions for each topic on each course varies from one General Exam to another, then yes, you want a structure like this (which is pretty much what you have):

NumberQuestions:
GE_ID int FK
Course_ID int FK
Topic_ID int FK
MaxQuestions int


Migrate SQL Server from 2012 to 2014 (11 to 12) I have just installed SQL Server Management studio 2014 and I also need to update my (local) server to accept a new database via a bak file from a version 12 server. When I look in Server Properties I see my server has Version 11.0.5858.0 (which is 2012 sp2).

How do I upgrade/migrate my server to version 12?

I've looked at a couple of links: Upgrade Database Engine, Upgrade to SQL Server 2014 Using the Installation Wizard (Setup). I've also seen suggestions that I need to backup and restore databases, which I assume would mean creating a new local (I forgotten how to do this, to tell the truth) and moving them there?

I have just installed SQL Server Management studio 2014

SSMS wont install database engine. Its just a GUI to connect to SQL Server. You can check the SQL Server version using below T-SQL :

SELECT
   SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
   SERVERPROPERTY ('ServerName') AS SQLServerName,
   SERVERPROPERTY ('Edition') AS ServerEdition,
   CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + '
       + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
       + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion,
    RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],
 SERVERPROPERTY ('Collation') AS Collation

  How do I upgrade/migrate my server to version 12?

There are couple of ways to do.

InPlace Upgrade : You just upgrade the current instance by running the setup.exe. The older instance is replaced by the newer version.

Side by side migration: You install and configure a new instance that might exist on the same server or different server and then you move all you databases using backup/restore (preferred) or other methods like - detach/attach, logshipping, copy database wizard, etc.

Note: Depending on your downtime requirements, you have to plan accordingly. Prepare a detailed migration plan and discuss that will stake holders for an acceptable downtime.

If it is your local instance, then you dont have to worry about any downtime.

An excellent answer by Mike Walsh : Are SQL Server in-place upgrades as ill advised as they used to be?

Also, check out UPGRADING TO SQL SERVER 2014: A DOZEN THINGS TO CHECK as well as What to Consider When Creating SQL Server Database Migration Plan?


How can I push data from my local MAMP database to my remote database? I'm developing a site locally using MAMP Pro, and I've created a mysql database locally that have the same structure as the remote server. (username and password are different though)

I'm inserting new data into the local database - since that's much faster than interacting with the remote server - and at the end of the day I would like to just update my remote database with the stuff I've added locally.

Now I'm using the "export to file" in phpMyAdmin on each table that I've added new stuff into, and adding the DROP IF EXISTS-statement. It's not that hard, but I kind of have to take the site down because the foregin keys fails when I'm just updating one at a time.

Any suggestion on how to just update the content of tables on the remote server, based on what's in the local server?

I have MySQL Workbench, but all I could find was a way to sync the models. As in updating the table structures, adding new tables and stuff like that...
I might have overlooked some functionality there?

There are a couple of ways to get your data from your local Dev to your remote Dev or Production environment. The first and simplest way is to just dump your db and upload it. You can also use a tool like phpMyAdmin to Sync your db but that would require you to install more software and understand basic networking. If you have a huge Dev database with lots of unused test data you can also dump and import just the db structure which will give you an empty db with no information (users, setting, whatever you are storing). I would highly recommend installing phpMyAdmin on your local Dev server to simplify your life, it makes any of these options extremely simple and pretty much 1 click tasks.


Error When Restoring Log Here is the situation.  I have restored a database to replace an existing version (need to get it back to yesterday at a certain time).  So I do a RESTORE DATABASE ... WITH NORECOVERY, REPLACE, etc... with no problem.

There are about 16 transaction logs that I need to then restore as well to get the database to the point in time that I need it.  The first RESTORE LOG completes with success.  The second one tells me 100% complete, but immediately after that I get this message:

An error occurred while processing the log for database 'MyDb'.  If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

What exactly does this mean??  I did some research and it looks like a few things can cause this, and none of the examples/situations I read about had to do with a RESTORE LOG.  Does this possibly mean my transaction log backup is corrupt??

Is there any remedy to this?

Sounds like the data in the log backup might have been corrupted. Can you restore the next log ok, or do you get an error at that point as well?


Count the total tree structure I've got a table which contains object_id, object_name, derived_from_object_id

What function(s) do I need to look at to be able to provide a total count of derivations based on a provided object_id?

Example

1    object1    0
2    object2    1
3    object3    1
4    object4    0
5    object5    3
6    object6    2
7    object7    3
8    object8    5

There is no defined maximum number of derivations from an object or the children. So if I wanted a total count from objectid 1 the result would be 7.

I've thought about writing a stored procedure which simply returned a count for each object id, it would then call itself for each id that there was a child for. Whilst I think this would work I think it's completely the wrong direction to go.

You could use a recursive common table expression to find all the derivations from a particular starting point.

Table and Data

DROP TABLE IF EXISTS dbo.ObjectTree;

CREATE TABLE dbo.ObjectTree
(
    [object_id] integer NOT NULL
        CONSTRAINT [PK dbo.ObjectTree object_id]
        PRIMARY KEY CLUSTERED,
    [object_name] varchar(50) NOT NULL
        CONSTRAINT [UQ dbo.ObjectTree object_name]
        UNIQUE NONCLUSTERED,
    derived_from_object_id integer NULL
        CONSTRAINT [FK object_id]
        FOREIGN KEY (derived_from_object_id)
        REFERENCES dbo.ObjectTree ([object_id]),

    INDEX [IX dbo.ObjectTree derived_from_object_id] (derived_from_object_id)
);

INSERT dbo.ObjectTree
    ([object_id], [object_name], derived_from_object_id)
VALUES
    (1, 'object1', NULL),
    (2, 'object2', 1),
    (3, 'object3', 1),
    (4, 'object4', NULL),
    (5, 'object5', 3),
    (6, 'object6', 2),
    (7, 'object7', 3),
    (8, 'object8', 5);

Recursive Query

-- Where to start
DECLARE @ObjectID integer = 1;

WITH R AS
(
    -- Anchor
    SELECT
        OT.[object_id]
    FROM dbo.ObjectTree AS OT
    WHERE
        OT.[object_id] = @ObjectID

    UNION ALL

    -- Recursive
    SELECT
        OT.[object_id]
    FROM R
    JOIN dbo.ObjectTree AS OT
        ON OT.derived_from_object_id = R.[object_id]
)
SELECT
    derivations = COUNT_BIG(*)
FROM R
OPTION MAXRECURSION(0);

This returns 7 for [object_id] = 1.

Try it at db<>fiddle.

Using MAXRECURSION

The MAXRECURSION query hint can be used to prevent a poorly formed recursive CTE from entering into an infinite loop. If that is a concern for you, use a suitable maximum recursion level instead of the zero (no limit) above. The default (no hint) is 100.


AlwaysOn Failover - SQL Server 2014 I have two SQL Servers 2014 in AlwaysOn AG setup (OS - WSFC2012R2) with secondary acting as a synchronous replica. I'm facing an issue in the role switchover on daily basis.

Every day morning around 7 or 8AM, due to some reason Primary fails over to the secondary. Ideally, this happens due to network glitch. But i'm unable to find the root cause behind this failover.

This happens on daily basis. Is there any way to narrow down the issue which causes the failover?

The cluster log, SQL Server Error Log and the application log in Event Viewer should give you the reason.

It sounds like quorum is not setup properly. Without a 3rd resource to act as the witness, a network glitch will cause a failover.

Setup a witness, such as a file share on another server, or a witness disk. Some people use a file share on a file server or an AD server. I've also used the witness disk approach where the disk is a shared drive, like what you'd have on a Failover Cluster.


Change Order of Database Backups Can I specify a specific database to be backed up first and another database after that?

For example in the following order:

DB2
DB3
DB1

The code which I am currently using is as follows:

EXECUTE [dbo].[DatabaseBackup]
    @Databases = 'USER_DATABASES',
    @Directory = N'E:\MSSQL\Backup',
    @BackupType = 'FULL',
    @Verify = 'Y',
    @CleanupTime = 180,
    @Compress='Y',
    @Encrypt = 'Y',
    @EncryptionAlgorithm = 'AES_256',
    @ServerCertificate = 'DBCertificate',
    @CheckSum = 'Y',
    @CleanupMode = 'BEFORE_BACKUP',
    @LogToTable = 'Y'" -b

This seems to have been released in the latest version as below:

https://github.com/olahallengren/sql-server-maintenance-solution/issues/47

You'll need to add the list of database names to the @Databases parameter - but do check through the documentation and Github site for clarification.

As hot2use states above, master and model will never be covered by this script though, as they are not user databases.

I would suggest you give the documentation a read here.


You do not have privileges to manipulate with the users! Error message:

You do not have privileges to manipulate with the users!

Appears in PhpMyAdmin using Mariadb 10.3.7 on MacOs High Sierra installation from Homebrew

I'm logged in as root
installed and rebooted with multiple server options: Mysql 5.7, Mysql 8.0
Mariadb 10.2 and 10.3.7.

I've tried everything from granting tables, flushing the permissions.
I need to be able to edit users from the PhpMyAdmin page and its not allowing me even though I have root access (with password set)

According to comments in this Github issue report for phpMyAdmin, you probably need to set this option:

$cfg['Servers'][$i]['DisableIS'] = true;

in config.inc.php. Though it sounds like this is a workaround for a bug, so may be fixed in later releases or not an issue in earlier releases.


Backup Disappearing? I've created a Database maintenance plan for creating a backup set daily.  The Back Up Database Task has three subplans:

Full Backup: Occurs every day at 12am
Differential Backup: Occurs every hour starting at 1:30am
Transaction Log Backup: Occurs every 10 minutes starting at 1:45am

The general idea is that we only have to restore the full backup, then the last viable differential, and then the logs after that.

The issue is that the previous days full backup is not available when using the Restore Database tool in SSMS.  I.E. if I want to go back more than last nights full backup I'm out of luck.
What detail did I miss here?

It might give a hint if I describe how I discovered this issue... I created a database yesterday afternoon and worked on it into the evening.  Today I realized that I'd like to restore back to a point about an hour after I created the database to find that I could only go back to the full backup that was created at midnight (see below for configs if you think that will help...).

Further configurations below:

Full backup settings

Connection: Local server connection
Backup type: Full
Database(s): All databases
Backup set will expire: (not selected)
Backup to: Disk
Backup databases across one or more files: (not selected)
Create a backup file for every database: (selected)
Create a sub-directory for each database: (checked)
Folder: \backups\sql
Backup file extension: bak
Verify backup integrity: (checked)
Set backup compression: Use the default server setting

Differential backup settings

All is the same as full except that type is differential

Transaction Log backup settings

All is the same as full except that type is Transaction log and backup file extension is "trn."

Ok, so it turns out I didn't know that I couldn't roll back to an earlier version of my newly created database until a full backup was made.  This is why I couldn't see any options to roll back to before midnight in the restore dialogue.


MySQL InnoDB over a Raw Device in an Active/Passive topology We have an Active/Passive topology where there are two x86 complexes with a shared raw storage, where only one of the nodes in a given moment has an access to the shared storage (AKA the active node). In case of a a failover in the active node, the passive node initiates a take over and becomes the active node with an access to the shared storage.  Each node has its own boot device storage with a filesystem. However, the shared storage cannot have a filesystem mounted on it.

We are interested in installing MySQL on both nodes, where its data resides in the shared storage and only the active node is running the server.

MySQL with InnoDB is capable of running on a raw device, and there is also a guide on how to run MySQL over a cluster similar to our topology. However, in the second example, they do have a filesystem mounted on  the shared storage. The filesystem issue raises a major concern:

ib_logfile* still require a file system. So the raw MySQL feature is not exactly fully raw. Please correct me if I'm mistaken. Is there a workaround to store those files in the raw storage? We can save the redo logs (ib_logfile0, ib_logfile1) in the node's boot device and always delete those files before the server is starting (So we won't have old logfiles in case of multiple failovers). However, this might lead to uncommitted transaction to be partially committed in case of a failure in a middle of a transaction, thus contradicting the whole idea of transactions.

Are there any more files/features that might affect the behaviour of mysql in this topology?

It's worth noting that InnoDB's write ahead log (WAL), the ib_logfile*, is not the only thing that will need a file system. You have:

System tables in the mysql schema that likely use the MyISAM storage engine (.frm, .MYD, .MYI per table) (most are now using InnoDB in 5.7)
.frm files for each InnoDB table, even when using the shared system tablespace (table metadata that's required)
MySQL log files (error log, general log, binary log)
SSL artifacts
auto.cnf (where the MySQL instance UUID is generated and automatically stored)
db.opt file for each schema (in /<datadir>/<schema>/)
.par file if you create a partitioned table (gone in 5.7)
.trn and .trg files if you create triggers
InnoDB tmp tablespace (5.6+)
Persisted buffer pool page map (ib_buffer_pool, 5.6+)

All of the above are typically within the data directory, so as long as you have datadir=/some/valid/fs/path -- that's also replicated (e.g. DRBD) or shared (e.g. NFS, GFS, OCFS) between the two nodes -- then you'll be fine.

It's worth noting that the .frm, .par, .trn, .trg, and .opt files will go away with the new Data Dictionary.

Stay tuned for some big announcements there in the coming months! :)

It's not clear to me why you're using the RAW device? I'm sure you have your reasons though. :)

Good luck!


SQL Server specific file group I have a database with three file groups, Primary, FG_ExtendedTables, and FG_ReportingTables.

I need to create another database (production db let's say), and restore only specific file groups and its tables with its data

I've seen this answer on stackoverflow https://stackoverflow.com/questions/25841822/backup-and-restore-sql-server-database-filegroup
but he's restoring the .bak file, I did like the example he provided but the bak file has all tables and I don't want that.

is that even possible ? if no, what is the purpose of the file groups then ?

I appreciate if code is provided, thank you.

Database schema for all objects is stored in the PRIMARY filegroup although user data may optionally be stored in a different filegroup.  A piecemeal of individual filegroups is not intended to be used to create a new database, but rather to restore critical data before less frequently used data.  That technique allows a database to be partial available for use during a restore sequence before the entire restore is complete.

  if no, what is the purpose of the file groups then ?

In addition to piecemeal restores, user-defined filegroups allow you to control placement of individual tables, indexes, and partitions.  This allows the underlying files to be placed on different underlying storage devices such as in a scenario where isolated storage provides a performance improvement (e.g. segregate random and sequential access) or with tiered storage (e.g. archive data on slower, less expensive media).


What code underlies the PG_GET_LATE_BINDING_VIEW_COLS sys information function? We want to transition to late-binding views, but late-binding view metadata is not presented by some clients (workbench/J, Toad, pgcli) which makes it hard to discover column names in these views. AWS provides a function, PG_GET_LATE_BINDING_VIEW_COLS, to get the metadata, but we can't seem to load this into a redshift table. Are there PG sys tables storing the late-binding view columns, or what underlies this function?

try svv_columns, it should give you what you need


Using COUNT in triggers before insert into table I am new to MySQL and am struggling with triggers. Say I have a table called members with the following data

MemberID(PK) (INT) | Name (VARCHAR)|
1                  | Barry         |

and another table called class with the following data

ClassID(PK) (INT) | ClassType(VARCHAR) | ClassSize(INT)
1                 | Boxing             | 20

I also have a link entity table called members_has_class with the following data

Members_MemberID(FK) (INT) | Class_ClassID (FK) (INT)|
1                          | 1                       |

I'd like to make a trigger that checks the class is not full when a user likes to sign up to a class where the information entered (Member ID and Class ID) is inserted into members_has_class table.

So for example, if boxing has  a class size of 20, the trigger checks that there are not already 20 people signed up to it. So if there are not 20 people already in the members_has_class they can sign up, if the class is full it does not allow sign up. I was thinking I would have to count how many of the same class id values there are and compare if it is bigger and smaller than the class size.

I suggest avoiding TRIGGERs.

BEGIN;
SELECT COUNT(*) ... FOR UPDATE;
if there is still room
    INSERT ... to add student to class
else
    give error
COMMIT;

That is, write application code perform the logic.  (TRIGGERs and FOREIGN KEYs have limited capabilities.)

Put it into a transaction to prevent another student from sneaking in while you are checking availability.  Note also the FOR UPDATE.

The above transaction could be put in a STORED PROCEDURE, but I suggest you do it in application code, rather than also taking on the potential challenges of SPs.


Identity Column in Concurrency, Multithreading, Parallel Processing Our company would like to understand the effects:

If we have single table with Primary Key Identity column, int primary key IDENTITY(1,1),
and the Application is conducting multi-threading parallel processing, trying to conducts 1000s of simultaneous inserts on one table.

Would the identity primary key column fail, with a primary key violation in any way, for example: processors are trying to input the same identity number?

Or was SQL designed to be successful even in multithreading parallel insert?

Note: IDENTITY_INSERT is OFF

Would the identity primary key column fail, with a primary key violation in any way, example: processors are trying to input the same identity number?

This is covered in the SQL Server product documentation:

  CREATE TABLE (Transact-SQL) IDENTITY (Property)

  Identity columns can be used for generating key values. The identity property on a column guarantees the following:

  Each new value is generated based on the current seed & increment.
  Each new value for a particular transaction is different from other concurrent transactions on the table.

The second point is the guarantee you need.

It is valid no matter how many user processes or threads access SQL Server concurrently.

To state it explicitly: yes, identity allocation is thread-safe.

A call to CMEDSeqGen::GenerateNewValue allocates the next value to a caller, and is protected by a latch (a lightweight synchronization object). It is not affected by user transactions, which means you can end up with gaps in case of rollbacks. Concurrent transactions can generate identity (and sequence) values without having to wait on a lock.

For more details, see my answer to a related question.


Why does my scalar UDF perform so differently on two different (but extremely similar) servers? Recently, I was troubleshooting a strange performance issue that impacted an application's production environment, but not any of the lower environments. I managed to replicate the issue in its simplest form with this query:
SELECT product_id, dbo.TranslateStatusToActive(status_id) FROM prod_Products

TranslateStatusToActive is a very simple scalar UDF, which basically just joins the value given to another table and returns 1 or 0 based on a case statement. I'd post the code, but it's a vendor written function and I'm not particularly interested in getting sued today. (Yes, the logic can be inlined. Yes, it fixes the performance issue. Yes, we've convinced the vendor to implement the change. This is not my question.)
When executing in production, the query would take between 10 and 20 seconds to return results. In development, the same query returns in less than 3 seconds. The execution plans are nearly identical, except for showing that CPU time was around 15000 ms in production and 3000 ms elsewhere.
I suspected there were some environmental differences, so I set up another server that replicated the conditions of production as closely as possible: I made sure the number of CPUs, the amount of memory given to SQL Server, and the specific patch level (13.0.4451) were the same.
I restored a copy of the production database to this new sandbox server, and to my surprise, the query executed about as quickly as it did in development. Once again, the plan and data were identical, save for the extra CPU time. The waits listed in the execution plan were the same types and within a few ms of each other on every environment.
At a loss for what to do next, I enabled optimize for ad hoc workloads on the production server. This fixed the performance issue! One thing, though: neither of the other environments had this setting enabled. I had been regularly clearing the procedure and system caches in each environment during testing, so I don't think it was the result of changing a setting causing a recompile.
Questions

What could cause the UDF to run so differently in each environment despite identical plan and near identical systems?
Why did the production environment need to have optimize for ad hoc workloads enabled to perform as well as the other environments, which did not have it enabled?
Is there some setting I didn't think to check that might cause such a large difference?

Development is shared, while production is currently used only by this application. The third box's usage would be almost the same as the production one. I cleared pretty much every cache they give a DBCC command for. The development environment is used regularly as a training system, so I am fairly confident it wasn't a plan caching issue.
The only difference with the third box is that there isn't an app hooked up to it, but there was little to no app usage while I was testing the function in production, so the difference was, based on my experience working in this environment, negligible. The only thing I couldn't do was restart the production server, but Microsoft's documentation explicitly states that enabling optimize for ad hoc workloads doesn't clear or affect any existing plans, so I don't see what the difference would be.

The situation that you described can happen when there's some kind of monitoring enabled (trace, extended event session, some third party tool), which does some kind of logging or work per UDF execution (or even per statement inside the UDF).

If the UDF is executed many times in a query there can be a very large amount of overhead to do that monitoring. If the monitoring is only occurring on one server then you would see a large performance difference between them.


Single Server (Replica) Availability Group First time caller, long time listener.

I have a situation where I need to stand up a single server Availability Group for around 2 months (i.e. move DBs to a new server, rebuild the current server, and add the rebuilt current server to the AG).  Both servers are physical and have roughly the same specs. Windows Server 2016, SQL Server 2014.

My thought was to stand up the AG with the listener now so that when the current server is rebuilt and added to the group, we wouldn't need to add the listener then and update all of our apps/clients (i.e. that would force two migrations, one to the new server, one for the new listener).

I've tested this situation on a few test servers and its working as expected.  I also can't foresee a reason not to do this, but I thought it would be worth the community ask since we're talking about Production DBs.  My question is has anyone tried this and had any issues?  Would anyone recommend against this solution. Thanks.

First time caller, long time listener.

Ha! Love it! Welcome :)

  I've tested this situation on a few test servers and its working as expected.

You are correct, it should work without many if any issues. Since clustering will now be part of the scenario you don't get a free single node lunch... you're still beholden to any cluster or AG level issues that may arise (I'm not saying they will, just that it's now in the issues list).

  My question is has anyone tried this and had any issues?

I have, in fact, tried this. It does work, the 2-months part does give me a little bit of uneasiness... but assuming the configuration doesn't drift, you should be alright.

You can also pre-stage a listener by creating a DNS entry with the same name so connection strings don't change, use a network appliance or hardware load balancer, etc. The largest issue is enabling the Always On manager and the service restart required for it, in my opinion. All else (except the listener unless you do the DNS/HLB thing) should or can be transparent.

  Would anyone recommend against this solution.

I only have the reservations of getting used to the single server performance characteristics and then adding in a synchronous replica or such afterwards. Since the single node AG won't have sync_commit waits and such, you really won't be in for much of a shock... though this could change once the other node is added. Just something to think about.


Best way to design table structure for Operating Hours along with break time and optimized Query Problem #1:

How can the table be designed in MySQL in efficient way to structure

1) Store Opening Time,
2) Break Time and
3) Closing Time in a day

For an instance, On Sunday: Opening Time: 7:00AM, Closing Time: 5:00PM, Break Time Start: 1:00PM, Break Time End: 2:30PM.

Problem #2:

How to define the query to search that table for all Stores that are being Opened/Closed between the given range.

From your comment above I cannot understand your motive for how you are trying to achieve it the way that you are but this is how I would do it (this is off the top of my head and I have not had chance to test this yet so please bear that in mind):

CREATE TABLE `Store`
(
    id INT AUTO_INCREMENT NOT NULL,
    -- additional columns for the store type
    PRIMARY KEY (id)
);
CREATE TABLE `OpeningHours`
(
    store_id INT NOT NULL,
    `open` TIME NOT NULL,
    `close` TIME NOT NULL,
    FOREIGN KEY (`store_id`) REFERENCES `stores`(`id`)
    -- add your own indexes here
);

Then put your open to close times in here such that if you opened at 9, closed at 5 and took a 1 hour lunch at 12 you would have data like so:

stored_id    `open`    `close`
1             09:00:00  12:00:00
1             13:00:00  17:00:00

Then you can query for stores that are open between a range of times like so:

DECLARE p_Time TIME;
    SET p_Time = TIME();

 SELECT DISTINCT store_id
   FROM `OpeningHours`
  WHERE  p_Time BETWEEN `open` AND `close`;

You can then use the store_id from the results above to join to the store table to get the store details.


How to select users that doesn't in the role I have 4 roles which are Admin, Procurement, Accounting, HR roles and 3 tables which are Users, Roles and Userroles. They user can have many roles as well as the roles can have many users. the problem is how can i get the users that are not in the role for example is the procurement because in my query I still get the users that are in procurement role? Here is my query.

  Admin role_id = 1

  Procurement role_id = 2

  Accounting role_id = 3

  HR role_id = 4

SELECT
  `users`.`user_id`,
  `users`.`username`,
  `users`.`email`,
  `users`.`last_login`,
  `users`.`last_ip`,
  `users`.`created_on`,
  `users`.`status` AS user_status,
  `roles`.`role_id`,
  `roles`.`role_name`,
  `roles`.`role_code`,
  `roles`.`status` AS role_status
FROM
  (`users`)
  INNER JOIN `userroles`
    ON `users`.`user_id` = `userroles`.`user_id`
  INNER JOIN `roles`
    ON `userroles`.`role_id` = `roles`.`role_id`
WHERE `roles`.`role_id` != '2'
ORDER BY `users`.`user_id` ASC

Let's say that we have user1, user2, and user3.

  User = Role

  user1 = Admin

  user2 = Procurement, Accounting

  user3 = Procurement

When I run the query above, it will show the user1 and user2.

SELECT ...
  FROM users u
  LEFT JOIN userroles ur ON ur.user_id = u.user_id AND ur.role_id = 2
 WHERE ur.role_id IS NULL
 ORDER BY u.user_id ASC;

Examine all rows of user, and their matching row from userroles with a role_is = 2 if it exists, eliminating the rows where it does indeed exist by requiring the role_id in the (non-existent) row to be null... which, since the row does not exist, is indeed null.

Alternately,

SELECT ...
  FROM users u
 WHERE NOT EXISTS (SELECT * FROM userroles ur WHERE ur.user_id = u.user_id AND ur.role_id = 2)
 ORDER BY u.user_id ASC;

Note that the SELECT * in the subquery doesn't actually select everything, it's just a way to express the test of whether such a row exists.


MySQL on Windows or Linux I am new to the Database region while have to migrate the company's database from AWS RDS to AWS EC2 and subscribe MySQL Enterprise edition.

What I am confused now is which OS should I choose, Windows or Linux? The most important thing is the performance, which one would MySQL perform better? If its Linux, is it possible to use Ubuntu OS? Can anyone help to advise?

Really appreciate!

It depends on the person who is managing the server. you can make decision based on following points:

If you are familiar with commands based administrative tasks, then Ubuntu Server edition is a better choice, as finding and installing required tools is easier compared to other distros. Best part is reduced overhead of Licencing and GUI.
If you prefer GUI based administration, then windows is good choice.


Error join database to SQL availability group Can anyone help me with the following problem?  I get the following error when I try to join a database to an availability group

  "Restoring the database resulted in an error.
  (Microsoft.SqlServer.Management.HadrTasks)

  ---------------------------------------------- ADDITIONAL INFORMATION

  Restore failed for Server '[computername]\SQL01'.
  (Microsoft.SqlServer.SmoExtended)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.6020.0+((SQL11_PCU_Main).151020-1526+)@EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

  System.Data.SqlClient.SqlError: The operating system returned the
  error '5(Access is denied.)' while attempting
  'RestoreContainer::ValidateTargetForCreation
  on'M:\UserLogs\OperationsManager.ldf'. (Microsoft.SqlServer.Smo)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.6020.0+((SQL11_PCU_Main).151020-1526+)&LinkId=20476

Background

2 Instances of SQL Server on two servers (Server1\instance1, Server1\instance2, Server2\instance1, Server2\instance2)
SCCM databases on instance 1; SCOM databases on instance 2
Moved SCCM databases to another server
Changed collation on instance 1 on each server to the one used by the SCOM databases; restarted each server after doing this
Detached OperationsManager database from instance 2, copied to instance 1 drives and attached to instance 1
Created SQL Availability group on instance 1 for OperationsManager database
Getting error message listed above when trying to add OperationsManager database to availability group
Have checked permissions on instance 1 folders on both servers, also on share folder used to synch AG; have granted SQL Server instance, SQL Server service accounts (domain user accounts) full access to instance 1 folders and synch folder
Have also made sure that SPNs for instance 1 on both servers are present & correct

It seems like you don't have permission to the folder M:\UserLogs\.

Try the below method, it may be help you.

Grant full access to SQL server service account for M:\UserLogs. (Which must be a domain account)
Manually restore the database.
While adding the db to AG choose JOIN ONLY.


begin tran instead of use db_name inside a SQL Job for executing Stored Procedure I was defining a SQL job yesterday for executing stored procedure at a specific time in the morning. It was an ad-hoc and urgent requirement and hence I developed this procedure on the fly and scheduled it to run every morning.

I was supposed to be just calling the procedure in the job step and define output file in the advanced tab. I had to use below command inside the job step:

use db_name
go
exec sp_name
go

Instead of above, I wrote as below by mistake:

begin tran
go
exec sp_name
go

This sql job didn't fail however it didn't do its intended job.

Output file contains below:

  Job 'Job_Name' : Step 1, 'Step_name' : Began Executing 2020-02-03
  06:10:00

Which seems to be correct however I was wondering that above could keep an open transaction and could cause another issues.

I executed sp_whoisactive(Thanks to Mr. Adam Machanic) and don't see any session running from that time as well as there is no blocking on any table as underlying procedure was having tablock table hint while inserting data into the table.

Is there anyway to find what actually happened to this sql job and why did it not do its intended work as well as if there is anyway to find whether it is still running in the background and could be committed or rolled back. I ran sp_who2 also to see if there is any SPID associated to this job, couldn't find anything.

Ran below query to check the status of all jobs and it shows my job succeeded:

USE MSDB
SELECT name AS [Job Name]
         ,CONVERT(VARCHAR,MAX(DATEADD(S,(run_time/10000)*60*60 /* hours */
          +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
          + (run_time - (run_time/100) * 100)  /* secs */
           ,CONVERT(DATETIME,RTRIM(run_date),113))),100) AS [Time Run]
         ,CASE WHEN enabled=1 THEN 'Enabled'
               ELSE 'Disabled'
          END [Job Status]
         ,CASE WHEN SJH.run_status=0 THEN 'Failed'
                     WHEN SJH.run_status=1 THEN 'Succeeded'
                     WHEN SJH.run_status=2 THEN 'Retry'
                     WHEN SJH.run_status=3 THEN 'Cancelled'
               ELSE 'Unknown'
          END [Job Outcome]
FROM   sysjobhistory SJH
JOIN   sysjobs SJ
ON     SJH.job_id=sj.job_id
WHERE  step_id=0
AND    DATEADD(S,
  (run_time/10000)*60*60 /* hours */
  +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
  + (run_time - (run_time/100) * 100)  /* secs */,
  CONVERT(DATETIME,RTRIM(run_date),113)) >= DATEADD(d,-20,GetDate())
  group by name, CASE WHEN enabled=1 THEN 'Enabled'
               ELSE 'Disabled'
          END
         ,CASE WHEN SJH.run_status=0 THEN 'Failed'
                     WHEN SJH.run_status=1 THEN 'Succeeded'
                     WHEN SJH.run_status=2 THEN 'Retry'
                     WHEN SJH.run_status=3 THEN 'Cancelled'
               ELSE 'Unknown'
          END

Any help or input is highly appreciated.

  Version: Microsoft SQL Server 2014 (SP3-GDR) (KB4505218) - 12.0.6108.1
  (X64)   May 29 2019 20:05:27   Copyright (c) Microsoft Corporation
  Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3
   (Build 9600: ) (Hypervisor)

When you do that in a job it basically ends the session and rolls back the transaction when the job finishes.  You can trace that using SQLTransaction event class in Profiler.
That's the reason why you do not see any open transaction/sessions or the work being done.


Create multiple output tables from one query I'm an inexperienced Postgres user and need help if someone can suggest it.

I require output tables of column 'county' so all counties in the table have their own table. I need independent tables for each to use separately. My source table is 'my_counties' and 'county' contains the variable county names I wish to separate.

CREATE OR REPLACE FUNCTION myfunction()
RETURNS SETOF my_counties AS
$BODY$
DECLARE
    county my_counties%ROWTYPE;
BEGIN

    FOR row in SELECT county DISTINCT my_counties LOOP
        EXECUTE 'SELECT * INTO myschema.test_' || row.county || ' from myschema.source WHERE source.county = ' || row.county;
    END LOOP;

RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE

The error I return is

  ERROR:  syntax error at or near "DISTINCT"

I'm an inexperienced Postgres user and need help if someone can suggest it.

A few things, this is a bad idea, you probably want Table Partitioning because essentially you're creating partitions of another table.

If you don't want to partition a table, you probably want a MATERIALIZED VIEW which sits as a table on the disk, but it can be refreshed from the query which is was created from with a simple command REFRESH MATERIALIZED VIEW.

What I think you want is something like this,

SELECT FORMAT ($$
  CREATE MATERIALIZED VIEW %I.%I AS
    SELECT *
    FROM my_countries
    WHERE country = %L;
  $$,
  'myschema',
  'test_' || country,
  country
)
FROM (
  SELECT DISTINCT county
  FROM my_counties
) AS t(country);

Run that in psql and then run \gexec, or loop through that result set and run EXECUTE in plpgsql.


Return Results From Select Statement and Store Count In Variable I am wanting to execute my select statement, but also store the returned row count into a variable.  This is how my DDL is set-up, how can I store the row count from my Select statement in my variable@returnedrows

Declare @returnedrows int;

Insert Into pinkpurplegreen (field1, field2, field3, field4, field5)
Select field1, field2, field3, field4, field5
FROM blue

Select * FROM pinkpurplegreen

You would need to select the @@ROWCOUNT variable

CREATE TABLE dba_152082 (field1 int NOT NULL);

INSERT INTO dba_152082 (field1) VALUES (1),(1), (2);

DECLARE @numberofrows int;

SELECT * FROM dba_152082;

SELECT @numberofrows = @@ROWCOUNT;

PRINT @numberofrows;

DROP TABLE dba_152082;

You should be able to verify the value of your variable in the messages tab after you run that.


Does PL/SQL support pointers, aliases, or references? I'm writing code for an environment I also support, and I'm trying to design it to handle future changes.

The current requirement is that two fields should be identical, but I used to be a user of the software I'm supporting, and I have a hunch there may be a requirement to separate the two fields at some point.

Let's start with this example:

DECLARE
   x_ VARCHAR2(32767);
BEGIN
   x_ := 'ABC';
   do_thing_1(x_);
   do_thing_2(x_);
END;

If someone changes their mind on the spec, I'll have to rewrite it like the code below.

DECLARE
   x_ VARCHAR2(32767);
   y_ VARCHAR2(32767);
BEGIN
   x_ := 'ABC';
   y_ := 'DEF';
   do_thing_1(x_);
   do_thing_2(y_);
END;

So, I have a couple of bad options. (1) I could write the code today for the original spec I have in my hands now, or (2) I could write the code with redundant variables to handle the changes I suspect are coming.

The problem with option (1) is that I'll have to rip out what I've done later, and some of the design intent I have in my head today will be lost without a lot of verbose documentation. The problem with option (2) is that, if the changes never come, the redundant variables amount to dead code, and memory and logical reads will be wasted during run time. Option (2) would be equivalent to passing a function argument by value when a reference parameter would be more efficient. Option (2), from a developer's perspective, is partially a violation of the DRY principle, too.

-- Change never materialized. x_ and y_ are redundant.
DECLARE
   x_ VARCHAR2(32767);
   y_ VARCHAR2(32767);
BEGIN
   x_ := 'ABC';
   y_ := 'ABC';
   do_thing_1(x_);
   do_thing_2(y_);
END;

Does something like this exist?

DECLARE
   x_ VARCHAR2(32767);
   y_ DBMS_POINTER_THINGY.REFERENCE;
BEGIN
   x_ := 'ABC';
   y_ := DBMS_POINTER_THINGY.POINTER_TO(x_);
   do_thing_1(x_);
   do_thing_2(y_);
END;

If I use a DEFINE, I'll lose the magic when it goes server-side.

If I use conditional compilation, can I assign a value to a directive within my code without having to set it at the session level?

DECLARE
   x_ VARCHAR2(32767);
   -- something defining $$y_ here?
   $$y_ := x_
BEGIN
   x_ := 'ABC';
   do_thing_1(x_);
   do_thing_2($$y_);
END;

Since it is a database you can create a table with key and value. You can create a package that has a CONSTANT which points to a key. You can create a function that takes a key and returns a value from your key/value table. Then you would just look up and use that values from the key/value table and rather than changing code you change data.


Optimizing PostgreSQL with Index I'm having a hard time trying to get PostgreSQL to use an index that I created. I understand that PostgreSQL is smart enough to determine when an index should be used because in most cases a seq scan is fine. However, I want to make my table to run faster. Can someone tell me what am I doing wrong with my index? My Query Plan time and cost does not change.

I know when I use the index in Oracle, it actually improves the performance. I'm not sure how I can get PostgreSQL to do the same.

This is my query:

select sum(lo_revenue), d_year, p_brand1
from lineorder, dwdate, part, supplier
where lo_orderdate = d_datekey
    and lo_partkey = p_partkey
    and lo_suppkey = s_suppkey
    and p_brand1 between 'MFGR#2221' and 'MFGR#2228'
    and s_region = 'ASIA'
    group by d_year, p_brand1
    order by d_year, p_brand1;

This is my index:

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

Here is what I get when I run an EXPLAIN:

QUERY PLAN

-------------------------------------------------------------------------------------------------------
------------------------------
 Sort  (cost=166357.23..166357.26 rows=13 width=17)
   Sort Key: dwdate.d_year, part.p_brand1
   ->  HashAggregate  (cost=166356.86..166356.99 rows=13 width=17)
         ->  Hash Join  (cost=6023.57..166338.87 rows=2398 width=17)
               Hash Cond: (lineorder.lo_orderdate = dwdate.d_datekey)
               ->  Hash Join  (cost=5928.06..166207.39 rows=2398 width=17)
                     Hash Cond: (lineorder.lo_suppkey = supplier.s_suppkey)
                     ->  Hash Join  (cost=5869.45..166084.74 rows=10682 width=21)
                           Hash Cond: (lineorder.lo_partkey = part.p_partkey)
                           ->  Seq Scan on lineorder  (cost=0.00..137603.98 rows=6001198 width=16)
                           ->  Hash  (cost=5865.00..5865.00 rows=356 width=13)
                                 ->  Seq Scan on part  (cost=0.00..5865.00 rows=356 width=13)
                                       Filter: (((p_brand1)::text >= 'MFGR#2221'::text) AND ((p_brand1)
::text <= 'MFGR#2228'::text))
                     ->  Hash  (cost=53.00..53.00 rows=449 width=4)
                           ->  Seq Scan on supplier  (cost=0.00..53.00 rows=449 width=4)
                                 Filter: ((s_region)::text = 'ASIA'::text)
               ->  Hash  (cost=63.56..63.56 rows=2556 width=8)
                     ->  Seq Scan on dwdate  (cost=0.00..63.56 rows=2556 width=8)
(18 rows)

Time: 0.821 ms

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

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

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


"New query window" shortcut for Oracle SQL Developer I'm used to Ctrl-N to give me a new query window in SSMS. How do I do it in Oracle?

In Oracle SQL Developer Application

Goto: Tools->SQL Worksheet

OR

ALT+F10