Error in code in SQL Server I made this code for SQL Server to select how many times the rider "AsdoVolante" came first in the first semester of 2017. But it does not work. What could be wrong?
Tables created in SQL Server:
CREATE TABLE Car(
IdCar INT NOT NULL,
Brand VARCHAR(20) NOT NULL,
Model VARCHAR(50),
PRIMARY KEY (IdCar));

CREATE TABLE Rider(
IdRider INT NOT NULL,
Name VARCHAR(20) NOT NULL,
Address VARCHAR(50),
Cell INT,
PRIMARY KEY (IdRider));

CREATE TABLE Team(
IdTeam INT NOT NULL,
Name VARCHAR(20),
Address VARCHAR(50),
Cell INT,
PRIMARY KEY (IdTeam));

CREATE TABLE Sponsor(
IdSponsor INT NOT NULL,
Name VARCHAR(20)NOT NULL,
Address VARCHAR(50),
PRIMARY KEY (IdSponsor));

CREATE TABLE Race(
IdRace INT NOT NULL,
Name VARCHAR(20) NOT NULL,
Address VARCHAR(50) NOT NULL,
DateRace DATE NOT NULL,
NumbLaps INT NOT NULL,
BestTime TIME,
PRIMARY KEY (IdRace));

 CREATE TABLE TeamJoinsRace(
IdEnrolment INT NOT NULL,
IdTeam INT NOT NULL,
IdCar INT NOT NULL,
IdRider INT NOT NULL,
IdRace INT NOT NULL,
FinalPosition INT,
FinalRaceTime TIME,
FOREIGN KEY (IdRace) REFERENCES Race(IdRace),
FOREIGN KEY (IdCar) REFERENCES Car(IdCar),
FOREIGN KEY (IdRider) REFERENCES Rider(IdRider),
FOREIGN KEY (IdTeam) REFERENCES Team(IdTeam),
PRIMARY KEY (IdEnrolment));

CREATE TABLE OficialSponsor(
IdSponsor INT NOT NULL,
IdRace INT NOT NULL,
SponsorMoney MONEY NOT NULL,
FOREIGN KEY (IdRace) REFERENCES Race(IdRace),
PRIMARY KEY (IdSponsor, IdRace));

CREATE TABLE NonOficialSponsor(
IdSponsor INT NOT NULL,
IdRace INT NOT NULL,
SponsorMoney MONEY NOT NULL,
FOREIGN KEY (IdRace) REFERENCES Race(IdRace),
PRIMARY KEY (IdSponsor, IdRace));

CREATE TABLE Mechanic(
IdMechanic INT NOT NULL,
Name VARCHAR(20),
Address VARCHAR(50),
Cell INT,
PRIMARY KEY (IdMechanic));

CREATE TABLE Tuning(
IdTuning INT NOT NULL,
IdMechanic INT NOT NULL,
IdTeam INT NOT NULL,
TypeTuning VARCHAR(20) NOT NULL,
TimeTuning TIME,
DateTuning DATE NOT NULL,
FOREIGN KEY (IdTeam) REFERENCES Team(IdTeam),
FOREIGN KEY (IdMechanic) REFERENCES Mechanic(IdMechanic),
PRIMARY KEY (IdTuning));

CREATE TABLE Laps(
IdLap INT NOT NULL,
IdEnrolment INT NOT NULL,
RelativePosition INT NOT NULL,
TimeLap TIME,
FOREIGN KEY (IdEnrolment) REFERENCES TeamJoinsRace(IdEnrolment),
PRIMARY KEY (IdLap, IdEnrolment));

Code with the error:
SELECT P.DateRace, Nm.Name, epp.FinalPosition
FROM Race P, Rider Nm, TeamJoinsRace epp
WHERE epp.FinalPosition = (SELECT FinalPosition FROM TeamJoinsRace where FinalPosition = 1)
HAVING Nm.Name ='AsdoVolante' AND P.DateRace BETWEEN '2017-01-01' AND '2017-06-30';

Here is the error that I get:

Msg 8121, Level 16, State 1, Line 14
Column 'Piloto.Nome' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8121, Level 16, State 1, Line 14
Column 'Prova.Data' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8121, Level 16, State 1, Line 14
Column 'Prova.Data' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

In most cases HAVING should only be used in a query with a GROUP BY. From BOL:

  Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

You don't have any aggregates so HAVING isn't the right choice. You probably want to continue to use your WHERE clause:

SELECT P.Data, Nm.Nome, epp.PosicaoFinal
FROM Prova P, Piloto Nm, EquipaParticipaProva epp
WHERE epp.PosicaoFinal = (SELECT PosicaoFinal FROM EquipaParticipaProva where PosicaoFinal = 1)
AND Nm.Nome ='AsdoVolante' AND P.Data BETWEEN '2017-01-01' AND '2017-06-30';

Below is a similar query that can use HAVING (although it doesn't really make sense). Note the presence of the GROUP BY:

SELECT P.Data, Nm.Nome, epp.PosicaoFinal
FROM Prova P, Piloto Nm, EquipaParticipaProva epp
WHERE epp.PosicaoFinal = (SELECT PosicaoFinal FROM EquipaParticipaProva where PosicaoFinal = 1)
GROUP BY P.Data, Nm.Nome, epp.PosicaoFinal
HAVING Nm.Nome ='AsdoVolante' AND P.Data BETWEEN '2017-01-01' AND '2017-06-30';

In terms of getting the right results, the query that you have doesn't have join clauses. You're doing a cross join between Prova, Piloto, and EquipaParticipaProva.

Perhaps this is closer to what you wanted:

SELECT P.Data, Nm.Nome, epp.PosicaoFinal
FROM EquipaParticipaProva epp
INNER JOIN Prova P ON p.IdProva = epp.IdProva
INNER JOIN Piloto Nm ON Nm.IdPiloto = epp.IdPiloto
WHERE epp.PosicaoFinal = (SELECT PosicaoFinal FROM EquipaParticipaProva where PosicaoFinal = 1)
AND Nm.Nome ='AsdoVolante' AND P.Data BETWEEN '2017-01-01' AND '2017-06-30';


Monitor volume of data returned to the client application I've identified ASYNC_NETWORK_IO as our top wait by some distance averaging between 70-77% of wait time for our client application SQL Server. This has been put forward towards the development team and rebuffed in a way and so I'd like to provide more detail to emphasise that we are sending an excess of data to the client. My analysis contained a supporting document explaining waits and also a wealth of resources including those from the actual client application side stating that high ASYNC_NETWORK_IO is related to poor code.

To follow this up, I'd like additional detail emphasising the volume of data consistently returned the client and tie these to peaks in our ASYNC_NETWORK_IO waits. Are there any DMVs or any other free sources I could use to report on data sent to the client?

I am aware of sys.dm_exec_query_stats although this only displays information for queries still in the plan cache.

Edit:
Over the past couple of weeks/last month performance has degraded significantly which coincides with a colleague leaving the company. Whether this is coincidence or not is unknown to me. The issue we have is we are unaware of what is going on at the client side. At the moment we have effectively 1 junior developer looking at it and no means to monitor. From the SQL Server side I am constantly seeing little strain and I want to rule SQL as the bottleneck. Obviously things can be done to improve the databases and I'll never dispute it but the issue at the moment is not SQL Server related and I need to be able to relay this. Factors such as the application simply not being able to respond and issues with connecting from the client to the actual application server (not application SQL Server) are for some reason being attributed to SQL Server, with our software development manager looking at Indexing of all things as a cause for the application not responding and people not being able to connect despite no request being made the the SQL Server ?!?!?

Our situation is an incredibly frustrating one in that it's heavily turning into a political battle and accusations are been thrown at us without any substance or data to back them up. In terms of understanding what happens at the client side, it's really difficult for me, like I said, we have no monitoring on the application side, but also the way in which the client submits queries makes things difficult. The request we see at the SQL Server layer is C\AL code (from the dynamics NAV application), but also they appear to have instances where the client session sumbits many batches....

I'll provide an example from last week which caused great confusion for me. The user submitted a query on the client side to return aged stock for customers. This request executed for 30 minutes however whilst monitoring from the SQL Server side I saw many individual requests from the session. So in SQL obviously we'd just join the relevant tables using good syntax and practise to return data. However on the client side, from what I could tell would iterate through all customers in the customers table, and one by one return the data back to the client whilst simulateanously looking at the other relevant tables for aged stock information by individual customer.

This was just one report and unfortunately I am getting little support from devs for troubleshooting. The whole political situation is really hurting our process with the software development manager pursuing a theory based on 'finger in the air' logic. Apologies for the long read.

Thanks,

Warning: this is more of an soft-skill, wholistic approach answer than a technically oriented one.

I'm not aware of anything that will make quick work of this problem - you did say it was a problem, right? I'm not saying don't  pursue this, but if everything is basically working it may not be worth dying on this hill. That is often why developers are had pressed to care at all, in my experience. However, if there are actual problems and you've narrowed down this is the cause...

It should be pretty easy to find out the queries that are your worst offenders (by time, execution frequency, etc) based on your workload. Because if there is a problem, it should be obvious.

Then, just look at what they do. Are they doing a ton of SELECT * on wide tables? Did someone decide to store a ton of XML into an NVARCHAR(MAX) column? Run them a few times and use Client Statistics in SSMS to view the actual size of the data returned. These are all well researched and documented areas of best practice that you can communicate are necessary to ensure smooth operation of the database.

But, it may not even be that the data size is hyuuge. It could be just how the client is handling the data (not uncommonly RBAR is at play in this scenario). In these cases, I try to convince the developers to give me read access to their repositories so I can tunnel in and try to do some light reverse-engineering of the code to understand myself what is going on. I haven't had much push back from this since it is an easy ask and doesn't require much of them.

Depending on your language experience, this may be easy or hard, but it definitely can't hurt to start to understand how the ORM and/or code is traversing through the data it receives. From there, if there are any big flashing red lights, you can probably find more resources or patterns to describe the ideal way to handle the data.

But until you can narrow down the problem more clearly, it isn't surprising no one wants to volunteer their time to help on a wild goose chase.

It can be hard to get development teams to take the concerns of a DBA seriously, so the more you understand the better. The blame game between developers and DBAs can get tense fast and usually doesn't end up benefiting anyone, so just make sure you're not accidentally making them feel attacked or blamed as the source of a problem.

Finally - there are other things that can cause this, but they are very rarely the cause in 2018, so be sure to rule those out just in case.


Is there a setting in MySQL that will allow you to see the columns of a table on a query, even if the table is empty? When I am writing code to go with a certain MySQL query, I like to do a basic select statement to see the columns, and to perhaps see what data is available as a sample.  Sometimes, the table I would like to look at has no data, so I get a completely empty query.

Now, I know I can use something like "show create table [table_name]" to see the query; similarly, I am aware that it's possible to show the tables with a join statement, but the syntax is just complex enough that I don't always remember it, and I don't want to add complexity to every simply query I want to make, just to handle the occasional empty table.

Thus, I was wondering:  is there a setting in MySQL's client, that would allow me to see the column names every time, even when the table is empty?

This will always return a single row with NULL in all the columns of the table:

SELECT t.*
FROM (SELECT 1) AS dummy
  LEFT JOIN table_name AS t
    ON FALSE ;

This will always return a single row with NULL in all the columns of the table if the table is empty and real data from one row if it isn't empty:

SELECT t.*
FROM (SELECT 1) AS dummy
  LEFT JOIN table_name AS t
    ON TRUE
LIMIT 1 ;


what is the impact of changing log shipping schedule timing jobs? I have two severs primary and secondary log shipped server , after configuring it, I noticed that the latency of last copied file and last restored file increased and the alert of monitor sever fired a lot of times as I see it in SQL server error logs so I  modified the alert job at primary server  to fire every 15 minutes instead of 2 minutes but now I decided to reschedule the time of copy jobs to fire every 15 minutes instead of 10 minutes to decrease the the time of latency for both of the copy and restored jobs.

My question: is there any threats that may affect the log shipping process or is it normal to change the job schedule even for the alert, copy, restore and backup jobs?

As Shanky noted, this is not a problem as long as you are not actively conflicting with your own SLAs around backup/restores.

The documentation notes you can customize the jobs as needed (steps #8, 16, 21):

  Note the backup schedule listed in the Schedule box under Backup job. If you want to customize the schedule for your installation, then click Schedule and adjust the SQL Server Agent schedule as needed.


SQL Procedure Cache getting wiped out every 60 seconds I have an Azure VM running SQL 2017, deployed around August 2020. Not yet taking a production load. Several databases restored, but no users active yet. Running sp_Blitz I was getting this: "The oldest query in the cache was created at "
So I ran this query every second for several minutes:
select
datediff(second, min(creation_time), getdate() ) AS [Age seconds],
count(*) as [Row Count]
from sys.dm_exec_query_stats

[Age Seconds] would get up to 60, and [Row Count] would get up to
about 30 or 40 max, then it would reset to 0's EVERY MINUTE.

This was going on for several days.
I disabled just about every login except mine, stopped ancillary services (Agent, SSIS, SSRS, etc.) and took user databases off-line. Nothing stopped it.
I see in the log several entries of

"A significant part of sql server process memory has been paged
out..."

Finally a re-start of the service did the trick.
SOMETHING SOMEWHERE was running DBCC FREEPROCCACHE every MINUTE like clockwork.
Has anyone ever encountered this, and if so, what was the resolution?

"A significant part of sql server process memory has been paged out..."

I would start with above message. This message means SQL Server working set memory is paging out process to disk. A widely used workaround is, and since you have SQL Server running on Azure VM, to provide SQL Server service account Locked pages in memory (LPIM) privilege. Please refer to this MS support article . To assign LPIM
To assign the Lock pages in memory user right, follow these steps:

Click Start, click Run, type gpedit.msc, and then click OK.

Note The Group Policy dialog box appears.

Expand Computer Configuration, and then expand Windows Settings.

Expand Security Settings, and then expand Local Policies.

Click User Rights Assignment, and then double-click Lock pages in memory.

In the Local Security Policy Settingdialog box, click Add User or Group.

In the Select Users or Groups dialog box, add the account that has permission to run the Sqlservr.exe file, and then click OK.

Close the Group Policy dialog box.
9.Restart the SQL Server service.


Replace Null values with values from the same column by matching values in a different column I have a table with some null values in column "date":

platform   date         id
---------------------------
web        2018-10-10   1
mob                     1
mob                     1
web        2018-10-15   2
mob                     2
ntl        2018-10-09   3
web        2018-10-12   3
web        2018-10-11   4
mob                     3

I want to update null values in 'date' for 'mob' platform by matching the 'id' column from platform 'web'. The result should look like this:

platform   date         id
---------------------------
web        2018-10-10   1
mob        2018-10-10   1
mob        2018-10-10   1
web        2018-10-15   2
mob        2018-10-15   2
ntl        2018-10-09   3
web        2018-10-12   3
web        2018-10-11   4
mob        2018-10-12   3

Will really appreciate your help!

UPDATE `table` t1, `table` t2
SET t1.`date` = t2.`date`
WHERE t1.id = t2.id
  AND t1.platform = 'mob'
  AND t2.platform = 'web'
  AND t1.`date` IS NULL
  AND t2.`date` IS NOT NULL

PS. The solution assumes that in a subarray of the table which matched platform = 'web' AND t2.`date` IS NOT NULL condition the id value is unique. If not, a random value from possible values list will be used, and you must use a subquery instead of t2 table copy, which filters data by platform = 'web' condition, groups it by id and selects alone data value (maximal, for example) for each id value. If your MySQL version is 8+, you may do it in WITH clause.


Mysql foreign key cascade delete Does mysql have a way to delete all sub links if a row was deleted from a "parent" table?

Example table setup is below, the issue I am trying to avoid is having to maintain a script that deletes the data as the table ex_item_a will have a lot of similar tables[they have that same foreign key relationship]....In this case, let's say if I delete someone from the user table it should delete all links.

CREATE TABLE IF NOT EXISTS `account` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `user` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `account_id` int(10) unsigned NOT NULL,
    `email` varchar(50) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `account_id` (`account_id`),
    CONSTRAINT `FK_user_account` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS `account_tbl_ex` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `account_id` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    KEY `account_id` (`account_id`),
    CONSTRAINT `FK_account_tbl_ex_account` FOREIGN KEY (`account_id`) REFERENCES     `account` (`id`)
);

CREATE TABLE IF NOT EXISTS `account_tbl_ex_link` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `link_id` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    KEY `link_id` (`link_id`),
    CONSTRAINT `FK_account_tbl_ex_link_account_tbl_ex` FOREIGN KEY (`link_id`) REFERENCES `account_tbl_ex` (`id`)
);

