Is it possible for a node to host multiple cluster in relation to Always On AG In my example, I have 3 database servers with their own DR servers, own availability group, own clusters.  Is it possible to setup another server which can be part of the 3 clusters and be a secondary replica on each of them?

Taking from your last comment

  yeah im asking if each of the primary replica in each cluster can create a secondary replica in a new server. even though each are coming from different clusters. can the server (to the most left) be on each of the cluster?

This is possible but you would have to use Distributed Availability groups and should have Windows Server 2016 or above as OS.

Also note that the third server cannot be part of all WSFC(Cluster 1,2 ,3). If you want to achieve what you need you would have to create new WSFC and add new machine as part of it. Now you can configure distributed AG's to achieve what you need.


Table name both exists and does not exist in the same query I'm trying to send some data to an Oracle database.  The parameterized query looks like this:
INSERT ALL
INTO mySchema.myTable (c1, c2, c3) VALUES (?, ?, ?)
INTO mySchema.myTable (c1, c2, c3) VALUES (?, ?, ?)
INTO mySchema.myTable (c1, c2, c3) VALUES (?, ?, ?)
INTO mySchema.myTable (c1, c2, c3) VALUES (?, ?, ?)
INTO mySchema.myTable (c1, c2, c3) VALUES (?, ?, ?)
SELECT 1 FROM DUAL

When I run it, I get a truly bizarre error message:

Error: 942, Position: 506, Error msg: ORA-00942: table or view does not exist

Position 506 is the fourth iteration of mySchema.myTable in my query.  But they're all the same table name!  How is it that the first three copies of the table name exist just fine, and then the fourth one is nowhere to be found?
I know Oracle has a reputation for being difficult to deal with, but this is just straight-up insane!  Has anyone seen this before, who might know how to get it working?

I know Oracle has a reputation for being difficult to deal with,

No comment

but this is just straight-up insane! Has anyone seen this before, who
might know how to get it working?

It's working just fine.  As it parses that single sql statement, it is finding multiple errors . . .  multiple times it refers to a non-existent table.  The error that it reports is simply the last one ...
SQL> --
SQL> INSERT ALL
  2  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  3  SELECT 1 FROM DUAL
  4  ;
INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
              *
ERROR at line 2:
ORA-00942: table or view does not exist

SQL> --
SQL> INSERT ALL
  2  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  3  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  4  SELECT 1 FROM DUAL
  5  ;
INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
              *
ERROR at line 3:
ORA-00942: table or view does not exist

SQL> --
SQL> INSERT ALL
  2  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  3  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  4  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  5  SELECT 1 FROM DUAL
  6  ;
INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
              *
ERROR at line 4:
ORA-00942: table or view does not exist

SQL> --
SQL> INSERT ALL
  2  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  3  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  4  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  5  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  6  SELECT 1 FROM DUAL
  7  ;
INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
              *
ERROR at line 5:
ORA-00942: table or view does not exist

SQL> --
SQL> INSERT ALL
  2  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  3  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  4  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  5  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  6  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  7  SELECT 1 FROM DUAL
  8  ;
INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
              *
ERROR at line 6:
ORA-00942: table or view does not exist

Your reported error points, and the fact that you mention parameterized values, indicate that your actual use case is more complex than my demo, raising the possibility of there being other issues at play.  But since we can only go with what we see, we can't address those potential issues.


Do auto created statistics indicate that you should build an index? In a blog post from 2008 the famous Kalen Delaney wrote:

  What is the difference between statistics and indexes? Do auto created
  statistics indicate that you should build an index?

  Check out this the answer to this question at SQLCommunity.com

Unfortunately SQLCommunity.com is no longer. But the answer to this very question I would very like to know. Especially to the second one.

Update: An insightful article is here: http://msdn.microsoft.com/en-us/library/ms190397.aspx

No, they don't.  If statistics are automatically created, it is because the optimizer can benefit from the single-column statistics of the cardinality estimates.  It's simply an option.  I'm guessing your real question may stem from statistics being automatically updated (as opposed to automatically create).

BOL definition for Statistics:

  Statistics for query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view. The query optimizer uses these statistics to estimate the cardinality, or number of rows, in the query result.

There are many parameters for exactly WHEN statistics are marked as invalid (therefore updated when the next using query executes).  Please see this blog post for a list of when this happens.

Take an example like this:  You have a sequential table.  You do 10 million sequential inserts at the end of the table.  There is going to be zero fragmentation in that table.  But the statistics will be forced as invalid (stale) and surely updated.

