texts: SQLServer: Backup a database on a server and restore it on another server

texts:
I would like to create a process where everyday or every week I backup a database from my production server and restore it on my staging server for testing purpose.

My first idea was to create a Maintenance plan but maintenance plan can only backup. When I backup the backup system also generate name including the date a time of the backup.

On my file system I can find my backup under:

\\ServerProd\d$\Database Backup\MyDb\MyDb_backup_2018_11_13_152440_3319782.bak

Now from my ServerStaging I cannot use a Maintenance Plan because there is no restore database option. So I'm going to create a job. Step 1 of my job I kill all connection on existing database and I drop it. I don't care it is staging. Step 2 I restore:

RESTORE DATABASE MyDb FROM DISK = '\\ServerProd\d$\Database Backup\MyDb\MyDb_backup_2018_11_13_152440_3319782.bak'

  Msg 3201, Level 16, State 2, Line 1 Cannot open backup device
  '\ServerProd\d$\Database Backup\MyDb\MyDb_backup_2018_11_13_152440_3319782.bak'. Operating
  system error 5(Access is denied.). Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

And now I'm lost. No need to tell you I'm not use to database administration.


texts: what better way to multiple ON conditions

texts:
I've got a table called Customers. And a table Users. (MySQL 5.3)

Customers:

customerID|customerName|accountManagerID|attendantID
123       |Google      |456             |789
124       |Apple       |457             |890

Users:

userID|userName
456   |Peter Norton
457   |Paul Allen
789   |Bill Gates
890   |Steve Jobs

I would like to be able to get the following result:

ID |Customer|Manager     |Attendant
123|Google  |Peter Norton|Bill Gates

I can get by using the following query:

SELECT customerID AS 'ID'
    ,customerName AS 'Customer'
    ,u1.accountManagerID AS 'Manager'
    ,u2.attendantID AS 'Attendant'
FROM Customers c
LEFT JOIN Users u1 ON (u1.customerID = c.customerID)
LEFT JOIN Users u2 ON (u2.customerID = c.customerID)

But I do think horrible do it this way, moreover, I consider 'little performatic'. Can someone point me to the right direction? Thanks!


texts: Query turned into a CPU gobbling monster

texts:
I have a SP who's execution time went from 5 minutes > 20 minutes > 30 minutes > 53 minutes over four days

Waits were showing increasing CPU and suspended status

I isolated a single query that pegs the CPU

UPDATE thing.table
SET YYYYMM =
CASE
  WHEN
    DAY(SnapshotDate) = 1
    OR
  SnapshotDate = (SELECT MAX(SnapshotDate) FROM thing.table)
  THEN CAST(FORMAT(DATEADD(day,-1,snapshotdate),'yyyyMM') AS INT)
  ELSE NULL
END

I ran it again, adding WITH (RECOMPILE) at the end - no difference

I ran UPDATE STATISTICS thing.table - no difference

It would be interesting to run it and get the actual plan but I don't want to peg the CPU for an hour. I checked sys.dm_exec_cached_plans but it appears to only have the estimated plan not the actual plan

I rewrote using CONVERT instead of FORMAT (because I am suspicious of new things) - no difference

So I rewrote like this and took execution back down to a few seconds:

BEGIN TRAN;

UPDATE thing.table
SET YYYYMM = NULL;

UPDATE thing.table
SET YYYYMM = CAST(FORMAT(DATEADD(day,-1,snapshotdate),'yyyyMM') AS INT)
WHERE
(
DAY(SnapshotDate) = 1
OR
SnapshotDate = (SELECT MAX(SnapshotDate) FROM thing.table)
);

COMMIT TRAN;

The table has about 150,000 records in it. It's quite possible that it recently had a lot more records dumped in it, skewing statistics, but why would WITH(RECOMPILE) and UPDATE STATISTICS not fix that?. It takes a daily snapshot and possibly the number of records increased due to end of month.

So the questions are:

Is actual query plan stored anywhere?, to save me running it interactively?
Normally when a query suddenly takes forever it stale statistics but that doesn't seem to be the case here

This is my version of SQL Server

    Microsoft SQL Server 2014 - 12.0.4100.1 (X64)   Apr 20 2015 17:29:27   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

Here are the slow and fast query plans. No suprise they are different because they are doing different things:

Slow Plan:

Fast Plan:

I notice slowpoke uses an loop join and fasty uses a hash match.

I notice the small leg of the loop join has filter

[Expr1006]=DB.thing.table.[SnapshotDate]. Maybe that wasn't so small anymore?


texts: Unable to trace "Arithmetic overflow error converting numeric to data type numeric" error in profiler

texts:
I have a .net application. Its trying to create tables, views and insert/update data into many tables. while application running i got an error.

I tried to trace the query in SQL Profiler, but i didn't find any error. Is there any way to track the Error statement or Is there any other events i need to add in profiler.

I tried already following events (exception):

RPC: Completed
RPC: started
SP: started
SP: Completed
SP: Stmtstarted
SP: StmtCompleted
SQL: Batchstarted
SQL: BatchCompleted


texts:
I need to delete a column named "column" form a table but the standard:

mysql> ALTER TABLE table_name DROP COLUMN 'column';

returns:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''column'' at line 1

I am thinking "column" is reserved word and shouldn't be used. But how do I get around to deleting it ?


texts:
I have set up master slave replication in mongo. It works fine for a few months, then suddenly replication fails. I have started it again with initial sync but during sync after a specific point it will again start resync and this process goes on and on, and slaves have not complete their initial sync.

The log file contains initial sync pending and again start resync again and again.


texts: How to write double Natural Join in SQL?

texts:
Let be the following database :

  Product(Constructor, Model, Type)
  PC(Model, Speed, RAM, HD, CD, Price)
  Laptop(Model, Speed, RAM, HD, Screen, Price)
  Printer(Model, Color, Type, Price)

  Who are the constructors giving printers and PC ?

I think that the answer in relational algebra is :

ΠConstructor(Product ⋈  Laptop ⋈  Printer)

Yet I don't know how to write it in SQL, would it be :

SELECT Constructor
FROM Product NATURAL JOIN Laptop NATURAL JOIN Printer;


texts: What's the least risky way to Select on production environment?

texts:
My goal is to use selects on production without affecting anything else.

When I was new to SQL, I just wrote simple selects, but later I learned this can cause locks.

If I don't care about dirty reads, I can use nolocks on every select or set transaction isolation level to read uncommited.

But I do care about dirty reads, so I went with isolation level snapshot.
Isolation level snapshot writes into tempdb and I don't know if that's a problem or not (extra writing to database means slower DB and less space?)

So what is the least intrusive way to write a select on DB, where I cannot afford slowing it down or creating shared locks? Should I go back to isolation level read uncommited on every query?

This is asked from developer point of view. I don't know details about DB settings, but want to use selects in a way that will cause the least trouble.


texts:
I have one doubt on master to slave replication in streaming replication in postgresql. I confgured ONE Master and One Slave with streaming replication in my system If Master fails slave act as a master then it is fine.

My Question:
1) who will be the master and who will be the slave when master fails? 2) when slave becomes as a master how much time it will take to get it back fail-over matster as slave?

can please give me the correct scenario for this?


texts: How to/Where to find MySQL templates for Toad Data Modeler

texts: I have recently install Toad Data Modeler 5.3.4 and it only has Oracle data model templates. Where can I find and import the MySQL data templates so that I can create MySQL database models. I have Googled and looked on the Toad World website.


texts: MySQL queries to some tables in information_schema kill all connections to server

texts:
Running this query:

SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS

kills all connections to the server.

If I add a condition like where table_name = 'some table' it does not.

MySQL command line client gives this error:

ERROR 2013 (HY000): Lost connection to MySQL server during query

A jdbc-connected client gives this:

The last packet successfully received from the server was 766 milliseconds ago.  The last packet sent successfully to the server was 724 milliseconds ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1121)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3603)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3492)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4043)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2809)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2758)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:894)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:732)
    at \\.\\.\\हिñçêČάй語简�?한\\.mꆯᜫꋱꐚassert.execute(Unknown Source)
    at \\.\\.\\हिñçêČάй語简�?한\\.lꑰꋘꏇᠣpublicbreak.a(Unknown Source)
    ... 1 more
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3052)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3503)
    ... 11 more

Things I've tried that I thought sounded relevant based on other people's questions that seemed similar:

grant all on . / flush privileges / restart
installing a newer patch level of mysql
repairing the table (it's more than just this one, but trying to repair gives the same error)

Any ideas on how to fix this?


texts: How to combine Oracle RAC with in-memory database to isloate OLAP from OLTP

texts:
I am going through a document
https://www.oracle.com/technetwork/database/options/database-in-memory-data-sheet-2338744.pdf
There is a thing written "Oracle Real Application Clusters (RAC) can be combined with Oracle Database InMemory to isolate analytic workloads from OLTP workloads by running them on different
servers of a cluster"
Can anyone explain how would it be possible as RAC has the same storage under it and how can different nodes take responsibility of OLAP and OLTP workload independently


texts:
I have a table :

[sch].[Parent]
(
     ID Identity [INT] (PK),
     Name VARCHAR(100),
     Email VARCHAR(250),
     Active TINYINT,
     InsertedBy VARCHAR(100),
     InsertedDTM DATETIME,
     UpdatedBy VARCHAR(100),
     UpdatedDTM DATETIME,
)

This table has PK on the ID column and a UNIQUE NONCLUSTERED CONSTRAINT on Name.

When I query a normal SQL without a WHERE clause:

SELECT ID, Name
FROM sch.Parent

This query uses a Index Scan and this is the case when I use a INNER JOIN with another CHILD table which has an FK in another table

What covering index should I use so that there is Index Seek in case of normal query and INNER JOIN with another table as well?

SELECT
     b.Id AS 'ParentsChildId'
    , b.[Name] AS 'ParentsChildName'
    , a.Id AS 'ParentId'
    , a.[Name] AS 'ParentName'
FROM
    [sch].[ParentsChild] b
INNER JOIN
    [sch].[Parent] a
ON  a.Id = b.Id
WHERE
    a.Active = 1 AND b.Active = 1


texts: SQL Server query result transfer time over VPN (WAN)

texts:
I have a mysterious phenomenon on one remote SQL Server 2005 which I manage and query using SQL Server Management Studio. I connect to the server through a VPN tunnel (provided by an ISP, about 8 MBit/s up and down).

When I open a new query window (first connection) the result is quickly transferred to the client. Let's say < 1 sec. When I repeat the query a few times, the results transfers are getting slower, up to 5 sec and more. It has nothing to do with the query execution time, it's just the transfer time to the client (SSMS).

Now, even stranger: I opened a second query window to the same server/database, the query result transfer is always fast even if I run them at the same time. Even after two hours the results are always transfered very quickly in that other window, while it takes much longer in the first query tab.

I'm using this testing query in order to avoid some potential caching issues:

select top 2000 newid() from <SOMETABLE>

I tried to change the server registration's connection packet size, but it didn't change much.

What could cause the problem?

Update: as suggested in the comments I cross posted on Serverfault.com.


texts:
I have an SQL Server Agent job set up to run every minute on the minute.

Usually it runs exactly at :00 on the clock but occasionally it waits several seconds.

See below, one of the jobs was triggered to run at 6:32AM, but only actually started running 9 seconds later.

I suppose there are many reasons this could happen, but my question is: Is there any way that I can pinpoint what is causing this (as opposed to just guessing, making a change, and hoping that fixes it)?

I read this question and answer, but this is not the issue in my case. I do have other SQL Server Agent jobs, but no other jobs were running at 6:32, and also there are no other jobs that touch any of the tables involved in this particular job. When I tried running the query in the accepted answer of that question, it always resulted in 0 rows of data.


texts: SQL Server Agent stopped - Event ID 17052

texts:
I've had back to back Saturdays where our SQLServerAgent (Test Instance) has stopped with an Event ID of 17052. The times do differ as the initial week was 21:00 and last Saturday was 19:00. Having checked the event log, I get the below messages. After checking the SQL Server Agent job history, there were no job failures logged. The Server hosts multiple instance and all other services on the instance were not affected. Please can someone suggest what would cause SQL Server Agent to stop based on the below?

Select @@version

  Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) - 12.0.5207.0 (X64) Jul 3 2017 02:25:44 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