CREATE TABLE IF NOT EXISTS `ex_item_a` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `tbl_link_id` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    KEY `tbl_link_id` (`tbl_link_id`),
    CONSTRAINT `FK_ex_item_a_account_tbl_ex_link` FOREIGN KEY (`tbl_link_id`) REFERENCES `account_tbl_ex_link` (`link_id`)
);

I did a little more research and tested on my local database, found out that the child tables just need to cascade delete and it works from the top down perfectly.

You do this by adding the ON CASCADE DELETE option to the foreign keys where you want this to happen.


Only specific variations of LIMIT are working for me? I spent most of yesterday coding a "TOP 10" page for my website to pull from my database.

It was a pretty simple SQL statement:

SELECT * FROM `cards` GROUP By `name` ORDER BY `viewsperweek` DESC LIMIT 10;

It was all working perfectly for me last night and the info was correct but when I woke up this morning it no longer works.

After some testing it seems only LIMITS of 1-5 will work. If I put it past 5 now the SQL result now longer shows on the website (it DOES continue to work on the database itself).

So basically now I can seemingly only do this:

SELECT * FROM `cards` GROUP By `name` ORDER BY `viewsperweek` DESC LIMIT 5;

Can't for the life of me figure out why it would work last night and not this morning.
The code itself is being called from a js file which in turn calls the php file for the SQL code.

Fixed it finally.

Was an issue with the I was using GROUP BY, ORDER BY and LIMIT, at least I think.

I changed it to the following and it's working smoothly now:

SELECT DISTINCT `name`, `id`, `viewsperweek`, `totalviews` FROM `cards` GROUP BY `name` ORDER BY `viewsperweek` DESC LIMIT 10

I ran into this issue before with my Database and forgot about it. There are some cards that are duplicates so I needed to remove them through the SQL code. In my original code it was hitting some duplicates after the 5th and breaking.
Honestly not sure why (as the code worked in my back-end db), but it's working now.


Grant access to all objects (with a few exceptions) to a role In SQL Server 2014, I want to create a role that will have access (select, delete, update, insert, execute) to all the tables, views, stored procedures, except a certain list of tables that contain sensitive data that only the "sa" user should have access to.

I tried the following:

Using SQL Server Management studio, in the database (TESTCOMPANY) -> Security -> Roles -> Database roles, create the database role "CompanyAdmin".

Run the following query:

GRANT  SELECT,INSERT, UPDATE, DELETE, EXEC ON DATABASE::TESTCOMPANY TO CompanyAdmin

Then:

DENY SELECT, INSERT, UPDATE, DELETE ON TestTable1 TO CompanyAdmin;
--Repeat for all tables to secure

In Management Studio, server -> Security -> Logins, create a new login "testuser". Server Roles = "public" only.
Then go to TESTCOMPANY -> Security -> Users -> New user:

User Type: SQL user with login
User name: testuser
Login name: testuser
Default schema: dbo
Membership: CompanyAdmin

Once logged in as testuser, all seems fine, I see all tables except the ones in the DENY permissions. BUT:

If, in the future, the "sa" user creates a view on the secured tables, the testuser user has access to it and can view the data it returns
In SQL Server Management Studio, I can go to Server -> Security -> Logins and I see the "sa" login properties

Am I taking the right approach or is there a better way to accomplish what I want to do?

It's the database for the accounting application of our company and the software is designed to have everything in the same database.

The sad truth is that you cannot do anything computerwise about the 'sa' activities.  This is because the 'sa' (or a member of the sysadmin server role) are defined to be able to do pretty much anything.

So, of course, you should only have  sysadmin's that you can trust to do their best to adhere to the standards for the database.  But even the best sysadmin is imperfect.  So you might set up some auditing to report on changes made in the database.

(Or, more low tech, periodically check your views for a restricted table name in a view.)

So it depends on what you are concerned about and how much effort you need to put into protecting the secure resources.

In terms of views that expose secure information, you can DENY permissions on a view.  Even if a sysadmin created the view, the DENY of the view for those without approval will prevent their access.

Perhaps any high security views could be named something like SecureViewXYZZY so as to emphasize the reason for those views and the restriction of who can use them.


Why DBCC CHECKDB is multiple times in error log I have an issue with dbcc checkdb command . In the errorlog file I find the dbcc checkdb command multiple time within the interval of 30-45 minutes. I have scheduled a job which grep keyword error from the errorlog file but due to dbcc checkdb all the dbcc checkdb command my mail is simply full with it.

Can anyone please help me on this that why we use dbcc checkdb command and what is the use of it. Is this behavior is ok or we need to take some action over it.

Please help me out.

Thanks,

Nitesh Kumar

I think @MartinSmith nailed it in his comment.  When you have AUTO_CLOSE set to ON, then the database will shutdown when the last user exits.  Likewise, when somebody attempts to use the database it will reopen.  When the database reopens, a message about the last time DBCC CHECKDB ran will be logged.

Take the below example (sample code, not meant to be run anywhere near a production environment) to show proof of this:

create database AutoCloseDb;
go

alter database AutoCloseDb
set auto_close on;
go

dbcc checkdb('AutoCloseDb');
go

-- rotate the below "use" statements a few times in order
-- to cause the database to close and reopen
--
use AutoCloseDb;
go

use master;
go

