AlwaysOn availability group DR failover with Powershell I am setting up AlwaysOn availability group for Disaster Recovery (DR) in the test environment. For AG1, node1 and node2 (synchronous commit/automatic failover) are in DC1; node 3 and node 4 (Asynchronous commit/manual failover) are in DC2 and file share witness is in DC3. When DR event occurs, 2 nodes in DC1 will be gone. Since DR nodes are set up as an Asynchronous commit, AG status is resolving. I will need to failover manually to bring AG group to a synchronized state. I want to use powershell to do failover with data loss. When I run the following powershell to failover, I am getting the error.
Switch-SqlAvailabilityGroup -Path SQLSERVER:\Sql\SQLDRPOCA03\DEFAULT\AvailabilityGroups\SQLDRPOCAG

Switch-SqlAvailabilityGroup : The availability replica for availability group 'SQLDRPOCAG' on this instance of SQL Server cannot become the primary replica. One or more databases are not synchronized or have not joined the availability group.
If the availability replica uses the asynchronous-commit mode, consider performing a forced manual failover (with possible data loss). Otherwise, once all local secondary databases are joined and synchronized, you can perform a planned manual
failover to this secondary replica (without data loss). For more information, see SQL Server Books Online.

Is it possible to use powershell to do manual failover?

I think what you are trying to achieve via powershell is for planned manual failover as mentioned in msdn link.  here

A planned manual failover is supported only when the primary replica
and the target secondary replica are running in synchronous-commit
mode and are currently synchronized. A planned manual failover
preserves all the data in the secondary databases that are joined to
the availability group on the target secondary replica

And what you exactly are trying to achieve " failover between async replicas " is more of forced manual failover with/without data loss which can be achieved via TSQL as mentioned in same msdn link. I don't see PS there but may be someone here who have used can answer. I am not sure but may be you can check dbatools if it provides one
Go through the steps as mentioned laster in that article

Manual failover without data loss
Use this method when the primary replica is available, but you need to
temporarily or permanently change the configuration and change the SQL
Server instance that hosts the primary replica. To avoid potential
data loss, before you issue the manual failover, ensure that the
target secondary replica is up to date.


Force index on a delete UPDATE backup.records FORCE INDEX(IDX_records_deviceId) SET readyToBeDeleted = 1 WHERE deviceId = 226219 AND timestamp <= 1445255546
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76903 page no 7 n bits 872 index `IDX_records_deviceId` of table `backup`.`records` trx id 992346439 lock_mode X waiting

DELETE r.* FROM backup.records r  WHERE readyToBeDeleted = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 76903 page no 7 n bits 872 index `IDX_records_deviceId` of table `backup`.`records` trx id 992346377 lock_mode X locks rec but not gap

I have a persistent deadlock between these two queries. The second query, the delete one, is using the wrong index. I have an index that has only the readyToBeDeleted column.

Is there any way to force an index into a DELETE statement?
If not, could you propose a way to avoid this deadlock?

Table definition
CREATE TABLE `records` (
  `recordId` int(11) NOT NULL AUTO_INCREMENT,
  `deviceId` int(11) DEFAULT NULL,
  `timestamp` bigint(20) DEFAULT NULL,
  `priority` set('SOS','HIGH','NORMAL','GTWFIX') DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `speed` int(11) DEFAULT NULL,
  `hdop` double DEFAULT NULL,
  `sat` int(11) DEFAULT NULL,
  `di1` tinyint(1) DEFAULT NULL,
  `di2` tinyint(1) DEFAULT NULL,
  `di3` tinyint(1) DEFAULT NULL,
  `do1` tinyint(1) DEFAULT NULL,
  `do2` tinyint(1) DEFAULT NULL,
  `movement` tinyint(1) DEFAULT NULL,
  `immobilizer` tinyint(1) DEFAULT NULL,
  `analogInput` int(11) DEFAULT NULL,
  `iButtonId` varchar(20) DEFAULT NULL,
  `usb` tinyint(1) DEFAULT NULL,
  `battery` int(11) DEFAULT NULL,
  `ReportCommand` varchar(10) DEFAULT NULL,
  `reportType` tinyint(1) DEFAULT NULL,
  `sendTime` bigint(20) DEFAULT NULL,
  `state` varchar(4) DEFAULT NULL,
  `engineRPM` int(11) DEFAULT NULL,
  `fuelConsumption` double DEFAULT NULL,
  `temporaryRecordId` int(11) DEFAULT NULL,
  `temporaryRecordAlertId` int(11) DEFAULT NULL,
  `imei` varchar(50) DEFAULT NULL,
  `type` varchar(25) DEFAULT NULL,
  `activeStatus` tinyint(1) DEFAULT NULL,
  `doDebug` tinyint(1) DEFAULT NULL,
  `sim` varchar(50) DEFAULT NULL,
  `processingPriorityRecord` tinyint(1) DEFAULT NULL,
  `receivingIncompleteCommunication` tinyint(1) DEFAULT NULL,
  `lastLogTime` int(11) DEFAULT NULL,
  `lastLatitude` double DEFAULT NULL,
  `lastLongitude` double DEFAULT NULL,
  `SystemId` int(11) DEFAULT NULL,
  `viaGtw` tinyint(4) DEFAULT NULL,
  `readyToBeDeleted` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`recordId`),
  UNIQUE KEY `IDX_records_deviceId` (`deviceId`,`timestamp`),
  KEY `UK_records_Send_time` (`deviceId`,`sendTime`),
  KEY `IDX_records_readyToBeDeleted` (`readyToBeDeleted`,`deviceId`)
) ENGINE=InnoDB AUTO_INCREMENT=118492776 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT

The solution I have found for my problem with the help of @jkavalik was to:

Move the DELETE statement into a DB Event that runs every minute.

CREATE
  DEFINER = 'admin'@'%'
EVENT backup.DeleteProcessedRecords
  ON SCHEDULE EVERY '1' MINUTE
  STARTS '2015-10-20 11:10:18'
  ON COMPLETION PRESERVE
  DO
BEGIN
  DELETE FROM backup.records WHERE readyToBeDeleted = 1 ORDER BY deviceId;
END

add the ORDER BY deviceId to the DELETE Statement.
change the Index IDX_records_readyToBeDeleted from (readyToBeDeleted) to (readyToBeDeleted,deviceId).

Now I am waiting to see if I still get as many deadlock as I had before. I will keep you posted @jkavalik. And once more thank you for your expertise.


Selecting from all databases from multiple tables I've got over 2000 databases that may or may not have the same tables, but in the cases where they do, they should have the same type of information. From one table (settings) I'm trying to get the rows that have the names:

email
client_id
client_name

Then from a second table (Cluster) I'm trying to just add up the number of rows.

I'm stuck at even getting just the email for all of them. Trying to do the following with every single database listed, it tells me it can only join a certain number at once. I'm not opposed to running the command multiple times to eventually get the information, but then it tells me that "name" is too ambiguous.

SELECT *
FROM  `database1`.`settings`,
`database2`.`settings`,
`database3`.`settings`,
`database4`.`settings`
WHERE (
CONVERT(  `name`
USING utf8 ) LIKE  '%email%'
)
LIMIT 0 , 30

Ideally, I want to be able to have email, client_id, client_name, and the Cluster row totals associated so I can import it into a single database later.

Any help is appreciated!

I would start by using information_schema.TABLES and COLUMNS to find which databases (table_schema) has what tables with the desired columns.

Then I would construct (via SELECT CONCAT("SELECT ", ...)...) the SELECT statements that contain references to all the tables/columns desired.

From them, copy and paste to run them.  Or put all of this in a Stored Procedure so that a single CALL can do all the work including executeing the constructed SELECTs.


Get data from Berkeley DB with Java I have a basic Java program which can list data from Berkeley DB database. The question is how I can list the database scheme to see what object are available? Unfortunately I don't have information what is the design of the database.

You could try RTF(ine)Manual. Start here. This line is worthy of attention in your case "Berkeley DB has no notion of schema and data types in the way that relational systems do".


Join 2 tables and get the latest record I have 2 tables table1 contains the columns of id, status,created_by,created_date,name and table2 is a log table which contains id, updated_by,updated_date. Table2 contains multiple records of same id. I'm trying to get the resultant query which support to get the selected columns from both tables by join operation. but when I tried i'm getting the duplicate records associated with that id. I want only one latest record for that id. My query...

select  t.created_by,t.created_date,tl.updated_by,tl.updated_date
    from  test_name t
    join (
        SELECT  updated_by,updated_date,id
            from  test_log
            order by  updated_date desc
            limit  1
         ) tl  ON t.id=tl.id
    where  state = 'active';

You could return the sequential number of a row within a partition (in this case id) of a result set, starting at 1 (AND seqnum = 1) for the first row in each partition.

See below

SELECT t.created_by,
       t.created_date,
       tl.updated_by,
       tl.updated_date
FROM   test_name t
      INNER JOIN (SELECT tl.*,
            Row_number()
              OVER (
                partition BY id
                ORDER BY updated_date DESC ) AS seqnum
             FROM   test_log tl) tl
         ON t.id = tl.id
            AND seqnum = 1
WHERE  state = 'active';


How to get one MAX() between SUMs of several columns in SQL? I need to select MAX() between SUM(Math), SUM(English), SUM(Physics)

Result must be:

19 | English

It's never going to be pretty, but heres one attempt:

select gt, case gt when math then 'Math'
                   when physics then 'Physics'
                   when english then 'English'
           end
from (
    select greatest(math, physics, english) as gt
         , math, english, physics
    from (
        select sum(math) as math
             , sum(english) as english
             , sum(physics) as physics
        from scores
    )
)


SQL Server 2016 - Catastrophic linked server error I have an issue with connecting to a linked server from SQL Server 2016 to an Ingres 10.1 database via an Ingres 64bit driver ODBC connection setup - ODBC connection succeeds and the linked server connection test succeeds.

T-SQL used for testing has been:

select *
from openquery("linked server", 'select * from "table"')

Some tables come back fine although most tables retrieve the columns but have no data throwing the error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "" reported an error. Provider caused a server fault in an external process.

Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server

If I change the select * to specific columns, I still get the errors. I have tried changing the provider settings to "Allow in process" on/off; both settings still do not work. If I turn the above setting on i get the error:

The provider reported an unexpected catastrophic failure.

Msg 7330, Level 16, State 2, Line 1
Also a stack dump is created within the SQL logs saying EXCEPTION_ACCESS_VIOLATION.

I have played with the security connection settings of the linked server but no luck.

Any ideas fellow DBAs?

If you get a stack dump, it's time to contact the author of the linked server driver and start a support call. That's not something you can fix via query changes or configuration.


Firebird isql exclusive mode? I have a vague recollection that Firebird's isql utility will in some situations make an exclusive connection to the database, meaning that any other connection attempts will be refused.

I fail to find information about this.

Can anyone explain in which situations this happens, and in particular how I can make sure it does NOT happen?

My intended usage is to execute some commands/queries in a scheduled task, and I don't want to run the risk of blocking other database work while this task is executing.