Window Application Event Log:

Error Message No 1:
    The description for Event ID 17052 from source MSSQL$TEST cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

    If the event originated on another computer, the display information had to be saved with the event.

    The following information was included with the event:

    SQLServerAgent Monitor: SQLServerAgent has terminated unexpectedly.

    the message resource is present but the message is not found in the string/message table

Error Message No 2:

    The description for Event ID 17052 from source MSSQL$TEST cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

    If the event originated on another computer, the display information had to be saved with the event.

    The following information was included with the event:

    SQLServerAgent Monitor failed to restart SQLServerAgent after SQLServerAgent terminated unexpectedly (reason: SQLSCMControl() returned error 5, 'Access is denied.').

    the message resource is present but the message is not found in the string/message table

SQL Server Error Log:

    Date        11/08/2018 19:00:19
    Log     SQL Server Agent (Archive #1 - 11/08/2018 19:00:00)

    Message
    [165] ODBC Error: 0, COUNT field incorrect or syntax error [SQLSTATE 07002] (ConnExecuteCachableOp)

    Date        11/08/2018 19:00:09
    Log     SQL Server Agent (Archive #1 - 11/08/2018 19:00:00)

    Message
    [298] SQLServer Error: 16389, Communication link failure [SQLSTATE 08S01]

    Date        11/08/2018 19:00:09
    Log     SQL Server Agent (Archive #1 - 11/08/2018 19:00:00)

    Message
    [298] SQLServer Error: 121, Communication link failure [SQLSTATE 08S01]

    Date        11/08/2018 19:00:09
    Log     SQL Server Agent (Archive #1 - 11/08/2018 19:00:00)

    Message
    [298] SQLServer Error: 121, TCP Provider: The semaphore timeout period has expired. [SQLSTATE 08S01]


texts: In Search of FILESTREAM Insider Information

texts:
When the FILESTREAM feature is activated on Microsoft SQL Server 2012 then SQL Server will create a "hidden" share on the system. The share is defined as follows:
Sharename          FILESTREAM_SHARE
Path               \\?\GLOBALROOT\Device\RsFx0320\<localmachine>\FILESTREAM_SHARE
Remark             SQL Server FILESTREAM share
Maximum users      unlimited
Users Caching      Manual caching of documents
Permissions        NT-AUTHORITY\Authenticated Users, FULL

The name is the name of the share you provide when initially configuring FILESTREAM in the SQL Server Configuration Manager. But what is it for?
So far
I read through all the available FILESTREAM documentation starting at:

FILESTREAM (SQL Server)
Enable and Configure FILESTREAM
Create a FILESTREAM-Enabled Database
Create a Table for Storing FILESTREAM Data
... and everything related
FILESTREAM Compatibility with Other SQL Server Features
FILESTREAM DDL, Functions, Stored Procedures, and Views
Access FILESTREAM Data with OpenSqlFilestream
the paper SQL Server 2008 - FILESTREAM Storage in SQL Server 2008
and the article FILESTREAM (OLTP)---a Technical Reference Guide for Designing Mission-Critical OLTP Solutions which references a lot of other material

...but there was no mention of the share and what it does or what it is for. You enter the name and SQL Server will create the share under-the-hood.
FILESTREAM-enabled database
When you create a FILESTREAM-enabled database, the database references a filegroup which references a directory (recommended on a separate drive) that has absolutely nothing to do with the share, which was initially created during FILESTREAM configuration.
Screenshot FILESTREAM-enabled database script

Yes, I do realise that all paths are on C:; it is just an example
The white paper by Paul Randall et al. goes on to explaing that...

FILESTREAM data is stored in the file system in a set of NTFS directories called data containers, which correspond to special filegroups in the database. Transactional access to the FILESTREAM data is controlled by SQL Server and a file system filter driver that is installed as part of enabling FILESTREAM at the Windows level. The use of a file system filter driver also allows remote access to the FILESTREAM data through a UNC path. SQL Server maintains a link of sorts from table rows to the FILESTREAM files associated with them. This means that deleting or renaming any FILESTREAM files directly through the file system will result in database corruption.

... further down the document (page 14) they carry on with ...

There is a single FILESTREAM file system filter driver for each NTFS volume that has a FILESTREAM data container, and there is also one for each version of SQL Server that has a FILESTREAM data container on the volume. Each filter driver is responsible for managing all FILESTREAM data containers for that volume, for all instances that use a particular version of SQL Server.
For example, an NTFS volume that is hosting three FILESTREAM data containers, one for each of three SQL Server 2008 instances, will have only one SQL Server 2008 FILESTREAM file system filter driver.

Questions

It's nice to know that SQL Server has everything nice and tied up, but what does that share actually do? Is it the so called "file system filter driver"?
Seeing as any authenticated user can access the "share", what are the security implications?
Is the Device RsFx0320 a predecessor to the resilient file system format that was introduced with Windows Server 2012?

If you can supply answers to my questions, then it would be nice if you could provide a source reference.


texts: How to send email from SQL Jobs in SQL Server 2014

texts: How to send email from SQL Server Jobs in SQL Server 2014. I want to implement email notification through stored procedure instead of Windows services.


texts: Select column name dynamically based on rows from another table in SQL server

texts:
I want to select column name from another table row value in single query.

CREATE TABLE [dbo].[DynamicFieldsMaster](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NULL,
    [DisplayName] [nvarchar](max) NULL,
    [Type] [nvarchar](max) NULL,
 CONSTRAINT [PK_DynamicFieldsMaster] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Id    Name    DisplayName Type
1 Field1  ID  int
2 Field2  NAME    string
3 Field3  EMAIL   string
4 Field4  ADDRESS textarea

And my Another table is ...

CREATE TABLE [dbo].[DynamicFieldsValue](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CustomerID] [int] NULL,
    [Field1] [int] NULL,
    [Field2] [nvarchar](max) NULL,
    [Field3] [nvarchar](max) NULL,
    [Field4] [nvarchar](max) NULL,
    [Field5] [bit] NULL,
 CONSTRAINT [PK_DynamicFieldsValue] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Id    CustomerID  Field1  Field2      Field3     Field4      Field5
1       1         101     testField2  testField3 testField4   1
2       2         1       tert        erter      terter       1
3       3         7       tertretert  ertert     erterertert  1
4       4         12      ertert      erter      tertr        1
5       5         11      add         jhgjh      jhghjghjghj  1

and i want result like

SELECT *, (SELECT v.Field1(dynamic column name)  FROM DynamicFieldsValue v WHERE CUSTOMERID =1 ) VALUE FROM [dbo].[DynamicFieldsMaster]  DM

For customerid =1 i need to retrive all data from dynamicfieldvalue table

Id  Name    DisplayName Type    VALUE
1   Field1  ID  int             101
2   Field2  NAME    string      testField2
3   Field3  EMAIL   string      testField3
4   Field4  ADDRESS textarea    testField4
5   Field5  GENDER  boolean     1

any one please help me


texts: What difference between these 2 SQL styles?

texts:
If I have a simple table named: conn_log in my Redshift/Postgresql

In the performance of view, what difference between these two commands ?

select t1.wifi_name, (t2.sconn*100)::numeric/t1.ttconn
from (select wifi_name, count(*) as ttconn
      from conn_log
      group by wifi_name) t1,
     (select wifi_name, count(*) as sconn
      from conn_log
      where success = 1
      group by wifi_name) t2
where t1.wifi_name = t2.wifi_name;

second query:

select t1.wifi_name, (t2.sconn*100)::numeric/t1.ttconn
from (select wifi_name, count(*) as ttconn
      from conn_log
      group by wifi_name) t1
join
     (select wifi_name, count(*) as sconn
      from conn_log
      where success = 1
      group by wifi_name) t2
on t1.wifi_name = t2.wifi_name


texts: Mongo error : Could not lock auth data update lock. on root user

texts:
i have a mongodb on 2.6.9.
my connected user is root:

mongo --username myadmin --password thepassword --port  27018 --host 127.0.0.1 admin
MongoDB shell version: 2.6.9
connecting to: 127.0.0.1:27018/admin
mongos> db.getUsers()
[
{
    "_id" : "admin.myadmin",
    "user" : "myadmin",
    "db" : "admin",
    "roles" : [
        {
            "role" : "root",
            "db" : "admin"
        },
        {
            "role" : "dbAdminAnyDatabase",
            "db" : "admin"
        },
        {
            "role" : "readAnyDatabase",
            "db" : "admin"
        },
        {
            "role" : "clusterAdmin",
            "db" : "admin"
        }
    ]
},

So i am root, but when i want update a user:

mongos> db = db.getSiblingDB('myuser')
myuser
mongos> db.updateUser(  'myuser' ,{ pwd: 'hg76vZa091', roles: ['dbOwner','dbAdmin','readWrite'] } )
2015-10-06T09:00:00.100+0000 Error: Updating user failed: Could not lock auth data update lock. at src/mongo/shell/db.js:1155

So i try to grant userAdmin to myadmin, and it's not working.

mongos> use admin
mongos> db.grantRolesToUser(     "myadmin",     [       { role: "userAdminAnyDatabase", db: "admin" }     ] )
2015-10-06T08:46:40.987+0000 Error: Could not lock auth data update lock. at src/mongo/shell/db.js:1287

Root user cannot grand user ? cannot update user ? How i can do it ?
Maybe i miss some query in my script. I try

use admin

or
    use root

before grant or update user, but it's not working. Any idea?


texts: Joining tables with one to many relationship

texts:
Hi I have a service ticketing system database (SQL Server 2008) which i want to get the hour elapsed for the tickets. One table contained the Ticket number and the created date/time while the other table contained the status log of each ticket.  My problem is how to get the time elapsed considering the last update from the other table.  Here is a sample table.

Table1
------
Ticket_Number   created
1234            06-12-2014 08:00:00

Table2
------
Ticket_Number        statuschange
1234              06-12-2014 08:30:00
1234              06-12-2014 08:45:00
1234              06-12-2014 10:00:00


texts:
I'm currently trying to learn MySQL and have a problem that I was unable to solve.

I started to use the query window to give certain commands like:

CREATE DATABASE ijdb;

The problem is that whenever I do a command in the Query Window, I get the following:

Any hints on how to debug this? Tried numerous wamp servers on windows and all of them give me the same pop-up when trying to use PHPMyAdmin Query Window.

If It helps:
I'm currently using : BitNami Wamp Stack 5.4.24-0 and latest version of Google Chrome.

Thanks in advance!


texts: MySQL replication setup for high load Master

texts:
We have simple replication set up with 1 master and 1 slave.
However, setting this up (after restart for instance) is always a difficult task.
The moment between taking my database dump, noting the position of the binlog on the master and starting the slave back up, it seems data has already moved on (new inserts, updates, ...) and my slave is not able to start up.
The database has a lot of constraints between tables and this often is the reason data is not able to sync: a log for a specific consumers doesn't get synced because the consumer doesn't exist on the slave.
I can, sometimes, work around this by executing:
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
SELECT SLEEP(2);
SHOW SLAVE STATUS;

But it feels very hack-y and a more a game of luck than anything else.
How should I go about this?
I'm not really willing to shut down the master database in this process. How can I copy the data and start the slave at the exact position as the copied data?


texts:
I need help optimizing time-based queries on a relatively small table.

The table looks like this:

time int | tag varchar(21) | val decimal

I also have a index like such

create index _index on table (time, tag)

And queries looks like this:

select * from table where time >= 1554599241 and time <= 1554599251 and tag = ANY('{aaa,bbb,ccc}')

Is there any way for me to reduce query times?

Example

select * from table where time >= 1554020803 and time <= 1554625603 and tag = any('{ryeb1UQ2Gm,HkGM18QnGX,rkxE1872fQ,rybS18XnGm,B1f8JUmnzX,BkuJ873M7,HyO6Z9OTMQ,BkmCbqOaG7,HJpCWcdpfm,rkGgAb9upGm,ry7JM9Oaz7,BkOkf5OTzm,B1ejy8Xhz7,Syp1G9uTzm,HkMlJMcupzQ,rycJLm3zQ,SyxFJLmnGm,Sy_RZ9u6Gm,BkfJy8QnMQ,Sy76-qupGm}')

produces this explain:

Bitmap Heap Scan on table (cost=20795.15..520342.18 rows=81781 width=69) (actual time=72.530..674.270 rows=224012 loops=1)
  Recheck Cond: (("time" >= 1554020803) AND ("time" <= 1554625603))
  Filter: ((tag)::text = ANY ('{ryeb1UQ2Gm,HkGM18QnGX,rkxE1872fQ,rybS18XnGm,B1f8JUmnzX,BkuJ873M7,HyO6Z9OTMQ,BkmCbqOaG7,HJpCWcdpfm,rkGgAb9upGm,ry7JM9Oaz7,BkOkf5OTzm,B1ejy8Xhz7,Syp1G9uTzm,HkMlJMcupzQ,rycJLm3zQ,SyxFJLmnGm,Sy_RZ9u6Gm,BkfJy8QnMQ,Sy76-qupGm}'::text[]))
  Rows Removed by Filter: 896063
  Heap Blocks: exact=7229
  ->  Bitmap Index Scan on _test_index  (cost=0.00..20774.71 rows=817814 width=0) (actual time=71.430..71.430 rows=1120075 loops=1)
      Index Cond: (("time" >= 1554020803) AND ("time" <= 1554625603))
Planning time: 0.137 ms
Execution time: 682.200 ms


texts: JOIN FOR SYSTEM TIME with column as the effective system time

texts:
Imagine I have a schema that includes Products, Orders, and OrderLineItems, with Products being a system-versioned temporal table.

Schema:

CREATE TABLE dbo.Products
(
    ProductID INT NOT NULL IDENTITY PRIMARY KEY,
    Name nvarchar(255) not null,
    SysStart DATETIME2 (7) GENERATED ALWAYS AS ROW START NOT NULL,
    SysEnd DATETIME2 (7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd])
)
WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.Products_History, DATA_CONSISTENCY_CHECK = ON));
GO

CREATE TABLE dbo.Orders
(
    OrderID int not null identity primary key,
    OrderDate datetime2 (7) not null
);

CREATE TABLE dbo.OrderLineItems
(
    OrderID int not null,
    ProductID int not null,
    CONSTRAINT FK_OrderLineItems_Orders FOREIGN KEY (OrderID) REFERENCES dbo.Orders (OrderID),
    CONSTRAINT FK_OrderLineItems_Products FOREIGN KEY (ProductID) REFERENCES dbo.Products (ProductID),
    CONSTRAINT PK_OrderLineItems PRIMARY KEY (OrderID, ProductID)
);
GO

-- Load Sample data
insert into Products (Name) values ('a'), ('b');
waitfor delay '00:00:02';
insert into orders (OrderDate) values (getutcdate());
waitfor delay '00:00:02';
update products set name= 'c' where name = 'a'
waitfor delay '00:00:02';
insert into orders (OrderDate) values (getutcdate());
insert into OrderLineItems (OrderID, ProductID) values (1, 1), (1, 2), (2, 1);

How would I write a query which joins Products to OrderLineItems using the date from Orders?

For example, to query "Orders which contains products whose names have changed":

-- Fake syntax:
SELECT o.OrderID, p_then.Name as [Old Name], p_now.Name as [New Name]
FROM dbo.Orders o
INNER JOIN dbo.OrderLineItems oi on o.OrderID = oi.OrderID
INNER JOIN dbo.Products as p_then
    for system time o.OrderDate
    on oi.ProductID = p_then.ProductID
INNER JOIN dbo.Products p_now
--  for system time now
    on oi.ProductID = p_now.ProductID
WHERE p_then.Name <> p_now.Name


texts: Enabling Service Broker on a database involved in an availability group

texts:
I'm trying to enable Service Broker on a SQL Server 2012 database that is part of an availability group and keep getting this error message:

  The operation cannot be performed on database "dbname" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
  ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 1468)

Even attempting to enable Trustworthy on the database generates the same error.  According to this article, it seems that Service Broker could be enabled on databases configured for AG.  What could I be missing?

The listener has been configured, endpoints created with public permission, targets reference the listener name.  The error occurs when I try to enable service broker or set Trustworthy via management console or T-SQL.

I wanted to add that the database is a restore from a SQL Server 2005 version with a existing Service Broker implementation.


texts: Repair Mysql table structure from existing frm file

texts:
i have moved my mysql database from another computer, but when i moved to the other some table seems doesn't exist, when i check to mysql data folder, the  MYI and MYD from  table is gone, just FRM left, how do i create the same table structure from existing FRM ? never mind if the data is gone. i just need the structure, so how i could do that ? by the way, i am using windows, i have try with this command but it seem doesn't work

mysql> repair TABLE m_tarif2012 USE_FRM;
+-----------------------+--------+----------+------------------+
| Table                 | Op     | Msg_type | Msg_text         |
+-----------------------+--------+----------+------------------+
| rsud_2012.m_tarif2012 | repair | Warning  | Can't open table |
| rsud_2012.m_tarif2012 | repair | status   | Operation failed |
+-----------------------+--------+----------+------------------+
2 rows in set (0.01 sec)


texts: TokuDB/Percona - Insert Performance worse as table grows

texts:
We have a bunch of existing servers which run just fine, inserting our thousand rows/second, but the new batch just will not do it. As the day goes on and the table grows, it gets slower and slower, until we are millions of rows behind.

I've tried to match all the settings, but still no juice.

tokudb_commit_sync=OFF;
tokudb_block_size=1048576;
tokudb_analyze_in_background = OFF;
tokudb_auto_analyze=0;

NOTE the current tables on the new servers were created with a 4MB block size, though I don't think that would cause this kind of performance degradation.

Old [working] servers (on worse but similar hardware)

    Linux 2.6.32-504.30.3.el6.x86_64 CentOS release 6.6 (Final)
    tokudb_version: 5.6.36-82.0
    mysql  Ver 14.14 Distrib 5.6.36-82.0, for Linux (x86_64) using  6.0

New [fubar'd] servers:

    Linux 3.10.0-862.3.2.el7.x86_64 CentOS Linux release 7.5.1804 (Core)
    tokudb_version: 5.7.21-21
    mysql  Ver 14.14 Distrib 5.7.21-21, for Linux (x86_64) using  6.2

I think I may be onto something with this, however, when I do a SHOW INDEX, the old servers have cardinality equal to the number of rows in the table for ALL indices, while the new servers have varying cardinality, sometimes small, dependent on the cardinality (as expected). (Both tables are identical, aside from the data)

So it seems the new servers are getting caught up in some sort of index optimization on each new write (LOAD DATA LOCAL btw), while the old servers are able to insert much faster because they don't do as much?

Please help me I've been all over google and in logs and code for 16 hours today and I have no more ideas.

The goal is to make the new servers have INSERT throughput like the Old servers.


texts:
I have a query inner joining multiple tables all of which are partitioned and have 10 partitions each:

SELECT A.COL1, B.COL2, C.COL3
FROM A
INNER JOIN B ON A.ID = B.ID
INNER JOIN C ON A.ID = C.ID
WHERE COL20 < 10000  ---- COL20 IS NOT THE PARTITION KEY COLUMN

In the actual query execution plan, for one of the tables, there is a non-clustered index scan with a key lookup.

When I look at the properties for Key lookup in the actual execution plan, it looks like it's getting partition pruned.

I'm confused as to why that would happen, is this like a negative impact on the system. I understand that key lookups themselves are bad, but why does it show that only 7 partitions have been accessed? The properties say:

Key Lookup (Clustered)
----------------------------------
Actual number of rows:      215805
Actual Partition Count:     7
Actual Partitions Accessed: 3..9

My concern is around how the lookup works. In case, when there is no partitioning, when the lookup fetches the data from the data page for a particular key, would it do a seek or a scan to reach to this page? Would a direct key lookup perform better than a key lookup  with partition pruning?


texts: automatic cast of anonymous record array type to the array type of a named composite type in PostgreSQL?

texts:
From PostgreSQL documentation, it is possible to cast an anonymous record type to a named composite type at a function call site. Quoting the example there:

CREATE TABLE mytable(f1 int, f2 float, f3 text);
CREATE FUNCTION getf1(mytable) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL;

-- No cast needed since only one getf1() exists
SELECT getf1(ROW(1,2.5,'this is a test'));

However, the implicit cast of record type does not seem to work for an array of anonymous records. If I slightly change the above example to work on an an array function taking getf1a(mytable[]) as below, I get an error:

CREATE FUNCTION getf1a(mytable[]) RETURNS int AS 'SELECT $1[0].f1' LANGUAGE SQL;

SELECT getf1a(ARRAY[ROW(1,2.5,'this is a test')]);

The error says:

=> ERROR:  function getf1a(record[]) does not exist
LINE 1: SELECT getf1a(ARRAY[ROW(1,2.5,'this is a test')]);

The anonymous and named array record types are compatible and there is no ambiguity. It would be nice if the cast is implicit. My question is:

Is there a way to make the implicit cast work for array record types too?

(This is with PostgreSQL 12.x)


texts:
I have the following SQL command which is built inside a stored procedure according to input parameters:

SELECT DISTINCT *
    FROM (
        SELECT DISTINCT TOP @displayLength *
        FROM (
            SELECT DISTINCT TOP @displayStartPlusLength
                ID,
                HostName,
                ProductName,
                ProductPublisher
            FROM dbo.SQLtable
            WHERE
          ProductID NOT IN (SELECT ISNULL(ProductID,''NULL'') FROM dbo.ProductList1)
            AND ProductID NOT IN (SELECT ISNULL(ProductID,''NULL'') FROM dbo.ProductList2)
            AND (
        HostName like ''%' + @searchString + '%'' OR
        ProductName like ''%' + @searchString + '%'' OR
        ProductPublisher like ''%' + @searchString + '%'' OR
            )
            ORDER BY ID ASC
        ) x ORDER BY ID DESC
    ) y ORDER BY ID ASC

It returns a page of products from a table which contains tens of thousands of rows.
It works fine and is well compatible but has one disadvantage: it takes 15 seconds. All columns have indexes and there is a primary key on the ID column.

Is it possible to rewrite it to make it faster?

Modified original SQL

SELECT *
    FROM (
        SELECT TOP @displayLength  *
        FROM (
            SELECT TOP @displayStartPlusLength
                ID,
                HostName,
                ProductName,
                ProductPublisher,
            FROM dbo.SQLTable
            WHERE
                ProductID NOT IN (
                    SELECT ISNULL(ProductID,'NULL') FROM dbo.ProductList1
                    UNION
                    SELECT ISNULL(ProductID,'NULL') FROM dbo.ProductList2
                )

            ORDER BY ID ASC
        ) x ORDER BY ID DESC
    ) y ORDER BY ID ASC

New version with CTE

;WITH Results_CTE AS
    (
        SELECT
                    ID,
                    HostName,
                    ProductName,
                    ProductPublisher,
                    ROW_NUMBER() OVER (ORDER BY ID) AS RowNumber
                FROM dbo.SQLTable
                WHERE
                    ProductID NOT IN (
                        SELECT ISNULL(ProductID,'NULL') FROM dbo.ProductList1
                        UNION
                        SELECT ISNULL(ProductID,'NULL') FROM dbo.ProductList2
                    )
    )
    SELECT *
    FROM Results_CTE
    WHERE RowNumber >= @displayStart - 1 --// Offset
    AND RowNumber < @displayLength - 1 --// Limit

The performance of the CTE version is 49% against 51% (Modified version), according to the Actual execution plan, so there is not much improvement.

Solution (Removing ISNULL Function)

SELECT *
    FROM (
        SELECT TOP @displayLength  *
        FROM (
            SELECT TOP @displayStartPlusLength
                ID,
                HostName,
                ProductName,
                ProductPublisher,
            FROM dbo.SQLTable t
            WHERE
                NOT EXISTS (
                    SELECT ProductID FROM dbo.ProductList1 WHERE pl1.ProductID = t.ProductID
                    UNION ALL
                    SELECT ProductID FROM dbo.ProductList2 WHERE pl2.ProductID = t.ProductID
                ) AND ProductID IS NOT NULL

            ORDER BY ID ASC
        ) x ORDER BY ID DESC
    ) y ORDER BY ID ASC


texts: Better understand table indexing best practices

texts:
I'm not completely sure when would I start indexing my tables, when there are some criterias that I should consider:

The tables are not that big, I think (1000 - 10,000 rows).
The searches are made from the CMS admin pages - So they include more types of filtering/sorting options/combinations than the frontend.
I'm wondering if there is such thing as over-indexing where I'll add too much indexes that the searches might be quicker, but it will impact dramatically on the performances of inserting/updating data to the table, or that the DB engine will choose the wrong index for a query.
I'm adding Fulltext index when it come to textual searches.
I'm also adding asc/desc indexes for DATETIME/INT/BIGINT for what I believe will give better performances when it comes to sorting/filtering based on columns.

I'm just not sure what will be the optimal point in which adding an index will improve the performances of my App, when I take into consideration the above parameters, and my tendency to be an "Index Nazi" (Pardon my French) - I'm finding myself adding more and more indexes for every possible scenario I can think of.

I do hope that this question is not too broad, and I didn't include an example because it's more general behaviour of good and bad practices when it come to indexing.


texts:
I am having trouble designing a schema in which there are two models an employee and projects. I am storing employees details in a table structure as:

id | employee | report_to

wherein employee table store the employee id and report_to store the id of the employee to whom the employee reports.

I have another table in which we store diff projects created by employees.

Id | project_name | employee_id

Now if I need to get projects done by employees who report to me I can easily get the list and also get the projects.

But the concern is eventually over time the employee reporting manager changes and hence along with that all the project that falls under let's say project manager 'a' also changes.

How to handle this kind of a problem in database. can this be designed in such a manner so that the projects which were initiated during that time remains under that particular manager only?


texts: DB2 - how to safely blow away database on linux?

texts:
I'm using (I believe) 10.1.2, on CentOS 6.5 (for sure).

The underlying problem I have is that the database upgrade package prepared in house isn't working for me, in at least the sense that the database schema is not changing as it is supposed to. Of course, no-one external can help me with that.

What I'd like to do is remove the existing database as completely as possible (preferably to the level of deleting/archiving files that might have records of it), so that there's no way the upgrade package can be reading old schema data. What's the safest way to do that?

I've tried doing a drop database in the db2 commandline as db2inst1, with no luck:

db2 => connect to xydb

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.1.2
 SQL authorization ID   = DB2INST1
 Local database alias   = XYDB

db2 => drop database
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following
"DATABASE".  Expected tokens may include:  "<identifier>".  SQLSTATE=42601
db2 => drop database xydb
SQL1035N  The operation failed because the specified database cannot be
connected to in the mode requested.  SQLSTATE=57019
db2 =>

Any help much appreciated.


texts: Find partial match with textsearch query

texts:
I have the following query that I did hard looking on this site:
SELECT *
FROM   renns
WHERE
    (
        to_tsvector('english', coalesce(numar_admin, '')) ||
        to_tsvector('english', coalesce(nume_strada, '')) ||
        to_tsvector('english', coalesce(nume_prenume, ''))||
        to_tsvector('english', coalesce(bloc, '')) ||
        to_tsvector('english', coalesce(apartament, ''))
    ) @@ plainto_tsquery('english', '$q')

It works if I write in the variable $q all the text that appears in one of the columns present in the query. For example: cherry
I would like to write only: cher and return all rows fields in which it finds this string: "cher".


texts: "Cannot open user default database. Login failed" after altering MDF and LDF locations and bringing DB offline

texts:
I was attempting to move my database to a separate hard drive on the server, so I ran the following commands in the following order (I was following this question : How to change location of SQL Server 2012 database which is already configured with a merge replication):

use master;
ALTER DATABASE db_name
MODIFY FILE (NAME = db_name,
     FILENAME = 'E:\Data\db_name.mdf');

ALTER DATABASE db_name
MODIFY FILE (NAME = db_name_log,
     FILENAME = 'E:\Data\db_name_log.ldf');

Here is where my mistake happened: I either did one or both of the following mistakes:

I may have input the incorrect Name parameter above (Would the mdf/ldf files be dropped in this case?)

...and/or...

I also did not do step 2 in the answer referenced above (Stop log reader agent, distribution and merge agent (if they are running).

Then I brought the database offline:

alter database db_name set offline

Now when I try to connect to the database server, I get the error I described in this question.
How can I remedy this? Do I need the sa login? Or is there something else I can do?

Thank you.

Other users are unable to connect to the database also, getting the same error. Did I actually corrupt the master db somehow?

I've been researching the causes of this error and they all seem to point to the need to restore access using sa login or something to that effect.


texts:
There are many similar questions, but none of them helped me.

I had been using MySQL on Windows with root/root credentials for a while, but since lately, I'm not able to login

> mysql
ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO)

> mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

> mysql -uroot -p
Enter password: ****
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

> mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I've tried to reset root password: (in admin console)

> cat C:\mysql-init
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

> mysqld --init-file=C:\mysql-init --console

but there is an error line:

2015-12-17T10:34:14.616701Z 1 [ERROR] 1131  You are using MySQL as an anonymous user and anonymous users are not allowed to change passwords


texts: Count distinct instances of a SKU and count all items in all instances containing that SKU

texts:
This is on MSSQL/Azure SQL.
I have the following Sales Table:

Line_Item_ID
Sales_Order_ID
SKU

1
1
4

2
1
1

3
2
4

4
3
1

5
3
7

6
3
4

7
4
4

8
5
1

9
5
8

I am trying to get an output that shows for each SKU, the total number of Orders (very easy) AND the total number of line items for an Order that contains that SKU.
So for the first part:
select SKU, count(distinct(sales_order_id) as "Order_Count"  from Table T
group by SKU

Easy enough, however the second part is doing my head in -
The desired output would look like this:

SKU
Order_Count
Line_Item_Count

1
3
7

4
4
7

7
1
3

8
1
2

How do I get a count of the total Line_item_IDs for each order that contains a SKU? Ideally in a single query without a load of crazy joins or subqueries.


texts: Temporarily Turn Off Table Type Reference Checking On A Development Database

texts:
During my database design process I have begun to add Table Types to the database and have run into the need to alter a table type. It is painfully clear that one cannot remove or drop the table type if it is being used in stored procedure(s); and unless one drops the related sprocs first, the table type can't be dropped/altered.
I get why the table type is important in an active database and why it should not be dropped if live, but if one is in a development mode is there/why not be able to turn off the database, so to drop the table type, and the add the modified type and restart? ...
 So is there a way to stop the checking and allow the drop of the table type, hence avoid the warnings to be able to alter the type?


texts: mysql "can't create test file" error on Centos

texts:
When starting mysql on Centos servers with a custom data directory you might get an error similar to the following:

2014-10-15 10:23:56 17081 [Warning] Can't create test file /database/mysql/localhost.lower-test


texts: Manipulate SQL statement before execution on server

texts:
Our databases are on SQL Server 2000. We are migrating to SQL Server 2008 R2.
We want to update the compatibility level to 100.

But the join operator \*= and =\* are no more supported with that compatibility level.
The advised solution is to update our source code and change the operators to OUTER JOINS. But we cannot afford the modification of thousand .exe and .dll. So we cannot update our source code; there are too many projects.

What I want to accomplish, in SQL Server 2008 R2, is the following:

Catch the SQL statement before its execution ?
Update the statement (i.e., replace *= by LEFT OUTER JOIN) ?
Give back the modified statement to the SQL engine for execution ?

This artifice is really not advised, but it will allow us to set the compatibility level to 100 event to our program still use the old syntax.

Thanks

I posted the same question on stackoverflow before I found this specific site.
https://stackoverflow.com/questions/27320554/manipulate-sql-statement-before-execution-on-server


texts: How to easily convert utf8 tables to utf8mb4 in MySQL 5.5

texts:
I have a database which now needs to support 4 byte characters (Chinese). Luckily I already have MySQL 5.5 in production.

So I would just like to make all collations which are utf8_bin to utf8mb4_bin.

I believe there is no performance loss/gain with this change other than a bit of storage overhead.


texts: can I quickly create tables for each partition?

texts:
I have a table with two partitions and I would like to create two tables, one from each partition.  Is there a quick way to do this (a la partition exchanges) instead of a create table as select (CTAS) statement?

For what it's worth, this need arose because I want to exchange these two partitions into another table; from what I've read, in order to do a partition exchange the data needs to be in a non-partitioned table.


texts: Query between tables to delete similar records from another table

texts:
I am writing an SQL query on two tables products01 and products02 in a PostgreSQL database.

For the entire set of records in products02 take the substring (first 10 characters of volumeId and use those substrings to find all the corresponding records in products01. There can be more than one hit in products01, for instance finding older legacy record of the same item, just a different edition. The eleventh character of volumeId makes it unique.

For instance, in products01 I can have volumeId's like:

record45671
record45672
record45673

And in products02 I would be using substring(volumeId for 10) or 'record456' to find those similar records in products01.

Also I'd like to display the results so I can verify my query is accurate.

Here is my initial attempt:

    SELECT substring(volumeId, for 10) FROM temporary.products01 t01
    WHERE EXISTS (
        SELECT volumeId FROM temporary.products02 t02
        WHERE t01.volumeId LIKE t02.volumeId);


texts: This is highly a research based question about converting the unstructured textual data to structured data. Are there any systems or tools perform automated process of converting the unstructured data  without human interventions to structured data sets? The unstructured data flows from different resources can be directed to a tool/software to be extracted automatically.


texts: Least Privilege for SQL Agent to run DISKPART

texts:
The DISKPART script

SELECT DISK 1
ONLINE DISK

runs fine as long as SQL Agent has admin rights on the server; otherwise the script fails with this message:

Message
Microsoft DiskPart version 6.3.9600

Copyright (C) 1999-2013 Microsoft Corporation.
On computer: FOO

DiskPart has encountered an error: Access is denied.
See the System Event Log for more information.

Initially working through this issue there were errors in the event viewer.  However, now that I have used dcomcnfg.exe to give Full configuration, Local Access, Local Launch and Local Activation rights for the SQL Agent account to Virtual Disk Service, there are no messages showing in the system log -- just the above message in the SQL Agent job history.  I'm looking for the master tome of Windows configuration but there does not seem to be one. Do I need to set up auditing or traces to see what privileges are being requested?


texts:
I am periodically running into a THREADPOOL wait issue with a SharePoint data store of hundreds of databases using a SQL Server 2008R2 Standard Edition instance (recently migrated to an Azure VM). It is running a stored procedure named proc_DefragmentIndices in many (possibly all) of these databases at a time.
The stored procedure rebuilds every index in the database unconditionally. Of course, they are head blockers (because it is Standard Edition, each ALTER INDEX command runs with ONLINE=OFF). Because there are so many of them running at a time (each in a different database), and they are going parallel (which ties up even more workers), everything piles up. Just for extra noise, Azure Backup is taking backups of many of the databases while all this is going on, eating up even more workers. Activity Monitor shows 106 waiting tasks, and multiple instances of the same session Id for many of the ALTER INDEX commands (which is why I say they are going parallel).
What I find puzzling is that these ALTER INDEX statements are going parallel even though MAXDOP is set to 1 in the instance, as is recommended for SharePoint databases, and the ALTER INDEX statements executed by the stored procedure are not using a MAXDOP option to override it.
Q1: How can INDEX rebuilds be going parallel when MAXDOP is set to 1?
Q2: Activity Monitor shows the ALTER INDEX commands but sp_WhoIsActive does not. Does anyone know why?


texts: How to set all auto increments to 1 in a Schema

texts: Is it possible to reset all auto increment in a schema in one query? if not even just by per table will do. btw I am using MySQL server 8 thanks in advance :)


texts: How to deploy SSIS package with XML config, using SQL agent

texts:
I wonder if anyone can help me with a question.

Context
I've created a database which needs a front end in MS Excel. When the user presses upload button I want it to start an SSIS package which moves the data into some tmp tables then runs a couple of stored procs to move the data into the correct tables. I'm struggling to deploy my package successfully.

Hurdles I've already jumped over

The SSIS package is functional in debug mode.
I've got a SQL Agent job which can run the package on the integration services server file system.
I've managed to create a way of the users starting the job from excel and inheriting SQLAgentOperatorRole through the very convoluted process of creating certificates and a new stored proc, I called it spTriggerJob [with parameter for job name], which then runs EXEC msdb.dbo.sp_start_job @job_name = @strJob with the correct privileges.
I've created and implemented a proxy with permissions to access the network file server.
I've created XML configurations to pass the required variables (e.g. the filepath of the file to be uploaded) and a way of updating them on-the-fly.

Problem 1
When I run the package manually from SSMS I can load the XML configurations successfully. However I haven't been able to do this from the SQL Agent job. If I go into job properties > steps > edit > configurations tab > add the only options I get are C:\ D:\ and a couple of obscure/unhelpful network drives. Ideally I want to point it at a full UNC filepath. Is it necessary to map the drive on the server?

I suspect there is a more optimal way of deploying my SSIS package which circumvents this. Can anyone suggest a better way? I'm fairly new to all this so don't make any assumptions as to my existing knowledge!

Problem 2
When I use my spTriggerJob [name of job], I see a weird message in the job history. It says "The job succeeded. The Job was invoked by user " then it gives the Windows account login of one of the network administrators. However I've been able to verify that the permissions of the proxy (which have nothing to do with Mr Network Admin) are applied correctly by using it to create an entry in a test table and recording the user name.
However if I call msdb.dbo.sp_start_job directly, then the login which appears in the job history message is my own.
This seems wrong! Does anyone know how to resolve this?

Edit: I figured out problem 2. It is because 'spTriggerJob' has to have 'WITH EXECUTE AS OWNER'. Whilst I thought I was owner, in fact it is Mr Network Admin. The confusing bit was that the job was running with the correct privileges inherited from the proxy, which threw me off a bit.

Required solution
I need to reach a point where the package is run on an ad hoc basis at the click of a button, with different configuration settings each time it is run. Surely it can't be that hard!!!

Thanks in advance for your help.

Cheers,
Andy


texts: Linked Server with Windows Authentication Permissions

texts:
I’m trying to figure out how to setup a linked server object from SQLServerA to SQLServerB using Windows Authentication.

I’ve setup many linked servers but have always used a SQL Server login which works fine.

In this scenario, a logon has been created at SQLServerB using Windows Authentication granting access to a specific database.

From SQLServerA, I’m creating a linked server object to SQLServerB. On the Security Page of the linked server configuration, I’m using "Be made using this security context" and enter Remote login:domain\user, With Password: password respectively.

When SQL performs the Test Connection final step, I receive an error indicating “Login failed for user ‘domain\user’ (Microsoft SQL Server, Error: 18456)”.

Is there something special I need to do to create the linked remote server using Windows Authentication as the authorization type instead of a SQL Server login?

If that’s not possible, is there another solution that I can use to query a remote servers other than interactively through SSMS?

My goal is to setup SQL job(s) to import some data from the remote SQL Server.

Thanks


texts: Execution Plan is NOT using INDEX, It uses Table Scan

texts:
I know when it comes to using an index or a table scan, SQL Server uses statistics to see which one is better.
I have a table with 20 million rows. I have an index on (SnapshotKey, Measure) and this query:
select Measure, SnapshotKey, MeasureBand
from t1
where Measure = 'FinanceFICOScore'
group by Measure, SnapshotKey, MeasureBand

The query returns 500k rows. So the query selects only 2.5% of the table's rows.
The question is why SQL Server does not use the nonclustered index I have, and uses a table scan instead?
Statistics are updated.
Good to mention that the query performance is good though.
Table Scan

Forced Index

Table/Index Structure
CREATE TABLE [t1](
    [SnapshotKey] [int] NOT NULL,
    [SnapshotDt] [date] NOT NULL,
    [Measure] [nvarchar](30) NOT NULL,
    [MeasureBand] [nvarchar](30) NOT NULL,
    -- and many more fields
) ON [PRIMARY]

No PK on table, as it is a data warehouse.
CREATE NONCLUSTERED INDEX [nci_SnapshotKeyMeasure] ON [t1]
(
    [SnapshotKey] ASC,
    [Measure] ASC
)


texts: Correlated sub-query on the same table with only one alias

texts:
I have query which look like this

SELECT *
FROM my_table
WHERE ... AND id IN (SELECT t2.id
                     FROM my_table t2
                     WHERE id = t2.id AND ... );

I know in this example I could combine the two WHERE clauses and don't use a sub-query but this is not the point.

This query works perfectly on the DBMS I'm using (SQL Anywhere 16) but I'm wondering if the fact that id in the sub-query refers to my_table.id from the main query is a standard behavior or if I'm just lucky.

Link or reference to an RFC or any official document appreciated :)


texts: What would be best data model for finding difference within n, n-1 and n-2 elements?

texts:
This is from betting domain which has something that is called a long list: a list of a "home team win/draw/away team win"  markets for 13 games.
A punter can select any combination of the possible outcomes which are encoded in a following way:

1 - home team wins
2 - draw
4 - away team wins
3 - home team wins or draw
5 - home team wins or away team wins
6 - draw or away team wins
7 - home team wins or draw or away team wins

Meaning a [3, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7] represents a selection where punter put following bets:

"home team wins or draw" in a first game
"home team wins"  in games 2-12
"home team wins or draw or away team wins"  in 13th game

After games are finished there will be another 13 elements list representing winning outcomes, for example: [4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1] means that in a first game away team won and in all other games home team won.

Let's take this bet [3, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 7] and this query as an example [4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1].

Next we have a following correspondence between selected outcomes and actual results

1 - 1, 3, 5, 7
2 - 2, 3, 6, 7
4 - 4, 5, 6, 7

Meaning for an actual result of 13 games [4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]  following bets considered to be a winning bets:

[4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]
[4, 3, 1, 5, 1, 7, 1, 1, 1, 1, 1, 1, 1]
[5, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]
[6, 1, 7, 1, 1, 5, 1, 1, 3, 3, 3, 3, 1]
[7, 1, 5, 5, 5, 1, 1, 1, 1, 7, 7, 7, 1]

The question is what is the best way to model this in database for following use cases:

find bets where all 13 game results were guessed correctly
find bets where 12 game results were guessed correctly
find bets where 11 game results were guessed correctly

Examples of queries for following use-cases will be highly appreciated ;))


texts: Database VIEW with a dynamically changing filter

texts:
I am trying to create a view based on a specific case. Following is my table:

Table Name: Calls

CustomerName | CallID | Timestamp | Quality | PacketLoss | Jitter

The goal is to create a view that can do the following:

SELECT * FROM [VIEW NAME] WHERE TIMESTAMP BETWEEN {Start Time} AND {End Time}
At this point the database need to go through the table and find top 5 CustomerNames with the most amount of rows where the Quality was marked as "Bad".
The database will use the return as a filter and send me back all the rows that had one of the CustomerName's listed in top 5.

I tried this:

CREATE VIEW "Calls_View"
AS (
SELECT *
FROM "Calls"
WHERE "CustomerName" IN (SELECT "CustomerName"
                         FROM "Calls"
                         WHERE "Quality"='Bad'
                         GROUP BY "CustomerName"
                         ORDER BY COUNT(*) DESC LIMIT 5)
  AND "Quality"='Bad');

Is my logic correct?


texts: How to return all values from a stored procedure casted as varchar(max) in sql server due to PHP mssql_query() ntext issue?

texts:
This is more of a concept/how-to question rather than about actual code.  I can do the programming but I just need to know what direction to head...  I was going to post this on StackOverflow but it's not about actual code... so.... and it's database stuff... I hope this is the right spot and I hope I can get an answer. (if not, tell me where I should ask; plz, thnx).

I am working with PHP 5.2.4-2 and Sql Server 2005.  To query, I must use a PHP function called mssql_query() and it apparently has an issue with some of the datatypes being returned. The solution to this error is to cast those things to compatible datatypes (varchar(max) or text).  PHP treats everything as a string so not retaining something as a true integer or datetime or something is not an issue for me.

PHP error:

  Warning: mssql_query() [function.mssql-query]: message: Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier. (severity 16)

I am working with many stored procedures that I did not write myself and it would be a hassle to go through all of them to check and cast everything necessary.  Since I can't even query in the PHP without getting an error (so I can't even get column names) and I am not working with an actual query (select col_name_1, col_name_2 from tbl) this must all be done in the database.

