texts: How to resolve this 'XML Validation: Declaration not found for element' error?

texts:
I'm new to SQL Server and I have this task to complete (for learning purposes), yet I'm unable to figure out this error due to inexperience with XML Schemas (I think)

Here's what I'm trying to do -

Create a table with 2 columns - ID, XML info for some product (non-typed).
Add a row entry, that is, XML data for a product.
Create an XML Schema for the XML-product-data.
Bind this schema to the one (column) in the table.

I did this similar to what is mentioned here.

-- Create table ProductDocs
CREATE TABLE ProductDocs
(
    ID INT IDENTITY PRIMARY KEY,
    ProductDoc XML NOT NULL
);

-- Insert data into ProductDocs
INSERT INTO ProductDocs
(ProductDoc)
VALUES ('<Product>
            <ProductID>1</ProductID>
            <ProductName>Chai</ProductName>
            <SupplierID>1</SupplierID>
            <CategoryID>1</CategoryID>
            <QuantityPerUnit>10 boxes x 20 bags</QuantityPerUnit>\
            <UnitPrice>18.0000</UnitPrice>
            <UnitsInStock>39</UnitsInStock>
            <UnitsOnOrder>0</UnitsOnOrder>
            <ReorderLevel>10</ReorderLevel>
            <Discontinued>0</Discontinued>
        </Product>');

-- Create XML Schema
CREATE XML SCHEMA COLLECTION XS_ProductDoc
AS '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
               targetNamespace="http://www.microsoft.com/schemas/adventure-works/products"
               xmlns:prod="http://www.microsoft.com/schemas/adventure-works/products">

        <xs:element name="Product">
            <xs:complexType>
                <xs:sequence>
                    <xs:element ref="prod:ProductID" />
                    <xs:element ref="prod:ProductName" />
                    <xs:element ref="prod:SupplierID" />
                    <xs:element ref="prod:CategoryID" />
                    <xs:element ref="prod:QuantityPerUnit" />
                    <xs:element ref="prod:UnitPrice" />
                    <xs:element ref="prod:UnitsInStock" />
                    <xs:element ref="prod:UnitsOnOrder" />
                    <xs:element ref="prod:ReorderLevel" />
                    <xs:element ref="prod:Discontinued" />
                </xs:sequence>
            </xs:complexType>
        </xs:element>

        <xs:element name="ProductID" type="xs:integer" />
        <xs:element name="ProductName" type="xs:string" />
        <xs:element name="SupplierID" type="xs:integer" />
        <xs:element name="CategoryID" type="xs:integer" />
        <xs:element name="QuantityPerUnit" type="xs:string" />
        <xs:element name="UnitPrice" type="xs:double" />
        <xs:element name="UnitsInStock" type="xs:integer" />
        <xs:element name="UnitsOnOrder" type="xs:integer" />
        <xs:element name="ReorderLevel" type="xs:integer" />
        <xs:element name="Discontinued" type="xs:boolean" />

    </xs:schema>';

-- Bind the schema
ALTER TABLE ProductDocs
ALTER COLUMN ProductDoc xml (XS_ProductDoc);

But I'm getting this error -

  XML Validation: Declaration not found for element 'Product'. Location:
  /*:Product[1] The statement has been terminated.

Why is this error occurring?
And, how can I resolve it?


texts: Searching & deleting content that starts & ends the same, but has random text in between

texts:
So my Wordpress sites been hacked & spuriously created a load of content in the posts which starts with  & ends with .

The only trouble is that the bit in between is random hyperlinks.  Any idea how I go about writing a sql script to grab & delete it.

So in theory:

<div style="position:absolute;filter:alpha(opacity=0);opacity:0.001;z-index:10;">  %  </div>


texts: Where is the generic plan stored when using PREPARE statement in PostgreSQL?

texts: According to the document, if a prepared statement is executed enough times, the server may eventually decide to save and re-use a generic plan rather than re-planning each time. My question is where the generic plan is saved? Is it stored in a file in disk? If yes, where is it located and what is the file name?


texts: Improve update performance in PostgreSQL data warehouse (RDS)

texts:
I am using a Postgres data warehouse hosted on Amazon RDS. When trying to update one column of a fact table (25 million rows) from another table in the same database, the query takes several days to run. Why is this happening and how can I improve this performance? I know that PG is designed more for OLTP than OLAP, but select query performance is usually pretty decent on this table.

The query in question looks like this:

UPDATE a
SET a.value = b.value
FROM b
WHERE a.id = b.id

b is a temp table in a different schema but same database that has the same number of rows as a. Both tables have primary keys on id. There is no index or constraints on the value column. There are views that depend on table a but no foreign keys

I am using PG 9.5 on RDS. General purpose (SSD) with 256 GB of storage, so after exhausting our initial burst IOPS, I should get a little under 800 IOPS.

Is the IOPS throttling really the issue here? While watching the query run I see ~ 400 IOPS of write performance, and similar read performance. 25,000,000 rows / 400 IOPS = 17 hours, but this query took much longer than 24 hours to run ( cancelled after ~ 30 hours to try and make tweaks). There was some other periodic update traffic on the same table, but I halted this at around the 20 hour mark when I saw how long this query was taking.

I wondering if my general update approach is wrong, or if there is general advice for operating a data warehouse (OLAP workload) using postgres. Could I get better performance by ditching RDS and running an PG on EC2?

UPDATE:
Inspired by responses and comment, I ran a test on 45k rows (by limiting the pk below a certain range)

You can see the results of explain analyze here. The vast majority of the time is spent writing the actual updates to the table. Right now I am still leaning towards write IOPS being a limiting factor, but I will dig into possible replication issues as mentioned by joanolo.

This image shows the RDS instance monitoring page. The most recent spike is the query in question.


texts:
I was wondering is it feasible or doable to work from home as a DBA or Database designer/developer or Business intelligence developer.
I have seen developers doing great working from home, but can DBAs work from home and be productive or do they have to physically be at work.

Do you know any DBA working from home.
If yes how is he managing it and what challenges is he facing
Is it more productive to work from home as a DBA


texts: Keyword Search a single SQL Server table for multiple search terms and create a new table from results?

texts:
I am not a database administrator. Most of my experience is with MySQL. However I think what I want to do is in the realm of possibility.

I want to text query a single large table in a SQL Server database for about 30 keyword terms. I then want the results to be dumped into a new table on the same database. I want the terms found to include entire field they were found in. (other items in row don't matter).

I am trying to produce data from the database to provide to a outside party. The table I want to search is to large to export to any format.

Is this possible?


texts: MongoDB secondaries become unresponsive during replication

texts:
We got 3 Servers running. One Primary, two as secondaries. Primary has 4 vcpus, 16 GB memory, both secondaries have 8 vcpus, 64 GB memory.

Every night, we run a full sync with several large collections on the primary with multiple threads.

During that sync, both secondaries become unavailable from time to time.
mongod.log states following notice:

serverstatus was very slow: { after basic: 0, after asserts: 0, after
backgroundFlushing: 0, after connections: 0, after dur: 0, after extra_info:
0, after globalLock: 0, after locks: 0, after network: 0, after opLatencies:
0, after opcounters: 0, after opcountersRepl: 0, after repl: 0, after
security: 0, after storageEngine: 0, after tcmalloc: 0, after wiredTiger:
4992, at end: 4992 }

Mongostat during that time states:

Our clients have readPreference set to secondaries only, but we dont have much connection during that time tough.

Standard is default, so default mongodb config with no special tweaks.

So the only thing is see is, that the mongodb log states an "after wiredTiger" message with an higher amount of time. Any clue what's happening here?

Used mongoDB Version is 3.4.16


texts: Is this the right attitude from the DBA team?

texts:
I'm a software developer at a rather large company with a small team of software developers. I am the only remaining developer (.NET developer) in the "reports" team of the company. The rest of the developers (7 in total) are in the transactional part of the software. There are two "report analysts" with me.

This company has three full-time DBAs. But lately I've been receiving what, in my opinion, are too many tickets from the DBAs. Most of these tickets are complaints from the DBAs that our scripts are running "too slowly". They decided that 3 minutes should be enough for any script, and anything that takes more than 3 minutes needs fixing. The other team, the transactional team, basically just does CRUD operations (mostly C, we're into "tens of millions of rows" territory). The company has been bought by a larger company and dozens of people were laid off. There is no money for new servers (production is a '09 Xeon with 24GB RAM, and 10K SAS).

The problem seems to be that the DBA expect any script to run under 3 minutes, except of course, being the reports person, I have to sift through millions of records to get the required statistics (our reports are done nightly because of that reason). The transactional team has also broken down the database to textbook normal form. This means most scripts start off 10 INNER JOINs.

The DBAs messages aren't very helpful. I'm often required to look at query plans and make sure the indices are used. They expect the developers to worry about index creation and preformance. I'm not really sure that, as a software developer, it's my job to worry about indices.

I also have no production credentials, so I have no way of knowing if certain indices are present in production (often I get tickets back that "this table has more indices than data!!!!"). I also have no SP execution permissions (big no, developers have only SELECT permissions in production). And certainly not SHOWPLAN permissions.

The main issue seems that we don't have a copy of production to test our queries against. There is a mockup with a few records, and of course any script runs really quickly against a few records but runs slow on production. So basically I have to make guesses about SQL performance tuning, send the script out to the DBA to deploy (that seems to be their job description), and wait for their ticket next day to tell me in what new way the script has failed or made things worse.

Today I received a ticket with the following description: "X sp taking longer than 3 minutes. Replace CURSOR with WHILE", so this is why I'm asking:

Is this normal procedure for DBA operations? Am I wrong to assume that, if an SP is underperforming, it should be the DBA's job to optimize it?
In case it's not the DBA's job, but mine, should I have more permissions to the production DB for the required traces, query plan, SP execution, etc?
Is it normal not to have a copy of the production DB to test large queries against?

I would like to present my case to the boss here, but I want to be sure I'm not wrong about where my responsibilites as a .NET developer end.


texts: How can I retrieve the image which is stored in bytea column of a pg_toast schema?

texts:
I have a table called photo, that contains 3 columns

chunk_id oid
chunk_sq int
chunk_data bytea

How can I see the stored image?

The (bytea) type column contains data like

'034\350sec\302\340s\221\\030seq\301\037\022\034seq`uence!V!\364s\030ett\241\376\001\310sho\214rt\224\204\002Tize\001\240\370sloDR#\024\002\020\023\273\003\240\360ourc\324\.................'

select * from pg_toast.photo


texts:
I'm considering changing the SQL Server differential backup interval for simple recovery databases from every 2 hours to every 4 hours with one daily full backup. A four hour data loss is acceptable per SLA. Considering SQL Server will automatically issue checkpoints, log clearing will continue and doesn't appear to be a concern at this point. Before making this change should I monitor data churn or checkpoint occurrences? Thus far I'm thinking this is truly a RPO/SLA concern or issue. Let me know if I'm missing anything.

Thanks in advance!!!


texts: How to recover the innodb when mysql data dir moved from one system to another system?

texts: I have transfer the mysql data dir from one linux box to another box. When i start the mysql server. I can find that the innodb based DB are corrupted. So, may i know how to recover the corrupted innodb


texts: Inserting result of stored procedure into a table with additional column

texts:
I've found hundreds of articles about how to insert output of stored procedure into a table but nobody goes a step further and explains how to insert additional data as column.

I'm looping through a table and execute store procedure which I insert details into tempTable however I need to add additional column with an ID from second table. I could change stored procedure to return that additional ID however I need to run it on 20+ databases on different servers which would be time consuming.

DECLARE @c_id_view varchar(255)
DECLARE @c_id int

DECLARE cur CURSOR FOR SELECT c_id FROM #temp where ins != 'Health'
OPEN cur

FETCH NEXT FROM cur INTO @c_id_view

WHILE @@FETCH_STATUS = 0 BEGIN
set @c_id = (select c_id from claim where c_id_view = @c_id_view)
insert into #tempbedsNONIL (c_id_view,room_Type,ward_name,common_bed_name,bed_name,no_of_bed,no_of_days) values (@c_id_view, usp_patient_transfer_retrieve_forPDF @c_id)  -- call your sp here
FETCH NEXT FROM cur INTO @c_id_view

END

CLOSE cur
DEALLOCATE cur

I know that to insert SP results into a table I just need to do INSERT INTO #TEMP EXEC sp_test but in my case I need to enter extra value. This might be a stupid question but I can't figure out how to achieve it.


texts:
I am going through my database and what I want to do is re-design it because I feel like it's very messy and that I am doing it wrong. This is just for practice; I've never had anyone teach me so I would love to learn new ways and how to approach this.

What I have is a users table with the following fields:

id, admin, active, username, password, business_name, address, city, state, zip, phone, email, hours, hours2, credit_cards, logo_ext, package, theme, header_image, facebook_link, twitter_link, analytics, analytics_id, website_url, banner_image, member_forgot, home_page, about_page, contact_page

Wow, pretty messy right?  Here are a few things I see:

admin and active is just a bunch of 0's and 1's so can I do something like this? Since there will be 99% active and only a couple that will be inactive.

admin  - id, user_id

inactive - id, user_id

social - id, user_id, facebook_link, twitter_link

member_forgot - id, user_id, forgot_code

I'm not too sure about the rest.   Just to explain a few of the fields:

Hours = Monday through Friday hours
Hours2 = Saturday hours
Credit_Cards = 1110 format (visa, mastercard, discover, amex)
logo_ext = jpg, png - stores it when you upload logo
package = 1, 2, or 3. Different level. Each level has its own template.
theme = what stylesheet to use
header_image = which image to use on the header for that account.
analytics_id = I use piwik, so piwik stores a unique id that I need to store
banner_image = 1-30 of different images there are to choose from
pages = stores the text in the database, which they can change via wysiwyg editor.

I really want to practice splitting it up in different tables even if it is not a huge database, or is that a bad idea? So if I delete a user from the users table, it will delete all of the other records in the other tables associated with that user_id?

Any suggestions / help is appreciated.  I love to learn new things and am very interested in making this database more efficient and flexible.

Thanks so much.


texts: How to implement posts with “seen by” like facebook?

texts:
Inside a Facebook group there is a feature when every time see the group newsfeed it mark all posts as seen by me.

And if I see a specific post inside a group thought a url (facebook.com/groups/123/posts/123), it mark as seen this single post.

My question is about what is the intuitive way and right to model this and performance. I want to know what I am missing or wrong about about to get it right.

What happens on facebook: "When a new member join the group, all the old posts are marked as seen by him". But is it per post or global?

Scenarios:

Mark as seen all individual posts when click group.

Pros:

Easy development
Easy and fast queries. Could implement a int counter column on each post and a has_many table users_id x post_id
Getting and individual post I can tell how many users saw without context

Cons:

How to deal with a new member join a group with > 100 posts? Batch writes?

2.Mark as seen the group when click group.

Pros:

One single write when seen a group

Any tips and advices are welcome!
Thanks!


texts: How to run a long query from a trigger without upset

texts:
I have some log records that are inserted into a table in my database. When they land, I would like to process them asap. The processing could potentially be a bit lengthy.

I would just write a trigger to do the processing, but I'm worried it will cause problems if it takes a while.

Is there a way to call a stored procedure asynchronously from a trigger? Or perhaps a better way of doing this altogether?


texts: Automatic inheritance of permissions, when databases are added afterwards

texts:
How do I give a user automatic access to all databases that's being created or imported to a database server?

I have a selection of one user and two activedirectory-groups which have to automatically given access to everything created, restored and mounted. We are not able to do this manally as is appear to be at this moment.

Thank's in advanced,


texts: Postgres cost of large volume of inserts in many tables in a single transaction

texts:
What is the additional cost incurred by inserting large volume or rows (millions), in many tables in a single transaction ?

Can something be done (tuning parameters) so that the cost of inserting in large volume in a single transaction approaches the cost of doing it in autocommit ?


texts:
I'm using ArchLinux and PostgreSQL 9.4.4 and have enabled the logging in the config file:

$ sudo egrep -v "^[[:blank:]]*($|#|//|/\*| \*|\*/)" /var/lib/postgres/data/postgresql.conf