After we do a few rotations of use AutoCloseDb; followed by use master;, we would see something similar to the below in the SQL Server error log (I use the below PowerShell code to retrieve this):

Add-Type -Path "C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server("<your SQL Server name>")

$SqlServer.ReadErrorLog() |
    Where-Object {$_.Text -like "*AutoCloseDb*"} |
    Select-Object LogDate, Text |
    Format-List

And in my test environment, the output is the following:

  LogDate : 11/15/2013 5:47:17 AM Text    : Starting up database
  'AutoCloseDb'.

  LogDate : 11/15/2013 5:47:18 AM Text    : CHECKDB for database
  'AutoCloseDb' finished without errors on 2013-11-15 05:39:19.137
  (local time). This is an informational message only; no user action is
  required.

  LogDate : 11/15/2013 5:47:18 AM Text    : Starting up database
  'AutoCloseDb'.

  LogDate : 11/15/2013 5:47:18 AM Text    : CHECKDB for database
  'AutoCloseDb' finished without errors on 2013-11-15 05:39:19.137
  (local time). This is an informational message only; no user action is
  required.

  LogDate : 11/15/2013 5:47:19 AM Text    : Starting up database
  'AutoCloseDb'.

  LogDate : 11/15/2013 5:47:19 AM Text    : CHECKDB for database
  'AutoCloseDb' finished without errors on 2013-11-15 05:39:19.137
  (local time). This is an informational message only; no user action is
  required.

  LogDate : 11/15/2013 5:48:25 AM Text    : Starting up database
  'AutoCloseDb'.

  LogDate : 11/15/2013 5:48:26 AM Text    : CHECKDB for database
  'AutoCloseDb' finished without errors on 2013-11-15 05:39:19.137
  (local time). This is an informational message only; no user action is
  required.

  LogDate : 11/15/2013 5:49:30 AM Text    : Starting up database
  'AutoCloseDb'.

  LogDate : 11/15/2013 5:49:30 AM Text    : CHECKDB for database
  'AutoCloseDb' finished without errors on 2013-11-15 05:39:19.137
  (local time). This is an informational message only; no user action is
  required.

If it is indeed AUTO_CLOSE that is causing this logging behavior in your case, you should see the similar message of "Starting up database ..." in your error log.

If this is not what you're seeing, then please modify your question to include other error log events surrounding the DBCC CHECKDB logging.


How to write concat query for multiple 'select into' statement in mysql stored procedures? I am trying to assign values to variables in mysql stored procedures. Here is my code
declare count int;
declare ParamOrgId int;
declare isPremium int;

