texts:
I am dealing with SQL that looks as follows

create procedure

as

begin

Notice the large gaps e.g. between as and begin.

How can I remove these? I used SQL formatter but this is not working.


texts: How to create function with Return Type as (TABLE student_name string)

texts:
I am creating function using pgAdmin4 and the CODE Basically something like

BEGIN
   RETURN QUERY
    SELECT * FROM teest."students";
END

I am just wondering, how do I set the TABLE ReturnType as
TABLE(applicant_id integer, ship_id integer) something like that. There's no TABLE return type there.

Thanks


texts: Oracle19C installation is stuck at executing datapatch in Windows 10 using dbca

texts:
I tried using dbca. There are no errors. It just hangs. I tried leaving it running for 4 hours. It is always stuck at 36% even after multiple tries.

Logs:

[progressPage.flowWorker] [ 2020-05-25 00:26:04.206 IST ] [CloneDBCreationStep.executeImpl:823]  executing datapatch %ORACLE_HOME%\OPatch\datapatch.bat


texts: Is it safe to make a Unique Index the Primary Key?

texts:
I have a vendor database that I want to use Change Data Tracking against but cannot because none of their tables have Primary Keys defined.  The tables do have Unique Indexes but they are not defined as PK Constraints.  It seems to me that making them Primary Keys would be a safe thing to do (i.e. will not interfere with or change the software operation) given that they are already Unique Indexes.

Can anyone tell me if my understanding is correct or if I am missing something?


texts: How do I replicate a MySql server's data without using mysqldump?

texts:
I've got a working server with a lot of data on it. I've built a new server that will be lots faster. Both are running x686 code on Debian Squeeze.

A quick test using one of the tables shows that it takes about ten days to load up
the output of a simple
mysqldump dbname tablename > table.sql
mysql dbname < table.sql

Since I have lots of tables with lots of data, this isn't going to work. It would take until Christmas to load them all up.

Is there any way to just copy the binary files that mysqld is using on the first server to the disks on the second and use them without the dump/reload?

If not, are the dump or load options that will get me a two order of magnitude speed improvement?

A typical table has 10 million to 100 million records. Records lengths vary from 100 bytes to 10K bytes per record.

It would be nice to have the old slow server be a replication client of the new fast one, but I think I have to get the data up first.

Thanks


texts: Best practise for data dump 'entry point' in DB processing chain - PostgreSQL

texts:
I need to implement a process chain to transform and analyze potentially large sets of data, inserted both programmatically and manually (e.g. CSVs). In either way, the input data is standardized in terms of columns and datatypes. New data-sets are added regularly.

Once inserted, however, the data needs to be transformed and aggregated (spatially -> PostGIS) into different table structures (columns & datatypes) to then be analyzed extensively and distributed among several related tables where they will be stored (appended) as results. The initially inserted data will be obsolete once processed.

l need to define an 'entry point' to this process chain, and I am not sure which way to go; I intend to have users generally to not work on the base relations themselves (thus working on Views, while 'working on' generally refers to select and export), and I'd like to not have an 'empty' (truncated each time) physical table sitting in the DB for this if not necessary.

The 'entry point' should ideally be accessible via simple INSERT or COPY/\copy commands; I could possibly set up fdw connections to directly query the data without an 'entry point', but I'm not sure how I could make that available universally and mostly automated (I don't want users to need to set up the fdw server connection each time; if this can be automated, feel free to add solutions accordingly).

How would I define an 'entry point' to dump data for further (automated) processing, where the input data structure differs substantially from the subsequent tables in the chain and has to be deleted after import or is only transitional?


texts: how to add a data source to SSRS - reporting services 2014?

texts:
For infomation as Create, Modify, and Delete Shared Data Sources (SSRS)
but I want to get this done quickly, so when I have been requested to add a new data source to our reporting services:

This is the way I am currently doing it:
I look for another data source that I already have -  (example only - the real server name has been modified)

  http://myreportingserver/Reports/Pages/DataSource.aspx?ItemPath=%2fData+Sources%2fOEL_UK

there I have already another data source working fine,
I get the information like the connection string,
and the credentials (user name of the account running the report) and modify accordingly for my new data source.

then, for creating a new data source I go to the page below and press new data source adding the relevant data.

Question

Isn't there another simpler way of creating a data sources?

I was thinking something like from the ReportingServices database there must be some table with the datasource records, and all the information.
Just copying that info into another record, doing the appropriate changes - and I will have my new data source.

This question shows how to find data sources in the reporting services database

How can I tell which data sources are being used in SSRS?


texts:
Right now I have a trigger in my database that is giving me some issues. I've set up this line so I can check what is being fetched from a query

RAISE NOTICE 'hstore_key = %',hstore_key;

as well I have postgres configured as so for logging

log_destination = 'stderr'

logging_collector = on

log_directory = '/var/log/pg_log'written,

client_min_messages = notice
log_min_messages = notice
log_min_error_statement = notice

But when I check my log at /var/log/pg_log I don't see any thing regarding my notice, using the command

/hstore_key =