From a few simple tests it seems like I get an exclusive connection, i.e. no other connections are allowed, when there are no other connections present. Conversely, if there are other connections when isql connects, isql's connection doesn't block other connections.

I tested this with Firebird 3.0.4 in superserver mode and FlameRobin, using the same database alias for both isql and FlameRobin connections. The alias is defined with an absolute local file path.

Should I include host and port to ensure that isql always connects through the superserver (allowing other connections) rather than opening the file directly (blocking other connections)?

OK, so further testing seems to indicate that what happens is this:

If the database specified for isql's connection is just a local file path (possibly indirectly via an alias), isql will first attempt to connect by opening the file directly. This would be the kind of connection used for classic (and supeclassic?) mode. So it's not exclusive per se, but incompatible with superserver. If the file is already locked, as would be the case if the superserver has a connection to it, isql will try to connect via the superserver. In this case, the connection will obviously not block any other connections in any way.

So, to make sure other (superserver) connections are never blocked when using isql, isql's command line should specify a TCP/IP connection string, something like this:

isql -u usr -p pwd localhost/3050:MyDBAlias

If a connection string like that is used, other (superserver) connections work just fine.


Is individual column and row-level encryption possible in SQL Server? I know about complete column level encryption techniques and complete database encryption. But is it possible to encrypt individual rows and columns in SQL Server?

Thanks

Always Encrypted feature of SQL Server 2016 onward supports individual column level encryption but not row level.

Instead of row level encryption, Row Level Security feature of SQL Server supports restricting access of particular set of records in the table.

Thanks!


Lose some FDs in 2nf normalization? Suppose we have R=(A,B,C,D) and FDs are {B->C, C->D}. The only candidate key is AB, then A and B are prime; C and D are not prime.
B->C breaks the 2NF requirements because is a partial dependency. So we break the relation into R1(A,B,D) and R2(B,C). But now C->D is lost! Am I wrong? So this breaking is not "good"?

Yes, the breaking is not good.

The reason is that you FDs (B->C and C->D) also imply B->D. So B->CD (both B->C and B->D) break 2NF and should be used for the table breaking.

So, the relations, in order to be 2NF should be: (A,B) and (B,C,D).

(and then the C->D can be used to break the second further for 3NF.)


import geonames allCountries.txt into MySQL 5.7 using LOAD INFILE - ERROR 1300 (HY000) I'm trying to import the allCountries.txt file from http://download.geonames.org into MySQL 5.7 using LOAD INFILE
but getting the following error:

  ERROR 1300 (HY000): Invalid utf8 character string:
  ''Afikanisitani,'Apekanikana,A Phu Han (Afghanistan),A Phú Hãn '

procedure:
- download & unzip the allCountries.zip from   http://download.geonames.org/export/dump/
- create a table in mysql / phpmyadmin / MySQLWorkbench for the import:

CREATE TABLE `geoname` (
 `geonameid` int,
 `name` varchar(200),
 `asciiname` varchar(200),
 `alternatenames` varchar(4000),
 `latitude` float,
 `longitude` float,
 `feature_class` char(1),
 `feature_code` varchar(10),
 `country_code` varchar(2),
 `cc2` varchar(60),
 `admin1_code` varchar(20),
 `admin2_code` varchar(80),
 `admin3_code` varchar(20),
 `admin4_code` varchar(20),
 `population` int,
 `elevation` int,
 `dem` int,
 `timezone` varchar(40),
 `modification_date` date,
 `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY
) CHARACTER SET utf8;

- run the following command to import the .txt file:
LOAD DATA LOCAL INFILE '/location/of/my/allCountries.txt' INTO TABLE geoname;

This procedure worked fine in MySQL 5.6 - but fails with the aforementioned error in MySQL 5.7.13

(Caution: TMI coming...)

There are definitely utf8mb4 characters in that data.

The LOAD DATA needed CHARACTER SET utf8mb4.

The alternatenames column has 46 cases of 4-byte UTF-8 codes.  Here's one:

mysql> SELECT * FROM allcountries WHERE geonameid = 6962506\G
*************************** 1. row ***************************
        geonameid: 6962506
             name: Shidanbian
        asciiname: Shidanbian
   alternatenames: Shidanbian,shi dan pian,石旦
         latitude: 31.08403
        longitude: 107.27187
    feature_class: P
     feature_code: PPL
     country_code: CN
              cc2:
      admin1_code: 32
      admin2_code:
      admin3_code:
      admin4_code:
       population: 0
        elevation: 0
              dem: 378
         timezone: Asia/Shanghai
modification_date: 2014-10-02

I found such rows with WHERE HEX(alternatenames) REGEXP '^(..)*F0'

The last character is HEX F0A18E9A, which needs utf8mb4.

Another example is id 281184, where  is the "Gothic" writing for Jerusalem.

FYI, here is the table and the LOAD DATA I used:

CREATE TABLE AllCountries (
    geonameid MEDIUMINT UNSIGNED NOT NULL  COMMENT "integer id of record in geonames database",
    name     VARCHAR(200) CHARACTER SET utf8mb4  COMMENT "name of geographical point (utf8) varchar(200)",
    asciiname  VARCHAR(200)    COMMENT "name of geographical point in plain ascii characters, varchar(200)",
    alternatenames TEXT CHARACTER SET utf8mb4   COMMENT "alternatenames, comma separated, ascii names automatically transliterated, convenience attribute from alternatename table, varchar(10000)",
    latitude   DECIMAL(7,5)    COMMENT "latitude in decimal degrees (wgs84)",
    longitude  DECIMAL(8,5)    COMMENT "longitude in decimal degrees (wgs84)",
    feature_class CHAR(1)      COMMENT "see http://www.geonames.org/export/codes.html, char(1)",
    feature_code  VARCHAR(10)  COMMENT "see http://www.geonames.org/export/codes.html, varchar(10)",
    country_code  CHAR(2)      COMMENT "ISO-3166 2-letter country code, 2 characters",
    cc2          VARCHAR(200)  COMMENT "alternate country codes, comma separated, ISO-3166 2-letter country code, 200 characters",
    admin1_code  VARCHAR(20)   COMMENT "fipscode (subject to change to iso code), see exceptions below, see file admin1Codes.txt for display names of this code; varchar(20)",
    admin2_code  VARCHAR(80)   COMMENT "code for the second administrative division, a county in the US, see file admin2Codes.txt; varchar(80) ",
    admin3_code  VARCHAR(20)   COMMENT "code for third level administrative division, varchar(20)",
    admin4_code  VARCHAR(20)   COMMENT "code for fourth level administrative division, varchar(20)",
    population  DECIMAL(11,0)  COMMENT "bigint (8 byte int) ",
    elevation  SMALLINT UNSIGNED  COMMENT "in meters, integer",
    dem        SMALLINT UNSIGNED  COMMENT "digital elevation model, srtm3 or gtopo30, average elevation of 3''x3'' (ca 90mx90m) or 30''x30'' (ca 900mx900m) area in meters, integer. srtm processed by cgiar/ciat.",
    timezone   VARCHAR(40)     COMMENT "the timezone id (see file timeZone.txt) varchar(40)",
    modification_date DATE     COMMENT "date of last modification in yyyy-MM-dd format",
    PRIMARY KEY(geonameid)
) ENGINE=InnoDB DEFAULT CHARACTER SET ascii COMMENT 'http://download.geonames.org/export/dump/';

LOAD DATA INFILE 'C:/htdocs/misc/allcountries.csv'
    INTO TABLE allcountries
    CHARACTER SET utf8mb4
    FIELDS TERMINATED BY "\t"
    LINES TERMINATED BY '\n';

Note the careful picking of ascii vs utf8mb4 for charset.

A SHOW WARNINGS indicated that that is still not 'correct':

Incorrect integer value: '' for column 'elevation' at row ...

That can be solved via use of an @variable, and a SET in the LOAD DATA.

You were stopped at least by Afghanistan in Gothic:  .  That's almost readable!

(That was fun.)


Changing Schema ownership and side effects I am trying to understand why changing schema ownership is causing permissions to be revoked.

I am having a hard time understanding schema ownership change.  Here are the steps I have taken in order.

Created two logins hr and it
Created two users(hrread and itread) for those logins
created two database roles hr_read_only and it_read_only
Created two schemas with names hr and it and populated some tables
Added hr_read_only and it_read_only roles as owners of hr and it schemas, respectively
added users hrread and itread as hr_read_only and it_read_only roles

If I try accessing hr tables as hrread user, I am able to.  However, when I change the owner of hrschema to dbo, I am not able to access tables within the hr schema.

After some time I determined that the role is owner and users are part of that role.  So keeping the ownership of hrschema to dbo, I have added securrables to the role hr_read_only (select permissions on objects with hr schema).  I am able to access now.

But when I change ownership of hrschema, this is affecting the users too, they were not able to access hr tables, only one way is I have to add back securables again.

Can you please help me understand why changing schema ownership is causing this and any best recommendation to avoid this.

SCRIPT:
create login itreadonly with password='Password123$'
create login Hrreadonly with password='Password123$'

create user hrread for login hrreadonly
create user itread for login hrreadonly

CREATE ROLE [hr_read_only]
CREATE ROLE [it_read_only]

create schema hr
create schema it

i went ahead and added hrread and itread users part of hr_read_only and it_read_only role.

2.I changed ownerships of hr and it schema to hr_read_only and it_read_only respectively

3.i am able to access now all tables with hrschema when executing as hrread

4.Now i went and changed the ownership of hrschema to DBO

5.I was not able to access now when executing as hrread

6.So i went back to hr_read_only role and added securables giving select permission on tables with hr schema.

7.I was able to access now all hr tables

8.I changed back hrschema ownership to someother role other than hr_read_only and it_read_only and dbo

9.Now i am not able to access any tables with hr schema when trying to execute as hrread,when i tried to see securables for hr_read_only ,i am not seeing anything

I am not able to understand why this is happening,even though i gave securables

In SQL Server, changing the schema owner will drop all permissions. This is a "feature" buried deep in the documentation:

  If the target entity is not a database and the entity is being
  transferred to a new owner, all permissions on the target will be
  dropped.

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-authorization-transact-sql

For a schema, this apparently applies to the permissions on all objects within it.


Visualiser for spatial query results This question Whats the easiest way to get sql 08 spatial data onto a map? (and many other articles) recommends Craig Dunn's Geoquery, but all the download links appear to be kaput.

SQL Server Managament Studio's spatial results tab will show a single dataset, and this MSDN article, Spatial Data Support In SQL Server 2008 mentions a workaround using UNION ALL to display point data overlayed on a map:

SELECT geog, name FROM Mondial.dbo.city WHERE geog IS NOT NULL
UNION ALL
SELECT geog, cntry_name FROM SpatialSamples.dbo.cntry00

Whenever I try something similar, I only ever get a single resultset displayed.

Is there something built into either SQL Server 2008 or 2012 to do this?

I would use Excel 2013 - it has fairly easy Map integration which can take Lat+Lon, eg:

http://www.sqljason.com/2013/03/geospatial-analytics-microsoft-bi-john.html

I would pull a SQL view through PowerPivot for easy derivation of .Lat and .Long, and for refreshing the data.

Alternatively SQL Server Reporting Services 2008 R2 or 2012 has similar Map integration, but the end user experience is static.

http://msdn.microsoft.com/en-AU/library/ee240845.aspx


InnoDB, Writing TRIGGER to INSERT into tables Table1 "user_address"
Field "u_id"
Field "street_name"
Field "locality_name"

Table2 "street_master"
Field "s_id"
Field "s_name"

I am supposed to write a trigger which BEFORE INSERT ON user_address(table1) checks if NEW.street_name already exists in s_name in table2, and
if it does not exist then NEW.street_name is inserted in table2.

I don't know how to do it . It has to be done by trigger only.

Given your table structure:

create table user_address (
u_id integer auto_increment primary key,
street_name varchar(1000),
locality_name varchar(1000)
);

create table street_master
(
s_id integer auto_increment primary key,
s_name varchar(1000)
);

This will do the job:

DELIMITER $$
create trigger user_address_btrig
before insert on user_address
for each row
begin
DECLARE CNT INT;
SET CNT = ( select count(*) from street_master where s_name = new.street_name );

IF CNT=0
THEN
insert into street_master (s_name) values (NEW.street_name);
END IF;
end;
$$
DELIMITER ;

To test:

insert into user_address (street_name, locality_name) values ( 'test street', 'test locality' );

select * from street_master where s_name = 'test street';

I suggest you read the MySQL documentation on triggers and learn for yourself.


Just update one column, strange execution plan I have logged the slowest queries on my database, and one surprises me, showing many times in the list, and taking often many seconds to execute.

UPDATE books SET last_read='2018-12-31 11:23:45' WHERE book_id='15'

book_id is int identity PK (clustered), last_read is a datetime.
The query is written with the 15 in single quotes, thus requiring a conversion, but I cannot imagine this being a big deal, because the conversion would only be done once per query.
There are 6 indexes on the table, but the column last_read is not involved or included in any of them. The PK is on book_id and is nothing special.

The estimated execution plan tells me:

UPDATE: 0%
Clustered Index Update (on the PK constraint): 100%

I would not expect the query to update any index, because last_read is not indexed in any way, and book_id is not changed.

What am I missing?

Let's do a small thought exercise:

How do you think SQL Server finds the row with that book_id?
How do you think it finds (not updates) the value for last_read?

Remember that the clustered index is the table. If you update the table, you must update at least the clustered index.

Let me continue the thought exercise by asking a different question - do you think there is (or should be) a more efficient way of updating this value, than by finding the row via the clustered index, and changing the value there?

Let's leave heaps out of this for now, but the concept is similar there.


Script out XMLA for all databases I have several SSAS databases.  I only want to script out the XMLA for them on a nightly basis.  This will be a second tier backup in addition to the regular backups we have.

How do I auto-generate XMLA scripts for all the databases?

You can do it using powershell.

$serverName = "servername\instanceName"
$outputFolder = "D:\data\"

## load the AMO and XML assemblies into the current runspace
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $null
[System.Reflection.Assembly]::LoadWithPartialName("System.Xml") > $null
$dateStamp = (get-Date).ToString("yyyyMMdd")

## connect to the server
$svr = new-Object Microsoft.AnalysisServices.Server
$svr.Connect($serverName)
foreach ($db in $svr.Databases)
{
    write-Host "Scripting: " $db.Name
    $xw = new-object System.Xml.XmlTextWriter("$($outputFolder)DBScript_$($db.Name)_$($dateStamp).xmla", [System.Text.Encoding]::UTF8)
    $xw.Formatting = [System.Xml.Formatting]::Indented
    [Microsoft.AnalysisServices.Scripter]::WriteCreate($xw,$svr,$db,$true,$true)
    $xw.Close()
}
$svr.Disconnect()

References :

Automating the Scripting of an SSAS database
PowerShell to Script SQL Analysis Service Database(XMLA Script) and ReApply on a different Server
SQL Server Analysis Services Backup in Powershell


Slow scalar function that used to be fast I have a scalar function that defined as below:

CREATE FUNCTION GetUserFullname(@Id INT)
RETURNS NVARCHAR(500)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @fullname NVARCHAR(500)

    -- Add the T-SQL statements to compute the return value here
    SELECT @fullname = (Firstname + ' ' + Lastname)
    FROM Account_Profiles
    WHERE UserId = @Id

    -- Return the result of the function
    RETURN @fullname
END

This function used to be fast when I used it in a query:

SELECT dbo.GetUserFullname(CurrentRefId) FROM OrderItems

The query above has a result of ~50,000 rows. the query used to be executed in 2 seconds but now all of sudden it takes 22 seconds to run!

It is slow for all distinct values of CurrentRefId in OrderItems, when I query SELECT dbo.GetUserFullname(CurrentRefId) FROM OrderItems. There are statistics updated every night. There is also an index with Total Fragmentation 75.

I also have a copy of this database in my local SQL Server and it does run very fast in ~1 second.

My production database server has 512 GB memory and 64 processors. It is an Always On Failover Cluster server and Availability Group. The database version is 2012 SP2.

You are requerying the Account_Profiles table for every single row in OrderItems.  If OrderItems has 50,000 rows then you are going to hit Account_Profiles 50,000 times.  If OrderItems grows larger you will query it more times.  If Account_Profiles grows larger then each of those times will take longer.  You will see a large difference by inlining the concatenation. (making assumptions on the join column names here)

Select Firstname + ' ' + Lastname FullName --other data here
FROM OrderItems oi INNER JOIN Account_Profiles ap
ON oi.userid=ap.userid


Is there any Cross-platform SQL editors that allow joins from different databases? Initially tried dbeaver but after configuring it, I realized that you can't perform joins from different data bases.

Also tried TeamSQL which apparently does what I need but looks to be designed without privacy in mind (requires constant connection with internet).

Select a.Product, b.Product, a.ID, b.ID, c.Product, c.ID
FROM SQL_Server 1 a
LEFT JOIN Oracle b
ON a.ID = b.ID
LEFT JOIN SQL Server 2 (another location) c
ON a.ID = c.ID

Thank you for your time.

This seems more like something you'd set up in the database itself, than via an external tool.

Since this is sql-server, for instance, you can use Linked Servers to accomplish this.


CLUSTER using sequential scan followed by sorting I'm tring to understand the meaning of the following clause from PostgreSQL 12 docs

  CLUSTER can re-sort the table using either an index scan on the
  specified index, or (if the index is a b-tree) a sequential scan
  followed by sorting.

  When a sequential scan and sort is used, a temporary sort file is also
  created, so that the peak temporary space requirement is as much as
  double the table size, plus the index sizes. ... you can disable this choice by temporarily setting
  enable_sort to off.

Clustering is meant to physically reoder the table based on the index information. So what is going to happen if I set enable_sort=off? Will it still manage to reoder the data properly? If yes, then what is the benifit of having the enable_sort option in the play here at all?

TFM says:

  It is impossible to suppress explicit sorts entirely, but turning this variable off discourages the planner from using one if there are other methods available.

So yes, it will reorder the table rows properly, but will try to chose an alternative, potentially slower, access method, if at all possible.


Update specific rows in a column I have a column with 82 rows(from table1) and I want to update just the 76 rows in it with different values from another table(table2). Avoiding 6 specific rows(1, 10, 38, 39, 46, 77). Leaving these rows with null values.

I used cursor to save all the 76 rows from my table2

Cursor my_cur
 Is
  Select * from table2

Then I tried to update my table1

Begin
 For x in  my_cur loop
   Update table1
   Set my_col = x.acct_num
   Where rnum not in ('1', '10', '38', '39', '46', '77');
end loop;
End;

But I ended up updating these rows with same value(only 1 acct_num to all 76 rows). It seems that the last value(acct_num) from my cursor was the only one im always getting.

I want to enter different acct_num to 76 rows and in the same order as in my table2

I added rownum in both of them to have at least be sure what rows to skip. Any ideas how to do it? Thanks.

you are missing a key based on what you can update the data ... obviously there is always the last value in table1.my_col because what you are doing is looping through all values and updating the column in the table1..

The database design is not correct ..  Can you share your table structure / definition ?

Then, we can come up with some solution for you

EDIT:
if you wanna just move those records from one table to another ...

try this:

insert into t1 (my_col) as select acct_num from t2 where rownum not in ('1', '10','38', '39', '46', '77');


Connecting client application to MariaDB Galera cluster I currently have a REST API written in Flask that connects to a MariaDB server. I'm thinking about replacing the server with a Galera cluster to improve availability and ensure continuity in case one of the nodes goes down. What I'm having trouble understanding is how client applications connect to the cluster itself. As I'm currently only using a single database server, I can connect using the following code:

engine = create_engine('mysql://username:password@hostaddress/database_name)

If I were to have three nodes in the cluster with the addresses 192.168.1.1, 192.168.1.2 and 192.168.1.3 how would I connect the application to the cluster? I assume that it would be possible to replace the current hostname value with one of the node IPs e.g. 192.168.1.1 but I'd imagine that if I did that and that node went down, the application would no longer be able to connect to the cluster because it's specifically trying to connect to that one node. How can I ensure that the application continues to function if a node fails?

I'm still new to the idea of Galera cluster so apologies if I've misunderstood something about how it works. Any advice would be much appreciated.

You'll have to look up on the documentation of your database connector. Normally (e.g. in Java world) you can specify comma-separated list of nodes instead of a single host.

For instance, check the documentation on Failover at:
https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html

Alternatively you can run a separate load-balancer server "in front of" the cluster nodes and just point the client to the load-balancer.


Shrinking mdf files - SQL Server 2012 I am in need to shrink a data file in SQL Server 2012. I am using shrink files option in tasks of the database and am using "Reorganize pages before releasing unused space" option. SQL Server says minimum size I can shrink the file upto is 350000 MB. What will happen if I try and shrink it to 300000 MB, Will this delete the data from the database?
In simple words, if I shrink the file beyond the minimum size of the file, does it delete the data from the file?

No, shrinking a file or a database never deletes data, at least not data that hasn't been marked to be deleted via the DELETE FROM or the TRUNCATE TABLE ... T-SQL statements.

The "Reorganize pages before releasing unused space" provides the facility to move data around within the file to allow shrinking the file as much as physically possible.  If the minimum size reported by the "shrink file" dialog is 350,000 MB, then you won't be able to shrink the file to anything less than that number.

You may want to look at this question and the associated answers for more details about the pros-and-cons of shrinking databases/files.


No CREATE_TIMEDATE column, can I still export records inserted within last 24 hours? A database was designed almost 10 years ago, now I may need to export records inserted from the last 24 hours. Back then, the table design did not include CREATE_TIMEDATE col for these tables.

Is there any luck that database servers (in this case SQL Server 2005), have a system hidden TIMEDATE attribute for all table records so I can export all records within 24 hours?

Or is my only solution is to save last primary key id at midnight and then 23.99 hours later, export from last primary key id to current primary key id?

In SQL Server 2005 there is no hidden column for the date something was added to the table.

the best solution would be to add a new DATETIME column to the tables you are looking to export the last 24 hours if this is to be an ongoing process (you could put an insert trigger on the table to make sure the column is populated).  Entries from before you started tracking the creation date can remain NULL or be set to something like '1900-01-01' (the "0" date in SQL Server).

If changes to the table are not permitted then the only option is to query the table at midnight and then again at midnight the next day (a SQL Agent job would be the best way to accomplish that).  However this only works if the primary key is always increasing in some manner (Identity column for example).  Otherwise you wont know what range you need to select, (IE if your primary key is email for some reason you wont know when a record was inserted based on the sequence).

I would also suggest a table to hold those results from the query every 24 hours so if you need to rerun the export for a specific day you would have the information you needed to do that.


I want to start the Postgres database I run this:

$ postgres -D foobar

It just hangs.  There is no error message.  I newly created this database with the initdb command.  What should I do?

From the INSTALL document

                                Short Version

./configure
make
su
make install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

So,

initdb initialises the system after the binaries have been put in place, either through an rpm or a source compile.

postgres launches the server (which is what you're trying to do)

createdb xyz creates a database of name xyz

psql is the command-line client

You could try

./bin/pg_ctl -D ./data/ -l logfile start

On running initdb, you should have received a message

===========================

Success. You can now start the database server using:

./bin/postgres -D ./data/

or

./bin/pg_ctl -D ./data/ -l logfile start

===========================

What I can suggest to you is to run the pg_ctl option - it's the one that I use, for what it's worth.

You appear to have confused the server command with a database access command. You should run psql foobar to access the foobar database, provided the server is already running - see above how to do that.


How to create a table with columns from other 4 tables(they don't have primary/foreign key relations)? how can I create a SQL statement to create a table that has columns from 4 tables? So in my new created table I would like to extract the Id, Product Title, Items in cart(number) (from these 4 tables so it will be 4 columns with Items in cart (number)), Link. These tables have 3024 rows so how can I extract these information in one table?
I attached the tables(SQL) and the table(SQL) that I would like to have(I would like a SQL command to do that an insert or join). I will appreciate any help.

DROP TABLE IF EXISTS `csv_10_05`;
CREATE TABLE IF NOT EXISTS `csv_10_05` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `product title` varchar(2040) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `product price` varchar(55) NOT NULL,
  `items in cart` varchar(2020) DEFAULT NULL,
  `items in cart(number)` varchar(50) DEFAULT NULL,
  `link` varchar(2024) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `csv_11_05`;
CREATE TABLE IF NOT EXISTS `csv_11_05` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `product title` varchar(2040) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `product price` varchar(55) NOT NULL,
  `items in cart` varchar(2020) DEFAULT NULL,
  `items in cart(number)` varchar(50) DEFAULT NULL,
  `link` varchar(2024) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `csv_12_05`;
CREATE TABLE IF NOT EXISTS `csv_12_05` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `product title` varchar(2040) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `product price` varchar(55) NOT NULL,
  `items in cart` varchar(2020) DEFAULT NULL,
  `items in cart(number)` varchar(50) DEFAULT NULL,
  `link` varchar(2024) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `csv_13_05`;
CREATE TABLE IF NOT EXISTS `csv_13_05` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `product title` varchar(2040) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `product price` varchar(55) NOT NULL,
  `items in cart` varchar(2020) DEFAULT NULL,
  `items in cart(number)` varchar(50) DEFAULT NULL,
  `link` varchar(2024) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

And this is what I want (now I've created manually but I would like a query like join to extract the columns)

DROP TABLE IF EXISTS `amazon_stock`;
CREATE TABLE IF NOT EXISTS `amazon_stock` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `product title` varchar(2040) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `items in cart(10_05)` varchar(50) DEFAULT NULL,
  `items in cart(11_05)` varchar(50) DEFAULT NULL,
  `items in cart(12_05)` varchar(50) DEFAULT NULL,
  `items in cart(13_05)` varchar(50) DEFAULT NULL,
  `link` varchar(2024) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

Create a table with an extra column to distinguish where it came from
Do something like

...

INSERT INTO new_table (title, price, items_ct, link, which)
    ( SELECT *, '10_05' AS which FROM csv_10_05 )
    UNION ALL
    ( SELECT *, '11_05' AS which FROM csv_11_05 )
    UNION ALL
    ...


How to run ALTER TABLE on a list of tables sequentially in a function I have a schema (called import) where data are imported as tables, and a function that processes them all. After that I want to move these tables away in another schema (called data_archive). The existing function works great but I can't find a way to change the schema; here's a function I wrote to do this:

My idea was to create a list of tables according to criteria provided by a select on a table made for this management, then loop over this list and do a Alter table xxxx SET schema data_archive:

CREATE OR REPLACE FUNCTION archive_datasets()
  RETURNS VOID AS
$BODY$
DECLARE
   table_rec  record;

BEGIN

   FOR table_rec IN
      SELECT t.table_name
      FROM information_schema.tables AS t
      WHERE table_schema = 'import'
      AND quote_ident(t.table_name) IN (
              SELECT "table_name"
              FROM data_sets
              WHERE status IN ('Processed', 'Archived', 'Deleted')
              )

   LOOP
     ALTER TABLE quote_ident(table_rec.table_name) SET schema data_archived;
   END LOOP;

END;
$BODY$
  LANGUAGE plpgsql;

But I obtain this error message:

ERROR:  syntax error at or near "("
LINE 28:  ALTER TABLE quote_ident(table_rec.table_name) SET schema ...

So my question is how to pass the name of the table to this ALTER TABLE query? Or what am I doing wrong?

I use PostgreSQL 9.3.

The problem is that you cannot use parameters as object names (tables, columns, etc.) in straight SQL statements, like your ALTER TABLE above.

I use 'straight' here as an opposite of 'dynamic' - the thing you need:

...
loop
    EXECUTE format($$ALTER TABLE %I SET SCHEMA TO data_archived$$,
                   table_rec.table_name);
END loop;
...

Notes:

here I am using dollar quoting when building the dynamic query.  This allows me to use 'normal' syntax, as opposed to multiplicating single quotes, for example (not present in this example).  This way most editors will highlight the statements nicely.
I also use format() with the %I format specifier to have the object name properly quoted if necessary.  This approach is far more readable than building the query with concatenation of string constants and some quote_ident() calls.  It is present in versions 9.1 and newer.
I have a feeling that you compare stuff like quote_ident('This_table') IN ('This_table'), which won't be true.  Either use quote_ident() on both sides - or it is easier to just omit quoting and compare the string values.


Postgres 11 declarative partitioning - using large number of partitions We are running a system using Postgres 11 and we implemented partitioning with ID which is most convenient to us but which produces many partitions (currently over 1000 with expected total of 10000 within next 5 years). We generate 30 million rows per month with heavy indexing. Partition rows are never updated and our queries always target single partition.

Now we have run into a problem that we exhausted maximum allowed locks (cca. 25000). Is increasing max_locks_per_transaction and max_connections a viable solution with expected rise in partition number? Or should we switch to hash or date-range partitioning? And if so, what would be the optimal partition size and count?

we implemented partitioning with ID

What does that mean specifically? List partitioning, where each list has only one member?  And what is ID?  Is that the primary key from a parent table, and you are partitioning a child table?

Did you do before-and-after studies, or testing in a good test environment, to show that you actually get a benefit from this setup in the first place?

  Partition rows are never updated and our queries always target single partition.
  Now we have run into a problem that we exhausted maximum allowed locks (cca. 25000).

If you don't update, and queries always target a single partition, then I don't see how you would be running out of locks.  What operation is happening which locks all the partitions at once?  Is 25000 the current setting of max_locks_per_transaction * max_connections, which is inadequate, or is that what you want to increase it to?  If 25,000 is already inadequate and you still expect your data to grow 10 times, I'd be a bit worried there that just increasing it may not be good long term solution (although probably fine as a short term solution).  Or at least, I'd do some simulations in a test environment.

  Or should we switch to hash or date-range partitioning?

Partitioning is not fungible.  If partitioning by ID gave you a substantial benefit, there is no reason to think changing it to date-range would give the same benefit.  Without knowing the source of the benefit, we can't predict what other methods would retain (or better) that benefit.


SSRS Scale Out Deployment I have setup SSRS scaled out deployment by using two nodes cluster configured in always on availability group. Now i can access the SSRS web portal via the AG listener name via port http protocol.
Example: http://aglistenername.contoso.local/Reports/Browse
My question is, is there any way I can configure the SSRS web portal URL with https protocol?
Thank you.

First of all you need a proper https certificate available and installed in both instances (i suppose you installed SSRS on both instances and i suppose also that you are using SSRS native mode).
Then open Reporting Services Configuration Manager,
click Connect at the “Reporting Services Configuration Connection” Screen;
click on Web Site URL in the left-hand pane window
In the “HTTPS Certificate” drop-down box select the certificate you installed.  You will see the Port text box pre-fill and the Report Server Web Services URL pre-fill.
Click Apply


Upgrading SQL Server 2008 Standard to Enterprise I'm trying to do the upgrade but when it asks me for the Product Key for the current version, I can't find it. When I go on the MSDN website for it, it says that a key is not necessary for this product. So I'm at a standstill. Do I need to do the upgrade directly from the Enterprise ISO instead of from the Standard SQL Server Installation Center Maintenance Edition Upgrade option? How else can I get the product key?

Or when it asks from what version I'm upgrading, can I select Express or Evaluation where it doesn't require me to put in the current product key?  Will that affect the upgrade?

Will I definitely need the ISO?  The same deal with that is that it says the product key is not necessary on the MSDN website since we have an EA.

Have your client with the ISOs or install files open the DefaultSetup file within either the x64 or x86 directory, cut and paste, and send you the text for the SQL Server edition you require. It should look something like:

;SQLSERVER2008 Configuration File
[SQLSERVER2008]
PID="AAAAA-BBBBB-CCCCC-DDDDD-EEEEE"

No need to copy/download the whole ISO.


Does SQL Server 2016 Standard Edition support table partitioning? I want to upgrade my SQL Server 2008 Enterprise Edition to SQL Server 2016 Standard Edition; however, one database makes use of table partitions over multiple file groups (used on a large logging table, each day is a partition)

I see in Editions and Supported Features for SQL Server 2016 under the section "RDBMS Scalability and Performance", that it says that Standard Edition supports Table and index partitioning, but it does not support Partitioned Table Parallelism.

I am not sure that I fully understand the consequences of this.

What exactly does it mean in my case, and how will it affect the database's performance?

Starting with SQL Server 2016 SP1 the Standard Edition supports all functionality most programming surface of the Enterprise Edition, as announced here: SQL Server 2016 Service Pack 1 (SP1) released !!!.

Quoted from that article:

  The following table compares the list of features which were only available in Enterprise edition which are now enabled in Standard, Web, Express, and LocalDB editions with SQL Server 2016 SP1. This consistent programmatically surface area allows developers and ISVs to develop and build applications leveraging the following features which can be deployed against any edition of SQL Server installed in the customer environment. The scale and high availability limits do not change, and remain as–is for lower editions as documented in this MSDN article.

What still differentiates EE from SE are primarily performance and administrative improvements.

So, in your case, SE will support table partitioning (functionality) but will not leverage certain performance enhancements available on partitioning, like 'partitioned table parallelism'.


What's the duration of U lock As far as I know, SQL Server holds X locks for the duration of a transaction no matter which isolation level is in use. What about the U lock? If a transaction reads 100 records in an UPDATE statement (U locks) but updates 10 records only, will we end up with 90-U and 10-X after the update is done and if the tran is still open?

In response to David's answer below: No those situations are not related (thanks for that answer!). I cannot post the code as I have no my PC at hand, but I will try to describe this case in a nutshell. I have a heap. I update it, there are 10 records updated. Q: will we have any U locks at the ... point or are they released once X locks are obtained? Thanks!

Begin tran;

Update t
Set b = 10
Where a = 1;
...

An UPDATE will read with U locks, releasing each U lock immediately if it is not going to X lock and change the row.  Profiler will show you this with the Lock:Acquired and Lock:Released events.

EG an update on a heap will IX lock the object, then for each page IU lock the page, then for each row U lock the row, and either release the U lock or convert to IX lock on the page, convert to X lock on the row and update it.  The page lock will be released when it's read the last row on the page.

use tempdb

select *
into test
from sys.objects

go

begin tran
update test set name = 'foo2'
where object_id = 9

Has a lock sequence like this:

Lock:Acquired                               59      8 - IX  5 - OBJECT
Lock:Acquired   5:64                            59      7 - IU  6 - PAGE
Lock:Acquired   5:64:0                          59      4 - U   9 - RID
Lock:Released   5:64:0                          59      4 - U   9 - RID
Lock:Acquired   5:64:1                          59      4 - U   9 - RID
Lock:Released   5:64:1                          59      4 - U   9 - RID
Lock:Acquired   5:64:2                          59      4 - U   9 - RID
Lock:Released   5:64:2                          59      4 - U   9 - RID
Lock:Acquired   5:64:3                          59      4 - U   9 - RID
Lock:Released   5:64:3                          59      4 - U   9 - RID
Lock:Acquired   5:64:4                          59      4 - U   9 - RID
Lock:Released   5:64:4                          59      4 - U   9 - RID
Lock:Acquired   5:64:5                          59      4 - U   9 - RID
Lock:Acquired   5:64                            59      8 - IX  6 - PAGE
Lock:Acquired   5:64:5                          59      5 - X   9 - RID
Lock:Released   5:64:5                          59      0 - NULL    9 - RID
Lock:Acquired   5:64:6                          59      4 - U   9 - RID
. . . continue reading rows


how to minimize the data scanned on my database using index? Is there anyone how to minimize the data scanned on my database??
please show me.. using index?

query

        SELECT
        IF (e.channel LIKE 'IAX2%', e.calldate,
            IF(d.channel LIKE 'IAX2%',d.calldate,
                 IF(c.channel LIKE 'IAX2%',c.calldate,
                   IF(b.channel LIKE 'IAX2%',b.calldate,
                     a.calldate ))))
                     AS true_calldate,

        IF (e.channel LIKE 'IAX2%', e.src,
            IF(d.channel LIKE 'IAX2%',d.src,
                 IF(c.channel LIKE 'IAX2%',c.src,
                   IF(b.channel LIKE 'IAX2%',b.src,
                     a.src ))))
                     AS true_source,

        IF (e.channel LIKE 'IAX2%', e.dst,
            IF(d.channel LIKE 'IAX2%',d.dst,
                 IF(c.channel LIKE 'IAX2%',c.dst,
                   IF(b.channel LIKE 'IAX2%',b.dst,
                     a.dst ))))
                     AS true_destination, a.dcontext AS context, a.channel AS trim_channel

        FROM
        cdr a
        LEFT JOIN cdr b
        ON (
        SUBSTRING(a.channel, 1, CHAR_LENGTH(a.channel) - 2) = SUBSTRING(b.dstchannel, 1, CHAR_LENGTH(b.dstchannel) - 2)
        )
        LEFT JOIN cdr c ON (
        SUBSTRING(b.channel, 1, CHAR_LENGTH(b.channel) - 2) = SUBSTRING(c.dstchannel, 1, CHAR_LENGTH(c.dstchannel) - 2)
        )
        LEFT JOIN cdr d ON (
        SUBSTRING(c.channel, 1, CHAR_LENGTH(c.channel) - 2) = SUBSTRING(d.dstchannel, 1, CHAR_LENGTH(d.dstchannel) - 2)
        )
        LEFT JOIN cdr e ON (
        SUBSTRING(d.channel, 1, CHAR_LENGTH(d.channel) - 2) = SUBSTRING(e.dstchannel, 1, CHAR_LENGTH(e.dstchannel) - 2)
        )
        WHERE a.accountcode = 003586 AND
        a.dstchannel = '' AND
        (a.channel LIKE 'LOCAL%' OR a.channel LIKE 'IAX2%')
        ORDER BY a.calldate

here is the query EXPLAIN output

  id    select_type     table   type    possible_keys               key             key_len     ref     rows    Extra
  1     SIMPLE          a       ref     accountcode,accountcode_2   accountcode     4           const   334     Using where; Using temporary; Using filesort
  1     SIMPLE          b       ALL     NULL                        NULL            NULL        NULL    801     Using where; Using join buffer (Block Nested Loop)
  1     SIMPLE          c       ALL     NULL                        NULL            NULL        NULL    801     Using where; Using join buffer (Block Nested Loop)
  1     SIMPLE          d       ALL     NULL                        NULL            NULL        NULL    801     Using where; Using join buffer (Block Nested Loop)
  1     SIMPLE          e       ALL     NULL                        NULL            NULL        NULL    801     Using where; Using join buffer (Block Nested Loop)

You should give CREATE TABLE statements or at least info about what is indexed and how. But what I can gather from what you posted:

JOIN on functions is bad, not indexable. If you cannot get rid of those substrings, you might store them precomputed in separate columns and index those (at least the dstchannel ones should be enough, because of current join order).

You can extend index on accountcode with dstchannel for better selectivity and calldate to get rid of the filesort.

The OR over a.channel is not very indexable as it is, but as it uses prefix, you might split your query into two, each one using one part of that OR (and "glue" them with UNION) and replace calldate with channel in that index, it will mean doing filesort, but can be better if those conditions on channelare selective enough (you will have to test that probably to find out which variant is better).


What language and data structures does MYSQL uses as backend? I am just curious to know what data structures and languages does MYSQL exactly use as back end language? and what does the file format .MYD , .MYI and .frm stand for and how does MYSQL use them?

Thanks.

MySQL can be compiled in C/C++ and you can download the source code to do so.

At this link, Under the Select Platform ComboBox, Select Source Code and Download it from there

The .frm , .MYD, and .MYI make up a MyISAM table

For example, the user table for the mysql grants is amde up of three files

/var/lib/mysql/mysql/user.frm (Table Structure for any Storage Engine)
/var/lib/mysql/mysql/user.MYD (MyISAM Table Data)
/var/lib/mysql/mysql/user.MYI (MyISAM Table Indexes)


Check if DB2 backup image contains any tablespace not using automatic storage? I want to check a backup DB2 image if it contain any tablespace using non-automatic storage.

I had tried to use db2ckbkp commands. But it seems it cannot tell me if any tablespace using non-automatic storage.

db2ckbkp -Tablespace NEWDB.0.DB2_01.DBPART000.20170317153802.001

       NONAUTOTS
                  tbspInImage: T

                           ID: 7
                        flags: 0x101
                       flags2: 0x0
                  extent_size: 32
                prefetch_size: 16
                      version: 4
                      flavour: 9
                        state: 0x0
          statechangeobjectid: 0
            statechangepoolid: 0
                      LifeLSN: 0000000000062B2C
               LoadPendingLSN: 0000000000000000
              LoadRecoveryLSN: 0000000000000000
                     BeginLSN: 0000000000000000
                       EndLFS: 0
                   StordefLSN: 0000000000000000
              Full Backup LSN: 0000000000000000
              Last Backup LSN: 0000000000000000
             Full Backup Time:  00000000 = "19700101080000"
             Last Backup Time:  00000000 = "19700101080000"
                   TotalPages: 0
                 UseablePages: 0
                  reorgPoolID: 0
                   reorgObjID: 0
               poolReorgCount: 0
              # of containers: 1
                current_group: 0
                    cont_csum: 3582768349
          current_map_entries: 0
                    page_size: 4096
                     map_csum: 0
         tsp rfwd encountered: 16
             storage group ID: -1
      source storage group ID: -1
                     data tag: 0
    recovery storage group ID: -1
   recovery storage group LSN: 0000000000000000

                Container CB
                                 Type: 0x0
                           TotalPages: 0
                          UsablePages: 0
                   # of OS rsvd bytes: 0
                        Page 0 offset: 0
                           Tag offset: 0
                        Extent offset: 0
                                 Name: C:\Users\anson.wong\Desktop\ABC\nonAutoTS

I found a command from DB2 RESTORE which could generate a script file containing information about tablespace using non-automatic storage. But I prefer handling it in memory without the need to write a file.

db2 RESTORE DATABASE NEWDB FROM "C:/Users/Desktop/ABC" redirect GENERATE SCRIPT generatedScript.txt

Tablespace name                          = NONAUTOTS
Tablespace ID                            = 7
Tablespace Type                          = System managed space
...
Using automatic storage                  = No

Thank you.

Finally, I could judge if a backup image contains any tablespace not using automatic storage from db2ckbkp command by using a special number 0x1000 with Flags value

db2ckbkp -Tablespace NEWDB.0.DB2_01.DBPART000.20170317153802.001

// Here are some snippets:

NONAUTOTS
tbspInImage: T
ID: 7
flags: 0x101

Bitwise AND operation
     0x101
AND 0x1000
       0x0
// The zero means that this tablespace is non-automatic storage tablespace.

SYSCATSPACE
tbspInImage: T
ID: 0
flags: 0x3102

Bitwise AND operation
    0x3102
AND 0x1000
    0x1000
// The non-zero value means that this tablespace is automatic storage tablespace.

With reference to IBM Support


How to know which security updates to install? I am using KB2977316 and KB3045324 for this example.  These are security updates for SQL Server 2014 RTM.

KB2977316 is version 12.0.2381.0 published 6/2/2015
KB3045324 is version 12.0.2269.0 published 7/13/2015

If I have a SQL Server 2014 RTM system, do I need to install both of these, just the last one released, or the one with the highest version number?  The 2381 update is a 600 MB download, while the 2269 version is 183 MB, so logic would dictate that 2269 does not contain all of the fixes included in 2381 and I need to install both.  I guess what's throwing me off is that it just seems odd to have the version number decrease as newer updates are installed.

Please limit scope of answers and comments to security updates only.  This question has nothing to do with CUs or SPs (other than that different security updates will apply based on the specific CU or SP level).  Thanks!

Part of my mistake was in comparing QFE and GDR builds and release dates.  The QFE downloads are much larger (~600 MB) than the GDR downloads (~200 MB).  The dates and build numbers vary widely as well

I found the information at Latest Builds of SQL Server 2014 quite helpful.  So just comparing the related security bulletins and GDR releases:

ms14-044 gdr = 183 MB   build 2254  8/12/2014
ms15-058 gdr = 205 MB   build 2269  7/13/2015

The one released on the later date is the higher version.  I'm not yet sure how the person that maintains the page deduced that build 2269 includes the fixes from build 2254, so perhaps someone can comment on that or edit this answer.


Import .frm and .ibd files into a new instance of MySQL I have the .frm and .ibd files from an old, non-working MySQL server (on Windows).  I took out the hard drive and was able to get the data directory copied to my machine.

How would I get them into MySQL on my development machine? I tried just copying the directory into the MySQL data directory, but that didn't work.

The old database is v5.5.4, and my version is v5.7.15. I'd be happy to install a matching MySQL version if that would help.

I saw Restoring MySQL Tables from .ibd, .frm and mysqllogbin files, but this database has over 800 tables in it - way too much for that process.

It should work copying the whole data directory to the target machine given the following conditions:

You must have the whole data directory (ibdata1 and iblogfiles included).
Try to use the same my.ini file.
The OS where the source DB files were running must be the same as of the target OS.
Try to use MySQL 5.5.x. If you need to upgrade to 5.7 you must first install 5.6.x and run mysql_upgrade script, install 5.7.x and run again mysql_upgrade script, OR install 5.5.x, run a mysqldump, and then importing the dumpfile on 5.7.x.

If you don't have the ibdata1 and iblogfiles then you must apply the procedure of the thread that you shared, scripting as Rick suggested.


mysql master.info shows password in plain text I'm running 5.7 mysql. Is there anyway I can hide the password? I read that it is visible on 5.6 as well

"The password" is a password the slave uses to log itself in to the master. In MySQL replication, the master's role is passive with regard to connections between master andbl slave -- the slave always initiates the connection.

The slave needs a password in order to connect to the master and authenticate itself.  Fundamentally, there's no way around that.  Encrypt it?  Okay, but the key then needs to be on the slave, so no ground is gained.  Hash it?  Well, no, the slave is the client.  It needs the plaintext password so it can generate a response to the server's auth challenge.  TLS?  Same problem, the slave needs a copy of the private key.

You can store it in a table instead of a file, but that doesn't really accomplish anything.

Correctly configured, though, this seems like a non-issue.

If the account the slave is using follows the principle of least privilege and is locked down to holding only the necessary privilege level and being usable from only on that one machine (e.g. GRANT REPLICATION SLAVE ON *.* TO my_repl_user@ip.of.slave.svr IDENTIFIED BY PASSWORD 'slave_pw';) then the password and the associated account is fundamentally useless for compromising the master -- it's completely invalid unless used from the slave's IP (which the master reads from the IP stack -- this isn't a value the slave sends) and even then can only request the replication stream... so where's the problem?  If the slave is compromised, the password to the master one of the least interesting pieces of information, unless the account is not tied to the slave's IP address or is a reused password.


$X Tables Do Not Exist? In troubleshooting why a database application is working infinitely better on test than production, I ran across this phenomenon: I cannot query the X$ tables in production.

SQL> desc x$ksppi
ERROR:
ORA-04043: object "SYS"."X_$KSPPI" does not exist

SQL> select * from dba_synonyms where synonym_name = 'X$KSPPI';

OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
DB_LINK
--------------------------------------------------------------------------------------------------------------------------------
PUBLIC                         X$KSPPI                        SYS                            X_$KSPPI

SQL> desc sys.x$ksppi;
ERROR:
ORA-04043: object sys.x$ksppi does not exist

On test, I actually receive the table description back.

It is my limited understanding that these are actually defined outside of the database as C structures or some such thing and are created upon startup to populate the V$ views. If my production database, specifically the optimizer, does not have access to the hidden parameters, I could definitely see why it could have a negative impact on performance.

Why wouldn't the X$ tables be created upon startup? Issues with this application began some time after it was upgraded to 11.2.0.4, so could a botched upgrade have done this? What troubleshooting steps can I take to analyze the root issue?

There is no such thing as X_$KSPPI. It is X$KSPPI. You have a synonym pointing to a non-existent object.

That synonym does not exist in Oracle databases, that was created by someone or something else.

Your attempt to desc sys.x$ksppi; fails if you do that any other user than SYS.

If your database could not access parameters, you would have a much bigger problem with more obvious symptoms.


inconsistent Data Type -ORA-00932 I know there is question related to inconsistent datatypes but that comes from a query. while
I am getting this error on database side when we try to access user defined datatype on our schema that is written in some other schema.
E.g.
we are having xxxtypes schema and we have defined several UDT there. Now in our database we are using xxxtypes.employeeList, so it is throwing following error from that line.

java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got SYSTPzM8RTCHEUy/gQAB/AQBa6A==

EDIT:
Q :Why this error is coming ? and How to overcome this ?

Examples:

create or replace
TYPE InstalmentList IS TABLE OF InstalmentEntry;

   create or replace
   TYPE InstalmentEntry IS OBJECT
   (
    reservationinstalment_id NUMBER,
    value NUMBER
   );

We have created above type in xxxtypes schema, to use it for several clients.
Now for a client's database  we are using it as :

  SELECT
          column_value AS list,
          rownum AS rownumber
        FROM
          TABLE(
            CAST(
              powermultiset(
                CAST(
                  MULTISET(
                    SELECT
                      rsv_int.reservationinstalment_id,
                      rsv_int.value
                    FROM reservationinstalment rsv_int
                    WHERE
                      rsv_int.reservation_id = p_reservation_id
                      AND
                      rsv_int.payer_type = p_payer_type
                      AND
                      rsv_int.type <> 'r'
                  )
                  AS
                  **xxxtypes.InstalmentList**
                )
              )
              AS
              InstalmentListList
            )
          )
      ) combinations

now xxxtypes.InstalmentList is throwing error saying that it is getting some inconsistent datatypes.

The type InstalmentList is a nested table of InstalmentEntry, so you need to build a set of InstalmentEntry objects to get the list.

The following query works on 11.2:

SELECT column_value AS list, rownum AS rownumber
  FROM TABLE(powermultiset(
               CAST(MULTISET (SELECT InstalmentEntry(1, 1) /*Entry "constructor"*/
                                FROM dual)
                    AS InstalmentList)
             ));


Performance and correctness of selecting only most recent entries across joined tables I have 2 tables: things and purchases. I am trying to select only the most recent purchase record for each thing.

CREATE TABLE `things` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `product_image` varchar(1024) DEFAULT NULL,
  `barcode` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `unique_barcode` (`barcode`)
) ENGINE=InnoDB AUTO_INCREMENT=643 DEFAULT CHARSET=utf8

CREATE TABLE `purchases` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `thing_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  `status` enum('IMMEDIATELY','SOON','LATER','INACTIVE') DEFAULT 'IMMEDIATELY',
  `estimated_number_of_days` int(4) NOT NULL DEFAULT '7',
  `created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `purchase_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `predicted_replace_days` int(4) NOT NULL DEFAULT '7',
  PRIMARY KEY (`id`),
  KEY `foreign_key_things` (`thing_id`),
  KEY `foreign_key_users` (`user_id`),
  CONSTRAINT `foreign_key_things` FOREIGN KEY (`thing_id`) REFERENCES `things` (`id`),
  CONSTRAINT `foreign_key_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8

The following query (based on this answer) appears to work. My questions are:

Is this actually doing what I want it to?
How can I optimize the performance of this query (indexes, etc.)?
Is there a more efficient way to do this in MySQL?

SQL:

SELECT purchases.id AS purchase_id, thing_id, purchase_date, name, predicted_replace_days, product_image, barcode
FROM (
    SELECT id, MAX(purchase_date)
    FROM purchases
    GROUP BY id
    ORDER BY purchase_date DESC
) AS sub
JOIN purchases ON purchases.id = sub.id
INNER JOIN things on thing_id = things.id
WHERE user_id = 1
GROUP BY thing_id;

Thanks!

UPDATE: I think I was overcomplicating it. Here is what looks like a simpler and more correct query:

SELECT purchases.id AS purchase_id, MAX(purchase_date) AS purchase_date, predicted_replace_days, name
FROM purchases
INNER JOIN things ON purchases.thing_id = things.id
GROUP BY thing_id

Something like this:

SELECT purchases.id AS purchase_id, thing_id, purchase_date, name, predicted_replace_days, product_image, barcode
FROM (
    SELECT thing_id, MAX(purchase_date) pd
    FROM purchases
    GROUP BY thing_id
) AS sub
JOIN purchases ON purchases.id = sub.thing_id and sub.pd=purchase_date
INNER JOIN things on thing_id = things.id
WHERE user_id = 1


How to programatically change the login audit settings? Is it possible to change the login auditing settings of a SQL Server database by script, rather than through the Server Properties Security setting in the SSMS UI?

I need to programatically change the setting to "None", and then back to "Failed logins only"

Sure, here are the commands listed below. (Reformatted from original post for reading ease.)

These can derived from clicking on Server Properties, then choosing the Security tab and changing the Login auditing settings and scripting out the code, instead of clicking on the OK button to close the dialog.  This type of scripting is usually an option on actions taken through the SSMS interface.

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\MSSQLServer\MSSQLServer',
   N'AuditLevel', REG_DWORD, 0 -- None
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\MSSQLServer\MSSQLServer',
   N'AuditLevel', REG_DWORD, 1 -- Successful Only
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\MSSQLServer\MSSQLServer',
   N'AuditLevel', REG_DWORD, 2 -- Failed only
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\MSSQLServer\MSSQLServer',
   N'AuditLevel', REG_DWORD, 3 -- Failed & Successful
GO

So, for your requirement just set the key to 0 (no auditing) then back to 2 (failed only).

But, alas, as Shawn Melton pointed out, it requires the server to be restarted before the change can be applied. Of course, you can program for restarting the server as well. Does allowing a restart (or 2 restarts) fit into your process?


SQL Server 2014 - Replica Server not available I'm new to Sql Server and want to install SQL server alwayson clustering on my test environment. I followed the steps by this site:

On each node I can connect to SQL Server but why my 2nd node is not available so that I can add it as my 2ndary Replica?
According to this site, till "Always-On Availability Group Configuration" everything was successful.
p.s. It seems I cannot add my screenshot as I do not have enough reputation.

Instead of giving the name of the other node, I gave the IP address of the other node. Then everything went well.
BUT what the reason was that it did not accept the name, I still do not know.


calculate the difference between two column aliases in a SELECT statement? I'm not sure how to do the last part of my query, which is doing calculations directly with the aliases: (SUM(task_avg - minutes) AS difference.  I don't think I can do it as easy as I'd like, but can't find a means to do it.

$result = mysql_query("SELECT U.user_name, SUM(TA.task_average*M.minutes) AS task_avg, SUM(M.minutes) AS minutes, SUM(task_avg-minutes) AS difference
                            FROM summary S
                            JOIN users U ON U.user_id = S.user_id
                            JOIN tasks TA ON TA.task_id = S.task_id
                            JOIN minutes M ON M.minutes_id = S.minutes_id
                            GROUP BY U.user_name
                            LIMIT 0 , 30");

OK. So I am going to go out on a bit of a limb here. I may have misunderstood the data model in the OP but as I understand it:

1) tasks holds a list of unique tasks

2) TA.task_average = the average time in minutes that a task takes to complete

3) users holds a list of unique users

4) summary represents users attempts at the tasks along with how long each attempt took to complete (through reference to the minutes table)

Gross assumptions out the way here is what I've come up with:

Firstly to get a list of all users, how long they took to complete each task in each attempt and the difference between each user's attempt and the task average:

select U.user_name,
TA.task_id,
TA.task_average,
M.minutes,
M.minutes-TA.task_average as diff
FROM summary S
JOIN users U ON U.user_id = S.user_id
JOIN tasks TA ON TA.task_id = S.task_id
JOIN minutes M ON M.minutes_id = S.minutes_id;

then aggregating that up to get the average difference from the task average for each user (which I think is what the OP was asking for):

select U.user_name,
avg(M.minutes-TA.task_average) as avgDiff
FROM summary S
JOIN users U ON U.user_id = S.user_id
JOIN tasks TA ON TA.task_id = S.task_id
JOIN minutes M ON M.minutes_id = S.minutes_id
group by U.user_name
limit 0,30;

If I have then wrong end of the stick then please ignore this. Thought I'd post it up anyway in case it is useful.


Nested case statements vs multiple criteria case statements Had an interesting discussion with a colleague today over optimizing case statements and whether it's better to leave a case statement which has overlapping criteria as individual when clauses, or make a nested case statement for each of the overlapping statements.

As an example, say we had a table with 2 integer fields, column a and column b. Which of the two queries would be more processor friendly? Since we're only evaluating if a=1 or a=0 once using the nested statements, would this be more efficient for the processor, or would creating a nested statement eat up that optimization?

Multiple criteria for the case statement:

Select
case
    when a=1 and b=0 THEN 'True'
    when a=1 and b=1 then 'Trueish'
    when a=0 and b=0 then 'False'
    when a=0 and b=1 then 'Falseish'
    else null
end AS Result
FROM tableName

Nesting case statements:

Select
case
    when a=1 then
        case
            when b=0 then 'True'
            when b=1 then 'Trueish'
        end
    When a=0 then
        case
            when b=0 then 'False'
            when b=1 then 'Falseish'
        end
    else null
end AS Result
FROM tablename

Someone may come along and dig into the details of how these operations are actually processed on the CPUs, or what instructions get generated differently between those two queries.  But from a practical standpoint, it seems that there's not a significant difference.

This loads up a table with 10 million rows of two bit columns, with a somewhat arbitrary distribution of 1 and 0 values.

CREATE DATABASE [239583];
GO

USE [239583];
GO

SELECT TOP 10000000
    CASE WHEN v.number % 2 = 0 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS a,
    CASE WHEN v.number % 2 = 1 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS b
INTO dbo.TableName
FROM master.dbo.spt_values v
    CROSS JOIN master.dbo.spt_values v2
    CROSS JOIN master.dbo.spt_values v3;

I then told SSMS to discard results after execution (Query -> Query Options -> Grid -> Discard results after execution), because I don't have time to wait on SSMS to crash loading 20mm rows of 1s and 0s.

Then ran this code:

SET STATISTICS IO, TIME ON;

Select
case
    when a=1 and b=0 THEN 'True'
    when a=1 and b=1 then 'Trueish'
    when a=0 and b=0 then 'False'
    when a=0 and b=1 then 'Falseish'
    else null
end
FROM tableName;

Select
case
    when a=1 then
        case
            when b=0 then 'True'
            when b=1 then 'Trueish'
        end
    When a=0 then
        case
            when b=0 then 'False'
            when b=1 then 'Falseish'
        end
    else null
end
FROM tablename;

I typical run has CPU output like this on my machine:

Query 1: CPU time = 2141 ms,  elapsed time = 2191 ms.
Query 2: CPU time = 2359 ms,  elapsed time = 2461 ms.

Sometimes they are slightly closer to equal, or the second query is slightly faster, but I think those are just the usual variations of the CPUs on my laptop doing other stuff than just running this query.


Synonym with same name as linked server I have a database that is used for reporting services. This database was created by a contractor who is no longer around. Buried all throughout the database are references to a single table on a linked server. The content on that linked server is being moved, and the server being retired.

Rather than hunting around for all of the linked server references and updating them to point at the new linked server, or changing them to use a synonym name, would it be possible (or acceptable) to create the new linked server, then add a synonym that uses the same name as the current fully qualified linked server reference, but points to the new linked server? This would allow me to leave all database code intact and minimize disruption to business users.

Here's what I'm thinking. Current linked server is SRAPP. All queries using the linked server point to OPub.dbo.ADE.

 exec sp_addlinkedserver @server=SQLProd1

Then

CREATE SYNONYM [SRAPP.OPub.dbo.ADE]
FOR SQLProd1.OPub.dbo.ADE
GO

Finally,

exec sp_dropserver 'SRAPP'

Will SQL Server get confused by a synonym name that looks identical to a linked server reference?

Did you try your idea?  What were your results?  Yes, you can create a synonym as:

CREATE SYNONYM [SRAPP.OPub.dbo.ADE]
FOR SQLProd1.OPub.dbo.ADE
GO

But you cannot use a synonym for a linked server.  Also, as of SQL Server 2016 the following limitation exists:

  Four-part names for function base objects are not supported.

Recommendation:  Drop and Recreate the Linked Server 'SRAPP'.  You can help yourself by scripting out the existing definition. Drop the existing linked server and recreate it using the new server name.

As of SQL Server 2016 synonyms can only be defined for:

Assembly (CLR) Stored Procedure
Assembly (CLR) Table-valued Function
Assembly (CLR) Scalar Function
Assembly Aggregate (CLR) Aggregate
Functions Replication-filter-procedure
Extended Stored Procedure SQL
Scalar Function
SQL Table-valued Function
SQL Inline-table-valued Function
SQL Stored Procedure
View
Table (User-defined)

Yes, there are many things that we can conceive about synonyms, but they do not yet exist.


How do I use a stored procedure in SQL Server 2014 to call a external XML source? I am attempting to retrieve currency exchange rates from http://www.floatrates.com/daily/usd.xml using a stored procedure in SQL Server 2014. The result is in XML. So I created the following stored procedure:

--DROP PROCEDURE [dbo].[proc_CallWebService]
CREATE PROCEDURE [dbo].[proc_CallWebService]
AS
    DECLARE @obj INT
    DECLARE @sURL VARCHAR(200)
    DECLARE @response varchar(max)

    SET @sURL = 'http://www.floatrates.com/daily/usd.xml'

    EXEC sys.sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
    EXEC sys.sp_OAMethod @obj, 'Open', NULL, 'GET', @sURL, FALSE
    EXEC sys.sp_OAMethod @obj, 'send'
    EXEC sys.sp_OAGetProperty @obj, 'responseText', @response OUT

    SELECT @response [response]
    EXEC sys.sp_OADestroy @obj
RETURN

and when I run this stored procedure, I get NULL in return instead of the XML.

What do I need to change in my stored procedure to get an XML response from that site mentioned above?

EXEC sys.sp_OAGetProperty @obj, 'responseText', @response OUT

sometimes doesn't return the output parameter.  Particularly if the response XML is longer than your @response variable.

Try:

declare  @result table(data text)

INSERT into @result (Data) exec sp_OAGetProperty @obj, 'responseText'

select * from @result


mysql - query to fill in missing datetime values I have a Table similar to:

CREATE TABLE `stats` (
    `stat_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `stat_Start` timestamp NULL DEFAULT NULL,
    `stat_values` smallint(5) unsigned DEFAULT NULL,
    PRIMARY KEY (`stat_ID`),
    );