max_connections = 1024          # (change requires restart)
shared_buffers = 128MB          # min 128kB
dynamic_shared_memory_type = posix  # the default is the first option
logging_collector = on      # Enable capturing of stderr and csvlog
log_directory = '/tmp'      # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
log_file_mode = 0644            # creation mode for log files,
log_error_verbosity = verbose       # terse, default, or verbose messages
log_statement = 'all'           # none, ddl, mod, all
log_timezone = 'Asia/Jakarta'
datestyle = 'iso, dmy'
timezone = 'Asia/Jakarta'
lc_messages = 'en_CA.UTF-8'         # locale for system error message
lc_monetary = 'en_CA.UTF-8'         # locale for monetary formatting
lc_numeric = 'en_CA.UTF-8'          # locale for number formatting
lc_time = 'en_CA.UTF-8'             # locale for time formatting
default_text_search_config = 'pg_catalog.english'

then restart or reload the service:

sudo systemctl restart postgresql
sudo systemctl reload postgresql

the loaded settings:

postgres=# SELECT name, setting FROM pg_settings WHERE name LIKE '%log%';
            name             |            setting
-----------------------------+--------------------------------
 log_autovacuum_min_duration | -1
 log_checkpoints             | off
 log_connections             | off
 log_destination             | stderr
 log_directory               | /tmp
 log_disconnections          | off
 log_duration                | off
 log_error_verbosity         | verbose
 log_executor_stats          | off
 log_file_mode               | 0644
 log_filename                | postgresql-%Y-%m-%d_%H%M%S.log
 log_hostname                | off
 log_line_prefix             |
 log_lock_waits              | off
 log_min_duration_statement  | -1
 log_min_error_statement     | error
 log_min_messages            | warning
 log_parser_stats            | off
 log_planner_stats           | off
 log_rotation_age            | 1440
 log_rotation_size           | 10240
 log_statement               | all
 log_statement_stats         | off
 log_temp_files              | -1
 log_timezone                | Asia/Jakarta
 log_truncate_on_rotation    | off
 logging_collector           | on
 syslog_facility             | local0
 syslog_ident                | postgres
 wal_log_hints               | off
(30 rows)

the /tmp permission:

$ ll /tmp/
total 11656
drwxrwxrwt 22 root root       580 Jun 29 10:05 ./
drwxr-xr-x 17 root root      4096 Jun 10 08:04 ../
...

the /tmp usage:

$ df | grep /tmp
tmpfs            8159996    31204   8128792   1% /tmp

doing some query on psql, but the /tmp/postgresql-* still doesn't show up. What can I do to find which part that prevent log from being created?

EDIT 1

the result of journalctl -u postgresql

Oct 08 09:13:53 xxx postgres[19861]: LOG:  00000: redirecting log output to logging collector process
Oct 08 09:13:53 xxx postgres[19861]: HINT:  Future log output will appear in directory "/tmp".
Oct 08 09:13:53 xxx postgres[19861]: LOCATION:  SysLogger_Start, syslogger.c:645
Oct 08 09:13:55 xxx systemd[1]: Started PostgreSQL database server.


texts: If a Login is not granted the db_ddladmin role on a database, but is individually granted CREATE VIEW on it, will they still be denied?

texts: Since SQL Server permissions work as "deny first", I was wondering if that applies downward to individual permissions that are granted, if the parent permission isn't granted e.g. db_ddladmin is not granted but CREATE VIEW is for the Login.


texts: Creating a copy of primary database and attaching the copy database in the same sql instance

texts:
I just got into a project in which we have only one SQL Server 2012 copy. We have 12 databases attached to the primary instance of SQL Server 2012.

As I don't have SQL Server 2012 or supporting OS, I have to depend on the primary instance of SQL Server 2012.

I am not sure whether it is good to create copy/clone of all 12 databases and attach it to same instance and work on the copy for data manipulation and query testing purpose.

Based on Kin's query

SELECT
   SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
   SERVERPROPERTY ('ServerName') AS SQLServerName,
   SERVERPROPERTY ('Edition') AS ServerEdition,
   CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + '
       + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
       + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion,
    RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],
 SERVERPROPERTY ('Collation') AS Collation

Following is the output. I ignored PhysicalMachineName and SQLServerName.

ServerEdition : Business Intelligence Edition (64-bit)
ProductVersion : SQL Server 2012 + RTM + (Build11.0.2100.60)
O.S.           : Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Collation      : SQL_Latin1_General_CP1_CI_AS


texts: Rotating a table with multiple value columns


texts: SQL: revoking permissions from public in master

texts:
By default SQL grants execute permission on stored to public and also select to some table and views in Master to public...I am not 100% sure about this... What is the implication if we have to remove the select privileges on those objects from Public on Master?

Thanks...


texts: MongoDB Sharded Cluster: Balancing chunks not working, stuck at "step 2"

texts:
I've one collection enable sharding by id key on 2 shard rs01 and rs02

Today I added a new shard rs03 into cluster.
I've checked balancer status by sh.status() as below:

shards:
    {  "_id" : "rs01",  "host" : "rs01/mongo30-01:27017,mongo30-02:27017" }
    {  "_id" : "rs02",  "host" : "rs02/mongo30-03:27017,mongo30-04:27017" }
    {  "_id" : "rs03",  "host" : "rs03/mongo30-05:27017,mongo30-06:27017" }
  balancer:
    Currently enabled:  yes
    Currently running:  yes

