texts:
I have been using a cursor to get my results, but it is taking an extended period of time to return my result set.  I am hoping to find a way to QUICKLY get the results returned (if at all possible).  Here is my data structure with garbage DDL.

1st problem is each employee has there own table.  I understand this is a major design flaw, and we are quickly working to remedy this, but I need an alternative until all elements have been updated.  All 3 tables geoffrey, jorge, mitch all have the same structure, and I have attached sample DDL for geoffrey, & jorge and below is a sample query that shows what I need to query from each of the two tables.

Greatly appreciate any and all help on how to achieve this result!

Create Table #ReviewDB
(
  [TableName] varchar(500)
  ,[ServerDBTable] varchar(MAX)
)
Insert Into #ReviewDB VALUES
('geoffrey', 'beans.franks.dbo.geoffrey')
,('jorge', 'smallpox.virus.dbo.jorge')
,('mitch', 'mosquito.insect.dbo.mitch')
Create Table #geoffrey
(
  recordID int IDENTITY(1,1) PRIMARY KEY
  ,firstname varchar(500)
  ,lastname varchar(500)
  ,address varchar(500)
  ,city varchar(500)
  ,state varchar(500)
  ,zip varchar(500)
  ,phone varchar(500)
  ,itemsold varchar(500)
  ,saledate datetime
  ,managerapproved varchar(500)
)
Insert Into #geoffrey VALUES
('manny', 'ramirez', '1111111 aoaswdfrljkasdf ', 'topaz', 'mn', '1111', '9995552222', 'sofa', '01/21/2016', '')
,('hi', 'ho', '2323 aoaswdfrljkasdf ', 'topaz', 'mn', '1111', '6662229888', 'chair', '02/21/2016', '')
,('ee', 'aa', '4646 aoaswdfrljkasdf ', 'topaz', 'mn', '1111', '3332221919', 'ottoman', '01/21/2016', '')
Create Table #jorge
(
  recordID int IDENTITY(1,1) PRIMARY KEY
  ,firstname varchar(500)
  ,lastname varchar(500)
  ,address varchar(500)
  ,city varchar(500)
  ,state varchar(500)
  ,zip varchar(500)
  ,phone varchar(500)
  ,itemsold varchar(500)
  ,saledate datetime
  ,managerapproved varchar(500)
)
Insert Into #jorge VALUES
('aa', 'bb', '1111111 c street ', 'holt', 'tn', '2222', '1113334444', 'sofa', '01/21/2016', '')
,('cc', 'ddo', '2323 b street ', 'holt', 'tn', '2222', '8889997788', 'chair', '02/21/2016', '')
,('mm', 'rr', '4646 e street ', 'holt', 'tn', '2222', '8889998877', 'ottoman', '03/21/2016', '')
Create Table #managerrevieweddata
(
  ID int IDENTITY(1,1) PRIMARY KEY
  ,recordID int
  ,dbname varchar(500)
  ,managernotes text
  ,regionalmanager varchar(500)
  ,districtmanager varchar(500)
  ,incentiveitem varchar(500)
)
Insert Into #managerrevieweddata VALUES
('1', 'geoffrey', 'Valid sale, remind to offer upsell next time.', 'Regional Manager', 'District Manager', 'No')
,('2', 'jorge', 'Review with salesman', 'Regional Manager', 'District Manager', 'NO')

And here is a query that shows what I want my desired output to be.

  I need a way to query each serverdbtable in #ReviewDB and return the below select statement

Declare @saledate datetime
Set @saledate = '03/21/2016'

Select a.firstname, a.lastname, a.address, a.city,
a.state, a.zip, a.phone,   a.itemsold, a.saledate,
b.dbname, b.managernotes, b.regionalmanager,
b.districtmanager, b.incentiveitem
FROM #ReviewDB.ServerDBTable a
INNER JOIN #managerrevieweddata b
ON a.recordID = b.recordID
and a.saledate = @saledate


texts: Understanding of SQL Server active/active cluster

texts:
I have a vague understanding of SQL Server's active/active cluster. Can anyone help to confirm if my understanding is correct?

As I understand, active/active cluster use two or more Windows servers. Let's say we have two servers n1 and n2. Then we create a failover cluster on those two servers and join n1 and n2 into the cluster. Then we install a SQL Server instance i1 on n1 and n2. After that we install another SQL Server instance i2 on n1 and n2. Then we can start i1 on n1 and start i2 on n2 to make a active/active cluster. Later we can failover i1 from n1 to n2 and failover i2 from n2 to n1.

Is my understanding correct? Do we need to install the instances i1 & i2 on both n1 and n2? After the installation and configuration of the active/active cluster, how many SQL Server services are installed and running on each node?


texts: What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

texts:
I have created a user and granted many permissions and roles to it including SQLAgentUserRole and SQLAgentReaderRole.

My application checks the status of the SQL Server Agent to see if it is running or not, and whenever I am trying to run my application using the user I have created it is not able to identify a Running SQL Server Agent.

When I do the same thing using a user in the sysadmin role it works fine.

We are using master..xp_servicecontrol to check status of SQL Agent Service and I already provided user with

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO User;

Still it is not working. Any help?


texts: Availability Group, asynchronous mode, manual/forced failover - handling identity overlap