It contains a list of events with the time it happenned, and the value.

I am now looking to generate a report from this similar to:

SELECT
    LEFT(stat_start,16) AS `time`,
    AVG(`stat_values`) AS `data`
FROM stats s
WHERE s.stat_Start BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND NOW()
GROUP BY LEFT(s.stat_start,16)
ORDER BY stat_start;

In other words, get the Average value for all entries per minute for the past hour.

However, in some cases I don't have an entry for some minutes, but I still need to know that there were 0 results for that minute.
Is there a way of achieving this in MySQL?

so I might get something like:

'2017-04-07 09:01', 102,
'2017-04-07 09:02', 0, --> row for which no events were recorded
'2017-04-07 09:03', 132

Filling in 'missing' rows is done via an extra table with all possible values, plus a LEFT JOIN:

Create have a table all_minutes with all possible minutes (in some finite range).  (If using MariaDB, use a "Sequence" table.)  Then

SELECT a.time,
       IFNULL(s.data, 0)  AS `data`  -- assuming you want 0, not NULL
    FROM all_minutes AS a
    LEFT JOIN ( SELECT
            LEFT(stat_start,16) AS `time`,  -- eg, '2017-04-07 11:03'
            AVG(`stat_values`) AS `data`
        FROM stats
        WHERE stat_Start BETWEEN NOW() - INTERVAL 1 HOUR AND NOW()
        GROUP BY `time`
        ORDER BY `time`    -- note a change here
              ) AS s    ON s.time = a.time
    WHERE a.time BETWEEN ... AND ...; -- whatever range you need