and some chunks already migrated to rs03

{  "_id" : "myDB",  "partitioned" : true,  "primary" : "rs01" }
    myDB.myCo
        shard key: { "_id" : 1 }
        chunks:
            rs01    20027
            rs02    19532
            rs03    8

I found I set too few memory on rs03.
I stopped mongod service on rs03, added memory and start it after reboot.

After many hours, I found the migrated chunks still 8.
I checked the operation in rs03 primary by db.currentOp() as below:

{
  "inprog": [
    {
      "desc": "migrateThread",
      "threadId": "0x22677e00",
      "opid": 206642,
      "active": true,
      "secs_running": 19754,
      "microsecs_running": NumberLong("19754679731"),
      "op": "none",
      "ns": "myDB.myCo",
      "query": {

      },
      "msg": "step 2 of 6",
      "numYields": 0,
      "locks": {

      },
      "waitingForLock": false,
      "lockStats": {
        "Global": {
          "acquireCount": {
            "r": NumberLong("662"),
            "w": NumberLong("660")
          }
        },
        "Database": {
          "acquireCount": {
            "r": NumberLong("1"),
            "w": NumberLong("659"),
            "W": NumberLong("1")
          }
        },
        "Collection": {
          "acquireCount": {
            "r": NumberLong("1"),
            "w": NumberLong("330"),
            "W": NumberLong("1")
          }
        },
        "oplog": {
          "acquireCount": {
            "w": NumberLong("328")
          }
        }
      }
    },

the opid kept as "step 2 of 6" for many hours.
Anything I can do to make the migration going on?
I tried to kill the opid and it forked again still "step 2 of 6" now.
and the chunks in sh.status() still 8.


texts: PostgreSQL: How does COPY FROM behave inside a transaction?

texts:
I have an 8-million-line CSV file I need to import into a table. This import will currently take place within a transaction.

Does COPY FROM create a subtransaction? Will a failure roll back the parent transaction, or leave it alone?


texts: Find columns with different values compared with a reference row

texts:
My table, MARC, has more than 200 columns with WERKS (plant) as the key.
Most of the entries in MARC have values that come from a reference plant. So, usually, all the 200 columns will contain the same values.
I need to find the columns that have different values compared with the reference plant row, in order to find the deviations and show the names of the columns that had different values.
The main question is: how to do the select when it is not known which columns need to be selected.
Example

In the above, ABAS is the reference plant.
I expect only the first row to be returned, because only that row contains any differences compared with the reference row. Differences in that row occur in the following columns:

WERKS
PSTAT
DISMM
DISPO

The returned row should contain only those four columns.
I do not need any other columns to be able to show on the UI that these 3 columns have different values for that particular WERKS.
I am using SAP HANA.


texts: Design: Postgres, Parent to Child / Child to Parent / Child to Child | JSONB

texts:
I am very new to database design. I am hoping for suggestion or solutions for a feature I am working.

Note: We are majorly using JSONB type in Postgres

Feature:
I have a orders table which would contain the every order information, every order has a unique items user ordered.

Sample Order Schema

{
    id: "parent_order_id_uuid",
    "items": [
        {
            item_id: "uuid-#1",
            details: {
                etc....
            }
        },
        {
            item_id: "uuid-#2",
            details: {
                etc....
            }
        }
    ]
}

I am working on a feature were users can initiate a replacement for the item he purchased for n-number of times.

For Eg - "uuid-#1" can be replaced separately and "uuid-#2" can be replaced separately or both "uuid-#1" & "uuid-#2" n-number of times.

so every time user replace an item I need to create a new order which would only contain the replaced item only.

Sample Replacement Order:

{
    id: "order_id_uuid",
    "items": [
        {
            item_id: "uuid-#1",
            details: {
                etc....
            }
        }
    ]
}

{
    id: "order_id_uuid",
    "items": [
        {
            item_id: "uuid-#2",
            details: {
                etc....
            }
        }
    ]
}

Requirements:

At any point in time, i need to able to create a tree-like structure based on the "parent_order_id_uuid".
At any point in time, I need to able to query the replaced order based on the "parent_order_id_uuid".
I need to have relationship between parent_order_id and replacement_orderIds + parent_item_id and replacement_ids + replacement_ids and replacement_ids

How to store this data in Postgres to get the all this requirement, Once again, I am okay with storing this data as JSONB. Some design suggestions will be helpful for me.

(Parent to Child + Child to Parent + Child to Child)

Simply to Put my requirements:

Parent to Child for N-Times

{
  "parent_order_id_uuid": [
    {
      "replacement_order_id_1": {
        "items": [
          {
            "parent_item_id": "uuid-#1",
            "item_id": "uuid-#3"
            "details": {
                etc...
            }
          }
        ]
      },
      "replacement_order_id_2": {
        "items": [
          {
            "parent_item_id": "uuid-#2",
            "item_id": "uuid-#4"
            "details": {
                etc...
            }
          }
        ]
      }
    }
  ]
}

If this is not very verbose, please let me know.

P.S: This is my first question in Database Stack.

Thanks in Advance for helping minds.


texts: Update code works in manual stored procedure but not when run as SQL Server Agent Job

texts:
I have a stored procedure that deletes all records from an existing table, and then inserts new records. After the insert I have several update operations that change the value of one field.

If I run the SQL Server Agent job, the update does not happen. If I select and execute the update code manually, the update occurs.

If I execute the entire stored procedure manually, the update occurs.

No errors are thrown; everything 'works' but the update does not occur when I kick off the SQL Server job.


texts: How can I update a single field in an array of embedded documents?

texts:
I would like to update a field in an array of embedded documents.

My sample document is:

db.students.insert(
    {
        first_name:'AA',
        last_name:'BB',
        cours_reussis:[{intitule:'PAI',note:0,name_school:'EPFC'}]
    }
)

I'd like to change the value of name_school to "ENSA" instead of "EPFC".


texts: Advisable to restart a SQL Server running Windows when disk space is low?

texts:
We've had a sudden drop in hard drive space on the OS partition of our SQL Server (as databases are being stored there). It's causing various issues, and we're unable to move one of the databases, as the OS now seems to be placing pre-emptive locks on the database to ensure that the machine at least stays running.

We've tried everything we can think of. Is it advisable to now stop the SQL Server instance then restart Windows?

We're thinking this is the only way to clear things up, however our infrastructure guy is concerned that Windows will first try to write its state to disk before it restarts which could make the issue worse.

Any advice/experience in this situation?


texts: What User Level is needed for Transactional Replication

texts:
I've been setting up Transactional Replication between two database servers I have running. My first experiment with setting it up I simply used SA and local Admin account for all of the agents. Because of security reasons I would not want to have agents running around with more rights than they actually need. I tried using DB_Owner for sql accounts and just a regular Windows user however it seems that Transactional Replication isn't working with those settings. ( I might have messed up somewhere)

My question is: What is the minimum level of rights that is required by each agent in order to run Transactional Replication successfully?

Side note: One server is running SQLServer 2012 and the other 2008


texts: Generic log-table for CRUD operations that support both single and composite Primary Keys

texts:
I'm trying to come up with a decent table design for a table that will log CRUD operations for a small application. There won't be a lot of traffic, which could justify a single table for all events. So far, my idea looks like this

ID int identity primary key,
[Schema] nvarchar(128),
[Table] nvarchar(128),
ObjectID int, -- primary key of the object we're logging
Action nchar(1) , -- the type of event (I/U/D, short for INSERT, UPDATE etc
Field nvarchar(128), -- the changed column, in the event of UPDATE
OldValue nvarchar(1000),
NewValue nvarchar(1000),
ChangedAt datetime2,
ChangedBy nvarchar(128)

This would cover most of our use cases. There is, however, one itch with this design, and that is when I want to log changes to records where the Primary Key spans multiple columns, aka a Composite Primary Key.

In order to satisfy this need, I would have to extend the proposed table-design with yet another ID-column, allowing us to store both sides of the composite primary key.

You probably see that this is heading down the wrong path. What if I later need to log events for a table containing a Primary Key spanning 3 columns or more? Simply extending the log-table with "ObjectID_2" and "ObjectID_3" columns sounds asinine. Not to mention that the majority events will only have NULL-markers in those columns, as most our tables use single-column primary keys.

I am no fan of generic tables and column in general, and I'm starting to think a better solution would be to keep a separate log-table for each table that needs auditing. Am I going about this the wrong way?

Change Tracking does not solve this problem. The log-table needs to be exposed to the web and in a human-readable format. I have considered both CDC and Temporal Tables, but then we'd already be in the realm of "log everything for a single table", which is not my use-case. The log-table will be used by the application to log whatever events they care about, not every DML operation. That's is why I opted for a single-table solution.


texts: select for update gives error on indexed column

texts:
here is my code:

mysql_query("start transaction");
$q="select max(id) from test for update";
$r=mysql_query($q);
$row=mysql_fetch_array($r);
for ($k=0;$k<100000;$k++)
{
$q="insert into test values ($row[0]+$k)";
$r=mysql_query($q);
}
mysql_query("commit");

if i run code with 2 browser if id column is not indexed code run correctly-  second session wait until first session commit -.

but if id column is indexed i get this error "mysql_fetch_array() expects parameter 1 to be resource, boolean given " on second browser - waiting session -.

when i use mysql_error() before mysql_fetch_array() i get this error "Deadlock found when trying to get lock; try restarting transaction". why?


texts: Conceptual Diagram advice for varying Product and History states

texts:
AIM:

A MsSQL DB to hold Products / services that will vary significantly, although there won't be many; hence, the N:N attributes for ProductTypes.
Be able to keep a record of Product changes [Price, attribute additions, etc.]; hopefully facilitated via AttributeHistory Table, or a better proposed method.
Be able to record an Order of said items for purchase/delivery; OrderItem / ItemAttributes

So DB design certainly isn't my forte, so understanding some basics, this is what I came up with [which is almost a carbon copy of a blog post at: www.codingblocks.net/programming/database-schema-for-multiple-types-of-products/ If you're interested

CONCERNS:

Are with the following:

ItemAttributes and the ProductAttributeValues Tables are almost identical - so was wondering if I could/should combine them?
Will such an AttributeHistory table work, or will it be an eventual nightmare. Straightaway, I see that sometimes values will be INTs and other times CHAR's; so perhaps two columns to suit, or just a CHAR that I have to handle any transposing via code?


texts: What is MSrepl_tran_version? (MS SQL Server)

texts:
So, I'm an "enterprise software developer" (welcome to hell, here's your accordion), meaning I'm not a DBA and I'm inheriting something that's been around for years and very few people have a crisp explanation for much at all.

My question:  what, exactly, is msrepl_tran_version?  We have it in all our tables (a rather large number), but I don't believe we're using replication.  I guess we did at some point in the past, maybe.

However, we seem to be using this field for concurrent-update detection; at update time, we check this field to see if it's changed, and, if so, reject the user's change (error on the screen, force them to refresh, etc., etc.0

Does this field get updated magically, somehow?  Should I be looking for a trigger?  (I doubt it.)  If we're not using replication, what do changes in this field affect?  I can't seem to actually find a good overview explanation of what this column is.

TIA.

John.


texts: Regarding Always On Licensing (Secondary replica no license required)

texts:
I have few questions regarding the licensing of SQL Server when it comes to Always On:

Under which scenario / settings we can say that secondary replica is truly passive and will not be considered for licensing:

"Readable Secondary" AG setting for all the replicas associated to Always On(including current Primary replica) should have setting either as “NO” or “Read-Intent Only” or it should be "NO" only?
There should not be any Standalone Database? (not even any third party backup tool database such as "LiteSpeedLocal").
Also the backup should not be configured to run on Secondary replica. Otherwise, the Secondary replica will not be truly passive. Is it true?

Any other things which we need to consider / look for in order to be 100% sure that the secondary replica will not require license?


texts:
For reasons that are not mine to address, we are looking for alternatives to WSUS and SCCM to automate SQL CU patching (particularly on SQL 2017+)

There are some neat Powershell solutions like this one by Adam Bertram which I found via the related post Automate the patching of SQL Severs on Windows

I know there are a plethora of commercial solutions, that Google finds easily.  I am looking for solutions we can manage in house.

I am looking for other approaches to consider as well.  After extensive searching the only other approach I can even imagine is a SQL job, that runs on server restart (server patching occurs regularly generally with restart) that would go look in a shared folder for the most current CU and apply it if not installed. But I don't find any mention of solutions other the those above.

Are there any methods for automating SQL CU patching, that I have not found examples of?

If SQL job patching is viable, are there any examples?


texts: How do we reference to a collection in MongoDB?

texts:
This is my first my first post in this forum. I am from RDBMS background and wanted to learn MongoDB which will help me in migrating our applications to MongoDB. I am creating two collections (customer & address).

Customer collection

Has generic information like name,gender,city,country,phone, address (which should be reference to another collection).

Address collection

Has an array of information like current address, office address, mailing address. I wanted to know, how can I add a reference to another collection? Considering this scenario, Customer Collection will be having an Address document which will have to contain reference to ids in Address Collection.

If someone could provide me a sample example for this model. It would be great. Thank you in advance.


texts: Why can't outer join simplification happen for embedding outer join?

texts:
The MySQL documentation here suggests the following:

  Sometimes the optimizer succeeds in replacing an embedded outer join operation, but cannot convert the embedding outer join. The following query:

  SELECT * FROM T1 LEFT JOIN
              (T2 LEFT JOIN T3 ON T3.B=T2.B)
              ON T2.A=T1.A
  WHERE T3.C > 0

  Is converted to:

  SELECT * FROM T1 LEFT JOIN
              (T2 INNER JOIN T3 ON T3.B=T2.B)
              ON T2.A=T1.A
  WHERE T3.C > 0

  That can be rewritten only to the form still containing the embedding outer join operation:

  SELECT * FROM T1 LEFT JOIN
              (T2,T3)
              ON (T2.A=T1.A AND T3.B=T2.B)
  WHERE T3.C > 0

Why can not we convert the 'embedding' left outer join in this case into inner as well? Since T3.C > 0 is null-rejecting, T3.B=T2.B becomes null-rejecting. Consequently shouldn't T2.A=T1.A also be null-rejecting?

Overall, can't the embedded query can be treated like below?

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 LEFT JOIN T3 ON T3.B=T2.B
  WHERE T3.C > 0

Is there a reason behind this? It is just another form of writing the query right? Why is it being treated like subquery?


texts: For Postgres, efficiently get points within X distance of point Y?

texts:
I have this table as below in Postgres:

create table locations
(
    id serial not null
        constraint games_pkey
            primary key,
    name varchar,
    location point,
)
;

I want to query based on another location point( x, y ) as latitude and longitude; I only need to return records within, say, 1 kilometre distance of that location. How can I efficiently select records meeting that criteria?

I only need the distance to be accurate on a local scale and so curvature of the earth shouldn't be an issue for my application, but is there a performance benefit of either POINT or GIS in this context?

I found this question on stackoverflow which appears to do something similar, but the accepted answer has nothing but code and no indication of how it works:
https://stackoverflow.com/questions/37827468/find-the-nearest-location-by-latitude-and-longitude-in-postgresql


texts: Is there a way to force inheritance of a procedure's schema to all procedures called within it

texts:
If I have a stored procedure schema1.proc1 and it selects from table1, it correctly selects from schema1.table1. However, when schema1.proc1 executes proc2, it tries to find and execute dbo.proc2.

There is no dbo.proc2 so it returns an error that stored procedure is not found.  It never looks for schema1.proc2 even though sys.sysdepends links schema1.proc1 to schema1.proc2. However, there is also a schema2.proc2, but the error returned is just that proc2 is not found or does not exist.

Is there any way other than making that user's default schema = schema1 to have the stored procedure execute the procedure from its own schema (i.e. assume schema1.proc2 should be executed prior to searching for dbo.proc2)?

I know that we could simply preface proc2 with schema1 explicitly, but we are trying to avoid having to find all stored procedure calls within the stored procedures to append the schema prefix of the calling procedure to the procedure being called.

I can see from sys.sysdepends that it correctly links proc1 to the correct database object proc2, that is also associated with schema1, but for some reason it does not seem to look for that proc2, but instead for dbo.proc2.

Please let me know if there is some database level setting that I am just missing to tell the compiler which to check first.


texts: Annonymous user has no privileges, but still able to show databases?

texts:
Output of SHOW GRANTS FOR ''@'localhost';

GRANT USAGE ON *.* TO ''@'localhost'

However, when I log in using my default user account, which is later defaulted to an annonymous account by mySQL as shown below :

Output of SELECT USER(),CURRENT_USER();

+------------------+-------------------+
| USER()           | CURRENT_USER()    |
| dukn@localhost   | @localhost        |
+------------------+-------------------+

I can still do show databases; using @localhost. Why is this so ?

UPDATE:

Output of @@version :  5.5.34-0ubuntu0.13.10.1

Output of show grants while logged in as dukn@localhost : GRANT USAGE ON *.* TO ''@'localhost'


texts: Oracle Hierarchical query: with two node attributes NodeId and NodeType

texts:
I have the following use case where I want to make use of hierarchical queries to get the desired result.

In my use case I have two types of node say 'A' and 'B'. So the unique identifier of node is its ID and Type.

Now the problem is when two nodes with same Id and different type, when I call connect by clause only on id I get child for other types of nodes also (if id of that node is same).

create table TreeTest (
nodeid integer,
nodetype char(1),
parentid integer,
parenttype char(1)
);

data in table:

   1, 'A', NULL, null
   2, 'A', 1, 'A'
   3, 'A', 1, 'A'
   2, 'B', NULL, null
   3, 'B', 2, 'B'

Now I am firing the following query (which is not giving the correct result)

SELECT * FROM TREETEST
START WITH PARENTID = 1
CONNECT BY PRIOR nodeid = PARENTID;

I tried following query also, but again wrong result

SELECT * FROM TREETEST
START WITH PARENTID = 1 AND PARENTTYPE = 'A'
CONNECT BY PRIOR nodeid = PARENTID AND NODETYPE = PARENTTYPE;

Kindly provide the correct query and kindly also explain why second query is not working.

Expected output:

2   A   1   A
3   A   1   A


texts: Why is SQL running the same query longer from another connection?

texts:
Here is the issue overview: Why does my stored procedure run faster when executed localy vs remotely?

Dont jump to any conclusion just yet, let me explain what I mean...

Here is the setup:
A Windows 2008R2 (correction: 2003) application server executes a stored procedure that performs some action, (what its doing is really important at this point in time). This stored procedure is executed on the SQL server over a TCP/IP connection to the database server. The DB server is physicaly located right next to the application server, and they are connected to eachother via 1GB NICs to a 1GB Switch. The DB server is running SQL 2005 SP2 Enterprise Edition, and has 16GB of memory and several vLUNS striped across 48 15k drives in an HP-EVA FC connected SAN. From all indicators thus far, there are no I/O, Mem, or CPU constreints or limits being hit. Trace Falg 1118 is on and TempDB is split across 8 file on their own vLUN. Data, and TLogs also have their own vLUNS too.

So, here is what I am seeing:
Using SQLCMD on the database server, with SQLProfiler running from the same DB server, I can execute the stored procedure and I see that the execution starts immediatly, and compleats with a durration of about 2,100ms with an IO of about 1200.

Using SQLCMD on the application server, with SQLProfiler running from the DB server, I can execute the same exact stored procedure, with the SAME exact parameters, and I see that the execution starts immediatly, and compleats with a durration of about 110,000ms with an IO of about 1200.

The query results in 1 row, with 4 columns [INT, INT, VARCHAR(50), VARCHAR(100)]

ASIDE:(I know the query is a train wreck, this is a regulated system and I cannot change it on a live prodution server, so please dont make any sugestions about doing so. The next version has been rewritten to be better.)

From everything we can see, there is no reason that we should be seeing differances like this, but what is heppening is the .NET application that calls this query from the application server is timing out waiting for the responce.

We have checked locking and blocking, WAIT states, Query plans, IO contention, CPU contention, MEM contention, NETWORK saturation/utilization, performed indexes rebuilds on all indexes, updates all stats, and a hand full of other items, but haven't come up with anything that points to why this is happening.

Please ask more questions if you have any, make recomendations as you come up with them, and depending on the impact (remember this is a production environment) we will try them and respond back.

-Thanks!
-Chris


texts: How to adjust a hierarchical query in Oracle so then the parent and the id can come in sequence?

texts:
I have a view with a hierarchical query like the print screen below. I'm trying to find a way of getting the ids and parent ids (columns cfg_info_id and cfg_parent) as sequential numbers. Is there a way of doing this just adjusting my sql? Or do I need to create a pipelined function to manipulate these values?

Here is the sql (simplified):

select
       ci.CONFIG_ID, rownum, ci.CONFIG_INFO_ID as cfg_info_id,
       lpad(' ',(level-1)*3,' ')||ci.key as CFG_KEY,
       ci.value AS CFG_VALUE, ci.parent AS CFG_PARENT,
       level AS CFG_LEVEL
from CONFIGS_INFO ci
where config_id = 16
start with ci.parent is null
connect by prior ci.CONFIG_INFO_ID = ci.PARENT;

I can use rownum to get the cfg_info_id as a sequence. But do I have some way of changing the cfg_parent accordingly?


texts: How to duplicate the database in postgresql?

texts:
I need to duplicate the existing database including its schema and structure to another new database. I need this in shell command environment and not in pgadmin. Please kindly help me.

nohup pg_dump exampledb > example-01.sql
createdb -O postgres exampledbclone_01

my user is "postgres"

nohup psql exampledbclone_01 < example-01.sql

$ pg_dump mydb > db.sql
$ psql -d newdb -f db.sql


texts:
How to make short circuit in where clause, e.g:

select 1 where 1=2 AND 1/0=0

In the above example 1/0=0 condition should not be evaluate as the first condition is false.


texts: Is this procedure to Failover to Logshipped secondary and Fail back again procedure correct?

texts:
I have a primary database in Amsterdam (AMDB), and a secondary in London (LNDB). I want to fail over from AMDB, to LNDB and then back again, without having to make another full backup (because the database is too large).

According to Changing Roles Betweeen Primary and Secondary Servers, and Failing Over to a Log Shipping Secondary this all looks possible. I've never done it before and I'm going to be doing it in production this weekend. (I'll be doing some test runs on non prod databases before hand.)

My question: Should I be doing anything else over and above the Microsoft documentation to ensure success? (I'm wondering if anyone else has some gotya's or extra steps they take.) I'm hoping for a reassuring answer such as "Yes this works every time we do it in production and it's cool" but will be very happy also with "No don't do that, make sure you do x/y/and z instead."

Edit: Please don't recommend I use Mirroring as the answer to this question - yes that's a great idea but it doesn't work for us because of licensing costs. We use standard edition (and this only supports Mirroring in synchronous mode). I would like to know if anyone has followed the steps in the MS documentation in prod or if there's a better alternative which an "experienced" dba would use.


texts:
Is it possible to determine what user created another user's account in Oracle database?

I have two types of users in database:

Users having system and object privileges
Users having only system privileges

I can find the owner of all users having system and object privileges.

select distinct d.username,d.account_status,t.privilege,t.owner
from dba_users d,dba_tab_privs t where d.username=t.grantee
and d.account_status='OPEN';

But this query doesn't fetch the users having only system Privileges. Please help how to find the owner of all those users having only system privileges?


texts: Slow Query not using index in one of the tables


texts:
I have a simple SSIS package written in Visual Studio 2015 in a Project Deployment model solution. The package includes a Script Task with code written in C# and performs a very simple task. This script targets .NET Framework version 4.5.2.  I verified this version of .NET is installed on the SQL Server machine.

When I run the package in my local development environment it runs fine and does exactly what I need it to do.

I deployed it to the Integration Services Catalogs in the DEV SQL Instance and setup a SQL Server Agent Job to execute the package.  I executed the job but it failed in a step past the script task. The failure was due to the Script Task not doing it's job. A check of the SSIS Package execution messages showed the Script Task step completed with no errors.  I checked the event logs and found no errors there.  I double checked the C# code.  I modified the script task code to also generate an Information Message (Dts.Events.FireInformation) just to make sure the script task was actually firing.  I changed the SSIS logging in the job to Verbose as well.  I tried several other methods to validate the C# code was being called and executed. I turned antivirus off and re-ran with no change.

I’m convinced that the Script Task is just not executing the C# code in the Script Task.  I took the same basic c# script code and created a console app and successfully ran it on the server so I know the .NET code works on the server. I looked at the access permissions in Component Services and the permissions seem fine.  No errors are being thrown in SSIS or the event logs so it's been difficult to figure out what the problem is.

I’m not sure what else to check.  Does anyone know if what else I can check that might be preventing Script Tasks from being executed by SSIS or the SQL Agent?

--UPDATE--

So after spending over 3 days trying to figure this out I decided to approach this differently.

On a hunch I decided to install VS 2017 and SSDT 2017 to see if the upgraded development environment would make any difference. It works perfectly on the server after upgrading the project to 2017 and rebuilding the script task.

So apparently there was some sort of issue with my development environment or the versioning of the compiled code.

Live and learn I guess!


texts: sqlite 3.4 updating table with data from different table - changed with 3.3 - newbie question

texts:
All of the other questions on this seem to be before 3.3 when sqlite added an update join feature? Newbie here, so I may even be getting the terminology wrong. I am trying to update the issbn field in table GULLfromLG (currently empty), with data from the issbn field in BIBinfo (field can be empty in a given record).
I am using SQLiteStudio. From looking through the documentation, sqlitetutorial, w3resource, and general web searches I believe the following should work. But it doesn't. The processing happens, is declared successful, but all the fields in GULLfromLG are empty. What am I missing?
    UPDATE GULLfromLG
   SET issbn = (
   select issbn
  FROM BIBinfo
 WHERE bibno = bibno)

I have also tried naming the fields with variations of
GULLfromLG.issbn GULLfromLG.bibno BIBinfo.issbn BIBinfo.bibno
Any help would be greatly appreciated!


texts: SQL query with GROUPING SETS to get subtotals with a HAVING clause

texts:
Let's say I have a (fictional) table City with the following columns:

Country: string
State: string
City: string
Population: integer

I want a query that shows the total population by State, by Country and overall, excluding states below 1,000,000 inhabitants.

SELECT Country, State, SUM(Population) AS [Total]
FROM   City
GROUP  BY GROUPING SETS ( ( Country, State), ( Country), () )
HAVING SUM(Population) < 1000000
ORDER  BY Country, State

The problem with this query is that the Country and Overall totals will include the population of states below 1,000,000 inhabitants (meaning the total of states doesn't match the total of its parent country).
(This is assuming that the countries have over 1,000,000 inhabitants)

How can we amend the query above to fulfill the requirements?

I'm using sql azure...


texts: Composite Primary Key efficiency as a Foreign Key

texts:
I have a table with a Composite Primary key (consisting of 4 columns) which is used to ensure no duplicates are entered into the table. I am now in need of a new table which will need to reference the keys in this table as foreign keys.

My question is which approach is more efficient for lookup speeds:

1) Do I create the new table including all 4 columns and reference them all in a foreign key.

or

2) Do I create a new identity column in the Primary Key table and use this as a foreign key in the new table.

This database is expected to hold a very large amount of data, so I have built it up until now with a view to minimising the amount of data held in each table. With this in mind, option 2 would be the best approach since I will save 2 int columns and a datetime column for every row, but I want to avoid increasing the lookup time if unnecessary.


texts: Why is a secondary index chosen over a clustered index for SELECT COUNT(*) ...?


texts:
Example query

SELECT * FROM Table WHERE ID in (5,3,4)

How do I adjust this query so that the order of the returned entries follows the input of the ID's (IE, 5 first, 3 second, 4 third).

Edit - To be clear the ID's a dynamically generated list that is in the right order.


texts: Azure SQL: Should Manual Indexes be Applied with automatic tuning?

texts: Should Manual Indexes on tables be applied in AzureSQL Cloud Database? We read that AzureSQL does automatic tuning, so are manual indexes even required? We read, there can be cases where AzureSQL drops our manual indexes, if its not needed. Is there anyway to prevent this ?


texts: Concatenation Physical Operation: Does it guarantee order of execution?

texts:
In standard SQL, the result of a union all is not guaranteed to be in any order.  So, something like:

select 'A' as c union all select 'B'

Could return two rows in any order (although, in practice on any database I know of, 'A' will come before 'B').

In SQL Server, this turns into an execution plan using a "concatenation" physical operation.

I could easily imagine that the concatenation operation would scan its inputs, returning whatever input has records available.  However, I found the following statement on the web (here):

  The Query Processor will execute this plan in the order that the
  operators appear in the plan, the first is the top one and the last is
  the end one.

Question:  Is this true in practice?  Is this guaranteed to be true?

I haven't found any reference in Microsoft documentation that the inputs are scanned in order, from the first to the last.  On the other hand, whenever I try running it, the results suggest that the inputs are, indeed, processed in order.

Is there a way to have the engine process more than one input at a time?  My tests (using much more complicated expressions than constants) are on a parallel-enabled 8-core machine, and most queries do take advantage of the parallelism.


texts: Oracle 12c database cannot log in as a local user

texts:
I met a problem from Oracle 12C Database, when I try to login to the user account with commandconn test/test, I got prompt of "invalid username/password, logon denied".
when I use conn test/test@PDBORCL, I got "Could not resolve the connect identifier specified"
Can anyone please help me out? I really appreciate!!


texts:
I was log shipping. I disabled the jobs, and restored the database on the secondary machine and used them for a bit. Now I want to undo this. I don't care about history or syncing, I simply want to turn log shipping back on and carry on my merry way.

I enabled the jobs, but 'running' them (right click > start job at step, if that's how you run them) just pops up and says success but nothing happens.

I am unsure of how I can start the log shipping going again. I tried deleting the databases from the secondary machine but that didn't help either.

Any ideas? Surely it's a simple thing?

Edit: Still at a loss. Says the job was successful but nothing happens, no database is created. It was working fine before I took the databases out of recovery on the secondary machine.


texts: How to get the Slave commit timestamp using MySql replication?

texts:
I've been looking for ways to collect information about the replication delays on MySql 8. I found an alternative solution, but I imagine there's a more straightforward way to do that.

By reading its reference manual, I found out that I could get useful information in the performance_schema database.
When I look into the table replication_connection_status on the Slave server I can find the following information, among others:

When the original Master commited the transaction (LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)
When the immediate Master commited the transaction (LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP).
When the Slave started and finished to write the updates in its relay log (LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP, LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP).

But it lacks the information about when the transaction was commited on this Slave server.

The alternative solution:
Perform replication of these updates, from the Slave server to a third server (its master would be the Slave we're trying to get this info from), and then looking the column LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP on the third server, which would be when the main Slave server has commited the transaction.

A possible solution:
The table events_transactions_history may contain the time where the transaction ended, but the time format is confusing.
According to 2:

  The timer baseline (“time zero”) occurs at Performance Schema
  initialization during server startup. TIMER_START and TIMER_END values
  in events represent picoseconds since the baseline. TIMER_WAIT values
  are durations in picoseconds.

I have no idea of how to know what this "time zero" would be exactly, all we're given is that it occurs during server startup, but how could I get this exact time?

And of course, if there's an easier solution that I've missed, let me know.

Thanks.


texts:
I am building an application for comparing two or more songs. We have millions of songs. Currently we have access to raw data files (form a company like soundcloud) consisting of

1) Daily Users.csv  with new signups

2) Daily Tracks.csv with new songs released

3) Daily streams.csv with today's streams.

For my application, the input will be two or more songs which has to be compared. The songs will be compared based on the data of users who listen to the songs.
For the database design, I am thinking of relational database with

1) One Table for for users data

2) One table for tracks data.