SET @sql = CONCAT(' select count(id) , orgid , (select id from org_subscription t where t.orgid=mainOrgID)  into ', @count,',',ParamOrgId ,',',isPremium ,' '
 from ', tableA );

PREPARE stmt from @sql;
EXECUTE stmt ;

SELECT concat('my val is ', ParamOrgId);
END

SIMPLY use user defined variales instead of variables.
CREATE PROCEDURE `new_procedure` (IN tableA varchar(255))
BEGIN
SET @sql = CONCAT(' select count(id) , orgid , (select id from org_subscription t where t.orgid=mainOrgID)  into @count,@ParamOrgId ,@isPremium  from ', tableA );

PREPARE stmt from @sql;
EXECUTE stmt ;

SELECT concat('my val is ', @ParamOrgId);
END

But this only works if you get only 1 result row. IF you want more, you need a Loop


SQLite: choosing a unique separator I'm working on a project that will result in an SQLite database of about 6 GB of text content (encoded UTF-8). The text will be diverse: it will have a great deal of plain text writing, but also a significant amount of special characters (tildes, backticks, section symbols, mdash's, endash's etc.). There will also be math formulas.

It looks like .import will be the load method.

Question: What could I use as an .separator value that won't be in my text?

I've grepped the future text with a few ideas and have not identified a separator that isn't in the actual content.

I suppose I could escape whatever separators may be in the text. But I prefer to avoid that option if I can.

I was just about to suggest using a multi-character separator or a character from a foreign language (e.g. 日) but .separator does not allow multi-character strings or even a multi-byte characters.

I would use a multi-character separator with very low probability to be in the text to import and then parse the document using a custom script. Here is an example in Python3 using $$$$$$ as a separator, but consider strings like [ŠĐć~^˘°˛˙€] if needed.

import.csv

hello $$$$$$ world $$$$$$ 1
foo   $$$$$$ bar   $$$$$$ 2

sqliteimport.py

import sqlite3

import_file_name = "import.csv"
cell_separator = "$$$$$$"
lines = []
insert_query = "INSERT INTO imported (a, b, c) VALUES (?, ?, ?);"
db_connection = sqlite3.connect("database.sqlite")
db_cursor = db_connection.cursor()
db_cursor.execute("CREATE TABLE imported (a TEXT, b TEXT, c INTEGER);")
db_cursor.execute("BEGIN TRANSACTION")
with open(import_file_name, 'r') as import_file:
    for line in import_file:
        # Split each line at each cell_separator into a list of strings.
        # Strip each one of the columns to remove whitespaces.
        cleaned_columns = [column.strip()
                           for column in line.split(cell_separator)]
        db_cursor.execute(insert_query, tuple(cleaned_columns))
db_connection.commit()
db_cursor.execute("VACUUM;")
db_cursor.close()
db_connection.close()


How to refresh Oracle data dictionary to remove deleted tablespace and datafiles? Long story short we have a server with two Oracle instances running on it: ora1 and ora2. Somebody defined an undo tablespace for ora2 and provided the same datafile of the undo tablespace datafile of ora1 that was already running, so datafile got corrupted and ora1 was shutdown. When trying to reopen the instance ora1, we were faced with the below error:

ORA-01159: file is not from same database as previous files - wrong database id

We tried to alter database datafile .... offline drop; and it went fine without errors but that also didn't allow us to open the database, so we backed up controlfile to trace and re-edited the file removing that corrupted datafile, and ran the new control file only to get the below error this time:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01173: data dictionary indicates missing data file from system tablespace
Process ID: 9460
Session ID: 355 Serial number: 9

Noting that the corrupted datafile is now not listed when selecting from v$datafile nor the tablespace associated to it from v$tablespace.

Is there anyway to edit the data dictionary that Oracle compare upon opening and causing the error? My ultimate goal is to open the database without having to reinstall it, and I'm okay with losing the data as I have an expdp dmp that I can restore for the schema I am using.

Database is Oracle 11gR2 on Oracle Linux server 6.3

There is nothing wrong with the data dictionary.

The moment you overwrote the undo datafile of an active database (which I still wonder how you managed, because file locking does not allow this) you made it impossible to recover the database to a consistent state.

The method you tried would have worked with a regular user tablespace, but undo is different. Your database crashed, it is in inconsistent state. The database can not be recovered to a consistent state, because transactions that were active at the time of the crash must to be rolled back, the rollback needs the undo, but you do not have it anymore. You can not just simply skip undo and remove references to it from the dictionary.

Even if you managed to open an inconsistent database (there are some hidden parameters that allow this in some cases), it may just crash immediately after opened. This scenario is the one before last resort to salvage data from a database that can not be opened anymore and has no backups. (The last resort is contacting 3rd party companies for their data mining tools that can read Oracle datafiles directly and salvage data from them.)

Typically you would just get your RMAN backup, restore and recover the missing file, and you would be over with it - but you have no backup.

You have a dump file, then go ahead, create a new empty database, and import to it. Then schedule a proper backup job.


Reverse engineer Oracle database in visio Trying to reverse engineer tables added into a database but when i connect, I get strange Chinese symbols with a lot of apex stuff.

I dont see anywhere the tables that i added, what am i doing wrong.

Check the character set the database is configured to support, then check Visio to see if it's using the same set. Also I think character sets can be configured on a per table basis so if some tables are fine and the new ones are not, take a look at the table character set too.


Can I trigger events based on timestamp data or should I use polling? I'm asking this question for SQL Server 2008+ but this could really be for any DBMS.

Imagine you have scheduled data, something like:

ID   Data   Timestamp
0    NULL   2014-11-22
1    ...    2014-11-24
2    ..     2014-11-25

Is there a way to trigger my application when a timestamp is reached ?
Or shouldn't I do this in the first place and use polling instead because of performance reasons just like with triggers when new rows are added ?

I think that in theory the 'trigger based on timestamp' seems much better than the 'trigger based on new row' concerning the performance because you can easily group rows together based on their timestamps.
However, I have absolutely no idea how to actually do this in practice.

FYI: I'm inexperienced with DBMS systems, so maybe I have overlooked something very obvious. Sorry in that case.

(This is very similar to a question recently posted on S.O.: Time trigger database updating)

There is no direct way of doing this, at least not in SQL Server. In that similar question linked above I did detail a way of getting very close to this concept in SQL Server, and someone mentioned Service Broker, but these are still indirect and have not really been tested to see how well they meet up to the original desire.

On the other hand, I also mentioned in my answer in that other question that there is a technology that does do exactly this (i.e. time-based expiration): cache severs. There are at least three that I am aware of:

memcached
redis
Microsoft AppFabric

Or, depending on how this question is interpreted, if the desire is to kick off a process based on reaching a specific time, you can implement something like Quartz.NET which is a job scheduler. Using this you could still keep track of the schedules in a table in SQL Server, and when events get kicked off, part of that process would update that table in SQL Server with the status.

A simplistic, general setup might look like:

[ScheduleData] table

Add [StatusID] TINYINT

Values:

1 = Completed/Pending
2 = Running
3 = Error

Add [ErrorCount] TINYINT
Add DEFAULT CONSTRAINT on [ErrorCount] being (0)
Maybe rename [timestamp] field to be [CreatedOnDate] or [ModifiedOnDate]
Add DEFAULT CONSTRAINT on [CreatedOnDate] being (GETDATE() or GETUTCDATE())
Add [TaskOutput] NVARCHAR(MAX)
Add DEFAULT CONSTRAINT on [TaskOutput] being ('')

Stored Procedures:

[ScheduleData_AddNewTask]

Params (@Data NVARCHAR(4000 or MAX), @ScheduleDataID INT OUTPUT)
INSERT INTO dbo.ScheduleData (Data, StatusID) VALUES (@Data, 1);
SET @ScheduleDataID = SCOPE_IDENTITY();

[ScheduleData_UpdateTask]

Params (@ScheduleDataID INT, @StatusID TINYINT, @TaskOutput NVARCHAR(MAX))
UPDATE [ScheduleData]
SET StatusID = @StatusID,
TaskOutput = @TaskOutput,
ErrorCount = [ErrorCount] + (CASE @StatusID WHEN 3 THEN 1 ELSE 0 END)
WHERE ScheduleDataID = @ScheduleDataID;

App code:

When you are creating a new job to be scheduled:
First, call [ScheduleData_AddNewTask] which returns ScheduleDataID
Create the job such that it will:

Start by calling [ScheduleData_UpdateTask], passing in the ScheduleDataID, 2 for @StatusID, and String.Empty for @TaskOutput.
Finish by calling [ScheduleData_UpdateTask], passing in the ScheduleDataID, 1 for @StatusID, and anything relevant for @TaskOutput.
If an error occurs, call [ScheduleData_UpdateTask], passing in the ScheduleDataID, 3 for @StatusID, and anything relevant for @TaskOutput.


sp_executesql with user defined table type not behaving correctly Problem
Is there a known issue with user defined table types as parameters to sp_executesql?
Set up script
This script creates one each of table, procedure and user defined table type (restricted SQL Server 2008+ only).

The heap's purpose is to provide an audit that yes, the data made it into the procedure. There are no constraints, no nothing to prevent data from being inserted.
The procedure takes as a parameter a user defined table type. All the proc does is insert into the table.
The user defined table type is bog simple as well, just a single column

I have run the following against 11.0.1750.32 (X64)  and 10.0.4064.0 (X64) Yes, I know that box could be patched, I don't control that.
-- this table record that something happened
CREATE TABLE dbo.UDTT_holder
(
    ServerName varchar(200)
,   insert_time datetime default(current_timestamp)
)
GO

-- user defined table type transport mechanism
CREATE TYPE dbo.UDTT
AS TABLE
(
    ServerName varchar(200)
)
GO

-- Stored Procedure to reproduce issue
CREATE PROCEDURE dbo.Repro
(
    @MetricData dbo.UDTT READONLY
)
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO dbo.UDTT_holder
    (ServerName)
    SELECT MD.* FROM @MetricData MD
END
GO

Problem reproduction
This script demonstrates the problem and should take five seconds to execute. I create two instances of my user defined table types and then try two different means of passing them in to sp_executesql  The parameter mapping in the first invocation mimics what I captured from SQL Profiler. I then call the procedure without the sp_executesql wrapper.
SET NOCOUNT ON
DECLARE
    @p3 dbo.UDTT
,   @MetricData dbo.UDTT
INSERT INTO @p3 VALUES(N'SQLB\SQLB')
INSERT INTO @MetricData VALUES(N'SQLC\SQLC')

-- nothing up my sleeve
SELECT * FROM dbo.UDTT_holder

SELECT CONVERT(varchar(24), current_timestamp, 121) + ' Firing sp_executesql' AS commentary
-- This does nothing
EXECUTE sp_executesql N'dbo.Repro',N'@MetricData dbo.UDTT READONLY',@MetricData=@p3
-- makes no matter if we're mapping variables
EXECUTE sp_executesql N'dbo.Repro',N'@MetricData dbo.UDTT READONLY',@MetricData

-- Five second delay
waitfor delay '00:00:05'
SELECT CONVERT(varchar(24), current_timestamp, 121) + ' Firing proc' AS commentary
-- this does
EXECUTE dbo.Repro @p3

-- Should only see the latter timestamp
SELECT * FROM dbo.UDTT_holder

GO

Results
Below are my results. The table is initially empty. I emit current time and make the two calls to the sp_executesql. I wait for 5 seconds to pass and emit current time followed by calling the stored procedure itself and finally dump the audit table.
As you can see from the timestamp, the record for the B record corresponds to the straight stored procedure invocation. Also, there is no SQLC record.
ServerName                                       insert_time
------------------------------------------------------------------------

commentary
-------------------------------------------------
2012-02-02 13:09:05.973 Firing sp_executesql

commentary
-------------------------------------------------
2012-02-02 13:09:10.983 Firing proc

ServerName                                       insert_time
------------------------------------------------------------------------
SQLB\SQLB                                        2012-02-02 13:09:10.983

Tear down script
This script removes the objects in the correct order (you can't drop the type before the procedure's reference has been removed)
-- cleanup
DROP TABLE dbo.UDTT_holder
DROP PROCEDURE dbo.Repro
DROP TYPE dbo.UDTT
GO

Why this matters
The code seems silly but I don't have much control over the use of the sp_execute vs a "straight" call to the proc. Higher up the call chain, I am using the ADO.NET library and passing in a TVP as I have done previously. The parameter's type is correctly set to System.Data.SqlDbType.Structured and the CommandType is set to System.Data.CommandType.StoredProcedure.

sp_executesql is for executing ad-hoc T-SQL. So you should try:

EXECUTE sp_executesql N'exec dbo.Repro @MetricData',N'@MetricData dbo.UDTT READONLY',@MetricData=@p3


Does Index Intersection work with Included Columns? I've been asked by a report designer whether it is acceptable to add a new index to a table to speed up a particular report.  The index would be on a single column, CreatedDate, but would have included columns.

There are already five indexes on the table and I want to avoid creating separate indexes for specific reports or queries.  However, I figure a single-column index might be useful in index intersection, particularly on a column that is likely to be used in multiple queries, like CreatedDate.

What I'd like to know, to help me make my decision, is whether an index which has included columns could be used in index intersection, or whether having included columns would prevent the index from being used in index intersection.

I've tried to Google this question and haven't found any information about the effect of included columns on index intersection.

Included columns don't prevent the index being used for any purpose.

The query optimizer will pick the best index (or possibly a table scan) that is the most efficient for the query being compiled.  Keep indexes as small as possible, while still providing the desired functionality.  According to Books Online INCLUDEd columns don't affect the query optimizer's selection of an index based strictly on size, however if any given index has all columns required for a query, either as key columns or included columns, that index will be given priority.

Since the query optimizer is a fairly opaque beast, you most likely need to test the suggested index to see if it is used, and what the resulting impact is for INSERTs, UPDATEs, and DELETEs.


sys.dm_exec_requests - start_time I have the following query that tells me the status of queries in a database I'm responsible for (although I am not a DBA).

SELECT
     T3.FullStatement as FullSQLStatement
    ,T3.ExecutingStatement
    ,req.session_id as SessionId
    ,T2.login_name as LoginName
    ,command as SQLCommand
    ,start_time as StartTime
    ,DateDiff(MINUTE,start_time,GetDate()) as ElapsedTimeMinutes
    ,req.status as QueryStatus
    ,req.wait_type as WaitType
    ,req.wait_time as WaitTimeMs
    ,blocking_session_id as BlockingSessionId
    ,req.row_count as [RowCount]
    ,req.cpu_time as CpuTimeMs
    ,req.total_elapsed_time as TotalElapsedTimeMs
    ,SubString(sqltext.TEXT,req.statement_start_offset,req.statement_end_offset-req.statement_start_offset)
FROM sys.dm_exec_requests req
    Inner Join sys.dm_exec_sessions T2 ON T2.session_id = req.session_id
    Cross Apply dbo.GetExecutingSQLStatement (req.session_id) T3
    Cross Apply sys.dm_exec_sql_text(sql_handle) AS sqltext
where req.database_id = 5
Order By 6 Desc

It generates output that looks, partly, as follows:

Even though it doesn't show very well in the example I've included, the start_time is the start time of the full statement and not the executing portion of the statement. Is there somewhere I can get the start time of the executing portion along with the start time of the full statement. This is important to me because, obviously, a full statement could have many individual queries.

At the moment this does not seem like a possible value to get, which is unfortunate as this does seem like a handy value to have.

Ok, I did find it (mostly). There is a last_execution_time field in the sys.dm_exec_query_stats DMV, though that DMV comes with the following warning:

  An initial query of sys.dm_exec_query_stats might produce inaccurate results if there is a workload currently executing on the server. More accurate results may be determined by rerunning the query.

Also, I am not sure how the value of last_execution_time is affected by multiple concurrent executions of the exact same SQL across multiple sessions since sys.dm_exec_query_stats is per query batch, not per session.

Below is what I have so far. There is maybe one thing I kinda want to clean up, but this should already account for the first query in a batch which will not have a corresponding entry in sys.dm_exec_query_stats since that gets populated (ideally, but not always in a timely manner) after the current statement / query completes. And it seems to work for single-query batches, so try it out.

SELECT req.session_id AS [SessionID],
       req.start_time AS [BatchStartTime],
       req.total_elapsed_time as [BatchMilliseconds], --[TotalElapsedTimeMs],
       CASE WHEN stat.last_execution_time IS NOT NULL THEN
                DATEADD(MILLISECOND,
                        (stat.last_elapsed_time / 1000.0),
                        stat.last_execution_time)
            ELSE req.start_time END AS [StatementStartTime],
       DATEDIFF(MILLISECOND,
                CASE WHEN stat.last_execution_time IS NOT NULL THEN
             DATEADD(MILLISECOND, (stat.last_elapsed_time / 1000.0),
                stat.last_execution_time)
                  ELSE req.start_time END, GETDATE()) AS [StatementMilliseconds],
       T2.login_name AS [LoginName],
       req.command AS [SQLCommand],
       req.[status] AS [QueryStatus],
       req.wait_type AS [WaitType],
       req.wait_time AS [WaitTimeMs],
       blocking_session_id AS [BlockingSessionId],
       req.row_count AS [RowCount],
       req.cpu_time AS [CpuTimeMs],
       sqltext.[text] AS [QueryBatch],
       SUBSTRING(sqltext.[text],
                 req.statement_start_offset / 2,
                 CASE req.statement_end_offset
                      WHEN -1 THEN DATALENGTH(sqltext.[text])
                      ELSE (req.statement_end_offset - req.statement_start_offset) / 2
                 END) AS [CurrentQuery]
FROM sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions T2
        ON T2.session_id = req.session_id
LEFT JOIN sys.dm_exec_query_stats stat
        ON stat.[sql_handle] = req.[sql_handle]
       AND stat.statement_end_offset = (req.statement_start_offset - 2)
CROSS APPLY sys.dm_exec_sql_text(req.[sql_handle]) sqltext
-- WHERE req.session_id = 56
ORDER BY req.start_time;

I have been testing with your query (slightly modified) and the following SQL running in another tab / session (the TOP(1), TOP(2), etc, and 10.1, 10.2, etc on the WAITFOR times are to make it easily visible which statement is being processed when looking at the current statement field):

SELECT GETDATE() AS [StartTime];
GO
SELECT TOP(1) [name] FROM sys.objects;
WAITFOR DELAY '00:00:10.1';

SELECT TOP(2) [name] FROM sys.objects;
WAITFOR DELAY '00:00:10.2';

SELECT TOP(3) [name] FROM sys.objects;
WAITFOR DELAY '00:00:10.3';

SELECT TOP(4) [name] FROM sys.objects;
WAITFOR DELAY '00:00:10.4';

Then just run your query several times and it is pretty clear that dm_exec_requests.start_time and dm_exec_sessions.last_request_start_time are:

the same value, and
do not change at all during the execution of that batch.

I did try getting the execution plan via CROSS APPLY sys.dm_exec_text_query_plan(req.plan_handle, req.statement_end_offset, req.statement_end_offset) tplan, but that was NULL so not very helpful.

In order to test with your query, I had to remove the first two columns and the reference to the dbo.GetExecutingSQLStatement function. This is probably something you could also get rid of since you have that logic in this query already via CROSS APPLY sys.dm_exec_sql_text(req.[sql_handle]). You just need to make the following three changes:

T3.FullStatement (Select field # 1) becomes sqltext.[text]
T3.ExecutingStatement (Select field # 2) becomes SUBSTRING(sqltext.[text], req.statement_start_offset / 2, CASE req.statement_end_offset WHEN -1 THEN LEN(sqltext.[text]) ELSE (req.statement_end_offset - req.statement_start_offset) / 2 END)
Remove the SUBSTRING (your current last field). If you look closely at the SUBSTRING I am suggesting directly above, it is essentially what you had at the end there, expect it corrects for two mistakes:

Your SUBSTRING did not account for when there was only 1 query in the batch, in which case statement_end_offset is -1 and your substring gets an error
Your SUBSTRING did not account for the translation of double-byte byte count into character count.

But I digress.


Restore SQL Server DB encrypted by EKM - where's the asymmetric key? MS is clear about restoring TDE backups: Certificate has to be restored on destination OR, in case of EKM, the asymmetric key

"
When moving a TDE protected database, you must also move the certificate or [[asymmetric key]] that is used to open the DEK. The certificate or asymmetric key must be installed in the master database of the destination server, so that SQL Server can access the database files
"

Now, there is no asymmetric backup built-in SQL Server. We might assume that the EK can export it or so?? we can restore Asym keys created as strong name files but the create statement of Asym key with EKM does not look to support a source file

CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE

FROM Provider [SafeNetLunaHSM]

WITH ALGORITHM = RSA_2048,

PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key_TDE',

CREATION_DISPOSITION=CREATE_NEW

I am using Safenet Luna HSM. Has anyone been in a similar situation? simply restoring a TDE backup from EKM source to Native TDE destination.

Appreciate the help

If you're using an HSM, there would be no point to export the key in a situation like this. The main point of an HSM is to keep the keys safe, among various other things - so you really don't want to export the key.

First, I'm going to assume that on the server you want to restore this database to you've created the credential used to access the HSM and I'm going to assume you've allowed the addresses of the new server to talk to the HSM, and all of the other assumptions such as load the EKM DLL, etc. In that case, rather than specifying CREATION_DISPOSITION = CREATE_NEW you'll want to use CREATION_DISPOSITION = OPEN_EXISTING which is part of the CREATE ASYMMETRIC KEY T-SQL.


Cannot create PostgreSQL user I'm using PostrgreSQL 9.1.9 on Ubuntu 13.04.

Using the following StackOverflow question, I tried to create a user/role in postgressql:

giladnaaman@giladnaaman-Lenovo-G550:~/Programming/Eclipse/Hephaestus$ createuser --version
createuser (PostgreSQL) 9.1.9

giladnaaman@giladnaaman-Lenovo-G550:~/Programming/Eclipse/Hephaestus$ createuser f4
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y
createuser: could not connect to database postgres: FATAL:  role "giladnaaman" does not exist

How can I solve this problem?

You are trying to connect to the database as your user but that use doesn't exists. The best way to execute administrative commands on PostgreSQL is to issue them as the "postgres" user. Change user with:

sudo su -  postgres

The user postgres exists and has administrative privileges (it is a superuser) so can connect the database and create new users (just do a createuser after the sudo).


Scripting a Maintenance Plan in SQL Server Express How would I run each aspect of Ola Halengren's sql maintenance plan on SQL Express?

It's my understanding that each job should be run in a specific order.  I'm sure that a .bat or cmd file can do this, but not sure where/how to start. SQL Express does not show the Server Agent, so the jobs do not display.

Can I view them another way and still use the scheduler, or would I have to script those? Would I have to run that from Task Scheduler, or can the SQL Agent scheduler handle this? Any help/direction you could provide is greatly appreciated.

On a side note, I'm also wondering if jobs can be scheduled to run after another job completes, as opposed to specifying a time to run the job?

You've pretty much self answered here - you won't be able to rely on SQL Server to do the scheduling for you.

The most common method is to use Windows Task Scheduler, but if you have any other job scheduling application already that might be a better choice. Either way, you'll need to get comfortable with the sqlcmd utility if you aren't yet.

If I were to use Task Scheduler, I would personally chain any dependencies together in .bat files, assuming your maintenance needs are fairly simple since its an Express instance.

I've got a website post with all of the specific steps to setting up Ola on Task Scheduler if that is the route you decide to pursue. There is also a Microsoft KB with similar (though pictureless) instructions.


How to export my query output to CSV in SSMS I have some outputs in SQL Server Management Studio, but want to export them to CSV for easy transmission to another db. I have seen command to export to xml but not CSV. Thanks Abdulmojeed

If you have a working R-in-Database (installation option of SQL-Server) and you need to do this job to be done programatically you can use R. Use this way if you try to export your data to a final sink (office for the enduser) or to use some extra functionality when exproting (graphic, computation...)

If you hava a working SSIS-environment (data-tools installaled) you can export data most easily with data flow task. You can produce this packages with SSMS and the Export data assistant - at least the first version.

If you want to have direct access to the other database: Don't use CSV. Use a driver to connect to both databases and use SSIS or any other tool to write from database to database using drivers (ODBC, JDBC, OLE DB).

If you don't have any other possibility and really need CSV: Don't write your own "export script". Ctrl+A, Ctrl+C does not do the job properly. There are lot of issues to keep in mind: date formats, escape sequences, separators, end of lines...

And: Alwaye test in advance. And test early&everything. Don't start by exporting GBs of data and sending this anywhere. This will most certainly not work.


Prepared statement execution with variable number of parameters to be bound Some stored procedures I work with need to interpolate WHERE criteria based on if procedure input parameters have been supplied. To avoid potential injection points, I'd like to utilize parameter binding for the values that are to be part of the interpolated criteria.

Since the criteria added to the prepared statement and thus the number of parameters to be bound may differ depending on the user input, I devised the method below to determine which variables will be passed to the EXECUTE statement. This works, but it seems inelegant.

CREATE PROCEDURE foo (IN mandatory INT, IN optional INT, IN optional2 VARCHAR(20))
  BEGIN

    SELECT
      0, '', '', mandatory, optional, optional2
    INTO
      @params, @sql, @where, @m, @o1, @o2;

    IF (@o1 > '' AND @o1 IS NOT NULL) THEN
      SET @where = CONCAT(@where, ' AND field = ?');
      SET @params = @params + 1;
    END IF;
    IF (@o2 > '' AND @o2 IS NOT NULL) THEN
      SET @where = CONCAT(@where, ' AND field2 = ?');
      SET @params = @params + 3;
    END IF;

    SET @sql = CONCAT('
      SELECT id, bar FROM table
      WHERE
        baz = ?
        ', @where
    );
    PREPARE STMT FROM @sql;
    CASE @params
      WHEN 0 THEN EXECUTE STMT USING @m;
      WHEN 1 THEN EXECUTE STMT USING @m, @o1;
      WHEN 3 THEN EXECUTE STMT USING @m, @o2;
      WHEN 4 THEN EXECUTE STMT USING @m, @o1, @o2;
    END CASE;
    DEALLOCATE PREPARE STMT;

  END$$

I'm aware of alternatives:

The binaries that would call these stored procedures have a function that attempts to identify potential SQL injection by passing the user supplied strings through a regular expression.
A user-defined function could be used to dynamically construct the EXECUTE statement given a dynamic number of inputs.

However, I was wondering if anyone else has ran into this desire to handle dynamic construction of an EXECUTE statement purely with SQL.

At least in this example, there's an easier approach than this. Remember that the optimizer is always trying to plan the query execution in a way that the path involving least amount of work necessary to retrieve a valid result set will be the path chosen.

SELECT * FROM t1 WHERE (col1 = 'foo') OR (1 = 1);

The server will always return all of the rows, because 1 = 1 contains all the truthiness required to conclude "yes, this row matches the WHERE clause"... and it will not bother scanning the values in col1 to see if they contain 'foo' because the optimizer has found a more obvious, simpler, "lower cost" approach... (any expression) OR (TRUE) is always true... and the shortest path to truth is the one the optimizer is supposed to find and take.  It doesn't need to evaluate whether 1 = 1 row-by-row, since that's a constant expression.

You don't even need a prepared statement, just a query in the procedure:

SELECT id, bar
  FROM table
 WHERE baz = mandatory
   AND (optional IS NULL OR field = optional)
   AND (optional2 IS NULL OR field2 = optional2);

If the program variable "optional" is null, the optimizer realizes that entire expression (in parentheses on that line) will be true for each row, no matter what, since, in the context of the running query, "optional" is a constant value, and optional is NULL is always going to be true, just like 1 = 1 is always true.  The optimizer realizes that this also is a constant expression.  The contents of "field" never need be evaluated, because the OR expression has been determined in advance to be true.  The same goes for optional2.

On the flip side, if "optional" is not null, then optional IS NULL gets optimized away, since it's impossible for that expression to be true, and you are left with AND (field = optional).  Same, again, for "optional2."

The optimizer is left, in each case, with query that can still use indexes as appropriate.

Since the query is in the procedure body, not a prepared statement, SQL injection is impossible since the server has no chance of blurring the distinction between data in variables and the literal query.


Configure database mirroring for multiple databases via TSQL or Powershell when mirror is already restoring We are trying to automate the mirroring set up for multiple databases whose mirror is already in RESTORING state as the set up was part of log shipping

So when we switch from log shipping to mirror I am unable to find a way using TSQL or Powershell to set this mirroring between Primary and Secondary as most of the scripts on the internet are taking full backups initially and restoring. But we don't need to implement that as DB is already restoring.

We can set this up using GUI but it's time consuming when doing so for 20 DBs during migration. Please help with any mirroring automation that will set up for multiple databases having their mirror already set up with databases in restoring mode.

Run the Alter Database command to add database to mirroring session

--Run on Mirror
ALTER DATABASE Test_DB
    SET PARTNER = 'TCP://Mirror_Me:5022'

Run on both mirror and principal. Remember to change the port numbers appropriately

--Run on Principal
ALTER DATABASE Test_DB
    SET PARTNER = 'TCP://Mirror_Me:5023'

Read this for more info.

Adding Additional script for a complete configuration minus the backup and restore process:

--This script allows db mirroring to be setup iwth a witness.
--Edited to skip the backup and restore process to the mirror
--Make right configuraton changes accordingly

--On A server
    CREATE ENDPOINT [Endpoint_Mirroring] --change endpoint name as required
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022)  --change port number as required. default is 5022
    FOR DATA_MIRRORING
    (ROLE = ALL);

    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [domain\login_name] --change to required domain account

--On B server
    CREATE ENDPOINT [Endpoint_Mirroring] --change endpoint name as required (good to keep the same name)
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022) --change port number as required.
    FOR DATA_MIRRORING
    (ROLE = ALL);

    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [domain\login_name] --change to required domain account

--Change for current db
    ALTER DATABASE TEST_DB SET PARTNER = 'TCP://servername.domainname.net:5022'  --change the database/server name
    GO

--On B server
    ALTER DATABASE TEST_DB SET PARTNER = 'TCP://servername.domainname.net:5022' --change the database/server name
    GO

--To setup Witness on Server C
    CREATE ENDPOINT [Endpoint_Mirroring] --change endpoint name as required (good to keep the same name)
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022) --change port number as required.
    FOR DATA_MIRRORING
    (ROLE = ALL);

    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [domain\login_name]