Note:  Both tables should exclude or include the :00 for seconds.  Otherwise (I think) there could be some difficulty in doing the = in the ON.


‘PRIMARY’ filegroup is full. How do I increase temdb size in SQL Server Express 2017? When I reindex a particular table

DBCC DBREINDEX(@TableName, '',90)

I get an error

  Level 17, State 2, Procedure aareindex, Line 21 [Batch Start Line 0]
  Could not allocate space for object ‘dbo.SORT temporary run storage:
  422738479742976’ in database ‘mydatabase’ because the ‘PRIMARY’
  filegroup is full. Create disk space by deleting unneeded files,
  dropping objects in the filegroup, adding additional files to the
  filegroup, or setting autogrowth on for existing files in the
  filegroup.

So I want to try increasing the size of TempDb

I can see the size using SSMS by looking at the database properties but I don't see how to set it.

[Update]

select type_desc, name, size, max_size, growth from tempdb.sys.database_files

returns

The file size is already 10236 Mb and the Express limit is 10 Gig
However the Disk Usage report shows that 26% of the diskspace is unallocated, 40% is data and 32% is index.

You do not need to increase the size of temp db.

  in database ‘mydatabase’ because the ‘PRIMARY’ filegroup is full.

Is telling you it can't write to your primary filegroup is full (c:\path to database\mydatabase.mdf).  Either there is no disk space where the MDF is stored, or the more likely cause is that your Primary Filegroup has no more available room to write into.