3) Millions of tables; one for each song. The reason for this is that on query, it will be very easy to pick up relevant tables and compute statistics for each table.

Moreover, for a particular song, for a particular user, we want to store ordered time series data of the source of stream ( collection, album page, search). I am thinking of storing it as a json in Stream Source field in Song_ID tables.

Is it a good relational database design? Or should I switch to non relational databases? In that case which database would you suggest?


texts: Query to count the number of different departments that a student is studying a subject from

texts:
The database schema are as follows:

  depts(deptcode(primary key), deptname)

  students(rollno (primary key), name , deptcode (references depts(deptcode)))

  faculty(
  fac_code(primary key),
  fac_name,
  fac_dept (references depts(deptcode)))

  crs_offrd(
  crs_code (primary key),
  crs_name,
  crs_fac_cd (references faculty(fac_code)))

  crs_regd(
  crs_rollno (references students(rollno)),
  crs_cd (references crs_offrd(crs_code)),
  primary key(crs_rollno,crs_cd))

The query is Count the number of different departments that a student is studying a subject from.

I wrote a query like this:

 SELECT crs_rollno,
       Count(DISTINCT students.deptcode, fac_dept)
FROM   crs_regd,
       students,
       crs_offrd,
       faculty,
       depts
WHERE  students.rollno = crs_regd.crs_rollno
       AND faculty.fac_dept = depts.deptcode
       AND faculty.fac_code = crs_offrd.crs_fac_cd
       AND crs_offrd.crs_code = crs_regd.crs_cd
