Complex dto based result sets with EF and MS SQL server ok lets say I have a collection of these ...

public class ReferenceLookup
{
    public string InvoiceNumber { get; set; }
    public CompanyReference Buyer { get; set; }
    public CompanyReference Supplier { get; set; }
}

I need a collection of these as results where any of the pieces exist ...

public class ReferenceLookupResult
{
    public SIHead SIHead { get; set; }
    public Company Buyer { get; set; }
    public Company Supplier { get; set; }
}

SIHead == invoice

Companies have related tables that contain external references for the company
I somehow (if possible) want to write a single linq query that can do this in a way that exposes me a queryable set of  ReferenceLookupResult if possible,
how I would get these pieces is quite complex though

for example here's how I currently get a collection of companies by reference ...

public IQueryable<Company> ByReferences(CompanyReference[] refs)
{
    var refStrings = refs.Select(r => r.System + "|" + r.Ident);
    return GetAll().Where(c => c.CompanyTaxIdentifiers.Any(cti => refStrings.Contains(cti.ExternalReferenceSystem.DisplayName + "|" + cti.Reference)));
}

... i think i need multiple collections of companies and a collection of invoices where one from each set makes up 1 result dto

here's how I get invoices by reference ...

public IQueryable<SIHead> GetAllByRefs(ICollection<InvoiceReference> refs)
{
    var keys = GetAll().Select(i => new { Id = i.SIHeadId, Number = i.InvoiceNumber, Supplier = i.SupplierCompanyAux.CompanyId }).ToList();
    var resultItemIds = keys.Where(k => refs.Any(r => r.InvoiceNumber == k.Number && r.SupplierId == k.Supplier)).Select(k => k.Id).ToArray();
    return GetAll(resultItemIds);
}

an invoice reference is an invoice number and a supplier reference
all invoices are at their root generated by suppliers.

Options considered so far

Create a sproc and call that (not queryable though I would think)
Create a view and add that to the context (EF Code First treats this like a normal entity set)

I'm thinking the latter might be the way to go, but in the past each time I've had a view for something I've ended up replacing it longer term with something LINQ based.

Using a view to produce the complex result set seems to be the best option in my case of the options presented in the question however ...

I found that with a slight architectural change in the business process I wouldn't end up in this situation at all, resulting 2 or 3 simpler requests rather than 1 complex one requiring a "special case" like this to be handled in the call handling logic.


Converting VARCHAR to TIMESTAMP with milleseconds yields "ERROR: date/time field value out of range" I have a VARCHAR column with the value '2018-07-05T16:14:27.1427' and I convert it to timestamp with this command:

select last_run,
       to_char(to_timestamp(last_run,'yyyy-MM-dd HH24:mi:ss') - interval '2 hour','yyyy-MM-dd"T"HH24:mi:ss"Z"') as new_last_run
from schedule_tasks;

Until the version 9.6.10 of PostgreSQL, all works good, but now I will migrate to 10.5 and the same command returns ERROR. The same issue happens on PostgreSQL 11.

What I realize is that, on 9.6, the first value of milliseconds when I have 4 digits is added to seconds; for example, 2018-07-05T16:14:27.1427 converted is 2018-07-05T16:14:28.427.

I don't know if this behavior is normal in the newer versions and I will have to handle it, or if this is a bug.

Handling of date and timestamp input has been made stricter in Postgres 10. Quoting the release notes:

  Make to_timestamp() and to_date() reject out-of-range input
  fields (Artur Zakirov)

  For example, previously to_date('2009-06-40','YYYY-MM-DD') was
  accepted and returned 2009-07-10. It will now generate an error.

Explains what you observed. You'll need to sanitize your input.

Here is the thread where these changes were discussed.

Casting seems to work fine with PostgreSQL 11.5

select '2018-07-05T16:14:27.1427',
       to_char(to_timestamp('2018-07-05T16:14:27.1427','yyyy-MM-dd HH24:mi:ss') - interval '2 hour','yyyy-MM-dd"T"HH24:mi:ss"Z"') as new_last_run


mongodb restore multiple collections with single command How do I restore multiple collections at once in mongodb.

I have tried

mongorestore -c Role -c UserAccount -c Permission -d movie-app dump/

and I got an error file dump is a directory, not a bson file

I can restore single collection at a time I have to  specify the bson file like

  mongorestore -c UserAccount -d movie-app dump/movie-app/UserAccount.bson

I need to know how do I restore multiple collection with one command.

Try these parameter to Include or exclude collections

--nsInclude
--nsExclude

During restoration, you can ignore the unwanted collections.

EG:

Dump all the collections

mongodump --db bhuvi --out /tmp

drwxr-xr-x 2 root root 4096 Dec 20 18:09 ./
drwxrwxrwt 9 root root 4096 Dec 20 18:25 ../
-rw-r--r-- 1 root root  125 Dec 20 18:09 coll1.bson
-rw-r--r-- 1 root root  125 Dec 20 18:09 coll1.metadata.json
-rw-r--r-- 1 root root   83 Dec 20 18:09 coll2.bson
-rw-r--r-- 1 root root  125 Dec 20 18:09 coll2.metadata.json
-rw-r--r-- 1 root root  125 Dec 20 18:09 coll3.bson
-rw-r--r-- 1 root root  125 Dec 20 18:09 coll3.metadata.json

My dump folder has 3 collections.

coll1
coll2
coll3

Restoring coll2 and coll3 collections

I want to restore coll2 and coll3 in the test db.

mongorestore --db test --nsExclude 'test.*1' /tmp/bhuvi/

2017-12-20T18:25:50.299+0000    the --db and --collection args should only be used when restoring from a BSON file. Other uses are deprecated and will not exist in the future; use --nsInclude instead
2017-12-20T18:25:50.299+0000    building a list of collections to restore from /tmp/bhuvi dir
2017-12-20T18:25:50.299+0000    reading metadata for test.coll3 from /tmp/bhuvi/coll3.metadata.json
2017-12-20T18:25:50.312+0000    restoring test.coll3 from /tmp/bhuvi/coll3.bson
2017-12-20T18:25:50.313+0000    reading metadata for test.coll2 from /tmp/bhuvi/coll2.metadata.json
2017-12-20T18:25:50.326+0000    restoring test.coll2 from /tmp/bhuvi/coll2.bson
2017-12-20T18:25:50.327+0000    no indexes to restore
2017-12-20T18:25:50.327+0000    finished restoring test.coll3 (3 documents)
2017-12-20T18:25:50.327+0000    no indexes to restore
2017-12-20T18:25:50.328+0000    finished restoring test.coll2 (2 documents)
2017-12-20T18:25:50.328+0000    done

Check the collections

> use test
switched to db test
> show collections
coll2
coll3

For restore collections with similar names

mongorestore --db test --nsInclude 'test.coll*' /tmp/bhuvi/

Restore collections with different pattern

There is no straight forward method for this, but we can create a loop to restore this.

create a file with list of collections that we need to restore

vi collections
#add the collections names and save.
coll1
coll2

shell script for restore in loop

#!/bin/bash
input="collcetions"
backup_path="/tmp/mydb"
while IFS= read -r col_name
do
  mongorestore --db sqladmin --collection $col_name $backup_path/$col_name.bson
done < "$input"

root@lin_sql1:/home/ubuntu# ./restore

2017-12-21T04:56:36.596+0000    checking for collection data in /tmp/mydb/coll1.bson
2017-12-21T04:56:36.596+0000    reading metadata for sqladmin.coll1 from /tmp/mydb/coll1.metadata.json
2017-12-21T04:56:36.616+0000    restoring sqladmin.coll1 from /tmp/mydb/coll1.bson
2017-12-21T04:56:36.677+0000    no indexes to restore
2017-12-21T04:56:36.677+0000    finished restoring sqladmin.coll1 (3 documents)
2017-12-21T04:56:36.677+0000    done
2017-12-21T04:56:36.686+0000    checking for collection data in /tmp/mydb/coll2.bson
2017-12-21T04:56:36.686+0000    reading metadata for sqladmin.coll2 from /tmp/mydb/coll2.metadata.json
2017-12-21T04:56:36.699+0000    restoring sqladmin.coll2 from /tmp/mydb/coll2.bson
2017-12-21T04:56:36.760+0000    no indexes to restore
2017-12-21T04:56:36.760+0000    finished restoring sqladmin.coll2 (2 documents)
2017-12-21T04:56:36.760+0000    done

Check the collections

> use sqladmin
switched to db sqladmin
> show collections
coll1
coll2

How do I restore multiple collections at once in mongodb

if you want to restore multiple collections of dumps at a time from your MongoDB dumps then simply use in mongo shell the below command.

mongorestore --verbose C:\locationofdumpsFiles

Note:- You must sure you are running the above command in BIN directory of MongoDB Server.

Suppose that in my environment the MongoDB installation location is such as

C:\Program Files\MongoDB\Server\3.6\bin

So, i will first open the 'command prompt' and go to the BIN location of the MongoDB. Because mongorestore shell is reside inside of MongoDB BIN foleder.

C:\Program Files\MongoDB\Server\3.6\bin>

Then run the command such as

 C:\Program Files\MongoDB\Server\3.6\bin>mongorestore --verbose C:\locationofdumpsFiles


How do you detect whether you have permission to query sys.dm_os_performance_counters? According to the sys.dm_os_performance_counters documentation:

  On SQL Server, requires VIEW SERVER STATE permission.

  On SQL Database Premium Tiers, requires the VIEW DATABASE STATE permission in the database. On SQL Database Standard and Basic Tiers, requires the Server admin or an Azure Active Directory admin account.

How can you check, from T-SQL, if you have these permissions?

Eventually we worked it out!

If you're connected to SQL Azure Database, and the database tier is Premium, check for the VIEW DATABASE STATE permission
If you're connected to SQL Azure Database, and the database tier is not Premium, check for EXECUTE permission on sys.dm_os_performance_counters
Otherwise, check for the VIEW SERVER STATE permission

The bit of this that isn't an obvious translation of the documentation is that "EXECUTE permission on sys.dm_os_performance_counters" is equivalent to "being a Server admin or an Azure Active Directory admin" in the case of non-Premium Azure SQL Database. This was just what we found by trial and error, and is not documented anywhere that I'm aware of.

DECLARE @Result VARCHAR(MAX)
SET @Result = ''

IF (SERVERPROPERTY('EngineEdition') = 5)
    BEGIN
        IF (DATABASEPROPERTYEX(DB_NAME(), 'Edition') = 'Premium')
            BEGIN
                IF NOT EXISTS(SELECT 1 FROM sys.fn_my_permissions(NULL, 'DATABASE') WHERE permission_name = 'VIEW DATABASE STATE')
                    SET @Result = 'You do not have VIEW DATABASE STATE permissions for this database.'
            END
        ELSE
            BEGIN
                IF NOT EXISTS(SELECT 1 FROM sys.fn_my_permissions('sys.dm_os_performance_counters', 'OBJECT') WHERE permission_name = 'EXECUTE')
                    SET @Result = 'You do not have server admin or Azure active directory admin permissions.'
            END
    END
ELSE
    BEGIN
        IF NOT EXISTS(SELECT 1 FROM sys.fn_my_permissions(NULL, 'SERVER') WHERE permission_name = 'VIEW SERVER STATE')
            SET @Result = 'You do not have VIEW SERVER STATE permissions within this instance.'
    END
RETURN @Result

In the end - just build a query for said counters ...
you should receive either results - or a matching error description if you lack the necessary rights/permissions.


Any ideas on how to parallelize mysql_upgrade? I am migrating from 5.5 to 5.6 and would like to speed up the mysql_upgrade process. I have thousands of databases per machine and 200 tables (innodb) per db. Any suggestion how to speed up the upgrade process?

