texts: How to select and order by id and get multiple results?

texts:
I have one database clients and two tables: business and categories.

The site is a classified ads placement, business contains:

Field            Type          Collation           Null    Key     Default          Extra   Privileges                       Comment
---------------  ------------  ------------------  ------  ------  ---------------  ------  -------------------------------  ---------
id               int(11)       (NULL)              NO      PRI     (NULL)                   select,insert,update,references
name             varchar(255)  latin1_swedish_ci   NO              (NULL)                   select,insert,update,references
category_id      int(11)       (NULL)              YES     MUL     (NULL)                   select,insert,update,references

And this is category

Field   Type          Collation          Null    Key     Default  Extra   Privileges                       Comment
------  ------------  -----------------  ------  ------  -------  ------  -------------------------------  ---------
id      int(11)       (NULL)             NO      PRI     (NULL)           select,insert,update,references
name    varchar(255)  latin1_swedish_ci  NO              (NULL)           select,insert,update,references

Now, there are thousands of records in business but only 10 records in category.

I need to fetch around 100 records from business but their category_id should be from 1-10

In other words, the 100 records would be like this

id 1 category_id 1
id 2 category_id 1
id 11 category_id 2
id 21 category_id 3

I have tried these two methods, but they don't work

first attempt

SELECT `id`, `name`, `category_id` FROM business GROUP BY category_id ORDER BY category_id DESC LIMIT 100;

second attempt

SET @random := (SELECT FLOOR(1 + (RAND() * 10)));
SELECT `id`, `name`, `category_id` FROM business GROUP BY @random ORDER BY category_id DESC LIMIT 100;

The above examples don't work, because I only get one record back.


texts: Getting "sqlcmd sqlcmd: command not found" in Linux?

texts:
Whenever I run sqlcmd I get "command not found"

sqlcmd -S localhost -U SA -P '<YourPassword>'
sqlcmd: command not found

How can I resolve that?


texts: SQL - use variable as column in SELECT statement and WHERE clause

texts:
Can any one please support me with below problem

I want to use Variable column name in Select Statement.
But when I give variable - its giving out put of Column Name itself

Note : My data in column is INT but Column Heading is Varchar.

Please refer attached Image for Code & Results ( Expected & Current )

Code :

Alter Procedure dbo.dayplanv00
(
@dt as varchar(255) = '[15-04-2017]',
@fg as nvarchar(255)= 'XC 1 IEC'
)
as
begin
Select [15-04-2017] as dt from RollingPlanWK15 where FNGD = 'XC 1 IEC'
END

Execute dbo.dayplanv00


texts:
I got this wild idea to fix a date format issue i've been having.

The idea is to use column info to act as a template for the date format since mm/dd/yy can get really confusing for the database since I always use YYYY-mm-dd but my users like to mix things around.

The thing is that I have some trouble to figure out exactly how the SQL would look like.

My idea is something like this:

Tablename: USER
Col1, Col2, Col3, date_template

And the other info:

Tablename: activity
item, quantity, price, date, time

Yes, I know that it could be fixed in the code of the page but since I'm not the creator of the site I won't go into fiddle around with the code until I got some time to make a new page.


texts: Is it possible to have different authentication mode for SQL Server Database Engine and corresponding SQL Server instance?

texts:
I have installed the x64 SQL Server 2008 R2 Express with default settings and run MBSA 2.3 Microsoft analizer (using default settings too). Tried on clean Windows 8 installation (not 8.1) and Windows 7 with the same result. It shows three SQL Server instances:

MSSQL10_50.SQLEXPRESS
SQLEXPRESS
SQLEXPRESS (32-bit)

I don't understand why there are three of them (SQL Server Configuration Manager shows only one). For the first, authentication mode is Windows, for the rest two - mixed. Here is the question about such multiple instances was asked and the answer is that "MSSQL10.TEST_DB is the instance ID for the SQL Server Database Engine of the instance, TEST_DB", so in my case, it seems that MSSQL10_50.SQLEXPRESS is the instance ID for SQL Server Database Engine  of the SQLEXPRESS instance.

I have two questions:

How can it be that SQL Server Database Engine instance has different authentication mode than corresponding SQL Server Instance?
Why 32-bit instance reported although I installed only 64-bit version?

