texts: Do valid to/from columns on a fact table make sense?

texts:
I recently came across "valid from" and "valid to" columns on a fact table. Obviously this is common for dimensions but I've not seen them on fact tables before and can't find any info suggesting they're widely used.

The table granularity is analogous with bank account transactions, so a new fact is generated when a deposit is made, or an amount withdrawn. The reason given for inclusion of the columns is the need for consistent reporting (if we run this report today it must be the same as it was a week ago) combined with poor quality of source systems/data; users are able to go into the source system and say "this deposit was actually only £10, not £100". When this happens a second fact row is inserted and the original row is expired.

In my mind, a new fact row should be inserted to reverse the original in order to maintain the history (applying -£100 in the example) and the updated fact should be inserted (+£10). It feels like working with the valid to/from columns introduces too much complexity for users when reporting as well as the risk of error (summing up both active and expired facts).

Does anyone have experience of this and are there any references which specifically cover it (blog posts, articles or even books)?


texts: Class not registered error in SSAS Data Source View

texts:
Has anyone come across Class Not Registered issue while opening Data Source Views in SSAS using Visual Studio 2015? There are similar posts not haven't found anything exactly the same.

Error text:

  Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)) (msddsp)


texts:
Accidental DB admin here.

Question:

Would you still manually limit max RAM for SQL Server even though the sole purpose of that server is to serve SQL Server database engine only?

I have Microsoft Windows 2012 R2 Server, SQL Server 2012 Std, with 32GB RAM.

Admin constantly login to the SQL Server to use its console and all.

Secondly, how do you check if this Windows server is NUMA enabled?


texts: Insert INTO a table combining * with a column value

texts:
In MySQL, I am trying to insert all values from table A to table B, but I need to change the structure of table B and a column where all the values would be 0.
Is it possible to mix and create such a query as below, or what could be the possible query for this case?!
Many thanks.
$limitid = 50;
$pr1 = "INSERT INTO B SELECT * FROM A where A.id > $limitid ;
//to get
$pr1 = "INSERT INTO B (*,col10) VALUES(*,0);


texts: Replicating contained SQL databases to customer in near real-time

texts:
We host a number of customer SQL Server databases that we need to replicate to external customer sites in near real-time. The main constraint we have is that, for security reasons, our databases are partially contained to ensure customer A can not see customer B's database.
Depending on how big the customer is, the databases range from 5GB to 200GB in size. There are usually up to 5GB of changes on any one day.
Other constraints are that our SQL servers are secured behind firewalls and DMZs. We don't want to expose them directly to customers. A proxy we host in our DMZ may be a workaround. In addition, Availability Groups, Mirroring and Log shipping have version compatibility constraints that would lead to tight coupling which would be undesirable with a single customer, let alone across multiple customers that have potentially a wide variety of SQL versions. We are running SQL Server 2019 Enterprise which AFAIK means customers would need to be running SQL Server 2019 for Availability Groups, Mirroring or Log shipping compatibility.
Options I've considered so far

Change Data Capture (CDC). SQL Server 2019 does not support CDC with contained databases.
Table replication. SQL Server 2019 does not support table replication with contained databases.
Triggers. Triggers are synchronous and can have unwanted side effects such as slowing
down transactions and failing transactions if replication fails.
3rd party software HVR Software and Qlik replication. These both rely on initially enabling CDC to add "supplemental logging" to the log stream. They do not actually rely on CDC for replication but need the additional metadata in the SQL log files that requires enabling CDC. Due to CDC not being supported with contained databases this rules out these options too.
Sending a copy of the complete database (in compressed BACPAC format via SFTP) to the customer on a schedule. The databases are then restored on the customer site. This complete process takes approximately 45 minutes on a fast internet connection so we only schedule this overnight at present. It can't satisfy the near real-time requirement.
Replicating each customer database to an Azure SQL database and opening this up for the customer to access. Is this even viable, can customers subscribe to changes from Azure SQL and how real-time would the data be? What costs are involved?

Has anyone dealt with such a scenario and how did you manage to solve it? What other options are there for replicating databases?


texts: How export a sql server 2008 diagram to PDF filetype?

texts:
I want to have an export from my database diagram to PDF or image types. How can I do this?
I worked with SQL Server 2008 R2.


texts: Why is my AWS RDS (postgres) at a high percentage of cpu with no active sessions?

texts:
I have two postgres db, prod and dev.  Lately, dev has been really slow, so I checked into it, and it appears to be using CPU credits faster than I'm getting them.  I could not figure out why, so I checked the cpu usage history, and it appears that after a strange spike, the baseline has settled to twice the usage it was before:

I could not find any active sessions which would warrant such cpu usage, but just to be sure, I stopped the db, let it sit for a bit, then started it again.  Even after a fresh start, though, the cpu usage is still strangely high, even as compared to a very similar db:

How can I figure out what all of this usage is coming from?


texts: DateDiff Returning Dates and Not Integer Values

texts:
I've added a [DAYS_FROM_TODAY] column to my table to calculate the number of days between today's date and the date a check was written for each record in that table.

I used the following code to update the table:

UPDATE [dbo].[Unpaid]
SET [DAYS_FROM_TODAY] = DATEDIFF(dd, CHECK_DATE, GETDATE())

However when I look at the top 1,000 records of the [dbo].[Unpaid] table, the [DAYS_FROM_TODAY] column has the following appearance instead of integers:

DAYS_FROM_TODAY

1900-09-03 00:00:00.000

1900-01-23 00:00:00.000

Both the CHECK_DATE and DAYS_FROM_TODAY columns are datetime types.
Am I missing an important step?


texts: How to get last messge between two friends in the messge table having id in descending order?


texts: How to create a stored procedure with several delete in(select) statements?