--On Principal Server
    ALTER DATABASE Test_DB SET WITNESS = 'TCP://servername.domainname.net:5022' --change the database/server name
    GO

NOTE: This does not contain setting up certificates for encrypted connections on the endpoints/mirroring. It does not hand transfer of logins from principal to mirror. This is outside the scope of this question.

DISCLAIMER: This script is AS-IS and should be thoroughly tested before implementing into a production environment.


How to set user-level security in 2007+ MS Access? I've read this MS info which should cover my problem. There are quite clear steps:

  Open the .mdb or .mde file that you want to help protect.
  On the Database Tools tab, in the Administer group, click the arrow below Users and Permissions, and then click User-Level Security Wizard.

The problem: I don't see "Users and Permissions" option in the "Administer" group. So I can't proceed. (I use Access 2013, which I don't think that's a problem here.)

In the site, it also says that "you must also use the /WRKGP command-line switch to point to your workgroup information file when you start Access".

And I did exactly that (which should be correct according to this):

"msaccess.exe" "C:\Temp\testDB.mdb" /wrkgrp "C:\Users\ZygD\AppData\Roaming\Microsoft\Access\System1.mdw"

The file loads, I get no error. Any thoughts? Anything I'm missing?

So, the problem is that I cannot proceed in creating user-level-security. This is a step towards creating separate user accounts for the Access DB file. Initially there is only one Admin account, and it's quite a challenge to create other accounts in new versions of Access...