Therefore, the stats are updated.  But there is no need to rebuild that clustered index (provided it's not a heap).

Often times, there will be inserts that aren't as sequential and this will cause page splits.  So many inserts, much index fragmentation.  Many inserts, stale statistics.  So will stats be updated and the need for an index rebuild?  Yes, absolutely.

But these two do not walk hand-in-hand.


SQL Server FILESTREAM access denied I have my machine and a database server on the same Windows domain. I also have a staging machine that is not on the domain.

From my machine, I am able to perform any and all operations using FILESTREAM without trouble. From the staging machine, my application is able to use TSQL as it pleases, but the moment it tries to open a SqlFileStream, I receive 'access is denied.`

The staging machine, the database server, and my machine all have a local user set up with an identical name and password so that the application can use integrated security to connect to SQL Server. Since FILESTREAM is the only thing not working for the non-domain machine, I am wondering if I am missing something in the FILESTREAM documentation or if the documentation fails to specify that only Active Directory will suffice for integrated security, or of course if there is something I can do to make my situation work.

The problem is not the user account, as queries are able to run without issue. The problem is firewall-related. Make sure that ports 139 and 445 are open between the application server and the database server, as per http://msdn.microsoft.com/en-us/library/dd283098.aspx:

  To use FILESTREAM in a firewall-protected environment, both the client
  and server must be able to resolve DNS names to the server that
  contains the FILESTREAM files. FILESTREAM requires the Windows
  file-sharing ports 139 and 445 to be open.


MySQL: grant SELECT to all databases except system I need to add an user able to read all my databases.
So I create:

create user 'reader' identified by 'password';
grant select on *.* to 'reader';

The problem with this is that the user is able to read also system tables like mysql.user, which I think is a security problem.
I tried to revoke the select privilege on that table:

revoke SELECT ON mysql.* from 'reader';

But I get

ERROR 1141 (42000): There is no such grant defined for user 'reader';

What am I doing wrong? Is there another way to achieve what I want?
Or manipulating the grant tables is the only way?

may i correct what you have posted above..

"create user 'reader' identified by 'password';
grant select on . to 'reader';"

try below

create user reader@whatEverIPAddressOfUser identified by 'password';
grant select on . to reader@whatEverIPAddressOfUser;
flush privileges;

then issue your revoke statement

revoke SELECT ON mysql.* from reader@whatEverIPAddressOfUser;
flush privileges;


Is there any way to monitor execution of xp_cmdshell in SQL Server 2012? I've been tasked with assessing the security risk involved with allowing a third party vendor to debug their queries remotely within SSMS. This means that the user's account must be in the sysadmin fixed role. While this means that the user for all intents and purposes "owns" the SQL Server instance, we have set that instance to a service account specific to that vendor. I'm left with primarily assessing the damage that can be done using xp_cmdshell.

I know that a sysadmin can undo almost anything that is implemented to monitor that activity, but if there is a way to monitor it (using Extended Events perhaps?) and/or log function use it would provide us with an additional level of security.

Audit events are actually not available through Extended Events. You would need to use SQL Server Audit, which more or less works on top of the Extended Events engine (at least from what I understand).

Steps to go with:

Create a Server Audit
Create a Server Audit Specification
Then go back and enable them.

It is fairly straight forward in using the UI via SSMS to create them but for purposes of this post, this would be the T-SQL to do the same thing:

USE [master]
GO

CREATE SERVER AUDIT [cmdshell-20150603-124954]
TO FILE
(   FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL12\MSSQL\Log\' )
WHERE object_name = 'xp_cmdshell';
GO

CREATE SERVER AUDIT SPECIFICATION [Audit-cmdshell]
FOR SERVER AUDIT [cmdshell-20150603-124954]
ADD (SCHEMA_OBJECT_ACCESS_GROUP);
GO

Then when you go back and view the Audit Collection it you would see something similar to this:

In addition with regard to the audit being turned off you can create a seperate server audit that will specifically log when any SQL Server Audit is disabled or started. This will at most give you who stopped it.


SQL Server Client Tools: How can you tell what SQL edition the client tools are from? While working on SQL Server: Unable to save import export package, but am on Standard Edition , the question came up:

We know how to ask SQL Server what edition it is. (Look at properties of server in SSMS, "select @@version" etc.). But how do we look at an install of client tools and tell what version the client tools are?

Go to Management Studio's Help > About... menu. For example, I am running the latest build of SQL Server 2016 (CTP 2.1), and this is what my Help > About shows:

If I click Copy Info, I get this in my clipboard:

Microsoft SQL Server Management Studio          13.0.300.44
Microsoft Analysis Services Client Tools        13.0.300.44
Microsoft Data Access Components (MDAC)         10.0.10166.0
Microsoft MSXML                                 3.0 6.0
Microsoft Internet Explorer                     9.11.10166.0
Microsoft .NET Framework                        4.0.30319.42000
Operating System                                6.3.10166

There are no longer any "editions" of client tools, starting with SQL Server 2012 Service Pack 1. Before that, there were only two editions: licensed and Management Studio Express. Help > About would yield that information as well, and the title bar would say so too.


SQL Server OUTPUT inserted Is it possible to apply a condition on an output.inserted statement?

DECLARE @tblA TABLE (foo INT)
DECLARE @tblOut TABLE (bar INT)

INSERT INTO @tblA
OUTPUT inserted.foo INTO @tblOut(bar) --WHERE foo = 1
VALUES (1),(2),(3)

As far as I know, it is not possible to apply condition on OUTPUT directly, but you can achieve what you need using output of the MERGE:

INSERT INTO @tblOut(bar)
SELECT foo
FROM (
    MERGE INTO @tblA t
    USING (VALUES (1),(2),(3)) s(foo)
    ON 1 = 0
    WHEN NOT MATCHED THEN
        INSERT (foo)
        VALUES (s.foo)
    OUTPUT inserted.foo
) m
WHERE m.foo = 1;


Error in table 'sys.sysbinobjs' on master database We have a SQL server DatabaseIntegrityCheck job to check the SYSTEM_DATABASES, and it shows an error message below in the logs now:

  Msg 2570, Level 16, State 2, Line 1 Page (1:354), slot 28 in object ID
  58, index ID 1, partition ID 281474980511744, alloc unit ID
  281474980511744 (type "In-row data"). Column "name" value is out of
  range for data type "nvarchar". Update column to a legal value.
  CHECKDB found 0 allocation errors and 1 consistency errors in table
  'sys.sysbinobjs' (object ID 58). CHECKDB found 0 allocation errors and
  1 consistency errors in database 'master'.

Recommendation is to install SP4 - already done and problem still exists.

Any thoughts please other than rebuild master - no backup available unfortunately.

There's nothing you can do to fix it, I'm afraid, without a backup, you can't fix errors in system tables like this and I actually would've been surprised if installing SP4 would've worked.

The best (and probably only) way to resolve this is to create a brand-new, clean install of SQL Server somewhere else, then restore all of users database there, and script out your logins and jobs etc. to the new site.

Not ideal, I know, and a bit time consuming perhaps, but easy to do and it'll definitely solve your problem. You might be able to sell it as/use it as an opportunity to upgrade everything, spec the system exactly who you'd like it and get everything updated. There will be some downtime (but this can be minimised).

This is similar to your situation (but perhaps worse) and Paul Randal's reply puts it quite succinctly. This on SSC is another similar one, but not answers (except a confirmation that there's not a lot you can do). Finally, this MSDN post explores the problem as well - same conclusion.


Is it worth checking value with select before updating I have a tinyint boolean value in my row that is 0 by default and 1 whenever a specific thing happens. After it's 1, it's always gonna stay 1.
Now that specific thing can happen for each row many times, but I only need to update the value to 1 once.

Is it worth doing a SELECT, check the value and only if the value is 0, trigger the UPDATE?

Also would maybe this

UPDATE table SET lock='1' WHERE id='100' AND lock='0'

be better than

UPDATE table SET lock='1' WHERE id='100'

for my situation?

Well, "13.2.11 UPDATE Syntax" suggests, that no writing is done, when the value doesn't actually change.

  If you set a column to the value it currently has, MySQL notices this and does not update it.

So the extended WHERE clause isn't needed to prevent unnecessary writes.

But you might get a tiny performance benefit from the extended WHERE clause as if not record to update was found, the logic to check, if the row actually needs to be changed won't run. But really, that's tiny.

And the effort for retrieval is there in either way. You can only try to support it with indexes (on id and lock (compound) for the extended version and on id for the other).


Converted ERD into Dependency Diagram 1NF, 3NF Thats the ERD.

I'm studying for an upcoming test and having trouble getting my head around converting thes ERDs into dependency diagrams. This is from a previous exam that the Lecturer told us to study just btw so he said it will be pretty similar to this.

We have to -

-Convert the ERD into a dependency diagram.
-And then, conver that diagram into a 3NF model, with no transitive or partial dependices.

Any help would be much appreciated as I'm struggling to get my head around it and need to pass this test :/

Thanks in advance!

The first, you should read the NORMALIZATION concepts (1NF,2NF,3NF,...) after that you can use them to verify your dependency diagram. So, I'm talking about some basic steps to help you convert ERD, which I often do:

1- Identify objects (objects are WHO, WHERE, WHAT, WHEN ), you imagine that they are exited
and can be defined.
2- Identify natural keys of objects and their properties,...
3- Identify relationships of objects. The step help you answer some questions:

What actions?
Who took place actions?
Where actions are happened?
When actions happened

4-If we have many-many relationship, we separate them into an individual table. Otherwise, we use foreign keys or not.

5- Verify our diagram matching which NF.

Now, we come back your ERD and we will convert it to dependency diagram.

Step 1- identify objects: Here, we can understand that we want to manage list of appointments of doctors and patients. So, we have 2 objects Doctor and Patient

Step 2- identify natural keys/properties of objects: you listed out above.

Step 3- identify relationships of objects: As you know, a patient can have zero or many appointments with doctors and a doctor can have zero or many appointments with patients. So, we have a many-many relationship.

Step 4- Because we have a many-many relationship, we create a table here.

Finally, we have a diagram

Doctor(Doc_FEmpID,Doc_Fname,Doc_FCellPhone)          Patient(Pat_PatientID,Pat_PatientName,Pat_Phone,Pat_Address,Pat_City) Appointment(App_Num,Doc_FEmpID,Pat_PatientID,App_Date,App_Time)

Step 5- verify the diagram again.


how to output 3 column into 3 element in xml format with the same name? I have this code and I can't figure out how to get the expected result?

declare @table table (TAG int, PARENT int,col1 varchar(100)
                                         ,col2 varchar(100)
                                         ,col3 varchar(100))

insert into @table select 1, NULL, 'Value1'
                                 , 'Value2'
                                 , 'Value3'

select TAG,PARENT, col1 as [MyElement!1!!element],
                   col2 as [MyElement!1!!element],
                   col3 as [MyElement!1!!element] from @table for xml explicit

result :

<MyElement>Value1Value2Value3</MyElement>

expected :

<MyElement>Value1</MyElement>
<MyElement>Value2</MyElement>
<MyElement>Value3</MyElement>

It seems that when setting the directive (the 4th item in the !-delimited specification that is the field name alias) to "element", then the 3rd item in that specification is the element name that contains the value, whereas the 3rd item would otherwise be the attribute name that contains the value. In both cases the 1st item in the specification is the element that contains either the attribute or the sub-element. Hence, leaving it blank in [MyElement!1!!element] gave it no name for the child elements.

Instead, fill out the 3rd item so that the specification looks like:

[Parent!1!MyElement!element]

And so using the following query:

SELECT TAG,PARENT, col1 AS [Parent!1!MyElement!element],
                   col2 AS [Parent!1!MyElement!element],
                   col3 AS [Parent!1!MyElement!element]
FROM @table
FOR XML EXPLICIT

returns:

<Parent>
  <MyElement>Value1</MyElement>
  <MyElement>Value2</MyElement>
  <MyElement>Value3</MyElement>
</Parent>

Please note that you must fill out the 1st item in the specification, which means you cannot get this list without having a parent element.

Please also see the MSDN page for Example: Specifying the ELEMENT Directive.


But from what I can tell, max_binlog_cache_size is
  184467440737095*47520* and the maximum for a BIGINT is
  184467440737095*51615* so it should fit, right?

The maximum value of BIGINT SIGNED is 9223372036854775807.
The maximum value of BIGINT UNSIGNED is 18446744073709551615.
SIGNED is the default behavior. Hence the ERROR 1264 (22003): Out of range value error.

In the subsequent examples, you are expecting the @input1 variable to act like a BIGINT. But in MySQL, the datatype of a session variable depends on the value that is being assigned at runtime. For example:

rds@int[(none)]> set @i := 9223372036854775807;
Query OK, 0 rows affected (0.00 sec)

rds@int[(none)]> select @i + 0.0;
+-----------------------+
| @i + 0.0              |
+-----------------------+
| 9223372036854775807.0 |
+-----------------------+
1 row in set (0.00 sec)

In the above example, the variable @input1 is acting like a BIGINT.
But if we increase the value of @input1 by 1(**beyond the range of **BIGINT SIGNED****) the behavior changes.

rds@int[(none)]> set @i := 9223372036854775808;
Query OK, 0 rows affected (0.01 sec)

rds@int[(none)]> select @i + 0.0;
+------------------------+
| @i + 0.0               |
+------------------------+
| -9223372036854775808.0 |
+------------------------+
1 row in set (0.00 sec)

I think that in this case the session variable is acting like a FLOAT.

As suggested by Rick James, CAST the variable to the desired datatype to avoid such an unpredictable situation.


Postgresql: does effective_cache_size includes shared_buffers? I am tuning a PG installation with 32GB of RAM.
The question is: which one of the following sentences is true?

effective_cache_size + shared_buffers ~= 30GB (PG knows that it has shared buffer plus the effective_cache_size)

effective_cache_size ~= 30GB (effective_cache_size includes shared_buffers)

Quote from the manual

When setting this parameter you should consider both PostgreSQL's shared buffers and the portion of the kernel's disk cache that will be used for PostgreSQL data files

(emphasis mine)
So the answer is: yes, it includes the shared buffers.
If you set shared buffer to e.g. 8GB and see that the operating system uses approx 16GB for caching, effective_cache_size should be set to 24GB
Note that this parameter does not allocate any memory. It's just a hint to the optimizer how likely it is that a block needs to physically read from disk.


Is it more secure to go through a 3rd database to connect two databases using the same login? We have the following setup:

Multiple production database containing private data which is used by desktop software
A web database for a public website which needs some data from the private databases
An intermediary database which contains a few views and stored procedures that pull data from the private databases

Currently the website logs in to web database, and the web database connects to intermediary database to pull data or execute stored procedures on the production databases. All the databases are on the same SQL instance, and the entire process uses the same user account.

The user account has full access to the web database and the intermediary database, but can only access specific views and stored procedures of and private database

Is this really more secure than just making the public database connect directly to the private ones?

It seems like the intermediary database is only there to complicates things since the same login is used to access data in all the databases, and it is already limited to just the views / SPs it needs in the private databases. I am hoping to remove it.

One thing jumps out here:

  The entire process uses the same set of login credentials

Problem

So hypothetical userX (whether some meatsack using Excel, or IIS AppPool Identity) can see some views and code. It doesn't matter what database these views and code are in because userX is setup in 3 databases anyway.

However, you lose ownership chaining like this.

Let's say WebDB.dbo.SomeProc calls PrivateDB.dbo.SomeTable. UserX requires permissions on both objects. If this was OneDB.WebGUI.SomeProc using OneDB.dbo.SomeTable then only the OneDB.WebGUI.SomeProc needs permissions. Permissions on referenced objects with the same owner are not checked.

Note: I haven't looked too deeply at cross database ownership chaining. I only know plain old "ownership chaining" well

Now, as per comments you really have 2 databases that can be combined. Not 3 which was originally implied. However, the intermediate and web can be combined.

The other "private" databases can perhaps be combined, but that'll be a separate issue. See the bottom link for a fuller discussion of "one database or many"

Solution?

If the extra databases are code containers only, then schemas are a better idea.

This sounds like you've used "Database" where you should use "Schema" (in the SQL Server sense, not MySQL sense). I'd have a WebGUI schema, a Helper or Common schema (to replace Intermediate database) and Desktop schema. This way you separate permissions based on clients and just have one database

With one database (in addition to "ownership chaining") you can also start to consider indexed views, SCHEMABINDING (I use it always) and such that can't be done with seperate databases

For more on schemas, see these questions:

Schema design - best practices?
Over use/correct use of schemas?

Finally, there appears no reason to have separate databases based on "transactional integrity not required". See this question to explain this:
 Decision criteria on when to use a non-dbo schema vs a new Database


MySQL High Performance for Lots of SELECTs/INSERTs/UPDATEs/DELETEs I am creating a module where every user often gets a record into a table for 10 to 300 seconds.

When the time expires a record gets deleted. The case is: there will be a lot of users and records will change really often - how this will affect application's performance for this table, because records will change really often and I am wondering if mysql is fine with that? Like indexes would come and go, data changes like 200 times/seconds for this particular table. Maybe I am choosing a bad solution for this kind of job. Any suggestions ?

Thank you!

If that's a bad solution would depend on many things. Does this data need to be persistent? Otherwise maybe a solution that simply keeps this data in memory would work better.

"A lot of users" isn't really helping anybody. MySQL will most likely be fine if "a lot" would mean a few hundreds. (Though depending on what else your database has to handle. Several thousand should most likely work too.)

After all, it doesn't matter that much, if you write those records to stay or delete them after a few seconds to minutes. Deleting just makes two operations out of one. And MySQL can for sure handle a very large amount of creating and deleting records. Make sure you use a simple index to find those records again for deletion.

But without actual numbers and some information about the hardware your database server uses, that can't be answered with much precision.

Best thing would be to write some small application, that simply simulates the amount of load you think you will get without doing much real processing, just drop a lot of records against the server, delete them, at the same rate run some queries like the rest of your program would generate. Have a look at your server and see, if that affects it in any way.

Not sure, but there are options to set for MySQL that would allow it to cache a table in memory completely.It does this anyway in many situationsand most likely you won't have to change much. But if you talk about a really very large amount of users and records, you can maybe tweak a few parameters to optimize caching for your special needs.


Always Encrypted Data Validation Just a question in how could i validate duplicate data's in my database if columns is set to be encrypted(deterministic) in db?

I've tried running the following code, but receive the error below.

declare '@test1' varchar(max) = 'XXXXXX'
select * from testing where t1 = '@test1'

  Encryption scheme mismatch for columns/variables '@test1'. The
  encryption scheme for the columns/variables is (encryption_type =
  'PLAINTEXT') and the expression near line '4' expects it to be
  (encryption_type = 'DETERMINISTIC', encryption_algorithm_name =
  'AEAD_AXS_XXX_CBA_HMAC_SHA_XXX', column_encryption_key_name =
  'CEK_Auto1', column_encryption_key_database_name = 'XXX') (or weaker).

How can I find the source of this error, or is there another way to determine duplicate rows?

Deterministic encryption will produce the same value as long as the inputs are the same. In database terms it means that:

  Queries can perform equality comparison on columns encrypted using deterministic encryption, but no other operations (for example, greater/less than, pattern matching using the LIKE operator, or arithmetical operations).

Source

So yes you can see if you have duplicates. How? By comparing the stored value of one row with the others in the column.

A theoretical example would be like this:

Select EncryptedColumn, COUNT(EncryptedColumn) from yourtable
GROUP BY EncryptedColumn
    HAVING COUNT(EncryptedColumn) > 1

This will tell you which values within your EncryptedColumn are duplicated. If you can provide the table schema a better example can be given.


Create view in a different database with dynamic SQL As part of a larger project I'm trying to create a new view on each database in a SQL Server instance.

I've created a stored procedure that among other things is looping through the databases in the instance via a cursor then trying to create the view using sp_executesql.  Unfortunately the stored procedure does not exist in the databases that I am trying to create the views on.  So I'm trying to get sql server to use that database and then run the create view script.  When I run this I receive the error

  'CREATE VIEW' must be the first statment in a query batch.

Any ideas on how I can accomplish this?
Here is the sp_executesql portion of the procedure.

declare @DB VARCHAR(50)
declare @SQL NVARCHAR(max)
set @DB = '[dbname]'

SET @SQL =  N'USE ' + @DB + N' CREATE VIEW vNonhiddenCategories
AS
SELECT categories.categoryid
FROM categories
--WHERE ... irrelevant remainder of view code ...
'
execute sp_executesql @SQL

This will require really having fun with escaping and re-escaping single quotes, but I think the technique you're after is:

DECLARE
  @DB  NVARCHAR(255) = QUOTENAME(N'dbname'),
  @SQL NVARCHAR(MAX);

SET @SQL =  N'EXEC ' + @DB + '.sys.sp_executesql '
+ 'N''CREATE VIEW dbo.vWhatever
AS
  SELECT x = 1, y = ''''x'''', z = GETDATE();''';

EXEC sys.sp_executesql @SQL;

So you're kind of nesting the dynamic SQL; the inner one ensures that the SQL is executed at the target database, not locally.

Another way that I've picked up since this original question appeared:

DECLARE
  @DB  NVARCHAR(255) = QUOTENAME('dbname'),
  @SQL NVARCHAR(MAX),
  @DBExec NVARCHAR(MAX);

SET @DBExec = @DB + N'.sys.sp_executesql';

SET @SQL = N'CREATE VIEW dbo.whatever
  AS
    SELECT x = 1, y = ''x'', z = GETDATE();';

EXEC @DBExec @SQL;

This is slightly tidier because you don't have to double-nest single quotes.

And here is a slightly tidier way to do it without cursors (well, without all the scaffolding of setting up a cursor). Note that the inner dynamic SQL will only be executed in databases where (a) a categories table exists (b) this view does not already exist and (c) it is not a system database (well, not one of the primary system databases, anyway).

DECLARE @SQL NVARCHAR(MAX) = N'';

SELECT @SQL += NCHAR(13) + NCHAR(10)
  + N'IF NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
  + '.sys.views WHERE name = ''vNonhiddenCategories'')
  AND EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
  + '.sys.tables WHERE name = ''categories'')
  BEGIN
    EXEC ' + QUOTENAME(name) + '.sys.sp_executesql N''
    CREATE VIEW dbo.vNonhiddenCategories3
    AS
      SELECT x = 1, y = ''''x'''';''
  END'
  FROM sys.databases
  WHERE database_id BETWEEN 5 AND 32766;

PRINT @sql;
-- EXEC sp_executesql @sql;

Of course "tidier" is in the eye of the beholder.


mysql table very large size although it doesn't have a lot of records I have drupal website, one of the tables is cache_form.  I have a cron to

DELETE FROM cache_form where expire < UNIX_TIMESTAMP(NOW());

which works fine.

The size of cache_form.myd is 4.9G although I have this:

mysql>select sum(length(data))/1024/1204 from cache_form;

11.19144630 <b>==> 11 M

mysql>check table cache_form\G
Table: drupal.cache_form
Op: check
Msg_type: status
Msg_text: OK

mysql>select cid,length(data) from cache_form order by length(data) desc limit 1;
82373 ==> 80K

select * from information_schema.tables where table_name='cache_form'\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: drupal
     TABLE_NAME: cache_form
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 2681
 AVG_ROW_LENGTH: 4621
    DATA_LENGTH: 12391088 <b>==>11M</b>
MAX_DATA_LENGTH: 281474976710655
   INDEX_LENGTH: 177152
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2011-07-11 19:07:38
    UPDATE_TIME: 2011-12-26 08:40:09
     CHECK_TIME: 2011-12-26 08:09:45
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:

I have tried to repair it

mysql> repair table cache_form;
+-------------------+--------+----------+----------+
| Table             | Op     | Msg_type | Msg_text |
+-------------------+--------+----------+----------+
| drupal.cache_form | repair | status   | OK       |
+-------------------+--------+----------+----------+

then I got the correct size!!

Any idea of what might cause cache_form.myd file to be with this size and repair table finally removed the extra space?

Thanks

When it comes to a MyISAM table, deleting rows does trigger a kind of garbage collection in that all the unused space is recording in a linked list.

The data length you saw is correct. Your cron job deleted every row less than UNIX_TIMESTAMP(NOW()). This will not cause the table to shrink. All the delete rows are just chained together.

THere are other options you could have chosen to shrink the table:

Option 1 : ALTER TABLE cache_form ENGINE=MyISAM;
Option 2 : OPTIMIZE TABLE cache_form;
Option 3 : Manually copy to a temp table

Option 1 simply does the following

copies data pages in the .MYD to a temporary .MYD and renames it back to cache_form.MYD.
copies index pages in the .MYI to a temporary .MYI and renames it back to cache_form.MYI.

Naturally, Option 1 will not copy the unused space.

Option 2 performs the same operation as Option 1 and then performs ANALYZE TABLE cache_form; to compute index statistics.

Option 3 would simply be an emulation of Option 1. Here it is:

CREATE TABLE cache_form_new LIKE cache_form;
INSERT INTO cache_form_new SELECT * FROM cache_form;
ALTER TABLE cache_form RENAME cache_form_old;
ALTER TABLE cache_form_new RENAME cache_form;
DROP TABLE cache_form_old;

Whenever you did the compression this way, find out what data_length is for cache_form_new. This will give you an idea what the actual size should be.


Postgresql Cost-Based Vacuum Delay parameters How to set the these parameters on postgresql.conf ?
What I should consider while setting them?
What should I monitor?

#vacuum_cost_delay = 0ms
#vacuum_cost_page_hit = 1
#vacuum_cost_page_miss = 10
#vacuum_cost_page_dirty = 20
#vacuum_cost_limit = 200

is there any good tutorial or example?

The postgres docs are the best place to start


How to order rows randomly but keep groups together? My table looks like this:

GroupID INT NOT NULL
SomeValue INT NOT NULL

I'd like to keep rows with the same GroupID together in the result set. The groups themselves should be ordered randomly, though. SomeValue is supposed to be the secondary sort criterion. Like this:

GroupID, SomeValue
13, 1
13, 2
7, 1
7, 2
11, 1
11, 2
...

ORDER BY GroupID, SomeValue does not order groups randomly. ORDERBY NEWID() is totally random. ORDER BY SomeHash(GroupID), SomeValue comes to mind but I need a new random order each time.

How can this be done?

You only need a random order for the GroupID values, so first get these distinct values and a random number for each, in a derived table:

SELECT t.*
FROM tablename AS t
  JOIN
    ( SELECT GroupID,
             NEWID() AS rnd   -- or another random function
      FROM tablename
      GROUP BY GroupID
    ) AS r
    ON r.GroupID = t.GroupID
ORDER BY
    r.rnd,
    t.SomeValue ;

If you already have a Groups table which holds the GroupID values, you can replace that FROM tablename GROUP BY GroupID with FROM Groups.


distributed queries are configured to run in single-threaded apartment mode I have sql server 2008 R2 64bit Developer's Edition Installed on my machine.

And Microsoft Office 2010 Professional 32bit.

I have been trying to import some Excel data from an Excel sheet into a sql server database. I have used the following query to do this:

Query

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=D:\Files\BlueFile.xlsx;IMEX=1',
                'SELECT * FROM [Sheet1$]')

