texts: How can I link to query file that opens in SQL Server Management Studio showing database login window?

texts:
Background:

I have many tasks at work everyday. I have a query at work that needs
to be performed everyday(number of queries may increase). I have
general(good enough) ability in computers, databases and SQL.

Goal:
I want to optimize my work flow, and I want to make it very easy so even if I am not at work somebody with very little knowledge can do it.

Now: SQL is written in Excel file. The SQL text is copied -> Management Studio is opened -> Database login is done -> SQL text is pasted in -> SQL is executed.

Wanted: SQL is stored in file(sql) -> If opened, the database login screen is shown OR login is done automatically -> SQL is executed.

Problem:
The database login screen doesn't seem to be shown when an SQL file is opened(opens if Management Studio is already open).
I either want the login screen to be shown or to be able to include the username and password in the file somewhere so it is done automatically.
(I know this is small, but assume I'm lazy and time is money, and newbies can waste a lot of time :) )


texts: How to combine date and time with a string value in column created by Case in SQL Query

texts:
I am in the process of creating a query for an SSRS report that will show a column that has DATETIME values.  The column can have NULL values.  In my query I would like to use a CASE expression.

I want to create this column to just display the date and time value if its not null but if its NULL I want it show something like 'No Data'.

The Expression I have is:

Case
  When (Column with Date/Time) is not null then (Column with Date/Time)
  When (Column With Date/Time) is NULL then 'No Data'
End AS Column A

However, this query returns an error:

  Conversion failed when converting date and/or time from character
  string.

I guess because it can't have a string value with a column formatted for Date and time?

Is there any way I can combine these two?  If not I know I can leave it as is and just show the NULL value, but I wanted it to look cleaner.


texts:
I have a column of characters in SQL Server 2014 that I have to convert to numeric and give them the opposite value (- to + and + to -).
The data looks like this:

 -0000000000002999
000000000000002999
000000000000002459
 -0000000000002459
000000000000000437

I need it to look like this:

29.99
-29.99
-24.59
24.59
-4.37

This is the query i'm trying to use but it makes them all positive!
select

CASE
  WHEN CHARINDEX('-', o.PMT_AMOUNT) > '0'
     THEN CONVERT(numeric(18), Right(o.PMT_AMOUNT,17))* -.01
  ELSE CONVERT(numeric(18), (o.PMT_AMOUNT)) * .01
END from OPS_PMT_RECON_CONV o


texts: MySQL Replication fails - commands received in wrong order?

texts:
The other day, I had this issue with one of our MySQL 5.1.60 master and 5.1.61 slave setups:

Last_Error: Error 'Can't create table 'foobar.#sql-4b87_2' (errno: 150)' on query.
Default database: 'foobar'. Query: 'alter table picture add index FKDD905 (pictureSource_id),
add constraint FKDD905 foreign key (pictureSource_id) references picturesource (id)'

Got to mention, that we just "host" the MySQL servers/setups; the content (including INDEXes and such) comes from the customer. We just run it.

Okay, so here's what happened, as far as I understand it:

On the master, the customer created the table "picturesource"
On the master, they added the index "FKDD905" to the table "picture", referencing the field "id" from "picturesource" as a Foreign Key
The slave tried to execute "alter table add index…".
The slave tried to execute "add table picturesource…".

In that order.

On the slave, that failed. It failed, because the table picturesource did not exist on the slave. How can that happen? Why did the slave try to add the index BEFORE creating the table? I mean, on the master, the table MUST have existed before they added the key.

Don't know if it's important, but binlog_format = MIXED. I manually added the (empty) table picturesource and could then restart the slave process (→ START SLAVE;). In a subsequent SHOW FULL PROCESSLIST\G, I then saw, that it did a copy to tmp table for the picture table; the picture table is huge (~7 GB).

Can someone clarify?


texts:
I am creating to the user after create table, but not insert the data why?
Then display the message NO PRIVILEGES ON TABLESPACE 'USERS'


texts: Does clearing cache in at os level affect oracle running database?

texts:
I have 32 gb of ram for running Oracle 12c database. Now I am facing memory issue at OSlevel.
Here are the details of free -h:

[oracle@sa1 df_size]$ free -h
total          used        free      shared  buff/cache   available
Mem:            31G        1.2G        369M         22G         29G        7.7G
Swap:          2.0G        2.0G        9.3M

If I clear buff/cache memory at OS level does it impact on Database ?

To clear cache I will execute this command:

sync; echo 1 > /proc/sys/vm/drop_caches


texts: Secondary Replica Read-Only Routing via Linked Server working from one server, but not the other

texts:
I have been given the task of setting up a read-only linked server on ServerA to Availability Group listener ListnerX. ListnerX points to ServerY or ServerZ (ServerY is usually the primary). Read-only routing URLs and priority lists have been set up for ServerY and ServerZ. When I create the linked server on ServerA using this command...

Exec master.dbo.sp_addlinkedserver
@server = N'ListnerX',
@srvproduct = N'SQL',
@provider = N'SQLNCLI11',
@datasrc = N'ListenerX,1433',
@provstr = N'ApplicationIntent=ReadOnly',
@catalog = N'TestDatabase'

... and then run...

select * from openquery([ListenerX], 'select @@servername')

...I get...

ServerY

...which is not what I'm expecting since it is the primary replica.

However, when I create the same ListenerX linked server on ServerY itself using the above command, I get...

ServerZ

...which is what I'm expecting since it is the readable secondary replica.

ServerA lives in a separate zone of our network from ServerY and ServerZ. From ServerA I can definitely connect to ListenerX, but I don't know what network restrictions exist between the two zones.

Any ideas why the read-only linked server on ServerA to ListenerX doesn't route correctly, but the one on ServerY does?


texts: Is there a way to get PostgreSQL to randomly order query results that haven't been ordered by an ORDER BY? I think this would be a useful way to seek out bugs caused by an implicit reliance on the order of DB results.


texts: What principles should I follow when entering data into a complex relational database?

texts:
I'm new to Databases, so forgive me if this is a basic question.

I'm attempting to parse a complex open source dictionary, and output the data into an Access database, which is equally complex:

I understand how to insert data into a table, but what I don't understand is how one should insert data into multiple tables that rely on one another for foreign keys. To give a simplified example:

I've got three tables: Entry, Keyword, and Keyword_Priority. Every entry can have multiple keywords, so the Keyword table links back to Entry through a foreign key. And each keyword element can have multiple elements describing the priority of the keyword, so the Keyword_Priority table links back to Keyword via foreign key, as below:

In this simplified scenario, let's say I want to create a new entry using SQL. Where do I start? Do I:

Insert into the Entry table first to create a new EntryID
Then Insert into the Keyword table using the new EntryID as a Foreign Key
Then Insert into the Keyword_Priority table using the Keyword's ID field as a Foreign Key

Or alternatively, should I do it the other way around ...

Insert into the Keyword_Priority table
Insert into the Keyword table
Insert into the Entry table

So to sum up, my question is:

Where should I start when inserting data into multiple tables at once? From the top-down, or from the bottom-up?


texts: High Availibility Solution to send data to multiple locations

texts:
I inherited a very interesting environment and I'm tasked with finding and implementing High Availability/Disaster Recovery solution as soon as possible.

Simply speaking our environment looks like this:

Server 1 - production database(200GB)

Servers 2, 3, 4 - need daily restored copies of production database (have to be updated with production data once per day)

Server 5 - DR server (data loss should be as little as possible, no more than 5 minutes)

We use:

Windows 2012 R2 on all servers
SQL Server 2012 Enterprise
We are happy to upgrade software and hardware (within reason) if that's necessary.

Requirements:

We need solution that:

would require as little maintenance work as possible (only 1 super busy DBA is available)
would allow us to continue producing full, differential and transactional backups(we need them for auditors)

Options I already considered:

I thought about it and I excluded following methods:

1) log shipping because we need to be able to show log backups to auditors and store them for years. Please correct me if I'm wrong but log shipping wouldn't let us create separate log backups.

2) Mirroring only works between 2 servers (principal and mirror) so it doesn't solve problem of additional servers that have to be refreshed daily. Maybe I can use it on only one server and something else on the other 3 servers.

3) Replication is mostly meant for single tables and functions so I'm afraid its not possible to replicate one database on a slow server to 4 other servers.

4) I never worked with other HA techniques so for example AlwaysOn or clusters is a one big unknown for me. I am happy to learn though as long as someone points me in the right direction.

I would be very grateful if you could suggest HA/DR solution that can be implemented in the described environment.


texts: Best practice for table to table operation

texts:
I have a table loaded daily (let's call it A_table), with the data from this table I have to load another table (B_table).

A is replaced every day (truncated and loaded anew), B is the final table, which have to stay and it'll be queried.
As far as I know we should have a relatively low number number of row loaded daily in A while B is ever-expanding.
I have to do insert or update (from A to B) based on what I already have on information in B.

I'm pretty new to this, but I think it's not an uncommon operation. Is there a best practice or more simply a common approach to this type of operation ?


texts: How can I make a stored procedure to print a specific message if an empty set wa returned by the query?


texts: How to resolve MDF and LDF files remaining in use?

texts:
I am attempting to copy off approximately 220 gigs of database MDF and LDF files from a MS SQL server 2000 install in order to transfer ownership to a third party.

The error I get when I try to stop the database using the enterprise manager is

  Error 1051: A Stop Control Has Been Sent To A Service Which Other Running Services Are Dependent On

Going to the service and looking at the dependencies tab does not show any other services running in relation to it. If I stop the actual Windows service, and try to copy it tells me that the databases are still in use. I have changed the service from automatic to manual and rebooted, and it still insists the files are in use.

I don't particularly care about copying the log files, but how can I resolve the error to do a clean shutdown and be able to copy off at least the MDF files?


texts: What is an equivalent of EXCEPT(in PostgreSQL) in MySQL?

texts: I want to find out unmatched records of two tables in MySQL.


texts: Stack dumps when turning on Temporal Table SYSTEM_VERSIONING

texts:
I am running SQL Server 2016 SP1 (13.0.4001) on Windows Sever 2012 R2. I have the below table.
CREATE TABLE [SubEx].[Ingredients](
    [IngredientId] [int] IDENTITY(1,2) NOT NULL,
    [IngredientName] [nvarchar](328) NOT NULL,
    [IngredientShortDescription] [nvarchar](328) NOT NULL,
    [PortionUOMTypeId] [smallint] NULL,
    [WISRTypeId] [smallint] NOT NULL,
    [WISRSubTypeId] [smallint] NULL,
    [FlavorTypeId] [smallint] NULL,
    [StatusTypeId] [smallint] NOT NULL,
    [IsMandatory] [bit] NOT NULL,
    [IsDownloaded] [bit] NOT NULL,
    [CreatedDT] [datetime] NOT NULL,
    [CreatedBy] [int] NOT NULL,
    [LastUpdateDT] [datetime] NULL,
    [LastUpdateBy] [int] NULL,
    [Deleted] [bit] NOT NULL,
    [DeletedDT] [datetime] NULL,
    [DeletedBy] [int] NULL,
    [CreatedUserName] [nvarchar](136) NOT NULL,
    [UpdatedUserName] [nvarchar](136) NULL,
    [DeletedUserName] [nvarchar](136) NULL,
    [ValidFrom] [datetime2](2) GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] [datetime2](2) GENERATED ALWAYS AS ROW END NOT NULL,
 CONSTRAINT [PK_Ingredients_IngredientId] PRIMARY KEY CLUSTERED
(
    [IngredientId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
    PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
) ON [PRIMARY]

GO

ALTER TABLE [SubEx].[Ingredients] ADD  CONSTRAINT [DF_Ingredients_IsMandatory]  DEFAULT ((0)) FOR [IsMandatory]
GO

ALTER TABLE [SubEx].[Ingredients] ADD  CONSTRAINT [DF_Ingredients_IsDownloaded]  DEFAULT ((0)) FOR [IsDownloaded]
GO

ALTER TABLE [SubEx].[Ingredients] ADD  CONSTRAINT [DF_Ingredients_Deleted]  DEFAULT ((0)) FOR [Deleted]
GO

ALTER TABLE [SubEx].[Ingredients] ADD  CONSTRAINT [DF_ValidFrom]  DEFAULT (dateadd(second,(-1),sysutcdatetime())) FOR [ValidFrom]
GO

ALTER TABLE [SubEx].[Ingredients] ADD  CONSTRAINT [DF_ValidTo]  DEFAULT ('9999.12.31 23:59:59.99') FOR [ValidTo]
GO

ALTER TABLE [SubEx].[Ingredients]  WITH CHECK ADD  CONSTRAINT [FK_Ingredients_TlkpFlavorTypes_FlavorTypeId] FOREIGN KEY([FlavorTypeId])
REFERENCES [SubEx].[TlkpFlavorTypes] ([FlavorTypeId])
GO

ALTER TABLE [SubEx].[Ingredients] CHECK CONSTRAINT [FK_Ingredients_TlkpFlavorTypes_FlavorTypeId]
GO

ALTER TABLE [SubEx].[Ingredients]  WITH CHECK ADD  CONSTRAINT [FK_Ingredients_TlkpStatusTypes_StatusTypeId] FOREIGN KEY([StatusTypeId])
REFERENCES [SubEx].[TlkpStatusTypes] ([StatusTypeId])
GO

ALTER TABLE [SubEx].[Ingredients] CHECK CONSTRAINT [FK_Ingredients_TlkpStatusTypes_StatusTypeId]
GO

ALTER TABLE [SubEx].[Ingredients]  WITH CHECK ADD  CONSTRAINT [FK_Ingredients_TlkpWISRCategoryTypes_WISRTypeId] FOREIGN KEY([WISRTypeId])
REFERENCES [SubEx].[TlkpWISRCategoryTypes] ([WISRTypeId])
GO

ALTER TABLE [SubEx].[Ingredients] CHECK CONSTRAINT [FK_Ingredients_TlkpWISRCategoryTypes_WISRTypeId]
GO

ALTER TABLE [SubEx].[Ingredients]  WITH CHECK ADD  CONSTRAINT [FK_Ingredients_TlkpWISRSubCategoryTypes_WISRSubTypeId] FOREIGN KEY([WISRSubTypeId])
REFERENCES [SubEx].[TlkpWISRSubCategoryTypes] ([WISRSubTypeId])
GO

ALTER TABLE [SubEx].[Ingredients] CHECK CONSTRAINT [FK_Ingredients_TlkpWISRSubCategoryTypes_WISRSubTypeId]
GO

When I run the below script to enable SYSTEM_VERSIONING for my temporal table, I receive the below error message and a stack dump.
ALTER TABLE SubEx.Ingredients
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = SubEx.IngredientsHistory));