You can address this a few different ways

Enable autogrowth if not enabled
If autogrowth is enabled increase the maximum size by MB's, percentage or unlimited

In the GUI

In SSMS:

right click your database
select properties
select files in the database properties window
On the Autogrowth Max Size select your auto growth options.

SQL Alter statement

use master
go
-- Use one of the statements below

-- this statement will allow for unlimited file growth
alter database [mydatabase] modify file ( name ='mydatabase', maxsize = UNLIMITED)

-- this statement will allow the database size to 100MB's.
alter database [mydatabase] modify file ( name ='mydatabase', maxsize = 102400KB)


Can i set Accent insensitive for individual languages? Can i make a table/column like this:

Accent sensitive for Turkish and English.
Accent insensitive for Arabic.

Actually if this can be done on a full-text index, that would also work for me.

Accent does matter for Turkish but doesn't matter that much for Arabic.

And I don't want to compose individual tables for each table if possible.

No, this is not how Collations work. Not only are the various sensitivities (case, accent, width, and Kana) not per Language, you can't even get more than one Language at a time per column or expression. The issue is that multiple languages may share one or more characters. Even if the languages you are concerned about using have entirely distinct characters (e.g. Arabic vs English), you can only specify one locale at a time (even at the OS level and other software -- I've never seen anything allow for multiple locales in a single column / entity / object).