GROUP  BY crs_rollno;

My idea is to count distinct pair of (students parent dept, course parent dept(can be traceable from dept that faculty of that course is associated with))  grouped by rollno gives the details of each student. Hence it should work.

But I am getting the following error:

  select crs_rollno, count(distinct students.deptcode,fac_dept) from
  crs_regd, students, crs_offrd, faculty, depts

  ERROR at line 1: ORA-00909: invalid number of arguments

What is the problem?

If I am right, as the error suggests count can't take more than one argument, then what is the round about way for this query?


texts:
I want to enable trace flags 4199, 8024, 9024, 1236 & 1117 on SQL SERVER (Startup Parameters).

I went to SQL Server Configuration Manager -> SQL Server ("instance_name"), and then clicked Properties -> Startup Parameters tab -> and I  added -T4199 -T8024 -T9024 -T1236 -T1117 separately.

This is a SQL Server 2016 instance.


texts: Intermittent EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb'

texts:
I have a job that runs every 2 minutes and sends an email to users after it has gathered job information. Most of the time it is successful but every couple of days it fails with the Execute permission was denied. Right after the failure it runs again just fine.

It is a job that goes through tables and gets information and status of certain jobs. Not SQL Server jobs but proprietary application jobs that has its information stored in tables in the databases. It fashions an email and sends it in HTML format from msdb.dbo.sp_send_dbmail.