Msg 596, Level 21, State 1, Line 8 Cannot continue the execution
because the session is in the kill state.
Msg 0, Level 20, State 0,
Line 8 A severe error occurred on the current command.  The results,
if any, should be discarded.

Am I doing something wrong or do I need to submit a bug with Microsoft?


texts: MySQL status variable Handler_read_rnd_next is growing a lot

texts:
In MYSQL status, Handler_read_rnd_next value is very high.

I am aware that, this value will be incremented when a query is executed which is not having proper indexes.

But, even when we execute show status like 'Handler_read_rnd_next', this value is getting incremented by 2.

Based on this status flag, we are monitoring some stats.

So every time, this stats are showing critical.

Can we exclude these 'show' execution counts from 'Handler_read_rnd_next' count.

One more example for this,

There is a table with 10 rows, table is indexed on column 'data', and if we execute the following query:

select data from test where data = 'vwx' -> returns one row

and if we check value of 'Handler_read_rnd_next', it got incremented by 7.

Following is result of explain command for the above query:

explain select data from test where data = 'vwx';

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra

1, 'SIMPLE', 'test', 'ref', 'data', 'data', '35', 'const', 1, 'Using where; Using index'

Is there any way to restrict this value, or can i know why this value is getting incremented very fast.


texts: Why do we have to use SWITCH in table partitioning?


texts: Difference between joining one table twice times and operator OR

texts:
I have a table Orders that contains creatorId and editorId. My query is trying to compare a current user userId = creatorId OR userId = editorId. So I must join table 2 times. It seems like that:

select...
from       Orders o
inner join Employee e1
on         o.creatorId = e1.userId
inner join Employee e2
on         o.ediorId = e2.userId

--It returns 174 rows.

But I'm really incredulous when using OR operator. The query will be:

select...
from       Orders o
inner join Employee e
on         e.userId = o.creatorId
           OR
           e.userId = o.editorId

--It returns 181 rows.

The result has a small difference here. It means 2 query upper is not same. So I will try by another way, using UNION. The query may like:

select...
from       Orders o
inner join Employee e
on         e.userId = o.creatorId
UNION
select...
from       Orders o
inner join Employee e
on         e.userId = o.editorId

--It returns 181 rows.

And finally, I will check the difference (missing rows) by using EXCEPT:

select...
from       Orders o
inner join Employee e
on         e.userId = o.creatorId
           OR
           e.userId = o.editorId
EXCEPT
select...
from       Orders o
inner join Employee e1
on         e1.userId = o.creatorId
inner join Employee e2
on         e2.userId = o.editorId

--It returns 7 missing rows.

Single query (temporarily called as first single query):

select...
from       Orders o
inner join Employee e
on         e.userId = o.creatorId

return 174 rows same to:

select...
from       Orders o
inner join Employee e
on         e.userId = o.editorId

My friend said that the second inner join is USELESS/MEANINGLESS for this case (the query that inner join one table 2 times is same as 1st single query). Though I have found that depending on several cases, sometimes they can join a table more than 2 times. But I'm really don't know to explain the difference between these upper queries. Please give me some advice, which query is right? Thanks so much.


texts: innodb_log_file_size set to a lower value than the size of ib_lofile0 and iblogfile1

texts:
I am trying to run an application from my MariadDB installation, that needs a larger value for innodb_log_file_size. So I have set it to innodb_log_file_size=256M in /etc/my.cnf.d/server.cnf.

Now when I check for the set size with show variables like 'innodb_buffer%'; it tells me that the size is 134217728 which is 128M. So it doesn't seem to acknowledge my change...

Funny enough though, when I run my application (which is Rancher by the way), the iblogfiles are instantiated with a size of 256M.

Can anybody tell me why this is the case? My application doesn't seem to work properly and I'm not quite sure whether this is related...


texts: Failed to add 'Admin user' to %2% user group in Oracle 19C Windows 10 installation

texts:
Oracle 19c installation fails with this error message.

I am trying to set up oracle for my 'Existing Local User' account

Installation logs tries to add Admin user to all groups and fails .it says member does not exist.

Installation logs :

Failed to add Windows user or Windows group "Admin user" to Windows group "USERS"

Failed to add Windows user or Windows group "Admin user" to Windows group "ORA_OraDB19Home1_SYSDG"

Failed to add Windows user or Windows group "Admin user" to Windows group "ORA_DBA" etc.,

SEVERE: PRCZ-1082 : Failed to add Windows user or Windows group "Admin user" to Windows group "ORA_DBA"

WSE-000031: Unable to add user to operating system group.

O/S-Error: (OS 1387) A member could not be added to or removed from the local group because the member does not exist

at oracle.cluster.impl.winsecurity.GroupImpl.doInternalAddGroupMember(GroupImpl.java:488)

Caused by: oracle.ops.mgmt.nativesystem.NativeException: WSE-000031: Unable to add user to operating system group.

Should I be doing anything different?


texts: Normalizing a relation schema with 'abstract' attribute names

texts:
I'm a student currently attending a database design course and I'm having some issues normalizing 'abstract' attribute relation schemas from 1NF all the way to 3NF.

When we used examples with 'explicit' values in the lecture I didn't have issues normalizing those relation schemas, but as soon as it gets 'abstract' I just can't do it.

Given the following relation schema (R, a) with a = {A, B, C, D, E, G, H, I} and functional dependencies FDs = {GH → A, AH → BC, CHI → B, B → C, A → B, CE → D, BC → H, D → A, AG → B, H → I}. I first reduced the left and right sides of the functional dependencies to find the minimal cover M = {GH → A, CH → B, B → C, A → B, CE → D, B → H, D → A, AG → B, H → I}.

In the lecture we agreed that this sort of 'abstract' relation schema would already be in 1NF.

I chose G, E and D as my key as G and E can't be determined by any other attribute and D seemed good as it determines A which then determines all other attributes.

I came to the following solution for the normalization to 2NF.

Solution

{D, A, B, C, H, I}
{G, H, A}
{E, C, D}

I'm pretty sure my solution is incorrect. I'm not even really sure where to start and I haven't started normalizing to 3NF as I think it would be good to at least have a correct 2NF to start from. Any help is much appreciated.

Where do I start to normalize the given relation schema to 2NF and 3NF?


texts: Does SQL Server 2016 still contain sqlmaint.exe?

texts:
According to the documentation (Deprecated Database Engine Features in SQL Server 2016), sqlmaint.exe should still be available (emphasis mine):

  This topic describes the deprecated SQL Server Database Engine features that are still available in SQL Server 2016. These features are scheduled to be removed in a future release of SQL Server.

  ...

  Tools: sqlmaint Utility ...

It is definitely not listed on the list of discontinued features.

However, I fail to find sqlmaint.exe on my recently installed SQL Server 2016 Express systems. In previous versions, it was in the same folder as sqlserver.exe.

Is the documentation wrong? Or did the file location move? Or did I miss something during the install process?

(Note: Encouraged by the comments reproducing this issue, I have created a Microsoft Connect entry for this, which was migrated to UserVoice.)


texts: Recommendations for my.cnf on MySQL Replicant

texts:
I have replication environment running in a master-slave configuration. The replicant is doing all writes and doing selects only very rarely - and none in which high performance is a requirement.

Aside from the replication options themselves which have to be set, do the db administrators here have any recommendations for configuration of a mysql replicant that would be different from the default my.cnf values on a master server which is doing both selects and writes?

I can provide my own mysql-tuner results and my.cnf configuration, but I guess I was hoping for a more general overview on the configuration best practices for a replicant that is doing mostly writes.


texts: firebird 3 identity column - return last inserted id

texts:
I am using FireDAC in Delphi 101. Berlin and I want to get the last inserted id of IDENTITY DATA TYPE column after executing insert query but, the returned value is empty.

Query: INSERT INTO ACC_ACCOUNT_HEAD (ACCOUNT_HEAD_TITLE) VALUES ('TEST') returning ACCOUNT_HEAD_ID;

table structure

CREATE TABLE ACC_ACCOUNT_HEAD (
 ACCOUNT_HEAD_ID       INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  ACCOUNT_HEAD_TITLE    VARCHAR(208),
  PRIMARY KEY (ACCOUNT_HEAD_ID)
);

Note: I know it is possible with generators but I want to know if it is possible with IDENTITY column.

Complete Delphi Code:

var
aQry : TFDQuery;
begin
aQry := TFDQuery.Create(Self);
aQry.Connection := frmMain.aCon;

 aQry.SQL.Text :=
      'INSERT INTO ACC_ACCOUNT_HEAD (ACCOUNT_HEAD_TITLE) VALUES (:P1) returning ACCOUNT_HEAD_ID';
    aQry.ParamByName('P1').AsString := 'Testing';
    aQry.ExecSQL; // or Should I use aQry.Open(); to get returned value???


texts: Classic ASP Connection to SQL Server 2014 without TLS 1.0 using ADODB.Connection

texts:
With TLS 1.0 being disabled in the name of PCI compliance, I'm unable to get a 32-bit Classic ASP application working.

Per MS / Stack Exchange recommendations, I've installed:

SQL Server 2014 SP1 CU1
.NET Framework 4.6

This has gotten our ASP.NET applications / SSMS running. But our Classic ASP application, which uses an ADODB.Connection object is not working.