I upvoted your question because this is a common problem, and a solution would be welcome. But unfortunately, I believe that there is no way to parellalize it. mysql_upgrade always operates on the whole server directory, there is no way to only upgrade one database, and it reads the system tables.

Suggest you switch to a topology that includes multiple masters.  Then upgrading, schema changes, etc, may be able to be done without downtime other than some flavor of failover.

With traditional replication have two servers replicating from each other, but write to only one.  Failover is making the other machine writable and pointing all write-clients to it.  Depending on the details of the upgrade, there may be issues about what gets replicated, when.

(Sorry, I proffer this for discussion, but without all the details.)


How to optimise T-SQL query using Execution Plan I have a SQL query that I have spent the past two days trying to optimise using trial-and-error and the execution plan, but to no avail. Please forgive me for doing this but I will post the entire execution plan here. I have made the effort to make the table and column names in the query and execution plan generic both for brevity and to protect my company's IP. The execution plan can be opened with SQL Sentry Plan Explorer.

I have done a fair amount of T-SQL, but using execution plans to optimise my query is a new area for me and I have really tried to understand how to do it. So, if anyone could help me with this and explain how this execution plan can be deciphered to find ways in the query to optimise it, I would be eternally grateful. I have many more queries to optimise - I just need a springboard to help me with this first one.

This is the query:

DECLARE @Param0 DATETIME     = '2013-07-29';
DECLARE @Param1 INT          = CONVERT(INT, CONVERT(VARCHAR, @Param0, 112))
DECLARE @Param2 VARCHAR(50)  = 'ABC';
DECLARE @Param3 VARCHAR(100) = 'DEF';
DECLARE @Param4 VARCHAR(50)  = 'XYZ';
DECLARE @Param5 VARCHAR(100) = NULL;
DECLARE @Param6 VARCHAR(50)  = 'Text3';

SET NOCOUNT ON

DECLARE @MyTableVar TABLE
(
    B_Var1_PK int,
    Job_Var1 varchar(512),
    Job_Var2 varchar(50)
)

INSERT INTO @MyTableVar (B_Var1_PK, Job_Var1, Job_Var2)
SELECT B_Var1_PK, Job_Var1, Job_Var2 FROM [fn_GetJobs] (@Param1, @Param2, @Param3, @Param4, @Param6);

CREATE TABLE #TempTable
(
    TTVar1_PK INT PRIMARY KEY,
    TTVar2_LK VARCHAR(100),
    TTVar3_LK VARCHAR(50),
    TTVar4_LK INT,
    TTVar5 VARCHAR(20)
);

INSERT INTO #TempTable
SELECT DISTINCT
    T.T1_PK,
    T.T1_Var1_LK,
    T.T1_Var2_LK,
    MAX(T.T1_Var3_LK),
    T.T1_Var4_LK
FROM
    MyTable1 T
    INNER JOIN feeds.MyTable2 A ON A.T2_Var1 = T.T1_Var4_LK
    INNER JOIN @MyTableVar B ON B.Job_Var2 = A.T2_Var2 AND B.Job_Var1 = A.T2_Var3
GROUP BY T.T1_PK, T.T1_Var1_LK, T.T1_Var2_LK, T.T1_Var4_LK

-- This is the slow statement...
SELECT
    CASE E.E_Var1_LK
        WHEN 'Text1' THEN T.TTVar2_LK + '_' + F.F_Var1
        WHEN 'Text2' THEN T.TTVar2_LK + '_' + F.F_Var2
        WHEN 'Text3' THEN T.TTVar2_LK
    END,
    T.TTVar4_LK,
    T.TTVar3_LK,
    CASE E.E_Var1_LK
        WHEN 'Text1' THEN F.F_Var1
        WHEN 'Text2' THEN F.F_Var2
        WHEN 'Text3' THEN T.TTVar5
    END,
    A.A_Var3_FK_LK,
    C.C_Var1_PK,
    SUM(CONVERT(DECIMAL(18,4), A.A_Var1) + CONVERT(DECIMAL(18,4), A.A_Var2))
FROM #TempTable T
    INNER JOIN TableA (NOLOCK) A ON A.A_Var4_FK_LK  = T.TTVar1_PK
    INNER JOIN @MyTableVar     B ON B.B_Var1_PK     = A.Job
    INNER JOIN TableC (NOLOCK) C ON C.C_Var2_PK     = A.A_Var5_FK_LK
    INNER JOIN TableD (NOLOCK) D ON D.D_Var1_PK     = A.A_Var6_FK_LK
    INNER JOIN TableE (NOLOCK) E ON E.E_Var1_PK     = A.A_Var7_FK_LK
    LEFT OUTER JOIN feeds.TableF (NOLOCK) F ON F.F_Var1 = T.TTVar5
WHERE A.A_Var8_FK_LK = @Param1
GROUP BY
    CASE E.E_Var1_LK
        WHEN 'Text1' THEN T.TTVar2_LK + '_' + F.F_Var1
        WHEN 'Text2' THEN T.TTVar2_LK + '_' + F.F_Var2
        WHEN 'Text3' THEN T.TTVar2_LK
    END,
    T.TTVar4_LK,
    T.TTVar3_LK,
    CASE E.E_Var1_LK
        WHEN 'Text1' THEN F.F_Var1
        WHEN 'Text2' THEN F.F_Var2
        WHEN 'Text3' THEN T.TTVar5
    END,
    A.A_Var3_FK_LK,
    C.C_Var1_PK

IF OBJECT_ID(N'tempdb..#TempTable') IS NOT NULL
BEGIN
    DROP TABLE #TempTable
END
IF OBJECT_ID(N'tempdb..#TempTable') IS NOT NULL
BEGIN
    DROP TABLE #TempTable
END

What I have found is that the third statement (commented as being slow) is the part that is taking the most time. The two statements before return almost instantly.

The execution plan is available as XML at this link.

Better to right-click and save and then open in SQL Sentry Plan Explorer or some other viewing software rather than opening in your browser.

If you need any more information from me about the tables or data, please don't hesitate to ask.

Before getting to the main answer, there are two pieces of software you need to update.
Required Software Updates
The first is SQL Server. You are running SQL Server 2008 Service Pack 1 (build 2531). You ought to be patched up to at least the current Service Pack (SQL Server 2008 Service Pack 3 - build 5500). The most recent build of SQL Server 2008 at the time of writing is Service Pack 3, Cumulative Update 12 (build 5844).
The second piece of software is SQL Sentry Plan Explorer. The latest versions have  significant new features and fixes, including the ability to directly upload a query plan for expert analysis (no need to paste XML anywhere!)
Query Plan Analysis
The cardinality estimate for the table variable is exactly right, thanks to a statement-level recompilation:

Unfortunately, table variables do not maintain distribution statistics, so all the optimizer knows is that there are six rows; it knows nothing of the values that might be in those six rows. This information is crucial given that the next operation is a join to another table. The cardinality estimate from that join is based on a wild guess by the optimizer:

From that point on, the plan chosen by the optimizer is based on incorrect information, so it's no wonder really that performance is so poor. In particular, the memory set aside for sorts and hash tables for hash joins will be far too small. At execution time, the overflowing sorts and hashing operations will be spilled out to physical tempdb disk.
SQL Server 2008 does not highlight this in execution plans; you can monitor the spills using  Extended Events or Profiler Sort Warnings and Hash Warnings. Memory is reserved for sorts and hashes based on cardinality estimates before execution starts, and cannot be increased during execution regardless of how much spare memory your SQL Server may have. Accurate row count estimates are therefore crucial for any execution plan that involves workspace memory consuming operations.
Your query is also parameterized. You should consider adding OPTION (RECOMPILE) to the query if different parameter values affect the query plan. You should probably consider using it anyway, so the optimizer can see the value of @Param1 at compilation time. If nothing else, this may help the optimizer produce a more reasonable estimate for the index seek shown above, given that the table is very large, and partitioned. It may also enable static partition elimination.
Try the query again with a temporary table instead of the table variable and OPTION (RECOMPILE). You should also try materializing the result of the first join into another temporary table, and run the rest of the query against that. The number of rows is not all that large (3,285,620) so this should be reasonably quick. The optimizer will then have an exact cardinality estimate and distribution statistics for the result of the join. With luck, the rest of the plan will fall into place nicely.
Working from the properties shown in the plan, the materializing query would be:
SELECT
    A.A_Var7_FK_LK,
    A.A_Var4_FK_LK,
    A.A_Var6_FK_LK,
    A.A_Var5_FK_LK,
    A.A_Var1,
    A.A_Var2,
    A.A_Var3_FK_LK
INTO #AnotherTempTable
FROM @MyTableVar AS B
JOIN TableA AS A
    ON A.Job = B.B_Var1_PK
WHERE
    A_Var8_FK_LK = @Param1;

You could also INSERT into a predefined temporary table (the correct data types are not shown in the plan, so I cannot do that part). The new temporary table may or may not benefit from clustered and nonclustered indexes.

I notice there should be a PK on @MyTableVar and agree that #MyTableVar is often better performing (esp. with larger number of rows).

The condition within the where clause

   WHERE A.A_Var8_FK_LK = @Param1

should be moved to inner join A AND'ed.  The optimizer is not clever enough in my experience to do this (sorry didn't look at plan) and it can make a huge difference.

If those changes do not show improvement I would next create another temp table of A and all the things it joins to constrained (nicely?) by A.A_Var8_FK_LK = @Param1 if that grouping makes logical sense to you.

Then create a clustered index on that temp table (either before or after creation) for the next join condition.

Then join that result to the few tables (F and T) that remain.

Bam, who needs a stinking query plan when the row estimates are off and are sometimes not easily improvable anyway).  I'm assuming you have proper indices which is what I would first check within the plan though.

A trace can show the tempdb spills which may or may not have a drastic impact.

Another alternative approach--that is faster to try out at least--is to order the tables from lowest number of rows (A) to highest and then begin adding the merge, hash, and loop to the joins.  When hints are present the join order is fixed as specified.  Other users wisely avoid this approach because it can hurt in the long run if the relative row counts change dramatically.  A minimum number of hints is desirable.

If you're doing many of these, perhaps an commercial optimizer is worth a try (or trial) and is still a good learning experience.


Associate BAK file with SQL Server database One of our users deleted a lot of data from one of our database applications, and I need to find a way to restore it.  I have a bak file and full recovery mode, but there is a catch.

This bak file is not recognized as belonging to this database, so I am not given the option of a timeline restore.  I can restore the bak as is, but it does not contain all of the data I need.  My only other option seems to be somewhat expensive (~$2000) recovery tools which can recover the data from the transaction logs.

Is there a way to convince SQL Server that this bak file really does belong to this database?

Do I have any other options?

This bak file is not recognized as belonging to this database

It may just be that you haven't performed a tail log backup on the target database.  But you should restore the backup and all the log files to a new database first to determine if it has the data you need.  Then you can either proceed with the restore, or manually copy the data from the new database.

  It was my assumption that I could use the LDF in conjunction with a BAK to reconstitute the database at any given point. Does it not work that way?

No.  The LDF will be overwritten in a restore.  You must move the transaction log records from the LDF to a log backup first.  That's why RESTORE over an existing database will fail if you haven't taken a Tail Log backup: otherwise you would lose data.

So before you start the restore sequence, ensure you've taken a Log Backup of the database.  This will move all the log records currently in the .LDF file into the log backup file and make them available for use in a point-in-time restore.

Use the BAK file to create a new database. Copy all the LDF before using any procedure to ensure you don't loose any data.