I would suggest picking the Collation that will be used most often as how the column is defined, and then for the other one or two you can create computed columns, defined as simply being the column you are dealing with here -- but with the appropriate Collation -- and defining the Full Text indexes on the various columns. For example:

CREATE TABLE #Temp
(
  ID INT,
  Col1 NVARCHAR(50) COLLATE Latin1_General_100_CI_AS_SC,
  Col2 AS [Col1] COLLATE Turkish_100_CI_AS_SC [PERSISTED],
  Col3 AS [Col1] COLLATE Arabic_100_CI_AI_SC [PERSISTED]
);

I'm not sure if Full Text Search will require that the computed columns be defined as PERSISTED, so you might want to try with and without it to see not only what is allowed, but also which performs better. This approach will at least make it easy to keep the values in sync as they only truly exist in one place.


is it a good practice to ip block dev/test app servers from production database server? I would like to do this as somewhat of a last line of defense as we will be replicating production to test regularly.  Is this good practice?

Not only is it a best practice, it's mandatory to any major compliance requirements such as PCI V2 & V3, SOX, and almost any auditor will ask about it. Yes, having them on the same network is asking for major problem.

Page 5 of the PCI DSS V2:

For example, there must be a clear segmentation of functions and segregation
of networks with different security levels; segmentation should
prevent the sharing of production and test/development environments;