I've tried a connection string which uses Provider=SQLNCLI11;, but that doesn't seem to help either. The Shared Memory provider is still complaining about nothing being on the other end of the pipe. Error message:

  Microsoft SQL Server Native Client 11.0 error '80004005'

  Shared Memory Provider: No process is on the other end of the pipe.

I also tried using Named Pipes with the connection string Provider=SQLNCLI11;Server=np:\\.\pipe\MSSQL$SQLEXPRESS\sql\query;Database=northwind;Trusted_Connection=Yes; and received this error message:

  Microsoft SQL Server Native Client 11.0 error '80004005'

  Named Pipes Provider: No process is on the other end of the pipe.

Is there a patch for ADODB that I should be aware of?
Should I consider using Named Pipes in some other way or the like instead? (although I'm a little baffled as to why shared memory doesn't work, regardless of the TLS settings)

Updates from comments:

The web server and the SQL Server are on the same box.

It's a SQLExpress named instance. The connection string working with TLS 1.0 enabled is:

"Driver={SQL Server}; Server=.\SQLExpress; Database=northwind; Trusted_Connection=Yes; Integrated_Security=True;"

I've also tried:

"Provider=SQLNCLI11; Server=.\SQLExpress; Database=northwind; Trusted_Connection=Yes;"

Neither work once TLS 1.0 is disabled. I know Trusted_Connection / Integrated_Security is redundant, but messing around with these doesn't seem to help either.

I've toggled enabling, disabling, and order for both TCP and named pipes. The name of the pipe used in the connection string above is directly from the Server Configuration for that instance. I've also tried disabling Shared Memory to make sure named pipes is working. I haven't tried TCP (as if Shared Memory doesn't work because of TLS, why would TCP?) Shared Memory and Named Pipes both work with TLS 1.0 enabled. As soon as I flip the registry key to disable TLS 1.0 (and reboot), the above error messages occur. We're likely just going to put this machine behind a proxy.

SQL server service won’t start after disabling TLS 1.0 and SSL 3.0

The issue described in that link is addressed by CU1 in my posed question above. The SQL Server instance DOES start with TLS 1.0 disabled (thanks to the MS update). .NET based applications work just fine with the SQL instance (after update to 4.6). This question is specific to a Classic ASP (32-bit) application. My understanding is that the native client is also patched by CU1, hence my confusion as to why this isn't working.


texts: current_timestamp at time zone 'UTC' SQL Server equivalent

texts:
What is the equivalent of the Postgres "current_timestamp at time zone 'UTC'" in SQL Server?

How do I select the current timestamp in UTC in SQL Server?


texts: What is the correct name and significance of EAV table pivot?

texts:
I've been forced to use a very uncomfortable structure for a table. After trying to up similar examples, I've discovered the EAV, which seems almost like a pivot of said structure. The attribute order is not set in stone (except that there are no new attributes after a NULL column). For the max amount of attributes N, there are 2*N columns in pairs of Attribute_Name - Attribute value.

EAV example:

ID| Entity  | Attribute  | Value
__________________________________
1 | Entity1 | Attribute1 | Value1
2 | Entity2 | Attribute1 | Value2
3 | Entity2 | Attribute2 | Value3
4 | Entity2 | Attribute3 | Value4
5 | Entity3 | Attribute1 | Value5
6 | Entity3 | Attribute2 | Value6

Example of my table:

Entity  | A_Name1    | A_Value1 | A_Name2       | A_Value2 | A_Name3       | A_Value3
______________________________________________________________________________________
Entity1 | Attribute1 | Value1   | NULL          | NULL     | NULL          | NULL
Entity2 | Attribute2 | Value3   | Attribute1    | Value2   | Attribute3    | Value4
Entity3 | Attribute2 | Value6   | Attribute1    | Value5   |  NULL         | NULL

Should this be considered separate from EAV structure? Is there any classification for this kind of table structure, or is it one of a kind? Basically, I want to learn as much as possible about it and ways to work around it.

EDIT: Forgot to mention -- the Attribute names might also repeat like this:

Entity  | A_Name1    | A_Value1 | A_Name2       | A_Value2 | A_Name3       | A_Value3
______________________________________________________________________________________
Entity1 | Attribute1 | Value1   | NULL          | NULL     | NULL          | NULL
Entity2 | Attribute2 | Value3   | Attribute1    | Value2   | Attribute2    | Value4
Entity3 | Attribute2 | Value6   | Attribute1    | Value5   |  NULL         | NULL


texts: Check for duplicate data from called functions

texts:
I'm new to SQL and am not sure how to go about getting the result set I am after. Below is the closest I have gotten to my desired result set although I am seeing duplicate data.

I have a stored procedure

Select
o.cFirstName+' '+o.cLastName [owner], o.nContactUID [oUID],
p.cFirstName+' '+p.cLastName [Process], p.nContactUID [pUID]
from (
     SELECT

         cFirstName,
         cLastName,
         nContactUID
     FROM  dbo.ip_contact_owner(@facilityID)
        union
     SELECT

         cFirstName,
         cLastName,
         nContactUID
     FROM
     dbo.ip_contact_owner_alternates(@facilityID)
 ) as o,
 (
     SELECT
        cFirstName,
        cLastName,
        nContactUID
     FROM  dbo.ip_contact_process(@facilityID)
        union
     SELECT
        cFirstName,
        cLastName,
        nContactUID
     FROM
     dbo.ip_contact_process_alternates(@facilityID)
 ) as p

is calling the below table-valued functions for each owner, owner alternate, process and process alternate:

ALTER FUNCTION [dbo].[ip_contact_owner](@facility varchar(100))
RETURNS TABLE

AS
  RETURN  (

  SELECT
 f.nFacilityUID,
 c.cFirstName,
 c.cLastName,
 c.nContactUID
 from dbo.ip_ContactAssignments as a
 join dbo.Contacts as c on a.nContactUID = c.nContactUID
 join dbo.Facilities as f on a.nFacilityUID = f.nFacilityUID
 join dbo.ip_ContactPermissions as p on a.nPermissionUID = p.nPermissionUID
 where
      a.lActive = -1 and
      c.lActive = -1 and
      f.lActive = -1 and
      a.lPrimary=-1 and
      p.cDescription = 'owner' and
      f.nFacilityUID = @facility
 )

ALTER FUNCTION [dbo].[ip_contact_owner_alternates](@facility varchar(100))
RETURNS TABLE

AS
  RETURN  (

  SELECT
 f.nFacilityUID,
 c.cFirstName,
 c.cLastName,
 c.nContactUID
 from dbo.ip_ContactAssignments as a
 join dbo.Contacts as c on a.nContactUID = c.nContactUID
 join dbo.Facilities as f on a.nFacilityUID = f.nFacilityUID
 join dbo.ip_ContactPermissions as p on a.nPermissionUID = p.nPermissionUID
 where
      a.lActive = -1 and
      c.lActive = -1 and
      f.lActive = -1 and
      a.lPrimary = 0 and
      p.cDescription = 'owner' and
      f.nFacilityUID = @facility
 )

My result set currently looks like this (owner, ownerID, process, processID):

Lisa Simpson |348392000000267 | Bart Simpson   | 348392000000347
Lisa Simpson |348392000000267 | Homer Simpson  |  348392000000350
Lisa Simpson |348392000000267 | Maggie Simpson |  348392000000306

I would like for it to return like this

Lisa Simpson |348392000000267 | Bart Simpson   | 348392000000347
NULL         |NULL            | Homer Simpson  |  348392000000350
NULL         |NULL            | Maggie Simpson |  348392000000306

Basically, there will always be 1 owner and 1 process name returned but 0 to many owner and process alternates. How do I eliminate the duplicate data?

I'm on SQL Server 2008


texts: Postgres latency issues during memory compaction on EC2 (Ubuntu 14.04)

texts:
We've recently upgraded our EC2 instance that hosts our Postgres database to an i2.8xlarge with 244GB of memory (this is to utilise the large amounts of ephemeral storage it comes with). Since upgrading, we've been having some issues with latency in Postgres that appear to be due to memory compaction that's occurring in the Linux kernel.

We're using PostgreSQL 9.3 on a recent Ubuntu 14.04 kernel running the following (hopefully relevant subset of) config:

max_connections = 1000
effective_cache_size = '220GB'
shared_buffers = '24GB'
work_mem = '25MB'
maintenance_work_mem = '1024MB'
fsync = off
full_page_writes = on
synchronous_commit = off

We have transparent huge pages completely disabled on this server (/sys/kernel/mm/transparent_hugepage/enabled and /sys/kernel/mm/transparent_hugepage/defrag are both set to never and /sys/kernel/mm/transparent_hugepage/khugepaged/defrag is set to 0) and we're fairly sure that we're not seeing any issues as a result of THP because the thp_* stats and nr_anon_transparent_hugepages stat in /proc/vmstat never increment.

Our issue is that we see constant memory compaction (failure and success) events in /proc/vmstat (all the stats under compact_* incrementing frequently) and some of these cause pretty severe stalls that get worse over time (presumably as memory fragmentation gets worse) and impact on our application. We're tracking the stats from /sys/kernel/debug/extfrag/unusable_index and often see a flurry of movement between the different page orders when we see stall-causing events.

We're wondering whether this is just some combination of Postgres version, Linux kernel version and having to deal with a large amount of memory (as obviously most of the memory usage is file cache, so Linux might be doing things with that that Postgres isn't happy about), but haven't been able to come up with anything other than assuming a more recent version of Postgres (9.4 or 9.5) might avoid the issue altogether for some reason.

$ uname -a
Linux db-04 3.13.0-91-generic #138-Ubuntu SMP Fri Jun 24 17:00:34 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
$ dpkg -l postgresql-9.3
postgresql-9.3     9.3.13-1.pgdg14.04+1

We also tried reducing the effective_cache_size on the instance to 160GB to see if we could reduce memory pressure but that didn't change much (and mostly seemed to make the stalling worse).

Just wondering if memory stalls on Postgres is something that's been raised before or that people have experience with?


texts: Can I migrate an Oracle Model Mart database to SQL Server

texts: Currently we have two Model Mart repositories, 7.2 and 9.5. We only migrated the models that are currently being used, but we need to keep the old models available in case they are needed. Is there a tool to do the migration from Oracle 11g to SQL Server 2016? Has someone done a manual migration where the instance, database and schema are created in SQL Server, the constraints are dropped and the data is moved over followed by creating the constraints.


texts: Sql server sleeping state is increasing using ADO.NET?

texts:
We have one live ASP.Net application which is used by around 1000 concurrent users. In this application we have used connection pooling. There are 2 connection string defined in web.config with different name but we are calling same DB.

<add key="connection1" value="server=1.1.1.1;USER ID=**;pwd=**;database=test;Max Pool Size=500;Min Pool Size=50;Connection Timeout = 300; Pooling='true'" />

<add key="connection2" value="server=1.1.1.1;USER ID=**;pwd=**;database=test;Max Pool Size=500;Min Pool Size=50;Connection Timeout = 300; Pooling='true'"/>

In my application we are opening an closing the connection every where like below

try{
    if (connection.State == ConnectionState.Closed)
        {
            connection.Open();
        }
}
catch(Exception ex)
{
}
finally{connection.close()}

Now issue is when I am checking Database using sp_who2, I am seeing around 1000 or more than that sleeping session and for each record program name is ".Net sql server Provider". I gone through MSDN and found that if we are using connection pooling then connection keep opened till 4-8 minutes. So I am bit confused now, if I am using only 2 connection string with connection pool then why we are seeing so many sleeping connection, I am thinking it should be only 2? Can someone please help me in this situation and suggest what is correct? And how can we minimize sleeping sessions.
Also not sure whether it's an application pool(IIS server) error or application error?
Please help.

Thanks,


texts: Postgres - Update multiple rows in the same query with limit

texts:
I am trying to update multiple rows in a table by using the following statement:

update test as t set
    column_a = c.column_a
