texts: left joining and combining the results where they are not null

texts:
I'm given a MySQL database with tables like this:

show create table c;
CREATE TABLE `c` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  -- and other columns
  PRIMARY KEY (`id`)
)
show create table c_r;
CREATE TABLE `c_r` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_id` int(11) DEFAULT NULL,
  `c_date` date DEFAULT NULL,
  `something_numeric` decimal(8,3) DEFAULT NULL,
  -- and other columns
  PRIMARY KEY (`id`),
  CONSTRAINT ... FOREIGN KEY (`c_id`) REFERENCES `c` (`id`)
)
show create table c_s;
CREATE TABLE `c_s` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_id` int(11) DEFAULT NULL,
  `c_date` date DEFAULT NULL
  `something_numeric` decimal(8,3) DEFAULT NULL,
  -- and other columns
  PRIMARY KEY (`id`),
  CONSTRAINT ... FOREIGN KEY (`c_id`) REFERENCES `c` (`id`)
)

Now, I want, for each date, the sum of the something_numeric values from c_r and the sum of the something_numeric values from c_s. If one of those is null, I still want the other. My output should have a row per date.

I don't see a way to get that. If I join twice on c.id, I get only such dates as exist in both c_r and c_s; so I need to left join twice instead. But then how do I make sure to get one row per date altogether, whether that date exists in only one table, only the other, or both?


texts: What is CP1 in SQL_Latin1_General_CP1_CI_AI?

texts:
I was wondering what the "CP1" means in the collation "SQL_Latin1_General_CP1_CI_AI".

This should be the codepage, but doesn't exist a codepage 1 (for ex. I know it exists a CP850, but never heard about a CP1).


texts: How to Start Express SQL Server instance in Single user mode?

texts:
I want to start my SQL Server Express instance in single user mode to do some troubleshooting such as restoring the Master database.

I am unable to find the "Startup Parameter" options in SQL Server Configuration Manager.

I have found some blogs such as http://mssqlfun.com/2014/09/04/how-to-start-sql-server-in-single-user-mode/, however I am unable to add the -m parameter to the 'Startup Parameters'.

See below for screenshots of my SQL Server Configuration Manager:

My working environment is:

MS SQL Server 2012 Express 11.1.3000.0
Windows Server 2012 R2
SQL server 2012 Express is installed in a Domain
OS is running under VMware

Is there any possible way to connect through the console?  Any idea or suggestion would be appreciated.

I am able to see startup parameters from this T-SQL: 'select * from sys.dm_server_registry;', however I am unable to see the settings in SQL Server Configuration Manager.


texts: Which text-index I should create for xooops engine to achieve better search results?

texts:
In one of projects we use xoops engine to manage content. In mysql slow query log most of queries are following :

SELECT p.uid,f.forum_id, p.topic_id, p.poster_name, p.post_time, f.forum_name, p.post_id, p.subject
            FROM xps33_bb_posts p,
            xps33_bb_posts_text pt,
                xps33_bb_forums f WHERE p.post_id = pt.post_id AND p.approved = 1 AND p.forum_id = f.forum_id AND f.forum_id IN (1,4,61,7,9,17,20,45,35,44,38,39,43,53,54,55,56,57,58,60,14,29,40,26,18,41,33,24,32,59,25) AND ((p.subject LIKE '%rivi%' OR pt.post_text LIKE '%orvi%') AND (p.subject LIKE '%care%' OR pt.post_text LIKE '%gor%'))  ORDER BY p.post_time DESC LIMIT 0, 5;

I can't change them as It would involve changing the engine which is not an option atm. But I can help the engine to search faster. As I understood as the table uses MyIsam engine I can create text indicies which should make search faster, am I right?

So in general for which indicies I should create to avoid following queries run for long time?

+----+-------------+-------+--------+---------------------+---------+---------+--------------------+--------+-----------------------------+
| id | select_type | table | type   | possible_keys       | key     | key_len | ref                | rows   | Extra                       |
+----+-------------+-------+--------+---------------------+---------+---------+--------------------+--------+-----------------------------+
|  1 | SIMPLE      | p     | ALL    | PRIMARY,forumid_uid | NULL    | NULL    | NULL               | 144090 | Using where; Using filesort |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY             | PRIMARY | 4       | diginew.p.forum_id |      1 | Using where                 |
|  1 | SIMPLE      | pt    | eq_ref | PRIMARY             | PRIMARY | 4       | diginew.p.post_id  |      1 | Using where                 |
+----+-------------+-------+--------+---------------------+---------+---------+--------------------+--------+-----------------------------+
3 rows in set (0.00 sec)


texts: What would be the best to store date + hour + timezone in SQL? i dont need a higher precision (minutes, seconds, ms..) the primary reason for this is to reduce memory use and calculation time on big datasets. or should'nt this a concern at all?


texts: Unable to Restart the SQL server Agent from SSCM

texts:
In my machine SQL Server Agent is not expandable with a label (Agent XPs disabled). In this case I run this code to enable it.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

Then Restart the Agent on SQL server Configuration Manager, it throws this error:

  The Request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details

When I checked the Windows event log, I saw this error:

  Failed to initialize SQL Agent log, The system cannot find the path specified.

What is the resolution?


texts: How to determine the collation of a table in PostgreSQL?

texts:
I want to script a check of the collations used on my tables in PostgreSQL, but googling for Postgresql detect collation is not working well for me, and the documentation is not making this an easy search.

Can anyone tell me how I would check this?


texts: How to restore a database then upgrade it in a single SQL script?

texts:
I'm trying to write some SQL that will restore a backup if the database doesn't exist, then run SQL on the database. It is used for installing a database we received from a 3rd party in the form of a backup. For a new installation we restore the backup then apply our own patches to it. For an upgrade we just apply our own patches to the existing database.

I can get it to restore, and I can get it to patch, but I can't get it to do both from one script because it doesn't like to USE the database if it didn't exist when the script starts.

This is the outline of my script:

USE master

IF NOT EXISTS (SELECT NULL FROM sys.databases WHERE name = 'MyDatabase')
BEGIN
    RESTORE DATABASE MyDatabase ...
END
GO

USE MyDatabase -- The database exists by this point

-- Apply patches ...

The line USE MyDatabase throws an error:

Msg 911, Level 16, State 1, Line 2
Database 'MyDatabase' does not exist. Make sure that the name is entered correctly.

I guess it's parsing the whole script before it executes anything because by the time it gets to the USE the database either already existed or was restored, either way it now does exist.

Is there a way to RESTORE a database then USE it in the same script, or do I just have to separate the restore from the update?


texts: Visual Studio SSDT Ignore Query Store Options in Publish Profile

texts:
Is there any way in Visual Studio SSDT Publish Profile, to Totally Ignore Query Store Options Completely?

It keeps asking us to Rerun publish code below. I go to Database advanced settings, and turn it off. Even after its set off, it keeps asking to be in publish profile. Is there any way to Just Ignore Query Store in Publish profile, rather than trying to just turn it off?

I am looking through advanced options in publish profile.

BEGIN
    ALTER DATABASE [$(DatabaseName)]
        SET QUERY_STORE (QUERY_CAPTURE_MODE = NONE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 367))
        WITH ROLLBACK IMMEDIATE;
END


texts: Postgresql rds: relation .... does not exists

texts:
This seems to be a question that is asked alot all of the answers that I have read do not seem to pertain to this situation.  I maybe wrong, I am a newbie but here is the question.
I am using  psl 11 in amazon rds.  I have one db on the instance with 2 schemas.  It is running slow in testing so I was going to run the  missing index query
SELECT   relname
    ,seq_scan - idx_scan AS too_much_seq
    ,CASE
        WHEN
          seq_scan - coalesce(idx_scan, 0) > 0
        THEN
          'Missing Index?'
        ELSE
          'OK'
      END
     ,pg_relation_size(relname::regclass) AS rel_size, seq_scan, idx_scan

FROM    pg_stat_all_tables
WHERE   schemaname IN( 'schemaname')
AND pg_relation_size(relname::regclass) > 80000
ORDER BY  too_much_seq DESC;
When I run it in this new environment I am getting a relation does not exists.
I have checked permissions, ownership, lowercase, I can see the table, run queries against the table, however I can't seem to get this query to work.  If I do a SELECT * from pg_stat_all_tables  I can see all of the tables that do not exists in he other query.
I am not sure what is wrong. I am pretty sure it is in the   pg_relation_size(relname::regclass)  section, I think that is where it is not finding the relation,  however I have been unable to find documentation on this function that would appear to shed light on my issue.
Any assistance would be appreciated.


texts: From PostgreSQL to SQL Server (No User/Pass)

texts:
Some of you MAY be able to crack a really tough nut...

I have to move all the data from a PostgreSQL (9.1 - Windows) database to SQL Server because the company I am contracting with is upgrading the old program with a newer .NET version.

The real nut is that the User/Password for Postgres are baked in the old program... Of course the guy who wrote it is gone. No source code or any way to contact him. (Smaaaart)

I was able to obtain the WHOLE Postgres directory (Bin, Data... The whole thing) and I hope it can be done.

Is there a way out of this mess, or should I just blow the contract and look for something else?

THANK YOU!


texts: Is XFS still the best choice for mongodb?

texts:
There is this guide for production servers:

https://docs.mongodb.com/manual/administration/production-notes/#kernel-and-file-systems

It tells that for the WiredTiger storage engine, XFS is strongly recommended against ext3 or ext4. But that article must be very old. Nowdays we are using Linux kernel 4. The 2.6 series of kernels date back to 2003. That is incredibly old.

So here comes the question: did something change in the last 14 years? (This sounds a silly question in the IT field, and it feels ridiculous to write down.) Is XFS still prefered for a production MongoDb cluster? Or should we use ext4 instead, or something else?


texts: How to delete the remote files older than N days on schedule from SQL Server 2012 (with SqlServerAgent job)?

texts:
Struggling to execute PowerShell commands in SqlServer Agent job of MS SQL Server 2012 R2 (Windows Server 2008R2, Windows 7 Prof)