texts:
We have a two replica 2017 Standard Availability Group in asynchronous mode.  With a failover (manual/forced, since can't do automatic), we have the potential for overlapping/reused identity values if the secondary is lagging behind the primary.  This is really bad for us...CustomerA seeing CustomerB's data.

EDIT: I'm mostly concerned about a DR/failure failover where the primary goes offline unexpectedly.  A planned failover (patching, etc.) would be easier to manage by waiting for or forcing the secondary to sync up to the primary before taking the primary offline, as suggested below.

Is there a way to automatically run a script as the secondary takes over the primary role, to bump all identity columns (DBCC CHECKIDENT RESEED) to allow for the eventual merging of the old primary's unsynced records?
Since this is Standard, the secondary is inaccessible until it takes over as the primary, so when/how would the script run?
Side question: Since the old primary becomes the secondary (once it's back online) and is at that point inaccessible (Standard), how does one run queries on it to determine the unsynced records?  Back up/restore the old primary as a nonAG db?

Thanks!


texts:
I've a table Columns

and a second table Response in which all data is saved.

Now I want to create a SQL View in which the result should be like this

I tried using pivot

select UserId ,FromDate, ToDate, Project, Comment
from
(
  select R.UserId ,R.Text , C.ColumnName
  from [Columns] C
  INNER JOIN Response R ON C.Id=R.ColumnId
) d
pivot
(
  max(Text)
  for ColumnName in (FromDate, ToDate, Project, Comment)
) piv;

but that didn't worked for me, I also referred this https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server but was not able to implement it. Any ideas how to achieve the same in SQL View?

Scripts for Tables:

CREATE TABLE [dbo].[Columns](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](1000) NULL,
    [IsActive] [bit] NULL,
 CONSTRAINT [PK_Columns] 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]

GO

insert into [Columns] values('FromDate',1)
insert into [Columns] values('ToDate',1)
insert into [Columns] values('Project',1)
insert into [Columns] values('Comment',1)

CREATE TABLE [dbo].[Response](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [UserId] [bigint]  NOT NULL,
    [ColumnId] [bigint]  NOT NULL,
    [Text] [nvarchar](max) NULL,
    [IsActive] [bit] NULL,
    CONSTRAINT [PK_Response] 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]

GO
insert into [Response] values(1,1,'1/1/2012',1)
insert into [Response] values(1,2,'1/2/2012',1)
insert into [Response] values(1,3,'p1',1)
insert into [Response] values(1,4,'c1',1)
insert into [Response] values(2,1,'1/1/2013',1)
insert into [Response] values(2,2,'1/2/2013',1)
insert into [Response] values(2,3,'p2',1)
insert into [Response] values(2,4,'c2',1)
insert into [Response] values(2,1,'1/1/2014',1)
insert into [Response] values(2,2,'1/2/2014',1)
insert into [Response] values(2,3,'p3',1)
insert into [Response] values(2,4,'c3',1)
insert into [Response] values(3,1,'1/1/2015',1)
insert into [Response] values(3,2,'1/2/2015',1)
insert into [Response] values(3,3,'p4',1)
insert into [Response] values(3,4,'c4',1)


texts: how to Upload xml data to db Table by check primary key in oracle

texts:
i want to upload xml file data to db table by checking the primary key and wants update the each row data.Here trid getting as a primary key.example

OFF
    1133
    2388977

is their any solution for upload xml to db table by check primary key in oracle ?


texts:
I have 2 CentOS servers, one is running PostgreSQL 9.2 and the other, 9.3.

I'm trying to migrate the database but I'm getting 'relation  does not exist' errors.

What I did was on the 9.2 server:

pg_dump -h localhost -W -U postgres --clean DBName > dbname.dump

copied the dbname.dump file to the 9.3 server and then:

pgsql -h localhost -W -U postgres -f dbname.dump

Am I doing something wrong?

Other misc info, I am using the postgis extension and have some foreign key restraints.


texts: Minimal Permissions to Create an Application Database and Database Owner

texts:
I wish to write (or borrow if you have one) a script template for creating an application database and associated logins. The script is to be run in CI pipelines and on developer desktops.
Possibly influenced by postgres but also by MS maybe-saying that assigning ownership to a disabled login is best practice I wish to assign ownership of the database to some insignificant login created for the purpose:
Create Login appname_owner With Password = 'randomlygeneratedpassword';
Alter Login appname_owner Disable
Create Database appname  ;
Alter Authorization On database::appname To appname_owner ;

Question: What is the minimal set of permissions that will allow a CI login to do this?

I believe that Create Any Database and Alter Any Login are minimal for create login & database. [ I can't say I'm happy with CI scripts needing Alter Any Login. It would be nicer to restrict alterable Logins to some group or role defined for the purpose.]
But I'm stumped on Alter Authorization. It seems I need  Grant Impersonate On Login::appname_owner To CILogin, which the CILogin can't grant to itself, not even for a login it has just created.

How can I resolve this,

without the CI scripts using an sa login and
preferably, without granting the CILogin Impersonation rights on all logins to the server

?


texts: Memory utilization at 98%, why not 100%?

texts:
I wonder why SQL Server does not use all available memory - 100% when I am quite sure there is a memory bottleneck. For example, one server I administer starts with using 91% of memory and gradually increases up to 98 but not 100. Increase trend is as follows after a restart at 23:00:

58% after restart and reaches 79% throughout the night. I suppose this should be due to overnight back-ups taken.
82% by 11:00 next day.
90% by 12:00
A steady trend throughout the day until 23:00 when memory utilization increases to 93%
95% next morning at 08:00
97% at 17:00
98% next day at 14:00 and stays there.

What might be causing that 2% not being utilized?  Is it possible that Windows O/S is preventing it? If so, how can I prove that indeed Windows is keeping it to itself?

I am referring to the overall memory usage by the server - i.e the OS. Max memory configuration is already set well beyond the server's current memory. I have a monitoring tool at hand for tracking memory utilization.

Memory utilization by the server is no less nor above 98%. This is a dedicated, single-instance, two database SQL Server on which no other services, applications etc. are running. SQL Server might be using most of the allocated memory -i.e. 98%- but my question is why is it not using the rest 2%? I'm not sure if I am becoming too peevish in questioning that 2%?

Thanks to the query Determining Current Memory Allocation in Monitor memory usage I confirm Memory_usedby_Sqlserver_GB is 60 out of 64 GB total RAM. I was unable to find the column for max server memory and total RAM in the output of sys.dm_os_process_memory DMV (maybe due to SQL Server 2008 R2) but I may confirm it is a 64 GB server and max. server memory is set to 2147483647 MB.

I don't know if my server will utilize any extra memory once they are put in the slots. I am trying to prove if the server needs more memory, (if so) will make use of it and thus make a cause to push for purchase. I will be left in an awkward position if the memory is bought and memory utilization does not increase and stays in its current state - 60 GB. Seeing 60 GB of RAM being utilized after increasing the server's memory from 64 GB to 128 GB will be a waste of money.I want to make sure before the fact if the server will benefit from the new extra RAMs or be indifferent.


texts:
If I need to update same row with two concurrent thread then is it possible in SQL Server?

Suppose I have one table that contains Id and Name columns and now I want to update the third number Id with Name 'xyz' using one thread. Using the other thread, I want to update the same name which I am updating using the first thread, but it gives error like your connection is in connecting state. I can't update the data.

Does anyone know how to solve it? Do I need to assign a lock?


texts: SQL Server login failed, but user has permissions

texts:
First off, I'm pretty sure I've found every related post on the entire internet, so please read carefully before marking as a duplicate.

I have an ASP.NET site, hosted with IIS, using SQL Server Express. It's set to use Windows Authentication, and I'd prefer it to stay that way (although, at this point, I'm not picky). All three programs/services are on the same machine.

There are two databases: one for site logins, another for site data. The logins one works fine, yet both are set up identically.

The user in question has db_owner membership under the database in question. This is the Windows user that corresponds to the ApplicationPoolIdentity option in IIS.

Error:

  Cannot open database "EDB-Content" requested by the login. The login failed.
  Login failed for user 'IIS APPPOOL\EDB'.

Connection strings:

  name="LoginDB" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=aspnetdb;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"

  name="ExcDbContext" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=EDB-Content;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"

Stack trace:

https://pastebin.com/embed_iframe/RQN3uwUM

I added the login/user via SSMS, which had no problems with the space. I don't even know how I'd add quotes/brackets.

I have checked the SQL Server error log before, but this time I dug deeper. I restarted SQL Server and checked to see if everything is starting up correctly. It seems the EDB-Content database never gets started:

Any idea what's going wrong there?

In sys.databases the state_desc of that database is ONLINE.

The article Allowing IIS 7.5 Applications to communicate with SQL server via Windows Authentication by Giles Middleton details exactly as I've already done.

IIS APPPOOL\EDB is the Windows user.

Non-exhaustive list of things I've tried: (will update as I recall/try things)

Confirmed that the login can connect to the server (GRANT CONNECT TO SQL <login>)
Added and removed the login from database and global levels
Restarted just about everything that can be restarted (including
Wrapped the DB name in quotes in connection string (&quot;)
See update below
Confirmed that "LoginDB" (aspnetdb.mdf) is indeed being read/written to

Update

I've narrowed down the problem, but not why it's a problem. If I detach and delete the database and change the Initial Catalog and AttachDbFilename values in the connection string, a new database is generated next time I run the website that works fine, even down to the database file being in App_Data as it's supposed to. However, oddly, it is not attached to SQL Server. If I attach it myself, it does so without complaint, but subsequent runs of the website generate the above error. If I detach the DB, the website still throws an exception. The only fix I've found is to create a whole new database by changing Initial Catalog and AttachDbFilename.

At this point my connection strings are as follows:

  name="LoginDB" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=aspnetdb;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"

  name="ExcDbContext" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=contentdb;AttachDbFilename=|DataDirectory|\contentdb.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"


texts: What is the underlying file system in ASM if exists? I have gone through many sites but neither of them answers my question directly rather each site I visisted raises a different point like 'ASM uses ACFS file system' or makes the topic complicated.


texts: AlwaysON AG synchronous commit mode disconnected transaction log effect and mitigation

texts:
I have an AlwaysON AG with one primary and one secondary replica in synchronous commit mode.  The databases in the AG all have autogrowth disabled (standard policy within organisation).  There is planned hardware maintenance on the server that hosts the secondary replica that will result in the server being offline for 4-8 hours.  My undestanding is that when this happens, the connected state of the secondary replica will change to DISCONNECTED, the send queue of the databases in the primary replica will accumulate unsent transaction log records.  For as long as the secondary replica remains offline, all of the current log records remain active (autogrowth is disabled), transaction log backups will not truncate the log with the potential of the log filling.  Can someone please confirm if this is correct?  Is there any way we can mitigate this, ie. ensuring there's no index maintenance during this window or switching to aynschronous commit mode?

That was my understanding, but after reading this msdn article https://msdn.microsoft.com/en-us/library/ff877931%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396 I am confused.  There is a note that states: "If primary's session-timeout period is exceeded by a secondary replica, the primary replica temporarily shifts into asynchronous-commit mode for that secondary replica. When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode."

Does this pertain to my situation as described above and if so does this mean that the potential for the transaction to fill is mitigated by design? Can this temporary shift in commit mode be seen when querying sys.availabilty_replicas or via any extended event or is it a purely a under the covers change?


texts: when running SSIS package with Windows Auth it works but with SQL Auth it fails

texts:
I have setup a SSIS package, and within one of my connections to the same server that the package is on it fails when I use SQL server Auth for login and it does not matter which user I put in.

I can put in my user name which works fine when logging in from SQL Management studio and which works fine when I run the package manually and the connection is set to Windows Auth.

The error I get is Test connection failed because of an error in initializing provider. Login failed for user.

I even tried using our SA account and it fails as well.

Connection string:
Data Source=SQL004;Initial Catalog=SAP_TO_E1;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=SSIS-Package-{1AE2C7B9-91AC-34B3FB255590}SQL004.SAP_TO_E1;Auto Translate=False;

It's an OLEDB connection.


texts:
I was surprised to see MySQL's InnoDB showing an interesting behavior, which I cannot fully explain. According to the official MySQL InnoDB Documentation:

  "All indexes other than the clustered index are known as secondary
  indexes. In InnoDB, each record in a secondary index contains the
  primary key columns for the row, as well as the columns specified for
  the secondary index. InnoDB uses this primary key value to search for
  the row in the clustered index"

So to my understanding, any single-column index is actually a compound index over the selected column and the primary key (please correct me if this is not the case). Thus if I select from a table filtering by an indexed column and sorting by the primary key it should be an effective operation not requiring a filesort.

In practice, however, this is not the case as I illustrate below:

mysql> describe object_settings;
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| object_setting_id | int(11)      | NO   | PRI | NULL    | auto_increment |
| object_id         | int(11)      | NO   | MUL | NULL    |                |
| name              | varchar(50)  | YES  | MUL | NULL    |                |
| value             | varchar(255) | YES  |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from object_settings;
+-------------------+-----------+------+-------+
| object_setting_id | object_id | name | value |
+-------------------+-----------+------+-------+
|                 1 |        10 | foo  | bar   |
|                 2 |        10 | bar  | foo   |
|                 3 |        11 | bar  | foo   |
|                 4 |        12 | bar  | foo   |
|                 5 |        13 | bar  | foo   |
+-------------------+-----------+------+-------+
5 rows in set (0.00 sec)

mysql> explain select * from object_settings where object_id = 10 order by object_setting_id DESC;
+----+-------------+-----------------+------+---------------+-----------+---------+-------+------+-----------------------------+
| id | select_type | table           | type | possible_keys | key       | key_len | ref   | rows | Extra                       |
+----+-------------+-----------------+------+---------------+-----------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | object_settings | ref  | object_id     | object_id | 4       | const |    2 | Using where; Using filesort |
+----+-------------+-----------------+------+---------------+-----------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

Why is there a filesort operation present all of a sudden? Or does the MySQL Documentation mean something completely different to my understanding?


texts: Nested AD group access not working when nested more than 2 levels deep?

texts:
For the setup of the security of the databases we have created a number of nested Active Directory groups:

Group
Members
Group type

RGP-EA_PROD-RW
RGP-SparxEA-RW
Domain Local Security

RGP-SparxEA-RW
BGP-FunctionalAnalyst
Domain Local Security

BGP-FunctionalAnalyst
User1
Global Security

On my database I have added the group RGP-EA_PROD-RW as a user login and given it db_datareader and db_datawriter roles.
When User1 tries to login he gets an Access Denied error.
User1 is a member of BGP-FunctionalAnalyst who is a member of RGP-SparxEA-RW, who is a member of RGP-EA_PROD-RW. So User1 is ultimately a member of RGP-EA_PROD-RW.

The SQL Server Logs record two events when the login fails:
Date        3-5-2021 08:56:17 Log       SQL Server (Current - 15-4-2021 05:21:00)
Source      Logon
Message Error: 18456, Severity: 14, State: 38.

and
Date        3-5-2021 08:56:17 Log       SQL Server (Current - 15-4-2021 05:21:00)
Source      Logon
Message Login failed for user 'MYDOMAIN\User1'. Reason: Failed to open the explicitly specified database 'AE_PROD'. [CLIENT: 100.74.0.4]

The database is online, and the AD group has been setup to have access to it. It works just fine with the other groups, just not with the group nested at the third level.
I did a little bit of experimenting and found that when setting up logins:

Login User1 directly works
Login BGP-FunctionalAnalyst works
Login RGP-SparxEA-RW works
Login RGP-EA_PROD-RW does not work

I'm not sure what is causing this problem:

Is there a maximum level of nesting supported by SQL Server?
Is there a certain property I should look for in AD? (both Domain Local groups look similar to me)
Something else?

This is a completely new Active Directory with only a handful of users and groups.
We are trying to apply the AGDLP guideline in a single AD domain.

RGxxx groups are Resource Groups, defined as Local Security Groups. They are used to assign access rights to resources, and they only have other groups as members, no users.
BGxxx groups are Business Groups defined as Global Security Groups. They have only users as members and are not used to assign access rights.


texts: How to deny database acess to windows autentication users

texts:
We have a Database that is attached on the client server, but we don´t want to let anyone besides our user to acess that database, is there a way to do this? I´m using the current script to create our user, 'automacao'. And I tried to modify it to not allow anyone besides 'automacao' to acess it, but they're using windows autentication to acess it, there´s a way to deny their view permission?

DECLARE @USERNAME VARCHAR(MAX) = N'automacao'
DECLARE @TABLEVISIVEL INT = 0 -- 1 SIGNIFICA QUE AS TABELAS E SP ESTARÃO VISIVEIS, 0 SIGNIFICA QUE NÃO ESTARÃO VISIVEIS

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = @USERNAME)
BEGIN
    EXEC('DROP USER '+@USERNAME)
END

EXEC('CREATE USER '+ @USERNAME +' FOR LOGIN '+ @USERNAME +' WITH DEFAULT_SCHEMA=[guest]')

EXEC sp_addrolemember N'db_datawriter',@USERNAME

EXEC sp_addrolemember N'db_backupoperator', @USERNAME

EXEC sp_addrolemember N'db_datareader', @USERNAME

DECLARE @DENYTAKEOWNERSHIP VARCHAR(MAX) = 'DENY TAKE OWNERSHIP ON ? TO '+ @USERNAME
DECLARE @DENYALTER VARCHAR(MAX) = 'DENY ALTER ON ? TO '+ @USERNAME
DECLARE @GRANTSELECT VARCHAR(MAX) = 'GRANT SELECT ON ? TO ' + @USERNAME
DECLARE @GRANTUPDATE VARCHAR(MAX) = 'GRANT UPDATE ON ? TO ' + @USERNAME
DECLARE @GRANTDELETE VARCHAR(MAX) = 'GRANT DELETE ON ? TO ' + @USERNAME
DECLARE @GRANTINSERT VARCHAR(MAX) = 'GRANT INSERT ON ? TO ' + @USERNAME

EXEC sp_MSforeachtable @DENYTAKEOWNERSHIP
EXEC sp_MSforeachtable @DENYALTER
EXEC sp_MSforeachtable @GRANTSELECT
EXEC sp_MSforeachtable @GRANTUPDATE
EXEC sp_MSforeachtable @GRANTDELETE
EXEC sp_MSforeachtable @GRANTINSERT

DECLARE @StoredProcedureName nvarchar(250);
DECLARE @CMDEXEC1 nvarchar(2000);

DECLARE db_cursor CURSOR FOR
SELECT  SPECIFIC_NAME
FROM    information_schema.routines
WHERE   routine_type = 'PROCEDURE'
    and Left(Routine_Name, 3) NOT IN ('sp_', 'xp_', 'ms_')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @StoredProcedureName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @CMDEXEC1 = 'DENY TAKE OWNERSHIP ON [' + @StoredProcedureName + '] TO '+ @USERNAME + ';'
    SET @CMDEXEC1 = @CMDEXEC1 + 'DENY ALTER ON [' + @StoredProcedureName + '] TO '+ @USERNAME + ';'
    SET @CMDEXEC1 = @CMDEXEC1 + 'GRANT EXECUTE ON [' + @StoredProcedureName + '] TO '+ @USERNAME + ';'
    EXEC(@CMDEXEC1)
    FETCH NEXT FROM db_cursor INTO @StoredProcedureName
END
CLOSE db_cursor
DEALLOCATE db_cursor
IF(@TABLEVISIVEL = 0)
BEGIN
    DECLARE @CMD1 VARCHAR(MAX) = 'DENY VIEW DEFINITION TO '+@USERNAME+';'
    EXEC(@CMD1)
END
ELSE IF(@TABLEVISIVEL = 1)
BEGIN
    DECLARE @CMD0 VARCHAR(MAX) = 'GRANT VIEW DEFINITION TO '+@USERNAME+';'
    EXEC(@CMD0)
END
EXEC('GRANT EXECUTE TO '+@USERNAME)
GO


texts:
I am wondering if there are examples of open source apps that are written in stored procedures? I read a lot about them in textbooks and would like to see how they are used in practice beyond implementing simple functionalities. One example that I found is the DBT benchmark implementations (http://sourceforge.net/apps/mediawiki/osdldbt) using stored procedures. If there are other similar applications I'd love to learn about them.

@a_horse_with_no_name: thanks for your pointer. Apps like LedgerSMB that makes heavy uses of stored procedures are exactly those that I am looking for. If there are other similar ones please let me know.


texts: SQL ISO Standard for group concatenation

texts:
Is there an operator in the latest standard SQL:2011 (or earlier) that will handle group concatenation the likes of which we currently have to resort to these tricks for?

GROUP_CONCAT in MySQL
FOR XML in SQL Server
LISTAGG in Oracle
string_agg in PostgreSQL

Here's a recent question on this site for which various answers were given to different RDBMS.

Since this is a small question for the heavyweight site DBA.SE, please find two sub-parts to the question:

Is there a publicly accessible draft of SQL:2011?
Which part of the SQL standard (any revision) states that CONCAT NULL should YIELD NULL? I can't seem to track it down in the volume of text in the standard.


texts: Converting RTF in a text column to plain text in bulk

texts:
I have a legacy system with about 10 million rows in a table. In that table there is a column of type text, most of them are standard text but about 500 thousand rows have RTF markup in them. I need to convert the RTF formatted text in to plain text.

My current method is I have a C# program that loads the query in to a DataTable using a SqlDataAdapter and uses the winforms RichTextBox control to do the conversion.

void bw_DoWork(object sender, DoWorkEventArgs e)
{
    count = 0;

    rtbRTFToPlain = new RichTextBox();

    using (SqlDataAdapter ada = new SqlDataAdapter("select note_guid, notes from client_notes", Globals.SQLConnectionString))
    using(SqlCommandBuilder cmb = new SqlCommandBuilder(ada))
    {
        DataTable dt = new DataTable();
        ada.UpdateCommand = cmb.GetUpdateCommand();

        ada.Fill(dt);

        int reportEvery = dt.Rows.Count / 100;
        if (reportEvery == 0)
            reportEvery = 1;
        foreach (DataRow row in dt.Rows)
        {
            if (count % reportEvery == 0)
                bw.ReportProgress(count / reportEvery);

            try
            {
                if (((string)row["notes"]).TrimStart().StartsWith("{") == true)
                {
                    rtbRTFToPlain.Rtf = (string)row["notes"];
                    row["notes"] = rtbRTFToPlain.Text;
                }
            }
            catch
            {
            }

            count++;

        }
        bw.ReportProgress(100);

        this.Invoke(new Action(() =>
            {
                this.ControlBox = false;
                this.Text = "Updating database please wait";
            }));
        ada.Update(dt);
    }
}

This is working great for small tables, however this is the first time I had to run it on a table with such a large data-set (some of the rtf files can be several megabytes in size with embedded pictures) and I am getting OutOfMemory errors with my C# program.

I know I can chunk my query down in to a smaller batches, but I wanted to see if there is a better way that I was missing to strip off RTF formatting.

Should I just do the same thing as my current solution but only query out data smaller chunks at a time, or is there a better way to do this?


texts: Postgres latest sequence id and uncommitted writes

texts:
Hoping someone could clarify a section of this article "scalable-incremental-data-aggregation" and the corresponding question.
Assume Postgres 10 and up.

In Postgres 10, you can use the pg_sequence_last_value function to check the most recently issued sequence number.  However, it would not be safe to simply aggregate all events up to the most recent sequence value. There might still be in-progress writes to the events table that were assigned lower sequence values, but are not yet visible when the aggregation runs.

Are there alternatives to pg_sequence_last_value that guarantee the correct seq id?

In a table (call it events) with a BIGSERIAL id, is there a preferred way to fetch the latest seq id for aggregation purposes? It's fine if there are pending inserts in a different transaction (they'll have higher sequence ids) and be picked up on subsequent runs.
The goal is to use a seq id to run aggregation (incremental rollups) on already committed inserts.
Are the following three methods guaranteed to return the latest seq id for committed inserts and avoid the pitfall mentioned in the article relating to pg_sequence_last_value?
Are there other techniques one might suggest?
SELECT max(id) FROM events;

SELECT last_value FROM pg_sequences WHERE sequencename='events_id_seq';

SELECT id FROM events ORDER BY id DESC LIMIT 1;

Assume read committed isolation level


texts: Enterprise Manager goes pink, legend color for other

texts:
To whom it may respond to,

While monitoring our databases, we have seen that one of EM screens has gone pink, the color stands for OTHER at the legend. We have checked alert log and trc files, indicating deadlocks,but not sure that is the reason or not. Any further methods that you can offer to dig out the root cause of this?

Thank you for concern,


texts: Query on Linked Server in SQL Active-Passive

texts:
We have plan to implement Active-Passive SQL server cluster with shared storage with SQL 2014 version.
A-P: There will be two DB servers (DB1 and DB2) and one will be working (DB1) and the other (DB2) does not operate until there is any problem in the first one. Once the working cluster SQL server fails (fail – reboot/hardware failure/OS corruption, etc.), the other server (DB2) takes over from it and starts functioning as regular SQL server.

Just I want to know behaviour of linked server in A-P environmnet during below cases.

We have configured linked server on DB1 (active node) to fetch remote SQL data. If we perform failover from DB1-DB2, Will linked server work automatically on DB2 active node? or Is there any manual modifications are required to work linked server properly?
When we failback from DB2 to DB1, Will linked server work automatically on DB1 active node? or Is there any manual modifications are required to work linked server properly?
Can we use SQL Virtual IP in linked server configuration in A-P environment?


texts: query optimization for update nested query IN(SELECT

texts:
Can we optimize this?

UPDATE TABLE_NOTE
SET COL_NOTE_DISPLAY='BLOCK'
WHERE COL_NOTE_BIBID = $projectID
  AND $COL_NOTE_ID IN
      ( SELECT COL_PILENOTEREL_NOTE_ID
        FROM TABLE_PILENOTEREL
        WHERE $COL_PILENOTEREL_PILE_ID = $pileId
      ) ;


texts: How can I better optimize my tables and a LEFT JOIN query to find items that don't yet exist in the right table?

texts:
I have two tables that represent a list of urls and their related word indexes. Here are the table definitions for reference.

desc urllist;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| url   | text                | NO   |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+

and

desc wordlocation;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| urlid    | bigint(20) unsigned | NO   |     | NULL    |       |
| wordid   | bigint(20) unsigned | NO   |     | NULL    |       |
| location | int(10) unsigned    | NO   |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+

The software app is a web spider. It crawls a list of urls, extracts those urls, and inserts these into the urllist table. Then, an indexer checks to see what urls have not yet been indexed, and then proceeds to index said urls.

Here is the query I am using to find items in the left table (urllist) that have not yet been indexed in the right table (wordlocation). This query is as suggested on the mysql.com website:

select * from urllist ul
left join wordlocation wl on ul.id = wl.urlid
where wl.urlid IS NULL;

As of this writing my test database has only 600 indexed urls, and the wordlocation table has 1.3 million rows.  However, my CPU is at 100%, and the longest I've waited to see if the query would complete is a half hour (which, it never did by the way).

To be thorough, here is the explanation of the query:

explain select * from urllist ul left join wordlocation wl on ul.id = wl.urlid where wl.urlid IS NULL;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra                   |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+
|  1 | SIMPLE      | ul    | ALL  | NULL          | NULL | NULL    | NULL |   50364 |                         |
|  1 | SIMPLE      | wl    | ALL  | NULL          | NULL | NULL    | NULL | 1351371 | Using where; Not exists |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+

I need this query to finish in seconds, not minutes. Also, I'm worried about scalability. I have 40,000 unique urls that are waiting to be added to the index, so how do I take that into consideration with my table and query design? 400,000 urls?

Just a couple notes about my decisions on the current table structure.

I have no intention of stopping at 400,000 urls, but perhaps bigint(20) is a bit overzealous?

Url as text is for more practical reasons. I index a lot of Asian and other foreign language domains which do not appear as their equivalent Kanji or other characters in the database and frequently take more than 255 characters.

I'm using MySQL. I'm definitely open to suggestions as to better table and query design. Please let me know if I can provide more information.


texts: Delete old records based on its identifier

texts:
For example, I have a table similar to this:

deviceid | servertime
1          2019-05-13 12:00:00
1          2019-05-14 12:00:00
2          2019-05-13 13:00:00
2          2019-05-12 13:00:00
2          2019-05-11 13:00:00
2          2019-05-10 13:00:00
3          2019-05-13 10:00:00
4          2019-04-16 15:00:00
...

I need a script that would run every day and delete all records belonging to deviceid "2" that is older than a year (from current date). In addition, it needs to delete records with other deviceids that are older than a month.

I would prefer to avoid cron jobs, because I'm concerned about security (I don't think storing MySQL password in a cron file is safe)

I'm a complete noob at SQL, I myself wouldn't know where to begin. I have phpMyAdmin if it's of relevance.


texts: SQL Server 2008 R2 Lockup for 10 minutes

texts:
Last Saturday night, our site went down for about 10 minutes. Analysis of the logs showed the following errors on the principal SQL Server (in a mirrored pair), during the outage and not outside the outage:

06/06/2015 23:14:41,spid[various],Unknown,Timeout occurred while waiting for latch: class 'ACCESS_METHODS_DATASET_PARENT' id [various] type 4 Task [various] : [various] waittime 300 flags 0x1a owning task [various]. Continuing to wait.
06/06/2015 23:06:54,spid19s,Unknown,Time-out occurred while waiting for buffer latch type 3 for page (5:159076157) database ID 6.
06/06/2015 23:06:54,spid19s,Unknown,Error: 845 Severity: 17 State: 1.
06/06/2015 23:06:54,spid19s,Unknown,A time-out occurred while waiting for buffer latch -- type 3 bp 0000000518FA1200 page 5:159076157 stat 0xc0000b database id: 6 allocation unit Id: 72057793340899328 task 0x0000000008EDA748 : 0 waittime 300 flags 0x100000001a owning task 0x0000000004472BC8. Not continuing to wait.

There were over a hundred of the first error mostly before but also after the others, which only occurred once. These errors occurred about 2 hours after doing a failover, applying OS updates, and failing back to the original server. We've been running on these servers for about 2 years now and have never seen this issue. The software calling into the servers was most recently updated on Thursday afternoon (about 55 hours before the outage).

I'm finding very little information here or through google about this timeout. The closest thing I've found is the second answer to this question: https://stackoverflow.com/questions/3149310/time-out-occurred-while-waiting-for-buffer-latch-type-2-error-in-sql-server, which talks about type 4 latch error being tempdb-related and caused by a bug in 2008, but that bug was resolved in 2009, before 2008 R2 was released. The exact version reports as:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

The servers both use mirrored Intel Enterprise SSDs for everything (dual RAID 10 arrays for the data), and none of the drives are reporting issues. The tempdb volume has 200GB free. There are 20 tempdb files (it's a BIG server). SQL traffic is down significantly from our peak over a year ago due to optimizations in the schema, stored procedures, and the software calling SQL, so it's probably not a load-related issue.

Is type 4 definitely related to the tempdb as many posts seem to indicate (database 6 referenced in the error message is our main database, not the tempdb)? What can I do to prevent this issue from happening again?


texts:
I'm studying for exam 70-462 and I'm practicing with some exercises I find around the web. In the site "Microsoft Questions and Answers" there's one solution I don't understand.

The solution's last step is to "Deny SELECT access to the SalaryDetails table to the Employees role." However, all company employees were added to this role and because "HR Administrators are also company employees," then HR Administrators should end up without any access to the SalaryDetails table, which is not what's being requested.

Is the solution wrong, or am I missing something?

Thanks in advance for any help!


texts: JOINing 3 tables with ORDER, LIMIT and WHERE

texts:
I am trying to join these 3 tables by key organization_id but I had no success.
What I'm trying to achieve: I know ipo and I want to get some values from name_entries and address_entries.
First query gives me organization_id, which is used in second and third query.
Is this even possible in 1 query?

My current solution is calling these queries separately and then joining them in my code.

SELECT * FROM "rpo"."organization_identifier_entries" WHERE ipo = '********' ORDER BY updated_at DESC LIMIT 1;
SELECT * FROM "rpo"."organization_address_entries" WHERE organization_id='########' ORDER BY updated_at DESC LIMIT 1;
SELECT * FROM "rpo"."organization_name_entries" WHERE organization_id='########'  ORDER BY effective_to DESC NULLS FIRST LIMIT 1;


texts: Pulling from Two Tables with Different Number of Records

texts:
I have three tables, one for members with 8 records, one for tasks with 1 record, and one for events with 3 records. The tasks table associates the events with members using one-to-many primary to foreign keys, creating a many-to-many relationship (so the columns are TaskID, MemberID, and EventID in the Tasks table).

I want to pull member data from the members table and the tasks data from the tasks table.  Instead of showing the one Task record with the member name in the adjacent column, MySQL is listing ALL 8 members adjactent to the same Task, but only one of the members is actually associated with that task.  This is what I have tried:

SELECT EventID, CONCAT(Title, " ", LastName) AS Member FROM Events, Members

How do I get MySQL to list only the tasks in the tasks table with the associated member CONCAT from the members table?


texts: Sql Server string concat with order by and subquery

texts:
While working on some dynamic sql production code we encountered code that's concatenating a bunch of strings and a subquery and then more string concatenation. The goal of the code is to populate a sql variable to eventually call EXEC(@sql). One thing we noticed was that despite our string concat and our COALESCE checks, only the last record was populating the variable.

The below code is a simplified version to reproduce the results.

IF OBJECT_ID('tempdb..#test') IS NOT NULL
BEGIN
  DROP TABLE #test
END

CREATE TABLE #test
(
    tmp_id INT IDENTITY(1,1),
    error VARCHAR(MAX),
    error2 VARCHAR(MAX),
    object_id INT
)

DECLARE @sql VARCHAR(MAX) = ''

INSERT INTO #test(error, error2, object_id)
SELECT TOP 100 CONVERT(VARCHAR(MAX), name), CONVERT(VARCHAR(MAX), name), object_id
FROM sys.columns

    SELECT @sql +=
      ' | '+(SELECT xC.name
        FROM sys.columns xC
        WHERE xC.name  = T.error
        AND xC.object_id = T.object_id) + ' | '
            + CAST(T.error2 AS VARCHAR(MAX))
FROM #test T
ORDER BY T.tmp_id

SELECT @sql

The strange thing is there seems to be a combination of factors at play here. If you remove the subquery sys.columns, or the order by, or the error2 column then it works as expected. You can also change the order by to be ordered by error instead of tmp_id and it will also still work.

I'm not looking for a solution as I've already rewritten the production code but I'm very interested in the "why" this is happening. I've tested on sql server 2008, 2016, 2017, and 2019 server.

Anyone have any ideas as to the cause?


texts: Should "Long Running with Low CPU" be expected for an SPROC that performs millions of INSERTS?

texts:
Brent Ozar's sp_BlitzCache flags the sproc below as the most "expensive" one in my system ("Frequent Execution, Long Running With Low CPU").  The sproc just checks a table for a row corresponding to a pair of input parameters which together define the table's PK.  If the row exists, the sproc does nothing; otherwise it INSERTS a row into the table and exits.  The table currently has about 5.3 million rows.

The table's columns and indexes look like this:

This sproc is called by an XML shredder app I've coded to shred millions of XML files.  For every XML file at least one row and sometimes several rows get inserted into the table.  So "Frequent Execution" makes sense, but I don't understand the "Long Running With Low CPU" part, and I have no idea what to do about it if it's a problem.

Is my sproc poorly designed?  If so, how can I improve it?

/****** Object:  StoredProcedure [dbo].[checkAdminStatusDocXref]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[checkAdminStatusDocXref] (
    @documentID         INT,
    @adminStatusID      INT,
    @adminStatusDate    INT)
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (SELECT 1 FROM tblAdminStatusDocXref
                WHERE documentID = @documentID
                AND adminStatusID = @adminStatusID)
    BEGIN
        RETURN;
    END
    ELSE
    BEGIN
        SET NOCOUNT ON;
        INSERT INTO tblAdminStatusDocXref (documentID, adminStatusID, adminStatusDate)
        VALUES(@documentID, @adminStatusID, @adminStatusDate)
    END
END;


texts: Find 13th min value in table group by a column

texts:
I am trying to replicate Small function in Excel which is Small(Array,K) gives Kth smallest element from stream of data.

I want to find 13th Min value for perticular Column values. in my table I have 2 columns A and B for each A column row value I have lets say 20 B values. I am trying to find 13th Smallest value for Each A value from column B

I am using following query to get min from group by clause

select A,min(B) from test group by Level_Name

I want to find 13th smallest value and not the smallest value.

I added tried a new query as

with a as(
select A,B,ROW_NUMBER() over (order by A,B) as rownum
 from test
group by Level_Name,pl
)
select A,B from a
where rownum=13
group by A,B

It is returning only 13th number for 1 of the A value but there are multiple rows in A.


texts: Error: There is a column named in table "*SELECT*", but it cannot be referenced from this part of the query

texts:
Assume we have a simple table,

CREATE TABLE foo ( a int PRIMARY KEY, b int );

I'm wanting to write a complex query inside and refer to that query

INSERT INTO foo
  SELECT 5 AS z
ON CONFLICT (a)
DO UPDATE SET b = z;

However when I execute that I'm getting an error like this,

ERROR:  column "z" does not exist
LINE 4:     DO UPDATE SET b = z;
                              ^

  HINT:  There is a column named "z" in table "SELECT", but it cannot be referenced from this part of the query.

Is there a way to make a structure like this work?


texts: Load sybase .db into new sybase developer server

texts:
We've got a few .db files laying around here which were created by Sybase (version 8.x, I guess) and I've the task to extract some data from those old databases.

Now I've installed the latest Sybase developer version (15.7) and want to add these databases to the sybase server.

Unfortunately I do not have any experience with sybase and I couldn't find any documentation in the official sybase manual for this task.

Is this even possible? Does anyone of you know about an article describing the integration of existing complete databases or the necessary commands for the task?


texts: How to find the price based on the given range

texts:
Here i need to find the price of the qty based on which price range the qty lies. In the first date(2015/01/01) 5 quantities have been sold and we made entries from (1 to 5) ,On second day for 10 quantities, the entry should be made from (6-15) and so on.

Example : Product A will be sold in the price of $ 100 per quantity between 1-3 (qty)

The 4th qty and more will be sold in the price of $ 110.

I've written the query, but is there any way to make it simple ?

# PRODUCT #

PROD    DATE         QTY
 A |   2015-01-01  |  5
 A |   2015-02-01  | 10
 A |   2015-03-01  |  5
 A |   2015-04-01  | 12

 # PRICE #

PROD |  SRANGE |  ERANGE |  PRICE
A    |    1    |  3      |   100
A    |    4    | 15      |   110
A    |   16    | 30      |   120

# RESULT #

PROD    DATE         QTY      PRICE
 A      2015-01-01   5     (300 +220) =520
 A      2015-02-01   10    1100
 A      2015-03-01   5     600
 A      2015-04-01   12    1500

Declare @Ddate as date
Set @Ddate = '2014-01-01'

select DISTINCT Top 1   case when
 (select COUNT (*) from PdtDet where ERange >=
 (select Top 1  Qty +1 from Pdt where Pdate <= DATEADD(d,-1,@Ddate) and Product = 'A' order by Qty  desc)
  and ERange<=T0.Qty and Product ='A') =0

then (select sum(Price) from PdtDet PD where PD.Product = 'A' and
 PD.SRange >= (select Top 1 case when (select COUNT(*) from Pdt where Pdate < @Ddate and Product = 'A')  >0 then
 (select Top 1  Qty +1 from Pdt where Pdate <= DATEADD(d,-1,@Ddate) and Product = 'A' order by Qty  desc) else 1 end) and PD.SRange<=t0.Qty)

 else
 (select Top 1 Price   from PdtDet PD where PD.Product = 'A' and
  PD.ERange >= (select  Qty +1 from Pdt where Pdate <= DATEADD(d,-1,@Ddate) and Product = 'A') and PD.ERange <= T0.Qty)
 end
   'Price'
     from Pdt T0 inner join PdtDet T1 ON T0.Product = T1.Product where t0.PDate = @Ddate and t1.Product = 'A'


texts: Would adding an INDEX on varchar column in Mysql increase performance?

texts:
I have the following table to store log messages:

CREATE TABLE `log` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `timestamp` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
 `logger` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `level` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
 `message` varchar(4000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `thread` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
 `file` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `line` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
 `machine` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10321 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

There will be lots of SELECTs with a WHERE machine = 'xyz'

Would making this column an INDEX improve performance on that?


texts:
I have an Oracle instance, with several schemas (all starting with the same prefix).

PATT_A
PATT_B
PATT_C
...

All of them have the same table, and I'd like to update all of them in one shot (with SYSTEM account).

I know I can get these schemas doing so :

SELECT username FROM dba_users WHERE username LIKE 'PATT_%' ;

And I would like to apply the same query on them, like this one :

UPDATE table1 SET field1 = 'new_value' WHERE field2 = 'filter' ;

I assume something like a loop could do the job, but I don't know how to use it here.


texts: Nested Loop with Materialize resulting in very slow query on postgresql 9.5

texts:
I have an analytics database on Postgres 9.5
I'm taking advantage of constraint partitioning with table inheritance to split up events into monthly tables. The base (parent) table contains no rows.

I have a relatively simple query but the planner is coming up with a completely insane plan where it materializes within a nested loop as can be seen from the explain.

The query

select count(person_id) as thecount from (
    select distinct A.person_id from event_page as O
    join alias as A on (O.person_alias = A.alias)
    where O.timestamp_ between '2017-4-28 04:00:00' and '2017-4-29 03:59:59.999'
    and O.location_host = 'www.foo.com' AND O.location_path= '/ca/sale'
) as alias_3494697

The explain: https://explain.depesz.com/s/IoO

My theory is that table inheritance with constraint partitioning is tripping up the planner somehow. If I replace the base table with the monthly table for April I get a sane plan with good performance:

https://explain.depesz.com/s/GpbU

NOTE The slight difference from the above query - i'm using the child table instead of the base table (ie no inheritance at play)

select count(person_id) as thecount from (
    select distinct A.person_id from event_page_2017_4 as O
    join alias as A on (O.person_alias = A.alias)
    where O.timestamp_ between '2017-4-28 04:00:00' and '2017-4-29 03:59:59.999'
    and O.location_host = 'www.foo.com' AND O.location_path= '/ca/sale'
) as alias_3494697

All stats are up to date via vacuum analyze. The relevant schema is below:

CREATE TABLE event_page (
    id                      CHAR(24) PRIMARY KEY,
    timestamp_              TIMESTAMP NOT NULL,
    person_alias            VARCHAR(128) NOT NULL,
    visitor_id              VARCHAR(128) NOT NULL,
    session_id              VARCHAR(24) NOT NULL,
    ip_address              VARCHAR(64) ,
    user_agent              VARCHAR(256) ,
    operating_system        VARCHAR(64) ,
    device_type             VARCHAR(64) ,
    browser                 VARCHAR(64) ,
    browser_major           VARCHAR(64) ,
    page_title              VARCHAR(1024) ,
    location_host           VARCHAR(256) ,
    location_path           VARCHAR(1024) ,
    location_query          VARCHAR(1024) ,
    location_fragment       VARCHAR(1024) ,
    referrer_host           VARCHAR(256) ,
    referrer_path           VARCHAR(1024) ,
    referrer_query          VARCHAR(1024) ,
    referrer_fragment       VARCHAR(1024) ,
        duration                INT
);

--the monthly tables all look like this example for April 2017
CREATE TABLE event_page_2017_4
(LIKE event_page including defaults including constraints including indexes,
CONSTRAINT page_2017_4_part CHECK ( timestamp_ >= '2017-4-01'::timestamp AND timestamp_ < '2017-05-01'::timestamp )
)
INHERITS (event_page);

--indexes are the sames on each of the monthly tables
CREATE INDEX ep_2017_4_location
  ON event_page_2017_4
  USING btree
  (location_host varchar_pattern_ops, location_path varchar_pattern_ops, location_query varchar_pattern_ops, location_fragment varchar_pattern_ops, timestamp_, person_alias, session_id);

CREATE INDEX ep_2017_4_ts
  ON event_page_2017_4
  USING btree
  (timestamp_, person_alias, session_id);

CREATE INDEX ep_2017_4_a_ses_ts
  ON event_page_2017_4
  USING btree
  (person_alias, session_id, timestamp_);

--the alias tables and two indexes
CREATE TABLE alias (
    person_id               CHAR(24),
    alias                   VARCHAR(128) NOT NULL,
    first_seen              TIMESTAMP,
    soft                    BOOLEAN
);

ALTER TABLE ALIAS ADD CONSTRAINT alias_alias_pkey PRIMARY KEY(alias);
CREATE INDEX alias_a_p ON alias (alias, person_id);
CREATE INDEX alias_p_a ON alias (person_id, alias);

NOTE: The problem went away after upgrading to 9.6.1


texts:
I followed a tutorial to update My MySql 5.1 to 5.5 (actually i wanted to upgrade to 5.6, but the tutorial mentioned that i should upgrade to 5.5 first and then to 5.6).

However, after removing previous version and updating mysql configuration and path environment variable, i'm stuck at starting the server and upgrading. I'm a newbie i this field and i don't know what to do now.

These are the steps i followed:

Stopped mysql service, Copied mysql data files and configuration files.
Removed version 5.1.
Downloaded 5.5 and installed it.
Updated start-up script to register the new installation.
Updated Path Environment Variable.
Updated MySql Configuration.
Made appropriate changes to path to mysql plugins directory.
Creates mysqld symbolic link in /usr/sbin/ directory.

Now the final step was to start the server and upgrade it. So i tried sudo /usr/sbin/mysqld --skip-grant-tables, but it says sudo: /usr/sbin/mysqld: command not found.

i don't know what to do now. I'm stuck at this from the last 4 hours. I was following this tutorial.


texts: Create new database with large initial size

texts:
I want to create a new database with a 200GB data file (.mdf) and 50GB log file (.ldf).

But it is very slow (it's about 15 minutes and still hasn't created). and it is very time consuming. Is that normal? if yes what does it do that takes time? can I enhance its creating speed?

I am using SQL Server 2008 R2, Windows Server 2008R2, 16GB RAM (which I limited that to 12GB in ssms) and Core i7 Cpu


texts: What is the performance impact of indexing a frequently updated column?

texts:
I just want to know if I'm doing this right. I've searched google for hours already and I can't find a straight answer.

I have a table with potentially a very large amount of inserts and updates. Any time an update is made to a row, the 'modified' column gets changed. My application needs to query this table (along with some joins and filtering), and get paginated results sorted by that 'modified' column. Everything I've read tells me that I need to index the column that is used for sorting. But I've also read many articles telling me that indexing a column that gets updated frequently is a bad idea, because the index needs to be rebuilt every time the value of an indexed column changes.

So what is the proper way to go about doing this? If I index the 'modified' column, will this lead to problems later on when the index becomes very large and it needs to constantly rebuild itself over and over again? If I don't index the 'modified' column, will my pagination queries eventually become so slow that my application becomes unusable? Or is there another solution I haven't thought of?

-EDIT- I am leaning towards using an index on the column. But what I'm still not sure about is what affect a large number of UPDATEs to the indexed column will have on the SELECT queries that use that index. I know that indexes slow down UPDATEs and INSERTs, but I'm not sure if that increased I/O workload from rebuilding the index so many times (EVERY time a row gets updated, which there would probably be dozens of at any given moment) would be a major bottleneck to the server's performance in general. Or is it not as big of a bottleneck as I'm thinking?


texts: How to dump multiple databases of the same cluster in single execution?

texts:
I have 100 databases in my cluster. I need to backup 30 databases (as single file or multiple file, anything is fine). I could do it for tables using pg_dump with -t.

Is there any way to do something like this (pseudocode):

pg_dump -d db1 -d db2 -d db3 -Fc > dbs_backup.backup


texts: Insert up to 10 missing products into a related product discounts table

texts:
I have two tables: products and products_discounts. If a product is designated as a "Top 10" product, and it is missing a defined discount in products_discounts, I want to insert a record into products_discounts for each missing record. The inserted values would consist of the product_id and two other hard-coded values in the INSERT INTO.

  Products
      id, product_id, name, top_ten

  Products_Discounts
  id, product_id, discount_amount, discount_description

The following is how I plan to do this manually, but since I'll be repeating this process a few times a year, I'm looking to see if this can be done in a few lines of SQL.

(1) Get the list of products that meet my criteria.

SELECT p.product_id, pd.discount_amount FROM products AS p
LEFT OUTER JOIN products_discounts AS pd
    ON p.product_id = pd.product_Id
WHERE p.top_ten = 1
    AND pd.product_id IS NULL

(2) Use a text editor to construct each individual INSERT INTO statement. I'd use the output of step 1 to get the product_ids but the other two values would remain the same (hard-coded).

INSERT INTO products_discounts (product_id, discount_amount, discount_description)
VALUES ('<product_id goes here>', 10, 'Top 10 Product')

Can these steps be combined into a few lines of SQL?


texts: Trigger in trigger or trigger before table creation

texts:
I need to create trigger inside trigger as follows:

SQL> CREATE OR REPLACE TRIGGER table_account_modify AFTER CREATE ON SCHEMA
  2  DECLARE
  3  NAZWA_TABELI varchar(250);
  4  sq0 VARCHAR2 (30000);
  5
  6
  7  BEGIN
  8  select ora_dict_obj_name into NAZWA_TABELI from DUAL;
  9
 10  sq0:= 'CREATE OR REPLACE TRIGGER new_trigger2; BEFORE INSERT on ACCOUNT FOR EACH ROW; BEGIN INSERT INTO pstepien VALUES (:NEW.DN,:NEW.CONTAINERDN,:NEW.SUPERVISOR,:NEW.OWNERDN,:NEW.SELFDN,:NEW.TARGETCLASS,:NEW.ERACCOUNTCOMPLIANCE,:NEW.ERACCOUNTSTATUS,:NEW.ERLASTACCESSDATE,:NEW.ERPARENT,:NEW.ERSERVICE,:NEW.ERUID); END;';
 11
 12
 13  IF NAZWA_TABELI = 'ACCOUNT' THEN
 14  execute immediate sq0;
 15  END IF;
 16  END table_account_modify;
 17  /

Trigger created.

SQL> create table ACCOUNT (
2    DN VARCHAR2(255),
3    CONTAINERDN VARCHAR2(255),
4    SUPERVISOR VARCHAR2(255),
5    OWNERDN VARCHAR2(255),
6    SELFDN VARCHAR2(255),
7    TARGETCLASS VARCHAR2(255),
8    ERACCOUNTCOMPLIANCE VARCHAR2(255),
9    ERACCOUNTSTATUS VARCHAR2(255),
10    ERLASTACCESSDATE VARCHAR2(255),
11    ERPARENT VARCHAR2(255),
12    ERSERVICE VARCHAR2(255),
13    ERUID VARCHAR2(255)
14  );
create table ACCOUNT (
*
ERROR at line 1:
 ORA-00604: error occurred at recursive SQL level 1
ORA-00911: invalid character
ORA-06512: at line 13

SQL>

After I created the trigger, I created a table to see if it works and I got that error.
Could you please advice me what the problem could be and how to proceed here?


texts: How to find the specific file in a filegroup in which an object resides

texts:
Today, I encountered below error on one my SQL Server which I manage.

  Could not allocate space for object '%.*ls'%.*ls in database '%.*ls'
  because the '%.*ls' filegroup is full. Create disk space by deleting
  unneeded files, dropping objects in the filegroup, adding additional
  files to the filegroup, or setting autogrowth on for existing files in
  the filegroup.

It occurred during Index Reorg operation. Upon investigation, I found that my DB was split in 3 filegroups and had multiple files in them. All of them had at least one file with auto-growth enabled and disks hosting them had enough free space available. So then why my index maintenance failed? My research led me to this MS article https://msdn.microsoft.com/en-us/library/aa337441.aspx which says:

  When an index is located on several files, ALTER INDEX REORGANIZE can
  return error 1105 when one of the files is full. The reorganization
  process is blocked when the process tries to move rows to the full
  file. To work around this limitation perform an ALTER INDEX REBUILD
  instead of ALTER INDEX REORGANIZE or increase the file growth limit of
  any files that are full.

Ok. So the solution is to rebuild the index so it moves to the new file. But what if I want to continue having my index in same file so that my reorg can continue? Is that possible? Also, is it possible to find out in which file my DB object resides? Most of the blogs have scripts to find objects residing in filegroup but I want to find files in that filegroup so I can manually grow them and see if that fixes it.


texts: How is it possible we have rows in the database that violate a unique constraint?

texts:
Recently We meet a mysql error on slave, because a transaction can not be executed on slave.

We found two records have the same unique in master, but inserted successfully.

I have post two screenshot of my situation, Could you give me any clues about how can this be happened?


texts: I'm Making an application, i have 1 table in 1 device using Mysql, and i already connect to another device Using SQL SERVER with ODBC,  That table from Mysql is doing update every 2-3 seconds, i wanna save that data on SQL SERVER, how can i do that? first the table from mysql need to duplicate on a  new table create on SQL SERVER, after that they both change data at the same time (i hope) i would like to make like a "historian report".


texts: sqlplus: ORA-01017 logon denied from one host but OK from another

texts:
I have a strange behavior for remote sqlplus login to Oracle 11.2 that I cannot explain:

1) I have an Oracle 11.2 server running on a virtual host, say host1
2) I set up another VM, say host2 (CentOS 6.5 x86_64) with oracle-instantclient 11.2.0.4 64bit, built up from a minimal install ISO
3) I also downloaded a CentOS 6.5 x86_64 VM image from the net (a desktop install with everything already installed), say host3, on which I installed the same Oracle instantclient

I use the same environment and tnsnames.ora file on both host2 and host3, something like:

MONSTER=
(DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))
    (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CRM)))

Issue:
On host2, I cannot get rid of the ORA-01017 error:

host2> sqlplus sys/<passwd>@monster as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 1 12:09:51 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

BUT, on host3, everything is OK:

host3> sqlplus sys/<passwd>@monster as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 1 06:21:59 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release ...
SQL>

Has someone an idea why connecting from one host is OK while connecting from the other one is KO?

NB: Needless to say this is not an issue of case sensitivity, or bad orapwd, or remote_login_passwordfile not set to EXCLUSIVE, since it works from host3.

Thanks


texts:
I've got a query like this:

SELECT count(*)
FROM acs2014_5yr.geoheader as g
JOIN tractcache as c ON (c.tract_id = g.state||g.county||g.tract)
JOIN tiger2014.tract AS t ON (t.geoid = c.tract_id)

and I'd like to add this filter to it:WHERE g.blkgrp IS NULL AND c.metro_id = '348'.  However, the query (without the WHERE clause) takes about 1.3 seconds.  When I add the WHERE clause, the query takes more than 3 minutes (too slow for production).  This doesn't make any sense to me, since it's just a filter on rows already being iterated through in the query.

Also, if I take either half of the WHERE query out (so it's either WHERE g.blkgrp IS NULL or WHERE c.metro_id = '348') the query remains extremely performant (about 1.5 seconds).  What's up with this behavior?  I've been looking at this for more than a day and still don't have a clue as to what's killing performance here.

A couple notes:

There are no indices on either g.blkgrp or c.metro_id.  This shouldn't matter though because there are only a couple thousand (< 4,000) rows in c, and each half of the WHERE clause is performant enough on its own.
Again, there are only a couple thousand rows in c, so even if this was causing an additional full table scan, performance shouldn't be hurting that much.
If you're having any trouble visualizing what I'm trying to do here, acs2014_5yr.geoheader and tiger2014.tract have a one-to-one relationship through tractcache.

Again, I can't figure this out for the life of me.  I sincerely appreciate any help I get.

Output of EXPLAIN for the performant query (no WHERE clause at all):

Hash Join  (cost=68995.64..71160.78 rows=1643543 width=24)
  Hash Cond: ((c.tract_id)::text = (t.geoid)::text)
  ->  Seq Scan on tractcache c  (cost=0.00..42.68 rows=2068 width=56)
  ->  Hash  (cost=64990.76..64990.76 rows=158950 width=76)
        ->  Hash Join  (cost=24259.82..64990.76 rows=158950 width=76)
              Hash Cond: ((((g.state)::text || (g.county)::text) || (g.tract)::text) = (t.geoid)::text)
              ->  Seq Scan on geoheader g  (cost=0.00..33379.50 rows=158950 width=64)
              ->  Hash  (cost=23333.81..23333.81 rows=74081 width=12)
                    ->  Seq Scan on tract t  (cost=0.00..23333.81 rows=74081 width=12)

Output of EXPLAIN for the poorly performing query (with WHERE g.blkgrp IS NULL AND c.metro_id = '348'):

Nested Loop  (cost=47.98..57198.21 rows=40 width=24)
  Join Filter: ((c.tract_id)::text = (((g.state)::text || (g.county)::text) || (g.tract)::text))
  ->  Seq Scan on geoheader g  (cost=0.00..33379.50 rows=795 width=64)
        Filter: (blkgrp IS NULL)
  ->  Materialize  (cost=47.98..23659.74 rows=10 width=68)
        ->  Hash Join  (cost=47.98..23659.69 rows=10 width=68)
           Hash Cond: ((t.geoid)::text = (c.tract_id)::text)
           ->  Seq Scan on tract t  (cost=0.00..23333.81 rows=74081 width=12)
           ->  Hash  (cost=47.85..47.85 rows=10 width=56)
                 ->  Seq Scan on tractcache c  (cost=0.00..47.85 rows=10 width=56)
                       Filter: ((metro_id)::text = '348'::text)

UPDATE: Apparently this has to do with database configuration, it runs fine locally (in a docker container).  The database is managed by RDS.


texts: Catching two error messages/Throw into table

texts:
My goal is to catch errors from backup jobs into monitoring table.
The problem is, that there are cases that backup statement returns more than one error, so ERROR_MESSAGE() is not enough:

  Msg 3201, Level 16, State 1, Line 1 Cannot open backup device '...'
  Operating system error 3(The system cannot find the path specified.).

  Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating
  abnormally.

I can use throw to catch both messages, but is there an easy way to insert throw output into table or overcome this problem in another way?


texts: How can I rewrite for SQL Server a trigger that writing for Oracle?

texts:
How can I rewrite for SQL Server a trigger that was originally written for Oracle ?

Here is my Oracle trigger code:

CREATE OR REPLACE TRIGGER P000KUL_TEST
BEFORE
INSERT
ON P000KUL
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
SELECT TO_CHAR(SYSDATE, 'dd/mm/RRRR') INTO :NEW.SYSTEM_DATE FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'hh:mi') INTO :NEW.SYSTEM_TIME FROM DUAL;
END;


texts: Temporarily disable SQL Server Mirroring

texts:
We've got a migration going on where a SQL Server 2012 mirroring configuration running in high safety with auto failover (primary/mirror/witness) is being completely shut down and restarted to move hardware.

Shutdown is being done by automation so the order of shutdown isn't going to be tightly controlled.  We would like to prevent role switching during the shutdown/restart, so we need to "remove" the witness.  Seems like this could be done either via explicitly removing the witness via TSQL on each mirrored database (there are about 5), then re-adding after everything restarts/resyncs or by just disabling the SQL Server service on the witness.  The second option seems like less fiddling around, but most of the references I see say to remove the mirror from each session, then go back and re-add after.

Is there a reason not to just stop and disable the witness SQL server service until the maintenance is complete?  Without the witness there will be no failover and all it takes is restarting the service to recover.

We are also planning on disabling tlog backups on the primary just before shutdown to make sure the mirror can resync automatically after restart.


texts: New login is created but 'sa' cant assign grant/deny permissions to user in SQL Server 2008 R2?

texts:
I created users with login option in security tab in SQL Server 2008 R2 running on windows 7 successfully and grant permissions to :

Use dummydb
Grant REFERENCES  ON OBJECT::vuUser TO nvp
GO

Use dummydb
Deny INSERT,SELECT,UPDATE,DELETE ON OBJECT::dummyDB.userTb to nvp
GO

nvp user can login successfully and restriction work great but when I create user and grant permissions as above on SQL Server 2008 R2 running on windows server 2008.

User can login successfully but when I try to grant permission using above code, I get this message:

  Msg 15151, Level 16, State 1, Line 2
  Cannot find the object 'usertb', because it does not exist or you do not have permission.

Is their is something I am doing wrong or I need to try something else ?????

Database and table name are correct...

I login in using user 'sa' credentials

Need urgent help n thanks in advance


texts: SQL Server 2012 not using allocated memory

texts:
I have an issue with a SQL Server 2012 SP2 std server on a windows 2012 64-bit server

Even though I have assigned SQL Server 12GB of RAM - it won't use more than ~700MB
I've simulated memory load under windows and the VM uses the RAM as it should, but if I simulate load under SQL Server it never goes above ~700MB.

I'm using this to report SQL Server memory usage:

SELECT
(physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB
FROM sys.dm_os_process_memory;

The DB's have been moved to this server from SQL Server 2008 which never had this issue.

The app hasn't changed - apart from running really slowly now :/

Any suggestions please?

Thanks


texts: How do I enable both PowerShell Remoting and SPN for SQL Server Reporting?

texts:
I'm struggling with remoting servers with SQL Server Reporting Services running. My Reporting Services are running with individual domain-accounts, and I have set up SPNs for them (HTTP/<Machine> <domain>\<user>). As far as I can see, this effectively disables using PowerShell remoting, since the SPN which WinRM should use points to the domain account used by Reporting Services.

I have no problem running e.g. Get-Service -ComputerName <Machine>, but if I try Get-CimInstance Win32_Service -ComputerName <machine> or Enter-PsSession <machine> I get an error similar to this:

  Get-CimInstance : WinRM cannot process the request. The following
  error with errorcode 0x80090322 occurred while using Kerberos
  authentication: An unknown security error occurred. Possible causes
  are:
  -The user name or password specified are invalid.
  -Kerberos is used when no authentication method and no user name are specified.
  -Kerberos accepts domain user names, but not local user names.
  -The Service Principal Name (SPN) for the remote computer name and port does not exist.
  -The client and remote computers are in different domains and there is no trust between the two domains.

  After checking for the above issues, try the following:
  -Check the Event Viewer for events related to authentication.
  -Change the authentication method; add the destination computer to the WinRM TrustedHosts configuration setting or use HTTPS transport.

  Note that computers in the TrustedHosts list might not be
  authenticated.
  -For more information about WinRM configuration, run the following command: winrm help config.  At line:1 char:1    + Get-CimInstance
  win32_service -ComputerName <machine>    +
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~    +
  CategoryInfo: AuthenticationError: (root\cimv2:win32_service:String)
  [Get-CimInstance], CimException    + FullyQualifiedErrorId : HRESULT
  0x8033809d,Microsoft.Management.Infrastructure.CimCmdlets.GetCimInstanceCommand
  + PSComputerName        : <machine>

If I delete the SPN on one of my servers, then after a few seconds (a bit fast for AD replication?) I can use the above commands, but If I then reset the SPN the commands fail again after a while.

Some of my Reporting Services need to be able to forward credentials, so I hope someone is able to help me solve this dilemma.


texts: I have two MySQL servers (A and B) running on separate machines.  I want to replicate database X on A to B.  B is still master for other databases.  How do I set this up so that B is only slaved for X but master for the others?  I currently have A configured to only replicate-do-dbs=x.


texts: TSQL Re-Enabling Contraints take a long time

texts:
Is there any way I can speed up the re-enabling of constraints?

As part of a job to update several tables in our Warehouse, which is being done out of hours, the re-enabling of constraints on said tables can take over an hour.  The rest of the job, the updates, take about 5 mins. Disabling the same constraints before the updates begin is sub second.

EDIT
The job in question is to update the PKs and FKs on rows of data before a given date so to prevent those records appearing in reports. A way to soft delete records` if you like.

Most of the table row sizes are >130m rows each except for one which is over 370m rows. The other two are smaller.

TableName   rows
Table_1     371255778
Table_2     131703902
Table_3     131665535
Table_4     131665535
Table_5     131665535
Table_6     131665535
Table_7     19364988
Table_8     1458800

I am using the following the command to disable the constraints on each table

ALTER TABLE database.dbo.table_name        NOCHECK CONSTRAINT ALL;

And the following to re-enable them again

ALTER TABLE database.dbo.table_name        WITH CHECK CHECK CONSTRAINT ALL;

there are no reports or other jobs running at the same time.

Is taking so long to re-enable constraints expected behaviour or is there something that can be done to speed it up?

Thanks in advance


texts: Access Linked Tables and Their Network Connections

texts:
I need to know some simplistic details about how Microsoft Access' linked tables work. I think I know the answer, and this question is more about asking for a confirmation than anything else. A pointer to documentation would be appreciated, as well.

I am testing an Access ancillary application that pulls its data from a crime statistics Informix IDS database. The Access database contains several linked tables to the Informix database, and, using a multi-table query, updates a native Access table with information.

I am assuming if the network link to the Informix database breaks even for an instant, the linked tables' links also break, and the application must be reloaded. Is that assumption correct? I saw this happen when I let the application run overnight. The links were broken, and I believe the network link burped overnight.

The reason I'm asking this, is I have to build in a re-load mechanism into the Access form, so that the user does not have to close the application and re-open it.


texts:
I'm planing to do a database backup and restore by using SQL Job. My database size is to large and I want to backup and restore my schema only.

By using this code I can set up SQL Job. Can anyone help me to backup and restore my schema only?

BACKUP DATABASE [pubs]
TO DISK = N'c:\temp\backups\pubs_full.bak'
WITH INIT, SKIP, CHECKSUM
GO

RESTORE DATABASE pubs_RestoreTest
FROM DISK = N'c:\temp\backups\pubs_full.bak'
WITH MOVE 'pubs' TO 'c:\temp\backups\pubs_RestoreTest.mdf',
MOVE 'pubs_log' TO 'C:\temp\backups\pubs_RestoreTest_log.ldf'


texts: What can I add to a server to make SQL restores faster?

texts:
I have a 2.8TB SQL database (mostly data files, some 400GB of log files) that currently takes around 9 hours to restore. This database is used for testing purposes and must be deleted and restored from a backup between each run, to make sure we're always starting from the same point.

My question is, the server currently has 12 cores and 92GB of RAM, with a RAID 5 disk subsystem that the database is on. What areas usually cause bottlenecks for SQL restore processes? Is it the disk, memory, or CPU?


texts:
My understanding of a full backup is (based on https://technet.microsoft.com/en-us/library/2009.07.sqlbackup.aspx - most of the below is copied, from this link):

Force a database checkpoint and make a note of the log sequence number at this point. This flushes all updated-in-memory pages to disk before anything is read by the backup to help minimize the amount of work the recovery part of restore has to do.
Start reading from the data files in the database.
Stop reading from the data files and make a note of the log sequence number of the start of the oldest active transaction at that point
Read as much transaction log as is necessary.

Backing up enough of the transaction log is required so that recovery can successfully run during the restore and so that all pages in the database are at the same point in time—the time at which the data reading portion of the backup operation completed. The transaction log from the beginning of the oldest active (or uncommitted) transaction to the end of the backup, is required to allow recovery to run. The transaction log back from the backup checkpoint to the end of the backup is required to allow all pages to be brought to the same point in time and for transactional consistency.

The minimum log sequence number (LSN) of the transaction log that is included in the full backup is MIN (LSN of backup checkpoint, LSN of oldest active transaction) and it could be for a transaction that began even before the backup began. This ensures that the restored copy of the database (or whatever you restored—page, file, filegroup, database) is completely consistent.

Questions when I read This book from Microsoft:

The book from Microsoft suggests that MIN LSN - "Determines the minimum LSN required for the log backup by taking the earliest in time between the backup end LSN and the oldest active transaction’s LSN." Is this incorrect and should it have been "start" instead of end?
Same page, the para above that on the book says that the back-up end LSN is - "LSN of the start of the oldest active transaction" - but should'nt it be end of the backup?
Same page, notice also that the image is incorrect - see Transactions A and B start twice - I think the 2nd one should be transaction end instead of start?

Bit confused.


texts: Would it make security sense to split up tables into different databases and or servers?

texts:
What I am asking is would it be more secure if I had a customer table in one database server and the transactional data in another database server? This way if the database is compromised they are only getting one piece of the pie? What kind of service hit would this take on the application?

Just a thought on design.


texts: Grouping date's as 1 count if difference is 5 days

texts:
I am trying to think of a way of grouping some dates to make a count if they are more then 5 days apart.

For Example:

ID - DATE
22   15/05/2016 08:57
22   15/05/2016 10:50
22   15/05/2016 12:01
22   16/05/2016 01:12
22   22/05/2016 02:25

So for this I would currently get a count of 5 as there are 5 occurrences however I want to only get a count for 2 due to the fact that the first 4 have sequential dates and the final one is 5 days after the previous date so would need that counted as a new one.

Hopefully that makes sense!


texts: Azure SQL Database SQL71562 error exporting to blob storage

texts:
I am attempting to export an Azure SQL Database on an elastic pool to blob storage via the portal Export selection from the target database.
This errors with:
One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71562: Error validating element (and a partial listing of objects follows).
When moving from on-premises to Azure there were things that had to been done regarding database tables and stored procedures to ensure a successful bacpac creation.  It appears that is the case even with Azure SQL Databases but not finding anything that documents this (not sure why unsupported objects could be created within SQL Database).  Anyone else experience this?


texts: 'SELECT my_table.id' slower than 'SELECT my_table.*'

texts:
Running MySQL with InnoDB:

I have a SELECT wide_table.* query that I want to refine to SELECT wide_table.id since that's all the calling code needs. Testing it out I've found that the execution time with * is faster than with id (though the time to transfer the result over the network is faster with the "refined" version).

Why would this be the case?

The query (with names changed to protect the innocent) is:

SELECT
    `things`.*
FROM
    `things`
WHERE
    `things`.`active` = 1
        AND (owner_id IS NOT NULL
        AND owner_id > 0)
        AND ((`things`.`status` IN (0 , 1)
        OR `things`.`status` IS NULL))
        AND (date < '2015-07-11 00:00:00');

There's a compound index on active and date, which is being used in both versions.

For reference, the ouput of SHOW CREATE TABLE (with irrelevant columns omitted):

CREATE TABLE `things` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) DEFAULT '0',
  `date` datetime DEFAULT NULL,
  `owner_id` int(11) DEFAULT '0',
  `status` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_things_on_active_and_date` (`active`,`date`),
  KEY `index_things_on_date` (`date`),
  KEY `index_things_on_owner_id` (`owner_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1862 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Limits

After playing around with it for a while, I've found that the comparison is affected by the limit imposed on the query. With large limits, >200, the * version reports a faster execution time. As the limit is decreased <200, the id version gains the upper-hand. Still not sure what to make of this...

EXPLAIN

Running EXPLAIN on both versions of the query yields identical output, with select_type: SIMPLE and key: index_things_on_active_and_date.


texts: How to filter a JOIN on *all* items of a given list (filter articles with all given tags for example)

texts:
I have this use case which seems quite usual but I don't find any efficient and obvious solution.

I have an article table, a tag table and a intermediate article_tag table (each article can have multiple tags, and each tag can be linked to multiple articles).

Given a list of tag ids 1, 2, 3, I want to retrieve articles that have all these tags at the same time.

I easily managed to retrieve articles that have at least one tag in this list:

SELECT * FROM article a
JOIN article_tag at ON at.article_id = a.id AND at.tag_id IN (1, 2, 3)

This query can return articles with only 1 tag (id 2 for example). But this is not what I want. I want only the articles with the 3 exact tags 1, 2 and 3.

How to do it in an efficient way? There are quite a lot of articles, I do care about performances.

Thanks!


texts:
I'm attempting to upgrade my local instance of postgres to 10.1 (from 9.6.6) on macOS.  Using this gist as a guide, I've encountered a problem when running pg_upgrade.  Here are the steps taken:

$ mv /usr/local/var/postgres /usr/local/var/postgres.old

$ mkdir /usr/local/share/postgresql.old
$ cp -r /usr/local/share/postgresql/timezone /usr/local/share/postgresql.old
$ cp -r /usr/local/share/postgresql/timezonesets /usr/local/share/postgresql.old

$ initdb /usr/local/var/postgres -E utf8

$ pg_upgrade \
  -d /usr/local/var/postgres.old/ \
  -D /usr/local/var/postgres/ \
  -b /usr/local/Cellar/postgresql@9.6/9.6.6/bin \
  -B /usr/local/Cellar/postgresql/10.1/bin/

Performing Consistency Checks
-----------------------------
Checking cluster versions
Old cluster data and binary directories are from different major versions.
Failure, exiting

Prior to this attempted upgrade, 9.6.6 was running perfectly with /usr/local/var/postgres as its data directory.  Why does pg_upgrade think the old data does not match the old binaries?

Edit: Here's the output requested by @jjanes:

$ /usr/local/Cellar/postgresql@9.6/9.6.6/bin/pg_ctl start -D /usr/local/var/postgres.old
server starting
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.5, which is not compatible with this version 9.6.6.

I had assumed 9.* was considered the same "major version", as per semver.  Apparently not; upgrading from 9.5.10 worked perfectly.


texts: What does GRANT CONTROL on a schema do in MSSQL?

texts:
I'm using SQL Server 2016 for these tests.

The following did not allow me to create a table in schema S for user U

USE [D];
GRANT CONTROL ON SCHEMA :: [S] TO [U];

But this did:

USE [D]
GRANT ALTER ON SCHEMA :: [S] TO [U];
GRANT CREATE TABLE TO [U];

What is the GRANT CONTROL supposed to do, if you have to run these two additional commands to allow the user to create a table in a specific schema?

This description is nice, but what kind of a use case can I run that can confirm (with success or failure) the GRANT CONTROL command physically affected the user's security on the database with and without this schema, before and after running the command?

https://blogs.msdn.microsoft.com/lcris/2009/08/11/basic-sql-server-security-concepts-ownership-control-take-ownership/

  CONTROL permission: The CONTROL permission can be used to easily grant
  all permissions on an entity to some principal. It's the next best
  thing after ownership of the entity, but it's not quite as powerful as
  ownership. The main difference is that a grantee of CONTROL can still
  be denied some other permissions on the entity. For example, I can be
  granted CONTROL on a table, while at the same time I can be denied
  SELECT on that table, preventing me from selecting from it - this can
  never happen to the owner, because the owner cannot be granted or
  denied permissions.


texts: Why does SQL Server Backupset table goes back a few months, but Agent Logs go back further?

texts:
I ran this script looking at the history of my backups for the past month.

select * from msdb.dbo.backupset order by backup_start_date

No problem, but if I want to go back to Jan 2020, there are no records.

So why does the Agent Log, with Log Type = 'DATABASE' go back further?

Thanks


texts: Get minimum value row based on column values


texts: Microsoft SQL Server - Permission to create a Snapshot

texts:
I've developed a set of SSIS packages to load a data warehouse.
In order to manage any issue that may arise during package execution, each package starts by creating a snapshot of the data warehouse.

The package authenticates on my server using a login that does not belongs to the dbcreator server role.
Obviuosly with this configuration the package fails due to the lack of permission to create/drop a database.

The dbcreator server role allows a user to create/drop a database on the whole instance.
My question is: Is there a way to grant a database user the permission to create a snapshot of the given database (and only that) and eventually drop the snapshot (and only the snapshot associated to the given database)?

What are the best practices to handle this requirement?


texts: What are the differences between CREATE INDEX and CREATE STATISTICS and when should I use each?


texts: SQL Server Full-text search for keywords without prefix matching

texts:
I have the following text in my NVARCHAR column

  "Ich bin eine SuperbProdukt"

I have a full-text search enabled on the column and I can search for it by executing the following query that I wrote :

SELECT FROM MY_TABLE WHERE CONTAINS(Title, ' "Ich*" ')

The query gives me all the result that contains my search criteria, I can also search for other words within the whole text such as "Superb" and get the same record back. However, I am trying to have a search functionality that returns results even when I give keywords that are not the beginning of any words in a phrase, but rather it's in between. For instance, I am expecting to get the same record back when searching for "Produkt"

Is there a way to achieve this in SQL server without using LIKE operation? I heard that CharIndex can do the job, but I have no clue how that can be helpful in my scenario.

P.S: I am using SQL SERVER 2012 on Azure.


texts: Which is better schema for given discussion forum, MongoDB

texts:
Lets have two variants of nosql collection for storing discussion comments.
Assume discussion, where is only one thread/root comment with 100 replies.

A./

collection: discussion_thread (id, discussion_id, comments: Comment[])

comments is recursive tree structure (Comment = {content: string, comments: [...recursion...]})
-For given discussion example, there is only one document in discussion_thread, that holds all comments.

B./

collection: comment (id, discussion_id, root_comment_id, parent_comment_id, content) -- I know there could be slug

For given discussion example, there will be 101 documents in comment collection

Lets assume:

the database has 500GB and is growing, collections are sharded by discussion_id with master/slave replication
we have 1000 request /minute, 90% reads
on each write (e.g. upvote of comment) we need to rebuild the cache for whole thread, for fast read/serving

To be reasonably brief, in this question I would like to focus only on cache rebuilding. So we need to read all the comments for given thread / root_comment.

In A./ we need one lookup to get 1 document with all comments.
But this structure is quite problematic to write into(at least because potentially all concurent writes to single thread ends up on one document).

In B./ to get all of the 101 comments of the example thread, mongo needs to find and return 101 documents.
Because there is index on discussion_id and root_comment_id and sharding on discussion_id, do you think, that the B variant will be significantly slower then the A?

Do You think there is some other major disadvantage in one of the variants?
Thanks.


texts: How to set the max characters per column field to specify the maximum length of data displayed in a single column in pgAdmin 4

texts:
In my table I have a column of type "json". This json data is up to 10MB big. It takes a lot of time to render such an amount of data in PgAdmin 4. Even if my table consists just of one row the rendering process is between 5 and 10 seconds.

In PgAdmin 3 there is a possibility to set the "Max characters per column" https://www.pgadmin.org/docs/pgadmin3/1.22/options-query_tool.html

But where to define that in PgAdmin 4?


texts: best strategy to update from invalid date formats vs allow_invalid_dates

texts:
Here is the create table syntax from a legacy table:

CREATE TABLE `bais_logs` (
`lg_id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT,
`lg_machines_id` int(11) unsigned NOT NULL COMMENT 'unique machine id from which they are logged in',
`lg_editdate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'this will not be changed with a ping; only a logout or other edit',
`lg_acctname` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'admin1' COMMENT 'as of 2012-04-01 this has not been used',
`lg_stusername` char(64) CHARACTER SET latin1 NOT NULL DEFAULT '' COMMENT 'the logged in user; note st_ prefix is inaccurate as it may be a contact or other object',
`lg_masterlogin` tinyint(1) unsigned NOT NULL DEFAULT '0',
`lg_stemail` char(85) CHARACTER SET latin1 NOT NULL DEFAULT '' COMMENT 'email address at time of login',
`lg_action` mediumint(5) NOT NULL DEFAULT '0',
`lg_requesttype` mediumint(5) NOT NULL DEFAULT '0',
`lg_sessionkey` char(32) CHARACTER SET latin1 NOT NULL DEFAULT '',
`lg_ipaddress` char(16) CHARACTER SET latin1 NOT NULL DEFAULT '0.0.0.0',
`lg_referrer` char(128) CHARACTER SET latin1 NOT NULL DEFAULT '',
`lg_feed` char(64) CHARACTER SET latin1 NOT NULL DEFAULT '',
`lg_lastping` datetime NOT NULL COMMENT 'last ajax ping to determine still logged in',
`lg_entertime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`lg_exittime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`lg_logouttime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`lg_id`),
KEY `lg_machines_id` (`lg_machines_id`),
KEY `lg_acctname` (`lg_acctname`),
KEY `lg_stusername` (`lg_stusername`),
KEY `lg_masterlogin` (`lg_masterlogin`),
KEY `lg_lastping` (`lg_lastping`)
) ENGINE=MyISAM AUTO_INCREMENT=3074 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

You will notice a whole lot of 0000-00-00 00:00:00 in there; that is no longer a valid format and entries into this table are failing.  Even trying to change the field definitions is problematic with lots of "0" dates in there.

I am trying to update the dates to 1000-01-01 00:00:00 which is supposed to be valid, but I am getting the message:

update bais_logs set
lg_editdate = IF(lg_editdate=0, '1000-01-01 00:00:00', lg_editdate),

lg_lastping = IF(lg_lastping=0, '1000-01-01 00:00:00', lg_lastping),

lg_entertime = IF(lg_entertime=0, '1000-01-01 00:00:00', lg_entertime),

lg_exittime = IF(lg_exittime=0, '1000-01-01 00:00:00', lg_exittime),

lg_logouttime = IF(lg_logouttime=0, '1000-01-01 00:00:00', lg_logouttime)
----------------
MySQL said:

#1292 - Incorrect datetime value: '1000-01-01 00:00:00' for column 'lg_editdate' at row 373

What am I doing wrong on this? Also, how would I set ALLOW_INVALID_DATES for now?  Is this something that is done in the mysql.ini file?

NOTE: from the command line I am logging into mysql as root and entering:

SET GLOBAL sql_mode = 'traditional,allow_invalid_dates';

and it shows as set, but it doesn't make any difference; I still get the behavior above.