from (values
    ('123', 1),
    ('345', 2)
) as c(column_b, column_a)
where c.column_b = t.column_b;

However, in my database the values in column_b are not unique (e.g., multiple rows can have the '123'). I also have a column_c with a DATE type. For each of the rows in the update statement, I only want the update stated above to happen on the row with the most recent date in column_c, for example by ordering the data by date and using LIMIT 1.

Therefore, I am trying to combine this query with the answer provided here. However, I have trouble doing this.


texts: PostgreSQLpgagent "Running at: is not runing"

texts:
I have a problem to run pgAgent job for PostgreSQL 9.6 under ubuntu 16.04.
The job is never executed.

I already configured .pgpass and added:

localhost:5432:*:postgres:mypass

Also modified the permission for .pgpass:

$ touch ~/.pgpass
$ chmod 0600 ~/.pgpass

After that I run pgAgent with no problem:

$pgagent hostaddr=127.0.0.1 dbname=mydb user=postgres

When I check the process for pgAgent I would get this result:

user@gis-srv:~$ ps aux | grep pgagent
user   153179  0.0  0.0 113844  9392 pts/26   S    17:15   0:00 pgagent hostaddr=127.0.0.1 dbname=postgres user=postgres
user   153220  0.0  0.0 113844  9360 pts/26   S    17:16   0:00 pgagent hostaddr=127.0.0.1 dbname=mydb user=postgres
user   153227  0.0  0.0  12944   936 pts/26   S+   17:16   0:00 grep --color=auto pgagent

Here is my job setting:
 Job Setting:

Schedule setting:

Step Setting:

My propblen that the job is never executed and there is no error in the errors log for PostgreSQL.
Please help me to find out why jobs never run.

I am receving the following errors:
from postgres.log:

2017-11-03 16:01:32.269 EDT [15344] postgres@postgres LOG:  could not receive data from client: Connection reset by peer

from pgagent.log:

(attempt 9): fe_sendauth: no password supplied
Fri Nov  3 16:01:32 2017 : WARNING: Couldn't create the primary connection (attempt 10): fe_sendauth: no password supplied
Fri Nov  3 16:01:32 2017 : ERROR: Stopping pgAgent: Couldn't establish the primary connection with the database server.

Also I modified the permission .pgpass to allow everyone to access it. (Just for testing)


texts: Update with rolling sum grouped by another column

texts:
I'm trying to build a query to update a MySQL 8.0 table that looks like this

-----------------------
user_id | total | delta
-----------------------
      1 |     0 |    30
      1 |     0 |   -10
      2 |     0 |    -5
      2 |     0 |    10
      2 |     0 |   -10
      3 |     0 |    30

Into this:

-----------------------
user_id | total | delta
-----------------------
      1 |    30 |    30
      1 |    20 |   -10
      2 |    -5 |    -5
      2 |     5 |    10
      2 |    -5 |   -10
      3 |    30 |    30

Basically it should compute a rolling sum for each user and update the table with the value at each row. You can think of each row as being a transaction that takes place at a certain timepoint, but for simplicity's sake I've just removed the date info. For this example just presume that they're already ordered by 'date'.

It's easy to do something like this to do a rolling sum over the entire column:

SET @sum := 0;
UPDATE tablename
SET total = (@sum := @sum + delta)

But I'm not sure how to get it to perform separate rolling sums. I also have hundreds of millions of entries, so it needs to be performant.


texts: Need to add a Table Level Constraint to prevent bad data - Constraint is not working

texts:
I have a table that has a flag that can be set to 0 or 1.  I need to make sure for every claim record in that table that the flag is only set 1 once.  So in other words I may have multiple records for one claim and all but one should be set to 0. Below is the table

CREATE TABLE [dbo].[ClaimAccounting_Assignments](
[PracticeID] [int] NULL,
[ClaimID] [int] NULL,
[ClaimTransactionID] [int] NULL,
[InsurancePolicyID] [int] NULL,
[InsuranceCompanyPlanID] [int] NULL,
[PatientID] [int] NULL,
[LastAssignment] [bit] NULL,
[Status] [bit] NULL,
[PostingDate] [datetime] NOT NULL,
[EndPostingDate] [datetime] NULL,
[LastAssignmentOfEndPostingDate] [bit] NULL,
[EndClaimTransactionID] [int] NULL,
[DKPostingDateID] [int] NULL,
[DKEndPostingDateID] [int] NULL,
[RelativePrecedence] [int] NULL
) ON [PRIMARY]

And here is the constraint with the function I created to make this happen

Alter FUNCTION FN_IsLastAssigned(@ClaimId INT)
RETURNS INT
AS

BEGIN
DECLARE @LastAssignment Int

SET @LastAssignment=(
SELECT COUNT(*)
FROM ClaimAccounting_Assignments AS caa
WHERE claimId=@ClaimID AND caa.LastAssignment=1)

RETURN @LastAssignment

END

Here is the Constraint - It should check to see that there are no current records for that claim that have LastAssignment=1 when it inserts into the table:

ALTER TABLE ClaimAccounting_Assignments
WITH NOCHECK
ADD CONSTRAINT  CK_Constraint_CAA_LastAssignments
CHECK ( dbo.FN_IsLastAssigned(ClaimId)=0)

I have a feeling it is something silly I am missing but I don't know.  Any help would be greatly appreciated

Here is some data to do testing with if you need.

INSERT INTO ClaimAccounting_Assignments

VALUES  (1
    , -- PracticeID
     1191
    , -- ClaimID
     12345
    , -- ClaimTransactionID
     0
    , -- InsurancePolicyID
     0
    , -- InsuranceCompanyPlanID
     0
    , -- PatientID
    0
    , -- LastAssignment
     NULL
    , -- Status
     '2012-07-03 16:56:49'
    , -- PostingDate
     '2012-07-03 16:56:49'
    , -- EndPostingDate
     NULL
    , -- LastAssignmentOfEndPostingDate
     0
    , -- EndClaimTransactionID
     0
    , -- DKPostingDateID
     0
    , -- DKEndPostingDateID
     0  -- RelativePrecedence
    ),
    VALUES  (1
    , -- PracticeID
     1191
    , -- ClaimID
     12346
    , -- ClaimTransactionID
     0
    , -- InsurancePolicyID
     0
    , -- InsuranceCompanyPlanID
     0
    , -- PatientID
    0
    , -- LastAssignment
     1
    , -- Status
     '2012-07-04 16:56:49'
    , -- PostingDate
     '2012-07-04 16:56:49'
    , -- EndPostingDate
     NULL
    , -- LastAssignmentOfEndPostingDate
     0
    , -- EndClaimTransactionID
     0
    , -- DKPostingDateID
     0
    , -- DKEndPostingDateID
     0  -- RelativePrecedence
    )
    ,
    VALUES  (1
    , -- PracticeID
     1191
    , -- ClaimID
     12347
    , -- ClaimTransactionID
     0
    , -- InsurancePolicyID
     0
    , -- InsuranceCompanyPlanID
     0
    , -- PatientID
    0
    , -- LastAssignment
     1
    , -- Status
     '2012-07-05 16:56:49'
    , -- PostingDate
     '2012-07-05 16:56:49'
    , -- EndPostingDate
     NULL
    , -- LastAssignmentOfEndPostingDate
     0
    , -- EndClaimTransactionID
     0
    , -- DKPostingDateID
     0
    , -- DKEndPostingDateID
     0  -- RelativePrecedence
    )


texts: RDS: permission denied for relation spatial_ref_sys when copying

texts:
I am copying tables using ogr2ogr from Oracle to PostGIS. When I run try to load these I get,

ERROR 1: ERROR: permission denied for relation spatial_ref_sys when copying <table>@AWS


texts: Using transactional replication to roll back from SQL server 2014 to 2008R2

texts:
I know it sounds crazy. However its just a thought given by DBA,s and management to check on.

We are going to use LS to do migration of around 10 TB Db from 2008R2 to SQl 2014. This DB is 24*7 in used and an OLTP in nature.

In case we need a revert due to XYZ issues, we are thinking to use tran replication from 2014 to 2008R2 , the old server so as to make sure we don't loose any activity since migration. Also on 2014 db will be configured for mirroring .

I am not sure how much above is helpful. Please provide your thoughts if this can be achieved or for any better way of doing this.


texts: BDR error :ERROR: could not open extension control file

texts:
BDR error :
when making changes to the postgresql.conf for
shared_preload_libraries = ’pglogical,bdr’