I understand sql server has disabled this feature by default as a security measure, however I executed the following statements to enable it.

Configuration

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

Error Message

Yet every time I execute the select statement to get data from Excel sheet I get the following error.

  Msg 7308, Level 16, State 1, Line 1 OLE DB provider
  'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries
  because the provider is configured to run in single-threaded apartment
  mode.

As Mark has mentioned in the comments, you are running into a bit-level mismatch between driver sets.  You will need to either install a 64-bit installation of Office (specifically Excel in this case) or install a 32-bit installation of SQL Server and import the data to that instance.


Activity Monitor (SSMS) timeout error on Sql server instance Environment:

Window server 2012R2: RAM   = 4GB

Sql Server 2008R2: Min server memory = 0MB Max server memory = 2147483647MB

Background of problem:

I am managing database of asp.net application and recently application started to crash (when user tried to sign-in to the application; user see timeout exception on the browser).

In order to rectify the issue I set timeout =1000 in connection string of webconfig file and that resolved the issue.

When application was crashing I tried to turn on the activity monitor on Sql server instance but I coudn't start activity monitor and got following error:

My finding so far:

I started my quest and troubleshooted and found that there is memory pressure but I need expert advice if there is any link of activity monitor not starting with memory pressure? I know error is from database as Sql server was busy at the time of crash. How I can further troubleshoot the problem?