I was thinking a stored procedure to dynamically put together a query that casts each column from the original sp.  This isn't syntactically correct.  Pseudo-sql.

so... if exec sp_test_1 returns fields id, field1, field2, field3.....

a wrapper SP... will have to... build the query and I will have to painfully get all the column names and everything:

select cast(id as varchar(max)) as id,
    cast(field1 as varchar(max)  as field1,
    cast(field2 as varchar(max)  as field2..
from (exec sp_test_1)

or something like that.  I haven't really thought out the details because I'm not sure if this is the best route.  I built something like this a year or so ago... I bet I can bring it back into my active memory in my head if I try hard enough.

Is this the route to go?  Or will that ultimately kill performance and make every query I do through the PHP take absolutely forever?

I ran this by my friend who is strictly a front-end guy and he simply said, "uhm... that can't be the best way to do that..." but he rarely works in the database.

I can build this if needed.  I am scared of performance issues.  I can go through all 30+ extremely messy stored procedures that I didn't write and check for the bad datatypes and then include the cast in the SP, but these will be updated eventually by people who won't understand the importance of my changes (even if told directly and repeatedly) and that would just be an on-going battle.

If this is right idea (wrapper sp that dynamically builds a query to cast all returned columns as good data types)...?

I might start working on some quick n dirty sql do to this while I'm waiting for answers just to test out how I would put the wrapper together.  If I come up with anything truly functional I'll post it as I go...

Edit 1:  actually, I'm having a hard time even figuring out what the column names for the resulting table of an SP.  (does that sentence make sense?  I can't just join sysobjects and syscolumns for an SP, i guess)

Edit 2:  Nevermind.  As soon as I typed that the next google search came up with answers...


texts: How many SELECT/UPDATE/INSERT Operations can you run on a 4 core / 8 GB RAM server?

texts:
Assume you are working on a web project with html/css/js frontend with php in backend. How many SQL Operations (executed by mysqli for example) can do do with 4 core / 8 GB RAM server ? The database is running on a dedicated server.

I am only talking about some simple SELECT/UPDATE/INSERT Operations with maybe 2-3 subselects.

I guess the higher complexity level of the query the higher the (cpu/ram/io ?) ?

Is there some kind of "table" like 2c/4GB -> 10000 OPS/second , 4c/8GB -> 50000 OPS/second ?

I alreay took some tries in google but i only got links to specific database hardware requirements from ibm or sap hana.

Is this just a stupid question or can't you make a "general" recommendation. Of course i know that you cant make an exact recommendation but maybe a roughly one ?

Thanks in advance.


texts: identifying relevant incompatibilities when upgrading from version 5.6 to 5.7 for mysql on AWS RDS Instance

texts: I currently have an AWS RDS instance based on mysql version 5.6 that I need to upgrade to version 5.7. I understand that a comprehensive list of potential incompatibilities is given at https://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html#upgrade-system-table-changes, but if I wanted to know just the ones which require addressing before updating, is there any way to get this filtered list (containing just the incompatibilities that need attention)? Will this list be shown in the changes that are reviewed in step 7 of the procedure given at https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.Upgrading.html#USER_UpgradeDBInstance.Upgrading.Manual?


texts: How to Identify Multiple Insert Rows from Single Transaction

texts:
Is there method to identify multiple rows from single insert transaction?

IdentityId will uniquely identify each row. If 2000 rows were inserted from one insert transaction, how would I identify this? Is there like a BatchId() Identifier or TransactionId() reserved word? Using ETL default getdate() will not work, sometimes a transaction could span over milliseconds.

EG:

begin transaction
insert into dbo.Test2
select * from Test where ColumnA = 'etc...;
commit transaction

Refraining from using pattern below, may be inefficient, is there a  reserved keyword for batch transaction identifier?

select @MaxTransactionId = (select Max(transactionId) from dbo.Test2),
TransactionId = @MaxTransactionId + 1,


texts: SQL Always on availability with failover and mixed authentication

texts:
I have 2 boxes running SQL Server 2016, and set up using Always On Availability. I have a listener set up to choose which server to talk to in case of a failover, which seems to work fine, except for the fact that I'm using mixed authentication, and my connection string is using a SQL Server authentication user rather than a windows one.

For example, the connection string in my application is:

Database=MySQLServer;Server=AGListener;User Id=sqluser;Password=XXXXXXX;

when I'm working against the primary, this works fine. when I failover to the secondary, then I get an error that 'sqluser doesn't have any permissions to the database'. If i run the below script on the secondary server:

EXEC sp_change_users_login 'Update_One', 'sqluser', 'sqluser'

then it works fine again, but when I failover back to the original primary, I get the error again. it looks like on every failover, I have to run the update user script for the connection string to work.

Is using SQL Server authentication for a connection string in an AG environment just not a good idea? Or is there a step I'm missing? I would like my failover to be automatic, rather than needing to remember to run a script every time.


texts: pgAgent installation with Stack Builder in PostgreSQL

texts:
I have installed PostgreSQL 9.1 on ubuntu 12.04 64-bit. I try to run Aplication Stack Builder and install pgAgent.

At first I am asked for root password, which I provide, after I choose PostgreSQL 9.1 on port 5433 as the only option. On next screen I tick pgAgent and click Next a few times to get to the Setup screen with verification the postgradeSQL installation details. The Host is local, the User Name is postgres and I type my password for postgrade which I use when open pgAdmin III. When I click next I have a warning:

psql.bin: could not connect to server: Connection refused

Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connection on port 5432?

When I try pgAdmin it does work with password I provide but it looks for me like this password is not accepted by Stack Builder. I also provide my root password on the beginning so it should be able to do everything with sudo privilege. I don't know what to do now and what is the problem. Should I run something else before Stack Builder?

I know it seems like password problem but I tried all and it is not a simple password case. Did anyone have similar problem or know what to do?


texts:
I have a database which get restored everyday through a agent job, but after restoration the user gets killed so i want to create a step so that after restoration user should be created with readonly permission.

CREATE USER [username] FOR LOGIN [loginname]
 ALTER ROLE [db_datareader] ADD MEMBER []

Is this ok ?


texts: How to order the where clause and join columns in a covering index?

texts:
Simple query example:

Select t1.A, t1.B, t1.C, t1.CustomerID
from table1 t1
join table2 t2 on t1.CustomerID = t2.Id
where t1.A = 'x'
and t1.B = 'xx'
and t1.C = 'xxx'

I created a non clustered with A, B, C, CustomerID for table1

I turned on statistic io. I recreated the same index and moved the order of the columns around and ran the queries at least twice for each order and checked the logical read count. After several trials, I noticed that one order:
A, B, CustomerID, C produced the least number of logical reads, instead of something like A, B, C, CustomerID. I know this is a simple query and I haven't indicated the schema.

In general, it seems the order matters but I thought if I placed the where clause columns together, it doesn't matter in what order as SQL Server will figure it out. In the case above, the join column came between the where clause columsn and had an effect. I am not sure why? Does column selectivity matter in deciding where to place the column in the order? What are other considerations? Right now I am just doing it by trial and error.


texts: INSERT INTO using the RETURNING values from another INSERT

texts:
I'd like to do something like this:

INSERT INTO table2(name)
  INSERT INTO table1(name, address) VALUES ('me', 'home')
  RETURNING name;

And then I would expect a new record in table2 with 'me' in name. This would work fine with a SELECT returning the values, but I'm having trouble with the nested insert.


texts: How to reference categories table in products table?

texts:
I am using modified preorder traversal algorithm to store product categories in database (like image shown above). But i don't understand how to reference it in my products table. Here is my products table

product
id |  name        | supplier         | price | description
--------------------------------------------------------------------------------------
1  |  T shirts    | Rana Enterprises | 200   | Run faster than light!
2  |  Shoes       | RS Shirts        | 100   | If you're going to San Francisco...

This is my categories table

Tell me how to link my categories table to products table ?


texts:
I found the following query from this question which appears to show row deletions from a specific table:
DECLARE @TableName sysname
SET @TableName = 'dbo.ObjectInstances'

SELECT
    u.[name] AS UserName
    , l.[Begin Time] AS TransactionStartTime
FROM
    fn_dblog(NULL, NULL) l
INNER JOIN
    (
    SELECT
        [Transaction ID]
    FROM
        fn_dblog(NULL, NULL)
    WHERE
        AllocUnitName LIKE @TableName + '%'
    AND
        Operation = 'LOP_DELETE_ROWS'
    ) deletes
ON  deletes.[Transaction ID] = l.[Transaction ID]
INNER JOIN
    sysusers u
ON  u.[sid] = l.[Transaction SID]

The results all show the same username, which is the username we use from our app to connect to the database. However, I had just deleted a row using SSMS, while logged in using Windows Authentication.  This record is not shown in the results from the query above.
How can I view an audit of rows/records deleted using SSMS (right-click, delete)?


texts: how see parameter value on xml_deadlock_report

texts:
i have some xml_deadlock_report i got from this step

management > sessions > system_health > package().even_file

but when i see xml_deadlock_report i got sql query but i can't see what parameter use in where condition

<deadlock>
 <victim-list>
  <victimProcess id="process30e9e810c8" />
 </victim-list>
 <process-list>
  <process id="process30e9e810c8" taskpriority="0" logused="5608" waitresource="PAGE: 31:1:8080697 " waittime="982" ownerId="3428828901" transactionname="implicit_transaction" lasttranstarted="2020-06-02T16:57:47.850" XDES="0x2bea7c6d28" lockMode="U" schedulerid="1" kpid="23404" status="suspended" spid="150" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-06-02T16:58:06.750" lastbatchcompleted="2020-06-02T16:58:06.747" lastattention="1900-01-01T00:00:00.747" hostname="172.16.12.40" hostpid="0" loginname="user_xxxx_admin" isolationlevel="read committed (2)" xactid="3428828901" currentdb="31" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x02000000adf830280fe6a3ada9d7a7b50732f1aa6cf6359e0000000000000000000000000000000000000000">
DELETE FROM TTADATTSTATUSDETAIL
            WHERE Attend_ID = @P1    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@P1 nvarchar(4000))DELETE FROM TTADATTSTATUSDETAIL
            WHERE Attend_ID = @P1    </inputbuf>
  </process>
  <process id="process30e9db1c38" taskpriority="0" logused="7004" waitresource="PAGE: 31:1:8635778 " waittime="1040" ownerId="3428815086" transactionname="implicit_transaction" lasttranstarted="2020-06-02T16:57:35.037" XDES="0x29609d83a8" lockMode="U" schedulerid="5" kpid="12076" status="suspended" spid="130" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-06-02T16:58:06.030" lastbatchcompleted="2020-06-02T16:58:06.023" lastattention="1900-01-01T00:00:00.023" hostname="172.16.12.40" hostpid="0" loginname="user_xxxx_admin" isolationlevel="read committed (2)" xactid="3428815086" currentdb="31" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x02000000adf830280fe6a3ada9d7a7b50732f1aa6cf6359e0000000000000000000000000000000000000000">