I need to delete (.bak files older, than, say, 7 days in remote file share.

Before inserting the command into SqlServerAgent job, I tried to execute it from Windows command line:

using forfiles :
  forfiles /P "\\RemoteHostName\sharedFolderName" /s /m *.bak /D -7 /C "cmd /c del @path"

gives the error:
"ERROR: UNC paths (\machine\share) are not supported"
using Windows PowerShell:

dir \\RemoteHostName\sharedFolderName |? {$_.CreationTime -lt (get-date).AddDays(-7)} | del

The latter deletes the required remote files when used from Windows PowerShell command prompt but when executed in/from an SqlServerAgent job - fails

The history of such SqlServerAgent jobs shows, quoting:

The job succeeded. The job was invoked by User domain\domainAccountName. The last step to run was step 1

Executed as user: localHostName\sqlbackup. The job script encountered the following errors. These errors did not stop script: A job step received an error at line in a PowerShellScript. The corresponding line is 'dir "\\RemoteHostName\sharedFolderName\" |? {$_.CreationTime -lt (get-date).AddDays(-7)} | del'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot find path '\\RemoteHostName\sharedFolderName' because it does not exist. ' Process Exit Code 0. The step succeeded.

Note the green check-marks on failed job and job steps

Again, zoomed-in the text output of the result:

Also, when executed this SqlServerAgent job step shows: "processing", then "Success"

though it is persistingly failing to delete required files.

The question(s):

How to make the job and step, containing errors,  to show "Failed" instead of of "Succeeded"?
Why does the purging of older files using Windows PowerShell commands succeedes from command line prompt and fails from within SqlServerAgent job&step?

2a. How succeed with removing of older remote files on schedule from SQL Server 2012?

PS
I am using SQL Server Agent Job proxy according to this answer using local Windows user accounts on remote/target and local/source hosts with the same/equal names sqlbackup and the passwords

The same story with scheduling in Windows Task Scheduler - the job and steps are "succeeding" without deleting anything though copying to remote location (out of description here) by previous steps are successful. And I am interested to centralize all in SQL Server SqlServerAgent job (but not outside)


texts: postgresql - paginate correctly ordering by non-distinct values?

texts:
How can I properly page by ordering on a column that could possibly have repeated values? I have a table called posts, which has a column that holds the number of likes of a certain post, called num_likes, and I want to order by num_likes DESC. But, the image below shows a problem that I run into - the new row inserted between the two pages causes repeated data to be fetched.

This link here explains the problem, and gives the solution of keyset pagination, but from what I've seen, that only works if the column that the rows are being sorted on are distinct / unique. How would I do this if that is not the case?


texts: SQL Server upgrade re-initializes subscriptions

texts:
I recently, in a test environment, upgraded my SQL Server Distributor from 2012 to 2017. The upgrade went off without a hitch but I noticed that after the upgrade was completed all of my subscriptions from the publisher using that distributor were re-initialized.

Is this expected behavior?
Is there anyway to prevent this from happening if this is the expected behavior?


texts: How to update/insert/inject nodes into XML

texts:
I have an application which stores optional Site information in an XML field.

Sample table:

CREATE TABLE [dbo].[Sites](
    [SiteID] [int] IDENTITY(1,1) NOT NULL,
    [SiteName] [nvarchar](80) NULL,
    [SiteInfo] [xml] NULL );

INSERT INTO [dbo].[Sites] ([SiteName]) VALUES  ('TestSite1') ;

If no attributes apply the XML field is NULL. If any of those attributes do apply then the application inserts an XML document and relevant nodes.

A valid example looks like this:

<SiteInfo>
  <BoreID>ABC123</BoreID>
  <ConsentCompliance>true</ConsentCompliance>
</SiteInfo>

I need to migrate some data from another system, and so I'm trying to insert data into this field/XML. So far my attempts have resulted in singleton nodes which the app doesn't like.

Using some examples i found in the web I've tried a the following:

update [dbo].[Sites]
SET [SiteInfo].modify('insert <Anothernode>ABC123</Anothernode> into (/SiteInfo)')
where Siteid = 1

Which throws the following error:

  Msg 2226, Level 16, State 1, Line 1 XQuery
  [dbo.Sites.SiteInfo.modify()]: The target of 'insert' must be a single
  node, found 'element(SiteInfo,xdt:untyped) *'

Questions:

If the field is NULL, Do I need to update the field first to insert a base for the xml i.e  before I can insert to it?
Whats the name of an XML node that doesn't use an opening and closing pair. For example: <BoreID>ABC123</BoreID> vs a single side <BoreID=ABC123/>
Are there other/better methods of inserting nodes into XML using SQL?

Cheers

Pete

Answers:

Yes  update [dbo].[Sites] SET [SiteInfo] = '<SiteInfo />'
Shorthand vs Long
No


texts:
we are setting up Kerberos authentication in Sybase (linux). DB version is 15.7. We are using Active directory for kerberos. We have done the configuration settings on the DB side and kerberos is successfully initialized on the DB server, we can see it from the log "00:0000:00000:00000:2013/09/19 15:47:30.96 server  Successfully initialized the security mechanism 'csfkrb5'. ASE will support use of this security mechanism."
We have generated the keytab and placed it in \etc\krb5.keytab and successfully generated the ticket as well.

But when we try to connect using isql -Sservername -V we are getting the below error.
CT-LIBRARY error:
        ct_con_props(SET): security service layer: internal security control layer error: Unable to open security driver.
Msg 4002, Level 14, State 1:
Server 'Servername':
Login failed.
CT-LIBRARY error:
        ct_connect(): protocol specific layer: external error: The attempt to connect to the server failed.

Please someone help us on this to resolve the issue.

Thanks in advance.

Sybase DBA


texts: Forcing MS SQL Server to rollback on error

texts:
I am having some problem to understand how transactions work on SQL Server, even after I thought I had all answers from the documentation.

To put it simply, I have a transaction at the beginning of a loong script (which gravely alters the schema), and if any statement fails it should result in a rollback.
On PostgreSQL this works without no problem.

An example to illustrate, on PostgreSQL:

BEGIN TRANSACTION;
DROP TABLE t1;  -- This results in a rollback, because t1 doesn't exist
CREATE TABLE t1 (c1 int); -- This and following statements are never run
COMMIT TRANSACTION;

Example on SQL Server:

SET IMPLICIT_TRANSACTIONS OFF --
SET XACT_ABORT ON -- Supposedly stop everything on error
GO
BEGIN TRANSACTION
GO
DROP TABLE t1 -- This results in an error
GO
CREATE TABLE t1 (c1 int) -- This is execute anyway?
GO
COMMIT TRANSACTION
GO

Even though the script results in an error, it never aborts to rollback. And the table "[dbo].[t1]" exist in the schema.

Someone suggested wrapping the statements in a TRY/CATCH block, but this does not work due to some schema alterations requiring be split up in batches. (E.g. renaming/adding columns, and later inserting data).

Many answers here does state that SET XACT_ABORT ON should indeed result in the script to be aborted, but it does not.

What am I missing here?


texts: SQL Server Data Warehouse stored procedure location

texts:
We are building a Kimball data warehouse. We have a Staging Database, and Final Datawarehouse.

We have a stored procedure which creates the table CustomerTransaction.
Where is the best location to place the stored procedure? In the Staging Database or the Datawarehouse? Or what is the positives/negatives of each location? I just started creating data warehouses and trying to learn.

create procedure dbo.FactCustomerTransactionImport  -- should this be in StagingDB or datawarehouse DB?
as

insert into DWFinal.dbo.FactCustomerTransaction
(
     CustomerId,
     Quantity,
     Price,
     Amount
)
select
     CustomerId,
     Quantity,
     Price,
     Quantity * Price as Amount
from StagingDB.dbo.CustomerTransaction


texts: Does a SQL Server bit column really use a whole byte worth of space?

texts:
I was poking around SSMS and noticed the "size" of my INT columns were 4 bytes (expected) but I was a bit shocked to see my BIT columns were a whole byte.

Did I misunderstand what I was looking at?


texts: Not searching GIN index when querying ANY(column)

texts:
In a Postgres 9.4.11 database, I have a large table of books, which has a one-to-many relationship with authors. Theoretically to improve performance, I've cached the author names in the ebook record for quick searching. Properties of the column authors_cache:

Sample rows:

I've created a GIN index on this column:

CREATE  INDEX  "index_ebooks_on_authors_cache" ON "ebooks" USING gin ("authors_cache")

It's not using the index when searching authors_cache and takes a unacceptable amount of time:

EXPLAIN ANALYZE
SELECT  * FROM "ebooks"
WHERE ('Charles Bukowski' = ANY (authors_cache))
LIMIT 60 OFFSET 0;

Result:

Limit  (cost=0.00..172334.67 rows=30 width=124) (actual time=71.962..10067.070 rows=59 loops=1)
  ->  Seq Scan on ebooks  (cost=0.00..172334.67 rows=30 width=124) (actual time=71.960..10067.015 rows=59 loops=1)
        Filter: ('Charles Bukowski'::text = ANY ((authors_cache)::text[]))
        Rows Removed by Filter: 894504
Planning time: 0.188 ms
Execution time: 10067.112 ms

10 seconds is not an acceptable amount of time. I'm open to design changes, as I'm not "married" to this column, yet.


texts: Create a Constraint such that only one of two fields must be filled

texts: I have a table in SQL server where the users need to enter data in either of two columns. That is, One of the two must have data inputted but at the same time i don't want to allow users to input in both columns. It's either or but one is a must.


texts:
My web app server is running on Ubuntu 12.04 server 32bits.
The mysql db is going to be installed on a different Ubuntu 12.04 server. Should I use 32 bits for the server on which mysql is or 64 bits?

If the db server is 64bits, can it cause issues knowing that the webapp is running on a 32 bits or it does not matter at all?

Many thanks,
Roman


texts: Azure SQL Hyperscale, Very Large Tables, and lack of explicit partitioning

texts:
Hyperscale scales to 100TB when it comes to storage.  And I understand the page server concept - each server handling 128GB of data, with its own local cache.
What I do not understand though is how this can scale from a performance perspective, without explicit user sharding/partioning.
If I have a single table, with billions of rows and terabytes of data, my indexes will continue to grow proportionately.  Every insert will have to update those as well. Surely any query that does a table scan will crumble eventually, and even those using the indexes will get slower and slower?
Other DB solutions that scale tables infinitely, enforce a partition key, and that key needs to be in any query predicate.  How can Hyperscale get away without that requirement?  Have I missed something?
Thanks!


texts: Does SELECT remove dead rows like VACUUM does?

texts:
I was fiddling with VACUUM and noticed some unexpected behavior where SELECTing rows from a table seems to reduce the work VACUUM has to do afterwards.

Test Data

Note: autovacuum is disabled

CREATE TABLE numbers (num bigint);
ALTER TABLE numbers SET (
  autovacuum_enabled = 'f',
  toast.autovacuum_enabled = 'f'
);

INSERT INTO numbers SELECT generate_series(1, 5000);

Trial 1

Now we run an update on all the rows,

UPDATE numbers SET num = 0;

And when we run VACUUM (VERBOSE) numbers; we get,

INFO:  vacuuming "public.numbers"
INFO:  "numbers": removed 5000 row versions in 23 pages
INFO:  "numbers": found 5000 removable, 5000 nonremovable row versions in 45 out of 45 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 6585
There were 0 unused item pointers.

Trial 2

Now we issue another UPDATE, but this time we add a SELECT afterward,

UPDATE numbers SET num = 1;
SELECT * FROM numbers;

And when we run VACUUM (VERBOSE) numbers; we get,

INFO:  vacuuming "public.numbers"
INFO:  "numbers": removed 56 row versions in 22 pages
INFO:  "numbers": found 56 removable, 5000 nonremovable row versions in 45 out of 45 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 6586
There were 56 unused item pointers.

What exactly is happening here? Why does the second version I run, after the SELECT remove dead tuples from the pages it visits, quite like VACUUM does?

I am running Postgres 11.3 on macOS 10.14.5.


texts: Should images be separated from posts in MongoDb?

texts:
I am working on a project with Meteor.js and I am using CollectionFS with GridFS to easily store images in MongoDb. Now I am not the best with databases and database design, but I heard that in nosql database you should try to denormalize data. So to my real question, what is preferred?

One:

An Images collection and then a Posts Collection with a corresponding imageId?

Only a Posts Collection(FS.Collection) and then save the image along with title, etc?

I feel like this is stupid, but somehow I think they should be separated.


texts: Same query way faster with distinct than without distinct

texts:
I have a query that runs very fast with distinct than without distinct. when running both queries, the one with distinct takes 13% execution time, the one without distinct takes 87% execution time.

These are TSQL query in SQL Server 2014 on a database with 110 compatibility mode.

Strange thing is that the query with distinct is using seeks while the query without distinct is using 1 scan. Both query are identical and have the same where clause.

Can you help me understand why the query with the distinct is faster and why the query without distinct is not using a seek ?

Query plans : www.brentozar.com/pastetheplan/?id=ryr18jJsb

Queries:

select
    SMS_R_SYSTEM.ItemKey
   ,SMS_R_SYSTEM.Name0,SMS_R_SYSTEM.SMS_Unique_Identifier0
   ,SMS_R_SYSTEM.Resource_Domain_OR_Workgr0
   ,SMS_R_SYSTEM.Client0
from vSMS_R_System as SMS_R_SYSTEM
   inner join Add_Remove_Programs_DATA
      on Add_Remove_Programs_DATA.MachineID = SMS_R_System.ItemKey
   inner join Add_Remove_Programs_64_DATA
      on Add_Remove_Programs_64_DATA.MachineID = SMS_R_System.ItemKey
where Add_Remove_Programs_DATA.DisplayName00 = 'aze'
   or Add_Remove_Programs_64_DATA.DisplayName00 = 'aze'
;

select distinct
    SMS_R_SYSTEM.ItemKey
   ,SMS_R_SYSTEM.Name0,SMS_R_SYSTEM.SMS_Unique_Identifier0
   ,SMS_R_SYSTEM.Resource_Domain_OR_Workgr0
   ,SMS_R_SYSTEM.Client0
from vSMS_R_System as SMS_R_SYSTEM
   inner join Add_Remove_Programs_DATA
      on Add_Remove_Programs_DATA.MachineID = SMS_R_System.ItemKey
   inner join Add_Remove_Programs_64_DATA
      on Add_Remove_Programs_64_DATA.MachineID = SMS_R_System.ItemKey
where Add_Remove_Programs_DATA.DisplayName00 = 'aze'
   or Add_Remove_Programs_64_DATA.DisplayName00 = 'aze'
;


texts:
How mysqldump is processing backup?
From which all files is it reads data for creating dump?
Is the process differs if storage engines are different?


texts: SQL Performance issue after migration from SQL Server 2005 to SQL Server 2012

texts:
Yesterday we migrated a SQL Server 2005 database to SQL Server 2012. We made a full backup and restored it on the new server.

Problem now is that the database on SQL Server 2012 is much slower than it was on the Windows 2003 server with SQL Server 2005. What's even more worrisome is that the exact same maintenance plan (that took 37 minutes to run on SQL Server 2005) is now taking more than 2 hours and uses 16GB of memory and the tempdb is filling up the empty space on the C: drive. What's going on?

The server specification are the same for both:

X5650 @ 2.67GHz (4procs)
16 GB RAM

The maintenance plan consists of the following steps:

Step 1: Check DB integrity
Step 2: Reorganize Index
Step 3: Rebuild Index
Step 4: Shrink Database


texts:
In general, I always use Ints.  I know that in theory this is not the best practice, though, since you should use the smallest data type that will be guaranteed to store the data.

For example, it's better to use tinyint when you know that the only data you will store is a 1, 0 or null (with a very small chance of expanding that to a 2 or 3 later).

However, the only reason I know for doing this is for storage purposes--using 1 byte on a row instead of 4 bytes.

What are the impacts of using tinyint (or smallint or even bigint) over just int, other than saving space on your hard drive?


texts: Fast reads without paying the price on writes

texts:
We have a large append-only table containing financial transactions. On average 1000 transactions are inserted per minute. Since there are now more and more usecases where we actually want to read, search and aggregate these transactions, fast reads would be very nice.

We want to guarantee very fast writes, and adding indexes to cover some of the reads would slow down writes.

The good news is that we can afford stale data. I was considering to create a copy of the data to a read-optimized table (with indexes) every n minutes, which also allows for a periodical bulk-copy (thus limiting the number of ops?)

I'm looking for an opinion on whether this is a valid strategy. If you consider this a decent strategy, do you have implementation options pointers? If not, what are the alternatives?


texts: Principle of least privilege for sql server agent jobs

texts:
I've just read this article which advocates using the sa account for ownership of sql server agent jobs.

http://sqlmag.com/blog/sql-server-tip-assign-ownership-jobs-sysadmin-account

He has a couple of caveats but doesn't provide much detail on these.

He does say the following:

  ...in situations where security is a concern, and where least privilege
  makes sense (such as with jobs other than backups, consistency checks,
  index defrags, etc.) then you'll want to look at AND take advantage of
  SQL Server proxies.

My question is - why does least privilege not make sense with backups, consistency checks, index defrags, etc? What is the rule of thumb for when least privilege can be ignored?


texts:
Say I have a table prices. Users can see the prices of this table (I'm simplifying it). Users also can send prices to the server, so the server is going to collect these data in another table (temp_prices).

When the server detects that there are 50 prices or more, then the server calculates a median of those temporal prices and update the table prices with the calculated value (again, very simplified).

The first idea that came to my mind was have two tables with the same schema but different names (prices and temp_prices).

I don't know if this is a good/common practice. I am missing something?
Also, what mechanism should I use to trigger the server to inspect the table temp_prices? I think about cron but again, I don't know if there is something I'm missing (another tool or common practice for doing this kind of stuff).

Thanks.


texts:
I try to have a view created on the fly.
In my case it's a pivot view on a EAV-style table.

Assuming a table which contains two attributes I use a statement like this, executed inside of a trigger function (written in PL/Python):

CREATE OR REPLACE VIEW pivot_eav AS
  SELECT ct.id,
         ct.attr1,
         ct.attr2
  FROM crosstab(
    '
      SELECT entity, attribute, value
      FROM eav_table
      ORDER BY 1
    '::text,
    '
      VALUES (''attr1''), (''attr2'')
    '::text
    )
  AS ct(id    text,
        attr1 text,
        attr2 text
    );

But now given I don't know the names of the two attributes at creation time. I will have to insert them at runtime.
I can build the statement on the fly and execute it. Actually that's what I do at the moment:

"""
CREATE OR REPLACE VIEW pivot_eav AS
  SELECT ct.id,
         ct.attr1,
         ct.attr2
  FROM crosstab(
    '
      SELECT entity, attribute, value
      FROM eav_table
      ORDER BY 1
    '::text,
    '
      VALUES (''{0}''), (''{1}'')
    '::text
    )
  AS ct(id text,
        {0} text,
        {1} text
    );
""".format("attr1", "attr2")

But I'm aware that this is highly insecure, because input is executed.

I tried to use a prepared statement like this:

CREATE OR REPLACE VIEW pivot_eav AS
  SELECT ct.id,
         ct.attr1,
         ct.attr2
  FROM crosstab(
    '
      SELECT entity, attribute, value
      FROM eav_table
      ORDER BY 1
    '::text,
    '
      VALUES (''$1''), (''$2'')
    '::text
    )
  AS ct(id text,
        $1 text,
        $2 text
    );

which doesn't seem to be legal.

I was able to circumvent the first to placeholder by using dollar sign escaping, but still the second occurrences seem not to e legal in those positions!?

I also wasn't able to use dollar escaping in that place, because the names are use as literal there.

My question:
Is it correct, that placeholder in prepared statements only have special uses? (And can be used in arbitrary positions like string interpolation in programming languages.)
If so, is there another, secure, way to put that statement together?

Robin


texts: Caching intermediary CTEs for multiple uses

texts:
I'm working on this huge query that I chose to use CTEs. Due to the complexity of this query, I ended up having to reuse intermediary query results more than once in subsequent queries. The problem I'm facing can be summarized by the following toy code:

WITH cte1 AS (
    SELECT id, name
    FROM Manager)
, cte2 AS (
    SELECT id, name
    FROM Employee
    LEFT OUTER JOIN cte1 ON Employee.id = cte1.id)
SELECT *
FROM cte1

I have notice an unusual amount of indexes seeks in a fairly small table that was being queried in one the first queries which, according to the execution plan, were taking about 30% of the whole query. This led to me believe the base table was being queried multiple times as it was being referenced more than once in subsequent queries.

I was under the impression that intermediary results in a CTE were cached until the final statement. Reading Microsofts docs on CTEs, I couldn't find any reason to think otherwise.

Are intermediary CTEs cached for muliple uses within an WITH statement? If not, is there a way to cache it without rewriting the query?

Thanks!


texts:
I have an instance running Postgres v PostgreSQL 10.3 (Debian 10.3-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit. We have several indexes, GIN indexes included.

Occasionally queries made on one of the table get locked, this is what i can see from pg_stat_activity:

select wait_event, wait_event_type from pg_stat_activity where
state='active';
  wait_event  | wait_event_type
--------------+-----------------
 DataFileRead | IO
 buffer_io    | LWLock
 buffer_io    | LWLock
 buffer_io    | LWLock
 buffer_io    | LWLock
 buffer_io    | LWLock
 buffer_io    | LWLock
 buffer_io    | LWLock
 buffer_io    | LWLock
 buffer_io    | LWLock
 buffer_io    | LWLock
 buffer_io    | LWLock
 buffer_io    | LWLock
 buffer_io    | LWLock
 buffer_io    | LWLock
 buffer_io    | LWLock
 buffer_io    | LWLock
 buffer_io    | LWLock
 buffer_io    | LWLock
 DataFileRead | IO
 buffer_io    | LWLock
 buffer_io    | LWLock
 buffer_io    | LWLock
 buffer_io    | LWLock
              |
(25 rows)

All the locked queries are not always on the same table, but on a random one, when this happens the other tables are still accessible.

I'm using Postgres over Kubernetes, with a network mounted storage.

I tried to run VACUUM ANALYZE <table>; as well, but it got stuck too.

Anybody ever run into a similar issue? how did you get through it?


texts: How to change the SQL Server Backup file (.bak) file default icon?

texts:
As I would like to say that by mistake the SQL Server 2012 backup file (.bak) open with wordpad. Due to this reason all backup files of SQL Server (.bak) file change with wordpad icon in entire the Windows Server 2012 R2 Standard 64-bit O.S.

Now the SQL Server (.bak) file icon looks like as attached below:

To Verify the Backup File through Restore Headeronly TSQL Command

As per MSDN BOL documentation RESTORE Statements - HEADERONLY (Transact-SQL). I have checked the Restore Headeronly from Disk  from verification of my (.bak) file. The

I have checked and verify through the Restore Headeronly TSQL command. The result set returned by RESTORE HEADERONLY includes a row for each of these other backup sets.

I am also attaching that screen shot for persual.

It seems to odd the icon format of SQL Server (.bak) file. The (.bak) file icon format through out the Server has been changed with (wordpad) icon.

  My Question is that: How to revert back the (.bak) file with
  their default icon format? Is it possible or not?


texts: How to generate create table script without primary and foreign key in Oracle

texts:
I have lots of schemas and tables. I want to generate create script of all of my tables. I am using below statement and it is working pretty well.

SELECT DBMS_METADATA.GET_DDL('TABLE','table_name','schema') FROM DUAL

But because this statement also generates all primary and foreign key scripts that belong to table when I try to insert my tables to another location I am getting "ORA-00942: table or view does not exist" error because I didn't create key's reference tables yet.

You can say that, previousl

So, is there any way to not include primary and foreign keys in create table scripts


texts:
So we have a couple of SQL Server instances involved on mirroring without witness and operating mode is asynchronous in all cases.

After we set put mirroring in place, we started facing connections issue on our applications like this one:

  org.apache.torque.TorqueException: org.apache.commons.dbcp.SQLNestedException: Could not retrieve connection info from pool
  Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user XXXXXX.

After some investigation we saw in the alert log of the mirrored instance errors like this:

  Login failed for user XXXXXX. Reason: An error occurred while evaluating the password.

Connection string used on the app side:

torque.dsfactory.service.connection.url = jdbc:sqlserver://SERVER_IP:INSTANCE_PORT;databaseName=DB_NAME;applicationName=APP_NAME torque.dsfactory.service.connection.user = XXXXXX

So, this makes me think that somehow the application is trying to connect to the mirrored instance and since all logins are disabled there the connection is rejected.

Does anyone had this type of issues in the past? I do not see any problem on the mirroring configuration.


texts:
Where can I find resources on how to better move to a 24x7 operation?  How do the big companies with big databases accomplish this? Our nightly jobs such as

purge old data
reindex
update statistics

all seem to cause critical impact to our system (i.e. online users and real time data feeds).  I've looked on Amazon for any book related to this subject, and so far haven't found anything.


texts: Optimizing a query so MySQL will use the indexes for the joins


texts: Transfer microsecond timestamp into table using COPY

texts:
I need to transfer a microsecond timestamp into a table by using the COPY command.

My approach using a normal INSERT, where X is a timestamp (since 1.1.1970) in microseconds, looked like this:

TO_TIMESTAMP( X ::double precision / 100000)

So, an X of 1484579365136360 would translate to  1/16/2017, 4:09:25.136360 PM.

How could I do the same with the COPY command?

I expect I need to convert the timestamp before issuing the COPY command, but in what format?


texts: Why can SELECT see updated row before commit?

texts:
If I run this without a commit statement...

begin transaction

update dateranges set DateRangeTypeId = 1 where DateRangeId = 154

In another tab of Management Studio, I can select the range with Id 154 and it shows the old value for the type column.

Why doesn't the SELECT block on the updated row?


texts:
Is having a field in primary key constraint optimizes sorting of the table ?

Here's the schema of the table:

CREATE TABLE Persons (
Field1 long NOT NULL,
Field2 long NOT NULL,
Field3 long NOT NULL,
Field4  long NOT NULL,
Filed5 long NOT NULL,
CONSTRAINT PK_Person PRIMARY KEY (Field1 Asc,Field2 Asc,Field3 Asc,Field4 Asc));

The sorting query: Select * from Persons order by Field4

The field4 in primary key constraint doesn't provide any uniqueness with respect to values.

The only reason it's added is to optimize the sorting on field4, since sorting on a column that does have an clustered index helps in optimization.

In short, Is having the field4 in primary key constraint helping with the optimization of the above sorting query, even though the primary key constraint is created using ascending order of field1 then field2 then field3 and the field4 ?


texts: How to fetch the transaction activities/logs for a specific table?

texts: I can see the transaction logs with fn_dblog, but I need to do this for specific table. How can I do that?


texts: How to output string values in single quotes?

texts:
I'm trying to convert values stored in a json into its string representation.

The #>> operator works for this purpose for numbers, e.g.:

SELECT '{"a":[1,2,3],"b":["4","5","6"]}'::json#>>'{a,2}';

gives

 ?column?
------
 3
(1 row)

But for string values, the text representation (6 in the example below) does not have single quotes around it, so that one cannot tell that the stored value was a string:

SELECT '{"a":[1,2,3],"b":["4","5","6"]}'::json#>>'{b,2}';
 ?column?
----------
 6
(1 row)

How to use a function or expression to let PostgreSQL output '6' instead of 6 in the second example above (and output 3 in the first example)?

(This is with PostgreSQL 12)

-- UPDATE --

@McNets

To clarify, I am interested in getting the string representation in quotes, so that after decomposing a jsonb into its key value pairs, one can use them with inverse functions like jsonb_build_object (so to speak) to reconstruct the original jsonb.

jsonb_each_text and the #>> apparently aren't good enough because they drop the single quotes.


texts: Current Active Session on a Oracle Table

texts:
I'm running a query which updates an Oracle table through Datastage. The query is taking a long time to execute so I want to examine the query/session on that Oracle table in the database and determine how the query is being executed.

Could someone please advise how I can examine that active query/session in DB?

I'm using DB visualizer tool.


texts: append 0 returned by DATENAME function Sql Server management studio

texts:
I want to append 0 to my minute(mi) result returned by DATENAME function of sql server management studio.

select datename(mi,GETDATE())

If the current time where 12:06:33.12(hh:mi:ss) then '6' would be
returned by the above query and I want 0 to be prefixed with the
result i.e '06' only when my minute is less than 10.
This can be easily done by branching and checking the result obtained
by above query and seeing if it is less than 10 i.e using a CASE
statement.Is there a much cleaner way to do the job without
branching?


texts: INSERT rows in two tables preserving connection to a third table

texts:
As part of a recent database development project, I'm stuck with a query for which I seem to find no elegant solution (i.e. without looping).

Relevant bits of the schema:

CREATE TABLE sites (
  site_id SERIAL PRIMARY KEY,
  ...
);

CREATE TABLE properties (
  property_id SERIAL PRIMARY KEY,
  property_def_id INT NOT NULL,
  FOREIGN KEY (property_def_id) REFERENCES property_def(property_def_id)
);

CREATE TABLE site_properties (
  property_id INT NOT NULL,
  site_id INT NOT NULL,
  PRIMARY KEY (property_id, site_id),
  FOREIGN KEY (property_id) REFERENCES properties(property_id),
  FOREIGN KEY (site_id) REFERENCES sites(site_id)
);

The task:
Insert one new, distinct property in table properties for each row in table sites and also insert a row in site_properties to store  the relation between both.

Desired behaviour:

for each site_id (as my_site_id) in sites:
* INSERT INTO properties (property_def_id) VALUES (1) RETURNING property_id INTO my_prop_id
* INSERT INTO site_properties (property_id, site_id) VALUES (my_prop_id, my_site_id)

At the end, I would have in the site_properties table each "new" property having being assigned only to one site (i.e. no cross-joins)

I feel it should be possible to do this in one query, but I couldn't figure out how for the love of me. I have tried CTEs but I ended up being stuck with a list of property_id's, a list of sites and no way to join between the two.

What am I missing?


texts: Best way to insert error information from catch block in the table for stored procedure?

texts:
I have stored procedure that process single row transaction. I would like to catch any errors and insert in Error table. Then use nightly process to forward the information from error table to developers. I'm wondering what is the best practice for this situation? Is there any good example on how to handle this? Here is my code and idea on what I have as of now:

CREATE PROCEDURE [dbo].[InsertBuilding]
    @Status BIT = 0,
    @Number CHAR(4) = NULL,
    @Name VARCHAR(50) = NULL,
    @Contact VARCHAR(50) = NULL,
    @Address1 VARCHAR(50) = NULL,
    @Address2 VARCHAR(50) = NULL,
    @City VARCHAR(50) = NULL,
    @State CHAR(2) = NULL,
    @Zip CHAR(5) = NULL,
    @Phone CHAR(10) = NULL,
    @ActionID UNIQUEIDENTIFIER = NULL
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON
    BEGIN
        BEGIN TRY
            INSERT INTO Buildings (
                Status, Number, Name,
                Contact, Address1, Address2,
                City, State, Zip, Phone,
                ActionDt, ActionID
            )
            OUTPUT INSERTED.RecID
            VALUES (
                @Status,@Number,@Name,
                @Contact,@Address1,@Address2,
                @City,@State,@Zip,@PhonePri,
                CURRENT_TIMESTAMP,@ActionID
            )
        END TRY
        BEGIN CATCH
            INSERT INTO dbo.ERROR(
                ErrorNumber,
                ErrorSeverity,
                ErrorState,
                ErrorProcedure,
                ErrorLine,
                ErrorMessage,
                ErrorDateTime
            )VALUES(
                ERROR_NUMBER(),
                ERROR_SEVERITY(),
                ERROR_STATE(),
                ERROR_PROCEDURE(),
                ERROR_LINE(),
                ERROR_MESSAGE(),
                CURRENT_TIMESTAMP()
            )
        END CATCH
    END;

If anyone have any suggestions or example how this can be improved or good way to deliver these errors to developers please let know. Thank you.


texts: Adding a database with Service Broker to an existing Availability Group on a new replica

texts:
Server 1 - SQL Server 2016 (Primary in existing AG)
Server 2 - SQL Server 2016 (Secondary in existing AG)
Server 3 - SQL Server 2016 (Offsite Secondary in existing AG)
Server 4 - SQL Server 2017 (New server, destined to become new primary)

An ~800GB database is happily running on the 2016 AG. It has Service Broker enabled and is running fine.

The plan is to restore the database onto Server 4, add it to the AG, then manually failover so Server 4 is the primary in order to upgrade Servers 1, 2 & 3 to SQL 2017.

Server 4 has a different drive configuration to the other Servers so in order to add the database to the AG, I am using dba-tools Restore-DbaDatabase command. The database must be left in NORECOVERY mode in order to finally add it to the AG.

The problem here is that Service Broker must be enabled via ENABLE_BROKER on the DB before it is added to the AG whereas this is not possible because of the NORECOVERY state of the DB. Also SET TRUSTWORTHY ON must be set.

Is there a way to achieve this without tearing down the whole AG?

I'm really trying to avoid this because it takes a long time to copy and restore backups onto the offsite server (Server 3) when initially joining the AG.

This guide has proved useful for the rest of the process.


texts: Extract data along a path (lat, long, altitude and time)

texts:
I will be deploying PostGIS in the next few day. I've never done this before, nor have I ever worked with and geo-spacial data in the past.

I will have two datasets: weather data (A) and coordinate data (B).

(A) will have records containing a lat, long, altitude, time and a measurement value.
(B) will have records containing a lat, long, altitude and time.

I would like to pull out a bunch of records from (B), which together would represent a trajectory through space and time.

I would then like to, for each record extracted from (B), query (A) for the measurement value which corresponds to the lat, long, altitude and time from (B)... give or take some distance (eg, within 10 seconds or within 1km radius from the record from (B)).

An example would be I have a GPS device taped around my head and I am flying through the clouds. As I fly, I record my lat, long, altitude and time of measurement. When I get home, I want to look at the weather data that I have available and show what the temperature was at lat_x, long_x, altitude_x and time_x. I want to do this for each measurement that I made while flying so that I can work out what temperatures I experienced during my flight. However, since I know my measurements may not be 100% accurate, I'd like to give a bit of tolerance to the time of my measurement, as well as to the location (imagine I have a donut with radius r around my center).

How can I achieve this? What functions should I look to use?

Happy to clarify further if needed.

UPDATE

As requested, some example data & expected output.

altitude is in feet.
temp in deg c

Weather data (A):

CREATE TEMP TABLE weather AS
SELECT lat, lon, alt, time::timestamp, temp
FROM ( VALUES
  ( 23, 7  , 30000, '2017-01-01 00:01:00', -4  ),
  ( 35, 13 , 33000, '2017-01-01 00:01:30', -9  ),
  ( 45, 19 , 36000, '2017-01-01 00:02:00', -11 ),
  ( 53, 30 , 40000, '2017-01-01 00:02:30', -15 )
) AS t(lat, lon, alt, time, temp);

Flight path (B):

CREATE TEMP TABLE flightpath AS
SELECT id, lat, lon, alt, time::timestamp
FROM ( VALUES
  ( 1, 7 , 9 , 29000, '2017-01-01 00:01:05' ),
  ( 1, 35, 14, 34000, '2017-01-01 00:01:31' ),
  ( 1, 44, 19, 37000, '2017-01-01 00:01:59' ),
  ( 1, 55, 31, 40100, '2017-01-01 00:02:28' ),
  ( 2, 20, 9 , 60000, '2017-01-01 00:14:05' )
) AS t(id, lat, lon, alt, time);

In the above example, I would expect to extract all records from (B) where id = 1. I'd then want to go over each record in (A) and extract records within some range according to lat, lon, altitude & time. So, for example, I would like a tolerance of +-3 for lat, lon, altitude and time... I would then expect to see a new table:

id | lat | lon | altitude (feet) | time                | temperature (c)
------------------------------------------------------------------------
1  | 7   | 9   | 29000           | 2017-01-01 00:01:05 | no measurement
1  | 35  | 14  | 34000           | 2017-01-01 00:01:31 | -9
1  | 44  | 19  | 37000           | 2017-01-01 00:01:59 | -11
1  | 55  | 31  | 40100           | 2017-01-01 00:02:28 | -15


texts:
I am using MySQL 8.0.17 and I have a table with just 7 million rows defined like
CREATE TABLE `measurement` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `field1` int(11) DEFAULT NULL,
  `field2` varchar(200) NOT NULL,
  `field3` datetime(6) NOT NULL,
  `field4` double NOT NULL,
  `field5` double NOT NULL,
  `field6` double NOT NULL,
  `field7` double NOT NULL,
  `field8` double NOT NULL,
  `field9` double NOT NULL,
  `field10` double NOT NULL,
  `field11` double NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index2` (`field2`),
  KEY `index3` (`field3`)
) ENGINE=InnoDB AUTO_INCREMENT=7435062 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

and queries on it are extremely slow. For example,
select count(*) from measurement;

takes around 10 minutes. I have another table in the same database with roughly the same number of rows and columns where the same query takes 35 ms. When I do an explain on the above query I get
id|select_type|table             |partitions|type |possible_keys|key       |key_len|ref|rows   |filtered|Extra      |
--|-----------|------------------|----------|-----|-------------|----------|-------|---|-------|--------|-----------|
 1|SIMPLE     |measurement       |          |index|             |index3    |8      |   |7132937|   100.0|Using index|

Before I added the other two indices this query took 17 minutes. Everything was created using Django 3.1. When I try to analyze the table the result is
Table      |Op     |Msg_type|Msg_text|
-----------|-------|--------|--------|
measurement|analyze|status  |OK      |

Where do I need to look to figure out what my issue is?


texts:
I have installed Oracle 11g on fedora 14. I connected with SQL*Plus as 'SYS' and
created some tables, then created a user named "account" and a role, "payroll"
with the following privileges. I assigned the role to the "account" user but I
cannot access the created tables when I log-in as "account".

Here are my queries:

As SYS

create type pay_t as object(
id int,
name varchar(10))
/

create table pay of pay_t;
insert into pay values(pay_t(10,'p01'));

create user account identified by paccount;
grant create session to account;
grant connect to account;

create role pay_roll;
grant select on pay to pay_roll;
grant pay_roll to account;

As account:

select * from pay;

This gives me an error saying there is no such table or view.


texts:
I was planning to download a trial of SQL Server Developer today from MSDN Subscriber Downloads. I have two questions:

Next to each download it says: "No product key is required".  I assume this means that a 28 day trial is initiated after download?
What is the difference between SQL Server Developer; SQL Server Web Edition and SQL Server Business Intelligence? I assume that SQL Developer can do everything that SQL Server Business Intelligence and SQL Server Web Edition can do?

I followed these steps:

Went to the appropriate MSDN Subscriber Download page
Selected: SQL Server 2014 with SP1 x64
Logged In
Saved the file locally
During the installation select "Evaluation" rather than the license key.

To my surprise there was already a license key populated.  I have an active Bizspark account (though I did not renew in year three). It must be this account that has populated the license key.  Why is Enterprise Edition evaluation installed rather than developer edition?


texts: Is it possible to calculate the SQL Server sql_handle for a given SQL statement?

texts:
The following site mentions the sql_handle is a MD5 hash of the entire batch text
https://techcommunity.microsoft.com/t5/sql-server/2-0-sql-handle-and-plan-handle-explained/ba-p/383204
However, I'm unable to calculate it manually. From dm_exec_query_stats we determine the following statement has the following sql_handle.
SQL: (@P1 bigint)SELECT NID MimeTypeID, Name FROM DDocumentClass WHERE Type = @P1
SQL_HANDLE: 0x02000000CA17931BEF7F24A1787BF580EA365A56408697B30000000000000000000000000000000000000000

The following Ruby script calculates the MD5.
require 'digest'

sql1 = 'SELECT NID MimeTypeID, Name FROM DDocumentClass WHERE Type = @P1'
sql2 = '(@P1 bigint)SELECT NID MimeTypeID, Name FROM DDocumentClass WHERE Type = @P1'

puts Digest::MD5.hexdigest sql1
puts Digest::MD5.hexdigest sql2

Output:
81707e73438befe82957c766dca86799
2652dc1d05e927ac7c5bfc985a3d1483

There must be more to the algorithm.
We can calculate Oracle's SQL_ID, I'm hoping to be able to also calculate SQL Server's SQL_HANDLE.


texts:
I have a view and I need to remove a column in the view. Before doing that modification I need to determine which are all the applications that access the view. I need that information so as to make sure that removing the column would not break any application code that access the view.

Is there any DMV or system stored procedure that contains such information? Or is there any system tables that store such information?


texts: Is there a database tool that bashes data with HUGE excel files?

texts:
Ok this is hard to explain and im not sure if this is the right place to ask it but.

Im 2 weeks in on my new work and I feel like our work can be a lot more easier if we dont rely on just Excel for our tasks.
We deal with a lot of excel files. And these excel files are a lot. Per day we have 2000+ entries. So when we do monthly data, that excel file would be 2000x30 days. Imagine that.

Basically we just do matching with these excel files. All these excel files have different formats but they all have this unique mobile number (i work at a telco company) so we bash data with the excel files we have. Currently, we just use Access to bash data. But its not efficient. Access is REALLY SLOW.

So its like data mining. Lets say we are given this mobile number they want to investigate (we are in the fraud analysis team) so its my job to backtrack and check that phone number's usage. But our company has an inefficient way of handling their data. Like theres various excel files that arent centralized.

Anyway, any suggestions on how to deal with this? Are there alternative tools? etc. Since you guys know about database and i know its a rule that Excel shouldnt be used for database management. I do have some background with mysql and mssql but thats just basic commands.. Im not sure if it can be incorporated with my job.

(also, is there a word for what i do exactly? they call it data mining. We just match a mobile number and look for its corresponding data across different sources)


texts: Replication issue - CREATE SELECT alternative?

texts:
I've an MySQL 5.1 slave for our BI team.

They need to make some CREATE SELECT with big select queries (several million lines).

As CREATE SELECT is a DDL, if the replication attempts to update some rows in same tables than the SELECT statement, replication is blocked until the freeing of the CREATE SELECT.

Do you now a good non-blocking alternative to thoses CREATE SELECT statements?

I thought to an SELECT INTO OUTPUT FILE then LOAD DATA INFILE but they will fill out our disks as BI guys like to do... :)

Max.


texts:
Some of our end-users still like to use the Activity Monitor in SQL Server.
When setting up our SQL-server 2016 servers the activity monitor always ends up in a paused state when the users are not part of the "Administrators" group in Windows (on the server).

When the users are part of the Administrator-group all data gets through (so probably not a Firewall-problem).
We also tried:

Adding the users to the "Performance Monitor Users" and "Distributed COM Users"
In COM: Changing the limits of "My Computer" and the permissions on "Windows Management and Instrumentation".
The users also have the "View server state" permission in SQL Server.

Do you have any other suggestions?

Kind Regards,

Tim


texts: Toad 9.7.2 for Oracle hangs on Windows 7

texts:
I am using Toad 9.7.2 on Windows 7 64 bit.

It is generally working fine but there is one problem. When I click on certain tables in "Schema Browser" and then click on "Data" tab to see records in them then Toad hangs.

This is only happening for some tables and most of them are working fine. If I run SELECT query to retrieve data from those problemetic tables then it also runs fine. Problem only happens when I try to view records by clicking on "Data" tab.

I thought may those tables contain huge number of records but that's not the case. One of the table only contain 25 records but still Toad still hangs and then I have to kill the process.

Any idea why this is happening and how to solve this?

EDIT

Found one clue. This is only happening for those tables which have Parent table.

You know there is another tab called "Referential" which shows both Child Tables i.e. "table is referenced by" and Parent Tables "table references". So if a table has parent table then Toad hangs when I click on "Data" tab for that table.


texts: MongoDB terminates when it runs out of memory

texts:
I have the following configuration:

a host machine that runs three docker containers:

MongoDB
Redis
A program using the previous two containers to store data

Both Redis and MongoDB are used to store huge amounts of data. I know Redis needs to keep all its data in RAM and I am fine with this. Unfortunately, what happens is that mongo starts taking up a lot of RAM and as soon as the host RAM is full (we're talking about 32GB here), either mongo or Redis crashes.

I have read the following previous questions about this:

Limit MongoDB RAM Usage: apparently most RAM is used up by the WiredTiger cache
MongoDB limit memory: here apparently the problem was log data
Limit the RAM memory usage in MongoDB: here they suggest to limit mongo's memory so that it uses a smaller amount of memory for its cache/logs/data
MongoDB using too much memory: here they say it's WiredTiger caching system which tends to use as much RAM as possible to provide faster access. They also state it's completely okay to limit the WiredTiger cache size, since it handles I/O operations pretty efficiently
Is there any option to limit mongodb memory usage?: caching again, they also add MongoDB uses the LRU (Least Recently Used) cache algorithm to determine which "pages" to release, you will find some more information in these two questions
MongoDB index/RAM relationship: quote: MongoDB keeps what it can of the indexes in RAM. They'll be swaped out on an LRU basis. You'll often see documentation that suggests you should keep your "working set" in memory: if the portions of index you're actually accessing fit in memory, you'll be fine.
how to release the caching which is used by MongoDB?: same answer as in 5.

Now what I appear to understand from all these answers is that:

For faster access it would be better for mongo to fit all indices in RAM. However, in my case, I am fine with indices partially residing on disk as I have a quite fast SSD.
RAM is mostly used for caching by mongo.

Considering this, I was expecting mongo to try and use as much RAM space as possible but being able to function also with few RAM space and fetching most things from disk. However, I limited mongo Docker container's memory (to 8GB for instance), by using --memory and --memory-swap, but instead of fetching stuff from disk, mongo just crashed as soon as it ran out of memory.

How can I force mongo to use only the available memory and to fetch from disk everything that does not fit into memory?


texts:
What is the faster statement? A MERGE or Select->Update/Insert depending on if it returns a value?

I'm trying to optimize a database, but I haven't found any speed tests comparing the two.

I found the MERGE command through a StackOverflow answer


texts:
I have this table structure:

id1   id2
 1     2
 1     3
 2     1
 2     4

id1 and id2 are not unique as you can see in the values. I need to retrieve those values that exists in the two columns meaning id1=1,2 id2=2,1. Any help?

Cheers and thanks in advance


texts: Redshift is missing in target database list of AWS SCT Schema Convertion Tool

texts:
I want to convert schema of SQL Server database to Amazon Redshift using AWS SCT tool

But as target database, Redshift is not listed

When I use PostgreSQL compatible with IP address of the cluster, I experience a lot of Telemetry errors in the log

Any help is appretiated much
Thanks


texts: Why are some write requests throttled whereas the average consumed write capacity is below the provisioned write capacity?

texts:
I see the following graphs in one of my DynamoDB table:

Between 2/15 9:30 and 2/15 10:00 some write requests were throttled while the average consumed write capacity was below the provisioned write capacity. How comes?


texts: How do I list all available encoding types available in postgreSQL?

texts:
SELECT datname, pg_encoding_to_char(encoding)
FROM pg_database;

…lists all the databases, each with its encoding type.

However, I am trying to find out what are all the encoding types available in the PostgreSQL server. Can I query for all available encoding types?

Or are the only encoding types ever available listed in Chapter 23.3 Character Set Support?


texts: How does DBCC SHRINKFILE() affect backups?

texts:
I have a pile of SQL 2005 databases that have been around for a very long time and, for varying reasons, experienced transaction log autogrowth. Regular backups now keep the transaction log usage under control, but the files are still large and fragmented.

I'd like to reclaim some of that space and boost performance by reducing the number of VLFs. However, I'd also prefer not to disrupt our scheduled backups. How does DBCC SHRINKFILE() affect the transaction log backup chain? Or does it? Does this change if I add TRUNCATEONLY to the command?

Note that this is a one-time fix. I'm well aware that regularly shrinking logfiles is a Bad Thing - had these databases been configured with rational initial sizes and autogrowth settings and had backups run perfectly every day of their history, this would not be an issue.


texts: Can you trigger an action when a view is created?

texts:
Is it possible to create a trigger that fires when a view is created on a user schema?

I want to create a meta data table which will hold all the views and a listing containing what they are supposed to do.  In this system  (Oracle 9.2.0.8) there is only one user who has the create view privilege so that may make it easier.

The sequence of events should be something like
view is created   trigger fires and inserts the name of the view in the table of meta data table

I could write up some package to create views using dynamic sql but that seems a bit of overkill


texts: SSIS Force all fields to be maximum column length

texts:
I have an existing SSIS package that contains several data types. We are consuming 8 flat files (CSV's) and pushing them to a SQL Server Database. There are several fields that could, possibly, be unreasonable in length. The preferred method would be a global setting to trim a string if it's longer than it's destination allows.

Is there any feature for this, or does it have to be handled on a column by column approach using derived columns for each?


texts: Find the date and time that a data page was modified

texts: Is there a method (documented or undocumented) to determine the last time a data page was changed in a SQL Server 2012 database data file.


texts: Does my ERD account for my business logic

texts:
As part of my new and upcoming project, part of the business logic involves sending each and every one of my customers (assume they number in their 1000s) multiple (possibly customized) emails.

Each of these emails will have a:

Subject
To Field (can take list of emails)
CC Field (can take list of emails)
BCC Field (can take list of emails)
Email Body

The Email body will be HTML, and will have in it placeholders where my code can inject values (e.g. a placeholder for Order No, where the code will then inject the Order No for that customer).

If the Customer is new, he/she will receive an extra email (making the total three), if on the other hand they are an old customer, he/she will only receive two emails.

Certain customers might wish some different information displayed on their emails, so my database design need to account for this. E.g. Customer John Smith might want his emails to also list the 'Item Color', where as Emma Watson is not too fussed about that detail, and does not want that listed in the emails.

If customers have not explicitly mentioned that they want customized emails sent, then a Standard Template email is sent.

I believe this is a Many to Many relationship between the Customers and the Emails, so my ERD is very simple! After examining my business logic above, have I missed anything in the ERD below?

Thank You.

UPDATE: ERD updated AGAIN after Joel Brown's input.


texts: How to split a multivalued column, do a lookup and update another column?

texts:
This is a part of my previous question.

How can I achieve the following for all the records in my table using a single query?

SET @TeamMembers = TeamMembers from tblProjects where ID = '125'

DECLARE @MemberEmails TABLE(col VARCHAR(255))
DECLARE @MemberNames TABLE(col VARCHAR(255))
DECLARE @Names VARCHAR(255)

INSERT INTO @MemberEmails
SELECT * FROM [dbo].[fnSplitString] (@TeamMembers, ',')

INSERT INTO @MemberNames
SELECT u.NAME FROM @MemberEmails e LEFT OUTER JOIN Users u ON e.col = u.EMAILID

SELECT @Names =
   STUFF((SELECT '; ' + RTRIM(CONVERT(CHAR(50),col))
   FROM   @MemberNames b
   FOR XML PATH('')),1,1,'')

UPDATE tblProjects SET Team = @Names  WHERE ID = '125'


texts: Amazon RDS remove read-only and allow defining table structures

texts:
I have set up an Amazon RDS Mysql instance and it is successful.
I am trying to create some tables but I am getting this error:

#1290 - The MySQL server is running with the --read-only option so it cannot execute this statement

I have tried to set global to read-only = 0 but my user does not have privileges.
I have tried googling for a solution to no avail. (I am pretty new to RDS)

Update
I do not have a create read replica option on my actions


texts: Best way to drop / recreate a table that has dependent views?

texts:
Problem

PostgreSQL does not have logical column reordering, at least not in 9.2

If columns need to be added in the middle of a large table and order is important, the best way is to drop and recreate the table.  However, if there are external dependencies, such as views, this is not possible without DROP CASCADE.

If there are lots of views built on this table, it seems like a lot of work.  Is there any way to accomplish this without having to drop/recreate the views?

Hopeful

My hope is that you can do something like DROP CASCADE DEFERRED in a transaction.  So that the dependencies only drop if the original table doesn't exist at the end of the commit, but giving you the ability to drop the table, create the table, and commit without losing changes.


texts: Efficient SELECT for array of tuples in PostgreSQL

texts:
Assume I have a table records with the following structure

id (unique int)
updated (timestamp)

I have an input_array with values [[id_1, timestamp_1], [id_4, timestamp_4], ...]. I'll refer to each element as tuple_1, tuple_4, etc.

I'm looking for the most efficient query (in PostgreSQL v11.2+) to select [id_1, id_4, ...] from records, but only where tuple_{n}.updated > row{n}.updated. Assume input_array may contains thousands of tuples, and records upwards of a million rows.

I don't even know where to begin with this. Lateral join comes to mind, as does unnest, and where in, but everything I've tried so far fails miserably

Update I'm open to input_array being in any format (tuples, two separate arrays, whatever), and updated being an int


texts: Consequences of increase Oplog to large size

texts:
I'm trying to add a new member (m*-server: 4.2.7) in my replica set (m*-servers: 4.2.1),
and it can't sync in time, after the initial sync it changes state to RECOVERING and have: (~ 4.3 - 6.31 hrs) behind the primary; Oplog size: "usedMB" : 50769.34, "timeDiffHours" : 1.19 ( ~0.79 -1.19).

I can't optimize queries or reduce size of dbs (~1.9T), or make backup and copy data (there is not enough time),
so think only solution for me is replSetResizeOplog on all members.

Question:
What the consequences can be I if increase size of Oplog larger than Upper Bound: 50GB (WiredTiger Storage Engine) to for example: 400 Gb?


texts: How to move geographical data from MS Access to SQL Server

texts:
For 20 years I've been working with MS Access, with much VBA involved.
I have a lot of geographical data in Access and I'm preparing to move to SQL Server because it supports geographical data types, and because it has a good connection with QGIS, in which I want to show and query my geographical data efficiently in a map.

Basically, my geographical data in Access comprise my GPS tracks that I've collected in 20 years, altogether about 5000 tracks with an average 800 points per track. In Access I have two tables for them:

[tbl Track] with metadata describing the track (e.g. distance, start date and time, duration, maximum elevation etc.).
[tbl TrackGeo] with the tracks themselves in geographical terms. Attributes:

ID (autonum, PK)

TrackID (long, FK to [tbl Track])

LatWGS84 (double)

LonWGS84 (double)

Moving [tbl Track] to SQL Server is easy, but it gets difficult for me (newbie in SQL Server and its geographical data types!) to move the geograhical data to SQL Server in a spatial data field.

My idea is:
Put the data of [tbl TrackGeo] per track in a geography spatial data field (type linestring) in [tbl Track] in SQL Server.

My question is:
What is the best approach to accomplish this, preferably (if possible) using VBA from within MS Access?


texts: How to convert an age interval into a decimal year number in PostgreSQL

texts: I try to convert an interval like "29 years 6 mons 22 days 21:17:52.860842" into a decimal year reprensentation like 29,55 years in PostgreSQL.


texts: Database design for recurring lessons for private school

texts:
I am currently designing a database schema for a private school. One feature needed is that students can have recurring lessons (e.g. each Monday at 2pm, teacher x, room y). I searched the internet for solutions and the 2 most prominent were:

Only store the "event" with a recurrence pattern in a table in the beginning. Afterwards only store exceptions to this schedule. When events are queried generate the required instances at runtime. This seems to be the preferred approach.
Store the "event" with a recurrence pattern in a table, generate all actual instances into a second table.

At the moment i am unsure which approach to follow. Personally i think the second approach is much easier for various reasons:

requesting events comes down to a simple db-query instead of "more complex" instances generation
you have actual instances to modify and delete instead of dealing with "event-exceptions" which need to be taken into consideration during event instance generation of the first approach
requirements which are tied to an individual event instance, like lesson attendance reporting (was the student present or absent?) are easier to implement (i do not even know how i would achieve it using the first approach)

The biggest argument i found against the second approach was how to deal with events with no end-date, but in my case students are tied to a contract with a maximum runtime of 12 months. Therefore i have a finite number of event instances.
I tried to construct a simple example how many rows would be created at maximum for a given private school in a year. Given a school with

10 rooms
5 students per room
5 90 minutes timeslots per room (0:30-8:00 pm) per day
7 days a week, 4 weeks a month, 12 months

you would end up with a maximum of 84000 instances per year. I do not think that a table of a database like Postgres would have any problems with this amount of data.
What are the considerations i should make for this design? Thanks in advance.


texts: T-SQL Gurus, I need help optimising these queries for performance

texts:
I would like to have suggestions on improving the performance of some queries. These will be run on SQL Server 2008 R2 against a single table called dbo.bids

In a production environment the table is expected to hold up to 1,000,000 records. Here is what I have upto now.

    /* dbo.Bids structure
    bid     decimal (10,2)
    auctionid   bigint
    msisdn      bigint
    lineid      bigint
    timestamp   bigint

CONSTRAINT [PK_Raw] PRIMARY KEY CLUSTERED
(
    [auctionid] ASC,
    [bid] ASC,
    [msisdn] ASC,
    [lineid] ASC,
    [timestamp] ASC
)

    dbo.bids test data:

    bid     auctionid   msisdn  lineid  timestamp
    1.01    1011        9999    1       0
    1.20    1011        8888    2       1
    0.98    1011        7777    4       2
    1.01    1011        6666    5       3
    0.98    1011        5555    7       4
    0.99    1011        6666    8       6
    0.99    1011        4444    9       6
    0.99    1011        6666    10      6
    0.99    1011        3333    11      8
    1.20    1011        1234    13      10
    1.01    1011        8888    14      11
    */

    DECLARE @auctionid bigint;
    DECLARE @msisdn bigint;
    SET @auctionid = 1011;
    SET @msisdn = 8888;

    SELECT TOP 1 [bid], [msisdn] FROM [dbo].[bids]
    WHERE [auctionid] = @auctionid AND [bid] =
        (
            SELECT [bid] FROM
                (
                    SELECT TOP 1 [bid], COUNT([bid]) AS [tally] FROM [dbo].[bids]
                    WHERE [bid] >= 0.01 AND [auctionid] = @auctionid
                    GROUP BY [bid] ORDER BY [tally] ASC, [bid] ASC
                ) AS LowestTally
         ) ORDER BY [lineid] ASC;

    /* RESULTS SHOULD BE
    bid msisdn
    0.98    7777
    */

    SELECT msisdn, MinBid, Ranking FROM
    (SELECT msisdn, MIN(bid) as MinBid, ROW_NUMBER() OVER(ORDER BY MIN(bid), MIN(lineid)) AS Ranking FROM [Bids] WHERE [auctionid] = @auctionid and [bid] IN
    (select [bid] from [Bids] WHERE [auctionid] = @auctionid and [bid] >= 0.01 GROUP BY [bid] HAVING COUNT([bid]) = (SELECT [tally] FROM (SELECT TOP 1 bid, COUNT(bid) AS [tally] FROM [dbo].[bids] GROUP BY bid ORDER BY [tally] ASC) AS LowestTally) )
     GROUP BY msisdn ) AS Temp  ORDER BY MinBid;

     /* RESULTS SHOULD BE
    msisdn  MinBid  Ranking
    7777    0.98    1
    5555    0.98    2
    8888    1.20    3
    1234    1.20    4
     */

    SELECT msisdn, MinBid, Ranking FROM
    (SELECT msisdn, MIN(bid) as MinBid, ROW_NUMBER() OVER(ORDER BY MIN(bid), MIN(lineid)) AS Ranking FROM [Bids] WHERE [auctionid] = @auctionid and [bid] IN
        (SELECT [bid] FROM [Bids] WHERE [auctionid] = @auctionid and [bid] >= 0.01 GROUP BY [bid] HAVING COUNT([bid]) =
            (SELECT [tally] FROM
                (SELECT TOP 1 bid, COUNT(bid) AS [tally] FROM [dbo].[bids] GROUP BY bid ORDER BY [tally] ASC) AS LowestTally))
     GROUP BY msisdn ) AS Temp  WHERE msisdn = @msisdn ORDER BY MinBid;

     /* RESULTS SHOULD BE
    msisdn  MinBid  Ranking
    8888    1.20    3
     */


texts: So I have some Zabbix instances which are using MySQL for their backends. What I would like to do is, for DR purposes, backup the DBs for each instance using a Master/Slave configuration, with the slave node sitting at the DR site. However, I don't want to capture trends/historical data, all I want is configuration data. Is there a way to replicate Zabbix's database without populating the tables which hold all of the applications historical monitoring metrics?


texts:
I have an InvoiceHeader, InvoiceItem tables

InvoiceHeader content:

InvoiceId   InvoiceNumber   BusinessId  BusinessName    BranchId
        1               1           10       BSN INC    1
        2               2           10       BSN INC    2
        3               3           23    SAMPLE INC    1
        4               4           45   ANOTHER INC    1
        5               5           45   ANOTHER INC    3

InvoiceItem content:

InvoiceId   ItemId  ItemCode    Name            Quantity
        1        1    SNACK1    GOOD SNACKS     150
        1        2    SNACK2    SNACKERS        120
        2        1    SNACK1    GOOD SNACKS     100
        2        2    SNACK2    SNACKERS        150
        3        1    SNACK1    GOOD SNACKS     150
        3        2    SNACK2    SNACKERS        150
        3        3    SNACK3    BIG SNACKS      110
        4        1    SNACK1    GOOD SNACKS     90
        4        2    SNACK2    SNACKERS        80
        4        3    SNACK3    BIG SNACKS      120
        5        1    SNACK1    GOOD SNACKS     50
        5        2    SNACK2    SNACKERS        70
        5        3    SNACK3    BIG SNACKS      90
        5        4    SNACK4    TASTY SNACKS    70

Here is the sqlfiddle: http://sqlfiddle.com/#!18/aa5fa/6

Now the result that I want is like this:

InvoiceNumber   BusinessName    BusinnessBranch SNACK1  SNACK2  SNACK3  SNACK4  TotalItems
    1           BSN INC         Branch1         150     120     0       0       270
    2           BSN INC         Branch2         100     150     0       0       250
    3           SAMPLE INC      Branch1         150     150     110     0       410
    4           ANOTHER INC     Branch1         90      80      120     0       290
    5           ANOTHER INC     Branch3         50      70      90      70      280
                                                                                1500

As you can see the items code from the invoices are added as columns to count the items, in the last column the items total from that invoice, and the last row it shows the total items from all selected invoices.

The number of transposed columns can be more than that, can reach even more than 10.


texts: Use flag on each row to mark record as primary or use foreign key on parent table

texts:
I came across pretty simple and probably very basic problem, and I can't decide what solution is the best.

After simplifiyng my scheme to the bone, I have this:

table of Suppliers (id, name)
table of Parts (id, name)
m to n table PartSuppliers(supplier_id, part_id, price)

I need to mark one of the suppliers as primary supplier for given part. I see two options:

1) (currently used)
Add primary_supplier_id column to Parts table

2) Add column is_primary to PartSuppliers table

Both solutions have some pros and cons. Solution 1) there are problems with validation and CRUD - info about part suppliers is on "two places".  Solution 2 would be easier to use in app, but isn't it breaking normalization?


texts: Is it possible to install just the mongo Shell?

texts:
I have Docker installed and am running a MongoDB container for my local development on my Mac.  The problem is that I can't connect to said DB easily from CLI.  I have Robo 3T installed, but I would prefer to use the CLI client instead.

Is there a known way to install JUST the mongo shell (command mongo) and not the full DB distribution on OS X?


texts: Oracle Reports 10g (rwserver.sh) - connecting from a remote server

texts:
I have an Oracle Reports server 10g (10.1.0.4.2) that for the last few years has been running okay with the rwclient.sh client application residing on the same host.

Due to some performance issues caused by long running/heavy reports we are looking at trying to separate the reports server from the application server.

From memory rwserver.sh  should be able to accept connections from a client on the same LAN, but not on the rwservers localhost (but I'm going back many years since I saw that sort of setup and I was just a developer, not an admin, so I could be misremembering it).

When I try and submit a report across the network I get:

dgs@local:~ $ rwclient.sh server=rep_cig userid=test/test@CIGDB report=/home/dgs/report_file/RCL16.rdf desformat=XML destype=FILE desname=/home/dgs/report_output/claims_outstanding.xml imp_comp_id=1619

REP-0178: Cannot connect to Reports Server 'rep_cig'.

I've enabled tracing on the reports server and can see requests on the server coming from the external clients (i think), but I only get one line per report run attempt. (But consistently get this same line so I think the rwclient is at least seeing the rwserver):

 [2017/8/4 1:43:19:104] Debug 50103 (ServerPacketReceiver:handlePacket): Got a request packet

Looking at the output of  netstat -tupln, there seems to be number of ports opened by the reports/java process - most of them open to 0.0.0.0, but a couple are bound to 127.0.0.1:

Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp6       0      0 :::33886                :::*                    LISTEN      30759/java
tcp6       0      0 :::38083                :::*                    LISTEN      6823/java
tcp6       0      0 :::51140                :::*                    LISTEN      30729/java
tcp6       0      0 :::43429                :::*                    LISTEN      7337/java
tcp6       0      0 :::42182                :::*                    LISTEN      7490/java
tcp6       0      0 :::47181                :::*                    LISTEN      30818/java
tcp6       0      0 :::52270                :::*                    LISTEN      30797/java
tcp6       0      0 :::38711                :::*                    LISTEN      4916/java
udp        0      0 127.0.0.1:54063         0.0.0.0:*                           4916/java
udp        0      0 127.0.0.1:40289         0.0.0.0:*                           7337/java
udp        0      0 0.0.0.0:123             0.0.0.0:*                           7490/java
udp6       0      0 :::14021                :::*                                30700/rwserver

(The java processes above are all for oracle.reports.engine.RWEngine)

I assume the 0.0.0.0/127.0.0.0.1 multiple ports things is why the server can see the initial probe, but the client then fails to connect properly.

I suspect there's got to be an option somewhere that determines what interface the server tries to bind to but I can't find any option in the report server .conf to enable this.

Questions:

Is it actually even possible to connect to the reports server
from external clients? and if so;
Where abouts do I need to
configure the reports server so it listens for external connections?


texts: How to change ANSI_NULLS option for a table?

texts: I have a table that mistakenly has ANSI_NULLS option set to OFF. I'd like to set it ON for this table. The table has millions rows of data. Is it possible to change this option without creating a new table with ANSI_NULLS ON, moving all the data into this new table and renaming this table. Currently downtime for using this solution makes it impractical. Is there another way?


texts: Using dynamic database name for DML in trigger

texts:
I am using SQL Server 2008 in my application. Here I need to do database operations between multiple databases. The complexity is database names are user defined except one masster database.
I can have information about secondary database name in my master database, from which I have to fire DML statements. I am using following approach to achive the same:

USE [Database1]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[CTR_Production_InsertRecordInDatabase2] ON [dbo].[Table1]
AFTER  INSERT, UPDATE, DELETE
AS

SET NOCOUNT ON;

    DECLARE @SecondDatabaseName varchar(125)

    --Fetch second db name
    SET @SecondDatabaseName = dbo.sfn_GetSecondDbName()
    :SETVAR SecondDatabaseName @SecondDatabaseName

------------------------------INSERT TO Second DB------------------------------

    INSERT INTO $(SecondDatabaseName).[dbo].[pDM_Production_RecordInfo]
       (  Column1
        )
        (
          SELECT Column1 FROM INSERTED i
        );

But due to some misinterpretation of SQLCMD usage could not achieve the same.

Here my requirement is to,

Fetch secondary db name from Table1 in Database1
set to some variable
use it to insert data in secondary database

Could anyone please guide me to achieve the same?


texts:
I have a table event_seating_lookup with 3 columns: (id int, event_id int, lookup_is_successful boolean).

For every event_seating_lookup record, I want to get a number of previous (lag) records with the same lookup_is_successful value, i.e.

Assuming the following setup:

CREATE TABLE event_seating_lookup  (id int, event_id int, lookup_is_successful boolean);

INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (1, 1, TRUE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (2, 1, TRUE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (3, 1, TRUE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (4, 1, FALSE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (5, 1, FALSE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (6, 1, TRUE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (7, 1, TRUE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (8, 1, TRUE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (9, 1, TRUE);
INSERT INTO event_seating_lookup (id, event_id, lookup_is_successful) VALUES (10, 1, TRUE);

Using a simple row_number() function gives absolute number within the group:

SELECT
  esl1.id,
  esl1.event_id,
  esl1.lookup_is_successful,
  row_number() OVER (PARTITION BY event_id, lookup_is_successful ORDER BY id ASC) consecutive_lookup_is_successful_without_change
FROM event_seating_lookup esl1
WHERE esl1.event_id = 1
ORDER BY esl1.id DESC;

id  event_id  lookup_is_successful  consecutive_lookup_is_successful_without_change
--  --------  --------------------  -----------------------------------------------
10  1         true                  8
9   1         true                  7
8   1         true                  6
7   1         true                  5
6   1         true                  4
5   1         false                 2
4   1         false                 1
3   1         true                  3
2   1         true                  2
1   1         true                  1

http://www.sqlfiddle.com/#!15/7f550/2

Meanwhile, I want to get a table:

id  event_id  lookup_is_successful  consecutive_lookup_is_successful_without_change
--  --------  --------------------  -----------------------------------------------
10  1         true                  5
9   1         true                  4
8   1         true                  3
7   1         true                  2
6   1         true                  1
5   1         false                 2
4   1         false                 1
3   1         true                  3
2   1         true                  2
1   1         true                  1

The ultimate objective is to get a table that tells me for every event (event_id) in event_seating_lookup, how many failed lookups (lookup_is_successful=FALSE) there are, i.e.

event_id  consecutive_lookup_is_successful_without_change
--------  -----------------------------------------------
5         5


texts:
I need to set all values of a given column foo to a constant varchar bar. Is there a faster way to do that than with the vanilla UPDATE mytable SET foo = 'bar'?
Facts:

This is an Oracle MySQL 8.0.20 server (via SELECT @@VERSION)
This table uses the InnoDB engine
The column is not part of any index
The column datatype is varchar(2000).
I can't change the datatypes
I have full access to the server and could manipulate the .idb files directly, if necessary.
The largest table has ~1.7mi records and the regular update takes ~30mins
Memory, CPU and storage doesn't seem to be a bottleneck, so far (checking iotop and htop itself)

Note: I need to do similar updates to multiple columns in multiple tables, but what I describe here applies to them all, except for the datatype, which is the worst case (all the other columns are also varchar, but not that long).


texts:
The Dashboard option is gone from the menu. Now I can't see the nice graphs anymore.

What do I need to get it back

I'm using version 4.17?


texts: How to model a 1..1 Composition Relationship

texts:
We have a couple of entities/relationships of the following kind:

Entity Event has a TimePoint
The TimePoint contains time information about the event. It belongs to that event and cannot be used by any other entity. Without the corresponding Event, the TimePoint has no meaning, it is orphaned. In short, it looks like a composition to me.
The Event, in turn, must have a TimePoint. There is no event without a time. It depends on the TimePoint.

The question I am facing is, which entity has the foreign key to the other entity?

Option 1:

FOREIGN KEY (Event.TimePointID) references TimePoint.ID

where as Event.TimePointID NOT NULL

Problem: When the Event is deleted, the TimePoint is left orphaned.

Option 2:

FOREIGN KEY (TimePoint.EventID) references Event.ID

where as TimePoint.EventID NOT NULL

Problem: When the TimePoint is deleted, the Event becomes invalid and makes the application crash.

I think this is a pretty common issue and there must be a good old DBA's advice to how to model such a relationship...?

Update: The example above is simplified to only represent the relationship in question. One reason why we need two separate entities is because a TimePoint can be owned by a record from a different table instead of an Event too. But it must always be owned by exactly one record from exactly one of either table.


texts: Start, stop, configure PostgreSQL through Mac terminal

texts:
I am having basic problems using PostgreSQL on my Mac OSX Yosemite, through the terminal. I tried google-ing but this is becoming increasingly difficult and info very scattered so I keep running into walls.
I did the following:

installed PostgreSQL 9.5.2 via Homebrew
modified my shell config file (.zshrc) to include PostgreSQL bin directory in $PATH variable
I did the "initdb" routine and "told" Postgres that I want to store my databases in /usr/local/cellar/postgresql/9.5.2/data

Here I tried starting postgreSQL:
$pg_ctl /usr/local/cellar/postgresql/9.5.2/data -l logfile start

first error:

pg_ctl: another server might be running; trying to start server anyway
/bin/sh: logfile: Permission denied

As far as I understand, I am trying to start the PostgreSQL server and direct its output and errors into a log file, located in a location for which my user has no write privileges.
How can I fix this?
Here I tried stopping the server and try again:
$pg_ctl -D /usr/local/cellar/postgresql/9.5.2/data stop

This worked ok. So,

How can I use other location to start the server and enable log
How do I check if the server is running if I need to stop it (if errors appear)?


texts: Non-overlapping rectangles constrained to a boundary

texts:
I am trying to model placement of parts on a circuit board. Without any
meaningful constraints, my basic schema looks like this:

create table part (
    part_id bigserial primary key,
    name text not null,
    width double precision not null,
    height double precision not null
);
create table board (
    board_id bigserial primary key,
    width double precision not null,
    height double precision not null
);
create table board_part (
    board_id bigint not null references board,
    part_id bigint not null references part,
    position point not null
);

(SQL Fiddle, Visualization)

For b and b2 any board_parts, I want to enforce the following
constraints:

b lies on the board:

box(b.position, point(b.part.width,b.part.height))
    <@ box(point(0,0), point(b.board.width,b.board.height))

b and b2 do not overlap if they lie on the same board:

b.board_id != b2.board_id or
not (box(b.position, point(b.part.width,b.part.height))
        && box(b2.position, point(b2.part.width,b2.part.height)))

How can I achieve this (without too much data duplication)? Changing the schema is fine.

Here is my best attempt (SQL Fiddle), taking
inspiration from
Erwin's answer to my previous question.
It enforces the constraints I wanted, but has a lot of duplicate data in the board_part table. I imagine I could write a function to fill in the board_width, board_height, part_width, and part_height fields automatically, but it still feels wrong having so much duplicate data around. Also, keying to the width/height fields feels like a hack.


texts: Explain PostgreSql ssl_cipher values: ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH

texts:
In the postgresql.conf file, one can configure authorized values for ssl_cipher. Yet I could not find documentation explaining what the ALL, ADH, LOW, EXP, MD5 and STRENGTH values correspond to.

MD5 refers to the corresponding MD5 algorithm I guess, but what about the other values? I believe LOW should not be used in a production environment.


texts:
Disk space was full on my server and I needed to do a mysqldump. I tried logging into MySQL, but temporarily forgot the password. I then tried using the --skip-grant-tables & , but I received an error and it did not work. I tried several times.

I finally got hold of the correct password, but I encountered an issue that:

Can't connect to local MySQL server through socket '/var/lib /mysql/mysql.sock'

Somehow I was able to get rid of that error through lots of trial and error, but now I can still not login.

Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost'(using   password: YES)

I need to reset the password for MySQL without losing data.


texts: Query plan showing a missing index even though the index is present in the table

texts: In one of my production SQL server instance, I am facing an issue. A query is taking long time to run. While checking SQL query plan, I found that it was giving suggestion for creating a missing index. I went to the recommended table and found that the suggested index was already present in the table but was some not getting used.  Maintenance plans (of rebuilding indexes, updating statistics etc) are done on a regular basis.I am not sure why the index is not getting used ? and why query plan keeps on giving suggestion for missing index when it already exists? Any help would be really appreciated.


texts: Why scalar valued functions need execute permission rather than select?

texts:
I am wondering why, for scalar valued function, that I have to grant the user to execute rather than just a select?

meanwhile a table valued functions works just fine with only select permission or db_datareader membership.

to be more clear here is my example:
I need a user that has read only permission to the database.
so I created a user called testUser and give it db_datareader membership.
then I created a table valued function called fn_InlineTable. And all is great. testUser runs this SQL all day long

select * from dbo.fn_InlineTable

then I need a scalar function , so I created a scalar function called fn_ScalarTest.
testUser cannot run this SQL

Select dbo.fn_ScalarTest(1)

Well understandably: it is because I have not given "testUser" permission to execute fn_ScalarTest.

My question is: based on this link https://stackoverflow.com/questions/6150888/insert-update-delete-with-function-in-sql-server,
that says a FUNCTION cannot be used to perform actions that modify the database state. So why not let a scalar function to be used with the same "SELECT" permission rather than execute permission??

I hope my question makes sense. Thank you.