You're running out of RAM on the server. One of the comments refers to lowering your Max Server Memory setting. For an instance with only 4GB RAM (which is not really enough to run SQL Server), your Max Server Memory should be set to 2048MB at most, possibly even lower.


Oracle database - get two rows (A,B) for every (COD) row I have a Oracle database and a variable that can have a value for A and a value for B like in this example:

cod   cat  v1   v2   v3
123   A    1    10   11
123   B    0    0    0
111   A    2    3    4
111   B    0    7    0

I have cod in a table RAP and the CAT is A or B if it has values v1,v2 or v3 in two different tables. For the upper result I must have

RAP:

id cod den
1  123 aaa
2  111 bbb

VAL1_A

fk_rap  v1  v2  v3
1       1   10  11
2       2   3   4

VAL1_B

fk_rap  v1  v2  v3
2       0   7   0

how can I write an script to do that so that if I don't have nothing in the VAL1_B to get an row with zero on B cat for the cod. So I have to get an CAT A and B for every COD. I don't have anywhere the value A or B, it's just a text to put in CAT for the values it has in VAL1_A and VAL1_B.

I'm making something like this:

        with cte as
            (select rap.cod,
                    case
                     when (coalesce(val1_a.v1,0)<>0 or
                           coalesce(val1_a.v2,0)<>0 or
                           coalesce(val1_a.v3,0)<>0)
                     then
                        'A'
                     when (coalesce(val1_b.v1,0)<>0 or
                           coalesce(val1_b.v2,0)<>0 or
                           coalesce(val1_b.v3,0)<>0)
                     then
                        'B'
                    end
                     as cat,
                   coalesce(val1_a.v1,0) as v1_a,
                   coalesce(val1_a.v2,0) as v2_a,
                   coalesce(val1_a.v3,0) as v3_a,
                   coalesce(val1_b.v1,0) as v1_b,
                   coalesce(val1_b.v2,0) as v2_b,
                   coalesce(val1_b.v3,0) as v3_b
             from rap
                 full outer join val1_a on val1_a.fk_rap=rap.id
                 full outer join val1_b on val1_b.fk_rap=rap.id
           )
     select cod,
            cat,
            case when cat='A' then v1_a
                 when cat='B' then v1_b
            end
              as v1,
            case when cat='A' then v2_a
                 when cat='B' then v2_b
            end
              as v2,
            case when cat='A' then v3_a
                 when cat='B' then v3_b
            end
              as v3
     from cte

but I have only a value for A or B... I want a value for A and one for B....
How can I do this?

I solve it with UNION ALL:

with cte as
        (select rap.cod,
                case
                 when (coalesce(val1_a.v1,0)<>0 or
                       coalesce(val1_a.v2,0)<>0 or
                       coalesce(val1_a.v3,0)<>0)
                 then
                    'A'
                end
                 as cat,
               coalesce(val1_a.v1,0) as v1_a,
               coalesce(val1_a.v2,0) as v2_a,
               coalesce(val1_a.v3,0) as v3_a,
               coalesce(val1_b.v1,0) as v1_b,
               coalesce(val1_b.v2,0) as v2_b,
               coalesce(val1_b.v3,0) as v3_b
         from rap
             full outer join val1_a on val1_a.fk_rap=rap.id
             full outer join val1_b on val1_b.fk_rap=rap.id
         UNION ALL
         select rap.cod,
                case
                 when (coalesce(val1_b.v1,0)<>0 or
                       coalesce(val1_b.v2,0)<>0 or
                       coalesce(val1_b.v3,0)<>0)
                 then
                    'B'
                end
                 as cat,
               coalesce(val1_a.v1,0) as v1_a,
               coalesce(val1_a.v2,0) as v2_a,
               coalesce(val1_a.v3,0) as v3_a,
               coalesce(val1_b.v1,0) as v1_b,
               coalesce(val1_b.v2,0) as v2_b,
               coalesce(val1_b.v3,0) as v3_b
         from rap
             full outer join val1_a on val1_a.fk_rap=rap.id
             full outer join val1_b on val1_b.fk_rap=rap.id
       )
 select cod,
        cat,
        case when cat='A' then v1_a
             when cat='B' then v1_b
        end
          as v1,
        case when cat='A' then v2_a
             when cat='B' then v2_b
        end
          as v2,
        case when cat='A' then v3_a
             when cat='B' then v3_b
        end
          as v3
 from cte


Query resource quota for PostgreSQL accounts Within the latest (12, 13) versions of PostgreSQL, is there any throttling support on the user-account level, to limit how much data any query can produce and/or how much time + memory it may consume under that user account?
I want to limit specific user accounts to allow up to certain amount of data and/or time to consume within the server, before erroring with "query quota exceeded" or something like that.
Example
I want to configure user account guest to produce "quota exceeded" failure for any query that returns more than 10kb of data or takes more than 10 seconds to execute.

There is no support for resource quotas in PostgreSQL.
Your best bet is statement_timeout:
ALTER ROLE baduser SET statement_timeout = '10s';

You cannot limit the number of rows returned, but perhaps statement_timeout is good enough.