DELETE FROM TTADATTSTATUSDETAIL
            WHERE Attend_ID = @P1    </frame>
   </executionStack>
   <inputbuf>
(@P1 nvarchar(4000))DELETE FROM TTADATTSTATUSDETAIL
            WHERE Attend_ID = @P1    </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <pagelock fileid="1" pageid="8080697" dbid="31" subresource="FULL" objectname="dbSF_BizNet_KPKRI.dbo.TTADATTSTATUSDETAIL" id="lock29105cff80" mode="U" associatedObjectId="72057594477346816">
   <owner-list>
    <owner id="process30e9db1c38" mode="U" />
   </owner-list>
   <waiter-list>
    <waiter id="process30e9e810c8" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="1" pageid="8635778" dbid="31" subresource="FULL" objectname="dbSF_BizNet_KPKRI.dbo.TTADATTSTATUSDETAIL" id="lock29808f0600" mode="IX" associatedObjectId="72057594477346816">
   <owner-list>
    <owner id="process30e9e810c8" mode="IX" />
   </owner-list>
   <waiter-list>
    <waiter id="process30e9db1c38" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
 </resource-list>
</deadlock>

how i see what value at parameter @P1 ??


texts: Select statement on a table with constant inserts

texts:
This is a dumb question. I was wondering how sql server works in this situation. Lets say I am selecting from a table ( select * from tab) and the table is having hundreds of rows getting inserted per second, how does sql server know when to stop reading the rows?

