Add Active Directory User For Azure-SQL-DB I have an active directory user LDomain\LUser and I want that user to be able to connect to Azure-Sql-DB.  The syntax MS uses is throwing an error.

T-SQL:

CREATE USER [LDomain\LUser] FROM EXTERNAL PROVIDER

Error:

  Principal 'LDomain\LUser' could not be found or this principal type is
  not supported.

I'm only looking for the script to add an AD user - no interface.  I know that the AD user exists in Azure and have confirmed, but the Azure-Sql-DB isn't recognizing it, or this T-SQL is invalid - though this is from their documentation.

When provisioning users from external Azure Active Directory instances that are federated with your Azure subscription, you need to use the underlying "guest" email address created for the Azure subscription, not the "actual" email address.

i.e. The Microsoft scenario mentioned here as "Imported members from other Azure AD’s who are native or federated domain members".

So, instead of:

CREATE USER [your.user@example.com] FROM EXTERNAL PROVIDER

One needs to use the following convention. This is Microsoft's way of storing a guest / federated user from another Azure active Directory.

CREATE USER [your.user_example.com#EXT#@<yourAzureSubscriptionPrefix>.onmicrosoft.com] FROM EXTERNAL PROVIDER

Alternatively, using groups makes this far more intuitive and manageable.

Create a group (say SqlUsersFromExternalDirectory) in the Azure subscription's default Azure Active Directory.
Add the external users you want to access the SQL Warehouse or DB to to the group.
Add the group as an external user in the target database

CREATE USER [SqlUsersFromExternalDirectory] FROM EXTERNAL PROVIDER

This works fine, the external users can then sign in, admins can GRANT permissions etc, etc.


PostgreSQL hot standby promotion, multiple hot standbys I'm trying to work out failover and disaster recovery procedures for a
cluster of three servers. Streaming replication is being used with a high
wal_keep_segments, no log shipping is happening. I need to avoid the
several hours it takes to rebuild a hot standby from scratch.

ServerA is the master.
ServerB is a streaming hot standby and prefered failover server.
ServerC is a streaming hot standby.

For a planned failover, maintenance on ServerA:

Shutdown ServerB & ServerC
Shutdown ServerA
Copy pg_xlog from ServerA to ServerB and ServerC
Reconfigure ServerB as master, start it up.
Reconfigure ServerC as streaming hot standby of ServerB. Start it.
After maintenance, reconfigure ServerA as streaming hot standby of
ServerB. Start it.

For an unplanned failover, ServerA has exploded:

Run 'SELECT pg_last_xlog_receive_location()' on ServerB and ServerC, determining which is most up to date.
Shutdown ServerB and ServerC
If ServerC is more up to date, copy pg_xlog from ServerC to ServerB.
Reconfigure ServerB as master, start it up.
Reconfigure ServerC as streaming hot standby of ServerB, start it up.

Does this look correct to people?

Am I going to end up in trouble copying files into pg_xlog like this on a
busy system? With further reading, I suspect I need a recovery.conf on the new master with a restore_command pointing to an archive of the old master's WAL files (if available).

Is it overengineered? eg. will a master ensure everything is streamed to
connected hot standbys before a graceful shutdown?

I don't think copying pg_xlog after you've lost the master is going to allow you to make ServerB or ServerC the new master.  If you might need to remaster, you really need to add log-shipping archives as well (this is a current limitation in Postgres, at least through 9.2).

Also, if the master has been shut down cleanly, you don't need to copy anything.


Which mysql collation is good for storing unicode strings? For all I know it must be utf8mb4_unicode_ci, but:

> create table t1 (f1 varchar(255) collate utf8mb4_unicode_ci)
> insert into t1 (f1) values ('')

And what I get is:

ERROR 1366 (22007) at line 1: Incorrect string value: '\xF0\xA2\x84\x82'
    for column 'f1' at row 1

The symbol I'm inserting into the database is this one.

The issue was with connection character set. After setting it to utf8mb4, it succeeded. One way is to add default-character-set = utf8mb4 to client or mysql sections of ~/.my.cnf.

UPD Before adding default-character-set to ~/.my.cnf I had:

> show variables like 'character_set_%'
character_set_client    utf8
character_set_connection    utf8
character_set_database  latin1
character_set_filesystem    binary
character_set_results   utf8
character_set_server    latin1
character_set_system    utf8

And insert into t1 (f1) values ('') gave me:

ERROR 1366 (22007) at line 1: Incorrect string value: '\xF0\xA2\x84\x82'
    for column 'f1' at row 1

After adding default-character-set = utf8mb4 I have:

> show variables like 'character_set_%'
character_set_client    utf8mb4
character_set_connection    utf8mb4
character_set_database  latin1
character_set_filesystem    binary
character_set_results   utf8mb4
character_set_server    latin1
character_set_system    utf8

And insert into t1 (f1) values ('') succeeds.


Inner join or cross join Confusion For example select * from a inner join b on a.i=b.i ; is definitely inner join.

But can you let me know whether the below statements belongs to cross join or inner join ?

select *
from a ,b
where a.i=b.i;

select *
from a ,b
on a.i=b.i;

Thanks,
Kasi.

The first query is the same as an INNER JOIN, the second query is not valid because it is using ON without a JOIN statement.

From a similar question:

  The comma operator is equivalent to an [INNER] JOIN operator. The
  comma is the older style join operator. The JOIN keyword was added
  later, and is favored because it also allows for OUTER join operations

Source

Testing db<>fiddle


How to run DB Admin Tool (GUI) in IBM DB2 Express-C? I had installed IBM DB2 Express-C in Windows but I can't find any shortcut to launch the Administration Tools (GUI). When installing, I had selected "Administration Tool" option (I had checked twice to make sure).

This is the only shortcuts available from Start Menu:

So how to run the Administration Tools for IBM DB2 Express-C?

DB2 Express-C version 10 and later no longer include a full suite of administration tools as part of the installer (the "Administration Tools" you installed might only include the ones you're looking at...).

Like MySQL and SQL Server, the administration tools are now available as a separate download.

The official tool you're looking for is IBM Data Studio, which is available free of charge.

Older versions of DB2 Express-C used to include the legacy Control Center apps, but IBM has deprecated those and is really pushing the Eclipse-based Data Studio.


pg_ctl start gives FATAL: role does not exist On my system, whoami returns Dan. I run initdb with the --username=PgUser argument. Then as OS user Dan I run pg_ctl start on the newly-initialized cluster, and it gives this output:

LOG:  database system was shut down at 2013-06-13 00:54:33 UTC
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
FATAL:  role "Dan" does not exist
 done
server started

The server appears to have started up normally. I believe the error occurs because it wants me to run pg_ctl as the OS user named PgUser. What I want to know is:

Is that belief correct?
Is there a way to specify the user as PgUser without using su? I would prefer not to create a new OS user and would like to use a PostgreSQL user with a different username (I'm doing some code refactoring that makes this more convenient).
Is the instance I started crippled in some way because I started it as OS user Dan?

This happens when you run pg_ctl start with the -w (wait) option, because it will try a test connection with a user that does not exist (in your case).  But that's not really a problem (except for the confusing error message), because that proves that the server is up.


Fastest way to extract full table in Postgres I'm trying to dump a table with 50M records to a file, and my goal is to reduce the time in which this action is performed. I usually use the COPY metrics TO 'metrics.csv' DELIMITER ',' CSV; This could take like an hour in the best cases. I'm also interested in exporting the data in some plain format(avoid using pd_dump directories).

One of the ideas is to somehow access this table by a condition or cursor that splits the whole table into equal size pieces, so you can perform for example 2 copy queries at the same time reducing the time to the half.

Example:

COPY (SELECT * FROM metrics WHERE id < 25000000) TO 'metrics_1.csv' DELIMITER ',' CSV;
COPY (SELECT * FROM metrics WHERE id >= 25000000) TO 'metrics_2.csv' DELIMITER ',' CSV;

Could partial indexes created on those conditions help?

Any ideas a good way to implement this partial copy dumps of a table? Is there any other solution to dump this table faster?

Postgresql 11 / 100GB RAM / 20 cores.

After some parallelization with COPY IO bounds doesn't seem to be the bottleneck.

Your current idea of using a range query on the primary key is probably the best bet.  I don't see how partial indexes could help with that.  You would need a series of partial indexes which would in aggregate be a total index, and with BTREE indexes used for range queries that would be pointless.  But, the success of this method is likely to depend on the rows of your table being physically approximately ordered by the primary key value.  You can enforce this ordering by using the CLUSTER the command, but that is itself a very expensive operation to do.

If you want to get PostgreSQL to parallelize this for you, you would have use a dummy query which selects everything:

COPY (SELECT * FROM metrics) TO 'metrics.csv' DELIMITER ',' CSV;

and you will probably also need to dramatically lower the "parallel_tuple_cost setting", perhaps all the way to zero.  But this is not likely to give you an actual improvement, because the bottleneck (if it is not disk IO) will be in converting your data from its internal binary format to the text format that is going to be output by the COPY.  This conversion is always done in the leader process, not the parallel workers.

You can kind of get around this, by writing your queries so the heavy lifting is explicitly done in the query itself:

COPY (SELECT col1::text, col2::text, col3::text, ... FROM metrics) TO 'metrics.csv' DELIMITER ',' CSV;

Now the lead process still needs to scan all that text for things which need to be escaped/quoted, but at least it doesn't need to convert JSONB to text for all the rows by itself.  In my hands, using 7 parallel workers (8 processes including the leader) cuts the time required to COPY...TO in half.  The overhead then becomes the communication from the parallel workers to the lead process.  The way to remove that is to go back your original suggestions of indexed range queries in separate sessions.


How do you manage SQL jobs that belong to a database? We just had an issue where we lost msdb on one of our servers along with all of the jobs which we created for a multitude of databases. The fact that we didn't have a backup of msdb is one issue, which I won't get into, but it did make me think about how we develop the jobs that go along with our databases.

What are the different ways that one could manage jobs? I guess ideally they would be in source control, but I've yet to find any tools which do a good job of managing large systems of databases with their associated jobs.

So let us begin with the obvious: Back up your msdb database. Get a maintenance plan going, back it up regularly, and let us never lose our jobs or job history again.

This also leads to the second part of my answer which is: jobs and their steps are just rows in a table. They have steps which invoke subsystem commands using arguments provided in the job step row. The only way ultimately to "manage" the jobs is by managing the SQL that creates / modifies / deletes them. And because there's surprisingly little customization for a given job step, most people have just stuck to static SQL scripts for deployment.

Build your job in dev, test it, script it out to SQL, store it as a post-deployment file in your database project (your databases are in source control ... right?) and you've got the path of least resistance to SQL Agent job management.

Maybe someday in the future there will be ways to "compare" jobs in a project to a target server job a la Schema Compare - again, it's just SQL, shouldn't be too hard to reconcile - and then publish changes (even bidirectionally!) but for now you're just stuck with scripting.


What does TOAD do, when I terminate script execution? When it stops the execution of a long running script.  The script terminating needs even longer then the time the script was running. I.e. I have to kill Toad.

2nd EDIT:

Just found that this is a feature of Toad and not of Oracle

In SQL developer, you can stop execution of long running queries and similar to SQL Server Management Studio you get can modify your query immediately.

Trace your session. I expect you will find Toad is waiting for Oracle to roll back your transaction - which Oracle will continue to do in the background even if Toad is killed.

Under the covers, when you abort a query, an Oracle client application sends OCIBreak(). However in order to actually process this, the shadow process on the Oracle side has to come back and read that message - it is a request only, there is no way to send a true interrupt (i.e. a signal in Unix terms) across the network. If the shadow process is in a state where it is itself waiting for an operation to complete (such as a big I/O request to the OS) then it is not until that completes that it can advance to the next "tick" and find the break command waiting for it.

If you are really impatient, you could get the same result by finding the shadow process (v$process.spid where addr = v$session.paddr and sid=<your sid>) on the server and just issuing a kill command at the shell.

Incidentally there is some clean-up to do when a long-running SELECT is aborted: the previous versions of the rows are no longer needed.


Check if any table has a user defined type I've seen on sys.table_types that I have 8 user-defined table types on my db.

How can I check if there is any table (1000+) on my db that are currently using any of these user-defined table types before dropping them.

How can I check if there is any table (1000+) on my db that are
  currently using any of these user-defined table types before dropping
  them.

First things first

  A table type is a type from which table variables or table-valued
  parameters could be declared

Source

This means that table types can be used by table variables / TVP's and these can be used in parameter calls. An example of this is a stored procedure calling a TVP.

Table types are not permanent tables or used by permanent tables.

sys.parameter_type_usages

You can use the sys.parameter_type_usages dmv to get the parameters that are mapped to user defined types, these include Table Valued Parameters.

  Returns one row for each parameter that is of user-defined type.

Source

SELECT OBJECT_NAME(ptu.object_id),tt.name
FROM sys.parameter_type_usages ptu
INNER JOIN sys.table_types tt
on ptu.user_type_id = tt.user_type_id;

Testing

CREATE TYPE LocationTableType
   AS TABLE
      ( LocationName VARCHAR(50)
      , CostRate INT );
GO
/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE dbo. usp_InsertProductionLocation
   @TVP LocationTableType READONLY
      AS
      SET NOCOUNT ON
      INSERT INTO AdventureWorks.Production.Location
         (
            Name
            , CostRate
            , Availability
            , ModifiedDate
         )
      SELECT *, 0, GETDATE()
      FROM @TVP;
GO

test statements from here

Running the query at the top gives us both the procedure & the table type used

(No column name)    name
usp_InsertProductionLocation    LocationTableType

The call to the procedure simply uses the table type and a self defined table variable:

DECLARE @LocationTVP AS LocationTableType;
/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
   SELECT Name, 0.00
   FROM AdventureWorks.Person.StateProvince;

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;

Statements also from here


How to grant database creation privilege to a user in MySQL Following command provides all privilege in all databases for User 'admin'. But it doesn't allow 'admin' to create database.

GRANT ALL ON * . * TO 'admin'@'localhost';

How can I provide the access for 'admin' to create database?

As I am trying to Create database, I am getting the following error -

  ERROR 1044 (42000): Access denied for user 'admin'@'localhost' to
  database 'newdb'

ALL privileges includes "ALL" of them except "wITH GRANT OPTION"... Thus above command will let admin user create the database too.

You might want to show the error that you're getting. Note that in above GRANT you have not specified the password for admin user too.

Update:

You should be able to do following:

login as root and create user:

$] mysql -uroot -p
mysql> grant all privileges on *.* to 'admin'@'localhost';

login as admin (without password):

$] mysql -uadmin -p
mysql> create database admin;

When you login, you can verify your privileges as follows:

mysql> show grants;

or

mysql> show grants for current_user();

mysql> select current_user() will show you, user you logged in as.


When Creating a Foreign Key Relationship When creating a primary/foreign key relationship there are the following options:

Check Existing Data On Creation [Yes/No]
Enforce for Replication [Yes/No]
Enforce Foreign Key Constraint [Yes/No]

What are the uses for all these when you select 'Yes' or 'No' ?

Because when deleting the record with the primary key, I have a problem if there are any foreign key relationships, it doesn't let me delete.  So how do you delete all related records to primary key at that same time? or how do you ignore other foreign records when primary is deleted and allow the delete?

I searched online but can't find the answer to my questions.

What you need to check are the referential integrity constraints.

If you want to delete the parent key there are around four options on how you want to deal with the children keys:

NO ACTION which raises an error and does not delete the parent key (rolls back).
CASCADE which will carry on and delete all the child keys referred by that parent key,  leaving no orphans.
SET NULL which will set all the child keys to null values.
SET DEFAULT sets the child keys to the specified default value.


Is there a way to validate XML using variable as schema? Is there a way to declare XSD schema as variable inside a construct: DECLARE @xml XML(Books)?

I need to make quick validation of XML using XSD schema which is declared as variable. I suppose there is no way, but I have to ask.

If someone can propose some workaround it will be very helpful also.

I don't think this is possible using T-SQL, however you can achieve what you need using C# and .NET CLR assembly.

Steps

Create a new C# class library project in Visual Studio.
Write a public static method that will do the work for you.
Add CLR assembly to Microsoft SQL Server.
Create a scalar function that will call your method.
Call your method from T-SQL, just like any other command.

Public static method to validate an XML document against an XSD

using System;
using System.IO;
using System.Xml;
using System.Xml.Xsl;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml.Schema;

namespace SqlHelperClr
{
    public static class Xml
    {
        [SqlFunction]
        public static SqlString XmlValidate(SqlXml InputXml, SqlXml ValidationXsd)
        {
            if (InputXml.IsNull)
                throw new ArgumentNullException("InputXml", "InputXml is NULL.");
            if (ValidationXsd.IsNull)
                throw new ArgumentNullException("ValidationXsd", "ValidationXsd is NULL.");

            using (XmlReader xmlSchemaReader = ValidationXsd.CreateReader())
            {
                XmlReaderSettings settings = new XmlReaderSettings();

                settings.Schemas.Add("http://schemanamespace.example.com/", xmlSchemaReader);
                settings.ValidationType = ValidationType.Schema;

                using (XmlReader xmlReader = XmlReader.Create(InputXml.CreateReader(), settings))
                {
                    XmlDocument doc = new XmlDocument();
                    doc.Load(xmlReader);

                    ValidationEventArgs capturedEventArgs = null;

                    // Declare anonymous validation event handler to save the validation event arguments to a 'captured variable'
                    ValidationEventHandler eventHandler = new ValidationEventHandler(
                        (obj, e) =>
                        {
                            capturedEventArgs = e;
                        });

                    try
                    {
                        doc.Validate(eventHandler);
                    }
                    catch (Exception ex)
                    {
                        return String.Format("ERROR; Severity {0}: {1}", capturedEventArgs.Severity.ToString(), capturedEventArgs.Message);
                    }

                    return "OK";
                }
            }
        }
    }
}

Add CLR assembly to Microsoft SQL Server

Build the assembly, then copy the output DLL file to a location accessible by the SQL Server instance. Then run this command to register a CLR assembly in a database:

USE [Mydatabase]
CREATE ASSEMBLY [SqlHelperClr]
FROM 'C:\SqlAssemblies\SqlHelperClr.dll'
WITH PERMISSION_SET = UNSAFE;

Create a scalar function that will call XmlValidate

USE [Mydatabase]
CREATE FUNCTION dbo.fn_ValidateXsd (@InputXml XML, @ValidationXsd XML)
RETURNS NVARCHAR(1000)
AS EXTERNAL NAME [SqlHelperClr].[SqlHelperClr.Xml].[XmlValidate];
-- [AssemblyName].[NamespaceNames.StaticClassName].[MethodName]

Use fn_ValidateXsd

DECLARE @XmlDoc Xml = '<Root><NodeA>Blah</NodeA></Root>'
DECLARE @XmlSchema Xml = '' -- Schema goes here

DECLARE @Validation_Message NVARCHAR(1000)
SET @Validation_Message = dbo.fn_ValidateXsd(@XmlDoc, @XmlSchema)


SQL Server DATEDIFF: How's dayofyear different from day? When I look at the DATEDIFF() function on SQL Server, I see that it takes a datepart as its first parameter.

Possible datepart values include day and dayofyear.

I understand that dayofyear is the day number of the year (so that Feb 2 is 33, for example) and day is the day of the month (so that Feb 2 is 2).  However, I don't understand the difference when it comes to the DATEDIFF function.

Here are some examples:

select DATEDIFF(dayofyear, '2012-01-01', '2012-02-02')
returns: 32

select DATEDIFF(day, '2012-01-01', '2012-02-02')
returns: 32

select DATEDIFF(dayofyear, '2011-02-01', '2012-02-02')
returns: 366

select DATEDIFF(day, '2011-02-01', '2012-02-02')
returns 366

Are dayofyear and day equivalent for the purposes of DATEDIFF()?

Yes it is the same effect for DATEDIFF().  For an arithmatetic function like DATEDIFF(), it will have the same result, as the math is still taking into account the same unit: whether it's for day or dayofyear they are both still in days.


250 billion rows - computing interconnectedness on a really huge scale I have a table of approximately 700k rows, each identified by a unique itemnumber.

Each row/item can be associated with any of the other rows/items in the table by calculating a single numerical value indicating the strength of the association or the mathematical "distance" between two items, with no association/infinite distance represented by "0" while the numerical value "1" indicates the same item/no distance.

These association numbers are difficult to calculate/computing intensive and are based on data stored in a separate database. Therefore, precalculating them once for all row combinations, and then just for new rows as they are added (<4k new rows per year) seems to make sense.

The resulting table of associations might look like this:

itemnumber    |  associatedwithitem        | associationstrength

23920390293   |   12356456885              | 0.12255888644888

45468411516   |   44565464884              | 0.91155684161123

45648855222   |   98956221818              | 0.00000000000000

45468411516   |   23920390293              | 0.46813185844468

The size of such a table, however, would be immense:
((700,000 x 700,000) - 700,000)/2 = 244.999.650.000 = approximately 250 billion rows,
even after throwing out all self-connections (-700,000 in formula) and storing each connection only one-way (the divide by two in the formula).

I will be running only one type of query as follows:

"Given a list of itemnumber (see table example above), calculate the average (mean) association number."

Each list of itemnumber run against the association table will generally contain < 1k itemnumber, but may rarely be as large as 50k. But because each number in a list of 1k itemnumber will be associated with 700k other itemnumber, such a query would extract 700,000 x 1000 = 700,000,000 association numbers and would then need to have the mean of those 700m association numbers calculated.

Any ideas for the following:

Best data management system to hold this table
Structure (250 billion rows vs 700k rows with blob containing association data for each)
Best way to extract data and calculate means

Any input would be helpful.

Short answer:  it depends.

Long answer:  the answer depends on several factors.  Here are a few:

Does your 700K row table get updated infrequently?  If so, I would lean toward pre-calculating the means.  If they are not static but change in a regular, predictable interval (e.g., monthly or yearly), pre-calculating may still be a solid answer.  If they change often or unpredictably, pre-calculating becomes harder to justify.  You do mention that the number of rows added per year is relatively small compared to the total.
Do the existing item numbers ever change?  If not, that is a factor in favor of pre-calculation.  If they do change, pre-calculation becomes a little harder to justify, depending upon the frequency of change.
Do you have the disk space available?  Figure that you have 250,000,000,000 rows.  Your association table would have two bigints (8 bytes apiece) and one decimal(8,7) (5 bytes apiece), or 23 bytes per row.  For 250 billion rows @ 23 bytes per row, you're looking at a bit over 5 TB.  You'll want to factor in disk space for the transaction log, database backups, possibly having development/QA/staging environments, (probably) having non-clustered indexes on the table, etc.  Depending upon your database product, you could have compression reduce that by a fairly significant amount, but I'd say you would probably want at least 15-25 TB of disk space available before materializing; if you can't get that much disk space, then materializing the relationship just doesn't work.  Even if you generate a surrogate 32-bit integer key for each of the 700K products, you're looking at ~13 bytes per row, or 3 TB for that table (plus additional bits).
How time-sensitive is this process?  I don't know how long a regular run would take when you calculate it as part of the script, but if these jobs are something that can run in the background or overnight, then that would be a factor against pre-calculating.  Even if you do pre-calculate, you're likely going to scan the 250 billion row table to get your answer, so performance won't be zippy in either event.
How often do these requests come in? If it's once a month, that argues against pre-calculating.  But if requests come in daily (or several at once), pre-calculation might be the better option.
What else is this server used for?  If you're running this from an active machine, you probably do not want to pre-calculate and select from a big table.  The reason is that scanning 5 TB of data would drive pretty much everything else out of memory, hurting the performance of everything else.  If, on the other hand, this is a research server, development server, or you don't mind slow performance from the server in the aftermath of running one of these queries, then pre-calculation wouldn't be a big deal.
What is the frequency of item repeats in requests?  In other words, does item 23920390293 (for example) show up in a lot of reports, or are the separate requests essentially distinct?  If you see common elements, that argues in favor of pre-calculation (because then you only have to calculate once), but if the requests tend rarely to repeat numbers, you don't gain as much from pre-calculation.

If you do keep this in a SQL table, I'd recommend against having a blob.  I don't think you would get much (if any) size gain over a compressed table, and you might take a performance hit.  Even if you use a separate server to perform application-level calculation (instead of doing it in the database), I think it still makes sense to keep the table normalized.

Also, if you have all the disk space in the world, you might want to keep both sides (distance between A and B, and distance between B and A).  You double the amount of disk space required, but make the query that much simpler:  one join between your item list and the master item relationship table, rather than two separate queries (and the additional difficulty of ensuring that you have all of the relationships between the tables).


SQL Server BACKUP WITH FORMAT When doing a SQL Server database backup using the T-SQL BACKUP statement, does WITH FORMAT only wipe out the specified Media Set or does it wipe out all Media Sets 'associated' with the database?

BACKUP DATABASE ExampleDatabase To DISK = N'...'
WITH FORMAT,
MEDIANAME = N'ExampleDatabase-Media-yyy-mm-dd-hh-mm',
NAME = 'ExampleDatabase-yyy-mm-dd-hh-mm',
COPY_ONLY
...

will this result in only the Media Set with name ='ExampleDatabase-Media-yyy-mm-dd-hh-mm' being wiped (if it exists)?

The context is that I am doing some ad-hoc backup and restores from one server to another. And I want to make sure that the FORMAT in my command will not format any other Media Sets that might have been created.

I want to make sure that my backup statement that uses FORMAT will not interfere with any other Backup maintenance plans that are in place by destroying Media Sets other than the one I define in my statement.

@zadam,I would like to say some bit about media set. I also expect some more details from our expert like Mr. Aaron Bertrand & some other StackExchange DBA Experts.
First of all , what is media set.

A successful backup operation adds a single backup set to the media
set.

The backup set is described in terms of the media set to which the backup belongs.If the backup media consists of only one media family, that family contains the entire backup set.
A media set is created on the backup media during a backup operation by formatting the backup media.
Backing Up to an Existing Media Set
When you are backing up to an existing media set, you have the following two options:

Append to the existing backup set.

Note Here:- which is the default behavior of the BACKUP, can be explicitly specified by using the NOINIT option.

Overwrite all existing backup sets with the current backup, leaving
the current media header in place.

Note Here :- Overwriting existing backup sets is specified by using the INIT option of the BACKUP statement.
For Example:-

BACKUP DATABASE DatabaseName
TO DISK = 'd:\bu\mm_labeled.bak'
WITH MEDIANAME = 'BackupsToTheDDrive',
MEDIADESCRIPTION = 'Portable Drive Used for Backups',
INIT;

As per Grant Fritchey Here The MediaSetID is a GUID that uniquely defines this set.It will also be recorded in msdb so you can link a backup to a particular server.
For example :

SELECT  *
FROM    dbo.backupmediaset AS b
WHERE   b.media_uuid = '720C1417-3889-4730-9D33-74EBF11E854A' ;

For example if your full backup TSQL will be like that
Use DatabaseName
Go
Backup Database DatabaseName
To Disk=N'E:\Database_Backup\DatabaseName_Backup_16022016\DatabaseName.bak'
with FORMAT,
Medianame='E_SQLServerBackup',
Name='DatabaseName-Full Database Backup'
GO

Every volume of backup media (disk file or tape) contains a media
header that is created when by the first backup operation that uses
the tape (or disk). That header remains intact until the media is
reformatted.

If you will execute this TSQL statement to know about media_set_id & 'family_sequence_number'.
select * from msdb.dbo.backupmediafamily;

Here i would like to say that if you shall take backup in Disk. So always your 'family_sequence_number' will be 1. But if you shall take backup in Tape then 'family_sequence_number' will be like the sequence number of the initial tape is 1, the sequence number of the second tape is 2, and so forth.

The media header contains all of the information required to identify the media (disk file or tape) and its place within the media family to which it belongs. This information includes:
The name of the media.
The media name is optionally, but we recommend consistently using media names that clearly identify your media. A media name is assigned by whoever formats the media.
The unique identification number of the media set.
The number of media families in the media set.
The sequence number of the media family containing this media.
The unique identification number for the media family.
The sequence number of this media in the media family. For a disk file, this value is always 1.
The Microsoft Tape Format media label or the media description (in free-form text).
The name of the backup software that wrote the label.
The unique vendor identification number of the software vendor that formatted the media.
The date and time the label was written.

Note :- In case of Disk, The sequence number of this media in the media family.This value is always 1.
For your ref of Tape Media Set along with Backup see Media Sets, Media Families, and Backup Sets (SQL Server)  and
Here


Extracting Company from Multi-tenant Shared Schema Database We have a mutli-tenant shared schema database architecture. As a form of load distribution we have spread the tenants/companies across 6 SQL 2014 servers and 20 databases. All DBs are virtually the same except they hold about 10K different companies combined. We need the ability to extract entire companies out of one database and move them into another - some 200 tables. Ideally, we would like to be able to move them with minimal downtime. I am exploring ways to make this happen short of just writing custom scripts to copy every single table.

I learned you can setup replication with a filter based on a view (selectively extract company x data), but then I would have to setup replication for 200+ tables, which sounds cumbersome and could adverse impacts on production. Partitioning can do wonders, but not every table has a neat CompanyID reference. We don’t have AG but are planning on it and thought we could then use the replica to extract the company data as to not impact production.

Sounds like a job for SSIS  and the Transfer SQL Server Objects Task https://msdn.microsoft.com/en-us/library/ms141134(v=sql.120).aspx

Make sure you install and configure SSIS, and then you should be able to do this with ease.


Help converting a MySQL query to postgres Is it possible to do this query in postgresql?

SET @p:= 0;
UPDATE t SET x=CEIL((@p:= @p + 1) / $n) ORDER BY id DESC;

I'm struggling with the variable syntax.  I tried:

\set p 0
update t set x=CEIL((:p := :p+1) / 10);
ERROR:  syntax error at or near ":="
LINE 1: update t set x=CEIL((0 := 0+1) / 10);

This kind of queries do not need variables in Postgres. You can use window functions:

WITH cte AS
( SELECT id,
         1 + (ROW_NUMBER() OVER (ORDER BY id DESC) - 1) / $n  AS new_x
  FROM t
)
UPDATE t
SET x = new_x
FROM cte
WHERE t.id = cte.id ;

You 1 + (ROW_NUMBER() OVER (ORDER BY id DESC) - 1) / $n could also be written similarly to your expression, if you prefer. Assuming that $n is an integer parameter, the result will be the same:

ceiling(ROW_NUMBER() OVER (ORDER BY id DESC) / $n::numeric)


Sql Server Cluster with multiple nodes Does it make sense to think about a failover cluster configuration where less than 50% of all included nodes are standby?

For example instead of setting up 4 two-node clusters where one is active and one is on standby on each cluster - doesn't it make more sense to create 1 cluster with 4 active nodes and two on standby/passive for failover? That makes a saving of 2 nodes in total.

Sure, there is a risk in case multiple nodes fail unplanned and have to share one of the previous standby nodes, but in case you decide to take that risk over provisioning the standby hardware resources?

Looking for "multiple node failover clustering" I couldn't find more on this.

Firstly, having 4 separate 2 node clusters with one FCI deployment in each seems to me to be a little extreme. The second model you describe does not really make sense to me. Instead of either deployment I would suggest running 2 Clusters of 4 nodes (you would need a witness for each) and consider one node in each as a dedicated failover node (i.e. that you install 3 SQL FCIs to each cluster).

Also you should try to deploy on Windows 2012 R2 because of its dynamic quorum and dynamic witness capabilities (2012 only had dynamic quorum) -which will help to maintain maximum uptime upon node failure.

As you will be aware, the biggest issue with not having enough dedicated failover nodes is that ultimately you are exposed to SQL co-existence considerations (such as setting the correct max-memory upon failover if shared with other SQL FCIs amongst other things). Furthermore in truly Highly Available environments you probably would not want to fail-back post failover (otherwise it is a further addition of down-time), so sharing dedicated failover nodes would probably necessitate failback of one instance (assuming two had failed over).

However, that said, all system deployments are a compromise and provisioning too many redundant failover nodes does not make sense and it is important to find the right balance between HA requirement and cost effectiveness.

As a general rule of thumb you should consider an approximate 1 dedicated (failover node) per (2 SQL FCI) 3 node cluster, 2 dedicated per (5 FCI) 7 node cluster and 3 dedicated per (8 FCI) 11 node cluster.
These figures are generous approximations from nearly 20 years personal experience of SQL Failover Clustering but obviously there are many other factors that can affect your design strategy.

p.s. There are some technical Clustering reasons why it makes sense not to deploy a huge number of cluster nodes so I would probably not go beyond a 5 node cluster anyway (and in that case I would probably still be happy with 1 dedicated failover node).


SQL Server Express 2008R2 - restore db failing due to size limit, but file is under 10GB I'm trying to restore a live db to a test db, but I keep getting a '..failed because the resulting cumulative db size would exceed...limit of 10240' error, yet my database is under 10GB, I feel like the answer is in the 'cumulative db size' part.

Being new to SQL Server, I find it confusing that different numbers are reported depending on where you look. I've done multiple purges yet the upper 10,243MB number doesn't seem to change.
The source .BAK file size in bytes is: 10,622,637,056 (9.9GB)
Also, autogrowth set to 50MB - could that allocation be pushing it over the limit? Since the source is the live db, I'm hesitant to monkey with the autogrowth limit. Suggestions appreciated!

The database size limit is for the total size of all database files, both data file(s) and log file(s). So you've got a 7GB problem, not a 0.1GB problem.

You could try to shrink the log file down to near-zero, but even then it is going to be very close (it also may expand as soon as you try to query the database).


Using a 512 bit binary column as a bit field I'm designing a table to keep track of a value that has to represent the on-off setting of several hundred states. I would expand this to 512, to give room for future growth and be a power of two.

So I want a 64 byte = 512 bit column. My problem is that SqlServer bitwise operations don't support right operands larger than int, so there would be no way to access any bits past 32.

How can I handle this issue?

SQL Server supports up to 1,024 Columns per nonwide table. Use them. The bit data type, if declared as NOT NULL, will take one bit of storage. You can then address each state specifically by name, making for cleaner, more maintainable code.


Composite vs auto incrementing integer as Primary key I am designing a MySQL 5.7 database for a mobile platform. This platform has activities that users can add to 'to-do' and 'completed' lists.

I have 3 tables, one for activities where it has an auto incrementing primary key called 'activityID' and two tables for each list. Users are not stored in the MySQL DB for several reasons but have a GUID to identity them.

I only need to make 2 fairly simply select queries per list table:

Select/return all activity id's for a specific user in a list
Select/return all activity rows for a specific user after joining the activity table and whichever corresponding list table.

I've researched on stack exchange and elsewhere but have found conflicting answers on the following:

Should I use a composite primary key of the userID + activityID for the list tables, or just use an auto incrementing int as the primary key?
In either case, should I also have activityID as a foreign key to the activity table for each list table? (My understanding is I should to increase data integrity, and to take advantage of cascading delete if needed in the future)

Any suggestions for this is greatly appreciated, I know these things can be difficult to change once the DB is live. Thanks so much!

As Kumar said, using a composit primary key will prevent you from having two entries with the same userID and activityID - you have to decide if this is the desirable configuration. Logically, I would say that a user can complete the same activity twice, so the "completed" list would have two entries, both with the same userID and activityID. In this case you would need a seperate primary key.

Generally I would recommend using the activityID as a foreign key. If you configure ON DELETE CASCADE the deletion of an activity will also delete all entries in both lists that refer to this activity. However I would rather recommend using ON DELETE RESTRICT which would prohibit the deletion of an activity as long as there is a corresponding entry in either list (this will prevent you from having invalid lists where an activityID cannot be resolved).


SQLite: updating string values I couldn't find any answer for my question so I'm writing.

Are there any restrictions for updating strings in SQLite database ?
I'm wondering if any new value is written into database file there could be problem for strings of different length. Isn't it ?
I anticipate that I update strings in database and their length will be different.
In such situation should I store them into files and store only file names into database ?
Please give me some optimization tips.

SQLite can resize field values and records dynamically, so there are no restrictions on updating strings (except for the normal limits).


Check Constraint with Case Statement I want to add a check constraint on a column if entered value for column is greater than 3 than it should be saved as 1 else entered value. For that scenario I am using below query but on insertion of data it shows an error message.

create table tblTestCheckConstraint
(
id int,
NewColumn int
)

alter table tblTestCheckConstraint
add constraint chk_tblTestCheckConstraint_NewColumn1 CHECK
(
    CASE
        WHEN NewColumn >4 THEN 1
        ELSE NewColumn
    END = 1
)

Insert Statement:

insert into tblTestCheckConstraint values ( 1,5)
insert into tblTestCheckConstraint values ( 1,2)

Error Message:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "chk_tblTestCheckConstraint_NewColumn1". The conflict occurred in database "DBName"
, table "dbo.tblTestCheckConstraint", column 'NewColumn'.
The statement has been terminated.

Check constraints can only be used to verify if the data falls within a given set of parameters.  The CHECK(...) clause is essentially validated as a boolean condition; if the result is 1, the row is inserted, if the result is 0 the row is not inserted, and an error is returned to the client.

To test this, I created your table, along with your proposed check constraint, and inserted several rows of test data:

CREATE TABLE dbo.tblTestCheckConstraint /* always specify the schema */
(
id INT,
NewColumn INT
);

ALTER TABLE tblTestCheckConstraint
ADD CONSTRAINT chk_tblTestCheckConstraint_NewColumn1 CHECK
(
    CASE
        WHEN NewColumn >4 THEN 1
        ELSE NewColumn
    END = 1
);

INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (1);
INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (2);
INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (3);
INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (5);

SELECT *
FROM dbo.tblTestCheckConstraint;

This results in several errors and allows two rows to be inserted:

Results:

When attempting to insert 2 and 3 (or in fact anything other than 1 or >4) the constraint will return false, indicating to SQL Server to not perform the insert, and return the error messages seen above.

Here is an example of how you could accomplish this using a trigger:

USE tempdb

CREATE TABLE dbo.tblTestCheckConstraint
(
id INT CONSTRAINT PK_tblTestCheckConstring PRIMARY KEY CLUSTERED IDENTITY(1,1),
NewColumn INT NULL
);
GO
CREATE TRIGGER dbo.trg on dbo.tblTestCheckConstraint
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
    SELECT NewColumn
    FROM INSERTED i
    WHERE i.NewColumn <=3 OR i.NewColumn IS NULL;

    INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
    SELECT 1
    FROM INSERTED i
    WHERE i.NewColumn >3;
END;
GO
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (1);
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (2);
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (3);
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (4);
SELECT *
FROM dbo.tblTestCheckConstraint;

Ensure you remove the constraint from the table before trying to insert data, since it may conflict with the trigger code:

ALTER TABLE dbo.tblTestCheckConstraint
DROP CONSTRAINT chk_tblTestCheckConstraint_NewColumn1;


ERROR 1290 MYSQL secure-file-priv option I have a database which I have been LOADING text files, these were stored in /tmp.

for reasons of permits however this morning when I turn the computer the files were no longer in that folder and attempt the LOAD gives me the next

  ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it can not execute esta statement

I had any problems to use the LOAD so far what could be?

/tmp is, by design, cleared on reboot.  This cleans up certain trash that might be left behind by a crash.  And it is a security feature.
LOAD FILE INFILE is a security hole waiting for hackers to march in.  5.7 (finally) got serious about it, and partially plugged it by having --secure-file-priv.


Insert INTO a table combining * with a column value In MySQL, I am trying to insert all values from table A to table B, but I need to change the structure of table B and a column where all the values would be 0.
Is it possible to mix and create such a query as below, or what could be the possible query for this case?!
Many thanks.
$limitid = 50;
$pr1 = "INSERT INTO B SELECT * FROM A where A.id > $limitid ;
//to get
$pr1 = "INSERT INTO B (*,col10) VALUES(*,0);

You can do it this way
$pr1 = "INSERT INTO B SELECT *, 0 col10 FROM A where A.id > $limitid"

But this code is vulnerable to sql injection  so use only prepared statemenst with parameters see https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php


Can I restore all tables but not the stored procedures? I have a testing database that I restore regularly from a production backup file in SQL Server 2008 R2 to get new data. We often edit\change or update the stored procedures in the testing DB to test the data compared to the production's stored procedures!

Is there away to only refresh\restore the table(s) and not the stored procedures? so I don't have to re-edit them back to the changes that I need?

Both of these databases (Production and Testing) are in one server.

My DB size is about 5 GB and we can't afford to have third party tools.

Yes, you can do this, just not by using a database BACKUP / RESTORE. Instead, you would use the SqlPackage.exe utility that is part of SQL Server Data Tools (SSDT). This utility usually assists in migrating database projects in Visual Studio to SQL Server. But, it can also extract schema and data from a database into a file, and then it can push whatever is in that file into another database.

I created a CMD script, GetDataFromProduction.cmd, that extracts the schema and data, and then publishes only the tables and data. The Stored Procedures, Functions, etc are extracted into the .dacpac file (no way around that), but they can be excluded when publishing the content into a database. The following script calls SqlPackage.exe twice, once with /Action:Extract and once with /Action:Publish. It uses /p:ExtractAllTableData=True on the "extract" to grab the data for all tables.

@ECHO OFF

"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" ^
    /Action:Extract ^
    /SourceDatabaseName:TEMPTEST ^
    /SourceServerName:(local) ^
    /TargetFile:C:\TEMP\_Extract.dacpac ^
    /p:IgnoreExtendedProperties=True ^
    /p:ExtractAllTableData=True

ECHO Hit the "any" key to publish the extracted data
PAUSE

"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" ^
    /Action:Publish ^
    /SourceFile:C:\TEMP\_Extract.dacpac ^
    /TargetDatabaseName:TestPublish ^
    /TargetServerName:(local) ^
    /p:DropObjectsNotInSource=False ^
    /p:ExcludeObjectTypes=Aggregates;ApplicationRoles;Assemblies;AsymmetricKeys;^
BrokerPriorities;Certificates;Contracts;DatabaseRoles;DatabaseTriggers;^
ExtendedProperties;FileTables;MessageTypes;Permissions;Queues;RemoteServiceBindings;^
RoleMembership;Rules;ScalarValuedFunctions;SearchPropertyLists;Sequences;Services;^
Signatures;StoredProcedures;SymmetricKeys;Synonyms;TableValuedFunctions;^
UserDefinedDataTypes;UserDefinedTableTypes;ClrUserDefinedTypes;Views;Audits;Credentials;^
CryptographicProviders;DatabaseAuditSpecifications;Endpoints;ErrorMessages;^
EventNotifications;EventSessions;LinkedServerLogins;LinkedServers;Routes;^
ServerAuditSpecifications;ServerRoleMembership;ServerRoles;ServerTriggers;^
ColumnEncryptionKeys;ColumnMasterKeys;Defaults;ExternalDataSources;ExternalFileFormats;^
ExternalTables;Filegroups;FullTextCatalogs;FullTextStoplists;PartitionFunctions;^
PartitionSchemes;SecurityPolicies;Users;XmlSchemaCollections;DatabaseScopedCredentials;^
Logins

PAUSE

Now, if you do not want the data for all tables, then you can specify individual tables to get. Please note that there is no option to instruct SqlPackage to get all tables except one or two. If you want all but one or two, then you need to specify all of the tables to grab. When specifying individual tables, you need to first specify /p:ExtractAllTableData=False (it was True in the example above) and then add a new "property" to specify each table in "SchemaName.TableName" format:

    /p:TableData="dbo.Table1" ^
    /p:TableData="Orders.Order" ^
    /p:TableData="Orders.OrderItems"

As you can see, you can specify /p:TableData="SchemaName.TableName" as many times as you need. Unfortunately it does not appear that there is any ability to specify a wild-card for "SchemaName.%" or anything like that (because that would make things too easy, and what's the fun in that?). However, you can use the following to generate that list of tables to just copy-and-paste into the CMD script:

SELECT N'   /p:TableData="'
        + SCHEMA_NAME(tbl.[schema_id])
        + N'.'
        + OBJECT_NAME(tbl.[object_id])
        + N'" ^'
FROM sys.tables tbl;

Please also note that while this method does do exactly what is being requested here, I have no basis for estimating at what data volumes this becomes unmanageable. I suppose if a database is too large for /p:ExtractAllTableData=True, then you can break the migration into pieces by specifying /p:ExtractAllTableData=False and then specifying a subset of tables that is manageable, and repeating that across 2 or several of these scripts.

That being said, it does handle the data sync quite nicely in that it will make whatever changes are necessary to get the existing schema and data to match whatever is in the .dacpac file. You don't need to mess with truncating the tables first or anything like that.

If you do not already have Visual Studio and/or SSDT installed, you can get SSDT by itself from: Download Latest SQL Server Data Tools

On a related topic:

  We often edit\change or update the stored procedures in the testing DB to test the data compared to the production's stored procedures!

Why are you changing the Stored Procedures? Doing this, or needing to do this, might point to a problem in your release process and/or architecture that could be improved. Generally speaking, you shouldn't be making code changes to match a different set of data. That implies that you are either hard-coding Customer info in the code (very bad for several reasons) and/or are hard-coding environment-sensitive info, such as URLs, file paths, etc. None of that should be hard-coded; it should all be pulled from config files and/or values in a config table. And you can have a config table where there is a field for EnvironmentName or EnvironmentID so that all environments can have the exact same data, and then you just need a single function to return the value of the current environment, and that will be used to filter against the config table. This makes the release process easier when add/updating/removing config data since the same release script (which usually includes values for all environments) runs in all environments and doesn't need any IF conditions to check for which environment it is running in.

Comment from O.P. on this answer:

  unfortunately I can't install the package without getting an approval

This approach technically doesn't require installing on the server running SQL Server. SqlPackage is a client tool, just like SQLCLD, SSMS, etc, etc. It just needs to be installed on a computer that can connect to the source server. So if I.T. won't let you install it on your local PC, then perhaps you can find a dev or QA server somewhere to install it on.


How to find out the activity which increasing the response time of the Sql Server? We are using SQL Server 2008 as our database server. Recently we came to know, by using a 3rd party tool, that the response time of our database server is increasing at a specific time for example at night 1AM to 4AM. That 3rd party tool provides no information about the activity which is causing this.

Some Facts:

We are using SQL Server 2008.
We have scheduled some SQL Jobs but their execution time is different; not in the range where the response time is high.

I hope my question is clear enough if not please let me know.

I highly recommend using Adam Machanic's excellent diagnostic stored procedure sp_WhoIsActive.  I've got an sp_WhoIsActive tutorial on my blog.  Run it during the slow time window as I describe in the video, and it'll show the active queries, what they're waiting on, and their execution plans.

Keep in mind, though, that this is more of a DBA tool than a "here's-your-problem" tool.  This is where a database administrator usually has to step in and do diagnostic work.  If you're confused by the output of sp_WhoIsActive, check out the book Troubleshooting SQL Server.


Where can I find example databases for MySQL, SQL Server and Oracle? I want to experiment with the 3 large database management systems.
For this purpose it would be cool to have the same database (the same tables, etc.) for each of those.

Where can I find sql scripts which create identical test databases for MySQL, MSSQL and Oracle?

Oracle provides several sample schema, but as with Rolando's answer, you would have to convert these for the other databases.

My answer in a nutshell is Don't use identical scripts.

The problem with using scripts that are as similar as possible for each database is that you will be using least common denominator functionality.  Using a database requires an investment in infrastructure, installation, support and development that will not reach it's full potential if the advantages of each platform are not utilized.

To truly do a comparison of these platforms, I suggest you determine what your requirements are and then implement those requirements in the best way possible for each database.  This will of course require significantly more knowledge of the platforms, but any other comparison would only be academic.

Here are two examples.

Requirement: Show all days in a given date range with sales data for the day regardless of whether any sales were made that day or not.
Solutions: In Oracle you might do a cross join with a hierarchical query on dual returning all the days in the range.  In SQL-Server you might use a CTE to generate the days.  In MySQL you might use something else entirely.1

Requirement: Be able to run a query at any time to retrieve the data for any table as it existed at any point in time within the last four hours.
Solution: In Oracle you would only need to ensure that the undo retention was guaranteed and set to at least four hours.  In other databases the implementation would be dramatically different.2


SQL profiler how to filter with EventClass column I need to filter Profiler events by EventClass like RPC:completed, but I cannot find any filter for EventClass. Any idea how to do that?

Event Classes cannot be filtered, instead you select which Event Classes to include in your trace and then filter events based on the properties of each Event Class.
For example, to exclude "RPC:Completed" from your trace, on the Events Selection tab, expand Stored Procedures and uncheck the box on the left-hand side of RPC:Completed.
To only "RPC:Completed" for a particular stored procedure, you click Column Filters, locate ObjectName and add your procedure name under Like.


MySQL binlog format change from MIXED to ROW One of my customer has MySQL master-slave setup at on-premises, where the binlog format is kept to be MIXED. Now, we are planning to migrate the DB to AWS RDS using DMS, the binlog format should be kept as ROW. I am not sure whether MIXED to ROW at this point of time at source will impact the source DB or replications. I see MySQL docs talking about the implication of STATEMENT to ROW change, but not from MIXED to ROW. Can someone help me on this.?
If the above is not doable, another plan is to go with Daisy chain approach, where an EC2 instance is created as slave connected to the slave DB of on-prem. This EC2 slave should have the binlog format as ROW. Even in this case, at on-prem the binlog would be MIXED and EC2 slave would be ROW. I am not sure whether this approach (within the same DB) will work, or anything break.
Any expert suggestion/solution (with MySQL document reference for the solution, if possible) would be much appreciated.

I've watched the move from STATEMENT -> MIXED -> ROW over the past decade or more.  There has been virtually no hiccups.  So, I would not worry.
ROW is essentially required for some clustering solutions, etc.  There are subtle things that can go 'wrong' in STATEMENT replication, where ROW solves it.
If RDS requires ROW, go with it.


How Do I Gain SysAdmin Access Without Having a SysAdmin Account? Situation:
We recently had a consultant company build new Lync 2013 Servers in a mirror setup.  There are two security principles set, SA (disabled), and a AD group that is only associated with the Public server role.

  Setup:
  - 6 x Windows Server 2012 R2 Standard 64-bit
  - 4 x SQL Server 2012 Standard w/SP1 64-bit (11.0.3128) -
  - 2 x SQL Server Express 2012 64-bit(Witness)
  - All servers are VMware VMs

Questions:
 - Is there a Jedi trick to gain SysAdmin access or will I have to contact our consultant?
 - How does this happen?  I thought there was some sort of failsafe to
   keep this from happening.

Yes there is please follow below Microsoft web resource.It requires you to have admin access to windows machine and then start SQL server with -m option and create login in SQL server and grant it admin privileges.

http://msdn.microsoft.com/en-us/library/dd207004.aspx

Hope this helps


MySQL max_connctions+1 I experienced an incident that my connections are full. Even I as an administrator I cannot log to MySQL due to "too many connections.". Does anybody knows how to setup this max_connections+1? I use version 5.6.14. Thank you very much.

https://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html

Here is your basic problem: You most likely have many users in mysql.user other than root@localhost with the SUPER privilege. Please note the MySQL Documentation in your question:

  mysqld actually permits max_connections+1 clients to connect. The extra connection is reserved for use by accounts that have the SUPER privilege. By granting the SUPER privilege to administrators and not to normal users (who should not need it), an administrator can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected. See Section 13.7.5.30, “SHOW PROCESSLIST Syntax”.

You need to run this

SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) FROM mysql.user
WHERE super_priv='Y' and user<>'root';

You need to take away the SUPER privilege from those users.

The fastest way to revoke the SUPER privilege from those users is to login to mysql as root@localhost and execute the following:

UPDATE mysql.user SET super_priv='N' WHERE
WHERE super_priv='Y' and user<>'root';
FLUSH PRIVILEGES;

GIVE IT A TRY !!!

NOTE #1 : I have discussed this before on Jul 06, 2012 in How to resolve too many connections and fatal error in mysql running on vps

NOTE #2 : If you have other mysql users doing database backups and cronjobs, you need to make sure those users have all needs privileges for their respective operations after removing the SUPER privilege.

NOTE #3 : If you do not use other users to connect to mysql, you need to create extra users that do no have all privileges, but still have enough privileges for needed operations.


Mongodb as a data ware house? Will mongodb a good choice as a data warehouse considering following situation?

We are building analytics, report generation platform for our client and a typical table growth in terms of rows will be 2 million rows every year (We need 5 yr history at max, total tables ~ 50)
We have proof of concept ready using mysql and it works but my worry is as we hit production the growth in data might create issues with mysql. mysql doesn't support horizontal scaling whereas sharding in mongodb makes it scale well
On average once or twice a year schema of a table changes where either new column is being added or existing one is deleted (deletion is not a concern as we can always keep the column but how difficult it would be to add new column in mysql table containing few million records?). This is other reason we are thinking about mongodb as it is flexible in terms of schema changes
Writes: Average 5000 records per table per day. Reads will be less then this and it will be performed for generating analytics.

I don't have much experience dealing with this kind of data volume both with mysql and mongodb hence pardon my ignorance if some of the things mentioned above are naive.

(Speaking from a MySQL perspective...)

10M rows is not that big -- perhaps only 1GB?  It is hardly a candidate for sharding unless the system activity is really high.

2M rows/year = 25/second.  100/second is where I perk up an eyebrow and suggest looking more closely.  1K/sec is likely to need some special treatment.

ALTER TABLE .. ADD COLUMN .. -- There are techinques that make that not too bad.  And with MySQL 8.0, there is a truely painless ADD COLUMN.

Consider having JSON column for "miscellany" that gets added later; do not add more columns unless you need the database engine to filter on them or sort on them.

For analytics, build and maintain Summary Tables.  These make "report writing" 10x faster than scanning through 10M rows.

CTE and Windowing functions exist in MySQL 8.0 and MariaDB 10.2.  But you may not need such.

5K Writes/day -- In addition to the 2M inserts/year?  Not a big deal.


Why first_lsn of full backup and 1st transaction log backup are same? shouldn't first_lsn of the 1st transaction log backup be 36000000022400001 instead of 36000000022100001? In this case it equals to first_lsn of full backup.

Why first_lsn of full backup and 1st transaction log backup are same?

Because Full backups never break the log chain.  The log backups contain a contiguous sequence of log records regardless of how many Full backups were taken.
The log records in the Full are only there to enable the database to be restored and recovered to the point-in-time at which the backup completed.


When to use JSON or JSONB array vs SQL array in postgres I am looking for the main points to consider when choosing between a JSON or JSONB array and the regular SQL postgres array. If you know that you will only be inserting one data type into an array, why would you use the SQL array over the JSON array? Thanks

I think this is mainly a matter of personal preference, so I guess your question will be closed soon.
If you deal with JSON values in your application anyway, going with JSON might be easier (but then you can easily convert a native array to a json array using to_jsonb()).
There are a bit more functions for changing native arrays than there are for JSON arrays. So if you need to remove or find elements by value, native arrays might be easier. If those arrays contain integers, you can have even more functions for manipulating them through the intarray extension
And then of course there is the underlying question: wouldn't a properly normalized model be the best solution anyways, so that you avoid arrays completely?


Data Replication in Oracle I have a table on master site which only allow insert operation. I want to replicate the rows which are inserted recently so, need something which can track the last record replicated into the local site and perform the replication afterwards.

I have tried Oracle materialized view but still confuse that whether I use the Fast Refresh or Complete Refresh. I need all the newly inserted rows replicated in one transaction.

Are there any better approach to do that? Any help would be highly appreciated.

Thanks.

A fast refresh would copy incremental changes over the network but requires that a materialized view log be created on the master site on the source table.  That adds some overhead to the inserts happening on the master table but would generally make the refresh more efficient.

A complete refresh would copy every row over the network every time the materialized view is refreshed.  That is likely to be less efficient from a refresh perspective but there will be no overhead to inserts on the source table and the master site does not need to create a materialized view log.

Oracle provides a host of data replication technologies-- materialized views are the oldest and probably the least efficient but are relatively trivial to set up.  Streams is a newer technology that has much lower overhead but is quite a bit more complex to set up.  Golden Gate is the preferred replication technology today but that has extra licensing costs.


Creating index on computed field: string or binary data would be truncated I have a table Foo with the following fields:

ID bigint not null identity(1,1),
SerializedValue nvarchar(max),
LongValue as TRY_CAST(SerializedValue as bigint)

Now I want to create an index on LongValue, so that I can easily look up on serialized values that represent numbers.

create nonclustered index IX_Foo on Foo(LongValue);

Which spits out the following error at me:

  String or binary data would be truncated.

Yes, there is existing data in SerializedValue.  But what, pray, can be truncated by creating an index on a computed field?

The error isn't caused by creating the index. The error is caused by TRY_CAST when the computed column values are evaluated at index creation.

If I run this:

SELECT TRY_CAST(REPLICATE(CONVERT(nvarchar(MAX), N'a'), 4001) AS bigint)

I get the same error.

The documentation says (emphasis mine):

  If the cast succeeds, TRY_CAST returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CAST fails with an error.

Now, it's not exactly clear under which cases it will fail with an error (seems kind of asinine given the whole point of the function, but anyway...), so we can fix up the code by transforming the input values (use something reasonable for the data in your table), as there's no need to process a huge string when it won't fit in a bigint anyway:

SELECT TRY_CAST(LEFT(REPLICATE(CONVERT(nvarchar(MAX), N'1'), 4001), 100) AS bigint)

This returns NULL as the value isn't valid, but it doesn't bomb with an error.


How can I use a SQL query to retrieve data on a database sever? I have connected to a SQL server in Excel . I want to be able to import data from a table located on the server. However this table is too big to import into Excel, I'm talking millions of rows. I only want to import certain data into Excel, for example only import the data where the date is between a certain range. Where can I do this in Excel? If there are any alternatives to retrieving data from a large database please let me know.

In EXCEL, from the Data ribbon, Get External Data tab, select From Other Data Sources -> From SQL Server.

Follow the wizard to connect to your server and create a query.


Sqlite - Query on table? I have a lot of records like this (over 6000) in this table Lat and Lng is same and I should select base these fields (lat,lng) :

I need to computing on these records (  and  )

I need to get first record of a Lat Lng(same) and latest (between same date) for compute on time field with filter date ,and get latest record with first record from lat lng next one.

How I can write this query ?

I wrote this query, and I need to get first and last record base date now !

SELECT mDate as date ,xLat as lat,yLng  as lng
FROM ReportAct_tbl ,
 (SELECT mDate as dt,xLat as lt,yLng as lg
  FROM ReportAct_tbl )
WHERE date  = dt and lat = lt and lng = lg
order by date  ASC,lat ASC,ylng ASC

As I understand it sqlite does not have window functions which really simplify these kind of queries (I replaced a couple of identifiers which I suspect is reserved words):

select dt, tm, lat, lng
from (
    select dt, tm, lat, lng
         , row_number() over (partition by dt, lat, lng
                              order by tm asc) rn1
         , row_number() over (partition by dt, lat, lng
                              order by tm desc) rn2
    from ReportAct_tbl
) x
where 1 in (x.rn1, x.rn2)

Without window function (like row_number) it's probably easiste to get the row where its does not exists any rows that date before that time

select dt, tm, lat, lng
from ReportAct_tbl r1
where not exists (
    select 1
    from ReportAct_tbl r2
    where r1.dt = r2.dt
      and r1.lat = r2.lat
      and r1.lng = r2.lng
      and r1.tm < r2.tm
)

To get the last rows we do the same:

select dt, tm, lat, lng
from ReportAct_tbl r1
where not exists (
    select 1
    from ReportAct_tbl r2
    where r1.dt = r2.dt
      and r1.lat = r2.lat
      and r1.lng = r2.lng
      and r1.tm > r2.tm
)

and finally we can combine them as:

select dt, tm, lat, lng
from ReportAct_tbl r1
where not exists (
    select 1
    from ReportAct_tbl r2
    where r1.dt = r2.dt
      and r1.lat = r2.lat
      and r1.lng = r2.lng
      and r1.tm < r2.tm
)
union
select dt, tm, lat, lng
from ReportAct_tbl r1
where not exists (
    select 1
    from ReportAct_tbl r2
    where r1.dt = r2.dt
      and r1.lat = r2.lat
      and r1.lng = r2.lng
      and r1.tm > r2.tm
)


How can one create a directory dynamically in PL/pgSQL? I want to create a directory in PL/pgsql:
CREATE OR REPLACE FUNCTION getNextId() RETURNS VARCHAR(200) AS
$BODY$

DECLARE
    mfId integer;
    rootDir varchar(50) := 'mfdata/';
    createDir text := 'mkdir --mode=777 -p mfdata';
    dir varchar(100);
BEGIN
    INSERT INTO copy.history(time) values(now()) RETURNING id INTO mfId;
    EXECUTE('COPY (SELECT 1) TO PROGRAM ' || quote_literal('mkdir --mode=777 -p mfdata '));
    RETURN 'mfdata' || mfId;
END;
$BODY$
LANGUAGE plpgsql;

but occur an error as follow:

Where is the problem? How can I create a directory dynamically in PL/pgSQL?

It looks good.  There might be two issues when using COPY {FROM | TO } PROGRAM though:

missing permissions on the directory you want to create the new one in.  With your present code this is unlikely, as your dir will be created in the Postgres data directory.  The directory would be created as owned by the OS user/group postgres, permissions set to 700.
you are trying this as a non-superuser.  That won't work - and also produces a different error:

ERROR:  must be superuser to COPY to or from an external program
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

Based on the above, your problem is the first case.  Check if the user has write permission on the target directory.


Does SSMS v17 allow user input in Python scripts? With the introduction of Python feature shipped with SQL Server 2017, I would like to know if anyone has been able to use SSMS v17.x and run a python script that requires a user input. Here is a sample basic script:

execute sp_execute_external_script  @language = N'Python',
@script =N' print ("This is a PYTHON test")
userinput = int(input ("Enter LBs to convert to KG"))
converted = userinput * 0.453592
print (converted) '

The script runs endlessly in SSMS while if it was just a print statement; that completes successfully and produces the print result.

I know I can use a Python IDE console like Pycharm but I am wondering if SSMS has such capabilities yet. My research on the internet has not been that fruitful. Most of the use of Python is related to modelling and analysis.

I cannot see any possible way for this to work given that the script is being submitted through a back-end service where there is no user-context (i.e. display, etc). SSMS is not executing anything. It is merely submitting those statements to SQL Server which handles the execution (even if SQL Server then passes it off to something else).

The only potential possibility of getting around this, and it is would have been a long-shot and not likely to be a practical work-around even if it does did work, is to run this statement against SQL Server Express LocalDB. Because LocalDB runs in user mode (not an NT service), it does have access to the console / display. I know this because I have been able to pop-up a Windows dialog / confirm box via SQLCLR when using LocalDB :-D.

HOWEVER, I just tried and couldn't even get the print to work. So, I checked the supported features list ( Editions and supported features of SQL Server 2017 ) and neither R nor Python are available in Express Edition, and hence not in LocalDB. :-(


List the underlying objects used in a SQL Server Function I use the below to get a list of underlying objects in a View:

SELECT view_name, Table_Name
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE View_Name IN
    (
      'View1', 'View2'
    )
ORDER BY view_name, table_name

I could not find any system table that provided the base objects in a function. Does anyone have any idea how to easily pull this information? I am dealing with many functions with multiple base DB objects and want to avoid manual effort to get this information. Thanks!

sp_depends will give you list of dependent objects in all current versions of SQL server but is listed as deprecated.

Here is a MSDN article with a way to find objects referencing a function and with small modifications you can get the list of object referenced by a function:

SELECT OBJECT_NAME(object_id) AS referencing_object_name
 ,COALESCE(COL_NAME(object_id, column_id), '(n/a)') AS referencing_column_name
 ,object_name(referenced_major_id) as referenced_object
 ,*
FROM sys.sql_dependencies
--WHERE referenced_major_id = OBJECT_ID('schema.objectname')
where object_id = object_id('schema.objectname')
ORDER BY OBJECT_NAME(object_id), COL_NAME(object_id, column_id);

And as a bonus: http://beyondrelational.com/modules/2/blogs/28/posts/10399/tsql-lab-9-how-to-find-the-dependency-chain-of-a-database-object.aspx


Best Practices for Using Temp Tables in Stored Procedures I recently developed a stored procedure which essentially queries data from several different tables, manipulates it, and then inserts the result in a certain table.

It ended up being one INSERT statement with many subqueries and was around 300 lines. The code was very performant; however, it is not very readable and may have other drawbacks.

Is it within recommended usage to write a query like so:

SELECT ...
INTO #tempfoo
FROM foo;

SELECT ...
INTO #tempbar
FROM bar;

SELECT ...
FROM #tempfoo
     INNER JOIN #tempbar ON #tempfoo.id = #tempbar.id;

Or is it better to write it like this, even though it can become somewhat unreadable:

SELECT ...
FROM
(
    SELECT ...
    FROM foo
) AS foo
INNER JOIN
(
    SELECT ...
    FROM bar
) AS bar ON foo.id = bar.id;

Note: I know this is a simple example, but imagine if you have layers of nested subqueries and four or more table joins.

There are times when the indexes on underlying tables just aren't enough to satisfy a particularly big query with lots of joins/applies/subqueries/CTEs. For those times, temp tables and table variables can be just what you need to improve performance.

By using a temp table to store intermediate results, you can create any indexes or statistics that you need to improve further processing. I run into this now and then when we need to build an occasional-use report on top of a bunch of OLTP data. Table variables can be used to similar ends, but with the restriction that they're not nearly as flexible with indexing (up until 2014, at least, which began to remove much of this restriction). But they are a bit lighter-weight, and more importantly they are scoped to the module rather than session, so you don't have to worry about naming conflicts like you would with temp tables.

There is overhead associated with creating and writing to temp tables, of course, to say nothing of the impact on code maintainability, so if the query can be written cleanly as a single query making use of set-based operations without sacrificing performance, then stick with that approach.


Extensible Asset Database Schema Background

I work for a decent sized company in the hardware sector.  Currently, we have a number of varying assets that need to be tracked.  Until recently, this was done by hand as was an (obviously) error-prone practice.  We are now (finally) putting this into a proper RDBMS, though the exact DBMS has not yet been selected.

Thankfully, each asset is already tracked by a unique asset tag / number.

Question

I am trying to figure out which is the best way to track check-outs and check-ins.  I have done some googling on the matter, but I cannot seem to find what I am looking for.  I simply need to track when a given asset was checked out and subsequently checked back in.  Currently, I am considering three primary options:

One table each for check-ins and check-outs.  This has the benefit of cleanly separating the two concepts and avoiding any NULL values.  However, it does make the queries for such information more complicated.
One table for both check-ins and check-outs; check-in and check-out on different rows.  This allows me to reuse the same table, but it will require a lot of self-joining to get anything.  I also don't like the reliance on some kind of constant to differentiate between 'out' and 'in' records.
One table for both check-ins and check-outs;  check-in and check-out on the SAME row.  This is my current favorite as it makes querying trivial and marries the two concepts ('out' and subsequent return) in a very understandable manner.  My only hesitation with this is that it relies on NULL to determine if an asset is still checked out or not.  It has been my experience that NULL can be a harsh mistress, especially when the design is DBMS-independent.

I am aware that a lot of this may depend on my particular requirements; however this problem seems common enough that I am hoping for some level of expected best-practice and WHY that practice was chosen.

Too long; Didn't read

Which RDBMS-agnostic design technique is best for tracking check-outs and check-ins of assets and WHY:

Two tables; One each for check-outs and check-ins
One table for both; Different rows for' out' vs. 'in'
One table for both; 'out' and 'in' on same record

Thanks in advance for any help.

Option #3

NULLs are your friend.  After you select the platform, study how NULLs work for the platform and embrace them.  In this case they accurately reflect the absence of a check-in.  In Oracle NULLS are not indexed, so you could create a function based index that swaps the NULL state giving you a very small index that contains only the entries not checked-in.

Option #1 would need a join anytime you want the Check-In date or status.  Joins are your friend too, but in this case there isn't a benefit to separating this data unless there are times when a single checkout can produce multiple checkins.  Option #2 requires repeating data or even more NULLS than option #3.  Here are the three concepts fleshed out to tables columns and data.

#1
CheckOut
   Asset CheckInOut User DateTime
   1     1          1    3/10/2013
   2     2          1    3/10/2013
   3     3          2    3/11/2013

CheckIn
   CheckInOut DateTime
   1            3/11/2013

#2
CheckInOut
   Asset CheckInOut User DateTime   InOrOut
   1     1          1    3/10/2013  O
   2     2          1    3/10/2013  O
   1     1          1    3/11/2013  I
   3     3          2    3/11/2013  O

#3
CheckOut
   Asset CheckInOut User OutDateTime InDateTime
   1     1          1    3/10/2013   3/11/2013
   2     2          1    3/10/2013
   3     3          2    3/11/2013

SQL Fiddle's showing Option #2 and Option #3 with the additional requirements Joel Brown added and how various questions would have to be answered in SQL.

#2 - http://www.sqlfiddle.com/#!4/55e72/25

#3 - http://www.sqlfiddle.com/#!4/e2f27/6


How to convert columns into rows in MS Access Please help me to understand, how to convert columns to rows in MS Access. I have the below table, I would like to convert as the below result table.

NoOf_A NoOf_B NoOf_C NoOf_D
200    300    400    500

Result Table:

Type_OfCValue  Total
NoOf_A          200
NoOf_B          300
NoOf_C          400
NoOf_D          500

SELECT "NoOf_A" AS Type_ofCValue, NoOf_A AS [Value] FROM T1

UNION

SELECT "NoOf_B" AS Type_ofCValue, NoOf_B AS [Value] FROM T1

UNION

SELECT "NoOf_C" AS Type_ofCValue, NoOf_C AS [Value] FROM T1

UNION

SELECT "NoOf_D" AS Type_ofCValue, NoOf_D AS [Value] FROM T1;


Creating/restoring mdf/ldf to non-default file location giving access denied Default folder is typical C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA. But C: drive on server is almost full.

So, in restoring an 11gb database from another server, I decided to use the L: drive on the same server.

Tried to restore to L:\SQL\Data (a folder I created). Access Denied.

Some quick Googling later, I see that the SQL Server service is started by NT SERVICE\MSSQLSERVER, so I make MSSQLSERVER the owner of the L:\SQL\Data folder and give it full permission to the folder.

Try to restore again. Access Denied.

Hm. Try to create NEW database in same location. Access Denied.

Create new database in default folder, detach, move mdf/ldf to L:\SQL\Data, making sure all folder and file permissions and ownership are set to MSSQLSERVER, then attach using CREATE DATABASE ... FOR ATTACH. Database created in read-only mode.

Try to change to read/write mode. Access Denied.

Create a folder datax in C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL and give it appropriate permissions/ownership. Create a new database there. It worked!

Move back one folder and created a folder data in C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER and give it appropriate permissions/ownership. Create a new database there. Access Denied.

Create a folder C:\temp\data and give it appropriate permissions/ownership. Access Denied.

SQL Server service has been restarted. Server has been restarted. Same results.

This last bit with xp_cmsshell might be a bit of a diversion...

So... tried EXEC xp_cmdshell 'md l:\data'; Message back is: "An error occurred during the execution of xp_cmdshell. A call to 'CreatePipe' failed with error code: '5'."

Changed permissions on root of L:, tried again, same result.

EXEC xp_cmdshell 'dir c:*.*'; Same result.

EXEC xp_cmdshell 'whoami /all'; Same result.

Anyway, bottom line is, everything I've read out here about getting an "access denied" when restoring/creating db is because of folder permissions. But best I can tell, those are correct. I even tried some differences with permissions being inherited versus explicit.

A couple links I've come across would indicate that there is no inherent restriction on putting data/log files anywhere I want, i.e., outside the default location.

I haven't tried changing the service account for the SQL Server service because I don't have that level of freedom on this server.

Also, this is from SSMS on both local machine and directly on server, and always connected under the context of an administrator on the domain.

I guess the first thing I would do would be to make sure that the permission are set on the correct account.
Run : select servicename, service_account from sys.dm_server_services
to get the account on which the permission should be set.


Make View Supersede Table in Same Database? For testing purposes we have a sanitized copy of our production database in our test world. Because of the way it is sanitized, we now have empty fields where encrypted data previously existed. We would like to be able to run against the database and have dummy data appear to be in the empty sanitized fields.

My initial thought is to have views with the same name as the sanitized tables that would return dummy (probably fixed) data in the empty fields. However, my understanding is that a View cannot exist with the same name as a Table. Is it possible to work around this?

My first thought was to use schemas, but I don't know enough about schemas at the moment. My second thought is to have a second "wrapper" database that references objects in the first database.

Is there a common practice for handling this sort of task?

You could use an updatable view as a proxy in front of the base table behind it. The view should behave the same way the table did, with some exceptions (for example, TRUNCATE will not work).

The table can the have any name because it is never referenced by app code.


Copying Oracle user without dump file I'm looking for a way to copy an Oracle user and its data to a new one without dump file.

Google search brings a tip by Steve Karam, which seems to be exactly what I want.
However the page is not accessible to me.

@edited
Thank you for your answers, Justin and Leigh.
I'm considering a simulator of an enterprize system whose database scheme is not designed for it.
In order to apply current situation and to keep the simulated result, I decided to make a cloned database user and execute the simulation on that user.
The simulator will be handled by non-IT person, who could have no knowledge about dump file, so cloning the database must be done internally without any user interaction.
That's the reason why I'm afraid of using export/import tool.

Steve is discussing how to copy a user and that user's grants from one database to another.  That wouldn't copy the objects the user owns or the data in those objects.

Why don't you want to use the export and import utilities?  Whatever restrictions you have that prevent the use of export and import might prevent you from using other alternatives.  You could potentially copy the data over a database link, for example, but that wouldn't generally be preferred to just using export and import.  If you want to do this on an ongoing basis, Oracle provides various tools for replicating data-- Streams, materialized views, etc.


archiving tables that are related to other tables with FK I have a database i need to archive some of its tables leaving always data above 2 years.
some of the tables are related to other tables with FK (about 21 FK tables) , so clearly i cant delete the rows with the PK without deleting the fk related rows.
the process of the archiving was suppose to be inserting the rows to the archiving database and deleting them from the old DB using dynamic SQL. but not with the FK i was wondering what is the best way to handle the archiving of the related tables.
can i do it using dynamic SQL or do i have to compare all the PK IDS from the originals tables and compare them in the related tables move the data from there delete the FK rows and then do the delete for every table?

what is the best approach to deal with that situation?
please let me know if i need to clarify my question.

thank you

We faced exactly the same scenario.  Our solution was to have a SQL Server Agent job with a steps to call stored procs co-ordinating the delete/archive.

To make our lives easier we had the application that persisted the data in the first place write a DateTimeCreated field in each related table with exactly the same date/time.  For example an ProductEnquiry record and associated ProductResults record would get exactly the same date/time.

Our clustered index was on that DateTimeCreated so DELETE FROM ProductResults WHERE DateTimeCreated BETWEEN... made use of the clustered index.

As the database grew we found that the purge jobs had to have a pre/post step to disable/re-enable the FK constraints.

We also had to start using a loop so we purged 50,000 records at a time and kept going until there were no more qualifying records to delete.

When we move to Enterprise edition we started using partition switching which had a dramatic performance improvement on purge activity and massively reduced IO on our SAN.


check if found specific record then find another record with the same group I have a table

Master table

PROBLEM_ID  PROBLEM            PGROUP
1           Other Hardware     Hardware
2           Error On hardware  Hardware
3           PM                 Hardware
4           Service            Maintenance

and a detail

TERM          PROBLEM            PROBLEM_ID
S1G166901     PM                    3
S1G166902     PM                    3
S1G166903     Service               4
S1G166902     Other on hardware     1
S1G166901     Service               4
S1G166901     Other Hardware        1
S1G166901     Error on Hardware     2

i need to query if PROBLEM_ID = '3' then find a PROBLEM that have PGROUP = 'Hardware'

like this

TERM          PROBLEM            PROBLEM_ID    PGROUP
S1G166901     PM                    3          Hardware
S1G166901     Other hardware        1          Hardware
S1G166901     Error on hardware     2          Hardware
S1G166902     PM                    3          Hardware
S1G166902     Other Hardware        1          Hardware

i try to use Outer apply but it's return too much row

my specific result is if they're not contain PROBLEM_ID = '3' then will not show but if contain will show all problem with pgroup ='Hardware' by term

i try this

SELECT p.TERM, p.Problem_ID,p.problem, PGROUP
FROM Problem_log AS p
CROSS APPLY (
            SELECT s.pgroup, s.problem_id
            FROM problem AS s
            WHERE   s.problem_id = p.problem_id AND PGROUP = 'Hardware'  )as s

            WHERE  exists (Select s.PGROUP,s.PROBLEM_ID
            from PROBLEM_LOG
            WHERE problem_log.PROBLEM_ID = '3'  GROUP BY term)
            ORDER BY TERM

my sequence is
- Select data from table problem_log for problem_ID = '3'

if problem_ID = '3' then For each record check found other problem (Hardware only)

try this

SELECT p.TERM, p.Problem_ID,p.problem, PGROUP
FROM Problem_log p
INNER JOIN problem AS s
ON s.problem_id = p.problem_id
WHERE s.PGROUP IN
(
    SELECT PGROUP
    FROM Problem_log
    INNER JOIN problem
    ON Problem_log.problem_id = problem.problem_id
    WHERE Problem_log.PROBLEM_ID = 3
)


MySQL Workbench fails to store password in vault I am trying to access my SQL server through MySQL Workbench. So I click the + sign to create a new connection.

At this point I choose a connection name and enter the hostname, username, and port. Then I click "Store in Vault" to store my password.

So I then enter my password to store.

At this point I click "Test Connection", but despite having just stored my password in the vault, I am yet again prompted again to enter my password.

I comply and enter my password once more. This time I get a connection failure message:

I know that my username, password, hostname, and port are all correct.

I just wanted to know if the fact that MySQL seemingly 'forgets' that I stored my password in the vault is indicative of any particular errors on my part.

The final error message shows the real problem. Your credentials are not correct or the user is not allow to connect. Hence MySQL Workbench asks again for them when you test the connection (as it failed to connect using your stored credentials).