How fast should I expect postgresql to be? I'm assessing postgresql performance for a time-series application, particularly for inserts. I'm running the speed test script from sqlalchemy at https://docs.sqlalchemy.org/en/13/faq/performance.html#i-m-inserting-400-000-rows-with-the-orm-and-it-s-really-slow. You can ignore the ORM stuff, it's the penultimate "core" line which shows the performance.
I was expecting to see from 10 to 100k writes per second as per e.g. (https://docs.timescale.com/latest/introduction/timescaledb-vs-postgres):

However, I'm instead getting the following:

So that's 358 writes per second! Compared to 83k writes per second for sqlite.
That seems really slow to me, but I'm getting this performance consistently across a Windows installation, Docker on Windows, native Ubuntu and Docker on Ubuntu.
Are my expectations just misaligned with reality, or am I doing something wrong?
EDIT:
To clarify, I'm not using Timescale DB's hyptertables feature yet, just plain vanilla PostgreSQL tables. I am using the TimescaleDB docker installation for my docker tests (though not the native test). Specifically, I'm invoking
docker run -d --rm -p 5433:5432 -e POSTGRES_PASSWORD=password timescale/timescaledb:latest-pg12

The trick to getting good performance out of PostgreSQL for inserts using SQLAlchemy is, as @LaurenzAlb hinted at, better management of transactions. SQLAlchemy can, but doesn't by default, use psycopg2's executemany helpers for PostgreSQL.
Turning these on transforms performance for multiple inserts into the same table. Here's the modified code for the python test.
    engine = create_engine(DB_NAME, echo=False, executemany_mode='values')

With executemany_mode=None:
SQLAlchemy Core: Total time for 50000 records 95.0814561843872 secs = 525.8648952855459 /s

with executemany_mode="values":
SQLAlchemy Core: Total time for 50000 records 1.761573314666748 secs = 28383.71788656377 /s

See https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#psycopg2-batch-mode for an explanation.


Command line switch to disable Coherence? We have a Java app that happens to have the coherence jar in the path.

My application doesn't need it, so I'd like to pass some type of -D variable to the JVM to tell it to ignore coherence, or perhaps send it to a no-op cache / tangsol config file?

I've been on the Oracle site but haven't found this yet.  I admit I don't know much about Coherence.  Removing the jar from the app would be awkward at best, it'd be better to handle this through configuration.

Thannks

It's funny that you should ask about this. There are other requests for the same (e.g. some sort of command line flag to "disable" Coherence), and I believe that it is planned for an upcoming release (either the currently-rolling-out 12.1.3 or the following release).

Is there anything in specific that you see Coherence "doing" that you're trying to avoid?

(Disclaimer: I work at Oracle, including on Oracle Coherence.)


Tuning key_reads in MySQL We are trying to tune our MySQL database (Myisam,  5.1.73) which is getting busy.

We investigated Key_reads:

$ mysqladmin ext -ri10 | grep Key_reads

During a normal load we have the following values (average):

Key_reads          40      (per 10 seconds)
Key_read_requests  350000  (per 10 seconds)

But sometimes, during a few dozens of minutes, we see values like this:

Key_reads          1000    (per 10 seconds)
Key_read_requests  350000  (per 10 seconds)

We have increased Key_buffer_size to 512 (which is bigger than the total of our indexes). We still have 16Gb of free RAM.

Should Key_reads be low or null?

Are these peacks normal or avoidable?

DEFINITIONS

First, you need to know what these status variables mean

Key_read_requests : The number of requests to read a key block from the MyISAM key cache.
Key_reads : The number of physical reads of a key block from disk into the MyISAM key cache. If Key_reads is large, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.

OBSERVATIONS

40 reads from disk out of 350000 reads from the MyISAM Key buffer has a Cache hit Ratio of 100 X (1.0 - (40.0 / 350000.0)) = 99.98857 %.
1000 reads from disk out of 350000 reads from the MyISAM Key buffer has a Cache hit Ratio of 100 X (1.0 - (1000.0 / 350000.0)) = 99.7143 %.
I have discussed this aspect of Performance tuning before : How to improve MySQL Server Performance..??

CONJECTURE

When your Key_reads started increasing, it simply indicated you read new data and the index pages accessed have been freshly loaded into the MyISAM Key Cache. Such Key_reads are unavoidable. Once that spike stopped, then either your read requests have drastically reduced or subsequent queries are now using the key blocks you last read into the MyISAM Key Cache. High Key reads indicate that your queries are frequently reading index blocks from disk, loading them into the Key Cache, bumping out index blocks from the MyISAM Key Cache and subsequent queries are not accessing them.

CONCLUSION

Since your cache hit ratio (> 99.00 %) is excellent, I would say you are fine because your Key_reads are significantly low and infrequent.


What determines the time waited for Postgres Transaction Timeout? What parameter would you change to set how long Postgres waits before deciding a transaction has been interrupted and should be rolled back?

I have a client program that is being frequently terminated in the middle of updating a postgres database.  I need to make sure that postgres catches the loss of connection to the client as quickly as possible and immediately rollsback the transaction.

I have control over the timeout settings on the program, if those matter.  I also have control over the postgres database, but I'm not sure what I should be changing on either if not both.

idle_in_transaction_session_timeout (integer)

Releases locks on open transactions that have become idle.

idle_in_transaction_session_timeout parameter


Read only access to Slaves from Server A Here's a little scenario:

Server A which is a Dev server needs READ access to the Mysql databases which are on slaves B and C. No WRITE access is allowed from server A to the slaves.

How do i achieve this ?

First a clarification. A machine is a client, not a server, not running the mysqld program. B and C are servers, they are running mysqld and they are part of a master-slave topology, they are slaves of another server (just assume is D)

And after that, mysql permissions are the answer. Just define a user in servers B and C and grant only the select permission

On both B and on C

GRANT SELECT ON *.* TO 'name_of_user'@'IP_of_A' IDENTIFIED BY 'pass'


Scripted SQL Deployment in a Transaction with Rollback Note: I believe this differs from other questions simply due to the size of the creation script I want to run under a single transaction.

Background

I'm working on automating the deployment of the data side of an application. I'm working with the team's existing scripts. They seem to drop everything and recreate. I don't have much time to make it perfect.

I'm thinking of running their drop-all script outside of a transaction, but running a single long "genesis" creation script inside a transaction at least so if that part should fail, its at a clean point and easier to recover from.

Genesis.sql contains 34k lines and seems to all be CREAT(ions) of functions, procedures, tables, types etc.

Question

Can I do this:

SET XACT_ABORT ON
BEGIN TRAN

# Genesis!

COMMIT TRAN

?

What's really confusing everyone is the impact of the GOs in the script with some saying that it will commit the transactions and other people (on the web) saying GO is just an instruction for the client to send a batch.

Follow-up

So far, this hasn't worked, new stuff is written and I see this message:

  The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

However, I think its mostly due to syntax errors in one or two scripts (that all get concatenated to 30k lines) and the parser is thrown out.

It seems I'll need to dummy run the scripts into an empty, local DB first, to discover syntax errors, then attempt against the target environment server.

Is your database online during your release or do you have a maintenance window?

If you have a maintenance window and are running Enterprise Edition you could create a database snapshot pre-deployment.

Then you can easily revert to the snapshot if your release process does not execute properly.


How to check the pre-allocated database file of MongoDB is Used or not? We have been using MongoDB for a few weeks. One thing we could not understood is Pre-allocated file. Is there any way to check whether the pre-allocated file is currently used by MongoDB or not?

We have planned to implement "Quota File". So we need to ensure the pre-allocated file is currently used or not.

For Example, I have the database file as follows:

sgserver.0 ---> 16MB
sgserver.1 ---> 128MB
sgserver.2 ---> 256MB
sgserver.3 ---> 512MB

How do I check the pre-allocated file sgserver.3 is used by MongoDB (for example, this database is used by any BSON or its Un-used)?

I am not very familiar with MongoDB, but from this link:

  As soon as a datafile starts to be used, the next one will be preallocated.

It seems to me that (on *nix at least), MongoDB will always have a datafile that is empty, or mostly empty. If it's writing heavy and filling up the file, the last file might have some data in it before a new empty file can be created.

  Additionally, on Unix, mongod will preallocate an additional datafile in the background and do background initialization of this file. These files are prefilled with zero bytes.

So to determine if it's used, check the file for non-zero bytes.


How can I get all orphan records in a single query? I just noticed few tables that has orphan records (foreign keys) that  no longer point to any primary key. How can I query the tables to get a list of all such rows? When these parent records got deleted or how to handle this?

I'm using SQL Server 2008 R2 and we are trying to import 3 similar databases into one after cleaning it up, please advise.

Scripted a sample child table (DocumentDistribution) and parent table (DocumentSource):

ALTER TABLE [dbo].[DocumentDistribution] WITH NOCHECK ADD CONSTRAINT [fk_documentdistsourceid_documentsourceid] FOREIGN KEY([DocumentDistSourceID]) REFERENCES [dbo].[DocumentSource] ([DocumentSourceID])
GO
 ALTER TABLE [dbo].[DocumentDistribution] CHECK CONSTRAINT [fk_documentdistsourceid_documentsourceid]

Doesnt that tell me that the tables are in relationships and explicitely defined the foreign key constraints?

This should only be possible if you have a relationship between tables that you know about but SQL Server doesn't.

SELECT fk
  FROM dbo.ChildTable AS c
  WHERE NOT EXISTS
  (
    SELECT pk FROM dbo.ParentTable AS p
    WHERE p.pk = c.fk
  );

Now, in the future, define this relationship explicitly, then people won't be able to delete the parent rows while child rows still exist. To delete the rows that shouldn't be there today:

DELETE c
  FROM dbo.ChildTable AS c
  WHERE NOT EXISTS
  (
    SELECT pk FROM dbo.ParentTable AS p
    WHERE p.pk = c.fk
  );

Next, take away their rights to disable these constraints so that they stop doing this the wrong way.


Out Param With OpenQuery I am trying to output a parameter from openquery, but my syntax produces this error

  Msg 137, Level 15, State 2, Line 6
  Must declare the scalar variable "@NameID".
  Msg 137, Level 15, State 2, Line 8
  Must declare the scalar variable "@NameID".

What in my syntax should be altered in order to have @NameID returned from the query?

Declare @sql nvarchar(4000), @Name nvarchar(100), @Nameid nvarchar(25)
SET @Name = 'Foxtrot'
SET @SQL = 'DECLARE @NameID nvarchar(25) Select @Nameid = id FROM OPENQUERY(BlueBox, ''Select id from employees where id = '+CHAR(39)+CHAR(39)+@Name+CHAR(39)+CHAR(39)+ N' '')'
PRINT @SQL
EXEC sp_executesql @sql, @NameID = @NameID OUTPUT;

Select @NameID

According to sp_executesql documentation you need a @ParamDefinition.

Declare @sql nvarchar(4000)
Declare @ParamDefinition nvarchar(400)
Declare @Name varchar(100)
Declare @NameID nvarchar(25)
SET @SQL = N'Select @Nameid = id FROM OPENQUERY(BlueBox, ''Select id from employees where id = '+CHAR(39)+CHAR(39)+@Name+CHAR(39)+CHAR(39)+ N' '')'
SET @ParamDefinition = N'@Name varchar(100), @NameId nvarchar(25) OUTPUT'
EXEC sp_executesql @SQL, @ParamDefinition, @Name='Foxtrot', @NameID = @NameID OUTPUT;
Select @NameID

You can check it here: http://rextester.com/WTCOZ80449


Suggest better design pattern for reverse DNS lookups I'm working on a project where I need to look up the hostnames associated with IP addresses who were logged making HTTP requests. The lookups currently happen as part of a daily ETL job. The current method is to use a scalar CLR function (similar code to this is posted a number of places on the web, posted below with my revisions; I'm not sure who the original author was):

using System.Data.SqlTypes;
using System.Net;
using System.Security;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

public partial class udfn_GetHostName
{

[Microsoft.SqlServer.Server.SqlFunction]
public static string udfn_GetHostname(string IPAddr)
{
    try
    {
        /*
         Using deprecated method intentionally.
         GetHostEntry() is now recommended.
         But it does some irritating things like returning an error if a PTR
         record points to a name that doesn't have an A record.
        */
        IPHostEntry IpEntry = Dns.GetHostByAddress(IPAddr);
        // Test whether the record returned has at least one alphabetic character
        // If it does, then it's a name
        // Otherwise the DNS server might have returned the IP address
        Match match = Regex.Match(IpEntry.HostName.ToString(), @"[a-zA-Z]+");

        if (match.Success)
        {
            return IpEntry.HostName.ToString();
        }
        else
        {
            return "None";
        }

     }
    catch(Exception ex)
    {
        return "Failed";
        //return ex.Message.ToString();
    }
  }
}

I'm not a C# developer so the quality of the CLR code is likely not great.

Then I call the function like this after loading new rows into the dimension:

-- Update only rows that we just inserted
UPDATE DIM.Network_Addresses
SET reverse_dns = dbo.[udfn_GetHostname](client_ip)
WHERE reverse_dns IS NULL
AND is_current = 1
AND created_date = (SELECT MAX(created_date) FROM DIM.API_Network_Address);

This method works but is very slow, for at least a couple reasons.

1) Using a scalar function makes SQL Server call the CLR function
    once per row that needs to be updated, using a new SQL context.

2) The function calls themselves are very slow due to how GetHostname() and other CLR name resolution functions work: long timeouts, sometimes multiple round trips across the network that all time out if a DNS server doesn't respond or there is no PTR record, etc.

Could any recommend a design pattern that would improve the performance of looking up reverse DNS records and updating the table?

I'm considering a few different things:

1) Move this work outside of the database and use a tool such as dig to do the lookups in parallel.

2) Try to find some way to call the function in parallel or convert it to an in-line function (not making much progress on this!)

However, any ideas would be welcome.

Do not do it in SQL. From the few things that can be categorized as 'stupid use of SQLCLR', making expensive lengthy network calls ranks as #1. At the very very very very least, make sure the CLR code calls Thread.BeginThreadAffinity() before it goes into waiting for the intertubez to respond (DNS lookup and reverse lookup included).

The proper way to handle this is to use an external process, place the IPs to resolve in a queue, dequeue in batches and resolve several (tens) of IPs in parallel using asynchronous I/O, eg. the non-obsolete Dns.BeginGetHostEntry().


"Cannot create a row of size 8074 which is greater than the allowable maximum row size of 8060" while altering the table I am trying to alter a column in a table. The existing table is like this:

CREATE TABLE [dbo].[table](
   [id1] [int] NOT NULL,
   [id2] [int] NOT NULL,
   [id3] [int] NOT NULL,
   [name] [nvarchar](255) NOT NULL,
   [id4] [int] NOT NULL,
   [xmlData] [xml](CONTENT [dbo].[xml_schema]) NULL,
   [booleanData1] [bit] NOT NULL,
   [notes] [varchar](4096) NULL,
   [id5] [int] NULL,
   [booleanData2] [bit] NULL,
   [id6] [int] NULL,

   CONSTRAINT [PK_table] PRIMARY KEY CLUSTERED
   ([id1] ASC, [id2] ASC, [id3] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Now I am trying to execute this sql on this table:

ALTER TABLE [table] ALTER COLUMN [xmlData] XML

so that I can drop the xml schema and replace it with new one.

But I am getting this error:

  Cannot create a row of size 8074 which is greater than the allowable maximum row size of 8060.

Can anyone tell me what is the problem here?

If you have previously removed or modified columns on this table, you may need to reclaim space before this operation will succeed. SQL Server does not always/usually immediately reclaim space for dropped or altered columns.

If the previous operations were all removals (or alterations) of variable-length columns, issuing DBCC CLEANTABLE ought to be sufficient. Otherwise, you will need to rebuild the table. You can do this by rebuilding the clustered index:

ALTER INDEX PK_table
ON dbo.table
REBUILD
WITH (ONLINE = ON); -- Enterprise only, optional


MSDTC on SQL Server 2012 with mount point for data disk I have installed a new SQL Server 2012 instance on a cluster. The installation went fine but when I've added the MSDTC as resource of the instance, it went wrong. When I just added it, the MSDTC was online but when I tried my first failover, it refused to go online. The error message was "MSDTC failed".

My configuration is:
a physical drive disk (let say J:) with two mount points "DATA" and "LOG".

I read some people who experienced this kind of errors with mount points and SQL Server 2008 but nothing with 2012.

I tried to create the "MSDTC" folder on the physical drive (the J: one) but nothing better.

Does anyone know something about it or know how to configure the MSDTC and force it to use the J: drive?

When parametrized, the dependency disk is the disk on which the msdtc will create its directories. This disk must NOT be a mount point.

Hope it can help someone


Search for first and lastname without order maybe there is a simple answer to my problem:

I have a mssql table ('Person') with a field for first and one for last name.
I am coding a Program, where the User should be able to search for persons.

  SELECT id, firstname, lastname FROM Person WHERE firstname LIKE
  @searchstring + '%' OR lastname LIKE @searchstring + '%'

doesn't work very well, because if the user types 'John W' nothing is found.

For example the person I search for is called John Williams:
It should work with searchstrings like 'John W', 'Williams Jo', 'Will' or 'Joh'

Concatenating first and lastname and use %Searchstring% also doesn't work well, because if the searchstring is e.g. 'Will' it should find people who are called Will, Willibald, Williams or Willson or something like that, but not a Person called Goodwill e.g.

And the query should be as fast as possible, because it is executed at every keydown...

edit: forgot to mention that the database must not be changed, because it is from another company.

Thank you for your help.

There are two main components to the question: the searching of the name column and how the data is delivered to the user. As predictive typing is more related to programming, caching, etc. and as Aaron mentioned is not a good idea to do on each keystroke, I will not cover it.

As for how best to query the database:

The logical choice here is Full Text Search, because that provides the exact features you're looking for:

  Full-text queries perform linguistic searches against text data in
  full-text indexes by operating on words and phrases based on rules of
  a particular language such as English or Japanese. Full-text queries
  can include simple words and phrases or multiple forms of a word or
  phrase. A full-text query returns any documents that contain at least
  one match (also known as a hit). A match occurs when a target document
  contains all the terms specified in the full-text query, and meets any
  other search conditions, such as the distance between the matching
  terms.

However, the OP has updated the question to say that the database belongs to another company. So that leaves two main options:

Using a complex setup of the LIKE operator
Creating a localized version of the database and implementing the Full Text Search features.

Option 1

  For example the person I search for is called John Williams: It should
  work with searchstrings like 'John W', 'Williams Jo', 'Will' or 'Joh'

To achieve this, you'd need to split the user's input by spaces (or some other desired delimiter) and dynamically build your WHERE clause based off that in a format like

WHERE ... LIKE '%' @string1 + '%' OR ... LIKE '%' + string2 + '%' OR ...

While possible, this is far from ideal as the LIKE operator is not very efficient when it comes to comparing strings, even if there are only 200,000 rows. It will not scale well and I wouldn't trust it for this use case in a production system:

  In contrast to full-text search, the LIKE Transact-SQL predicate works on character patterns only. Also, you cannot use the LIKE predicate to query formatted binary data. Furthermore, a LIKE query against a large amount of unstructured text data is much slower than an equivalent full-text query against the same data. A LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query can take only seconds or less against the same data, depending on the number of rows that are returned.

Aside from this, you'll have to accommodate users who have special characters (language-specific characters, punctuation), extra spaces, abbreviations, etc. in their input.

Not the "right" choice by any means, but you can probably hack it together if you must.

Option 2

Use replication, mirroring, etc. to get a local copy of the user data and build a Full Text Index on it to leverage all of the features, like a thesaurus, language support, and limiting search results based on rank of relevance which would greatly help your predictive text typing if you still decide to pursue it.

While requiring more up front effort, this is the "right" way to do this.


delete records in one table based on multiple column values in second table I need to insert and delete records in table 1 based on the data in multiple columns in table 2.

Example:  Table1 and Table2 have identical columns: address, city, state, and zip.

Table1 is the master table with millions and millions of records.
Table2 (thousands of records) is a table based on data that changed in some way - although it may not be a change in data in this table.

Matching records means that the address, city, state, and zip are identical in value in both tables.

So I want to do 2 operations:

Insert records into Table1 that only exist in Table2
Delete records from Table1 where the record is not in Table2 but the city
is in Table2

Based on the size of Table1, I need a very efficient way to execute this.
I know some SQL - but not to the level this would require if done with SQL.

This is a DB2 database - version 10.5.

Operation 1

Insert records into Table1 that only exist in Table2:

INSERT INTO Table1
    (address, city, state, zip)
SELECT address, city, state, zip  FROM Table2
EXCEPT
SELECT address, city, state, zip  FROM Table1 ;

Operation 2

Delete records from Table1 where the record is not in Table2 but the city is in Table2.

(assuming that the columns ar enot nullable. If they allow nulls, it would be better to rewrite the NOT IN with a NOT EXISTS subquery):

DELETE FROM Table1
WHERE (address, city, state, zip)
       NOT IN
      (SELECT address, city, state, zip  FROM Table2)
  AND city IN
      (SELECT city FROM Table2) ;

As for performance and efficiency, I have almost zero experience with DB2, so it would be better if you waited for some other answer. But an index on all the columns (address, city, state, zip) - on both tables - would probably be the best you can do for the queries, plus a single index on (city). Or perhaps combine the two suggestions in one index with all the 4 columns but choose city for the 1st part of the index: (city, address, state, zip).

You haven't provided the tables' structure but the 4 columns seem appropriate for a UNIQUE constraint so you may already have such an index.


Database schema for timesheet app? I am very new to database managements as I am enrolled in my first class involving databases at university. For my project, I have to create an app that clocks employees in an out and keeps track of hours they worked. There are many employers who each employ many employees who each can technically clock in and out an unlimited number of times daily. My goal is to be able to easily select an employer, get all his/her employee, and be able to make a table for their hours for any given week. Here's how I was thinking to design the schema:

Employer
Employer id
Employee id (FK)
Name

Employee
Employee id
Employer id (FK)
Name
Password

Timesheet
Employee id (FK)
Employer id (FK)
Timestamp
A boolean indicating whether the employee is checking in or out

So every time an employee checks in or out I put an entry to the timesheet table. Then to grab a week, I grab all the timesheets for an employee within that week's range and compile them into a table. This doesn't seem to be the most efficient way, but I am not sure of a better way.

Any help is very much appreciated!

Your outline is exactly what you would want to do. If this was a real product the only addition I might add is that for the Timesheet table you would probably want your Clustered Index on the TimeStamp field as it sounds like one of the primary filters and you'll be selecting ranges from the table. If you are always searching by TimeStamp, Employee, and Employer key you can make the Clustered Index on all three.

A Clustered Index is the table itself on the disk, and it will be stored using the selected index as the logical sorting for the data. In most SQL servers when you create a Primary Key it automatically creates the Clustered Index on those columns, however it is not a requirement that they are the same columns (or even both need to be present) and can be on different columns. The primary reason to have the Clustered Index on other columns is to improve query performance on commonly searched data that is grouped by value or accessed via a range.

In general Indexing requires careful analysis of your queries to insure they benefit from all your indexing choices.


how can I call nzsql or nzload from script without disclosing sensitive password? I don't see this in the Netezza documentation.

We have lots of Linux scripts where we do this:

NZ_PASSWD_ENCRYPTED=29TY20T98=
NZ_PASSWD_CLEAR=`DecryptFunc $NZ_PASSWD_ENCRYPTED`
NzResult=$(nzsql -db ${NZ_DATABASE}  -u ${NZ_USER}  -pw $NZ_PASSWD_CLEAR -h $NZ_HOST \
              -qc "select 'hi there folks;'")

I don't like leaving the password hanging there in the command environment where it can be hacked.

Is there any kind of function to handle the password differently in the nzsql script?  Did I miss something in the documentation?  Or any tricks in Linux scripting I could use?

Thanks in advance....

Three options:

export it to the environment.
put it in the script
use nzpassword

the examples are off the top of my head, I don't have an instance to test it out right now, you might need to tweak them a bit.

exporting it to the environment:

NZ_PASSWD_ENCRYPTED=29TY20T98=
export NZ_PASSWORD=`DecryptFunc $NZ_PASSWD_ENCRYPTED`
NzResult=$(nzsql -db ${NZ_DATABASE}  -u ${NZ_USER} -h $NZ_HOST \
              -qc "select 'hi there folks;'")
unset NZ_PASSWORD

Anyone who can read the environment variables set for your shell can still retrieve the password, but it won't show in the process list visible to everyone on the system

putting it in a script:

put the query in a sql file, add something like this at the top of it:

\set STOP_ON_ERROR TRUE
\echo *** connecting to MY_DATABASE ***
\connect MY_DATABASE MY_USER MY_PASSWORD
select 'hi there folks;'

Then use nzsql to execute this file.
You may need to specify a login user to nzsql as well, but any user should do (even one that can only has no other rights besides log in)
Anyone who can read the sql file will be able to see the password, but I don't think it will show up anywhere else

using nzpassword

There is a tool to store passwords encrypted in a hidden ~/.nzpassword file, for later use, with:

nzpassword add -u user -pw password -host hostname

see: http://www-01.ibm.com/support/knowledgecenter/SSULQD_7.1.0/com.ibm.nz.adm.doc/c_sysadm_encrypted_passwords.html


How do databases deal with file system fragmentation? Especially analytic databases that try to optimize for queries that scan large portions of tables instead of randomly accessing specific rows. For example Redshift has a concept of a sort key that, given you VACUUM after major inserts/updates/deletes, ensures the data is sorted on disk according to the sort key. Ideally this minimizes disk seeks and optimizes large scans.

However, my understanding is databases write their data blocks to a set of database files. And these files are managed by the file system which has it's own block scheme that is subject to fragmentation. So how does the database manage this (or maybe more precisely how does something like VACUUM manage this) so that database blocks are sorted correctly on disk?

I'm guessing it's to do with the fact that most databases run on servers where not much else is running? Or the database is usually given its own disk partitions? Or the database files reserve a bunch of space ahead of time? In either of these cases there aren't too many processes writing to the same disk space as the database so there isn't much fragmentation outside of the database's control?

Depending on the operating system in use, and the DBMS in use, there are varying degrees of cooperation between them.

For instance, using SyBase ASE on AIX, you can tell the DBMS to allocate an entire file system as a single monolithic structure for use ONLY by the DBMS.  In this instance, the database is the file system, and file-system-level fragmentation is a non-issue.

For SQL Server, and probably most other DBMS's, database files are stored on a pre-designed file system that may or may not contain other files.  If the filesystem containing these database files does not contain files from any other system, the database files may still become fragmented due to growth of the files themselves.

In a filesystem that contains both databases and other files, filegrowth operations on the database files will almost certainly result in their becoming physically fragmented.  As far as I am aware there are no DBMS's that integrate directly with the operating system to prevent physical file fragmentation.

Many larger production database systems run on highly complex systems including virtualization of the physical machines, and storage-area-networks, or SANs.  These systems are designed to share resources across the machines using them.  In the case of databases stored on a SAN, there is a very good chance the physical blocks on the SAN that contain the database files will in fact be spread across a large number of physical drives, and are unlikely to be contiguously allocated.  Brent Ozar says "If you’re using shared storage like EMC, NetApp, or Dell gear that shares drives between a bunch of different servers, then all of your drive access will be random anyway.  Your hard drives are shared with other servers that are also making drive requests at the same time, so the drives will always be jumping all over the place to get data."1


MySQLdump on specific db date I'm new in here and i don't really know where to start but here is my concern. I need to backyp a MySQL DB running under windows once a week. For e.g every friday i need to backup and transfer to another server the MYSQL DB created on the Thursday.

I have the DB created with the following naming : YYMMDD_HHMM_dbname.

i've tried loads of mysqldump options so far but can't get that specific DB that i would like too.

I would higly appreciate if you guys could help me on that.

With thks..

Your best option is the following:

STEP 01 : Create a SQL script that will write a Windows batch file
STEP 02 : Execute the SQL script to create the Windows batch file
STEP 03 : Execute the Windows batch file

STEP 01

First let's create the SQL file. The SQL file will have these rules

Find the Last Thursday as of Today
Locate the database
If today is Thursday and you run this script, then pick DB for the Thursday before that
Echo the Windows batch file, starting with @echo off

Here is the Windows batch file

SET @dborigin='rolando';
SET @today = DATE(NOW());
SET @this_thurs = @today - INTERVAL WEEKDAY(@today) DAY - INTERVAL 3 DAY;
SET @ut1 = UNIX_TIMESTAMP(@this_thurs);
SET @ut2 = UNIX_TIMESTAMP(@today);
SET @last_thurs = @this_thurs - INTERVAL IF(@ut1=@ut2,1,0) WEEK;
SET @user = 'root';
SET @pass = 'password';
SET @dumpfile = 'C:\\TransferDB.sql';
SELECT '@echo off';
SET @SQL='SELECT CONCAT(''mysqldump -u'',@user,'' -p'',@pass,'' --routines --triggers '',db,'' > '',@dumpfile) mysqldump_command FROM (SELECT schema_name db FROM information_schema.schemata WHERE LEFT(schema_name,6) = DATE_FORMAT(@last_thurs,''%y%m%d'') AND RIGHT(schema_name,LENGTH(@dborigin))=@dborigin) A';
PREPARE s FROM @SQL; EXECUTE s; DEALLOCATE PREPARE s;

Save the SQL script as C:\Make_mysqldump.sql

STEP 02 and 03

C:\> mysql -uroot -ppass -AN < C:\Make_mysqldump.sql > C:\Exec_mysqldump.bat
C:\> C:\Exec_mysqldump.bat

I will demonstrate Steps 01 and 02

DEMONSTRATION

Here the databases on my Windows machine

mysql> show databases;
+---------------------+
| Database            |
+---------------------+
| information_schema  |
| 131128_1234_rolando |
| 131129_1234_rolando |
| 131130_1234_rolando |
| 131203_1234_rolando |
| 131204_1234_rolando |
| 131205_1234_rolando |
| 131206_1234_rolando |
| alisha              |
| beercan             |
| musicdata           |
| mysql               |
| performance_schema  |
| test                |
+---------------------+
14 rows in set (0.00 sec)

mysql>

Here is the text file:

C:\>type Make_mysqldump.sql
SET @dborigin='rolando';
SET @today = DATE(NOW());
SET @this_thurs = @today - INTERVAL WEEKDAY(@today) DAY - INTERVAL 3 DAY;
SET @ut1 = UNIX_TIMESTAMP(@this_thurs);
SET @ut2 = UNIX_TIMESTAMP(@today);
SET @last_thurs = @this_thurs - INTERVAL IF(@ut1=@ut2,1,0) WEEK;
SET @user = 'root';
SET @pass = 'password';
SET @dumpfile = 'C:\\TransferDB.sql';
SELECT '@echo off';
SET @SQL='SELECT CONCAT(''mysqldump -u'',@user,'' -p'',@pass,'' --routines --triggers '',db,'' > '',@dumpfile) mysqldump
_command FROM (SELECT schema_name db FROM information_schema.schemata WHERE LEFT(schema_name,6) = DATE_FORMAT(@last_thur
s,''%y%m%d'') AND RIGHT(schema_name,LENGTH(@dborigin))=@dborigin) A';
PREPARE s FROM @SQL; EXECUTE s; DEALLOCATE PREPARE s;

Here is the creation of the batch file and its contents:

C:\>mysql -AN < C:\Make_mysqldump.sql > C:\Exec_mysqldump.bat

C:\>type C:\Exec_mysqldump.bat
@echo off
mysqldump -uroot -ppassword --routines --triggers 131129_1234_rolando > C:\\TransferDB.sql

C:\>

CAVEAT : I'll leave the rest to your imagination to run the mysqldump and execute C:\TransferDB.sql on the target DB server.

Give it a Try !!!

UPDATE 2013-12-06 18:51 EST

Here is a crazy idea requiring some elbow grease

STEP #1

Remove the first line of C:\Make_mysqldump.sql do it looks like this:

SET @today = DATE(NOW());
SET @this_thurs = @today - INTERVAL WEEKDAY(@today) DAY - INTERVAL 3 DAY;
SET @ut1 = UNIX_TIMESTAMP(@this_thurs);
SET @ut2 = UNIX_TIMESTAMP(@today);
SET @last_thurs = @this_thurs - INTERVAL IF(@ut1=@ut2,1,0) WEEK;
SET @user = 'root';
SET @pass = 'password';
SET @dumpfile = 'C:\\TransferDB.sql';
SELECT '@echo off';
SET @SQL='SELECT CONCAT(''mysqldump -u'',@user,'' -p'',@pass,'' --routines --triggers '',db,'' > '',@dumpfile) mysqldump_command FROM (SELECT schema_name db FROM information_schema.schemata WHERE LEFT(schema_name,6) = DATE_FORMAT(@last_thurs,''%y%m%d'') AND RIGHT(schema_name,LENGTH(@dborigin))=@dborigin) A';
PREPARE s FROM @SQL; EXECUTE s; DEALLOCATE PREPARE s;

STEP #2

Create a new batch file (Call it C:\MakeBackupJob.bat) whose sole purpose is to make a dborigin variable and append the rest. It should have these lines:

@echo off
echo set @dborigin='%1'; > C:\MakeJob_%1.sql
type C:\Make_mysqldump.sql >> C:\MakeJob_%1.sql

EPILOGUE

To use C:\MakeBackupJob.bat, simply specify the database tag name. Suppose the databases are rolando,pamela,dominique,diamond. Simply run the following:

C:
cd \
MakeBackupJob.bat rolando
MakeBackupJob.bat pamela
MakeBackupJob.bat dominique
MakeBackupJob.bat rolando

You should have 4 SQL files in C:\

MakeJob_rolando.sql
MakeJob_pamela.sql
MakeJob_dominique.sql
MakeJob_diamond.sql

Now, run the following:

echo @echo off  > C:\Exec_mysqldump.bat
C:\>mysql -AN < C:\MakeJob_rolando.sql   >> C:\Exec_mysqldump.bat
C:\>mysql -AN < C:\MakeJob_pamela.sql    >> C:\Exec_mysqldump.bat
C:\>mysql -AN < C:\MakeJob_dominique.sql >> C:\Exec_mysqldump.bat
C:\>mysql -AN < C:\MakeJob_diamond.sql   >> C:\Exec_mysqldump.bat

Now, C:\Exec_mysqldump.bat contains 4 mysqldumps to separate files.

Give it a Try !!!


PostgreSQL error: "subquery uses ungrouped column" I have a query with subqueries that use only elements from the GROUP BY clause:

SELECT
        DATE(cm.created_at) as "date",
        COUNT(1) as total_message,
        room_id,
        (SELECT seller_id FROM chat_room cr WHERE cr.id = cm.room_id LIMIT 1) seller_id,
        (SELECT count(1) FROM "order" o WHERE o.room_id = cm.room_id) total_order,
        (SELECT sum(total_amount) FROM "order" o WHERE o.room_id = cm.room_id and DATE(o.created_at) = date(cm.created_at) )  total_order_amount
    FROM chat_message cm
    GROUP BY DATE(cm.created_at), cm.room_id
    ORDER BY DATE(cm.created_at)

Error:

ERROR: subquery uses ungrouped column "cm.created_at" from outer query

This part of the query causes the error:

(SELECT sum(total_amount) FROM "order" o WHERE o.room_id = cm.room_id and DATE(o.created_at) = date(cm.created_at) )  total_order_amount

How to solve this?

You could use a subselect:

SELECT q.date,
       q.total_message,
       q.room_id,
       (SELECT seller_id
        FROM chat_room cr
        WHERE cr.id = q.room_id
        LIMIT 1) seller_id,
       (SELECT count(1)
        FROM "order" o
        WHERE o.room_id = q.room_id) total_order,
       (SELECT sum(total_amount)
        FROM "order" o
        WHERE o.room_id = q.room_id
        and DATE(o.created_at) = q.date) total_order_amount
FROM (SELECT DATE(cm.created_at) as "date",
             COUNT(1) as total_message,
             room_id,
      FROM chat_message cm
      GROUP BY DATE(cm.created_at), cm.room_id) AS q
ORDER BY q.date;


Optimizations Possible - SQLServer I have been given a query that takes about 1:30 to run. I was asked to optomize it as much as possible but am having issues making any headway on it.

SELECT DISTINCT B.PROV,
                FACILITY=CASE B.FACILITY
                           WHEN '' THEN (SELECT FACILITY
                                         FROM   CLDOCTOR
                                         WHERE  DOCTOR = B.PROVCODE)
                           ELSE B.FACILITY
                         END,
                C.PLNAME,
                C.PFNAME,
                C.PMNAME,
                C.DOB,
                C.SEX,
                Cast(Cast(A.DATEBACK AS VARCHAR(12)) AS DATETIME) AS 'DATEBACK',
                C.CITY,
                C.STATE,
                C.ZIP,
                C.HPHONE,
                C.WPHONE,
                A.SSNO,
                C.PTYPE,
                B.ACCOUNT,
                B.ORDERDATE
FROM   CLLAB A,
       CLORDER B,
       CLMASTER C
WHERE  A.COMPANY = B.COMPANY
       AND A.ORDER_ID = B.ID
       AND B.DEPT = 'LAB'
       AND B.ACCOUNT = C.ACCOUNT
       --AND DATEDIFF(MM, DATEBACK, GETDATE()) <=6
       AND A.DATEBACK NOT IN (SELECT DISTINCT DATEBACK
                              FROM   CLLAB
                              WHERE  COMPANY = A.COMPANY
                                     AND DEPT = 'LAB'
                                     AND SSNO = A.SSNO
                                     --AND DATEDIFF(MM, DATEBACK, GETDATE()) <= 6
                                     AND OBSERVE_ID = 'REVIEWED'
                                     )

CLLAB contains 34,185,327 rows, CLORDER contains 13,455,600 rows, and CLMASTER contains 392,797 rows.

Do you see anything that jumps out as inherently wrong that could be cleaned up to help the query run quicker?

Edit 1:
There are indexes on CLLAB's ORDERID, SSNO, and a few other unused columns.
There are indexes on CLORDERS's PROV, ORDERDATE, and a few other unused columns.

The execution plan says Missing Index (Impace 48.1371): CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] on [dbo].[CLORDER] ([DBO].[CLORDER][DEPT]) INCLUDE ([COMPANY],[ID],[PROV],[ORDERDATE],[ACCOUNT],[FACILITY],[PROVCODE]) which makes me think that there could be huge gains made by indexing CLLAB's DEPT

First of all be aware though that when you are querying on tables that large you have to expect it to take some time.

That being said assuming you are using SQL 2008 or later you could try a filtered index on CLLab, however that makes several assumptions.

The number of rows where the Dept = "Lab" and Observe_Id = "Reviewed" is a relatively small percentage of the total number of rows.
This is an important enough query to warrant the extra write time to CLLAB for an additional index just for it.

If the answers to both of those are Yes (and of course SQL 2008 or higher) then try this.

CREATE NONCLUSTERED INDEX "IX_CLLAB_REVIEWED_LABS"
ON CLLAB (SSNO, COMPANY, DATEBACK)
WHERE DEPT = "LAB"
  AND OBSERVE_ID = "REVIEWED";

Really I would rather get rid of the subquery all together if possible but that would require more knowledge of the data and what you are trying to get out of the query.

Last but not least if you comment out the small subquery in the field list does it run significantly faster?  If so that part of the query could be incorporated into the main query as a LEFT OUTER JOIN and might save you some time.

It would look like this.

-- In the field list
FACILITY = CASE WHEN B.FACILITY = '' THEN D.FACILITY ELSE B.FACILITY END

-- The FROM & WHERE Clauses
FROM CLLAB A
JOIN CLORDER B
    ON A.COMPANY = B.COMPANY
    AND A.ORDER_ID = B.ID
JOIN CLMASTER C
    ON B.ACCOUNT = C.ACCOUNT
LEFT OUTER JOIN CLDoctor D
    ON  B.PROVCODE = D.DOCTOR
    AND B.FACILITY = ''          -- optionally. May be more efficient
                                 -- with different indexing
WHERE B.DEPT = 'LAB'
  AND A.DATEBACK NOT IN (.....)


SQL Server 2012 - Can't use AlwaysOn HA. What other options do I have? I have 2 SQL Server 2012 machines. One is production and the other will be for backup. I was looking at using the AoHA but it requires

SQL Server 2012 Enterprise for server OS
an AD domain

I have neither Enterprise or a domain so that throws out the AoHA option.

I was investigating mirroring as that looks to be my next best option but all the documentation I'm reading on MSDN, etc says that this feature will be removed.

For my situation will mirroring work? I have about 50GB worth of DB's and would like to have them duplicated on a 2nd server in case the primary goes down or something.

Quick update - based on comments I looked into Log Shipping and that solved my issues. My DB's are now backing up on a secondary server via the Log Shipping method (I also offsite backup the DB's as well nightly). Thanks for the help.