``FATAL:  could not access file "pglogical": No such file or directory```

also cannot make extension postgres=# create extension BDR;
ERROR:  could not open extension control file "/usr/share/postgresql/10/extension/bdr.control": No such file or directory Its Postgresql 10 and BDR3


texts: How to create function using another functions mysql

texts:
I want to create a function in MySQL using another function but I am getting an error "Explicit or implicit commit is not allowed in stored function or trigger".

This is my code:

DELIMITER $$

CREATE FUNCTION `ufn_gettotalavg` (pUserID bigint,pclass bigint) RETURNS int(11)
BEGIN
  DECLARE learnavg BIGINT;
  DECLARE Assesavg BIGINT;
  DECLARE Pratiecavg BIGINT;
  DROP TABLE IF EXISTS AvgTemp;
  CREATE TEMPORARY TABLE IF NOT EXISTS AvgTemp (SType varchar(20),Percentage bigint);
  DROP TABLE IF EXISTS  assesTemp;
  CREATE TEMPORARY TABLE IF NOT EXISTS assesTemp AS (SELECT  COALESCE(ROUND(((SELECT uf_GetTotalScoreByAssessment( AssessmentID,pUserID,1)) /
  (SELECT ufn_getMaxMarks( AssessmentID))) * 100),0) AS Percentage FROM tr_t_assessments where AssessmentType='LiveTest');
  SET Assesavg= (SELECT COALESCE(round(AVG(Percentage)),0) FROM assesTemp);
  DROP TABLE IF EXISTS  learnTemp;
  CREATE TEMPORARY TABLE IF NOT EXISTS   learnTemp AS (SELECT IFNULL(ROUND((SELECT count(MaterialID) FROM tr_t_materialtracking
  where MaterialStatus=1 and UserID=pUserID and SubjectID = S.SubjectID and MaterialID in
  (SELECT MaterialID FROM tr_m_material where ClassID=pclass and SubjectID = S.SubjectID)) /
  (SELECT COUNT(TopicID) FROM tr_m_material where TopicID IN
  (SELECT TopicID FROM tr_m_subjecttopics where SubjectID = S.SubjectID))* 100) ,0) AS Percentage
  FROM tr_m_subjects S WHERE Status = 1 AND SubjectID IN (SELECT SubjectID FROM tr_m_subjects where ClassID=pclass));
  SET learnavg= (SELECT round(AVG(Percentage)) FROM learnTemp);
  DROP TABLE IF EXISTS  PratiecTemp;
  CREATE TEMPORARY TABLE IF NOT EXISTS PratiecTemp AS (SELECT COALESCE(ufn_getPracticePercentage(pUserID,pclass,S.SubjectID),0) as Percentage
  FROM tr_m_subjects S WHERE Status = 1 AND SubjectID IN (SELECT SubjectID FROM tr_m_subjects where ClassID=pclass)) ;
  SET Pratiecavg= (SELECT ROUND(AVG(Percentage)) FROM PratiecTemp);
  INSERT INTO AvgTemp(SType ,Percentage) VALUES('Learn',learnavg);
  INSERT INTO AvgTemp(SType ,Percentage) VALUES('Pratice',Pratiecavg);
  INSERT INTO AvgTemp(SType ,Percentage) VALUES('Assess',Assesavg);
RETURN (SELECT ROUND(AVG(Percentage)) as consolidatedAvg FROM AvgTemp);
END


texts:
Am considering Amazon RDS to host our Oracle database. Ideally, I'd like to have my daily backup and as many of the transaction logs as possible since copied to an external location in case of Amazon failure. Although they've automated the backup/restore process, I can't rely totally on that and don't see a way to create an offsite backup of my data. The closest thing I can find is in their announcement for cross-region snapshot copies:

"Once a DB Snapshot is copied to a specific AWS Region, any subsequent copy operations of the DB Snapshots of the same DB Instance to that Region will only transfer the data that has changed since the last copy. Thus the subsequent copy operations transfer fewer amounts of data and complete faster."

Seems to imply that I could copy a snapshot multiple times a day, obviously a hack. Anyone have a solution for this?


texts: Install MariaDB and MySQL in CentOS 6 32bit in same server

texts:
I'm trying to install MariaDB with MySQL in the same server but got this errors:

Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-client.i686 0:5.5.32-1 will be installed
--> Processing Dependency: MariaDB-common for package: MariaDB-client-5.5.32-1.i686
---> Package MariaDB-server.i686 0:5.5.32-1 will be obsoleting
---> Package mysql-server.i686 0:5.5.33-1.el6.remi will be obsoleted
--> Running transaction check
---> Package MariaDB-common.i686 0:5.5.32-1 will be installed
--> Processing Dependency: MariaDB-compat for package: MariaDB-common-5.5.32-1.i686
--> Running transaction check
---> Package MariaDB-compat.i686 0:5.5.32-1 will be installed
--> Processing Conflict: mysql-5.5.33-1.el6.remi.i686 conflicts MySQL
--> Finished Dependency Resolution
Error: mysql conflicts with MariaDB-server-5.5.32-1.i686
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest

I read docs here and here but that doesn't help since those guides are for alone installation I though, how I can install MariaDB and MySQL both in the same server by using yum?


texts: sql delete records based on combination of multiple fields in other table

texts:
I need suggestions on deleting records in relation with another table

Main Table:

c1 c2 c3
2  5  3
1  2  8
1  2  NULL
1  4  7
2  4  8
1  3  NULL
2  5  NULL
5  1  3
1  5  1

I need to delete all rows where c1 and c2 are identical to a row where c3 is NULL.

e.g.: for c3 is NULL if record c1=1 and c2=2 delete that record as well as c1=1, c2=2 and  c3=8

The resulting table should be

Main Table:

c1 c2 c3
1  4  7
2  4  8
5  1  3
1  5  1


texts: I need to reference the same two columns multiple times in where clause

texts:
I've got a table that stores survey question information and I'm looking to find out a count based on gender, race, etc My issue is that I have to reference the same columns multiple times in the where clause and I'm not sure the best way to do that.

example:

I need to know the count all of the people that answered yes to survey question 24 AND ALSO yes to questions 4 or 16 or 24.  I have tried messing with Or statements and IN statements but both aren't working for me.  I was looking at using a case statement in the WHERE clause but not sure what that would need to look like.  Any help or advice would be most appreciated!!

  with TheTotal as
(select count(table.name_id)over (partition by
table.other_id) as blah
where sex='M'
 AND table.date between '2019-01-01' and '2019-09-30'
 AND (table.jq_nbr=35 AND table.answer='Y')

 AND (
  (jq_nbr=4 and answer='Y')
  OR (jq_nbr=16 and answer='Y')
  OR (jq_nbr=24 and answer='Y')
      )


texts:
I wanted to get a backup for a very large database with pgAdmin, I started as usual backups, but after a couple of hours it still runs, and I can't cancel it also.

I don't even get to see the file being created.

so a couple of questions:
1. for big databases (more than 8GB) should we use command line only ?
2. if I stop the pgAdmin process would be any side effect on the operational DB ?

BR


texts: EXTPROC issue during 10g to 11g database

texts:
I am currently 10g  to 11g database migration and trying to configure EXTPROC.
EXTPROC32 was configured as part of 10g to execute C++ library and working fine.

It appears that 11g does not provided EXTPROC32 so C++ library compiled for 64 bit and configured as follows.

Listener configuration and confirmed file is available in /opt/UTILfddb/lib/utillib.so. in database server.

LISTENEREXTPROC =
  (DESCRIPTION =
  (ADDRESS = (PROTOCOL = IPC)(KEY = extproc))
  )
SID_LIST_LISTENEREXTPROC =
  (SID_LIST =
  (SID_DESC =
  (PROGRAM = extproc)
  (ENVS = "EXTPROC_DLLS=/opt/UTILfddb/lib/utillib.so,LD_LIBRARY_PATH=/usr/ucblib/sparcv9:$LD_LIBRARY_PATH")
  (SID_NAME = extproc)
  (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db)
  )
  )

Following errors returned when try to execute pl/sql function:

SQL> select gmt_sysdate from dual;
select gmt_sysdate from dual
  *
ERROR at line 1:
ORA-06520: PL/SQL: Error loading external library
ORA-06522: ld.so.1: extproc: fatal: /opt/UTILfddb/lib/utillib.so: open failed:
No such file or directory
ORA-06512: at "UTIL.GMT_SYSDATE", line 4

Do I need to grant any permission to oracle user to execute or load this file?
Can you please help to resolve this issue? Let me know if you need any further information. Thanks

UPDATE

(ora11)% file utillib.so
utillib.so:    ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped

ora11:%file lsnrctl
lsnrctl:        ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped

[oracle@rms-ora11:]%ldd utillib.so
libc.so.1 => /lib/64/libc.so.1
libm.so.2 => /lib/64/libm.so.2
/lib/sparcv9/../libm/sparcv9/libm_hwcap1.so.2
/platform/sun4v/lib/sparcv9/libc_psr.so.1


texts:
I have a set of car data, one of the columns represents the registration year. I want to change this column to age. As the data is from 2010 I assume that the following statement should calculate the age but there is an error?

Select Model, Variant, EngineSize, RegYear, Mileage, Price, Sector from   sampledata
update sampledata
set RegYear = 2010 - RegYear
where sector = 'Van Derived Cars'
and mileage > 200
and regyear != -1
and price != 999999

Can someone please help. I feel this should be very easy to do but everything I try seems to fail!


texts: how to create database(1 CDB and 2 PDB) by dbca silent mode with response file, oracle 12c

texts:
I want create a new database with 1 CDB and 2 PDB, so i write response file and use dbca tool with silent mode. But it must Manual input PDBADMIN password through terminal and only create 1 CDB and 0 PDB. Here is my response file sample:

[GENERAL]
RESPONSEFILE_VERSION=12.1.0
OPERATION_TYPE=createDatabase
[CREATEDATABASE]
GDBNAME=orcl1
SID=orcl1
CREATEASCONTAINERDATABASE=true
NUMBEROFPDBS=2
PDBNAME=pdb1/pdb2
TEMPLATENAME=General_Purpose.dbc
SYSPASSWORD=Welcome1
SYSTEMPASSWORD=Welcome1
EMCONFIGURATION=NONE
DATABASETYPE=MULTIPURPOSE
LISTENERS=LISTENER

anybody knows how can fix it?


texts: I am trying to understand why is my query (update statement) causing clustered index update in the plan? Based on my clustered index (in the where clause), I am just updating columns in my table. What is the need for SQL Server to do clustered index update? I am not updating clustered key for SQL Server to reorganize/order the table according to clustered key, instead it has to update the columns or NC index on them wherein clustered key pointer to the NC rows will be the same (since I am not updating the clustered key). Can someone explain me why such behavior from SQL Server?


texts: How to restore one table from full backup in Sybase ASE

texts:
I need to retrieve 2 data records that were in a single table from yesterday's backup.

How can I restore a single table from a full backup in Sybase?
Or is it possible to retrieve the required records from the dump file itself?
Can I somehow load the dump file without recovering to the production database?

I don't have enough disk space to recover the database to a temporary database on the same disks/server.


texts: Why is a Full Database Backup (as opposed to a level 0 backup) required in Oracle after switching to ARCHIVELOG mode?

texts:
The Oracle 12c 2 Day DBA manual states:

  While whole database backups can be an important element in your
  overall backup strategy, they are also a required step in some
  situations, such as when you enable or disable ARCHIVELOG mode.

(I'm assuming that a "whole" database backup is the same as a "full" database backup as mentioned in the Concepts section.)

In the section "Enabling Archiving of Redo Log Files" the manual states:

  You cannot use backups from before the switch to ARCHIVELOG mode to
  restore and recover the database to a point in time after the switch.
  Thus, if you do not immediately make a backup after switching, then
  you are running your database without a valid backup.

Is the first excerpt of the manual wrong? Is the requirement that you need -any- type of backup? Or does it really need to be a full backup, as opposed to a level 0 backup? If so, why the difference?


texts: DBCC check on Always On Secondary Replica

texts:
What is the best way to schedule DBCC check on all the nodes in Always On availability group. Can we just schedule the same job on all the nodes at the same time  including all the secondaries. In my environment all the back ups are happening on the Primary Node only. Any expert advice will be highly appreciated.
I intend to use the Ola hallengren scripts.


texts:
I'm trying to run a select IF there's something inside a folder:
   INSERT INTO #tb (SubDirectory, Depth, [File])
   EXEC master..xp_dirtree 'C:\folder\', 10, 1

This will populate a table with the name of the file (if there's one inside the folder)
Then, if there's an Excel file there (or not) I will run this query:
IF (SELECT COUNT(1) FROM  #listavarejo) > 0
        SELECT *  FROM OPENROWSET('Microsoft.ACE.OLEDB.15.0','Excel.0;Database=C:\folder\excelfile.xlsx', [Planilha1$])
ELSE
        RAISERROR('NO FILES INSIDE FOLDER.',16,10);

The problem is, when I try to run this:
IF (SELECT COUNT(1) FROM  #listavarejo) > 0
        RAISERROR('there are file inside the folder.',16,10);
ELSE
        RAISERROR('theres no files inside the folder.',16,10);

It works perfectly. If there's a file inside the folder, the error "there's is a file inside the folder", if there's no files, I can receive the error message after else.
But with the select * from openrowset, even with a file inside the folder or not, the query try to runs the select, and obviously I receive the error that the openrowset couldn't find the file.
Even using BEGIN/END I cant fix this.


texts: Utilizing a single core to 100% doesn't show in perf mon as single cpu being fully utilized

texts:
Ever since Windows 2008 I've noticed the following behavior:

I'm using a windows 2008R2 VM with two vCPUs but this also works on a physical server.
I'm using SQL server 2008 R2 for this example.

Make sure you have processor affinity set to AUTO (the default):

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE = 0

(takes affect immediately)

Run a query that will utilize a single core at 100%:

DECLARE @c INTEGER

WHILE 1=1
BEGIN
    SET @c=@c+1
END

Now, open Windows Performance Monitor and add the counters:

Processor: % Processor time _Total
Processor: % Processor time 0
Processor: % Processor time 1

In my pictures, _Total is highlighted (black) and both seperate CPUs are red and green.

What I would expect to see is, one of the cores at 100%, another near 0% and the _Total around 50%.

But instead I get this:

It seems as if the Windows OS Scheduler tries to divide the load across both cores by alternating the task between cores.

Set the processor affinity explicitly to use all cores. From a SQL cpu perpective everything is still the same. SQL Server still has goth both vCPUs are both usable.

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE = 0

When I run the same query I get the graph that I was expecting:

For the most part of the time, the task is running on one core only. In this case the green one.

This last graph is basically how it was in pre windows 2008.

Questions:

What's happening under the hood in the first scenario?
Why is this behaviour changed once I explicitly tell SQL Server that my processor affinity is to use all cores. Instead of leaving it on auto.
In the old days it was very easy to spot a query that was limited by a single core by opening up perf mon and look at all separate cores and find one that was utilized at 100%. Any alternatives to do that now?


texts: With and without autoincremented id performance

texts: I am using myqsl an sql server for quite long time. I am C# developer. I always used to create for each table first column Id as primary key and auto-incremented. Last time i saw my colleague (also C# developer) are not using it at all - no id at all. I found lot of issues when there is no id with pk and ai - hard to delete records etc... Our last conversation was also about performance. He said for performance is better to not use Id with auto increment pk for big data. I would like to get your feedback what's the fact?


texts: INSERT OR REPLACE INTO + SELECT as source in Oracle

texts:
I have two tables A and B.

I want to insert B's content into table A. The issue is there's a row in B that already exists in A.

I've searched and I found some answers talking about MERGE, but no one says how to use it with a SELECT as source.

In summary, I would like to use this statement

INSERT INTO A (data1, data2, dataX)
SELECT B.data1, 'String', B.data2, B.data3
FROM B;

But avoiding existing rows.

Thank you!


texts: How to approach copying a schema for testing?

texts:
I'm an Oracle newb.

The systems in question are strictly for developers to test against. We fire them up and then blow them away afterwards. Problem is that this takes as long as 8 minutes per build which is costing us severely in CI. Four minutes to erase and four minutes to rebuild. Also problematic is that we can only run one set of tests at a time because we only get one schema to test against.

One thing to consider is the possibility that a developer might check in something with screwed up transaction management, like something that does real commits with no transactional rollback.

What we were thinking of as a solution was to keep a copy of the schema that is kept up to date via automated means. Then do a straight copy of the entire schema to a unique name. Do the test run against the copy and then asynchronously remove it at the end.

Generally speaking, what is a good approach for this? We are working with Oracle running on our local machines in a Docker container. What I've been experimenting with is using the expdp/impdp tools. The Docker container adds a pain in the 6 O'clock, but I've been able to get limited success with this technique. In a nutshell I do the copy but then I get errors to the effect that several items pre-exist despite copying to a new name. I expect that this has something to do with the close association between Oracle Schemas and User's. I'll post examples of the errors I'm seeing as soon as I have a new test run.


texts: Adding subscription for replication in sql server

texts:
when i am trying to add subscription on subscriber part in SQL server. error be like :
This database is not enabled for publication.

Can you please suggest me how to solve this issue..
Note:IS Adding Transaction subscription script is different ? & Adding Transaction pull subscription script is different ?
Can you please guide me.
Thanks in advance


texts: Can someone help me with this MySQL code?

texts:
I want to retrieve some date from 2 different tables action and staff.

This is my code:

SELECT ActionID, FeedbackID, StaffID, ActionDate, ActionDetails,
       (SELECT StaffName, Department
        FROM staff
        WHERE staff.StaffID=action.StaffID)
FROM action;

I am getting this error:

  #1241 - Operand should contain 1 column(s)


texts:
I think about developing my new mobile and web app against NuoDB. I believe in their technology. The thing that I care the most is backup and restore in case of data corruption and read / write speed scalability.

From what I've read, you can use NuoDB with Amazon S3 as the archive node for storing the data. That looks very useful because you aren't limited on how much data you can store on a single bucket. Furthermore, It's easy to backup the data because you can detach the archive node and you get to have a backup of the data which you can restore back by attaching the archive node back and let the other node sync with it.

Because I expect a lot of data (terabytes), it seems like a good solution, because I will not need to export the data, compress it and store it on EBS volume(s).

1) How efficient is S3 as archive node with NuoDB for high read/write scenerios?

2) Is this is a good backup option to use with NuoDB (the one that I described above)?

3) Should I be better to use MySQL Load balancers? I've read that their are many solutions that you can use on Amazon RDS to scale the DB horizontally and without any code and DB modification (ScaleBase?).


texts: PgBouncer "no more connections allowed (max_client_conn)"

texts:
I am using PostgreSQL with PgBouncer, and receive the following error in my application:

  08P01: no more connections allowed (max_client_conn)

pgbouncer.ini has the following configuration:

max_client_conn = 100
default_pool_size = 20
pool_mode = session

postgresql.conf has:

max_connections = 100

The PostgreSQL server has 5 databases that are accessed by a single user.

My understanding is that max_connections should be equal to (default_pool_size * # databases), and that max_client_conn should be at least this much. I have also seen the comment in the pgbouncer.ini that the default_pool_size should be equal to the max_client_conn if using session pooling.

So to resolve this error I am encountering, should I simply set default_pool_size = 100? Or increase just the max_client_conn? Or something else?


texts:
My database crashed and my PC and I now only have the .frm and .ibd files left.

Is there any way to restore my database?


texts: Replica identity value on PostgreSQL read-only replica

texts:
In PostgreSQL will the replica identity of a table in a read-only replica database be the same as the replica identity in the primary? I.e., if I run the following query on primary and replica databases will I always get the same result (assuming replication is caught up)?
SELECT CASE relreplident
          WHEN 'd' THEN 'default'
          WHEN 'n' THEN 'nothing'
          WHEN 'f' THEN 'full'
          WHEN 'i' THEN 'index'
       END AS replica_identity
FROM pg_class c
WHERE oid = 'mytable'::regclass;


texts: Log File over 400gigs...need to bring this database back to life

texts:
I'm not a dba so I don't understand the admin side of SQL Server too much.  One of our sharepoint instances blew up and our IT staff asked if I could assist bring it back to life.  I found backups for sharepoint which is great.  The thing about sharepoint is it is huge with many many databases.  I have recovered all but one database (Sharepoint_Config).  This database Sharepoint_Config's log file is over 400 gigs large!

Remember this database is currently "disconnected" have you, meaning I do not have access to it via sql management studio, its just a physical ldf file and an mdf file.  We have software that we use that can restore these physical files back to their original location.  So that part is simple, however I do not want to restore an ldf file that is over 400 gigs large.

Is there anything I can do to fix this mess?


texts:
I will edit some fake data in my database. But if I will make random numbers for each row and column, it is not so random as I want it. The result can you see on image below.

Here is my code I use:

WITH x AS
(
    SELECT mintwee, mineen, nul, pluseen, plustwee
    FROM Topic
)
SELECT mintwee = CAST(RAND()*100 AS INT),
       mineen = CAST(RAND()*100 AS INT),
       nul = CAST(RAND()*100 AS INT),
       pluseen = CAST(RAND()*100 AS INT),
       plustwee = CAST(RAND()*100 AS INT)
FROM x

and this:

WITH x AS
(
    SELECT
        mintwee = CAST(RAND()*100 AS INT),
        mineen = CAST(RAND()*100 AS INT),
        nul = CAST(RAND()*100 AS INT),
        pluseen = CAST(RAND()*100 AS INT),
        plustwee = CAST(RAND()*100 AS INT)
    FROM Topic
)
SELECT mintwee, mineen, nul, pluseen, plustwee
FROM x ;

But both has the same result. I know, I must use an UPDATE statement, but this is only for try if the result is good. After it, I am going to use the UPDATE statement.

I use SQL server with an express 2014 version.


texts: Model Entity Representation of an Organigram

texts: I'm new to database and I'm trying to represent and organigram of a family (Great Grand father, Grand father, father, etc) using MER (Model Entity Relationship), and I'm quite confused on if I should be represent them hierarchical or if I should represent them as Individual entities.


texts: How to ensure that row with same PK inserted replaces old row in MS SQL Server, instead of throwing exception of PK Violation?

texts:
I'm transferring bulk data from a C# DataTable into a SQL Server DB. The DataTable provides values for all 26 columns except the first column, which is an auto-incremental ID The schema skeleton is as follows:

CREATE TABLE (
ID INT IDENTITY(1,1) PRIMARY KEY
TS_Code INT, -- This value is generated at run-time & is unique for each row
Column2 .....
(Upto 25 columns)
)

I want that if a row is entered with the same TS_Code, it should be replaced with the newly inserted row. I wrote a trigger that would delete the old row & insert new one.

CREATE TRIGGER [Trig_Insert]
ON [dbo].[Input]
INSTEAD OF INSERT
AS
BEGIN
    --To check if unique key already exists in DB

    DECLARE @TS_C BIGINT
    SELECT @TS_C = TS_Code FROM inserted    --TS_Code is unique for each row, but hasn't been set as "UNIQUE"

    --If TS_Code already exists
    IF EXISTS(SELECT * FROM Input WHERE TS_Code = @TS_C)
    BEGIN
        delete from Input
        where TS_Code = @TS_C
    END

    --Insert into [Input] table; get an auto-incremented PK ID assigned to new row
    INSERT INTO Input
    SELECT * FROM inserted
END

However, I get the following error when trying to compile this trigger:
"An explicit value for the identity column in table 'Input' can only be specified when a column list is used and IDENTITY_INSERT is ON."

Why is this, when I'm not even sending it explicit Identity values?


texts:
In preparation of my previous Constant Scan question I was experimenting with VALUES in various ways and came across the thing regarding joining of VALUES that looks odd to me.

Setup is simple

CREATE TABLE #data ([Id] int);
INSERT INTO #data VALUES (101), (103);

Then there is a query

DECLARE @id1 int = 101, @id2 int = 102;

SELECT *
FROM (VALUES (@id1), (@id2)) p([Id])
    FULL HASH JOIN #data d ON d.[Id] = p.[Id];

There is nothing special about it. It works and produces its result, if you run it. Here is its execution plan

Removing a row from VALUES however

SELECT *
FROM (VALUES (@id1)) p([Id])
    FULL HASH JOIN #data d ON d.[Id] = p.[Id];

causes optimizer to fail

  Msg 8622, Level 16, State 1, Line 1
  Query processor could not produce a query plan ...

Why? Is there a way (besides putting parameter into temporary table) to make it work using hash algorithm?

Note: This is of no real appliance and serve purpose of studying optimizer behavior and capabilities.

The example above was tested on

  Microsoft SQL Server 2017 (RTM-CU15-GDR) (KB4505225) - 14.0.3192.2 (X64)


texts: Exporting SQL Server DB from my laptop to AWS RDS

texts:
I am following this tutorial from AWS to export a SQL Server database that currently sits on my laptop to Amazon Web Services RDS. In particular I am trying the Generate and Publish script wizard followed by bulk copy.

The script that gets generated starts like this:

CREATE DATABASE [Test]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'Datapump_Test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Datapump_Test.mdf' , SIZE = 1127424KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'Datapump_Test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Datapump_Test_log.ldf' , SIZE = 2160960KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

So my question is what are those paths for (i.e. the C:\..... lines) and are they pointing to files on my laptop? And if so, should I change them when I run this script on the RDS DB? I find that if I leave them there it will error, but if I manually create the DB in SSMS and then comment out these lines and run the script, I still get many errors, but it does create all the table schemas, user and indices etc... so is this the correct approach? If it is, it seems strange that the link I posted above doesn't mention this. This makes me think I've done something wrong. Does anyone have any insight on the matter?


texts: Optimize index on a 2,135,044,521 row table

texts:
I have an I/O problem with a large table.

General stats

The table has the following main characteristics:

environment: Azure SQL Database (tier is P4 Premium (500 DTUs))
rows: 2,135,044,521
1,275 used partitions
clustered and partitioned index

Model

This is the table implementation:

CREATE TABLE [data].[DemoUnitData](
    [UnitID] [bigint] NOT NULL,
    [Timestamp] [datetime] NOT NULL,
    [Value1] [decimal](18, 2) NULL,
    [Value2] [decimal](18, 2) NULL,
    [Value3] [decimal](18, 2) NULL,
    CONSTRAINT [PK_DemoUnitData] PRIMARY KEY CLUSTERED
    (
        [UnitID] ASC,
        [Timestamp] ASC
    )
)
GO

ALTER TABLE [data].[DemoUnitData] WITH NOCHECK ADD CONSTRAINT [FK_DemoUnitData_Unit] FOREIGN KEY([UnitID])
REFERENCES [model].[Unit] ([ID])
GO

ALTER TABLE [data].[DemoUnitData] CHECK CONSTRAINT [FK_DemoUnitData_Unit]
GO

The partitioning is related to this:

CREATE PARTITION SCHEME [DailyPartitionSchema] AS PARTITION [DailyPartitionFunction] ALL TO ([PRIMARY])

CREATE PARTITION FUNCTION [DailyPartitionFunction] (datetime) AS RANGE RIGHT
FOR VALUES (N'2017-07-25T00:00:00.000', N'2017-07-26T00:00:00.000', N'2017-07-27T00:00:00.000', ... )

Quality of service

I think the indexes and statistics are well maintained every night by incremental rebuild/reorganize/update.

These are the current index stats of the most heavily used index partitions:

These are the current statistics properties of the most heavily used partitions:

Problem

I run a simple query on a high frequency against the table.

SELECT [UnitID]
    ,[Timestamp]
    ,[Value1]
    ,[Value2]
    ,[Value3]
FROM [data].[DemoUnitData]
WHERE [UnitID] = 8877 AND [Timestamp] >= '2018-03-01' AND [Timestamp] < '2018-03-13'
OPTION (MAXDOP 1)

The execution plan looks like this: https://www.brentozar.com/pastetheplan/?id=rJvI_4TtG

My problem is that these queries produce an extremely high amount of I/O operations resulting in a bottleneck of PAGEIOLATCH_SH waits.

Question

I have read that PAGEIOLATCH_SH waits are often related to not well-optimized indexes. Are there any recommendations you have for me how to reduce I/O operations? Maybe by adding a better index?

Answer 1 - related to comment from @S4V1N

The posted query plan was from a query I executed in SSMS. After your comment I do some research on the server history. The accual query exceuted from the service looks a bit different (EntityFramework related).

(@p__linq__0 bigint,@p__linq__1 datetime2(7),@p__linq__2 datetime2(7))

SELECT 1 AS [C1], [Extent1]
   .[Timestamp] AS [Timestamp], [Extent1]
   .[Value1] AS [Value1], [Extent1]
   .[Value2] AS [Value2], [Extent1]
   .[Value3] AS [Value3]
FROM [data].[DemoUnitData] AS [Extent1]
WHERE ([Extent1].[UnitID] = @p__linq__0)
AND ([Extent1].[Timestamp] >= @p__linq__1)
AND ([Extent1].[Timestamp] < @p__linq__2) OPTION (MAXDOP 1)

Also, the plan looks different:

https://www.brentozar.com/pastetheplan/?id=H1fhALpKG

or

https://www.brentozar.com/pastetheplan/?id=S1DFQvpKz

And like you can see here, our DB performance is hardly influenced by this query.

Answer 2 - related to answer from @Joe Obbish

For testing the solution I replaced Entity Framework with a simple SqlCommand.
The result was an amazing performance boost!

The query plan is now the same as in SSMS and the logical reads and writes drop to ~8 per execution.

The overall I/O load drop to almost 0!

It also explains why I get a big performance drop after I changed the partition range from monthly to daily. The missing of partition elimination resulted in more partitions to scan.


texts: SQL Server query problem when selecting data from child table based on column in parent table

texts:
I am using MS SQL Server 2008 using Management Studio. I am running a query(below) to extract dates and value based on the 'ticker' column from the parent table, the attribute column (child) , and dates (child) within a date range.

When I run the query, I get no values returned, but when I use primary key (param_id) from the parent or child table, I get the values returned that I want. However, I would like to use the column "ticker" in the parent table as the reference in the where clause.

I have 2 tables.

Parent Table
PK - param_id - int
FK - fed_id -   int    (references different fed reserve ids from other table)
description - varchar(50)
ticker -    varchar(50)
Child Table - holds the data
(PK, FK) param_id - int
(PK, FK) attrib_id -    int  (references different attributes from attribute table)
(PK) [update] - datetime
  value -   decimal(19, 6)

SQL Query below:

  SELECT [update], [value]
  FROM m_econ_fred_source
  JOIN s_econ_fred on m_econ_fred_source.param_id = s_econ_fred.param_id
  WHERE m_econ_fred_source.ticker = 'PHILINFLEXP3M' AND
        attrib_id = 7 AND
        [update] BETWEEN '2014-01-01' AND '2016-03-01'
  ORDER BY [update] ASC

If I do this, it works:

  SELECT [update], [value]
  FROM m_econ_fred_source
  JOIN s_econ_fred on m_econ_fred_source.param_id = s_econ_fred.param_id
  WHERE m_econ_fred_source.param_id = 2 AND
        attrib_id = 7 AND
        [update] BETWEEN '2014-01-01' AND '2016-03-01'
  ORDER BY [update] ASC

I have also made the ticker column unique as well to see if that would help to no avail. I also created the tables using the wizard instead of create statements.

Would anyone be able to tell me what may be wrong with my query or probable causes/solutions to my problem? Thank you. I have looked everywhere for an answer to this problem. I was unable to find a problem similar to this.


texts: Disk errors in one of the SQL cluster nodes and SQL patching was failing with a lot of errors related to disk and permission issues

texts:
We have two cluster nodes in a SQL cluster and the application use this cluster is Microsoft Identity Manager.

Since few month both nodes ( Node 1 and Node2) are throwing disk error and SQL team is tried fixing that multiple time by running CHKDSK. Recently noticed that, resources are unable to move to primary node(Node1) and they did patching that also failed.But somehow they are able to move the resources from Node 2 to primary Node1.. But SQL team is suggesting to rebuild the server( Node1) as the server still has Disk error and patch failures.

So my question here would be, wont that disk replacement work here in this case and copy all the data from old to new disk ?


texts: Sql Server Fulltext catalog gets truncated

texts:
We're facing an interesting issue which we can't reproduce.
On our Sql 2012 from time to time our Fulltext catalog gets truncated, We read MSDN but we didn't find any information if there is such an event which triggers truncation on the catalog.
Thanks in advance!


texts: SSIS 2008R2 - Use Package Config Value in Expression

texts: Is it possible to add a SQL Server package configuration value for an Expression attribute on, for example, a 'Flat File Connection Manager'?


texts: Replication or Sharding for a huge table?

texts:
I have a table that I'm pretty sure in the very near future (within a year) will reach 14 million rows, and I'm trying to scale out my web app before hitting any bottleneck. I'm in dilemma whether I should use sharding or replication?

What is a good strategy for my web app?

EDIT:

Table structure in which I'll have millions of rows:

    id mediumint(8) unsigned NO  NULL  auto_increment
    user_id  smallint(5) unsigned NO  NULL
    jqci_id  mediumint(8) unsigned NO   NULL
    selected_answer  enum('a','b','c','d')  NO  NULL


texts: Calling a stored procedure in a stored procedure

texts:
Environment sql server 2005
My question is

Calling a stored proc within a stored proc within another stored proc.
When is this type of development detrimental?

In order to promote code reusablity we have encapsulated various insert/update statements into stored procedures. So if want to insert A Foo record then you would pass the parameters to the CreateFoo stored proc and go about your day.

Well lets assume a Foo object needs to Create A Bar objects which also Creates a Mark object.

Thus your stored proc will call the foo stored proc which will then call the bar stored proc which will then call the mark stored proc.

When is this not a good idea, and what are my other options. Thank you very much.
If you could, could you put the sources that back up your answer. Again thank you very much.


texts: RDS Replication Error (Apply Error 1406 / Truncation)

texts:
I have a MySQL RDS instance as a master, created a Read Replica from it, and ran some schema change operations on it.  To be specific, I changed the charset and collation of all the tables and columns from utf8 to utf8mb4.  Things were replicating fine, but an error just occurred.

Apply Error 1406: Error; Data too long for column... etc

This is due to lowering the varchar length on some columns from 255 to 191.

I read that you can run some commands to skip replication errors, as described here:  http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_skip_repl_error.html

However, would this "skip" the insert, or, just truncate the data and proceed with the insert?

I'd like the data to be truncated and still added to the table rather than aborting the entire operation, but I'm not sure if that is going to happen or not.  Any suggestions would be welcome!


texts: Is there a UNION ALL equivalent for columns?

texts:
It's possible to simulate a loop in SQL Server with a self-referencing CTE and a union all, something like the following:

declare @a int = 1;
declare @b int = 5;

;with Tbl as (
    select @a a
    UNION ALL
    select (a + 1) a
    from Tbl
    where (a + 1) < @b
)
select * from Tbl

This is useful when you want to create a list of dates from a start date to an end date and then join on those specific dates.

I could also imagine wanting to do that for columns as well. Is there a means of achieving this in SQL Server without using dynamic SQL? I would probably want to save such a query as a view, and not an SP (though I know that this could be achieved via dynamic SQL)


texts: How do you configure Postgres 9.4 for incremental backups and hot standby?

texts:
I want to set up Postgres to do incremental backups. I'm using PostgreSQL 9.4 on Windows with a database that weighs about 1GB and has a growing 10000 records each day.

I understood that the steps necessary to configure the backup are:

Configure "write_ahead_logs" to "on" and "wal_level" in "archives/hot_standby"
Make a "start_basebackup" and copy the data folder
After copying the files do a "stop_basebackup"
Finally copy to another location the WAL files generated

But I have doubts about the Postgres documentation:

How often it is recommended or advisable to make a new base_backup
and, if necessary?
If you can have a slave server configured with
hot_standby and have the incremental backup at the same time?

Also I wonder if there is a PITR manager for Windows, as the Barman for linux.

Thanks


texts: How to effectively filter between several tables in SQL

texts:
I have a main table with another 7 tables attached to it by a key.
I have 10 different filter types in the secondary tables
I will not always have all types of filters in one QUERY, some of them may be equal to NULL.
The problem is that the secondary tables are very large and if they do not need to be accessed by the filter then it is best not to include them in the INNER JOIN,
How to make it the most effective?


texts: Error When Altering Table - invalid ALTER TABLE option

texts:
CREATE TABLE STUDENT
(STUDENT_ID NUMBER (10),
Matriculation_Date DATE,
Enrollment_Status VARCHAR (11),
Enrollment_Effective_Date DATE,
First_Name VARCHAR (20),
Last_Name VARCHAR (20),
Address_Line_1 VARCHAR (20),
Address_Line_2 VARCHAR (20),
Address_Line_3 VARCHAR (20),
Telephone# Number(10),
Spouse_First_Name VARCHAR (20),
Spouse_Last_Name VARCHAR (20),
ACADEMIC_ADVISOR_ID VARCHAR(25));

 CREATE TABLE ENROLLMENT_STATUS
(STUDENT_ID NUMBER(10),
ENROLLED VARCHAR(1),
FULL_TIME VARCHAR(1),
PART_TIME VARCHAR(1),
ON_LEAVE VARCHAR(1),
WITHDRAWN VARCHAR(1));

 CREATE TABLE THESIS_ADVISOR
(THESIS_ADVISOR_ID VARCHAR(20),
First_Name VARCHAR (20),
Last_Name VARCHAR (20),
Office_Telephone_Number Number (10),
Office_Site CHAR (30));

CREATE TABLE ACADEMIC_ADVISOR
(ACADEMIC_ADVISOR_ID VARCHAR(20),
First_Name VARCHAR (20),
Last_Name VARCHAR (20),
Office_Telephone_Number Number (10),
THESIS_COMMITEE_MEMBER VARCHAR(1),
THESIS_TITLE VARCHAR (20));

ALTER TABLE ACADEMIC_ADVISOR
(ADD PRIMARY KEY ASSIGNED_ADVISOR (ADVISOR_ID),
ADD FOREIGN KEY ASSIGNED_STUDENT(STUDENT_ID),
REFERENCES STUDENT (STUDENT_ID),
ADD FOREIGN KEY TH_ADVISOR (THESIS_ADVISOR_ID),
REFERENCES (THESIS_ADVISOR));

Error starting at line : 1 in command -

ALTER TABLE ACADEMIC_ADVISOR
(ADD PRIMARY KEY ASSIGNED_ADVISOR (ADVISOR_ID),
ADD FOREIGN KEY ASSIGNED_STUDENT(STUDENT_ID),
REFERENCES STUDENT (STUDENT_ID),
ADD FOREIGN KEY TH_ADVISOR (THESIS_ADVISOR_ID),
REFERENCES (THESIS_ADVISOR))

Error report -
SQL Error: ORA-01735: invalid ALTER TABLE option
01735. 00000 -  "invalid ALTER TABLE option"
*Cause:
*Action:


texts: Does changing a Postgresql table's tablespace compact it like "vacuum full" or "cluster"?

texts: I need to reclaim disk space in a table in a Postgresql 9.3.2 database to be used for other tables.  The standard suggestions to accomplish this are: "VACUUM FULL" or  "CLUSTER" or copy the contents into a flat file and copy them back in.  When moving an existing table to a new tablespace with an "ALTER TABLE" command, the table's contents are copied.  Can anyone tell me if they are compacted during the "ALTER TABLE" process?


texts: If the query for database too complicated, does it means the design of Database is bad?

texts:
I am new to database, and below is one of my query with it's explanation, I feel it's too complex. It works, but I am worried the design is bad.

Provide a list of customer information for customers who purchased anything written by the most profitable author in the database.

SELECT E.id,NAMES.fname, NAMES.lname, E.address, E.city, E.state,E.country,    E.email, E.phone, E.postalcode
FROM ENTITY AS E, (SELECT DISTINCT T.customer_id
               FROM (SELECT  DISTINCT O.order_id
                     FROM ORDERITEM AS O
                     WHERE O.bid IN (SELECT DISTINCT A.b_id
                                     FROM AUTHOR AS A
                                     WHERE A.name_id IN (SELECT DISTINCT NA.name_id
                                                         FROM (SELECT A.name_id, A.b_id
                                                               FROM (AUTHOR AS A JOIN NAMES AS N ON (A.name_id = N.id))
                                                               GROUP BY A.name_id) AS NA,(SELECT B.b_id AS bid, max(C.total_quantity * B.price), B.title
                                                                                FROM BOOK AS B, (SELECT bid, SUM(quantity) AS total_quantity
                                                                                FROM ORDERITEM AS O
                                                                                GROUP BY O.bid) AS C
                                                                                WHERE B.b_id = C.bid) AS BD
                                                         WHERE NA.b_id = BD.bid))) AS OID JOIN ORDER_TRANSACTION AS T ON (OID.order_id = T.order_id)) AS TID, NAMES
WHERE TID.customer_id = E.id AND
NAMES.id = E.name_id

And This is my design, look very simple.


texts:
I am designing my very first relational database. I am now in the conceptual design, building tables and views, selecting the primary and foreign keys and normalizing everything. But now, I am stock with a particular table; I have a hard time selecting the appropriate foreign keys and I need some help. Here is a fictive, simple case to represent the relationships where I have a problem. Let's say I have those 3 tables:

Person Table

Name

DOB

Name-DOB (concatenated PK)

Name Table

Name (PK)

Meaning

Origin

DOB Table

DOB (PK)

Astrological Sign

So far it's okay, but the next table is where I get confused. Let's say I have a many-to-many recursive relationship that points from my "Person Table" to my "Person Table" again, to map the weddings. For this many-too-many relationship, I need a linking table. This table (so far in my design) goes as follow:

Wedding Table

Husband name

Husband DOB

Wife Name

Wife DOB

Now for the "Wedding Table" I have some questions:

Question 1: Do I need a Primary Key for the linking tables on the many-to-many relationships ? If so, what PK may I use ? The only one I see (other than inventing a random "serial number" for each line of data) would be a concatenated of the 4 attributes in the table, because any concatenate of 2 or 3 attributes has chances to be "not-unique" (let's say someone can have a 2nd wedding when the 1st husband/wife dies, for example. I'm sorry, that's the best example of fictive tables I found to illustrate my real life problem). But isn't it too long, a concatenate of 4 attributes ?

Question 2: Do I need to normalize the linking tables on the many-to-many relationships ? If so, how may I do that ? Because it appears to me that a 2nd (elimination of partial dependency) and 3rd (elimination of the transitive dependency) is impossible since the Husband DOB depends only on the Husband Name (same for the wife)...

Question 3: What Foreign Key do I use to link the "Wedding Table" to the "Person Table" ? Should I use the "Husband Name-Husband DOB" and "Wife Name-Wife DOB" concatenates since the "Name-DOB" is the PK of the "Person Table" ? If so, do I need to keep the individual attributes (that is Husband Name, Husband DOB, Wife Name and Wife DOB) in the Wedding Table since they are not needed in the many-to-many relationship ? Will is pose any problem in the updating and feeding of information in the database via user forms if those individual attributes are not there ?

Thanks to anyone who can help me with my questions !


texts: Unpivot avoid cartesian product on empty columns

texts:
I have an issue with UNPIVOT.

The initial situation is that I receive some files with a fixed number of columns and I have to import them in my Database like that. That means my table has for example 10 departement columns : departement_1, department_2, departement_3, ...

Based on that data, I need to create some views where I only have 1 column because I am limited by the software where I'll be importing the data.

This is a view I made:

SELECT reference, libelle, date_naissance, pays_naissance, nationalite,
    pays_fiscal, nif, us_tin, us_person, type_activite, etat_civil, rac,
    pep, departement, etat_individu, relation
FROM S2i_individu
LEFT JOIN relation_individu
    ON S2i_individu.reference = relation_individu.individu
UNPIVOT (
    pays_fiscal FOR t1 IN (pays_fiscal_1, pays_fiscal_2, pays_fiscal_3)
) UNPIV
UNPIVOT (
    nif FOR t2 IN (nif_1, nif_2, nif_3)
) UNPIV
UNPIVOT (
departement for t3 in (departement_1, departement_2, departement_3, departement_4, departement_5, departement_6, departement_7, departement_8, departement_9, departement_10)
) UNPIV
WHERE import_id = (SELECT TOP(1) id FROM import ORDER BY import_date DESC)

My issue is that by the nature of UNPIVOT, the result is the cartesian product of departement, nif and pays_fiscal. Which means that I will always have 90 rows for each entry in S2i_individu.

Does anyone know a way to limit to 1 row if there's no data ? Like if I don't have any data for pays_fiscal, nif and departement, that I still have a single row in my result?


texts: Ensure data is physically deleted from a data file

texts:
Consider the following:

You create a table space/data file for a table. You insert data into the table and this table has an index.

When you delete the data, it is possible that the data is not actually deleted from the data file. I wrote the program

#include <stdio.h>

int main()
{
   FILE *file = fopen("/some/file/name", "r");
   int ch;
   while ((ch = fgetc(file)) != EOF) {
     if (isprint(ch)) printf("%c", ch);
   }
}

This will display any printable characters. I have found that sometimes the data still exists in the data file (on the OS level)

Is there a way of getting Oracle to zero this data?

EDIT

The URL to the posted question does not solve the problem - as stated in one of the answers - This wouldn't work with indexes since updates are translated to delete+insert in a btree index.*


texts: How to append records from one complete table into a partly populated differently formatted table?

texts:
I have two tables of interest linked in a MS Access 2007 database. The master table called EMPL_MASTER (from which I only have read access, I cannot make changes) updates every time a system administrator adds a new employee. The table which I populate and store some data in is called AllEmployees. The AllEmployees table already has several hundred employee records loaded in, but I need to (automatically) insert new records every time the EMPL_MASTER has new records. The two tables have different column names, as I don't need all of the columns from the master table. This is the query I have tried so far, without success:

INSERT INTO AllEmployees ( OPERATOR_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, etc.. )
SELECT DISTINCT EM.OPERATOR_ID, EM.LAST_NAME, EM.FIRST_NAME, EM.MIDDLE_NAME, EM.etc
FROM dbo_TBL_SQL_OHCMS_EMPL_MASTER EM
WHERE NOT EXISTS (SELECT * FROM AllEmployees
WHERE (AllEmployees.OPERATOR_ID=EM.OPERATOR_ID));

When run, however, this query shows "You are about to append 0 row(s)," although I know that there are employees in the EMPL_MASTER table that do not yet exist in the AllEmployees table.
How can I fix this query?


texts:
I am trying to optimize the query

SELECT
  CONCAT(lastname, ', ', title, ' ', firstname) AS fullName,
  e.lastname,
  e.firstname,
  e.email,
  e.userType,
  e.userID
FROM
  employee e,
  employee_categories ec
WHERE
 1 = 1

which is taking 20 seconds to run. The employee table has 6000 records(number of employees) and employee_categories table has 1000 records. Both tables are indexed and index is the primary key in both tables. The index type is unique and index method is BTREE.

I tried

SELECT
  SQL CACHE CONCAT(lastname, ', ', title, ' ', firstname) AS fullName,
  e.lastname,
  e.firstname,
  e.email,
  e.userType,
  e.userID
FROM
  employee e,
  employee_categories ec
WHERE
  1 = 1

I set query_cache_size to 32MB, ensured have_query_cache is set to Yes, query_cache_type is set to 1. I am using MySql 5.5.24 with InnoDB as storage engine

What else can I try?

Any suggestions would be appreciated.


texts:
Is it a common case to have a table with a single column for the purpose of grouping rows in another table?

I'm inserting data in batches and I want to have an autoincrement key for each batch to be able to group data based on generated id.

Concretely I want to get from this

A
id, x, y, b_id
id PRIMARY KEY
b_id FOREIGN KEY REFERENCES B.id

B
id, timestamp
id PRIMARY KEY

SELECT count(*) as number, B.timestamp FROM A inner join B on A.b_id=B.id
                     where A.x='value' and A.y='value'
                     group by B.id;

to

A
id, x, y, timestamp, b_id
id PRIMARY KEY
b_id FOREIGN KEY REFERENCES B.id

B
id
id PRIMARY KEY

SELECT count(*) as number, A.timestamp FROM A
                         where A.x='value' and A.y='value'
                         group by A.b_id, A.timestamp;

So basically move timestamp to B (denormalize) and use foreign key only for grouping. I want to avoid having join only for the timestamp placed in B. Tables are quite big (60M of rows) and join is very slow. If I still filter on A and have foreign key only for grouping then that would speed up things a lot.

Concretely, I'm using MySQL.


texts: How can I index a text[] field in postgresql?

texts:
I have two columns:

alternate_names text[]
first_name character varying

I am not sure how I can write an index on this type of query?

SELECT "people".* FROM "people"
WHERE (lower(first_name) = 'Balzaretti' OR 'Balzaretti' ILIKE ANY (alternate_first_names));


texts: Differences of MySQL locking for update with WHERE and IN

texts:
So I have a table with id as INT NOT NULL as the PK. When I do query 1 (Trans1) in a transaction I get 3 rows locked, and when I do query 2 (Trans2) also in a transaction, I do also get 3 rows locked.

Example Table:

+----+--------------------------------------+-------------------------+
| id | some_val                             | created_at              |
+----+--------------------------------------+-------------------------+
|  1 | 33aa186d-e2de-4c41-aa50-000000000001 | 2019-03-17 15:15:21.130 |
|  2 | 33aa186d-e2de-4c41-aa50-000000000002 | 2019-03-17 15:15:31.292 |
|  3 | 33aa186d-e2de-4c41-aa50-000000000003 | 2019-03-17 15:16:01.396 |
+----+--------------------------------------+-------------------------+

Trans1:

SELECT * FROM example WHERE id >= 1 AND id <= 3 FOR UPDATE;

INNODB Status

---TRANSACTION 10750, ACTIVE 4 sec
3 lock struct(s), heap size 360, 4 row lock(s)
MySQL thread id 23, OS thread handle 0x7f41f5153700, query id 11504 172.19.0.1 root

Trans2:

SELECT * FROM example WHERE id IN (1,2,3) FOR UPDATE;

INNODB Status

---TRANSACTION 10854, ACTIVE 11 sec
2 lock struct(s), heap size 360, 3 row lock(s)
MySQL thread id 23, OS thread handle 0x7f41f5153700, query id 11652 172.19.0.1 root

Now, even though the results seem similar, they are quite different in regarding of what's actually locked. For starter, no inserts are possible while Trans1 is in progress, while inserts are possible during Trans2. Also, if table has only 3 rows, how is INNODB showing 4 locks (I guess is the next auto increment, thus no inserts possible).

I would like to understand why does this happen, as I've read about gap locking and next-key lock, but none makes sense about what's happening on Trans1.