Edit: Just to add, I did a select on a 200Gb table and monitored the locks on another session with sp_lock and saw that sql server was grabbing page shared lock on one page at a time and reading data. So wondering how does it know when to stop? Does sql server take into consideration when the select started and select only the pages that are committed when the transaction starts?


texts: Store both geometry and geography or store one and calculate the other?

texts:
I am just starting out designing my first GIS database, using PostGIS / PostgreSQL, and I have a question regarding geometry and geography, specifically whether it is more efficient to store geometry (because it supports more functions) and then project into geography when I need it, or whether I should store both geometry and the re-projected geography alongside?

To elaborate a little more, the database will be populated with pre-existing data that contains both British National Grid (EPSG:27700) (OSGR) and converted WGS84 (EPSG:4326) values, and there will be a regular update of that data from external sources. This would argue for two columns, because I don't need to do the calculation.

Going forward, I want to allow data to be edited through a "point and click" web-based map application, using OSM, or text input as either OSGR or via GPS coordinates. Search facilities would also allow either OSGR or lat/lon. Having two columns would mean that I would always have to perform at least one calculation for input (though not search) while having a single column would mean that I may sometimes require a calculation for entry, and again maybe one calculation for search.

I think I've argued myself into wanting both geometry and geography columns in my table (and probably projection columns for each, too, so that I can deal with Ireland at some future time), but is that sensible?