As I was stuck in not seeing "User-Level Security Wizard", I made my way to access it by including this item to the custom ribbon tab:

After that I was able to continue and create users for the DB.


Does granting SA rights to a user in SQL compromise the OS? I'm setting up a server that my company will be managing for one of our clients. This server runs an application that is managed by a 3rd party. We are responsible for the server and OS and the 3rd-party is responsible for the application and the MS SQL databases. I'm wondering if the 3rd party is given SA rights to SQL if this will allow them any sort of control over the OS side. SQL is not my forte. We would be using Server 2008 R2 with MS SQL Server 2008.

Thanks.

sysadmin rights allow the use of things like xp_cmdshell and the sp_OA% stored procs which can affect the underlying OS, but it depends on the service account used

If you limit the service account permissions (eg not local system, not local admin and certainly not domain admin) then it's pretty much OK. The SQL installer limits the permissions of the service account during install, of course this assumes you don't escalate permissions yourself.


SQL server database backup - Destination Disk - Adding multiple files - does it duplicate or split backup into the files? When we do full data backup (using SSMS UI), at the bottom of the window we have the option to specify the destination as Disk and also to add multiple files.

My question is - does adding multiple files create duplicate copies of the full backup? or does it create a split backup - that is split the full backup into the specified files?

This book suggests it does a duplication where as this link suggests that it does a split. Please can someone clarify.

Don't rely on the behaviour of SSMS, which has historically been buggy or poorly documented and also sometimes changes from version to version.

The easiest way to be sure is to click the "script" button on top of the dialog and compare the output to the documentation.

For example when you click the script button after adding several files in the SSMS version that comes with SQL Server 2014 like this:

You can see the exact script that will be used is this:

BACKUP DATABASE [mydatabase] TO
DISK = N'G:\location\file1.bak',
DISK = N'G:\location\file2.bak' WITH NOFORMAT, NOINIT,
NAME = N'databasename-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

If you then refer to the documentation about backups you will see that this script creates a striped backup.

A mirrored backup would need the MIRROR TO clause like this

BACKUP DATABASE [mydatabase] TO
DISK = N'G:\location\file1.bak'
MIRROR TO DISK = N'G:\location\file2.bak' ;
GO

I don't know which version of SSMS your book is talking about as it's unavailable to me, but the bottom line is don't trust SSMS, use T-SQL and refer to the documentation.


Why would SQL Server stop excluding partitions appropriately? I have a Fact Table that will run with an appropriate plan during our morning processing. Occasionally, later in the day, if I go to query it, it will have a really strange plan.

When querying a table partitioned on date, you usually see a seek predicate like so:

But for some reason, it turns into this:

Yes, I'm aware that there are two different tables shown above. I pulled the good example from a similarly configured table, and it appropriately displays the seek predicate I would expect.

Both of these are Clustered Index Seek operators. Updating stats doesn't seem to help, but some part of Ola's maintenance scripts seem to fix it each morning.

Has anybody seen this before? Any tips on how to restore the good plan?

In the second execution plan, the seek predicate has a CONVERT_IMPLICIT function in it. This means the wrong data type was input and SQL Server has to convert the data type to something else. In this case, it was expecting a DATE data type and something other than DATE was passed in... most likely a (VAR)CHAR or N(VAR)CHAR value. The fix would be to either explicitly cast/convert the value before submitting the query or fix the code to use the proper data type.

Here are some further resources on the matter:

Implicit_Conversions
Conversion Matrix
Performance Issues with Convert_Implicit


Generate script for SQL Broker objects I'm using SQL Server Management Studio to generate scripts for all the objects in an existing database:

but the objects related to SQL Broker (messages, queues and services) are not generated.

I've fiddled with the advanced options but still can't get them out.
Is there a way to do it?

Even in Management Studio that ships with SQL Server 2012 (BTW please stop using SSMS 2005 to manage 2008+ instances), the Generate Scripts option will not offer Service Broker objects, whether you select "script all" or select individual objects. You'll need to use other methods - SMO, PowerShell, or right-clicking within the Service Broker node of Object Explorer and scripting items individually. You can speed this up a bit using Object Explorer Details (in the right-hand pane you can select multiple objects and script them), but this is still one operation per object type.

PS this was complained about years ago, but sadly, closed as Won't Fix.

http://connect.microsoft.com/SQLServer/feedback/details/515831/generate-sql-scripts-wizard-missing-object-types

On the plus side, David is right below: SqlPackage.exe will definitely script out service broker objects (it just does it in the wrong order). See my answer to a more recent question here:

How to script all the service broker elements for each database it is on, in the current server?


PostgreSQL trigger function that selects from %current% table I have multiple tables that have the same column names, they only vary in their column values, like:

tbl_log_a
tbl_log_b
tbl_log_c
...

26 tables from a to z. Each table has a trigger that calls a trigger function which does the exact same thing:

SELECT columnname FROM tbl_log_a

Other than that, all my trigger functions do the exact same thing. They differ in that they:

select columnname FROM tbl_log_a
select columnname FROM tbl_log_b
select columnname FROM tbl_log_c
...

So I have to create 26 trigger functions, one for each tbl_log_%letter%. Is there a way to tell the trigger function to:

SELECT columnname FROM %currenttable%

By %currenttable% I mean the table where the trigger is placed. Or:

SELECT columnname FROM tbl_log_%letter%

Is it possible in Postgres 9.1? I'm reading about dynamically determined tables. Any clue? I would like to store the table name itself inside a variable, not the columns inside that table, because the trigger function works on multiple columns inside that table.

TG_TABLE_NAME
TG_TABLE_SCHEMA

I suggested that you use trigger arguments, but it's actually not necessary. You can use the automatic variables TG_TABLE_SCHEMA and TG_TABLE_NAME, or use TG_RELID. These, alongside EXECUTE for dynamic SQL, let you do what you want:

BEGIN
    EXECUTE format('SELECT colname FROM %I', TG_RELID)
END;

or