It is owned by sa and is not run manually. I'm thinking that since it only happens once every couple of days (sometimes 3 or 4), it could be the content of the message is too big or has some garbage in it that prevents the msdb.dbo.sp_send_dbmail proc from sending.

There is no error handling on the job or step. There is only one step: It executes the proc that aggregates the information and sends the email. The agent log says the same thing that's in the title of the question about permissions.

SQL Server is setup to log failed logins. There have been no failed logins as of a week ago.


texts: Linked Servers pointing to same server (localhost) causing "Transaction context in use by another session" error

texts:
We are using SQL Server 2008. Normally our app that is using different databases distributed over the network and some of the stored procedures we call therefore make use of linked servers.

Now I wanted to test our app locally and thus created all databases (that are normally distributed over the network) on my local machine. So all the databases are now living on "localhost". I changed the linked servers so that they are all pointing to localhost (and a quick select-test shows they are working).

However, when I am calling stored procedures that make use of the linked servers (that now point to localhost) and these procedures are using transactions, then I am getting the following error: "Transaction context in use by another session".

I am guessing this is because SQL Server thinks it's a distributed transaction, but it's really a local transaction?!

What are my chances of resolving this other than eliminating the connection servers on my local install and directly address the different database as they are now living on the same server?

If it's important: I didn't configure MS DTC, I just started the corresponding windows service on my machine. (Mostly because I wasn't sure where to solve this problem.)


texts: Postgres PgAdmin III Query Builder crash

texts:
I've been using Postgres and pgAdmin III for some years, but this morning I had a very BIG BIG problem.
I'm using pgAdmin III 9.0 on Windows Xp operating system, on my laptop Dell Latitude E4300.

From this morning, Query Builder ("SQL Editor") option simply stopped working. When I open pgAdmin, everything is fine and workin', until I click on the Sql Query Builder button. When I do that, nothing appears and pgAdmin crashes without giving any information.

I tried to uninstall and install PgAdmin III 9.1, again, but Sql Query Builder kept not workin'.

What could I do?


texts: Enabling Edition Based Redefinition failed with ora-33819. Could I query why?

texts:
I want to enable Edition Based Redefinition for a user. Executing this command gives an ORA-33819 exception:

ALTER USER user ENABLE EDITIONS;

I know there's an option to enable it with the parameter Force

ALTER USER user ENABLE EDITIONS FORCE;

but as this operation is retroactive and irreversible, I would rather see beforehand why it failed. Does anyone know how to get a list of problems before altering the user?

Working on an Oracle 12.1.0.1.0


texts: Update a column using LOAD_FILE function

texts:
I have created a table lfile. The structure of table is:

CREATE TABLE `lfile` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |

In the table I have 3 records:

select * from lfile;
+----+-------+
| id | name  |
+----+-------+
|  1 | ravi  |
|  2 | kumar |
|  3 | ark   |
+----+-------+

Now I want to update 'ravi' with 'ravikumar' using LOAD_FILE.

  UPDATE lfile SET name=LOAD_FILE('/home/ravi/Desktop/t') WHERE id=1;

But it updates the name column with a NULL value.

mysql> select * from lfile;
+----+-------+
| id | name  |
+----+-------+
|  1 | NULL  |
|  2 | kumar |
|  3 | ark   |
+----+-------+

Settings:

show variables like 'character_set_filesystem';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| character_set_filesystem | utf8  |
+--------------------------+-------+


texts: SQL Profiler Audit Logout duration 18446744073709548

texts:
I'm running traces on our SQL Server 2014 box and see something disturbing. The duration column of the Audit Logout event is normally the time in microseconds a connection has been alive to the server and can get quite large due to connection pooling, but what does it mean when it's 18446744073709548?

I cant help to think this number is special. A bit like the value of maxint in .NET etc. I've googled this number and it returns various stuff. A few of them are about SQL server and the audit logout event, so I'm not the only one seeing this, but I cant find an explanation about what it means.

Anyone? (preferably someone who has participated in the development of SQL server ;))

br
Frederic


texts:
I am bit confused with terminology as official Microsoft SQL Server documentation including BOL refers to plan guide the one you create using sp_create_plan_guide proc. Does plan freezing is same thing or slightly different?. Reading Erland's great article here

  There are two ways to set up plan guides, a general way, and a
  shortcut which is also known as plan freezing. General plan guides
  were introduced in SQL 2005, whereas plan freezing was added in SQL
  2008.

I am reading plan freezing is when you create guide using sp_create_plan_guide_from_handle

Seeking a clarification on terminology being used, does plan guide and plan freezing is the same thing or they refer to slightly different way to stick the execution plan in the cache?


texts: Why can't I update or delete certain records in SQL Server?

texts:
I am having a weird issue in SQL Server 2014. I cannot update or delete certain rows from a table.

I can insert rows fine, and select them, but when I try to update or delete a row, I get a timeout error. It is also strange, that some of the rows can be updated, while others cannot. The table is small, only a few thousand rows, and it's hard to see a pattern with which rows can and cannot be updated, but it seems the more recently inserted rows cannot be deleted/updated.

There are no triggers on the table, there are no locks on the database, and the table has a primary key.

What could be causing this?


texts:
I have 2 dbs, let's call them writedb and readdb.  Readdb has a dblink to writedb and a view to a table over that dblink.

I do an update on writedb to that table which view points to and then read from that table over the dblink.  I appear to be seeing old values for seconds after the transaction commits even if I set isolation level to SERIALIZABLE.
Does this make any sense?

On writedb:

create table mk.dblink_test (
id varchar2(16),
status varchar2(16));

insert into mk.dblink_test
SELECT rownum, 'ACTIVE'
FROM   dual
CONNECT BY LEVEL <= 1000000;

On readdb:

CREATE DATABASE LINK DBL_TEST CONNECT TO mk IDENTIFIED BY password USING 'writedb:1521/qa'

The SQL that updates writedb directly:

update mk.dblink_test
set status = 'DONE'
where id = '1';
commit;

Now the situation with SQL which get dirty reads is weird.  The following simpler query

SELECT r.status
FROM mk.dblink_test@DBL_TEST r
WHERE r.id = '1';
commit;

does not appear to exhibit the problem.
But a more complex version:

SELECT r.status
FROM mk.dblink_test@DBL_TEST r
LEFT OUTER JOIN (SELECT '3' status from dual) optout ON optout.status = r.status
WHERE r.id = '1';
commit;

reproduces it 90% reliably.
The steps to reproduce:

launch sqlplus and connect to the readdb
run the read query, get status 'ACTIVE'
run the update query against the writedb in a different
sqlplus
go back to the sqlplus from step (1), launch the read
query from step (2) and get 'ACTIVE' again
repeat read query same
as in step 4 -- get the correct 'DONE' response.


texts: Why is my SQL Server database file so large?

texts:
I have a relatively simple database that is over 300MB on disk.  When I look at the table data it adds up to just over 5MB.  Please see the screenshot below:

This screenshot can also be seen here.
If I add several more devices, I will run out of disk space at this rate! Why is this so large and how do I shrink it and keep it under control automatically?
Query results from the "answer" in this related thread say that reserved MB should be 7.66, which is still far more reasonable than 300+.

It is the log file.  I've backed it up, told it to truncate, shrunk the log file and still no luck. I've tried shrinking both database and the log file directly.  I should probably give up and move to simple mode, but I would prefer full recovery if I could control the log size.


texts: Converting from domain calculus to relational algebra

texts:
I have an assigned task at the uni and I have the three relations that follow:

Customer (Name, City, Account)

Offer (Product_Description, Supplier, Price)

Order (Name, Product_Description, Quantity, Supplier)

Domain calculus is already given:

F = {Name, City| ∃ Account, Product_Description, Supplier, Price, Quantity
(customer(Name, City, Account)

∧ offer(Product_Description, Supplier, Price)

∧ order(Name, Product_Description, Quantity, Supplier)

∧ Price≥ 100 ∧ Supplier= ’Meier’)}