Also, this question seems to be related to this issue, but there is no answer :(.

UPD: corrected the question, now it should be clearer

UPD2: SQL Server 2008 Discovery Report:

UPD3: MBSA Report screenshots:

UPD4: MBSA Report for SQLEXPRESS (32-bit) instance:


texts: SQL query with Like operator with Leading Wildcards

texts:
I have a query as follows:

select top (30000) * from table1 where licenseplate like '%as43bv%'

I have an index on licenseplate but the query will not hit the index due to leading wildcard.

Since the column queries license plate numbers full text search wont come to any benefit we are searching on some letter or number rather than words

The above query fetches millions of rows and brings the CPU usage around 100%.

Is there any efficient way to rewrite the query and make use of the indexes.


texts:
I have been trying to troubleshoot an issue I have detected on a UAT SQL Server, there is some resource usage even if the server is not being used...

On Activity monitor I have an average of 5% CPU usage (Real SQL Server Usage double checked with DMVs) and also a 20mb/s disk IO, this has been the same for 2 days now...

I have found that the only process that is doing IO Operation is the ghost cleanup...

After reviewing what data file is doing the IO Operation I found that I have 1500Reads/sec (NO WRITE OPERATIONS) on one Database where some users were doing load tests days ago...

What can I do with this, will this process finish? Is there anything I can do?

EDIT

After taking some time on troubleshooting this I finally found that after disconnecting the Database from the Availability Group GHOST_CLEANUP started to really free up space on my Database, took 4 hours for him to free 80GB of Storage. After connecting the Database to the Availability Group GHOST_CLEANUP starts to not free space again... Weird.


texts: Inserting filtered data from one table to another

texts:
I need to find the number of employees who are not in all departments.

I have one more table T2 in which same department column is present. Now I want to insert only those values of department which are not in T1.Department for those employees whose name doesn't appear in all 37 departments.

I'm using MySQL.

Do I need to make any procedure for this?

Reference to : Need to find the number of employee who are not in all department

Tables

create table T1 (empname, department)
create table T2 (department)


texts:
I want to transfer data from an old DB2 system to a new Oracle database.

How should I go about doing this?


texts: How do I merge two rows only if there is the TagWidgetIds are same but the tagValues are different?

texts:
I have this stored procedure which generates the following output.

The query which generates the output from the stored procedure is as follows:

SELECT tat.[agent-id],
               tat.[agent-guid],
               tat.[agent-name]  AS [AgentName],
               tw.TagWidgetID,
               tw.TagWidgetName,
               t.TagID,
               core.fnInitialCapitalization(t.DisplayName) AS TagName,
               tt.TagTypeID,
               tt.TagTypeName,
               tdt.TagDataTypeID,
               tdt.TagDataType,
               tu.TagUnitID,
               tu.TagUnit,
               --CASE
               --     WHEN itv.TagIndex IS NULL THEN 0
               --     ELSE itv.TagIndex
               --END               AS [TagIndex],
               ISNULL(itv.TagIndex, 0) AS [TagIndex],
               --CASE
               --     WHEN itv.ValueIndex IS NULL THEN 0
               --     ELSE itv.ValueIndex
               --END               AS [ValueIndex],
               ISNULL(itv.ValueIndex, 0) AS [ValueIndex],
               --CASE
               --     WHEN itv.[Value] IS NULL THEN 'N/A'
               --     ELSE itv.[Value]
               --END               AS [TagValue]
                ISNULL( itv.[Value], 'N/A') AS [TagValue]
        FROM   @tmpAgentTags tat
               LEFT OUTER JOIN tag.ItemTagValue itv
                    ON  itv.FkTagID = tat.[tag-id]
                    AND itv.FkItemID = tat.[agent-id]
               INNER JOIN tag.Tag t
                    ON  t.TagID = tat.[tag-id]
               INNER JOIN tag.TagWidget tw
                    ON  tw.TagWidgetID = t.FkTagWidgetID
               INNER JOIN tag.TagType tt
                    ON  tt.TagTypeID = t.FkTagTypeID
               INNER JOIN tag.TagDataType tdt
                    ON  tdt.TagDataTypeID = t.FkTagDataTypeID
               INNER JOIN tag.TagUnit tu
                    ON  tu.TagUnitID = t.FkTagUnitID
               INNER JOIN item.Item i
                    ON i.PkItemID = tat.[agent-id]
        WHERE i.FkItemTypeID NOT IN (5,8)

How can  I join the output rows only if the TagWidegetIds are same but the TagValues, TagIndex and ValueIndex columns are differnt? please advice


texts: Three Node Cluster that Allows For Two Failures

texts:
We are creating a three-node three-instance cluster with SQL 2008 R2 (not cutting edge, but that is what the application supports) and think it should be able to maintain quorum with two physical nodes down.  What is the best way to configure this?

My manager has been pushing for a witness server that acts like an additional quorum vote on top of a node and disk majority.  That gives us a vote per node, a vote for the quorum disk, but is it possible to setup a witness server that receives a vote?

I thought a witness was for mirroring, which we have no plans to implement on this cluster.  I have been told that adding a fourth node is not a possbility, even if it is only a passive node.


texts:
I'm configuring a job to get the SPID of an offending process and kill it. I'm using a mix between the great sp_Whoiscative from @AdamMachanic and joining with sysprocesses on login name and SPID and session_id values as those are supposed to be the same (or I'm wrong and then that's my error?).

I'm getting something I don't understand: for a same SPID from sysprocesses I'm getting different session_id values from whoisactive results. What is incorrect here?

Here is the code I'm using. The filter to get values 10s before current datetime is because the whoisactive is running with the parameter @deltainterval that takes about 10s to execute on each run.

USE master;

--  Log information about current running processes to table Log_WhoIsActive
    EXEC [master].[dbo].[sp_WhoIsActive]
        @get_full_inner_text = 1,
        @get_plans = 2,
        @get_outer_command = 1,
        @get_transaction_info = 1,
        @get_task_info = 2,
        @get_locks = 1,
        @get_avg_time = 1,
        @get_additional_info = 1,
        @delta_interval = 2,
        @sort_order = '[start_time] DESC',
        @destination_table = 'Log_WhoIsActive_tests';

--compare date from sysprocesses with data gathered on sp_whosiactive
SELECT  dt.collection_time, sp.spid, dt.[dd hh:mm:ss.mss], sp.loginame,
        dt.login_name, dt.session_id
FROM    sys.sysprocesses sp JOIN Log_WhoIsActive_tests dt
            ON sp.loginame = dt.login_name
WHERE   sp.loginame = 'my_login_name'
        AND sp.status = 'runnable'
        AND sp.spid > 50
        AND dt.collection_time > DATEADD(ss, -10, GETDATE());


texts: pg_dump freezes on windows service application

texts:
I have a system running over Tomcat which executes a backup batch job every day, it calls  pg_dump.exe from Java and stores its output to an SQL file.

When I'm executing Tomcat from the shell, it works perfectly, but when I start Tomcat as a Windows service the pg_dump.exe process stays there doing nothing, the SQL file is never written and the dump is never performed.

Is there any additional parameter or configuration required for Windows services? because it works without problems on a Unix-box.

Update: I'm using a Java class to call the operation.

Process p = new ProcessBuilder(
          "C:/postgres9/bin/pg_dump.exe",
          "-U", "myuser",
          "-h" , "localhost",
          "mydb").start();
storeToFile( p.getInputStream() );

Specs:

Tomcat 7.0.23
Postgresql 9.1
Windows 2008


texts: Restore from ibd files where is no frm files

texts:
I had an Ubuntu 18.04 with mysql 5.7. I upgraded it to Ubuntu 20.04 with mysql 8.x. But this 8.x not was compatible with my Drupal 7 and I tried to downgrade. After this I tried with a 5.7 docker image and this show the database but not the tables inside.
In the /var/lib/mydatabes directory I see a lot of .idb files, but nothing else. Is it possible to recover the database tables? And how?
Thanks.


texts:
I´ve 2 questions. I am using a T-SQL Server 2014.

When creating a table with a filtered index on a T-SQL server, it is a must-have to set QUOTED_IDENTIFIER to ON. Why is this so?

I´ve some SPs which have set quoted_identifiers to OFF, my question is, if I call the SP with QUOTED_IDENTIFIER ON, is it possible to inject these with values which have doublequotes inside?

The SP makes an insert and just uses the parameters. As I understand it, it should not take an effect if QUOTED_IDENTIFIER is set to ON or OFF.

This is my first SP which calls the second:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

DECLARE @PreviousVersion bigint

IF @EntryTime IS NULL
BEGIN
    SET @EntryTime = GETUTCDATE()
END

IF @ID = 0 BEGIN
    EXEC dbo.GetNextDataID 'Object', @ID OUTPUT
    SET @PreviousVersion = 0
END
ELSE BEGIN
    SET @PreviousVersion = @Version
END

EXEC dbo.GetNextVersion 'Object', @Version OUTPUT

EXEC dbo.Insert_Object
    @ID,
    @Version,
    @PreviousVersion,
    @deleted,
    @Parent

The second looks like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET NOCOUNT ON;

IF ISNULL(@ID, 0) = 0
BEGIN
    RAISERROR('Die ID ist 0 oder leer.', 11, 1)
    RETURN
END

IF ISNULL(@Version, 0) = 0
BEGIN
    RAISERROR('Die Version ist 0 oder leer.', 11, 2)
    RETURN
END

IF @EntryTime IS NULL
BEGIN
    SET @EntryTime = GETUTCDATE()
END

UPDATE dbo.ObjectTable
SET ID = @ID

INSERT INTO dbo.ObjectTable
            ( ID
            , Version
            , PreviousVersion
            , deleted
            , Parent)
        VALUES
            ( @ID
            , @Version
            , @PreviousVersion
            , @deleted
            , @Parent)

If I add the filtered index without setting QUOTED_IDENTIFIER to ON, it raises an error.


texts:
I've deleted database from PostgreSQL and after this I can do nothing.
Here what i receive with createuser

$ createuser -D new_user
createuser: could not connect to database postgres: FATAL:  role "postgres" does not exist

And this when try to run psql

$ psql
psql: FATAL:  role "postgres" does not exist

I've tried above with root, postgres, custom users. Still same output

Also I searched for pg_hba.conf like this
 find / -name 'pg_hba.conf'
and got this

/var/lib/postgres-xc/DN2/pg_hba.conf
/var/lib/postgres-xc/DN1/pg_hba.conf
/var/lib/postgres-xc/CN/pg_hba.conf
/etc/postgres-xc/coordinator/CN/pg_hba.conf
/etc/postgres-xc/datanode/DN2/pg_hba.conf
/etc/postgres-xc/datanode/DN1/pg_hba.conf
/etc/postgresql/9.3/main/pg_hba.conf

I've changed connection method to trustfor localhost and IPv4 in /etc/postgresql/9.3/main/pg_hba.conf.

What should I do get rid of this error?

Does connection method in other files above should be edited too?


texts: Retrieve Indices of Associative Array as Collection

texts:
In PL/SQL, suppose I have some associative array defined as follows:

declare
       type a_arr_t is table of PLS_INTEGER index by PLS_INTEGER;
       a_arr a_arr_t;

I then, sparsely, populate the array as follows:

begin
      a_arr(1)   := 2;
      a_arr(10)  := 4;
      a_arr(100) := 6;
end;

Is there some operator or function that can give me the indices of the array, (1,10,100) as a collection of some sort, something like indices of in a forall statement?


texts: How to clear gcache pages in Percona PXC

texts:
I have a 4 node cluster(percona PXC) and one of the nodes have more than 150 gcache disk files each of size ~128 MB. I would like to purge or flush them so that the server user fresh gcache disk file.

I am not sure if the node restart will do it for me.

Thank You.


texts: Can we reuse a .dmp file(expdp) to perform import using data pump utility

texts: I'm an accidental DBA. We use Oracle 11g database. I had to do a database refresh in the test environment. I created a .dmp file using expdp command and performed an import using impdp command a week ago. I had to make some schema changes in test and then the department decided to revert to how it was before we made changes to the schema. The department wants me to perform impdp using the old .dmp file that I created a week ago. Can I re-use this file to do the import? Can the .dmp files be reused as many times as we can?


texts: SSIS 2008 Package Configurations being ignored

texts:
With the change to package configurations in 2008 compared to 2005 when I specify /ConfigFile something.dtsConfig on the command line, variables defined in the package are keeping their design-time values instead of using the settings from the config file.

I'm not quite sure I understand HOW to get the external config file to be used at all.  I've read articles that say that only design-time configurations that are set will overwrite the load of the external file.  Does this mean I can change the variables to blank strings and then they will get overwritten?  I can't delete the variable completely!  What about integers?

I've seen articles that mention turning OFF using package configurations in the package.

I can use the SSIS Package Editor or an XML editor to change the configuration file path in the package, and then it will use that file's settings "last" (regardless of the external /ConfigFile option), but I don't want to be changing the package.  I want one package with Test.dtsConfig and Production.dtsConfig and be able to swap back and forth without changing the package.

What is the recommended way to do this now?


texts: How much time would it take for synchronizing between master and slave in Mysql on AWS RDS?

texts:
I implemented Master Slave for RDS Mysql database. The sync time for 1 GB was very less. Can I predict time for 20GB to synchronize?
Master DB

Slave DB

Can I also predict the time and load on the Master DB while synchronizing 20GB in production using IOPS read, write graphs?


texts: How to modify the below script to take tables and its sizes for a particular databse to send via mail to a recipient?

texts:
SELECT top 10
    t.NAME AS TableName,
    (SUM(a.total_pages) * 8)/1024.0/1024.0 AS TotalSpaceGB
    FROM sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name
ORDER BY
    TotalSpaceGB desc


texts: Iterate through all non-system databases and empty them

texts:
On SQL Server 2012...I have multiple databases on a server used for training purposes.  Periodically, these need to be cleared for a fresh start.  When quantities of databases were small I would use Adam Anderson's code to remove all objects and manually change the USE statement.  With 200+ databases now, I'd rather not.  Looking to automate this process.  I'm trying to use sp_MSforeachdb, without luck so far.  Any suggestions?  Thanks in advance!

DECLARE @command nvarchar(max)

SELECT @command ='IF EXISTS
(
    SELECT 1
    FROM sys.databases
    WHERE name = ''?''
    AND name LIKE ''learndb%''        --we have learndb1, learndb2, etc...
)
BEGIN

DECLARE @stmt nvarchar(max)
DECLARE @n char(1)
SET @n = char(10)

select @stmt = isnull( @stmt + @n, '''' ) +
    ''drop procedure ['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.procedures

select @stmt = isnull( @stmt + @n, '''' ) +
''alter table ['' + schema_name(schema_id) + ''].['' + object_name( parent_object_id ) + '']    drop constraint ['' + name + '']''
from sys.check_constraints

select @stmt = isnull( @stmt + @n, '''' ) +
    ''drop function ['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.objects
where type in ( ''FN'', ''IF'', ''TF'' )

select @stmt = isnull( @stmt + @n, '''' ) +
    ''drop view ['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.views

select @stmt = isnull( @stmt + @n, '''' ) +
    ''alter table ['' + schema_name(schema_id) + ''].['' + object_name( parent_object_id ) + ''] drop constraint ['' + name + '']''
from sys.foreign_keys

select @stmt = isnull( @stmt + @n, '''' ) +
    ''drop table ['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.tables

select @stmt = isnull( @stmt + @n, '''' ) +
    ''drop type ['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.types
where is_user_defined = 1

select @stmt = isnull( @stmt + @n, '''') +
    ''drop trigger ['' + schema_name(schema_id) + ''].['' + name + '']''
from sys.objects
where type = ''TF'' exec sp_executesql @stmt END'

EXEC sp_MSforeachdb @command


texts: SORT_SEQUENCE is wrong in as400 library indexes. Can this cause performance issues?

texts:
I have 4 AS400s running JDEdwards.

Lately a performance issue occured as described here: AS400 Performace issue

Although i am not an expert in AS400 , i tried to find out the cause. I think it came down to this:

I run the following SQL in all my systems:

select sort_sequence,index_schema,count(*) from qsys2.sysindexstat group by sort_sequence,index_schema

In 3 of my 4 systems all of them have sort_sequence="BY HEX VALUE"

In my 4 problematic system many indexes have sort_sequence="*LANGIDSHR"

I start to believe that this can cause performance problems as the query optimizer may leave these indexes unused and create temp indexes.

I wish somebody has an idea on where to go on from now and how.

Is there any specific setting that make all the indexes have default sort_sequence?

All my indexes are created massively so this could not happen by mistake.


texts: Setup standalone with all the data from cluster, mongodb

texts: I have a mongo cluster with 3 nodes(a primary, a secondary, and an arbitrary) with replication enabled for primary and secondary. For some reason would like to take down the cluster and have a standalone instance(in arbitrary). Since data is not available in arbitrary, have copied data from cluster nodes to the path(/data/db). Removed the replication factor in mongod.conf and started mongo instance. But couldn't see the databases. What else is required? OR do I have to get mongo-dump and mongo-restore? If so, doing so for 200GB is a costly and time-consuming process. Help me with this.


texts: Transactional replication using articles filters and delete operations

texts:
I have a problem with my transactional replication when I use articles, filters, and delete operations. When I insert the new data into the publisher all the data that meets the specified filter is inserted to the subscriber - everything works fine. When I delete some data from the publisher, not all data that meets the specified filter is deleted from the subscriber and I don't know why.

I have three servers:

A – publisher (Microsoft SQL Server 2012)
B – distributor (Microsoft SQL Server 2012),
C – subscriber (Microsoft SQL Server 2012).

There are six tables in my publisher database. Each table has a primary key and some of them have foreign key as shown below:

I want to replicate only data for the first manufacturer (manufacturer ID=1) from the publisher to the subscriber, so I use these filters:

SELECT <published_columns> FROM [dbo].[Manufacturer]
WHERE [dbo].[Manufacturer].ID IN (Select M.ID from [dbo].[Manufacturer] AS M where M.ID = 1)

SELECT <published_columns> FROM [dbo].[Catalog]
WHERE [dbo].[Catalog].ID IN (Select C.ID from [dbo].[Catalog] AS C
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)

SELECT <published_columns> FROM [dbo].[Cars]
WHERE ID IN (Select CA.ID from [dbo].[Cars] AS CA
INNER JOIN [dbo].[Catalog] AS C ON C.ID = CA.CID
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)

SELECT <published_columns> FROM [dbo].[CarParts]
WHERE ID IN (Select CP.ID from [dbo].[CarParts] AS CP
INNER JOIN [dbo].[Cars] AS CA ON CA.ID = CP.CarID
INNER JOIN [dbo].[Catalog] AS C ON C.ID = CA.CID
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)

SELECT <published_columns> FROM [dbo].[Parts]
WHERE ID IN (Select P.ID from [dbo].[Parts] AS P
INNER JOIN [dbo].[CarParts] AS CP ON CP.PID = P.ID
INNER JOIN [dbo].[Cars] AS CA ON CA.ID = CP.CarID
INNER JOIN [dbo].[Catalog] AS C ON C.ID = CA.CID
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)

SELECT <published_columns> FROM [dbo].[PartDetails]
WHERE ID IN (Select PD.ID from [dbo].[PartDetails] AS PD
INNER JOIN [dbo].[Parts] AS P ON P.ID = PD.PartID
INNER JOIN [dbo].[CarParts] AS CP ON CP.PID = P.ID
INNER JOIN [dbo].[Cars] AS CA ON CA.ID = CP.CarID
INNER JOIN [dbo].[Catalog] AS C ON C.ID = CA.CID
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)

When I insert the new data into the publisher's CarParts, Parts, or PartDetails tables, all the data that meets the specified filter (manufacturer ID=1) is inserted at the subscriber.

When I delete data from the publisher CarParts, Parts, or PartDetails tables, not all data that meets the specified filter (manufacturer ID=1) is deleted from the subscriber - it only deletes data from the CarParts table, not from Parts or PartDetails.

The same filter is used for insert and delete operations. What could be the problem? Maybe it is a Microsoft SQL Server 2012 bug?


texts: How to get the sum with more then one table

texts:
I have 3 tables as shown in the link sqlfiddle
When I take the sum with two tables I get good result
    sqlfiddle

but when I add 3rd table and it's query I get unexpected result as this

http://sqlfiddle.com/#!2/f9858a/12

How can I resolve this problem pls ?


texts: Storing and searching user location using latest MySQL 5.7 spatial features

texts:
I want to use MySQL spatial features to store user location.
I have read about MySQL spatial geometry data type.
According to this article this works very well in MySQL 5.7
http://mysqlserverteam.com/mysql-5-7-and-gis-an-example/

Have anyone used it and would recommend it? Is InnoDB good engine type for this kind of think? I want this to be efficient, fast and reliable.

What would be the best table structure to store the location?
I thought I could do something like that.

CREATE TABLE locations (
    id INT(11) AUTO_INCREMENT,
    location geometry NOT NULL, # I am guessing this will store the latitude and longitude,
    city varchar(100) not null,
    county varchar(100) not null,
    postcode TINYTEXT,
    PRIMARY KEY(id),
    SPATIAL KEY i_location (location)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE user_location (
    id INT(11) AUTO_INCREMENT,
    userId INT(11) NOT NULL,
    locationId INT(11) NOT NULL,
    PRIMARY KEY(id)
) ENGINE=InnoDB;

Any thoughts on that?

Then when I will need to use this location for example find users near me within 50 miles.
I was thinking to use something similar to examples given in this article http://mysqlserverteam.com/mysql-5-7-and-gis-an-example/

Would that work fast when the database gets huge?

I want to build this solid as I don't want to worry about the speed as the database grows.

Any ideas will be appreciated

EDIT:

I forgot to add that I only want to store UK location


texts:
In a SQLITE3 DB, I have a table A with a column 'word' containing search words that partially match a column in another table B.

I'd like to SELECT all rows FROM table B WHERE B.name LIKE %any_of_the_words_in_a.word%';

My first idea was to use a REGEX, but I can't get it to work with REGEX and a subquery.


texts:
I am trying to run any command from SQL Server Express from the command line using sqlcmd. Here is an example.

sqlcmd -U myname-P mypwd -S mysys/SQLEXPRESS -d MASTER -Q "Select count(*) from sys.databases"

I keep getting the error below. It appears to be trying to connect using named pipes, but I have configured SQL Express to use TCP.  How can I get SQLCMD to not use the named pipes?

Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Named Pipes Provider:
    Could not open a connection to SQL Server [67]. .
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server :
    A network-related or instance-specific error has occurred
    while establishing a connection to SQL Server. Server is not found or not accessible.
Check if instance name is correct and if SQL Server is configured to allow remote connections.
For more information see SQL Server Books Online..


texts: I am not seeing Management Folder in SSAS but I do see it in SSMS. What am I doing wrong?

texts:
So I am trying to turn on extended events for my SSAS multidimensional cube. However, when I am logged into SSAS (with full admin priv) I only see Database & Assembly folders as seen below.

I am following Microsoft's guide online for this from the following link:
Monitor Analysis Services with Extended Events

They show the following options under Microsoft Analysis Sevices in this guide as can be seen below:

Any help would be appreciated as I really want to use this data.


texts:
I have many strings in the database like:

Original

www.youtube.com/embed/-qTiYA1WiY8&index=8&list=PLPNcLSfYNC3Dw535smncyHRCBRmdf1Bti

Desired

www.youtube.com/embed/-qTiYA1WiY8

So final string can be converted to:

I can do this using PHP, but I want to do it with a MySQL query. I tried to use:

UPDATE MyTable
    SET StringColumn = REPLACE (StringColumn, 'SearchForThis', 'ReplaceWithThis')

It needs to be a general solution that can replace string from & and before " if exist in the string. I am not able to get the proper result.


texts:
I am working on scrambling PII tags in CLOB columns such as <FirstName> or <LastName> or <SSN_Number>.

so I am using the below query to achieve the same.

 UPDATE /*+PARALLEL(8)*/ HI.E_SUB
  2     SET SUED_DATA =
  3             UPDATEXML (
  4                 xmltype (SUED_DATA),
  5                 '//*/FirstName/text()',
  6                 TRANSLATE (
  7                     EXTRACT (xmltype (SUED_DATA), '//*/FirstName/text()'),
  8                     'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',
  9                     'ZYXWVUTSRQPONMLKJIHGFEDCBAzyxwvutsrqponmlkjihgfedcba0876543219')).getClobVal ();

The above query runs for really a long time(more than 6 hours) and at last fails with below error.

 UPDATE /*+PARALLEL(8)*/ HI.E_SUB
  2     SET SUED_DATA =
  3             UPDATEXML (
  4                 xmltype (SUED_DATA),
  5                 '//*/FirstName/text()',
  6                 TRANSLATE (
  7                     EXTRACT (xmltype (SUED_DATA), '//*/FirstName/text()'),
  8                     'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',
  9                     'ZYXWVUTSRQPONMLKJIHGFEDCBAzyxwvutsrqponmlkjihgfedcba0876543219')).getClobVal ();
               xmltype (SUED_DATA),
               *
ERROR at line 4:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00217: invalid character 3 (U+0003)
Error at line 181
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1

I am not aware of what could be wrong with it. Is there anyway to optimize the same? What is that I am missing?


texts: Does postgresql pl/lua language have luajit performance?

texts:
I am trying to use PostgreSQL for a financial system and I need the best performance I can use, so I decided to use stored procedures for managing all the math and logic of the system. I have read that luajit have performance similar to C but without the "dangers" of using C.

Fortunately PostgreSQL has lua as one of its procedural languages but I don't know if this pl/lua is luajit or only lua (and therefore the performance associated to them).

So if PostgreSQL uses lua performance I would use pl/v8 instead but if pl/lua uses luajit I will stay with pl/lua.


texts: Update an Oracle DB with a max number for the null rows

texts:
I have an Oracle db and I need to update some columns and one of them is an number that has to be some kind of an id - max number on that column. If I use something like this:

update ss_chest ssc
    set SSC.NUMAR_CHEST = 'RO' || (select lpad(max(SSC.NUMAR_CHEST),5,'0')
                                   from ss_chest ssc where SSC.NUMAR_CHEST is not null)
where SSC.NUMAR_CHEST is null;

And the result is always the same and it updates my table with the same max NUMAR_CHEST. How can I update it always with the max number? (I have to concatenate my number with RO in front and with leading 0 on 5 spaces)
Thanks!


texts: How fast should I expect postgresql to be?

texts:
I'm assessing postgresql performance for a time-series application, particularly for inserts. I'm running the speed test script from sqlalchemy at https://docs.sqlalchemy.org/en/13/faq/performance.html#i-m-inserting-400-000-rows-with-the-orm-and-it-s-really-slow. You can ignore the ORM stuff, it's the penultimate "core" line which shows the performance.
I was expecting to see from 10 to 100k writes per second as per e.g. (https://docs.timescale.com/latest/introduction/timescaledb-vs-postgres):

However, I'm instead getting the following:

So that's 358 writes per second! Compared to 83k writes per second for sqlite.
That seems really slow to me, but I'm getting this performance consistently across a Windows installation, Docker on Windows, native Ubuntu and Docker on Ubuntu.
Are my expectations just misaligned with reality, or am I doing something wrong?
EDIT:
To clarify, I'm not using Timescale DB's hyptertables feature yet, just plain vanilla PostgreSQL tables. I am using the TimescaleDB docker installation for my docker tests (though not the native test). Specifically, I'm invoking
docker run -d --rm -p 5433:5432 -e POSTGRES_PASSWORD=password timescale/timescaledb:latest-pg12


texts: Postgres JSON output using specific column as JSON key


texts: "Unrecognized configuration parameter: qreplace_function" trying to backup a database

texts:
I am new to PostgreSQL and I am trying to backup a database with the pg_dump utility.

I am using this command:

/opt/PostgresPlus/9.4AS/bin/pg_dump -U (username) -f /opt/PostgresPlus/9.4AS//pgbackup/db_name.sql dbname -p 5432

and I am encountering this error:

  pg_dump: [archiver (db)] query failed:
  ERROR: unrecognized configuration parameter "qreplace_function"

Can anyone please help me understand what I am doing wrong?


texts: Achieve PostgreSQL hstore functionality in MySQL

texts:
Please suggest any MySQL 5.5+ Engine that can support collection of key-value pairs be stored in any column of a table.

I don't know if this feature is available out of the box in MySQL like PostgreSQL equivalent to HSTORE which is immensely useful and adds some of NoSQL benefits ..

Example in PostgreSQL -

SELECT 'a=>1,a=>2'::hstore;
  hstore
----------
 "a"=>"1"

source - http://www.postgresql.org/docs/current/static/hstore.html


texts: clustered and covering index ignored on delete statement. Table scan occurs

texts:
Why would SQL Server 2005 find it more efficient to perform a table scan instead of using the available clustered index on the primary key (and only the primary key)?

DISCLAIMER:
There is also a non-clustered, non-unique index on the primary key with no included columns. This is baffling to me and we've had a good office chuckle already. If this index ends up being the problem, then we know who to shoot. Unfortunately, it's a production site and I can't just rip it out but will make plans to do so if necessary.

Maybe the problem is not the mentally deficient contrary index, however...

According to Foglight PASS the following statement has been causing a scan on a table with ~10 million rows about 600 times an hour when we delete a row by the primary key:

DELETE FROM SomeBigTable WHERE ID = @ID

The table DDL:

CREATE TABLE [SomeBigTable]
(
    [ID] [int] NOT NULL,
    [FullTextIndexTime] [timestamp] NOT NULL,
    [FullTextSearchText] [varchar] (max) NOT NULL,
    CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )
) -- ...
ON PRIMARY

The clustered index constraint in detail:

ADD CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED
(
    [ID] ASC
) WITH  PAD_INDEX = OFF
       ,STATISTICS_NORECOMPUTE = OFF
       ,SORT_IN_TEMPDB = OFF
       ,IGNORE_DUP_KEY = OFF
       ,ONLINE = OFF
       ,ALLOW_ROW_LOCKS = ON
       ,ALLOW_PAGE_LOCKS = ON
       ,FILLFACTOR = 75
ON PRIMARY

The non-unique, non-clustered index on the same table:

CREATE NONCLUSTERED INDEX [IX_SomeBigTable_ID] ON [SomeBigTable]
(
    [ID] ASC
) WITH  PAD_INDEX = OFF
       ,STATISTICS_NORECOMPUTE = OFF
       ,SORT_IN_TEMPDB = OFF
       ,IGNORE_DUP_KEY = OFF
       ,ONLINE = OFF
       ,ALLOW_ROW_LOCKS = ON
       ,ALLOW_PAGE_LOCKS = ON
       ,FILLFACTOR = 98
ON PRIMARY

There is also a foreign key constraint on the [ID] column pointing to an equally large table.

The 600 table scans are about ~4% of the total delete operations per hour on this table using the same statement. So, not all executions of this statement cause a table scan.

It goes without saying, but saying it anyway...this is a lot of nasty I/O that I'd like to send packing.


texts:
Following is the create table statement that is working as expected. But if I have a table without any partitions and if I try add partitions, it seems that I can not add more than 16 values in it.

   CREATE TABLE `mypart` (
      `part_id` int(11) DEFAULT NULL
    ) ENGINE=MyISAM
    PARTITION BY LIST (part_id)
    (
    PARTITION Jan10 VALUES IN (1,2,3,4,5) ENGINE = MyISAM,
    PARTITION Mar10 VALUES IN (734201,734202,734203,734204,734205,734206,734207,734208,734209,734210,734211,734212,734213,734214,734215,734216, 734217) ENGINE = MyISAM
    );

All the following statements should execute with success and create a table as shown above.

drop table mypart;
create table mypart (`part_id` int) ENGINE=MyISAM ;
alter table mypart partition by list(part_id) (PARTITION Jan10 VALUES IN (1, 2, 3, 4, 5));

mysql> alter table mypart add PARTITION (PARTITION Mar10 VALUES IN (734201, 734202, 734203, 734204, 734205, 734206, 734207, 734208, 734209, 734210, 734211, 734212, 734213,  734214, 734215,734216, 734217  ) ) ;
ERROR 1657 (HY000): Cannot have more than one value for this type of LIST partitioning

At this point, the second alter table statement does not work and hence I can not have the 17 values for Mar10 partition as shown in the first example. If I delete the last value 734217 then the alter table statement is successful!

mysql> alter table mypart add PARTITION (PARTITION Mar10 VALUES IN (734201, 734202, 734203, 734204, 734205, 734206, 734207, 734208, 734209, 734210, 734211, 734212, 734213,  734214, 734215,734216));
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

Is there some limit in the alter table statement that does not applies to create table statement ?


texts: Remove duplicate comparisons in postgres?

texts:
I have a polygon layer named scale_polygons_v4. I want to get ids of overlapping polygons. I have tried the code below:

select distinct s1.id,s2.id from scale_polygons_v4 s1
inner join scale_polygons_v4 s2
on s1.id!=s2.id and s1.kind=s2.kind and st_overlaps(s1.geom,s2.geom)

The results are correct, but with duplicate comparisons it returns for example:

10029161,10011031
10011031,10029161

There are multiple such cases I only want to get one comparison.


texts: Is it possible to show DML that is locking some table?

texts: I already saw here how I can list my locked objects. Suppose I, using PL/SQL Developer(pink cylinder logo one), run select * from someTable for update and didn't commit nor rollback. Now using the query from the linked question, I was able to discover 'someTable'. I want to retrieve the full query. Is is possible?


texts: AWS RDS PostgreSQL: Significant query runtime difference between two (identical?) instances

texts:
TL;DR: Solution

The problem was that one instance hat magnetic storage attached and was therefore significantly slower.

Original Question

I have two identical AWS RDS PostgreSQL 10.6 databases running on separate t2.medium instances in different VPC subnets, but within the same availability zone. They represent our staging/production environment, respectively. The only difference between both instances I can see is that the production instance is multi-AZ, while the staging instance is not.

When I run the simple query

select * from auva_orders

on a table with ~18 mio. rows each, I see dramatic runtime differences:

staging: 1:30m - 1:50m
production: ~0:30m

Both tables are continuously populated with new entries by a Kafka consumer, contain nearly the same number of rows and have identical indices:

Indexes:
    "auva_order_pk" PRIMARY KEY, btree (topic_partition, topic_offset)
    "auvaOrderCompanyCountryOrderdate" btree (company_code, country_code, orderdate)

If I EXPLAIN (ANALYSE, BUFFERS) the queries for each environment, I get the following results:

staging:

EXPLAIN (ANALYZE, BUFFERS) select count(*) from auva_order;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=421029.42..421029.43 rows=1 width=8) (actual time=97541.306..97541.306 rows=1 loops=1)
   Buffers: shared hit=52389 read=273887 dirtied=129
   ->  Gather  (cost=421029.21..421029.42 rows=2 width=8) (actual time=97540.845..97542.098 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=52389 read=273887 dirtied=129
         ->  Partial Aggregate  (cost=420029.21..420029.22 rows=1 width=8) (actual time=97538.854..97538.855 rows=1 loops=3)
               Buffers: shared hit=52389 read=273887 dirtied=129
               ->  Parallel Seq Scan on auva_order  (cost=0.00..401278.57 rows=7500257 width=0) (actual time=4.692..95822.212.
. rows=6000544 loops=3)
                     Buffers: shared hit=52389 read=273887 dirtied=129
 Planning time: 0.069 ms
 Execution time: 97542.148 ms
(12 rows)

production:

EXPLAIN (ANALYZE, BUFFERS) select count(*) from auva_order;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=417818.52..417818.53 rows=1 width=8) (actual time=30899.683..30899.684 rows=1 loops=1)
   Buffers: shared hit=60023 read=263042 dirtied=1
   ->  Gather  (cost=417818.30..417818.51 rows=2 width=8) (actual time=30898.544..30900.360 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=60023 read=263042 dirtied=1
         ->  Partial Aggregate  (cost=416818.30..416818.31 rows=1 width=8) (actual time=30897.146..30897.147 rows=1 loops=3)
               Buffers: shared hit=60023 read=263042 dirtied=1
               ->  Parallel Seq Scan on auva_order  (cost=0.00..398067.64 rows=7500264 width=0) (actual time=0.312..29168.159.
. rows=6000545 loops=3)
                     Buffers: shared hit=60023 read=263042 dirtied=1
 Planning time: 0.070 ms
 Execution time: 30900.420 ms
(12 rows)

I have no idea what causes the differences in runtime. Our staging instance might be under heavier load during the current development phase, but I don't think this explains a factor of 3-4 in runtime. The CPU credits of the burstable AWS EC2 instances are "full", so there is no shortage of CPU power in any of the instances. Any ideas what might be the cause?


texts:
We have an application that access the database (Sql server 2014 enterprise edition). The application calls a stored procedures to access the database. Everything was working fine until recently start sending the following error and stop the applications. Restarting the app fix the issue temporarily but facing the same error latter.

  Error: A transport-level error has occurred when receiving results from the server. (Provider: TCP Provider, error: 0 - The specified network name is no longer available.)

I did a lot of research which most of them pointing out as a network issue but could not find anything to actually solve the problem.  Do anyone know what change I should have to make on the database side to resolve this issue.  I highly appreciate any suggestions.


texts: Implement row-level security as a user table or millions of roles?

texts:
PostgresSQL 10.4 is our backend database for a consumer-facing app with millions of users. When implementing row-level security so each user can only access their own data, is it best practice to use a separate Postgres role for each user or to have a shared role with a user table?

The documentation always uses examples with separate Postgres roles, whereas e.g. this blog describes how to do it using a user table.


texts: Is it possible to convert .trn (transaction log) file to human readable form

texts:
I have used below SQL statement to get the transaction information done on 2015-10-26, but the information not in human readable form.

**

SELECT * FROM fn_dump_dblog
(NULL,NULL,N'DISK',1,N'D:\Inventrax369\RT\TransactionFilesForMani\FalconWMS_RT_Release_27102015.trn',
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, DEFAULT)
where convert(date,[Begin Time]) = convert(date,'2015-10-26')

**

Looking forward to receive vital suggestions.


texts: MySQL function: Get string/number separated by some place holder

texts:
I have the following product table

| product_id    | sku   | created_at    |
|------------   |-----  |------------   |
| 145687        | abc   | 2017-08-08    |
| 22456         | xyz   | 2017-09-01    |
| 6553214       | asd   | 2017-09-05    |

I want to get output as 1/4/5/6/8/7 with SQL

SELECT someMySQLFunction(product_id) FROM product WHERE sku = 'abc';

How to achieve this in MySQL?


texts: Force update statements to take page locks in the same order?

texts:
I have two update statements on the same table that take page locks in different orders. This leads to deadlocks.

Process A owns an update lock on page 1, and requests an update lock on
page 2.
Process B owns an update lock on page 2, and wants an update
lock on page 1.

How can I force both statements to take page locks in the same order?


texts:
I have 1000 CSV files. Each CSV file is between 1 and 500 MB and is formatted the same way (i.e. same column order). I have a header file for column headers, which match my DynamoDB table's column names. I need to import those files into a DynamoDB table. What's the best way / tool to do so?

I can concatenate those CSV files into a single giant file (I'd rather avoid to though), or convert them into JSON if needed. I am aware of the existence of BatchWriteItem so I guess a good solution would involve batch writing.

Example:

The DynamoDB table has two columns: first_name, last_name
The header file only contains:  first_name,last_name
One CSV file looks like

:

John,Doe
Bob,Smith
Alice,Lee
Foo,Bar


texts: Can someone guide me how to compare tables with SSIS

texts:
I have two tables in two different databases in two different servers.

Both tables have an orderid column, and a given value for orderid references the same entity in both tables.

I want to check whether each value for orderid in either table has a matching entry in the other table, and write a record to table3 based on this information. table3 has the same basic columns as table2, with the additon of a status column.

Server1
Database1
Table 1
orderidproductpricequoted pricediscounted price

Server2
Database 2
Table 2
orderidcreatedonmodifiedon

Table 3
orderidcreatedonmodifiedonstatus

The status can be matching,not matching,only in table 1,only in table 2,error. If in both orderid is same the status will be matching, if it's there only in table 1 it becomes only in table 1, if found only in table
 2 it's only in table 2 not sure whether not matching & error are relevant in this scenario.

I have crafted an sql for it, just let me know how to use it in SSIS

insert into Table3 (orderid,createdon,modifiedon,status)
select
    COALESCE(t1.orderid,t2.orderid) as orderid,
    COALESCE(t1.CREATE_DATE,t2.CREATE_DATE) as createdon,
    COALESCE(t1.UPDATE_DATE,t2.UPDATE_DATE) as modifiedon,
    case when t1.orderid is null then 'only in table 2'
         when t2.orderid is null then 'only in table 1'
         when t1.orderid = t2.orderid then 'matching'
         when t1.orderid != t2.orderid then 'not matching'
         else 'error'
    from Table1 t1
    full outer join Table2 t2
    on t1.orderid = t2.orderid


texts: Performance of large transactions and concurrency?

texts:
If I have a multi-million row table and I run a transaction that updates 50k rows, what are the performance implications of this?

Assuming it's indexed correctly, it shouldn't take long, but what rows are locked and how is the usage of that table affected?

Are rows being updated during the transaction able to be read
after the transaction starts and before it finishes?
Are rows not being updated during
the transaction able to be read after the transaction starts and before it finishes?
If another transaction starts trying to change rows that are being changed by
a previously unfinished transaction, will that transaction fail at
start or after it tries to commit (assuming conflict)?

My question is for Postgres 9.3; I assume there are variations.


texts:
Sorry if this question does not make sense, I have no experience with databases, I just stumbled upon this task.

I need to attach an mdf to a database. I have the mdf file stored in my local computer. When I click attach in SSMS, and then add, I can only browse the disks and folders of the SQL Server, while the file is in my local computer. How can I move the mdf file to the sql server?


texts: How to use timescale-bitnami image on Windows?

texts:
Since there are issues with collations in the default alpine-based timescaledb images, I tried to use the bitnami-image. But I get an error on startup.

docker run ^
-p 5432:5432 ^
-v tsdb-bitnami:/bitnami/postgresql ^
--env POSTGRES_USER=tsdbu ^
--env POSTGRES_DB=tsdbu ^
--env POSTGRES_PASSWORD=tsdbu ^
--name timeseriesdb ^
timescale/timescaledb:1.5.0-pg11-bitnami

This fails with:

ERROR:  permission denied to create extension "timescaledb"
HINT:  Must be superuser to create this extension.

Is there some other parameter that we must pass? i.e. I've tried POSTGRESQL_POSTGRES_PASSWORD (ref= bitnami postgres docs), but this did also not work.

Versions:

Windows 10 64-bit
Docker version 19.03.4, build 9013bf5
Timescale container: timescale/timescaledb:1.5.0-pg11-bitnami

Full Output:

postgresql 12:31:34.41
postgresql 12:31:34.41 Welcome to the Bitnami postgresql container
postgresql 12:31:34.41 Subscribe to project updates by watching https://github.com/bitnami/bitnami-docker-postgresql
postgresql 12:31:34.41 Submit issues and feature requests at https://github.com/bitnami/bitnami-docker-postgresql/issues
postgresql 12:31:34.42 Send us your feedback at containers@bitnami.com
postgresql 12:31:34.42
postgresql 12:31:34.42 INFO  ==> ** Starting PostgreSQL setup **
postgresql 12:31:34.45 INFO  ==> Validating settings in POSTGRESQL_* env vars..
postgresql 12:31:34.46 INFO  ==> Initializing PostgreSQL database...
postgresql 12:31:34.46 INFO  ==> postgresql.conf file not detected. Generating it...
postgresql 12:31:34.47 INFO  ==> pg_hba.conf file not detected. Generating it...
postgresql 12:31:35.67 INFO  ==> Starting PostgreSQL in background...
postgresql 12:31:36.14 INFO  ==> Creating user tsdbu
postgresql 12:31:36.15 INFO  ==> Grating access to "tsdbu" to the database "tsdbu"
postgresql 12:31:36.16 INFO  ==> Configuring replication parameters
postgresql 12:31:36.18 INFO  ==> Configuring fsync
postgresql 12:31:36.18 INFO  ==> Loading custom scripts...
postgresql 12:31:36.19 INFO  ==> Loading user's custom files from /docker-entrypoint-initdb.d ...
postgresql 12:31:36.19 INFO  ==> Starting PostgreSQL in background...
ERROR:  permission denied to create extension "timescaledb"
HINT:  Must be superuser to create this extension.
postgresql 12:31:36.20 INFO  ==> Stopping PostgreSQL...


texts: How to get output from trigger on linked server

texts:
Fair warning, I know this isn't the best way to do this, but I'm trying to extend some existing code without needing to do a rewrite.

Anyway, I've got two SQL Server 2012 installs, one local in my office and another at a remote site.  The server at the remote site has a linked server setup for my local system.

An application at the remote site inserts a record to a table on the linked server which has an INSTEAD OF trigger.  That trigger has a select statement at the end which returns a single integer value.  If I do the insert on the local box in SSMS I get the integer as output.  If I do the insert on the remote box all I get is 1 row(s) affected.

Is there anyway I can get the trigger's output passed back to the linked server or am I going to need to rewrite as a stored procedure?

TEXT BELOW HERE IS BASED ON CLARIFICATION REQUESTED IN COMMENTS

All connections are made in the security context of a single user on the remote side. The output appears as expected if I use EXECUTE AS USER on the local server to do the insert rather than do it across the link, so it isn't a permission issue with the account.

Server options are:

Collation Compatible=False
Data Access=True
RPC=True
RPC Out=True
Use Remote Collation=True
Collation Name=(blank)
Connection Timeout=0
Query Timeout=0
Distributor=False
Publisher=False
Subscriber=False
Lazy Schema Validation=False
Enable Promotion of Distributed Transaction=True


texts: How do I in "Navicat for PostgreSQL" run a simple PL/pgSQL script like I would in MS SQL Server Management Studio?

texts:
I don't want a function or anything. I just want to run/test a simple script with some PL/pgSQL features in it. Something like the following:

IF 1=1 THEN
    SELECT concat('Hello', 'World');
END IF;

I'm sure the syntax is correct, but it doesn't work in the normal "Query Editor" like I'd do it in Microsoft SQL Server. I get the following error:

[Err] ERROR:  syntax error at or near "IF"
LINE 1: IF 1=1 THEN
        ^

I'm running Navicat for PostgreSQL version 11.1.13 (64-bit) on a Windows 10 machine. select version() says the following:

PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

edit:

I just realized that this question might be the victim of the X-Y Problem. What I really want to do is to test the following way of inserting values ignoring duplicates:

BEGIN
    INSERT INTO db_table (tbl_column) VALUES (v_tbl_column);
EXCEPTION WHEN unique_violation THEN
    -- Ignore duplicate inserts.
END;

I found it here: Optimal way to ignore duplicate inserts?

I get the following error:

[Err] ERROR:  syntax error at or near "INSERT"
LINE 2:         INSERT INTO db_table (tbl_column) VALUES ('test');
                ^


texts:
Here and here seem to suggest that they are, but I've also been told (source unlocatable) that a multi-row single-statement insert will only use one thread for all writing, that to multithread it, the query should be broken up and sent to as many connections as there are cores.

The documentation states that CTEs are executed "concurrently", but none linked say explicitly in effect that "CTEs will use all available cores to process any parallel sub-statements and/or the main query".

Do CTEs always use all available cores for any parallel tasks?  If so, are there any caveats aside from what could be reasonably assumed from chaining with RETURNING?


texts: Two Oracle ORDS on one web server accessing one database

texts:
Oracle ORDS 3.0
Oracle 12c database
Red Hat Linux 6.8
Tomcat 8.0.22

Can two ORDS be deployed to one web server (Tomcat) and access the same database?
If so, how is this accomplished?

That is, I would be deploying to Tomcat an ords1.war file and an ords2.war file that both connect to the same Oracle database.

Better yet, I need two URLs that point to two different applications running on the same database.  Perhaps another ORDS is not needed.

The context root (normally just 'ords') has to be different for each application.
For example, ords1 and ords2.  Hence, the thought that there needs to be a ords1.war and ords2.war file deployed.


texts: Postgres - Select all if given parameter is null

texts:
i have a table with the below columns
name varchar(250)
type varchar(25)

is there a way for me to structure a query to return all records if the type parameter is null?
Im aware that i can do something like
select * from tbl where type = Coalesce(@param, 'a');

which will give me all records of type 'a' if i give an empty param.
But what if i want to retrieve all records if param is null?


texts: SHRINKFILE Failure - Why does increasing file size resolve it?

texts:
I am running some SHRINKFILE operations to clean up a bunch of tiny, unnecessary files in a filegroup. For one of the shrinks, the command below results in an error:

DBCC SHRINKFILE (N'myfile' , EMPTYFILE)'

  File ID x of database ID x cannot be shrunk as it is either being
  shrunk by another process or is empty

It is not empty nor being shrunk. It is being run on a database not currently in use by anyone except myself. Auto shrink is not enabled and never was. However, there were manual shrinks performed on this database on a regular basis prior to me getting my hands on it, if that matters at all.

On SQLServerCentral, a thread from a decade ago suggests adding a few MB to the file because that "resets an internal counter or switch that tells it it's not in the middle of a shrink now."

This worked - awesome. But can anyone explain with greater detail how/why this works in regard to SQL Server internals?


texts: postgresql Cannot login with created users

texts:
I am very new to postgreSQL, but quite familiar with SQL.

I have recently installed postgreSQL on Windows 10.  I ran through the startup tutorials and made a few DBs to mess around with.  I can login just fine with the postgres account with the command

psql -U postgres -d DatabaseName

However, I have thus far been unable to create a new user to log in with.

I tried the command

CREATE USER myUser WITH SUPERUSER PASSWORD 'password';

When I do this, it creates the user.  I can see this with the \du command:

Role name  |   List of Attributes                      | Member of
postgres   | Superuser, Create Role, Create DB, yada...| {}
myuser     | Superuser                                 | {}

I then try \q and logging in with my new user and get the following:

psql -U myUser -d Database
Password for myUser: password
psql: FATAL: password authentication failed for "myUser"

I have also tried createuser from cmd, this user showed up in the list of users as well, but I was again unable to login as these users.

Thank you for any help you can offer.


texts:
We are using Java EE and are making an application where in the worst case, a lot of message queue messages will come form the same User.

Therefore, we are looking on Pessimistic, SELECT FOR UPDATE style locking. Which in theory and first tests solves our problems.

Yet, we are afraid of deadlocks. Not the classical ones: User X locks A, User Y locks But more of scenarios like: system crash, network problems,... and so on. Database system locking up, for no know reason. We will use modern databases like: Oracle, MS SQL and PostgreSQL.

What we would like to know is pessimistic locking used in production and what practical problems to expect?

Thanks in advance!


texts: How to get a list of stored procedure along with their execution count in oracle 12c?

texts:
Hi I am new to Oracle 12c, though I was working on SQL Server for a long time.

Is there a way to get a list of stored procedures with their execution count in oracle 12c.

We can get it using DMV in SQL Server (various versions). But I am looking for it in Oracle 12c.


texts: How to user the Tablediff Utility with Replication Filters

texts:
I am using merge replication in SQL 2012. I am trying out the TableDiff utility to show non convergence.

I see a problem with this approach though. This is because I am using parameterised filters to filter the subscriptions.

In this instance the TableDiff utility tells me that there are missing rows in the subscription and generates the SQL to insert them. If I swap the source and destination around it generates a whole lot of delete statements to remove the records at the publication.

This is not a correct result. It should just check the rows that exist at the subscriber, or understand the filters and know which rows should be at the subscriber.

Can I use this utility in this instance? If not is there an alternative? I find it surprising that none of the documentation about this utility mentions this problem.


texts: Database size twice as large after mysql dump and re-import

texts:
After exporting and re-importing a mysql database, the size of the database on disk appears to be quadrupling in size. Any ideas why this my be?

This is occurring when importing the sql dump right back into exactly the same mysql database. To show what I mean:

Export mysql database: mysqldump -u mysql_user -p database1 > dump.sql
Import mysql database (into same mysql server, on same host): mysql -u mysql_user -p database 2 < dump.sql
Check the database size using:

SELECT table_schema "Database Name",
    sum( data_length + index_length ) / 1024 / 1024 "database size in MB",
    sum( data_free )/ 1024 / 1024 "free space in MB"
FROM information_schema.TABLES
GROUP BY table_schema;

Which then gives:

+--------------------+---------------------+------------------+
| Database Name      | database size in MB | free space in MB |
+--------------------+---------------------+------------------+
| information_schema |          0.01074219 |       0.00000000 |
| database2          |        978.73327637 |     120.00000000 |
| database1          |        284.76471329 |     217.00003815 |
+--------------------+---------------------+------------------+
3 rows in set (0.52 sec)

Note how the database 2 is 3.5 times bigger than database 1.
If I do a checksum test, I get the same checksum so the data seems to be valid. Just it's taking up much more space on the disk!

mysql> use database1

;Database changed
mysql> checksum table wp_site;
+--------------------+------------+
| Table              | Checksum   |
+--------------------+------------+
| database1.wp_site  | 2263483605 |
+--------------------+------------+
1 row in set (0.00 sec)

mysql> use database2

;Database changed
mysql> checksum table wp_site;
+---------------------+------------+
| Table               | Checksum   |
+---------------------+------------+
| database2.wp_site   | 2263483605 |
+---------------------+------------+
1 row in set (0.00 sec)

So my questions:

Why is this happening?
Is it really taking up more space on the disk, or is this somehow a mis-report?
Will database 2 have performance issues since it's much larger in size (even though the data seems to be the same)?

Note: in reality I'm copying the data over to a new server, not duplicating it into the same server. I'm using the example of the same server here to avoid any other unknowns.

Update

After running mysqlcheck -o -A, the database size did shrink a little, but not that much:

+--------------------+-----------------+------------------+------------------+
| Database Name      | data size in MB | index size in MB | free space in MB |
+--------------------+-----------------+------------------+------------------+
| information_schema |      0.00000000 |       0.01074219 |       0.00000000 |
| database2          |    568.22155762 |     358.74609375 |       6.00000000 |
| database1          |    174.76861954 |     109.99609375 |     215.00003815 |
+--------------------+-----------------+------------------+------------------+


texts: How to know if mysql server is using sha_256 or caching_sha_256 authentication plugin?

texts:
Seems like I am using an older version on Mysql Server which uses sha_256 plugin for Authentication (which is now deprecated) .Because of this, I am unable to connect to my server  with third party softwares like beekeper studio.
But I am able to connect from the terminal itself. So I went through the Mysql manual . It shows how to change the default authentication plugin , but before changing it,  I just want to know what is my default authentication plugin.  How to do that?


texts: Deny permissions for master in SQL Server

texts:
I created a new database on a SQL Server 2008 R2 instance and a login/user for it. I have set the user to db_owner so he can't view any other databases on that server but he can see and query the master

database. Is there a way to hide/deny permissions for the master database? Are there any possible vulnerabilities if I leave him to query the master database or is there a way to deny him access to the master database?


texts: How can I improve the performance of a sorted join?

texts:
This feels like such a common question, I'll understand if it is closed but if so please suggest a better place I could ask. I have the following two tables of interest:
CREATE TABLE [dbo].[Sessions]
(
    [Id] [int] PRIMARY KEY,
    [DateConnected] [datetime] NOT NULL,
    [Origin] [nvarchar](max) NULL,
    [TrackerId] [int] NULL,
    [Imei] [nvarchar](max) NULL,
    [Sim] [nvarchar](max) NULL,
    [ProtocolVersion] [tinyint] NULL
)

CREATE TABLE [dbo].[PacketTransmissions]
(
    [Id] [int] PRIMARY KEY,
    [RequestId] [int] NULL,
    [SessionId] [int] NOT NULL,
    [DateProcessed] [datetime] NOT NULL,
    [Direction] [int] NOT NULL,
    [Sequence] [int] NOT NULL,
    [Acknowledgement] [int] NOT NULL,
    [DateRecorded] [datetime] NOT NULL,
    [Version] [tinyint] NOT NULL,
    [Command] [tinyint] NOT NULL,
    [Flags] [tinyint] NOT NULL,
    [Checksum] [tinyint] NOT NULL,
    [Data] [varbinary](max) NULL
)

CREATE NONCLUSTERED INDEX [IX_TrackerId_DateConnected] ON [dbo].[Sessions]
(
    [TrackerId] ASC,
    [DateConnected] ASC
)

CREATE NONCLUSTERED INDEX [IX_SessionId_DateProcessed] ON [dbo].[PacketTransmissions]
(
    [SessionId] ASC,
    [DateProcessed] ASC
)
INCLUDE ([Direction], [Sequence], [Acknowledgement], [Command])

My most common query, and most expensive (quite often times out now) involves listing all packet transmissions for a particular tracker.
DECLARE @TrackerId INT = 10
DECLARE @StartDate DATETIME2 = '2018-03-10'
DECLARE @EndDate   DATETIME2 = '2018-03-12'

SELECT [PacketTransmissions].*
FROM [Sessions]
JOIN [PacketTransmissions] ON [PacketTransmissions].[SessionId] = [Sessions].[Id]
WHERE [Sessions].[TrackerId] = @TrackerId
AND [PacketTransmissions].[DateProcessed] > @StartDate
AND [PacketTransmissions].[DateProcessed] < @EndDate
ORDER BY [PacketTransmissions].[DateProcessed] DESC

This was good at first, but now there is a lot of data, it has slowed right down. My attempt to get the query plan today took 2 minutes, and shows that it will be using a table scan, rather than the index I created. Even when I force the index, it is still very slow.
In comparison, if I choose a session first, and search only for packet transmissions recorded within that session, the query uses the index and is incredibly fast.
My most successful attempt to speed up the query has been to order the results first by session id, then by date processed, to match the index order. While this is not technically always correct, it is acceptable. However, even this has started to time out, and I feel like there is something wrong with my understanding of how to make the JOIN faster.
What can I do to improve the performance of this query?
Querying with DATETIME variables instead of DATETIME2 has simplified the query plan, however it is still very slow.

Sessions has 265,929 rows

PacketTransmissions has 32,916,233 rows
That works out to be 123.7 packets per session, on average.

Some of the sessions are for unregistered devices, so they create a session, send between one and three packets, and then the session is rejected by the server.

I will normally be debugging a registered device, so the actual number of packets per session is considerably higher, between 300 and 5000 packets per session

Some trackers may maintain the same session for a month at a time if they have connectivity

I have in the past had a bad experience with changing the clustered index to use a non-sequential key. It results in a lot of out-of-order writes, and page splits, and the insert performance drops significantly.
The problem with the actual execution plans is that I don't want to run the database at max DTU for up to an hour, and potentially have inserts fail in the meantime.


texts: How do you copy a TDE-encrypted SQL Server database using T-SQL programatically?

texts:
I need to copy a TDE-encrypted SQL Server database to a separate, non-encrypted, SQL Server instance using T-SQL, but only by reading the schema of the source database.  This is because I'm going to use this T-SQL in an Execute SQL Task on SSIS, and it needs to be able to account for changes to the production database without further maintenance to the SSIS package.

The database is fairly large and has all types of constraints (foreign, primary, default), indexes, views, user-defined functions, etc, so I will need to make sure I grab everything.

Unfortunately because it is TDE-encrypted, I cannot use backup/restore, attach/detach, or copy-move because it will throw a "security certificate not found" exception.

Is this possible using just T-SQL?  Thank you in advance.


texts: How do I rank the total score in order of positions

texts:
How do I rank the total_score such that the highest total score has position 1, etc. If 2 or more have the same total score, then they should have the same position

CREATE TABLE `scores` (
  `id` int(11) NOT NULL auto_increment,
  `student_id` int(11) default NULL,
  `sessions_id` varchar(11) default NULL,
  `term_id` int(11) default NULL,
  `ass1` int(11) default NULL,
  `test1` int(11) default NULL,
  `proj1` int(11) default NULL,
  `ass2` int(11) default NULL,
  `test2` int(11) default NULL,
  `proj2` int(11) default NULL,
  `exam` int(11) default NULL,
  `class_id` int(11) default NULL,
  `subject_id` int(11) default NULL,
  `total_score` int(11) default NULL,
  `class_average` decimal(6,2) default NULL,
  `highest_score` int(11) default NULL,
  `lowest_score` int(11) default NULL,
  `position_in_subject` int(11) default NULL,
  `grade` varchar(2) default NULL,
  `teacher_remark` varchar(50) default NULL,
  `date_created` varchar(20) default NULL,
  `employee_id` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=85 DEFAULT CHARSET=latin1;

and here is my query

INSERT INTO `scores` VALUES ('1', '1', '2014/2015', '2', '5', '5', '5', '5', '5', '5', '50', '1', '7', '80', '51.58', '80', '41', null, 'A2', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('2', '1', '2014/2015', '2', '5', '5', '5', '5', '5', '5', '30', '1', '8', '60', '56.33', '66', '29', null, 'C', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('3', '1', '2014/2015', '2', '6', '6', '6', '6', '6', '6', '6', '1', '9', '42', '35.00', '63', '22', null, 'E', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('8', '5', '2014/2015', '2', '5', '6', '6', '6', '6', '6', '6', '1', '7', '41', '51.58', '80', '41', null, 'E', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('9', '5', '2014/2015', '2', '5', '4', '5', '4', '5', '4', '34', '1', '8', '61', '56.33', '66', '29', null, 'C', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('10', '5', '2014/2015', '2', '5', '4', '4', '5', '5', '4', '5', '1', '9', '32', '35.00', '63', '22', null, 'E', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('15', '8', '2014/2015', '2', '6', '6', '6', '6', '7', '7', '7', '1', '7', '45', '51.58', '80', '41', null, 'E', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('16', '8', '2014/2015', '2', '4', '3', '4', '3', '4', '3', '40', '1', '8', '61', '56.33', '66', '29', null, 'C', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('17', '8', '2014/2015', '2', '4', '5', '4', '4', '5', '4', '23', '1', '9', '49', '35.00', '63', '22', null, '', null, '2015-04-05', '1');

-- suggestion include a student with the same total so that calculating the rank can be QA in a proper way

INSERT INTO `scores` VALUES ('4', '2', '2014/2015', '2', '5', '5', '5', '5', '5', '5', '50', '1', '7', '80', '51.58', '80', '41', null, 'A2', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('5', '2', '2014/2015', '2', '5', '5', '5', '5', '5', '5', '30', '1', '8', '60', '56.33', '66', '29', null, 'C', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('6', '2', '2014/2015', '2', '6', '6', '6', '6', '6', '6', '6', '1', '9', '42', '35.00', '63', '22', null, 'E', null, '2015-04-05', '1');


texts: Uploading a csv and losing a connection results in lost disk space and table bloat

texts:
I have a server with 150GB of diskspace. Today I uploaded a dataset of 30GB. I cancelled the import due to internet dying, then noticed there was 29GB of space missing in the database (meaning the CSV was uploaded, but not deleted when I broke the operation). When uploading the data once again, it broke again and I lost another ~25GB. Now there isn't enough free space to upload the data.

This is hosted on AWS RDS, Postgres 10.6.

Is there a way to fix this? But will this delete records? I'm hosting at the moment ~70GB of data and don't want to lose any records. What's the best way to go about this?


texts: varchar storage and comparisons in SQL Server 2008

texts:
I have a large table with a structure similar to this:

Id       bigint primary key
Sequence int
Parent   int foreign key
Data1    varchar(20)
Data2    varchar(20)
Data3    varchar(20)
Data4    varchar(20)
Data5    varchar(20)

Data 1-5 are text fields (mostly numbers and dashes) and can be used to help prevent accidentally recording duplicates, but the data can actually duplicate occasionally. We have to let the user confirm that yes it's OK it's a duplicate.

This duplicate check can be time consuming with millions of rows. I've been tasked to ensure we can deal with tripling the amount of data we are keeping in this database. I've been told by previous developers that the current process will not work with that much data (there is of course a lot more going on with the data than I'm including here, and though the actual query for finding duplicates is pretty simple this part of the system is apparently sensitive to additional latency). I have not done experiments myself to prove this but I trust their judgement, and regardless I'd like to reduce the impact as much as possible. Unfortunately, I do not have a huge volume to test with until fairly late in the process. So while I have time I would like to try to be set up to test a few scenarios when I get a window of time on the test server.

All 5 fields must match to be counted as a duplicate. I'm skeptical that this could never change, but I'm being told that for the foreseeable future all 5 must match exactly. I'm thinking that by adding a indexed column that is a hash of the combined values, I will be able to find potential duplicates much faster. I would still have to compare the individual values to account for hash collisions though. Would I gain anything by combining the values themselves too? So a hash column, and a single column with all 5 values in a delimited string that I use to compare rather than compare each value separately?

It seems like I would only benefit if varchars are not stored with the rest of the row, which I always assumed they were. However that doesn't seem to be the case. I'm having a hard time finding specific information, but it seems that if my row is less than 8060 bytes, the full 20 characters will be allocated in the row. Only if the row is over 8060 will some varchar columns be chose to move into a separate page.

Any documentation that explains how small varchars are stored, and any suggestions regarding whether or not combining the columns into a single column will have a performance impact would be much appreciated.

EDIT:
This table will have millions of rows (say 10 million for an estimate). Each row will definitely have a maximum length of less than 8060 bytes, though I can't give you an exact number right now.


texts: Compare possible null values in one query

texts:
Question is pretty short and simple: I have following query:

SELECT *
FROM CTE

Now I want to get all rows with only maximum value of some field.

SELECT *
FROM CTE
WHERE NodeId = (SELECT MAX(NodeId) FROM CTE)

But maximum value can be null, so this query doesn't work, and I should write:

SELECT *
FROM CTE
WHERE NodeId = (SELECT MAX(NodeId) FROM CTE)
OR (SELECT MAX(NodeId) FROM CTE) is null

So I should write another OR do return true if current value is equal to maximum or maximum is null (we shouldn't check for NodeId is null because it defenitly is).

Is there any way to simplify this script?


texts:
I am getting this error in Windows Server 2012 with Visual Studio 2012 installed trying to open SQL Server Management Studio 2012:

  Cannot find one or more components. Please reinstall the application

I tried many things, including installing SQL Server Management Studio 2014, uninstalling, reinstalling… Then repairing VS2012, installing SSMS 2014 again. All to no avail.


texts: Very large Primary Key index with few rows

texts:
I have a table with a GUID as the primary key.

The default is newsequentialid() to keep the GUID's in order.

With only 65,000 rows, the primary key is 1.5 GB! There's a similar table which was 2GB with only 3,000 rows.

I've truncated the second table in a test environment and it has reduced the table size and DB size by that amount.

How do I troubleshoot this? What causes the indexes to be so large with so little data?

It's a SQL Server 2016 standard database on a Windows 2012 R2 server.

The data is from running the script below.

;with SpaceInfo(ObjectId, IndexId, TableName, IndexName
    ,Rows, TotalSpaceMB, UsedSpaceMB)
as
(
    select
        t.object_id as [ObjectId]
        ,i.index_id as [IndexId]
        ,s.name + '.' + t.Name as [TableName]
        ,i.name as [Index Name]
        ,sum(p.[Rows]) as [Rows]
        ,sum(au.total_pages) * 8 / 1024 as [Total Space MB]
        ,sum(au.used_pages) * 8 / 1024 as [Used Space MB]
    from
        sys.tables t with (nolock) join
            sys.schemas s with (nolock) on
                s.schema_id = t.schema_id
            join sys.indexes i with (nolock) on
                t.object_id = i.object_id
            join sys.partitions p with (nolock) on
                i.object_id = p.object_id and
                i.index_id = p.index_id
            cross apply
            (
                select
                    sum(a.total_pages) as total_pages
                    ,sum(a.used_pages) as used_pages
                from sys.allocation_units a with (nolock)
                where p.partition_id = a.container_id
            ) au
    where
        i.object_id > 255
    group by
        t.object_id, i.index_id, s.name, t.name, i.name
)
select
    ObjectId, IndexId, TableName, IndexName
    ,Rows, TotalSpaceMB, UsedSpaceMB
    ,TotalSpaceMB - UsedSpaceMB as [ReservedSpaceMB]
from
    SpaceInfo
order by
    TotalSpaceMB desc
option (recompile)


texts:
I want to see the performance differences between an indexed vs non-indexed column when running the same query.

The table I've created (FileCollection) is very simple and has the following 3 columns

Id (int) - Primary key which auto generates the number.
FileName (nvarchar(MAX)) - Contains a file name.
Description (nvarchar(MAX)) - Contains a description of the file.

The query I'm using is:

SELECT * FROM FileCollection WHERE FileName='readme.txt'

I've populated the database with more than 100k records. Using the SQL Server Profiler tool, I initially ran the query with no index on the FileName column and again when adding the index to FileName column. However, I cannot see any difference in performance.

Both tests average around the same stats:
CPU: 500
Reads: 13,000
Writes: 0
Duration: 200

Can anyone help me figure out why? I would assume 100k records is enough to show some difference but do I need more records?

TIA

Edit:
I've now changed the FileName column to nvarchar(255) but still the problem exists. BTW, I'm using SQL Server 2008 R2.


texts: Prevent drop column with index on it in PostgreSQL

texts:
In PostgreSQL when you drop column in a table, all indexes and constraints which use this column will be also dropped silently. If you overlook such situation, you can get serious problems in production.

Here is a demonstration:

CREATE TABLE test (
    id INT     NOT NULL PRIMARY KEY,
    a  VARCHAR NOT NULL,
    b  VARCHAR NOT NULL
);
CREATE INDEX test_index ON test(a, b);

SELECT * FROM pg_indexes WHERE tablename='test';
/*
tablename | indexname
---------------------
test      | test_pkey
test      | test_index
*/

ALTER TABLE test DROP COLUMN b;
SELECT * FROM pg_indexes WHERE tablename='test';
/*
tablename | indexname
---------------------
test      | test_pkey
*/

Is it possible to make PostgreSQL return error when you drop a column with an index on? Or maybe some other ways to prevent described situation?


texts: Delete image/blob content to reduce *.mdf file size?

texts:
I have an old SQL Server database and find it unnecessary to keep old image files the users upload.

Currently the .mdf filesize is around 10 GB and database size is mainly blown up by the image files stored in each record row (I estimate 8GB are image files). If all blob/image files older than 1 year old are deleted. Is it possible to drastically reduce the .mdf filesize to 2-3 GB?

If this works, I'll schedule a script to delete all old blob/images at the end of each year, and just keep images for the past 12 months.


texts:
I have a simple SQL statement (executing on a SQL Server upgraded from 2000 to 2008 R2):

SELECT TOP (1)
    DocNox = CONVERT(integer, SUBSTRING(DocNo, 2, LEN(DocNo)-1)
FROM Tbl1
WHERE
    Fld1 = 19
    AND Fld2 = 1
    AND Fld3 = 1
    AND DocNo NOT LIKE '%-%'
ORDER BY
    CONVERT(integer, SUBSTRING(DocNo, 2, LEN(DocNo)-1) DESC;

Sample data (Serial is an IDENTITY column)

Serial   DocNo    Fld1   Fld2   Fld3
211      A1       19     1      1
212      A2       19     1      1
213      A003     19     1      1
214      X1-C1-1  20     1      1

Executing the query produces this error message:

Error converting nvarchar value '1-C1-1' to a column of data type int.

This is unexpected because the row containing that value ought to be filtered out by the WHERE clause before the conversion in the SELECT and/or ORDER BY clauses.

I have noticed that the following changes prevent the error occurring:

Changing the condition WHERE Fld1 = 19
Copying the data to another table with the same structure
Removing the TOP (1)
Removing the ORDER BY
Changing the ORDER BY to a different column (e.g. Serial)

But the error still occurs in the following circumstances:

Importing the whole table to another database
Running the same statement against a different database with the same data

I also made a change to the statistics that prevented the error but I cannot remember exactly what I did.

Can any one explain what is happening here?


texts: Is it safe to run parallel innodb single-transaction dumps of individual tables?

texts: I can't seem to find the internals for --single-transaction mysqldump option for InnoDB tables, but is it possible to use --single-transaction for single tables in a parallel dump so that the --single-transaction only affects the current table I'm dumping and it is safe to run in parallel for quicker dumps? I tested this on a development server and works well but without knowing the internal locking methods I am worried to use it in production


texts: Can I use transaction logs to rebuild a database?

texts:
We've had some data loss and don't have a reliable backup it seems. We do have HUGE transaction logs and there was some thought that we might be able to use these to get back to a certain point.

Is it possible to help rebuild a database using transaction logs?

SQL Server 2008 R2, btw.

(x-posted from stackoverflow)


texts: TDE error when attempting to encrypt a datafile

texts:
I'm encountering error
ORA-28374: typed master key not found in wallet

when attempting to run
alter database datafile '+DATA/testdb/datafile/users.2514.1233355332' encrypt;

against an 11g database on Linux. Basically it's not possible to encrypt any datafile.
The sqlnet.ora file is correct and pointing to the vault.
If anyone could please direct me to a check list of what could be wrong it would be greatly appreciated.
Also (just guessing) if the original primary was encrypted but not the standby and then the standby became the primary and the old primary dropped. Could that lead to the error above? How could such a situation be rectified? Is it just a case of deleting the old endpoints in the key vault? Thank you.


texts: Query plan cache bloated by ad-hoc queries, even with "Optimize for Ad-hoc Workloads"

texts:
I've been noticing what I thought to be unusual issues with our query plan cache, where the plans in the cache were never more than a day old.

Through running the following query (courtesy of Kimberly Tripp), it showed that the majority of plans (4.5Gb of 6Gb cached plans or 44813 of ~50000), were Ad-hoc queries that had a use count of 1.

SELECT objtype AS [CacheType]
    , count_big(*) AS [Total Plans]
    , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
    , avg(usecounts) AS [Avg Use Count]
    , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
    , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC

I've determined the problem query (dynamic, with an EXEC, of course...), which is rather hideous, but also complicated to just 'fix', so I was looking into immediate improvements that could be made.

The instance is already set to use Optimize for Ad-hoc Workloads, however the CacheObjType from sys.dm_exec_cached_plans are all Compiled Plan rather than Compiled Plan Stub.

When using the Ad-hoc Workload mode, should the plans not be Compiled Plan Stub until their usecounts are greater than 1? Or is that not how this works?

There's also a refcounts field that no one seems to refer to when talking about Adhoc queries. The refcounts is always 1 when the type is Compiled Plan Stub and 2 when type is Compiled Plan.  Even through reading BOL, I'm not entirely sure what this field means. Can someone clarify?


texts:
I have in the name column:

3 boxes of frogs
7 boxes of frogs
48 boxes of frogs

When I execute:

SELECT name FROM items WHERE name REGEXP "[^1-5] boxes"

...the result is:

7 boxes of frogs
48 boxes of frogs

When I tried:

SELECT name FROM items WHERE name REGEXP "[^1-50] boxes"

and

SELECT name FROM items WHERE name REGEXP "[^1-48] boxes"

...the result is:

7 boxes of frogs
48 boxes of frogs

and

7 boxes of frogs

...respectively.

Why won't "3 boxes of frogs" show with REGEXP "[^1-48] boxes and others.
The result of REGEXP "[^1-48] boxes and REGEXP "[^1-50] boxes is so weird.
Can someone explain why?


texts: Getting the exact count of rows in Postgres database

texts:
Is there any way to get the exact row count of all tables in Postgres in fast way?

Count(*) seems to run very slowly for larger tables.

Is there a way to get an exact count from Postgres statistics tables without running vacuum, which is also a costly operation?


texts: EXEC master.dbo.xp_cmdshell 'The user name or password is incorrect'

texts:
I am trying to move a file from a network drive to another server and when I execute this statement. I get an output of

  The user name or password is incorrect

DECLARE @SourceFile AS VARCHAR(500);
DECLARE @DestinationFile AS VARCHAR(500);
DECLARE @Cmd AS VARCHAR(500);

SET @SourceFile='\\SHDVNFDBV1\shared-backup\netFORUM_Archive.bak';
SET @DestinationFile ='J:\netFORUM_Archive_TEST.bak';

SET @Cmd = 'COPY ' + @SourceFile + ' ' + @DestinationFile;

EXEC master.dbo.xp_cmdshell @Cmd


texts: Using wm_concat to concatenate rows, but in an order determined by another column

texts:
Let's say I have 3 columns: p_id, description, order_by.  I am trying to do the following:

I would like to concatenate the description for all like p_id values.  So we are talking a group by p_id sort of thing.  But then, I want the description to be concatenated in the order of the order_by column (which is an integer).  So my ideal query (not-working) would look like

select p_id, wm_concat(description)
from my_table
where p_id = 12345
group by p_id
order by order_by asc

How do I concatenate rows in this fashion?


texts:
I am a beginner trying to understand the basic concepts of database's. I know several types of keys (Primary key, Foreign key, candidate key, super key, surrogate key.....etc). While learning about the degenerate dimension, I came across "dimension key".

  In a data warehouse, a degenerate dimension is a dimension key in the fact table that does not have its own dimension table        ----Ralph Kimball

Could someone please helps me understand the dimension key ?


texts: MySQL HA_ERR_LOCK_WAIT_TIMEOUT ignoring innodb_lock_wait_timeout (deleting most of a table)

texts:
I am issuing some long-running delete queries to some databases, and I am getting the following error:

Last_Error: Could not execute Write_rows event on table DATABASE.TABLENAME; Lock wait timeout exceeded; try restarting transaction, Error_code: 1205; handler error HA_ERR_LOCK_WAIT_TIMEOUT; the event's master log mysql-bin.005698, end_log_pos 91495479

I have set "innodb_lock_wait_timeout" to high value such as the query will never hit the timeout.

SELECT @@GLOBAL.innodb_lock_wait_timeout;
+-----------------------------------+
| @@GLOBAL.innodb_lock_wait_timeout |
+-----------------------------------+
|                           1000000 |
+-----------------------------------+

Any suggestions for how to avoid this error? I need to delete large amounts of data so I want to issue queries in a loop without supervision. It feels a bit like a mysql bug. MySQL version is 5.6.

EDIT: Here's the exact query. The data is sharded client-side based on the function shown (MOD(MOD(user_id,65536), shard_count) != shard_id). The majority of the data in the DBs shouldn't be there, so the delete is big, and it's cascading down to tables that are >300GB (where each user might have an inventory of 200 items, for example)

DELETE FROM User
WHERE account_type = "type1"
AND
user_id IN (
    SELECT b.user_id
    FROM (
        SELECT a.account_type, a.user_id
        FROM (
            SELECT account_type, user_id
            FROM User
            WHERE account_type = "type1"
            AND
            MOD(MOD(user_id,65536), 20) != 9
            LIMIT 1000000
        ) a
    ) b
)

EDIT: As requested, output of SHOW CREATE TABLE

mysql> SHOW CREATE TABLE User \G;
*************************** 1. row ***************************
       Table: User
Create Table: CREATE TABLE `User` (
  `account_type` varchar(20) NOT NULL DEFAULT '',
  `user_id` bigint(20) unsigned NOT NULL,
  `mod_date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `team_id` int(10) unsigned DEFAULT NULL,
  `shard_key` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`account_type`,`user_id`),
  KEY `PLAY_SHKEY_I` (`shard_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

EDIT: I believe at this point that it was down to a programming error, because I wasn't calling commit() from the python client until a really big loop completed, so I think there was a huge ongoing transaction that did not commit ever (because it never made it all the way through the loop)


texts: Unable to ping/connect to azure sql server from sql server management studio

texts:
I created a database in azure and assigned a new server to it. I entered my ip address in the firewall and tried to connect to the server in Sql Server Management Studio but no success. I tried to ping the FQDN of the server server.database.windows.net but no success.
I searched the problem and I found that the server cannot be pinged
click here. Can you please help me in connecting to the SQL server or database from SSMS on my machine?
Thanks


texts: Does a single rollback affect all transactions?

texts:
When I run the following script:

begin transaction
    select XACT_STATE()

    select @@TRANCOUNT

    begin transaction

    select XACT_STATE()

    select @@TRANCOUNT

    begin transaction

    select XACT_STATE()

    select @@TRANCOUNT

    rollback

    select XACT_STATE()

    select @@TRANCOUNT

I get the following result:

So one single rollback statement rolls back any number of transactions?

There is no point in writing the following then:

while @@TRANCOUNT > 0
      rollback


texts: What are alternatives to Replication to distibute data (Microsoft SQL Server)

texts:
Is anyone knowledgeable of alternatives to SQL Server replication?

I have a scenario where separate databases will share a lot of the same data which complex queries will match against.
We want to separate the databases out from each other (i.e. not on the same SQL Server) so we can ensure the server resources are not a bottleneck.

What techniques allow fast and efficient data sharing, fairly instantly.

Replication seems to be quite a slow and cumbersome technology?

Are there any new features in SQL 2012 and 2014 that cater for this kind of scenario?

AlwaysOn Availability groups doesnt quite cut it as these are readonly replicas.

I need a read/write replica where multiple data sources interact with each other

Server A        Server B      Server C

All have read/write transactions, all require as instant
as possible sharing of data between them of the results of the transactions.

Thanks for your help


texts: SQL Server 2000\2005 compatibility with Active Directory 2016

texts: We are currently using Active Directory 2008R2 and will be upgrading to AD 2016. I'm trying to determine if there is any known compatibility issues when running older versions of SQL Server (2000 and 2005) when upgrading to AD 2016. Has anyone been through this process? Most of our db servers are using SQL 2008 > 2016, but a few still runs with 2000/2005. Thanks


texts: Returning Row Count in a Stored Procedure in Oracle

texts:
I'm mostly  SQL Server person, so forgive me if this is too close to SQL Server, but in a nutshell using Oracle 10 I'm trying to build a procedure something like this:

PROCEDURE SP_INFO_EX_S(
    v_EX_ID    IN NUMBER,
    v_EX_ROWS               OUT NUMBER
    )
AS

SELECT COUNT(*) AS ExCount
FROM EXAMPLE
WHERE EX_ID = v_EX_ID
END SP_INFO_EX;

What I'd like this to do is return v_EX_ROWS as the total number of rows in the example table that match the WHERE Clause, but Google doesn't seem to have the answer to this.  Am I missing something?  Thanks!


texts: MariaDB writes at 500kb/s how to get it to write at normal speeds?

texts:
My ubuntu 18.04 has mariadb. I've tried mariadb 10.1, 10.2 and 10.3 all with the same symptons
When I time the harddrive, it has reasonable speeds

$ sudo hdparm -Tt /dev/sda
/dev/sda:
Timing cached reads:   33470 MB in  1.99 seconds = 16840.64 MB/sec
Timing buffered disk reads: 354 MB in  3.03 seconds = 116.92 MB/sec

write:

$ dd if=/dev/zero of=/tmp/output conv=fdatasync bs=384k count=1k; rm -f /tmp/output
1024+0 records in
1024+0 records out
402653184 bytes (403 MB, 384 MiB) copied, 2.4399 s, 165 MB/s

But when I check the actual thouroughput of mariadb it only has 500kb/s which is ridiculously slow. On my windows box it runs at 60mb/s and it both has mariadb 10.2
 10012 be/4 mysql       0.00 B/s  502.34 K/s  0.00 %  2.50 % mysqld
   563 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.44 % [ext4lazyinit]
 10009 be/4 mysql       0.00 B/s    0.00 B/s  0.00 %  0.09 % mysqld
     1 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % init
     2 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kthreadd]
     4 be/0 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kworker/0:0H]
     6 be/0 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [mm_percpu_wq]

I've tried various iterations of these settings, but all end up with really slow throughput/no noticable change
innodb_file_per_table=1
innodb_buffer_pool_size=8G
thread_cache_size=10
skip-name-resolve
query_cache_type=1
query_cache_limit=256k
query_cache_min_res_unit=2k
query_cache_size=80M
tmp_table_size=256M
max_heap_table_size=256M

I set the sysctl -w vm.swappiness=0 without any effect.
I'm doing transactions of 5000 rows that get inserted. When I run the exact same code at my workstation it pops each query off per second. ON the remote system it takes roughly 10 minutes for one batch to complete.
I've tried every trick I could find, tried various versions, and nothing really helped.
Does anyone have an explanation why mariadb only runs at 500kb/s?
It's a fresh system, bought today, freshly installed with ubuntu today.
I had a similar issue with an SQLITE database, where the database would wait for the disk to spin around, release, wait, etc... I could disable the waiting on disk there.
Is there a similar setting in MariaDB? to write to disk buffer, where I would accept the risk of powerloss, isntead of waiting on a hardware lock?