texts: Warnings: Why are statements with limit clause unsafe even if there isn't a limit?

texts:
I'm a bit irritated because of some warnings in mysql errorlog.
Example 1:

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.
The statement is unsafe because it uses a LIMIT clause.
This is unsafe because the set of rows included cannot be predicted.
Statement: UPDATE `table` SET `status` = '1' WHERE `ID` = '15800' LIMIT 1

My colleague wrote these statements and I always wondered about the purpose of limiting a statement while using explicit ID. Nonetheless, this warning also occurs by not using LIMIT.
Example 2:

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.
The statement is unsafe because it uses a LIMIT clause.
This is unsafe because the set of rows included cannot be predicted.
Statement: DELETE FROM table WHERE id = 426888

What could be wrong in this case?


texts: Assigned filetype for backups not applying

texts:
I am working to migrate backups over to a new storage point. I figured the easiest thing to do was to duplicate the configuration over from the existing Maintenance Plan to a new one, just in case something goes wrong with the new backup point in the short term.

I copied over the existing configuration and ran the Maintenance Plan to test, but ran into an issue where one of the DBs didn't append the .bak filetype. Instead, it went with nothing or .b as the filetype.

The code within the T-SQL on the new backup Maintenance Plan matches the original, so I am unsure why it's acting like this.


texts: Best situation to use READ UNCOMMITTED isolation level

texts:
As we all know, READ UNCOMMITTED is the lowest isolation level in which things like dirty reads and phantom reads may accrue. When is the best time to use this isolation level and for what reasons might it be used?

Actually I read the answers before, but I could not understand it completely because there were not enough examples.


texts: Are there any risks of data file corruption resulting from shrinking sql data files?


texts: How to do a cumulative count for certain values

texts:
I have a scenario to do cumulative counts in certain way. So I decided to create an experiment for that before I run the query in the actual environment.

I have created a table as:

create table ttable (id numeric(10,0), txt text, dtd timestamp, cnt numeric)

And inserted the values as:

insert into ttable values (1,'Pen','2019-01-01',10),
  (2,'Pencil','2019-01-01',10),
  (3,'Pen','2019-01-02',20),
  (4,'Eraser','2019-01-02',1),
  (5,'Eraser','2019-01-03',1),
  (6,'Other1','2019-01-03',10),
  (7,'Other1','2019-01-04',10);

Now, I do have data as below:

 id  txt      dtd                   cnt
 --  ------   -------------------   ---
 1   Pen      2019-01-01 00:00:00   10
 2   Pencil   2019-01-01 00:00:00   10
 3   Pen      2019-01-02 00:00:00   20
 4   Eraser   2019-01-02 00:00:00   1
 5   Eraser   2019-01-03 00:00:00   1
 6   Other1   2019-01-03 00:00:00   10
 7   Other1   2019-01-04 00:00:00   10

I want the output as below:

txt      dtd                    cnt
------   -------------------    ---
Eraser   2019-01-01 00:00:00    0
Pen      2019-01-01 00:00:00    10
Pencil   2019-01-01 00:00:00    10

Eraser   2019-01-02 00:00:00    1
Pen      2019-01-02 00:00:00    30
Pencil   2019-01-02 00:00:00    10

Eraser   2019-01-03 00:00:00    2
Pen      2019-01-03 00:00:00    30
Pencil   2019-01-03 00:00:00    10
Other1   2019-01-03 00:00:00    10

Eraser   2019-01-03 00:00:00    2
Pen      2019-01-03 00:00:00    20
Pencil   2019-01-03 00:00:00    10
Other1   2019-01-04 00:00:00    20

(I have added empty lines in above output for clear presentation).

I want to consider only first three (Eraser, Pen, Pencil) should be repeated for each date, rest can be added if there is any entry for the date.

I have found a query from Stackoverflow for date range generation and modified based on my needs:

select dt::date from generate_series('2019-01-01', '2019-01-05', '1 day'::interval) dt

I could use above query to join with my main table data. However, I couldn't achieve what I want:

with b1 as (
select id, txt, dtd, sum(cnt) over (partition by txt order by dtd) cnt from ttable
group by 1,2,3, cnt order by 3
),
b2 as
(select dt::date from generate_series('2019-01-01', '2019-01-05', '1 day'::interval) dt ),
b3 as
(select 'Pen' obj union select 'Pencil' obj union select 'Eraser' obj)
select b2.dt, b1.*, b3.* from b2 left join b1 on b1.dtd = b2.dt left join b3 on b1.txt = b3.obj

What am I missing here?


texts:
I am currently in a task where I need to move the data from MySQL to PostgreSQL. I have 2000 rows of data mostly from 2 out of 50 tables in MySQL DB. I'm using mysql connector and psycopg2 for the tasks.

The tables and columns per table in destination DB is very different from the source DB so I need to dissect some data from source DB before moving it to destination DB. To achieve this I am using a for loop.

I stumbled on this thread which is similar to my situation: MySQL-to-PostgreSQL migration script. It was stated there that it's not good to use for loop. I would like to know, should I stick to for loop or look for another way of approach to the task?


texts: How do pages get updated checksum when switching page verification mode from torn page to checksum?

texts:
Have an older but large db (6tb on the san) that still has its page verification option set to TORN_PAGE_DETECTION. How do you apply the new checksum to all existing pages when changing page verification to checksum mode?  Is there any big overhead to be wary of when making this change to page verification mode?  Does this need to be done during a long maintenance window?
SQL 2014
Current DB Compat Level = 120


texts:
I have a problem with my database. I have a production server running MySQL 5.5.33 and a local test server running MySQL 5.5.5. The only other difference I can think of is that the prod DB is being replicated
They both run the same exact SQL databases (local is a replica of the prod environment).
I need to create a materialized view in MySQL which will contain a subset of the record_status_events table. Below you will find the query that does this. This same exact query works in under a second on local, but halts on prod. It gets stuck in the "Sending data" status. If I export the data and import it manually it works fine. It is just the INSERT INTO ... SELECT combo causing the problems.
The underlying query I am trying to insert runs under 150ms and has 50k entries.
I tried:

Changing the DB engine
Wrapping it in a transaction
Removing all indexes
Removing the primary key
Using the auto increment and ignoring the key from the source table

