Adding SQL Agent output to text file for all agent jobs in one go As a best practice, i am trying to schedule an activity where all SQL agent jobs should log output to text file.

Manually i can do this for single job. But i am looking for a better way either by script or any good method i can make this change for multiple jobs about 80-90 jobs per server.

Moreover i have to do this in QA, UAT and prod, covering over 100 servers.

Please share you're experience or any helpful links how can this be achieved in best way as path of log files where output will be generated should be common across servers/jobs.

The msdb database contains a stored procedure that allows modifications to job steps in a wide variety of ways, including the ability to modify only the output properties.

The following code will output the commands required to modify all job steps that have no "output file" specified, such that job step output will be written into C:\temp\job_name_here.txt.  Output will be overwritten each time the job step runs.

USE msdb;
DECLARE @OutputPath nvarchar(260);
DECLARE @cmd nvarchar(max);
SET @OutputPath = N'C:\temp';
DECLARE @msg nvarchar(1000);
SET @msg = N'
/*
flags:
    0 - overwrite output file
    2 - append to output file
    4 - Write T-SQL output to step history in MSDB
    8 - Write log to table (overwrite existing)
    16 - Write log to table (append)
*/

';
PRINT @msg;
DECLARE cur CURSOR LOCAL FORWARD_ONLY LOCAL
FOR
SELECT N'EXEC msdb.dbo.sp_update_jobstep @job_id = N''{' + CONVERT(varchar(50), (sj.job_id)) + N'}''
    , @step_id = ' + CONVERT(nvarchar(30), sjs.step_id) + '
    , @output_file_name = N''' + @OutputPath + N'\' + sj.name + N'_Step_' + CONVERT(nvarchar(10), sjs.step_id) + N'.txt''
    , @flags = 0;

'
FROM dbo.sysjobs sj
    INNER JOIN dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
WHERE sjs.output_file_name IS NULL OR sjs.output_file_name = ''
ORDER BY sj.name
    , sjs.step_id;
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF LEN(@cmd)>4000
    BEGIN
        SET @msg = 'Output of the next command is truncated.';
        RAISERROR (@msg, 14, 1);
    END
    PRINT @cmd;
    --EXEC sys.sp_executesql @cmd; --uncomment this line to actually run the command
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

You could run this against multiple servers by creating a local server group in SSMS using the "Registered Servers" functionality, adding the desired servers to that group, then executing a query against the group.


SQL Sum with MAX condition I have two tables named Claim

EmployeeClaimId
      1144
      3481
      3452
      5760
      7296

and ClaimStatus

EmployeeClaimId   Amount   Version
       7            0.00      1
       7          300.00      3
       7          150.00      3
       7          400.00      1
       7          300.00      7
      14         1860.00      1
      14          541.00      7
      14          530.00      1
      14         2490.00      1

I want the sum of amount from second table where version is max for each claimid.

Can someone help?

What is the Claim table used for?

Well, you can try that:

select EmployeeClaimId, sum(Amount)
from ClaimStatus cs
join (
    select EmployeeClaimId, max(version) as version
    from ClaimStatus
    group by EmployeeClaimId
    ) c on cs.EmployeeClaimId = c.EmployeeClaimId
        and cs.version = c.version


How many users and objects can be in an Oracle Database? I wonder if anyone know what can be the biggest existing Oracle databases in the world and how many users (DBA_USERS), objects (DBA_OBJECTS) they can contain?

Is there a scenario when a database contain more than 100 000 users?

For example, as far as I can guess, there can be thousands of users in MySQL database on a webhosting server where each hosting user registered on the hosting website has its own MySQL user. Is there a real-life Oracle databases that are used in a way like this?

All Oracle Documentation can be found on-line
https://docs.oracle.com/en/database/oracle/oracle-database/index.html
It is highly recommended that you read the "Concepts Guide".  If you understand just a tiny bit, it will make you a much better developer/DBA.
For your specific answer, you need to look in the "Database Reference" manual section "Database Logical Limits".

Users and Roles  |  maximum   |   2,147,483,638
Tables | maximum per database | unlimited

App Design Notes
Web Users are normally stored in a table.
In Oracle :  Users and Schemas are almost synonymous.  This makes the requirement "create an Oracle user for each and every Web User" a very expensive task.... And a dangerous one at that.  (The last thing you want is L33TH4CKR to have his/her own account on your database)


Sql Server Agent fails to run batch from desktop I’m using SQL Server 2008 and running a batch file from SQL Server Agent. The batch file is located in my desktop. When I try to run the job I get an error: “access is denied.” The batch file runs successfully if I run it manually. Also, the job runs successfully if I put the batch file anywhere in the C drive or F drive and run it from Agent. Any idea why the access is denied if the batch file is in desktop? The agent service account has the sysadmin server role in SQL Server. Thanks.

Any idea why the access is denied if the batch file is in desktop?

Yes. Because it is a rotten idea. The service agent account has no rights on your personal folders and your personal folders include the desktop. And you should keep it that way.

Your personal fodlers are btw., locked also for the admin accounts etc.

If you need sql agent to do something, I strongly suggest you agree on a location for that to happen and move the batch there.


MySQL Query/Connect to eximstats? I'm trying to run a report for my clients in real-time. I created a new eximstats user (renamed the old one) with:

update mysql.user set password=password("XXXXXXXXX") where user="eximstats";

In mysql.db I'm showing eximstats user and DB.

My connection:

  $exim_connect = new mysqli($exim->host, $exim->user, $exim->password, $exim->db);

This is my result with and without a password:

  EXIM Connection failed: Access denied for user 'eximstats'@'localhost'
  (using password: YES)

Is this even possible? If yes, what I'm I missing?

cpsrvd 11.52.0.22
Database client version: libmysql - 5.1.73
PHP extension: mysqli

Few reasons could be:

one of them - 'eximstats' has other host than @'localhost' in user table
more than 1 user with same name but different host could also give
error

try to manual connect to the database with this user credentials and check errors if it will be.


How does like query work with indexed table I am working in Spring-java and mysql.

I have to query the table of size 100k records. Table has say 10 columns.
And In my sql select query I have to make like queries with %text% search on say 4 columns. Those 4 columns are varchar(200), having average text size of 30 character.

I have gone through few blogs and SO answers to understand about index and after reading I came to this question.

Will making those 4 columns individually FULLTEXT index, makes difference in execution time of like query?

Thank you

Let me help you to understand how a "standard" index works.

Most databases indexes are just B-Trees (not to confuse with a binary tree). Simply speaking, when you query an indexed column, a binary search will be performed. A binary search performs generally in O(log(n)) and thus you can find individual rows quite fast, even if there are many of them. The database uses B-Trees instead of loading and sorting the table as the indexes don't require as much memory and less disk reads are required.

Now imagine that you try to binary search for a value, but you don't know the exact beginning or ending of the value. A binary search is basically not possible and you have to traverse nearly the whole tree to find every possibility.

Of course, there're cleverer techniques than this. MySQL is not that dumb, it uses a Boyer-Moore algorithm for this problem, but that doesn't mean you don't suffer a performance impact.

A Fulltext search index will of course help. It uses entirely different datastructures (Tries, Suffix-Trees). Reading the manual of MySQL, I also get the impression that fulltext searching is quite easy with it.

However, on most systems fulltext searches require some maintenance/housekeeping by the admin to keep up good performance. Often times fulltext indexes keep "mapping/index" tables for the tokens of the indexed texts. These tend to fragment which can impact query response time, as the index grows unecessarily larger. So from time to time, they should be defragmented and optimized. You might want to look into that.


MySQL consistent nonlocking reads vs. INSERT ... SELECT So, from my reading of the MySQL 5.5 documentation and some experiments I did with colleagues, I understand that the following is the case:

With InnoDB tables, a simple SELECT statement (one that does not use FOR UPDATE or LOCK IN SHARE MODE) will not grab any row locks on the tables that the SELECT statement reads.  This is true at all transaction isolation levels.
However, if your transaction isolation level is REPEATABLE READ or higher, an INSERT ... SELECT or CREATE TABLE AS SELECT statement will place row locks on tables that it reads from.

Sources:

http://dev.mysql.com/doc/refman/5.5/en/innodb-consistent-read.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-set.html

If I understand this correctly (and correct me if I don't), then I'm puzzled by this difference.  Why does reading a table require lock rows in the one case but not the other, when the transaction isolation level is the same?  I'd like to understand the reason for this.

From the MySQL Documentation

The type of read varies for selects in clauses like INSERT INTO ... SELECT, UPDATE ... (SELECT), and CREATE TABLE ... SELECT that do not specify FOR UPDATE or LOCK IN SHARE MODE:
By default, InnoDB uses stronger locks and the SELECT part acts like READ COMMITTED, where each consistent read, even within the same transaction, sets and reads its own fresh snapshot.
To use a consistent read in such cases, enable the innodb_locks_unsafe_for_binlog option and set the isolation level of the transaction to READ UNCOMMITTED, READ COMMITTED, or REPEATABLE READ (that is, anything other than SERIALIZABLE). In this case, no locks are set on rows read from the selected table.

Evidently, you do not need stronger locks when just doing reads. Picture copying data into a table using INSERT ... SELECT or CREATE TABLE ... SELECT. The SELECT needs to be a frozen snapshot for loading the table. If it were a moving target, that would require a transaction within a transaction. If you wanted transaction within a transaction behavior, you would need to script that using SAVEPOINT. Otherwise, stronger locks allow for consistent SELECTs for an atomic INSERT.
Doing the simple SELECT allows your DB Session to trust other transactions not to play smoke-and-mirrors with your view of the data. If it has to violate that trust, it will politely lock every row it can to do its work, rather than deceive DB Sessions that are only doing SELECTs into thinking the data is stable when it is, in fact, changing.
No matter which isolation level you pick, none of them is so granular that a simple SELECT gets held up. That's why SELECT ... FOR UPDATE and SELECT ... FROM ... LOCK IN SHARE MODE were invented, so you can be that granular if politeless is not an option.
I wrote about this before : How can I make a select statement get blocked?


What frequency of hash/sort spills into tempdb is concerning? Our enterprise application uses SQL Server for data storage and is primarily an OLTP system. However, an important component of our application generates a significant OLAP workload.
Our write latency to tempdb is about 100ms. This trend holds over time, and ALLOW_SNAPSHOT_ISOLATION is turned off. We are troubleshooting this concerning problem and the only interesting thing we've found so far is that there are a significant number of hash and sort spills to tempdb. We surmise this is coming from our OLAP workload.
Question
What frequency of spills is concerning? Any? How many spills/sec? Our preliminary data indicates that we have about 2 hash spills per second and 25 sort spills per minute.
Is it possible that this frequency of spills could be a primary culprit in our high tempdb write latency?
Other information
We're using multiple files for tempdb as recommended per number of cores. The tempdb files are on a RAID 1+0 SAN (with high performance SSDs) but that's the same device as the main DB data and log files. The tempdb files are sized large enough that they grow very infrequently. We aren't using trace flags 1117 or 1118. Another variable is that this set up is shared for a number of different databases that all experience medium to high load.
Our 100 ms write latency is much greater than the acceptable ranges for tempdb write latency that we have found on MSDN, SQL Skills, and other sites. However, write latency for our other databases is good (below 10ms). Based on other stats, it does appear we're using tempdb heavily, particularly for internal objects. So we're digging in to try to find out why our application is using internal objects so heavily.
We do have real performance issues on our platform that manifest in varying ways. We've been monitoring perf counters, looking at DM views, and analyzing our app behavior to try to dig into the resource usage characteristics of our system. We're focused on spills right now as we've read that spills have drastic negative impact because they are performed on disk instead of in memory. And we appear to have a very high number of spills, but I wanted to get some input on what people consider "high."

Is it possible that this frequency of spills could be a primary culprit in our high tempdb write latency?

Yes it is possible, though typically it is the average size of the spills, and how deep they go (i.e. recursive hash spills, multi-pass sorts) that matters more than the frequency per se.

SQL Server provides a wide range of metrics and DMV information to help you troubleshoot the various contributing factors to tempdb pressure, many of which are discussed in the Microsoft Technical Article, "Working with tempdb in SQL Server 2005" (applies to all versions 2005 onward).

You should be able to use the guidance and diagnostic queries contained in that document to start identifying the primary causes of any tempdb pressure. Do not disregard e.g. version store activity simply because ALLOW_SNAPSHOT_ISOLATION is not enabled. Many features use the version store (e.g. triggers, MARS, RCSI) aside from snapshot isolation.

If sort and hash spills do turn out to be significant at a high level, you will probably need to set up some specific monitoring for this. Depending a little on your SQL Server version, this is not always a straightforward as one might hope. To connect sort and hash spills with the particular query that caused them requires Event Notifications or Extended Events. The SolidQ article, "Identifying and Solving Sort Warnings" contains details and some good general advice about resolving common causes.

You should also work with your storage team to determine how much of the high latency is attributable to your workload, how much comes from other shared uses, and what options there are for reconfiguration. Your analysis of SQL Server's metrics will help inform this discussion, as will any metrics the SAN people are able to provide.


When duplicate date values exist in a column, change the value of the oldest I have a query that returns records with duplicate Month and Year in a date field.  I need to identify the records with duplicate Month and Year, then identify the oldest based upon the full date and change the value of a column that displays the month based upon the date column to that month - 1.

Background:  The query grabs the last 12 bills for a customer.   In a few instances, the Date (actually the read date - which is typically the last day of the month) reflects when a meter was read.  The only time there could be read dates with the same month year in that 12 month period is when a read date (the last day of the month) falls on a non business day, then it pushes to the next month.  I'm looking for the usage in the month the meter was read for the last 12 months.

Example, in the query results below the highlighted month should read Feb as it is the oldest of the duplicate Mar 2017 results.

I think this solves your problem.

--demo setup
drop table if exists billing;
go
CREATE TABLE Billing
    ([BillDt] datetime, [BillMonth] varchar(3))
;

INSERT INTO Billing
    ([BillDt], [BillMonth])
VALUES
    ('2018-01-30', 'Jan'),
    ('2017-12-28', 'Dec'),
    ('2017-11-30', 'Nov'),
    ('2017-10-31', 'Oct'),
    ('2017-09-28', 'Sep'),
    ('2017-08-31', 'Aug'),
    ('2017-07-31', 'Jul'),
    ('2017-06-29', 'Jun'),
    ('2017-05-31', 'May'),
    ('2017-04-27', 'Apr'),
    ('2017-03-30', 'Mar'),
    ('2017-03-01', 'Mar')
;

--the solution
--With SQL Server looking at each row on the Billing table...
--if you can find a row where the month is the same as the 'current' row, but
--the day is greater than the 'current' row, update the BillMonth of the 'current'
--row to be the previous month of the 'current' row.
UPDATE b
SET BillMonth = Substring(datename(month, DATEADD(month, - 1, billdt)), 1, 3)
FROM Billing b
WHERE EXISTS (
        SELECT *
        FROM Billing
        WHERE datepart(month, BillDt) = datepart(month, b.BillDt)
            AND datepart(day, BillDt) > datepart(day, b.BillDt)
        )

SELECT *
FROM billing
ORDER BY billdt desc

 BillDt                  | BillMonth |
|-------------------------|-----------|
| 2018-01-30 00:00:00.000 | Jan       |
| 2017-12-28 00:00:00.000 | Dec       |
| 2017-11-30 00:00:00.000 | Nov       |
| 2017-10-31 00:00:00.000 | Oct       |
| 2017-09-28 00:00:00.000 | Sep       |
| 2017-08-31 00:00:00.000 | Aug       |
| 2017-07-31 00:00:00.000 | Jul       |
| 2017-06-29 00:00:00.000 | Jun       |
| 2017-05-31 00:00:00.000 | May       |
| 2017-04-27 00:00:00.000 | Apr       |
| 2017-03-30 00:00:00.000 | Mar       |
| 2017-03-01 00:00:00.000 | Feb       |


Switching alias in subquery equality leads to different result Trying to learn sql queries recently and  as the title says I stumbled upon some "behavior" i didn't understand.

This query returns the managers from the database.

SELECT *
FROM employees e
WHERE EXISTS (SELECT * FROM employees f WHERE e.employee_id = f.manager_id)

However if I switch the aliases in the equality it returns all the employees.

SELECT *
FROM employees e
WHERE EXISTS (SELECT * FROM employees f WHERE f.employee_id = e.manager_id)

Isn't employees e and employees f the same table but with different names? If so, why is then the alias order important?

Think about the meaning of your queries as well, not just the structure.
It is not an order, but an employee-manager (boss) hierarchy.

First query means: list all employees who are managers of other employees.

There are 18 different manager_id values in the table, so you will get the data of 18 employees (as seen on your picture):

SQL> select count(distinct manager_id) from employees;

COUNT(DISTINCTMANAGER_ID)
-------------------------
                       18

Second query means: list all employees that have managers. There are 107 employees and only one of them has no manager, so you get the data of 106 employees (as seen on your picture):

SQL> select count(*), count(manager_id) from employees;

  COUNT(*) COUNT(MANAGER_ID)
---------- -----------------
       107               106


Galera Cluster with MariaDB 10 and asynchronous slave As it's easier to understand what I'm trying to accomplish with a picture, here's a picture:

I'm really not an expert in database administration, so please be patient.

I am working on a setup where I run three servers with MariaDB 10.0.12 and Galera cluster. I also have a separate installation of MariaDB that I want to run a master/slave setup where the databases gets replicated from each Galera node through keepalived and haproxy for high-availability.

After setting everything up according to a jumble of guides online, I have set up the Galera cluster and servers just fine. However, the replication to my async server doesn't seem to be working very well. Mind you I am also trying to use GTID for all of this using Using_Gtid: Slave_pos. The slave server uses the virtual IP of keepalived to connect to remotely.

Right now I'm getting the following error under the show slave status part of the slave:

  Got fatal error 1236 from master when reading data from binary log: 'Could not find GTID state requested by slave in any binlog files. Probably the slave state is too old and required binlog files have been purged.'

This seems to be related to some kind of problem with the slave not being able to sync with the master servers due to the master logs being too high in value (e.g.: mariadb-bin.000080 on the master servers). Could that be the case? And if so, how do I manage to update the slave to understand that it needs to get everything up to the latest?

I really hope someone could help me out with this, or at least show me some way to better solve my current problem.

I was expecting the slave data to be synchronized from the Galera cluster at first connection and the GTID be assigned automatically, but is this is not the case? If so, how do I get the slave up to date?

Galera cluster auto-provisions its nodes, but that is not the case with traditional replication. You must do it manually.

Look at MariaDB documentation for setting up a slave from a backup. The process is essentially the same as doing it in MySQL, but the syntax is slightly incompatible. It usually just involves mysqldump with the --master-data and --gtid options or using  Percona Xtrabackup (these methods are handled automatically among Galera nodes).

I also include a link to the MySQL documentation on setting up a new slave because even MariaDB decided to become incompatible with it, the common explanations are way better documented than on the MariaDB wiki (e.g. using mysqldump to backup a server).


I can open a connection with SQL Developer but when i do the same with .NET returns me ORA-12514 In SQL Developer

In my .NET proyect, Web.config:

<add name="DBConection" providerName="Oracle.ManagedDataAcess.Client" connectionString="Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.27.32.27)(PORT=1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME=BDTEST1)));Password=pass;User ID=user;" />

When trying to connect with the application it gives me:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

I have modify all TNSNames.ora files I could find in my pc, but the database is in another server that i cannot acces. Is the problem in my PC or is something wrong in the server that impede this connection?

EDIT:

This is the TNSNames.ora, in fact, all the TNSNames.ora I could find in mi PC, because I don't know what is the file the application is using, or if it is even in my pc.

    BDTEST1 =
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.27.32.27)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BDTEST1)
    )
  )

Your database instance "BDTEST1" will, by default, register service "BDTEST1" in the Oracle Listener. Oracle Listener will expose that SERVICE_NAME.

Replace SERVICE_NAME=BDTEST with SERVICE_NAME=BDTEST1 for your .NET config and you can connect.

Before you enter the app layer, make sure your connect-string is correct.
use sqlplus or SQL Developer (custom connect):

sqlplus user/pass@\"(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.27.32.27)(PORT=1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME=BDTEST1)))\"

If you connect using the SID the connect-string will be:

sqlplus user/pass@\"(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.27.32.27)(PORT=1521))(CONNECT_DATA = (SERVER = DEDICATED)(SID=BDTEST1)))\"

I think you have to escape the " on Windows...not sure.

Side note: Behind a service there can be multiple oracle database hosts and DR hosts. If you connect using SID, you will always connect to the named instance (SID). If this one is down, no failover can happen. So you better use SERVICE_NAME. Ask you admins to provide it to you.

Best of luck!


Postgres settings reported differently in pgsql We are running PostgreSQL 9.2 on CentOS 6 and I am seeing the settings are reported differently when I run the show effective_cache_size; command vs select * from pg_settings where name = 'effective_cache_size'; query. As far as I can understand these commands should be identical. For example

show effective_cache_size;

effective_cache_size
----------------------
 2816MB
(1 row)

vs

select name,setting from pg_settings where name = 'effective_cache_size';

name    | effective_cache_size

setting | 360448

I am trying to figure out the value used by PostgreSQL. I get the same results if I run this as a superuser. Which one do I trust?

I see the same difference on my test database:

show shared_buffers;
 shared_buffers
────────────────
 768MB

select name, setting from pg_settings where name = 'shared_buffers';
      name      │ setting
────────────────┼─────────
 shared_buffers │ 98304

Then watching the numbers for a short while, I set up the following query:

SELECT 98304 * 8192 / (1024 * 1024);
 ?column?
──────────
      768

So, to me it looks like that pg_settings reports these sizes in 8 kb pages (8192 refers to this fact).

Note that there is a third way to check settings, namely the current_setting() function:

SELECT current_setting('shared_buffer');
 current_setting
-----------------
 768MB


Working SSIS package fails as a job, probably due to data flow connection I'm working in SQL Server 2012. I've built out an SSIS package that is made up of a few scripts and 2 data flow tasks. The data flow tasks move data from the local SQL server out to an Amazon SQL server.

The whole package runs fine in Visual Studio. I imported it into the Stored Packages in Integration Services, and I can successfully run it from there.

However, when I set up a job and run that SSIS package as the one and only step, it fails. The only error message I get is that Step 1 failed. I can't trouble shoot why it fails because it otherwise runs successfully in Visual Studio and even in the Integration Services.

I've done a similar project before successfully, and what's different here and where I suspect the issue is in the Data Flow tasks. I suspect that the connection to the Amazon SQL Server is failing, that the password isn't getting properly passed. Again, I've got that information correct in the package, but somehow it's not running with that.

Can anyone point me to the way to properly pass the authentication info when the SSIS package is run as a job?

All right, I think I've got it. I'm relatively new to this, so forgive any bad terminology.

I got on the right path with this: https://stackoverflow.com/questions/31755979/storing-password-in-ssis-package

Basically, the external connection to the Amazon DB was the issue. I had the correct credentials stored in the SSIS package to make the connection, but by default sensitive information (such as passwords) are encrypted in such a way that is specific to the user who created the package. So the package ran fine for me, but it wouldn't work for anyone else, including the job.

To fix this, you need to change the default Package protection to give the package a sort of master password, so that the package will run correctly for anyone, as long as they have the password for the package. In the project properties, you can find ProtectionLevel and switch the value to another option (I chose "EncryptSensitiveWithPassword.") You'll enter a password, then be prompted to also change the child package files. Get into the package properties and change protection level there too. Enter the same password on the line above. Save it.

Now, when you import package into Integration Services you'll be prompted to enter the password. Then you'll be prompted again when you set up the job. But with the password entered it runs successfully.

Hope this helps somebody.


How can I check if BULK INSERT is running on a table? A BULK INSERT command may take a long time to run.
I have a program that receives multiple files and pushes them to the DB using the BULK INSERT command to a specific table.
After a file is uploaded, the program deletes it and moves to the next.
The BULK INSERT command may continue to run on the DB after the program stopped. so a file may be loaded to the DB, or still in process when my program starts.
So, my questions are:

Can I tell if a BULK INSERT command is still running for a specific table?
Can I tell if a file has been already loaded to the DB?

I can get the running query by running:
select t.text
from sys.dm_exec_requests r
cross_apply sys.dm_exec_sql_text(r.sql_handle) t
where command= 'BULK INSERT'

but how can I know on which table it runs?

There is no way to directly monitor the queries against a single table in a database. Instead, what you have is the ability to see queries running against that database. Then, you can filter the query text to determine which table or tables that query is running against. So, in order to do exactly what you want, you need to pick one of the methods for monitoring queries. Extended Events are historical more than they're current, although, you can look for statement starts without statement ends to find queries that are active. Query Store is historic only. Querying the DMVs is usually the best way to see what's immediately happening.
So, you want to query sys.dm_exec_requests to see what's currently running. Then you combine that with sys.dm_exec_query_text to see the specific query being run. Finally, can filter for both the BULK INSERT statement and your table name against the query text. If you don't want to write your own queries to combine these DMVs, as was pointed out in the comments, sp_whoisactive, is an open-source query that you can install on your servers. Then, using it, you do the same filtering against the query text.
Regardless of how you do it, you just have to filter on the query text. There's no other way to see what's running against a single table.


How many backup devices is recommended to improve backup performance? In order to improve performance of our SQL Server backups we backup to multiple backup files.

On most blog posts about this topic they say to use multiple backup
files but, is there a point where you can have too many files that
instead of improving performance you get a worse performance due to
the big amount of threads waiting?
Is there any general recommendation based on, for example, number of
cores in the server? To be more specific, one of our server shows
BACKUPIO and BACKUPBUFFER as top 2 offenders wait stats.

We're backing up a big DB (several TBs) using 10 backup files, but I noticed server has only 4 cores and 32 GBs of RAM. I have changed the number of backup files to use 4 backup files instead. I'll see how it goes next week in the next backup cycle, but meanwhile I was trying to find any recommendations on how many backup files to use depending on the server specifications.

In order to improve performance of our SQL Server backups we backup to multiple backup files

Not generally, no. That is a special purpose technique that can help.

  big amount of threads waiting

Why would that cause a loss of throughput? Threads are normally an ample resource. Problems arise in certain configurations where you burn through hundreds and into the thousands of threads.

  based on [the] number of cores in the server

That would make sense if the backup operation was CPU bound. That could be the case. As long as it is IO bound it cannot help to target the number of cores with any setting.

  one of our server shows BACKUPIO and BACKUPBUFFER as top 2 offenders wait stats

That points to an IO bound backup process. According to my experience that is far more common than CPU boundedness.

  a big DB (several TBs)

The size of the backup does not play a role in throughput considerations. Throughput is performance per unit of time.

There mere presence of waiting threads does not normally affect other threads and operations on the server. Waiting threads do nothing.

  so I have changed the number of backup files to use 4 backup files instead

That was unfounded because the backup is IO bound. Targeting the number of cores is not a good target.

If you added 128 CPU cores, your backup would not become one second faster.

You might have increased performance but only accidentally.

How many backup files to use is influenced by many things. An easy way to find a good number is to simply test different values, including 1 (the most likely value for common systems).


How unsafe is it to change the data source of repl_distributor on a publisher Here's my situation.

Reporting Server (SQL 2008) is distributor/subscriber cluster for a SQL 2000 Transactional Publication
Reporting Server was migrated to new SAN
Reporting Server fatally crashed upon completion of migration (model db was corrupted)
I installed another instance of SQL on a node of the cluster, attached dbs, and changed report connections to use this new instance
Stupidly, I attached the [distribution] database to this new instance
select * from sysservers (rightly) returns that the distributor is still the first instance on the cluster.

How terribly would it break things if I ran the following on the publisher (or is this even possible)?

sp_configure ‘allow updates’, 1
 GO
 RECONFIGURE WITH OVERRIDE
 GO

update sysservers set datasource = 'server\instance' where srvname = 'repl_distributor';
GO

sp_configure ‘allow updates’, 0
 GO
 RECONFIGURE WITH OVERRIDE
 GO

Is this even the best way, or is there an alternative way to change the distributor?

Know this is an old question but we've just been working with this, Last month we had to move our distribution server to a brand new machine, (Setup is two machines in AG stand alone distributor and a reporting server)

The only real way we found to move distributor server is to completely tear down and rebuild replication, this can be done with right clicking replication and generate scripts

bare in mind you'll need to do this on the publishers and the new distributor.

We've just had an issue where our AG group didn't take the distributor change, and since its a read only node we're doing some experiments with different ways to do that,

our tests of updating the [master].[sys].[sysobjvalues] table (which is where the 'datasource' value is actually stored (the sysservers is just a view) have resulted promising results if you want the server to never work ever again.  Tearing things down and rebuilding it is painful, but its by far the safest thing to do.

Ste


Best practice for MySQL CASE with multiple WHEN conditions vs a single OR condition Is there a preferred (or more performant) way of writing a CASE with multiple WHEN conditions that generate the same value?
For example:
SELECT
  CASE
    WHEN 1 > 0 AND 2 > 0 THEN 1
    WHEN 0 < 1 AND 0 < 2 THEN 1
    ELSE 0
  END AS multiple_WHEN,
  CASE
    WHEN (1 > 0 AND 2 > 0) OR (0 < 1 AND 0 < 2) THEN 1
    ELSE 0
  END AS single_OR

Would the remainder of the WHEN conditions need to be evaluated before reaching the end of the CASE, and would an OR condition act any differently?

The performance difference between the two suggestions is insignificant.
As a rule of thumb, fetching a row is far more costly than any expressions that need to be evaluated for that row.
Do whichever is easier for you.
The expressions in those examples will be evaluated before it starts to perform the query.  Perhaps your real query is more involved than that?  In particular, if 2 > 0 is, instead, some complex subquery, then I need to reconsider my answer.
These may apply to MySQL:

"short circuit" -- But for simple expressions, you would be hard put to measure the performance difference.
When possible MATCH .. AGAINST (FULLTEXT) is performed first.  (But this applies to WHERE, not CASE.)
User functions can be defined as DETERMINISTIC or non.  This should impact the evaluation.  (But I don't have proof of such.)
@variables are probably soon to be deprecated due to evaluation issues.


restoring to new db putting old db into restore The image below says it all.  I need to get some data out of a back-up that's two weeks old, so I restored to a new db called ETLFix.  However, when I started the restore my production DB, ETL went into restore as well.

I suspect this because I restored ETLFix from a two week old .bak and an notification went off saying that the back-up chain is broken for ETL.  Another way to say it is, ELT thinks it's in restore because it learned an old .bak was read.

How to I get my ETL production db out of restore, and keep this from happening in the future?

Try this statement:

RESTORE DATABASE ETL WITH RECOVERY

Since this is your prod environment, be very careful in doing your due diligence to be sure you're doing no harm that cannot easily be fixed.

Also, check the Options tab to see if "Take a tail-log backup before restore" is checked. If it's checked, then un-check it. Starting in 2016 (I think), if you're restoring over an existing database, then SSMS defaults to taking a tail log backup. In my quick test just now (restoring to a new name from a backup of an existing database), SSMS still defaults to take a tail-log backup prior to restore. Get in the habit of always checking the Options tab.

Hope that helps.


Log shipping in sql server I have configured log shipping in sql server. During logshipping the size of log file increased. And I need to shrink log file. for that I have to change database to simple. when I will change the recovery mode from Full to simple chance of break of log shipping will be maximum as I have read in Microsoft guide.

Is it possible to shrink log file in database when it is being used for log shipping?

Assuming you have taken log backups since the log file grew, you should be able to shrink the file without changing recovery models.

But you absolutely, 100% have to understand that shrinking the log file is an absolutely useless activity if the log file is going to grow again. Let it stay as big as it's going to get during normal operations, because shrinking it to free space temporarily doesn't gain you anything. What will you use that space for today when you know that tomorrow the log file will grow again? This is like watering the lawn right before a thunderstorm. Perhaps you are not backing up the log often enough? Doing it more often

Please read this in full before proceeding. And I really mean in full. Like, the whole thing.

Now, if you have read that entire page, and are absolutely, 100% sure that you need to shrink the log file, and that it is a worthwhile activity because you know the log file won't grow again, then:

USE your_database;
GO
DBCC SHRINKFILE(your_database_log, 100); -- hypothetical, target size is in MB
GO

If you don't know that the log file won't grow again, put the mouse down and leave the log file alone.


Is there a way to upgrade to SQL Server 2012 from SQL Server 2008 R2 without installing any of the three service packs? I am trying to upgrade to SQL Server 2012 from SQL Server 2008 R2 without installing any of the 2008 R2 service packs (1, 2, and 3).

The reason is that, at my work, we ship software with SQL Server Express RTM as a prerequisite and we include the RTM versions with our software.

Now the problem comes when we included SQL Server 2012 in our new release. For customers which are in 2008 R2 RTM, we have to ship the service packs with our software so that they can upgrade to the service pack and then upgrade to the SQL Server 2012. However, the service packs have significantly increased the size of our software package and downloading it is taking so much time for customers.

Is there a way to upgrade to SQL Server 2012 from SQL Server 2008 R2 without installing any of the three service packs?

According to this Microsoft link, you have to be at least at Sql 2008 R2 (SP1) to upgrade to Sql 2012.


Oracle RAC 11g R2 DB silent install: parameter needed for system class I'm installing Oracle RAC DB 11gR2 using response file and silent mode. The installation is failing with the error message:

  [INS-35421] This options installs a single instance database only
  You have chosen to perform a Desktop class install on a cluster. This options will not install oracle RAC

Which parameter is needed for a server-class install? I tried changing the value for oracle.install.db.config.starterdb.control from DB_CONTROL to GRID_CONTROL, but I still get the same warning.

Try using these 3 options :

oracle.install.option=INSTALL_DB_SWONLY \
oracle.install.db.InstallEdition=EE \
oracle.install.db.isCustomInstall=true


Is EAV an appropriate design for a collection of coexisting metadata-rich "fact estimates" on objects? I'm attempting to architect a system that will collect estimates on various quantities (10+) on each of many (300K+) objects, and make decisions from the historical record of these estimates. For example, we have a number of human and automated processes that could be attempting to ascertain an object's, say, PowerLevel, and as they report results that may (or may not) supercede each other, we'd like to track the history of these reports, along with metadata (dates, tuning parameters, code versions, etc.) from the processes. Specifically, we might want to do queries like "grouping by entity id, for each distinct attribute, find the most recent estimate" or "find all entities whose attributes had any updates that were run by codebase 573ae4".

As someone who's never actually used an entity-attribute value schema in production, this seems like a perfect use case for something like EAV, with additional metadata columns for the sources. Specifically, I'd envision a table like this:

entity_id | attribute_id | string_value | numeric_value | datetime_value | discovery_time | discovery_source | discovery_tuning_parameters | discovery_code_hash

But I've heard a lot of criticism of these types of schema, such as this answer. I'm hard pressed to find anyone using EAV on non-legacy systems, and that makes me very hesitant that I may be reinventing a very obsolete wheel...

On the other hand, the only other alternative I can think of is full NoSQL (bleh) or some unholy hybrid like:

id | power | power_current_discovery_date | power_current_discovery_source | power_history (an array of hstores?) | foobar | foobar_current_discovery_date | ...

because we'd need metadata on each attribute.

Any thoughts? Is this one of the few times that EAV is a good fit? Thanks for your help!

EAV is not evil; like any other tool, it can be implemented poorly and abused. You can find articles trash talking cursors, dynamic SQL, triggers, even SQL Server itself. That doesn't make them bad things.

EAV can be an appropriate solution. Whether it's the right answer in your specific case is probably more opinion-based than anything; I'm answering more to suggest that you not close your mind to a solution because someone said it was bad. There are always opposing viewpoints to consider:

What's so bad about EAV, anyway?


How much the decision to use ternary or binary relationship influences the accuracy of the model with regards to a given business model? The question title is very long, so here's a little bit of context to (try to) make it clear.

I asked a question regarding ternary relationship. The question was Ternary relationship: what's the difference between having single table and having multiple tables?

It was very well answered by @Michael Green but it raised me another question, which follows.

Consider the following ERD:

Consider the following business rules:

Companies produce products
Companies belong to a sector (examples of sector: machinery, food, software)
Products belong to sectors

Questions

Does the ERD above accurately model the business rules?
Is there a way to make the ERD very precise regarding the business rules?
Does the ternary table accurately represent the ERD above? (for that question, please refer to the parent question). If so, then the ERD above is the wrong model with regards to the stated business rules, right?

Answers

The ERD shown does not match the rules stated.
I believe your second ERD shows these rules.
A ternary table would not match because of the many-to-many between sector and product.

Exposition

The rules you give are all binary rules.  They relate one entity type to another.  If you have a rule which mentions three entity type then a ternary table would be appropriate, but you have not.  For example the intersection entity type "TargetMarket" would be ternary - Red Bull (company) targets energy drink (product) to software (sector).

I'm inferring from the many-to-many between company and product that the products are generic.  For example "chocolate" and "spreadsheet".  They cannot be "Toblerone" and "MS Excel" as they are trademarked and can be produced by only one company (ignoring licencing agreements).  If all companies stoped producing chocolate (God forbid!) I imagine you would still like to record that "chocolate" was in the sector "food".  With a ternary table this would not be possible.  If all companies ceased producing chocolate (i.e. deleted corresponding rows from the ternary table) the chocolate <-> food association would disappear.  Similarly a newly-formed company could not be recorded in this system until it was producing products.  Should it ever cease producing products (e.g. become a shell company, go into administration) it would have to be removed from the ternary table and hence from the system entirely.

As I mentioned in the parent question, the full answer depends on the meaning of the relationships between the entity types, as embedded in their names, and both binary and ternary tables may be required.  Say a company produces 4 products, each of which is categorised in 3 sectors.  That would produce 12 possible sector-product-company combinations.  If your system requires to capture that only, say, 9 of these possibilities are valid or exist in fact, or some other well-named constraint then the ternary table would be the right way to do this.  But this is a rule which is not mentioned in your set above.

I would suggest you examine the constraints between a company's sector and those of the products it produces.  There may be redundancies there which should be removed for the model.


How to name tables which are responsible for storing dictionaries and configuration? Below I attached diagram which corresponds to part of my database :

My questions:

In your opininon Do I have to divide ServiceInsance and ConfServiceInstance ? I did it like that because I would like to have separated IDs and Configuration columns.
What do you think about name of these tables ? I mean Is it worth to have Dict and Conf in table names ?
Is it a problem that PK does not corespond to table name by convention: TableName + ID in Conf and Dict tables ?
Is it a better idea to create schemas conf and dict maybe ?

I would have 3 tables "Service", "ServiceInstance", and "Node".  1-1 relationships are generally best collapsed into a single table, and there's no reason to decorate your lookup tables with a a prefix.


Phones and SIMS table I have to design a table of Mobile Phones and SIMS. Some phones have more than 1 SIM slot (2). Some phones have no SIMS (or only 1), and any 1 SIM can only be in 0 or 1 phone(s).

A table that has PHONE_ID, SIM_ID is the starting point. Both columns are nullable. A primary key of (PHONE_ID, SIM_ID) allows for any phone to have more than 1, but it also unfortunately allows for any 1 SIM to be in more than 1 phone! How do I ensure that any 1 SIM can only be in 1 phone, while still allowing any number of phones to have no SIMS?

A table

You have two types of entity so almost certainly need two tables to model them. As the relationship is one-or-zero-to-many from the SIM side it should have a nullable foreign key to the phone table. Something like

Sim
============         Phone
ID      (PK)         =======
inPhone (FK)  ---->  ID (PK)

That way a SIM is either in a phone or floating. If you want to enforce the maximum number of SIMs in each phone at the schema level you'll need custom check constraints or triggers.


Is it possible to "bind" sp_executesql to the calling procedure, as recorded in the Query Store? Statements executed with sp_executesql appear to be generally "unbound" from the procedure in which they appear. By "bind", I simply mean "associated to the calling object".
The goal is to make it simpler to associate statements in the Query Store:

select object_name(q.object_id) as [Statement Context]
from sys.query_store_query q
where 1=1
    and object_name(q.object_id) like 'This will be the procedure name for ''normal'' statements'

While it's possible to embed comments in the statements that will show up in the query_sql_text, this feels a bit extra-hackish.
Also, it seems that sp_executesql would need some form of context binding as the dynamic SQL can access non-global temp tables in the surrounding scope: without a binding, how can SQL Server ensure the validity & stability of the temp table schema in the created plans?

I think that embedding comments is currently the best way to distinguish different pieces of dynamic SQL when viewed from Query Store / Extended events / Trace - unfortunately.
I've had plenty of runtime errors with dynamic sql & temp tables so I don't think it does ENSURE temp table validity & stability.
You can use @@nestlevel to see how many SPs / functions are currently in the call stack, but I think you want more information than that. You can use error_procedure() - but only in a catch block, sounds like what you want is Microsoft to add a function current_procedure() or calling_procedure() that will tell you the name of either the current routine or the routine that called this one - maybe add a request here ? :- https://feedback.azure.com/forums/908035-sql-server


Replace special characters in a column with space I'm trying to write a query which replaces the special characters with space. Below code helps to identify the rows. (alpha-numeric characters, comma and space is valid):

SELECT columnA
FROM tableA
WHERE columnA like '%[^a-Z0-9, ]%'

How can I integrate the replace function into the select statement so that all characters other than alphanumeric, comma and space in the result set are  replaced by ' ' (space). This one won't work:

SELECT replace(columnA,'%[^a-Z0-9, ]%',' ')
FROM tableA
WHERE columnA like '%[^a-Z0-9, ]%'

If you are guaranteed to only ever use the 26 letters of the US English alphabet (both upper-case and lower-case versions) then sure, you can get away with using LIKE and/or PATINDEX with the simple range notation of [a-z] (you wouldn't need to use an upper-case "Z" when using a case-insensitive Collation).

But, if you might get characters not found in the en-US alphabet yet available in various Code Pages / Collations for VARCHAR data (e.g. Þ = Latin capital "Thorn" = SELECT CHAR(0xDE)), then you might need to include those in the character class: [a-z0-9, Þ]. Of course, what those extra characters would be is on a per-Code Page basis.

Also, please be aware that both Collation type (SQL Server vs Windows) and sensitivity settings (case, accent, etc sensitive vs insensitive) will affect which characters are included in a particular range. For example, the SQL Server Collations sort upper-case and lower-case letters in the opposite order as the Windows Collations. Meaning, assuming a case-sensitive Collation for both types of Collations, one will do AaBb... and the other will do aAbB.... The effect will be that a will be within the range of A-Z for one of them, but not the other. And the range of a-Z won't match any characters in a binary Collation (one ending in either _BIN or _BIN2, but don't use _BIN) given that the value of A is 65 and a is 97, hence it is an invalid range of 97 through 65 ;-). There are far too many variations to give examples for here so I will try to post a detailed explanation on my blog sometime soon (and then will update this with the link to it). However, if you are going to be strict about only accepting US English characters (even if you might get valid letters from other languages) then your best option will probably be to use the following pattern and Collation:

LIKE '%[^A-Za-z0-9, ]%' COLLATE Latin1_General_100_BIN2

Now, if you are supporting NVARCHAR data and can get "word" characters from various languages, then T-SQL will not be of much help as it has no real way to differentiate these things. In this case, you should use a Regular Expression (RegEx) -- specifically the Replace method / function -- and those are only available through SQLCLR. The following shows an example of replacing several "special" characters, yet leaving all that are valid letters in at least one language:

DECLARE @Test NVARCHAR(500);
SET @Test = N'this$is%a<>TEST,;to}⌡↕strip╞╟╚══¶out_ç_ƒ▀ special-ĳ-೫-chars-舛-დ-א-B';
SELECT SQL#.RegEx_Replace4k(@Test, N'[\W\p{Pc}-[,]]', N' ', -1, 1, NULL);

Returns:

this is a  TEST, to   strip      out ç ƒ  special ĳ ೫ chars 舛 დ א B

The RegEx expression means:

\W = a RegEx "escape" meaning "any non-word character"
\p{Pc} = a Unicode "category" of "Punctuation, Connector" (this is needed for the match only because this "category" is specifically excluded by the \W escape)
-[,] = class subtraction (this is needed to exclude commas from matching as "special" since they are included in the \W escape)

You can do an update of a table simply by issuing:

UPDATE tbl
SET    tbl.field = SQL#.RegEx_Replace4k(tbl.field, N'[\W\p{Pc}-[,]]', N' ', -1, 1, NULL)
FROM   tbl
WHERE  SQL#.RegEx_IsMatch4k(tbl.field, N'[\W\p{Pc}-[,]]', 1, NULL) = 1;

Please note that for these examples, I used two functions available in the Free version SQL# library of SQLCLR functions, which I created (but again, these are free). Also note that I used the "4k" versions which are faster due to using NVARCHAR(4000) instead of NVARCHAR(MAX) parameter types. If your data is using NVARCHAR(MAX), then just remove the "4k" from the function names.

Please also see:

RegEx Character classes


Just wrap it in a subselect.

SELECT *
FROM (
  SELECT id, date, amount, SUM(amount) OVER (ORDER BY date, id) AS balance
  FROM entries
  GROUP BY id, date, amount
)
WHERE date >= '2017-12-03'
ORDER BY date, id;

I'm also confused about a few things if you have three rows you don't have to group by it, see if this gives you the same result, it should be a lot faster.

SELECT *
FROM (
  SELECT id, date, amount, SUM(amount) OVER (ORDER BY date, id) AS balance
  FROM entries
)
WHERE date >= '2017-12-03'
ORDER BY date, id;


GRANT SELECT ON SEQUENCE succeeds, but to no effect? I am trying to grant select access to sequences from one user/role to another. There are no errors when I run the command but once run, the second role is unable to view the sequences. I have run the exact same command on several other services/database instances which succeeded, this is only misbehaving one.

I have run both:

GRANT SELECT ON ALL SEQUENCES IN SCHEMA schema_name TO new_role;

As per the recommendation here:

https://stackoverflow.com/questions/7386321/grant-select-privilege-to-all-sequences-using-one-statement

And as I mention above, this has been successful on other database schema on different machines. I have also tried individually:

GRANT SELECT ON SEQUENCE some_id_sequence TO new_role;

and

GRANT SELECT ON SEQUENCE public.some_id_sequence TO new_role;

This also has no effect. When logged in from new_role I see:

select * from information_schema.role_usage_grants ;
...
(0 rows)

Similar results (or lack thereof) when running \ds.

I can see from the previous role that the sequence should be "grantable" (whatever that means, I can't find any documentation on it)

live@live ~ => select * from information_schema.role_usage_grants limit 1;
┌──────────┬──────────┬────────────────┬───────────────┬──────────────┬─────────────┬────────────────┬──────────────┐
│ grantor  │ grantee  │ object_catalog │ object_schema │  object_name │ object_type │ privilege_type │ is_grantable │
├──────────┼──────────┼────────────────┼───────────────┼──────────────┼─────────────┼────────────────┼──────────────┤
│ old_role │ old_role │ old_role       │ public        │ some_id_seq  │ SEQUENCE    │ USAGE          │ YES          │
└──────────┴──────────┴────────────────┴───────────────┴──────────────┴─────────────┴────────────────┴──────────────┘
(1 row)

So I don't really know where to look at this point. The old role appears to have the ability to grant select to other roles, and doesn't error when attempting to run the command, however the new role still has no access.

The results of \dn+

\dn+
                  List of schemas
┌───────────┬──────────┬────────────────────────┬─────────────┐
│  Name     │ Owner    │ Access privileges      │ Description │
├───────────┼──────────┼────────────────────────┼─────────────┤
│ new_role  │ old_role │ old_role=UC/old_role  ↵│             │
│           │          │ new_role=U/old_role    │             │
│ public    │ old_role │                        │             │
└───────────┴──────────┴────────────────────────┴─────────────┘
(2 rows)

\du+ new_role
                   List of roles
┌───────────┬────────────┬───────────┬─────────────┐
│ Role name │ Attributes │ Member of │ Description │
├───────────┼────────────┼───────────┼─────────────┤
│ new_role  │            │ {}        │             │
└───────────┴────────────┴───────────┴─────────────┘

The results from \dp

\dp some_id_sequence
                                     Access privileges
┌────────┬──────────────────┬──────────┬───────────────────────┬───────────────────┬──────────┐
│ Schema │       Name       │   Type   │ Access privileges     │ Column privileges │ Policies │
├────────┼──────────────────┼──────────┼───────────────────────┼───────────────────┼──────────┤
│ public │ some_id_sequence │ sequence │ old_role=rwU/old_role │                   │          │
│        │                  │          │ new_role=r/old_role   │                   │          │
└────────┴──────────────────┴──────────┴───────────────────────┴───────────────────┴──────────┘

Question: How do I establish what is preventing the sequence grants from being applied?

The manual:

  The view role_usage_grants identifies USAGE privileges granted on various kinds of objects where the grantor or grantee is a currently enabled role.

Nothing additional will show up there after GRANT SELECT .... Try instead:

GRANT USAGE ON SEQUENCE public.some_id_sequence TO new_role;

.. if that's, indeed, what you want. The SELECT privilege has limited use for a SEQUENCE, it allows currval() and lastval().
Typically, you want the USAGE privilege which also allows the crucial nextval() in addition to the above. To work with a serial column, for instance. See:

Explicitly granting permissions to update the sequence for a serial column necessary?

So why do you see this confusing line in your test after granting SELECT?

live@live ~ => select * from information_schema.role_usage_grants limit 1;
┌──────────┬──────────┬────────────────┬───────────────┬──────────────┬─────────────┬────────────────┬──────────────┐
│ grantor  │ grantee  │ object_catalog │ object_schema │  object_name │ object_type │ privilege_type │ is_grantable │
├──────────┼──────────┼────────────────┼───────────────┼──────────────┼─────────────┼────────────────┼──────────────┤
│ old_role │ old_role │ old_role       │ public        │ some_id_seq  │ SEQUENCE    │ USAGE          │ YES          │
└──────────┴──────────┴────────────────┴───────────────┴──────────────┴─────────────┴────────────────┴──────────────┘

The owner of an object has all privileges on it automatically. Hence pg_class.relacl starts out as NULL - signifying default privileges. Once that owner grants privileges to another role, his own privilege is entered explicitly in addition - thus popping up in the information schema views as well. Or, quoting the source:

  If the “Access privileges” column is empty for a given object, it
  means the object has default privileges (that is, its privileges
  column is null). Default privileges always include all privileges for
  the owner, and can include some privileges for PUBLIC depending on the
  object type, as explained above. The first GRANT or REVOKE on an
  object will instantiate the default privileges (producing, for
  example, {miriam=arwdDxt/miriam}) and then modify them per the
  specified request.

Generally, to debug non-trivial stuff, I'd rather look at pg_catalog tables, which are the primary source of truth. pg_class.relacl in this particular case:

SELECT relnamespace::regnamespace, relname, relacl
FROM   pg_class
WHERE  relname = 'some_id_sequence';

Or \z (short for \dp) in psql:

\z some_id_sequence

Assuming old_role is the owner, you should see:

{old_role=rwU/old_role,new_role=r/old_role}  -- after GRANT SELECT...
{old_role=rwU/old_role,new_role=U/old_role}  -- after GRANT USAGE ...
{old_role=rwU/old_role,new_role=rU/old_role}  -- after granting both

And NULL (empty in psql) before granting anything.

Your added output shows that new_role actually has the SELECT privilege, you have just been looking up the wrong view. Seems like new_role is lacking privileges on the public schema - which are normally given to PUBLIC by default.

Aside:  IDENTITY columns in Postgres 11 or later avoid the fuss. Those use sequences just as well, internally, but implicitly owned by the IDENTITY column and with the appropriate privileges automatically. Then you need no separate grants for sequences. See:

Auto increment table column


Can MySQL be patched I just saw a note about a security issue in MySQL so thought that I'd better patch my test server just in case. The MySQL site is not very helpful and a quick google revealed lots of people asking how to patch MySQL and no answers that I could find.

Does anyone know if it's possible to patch the Community Edition of MySQL or is the assumption that it requires a full install of whatever the latest version happens to be?

If you're running MySQL in a Linux system, your maintainers will probably do it the next minute after a patch is released and will integrate it in the current version they're offering. If you're stuck with Windows, yes, you should upgrade to a not affected version.


Database table and NULLs I know that there have been many opinions/sides concerning NULL values in a database.
I have not understood though what is the best practice for this.

I.e. if I have a relational table with an optional attribute i.e. it can take NULL value -so we can end up with a table with many NULLs on that column- is it best to make the attribute a new relational table?

What is the best approach on this?

From a purely relational point of view (prior to sixth normal form), I don't see any need to move a set of columns out into a separate table, just because they are frequently null.

As a trivial example, consider a customer account table with an end date as one of the columns - until the customer closes their account, the end date will be NULL. You are therefore likely to have a large number of NULL values in the end date column, yet it would be a bad choice to move this out into a separate table.

However, as an implementation issue, there may be good reasons to split out certain fields into separate tables. For example, consider a customer address table; certain addresses may require complicated delivery instructions, yet the vast majority are not likely to require them. In such circumstances, it would make sense to have a separate table for address delivery instructions, even if it has the same key (customer address ID) as the main customer address table.

(As a side note, some experts on relational theory - such as Chris Date and Fabian Pascal - are opposed to allowing NULLs within relational databases at all.)


How do I create a table whose values are based on queries of same table? This is something that interests me, but I can't seem to find an answer as to how to do it.

Basically, if I have a table and I want a column of that table to be based on a query, particularly when that query is based on information that is inside the same table, how do I do that?

I don't have access to my SQL program right now, but I suspect it would look something like this:

{
     CREATE TABLE ABCD as ABCE
     (

          A int(11) default null,
          B int(11) default sum(ABCE.A)
     );
}

or something to that effect.

Am I way off base?

You are not off-base at all.  Actually pretty close.  In t-sql you can just use code like this:

CREATE TABLE [dbo].[Sample2](
    [myIntColumn] [int] NULL,
    [myOtherIntColumn] [int] NOT NULL,
    [myBigIntColumn] AS ([myOtherIntColumn]+[myIntColumn])
)

GO

    INSERT INTO Sample2 VALUES (365,256),(10,25000), (1,2)

    SELECT * FROM Sample2

    --myIntColumn   myOtherIntColumn    myBigIntColumn
    --365           256                 621
    --10            25000               25010
    --1             2                   3

If you are looking for an aggregate as the default value, I do not believe you can do that simply.
To have an aggregate as a column in the a table, a view is a viable option, like so:

CREATE VIEW Sample3
AS
WITH CTE AS (
SELECT SUM(myIntColumn) as mySUM
From Sample2 )
SELECT Sample2.*, CTE.mySUM FROM Sample2, CTE
GO

SELECT * FROM Sample3

--myIntColumn   myOtherIntColumn    myBigIntColumn  mySUM
--365           256                 621             376
--10            25000               25010           376
--1             2                   3               376

These queries assume your platform is MS SQL Server.

If your tag sql was meant to only indicate the language, it would be helpful both for this question and future questions to tag with the platform.  For example, you could additionally tag as sql-server.

Depending on your RBDMS and your requirements you might be able to leverage an indexed/materialized view.  This could additionally help performance, but there are some limitations on an Indexed View in SQL Server.


Duplicate Statistics? I have been looking at poorly performing stored procedure that performs well for larger customer databases.  I believe the reason is that SQL Server has not had time to properly create good statistics on the queries.

I used the database tuning advisor to recommend several statistics, and after applying those in test environment with a restored backup of the database in question, performance was significantly better.

My question is this.  If I create a script (Maintenance Script as we call them in house) for support to run on customers who may also be experiencing this issue. Would it be a potential bottle neck on performance if a statistic had already been created automatically for the same column(s) by SQL Sever and my script creates a duplicate statistic?

Auto create and update statistics are set to ON in case you are wondering.  We support customers who run SQL 2005 (unfortunately) or higher.

Would it be a potential bottle neck on performance if a statistic had already been created automatically for the same column(s) by SQL Sever and my script creates a duplicate statistic?

Surely you can write your script such that it detects such a duplicate statistic, instead of just blindly creating another one (possibly multiple times)? We do this for tables, views, procedures, functions, indexes... why not for statistics too? If you have the ability to create a statistic you have the ability to check the metadata for one that's already there (and maybe just needs to be updated).

I haven't done any performance tests, and I'm not sure if auto-stats (sync or async) will kick off multiple times or just pick one of them, but certainly nothing good can come of it. In the one case you're going to spend twice (or more!) the amount of time updating redundant statistics; if they don't both get updated then it's possible that the optimizer will pick the wrong one when using the statistic for cardinality.

I'd be more interested to know how often you find that - with auto-create stats turned on - SQL Server hasn't created a statistic that would yield better performance or estimates, and then suddenly the plans improve because of the statistic you decided to create (that SQL Server didn't bother). My guess is that would be pretty rare (unless you haven't really enabled auto-create stats for those customers, or maybe these are filtered statistics, though that isn't possible for your 2005 folks).

As for stats going stale in general, you may be able to implement Trace Flag 2371 - which improves the thresholds for engaging auto-stats - for some of your customers.


SQL Server Management Studio - Processor counters for different sessions do not add up In SQL Server Management Studio, I have two sessions. The workload for each session is:

Session 1: while loop to select top items from one table;

Session 2: while loop to update statistics of another table using full scan.

Then I used Get-Counter to get the processor counters of each session for a minute:

Get-Counter -Counter "\Process(sqlservr)\% Processor Time" -SampleInterval 1 -MaxSamples 60

When running session 1 itself, the average processor counter in a minute is about 34.6. When running session 2 itself, the average processor counter in a minute is 229.8. But when running session 1 and 2 together, the average processor counter is only 91.7. I tried it several times, sometimes session 1 and 2 add up together would be similar to session 2 itself. However, I was expecting something like the processor counter of session 1+2 would be similar to the one of session 1 + the one of session 2.

Session 1 only has logical reads (no physical reads) so I guess it's not a matter of IO block. The whole CPU usage of session 1+2 is only like 12% so it's not because CPU is fully utilized. Could someone explain why? What should I do to make the CPU usage of running session 1+2 together similar to the sum of running each itself? Thanks!

You are measuring CPU time, not real SQL Server query execution time or real CPU usage. Why do you expect these numbers to add up?
You have to use SQL Profiler or Extended events to capture CPU usage by those queries.


My database keeps crashing on startup how can i repair it without it running? I've got some problems with my database. I hope somebody can help me out.
Problem:
The database keeps crashing on startup. In my log i see a couple off these errors:
[ERROR] /usr/libexec/mysqld: Table '.tablename' is marked as crashed and should be repaired
Normally i would repair this table using PHPMyAdmin or SSH but is it possible to do this without the database running? (because when i start it it crashes and stops)
Or is there any other way to get this all working again? I've got a back up but i will lose some data. The table that is crashed contains cache data, so i could miss that.
Thank you!
Gr. Matthijs
UPDATE by RolandoMySQLDBA

Please post contents of /etc/my.cnf
Is the DB Server bare metal machine or VM ?
How much RAM does the server have ?
Are all the tables InnoDB, MyISAM, or a mixture of both ?
Is the OS Linux or Windows ?

I take it you are using REPAIR TABLE by hand.
I also take it your are doing an automatic table repair for MyISAM. (You have to remove  or comment out the option of my.cnf to disable automatic MyISAM table repair).
You can actually repair the table without using MySQL but mysqld cannot be accessing it. How?
You have to use the offline utility myisamchk
Suppose you have the following

datadir is /var/lib/mysql
database is mydb
table is mytable

The table files would be

/var/lib/mysql/mydb/mytable.MYD
/var/lib/mysql/mydb/mytable.MYI

You can repair it with something like this
cd /var/lib/mysql/mydb
cp mytable.MY[ID] /tmp
cd /tmp
myisamchk -r mytable.MYD mytable.MYI
cd /var/lib/mysql/mydb
mv mytable.MY[ID] ..
mv /tmp/mytable.MY[ID] .

Afterwards, you can either login to mysql and do this
mysql> flush tables;

or restart mysql
service mysql restart

Give it a Try !!!
UPDATE 2014-01-08 15:46 EST
Based on the last comment

Hi Rolando, It is an Linux OS Virtual Machine. 2 Intel Xeon Core 2048 Mbyte Memory. The tables are MyISAM. I will post the rest off the data in a sec (it's hard because it keeps crashing remember)

I would stronly recommend that you get more memory. Why ?
MyISAM caches index pages to the MyISAM key buffer. MyISAM does not cache data. The mysqld process defers all data caching to the OS. That can be a little dangerous for all open MyISAM tables in a low memory DB Server.
A MyISAM table keeps a count of the number of times a file handle has been open against it. When MySQL is down, a MyISAM table with a nonzero file handle count is considered crashed.
Although I provided the answer to the question in fixing a crashed a MyISAM table without mysqld running, you have a bigger problem with a 2GB VM. It should be more like 8GB. You should also run mysqltuner.pl against the VM by doing this:
# wget mysqltuner.pl
# perl mysqltuner.pl

You can then learn if /etc/my.cnf is underconfigured.


SQL Server 2005 Whole-Migration, which databases should I recreate? In relation to my question about SQL Server 2005 migration procedure, I am re-creating the databases on the new server.

I noted that the old server has the following databases (outside of System Databases):

 1. Analysis Server Repository
 2. Archive
 3. BCI
 4. BDD Data Center
 5. Nemo
 6. Fixing
 7. Neutron
 8. ReportServer
 9. ReportServerTempDB
10. Sharepoint
11. STS_ho_whdata_1
12. TVS

Now, some of the databases are clearly custom-made, e.g., #5, #6, #7, #11, etc. However there are some databases that -- from their name -- doesn't seem like databases created by the (previous) dev/maintainer, e.g., #1 and #8.

Is it safe for me to re-create all databases willy-nilly, and just 'copy' the data from the old server to the new server?

If you really care to know which databases are in use, you have to use dmv's (log that data to a table as they get flushed out when server is restarted) or profiler (server side trace) and just filter out the logins and program names connecting to those databases.

once you have that info for your complete business cycle, you will be able to figure out what databases needs to be moved to new server.

also, backup/restore should be a preferred method unless you have a reason of not doing it.

note: we do not know your environment, nor your business so we CANNOT answer what databases you need to move.

reportserver* are eelayed to reporting server that might be installed on the current server.


How can I copy specific tables into one SQL Server Table into a separate table automatically? I'm not sure if this is possible. Looking for some help with a specific issue. I am building an iPhone application with a data layer written in Ruby that will connect to SQL Server 2008. For various reasons, I need to separate the iPhone application from the servers that power my websites. The databases will be hosted on separate servers, and the tables will need to be laid out differently, but I need to push some information from the one database into the other database automatically and instantaneously.

Example: Someone makes a change on the website, updating the website DB. This website DB has a table which will need to be pushed onto a differently named table on a different server.

How can I go about doing this?

The instant answer that springs to mind is transactional replication. If it needs to be bi-directional, then peer-to-peer transactional replication.

If the latency here proves too high, or if the schemabinding is too different, then you could have a synchronous solution, with a linked server between the two DB servers and a trigger on the table which fires on insert/update/delete, that updates the table on the other server using the linked server. Warning here is that triggers can degrade performance a little.


This instance of (MS) SQL Server has been using a process id of X since dd/mm/yyyy I was looking through the SQL Server logs and come across the following informational message.

  This instance of SQL Server has been using a process ID of 1960 since 12/10/2014 20:02:48 (UTC). This is an information message only: no user action is required.

This message has been generated for the last three days. I cannot see the process-id using the activity monitor.

Is this something that may cause/highlight a problem that I need to look into?

No, this is perfectly normal, as the message says. It is just SQL Server's way of telling you the pid.

To verify, within SQL Server you can do select serverproperty('processid'), and then in the OS space you should be able to check the process ID like this in a one-liner PowerShell command:

Get-Process "sqlservr"


Set sql_mode “blank” after upgrading to MySQL 5.6 After upgrading MySQL from 5.5 to 5.6, some our app face to problem and need set sql_mode to blank to solve this issue. I added sql_mode = '' to my.cnf but there was no effect on the mysql setting.

How do I keep the sql_mode blank ?

PROLOGUE

Someone asked the same thing of me in my organization because everyone was using MySQL 5.5. All DB servers was upgraded over the past 8 months to MySQL 5.6. Some client applications were being affected by sql_mode change as well.

ROOT CAUSE

I just found out why what you did does not work and the workaround is very simple.

According to MySQL 5.5 Documentation, sql_mode default is a blank sting.

According to MySQL 5.6 Documentation, sql_mode is default is

blank string in MySQL 5.6.5 and back
NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES in 5.6.6 and into GA

OK, I hope you are sitting down.

This is the lazy way Oracle implemented sql_mode in MySQL 5.6: There is an additional my.cnf file.

If you run

cat /usr/my.cnf

you will see the following

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

See line 28 ?

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

SOLUTION

STEP 01 : Comment out line 28 of /usr/my.cnf

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

STEP 02 : Set sql_mode by hand

mysql> SET GLOBAL sql_mode = '';

THAT'S IT !!!

EPILOGUE

STEP 01 prevents restarts of mysqld from changing sql_mode.
STEP 02 sets sql_mode now, so restarting mysqld is not needed immediately

GIVE IT A TRY !!!


Large `mongodump` followed by `mongorestore` I have a fairly large (~240GB) Mongo database that I need to transfer across a sluggish network and onto a new server.

Traditionally for these situations, I've found that a mongodump followed by a mongorestore has been much faster than a db.cloneCollection() method. However, I realized today that doing a full mongodump followed by a mongorestore is a bit wasteful (I think) since I do all of the data transfer, then do all of the insertions.

I would prefer to transfer data from the old mongo (the mongodump step) while simultaneously inserting available data into the new database (the mongorestore step).

Does anyone know how to parallelize the dumping and inserting process in MongoDB? (And would this actually be faster?)

First of all, you can use a pipe

mongodump -h sourceHost -d yourDatabase … | mongorestore -h targetHost -d yourDatabase …

This reduces the time, as each document read will more or less instantly be restored on targetHost.

However, this has the disadvantage that you might run into problems if the procedure is aborted for some reason, for example of a network failure. As for parallelization, you could do the above for each collection, but I doubt that you will have any performance gain, as the limiting factor most likely is IO, and even if not, the concurrency will most likely be a killer.

What I would do is to create a temporary replica set, consisting of the old server, the new server and an arbiter. The initial sync is rather fast, and even if you run into network problems, the sync mechanism will make sure that everything is fine. After the initial sync is done, you simply have the old server step down as primary and restart the new server without the replSetName option, making it a standalone again. Now you can connect to the new server and all data is transferred.

The advantage is that this works with minimal downtime and no attendance. After you initialized the replica set, your application can still use the old server, and even new data will be automatically transferred to the new server. So you do not have to sit besides it – and we all know that the process is likely to finish at 3am, regardless of the time zone. ;) Any time after the initial sync is finished (even hours or days later), you can make the new server a standalone, and change your application's connection string to the new server. This is a matter of 2 or 3 minutes, if planned properly.

Edit

This method has a drawback, however: You can only upgrade from one release to one release higher. So migrating from 2.4 (archeology as of the time of this writing) to 3.4 (cutting edge), you'd have to repeat the process multiple times:

From 2.4 to 2.6
From 2.6 to 3.0
From 3.0 to 3.2
From 3.2 to 3.4


Storing dynamic data in NoSQL I have scenario where I need to store unstructured data but the rest of my data is structured and relational. An example of the type of unstructured data is as explained below:

User Type 1:

How do you blah blah : 5 fields

User Type 2 :

How do you blah blah : 3 fields

User Type 3 :

How do you blah blah : 7 fields

All 3 types are asked the same question "How do you blah blah" but each user type answers it using different number of fields. And there can be a lot of different user types.

For the relational data, I'm using MySQL but i'm a little confused on how to store this unstructured data:

Serialize as JSON and store in MySQL
Use NoSQL

My requirements are high reads, average updates, average inserts & no deletes. No JOINS needed. I need guaranteed writes & high availability. If I were to choose a NoSQL, it'd be a AP type according to the CAP theorem. I won't be hitting millions of records anytime soon.

I also plan to provide a text search for this data in the future, but it doesn't need to be a real time search, so I can always index the data using Lucene periodically. But of course, document based NoSQL implementations do provide this out of the box. But I have read in a few places where people have advised against storing JSON data in MySQL. But adding a NoSQL layer could be overkill.

What do I do & if you do advise me to go for NoSQL DB, which one should I choose?

I'll answer your question in an orthodox manner, with a twist of heresy:
Orthodoxy: You shouldn't store data in a column in a relational database that isn't "atomic."
Heresy: In your specific situation, you could -- maybe -- consider this blob of JSON to be an atomic object.
Years ago, Chris Date said it like this:

"A relation is said to be in first normal form (abbreviated 1NF) if and only if it satisfies the condition that it contains scalar values only"
 Date, C.J. An Introduction to Database Systems, 6th edition (Addison-Wesley, 1995)

Later, he took a somewhat softer stance:

"1NF just means each tuple in the relation contains exactly one value, of the appropriate type, for each attribute. Observe in particular that 1NF places no limitations on what those attribute types are allowed to be."
Date, C. J. Database Design and Relational Theory: Normal Forms and All That Jazz (OReilly Media, 2012)

The "exactly one value" I'm arguing for here is "exactly one JSON object" (which could, in turn contain a JSON array).
Storing things in JSON in a column is a bad idea if you need to the DBMS to manipulate it in any way, since, of course, it can't be properly indexed like properly normalized data can be... but, arguably, if you really really really don't intend for the DBMS to do anything with what you're storing other than write and read it, the case could be made to store a chunk of JSON in a single column, claiming the JSON array of values to be a single atomic value.
The big objection, I think, to doing this, is when it's done out of a lack of familiarity with the relational model or out of laziness or naivete.  Obviously, there are a lot of ways it could be done wrong, but I'd suggest that there's nothing inherently wrong about storing a chunk of JSON in a database, As Long As You Know What You're Doing.™
And, of course, you could use a MySQL FULLTEXT index on it, now that those are supported in InnoDB as of (MySQL 5.6).


Is there a performance difference in committing and rolling back a read-only transaction? I open a (repeatable read) transaction (BEGIN TRAN) to do some work on certain records. First thing I do is checking if the data I need to change is in the database. In some cases there will be and then I proceed to my changes. But in some cases there will be nothing to do. In this case I either COMMIT TRAN or ROLLBACK TRAN and return from the stored procedure. At this time no changes were done to the data yet, so the effect of commit and rollback is the same.

Are there any consideration I should be aware about to chose between commit and rollback? Is there different performance cost? Other considerations?

Having run this through a debugging session (to refresh my failing memory):

Rollback does more checks than a commit, but it shouldn't result in additional work or have a noticeable affect on performance in the situation you describe.
The read-write transaction doesn't truly begin unless and until a data modification is made.

You can see much of this using DMVs, for example:

-- Temporary procedure to show the state of the transaction
CREATE PROCEDURE #TranState
    @Comment varchar(100)
AS
BEGIN
    SELECT
        @Comment AS Comment,
        DTCT.transaction_id,
        database_name =
            CASE DTDT.database_id
                WHEN 32767 THEN N'resource'
                ELSE DB_NAME(DTDT.database_id)
            END,
        tran_begin_time = DTDT.database_transaction_begin_time,
        tran_type =
            CASE DTDT.database_transaction_type
                WHEN 1 THEN 'read/write'
                WHEN 2 THEN 'read only'
                WHEN 3 THEN 'system'
            END,
        tran_state =
            CASE DTDT.database_transaction_state
                WHEN 1 THEN 'The transaction has not been initialized.'
                WHEN 3 THEN 'The transaction has been initialized but has not generated any log records.'
                WHEN 4 THEN 'The transaction has generated log records.'
                WHEN 5 THEN ' The transaction has been prepared.'
                WHEN 10 THEN 'The transaction has been committed.'
                WHEN 11 THEN 'The transaction has been rolled back.'
                WHEN 12 THEN 'The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted.'
            END
    FROM sys.dm_tran_current_transaction AS DTCT
    JOIN sys.dm_tran_database_transactions AS DTDT
        ON DTDT.transaction_id = DTCT.transaction_id;
END;

Testing on AdventureWorks:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;

EXECUTE dbo.#TranState @Comment = 'After Begin Tran';

SELECT TOP (1)
    P.Name
FROM Production.Product AS P
ORDER BY
    P.Name;

EXECUTE dbo.#TranState @Comment = 'After Select';

UPDATE Production.Product
SET Name = N'New Blade'
WHERE Name = N'Blade';

EXECUTE dbo.#TranState @Comment = 'After Update';

-- Or Commit
ROLLBACK TRANSACTION;

EXECUTE dbo.#TranState @Comment = 'After Tran';

Output:

From a purely practical point of view (as Aaron noted in a comment), it is probably safer to issue a rollback to guarantee no changes are made, in case the code is modified in future. So, it's all about intent: no changes = rollback.

In passing, REPEATABLE READ is an unusual isolation level to choose; it does not always work how people would intuitively expect. Depending on your requirements, you might find SNAPSHOT isolation is a better fit.


user parent table id in nested subquery SELECT users.*,(SELECT COUNT(user_id) AS mutual_connection FROM
                (SELECT user_id
                       FROM (
                                SELECT sender_id AS user_id
                                FROM `connections`
                                WHERE receiver_id=users.id AND status='2'
                                UNION
                                SELECT receiver_id AS user_id
                                FROM `connections`
                                WHERE sender_id=users.id AND status='2'
                        ) tempUser
                        WHERE user_id IN (
                                  SELECT sender_id AS user_id
                                  FROM `connections`
                                  WHERE receiver_id='4' AND status='2'
                                  UNION
                                  SELECT receiver_id AS user_id
                                  FROM `connections` WHERE sender_id='4' AND status='2')
                         GROUP BY user_id)
                as mutualConnection)

     FROM users

Error:

  #1054 - Unknown column 'users.id' in 'where clause'

How to use pass value to sub query

MySQL forbids referencing outer-level columns deeper than one level of nesting. Your query, however, is referencing users.id three levels deep.

What you need, therefore, is to rewrite the correlated subquery in such a way that, even if it uses nested queries, the correlation with the outer level is not nested, something like this:

(
  SELECT
    COUNT(*)
  FROM
    (
      SELECT ...
    ) AS mutualConnection
  WHERE
   ... = users.id
)

The task is quite a challenge because of the way your subquery appears to be correlated with the main query. If I understand it correctly, the logic goes like this:

  For every user, find the number of distinct connections (users) who are also connected to a certain other user (in this case user ID='4').

So you are retrieving a column collected from either of two connections columns, sender_id and receiver_id, depending on whether the other matches users.id. After retrieval, you are checking that the retrieved sender_id or receiver_id be among the connections of User 4. Finally, you are counting all distinct occurrences of the resulting column (which, I will repeat, is a mix of sender_id and receiver_id).

This is how you could do that without the many nesting levels you have attempted and keeping all the correlations at the same level:

SELECT
  u.*,
  (
    SELECT
      COUNT(DISTINCT CASE u.id WHEN c.sender_id THEN c.receiver_id ELSE c.sender_id END)
    FROM
      connections AS c
    WHERE
      c.status = '2'
      AND u.id IN (c.sender_id, c.receiver_id)
      AND (CASE u.id WHEN c.sender_id THEN c.receiver_id ELSE c.sender_id END)
      IN  (
            SELECT sender_id AS user_id
            FROM   connections
            WHERE  receiver_id = '4' AND status = '2'
            UNION
            SELECT receiver_id AS user_id
            FROM   connections
            WHERE  sender_id = '4' AND status= '2'
          )
  ) AS mutual_connection_count
FROM
  users AS u
;

The CASE expression is the user_id column of your query's tempUser derived table. It is used in the COUNT function as well as in the WHERE clause (the IN predicate). Usually such repetition of code is eliminated by nesting. But nesting cannot be used here because of the MySQL limitation mentioned at the beginning of this post. So, repetition of code is the price you have to pay to work around it. Luckily, there is not much of it in this specific case.


Logical Backup strategy in large Oracle Databases in Oracle databases, I would like to know if it is advisable to make logical backups with exp or expdp in large databases with large volumes of data, greater than terabyte, I have seen several systems with Oracle that have large sizes and only perform physical backups With RMAN, I wonder what the strategy would be if a table was deleted for some reason ...? What do you do in such cases? What would be a save strategy in these large Oracle?

With RMAN you can do point-in-time recovery (if properly) configured, so you could restore the database to the moment before the drop. Also, you can even restore just a single table from a backup. Other than that you have the recyclebin from which you can recover dropped tables and you have also Flashback Database. So to recover from a dropped table there are plenty of options - without an export.
In my career I have only once used daily exports as part of the backup strategy, but we stopped that once you could restore a single table from backup.
Regards,
Daniel


ORA-12899: value too large for column COMESFROMTOWN (actual: 50, maximum: 50) I am importing a csv file into an Oracle database using an external table.

The file encoding is UTF-8. The separator is actually ;.

The target table definition is identical to the external table definition.
The NLS_LENGTH_SEMANTICS is defined as CHAR.

Target character fields are defined as COMESFROMTOWN VARCHAR2(50), (example).

Reading the external table and loading into the target table works fine for the majority of them. But records with COMESFROMTOWN length of 49 to 50 characters including a ',' (comma) are rejected because of length: ORA-12899: value too large for column COMESFROMTOWN (actual: 50, maximum: 50)

I tried resizing the external table fields, but the issue also happens when loading the target from the external table. Resizing the target is not an option.

Can you explain why:

The comma being the 2c UTF-8 character, coded on 1 byte, it raises an
error?
NLS_LENGTH_SEMANTICS being defined as CHAR, it does not uses
the requested number of bytes for the string?

How can I workaround this ?

Thanks.

Thanks to Wernfried Domscheit suggestion, this solved the issue :

ALTER SESSION SET nls_length_semantics = CHAR which was decisive
CHARACTERSET WE8ISO8859P1 which exactly fits the source file characterset

Note that our Oracle 11g instance is configured with following defaults:

NLS_LENGTH_SEMANTICS = CHAR
NLS_CHARACTERSET = UTF8


ERROR 1091 (42000): Can't DROP 'created_date'; check that column/key exists mysql> alter table bill_item add  index idx_comp(created_date,product_id);
Query OK, 0 rows affected, 1 warning (1 min 2.62 sec)
Records: 0  Duplicates: 0  Warnings: 1

Trying to drop the index:

mysql> alter table bill_item drop index created_date;
ERROR 1091 (42000): Can't DROP 'created_date'; check that column/key exists

If you want to drop the index that you created previously, the correct statement would be:

alter table bill_item drop index idx_comp;

created_date is the name of one of the columns in your table bill_item.

Note: To find the index name use
SHOW INDEXES FROM <table> if you used a different naming convention than prepending 'idx_'


Do non-PostgreSQL database softwares use roughly the same "structure" for communicating with them? Basically, I have developed a PostgreSQL-based application which "in theory" could have its database software swapped out, but probably would cause a million headaches if I actually attempted to. I'm trying to determine if the other SQL database softwares (I frankly don't care about non-SQL ones in the least, because they seem too different for me to bother with them in this life) have the following concepts:

"hostname"
"port"
"username"
"password"
"handle database" (such as "postgres", which must be used to connect when there is no other database or when certain operations are to be done to the actual database)
"database name"

I guess I'm fairy sure already about all the points except for the 5th. The concept of a "handle database" seems like it might be PG-only. If such is the case, I'm not sure how I should handle that, but I'm awaiting your answers before I make a decision.
I have a good mind to just forget about ever supporting other database softwares, but the way my system is structured basically forces me to at least try to "genericize" the communication with the database with functions called "database_" rather than "PostgreSQL_". (Even when the queries sent to these functions would only work on PG...)

Most client/servers DBMSes have parameters similar to those, and the handle database indeed is an unknown concept elsewhere. But they might need other parameters; the common way to handle is with a connection string (or URL) that encodes all those parameters, or with a generic list of keys/values.
And for databases that do not use a client/server architecture, it's even more different. If you do not need much concurrency and want to avoid having to install the DB server, you would be likely to choose SQLite, and then the only connection parameter is the database file name.