Thanks.


texts:
Assuming the choice is to stay with relational model, how are tables structured in the case of large User profiles? All the examples I see consider only simple cases, such as a "profile" containing name, email, and a few other columns.

Say the app is a Life Dashboard, where each user profile has all kinds of disparate data, from favorite movies, to money spending statistics, to pictures. And since its a dash, we want to return all of it to the single page app upon user login.

Is this a use case for denormalization? without denormalization, it would require multiple queries just to return the first request: an authorization query, followed by one for each of the potentially 10-15 different normalized tables on userID. is this common? or in this case is denormalization or even switching to NoSql the preferred method? or case by case depending on how whether we want each entity in the normalized table represented and queryable?


texts: Oracle 11g database datafile addition to ASM without '+' sign

texts: what will happen, if we miss the '+' sign while adding datafile to ASM in oracle 11g database? Whether datafile added will be ASM based or Non-ASM based?


texts: Selecting from random amount of tables with identical structure

texts:
I am programming along the lines of an unfavorable design. I have no influence on the schema because it belongs to a readymade standard software.

For simplicity let's say this software stores address books of several users. Each table is assigned with the name of the corresponding user.

The tables are created like this:

CREATE TABLE
    NAME_OF_USER
    (
        SURNAME VARCHAR(100),
        TOWN VARCHAR(100)
    )

