texts: procedure is deleting data instead of inserting

texts:
I have a procedure as the following:

create procedure MoveDailyData
    @date1 bigint
    as
    declare @datenow bigint
    set @datenow = datediff(S,'01/01/1970',getdate())
    if ((@datenow)-@date1)>=86400
    begin
     insert into FinalLogs(problem,channel_number,username,app_id,Is_On,logtype,viewing_channel,playtime,macaddress,wifimacaddress,seen,version,date)
     select problem,channel_number,username,app_id,Is_On,logtype,viewing_channel,playtime,macaddress, wifimacaddress,seen,version,date from DayTempTable
     delete from DayTempTable where (@datenow-@date1)>=86400
     end
     else select 'Not Yet';

The procedure should copy the data from 1 temporary table to the final one (in a daily schedule ) and after that delete that data from the temporary table. Instead, I have been noticing that every couple of days, the data does not get copied, but only deleted from the temporary table, and so I have lost a great amount of relevant data.
the date column is a bigint (dates are saved as unix time). Is there something that I am doing wrong in this procedure?


texts:
I have two tables - table1 & table2 as below :

table1

id | name
1  | color
2  | size

table2

id | table1id | name
1  | 1        | white
2  | 1        | red
3  | 2        | small
4  | 2        | medium
5  | 2        | large

In both table first id column in primary key. How can i get unique combination from both tables.  Please suggest query to get the result.

expected output:

white       | small
white       | medium
white       | large
red         | small
red         | medium
red         | large


texts: How to find similar (but different) names with the same soundex value?

texts:
I got the output with the same name and same soundex value, but I want the result which has similar name and same soundex value.

Can anyone help me to do this?


texts: SQL Server : where with cast(value as varbinary) vs cast(value as varbinary(300))

texts:
Are there any theoretical performance differences between these queries in the scenarios below.

Scenarios are:

MyTable has Value nvarchar(300)
MyTable has Value nvarchar(max)

Q1:

declare @p nvarchar(3);
set @p = 'Foo';
select foo from MyTable where cast(value as varbinary) = cast(@p as varbinary);

Q2:

declare @p nvarchar(3);
set @p = 'Foo';

/****
x below should be:
- 300 when table allows value to me nvarchar(300)
- `max` when table allows value to be `nvarchar(max)`
****/

select foo from MyTable where cast(value as varbinary(x)) = cast(@p as varbinary(x));


texts:
I am getting the following error when attempting to add a node to an existing SQL Server 2016 Clustered instance on a Windows 2012 R2 Server

  TITLE: SQL Server Setup failure.

  SQL Server Setup has encountered the following error:

  Could not find subkey
  System\CurrentControlSet\Services\MSSQLFDLauncher$INSTANCE_NAME

  Error code 0x851B0001.

  For help, click:
  http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&EvtType=0x121D5FF7%25400x24ECD2CC%25401307%25401

Has anyone encountered this error and/or found a way to get around it?

I have done the following in an attempt to resolve:

Rebooted
Added the missing key to the registry

There are other instances that I have successfully added to this same machine. So there is no rational reason why this instance can't be added as well.

Thanks,

P.S. The link provided in the error goes to a page about the Surface Laptop.

Update 06/23/2017

More information:

This is a clustered instance with SQL Server Analysis Services that
is also clusterized.
The add node is using software with SP1 slipstreamed in
ProductUpdates
The error occurs when I get to the Perform Volume Maintenance screen
which adds the SQL Server service user to that role.


texts: Cant unlock file (Errcode: 11) on my new tables

texts:
I get the following error on my MySQL database

Cant unlock file (Errcode: 11)

But this error only happens when I try to access my new table I created below has two tables 1st I created recently and the second was already created when I got the database to update it
How I'm remove the errors?

CREATE TABLE `tbl_produtos` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Nome` varchar(140) DEFAULT NULL,
  `Descricao` text,
  `FotoDestaque` varchar(190) DEFAULT NULL,
  `FK_Categoria` int(11) DEFAULT NULL,
  `FK_SubCategoria` int(11) DEFAULT NULL,
  `Count` int(11) DEFAULT '0',
  `Valor` float DEFAULT NULL,
  `Propriedades` varchar(5) NOT NULL DEFAULT '0,1',
  `FotoCapa` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=latin1;

CREATE TABLE `tbl_admin_logs` (
  `RegistroID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `UsuarioID` int(10) unsigned NOT NULL,
  `UsuarioNome` varchar(45) COLLATE latin1_general_ci NOT NULL,
  `Modulo` varchar(25) COLLATE latin1_general_ci NOT NULL,
  `Registro` text COLLATE latin1_general_ci NOT NULL,
  `IProtocol` varchar(20) COLLATE latin1_general_ci NOT NULL,
  `Data` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`RegistroID`)
) ENGINE=InnoDB AUTO_INCREMENT=383 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

EDIT:

SELECT @@VERSION = 5.1.39-community
SHOW GLOBAL VARIABLES LIKE '%locking%'=
Variable_name: skip_external_locking Value: OFF


texts: Show application name instead of process in warning (PostgreSQL)

texts:
Sometimes we get a warning like this:

2019-... [15138] [Xc0BxiGn2g7j] LOG:  process 15138 still waiting for ShareLock on transaction 274489730 after 1000.107 ms
2019-... [15138] [Xc0BxiGn2g7j] DETAIL:  Process holding the lock: 14625. Wait queue: 15138.
2019-... [15138] [Xc0BxiGn2g7j] CONTEXT:  while deleting tuple (1,32) in relation "foobar"
2019-... [15138] [Xc0BxiGn2g7j] STATEMENT:  DELETE FROM "foobar" WHERE "foobar"."id" IN (2216750)

Unfortuantely the process id (in this case 14625) does not help much.

We set the application_name for all connections to PostgreSQL to a unique request-id.

Is there a way to let the above message print out the application name of the process which holds the connection?

Then it would make it much easier to investigate the root cause.

I updated the question, to show that the request-id of the blocked process is already visible to me. I am searching the request-id of process 14625.


texts: What is a good auto-generated key for a federated database?

texts:
I am about to split a large database into a bunch of federated instances.  Currently, most of the primary keys are auto-generated identity ints.  Obviously, that's not going to work in a federated setup.

I've read people using auto-generated GUIDs to replace the ints, but that data type has well-known performance sapping problems on its own.

What are some of the strategies I can use for having unique values for primary keys across federated members?


texts: Difference in number format (comma and dot) on SSMS and SSIS

texts:
I have a numeric(5,2) field on my SQL Server database. When I run the query, the number on that field appears with . (dot) separator. However, when I preview the query on SSIS, it appears with comma(,) separator. I am trying to show the number format with dot(.) on SSIS as well. How can I do that?
Thank you.


texts: Join two tables using pivot combining two columns in one table

texts:
I have two tables in this format:

Job_Skill
ID_Skill  Min_Job  Idea_Job   Max_Job
=====================================
1        0           0       1
2        0           1       1
7        1           1       1
8        1           1       1

Job_Education
Degree_Job  Field_Job  Min_Job   Idea_Job   Max_Job
===================================================
7        37         0         0         1
7        106        0         1         1
13       37         1         1         1
13       106        1         1         1

Desired Result
Criteria   1  2  7  8 [ 7  37 ] [ 7 106 ]  [13 37]   [13 106]
=============================================================
Min        0  0  1  1    1         1         0          0
Ideal      0  1  1  1    0         1         1          1
Max        1  1  1  1    1         1         1          1

How can I achieve this using a pivot, or any other method?
I am using SQL Server 2014. The columns are not fixed; they are dynamic. A static version is also acceptable, as I just want to understand how I should approach this.


texts: Connecting to SQL Server from within an SSIS script task

texts:
In SSIS, I have defined a Native OLE DB\SQL Server Native Client 11.0 connection that I've calledMyConnection.

Then I've created a data flow task, and within the Data Flow of that task there is:

A flat file source (from a CSV)
A script component in which I'm taking the CSV output and inserting to tables via first staging in a TVP.

Within the script component I want to connect to the database using MyConnection, but I see in
    the Microsoft Docs:

  You cannot call the AcquireConnection method of connection managers
  that return unmanaged objects, such as the OLE DB connection manager
  and the Excel connection manager, in the managed code of a Script
  task. However, you can read the ConnectionString property of these
  connection managers, and connect to the data source directly in your
  code by using the connection string with an OledbConnection from the
  System.Data.OleDb namespace.

That seems straightforward enough, so in my script task code I retrieve the connection string and try to use that to create a connection:

string conString = Connections.MyConnection.ConnectionString;
using (OleDbConnection con = new OleDbConnection(conString))
{
    try
    {
        if (con.State != ConnectionState.Open) con.Open();
        ...

And I get the error that authentication failed for the user. In the connection string there is no password (I'm using SQL Server authentication). I did check the box 'Save password' in the connection manager.

How do I use a connection defined in the project scope from within a script component?

When I provide the string explicitly, everything works. Also. Why can I not define the connection as anything but an Ole connection in the main package?


texts:
I think I need some pointers on security in SQL Server.   I'm trying to restrict some of our end users from getting access to certain columns (i.e. SSN) on a table.

I thought I could just use column level security to restrict access to the columns.  That successfully prevented users from accessing the table directly, but I was surprised that they could still get to those columns through a view that accessed that table.

I followed the tips here: http://www.mssqltips.com/sqlservertip/2124/filtering-sql-server-columns-using-column-level-permissions/   Those were very helpful, but when I created a view at the end, the intern was able to access that column by default

I've read that views are the best way to accomplish this, but I really don't want to go through and change all of the views and the legacy front-end application.  I would rather just restrict it once on the table and if a view tries to access that column it would just fail.

Is that possible or am I misunderstanding how security works in SQL Server?


texts: how to restore database from other server - sql

texts:
I have backup database from server A. Then I want to restore it in server B.
I have manage to restore it, but I cannot connect to it when running my VB program. I already change the configuration file, but still doesn't work.

I guess that I need to change something related with the permission, but I dont know what should I change. Anyone can help me?


texts: Why did a SQL Server Restart roll-back seemingly committed transactions?

texts:
I am using SQL Server 2014 Express

It seems that restarting SQL Server when an event such as a Data Lock happens is not (always) such a good idea. http://www.sqlpassion.at/archive/2016/08/08/restarting-sql-server-always-a-good-idea/

I want to ask specifically about the problem that a SQL Server Restart will roll-back uncommitted transactions.

My colleague ran SELECT @@TRANCOUNT and got zero. Therefore he assumed that restarting the SQL Server would not result in any kind of roll-backs. Nevertheless, a position which was moved from one table to another, got moved back after the restart.

Are there any situations where roll-backs would happen even if SELECT @@TRANCOUNT would return zero?


texts: Why use an intent lock at a higher level in the hierarchy, why not just a regular lock?

texts:
This article suggests intent locks are used at a higher level of the lock hierarchy to indicate that a lock has been taken on a lower level. The idea of this mechanism is that SQL Server doesn't need to check every row for active locks if it wants to place a lock on a page or table.
As an example, if I update a single row in AdventureWorks:
USE AdventureWorks2014;

BEGIN TRAN;
UPDATE  Person.Person
SET     LastName = 'Smith'
WHERE   BusinessEntityID = 1;

and then in another session, run sp_whoisactive with get_locks to see what locks were taken:
EXEC sp_whoisactive @get_locks = 1;
I can see the following:
      <Locks>
        <Lock resource_type="KEY" index_name="IX_Person_LastName_FirstName_MiddleName" request_mode="X" request_status="GRANT" request_count="2" />
        <Lock resource_type="KEY" index_name="PK_Person_BusinessEntityID" request_mode="X" request_status="GRANT" request_count="1" />
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" index_name="IX_Person_LastName_FirstName_MiddleName" request_mode="IX" request_status="GRANT" request_count="4" />
        <Lock resource_type="PAGE" page_type="*" index_name="PK_Person_BusinessEntityID" request_mode="IX" request_status="GRANT" request_count="1" />
      </Locks>

this backs up what the article above says - we have an exclusive row (key) lock on the two relevant indexes and therefore the pages and object have IX locks.
If I try and SELECT that row in another session:
SELECT * FROM Person.Person WHERE BusinessEntityID = 1
that query is indeed blocked.
My question therefore is, why is an intent lock a separate thing to a "regular" lock? Why would SQL Server not just place an X lock on the table?


texts: Reg: Default Privileges on PostgreSQL Schemas

texts:
I have a situation where many users can create database objects across all the available schema.

There is a generic user pg_dump_user used for by all the developers to dump the whole database to their local db whenever required. So this user should have read access to all the database objects.

To achieve this, I have tried setting the default privilege at schema level as well as at the user level.

--Default privileges set for each schema

ALTER DEFAULT PRIVILEGES IN SCHEMA xyz GRANT SELECT ON TABLES to ro_user';

--Default privileges set for each user

ALTER DEFAULT PRIVILEGES FOR ROLE abc_user GRANT SELECT ON TABLES to ro_user' ;

Even though above setup is there, after certain times these are getting vanished as the new objects created are not accessible by ro_user.

Any pointer on this would help, my aim is just to achieve select access for all the objects current and future for a user so as to do pg_dump.


texts: Is GUID(uniqueidentifier) not suitable for a clustered index?

texts:
When i use uniqueidentifier datatype for primary key column then it will automatically create clustered index...
So, will it slow down my queries or not ?

I am explaining some description of my problem..
I have two publisher which is replicating data into one subscriber(i.e primary Alwayson) then how to maintain primary key value in this scenario ?

So,Please give me a idea to maintain primary key value ?


texts: SQL Agent Jobs - What Permissions Required

texts:
We have a application that allows a user to view a list of SQL Agent jobs and create new ones.

However, the application displays a form asking for user credentials.
The user is a member of the SQLAgentOperatorRole in msdb.

I tested by making the user a member of sysadmin and this works.

Obviously I do not want to make that user sysadmin so what permission level am I missing?


texts: How to restore database from /var/lib/mysql

texts:
I have a database folder from a crashed server called gain_wordpress. I'm trying to put that database on a new server but I'm not able to do it successfully. I don't have a .sql dump, all I have is the database folder from a past crashed server with ibdata and the 2 log files.

I copied the gain_wordpress folder to /var/lib/mysql on the new server but when I do a > show tables on the database I get the following error:

ERROR 1018 (HY000): Can't read dir of './gain_wordpress/' (errno: 13)

I also changed the permission of the folder and did a chown mysql:mysql gain_wordpress -R and it still didn't take care of the problem.

What is the best way to recover gain_wordpress. Do I need to replace any of the ibdata or the logfiles? What is the best way? I have the corresponding .frm, .MYD and .MYI files for each table. Please advise.


texts: how to create trigger in SQL server using two tables to find stock quantity

texts:
I have two table sales and purchase. I want to create a trigger which stores final quantity in stock table by subtracting sales table sold quantity from purchase table.

There is kikkat as a name n quantity are 10, 15 respectively and both quantities should be subtracted from purchase quantity table and it should be saved in the stock table.


texts: Perform calculation from count of values for a distinct value

texts:
My Goal is to show the top hosts with successful access attempts (Total, %)

200 = successful attempt
404 = unsuccessful attempt

logs table

host     | status
10.1.1.1   | 200
10.1.1.1   | 200
10.1.1.1   | 404
10.10.10.1 | 200
10.10.10.1 | 200
10.10.10.1 | 404

SELECT DISTINCT(host) AS Host, count(host) as Attempts
FROM logs
GROUP BY host
ORDER BY count(status) DESC;

Results in

Host       | Attempts
10.1.1.1   |    3
10.10.10.1 |    3

Query

SELECT DISTINCT(host) as Host, count(status) as Failed
FROM logs
WHERE status = '404'
GROUP BY host
ORDER BY count(host) DESC;

Returns

Host       | Failed
10.1.1.1   |    1
10.10.10.1 |    1

so far so good for getting started, now however when I try this query

SELECT DISTINCT(host) as Host,
    (SELECT count(status)
     FROM logs
     WHERE status = '404') AS Failed,
     (SELECT count(status)
     FROM logs
     WHERE status = '200') AS Successful
FROM logs
GROUP BY host
ORDER BY count(host) DESC;

it returns

Host       | Failed |Successful
10.1.1.1   |    2   |   4
10.10.10.1 |    2   |   4

I'm trying to get it to return 1 failure and 2 successful. I can see what it's doing (returning the total for all hosts). Eventually I'm trying to get to where I divide the total of the successful attempts (2) by total count(status) (3) and return a percentage. I have searched for hours on this one problem with no success, any help would be really appreciated.


texts: Cleanup of AG databases on secondaries nodes

texts:
Consider a scenario where you have an AG running across 3 nodes and your backups are being backed up locally.
Each node have all the default jobs deployed and scheduled to run at same time. As the databases are part of the AG, backups are only being taken on the PRIMARY NODE.
After failover to a different node, the backups stored in the previous PRIMARY are not being deleted.

I thought that this could be happening since the default for the cleanup is AFTER_BACKUP and since no backups were happening on that node, the cleanup was not happening. I tried changing this to 'BEFORE_BACKUP' to no avail.

Am I missing anything obvious or is there anything I can do ?

Command running on every node

     sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBAAdmin -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'L:\Backups', @BackupType = 'FULL',@Compress='Y', @Verify = 'Y', @CleanupTime = 72, @CheckSum = 'Y', @LogToTable = 'Y'" -b

Thank you


texts: What is the purpose of sqlbulkcopy's useinternaltransaction property?

texts:
I'm just testing my PowerShellScript to do bulkcopy of multiple tables between sql-server and oracle Databases.
When the destination is Oracle, than I can monitor the progress by by executing

select count(*) from Mytable

the single batchsize chunkss individual committed.

Using a sql-server destination, there seems to be a single transaction. Which was rolledback due to network error at first trial. The second is still in progress. Will the use of internal transaction commit the individual chunks?

The use of bulkcopy seems a bit of an all or nothing, if it fails you can restart from the beginning.

$source = 'ora'
$dest = 'sql'

$SourceConnectionString_sql      = "Data Source=localhost;Initial Catalog=MySQLDatabase;Integrated Security=True"
$SourceConnectionString_ora      = "Data Source=MyOracleDatabase;User ID=MyUser;Password=MyPassword"

$DestinationConnectionString_sql = "Data Source=localhost;Initial Catalog=MySqleDatabase2;Integrated Security=True"
$DestinationConnectionString_ora = "Data Source=MyOracleDatabase2;User ID=MyUser;Password=MyPassword"

# -----------------------------------------------------------------------------

$SQL_Select_TABLES = "SELECT name FROM sys.Tables ORDER BY 1"
$SQL_Select_TABLES = "SELECT Top 1 name FROM sys.Tables"

$ORA_Select_TABLES = "SELECT TABLE_NAME Name FROM user_tables ORDER BY 1"
$ORA_Select_TABLES = "SELECT TABLE_NAME Name FROM user_tables where ROWNUM = 1"

$notifyAfter = 1000
# -----------------------------------------------------------------------------
if ($ora_dll -eq $null)
{
    $ora_dll = [System.Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess")
}

$global:start = get-date
$notifyFunction={
    $dauer = [int]((get-date) - $global:start).TotalMilliseconds / 1000
    Write-Host "$tableName $($args[1].RowsCopied) $dauer"
    }

if ($dest -eq 'sql')
{
    $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $DestinationConnectionString_sql
}
else
{
    $bulkCopy = new-object ("Oracle.DataAccess.Client.OracleBulkCopy") $DestinationConnectionString_ora
}

$bulkCopy.BatchSize = 50000
$bulkCopy.BulkCopyTimeout=10000000
if ($notifyAfter -gt 0){
    $bulkCopy.NotifyAfter=$notifyafter
    if ($dest -eq 'sql')
    {
       $bulkCopy.Add_SQlRowscopied($notifyFunction)
    }
    else
    {
       $bulkCopy.Add_OracleRowscopied($notifyFunction)
    }

}

if ($source -eq 'sql')
{
    $sourceConnection  = New-Object System.Data.SqlClient.SQLConnection($SourceConnectionString_sql)
    $sourceConnection.open()

    $commandSourceData  = New-Object system.Data.SqlClient.SqlCommand($SQL_Select_TABLES, $sourceConnection)
    $da = New-Object system.Data.SqlClient.SqlDataAdapter($commandSourceData)
    $dt = New-Object system.Data.datatable
    [void]$da.fill($dt)
}
else
{
    $sourceConnection  = New-Object Oracle.DataAccess.Client.OracleConnection($SourceConnectionString_ora)
    $sourceConnection.open()

    $commandSourceData  = New-Object Oracle.DataAccess.Client.OracleCommand($Ora_Select_TABLES, $sourceConnection)
    $da = New-Object Oracle.DataAccess.Client.OracleDataAdapter($commandSourceData)
    $dt = New-Object system.Data.datatable
    [void]$da.fill($dt)
}

foreach ($table in $dt)
{
    $tablename  = $table.name
    $Tablename
    try
    {
        $sql = "SELECT * FROM $tableName"
        if ($source -eq 'sql')
        {
            $commandSourceData  = New-Object system.Data.SqlClient.SqlCommand($sql,$sourceConnection)
        }
        else
        {
            $commandSourceData  = New-Object Oracle.DataAccess.Client.oracleCommand($sql,$sourceConnection)
        }
        $reader = $commandSourceData.ExecuteReader()
        $bulkCopy.DestinationTableName = $tableName
        $bulkCopy.WriteToServer($reader)
    }
    catch
    {
        $ex = $_.Exception
        Write-Host "Write-DataTable$($connectionName):$ex.Message"
    }
    finally
    {
        $reader.close()
    }
}
$sourceConnection.close()
$bulkCopy.close()

((get-date) - $start).TotalSeconds


texts: How to get the return value from a stored procedure executed with sp_executesql

texts:
I need to call a stored procedure in another database and check the return value. The name of the other database can vary so I'm trying to use sp_executesql, but I haven't been able to find how to get the return value from the procedure.

This is a simplified version of what I've got:

DECLARE @errorLogId INT
DECLARE @otherDbName NVARCHAR(MAX) = 'Foo' -- Get the database name from somewhere
DECLARE @sql NVARCHAR(MAX)

SET @sql = @otherDbName + '.dbo.SomeProc'

-- I want @errorLogId to be the return value from SomeProc, this didn't work.
EXECUTE @errorLogId = sp_executesql @sql

IF @errorLogId <> 0
   RAISERROR('SomeProc failed, ErrorLogId = %d.', 16, 1, @errorLogId) WITH SETERROR

If an error occurs in SomeProc it is caught and an entry is written to an error table (in the other database), then SomeProc returns the error table ID (identity) of the record that was written. Otherwise SomeProc returns 0.

I know SomeProc is failing because an error is written to the error log in the other database, but in the local database @errorLogId is 0 which is treated as success. I thought the return value would be 'passed through' sp_executesql, but I think the return value is actually from sp_executesql not SomeProc (i.e. sp_executesql can be successful regardless of whether SomeProc succeeded or failed).

Is there a way to access the return value from @otherDbName.dbo.SomeProc in the local database?

Thanks


texts: Object_name(object_id) returning incorrect results

texts:
We are trying to exclude stored procedures which have name like '%backup%' or %WI%'..

Here is the total query i used..

;WITH CTE
AS
(select object_name(object_id) as procname
from sys.sql_modules
where definition LIKE '%CTLEMPLOYEE%' and ( object_name(object_id) NOT LIKE '%BACKUP%'
  or object_name(object_id) not like '%backup%'
  OR object_name(object_id) NOT LIKE '%Wi%')
)
SELECT * FROM CTE

sample Output of above query:
  report_spBIHRRecruitmentReport_Phase2_backup

But strangely above query is returning results which still have names
like
1.backup
2.Wi

Then i tried to use collate to restrict results like results..

;WITH CTE
AS
(select object_name(object_id) as procname
from sys.sql_modules
where definition LIKE '%CTLEMPLOYEE%' collate SQL_Latin1_General_CP1_CI_AS
 and ( object_name(object_id) NOT LIKE '%BACKUP%'
  or object_name(object_id) not like '%backup%'
  OR object_name(object_id) NOT LIKE '%Wi%' collate SQL_Latin1_General_CP1_CI_AS
))
SELECT * FROM CTE

Output still contains names like below:

report_spBIHRRecruitmentReport_Phase2_backup

Below is my database info:
Version:SQL Server 2012
Collation:SQL_Latin1_General_CP1_CI_AS
comptabilty level :90

ASK:
Can you please help me understand why i am still getting names like '%backup% or '%wi%'

Scripts to Reproduce:

create proc usp_test1_backup
as
begin
end

create proc usp_test2_wi
as
begin
end

drop proc usp_test1_backup
drop proc usp_test2_wi

Execution plan of my query if it helps you:

http://pastebin.com/aKBqkPzj

Please let me know if you need any further info


texts: how do you reference server logins from a visual Studio database project?

texts:
I'm looking for a way to move all our SQL Server databases (on one server) into source control.

I tried creating a solution, and then adding VS database projects to the solution that correspond to the SQL Server databases.

As I was creating the projects (via importing existing databases) I chose to import security objects.

These security objects are users that need to reference logins of some kind. i.e. in a database project I have a folder Security with the SQL script: CREATE USER xxx. The VS debugger underlines this in red and warns me:

  SQL71501: User: xxx has an unresolved reference to Login xxx.

To fix this I tried creating a new project called SERVER, in which I would include server-related SQL files.

How do I reference this new project from the other database project?


texts: CPU load detalization does not work in SSMS Performance Dashboard

texts:
Starting from some version (it's actually earlier than the documented 17.2), SSMS has the Performance Dashboard built in, so you don't have to download and install it separately. Great.
We have SQL Server 2016 Standard and we are using it with SSMS 2017.
The Performance Dashboard works as intended, except in one specific place.
The bars on the System CPU Utilization chart are clickable, and when clicked, show which SQL loads contributed to the specific bar. This does not work. Instead, the following page opens:

Error:
A data source instance has not been supplied for the data source 'CHECK_DEPENDENCIES'.

I searched extensively for this issue, and only found discussions about fixing the reporting component when developing desktop apps, but nothing in regard of SSMS.
Further experimenting showed that:

SSMS 2016 does not have this entire Performance Dashboard page at all ("Performance Dashboard" is both the name of the entire report suite, and of one of its reports that contains the chart in question). It has other reports in the Performance Dashboard suite, but not the "Performance Dashboard" report.
SSMS 2017 has the "Performance Dashboard" page. It shows the same problem with SQL Server 2016, and with SQL Server 2017 on another machine.
SSMS 2019 has the "Performance Dashboard" page. It shows the same problem with SQL Server 2016, and with SQL Server 2017 on another machine. We did not have SQL Server 2019 to test there as well.

How do I fix this built-in performance report so that I can see the CPU load detalisation?


texts: Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days?

texts:
Our databases consist of lots of tables, most of them using an integer surrogate key as a primary key. About half of these primary keys are on identity columns.

The database development started in the days of SQL Server 6.0.

One of the rules followed from the beginning was, Avoid creating a clustered index based on an incrementing key, as you find in these Index Optimization Tips.

Now using SQL Server 2005 and SQL Server 2008, I have the strong impression that the circumstances changed. Meanwhile, these primary key columns are perfect first candidates for the clustered index of the table.


texts: can I use select statement for ifnull function?

texts:
I need to select a value from a table according to its date. If the record does not exists in the target table, I want to replace it with another value as defualt value instead of Null.
Can I do this with ifnull() function?

I have written this piece of code in a procedure, but I only get Null value for this field.

     CREATE PROCEDURE `test`(IN report_date DATE)
     BEGIN
        DECLARE initial_stock INT UNSIGNED;

        SET initial_stock = (select DISTINCT gs_fuel_type.fuel_initial_stock
                             from gs_fuel_type
                             WHERE gs_fuel_type.fuel_type_id = 2 );

        drop TEMPORARY table if EXISTS temp_daily_fuel;
        create TEMPORARY TABLE temp_daily_fuel (
           fuel_stock INT UNSIGNED DEFAULT 0
         );

        insert into  temp_daily_fuel (fuel_stock)
        select distinct
        (select ifnull(gs_fuel_stock.fuel_remainder , initial_stock)
            from gs_fuel_stock
            where   gs_fuel_stock.fuel_type_id = 2
                and gs_fuel_stock.shift_date = date_sub(report_date , INTERVAL 1 day)
        ) as fuel_stock

        FROM shifts_view
        where   shifts_view.shift_date = report_date
        AND shifts_view.fuel_type_id = 2
        GROUP BY  shifts_view.fuel_type_id;

     select  * from temp_daily_fuel ;

    END

Why { select ifnull(gs_fuel_stock.fuel_remainder , initial_stock) ....  } still returns null and doesn't replace the initial_stock value for this statement?

and I am quite sure that initial_stock value is not null.

The simplified Version of this code:

    CREATE PROCEDURE `test`(IN report_date DATE)
        BEGIN
           DECLARE i INT UNSIGNED;

           SET i = (select DISTINCT g.i from g WHERE g.id = 2 );

           drop TEMPORARY table if EXISTS t;
            create TEMPORARY TABLE t (
                f INT UNSIGNED DEFAULT 0
                );

           insert into  t (f)
                select distinct
            (select ifnull(gs.r , i) from gs
                where  gs.id = 2 and gs.date = date_sub(report_date , INTERVAL 1 day) ) as f
             FROM a
               where a.date = report_date AND a.id = 2
                  GROUP BY  a.id;

               select  * from t ;
            END

in this code, I want the value of "i" to be replaced instead of showing NULL.


texts: Table structure and query for messaging between users

texts:
I'm building a PHP Laravel app and one of the features is messaging between users. A user can message other users.

This is my current schema:

I'm trying to achieve the following:-

Have a list of conversations between different users on a page with the last message between users.
The first column should be the participant's name. This is the query I'm using currently:

SELECT * FROM messages WHERE id IN (SELECT MAX(id) FROM messages WHERE sender_id = $current_user_id OR recipient_id =$current_user_id GROUP BY conversation_id)

which gives me the result in screenshot (Note: The first column is the sender's id so this is incorrect for now).
I have a col in messages table :conversation_id which I'm using to group the messages. I set this value in php while inserting message, based on the 2 participants' id, eg. 1_2. this is calculated using: "smaller_id_number"_"bigger_id_number". This makes sure that the conversation id between 2 users will always be same. Whether they sent or received the message. I think this is not the best approach so I'm open to alternative suggestions to achieve what I'm trying to where I don't have to use a redundant (as per my thoughts) column conversation_id.

On a side note. I also have a single page for conversations between 2 users where users can see all messages between them and the participant. I'm using this query for that:

SELECT * FROM messages WHERE conversation_id='1_3'

Then on php side, I check if the message was sent or received by current user by checking the sender_id. So if I get a solution for my problem above without using conversation_id, I might need to update this feature as well.

Any help is appreciated. Thanks.

P.S. I have checked similar questions but they are not answering my problem.

EDIT:
To clarify, I'm only asking one question here.

Current sample data
These are the messages. My goal is to get a list of last messages between a user and other users(highlighted in yellow) and the participant's name. I will query using the current user's id on recipient_id or sender_id

Current results with my new query:
SELECT m.*, u1.name AS sender, u2.name AS recipient
FROM messages m
JOIN users u1 ON m.sender_id=u1.id
JOIN users u2 ON m.recipient_id=u2.id
WHERE m.id IN (
SELECT MAX(id)
FROM messages
WHERE sender_id = 1 OR recipient_id =1
GROUP BY conversation_id) ORDER BY m.id DESC

I am able to get the participant name in PHP by checking if the current user's id is the sender's id. If yes then I set the participant name to recipient. If not then sender.

As you can see, I have to use php to get the participant name. My goal is to get the participant in MYSQL, if possible. And to eliminate the extra column conversation_id that I'm using if it would be possible without it because I also have to generate this first in php before inserting new message.

Expected output:


texts:
I am trying to design a database that has supertype/subtypes. I have the following users:
- basic user
- business user
- administrator (super user).

All three users share a common table (users) where fields like name, email, password, etc are stored. However, the business users have a separate table (business) where the business fields like business_name, business_license, business_email, etc are stored. My problem is that my business users are themselves split into 5 or more categories.
My business users are split like this:

painters
vehicle detailers
lot attendants
service technicians:

engine technicians
transmission technicians
computer technicians
electrical technicians

sales representatives

physical location sales representative
internet sales representative
sales manager

I currently designed this database but I not really happy with it. Is there a better approach?

EDIT 1 I need to mention that the business types and subtype do not have any different fields. They business table holds all I need. The business types and subtype are more like an ENUM, but held in a table.

EDIT 2
Example of SELECTs that I will need. More exactly, how a result should look like:

username       | user_type   | business_name    | business_type      | business_subtype

John Doe       | business    | AUDI Dealership  | service technician | engine mechanic
Mary Liu       | basic user  | NULL             | NULL               | NULL
George Vangh   | super user  | NULL             | NULL               | NULL
Martha Ayla    | business    | AUDI Dealership  | sales              | sales representative
Carlos M       | business    | Ford & Lincoln   | sales              | sales manager

UPDATE 3
Found this example online and I thought I'll give it a try. Is this acceptable?


texts: Create view with dynamically selected tables - is it possible?

texts:
I have subsection of tables in my database that share part of a schema and a naming scheme. I'd like to create a view that automatically gathers all of the common columns of these tables into a single place. The catch is that more of these tables may be added from time to time, and I would ideally like to have the view dynamically add these new tables to itself.

For example, say we have three tables

+-----------------+ +-----------------+ +-----------------+
|table1           | |table2           | |table3           |
+-----------------+ +-----------------+ +-----------------+
|id               | |id               | |id               |
|name             | |name             | |name             |
|unique_attribute1| |unique_attribute2| |unique_attribute3|
+-----------------+ +-----------------+ +-----------------+

If I were doing this statically, I would write the view as:

CREATE VIEW unified_table_view AS
SELECT id, name FROM
  (SELECT id, name FROM table1)
  UNION (SELECT id, name FROM table2)
  UNION (SELECT id, name FROM table3);

However, if a new table

+-----------------+
|table4           |
+-----------------+
|id               |
|name             |
|unique_attribute4|
+-----------------+

is added and I want it to be included in the view, I would have to go an manually add that to the view definition:

CREATE VIEW unified_table_view AS
SELECT id, name FROM
  SELECT id, name FROM table1
  UNION SELECT id, name FROM table2
  UNION SELECT id, name FROM table3
  UNION SELECT id, name FROM table4;

I want to know if it is possible to write the view such that when table4 is added, the view automatically incorporates it, without me having to update the view definition.

Normally if I was doing something like that, I would interrogate information_schema to construct something for EXECUTE, but that is a one-time thing and I would have to run the query again to update the view.


texts: What is the definition of a bad index? How do we decide? What logic we should have?

texts:
There are queries on the internet that finds bad indexes

Their logic is so simple though

If write count > read count = bad index

Here one example query

    SELECT  OBJECT_NAME(s.object_id) AS 'Table Name',
        i.name AS 'Index Name',
        i.index_id,
        user_updates AS 'Total Writes',
        user_seeks + user_scans + user_lookups AS 'Total Reads',
        user_updates - ( user_seeks + user_scans + user_lookups ) AS 'Difference'
FROM    sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
        INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON s.object_id = i.object_id
                                                       AND i.index_id = s.index_id
WHERE   OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
        AND s.database_id = DB_ID()
        AND user_updates > ( user_seeks + user_scans + user_lookups )
        AND i.index_id > 1
ORDER BY 'Difference' DESC,
        'Total Writes' DESC,
        'Total Reads' ASC ;
-- Index Read/Write stats for a single table
SELECT  OBJECT_NAME(s.object_id) AS 'TableName',
        i.name AS 'IndexName',
        i.index_id,
        SUM(user_seeks) AS 'User Seeks',
        SUM(user_scans) AS 'User Scans',
        SUM(user_lookups) AS 'User Lookups',
        SUM(user_seeks + user_scans + user_lookups) AS 'Total Reads',
        SUM(user_updates) AS 'Total Writes'
FROM    sys.dm_db_index_usage_stats AS s
        INNER JOIN sys.indexes AS i ON s.object_id = i.object_id
                                       AND i.index_id = s.index_id
WHERE   OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
        AND s.database_id = DB_ID()
        AND OBJECT_NAME(s.object_id) = 'AccountTransaction'
GROUP BY OBJECT_NAME(s.object_id),
        i.name,
        i.index_id
ORDER BY 'Total Writes' DESC,
        'Total Reads' DESC ;,

However is the issue this simple? Can we call all write > read = bad index?

Are there any better logic having SQL query that can define bad indexes?

Lets ignore indexes that ensures integrity of data such as unique constraints

Lets assume that indexes are updated and used every minute

I am using SQL server 2014 and here the results this query generates

Ty


texts: Powershell and Postgres – improving INSERT performance

texts:
I have a postgres database which I populate via a powershell script. I’m having trouble with the time it takes to for the script to perform an INSERT

The powershell script is very simple, it reads records from a remote host, and writes the raw (unprocessed) record as a simple text-string into a postgres table. The table looks like this…..

CREATE TABLE message
(
  messid SERIAL UNIQUE,
  rawmessage character(250),
  loadedtime timestamp without time zone
)

The powershell connection to the database is this….

$DBConnectionString = "Driver={PostgreSQL UNICODE(x64)};Server=$MyServer;Port=$MyPort;Database=$MyDB;Uid=$MyUid;Pwd=$MyPass;Options='autocommit=off';"
$DBConn = New-Object System.Data.Odbc.OdbcConnection;
$DBConn.ConnectionString = $DBConnectionString;
$DBConn.Open();

And the powershell INSERT is….

$insertDml3 = "INSERT INTO Message(rawMessage, loadedTime) VALUES ('$strLine',to_timestamp('$loadDate','DD-Mon-YYYY HH24:MI:SS'))"
$DBCmd = $DBConn.CreateCommand();
$DBCmd.CommandText = $insertDml3;
$DBCmd.ExecuteReader();

I’ve done some performance testing (using the powershell stopwatch) and know that the time taken for each insert is typically 12-20ms per record. Most of the time the records arrive (from the remote host) at a rate of about 10 a second, so 10-20ms per record isn’t a problem, however during busy periods the records can arrive at a rate of several hundred per second, so basically they’re arriving at a far quicker rate than they can be added to the database. On a typical day a few million records might arrive. I’ve also done some other experiments so I know that the bottleneck is inserting in the database, I can (for instance) just write the incoming records to a flat-file much quicker than inserting to a database.

The biggest potential issue I’ve looked at is ‘autocommit’, the postgres session default is ‘off’ (which is what I want) but I believe the ODBC default is to set it to ‘on’ (which I don’t want). I have attempted to set autocommit to off (see the connection string above) – but this doesn’t seem to make any difference, either it was off anyway, or my syntax for turning it off isn’t correct.

The powershell version is 5.1 (build 16299, revision 251)
The table has no triggers
There are no foreign keys involved.
The raw messages are variable length text strings from 10 to 220 characters long
My current solution has been to simply discard every nth incoming record
as the workload get’s higher.

How can I improve performance? How can I confirm if autocommit is really off or on – the opitions keyword in the connection string seems to accept any garbage without any visible error so I’ve no idea if the syntax is correct.

Thanks

addendum -

I will investigate creating a flat file with the records and then doing a COPY (I had previously thought COPY only works from one table to another)
I will also look at inserting multiple rows with a single INSERT, I've only just discovered this is an option.
By way of a test I tried timing how long it takes for the same powershell script to insert into an MS Access table instead - the answer is 1-2ms per record, less than a tenth of the time to insert into postgres!


texts: NTFS Filesystem Fragmentation issue occur after migrating mysql 5.6 database to mariadb 10.2

texts:
I am currently experience a novel issue with a previously fine running database server. For a second time within 8 weeks, the current running MariaDB 10.2.7 has been corrupted due to Operating System Error 665 with underlying cause being the NTFS Filesystem.The Sysinternals Utility contig should a heavily fragmented .ibd file (>1Million fragments) which leads me to believe the above is the cause of the problem.

3 months ago I switched from MySQL Server 5.6 to MariaDB 10.2.6 due to better backup features. The database runs on top of a virtual Windows 2012 R2 Machine. Previously, this setup was able to handle a much larger database without so much as a sweat and was running 24/7. The underyling hardware does not post any errors, nor do the physical harddrives show any errors.

As this has happened twice in the past 8 weeks, I am wondering if something is going wrong in MariaDB? Has anybody else experienced such heavy fragmentation of the database file? And does it have to do with the SQL Server switch? Are there any settings which I have overlooked in the MariaDB my.cnf which make it play nice with NTFS?

Thank you very much for your answers!

Edit:

The exact chronology is as follows

31st of July Write the existing 500GB Database into empty MariaDB. This was done via Parsing the old database and writing into the new via INSERT Statement. This was done as a new column for legacy "flagging" was added.
During the writing process of a minor table( one .ibd file), the error occurred for the first time. This resulted in a corrupt new database. Unfortunately, at the time I thought the operating system error 665 was due to a faulty MariaDB install.

To test this hypothesis, reinstalled MariaDB on 4th of August, recreated an empty database to write in TestData and see if the error occurred again. The database ran since then till the 22nd of September. Then the operating system error 665 was logged again and the database was corrupt.

This prompted me to look more seriously at error 665 and see if the database files had the fragmentation issue. This was the case with 1M+ fragments for now only 200GB. Unfortunately, I never checked the fragmentation issue before as it had been a non-issue. The write operations involve uuid of incoming packages from multiple sources to verify origin. Additionally, the data is mainly Blob and timestamp information.

Now I am sitting on two corrupted MariaDBs. In recovery mode, the SHOW TABLE Status shows errors whereas SHOW CREATE TABLE gives an error on execution.

The my.cnf is below

[mysqld]
datadir=D:\Database\Data\
port=3306
max_allowed_packet=64M
max_connections=251
innodb_buffer_pool_size=6G

[client]
port=3306
plugin-dir=C:/Program Files/MAriaDB 10.2/lib/plugin

The machine has 8 Vcores and rund on 16GB RAM. I hope this provides you with the wanted information.

The main issue is there anything that I can set in mariadb or on the NTFS Filepartition to stop this error from occurring?

Thanks for your time!


texts: MongoDB hashtable namespace index max chain reached error after machine crash

texts:
I have a database machine running standalone MongoDB v2.6.12 on Ubuntu 14.04. It is running well until the machine crashed (by power failure).

MongoDB won't start since then outputting the following error in log.

2017-11-07T16:19:12.149+0800 ***** SERVER RESTARTED *****
2017-11-07T16:19:12.151+0800 [initandlisten] MongoDB starting : pid=1978 port=27017 dbpath=/edx/var/mongo/mongodb 64-bit host=external-db
2017-11-07T16:19:12.151+0800 [initandlisten] db version v2.6.12
2017-11-07T16:19:12.152+0800 [initandlisten] git version: d73c92b1c85703828b55c2916a5dd4ad46535f6a
2017-11-07T16:19:12.152+0800 [initandlisten] build info: Linux build5.ny.cbi.10gen.cc 2.6.32-431.3.1.el6.x86_64 #1 SMP Fri Jan 3 21:39:27 UTC 2014 x86_64 BOOST_LIB_VERSION=1_49
2017-11-07T16:19:12.152+0800 [initandlisten] allocator: tcmalloc
2017-11-07T16:19:12.152+0800 [initandlisten] options: { config: "/etc/mongod.conf", net: { port: 27017 }, security: { authorization: "enabled" }, storage: { dbPath: "/edx/var/mongo/mongodb", journal: { enabled: true }, smallFiles: true }, systemLog: { destination: "file", logAppend: true, path: "/edx/var/log/mongo/mongodb.log" } }
2017-11-07T16:19:12.154+0800 [initandlisten] journal dir=/edx/var/mongo/mongodb/journal
2017-11-07T16:19:12.154+0800 [initandlisten] recover begin
2017-11-07T16:19:12.154+0800 [initandlisten] info no lsn file in journal/ directory
2017-11-07T16:19:12.155+0800 [initandlisten] recover lsn: 0
2017-11-07T16:19:12.155+0800 [initandlisten] recover /edx/var/mongo/mongodb/journal/j._0
2017-11-07T16:19:12.155+0800 [initandlisten] recover cleaning up
2017-11-07T16:19:12.155+0800 [initandlisten] removeJournalFiles
2017-11-07T16:19:12.156+0800 [initandlisten] recover done
2017-11-07T16:19:12.466+0800 [initandlisten] error: hashtable namespace index max chain reached:1335
2017-11-07T16:19:12.469+0800 [initandlisten] error: hashtable namespace index max chain reached:1335
2017-11-07T16:19:12.469+0800 [initandlisten] error: hashtable namespace index max chain reached:1335
2017-11-07T16:19:12.472+0800 [initandlisten] waiting for connections on port 27017
2017-11-07T16:19:12.483+0800 [IndexRebuilder] Fatal Assertion 16253
2017-11-07T16:19:12.487+0800 [IndexRebuilder] 0x121df81 0x11bd689 0x11a019d 0xa2d5e5 0xa2d703 0x904f70 0xba15e4 0xba24b8 0x11a3532 0x1262d49 0x7f8f424c9184 0x7f8f417cdffd

It is unable to run with --repair which will result in this error BadValue Can't have journaling enabled when using --repair option.

And I don't think it is really a namespace problem, because a clone machine from the snapshot before the crash will run without any problem.

The other question you guys marked that I'm "duplicateing" is in completely different situation. As I'm not even able to start MongoDB, how can I run command in it?

Also I tried to disable journal and get the following error.

You specified --repair but there are dirty journal files. Please
restart without --repair to allow the journal files to be replayed.
If you wish to repair all databases, please shutdown cleanly and
run with --repair again.

And if I remove --repair I get the same error: hashtable namespace index max chain reached:1335 errors in the log as in the beginning.


texts: Adding multiple indexes to avoid skip/limit in MongoDB

texts:
Let's say I have a MongoDB's database with millon of records having a City name, a User name and an activity.
City        | User   | Action
------------+--------+-------------------
Gotham      | Batman | Fight with Joker
Gotham      | Batman | Fight with Penguin
Gotham      | Robin  | Fight with Joker
Gotham      | Robin  | Fight with Riddler
Springfield | Homer  | Working
Springfield | Homer  | Moe's bar
Springfield | Bart   | At school

I want a fast paging method to show data in a web site listing all results, results by city or results by city and user. Although I have the main indexes by city & name, still using the db.find(...).skip((page_number - 1) * rows_per_page).limit(rows_per_page) method is slow.
Because no record is deleted, my approch to circumvent the performance issue, is to add some extra rows with incremental values and several new indexes:
City        | User   | Action             | Global Index | Index By City | Index By City & User
------------+--------+--------------------+--------------+---------------+---------------------
Gotham      | Batman | Fight with Joker   | 1            | 1             | 1
Gotham      | Batman | Fight with Penguin | 2            | 2             | 2
Gotham      | Robin  | Fight with Joker   | 3            | 3             | 1
Gotham      | Robin  | Fight with Riddler | 4            | 4             | 2
Springfield | Homer  | Working            | 5            | 1             | 1
Springfield | Homer  | Moe's bar          | 6            | 2             | 2
Springfield | Bart   | At school          | 7            | 3             | 1

So I do paging by Global Index, City + Index By City or City + User + Index By City & User
Is this approach good or horrible? Or is there a better method?


texts: How can I perform a full-text CONTAINS call across a list of phrases?

texts:
I have a list of words/phrases in a table phrases. I have another table with a full-text catalog indexing the description column.

I want to populate a table results with a record for each phrase, and the number of times that phrases was found in the description:

INSERT INTO results(phrase, foundcount)
SELECT  phrase,
    (SELECT COUNT(*) FROM table WHERE CONTAINS(table.description, phrase))
FROM    phrases;

No matter how I try, this results in the error:

Incorrect syntax near `phrase`. Expecting STRING, TEX_LEX, or VARIABLE

I would really rather not use a cursor just so I can assign each phrase to a variable. Is that really the only option?


texts:
I have implemented a like/unlike features for posts in one of my projects. The like table is normalized and has a column visible. The schema is as follows:

Column      Type       Null    Default  Links to
id          int(11)     No
user_id     int(11)     Yes     NULL    user  -> id
post_id     int(11)     Yes     NULL    posts -> id
visible     tinyint(1)  Yes     NULL
created     datetime    No
updated     datetime    No

What I'm doing currently is that when a user likes a post, I create a record and when he un-likes it, I just set the visible column to false. The count is calculated by post_id and visible = true. I did that to prevent excessive load on database when a user likes/unlikes a post.

My question is am I being paranoid in considering database performance so early (without much traffic) or is this among the best practices and I should continue with this?


texts: How to get 0 as array_length() result when there are no elements

texts:
I have a query like this:
select id, array_length(users_who_like_ids,1) as ct
from queryables
order by 2 desc;

But empty arrays (with no elements) sort on top. I'd rather have array_length() return 0 for that case so that this would get sorted to the bottom.
I'm probably not understanding array_length() (ok, definitely) but:
embers_dev2=# select array_length(array[1,2], 1), array_length(ARRAY[]::integer[],1);

should return 0 rather than nothing (NULL), right?
Could I do an if statement like inline on it?
Edit
According to this related answer on SO, it looks like COALESCE might be what I want - but I am open to better ideas:

postgresql return 0 if returned value is null


texts:
I need to assign db_owner to all users within an instance. There are a few hundred logins and same number of databases within the instance. I would like each user to get db_owner within the database where that user exists. I know how to do it for individual users, but I do not know how it's done for all of them at once.

I am well aware that it is not a good idea to give db_owner to everyone, but...we are installing a very specific application. Every users who wants to use this application will need db_owner. We already tried walkarounds, but the aplicaton is rather primitive in nature so until application code is changed we will have to tolerate insane number of db_owners. There is no sensitive data on this particular instance anyway so we have management approval.


texts: MongoDB Shema to support concurrent update on a document

texts:
We were working on a project with a 300 documents with currentValue field in a main collection, in order to track the history of each document of first collection. we created another collection named history with approximately 6.5 millions of documents.
For each input of system we have to add around 30 history item and update currentValue field of main collection, so, We tried computational field design pattern for currentValue, which lead us to have writeConfilict in concurrent situations (at concurrency of around 1000 requests).
Then we tried to compute currentValue field with sum (amount field) and groupBy(mainId field) on history collection  which takes too long (> 3s).
Main collection docs:
{
    "_id" : ObjectId(...),
    "stock" : [
        {
            "currentAmount" : -313430.0,
            "lastPrice" : -10.0,
            "storage" : ObjectId("..."),
            "alarmCapacity" : 12
        },
        {
            "currentAmount" : 30,
            "lastPrice" : 0,
            "storage" : ObjectId("..."),
            "alarmCapacity" : 12
        },
       .
       .
       .
    ],
    "name" : "name",

}

History collection docs:
{
    "_id" : ObjectId("..."),
    "mainId" : ObjectId("..."),
    "amount" : 5,
}

If you have any other idea to handle this situation(application or db level), I would be thankful.
UPDATE 1
The update query if I use computed pattern would be:
mainCollection.findOneAndUpdate(
            {
                $and: [
                    { _id: id },
                    { "stock.storage": fromId },
                    { "stock.deletedAt": null }
                ],
            },
            {
                $inc: {
                    "stock.$.currentAmount": -1 * amount,
                }
            },
            {
                session
            }
        )

And Aggregation pipeline if I want to calculate currentAmount everytime:
mainCollection.aggregate([
            {
                $match: {
                    branch: new ObjectId("...")
                }
            },
            {
                $group: {
                    _id: "$ingredient",
                    currentAmount: {
                        $sum: "$amount"
                    }
                }
            }])


texts: How to diagnose try_parse being very slow?

texts:
I had a very large query that ran slower than I thought it should do, but no amount of digging through the query execution plan helped shed any light on the slowness. Eventually I narrowed it down though: try_parse was the culprit!

Normal query:

SELECT CloseDate
FROM MyTable

(4959 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 17 ms.

With try_parse:

SELECT try_parse(CloseDate as datetime using 'en-us')
FROM MyTable

(4959 row(s) affected)

SQL Server Execution Times:
   CPU time = 719 ms,  elapsed time = 718 ms.

The execution plan in the latter case looks innocent enough:

Is there a way I can spot the culprit more easily in the future? The actual source of slowness is completely hidden from view.


texts: Oracle 12c Enterprise Manager Database Express Subsequent Installation on Windows Server 2012

texts:
I have an Oracle 12c database instance which was installed without the option for configuring the "Enterprise Manager (EM) Database Express"

How can i install it subsequent for a running instance? The Database Configuration Assistant doesn´t give me this option and the prior to 12c command emctl doesn´t exist anymore.

Any suggestions?


texts:
Say I want 20 closest businesses to a location.

Innodb doesn't support that. So what does?

Is it possible to have some tables in some engine and other tables on different engines?


texts:
I'm trying to find a way to get last request date for a given SPID. I have 2 SPID's on Waiting Command; there is no transaction open for either SPID.

SELECT @@version

> Adaptive Server Enterprise/12.5.3


texts: Emulating Oracle flashback functionality

texts:
I'm considering going from Oracle Enterprise Edition (EE) to SE/SE1 but want to retain the flashback database and flashback table features from EE.

Does anyone know how to emulate these features in SE/SE1?

Thanks in advance


texts:
Using SQL Server 2008R2, I am trying to pull the latest assessment date for a patient. I have it pulling the correct data I need, but it is pulling all dates. For instance if a patient has an assessment on 1/1, 1/5, 1/8, and 1/10 I am currently getting all records when I really only want the last one (in this example, 1/10).

SELECT     TOP (100) PERCENT dbo.PT_BASIC.PATIENT_CODE,
           dbo.PT_BASIC.NAME_FULL,
           dbo.VW_ASSESSMENT_DATA.ASSESSMENT_DATE,
           dbo.VW_ASSESSMENT_DATA.QUESTION_TEXT,
           dbo.VW_ASSESSMENT_DATA.ANSWER_TEXT
FROM       dbo.VW_ASSESSMENT_DATA
INNER JOIN dbo.PT_BASIC
ON         dbo.VW_ASSESSMENT_DATA.PATIENT_ID = dbo.PT_BASIC.PATIENT_ID
WHERE      (dbo.VW_ASSESSMENT_DATA.ANSWER_TEXT LIKE '%Level %')
AND        (dbo.VW_ASSESSMENT_DATA.ASSESSMENT_DATE > CONVERT(DATETIME, '2017-12-01 00:00:00', 102))
AND        (dbo.VW_ASSESSMENT_DATA.QUESTION_TEXT LIKE '%Plan/Class%')
ORDER BY   dbo.PT_BASIC.NAME_FULL


texts: Which hot backup tools for MySQL InnoDB database : Percona XtraBackup, Zmanda Recovery Manager Enterprise or Community or…?

texts:
I manage a PHP/MySQL website :

max 500 simultaneous users / average of 4000 per hour in update/read
1 MySQL Community Server (5.1) - (Xeon / 16 Go RAM)
InnoDB Storage engine on 100 tables for 1.4 Go of data

Actually, every 30mn a cronjob make a full dump in file... But since several, it's cause fatal errors on PHP (because of timeout)

So I would like to have a tool to do real Hot Backup... I googled and "stackoverflowed" and it seems that " products seems to do the job :
 - Percona XTraBackup (free)
 - Zmanda Recovery Manager Community (free)
 - Zmanda Recovery Manager Enterprise (500$)

But I can't see what are really the difference and what could be the criteria. My need seems to be simple : made full (or incremental) backup to restore my database if a crash occurs without putting my website down...
I'm not a DBA so I need a "simple" product.

Many thanks for you help.


texts: SQLite - No such column when column exists

texts:
I have a table (trackedinfo) inside my database that has the following columns (columns obtained by running PRAGMA table_info(trackedinfo);)

The problem is that even though the column sendok exists, when running a query on the database with that field, it throws an error.

Example queries:

SELECT * FROM trackedinfo WHERE sendok IS NULL;
SELECT sendok FROM trackedinfo;

Error:

SQLITE_ERROR: SQL error or missing database (no such column: sendok)

But, if I run a query selecting all of the fields, it brings me the info regarding sendok:

Here is the CREATE command of the database:

CREATE TABLE trackedinfo
(
    id INTEGER PRIMARY KEY,
    date_time_start TEXT,
    date_time_end TEXT,
    tracked_name TEXT,
    tracked_origin TEXT,
    tracked_maker TEXT,
    tracked_version TEXT,
    tracked_type TEXT,
    sendok TEXT,
    tracked_id TEXT
);

EDIT

It also happens with the column tracked_id

EDIT 2

Info that I got by executing .schema trackedinfo

CREATE TABLE IF NOT EXISTS "trackedinfo" ("id" INTEGER PRIMARY KEY, "date_time_start" TEXT, "date_time_end" TEXT, "tracked_name" TEXT, "tracked_origin" TEXT, "tracked_maker" TEXT, "tracked_version" TEXT, "tracked_type" TEXT, "sendok " TEXT, "tracked_id " TEXT);


texts: How can I drop a column from a large (700M rows) hot (multiple transactions per second) table with minimal downtime?

texts:
My research suggests I can drop a column on a table in a matter of moments (provided I obtain an exclusive lock) because it is only a metadata change.

The table has two different services constantly inserting and updating records.  It is very hot.  Without developing a full reproduction environment of this level of activity, how can I ensure a statement like

ALTER TABLE x DROP COLUMN y

will successfully obtain an exclusive lock, do the drop quickly, and then unlock the table?

How will SQL Server queue this request (eg First In First Out)?
Can I be sure the DROP COLUMN will truly only take a few moments?

Locking this table in PROD for an extended time will be unacceptable so I am trying to avoid surprises.

Other notes:
Later I will re-index ONLINE to defragment the index and reclaim the space.


texts:
I've created DBMS_SCHEDULER job, which type is CHAIN. Chain has 2 steps and 2 rules. Each step has PL/SQL "program" (which inserts SYSDATE to table). First rule's condition is TRUE. Second rule has condition checks if step 1 is completed and then runs second step. After I enabled CHAIN and JOB, then DBA_SCHEDULER_JOB_RUN_DETAILS table shows SUCCEEDED for both chain steps. Table keeping date has new values. So I would say that chain has ran successfully.

Problem here is that JOB does not execute second time. Job is suppose to run every 5 minutes.

DBMS_SCHEDULER.CREATE_JOB (
    job_name          => 'RUN_REFRESH',
    job_type          => 'CHAIN',
    job_action        => 'QUICK_REFRESH_CHAIN',
    repeat_interval   => 'FREQ=MINUTELY;INTERVAL=5',
    start_date       => SYSTIMESTAMP,
    enabled      => TRUE
);
END;
/

Job is also active in DBA_SCHEDULER_RUNNING_JOBS table.

Additional info:

SELECT START_DATE, LAST_START_DATE, NEXT_RUN_DATE
FROM DBA_SCHEDULER_JOBS
WHERE job_name = 'RUN_REFRESH';

shows:
21.01.2015 12:58:18,000000000
21.01.2015 12:58:18,328107000
21.01.2015 12:58:18,000000000

Questions:

Does DBA_SCHEDULER_RUNNING_JOBS show only currently running
jobs instead or enabled/scheduled jobs?
Why doesn't JOB run second time, even though CHAIN ran
successfully?


texts:
When attempting to delete a publication, I am getting the error cannot drop publication 'XX' cannot drop article 'XX'.

The subscription has been deleted at the subscriber. Any ideas on how I can force the deletion of the publication so that it can be recreated? I can execute

exec sp_dropsubscription @publication = N'XX', @subscriber = N'all'

but this has no apparent effect.


texts: What does "+date+" mean in this context?

texts:
select LTRIM(RTRIM(convert(char(10), max(date), 101))) as str
from   inventory
where  date < '" + date + "'"

I happen to see this a lot in my Java code. Can someone please re-phrase the query above in plain English? Thank you.


texts: use of coalesce to check for all variables being null

texts:
How can I use coalesce to check for all variables with different type precedence to all be NULL without encountering errors.

DECLARE
@StartDate DATETIME = NULL,
@EndDate DATETIME = NULL,
@StateCode CHAR(2) = NULL,
@CountyCode CHAR(3) = NULL,
@ProducerName VARCHAR(64) = null,
@TaxID VARCHAR(9) = null,
@Farm INT = null

SELECT CASE WHEN COALESCE(
    @StartDate,
    @EndDate,
    @StateCode,
    @CountyCode,
    @ProducerName,
    @TaxID,
    @Farm) IS NULL
THEN 'yes' ELSE 'no' END

Output: 'yes'

adding SELECT @StateCode = '22' gives this error

Msg 241, Level 16, State 1, Line 94
Conversion failed when converting date and/or time from character string.

this is valid

SELECT CASE WHEN
   COALESCE(@StartDate, @EndDate) IS NULL
   AND COALESCE(@StateCode, @CountyCode, @ProducerName, @TaxID) IS NULL
   AND @farm IS NULL
THEN 'yes' ELSE 'no' END

I'd love to know if there's another clever way to accomplish this in a terse manner so I don't have to remember to separate by type family.


texts:
We've got a customers table (Who doesn't?), containing many records that are, from a business perspective, duplicates. I've been able to create an SSIS package to perform fuzzy grouping, and report on potential duplicates.

Now, suppose I want to do this kind of analysis just as somebody is entering a new customer. The idea would be to perform a fuzzy lookup on customer name (and possibly some other basic info like postal code), and show potential duplicates prior to proceeding to the customer creation form.

The obvious problem here is that the fuzzy grouping and lookup components are part of SSIS. If I wanted to run those on-demand, I'd have to do something insane like putting the search terms in a staging table, running the SSIS package, waiting for it to complete, and fetching the results from an output table. It would be slow, painful, and have severe concurrency problems.

So, the other idea was to use full-text indexing. In experimenting with it, it looks like it won't be suitable. It can't catch subtle misspellings of customer names, or names that differ in "Company" vs. "Corporation" vs. "Co.", or "Anderson" vs. "Andersen", and other such variations.

Is there something that will allow for the flexibility of fuzzy grouping/matching from T-SQL? I can tell a fuzzy lookup to save the tokens, but it looks like I would still have to reimplement most of the matching algorithm to make use of them.


texts:
I am learning about database, I need to know something about the foreign key in MySQL.

Consider the following two tables.

UserGroupType.
UserGroup.

UserGroupType:

UserGroup:

In this table the foreign key is defined as follows,

foreign key   (GroupType_id) references UserGroupType(GroupType_id)

I just want to know whether the name of the field in UserGroup can be changed? consider the following altered statement.

GroupType:

foreign key (Type_id) references UserGroupType(GroupType_id)

Is it necessary that both the field names must be same?

How to set the default value for datetime?

Thanks in advance, sorry if it is very basic things.


texts: MySQL 5.7 - B-Tree indexes, performance and fragmentation

texts:
I have a large table with a timestamp column and it holds a very large amount of data (a few million rows).

Whenever running a filtered query by range on the timestamp column for this table, depending on the size of the range, it takes a considerable amount of time (2+ seconds). This column has an index created for it which would lead me to believe that the query return should be faster.

I noticed that by destroying the index and re-adding it, the query performs easily 40% better.

Bellow is a simple example of how the table is structured:

create table foo (
  id int(11) not null auto_increment,
  fk_id int(11) not null,
  datecolumn timestamp,
  primary key (id, fk_id),
  key idx_timestamp (datecolumn)
  constraint fk_fk_id foreign key (fk_id) references bla (id)
 ) engine = InnoDB;

Any reason why the re-creation of the index would make the query run faster and how can I make this as part of a maintenance ? I understand that MySQL InnoDB default index is BTree and it can get messy but it should have a way to optimize or re-organize these so the query performance is not so bad.

Note: I have ran

optimize table foo;
flush tables;

And these didn't seem to solve anything related to the index.

When you use explain it shows some indexes being used but mainly, it shows the type ranged and under this type that there is most of number of rows analyzed to return just a few of them.

fk_id is part of the where clause, needs to be, it is also part of a composite key which means that it is deterministic to return the results.

The query is basically this:

select id, fk_id, datecolumn, bar.id, bar.name, bar.type
    from foo
    join bar on bar.id = foo.fk_id
   where foo.datecolumn between '2016-01-01 05:00:00' and '2017-01-01 04:59:59'
   and foo.fk_id in (1, 2, 4)

Even if the query was a simple select where datecolumn between range I get the same result.


texts: Does anyone knows how the filestream folder structure works?

texts:
I am trying to find the database/table that these files correspond to. In the filestream data store they are always stored under two GUID.

Do they correspond to object id / column id? how to i translate back to know what tables and column they correspond to?

├───23617e6f-8c5d-4dba-9c99-6088a05a3e61
│   └───b644ba19-a592-4e1a-9d6f-e3914875a587
│           00000024-00000678-0009
│           00000024-00000688-0006
│
├───423da63b-0de5-4095-900f-2c5bfdbaf190
│   └───ccb1c3e9-8de5-4773-80fa-d9aa4a383b27
│
└───6e99731c-fc79-4859-a13e-ec2706e7ef60
    └───dd9465af-a05c-4636-ab73-c95be4a84254
            00000024-00000590-0009


texts: Fetch Only new data from auto-updating online sql database

texts:
I have an online MySQL (presto) database which keeps on auto-updating (i.e., new rows keep getting added to the tables(views, to be exact) every day). I want to fetch the data from it every day but don't want to refetch the data that I have already fetched before.

I am using python3 for extracting the data from the online host database as:

from pyhive import presto
import numpy as np
cursor=presto.connect().cursor()
sql = 'select * from schema.table'
cursor.execute(sql)
rows = np.array(cursor.fetchall())
cursor.close()

Doing this takes time and fetches all the data (new data as well as already fetched data). I am storing the data in rows variable in a csv file afterward.

Is there any way to get only the new data (id is the primary key in the table)?

Thanks in advance.


texts: Doing a point in time restore with CDC enabled; Possible?

texts:
I discovered this week, the hard way, that the database restore options NORECOVERY and KEEP_CDC are mutually exclusive.  So, this begs the question.  How do you do a database restore, keeping CDC intact, with both full and log backups?

Doing research on MSDN etc. I cannot find any documentation on restoring a database with KEEP_CDC using any other option than a single full db restore with RECOVERY specified.

I was able to find one attempt that did the full and subsequent logs without the keep_cdc option waiting until the final log.  Only then was the table brought online with the RECOVERY and KEEP_CDC option.  The result was a corrupt CDC schema as demonstrated here.

If the intent is to KEEP_CDC on restore are you truley limited to a full backup only or is there a mechanism similar to the attempt above to keep it intact during multi-file restors on a server other than the original?


texts: Query on char with selector with N prefix is very slow

texts:
I have a table with a CHAR(36) "id" column, which is a clustered primary key. It has multiple other columns, including an "imdbId" column, which is an INT.

When I update using

UPDATE Movies SET imdbId=12 WHERE id = N'012e1892-872e-4bb0-804b-f70ff59689ae';

the query takes forever. And I don't really understand why. Can't SQL Server convert the Unicode string (as it does not contain special characters) and then use the index?

If I remove the N prefix, the query returns instantly.

The problem is, of course, that an existing application does all of its queries with the N prefix. Is converting my "id" column to NCHAR my best option?


texts: SQL logins Audit for SQL Server 2005/2008

texts:
For the Security Audit of SQL servers 2005/8 R2,

We are looking for a method which can list us SQL accounts which have not changed password for last X days. For a single login we can do it via LastSetTime in Loginproperty. But for many servers there are more than 80 logins. How should we extract this information for existing logins?

Also, for those we found who have not changed password in last X days, I believe we need to have existing password. Now the problem is, that existing password cannot be found as was set up way back and password change policy was not marked. What should we do in such cases where we need to change the password of more than 80 logins in a go, as we have plenty of servers to perform this activity?

Thanks!


texts: How to create an user with permission to do everything but drop databases?

texts: If it is easier, the user can be denied only deletion of one specific database instead of any database.


texts: unable to insert utf8mb4 characters in mysql 5.6

texts:
recently i was facing problem in inserting emoji and other 4 bytes character in my mysql database then i found that my mysql version was 5.1 and it does not support utf8mb4 so i upgraded to mysql 5.6 which supports utf8mb4 and in my collation i have the option to select utf8mb4.but when i try to insert directly in table row through phpmyadmin i am  getting this error
refered this link also

       http://dba.stackexchange.com/questions/59126/set-value-of-character-set-client-to-utf8mb4

 1 row affected.
      Warning: #1366 Incorrect string value: '\xF0\x9F\x9A\x8A k...' for column 'rajulp' at row 1

i googled and found that this is because the database is not supporting utf8mb4 characters and then i followed few tutorials in internet to update my.cnf file and restarted mysql server as well  as  server.

       https://mathiasbynens.be/notes/mysql-utf8mb4

i tried almost all possible combination but again when i type this in sql it throws maximum as utf8 because of that i am unable to insert eboji and several 4 bytes character.

 Punjab me 1Train k niche 100 Sardar aa gaye..

output of below command in mysql

 SHOW VARIABLES LIKE 'char%'

  character_set_client  utf8
     character_set_connection   utf8
   character_set_database   utf8mb4
    character_set_filesystem    binary
    character_set_results   utf8
   character_set_server utf8mb4
  character_set_system  utf8
     character_sets_dir /usr/share/mysql/charsets/

please guide


texts: Improving remake of primary key performance

texts:
I'm using a MySQL 5.7 hosted on AWS that has a table with about 500,000,000 rows and 124GB in size.
I want to drop the existing primary key (id) and create a new composite primary key of 2 columns:
ALTER TABLE tableName DROP PRIMARY KEY;
ALTER TABLE tableName ADD PRIMARY KEY(column1, column2);

I have already done this query in a testing environment with the same data, and it works fine.
The problem is that running those 2 queries took over 20 hours, and that is unacceptable in a production scenario.
The way I understand it, InnoDB runs these queries by creating a copy of the table (tmp table) and applying the changes to it - and swapping that table.
I've tried scaling it to a db.m5.24xlarge instance, in the hope that the 384GB memory would allow InnoDB to store the entire tmpTable in memory, but it's not working very well - it seems the like the memory is not being utilized.
I have also set the following configuration values:
tmp_table_size = 18446744073709551615;
max_heap_table_size = 1844674407370954752;
innodb_online_alter_log_max_size = 184467440737095;


texts: Oracle sys SYSDBA audit information to a report

texts:
I am trying to produce a monthly report on Audit.

I am able to query the DBA_AUDIT_TRAIL, for all other users and no issue in that.

I wanted to include the SYS/SYSDBA activities in this report and I know this is not recorded in SYS.AUD$ table but its recorded as an operating system files called .AUD. I know this is done to make sure that DBAs don't go and modify these data.

Because this is just a reporting how can I get this details to my report?

Is there a way I can query the operating system files eg. XXXX.AUD ?

Thanks


texts:
I created a table with name "vegs" and inserted values just like this.

    id  name            parent
--------------------------------------
    1   fruits          0
    2   Apple           1
    3   Orange          1
    4   Grape           1
    5   Green Apple     2
    6   Red Apple       2

I want to recursively fetch all parents of the child  "6".


texts: How to LOCK/BLOCK selected row in table to be ReadOnly?

texts:
Is it possible to LOCK completely or make READ-ONLY where id = value in MariaDB?
or add some trigger locking if someone is trying to update selected row ?

Some people says:
"Trigger is not a good way to prevent update" .... well, just because MySQL/MariaDB allow you to do it and it is the easiest solution I decided to use/create one as follows:

CREATE TRIGGER `my_trigger` BEFORE UPDATE ON `my_table`
    FOR EACH ROW IF NEW.locked THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You are not allowed to change
    this record, please contact your_administrator';
    END IF

As long as it does the job .... i'm happy.
If there is any "against" please let me know.


texts: Pass the unix variable to the ddl command inside a pl/sql block

texts:
I would like to create a db link dynamically, using the input given in the unix session. i.e. the remote db name and db instance would be given by the user, in unix, which are VAR1 and VAR2. Now, from the current unix session, I want to connect to one sql database and then to create the db link to the another database using the input values. When I tried the below script, this is creating the db link successfully, but the values of VAR1 & VAR2 are not taken as 'xxx' and 'yyy', instead these are read as it is, i.e. the username is 'VAR1' and host name is 'VAR2'. How do I make sure that the values of these variables are passed instead of these? Thanks in advance.

VAR1="xxx"
VAR2="yyy"
sqlplus aaa/bbb @try.sql $VAR1 $VAR2

--Then below is my SQL (try.sql)

SET SERVEROUTPUT ON
DECLARE
VAR1 varchar2(100):='&&1';
VAR2 varchar2(100):='&&2';
BEGIN
execute immediate 'CREATE DATABASE LINK  try_lnk CONNECT TO VAR1 IDENTIFIED BY VAR1 USING ''VAR2''';
END;
/
exit


texts: Oracle DBID what is logic to create oracle DBID?

texts:
Oracle DBID what is logic to create oracle DBID ? Does it ensures uniqueness across multiple database in different host.?

Can I relay on DBID to identify database among 100 database in the organization ?

Thanks,


texts: execute R script using a postgresql trigger:

texts:
This is my first question here, so excuse me if it does not look well organized:

I want to call a R script from a trigger (postgresql) so this script will be executed after an INSERT on my table (with a specific condition on the insert), here is what I did (based on an example that I found here):

1- I created a table to save the result

CREATE TABLE trigger_test (
    tt_id serial PRIMARY KEY NOT NULL,
    command_output text
);

2- I created the function:

CREATE OR REPLACE FUNCTION execute_rscript()

RETURNS TRIGGER

LANGUAGE plpgsql

AS $BODY$

BEGIN

    IF NEW.idniveau = 5 THEN

   COPY trigger_test (command_output) FROM PROGRAM 'Rscript /home/.../script.R';

END IF;
    RETURN NULL;
END;
$BODY$;

3- the trigger

CREATE TRIGGER trigger_execute_rscript
    AFTER INSERT
    ON event_table
    FOR EACH ROW
    EXECUTE PROCEDURE execute_rscript();

but it does not work:

  ERROR:  program "/home/.../script.R" failed DETAIL:  child process
  exited with exit code 1 CONTEXT:  SQL statement "COPY trigger_test
  (command_output) FROM PROGRAM '/home/.../script.R'"

any ideas please to solve this problem?


texts: SQL Server: ODBC Linked Server to Oracle does not return all rows

texts:
Let's say we have one SQL Server instance on one side:

SQL Server 2008 R2 SP3
Oracle 11.2 client installed with OLEDB Drivers for ODBC

.. and an Oracle database instance on another side:

Oracle Database 11.1.0.7 PSU 24
InstanceName: MERCURE

I created two database links on the SQL Server instance:

MERCURE (using ODBC Server DSN and MSDASQL Provider)
MERCURE_OLE (using OraOLEDB.Oracle)

On the Oracle instance (with instance name MERCURE), I created a schema and table as follows:

CREATE USER JEL_PERF_TST identified by "XXX";
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE to JEL_PERF_TST;
ALTER SESSION SET CURRENT_SCHEMA=JEL_PERF_TST;

-- select DEFAULT_TABLESPACE from dba_users where username='JEL_PERF_TST';
-- USERS

-- create a table for testing (Taken from https://use-the-index-luke.com/sql/example-schema/oracle/3-minute-test)
CREATE TABLE TestTbl (
  id          NUMERIC NOT NULL,
  date_column DATE,
  a           NUMERIC,
  b           NUMERIC,
  text        VARCHAR(255),
  state       CHAR(1),
  PRIMARY KEY (id)
);

 INSERT INTO TestTbl
 SELECT level
      , SYSDATE - level
      , MOD(level,1234)
      , TRUNC(DBMS_RANDOM.VALUE(1, 10))
      , DBMS_RANDOM.STRING('l', 20)
      , DECODE(MOD(level, 5), 'X', 'A')
   FROM dual
CONNECT BY level <= 50000;

So, basically, I have 50.000 rows in my table.
Now, let's run the following queries on SQL Server instance:

First, let's activate statistics:

set statistics io on;
set statistics time on;

Then, run a first query using OraOLEDB.Oracle provider:

SELECT * FROM [MERCURE_OLE]..[JEL_PERF_TST].[TESTTBL];

Here is what I got:

SQL Server parse and compile time:
   CPU time = 32 ms, elapsed time = 274 ms.

(50000 row(s) affected)

 SQL Server Execution Times:
   CPU time = 2199 ms,  elapsed time = 5769 ms.

Performances aren't pretty good (more tha 8.5 ms for a single row) but it doesn't matter here. We have 100% of rows returned.

Let's now run the query using OPENQUERY and same Linked Server:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 103 ms.

(50000 row(s) affected)

 SQL Server Execution Times:
   CPU time = 2542 ms,  elapsed time = 6716 ms.

Same here, performances are bad but we have 100% of rows returned.

Now, let's run the exact same query, but using MSDASQL provider:

select * from openquery(MERCURE,'select * from JEL_PERF_TST.TestTbl');

It results in:

 SQL Server parse and compile time:
   CPU time = 15 ms, elapsed time = 107 ms.

(**49901** row(s) affected)

 SQL Server Execution Times:
   CPU time = 1357 ms,  elapsed time = 3932 ms.

Timing is better, but where are my 99 missing rows ??

I've tried many changes to my ODBC connection descriptor (Enable/disable result sets, raise the Fetch Buffer Size, Use MSDTC or not, etc.). Nothing worked.

Any clue ?


texts: Can I create a new column with default and a foreign key as a metadata-only operation?

texts:
It is possible to create a new column with a default value as a metadata-only operation (not a size-of-data operation):

ALTER TABLE dbo.MyReallyBigTable
ADD MyThingId INT NOT NULL
    DEFAULT 0;

But is it possible to also create such a column that references another table as a metadata-only operation?

ALTER TABLE dbo.MyReallyBigTable
ADD MyThingId INT NOT NULL
    DEFAULT 0
    REFERENCES dbo.MyThing(MyThingId);

In theory it's possible because there should only be one value to check in the referenced table. However, in my example, the foreign key creation seems to be a size-of-data operation.


texts: MariaDB: point in time backup recovery with binary log using position

texts:
Following the below sequence of commands i expected the recovery of backup and subsequent point in time recovery using binary log. However, the database was restored to backup only:

1)

CREATE TABLE `CUSTOMERS` (
  `ID` int(11) NOT NULL,
  `NAME` varchar(20) NOT NULL,
  `AGE` int(11) NOT NULL,
  `ADDRESS` char(25) DEFAULT NULL,
  `SALARY` decimal(18,2) DEFAULT NULL,
  PRIMARY KEY (`ID`))

2) INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (70, 'Hardik70', 27, 'Bhopal', 8500.00 );

3) mysqldump db -uroot -ppass --master-data=2 > out.sql

4) INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (80, 'Hardik80', 27, 'Bhopal', 8500.00 );

5) mysql -uroot -ppass < out.sql

6)

mysqlbinlog --start-position=368315 /var/log/mysql/bin.123456 \
             | mysql -u root -p

/var/log/mysql/bin.123456 and 368315 was taken from out.sql because the dump was done using --master-data=2 option

i expected    select * from CUSTOMERS; to return

| 70 | Hardik70 |  27 | Bhopal    | 8500.00 |
| 80 | Hardik80 |  27 | Bhopal    | 8500.00 |

but only | 70 | Hardik70 |  27 | Bhopal    | 8500.00 | is returned

How to do point-in-time backup recovery correctly?


texts: Count the nodes in a binary tree structure

texts:
I need to count the left and right nodes in a binary tree structure (output grouped by joiningDate), given a particular starting node parent id (pid).

The tree is stored in the table shown below:

For example using pid = 4 you would get 2 cid (5 and 11 ) then you would use those as the new pid (5, 11). When cid is null or the full tree has been traversed count all the placement = L and placement = R. Other placements like "M" should be ignored.

Illustration:

Expected output for a selected starting node of 4:

+-----------+-------------+-------+
| placement | joiningDate | Total |
+-----------+-------------+-------+
| L         | 2015-02-02  |     3 |
| R         | 2015-02-02  |     1 |
| L         | 2015-08-21  |     4 |
| L         | 2015-12-12  |     1 |
+-----------+-------------+-------+


texts:
This MySQL table had me perplexed for a moment:

mysql> desc quux;
+---------------------------+-----------------------+------+-----+---------+----------------+
| Field                     | Type                  | Null | Key | Default | Extra          |
+---------------------------+-----------------------+------+-----+---------+----------------+
| foobar                    | int(11)               | NO   |     | NULL    |                |
(...)

mysql> show create table quux;
(...)
`foobar` int(11) NOT NULL,
(...)

Since the foobar field was never created with a DEFAULT clause, it gets automatically assigned DEFAULT NULL. However, at a first sight this looks like contradicting the fact that it was also defined as NOT NULL.

Then I realized that this schema aims to force to insert a value (and a non-NULL one) in foobar when adding a new record.

Is this an acceptable way to do so, or there are better ways?


texts: How to list all postgres tables in one particular schema

texts: Using the psql command line tool, how do I list all postgres tables in one particular schema


texts: Security : SQL Server Object Access From One Schema to another schema

texts:
Scenario:
I have denied permissions from table1 for USER1.
DENY SELECT,INSERT,UPDATE,DELETE ON OBJECT :: dbo.table1 TO USER1

Instead, I have allowed access to a view(vw_table1) which will selects from table1 and filter some data using where clause. The idea is, users won't be able to see entire data of the table. To support insert, update and delete, I have created instead of triggers.

The above scenario works fine when Table1 and VW_table1 are on same schema.

Now, I have a requirement to move the view only in a different schema (sec.vw_table1). After moving the view when I do "select * From sec.vw_table1" It gives access denied error when the view tries to access dbo.table1.

How can we achieve this in SQL Server? I don't want to give access to dbo.Table1 to User1. But should work when it is called from sec.vw_table1.

Any help would be highly appreciated!!


texts: SQL Server 2008 R2 SP2 Execution Plan not getting missing index suggestions

texts:
I have several SQL Server 2008 R2 SP2 that have server collation Latin1_General_BIN.  These instances will not give me the missing index suggestion with performing an execution plan.  I have other instances that do work.  I can use the following TSQL code to test my results
By performing a execution plan on the select statement I get the missing index suggestion with my instances that do not have collation Latin1_General_BIN.  The instances that have collation Latin1_General_BIN do not get the suggestion.  PeopleSoft requires me to have this collation on my instances.  This worked months ago, but now does not work.  I have reset the SQL Servers and re-installed MSSMS on my desktop.

USE tempdb;
GO

CREATE TABLE dbo.smorg(blamp INT);
GO

INSERT dbo.smorg(blamp) SELECT n FROM
(
  SELECT ROW_NUMBER() OVER (ORDER BY c1.object_id)
  FROM sys.all_objects AS c1, sys.all_objects AS c2

) AS x(n);
GO

SELECT blamp FROM dbo.smorg WHERE blamp BETWEEN 100 AND 105;


texts: does SQL automatic backup runs even the machine/server is offline

texts: I am wondering if the SQL jobs like automating the database backup runs even the machine/server is offline?


texts: SQLCMD does not print Rows affected in log file

texts:
I am calling stored procedure using SQLCMD which will insert data into multiple tables. And output of that procedure is captured in log file using -o. But, i can't see Row(s) affected any where in the log file. And same when I am executing procedure I am getting Row(s) affected.

Is there any other way to print Row(s) affected in log file along with print statement. I have tried using SET NOCOUNT OFF as well but no use.


texts: How to normalize table with multiple similar columns (day1, day2, day3...)?

texts:
I have a database named BusinessPass and a website that users can use to create Business Passes. Below is the web form.

Date | Time From | Time To | Location

The problem is the table design. Below is my proposed table design.

BusinessPassId, UserID, Date1, From1, To1, Location1, Date2, From2, To2, Location2.... and so on. There would be a maximum of up to 31 days. So I would have 126 columns total. Is there a better way to design this?


texts: I am working with pg_stat_activity in postgres. It is working and I can see queries in this view after clicking the play button in datagrip. However, some queries I definitely executed (see screenshot, and look at the first query) are not shown at all. I need statistics of them (so really all my queries). How to ensure pg_stat_activity shows all my queries? Do I need to add additional settings?


texts: create a stored procedure if it doesnt exist using a ddl in db2

texts:
My requirements are,

I want to create a ddl script which will check if a stored procedure exists on DB, if yes then drop it and recreate it.

What I tried is,

IF EXISTS (select procname into Migration_procname from sysibm.sysprocedures where procname like 'GIAM_PRIVILEGE_MIGRATION') THEN
    DROP PROCEDURE ITIMUSER.GIAM_PRIVILEGE_MIGRATION;
ELSE
CREATE PROCEDURE ITIMUSER.GIAM_PRIVILEGE_MIGRATION()
SPECIFIC ITIMUSER.GIAM_PRIVILEGE_MIGRATION
LANGUAGE SQL
BEGIN

......
......
update/select statements
......

END
@

which didn't work.
so I even tried with creating another procedure which is doing same task still no success.

The error message was

  DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "IF EXISTS (select procname" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "". SQLSTATE=42601

Any pointers will be helpful.

NOTE: I am using DB2/LUW 9.5


texts: How to change the path of data during database restore + SQL Server 2008

texts:
I am trying to restore a big database in SQL Server 2008 R2. The database is about 700GB in size. My SQL Server is installed in C drive which has only 100GB of space left in it. I have the .bak file on the D drive from which I need to restore the database. Is there a way I can restore this database without increasing the space on C drive? (The D drive has 5TB of space and I would want SQL Server to access/place the data from/on D drive)

Any input will be highly appreciated.


texts: Not displaying special characters imported from a file

texts:
The scenario is a SQL Server instance, a database which is data fed using BULK INSERT operations mainly, and some of the texts inserted contain special characters like ñ because I am working in a Spanish environment.

So, after the first little tests I realize these special characters are not being displayed correctly when I run a simple select, so I start checking everything I can think of:

File encoding: the file to be bulk-inserted has the right encoding: ANSI
Database encoding: the database has the right encoding (thank god): select collation_name from sys.databases where name='DBNAME'; results in SQL_Latin1_General_CP1_CI_AS

Latin1: charset used. This suits me fine enough
General: nothing really interesting here
CP1: this means that it uses the codepage 1 which, in short, means the codepage 1252 <=> codepage used for the encoding WIN-1252 which is fairly similar to Latin1
CI: case insensitive
AS: Accent sensitive, so á is different from a

export data to file and check: file encoding is ANSI but the data is not displayed correctly, there are no special characters, instead I find some other characters that make the text hard to read.

With these tests, I conclude that the data is not being stored correctly, and that is why it is not being displayed and exported correctly. Almost every solution I have found on the internet suggests using nvarchar instead of varchar for string datatype fields, but that does not solve this scenario. What is it that is sabotaging my insertions?


texts: Any Issues, concerns on having a Stored Procedure in Subscriber Database

texts:
I have a database (ms-sql) that is replicated(transactional) to a subscriber.

I'd like to add a stored procedure to the subscriber only

Is this possible?
Does it affect replication?
Any potential Issues?
Under what circumstance could the sp be erased/cleared?


texts: Should I favor using a param or a scalar function result within a SELECT?

texts:
I have a select statement in SQL Server that must compare a large set of rows against a single calculated date.

The calculation itself is negligible; just looking for the prior Monday. Given that the column this calculated date will be matched against is indexed, is there any difference whether I pass in the calculated date from application code as a parameter or use a scalar function to calculate the date?

Said another way: should I favor using a param or calling a scalar function, or are they basically equal in this scenario?


texts:
The image below is my ham-fisted attempt to design a database of cabinets. I'm new to this and this is simply an exercise to help me understand database structure.

My goal is not to create an inventory/order database that relates to client purchases. I want to create a database that would allow me to populate products based on style, material, joinery, or etc... In other words, if a customer visits the website and selects to view cabinets of a certain category then all the cabinets associated with that category will populate in an array. It will display an image and beneath that a title. For example:

From what I've researched attributes should define the columns of the table, but many of the cabinets have multivalued attributes. I'm starting to believe I've overcomplicated the design. I added a few text blocks to hopefully clarify the purpose of the table. Can someone with experience provide some constructive feedback?  Thanks!


texts: Know when MySQL server was slow shutdown?

texts:
I use MySQL 8.0.

How I can know when MySQL server was slow shutdown?

I start mysql server

/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/my.cnf --user=mysql

I shutdown mysql server

/usr/local/mysql/bin/mysqladmin --defaults-file=/usr/local/mysql/my.cnf -pmypassword shutdown

Maybe this information is written in log?


texts: How can i make month appear with two digits SQL Server

texts:
how can i make monthid column values appear as  example: 201901, not 20191.

INSERT INTO [dbo].[table2](accountno, monthid, active)
SELECT accountno, CONCAT(YEAR(InsertionDate), month(insertiondate)), active
FROM [dbo].[table1]