Update: as Paul White mentions in a comment, it has been announced that SQL 2016 will support two-node alwayson groups via the limited "Basic AlwaysOn Group" feature. As Windows 2012 Standard edition includes clustering there will be no need for "enterprise" licensing to use a simple AlwaysOn pair. As SQL2016 is not yet released this does not immediately affect the asker of this question, things may change further between now and RTM, and Valien is already using SQL2012 currently, the answer is still correct as of now but there will soon be a more-or-less direct replacement for simple mirroring in SQL Standard Edition.

But before SQL 2016 is released...

Mirroring being dropped in future versions is an annoyance to many who can't use AO instead (mainly due to the enterprise edition requirements), but if you need to use it you'll find it present and supported as far as SQL2014 (in MS release terminology deprecated means "we intend to remove it at some point, so be prepared to switch sooner rather than later" not "stop using it right now or you are in an unsupported configuration"). As 2016 is not released yet the final feature set can not be confirmed but the length of 2014's support window gives you some time to play with before you have to switch to some other method so you could go with mirroring now but make sure management know that you need to put some time into researching future options.

Having said that implementing a fresh solution with a deprecated feature (what-ever definition of "deprecated") is not generally recommended.

If your intention is to prove disaster recovery then a good backup solution may be all you need: set your DB to full recovery, take full backups regularly and log backups even more regularly in between, make sure copies of these backups are kept off the main server and tested regularly.