So all tables have these columns:

surname
town

Two tables equalling two address books exist:

donaldduck
daisyduck

The address books have been filled like this:

INSERT
INTO
    DONALDDUCK
    (
        SURNAME,
        TOWN
    )
    VALUES
    (
        'Dagobert Duck',
        'Entenhausen'
    )

In order to retrieve a comprehensive list of addresses I could run

Select * from donaldduck
Select * from daisyduck

But what if someone adds an address book for GOOFY ? I would have to change my query for every newly created address book, in this case adding a SELECT * FROM GOOFY as the third row.

To make this even more complicated there are other tables with a different setup (they are storing other information which is totally irrelevant for my task of listing all entries from the given address books). So I can not simply iterate over all existing tables because this would lead to processing tables which I do not need in my query.

Is there a way to say "select * from all tables which contain a column named town" ? This should be solved by using plain SQL or PL/SQL because I want to avoid having to code this in a high level language like I think I would need to be doing by following advice from jsapkota.


texts: Very light queries are in slow-query-log ! Why?

texts:
I try to optimize my websites, because MySQL falled in timeout.
I've simplied queries and added indexes.
Server works fine, but in log stil some queries.

Amazing ! : the only queries on the slow-query-log are very simple and light queries. I don't understand !...
Can you help me ?

My site is on Private MySql servor, OVH.
Logs extract :

# Time: 170109 18:17:04
# User@Host: doctsfgl[doctsfgl] @  [10.0.83.198]  Id: 837382
# Query_time: 1.185552  Lock_time: 0.000117 Rows_sent: 0  Rows_examined: 1
SET timestamp=1483982224;
delete from gl_visiteurs
                where IP like '68.180.230.33';
# Time: 170109 18:17:05
# User@Host: doctsfgl[doctsfgl] @  [10.0.83.198]  Id: 837382
# Query_time: 1.440233  Lock_time: 0.000151 Rows_sent: 0  Rows_examined: 0
SET timestamp=1483982225;
INSERT INTO gl_visiteurs (IP, user_id)
                VALUES ('68.180.230.33',0);
# Time: 170109 18:17:20
# User@Host: doctsfgl[doctsfgl] @  [10.0.83.92]  Id: 837412
# Query_time: 2.540715  Lock_time: 0.000124 Rows_sent: 0  Rows_examined: 1
SET timestamp=1483982240;
delete from gl_visiteurs
                where IP like '109.220.253.15';
# Time: 170109 18:40:36
# User@Host: doctsfgl[doctsfgl] @  [10.0.83.198]  Id: 839694
# Query_time: 1.566650  Lock_time: 0.000095 Rows_sent: 0  Rows_examined: 1
SET timestamp=1483983636;
delete from gl_visiteurs
                where IP like '66.249.64.218';

These queries, when executed in PhpMyAdmin, run in very short laps, about 0.01 s !!!
All columns concerned are indexed.
Table contain less than 100 records, and 3 columns !...