I have been asked to express this domain calculus as a sentence and convert it to relational algebra.
The expression is:

List the name and cities of the customers who ordered a product that has a value greater than or equal to 100 and product supplier is Meier.
(I didn’t use “customers who ordered greater than or equal to 100” because it’s possible that the customer ordered, for example, 2 products (quantity is 2) which has a value of 50 euros.)

Relational Algebra:

π name,city (Customer) ⋈π name (Order ⋈ Price ≥ 100 ∧ Supplier = 'Meier' (Offer))

I am not sure if my relational expression is accurate. There are many ways to express it but I tried to find the best efficient way for execution plan. (Equivalence of expressions)


texts:
Is there any check list for how to do migration from SQL Server 2008 to SQL server 2016 ?

I am building a new server for the 2016 want to move all the DBs from the old server 2008 to the 2016

any suggestions ?


texts: Platform independent select ordered by primary key

texts:
Is there a reasonably standard way to perform a select on a single table and have the result set ordered by primary key without having to specify the primary key condition explicitly?

Ideally I wish I could perform a query such as the following, invalid one:

select * from my_table order as primary key;


texts:
Is it possible to restore a differential backup separately?
The production server runs the following every sunday

DECLARE @Pathname VARCHAR(512) = 'N:\Backup\Dir\DBName_' + (select datename(dw,getdate())) + '.bak'
BACKUP DATABASE [DBName] TO DISK = @Pathname
WITH NOFORMAT, NOINIT,  NAME = 'Full Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

every other day I have the following as a differential backup

DECLARE @Pathname NVARCHAR(512)
SET @Pathname = 'N:\Backup\Dir\DBName_' + (select datename(dw,getdate())) + '.bak'
BACKUP DATABASE [DBName] TO  DISK = @Pathname
WITH  DIFFERENTIAL , NOFORMAT, INIT,  NAME = 'Diff Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Here is the report server restore job

DECLARE @file VARCHAR(255) = '\\RemoteServer\Dir\\DBName_' + (select DATENAME(dw, DATEADD(day, DATEDIFF(day, 0, getDate()) - 1, 0))) + '.bak'
RESTORE DATABASE [DBName]
FROM DISK = @file
WITH REPLACE

I keep getting the following error

The log or differential backup cannot be restored because no files are ready to rollforward.

Anyone help explain what i'm doing wrong?

UPDATE

I had to use WITH MOVE because the paths are different


texts: MySQL Multiple threads inserting *SAME* record a Bad Thing?

texts:
Okay, as I've noted in other questions, I'm not a sophisticated database user even though I took my first course in relational algebra from ted Codd. I've now inherited an application that occasionally gets into what, to my naive mind, strikes me as a bad place.

SHOW PROCESSLIST; shows multiple locked thrads, each of them with exactly identical INSERT statements, viz:

INSERT LOW_PRIORITY IGNORE INTO node (f0, f1, f2, f3) VALUES ('group', 'larry', 'moe', 'curly')

Am I right that this is a Bad Thing? What would the expected consequences be?

Update

As requested, here's the table:

CREATE TABLE `node` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `graph` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  `f1` varchar(200) NOT NULL,
  `f2` varchar(200) NOT NULL,
  `f3` mediumtext NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `nodeindex` (`graph`(20),`f1`(100),`f2`(100),`f3`(100)),
  KEY `ix_node_f1` (`f1`),
  KEY `ix_node_graph` (`graph`),
  KEY `ix_node_f3` (`f3`(255)),
  KEY `ix_node_f2` (`f2`),
  KEY `node_po` (`f2`,`f3`(130)),
  KEY `node_so` (`f1`,`f3`(130)),
  KEY `node_sp` (`f1`,`f2`(130)),
  FULLTEXT KEY `node_search` (`f3`)
) ENGINE=MyISAM AUTO_INCREMENT=455854703 DEFAULT CHARSET=utf8


texts:
I am getting

  SSL: error:14094412:SSL routines:SSL3_READ_BYTES:sslv3 alert bad certificate" error at mongod console while connecting using ssl. and " exception={com.mongodb.MongoSocketOpenException: Exception opening socket}, caused by {iaik.security.ssl.SSLException: Server certificate rejected by ChainVerifier}}

I am using Entrust security vendor and mongoDB 3.4 version. When I change the vendor to SUN_JSSE then its working fine and connection is established using same set of certificates which are self signed. I want to know whether mongodb uses support Entrust based ssl?


texts: ORA-12899 when creating varchar virtual column based on numeric column

texts:
I have a table with numeric column:

create table test (a number(2,0));

Next I want to create a virtual column based on column a:

alter table test add (b varchar2(3) as ('A:'||to_char(a)));

This results in error:

ORA-12899: value too large for column "B" (actual: 12, maximum: 42)

Why do I get this error? This works if I replace to_char with substr, but I'm curious how does Oracle determine the "actual" and "maximum" values?

I suspect the maximum value is related to maximum precision of numeric types (Oracle ignoring the data type precision), but I still don't quite understand this error. Character set is UTF-8.


texts: When inserting the varchar value into the datetime field, it throws conversion error

texts:
FROM_DATE column is DATETIME format in the temptable1stockdetails table.

Gradd19 column is VARCHAR format in the GradedDETAILS table.

I want to insert the Gradd19 value to FROM_DATE but it throws the following error:

  The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
  The statement has been terminated

How can I solve this issue.

Sample data of the Gradd19 in the GradedDETAILS table is:

Sep 16 2016 12:00AM
Sep 16 2016 12:00AM
Sep 16 2016 12:00AM
Sep 19 2016 12:00AM
Sep 19 2016 12:00AM
Sep 16 2016 12:00AM
20
60
20-09-2016
20-09-2016


texts: Postgres: How can I force index usage for view with latest rates?

texts:
We import currency rates into DB:

CREATE TABLE currency_rate (
    id int8 NOT NULL,
    date date NOT NULL,
    currency varchar(3) NOT NULL,
    rate numeric(12,6) NOT NULL,
    CONSTRAINT currency_rate_pk PRIMARY KEY (id)
);

ALTER TABLE currency_rate add constraint currency_rate_un UNIQUE (currency, date);

but actually we need only the latest available rate to work with.

It is cumbersome to write CTE with sort and distinct on (currency):

with cr as (
  select distinct on (currency) currency, rate from currency_rate
  order by currency, date)
select
  ...,
  sum((nd.original_amount - nd.new_amount)*cr.rate) as amount
from notification_data nd
join cr on cr.currency = nd.currency
...

Query has following execution plan that is nice:

  CTE cr
    ->  Result  (cost=0.28..69.66 rows=13 width=16)
          ->  Unique  (cost=0.28..69.66 rows=13 width=16)
                ->  Index Scan using currency_rate_un on currency_rate  (cost=0.28..67.17 rows=995 width=16)
  ...
              ->  Hash Join  (cost=1029.26..57129.68 rows=18 width=60)
                    Hash Cond: ((nd.currency)::text = (cr.currency)::text)

I created view:

CREATE OR REPLACE VIEW latest_currency_rate AS
SELECT
  DISTINCT ON (currency) currency, rate, date
FROM currency_rate
ORDER BY currency, date DESC;

but DB optimizer doesn't use index from currency_rate_un:

explain select * from latest_currency_rate;

Unique  (cost=60.83..65.38 rows=12 width=16)
  ->  Sort  (cost=60.83..63.10 rows=910 width=16)
        Sort Key: currency_rate.currency, currency_rate.date DESC
        ->  Seq Scan on currency_rate  (cost=0.00..16.10 rows=910 width=16)

and even for:

explain select * from latest_currency_rate where currency = 'USD';

Unique  (cost=16.87..17.13 rows=12 width=16)
  ->  Sort  (cost=16.87..17.13 rows=104 width=16)
        Sort Key: currency_rate.date DESC
        ->  Bitmap Heap Scan on currency_rate  (cost=5.08..13.38 rows=104 width=16)
              Recheck Cond: ((currency)::text = 'USD'::text)
              ->  Bitmap Index Scan on currency_rate_un  (cost=0.00..5.06 rows=104 width=0)
                    Index Cond: ((currency)::text = 'USD'::text)

Integration of new view to original query gives:

explain select
  sum((nd.original_amount - nd.new_amount)*cr.rate) as amount
from notification_data nd
join latest_currency_rate cr on cr.currency = nd.currency
...

...
 ->  Hash  (cost=73.54..73.54 rows=13 width=12)
       ->  Subquery Scan on cr  (cost=68.37..73.54 rows=13 width=12)
             ->  Unique  (cost=68.37..73.41 rows=13 width=16)
                   ->  Sort  (cost=68.37..70.89 rows=1008 width=16)
                         Sort Key: currency_rate.currency, currency_rate.date DESC
                         ->  Seq Scan on currency_rate  (cost=0.00..18.08 rows=1008 width=16)
...

Now I am puzzled. Why original CTE query uses Index Scan and view doesn't use the same index?

Should I rewrite view with some alternative trick (instead of distinct on)?

I am thinking about going with materialized view to avoid sequential scans...


texts: problems when importing csv files into SQL Server using SSIS when the columns are not consistent from quarter to quarter

texts:
I am using SSIS to upload a bunch of csv tables into SQL Server Express.  The csv files are quarterly data files.  They are largely consistent but every couple of years a column may be added or an existing column deleted.
Every time SSIS encounters a change in the columns present it throws one or both of these errors:

Error: 0xC0202055 at Data Flow Task, Flat File Source [2]: The column delimiter for column "XYZ" was not found.
Error: 0xC0202091 at Data Flow Task, Flat File Source [2]: An error occurred while skipping data rows.

When a new column is added, both errors appear.  When an existing column is excluded, only the 2091 error appears.  Note that the columns are not just null when they are deleted, they no longer in the data.
Is there setting or something I can use so that SSIS accommodates these changes in columns?  I've tried setting up the target table with the complete set of columns but that hasn't helped.
Thanks,


texts: H2 SELECT NULLABLE or other equivalent to check a column has NOT NULL constraint or not

texts:
I would like to check if a column has a NOT NULL constraint in order to start an automated upgrade of the table definition.
However in H2 the SQL SELECT NULLABLE FROM TABLE_NAME doesn't return me the NOT NULL columns and I don't seem to find anything similar to check the constraint on the table columns.

Notice I don't want to check the NOT NULL column values with something like SELECT COLUMN FROM TABLE WHERE COLUMN IS NOT NULL!  I want to check the table definition for that specific column.

thank you


texts: Recommendation for RDBMS for large table

texts:
I have been asked for recommendation on the optimal RDBMS for a large amount of data. The largest table would contain 2TB of data. My own largest table is only 400GB (mysql, performs very well).

The individual rows are going to be short, no blobs/etc, just a lookup table.