Here is a link that might help understand the issue What is the LDF file in SQL Server?

You can also try to read/recover from the LDF which might be tricky - I take no responsibility on using any of the procedures or tools in these links:

Reading the LDF Reading the transaction log in SQL Server – from hacks to solutions
Another example with a tool How to Read Data From LDF Files in SQL Server
A video example How to display and recover data from SQL log file


Issue when connecting to open shift via mysql workbench on Mac I am trying to connect to open shift with mysql workbench on an iMac. A colleague has shown me his setup on Windows which looks exactly as follows (other than the usernames / passwords changing)

However I keep running in to the following error and I have no idea how to fix it - I am 100% sure my password is ok, so don't really understand why it would say password yes.

Is there any special configuration differences needed between a Mac and PC setup, or any setting/config you can think of that might solve this issue?

The error message tells you that the MySQL server is not accepting a connection for user adminM4B1hN at the given host. You need to allow such a connection for that user (e.g. by adding an anyhost user entry (adminM4B1hN@%) for that user.

Also, keep in mind how the MySQL server is seen from the remote end of your SSH tunnel. If the tunnel ends on the same machine where the MySQL runs then the MySQL server address would be localhost not what you have given. The address is always resolved relative to the remote end of the tunnel.

The SSH Hostname must be the same as MySQL Hostname


SQL OrderBy When using an In() query Example query

SELECT * FROM Table WHERE ID in (5,3,4)

How do I adjust this query so that the order of the returned entries follows the input of the ID's (IE, 5 first, 3 second, 4 third).

Edit - To be clear the ID's a dynamically generated list that is in the right order.

Your best bet is going to be to split the list into a temp table.

I'm using Jeff Moden's DelimitedSplit8k.  You can find the code here.  Note it includes an ItemNumber column that is the values in order.

DECLARE @DelimString varchar(50) = '4,5,3'

SELECT * INTO #DelimList FROM dbo.DelimitedSplit8k(@DelimString,',')

SELECT Table.*
FROM Table
JOIN #DelimList
    ON Table.Id = #DelimList.Item
ORDER BY #DelimList.ItemNumber

This has the added benefit of being faster for large numbers of values in the delimited list.

Since the IDs are out of order (not DESCENDING or ASCENDING) I'd use UNION.

SELECT * FROM Table WHERE ID = 5
UNION ALL
SELECT * FROM Table WHERE ID = 3
UNION ALL
SELECT * FROM Table WHERE ID = 4

As a side note, using SELECT * in a UNION query isn't a good idea (and the rest of the time, it's debatable) - use the actual column names instead.

EDIT: It's been pointed out this won't necessarily work; and especially not with dynamic ID lists, like the edited question specifies.


What does the GO statement do in SQL Server? The GO statement from SQL Server caused me great curiosity and I don't really know how to use it properly.
I noticed that queries with or without GO don't return errors and seem to work the same, so what is the purpose of it and why should I use it?
And what is the difference between semicolons ; and GO at the end of a query?

GO is not a part of the TSQL language.  It's a batch separator used by SQLCMD and SSMS.

GO is not a Transact-SQL statement; it is a command recognized by the
sqlcmd and osql utilities and SQL Server Management Studio Code
editor.
SQL Server utilities interpret GO as a signal that they should send
the current batch of Transact-SQL statements to an instance of SQL
Server. The current batch of statements is composed of all statements
entered since the last GO, or since the start of the ad hoc session or
script if this is the first GO.
A Transact-SQL statement cannot occupy the same line as a GO command.
However, the line can contain comments.

SQL Server Utilities Statements - GO
It originated with the command line interfaces, and persists in sqlcmd to this day.  You can write a batch over multiple lines in the command line interface, and the batch isn't sent to the server until you type go.
1> select name, create_date
2> from sys.objects
3> where name = 'foo'
4> go
name                                                                                create_date
-------------------------------------------------------------------------------------------------------------------------------- -----------------------
foo                                                                                2020-07-20 09:56:42.393

(1 rows affected)
1>

You can configure what to use for a batch separator in SSMS. GO is the default, but it can be just about anything else.

From https://docs.microsoft.com/en-us/sql/t-sql/language-elements/sql-server-utilities-statements-go?view=sql-server-ver15 :
"GO signals the end of a batch of Transact-SQL statements to the SQL Server utilities."
A batch of statements is sometimes referred to as a query batch.  NOTE:  A query batch is not the same thing as a transaction batch.
Certain DDL statements (e.g. CREATE VIEW, CREATE FUNCTION) must be in a separate query batch from other statements.  The GO statement is used to end a query batch, so if you have a SQL script with a CREATE VIEW statement that is not at the beginning of the script, then you must insert a GO statement before that to close the previous query batch.
(NOTE:  An alternative is to place the DDL statement inside an EXECUTE.)
A ';' will not close a query batch.  It ends a SQL statement.


SQL column=ALL(query): Equivalent meaning I know that the expression:

WHERE column <> ALL(subquery)

could have been written as:

WHERE column NOT IN(subquery)

What would be the equivalent for

WHERE column = ALL(subquery)

I don’t think there would be any differences between dialects of SQL which support the ALL operator.

If not mistaken you can use a not exists predicate and negate the operator for ALL in the subquery. Something like:

SELECT ...
FROM T
WHERE T.x = ALL ( select y from U )

corresponds to

SELECT ...
FROM T
WHERE NOT EXISTS ( select 1 from U
                   WHERE NOT T.x = U.y )

I.e.

WHERE NOT EXISTS ( select 1 from U
                   WHERE T.x <> U.y )

In other words, for the predicate = ALL to hold there must NOT EXISTS a tuple that does satisfy NOT =

Note that this might not hold if nulls are taken into consideration. Example:

with t (n) as ( values (1),(1),(2) )
select * from t as t1
where not exists (select n from t as t2 where t1.n <> t2.n )

and:

with t (n) as ( values (1),(1),(2) )
select * from t where n = all (select n from t)

Both return 0 rows. However:

with t (n) as ( values (1),(1),(2),(null) )
select * from t where n = all (select n from t)

return 0 rows whereas:

with t (n) as ( values (1),(1),(2),(null) )
select * from t as t1 where not exists (select n from t as t2 where t1.n <> t2.n )

return 1 row.

Three-valid logic makes things a whole lot more complicated, and one needs to be even more careful than usual when doing query rewrites.

FWIW, there is an really interesting project that applies Homotopy Type Theory (HoTT) to pseudo SQL:

http://cosette.cs.washington.edu/

The idea is to be able to prove whether two queries are equvalent, and if not provide an example of situations where they are not. Any vendor that adopts this to there dialect of SQL will have an killer app for sure.

WHERE column = ALL(subquery)

It's a relatively useless construct, which is why it's a good question and seldom seen. Equivalence is a property. There is no reason it has to make sense, and sometimes it doesn't. For instance.

SELECT '{-0,+0}'::float[];
 float8
--------
 {-0,0}
(1 row)

That's zero being stored in two different ways. Yet IEEE 754, specifies they must compare to 0.

-- returns true.
SELECT 0::float = ALL('{-0,+0}'::float[]);

Most databases strive to never make two distinct things return true for an equality test, as it increase the chances for collision. But, ALL doesn't just work with =, for instance this is also totally valid,

SELECT 'fooBar' ILIKE ALL(ARRAY['%bar', 'foo%']);


How can I partitioning oracle table when the table still working? How can I partitioning oracle table when the table still working?

Explain:
- I need to partitioning table of Oracle but the table didn't partitioned before.
- I have many scheduler I can't close it all.

How can I partitioning table without closing all scheduled tasks?

I try it

BEGIN
dbms_redefinition.start_redef_table(
uname => 'kiku',
orig_table => 'pletest3_pn_kiku_api_db',
int_table => 'pletest4_pn_kiku_api_db');
END;

ERROR::

Error report:
ORA-12008: error in materialized view refresh path
ORA-14400: inserted partition key does not map to any partition
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 2
12008. 00000 -  "error in materialized view refresh path"
*Cause:    Table SNAP$_<mview_name> reads rows from the view
           MVIEW$_<mview_name>, which is a view on the master table
           (the master may be at a remote site).  Any
           error in this path will cause this error at refresh time.
           For fast refreshes, the table <master_owner>.MLOG$_<master>
           is also referenced.
*Action:   Examine the other messages on the stack to find the problem.
           See if the objects SNAP$_<mview_name>, MVIEW$_<mview_name>,
           <mowner>.<master>@<dblink>, <mowner>.MLOG$_<master>@<dblink>
           still exist.

Just copy your table with a new name and apply the partioning,
drop the original table and rename the copied file to the original table.


How do I join information schema to the table's data? I need to join a table using it's column name from the INFORMATION SCHEMA. Is this possible?

I actually found another way to work around it. I am using Innodb. For the structure it's kind of complicated, but this is the query I was working with :

SELECT c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
(
    SELECT *
    FROM
    (
        SELECT
            ct.field_name,
            ct.length,
            ct.description,
            IFNULL( mfc.hide, ct.hide) as hide,
            IFNULL(mfc.required, ct.required) as required,
            ct.example
        FROM xxxxxx.config_marketplace ct
        LEFT JOIN xxxxx.merchant_field_config mfc
            ON mfc.field_id = ct.id
            AND mfc.industry = 'MP'
            AND mfc.merchant_id = '1512MP5'
    ) as t
    WHERE hide != 'Y'
) b
ON LOWER(b.field_name) = c.COLUMN_NAME
WHERE
    c.TABLE_SCHEMA = 'XXXXX_1512MP5'
    AND c.TABLE_NAME = 'marketplace_custom';

Basically, config_marketplace holds the default configuration of the field names(column name) that will be shown, while merchant_field_config does the configurations of the fields I would like to hide based on each merchant's requirements. Lastly, marketplace_custom would be placed in the individual merchant's db with the column header which I would like to join to the data in that table.

PS. I know it's not a very good explanation.

Just out of curiosity, is this possible? I am working around it, but it will be a really good thing to know for future usage!

That depends upon the type of DB you actually use.

In NOSQL like MongoDB support Dynamic schemas to join the table using column name which results in embedded documents.


ORA-01031: insufficient privileges while connecting with sys as sysdba I installed Oracle database 10g as an administrator on a windows 10 machine, it worked fine, but when i try to connect sys as sysdba it gives me the error:

ORA-01031: insufficient privileges

I checked sqlnet.ora file and if SQLNET.AUTHENTICATION_SERVICES is NTS and it is:

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

I added the user to the ORA_DBA group and i checked with:

c:\Oracle>echo %username%

and

c:\Oracle>NET LOCALGROUP ORA_DBA

and the user is in the group.

I don't know what else i should do, anything will be helpful.

Are you connecting as

 sys as sysdba

or

 / as sysdba

?

If you are using the first option, is it prompting you for a password, and are you entering the password for the user?  Did you create a local Windows user account as the user, not the user id used to install the software?  Oracle recommends a user with administrators group install the software, and a local Windows user be defined for the sys and system accounts.


Error replicating password change in MySQL slave So I built a master-slave Mysql replication architecture in production because of a database FOO that I wanted to query (slave node) without affecting the performance of the main site (master node).

The thing is, I was afraid of someone breaking replication by inserting data in the slave which would conflict with data coming from master, so as soon as I created the slave node, I removed all privileged users (e.g. dba1, dba2) in the slave node and added a single readonly one (with the SELECT grant): readonlyUser.

The problem I have now is that suddenly dba1 has changed his password in the master node, and the result is this replication error:

Replication reported error 1396:
Error 'Operation ALTER USER failed for 'dba1'@'localhost'' on query.
Default database: 'mysql'.
Query: 'ALTER USER 'dba1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*DAB3A57F75AD41285552E115420DA2EFD756CD19''

Should I prevent the replication of the mysql DB then? I'm afraid that, if I do this, any structure changes for my FOO database will not be replicated, because the structure of the tables is stored in the mysql database, right?

Or should I just ignore errors of type 1396? Is error1396 always related to users or is it more broad?

Mysql Replication is pretty hard to manage, if anyone executed wrong query it'll affect the slave too. Use the below command to fix.

  Stop slave;

  SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

If you want to exclude this error all time, add the below line in my.cnf file.

If the error code is 1396 then

  slave-skip-errors = 1396


Maximum memory setting in SQL Server I'm running SQL Server 2008 and a web based application, on a single dedicated server, with only 2Gb of memory available.

As is noted elsewhere, SQL Server regularly takes up to 98% of physical memory, which appears to slow down the web application running on the server.

In Server Properties in SSMS, under Memory, Maximum Server Memory (in Mb) is set to: 2147483647

My question is, what would be the recommended number to put in the maximum server memory box, given the amount of memory I have available, and that the same server is also running the web application?

Additionally, is it safe to make a change to this number, while SQL Server is running?

Thank you for your advice.

I will answer the last question first: Yes, you can change it while the server is running without issue. If you want to change the value via SQL you can do it with the following query

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO

See this page for more details about setting memory on SQL server.

Your first question, unfortunately the answer is: I can't tell you, I'm not there.

There is a 1,000,000 things you need to factor in when allocating memory. How big are result sets from the queries, how often are they run, would a query that used to take 20 ms be ok to now take 200 ms?

Sql's defaults assumes that it is the only thing running on the server, so it just sets the memory to MAX_VALUE and it stops growing when all available memory is in use (and on dedicated hardware that is fairly close to what you want to happen(see Aarons comment for a possible caveat)). Normally any web server or other software interacting with the database would be on different hardware communicating to it over the network.

You really just need to just set it to a value you think is sane, and if your webserver is still memory choked lower it. If SQL is not giving you enough performance after you give the webserver the memory it needs you will need to either buy more ram or move the SQL to dedicated hardware.

Stick with the defaults in 99% of cases.  This will allow dynamic management of memory.


Back-end with encription and user-control for MS Access2003-VBA My client has an Access 2003 "multi-user" (<6) "database" (<3k records) with "encryption" located in a LAN folder and an easy Form I polished with VBA(DAO library). Access 2003 encryption can be broken in less than a second with very easy tools.
A decent level of encryption should be applied to the data (and maybe user control). My client needs an "easy" solution; something they can manage on their own (like they did with the access mdb file) without the help of engineers.
Some computers have access 2003, some Access 2007 (so 2007's marvellous and straight forward encryption isn't a solution). Access 2003 refuses to connect to an access 2007 database via ODBC and also stores the passwords for linked tables in plain text (and doesn't ask for them).

I have thought of the following:

Encripting individual fields. User generates a sha-512 hash that is substracted from every field to get the decrypted field. Problem: Manual editions of the Access table are common in this database.
MySQL in a computer-server, they connect to the database via ODBC. Or SQL Express (seems better with Access). Problem: Someone has to configure and manage the server periodically. It seems disproportionaly complex given the scarce amount of data and the not frequent use of them. sqlite (which also has an ODBC driver) doesn't have good encryption, it seems.
I believe a complex solution in their case would have greater security risks than a simpler one...
A mdb file inside a zip file, the decription consists of extracting the file when they need to use it. Would solve the problem but is not elegant or safe enough (if a hacker went into the remote folder when the database is opened he would gain access to all the data). The file is only opened once or twice every day.

What do you suggest? Do you have another ideas? I have thought many times and it seems I'll have to go for SQL Express 2008; I don't like this solution.
Thank you

I have talked to them. They consider moving to Access 2007, which provides data protection but no decent per-user control.

It's a bad solution. I will investigate JDBC-ODBC GPL bridges and modern GPL databases.

I hope it is ok to mention our third party product, NetLib Encryptionizer. It offers Transparent Data Encryption for any application, database or service on the Windows Platform, including Microsoft Access. You use either a point-and-click interface, or a script-based deployment for distribution.

I am the Founder and CTO of NetLib Security Inc.


PostgreSQL: PreparedStatement to execute set schema command I need to execute in a Java application a SET schema ? statement, where the placeholder is replaced by a value provided at runtime by the caller.

I assumed I could use PreparedStatement to overcome potential SQL injection, but if I try to execute a prepared statement I get a PSQLException with message

ERROR: syntax error at or near "$1"  Position: 18

If I do execute the SQL by just concatenating the schema name by doing:

public void executeSetSchema(Connection con, String schema){
  try{
     Statement stmt = con.createStatement();
     stmt.execute("SET SCHEMA '" + schema + "'");

  } catch(Exception){}
}

Then everything works as expected.

Clearly, this happens with all the latest releases of PostgreSQL

Why I cannot use a PreparedStatement and what should I do instead to prevent SQL injection here?

You can't pass identifiers as parameters. It's similar to select * from ?

However, you can use a function to set the search path:

public void executeSetSchema(Connection con, String schema) {
  try (
    PreparedStatement pstmt = con.prepareStatement("select set_config('search_path', ?, false)");
  ) {
     pstmt.setString(1, schema);
     stmt.execute();
  } catch(Exception) {
  }
}

I moved the prepareStatement() call into a try-with-resources so that the statement is closed properly.

I have working code with

     Statement stmt = conn.createStatement();
     stmt.execute("set search_path to '" + schema + "'");


What is the best way to store files metadata in DB? I have a task to create somethig like corporate data catalogue. Starting from the idea of data catalogue seems a little bit vague to me, but the question is  what is a good way to store files metadata like member_id, version etc? Usual row-wise format or JSON? I guess that json is not atomic and might be prone to  corruption, but db rows might be slightly more difficult to migrate to different instances due to the quanity of rows.

What is approx. size of the data? Your no. of rows being a concern here
How often does this data get updated? Consider data access and ACID transactions computation costs in your architecture.
Files metadata will have directories and subdirectories - which are dynamic in nature. Hence schematic representation(RDBMS table) may not be suitable. My initial impression says NoSQL may be a better candidate.
Choose the NoSQL database that suits best - Document DB, Key-Value pair, or Graph database if the files are interlinked (refer to each other) and you need to retrieve that relation
If you need to use RDBMS unless it is too old, many of them offer JSON capabilities inbuilt, please consider the same.


SQL Server mirrored database is stuck in recovery mode I have a database that is stuck in recovery mode for few days already. There are multiple threads about this, but the solutions there don't work for me.
Here is what I tried already:
RESTORE DATABASE [DBNAME] WITH RECOVERY
Error: Exclusive access could not be obtained because the database is in use.

ALTER DATABASE [DBNAME] SET OFFLINE WITH ROLLBACK IMMEDIATE
Error: ALTER DATABASE failed because a lock could not be placed on database 'DBNAME'
I get the same error when trying to set the db to SINGLE_USER

exec sp_who2 --> nothing that contains my database, so nothing that I can kill
(Or I need to look for something else ??)

I can't just stop the SQL-service, because it contains too many databases that can't go down.
Who knows what I can do to get the database out of recovery mode? At both the primary and mirror location is the database state 'In recovery'.
UPDATE
I found a process in sp_who2 with command DB STARTUP.
At sys.dm_tran_locks, I see this session has a resource_database_id for the database that is in recovery, so this keeps the database locked. Anyone knows how to fix this without stopping SQL Server?

Restart the db mirroring end point:

--To stop
ALTER ENDPOINT<Endpoint Name> STATE=STOPPED

--To start
ALTER ENDPOINT<Endpoint Name> STATE=STARTED

TECH note: if there are multiple databases, not just the one in error, then stopping and starting the enpoint affects all the databases on that endpoint. This can cause problems on a production system like SharePoint. To fix, go the other mirrored database server and enter the same commands:
 ALTER ENDPOINT  STATE=STOPPED
 ALTER ENDPOINT  STATE=STARTED

How to find the name of the end point?
select * from sys.endpoints  <- works for SQL 2012
The name will be of " type=4 " for mirroring.

Case:
Accidently When u run below command on Principal server instead of mirror server:
alter database  'Database_Name' set partner off

After running above command your DB goes in [mirror, disconnected] State on mirror server.

Resolution:

Step 1:Run below query on mirror
exec sp_resetstatus 'Database_Name'

Step 2: After running below command your DB will be available.
restore database 'Database_Name' with recovery

Note: Step 2 will take some time to recover.


SSMS 2016 Dark Theme Does anyone know how to change the color of the right click menu in SSMS 2016 with the dark theme enabled?

The Dark Theme is still under active development and the work-around to enable it should be used for testing only. If you are testing it, then check Microsoft Connect for the issues you find, if they have not been raised feel free to raise the issue yourself. You should expect problems with it until Microsoft enable it by default and as such I would recommend not using it until that point.

Here's how you can get the SSMS Theme for the editor.  It's like a Monokai setup for SSMS.

Works good.

https://mikesdatawork.wordpress.com/2017/07/07/dark-ssms-theme-ssms-2017/


Why does SQlite return incorrect sum? CREATE TABLE "Test" ("ItemQty" DOUBLE NOT NULL );

Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);
Insert Into Test VALUES (1.4);

Select Sum(ItemQty)
From Test;

Result: 15.400000000000002

Should be 15.4

The type DOUBLE is a floating point type represented in binary internally. Binary floating point numbers will produce "rounding errors" when converted to base 10. If you want precise numeric calculations with a decimal point, you'll have to use the NUMERIC or DECIMAL type.

To help you understand why floating point types have rounding errors when converting to base 10, please see this article:

https://stackoverflow.com/questions/2100490/floating-point-inaccuracy-examples

Also, just for your reference, the NUMERIC or DECIMAL types are going to be orders of magnitude slower at calculations compared to the DOUBLE type.

Also see Data Types in SQLite.

select printf('%.2d',Sum(ItemQty)) should do the job
See also the printf() core function


Update table where table name in select query itself I am having following select query

 (select   article.articleId ,'MUSIC' as type ,createddate
  FROM     MUSICarticle
  where    article.status=2
  and      article.createdBy=101
  order by createdDate ASC limit 1)
 UNION
 (select   article.articleId, 'SPORTS' as type ,createddate
  FROM     SPORTSarticle
  where    article.status=2
  and      article.createdBy=101
  order by createdDate ASC limit 1)
 UNION
 (select   article.articleId ,'DANCE' as type  ,createddate
  FROM     DANCE article
  where    article.status=2
  and      article.createdBy=101
  order by createdDate ASC limit 1)
 UNION
 (select   article.wikiTopicId as articleId ,'SINGING' as type ,createddate
  FROM     SINGING article
  where    article.status=2
  and      article.createdBy=101
  order by createdDate ASC limit 1)
 order by 3 ASC limit 1

and having output

articleid |  type   |        createddate
----------+---------|-------------------------
    1     |  MUSIC  | 2017-04-28 12:53:55.649

Now have to update MUSIC table where id =1 .

You could use a stored procedure or anonymous PL block to build dynamic SQL. See below for an example.

  DISCLAIMER: I must point out that dynamic SQL is almost never the way to go - the database cannot cache the query plans efficiently and it is much slower than conventional queries. Were we on Stack Overflow here I'd be more inclined to suggest you use a supplementary language with a switch statement or something to accomplish this.

do language plpgsql $$
declare
    _sql text;
    _r record;