The next step up would be to automate the testing of the backups: regularly restore on the second box the last full backup and the subsequent log backups. Or to achieve the same thing, implement log shipping. In either case have some automated solution scan the destination copy to make sure that it is running OK and has data you would expect (if your log backup is every hour then at any given time any change in the source DB made a little more than an hour ago should be present in the destination, if the application doesn't already have something you can easily check (such as a full audit trail with regular enough activity) then have a table with a single datetime column set a scheduled task to drop CURRENT_TIMESTAMP in there once per minute), have this drop you a mail/sms/other if it detects a problem (either database not available, or data in the secondary looks too old). This way you don't have to restore from scratch when something catastrophic happens: you should be at most one log restore away from having a relatively up-to-date database on the second box. This isn't as smooth as the quick switch-over possibility provided by mirroring or AO but it may be sufficient for your needs. Do consult with your users to make sure this is in fact sufficient - get them to specify "maximum downtime" and "maximum data loss in a DR situation" times, i.e. "service must be restored inside a two hours, we must not lose data from more than an hour before the outage" or some such (this can vary widely: some of the low volume systems we manage have a pleasantly lenient 24 hour loss window, if the business needs something in the few-minutes-or-less range then you probably need to convince them to stump up for relevant licensing so you can use AO (or just use mirroring for now but let them know the mid/long tern need to change due to the deprecation of the feature)).

Of course be aware that the backups are held off-site as well as on-site: while the most common need for DR is hardware failure within a single server your data could be rendered inaccessible by a number of things that would make a local second server unavailable too: complete power-out or network failure, fire, and so on. And make sure that your off-site backups are tested. It might feel inefficient to push the backups off-site then draw them back down for testing (instead of testing the original local copies) but this protects you from network transmission errors or otherwise undetected disk errors on the off-site store. If your second box is already off-site this dynamic changes somewhat.


SQL User Permissions for Continuous Integration via DbUp and Octopus Currently, our deverloper group has started using DbUp and Octopus to do Continuous Integration on SQL Database Deployments. We have created a Domain user which will utilized to do the SQL Database deployment via Octopus server.

I wanted to know, what permissions should a Domain user should be granted for Continuous Integration process?

I don't want to give "SysAdmin" permission to this Domain user.

Do people have implemented above combination for there Continuous Integration of DB Deployment?

Assuming you are using SQL Server 2008 and up, A better way of doing is to create a role in the database and grant that role permissions.

You can add users to the role, so they will inherit the permissions of the role.

-- to grant CREATE, ALTER, DROP OBJECTS (tables, procs, functions, views) with ALTER permissions on the schema. You can obviously fine tune below ones as per your needs.

USE db_name;
 CREATE ROLE [new_role] AUTHORIZATION [dbo];
grant alter
      ,delete
      ,execute
      ,insert
      ,references
      ,select
      ,update
      ,view definition
on schema::dbo
to new_role;

grant create table
     ,create procedure
     ,create function
     ,create view
to new_role;

-- Add an existing user to the new role created

  EXEC sp_addrolemember 'new_role', 'DBUserName'
  GO


Import a sample database into SQL Server on AWS RDS free tier I asked this on stack overflow but someone there suggested I try here as I might get better answers. So here goes...

I'm signed up to Amazon Web Service (AWS) free tier, which allows you to run some very limited instances of EC2 and RDS for free.

I've created an RDS instance running SQL Server 2012 Express - this does not allow remote desktop access, so I also have an EC2 instance running Windows Server 2012 base, and I have installed SQL Server Management Studio and I'm able to connect.

I would like to import some kind of sample database - for example, Northwind, AdventureWorks, or anything that has at least a handful of tables and relationships. The reason is I want to just experiment and practice on a few things (for example, I want to try out the EXPLAIN feature to get to learn how the relational algebra works and how SQL plans out various test queries).

My problem is that Amazon's RDS does not allow me to restore a .BAK file, as local disk access is not allowed, and neither is FILESTREAM, so I can't just download a sample database backup and restore it.

Amazon documents its workaround here, which involves creating a local instance of SQL Server on the EC2 instance (my machine with SQL Server Management Studio installed, which I have RDP access to), and use the Generate and Publish Scripts Wizard to import data from the EC2 to the RDS. My problem here is that I don't have enough disk space to install a local database instance - as I'm on the free tier I'm limited to 30GB total disk space for the server, so the OS + SQL Server Management Studio leaves just a couple of gigs. So I'm totally stuck.

Does anyone have any genius ideas here? Maybe someone has a simple bulk insert script with some data + schema I can use (I can't find any) or some other solution? The database doesn't have to be big, just something to work with. Of course I could always pay to increase my disk space, and go through the whole tedious process outlined by Amazon, but I'd rather not! Also, someone has suggested (on stack overflow) that I create a DB on my home PC and create some kind of remote database connection, but I'm trying to avoid installing tons of heavy software on my home PC, it's the whole reason I'm experimenting with AWS. I'm aiming to have almost nothing installed at home, cloud all the way!

Use this version of NW instead. Its very light (2.6 MB in disk) and you can download the scripted version.
Should be just fine.


What is a "hashed transaction" in SQL Server Replication terminology? sys.dm_repl_tranhash contains information about the hashing of transactions, but I have difficulties understanding the terminology. What is a "hashed transaction"?

In reality, this DMV (Dynamic Management View) won't be very helpful for a DBA or Developer and is geared to SQL server Product Support and Product Group. You can ignore this DMV.

Hash tables work well when there are a small number of items that fit into a bucket and this is an example of a DMV that support might use to check certain internal structures without the need of obtaining a dump or using a debugger.

There are various other DMVs that fall into this category and you'll find they either aren't documented at all, or there is only extremely light and vague documentation. This falls into that category.


Can a weak entity type be involved in an "is-a" relationship? Considering the scenario depicted in the diagram below, as the title says, can a weak entity type take part in an "is-a" relationship?

When I've modeled databases, I would use a isType description where you have isA.  You appear to have two types of Specs; labelSpecs and itemSpecs.  Common data belongs in Specs entity, with type specific data in the appropriate subtype record.  Normally subtypes are exclusive, so a Spec would be either a labelSpec or an itemSpec.


customized join output Example consider two tables Persons and Employee

Persons table sample:

Name,Address
1. john,new York
2. Diana,new jersey

Employee table sample:

Employee ID,Team
1. 123,developer
2. 568,tester

Required join Table output should be like

Name,Address,Employee ID,Team
 1.john,new York,null,null
 2.Diana,new jersey,null,null
 3.null.null,123,developer
 4.null,null,568,tester

I want above sample as output is there any way to solve this issue.

This can work for you:

SELECT *
FROM Persons a
LEFT JOIN Employee b ON 1=2
UNION
SELECT *
FROM Persons a
RIGHT JOIN Employee b ON 1=2;