Page 32: 6.4.1 Separate development/test and production environments.


How does SQL Server perfom lock when using CROSS APPLY? The best way I think I can ask my question is by starting with an example.

Imagine these two queries :

SELECT CAR.SerialNumber, TIRE.SerialNumber
FROM dbo.tCar AS CAR
INNER JOIN dbo.tTire AS TIRE ON ( TIRE.CarID = CAR.ID)
WHERE CAR.Brand = 'Jaguar'

and

SELECT CAR.SerialNumber, TIRE.SerialNumber
FROM dbo.tCar AS CAR
CROSS APPLY (
    SELECT TIRE.SerialNumber
    FROM dbo.tTire AS TIRE
    WHERE TIRE.CarID = CAR.ID
) AS TIRE
WHERE CAR.Brand = 'Jaguar'

NOTE: tTire.CarID is indexed and tCar.Brand is indexed

These two would return the same output.

I know the second query is just foolish as it stand, but again, it just serve as an example for my question.

Now, I guess (even if I don't know the details) SQL Server will perform a clever and limited lock on the data read by the first query (JOIN).

But I'm wondering how it will perform lock for the data read by the second query (APPLY), mainly (and obviously imo) on the "tTire" data read.

In case of APPLY how do SQL Server perfom lock?

EDIT: This example is so simple it seems SQL Server manage to create the same execution plan, but my "APPLY" question should be though in terms where a RBAR effect occurs.

A query is not an executable program. The SQL query specification language enables us to define the logical results of a query, but it is up to components like the query optimizer and execution engine to take care of the physical processing involved in delivering those results.

In principle, the physical processing side of things is free to perform whatever operations it likes, so long as the data returned matches the query specification. It might use hashing, scanning, sorting ... anything it likes, so long as the results match the logic of the query.

For example, the optimizer might well produce a very similar (or even identical) user-visible execution plan for both your sample queries, because they logically describe the same result. On the other hand, it might not; and it does not really matter either way.

The second major factor here concerns the transaction isolation level that the query is executed at. The different isolation levels available each provide different guarantees regarding the effects on one query due to concurrent data modifications by another.

Locks are just one possible mechanism by which these isolation guarantees might be enforced by the database engine. Other isolation implementations that do not use locks at all are available, in SQL Server as well as most mainstream products.

The point is that the engine guarantees to respect the requested isolation level, and it may do so using locks, or it may not. These details may be important sometimes from a practical point of view (for example when blocking occurs) but they are not truly fundamental.

As a result of all this, there is usually no direct relationship between the original written form of the query and the locks that might be taken - there's just too much other 'stuff' going on in between.

  Isn't there even known "tendencies" of the SQL Server's behavior when it deals with RBAR scenarios? Something to be aware of maybe?

That comes with experience, though that is not universally a good thing. Certainly there is plenty of advice out there, though some is outdated, or based on a misconception.

In SQL Server, we can often safely encourage "set-based thinking" and say cursors, loops, and row-by-row processing should most often be avoided, but that is not the whole story, though this is all going a bit beyond your original question.

My usual advice is to write good queries first (using whatever syntax you find most natural) against well-designed and maintained databases, then worry about any specific issues like locking (optimizer limitations, plan shape issues etc.) if and when they arise. People do sometimes learn to avoid things like 'APPLY' for the wrong reasons, and have to unlearn it all later.

In general, I am pretty skeptical of advice that says to always prefer one syntax element or construction over another.

Resources:

Lesson 2: Understanding Logical Query Processing - Microsoft Press
Basics of Transaction Isolation Levels - SQL Server Customer Advisory Team
SQL Server Isolation Levels - My series of articles


GROUP BY and ORDER BY problem I have two tables like this:

CREATE TABLE cmap5 (
   name     varchar(2000),
   lexemes  tsquery
);

and

CREATE TABLE IF NOT EXISTS synonyms_all_gin_tsvcolumn (
   cid       int NOT NULL,  -- REFERENCES pubchem_compounds_index(cid)
   name      varchar(2000) NOT NULL,
   synonym   varchar(2000) NOT NULL,
   tsv_syns  tsvector,
   PRIMARY KEY (cid, name, synonym)
);

My current query is:

SELECT s.cid, s.synonym, c.name, ts_rank(s.tsv_syns,c.lexemes,16)
FROM synonyms_all_gin_tsvcolumn s, cmap5 c
WHERE c.lexemes @@ s.tsv_syns

And the output is:

cid     |  synonym                              | name (query)              | rank
5474706 | 10-Methoxyharmalan                    | 10-methoxyharmalan        | 0.0901673
1416    | (+/-)12,13-EODE                       | 12,13-EODE                | 0.211562
5356421 | LEUKOTOXIN B (12,13-EODE)             | 12,13-EODE                | 0.211562
 180933 | 1,4-Chrysenequinone                   | 1,4-chrysenequinone       | 0.211562
5283035 | 15-Deoxy-delta-12,14-prostaglandin J2 | 15-delta prostaglandin J2 | 0.304975
5311211 | 15-deoxy-delta 12 14-prostaglandin J2 | 15-delta prostaglandin J2 | 0.304975
5311211 | 15-deoxy-Delta(12,14)-prostaglandin J2| 15-delta prostaglandin J2 | 0.304975
5311211 | 15-Deoxy-delta-12,14-prostaglandin J2 | 15-delta prostaglandin J2 | 0.304975
5311211 | 15-Deoxy-delta 12, 14-Prostaglandin J2| 15-delta prostaglandin J2 | 0.304975

I would like to return the name matches of all rows in cmap5 in my main table ranked by the ts_rank() function but for each row in cmap5 I want to:

select only the best X cids to each query (group by cid)
or ORDER BY my results as 1+ts_rank/count(cid)

To get the best match I tried to add select distinct on c.name, but when the rank is the same I want to get the cid with more matches to the query. I have tried adding a simple group by at the end of the query but I get an error, how could I do this?

Added comments:

On one hand for those results whose rank is the same, eg. above 5283035 and 5311211, get 5311211 as top result because that cid has more hits than 5283035, so I sort of want to take into account the number of hits / cid in the rank, like final_rank = 1+ts_rank(cid)/no. of hits(cid).

On the other hand I want to get the first X cids per query name. If I use LIMIT X it returns the first X results of the entire query table, not the first X per name (row) of the query table as I want.

First off, your PRIMARY KEY spanning two varchar(2000) columns seems extremely expensive. If you use your PK for anything else I suggest a surrogate PK (use a serial column) and add a UNIQUE constraint to enforce uniqueness on (cid, name, synonym).

If one of your varchar columns actually uses the maximum length you would exceed the maximum size for an index entry. See:

Character varying index overhead & length limit

I guess what you want is this, because it would make sense:

SELECT DISTINCT ON (c.name)
       c.name, min(s.synonym) AS min_synonym, s.cid
     , ts_rank(s.tsv_syns, c.lexemes, 16) AS rnk
     , count(*) AS ct
FROM   synonyms_all_gin_tsvcolumn s
JOIN   cmap5                      c ON c.lexemes @@ s.tsv_syns
GROUP  BY c.name, rnk, s.cid
ORDER  BY c.name, rnk DESC, ct DESC;

I use explicit [INNER] JOIN with attached join condition replacing your CROSS JOIN plus WHERE clause. It's generally considered superior (easier to read and debug). I also use rnk as column name to avoid the basic function name rank as identifier.
Group the results per c.name that have the same rnk and s.cid, take min(s.synonym) (for lack of definition in the question), and count(*) the peers per group.
Reduce to one row per c.name with DISTINCT ON (Postgres specific extension of SQL standard DISTINCT), taking the highest rank first and, within same rank, the highest peer count. See:
Select first row in each GROUP BY group?

Combining GROUP BY and DISTINCT ON this way in one query level is possible since DISTINCT or DISTINCT ON are applied after GROUP BY.