begin
    for _r in (
        select   article.articleId ,'MUSIC' as type ,createddate
        FROM     MTDDiscussion article
        where    article.status=2
        and      article.createdBy=101
        order by createdDate ASC limit 1)
        UNION
        (select   article.articleId, 'SPORTS' as type ,createddate
        FROM     MTDOpinion article
        where    article.status=2
        and      article.createdBy=101
        order by createdDate ASC limit 1)
        UNION
        (select   article.articleId ,'DANCE' as type  ,createddate
        FROM     MTDSurvey article
        where    article.status=2
        and      article.createdBy=101
        order by createdDate ASC limit 1)
        UNION
        (select   article.wikiTopicId as articleId ,'SINGING' as type ,createddate
        FROM     WikiTopic article
        where    article.status=2
        and      article.createdBy=101
        order by createdDate ASC limit 1
    ) loop
        _sql = E'update ' || _r.type::text || E' set COLUMNNAME = \'' || _r.createddate::text || E'\'::date where id = ' || _r."articleId"::text;
        execute _sql;
    end loop;
end
$$;

What about this:`(replace FIELDNAME and fieldname with appropriate field names)

    update MUSIC
set FIELDNAME = xx.fieldname
 from (

 (select   article.articleId ,'MUSIC' as type ,createddate
  FROM     MTDDiscussion article
  where    article.status=2
  and      article.createdBy=101
  order by createdDate ASC limit 1)
 UNION
 (select   article.articleId, 'SPORTS' as type ,createddate
  FROM     MTDOpinion article
  where    article.status=2
  and      article.createdBy=101
  order by createdDate ASC limit 1)
 UNION
 (select   article.articleId ,'DANCE' as type  ,createddate
  FROM     MTDSurvey article
  where    article.status=2
  and      article.createdBy=101
  order by createdDate ASC limit 1)
 UNION
 (select   article.wikiTopicId as articleId ,'SINGING' as type ,createddate
  FROM     WikiTopic article
  where    article.status=2
  and      article.createdBy=101
  order by createdDate ASC limit 1)
 order by 3 ASC limit 1
 ) xx
where xx.type = 'MUSIC'
and music.id = xx.articleId;


Mutating Error , How to solve it CREATE OR REPLACE TRIGGER LOAN_RP
AFTER INSERT OR UPDATE ON FUND_DISBURSE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_FA          FUND_DISBURSE.FUND_AMT%TYPE;
V_RC          NUMBER (10);
V_AC          FUND_DISBURSE.AREA_CODE%TYPE;
V_BC          FUND_DISBURSE.BRANCH_CODE%TYPE;
V_CC          FUND_DISBURSE.COMP_CODE%TYPE;
V_DN          FUND_DISBURSE.DAFA_NO%TYPE;
V_PRN         NUMBER (15,2);
V_SC          NUMBER (15,2);
V_INSN        NUMBER (2) :=0;
V_INSD        FUND_DISBURSE.DISBURSE_DATE%TYPE;
V_RAMT        NUMBER (15,2);
BEGIN
select AREA_CODE, BRANCH_CODE, COMP_CODE,DAFA_NO,DISBURSE_DATE,FUND_AMT
into
V_AC,V_BC,V_CC,V_DN,V_INSD,V_FA
from FUND_DISBURSE
WHERE AREA_CODE = :NEW.AREA_CODE
and BRANCH_CODE = :NEW.BRANCH_CODE
and COMP_CODE = :NEW.COMP_CODE
and DAFA_NO = :NEW.DAFA_NO
AND DISBURSE_DATE =:NEW.DISBURSE_DATE
AND FUND_AMT = :NEW.FUND_AMT;
V_RAMT := V_FA*1.13;
V_INSD := (V_INSD-7) + 14;
for i in 1..50 loop
V_INSD := V_INSD+7;
V_RC := V_FA/1000*25;
V_PRN := V_RC/1.13;
V_SC := V_RC - V_PRN;
V_INSN := V_INSN + 1;
V_RAMT := V_RAMT - V_RC;
DBMS_OUTPUT.PUT_LINE(V_RC);
INSERT INTO F_REPAY_SCHEDULE (AREA_CODE,BRANCH_CODE, COMP_CODE,
DAFA_NO,INSTALL_NO,INSTALL_DATE,INSTALL_AMT, PRN_AMT, SC_AMT)
VALUES (:new.area_code,:new.branch_code,:new.comp_code,:new.dafa_no,V_INSN, V_INSD,
V_RC,
V_PRN,V_SC );
EXIT WHEN V_RAMT < V_RC;
END LOOP;
END;
/

Trigger complied, but can not insert any record.
 table is mutating, trigger/function may not see it

How can I solve it. Please suggest.

Sorry again. Thanks to ***mat and all* for helping me**.
I have understood.
Here my new code after correction.

CREATE OR REPLACE TRIGGER LOAN_RP
AFTER INSERT OR UPDATE ON FUND_DISBURSE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_RC          NUMBER (10);
V_PRN         NUMBER (15,2);
V_SC          NUMBER (15,2);
V_INSN        NUMBER (2) :=0;
V_RAMT        NUMBER (15,2);
v_insd        date;
BEGIN
V_RAMT := :NEW.FUND_AMT*1.13;
V_INSD := (:NEW.DISBURSE_DATE -7 ) + 14;
LOOP
V_INSD := V_INSD+7;
V_RC   := :NEW.FUND_AMT/1000*25;
V_PRN  := V_RC/1.13;
V_SC   := V_RC - V_PRN;
V_INSN := V_INSN + 1;
V_RAMT := V_RAMT - V_RC;
DBMS_OUTPUT.PUT_LINE(V_RC);
INSERT INTO F_REPAY_SCHEDULE (AREA_CODE,BRANCH_CODE, COMP_CODE,
DAFA_NO,INSTALL_NO,INSTALL_DATE,INSTALL_AMT, PRN_AMT, SC_AMT)
VALUES (:new.area_code,:new.branch_code,:new.comp_code,:new.dafa_no,V_INSN, V_INSD,
V_RC, V_PRN,V_SC);
EXIT WHEN V_RAMT < V_RC;
END LOOP;
END;
/

the code erro ORA-04091 is relative the transaction. In a trigger you dont can execute select, unless you declare a new transacation with clause PRAGMA AUTONOMOUS_TRANSACTION.

remember to run a commit or rollback to finalize transaction on the trigger.


Users who are connected into the database I need some help on how to find all the users that are connected to one of my database. I should check this on weekends to find out all the users that connected during the week.

Thank you.

You can check connected logins with the following query:

SELECT
    sdes.session_id,
    sdes.login_time,
    sdes.last_request_start_time,
    sdes.last_request_end_time,
    sdes.is_user_process,
    sdes.host_name,
    sdes.program_name,
    sdes.login_name,
    sdes.status,
    sdec.num_reads,
    sdec.num_writes,
    sdec.last_read,
    sdec.last_write,
    sdes.reads,
    sdes.logical_reads,
    sdes.writes,
    sdes.client_interface_name,
    sdes.nt_domain,
    sdes.nt_user_name,
    sdec.client_net_address,
    sdec.local_net_address
FROM
    sys.dm_exec_sessions AS sdes
    INNER JOIN sys.dm_exec_connections AS sdec ON sdec.session_id = sdes.session_id

However, this query will only retrieve currenty connected sessions. if you would like to know which logins were logged into the server, you need to set up a trace, enable the audit for error log or use extended events.

Here's a solution to enable audit for error log:

Using SSMS, right click your server and go to Properties.
On Security, below Login auditing click either "Successful logins only" or "Both failed and successful logins", depending if you also want failures or not.
Restart the SQL Server service (unfortunately).
Information will be stored on the Error Log. Data will be deleted over a time so make sure to review it's retention policy. To read the information on the error log you can use sys.sp_readerrorlog. The first parameter is the error log number (0 = current, 1 = first archive, etc.), second parameter determines the source to read (1 for error log) and the third parameter is the literal you want to filter info from:

EXEC sys.sp_readerrorlog 0, 1, 'login'

A more visual alternative to read the log is going to Management (on SSMS), then SQL Server Logs and double click on any log.

To see amount of logins by time you can use the following script (I'm using the "failed login" event because it's the one my server is tracking):

IF OBJECT_ID('tempdb..#FailedLogins') IS NOT NULL
    DROP TABLE #FailedLogins

CREATE TABLE #FailedLogins (
    LogDate DATETIME,
    ProcessInfo VARCHAR(100),
    Message VARCHAR(MAX))

INSERT INTO #FailedLogins (
    LogDate,
    ProcessInfo,
    Message)
EXEC sys.sp_readerrorlog 0, 1, 'login failed'

SELECT TOP 5 * FROM #FailedLogins AS F

Result:

LogDate                 ProcessInfo Message
2018-07-16 09:47:52.240 Logon       Login failed for user 'User1'. Reason: Password ...
2018-07-16 12:31:51.930 Logon       Login failed for user 'User1'. Reason: Password ...
2018-07-17 13:38:05.520 Logon       Login failed for user 'User2'. Reason: Could not ...
2018-07-17 13:42:14.130 Logon       Login failed for user 'User3'. Reason: Password ...
2018-07-17 13:42:28.690 Logon       Login failed for user 'User3'. Reason: Password ...

How to obtain user from these messages (this will highly depend on the message being shown on the error log):

DECLARE @Message VARCHAR(MAX) = 'Login failed for user ''User1''. Reason: Password did not match that for the login provided. [CLIENT: X.X.X.X]'

SELECT
    Original = @Message,
    UserStartPoint = SUBSTRING(@Message, 24, 100),
    TrimmedUser = LEFT(
        SUBSTRING(@Message, 24, 100),
        CHARINDEX(
            '''',
            SUBSTRING(@Message, 24, 100)) - 1)

Result:

Original: Login failed for user 'User1'. Reason: Password did not match that for the login provided. [CLIENT: X.X.X.X]
UserStartPoint: User1'. Reason: Password did not match that for the login provided. [CLIENT: X.X.X.X]
TrimmedUser: User1

Group by and count between a period:

DECLARE @PeriodStartDate DATE = '2018-07-16'
DECLARE @PeriodEndDate DATE = GETDATE()

SELECT
    Amount = COUNT(1),
    TrimmedUser = LEFT(
        SUBSTRING(F.Message, 24, 100),
        CHARINDEX(
            '''',
            SUBSTRING(F.Message, 24, 100)) - 1)
FROM
    #FailedLogins AS F
WHERE
    F.LogDate BETWEEN @PeriodStartDate AND @PeriodEndDate
GROUP BY
    LEFT(
        SUBSTRING(F.Message, 24, 100),
        CHARINDEX(
            '''',
            SUBSTRING(F.Message, 24, 100)) - 1)

Result:

Amount  TrimmedUser
7       User1
3       User3
1       User2

To get information about users that are currently connected to the database you can use the sp_who procedure.

Alternatively you could use sys.sysprocesses as described in this answer


MySql Database solution for specific web app problem Well, I need to create a comment system for posts that people add to my web app. This web app is just like facebook or myspace posts. Users add posts and people can comment on them. But the problem is I don't know how to store each comment in mysql table. How can I map each comment to the specific author.

I am new to mysql and how can I learn to solve things like that. Is there any books on the subject. I know some basic sql.

Thank you!

You could use Nested Set, but CRUD operations are more complex than other patterns, and there's no referential integrity.

The Closure Table pattern is simpler to CRUD and supports integrity, but requires an extra table.

Recursive Query is the simplest and supports integrity, but you would need a more advanced database like Postgres, SQL Server, or Oracle.

http://blog.mclaughlinsoftware.com/2009/04/03/t-sql-hierarchical-query/

http://karwin.blogspot.ca/2010/03/rendering-trees-with-closure-tables.html

I guess that everybody is going to tell you to take a quick look to database design (E-R Model), create your model and we'll help you to adapt it to your web app:
http://en.wikipedia.org/wiki/Entity-relationship_model
you can go to youtube there is a bunch of good tutorial how to design a relational database


Sqlplus not using NLS_LANG env variable I am trying to login to an Oracle 10g database vía sqlplus.

In the nls_session_parameters I need NLS_DATE_LANGUAGE in American, but I always have Spanish.

In the database server my NLS_LANG is SPANISH_SPAIN.WE8ISO8859P1.

In the client my NLS_LANG is AMERICAN_AMERICA.UTF8.

I tried setting this with an after logon trigger, but it does not work.
If I change this setting vía ALTER SESSION it works ok, but it is not a solution.

The database server is a Red Hat Enterprise 5.6
The client is a CentOS 7.5.
I am using Oracle Instant client 10.2.0.5.

I do not know how to continue...

I am presuming you are setting it to AMERICAN_AMERICA.* with your ALTER SESSION.

In my experience the language setting has to match between the client, server and nls_database_parameters.  It might be a big mission for your DBA to convert your character set to AMERICAN_AMERICA.UTF8 using the Database Migration tool, so would it be possible to make your change to NLS_LANG=SPANISH_SPAIN.WE8ISO8859P1 on the server?

Another problem is that WE8ISO8859P1 is a subset of UTF8 so when your RDBMS does its single-pass (or more) conversions when transporting data between the layers, you can get lossy results and see strange characters returning to your client.  So you would be better off to set your client and server locale to WE8ISO8859P1 to match your nls_database_parameters on the database.


Oracle installation fails due to ORA-00443 I hope that you help me to resolve my problem.
I'm trying to install Oracle 11g on RHEL 6.
All prerequisites are ok, but I'm getting such error during step "Creating and starting Oracle instance". The error text is ORA-00443: background process "DIAG" did not start

Sometimes installation fails on 8%, sometimes on 44%. It's very strange. I thought it's related to RAM but I have enough memory (10Gb).

I tried to retry installation several times and sometimes it shows me that another background process (not DIAG, but RDBM for example) did not start. It seems that second part of error is random. But the error code ORA-00443 remains the same.

Here are some information from logs which says nothing to me where is the root cause of a problem

[Thread-10] [ 2013-12-17 23:04:34.027 EET ] [CloneDBCreationStep.executeImpl:460]  calling zerodbid
[Thread-10] [ 2013-12-17 23:04:41.620 EET ] [CloneDBCreationStep.executeImpl:470]  Shutdown database
[Thread-10] [ 2013-12-17 23:04:41.622 EET ] [CloneDBCreationStep.executeImpl:492]  Startup ......nomount......
[Thread-14] [ 2013-12-17 23:07:03.433 EET ] [BasicStep.handleNonIgnorableError:430]  oracle.sysman.assistants.util.UIMessageHandler@1719f30:messageHandler
[Thread-14] [ 2013-12-17 23:07:03.433 EET ] [BasicStep.handleNonIgnorableError:431]  ORA-00443: background process "DIAG" did not start

I tried to find solution within all possible resources, but no luck. Please help to resolve issue. Thanks in advance.

Your OS is x86 but not amd64?
It seems SGA setting larger than system limit. Set your oracle memory under 2GB.


Audit Trail in Oracle 11gR2 I'm using Oracle 11g R2 on RHEL5.

How can I find what is being audited? I want to know what actions (like create, insert, delete, update etc.) are being audited or logged for a user, say scott.

This topic is documented in the Oracle Security Guide at the section Finding Information About Audited Activities.

In your case you should find useful the catalog view DBA_OBJ_AUDIT_OPTS which displays the objects on which auditing options have been enabled.

Here is described the view structure.


Install Oracle Database 12c Error unable to check whether the location specified is on CFS ps://i.stack.imgur.com/0DX0d.png

I am trying to install the Oracle database 12c on the Windows 10 Laptop.

Have tried disk C and disk E, got the same error.

And I tried to change the file properties, change the security to allow the full control.. And still got the same error message.

Anyone could help me with this? Thank you!!!!

there is two solutions for this:
1- install Oracle as administrator
2- create a folder in C or E drive then right click it, go to security tab, and change permission for the user (grant him owner access on that folder)

Go to C:\Windows\System32\drivers\etc and edit the host file.
Add your ip at the end of the file like 172.XX.XXX.1 oracle


Reporting services existing in SQL Cluster instance but unable to see on SSMS? This is first time I am working on SQL Failover Cluster environment and I installed Reporting services on SQL server Failover Cluster Instance both Active Nodes(Node1 & Node2) and configured through Reporting services configuration manager, when I was opened SSMS I haven't find any Reportserver and ReportserverTempDB Databases. Which means did I miss something in installation? or we have to configure those separately? When I was trying to add Reporting services feature I got an error like below.

If you used the Reporting services configuration manager to configure reporting services, which SQL instance did you point Reporting Services at?  Go back into Reporting services configuration manager and confrim which instance it's pointing to, this should be the instance that the ReportServer and ReportServerTempDB databases reside on.


How to export some rows of multiple tables linked between them according to one column value? I'm not sure my question is clear so let's me rephrase it. I have three tables organized like that:

I want to make a dump of these tables but I'm not interested by all the rows, I just want those who were last updated during the last month.
Is there a way with Data Pump Export to do it? I thought to the QUERY parameter but I can't use it because there is no last_updated_date in the SON and DAUGHTER tables.

For instance, if, in my MOTHER table, I have 3 rows:

id_mother = 1 ; last_updated_date = 01/01/17 ;
id_mother = 2 ; last_updated_date = 01/02/17 ;
id_mother = 3 ; last_updated_date = 01/03/17.

And, in my SON table, I have 2 rows:

id_son = 1 ; id_mother = 2 ;
id_son = 2 ; id_mother = 3.

If I want to export the rows of month of March, the process will return one row for the MOTHER table (id_mother = 3) and one row for the SON table (id_son = 2).

How can I handle it?

you can, if you use a correlated subquery. Check the manual.

QUERY=son:"where exists (
    select *
    from mother
    where
        mother.id_mother = ku$.id_mother and
        mother.last_updated > trunc(sysdate,'month')"

ku$represents the table you are actually exporting, so here it is the table son.

Starting with Oracle 12c you have the VIEWS_AS_TABLES parameter. Then you can export  the data of a view and store it in a table, e.g. with sqlplus do

create view new_son
as
select son.*
from son, mother
where
    mother.id_mother = son-id_mother and
    mother.last_updated > trunc(sysdate,'month')"

Now export this using

VIEWS_AS_TABLES='new_son'

If you import this it will create a table new_son.

If every rows in tables 'SON' and 'DAUGHTER' having mother_id value is captured, you can export using query parameter where mother_id is greater than requested date.

SELECT * INTO OUTFILE '/path/file.csv' FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM MOTHER
WHERE last_updated BETWEEN DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y-%m-01')
AND DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y-%m-31'))

SELECT A.* INTO OUTFILE '/path/file.csv' FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
FROM SON A
INNER JOIN mother B ON A.mother_id=B.mother_id WHERE B.last_updated
BETWEEN DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y-%m-01')
AND DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y-%m-31')

and same for DAUGHTER table, you will get dump of these tables.


Main Purpose of FOREIGN KEY When I did my first interview, I faced this confusing question.
By using join we can join the tables, but what is the main purpose of FOREIGN KEY in mysql?

Please suggest me the best answer.

The primary purpose of FOREIGN KEYs is to maintain Referential Integrity (RI). Check out any of the standard textbooks (Connolly, Elmasri, Garcia-Molina or Silberschatz amongst others).

This fundamental concept endures despite users' and programmers' best efforts to produce systems which render the concept null and void - by that I mean that they frequently try and subvert these (reasonable) requirements of a decent database system. See here for further details on that matter.

As explained in the Wiki, RI is

  a property of data which, when satisfied, requires every value of one
  attribute (column) of a relation (table) to exist as a value of
  another attribute (column) in a different (or the same) relation
  (table).

To give a simple example, that means that if you have a purchase order system with an order_header table with fields as follows:

CREATE TABLE order_header
(
  order_id INTEGER,
  client_id INTEGER,
  order_date DATE,
  <...other fields depending on your individual requirements...>
);

You might then have a client table like this

CREATE TABLE client
(
  client_id INTEGER,
  client_name VARCHAR(50),
  client_address VARCHAR(50),
  <.... other fields depending on your requirements...>
);

You would then add a CONSTRAINT to your order_header table with something like this:

ALTER TABLE order_header
    ADD CONSTRAINT fk_header_client
    FOREIGN KEY (client_id)
    REFERENCES client (client_id);
-- the exact syntax may differ per RDBMS.

This is telling the RDBMS that on the client_id column of the order_header table, there is now a constraint such that any client_id in order_header must already exist in the client table.

The referenced FOREIGN KEY columns(s) on the parent table need to have a UNIQUE constraint (or be the PRIMARY KEY).

Otherwise, it wouldn't make much sense - if there were 2 clients with the same client_id, how would the system be able to tell them apart - what address do you send the order to?

Note also that with many systems, you can create the CONSTRAINT in the CREATE TABLE statement.

Now, your system will be unable to create a purchase order for a client which does not exist. This doesn't mean that the wrong client can't be entered into the system, but it does help to ensure that the system is internally consistent.

Many optimizers can also take advantage of some optimizations when there are FOREIGN KEY constraints on the joined tables and produce better (more efficient) and simpler query plans.

Finally, a further advantage of using FOREIGN KEYs is that some systems also can (depending on your RDBMS) automatically create INDEXes on the FOREIGN KEY columns.

The purpose of the foreign key is to ensure referential integrity of the data.
In other words, only values that are supposed to appear in the database are permitted.
check this page please.
Of course we can join two tables, but JOIN will not ensure referential integrity.


"Access is denied." for xp_cmdshell for network share On a SQL 2014 SP 1 instance a SQL Agent job  I'm using xp_cmdshell to return a list of files from a network share. This has recently stopped working and returns "Access is denied."

The command is EXEC master..xp_cmdshell 'DIR "\\backups\marshall\master" /A:-D /S /B'

The command will work for a local folder.EXEC master..xp_cmdshell 'DIR "C:\Temp" /A:-D /S /B'

Running EXEC master..xp_cmdshell 'whoami' returns the Windows account that is used for the SQL Engine and SQL Agent. This account has Full Control for the backup folder. I've deleted and re-added permissions for this account. I can manually add and delete files from this folder.

I will be changing the process to no longer use xp_cmdshell, but can't do that today. Any ideas on what is going wrong?

Create a proxy account to run xp_cmdshell and give the proxy access to your share.

Link


MySQL:- Fetch records between two specific months and years I need a Query to fetch records between two specific months and years also, Presently I am fetching only current year records like this

SELECT material_status, COUNT(*) c
FROM purchase_order
WHERE YEAR(date_created) = YEAR(CURDATE()) AND material_status='Closed';

But I want the records between Current year "April Month" to Upcoming year April Month" means 2016-April to 2017-April .

Try this :

    SELECT material_status, COUNT(*) c
    FROM purchase_order
    WHERE date_created
         between str_to_date(CONCAT(YEAR(CURDATE()),'-04-01'),'%Y-%m-%d')
            AND DATE_ADD(str_to_date(concat(YEAR(CURDATE()),'-04-01'),'%Y-%m-%d'),INTERVAL 1 YEAR)
     AND material_status='Closed'
     GROUP BY 1;

But: BETWEEN is inclusive.  If date_created is really a DATE, then this includes 4/1/2017, but not the rest of 4/2017. Use last_day() function on to date, i.e.

last_day( str_to_date(CONCAT(YEAR(CURDATE()),'-04-01'),'%Y-%m-%d'))

SQL:
WHERE date_field >= '2018-04-01 00:00:00' AND date_field < '2018-04-01 00:00:00'
To make it generic, we need to conditionally change Year based on current month
WHERE date_field >= (
    SELECT
        CASE WHEN MONTH(CURRENT_DATE)>= 4 THEN concat(
            YEAR(CURRENT_DATE),
            '-04-01 00:00:00'
        ) ELSE concat(
            YEAR(CURRENT_DATE)-1,
            '-04-01 00:00:00'
        ) END
)
AND date_field < (
    SELECT
        CASE WHEN MONTH(CURRENT_DATE)>= 4 THEN concat(
            YEAR(CURRENT_DATE)+ 1,
            '-04-01 00:00:00'
        ) ELSE concat(
            YEAR(CURRENT_DATE),
            '-04-01 00:00:00'
        ) END
)


Best way to document database mappings for migrations I'm working on a project that includes mapping database elements for a migration and I wanted to know what tools others were using to do this?

Excel is a very flexible way to document simple mappings but I was wondering if anybody had a specific methodology they followed or other tools they use that they could recommend?

The most common way to do this Excel, but it has it's tradeoffs:

Very difficult to update the spreadsheet if either side of the migration changes (due to missing something, something changing, etc.)
Excel is harder to use by the people implement the rules documented in it.
Change tracking is nearly impossible if you have multiple people collaborating on the document.

I prefer to use data lineage features of data modelling tools such as ER/Studio, ERwin or PowerDesigner to track the transformations required for migration from one data store to another.  I get all the benefits of RE and compares to the source and target, plus none of the tradeoffs mentioned above.  It really makes a difference.

With my personal experience working with a giant healthcare organization where multiple payer and provider data comes from multiple sources and it really becomes a togh task to document and map them using excel spreadsheet. We were recommended to do a POC by our Director for AnalytiX Mapping Manager (www.analytixds.com) whom he met at a conference. We used a live use case and the results were outstanding.The solution is 100% metadata driven accessible from a web-browser. We have used it at enterprise level. It scans and organizes metadata from wide variety of databases, data models, flat files and unstructured data and identify upstream and downstream dependencies with its impact analysis and data lineage. The GUI is user friendly with its easy Drag/Drop user interface to create mapping specifications. We not only build mapping and document but also auto Generate ETL jobs for Informatica and SSIS ETL tools used internally. You can easily import your legacy spreadsheets and enforce mapping standards with built in customizable mapping templates and have full version control.


SSMS constraint script: Purpose of second alter table statement? SSMS scripts Foreign Key constraints as two statements:

ALTER TABLE {table}
WITH CHECK
ADD CONSTRAINT {constraintname} {constraint spec}
GO

ALTER TABLE {table}
CHECK CONSTRAINT {constraintname}
GO

What is the purpose of the second statement?

The second line is redundant but I agree the overuse of CHECK in that syntax is confusing.

According to the ALTER TABLE documentation the existing data is checked anyway if the WITH CHECK ADD option is used and the newly added constraint is enabled.

  WITH CHECK | WITH NOCHECK
  Specifies whether the data in the table is
  or is not validated against a newly added or re-enabled FOREIGN KEY or
  CHECK constraint. If not specified, WITH CHECK is assumed for new
  constraints, and WITH NOCHECK is assumed for re-enabled constraints.

The second line enables the constraint, again from the documentation, but that constraint was already enabled.

  { CHECK | NOCHECK } CONSTRAINT   Specifies that constraint_name is
  enabled or disabled. This option can only be used with FOREIGN KEY and
  CHECK constraints. When NOCHECK is specified, the constraint is
  disabled and future inserts or updates to the column are not validated
  against the constraint conditions. DEFAULT, PRIMARY KEY, and UNIQUE
  constraints cannot be disabled.

However, the constraint is already enabled as can be evidenced from this code:

CREATE TABLE [dbo].[constraintdemo](
    [constraintfield] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[constraintdemo]  WITH CHECK ADD  CONSTRAINT [CK_constraintdemo] CHECK  (([constraintfield]>(0)))
GO

INSERT INTO constraintdemo (constraintfield) VALUES (-1)

Which results in:

  Msg 547, Level 16, State 0, Line 12   The INSERT statement conflicted
  with the CHECK constraint "CK_constraintdemo". The conflict occurred
  in database "PlayGround", table "dbo.constraintdemo", column
  'constraintfield'.   The statement has been terminated.

See this dbfiddle where you can run the above code

If you want evidence that the existing data is effectively validated by the WITH CHECK you can run this code:

CREATE TABLE [dbo].[constraintdemo](
    [constraintfield] [int] NULL
) ON [PRIMARY]

GO

INSERT INTO constraintdemo (constraintfield) values (-1),(-2)
GO

ALTER TABLE [dbo].[constraintdemo]  WITH CHECK ADD  CONSTRAINT [CK_constraintdemo] CHECK  (([constraintfield]>(0)))
GO

Which returns:

  (2 row(s) affected)   Msg 547, Level 16, State 0, Line 21   The ALTER
  TABLE statement conflicted with the CHECK constraint
  "CK_constraintdemo". The conflict occurred in database "PlayGround",
  table "dbo.constraintdemo", column 'constraintfield'.

Again: dbfiddle

In this example it doesn't matter if it's a check constraint or a foreign key constraint as evidenced by this somewhat more complicated example (note that I commented out the second line):

CREATE TABLE [dbo].[keytable](
    [key] [nchar](10) NOT NULL,
 CONSTRAINT [PK_keytable] PRIMARY KEY CLUSTERED
(
    [key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[valuetable](
    [key] [nchar](10) NULL,
    [value] [nchar](10) NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[valuetable]  WITH CHECK ADD  CONSTRAINT [FK_valuetable_keytable] FOREIGN KEY([key])
REFERENCES [dbo].[keytable] ([key])
GO

--ALTER TABLE [dbo].[valuetable] CHECK CONSTRAINT [FK_valuetable_keytable]
GO

INSERT INTO keytable ([key]) VALUES (1),(2);
GO

INSERT INTO valuetable ([key],[value]) VALUES (3,'test');
GO

This still results in an error showing the foreign key is enabled:

  Msg 547 Level 16 State 0 Line 1   The INSERT statement conflicted with
  the FOREIGN KEY constraint "FK_valuetable_keytable". The conflict
  occurred in database "fiddle_7378e515ee284b358b4e2edbc07d1329", table
  "dbo.keytable", column 'key'.   Msg 3621 Level 0 State 0 Line 1
  The statement has been terminated.

Dbfiddle here

And again we can turn things around to prove that it checks existing data on creation using this code:

CREATE TABLE [dbo].[keytable](
    [key] [nchar](10) NOT NULL,
 CONSTRAINT [PK_keytable] PRIMARY KEY CLUSTERED
(
    [key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[valuetable](
    [key] [nchar](10) NULL,
    [value] [nchar](10) NULL
) ON [PRIMARY]

GO
INSERT INTO keytable ([key]) VALUES (1),(2);
GO

INSERT INTO valuetable ([key],[value]) VALUES (3,'test');
GO

ALTER TABLE [dbo].[valuetable]  WITH CHECK ADD  CONSTRAINT [FK_valuetable_keytable] FOREIGN KEY([key])
REFERENCES [dbo].[keytable] ([key])
GO

Which returns:

  Msg 547 Level 16 State 0 Line 1   The ALTER TABLE statement conflicted
  with the FOREIGN KEY constraint "FK_valuetable_keytable". The conflict
  occurred in database "fiddle_59761b4587c14f2b96b8029a10de6229", table
  "dbo.keytable", column 'key'.

dbfiddle here

When you add a FK constraint to a table with data already in it, it's untrusted.  New data will be verified, but existing data will not be.  The second command tells the engine to verify that the constraint is valid (no violations) and then it is marked as trusted.  This article, from Brent Ozar (not affiliated) goes into more detail.  https://www.brentozar.com/blitz/foreign-key-trusted/


Truncate parent table in Oracle when child table is empty Suppose I have a parent table parent referenced by a child table child. The table parent is populated but child is not. Attempting to truncate parent results in

 ORA-02449: unique/primary keys in table referenced by foreign keys

Is there a way to hint to the DBMS that child is empty, so that the foreign key constraint doesn't need to be disabled?

No, there is no such hint.
What you can do is

alter table {child_table} disable constraint {fk_constraint_name};
truncate table {parent_table};
alter table {child_table} enable constraint {fk_constraint_name};

There is no way round the restriction with truncate as other answers have indicated, but you can of course use delete without disabling the constraints:

delete from parent;

Note that 'delete' and 'truncate' have different side-effects


Copy a view from one database to another database on the same server I want to copy a view from one database to another database, on the same server. Both databases are structured exactly the same, except for about 8 views added in one.

Basic method:

Right click on the view in SQL Server Management Studio
Save as script to new query window
Change context to target database
Execute script

This is basically correct, but this will not preserve permissions on the views. A better way is to:

Right-click the database
Choose Tasks -> Generate Scripts -> Choose Objects -> Select specific database objects
Choose your views -> next
Click the [Advanced] button and then [Script Object-Level Permissions] should be set to True (default is False)

Another way you can execute below query;

select * into [destination_db_name].[dbo].[viewname]
from [source_db_name].[dbo].[viewname]

Hope it helps.


Online resource for writing Access SQL statements I am looking for an online resource for writing Access SQL statements. I write queries using SQL because the UI puts limitations on what can be queried. I have been using w3schools for reference, however MS-Access sometimes has slightly different syntax, so when I run the query I get an error. Then I end up wasting time trying to figure out what I did wrong only to find out that Access does it differently.

Here is Microsoft's page about Access SQL syntax:

http://office.microsoft.com/en-us/access-help/access-sql-basic-concepts-vocabulary-and-syntax-HA010256402.aspx

and here is a tutorial about Access SQL syntax for Access 2000.  A bit dated, but the concept is still there:

http://www.cwnresearch.com/resources/databases/access/tutorials/access2000/SQLQueries/SQLQueries.html

There's not really a good online "build my query for me" resource. It's rather the reason most of us goto school and study mathy concepts and learn how to do these things with lots of study and peer review. I realize this doesn't help you, but it helps to understand the resounding "NO" you're going to get.

Now you do realized Microsoft has a visual query editor right? Kind of click and drag sort of builder. I figure you do.

I suggest asking a specific question about a specific query and specifying that you don't understand the problem for more help in the future.


DBCC CHECKDB Results: page ID contains an incorrect page ID in its page header DBCC CHECKDB results for 'tablename':

  Msg 8909, Level 16, State 1, Line 1
     Table error: Object ID 1637580872, index ID 1, partition ID 72057596516040704, alloc unit ID 72057594048020480 (type LOB data), page ID (23:853329) contains an incorrect page ID in its page header. The PageId in the page header = (0:96).

  Msg 8928, Level 16, State 1, Line 1
     Object ID 1637580872, index ID 1, partition ID 72057596516040704, alloc unit ID 72057594048020480 (type LOB data): Page (23:853329) could not be processed.  See other errors for details.

  Msg 8965, Level 16, State 1, Line 1
     Table error: Object ID 1637580872, index ID 1, partition ID 72057596516040704, alloc unit ID 72057594048020480 (type LOB data). The off-row data node at page (23:853329), slot 0, text ID 12904397012992 is referenced by page (16:840512), slot 0, but was not seen in the scan.

This is the only table I have a problem with.

As this issue occur when purge some old data so we don't have enough old backup to say clean. Restore option may not possible.

Now we are looking into repair. Is single table repair possible?

Can we rebuild index as problem is with clustered index?

Never use REPAIR_ALLOW_DATA_LOSS.  I would do a database restore before that.

I suggest following this order:

REPAIR_FAST (works 1 of 10 times).
REPAIR_REBUILD (works up to 50% of the time).
Restore database.

Full syntax:

DBCC CHECKALLOC
[
    ( database_name | database_id | 0
      [ , NOINDEX
      | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
    )
    [ WITH
        {
          [ ALL_ERRORMSGS ]
          [ , NO_INFOMSGS ]
          [ , TABLOCK ]
          [ , ESTIMATEONLY ]
        }
    ]
]

See if you can select into another table:
select * into newtable from oldtable

If that is not possible, and you do not have a good backup, and you have COMPLETELY RUN OUT OF ANY OTHER OPTIONS, then you can run DBCC CHECKTABLE (tablename, REPAIR_ALLOW_DATA_LOSS)
http://technet.microsoft.com/en-us/library/ms174338(v=sql.110).aspx

WARNING!!!! If you run DBCC CHECKTABLE with the REPAIR_ALLOW_DATA_LOSS, there is a very high probability that you WILL LOSE DATA.  Also, there is no guarantee that DBCC CHECKTABLE (tablename, REPAIR_ALLOW_DATA_LOSS) will be able to repair the corruption.

BEFORE attempting ANY repair, shut down SQL Server and copy the .mdf and .ldf files off to a safe place, so you can always get back to your starting point.


Speeding up a simple select query on Mariadb I have a 100k rows table with the following structure:

+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(11)          | NO   | PRI | NULL    | auto_increment |
| user_id  | int(11)          | NO   | UNI | NULL    |                |
| success  | int(10) unsigned | NO   |     | NULL    |                |
| fail     | int(10) unsigned | NO   |     | NULL    |                |
| points   | int(11)          | NO   | MUL | NULL    |                |
+----------+------------------+------+-----+---------+----------------+

This table holds users' scores in a game. All fields' values other than id's and user_id's are constantly changing on every user event.

A simple select used for displaying the top scoring users on this table runs in 0.3s on production.

MariaDB [db]> select user_id, points from state where points > 100000 ;
...
119 rows in set (0.31 sec)
MariaDB [db]>show profile for query 3;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000090 |
| checking permissions | 0.000018 |
| Opening tables       | 0.000030 |
| After opening tables | 0.000014 |
| System lock          | 0.000015 |
| Table lock           | 0.000013 |
| After table lock     | 0.000016 |
| init                 | 0.000027 |
| optimizing           | 0.000021 |
| statistics           | 0.000029 |
| preparing            | 0.000034 |
| executing            | 0.000012 |
| Sending data         | 0.307494 |
| end                  | 0.000041 |
| query end            | 0.000018 |
| closing tables       | 0.000036 |
| freeing items        | 0.000015 |
| updating status      | 0.000038 |
| logging slow query   | 0.000068 |
| cleaning up          | 0.000034 |
+----------------------+----------+

For the points column is ever changing, I did not want to create an index for it. And this behaviour is expected. But a full select, without a where condition runs for 0.4s either.

MariaDB [db]> select user_id, points from state;
...
126851 rows in set (0.40 sec)

If I create an index for the points column, it will most probably result in great amount of IO operations on the server, but It will speed up the top scores list. If I filter and sort the list in server side code, I can speed up the list, but this time mariadb does not provide the result in a shorter time.

How can I speed up this simple select query?

| Sending data         | 0.307494 |

Most of your time is spend on transporting results from server to your client.

use compression (MYSQLI_CLIENT_COMPRESS)
get faster connection
select less data


Beginner problems administering users on mysql Very new MySQL user here. Two questions for the community.

I seem to have created an account that I can't delete/drop.
I can't seem to get MySQL to actually require newly created accounts to require passwords even when I specify them during creation.

I ran myisamchk and corrupton doesn't seem to be an issue. Logs below.

mysql -u root -p
use mysql
select * from db\G;

*************************** 3. row ***************************
      Host: localhost
      Db: projectdb
      User: project
      Select_priv: Y
      Insert_priv: Y
      Update_priv: Y
      Delete_priv: Y
      Create_priv: Y
        Drop_priv: Y
       Grant_priv: Y
      References_priv: Y
       Index_priv: Y
       Alter_priv: Y
Create_tmp_table_priv: Y
 Lock_tables_priv: Y
 Create_view_priv: Y
   Show_view_priv: Y
Create_routine_priv: Y
 Alter_routine_priv: Y
     Execute_priv: Y
       Event_priv: Y
     Trigger_priv: Y

mysql> drop user project;
ERROR 1396 (HY000): Operation DROP USER failed for 'project'@'%'

~~~~~

CREATE USER project123 IDENTIFIED BY 'project123';
Query OK, 0 rows affected (0.00 sec)

GRANT ALL ON projectdb.* TO project123 IDENTIFIED BY 'project123';
Query OK, 0 rows affected (0.00 sec)

quit;

MySQL then proceeds to let me log in without a password.

macbook-pro-8: $ /usr/local/mysql/bin/mysql -u project123
Welcome to the MySQL monitor.  Commands end with ; or \g.

What am I doing wrong?

You obviously have anonymous users in mysql.user

Just run the following:

DELETE FROM mysql.user WHERE user='';
DELETE FROM mysql.user WHERE host='';
FLUSH PRIVILEGES;

I have past posts on this subject

Aug 14, 2011 : Cannot drop anonymous user from mysql.user
Jan 18, 2012 : MySQL error: Access denied for user 'a'@'localhost' (using password: YES)
Feb 17, 2012 : MySQL : Why are there "test" entries in mysql.db?
Apr 09, 2012 : Mysql users deleted
Jun 09, 2012 : Why ALL PRIVILEGES ON `db`.* gives access to all databases?
Jun 26, 2012 : Best practices to secure mysql

Another observation: You tried to run mysql> drop user project;

By mysql's viewpoint, a user is identified as user@host

To see all defined users, please run this query:

SELECT CONCAT('''',user,'''@''',host,'''') MySQLUser FROM mysql.user;

You should see something like this:

+--------------------------+
| MySQLUser                |
+--------------------------+
| 'advdb'@'%'              |
| 'anadb'@'%'              |
| 'dmvadb'@'%'             |
| 'dmvanswers'@'%'         |
| 'dmvdb'@'%'              |
| 'dmvqnadb'@'%'           |
| 'icdb'@'%'               |
| 'jjohnston'@'%'          |
| 'localdb'@'%'            |

Thus, when you drop a user, it is best to use this style format as shown.

For example, to drop user icdb, you must run

DROP USER 'icdb'@'%';

Have a look at your user table in mysql.

Try which may have some global DB privileges.

SELECT Host,User,Password FROM mysql.user;

I am sure this is not recommended anymore but I manually remove records from these tables and call

FLUSH PRIVILEGES;


ORACLE 11g Know Insert Record Details which Failed to insert I have started auditing insert records by user on failure to any table in my oracle 11g Database. I have used following command to do the same.

AUDIT INSERT ANY TABLE BY SHENA BY ACCESS WHENEVER NOT SUCCESSFUL;

I would like to know whenever the record insert will fail, Can i know what was the records which failed to insert into table.

Where we can see such information. Or if you know any other way of auditing of the same please suggest. One way which i know is to write a trigger on insert. In that trigger handle insert failure EXCEPTION and save those values to some table.

Use SQL Loader Utility with following control file format.

options(skip=1,rows=65534,errors=65534,readsize=16777216,bindsize=16777216)
load data
infile 'c:\users\shena\desktop\1.txt'
badfile 'C:\Users\shena\Desktop\test.bad'
discardfile 'C:\Users\shena\Desktop\test.dsc'
log 'C:\Users\shena\Desktop\test.log'
append
into table ma_basic_bd
fields terminated by '|' optionally enclosed by '"' trailing nullcols
(fs_perm_sec_id,
"DATE" "to_date(:DATE,'YYYY-MM-DD')",
adjdate "to_date(:adjdate,'YYYY-MM-DD')",
currency,
p_price,
p_price_open,
p_price_high,
p_price_low,
p_volume)

You are requested to use the conventional path loading so that we can get the rejected(rejected because of datatype mismatch and business rule violation) records in .bad file. Conventional path loading is a default option.

Following URL can be used for the detailed knowledge.

https://youtu.be/eovTBGAc2RI

Total 4 videos are there. Very helpful.


Why does ALTER COLUMN to NOT NULL cause massive log file growth? I have a table with 64m rows taking 4.3 GB on disk for its data.

Each row is about 30 bytes of integer columns, plus a variable NVARCHAR(255) column for text.

I added a a NULLABLE column with data-type Datetimeoffset(0).

I then UPDATED this column for every row and made sure all new inserts place a value in this column.

Once there were no NULL entries I then ran this command to make my new field mandatory:

ALTER TABLE tblCheckResult
ALTER COLUMN [dtoDateTime] [datetimeoffset](0) NOT NULL

The result was a HUGE growth in the transaction log size - from 6GB to over 36GB until it ran out of space!

Does anyone have any idea what on earth SQL Server 2008 R2 is doing for this simple command to result in such huge growth?

When you change a column to NOT NULL, SQL Server has to touch every single page, even if there are no NULL values. Depending on your fill factor this could actually lead to a lot of page splits. Every page that is touched, of course, has to be logged, and I suspect due to the splits that two changes may have to be logged for many pages. Since it's all done in a single pass, though, the log has to account for all of the changes so that, if you hit cancel, it knows exactly what to undo.

An example. Simple table:

DROP TABLE dbo.floob;
GO

CREATE TABLE dbo.floob
(
  id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
  bar INT NULL
);

INSERT dbo.floob(bar) SELECT NULL UNION ALL SELECT 4 UNION ALL SELECT NULL;

ALTER TABLE dbo.floob ADD CONSTRAINT df DEFAULT(0) FOR bar

Now, let's look at the page details. First we need to find out what page and DB_ID we're dealing with. In my case I created a database called foo, and the DB_ID happened to be 5.

DBCC TRACEON(3604, -1);
DBCC IND('foo', 'dbo.floob', 1);
SELECT DB_ID();

The output indicated that I was interested in page 159 (the only row in DBCC IND output with PageType = 1).

Now, let's look some select page details as we step through the OP's scenario.

DBCC PAGE(5, 1, 159, 3);

UPDATE dbo.floob SET bar = 0 WHERE bar IS NULL;
DBCC PAGE(5, 1, 159, 3);

ALTER TABLE dbo.floob ALTER COLUMN bar INT NOT NULL;
DBCC PAGE(5, 1, 159, 3);

Now, I don't have all the answers to this, as I am not a deep internals guy. But it's clear that - while both the update operation and the addition of the NOT NULL constraint undeniably write to the page - the latter does so in an entirely different way. It seems to actually change the structure of the record, rather than just fiddle with bits, by swapping out the nullable column for a non-nullable column. Why it has to do that, I'm not quite sure - a good question for the storage engine team, I guess. I do believe that SQL Server 2012 handles some of these scenarios a lot better, FWIW - but I have yet to do any exhaustive testing.

The behavior for this is different in SQL Server 2012. See http://rusanu.com/2011/07/13/online-non-null-with-values-column-add-in-sql-server-11/

The number of log records generated for SQL Server 2008 R2 and below releases will be significantly higher than the number of log records for SQL Server 2012.