DROP TABLE IF EXISTS `hlh_record_status_submission`;
CREATE TABLE `hlh_record_status_submission` (
  `id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `record_id` int(10) unsigned NOT NULL,
  `status` tinyint(3) unsigned NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Created At` (`created_at`),
  KEY `status` (`status`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `hlh_record_status_submission`
SELECT * FROM `hlh_record_status_events`
WHERE id IN (SELECT MIN(id) FROM `hlh_record_status_events`
                  WHERE status IN (21,24,31,40) GROUP BY record_id);


texts:
My database is about 10 GB and I use full recovery model.

Sometimes I have to add a lot of data at once and during that time, I change the recovery model from full to bulk-logged.

What do you think?
Is this a good idea?
Or it is risky?


texts:
Is there a way to found out what is the CPU usage of the reporting server? Sometimes the CPU usage is rised up by 40% percent for several seconds. I think the reporting server is causing it but I am not able to track it down.

I have try different techniques:

SQL Server Performance Dashboard Reports
sp_whoisactive
Activity top session report
this query
looking at ExecutionLog3 View

The major problem is that the CPU usage value is raised from 20 to 60 percent for several seconds only. A lot of queries are executed and I am not able to identify which ones belongs to the reporting server.

Also, a bit of offtopic, but I have read that is a good practice to use the reporting server of the secondary databases when you have Always on Availability Group - is there any other licence requirements?


texts: SQL on a VM. Backup to Network location or just backup to VM

texts:
I'm used to working with physical servers so interested in your thoughts/experience on this.

I'm now working in a VM environment and wondering how you guys handle SQL backups?  I understand best practice is to backup to a network share when using physical devices, but does this still apply to VM's?  VM's obviously use shared storage so not sure of the point of backing up to a network share.

Anyone any thoughts on this?

Thanks in advance!


texts: Testing a Hive array for IS NULL says not null

texts:
I have a table containing an array, and I want to check if it is empty or NULL. It appears that I cannot check the NULL directly! Can anyone shed light on why the NULL check isn't working?
create table `test_array_split`
( `campaign` string
, `questions` array<string>
)
stored as orc ;

insert into `test_array_split` (`campaign`) values ('1');

select
  `campaign`
, `questions`
, size(`questions`)
, case when `questions` is null then 'null' else 'not null' end `isnull`
from `test_array_split`;
+-----------+------------+------+-----------+
| campaign  | questions  | _c2  |  isnull   |
+-----------+------------+------+-----------+
| 1         | NULL       | -1   | not null  |
+-----------+------------+------+-----------+


texts: Do I need to have two queues for Service Broker?

texts:
I am just starting with Service Broker and looking at the examples in blogs, most have a sending and receiving service with separate queues.

Looking at the SEND TO command (to send a message) I need to reference a CONVERSATION. And it seems that a CONVERSATION has FROM SERVICE and TO SERVICE parameters.

Is it wrong or bad design to send from/to the same service, with a single queue? I tried it and it seems to work.

My SQL table will have code in a trigger to send a simple empty message. This will notify a SignalR client that the table has changed, and send out new data. For such an application, surely a single queue and single service is okay?


texts: INSERT... ON DUPLICATE KEY UPDATE not working as I expect

texts:
I have a table called "Example"

CREATE TABLE IF NOT EXISTS `example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

I want to insert values if not exists and if the value exists then update, so I am using following statement:

INSERT INTO example (a, b, c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE a = VALUES(a), b = VALUES(b), c = VALUES(c);

After the above queries executed the table look like this:

Again I execute the above statement, the result looks like this:

What is wrong with my statement?


texts: Modifying Ola Hallengrens index maintenance script for deadlocks

texts:
In a SQL Server 2012 Enterprise database without a true maintenance window, I would like to run Ola Hallengrens script to optimize indices.

Testing has shown that while indices are reorganized and rebuild (online rebuild) on this database, other processes can experience frequent deadlocks and timeouts.

I would like to avoid disturbing other processes as much as possible; the maintenance script can be rerun easily and without the users noticing, so it does not matter if the rebuild itself is cancelled.

Therefore I would like to modify the maintenance script

to run at a low deadlock priority (SET DEADLOCK_PRIORITY LOW),
to wait only a short time to acquire locks (SET LOCK_TIMEOUT),
and possibly to automatically retry again after a short delay when an index reorganize or online rebuild is cancelled by a deadlock or lock timeout error (some sort of WHILE ... TRY ... CATCH ...).

I have started by adding SET DEADLOCK_PRIORITY LOW at the start of indexOptimize.sql, and it seems to run just fine, but I am not sure whether I have added it in the right place. I am worried that I may be messing with things I do not fully understand.

What are the pitfalls in adding these three options?

Is there a particular danger I should be aware of, a reason why the script does not include this functionality already?

What is the right place to put the SET DEADLOCK_PRIORITY LOW and SET LOCK_TIMEOUT within the script, so that they will be observed for all reorganize and rebuild operation?


texts: Rename cell based on duplicate compound key in MS Access

texts:
I'm using MS Access (I know, I know...), and I'm trying to rename a cell when there are duplicates of mutliple columns.

Here's an example:

Col_1   Col_2   Col_3   Note
A1      B1      C1
A1      B2      C1      Not a Dup
A1      B1      C2      Not a Dup
A2      B2      C2
A2      B2      C2      Duplicate
A3      B3      C3
A3      B3      C3      Duplicate
A3      B3      C3      Duplicate

I would like the query or code to update Col_3 (or add another column if that's easier) as below. Note that the order of the duplication is important.

Col_1   Col_2   Col_3   Note
A1      B1      C1
A1      B2      C1      Not a Dup
A1      B1      C2      Not a Dup
A2      B2      C2
A2      B2      C2Dup1  Duplicate
A3      B3      C3
A3      B3      C3Dup1  Duplicate
A3      B3      C3Dup2  Duplicate

I've seen solutions that use row_number() and over() clauses, but those just work in SQL Server.

Any thoughts for how to tackle this in Access?


texts: On a single table, how is an indexed view different from a non clustered index?

texts: Assuming that no joins are involved, and the PK of the view (the UNIQUE Clustered Index) is the same as that of the base table, is there a difference between how a nonclustered index (NCL) and an indexed view is treated by SQL Server?


texts: Query 4.5 million record postgresql database quickly

texts:
I have a SQL Server database with 4.5 million records. I reproduced the same database with the exact same 4.5 million records in a PostgreSQL database.

When querying the SQL Server database, I am using FTS which returns data sub 1 second which is great. The problem is, when using the Postgresql without FTS, it is obviously extremely slow (25 - 45 seconds if not longer). I am struggling to find out what is the best PostgreSQL FTS equivalent?

API is .net core c# using Entity framework core.

Data is a list of companies. So a typical search might 'Kelly Construction' and I'd expect the API / DB to return results where company name column contains or is 'like' 'kelly construction'.


texts: How to get results in separate rows after joining two tables

texts:
I have two tables say - Plans and Discounts. Below are the sample data in Plans table.

Below are the sample data in the Discounts table.

These two tables are related using PlanName column. I am looking for a query in the most optimized way to get a resultset in such a way that a discount for a particular plan should come below the corresponding plans. Sample data below.


texts: How to store hierarchical dimension for timeseries data

texts:
I have a requirement to store data about a load of Searches. Each time someone searches we want to record it for analyzing later on.

As part of the search the user can select one or more Categories to search within. We would like to record the categories selected and the hierarchy that the category was in.

However, the categories can change and move over time. We are likely to want to analyze both by the parent categories that the child categories were in at the time of the search, and also by the parents that the categories are in at the time of analysis.

What is the best way of storing this kind of data?

Currently I have a stream of JSON API calls (we need to support 50 a second) which look like this:

{
    "timestamp":"2018-03-08T11:22:33.456+0000",
    "searchString":"This is a search",
    "categories":[
         "Cat D/Cat B/Cat A",
         "Cat E/Cat B/Cat A"
    ]
}

Categories D and E were selected, Category B is the parent of D & E and Category A (one of several roots) is the parent of B.

We are currently just storing the time stamp and search string in a flat table in a normal OLTP style SQL Server DB:

CREATE TABLE SearchEvent (
    id int PRIMARY KEY,
    searchString nvarchar(100),
    timestamp datetimeoffset
)


texts: How to use PostgreSQL bit_or function to aggregate column?

texts:
I wish to use PostgreSQL's bit_or function on several rows of a single column of my table, but I can't figure out the proper usage for this.

Suppose I have bitwise flags in my column flags, and the rows of interest hold the numbers 1 (B00000001), 2 (B00000010), and 13 (B00001101). I want the output to be 15 (B00001111).

What I've tried so far is as follows:

SELECT bit_or(SELECT flags FROM items);
-- ERROR:  syntax error at or near "select"
SELECT bit_or((SELECT flags FROM items));
-- ERROR:  more than one row returned by a subquery used as an expression
SELECT bit_or(array(SELECT flags FROM items));
-- ERROR:  function bool_or(integer[]) does not exist
SELECT bit_or(select array(SELECT flags FROM items));
-- ERROR:  syntax error at or near "select"

Do you have any advice?


texts:
I have a table in Oracle 10.2g that I'm trying to import roughly 11 million rows into. The table contains an SDO_GEOMETRY column. It previously had NOT NULL constraints on all 3 columns, a primary key constraint on a NUMBER(38,0) column, and a spatial index. I have removed all of these to speed up the import. I removed the primary key constraint using ALTER TABLE DROP CONSTRAINT with the additional parameter DROP INDEX.

However, selecting on USER_INDEXES reveals there are currently two indexes on the table with very strange names: SYS_IL0000471920C00009$$ and SYS_IL0000471920C00010$$. Their INDEX_TYPEs are both LOB, and they both have UNIQUE for their UNIQUENESS column. COMPRESSION is DISABLED. They are both VALID, non-TEMPORARY, and GENERATED (though I'm not sure what GENERATED means).

Where might these indexes have come from? Could dropping them cause problems?


texts: Why does the delete/update block each other in this case?

texts:
The following drives me crazy.
I have:

session1>begin;
session2>begin;
session2>update dummy set salary=1000 where id=11;
session1>delete from dummy where id=1;

id is the PK of the table.
These statements execute without blocking each other. Makes sense since they refer to different rows.
Next:

session1>begin;
session2>begin;
session2>update dummy set salary=1000 where id=11;
session1>delete from dummy where id in (1,2);

These also execute without blocking.

session1>begin;
session2>begin;
session2>update dummy set salary=1000 where id=11;
session1>delete from dummy where id in (1,2,3,4);

These block each other but not always! I can not understand this. These refer to different rows why do they block each other?


texts: MySQL Statement not displaying some values

texts:
I have a MySQL Statement that runs perfectly but does not display some of the values from the table that it is supposed to display. My table has got a column called 'token' which has values that are generated from another table. The values are numeric and 20 characters long so its something like '57683913499284962398' which I transfer to the working table.

I would like to be able to select the token which is already generated but on running my query it is the only column that is not displayed here is my query:

SELECT `Group Transaction No`,`Receipt No`, `Transaction No`, `Actual Cost`,
`CErr`,`Resource Units`, `Meter Serial No`, `Token`, `Transaction Timestamp`,
ROUND(SUM(`Transaction Amount`),0) as Amount FROM `efulusi_acc`
GROUP BY `Group Transaction No`
ORDER BY `efulusi_acc`.`Token` limit 5


texts: Why I cannot let EXPLAIN update statements?

texts:
I can imagine that it's actually more important to explain SELECT statements, but I wonder why there's no possibility to use EXPLAIN on UPDATEs/INSERTs.  I may use a WHERE-statement in an UPDATE where indexes are being used (I guess?), it should be possible to see if they are actually used.  Additionally, in the case of using a SELECT-statement inside an UPDATE, I also cannot use explain for this part without getting a syntax error.
Did I misunderstand something about the processes on UPDATEs and INSERTs?  How else can I else debug those statements?


texts: How can I quickly update cache expiration schedules for 100s of datasets?

texts:
I have over 100 different datasets on an ssrs-2016 site. Each one of those datasets needs a new caching schedule. I have created a shared schedule to apply to each dataset, but I am wondering if there is a quick way to apply that schedule to each dataset without clicking through each one and adding the schedule.

Can this be done through DML against a report server database? If so, is it wise to run DML in a report server database?


texts:
I am attempting to execute the below syntax and I get two errors

  A MERGE statement must be terminated by a semi-colon (;).
    Incorrect syntax near '='.

Now if I comment out the last line of code which is an update statement the syntax executes perfectly.  Is there a way to run both an Insert() and Update() statement in one Merge()

Here is syntax

MERGE INTO EmpSales es
USING (
    SELECT employee, COUNT(sales) as SaleCount
    FROM salesInfo
    WHERE [Sale Date] BETWEEN '01/01/2016' AND '12/31/2016'
    GROUP BY employee
) cs
    ON es.employee = cs.employee
WHEN MATCHED THEN
    UPDATE SET es.TotalSales = cs.SaleCount
WHEN NOT MATCHED BY TARGET THEN
  INSERT (employee, TotalSales)
  VALUES (cs.employee, cs.SaleCount)
  UPDATE SET es.Madrigal = '0';


texts:
I am trying to design a (part of a) database which has to accomplish the following:

There is a students table, containing a bunch of students.
There are educations in the database.
Each student can have 0..n educations.
There are x different types of educations, in which x is small (<10) and known in advance.
The different types of education all have the same type of data associated with it (location, name etc.)
Some more data is associated with a student-education connection (e.g. grades, start date etc.).
The type of data which is associated with a student-education connection depends on the type of education (e.g. a masters degree has a specialization, a course does not).

I try to create a good database design to represent this data, however there are quite a few difficulties. A design I came up with is as followed:

Student table, which contains student data
Education table, which contains data of educations. There is a type column to specify the type.
Student_Education junction table, which links students with educations.
x <type>Education tables which will contain data associated with student-education connections (thus a FK to Student_Education is always present). Each education type will have it's own table.

However, there is a problem with this design: a <type>Education row should only be allowed to reference a Student_Education connection when the education type matches. E.g. a MasterEducation row can only reference a row in the Student_Education table that references a row in the Education table with type == master.

Would it be possible to add a constraint which can check exactly that?

If not, what other options are available?


texts: Is it possible to use Report Builder Model, Report Builder Report, Report Data Sources with a Sharepoint Standard 2010 edition?


texts: Order By First Condtition and If multiple matches, order by second condition

texts:
I was practicing SQL problems at hackerrank.com when I came across the following question:

  Query the Name of any student in STUDENTS who scored higher than
  75. The Marks field in the table stores the marks for student. Order your output by the last three characters of each name. If
  two or more students both have names ending in the same last three
  characters (i.e.: Bobby, Robby, etc.), secondary sort them by
  ascending ID.

To solve this, I put together a query with the help of this post:

select Name
from STUDENTS
where Marks > 75
order by case when substring(Name from -1 for 3) then 1 else 0 end, ID asc;

Now I am not sure that it actually does what the question is asking since, I have no idea idea of using case in queries. Any help will be greatly appreciated.


texts: Datatype for a Persisted Computed Column? Should I Convert/Cast When Creating It?

texts:
What is the Datatype for a Persisted Computed Column?

(a) Does SQL Server automatically infer it?

(b) Also is better to cast it as a datatype, so SQL does not spend time to find what data type it is?

create table dbo.CustomerTransaction
(
   CustomerTransactionId int primary key identity(1,1),
   CustomerId int,
   Price decimal(10,2),
   Quantity int,
   TotalAmount as CustomerId * Price PERSISTED  -- should I cast as cast(CustomerId* Price as decimal(10,2) ??
)


texts: How to determine the sweet spot between pool size and database connections in PostgreSQL

texts:
We are having trouble handling the traffic during peak hours to our database server. We're looking into improving the hardware (see this question about that side of things) but we also want to work on the pooling configuration and server tuning.

The application we
are working on is a turn-based multiplayer game for smartphones, where the backend consists of Rails with unicorn and PostgreSQL 9.1 as
the database. We currently have 600 000 registered users and since the game state is stored in the database several
thousands writes are made every couple of seconds. We have analyzed the
log files from PostgreSQL using PgBadger and during the critical hours we get a lot of

FATAL: remaining connection slots are reserved for non-replication superuser connections

The naive solution to counter this problem would be to increase max_connections (which currently is 100) in
postgresql.conf . I have read http://wiki.postgresql.org/wiki/Number_Of_Database_Connections which indicates
that this might not be the right thing to do. In the aforementioned article it's referred to finding the sweet spot between
max_connections and pool size.

What can be done in order to find this sweet spot? Are there any good tools to measure the I/O performance for different
values of max_connections and pool size?

Our current setup is 4 game servers, each having 16 unicorn workers and a pool size of 5.

Here are the non-default postgres-settings we are using:

version                      | PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu,compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
checkpoint_completion_target | 0.9
checkpoint_segments          | 60
checkpoint_timeout           | 6min
client_encoding              | UTF8
effective_cache_size         | 2GB
lc_collate                   | en_US.UTF-8
lc_ctype                     | en_US.UTF-8
log_destination              | csvlog
log_directory                | pg_log
log_filename                 | postgresql-%Y-%m-%d_%H%M%S.log
log_line_prefix              | %t
log_min_duration_statement   | 200ms
log_rotation_age             | 1d
log_rotation_size            | 10MB
logging_collector            | on
max_connections              | 100
max_stack_depth              | 2MB
server_encoding              | UTF8
shared_buffers               | 1GB
ssl                          | on
TimeZone                     | localtime
wal_buffers                  | 16MB
work_mem                     | 8MB


texts: Insert records from output 1 to every record of output 2 from the same XML file in SSIS

texts:
I am using an XML document as a source in SSIS as below

<?xml version="1.0" encoding="utf-8"?>
<Doc>
  <Header>
    <ShipmentCode>ABC-EFG-123</ShipmentCode>
    <TruckCode>TRUCK-01</TruckCode>
    <TotalNumberOfPallets>3</TotalNumberOfPallets>
  </Header>
  <Pallet>
    <SSCC>0012345</SSCC>
    <ScannedDate>1/1/2017</ScannedDate>
  </Pallet>
  <Pallet>
    <SSCC>0012346</SSCC>
    <ScannedDate>1/1/2017</ScannedDate>
  </Pallet>
  <Pallet>
    <SSCC>0012347</SSCC>
    <ScannedDate>1/1/2017</ScannedDate>
  </Pallet>
</Doc>

My XML source has two outputs - Header and Pallet and there is one Header record to many Pallet records, as you can see in the above XML file.

I actually want to insert records to a SQL table, but include some of the Header information along with the Pallet information as I insert.
So the insert might include:

ShipmentCode: ABC-EFG-123, SSCC: 0012345 - Insert record one
ShipmentCode: ABC-EFG-123, SSCC: 001234n - Insert record n

I've seen people using a Merge Join - but then I must take care of sorting? Is this example basic enough that wouldn't be a problem?

OR

I've seen people talking about using an XML task to transform the XML using XSLT. Is that the correct way to do this?  If yes, how do I figure out the XSLT syntax?


texts:
For some reason my database is hanging on a specific record:

SELECT id
FROM Foo
WHERE
    id = 40469;

Executes right away:

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Same thing with

SELECT id
FROM Foo
WHERE
    id = 40469;

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

But when I run:

SELECT id
FROM Foo
WHERE
    id = 40470;

Server just hangs.

What could cause this?

I ran DBCC checktable and it didn't find any errors.


texts: Grouping Sessions using a DATETIME stamp which can Span across a DAY

texts:
I am working with data which has a session id value which gets recycled over time (asp session ids from IIS to be exact).

I am trying to give them a sequence so that each instance of an ASP_SESSION_ID don't get grouped together.

For example, the data would look like this. Same session id, used in Aug 2016, then again in March 2017.

DTTM                        SESSION_ID
2016-08-29 14:24:28.450     297692378
2017-04-13 23:54:53.760     297692378
2017-04-13 23:59:53.477     297692378
2017-04-14 00:04:52.897     297692378
2017-04-14 00:04:53.790     297692378

At first I thought to just group on the date (at the DAY level), but for the example above, notice how the 2nd instance of the session id spans across midnight. That would cause a 3rd group, when really it's the same session.

So if I could rank them properly, it would be:

DTTM                        SESSION_ID      RANK
2016-08-29 14:24:28.450     297692378       1
2017-04-13 23:54:53.760     297692378       2
2017-04-13 23:59:53.477     297692378       2
2017-04-14 00:04:52.897     297692378       2
2017-04-14 00:04:53.790     297692378       2

Here, ASP_SESSION_ID should be considered a new instance of a session when > 20 minutes passed since the last request.

So, how can I group by or rank the same ASP_SESSION_ID differently as they get re-used over time? e.g. if the next request by that ASP_SESSION_ID is > 20 minutes from the last, group it/rank it differently?

I am just not sure how to attack the problem.

Here are some statements to generate the data above:

CREATE TABLE #TEST
(
DTTM DATETIME,
SESSION_ID INT
)

INSERT INTO #TEST (DTTM, SESSION_ID)
select '2016-08-29 14:24:28.450', 297692378 union
select '2017-04-13 23:54:53.760', 297692378 union
select '2017-04-13 23:59:53.477', 297692378 union
select '2017-04-14 00:04:52.897', 297692378 union
select '2017-04-14 00:04:53.790', 297692378


texts:
So i have two tables:
Project:
----------
project_id (PK)
project_prefix

Issues:
----------
issue_id
project_id (FK)

for every project with some prefix ex P1 i want all issues to be like P1-1 P1-2... P3-1 P3-2, whenver an issues is created in the issue table.
The way that i can think of doing this is maintaining a thrid table with Project id & issue_id_max mapping.
project_issues_max:
-----------------
project_id issue_id
P1            1
P2            1
...
P3            2

& then having something like:
prefix_id -> fetch_project_prefix(p1)

BEGIN
    Insert case values(
        concat(
            prefix , select(
                        coalesce(
                             max (project_issues_max.case_id) + 1 ,0)
                             from project_case_max
                        )
                    )
            )
        )
    update project_case_max set case_id = case_id + 1 where project_case_max.project_id = "some project id (p1)"
COMMIT

I am sure this doing max()+1 will have concurrency issues with multiple simultaneous session reads of case_id value.
What is the best way to do this?, This question here is almost similar and seems to be accepted.what makes doing max()+1 safe by adding SKIP LOCKED. It also handles ON CONFLICT, so, what i can infer is conflict can happen only when multiple session read the same value, then how come its race condition safe.


texts: CHECK Constraint to enforce upper case letter and pattern match

texts:
For one of my columns I am trying to enforce a pattern. The first letter should be D upper case, and the remaining 3 characters should be digits. For example:
D678, D890, D000
I'm quite new with CHECK constraints  and things like regular expressions.
Below is what I've done so far, which (I think) enforces the general pattern. However when I try adding something like d900 it works, even though it's a lower case d. I expected this to fail.
Can someone please assist:
CREATE TABLE Systems(
SystemsID NVARCHAR(4),
Title NVARCHAR(30),
CONSTRAINT chk_SystemsID CHECK (SystemsID LIKE '[D][0-9][0-9][0-9]'));


texts:
I am trying to find out the Query which is consuming high CPU, but can't do it so.

This command  top -H gives me the high CPU consuming thread PID, but i cannot match them with my processlist output.

Is there a way to do this?.


texts:
We have VMWare ESXi hosts administered in VMware vSphere.  I've sent my network admins this artice, http://www.sqlservercentral.com/articles/Virtualization/125226/
and asked what they think.  Currently our SQL Server VMs have one vmnic.  They wonder why the article is suggesting 3 when according to them that's redundant.  See their email that I've anonomized and shortened below.

"The author isolated SQL frontend traffic, availability traffic, and backup traffic. Since the vmnic does not have a hardware speed limit, multiple NICs will not increase, according to our VMWare expert, performance. What other reason would there be to separate out these VLANs? We currently have a flat network within the datacenter, so unicast traffic between these three VLANs would not be limited by default. Broadcast traffic would be confined to the VLAN."

I'm wondering if this is necessary and/or recommended, and if so why?

Thanks in advance,


texts:
I am trying to understand a query . When I run the below query , no rows are fetched.

select * from Table

But when I run the query , a result is returned having multiple rows.

select * from Table@somestring

I am not sure what @ followed by somestring means and why that is used in PL/SQL.


texts:
Static Cursor doesn't allow to modify the data because it's read only, and when executed with "Where current of", it returns error as expected.
So far so good.
But i was surprised to find that static cursor allow to modify data with variable like this.
DECLARE @nome varchar(100), @salario int,@idemp int
DECLARE contact_cursor  CURSOR STATIC FOR
SELECT empno,ename, sal FROM emp

OPEN contact_cursor;

FETCH NEXT from contact_cursor into @idemp,@nome, @salario
WHILE @@FETCH_STATUS=0
BEGIN

If @salario < 5000
    Update Emp
    Set Sal = Sal * 1.1
    where empno=@idemp --No error and do the update
    --Where current of contact_cursor; --gives error

print @nome+' '+cast(@salario as varchar(100));

FETCH NEXT from contact_cursor into @idemp,@nome, @salario
END
CLOSE contact_cursor;
DEALLOCATE contact_cursor;
The question is:
Which is the difference between using "where current" and the variable extracted with the cursor in this update?


texts: Topics and posts represented by one table or two?

texts:
I'm currently building a forum component for a larger application and I'm considering different approaches to certain parts of the database schema. In particular, I am considering representing topics and posts in a single table. While I view topics and posts as practically the same, I feel a bit apprehensive as this may make things less flexible in the future.

When topics of a particular forum are queried, the title and first post will be shown as well as some of the user information (basically the name and avatar). In this application, there are various attributes that are used by both topics and posts except for views and replies; and perhaps title, and forum_id(forum_id because that would mean potentially hundreds of records need to be affected if a topic is changed to another forum as opposed to changing the forum_id attribute in the topic relation).

The tables look something like what I have below here:

TOPIC            POST
topic_id         poster_id
forum_id         topic_id
poster_id        content
title            upvote
views            dnvote
replies          closed
post_id          deleted
                 last_edited
                 last_editor
                 parent_id
                 content
                 post_id

Doing it this way, using table inheritance, generating the posts in the topic would require a 4-table join via TOPIC, POST, USER, and TOPIC_TYPE.

On the other hand, if I decide to take the single table approach, should I simply leave the views, replies, title, and forum_id attributes as null if the topic_type is a regular post? (topic_type references an appropriate icon for the type of topic displayed, and will be used for statistics and etc.)


texts: Inserting ID and Date to locate data for C#/VBA application

texts:
I am trying to allow users to upload an excel sheet to insert data into the sql database. When they upload data I want a datagrid view to show the data they just uploaded in the database and to be able to see that in retrospect as well based on the date.

I created a table like this:

**dbo.DB_TRACK**
V_ID Identity
TBL_N varchar(20)
ID  int
ENTR_DT date

I wrote Stored Procedure for one of the database tables like this:

Create Procedure dbo.Insert_Students
(
SName varchar(10),
SAge varchar(10),
SAdmit_Dt varchar(10)
)
    Declare @id table(id int),
     @pk int,
     @iSAge int,
     @iSAdmit_Dt date

     Set @iSAge=@SAge
     Set @iSAdmit_Dt=Convert(Date,@SAdmit_Dt)

    BEGIN TRY

      BEGIN TRANSACTION

      Insert into dbo.Student(Name,Age,AdmissionDt)
      OUTPUT INSERTED.S_ID into @id
      Values
      (@SName,@iSAge,@iSAdmit_Dt)

      COMMIT TRANSACTION

      BEGIN TRANSACTION

      Insert into dbo.DB_TRACK (TBL_N, ID, ENTR_DT )
      VALUES ('dbo.Student', (Select @pk=id from @id),GETDATE)

      COMMIT TRANSACTION

    END TRY
    BEGIN CATCH

      IF @@TRANCOUNT > 0
          ROLLBACK TRANSACTION

    END CATCH

I am getting the error:

  Incorrect syntax near '='.

UPDATE:
Not getting this error anymore (Thanks to David Browne) and was able to save the SP but when executing the SP, I am getting this error:

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

I tried Convert(date,GetDate())  but it did not work

The code that calls this stored procedure is in VBA like this:

Dim SName, SAge,SAdmit_DT as String
Dim oSName, oSAge,oSAdmit_DT as ADODB.Parameter

SName= ws.Cells(1, 11)
SAge = ws.Cells(1, 12)
SAdmit_DT = ws.Cells(1, 13)

Set oSName = cmd.CreateParameter("@SName", adVarChar, adParamInput, 10, SName)
cmd.Parameters.Append oSName
Set oSAge = cmd.CreateParameter("@SAge", adVarChar, adParamInput, 10, SAge)
cmd.Parameters.Append oSAge
Set oSAdmit_DT = cmd.CreateParameter("@SAdmit_DT", adVarChar, adParamInput, 10, SAdmit_DT)
cmd.Parameters.Append oSAdmit_DT

cmd.Execute' This is where it errors out

I tried removing SAdmit_Dt but still got the error
I tried passing DATE() through excel but got an Arithmatic overflow error
Any idea how to fix this? Also is there a better way to do what I am trying to do?


texts:
As our (32-bit) application uses the Oracle client for communication with the Oracle server I have set up a test environment on a Microsoft Windows Server 2019 Datacenter. I have installed the Oracle DB 19c (19.3.0.0 64-bit) and the appropriate Oracle client (19.3.0.0 32-bit).
As soon as I start TNSPING or SQLPLUS out of the installation directory of the Oracle client, the Windows error message "The application was unable to start correctly (0xc000007b). Click OK to close the application." occurs.
I have searched the internet for remedy and tried all the 'restart Windows' and 'reinstall framework' hints without success. The hint "64-bit software on 32-bit Windows doesn't work." simply doesn't apply to my problem.
Is there anybody out there who has further ideas - perhaps ideas more close to the Oracle world.
Thanks in advance,
Ben


texts: MSSQL Stored procedure becomes slow after few days

texts:
I am facing an issue on SQL Server in which my stored procedure becomes slow after couple of days.

Below is the sample of my stored procedure.

Could this be a caching issue on the server side? Can I increase the server's cache size to resolve the problem?

Normally the stored procedure returns data in one second

@START_VALUE int=null,
@END_VALUE int=null
@UID NVARCHAR(MAX)=null,
AS
BEGIN

 SELECT    dbo.TABLE1.ID,
ROW_NUMBER()  OVER (ORDER BY TABLE1.UPDATED_ON desc) AS RN,
CONVERT(VARCHAR(10), dbo.TABLE1.DATE, 101) AS TDATE,
CATEGORY = (
        SELECT TOP 1 COLUMN1
        FROM TABLE5 CT1
        WHERE TABLE1.CATEGORY = CT1.CATEGORY_ID
   ),
TYPETEXT = (
        SELECT TOP 1 COLUMN1
        FROM TABLE6 CT1
        WHERE TABLE1.TYPE = CT1.TYPE_ID
   ),
IMAGE = STUFF(( SELECT DISTINCT ',' + CAST(pm.C1 AS varchar(12))
                FROM TABLE2 pm
                WHERE pm.ID = TABLE1.ID AND pm.C1 IS NOT NULL AND pm.C1 <> ''
                FOR XML PATH('')),
              1, 1, '' ) INTO #tempRecords
 FROM dbo.TABLE1
 WHERE ((@UID is null OR  dbo.TABLE1.ID = @UID )
 ORDER BY TABLE1.UPDATED DESC

 SELECT @count = COUNT(*) FROM #tempRecords;

 SELECT *, CONVERT([int],@count) AS 'TOTAL_RECORDS'
 FROM #tempRecords
 WHERE #tempRecords.RN BETWEEN CONVERT([bigint], @START_VALUE) AND      CONVERT([bigint], @END_VALUE)

END

   GO


texts: Trigger not functioning correctly in MYSQL

texts:
I am trying to set up a trigger to get it so if a new entry is either null or set to '000-00-00 00:00:00', that it will set the current datetime (NOW()).

I can get the trigger to set the datetime if I don't check for null or other value, but if I do, it doesn't fire.

I have tried the following and they are not working:

delimiter // CREATE TRIGGER applicationsolution_OnInsert BEFORE UPDATE ON applicationsolution FOR EACH ROW BEGIN IF NEW.created = '0000-00-00 00:00:00' THEN SET NEW.created = NOW(); END IF; END;// delimiter ;

CREATE TRIGGER applicationsolution_OnInsert BEFORE INSERT ON applicationsolution FOR EACH ROW SET NEW.created = IFNULL(NEW.created, NOW());


texts: ORACLE - Trying to return MAX or MIN based on a CASE statement

texts:
I'm working on a query to give me either a MAX or a MIN based on whether another column is divisible by 2.

SELECT
        CASE mod(ROLE_TYPE, 2)
            WHEN 1 THEN MAX(SALARY)
            ELSE MIN(SALARY)
            END
FROM HR;

Is this possible?  I get an error when I run this:

ORA-00937: not a single-group group function
00937. 00000 -  "not a single-group group function"

My guess is that case can't handle aggregate functions like this.  Is there a way to do this in a single select statement so I don't have to do a IF/THEN SELECT?


texts: Workarounds for setting default schema for domain group?

texts:
I am currently working on a project for a client, they have asked me to change the database from Oracle to SQL Server. So we migrated the database across to SQL Server 2008, when this was migrated (Using SSMA SQL Server Migration Assistant) it added a schema name onto the start of all of the table and procedures. Since then we have made lots of changes to some of the procedures within the database.

My problem now is that the application (.NET web app) is calling the procedures without the schema name prefixed. So when it calls the database it cannot find the procedure as it does not have the schema name at the start.

My client has made it clear that the only authentication that they want to use is Windows authentication. From what research I have done I can see that it is not possible to set a default schema for a domain group of users. and it would not be possible for us to add a user in the database for every user that will use the system from that domain group.

Does anyone have any advice on what I could do or a workaround for the default schema issue for domain users.

Thanks


texts: Why are users allowed to connect to SQL Server when creating sql account, that defaults login to 'master" for login

texts:
When a new login is created in SQL Server it automatically defaults database to master.

We do not give permissions in master (systemdb) but in our user DB (CustomersDB) with db_datareader and db_datawriter.

We save the account.

Then a user is able to connect, why?  Does this have to do with public permissions in master?


texts: SQL Server memory benchmarking - how to fill up memory?

texts:
Recently I was asked by my VMWare colleague how to cause a (non-production and bare install) SQL Server to use up the memory of a machine.

The solution I found in this forum post does not cause the SQL Server process to use up memory but instead SSMS due to it's output.

As SSMS is only 32 bit, it crashes after about 4 GB, so this is not useful for a  server with 600 GB memory...


texts: Database design for changing number of fields

texts:
Google Contacts lets you to enter more than one phone number, or email address for a contact.  You can add as many as you want. I don't know if there is a limit, other than practical list size. To build this type of structure I normally create a table with userid, key, and value fields, so a user can keep an unlimited number of records.

I've read that key-value table design performance is low and they do not suggest it.  I have not used this type of structure for more than 50-100 users. At the moment, I am working on a big project and I need to keep information about accounts.

Without Key-Value pairs, I will define lots of fields to keep information of each account. I need a new kind of field later, I will add it for all account records.

I need ideas / sources / documents about how big businesses are building this type of structure.

For now, I expect to reach 2000-3000 customers in next 3 years. I normally use MySQL and PHP.

P.S. Each account will have at least 5 different kinds of information to start. In time, this will increase according to requirements.


texts: can a table in 2nd normal form have a FD in which the non prime attribute depend on a super key having an extraneous attribute?

texts:
For 2NF every source I can get hands on says the following:

  The non prime attribute must be fully dependent on a candidate key.

But can the non prime attribute be dependent on a super set of the candidate key? I mean can the left side contain an extraneous attribute.

Also, please explain, if it is necessary that all non prime attributes have to be fully dependent upon on any of the candidate keys . Can't one non prime attribute just be dependent on another non prime attribute?


texts: Informix v12 substitute for query using multiple with statements

texts:
I am trying to convert some PostgreSQL into Informix and factor out the WITH statements in the query below.

I have been unable to locate a solution to factoring out multiple with statements.

The user account to do this does not have 'create table' permissions.

WITH bear AS
(SELECT
lv.vct_id
lsetup.wrk_id,
lobservation.id AS obs_id,
ladoption.factor_1 AS bear_corr,
lsurvey.dataset_series
FROM crs_vector cv
INNER JOIN crs_observation co
ON cv.id = co.vct_id
INNER JOIN crs_setup cs
ON (co.stp_id_local = cs.id AND lobservation.obt_sub_type = 'BEAR')
INNER JOIN crs_work cw
ON (cs.wrk_id = cw.id)
LEFT JOIN crs_adoption ca
ON (co.id = ca.obn_id_new)
LEFT JOIN crs_survey cs ON
ca.sur_wrk_id_orig = cs.wrk_id
WHERE cw.id = 1672933 AND co.obt_sub_type = 'BEAR'),

dist AS
(SELECT
lv.vct_id
lsetup.wrk_id,
lobservation.id AS obs_id,
ladoption.factor_1 AS bear_corr,
lsurvey.dataset_series
FROM crs_vector cv
INNER JOIN crs_observation co
ON cv.id = co.vct_id
INNER JOIN crs_setup cs
ON (co.stp_id_local = cs.id AND lobservation.obt_sub_type = 'DIST')
INNER JOIN crs_work cw
ON (cs.wrk_id = cw.id)
LEFT JOIN crs_adoption ca
ON (co.id = ca.obn_id_new)
LEFT JOIN crs_survey cs ON
ca.sur_wrk_id_orig = cs.wrk_id
WHERE cw.id = 1672933 AND co.obt_sub_type = 'DIST'),

SELECT
bear.vct_id
bear.wrk_id,
bear.obs_id,
bear.factor_1 AS bear_corr,
bear.dataset_series
dist.wrk_id,
dist.obs_id,
dist.factor_1 AS bear_corr,
dist.dataset_series
FROM   bear
FULL OUTER JOIN   dist
ON (bear.vct_id =  dist.vct_id AND bear.wrk_id = dist.wrk_id);


texts: Does the length of the index name have any performance impact?

texts:
This may seem silly, but in trying to stick to my standard nomenclature when naming indexes in our DB, the latest one was a little lengthy, and like all things DB I wondered if it in any way could negatively affect performance.  I'm just not sure where or how index names are actually stored or used vs the actual column data.

For example, is it advantageous to name an index epoid vs ecm_products_options_product_id?

The later has been closer to our standard and makes for easier reading and identification in the application layer.


texts: How to enable failed login profle on MySQL

texts:
We are trying to implement failed_login profile for account lock on MySQL. didn't find it on documentation.

Our version is 5.7.12.


texts: How can I create a role to create,update,delete stored procedures and views

texts: what permissions do I need to give in order to achieve the above task to get users the capacity to create,update,delete stored procedures and views to a database role.


texts: mysql dump best practices for development

texts:
I have an application that has a large database that includes, among many other tables, a table called content and a table called users.

I would like to set up a development environment for this application, however I would like to bring in only some parts of the content and users tables.

If there are 50,000 rows for each of these tables, I only want to bring over 10 rows so my development environment has at least some data.

What is the best way to create a dump of the database data and structure, while also limiting the size of the dump and the risk of putting sensitive information in a development environment?


texts:
Is there a way to audit logins to MySQL?  I'd like to be able to create a username for each employee and thereby create an audit trail of logins. However, googling has turned up no good results.

The more we can audit, the better.  At the very least, it would be nice to know who logged in when. It would be even better to see who executed what query when. The logs are there mostly to tell clients we have them since there is potentially sensitive information in the database.

Obviously, being able to audit the queries executed by each user (and when) would also give us the ability to better pinpoint who is the cause of an security issue if one should arise.


texts:
I have two tables, table1 and table2. Let the two tables contain date, id and latency column.

I have a simple query that performs a join on the two tables and returns a set of rows:

Select table1.date,(table2.latency - table1.latency) as ans from table1, table2
where table1.id = table2.id order by ans;

I need to find the nth percentile row from the returned set of rows, lets say I need to find 90%, 99% and 99.9% percentile row from the data.

I need to display the data in a form like this:

    date       |   percentile  | ans
    01-12-1995 |    90         | 0.001563
    02-12-1999 |    99         | 0.0015
    05-12-2000 |    99.9       | 0.012

This is my first encounter to PostgreSQL. I am confused as to how should I proceed.

I was having a look at PERCENT_RANK() function. Please guide me in the correct direction.


texts: Fire a trigger function when checks change

texts:
I have the following use case:

I have a set of different checks on different tables in one schema. If a data set is 'wrong' (did not pass a check) it gets pushed into another table in another schema. There one can edit the data again and, via an AFTER UPDATE trigger, the data set will be pushed back to the original table. This will be successful when no check is violated.

Now I have the problem that I sometimes need to edit my check functions, because some data set might have been wrongfully pushed away from the table. But the data which still lay in the 'error' table will not get pushed back into the original table before I trigger an UPDATE on it, even though it might be correct after my edition of the check function.

My question is: Is it possible to automatically trigger an UPDATE on all data sets in such an 'error' table after editing the check functions of the original table (for instance based on the error message)?


texts: Parent and Child Relationship with the Same Foreign Table

texts:
Business Rules (I realize these rules are a little abnormal):
A Person (student) can attend multiple schools. Once one or more Schools are chosen, the student can pick one or more degrees at each school. Once a degree is chosen, the Student has an option of choosing to participate in many DegreeSpecificGroups, Courses, and OptionalTrips.

I have 4 tables:

Table Person
Table School
Table Degree
Table DegreeSpecificGroup
Table Course
Table OptionalTrip

Assume that each table has a need for a unique set of properties for each Person. However, I am trying to keep this simple. My question is - where should I keep the relationships with the Person table? Do I make a relationship table for each combination - ie: PersonSchool, PersonDegree, PersonCourse, PersonOptionalTrip...?

I am having troubles convincing myself to do this because it contradicts my hierarchical thinking - IE: you cannot attend an OptionalTrip unless you are enrolled in the School and have selected a Degree. Half of me wants to keep all relationship information in the highest relationship level (SchoolPerson) - but of course that table will become bloated and unmanageable. What is the proper way to handle these multi tiered relationships?

I have looked at some ER diagrams to see some examples of other diagrams but they do not have the level of detail I am looking at


texts: Why doesn't postgresql COPY command work with files in mapped drives

texts:
I have a problem copying CSV files from a network shared drive into postgreSQL, at present the fix is to copy the files to a local drive first, but it's another process in a chain which is becoming too messy.

If I run the following command

  COPY test_schema.test FROM 'X:\_Postgres DBs\Testing\test_file.csv'
  CSV HEADER;

I get:

  ERROR:  could not open file "X:\_Postgres DBs\Testing\test_file.csv"
  for reading: No such file or directory

ok, so postgreSQL Server doesn't understand windows mapped drives, fine, but if I run:

COPY test_schema.test FROM '\\ws7.domain.com\share-name\_Postgres
DBs\Testing\test_file2.csv' CSV HEADER;

I get:

  ERROR:  could not open file "\\ws7.domain.com\share-name\_Postgres
  DBs\Testing\test_file2.csv" for reading: Permission denied

the file test_file2.csv can be opened locally on the same server with eg notepad/notepad++/wordpad so it doesn't look like there are any file attributes such as 'file in use'

Anyone have any tips how to use COPY with shared network drives?
(Our DB UI is coded in Delphi so I'm looking for ways to do with with postgresql scripts I can call from Delphi otherwise I'll have to write some fiddly Delphi Firedac batchcopy method.)


texts: How do I ensure consistent MySQL backups?

texts:
How can we ensure MySQL backups are consistent when they are taken as full backups as well as incremental backups using binlogs?

According to me, I record the COUNT(*) from random tables after issuing flush logs on the database.  I then take binlogs generated until it flushes the binary file and ensuring the table is not altered while doing SELECT COUNT(*).  I then restore the binary logs in different database server and cross check COUNT(*) to ensure the random tables are the same or not against production database and restored database.

Any other thoughts?  Keep in mind that hits from application to tables can be paused only for a few minutes.


texts: Should I set document version field to be part of a primary key

texts:
Should I set document version as a primary key
I am working on a document management system, and I have these two tables representing part of the whole tables.

On the DocumentVersion table I have set the primary key to be (version + documentID) . so can anyone advice if this is a  recommended approach where the documentID is of type int while the version is of type decimal(5,2) ? or it is better if I create a new field and set it as the primary key inside the DocumentVersion table?
Thanks


texts: Accessing FILESTREAM share by a user on another domain with full trust

texts:
I have a FILESTREAM share on a SQL server in domain AAA and a users in domain AAA and BBB.   The two domains have a full trust.  Users in domain AAA can access the share from UNC \server\mssqlserver\dbshare\tableshare or through Explore FileTable in SSMS.  Users in domain BBB can't access the share through the UNC or SSMS. However they can map a network drive to the share and access it that way.  Both users have same login/user permssions on the instance/database. Why cant users in domain BBB access the share through the UNC path or SSMS?

Thanks!


texts:
From the Object Explorer in SQL Server, when selecting and scripting a foreign-key constraint, the following code is generated.

     USE [MyTestDatabase]
     GO

     ALTER TABLE [dbo].[T2] WITH NOCHECK ADD CONSTRAINT [FK_T2_T1] FOREIGN KEY([T1ID])
     REFERENCES [dbo].[T1] ([T1ID])
     GO

     ALTER TABLE [dbo].[T2] CHECK CONSTRAINT [FK_T2_T1]
     GO

What is the purpose of the last statement "ALTER TABLE CHECK CONSTRAINT"? It doesn't seem to matter whether or not it is run.  It does not fail on existing bad data, nor does it change that the constraint will be enforced on new data.

Thanks!


texts: Adding indexes to production MySQL database without downtime?

texts:
I am looking to improve the performance of queries on a large MySQL database that is running in production by adding proper indexes. We have tried to add the indexes on the live database, but of course this locks the database and makes our application unavailable.

We have thought of creating a secondary database with the updated indexes and then migrating the database data over. This would involve some downtime.

Would there be a better way about doing this? That doesn't involve downtime?

Thank you in advance for your help


texts: SELECT INTO with regexp_replace() doesn't write changes into newly generated table

texts:
I have been using this query in Postgres 9.1 in order to remove '' from existing data.
Below is a sample of the data before running regexp_replace():

6''
6''
6''
20''
12''
18''
20''
8''
10''
''
''

Upon running:

select REGEXP_REPLACE(regexp_replace, $$^'*$$, '' , 'g')
from temp_4 order by id;

I receive this clean output:

6
6
6
20
12
18
20
8
10
( ) <- stand-in for " "
( )

however when attempt to then write these results into a table, say using:

select * into table_3 from (select REGEXP_REPLACE(tbl, $$^"''*,*$$, '' , 'g') from temp_2 order by id) as temp_3;

I receive

6
6
6
20
12
18
20
8
10
''
''

Where '' has been removed from all values, except in the case it was the only value present. I have attempted to whitelist using [\w\s*] all other values instead of blacklist with this regex ^'*, but in both instances values of ''. I feel like I'm taking crazy pills.

How do I write my table where I can replace '' with 0 or null values?

Also, is my syntax terribly wrong in my attempts to write this data to other tables? Is there a better way to do this?


texts:
How do i get this?

to look like this:

my create and insert code :

Create Table Countries
(
    Country nvarchar(50),
    Town nvarchar(50),
    City nvarchar(50)
)
GO

Insert into Countries values ('USA','New Jersy','New York')
Insert into Countries values ('USA','houst','Houston')
Insert into Countries values ('USA','Dally','Dallas')

Insert into Countries values ('India','hyder','Hyderabad')
Insert into Countries values ('India','Bang','Bangalore')
Insert into Countries values ('India','Del','New Delhi')
Insert into Countries values ('India','townin','India Town')

Insert into Countries values ('UK','chelsea','London')
Insert into Countries values ('UK','sunderland','Birmingham')
Insert into Countries values ('UK','United','Manchester')

Current pivot code:

Select Country, town1, City1, town2, City2, town3, City3, town4, City4, town5, City5
From
(
Select Country, City, 'City'+ cast(row_number() over(partition by Country order by Country) as varchar(10)) ColumnSequence,
Town, 'Town'+ cast(row_number() over(partition by Country order by Country) as varchar(10)) ColumnSequence2
from Countries
) Temp
pivot
(
max(City)
for ColumnSequence in (City1, City2, City3, City4, City5)
) Piv
pivot
(
max(Town)
for ColumnSequence2 in (town1, town2, town3, town4, town5)
) Piv
group by Country, town1, City1, town2, City2, town3, City3, town4, City4,
town5, City5


texts: MDF, LDF, TempDb Location on same Hybrid SAN

texts:
I have seen this question asked before regarding SSDs or SANs, but never a Hybrid SAN. Also, most of the questions are several years old, so I figured I'd get a fall 2016 take on the answer.

We currently have a physical server with ~150 SQL 2012 databases all located on a RAID10 volume. The TempDb is located on the OS volume and is RAID1. We do mostly reading, with occasional, massive batch inserts.

We are migrating to a virtual server housed on a Hybrid SAN (24 drives, 1/3 SSD 2/3 10K HDD).

The general consensus is to keep MDF, LDF, and TempDb on different volumes, but I am getting conflicting opinions from my SQL guru vs. my SAN master.

One of the benefits to the Hybrid SAN is that I can create volumes of all SSD, all HDD, or a combination of both (magically migrated based on usage) with a different RAID method for each volume.

Given all that, what would be the suggested best practice for MDF, LDF, and TempDb placement? All on same volume of SSD? Split off into different volumes, some SSD some HDD? What RAID method? How about formatted block size and partition alignment (if that applies to SAN/SSD)?

Thanks in advance!


texts:
We're working on a PM system where a user can send an email to other users or groups.

We currently have the following tables:

Users
Groups

Users can be part of many groups. A user doesn't have to be part of any group.

The problem I am having is with managing recipients and inboxes. Groups don't have inboxes - users do. An inbox stores information like whether or not the recipient has read the message, marked it for follow up, deleted it, etc.

So I made two tables:

recipients

recipients_inbox

recipients just stores the thread id, recipient id, and whether or not the recipient is a group or not.

recipients_inbox has the users who this message was sent to - whether as part of a group or individually. It stores: thread id, recipient id, when the thread was last read, flagged, etc.

My question is: Does it make sense to keep the recipients and recipients_inbox table separate? Should I just combine them and where the recipient is a group leave user-fields such as lastRead and flagged as null?

Thank you


texts:
I have multiple data sources in multiple databases that I have been tasked with correlating for business analytics.  I currently have ODBC connections to two (one Pervasive, one Firebird) that I can use successfully in Excel.  A DBA friend suggested I use Microsoft Access to create linked tables and perhaps make creating lookup tables between the data sets easier.

I've run into a problem.  Although Excel uses the ODBC connection to Firebird fine, Access can't create the linked table through the Create Linked Table dialogue.  I get the following error message:

ODBC--call failed

[ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -104
Token unknown - line 30, column 13
when (#-104)

I take this to mean that whatever query Access is using to attempt to create the linked table is failing to be interpreted correctly by the Firebird database, but I don't get to see any of the code because Access is handling it.  I seem to recall reading that Access tend to fall back to SQL query structure when it can't figure out how to talk to certain databases, but I can't find the source.

I did see a post by Max Vernon with regards to linked tables in SQL server 2008.  He suggested using Microsoft OLE DB Provider for ODBC Drivers, but I have been unable to locate the driver.

I guess my question is this: Is it possible to manually configure linked tables in Access, do I need to get the OLE DB interface working (I have yet to figure out where it is to install it), or is there another method I should be exploring?


texts:
I am using SSIS packages to extract data from SAP database tables into SQL Server tables. I am using OLEDB source/destination connections to achieve this.

The problem now is that a table in SAP has 5 Million records and its taking around 2 hours to extract this data into my SQL Server table. I have used the trunc-dump method (truncating the table in sql server and dumping data into it from SAP table) and also tried using Multiple Hash key to bring in the updated/new records.

The problem with Hash key is that it still has to scan the entire table to look for changed/new records and hence takes almost the same time as the trunc-dump method.

I am looking for a new way or changing the existing way to reduce the time taken to complete this extraction.


texts:
I have two different local versions of SQL Server on my machine:

(localdb)\MSSQLLocalDB
localhost

I think one of them might have been installed alongside Visual Studio, but I am not sure. Is there a difference between these two (other than the version number)?

And if I only want to keep one of them, which one should it be? I am only using it for local stuff, while developing...


texts: set right permission to get connection ip for a user mapped to login

texts:
I have a problem with properly settings the rights permissions for my user to get access to connections ips.

I know that, to get the conn ip I must set the VIEW SERVER STATE to the login that I use to connect to db. I know hot to do it and it works ok. The problem is when I want to execute a stored procedure that is executed as other user. This user (mapped to the login with the above permission) doesn't have permission to get the ip. What I have missed?

A script to reproduce:

CREATE LOGIN TestLogin WITH PASSWORD=N'TestLogin',
                                    DEFAULT_DATABASE=[master],
                                    CHECK_EXPIRATION=OFF,
                                    CHECK_POLICY=OFF

CREATE USER TestUser FOR LOGIN TestLogin

use master
GRANT VIEW SERVER STATE TO TestLogin

With this I can log to the database and execute the query:

SELECT client_net_address
FROM sys.dm_exec_connections
WHERE Session_id = @@SPID

But when I add the procedure I can't execute it:

CREATE PROCEDURE TestProc
WITH EXECUTE AS 'TestUser'
AS
BEGIN
    SELECT client_net_address
    FROM sys.dm_exec_connections
    WHERE Session_id = @@SPID
END

GRANT EXECUTE ON OBJECT::dbo.TestProc TO TestUser

All I got is

Msg 297, Level 16, State 1, Procedure TestProc, Line 5
The user does not have permission to perform this action.


texts: What consequence deleting idb file will have on my mysql database?

texts:
I tried to check what is consumming my Disk Space on Centos 7, I found these 2 database file:

  /var/lib/mysql/pub/vues.ibd: 8.1G
  /var/lib/mysql/pub/log.ibd: 8.6G

I would like to delete them. pub is the name of my database.
And log and vues are  tables.


texts:
How can I validate a SSN column that is encrypted in a table using AE without passing one by one value as a parameter? I have to validate if the SSN is formatted properly and numbers and '-' etc.

I tried inserting the SSN column values into a #table to perform the validations on the decrypted values but that fails.

This is being implemented in a stored procedure which does all validations on the encrypted table in bulk and fails for SSN. Any suggestions that can help me perform this task?

I understand that validation has to be done by app. The requirement is that a third party tool loads a file to the DB which has SSN in it and they don't do any validation for the file upfront before loading it into the table. We have encrypted the column on the table and then after we want to validate the ssn to check if it's 10 digits or not etc. How do we achieve this? It's not defeating the purpose as anyone who has the master key can perform required actions as per the need.


texts: Mongo client cannot connect on first try in bash script

texts:
I'm working on a bash script for automatic MongoDB server installation and user creation.

#!/bin/bash
apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 7F0CEB10
echo 'deb http://downloads-distro.mongodb.org/repo/ubuntu-upstart dist 10gen' | tee /etc/apt/sources.list.d/mongodb.list
apt-get update
apt-get install -y mongodb-org
update-rc.d mongod defaults
sed -i 's/^auth = true/#auth = true/g' /etc/mongod.conf
sed -i 's/^#noauth = true/noauth = true/g' /etc/mongod.conf
service mongod restart
mongo user_creation.js

The script is run on a clean precise64 Vagrant VM. Add the Debian/Ubuntu repo to install the latest version (we're on Ubuntu 12.04 LTS, MongoDB version 2.6.4), add server to startup, set some variables in the MongoDB config file. Simple stuff really, however, the last line fails each and every time.

MongoDB shell version: 2.6.4 connecting to: test 2014-10-03T12:34.:56.000+0000
warning: Failed to connect to 127.0.0.1:27017, reason: errno:111 Connection refused
2014-10-03T12:34.:56.000+0000 Error: couldn't connect to server 127.0.0.1:27017
(127.0.0.1), connection attempt failed at src/mongo/shell/mongo.js:146
exception: connect failed

I thought maybe the server needed some time to load data or some other kind of initialization between its restart and the client connection so I tried putting a sleep 30 between the last two instructions : didn't work. However, removing the mongo execution from the script and starting it manually works like a charm every time! The .js is not the problem here, I've tested it and it's working alright.

Does anyone have any idea why the mongo cannot connect on its first try and how I could fix this? For now, I'll settle with this terrible (working) hack, but I'd rather have a cleaner, hackless script:

while :
do
    RESULT=`mongo --eval "1;"`
    echo $RESULT

    if [[ "${RESULT:-null}" == *Failed\ to\ connect* ]]
    then
        sleep 1
    else
        mongo root_creation.js
        break;
    fi
done

It's ugly but after 1 to 10 tries, it ends up connecting and working perfectly.


texts: How to select from a table where in a list and then diff that against the same list?

texts:
Here is what I need:

I have a LIST of keys:

A
B
C
D

A TABLE keys that contains:

A
B
**... and millions of other entries** ( but `NOT` `C` or `D` )

I want to see what is in the list AND NOT IN the table AND list.

the expected output would be:

C
D

Since C and D are in the LIST AND IN the TABLE.

I have found examples of how to create a table of a type and wrap the list I just could not get the syntax correct by putting together disparate examples to get what I want and they all failed.

Here is what I am stuck on:

  [42000][904] ORA-00904: "MISSING_KEY": invalid identifier

CREATE OR REPLACE TYPE MISSING_KEYS AS TABLE OF VARCHAR2(256);

SELECT missing_key
FROM TABLE (MISSING_KEYS('A','B','C','D'))
WHERE missing_key NOT IN (
    SELECT key
    FROM schema.keys
    WHERE key IN ('A','B','C','D')
);

How do I give the column from the MISSING_KEYS TYPE a name?


texts: Deleting multiple tables in a single delete query in Mysql

texts:
I have a task to delete records from multiple tables (say 4 or more). I came to know that I can do the delete in a single MySQL query as follows

DELETE table1,table2,table3,table4
  FROM table1
  LEFT JOIN table2 ON table1.pk=table2.pk
  LEFT JOIN table3 ON table3.pk=table2.pk
  LEFT JOIN table4 ON table4.pk=table3.pk
 WHERE table1.pk IN (101,102,106,...)

How does it works internally? Any other performance caveats compared to spliting the query into 4 separate delete queries?


texts: Why does MariaDB 10.1.16 vanilla installation comes with an almost empty config file?

texts: Why does MariaDB 10.1.16 vanilla installation comes with an almost empty config file (/etc/my.cnf) ? Whereas it is not the case for MySQL. The config file for MySQL is usually not empty.


texts:
I am trying to migrate a DB2 database (v 11.1) to MySQL (v 5.7). I have tried using DBConvert (https://dbconvert.com/db2/mysql/) which works quite well for "normal" tables but fails at a large table (130 GB) with LOBs.
Is it possible to migrate this LOB table in another way? Is it possible to do a db2 export so that the result is readable for a mysqlimport?


texts: cumulatively calculate the distance along a line

texts:
I am using Postgis to:

calculate the cumulative distance in metres along the line
store the cumulative distance in "m"

I have a table with points on this line and each row holds lat and lon coordinates. The "m" value is 0 for all rows.
The following code nicely gets me the distance between the first point and the next.
SELECT a.geom
FLOOR(
ST_Distance(ST_Transform(a.geom, 3857), ST_Transform(b.geom, 3857)) * cosd(42.3521))
AS dist
FROM line a
LEFT JOIN line b
ON a.id = b.id + 1

however, it does not add up to a total nor does it update the "m" value yet.
I tried a set of permutations of the following, but none of them store a cumulative sum of the previous row's value for "m" plus the calculation of the distance between this point and the previous.
WITH next AS (
    SELECT
    ST_Distance(ST_Transform(a.geom, 3857), ST_Transform(b.geom, 3857)) * cosd(42.3521)
    AS dist,
    a.id AS rowid,
    FROM line a
    JOIN line b
    ON a.id = b.id + 1
)
UPDATE line a
SET m = FLOOR(next.m + next.dist)
FROM next
WHERE a.id = next.rowid
RETURNING a.m, next.dist;

or
update line a
    set m = FLOOR(a.m + prev.dist)
    from (select
            l.*,
            ST_Distance(ST_Transform(
                lag(geom) over (order by l.id asc)
            , 3857), ST_Transform(geom, 3857)) * cosd(42.3521) as dist
            from line l
          ) AS prev
    where prev.id = a.id - 1
    returning prev.m, prev.dist, a.m

Any ideas?


texts:
I have added an extra Slave server to an existing MySQL Replication. The Master server and the old Slave server are working fine without any issue, but the newly added server is stoping with the following error:

  Last_SQL_Errno: 1032
                 Last_SQL_Error: Could not execute Update_rows event on table xxx.email_events; Can't find record in 'email_events', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysqld-bin.000410, end_log_pos 368808733

It will be fine for some hours after repairing.

Questions

I'm a newbie,

how can I resolve this issue?
Can we permanently skip Last_SQL_Errno: 1032?
Is there any issue with skipping this error?


texts: MySQL Query was killed, but caused mysql database to not accept new reads or writes

texts:
This has happened a few times before and on all of those times I was not able to find an answer on the internet and I've had to completely uninstall MySQL and reimport the database from a backup. Although right now there's a little more than 1 billion rows so that's not something I see as an acceptable solution at this time.

In the processlist there are four queries showing as "sending data" and all of them are killed. Below that there are about 100 more listed as "statistics" which cannot be killed either.

Is there a way I could fix this without reinstalling MySQL?

Edit: Ubuntu 16, mysql newest


texts: How to select blocks from a result by condition?

texts:
Example table:

master_id 1 created_at 22.02.1997
master_id 1 created_at 22.03.1997
master_id 1 created_at 22.04.1997
master_id 1 created_at 22.07.1997
master_id 1 created_at 22.08.1997
master_id 1 created_at 22.10.1997

Example query result:

master_id 1 date_from 22.02.1997 date_to 22.04.1997
master_id 1 date_from 22.07.1997 date_to 22.08.1997
master_id 1 date_from 22.10.1997 date_to 22.10.1997

Note: The interval between dates must not be longer than one month for a block.


texts: Possible to detach a database from SQL Server while retaining file permissions?

texts:
When a user detaches a database from MS SQL Server, it sets the owner of the data and log files to that user. It also removes all non-owner permissions from the files. This is partially explained at http://msdn.microsoft.com/en-us/library/ms189128.aspx

I have tested this on the following versions of SQL Server

2005
2008
2008 R2

Is it possible to change this behavior so file permissions are retained when a database is detached?


texts: Is MongoDB secondary index size affected by primary key size?

texts:
If I understand correctly, secondary index leaf nodes in MySQL InnoDB engine hold the table primary key value (at least for a unique index).

Thus, looking up a value in the secondary index results in two BTREE lookups: one for the secondary index and another one for the clustered index (clustered on the primary key). This also means that the primary key size affects the size of all secondary indexes.

Is this how MongoDB WiredTiger secondary indexes work too? Or do MongoDB secondary indexes store a reference to the physical block where the document resides? (I believe this is how Postgres handles indexes)


texts: How to size SQL Server VM so that it is comparable to bare metal primary SQL server?

texts: Are there any guidelines for sizing a new Windows SQL Server VM in Azure so that it matches performance of the on-prem / bare-metal primary SQL server?


texts:
I am currently unsure of the correct terminology for types of tables that exist, or indeed whether there is a term for the examples I have.

I have included my current understanding of the types that I am aware of further down in this question.

Q: Please provide a list of the different table types, with brief definitions and examples to illustrate them.

Sidenote: The context to this is that I am currently attempting to reduce a database to a bare-bones version (for testing). As part of this I am hoping to identify tables that contain values to be looked up and will not change (eg job titles and department titles) vs the "data" tables which may be emptied with no ill-effect on the remainder of the database (cf, referential integrity in the case of adding a staff member where no departments or job titles are defined).


texts: log_warnings doesn't seem to work for aborted connections in MySQL 5.1

texts:
The docs for 5.1 say that I can set log_warnings = 2 to capture Aborted_connects in the error log.  This bug report suggests that this was only merged into 5.5 and 6.0 (and maybe 5.2) but not into 5.1.

I tried out all combinations of set global log_warnings = 2; and set global log_warnings = 1; followed by login attempts with valid and invalid users and invalid and no passwords results in nothing in the error log.

Are the docs incorrect or have I made a mistake somewhere?
Do other types of aborted connections (such as a loss of network during the login or improper permissions for the database) get logged in the error log?
Do aborted clients get logged in the error log?

Some other things I have checked/done:

log_error is set to /var/log/mysql/error.log.
That file exists and the mysqld process has it open.
The last entry in it is from 2012/06/01.
skip-log-warnings is not set in my.cnf and the process was started with no arguments.
skip_log_warnings does not exist in SHOW GLOBAL VARIABLES LIKE '%skip%'
The uptime of MySQL is 50 days.
I ran FLUSH LOGS just in case.
The version string is 5.1.62-0ubuntu0.10.04.1-log.
The Aborted_connects value is increasing by one every time I try an invalid connection attempt.


texts: Simple model database transaction log full 'CHECKPOINT'

texts:
I have a SQL Server 2012 SP1 database which has a problem that the transaction log is full. It consists of a single data file and a single log file, both about 800MB.

I had 5GB free disk space, and I've enlarged it to 20GB+, just in case there was a disk space problem.

The database is in SIMPLE recovery model.

I cannot do a full database backup - it fails with

  transaction log is full, with wait_desc of 'CHECKPOINT'

MTA: Note - the wait_desc is CHECKPOINT (someone asked below about the wait desc strings)

I cannot add a log file - it fails with the same error.

MTA: I cannot change the growth model of the one existing log file - it fails with the same error.

MTA: The existing log file has +10% growth.

MTA: I also cannot increase the size of the existing log file - it fails with the same error.

I cannot change the recovery model to full or bulk-logged - it fails with the same error.

I cannot execute a manual CHECKPOINT - it fails with the same error.

DBCC LOGINFO (dbid) shows that there are a couple hundred VLFs, all in status 2.

I'm not using change tracking, mirroring, or transaction log shipping.

What can I do to resolve this?

(As recommended, moved from stackoverflow to dba.stackexchange)

MTA: More tech details here (but no answers at all): http://www.sqlservercentral.com/Forums/Topic1451836-2799-1.aspx

eg, "DBCC OPENTRAN
gives me no open transactions"


texts:
I have to store some amount of data for analytical purposes.

The data source produces 2TB data per month.
Data is collected on a monthly basis (not real-time).
Data is fully structured.
There are 100+ different columns of data.
Availability of SQL is important.
Engineer/developer resources are limited.

I planned to use Postgres (probably with column-oriented extension), however, it would not be feasible for such data amounts (more than 20TB per year). I also made a research on Hadoop/Spark, however, it looks like a bit massive solution (considering, that the data is fully structured). I don't consider cloud-based solutions, as well as expensive ones (preferably, free-licence) .
Would you be so kind to suggest, which data storage to use for big amounts of structured data for analytical purposes?


texts:
I have a batch query that I'm running daily on my database. However, it seems to get stuck in idle state, and I'm having a lot of difficulty debugging what's going on.

The query is an aggregation on a table that is simultaneously getting inserted, which I'm guessing somehow relates to the issue. (The aggregation is on the previous days data, so the insertions shouldn't affect results.)

Clues

I'm running this inside a python script using sqlalchemy. However, I've set transaction level to autocommit, so I don't think things are getting wrapped inside a transaction. On the other hand, I don't see the query hang when I run it manually in sql terminal.
By querying pg_stat_activity, the query initially comes into the database as state='active'. After maybe 15 seconds, the state changes to 'idle' and additionally, the xact_start is set to NULL. The waiting flag is never set to true.
Before I figured out the transaction level autocommit for sqlalchemy, it would instead hang in state 'idle in transaction' rather than 'idle'. And it possibly hangs slightly less frequently since making that change?

I feel like I'm not equipped to dig any deeper than I have on this. Any feedback, even explaining more about different states and relevant postgres internals without giving a definite answer, would be greatly appreciated.


texts: Single volume for five Filestream databases

texts:
We are going ahead with PROD server setup. SQL Version 2014 AOAGs.
I have a question about the M:\ holding 5 Filestream databases totaling 1.5TB.
What will be the operational risks of running one single volume for all five databases?
I understand that this is the simplest form to deploy. However, in light of recent unforeseen Filestream volume issues...
Most likely, we will have 2.5TB of data in total in a couple of years.
From the SQL Server Technical Article FILESTREAM Storage in SQL Server 2008 by Paul S. Randal:

Physical Storage Layout:
Be sure to take into account the anticipated workload on a FILESTREAM data container when you are deciding where to place it, as well as the workloads on any co-located data containers or SQL Server files. Each FILESTREAM data container may need to be on its own volume, as having multiple data containers with heavy workloads on a single volume could lead to contention.
The point to take away here is that without thinking about the workloads involved, simply placing everything on one volume may lead to performance problems. The degree of separation required will vary from customer to customer.
It is also possible to create a table schema inside SQL Server that allows crude load balancing of FILESTREAM data between multiple volumes.


texts: Importing hundreds of CSV's into postgres (non-sequential naming)

texts:
https://gis.stackexchange.com/questions/132569/loading-multiple-csv-files-into-postgresql-table
Looked at links like this, but it doesn't really make sense to me.  Is there any way anyone could dumb this down for me?  I'm using ubuntu btw.  Thank you!


texts:
I have a front end that logs into the DB as "logInA", and only has permission to "schemaA" stored procs, which use EXECUTE AS "logInB", (I have also tried EXECUTE AS 'DBO'), to handle all the business logic.

I have run into a problem though, where a query tries to access a table on a Linked Server, and returns the error:

  Access to the remote server is denied because the current security context is not trusted.

If it helps, the source server is SQL 2008, and the linked server is SQL 2005.

Can any one help me as to how to resolve this?

Thanks in advance

C


texts:
I'll be going onsite to run some performance tests for a client.  They have a Windows Failover Cluster, which has a clustered instance of SQL Server.  Let's assume for this post that my client's Windows Failover Cluster has two nodes, as in the image below.

I'll be running a server-side trace (via a tsql script generated from SQL Profiler)--I'm quite comfortable doing this.  After I connect to EkTechready14.Redmond.com\MySqlInstance via SSMS and start the trace, I am of the opinion that it doesn't matter which node is active when the trace is running.  Do you agree?
I'll also be running Perfmon to gather some garden variety stats.  Maybe for Perfmon, it does matter which node is active.  Should I run Perfmon from the client access point (EKTechready14.Redmond.com)?  Or from the active node (either MSDEPLOY60-01 or MSDEPLOY60-02)?


texts: MongoDB document insertion and referencing

texts:
I am new in mongo and I have a sql background. I am currently working on  a project about migrating data from mysql to mongo. I have transformed my data into json. here is a sample.

{
"IMDBURL":"http:\/\/us.imdb.com\/M\/title-

 exact?Schrei%20aus%20Stein%20(1991)",
"release_date":"08-Mar-1996",
 "ratings":[{"user_id":916,"rating":3,"timestamp":"880845755"}],
 "genre":["Drama"],"video":"",
 "_id":1682,
 "title":"Scream of Stone (Schrei aus Stein) (1991)"
}

this is the first type of documents that I insert.Also in the same collection I insert some documents like this below. As i have read it is possible in mongo to insert different types of documents in the same collection

{
"zipcode":"77841",
"occupation":"student",
"gender":"M",
"_id":943,
"age":22
}

the user_id in the first document references to the _id in the second document.Because i insert many documents from each type I thought it would better to insert user informations on different documents and refer to them using their id. After reading the mongo documentation I am a little confused. Is this the best way to model the data or should I insert the second type of documents (user details ) on different collection. Also if I want to retrieve information of users or a user that makes a rating to a certain minutes how I am going to do it?


texts: Rolling up multiple rows into a single row and column from two tables

texts:
Here is my code

SELECT
              register cr.id,
              register_cr.first_name,
              register_cr.father_name,
              register_cr.surname,
              register_cr.address,
              cr_record.charge,
              cr_record.date,
              cr_record.charge_type,
              cr_record.fir_no
FROM
             register_cr as register_cr
INNER JOIN
             cr_record as cr_record ON register_cr.id = cr_record.cri_reco_id


texts: SQL Server blocking report with waitresource Database

texts:
We recently had a number of unexpected blocks occur on one of our servers. We captured the blocked process report and they are showing the waitresource as waitresource="DATABASE: 12:0". Below is an example of one of the reports.

<blocked-process-report monitorLoop="45476">
 <blocked-process>
  <process id="process7db4d70c8" taskpriority="0" logused="10000" waitresource="DATABASE: 12:0 " waittime="24750" schedulerid="4" kpid="4556" status="suspended" spid="96" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="1900-01-01T00:00:00" lastbatchcompleted="1900-01-01T00:00:00" lastattention="1900-01-01T00:00:00" clientapp="App1" hostname="ServerMainFrame.domain.COM" hostpid="0" loginname="LoginA" isolationlevel="read committed (2)" xactid="0" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack />
   <inputbuf>
   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="suspended" waitresource="DATABASE: 12:0 " waittime="24998" spid="150" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="1900-01-01T00:00:00" lastbatchcompleted="1900-01-01T00:00:00" lastattention="1900-01-01T00:00:00" clientapp="Microsoft JDBC Driver for SQL Server" hostname="Server02.domain.com" hostpid="0" loginname="loginA" isolationlevel="read committed (2)" xactid="0" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack />
   <inputbuf>
   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

I was not even sure this could be a waitresource or sure what it means. Generally, the waitresource I see is a table, page, row, etc. The obvious would be that there is a lock on the database and nothing can use it, but not sure how that would happen. Lock escalation maybe? So my question is if someone could help shine some light on what this waitresource is and what might cause it.

The issue was fixed before I could look at the activity while it was happening so just have the block report to go on. I tried googling this but could not find any info on this. Any help or pointers to a resource that will help is appreciated


texts: Best database system to store and compare date ranges

texts:
I'm working on a project which is dealing with a lot of date ranges.
I'm starting completely from scratch; there is no existing code, infrastructure or database. Therefore, I'm searching for the best technology for each job.

I need to store a lot of date ranges in a database. Later on I would like to compare those data ranges to find overlaps.

I´ve already read about elasticsearch and influxdb.

Do you know any database system which is specialized to store and work with a great amount of date ranges?

The date ranges could be stored as single days or as range like from 01.01.2016 to 08.01.2016. I don't want to store any other data besides an identifier, to identify each date range record.


texts: Installing Anti-Virus on Availability Group databases

texts:
Is it worth the risk to use Anti-Virus on Availability Group databases? Even following the guidelines given by Microsoft in How to choose antivirus software to run on computers that are running SQL Server.

We are planning to install Kaspersky.

It is a department decision to have all servers installed with Anti-Virus.


texts: need the most efficient way to handle unique constraint errors

texts:
I am looking for the most efficient way to solve this situation:

We are an Oracle shop.  I need to load data from one table to another.  My source table, which contains 30 million rows, has a person identifier that is supposed to be unique, but it is not enforced so there are cases where it is not - about .1% of the time.  There is logic I can use to resolve the non-unique cases.  I want my target table to "enforce" the uniqueness of the identifier - so I have defined this identifier as my primary key.

(Note that the data that is loaded isn't a straight source table -> target table; there are a few look-up tables that are used too)

My goal: load this table as quickly as possible.  It's a lot of rows, so efficiency is key.

I have thought of a number of ways to address this, but I'm not sure what would be the most efficient.  My ideas include:

Use MERGE (concerned about performance compared to an INSERT with an APPEND hint.)
Change the SELECT statement of the load to include the logic to address the the non-unique cases
Use error trapping to write the non-unique information to a separate table (where I would use the logic)
Drop the primary key, load the data, fix the issues, add the key back

I am sure there are other methods that I haven't listed.

So - what's the fastest way to accomplish this task?


texts: Joining on 3 different keys as they're set

texts:
I have these tables:

TABLE vessels
ID int PK
organization_id int FK
fleet_id int FK
name VARCHAR

TABLE settings
ID int PK
organization_id int NULL FK
fleet_id int NULL FK
vessel_id int NULL FK
some_value INT

In table settings, some_value can be either 1, 2, 3, 4, 5, 6 or 7

Some entries in the settings table are linked to a vessel_id based on it's fleet_id or organization_id

How do I go about selecting the settings for a vessel, if a vessel has settings via it's fleet_id or organization_id ?


texts: mySQL Quickly find rows linked to another row

texts: For example if I have a posts table with id and a likes table width id post_id user_id, how can I set up the database to organize the likes table by post_id in order so queries are faster? I can't make the post_id the primary key because there are more than one likes for each post.


texts: Postgres-9.1 having more Idle connections

texts:
The connections were created 2 days before. Can I kill the process manually by using the below query?

select pg_terminate_backend(procpid)
from pg_stat_activity
where datname = 'mydb'
and procpid <> pg_backend_pid()
and current_query = '<IDLE>'
and query_start < now() - INTERVAL '2880' MINUTE;


texts:
Please Help. I have been trying this for the past 3 days.

I need to send an alert mail when the SQL Server Agent is not running. That is, there can be instances where SQL Server is is running, but Agent is not running. I need to set up the same as startup procedure to check whether the agent is disabled or not when the service starts.I had already made the procedure and set up the same as start up procedure. I had also made a job based on the same, but had not scheduled.

Thanks in advance. Please help


texts: Can we have dynamic SQL jobs in SQL Server Availability groups

texts:
We need to push all sql data changes to our local caching infrastructure in real time. We are using SQL Server 2012 Availability Groups. In the process of avoiding triggers for population SQL Service broker queues, I am researching the use of SQL jobs as explained in this article: Async SQL execution

By calling this sql procedure from insert/update/delete stored procedures should avoid the usage of triggers. Creating/deleting sql jobs dynamically from stored procedure is valid/allowed in sql availability groups?

The SQL Agent Job will allow the data to be manipulated prior to sending the message to the Service Broker Queue.


texts: Different behavior between MySQL 5.5 and 5.6

texts:
I have this schema with data:

CREATE TABLE `ticket` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `type` INT(11) NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `task` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `ticket_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_task_1_idx` (`ticket_id`),
  CONSTRAINT `fk_task_1` FOREIGN KEY (`ticket_id`) REFERENCES `ticket` (`id`)
) ENGINE=InnoDB;

CREATE TABLE `planing` (
  `id` int(11) NOT NULL,
  `date` datetime DEFAULT NULL,
  `task_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_planing_1_idx` (`task_id`),
  CONSTRAINT `fk_planing_1` FOREIGN KEY (`task_id`) REFERENCES `task` (`id`)
) ENGINE=InnoDB ;

INSERT INTO `ticket` (`id`, `name`, `type`) VALUES (1, 'ticket_1', 1);
INSERT INTO `task` (`id`, `name`, `ticket_id`) VALUES (1, 'task_1', 1);
INSERT INTO `task` (`id`, `name`, `ticket_id`) VALUES (2, 'task_2', 1);
INSERT INTO `planing` (`id`, `date`, `task_id`) VALUES (1, '2016-06-01 05:00', 2);

INSERT INTO `ticket` (`id`, `name`, `type`) VALUES (2, 'ticket_2', 1);
INSERT INTO `task` (`id`, `name`, `ticket_id`) VALUES (3, 'task_3', 2);
INSERT INTO `task` (`id`, `name`, `ticket_id`) VALUES (4, 'task_4', 2);
INSERT INTO `planing` (`id`, `date`, `task_id`) VALUES (2, '2016-06-01 05:00', 3);
INSERT INTO `planing` (`id`, `date`, `task_id`) VALUES (3, '2016-07-01 05:00', 4);

INSERT INTO `ticket` (`id`, `name`, `type`) VALUES (3, 'ticket_3', 1);

When I perform a select:

SELECT ticket.id AS ticket_id, task.id AS task_id, planing.date as due_date
FROM ticket
  LEFT JOIN task ON task.ticket_id = ticket.id
  LEFT JOIN planing ON (task.id = planing.task_id AND planing.date IS NOT NULL)
WHERE ticket.type = 1
GROUP BY ticket.id
ORDER BY planing.date ASC
;

on MySQL 5.5 I get

ticket_id | task_id | due_date
1         |1        |null
3         |null     |null
2         |3        |2016-06-01 05:00:00

and on MySQL 5.6 I get

ticket_id | task_id | due_date
3         |null     |null
1         |2        |2016-06-01 05:00:00
2         |3        |2016-06-01 05:00:00

example link

How to get the same behavior on MySQL 5.5 as on MySQL 5.6 (app server uses the MySQL 5.5 version)?