BEGIN
    EXECUTE format('SELECT colname FROM %I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME)
END;

(Of course these won't work as-is, since the SELECT has no destination for the data. You have to use EXECUTE format(..) INTO ... to store the result into a DECLAREd variable), e.g.

DECLARE
    _colvar integer;
BEGIN
    EXECUTE format('SELECT colname FROM %I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME) INTO _colvar;
    RAISE NOTICE 'colname value was %',_colvar;
END;


Create/Alter stored procedure without compiling Is it possible to create or alter a stored procedure in SQL Server 2005* that doesn't compile the procedure?

(* - yes, unfortunately, we still need to support 2005 as our minimum standard)

I have a situation where it would be exceedingly useful to be able to create/alter a stored procedure that is syntactically valid but not semantically valid (due to incorrect table/column names)... which I can then alter accordingly within SSMS.

I've just had a thought, and it's a pretty simple solution... comment out the entire contents of the stored procedure when it's being altered/created.

Using Ctrl+K Ctrl+C will comment out each line individually - which would be better than using /* */ around the entire thing, as that will fail if there are any instances of /* */ in the stored procedure already.

I would still be very interested to hear if there is an actual way to tell SSMS to ignore invalid table/view/column names.

Thanks to @marcelo for his answer, and it does appear that using the name of a non-existent table will allow you to create the stored procedure.

However, it does not allow you to create a stored procedure if you use a non-existent column in an existing table

What you are referring to is deferred name resolution and is further explained here Deferred Name Resolution and Compilation.

  Deferred name resolution can only be used when you reference
  nonexistent table objects. All other objects must exist at the time
  the stored procedure is created. For example, when you reference an
  existing table in a stored procedure you cannot list nonexistent
  columns for that table.

Basically, no you are not able to alter the parsers rules for what objects must exist at parse and compile time. I've previously had this issue when deploying SPs which reference linked servers and those linked servers did not have a valid connection. To work around it I updated the linked sever logic to be dynamic SQL.


PostgreSQL function to insert to table from another table has different structure I have the following table with five columns representing locations and their status each quarter in a year.

-- id, location_id, status, year, quarter
CREATE TABLE foo AS
SELECT * FROM ( VALUES
(1, 12, 1, 2014, 3),
(2, 12, 1, 2014, 4),
(3, 13, 0, 2015, 1),
(4, 13, 0, 2015, 2),
(5, 13, 1, 2015, 3),
(6, 13, 0, 2015, 4),
(7, 12, 1, 2015, 1),
(8, 12, 1, 2015, 2)
) AS t(id,location_id,status,year,quarter);

I want to create a function that will extract data from the table based on passed parameters and store them in a new table that has different structure.

I created this function that will receive 5 parameters,

the selected year
quarter 1 (1 to select, 0 to ignore)
quarter 2 (1 to select, 0 to ignore)
quarter 3 (1 to select, 0 to ignore)
quarter 4 (1 to select, 0 to ignore)

For example:

select get_info(2015,1,0,0,1)

This option will select id_ location and status where quarter = 1 and 4 only. This is the function

CREATE or REPLACE FUNCTION  get_info(
get_year int,
q1 int,
q2 int,
q3 int,
q4 int)
RETURNS VOID
AS $$
DECLARE
BEGIN
   CREATE TABLE IF NOT EXISTS tbl_temp
   ( parcelnum text,
    quart1  int,
    quart2  int,
    quart3  int,
    quart4  int);
[ I have prolem with this part:
INSERT INTO  tbl_calc_temp( parcelnum text,quart1  int,
quart2  int,quart3      int, quart4  int  )
Valuse (select some_thing from foo where quarter = 1 or 4)
]
END; $$
LANGUAGE PLPGSQL;

This function will create a table tbl_temp,
then it should select rows from table foo based on based parameter then insert them to the table tbl_temp. For the quarter was not select, it values will be null .

Please help me writing the part I have problem with.

Note:
The new table tbl_temp should have the following structure:

 CREATE TABLE tbl_temp
   ( parcelnum text,
     year   int,
    quart1  int,
    quart2  int,
    quart3  int,
    quart4  int);

I want to use:

INSERT INTO table(parcelnum,year, quart1 ,quart2, quart3,quart4)
VALUES
(values should be selected from foo and in case a quarter was not select or there is no record for a location in any quarter the value will be null);

You don't actually need a function for this. You can do the same thing you're doing with your sample data with CTAS. I wouldn't use a function anyway. But, if you are here is how you do with a SQL function.

CREATE FUNCTION get_info(year int, quarter int[])
RETURNS int AS $$

  CREATE TEMPORARY TABLE tbl_temp AS
  SELECT *
  FROM foo
  WHERE year = get_info.year
    AND quarter = ANY(get_info.quarter);

  SELECT 0;

$$ LANGUAGE SQL;

Then just use

SELECT get_info(2015, '{1}');
SELECT get_info(2015, '{1,3,4}');

Etc.

Now, if you insist on the syntax given you can do that too.. We'll modify it slightly. PostgreSQL has bools. You should never make use 1 as true, when you actually have true.

CREATE FUNCTION get_info(
  year int,
  q1 bool DEFAULT false,
  q2 bool DEFAULT false,
  q3 bool DEFAULT false,
  q4 bool DEFAULT false
)
RETURNS int AS $$

  CREATE TEMPORARY TABLE tbl_temp AS
  SELECT *
  FROM foo
  WHERE year = get_info.year
    AND (
      (q1 AND quarter = 1)
      OR (q2 AND quarter = 2)
      OR (q3 AND quarter = 3)
      OR (q4 AND quarter = 4)
    );
  SELECT 0;

$$ LANGUAGE SQL;

Using plpgsql for this is way over kill.


Left join including nulls i know this question might be dumb and might be duplicated. but im having a problem with my left join query. so please help Y.Y

the output im currently is something like this

judge name|score
judge1    |87
judge2    |87
judge3    |87
judge4    |87

but the output that i want is with null like something like this

judge name|score
judge1    |87
judge2    |87
judge3    |87
judge4    |87
judge5    |null
judge6    |null

scores are varchar and if table has no record match from table 1 will be automatically should be null or much better for default score of 87.

my query sample

SELECT j.judge_name,s.score
FROM `tbl_judges` j
LEFT JOIN tbl_scoring s
    on j.id = s.jud_id
where s.crit_id = 1
  and s.can_id = 11

if table has no record match from table 1 will be automatically should be null

SELECT j.judge_name,
       s.score
FROM tbl_judges j
LEFT JOIN tbl_scoring s
       ON j.id = s.jud_id
      AND s.crit_id = 1
      AND s.can_id = 11

  or much better for default score of 87

SELECT j.judge_name,
       COALESCE(s.score, 87) score
FROM tbl_judges j
LEFT JOIN tbl_scoring s
       ON j.id = s.jud_id
      AND s.crit_id = 1
      AND s.can_id = 11


Postgres: SELECT with ORDER BY is 4x slower when using index I have a table with 4 columns:

id SERIAL PRIMARY KEY
dt DATE
val bigint  -- Already has a btree index
txt VARCHAR(64)

I am running a query:

EXPLAIN ANALYSE SELECT dt, val FROM temp_test ORDER BY dt;

This was taking ~13ms and below is the output of EXPLAIN ANALYZE

Sort  (cost=2862269.49..2912269.54 rows=20000020 width=12) (actual time=10561.801..12214.518 rows=20000020 loops=1)
  Sort Key: dt
  Sort Method: external merge  Disk: 508816kB
  ->  Seq Scan on temp_test  (cost=0.00..436917.25 rows=20000020 width=12) (actual time=0.016..2451.364 rows=20000020 loops=1)
Planning time: 0.127 ms
Execution time: 12874.978 ms

I presumed that creating an index on dt will speed things up and I did

CREATE INDEX temp_test_dt_btree ON temp_test(dt);

Once index was created, I executed the same query again:

EXPLAIN ANALYSE SELECT dt, val FROM temp_test ORDER BY dt;

This one took ~54s which is around 4x more than one without index with the following output for EXPLAIN ANALYSE

Index Scan using temp_test_dt_btree on temp_test  (cost=0.44..1317015.07 rows=20000020 width=12) (actual time=0.049..53027.137 rows=20000020 loops=1)
Planning time: 0.241 ms
Execution time: 53742.120 ms

I am a little confounded as I expected the index to improve the performance at best and have little (or no) impact at worst.

I am running:

PostgreSQL 10.11 (Ubuntu 10.11-1.pgdg18.04+1)

If your table is well-vacuumed, you can get an index only scan by creating the index:

CREATE INDEX ON temp_test(dt,val);

This might be quite a bit faster as it doesn't need to visit the table at all. (In my test, it was about 2.5 times faster than the sort.)

As to why it chooses the wrong plan with the index you already have, it could just be that you have an effective_cache_size that is set too high for the amount of RAM actually available to it.  You would have to tell us more about your settings and you hardware.

I've replicated your finding in the absence of the index-only-scan index, with the planner choosing the much-slower ordered index scan over the seq scan.  I replicated it on AWS m5.large machine, using Ubuntu 18.04, with PostgreSQL 10 from the ubuntu repository, and using the default settings for both OS and PostgreSQL, other than setting track_io_timing on and max_parallel_workers_per_gather=0; setting it up like this:

create table temp_test (
  id SERIAL PRIMARY KEY,
  dt DATE,
  val bigint,
  txt VARCHAR(64)
);
insert into temp_test select x, now()+interval '10 years'*random(), random()*1000000000, random()::text from generate_series(1,20000000) f(x);
vacuum ANALYZE temp_test ;

With the default shared_buffers of 128MB, the ordered index scan is preferred by the planner, but is slow as you indicate, even if all data is in memory (vmstat shows no read/write activity during the query).  But if shared_buffers is increased so that it holds all the data, then the ordered index scan becomes much faster (still slower than the sort, but not by nearly as much).

So the main problem is that it is slow to read data from the filesystem cache into shared_buffers, and this is not accounted for by the planner.  The code that implements effective_cache_size assumes that reading a page which is already in memory is absolutely free.  The reality is that while it is much cheaper to read a page from the filesystem cache than it is to read it from disk, it is still quite expense relative to, say, a cpu_tuple_cost.  So "free" is not nearly accurate.

The planner does not expose any knobs you can twist to make it account for the residual cost of reading a page from the filesystem cache, such that it does not appear to be free.  And it would not be easy to add such a knob, because to do so accurately you would need to distinguish between how many pages will be re-found in shared_buffers, from how many will be re-found in the filesystem cache but not in share_buffers.  The current 'Mackert and Lohman formula' doesn't make such a distinction and it is not clear to me how to make it do so.


How to find an text in entire Oracle database? I have a Oracle database (11g2). But i don't know how to find a text in all of table on a database. Because there are so many tables, can't find down by one column, one table... it takes me a lot of times.
Is there any way to do this ? Or any tools, software can help me to do this ?
Please help me! Thank in advance.

See https://lalitkumarb.wordpress.com/2015/01/06/sql-to-search-for-a-value-in-all-columns-of-all-atbles-in-an-entire-schema. In short (with my slightly modified version of the code):

SELECT DISTINCT table_name, column_name
  FROM user_tab_cols,
  TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
  || column_name
  || ' from '
  || table_name
  || ' where upper('
  || column_name
  || ') like upper(''%'
  || '&searchword'
  || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
  where table_name not in (
    select distinct table_name
      from user_tab_cols where data_type like 'SDO%'
      or data_type like '%LOB')
  order by table_name, column_name;

I exclude tables that contain LOB or spatial columns as they can fail. Add other filters as you please. This will be very expensive to run in a large database!

EDIT: &searchword is the text you are searching for, of course.


How to increment identity without add a row and get the skipped value? I'm trying to preserve value in identity column in manner to be added later. The value should be used in implicit foreign key when there is no constraint.
For example:
I have this table:
Create table [t](
   [id] [int] IDENTITY(1,1) NOT NULL,
   [value] [varchar](255) Not NULL
)

I want to increment the id without adding line, and get the id, (similar to oracle sequence)
I'm trying to read current sequence value:
Select IDENT_CURRENT('t') as id

result:
id
----
1

trying to increment, use insert with default values:
insert into t default values

result:

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'value', table test.dbo.t; column does not allow nulls. INSERT fails.
The statement has been terminated.

I'm trying to read current sequence value:
Select IDENT_CURRENT('t') as id

result:
id
----
2

Means the sequence increment had been succeeded, even though no row was added.
Now I need a way to read the skipped value in a "scope safe" way.
SCOPE_IDENTITY and @@IDENTITY doesn't been changed unless the insertion was succeeded, and IDENT_CURRENT('t') is not "scope safe".
Does anyone have any idea how can I do so?
I prefer a solution that I can use for any table, not limited for a specific table (for specific table: like insert full row and delete it - I need to know the column names).

You could use DBCC CHECKIDENT (RESEED) for this purpose, but keep in mind you need special permissions, and I'm not sure it worth.

  Permissions
  Caller must own the schema that contains the table, or be
  a member of the sysadmin fixed server role, the db_owner fixed
  database role, or the db_ddladmin fixed database role.

CREATE TABLE tbl (foo int IDENTITY, bar int);

INSERT INTO tbl (bar) VALUES (10),(15),(20);

SELECT * FROM tbl;

foo         bar
----------- -----------
1           10
2           15
3           20

/* save current identity value */
DECLARE @CURRENT_ID int;
SELECT @CURRENT_ID = IDENT_CURRENT('tbl');
PRINT 'Current ID = ' + CAST(@CURRENT_ID as varchar(10));

Current ID = 3

/* get next identity value */
DECLARE @NEXT_ID int;
SET @NEXT_ID = @CURRENT_ID + 1;

/* reseed */
DBCC CHECKIDENT (tbl, RESEED, @NEXT_ID);

SELECT * FROM tbl;

foo         bar
----------- -----------
1           10
2           15
3           20
5           30
6           35
7           40

DROP TABLE tbl;


Is there a way to actually limit InnoDB data dictionary? I would like downgrade AWS instance to save the costs however due to the number of tables and workload (logical backups) InnoDB data dictionary grows extremely large.

mysql> SELECT COUNT(*) FROM information_schema.INNODB_SYS_TABLES;
+----------+
| COUNT(*) |
+----------+
|  1020034 |
+----------+
1 row in set (4.61 sec)

mysql> SELECT COUNT(*) FROM information_schema.INNODB_SYS_INDEXES;
+----------+
| COUNT(*) |
+----------+
|  2628181 |
+----------+
1 row in set (4.99 sec)

-

mysql> SHOW GLOBAL VARIABLES LIKE 'table_%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_definition_cache     | 400   |
| table_open_cache           | 1     |
| table_open_cache_instances | 1     |
+----------------------------+-------+
3 rows in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Open%table%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| Open_table_definitions   | 400      |
| Open_tables              | 1        |
| Opened_table_definitions | 2312885  |
| Opened_tables            | 22403462 |
+--------------------------+----------+
4 rows in set (0.00 sec)

-

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_mem_dictionary';
+-----------------------+------------+
| Variable_name         | Value      |
+-----------------------+------------+
| Innodb_mem_dictionary | 4517841711 |
+-----------------------+------------+
1 row in set (0.00 sec)

table_definition_cache

  For InnoDB, table_definition_cache acts as a soft limit for the number
  of open table instances in the InnoDB data dictionary cache. If the
  number of open table instances exceeds the table_definition_cache
  setting, the LRU mechanism begins to mark table instances for eviction
  and eventually removes them from the data dictionary cache.

Any idea why this is not happening? I was wondering if enebling innodb_file_per_table would help in this case.

Some extra info:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 8589934592 |
+-------------------------+------------+
1 row in set (0.00 sec)

mysql> SELECT @@version, @@version_comment;
+--------------------+---------------------------------------------------------------------------------------------------+
| @@version          | @@version_comment                                                                                |
+--------------------+---------------------------------------------------------------------------------------------------+
| 5.6.30-76.3-56-log | Percona XtraDB Cluster (GPL), Release rel76.3, Revision aa929cb, WSREP version 25.16, wsrep_25.16 |
+--------------------+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-

# cat /etc/redhat-release
CentOS Linux release 7.2.1511 (Core)

# free -m
              total        used        free      shared  buff/cache   available
Mem:          14881       14239         326           6         315         378
Swap:          4095        1237        2858

# ps -o %mem,rss,vsize -C mysqld
%MEM   RSS    VSZ
94.2 14364148 16262404

1 million tables ? !!  That, alone, is likely to choke MySQL.  What are you doing?

Creating a table for each 'user' or 'company' or whatever?  That has to stop.
Creating lots of partitions in tables?  That is inefficient.

Run this; it might give a clue of whether it is tables, partitions, or what-not:

( select * from information_schema.INNODB_SYS_TABLES
      order by rand() limit 33 ) ORDER BY name;

Patterns of database or table names may be obvious.  Names ending like #p#p200805 are partition names.
If it's tables or databases, let's discuss how to do that.  Please provide some hints of what your tables are.  Pending that, I will provide some generic suggestions.

Instead of having one-table-per-entity, have one table.  In it have an id that indicates which entity is involved.  ("Entity" is "user", "company", or whatever you are creating new tables based on.)

Then, to get away from one-table-per, you will need a lot of disk space and downtime to copy the million tables into a handful, adding the id as you go.

If it's partitions, let's discuss that.  More than about 50 partitions in a single table leads to inefficiencies.  Tables with fewer than a million rows should not (in general) be partitioned.  Please provide SHOW CREATE TABLE, since there are many possible issues.  I give some answers here .

Caveat:  Much of what I have said will be invalid with version 8.0 and it's integrated Data Dictionary.  Lots of databases or tables or partitions is not much of a problem.


Duplicate records on joining I have two tables books and books_data. books can have multiple books_data, books_data can have only one book. books_data table has date column by which I want to sort the books table. When trying to do this, the code returns duplicates. But the duplicates appear only when trying to paginate results, e.g. if I provide LIMIT 10 OFFSET 0 for first query and LIMIT 10 OFFSET 20 for the second, some of the records from first query will appear in the second query too. If I specify LIMIT 20 OFFSET 0 there are no duplicates. Here's my query (it may be not perfect, because it is generated by Rails ORM Active Record):

SELECT DISTINCT "books"."id", "books_data"."date" FROM "books"
LEFT OUTER JOIN "books_data" ON "books_data"."book_id" = "books"."id"
ORDER BY books_data.date DESC LIMIT 10 OFFSET 0;

Try adding books.id to ORDER BY. If books_data.date is not unique, relative order of rows in resultest is not guaranteed. Say you have 5 rows with date1, 10 rows with date2, and 5 with date3; date1 < date2 < date3.

Then some rows with date2 may appear on both pages, some on neither, and , if you are lucky enough, you may sometimes see expected results.
Assuming (books.id,books_data.date ) unique the following should return consistent results:

SELECT DISTINCT "books"."id", "books_data"."date" FROM "books"
LEFT OUTER JOIN "books_data" ON "books_data"."book_id" = "books"."id"
ORDER BY books_data.date DESC, books.id ASC LIMIT 10 OFFSET 0;

Short illustration :

id | some_non_unique_data
1  |  'val1'
2  |  'val2'
3  |  'val2'

When splitting such data into 2 pages, 2 rows per page ordering only by some_non_unique_data ascending the engine is free to return row id=2(or id=3) on any page. emphasized text


SQL Server Agent Alerts and Severity Errors I've setup alerts in SQL Agent for errors of severity level 15-16 for testing, and found out that these are not always triggered
To be more exact, these seem to trigger only when given error number has is_event_logged = 1 in sys.messages
This means when below commands are run:
create table  -- Msg 102,  Level 15, State 1  -- Incorrect syntax near 'table'
select 1/0    -- Msg 8134, Level 16, State 1  -- Divide by zero error encountered

Those errors (102, 8134) have is_event_logged = 0 and are not written to application log -> alerts for severity errors level 15-16 do not fire
When I try generate error severity 15 or 16, that has is_event_logged = 1, alerts fire just fine
raiserror(18054,16,1)
raiserror(15716,15,1)

Same with severity 17-25 errors, only ones that have is_event_logged = 1, can fire an alert
Questions:

It seems not severity level of error, but actually is_event_logged column controls whether or not error can trigger an alert that looks for severity level errors ?
Is this intended behavior in SQL Server ?

If I need to create custom error message as an alert for an application, using sp_addmessage procedure, and later setup an Alert for this error message,
@severity parameter does not really matter, I need to use @with_log parameter ?

Is there any way to make ALL errors of given severity levels (15,16,17-25) to fire an alert, regardless of is_event_logged equals to 1 or 0 ?
I am afraid to miss something important, because there are a lot of 17-25 severity errors that are not logged to app.log -> don't fire an alert email ?

Yes, this is working as designed.
Looking at the docs:

SQL Server Agent reads the application log and compares events written there to alerts that you have defined.

The mechanism that is used for SQL Agent alerting is to read the log. Thus, if it's not logged, there is no alert.
Instead, you can use Extended Events to capture errors, even if they aren't logged. You can then write your own process to read the Extended Events file and send alerts as necessary.


How to document an archived database? You've stored the backup file in a safe place, but what kind of info do you store with it?

database type (SQL Server)
compatibility level (SQL Server 2008)
application name
application owner
application version

And ...?

I have designed  a database archiving process - which is a semi automated process.

Below are my thoughts :

Note that you have to develop your custom solution based on your organizational needs

Create a server level trigger to track DROP database activity and log that activity in a table. Now if you are talking about only backup files, then you can do the same without a trigger once the backup is finished to update the table with relevant info.
Below is the possible table structure with columns

ID => this will be identity column
TRIGGER_NAME => the name of trigger that got fired in response to the event
COMMENT => Friendly comment for the command that got issues e.g drop database was issued
EVENT_TYPE => the event type that occured e.g. DROP_DATABASE
OCCURED_DATE => the datetime when the event occured
SERVER_NAME => the host name
SERVER_INSTANCE_NAME => sql server instance name
PATCH_LEVEL => version info from serverproperty or you can just use @@version
LOGIN_NAME => Login that issued drop command
DATABASE_NAME => The database that was dropped
DATABASE_VERSION => If sql 2005, 2008, etc or you can use the compatibility level as well
COMMAND_TEXT => The command that was used for the event e.g. drop database test_drop
TYPE => I use this to differentiate between if the backup stored will be on DVD (if small) or TAPE. This is manual bit to be updated later.
TRACKING_NO => If a tracking number is assigned or a request number can be used e.g. JIRA request or HelpStar request or a TFS request number. This is manual bit to be updated later.
APPLICATION_VERISON => If your database is storing application specific version number e.g. BUILD_NUMBER or database schema version.
NOTIFIED => This will be default to 0 and will be updated to 1 once the DBA team is notified by an email. Once it is set to 1, the next time the job runs, it will just send out new records.
You can add more if needed !!

The key here is EVENTDATA() which has a lot of information.

Remember that triggers (whatsoever) cannot be used for backups. For backups, you have to tailor your own custom solution, but the idea remains the same.


Database design -- companies, employees, individuals I'm having troubles modeling this database schema, will appreciate any help. Here is the requirements and description.

A user on registration creates his profile.
User can be an individual or a company, which is selected on registration.
User of type company can have multiple employee profiles.
All profiles have some data in common, like name, location, images, phones.
Company profiles have additional details like company type.
Humans (individuals & employees) share some additional details like age, gender.
Businesses (individuals & companies) also share some common details like work schedule, pricing.
Most frequent queries would be:

showing all profiles (company & individual & employee);
filtering profiles by selected types (company and/or individual and/or employee);
filtering all profiles by common details; filtering all profiles of selected type (all humans, ind. humans, employees, companies) by the type's details.

Profile of any type can be created by an admin, w/o linking it to a user.
One user has only one profile.
Only company profile can have multiple employee profiles.
An employee can't work for multiple companies.
An employee can't be the same person as some individual.

I want to normalize this as much as possible and then possibly denormalize something to improve usability and speed.

Database vendor preferably lowest common denominator – MySQL, possibly PostgreSQL, if it suits this task better.

My initial schema (I think it sucks, because it doesn't enforce many of the constraints and badly expresses the domain):

Read up on Table Inheritance.

Here's your data model in pseudo-syntax. : means inherits. Indentation means that thing is a property of the type.

AbstractParty
  BirthDate -- companies have "created" dates too
  Name

Individual : AbstractParty
  Gender

Organization : AbstractParty
  CompanyType

AbstractPartyRelationship
  FromAbstractParty
  ToAbstractParty

Employment : AbstractPartyRelationship

User
  Username
  Password
  Party (make this a one-to-one relationship)

AbstractAddress

PhoneNumber : AbstractAddress

EmailAddress : AbstractAddress

URL : AbstractAddress

MailingAddress : AbstractAddress

AbstractContactInfo
  AbstractParty
  AbstractAddress

PhoneContactInfo : AbstractContactInfo
  Extension

Picture

PartyPicture
  AbstractParty
  Picture

  An employee can't work for multiple companies.

That's a business rule to be enforced by a trigger, not your data model

  An employee can't be the same person as some individual.

Please elaborate.