An idea ?
Many thanks
(sorry for my english ; I'm french)

The create table statement:

CREATE TABLE IF NOT EXISTS gl_visiteurs
( IP varchar(15) NOT NULL DEFAULT '',
  start timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  logue int(11) NOT NULL DEFAULT '0',
  user_id int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Indices:

ALTER TABLE gl_visiteurs
 ADD PRIMARY KEY (IP,user_id),
 ADD KEY user_id (user_id),
 ADD KEY start (start),
 ADD KEY IP (IP);


texts:
I am having following scenarios and i am having some issues and some questions.

We have a DB Server on which we have some databases in these 2(two databases) are for my users.
Now i have created 2 windows group login A & B, A is the user which can only read data from 2 DB's, and B is the user which can do a restore to DB's.
Now what i like to know, is this possible to restrict user B to have restore DB rights only on 2 databases(i have many on my server)
Another problem i am facing is when i am restoring DB from user A than all rights assigned to user B from that specific DB are not there, so how can i always keep my user rights same for every DB.

I am using SQL Server 2008. Thanks in Advance.


texts: Scalability of concurrent log restore for different databases on SQL Server

texts:
Are there any locks or resource blocking/contention (beyond IO capacity) between two sessions, which restore logs for two different databases with norecovery? Same about restore logs with recovery?

a. My answer by now is “not much”, asking for validation.
Are there any locks or resource blocking/contention (beyond IO capacity) between two sessions, which restore two different databases with recovery from norecovery state?

a. My answer by now is “not much”, asking for validation.
What portions of concurrent log restore process (read backup/write to data/write to log, other) contribute into each of the major identified wait types BACKUPIO, IO_COMPLETION, LCK_M_S, BACKUPTHREAD, PREEMPTIVE_OS_FLUSHFILEBUFFERS, PAGEIOLATCH_EX

a. Most puzzling is LCK_M_S

I thought the question was very clear, but clarifying. The scenario is planning of log shipping of 2000-6000 DBs per server. There are two concurrency scenario - first is regular scheduled application of logs and second is DR scenario when all DBs need to be brought online using RESTORE DATABASE WITH RECOVERY.
Third question is a capture of actual top waits during test run of concurrent log restore. I do know what each wait mean, I don't know per SQL internals what portion of restore process is reflects and as such how can be optimized.


texts: Galera backup failing with error "mysqldump: Error 1213: Deadlock found when trying to get lock; try restarting transaction when dumping table"

texts:
We face the same issue like this posting on dba.stackexchange.com Deadlock from mysqldump --single-transaction, is it possible?. We offer DBaaS and can't control the DDL statements of our customers.

mysqldump: Error 1213: Deadlock found when trying to get lock; try restarting transaction when dumping table `x` at row: x

After that error the backup fails.

This is the backup command

mysqldump --max_allowed_packet=1G -u root -pvubaluaGebetee1aichaingubaePho --opt --flush-logs --single-transaction  --all-databases | bzcat -zc > /var/lib/mysql/backup/$FILE`date +%Y%m%d-%H%M%S`.sql.bz2

The accepted solution recommends Setup Master/Slave Replication. We use already Galera multi master replication.

> select version();
+----------------------+
| version()            |
+----------------------+
| 5.5.41-MariaDB-wsrep |
+----------------------+
1 row in set (0.00 sec)

other ideas howto make sure the backup completes? I don't care if a few rows not in backup.


texts: Howto query all files, their fill level and additional drive info with one query?

texts:
I need a query that puts together the following results:

one row per database file across all databases
additional columns with information about the locigal drive the file is on
information about the size and the usage of the file

So far I have put together the following query:

    SELECT
     GETDATE() as dt
     ,@@SERVERNAME as srv
     ,F.name
     ,F.physical_name
     ,Round(F.size * 8 / 1024, 2) as FileSizeMb
    , CAST(FILEPROPERTY(F.name, 'SpaceUsed') AS INT)/128 as FileUsedMB
    ,(F.size/128 - CAST(FILEPROPERTY(F.name, 'SpaceUsed') AS INT)/128) AS FileFreeMB
    ,Convert(decimal(18,2), (F.size/128 - CAST(FILEPROPERTY(F.name, 'SpaceUsed') AS INT)/128) / (F.size * 8 / 1024.1) * 100) as SpaceFreePerc
     ,stat.size_on_disk_bytes / 1024 / 1024 SizeOnDiskMb
     ,drv.volume_mount_point, drv.logical_volume_name, drv.available_bytes, drv.total_bytes
 FROM sys.master_files F
 inner join sys.dm_io_virtual_file_stats(NULL, NULL) stat on F.database_id = stat.database_id AND F.file_id = stat.file_id
 CROSS APPLY sys.dm_os_volume_stats(F.database_id, F.FILE_ID) drv

Now I face two problems here:

The FILEPROPERTY SpaceUsed is only available for the database in current context - so I need a solution to collect all Fileproperties from all dbs / files.
CROSS APPLY dm_os_volume_stats does not work for dbs with compatibility level 80, but I have some of those in place.

Is there a way to reach this goal with the given conditions? The solution does not have to be a one-statement query, since it will anyways be part of a stored procedure in the end.


texts: PostgreSQL get first letter of each word (except...)

texts:
I don't think I drank enough coffee today, but stuck on this for some reason.

Trying to get the first letter of each word, except a few stopwords.

Example "The United States of America" -> "USA"

select regexp_matches('The United States of America', '\y(?!(the|of)\y)\w', 'gi')

works fine, but it returns setof text[] .

How to convert that to a simple string? Or is there a better way than regexp_matches?


texts:
The error in its entirety reads:

  psql: could not connect to server: No such file or directory. Is the
  server running locally and accepting connections on Unix domain socket
  "/tmp/.s.PGSQL.5432"?

This is my second time setting up Postgresql via Homebrew on my Mac, and I have no clue what is going on. Previously, it had been working. At some point, I must've entered a command that messed things up. I'm not sure. Now, whenever I enter a SQL command from the command line, I receive the above message. I've run a command to check whether the server is running, and it apparently is not. If I attempt to start the server using

  $ postgres -D /usr/local/pgsql/data

I receive the following error:

  postgres cannot access the server configuration file
  "/usr/local/pgsql/data/postgresql.conf": No such file or directory

I've uninstalled and reinstalled Postgresql via Homebrew, but the problem persists. I'm completely at a loss as to how to get this working. Any help would be appreciated.


texts: MSG 666 when running an insert query on 80M-row indexed table

texts:
Strangely, my stored procedure started to receive Msg 666 for some input data.

Stored procedure fails on the last step when it tries to insert a row into a table with the following structure:

Columns:
A_Id: PK, int
B_Id: PK, FK, int
C_Id: PK, FK, int
D_Id: PK, smallint

This is essentially a table that connects all referenced entities together.

Indexes:
IX_TableName_D_id - Clustered index on D_id column
PK_TableName - Unique non-clustered index on all columns (A_Id, B_Id, C_Id, D_Id)

Fragmentation for both indexes is low (<25%). However PK_TableName fragmentation quickly grows, since the amount of operation on the table is quite intense.

Table size:

Row count: ~80,000,000 rows

So, when I try to run a veeery simple query, for some of D_Id's I get the following message:

  Msg 666. The maximum system-generated unique value for a duplicate group was
  exceeded for index with partition ID 422223771074560. Dropping and
  re-creating the index may resolve this; otherwise, use another
  clustering key.

Query example:

INSERT INTO TableName
(A_Id,B_Id,C_Id,D_id)
VALUES (1,1,1,14)

For example, when I set D_Id to some values - it fails, '14' for example. If I set D_ID to other values (1,2,3,...13, 15,16,...), the query runs fine.

I suspect there's something really bad going on with indexes... But I cannot get to the bottom of this... :( Why it fails?


texts:
I have a job with a few steps(that run reports information) and i added a query i wrote to the last step.
Sometimes the job fails because of factors i can not control (application issues)
and my query basically automatically executes jobs (the commands are running with a cursor in a table i made)when the current time is after 8:45 am, so the BI team won't need to do it themselves.
I checked it a few times and it seems to work when i execute it on ssms, but for some reason when it runs as a job it ignores the time condition and just execute the jobs anyway.

The query:

DECLARE @CodeID INT
declare @codecommand nvarchar(100)
DECLARE crsr CURSOR FAST_FORWARD
    FOR
        SELECT CodeID, CodeCommand
        FROM exec_jobs_on_error
        ORDER BY codeid asc;

    OPEN crsr
    FETCH NEXT FROM crsr
    INTO @CodeID, @CodeCommand

    WHILE @@FETCH_STATUS = 0
    begin

BEGIN TRY
if exists (select 'true' where  LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7)) > '08:45AM')
begin
EXEC sp_executesql  @codeCommand

INSERT INTO the_daily_BI_error_log
            VALUES(@CodeID, 'Completed', GETDATE())

            end

        END TRY
        BEGIN CATCH
        declare @CodeError nvarchar(100)
            SET @CodeError = (SELECT ERROR_MESSAGE())
            insert into the_daily_BI_error_log
            values(@codeid, @CodeError, GETDATE())
            END CATCH

        FETCH NEXT FROM crsr
        INTO @CodeID, @CodeCommand
        end
    CLOSE crsr
    DEALLOCATE crsr

Am i missing something in the condition writing?thanks.


texts: Im familiar with postresql WAL file and its purpose. Every transaction and the commit records of each transaction will be logged in this file .While recovery, it replays from the last checkpoint till the end to bring the database to a stable state. But there is a good chance that the uncommitted transactions would have made their way into the WAL files. How does postgres escape these uncommitted transaction records from being replayed? How is roll-backward recovery(UNDO ing the uncommitted transactions) implemented in postgresql?


texts: Can one assign a performance score to a stored proc to compare performance of 2 procs

texts:
I have an existing stored procedure,
and an updated version of that same stored procedure where changes have been made, but the same resultset is returned.

The updated version of the procedure focused on using existing #temp tables in order to do calculations as opposed to going back to the database a lot of times in order to get result values. Also a few tweaks were made to the updated version of the procedure.

Now the updated version runs a bit faster, but I can't consistently prove it because the line to the database which I query is remote and fluctuates with usage on the line.

My question - is there a common denominator which I can use as a "performance score", in order to see which of the 2 procedures are more efficient - less database calls for example?


texts: Two conversions of practically the same number giving two different results

texts:
To my surprise, I run the following queries in SQL Server 2014 and each returns a different result, as shown in this db<>fiddle...

This

Select convert(numeric(19,2),convert(float,9.76499999999999999999999999))

gives 9.76.

And this

Select convert(numeric(19,2),convert(float,9.7649999999999999999999))

gives 9.77.

Can someone explain why one truncates while the other rounds up?
This is causing a nightmare for me.

I wish I could just stop using floats but they are all over our application, which has been ongoing for over 30 years.