texts:
Our database needs to be periodically cleared of certain items that receive a specific flag (A4) on their id. Our application manages stores and all tables are myISAM. The items can be either in the bought, sold, exchanged, etc tables and they will also exist in the main table that contains all items (the only information the tables share is the id and I can't control that at the moment).

My initial idea was to create the individual delete statements and then place them together in a stored procedure. The stored procedure I created contains:

delimiter $$

create procedure clear_A4(in period char(10))

BEGIN

delete from mov_pedido where pednot in(select distinct titnum from mov_titulo where titnum like 'A4%' and titemi <='2012-01-01' and titvalpag >0);

delete from mov_orcame where orcnot in(select distinct titnum from mov_titulo where titnum like 'A4%' and titemi <='2012-01-01' and titvalpag >0);

delete from mov_nfsven where nfsnum in(select distinct titnum from mov_titulo where titnum like 'A4%' and titemi <='2012-01-01' and titvalpag >0);

delete from mov_movime where movdoc in (select distinct titnum from mov_titulo where titnum like 'A4%' and titemi <='2012-01-01' and titvalpag >0);

delete from mov_titulo where titnum like 'A4%' and titemi<='2011-12-01' and titvalpag >0;

end $$

delimiter ;

These lines refer to items sold. A sale starts with a order, then a quotation, then the actual sale, and finally it gets throw in the main record. The first table mov_pedido has the orders, mov_orcame has the quotations, mov_nfsven has the actual sales and mov_movime records all the transactions. These tables can only be cleared of items that have been paid for by customers but this information can only be found on mov_titulo so I decide to use the select statements with the IN operator. The problem is I also need to clear the mov_titulo table. If I delete the items from mov_titulo first then the information used in the select statements is lost.

Reading up on mySQL,multithreaded databases it seems that if the statements are inside the stored procedure together, they will be processed simultaneously and because the tables are related that way it will cause a problem.

My question is: how can I improve the logic of my delete statement to make them work together? Can I improve my stored procedure to deal with this?

Right now I am executing one statement at a time manually, and that works, but I can see problems down the line, such as entering the wrong date in one of the existing 20 some delete statements for example so I am looking for a better way to do this.

Thank you.


texts: Don't preallocate many gigs of ram in Oracle Database 12c?

texts:
I've just installed Oracle Database 12c Enterprise for Windows. The first thing I've noticed is it preallocates almost 10GB ram. How can I configure Oracle to only allocate and deallocate ram as needed to hold indexes and to perform normal database operations?

To be clear, I am happy for Oracle to use 10GB ram when it's actually needed, so I don't just want to set a lower value on SGA_TARGET and have Oracle struggle to work in the confines of a much smaller limit when a big index is created.

Edit: (answers to comments)

@a_horse_with_no_name I tried disabling pre_page_sga and restarting Oracle but I'm still seeing "Oracle RDBMS Kernel Executable" using up 10GB of RAM after the restart.

The RAM is used for offline scientific scripts whose performance scale according to the amount of free RAM. These scripts are run manually and they must be run on this server as it's the only high performance server we can afford right now.

This wasn't a problem on our old server, it just seems that Oracle 12c allocates 10GB RAM off the bat and never releases it.


texts: Domain Admin Account Accessing SQL Server

texts: I was monitoring some SQL calls using SQL Server Profiler and noticed a Domain Admin account accessing SQL quite a bit.  We do not have a DBA at this point.  There are two developers and I am one of them.  Is this a bad practice and/or a security risk?  I ask because the latest rash of ransomware attacks have a strong emphasis on hijacking Domain Admin account privileges so I am concerned.


texts: Does "consolidation" of query statements improve query performance?

texts:
Assume we have a set of postal addresses A1, A2, A3, ... of the form:

A1 = 124, "Smith", "Rd"
A2 = 142, "Jones", "St"
A3 = 8190, "Smith", "Ln"
A4 = 142, "Via Gracia", NULL
A5 = 190, "Jones", "St"
A6 = 31425, "Wedgewood", "Cir"
A7 = 810, "Via Gracia", NULL
...

where A_N is a vector of 3 components: StreetNumberN, StreetNameN, StreetSuffixN.

No two vectors are ever equal component-for-component, however many share StreetName and StreetSuffix.

Translating this dataset directly into a SQL conditional, using a loop, one might obtain:

WHERE
    (StreetNumber = 124 AND StreetName = "Smith" AND StreetSuffix = "Rd")
 OR
    (StreetNumber = 142 AND StreetName = "Jones" AND StreetSuffix = "St")
 OR
    (StreetNumber = 8190 AND StreetName = "Smith" AND StreetSuffix = "Ln")
 OR
    (StreetNumber = 142 AND StreetName = "Via Gracia")
 OR
    (StreetNumber = 190 AND StreetName = "Jones" AND StreetSuffix = "St")
 OR
    (StreetNumber = 31425 AND StreetName = "Wedgewood" AND StreetSuffix = "Cir")
 OR
    (StreetNumber = 810 AND StreetName = "Via Gracia")

Now, when the StreetName and StreetSuffix match for two condition statements, it is logically possible to  "consolidate" StreetNumbers:

WHERE
    (StreetNumber = 124 AND StreetName = "Smith" AND StreetSuffix = "Rd")
 OR
    (StreetNumber IN(142, 190) AND (StreetName = "Jones" AND StreetSuffix = "St"))
 OR
    (StreetNumber = 8190 AND StreetName = "Smith" AND StreetSuffix = "Ln")
 OR
    (StreetNumber IN (142,810) AND StreetName = "Via Gracia")
 OR
    (StreetNumber = 31425 AND StreetName = "Wedgewood" AND StreetSuffix = "Cir")

Please keep in mind this is just a toy example to demonstrate the principle of "consolidation" that I mean. In practice, there would be many such "consolidations" to perform.

Is there/should there be any performance benefit in consolidating StreetNumbers as demonstrated above?


texts:
I am in the process to move a really small database from Access to MySQL and I have some queries to translate.

I have this really simple query (in Access db):

SELECT InvoiceNumber,
       Field1 + Field2 AS CalcField1,
       Field3 + Field4 AS CalcField2,
       (CalcField1 - CalcField2) / CalcField2 AS Percentage ;

This does not work in MySQL, because it does not allow column aliases in formulas. I learnt here that the solution is to wrap the alias with a (SELECT alias). That works, but the result is really ugly and becomes unreadable. In my case it is:

((SELECT ((SELECT CalcField1) - (SELECT CalcField2))) / (SELECT CalcField2)) AS Percentage

In the former case I can understand immediately the expression, in the latter I cannot.

My question is: is there an alternative way to use aliases in MySQL formulas that have a better readability?


texts: Sql: Count Null and Non-Null values for set of tables and fields dynamically

texts:
I would like to create dynamic sql query where for the set of tables and fields from information schema I will count null and non-null values of these fields.
I have idea to use it something like this (if I am not mistaking)
select distinct DB_NAME() as db_n,
       TABLE_NAME,
       COLUMN_NAME,
       DATA_TYPE as DataType

     into #baseNew
from INFORMATION_SCHEMA.COLUMNS
where left(COLUMN_NAME,4) ='new_'
order by TABLE_NAME,COLUMN_NAME

this next part that should be dynamic would be something like this
select distinct b.db_n,
                TABLE_NAME,
       COLUMN_NAME,
                (select case when column_name is null then 1 else 0 end as NullValues
                        from table_name) NullValues,
                (select case when column_name is not null then 1 else 0 end as NotNullValues,
                from table_name) NotNullValues,
                (select count(1) as numRows from table_name) TotalRows

  from #baseNew b

(and probably insert in some table or data frame for python)

And if that is possible to be run in python script to be run on multiple DBs


texts: Using COPY in psql script does not write to file

texts:
I have a script with multiple queries I am triying to write to a csv file:

copy (
   select /* very long query */
) to '/tmp/report.csv' with csv header;

And I run the script like this

psql -d mydb -f produce_reports.sql

The script seems to work but nothing is written to /tmp.

I usually use \copy but I am unable to use this in a script. From what I understand using \copy require having all the command on the same line which is not a viable option for me.

Any idea why COPY report success but don`t write anything?

P.S tried runnig with sudo, with psql, and tries granting mydb user superuser.


texts: Should I have both Transaction Log Backups and Log Shipping

texts:
I set my database to Full Recovery a few months ago (to allow point-in-time restores) and set up Transaction Log backups with Maintenance Plan.

Now we want to conduct Log Shipping with the same database. In Log shipping setup, it asks for Source Transaction Log Backups, and then the usual copy to Destination Server and Restores, etc.

If I set up Log Shipping Transaction Log Backups, then I will have two transaction log backups. From setting the database to full recovery, and then log shipping.

(a) Is this is not repetitive? Should I remove the original transaction log backup in Maintenance Plan or the one from Log Shipping?

(b) Or perhaps the Log Shipping Transaction Log Backup does not clear out/truncate the Transaction Log ldf file like Maintenance Plan does?


texts:
I am using clearDB with max_connections plan being 40.

This is my jdbc pool configuration:

<bean
   id="dataSource"
   class="org.apache.tomcat.jdbc.pool.DataSource"
   destroy-method="close"

   p:driverClassName="com.mysql.jdbc.Driver"
   p:url="#{ @dbUrl.getJdbc() }"
   p:username="#{ @dbUrl.getUser() }"
   p:password="#{ @dbUrl.getPassword() }"
   p:initialSize="2"
   p:maxActive="10"
   p:maxIdle="5"
   p:minIdle="2"
   p:maxWait="60000"
   p:timeBetweenEvictionRunsMillis="5000"
   p:minEvictableIdleTimeMillis="60000"
   p:testOnBorrow="true"
   p:validationQuery="/* ping */ SELECT 1"
   p:validationInterval="30000"
   p:testWhileIdle="true"
  />

Today we had 197 concurrent users on site and received a lot of

  com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: User 'yyyyy' has exceeded the 'max_user_connections' resource (current value: 40)

which meant that other users were not able to log in and use the site.

It was very frustrating. Might it be because of our maxActive being only 10? What you would propose to refine for this configuration in order to get rid of these error?


texts:
I've got a table that describes some relation between two items. The items are referenced by ID. I'd like to create a trigger, so whenever you create a row (a,b), a symmetrical row (b,a) is created. I've tried creating after trigger, but it exceed stack depth, because it called itself on insert it had within itself. What is the proper way to do it?

Table would look somewhat like this:

CREATE TABLE MY_RELATION(
ID SERIAL,
THING1 INT NOT NULL,
THING2 INT NOT NULL,
PRIMARY KEY (ID)
);

CREATE TABLE MY_THING(
ID SERIAL,
VAL INT NOT NULL
PRIMARY KEY(ID)
);
ALTER TABLE MY_RELATION ADD FOREIGN KEY (THING1) REFERENCES MY_THING(ID);
ALTER TABLE MY_RELATION ADD FOREIGN KEY (THING2) REFERENCES MY_THING(ID);


texts: Is the official term "template database" or "maintenance database"?

texts:
pgAdmin has a field for the "maintenance database". The PostgreSQL manual talks about template databases.
What is the correct term? Why are they not consistent? This is even more confusing since PostgreSQL has "recently" changed the name from "template1" or "template0" into "postgres", suggesting that they didn't like the term "template database", yet continue to use it in the manual... while pgAdmin uses "maintenance database".
And I only vaguely understand the purpose for this to exist. I just know that whenever I have attempted to code a script which does the initial installation of a database, there is nothing for it to "connect to", so that's when I need to use the "maintenance database" when connecting, only to immediately CREATE DATABASE...


texts: ORA-12560: TNS:protocol adaptor error oracle client 19

texts:
I have problem  connect  to  databases  ORACLE. I can't connect through SQL PLUS

I surfed the whole internet everyone writes about oracleSERVICE ORACLESERVICEORCL services but I don't have them installed on WINDOWS SERWER 2016.

Please
Help Solutions problem


texts: Data directory of mysql moving from one drive to different drive?

texts:
I'm moving data directory from D drive which has raid controller hard disk to C drive where OS installed on windows environment.

Here is the issues:

Stop Mysql server
open my.ini --> data directory -> "C drive path"
copy data directory folder to c drive folder
start mysql server

Will it work as this is innodb engines, I have heard we need to restore back as .sql files , copying raw file from one place to another doesn't work.
Is it true or do you have any other workaround as there is 600 GB data size?


texts: DB2 - How to estimate proper BUFFERPOOL size?

texts:
DB2 9.7.
8GB RAM on server.
Currently BP  has 500 MB (number of pages*pagesize)
Database has issue with increased IOPS.
So we think maybe BP should be increased.
How to estimate needed size for BP? Is there some recommendation? Maybe I would like to  put it on 1GB. What can be negative effect? Is it always greater better for BP?


texts: Truncating Table Partitions Containing Data and Global Indexes

texts:
I have to truncate a partition (392,000 records) of a table with 20.000.000 records.

At the moment, I'm making a truncate table and rebuilding indexes. But the table has 37 indexes and it takes 20 minutes to rebuild them.

Taking a look at the Oracle Docs it gives me three options:

1- (This is what I'm doing)

ALTER TABLE sales TRUNCATE PARTITION dec98;
ALTER INDEX sales_area_ix REBUILD;

2-

DELETE FROM sales PARTITION (dec98);
ALTER TABLE sales TRUNCATE PARTITION dec98;

3-

ALTER TABLE sales TRUNCATE PARTITION dec98 UPDATE INDEXES;

So I have two questions:

1- Which of these options would be the faster in my case?
2- What is the meaning (in option 2) of deleting and then truncating the partition?

Thanks in advance!


texts: Performance implications of using OPENQUERY in a view

texts:
Please see this question on stackoverflow :

  I am using an EasySoft ODBC driver to link a SQL Server 2008 R2
  Express instance to Interbase and I am having some difficulty with
  getting metadata from the remote server. From looking about on the net
  the main sugestions all mention using OPENQUERY instead of the four
  part linked server syntax.

  E.G. My current (problematic) approach is...

CREATE VIEW [LIVE].[vwPRDETS]
AS

SELECT *
FROM [LBLIVE]...[PRDETS] WITH (NOLOCK)

  But on some tables I get the error when calling the view...

    Msg 7353, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for
    linked server "LBLIVE" supplied inconsistent metadata. An extra column
    was supplied during execution that was not found at compile time.

  Also, some views I cannot even create because I get the following...

    Msg 7315, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for
    linked server "LBLIVE" contains multiple tables that match the name
    ""SYSDBA"."AUDIT_LBABKP"".

  Although there is only one of the tables mentioned.

  The alternative approach from searching the net seems to be more
  like...

SELECT *
FROM OPENQUERY(<linked sevrer>, 'SELECT <column list> FROM MyTable')

  So, my question is, if I use OPENQUERY in my view definition will SQL
  Server be able to optimise the resulting SQL being sent to Interbase?
  Or is there really not much difference between the two approaches?

It's a cross over subject and would love a dba's POV.


texts: Reducing query cache for mostly INSERT-heavy database?

texts:
What sort of MySQL query cache should I choose considering the following...

Large, rapidly growing database (I got 500,000 rows).
UPDATEs practically never happen.
SELECTs are rare, but when they do happen, they'll usually happen in huge batches.
Lots of small INSERTs.
A row is not usually SELECTed multiple times within a small period of time.
I want to reduce memory footprint (server is using lots of memory).

Would I be looking for a large cache, or small cache?

From what I understand a cache isn't hugely important as repeated SELECTs are very rare.


texts: I am looking for a query to list all users without a mapping to a database. I have over 500 users and I think that there are some SQL or Windows accounts that are not needed any longer.


texts: MSSQL Master db singleuser in sys.databases but multiuser in sp_helpdb

texts:
After opening SSMS, I have found that the master database on one of our production servers is in Single User mode.

Result of select * from sys.databases shows, like SSMS, that the master database has a user_access_desc set to SINGLE_USER.

However, executing sp_helpdb 'master' says it is in MULTI_USER mode.

Nothing problematic noticed on the server. I can access server and master database through multiple threads.

Any ideas?


texts: Controlled Transaction Blocking - Resume Behavior

texts:
When several procedures are blocked on a shared resource, how does sql server pick which one to resume?
Example 1
proc sp_a
begin tran

  select 1 from dbo.lock_tbl with ( tbllockx )

  delay wait for '00:00:01'
  commit / rollback;

Execute
exec sp_a -- on terminal 1
exec sp_a -- on terminal 2
exec sp_a -- on terminal 3

Does sql server choose whether to start the instance on 2 or 3 first?
Example 2
Closer to intended use
proc sp_b @source_param

declare @rows cursor = ... get_rows( @source_param )
while ( ... for each @row )
begin
  begin tran
    select 1 from dbo.lock_tbl with ( tbllockx )

    -- do logic
    exec sp_g @row

  commit / rollback;

  delay wait for '00:00:01'
end

Execute
exec sp_b 'd' -- on terminal 1
exec sp_b 'e' -- on terminal 2
exec sp_b 'f' -- on terminal 3

Will the code above round-robin execute procedure sp_g from each procedure
eg: will it execute sp_g on terminal 1, then execute sp_g on terminal 2, then execute sp_g on terminal 3, then execute g on terminal 1, etc?
Followups :
Does it pass the lock to the procedure which has been waiting the longest?
Is there a way to encourage round robin behavior?
Rationale
( optional )
The database is a low transaction database, but will hit deadlocks when multiple transactions occur at the same time, due to complex execution business logic. A global lock table can be used to eliminate deadlocks via throttling the execution flow to one thread at a time. Keeping the execution synchronous seems more desirable than restructuring the database and UI into asynchronous queues. The work will take place in small transactions and hand off procedure control between the procedures, which will take turns to complete their respective work in chunks, thus slowing the completion of the tasks in exchange for reliable synchronous completion.


texts: Database structure for bonus system in MySQL

texts:
I have a problem with creating the structure for my database in MySQL as I'm new in creating databases. The problem is in this: there are three tables sellers, clients and bonus. Table sellers stores list of sellers with some additional info and table clients stores list of clients also with additional info. Last table bonus should store bonus accounts of clients regarding sellers, i.e. each client can have multiple bonus account (but not more than one bonus account for a seller):

Sellers                    Clients

id    name                 id    name
----------------           ------------
1     McDonalds            1     John
2     Starbucks            2     Bob
3     IKEA                 3     Anna
4     Apple

For example, Bob have two bonus accounts, one for IKEA and one for McDonalds and Anna have four bonus accounts, i.e. for all sellers and so on. All bonus accounts stored in the table bonus.

How to design table bonus in order to apply it as in above mentioned example?


texts: Does innodb_buffer_pool_size accept "*GB" values?

texts:
I thinks innodb_buffer_pool_size is specified in bytes, would typing in values with unit (i.e 1024MB or 1GB) work? Has anyone already tried this?

Specs:

MySQL 5.6


texts: Comprehensive troubleshooting of DatabaseMail

texts:
I am having a problem sending email from one of my servers.

I want to use DatabaseMail and/or msdb.dbo.sp_send_dbmail

I have other production servers that use all the same databaseMail settings and the same SMTP server, and they are all working fine, only this one particular server is not sending emails via Sql Server and I need to find out why.

The first thing I test is the connection to the SMTP server, and for testing that I use Powershell to send an email, and it works fine!!!!

Here is the script as how to send emails using Powershell.

$smtpServer = "200.1.1.223"
$smtpPort = 25
$emailFrom = "donotreply@xxxxx.co.uk"
$emailTo = "mmiorelli@xxxxx.co.uk"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Port = $smtpPort
$subject = "ccatsql"
$body = "test email from ccatsql "
$smtp.Send($emailFrom, $emailTo, $subject, $body)

and that works fine.

the problem that I am having in that server is
DatabaseMail didn't start when an email is queued

when I query the sysmail_event_log I don't see any event related to the emails I have just sent (attempted to).

SELECT * FROM msdb.dbo.sysmail_event_log order by log_date desc

when I query the sysmail_unsentitems I can see all my emails are there, in an unsert state.

SELECT * FROM msdb.dbo.sysmail_unsentitems
ORDER BY send_request_date DESC

This is the piece of code that I am mostly using to test the email sending.
It shows how to send the result of a query by email, but in this case I made the query very simple because I need to get the email working first.

-===========================================================================
-- Failed to initialize sqlcmd library with error number -2147467259
-- that was caused because I had left a piece of rubish code from a previous query
-- and internally it was saying: Invalid column name 'sintMarketID'.
-- but I could only see this using the profile.
--==========================================================================

set deadlock_priority high
set transaction isolation level repeatable read

declare @sub varchar(150)
declare @radhe int
select @sub = '-- Number of rows affected by Hare Krishna job run at '
               + CAST(convert(datetime, getdate(), 100)AS VARCHAR)
--print @sub

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'DBA', -- Change profile name accordingly
        @recipients='mmiorelli@xxxxx.co.uk', -- change mail address accordingly
        @subject = @sub,
        @body_format = 'TEXT',
        @importance='High',
        @sensitivity='Private',
        @file_attachments=NULL,
        @execute_query_database='MSDB',
        @query_no_truncate=0,
        @query_result_header = 1,
        @mailitem_id= @Radhe OUTPUT,
        @query= '

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @SQL VARCHAR(MAX)

SELECT @SQL = ''

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select getdate()
''

EXEC(@SQL)

'

there are similar questions without a conclusion:

DatabaseMail process is shutting down

What is missing?


texts: SQL Server 2016 SP1 - Unable to execute R code

texts:
I have SQL Server 2016 SP1 installed, with R Services. I believe I configued everything based on what I understood to be correct and the output below.

I have restarted SQL Server services and the Launcher services. When I attempt to execute the following test code:

EXEC sp_execute_external_script
  @language =N'R',
  @script=N'OutputDataSet<-InputDataSet',
  @input_data_1 =N'SELECT 1 AS hello'
  WITH RESULT SETS (([hello] int not null));
GO

I get the following error:

Msg 39021, Level 16, State 1, Line 1
Unable to launch runtime for 'R' script. Please check the configuration of the 'R' runtime.
Msg 39019, Level 16, State 1, Line 1
An external script error occurred:
Unable to launch the runtime. ErrorCode 0x80070057: 87(The parameter is incorrect.).

If it helps, my rlauncher.config files looks as follows:

RHOME=D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES
MPI_HOME=C:\Program Files\Microsoft MPI
INSTANCE_NAME=MSSQLSERVER
TRACE_LEVEL=1
JOB_CLEANUP_ON_EXIT=1
USER_POOL_SIZE=0
WORKING_DIRECTORY=C:\Temp

I'm at a bit of a loss to determine how to fix this.


texts: How to tell if there are currently any long running queries executing?

texts:
(Related to this question)

If I want to see if there are currently any long-running queries executing on my database, what's the easiest way of checking?  (I expect the answer will be some query on master..sysprocesses?)


texts: Oracle 11g - Spool file - enforcing not quoted columns

texts:
I am trying to spool large datasets (200k+ row) to csv format and need the columns not to be quoted. This is possible via the export settings of the SQL Developer GUI, but I am trying to do this via a SQL script.  The resulting csv file is then read by another application.  To demonstrate this I run the below script.

set ECHO      off
set FEEDBACK  off
set LINESIZE  60
set RECSEP    off
set TRIMSPOOL on
set VERIFY    off
set PAGESIZE  0
set SQLFORMAT csv
spool y:\aa.csv
SELECT * FROM PRODUCT_COMPONENT_VERSION;
spool off

This gives the ouput

"PRODUCT","VERSION","STATUS"
"NLSRTL ","11.2.0.4.0","Production"
"Oracle Database 11g Enterprise Edition ","11.2.0.4.0","64bit Production"
"PL/SQL ","11.2.0.4.0","Production"
"TNS for Linux: ","11.2.0.4.0","Production"

I need the output as below (NO QUOTED STRINGS)

PRODUCT,VERSION,STATUS
NLSRTL ,11.2.0.4.0,Production
Oracle Database 11g Enterprise Edition ,11.2.0.4.0,64bit Production
PL/SQL ,11.2.0.4.0,Production
TNS for Linux: ,11.2.0.4.0,Production

I would rather not post-process the file (powershell, perl, etc) after generation and before loading as it just adds unnecessary steps to a process.

How can I do a spool file where I can specify the left and right enclosures even setting them to NULL.

Regards,
Tim


texts: PostgreSQL: changing password for a user is not working

texts:
I installed PostgreSQL on EC2 machine and now I want to change the password of user postgres
I do

$ sudo -u postgres psql
psql (9.1.5)
Type "help" for help.

postgres=# ALTER USER postgres WITH PASSWORD 'newpasswd';
ALTER ROLE

Then I exit the shell and try to login with new password

$ psql -U postgres -W
Password for user postgres:
psql: FATAL:  Peer authentication failed for user "postgres"

My PostgreSQL version is

$ psql --version
psql (PostgreSQL) 9.1.5
contains support for command-line editing

What is that I am doing wrong?

Thank you

UPDATE
I made changes in pg_hba.conf and this is how it looks now

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
#local   all             all                                     peer
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Then I restarted the postgres

$ sudo /etc/init.d/postgresql restart
 * Restarting PostgreSQL 9.1 database server                                                                                [ OK ]

I tried logging in again, but failed

$ psql -U postgres -W
Password for user postgres:
psql: FATAL:  Peer authentication failed for user "postgres"


texts: SQL Server 2008, cpu 100% until recreate an index

texts:
I have a 4000000 lines table (SQL Server 2008). This table has a private key composed of two int columns. One corresponds to a PK in another table of the same database. There is a relation between them. The other to a PK of a table in another database. (I don't know if it is important)

Sometimes, there are the same amount of queries as usualy, but the CPU usage grows to 90-100%. And some stored procedures on this table are too long. At this moment, just SELECT queries run on this table.

If I wait, the CPU usage gets normal after 1 or 2 hours.
But I also can drop and recreate the PK index. And the CPU usage gets normal immediately. So I think the problem is related to this index. Am I wrong ?

But the index is not fragmented. I rebuild it every night in a maintenance plan. I even tried to reorganise it after each big insert or delete operations. I also tried to update its statistics. And I also tried to do nothing at all.

I get this problem randomly every day but at different hours, without any way to reproduce it. So I don't know how I could compare the execution plans.

Thanks,

Loic

EDIT : This table is populated with a SSIS package. It inserts missing lines and delete deprecated lines (no update). The insert is done by fast load within an oledb destination limited to 500 lines per batch. It does a bulk insert.
I tried adding several things at the end of this package including reorganize indexes and update statistics.

The issue does not occure during the execution of this SSIS package. It can be for example 15 minutes after its end. It can occure even if there ar few lines manipulated by the package (50 lines for example).


texts: Why do I have to change Media Sets to create a Full Backup to a new file and what does that mean?

texts:
I'm trying to create an ad-hoc Full Backup as a snapshot of the database in time and when I added a new file to backup to I get the following error:

I see I can surpass this error by going to Media Options and changing the Overwrite Media to "Back up to a new media set, and erase all existing backup sets". What does this mean?...will I lose my existing backups if I do that?


texts: Are autovacuum VACUUMs slower than a manual VACUUM?

texts:
I have a few large tables that take days to autovacuum, sometimes resulting in poor performance until the vacuum finishes.  Most of the recommendations I've seen mention doing a manual VACUUM during off-hours so that the autovacuum VACUUMs have less work to do, but if autovacuum takes days to finish is there any way to squeeze a manual VACUUM in (given a 24-hour peak activity cycle)?

I assume a manual VACUUM would still take days to finish?  Or is my assumption incorrect?

Related: Do the different "types" of autovacuum have different performance characteristics?


texts: Data discretization based on rank position

texts:
Lets say I have a table with the schema:

id   value
1    0.3
2    0.6
5    0.1
4    0.7

Provided by

CREATE TABLE foo AS
SELECT * FROM (
  VALUES (1,0.3::float),(2,0.6),(5,0.1),(4,0.7)
) AS x(id, value)

I want to discretize the value column.

The idea is to rank the values and associate 1 to the first half and 2 to the second.

id   value  normalized
1    0.3    1
2    0.6    2
5    0.1    1
4    0.7    2

I cannot figure out how to do that in SQL, any help?

PS: I am using Postgres, so any solution Postgres dependant would be fine as well


texts:
I am trying to deploy a SQL CLR function using the HTTPUtility.UrlDecode method of System.Web but I can't get it to deploy.  Error received:

  .Net SqlClient Data Provider: Msg 6503, Level 16, State 12, Line 1
  Assembly 'system.web, version=4.0.0.0, culture=neutral,
  publickeytoken=b03f5f7f11d50a3a.' was not found in the SQL catalog.

The function (as part of SSDT project):

using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true)]
    public static SqlString udf_UrlDecode(SqlString encodedXML)
    {
        string decodedXML;

        decodedXML = HttpUtility.UrlDecode(encodedXML.ToString());

        return new SqlString(decodedXML);
    }
}

It's in relation to this thread.  I am SQL Server 2014 with VS2012 SSDT and Database Project.  Have tried with other Target Frameworks, eg 3, 3.5, 4 and 4.5.

I have also tried CREATE ASSEMBLY with System.Web, but then have to add other assemblies, eg Microsoft.Build, System.Xaml until they also fail.  I see System.Web is not on the list of Supported Libraries so any ideas?


texts: Veryfing pg_restore database with pg_dump database

texts:
I just did a migration from Postgres 9.4 to 10 database.
How to verify if everything is okay?

I tried to look into huge tables, the rows are different (somehow in Postgres 10 then are more rows than in 9.4)

PSQL 9.4: 14 583 936
PSQL 10:  14 880 545

here are my pg_dump and pg_restore commands which were executed without errors, but took very long process, dump was 12hours, while pg_restore 10hours:

pg_dump    -U postgres -d hive -Z1 -Fc -f /mnt/offline_backups/hive_full_2019-03-01_1630.dmp 2> /mnt/offline_backups/hive_full_2019-03-01_1630.log
pg_restore -U postgres -d hive -v         /mnt/offline_backups/hive_full_2019-03-01_1630.dmp

both commands were without errors whatsoever.

Here some screenshots from pgadmin 4v:

In both DB I just sorted rows count in descending order.

As you can see, rows inserted is quite different.

Here is summary:


texts: Postgresql: create role, schema and change ownership but not as superuser

texts:
I need to create various schema in a database and give ownership of these to specific users. I want to do this in a script, and I rather not be the postgres user or a superuser.
My user pat has createdb and createrole privileges.

pat=> create role toto with login password 'pass';
pat=> create schema toto;
pat=> alter schema toto owner to toto;
ERROR:  must be member of role "toto"

Is there a way to do this without being postgres or superuser ? or should I give pat superuser privileges while running the scripts and drop it after? What is the best procedure?


texts:
I'm trying to start up Oracle DB with startup command.
But error occurred while startup.

dbstart[80]: test: argument expected
Failed to auto-start Oracle Net Listene using /bin/tnslsnr
touch: $ORACLE_HOME/startup.log cannot create
chmod: $ORACLE_HOME/startup.log: No such file or directory
Processing Database instance "$ORACLE_SID": log file $ORACLE_HOME/startup.log
No such file or directory
dbstart[350]: $ORACLE_HOME/startup.log: cannot create

How can I fix it?  Oracle Database 10g Release 10.2.0.1.0 - 64bit Production is running under AIX.


texts: What is the name of this property in (SQL?) Databases?

texts:
Suppose we query Clients Table by Date of Birth, and one of the returned results being a record having client id = CID1234.

Now if we query the same table for client id = CID1234 we should get the record with client id = CID1234 (maybe client id field is primary key or not, that does not matter). Nobody expects that this query to return no results found.

Edit : Trying to make it clear, lets say the returned record had a fieldX = XYZ, now querying on Table with the fieldX = XYZ should also include a record with client Id= CID1234 , if none of the returned records include such a record then it seems something is wrong.

Although this intuitively makes sense, my question is what is the name of this property and is it specific to SQL databases, or a whole category of Databases e.g. Non-SQL etc.

Is there a name for data systems that have such a property? Can this be found within Lambda calculus?

My original problem is SOA related, but knowing this a property in SQL databases made me ask it here, the original question is posted here : https://stackoverflow.com/questions/41215546/is-there-a-principal-that-requiers-total-of-possible-enumeration-results-to-add


texts: MySQL Database Normalization Foreign Keys. One to Many relationship. With Diagram

texts:
Here is my database design. I've been doing a lot of reading and still don't really know what I'm doing. Hoping you guys had tips!

My DB will have millions of entries.
Many "People" rows will have null values below idCounty and some rows will be half null.
DB is written to once, never updated. Read many times and mostly queried through exact idPeople

Questions

To make first,middle,last name searchable, should I make them Primary Keys?
How would I allow to search through ItemCode.codeSection and return all "People" that match?
I hope I understand the foreign key concept. It's on the "Item" table so it allows multiple items per "People". Is this the correct way?
There will be separate lists for different counties, should I create a View for this?

Any tips that would make this better in this scenario are welcome.  Please keep in mind that I'm not doing any updates or changes to rows, just the initial record creation and later lookup.


texts: MYSQL : unable to add privileges to newly created user


texts: Duration of consecutive timestamps by group in sql

texts:
I am new to psql window functions and would like to perform a query on test_table in order to find out every instance of inactive outages per mobile_id, country, and brand and most importantly the duration of the outage in seconds.

Each outage should be treated as a separate event, in other words, when ordered by time, the max(time) - min(time) of an uninterrupted sequence of inactive rows the should be considered the duration.

If there is only a single standalone "inactive" record, there cannot be a difftime and these cases should be assigned a default of 60.

For example:

> test_table
   mobile_id    state country brand                time
1         1a inactive      dk nokia 2018-08-09 19:01:53
2         1a inactive      dk nokia 2018-08-09 18:51:39
3         1a   active      dk nokia 2018-08-09 18:42:10
4         1a inactive      dk nokia 2018-08-09 18:31:23
5         1a inactive      dk nokia 2018-08-09 18:21:27
6         2a   active      dk apple 2018-08-09 18:12:08
7         2a   active      dk apple 2018-08-09 18:01:45
8         2a   active      dk apple 2018-08-09 17:51:29
9         2a   active      dk apple 2018-08-09 17:41:27
10        2a inactive      dk apple 2018-08-09 17:31:32
11        3a   active      de nokia 2018-08-09 17:21:34
12        3a   active      de nokia 2018-08-09 17:11:48
13        3a   active      de nokia 2018-08-09 17:01:46
14        3a   active      de nokia 2018-08-09 16:51:31
15        3a   active      de nokia 2018-08-09 16:41:34

Querying the test_table would produce a result like this:

> outages
  id country brand diff_time
1  1      dk nokia       614
2  2      dk nokia       596
3  3      dk apple        60

How would one structure such a query?


texts: Are "out of the row" fields read when a clustered index is used?

texts:
I know that when VARCHAR(MAX)/NVARCHAR(MAX) columns are used the data is stored out of the row - the data row will have a pointer to another location where the 'large value' is stored.

I have the following questions:

Is each field stored out of the row or only the max ones?
If you are using the clustered index of the table to read the whole record, are fields that are stored out of the row read, too?

  VARCHAR(MAX) or NVARCHAR(MAX) is considered as a 'large value type'. Large value types are usually stored 'out of  row'. It means that the ...


texts: Mongo connection failing with 'Transport endpoint is not connected'

texts:
I have a 2 server installation.

A web server with Apache and a DB server with MongoDB.

I am load testing it, and on ~300 RPS I am getting this error:

  PHP Fatal error:  Uncaught exception 'MongoConnectionException' with
  message 'Transport endpoint is not connected'.

The only thing I am noticing is that right before the fail, I am getting a lots of connections on Mongo:

insert  query update delete getmore command flushes mapped  vsize    res faults locked % idx miss %     qr|qw   ar|aw  netIn netOut  conn       time
     0    659      0      0       0       1       0   208m  1.28g    40m      0        0          0       0|0     0|0    62k   217k   486   03:57:20

Almost 500 connections here... but never more than that!

Mongo is 2.0.3. PHP is 5.3.x (latest of Debian install...)

Help!


texts: Identify table usage in sql server before we stop the load

texts:
I tried browsing this across lot of links but cant get much of help so i am posting this question which sound bit similar.
We have quite some tables which are being planned from our app team that they will stop sending data in next couple of months. So these tables would be good contender for clean up later some time.
However how can i find , if there is way within SQL server, some additional user which we are not aware might be using that table for XYZ operation.
Is there a way natively available or will i need XE to track this? This is an OLTP database server highly active throughout, so running any audit would be something to test.
What should be the approach and how to practically deal with this situation? Please suggest.
Thank you!


texts: Setting up Always On Availability Groups with Windows Server Failover Cluster

texts:
I'm trying to setup Availability groups for SQL Server with two nodes.

I have two SQL Servers at two different locations.

I ran through the failover cluster validation and everything looked good except that it complained about each node only having 1 pair of network interfaces.

I next ran through the wizard to create a cluster and now get the following errors.

  "This resource is marked with a state of 'Offline'. The functionality
  that this resource provides is not available while it is in the
  offline state. The resource may be put in this state by an
  administrator or program. It may also be a newly created resource
  which has not been put in the online state or the resource may be
  dependent on a resource that is not online. Resources can be brought
  online by choosing the 'Bring this resource online' action in Failover
  Cluster Manager."

I went to Failover Cluster Manager to try to bring it online and then get the following message.

  "The operation failed because either the specified cluster node is not
  the owner of the resource, or the node is not a possible owner of the
  resource"

Any suggestions of what I need to do to bring this second node online so I can continue with configuring Always ON?


texts: NoSQL or RDBMS for a Giant Lookup Table?

texts:
What is the best way store leaked website credentials? I've come up with two that would get the job done for the most part, but I am trying to find the fastest and most efficient solution.

Take for example this site, which has a pretty basic search from the look of it, a more basic example would be the site haveibeenpwned. Doing a small scale test, so really I have two questions, first, would this be a good fit for a NoSQL style database? Or should I stick with a RDBMS? The second question is which structure would be able to return the results the fastest (and most completely)?

I could do it with something like this, but it would have lots of redundant information:

{   "uiid": "18c13cb5-bb91-41c6-8267-fd35e868d4c0",
    "username": "foouser",
    "email": "foo@bar.tld",
    "ip": "127.0.0.1",
    "fullname": "Koolaid Man",
    "phonenumber": "+1800123456",
        "creds":    [{
        "hash": "1ed6c8f1465d39e64055e18762fca52850244c9aed2158d6e63406693e775b4a",
        "algo": "sha256",
        "salt": "andpepper",
        "plaintext": "iesh0oow3ieg4thoweonga5aengeaGee3xahbohCuu6oog7oorei2jooPi1hooR6Shi9aoji7wu8ahreeG4see7Thothoo2ev7ch",
        "dateadded": "2012-04-23T18:25:43.511Z"
        },
        {
        "hash": "1ed6c8f1465d39e64055e18762fca52850244c9aed2158d6e63406693e775b4a",
        "algo": "sha256",
        "dateadded": "2012-04-23T18:25:43.511Z"
        }
        ]
}

Using a RDMS, I could so something like this:

CREATE TABLE `identities` (
    `uiid`  UUID NOT NULL UNIQUE,
    `email` INTEGER,
    `username`  INTEGER,
    `fullname`  INTEGER,
    `ip`    INTEGER,
    -- ...
);
CREATE TABLE `hashed_plain` (
    `uhid`  UUID NOT NULL UNIQUE,
    `type`  INTEGER,
    `hash`  TEXT NOT NULL UNIQUE,
    `salt`  INTEGER,
    `plain_text`    INTEGER NOT NULL,
    -- ...
);
CREATE TABLE `hash_instance` (
    `id`    UUID NOT NULL PRIMARY KEY UNIQUE,
    `uhid`  TEXT,
    `uiid`  TEXT,
    -- ...
);
-- ...

I know that both of these are not very good implementations, hopefully someone more knowledgeable than myself can point me in the right direction.

There Are Many "People" (by this I mean the information that identifies the user : username, email, ip ,name, etc.)
There are many "Keys" (by this I mean the information that validates the person: password, hash, salt, etc.)
Each Person can have many keys
Each Key can have many People

I want to be able to search by any field (with the exception of the UUID's) and return everything else associated with that piece of information.  What's the best way to do that?


texts: Stored procedure encryption and master service master key, SQL Server 2008

texts:
Is the Service Master Key (SMK) used to encrypt stored procedures in SQL Server 2008?

I know it is used for data, but what about database objects? If the Service Master Key is not used, then I would expect that if one were to do a database recovery from one server to another (without restoring the SMK) that the stored procedures would continue to work.

Is this true?


texts: how to replace dbms_reputil.from_remote in oracle 12.2.0.1 since All objects removed except REPLICATION_IS_ON, _OFF and _ON


texts:
before 9.0 i had to compile my postgresql-server with "--enable-thread-safety" for http://www.bacula.org

since 9.0 the config-paramater is renamed to "--disable-thread-safety".

The "PostgreSQL Development Group"-Package for CentOS 6.2 doesn't use this parameter.
http://yum.postgresql.org/9.1/redhat/rhel-6-i386/

# pg_config --configure
'--disable-rpath' '--prefix=/usr/pgsql-9.1' '--includedir=/usr/pgsql-9.1/include'
'--mandir=/usr/pgsql-9.1/share/man' '--datadir=/usr/pgsql-9.1/share' '--with-perl'
'--with-python' '--with-tcl' '--with-tclconfig=/usr/lib64' '--with-openssl' '--with-pam'
'--with-krb5' '--with-gssapi' '--with-includes=/usr/include' '--with-libraries=/usr/lib64'
'--enable-nls' '--with-ossp-uuid' '--with-libxml' '--with-libxslt' '--with-ldap'
'--with-system-tzdata=/usr/share/zoneinfo' '--sysconfdir=/etc/sysconfig/pgsql'
'--docdir=/usr/share/doc'
'CFLAGS=-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -I/usr/include/et'
'CPPFLAGS= -I/usr/include/et'

I don't know, how bacula is testing the thread-safety.
http://www.bacula.org/de/dev-manual/Installi_Configur_PostgreS.html#tex2html63

after yum install postgresql91-devel
i have the file libpq.a and the check is successful:

nm /usr/pgsql-9.1/lib/libpq.a | grep pthread_mutex_lock
  U pthread_mutex_lock
  U pthread_mutex_lock

how can i check, that my database-server is thread-safety?


texts: What does the Service Master Key protect?

texts: I am looking for a complete list of items that the Service Master Key is used to protect in SQL Server 2008.


texts: Continue a command on the remote MySQL server when I exit local MySQL workbench?

texts:
I'm issuing a DROP TABLE command that is taking a very long time. If I close down my local MySQL Workbench, will the command still be running and finish on the remote server?

If it doesn't, is there a way for me to keep the remote server running the command while closing down my local connection?


texts: (SQL) User tables are separated into two sections

texts:
Using SQL Plus, I create 13 tables via an .sql file, each containing primary and foreign keys. When I execute "select table_name from user_tables;" they are displayed in this partitioned fashion. The number of tables in each partition varies each time the tables are dropped then recreated. Each table is unique/there are no repeating tables.

I am having difficulties interfacing with the DB via php/OCI (I can access the column headings, but no records are found) and was wondering if this could be the reason. This is after multiple INSERTs, and SQL PLUS queries confirm the existence of records.

Overall, what does this grouping mean, is it problematic and how can I resolve the tables into one grouping?


texts: Relation of PAGELATCH waits with PAGEIOLATCH and WRITELOG

texts:
I am seeing increase in PAGELATCH, PAGEIOLATCH and WRITELOG waits after migration to SQL Server 2017 (from SQL Server 2008). While I can explain increase in PAGEIOLATCH and WRITELOG (we've other disks - HDD instead of SSD), but don't know why PAGELATCH goes up too.

Are they anyhow related?

What I can see around they are not, cause PAGELATCH belongs to memory, while the others to IO.


texts: AlwaysOn Availability Groups Delayed Replica

texts: With AO SQL 2012, is it possible to configure one of the member as delayed replica? I know ideally we would like this to be synced but in case of human error, it would give us the ability to check on the secondary and recover the uncommitted data from the secondary. In one of our earlier setup we used Log shipping where the restore was delayed by an hour to one of the secondary server. Hence during any issues with data, we were able to recover the data faster instead of restoring the whole database and recovering to the point of time. Any input is appreciated...Thanks in advance


texts: Maybe I have misunderstood something but I can recall reading an article which said that there is no reason to create non-clustered indexes on very large data warehousing tables? That is because indexes itself will get so large. One should only consider partitioning these tables. I this right? I don't have real life experience about these cases.


texts: Two different ON DELETE paths, how do I need to change the schema?

texts:
For a project in school I'm designing a website that sells items via auctions. Think eBay, but on the complexity scale of school project. We went through the process of making an ER Diagram and planning things out but we still ran into a snag with the table creation statements in SQL. It's a problem I've never had before.

I know what the problem is now after a bit of digging. There is a table with two foreign keys to two different tables. One of those two tables has a foreign key into the other. That's all fine and great. Every foreign key has an ON DELETE CASCADE except for one, which has ON DELETE SET NULL because it makes business sense. Derby is baffing at the creation of the table with the two keys, because deleting a record in one table would cause two different actions in that table.

A clearer example, in SQL.

CREATE TABLE A (
  PrimaryA BIGINT,
  PRIMARY KEY (PrimaryA)
);

CREATE TABLE B (
  PrimaryB BIGINT,
  ForeignA BIGINT NOT NULL,
  PRIMARY KEY (PrimaryB),
  FOREIGN KEY (ForeignA)
    REFERENCES A(PrimaryA)
    ON DELETE CASCADE
);

CREATE TABLE C (
  PrimaryC BIGINT,
  ForeignA BIGINT
    DEFAULT NULL,
  ForeignB BIGINT
    NOT NULL,
  PRIMARY KEY (PrimaryC),
  FOREIGN KEY (ForeignA)
    REFERENCES A(PrimaryA)
    ON DELETE SET NULL,
  FOREIGN KEY (ForeignB)
    REFERENCES B(PrimaryB)
    ON DELETE CASCADE
);

So if those three statements went through (#3 wont) and someone deleted a row in A, there is the possibility that a row in C would want to be deleted and set null at the same time. While I respect the issue of the concurrency, shouldn't the deletion win? It isn't technically against the business logic for that to happen, but if it did I'd expect the row to be deleted.

So I obviously need to restructure the schema to avoid this pitfall but I can't really think of a way to do it and was hoping there was some magic keyword that would tell Derby to just delete the row and move on with life. If I do need to restructure, the context is that table C is the auction. Each auction has exactly one feedback entry, which is B. It also has exactly one user who is selling the item (A). Feedback (B) entries keep track of the user (A) who places them. In this respect it is the buyer who leaves the feedback.

Now it makes perfect sense to delete the auction (C) if the user (A) who is selling the item has their account deleted. However it doesn't make sense for the auction to be deleted if a rouge user (A) leaves foul feeback (B) on an auction (C) and gets their account deleted. It also doesn't make sense to populate the foreign key to B from C until after the auction is closed and the buyer (A) can leave feedback, which is why it is set to NULL initially. Like I said before, it is technically possible for a user (A) to create an auction (C) and then buy the item from themselves and leave feedback (B) and then have their account deleted. In that case the ON DELETE CASCADE should win from a business logic point of view.

I read through this question and the resolution was to normalize, which I've done already. In that question this was linked, and there they offered a solution to the multiple cascade path that I don't fully understand, but I get enough to believe it isn't a solution to this because I don't actually want to delete along both paths. Another thing mentioned was triggers. I have a rough understanding of triggers at a high level but have never designed or coded one before.


texts: Corrupt date fields causing query failure in Oracle

texts:
Having a real problem with date fields in Oracle. Executing the following works:
ALTER SESSION set NLS_DATE_FORMAT ='DD-MON-YYYY';
select * from mytable where id = 1;

However, changing the NLS_DATE_FORMAT causes the query to fail.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
select * from mytable where id = 1;

I end up with a date specific error.

ORA-01858: a non-numeric character was found where a numeric was expected
Cause: The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model.
Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation.

The table has four date fields, and I've been able to narrow down the fault to certain instances where one specific field is not null, confidental_until.
If I dump this date field, I don't get any evidence of it being corrupt
select dump(confidential_until) from mytable where id = 1;

Typ=12 Len=7: 120,107,12,31,1,1,1

What could be going wrong? Only 187 records of 420,000+ rows have this problem, with 80,000+ having a non-null confidential_until field.


texts: Export SQL query output to a file in a scheduler job

texts:
I need to export data to a csv file in a scheduler job. Here are the commands:

SPOOL "\\AServerName\Output\RMS_DataFileSizes.csv" REPLACE;
SELECT /*csv*/
  TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS "CurrentDTM",
  df.tablespace_name AS "Tablespace Name",
  SUBSTR(df.file_name,INSTR(df.file_name,'RMS_'),20) AS "File Name",
  Round(df.bytes/1073741824,2) AS "Size (GB)",
  decode(e.used_bytes,NULL,0,Round(e.used_bytes/1073741824,2)) AS "Used (GB)",
  decode(f.free_bytes,NULL,0,Round(f.free_bytes/1073741824,2)) AS "Free (GB)",
  decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,1)) AS "% Used"
FROM DBA_DATA_FILES DF,
     (SELECT file_id,sum(bytes) used_bytes FROM dba_extents GROUP by file_id) E,
     (SELECT sum(bytes) free_bytes,file_id FROM dba_free_space GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
  AND df.file_id  = f.file_id (+)
  AND df.tablespace_name = 'RMS_DATA'
ORDER BY df.tablespace_name,df.file_name;
SPOOL OFF;

When I make the "type of job" a "PL/SQL Block", it fails and I get the following error:

"ORA-06550: line 1, column 764:
PLS-00103: Encountered the symbol "\\AServerName\Output\RMS_DataFileSizes.csv" when expecting one of the following:

   := . ( @ % ;
ORA-06550: line 17, column 7:
PLS-00103: Encountered the symbol "OFF" when expecting one of the following:

   := . ( @ % ;
The symbol ":=" was substituted for "OFF" to continue.

What am I doing wrong?


texts:
I wrote this function:
create or replace function field_summaries(gap interval , _tbl anyelement, _col text)
returns table(hour text, average numeric, minimum numeric, maximum numeric) as
$func$
begin
 RETURN QUERY
  EXECUTE format('select time_bucket($1, time)::text as hour,
                         avg(%I),
                         min(%I),
                         max(%I)
                  from %I d
                  where d in (select device_id from sensors)
                  group by hour', _col, _col, _col, _tbl)
     USING gap; --<< this passes the parameter to the EXECUTE command
END;
$func$
language plpgsql
stable;

to which I pass a table name and a column name.I call it like this :
select field_summaries('5minutes', NULL::m_09, 'current');

which works fine. I need to call this function from my node.js code. so I need to pass a string as an argument. When I pass "Null::m_09" as a string  I get an error. Is there a way to alter the function so that I can call it like this :
select field_summaries('5minutes', m_09, 'current');

Right now I get this when I do it:
ERROR:  column "m_09" does not exist
LINE 1: select field_summaries('5minutes', m_09, 'current');
                                           ^

Any help is much appreciated.


texts: Determine databases specified Login has access to on SQL Azure

texts:
I need to obtain a list of all the databases a specific user has access to. To this end I am using the following ...

USE master;
GO
EXECUTE AS LOGIN = N'Login-I-Want-To-Check';
GO
SELECT name ,database_id
  FROM sys.databases
  WHERE database_id > 3 -- Eliminates system tables
  AND HAS_DBACCESS(name) = 1
  ORDER BY name ASC
GO
REVERT; -- Switches context back to caller of this script

This works great on my DEV SQL Server but not on SQL Azure where I get this error ...

Msg 40517, Level 16, State 1, Line 3
Keyword or statement option 'login' is not supported in this version of SQL Server.

Does anyone know how to work around this so I can determine the databases a specified user has access to on Azure?


texts: Transaction replication from SQL Azure database to on-premise SQL server

texts:
I have a use case where I need to keep an on-premise SQL Server database in-sync with my Azure SQL database.

Is there a way I can set up replication / sync from the Azure SQL db to the on-prem db?


texts: Is LIMIT processed before or after retrieving data?

texts:
MySQL.

My heavy query crashed our MySQL server.

If I add LIMIT 10 will it make my query more lightweight? (both for indexed and unindexed queries).

Which way LIMIT are processed: before the rest of the work or after?

I feel that LIMIT makes indexed queries more lightweight, but does not do for unindexed queries. Right thought?


texts: use a string array without CREATE privileges

texts:
I have a set of tables where I need to determine if a particular set of about 1000 row IDs are present in one specific field in each table.
select 'Accounts', row_id Acct_id, pr_postn_id
from schema.s_org_ext
where pr_postn_id in ('1-ABCD','1-BCDE')
UNION
select 'Orders', row_id Order_id, pr_postn_id
from schema.s_orders
where pr_postn_id in ('1-ABCD','1-BCDE')

The above works well for two row_ids, but copy/pasting the IN clause for 1000 IDs is unwieldy when I need to update the list, especially as the list appears in each IN clause on ten UNION-ed queries.
I am looking for an array or a string variable to use in the IN clause and refer to that variable or array in the subsequent queries, in the form UNION SELECT ... WHERE id IN  some kind of huge list of VARCHAR2 IDs .
Normally I would just create a table but in this system I'm a business analyst, not a dba. CREATE privileges won't be happening for me. If I do try create anything in the database, I get
[Error] Compilation (2: 1): ORA-01031: insufficient privileges

I've tried to use a DECLARE block with cursor, but with no success (no  privilege violations, just faulty SQL code)
DECLARE
    TYPE posrec_t IS RECORD (
    pos_row_id VARCHAR2(15) );
    TYPE poslist IS TABLE OF posrec;
    todaypos poslist :=
      poslist (
      posrec ('1-103KO-18'),posrec ('1-10TOV-1'));
    posrec posrec_t;

CURSOR MYCURSOR IS select 'Opportunity' Data_Object, row_id Data_object_Id, pr_postn_id
from siebel.s_oppor
WHERE PR_POSTN_ID IN posrec
UNION
[etc]
BEGIN
OPEN MYCURSOR;
END;

With the DECLARE BEGIN END block syntax, it seems like I could create a short-lived temporary table of 1000 IDs that appear once as a definition statement (and without needing CREATE rights), but I cannot figure out how to make it work.


texts:
I am in an environment where I'd like to grant some users the CREATEDB privilege. But I can't find a privilege option for allowing/disallowing people to DROP their databases.

Is there a way to grant users privileges to create databases, but forbid them to drop databases? (So that they won't accidentally delete each others' databases)


texts: How do I select by a JSONB array containing at least one element within a range?

texts:
I need to model products with properties.  Properties need to be dynamic, in the sense that application users can add / delete properties to products.  For this reason, I am using a JSONB column storing a JSONB object to model those dynamic properties, with each field of the object a dynamic property, for instance:

{
  product_number: "snsug1",
  color: "white",
  weight: [100, 200, 300]
  ...
}

Furthermore, as can be seen in the example above, some properties are allowed to have multiple values, as some products can come in different configurations (different values for the weight property for instance, for packets of sugar of different sizes).  I am modeling those as JSONB arrays.

I need to be able to select products based on some filter values for the properties.

Filtering based on comparisons on single value properties are straightforward (for instance where properties->'color' = '"white"'::jsonb) and overlap queries for multi-value properties are straightforward too when the overlap target contains discreet values, like where properties->'weight' @> any(array[to_jsonb(200), to_jsonb(300)]).

The problem comes with filtering on a multi-value property based on overlap with a range of values (instead of overlap with discreet values as above).  So my question is:  how can I filter on a multi-value property like weight, with criteria that any of the values in the array should fall with a range?

Something like (pseudo code):

select id from products where properties->'weight' @> range(50, 150);

should return the entry in my example above, but

select id from products where properties->'weight' @> range(350, 500);

should not.

The weight @> range(50, 150) bit in the pseudo code query above is what I am asking about.  What should that look like?

If I am barking up the wrong tree altogether and this is not doable with my current model, then what would be a good alternative for solving my problem?  Is there a more appropriate way of modeling the data that can lead to elegant and fast queries of the type above?  The solution needs to perform well on large data sets, it needs to be able to make use of indexes for the above types of queries.


texts:
I just upgraded my server to AWS, and I am using RDS for database. But here, it looks like the same query is taking such a long time as compared to my old server.

I was researching and found that there is RDS cache issue. It every time calls opening table and follow all the steps and in my server it was using cache so it was not opening tables every time. I am using MySQL on RDS.

I have hundreds of thousands of records in one one table. Can someone please help me to get good performance on RDS?


texts: Non-Admin access to SQL Server Configuration Manager

texts: Is there a way that access can be granted for a non-admin user for SQL Server Configuration Manager?


texts: How to reduce fragmentation of nonclustered index on date partitioned log table

texts:
Looking for some advice before I follow my gut on this and rebuild the nonclustered index to lead with our partition key. This is an insert-only table which is never updated or deleted, we keep a sliding window my truncating/merging periods on the left and adding new periods on the right.  The nonclustered index is constantly at 99% fragmentation due to the makeup of data we are inserted (many unique accountids and inventoryids).  I'm wondering what the best way would be to keep a nonclustered index for looking by accountId without all the fragmentation or maybe I shouldn't worry about the fragmentation?
I'm aware that our table doesn't have an explicit unique index, we rely on the [UNIQUIFIER] that is added to changedAt automatically.
Usage pattern: Table is always queried with a date filter (ChangedAt)
Partition function:
CREATE PARTITION FUNCTION pf_Weekly_QuantityHistory (datetime2(2)) AS RANGE RIGHT FOR VALUES ( '01 Jul 2019','08 Jul 2019','15 Jul 2019',etc )

CREATE PARTITION SCHEME [ps_Weekly_QuantityHistory]  AS PARTITION [pf_Weekly_QuantityHistory]
ALL TO ( [ExampleFG] );

CREATE TABLE [dbo].[QuantityHistory](
    [AccountId] [int] NOT NULL,
    [InventoryID] [int] NOT NULL,
    [QuantityBefore] [int] NULL,
    [QuantityAfter] [int] NULL,
    [ChangedAt] [datetime2](2) NOT NULL DEFAULT (getutcdate())
) ON [ps_Weekly_QuantityHistory](ChangedAt)

CREATE CLUSTERED INDEX [CX_QuantityHistory] ON [dbo].[QuantityHistory]
(
    [ChangedAt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION=PAGE)

CREATE NONCLUSTERED INDEX [IX_AccountId] ON [dbo].[QuantityHistory]
(
    [AccountId] ASC,
    [InventoryId] ASC,
    [ChangedAt] ASC
) INCLUDE(QuantityBefore,QuantityAfter) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION=PAGE)


texts: SQL update multiple rows in destination table with same id but different values from source table

texts:
I have two different tables with a common column called id:

Table1
----  -------
id  |  Date
----  -------
1      null
1      null
2      null
2      null
2      null
2      null
3      null
4      null
4      null

Table2
----  -------
id  |  Date
----  -------
1      2013-01-29 08:50:00.000
1      2013-01-29 15:28:00.000
2      2013-01-31 11:56:00.000
2      2013-03-11 16:08:00.000
2      2013-01-31 14:04:00.000
2      2013-01-31 14:08:00.000
3      2013-02-28 23:44:00.000
4      2013-01-31 14:04:00.000
4      2013-01-31 14:08:00.000

I need to write an update statement that will basically put the value of the first row with id=1 from Table2 into Table1 with an id=1 and the second row with id=1 from Table2 into Table1 with an id=1 so that Table1 now looks like this

Table1
----  -------
id  |  Date
----  -------
1      2013-01-29 08:50:00.000
1      2013-01-29 15:28:00.000

Currently this SQL statement

UPDATE Table1
  SET Table1.date = T2.date
  FROM
  Table2 T2
  INNER JOIN Table1 T1
  ON T1.date = T2.date

is making Table1 look like this, where it applies the first date it finds for that id to all the rows with matching ids in the destination table

Table2
----  -------
id  |  Date
----  -------
1      2013-01-29 08:50:00.000
1      2013-01-29 08:50:00.000

Is there a way to udpate Table1 to look like this below:

Table1
----  -------
id  |  Date
----  -------
1      2013-01-29 08:50:00.000
1      2013-01-29 15:28:00.000
2      2013-01-31 11:56:00.000
2      2013-03-11 16:08:00.000
2      2013-01-31 14:04:00.000
2      2013-01-31 14:08:00.000
3      2013-02-28 23:44:00.000
4      2013-01-31 14:04:00.000
4      2013-01-31 14:08:00.000

Is there a way to do this in a SQL statement? I don't want to use a cursor preferably.

There are other columns in both the tables and not all id values in Table1 might be there in Table2 and vice versa. Those other rows and columns I don't want to touch or alter.


texts: How can you insert data into a ARRAY column on a Snowflake table using SQL?

texts:
I am having difficultly finding documentation on how to insert data into an ARRAY column type using SQL on a Snowflake table.

Snowflake Documentation: https://docs.snowflake.net/manuals/sql-reference/data-types-semistructured.html#array

// example table
CREATE OR REPLACE TABLE array_test_table (
  id number,
  ids array
);

// This does not work
INSERT INTO array_test_table (id, ids)
VALUES (1, '[1,2,3]' );

I have tried using using postgres's syntax like ones described here: https://stackoverflow.com/questions/33335338/inserting-array-values

I originally asked this question here: Snowflake Community


texts: How do I enable pgadmin4 (PostgreSQL12) to read and write files in directories of other Linux Users on RHEL8/CentOS8

texts:
While trying to export a query result set from PGAdmin4 on PostgreSQL12 to a CSV I am encountering the following error:
ERROR:  could not open file "/home/linuxusername/Downloads/data.csv" for reading: Permission denied
HINT:  COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.
SQL state: 42501

I'm somewhat new to PGAdmin but am familiar with Linux and SQL. Is there a configuration setting that I'm unaware of to allow PGAdmin to read and write to files and directories of Linux users?
Using the /tmp works fine, but it's a pain to have to keep moving files to and from other directories and it's seriously cramping my work flow and slowing me down. Any help is much appreciated.


texts: Changing storage engine to InnoDb from MyIsam is dropping records from table

texts:
Currently our Database all tables are using MyIsam as a storage engine. We tried changing storage engine of our mysql tables from MyISAM to InnoDB from PHPMyAdmin operations tab. However during the change table records are dropped. So what can be the possible factor causing this problem.

This is what we tried:

We converted test_user table which was having storage engine MyIsam and was having total records 11k. When we converted it into InnoDB the records droped to 9k. This happened for many tables. Not sure what we are doing wrong.


texts: How can I pass the table name as an argument

texts:
I have this code

ALTER PROCEDURE [dbo].[tablepass12]
    (@TableName nvarchar(20))
AS
BEGIN
    DECLARE @SQL VARCHAR(8000)

    SELECT @SQL = 'SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT * FROM "'+@TableName+' -o d:\processing\'+@TableName+'.csv'

    EXEC MASTER..XP_CMDSHELL @SQL
END;

When execute it, I get this error

  Msg 105, Level 15, State 1, Server ICB3110\SQLEXPRESS, Line 1
  Unclosed quotation mark after the character string 'AIR_LINE
      '.

  Msg 102, Level 15, State 1, Server ICB3110\SQLEXPRESS, Line 1
  Incorrect syntax near 'AIR_LINE
      '.


texts:
I have 1.5M rows in a table. Following is the table create code:

CREATE TABLE `jobs` (
    `id` INT(8) NOT NULL AUTO_INCREMENT,
    `job_id` VARCHAR(50) NOT NULL DEFAULT '',
    `title` VARCHAR(255) NOT NULL DEFAULT '',
    `company` VARCHAR(255) NOT NULL DEFAULT '',
    `city` VARCHAR(50) NOT NULL DEFAULT '',
    `state` VARCHAR(50) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `job_id` (`job_id`),
    FULLTEXT INDEX `search` (`title`, `company`, `city`, `state`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM

The query below takes about 0.3 seconds, which is very high.

SELECT id
     , title
     , company
     , state
     , city
FROM `jobs`
WHERE MATCH (title, company, state, city) AGAINST
('senior software engineer in san fransisco california')
LIMIT 0,10

How can I decrease execution time and still provide relevant results? Any suggestions?

So far I tried followings but there is no improvement at all.

Searching in a single field that contains 4 field of data but it did not matter.
Using in Boolean mode>1 or >2, but then it gives me unrelated results
Repairing the table, increasing key_buffer_size to 1GB from 16MB, changing table type to Innodb, changing character set to latin1 from utf8.
Setting ft_max_word_len=1 and ft_stopword_file='' from default values.
I searched online for many hours but no luck so far.

"Explain select..." output:

id;select_type;table;type    ;possible_keys;key   ;key_len;ref;rows;Extra
1 ;SIMPLE     ;jobs ;fulltext;search       ;search;0      ;\N ;1   ;Using where

EDIT: SHOW PROFILE OUTPUT:

|| *Status*                || *Duration* ||
|| starting                || 0.000087   ||
|| checking permissions    || 0.000011   ||
|| Opening tables          || 0.000036   ||
|| init                    || 0.000030   ||
|| System lock             || 0.000013   ||
|| optimizing              || 0.000010   ||
|| statistics              || 0.000021   ||
|| preparing               || 0.000008   ||
|| FULLTEXT initialization || 0.276820   ||
|| executing               || 0.000010   ||
|| Sending data            || 0.000155   ||
|| end                     || 0.000008   ||
|| query end               || 0.000004   ||
|| closing tables          || 0.000016   ||
|| freeing items           || 0.000703   ||
|| cleaning up             || 0.000018   ||


texts:
I need a solution to monitor table permission changes:

how can I implement monitoring of every changes to granted permissions for a specific or any user on a specific table in SQL Server 2008 R2?


texts: Will increased network latency cause table locks in MS SQL Server?

texts:
If I'm making a single call to a SQL Server database over a high-latency network, will table locks occur due to that latency? Say I query table A for some records, and SQL Server has to return that data over a slow network - will there be a read lock on table A while the server sends the response over the network, or does SQL Server release the lock before sending the response?

Also, would the answer vary based on the size of the response? If it just has to return a few KB vs several hundred MB, would that make a difference?

Creating an explicit transaction, running queries, and closing the transaction would obviously cause the tables to lock, since the duration of the transaction is correlated with my latency.


texts: how do you recreate an table structure and table data that is on oracle database A to oracle database B?

texts:
I have an Oracle 11g database with about 10 tables filled with data on a database server A.

I want to recreate everything (table structure and all data inside each of the 10 tables) on a database server B.

Server A and server B don't talk to each other and are completely different machines.

I have heard of something called "database dump". I've also heard of import and export.

What is the easiest way to do this?


texts:
I have a built-in, regular maintenance plan scheduled to run weekly.

The databases are SharePoint databases and a snap manager (netapp snap manager) backs up these databases.

The rebuild maintenance plan is executed whenever the backup runs by the SQL server agent.

Why and how is this job being called?


texts: How to speed up the mongo query for getting the min and max values in range?

texts:
I have a simple collection that contains two fields: timestamp and value (float). I need to get min and max values in the timestamp range.
The collection contains approx 10000000 documents.
I've add indexes like
{"value" : 1, "timestamp" : 1} and {"value" : -1, "timestamp" : 1}.
Queries like
db.getCollection('Sample').find({'timestamp' : {'$gte': 1714284644, '$lte': 1745820644}}).sort({'value':-1}).limit(1)
took  about 1 second. Count without limit shows 525600 documents.
"Explain" show that query covered by one of the idexes. IXSCAN takes most of time.
Is there any way to speed this query up?


texts: DBA Tasks ( SQL Server 2008 ) - Recommendation - Maintenance plan

texts:
I'm a JR DBA on a big company. I was hired 6 months ago, and I'm learning things really fast (I'm the only DBA here).

The old DBA left servers and SQL Server instances for me, to take care of it.

It's a really awesome structure (cluster and etc).

But, he was using scripts and a lot of programming to make backups, cleanup tasks, and all other DBA tasks. Everything is good. I am fixing some things here and there but everything is really good.

I'm learning SQL Server all by myself, and I was reading about MAINTENANCE PLAN.

It's a good pick to use it? If I was hired on a new company, it's a good pick to use maintenance plan ( for me, looks like too "simple", don't want to look a bad DBA).

Thanks.

(For complement, he uses a script to put database names on a table, and then he uses scripts to seek that table and use it on jobs. I can do this with a simple maintenance plan. But he was a Senior DBA. he told me that he likes it, and it's not a problem to use maintenance plan. I just want some opinions)


texts: Recursive calls in stored procedure and temporary tables

texts:
I am trying to create a stored procedure in PostgreSQL which has recursive calls. The procedure creates a temporary table to store results. The temporary table isn't dropped after first call due to which subsequent recursive calls generate error.

How should this be fixed so as to avoid error?
Also, how can I improve performance of this procedure?

Definition of Stored Procedure:

-- Function: ramerdouglaspeuckerfunction2(text, double precision, integer, integer)

-- DROP FUNCTION ramerdouglaspeuckerfunction2(text, double precision, integer, integer);

CREATE OR REPLACE FUNCTION ramerdouglaspeuckerfunction2(IN table_name text, IN epsilon double precision, IN startindex integer, IN endindex integer)
  RETURNS TABLE(up_link double precision, time_stamp timestamp without time zone) AS
$BODY$
declare dmax double precision default 0;
declare idx integer default 0;
declare a integer;
declare b double precision;
declare c double precision;
declare d double precision;
declare e double precision;
declare norm double precision;
declare distance double precision;
declare i integer default 2;
declare j double precision;
declare result result_load_status;
begin
create temp table test(up_link double precision, time_stamp timestamp without time zone) on commit drop;
a := endIndex - startIndex;
execute 'select up_link from ' || table_name || ' where bar2 = '|| startIndex    into d;
execute 'select up_link from ' || table_name || ' where bar2 = '|| endIndex      into e;
b := e-d;
c := -(b * startIndex - a * d);
norm := sqrt(power(a, 2) + power(b, 2));

loop
execute 'select up_link from ' || table_name || 'where bar2=' || i into j;
distance := abs(b * i - a * j + c) / norm;
if distance > dmax
then
idx := i;
dmax := distance;
end if;
i := i+1;
exit
when (i > endIndex);

end loop;

if (dmax >= epsilon)
then
insert into test
select * from ramerDouglasPeuckerFunction2(table_name,epsilon, startIndex,idx);

insert into test
select * from ramerDouglasPeuckerFunction2(table_name,epsilon, idx, endIndex);

else
execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || startIndex into result ;
execute 'insert into test values(' || result.load_status || ','|| result.time_stamp || ')';

execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || endIndex into result ;
execute 'insert into test values(' || result.load_status || ','|| result.time_stamp || ')';

end if;

return query select * from test;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION ramerdouglaspeuckerfunction2(text, double precision, integer, integer)
  OWNER TO postgres;

Error Message:

ERROR:  relation "test" already exists
CONTEXT:  SQL statement "create temp table test(up_link double precision, time_stamp timestamp without time zone) on commit drop"
PL/pgSQL function ramerdouglaspeuckerfunction2(text,double precision,integer,integer) line 15 at SQL statement


texts:
I want to configure the innodb_io_capacity option for my MariaDB server. In the documentation I found the following:

  Faster hard drives, RAID configurations, and solid state drives (SSDs) benefit from higher values.

Does anyone have experience in configuring the value? In what unit is the value measured? Is it IOPS?

For example my setup is:

Samsung NVMe SSD

Reading 500.000 IOPS
Writing 400.000 IOPS

My current value for innodb_io_capacity is 2000 and innodb_io_capacity_max is set to 3000.

Am I right in thinking that I can set innodb_io_capacity up to 500.000, because the hard disk has such a high IOPS value (minus X because of some operation system processes)?

How does the innodb_read_io_threads setting affect the innodb_io_capacity parameter?
My current value is 16 threads (having Intel Xeon E5-1660v4 [8x 3.2 Ghz])


texts: Adding a ROW_NUMBER() with no column to ORDER BY?

texts:
So I'm working on a code golf puzzle and need to add an INT "number" column n to a result while maintaining the current order.

Let's say my source data is:

SELECT value
FROM STRING_SPLIT('one,two,three,four,five', ',')

which returns the items in the original (desired) order:

value
-----
one
two
three
four
five

If I try to use ROW_NUMBER() or RANK() I'm forced to specify an ORDER BY, for which value is the only legal choice:

SELECT value, n = ROW_NUMBER() OVER(ORDER BY value)
FROM STRING_SPLIT('one,two,three,four,five',',')

But this (as expected) sorts value alphabetically instead of leaving it in the desired original order:

value   n
------ ---
five    1
four    2
one     3
three   4
two     5

Joining to a number table doesn't work, since without a WHERE clause I'll get a full outer join.

About the best I could come up with was using a temp table with an identity field:

CREATE TABLE #argg (n INT IDENTITY(1,1), v VARCHAR(99))

INSERT #argg
SELECT value v
FROM STRING_SPLIT('one,two,three,four,five',',')

SELECT *
FROM #argg

DROP TABLE #argg

but this is really long and annoying. Any better ideas?


texts: Minimum permissions required to run sp_Blitz

texts:
I need to run sp_Blitz and a bunch of DMV queries from Glenn Berry's Dr DMV set for SQL 2016. The documentation for sp_Blitz on BrentOzar.com states "Sysadmin permissions. sp_Blitz® checks a lot of system-level diagnostic data."

Is sysadmin really the minimum required permission level or has anybody been able to run this with a more restrictive permission level?

Not sure that I am going to get away with asking for sysadmin privilege on a client's production server.


texts:
I noticed a peculiar thing. This query
declare @t int = 3;

select distinct top(@t)
    Number - Number + ABS(CHECKSUM( CAST(NEWID() as binary(16)) ))
from Numbers as n;

produces this plan

dbo.Numbers is a standard Numbers table - just a contiguous list of integers with a clustered primary key - containing 100k rows.
There are two Compute Scalar operators. The right-hand one computes Number - Number and defines an internal expression. The left-hand one performs the addition and internal function calls. Strangely there is only one Compute Scalar operator if it is rewritten as
Number + ABS(...) - Number

Is there something "interesting" going on here? Or is this a simple artefact of how the parser constructs the execution plan from memos? Both forms complete compilation in search(1). Forcing search(2) using TF8677 does not change the behaviour. TF8606 shows the separation in Tree After Project Normalization.
I don't see this as a problem. I'm just interested to know if there's something to learn here.
Some background
I want to produce a fixed-length list of random numbers. This
ABS(CHECKSUM(NEWID()))

seems to be the way to go. I have a Numbers table (with 100k rows), so using that to drive output seemed reasonable. I wanted sampling without replacement so a DISTINCT would ensure there were no duplicates and a TOP() will give the required resultset size. Putting it together I got
declare @t int = 3;

select distinct top(@t)
    ABS(CHECKSUM(NEWID()))
from Numbers as n;

Sadly the plan was a clustered index scan with a Hash Match (Aggregate). Reading only the minumum number of rows required would be preferable, perhaps with a Flow Distinct to enforce uniqueness.
That quickly lead me to this question. I tried each of the suggestions there. None changed the plan except converting the base table column to a string, appending a string function's empty output and re-converting to an integer (I didn't try a CCI). This felt unsatisfactory.
Recognising that CHECKSUM returned and integer, and that the Numbers table held integers, I tried this no-op:
declare @t int = 3;

select distinct top(@t)
    Number - Number + ABS(CHECKSUM( CAST(NEWID() as binary(16)) ))
from Numbers as n;

This gave the hoped-for plan seen above.
The shape is maintained for all parameter values from 1 to 100k.
For what it's worth I'm running

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)   Sep 24 2019 13:48:23   Copyright (C) 2019 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Home 10.0  (Build 18362: )
Intel Core i7-8550U @ 2GHz, 16GB RAM, SSD


texts: I want to perform multiple date adds on a date range and select those individual dates

texts:
I have a table of schedules with an effective date, discontinue date, and seven boolean fields indicating days of the week when that schedule is in use. Here's a sample:

effDate    discDate   opMon opTue opWed opThu opFri opSat opSun
---------- ---------- ----- ----- ----- ----- ----- ----- -----
2012-10-28 2012-11-27 0     1     0     0     0     0     1

What I need to do is select, for each date range, a table of the individual dates where each record is a date in that range. For the above data set, it would look like this:

dates
----------
2012-10-28
2012-10-30
2012-11-04
2012-11-06
2012-11-11
2012-11-13
2012-11-18
2012-11-20
2012-11-25
2012-11-27

I can't seem to wrap my brain around this one for some reason.


texts: Composite indexes: Most selective column first?

texts:
I’ve been reading about composite indexes and I’m slightly confused about ordering. This documentation (little less than half way down) says

  In general, you should put the column expected to be used most often first in the index.

However, shortly after it says

  create a composite index putting the most selective column first; that is, the column with the most values.

Oracle also says it here in other words

  If all keys are used in WHERE clauses equally often, then ordering these keys from most selective to least selective in the CREATE INDEX statement best improves query performance.

However, I have found a SO answer that says differently. It says

  Arrange the columns with the least selective column first and the most selective column last. In the case of a tie lead with the column which is more likely to be used on its own.

The first documentation I referenced says that you should first go by the most often used whereas the SO answer says that should only be for tie breaking. Then they also differ on the ordering.

This documentation also talks about skip scanning and says

  Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.

Another article says

  The prefix column should be the most discriminating and the most widely used in queries

which I believe most discriminating would mean most distinctive.

All of this research still leads me to the same question; should The most selective column be first or last? Should the first column be the most used and only the most selective on a tie-break?

These articles seem to be contradicting each other, but they do offer some examples. From what I have gathered, it seems to be more efficient for the least selective column to be the first in the ordering if you are anticipating Index Skip Scans. But I'm not really sure if that is correct.


texts: How to remove data redundancy in product table if multiple suppliers are there for same product

texts:
I am designing a database where I have different products and suppliers.
For example, I have a product, "mobile phone", say an Apple 5S. This will be sold through my website from different suppliers like Mapple, Mango.
I can not store the data for every single supplier as it will cause data redundancy.  I have been considering the following columns for the products table:

ProductID
SupplierID
ProductName
Value
SKUID
VendorName
and many more...

We can see the Product name is repeated every time which caused data redundancy.
My main concern is how to avoid data redundancy? How to design the Product table?
Added from comments:

There will be a many to many relationship between Products and Suppliers.
A Vendor and a Supplier are the same. It is a small organisation like shop owner who are listed to us.
There will be number of different shop owners and they all may have the same Product. It will increase everyday. Suppose we have 1000 shop owners (Vendors) with us, and each has 100 Products which are the same, then there will be 1000*100 rows for data which is not required.
There is correction in table Vendor and Supplier are the same. About individual or person. They will never be Supplier.


texts: Moving Clustered Index to new filegroup results in excessive filegrowth

texts:
I've Moved the clustered index on large tables to new filegroups to defrag, compress, and to recover space more easily from the original "primary" filegroup.

Problem:

A 315GB compressed Clustered Index (page compression) resulted in a filegroup that expanded to over 650GB.  (Uncompressed, this table is north of 2TB.)  The free space is not limited to the end of the file, so it cannot be truncated - to shrink this file will be slow and likely result in increased fragmentation.

Let me know if you know how I can create this index without the excessive growth.


texts:
I am trying to wrap my head around creating a delete statement for a table with a composite key.

I would like to create something like:

DELETE
FROM
    table_example1
WHERE
    COLUMN1, COLUMN2
    IN
    (SELECT COLUMN1, COLUMN2 FROM table_example2
    WHERE
        COLUMN_DATETIME > @Period);

Keeping in mind only the combination of COLUMN1 and COLUMN2 is unique, each column on its own is not unique.

I can't seem to get my head around how I would actually do this.


texts: Query execution showing error as login failed

texts:
I want to retrive the table values from other server

Query

EXEC sp_addlinkedserver @server = 'OPSERVER', @srvproduct = 'SQL Server'

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'OPSERVER', @rmtuser = 'SA', @rmtpassword = 'SA'

SELECT * FROM OPSERVER.VENUS.dbo.TABL1

The above query is showing error as login failed for user 'SA'

Treid Query

EXEC sp_addlinkedserver @server = 'OPSERVER', @srvproduct = 'SQL Server'

sp_addlinkedsrvlogin
  @rmtsrvname = 'OPSERVER',
  @locallogin = 'sa',
  @rmtuser = 'sa',
  @rmtpassword = 'sa'

SELECT * FROM OPSERVER.VENUS.dbo.TABLE1

Showing error as "Login failed for user 'sa'"

What wrong in my query.


texts:
I have a fact table with the user reservations with StartDate and EndDate.
I am thinking what is the proper way of serving the duration queries in the reports. Should I calculate DurationDays and DurationHours at the Fact ingestion or this is better to be done at the report run time? Or maybe I should have a separate dimension for durations?