Is PostgreSQL nowadays performing better than MySQL? Can I ask DBAs with tables in this order of magnitude for their experience? Oracle might be considered as well. The available hardware is probably a standard linux box with about 64G ram.


texts:
I have two tables that temporarily hold uploaded data from a web application before it gets processed. This is running on an Azure SQL Database.

UploadFile

Id uniqueidentifier
CustomerId uniqueidentifier
FileName nvarchar(MAX) NULL
UploadDate datetime
UploadedBy nvarchar(MAX) NULL

UploadFileChunk

Id uniqueidentifier
Data varbinary(MAX) NULL
UploadFileId uniqueidentifier
[Index] int

UploadFile has a foreign key with cascade delete to UploadFileChunk. UploadFileChunk has a non-clustered index on UploadFileId. I have been uploading chunks of 1MB each.

Inserting and reading this data is working really well, but deleting a record from UploadFile after its data has been processed is really slow. On an S0 10 DTU testing Azure environment, it took 38 minutes to delete 12 records with ~500 child chunks collectively (just an example - it also performs poorly on our higher powered production environment and a fast local machine.)

How do I make it fast?

There is an index on UploadFileChunk.FileUploadId. Here's the execution plan.

I ran the following query (from Finding Blocking Queries in SQL Azure) several times while the slow delete was mid-execution and it didn't return any results:

SELECT TOP 10
    r.session_id,
    r.plan_handle,
    r.sql_handle,
    r.request_id,
    r.start_time,
    r.status,
    r.command,
    r.database_id,
    r.user_id,
    r.wait_type,
    r.wait_time,
    r.last_wait_type,
    r.wait_resource,
    r.total_elapsed_time,
    r.cpu_time,
    r.transaction_isolation_level,
    r.row_count,
    st.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE
    r.blocking_session_id = 0
    AND r.session_id IN
    (
        SELECT DISTINCT (blocking_session_id) FROM sys.dm_exec_requests)
GROUP BY
    r.session_id,
    r.plan_handle,
    r.sql_handle,
    r.request_id,
    r.start_time,
    r.status,
    r.command,
    r.database_id,
    r.user_id,
    r.wait_type,
    r.wait_time,
    r.last_wait_type,
    r.wait_resource,
    r.total_elapsed_time,
    r.cpu_time,
    r.transaction_isolation_level,
    r.row_count,
    st.text
ORDER BY
    r.total_elapsed_time DESC;

Truncating might be faster [than deleting] but these two tables contain data for multiple files and I must be able to delete one at a time. The nvarchar(max) columns could probably be something else, this is just what Entity Framework generated based on our domain classes. Do you think that would help with this problem?

I confirmed that deleting records directly from FileUploadChunks is just as slow. On my extra slow example (S0 10 DTU) it's around 1 second per row deleted.


texts: Is it possible to track the progress of when I drop a the primary key clustered index on a table?

texts: I have a large primary key clustered index I need to drop. I believe dropping it will also trigger index rebuilds on the nonclustered indexes (which are also pretty big). Is there a way to determine the progress of these processes?


texts: Only one non-nullable value across 3 columns

texts:
I have 3 "Nullable" columns.

I am looking for a way to restrain it so that:

One of the three has value, the other two columns must be null
All three cannot be null

Is it possible to do this?

I tried this:

CREATE UNIQUE INDEX alpha ON contacts ("a")
WHERE "b" IS NULL AND "c" IS NULL;

CREATE UNIQUE INDEX beta ON contacts ("b")
WHERE "a" IS NULL AND "c" IS NULL;

CREATE UNIQUE INDEX gamma ON contacts ("c")
WHERE "a" IS NULL AND "b" IS NULL;


texts: Grant read/read-write access to SSAS cubes

texts:
I have a requirement to grant read access on SSAS cubes to one windows group and read/write access to another group. I have multiple SSAS cubes on my server.

The question is:

What minimum rights should I grant to the user?
Is there a way to apply this rights on multiple cubes. As manually creating roles on each SSAS cubes is painful. I have lot of SSAS cubes. So looking for an easy way to do this.


texts: Finding "words" separated by commas in a single column with the CONCAT_WS() function

texts:
I have a problem when using commas separating distinct values contained in a column. I call a query as follows:

$findd = KL123,OTHER123

$query = "SELECT *
            FROM test1
           WHERE concat_ws(' ', column1, column2) LIKE '%".$findd."%'
              OR concat_ws(' ', column2, column1) LIKE '%".$findd."%'";

So, I would like to search for words separated by commas with the following value $findd= KL123,OTHER123 in a table that contains the following data:

ID  name  column1    column2
--  ----  ---------  -------
 1  dan   KL123
 2  lolo  ASI22      OTHER123,KL145
 3  lala  TA123
 4  ttoo  OTHER123

But no results "found". I would like to "find" the IDs 1,2,4.

But if I search without commas the value $findd= OTHER123 works with the same data shown above, because I "get" the IDs 2,4

The question

How can I solve this task with commas?

My level in MySQL is very basic and I find it difficult to solve this.


texts: SQL Server - Linked Server - Using OPENROWSET with windows integrated security

texts:
I have seen many articles regarding OPENROWSET using integrated security (Windows Authentication), but I could not make it work for me.

It is working fine using SQL Server authentication:

 select *
 FROM
 OPENROWSET('SQLOLEDB',
 'myserver';'monitor';'#J4g4nn4th4#',
 'SELECT GETDATE() AS [RADHE]')

But the same query I am trying to run using windows authentication, and it is not working:

select *
 FROM
 OPENROWSET('SQLOLEDB',
'myserver';'Integrated Security=SSPI',
 'SELECT GETDATE() AS [RADHE]')

Can someone please post an example that works?

Here is a good article that describes OPENROWSET usage.

Examples of working scripts using OPENROWSET - please read comments

 ----------------------------------------------------------------
 -- this works - linked server REPLON1
 ----------------------------------------------------------------

select *
 FROM
 OPENROWSET('SQLOLEDB',
'Server=REPLON1;Trusted_Connection=yes;',
 'SELECT GETDATE() AS [RADHE]')

 select *
 FROM
 OPENROWSET('SQLOLEDB',
'Server=REPLON1;Trusted_Connection=yes;',
 'SET FMTONLY OFF select * from sys.dm_exec_requests')

 SELECT a.*
FROM OPENROWSET('SQLOLEDB', 'server=replon1;Trusted_Connection=yes;', 'SET
FMTONLY OFF select * from sys.dm_exec_requests') AS a
WHERE a.session_id > 50
ORDER BY a.start_time desc

  ----------------------------------------------------------------
 -- this does not work - when using windows authentication
 -- apparently because windows server 2003 and windows server 2012 have problems connecting - related to SID
 -- it works fine using SQL Server Authentication
 ----------------------------------------------------------------

 select *
 FROM
 OPENROWSET('SQLOLEDB',
'Server=SQLREPLON1\REP;Trusted_Connection=yes;',
 'SELECT GETDATE() AS [RADHE]')

-- Msg 18456, Level 14, State 1, Line 1
--Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

 ----------------------------------------------------------------
 -- this works - linked server SQLREPLON1\REP
 ----------------------------------------------------------------

 select *
 FROM
 OPENROWSET('SQLOLEDB',
 'SQLREPLON1\REP';'monitor';'#J4g4nn4th4#',
 'SELECT * from SAReporting.DBO.tblStockLedgerMovement')


texts: Why shouldn't INFORMATION_SCHEMA views be used to determine the schema of an object?

texts:
According to MS-DOCS about System information schema views, the schema columns definition have a warning note that says:

  ** Important ** Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of a
  object is to query the sys.objects catalog view.

Why can't INFORMATION_SCHEMA views be used to determine the schema of an object?

Is this information erroneous?


texts: Create a new column in a view and assign it a data type

texts:
here is my query:

CREATE VIEW VIEW2
AS SELECT [Column1],
(NULL) AS column2 VARCHAR(10)
FROM VIEW1;

I get an error in the third line.

If I leave the query like this, without specifying the data type for Column2, the query works, but the default data type will be int, whereas I want to have VARCHAR.

CREATE VIEW VIEW2
AS SELECT [Column1],
(NULL) AS column2
FROM VIEW1;

Can you please help? Thank you!

Note: Only Column1 is in VIEW1. Column2 is NOT in VIEW1, it's a generated column with NULL values.


texts: How to refactor this subquery to be written only once?

texts:
procedure foo(p1 IN INTEGER,
    p2 IN INTEGER,
    ooo out sys_refcursor)
is
begin

open ooo for
select T1.*
from T1
where T1.x=p1
    and (
        p2 in (SELECT z from T2 where T2.someId=T1.SomeId and T2.y=12)
    or
        not exists (SELECT z from T2 where T2.someId=T1.SomeId and T2.y=12)
        );
end;

The subquery is the exact same both times. Is there a way to refactor it to be specified only once? On Oracle at least.

One way would be to use it as a join:

procedure foo(p1 IN INTEGER,
    p2 IN INTEGER,
    ooo out sys_refcursor)
is
begin

open ooo for
select unique T1.*
from T1
left join T2 on T2.someId=T1.SomeId and T2.y=12
where T1.x=p1
    and (
        p2 = T2.z
    or
        T2.z is null -- T2.z is a NOT NULL column
        );
end;

But this is not equivalent (I added UNIQUE to filter out rows multiplied due to join, but this also removes possible correct duplicate rows from T1).

PS: Made an SQL Fiddle with the two queries from above and from the two answers below.


texts: How can I "merge" two queries into a single one?

texts:
I have the two queries shown below:

declare @myTag1
select @myTag1= TagID
from tag.Tag
where TagName = 'MyTag1'

declare @myTag2
select @myTag2= TagID
from tag.Tag
where TagName = 'MyTag2'

How can I "merge" these queries into only one? I tried the below way:

SELECT CASE TagName
        WHEN 'MyTag1' THEN @MyTag1
        WHEN 'MyTag2' THEN @MyTag2
   END,
   @myValue = TagID
FROM   tag.Tag
WHERE  TagName IN ('MyTag1', 'MyTag2')

But it gives the following error.

  Msg 141, Level 15, State 1, Line 9
   A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Any help would be much appreciated


texts: Any way to ensure that Change Data Capture clean up process does not delete the change data that you haven't processed yet?

texts:
I'm using CDC feature to track changes that are being made, since the cleanup period is set to 3 days by default, I have to run jobs to archive this change data from the CDC system tables to my tables. However, there's a probability that CDC might delete a few change data records that I haven't been able to archive yet.

For instance, if i set the retention period to 10 minutes and run the cleanup job after 10 minutes, only the data that has been archived should be deleted, and the data that hasn't been archived should be left untouched.
Is there a way around this predicament?