texts: Dropping columns from a table, truncating it and then re-inserting data as before into the table doesn't reduce space occupied

texts:
I dropped a few (decimal) columns from my table. Truncated the table and then re-inserted the data as before, barring the dropped columns. However I don't notice any perceptible difference in the space occupied by the table. I had expected it to decrease. Why did it not?

Below is the query I'm using to determine the space occupied:

SELECT
b.groupname AS 'File Group',
Name,
[Filename],
CONVERT (Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space (MB)],
CONVERT (Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)],
CONVERT (Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)],
'DBCC SHRINKFILE ('''+Name+''','+CAST(CONVERT (Decimal(15,2),ROUND((FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS VARCHAR(10))+')' QUERY
FROM dbo.SYSFILES a (NOLOCK)
JOIN SYSFILEGROUPS b (NOLOCK)
ON a.groupid = b.groupid
ORDER BY b.groupname

PS. I deemed Rebuilding the table and its indexes unnecessary, as the table was truncated


texts:
Given this table:

CREATE TABLE [dbo].[visit](
    [visit_id] [uniqueidentifier] NOT NULL,
    [group_id] [uniqueidentifier] NOT NULL,
    [visitor_company] [nvarchar](50) NOT NULL,
-- Other columns
PRIMARY KEY CLUSTERED
(
    [visit_id] ASC -- per @Mark Sinkson's comment, I agree that this is a poor choice for a clustered index, but this is a different can of worms
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

And given that the application executes these two queries frequently:

SELECT * -- not literally *; just don't want to complicate my question
FROM [dbo].[visit]
WHERE group_id = @group_id

SELECT DISTINCT (visitor_company)
FROM [dbo].[visit]
WHERE group_id = @group_id

Does a non-clustered index on group_id, visitor_company optimize both queries?
Or should I create a non-clustered index on group_id as well?

EDIT 1: This is not homework. I'm a full-stack developer (not a DBA). I'm torn between Options 2 and 3 and I'd like some experts to weigh in.

EDIT 2: Constraint: I can't change visit_id to an int.


texts:
I am running SQL Serve 2008 and we have a few test tables that were obviously used for testing.  The prefix of the tables are Test_, my question is, will this script delete all tables with the prefix of Test_ on the database I execute it on?

declare @cmd varchar(4000)
declare cmds cursor for
select 'drop table [' + Table_Name + ']'
from INFORMATION_SCHEMA.TABLES
where Table_Name like 'Test%'

open cmds
while 1=1
begin
  fetch cmds into @cmd
  if @@fetch_status != 0 break
  exec(@cmd)
end
close cmds;
deallocate cmds


texts:
I found a function that will properly capitalize text strings.  I have a table where my fields need this function run on.  Is it possible to run the function on my table?

Create Table @Garbage
(
  fullname varchar(500)
  ,saddy varchar(500)
)

Insert Into @Garbage (fullname, saddy) VALUES
('BlAmE iT oN tHe RaIn', 'APPALACHACOLA FUNVILLE FOOTHILLS')
,('RED DIRT ROAD', 'purple visors block THE sun')
,('blueBERRIES taste YUM', 'bLaCk CaTs')

ALTER FUNCTION [dbo].[fnConvert_TitleCase] (@InputString VARCHAR(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @OutputString VARCHAR(255)

SET @OutputString = LOWER(@InputString)
SET @Index = 2
SET @OutputString = STUFF(@OutputString, 1, 1,UPPER(SUBSTRING(@InputString,1,1)))

WHILE @Index <= LEN(@InputString)
BEGIN
  SET @Char = SUBSTRING(@InputString, @Index, 1)
  IF @Char IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&','''','(')
  IF @Index + 1 <= LEN(@InputString)
BEGIN
  IF @Char != ''''
  OR
  UPPER(SUBSTRING(@InputString, @Index + 1, 1)) != 'S'
  SET @OutputString =
  STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index + 1, 1)))
END
  SET @Index = @Index + 1
END

RETURN ISNULL(@OutputString,'')
END


texts: Mariadb master master replication supported by HAPROXY for automatic failover

texts:
We have implemented master-master setup in maria-db. Master A is being used for read\write and Master B is used for backups and MIS operations. We have built this setup so that we do not need to promote slave in case of failure.

Now we want to implement HA PROXY for automatic fail over. In case Master A goes down application should switch automatically to Master B.

Now my question is that if Master A goes down and applications start serving from Master B. But when Master A comes online and is not in sync with Master B what will HA PROXY do, will applications get connected to Master A instantly or HA Proxy will check synch status and let Master A synch up with Master B and then allow applications to connect with Master A.

What parameters need to be added in order to achieve this.

HA PROXY Configuration:


texts: WHERE statement on an inner join with GROUP BY

texts:
I need some assistance with the below. Essentially I am grabbing all lines that match certain parameters (matching itemid, custaccount, qtyordered). I need to also add a where statement to my inner join, i.e. where b.salesstatus in '3','4'. The issue I have is that I can't select salesstatus in my inner join unless I am also grouping by salesstatus? How can I add a where statement for the inner join that will be acceptable?

SELECT
 ST.DATAAREAID
,ST.CREATEDDATETIME
,ST.SALESID
,ST.SALESNAME
,SL.ITEMID
,SL.CUSTACCOUNT
,SL.QTYORDERED
,SL.SALESQTY
,SL.LINEAMOUNT
,SL.CUSTOMERREF
,ST.SALESPOOLID
,ST.RETURNITEMNUM
,CASE WHEN CONVERT(nvarchar(12), SL.SAB_PICKINGSTATUS) = '1'   THEN 'Confirmed' WHEN CONVERT(nvarchar(12), SL.SAB_PICKINGSTATUS) = '2'     THEN 'Invoiced' WHEN CONVERT(nvarchar(12), SL.SAB_PICKINGSTATUS) = '0'   THEN 'OpenOrder' ELSE 'UNKNOWN' END AS PickStatus
,CASE WHEN CONVERT(nvarchar(12), SL.SALESSTATUS) = '1' THEN 'OpenOrder'   WHEN CONVERT(nvarchar(12), SL.SALESSTATUS) = '3' THEN 'Invoiced' WHEN CONVERT  (nvarchar(12), SL.SALESSTATUS) = '4' THEN 'Cancelled' ELSE 'UNKNOWN' END AS   LineStatus
FROM dbo.SALESLINE AS SL
INNER JOIN dbo.SALESTABLE AS ST
ON SL.SALESID = ST.SALESID
AND SL.DATAAREAID = ST.DATAAREAID
INNER JOIN dbo.INVENTTABLE ON SL.ITEMID = dbo.INVENTTABLE.ITEMID
AND     ST.DATAAREAID = dbo.INVENTTABLE.DATAAREAID
INNER JOIN (
  SELECT
  LINE.ITEMID
  ,LINE.CUSTACCOUNT
  ,LINE.QTYORDERED
FROM dbo.SALESLINE AS LINE
  INNER JOIN dbo.SALESTABLE AS STABLE
ON LINE.SALESID = STABLE.SALESID
AND LINE.DATAAREAID = STABLE.DATAAREAID
GROUP BY
 LINE.CUSTACCOUNT
,LINE.QTYORDERED
,LINE.ITEMID
HAVING COUNT(*) > 1
) AS B
ON B.CUSTACCOUNT = SL.CUSTACCOUNT
AND B.QTYORDERED = SL.QTYORDERED
AND B.ITEMID = SL.ITEMID
**strong text**WHERE (SL.CREATEDDATETIME >= '2019-01-01 00:00:00')  AND (SL.SALESSTATUS != '4') AND (SL.CUSTACCOUNT NOT IN (N'2ALD217')) AND (SL.ITEMID NOT IN (N'15MEGA', N'15TACK', N'10MEGA', N'10TACK')) AND (NOT (dbo.INVENTTABLE.SAB_INVENTCLASSGROUPID IN (N'CATBRO', N'CHARGES', N'KIPLREG', N'MERCH', N'POS', N'SAMPLES', N'SWAFLD'))) AND (SL.QTYORDERED > 1) AND (SL.CUSTOMERREF NOT IN (N'')) AND (ST.SALESPOOLID NOT IN (N'POSE', N'DISP', N'INTC'))


texts: Should running number be separated column from primary key in database?

texts:
I have questions that blogged my mind on separating member number and member id.

Suppose that we have a database with member id primary key of Guid type. Then, normally, when we view the profile (on web page), we will display a Profile/100001 instead of Profile/{Guid-id}. So, in this case, we would have another column for member number which is separated from member id.

Now, assume that we have a case where Member ID is int datatype, and the running number of Member No is incremented 1 each time start from 100001, should we separate the member ID (primary key) from member number in this case?

This question is focus on web application database design. Because I got to "know" that exposing primary key to user is not a good practice. So, does that means that we should have an internal ID(primary key) that is used for database and another column with running number that is display to user?


texts:
Is it possible to separate CPU cores in Mysql?

I understand it is possible for Memory as we can tune it in innodb_buffer_pool_size or innodb_additional_mem_pool_size. How about CPU cores in Hardware? Or something in OS Level? It is going to be on RHEL 6.5.

EDITED:

I have a standalone Server with

2*8 Processor
32 GB Ram

I would like to run two Mysql instances one to use only 2 processors and another instance should use remaining all? Is it possible in defining them in server variables of Mysql?


texts: TempDB Transaction log not releasing space

texts:
Every time I complete a transaction in tempdb, the temp log grows, however the log doesn't seem to release the space once the transaction has completed.The log usage percent is 43.4%  but seems to increase in around 4% increments.

When I look for open transactions (sys.dm_exec_sessions), there are none. Yet the log_reuse_wait_desc in sys.databases shows 'ACTIVE_TRANSACTION'. When I query the sys.dm_tran_active_transactions DMV, it shows only work table.

I'm unsure of how I can release this space from the tempdb transaction log.

I've just run a manual checkpoint and that seems to free up the space so I'm not sure as to why the space isn't being freed up on it's own


texts:
I have a 1 TB read-only database where performance is critical. It's difficult to predict queries since they are dynamically generated by the users (the whole thing is basically a visualization platform atop a large collection of medical studies, and users select what they want to visualize). Queries can often be complex and involve 10+ joins. I recently learned about the extended statistics feature, but I find little information online about when best to use it (other than what's in the documentation).
The DB is pretty well normalized, but makes extensive use of materialized views which are de-normalized. Is there any performance penalty or other issue with creating extended statistics (dependency and top n) for all pairwise columns? It would result in, say, 500 statistics on some 70 tables. Time for analyze or inserts is not of relevance, only read performance. Also, is there a tool or code snippet to help me do this?
I'm using Postgresql 12 and it's optimized as far as possible w.r.t. indexing.


texts:
I already know how to schedule monthly jobs using SQL Server Agent > Jobs, I now would like to learn how to schedule such job using T-SQL.

I know how to schedule a job daily at a specific time:

CREATE PROCEDURE MyTask
 AS
BEGIN
    SET NOCOUNT ON;
    -- Now is set to run evry day at 23:00
    -- But I need to run it every 1th of the month
    declare @delayTime nvarchar(50)
    set @delayTime = '23:00'

    while 1 = 1
    begin
        waitfor time @delayTime
        begin
            -- Here the query I want to run every month
            select * from AdventureWorks2012.dbo.AWBuildVersion;
        end
    end
END

-- Sets stored procedure for automatic execution.
sp_procoption    @ProcName = 'MyTask',
                @OptionName = 'startup',
                @OptionValue = 'on'

On this Microsoft guide I see I can use @freq_type = 16 but I don't know how to set it and an example would be much appreciated


texts: Setting UNIQUE constraint on TIMESTAMP (WITH TIME ZONE)

texts:
I want to scrape a table from a website and synchronize the data into my own database i.o. to detect whenever there is a new entry available. The table has a timestamp column which tells me when the new record was created.
However, I have different tables for multiple objects so in order to know if there is a new entry, I'd need a unique-constraint on (object_id, timestamp):
object_id  | timestamp           | value
------------------------------------------
1          | 2021-01-01 00:00:00 | 100      <--|
1          | 2021-01-02 00:00:00 | 101         | error: have an
1          | 2021-01-03 00:00:00 | 102         | equal timestamp
1          | 2021-01-01 00:00:00 | 103      <--|
2          | 2021-01-10 00:00:00 | 1002
2          | 2021-01-12 00:00:00 | 1003

It feels a  bit odd to have such a unique-constraint so I was wondering if this is the only (or best) way I could do this?


texts: SSIS OnError propagating when it shouldn't

texts:
I have an SSIS package with a data flow task that uses a script component as its data source. The script component retrieves its data from a web service. The call to the web service sometimes fails, so I want to be able to retry a few times before failing the package.

My situation is very similar to the one described here. I followed the steps outlined there, but my For Loop still fails even though the Propagate variable on the Data Flow Task is set to False.

My question is: are there any situations you are aware of that would cause an error to propagate to a parent container even when the Propagate variable says not to?

Some more details about the error: it is caused by the web service call timing out. (There is some problem with our connection to the web service that we have not yet been able to diagnose. The web service provider shows that they receive the request and respond, but our SSIS never gets the response back, and instead waits until it times out.) This raises a System.TimeoutException in the Script, which in turn causes an SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.


texts: What is the benefit of two FK, when one of them can be deduced from another table?

texts:
The third party Microsoft SQL Server database I'm working on is using the structure which can be illustrated with this example:

Let's take three tables:

Shop which is the topmost level and corresponds, for example, to an e-commerce website, given that several websites are using the same database,
Category which is a logical category of products within a Shop,
Product which belongs to a Category (given that the category is mandatory, so there should be no products which don't belong to any category).

Category has one foreign key to Shop. The Product has two foreign keys: one to Category, another to Shop.

If I were designing a similar database, I would have put only one foreign key to Product, linking it only to a Category. IMO, it:

Simplifies the schema,
Avoids the risk of inconsistent state, where product 1 belongs to category 1 and site 2, but category 1 itself belongs to site 1,
(Avoids redundant information to waste the place in the database),
Doesn't make it particularly more difficult to query data. Even if there are cases (for example a search) where the website would need products without carrying too much about the categories, but still considering to what shop they belong, making two joins instead of one is not a big deal.

Why was this third party database done in the way it's done? Are there benefits from this approach?


texts: How to use the 'select benchmark' command on a procedure

texts:
I am trying to check which of my procedures is slowest and thus needs optimization.

I currently have around 160 procedures and about 30 functions.

I am using the select benchmark() to check the procedure speed, but all the commands I try end in the error "Check your syntax".

select BENCHMARK(1000, CALL testProc()); gives me an error,
select BENCHMARK(1000, testProc()); also gives me an error.

QUESTION

 Can Benchmark work on procedures, the examples I see only use functions.
 Is there a better, faster automated way to test execution speeds of procedures.


texts:
I am new to object oriented databases.

What is the error in this database schema?

It gives error after insertion query and not before that

insert into student_table values
(
  student_detail
  (
    name_student('abc','pqr'),add_student('ABCD','pqrs'),2000
  )

);

sqlfiddle


texts: Upgrade production database during software development lifecycle

texts:
Background

Using Oracle, there are a number of tools that help with migrating and applying development changes into the production environment (such as Embarcadero Change Manager). These tools can be configured to perform any database DDL upgrade with little to no human intervention.

Problem

I have development and production servers running PostgreSQL 9.x. After the initial deployment of the database DDL to the production server, I will continue to make changes to the development database. These changes will include bug fixes to stored procedures, changes to tables, additional sequences, new views, more types, more tables, and so forth.

Question

What are the steps to upgrade/migrate the DDL for a production PostgreSQL database application in an automatic fashion (or nearly automatically) such that it has the new changes made in development?

Related

http://apgdiff.startnet.biz/

Thank you!


texts:
I am building a small inventory database for copier supplies at my place of work. In the end this database will be automated to subtract taken from the shelves and add any incoming orders. Right now I am having a hard time with creating the tables. I don't have much experience with SQL except for a class I took a while back. I can't think of tables that I would need for this project. Currently we use a manual system with the following information.

Manufacturer
Model
Part #
Reorder Level
Reorder Quantity
On Hand
Quantity on Order

I don't need to know price or anything like that. I think all of that could go on one or two tables. But as stated earlier, I haven't had any experience with SQL in a while and would like some feedback.


texts: MySQL Replication - replication issues; procedures registered on master not giving same result on slave in MySQL 5.5.20

texts:
I am not able to replicate procedures registered in the Master to the Slave in MySQL 5.5.20

The Procedure is registered on the Master and can be seen on Slave. However, results fetched using the same tables are different.

Anyone know how can I debug and solve this???


texts:
I have a table having records as below:

ID   Name   parentId
1    A      Null
2    B      1
3    C      Null
4    D      3
5    E      4

I need out put in following format

ID   Name   ParentName

 1    A      Null
 2    B       A
 3    c      Null
 4    D      C
 5    E      C->D

I tried with following sql query but not able to get exact output. can anybody help me out

With CTE AS
(
   select id, Name, CAST('' AS VARCHAR(MAX)) as Parent from @table
   union All
   select t.Id, t.Name , CTE.Parent+','+CAST(t.Name AS VARCHAR(MAX)) as parents from
   CTE c inner join @table t on t.parentid=c.id and c.parentid is not null
)
select * from cte


texts: Automatic failover support on SQL Server having only two servers

texts:
We have two servers each one occupied by a dedicated storage. I am looking for the a way to setup these two servers to provide a database on MS SQL Server with automatic fail-over support.

Using Database Mirroring seems to be ineffective for automatic fail-over when only two servers are available.

More info on why: For automatic fail-over, Database Mirroring needs a third element called witness to coordinate Principal/Mirror roles between two DB instances and assist switching during fail-over. However, I only have two servers available so I am forced to install witness on one of the servers that can be either Primary (Principal) or Secondary (Mirror) DB instance. So when a hardware failure occurs on the server that both principal DB and witness are running, the mirror DB cannot become online and automatic fail-over doesn't happen.

A recent feature of SQL Server called Always On Availability Groups seems to provide the similar functionality for mirroring plus more. Can automatic fail-over be achieved using Always On Availability Groups having only two servers?

Do you have any other recommendation for having automatic fail-over support, particularly against power failures, by two servers?


texts: Why would optimiser choose Clustered Index + Sort instead of Non-Clustered Index?

texts:
Given the next example:
IF OBJECT_ID('dbo.my_table') IS NOT NULL
    DROP TABLE [dbo].[my_table];
GO

CREATE TABLE [dbo].[my_table]
(
    [id]    int IDENTITY (1,1)  NOT NULL PRIMARY KEY,
    [foo]   int                 NULL,
    [bar]   int                 NULL,
    [nki]   int                 NOT NULL
);
GO

/* Insert some random data */
INSERT INTO [dbo].[my_table] (foo, bar, nki)
SELECT TOP (100000)
    ABS(CHECKSUM(NewId())) % 14,
    ABS(CHECKSUM(NewId())) % 20,
    n = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
FROM
    sys.all_objects AS s1
CROSS JOIN
    sys.all_objects AS s2
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_my_table]
    ON [dbo].[my_table] ([nki] ASC);
GO

If I fetch all records ordered by [nki] (Non-clustered index):
SET STATISTICS TIME ON;
SELECT id, foo, bar, nki FROM my_table ORDER BY nki;
SET STATISTICS TIME OFF;

SQL Server Execution Times: CPU time = 266 ms, elapsed time = 493 ms

Optimiser chooses the clustered index and then applies a Sort algorithm.

Execution plan
But if I force it to use the non-clustered index:
SET STATISTICS TIME ON;
SELECT id, foo, bar, nki FROM my_table WITH(INDEX(IX_my_TABLE));
SET STATISTICS TIME OFF;

SQL Server Execution Times: CPU time = 311 ms, elapsed time = 188 ms

Then it uses non-clustered index with a Key Lookup:

Execution plan
Obviously if the non-clustered index is transformed into a covering index:
CREATE UNIQUE NONCLUSTERED INDEX [IX_my_table]
    ON [dbo].[my_table] ([nki] ASC)
    INCLUDE (id, foo, bar);
GO

Then it uses only this index:
SET STATISTICS TIME ON;
SELECT id, foo, bar, nki FROM my_table ORDER BY nki;
SET STATISTICS TIME OFF;

SQL Server Execution Times: CPU time = 32 ms, elapsed time = 106 ms

Execution plan

Question

Why does SQL Server use the clustered index plus a sort algorithm instead of using a non-clustered index even if the execution time is 38% faster in the latter case?


texts: Data Migration from SQL Server to PostgreSQL

texts:
I need to migrate database from SQL Server to PostgreSQL. Both databases are on different servers. I have about 20 tables and millions of rows.

What are the ways to accomplish this? I there any open source tools? Can I do this using some script?

I have access to the server that runs PostgreSQL database and I have connection parameters to the SQL Server. The OS on the server that runs PostgreSQL is ubuntu.

The SQL Server is still live, and will continue to be. Time is not a problem. I didn't understood you, sorry. Neither database is in a production so I guess they can be unavailable as long as needed.


texts: CDC and datamodel updates, best methodology?

texts:
We're about to start using CDC to track changes to a large number of tables in our db's. But since our datamodel is rather frequently updated, I'm wondering what is the best method for easily keeping CDC in a working order, also considering that during the update there are a bunch of standardized DML operations we don't want to keep any track of.

So I'd just like to run my current idea for doing things by you guys, and if you have better ideas, I'd like to hear how to simplify and optimize the process?

Enabling CDC for the first time, we have a script that will dynamically find the schemas from a list of all the auditable tables, and create new audit tables of those schemas (one audit table for each table tracked by CDC).
We then dynamically create a stored procedure that will read the data from CDC and transfer all the new rows to those audit tables, doing whatever necessary modifications on the way.
Then we manually update the CDC_Cleanup agent job with a new first step to always run the procedure before cleaning data, as well as create a new job that will run that procedure every X minutes.
Finally we create a DDL trigger on the database checking every ALTER / DROP TABLE command against the list of CDC tables, so as to prevent changes that could break CDC, and return an error warning of said issue.

Now the actual question concerns the datamodel updates (ie. when a new software version is released). This is the part where it gets tricky. The following is an idea of how I thought I would go about this.

Exec the stored procedure and add all latest CDC records to the audit tables (there is no traffic at this point, as the external services are off).
Disable CDC on database OR disable CDC on each table (whichever is fastest), and disable the timed job (tricky because with alwaysOn we need to do this on every replica, unless we make some kind of an SSIS job using the listener to call the procedure on the primary replica only)
Run all the data modification scripts (updates, inserts etc)
Update the datamodel
Run a manually maintained script, which will update the audit tables schemas to match the real tables they're keeping track of.
Enable CDC on the tables, at this point the real tables, CDC tables and audit tables should all have matching schemas.
Recreate the stored procedure from the updated definitions dynamically from the primary tables.
Modify & enable the CDC jobs and re-enable the DDL trigger again.

Now, this seems like a huge hassle with a lot of manual labor, especially considering we have multiple different environments to manage.

Is there any way to change this to a more dynamic approach? Can you see any obvious flaws or ways to optimize this whole process?

Basically, we just want to disable CDC before changes, enable it after, and make sure all the tables tracked by CDC don't break up even if their schemas were altered?

Thanks!


texts: Query to find sql server services currently running

texts: Is there any query to find all SQL services currently running on the server including SSRS, SSIS, SSAS?


texts: SQL Server Agent Alerts using severity 16

texts:
My work is currently deleting lot of stored procedures, views, tables and so on.

My worry is that if we miss judge a object and it goes into production that we'll only catch the errors when an user reports it.

I've created an alert using type "SQL Server event alert" and severity 16 and checked the e-mail for operator.

So, time to test the task:

EXEC SomeObjectThatDoesNotExist

I've verified that it's a severity 16 by checking message table.

USE MASTER

SELECT *
FROM SYS.MESSAGES m
WHERE m.language_id = 1033 AND
        m.message_id = 2812

But this does not raise an alert. Is it possible to raise an alert on object not been found?


texts: SQL - Find top rows where parameter is the same

texts:
I am learning sql and I am jumping into more complicated queries. I have the following tables:

players:
Columns: name, school_name, gender

simple:
Columns: player_name_fkey, school_name_fkey, category, tournament_number, score

My entries in the simple table will be similar to this. Each line is the score by a player at a specific tournament number.

Joe, Harvard, Junior, 1, 40
Joe, Harvard, Junior, 2, 30
Joe, Harvard, Junior, 3, 60
Anne, MIT, Senior, 1, 45
Anne, MIT, Senior, 2, 55
Anne, MIT, Senior, 3, 20 etc.

What I am looking to do is to get, for each player, the sum of his / hers top two scores of the season:

Joe, Harvard, 100
Anne, MIT, 90

I have the following query:

SELECT player_name_fkey,
       school_name_fkey,
       sum(t1.score) AS sum_scores
FROM (
  SELECT player_name_fkey,
         school_name_fkey,
         score,
         rank() OVER (ORDER BY score DESC) AS scorerank
  FROM simple
    LEFT JOIN players ON players.name = simple.player_name_fkey
  WHERE category = 'Benjamin'
    AND gender = 'Masculin'
  GROUP BY simple.player_name_fkey,
           simple.school_name_fkey,
           simple.score
) t1
WHERE scorerank <= 2
GROUP BY player_name_fkey,
         school_name_fkey

Unfortunately, this query returns only the top 2 scores over all players. How can I change it to get what I need?


texts:
I'm using mysql 5.7 on ubuntu server 14.04, and engine innodb.
I have a table that are very huge (about 66 Go), I insert and delete from it without problem, but some query of select are taking lot of time till they are abondoned from java.

When I do select count(*) from TABLE, it take about 10 minutes.

I export all my database and data to another server ubuntu server 16.04 mysql 5.7, and do the same query and it take about 2 minutes.

Why this difference happends on the same table and same data?

Here is my config files :

First server (10 minutes)

[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket  = /home/mysql_data/mysqld.sock
datadir = /home/mysql_data
log-error   = /var/log/mysql/error.log
bind-address    = 127.0.0.1
symbolic-links=0
open_files_limit = 10000
query_cache_type=0
query_cache_size=0
innodb_buffer_pool_size = 36G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_buffer_pool_instances = 8
innodb_thread_concurrency = 8
innodb_io_capacity = 1000
innodb_io_capacity_max = 3000
innodb_stats_on_metadata = 0
innodb_buffer_pool_dump_at_shutdown = 1 # MySQL 5.6+
innodb_buffer_pool_load_at_startup = 1 # MySQL 5.6+
innodb_buffer_pool_dump_pct = 75 # MySQL 5.7 only
innodb_checksum_algorithm = crc32 # MySQL 5.6 or newer
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_file_per_table

Second server (2 minutes)

[mysqld_safe]
socket  = /var/run/mysqld/mysqld.sock
nice    = 0
[mysqld]
user    = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket  = /home/mysql/mysqld.sock
port    = 3306
basedir = /usr
datadir = /home/mysql
tmpdir  = /tmp
lc-messages-dir = /usr/share/mysql
bind-address    = 127.0.0.1
skip-external-locking
key_buffer_size = 16M
max_allowed_packet  = 16M
thread_stack    = 192K
thread_cache_size = 8
query_cache_limit   = 1M
query_cache_size = 16M
myisam-recover-options = BACKUP
log_error = /var/log/mysql/error.log
expire_logs_days    = 10
max_binlog_size = 100M


texts: JOIN 2 Tables on 1 Table and Select their separate quantity

texts:
Is there any other way i can display the quantity field i have from 2 different tables where their ID's are referenced on 1 table.

Example:

shipment_box table, columns: shipment_box_id, box_code, quantity, date_created

shipment_consumable table, columns: shipment_consumable_id, shipment_box_id, item_id, date_added

shipment_unit table, columns: shipment_unit_id, shipment_box_id, item_id, date_added

The requirements would be:

The user must be able to create a shipment box.
The created shipment box must be able to accept a mix of unit and consumable items.
The report generated must display all shipment box's contents and quantity per item_code.

Below would be a visual representation of my database tables:

my initial idea was to create a separate SELECT Query for shipment_unit and shipment_consumable joining to shipment_box but if there's a way i can do this around without having to UNION these two queries, it would be more ideal.

below is my draft query (not tested yet):

SELECT
    sb.box_code,
    i.item_code,
    COUNT(su.shipment_unit_id) 'Qty'
FROM
    shipment_box sb
JOIN
    shipment_unit su
ON
    su.shipment_box_id = sb.shipment_box_id
JOIN
    item i
ON
    i.item_id = su.item_id
GROUP BY
    sb.box_code,
    i.item_code

UNION

SELECT
    sb.box_code,
    i.item_code,
    COUNT(sc.shipment_unit_id) 'Qty'
FROM
    shipment_box sb
JOIN
    shipment_consumble sc
ON
    sc.shipment_box_id = sb.shipment_box_id
JOIN
    item i
ON
    i.item_id = sc.item_id
GROUP BY
    sb.box_code,
    i.item_code

i was thinking of something similar to a hypothetical query like below:

SELECT
    sb.box_code,
    i.item_code, -- Display item_code from joined item table
    IF(COUNT(su.shipment_unit_id) = 0, COUNT(sc.shipment_consumable_id)) 'Qty' -- Display quantity of shipment_unit and shipment_consumable items
FROM
    shipment_box sb
JOIN
    shipment_unit su
ON
    su.shipment_box_id = sb.shipment_box_id
JOIN
    shipment_consumble sc
ON
    sc.shipment_box_id = sb.shipment_box_id
JOIN
    item i
ON
    (
        i.item_id = su.item_id
    OR
        i.item_id = sc.item_id
    )
GROUP BY
    sb.box_code,
    i.item_code

i am open with a lot of ideas that can help me please. Thank you.


texts: Query Performance depending on parameter 21seconds vs. > 14hours

texts:
I have the following query:

SELECT DISTINCT DATEADD(ms,DATEDIFF(ms,0,[Time]),DATEADD(dd,0,DATEDIFF(dd,0,[DATE]))),
                [DATE],
                DATEPART(YY,[DATE]),
                DATEPART(MM,[DATE]),
                DATENAME(MONTH,[DATE]),
                DATEPART(DD,[DATE]),
                [Time],
                DATEPART(HH,[Time]),
                DATEPART(MI,[Time]),
                DATEPART(SS,[Time])
FROM [SourceTable]
WHERE ([DATE] BETWEEN '2012-01-08' AND '2012-01-08') AND
       DATEADD(ms,DATEDIFF(ms,0,[Time]),DATEADD(dd,0,DATEDIFF(dd,0,[DATE]))) NOT IN
       (SELECT [DateTime]
        FROM [DIM].[DateTime]
        WHERE [Date] BETWEEN '2012-01-08' AND '2012-01-08')
ORDER BY [DATE], [Time]
OPTION (MAXDOP 6)

When I use this specific date I had to cancel the query after 14hours running time, any other date the same query takes normally 21 seconds. I did check the query execution plan independent on what date I use it is the same, the amount of rows impacted by the query is for each day nearly the same (around 500'000 Rows). I checked the indexes and the statistics are up to date. How is such a behaviour explainable and how to fix this ?

The Hardware of the machine is sufficient (8 Cores, 192 GB RAM)


texts: Disabling MariaDB cluster multi master mode

texts: Is it possible to configure replication with one master node and 2 slaves in MariaDB like replication works in mongodb? I noticed that the default is multi master mode. Thanks in advance.


texts: Using Postgres COPY TO for data export without overwriting file

texts:
I'm running several queries with a script and psql. I am also exporting the results to a.csv using the COPY TO command. However, this overwrites the file a.csv when I would like to instead append the results to a.csv instead.

Is it possible using the COPY TO syntax perhaps there is some parameter I am missing? Or should I look at using other tools?


texts: Impact on Recovery Time Using A Single Perpetual Base Backup and WAL Archives

texts:
What would be the impact on recovery time when restoring a Postgres database from a backup where one base backup was taken at the start of the backup strategy and then only applying WAL archives from anywhere between a week, to a month, to even 3 or 6 months?
From the perspective of an Oracle RMAN incremental backup strategy, Oracle recommends weekly level 0 backups combined with daily level 1 incrementals, however, I am unable to find any such recommendations with regards to Postgres.
All Postgres Continuous Archiving and Point-in-Time Recovery articles mention only one base backup and then archiving WAL from that point onward, but how would this impact recovery time compared to having a weekly base backup and daily WALs strategy?
The reason why I’m asking this is because Redstor (the cloud backup solution we use) supports deduplication and backup archiving, so we’re trying to find the best backup strategy that will provide PITR where the total size of the backup on the backup server’s filesystem will always reflect the total size of the database in its current state. When it comes to restoring a backup, we can choose any 24 hour time period to restore from and replay from the backed-up WAL files to any point in time within 24 hours to bring the system to a current state.


texts:
I am making a small program where users makes posts or write blogs. On those posts, other users can like or dislike the post as in facebook or upvote or downvote the post as in stackoverflow. I would like to know a good database structure which is commonly used & the program works efficiently with that structure. I have two options

First

Post:

id   head   message   datepost   likes   dislikes
1     ab    anchdg     DATE      1,2,3   7,55,44,3

In the above way, id is the postid. In the likes column, 1,2,3 is the user's id who liked or upvoted the post or blog. 7,55,44,3 is the id of the users who disliked or downvoted the post or blog.

Second

Post:

id    head  message   datepost
1     ab    anchdg     DATE

Likes:

id    postid    userid
1       1         1
2       2         2

Dislikes:

id    postid    userid
1       1         7
2       1         55

In this way, I have to create two separate tables for likes & dislikes to get post's likes. In this way, the tables i.e. Likes & Dislikes will get heavily filled. This might make table heavy & processing slow.

So, I would like to know which is the better & standard way to achieve this task?


texts: SQL Server Group By Very Slow unless using temp tables

texts:
I have a simple query:

INSERT INTO #Values
SELECT 2, MONTH(f.Datum), SUM(Quant * Price) FROM r_Fakt_Otpr_Data fd INNER JOIN r_Fakture f ON fd.FK_Faktura = f.ID_Faktura
WHERE f.Type = 2 AND f.Stock = @stock AND f.Year = @year
GROUP BY MONTH(f.Datum)

It has about 10000 records to go through. It takes more than a minute.

If I omit GROUP BY and SUM it runs in less than a second.

Now, funny thing, if I split the query into SELECT and GROUP BY separately, using temp table, it runs in less than a second again:

CREATE TABLE #ValuesBuff
(
    Type int,
    Mnth int,
    Value float
)

INSERT INTO #ValuesBuff
SELECT 2, MONTH(f.Datum), (Quant * Price) FROM r_Fakt_Otpr_Data fd INNER JOIN r_Fakture f ON fd.FK_Faktura = f.ID_Faktura
WHERE f.Type = 2 AND f.Stock = @stock AND f.Year = @year

INSERT INTO #Values
SELECT 2, Mnth, Sum(Vakue) FROM #ValuesBuff
GROUP BY Mnth

Why would this be the case?

What should I do to instruct SQL Server to perform better. Obviously it is GROUP BY that is the culprit, but it's fairly simple query, and i can't wrap my head around it.

Best Regards,


texts: How can I write a routine to check, optimize and analyze all tables in databases in MySQL

texts:
How can I create a routine in MySQL to check, optimize and then analyse the tables?

Also how can I schedule this routine to run every night at set time?

Thanks


texts: How to suppress specific warnings in mysql error log when ignoring a table to replicate

texts:
Is there a way to make the mysql error log less chatty with certain warnings? I just threw in the replicate-ignore-table option in my.cnf so our health check table doesn't get replicated. However now I'm getting a couple warnings every minute telling me something that I specifically want to happen... and it's annoying, not to mention that the log will grow at a much faster rate now.

I thought adding slave-skip-errors=1237 would fix it, but it's not an actual error that's stopping replication.

Any ideas?

2015-07-16 21:51:02 31210 [Warning] Error log throttle: 4 time(s) Error_code: 1237 "Slave SQL thread ignored the query because of replicate-*-table rules" got suppressed.
2015-07-16 21:51:02 31210 [Warning] Slave SQL: Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules; Error log throttle is enabled. This error will not be displayed for next 60 secs. It will be suppressed, Error_code: 1237
2015-07-16 21:53:01 31210 [Warning] Error log throttle: 3 time(s) Error_code: 1237 "Slave SQL thread ignored the query because of replicate-*-table rules" got suppressed.
2015-07-16 21:53:01 31210 [Warning] Slave SQL: Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules; Error log throttle is enabled. This error will not be displayed for next 60 secs. It will be suppressed, Error_code: 1237
2015-07-16 21:54:01 31210 [Warning] Error log throttle: 2 time(s) Error_code: 1237 "Slave SQL thread ignored the query because of replicate-*-table rules" got suppressed.
2015-07-16 21:54:01 31210 [Warning] Slave SQL: Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules; Error log throttle is enabled. This error will not be displayed for next 60 secs. It will be suppressed, Error_code: 1237
2015-07-16 21:55:01 31210 [Warning] Error log throttle: 3 time(s) Error_code: 1237 "Slave SQL thread ignored the query because of replicate-*-table rules" got suppressed.
2015-07-16 21:55:01 31210 [Warning] Slave SQL: Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules; Error log throttle is enabled. This error will not be displayed for next 60 secs. It will be suppressed, Error_code: 1237
2015-07-16 21:56:02 31210 [Warning] Error log throttle: 2 time(s) Error_code: 1237 "Slave SQL thread ignored the query because of replicate-*-table rules" got suppressed.
2015-07-16 21:56:02 31210 [Warning] Slave SQL: Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules; Error log throttle is enabled. This error will not be displayed for next 60 secs. It will be suppressed, Error_code: 1237


texts: Custom unit of measurement conversion problem

texts:
Let's say I have something that represents an item, let's say Beer. I store it in a table "Items". I also have a table with units of measurements. Like box, as beers can be sold in boxes.

Now the problem is, the box can have different amount of beers, there can be a box of lets say 20 beers and there can be a box of 10 beers. So there are a couple of possible conversion factors depending on the item.

Also the item can be sold in different unit, like a box, pack and each box and pack can have different amounts of items inside.

How should I design my database to handle this problem?


texts: How would I implement interprocess communication from Oracle (10g R1) trigger using DBMS_ALERT?

texts:
Let me state up front that I'm not proficient in Oracle at all, MS SQL yes but not Oracle; which is why I'm asking this.

Current Setup

Oracle 10g Release 1 running on Red Hat Ent. 3 (no support)
Can not upgrade OS or Database
Can not extensively modify Database (read-only would be preferred)
Using Visual Studio 2013 and .net 4.5 for data access to Oracle
Using ODAP/ODP.NET 11.2 Release 5 (can not be newer due to using Oracle 10g R1)
Using System.Data.Oracle instead of the ODP.NET classes to avoid file bloat
Can not use Data Change Notification because it isn't supported in 10g R1

Under this current setup I can easily connect to the database, issue sql statements and read/write data. The application being written is to be used as a sort of adapter or bridge between the Oracle database and MongoDB where only a couple tables are meant to replicate changes into MongoDB.

The current implementation of this uses DBMS_ALERT and triggers to communicate events into the .net application at which point it will read the data, convert it and write it into MongoDB.

Here is an example trigger that is being used

CREATE OR REPLACE TRIGGER EXAMPLE.TABLENAME_ALERT
AFTER UPDATE OR INSERT OR DELETE
    ON EXAMPLE.TABLENAME
    FOR EACH ROW

BEGIN

    IF INSERTING THEN
        DBMS_ALERT.SIGNAL('DATA_INSERTED', :new.data_id);
        DBMS_OUTPUT.PUT_LINE('INSERTED');
    ELSIF UPDATING THEN
        DBMS_ALERT.SIGNAL('DATA_UPDATED', :old.data_id);
        DBMS_OUTPUT.PUT_LINE('UPDATED');
    ELSIF DELETING THEN
        DBMS_ALERT.SIGNAL('DATA_DELETED', :old.data_id);
        DBMS_OUTPUT.PUT_LINE('DELETED');
    END IF;

END;

If I issue the following SQL statements:

update EXAMPLE.TABLENAME set example_column = 'test' where data_id = 1;
update EXAMPLE.TABLENAME set example_column = 'test' where data_id = 2;
update EXAMPLE.TABLENAME set example_column = 'test' where data_id = 3;

The result is getting 3 separate DATA_UPDATED signals and 3 separate UPDATED lines in the database output. On the other hand, if I issue this statement:

update EXAMPLE.TABLENAME set example_column = 'test' where data_id <= 3;

The result I get is 1 DATA_UPDATED signal that contains the id of the last row updated and 3 separate UPDATED lines in the database output. I need to get all 3 signals.

My guess is, that because DBMS_ALERT.SIGNAL doesn't fire until a transaction is committed, only fires the most current signal at that time and a single batch update/insert/delete is considered 1 transaction then all I will ever receive is 1 signal from the database.

Is there a way I can get the multiple calls per trigger to DBMA_ALERT.SIGNAL to actually send all the signals? Or perhaps there is a better way for me to detect changes to data in the database from an external application?

EDIT I know this implementation has a code smell, but my hands are tied, I have to work with what we have and can't really change anything.


texts: PostgreSQL - duplicate data show and count

texts:
This is table.

CREATE TABLE cameras
(
  id integer NOT NULL DEFAULT nextval('sq_streams'::regclass),
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now(),
  exid text NOT NULL,
  owner_id integer NOT NULL,
  is_public boolean NOT NULL,
  config json NOT NULL,
  name text NOT NULL,
  last_polled_at timestamp with time zone DEFAULT now(),
  is_online boolean,
  timezone text,
  last_online_at timestamp with time zone DEFAULT now(),
  location geography(Point,4326),
  mac_address macaddr,
  model_id integer,
  discoverable boolean NOT NULL DEFAULT false,
  preview bytea,
  thumbnail_url text,
  CONSTRAINT pk_streams PRIMARY KEY (id)
)

that config object is of type json and containing json data inside it as well.

i have 5000 records in this. What i am trying to get is ,
There are 57 Cameras and they have everything different but 3 things are same. which are

'external_http_port', config->> 'external_host', config-> 'snapshots'->> 'jpg'

I want to create a query which will show me results as

external_http_port | external_host | config-> 'snapshots'->> 'jpg' | Count
"93.87.73.118"     | "1024"        |      "/oneshotimage.jpg"      | 57
"180.180.180.48"   |  "81"         |"/onvif/media_service/snapshot"|20


texts:
Suppose I have a DB2 database instance that manages a single database with 2 Schemas, say A and B
What would be the benefits (if any) of having the instance instead manage 2 databases with one database containing Schema A and the other containing Schema B?


texts: How do I design a database for a resource scheduling and allocation application?

texts:
I have two entities: Resource and Project. A Project runs for a certain amount of time (in calendar days), and I need to allocate several Resources to it (allocation interval - 1 work day, not an hourly basis).

Now if I create two tables (resource and project), how do I achieve this? How should the tables be designed? Do I create a new entry for every day and resource?

Any help is much appreciated!


texts:
Is there any effect if i interchange MySQL/MariaDB .frm files from dbs with similar structure on any CRUD operation?

note: the different is some tables have different fields, whether its existence, datatype, default value or any others.


texts:
In the context of the CREATE ACCESS METHOD DDL in PostgreSQL,

CREATE ACCESS METHOD name
    TYPE access_method_type
    HANDLER handler_function

What is a a high level description of what an "access method" is and what they are used for?


texts:
According to my understanding, a default schema should apply when
issuing SQL statements on tables using unqualified table names.

My problem is that the following statement fails, because the table name
cannot be resolved.

-- Table name : [mydb].[myschema].[mytable]
USE mydb
SELECT * FROM mytable

Msg 208, Level 16, State 1, Line 2
Invalid object name 'mytable'.

I have a database user defined based on a Windows login user mydomain\myuser with the following properties:

The login user is not a member of the sysadmin group, but only of serveradmin and public. According to the documentation, these memberships should not be a problem.
The mapped database user (also called mydomain\myuser) has the default schema myschema assigned.
The database user is member of db_owner.

When connecting with MS SQL Management Studio as mydomain\myuser, calling SCHEMA_NAME() returns dbo. This indicates that for some reason, the default schema myschema is ignored. This is consistent with the failed SELECT statement.

But why is the default schema ignored?


texts: I have a SSIS package task that is displaying messageboxes even those that code in the task is commented out when I run it. How do I stop that?


texts: Find common attributes among given products in SQL Server

texts:
I am trying to find all attributes that are common to a given set of products (Stock_ID numbers). I have a table called Stock_Attributes that contains product info. It looks something like this:

+----------+--------------+------------+
| Stock_ID | Attribute_ID | Value      |
+----------+--------------+------------+
|     1001 |            2 | Farmhouse  |
|     1001 |            8 | Blue       |
|     1001 |            9 | Bronze     |
|     1001 |           10 | Metal      |
|     1001 |           10 | Wood       |
|     1002 |            2 | Industrial |
|     1002 |            9 | Chrome     |
|     1002 |           10 | Metal      |
|     1003 |            2 | Industrial |
|     1003 |            8 | Red        |
|     1003 |           10 | Glass      |
|     1003 |           10 | Metal      |
+----------+--------------+------------+

Stock_ID and Attribute_ID are both have INT as the data type. Value is a sql_variant since some attributes are Dates or Bits. As you can see, any product can have any combination of attributes and more than one attribute of the same type. The desired result for the above sample set would be one just one row, since these products only have one attribute in common (typically there will be several in common):

+--------------+------------+
| Attribute_ID | Value      |
+--------------+------------+
|           10 | Metal      |
+--------------+------------+

I do not need to return the Stock_ID numbers in this query since we are starting with that information in a temporary table called #TempItemNumbers. I simply need to return a set of Attribute_IDs and Values that are common to all Stock_ID numbers listed in #TempItemNumbers, which is generated via user input.
I do not typically use cursors or dynamic SQL so I am not very familiar with these methods, but I'm thinking this problem will require one or both.
Please help!


texts:
I am trying to work out a special UNIQUE constraint: there are many columns in a Users table but only these two are relevant for the question:

id bigint,
active boolean,
...

I want to allow multiple rows to have the same id - but only one of them may have active = true.

E.g. There can be many rows with id = 1 and active = false, but only one can have id = 1 and active = true.


texts: How do I get full details of each person when joining tables

texts:
I know this has to have a simple solution and I am not seeing it.

I have a table, person, which contains all information about each person such as first name, last name, age, address.

I have a second table called consumer_client which contains a row containing person id for each consumer and client like this:

id | consumer_person_id | client_person_id

What I want is to fetch both full names when I join person to client_consumer like this:

id | Joe Mama | Mike Smith

I cannot seem to get that with a simple JOIN syntax like

SELECT * FROM person JOIN client_consumer WHERE person.person_id = cc.person_id

as it always returns just the consumer_person_id and client_person_id along with other information in person. I want it replaced with the full name instead of person_id.


texts: What schema for different relation from two tables to another?

texts:
There are three tables instructors, meeting_points and bookings. Here are the relation I want:

1 instructor has 0, 1 or n meeting_points
1 booking has 1 meeting_point
1 meeting_point can be linked to 1 instructor + n bookings or just 1 instructor
There are no direct relation between instructors and bookings

I don't know how to represent this properly in a PostgreSQL table (or even in MySQL or other SQL). I can use a foreign key on instructor id in meeting_points table and and add a foreign key on meeting_point id in bookings table but I'm not sure it's the best way (I think it's confusing). How can I represent these relations? Do I need a pivot table for meeting_points with nullable relations?

It's my first post on dba stackexchange, I hope it's clear enough and not a duplicate post (or off topic)

EDIT - Reading answers, I think I must add a little more information.

Meeting points are declared by instructors. So, an instructor says: "you can meet me at X, Y or Z point". And other people (let says these are learners) can book a lesson in a particular meeting point: this is a booking.

In other words, the relation between instructor and meeting point is: an instructor declares available meeting point with him to potential learners. And a learner (learner does not matter in my question) says: "ok, I ask for a booking in that meeting point" (the meeting point is one of the available meeting point declared by the instructor).

Now, other (maybe useless) information:

Meeting points are not shared between instructors, instructors choose and "write" their own named and geo-localized meeting point (it can seems strange, but trust me!).
A booking has different status (accepted, pending, canceled) and is "created" by learners, so there can be n bookings for one lesson on x meeting points (lesson does not matter in this question). But the meeting points are only one of the meeting declared by the instructor who made lesson available.
Bookings have one foreign key on lesson, and one foreign key on learner.


texts: Encrypt connection between SSRS and SQL Server

texts:
We are working under a requirement that all connections to SQL Server be encrypted.  After setting up a Reporting Server (2016), I see an unencrypted connection to the SQL Server.  There are no data sources set up yet, so this is not a connection associated with report data sources, it is just the connection that RS makes to the ReportServer database.  I do not see an option for encrypting the connection anywhere in the setup where the database and credentials are configured.

How can this connection be configured from the client side to use encryption?  I don't want to force encryption on the server side as this may interrupt other connections (that we are working toward getting encrypted).  Yes, we have a cert installed and working--can connect with encryption from SSMS and other clients.

This is NOT about setting up RS to use HTTPS on the portal.  That is set up and working.


texts: ExclusiveLock on related table due to update?

texts:
Recently ran into the following:

Consider the tables created by this:

create table table_a (
        id  bigserial not null,
        last_update timestamp,
        primary key (id)
);

create table table_b (
        id  bigserial not null,
        primary key (id)
);

alter table table_b add column a_id int8;
alter table table_b add constraint fk_c foreign key (a_id) references table_a;

And then, the following query(which takes a bit longer than the example shown here might have you suspect):

update table_a set last_update = now() where id in(<list of ids>);

I observed that this update query created an ExclusiveLock on table_b.

That seems really weird, as table_b isn't being used by the query at all. Or so I think.

What's going on here? Does where id in create this lock implicitly?

Either way, how can I avoid this? Where does my understanding fail?

Also, the query that showed me this lock being taken on table_b:

SELECT a.datname,
      c.relname,
      l.transactionid,
      l.mode,
      l.GRANTED,
      a.usename,
      a.current_query,
      a.query_start,
      age(now(), a.query_start) AS "age",
      a.procpid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.procpid
JOIN pg_class c ON c.oid = l.relation
WHERE pid <> pg_backend_pid()
ORDER BY a.query_start;


texts: Express union which is dependent on other results

texts:
I would like to make a union of two tables that are actually in a hierarchical constellation.

How would I write that in relational algebra? Say table A is the parent of B in a 1:n relationship.

First I do a selection on A and then I want to only make a union with these entries in B that would be in a join with selection on A.

I would like to write it the way that a database would evaluate it.

Is there such a thing as a conditional union?


texts:
Is there a built-in Oracle feature that extends Real-Time SQL Monitoring to
 historical activity?  I am specifically interested in getting wait events aggregated at the SQL operation level, not just per-statement.

Operation level information is available in DBA_HIST_ACTIVE_SESS_HISTORY.  Many AWR reports use that table, but do any of them display information at the operation level?  Currently I am getting this data by joining DBA_HIST_ACTIVE_SESS_HISTORY and DBMS_XPLAN.DISPLAY_AWR, but it seems like there should be a built-in feature to do this for me.  Before I build my own tool I want to ensure someone else hasn't already built it.

This missing feature may sound like a minor inconvenience but using the actual execution statistics, or even a sample of them, makes tuning so much easier.  For example, below is a real SQL Monitoring Report for a recent problem, modified to fit here.  With operation-level information there was no need to guess which step was slow, or even examine the waits.  The problem was quickly found - the indexes were not disabled before a large INSERT.

SQL Monitoring Report

SQL Text
------------------------------
<SQL statement...>

Global Information
------------------------------
<metadata like run status, session, sql_id, duration, etc...>

Parallel Execution Details (DOP=64 , Servers Allocated=128)
...

SQL Plan Monitoring Details (Plan Hash Value=2062479242)

=================================================================================
| Id |     Operation          | Activity |          Activity Detail             |
|    |                        |   (%)    |            (# samples)               |
=================================================================================
...
|  1 |   PX COORDINATOR       |     0.19 | enq: KO - fast object checkpoint (21)|
|  2 |    PX SEND QC (RANDOM) |          |                                      |
|  3 |     INDEX MAINTENANCE  |    84.70 | buffer busy waits (2801)             |
|    |                        |          | enq: HW - contention (29)            |
|    |                        |          | enq: TX - allocate ITL entry (25)    |
|    |                        |          | free buffer waits (61734)            |
|    |                        |          | write complete waits (10365)         |
|    |                        |          | Cpu (341)                            |
|    |                        |          | enq: FB - contention (1632)          |
|    |                        |          | enq: TX - contention (53)            |
|    |                        |          | db file sequential read (3717)       |
|    |                        |          | read by other session (641)          |
|  4 |      PX RECEIVE        |     0.02 | Cpu (18)                             |
|    |                        |          | resmgr:cpu quantum (1)               |
...
=================================================================================

There are some other methods that produce similar information, such as tracing, /*+ gather_plan_statistics */, or alter session set statistics_level=all;.  But those methods are only useful in a development environment.  I don't have the luxury of being able to re-run statements with different settings.


texts:
I've got 2 SQL tables: NAMES and RELATIONSHIPS Currently NAMES just has 2 columns: name and name_id. The other is a list of relationships between people in the NAMES table.   It has 3 columns: primaryperson_id, relatedperson_id, and relationship_id.  The primaryperson_id and related_person_id are name_ids from the NAMES table.  Each person in NAMES can have multiple entries in either the primary or related columns of RELATIONSHIPS (is that a many-to-many relationship?).

This query works:

SELECT  people.name AS 'primary', relationships.related_person_id AS relatedto
FROM relationships
JOIN people
ON people.name_id=relationships.primary_person_id
ORDER BY people.name_id;

But I'd like to show a name (i.e. text) in the relatedto column rather than the id number.  How can I do that?


texts: Data Flow Task runs successfuly but wrong rows are updated

texts:
I created a simple Data Flow Task, that has two Data Flow components (I'm new to SSIS, so the follwoing may be trivial):

OLE DB Source - it simply connects to some table on some database
Script Component -

Its Input Columns are two of the table columns:

a. Column A (int type) - whose Usage Type is the default ReadOnly.

b. Column B (uniqueidentifier type) - whose Usage Type I defined as ReadWrite.

I called the Input Name: ABInput.

Edit: I added an Output Column - which is column B.
Edit:  OLE DB Destination - it connects to the same table as the OLE DB Source, and its Input Column is column B (that was updated on the Script Component).

Inside the script, inside the function ABInput_ProcessInputRow(ABInputBuffer Row), I do the following:

public class ScriptMain : UserComponent
{
    public Dictionary<int, Guid> Dict { get; set; }
    public override void PreExecute()
    {
         Dict = GetIdsForB();
    }

    public override void ABInput_ProcessInputRow(ABInputBuffer Row)
    {
        if (!Row.A_IsNull)
        {
            Output0Buffer.AddRow();
            Output0Buffer.B = Dict[Row.A];
        }
    }

    // ... other generated code and GetIdsForB code ...
}

Edit:

The task runs successfuly, but when I then go and check the table on the database - I see that wrong rows were updated (in fact, the updated rows seems to be pretty random), i.e., a row with a null value on A has a non-null value for B, and vice versa.

I presumed that SSIS would take the rows that were added to Output0Buffer, and match them to those on the database's table (based on the table's primary key, maybe?), but apparently that doesn't happen.

How can I make it work?


texts: REGEXP_REPLACE is matching but not replacing with empty string

texts:
Given a table with a column called files_name, and you want to match everything after the match with regex, and replace it with nothing.

I have this code but it matches everything, but it's always changed 0.

UPDATE files
SET files_name = REGEXP_REPLACE (files_name, 'S[0-9]?[0-9]?[0-9]E[0-9]?[0-9]\K.*', '')
WHERE type = 5;

A online regex tester says my regex is correct, and is matching as desired, yet no matter what I put into the second '' it never replaces the match.

I'm using mariadb 10.3.17


texts: Automatically and robustly restarting a Galera cluster

texts:
Galera clusters, somewhat famously, have a requirement that nodes need to be shut down and brought back up again in a certain order.
There are a few questions here and on other sites about recovering from unclean shutdowns, which may cause split brain situations in the cluster. For manual restarts, I usually follow a procedure that relies on checking files and potentially even database state and is, frankly, too fragile and too much manual work for my taste.
I think it should be possible to encode this manual procedure right into the systemd unit files of a cluster, so that when the failure condition is resolved and machines boot back up, the cluster members also come back up in the correct sequence automatically. This will definitely involve listing all cluster member nodes in some central location, but should be re-usable apart from that.
So, is there a commonly used, best-practice way to re-start a Galera cluster after any sort of shutdown, automatically during system startup?


texts: Why does server name need to be specified when accessing local instances?

texts: Even though I have direct access to the physical database machine, I have to specify dbmachine\sql in the connection field, rather than just the instance name. Why is that?


texts:
I have a situation where I have an hierarchical collection of items that I want to report on.

Let's say I want to track each individual item sold, so I have a ItemSellFacts table. But those items are sold as part of a shopping cart and I want to track those too, so I have a ShoppingCartFacts.

This second fact table is more like pre-processing of the previous fact table, where I keep track of max/min item price in the cart and so on. I could generate all that based only in the ItemSellFacts everytime, but this second table is where we want to make live dashboards to plug on.

So it seems natural to me that I can reference the shopping cart key as a foreign key in the item sell fact. Of course I'll avoid issuing a join between those two fact tables like the plague, but several people told me not to do that even so.

Is this really a flaw in the design? What could be the alternative?


texts: PostgreSQL 9.6.17 COALESCE function error

texts:
I'm trying a SQL query with the function COALESCE in PostgreSQL 9.6.17, but it produces an error:

invalid syntax for type double precision: ""

Example query:
SELECT COALESCE(date_part('year', s.date_pp),'')
FROM public.sendoc s.

This query is working on PostgreSQL 9.3.17.
How to get around the error at the server DB level without changing the source code of the program?


texts: Can you call a variadic function from a variadic function?

texts:
I have written a function which sums columns together (if there is a built in for this, that would also be good to know) and another which calls that function and replaces the sum with a different value if it is zero or null. When trying to call the second function I get the following error:

ERROR:  function enc_sum_columns(numeric[]) does not exist
LINE 1: SELECT            enc_sum_columns(summed_values::numeric[])
HINT:  No function matches the given name and argument types. You might need to add explicit type casts....

The sum columns function is as follows:

create or replace function enc_sum_columns(VARIADIC cols_to_sum numeric[], OUT total numeric)
RETURNS numeric AS
$BODY$
BEGIN
 SELECT INTO total SUM(cols_to_sum[i])
   FROM generate_subscripts(cols_to_sum, 1) g(i);
END;
$BODY$
  LANGUAGE plpgsql

And the function that calls it:

CREATE OR REPLACE FUNCTION public.enc_sum_with_choice_for_zero(
    IN replacer numeric,
    VARIADIC summed_values numeric[],
    OUT total numeric)
  RETURNS numeric AS
$BODY$
 BEGIN
   SELECT INTO total enc_sum_columns(summed_values::numeric[]);
   IF total=0 OR total IS NULL THEN
   total = replacer;
   END IF;
 END;
$BODY$
  LANGUAGE plpgsql VOLATILE

And an example of calling the function :

SELECT cov_s_fib, cov_s_cop, enc_sum_with_choice_for_zero(500::numeric, cov_s_fib::numeric, cov_s_cop::numeric) from test_dynamo_with_geom1

Here is some expected output from the query above:

|---------------------|------------------|------------------|
|     cov_s_fib       |     cov_s_cop    |      total       |
|---------------------|------------------|------------------|
|          1          |         3        |          4       |
|---------------------|------------------|------------------|
|          0          |         0        |          500     |
|---------------------|------------------|------------------|
|                     |                  |          500     |
|---------------------|------------------|------------------|
|          0          |                  |          500     |
|---------------------|------------------|------------------|
|                     |        1         |          1       |
|---------------------|------------------|------------------|

Can I not pass a variadic varaible to another function like that? Did I just mess up something syntactically? Originally, I had the code in enc_sum_columns inside of enc_sum_with_choice_for_zero and it worked fine.


texts: Audit login trigger for permission changes sql server

texts:
In my office some one changed the permissions of users on production database. so after that i have decided to write a ddl kind of trigger to audit permission changes. I have googled for this but i couldn't find a proper solution. So can you people suggest me few links or any sample code?

I would like to capture who changed the permissions and when permissions got changed.

Thanks in advance.


texts: What prerequisites, if any (such as version number, database objects in use), does an existing Oracle database have to fulfil in order to be able to migrate it straight away to Oracle Autonomous Database? I've read the FAQ but it doesn't address this.


texts:
The docs are unenlightening:

ALL_TABLES:

Provides summary information about tables in a Vertica database.

TABLES:

Provides information about all tables in the database.

The reason I'm asking is that users are confused about why they can see some tables in one but not the other. It's related to permissions, but it's not clear exactly how.
For example, a user is telling me they have access to query a table. However, that table doesn't show up in TABLES and its columns don't show up in COLUMNS, but the table does show up in ALL_TABLES.
So, this leads to the following questions:

What's the difference between ALL_TABLES and TABLES?
How can users see a list of all the tables (and columns!) they have access to query?


texts:
I am using AWS RDS Postgres's and RDS's logical replication support

(Edit: Note, this is not the same as the logical replication featurein PostgreSQL 10. It's AWS RDS functionality based on the same underlying logical decoding feature.)

I'm replicating Postgres -> Postgres . Basically consolidation a lot of DB's into one db.

The problem i have is when no activity in a database for some time disk is filling up . I want to control how much storage can be used by WAL . I adjusted the wal_segments to keep. Still disk is filling up .
Rather than doing some dummy update to a table , are there any parameters i can set to control the disk usage .
Current parameters are

wal_keep_segments =4
wal_max_size =128
wal_min_size 16

Murali


texts: Need advice on storing pincode system (Zip code) and Shipping company Service Details

texts:
I am in the process of creating a system that could identify shipping companies (Fedex, UPS, etc.) from a given pincode (zip code) for a e-commerce platform.

The shipping companies will provide a list of pincodes where they provide pickup and delivery. So when we get an order for a product, with the pincode of the seller and with pincode of the buyer I need to get a list of shipping companies who provides both these services (i.e. pickup and delivery).

My initial plan is to create a table structure like the following one:

Table shipping_companies

id | company
1  | Fedex
2  | UPS
3  | DHL

Table pincodes

id | pincode | shipping_ids_pickup | shipping_ids_delivery
1  | 263152  |         1,2         |       2,3

The pincodes table will have the pincodes and will have two columns one for pickup and another for delivery.

So when a user purchases a product I need to find a shipping company that has pickup from the sellers location and also has delivery at buyers location.

If we are adding another shipping company in the future I can update the  shipping_ids_pickup, shipping_ids_delivery for a pincode by appending the id.

Can someone suggest a better solution. Is there any issue with current approach.


texts: How to refresh Oracle data dictionary to remove deleted tablespace and datafiles?

texts:
Long story short we have a server with two Oracle instances running on it: ora1 and ora2. Somebody defined an undo tablespace for ora2 and provided the same datafile of the undo tablespace datafile of ora1 that was already running, so datafile got corrupted and ora1 was shutdown. When trying to reopen the instance ora1, we were faced with the below error:

ORA-01159: file is not from same database as previous files - wrong database id

We tried to alter database datafile .... offline drop; and it went fine without errors but that also didn't allow us to open the database, so we backed up controlfile to trace and re-edited the file removing that corrupted datafile, and ran the new control file only to get the below error this time:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01173: data dictionary indicates missing data file from system tablespace
Process ID: 9460
Session ID: 355 Serial number: 9

Noting that the corrupted datafile is now not listed when selecting from v$datafile nor the tablespace associated to it from v$tablespace.

Is there anyway to edit the data dictionary that Oracle compare upon opening and causing the error? My ultimate goal is to open the database without having to reinstall it, and I'm okay with losing the data as I have an expdp dmp that I can restore for the schema I am using.

Database is Oracle 11gR2 on Oracle Linux server 6.3


texts: Is it require to fill all tables inside of the database?

texts:
Now,I created upper that table and in order to test I will give 3 textboxes values as below.

SqlCommand cmd = new SqlCommand
("Insert Into dbo.PrnInf(PrnName, PrnSurName, PrnEgn )     Values
('"+txtPrnName.Text+", "+txtPrnSurName.Text+","+txtPrnEgn.Text+"')", conn);
cmd.ExecuteNonQuery();

However,I'm getting the error in the "cmd.execute".Also,I defined the primary key and identity specification on the primary key.
the moral of the story,is it require to fill all tables inside of the database?

Best Regards,


texts: Automatic deduplication (normalization) of strings in MySQL

texts:
The question is whether MySQL can use separate physical storage for strings transparently, but with the same operations that are applicable to varchar, including %.

I have a large number of duplicated strings in my MySQL database: for example, many customers have the same first name. To save space, especially in indexes, and thus speedup the queries, I would prefer to physically store only an ID and store the actual string elsewhere: instead of varchar(30)s

| Mary | Smith  | New York |
| Mary | Cooper | Chicago  |
| John | Cooper | New York |

I want to only store ints

| 1 | 2 | 3 |
| 1 | 4 | 5 |
| 6 | 4 | 3 |

where the IDs refer to the strings:

| 1 | Mary     |
| 2 | Smith    |
| 3 | New York |

I can do it manually and use joins to map between strings and IDs in each query, but my question is whether there is any property of a field to tell MySQL to handle this transparently for me, so that in the queries I operate with the table as if it contained strings but the underlying physical storage for strings would be separate?

In my case the DB is rarely (if any) updated but frequently read, the queries being rather complex and slow, so I need to optimize for select, not for update.


texts: Error with SQL stored procedure output type

texts:
I have a table 2 tables, namely DAH_Errors and Intraday_Errors, and I want to calculate some statistical performance indicators, fore example the NMAE over a specified range of rows. The range is specified by 2 variables @fromdate and @todate, as shown below:

DECLARE @fromdates VARCHAR(20)='2017-12-11 00:15:00'
DECLARE @todates VARCHAR(20)='2017-12-12 00:00:00'
DECLARE @fromdate SMALLDATETIME=CAST(@fromdates AS SMALLDATETIME)
DECLARE @todate SMALLDATETIME=CAST(@todates AS SMALLDATETIME)

DECLARE @nmae1 DECIMAL(20,5)
DECLARE @nmae DECIMAL(20,5)
DECLARE @nrmse1 DECIMAL(20,5)
DECLARE @nrmse DECIMAL(20,5)

In order to calculate the NMAE, I have created a the procedure get_nmaev2, which is the following:

CREATE PROCEDURE [dbo].[get_NMAEv2]
        @forecasttype VARCHAR(MAX),
        @fromdate2 SMALLDATETIME,
        @todate2 SMALLDATETIME,
        @nmae DECIMAL(20,5) OUTPUT
AS
SET NOCOUNT ON;

DECLARE @aux VARCHAR(MAX)='Hello'
IF @forecasttype='DAH'
    SET @aux='SELECT '+@nmae+'=AVG(ABS(['+@forecasttype+'_Errors].[Forecast]-['+@forecasttype+'_Errors].[Metering])/CAST(['+@forecasttype+'_Errors].[Capacity] AS DECIMAL(20,5)))
    FROM dbo.'+@forecasttype+'_Errors
    WHERE ['+@forecasttype+'_Errors].[DateTime(UTC)]>='+@fromdate2+' AND ['+@forecasttype+'_Errors].[DateTime(UTC)]<='+@todate2+' AND ['+@forecasttype+'_Errors].[NAE] IS NOT NULL;'
    EXEC(@aux)

IF @forecasttype='Intraday'
    SET @aux='SELECT '+@nmae+'=AVG(ABS(['+ @forecasttype+'_Errors].[Forecast]-['+@forecasttype+'_Errors].[Onlline])/CAST(['+@forecasttype+'_Errors].[Capacity] AS DECIMAL(20,5)))
    FROM dbo.'+@forecasttype+'_Errors
    WHERE ['+@forecasttype+'_Errors].[DateTimeUTC]>='+@fromdate2+' AND ['+@forecasttype+'_Errors].[DateTimeUTC]<='+@todate2+' AND ['+@forecasttype+'_Errors].[NAE] IS NOT NULL;'
    EXEC(@aux)

The variables @fromdate2,@todate2 are used in the WHERE statement in order to specify the range over which I will calculate the NMAE.

I then execute the following, in order to get my result:

EXEC dbo.get_nmaev2
     @forecasttype='DAH',
     @fromdate2 =  @fromdate,
     @todate2 = @todate,
     @nmae=@nmae1 OUTPUT

What I want to do, is to store value of the select statement in the variable @nmae, and output that to the variable @nmae1, shown in the first script where I call execute the procedure. However, I get the following error message:

Msg 8114, Level 16, State 5, Procedure get_NMAEv2, Line 16
Error converting data type varchar to numeric.

I can not find what causes the error, so I would really appreciate some help here. Does this happen because I am trying to include the variable @nmae in the dynamic SQL statement;

I know what the error itself actually means, but I simply cant find what causes it :)


texts: Backup on Share NFS fails - Windows Server 2008 R2

texts:
I've a network disk where backups are stored every night, but after some hour the backup fails with the following error:

  Executing the query "BACKUP DATABASE [foo] TO  DISK = N'\\XXX.XX.XX...." failed with the following error: "Cannot open backup device '\\XXX.XX.XX.XX\vol\BACKUP\BACKUP\foo\foo_backup_2019_07_04_010253_4929321.bak'. Operating system error 1450(Insufficient System Resources Exist to Complete the Requested Service.).

or else:

  ...failed: 121(the semaphore timeout period has expired.)

To mount this share NFS i've used this command:

mount -o mtype=hard XXX.XX.XX.XX:/vol/BACKUP T:

then in registry editor i've created two DWORD values (AnonymousUid and AnonymousGid) set to 0.

I've tried also to increase KeepAliveTime registry setting but it was uncessful.

If i modify the backup job in SQL Server Management Studio to store to a local disk (eg. D:) it runs without problem but then a free space problem will be raised.

There is something i can do to solve my problem?

Hope everything is clear


texts: How to Update Date in each row with different dates in MySQL

texts:
I want to update date column of the table with dates in the increment of 1 for all rows.
How do I do it in MySQL?
Table: tbl_question_of_the_day
Column: date
So the output that I want is:
Row 1: Today
Row 2: Tomorrow
Row 3: Day after tomorrow
...and so on.
There is an ID column as primary key.
Thanks!


texts:
I'm using ORACLE 12c. I have 1 CDB and 1 PDB (for what I know).
To practice, I have created 2 listeners. I have made the appropriate settings in my tnsnames.ora file.

Observations:

when I just run lsnrctl start i.e. without specifying one on my listeners name, **TNSLSNR actually loads a listener located in /ora01/app/oracle/product/12.1.0/db_1/bin/tnslsnr (this is the path indicated by ps)
Before running lsnrctl start, there is no listener.ora in $ORACLE_HOME/network/admin. I have renamed it to xxxxxlistener.ora for testing what "zombie" listener was loaded by simple lsnrctl start. The worst part is that the response message says it loaded a listener from $ORACLE_HOME/network/admin/listener.ora. This is AWFUL since I renamed that file to xxxxxlistener.ora

There is a ghost file on my computer. Please help me as I can't even practice my custom listeners.

I can't practice my custom listener as if I don't put them on port 1521, I can NEVER user them to connect on the DB. That "ghost" listener seems to be messing things up


texts: Why are there multiple plans for query under forced parameterization?

texts:
I see the following query in an Azure SQL Database (that has Forced Parameterization = FORCED, Parameter Sniffing = ON, and Query Optimizer Fixes = ON) from within Query Store:

delete from [CMS_WebFarmTask]
where [TaskIsAnonymous] = @0
and [TaskID] not in ( select [TaskID] from [CMS_WebFarmServerTask] )
and [TaskCreated] < dateadd ( minute , @1 , getdate ( ) )

It has a large number of plans (17 in the last hour), despite both parameters being integers with identical values in each plan:

        <ParameterList>
          <ColumnReference Column="@1" ParameterDataType="int" ParameterCompiledValue="(-3)" />
          <ColumnReference Column="@0" ParameterDataType="int" ParameterCompiledValue="(0)" />
        </ParameterList>

Why isn't only one plan being used?


texts:
I was trying to add a new replica set to my mongodb sharded cluster but I keep getting the following error:

{
    "ok" : 0,
    "errmsg" : "Could not verify that config servers were active and reachable before write",
    "code" : 25
}

I checked all the config servers and they are up and running.The problem is new as I can previously add shards without any issues.

The MongoDB version I am using is 3.2 running on Ubuntu 16.02.

How can I fix this?


texts: SSAS - Inferred Dimension Attributes - are they necessary?

texts:
I have a cube with fact tables that have a parent-child relationship: Person-Case. Some dimensions are inferred from the parent, e.g. State in the example below. Person does not have a foreign key to the State dimension, but Case does, and through the Person-Case relationship the State is inferred. By doing it this way, I end up with lots of "redundant" dimension attributes from these inferred dimensions.

If I want to query the cube, for instance to obtain persons by state, I have to use the dimension attribute of the fact dimension that I am querying, but it is not possible to mix them with attributes from other dimensions. In other words, the red relationship below is not created, and if I drop the State dimension by itself into the Pivot control, I don't get a breakdown by state. I have to use the inferred State attribute of the Person dimension.

Is there a better way of doing this, so that State exists only once in the cube? Is it not valid to have hierarchical facts in a single cube?


texts: SQL Server 2005: Job scheduled every 4 hrs, but sp_help_jobactivity shows unexpected value for next_scheduled_run_date

texts:
I have done a lot with sql and jobs, and this is throwing me off.

My job is scheduled as shown below.

I can run the job interactively from SSMS by starting the job, it runs, all is well.

It is currently Jan 27 at 11 AM local time.
But sp_help_jobactivity returns a next_scheduled_run_date of

next_scheduled_run_date
2012-01-27 12:01:00.000

I expect it to be four hours from the last time I ran it, or from when I created the job and schedule (which I created at the same time).

Observed Problem: While the SSMS GUI displays "12:01 AM" the data in next run shows "12:01:00" which is "12:01 PM"

What am I missing?
When will the job next run?

Thanks!


texts: How to handle billions of rows in MySQL for biometric project?

texts:
My MySQL table looks like this.

animal_id (mediumint) | dna_hash (binary 10)

Currently, we have about 2 billions different dna_hash and it will be increasing every day.

We need to search the animal by dna_hash. So, I have an index on dna_hash.

Currently, I'm starting to worry because after I imported around 20 million rows, my index on dna_hash is over 500MB. If I keep on adding, the final index size will be over 50GB which is very expensive to operate.

So, I just wondering if there are any other possible way to reduce the index size? Or, should I just upgrade my hardware.

Thanks.


texts:
I am creating a database for production work (specifically lab testing).
Most Work is for production, therefore performed strictly according to the Procedure for that Product.  By itself, this is easy to model. The Work references the Procedure as it contains how the work is done:

Example Schema
Work:      Work_id, Procedure_id, {other non-relevant fields}
Procedure: Procedure_id, Product_id, Machine_id, Material_id, RunMinutes

Two exceptions (overrides and special testing) add much complexity to the design.
Question: Given the two exceptions below, how should I store the Procedure fields that were actually used for each Work?

Exception - Overrides:
Sometimes the required equipment or components are not available. In these cases, the manager can approve a one-time override for equivalent substitutes.  Examples:

Machine X was broken. Perform the Work by hand.
We ran out of Material Y. Use Material Z instead.
Keep runtime at 45 minutes

The database must capture how the Work was actually performed.
I see three possible options:
Option 1: Store Locally: The Work references the original Procedure. Each Work also locally stores the Procedure fields used, including any modifications. This creates many duplicates, but you have a local "snapshot" for each Work.
Example Schema
Work_id | Procedure_id    | Machine | Material | RunMinutes
1       | 1               | By-Hand | Z        | 45

Procedure_id | Product_id | Machine | Material | RunMinutes
1            | 1          | X       | Y        | 45

Option 2: Single Use Procedure:  The original Procedure is copied to a new Procedure, marked inactive, and modified with the overrides. The Work then references the new Procedure.  This maintains the Work.Procedure_id for how the Work was performed.
Example Schema
Work_id | Procedure_id
1       | 2

Procedure_id | Product_id| Active| Machine | Material | RunMinutes
1            | 1         | Y     | X       | Y        | 45
2            | 1         | N     | By-Hand | Z        | 45

Option 3: Store as Overrides: The Work points to the Procedure and optionally points to a ProcedureOverride table. For each field in Procedure, if there is an override, then use it otherwise, use the Procedure value.
Example Schema
Work_id| Procedure_id| Override_id
1      | 1           | 1

Procedure_id| Product_id| Machine | Material | RunMinutes
1           | 1         | X       | Y        | 45

Override_id             | Machine | Material | RunMinutes
1                       | By-Hand | Z        | NULL

Query: ActualWork
Work_id   |Procedure_id | Machine | Material | RunMinutes
1         |             | By-Hand | Z        | 45

Exception - Special Testing:
For non-standard work (such as research and development), there is no specific Procedure. Again, the database again must capture how the Work was actually performed.
I see two options (equivalent to the respective options above)
Option 1: Store Locally: Each Work locally stores all Procedure fields used. The user must input values for each field.
Option 2: Single Use Procedure: A new Procedure is created, marked inactive, and populated by the user. The Work then references the new Procedure.  This maintains the Work.Procedure_id for how the Work was performed. Keep in mind however, there is no actual (real world) Procedure for the non-standard Work.


texts: SQL Server Hardware Specs for Analyzing TBs of Data

texts:
I am building a workstation with the purpose of analyzing multiple terabytes of data.  Hundreds of tables, hundreds of columns, various data types throughout. This will include slicing-and-dicing the data into various aggregations and writing it back to summary tables (similar to building a cube) as well as processing large queries with lots of joins.

Most of the specs for database servers are (obviously) designed with lots of users in mind hitting the server.  In this case, I'm the only user, so most of the times there will be only one or two big queries going on at once.  Likewise, I may be taking a complex query and then rendering the output of that query (say 500GB+) to another table somewhere.

I want these reads/writes to happen as fast as possible but I also don't want to invest in hardware that is never going to be utilized. What recommendations does anyone have as far as CPU (clock-speed vs core count), memory size, and disk speed?

One setup I had considered was the following:

CPU: i7-6950X
RAM: 128GB (is this enough?)
SSD: Multiple Samsung 960 PRO 2TBs (NVMe sticks)

No one database should exceed 2TB so the various DBs might be spread across multiple of the NVMe drives.  Not sure if I should be going Xeon instead of i7 and, if so, should favor higher clock speed or higher core count. Lastly, wondering if 128GB RAM is enough but I was hoping the blazing fast disks would help make up for that.

Any and all versions of SQL Server are in play (including Enterprise Edition), meaning I can use whatever version best fits the performance needs.

Any advice is appreciated!


texts: Error in update. I did not find the error. Why is max(C) not found?

texts:
(Translated from Portuguese with Google Translate)

I created a table and made an update that should update column B equally to the percentage that C represents in relation to the maximum value of column C.

create table z(
a serial primary key ,
b numeric(7,2),
c numeric(7,2)
);

insert into z values(default, 0,20);
insert into z values(default, 0,10);
insert into z values(default, 0,40);
insert into z values(default, 0,80);

But it shows an error while executing.

update z set b = c / max(c) * 100;

  aggregate functions are not allowed in UPDATE

The expected result is:

a   b      c
1   25     20
2   12.5   10
3   50     40
4   100    80


texts: How to Pivot Multiple Columns in SQL Server

texts:
I've read many posts about using pivot to get the data in the format I want, but the more I read the more confused I get.

I have this data:

That I'm trying to get into a format similar to this:

For the most part, everything I try results in an SQL error, and the only successful attempt I've had didn't return the data in the format I'm looking for.

Any help would be appreciated.


texts: Why is a duplicate record inserted when it should not be?

texts:
Why does this command

insert into "deliveryLog" select * from temp_dellog where "deliveryID" < 15620652

give this output?

********** Error **********

ERROR: duplicate key value violates unique constraint "deliveryLog_pkey"
SQL state: 23505
Detail: Key ("deliveryID")=(15620652) already exists.

Both tables have identical structure, deliveryID is a bigint.


texts: Debugging value of varchar set to empty string crashes SSMS?

texts:
DECLARE @test varchar(1) = '';
SELECT @test

When debugging, hovering over @test displays a little {...} token, and clicking the magnifying glass to open the text visualizer causes an application crash.

Is there any reason for this to be happening? Or is this a legit bug in SSMS or SQL Server?


texts: OPENROWSET error accessing XLSX if not manually saved

texts:
Good day,

Apologies if this is in the wrong forum. Please refer me to the correct one if this is the case, thank you.

I have a bunch of excel files that are added to a Dropbox folder on a monthly basis. These files should then be imported to a SQL Server Database.

My problem is that, when I try and import data from SOME of these XLSX files, OPENROWSET gives me the following error:

Msg 7399, Level 16, State 1, Line 14
The OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 14
Cannot initialize the data source object of OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)".

This is a general error message and I was not able to find a solution related to my problem in another thread.

Once I open a file and SAVE it MANUALLY, then OPENROWSET SUCCESSFULLY reads the selected range from the XLSX file.

I think that means OPENROWSET has some kind of access or permition restriction to these files, but I do not know why as other files with the same format does not produce this error.

Is there a way to give OPENROWSET power to read these files even if they have not been manually saved? Any alternate suggestion on how to overcome this problem in SQL will be much appreciated.

Thanks,

Raynard


texts: Is a surrogate key better than a natural key in this case

texts:
I copied this code from here:

CREATE TABLE records(
    email TEXT REFERENCES users(email),
    lat DECIMAL,
    lon DECIMAL,
    depth TEXT,
    upload_date TIMESTAMP,
    comment TEXT,
    PRIMARY KEY (upload_date,email)
);

CREATE TABLE samples(
    date_taken TIMESTAMP,
    temp DECIMAL,
    intensity DECIMAL,
    upload_date TIMESTAMP,
    email TEXT,
    PRIMARY KEY(date_taken,upload_date,email),
    FOREIGN KEY (upload_date,email) REFERENCES records(upload_date,email)
);

The first thing that caught my eyes was the use of natural composite keys as primary keys for both tables.

3 things I was able to extract from this piece of code:

The users table (not shown here) uses email as primary key of type text..
The records table uses a composite key of text + timestamp.
The samples table uses a composite key of 3 fields of type text+ timestamp+ timestamp.

Now in this case wouldn't a surrogate key be better of identification? I mean performance wise indexing an int should be better than indexing a text? Is there something that could make a surrogate key a bad choice?


texts: Is it possible to format SQL on output into associative array?

texts:
We have a table.

customer
+----+------+------+-------+
| id | type | name | group |
+----+------+------+-------+
| 1  | boy  | dave | adult |
| 2  | girl | lisa | child |
| 3  | girl | amy  | adult |
| 4  | boy  | ivan | child |
+------------------+-------+

If we query this table with

SELECT * FROM `customer` WHERE type = 'girl'

Result output

Array
(
    [0] => Array
        (
            [id] => 2
            [type] => girl
            [name] => lisa
            [group] => child
        )
    [1] => Array
        (
            [id] => 3
            [type] => girl
            [name] => amy
            [group] => adult
        )
)

I would like to receive the output like the following.

Array
(
    [girl] => Array
        (
        [child] => Array
            (
                [id] => 2
                [name] => lisa
            )
        [adult] => Array
            (
                [id] => 3
                [name] => amy
            )
        )
)

I could manipulate the result with another programming language, but wondered if this is possible with pure SQL?

Thanks in advance.


texts: Consolidating a row of data, based on previous rows

texts:
I'm trying to build a history table out of an audit log (ultimately to build out a type 2 dimension table).  Unfortunately, the audit log only records the specific fields being changes.  Here's a rough example of what I'm talking about;

CREATE TABLE Staff(
  [ID] int,
  [Surname] varchar(5),
  [FirstName] varchar(4),
  [Office] varchar(9),
  [Date] varchar(10)
);

INSERT INTO Staff ([ID], [Surname], [FirstName], [Office], [Date])
VALUES
  (001, 'Smith', 'Bill', 'Melbourne', '2015-01-01'),
  (001, NULL, NULL, 'Sydney', '2015-03-01'),
  (002, 'Brown', 'Mary', 'Melbourne', '2014-04-01'),
  (002, 'Jones', NULL, 'Adelaide', '2014-05-01'),
  (002, NULL, NULL, 'Sydney', '2015-01-01'),
  (002, NULL, NULL, 'Perth', '2015-03-01');

The first entry for a particular staff member is for when their record is created, and each subsequent record is an update... but only shows the update to the field that was updated*.  I want to "fill out" the update row with the rest of the employee record as it currently stands.  ie, a result like this;

001, Smith, Bill, Melbourne, 2015-01-01
001, Smith, Bill, Sydney, 2015-03-01
002, Brown, Mary, Melbourne, 2014-04-01
002, Jones, Mary, Adelaide, 2014-05-01
002, Jones, Mary, Sydney, 2015-01-01
002, Jones, Mary, Perth, 2015-03-01

I know I can do this using a while loop or a cursor but I suspect there is probably a more performant option.

*A NULL always means "value didn't change" rather than "value changed to NULL".


texts: Query distinct then select option then choose and query choosed as html result on page

texts:
Structure of database "diary":

id------user_name-----day-----month------post
1--------erik-------- 3--------dec-------something
1--------erik---------3--------dec-------12345
1--------erik---------2--------dec-------kkkkk
1--------erik---------6--------jan-------ok
2--------john---------1---------dec------testing

Now I need to get unique month for user $login_session and show him select option. I managed to get distinct select option query from the database.
So here it is, and it's working:

$sql = "SELECT DISTINCT  month FROM `diary`
    where user_name = '$login_session' order by month";
$result = mysql_query($sql);

while($row = mysql_fetch_assoc($result)){
    echo '<option value="'.$row['month'].'">'.$row['month'].'</option>';
}

When user erik login he got:

dec
jan

...as option. The problem is when user login and see top option value on select to list his data. Example:

Let's say he choose dec, I need to get his output in html form:

 id----day ------- month ------- posts ------------
  1----3 ------- dec -------  something  -------
  1----3 ------- dec -------  12345 -------
  1----2 ------- dec -------  kkkkk  -------

Basically, I am trying to list logged user all posts by month select.