in vim to search for the string that the notice should have put out. I have also tried to place a test notice at the start of the trigger (the trigger executes successfully, it just does not give me the results I'm looking for), the notice I placed at the top of the trigger was

RAISE NOTICE 'beginning of proto trigger';

And it does not display in the log either. Is there a configuration setting I missed enabling in my postgresql.conf?


texts: MongoDB ticket availability reaching zero

texts: I recently came across intresting issue. I have a sharded cluster which has two replication sets with 2 secondaries. On my primaries, there were 300-400 connections at a point in time, shortly after sometime around a minutes spane, there were almost read and write connections in total 1200 spiked over primaries. Read and write ticket availability went down to 0 for few seconds. Cluster was non responsive during that time. Cluster usually handels more load then this during month end. Any suggestions to find cause of this issue?


texts: MySQL GROUP BY query drastically slow down when after data reaches a certain amount

texts:
I need help finding out why the following query that aggregates peoples' responses by "item" slows down considerably after the data being aggregated reaches a certain size.

select
  t1.item,
  count(t1.val) as count,
  round(avg(t1.resp), 2) as mean,
  round(std(t1.resp), 2) as std
from
  person_response as t1
  join person_info as t2
    on t1.person_id = t2.id
where
  t1.resp between 1 and 5
  and t2.is_valid = 1
  and (t2.attr1, t2.attr2) in ((?, ?), ...)
  and t2.attr3 in (?, ...)
  and t1.item in (?, ...)
group by t1.item
;

t1 has about 15 million rows and t2 has about 200,000 rows. All columns involved in the query are indexed. If the WHERE clause captures under 50,000 rows, execution is generally under 100 ms. If the WHERE clause captures 138,665 rows, execution is under 500 ms. But when I extend the WHERE clause to capture 150,685 rows, execution takes considerably longer at 3 m 40 s 366 ms. Nearly 4 minutes!


texts: How would I undo or edit the last line of a multi-line query in mysql?

texts:
I often make mistakes when entering multi-line queries in mysql. Is there an easier way to undo the last line instead of retyping the entire query?

mysql> SELECT age, name
    -> FROM people
    -> WHERE age > 20
    ->

For example, I meant to type age < 20, is there a way to quicky fix that? Normally I would ctrl + C and use the up arrow key to re-enter first two lines quickly, but I feel like there should be an easier way.

I tried reading documentation and read some forums but I couldn't find anything. It should be a new feature if it doesn't exist.


texts: Time to apply transaction logs: does it matter how many logs?

texts:
When restoring from a backup in SQL Server, the procedure is to restore the .bak file and then apply any .trn files since the last full backup.

Does it make a difference how many .trn files there are, if they cover the same transactions? I.e. is it faster or slower restore a 1-hour .trn file vs twelve 5-minute .trn logs?


texts:
I created a PostgreSQL 10 instance on Amazon RDS with the PostGIS 2.4 extension in my database enabled. I want to test the performance of Amazon RDS as a dynamic vector-tile server.

This hinges on the ability to use the ST_AsMVT and ST_AsMVTGeom commands in the PostGIS enabled database.

These functions require the libprotobuf-c libraries to be installed on the system which hosts the PostgreSQL instance.

It seems that Amazon RDS instances have no support for libprotobuf-c and PostgreSQL/PostGIS on Amazon RDS cannot make use of the vector-tile functions which have been introduced with PostGIS 2.4.

Does anyone know about means to enable libprotobuf-c support on Amazon RDS?


texts: Speed up data transfer from db to application

texts:
I have build an application that handles data stored in a MS SQL server database. This server is hosted externally of our company and has to be connected to via SSH because of company rules.

On a regular working day it takes about 7 minutes to retrieve a list of data, while it takes less than one minute to do the same outside office hours.

The database is relatively simple. There are 3 main tables which have a relation to each other on one field which is also set as primary key. These tables have relations with several other small tables where lists are stored. So the fields in the main tables are integer fields related to a small table where that integer points to a text field.

I'm trying to figure out why it takes 7 minutes during office hours and less than one 1 outside office hours.

There are about 12 users.

Anyone have some tips for me?

rg, Eric

Edit:  SQL code:

SELECT
t1.TicketNummer
,t1.SiteNummer
,t9.name AS Categorie
,t7.name AS Klant
, (CASE WHEN t11.RapportNaam IS NULL THEN t5.FullName ELSE t5.FullName END) AS     AangenomenDoor
, (CASE WHEN t11.RapportNaam IS NULL THEN t1.AangenomenOp ELSE t1.AangenomenOp END) AS     AangenomenOp
, (CASE WHEN t11.RapportNaam IS NULL THEN t1.aangenomenop ELSE t8.UitgevoerdOp END) AS     UitgevoerdOp
, (CASE WHEN t11.RapportNaam IS NULL THEN t5.FullName ELSE t8.UitgevoerdDoor END) AS UitgevoerdDoor
, (CASE WHEN t11.RapportNaam IS NULL THEN 'Via ESIT' ELSE t11.RapportNaam END) AS Rapport
,t8.VraagNummer
,t8.Uploaded
,t2.Name AS PrioCode
, t4.offertenummer
, t4.ponummer
, CASE WHEN t4.hersteldoor=0 THEN ''
       WHEN t4.hersteldoor=1 THEN 'Aannemer'
       WHEN t4.hersteldoor=2 THEN 'Eigen personeel'
       WHEN t4.hersteldoor=3 THEN 'Operator'
       ELSE 'Aannemer' END AS TeHerstellenDoor
, t12.refnraannemer
, t12.offertebedrag
, t13.name as Operator
, t4.operatorRefNr
,(CASE WHEN t1.IsManco ='true' AND t1.IsOpgelost = 'false' THEN 'Manco'
       WHEN t1.IsConstatering ='true' THEN 'Constatering'
       ELSE '' END) AS IsManco
,(CASE WHEN t1.IsOpgelost ='true' THEN 'Direct opgelost'
       WHEN t6.verwijderd = 'true' THEN 'Verwijderd'
       ELSE t3.name END) AS VerwerkingsGroep
,(CASE WHEN (year(t4.DatumTechnischGereed)<2000 or t4.technischgereed='false') THEN NULL     ELSE CONVERT(VARCHAR,t4.DatumTechnischGereed,20) END) AS DatumTechnischGereed
,(CASE WHEN (year(t4.DatumAdministratiefGereed)<2000 OR t4.administratiefgereed='false')     THEN NULL ELSE CONVERT(VARCHAR,t4.DatumAdministratiefGereed,20) END) AS     DatumAdministratiefGereed
,(CASE WHEN (year(t4.DatumFinancieelVerwerkt)<2000 OR t4.financieelverwerkt='false')     THEN NULL ELSE CONVERT(VARCHAR,t4.DatumFinancieelVerwerkt,20) END) AS     DatumFinancieelVerwerkt
, (CASE WHEN t4.financieelverwerkt = 'true' THEN 'Ja' ELSE '' END) AS financieelVerwerkt
, (CASE WHEN t4.AdministratiefGereed = 'true' THEN 'Ja' ELSE '' END) AS     AdministratiefGereed
, (CASE WHEN t4.TechnischGereed = 'true' THEN 'Ja' ELSE '' END) AS TechnischGereed
, t10.Name AS Aannemer
, t6.verwijderd
, t14.fullname AS MutatieDoor
,(CASE WHEN (year(t4.MutatieOp)<2000) THEN NULL ELSE t4.MutatieOp END) AS MutatieOp
FROM MainTickets AS t1
LEFT JOIN PrioCode AS t2 ON t1.PrioCode =t2.iD
LEFT JOIN TicketVerwerking AS t4 ON t1.TicketNummer =t4.TicketNummer
LEFT JOIN Verwerking AS t3 ON t4.VerwerkingsGroep =t3.ID
LEFT JOIN Gebruikers AS t5 ON t1.AangenomenDoor =t5.ID
LEFT JOIN TicketNummers AS t6 ON t1.TicketNummer=t6.TicketNummer
LEFT JOIN Klanten AS t7 ON t1.Klant=t7.ID
LEFT JOIN SubTickets AS t8 ON t8.TicketNummer =t1.TicketNummer
LEFT JOIN Categorie AS t9 ON t9.ID =t1.Categorie
LEFT JOIN Aannemers AS t10 ON t10.ID =t4.aannemeropdracht
LEFT JOIN FormIDs AS t11 ON t8.formidcode=t11.FormIDcode
LEFT JOIN OfferteAannemers AS t12 ON (t4.offertenummer=t12.offertenummer AND     t12.aannemer=t4.aannemeropdracht)
LEFT JOIN Operators AS t13 ON t4.operator=t13.id
LEFT JOIN Gebruikers AS t14 ON t4.mutatiedoor =t14.ID
ORDER BY t1.TicketNummer


texts: How is Activity Monitor Pulling processes specific memory information?

texts:
How is SQL Server Management Studio's Activity Monitor pulling the following memory by processes information?

The screen I am talking about is...
Log into the database, right click on the server in the tree on the left and select activity monitor.
Expand out processes.
The Memory Use column - where is it coming? Is there an internal database table that it is pulling from?

I ask because I am trying to figure what all goes into this metric so I can determine if one of the processes is growing without bound or growing as expected due to design.


texts: Tool to test RDBMS SQL dialect for ANSI SQL standard compliance

texts:
We are developing an interface to our internal data storage and manipulation tool that would allow people to use SQL dialect.

We decided that it would be perfect to make our SQL dialect as close to the standard as possible.

Currently I'm trying to find a tool that would test our implementation of query language and make a verdict about its compliance and list missing details.

Have anyone ever encountered anything like this?


texts: Stored Procedure Resultset Row Count and Resultset Size in Bytes/KBs

texts:
Is there any way to get total row count and size of result-set generated by a stored procedure execution?

@@ROWCOUNT is a good option if its already added in each stored procedure but I just want something like all RPC calls with in specific time period, with Row count and Size (in Bytes or KBs) for each call without modifying any stored procedure.


texts:
I'm doing a small database for statistics, it's a really simple statistic like calculating the cost of an event and divide it by the amount of people that went to get the cost per attendee, but it can end up having loads of event, and it will require to poll in a lot of results at once.

I know with MySQL I can use the database to do such simple calculation, so I could run a query every time is needed, but it can end up being costly, if there's a lot of events in say a year.

My question is if I were to store the statistic results on a different table, I see it a little bit as data duplication, is that acceptable? or should I do it in a different manner?

Thanks


texts: What could justify a non explicit - non atomic primary key

texts:
I'm working as a "guest" dev at a random company.

I'm working with a table that does not have a primary key by definition(Oracle).

The column named "SOMETHING_ID" is varchar2(10) and constructed at insert time
by the following operation. And it is used as a primary key and referenced by other tables.

'A_STRING' || LPAD(SEQUENCE_NAME.NEXTVAL, 7, '0')

Some examples of the primary key data are

CH-00004321
CH-00004322
CH-00004323

My questions are,

first, in what circumstances would it be justified to use a primary key that is a combination of the type of the record(let's say, CH for Chinese food, In for Indian and MX for Mexican) and a sequence number ?

Second, in what circumstances would it be justified not to have a primary key defined but you have a column that distinctively distinguish each row ?

Third, is my perception correct that I see the combination of the type string(CH, IN, MX) and a sequence number is against the 1st normalisation ?

Fourth, in the table, there is only one "type string" value (let's say CH).
Is it okay to add that string to the pk while it does not seem necessary since there is only one value ? (The nature of the table does not seem to allow any other value for the type string though unlike my example of food. Perhaps you can see the table name as "CHINESE_FOOD", hence no other type string value would seem logical.)

Note - 1. Actual column, sequence names are replaced with arbitrary names.
2. The aim of this software is prediction of the damages caused by natural disasters.


texts: T-SQL: Convert XML to string without self-closing tags

texts:
We have a weirdo system we're integrating with which consumes XML, but it does not understand self-closing tags, so

<BOOKING>
  <BOOKINGID>56812</BOOKINGID>
  <PERSONCODE />
</BOOKING>

is beyond its capabilities.

If what I have is declare @x xml, how do I convert that @x to string without having self-closing tags:

<BOOKING>
  <BOOKINGID>56812</BOOKINGID>
  <PERSONCODE></PERSONCODE>
</BOOKING>


texts: MSSQL select count entities in date range


texts:
I am about to design a database which is going to run with PostgreSQL. I am used to the magnificent tool called MySQL Workbench for MySQL database. It is useful and it looks good which I kind of expect from a database designing software.

If I am about to learn a new database designing tool, I want it to be the most popular one. Therefore, my question is: what are the most popular tools for designing the database in PostgreSQL?


texts:
I've created a Database maintenance plan for creating a backup set daily.  The Back Up Database Task has three subplans:

Full Backup: Occurs every day at 12am
Differential Backup: Occurs every hour starting at 1:30am
Transaction Log Backup: Occurs every 10 minutes starting at 1:45am

The general idea is that we only have to restore the full backup, then the last viable differential, and then the logs after that.

The issue is that the previous days full backup is not available when using the Restore Database tool in SSMS.  I.E. if I want to go back more than last nights full backup I'm out of luck.
What detail did I miss here?

It might give a hint if I describe how I discovered this issue... I created a database yesterday afternoon and worked on it into the evening.  Today I realized that I'd like to restore back to a point about an hour after I created the database to find that I could only go back to the full backup that was created at midnight (see below for configs if you think that will help...).

Further configurations below:

Full backup settings

Connection: Local server connection
Backup type: Full
Database(s): All databases
Backup set will expire: (not selected)
Backup to: Disk
Backup databases across one or more files: (not selected)
Create a backup file for every database: (selected)
Create a sub-directory for each database: (checked)
Folder: \backups\sql
Backup file extension: bak
Verify backup integrity: (checked)
Set backup compression: Use the default server setting

Differential backup settings

All is the same as full except that type is differential

Transaction Log backup settings

All is the same as full except that type is Transaction log and backup file extension is "trn."


texts: Efficient locking for a conditional insert inside a larger transaction

texts:
I have a script I'm using to merge incoming data into a table that includes a conditional insert to capture some related data.
For example, where [#Records] contains the incoming data:
BEGIN TRANSACTION

INSERT INTO [Batch] (Id)
SELECT DISTINCT BatchId
FROM [#Records]
WHERE BatchId NOT IN (SELECT Id FROM [Batch])

-- Do a MERGE here with lots of records that might take a while

COMMIT TRANSACTION

The problem with this insert is that it may fail if run concurrently (due to a primary key constraint on [Batch]).
The advice I found was that I should be using UPDLOCK and HOLDLOCK to lock the [Batch] table for this insert:
INSERT INTO [Batch] (Id)
SELECT DISTINCT BatchId
FROM [#Records]
WHERE Id NOT IN (SELECT Id FROM [Batch] WITH (UPDLOCK, HOLDLOCK))

It also suggested that I wrap that insert in its own transaction, however, irrespective of whether I use a "nested" transaction here or not, my understanding is that the lock on [Batch] will be held until the end of the "outer" transaction. Is this correct?
As the other statements in the transaction may take a while I'd like to avoid the [Batch] table being locked until the end.
What is the most efficient way to do this?


texts: Trying to get multiple mysql instances running on Ubuntu dev box

texts:
I'm trying to get multiple instances of mysql running on a dev box for some testing.  This guy is running ubuntu so the setup's a little different than our production setups.

I'd like to find the easiest way to basically sudo service start   and have it use a different .cnf init file (other than the distro default location of /etc/mysql/my.cnf

Any ideas?


texts: Help designing tables for storing lab test results: values include numbers, number

texts:
We're using MySQL 5.5 and attempting to design tables to store the results from tests run in a lab. The results of some of the tests can be either a plain number, e.g. "5", or a range such as "< 5" or "> 25", and this is where we're getting stuck. The < and > values are due to the limits of the sensitivity of the tests; above or below a certain threshold for each test the exact amount can't be detected. Values right at the threshold are possible, so for the example above, both < 5 and 5 would be valid values for that particular test, and we need to be able to distinguish between these values.

There's a set list of tests, so needing to add new ones easily isn't a concern. Our preliminary design has a TestResults table and each test is a column in that. We were thinking of maybe adding a column for the tests that require < and > just to hold those values or possibly having "flag" values in the application code that signals a value is below/above the threshold and having the thresholds in a reference table. Neither of these solutions seems that great though. We don't want to just store the results as strings because we'll need to be able to get statistics, averages, etc. on the results.

Anyway we were hoping someone out there had done something like this and had some ideas on the best way to do it. We've been Googling (e.g. on LIMS) and haven't been able to find anything on database designs, but it seems like it wouldn't be that uncommon of a problem... Any ideas on the most appropriate way to model this type of data would be greatly appreciated.


texts: What SQL execution time should I expect when querying 4k records in a 150M records table?

texts:
We currently have performance issues with a SQL query on RDS Postgres 11.8. Here is the query:
SELECT id FROM answer_submission WHERE student_id = 3983137;

The table "answer_submission" has about 150M records. The query returns about 4k ids.
The query takes ~2s to execute which is problematic for the user experience in our app. (Note that this query is for debug purpose. The real query select most of the columns and thus adding all these columns in the index would make the index heavy)
An analysis of the query confirms that the B-TREE index that we created is correctly used:
Index Scan using answer_submission_student_id on public.answer_submission (cost=0.57..2505.35 rows=842 width=16) (actual time=2.356..2346.005 rows=4604 loops=1)
Output: id
Index Cond: (answer_submission.student_id = 3983137)
Buffers: shared hit=3 read=4392 written=1118
I/O Timings: read=2298.117 write=16.805
Planning Time: 2.370 ms
Execution Time: 2349.484 ms

I understand from this analysis that the bottleneck is the time to read on the disk.
I don't have a critical opinion about this response time. Is it in the range of time that one should expect for querying 4k records in a 150M records table? Or is there an issue somewhere? Do you have any clues on how to improve the execution time?
I already read this question Slow index scans in large table but before implementing this kind of solutions I would like to make sure I can't fix the root cause of this long execution time.
Our server has 64GB of RAM.  The size of the index is ~4Gb and we have other tables with similar index sizes.
This behavior is not an occasional behavior. It can be consistently reproduced at any time. Also there are many records saved at any time in this table.
Thanks for you help


texts: How to query Job Activity Monitor to get a list of all jobs

texts:
I am trying to obtain a list of ALL jobs (SQL Agent jobs). Specifically, I am trying to obtain the ID, name, status, LastRunOutcome, Last Run, Next Run, and most importantly description, owner AND schedule (eg. Every day at 5am) of each job.

I know that you can obtain all of this information through Job Activity Monitor window and then by clicking on each job. But I need to query this information.

My research here , here and here included a few articles that give partial info, but not complete one.

Any help in getting a full list of active and inactive jobs and their info is much appreciated. Thank you.

I have this code so far, which I hope is accurate:

SELECT
     job.job_id,
     notify_level_email,
     name,
     enabled,
     description,
     step_name,
     command,
     server,
     database_name
FROM
    msdb.dbo.sysjobs job
INNER JOIN
    msdb.dbo.sysjobsteps steps
ON
    job.job_id = steps.job_id


texts: Why is a round robin distribution better for a skewed dataset?

texts:
I have a skewed dataset, where most rows fall into the largest 10 values of my best candidate distribution key. My data is made up of two large tables, that only share two keys - my best candiate key, plus one other, but that one is null 80% of the time, so I have discounted it as an option.

Conventional wisdom says that if the data is skewed, I should use a round robin distribution. Looking at the explain plans produced by joins on the tables, I see my candidate column is the shuffle key for the shuffle move. This makes me question if I should change the distribution from round robin, to hash distributed, saving the time it takes to move data on every execution.

Is my logic correct? I feel like this is against the conventional wisdom when working with distributed sql. I don't expect any queries where this join isn't required, so that may be where others would see the benefit.


texts:
I have a problem with a SPFILE as shown below.How can I get rid of that problem?Any suggestions?

 SQL> startup
 ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
 ORACLE instance started.

 Total System Global Area 1068937216 bytes
 Fixed Size                  2182592 bytes
 Variable Size             784335424 bytes
 Database Buffers          276824064 bytes
 Redo Buffers                5595136 bytes
 Database mounted.
 Database opened.
 SQL> alter system reset log_archive_start scope=spfile sid='*';
 alter system reset log_archive_start scope=spfile sid='*'
 *
 ERROR at line 1:
 ORA-32010: cannot find entry to delete in SPFILE


texts: Are there ways to only replace SQL Server stored procedures when the definition has changed?

texts:
I know the following two patterns to write scripts for creating stored procedures, which can be executed repeatedly without throwing errors.
if object_id('my_proc') > 0 drop procedure my_proc
go
create procedure dbo.my_proc as Print 'This is not a dummy';

and the other which preserves permissions
if object_id('my_proc') is null
    EXEC ('create procedure dbo.my_proc as Print ''This is a dummy''');
go
ALTER PROCEDURE dbo.my_proc as Print 'This is not a dummy';

I guess when the procedure exists and its hash code is the same as that of the new version, than there would be no need to drop and recreate or alter the procedure.
My problem is that HashBytes is limited to a maximum of 8000 bytes and I can't use it generally like
if object_id('my_proc') is null
    EXEC ('create procedure dbo.my_proc as Print ''This is a dummy''');
go
if object_Id('my_proc') > 0
and
(
Select HashBytes('MD5',definition) MD5
from sys.sql_modules m
join sysobjects o on o.id = m.object_id
where o.name = 'my_proc'
) <> 0x9028A1B9D93AC7592EC939CCABF9D3DE
begin
    print 'definition has changed';
    EXEC ('ALTER PROCEDURE dbo.my_proc as Print ''This is not a dummy''');
end

For procedures whose definition are longer than 4000 Characters. Any proposals to handle these cases in a similar way?
Edit:
It is not only that I want to avoid flushing of cached plans.
I also have to cope with different customers having different versions of a stored procedure where I only want to replace one of these variants by a newer version.


texts: Postgresql 10.x installer configures password wrong? (Windows)

texts:
I am new to PostgreSQL, but I just installed 10.x and was asked to type a root/admin password which I could  not leave empty. I hence entered a password I know very well since i only intend for this to be a localhost database.

But when prompted by 3rd party application which uses PostgresSQL to login I only know password - and I can not login correctly.

Being optimistic I tried to start pgbench.exe from commandline which asks for password - but it says that password authentication failed for my name.

Is here anyway to reset password and setup a proper user/password account or somehow fix the situation? And maybe some kind of frontend tool I can use for testing? I have searched for pg_hba file, but I only have a "sample" file.

I have installed into path D:\databases\Postgresql\10


texts: DB Permission: User can drop only their created db's?

texts: I want to set permission against a user, which can create database and drop on their created databases neither any database. Any idea/suggestion ?


texts: Get a user's rank from an equation, and then store it in the table

texts:
I'm working on a project for my database class, and I have a table called "accounts". It looks like
What I want to do is have a stored procedure where it calculates the new ranks for each account. The ranks will be based off a value (higher the better) that will be computed with an equation I am working on. How would I be able to get the rank, and store the rank in my table?
Ex: Account_id||Games_won||Games_lost||(calculated value) not in table||Account_rank
         1        200           157              1856                    1

EDIT: Here is my CREATE TABLE statement:
CREATE TABLE IF NOT EXISTS league_of_legends.accounts (
account_id INT NOT NULL AUTO_INCREMENT,
account_name VARCHAR(45) NOT NULL,
account_password VARCHAR(45) NOT NULL,
subscriber_first_name VARCHAR(45) NOT NULL,
subscriber_last_name VARCHAR(45) NOT NULL,
subscriber_email_address VARCHAR(45) NOT NULL,
subscriber_dob DATE NOT NULL,
server VARCHAR(45) NOT NULL,
account_balance INT NULL,
games_won INT NULL,
games_lost INT NULL,
account_rank INT NULL,
PRIMARY KEY (account_id, account_name, subscriber_email_address));
My INSERT sample statements:
INSERT INTO accounts (account_id, account_name, account_password, subscriber_first_name, subscriber_last_name, subscriber_email_address, subscriber_dob, server, account_balance, games_won, games_lost, account_rank)
VALUES (DEFAULT, 'Bwomp', 'asdfqwer', 'Nile', 'Williams', 'getrekt@gmail.com', '2003-10-09', 'NA', 1250, 100, 100, NULL),
(DEFAULT, 'Dirty Dan', '1234567890', 'Daniel', 'Brown', 'awovuca-1084@yopmail.com', '1990-07-16', 'EU', 0, 3, 20, NULL),
(DEFAULT, 'Alfy', 'doodcool', 'Alfonso', 'Riggleman', 'AlfonsoRRiggleman@armyspy.com', '1992-12-27', 'KR', 50, 400, 410, NULL),
(DEFAULT, 'Justaguy1', 'retrodonkey', 'Nick', 'Chetta', 'NicholasSMaine@jourrapide.com', '1996-06-28', 'NA', 3, 0, 5, NULL);
Sample equation:
(Games_won / total_games) * games_won = value


texts:
There is a table in my database that starts with the @ symbol.  I am able to query it successfully using the following query:

select * from [@tablename]

This format works in SSMS and Crystal Reports.

However, I have a C# application where the same query give an 'invalid object name' error on the table name.

Any suggestions?


texts: Suppress display in a column if the value is equal to the value in another column

texts:
I have 2 date columns that are displayed side-by-side. I have some dates that are equal. I do not want to display the "equal" dates/duplicate date values in one of the columns.

Example:

    column1     column2
    1-1-2014    1-1-2014

I want to display:

    column1     column2
    1-1-2014

How do I achieve that?


texts: How to create a trigger that update the date and time field and modify of the row

texts:
I want to create trigger that will update the record. The situation is like:

I have one column named CheckInDateTime and one more named Status.  If the administrator is not able to update the Status within 30 minutes of CheckInDateTime I want to update the Status column of that particular row.

How can I update that particular row?


texts: Oracle 11g alternative options for oracle 12c with function

texts:
I am using (with function) in Oracle 12c. in 11g this is not available. Is there any other way we can implement with function in 11g?

with
  function x(p_NUM in number) return number
  is
    n number;
  begin
    SELECT 1 INTO N FROM DUAL;
    --
    return N;
    --
  end ;
  --
  function Y(p_NUM in number) return number
  is
    N1 NUMBER;
  begin
    SELECT 2 INTO N1 FROM DUAL;
    --
    return N1;
    --
  end ;
--
select X(1), Y(1)
from dual;


texts:
I'm working with a vendor with the arrangement that they provide the core application, and I can build my own extensions as long as I don't modify the core application.  It's built in ColdFusion connecting to a SQL Server 2005 database.

Some of the reports I've built depend on views using functions computed from the core tables, and the reports are getting very slow as the tables get larger.  To speed up the reports, I want to use indexed views.  But after creating an indexed view in my test environment, the core application could no longer insert into the core tables (it returned an error message that ARITHABORT is required to be ON when using indexed views).

So it seems that in order to use indexed views, I need to have the core application SET ARITHABORT ON whenever inserting/updating the core tables.  I ran this in my test environment:

ALTER DATABASE MyDatabase SET ARITHABORT ON;

and it seems to work fine.  But my vendor says since the application has thousands of queries, there could be a risk that this setting could break one of these queries, and if we have some future unexpected database issue they would insist I restore the default setting.

Are there actual queries that would be broken by SET ARITHABORT ON?  Is there any situation where it would be better to keep it OFF?

TL;DR For my new indexed views to work I need to set ARITHABORT ON for the whole database, but my vendor warns it will be at my own risk.  Is there actually a risk?


texts: How much memory are used by perticular session during run time in MySQL 5.1 community version?

texts:
Is it possible to find out how much memory is used by particular session?A user was asked me how much memory is utilized by particular session running in MySQLl 5.1 community version server.
Kindly provide the solutions thanx in advance.


texts: Combine column from multiple rows into single row with id


texts: Would using varchar(31) be bad compared to varchar(32)?

texts: I wonder whether using varchar(word*n) would be more efficient compared to using varchar(word*n-1). If yes, why? A 64 bit computer word equals 8.


texts: SQL Server 2008: The database [dbName] is not accessible

texts:
I got this error from another person and I've run out of ideas. I can access this database without any problems, the database is in MULTI_USER mode, the person receiving the error is a user, etc.

The database is READ_ONLY and the user in question has previously used this database and only has database read permissions, so there's no chance of settings being tampered with.

Yesterday, I added a few functions and had to set to SINGLE_USER and READ_WRITE, but I've triple checked that the db has been set back to MULTI_USER. I can't decide if that's coincidence or causality. Either way, I'm at a dead end.

Here's the error report:

The database [dbName] is not accessible. (Object Explorer)

----------------------------------------
Program Location:

    at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.DatabaseNavigableItem.get_CanGetChildren()
    at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.RequestChildren(IGetChildrenRequest Request)
    at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ExplorerHierarchyNode.BuildChildren(WaitHandle quitEvent)

Any experience with this kind of error?


texts: Create and use Fulltext Index in a Stored Procedure

texts:
I want to create and use a fulltext index within a single procedure. For example:

CREATE PROC [dbo].[dbp_PCL_to_Demo_Matching_Mason] AS

--IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = OBJECT_ID('Medicaid_State_Claims'))
    CREATE FULLTEXT INDEX ON dbo.Medicaid_State_Claims(Scrubbed_Account_Number)
      KEY INDEX nci_Seq_No;

select * from dbo.Medicaid_State_Claims
  where CONTAINS(Scrubbed_Account_Number, 'blahblah');

However, this fails with the error message:

  Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'dbo.Medicaid_State_Claims' because it is not full-text indexed.

Uncommenting the "IF NOT EXISTS" condition makes no difference. However, if the index already exists and I uncomment that line and create the stored procedure, it works. But I'd rather not create it ahead of time, I want to create it within the stored procedure. How can I create the fulltext index and use it in the same stored procedure?


texts: Identify data in PostgreSQL database in violation of foreign keys

texts:
Database PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit - this is on an Amazon AWS instance.

I recently identified some orphan data in one of the databases I look after when it caused a cron job to fail - this orphan data however was in violation of a foreign key constraint. After digging through the logs I found that these little queries had been ran (sadly I don't know by who - I presume their reason was to get around some triggers that discourage mass-deleting):

SET session_replication_role = replica;
delete from bmis.companies where company_id = 38;
SET session_replication_role = DEFAULT;

The data stored in bmis.companies is the absolute top-level data in this system - everything stems from it. I've manually been through the tables and ran deletes that basically boil down to this pseudo code:

delete from
    schema.table
where
    not exists(
        select
            *
        from
            schema.parent_table
        where
            parent_table.id = table.parent_id
    );

However I'm only human and it's entirely possible that I may have missed something - there are many tables in this system. I'm wondering if there's any reasonably easy-to-implement way of identifying data already inside the database that is in violation of the foreign keys, or even just identifying tables that contain this data?


texts: Support both stored procedures and functions with same TSQL code base?

texts:
We have a need for both Microsoft SQL Server 2008 functions (ITVFs) and stored procedures to provide the same interface and functionality to end-user reporting frontends.

Given the following constraints:

Functions may not call stored procedures.
Stored procedures may call functions.

does putting all the TSQL source code into functions and using stored procedures as wrappers around calls to the functions makes sense?

What other options (if any) exist to support both functions and stored procedures with the same TSQL codebase?

For the curious....

Frontend #1: Microsoft Access 2010 SP1 (version 14.0.6024.1000)

Users use Linked Tables > Views > ITVF's
This approach avoids necessity for developing frontend report screens/code
IT staff plan to sunset this environment in 2014-15 or so
Linked tables may access views but not functions or stored procedures
Views, in turn, may access functions but not stored procedures
Would still like to keep functions around for ad hoc queries even after sunsetting this frontend...though this is a would-be-nice not a need

Frontend #2: ASP.NET 4.0 web app

To-be-developed by in-house IT staff
IT staff policy requires .NET web apps to call only stored procedures

Backend: Microsoft SQL Server 2008 SP3 (version 10.0.5500...will likely upgrade to Microsoft SQL Server 2012 sometime in 2013)

UPDATE: to clarify, this represents a reporting app, so really only need read-only access, other than the ability to create incidental temp tables, table variables, and so forth.


texts: SQL Insert Into New Table Or Else Insert Overwrite Into Existing Table

texts:
What is the SQL to do the following in SQL Server:

I want to take rows from tbl_A and insert the distinct grouping of them into tbl_B.

If tbl_B doesn't yet exist, I want tbl_B to be created exactly like tbl_A, but receive only the distinct grouping of tbl_A's rows.

If tbl_B already exists, I still want the same data inserted but I want all existing rows in tbl_B to be deleted beforehand.

Of course, I need to do this across multiple tables (about 100).

Also, at the expense of stating the obvious, I do not want to have to specify any column names anywhere. The only parts of the script that should need to change as I do this for each pair of tables is the table names. Thanks!


texts:
I have a doubt regarding SSRS installation. I see two methods on the web.However I am not sure if both lead to same solution.

1)Install SSRS via installer OF SIZE 105 mb.

2)Install Visual Studio Community, then SSDT, which in turn installs SSRS, and post this  install templates via VS extensions.

My question is whether both these methods lead to the same result? If not which is the correct one?

The requirement in m organisation is to develop SSRS reports using TSQL DYNAMIC QUERIES,FUNCTIONS,AND STORED PROCEDURES.

SERVER :SQL SERVER 2019 DEVELOPER EDITION


texts: MS SQL 2017: Generate estimate execution plan take more than 3 min

texts:
Sometimes generate Execution Plan (estimated) for simple query take ~3min
Query:

SELECT MAX(field) FROM table_name
WHERE  Org = x and PartKey = y

The table is columnstore cluster index
SQL Server 2017 Standard edition

Estimated plan

Thank you


texts: Database design and relationships between users and tables

texts:
I am trying to design a database and i am having some questions, hopefully you can guide me for the next steps.
Setup
My stack consists of Node.js in the backend and my test DB is in Postgresql but i would also consider MongoDB if needed.

Concept
I am having the following entities with these relationships (As i imagine them in practice):

User (Customer)

username
email
password
role ENUM
PROJECTS[ ]

Project

description
CAMPAIGNS[ ]

Campaign

description
EQUIPMENT MEASUREMENTS[ ]

Measurements (Raw Data 80000 - 200000 rows each table with 20-50 cols, ideally no ID usage, maybe an index)

several different columns

The way i am thinking above is as follows.if a User is not an Admin, then the User should have some Projects that should be able to see via the API, of course if a User is an Admin as a Role then he/she should be able to pick whatever PROJECT-CAMPAIGN-MEASUREMENT.

Ideas

The first idea i have but seems to be error prone in the backend API for several reasons would be the following. The Users should live in a fixed table under a database in the cluster, and then i would break the relationships into the following: Project --> Database || Campaign --> Schema || Measurements --> Table.
More specifically, i was thinking to create these types of relationships by adding strings in the arrays above as in names to reuse in the API, so eventually, i would dynamically use queries that change the PG adapter (by passing a different DB name) , the schemas and the tables accordingly.

The other idea, assuming i would use MongoDB, would be to pass references but i am not sure yet how i would do that in practice, since i can see that i can pass ref IDs but in case i want to reference whole collections it would be tricky.

Any ideas would be really helpful, especially to know if i am on the right path or on a completely wrong one.
If you need any more clarifications please feel free to ask me anything.
Thanks in advance.


texts: T-SQL DATEDIFF_BIG - 469ms diff with currentmills and documented start-end constraints incorrect?

texts:
(NB I don't count as a db admin... I'm just using one, so please assume zero other knowledge)

My reference date is '02/04/2017 18:00:00.124', and for no particular reason, whilst I need the time difference in milliseconds since some reference point, I picked ntp epoch of 1/1/1900 00:00.

I compare the result with the calculation of CurrentMills

SQL gives (code to follow) 3700144800124, Current Mills (with the extra 124ms added by hand) gives 3700144800593; the difference is 469ms.

Why is there a 469ms difference and why does DATEDIFF_BIG work at all when  the documentation says it is limited to ~24 days?

I also did a comparison with Unix epoch conversion, respecting the stated limits (as shown) and the difference is also 469ms s CurrentMills.

Can someone explain these results?

SET DATEFORMAT dmy;
GO
DECLARE @datevar1 datetime2 = '02/04/2017 18:00:00.124';
DECLARE @ntpEpochBase datetime2 = '01/01/1900 00:00:00.000';
SELECT DATEDIFF_BIG(millisecond, @ntpEpochBase, @datevar1);
GO
-- result 3700144800124
DECLARE @datevar1 datetime2 = '02/04/2017 18:00:00.124';
DECLARE @unixEpochBase datetime2 = '01/01/1970 00:00:00.000';
SELECT DATEDIFF_BIG(millisecond, @unixEpochBase, @datevar1);
GO
-- result 1491156000124
DECLARE @datevar1 datetime2 = '02/04/2017 18:00:00.124';
DECLARE @unixEpochBase datetime2 = '01/01/1970 00:00:00.000';
SELECT DATEDIFF_BIG(second, @unixEpochBase, '02/04/2017 00:00:00.000')*1000 + DATEDIFF_BIG(MILLISECOND,'02/04/2017 00:00:00.000','02/04/2017 18:00:00.124');
GO
-- result 1491156000124

Has there been an (un)documented improvement in DATEDIFF_BIG capabiliites?

Setup is:

    Microsoft Sequel Server Express (64 bit), v14.0.2002.14
    Microsoft SQL Server Management Studio                      14.0.17277.0 Microsoft
    Analysis Services Client Tools                      14.0.1016.262 Microsoft Data
    Access Components (MDAC)                        10.0.17134.1 Microsoft MSXML                        3.0
    6.0  Microsoft Internet Explorer                        9.11.17134.0 Microsoft .NET Framework                       4.0.30319.42000 Operating System                        6.3.17134


texts: SSDT schema compare, removing and then renaming stored procedure result is incorrect

texts:
The initial setup contains two scripts:

stored_procedure.sql
stored_procedureV2.sql

What I did is:

Remove stored_procedure.sql
Rename stored_procedureV2.sql to stored_procedure.sql

When I perform a schema compare in SSDT between a .dacpac not containing this change and my project the action it proposes is to just remove stored_procedure.sql and do nothing else, whilst it should remove stored_procedureV2.sql and update stored_procedure.sql to its new content.

As I assume this is where schema compare gets its information:

The .sqlproj of the source:

...
<Build Include="Scripts\stored_procedure.sql">
  <AnsiNulls>On</AnsiNulls>
  <QuotedIdentifier>On</QuotedIdentifier>
</Build>
<Build Include="Scripts\stored_procedureV2.sql">
  <AnsiNulls>On</AnsiNulls>
  <QuotedIdentifier>On</QuotedIdentifier>
</Build>
...

The .sqlproj of the target:

...
<Build Include="Scripts\stored_procedure.sql">
  <AnsiNulls>On</AnsiNulls>
  <QuotedIdentifier>On</QuotedIdentifier>
</Build>
...

Is this a known issue? Do I need to perform this in two steps? Is there a way to make SSDT behave the way I want?


texts: mysql restart won't kill child processes on CentOS

texts:
I have a mysql database running on a CentOS server. I have noticed that, when left to run for several weeks without restarting the service, then when I need to do a restart (/etc/init.d/mysqld restart), the service will display "fail" for both stop and start. It will then refuse to start again, unless I kill -9 the mysqld_safe and mysqld processes.

Why does this happen and what can I do to prevent it?

Using mysql 5.1.66 on Centos 6.3


texts:
SQL-Fiddle

http://sqlfiddle.com/#!9/c82c87b/1

Table Definition

First of all, this is my table:

 CREATE TABLE `stackoverflow` (
`id` VARBINARY( 36 ) NOT NULL COMMENT 'GUID generated by PHP',
`time` TIME NOT NULL COMMENT 'Current time(stamp)',
`type` VARCHAR( 10 ) NOT NULL COMMENT 'start/stop',
`reference` VARBINARY( 36 ) NOT NULL COMMENT 'multiple starts/stops to one reference',
PRIMARY KEY ( `id` )
) ENGINE = MYISAM

Data

This is some example-data:

INSERT INTO `stackoverflow` (
`id` ,
`time` ,
`type` ,
`reference`
)
VALUES
('03bd8e91-b9aa-4d18-be47-9e9cce903cfd', '11:00:00', 'start', '76afe924-08aa-431b-904a-66290c50da6a'),
('ef10860a-7666-4ca0-95b6-79ef2d5b3f75', '11:01:00', 'start', 'fd064ef5-462f-489c-ae14-3cb766eb80c4'),
('9bc72e24-a0d4-43a3-86ab-973c331e2958', '11:02:00', 'stop', 'fd064ef5-462f-489c-ae14-3cb766eb80c4'),
('a245cda3-1196-4dba-832e-0474fd0eb0bf', '11:03:00', 'start', '05324e7b-a358-48bb-9779-08cf60038bb8'),
('488c67e6-c21d-4356-9578-49e857259345', '11:06:00', 'stop', '76afe924-08aa-431b-904a-66290c50da6a'),
('11c0e4ac-e7e9-418a-841f-650ced3e8343', '11:12:00', 'stop', '05324e7b-a358-48bb-9779-08cf60038bb8');

It is basically about timers - every timer (reference) has multiple starts and stops. Every entry is either a start or a stop.

Querying Data

To display these timers in HTML, I need to order the data like this:

SELECT * FROM stackoverflow ORDER BY reference DESC, time ASC;

The result looks like this:

  ef10860a-7666-4ca0-95b6-79ef2d5b3f75  11:01:00    start   fd064ef5-462f-489c-ae14-3cb766eb80c4
  9bc72e24-a0d4-43a3-86ab-973c331e2958  11:02:00    stop    fd064ef5-462f-489c-ae14-3cb766eb80c4
# 03bd8e91-b9aa-4d18-be47-9e9cce903cfd  11:00:00    start   76afe924-08aa-431b-904a-66290c50da6a
# 488c67e6-c21d-4356-9578-49e857259345  11:06:00    stop    76afe924-08aa-431b-904a-66290c50da6a
  a245cda3-1196-4dba-832e-0474fd0eb0bf  11:03:00    start   05324e7b-a358-48bb-9779-08cf60038bb8
  11c0e4ac-e7e9-418a-841f-650ced3e8343  11:12:00    stop    05324e7b-a358-48bb-9779-08cf60038bb8

Questions

How do I get the rows, marked with a # into first position?
I think ordering by reference is wrong, but how do I keep them together? Just ordering by time would produce wrong results. It would be great, if these pairs of references could stay together.
Do I need a virtual table or a sub-query?

Additional information

Meanwhile I fixed this  “‘SQL’ problem” in PHP with multiple separate SQL operations, as detailed below:

get all references

SELECT DISTINCT reference FROM stackoverflow;

for each reference

SELECT * FROM stackoverflow WHERE reference = :reference ORDER BY time;

for each row
-> generate HTML "timer"

Result:

# 03bd8e91-b9aa-4d18-be47-9e9cce903cfd  11:00:00    start   76afe924-08aa-431b-904a-66290c50da6a
# 488c67e6-c21d-4356-9578-49e857259345  11:06:00    stop    76afe924-08aa-431b-904a-66290c50da6a
  ef10860a-7666-4ca0-95b6-79ef2d5b3f75  11:01:00    start   fd064ef5-462f-489c-ae14-3cb766eb80c4
  9bc72e24-a0d4-43a3-86ab-973c331e2958  11:02:00    stop    fd064ef5-462f-489c-ae14-3cb766eb80c4
  a245cda3-1196-4dba-832e-0474fd0eb0bf  11:03:00    start   05324e7b-a358-48bb-9779-08cf60038bb8
  11c0e4ac-e7e9-418a-841f-650ced3e8343  11:12:00    stop    05324e7b-a358-48bb-9779-08cf60038bb8

The order is now as expected - it would still be great if there is a MySQL solution for this. :)

Note: The exact MySQL version is 5.1.61.


texts: Why did my UPDATE statement not complete?

texts:
Running SQL Server 2008; Mgmt Studio 2008. I am working with a MS SQL job that we have created for our MS CRM 4.0 implementation.  For anyone familiar with CRM, this job purges the aSyncOperationBase table nightly to save on DB size.  Here is the job code:

    Begin Transaction T1

Declare @p30Days as DateTime
Declare @p3Years as DateTime

Set @p30Days = DateAdd(d, -30, GetDate())
Set @p3Years = DateAdd(d, -1095, GetDate())

update AsyncOperationBase
set deletionstatecode=2
where deletionstatecode = 0 and
statecode = 3 and
completedon is not null and
completedon < @p30Days
and OperationType <> 10

update AsyncOperationBase
set deletionstatecode=2
where deletionstatecode = 0 and
statecode = 3 and
completedon is not null and
completedon < @p3Years
and OperationType = 10

delete from workflowlogbase
where AsyncOperationid in (select AsyncOperationid from AsyncOperationBase where deletionstatecode=2)

update DuplicateRecordBase
set DeletionStateCode = 2
where asyncoperationid in
(select DuplicateRecordBase.asyncoperationid
    from DuplicateRecordBase
        left join asyncoperationbase on (DuplicateRecordBase.asyncoperationid=asyncoperationbase.asyncoperationid and
        asyncoperationbase.deletionstatecode = 0)
    where asyncoperationbase.asyncoperationid is null)

update BulkDeleteOperationBase
set DeletionStateCode = 2
where asyncoperationid in
(select BulkDeleteOperationBase.asyncoperationid
    from BulkDeleteOperationBase
        left join asyncoperationbase on (BulkDeleteOperationBase.asyncoperationid=asyncoperationbase.asyncoperationid and
        asyncoperationbase.deletionstatecode = 0)
    where asyncoperationbase.asyncoperationid is null)

delete from asyncoperationbase
where deletionstatecode = 2 and
completedon < @p30Days

commit transaction T1

The problem that I recently noticed is that this job, which starts at 4am, runs for at least 4 hours and does not complete.  As I tried run the first update statement against 1 record, the SQL process did not complete and became the lead blocker in the process chain.  Does anyone have any ideas on what could be causing this behavior?  I have looked at the Activity Monitor and there are no other processes which are causing major blockages.


texts:
Is there a way to access statistics about IO operations per second in DB2 9.7? I am not interested in the buffer pool hit ratio, just IOPS.

Storage managers reported us that a few days ago average IOPS is over 5000  (previously it was 300 they said). I just want to track those numbers which storage managers reported us because I do not want to this problem re-occur.


texts: how to delete the file which is open in bcp.exe

texts:
I have run the bcp command in query window to generate the tsv file. In between I cancelled the query. I tried to kill the SPID. But it is killed/rollback.

I tried to delete the tsv file in server. It is throwing error as 'the file cant be deleted because it is open in bcp.exe'. please assist to resolve


texts: Why is MySQL order by performance poor within a single partition of a partitioned table?

texts:
I have to store some sequence numbered data in MySQL. I have about 300,000 data items per day for about a 10 year span. Let's say the table structure is just sequence number (a big int) and data (a varchar). One very common query i'll be making is for all data for a single day, ordered by sequence number.

Storing the data in one table per day works really well. Querying all data from a table takes 0.8 seconds (which is acceptable), and adding order by sequence number takes it up to 1.0 seconds (still acceptable), but this does result in a massive number of tables that i'd rather have less of.

Storing in one table per month (and adding an indexed day number field), the time to get a day's data goes up to 1.6 seconds (not great), but adding partitioning by day number brings it right back down to 0.8.

However... when I add the order by sequence number to the partitioned table query, the time goes up to 2.5 seconds! Does this make sense? I would have thought that since my where clause (day number = X) instantly limits to all the data in a single partition, it should then be about the same performance as one of my original day tables (which it is, until I add the order by clause, then everything goes to hell).

It's like it is doing the order by before checking the partitions or something. Anyone got any ideas? (either "yes, that'll happen, and here's why", or "here's what you need to do to speed it up").

Thanks.


texts: Importing BACPAC file from azure to SQL creating an empty database

texts:
Exported Data-Tier_application from an Azure database to create a BACPAC of it.

  SQL editions I'm trying to use to import the BACPAC:

  SQL Server 2016 express.

  SQL Server 2017 Developer Edition.

I droped some views and procedures to fix error and it fixed everything.

I have now a 3GB BACPAC file and when i'm trying to import it(right click - import data tier application on databases menu from SSMS )  to my SQL Server 2016 or 2017, I receive this error:

with this error message:

  TITLE: Microsoft SQL Server Management Studio

  Could not import package. Warning SQL72012: The object

  [DATABASE_NAME_Data] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

  Warning SQL72012: The object [DATABASE_NAME_Log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

  Error SQL72014: .Net SqlClient Data Provider: Msg
  7609, Level 17, State 5, Line 2 Full-Text Search is not installed, or
  a full-text component cannot be loaded. Error SQL72045: Script
  execution error.  The executed script: CREATE FULLTEXT INDEX ON
  [dbo].[Table]
      ([Nome] LANGUAGE 1033, [field_1] LANGUAGE 1033)
      KEY INDEX [PK_Table]
      ON [table];

it's not possible to have already these DATA and LOG because It's the first time I'm restoring it.

Changing .BACPAC file to .ZIP I could see a DATA folder with a lot of BCP files:

The bacpac file has 3GB.

Other answers from websites just say : "wait until the end" but the export ended 4 days ago.

I have another BACPAC file from a different database and I can import it to sql server normally. I've made the same steps for both BACPAC files when exporting.


texts: How do I design a Dimension and Fact Table from a relational database?

texts:
I'm new to using SQL Server 2008 R2 SSAS.  I have the following tables in my database, that I wish to model using SQL Server Analysis Services:

Table1

ServiceTypeID    | ServiceTypeName
---------------------------------------
   001           |    FLM
   002           |    PM
   004           |    SLM
   005           |    CL
   999           |    OTH

Table 2

BankCode   | BankName
------------------------------------------
BAY        | Bank Of Ayudhya
Brinks     | Brinks rama3
ESB        | Easy Buy
ISB        | Islamic Bank
SCB        | Siam Commercial Bank
SCBT       | Standard Chartered Bank
TCRB       | Thai Credit Retail Bank

Table3 (main table)

TransactionNo            | ServicedDate            | BankCode | ServicedTypeId
------------------------------------------------------------------------------
ATMBK00000000000104212   | 2011-06-09 00:00:00.000 | BAY      |  001
ATMBK00000000000104648   | 2011-06-14 00:00:00.000 | BAY      |  001
ATMBK00000000000105504   | 2011-06-24 00:00:00.000 | BAY      |  001
ATMBK00000000000107397   | 2011-07-14 00:00:00.000 | BAY      |  001
ATMBK00000000000111500   | 2011-08-23 00:00:00.000 | BAY      |  001
ATMBK00000000000115016   | 2011-09-23 00:00:00.000 | BAY      |  001
ATMBK00000000000123117   | 2011-12-14 00:00:00.000 | BAY      |  001

I need a to design a fact table and dimensions to get a report of BankName as a column and ServiceTypeName as row, categorized by month.  I've used the DISTINCT COUNT function on TransactionNo to get the total number of different services provided to each bank. I also don't have a time dimension, which I need.

Can anyone point me in the right direction?


texts: Have aggregate function group results in one row

texts:
I have a table that has the following columns:

debit
credit
payment_method
class

Now I will perform this query:

 select sum(debit - credit), payment_method, class
 from my_table
 group by payment_method, class

And since I have various payment methods and various classes, I will have several rows of the result set.

Now I want to be able to aggregate this result and have the output grouped by class only, for example for a certain class 001 I want to have:
payment_method1, sumofpayment_method1, payment_method2, sumofpayment_method2, payment_method3, sumofpayment_method3 in one row.

Is it possible in Postgres 9.1?

Update:

The table definition is as follows:-

debit numeric(10,2)
credit numeric(10,2)
payment_method varchar(20)
class varchar(25)


texts: plpy.execute('...') statements not in logs

texts:
How can I get the SQL statements executed by plyp into the postgres log files?

CREATE FUNCTION history_monitor() RETURNS trigger
    LANGUAGE plpythonu
    AS $_$
    ...
    plpy.execute('...')
$_$;

I have a function like above which gets executed if data gets inserted into a table.

I can see all statements executed via psycopg2.

How can I see the statements of the python code running in postgres executed by plpy?

Postgres Version: 9.3


texts: how to add an extended events tracking session to SSAS?

texts:
I would prefer to have a script to achieve this, but as I am not so familiar with SSAS I am using the wizard to create an extended events session in SSAS.

The way I am doing it is right clicking on the following place and getting a new session created:

wonderful,

but on the second screen, I pick this default tracking to start with, which is fine, but then I get this nasty error message that I am not aware as how to fix:

There are no valid data storage targets for this session.

I move on, to the events tab and check which events are included

Just to get stuck in the next tab, Data Storage:
which does not allow me to proceed.

How can I get over this

There are no valid data storage targets for this session.

or even more specifically, how can I set up an extended events session to track what is going on inside my ssas?

I would prefer a script, but any ways to see what processes and\or queries are running inside my ssas databases is fine.


texts: Implications of adding a trigger on production database

texts:
I am trying to add a trigger on a production database table which includes millions of rows and is used very heavily. There are around 70k-100k inserts per day.

When I try to add a trigger to this table, SQL Server takes too much time - until I stop the execution or SQL Server throws a deadlock error.

I need to run this trigger on production. How can I add a trigger with minimal impact on running the production database?

The trigger is just inserting a record in a audit table 100% similar to https://www.mssqltips.com/sqlservertip/5909/sql-server-trigger-example/


texts: What is the purpose of the USING parameter in SQL?

texts:
Working with some code on a standard MySQL database, and I came across this statement:

SELECT * FROM foo LEFT JOIN bar USING ('bar_id') WHERE foo_id = 1

What's throwing me is: what is USING for, what does it do?

My first assumption was that it involved working with foreign keys or possibly indexes, but the DB schema has no indications of any foreign keys or indexes, period.
My googlefu has failed me, probably because USING is such a common word in most text on sites out there. So I wonder if the DB gurus hear could shed some light on what this does before I look to modify this and multiple other queries in this code.


texts: Query has different result when run directly compared to when used as a subquery

texts:
I have a query that I will just call $QUERY to describe the problem that I'm seeing.
If I run $QUERY in the database, I get 287 rows. One of the columns is patientuid. If I count up how many rows have patientuid=40634, I see 3 rows.
Separately, I run "select * from ($QUERY) x where patientuid=40634" and I get 7 rows.
That tells me that $QUERY is actually giving me 7 rows where patientuid=40634, but when I run $QUERY, it is only showing me 3 of them. If I subselect the patientuid that I want, it shows me all rows. Hopefully this problem is obvious. The query gives me 3 rows for that patientuid value, but when I use it as a subquery, I get 7 rows. How is it possible for a query to display 3 rows on the screen, but when used as a subquery, it produces 7 rows?
I am using MariaDB RDS on AWS (10.3.23-MariaDB-log). I'm typing the query directly into the console, using the CLI mysql client from an AWS cloud server. My goal is to remove anything that could be causing this problem.
I thought that the database might be flaky, but it isn't. I get very consistent results. It simply filters results when I run $QUERY. But, when I make $QUERY a subselect and get specific patientuid values from it, I get all the results.
The query, which I doubt will be helpful for this problem, is:
select fu.patientuid patientuid, int_date, ini_sbp, fu_date, fu_sbp
from (
  select patientuid, bpdate fu_date, min(sbp) fu_sbp
  from bps
  where bpdate between '2020-03-31' and '2021-03-31'
  and sbp>0
  group by patientuid, bpdate
) fu
join (
  select patientuid, ini_date, floor(bp/1000) ini_sbp, int_date, int_cat
  from (
    select intdb.patientuid patientuid, intdb.ini_date ini_date, min(1000*bps.sbp+bps.dbp) bp, intdb.int_date, intdb.int_cat
    from (
      select intd.patientuid patientuid, max(bpdate) ini_date, int_date, int_cat
      from (
        select mcd.patientuid patientuid, mcd.startdate int_date, mcd.cat int_cat
        from (
          select m.patientuid patientuid, min(m.startdate) startdate, mc.cat cat
          from medications m
          join pub.medcats mc
          on m.generic=mc.generic
          and m.startdate between '2018-03-31' and '2021-03-31'
          join pub.cats c
          on mc.cat=c.id
          and c.parent in ('ah','ccb','d')
          and c.id not in ('ccb','d')
          group by m.patientuid, mc.cat
        ) mcd
        join (
          select m.patientuid patientuid, startdate, mc.cat cat
          from medications m
          join pub.medcats mc
          on m.generic=mc.generic
          and m.startdate between '2018-03-31' and '2021-03-31'
          join pub.cats c
          on mc.cat=c.id
          and c.parent in ('ah','ccb','d')
          and c.id not in ('ccb','d')
          join pub.maxdoses md
          on m.generic=md.generic
          and m.dailydose>=md.stdose
          group by m.patientuid, mc.cat
        ) msd
        on mcd.patientuid=msd.patientuid
        and mcd.cat=msd.cat
        and mcd.startdate=msd.startdate
      ) intd
      join (
        select patientuid, bpdate
        from bps
        where bpdate between '2018-03-31' and '2021-03-31'
        group by patientuid, bpdate
      ) bpd
      on intd.patientuid=bpd.patientuid
      and bpd.bpdate<=intd.int_date
      group by intd.patientuid, intd.int_date, intd.int_cat
    ) intdb
    join bps
    on intdb.patientuid=bps.patientuid
    and intdb.ini_date=bps.bpdate
    group by intdb.patientuid, intdb.ini_date, intdb.int_date, intdb.int_cat
  ) intdbp
  where bp>=140000
  or (bp%1000)>=90
) iniint
on fu.patientuid=iniint.patientuid
and datediff(fu.fu_date,iniint.int_date) between 7 and 180
where fu.patientuid between 40583 and 42920


texts: SQL Server Express 2005 DB over size but data < 1GB

texts:
I have a SQL Express 2005 instance which is not working correctly due to it hitting the 4GB DB size limit. I have looked at the data in the DB and cannot see how this is possible - the data is less than 1GB.

I checked with this query:

SELECT
  sum(p.rows) as RowCounts,
  sum(a.total_pages) as TotalPages,
  sum(a.used_pages) as UsedPages,
  sum(a.data_pages) as DataPages,
  (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
  (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
  (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM
  sys.tables t
INNER JOIN
  sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
  sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
  sys.allocation_units a ON p.partition_id = a.container_id
WHERE
  t.NAME NOT LIKE 'dt%' AND
  i.OBJECT_ID > 255 AND
  i.index_id <= 1
GROUP BY
  t.NAME, i.object_id, i.index_id, i.name
ORDER BY
  sum(a.total_pages) DESC

The output of this query shows the following table sizes:

105370,32028,32015,839,250,250,6
601107,11866,11857,11811,92,92,92
636064,11666,11088,10150,91,86,79
350950,3458,3377,3360,27,26,26
146825,2001,1980,1971,15,15,15
118641,1881,1837,1829,14,14,14
68834,1778,1756,1747,13,13,13
79954,1107,1051,570,8,8,4
87993,1025,1006,1001,8,7,7
63738,801,793,789,6,6,6
591,719,718,3,5,5,0
29614,404,395,158,3,3,1
43082,401,378,376,3,2,2
13314,275,230,86,2,1,0
20982,218,170,157,1,1,1
476,86,80,3,0,0,0
2382,82,66,20,0,0,0
634,74,60,53,0,0,0

There are more tables but their size is all zero from here downwards.

Where is the additional space going? I would delete some records but it seems there is a large amount of space being lost somewhere. I have tried compacting and shrinking the DB to no avail.

EDIT: sp_spaceused reports the following:

Reserved    Data        Index size  Unused
4194152 KB  552584 KB   3523920 KB  117648 KB

So it appears the additional space is being used by the index?


texts: Restarting SSRS using SQL Agent & Powershell

texts:
I'd like to create a SQL agent job to restart SSRS each day.

In the past i've used a Powershell script executed by a Scheduled task taken from here. This has worked fine, but I would prefer it to be a single job in SQL agent to simplify deployment and admin maintenance.

I have taken the powershell code and added a job with a single step.

Stop-Service "SQL Server Reporting Services (MSSQLSERVER)"
Start-Service "SQL Server Reporting Services (MSSQLSERVER)"
$wc = New-Object system.net.webClient
$cred = [System.Net.CredentialCache]::DefaultNetworkCredentials
$wc.Credentials = $cred
$src = $wc.DownloadString("http://localhost/Reports/Pages/Folder.aspx")

When I run it I get the following error.

The job script encountered the following errors. These errors did not stop the script:
A job step received an error at line 1 in a PowerShell script. The corresponding line is 'Stop-Service "SQL Server Reporting Services (MSSQLSERVER)"'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Service 'SQL Server Reporting Services (MSSQLSERVER) (ReportServer)' cannot be stopped due to the following error: Cannot open ReportServer service on computer '.'.
Cannot open ReportServer service on computer '.'.
Access is denied.

And a similar error for the start service command.

My SQL agent is running with local admin privileges. So I assume is has sufficient privs to do this.

What am I missing? Is there some technical limitation in SQL Agent stopping this from working?


texts:
I need guideline to setup DBMS(MYSQL or SQL Server 2008). The database size is expected upto 80-100GB in next few months. One main table will contain textual content like essays. Can MySQL handle such big size database and can  full text search really work for me for such big database. Should I consider MySQL or any other?

Thanks


texts: How can I automatically have a node become primary in an Availability Group?

texts:
In my environment, I have Db01 and Db02 setup with SQL 2012 AlwaysOn. Db01 is the preferred master as that is where all of the backup jobs reside. Typically, the only time that Db01 is unavailable is during maintenance period of installing Windows Update.

Is there a way to always have Db01 be the primary when it is a member of the cluster? Currently, when Db01 rejoins the cluster after a reboot, it is marked as the secondary, since Db02 has become the primary. I would like to see Db01 join the cluster and then automatically (or via a command executed on startup) be designated primary.

Db01 and Db02 are both set to SYNCHRONOUS_COMMIT and Automatic Failover Mode


texts: Select to get a value from a id combined with a max value

texts:
I have a table like this:

idAction | numberLine | action
   1     |     1      |   1
   1     |     2      |   2
   1     |     3      |   7
   1     |     4      |   6
   2     |     1      |   2
   1     |     2      |   5
   1     |     3      |   3

I need to select for each idAction where the numberLine is MAX the action for that row. So in this example I should get two rows:

idAction | numberLine | action
   1     |     4      |   6
   2     |     3      |   3

I have tried with Select idAction, MAX(numberLine), action from table Group by idAction. But it says to put action in the group by, but if I do that it gets me all the rows.


texts: MySQL Master not sending events to another Master

texts:
We have Master-Master Replication and it is configured correctly as it transmit data for certain period of time and after that it does not send further events till we stop/start slave on both nodes.

When I run Show Slave Status\G, it displays

Seconds_Behind_Master as 0
Slave_SQL_Running as Yes
Slave_IO_Running as Yes
server_id is also different for both nodes.

My Server Info

MySQL Version is 5.6.23
Windows Server 2012

Any help would be great.


texts: Why does WAL on postgresql takes so long to replay?

texts:
Context:

I'm running postgresql 11 using the official postgresql docker image. The configuration is the default one without any tweak. Sometimes, my docker server crashes and kills all the container with it. As a result the postgresql gets stopped in a dirty way and postgresql has to replay the WALs. It takes more than 1h to replay the logs thought the server is mostly idling. I mean when the server crashed it hardly had any writes in the logs. And when I check in pg_wal  I only see 65mb of data in the directory so it shouldn't take so much time to replay.

Here's some configurations:

max_wal_size: 1gb
min_wal_size: 80gb
checkpoint_timeout: 3min

So how come it take so long to restart? I have a different server with much less databases but similar configurations. Both are mostly idle all the time but the other one quickly restart while they have the same configuration.


texts:
I want to redesign a part of my system. I got the following objects:

Site:
id
Name

Site Version:
id
idSite
Name

Task:
id
idSite
Name

Test:
id
idTask
Name

TestResult:
idTest
idSiteVersion
dateStarted
dateEnded
result

Two problems:

You can see that I have a circle in the definitions. The problem is, I got Tasks defined for Sites, and also through the TestResult > SiteVersion > Site I can get back to Site again.
The main query to answer is, for a given Site, show all SiteVersion and their compatibility to Tasks defined for them.


texts: MS SQL Server 2008 R2 duplicate key row in object 'sys.syscommittab'

texts:
I have a server that is failing to backup due to an error with the syscommittab and have gone through everything in this question: SQL Server 2008 unable to backup due to Failed to flush the commit table to disk in dbid 11 due to error 2601 but am still having the same issue

Running a DBCC CHECKDB returns the following:

Msg 2601, Level 14, State 1, Procedure sp_flush_commit_table, Line 15
    Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'. The duplicate key value is (491219680).
Msg 3999, Level 17, State 1, Line 1
    Failed to flush the commit table to disk in dbid 9 due to error 2601. Check the errorlog for more information.

Change tracking is enabled on this database with retention of 30 days and auto-cleanup on but it is not cleaning any records since this error occurred several months ago

I've seen suggestions to run sp_flush_commit_table_on_demand but that does not exist on this installation (SQL Server 2008 R2 SP3 - now, was SP1 but installed latest SP to address known issues with change tracking related to this error)

I can see the problem rows by running:

select * FROM sys.dm_tran_commit_table where xdes_id='491219680'

Both of them are from when the problem occurred (within 20 mins of each other) and are well outside of the retention period.  I'm wary of trying to manually delete these but can't really find anything to confirm what the risks of doing this is

Any suggestions on next steps would be appreciated


texts: How to use GROUP_CONCAT for a column and COUNT(*)?

texts:
I get the ids of a query by GROUP_CONCAT.
SELECT GROUP_CONCAT(ArticleID) FROM TagMap WHERE Tag IN(...)

How can I merge this query with a COUNT(*) group by
SELECT ArticleID,COUNT(*) FROM TagMap WHERE Tag IN(...) GROUP BY ArticleID

to get GROUP_CONCAT(ArticleID) with order of COUNT(*)?
In other words, in the first query, I want to make GROUP_CONCAT(ArticleID) order by the number of tags found for each ArticleID.


texts: What is the best practice for the msdb database owner?

texts:
In the course of reading up on SQL Server security best practices, I learned that databases should generally be owned by a dedicated, low-privileged login (sometimes a separate one for each DB or each application), per these articles.

I've also learned that you can't change the owner of master, model, and tempdb, but you can change the owner of msdb.  My question is: should you?

The main concern with high-privileged database owners is that the TRUSTWORTHY setting could be enabled for that DB, and msdb has to have TRUSTWORTHY enabled.  It seems like that's a good argument for changing the owner of msdb to a low-privileged account, but I don't know if that could cause problems with instance functionality, and I haven't been able to find any articles or whitepapers even discussing the topic.  The closest I've seen is this one, which stops short of making any recommendations.

Can anyone provide any insight as to the possible effects of changing the owner of msdb?


texts: Query: How to get both Question and multi Answers in one query

texts:
For this question, Question and Answer are just my example tables for one-many relationship. For example: Question(QuestionID, QuestionTitle, QuestionContent) and Answer(AnswerID, AnswerContent, QuestionID)

In one query, I want to get one question and all answers of this question by QuestionID:

How to write a query like that
In case that, I "think" will have each rows is an answer with additional fields of question. This will make duplication because all those answers have same question content --> cost memory to store additional data. So compare to this: get QuestionID and query get Question. get QuestionID and query to get all Answers. Does this way lost performance than first approach but gain more memory ?

Here is my script:

DROP TABLE IF EXISTS `Question` ;

CREATE TABLE IF NOT EXISTS `Question` (
  `QuestionID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `QuestionContent` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`QuestionID`),
ENGINE = InnoDB;

DROP TABLE IF EXISTS `Answer` ;

CREATE TABLE IF NOT EXISTS `Answer` (
  `AnswerID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `QuestionID` INT UNSIGNED NOT NULL,
  `ReplyContent` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`AnswerID`),
  INDEX `fk_Answer_Question1_idx` (`QuestionID` ASC),
  CONSTRAINT `fk_Answer_Question1`
    FOREIGN KEY (`QuestionID`)
    REFERENCES `Question` (`QuestionID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
ENGINE = InnoDB;

Thanks :)


texts:
I would like to know if SQL Server 2008 R2 has a default logging scheme for SELECT statements (or any other T-SQL for that matter).

If yes, where can I see it? If not, how could I set it up?


texts:
I'm new to SQL Server TDE and have a question.

I have two VM's (VM1 = Source and VM2 = Target).  On the source machine, I executed all the required steps to enable my Test database as well as backing up the certificate and private key.  No issues here.

On the target machine, i executed the following t-sql commands

CREATE MASTER KEY ENCRYPTION

CREATE CERTIFICATE
FROM FILE
WITH PRIVATE KEY(
FILE
DECRYPTION BY PASSWORD
);

without any issue and successfully restored my Test TDE database.

On the source machine, I had to run this command

CREATE DATABASE ENCRYPTION KEY

but I didn't have to when I did the restore on the target machine.  Here's my question:  Is my Test database on the target machine TDE enabled?


texts:
Msg 229, Level 14, State 5, Line 1
The INSERT permission was denied on the object 'object_name', database 'database_2', schema 'dbo'.

Above is the error. I have a linked server set up (set up in prod which links to test server). I want to insert a data from prod to test. INSERT statement is working on database 1 but error above occurs on database 2. Is there a way to check the permissions of local user (prod) to linked server objects (test)? Where should I check for the missing set up?


texts: Record the duration of a stored procedure with multiple parameters

texts:
Is there a way to automatically record the duration and row count of the results of a stored procedure execution and save the plan?

I have a procedure i'm troubleshooting for possible parameter sniffing or an issue with the app server. I'm running it in SSMS with different parameters, but not getting the results i want and it's taking too long.

There is one parameter that will change and I can build a list of the parameters I want to pass it.

My question is there a way to have SSMS automate the execution of a procedure with a parameter changing every time and then record the duration and row count. and possibly save the execution plan.

Basically if I have a procedure with three parameters, I want to change the second one every time it executes from a list I create, automate the execution of a few hundred executions with a different parameter each time and record the duration and row count of each execution. And possibly save the plan.


texts: Is DROP/ADD UNIQUE constraint in ALTER TABLE atomic?

texts:
I have a UNIQUE constraint of a number of fields on a table. Image that I want to change the fields in this constraint (remove one field). Normally I would do something like:

DROP INDEX unique_name ON table_name;
CREATE UNIQUE INDEX unique_name ON table_name (field1, field2, ...);

But these are two statements, and after executing the first one there will be no UNIQUE constraint anymore, so any INSERT may potentially break the constraint, until new UNIQUE is created.

There is another way to do that:

ALTER TABLE table_name
DROP INDEX unique_name,
ADD CONSTRAINT unique_name UNIQUE (field1, field2, ...);

This is one statement. The questions is: will this statement perform an atomic operation? By atomic I mean that there is no time window when there is no UNIQUE constraint at all.


texts: Can I do VACUUM FULL to pg_largeobject table?

texts:
I have two tables (table1, table2) in a Postgres 9.1 database. Both having oid type. Each table 1 million records. And pg_largeobject table size is around 40GB. I have removed 0.9 million records from each table, and executed the below command.

vacuum full analyze table1;
vacuum full analyze table2;

Still no change in pg_largeobject table size (auto vacuum is enabled)

Do I need to execute above command to pg_largeobject table too? Will it impact anything?


texts: Clarification on SQL Server 2016 Always Encrypted

texts:
We are planning out a move from SQL Server 2008 to SQL Server 2016.  One of the key requirements for us is to have encryption at rest at a minimum.  We have been considering SQL Server Enterprise as it includes TDE though now with SQL Server 2016 Standard including the "Always Encrypted" feature set we are looking into this as an alternative to Enterprise (saves us some money).  Though we are having a very difficult time locating a source which clarifies what exactly AE encompasses in SQL Server. Thus far in our research it appears that AE is specific to column level encryption from the client to the DB utilizing certs though we've found nothing that mentions AE can be used in a similar fashion to TDE and encrypt the entire DB.

Can someone please shed some light on this for us?


texts:
I inherited an Oracle DB on Linux Redhat from someone who left the company and didn't tell anyone what the passwords were to the various accounts on the database itself (not Linux), including what he had the oracle DB sys account set to.

However currently a production app is running on the server, so I don't want to delete the password file to reset it, as that would disrupt the production app.

I have complete (root) acess to the Linux server; the Linux server password is not the issue. I know I've done this once in the past, but I can't recall how I gained control, and Google is pointing to either deleting the file, or changing the password via another user, both of which aren't really option.


texts: Why this statement invalid: `VALUES ( CURRENT PACKAGESET )`?

texts:
I'm on db2 luw 10.5.

db2 special registers can be set with:

SET CURRENT ...

and retrieved with:

VALUES CURRENT ...

with the exception of CURRENT PACKAGESET. For some reason the following statement fails:

VALUES CURRENT PACKAGESET
SQL0104N  An unexpected token "PACKAGESET" was found following "VALUES CURRENT
".  Expected tokens may include:  "END-OF-STATEMENT".  SQLSTATE=42601

Why so?


texts:
I need to know if there is a way in SQL*Plus to display the password of a certain username?

At my job, sometimes the developers ask me what happened to their account, and what their password is. When I try to display the username and password, the password shows as blank.

Could you please help?


texts: MongoDB - Best practices for Feed restructuring in a social app database

texts:
I have a social app, where users can post to the school they chose on registration. Each post can have a hashtag and a school (all pointers, (hashtag is optional, school is not)).

Users see posts based on school or hashtag. Now we want to introduce a new feature, Polls. Polls are another type of post... Users will be seeing them in their feed among other posts! Besides other properties, they too have a hashtag and a school.

Right now when a user asks for posts, I'm just making a query to the Post collection, comparing only the school or hashtag field (depending on the screen they're at). So if they're on the Home screen, I'm going to have something like:

  find all documents in Post, where school equals <some_value>

And if they are on the Hashtag screen, we have something like:

  find all documents in Post, where hashtag equals <some_value>

Since we are going to have the new Poll collection, I'm wondering what implementation would be best... Here's what I have so far:

1st implementation

We keep the posts query as is and we add another one, pretty much the same, for the polls and we combine the results. Have in mind here, that our app fetches data with pagination, so we bring the posts 20-by-20. I'm saying this because in case we have a page and the responses of the 2 queries bring 20 documents each, we'll have to compare dates to know what the response should be... eg 16 posts and 4 polls.

2nd (and last) implementation

We create a new collection, Feed, which would combine both collections and it would look like this:

Feed

post: <Pointer to Post>
poll: <Pointer to Poll>
creator: <Pointer to User> // we need this for block checking or if you open your profile
school: <Pointer to School>
hashtag: <Pointer to Hashtag>

Every time a post or poll will be created, their triggers will create a Feed document, with all the necessary values.

I believe the second solution is the best, as it is much cleaner than the first. I also think they're pretty much the same complexity-wise.

Any ideas on which one is better? If you have any other implementation to suggest, please do so!

NOTE: All collections are indexed.


texts: MySQL High CPU Usage (100% in peek times)

texts:
I have a simple LAMP server on Amazon EC2. (find the specs towards the end of this message.) I used the following command in the terminal to see status of each query

watch --interval=2 "mysqladmin -u{username} -p{password} processlist"

I see roughly 4-5 queries running every now and then that result in 5-100 rows on an average (they're not very costly queries). In another console, I bring up the task manager using the "top" command. In it, I see mysqld using 150%+ CPU on an average time where the overall CPU usage is 80-90%. In the peak times, the overall CPU goes up to 100%. I've also realized the update takes a long timer sometimes. This server only uses MySQL.

The select queries that are running frequently are pulling on indexes. It's very strange to see the MySQL hogging up to more than 3/4th of the CPU to run just a few queries. I think I just don't have the correct configuration or need to fine tune MySQL configuration for a better performance. Anyone have any idea why this may be happening?

Server Specs:

Ubuntu 12.04
Intel Xeon E5-2670 v2 (Ivy Bridge) Processors
7.5 GB RAM
SSD

MySQL Specs: (Ver 14.14 Distrib 5.5.37, for debian-linux-gnu (x86_64) using readline 6.2)

max_allowed_packet 64M
innodb_read_io_threads 4
innodb_write_io_threads 4

After a few modification:
I modified the queries to ensure the where clause is using all index and it's using the correct type. In the execution plan or EXPLAIN statement, I see the following

Select_type: simple
type: index_merge
possible_keys: idx_tickets_city_code,index3,index6
key: index5,idx_tickets_city_code
key_len: 4,4
rows:1612
extra: Using intersect(index3,idx_tickets_city_code);

Total result count is 142 rows and the CPU usage is still ~60% (a little high) for few instances of this query running every few seconds.


texts:
I am using MySQL. I have table like below:

CREATE TABLE test_table
(
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    full_name VARCHAR(40)
);

Now I want to insert values like below:

INSERT INTO test_table
VALUES('raju','pydi',CONCAT(first_name,last_name));

I know this will not work, but it illustrates what I want to achieve.

How can I do this with a query? (not a procedure or function or some other).


texts:
I have been developing a solution that uses MongoDB Atlas (currently M0) for storage. As I would need to provide some statistics throughout different time periods, I need to run periodic aggregations on hourly, daily, weekly, and monthly basis.

As the project is still small, I would prefer not to deploy Spark engine and not upgrade to higher Tier with map reduce functionalities. Considering this, I would be glad for any advice about how to periodically aggregate the raw documents.


texts: Why does SQL Server start REDO phase from minLSN instead of last CHECKPOINT?

texts:
while reading an article from BOL, I stuck on the picture which explains the recovery process of database (without ADR):

Shouldn't Phase 2:Redo start from Last CHECKPOINT as till that point everything is already in data file? Why does it starts from minLSN?


texts: Is there a way to use a Case statement to read from one field and change another field?

texts:
I have a simple MS SQL database created from an import from a CSV file. I need to check a field to see if a word is in that field, and if it is, change the contents of another field. Like this:

When [Category] contains the word 'Server' change [Form_Factor] to 'Server'.

I'm not a MS SQL person, and I have stumped my Database administrator for some reason.


texts: How do I find gaps between ranges of numbers?

texts:
Consider the following table:

T_ID | T_START | T_END
-----+---------+------
   1 | 0.25    | 0.5
   2 | 0.8     | 1
   3 | 0.4     | 0.6
   4 | 0.2     | 0.3
   5 | 0.7     | 0.8

T_ID is unique. Each row represents a continuous range of numbers and is a subset of 0 to 1. T_START is less than T_END.

I need to identify any ranges not included between 0 and 1. Note that some ranges do overlap. The exclusivity of the endpoints is not relevant for my use case; I only need to identify what the endpoints of the gaps are. (As such, single point gaps are not a consideration.)

For this specific data set, I'd expect the result to be

GAP_START | GAP_END
----------+--------
0         | 0.2
0.6       | 0.7

The actual data set is large and will be aggregated over some other data (hundreds of thousands of rows, with possibly 100 row per aggregate group), so performance is important. (Answers that are not performant but may be improved are welcome, though.)

I considered trying to first determine what the covered ranges are and then trying to reverse that, but I haven't been able to figure out how to even compute the covered ranges. A simple GROUP BY is insufficient, since we have chains of overlapping ranges that would be merged into a single range, even though not all ranges overlap each other. I am of the mind that a recursive query might be helpful, but I haven't been able to figure out the logic for it yet.

I've created a SQLFiddle with this example dataset.

I am, sadly (and unproductively), not at liberty to modify the underlying representation.


texts: How are DB locks tied to connections and sessions?

texts:
I've heard of page- and row-level locks, but never heard them used with regards to sessions and connections. Today, our (MySQL) DBA was talking about row locks and implied that their scope can be set at the connection or session level. Is this true, or did I misunderstand him?

I didn't realize that locks could be set when a connection is made (or when a session is started) and then released when that connection/session ends. If this is true, what benefits does this have over row-/page-level locks? Thanks in advance.


texts: Many transactions are getting stuck in the "cleaning up" state - what could cause that?

texts:
I'm trying to track down a locking issue, and found via SHOW PROCESSLIST that when the queries in question are waiting for locks and timing out, there are no other queries active in the process list.  So I looked at SHOW ENGINE INNODB STATUS, which showed that when the transactions in question are waiting on locks, the only other tansactions present that have started (there are many in thenot started state) are ones that have been running for 30 seconds or more, and are in the cleaning up state.

Why would a transaction get stuck in the cleaning up state? Is there any way to tell at that point, what any of the original queries were for that transaction? (The transaction does not appear to be present in the information_schema.innodb_trx table).

Example output:

---TRANSACTION 2670738352, ACTIVE 25 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 158609, OS thread handle 0x2ae9a6bcf700, query id 487681086 10.0.1.246 ownlocal cleaning up
Trx read view will not see trx with id >= 2670738353, sees < 2670727358
---TRANSACTION 2670736929, ACTIVE 40 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 158413, OS thread handle 0x2ae9a5842700, query id 487683091 10.0.1.203 ownlocal cleaning up
Trx read view will not see trx with id >= 2670737972, sees < 2670727358
---TRANSACTION 2670736875, ACTIVE 40 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 158415, OS thread handle 0x2ae9a64b3700, query id 487678962 10.0.1.203 ownlocal cleaning up
Trx read view will not see trx with id >= 2670736876, sees < 2670724054
---TRANSACTION 2670727358, ACTIVE 136 sec
3 lock struct(s), heap size 1184, 1 row lock(s), undo log entries 1
MySQL thread id 158216, OS thread handle 0x2ae9a7679700, query id 487676960 10.0.1.110 ownlocal cleaning up
Trx read view will not see trx with id >= 2670735437, sees < 2670724054

Additional Info:

MySQL Version: 5.6
Storage Engine: InnoDB


texts: Is there an ideal fstab rule for XFS volumes on EC2 for running MongoDB?

texts:
I've just been following Amazon's instructions for adding a new volume to create an XFS disk for MongoDB. As per these, my fstab line looks like:

UUID=....  /data    xfs     defaults,nofail     0       2

Are the defaults OK, or should I be settings something to optimize performance? MongoDB's production notes don't mention anything.


texts: Better database for "keep always the 5 latest entries per ID and delete older"?

texts:
I have a PostgreSQL database with a history table where I store an fooID (not a primary key serial but a text), an attribute target and the current timestamp whenever the target for this fooID changes:

CREATE TABLE history (
  fooId       text not null,
  target      text not null,
  updated_at  timestamp not null default default now()
);

There are several million entries in this table and a couple of thousand changes per day. Every day I will scan the table and keep the last 5 entries per fooId and delete any older entries.

The DELETE ... WHERE id in ... rank() over (partiton by nr order by created_at... query is not my question, that works. Just takes awful long.

My question is rather: Is a standard PostgreSQL table the best approach for this problem?

Would PostgreSQL table partitioning help here? I know partitioning is used to easily throw away big chunks of data that are older than X days, but partitioning by fooId would seem to create too many partitions in my case.

Are there NoSQL databases that would be much faster because they store the data differently?

Are there other PostgreSQL tricks that help me to store the data differently and more optimized for the use case of the daily purging (SELECTs are not an issue, it gets rarely queried)?

An exclusive lock for an 1 hour per week would be acceptable. There are about 1 million different fooIDs so no chance of having a separate table per fooID.